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