extract($_GET);
$headers = array("Form Number", "Location ID", "Sample ID", "Facility", "District", "Hub", "IP", "Date of Collection", "Sample Type", "Patient ART", "PID", "Patient OtherID", "Gender", "Date Of Birth", "Age (Years)", "Phone Number", "Has Patient Been on treatment for at least 6 months", "Date of Treatment Initiation", "Current Regimen", "Other Regimen", "Indication for Treatment Initiation", "Other Indication", "Which Treatment Line is Patient on", "Reason for Failure", "Is Patient Pregnant", "ANC Number", "Is Patient Breastfeeding", "Patient has Active TB", "If Yes are they on", "ARV Adherence", "Routine Monitoring", "Last Viral Load Date", "Value", "Sample Type ", "Repeat Viral Load Test after Suspected Treatment Failure adherence counseling", "Last Viral Load Date ", "Value ", "Sample Type ", "Suspected Treatment Failure", "Last Viral Load Date ", "Value ", "Sample Type ", "Tested", "Last Worksheet", "Machine Type", "Result", "Date/Time Approved", "Date/Time Rejected", "Rejection Reason", "Rejection Category", "Date/Time Added to Worksheet", "Date/Time Latest Results Uploaded", "Date/Time Results Printed", "Date Received at CPHL", "Date/Time First Printed", "Date/Time Sample was Captured");
fputcsv($output, $headers);
function rjctnRsnCase()
{
$arr = array('eligibility' => "outcomeReasonsID in (77,78,14,64,65,76) ", 'incomplete_form' => "outcomeReasonsID in (4,71,72,69,70,67,68,79,80,87,88,86, 61,81,82)", 'quality_of_sample' => "outcomeReasonsID in (9,60,74,10,59,8,63,75,2,7,85,1,5,62 ,3,15,83,84)");
$ret = "CASE ";
foreach ($arr as $k => $v) {
$ret .= "WHEN {$v} THEN '{$k}' ";
}
$ret .= " END";
return $ret;
}
$fro_s = isset($fro_s) ? $fro_s - 3 * 60 * 60 : date("Y-m-01 00:00:00");
$to_s = isset($to_s) ? $to_s + 21 * 60 * 60 - 1 : date("Y-m-d 23:59:59");
$rjctn_cat_case = rjctnRsnCase();
$sql = " SELECT s.*,s.id AS s_id,facility,district,hub,ip,\n\t\t\ts_type.appendix AS sample_type,\n\t\t\tartNumber,otherID,gender,GROUP_CONCAT( ph.phone SEPARATOR ', ') AS phone,\n\t\t\tregimen.appendix AS current_regimen,\n\t\t\ttrtmt_init.appendix AS treatment_indication,\n\t\t\tadhere.appendix AS arv_adherence,\n\t\t\ts_type_rm.appendix AS sample_type_rm,\n\t\t\ts_type_rp.appendix AS sample_type_rp,\n\t\t\ts_type_st.appendix AS sample_type_st,\n\t\t\tr.machine,verify.outcome AS approval_status,verify.created AS approval_time,\n\t\t\trjct.appendix AS rejection_reason,\n\t\t\t(UNIX_TIMESTAMP(s.created)-UNIX_TIMESTAMP(p.dateOfBirth))/31536000 AS age,\n\t\t\tp.dateOfBirth,\n\t\t\ts_regimen.otherRegimen,a_line.appendix AS trmt_line,\n\t\t\taf_reason.appendix AS failure_reason,\n\t\t\t{$rjctn_cat_case} AS rejection_category,\n\t\t\tr.vlSampleID AS tested,print.created AS print_date,\n\t\t\tGROUP_CONCAT(wksht.created SEPARATOR ',') AS wksht_date,\n\t\t\tGROUP_CONCAT(wksht.worksheetID SEPARATOR ',') AS worksheetID,\n\t\t\tr.created AS res_date,r.resultAlphanumeric\n\t\tFROM vl_samples AS s \n\t\tLEFT JOIN vl_patients AS p ON s.patientID=p.id\n\t\tLEFT JOIN vl_results_merged AS r ON s.vlSampleID=r.vlSampleID\n\t\tLEFT JOIN vl_facilities AS f ON s.facilityID=f.id\n\t\tLEFT JOIN vl_districts AS d ON s.districtID=d.id\n\t\tLEFT JOIN vl_hubs AS h ON s.hubID=h.id\n\t\tLEFT JOIN vl_ips AS ip ON f.ipID=ip.id\n\t\tLEFT JOIN vl_appendix_sampletype AS s_type ON s.sampleTypeID=s_type.id\n\t\tLEFT JOIN vl_patients_phone AS ph ON p.id=ph.patientID\n\t\tLEFT JOIN vl_appendix_regimen AS regimen ON s.currentRegimenID=regimen.id\n\t\tLEFT JOIN vl_appendix_treatmentinitiation AS trtmt_init ON s.treatmentInitiationID=trtmt_init.id\n\t\tLEFT JOIN vl_appendix_arvadherence AS adhere ON s.arvAdherenceID=adhere.id\n\t\tLEFT JOIN vl_appendix_sampletype AS s_type_rm ON s.routineMonitoringSampleTypeID=s_type_rm.id\n\t\tLEFT JOIN vl_appendix_sampletype AS s_type_rp ON s.repeatVLTestSampleTypeID=s_type_rp.id\n\t\tLEFT JOIN vl_appendix_sampletype AS s_type_st ON s.suspectedTreatmentFailureSampleTypeID=s_type_st.id\n\t\tLEFT JOIN vl_samples_verify AS verify ON s.id=verify.sampleID\n\t\tLEFT JOIN vl_appendix_samplerejectionreason AS rjct ON verify.outcomeReasonsID=rjct.id\n\t\tLEFT JOIN vl_samples_otherregimen AS s_regimen ON s.id=s_regimen.sampleID\n\t\tLEFT JOIN vl_appendix_treatmentstatus AS a_line ON s.treatmentStatusID=a_line.id\n\t\tLEFT JOIN vl_appendix_failurereason AS af_reason ON s.reasonForFailureID=af_reason.id\n\t\tLEFT JOIN vl_logs_printedresults AS print ON s.id=print.sampleID\n\t\tLEFT JOIN vl_samples_worksheet AS wksht ON s.id=wksht.sampleID\n\t\tWHERE UNIX_TIMESTAMP(s.created) BETWEEN {$fro_s} AND {$to_s}\n\t\tGROUP BY s.id";
$res = mysqlquery($sql);
// loop over the rows, outputting them
while ($row = mysqlfetcharray($res)) {
extract($row);
$row2 = array();
$row2["Form Number"] = $formNumber;
$row2["Location ID"] = $vlSampleID;
$row2["Sample ID"] = $s_id;
$row2["Facility"] = $facility;
$row2["District"] = $district;
$row2["Hub"] = $hub;
$row2["IP"] = $ip;
$row2["Date of Collection"] = $collectionDate;
$row2["Sample Type"] = $sample_type;
function getRejects2($year)
{
$ret = [];
$rjctn_rsn_case = rjctnRsnCase();
$age_grp_case = ageGroupCase();
$sql = "SELECT facilityID,month(s.created) AS mth,count(v.id) AS num,{$age_grp_case} AS age_group ,{$rjctn_rsn_case} AS rjctn_rsn\n\t\t FROM vl_samples_verify AS v\n\t\t LEFT JOIN vl_samples AS s ON v.sampleID=s.id \n\t\t LEFT JOIN vl_patients AS p ON s.patientID=p.id\n\t\t WHERE YEAR(s.created)='{$year}' AND outcome='Rejected'\n\t\t GROUP BY rjctn_rsn,mth,age_group,facilityID\n\t\t ";
$res = mysql_query($sql) or die(mysql_error());
while ($row = mysql_fetch_assoc($res)) {
extract($row);
$ret[$mth][$age_group][$facilityID][$rjctn_rsn] = $num;
}
return $ret;
}
