public static function update_temp($data_array)
{
$o = new facility_stocks_temp();
$o->fromArray($data_array);
$o->save();
return TRUE;
}
public function get_county_stock_level_new($commodity_id = null, $category_id = null, $district_id = null, $facility_code = null, $option = null, $report_type = null, $tracer = null)
{
//reset the values here
$tracer = isset($tracer) ? $tracer : null;
$report_type = isset($report_type) ? $report_type : null;
$commodity_id = $commodity_id == "NULL" ? null : $commodity_id;
$district_id = $district_id == "NULL" ? null : $district_id;
$option = $option == "NULL" ? null : $option;
$category_id = $category_id == "NULL" ? null : $category_id;
$facility_code = $facility_code == "NULL" ? null : $facility_code;
$option = $option == "NULL" || $option == "null" ? null : $option;
$county_id = $this->session->userdata('county_id');
$county_name = counties::get_county_name($county_id);
$category_data = $series_data = $series_data_ = $graph_data = $data = array();
$title = '';
$year = date('Y');
$month_ = date('M d');
if ($option == "mos" && $report_type == '1') {
return $this->load_stock_level_graph($district_id, $county_id, $facility_code, $commodity_id, $report_type, $tracer);
}
//check if the district is set
$district_data = isset($district_id) && $district_id > 0 ? districts::get_district_name($district_id)->toArray() : null;
$district_name_ = isset($district_data) ? " :" . $district_data[0]['district'] . " subcounty" : null;
$option_new = isset($option) ? $option : "Ksh";
$option_title = isset($option) ? $option : "Ksh";
$facility_code_ = isset($facility_code) ? facilities::get_facility_name_($facility_code)->toArray() : null;
$facility_name = $facility_code_[0]['facility_name'];
$commodity_name = isset($commodity_id) ? Commodities::get_details($commodity_id)->toArray() : null;
$category_name_ = @$commodity_name[0]['commodity_name'];
$commodity_name = isset($category_name_) ? " for " . $category_name_ : null;
$title = isset($facility_code) && isset($district_id) ? "{$district_name_} : {$facility_name}" : (isset($district_id) && !isset($facility_code) ? "{$district_name_}" : "{$county_name['county']} county");
$commodity_array = facility_stocks::get_county_drug_stock_level_new($facility_code, $district_id, $county_id, $category_id, $commodity_id, $option_new, $report_type);
$mos_array = facility_stocks_temp::get_months_of_stock($district_id, $county_id, $facility_code, $commodity_id, $report_type, $tracer);
//echo "<pre>";print_r($commodity_array);echo "</pre>";exit;
foreach ($commodity_array as $data) {
if ($report_type == "table_data") {
if ($commodity_id > 0) {
array_push($series_data, array($data['district'], $data["facility_name"], $data["facility_code"], (int) $data['total']));
} else {
array_push($series_data, array($data["commodity_name"], $data["facility_name"], $data["name"], (int) $data['total']));
}
} else {
$series_data = array_merge($series_data, array((int) $data['total']));
$series_data_ = array_merge($series_data_, array(array($data['district'], $data["facility_name"], $data["facility_code"], $data["commodity_name"], (int) $data['total'])));
$category_data = array_merge($category_data, array($data["commodity_name"]));
}
}
if ($report_type == "table_data") {
if ($commodity_id > 0) {
$category_data = array(array("Sub-county", "Facility Name", "Mfl", "TOTAL " . $option_new));
} else {
array_push($category_data, array("Commodity Name", "Facility Name", "Sub-county Name", "stocks worth in {$option_title} "));
}
$graph_data = array_merge($graph_data, array("table_id" => 'dem_graph_'));
$graph_data = array_merge($graph_data, array("table_header" => $category_data));
$graph_data = array_merge($graph_data, array("table_body" => $series_data));
$data['table'] = $this->hcmp_functions->create_data_table($graph_data);
$data['table_id'] = "dem_graph_";
return $this->load->view("shared_files/report_templates/data_table_template_v", $data);
} elseif ($report_type == "csv_data") {
$excel_data = array('doc_creator' => $this->session->userdata('full_name'), 'doc_title' => "Stock level {$commodity_name} {$title} {$month_} {$year}", 'file_name' => "Stock_level_{$commodity_name_}{$title_}{$month_}{$year}");
$row_data = array();
$column_data = array("Sub-county Name", "Facility Name", "MFL Code", "Commodity Name", "stocks worth in {$option_title} ");
$excel_data['column_data'] = $column_data;
$row_data = array_merge($row_data, $series_data_);
//echo "<pre>";print_r($mos_array);echo "</pre>";exit;
$excel_data['row_data'] = $row_data;
$this->hcmp_functions->create_excel($excel_data);
} else {
//echo "<pre>";print_r($category_data);echo "</pre>";exit;
$graph_type = 'bar';
$graph_data = array_merge($graph_data, array("graph_id" => 'dem_graph_'));
$graph_data = array_merge($graph_data, array("graph_title" => "Stock Level {$commodity_name} for {$title} as at {$month_} {$year}"));
$graph_data = array_merge($graph_data, array("graph_type" => $graph_type));
$graph_data = array_merge($graph_data, array("graph_yaxis_title" => "Commodity Stock level in {$option_title}"));
$graph_data = array_merge($graph_data, array("graph_categories" => $category_data));
$graph_data = array_merge($graph_data, array("series_data" => array('total' => $series_data)));
//echo $category_data;
$data['graph_id'] = 'dem_graph_';
$data['high_graph'] = $this->hcmp_functions->create_high_chart_graph($graph_data);
return $this->load->view("shared_files/report_templates/high_charts_template_v", $data);
}
}
public function add_stock_level()
{
if ($this->input->post('commodity_id')) {
$facility_code = $this->session->userdata('facility_id');
$form_type = $this->input->post('form_type');
$commodity_id = array_values($this->input->post('desc'));
//this rearranges the array such that the index starts at 0
$expiry_date = array_values($this->input->post('clone_datepicker'));
$batch_no = array_values($this->input->post('commodity_batch_no'));
$manu = array_values($this->input->post('commodity_manufacture'));
$total_unit_count = array_values($this->input->post('commodity_total_units'));
$source_of_item = array_values($this->input->post('source_of_item'));
$date_of_entry_ = $form_type == 'first_run' ? date('y-m-d H:i:s') : array_values($this->input->post('date_received'));
$count = count($commodity_id);
$commodity_id_array = $data_array_facility_issues = $data_array_facility_transaction = array();
//collect n set the data in the array
for ($i = 0; $i < $count; $i++) {
$status = $total_unit_count[$i] > 0 ? true : false;
$status = $status && strtotime(str_replace(",", " ", $expiry_date[$i])) > strtotime('now') ? 1 : 2;
$date_of_entry = $form_type == 'first_run' ? date('y-m-d H:i:s') : date('Y-m-d', strtotime($date_of_entry_[$i]));
$mydata = array('facility_code' => $facility_code, 'commodity_id' => $commodity_id[$i], 'batch_no' => $batch_no[$i], 'manufacture' => $manu[$i], 'expiry_date' => date('y-m-d', strtotime(str_replace(",", " ", $expiry_date[$i]))), 'initial_quantity' => $total_unit_count[$i], 'current_balance' => $total_unit_count[$i], 'source_of_commodity' => $source_of_item[$i], 'date_added' => $date_of_entry, 'status' => $status);
//get the closing stock of the given item
$facility_stock_ = facility_stocks::get_facility_commodity_total($facility_code, $commodity_id[$i], $date_of_entry)->toArray();
//update the facility stock table
facility_stocks::update_facility_stock($mydata);
//check
$facility_has_commodity = facility_transaction_table::get_if_commodity_is_in_table($facility_code, $commodity_id[$i]);
$total_unit_count_ = $total_unit_count[$i] * 1;
if ($facility_has_commodity > 0 && $status == 1) {
//update the opening balance for the transaction table
$inserttransaction = Doctrine_Manager::getInstance()->getCurrentConnection();
$inserttransaction->execute("UPDATE `facility_transaction_table` SET `opening_balance` =`opening_balance`+{$total_unit_count[$i]},\n\t\t\t`closing_stock` =`closing_stock`+{$total_unit_count[$i]}\n WHERE `commodity_id`= '{$commodity_id[$i]}' and status='1' and facility_code={$facility_code}");
$mydata_ = array('facility_code' => $facility_code, 's11_No' => '(+ve Adj) Stock Addition', 'commodity_id' => $commodity_id[$i], 'batch_no' => !isset($batch_no[$i]) ? "N/A" : $batch_no[$i], 'expiry_date' => date('y-m-d', strtotime(str_replace(",", " ", $expiry_date[$i]))), 'balance_as_of' => isset($facility_stock_[0]['commodity_balance']) ? $facility_stock_[0]['commodity_balance'] : 0, 'qty_issued' => $total_unit_count_, 'date_issued' => date('y-m-d'), 'issued_to' => 'N/A', 'issued_by' => $this->session->userdata('user_id'));
//create the array to push to the db
array_push($data_array_facility_issues, $mydata_);
} else {
//get the data to send to the facility_transaction_table
if ($status == 1) {
$mydata2 = array('facility_code' => $facility_code, 'commodity_id' => $commodity_id[$i], 'opening_balance' => $total_unit_count[$i], 'total_issues' => 0, 'total_receipts' => 0, 'adjustmentpve' => 0, 'adjustmentnve' => 0, 'date_added' => $date_of_entry, 'closing_stock' => $total_unit_count[$i], 'status' => 1);
//send the data to the facility_transaction_table
$this->db->insert('facility_transaction_table', $mydata2);
$mydata_ = array('facility_code' => $facility_code, 's11_No' => $form_type == 'first_run' ? 'initial stock update' : '(+ve Adj) Stock Addition', 'commodity_id' => $commodity_id[$i], 'batch_no' => $form_type == 'first_run' ? "N/A" : $batch_no[$i], 'expiry_date' => $form_type == 'first_run' ? 'N/A' : date('y-m-d', strtotime(str_replace(",", " ", $expiry_date[$i]))), 'balance_as_of' => isset($facility_stock_[0]['commodity_balance']) ? $facility_stock_[0]['commodity_balance'] : 0, 'qty_issued' => $total_unit_count_, 'date_issued' => date('y-m-d'), 'issued_to' => 'N/A', 'issued_by' => $this->session->userdata('user_id'));
//create the array to push to the db
array_push($data_array_facility_issues, $mydata_);
}
}
}
//updates the log table accordingly based on the action carried out by the user involved
$user = $this->session->userdata('user_id');
$user_action = "add_stock";
$update = Doctrine_Manager::getInstance()->getCurrentConnection();
$update->execute("update log set {$user_action} = 1\n\t\t\twhere `user_id`= {$user}\n\t\t\tAND action = 'Logged In'\n\t\t\tand UNIX_TIMESTAMP( `end_time_of_event`) = 0");
//send a text message to the facility admin and sub county pharmacist
$this->hcmp_functions->send_system_text($user_action);
$this->db->insert_batch('facility_issues', $data_array_facility_issues);
//delete the record from the db
facility_stocks_temp::delete_facility_temp(null, null, $facility_code);
//set the notifications
//$this->hcmp_functions->send_stock_update_sms();
$updateCase = Doctrine_Manager::getInstance()->getCurrentConnection();
$updateCase->execute("UPDATE facility_stocks SET\n manufacture=CONCAT(UCASE(SUBSTRING(`manufacture`, 1, 1)),LOWER(SUBSTRING(`manufacture`, 2)))");
//
$this->session->set_flashdata('system_success_message', "Stock Levels Have Been Updated");
redirect('reports/facility_stock_data');
}
}
public static function get_tracer_items_report_new($facility_code, $district_id, $county_id, $category_id, $commodity_id, $option, $from, $to, $graph_type = null, $tracer = null, $option = NULL)
{
$selection_for_a_month = (!isset($facility_code) || $facility_code == "ALL") && $district_id > 0 || $category_id > 0 ? " f.facility_name as name," : ($commodity_id == "ALL" && isset($facility_code) ? " d.commodity_name as name," : (isset($county_id) && $district_id == "ALL" ? " di.district as name," : $graph_type == 'table_data' && $commodity_id > 0 ? " di.district , f.facility_name, f.facility_code, " : 1));
// echo $option;exit;
if ($selection_for_a_month == 1) {
$seconds_diff = $to - $from;
$date_diff = floor($seconds_diff / 3600 / 24);
$selection_for_a_month = $date_diff <= 30 ? "DATE_FORMAT(fs.date_issued,'%d %b %y') as date," : "DATE_FORMAT(fs.date_issued,'%b %y') as date ,";
}
$to = date('Y-m-d', $to);
$from = date('Y-m-d', $from);
switch ($option) {
case 'ksh':
$computation = "ifnull((SUM(ROUND(fs.qty_issued/ d.total_commodity_units)))*d.unit_cost ,0) AS total,d.id as commodity_id,d.commodity_code,d.unit_size,d.total_commodity_units,d.commodity_name as commodity";
break;
case 'units':
$computation = "ifnull(CEIL(SUM(fs.qty_issued)),0) AS total,d.id as commodity_id,d.commodity_code,d.unit_size,d.total_commodity_units,d.commodity_name as commodity";
break;
case 'packs':
$computation = "ifnull(SUM(ROUND(fs.qty_issued/d.total_commodity_units)),0) AS total,d.id as commodity_id,d.commodity_code,d.unit_size,d.total_commodity_units,d.commodity_name as commodity";
break;
case 'mos':
$r = facility_stocks_temp::get_months_of_stock($district_id, $county_id, $facility_code);
return $r;
exit;
break;
default:
$computation = "ifnull((SUM(ROUND(fs.qty_issued/ d.total_commodity_units)))*d.unit_cost ,0) AS total,d.id as commodity_id,d.commodity_code,d.unit_size,d.total_commodity_units,d.commodity_name as commodity";
break;
}
$and_data .= isset($category_id) && $category_id > 0 ? "AND d.commodity_sub_category_id = '{$category_id}'" : null;
$and_data = isset($from) && isset($to) ? "AND fs.date_issued between '{$from}' and '{$to}'" : null;
$and_data .= isset($commodity_id) && $commodity_id > 0 ? "AND d.id = '{$commodity_id}'" : null;
$and_data .= isset($district_id) && $district_id > 0 ? "AND di.id = '{$district_id}'" : null;
$and_data .= isset($facility_code) && $facility_code > 0 ? " AND f.facility_code = '{$facility_code}'" : null;
$and_data .= $county_id > 0 ? " AND di.county='{$county_id}'" : null;
$and = isset($tracer) && $tracer > 0 ? " AND d.tracer_item = 1" : null;
$group_by_a_month = isset($facility_code) && isset($district_id) || isset($category_id) ? " GROUP BY fs.commodity_id having total>0" : ($district_id > 0 && !isset($facility_code) ? " GROUP BY f.facility_code having total>0" : " GROUP BY d.id having total>0");
$group_by_a_month = ($facility_code == "ALL" || !isset($facility_code)) && $district_id > 0 ? " GROUP BY f.facility_code having total>0" : $commodity_id == "ALL" && isset($facility_code) ? " GROUP BY fs.commodity_id having total>0" : isset($county_id) && $district_id == "ALL" ? " GROUP BY d.id having total>0" : ($graph_type == 'table_data' && $commodity_id > 0 ? " GROUP BY d.id, f.facility_code having total>0 order by di.district asc, f.facility_name asc" : 1);
if ($group_by_a_month == 1) {
$group_by_a_month = $date_diff <= 30 ? "GROUP BY DATE_FORMAT(fs.date_issued,'%d %b %y')" : " GROUP BY DATE_FORMAT(fs.date_issued,'%b %y')";
} elseif ($tracer = 1) {
$group_by_a_month = $date_diff <= 30 ? "GROUP BY commodity" : $group_by_a_month;
}
$group_by_a_month = isset($tracer) && $group_by_a_month ? " GROUP BY commodity" : $group_by_a_month;
$inserttransaction = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAll("SELECT {$selection_for_a_month} {$computation}\n\t\t\tFROM facility_issues fs, facilities f, commodities d, districts di\n\t\t\tWHERE fs.facility_code = f.facility_code\n\t\t\tAND f.district = di.id\n\t\t\tAND fs.qty_issued >0\n\t\t\t{$and} \n\t\t\tAND d.id = fs.commodity_id\n\t\t\t{$and_data}\n\t\t\t{$group_by_a_month}\n\t\t\t");
// echo "SELECT $computation";exit;
return $inserttransaction;
}
