API tools faq
paste
Guest User

Untitled

a guest
Nov 21st, 2017
63
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 23.05 KB | None | 0 0
raw download clone embed print report
  1. module.exports = new (function() {// jshint ignore:line
  2. var _ = require('lodash');
  3. var csv = require('csv');
  4. var mysql = require('mysql');
  5. var Workflow = require('../Workflow');
  6. Workflow.call(this); //inherit Workflow
  7. var app = require(__dirname + "/../../../server/server");
  8. var dataSource = app.dataSources.paddingtonDB;
  9. var self = this;
  10.  
  11. this.getBillingReportCSV = function(organizationId, accountId, filter, res, callback) {
  12. var columns = [
  13. { columnName: "SUBMITTED BY", value: "CONCAT(cb.firstName,\' ', cb.lastName)" },
  14. { columnName: "REF CLINIC", value: "rp_o.title" },
  15. { columnName: "INSURANCE", value: "ai.insuranceName" },
  16. { columnName: "CLINIC", value: "IF(cl.title IS NOT NULL, cl.title, IF(o.title IS NOT NULL, o.title, 'Consumer'))" },
  17. { columnName: "PATIENT NAME", value: "CONCAT(a.firstName,\' ', a.lastName)" },
  18. { columnName: "PATIENT ID", value: "IF(a.oldPatientId IS NULL, a.accountId, a.oldPatientId)" },
  19. { columnName: "PRIOR AUTH", value: "ai.insurancePriorAuth" },
  20. { columnName: "VALID DATES", value: "ai.insuranceExpireDate" },
  21. { columnName: "InsurancePlanName", value: "i.name" },
  22. { columnName: "InsurancePayerID", value: "i.payerId" },
  23. { columnName: "InsuranceStreetAddr", value: "IF(i.addressLine2 IS NULL, i.addressLine1, CONCAT(i.addressLine1, ' ', i.addressLine2))" },
  24. { columnName: "InsuranceCity", value: "i.city" },
  25. { columnName: "InsuranceState", value: "i_s.abbreviation" },
  26. { columnName: "InsuranceZip", value: "i.postalCode" },
  27. { columnName: "InsuranceCityStateZip", value: "" },
  28. { columnName: "InsurancePhone", value: "" },
  29. { columnName: "PlanMedicare", value: "IF(i.isMedicare=1, 'x', '')" },
  30. { columnName: "PlanMedicaid", value: "IF(i.isMedicaid=1, 'x', '')" },
  31. { columnName: "PlanChampus", value: "IF(i.isCHAMPUS=1, 'x', '')" },
  32. { columnName: "PlanChampVA", value: "IF(i.isCHAMPVA=1, 'x', '')" },
  33. { columnName: "PlanGroupHealthPlan", value: "IF(i.isGroupHealthPlan=1, 'x', '')" },
  34. { columnName: "PlanFECA", value: "IF(i.isFECA=1, 'x', '')" },
  35. { columnName: "PlanOther", value: "IF(i.isOther=1, 'x', '')" },
  36. { columnName: "PatientID", value: "ai.insuranceAccountNumber" },
  37. { columnName: "PatientLast", value: "a.lastName" },
  38. { columnName: "PatientFirst", value: "a.firstName" },
  39. { columnName: "PatientMidInit", value: "a.middleInitial" },
  40. { columnName: "PatientDOB", value: "DATE_FORMAT(a.birthDate, '%m/%d/%Y')" },
  41. { columnName: "patientMale", value: "IF(a.gender = 'male', 'x', '')" },
  42. { columnName: "patientFemale", value: "IF(a.gender = 'female', 'x', '')" },
  43. { columnName: "InsuredLast", value: "ai.insuredLastName" },
  44. { columnName: "InsuredFirst", value: "ai.insuredFirstName" },
  45. { columnName: "InsuredMidInit", value: "" },
  46. { columnName: "PatientStreetAddress", value: "IF(a.addressLine2 IS NULL, a.addressLine1, CONCAT(a.addressLine1, ' ', a.addressLine2))" },
  47. { columnName: "PatientCity", value: "a.city" },
  48. { columnName: "PatientState", value: "a_s.name" },
  49. { columnName: "PatientZip", value: "a.postalCode" },
  50. { columnName: "PatientPhone", value: "a.phoneNumber" },
  51. { columnName: "PatientRelationSELF", value: "IF((ai.insuredRelation is null OR (ai.insuredRelation <> 'Spouse' AND ai.insuredRelation <> 'Child' AND ai.insuredRelation <> 'Other') ), 'x', '')" },
  52. { columnName: "PatientRelationSPOUSE", value: "IF(ai.insuredRelation = 'Spouse', 'x', '')" },
  53. { columnName: "PatientRelationCHILD", value: "IF(ai.insuredRelation = 'Child', 'x', '')" },
  54. { columnName: "PatientRelationOTHER", value: "IF(ai.insuredRelation = 'Other', 'x', '')" },
  55. { columnName: "InsuredStreetAddress", value: "IF(insuredAddressLine2 IS NULL, insuredAddressLine1, CONCAT(insuredAddressLine1, ' ', insuredAddressLine2))" },
  56. { columnName: "InsuredCity", value: "ai.insuredCity" },
  57. { columnName: "InsuredState", value: "ai_s.name" },
  58. { columnName: "InsuredZip", value: "ai.insuredPostalCode" },
  59. { columnName: "InsuredPhone", value: "ai.insuredPhoneNumber" },
  60. { columnName: "PatientMaritalSingle", value: "" },
  61. { columnName: "PatientMaritalMarried", value: "" },
  62. { columnName: "PatientMaritalOther", value: "" },
  63. { columnName: "PatientEmploymentEmployed", value: "" },
  64. { columnName: "PatientEmploymentFullTimeStudent", value: "" },
  65. { columnName: "PatientEmploymentPartTimeStudent", value: "" },
  66. { columnName: "OtherInsuredLast", value: "" },
  67. { columnName: "OtherInsuredFirst", value: "" },
  68. { columnName: "OtherInsuredMidInit", value: "" },
  69. { columnName: "OtherInsuredPolicyOrGroupNumber", value: "" },
  70. { columnName: "OtherInsuredDOB", value: "" },
  71. { columnName: "OtherInsuredSexMale", value: "" },
  72. { columnName: "OtherInsuredSexFemale", value: "" },
  73. { columnName: "OtherInsuredEmlpoyerNameOrSchoolName", value: "" },
  74. { columnName: "OtherInsuredInsurancePlanorProgramName", value: "" },
  75. { columnName: "CondtionRelatedToEmlpoymentYes", value: "" },
  76. { columnName: "CondtionRelatedToEmlpoymentNo", value: "" },
  77. { columnName: "CondtionRelatedToAutoAccidentYes", value: "" },
  78. { columnName: "CondtionRelatedToAutoAccidentNo", value: "" },
  79. { columnName: "AutoAccidentState", value: "" },
  80. { columnName: "CondtionRelatedToOtherAccidentYes", value: "" },
  81. { columnName: "CondtionRelatedToOtherAccidentNo", value: "" },
  82. { columnName: "ReservedForLocalUse", value: "" },
  83. { columnName: "InsuredPolicyGroupOrFecaNumber", value: "" },
  84. { columnName: "InsuredDOB", value: "" },
  85. { columnName: "InsuredGenderMale", value: "" },
  86. { columnName: "InsuredGenderFemale", value: "" },
  87. { columnName: "InsuredEmployerNameOrSchoolName", value: "" },
  88. { columnName: "InsuredInsurancePlanNameOrProgramName", value: "" },
  89. { columnName: "IsThereAnotherHealhPlanBenefitYes", value: "" },
  90. { columnName: "IsThereAnotherHealhPlanBenefitNo", value: "" },
  91. { columnName: "PatientSignature", value: "'x'" },
  92. { columnName: "PatientSignatureDate", value: "DATE_FORMAT(c.startDate, '%m/%d/%Y')" },
  93. { columnName: "InsuredSignature", value: "'x'" },
  94. { columnName: "DateOfCurrent", value: "" },
  95. { columnName: "DateOfSimilarIllness", value: "" },
  96. { columnName: "UnableToWorkFromDate", value: "" },
  97. { columnName: "UnableToWorkToDate", value: "" },
  98. { columnName: "ReferringPhysician", value: "acc_rp.displayName" },
  99. { columnName: "ReferPhysQualifier", value: "" },
  100. { columnName: "ReferringPhysicianID", value: "" },
  101. { columnName: "Refer_Phys_NPI", value: "acc_rp.answer" },
  102. { columnName: "Super_Phys_NPI", value: "" },
  103. { columnName: "HospitalizationFromDate", value: "" },
  104. { columnName: "HospitalizationToDate", value: "" },
  105. { columnName: "Box19Notes", value: "" },
  106. { columnName: "OutsideLabChargesYes", value: "" },
  107. { columnName: "OutsideLabChargesNo", value: "" },
  108. { columnName: "OutsideLabFees", value: "" },
  109. { columnName: "DiagCode1", value: "cd.diagnosis" }, //This field needs to be exploded out into multiple columns
  110. { columnName: "DiagCode2", value: "" },
  111. { columnName: "DiagCode3", value: "" },
  112. { columnName: "DiagCode4", value: "" },
  113. { columnName: "DiagCode5", value: "" },
  114. { columnName: "DiagCode6", value: "" },
  115. { columnName: "DiagCode7", value: "" },
  116. { columnName: "DiagCode8", value: "" },
  117. { columnName: "MedicaidResubCode", value: "" },
  118. { columnName: "MedicaidRefNumber", value: "" },
  119. { columnName: "PriorAuthNo", value: "ai.insurancePriorAuth" },
  120. { columnName: "HCFACLIANumber", value: "" },
  121. { columnName: "FromDateOfService1", value: "DATE_FORMAT(c.completedDate, '%m/%d/%Y')" },
  122. { columnName: "ToDateOfService1", value: "DATE_FORMAT(c.completedDate, '%m/%d/%Y')" },
  123. { columnName: "PlaceOfService1", value: "11" },
  124. { columnName: "EMG1", value: "" },
  125. { columnName: "CPT1", value: "CASE i.insuranceId " +
  126. "WHEN 7 THEN CONCAT('99444 was ', cd.cptCode) " +
  127. "WHEN 36 THEN CONCAT('99444 was ', cd.cptCode) " +
  128. "WHEN 40 THEN CONCAT('99444 was ', cd.cptCode) " +
  129. "WHEN 28 THEN CONCAT('99203 was ', cd.cptCode) " +
  130. "WHEN 29 THEN CONCAT('99203 was ', cd.cptCode) " +
  131. "WHEN 30 THEN CONCAT('99203 was ', cd.cptCode) " +
  132. "WHEN 31 THEN CONCAT('99444 was ', cd.cptCode) " +
  133. "WHEN 32 THEN CONCAT('99444 was ', cd.cptCode) " +
  134. "WHEN 38 THEN CONCAT('99444 was ', cd.cptCode) " +
  135. "ELSE cd.cptCode " +
  136. "END" },
  137. { columnName: "ModifierA1", value: "CASE i.insuranceId " +
  138. "WHEN 7 THEN '' " +
  139. "WHEN 36 THEN '' " +
  140. "WHEN 40 THEN '' " +
  141. "WHEN 28 THEN 'GQ' " +
  142. "WHEN 29 THEN 'GQ' " +
  143. "WHEN 30 THEN 'GQ' " +
  144. "WHEN 31 THEN 'GQ' " +
  145. "WHEN 32 THEN 'GQ' " +
  146. "WHEN 38 THEN 'GQ' " +
  147. "ELSE 'GQ' " +
  148. "END" },
  149. { columnName: "ModifierB1", value: "" },
  150. { columnName: "ModifierC1", value: "" },
  151. { columnName: "ModifierD1", value: "" },
  152. { columnName: "DiagCodePointer1", value: "'1'" }, //comma separated count of diagnosis
  153. { columnName: "Charges1", value: "200.00" },
  154. { columnName: "Units1", value: "1" },
  155. { columnName: "EPSDT1", value: "" },
  156. { columnName: "RenderingPhysQualifier1", value: "" },
  157. { columnName: "RenderingPhysID1", value: "" },
  158. { columnName: "RenderingPhysNPI1", value: "de.nationalProviderId" },
  159. { columnName: "FromDateOfService2", value: "" },
  160. { columnName: "ToDateOfService2", value: "" },
  161. { columnName: "PlaceOfService2", value: "" },
  162. { columnName: "EMG2", value: "" },
  163. { columnName: "CPT2", value: "" },
  164. { columnName: "ModifierA2", value: "" },
  165. { columnName: "ModifierB2", value: "" },
  166. { columnName: "ModifierC2", value: "" },
  167. { columnName: "ModifierD2", value: "" },
  168. { columnName: "DiagCodePointer2", value: "" },
  169. { columnName: "Charges2", value: "" },
  170. { columnName: "Units2", value: "" },
  171. { columnName: "EPSDT2", value: "" },
  172. { columnName: "RenderingPhysQualifier2", value: "" },
  173. { columnName: "RenderingPhysID2", value: "" },
  174. { columnName: "RenderingPhysNPI2", value: "" },
  175. { columnName: "FromDateOfService3", value: "" },
  176. { columnName: "ToDateOfService3", value: "" },
  177. { columnName: "PlaceOfService3", value: "" },
  178. { columnName: "EMG3", value: "" },
  179. { columnName: "CPT3", value: "" },
  180. { columnName: "ModifierA3", value: "" },
  181. { columnName: "ModifierB3", value: "" },
  182. { columnName: "ModifierC3", value: "" },
  183. { columnName: "ModifierD3", value: "" },
  184. { columnName: "DiagCodePointer3", value: "" },
  185. { columnName: "Charges3", value: "" },
  186. { columnName: "Units3", value: "" },
  187. { columnName: "EPSDT3", value: "" },
  188. { columnName: "RenderingPhysQualifier3", value: "" },
  189. { columnName: "RenderingPhysID3", value: "" },
  190. { columnName: "RenderingPhysNPI3", value: "" },
  191. { columnName: "FromDateOfService4", value: "" },
  192. { columnName: "ToDateOfService4", value: "" },
  193. { columnName: "PlaceOfService4", value: "" },
  194. { columnName: "EMG4", value: "" },
  195. { columnName: "CPT4", value: "" },
  196. { columnName: "ModifierA4", value: "" },
  197. { columnName: "ModifierB4", value: "" },
  198. { columnName: "ModifierC4", value: "" },
  199. { columnName: "ModifierD4", value: "" },
  200. { columnName: "DiagCodePointer4", value: "" },
  201. { columnName: "Charges4", value: "" },
  202. { columnName: "Units4", value: "" },
  203. { columnName: "EPSDT4", value: "" },
  204. { columnName: "RenderingPhysQualifier4", value: "" },
  205. { columnName: "RenderingPhysID4", value: "" },
  206. { columnName: "RenderingPhysNPI4", value: "" },
  207. { columnName: "FromDateOfService5", value: "" },
  208. { columnName: "ToDateOfService5", value: "" },
  209. { columnName: "PlaceOfService5", value: "" },
  210. { columnName: "EMG5", value: "" },
  211. { columnName: "CPT5", value: "" },
  212. { columnName: "ModifierA5", value: "" },
  213. { columnName: "ModifierB5", value: "" },
  214. { columnName: "ModifierC5", value: "" },
  215. { columnName: "ModifierD5", value: "" },
  216. { columnName: "DiagCodePointer5", value: "" },
  217. { columnName: "Charges5", value: "" },
  218. { columnName: "Units5", value: "" },
  219. { columnName: "EPSDT5", value: "" },
  220. { columnName: "RenderingPhysQualifier5", value: "" },
  221. { columnName: "RenderingPhysID5", value: "" },
  222. { columnName: "RenderingPhysNPI5", value: "" },
  223. { columnName: "FromDateOfService6", value: "" },
  224. { columnName: "ToDateOfService6", value: "" },
  225. { columnName: "PlaceOfService6", value: "" },
  226. { columnName: "EMG6", value: "" },
  227. { columnName: "CPT6", value: "" },
  228. { columnName: "ModifierA6", value: "" },
  229. { columnName: "ModifierB6", value: "" },
  230. { columnName: "ModifierC6", value: "" },
  231. { columnName: "ModifierD6", value: "" },
  232. { columnName: "DiagCodePointer6", value: "" },
  233. { columnName: "Charges6", value: "" },
  234. { columnName: "Units6", value: "" },
  235. { columnName: "EPSDT6", value: "" },
  236. { columnName: "RenderingPhysQualifier6", value: "" },
  237. { columnName: "RenderingPhysID6", value: "" },
  238. { columnName: "RenderingPhysNPI6", value: "" },
  239. { columnName: "TaxID", value: "'27-3123104'" },
  240. { columnName: "SSN", value: "" },
  241. { columnName: "EIN", value: "'x'" },
  242. { columnName: "PatientAcctNumber", value: "CONCAT(IF(a.oldPatientId IS NULL, a.accountId, a.oldPatientId),'-',c.caseId)" },
  243. { columnName: "AcceptAssignYes", value: "'x'" },
  244. { columnName: "AcceptAssignNo", value: "" },
  245. { columnName: "TotalCharges", value: "200.00" },
  246. { columnName: "AmountPaid", value: "0.00" },
  247. { columnName: "BalanceDue", value: "200.00" },
  248. { columnName: "PhysicianSignature", value: "CONCAT(de.firstName,' ',de.lastname)" },
  249. { columnName: "PhysicianSignatureDate", value: "DATE_FORMAT(c.completedDate, '%m/%d/%Y')" },
  250. { columnName: "PhysicianLast", value: "de.lastname" },
  251. { columnName: "PhysicianFirst", value: "de.firstName" },
  252. { columnName: "PhysicianMidInit", value: "de.middleInitial" },
  253. { columnName: "FacilityName", value: "" },
  254. { columnName: "FacilityStreetAddr", value: "" },
  255. { columnName: "FacilityCity", value: "" },
  256. { columnName: "FacilityState", value: "" },
  257. { columnName: "FacilityZip", value: "" },
  258. { columnName: "FacilityCityStateZip", value: "" },
  259. { columnName: "FacilityNPI", value: "" },
  260. { columnName: "FacilityID", value: "" },
  261. { columnName: "MammographyCertification", value: "" },
  262. { columnName: "SupplierName", value: "de_o.title" },
  263. { columnName: "SupplierStreetAddr", value: "IF(de_o.addressLine2 IS NULL, de_o.addressLine1, CONCAT(de_o.addressLine1, ' ', de_o.addressLine2))" },
  264. { columnName: "SupplierCity", value: "de_o.city" },
  265. { columnName: "SupplierState", value: "de_o_s.name" },
  266. { columnName: "SupplierZip", value: "de_o.postalCode" },
  267. { columnName: "SupplierCityStateZip", value: "" },
  268. { columnName: "SupplierPhone", value: "de_o.phoneNumber" },
  269. { columnName: "SupplierNPI", value: "'1417263922'" },
  270. { columnName: "GroupID", value: "'ZZ207N00000X'" }
  271. ];
  272.  
  273. var sql = 'SELECT ';
  274.  
  275. for (var i = 0; i < columns.length; i++) {
  276. var column = columns[i];
  277. var value = column.value;
  278. if (!value || value.length == 0) continue;
  279. sql += value + ' as `' + column.columnName + '`';
  280. if (i < columns.length-1) {
  281. sql += ', ';
  282. }
  283. }
  284.  
  285. sql += " FROM `Case` as c " +
  286. "LEFT JOIN Organization as o ON o.organizationId = c.organizationId " +
  287. "INNER JOIN Account as a ON a.accountId = c.accountId " +
  288. "INNER JOIN State as a_s ON a_s.stateId = a.stateId " +
  289. "INNER JOIN Account as de ON de.accountId = c.lastUpdatedBy " + // dermatologist who closed the case
  290. "INNER JOIN Organization as de_o ON de_o.organizationId = ? " +
  291. "INNER JOIN State as de_o_s ON de_o_s.stateId = de_o.stateId " +
  292. "LEFT JOIN Account as cb ON cb.accountId = c.createdBy " + // account of the person who created the case
  293. "LEFT JOIN AccountLocation as cb_al ON cb_al.accountId = cb.accountId " +
  294. "LEFT JOIN Location as cb_l on cb_l.locationId = cb_al.locationId AND cb_l.organizationId = o.organizationId " + // location of the person who created the case
  295. "LEFT JOIN Location as cl on cl.locationId = c.locationId " + // location of case
  296. "LEFT JOIN ( " +
  297. "SELECT IF(acco.displayName IS NULL, acco.displayName, CONCAT(acco.firstname, ' ', acco.lastName)) as displayName, a_rp.answer as answer, af_rp.accountId as accountId FROM Account as acco " +
  298. "LEFT JOIN Form as f_rp ON f_rp.title = 'Referring Provider' " +
  299. "LEFT JOIN Question as q_rp ON q_rp.field = 'nationalProviderId' AND q_rp.formId = f_rp.formId " +
  300. "LEFT JOIN AccountForm as af_rp ON f_rp.formId = af_rp.formId " +
  301. "LEFT JOIN Answer as a_rp On a_rp.questionId = q_rp.questionId AND a_rp.accountFormId = af_rp.accountFormId " +
  302. "WHERE a_rp.answer = acco.nationalProviderId " +
  303. "ORDER BY a_rp.lastUpdated DESC " +
  304. ") as acc_rp ON acc_rp.accountId = c.accountId " +
  305. "LEFT JOIN ( " +
  306. "SELECT " +
  307. "af.accountId, " +
  308. "af.accountFormId, " +
  309. "MAX(IF(q.field = 'insuranceId', aw.answer, NULL)) as insuranceId, " +
  310. "MAX(IF(q.field = 'insuranceName', aw.answer, NULL)) as insuranceName, " +
  311. "MAX(IF(q.field = 'insuranceAccountNumber', aw.answer, NULL)) as insuranceAccountNumber, " +
  312. "MAX(IF(q.field = 'insurancePriorAuth', aw.answer, NULL)) as insurancePriorAuth, " +
  313. "MAX(IF(q.field = 'insuranceExpireDate', aw.answer, NULL)) as insuranceExpireDate, " +
  314. "MAX(IF(q.field = 'insuredLastName', aw.answer, NULL)) as insuredLastName, " +
  315. "MAX(IF(q.field = 'insuredFirstName', aw.answer, NULL)) as insuredFirstName, " +
  316. "MAX(IF(q.field = 'insuredRelation', aw.answer, NULL)) as insuredRelation, " +
  317. "MAX(IF(q.field = 'insuredAddressLine1', aw.answer, NULL)) as insuredAddressLine1, " +
  318. "MAX(IF(q.field = 'insuredAddressLine2', aw.answer, NULL)) as insuredAddressLine2, " +
  319. "MAX(IF(q.field = 'insuredCity', aw.answer, NULL)) as insuredCity, " +
  320. "MAX(IF(q.field = 'insuredState', aw.answer, NULL)) as insuredState, " +
  321. "MAX(IF(q.field = 'insuredPostalCode', aw.answer, NULL)) as insuredPostalCode, " +
  322. "MAX(IF(q.field = 'insuredPhoneNumber', aw.answer, NULL)) as insuredPhoneNumber " +
  323. "FROM AccountForm as af " +
  324. "INNER JOIN Form as f ON f.formId = af.formId " +
  325. "INNER JOIN FormWorkflow as fw ON fw.formId = f.formId AND fw.module = 'Insurance' " +
  326. "INNER JOIN Answer as aw ON aw.accountFormId = af.accountFormId " +
  327. "INNER JOIN Question as q ON q.questionId = aw.questionId " +
  328. "GROUP BY af.accountFormId " +
  329. ") as ai ON ai.accountId = c.accountId " +
  330. "LEFT JOIN State as ai_s ON ai_s.stateId = ai.insuredState " +
  331. "LEFT JOIN Insurance as i ON i.insuranceId = ai.insuranceId " +
  332. "LEFT JOIN State as i_s ON i_s.stateId = i.stateId " +
  333. "LEFT JOIN ( " +
  334. "SELECT " +
  335. "af.caseId, " +
  336. "GROUP_CONCAT(DISTINCT IF(q.field = 'diagnosis', " +
  337. "(SELECT d.icd FROM Diagnosis as d WHERE d.diagnosisId = aw.answer), NULL)) as diagnosis, " +
  338. "MAX(IF(q.field = 'cptCode', aw.answer, NULL)) as cptCode " +
  339. "FROM AccountForm as af " +
  340. "INNER JOIN Form as f ON f.formId = af.formId " +
  341. "INNER JOIN FormWorkflow as fw ON fw.formId = f.formId AND fw.module = 'CaseComplaintDiagnosis' " +
  342. "INNER JOIN Answer as aw ON aw.accountFormId = af.accountFormId " +
  343. "INNER JOIN Question as q ON q.questionId = aw.questionId " +
  344. "WHERE q.field IN ('diagnosis', 'cptCode') " +
  345. "GROUP BY af.caseId " +
  346. ") as cd ON cd.caseId = c.caseId " +
  347. "LEFT JOIN ( " +
  348. "SELECT rp_o.title, af.accountId " +
  349. "FROM AccountForm AS af " +
  350. "INNER JOIN Form AS f " +
  351. "ON f.formid = af.formid " +
  352. "INNER JOIN FormWorkflow AS fw " +
  353. "ON fw.formid = f.formid " +
  354. "AND fw.module = 'ReferringProvider' " +
  355. "INNER JOIN Answer AS aw " +
  356. "ON aw.accountformid = af.accountformid " +
  357. "INNER JOIN Question AS q " +
  358. "ON q.questionid = aw.questionid " +
  359. "LEFT JOIN Account as rp " +
  360. "ON rp.accountId = aw.answer " +
  361. "LEFT JOIN AccountDepartment as rp_ad " +
  362. "ON rp_ad.accountId = rp.accountId " +
  363. "LEFT JOIN Department as rp_d " +
  364. "ON rp_d.departmentId = rp_ad.departmentId " +
  365. "LEFT JOIN Organization as rp_o " +
  366. "ON rp_o.organizationId = rp_d.organizationId " +
  367. "WHERE q.field = 'searchedProvider' " +
  368. ") as rp_o ON rp_o.accountId = c.accountId " +
  369. "WHERE " +
  370. "c.completedDate >= ? AND c.completedDate <= ? ";
  371. var params = [organizationId, filter.startDate, filter.endDate];
  372.  
  373. if (filter.status) {
  374. if (filter.status == 'BILLING') {
  375. sql += ' AND c.status IN (\'FOR_BILLING_REVIEW\',\'BILLING_APPROVED\',\'CLOSED\')';
  376. } else {
  377. sql += " AND c.status = ? ";
  378. params.push(filter.status);
  379. }
  380. }
  381.  
  382. if (filter.insuranceId) {
  383. sql += " AND ai.insuranceId = ? ";
  384. params.push(filter.insuranceId);
  385. }
  386. sql += "GROUP BY c.caseId ";
  387. sql += "ORDER BY c.completedDate";
  388.  
  389. // console.log(mysql.format(sql, params));
  390. dataSource.connector.client.query(sql, params, function(error, results) {
  391. if (error) {
  392. callback(error);
  393. return;
  394. }
  395.  
  396. //Get headers
  397. var headers = [];
  398. _.forEach(columns, function(column) {
  399. headers.push(column.columnName);
  400. });
  401.  
  402. //Format CSV
  403. var data = [headers];
  404. for (var i in results) {
  405. var item = results[i];
  406. var row = [];
  407. for (var k in columns) {
  408. var column = columns[k];
  409. var nameArray=['DiagCode'];//add columns to expand by numbers
  410. for(var j in nameArray) {
  411. var title = nameArray[j];
  412. if (column.columnName == title+1 && item[title+1]) {
  413. var icds = item[title+1].split(",");
  414. var pointers = "";
  415. for (var index in icds) {
  416. var split = icds[index].replace(/"/g, "").replace(/[|]/g, "");
  417. if (split) {//creating empty string
  418. var x = Number(index) + 1;
  419. item[title + x] = split;
  420. if (pointers.length > 0) pointers +=',';
  421. pointers += x;
  422. }
  423. }
  424. if (title == 'DiagCode') item.DiagCodePointer1 = pointers; //save comma separated diag code pointers
  425. }
  426. }
  427. row.push(item[column.columnName]);
  428. }
  429. data.push(row);
  430. }
  431.  
  432. res.setHeader('Content-disposition', 'attachment; filename=report.csv');
  433. res.writeHead(200, {
  434. 'Content-Type': 'text/csv'
  435. });
  436. csv.stringify(data, function(error, results) {
  437. if (error) {
  438. callback(error);
  439. return;
  440. }
  441. res.write(results);
  442. res.end();
  443. //console.log('csv', results);
  444. });
  445. });
  446. };
  447.  
  448. })();
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!