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 edit()
{
$drugcode_id = $this->input->post('drugcode_id');
$data['generic_names'] = Generic_Name::getAllActive();
$data['drug_units'] = Drug_Unit::getThemAll();
$data['doses'] = Dose::getAllActive();
$data['supporters'] = Supporter::getAllActive();
$data['doses'] = Dose::getAllActive();
$data['drugcodes'] = Drugcode::getDrugCodeHydrated($drugcode_id);
echo json_encode($data);
}