public function national_allocation_chart()
{
$str_xml_body = '';
// $title="";
$strXML = "<chart caption='National Allocation' yAxisName='Allocation' alternateVGridColor='AFD8F8' baseFontColor='114B78' toolTipBorderColor='114B78' toolTipBgColor='E7EFF6' useRoundEdges='1' showBorder='0' bgColor='FFFFFF,FFFFFF'>";
$allocations = rtk_stock_status::get_national_allocation();
$counties = counties::getAllcounties();
foreach ($counties as $counties_data) {
$counties_data_id = $counties_data["id"];
}
$districts = districts::getDistrict(2);
// Gets districts within county id 2
foreach ($districts as $districts_data) {
$districts_data_id = $districts_data["id"];
$districts_data_name = $districts_data["district"];
// echo $districts_data_id;
//echo $districts_data_name."<br /><br />";
$facilities = facilities::getFacilities($districts_data_id);
foreach ($facilities as $facilities_data) {
$facilities_data_id = $facilities_data["id"];
$facilities_data_code = $facilities_data["facility_code"];
$allocations = rtk_stock_status::get_county_allocation($facilities_data_code);
$sum_determine = 0;
$sum_unigold = 0;
$sum_syphillis = 0;
$commodity = "";
foreach ($allocations as $allocations_data) {
if ($allocations_data["commodity_id"] == 1) {
$commodity = "Colloidal";
$sum_unigold += $allocations_data["qty"];
}
if ($allocations_data["commodity_id"] == 2) {
$commodity = "First Response";
$sum_determine += $allocations_data["qty"];
}
if ($allocations_data["commodity_id"] == 3) {
$commodity = "Unigold";
$sum_syphillis += $allocations_data["qty"];
}
$allocations_data_commodity_id = $allocations_data["commodity_id"];
}
}
$str_xml_body .= "<set value='{$sum_determine}' label='Total Determine {$districts_data_name}'/>";
$str_xml_body .= "<set value='{$sum_unigold}' label='Reporting Unigold {$districts_data_name}'/>";
$str_xml_body .= "<set value='{$sum_syphillis}' label='Sum Syphillis {$districts_data_name}'/>";
}
echo $str_xml_body;
$strXML .= "{$str_xml_body}</chart>";
echo $strXML;
}
public function consumption_stats_graph($commodity_id = null, $category_id = null, $district_id = null, $facility_code = null, $option = null, $from = null, $to = null, $report_type = null, $tracer = null)
{
//reset the values here
// echo $report_type;exit;
$commodity_id = $commodity_id == "NULL" ? null : $commodity_id;
$district_id = isset($district_id) && $district_id ? $district_id : $this->session->userdata('district_id');
//$district_id = ($district_id=="NULL") ? null :$district_id;
$facility_code = $facility_code == "NULL" ? null : $facility_code;
$option = $option == "NULL" || $option == "null" ? null : $option;
// $from = ($from == "NULL") ? strtotime(date('01-m-y')) : strtotime(urldecode($from));
// $to = ($to == "NULL") ? strtotime(date('d-m-y')) : strtotime(urldecode($to));
// echo "$from";die;
$from = $from == "NULL" ? strtotime(date('Y-m-01')) : strtotime(urldecode($from));
$to = $to == "NULL" ? strtotime(date('Y-m-d')) : strtotime(urldecode($to));
$category_id = $category_id == "NULL" ? null : $category_id;
$county_id = $this->session->userdata('county_id');
$county_name = counties::get_county_name($county_id);
$category_data = $series_data = $graph_data = $series_data_ = array();
//check if the district is set
$district_data = isset($district_id) && $district_id > 0 ? districts::get_district_name($district_id)->toArray() : null;
$district_name_ = isset($district_data) ? " :" . $district_data[0]['district'] . " subcounty" : null;
$option_new = isset($option) ? $option : "packs";
$facility_code_ = isset($facility_code) ? facilities::get_facility_name_($facility_code)->toArray() : null;
$facility_name = $facility_code_[0]['facility_name'];
$commodity_name = isset($commodity_id) ? Commodities::get_details($commodity_id)->toArray() : null;
$category_name_ = @$commodity_name[0]['commodity_name'];
$commodity_name = isset($category_name_) ? " for " . $category_name_ : null;
$title = isset($facility_code) && isset($district_id) ? "{$district_name_} : {$facility_name}" : ($district_id > 0 && !isset($facility_code) ? "{$district_name_}" : "{$county_name['county']} county");
$time = "between " . date('j M y', $from) . " and " . date('j M y', $to);
$consumption_data = Facility_stocks::get_county_consumption_level_new($facility_code, $district_id, $county_id, $category_id, $commodity_id, $option_new, $from, $to, $report_type, $tracer);
// echo "<pre>";
// print_r($consumption_data);die;
foreach ($consumption_data as $data) {
if ($report_type == "table_data") {
if ($commodity_id > 0) {
array_push($series_data, array($data['district'], $data["facility_name"], $data["facility_code"], $data['commodity'], (int) $data['total']));
} else {
array_push($series_data, array($data["name"], $data['commodity'], (int) $data['total']));
}
} else {
$series_data = array_merge($series_data, array((int) $data['total']));
$series_data_ = array_merge($series_data_, array(array($data["district"], $data["facility_name"], $data['commodity_name'], (int) $data['total'])));
$category_data = array_merge($category_data, array($data["name"]));
}
//
}
if ($report_type == "csv_data") {
//seth
$excel_data = array('doc_creator' => $this->session->userdata('full_name'), 'doc_title' => "Consumption level {$commodity_name} {$title} {$time}", 'file_name' => "consumption_level_{$commodity_name_}{$title_}{$time}");
$row_data = array();
$column_data = array("District", "Facility", "Commodity", "Consumption Level in {$option_new}");
$excel_data['column_data'] = $column_data;
$row_data = array_merge($row_data, $series_data_);
//echo "<pre>";print_r($row_data);echo "</pre>";exit;
$excel_data['row_data'] = $row_data;
$this->hcmp_functions->create_excel($excel_data);
//echo $commodity_name.$title.$time;exit;
} elseif ($report_type == "table_data") {
if ($commodity_id > 0) {
$category_data = array(array("Sub-county", "Facility Name", "Mfl", "Commodity Name", "TOTAL " . $option_new));
} else {
// array_push($category_data, array("Commodity Name", "stocks worth in $option_new"));
array_push($category_data, array("Consumption level {$commodity_name} {$title} {$time}", "Commodity Name", "stocks worth in {$option_new}"));
}
$graph_data = array_merge($graph_data, array("table_id" => 'dem_graph_'));
$graph_data = array_merge($graph_data, array("table_header" => $category_data));
$graph_data = array_merge($graph_data, array("table_body" => $series_data));
$data['table'] = $this->hcmp_functions->create_data_table($graph_data);
$data['table_id'] = "dem_graph_";
return $this->load->view("shared_files/report_templates/data_table_template_v", $data);
} else {
$graph_type = 'bar';
// $graph_type = 'column';
$graph_data = array_merge($graph_data, array("graph_id" => 'dem_graph_'));
$graph_data = array_merge($graph_data, array("graph_title" => "Consumption level {$commodity_name} {$title} {$time}"));
$graph_data = array_merge($graph_data, array("graph_type" => $graph_type));
$graph_data = array_merge($graph_data, array("graph_yaxis_title" => "Commodity Consumption level in {$option_new}"));
$graph_data = array_merge($graph_data, array("graph_categories" => $category_data));
$graph_data = array_merge($graph_data, array("series_data" => array('total' => $series_data)));
$data = array();
$data['graph_id'] = 'dem_graph_';
$data['high_graph'] = $this->hcmp_functions->create_high_chart_graph($graph_data);
return $this->load->view("shared_files/report_templates/high_charts_template_v", $data);
}
}
public function tracer_item_stock_level($county_id = null, $district_id = null, $facility_code = null, $commodity_id = null, $graph_type = null)
{
$district_id = $district_id == "NULL" ? null : $district_id;
$graph_type = $graph_type == "NULL" ? null : $graph_type;
$facility_code = $facility_code == "NULL" ? null : $facility_code;
$county_id = $county_id == "NULL" ? null : $county_id;
$commodity_id = $commodity_id == "ALL" || $commodity_id == "NULL" ? null : $commodity_id;
// echo $commodity_id;die;
$and_data = $district_id > 0 ? " AND d.id = '{$district_id}'" : null;
$and_data .= $facility_code > 0 ? " AND f.facility_code = '{$facility_code}'" : null;
$and_data .= $county_id > 0 ? " AND ct.id='{$county_id}'" : null;
// $and_data .= ($county_id > 0) ? " AND counties.id='$county_id'" : null;
$and_data = isset($and_data) ? $and_data : null;
$from_others = null;
$from_others .= $district_id > 0 ? "districts d," : null;
$from_others .= $county_id > 0 ? "counties ct," : null;
if ($graph_type == 'excel') {
$and_data .= isset($commodity_id) ? "AND d.id ={$commodity_id}" : "AND d.tracer_item =1";
// $and_data .= isset($commodity_id) ? "AND commodities.id =$commodity_id" : "AND d.tracer_item =1";
} else {
$and_data .= isset($commodity_id) ? "AND commodities.id ={$commodity_id}" : "AND commodities.tracer_item =1";
}
$group_by = $district_id > 0 && isset($county_id) && !isset($facility_code) ? " ,d.id" : null;
$group_by .= $facility_code > 0 && isset($district_id) ? " ,f.facility_code" : null;
$group_by .= $county_id > 0 && !isset($district_id) ? " ,c_.id" : null;
$group_by = isset($group_by) ? $group_by : " ,c_.id";
$title = '';
if (isset($county_id)) {
$county_name = counties::get_county_name($county_id);
$name = $county_name['county'];
$title = "{$name} County";
} elseif (isset($district_id)) {
$district_data = isset($district_id) && $district_id > 0 ? districts::get_district_name($district_id)->toArray() : null;
$district_name_ = isset($district_data) ? " :" . $district_data[0]['district'] . " Subcounty" : null;
$title = isset($facility_code) && isset($district_id) ? "{$district_name_} : {$facility_name}" : (isset($district_id) && !isset($facility_code) ? "{$district_name_}" : "{$name} County");
} elseif (isset($facility_code)) {
$facility_code_ = isset($facility_code) ? facilities::get_facility_name_($facility_code) : null;
$title = $facility_code_['facility_name'];
} else {
$title = "National";
}
if ($graph_type != "excel") {
$commodity_array;
$getdates = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAll("SELECT MIN(created_at) as EarliestDate,MAX(created_at) as LatestDate\n\t\t\tFROM facility_issues");
//echo '<pre>'; print_r($getdates);echo '<pre>'; exit;
$early = $getdates[0]['EarliestDate'];
$late = $getdates[0]['LatestDate'];
$now = time();
$my_date = strtotime($early);
$datediff = ($now - $my_date) / (60 * 60 * 24);
//in days
$datediff = round($datediff, 1);
if (isset($county_id)) {
$getdates = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAll("SELECT MIN(created_at) as EarliestDate,MAX(created_at) as LatestDate\n\t\t\t\t\tFROM facility_issues \n\t\t\t\t\tinner join facilities on facility_issues.facility_code=facilities.facility_code\n\t\t\t\t\tinner join districts on facilities.district=districts.id\n\t\t\t\t\tinner join counties on districts.county=counties.id\n\t\t\t\t\tinner join commodities on facility_issues.commodity_id=commodities.id where counties.id={$county_id}");
//echo '<pre>'; print_r($getdates);echo '<pre>'; exit;
$early = $getdates[0]['EarliestDate'];
$late = $getdates[0]['LatestDate'];
$now = time();
$my_date = strtotime($early);
$datediff = ($now - $my_date) / (60 * 60 * 24);
//in days
$datediff = round($datediff, 1);
$get_amc = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAll("SELECT commodities.id,commodities.commodity_name,avg(facility_issues.qty_issued) as total_units_consumed,\n\t\t\t\t(sum(facility_issues.qty_issued)*30/{$datediff})/commodities.total_commodity_units as amc_packs,commodities.total_commodity_units FROM hcmp_rtk.facility_issues \n\t\t\t\tinner join facilities on facility_issues.facility_code=facilities.facility_code\n\t\t\t\tinner join districts on facilities.district=districts.id\n\t\t\t\tinner join counties on districts.county=counties.id inner join commodities on facility_issues.commodity_id=commodities.id where s11_No IN('internal issue','(-ve Adj) Stock Deduction')\n\t\t\t\t{$and_data} group by commodities.id");
$get_amc = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAll($new_sql_amc);
//return $get_amc ;
$get_totals = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAll("SELECT commodities.id,commodities.commodity_name,sum(facility_stocks.current_balance) \n\t\t\t\tas total_bal_units, sum(facility_stocks.current_balance)/commodities.total_commodity_units as cur_bal_packs,commodities.total_commodity_units FROM hcmp_rtk.facility_stocks \n\t\t\t\tinner join facilities on facility_stocks.facility_code=facilities.facility_code\n\t\t\t\tinner join districts on facilities.district=districts.id\n\t\t\t\tinner join counties on districts.county=counties.id inner join commodities on facility_stocks.commodity_id=commodities.id \n\t\t\t\twhere commodities.status=1 {$and_data} group by commodities.id");
} else {
$new_sql_amc = "SELECT commodities.id,commodities.commodity_name,CEIL(AVG(facility_issues.qty_issued)) AS total_units_consumed, \n\t\t\t\tCEIL((SUM(facility_issues.qty_issued) / 3)) AS amc_units,\n\t\t\t\tCEIL((SUM(facility_issues.qty_issued) / 3) / commodities.total_commodity_units) AS amc_packs,commodities.total_commodity_units\n\t\t\t\tFROM facility_issues {$from_others} INNER JOIN commodities ON facility_issues.commodity_id = commodities.id \n\t\t\t\tWHERE s11_No IN ('internal issue' , '(-ve Adj) Stock Deduction') {$and_data} \n\t\t\t\tAND facility_issues.expiry_date > '2016-04-21' \n\t\t\t\tAND facility_issues.date_issued between '2016-02-31' and '2016-04-31'\n\t\t\t\tGROUP BY commodities.id order by commodities.id asc";
// echo $new_sql_amc;die;
$get_amc = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAll($new_sql_amc);
//return $get_amc ;
echo $and_data;
exit;
$new_sql_totals = "SELECT commodities.id,commodities.commodity_name,CEIL(sum(facility_stocks.current_balance))\n\t\t\t\tas total_bal_units, CEIL(sum(facility_stocks.current_balance)/commodities.total_commodity_units) as cur_bal_packs,commodities.total_commodity_units FROM facility_stocks {$from_others} inner join commodities on facility_stocks.commodity_id=commodities.id \n\t\t\t\twhere commodities.status=1 {$and_data} AND facility_stocks.expiry_date > '2016-04-21' group by commodities.id order by commodities.id asc";
$get_totals = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAll($new_sql_totals);
}
//return $get_totals ;
$combine = array();
for ($i = 0; $i < sizeof($get_totals); $i++) {
//array_push($combine,$get_totals[$i ],$get_amc[$i ]);
$combine[] = array_merge($get_totals[$i], $get_amc[$i]);
}
// echo '<pre>'; print_r($combine);echo '<pre>'; exit;
$category_data = array();
$series_data = $series_data_ = array();
$temp_array = $temp_array_ = array();
$graph_data = array();
$graph_type = '';
foreach ($combine as $data) {
$series_data = array_merge($series_data, array($data["commodity_name"] => (int) $data['cur_bal_packs'] / (int) $data['amc_packs']));
$category_data = array_merge($category_data, array($data["commodity_name"]));
}
// echo "<pre>";print_r($series_data);echo "</pre>";exit;
$graph_type = 'bar';
$graph_data = array_merge($graph_data, array("graph_id" => 'dem_graph_mos'));
$graph_data = array_merge($graph_data, array("graph_title" => "{$title} Stock Level in Months of Stock (MOS)"));
$graph_data = array_merge($graph_data, array("graph_type" => $graph_type));
$graph_data = array_merge($graph_data, array("color" => "['#4572A7','#FFF263', '#6AF9C4']"));
$graph_data = array_merge($graph_data, array("graph_yaxis_title" => "MOS"));
$graph_data = array_merge($graph_data, array("graph_categories" => $category_data));
$graph_data = array_merge($graph_data, array("series_data" => array('total' => $series_data)));
$data = array();
$data['high_graph'] = $this->hcmp_functions->create_high_chart_graph($graph_data);
//
$data['graph_id'] = 'dem_graph_mos';
return $this->load->view("shared_files/report_templates/high_charts_template_v_national", $data);
//
} else {
$excel_data = array('doc_creator' => "HCMP", 'doc_title' => "Stock Level in Months of Stock {$title}", 'file_name' => $title . ' MOS');
$row_data = array();
$column_data = array("County", "Sub-County", "Facility Name", "Facility Code", "Item Name", "MOS(packs)");
$excel_data['column_data'] = $column_data;
//echo '' ; exit;
$commodity_array = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAll("SELECT d.id,ct.county,sc.district,f.facility_code,\n\t\t\t\tf.facility_name,sum(fs.current_balance) as bal\n\t\t\t\t,sum(fs.current_balance)/d.total_commodity_units as packs,d.total_commodity_units,fs.batch_no,fs.expiry_date,d.commodity_name\n\t\t\t\tFROM hcmp_rtk.facility_stocks fs\n\t\t\t\tINNER JOIN facilities f ON fs.facility_code=f.facility_code\n\t\t\t\tINNER JOIN commodities d ON fs.commodity_id=d.id\n\t\t\t\tINNER JOIN districts sc ON f.district=sc.id\n\t\t\t\tINNER JOIN counties ct ON sc.county=ct.id\n\t\t\t\t{$and_data} AND fs.status=1 group by fs.batch_no order by ct.id asc\n\t\t\t\t");
$r_data = array();
$counter = 0;
foreach ($commodity_array as $key) {
$commodity = $key['id'];
$f_code = $key['facility_code'];
$batch_n = $key['batch_no'];
$amc = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAll("\n\t\t\t\t\tSELECT sum(qty_issued) as amc FROM hcmp_rtk.facility_issues where commodity_id={$commodity} and facility_code={$f_code} AND batch_no='{$batch_n}' AND\n\t\t\t\t\tdate_issued\t> DATE_SUB(CURDATE(), INTERVAL 31 DAY)\n\t\t\t\t\t");
foreach ($amc as $val) {
$amc = $val['amc'];
if ($amc < 0) {
$amc = $amc * -1;
}
$amc_packs = round($amc / $key['total_commodity_units']);
if ($amc_packs < 0) {
$amc_packs = $amc_packs * -1;
}
if ($key['bal'] < 0) {
$bal = $key['bal'] * -1;
} else {
$bal = $key['bal'];
}
if ($key['packs'] < 0) {
$packs = $key['packs'] * -1;
} else {
$packs = $key['packs'];
}
}
$r_data[$counter]["county"] = $key['county'];
$r_data[$counter]["district"] = $key['district'];
$r_data[$counter]["facility_code"] = $key['facility_code'];
$r_data[$counter]["facility_name"] = $key['facility_name'];
$r_data[$counter]["commodity_name"] = $key['commodity_name'];
$r_data[$counter]["amc_packs"] = $amc_packs;
// $r_data[$counter]["amc_units"] = $key['bal'];
$counter = $counter + 1;
}
array_push($row_data, $r_data);
$excel_data['row_data'] = $r_data;
// echo "<pre>";print_r($row_data);echo "</pre>";exit;
$this->hcmp_functions->create_excel($excel_data);
}
}
public function expiry($year = null, $county_id = null, $district_id = null, $facility_code = null, $graph_type = null)
{
$year = $year == "NULL" ? date('Y') : $year;
/*//Get the current month
$datetime1 = new DateTime('Y-10');
$datetime2 = new DateTime('Y-12');
$interval = $datetime2->diff($datetime1);
echo $interval->format('%R%a days');exit;
$current_month = date("Y-m");
$end = date('Y-12');
$interval = $current_month->diff($end);
echo $interval;exit;*/
//check if the district is set
$district_id = $district_id == "NULL" ? null : $district_id;
// $option=($optionr=="NULL") ? null :$option;
$facility_code = $facility_code == "NULL" ? null : $facility_code;
$county_id = $county_id == "NULL" ? null : $county_id;
// $months = array('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec');
$month_ = isset($month) ? $months[(int) $month - 1] : null;
$category_data = array();
$series_data = $series_data2 = $series_data_ = $series_data_2 = array();
$temp_array = $temp_array2 = $temp_array_ = array();
$graph_data = array();
$title = '';
if (isset($county_id)) {
$county_name = counties::get_county_name($county_id);
$name = $county_name['county'];
$title = "{$name} County";
} elseif (isset($district_id)) {
$district_data = isset($district_id) && $district_id > 0 ? districts::get_district_name($district_id)->toArray() : null;
$district_name_ = isset($district_data) ? " :" . $district_data[0]['district'] . " Subcounty" : null;
$title = isset($facility_code) && isset($district_id) ? "{$district_name_} : {$facility_name}" : (isset($district_id) && !isset($facility_code) ? "{$district_name_}" : "{$name} County");
} elseif (isset($facility_code)) {
$facility_code_ = isset($facility_code) ? facilities::get_facility_name_($facility_code) : null;
$title = $facility_code_['facility_name'];
} else {
$title = "";
}
$months = array('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec');
$category_data = array_merge($category_data, $months);
$and_data = $district_id > 0 ? " AND d1.id = '{$district_id}'" : null;
$and_data .= $facility_code > 0 ? " AND f.facility_code = '{$facility_code}'" : null;
$and_data .= $county_id > 0 ? " AND d1.county='{$county_id}'" : null;
$and_data = isset($and_data) ? $and_data : null;
$group_by = $district_id > 0 && isset($county_id) && !isset($facility_code) ? " ,d1.id" : null;
$group_by .= $facility_code > 0 && isset($district_id) ? " ,f.facility_code" : null;
$group_by .= $county_id > 0 && !isset($district_id) ? " ,c.id" : null;
$group_by = isset($group_by) ? $group_by : " ,c.id";
if ($graph_type != "excel") {
$commodity_array = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAll("select DATE_FORMAT( temp.expiry_date, '%b' ) AS cal_month,\n\t \t\t\tsum(temp.total) as total\n\t\t\t\tfrom\n\t\t\t\t districts d1,\n\t\t\t\t facilities f\n\t\t\t\t left join\n\t\t\t\t (select ROUND(SUM(f_s.current_balance / d.total_commodity_units) * d.unit_cost, 1) AS total,\n\t\t\t\t f_s.facility_code,f_s.expiry_date\n\t\t\t\t from\n\t\t\t\t facility_stocks f_s, commodities d\n\t\t\t\t where\n\t\t\t\t f_s.expiry_date < NOW()\n\t\t\t\t and d.id = f_s.commodity_id\n\t\t\t\t and year(f_s.expiry_date) = {$year}\n\t\t\t\t AND (f_s.status =1 or f_s.status =2 )\n\t\t\t\t GROUP BY d.id , f_s.facility_code having total > 1) \n\t\t\t temp ON temp.facility_code = f.facility_code\n\t\t\t\t\twhere\n\t\t\t\t\t f.district = d1.id\n\t\t\t\t\t {$and_data}\n\t\t\t\t\t and temp.total > 0\n\t\t\t\t\tgroup by month(temp.expiry_date)");
$commodity_array2 = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAll("\n\t\t\tselect \n\t\t\t DATE_FORMAT( temp.expiry_date, '%b' ) AS cal_month,\n\t\t\t sum(temp.total) as total\n\t\t\tfrom\n\t\t\t districts d1,\n\t\t\t facilities f\n\t\t\t left join\n\t\t\t (select \n\t\t\t ROUND(SUM(f_s.current_balance / d.total_commodity_units) * d.unit_cost, 1) AS total,\n\t\t\t f_s.facility_code,f_s.expiry_date\n\t\t\t from\n\t\t\t facility_stocks f_s, commodities d\n\t\t\t where\n\t\t\t f_s.expiry_date >= NOW()\n\t\t\t and d.id = f_s.commodity_id\n\t\t\t AND f_s.status = (1 or 2)\n\t\t\t\t\t\tAND year(f_s.expiry_date) = {$year}\n\t\t\t GROUP BY d.id , f_s.facility_code\n\t\t\t having total > 1) temp ON temp.facility_code = f.facility_code\n\t\t\twhere\n\t\t\t f.district = d1.id\n\t\t\t {$and_data}\n\t\t\t and temp.total > 0\n\t\t\tgroup by month(temp.expiry_date)\n\t\t ");
foreach ($commodity_array as $data) {
$temp_array = array_merge($temp_array, array($data["cal_month"] => $data['total']));
}
foreach ($commodity_array2 as $data2) {
$temp_array2 = array_merge($temp_array2, array($data2["cal_month"] => $data2['total']));
//$series_data2 = array_merge($series_data2, array($data2["cal_month"] => (int)$data2['total']));
//$category_data = array_merge($category_data, array($data2["cal_month"]));
}
// echo "<pre>";print_r($temp_array2);echo "</pre>";exit;
foreach ($months as $key => $data) {
//for expiries
$val = array_key_exists($data, $temp_array) ? (int) $temp_array[$data] : (int) 0;
$series_data = array_merge($series_data, array($val));
array_push($series_data_, array($data, $val));
//for potential expiries
$val2 = array_key_exists($data, $temp_array2) ? (int) $temp_array2[$data] : (int) 0;
$series_data2 = array_merge($series_data2, array($val2));
array_push($series_data_2, array($data, $val2));
}
$graph_type = 'column';
$graph_data = array_merge($graph_data, array("graph_id" => 'dem_graph_'));
$graph_data = array_merge($graph_data, array("color" => "['#6AF9C4','#4b0082', '#6AF9C4']"));
$graph_data = array_merge($graph_data, array("graph_title" => "Expiries in {$title} {$year}"));
$graph_data = array_merge($graph_data, array("graph_type" => $graph_type));
$graph_data = array_merge($graph_data, array("graph_yaxis_title" => "KSH"));
$graph_data = array_merge($graph_data, array("graph_categories" => $category_data));
$graph_data = array_merge($graph_data, array("series_data" => array()));
//$default_expiries=array_merge($default_expiries,array("series_data"=>array()));
$graph_data['series_data'] = array_merge($graph_data['series_data'], array("Potential Expiries" => $series_data2, "Actual Expiries" => $series_data));
//echo "<pre>";print_r($graph_data);echo "</pre>";exit;
$data = array();
$data['graph_id'] = 'dem_graph_';
$data['high_graph'] = $this->hcmp_functions->create_high_chart_graph($graph_data);
// print_r($data['high_graph']);
//exit;
return $this->load->view("shared_files/report_templates/high_charts_template_v_national", $data);
} else {
$excel_data = array('doc_creator' => "HCMP", 'doc_title' => "Expiry {$title}", 'file_name' => "Stock Expired in {$title} {$year}");
$row_data = array();
$column_data = array("Commodity", "Unit Size", "Quantity (Packs)", "Quantity (Units)", "Unit Cost (Ksh)", "Total Cost Expired (Ksh)", "Date of Expiry", "Supplier", "Manufacturer", "Facility Name", "Facility Code", "Sub-County", "County");
$excel_data['column_data'] = $column_data;
//echo ; exit;
$facility_stock_data = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAll("select c.county, d1.district as subcounty ,temp.drug_name,\n f.facility_code, f.facility_name,temp.manufacture, sum(temp.total) as total_ksh,temp.units,\ntemp.unit_cost,temp.expiry_date,temp.unit_size,\ntemp.packs\nfrom districts d1, counties c, facilities f left join\n (\nselect ROUND( SUM(\nf_s.current_balance / d.total_commodity_units ) * d.unit_cost, 1) AS total,\n ROUND( SUM( f_s.current_balance / d.total_commodity_units ), 1) as packs,\nSUM( f_s.current_balance) as units,\nf_s.facility_code,d.id,d.commodity_name as drug_name, f_s.manufacture,\nf_s.expiry_date,d.unit_size,d.unit_cost\n\n from facility_stocks f_s, commodities d\nwhere f_s.expiry_date < NOW( ) \nand d.id=f_s.commodity_id\nand year(f_s.expiry_date) !=1970\nAND (f_s.status =1 or f_s.status =2)\nGROUP BY d.id,f_s.facility_code having total >1\n\n ) temp\n on temp.facility_code = f.facility_code\nwhere f.district = d1.id\nand c.id=d1.county\nand temp.total>0\n{$and_data}\ngroup by temp.id,f.facility_code\norder by temp.drug_name asc,temp.total asc, temp.expiry_date desc\n ");
array_push($row_data, array("The below commodities have expired {$title} {$year}"));
foreach ($facility_stock_data as $facility_stock_data_item) {
array_push($row_data, array($facility_stock_data_item["drug_name"], $facility_stock_data_item["unit_size"], $facility_stock_data_item["packs"], $facility_stock_data_item["units"], $facility_stock_data_item["unit_cost"], $facility_stock_data_item["total_ksh"], $facility_stock_data_item["expiry_date"], "KEMSA", $facility_stock_data_item["manufacture"], $facility_stock_data_item["facility_name"], $facility_stock_data_item["facility_code"], $facility_stock_data_item["subcounty"], $facility_stock_data_item["county"]));
}
$excel_data['row_data'] = $row_data;
$this->hcmp_functions->create_excel($excel_data);
}
}
public function order($year = null, $county_id = null, $district_id = null, $facility_code = null, $graph_type = null)
{
$district_id = $district_id == "NULL" ? null : $district_id;
$graph_type = $graph_type == "NULL" ? null : $graph_type;
$facility_code = $facility_code == "NULL" ? null : $facility_code;
$county_id = $county_id == "NULL" ? null : $county_id;
$year = $year == "NULL" ? date('Y') : $year;
$and_data = $district_id > 0 ? " AND d.id = '{$district_id}'" : null;
$and_data .= $facility_code > 0 ? " AND f.facility_code = '{$facility_code}'" : null;
$and_data .= $county_id > 0 ? " AND c.id='{$county_id}'" : null;
$and_data .= $year > 0 ? " and year(o.`order_date`) ={$year}" : null;
$and_data = isset($year) ? $and_data : null;
//echo ; exit;
$commodity_array = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAll("SELECT \n sum(o.`order_total`) as total,DATE_FORMAT( o.`order_date`, '%b' ) AS cal_month\n FROM\n facilities f, districts d, counties c,`facility_orders` o\n WHERE\n o.facility_code=f.facility_code\n and f.district=d.id and d.county=c.id\n {$and_data}\n group by month(o.`order_date`)\n ");
$commodity_array_2 = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAll("SELECT \n sum(o.`order_total`) as total,DATE_FORMAT( o.`order_date`, '%b' ) AS cal_month\n FROM\n facilities f, districts d, counties c,`facility_orders` o\n WHERE\n o.facility_code=f.facility_code\n and f.district=d.id and d.county=c.id and o.status = 4\n {$and_data}\n group by month(o.`order_date`)\n ");
// var_dump($commodity_array_2);
// exit;
$category_data = array();
$series_data = $series_data_ = array();
$temp_array = $temp_array_ = array();
$graph_data = array();
$title = '';
if ($graph_type != "excel") {
if (isset($county_id)) {
$county_name = counties::get_county_name($county_id);
$name = $county_name['county'];
$title = "{$name} county";
} elseif (isset($district_id)) {
$district_data = isset($district_id) && $district_id > 0 ? districts::get_district_name($district_id)->toArray() : null;
$district_name_ = isset($district_data) ? " :" . $district_data[0]['district'] . " Subcounty" : null;
$title = isset($facility_code) && isset($district_id) ? "{$district_name_} : {$facility_name}" : (isset($district_id) && !isset($facility_code) ? "{$district_name_}" : "{$name} County");
} elseif (isset($facility_code)) {
$facility_code_ = isset($facility_code) ? facilities::get_facility_name_($facility_code) : null;
$title = $facility_code_['facility_name'];
} else {
$title = "National";
}
foreach ($commodity_array as $data) {
$series_data = array_merge($series_data, array($data["cal_month"] => (int) $data['total']));
$category_data = array_merge($category_data, array($data["cal_month"]));
}
$series_data2 = $series_data_2 = $category_data_2 = array();
foreach ($commodity_array_2 as $data_2) {
$series_data_2 = array_merge($series_data_2, array($data_2["cal_month"] => (int) $data_2['total']));
$category_data_2 = array_merge($category_data_2, array($data_2["cal_month"]));
}
//$graph_details = array('' => , );;
// array_merge($series_data,$series_data_2);
// echo "<pre>";print_r($series_data_2);echo "</pre>";exit;
$graph_type = 'column';
$graph_data = array_merge($graph_data, array("graph_id" => 'dem_graph_order'));
$graph_data = array_merge($graph_data, array("graph_title" => "{$year} {$title} Order Cost"));
$graph_data = array_merge($graph_data, array("graph_type" => $graph_type));
$graph_data = array_merge($graph_data, array("color" => "['#92A8CD', '#A47D7C']"));
$graph_data = array_merge($graph_data, array("graph_yaxis_title" => "Cost in KSH"));
$graph_data = array_merge($graph_data, array("graph_categories" => $category_data));
$graph_data = array_merge($graph_data, array("series_data" => array('Cost of Orders Made' => $series_data, 'Cost of Orders delivered' => $series_data_2)));
$data = array();
//seth
$data['high_graph'] = $this->hcmp_functions->create_high_chart_graph($graph_data);
// echo $data['high_graph'];exit;
$data['graph_id'] = 'dem_graph_order';
return $this->load->view("shared_files/report_templates/high_charts_template_v_national", $data);
} else {
$excel_data = array('doc_creator' => "HCMP", 'doc_title' => "{$year} {$title} Order Cost", 'file_name' => "{$year} {$title} Order Cost (KSH)");
$row_data = array();
$column_data = array("Date of Order Placement", "Date of Order Approval", "Total Order Cost (Ksh)", "Date of Delivery", "Lead Time (Order Placement to Delivery)", "Supplier", "Facility Name", "Facility Code", "Sub-County", "County");
$excel_data['column_data'] = $column_data;
//echo ; exit;
$facility_stock_data = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAll("SELECT c.county,d.district as sub_county, f.facility_name, f.facility_code, \n DATE_FORMAT(`order_date`,'%d %b %y') as order_date, \n DATE_FORMAT(`approval_date`,'%d %b %y') as approval_date,\n DATE_FORMAT(`deliver_date`,'%d %b %y') as delivery_date, \n DATEDIFF(`approval_date`,`order_date`) as tat_order_approval,\n DATEDIFF(`deliver_date`,`approval_date`) as tat_approval_deliver,\n DATEDIFF(`deliver_date`,`order_date`) as tat_order_delivery\n , sum(o.`order_total`) as total \n from facility_orders o, facilities f, districts d, counties c \n where f.facility_code=o.facility_code and f.district=d.id \n and c.id=d.county {$and_data}\n group by o.id order by c.county asc ,d.district asc , \n f.facility_name asc \n ");
array_push($row_data, array("The orders below were placed {$year} {$title}"));
foreach ($facility_stock_data as $facility_stock_data_item) {
array_push($row_data, array($facility_stock_data_item["order_date"], $facility_stock_data_item["approval_date"], $facility_stock_data_item["total"], $facility_stock_data_item["delivery_date"], $facility_stock_data_item["tat_order_delivery"], "KEMSA", $facility_stock_data_item["facility_name"], $facility_stock_data_item["facility_code"], $facility_stock_data_item["sub_county"], $facility_stock_data_item["county"]));
}
$excel_data['row_data'] = $row_data;
$this->hcmp_functions->create_excel($excel_data);
}
}
public function county_store_internal()
{
$county_id = $this->session->userdata('county_id');
$county = counties::get_county_name($county_id);
$data['county_id'] = $county_id;
$data['county_data'] = $county;
$data['content_view'] = "county/drug_store/drug_store_internal";
$data['donate_destination'] = "county";
$data['counties'] = counties::getAll();
$data['banner_text'] = "Redistribute Commodities To Counties";
$data['title'] = "Redistribute Commodities";
$data['commodities'] = "";
$data['county_stock_data'] = "";
echo "<pre>";
print_r($data);
echo "</pre>";
$this->load->view("shared_files/template/template", $data);
}
public function scheduled_email_iterator($interval = NULL)
{
$counties = counties::get_counties_all_using_HCMP();
// $interval = ((isset($interval))&& ($interval > 0))? $interval:3;//default to select annual
foreach ($counties as $key) {
$county_spec_mail = array();
// $county_email = $this-> hcmp_functions ->get_county_email("2");
$county_data = Users::get_cp_details_karsan($key['county']);
$county_name = Counties::get_county_name($key['county']);
$county_name = $county_name['county'];
// echo "<pre>";print_r($key['county']);exit;
foreach ($county_data as $keys) {
array_push($county_spec_mail, $keys['email']);
}
// echo "<pre>";print_r($key['county']);
$dev_mails = array();
$dev_mails[1] = "*****@*****.**";
$dev_mails[2] = "*****@*****.**";
// $county_spec_mail = array_merge($county_spec_mail, $dev_mails);
$county_spec_mail = $this->hcmp_functions->developer_emails($county_spec_mail);
// echo "<pre>";print_r($county_spec_mail);
$this->send_potential_expiries($key['county'], NULL, NULL, NULL, 3, $county_spec_mail);
}
$dev_mails = "karsanrichard@gmail.com,ttunduny@gmail.com";
$time = date('M d Y h:m:s');
$message = "\n\t\tCalling all Developers. </n>\n\t\tCounty Potential Expiries Emails Have Been Sent Out Successfully Today as at {$time}. </br>\n\t\tGod Speed Gentlemen. </br>\n\t\tMay the force be with you";
$subject = "Successful Potential Expiries";
$this->hcmp_functions->send_email($dev_mails, $message, $subject, $attach_file);
// exit;
}
public function send_system_usage_specific($county_id = NULL, $district_id = NULL, $facility_code = NULL, $listing_email_address = NULL)
{
//Karsan
$time = date('M , d Y');
$county_name = counties::get_county_name($county_id);
$district_name = districts::get_district_name_($district_id);
$cp_email_query = "SELECT * FROM email_listing_new WHERE county = {$county_id} AND usertype = 10";
$cp_emails = $this->db->query($cp_email_query)->result_array();
$scp_email_query = "SELECT * FROM email_listing_new WHERE county = {$county_id} AND usertype = 3";
$scp_emails = $this->db->query($scp_email_query)->result_array();
// echo "<pre>";print_r($cp_emails);exit;
//CP
$cp_emails_str = '';
foreach ($cp_emails as $emails => $value) {
$cp_emails_str = $cp_emails_str . ',' . $value['email'];
// echo "<pre>";print_r($value['email']);exit;
}
$cp_emails_str = ltrim($cp_emails_str, ',');
//SCP
$scp_emails_str = '';
foreach ($scp_emails as $emails => $value) {
$scp_emails_str = $scp_emails_str . ',' . $value['email'];
// echo "<pre>";print_r($value['email']);exit;
}
$scp_emails_str = ltrim($scp_emails_str, ',');
// echo $scp_emails_str;exit;
// echo "<pre>";print_r($scp_emails);exit;
// echo "<pre>";print_r($district_name);
// echo "<pre>OVER ";
$appended = !empty($district_name) ? $district_name['district'] . " Subcounty" : $county_name['county'] . " County";
$excel_title = 'HCMP facility activity log summary for ' . $appended;
$email_subject = 'HCMP System usage report for ' . $appended . ' as at ' . $time;
// echo $county_name.' '.$district_name;
// echo $excel_title;exit;
$active_facilities = Facilities::getAll_($county_id, $district_id);
// echo "<pre>";print_r($active_facilities);echo "</pre>";exit;
$last_seen = Facilities::get_facility_data_specific(NULL, $county_id, $district_id, $facility_code, 'all');
$last_issued = Facilities::get_facility_data_specific('last_issued', $county_id, $district_id, $facility_code, 'all');
$last_ordered = Facilities::get_facility_data_specific('last_ordered', $county_id, $district_id, $facility_code, 'all');
$decommissioned = Facilities::get_facility_data_specific('last_decommissioned', $county_id, $district_id, $facility_code, 'all');
$redistributed = Facilities::get_facility_data_specific('last_redistributed', $county_id, $district_id, $facility_code, 'all');
$added_stock = Facilities::get_facility_data_specific('last_added_stock', $county_id, $district_id, $facility_code, 'all');
// $all_faciliteis = Facilities::getAll_();
// echo "<pre>";print_r($active_facilities);echo "</pre>";exit;
$facility_count = empty($active_facilities) ? NULL : count($active_facilities);
$final_array = array();
$last_seen_count = count($last_seen);
$last_issued_count = count($last_issued);
$last_ordered_count = count($last_ordered);
$decommissioned_count = count($decommissioned);
$redistributed_count = count($redistributed);
$added_stock_count = count($added_stock);
foreach ($active_facilities as $a_c) {
$final_array[] = array('Facility Name' => $a_c['facility_name'], 'Facility Code' => $a_c['facility_code'], 'County' => $a_c['county'], 'Sub-County' => $a_c['subcounty']);
}
//active_facilities foreach
$final_array_count = count($final_array);
$last_seen_time = NULL;
$last_issued_time = NULL;
$last_order_time = NULL;
$last_deccommissioned_time = NULL;
$last_redistributed_time = NULL;
$last_added_stock_time = NULL;
foreach ($final_array as $keyy => $value) {
foreach ($last_seen as $key => $value) {
if ($final_array[$keyy]['Facility Code'] == $last_seen[$key]['facility_code']) {
// echo "<pre>".$last_ordered[$j]['last_seen']." ".$last_ordered[$j]['facility_code'];
if ($last_seen[$key]['last_seen'] > $last_seen_time) {
$last_seen_time = $last_seen[$key]['last_seen'];
$days_last_seen = $last_seen[$key]['difference_in_days'];
// echo "<pre>".$last_seen_time;
}
$final_array[$keyy]['Date Last Seen'] = $last_seen_time;
$final_array[$keyy]['Days From Last Seen'] = $days_last_seen;
// echo "<pre>".$last_seen_time;
// $final_array[$i]['Days From Last Seen'] = abs($last_seen_time - $now);
$last_seen_time = NULL;
}
//end of facility code if
}
//end of last seen foreach
}
// echo "<pre>";print_r($final_array);echo "</pre>";exit;
//last issued time
foreach ($final_array as $keyy => $value) {
foreach ($last_issued as $key => $value) {
if ($final_array[$keyy]['Facility Code'] == $last_issued[$key]['facility_code']) {
// echo "<pre>".$last_ordered[$j]['last_seen']." ".$last_ordered[$j]['facility_code'];
if ($last_issued[$key]['last_seen'] > $last_issued_time) {
$last_issued_time = $last_issued[$key]['last_seen'];
$days = $last_issued[$key]['difference_in_days'];
// echo "<pre>".$last_order_time;
}
$final_array[$keyy]['Date Last Issued'] = $last_issued_time;
$final_array[$keyy]['Days From Last Issue'] = $days;
// $final_array[$i]['Days From Last Seen'] = abs($last_seen_time - $now);
$last_issued_time = NULL;
}
//end of facility code if
}
//end of last seen foreach
}
//last ordered
foreach ($final_array as $keyy => $value) {
foreach ($last_ordered as $key => $value) {
if ($final_array[$keyy]['Facility Code'] == $last_ordered[$key]['facility_code']) {
// echo "<pre>".$last_ordered[$j]['last_seen']." ".$last_ordered[$j]['facility_code'];
if ($last_ordered[$key]['last_seen'] > $last_order_time) {
$last_order_time = $last_ordered[$key]['last_seen'];
$days_last_ordered = $last_ordered[$key]['difference_in_days'];
// echo "<pre>".$last_order_time;
}
$final_array[$keyy]['Date Last Ordered'] = $last_order_time;
$final_array[$keyy]['Days From Last Order'] = $days_last_ordered;
// $final_array[$i]['Days From Last Seen'] = abs($last_seen_time - $now);
$last_order_time = NULL;
}
//end of facility code if
}
//end of last seen foreach
}
foreach ($final_array as $keyy => $value) {
foreach ($decommissioned as $key => $value) {
if ($final_array[$keyy]['Facility Code'] == $decommissioned[$key]['facility_code']) {
// echo "<pre>".$last_ordered[$j]['last_seen']." ".$last_ordered[$j]['facility_code'];
if ($decommissioned[$key]['last_seen'] > $last_deccommissioned_time) {
$last_deccommissioned_time = $decommissioned[$key]['last_seen'];
$days_last_decommissioned = $decommissioned[$key]['difference_in_days'];
// echo "<pre>".$last_order_time;
}
$final_array[$keyy]['Date Last Decommissioned'] = $last_deccommissioned_time;
$final_array[$keyy]['Days From Last Decommissioned'] = $days_last_decommissioned;
// $final_array[$i]['Days From Last Seen'] = abs($last_seen_time - $now);
$last_deccommissioned_time = NULL;
}
//end of facility code if
}
//end of last seen foreach
}
foreach ($final_array as $keyy => $value) {
foreach ($redistributed as $key => $value) {
if ($final_array[$keyy]['Facility Code'] == $redistributed[$key]['facility_code']) {
// echo "<pre>".$last_ordered[$j]['last_seen']." ".$last_ordered[$j]['facility_code'];
if ($redistributed[$key]['last_seen'] > $last_redistributed_time) {
$last_redistributed_time = $redistributed[$key]['last_seen'];
$days_last_redistributed = $redistributed[$key]['difference_in_days'];
// echo "<pre>".$last_order_time;
}
$final_array[$keyy]['Date Last Redistributed'] = $last_redistributed_time;
$final_array[$keyy]['Days From Last Redistributed'] = $days_last_redistributed;
// $final_array[$i]['Days From Last Seen'] = abs($last_seen_time - $now);
$last_redistributed_time = NULL;
}
//end of facility code if
}
//end of last seen foreach
}
foreach ($final_array as $keyy => $value) {
foreach ($added_stock as $key => $value) {
if ($final_array[$keyy]['Facility Code'] == $added_stock[$key]['facility_code']) {
// echo "<pre>".$last_ordered[$j]['last_seen']." ".$last_ordered[$j]['facility_code'];
if ($added_stock[$key]['last_seen'] > $last_added_stock_time) {
$last_added_stock_time = $added_stock[$key]['last_seen'];
$days_last_added_stock = $added_stock[$key]['difference_in_days'];
// echo "<pre>".$last_order_time;
}
$final_array[$keyy]['Date Last Received Order'] = $last_added_stock_time;
$final_array[$keyy]['Days From Last Received Order'] = $days_last_added_stock;
// $final_array[$i]['Days From Last Seen'] = abs($last_seen_time - $now);
$last_added_stock_time = NULL;
}
//end of facility code if
}
//end of last seen foreach
}
// echo "<pre>";print_r($final_array);exit;
$row_data = array();
$counterrrr = 0;
foreach ($final_array as $facility) {
// echo "<pre>". $counterrrr . "</pre>";
// $counterrrr = $counterrrr + 1;
//random code to allow for commit
$issue_date = isset($facility['Date Last Issued']) ? date('Y-m-d', strtotime($facility['Date Last Issued'])) : "No Data Available";
$last_seen = isset($facility['Date Last Seen']) ? date('Y-m-d', strtotime($facility['Date Last Seen'])) : "No Data Available";
$redistribution = isset($facility['Date Last Redistributed']) ? date('Y-m-d', strtotime($facility['Date Last Redistributed'])) : "No Data Available";
$order_date = isset($facility['Date Last Ordered']) ? date('Y-m-d', strtotime($facility['Date Last Ordered'])) : "No Data Available";
$decommission_date = isset($facility['Date Last Decommissioned']) ? date('Y-m-d', strtotime($facility['Date Last Decommissioned'])) : "No Data Available";
$date_order = isset($facility['Date Last Received Order']) ? date('Y-m-d', strtotime($facility['Date Last Received Order'])) : "No Data Available";
$days_from_last_seen = isset($facility['Days From Last Seen']) ? $facility['Days From Last Seen'] : ' - ';
$days_from_last_issued = isset($facility['Days From Last Issue']) ? $facility['Days From Last Issue'] : ' - ';
$days_from_last_redist = isset($facility['Days From Last Redistributed']) ? $facility['Days From Last Redistributed'] : ' - ';
$days_from_last_ordered = isset($facility['Days From Last Order']) ? $facility['Days From Last Order'] : ' - ';
$decomissioned_days = isset($facility['Days From Last Decommissioned']) ? $facility['Days From Last Decommissioned'] : ' - ';
$days_from_last_recieved = isset($facility['Days From Last Received Order']) ? $facility['Days From Last Received Order'] : ' - ';
array_push($row_data, array($facility['Facility Name'], $facility['Facility Code'], $facility['Sub-County'], $facility['County'], $last_seen, $days_from_last_seen, $issue_date, $days_from_last_issued, $redistribution, $days_from_last_redist, $order_date, $days_from_last_ordered, $decommission_date, $decomissioned_days, $date_order, $days_from_last_recieved));
}
$excel_data = array();
// $excel_data = array('doc_creator' => 'HCMP ', 'doc_title' => 'System Usage Breakdown ', 'file_name' => 'system usage breakdown');
$excel_data = array('doc_creator' => 'HCMP-Kenya', 'doc_title' => $excel_title, 'file_name' => 'HCMP_Facility_Activity_Log_Summary_as_at_' . $time);
$column_data = array("Facility Name", "Facility Code", "Sub County", "County", "Date Last Logged In", "Days From Last Log In", "Date Last Issued", "Days From Last Issue", "Date Last Redistributed", "Days From Last Redistribution", "Date Last Ordered", "Days From Last Order", "Date Last Decommissioned", "Days From Last Decommission", "Date Last Received Order", "Days From Last Stock Addition");
$excel_data['column_data'] = $column_data;
$excel_data['row_data'] = $row_data;
$excel_data['report_type'] = 'Log Summary';
// echo "<pre>";print_r($excel_data);echo "</pre>";exit;
$res = $this->hcmp_functions->create_excel($excel_data);
// echo 'This '.$res;exit;
$message = '';
$message .= "<style> table {\n\t\t\tborder-collapse: collapse; \n\t\t}td,th{\n\t\t\tpadding: 12px;\n\t\t\ttext-align:center;\n\t\t}\n\n\t\t*{margin:0;padding:0}*{font-family:'Helvetica Neue',Helvetica,Helvetica,Arial,sans-serif}img{max-width:100%}.collapse{padding:0}body{-webkit-font-smoothing:antialiased;-webkit-text-size-adjust:none;width:100%!important;height:100%}a{color:#2BA6CB}.btn{text-decoration:none;color:#FFF;background-color:#666;padding:10px 16px;font-weight:700;margin-right:10px;text-align:center;cursor:pointer;display:inline-block}p.callout{padding:15px;background-color:#ECF8FF;margin-bottom:15px}.callout a{font-weight:700;color:#2BA6CB}table.social{background-color:#ebebeb}.social .soc-btn{padding:3px 7px;font-size:12px;margin-bottom:10px;text-decoration:none;color:#FFF;font-weight:700;display:block;text-align:center}a.fb{background-color:#3B5998!important}a.tw{background-color:#1daced!important}a.gp{background-color:#DB4A39!important}a.ms{background-color:#000!important}.sidebar .soc-btn{display:block;width:100%}table.head-wrap{width:100%}.header.container table td.logo{padding:15px}.header.container table td.label{padding:15px 15px 15px 0}table.body-wrap{width:100%}table.footer-wrap{width:100%;clear:both!important}.footer-wrap .container td.content p{border-top:1px solid #d7d7d7;padding-top:15px;font-size:9px;font-weight:500}h1,h2,h3,h4,h5,h6{font-family:HelveticaNeue-Light,'Helvetica Neue Light','Helvetica Neue',Helvetica,Arial,'Lucida Grande',sans-serif;line-height:1.1;margin-bottom:15px;color:#000}h1 small,h2 small,h3 small,h4 small,h5 small,h6 small{font-size:60%;color:#6f6f6f;line-height:0;text-transform:none}h1{font-weight:200;font-size:44px}h2{font-weight:200;font-size:37px}h3{font-weight:500;font-size:27px}h4{font-weight:500;font-size:23px}h5{font-weight:900;font-size:17px}h6{font-weight:900;font-size:14px;text-transform:uppercase;color:#444}.collapse{margin:0!important}p,ul{margin-bottom:10px;font-weight:400;font-size:14px;line-height:1.6}p.lead{font-size:17px}p.last{margin-bottom:0}ul li{margin-left:5px;list-style-position:inside}ul.sidebar{background:#ebebeb;display:block;list-style-type:none}ul.sidebar li{display:block;margin:0}ul.sidebar li a{text-decoration:none;color:#666;padding:10px 16px;cursor:pointer;border-bottom:1px solid #777;border-top:1px solid #FFF;display:block;margin:0}ul.sidebar li a.last{border-bottom-width:0}ul.sidebar li a h1,ul.sidebar li a h2,ul.sidebar li a h3,ul.sidebar li a h4,ul.sidebar li a h5,ul.sidebar li a h6,ul.sidebar li a p{margin-bottom:0!important}.container{display:block!important;max-width:100%!important;margin:0 auto!important;clear:both!important}.content{padding:15px;max-width:80%px;margin:0 auto;display:block}.content table{width:100%}.column{width:300px;float:left}.column tr td{padding:15px}.column-wrap{padding:0!important;margin:0 auto;max-width:600px!important}.column table{width:100%}.social .column{width:280px;min-width:279px;float:left}.clear{display:block;clear:both}@media only screen and (max-width:600px){a[class=btn]{display:block!important;margin-bottom:10px!important;background-image:none!important;margin-right:0!important}div[class=column]{width:auto!important;float:none!important}table.social div[class=column]{width:auto!important}}</style>";
$message .= '
<tr>
<td colspan="12">
</tr>
</tbody>
</table>';
$message .= "<!-- BODY -->\n\t\t<table class='body-wrap'>\n\t\t\t<tr>\n\t\t\t\t<td></td>\n\t\t\t\t<td class='container' bgcolor='#FFFFFF'>\n\n\t\t\t\t\t<div class='content'>\n\t\t\t\t\t\t<table>\n\t\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t\t<td>\n\n\t\t\t\t\t\t\t\t\t<p class='lead'>Find attached a summary of Facility Activity Log, as at {$time}</p>\n\n\t\t\t\t\t\t\t\t\t<table class='social' width='100%'>\n\t\t\t\t\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t\t\t\t\t<td>\n\n\t\t\t\t\t\t\t\t\t\t\t\t<!-- column 1 -->\n\t\t\t\t\t\t\t\t\t\t\t\t<table align='left' class='column'>\n\n\t\t\t\t\t\t\t\t\t\t\t\t</table><!-- /column 1 -->\t\n\n\t\t\t\t\t\t\t\t\t\t\t\t<!-- column 2 -->\n\t\t\t\t\t\t\t\t\t\t\t\t<table align='left' class='column'>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<tr>\n\n\t\t\t\t\t\t\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t\t\t\t\t\t</table><!-- /column 2 -->\n\n\t\t\t\t\t\t\t\t\t\t\t\t<span class='clear'></span>\t\n\n\t\t\t\t\t\t\t\t\t\t\t</td>\n\t\t\t\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t\t\t</table><!-- /social & contact -->\n\n\t\t\t\t\t\t\t\t</td>\n\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t</table>\n\t\t\t\t\t</div><!-- /content -->\n\n\t\t\t\t</td>\n\t\t\t\t<td></td>\n\t\t\t</tr>\n\t\t</table><!-- /BODY -->";
$handler = "./print_docs/excel/excel_files/" . $excel_data['file_name'] . ".xls";
// $subject = $excel_title." as at ".$time;
// $email_address = "smutheu@clintonhealthaccess.org,sethrichard40@gmail.com,ttunduny@gmail.com,teddyodera@gmail.com";
// $email_address = $listing_email_address;
$email_address = $cp_emails_str;
$cc_email = $scp_emails_str;
$bcc_email = "smutheu@clintonhealthaccess.org,karsanrichard@gmail.com,ttunduny@gmail.com,teddyodera@gmail.com,ronohb@gmail.com,odiwuorybrian@gmail.com,mwakiojoy@gmail.com,kevgithuka@gmail.com,margie.odora@gmail.com,kiganyastephenthua@gmail.com";
// $email_address = "*****@*****.**";
// $bcc_email = "";
// $cc_email = "";
// $email_address = "karsanrichard@gmail.com,ttunduny@gmail.com";
// $email_address = "*****@*****.**";
//$bcc = "";
if (!empty($facility_count) && $facility_count > 0) {
$status = $this->hcmp_functions->send_email($email_address, $message, $email_subject, $handler, $bcc_email, $cc_email);
}
// echo "I work till here";exit;
// echo "<pre>";print_r($status);exit;
// redirect('sms/new_weekly_usage');
}
public function summary_tab_display($county, $year, $month)
{
// county may be 1 for Nairobi, 5 for busia or 31 for Nakuru
$htmltable = '';
$countyname = counties::get_county_name($county);
$countyname = $countyname[0]['county'];
$ish = $this->rtk_summary_county($county, $year, $month);
$htmltable .= '<tr>
<td rowspan ="' . $ish['districts'] . '">' . $countyname . '';
$total_punctual = 0;
$county_percentage = 0;
foreach ($ish['district_summary'] as $vals) {
$early = $vals['reported'] - $vals['late_reports'];
$total_punctual += $early;
$htmltable .= '
</td><td>' . $vals['district'] . '</td>
<td>' . $vals['total_facilities'] . '</td>
<td>' . $early . '</td>
<td>' . $vals['late_reports'] . '</td>
<td>' . $vals['nonreported'] . '</td>
<td>' . $vals['reported_percentage'] . '%</td></tr>';
}
$county_percentage = ($total_punctual + $ish['late_reports']) / $ish['facilities'] * 100;
$county_percentage = number_format($county_percentage, 0);
$htmltable .= '<tr style="background: #E9E9E3; border-top: solid 1px #ccc;">
<td>Totals</td>
<td>' . $ish['districts'] . ' Sub-Counties</td>
<td>' . $ish['facilities'] . '</td>
<td>' . $total_punctual . '</td>
<td>' . $ish['late_reports'] . '</td>
<td>' . $ish['nonreported'] . '</td>
<td>' . $county_percentage . '%</td>
</tr>';
echo '
<table class="data-table">
<thead><tr>
<th>County</th>
<th>Sub-County</th>
<th>No of facilities</th>
<th>No reports before 10th</th>
<th>No of late reports (10th-12th)</th>
<th>No of non reporting facilities</th>
<th>Overall reporting rate in % (no of reports submitted/expected no of reports)</th>
</tr></thead>
' . $htmltable . '
</table>';
}
public function get_county_ordering_rate_chart()
{
$district = $this->session->userdata('district');
$county_id = districts::get_county_id($district);
$county_name = counties::get_county_name($county_id[0]['county']);
$districts_in_this_county = districts::getDistrict($county_name[0]['id']);
$category_data = '<categories>';
$orders_made_data = "";
$total_no_of_facilities = "";
$strXML = "<chart stack100Percent='1' showPercentValues='1' palette='2' bgColor='FFFFFF' formatNumberScale='0' showBorder='0' showLabels='1' showvalues='0' numberPrefix='' showSum='1' decimals='0' useRoundEdges='1' legendBorderAlpha='0'>";
foreach ($districts_in_this_county as $chart_data) {
$category_data .= "<category label='{$chart_data->district}' />";
$district_data = facilities::get_orders_made_in_district($chart_data->id);
$orders_made_data .= "<set value='{$district_data['orders_made_data']}' />";
$bal = $district_data['total_no_of_facilities'] - $district_data['orders_made_data'];
$total_no_of_facilities .= "<set value='{$bal}'/>";
}
$strXML .= $category_data . "</categories><dataset seriesName='Orders Made' color='659EC7' showValues='0'>{$orders_made_data}</dataset><dataset seriesName='Orders not made' color='E8E8E8' showValues='0'>{$total_no_of_facilities}</dataset></chart>";
echo $strXML;
}
public function orders($year = null, $county_id = null, $district_id = null, $facility_code = null, $graph_type = null)
{
$district_id = $district_id == "NULL" ? null : $district_id;
$graph_type = $graph_type == "NULL" ? null : $graph_type;
$facility_code = $facility_code == "NULL" ? null : $facility_code;
$county_id = $county_id == "NULL" ? null : $county_id;
$year = $year == "NULL" ? date('Y') : $year;
$and_data = $district_id > 0 ? " AND d.id = '{$district_id}'" : null;
$and_data .= $facility_code > 0 ? " AND f.facility_code = '{$facility_code}'" : null;
$and_data .= $county_id > 0 ? " AND c.id='{$county_id}'" : null;
$and_data .= $year > 0 ? " and year(o.`order_date`) ={$year}" : null;
$and_data = isset($year) ? $and_data : null;
//echo ; exit;
$commodity_array = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAll("SELECT \n sum(o.`order_total`) as total,DATE_FORMAT( o.`order_date`, '%b' ) AS cal_month\nFROM\n facilities f, districts d, counties c,`facility_orders` o\nWHERE\n o.facility_code=f.facility_code\n and f.district=d.id and d.county=c.id\n {$and_data}\ngroup by month(o.`order_date`)\n ");
//var_dump($commodity_array);
//exit;
$category_data = array();
$series_data = $series_data_ = array();
$temp_array = $temp_array_ = array();
$graph_data = array();
$title = '';
if ($graph_type != "excel") {
if (isset($county_id)) {
$county_name = counties::get_county_name($county_id);
$name = $county_name[0]['county'];
$title = "{$name} county";
} elseif (isset($district_id)) {
$district_data = isset($district_id) && $district_id > 0 ? districts::get_district_name($district_id)->toArray() : null;
$district_name_ = isset($district_data) ? " :" . $district_data[0]['district'] . " Subcounty" : null;
$title = isset($facility_code) && isset($district_id) ? "{$district_name_} : {$facility_name}" : (isset($district_id) && !isset($facility_code) ? "{$district_name_}" : "{$name} County");
} elseif (isset($facility_code)) {
$facility_code_ = isset($facility_code) ? facilities::get_facility_name_($facility_code) : null;
$title = $facility_code_['facility_name'];
} else {
$title = "National";
}
$arrayseries = array();
foreach ($commodity_array as $data) {
$arrayseries[] = (int) $data['total'];
//$series_data = array_merge($series_data, array($data["drug_name"] => (int)$data['total']));
$category_data = array_merge($category_data, array($data["cal_month"]));
}
$data['graph_type'] = 'spline';
$data['graph_title'] = "{$year} {$title} Order Cost";
$data['graph_yaxis_title'] = "Cost in Ksh";
$data['graph_id'] = "orders";
$data['legend'] = "Ksh";
$data['colors'] = "['#4b0082', '#DDDF00', '#FFF263', '#6AF9C4']";
$data['category_data'] = json_encode($category_data);
$data['series_data'] = json_encode($arrayseries);
return $this->load->view("national/ajax/bar_template", $data);
} else {
$excel_data = array('doc_creator' => "HCMP", 'doc_title' => "{$year} {$title} Order Cost", 'file_name' => "{$year} {$title} Order Cost (KSH)");
$row_data = array();
$column_data = array("Date of Order Placement", "Date of Order Approval", "Total Order Cost (Ksh)", "Date of Delivery", "Lead Time (Order Placement to Delivery)", "Supplier", "Facility Name", "Facility Code", "Sub-County", "County");
$excel_data['column_data'] = $column_data;
//echo ; exit;
$facility_stock_data = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAll("SELECT c.county,d.district as sub_county, f.facility_name, f.facility_code, \n DATE_FORMAT(`order_date`,'%d %b %y') as order_date, \n DATE_FORMAT(`approval_date`,'%d %b %y') as approval_date,\n DATE_FORMAT(`deliver_date`,'%d %b %y') as delivery_date, \n DATEDIFF(`approval_date`,`order_date`) as tat_order_approval,\n DATEDIFF(`deliver_date`,`approval_date`) as tat_approval_deliver,\n DATEDIFF(`deliver_date`,`order_date`) as tat_order_delivery\n , sum(o.`order_total`) as total \n from facility_orders o, facilities f, districts d, counties c \n where f.facility_code=o.facility_code and f.district=d.id \n and c.id=d.county {$and_data}\n group by o.id order by c.county asc ,d.district asc , \n f.facility_name asc \n ");
array_push($row_data, array("The orders below were placed {$year} {$title}"));
foreach ($facility_stock_data as $facility_stock_data_item) {
array_push($row_data, array($facility_stock_data_item["order_date"], $facility_stock_data_item["approval_date"], $facility_stock_data_item["total"], $facility_stock_data_item["delivery_date"], $facility_stock_data_item["tat_order_delivery"], "KEMSA", $facility_stock_data_item["facility_name"], $facility_stock_data_item["facility_code"], $facility_stock_data_item["sub_county"], $facility_stock_data_item["county"]));
}
$excel_data['row_data'] = $row_data;
$this->hcmp_functions->create_excel($excel_data);
}
}
