예제 #1
0
파일: order.php 프로젝트: OmondiKevin/ADT
 public function download_order($type = "cdrr", $id)
 {
     $this->load->library('PHPExcel');
     if ($type == "cdrr") {
         $cdrr_id = $id;
         $cdrr_array = array();
         $dir = "Export";
         $drug_name = "CONCAT_WS('] ',CONCAT_WS(' [',sd.name,sd.abbreviation),CONCAT_WS(' ',sd.strength,sd.formulation)) as drug_map";
         $sql = "SELECT c.*,ci.*,cl.*,f.*,co.county as county_name,d.name as district_name,u.*,al.level_name,IF(c.code='D-CDRR',CONCAT('D-CDRR#',c.id),CONCAT('F-CDRR#',c.id)) as cdrr_label,c.status as status_name,sf.name as facility_name,{$drug_name}\n\t\t\t\tFROM cdrr c\n\t\t\t\tLEFT JOIN cdrr_item ci ON ci.cdrr_id=c.id\n\t\t\t\tLEFT JOIN cdrr_log cl ON cl.cdrr_id=c.id\n\t\t\t\tLEFT JOIN sync_facility sf ON sf.id=c.facility_id\n\t\t\t\tLEFT JOIN facilities f ON f.facilitycode=sf.code\n\t\t\t\tLEFT JOIN counties co ON co.id=f.county\n\t\t\t\tLEFT JOIN district d ON d.id=f.district\n\t\t\t\tLEFT JOIN users u ON u.id = cl.user_id\n\t\t\t\tLEFT JOIN access_level al ON al.id=u.Access_Level\n\t\t\t\tLEFT JOIN sync_drug sd ON sd.id=ci.drug_id\n\t\t\t\tLEFT JOIN drugcode dc ON dc.map=sd.id\n\t\t\t\tWHERE c.id = '{$cdrr_id}'";
         $query = $this->db->query($sql);
         $cdrr_array = $query->result_array();
         $report_type = $cdrr_array[0]['code'];
         //Load download template
         $template = "";
         if ($report_type == "D-CDRR") {
             $template = "new_cdrr_aggregate.xlsx";
         } else {
             $template = "new_cdrr_satellite_standalone.xlsx";
         }
         $inputFileName = $_SERVER['DOCUMENT_ROOT'] . '/ADT/assets/' . $template;
         $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
         $objReader = PHPExcel_IOFactory::createReader($inputFileType);
         $objPHPExcel = $objReader->load($inputFileName);
         /*Delete all files in export folder*/
         if (is_dir($dir)) {
             $files = scandir($dir);
             foreach ($files as $object) {
                 if ($object != "." && $object != "..") {
                     unlink($dir . "/" . $object);
                 }
             }
         } else {
             mkdir($dir);
         }
         $objPHPExcel->getActiveSheet()->SetCellValue('C4', $cdrr_array[0]['name']);
         $objPHPExcel->getActiveSheet()->SetCellValue('C5', $cdrr_array[0]['county_name']);
         $objPHPExcel->getActiveSheet()->SetCellValue('E7', date('d/m/Y', strtotime($cdrr_array[0]['period_begin'])));
         if ($report_type == "D-CDRR") {
             $objPHPExcel->getActiveSheet()->SetCellValue('L4', $cdrr_array[0]['facilitycode']);
             $objPHPExcel->getActiveSheet()->SetCellValue('L5', $cdrr_array[0]['district_name']);
             //Sub_county
             $objPHPExcel->getActiveSheet()->SetCellValue('L7', date('d/m/Y', strtotime($cdrr_array[0]['period_end'])));
             $objPHPExcel->getActiveSheet()->SetCellValue('B73', $cdrr_array[0]['comments']);
             $drug_start = 16;
             $drug_end = 69;
         } else {
             $objPHPExcel->getActiveSheet()->SetCellValue('K4', $cdrr_array[0]['facilitycode']);
             $objPHPExcel->getActiveSheet()->SetCellValue('K5', $cdrr_array[0]['district_name']);
             //Sub_county
             $objPHPExcel->getActiveSheet()->SetCellValue('K7', date('d/m/Y', strtotime($cdrr_array[0]['period_end'])));
             $objPHPExcel->getActiveSheet()->SetCellValue('B72', $cdrr_array[0]['comments']);
             $drug_start = 15;
             $drug_end = 68;
         }
         $arr = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
         for ($i = $drug_start; $i <= $drug_end; $i++) {
             $drug = $arr[$i]['B'];
             $pack_size = $arr[$i]['C'];
             if ($drug) {
                 $key = $this->getMappedDrug($drug, $pack_size);
                 if ($key !== null) {
                     foreach ($cdrr_array as $cdrr_item) {
                         if ($key == $cdrr_item['drug_id']) {
                             $objPHPExcel->getActiveSheet()->SetCellValue('D' . $i, $cdrr_item['balance']);
                             $objPHPExcel->getActiveSheet()->SetCellValue('E' . $i, $cdrr_item['received']);
                             $objPHPExcel->getActiveSheet()->SetCellValue('F' . $i, $cdrr_item['dispensed_packs']);
                             $objPHPExcel->getActiveSheet()->SetCellValue('G' . $i, $cdrr_item['losses']);
                             $objPHPExcel->getActiveSheet()->SetCellValue('H' . $i, $cdrr_item['adjustments']);
                             $objPHPExcel->getActiveSheet()->SetCellValue('I' . $i, $cdrr_item['adjustments_neg']);
                             $objPHPExcel->getActiveSheet()->SetCellValue('J' . $i, $cdrr_item['count']);
                             if ($cdrr_array[0]['code'] == "D-CDRR") {
                                 $objPHPExcel->getActiveSheet()->SetCellValue('L' . $i, $cdrr_item['aggr_consumed']);
                                 $objPHPExcel->getActiveSheet()->SetCellValue('M' . $i, $cdrr_item['aggr_on_hand']);
                                 $objPHPExcel->getActiveSheet()->SetCellValue('O' . $i, $cdrr_item['expiry_quant']);
                                 $objPHPExcel->getActiveSheet()->SetCellValue('P' . $i, $cdrr_item['expiry_date']);
                                 $objPHPExcel->getActiveSheet()->SetCellValue('Q' . $i, $cdrr_item['out_of_stock']);
                                 $objPHPExcel->getActiveSheet()->SetCellValue('R' . $i, $cdrr_item['resupply']);
                             } else {
                                 $objPHPExcel->getActiveSheet()->SetCellValue('K' . $i, $cdrr_item['expiry_quant']);
                                 $objPHPExcel->getActiveSheet()->SetCellValue('L' . $i, $cdrr_item['expiry_date']);
                                 $objPHPExcel->getActiveSheet()->SetCellValue('M' . $i, $cdrr_item['out_of_stock']);
                                 $objPHPExcel->getActiveSheet()->SetCellValue('N' . $i, $cdrr_item['resupply']);
                             }
                         }
                         //End of key match to cdrr_id
                     }
                     //End of foreach
                 }
                 //End of key
             }
             //End of drug
         }
         //End of for loop
         if ($cdrr_array[0]['code'] == 'D-CDRR') {
             $objPHPExcel->getActiveSheet()->SetCellValue('D79', $cdrr_array[0]['reports_expected']);
             $objPHPExcel->getActiveSheet()->SetCellValue('L79', $cdrr_array[0]['reports_actual']);
             $logs = Cdrr_Log::getLogs($cdrr_id);
             foreach ($logs as $log) {
                 if ($log->description == "prepared") {
                     $objPHPExcel->getActiveSheet()->SetCellValue('C91', $log->user->Name);
                     $objPHPExcel->getActiveSheet()->SetCellValue('C93', $log->user->Phone_Number);
                     $objPHPExcel->getActiveSheet()->SetCellValue('O91', $log->user->Access->Level_Name);
                     $objPHPExcel->getActiveSheet()->SetCellValue('H93', $log->created);
                 } else {
                     if ($log->description == "approved") {
                         $objPHPExcel->getActiveSheet()->SetCellValue('C96', $log->s_user->name);
                         $objPHPExcel->getActiveSheet()->SetCellValue('C99', $log->user->Phone_Number);
                         $objPHPExcel->getActiveSheet()->SetCellValue('O96', $log->user->Access->Level_Name);
                         $objPHPExcel->getActiveSheet()->SetCellValue('H99', $log->created);
                     }
                 }
             }
         } else {
             $logs = Cdrr_Log::getLogs($cdrr_id);
             foreach ($logs as $log) {
                 if ($log->description == "prepared") {
                     $objPHPExcel->getActiveSheet()->SetCellValue('C86', $log->user->Name);
                     $objPHPExcel->getActiveSheet()->SetCellValue('C88', $log->user->Phone_Number);
                     $objPHPExcel->getActiveSheet()->SetCellValue('M86', $log->user->Access->Level_Name);
                     $objPHPExcel->getActiveSheet()->SetCellValue('H88', $log->created);
                 } else {
                     if ($log->description == "approved") {
                         $objPHPExcel->getActiveSheet()->SetCellValue('C90', $log->user->Name);
                         $objPHPExcel->getActiveSheet()->SetCellValue('C93', $log->user->Phone_Number);
                         $objPHPExcel->getActiveSheet()->SetCellValue('M90', $log->user->Access->Level_Name);
                         $objPHPExcel->getActiveSheet()->SetCellValue('H93', $log->created);
                     }
                 }
             }
         }
         //Generate file
         ob_start();
         $facility_name = str_replace(array("/", "'"), " ", $cdrr_array[0]['facility_name']);
         $original_filename = $cdrr_array[0]['cdrr_label'] . " " . $facility_name . " " . $cdrr_array[0]['period_begin'] . " to " . $cdrr_array[0]['period_end'] . ".xlsx";
         $filename = $dir . "/" . urldecode($original_filename);
         $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
         $objWriter->save($filename);
         $objPHPExcel->disconnectWorksheets();
         unset($objPHPExcel);
         if (file_exists($filename)) {
             $filename = str_replace("#", "%23", $filename);
             redirect($filename);
         }
     } else {
         if ($type == "maps") {
             $fmaps_id = $id;
             $fmaps_array = array();
             $dir = "Export";
             $sql = "SELECT m.*,mi.*,ml.*,f.*,co.county as county_name,d.name as district_name,u.*,al.level_name,IF(m.code='D-MAPS',CONCAT('D-MAPS#',m.id),CONCAT('F-MAPS#',m.id)) as maps_id,m.status as status_name,sf.name as facility_name,m.id as map_id\n\t\t\t \tFROM maps m\n\t\t\t \tLEFT JOIN maps_item mi ON mi.maps_id=m.id\n\t\t\t \tLEFT JOIN maps_log ml ON ml.maps_id=m.id\n\t\t\t \tLEFT JOIN sync_facility sf ON sf.id=m.facility_id\n\t\t\t \tLEFT JOIN facilities f ON f.facilitycode=sf.code\t\n\t\t\t \tLEFT JOIN counties co ON co.id=f.county\n\t\t\t\tLEFT JOIN district d ON d.id=f.district\n\t\t\t\tLEFT JOIN users u ON u.id=ml.user_id\n\t\t\t\tLEFT JOIN access_level al ON al.id=u.Access_Level\n\t\t\t\tWHERE m.id = '{$fmaps_id}'";
             $query = $this->db->query($sql);
             $fmaps_array = $query->result_array();
             $report_type = $fmaps_array[0]['code'];
             //Load download template
             $template = "";
             if ($report_type == "D-MAPS") {
                 $template = "new_fmaps_aggregate.xlsx";
             } else {
                 $template = "new_fmaps_satellite_standalone.xlsx";
             }
             $inputFileName = $_SERVER['DOCUMENT_ROOT'] . '/ADT/assets/' . $template;
             $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
             $objReader = PHPExcel_IOFactory::createReader($inputFileType);
             $objPHPExcel = $objReader->load($inputFileName);
             /*Delete all files in export folder*/
             if (is_dir($dir)) {
                 $files = scandir($dir);
                 foreach ($files as $object) {
                     if ($object != "." && $object != "..") {
                         unlink($dir . "/" . $object);
                     }
                 }
             } else {
                 mkdir($dir);
             }
             //Top menu
             $objPHPExcel->getActiveSheet()->SetCellValue('C4', $fmaps_array[0]['facility_name']);
             $objPHPExcel->getActiveSheet()->SetCellValue('C5', $fmaps_array[0]['county_name']);
             $objPHPExcel->getActiveSheet()->SetCellValue('D7', date('d/m/Y', strtotime($fmaps_array[0]['period_begin'])));
             $objPHPExcel->getActiveSheet()->SetCellValue('G4', $fmaps_array[0]['facilitycode']);
             $objPHPExcel->getActiveSheet()->SetCellValue('G5', $fmaps_array[0]['district_name']);
             //Sub_county
             $objPHPExcel->getActiveSheet()->SetCellValue('G7', date('d/m/Y', strtotime($fmaps_array[0]['period_end'])));
             //Regimen columns
             $arr = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
             //First column
             for ($i = 14; $i <= 66; $i++) {
                 if (!in_array($i, array(23, 31, 37, 49, 55, 61))) {
                     $regimen_code = $arr[$i]['B'];
                     $regimen_desc = $arr[$i]['C'];
                     $key = $this->getMappedRegimen($regimen_code, $regimen_desc);
                     if ($key !== null) {
                         foreach ($fmaps_array as $fmaps_item) {
                             if ($key == $fmaps_item['regimen_id']) {
                                 $objPHPExcel->getActiveSheet()->SetCellValue('D' . $i, $fmaps_item['total']);
                             }
                         }
                     }
                 }
             }
             //Second column
             for ($i = 14; $i <= 66; $i++) {
                 if (!in_array($i, array(25, 32, 33, 39, 43, 44, 45, 46, 51, 54, 61, 62, 63, 64, 65, 66))) {
                     $regimen_code = $arr[$i]['F'];
                     $regimen_desc = $arr[$i]['G'];
                     $key = $this->getMappedRegimen($regimen_code, $regimen_desc);
                     if ($key !== null) {
                         foreach ($fmaps_array as $fmaps_item) {
                             if ($key == $fmaps_item['regimen_id']) {
                                 $objPHPExcel->getActiveSheet()->SetCellValue('H' . $i, $fmaps_item['total']);
                             }
                         }
                     }
                 }
             }
             //If order has changed status, check who prepared the order
             $logs = Maps_Log::getMapLogs($fmaps_id);
             if ($report_type == "D-MAPS") {
                 $objPHPExcel->getActiveSheet()->SetCellValue('D69', $fmaps_array[0]['reports_expected']);
                 $objPHPExcel->getActiveSheet()->SetCellValue('H69', $fmaps_array[0]['reports_actual']);
                 foreach ($logs as $log) {
                     if ($log->description == "prepared") {
                         $objPHPExcel->getActiveSheet()->SetCellValue('C73', $log->user->Name);
                         $objPHPExcel->getActiveSheet()->SetCellValue('C76', $log->created);
                         $objPHPExcel->getActiveSheet()->SetCellValue('C77', $log->user->Access->Level_Name);
                         $objPHPExcel->getActiveSheet()->SetCellValue('C78', $log->user->Phone_Number);
                     } else {
                         if ($log->description == "approved") {
                             $objPHPExcel->getActiveSheet()->SetCellValue('G73', $log->user->Name);
                             $objPHPExcel->getActiveSheet()->SetCellValue('G76', $log->created);
                             $objPHPExcel->getActiveSheet()->SetCellValue('G77', $log->user->Access->Level_Name);
                             $objPHPExcel->getActiveSheet()->SetCellValue('G78', $log->user->Phone_Number);
                         }
                     }
                 }
             } else {
                 foreach ($logs as $log) {
                     if ($log->description == "prepared") {
                         $objPHPExcel->getActiveSheet()->SetCellValue('C69', $log->user->Name);
                         $objPHPExcel->getActiveSheet()->SetCellValue('C72', $log->created);
                         $objPHPExcel->getActiveSheet()->SetCellValue('C73', $log->user->Access->Level_Name);
                         $objPHPExcel->getActiveSheet()->SetCellValue('C74', $log->user->Phone_Number);
                     } else {
                         if ($log->description == "approved") {
                             $objPHPExcel->getActiveSheet()->SetCellValue('G69', $log->user->Name);
                             $objPHPExcel->getActiveSheet()->SetCellValue('G72', $log->created);
                             $objPHPExcel->getActiveSheet()->SetCellValue('G73', $log->user->Access->Level_Name);
                             $objPHPExcel->getActiveSheet()->SetCellValue('G74', $log->user->Phone_Number);
                         }
                     }
                 }
             }
             //Generate file
             ob_start();
             $facility_name = str_replace(array("/", "'"), " ", $fmaps_array[0]['facility_name']);
             $original_filename = $fmaps_array[0]['maps_id'] . " " . $facility_name . " " . $fmaps_array[0]['period_begin'] . " to " . $fmaps_array[0]['period_end'] . ".xlsx";
             $original_filename = str_replace('/', '-', $original_filename);
             $filename = $dir . "/" . urldecode($original_filename);
             $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
             $objWriter->save($filename);
             $objPHPExcel->disconnectWorksheets();
             unset($objPHPExcel);
             if (file_exists($filename)) {
                 $filename = str_replace("#", "%23", $filename);
                 redirect($filename);
             }
         }
     }
 }
