public function getCommodityStatusValues($drug_id, $start_date, $end_date, $stock_type = 1)
{
$period = 180;
$facility_code = $this->session->userdata("facility");
$pack_size = Sync_Drug::getPackSize($drug_id);
$pack_size = $pack_size['packsize'];
$beginning_balance = 0;
//D-CDRR
if ($stock_type == 1) {
//get satellites
$central_site = Sync_Facility::getId($facility_code, 0);
$central_site = $central_site['id'];
$satellites = Sync_Facility::getSatellites($central_site);
//get beginning balance as physical stock of last period
$period_begin = date('Y-m-d', strtotime($start_date . "-1 month"));
$facility_id = $central_site;
$beginning_balance = Cdrr_Item::getLastPhysicalStock($period_begin, $drug_id, $facility_id);
foreach ($satellites as $satellite) {
$satellite_site = $satellite['id'];
$sql = "SELECT ci.drug_id,SUM(ci.received) as received,SUM(ci.count) as phy_count\n\t\t\t\t\t FROM cdrr c\n\t\t\t\t\t LEFT JOIN cdrr_item ci ON ci.cdrr_id=c.id\n\t\t\t\t\t WHERE c.period_begin='{$start_date}' \n\t\t\t\t\t AND c.period_end='{$end_date}'\n\t\t\t\t\t AND ci.drug_id='{$drug_id}'\n\t\t\t\t\t AND c.facility_id='{$satellite_site}'\n\t\t\t\t\t GROUP BY ci.drug_id";
$query = $this->db->query($sql);
$results = $query->result_array();
if ($results) {
$row['reported_consumed'] = ceil(@$results[0]['received'] / @$pack_size);
$row['reported_count'] = ceil(@$results[0]['phy_count'] / @$pack_size);
} else {
$start_date = date('Y-m-01', strtotime($start_date . "-1 month"));
$end_date = date('Y-m-t', strtotime($end_date . "-1 month"));
$sql = "SELECT ci.drug_id,SUM(ci.received) as received,SUM(ci.count) as phy_count\n\t\t\t\t\t FROM cdrr c\n\t\t\t\t\t LEFT JOIN cdrr_item ci ON ci.cdrr_id=c.id\n\t\t\t\t\t WHERE c.period_begin='{$start_date}' \n\t\t\t\t\t AND c.period_end='{$end_date}'\n\t\t\t\t\t AND ci.drug_id='{$drug_id}'\n\t\t\t\t\t AND c.facility_id='{$satellite_site}'\n\t\t\t\t\t GROUP BY ci.drug_id";
$query = $this->db->query($sql);
$results = $query->result_array();
$row['reported_consumed'] = ceil(@$results[0]['received'] / @$pack_size);
$row['reported_count'] = ceil(@$results[0]['phy_count'] / @$pack_size);
}
}
$where = "AND (dsm.source='{$facility_code}' or dsm.destination='{$facility_code}') and dsm.source!=dsm.destination";
} else {
if ($stock_type == 2 || $stock_type == 3) {
//get beginning balance as physical stock of last period
$period_begin = date('Y-m-d', strtotime($start_date . "-1 month"));
$site = Sync_Facility::getId($facility_code, $stock_type);
$facility_id = $site['id'];
$beginning_balance = Cdrr_Item::getLastPhysicalStock($period_begin, $drug_id, $facility_id);
$where = "AND dsm.source=dsm.destination and dsm.source='{$facility_code}'";
}
}
$sql = "SELECT trans.name, trans.id, trans.effect, dsm.in_total, dsm.out_total \n\t\t\t FROM (SELECT id, name, effect \n\t\t\t FROM transaction_type \n\t\t\t WHERE name LIKE '%received%' \n\t\t\t OR name LIKE '%adjustment%' \n\t\t\t OR name LIKE '%return%' \n\t\t\t OR name LIKE '%dispense%' \n\t\t\t OR name LIKE '%issue%' \n\t\t\t OR name LIKE '%loss%') AS trans \n\t\t\t LEFT JOIN (SELECT dsm.transaction_type, SUM( dsm.quantity ) AS in_total, SUM( dsm.quantity_out ) AS out_total \n\t\t\t FROM drug_stock_movement dsm\n\t\t\t LEFT JOIN drugcode d ON d.id=dsm.drug\n\t\t\t LEFT JOIN sync_drug sd ON d.map=sd.id\n\t\t\t WHERE dsm.transaction_date \n\t\t\t BETWEEN '{$start_date}' \n\t\t\t AND '{$end_date}' \n\t\t\t AND sd.id = '{$drug_id}'\n\t\t\t AND dsm.facility='{$facility_code}' \n\t\t\t {$where} \n\t\t\t GROUP BY transaction_type) AS dsm ON trans.id = dsm.transaction_type \n\t\t\t GROUP BY trans.name";
$query = $this->db->query($sql);
$results = $query->result_array();
$total = 0;
if ($results) {
foreach ($results as $result) {
$effect = $result['effect'];
$trans_name = str_replace(array(" ", "(-)", "(+)", "/"), array("_", "_", "plus", "_"), $result['name']);
if ($effect == 1) {
if ($result['in_total'] != null) {
$total = (int) $result['in_total'];
} else {
$total = 0;
}
} else {
if ($result['out_total'] != null) {
$total = (int) $result['out_total'];
} else {
$total = 0;
}
}
if ($stock_type == 1) {
$row[$trans_name] = ceil(@$total / @$pack_size);
} else {
if ($stock_type == 3) {
$row[$trans_name] = ceil(@$total / @$pack_size);
} else {
$row[$trans_name] = $total;
}
}
}
}
$row['beginning_balance'] = $beginning_balance;
if ($stock_type == 1) {
$row["stock_out_days"] = ceil(@$row["stock_out_days"] / @$pack_size);
$row["stock_to_expire"] = ceil(@$row["stock_to_expire"] / @$pack_size);
$row['physical_stock'] = ceil(@$row['physical_stock'] / @$pack_size);
$row['beginning_balance'] = ceil(@$row['beginning_balance'] / @$pack_size);
$other_satellites = Sync_Facility::getOtherSatellites($central_site, $facility_code);
$my_satellites = array();
foreach ($other_satellites as $others) {
$my_satellites[] = $others['id'];
}
$my_satellites = implode(",", $my_satellites);
//get total issued to satellites and dispensed at central site
$sql = "SELECT dispense.total_dispensed,issued.total_issued\n\t\t\t FROM\n\t\t\t (SELECT SUM(dsm.quantity_out) as total_dispensed\n\t\t\t FROM drug_stock_movement dsm \n\t\t\t LEFT JOIN drugcode d ON d.id=dsm.drug\n\t\t\t LEFT JOIN sync_drug sd ON d.map=sd.id\n\t\t\t LEFT JOIN transaction_type t ON t.id=dsm.transaction_type\n\t\t\t WHERE dsm.transaction_date \n\t\t\t BETWEEN '{$start_date}' \n\t\t\t AND '{$end_date}' \n\t\t\t AND sd.id = '{$drug_id}'\n\t\t\t AND dsm.source=dsm.destination \n\t\t\t AND dsm.source='{$facility_code}'\n\t\t\t AND t.name LIKE '%dispense%')as dispense,\n\t\t\t (SELECT SUM(dsm.quantity_out) as total_issued\n\t\t\t FROM drug_stock_movement dsm \n\t\t\t LEFT JOIN drugcode d ON d.id=dsm.drug\n\t\t\t LEFT JOIN sync_drug sd ON d.map=sd.id\n\t\t\t LEFT JOIN transaction_type t ON t.id=dsm.transaction_type\n\t\t\t WHERE dsm.transaction_date \n\t\t\t BETWEEN '{$start_date}' \n\t\t\t AND '{$end_date}' \n\t\t\t AND sd.id = '{$drug_id}'\n\t\t\t AND Source_Destination IN({$my_satellites})\n\t\t\t {$where}\n\t\t\t AND t.name LIKE '%issue%')as issued";
$query = $this->db->query($sql);
$results = $query->result_array();
$row['Dispensed_to_Patients'] = ceil(($results[0]['total_dispensed'] + $results[0]['total_issued']) / @$pack_size);
} else {
if ($stock_type == 3) {
$row['dispensed_packs'] = $row['Dispensed_to_Patients'];
$row['Dispensed_to_Patients'] = $row['Dispensed_to_Patients'] * @$pack_size;
}
}
//Get End of Month Physical Stock
$sql = "SELECT phy.id,phy.physical_stock,exp.stocks_qty,exp.early_expiry,phy.stock_out_days\n\t\t\t\tFROM\n\t\t\t\t(SELECT dsb.drug_id AS id, SUM( dsb.balance ) AS physical_stock,IF(SUM(balance)>0,'0',DATEDIFF(CURDATE(),dsb.last_update)) as stock_out_days\n\t\t\t\tFROM drug_stock_balance dsb\n\t\t\t\tLEFT JOIN drugcode d ON d.id=dsb.drug_id\n\t\t\t LEFT JOIN sync_drug sd ON d.map=sd.id\n\t\t\t\tWHERE dsb.balance >0\n\t\t\t\tAND sd.id = '{$drug_id}'\n\t\t\t\tAND dsb.stock_type = '{$stock_type}'\n\t\t\t\tAND DATEDIFF( dsb.expiry_date, CURDATE( ) ) >=0\n\t\t\t\tGROUP BY dsb.drug_id) as phy\n\t\t\t\tLEFT JOIN \n\t\t\t\t(SELECT dsb.drug_id AS id,SUM(dsb.balance ) AS stocks_qty, dsb.expiry_date AS early_expiry\n\t\t\t\tFROM drug_stock_balance dsb\n\t\t\t\tLEFT JOIN drugcode d ON d.id=dsb.drug_id\n\t\t\t LEFT JOIN sync_drug sd ON d.map=sd.id\n\t\t\t\tWHERE DATEDIFF( dsb.expiry_date, CURDATE( ) ) <='{$period}'\n\t\t\t\tAND DATEDIFF( dsb.expiry_date, CURDATE( ) ) >=0\n\t\t\t\tAND dsb.balance >0\n\t\t\t\tAND sd.id ='{$drug_id}'\n\t\t\t\tAND dsb.stock_type ='{$stock_type}'\n\t\t\t\tGROUP BY dsb.drug_id\n\t\t\t\tORDER BY dsb.expiry_date) as exp ON phy.id=exp.id";
$query = $this->db->query($sql);
$results = $query->result_array();
if ((int) @$results[0]['stocks_qty'] > 0) {
$row["early_expiry"] = date('d-M-Y', strtotime($results[0]['early_expiry']));
} else {
$row["early_expiry"] = "-";
}
if (@$results[0]['stock_out_days'] == null) {
$row["stock_out_days"] = date('d');
} else {
$row["stock_out_days"] = $results[0]['stock_out_days'];
}
$row["stock_to_expire"] = (int) @$results[0]['stocks_qty'];
$row['drug'] = $drug_id;
if (@$results[0]['physical_stock'] == null) {
$physical_stock = 0;
} else {
$physical_stock = $beginning_balance + $row['Received_From'] - $row['Dispensed_to_Patients'] - $row['Losses__'] + $row['Adjustment_plus'] - $row['Adjustment__'];
if ($stock_type == 1) {
$period_begin = date('Y-m-d', strtotime($start_date . "-1 month"));
$site = Sync_Facility::getId($facility_code, 2);
$facility_id = $site['id'];
$beginning_balance = Cdrr_Item::getLastPhysicalStock($period_begin, $drug_id, $facility_id);
$sql = "SELECT trans.name, trans.id, trans.effect, dsm.in_total, dsm.out_total \n\t\t\t FROM (SELECT id, name, effect \n\t\t\t FROM transaction_type \n\t\t\t WHERE name LIKE '%received%' \n\t\t\t OR name LIKE '%adjustment%' \n\t\t\t OR name LIKE '%return%' \n\t\t\t OR name LIKE '%dispense%' \n\t\t\t OR name LIKE '%issue%' \n\t\t\t OR name LIKE '%loss%') AS trans \n\t\t\t LEFT JOIN (SELECT dsm.transaction_type, SUM( dsm.quantity ) AS in_total, SUM( dsm.quantity_out ) AS out_total \n\t\t\t FROM drug_stock_movement dsm\n\t\t\t LEFT JOIN drugcode d ON d.id=dsm.drug\n\t\t\t LEFT JOIN sync_drug sd ON d.map=sd.id\n\t\t\t WHERE dsm.transaction_date \n\t\t\t BETWEEN '{$start_date}' \n\t\t\t AND '{$end_date}' \n\t\t\t AND sd.id = '{$drug_id}'\n\t\t\t AND dsm.facility='{$facility_code}' \n\t\t\t {$where} \n\t\t\t GROUP BY transaction_type) AS dsm ON trans.id = dsm.transaction_type \n\t\t\t GROUP BY trans.name";
$query = $this->db->query($sql);
$results = $query->result_array();
$total = 0;
if ($results) {
foreach ($results as $result) {
$effect = $result['effect'];
$trans_name = str_replace(array(" ", "(-)", "(+)", "/"), array("_", "_", "plus", "_"), $result['name']);
if ($effect == 1) {
if ($result['in_total'] != null) {
$total = (int) $result['in_total'];
} else {
$total = 0;
}
} else {
if ($result['out_total'] != null) {
$total = (int) $result['out_total'];
} else {
$total = 0;
}
}
if ($stock_type == 1) {
$row[$trans_name] = ceil(@$total / @$pack_size);
} else {
if ($stock_type == 3) {
$row[$trans_name] = ceil(@$total / @$pack_size);
} else {
$row[$trans_name] = $total;
}
}
}
}
$stock = $physical_stock;
$physical_stock = $beginning_balance + $row['Received_From'] - $row['Dispensed_to_Patients'] - $row['Losses__'] + $row['Adjustment_plus'] - $row['Adjustment__'];
$physical_stock = ceil(@$physical_stock / @$pack_size);
$physical_stock = $stock + $physical_stock;
}
}
$row['physical_stock'] = $physical_stock;
$row['resupply'] = $this->getResupply($drug_id, $start_date, $facility_id);
echo json_encode($row);
}
