public function listing()
{
$access_level = $this->session->userdata('user_indicator');
$source = 0;
if ($access_level == "pharmacist") {
$source = $this->session->userdata('facility');
}
$data = array();
$drugcodes = Drugcode::getAll($source, $access_level);
$tmpl = array('table_open' => '<table id="drugcode_setting" class="setting_table table table-bordered table-striped">');
$this->table->set_template($tmpl);
$this->table->set_heading('id', 'Drug', 'Unit', 'Dose', 'Supplier', 'Options');
foreach ($drugcodes as $drugcode) {
$array_param = array('id' => $drugcode['id'], 'role' => 'button', 'class' => 'edit_user', 'data-toggle' => 'modal');
$links = "";
if ($drugcode['Enabled'] == 1) {
$links .= anchor('#edit_drugcode', 'Edit', $array_param);
}
$drug = $drugcode['id'];
if ($drugcode['Enabled'] == 1 && $access_level == "facility_administrator") {
$links .= " | ";
$links .= anchor('drugcode_management/disable/' . $drugcode['id'], 'Disable', array('class' => 'disable_user'));
$links .= " | ";
$links .= "<a href='#' class='merge_drug' id='{$drug}'>Merge</a>";
} elseif ($access_level == "facility_administrator") {
$links .= anchor('drugcode_management/enable/' . $drugcode['id'], 'Enable', array('class' => 'enable_user'));
}
if ($drugcode['Merged_To'] != '') {
if ($access_level == "facility_administrator") {
$links .= " | ";
$links .= anchor('drugcode_management/unmerge/' . $drugcode['id'], 'Unmerge', array('class' => 'unmerge_drug'));
}
$checkbox = "<input type='checkbox' name='drugcodes' id='drugcodes' class='drugcodes' value='{$drug}' disabled/>";
} else {
$checkbox = "<input type='checkbox' name='drugcodes' id='drugcodes' class='drugcodes' value='{$drug}'/>";
}
$mapped = "";
if ($drugcode['map'] != 0) {
$mapped = "<b>(mapped)</b>";
}
$this->table->add_row($drugcode['id'], $checkbox . " " . strtoupper($drugcode['Drug']) . " " . $mapped, "<b>" . $drugcode['drug_unit'] . "</b>", "<b>" . $drugcode['Dose'] . "</b>", "<b>" . $drugcode['supplier'] . "</b>", $links);
}
$data['drugcodes'] = $this->table->generate();
$data['suppliers'] = Drug_Source::getAllHydrated();
$data['classifications'] = Drug_Classification::getAllHydrated($access_level, "0");
$query = $this->db->query("SELECT s.id,CONCAT_WS('] ',CONCAT_WS(' [',s.name,s.abbreviation),CONCAT_WS(' | ',s.strength,s.formulation)) as name,s.packsize\n FROM sync_drug s \n WHERE s.id NOT IN(SELECT dc.map\n FROM drugcode dc\n WHERE dc.map !='0')\n AND (s.category_id='1' or s.category_id='2' or s.category_id='3')\n ORDER BY name asc");
$data['edit_mappings'] = $query->result_array();
$data['mappings'] = Sync_Drug::getActive();
$data['instructions'] = Drug_instructions::getAllInstructions();
$this->base_params($data);
}
public function getItems()
{
//Default row values
$row = array('beginning_balance' => 0, 'received_from' => 0, 'dispensed_to_patients' => 0, 'losses' => 0, 'adjustments' => 0, 'adjustments_neg' => 0, 'physical_stock' => 0, 'expiry_qty' => 0, 'expiry_month' => "--", 'stock_out' => 0, 'resupply' => 0);
//Set parameters
$param = array("drug_id" => $this->input->post("drug_id"), "period_begin" => $this->input->post("period_begin"), "facility_id" => $this->input->post("facility_id"), "code" => $this->input->post("code"), "stores" => $this->input->post("stores"));
$code = $param['code'];
$facility_id = $param['facility_id'];
$period_begin = date('Y-m-01', strtotime($param['period_begin']));
$period_end = date('Y-m-t', strtotime($param['period_begin']));
$stores = $param['stores'];
$stores = implode(",", $stores);
$stores = str_replace("multiselect-all,", "", $stores);
$drug_id = $param['drug_id'];
//get packsize
$drug = Sync_Drug::getPackSize($drug_id);
$pack_size = $drug['packsize'];
//check whether a satellite,standalone or central site
$facility_code = $this->session->userdata("facility");
$facility_type = Facilities::getType($facility_code);
$row['beginning_balance'] = $this->getBeginningBalance($param);
$row['pack_size'] = $pack_size;
$row = $this->getOtherTransactions($param, $row);
if ($row['stock_out'] == null) {
$row['stock_out'] = 0;
}
if ($facility_type > 1) {
//central site
if ($code == "D-CDRR") {
//reported_consumed & reported_stock_on_hand
$reported_consumed = 0;
$reported_count = 0;
$satellites = Sync_Facility::getSatellites($facility_id);
foreach ($satellites as $satellite) {
$satellite_site = $satellite['id'];
$sql = "SELECT ci.drug_id,SUM(ci.dispensed_units) as consumed,SUM(ci.count) as phy_count\n\t\t\t\t\t\t FROM cdrr c\n\t\t\t\t\t\t LEFT JOIN cdrr_item ci ON ci.cdrr_id=c.id\n\t\t\t\t\t\t WHERE c.period_begin='{$period_begin}' \n\t\t\t\t\t\t AND c.period_end='{$period_end}'\n\t\t\t\t\t\t AND ci.drug_id='{$drug_id}'\n\t\t\t\t\t\t AND c.status LIKE '%approved%'\n\t\t\t\t\t\t AND c.facility_id='{$satellite_site}'\n\t\t\t\t\t\t GROUP BY ci.drug_id";
$query = $this->db->query($sql);
$results = $query->result_array();
if (!$results) {
//if satellite did not report use previous period
$start_date = date('Y-m-01', strtotime($period_begin . "-1 month"));
$end_date = date('Y-m-t', strtotime($period_end . "-1 month"));
$sql = "SELECT ci.drug_id,SUM(ci.dispensed_units) as consumed,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\t\t AND c.period_end='{$end_date}'\n\t\t\t\t\t\t\t AND ci.drug_id='{$drug_id}'\n\t\t\t\t\t\t\t AND c.facility_id='{$satellite_site}'\n\t\t\t\t\t\t\t GROUP BY ci.drug_id";
$query = $this->db->query($sql);
$results = $query->result_array();
}
if ($results) {
$reported_consumed += @$results[0]['consumed'];
$reported_count += @$results[0]['phy_count'];
}
}
//append to json array
$row['reported_consumed'] = $reported_consumed;
$row['reported_physical_stock'] = $reported_count;
//get issued to satellites as dispensed_to patients
$sql = "SELECT SUM(dsm.quantity_out) AS 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 LEFT JOIN transaction_type t ON t.id=dsm.transaction_type\n\t\t\t WHERE dsm.transaction_date \n\t\t\t BETWEEN '{$period_begin}' \n\t\t\t AND '{$period_end}' \n\t\t\t AND sd.id = '{$drug_id}'\n\t\t\t AND t.name LIKE '%issue%'\n\t\t\t AND dsm.ccc_store_sp IN({$stores})";
$query = $this->db->query($sql);
$results = $query->result_array();
$row['dispensed_to_patients'] = 0;
if ($results) {
if ($results[0]['total'] != null) {
$row['dispensed_to_patients'] = $results[0]['total'];
}
}
}
}
// Changes made on DCDRR
if ($code == "D-CDRR") {
foreach ($row as $i => $v) {
$exempted_columns = array('expiry_month', 'beginning_balance', 'reported_consumed', 'reported_physical_stock');
if (!in_array($i, $exempted_columns)) {
$row[$i] = round(@$v / @$pack_size);
}
}
//Get Physical Count
$row['physical_stock'] = $row['beginning_balance'] + $row['received_from'] - $row['dispensed_to_patients'] - $row['losses'] + $row['adjustments'];
//Get Resupply
$row['resupply'] = $row['reported_consumed'] * 3 - $row['physical_stock'];
} else {
$row['physical_stock'] = $row['beginning_balance'] + $row['received_from'] - $row['dispensed_to_patients'] - $row['losses'] + $row['adjustments'];
$row['resupply'] = $row['dispensed_to_patients'] * 3 - $row['physical_stock'];
}
if ($code == "F-CDRR_packs") {
foreach ($row as $i => $v) {
if ($i != "expiry_month" && $i != "dispensed_to_patients" && $i != "beginning_balance") {
$row[$i] = round(@$v / @$pack_size);
}
}
$row['dispensed_packs'] = 0;
if ($row['dispensed_to_patients'] > 0) {
$row['dispensed_packs'] = round(@$row['dispensed_to_patients'] / @$pack_size);
}
}
echo json_encode($row);
}