예제 #2
0
 public function aggregate_download($period_begin, $facility_id, $cdrr_id, $fmaps_id, $facility_code)
 {
     $this->load->library('PHPExcel');
     $dir = "Export";
     $template = "order_merge";
     $inputFileType = 'Excel5';
     $inputFileName = $_SERVER['DOCUMENT_ROOT'] . '/ADT/assets/' . $template . '.xls';
     $objReader = PHPExcel_IOFactory::createReader($inputFileType);
     $objPHPExcel = $objReader->load($inputFileName);
     //get satellite facilities
     $central_site = array('id' => $facility_id);
     $satellites = Sync_Facility::getSatellitesDetails($central_site['id']);
     $details = Facilities::getCodeFacility($facility_code);
     $facility_name = $details->name;
     $district = $details->Parent_District->Name;
     //1.0 set worksheet index for cdrrs
     $objWorksheet = $objPHPExcel->setActiveSheetIndex(0);
     $highestColumm = $objPHPExcel->setActiveSheetIndex(0)->getHighestColumn();
     $highestRow = $objPHPExcel->setActiveSheetIndex(0)->getHighestRow();
     $arr = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
     $drug_count = 15;
     $drug_gap = 8;
     //range of satellites letters
     $start_column = 'F';
     $columns = array($start_column);
     $current = $start_column;
     while ($current != $highestColumm) {
         $columns[] = ++$current;
     }
     //loop through the drugs
     while ($drug_count <= $highestRow) {
         $drug_name = trim($arr[$drug_count]['A']);
         //drug exceptions that drug_id cannot be found
         $exceptions = array('Abacavir (ABC) liquid 20mg/ml' => 20, 'Lamivudine (3TC) liquid 10mg/ml' => 26, 'Lopinavir/ritonavir (LPV/r) liquid 80/20mg/ml' => 28, 'Nevirapine (NVP) Susp 10mg/ml' => 30, 'Nevirapine (NVP) Susp 10mg/ml (For PMTCT only)' => 141, 'Zidovudine (AZT) liquid 10mg/ml' => 35, 'Cotrimoxazole Suspension 240mg/5ml' => 38, 'Diflucan Suspension 50mg/5ml' => 41, 'Amphotericin B 50mg IV Injection' => 45);
         $pack_size = (int) str_ireplace(array('Packs', 'of', 'tablets', 'Bottle', 'ml', 'capsules', 'Tablets', 'Pack', 'Vials'), array(''), trim($arr[$drug_count]['B']));
         $drug_id = $this->getMappedDrug($drug_name, $pack_size);
         //get drug_id for exception drugs
         if (array_key_exists($drug_name, $exceptions)) {
             $drug_id = $exceptions[$drug_name];
         }
         //if drug_id is not null
         if ($drug_id != null) {
             //loop through satellite facilities
             foreach ($satellites as $index => $satellite) {
                 //write satellite name and level
                 $objPHPExcel->getActiveSheet()->SetCellValue($columns[$index] . '10', $satellite['name']);
                 $pos = stripos($satellite['keph_level'], "Level");
                 if ($pos !== false) {
                     $level = str_ireplace(array('Level'), array(''), $satellite['keph_level']);
                 } else {
                     $level = '';
                 }
                 $objPHPExcel->getActiveSheet()->SetCellValue($columns[$index] . '11', $level);
                 $facility_id = $satellite['id'];
                 //query to pull data about the drug
                 $sql = "SELECT *\n\t\t\t\t\t\t\tFROM cdrr c\n\t\t\t\t\t\t\tLEFT JOIN cdrr_item ci ON ci.cdrr_id=c.id\n\t\t\t\t\t\t\tWHERE c.facility_id='{$facility_id}'\n\t\t\t\t\t\t\tAND c.period_begin='{$period_begin}'\n\t\t\t\t\t\t\tAND ci.drug_id='{$drug_id}'\n\t\t\t\t\t\t\tORDER BY c.id desc\n\t\t\t\t\t\t\tLIMIT 1";
                 $query = $this->db->query($sql);
                 $orders = $query->result_array();
                 foreach ($orders as $order) {
                     //loop through order transactions and write them to excel
                     $objPHPExcel->getActiveSheet()->SetCellValue($columns[$index] . $drug_count, $order['balance']);
                     $objPHPExcel->getActiveSheet()->SetCellValue($columns[$index] . ($drug_count + 1), $order['received']);
                     $objPHPExcel->getActiveSheet()->SetCellValue($columns[$index] . ($drug_count + 2), $order['dispensed_units']);
                     $objPHPExcel->getActiveSheet()->SetCellValue($columns[$index] . ($drug_count + 3), $order['losses']);
                     $objPHPExcel->getActiveSheet()->SetCellValue($columns[$index] . ($drug_count + 4), $order['adjustments']);
                     $objPHPExcel->getActiveSheet()->SetCellValue($columns[$index] . ($drug_count + 5), $order['count']);
                     $objPHPExcel->getActiveSheet()->SetCellValue($columns[$index] . ($drug_count + 6), $order['out_of_stock']);
                     $objPHPExcel->getActiveSheet()->SetCellValue($columns[$index] . ($drug_count + 7), $order['resupply']);
                 }
             }
         }
         $drug_count += $drug_gap;
     }
     //write to file(B5-central site name,C7-period_begin,J7-period_end)
     $objPHPExcel->getActiveSheet()->SetCellValue('B5', $facility_name);
     $objPHPExcel->getActiveSheet()->SetCellValue('J5', $district);
     $objPHPExcel->getActiveSheet()->SetCellValue('C7', date('d/m/Y', strtotime($period_begin)));
     $objPHPExcel->getActiveSheet()->SetCellValue('J7', date('t/m/Y', strtotime($period_begin)));
     //2.0 set worksheet index for maps
     $objWorksheet = $objPHPExcel->setActiveSheetIndex(1);
     $highestColumm = $objPHPExcel->setActiveSheetIndex(1)->getHighestColumn();
     $highestRow = $objPHPExcel->setActiveSheetIndex(1)->getHighestRow();
     $arr = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
     $regimen_count = 16;
     //range of satellites letters
     $start_column = 'E';
     $columns = array($start_column);
     $current = $start_column;
     while ($current != $highestColumm) {
         $columns[] = ++$current;
     }
     //loop through the regimens
     while ($regimen_count < 100) {
         if ($regimen_count != 24) {
             //remove row 24
             $regimen_code = trim($arr[$regimen_count]['A']);
             if ($regimen_code != '') {
                 $regimen_id = '';
                 $regimen_id = Sync_Regimen::getId($regimen_code);
                 if ($regimen_id != "") {
                     //loop through satellite facilities
                     foreach ($satellites as $index => $satellite) {
                         //write satellite name and level
                         $objPHPExcel->getActiveSheet()->SetCellValue($columns[$index] . '10', $satellite['name']);
                         $pos = stripos($satellite['keph_level'], "Level");
                         if ($pos !== false) {
                             $level = str_ireplace(array('Level'), array(''), $satellite['keph_level']);
                         } else {
                             $level = '';
                         }
                         $objPHPExcel->getActiveSheet()->SetCellValue($columns[$index] . '11', $level);
                         $facility_id = $satellite['id'];
                         //query to pull data about the regimen
                         $sql = "SELECT *\n\t\t\t\t\t\t\t\t\t FROM maps m\n\t\t\t\t\t\t\t\t\t LEFT JOIN maps_item mi ON mi.maps_id=m.id\n\t\t\t\t\t\t\t\t\t WHERE m.facility_id='{$facility_id}'\n\t\t\t\t\t\t\t\t\t AND m.period_begin='{$period_begin}'\n\t\t\t\t\t\t\t\t\t AND mi.regimen_id='{$regimen_id}'\n\t\t\t\t\t\t\t\t\t ORDER BY m.id desc\n\t\t\t\t\t\t\t\t\t LIMIT 1";
                         $query = $this->db->query($sql);
                         $maps = $query->result_array();
                         foreach ($maps as $map) {
                             //loop through maps transactions and write them to excel
                             $objPHPExcel->getActiveSheet()->SetCellValue($columns[$index] . $regimen_count, $map['total']);
                             //write other data e.g total clients on ART only
                             $objPHPExcel->getActiveSheet()->SetCellValue($columns[$index] . '100', $map['art_adult']);
                             $objPHPExcel->getActiveSheet()->SetCellValue($columns[$index] . '101', $map['art_child']);
                             $objPHPExcel->getActiveSheet()->SetCellValue($columns[$index] . '102', $map['new_male']);
                             $objPHPExcel->getActiveSheet()->SetCellValue($columns[$index] . '103', $map['revisit_male']);
                             $objPHPExcel->getActiveSheet()->SetCellValue($columns[$index] . '104', $map['new_female']);
                             $objPHPExcel->getActiveSheet()->SetCellValue($columns[$index] . '105', $map['revisit_female']);
                             $objPHPExcel->getActiveSheet()->SetCellValue($columns[$index] . '106', $map['new_pmtct']);
                             $objPHPExcel->getActiveSheet()->SetCellValue($columns[$index] . '107', $map['revisit_pmtct']);
                             $objPHPExcel->getActiveSheet()->SetCellValue($columns[$index] . '108', $map['total_adult']);
                             $objPHPExcel->getActiveSheet()->SetCellValue($columns[$index] . '109', $map['total_child']);
                             $objPHPExcel->getActiveSheet()->SetCellValue($columns[$index] . '110', $map['diflucan_adult']);
                             $objPHPExcel->getActiveSheet()->SetCellValue($columns[$index] . '111', $map['diflucan_child']);
                         }
                     }
                 }
             }
         }
         $regimen_count++;
     }
     //set facility name on regimen sheet
     $objPHPExcel->getActiveSheet()->SetCellValue('B5', $facility_name);
     $objPHPExcel->getActiveSheet()->SetCellValue('I5', $district);
     //3.0 set worksheet index for D-CDRR
     $objWorksheet = $objPHPExcel->setActiveSheetIndex(2);
     $highestColumm = $objPHPExcel->setActiveSheetIndex(2)->getHighestColumn();
     $highestRow = $objPHPExcel->setActiveSheetIndex(2)->getHighestRow();
     $arr = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
     //D-CDRR
     $drug_name = "CONCAT_WS('] ',CONCAT_WS(' [',sd.name,sd.abbreviation),CONCAT_WS(' ',sd.strength,sd.formulation)) as drug_map";
     $sql = "SELECT c.*,ci.*,cl.*,f.*,co.county as county_name,d.name as district_name,u.*,al.level_name,IF(c.code='D-CDRR',CONCAT('D-CDRR#',c.id),CONCAT('F-CDRR#',c.id)) as cdrr_label,c.status as status_name,sf.name as facility_name,{$drug_name}\n\t\t\t\tFROM cdrr c\n\t\t\t\tLEFT JOIN cdrr_item ci ON ci.cdrr_id=c.id\n\t\t\t\tLEFT JOIN cdrr_log cl ON cl.cdrr_id=c.id\n\t\t\t\tLEFT JOIN sync_facility sf ON sf.id=c.facility_id\n\t\t\t\tLEFT JOIN facilities f ON f.facilitycode=sf.code\n\t\t\t\tLEFT JOIN counties co ON co.id=f.county\n\t\t\t\tLEFT JOIN district d ON d.id=f.district\n\t\t\t\tLEFT JOIN sync_user su ON su.id=cl.user_id\n\t\t\t\tLEFT JOIN users u ON su.id=u.map\n\t\t\t\tLEFT JOIN access_level al ON al.id=u.Access_Level\n\t\t\t\tLEFT JOIN sync_drug sd ON sd.id=ci.drug_id\n\t\t\t\tLEFT JOIN drugcode dc ON dc.map=sd.id\n\t\t\t\tWHERE c.id='{$cdrr_id}'";
     $query = $this->db->query($sql);
     $cdrr_array = $query->result_array();
     $objPHPExcel->getActiveSheet()->SetCellValue('C4', $cdrr_array[0]['name']);
     $objPHPExcel->getActiveSheet()->SetCellValue('G4', $cdrr_array[0]['facilitycode']);
     $objPHPExcel->getActiveSheet()->SetCellValue('C5', $cdrr_array[0]['county_name']);
     $objPHPExcel->getActiveSheet()->SetCellValue('G5', $cdrr_array[0]['district_name']);
     $objPHPExcel->getActiveSheet()->SetCellValue('D7', date('d/m/y', strtotime($cdrr_array[0]['period_begin'])));
     $objPHPExcel->getActiveSheet()->SetCellValue('G7', date('d/m/y', strtotime($cdrr_array[0]['period_end'])));
     if ($cdrr_array[0]['sponsors'] != "") {
         if (strtoupper($cdrr_array[0]['sponsors']) == "GOK") {
             $loc = "D";
         } else {
             if (strtoupper($cdrr_array[0]['sponsors']) == "PEPFAR") {
                 $loc = "F";
             } else {
                 if (strtoupper($cdrr_array[0]['sponsors']) == "MSF") {
                     $loc = "H";
                 }
             }
         }
         $objPHPExcel->getActiveSheet()->SetCellValue($loc . '9', "X");
     }
     $services = explode(",", $cdrr_array[0]['services']);
     if ($services != "") {
         foreach ($services as $service) {
             if (strtoupper($service) == "ART") {
                 $objPHPExcel->getActiveSheet()->SetCellValue('D11', "X");
             } else {
                 if (strtoupper($service) == "PMTCT") {
                     $objPHPExcel->getActiveSheet()->SetCellValue('F11', "X");
                 } else {
                     if (strtoupper($service) == "PEP") {
                         $objPHPExcel->getActiveSheet()->SetCellValue('H11', "X");
                     }
                 }
             }
         }
     }
     $objPHPExcel->getActiveSheet()->SetCellValue('A95', $cdrr_array[0]['comments']);
     $arr = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
     for ($i = 18; $i <= 93; $i++) {
         $drug = $arr[$i]['A'];
         $pack_size = $arr[$i]['B'];
         if ($drug) {
             $key = $this->getMappedDrug($drug, $pack_size);
             if ($key !== null) {
                 foreach ($cdrr_array as $cdrr_item) {
                     if ($key == $cdrr_item['drug_id']) {
                         if ($cdrr_array[0]['code'] == "F-CDRR_packs") {
                             $objPHPExcel->getActiveSheet()->SetCellValue('C' . $i, $cdrr_item['balance']);
                             $objPHPExcel->getActiveSheet()->SetCellValue('D' . $i, $cdrr_item['received']);
                             $objPHPExcel->getActiveSheet()->SetCellValue('E' . $i, $cdrr_item['dispensed_units']);
                             $objPHPExcel->getActiveSheet()->SetCellValue('F' . $i, $cdrr_item['dispensed_packs']);
                             $objPHPExcel->getActiveSheet()->SetCellValue('G' . $i, $cdrr_item['losses']);
                             $objPHPExcel->getActiveSheet()->SetCellValue('H' . $i, $cdrr_item['adjustments']);
                             $objPHPExcel->getActiveSheet()->SetCellValue('I' . $i, $cdrr_item['count']);
                             $objPHPExcel->getActiveSheet()->SetCellValue('J' . $i, $cdrr_item['expiry_quant']);
                             $objPHPExcel->getActiveSheet()->SetCellValue('K' . $i, $cdrr_item['expiry_date']);
                             $objPHPExcel->getActiveSheet()->SetCellValue('L' . $i, $cdrr_item['out_of_stock']);
                             $objPHPExcel->getActiveSheet()->SetCellValue('M' . $i, $cdrr_item['resupply']);
                         } else {
                             $objPHPExcel->getActiveSheet()->SetCellValue('C' . $i, $cdrr_item['balance']);
                             $objPHPExcel->getActiveSheet()->SetCellValue('D' . $i, $cdrr_item['received']);
                             $objPHPExcel->getActiveSheet()->SetCellValue('E' . $i, $cdrr_item['dispensed_units']);
                             $objPHPExcel->getActiveSheet()->SetCellValue('F' . $i, $cdrr_item['losses']);
                             $objPHPExcel->getActiveSheet()->SetCellValue('G' . $i, $cdrr_item['adjustments']);
                             $objPHPExcel->getActiveSheet()->SetCellValue('H' . $i, $cdrr_item['count']);
                             if ($cdrr_array[0]['code'] == "D-CDRR") {
                                 $objPHPExcel->getActiveSheet()->SetCellValue('I' . $i, $cdrr_item['aggr_consumed']);
                                 $objPHPExcel->getActiveSheet()->SetCellValue('J' . $i, $cdrr_item['aggr_on_hand']);
                                 $objPHPExcel->getActiveSheet()->SetCellValue('K' . $i, $cdrr_item['expiry_quant']);
                                 $objPHPExcel->getActiveSheet()->SetCellValue('L' . $i, $cdrr_item['expiry_date']);
                                 $objPHPExcel->getActiveSheet()->SetCellValue('M' . $i, $cdrr_item['out_of_stock']);
                                 $objPHPExcel->getActiveSheet()->SetCellValue('N' . $i, $cdrr_item['resupply']);
                             } else {
                                 $objPHPExcel->getActiveSheet()->SetCellValue('I' . $i, $cdrr_item['expiry_quant']);
                                 $objPHPExcel->getActiveSheet()->SetCellValue('J' . $i, $cdrr_item['expiry_date']);
                                 $objPHPExcel->getActiveSheet()->SetCellValue('K' . $i, $cdrr_item['out_of_stock']);
                                 $objPHPExcel->getActiveSheet()->SetCellValue('L' . $i, $cdrr_item['resupply']);
                             }
                         }
                     }
                 }
             }
         }
     }
     $objPHPExcel->getActiveSheet()->SetCellValue('E108', $cdrr_array[0]['reports_expected']);
     $objPHPExcel->getActiveSheet()->SetCellValue('H108', $cdrr_array[0]['reports_actual']);
     $logs = Cdrr_Log::getLogs($cdrr_id);
     foreach ($logs as $log) {
         if ($log->description == "prepared") {
             $objPHPExcel->getActiveSheet()->SetCellValue('C111', $log->s_user->name);
             $objPHPExcel->getActiveSheet()->SetCellValue('C113', 'N/A');
             $objPHPExcel->getActiveSheet()->SetCellValue('K111', $log->s_user->role);
             $objPHPExcel->getActiveSheet()->SetCellValue('G113', $log->created);
         } else {
             if ($log->description == "approved") {
                 $objPHPExcel->getActiveSheet()->SetCellValue('C115', $log->s_user->name);
                 $objPHPExcel->getActiveSheet()->SetCellValue('C117', 'N/A');
                 $objPHPExcel->getActiveSheet()->SetCellValue('K115', $log->s_user->role);
                 $objPHPExcel->getActiveSheet()->SetCellValue('G117', $log->created);
             }
         }
     }
     //4.0 set worksheet index for D-MAPS
     $objWorksheet = $objPHPExcel->setActiveSheetIndex(3);
     $highestColumm = $objPHPExcel->setActiveSheetIndex(3)->getHighestColumn();
     $highestRow = $objPHPExcel->setActiveSheetIndex(3)->getHighestRow();
     $arr = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
     //D-MAPS
     $sql = "SELECT m.*,mi.*,ml.*,f.*,co.county as county_name,d.name as district_name,u.*,al.level_name,IF(m.code='D-MAPS',CONCAT('D-MAPS#',m.id),CONCAT('F-MAPS#',m.id)) as maps_id,m.status as status_name,sf.name as facility_name,m.id as map_id\n\t\t\t \tFROM maps m\n\t\t\t \tLEFT JOIN maps_item mi ON mi.maps_id=m.id\n\t\t\t \tLEFT JOIN maps_log ml ON ml.maps_id=m.id\n\t\t\t \tLEFT JOIN sync_facility sf ON sf.id=m.facility_id\n\t\t\t \tLEFT JOIN facilities f ON f.facilitycode=sf.code\t\n\t\t\t \tLEFT JOIN counties co ON co.id=f.county\n\t\t\t\tLEFT JOIN district d ON d.id=f.district\n\t\t\t\tLEFT JOIN users u ON u.map=ml.user_id\n\t\t\t\tLEFT JOIN access_level al ON al.id=u.Access_Level\n\t\t\t\tWHERE m.id='{$fmaps_id}'";
     $query = $this->db->query($sql);
     $fmaps_array = $query->result_array();
     $objPHPExcel->getActiveSheet()->SetCellValue('B4', $fmaps_array[0]['facility_name']);
     $objPHPExcel->getActiveSheet()->SetCellValue('F4', $fmaps_array[0]['facilitycode']);
     $objPHPExcel->getActiveSheet()->SetCellValue('B5', $fmaps_array[0]['county_name']);
     $objPHPExcel->getActiveSheet()->SetCellValue('F5', $fmaps_array[0]['district_name']);
     $objPHPExcel->getActiveSheet()->SetCellValue('D7', date('d/m/y', strtotime($fmaps_array[0]['period_begin'])));
     $objPHPExcel->getActiveSheet()->SetCellValue('G7', date('d/m/y', strtotime($fmaps_array[0]['period_end'])));
     if (strtoupper($fmaps_array[0]['sponsors']) == "GOK") {
         $loc = "D";
     } else {
         if (strtoupper($fmaps_array[0]['sponsors']) == "PEPFAR") {
             $loc = "F";
         } else {
             if (strtoupper($fmaps_array[0]['sponsors']) == "MSF") {
                 $loc = "H";
             }
         }
     }
     $objPHPExcel->getActiveSheet()->SetCellValue($loc . '9', "X");
     $services = explode(",", $fmaps_array[0]['services']);
     foreach ($services as $service) {
         if (strtoupper($service) == "ART") {
             $objPHPExcel->getActiveSheet()->SetCellValue('D11', "X");
         } else {
             if (strtoupper($service) == "PMTCT") {
                 $objPHPExcel->getActiveSheet()->SetCellValue('F11', "X");
             } else {
                 if (strtoupper($service) == "PEP") {
                     $objPHPExcel->getActiveSheet()->SetCellValue('H11', "X");
                 }
             }
         }
     }
     $objPHPExcel->getActiveSheet()->SetCellValue('D14', $fmaps_array[0]['art_adult']);
     $objPHPExcel->getActiveSheet()->SetCellValue('F14', $fmaps_array[0]['art_child']);
     $objPHPExcel->getActiveSheet()->SetCellValue('D18', $fmaps_array[0]['new_male']);
     $objPHPExcel->getActiveSheet()->SetCellValue('E18', $fmaps_array[0]['revisit_male']);
     $objPHPExcel->getActiveSheet()->SetCellValue('F18', $fmaps_array[0]['new_female']);
     $objPHPExcel->getActiveSheet()->SetCellValue('G18', $fmaps_array[0]['revisit_female']);
     $objPHPExcel->getActiveSheet()->SetCellValue('H26', $fmaps_array[0]['new_pmtct']);
     $objPHPExcel->getActiveSheet()->SetCellValue('H27', $fmaps_array[0]['revisit_pmtct']);
     $objPHPExcel->getActiveSheet()->SetCellValue('H38', $fmaps_array[0]['total_infant']);
     $objPHPExcel->getActiveSheet()->SetCellValue('H107', $fmaps_array[0]['pep_adult']);
     $objPHPExcel->getActiveSheet()->SetCellValue('H108', $fmaps_array[0]['pep_child']);
     if ($report_type != "D-MAPS") {
         $objPHPExcel->getActiveSheet()->SetCellValue('E124', $fmaps_array[0]['total_adult']);
         $objPHPExcel->getActiveSheet()->SetCellValue('G124', $fmaps_array[0]['total_child']);
         $objPHPExcel->getActiveSheet()->SetCellValue('E128', $fmaps_array[0]['diflucan_adult']);
         $objPHPExcel->getActiveSheet()->SetCellValue('G128', $fmaps_array[0]['diflucan_child']);
         $objPHPExcel->getActiveSheet()->SetCellValue('D134', $fmaps_array[0]['new_cm']);
         $objPHPExcel->getActiveSheet()->SetCellValue('E134', $fmaps_array[0]['revisit_cm']);
         $objPHPExcel->getActiveSheet()->SetCellValue('F134', $fmaps_array[0]['new_oc']);
         $objPHPExcel->getActiveSheet()->SetCellValue('G134', $fmaps_array[0]['revisit_oc']);
         $objPHPExcel->getActiveSheet()->SetCellValue('D138', $fmaps_array[0]['reports_expected']);
         $objPHPExcel->getActiveSheet()->SetCellValue('G138', $fmaps_array[0]['reports_actual']);
     } else {
         $objPHPExcel->getActiveSheet()->SetCellValue('E164', $fmaps_array[0]['total_adult']);
         $objPHPExcel->getActiveSheet()->SetCellValue('G164', $fmaps_array[0]['total_child']);
         $objPHPExcel->getActiveSheet()->SetCellValue('E168', $fmaps_array[0]['diflucan_adult']);
         $objPHPExcel->getActiveSheet()->SetCellValue('G168', $fmaps_array[0]['diflucan_child']);
         $objPHPExcel->getActiveSheet()->SetCellValue('D174', $fmaps_array[0]['new_cm']);
         $objPHPExcel->getActiveSheet()->SetCellValue('E174', $fmaps_array[0]['revisit_cm']);
         $objPHPExcel->getActiveSheet()->SetCellValue('F174', $fmaps_array[0]['new_oc']);
         $objPHPExcel->getActiveSheet()->SetCellValue('G174', $fmaps_array[0]['revisit_oc']);
     }
     $arr = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
     for ($i = 25; $i <= 120; $i++) {
         if ($i == 36 || $i == 43 || $i == 53 || $i == 68 || $i == 75 || $i == 88 || $i == 99 || $i == 105 || $i == 113) {
             continue;
         }
         $regimen_code = $arr[$i]['A'];
         $regimen_desc = $arr[$i]['B'];
         $key = $this->getMappedRegimen($regimen_code, $regimen_desc);
         if ($key !== null) {
             foreach ($fmaps_array as $fmaps_item) {
                 if ($key == $fmaps_item['regimen_id']) {
                     $objPHPExcel->getActiveSheet()->SetCellValue('E' . $i, $fmaps_item['total']);
                 }
             }
         }
     }
     //If order has changed status, check who prepared the order
     $logs = Maps_Log::getMapLogs($fmaps_id);
     foreach ($logs as $log) {
         if ($log->description == "prepared") {
             $objPHPExcel->getActiveSheet()->SetCellValue('B141', $log->s_user->name);
             $objPHPExcel->getActiveSheet()->SetCellValue('B143', 'N/A');
             $objPHPExcel->getActiveSheet()->SetCellValue('G141', $log->s_user->role);
             $objPHPExcel->getActiveSheet()->SetCellValue('E143', $log->created);
         } else {
             if ($log->description == "approved") {
                 $objPHPExcel->getActiveSheet()->SetCellValue('B145', $log->s_user->name);
                 $objPHPExcel->getActiveSheet()->SetCellValue('B147', 'N/A');
                 $objPHPExcel->getActiveSheet()->SetCellValue('G145', $log->s_user->role);
                 $objPHPExcel->getActiveSheet()->SetCellValue('E147', $log->created);
             }
         }
     }
     //Delete all files in export folder
     if (is_dir($dir)) {
         $files = scandir($dir);
         foreach ($files as $object) {
             if ($object != "." && $object != "..") {
                 unlink($dir . "/" . $object);
             }
         }
     } else {
         mkdir($dir);
     }
     //Generate file
     ob_start();
     $file = "AGGR#" . date('Ym', strtotime($period_begin)) . ".xlsx";
     $filename = $dir . "/" . urldecode($file);
     $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
     $objWriter->save($filename);
     $objPHPExcel->disconnectWorksheets();
     unset($objPHPExcel);
     if (file_exists($filename)) {
         $filename = str_replace("#", "%23", $filename);
         redirect($filename);
     }
 }