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 list_facilities()
{
$county_id = $this->session->userdata('county_id');
$data['content_view'] = "subcounty/reports/facility_list_v";
$data['get_facility_data'] = facilities::get_facilities_online_per_district($county_id);
$this->load->view("shared_files/template/template", $data);
}
public function password_recovery()
{
$email = $_POST['username'];
if ($email != NULL) {
$password = '******';
$mycount = User::check_user_exist($email);
if ($mycount > 0) {
$account_details = User::get_user_details($email)->toArray();
$access_level = access_level::get_access_level_name($account_details[0]['usertype_id']);
$access_level = $access_level['level'];
switch ($account_details[0]['usertype_id']) {
case 2:
$facility_name = facilities::get_facility_name_($account_details[0]['facility']);
$user_delegation = "Facility: {$facility_name['facility_name']}";
$user_level = "Facility Level";
break;
case 5:
$facility_name = facilities::get_facility_name_($account_details[0]['facility']);
$user_delegation = "Facility: {$facility_name['facility_name']}";
$user_level = "Facility Level";
break;
case 3:
$district_name = districts::get_district_name_($account_details[0]['district']);
$user_level = "District Level";
$user_delegation = "District: {$district_name['district']}";
break;
default:
break;
}
$subject = "Password reset";
$message = 'Hello ' . $account_details[0]['fname'] . 'you requested for a password reset check you email address for more details (HCMP)';
$message_1 = 'Hello ' . $account_details[0]['fname'] . ', <br> <br> You requested for a password reset on the Health Commodities Management Platform (HCMP).
<br>
<br>
Web link: http://health-cmp.or.ke/
<br>
<br>
Please find your log in credentials below:
<br>
<br>
' . $user_delegation . '
<br>
User Level: ' . $user_level . '
<br>
User Type: ' . $access_level . '
<br>
User Name: ' . $email . '
<br>
Password: '******'
<br>
<br>';
//hash then reset password
$salt = '#*seCrEt!@-*%';
$value = md5($salt . $password);
$updatep = Doctrine_Manager::getInstance()->getCurrentConnection();
$updatep->execute("UPDATE user SET password='******' WHERE username='******' or email='{$email}'; ");
//send mail
$response = $this->send_email($email, $message_1, $subject);
$this->send_sms($account_details[0]['telephone'], $message);
$data['email'] = $email;
$data['popup'] = "Successpopup";
$this->load->view("login_v", $data);
} else {
$data['popup'] = "errorpopup";
$this->load->view("forgotpassword_v", $data);
}
} else {
$data['popup'] = "errorpopup";
$this->load->view("forgotpassword_v", $data);
}
}
public function send_order_approval_email($message, $subject, $attach_file, $facility_code, $reject_order = null)
{
//set cc email as blank
$cc_email = "";
$bcc_email = "*****@*****.**";
/*
$bcc_email = 'kelvinmwas@gmail.com,smutheu@clintonhealthaccess.org,collinsojenge@gmail.com,tngugi@clintonhealthaccess.org,
bwariari@clintonhealthaccess.org,
amwaura@clintonhealthaccess.org,
eongute@clintonhealthaccess.org,
rkihoto@clintonhealthaccess.org';
*/
$data = facilities::get_facility_name_($facility_code)->toArray();
$data = $data[0];
if ($reject_order == "Rejected" || $reject_order == "Updated") {
$email_address = $this->get_facility_email($facility_code);
$cc_email .= $this->get_ddp_email($data['district']);
} else {
$email_address = 'shamim.kuppuswamy@kemsa.co.ke,
jmunyu@kemsa.co.ke,
imugada@kemsa.co.ke,
laban.okune@kemsa.co.ke,
samuel.wataku@kemsa.co.ke,';
$cc_email .= $this->get_ddp_email($data['district']);
$cc_email .= $this->get_facility_email($facility_code);
$cc_email .= $this->get_county_email($data['district']);
}
return $this->send_email(substr($email_address, 0, -1), $message, $subject, $attach_file, $bcc_email, substr($cc_email, 0, -1));
}
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 facilities_json()
{
echo json_encode(facilities::getAll_json());
}
public function save()
{
/*
* Get posted data from the client
*/
$balance = "";
$facility = $this->session->userdata("facility");
$facility_detail = facilities::getSupplier($facility);
$supplier_name = $facility_detail->supplier->name;
$get_user = $this->session->userdata("user_id");
$cdrr_id = $this->input->post("cdrr_id");
$get_qty_choice = $this->input->post("quantity_choice");
$get_qty_out_choice = $this->input->post("quantity_out_choice");
$get_source = $this->input->post("source");
$get_source_name = $this->input->post("source_name");
$get_destination_name = $this->input->post("destination_name");
$get_destination = $this->input->post("destination");
$get_transaction_date = date('Y-m-d', strtotime($this->input->post("transaction_date")));
$get_ref_number = $this->input->post("reference_number");
$get_transaction_type = $this->input->post("transaction_type");
$transaction_type_name = $this->input->post("trans_type");
$transaction_effect = $this->input->post("trans_effect");
$get_drug_id = $this->input->post("drug_id");
$get_batch = $this->input->post("batch");
$get_expiry = $this->input->post("expiry");
$get_packs = $this->input->post("packs");
$get_qty = $this->input->post("quantity");
$get_available_qty = $this->input->post("available_qty");
$get_unit_cost = $this->input->post("unit_cost");
$get_amount = $this->input->post("amount");
$get_comment = $this->input->post("comment");
$get_stock_type = $this->input->post("stock_type");
$stock_type_name = $this->input->post("stock_transaction");
//Name of kind of transaction being carried
$all_drugs_supplied = $this->input->post("all_drugs_supplied");
$time_stamp = $this->input->post("time_stamp");
$email = $this->input->post("emailaddress");
$balance = 0;
$pharma_balance = 0;
$store_balance = 0;
$sql_queries = "";
$source_destination = $this->input->post("source_destination");
$check_optgroup = $this->input->post("optgroup");
//Check if store selected as source or destination
$source_dest_type = '';
$running_balance = 0;
$other_running_balance = 0;
//For other store
// If email is not empty
if ($email != "") {
$this->sendemail($email);
}
// STEP 1, GET BALANCES FROM DRUG STOCK BALANCE TABLE
//Get running balance in drug stock movement
$sql_run_balance = $this->db->query("SELECT machine_code as balance FROM drug_stock_movement WHERE drug ='{$get_drug_id}' AND ccc_store_sp ='{$get_stock_type}' AND expiry_date >=CURDATE() ORDER BY id DESC LIMIT 1");
$run_balance_array = $sql_run_balance->result_array();
if (count($run_balance_array) > 0) {
$run_balance = $run_balance_array[0]["balance"];
} else {
//If drug does not exist, initialise the balance to zero
$run_balance = 0;
}
//If transaction has positive effect to current transaction type
if (stripos($transaction_type_name, "received") === 0 || stripos($transaction_type_name, "balance") === 0 || stripos($transaction_type_name, "returns") === 0 && $transaction_effect == 1 || stripos($transaction_type_name, "adjustment") === 0 && $transaction_effect == 1 || stripos($transaction_type_name, "startingstock") === 0 || stripos($transaction_type_name, "physicalcount") === 0) {
$source_dest_type = $get_source;
//Get remaining balance for the drug
$get_balance_sql = $this->db->query("SELECT dsb.balance FROM drug_stock_balance dsb WHERE dsb.facility_code='{$facility}' AND dsb.stock_type='{$get_stock_type}' AND dsb.drug_id='{$get_drug_id}' AND dsb.batch_number='{$get_batch}' AND dsb.balance>0 AND dsb.expiry_date>=CURDATE() AND dsb.expiry_date='{$get_expiry}' LIMIT 1");
$balance_array = $get_balance_sql->result_array();
//Check if drug exists in the drug_stock_balance table
if (count($balance_array) > 0) {
$bal = $balance_array[0]["balance"];
} else {
//If drug does not exist, initialise the balance to zero
$bal = 0;
}
//If many transactions from the same drug, set balances to zero only once
if ($this->session->userdata("updated_dsb") && $this->session->userdata("updated_dsb") == $get_drug_id) {
} else {
//If transaction is physical count, set actual quantity as physical count
if (stripos($transaction_type_name, "startingstock") === 0 || stripos($transaction_type_name, "physicalcount") === 0) {
$bal = 0;
$run_balance = 0;
//Set all balances fro each batch of the drug to be zero in drug_stock_balance for physical count transaction type
$sql = "UPDATE drug_stock_balance SET balance =0 WHERE drug_id='{$get_drug_id}' AND stock_type='{$get_stock_type}' AND facility_code='{$facility}'";
$set_bal_zero = $this->db->query($sql);
$this->session->set_userdata("updated_dsb", $get_drug_id);
}
}
//If stock coming in from another store, get current store
if ($check_optgroup == 'Stores') {
$source_dest_type = $get_source;
//If transaction type is returns from(+),
if (stripos($transaction_type_name, "returns") === 0 && $transaction_effect == 1) {
$source_dest_type = $get_destination;
}
//Get remaining balance for the drug
$get_balance_sql = $this->db->query("SELECT dsb.balance FROM drug_stock_balance dsb \n\t\t\t\tWHERE dsb.facility_code='{$facility}' AND dsb.stock_type='" . $source_dest_type . "' AND dsb.drug_id='{$get_drug_id}' AND dsb.batch_number='{$get_batch}' \n\t\t\t\tAND dsb.balance>0 AND dsb.expiry_date>=CURDATE() AND dsb.expiry_date='{$get_expiry}' LIMIT 1");
$balance_array = $get_balance_sql->result_array();
//Check if drug exists in the drug_stock_balance table
if (count($balance_array) > 0) {
$bal_pharma = $balance_array[0]["balance"];
} else {
//If drug does not exist, initialise the balance to zero
$bal_pharma = 0;
}
//Get running balance in drug stock movement
$sql_run_balance = $this->db->query("SELECT machine_code as balance FROM drug_stock_movement WHERE drug ='{$get_drug_id}' AND ccc_store_sp ='{$source_dest_type}' AND expiry_date >=CURDATE() ORDER BY id DESC LIMIT 1");
$run_balance_array = $sql_run_balance->result_array();
if (count($run_balance_array) > 0) {
$other_run_balance = $run_balance_array[0]["balance"];
} else {
//If drug does not exist, initialise the balance to zero
$other_run_balance = 0;
}
$pharma_balance = $bal_pharma - $get_qty;
//New balance
$other_running_balance = $other_run_balance - $get_qty;
}
$balance = $get_qty + $bal;
//Current store balance
$running_balance = $get_qty + $run_balance;
} else {
//If transaction has negative effect (Issuing, returns(-) ...)
//If issuing to a store(Pharmacy or Main Store), get remaining balance in destination
if ($check_optgroup == 'Stores') {
$source_dest_type = $get_destination;
//If transaction type is returns to(-), get use source instead of destination as where the transaction came from
if (stripos($transaction_type_name, "returns") === 0 && $transaction_effect == 0) {
$source_dest_type = $get_source;
}
//Get remaining balance for the drug
$get_balance_sql = $this->db->query("SELECT dsb.balance FROM drug_stock_balance dsb \n\t\t\t\tWHERE dsb.facility_code='{$facility}' AND dsb.stock_type='" . $source_dest_type . "' AND dsb.drug_id='{$get_drug_id}' AND dsb.batch_number='{$get_batch}' \n\t\t\t\tAND dsb.balance>0 AND dsb.expiry_date>=CURDATE() AND dsb.expiry_date='{$get_expiry}' LIMIT 1");
$balance_array = $get_balance_sql->result_array();
//Check if drug exists in the drug_stock_balance table
if (count($balance_array) > 0) {
$bal_pharma = $balance_array[0]["balance"];
} else {
//If drug does not exist, initialise the balance to zero
$bal_pharma = 0;
}
//Get running balance in drug stock movement
$sql_run_balance = $this->db->query("SELECT machine_code as balance FROM drug_stock_movement WHERE drug ='{$get_drug_id}' AND ccc_store_sp ='{$source_dest_type}' AND expiry_date >=CURDATE() ORDER BY id DESC LIMIT 1");
$run_balance_array = $sql_run_balance->result_array();
if (count($run_balance_array) > 0) {
$other_run_balance = $run_balance_array[0]["balance"];
} else {
//If drug does not exist, initialise the balance to zero
$other_run_balance = 0;
}
$pharma_balance = $bal_pharma + $get_qty;
//New balance
$other_running_balance = $other_run_balance + $get_qty;
}
//Substract balance from qty going out
$balance = $get_available_qty - $get_qty;
$running_balance = $run_balance - $get_qty;
}
/*
* Get transaction source and destination depending on type of transaction
*/
// STEP 2, SET SOURCE AND DESTINATION
//Check if stock type is store or pharmacy
$s_d = "";
if ($check_optgroup == 'Stores') {
$source_destination = $get_source_name;
if (stripos($stock_type_name, "pharmacy")) {
//If pharmacy transaction, source and destinations is facility code
$source = $facility;
$destination = $facility;
//Check if transaction is coming in or going out to find what to put in source and destination
//If transaction is coming, destination is current store
if ($transaction_effect == 1) {
$source_destination = $get_source_name;
if (stripos($transaction_type_name, "returns") === 0 && $transaction_effect == 1) {
//If transaction is returns from(+), source is current store
$source_destination = $get_destination_name;
}
} else {
if ($transaction_effect == 0) {
//If transaction is going out, current store is sources
$source_destination = $get_destination_name;
if (stripos($transaction_type_name, "returns") === 0 && $transaction_effect == 0) {
//If transaction is returns from(-), destination is current store
$source_destination = $get_source_name;
}
} else {
//Transaction does not have effect ( Error)
$time = date("Y-m-d H:is:s");
$error[] = 'An error occured while saving your data ! No transaction effect found! (' . $time . ')';
}
}
} elseif (stripos($stock_type_name, "store")) {
//If store transaction, source or destination is facility code
//Check if transaction is coming in or going out to find what to put in source and destination
//If transaction is coming, destination is current store
if ($transaction_effect == 1) {
//If transaction is coming in, destination is current store
$source = $get_source_name;
$destination = $facility;
$source_destination = $get_source_name;
if (stripos($transaction_type_name, "returns") === 0 && $transaction_effect == 1) {
//If transaction is returns from(+), source is current store
$source = $facility;
$destination = $get_destination_name;
$source_destination = $get_destination_name;
}
} else {
if ($transaction_effect == 0) {
//If transaction is going out, current store is sources
$source = $facility;
$destination = $get_destination_name;
$source_destination = $get_destination_name;
if (stripos($transaction_type_name, "returns") === 0 && $transaction_effect == 0) {
//If transaction is returns from(-), destination is current store
$source = $get_source_name;
$destination = $facility;
$source_destination = $get_source_name;
}
} else {
//Transaction does not have effect ( Error)
$time = date("Y-m-d H:is:s");
$error[] = 'An error occured while saving your data ! No transaction effect found! (' . $time . ')';
}
}
}
} else {
if (stripos($stock_type_name, "pharmacy")) {
//If pharmacy transaction, source and destinations is facility code
$source = $facility;
$destination = $facility;
if ($transaction_effect == 1) {
$source_destination = $get_source;
$s_d = 's';
if (stripos($transaction_type_name, "returns") === 0 && $transaction_effect == 1) {
//If transaction is returns from(+), source is current store
$source_destination = $get_destination;
$s_d = 'd';
}
} else {
if ($transaction_effect == 0) {
//If transaction is going out, current store is sources
$source_destination = $get_destination;
$s_d = 'd';
if (stripos($transaction_type_name, "returns") === 0 && $transaction_effect == 0) {
//If transaction is returns from(-), destination is current store
$source_destination = $get_source;
$s_d = 's';
}
} else {
//Transaction does not have effect ( Error)
$time = date("Y-m-d H:is:s");
$error[] = 'An error occured while saving your data ! No transaction effect found! (' . $time . ')';
}
}
} elseif (stripos($stock_type_name, "store")) {
//If store transaction, source or destination is facility code
if ($transaction_effect == 1) {
//If transaction is coming in, destination is current store
$source = $get_source;
$destination = $facility;
$source_destination = $get_source;
$s_d = 's';
if (stripos($transaction_type_name, "returns") === 0 && $transaction_effect == 1) {
//If transaction is returns from(+), source is current store
$source = $facility;
$destination = $get_destination;
$source_destination = $get_destination;
$s_d = 'd';
}
} else {
if ($transaction_effect == 0) {
//If transaction is going out, current store is sources
$source = $facility;
$destination = $get_destination;
$source_destination = $get_destination;
$s_d = 'd';
if (stripos($transaction_type_name, "returns") === 0 && $transaction_effect == 0) {
//If transaction is returns from(-), destination is current store
$source = $get_source;
$destination = $facility;
$source_destination = $get_source;
$s_d = 's';
}
} else {
//Transaction does not have effect ( Error)
$time = date("Y-m-d H:is:s");
$error[] = 'An error occured while saving your data ! No transaction effect found! (' . $time . ')';
}
}
}
}
//Sanitize by removing (store) or (pharmacy)
$source_destination = str_ireplace('(store)', '', $source_destination);
$source_destination = str_ireplace('(pharmacy)', '', $source_destination);
//If source or destination is central site or satellite, insert exact name instead of IDs
if ($check_optgroup == 'Central Site' || $check_optgroup == 'Satelitte Sites') {
if ($s_d == 'd') {
$source_destination = $get_destination_name;
} elseif ($s_d == 's') {
$source_destination = $get_source_name;
}
}
//echo json_encode($running_balance ." -- ".$other_running_balance);die();
//echo json_encode($source_destination);die();
// STEP 3, INSERT TRANSACTION IN DRUG STOCK MOVEMENT FOR CURRENT STORES
$drug_stock_mvt_transact = array('drug' => $get_drug_id, 'transaction_date' => $get_transaction_date, 'batch_number' => $get_batch, 'transaction_type' => $get_transaction_type, 'source' => $source, 'destination' => $destination, 'expiry_date' => $get_expiry, 'packs' => $get_packs, $get_qty_choice => $get_qty, $get_qty_out_choice => '0', 'balance' => $balance, 'unit_cost' => $get_unit_cost, 'amount' => $get_amount, 'remarks' => $get_comment, 'operator' => $get_user, 'order_number' => $get_ref_number, 'facility' => $facility, 'Source_Destination' => $source_destination, 'timestamp' => $time_stamp, 'machine_code' => $running_balance, 'ccc_store_sp' => $get_stock_type);
$this->db->insert('drug_stock_movement', $drug_stock_mvt_transact);
//check if query inserted
$inserted = $this->db->affected_rows();
if ($inserted < 1) {
//If query did not insert
$time = date("Y-m-d H:is:s");
$errNo = $this->db->_error_number();
$errMess = $this->db->_error_message();
$remaining_drugs = $this->input->post("remaining_drugs");
$error[] = 'An error occured while saving your data(Drug Transaction 1) ! Error ' . $errNo . ' : ' . $errMess . ' (' . $time . ')';
echo json_encode($error);
die;
}
//STEP 4, UPDATE DRUG STOCK BALANCE FOR CURRENT STORE
if ($transaction_effect == 1) {
$balance_sql = "INSERT INTO drug_stock_balance(drug_id,batch_number,expiry_date,stock_type,facility_code,balance,ccc_store_sp) VALUES('" . $get_drug_id . "','" . $get_batch . "','" . $get_expiry . "','" . $get_stock_type . "','" . $facility . "','" . $get_qty . "','" . $get_stock_type . "') ON DUPLICATE KEY UPDATE balance=balance + " . $get_qty . ";";
if (stripos($transaction_type_name, "physical")) {
//Physical Count
$balance_sql = "INSERT INTO drug_stock_balance(drug_id,batch_number,expiry_date,stock_type,facility_code,balance,ccc_store_sp) VALUES('" . $get_drug_id . "','" . $get_batch . "','" . $get_expiry . "','" . $get_stock_type . "','" . $facility . "','" . $get_qty . "','" . $get_stock_type . "') ON DUPLICATE KEY UPDATE balance=" . $get_qty . ";";
}
} else {
if ($transaction_effect == 0) {
$balance_sql = "UPDATE drug_stock_balance SET balance=balance - " . $get_qty . " WHERE drug_id='" . $get_drug_id . "' AND batch_number='" . $get_batch . "' AND expiry_date='" . $get_expiry . "' AND stock_type='" . $get_stock_type . "' AND facility_code='" . $facility . "';";
}
}
$sql_dsb_current_store = $this->db->query($balance_sql);
$inserted = $this->db->affected_rows();
if ($inserted < 1) {
//If query did not insert
$time = date("Y-m-d H:is:s");
$errNo = $this->db->_error_number();
$errMess = $this->db->_error_message();
$remaining_drugs = $this->input->post("remaining_drugs");
$error[] = 'An error occured while saving your data (Drug Balance)! Error ' . $errNo . ' : ' . $errMess . ' (' . $time . ')';
echo json_encode($error);
die;
}
//STEP 5, IF STORE TRANSACTIONS, UPDATE OTHER STORE DETAILS
if ($check_optgroup == 'Stores') {
// If transaction if from one store to another, update drug stock balance in the other store
//STEP 6, UPDATE DRUG STOCK MOVEMENT FOR THE OTHER STORE
if (stripos($source_destination, "pharmacy")) {
//If pharmacy transaction, source and destinations is facility code
$source = $facility;
$destination = $facility;
}
$source_destination = $stock_type_name;
//Get corresponding transaction types
$sql = "";
if (stripos($transaction_type_name, "receive") === 0) {
//If transaction is received, insert an issued to
$sql = "SELECT id FROM transaction_type WHERE name LIKE '%issued%' LIMIT 1";
} else {
if (stripos($transaction_type_name, "issued") === 0) {
//Issued, insert a received
$sql = "SELECT id FROM transaction_type WHERE name LIKE '%received%' LIMIT 1";
} else {
if (stripos($transaction_type_name, "returns") === 0 && $transaction_effect == 1) {
//Returns froms(+), insert an returns to (-)
$sql = "SELECT id FROM transaction_type WHERE name LIKE '%returns%' AND effect='0' LIMIT 1";
} else {
if (stripos($transaction_type_name, "returns") === 0 && $transaction_effect == 0) {
//Returns to(-), insert an returns from (+)
$sql = "SELECT id FROM transaction_type WHERE name LIKE '%returns%' AND effect='1' LIMIT 1";
}
}
}
}
$get_trans_id = $this->db->query($sql);
$get_trans_id = $get_trans_id->result_array();
$transaction_type = $get_trans_id[0]['id'];
//Sanitize by removing (store) or (pharmacy)
$source_destination = str_ireplace('(store)', '', $source_destination);
$source_destination = str_ireplace('(pharmacy)', '', $source_destination);
$drug_stock_mvt_other_trans = array('drug' => $get_drug_id, 'transaction_date' => $get_transaction_date, 'batch_number' => $get_batch, 'transaction_type' => $transaction_type, 'source' => $source, 'destination' => $destination, 'expiry_date' => $get_expiry, 'packs' => $get_packs, $get_qty_choice => '0', $get_qty_out_choice => $get_qty, 'balance' => $pharma_balance, 'unit_cost' => $get_unit_cost, 'amount' => $get_amount, 'remarks' => $get_comment, 'operator' => $get_user, 'order_number' => $get_ref_number, 'facility' => $facility, 'Source_Destination' => $source_destination, 'timestamp' => $time_stamp, 'machine_code' => $other_running_balance, 'ccc_store_sp' => $source_dest_type);
$this->db->insert('drug_stock_movement', $drug_stock_mvt_other_trans);
//echo json_encode($source_destination);die();
//check if query inserted
$inserted = $this->db->affected_rows();
if ($inserted < 1) {
//If query did not insert
$time = date("Y-m-d H:is:s");
$errNo = $this->db->_error_number();
$errMess = $this->db->_error_message();
$remaining_drugs = $this->input->post("remaining_drugs");
$error[] = 'An error occured while saving your data(Drug Transaction 2) ! Error ' . $errNo . ' : ' . $errMess . ' (' . $time . ')';
echo json_encode($error);
die;
}
//STEP 7, UPDATE DRUG STOCK BALANCE FOR THE OTHER STORE
//If transaction has a positive effect on current store, it will have a negative effect on the other store
if ($transaction_effect == 1) {
//If transaction has a positive effect, substract balance in the other store
$balance_sql = "UPDATE drug_stock_balance SET balance=balance - " . $get_qty . " WHERE drug_id='" . $get_drug_id . "' AND batch_number='" . $get_batch . "' AND expiry_date='" . $get_expiry . "' AND stock_type='" . $get_source . "' AND facility_code='" . $facility . "';";
if (stripos($transaction_type_name, "returns") === 0 && $transaction_effect == 1) {
//If returns from(+), substract from other store
$balance_sql = "INSERT INTO drug_stock_balance(drug_id,batch_number,expiry_date,stock_type,facility_code,balance,ccc_store_sp) VALUES('" . $get_drug_id . "','" . $get_batch . "','" . $get_expiry . "','" . $get_destination . "','" . $facility . "','" . $get_qty . "','" . $get_stock_type . "') ON DUPLICATE KEY UPDATE balance=balance - " . $get_qty . ";";
}
} else {
if ($transaction_effect == 0) {
//If transaction has negative effect, add to balance in the other store
$balance_sql = "INSERT INTO drug_stock_balance(drug_id,batch_number,expiry_date,stock_type,facility_code,balance,ccc_store_sp) VALUES('" . $get_drug_id . "','" . $get_batch . "','" . $get_expiry . "','" . $get_destination . "','" . $facility . "','" . $get_qty . "','" . $get_stock_type . "') ON DUPLICATE KEY UPDATE balance=balance + " . $get_qty . ";";
if (stripos($transaction_type_name, "returns") === 0 && $transaction_effect == 0) {
//If returns to(-), add to drug stock balance in the other store
$balance_sql = "UPDATE drug_stock_balance SET balance=balance + " . $get_qty . " WHERE drug_id='" . $get_drug_id . "' AND batch_number='" . $get_batch . "' AND expiry_date='" . $get_expiry . "' AND stock_type='" . $get_source . "' AND facility_code='" . $facility . "';";
}
}
}
$sql_dsb_store = $this->db->query($balance_sql);
$inserted = $this->db->affected_rows();
if ($inserted < 1) {
//If query did not insert
$time = date("Y-m-d H:is:s");
$errNo = $this->db->_error_number();
$errMess = $this->db->_error_message();
$remaining_drugs = $this->input->post("remaining_drugs");
$error[] = 'An error occured while saving your data(Drug Balance 2) ! Error ' . $errNo . ' : ' . $errMess . ' (' . $time . ')';
echo json_encode($error);
die;
}
}
//Check if transaction came from picking list and not all drugs where supplied
if ($all_drugs_supplied == 0) {
//Update supplied drugs
$sql = "UPDATE cdrr_item SET publish='1' WHERE id='{$cdrr_id}'";
$this->db->query($sql);
}
//Get drug_name
$drug_det = Drugcode::getDrugCodeHydrated($get_drug_id);
$drug_name = $drug_det[0]['Drug'];
echo json_encode($drug_name);
die;
}
public function county_profile($county)
{
$data = array();
$lastday = date('Y-m-d', strtotime("last day of previous month"));
$County = $this->session->userdata('county_name');
$Countyid = $this->session->userdata('county_id');
$districts = districts::getDistrict($Countyid);
$facility = facilities::get_facility_name_($mfl);
$data['county'] = $County;
$data['countyid'] = $Countyid;
$data['title'] = 'Facility Profile: ' . $facility['facility_name'];
$data['banner_text'] = 'Facility Profile: ' . $facility['facility_name'];
$data['content_view'] = "rtk/rtk/clc/county_profile_view";
$this->load->view("rtk/template", $data);
}
public function send_order_approval_email($message, $subject, $attach_file, $facility_code, $reject_order = null)
{
$cc_email = "";
$data = facilities::get_facility_name_($facility_code)->toArray();
$data = $data[0];
if ($reject_order == "Rejected" || $reject_order == "Updated") {
$email_address = $this->get_facility_email($facility_code);
$cc_email .= $this->get_ddp_email($data['district']);
} else {
$email_address = $this->test_mode ? 'kelvinmwas@gmail.com,smutheu@clintonhealthaccess.org,collinsojenge@gmail.com,
' : 'shamim.kuppuswamy@kemsa.co.ke,
samuel.wataku@kemsa.co.ke,
jmunyu@kemsa.co.ke,
imugada@kemsa.co.ke,
laban.okune@kemsa.co.ke,
samuel.wataku@kemsa.co.ke,';
$cc_email .= $this->get_ddp_email($data['district']);
$cc_email .= $this->get_facility_email($facility_code);
$cc_email .= $this->get_county_email($data['district']);
}
return $this->send_email(substr($email_address, 0, -1), $message, $subject, $attach_file, null, substr($cc_email, 0, -1));
}
public function get_district_facility_mapping_($district_id)
{
$facility_data = facilities::getFacilities($district_id);
$dpp_details = Users::get_dpp_details($district_id)->toArray();
$district_name = districts::getDistrict(null, $district_id)->toArray();
$table_body = "";
$dpp_fname = '';
$dpp_lname = '';
$dpp_phone = '';
$dpp_email = '';
$indicator = "SubCounty";
$no_of_facility_users = 0;
$no_of_facility_users_online = 0;
$no_of_facilities = 0;
$no_of_facilities_using = 0;
$no_of_facilities_using_targetted = 0;
$series_data = $graph_data = array();
if (count($dpp_details) > 0) {
$dpp_fname = $dpp_details[0]['fname'];
$dpp_lname = $dpp_details[0]['lname'];
$dpp_phone = $dpp_details[0]['telephone'];
$dpp_email = $dpp_details[0]['email'];
}
foreach ($facility_data as $facility_detail) {
$facility_code = $facility_detail->facility_code;
$facility_extra_data = facilities::get_facility_status_no_users_status($facility_code);
$no_of_facility_users = $no_of_facility_users + $facility_extra_data[0]['number_of_users'];
$no_of_facility_users_online = $no_of_facility_users_online + $facility_extra_data[0]['number_of_users_online'];
$no_of_facilities = $no_of_facilities + 1;
$using = $facility_detail->using_hcmp;
$status_radio = $facility_detail->targetted == 1 ? 'checked="true"' : null;
$using == 1 ? $no_of_facilities_using = $no_of_facilities_using + 1 : ($status = null);
$temp = $facility_extra_data[0]['status'];
$status_using = $using == 1 ? 'checked="true"' : null;
$a_date = strtotime($facility_detail->date_of_activation) ? date('d M, Y', strtotime($facility_detail->date_of_activation)) : "N/A";
$using == 1 ? $status = "<span class='label label-success'>Active</span>" : ($status = "<span class='label label-warning'>Inactive</span>");
$facility_detail->targetted == 1 ? $no_of_facilities_using_targetted = $no_of_facilities_using_targetted + 1 : null;
array_push($series_data, array($district_name[0]['district'], $facility_detail->facility_name, $facility_code, $facility_detail->owner, $status, "<input id='{$facility_detail->id}' type='checkbox' name='targetted' class='checkbox' {$status_radio}/>", "<input id='{$facility_detail->id}' name='using_hcmp' type='checkbox' class='checkbox' {$status_using}/>", $a_date, $facility_extra_data[0]['number_of_users']));
}
$stats_data = '
<table style="float:left">
<tr>
<td><label style=" font-weight: ">' . $district_name[0]['district'] . ' ' . $indicator . ' Pharmacist :</label></td>
<td><a class="badge">' . $dpp_fname . ' ' . $dpp_lname . '</a></td>
</tr>
<tr>
<td><label style="font-weight: ">Phone No.</label></td>
<td><a class="badge">' . $dpp_phone . '</a></td>
</tr>
<tr>
<td><label style="font-weight: ">Email Address</label></td>
<td><a class="badge">' . $dpp_email . '</a></td>
</tr>
</table>
<table style="float:left">
<tr>
<td><label style=" font-weight: ">Total No of Facilities</label></td>
<td><a class="badge" >' . $no_of_facilities . '</a></td>
</tr>
<tr>
<td><label style="font-weight: ">Total No of Facilities Targeted</label></td>
<td> <a class="badge">' . $no_of_facilities_using_targetted . '</a></td>
</tr>
<tr>
<td><label style="font-weight: ">Total No of Facilities Using HCMP </label></td>
<td> <a class="badge">' . $no_of_facilities_using . '</a></td>
</tr>
<tr>
<td><label style="font-weight: ">Total No of Users</label></td>
<td><a class="badge" >' . $no_of_facility_users . '</a></td>
</tr>
</table>
</br><p>';
$category_data = array(array("Sub County", 'Facility Name', "MLF No", "Owner", "Facility Status", "Targeted For Roll Out", "Using HCMP", "Date Activated", "No. Facility Users"));
$graph_data = array_merge($graph_data, array("table_id" => 'dem_graph_1'));
$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_1";
$data['stats_data'] = $stats_data;
return $this->load->view("shared_files/report_templates/data_table_template_v", $data);
}
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);
}
}
