public function home() { $rights = User_Right::getRights($this->session->userdata('access_level')); $menu_data = array(); $menus = array(); $counter = 0; foreach ($rights as $right) { $menu_data['menus'][$right->Menu] = $right->Access_Type; $menus['menu_items'][$counter]['url'] = $right->Menu_Item->Menu_Url; $menus['menu_items'][$counter]['text'] = $right->Menu_Item->Menu_Text; $menus['menu_items'][$counter]['offline'] = $right->Menu_Item->Offline; $counter++; } $this->session->set_userdata($menu_data); $this->session->set_userdata($menus); //Check if the user is a pharmacist. If so, update his/her local envirinment with current values if ($this->session->userdata('user_indicator') == "pharmacist") { $facility_code = $this->session->userdata('facility'); //Retrieve the Totals of the records in the master database that have clones in the clients! $today = date('m/d/Y'); $timestamp = strtotime($today); $data['scheduled_patients'] = Patient_Appointment::getAllScheduled($timestamp); } //Get CCC Stores if they exist $ccc_stores = CCC_store_service_point::getAllActive(); $this->session->set_userdata('ccc_store', $ccc_stores); $data['title'] = "webADT | System Home"; $data['content_view'] = "home_v"; $data['banner_text'] = "Home"; $data['link'] = "home"; $data['user'] = $this->session->userdata['full_name']; $this->load->view("template", $data); }
public function update($table = "") { $id = $this->input->post("source_id"); $name = $this->input->post("source_name"); if ($table == "transaction_type") { $desc = $this->input->post("desc"); $effect = $this->input->post("effect"); $data_array = array("name" => $name, "effect" => $effect, "`desc`" => $desc); } else { $data_array = array("name" => $name); } $this->db->where('id', $id); $this->db->update($table, $data_array); $ccc_stores = CCC_store_service_point::getAllActive(); $this->session->set_userdata('ccc_store', $ccc_stores); $this->session->set_userdata('msg_success', $this->input->post('source_name') . ' was Updated!'); $this->session->set_flashdata('filter_datatable', $this->input->post('source_name')); $this->session->set_userdata("link_id", "listing/" . $table); $this->session->set_userdata("linkSub", "settings/listing/" . $table); //Filter datatable redirect('settings_management'); }
public function create_order($type = "cdrr", $order_type, $content_array = array()) { $data['hide_generate'] = 0; $data['hide_save'] = 0; $data['hide_btn'] = 0; $data['stand_alone'] = 0; if ($type == "cdrr") { $this->session->set_userdata("order_go_back", "cdrr"); $data['hide_side_menu'] = 0; $data['options'] = "none"; if ($order_type == 0) { //satellite $data['page_title'] = "Satellite Facility(F-CDRR)"; $data['banner_text'] = "Satellite Facility(F-CDRR)"; $facility = $this->input->post("satellite_facility", TRUE); if ($facility == null) { $facility = $this->session->userdata("facility"); } else { $data['hide_generate'] = 1; } } else { if ($order_type == 1) { //standalone $data['page_title'] = "Stand-alone(F-CDRR)"; $data['banner_text'] = "Stand-alone(F-CDRR)"; $facility = $this->session->userdata("facility"); $data['stand_alone'] = 1; } else { if ($order_type == 2) { //dispensing_point $data['page_title'] = "Central Dispensing Point(F-CDRR)"; $data['banner_text'] = "Central Dispensing Point(F-CDRR)"; $facility = $this->session->userdata("facility"); $order_type = 0; } else { //aggregate $data['page_title'] = "Central Aggregate(D-CDRR)"; $data['banner_text'] = "Central Aggregate(D-CDRR)"; $data['hide_generate'] = 2; $facility = $this->session->userdata("facility"); } } } if (!empty($content_array)) { $cdrr_array = $content_array; $data['cdrr_array'] = $cdrr_array['cdrr_array']; $data['status_name'] = strtolower($cdrr_array['cdrr_array'][0]['status_name']); $facility_id = $cdrr_array['cdrr_array'][0]['facility_id']; $data['facility_id'] = $facility_id; $facilities = Sync_Facility::getCode($facility_id, $order_type); $facility = $facilities['code']; $code = $cdrr_array['cdrr_array'][0]['code']; $code = $this->getDummyCode($code, $order_type); $data['options'] = $cdrr_array['options']; if ($data['options'] == "view") { $data['hide_save'] = 1; } $data['hide_btn'] = 1; $cdrr_id = $cdrr_array['cdrr_array'][0]['cdrr_id']; $data['cdrr_id'] = $cdrr_id; $data['logs'] = Cdrr_Log::getLogs($cdrr_id); if ($data['options'] == "view" || $data['options'] == "update") { if ($data['status_name'] == "prepared" || $data['status_name'] == "review") { $data['option_links'] = "<li class='active'><a href='" . site_url("order/view_order/cdrr/" . $cdrr_id) . "'>view</a></li><li><a href='" . site_url("order/update_order/cdrr/" . $cdrr_id) . "'>update</a></li><li><a class='delete' href='" . site_url("order/delete_order/cdrr/" . $cdrr_id) . "'>delete</a></li>"; } else { $data['option_links'] = "<li class='active'><a href='" . site_url("order/view_order/cdrr/" . $cdrr_id) . "'>view</a></li>"; } } if ($code == 0) { $and = ""; } else { $and = "AND ci.resupply !='0'"; } if ($cdrr_array['options'] == "update") { $data['commodities'] = Sync_Drug::getActiveList(); } else { $sql = "SELECT sd.id,CONCAT_WS('] ',CONCAT_WS(' [',name,abbreviation),CONCAT_WS(' ',strength,formulation)) as Drug,unit as Unit_Name,packsize as Pack_Size,category_id as Category\n\t\t\t FROM cdrr_item ci\n\t\t\t LEFT JOIN sync_drug sd ON sd.id=ci.drug_id\n\t\t\t WHERE ci.cdrr_id='{$cdrr_id}'\n\t\t\t AND(sd.category_id='1' OR sd.category_id='2' OR sd.category_id='3')"; $query = $this->db->query($sql); $data['commodities'] = $query->result(); } } else { $period_start = date('Y-m-01', strtotime(date('Y-m-d') . "-1 month")); $period_end = date('Y-m-t', strtotime(date('Y-m-d') . "-1 month")); $code = $this->getActualCode($order_type, $type); $facilities = Sync_Facility::getId($facility, $order_type); $duplicate = $this->check_duplicate($code, $period_start, $period_end, $facilities['id'], $type); $data['commodities'] = Sync_Drug::getActiveList(); $data['duplicate'] = $duplicate; } $facilities = Sync_Facility::getId($facility, $order_type); $data['facility_id'] = $facilities['id']; $data['facility_object'] = Facilities::getCodeFacility($facility); $data['content_view'] = "orders/cdrr_template"; $data['report_type'] = $order_type; $data['stores'] = CCC_store_service_point::getStoreGroups(); $this->base_params($data); } else { if ($type == "maps") { $this->session->set_userdata("order_go_back", "fmaps"); $data['o_type'] = "FMAP"; $data['options'] = "none"; $data["is_update"] = 0; $data["is_view"] = 0; if ($order_type == 0) { //satellite $facility_code = $this->input->post("satellite_facility", TRUE); $data['page_title'] = "Satellite Facility(F-MAPS)"; $data['banner_text'] = "Satellite Facility(F-MAPS)"; if ($facility_code == null) { $facility_code = $this->session->userdata("facility"); } else { $data['hide_generate'] = 1; } } else { if ($order_type == 1) { //standalone $facility_code = $this->session->userdata('facility'); $facility_id = $this->session->userdata('facility_id'); $data['commodities'] = Sync_Drug::getActiveList(); $data['page_title'] = "Stand-Alone MAPS"; $data['banner_text'] = "Maps Form"; } else { if ($order_type == 2) { //dispensing_point $facility_code = $this->session->userdata('facility'); $facility_id = $this->session->userdata('facility_id'); $data['commodities'] = Sync_Drug::getActiveList(); $data['page_title'] = "Central Dispensing Point"; $data['banner_text'] = "Maps Form"; $order_type = 0; } else { //aggregate $facility_code = $this->session->userdata('facility'); $data['page_title'] = "Aggregate Maps List"; $facility = Facilities::getParent($facility_code); $parent_code = $facility['parent']; if ($parent_code == $facility_code) { //Check if button was clicked to start new aggregate order $data['hide_generate'] = 2; } $data['banner_text'] = "Aggregate Maps List"; } } } if (!empty($content_array)) { $fmaps_array = $content_array; $data['fmaps_array'] = $fmaps_array['fmaps_array']; $facility_id = $fmaps_array['fmaps_array'][0]['facility_id']; $data['facility_id'] = $facility_id; $facilities = Sync_Facility::getCode($facility_id, $order_type); $facility_code = $facilities['code']; $code = $fmaps_array['fmaps_array'][0]['code']; $code = $this->getDummyCode($code, $order_type); //Central or Satellite or Aggregate $data['status'] = strtolower($fmaps_array['fmaps_array'][0]['status_name']); $data['created'] = $fmaps_array['fmaps_array'][0]['created']; // Pending, Approved, ... $data['options'] = $fmaps_array['options']; $data['hide_btn'] = 1; $maps_id = $fmaps_array['fmaps_array'][0]['maps_id']; //Complet id with # $map_id = $fmaps_array['fmaps_array'][0]['map_id']; //Id from DB $data['maps_id'] = $maps_id; $data['map_id'] = $map_id; $data['logs'] = Maps_Log::getMapLogs($map_id); if ($data['options'] == "view") { $data['hide_save'] = 1; $regimen_table = 'sync_regimen'; $regimen_cat_table = 'sync_regimen_category'; $regimen_code = 'r.code'; $regimen_desc = 'r.name as description'; $regimen_cat_join = 'r.category_id'; $regimen_join = 'mi.regimen_id=r.id'; $sql_regimen = "SELECT rc.id,r.id as reg_id,rc.Name as name,{$regimen_code},{$regimen_desc},{$regimen_cat_join},mi.total\n\t\t\t\t\t\t\t\t\tFROM {$regimen_table} r\n\t\t\t\t\t\t\t\t\tLEFT JOIN {$regimen_cat_table} rc ON rc.id = {$regimen_cat_join}\n\t\t\t\t\t\t\t\t\tLEFT JOIN maps_item mi ON {$regimen_join}\n\t\t\t\t\t\t\t\t\tWHERE maps_id='{$map_id}'"; $query_regimen = $this->db->query($sql_regimen); $regimen_array = $query_regimen->result_array(); $regimen_categories = array(); foreach ($regimen_array as $value) { $regimen_categories[] = $value['name']; } $regimen_categories = array_unique($regimen_categories); $data['regimen_categories'] = $regimen_categories; $data['regimen_array'] = $regimen_array; } if ($data['options'] == "update") { $data["is_update"] = 1; $data['regimen_categories'] = Sync_Regimen_Category::getAll(); } else { $data["is_view"] = 1; $data['regimens'] = Maps_Item::getOrderItems($maps_id); } } else { $data['regimen_categories'] = Sync_Regimen_Category::getAll(); $period_start = date('Y-m-01', strtotime(date('Y-m-d') . "-1 month")); $period_end = date('Y-m-t', strtotime(date('Y-m-d') . "-1 month")); $code = $this->getActualCode($order_type, $type); $facilities = Sync_Facility::getId($facility_code, $order_type); $duplicate = $this->check_duplicate($code, $period_start, $period_end, $facilities['id'], $type); $data['duplicate'] = $duplicate; } $facilities = Sync_Facility::getId($facility_code, $order_type); $data['facility_id'] = $facilities['id']; $data['content_view'] = "orders/fmap_template"; $data['report_type'] = $order_type; $data['facility_object'] = Facilities::getCodeFacility($facility_code); $this->base_params($data); } } }
public function stock_transaction($stock_type = 1) { $data['hide_side_menu'] = 1; $facility_code = $this->session->userdata('facility'); $user_id = $this->session->userdata('user_id'); $access_level = $this->session->userdata('user_indicator'); if ($access_level == "facility_administrator") { $transaction_type = Transaction_Type::getAll(); } else { $transaction_type = Transaction_Type::getAllNonAdjustments(); } $drug_source = Drug_Source::getAll(); $facility_detail = facilities::getSupplier($facility_code); $drug_destination = Drug_Destination::getAll(); //Check facility type(satelitte, standalone or central) $facility_type = Facilities::getType($facility_code); $get_list = array(); $data['list_facility'] = ""; if ($facility_type == 0) { //Satellite $central_code = facilities::getCentralCode($facility_code); $get_list = facilities::getCentralName($central_code); $data['list_facility'] = "Central Site"; } else { if ($facility_type == 1) { //Standalone $get_list = array(); $data['list_facility'] = ""; } else { if ($facility_type > 1) { //Central $get_list = facilities::getSatellites($facility_code); $data['list_facility'] = "Satelitte Sites"; } } } $name = CCC_store_service_point::getCCC($stock_type); $name = $name['Name']; $data['supplier_name'] = $facility_detail->supplier->name; $data['picking_lists'] = ""; $data['get_list'] = $get_list; $data['user_id'] = $user_id; $data['facility'] = $facility_code; $data['stock_type'] = $stock_type; $data['transaction_types'] = $transaction_type; $data['drug_sources'] = $drug_source; $data['drug_destinations'] = $drug_destination; $data['store'] = strtoupper($name); $data['content_view'] = "stock_transaction_v"; $this->base_params($data); }
public function getDrugsReceived_old($stock_type, $start_date = "", $end_date = "") { $data['from'] = $start_date; $data['to'] = $end_date; $start_date = date('Y-m-d', strtotime($start_date)); $end_date = date('Y-m-d', strtotime($end_date)); $facility_code = $this->session->userdata('facility'); $facilty_value = ""; $param = ""; $facilty_value = "dsm.ccc_store_sp={$stock_type}"; $ccc = CCC_store_service_point::getCCC($stock_type); $data['transaction_type'] = $ccc['Name']; $sql = "select d.id,d.drug,du.Name as unit,d.pack_size,SUM(dsm.quantity) as total from drug_stock_movement dsm \n\t\t\t\tLEFT JOIN transaction_type t ON t.id=dsm.transaction_type \n\t\t\t\tLEFT JOIN drugcode d ON d.id=dsm.drug \n\t\t\t\tLEFT JOIN drug_unit du ON du.id=d.unit \n\t\t\t\twhere dsm.transaction_date between '{$start_date}' and '{$end_date}' and {$facilty_value} and dsm.facility='{$facility_code}' \n\t\t\t\tAND t.name LIKE '%Received%' AND d.id IS NOT NULL GROUP BY d.drug"; //echo $sql;die(); $query = $this->db->query($sql); $source_array = Drug_Source::getAllHydrate(); $all_other_ccc_stores = CCC_store_service_point::getAllBut($stock_type); $dyn_table = "<table border='1' class='dataTables' cellpadding='5'>"; $dyn_table .= "<thead>\n\t\t\t\t\t\t<tr><th>Drug Name</th>\n\t\t\t\t\t"; $source_array = array_merge($all_other_ccc_stores, $source_array); //echo json_encode($dest_array);die(); foreach ($source_array as $value) { $dyn_table .= "<th>" . $value['Name'] . "</th>"; } $dyn_table .= "</tr>\n\t\t\t\t\t\t</thead>\n\t\t\t\t\t\t<tbody>"; $results = $query->result_array(); if ($results) { foreach ($results as $result) { $dyn_table .= "<tr><td>" . $result['drug'] . "</td>"; //Get all destinations for that drug $get_drugs = "\n\t\t\t \t\t\tSELECT table1.name,table1.total FROM\n\t\t\t \t\t\t(\n\t\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\tSELECT csp.name as name,temp.total \n\t\t\t\t\t FROM ccc_store_service_point csp\n\t\t\t\t\t LEFT JOIN \n\t\t\t\t\t (SELECT source_destination,SUM(dsm.quantity) as total \n\t\t\t\t\t \t FROM drug_stock_movement dsm \n\t\t\t\t\t \t LEFT JOIN transaction_type t ON t.id=dsm.transaction_type \n\t\t\t\t\t \t LEFT JOIN drugcode d ON d.id=dsm.drug \n\t\t\t\t\t \t LEFT JOIN drug_unit du ON du.id=d.unit \n\t\t\t\t\t \t WHERE dsm.transaction_date \n\t\t\t\t\t \t BETWEEN '{$start_date}' \n\t\t\t\t\t \t AND '{$end_date}' \n\t\t\t\t\t \t AND {$facilty_value} \n\t\t\t\t\t \t AND t.name LIKE '%received%' \n\t\t\t\t\t \t AND dsm.drug='" . $result['id'] . "' \n\t\t\t\t\t \t GROUP BY source_destination) as temp ON temp.source_destination = csp.name\n\t\t\t\t\t \t \n\t\t\t\t\t WHERE csp.id !={$stock_type} AND csp.active = 1 \n\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t\tUNION ALL\n\t\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\tSELECT des.name as name,temp.total \n\t\t\t\t\t FROM drug_source des \n\t\t\t\t\t LEFT JOIN (SELECT source_destination,SUM(dsm.quantity) as total \n\t\t\t\t\t \t FROM drug_stock_movement dsm \n\t\t\t\t\t \t LEFT JOIN transaction_type t ON t.id=dsm.transaction_type \n\t\t\t\t\t \t LEFT JOIN drugcode d ON d.id=dsm.drug \n\t\t\t\t\t \t LEFT JOIN drug_unit du ON du.id=d.unit \n\t\t\t\t\t \t WHERE dsm.transaction_date \n\t\t\t\t\t \t BETWEEN '{$start_date}' \n\t\t\t\t\t \t AND '{$end_date}' \n\t\t\t\t\t \t AND {$facilty_value} \n\t\t\t\t\t \t AND t.name LIKE '%received%' \n\t\t\t\t\t \t AND dsm.drug='" . $result['id'] . "' \n\t\t\t\t\t \t GROUP BY source_destination) as temp ON temp.source_destination=des.id \n\t\t\t\t\t \t WHERE des.active=1\n\t ORDER BY des.id ASC\n\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t) as table1\n\t\t\t \t\t\t"; //echo $get_drugs;die(); $get_dest = $this->db->query($get_drugs); $get_des_array = $get_dest->result_array(); if ($get_des_array) { foreach ($get_des_array as $value) { $total = $value['total']; if ($value['total'] == null) { $total = 0; } $dyn_table .= "<td>" . $total . "</td>"; } } $dyn_table .= "</tr>"; } } else { //$dyn_table .= "<tr><td colspan='4'>No Data Available</td></tr>"; } $dyn_table .= "</tbody></table>"; $data['dyn_table'] = $dyn_table; $data['title'] = "webADT | Reports"; $data['hide_side_menu'] = 1; $data['banner_text'] = "Facility Reports"; $data['selected_report_type_link'] = "drug_inventory_report_row"; $data['selected_report_type'] = "Stock Consumption"; $data['report_title'] = "Stock Consumption"; $data['facility_name'] = $this->session->userdata('facility_name'); $data['content_view'] = 'reports/drugreceived_v'; $this->load->view('template', $data); }
public function getRunningBalance() { ini_set("max_execution_time", "100000"); ini_set("memory_limit", '2048M'); $drug_id = $this->input->post("drug_id"); $stores = CCC_store_service_point::getActive(); $facility_code = $this->session->userdata("facility"); foreach ($stores as $store) { $store_id = $store['id']; $sql = "SELECT d.id AS trans_id, \n c.name AS trans_store, \n t.name AS trans_type, \n d.quantity AS trans_qty, \n d.quantity_out AS trans_qty_out,\n IF(t.name LIKE '%physical%','1','0')as trans_desc\n\t\t\t\t\tFROM drug_stock_movement d\n\t\t\t\t\tLEFT JOIN ccc_store_service_point c ON d.ccc_store_sp = c.id\n\t\t\t\t\tLEFT JOIN transaction_type t ON d.transaction_type = t.id\n\t\t\t\t\tWHERE d.drug ='{$drug_id}'\n\t\t\t\t\tAND d.ccc_store_sp ='{$store_id}'\n\t\t\t\t\tORDER BY d.id ASC"; $query = $this->db->query($sql); $transactions = $query->result_array(); $balance_before = 0; $balance_after = 0; $total = 0; $prev_count = 0; $balance = array(); /* $table="<table border='1'>"; $table.="<thead><tr><th>Beginning Balance</th><th>Type of Transaction</th><th>QTY Transacted</th><th>End Balance</th><th>Cumulative Balance</th></tr></thead><tbody>"; $table.="<tr><td>".$balance_before."</td><td>". $transaction['trans_type']."</td><td>".$total."</td><td>".$balance[$count]."</td><td>".$balance_after."</td></tr>"; $table.="</tbody></table>"; echo "<br/>Drug Balance:".$drug_balance."<br/>Difference:".$difference; */ foreach ($transactions as $count => $transaction) { /* 1.Beginning Balance 2.Type of Transaction 3.Quantity Transacted (quantity-quantity_out) 4.End Balance (Beginning_Balance+Quantity transacted) 5.Cumulative Balance (+=End Balance) */ $prev_count = $count - 1; $total = $transaction['trans_qty'] - $transaction['trans_qty_out']; if ($prev_count >= 0) { $balance_before = $balance[$prev_count]; } $balance[$count] = $balance_before + $total; $balance_after = $balance[$count]; if ($balance_after < 0) { $balance[$count] = 0; $balance_after = 0; } if ($prev_count >= 0) { $balance_after = $balance[$prev_count] + $total; if ($balance_after < 0) { $balance[$count] = 0; $balance_after = 0; } } //transaction is a starting stock if ($transaction['trans_desc'] == 1) { $balance[$count] = $total; $balance_after = $total; } //update running_balance in machine code column $record_id = $transaction['trans_id']; $sql = "UPDATE drug_stock_movement SET machine_code='{$balance_after}' WHERE id='{$record_id}'"; $this->db->query($sql); } //get drug balance in drug stock balance $sql = "SELECT SUM(dsb.balance) as total,dsb.batch_number as batch_no,dsb.expiry_date\n\t\t\t\t FROM drug_stock_balance dsb\n\t\t\t\t WHERE dsb.drug_id ='{$drug_id}'\n\t\t\t\t AND dsb.stock_type ='{$store_id}'\n\t\t\t\t AND facility_code='{$facility_code}'\n\t\t\t\t AND dsb.expiry_date > CURDATE()\n\t\t\t\t AND dsb.balance >0"; $query = $this->db->query($sql); $balances = $query->result_array(); $drug_balance = 0; if ($balances) { $drug_balance = $balances[0]['total']; $batch_no = $balances[0]['batch_no']; $expiry_date = $balances[0]['expiry_date']; } //compare last balance after with balance in drug stock balance $difference = $drug_balance - $balance_after; if ($difference != 0) { if ($difference > 0) { //positive adjustment $column = "quantity"; $sql = "SELECT id FROM transaction_type WHERE name LIKE '%adjustment (+)%'"; } else { //negative adjustment $column = "quantity_out"; $sql = "SELECT id FROM transaction_type WHERE name LIKE '%adjustment (-)%'"; } //get transaction_type_id $query = $this->db->query($sql); $results = $query->result_array(); $transaction_type_id = $results[0]['id']; //make difference absolute(positive) $difference = abs($difference); //source/destination (pharmacy) $source = $facility_code; $destination = $facility_code; //if store_id is 1 then source is (store) if ($store_id == 1) { $source = $store_id; } //insert record into drug_stock_movement $insert_data = array('drug' => $drug_id, 'transaction_date' => date("Y-m-d"), 'batch_number' => $batch_no, 'expiry_date' => $expiry_date, 'transaction_type' => $transaction_type_id, 'source' => $source, 'destination' => $destination, $column => $difference, 'facility' => $facility_code, 'ccc_store_sp' => $store_id); $this->db->insert('drug_stock_movement', $insert_data); $last_insert_id = $this->db->insert_id(); //update running_balance in machine code column $sql = "UPDATE drug_stock_movement SET machine_code='{$drug_balance}' WHERE id='{$last_insert_id}'"; $this->db->query($sql); } } return true; }
public function save_edit() { $timestamp = ""; $patient = ""; $facility = ""; $user = ""; $record_no = ""; $soh = $this->input->post("soh"); //Get transaction type $transaction_type = transaction_type::getTransactionType("dispense", "0"); $transaction_type = $transaction_type->id; $transaction_type1 = transaction_type::getTransactionType("returns", "1"); $transaction_type1 = $transaction_type1->id; $original_qty = @$_POST["qty_hidden"]; $facility = $this->session->userdata("facility"); $user = $this->session->userdata("full_name"); $timestamp = date('Y-m-d H:i:s'); $patient = @$_POST['patient']; $expiry_date = @$_POST['expiry']; $ccc_id = @$_POST["ccc_id"]; //Define source and destination $source = $facility; $destination = $facility; //Get ccc_store_name $ccc_store = CCC_store_service_point::getCCC($ccc_id); $ccc_name = $ccc_store->Name; if (stripos($ccc_name, 'store')) { $source = $facility; $destination = ''; } //Get running balance in drug stock movement $sql_run_balance = $this->db->query("SELECT machine_code as balance FROM drug_stock_movement WHERE drug ='" . @$_POST['original_drug'] . "' AND ccc_store_sp ='{$ccc_id}' AND expiry_date >=CURDATE() ORDER BY id DESC LIMIT 1"); $run_balance_array = $sql_run_balance->result_array(); if (count($run_balance_array) > 0) { $prev_run_balance = $run_balance_array[0]["balance"]; } else { //If drug does not exist, initialise the balance to zero $prev_run_balance = 0; } //Get running balance in drug stock movement end --------- //If record is to be deleted if (@$_POST['delete_trigger'] == 1) { $sql = "update patient_visit set active='0' WHERE id='" . @$_POST["dispensing_id"] . "';"; $this->db->query($sql); $bal = $soh + @$_POST["qty_disp"]; $act_run_balance = $prev_run_balance + @$_POST["qty_disp"]; //Actual running balance //If deleting previous transaction, check if batch has not expired, if not, insert in drug stock balance table $today = strtotime(date("Y-m-d")); $original_expiry = strtotime(@$_POST["original_expiry_date"]); if ($today <= $original_expiry) { //If balance for this batch is greater than zero, update stock, otherwise, insert in drug stock balance $sql_batch_balance = "SELECT balance FROM drug_stock_balance WHERE drug_id='" . @$_POST["original_drug"] . "' AND batch_number='" . @$_POST["batch"] . "' AND expiry_date='" . @$_POST["original_expiry_date"] . "' AND stock_type='{$ccc_id}' AND facility_code='{$facility}'"; $query = $this->db->query($sql_batch_balance); $res = $query->result_array(); $prev_batch_balance = ""; if ($res) { $prev_batch_balance = $res[0]['balance']; } if ($prev_batch_balance > 0) { //Update drug_stock_balance $sql = "UPDATE drug_stock_balance SET balance=balance+" . @$_POST["qty_disp"] . " WHERE drug_id='" . @$_POST["original_drug"] . "' AND batch_number='" . @$_POST["batch"] . "' AND expiry_date='" . @$_POST["original_expiry_date"] . "' AND stock_type='{$ccc_id}' AND facility_code='{$facility}'"; //echo $sql;die(); $this->db->query($sql); } else { $sql = "INSERT INTO drug_stock_balance (balance,dug_id,batch_number,expiry_date,stock_type,facility_code) VALUES('" . @$_POST["qty_disp"] . "','" . @$_POST["original_drug"] . "','" . @$_POST["batch"] . "','" . @$_POST["original_expiry_date"] . "','{$ccc_id}','{$facility}')"; //echo $sql;die(); $this->db->query($sql); } } //Insert in drug stock movement //Get balance after update $sql = "SELECT balance FROM drug_stock_balance WHERE drug_id='" . @$_POST["original_drug"] . "' AND batch_number='" . @$_POST["batch"] . "' AND expiry_date='" . @$_POST["original_expiry_date"] . "' AND stock_type='{$ccc_id}' AND facility_code='{$facility}'"; $query = $this->db->query($sql); $results = $query->result_array(); $actual_balance = $results[0]['balance']; $sql = "INSERT INTO drug_stock_movement (drug, transaction_date, batch_number, transaction_type,source,destination,source_destination,expiry_date, quantity, balance, facility, machine_code,timestamp,ccc_store_sp) SELECT '" . @$_POST["original_drug"] . "','" . @$_POST["original_dispensing_date"] . "', '" . @$_POST["batch"] . "','{$transaction_type1}','{$source}','{$destination}','Dispensed To Patients','{$expiry_date}','" . @$_POST["qty_disp"] . "','" . @$actual_balance . "','{$facility}','{$act_run_balance}','{$timestamp}','{$ccc_id}' from drug_stock_movement WHERE batch_number= '" . @$_POST["batch"] . "' AND drug='" . @$_POST["original_drug"] . "' LIMIT 1;"; $this->db->query($sql); //Update drug consumption $period = date('Y-m-01'); $sql = "UPDATE drug_cons_balance SET amount=amount-" . $original_qty . " WHERE drug_id='" . @$_POST["original_drug"] . "' AND stock_type='{$ccc_id}' AND period='{$period}' AND facility='{$facility}'"; $this->db->query($sql); $this->session->set_userdata('dispense_deleted', 'success'); } else { //If record is edited $period = date('Y-m-01'); $sql = "UPDATE patient_visit SET dispensing_date = '" . @$_POST["dispensing_date"] . "', visit_purpose = '" . @$_POST["purpose"] . "', current_weight='" . @$_POST["weight"] . "', current_height='" . @$_POST["height"] . "', regimen='" . @$_POST["current_regimen"] . "', drug_id='" . @$_POST["drug"] . "', batch_number='" . @$_POST["batch"] . "', dose='" . @$_POST["dose"] . "', duration='" . @$_POST["duration"] . "', quantity='" . @$_POST["qty_disp"] . "', brand='" . @$_POST["brand"] . "', indication='" . @$_POST["indication"] . "', pill_count='" . @$_POST["pill_count"] . "', missed_pills='" . @$_POST["missed_pills"] . "', comment='" . @$_POST["comment"] . "',non_adherence_reason='" . @$_POST["non_adherence_reasons"] . "',adherence='" . @$_POST["adherence"] . "' WHERE id='" . @$_POST["dispensing_id"] . "';"; $this->db->query($sql); if (@$_POST["batch"] != @$_POST["batch_hidden"] || @$_POST["qty_disp"] != @$_POST["qty_hidden"]) { //Update drug_stock_balance //Balance=balance+(previous_qty_disp-actual_qty_dispense) $bal = $soh; //New qty dispensed=old qty - actual qty dispensed $new_qty_dispensed = $_POST["qty_hidden"] - $_POST["qty_disp"]; $act_run_balance = $prev_run_balance - $_POST["qty_disp"]; //If new quantity dispensed is less than qty previously dispensed //echo $new_qty_dispensed;die(); if ($new_qty_dispensed > 0) { $bal = $soh + $new_qty_dispensed; $sql = "UPDATE drug_stock_balance SET balance=balance+" . @$new_qty_dispensed . " WHERE drug_id='" . @$_POST["original_drug"] . "' AND batch_number='" . @$_POST["batch"] . "' AND expiry_date='" . @$_POST["original_expiry_date"] . "' AND stock_type='{$ccc_id}' AND facility_code='{$facility}'"; $this->db->query($sql); //Update drug consumption $sql = "UPDATE drug_cons_balance SET amount=amount-" . $new_qty_dispensed . " WHERE drug_id='" . @$_POST["original_drug"] . "' AND stock_type='{$ccc_id}' AND period='{$period}' AND facility='{$facility}'"; $this->db->query($sql); } else { if ($new_qty_dispensed < 0) { $bal = $soh - $new_qty_dispensed; $new_qty_dispensed = abs($new_qty_dispensed); $sql = "UPDATE drug_stock_balance SET balance=balance-" . @$new_qty_dispensed . " WHERE drug_id='" . @$_POST["original_drug"] . "' AND batch_number='" . @$_POST["batch"] . "' AND expiry_date='" . @$_POST["original_expiry_date"] . "' AND stock_type='{$ccc_id}' AND facility_code='{$facility}'"; $this->db->query($sql); //Update drug consumption $sql = "UPDATE drug_cons_balance SET amount=amount+" . $new_qty_dispensed . " WHERE drug_id='" . @$_POST["original_drug"] . "' AND stock_type='{$ccc_id}' AND period='{$period}' AND facility='{$facility}'"; $this->db->query($sql); } } //Balance after returns $bal1 = $soh + $original_qty; $act_run_balance1 = $prev_run_balance + $original_qty; //Actual running balance $act_run_balance = $act_run_balance + $original_qty; //Returns transaction $sql = "INSERT INTO drug_stock_movement (drug, transaction_date, batch_number, transaction_type,source,destination,source_destination,expiry_date, quantity,balance, facility, machine_code,timestamp,ccc_store_sp) SELECT '" . @$_POST["original_drug"] . "','" . @$_POST["original_dispensing_date"] . "', '" . @$_POST["batch_hidden"] . "','{$transaction_type1}','{$source}','{$destination}','Dispensed To Patients',expiry_date,'" . @$_POST["qty_hidden"] . "','{$bal1}','{$facility}','{$act_run_balance1}','{$timestamp}','{$ccc_id}' from drug_stock_movement WHERE batch_number= '" . @$_POST["batch_hidden"] . "' AND drug='" . @$_POST["original_drug"] . "' LIMIT 1;"; $this->db->query($sql); //Dispense transaction $sql = "INSERT INTO drug_stock_movement (drug, transaction_date, batch_number, transaction_type,source,destination,expiry_date, quantity_out,balance, facility, machine_code,timestamp,ccc_store_sp) SELECT '" . @$_POST["drug"] . "','" . @$_POST["original_dispensing_date"] . "', '" . @$_POST["batch"] . "','{$transaction_type}','{$source}','{$destination}',expiry_date,'" . @$_POST["qty_disp"] . "','{$bal}','{$facility}','{$act_run_balance}','{$timestamp}','{$ccc_id}' from drug_stock_movement WHERE batch_number= '" . @$_POST["batch"] . "' AND drug='" . @$_POST["drug"] . "' LIMIT 1;"; $this->db->query($sql); } $this->session->set_userdata('dispense_updated', 'success'); } $sql = "select * from patient where patient_number_ccc='{$patient}' and facility_code='{$facility}'"; $query = $this->db->query($sql); $results = $query->result_array(); $record_no = $results[0]['id']; $this->session->set_userdata('msg_save_transaction', 'success'); redirect("patient_management/load_view/details/{$record_no}"); }