public function ors_zinc_report()
{
//Set the current year
$year = date("Y");
$county_total = array();
$excel_data = array();
$excel_data = array('doc_creator' => "HCMP", 'doc_title' => ' stock level report ', 'file_name' => 'stock level report');
$row_data = array();
$column_data = array("County", "Sub-County", "Facility Code", "Facility Name", "Commodity Name", "Unit Size", "Unit Cost(KES)", "Supplier", "Manufacturer", "Batch Number", "Expiry Date", "Stock at Hand (units)", "Stock at Hand (packs)", "AMC (units)", "AMC (packs)", "Stock at Hand MOS(packs)", "Date Last Issued", "Days From Last Issue");
$excel_data['column_data'] = $column_data;
//the commodities variable will hold the values for the three commodities ie ORS and Zinc
$commodities = array(51, 267, 36, 456);
foreach ($commodities as $commodities) {
$commodity_stock_level = array();
//holds the data for the entire county
//once it is done executing for one commodity it will reset to zero
$commodity_total = array();
//pick the commodity names and details
//get the stock level for that commodity
$commodity_stock_level = Facility_stocks::get_commodity_stock_level($commodities);
//echo "<pre>";print_r($commodity_stock_level);exit;
//Start buliding the excel file
foreach ($commodity_stock_level as $commodity_stock_level) {
//pick the facility code from the data
$facility_code = $commodity_stock_level["facility_code"];
//get the last date of issue from the database
$date_last_issue = Facilities::get_last_issue($facility_code);
//ensure that if the date is null change the message
$date_of_last_issue = $date_last_issue[0]['Date Last Issued'] != 0 ? date('j M, Y', strtotime($date_last_issue[0]['Date Last Issued'])) : "No Data Found";
$days_since_last_issue = $date_last_issue[0]['Days From Last Issue'] != 0 ? $date_last_issue[0]['Days From Last Issue'] : 0;
array_push($row_data, array($commodity_stock_level["county"], $commodity_stock_level["subcounty"], $commodity_stock_level["facility_code"], $commodity_stock_level["facility_name"], $commodity_stock_level["commodity_name"], $commodity_stock_level["unit_size"], $commodity_stock_level["unit_cost"], $commodity_stock_level["supplier"], $commodity_stock_level["manufacture"], $commodity_stock_level["batch_no"], $commodity_stock_level["expiry_date"], $commodity_stock_level["balance_units"], $commodity_stock_level["balance_packs"], $commodity_stock_level["amc_units"], $commodity_stock_level["amc"], $commodity_stock_level["mos"], $date_of_last_issue, $days_since_last_issue));
}
//Switch statement to build on the remaining part of the message body
//get the number of facilities stocked out on a specific commodity
$no_of_stock_outs = Facility_stocks::facilities_stocked_specific_commodity($commodities);
//get the number of facilities reporting on a specific commodity
$no_of_facilities_reporting = Facility_stocks::facilities_reporting_on_a_specific_commodity($commodities);
//get the number of batches expiring within 3 months
$no_of_batches = Facility_stocks::batches_expiring_specific_commodities($commodities);
switch ($commodities) {
case 51:
$message_body .= "<p>Number of Facilities Reporting on ORS Satchets (100):" . $no_of_facilities_reporting . "</p>";
$message_body .= "<p>Number of Facilities Stocked out on ORS Satchets (100): " . $no_of_stock_outs . "</p>";
$message_body .= "<p>Number of ORS (100) Batches expiring in the next 3 months: " . $no_of_batches . "</p>";
break;
case 267:
$message_body .= "<p>Number of Facilities Reporting on ORS Satchets (50):" . $no_of_facilities_reporting . "</p>";
$message_body .= "<p>Number of Facilities Stocked out on ORS Satchets (50): " . $no_of_stock_outs . "</p>";
$message_body .= "<p>Number of ORS (50) Batches expiring in the next 3 months: " . $no_of_batches . "</p>";
break;
case 36:
$message_body .= "<p>Number of Facilities Reporting on Zinc Sulphate 20mg: " . $no_of_facilities_reporting . "</p>";
$message_body .= "<p>Number of Facilities Stocked out on Zinc Sulphate 20mg: " . $no_of_stock_outs . "</p>";
$message_body .= "<p>Number of Zinc Sulphate Batches expiring in the next 3 months: " . $no_of_batches . "</p>";
break;
case 456:
$message_body .= "<p>Number of Facilities Reporting on ORS 4 Satchets & Zinc 10 Tablets 20 Mg: " . $no_of_facilities_reporting . "</p>";
$message_body .= "<p>Number of Facilities Stocked out on ORS 4 Satchets & Zinc 10 Tablets 20 Mg: " . $no_of_stock_outs . "</p>";
$message_body .= "<p>Number of ORS 4 Satchets & Zinc 10 Tablets 20 Mg Batches expiring in the next 3 months: " . $no_of_batches . "</p>";
break;
}
}
$excel_data['row_data'] = $row_data;
$excel_data['report_type'] = "download_file";
$excel_data['file_name'] = "Stock_Level_Report";
$excel_data['excel_title'] = "Stock Level Report for Zinc sulphate Tablets 20mg and ORS sachet (for 500ml) low osmolality (100) & (50) as at " . date("jS F Y");
//Start the email section of the report
//Get the number of facilities using HCMP
$no_of_facilities = Facilities::get_all_on_HCMP();
$subject = "Stock Level Report: Zinc sulphate Tablets 20mg and ORS sachet (for 500ml) low osmolality ";
$message = "<p>Find attached an excel sheet with a Stock Level Report for \n\t \t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tZinc Sulphate 20mg, ORS sachet (for 500ml) low osmolality (100 & 50) and ORS 4 Satchets & Zinc 10 Tablets 20 Mg as at " . date("jS F Y") . "</p>";
$message .= "<p>Number of facilities using HCMP: " . $no_of_facilities . "</p>";
$message .= $message_body;
$message .= "\n\t \t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<p>You may log onto health-cmp.or.ke for follow up.</p>\n\n\t \t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<p>----</p>\n\n\t \t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<p>HCMP</p>\n\n\t \t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<p>This email was automatically generated. Please do not respond to this email address or it will be ignored.</p>";
$report_type = "ors_report";
$this->create_excel($excel_data, $report_type);
// exit;
//path for windows
$handler = "./print_docs/excel/excel_files/" . $excel_data['file_name'] . ".xls";
//path for Mac
//$handler = "/Applications/XAMPP/xamppfiles/htdocs/hcmp/print_docs/excel/excel_files/" . $excel_data['file_name'] . ".xls";
$email_address = "smutheu@clintonhealthaccess.org,jaynerawz@gmail.com";
$bcc = "karsanrichard@gmail.com,kelvinmwas@gmail.com";
$this->hcmp_functions->send_email($email_address, $message, $subject, $handler, $bcc);
}
