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 synch_balance($stock_type = "2") { $stock_type = $this->input->post("stock_type"); //$drug_id=$this->input->post("drug_id"); $drug_id = 102; $not_saved = 0; $facility_code = $this->session->userdata('facility'); $stock_param = ""; //CCC Store Name $ccc = CCC_store_service_point::getCCC($stock_type); $ccc_name = $ccc['Name']; //Store if (stripos($ccc_name, 'store')) { $stock_param = " AND (source='" . $facility_code . "' OR destination='" . $facility_code . "') AND source!=destination "; } else { if (stripos($ccc_name, 'pharmacy')) { $stock_param = " AND (source=destination) AND(source='" . $facility_code . "') "; } } $count_it = 0; $stock_status = 0; //Get all the batches $get_batches_sql = "SELECT d.batch_number AS batch,expiry_date FROM drug_stock_movement d WHERE d.drug = '" . $drug_id . "' AND facility='" . $facility_code . "' " . $stock_param . " AND batch_number!='' GROUP BY d.batch_number"; $bacthes = $this->db->query($get_batches_sql); $batch_results = $bacthes->result_array(); foreach ($batch_results as $key => $batch_row) { //echo $count_it."<br>"; //Query to check if batch has had a physical count $batch_no = $batch_row['batch']; $expiry_date = $batch_row['expiry_date']; if (trim($batch_no) == '') { continue; } //Get the latest physical count $initial_stock_sql = "SELECT d.quantity AS Initial_stock, d.transaction_date AS transaction_date, '" . $batch_no . "' AS batch,t.name as trans_name \n\t\t\t\t\t\t\t\t\tFROM drug_stock_movement d LEFT JOIN transaction_type t ON t.id=d.transaction_type \n\t\t\t\t\t\t\t\t\tWHERE d.drug = '" . $drug_id . "' AND (t.name LIKE '%physical%' OR t.name LIKE '%stock count%') \n\t\t\t\t\t\t\t\t\tAND facility='" . $facility_code . "' " . $stock_param . " AND d.batch_number = '" . $batch_no . "' ORDER BY d.id DESC LIMIT 1"; //$initial_stock_sql = "SELECT SUM( d.quantity ) AS Initial_stock, d.transaction_date AS transaction_date, '" .$batch_no. "' AS batch,t.name as trans_name FROM drug_stock_movement d LEFT JOIN transaction_type t ON t.id=d.transaction_type WHERE d.drug = '" .$drug_id. "' AND (t.name LIKE '%physical count%' OR t.name LIKE '%stock count%') AND facility='" .$facility_code. "' ".$stock_param." AND d.batch_number = '" .$batch_no. "'"; $bacthes_initial_stock = $this->db->query($initial_stock_sql); $batch_initial_stock = $bacthes_initial_stock->result_array(); $x = count($batch_initial_stock); //echo $x.'<br>'; foreach ($batch_initial_stock as $key => $value2) { //If initial stock is not null if ($value2['Initial_stock'] != null) { //Get the balance for that batch //$batch_stock_balance_sql = "SELECT (SUM( ds.quantity ) - SUM( ds.quantity_out )) AS stock_levels, ds.batch_number FROM drug_stock_movement ds WHERE ds.transaction_date BETWEEN '" .$value2['transaction_date']. "' AND curdate() AND facility='" .$facility_code. "' ".$stock_param." AND ds.drug ='" .$drug_id. "' AND ds.batch_number ='" .$value2['batch']. "'"; $batch_stock_balance_sql = "SELECT ds.quantity AS stock_levels, ds.batch_number FROM drug_stock_movement ds WHERE ds.transaction_date BETWEEN '" . $value2['transaction_date'] . "' AND curdate() AND facility='" . $facility_code . "' " . $stock_param . " AND ds.drug ='" . $drug_id . "' AND ds.batch_number ='" . $value2['batch'] . "' ORDER BY ds.id DESC LIMIT 1"; $bacthes_balance = $this->db->query($batch_stock_balance_sql); $batch_balance_array = $bacthes_balance->result_array(); foreach ($batch_balance_array as $key => $value3) { //Save balance in drug_stock_balance table if ($value3['stock_levels'] > 0) { $batch_balance_save = $value3['stock_levels']; } else { $batch_balance_save = 0; } $batch_number_save = $batch_no; $drug_id_save = $drug_id; $expiry_date_save = $expiry_date; $insert_balance_sql = "INSERT INTO drug_stock_balance(drug_id,batch_number,stock_type,expiry_date,facility_code,balance) VALUES('" . $drug_id_save . "','" . $batch_number_save . "','" . $stock_type . "','" . $expiry_date_save . "','" . $facility_code . "','" . $batch_balance_save . "') ON DUPLICATE KEY UPDATE balance='" . $batch_balance_save . "'"; $q = $this->db->query($insert_balance_sql); if (!$q) { $not_saved++; } } } else { //Get the balance for that batch $batch_stock_balance_sql = "SELECT (SUM( ds.quantity ) - SUM( ds.quantity_out ) ) AS stock_levels, ds.batch_number FROM drug_stock_movement ds WHERE ds.drug = '" . $drug_id . "' AND ds.expiry_date > curdate() AND facility='" . $facility_code . "' " . $stock_param . " AND ds.batch_number='" . $value2['batch'] . "'"; //echo $batch_stock_balance_sql;die(); $bacthes_balance = $this->db->query($batch_stock_balance_sql); $batch_balance_array = $bacthes_balance->result_array(); foreach ($batch_balance_array as $key => $value3) { //Store balance in drug_stock_balance table $batch_balance_save = $value3['stock_levels']; if ($value3['stock_levels'] > 0) { $batch_balance_save = $value3['stock_levels']; } else { $batch_balance_save = 0; } $batch_number_save = $batch_no; $drug_id_save = $drug_id; $expiry_date_save = $expiry_date; $insert_balance_sql = "INSERT INTO drug_stock_balance(drug_id,batch_number,stock_type,expiry_date,facility_code,balance) VALUES('" . $drug_id_save . "','" . $batch_number_save . "','" . $stock_type . "','" . $expiry_date_save . "','" . $facility_code . "','" . $batch_balance_save . "') ON DUPLICATE KEY UPDATE balance='" . $batch_balance_save . "'"; $q = $this->db->query($insert_balance_sql); if (!$q) { $not_saved++; } } } } } }
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 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}"); }