public function listing()
{
$access_level = $this->session->userdata('user_indicator');
$source = 0;
if ($access_level == "pharmacist") {
$source = $this->session->userdata('facility');
}
$data = array();
$data['content_view'] = "regimen_listing_v";
$data['styles'] = array("jquery-ui.css");
$data['scripts'] = array("jquery-ui.js");
$regimens = Regimen::getAllHydrated($source, $access_level);
$tmpl = array('table_open' => '<table id="regimen_setting" class="table table-bordered table-hover table-striped setting_table">');
$this->table->set_template($tmpl);
$this->table->set_heading('id', 'Regimen', 'Line', 'Regimen Category', 'Type Of Service', 'Options');
foreach ($regimens as $regimen) {
$links = "";
$drug = $regimen['id'];
$type_of_service = $regimen['Regimen_Service_Type'];
//if($type_of_service!="ART" && $access_level!="system_administrator"){
if ($access_level != "facility_administrator") {
$array_param = array('id' => $regimen['id'], 'role' => 'button', 'class' => 'edit_user', 'data-toggle' => 'modal');
if ($regimen['Enabled'] == 1) {
//$links .= anchor('' . $regimen['id'], 'Edit', array('class' => 'edit_user','id'=>$regimen['id']));
$links .= anchor('#edit_form', 'Edit', $array_param);
}
} elseif ($access_level == "facility_administrator") {
//href="#entry_form" role="button" id="new_regimen" class="btn" data-toggle="modal"
$array_param = array('id' => $regimen['id'], 'role' => 'button', 'class' => 'edit_user', 'data-toggle' => 'modal');
if ($regimen['Enabled'] == 1) {
$links .= anchor('#edit_form', 'Edit', $array_param);
}
}
if ($regimen['Enabled'] == 1 && @$regimen['Merged_To']) {
$links .= " | ";
$links .= anchor('regimen_management/disable/' . $regimen['id'], 'Disable', array('class' => 'disable_user actual'));
}
if ($regimen['Enabled'] == 1 && @$regimen['Merged_To'] == "" && $access_level == "facility_administrator") {
$links .= " | ";
$links .= anchor('regimen_management/disable/' . $regimen['id'], 'Disable', array('class' => 'disable_user actual'));
$links .= " | ";
$links .= "<a href='#' class='merge_drug' id='{$drug}'>Merge</a>";
}
if ($regimen['Enabled'] == 0 && $access_level == "facility_administrator") {
$links .= anchor('regimen_management/enable/' . $regimen['id'], 'Enable', array('class' => 'enable_user actual'));
}
if ($regimen['Merged_To'] != '') {
if ($access_level == "facility_administrator") {
$links .= " | ";
$links .= anchor('regimen_management/unmerge/' . $regimen['id'], 'Unmerge', array('class' => 'unmerge_drug'));
}
$checkbox = "<input type='checkbox' name='drugcodes' id='drugcodes' class='drugcodes' value='{$drug}' disabled/>";
} else {
$checkbox = "<input type='checkbox' name='drugcodes' id='drugcodes' class='drugcodes' value='{$drug}'/>";
}
$mapped = "";
if ($regimen['map'] != 0) {
$mapped = "<b>(mapped)</b>";
}
if ($regimen['Regimen_Code']) {
$regimen_code = $regimen['Regimen_Code'] . " | " . $regimen['Regimen_Desc'];
} else {
$regimen_code = $regimen['Regimen_Desc'];
}
$this->table->add_row($regimen['id'], $checkbox . "" . $regimen_code . " " . $mapped, $regimen['Line'], $regimen['Regimen_Category'], $regimen['Regimen_Service_Type'], $links);
}
$data['access_level'] = $access_level;
$data['regimens'] = $this->table->generate();
$data['regimen_categories'] = Regimen_Category::getAll();
$data['regimen_service_types'] = Regimen_Service_Type::getAll();
$sql = "SELECT s.id,s.code,s.name,sr.Name as category_name,s.category_id\n FROM sync_regimen s \n LEFT JOIN sync_regimen_category sr ON sr.id = s.category_id\n WHERE s.id NOT IN(SELECT r.map\n FROM regimen r\n WHERE r.map !='0')\n OR s.name LIKE '%other%'\n OR s.code LIKE '%x%'\n ORDER BY s.category_id,s.code asc";
$query = $this->db->query($sql);
$unmapped_regimens = $query->result_array();
$sync_regimens = Sync_Regimen::getActive();
$data['edit_mappings'] = $unmapped_regimens;
$data['mappings'] = $sync_regimens;
$this->base_params($data);
}
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);
}
}
