I switched to CSVExportable and I'm seeing the following..... See below for the config I'm using and the SQL query in question. I can provide an example of the CSV on request, results of query and the resulting Koolreports csv
- I cannot seem to control the column order. Sometimes it seems like it's in a random order.
- period and billingproviderID, even both are integers, are being split into 2 cols.
THanks Jim
$ReportObj->exportToCSV(
[
"dataStores" => [
"PBHBDetailDownload" => [
"separator" => ",", // default separator = "," i.e. comma
"enclosure" => "\"", // default general enclosure = "" i.e. empty string
"typeEnclosures" => [
"string" => "\"", // default string enclosure is general enclosure
"date" => "\"", // default date enclosure is general enclosure
"datetime" => "\"", // default datetime enclosure is general enclosure
"number" => "", // default number enclosure = "" i.e. empty string
"boolean" => "", // default boolean enclosure = "" i.e. empty string
],
'columns' =>[
'modifier_two',
'modifier_three',
'modifier_four',
'units',
'charge',
'wRVU',
'payment,billing_provider_display_name',
'billingproviderID',
'performing_provider_display_name',
'performingproviderID',
'service_date',
'post_date',
'pos_name_full',
'trans_type',
'period',
'division_rpt_division_name',
'cost_center,division_rpt_dep_name',
'cpt_code',
'modifier_one',
],
],
],
// General options for all datastores
"useLocalTempFolder" => false,
"autoDeleteTempFile" => true,
"useCustomColumnEnclosure" => true,
"useCustomColumnNullString" => false,
"useCustomColumnEnclosureEscape" => false,
"BOM" => true, // default bom = false
"buffer" => 1000, // unit: KB ~ 1000 bytes. Default buffer = 1000 KB
//"buffer" => PHP_INT_MAX,
],
)
->toBrowser("PBHBDetailDownload.csv");
My query:
select trans_type,period,division_rpt_division_name,cost_center,division_rpt_dep_name,cpt_code,CASE WHEN modifier_one = '00' THEN NULL ELSE modifier_one END AS modifier_one,CASE WHEN modifier_two = '00' THEN NULL ELSE modifier_two END AS modifier_two,CASE WHEN modifier_three = '00' THEN NULL ELSE modifier_three END AS modifier_three,CASE WHEN modifier_four = '00' THEN NULL ELSE modifier_four END AS modifier_four,FORMAT(
CASE 1
WHEN 1
THEN net_eligible_units
WHEN 2
THEN units
END ,
2
) AS units,
FORMAT(
CASE 1
WHEN 1 THEN net_eligible_charge
WHEN 2 THEN charge
END ,
2
)AS charge,FORMAT(
CASE 1
WHEN 1 THEN net_eligible_wrvu
WHEN 2 THEN wrvu
END ,
2
)AS wRVU,
FORMAT(
payment,
2
) as payment,billing_provider_display_name,billingproviderID,performing_provider_display_name,performingproviderID,DATE_FORMAT(service_date, '%m/%d/%Y') as service_date,DATE_FORMAT(post_date, '%m/%d/%Y') as post_date,pos_name_full from `division_rpt_provider_summaries` where `period` in (202107) and `division_rpt_cost_center_id` in (82) and `billing_provider_id` in (955) and `division_rpt_hcpc_cpt_id` in (5522)
)