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); } }