public function reporting_notification()
{
$deadline = date('Y-m-10');
$today = date('Y-m-d');
$notification_days = 10;
$notification = "";
$message = "";
$notification_link = site_url('order/satellites_reported');
//get reporting satellites
$start_date = date('Y-m-01', strtotime("-1 month"));
$facility_code = $this->session->userdata("facility");
$central_site = Sync_Facility::getId($facility_code, 0);
$central_site = $central_site['id'];
$sql = "SELECT \n\t\t sf.name as facility_name,\n\t\t sf.code as facility_code,\n\t\t IF(c.id,'reported','not reported') as status\n\t\t FROM sync_facility sf\n\t\t LEFT JOIN cdrr c ON c.facility_id=sf.id AND c.period_begin='{$start_date}' \n\t\t WHERE sf.parent_id='{$central_site}'\n\t\t AND sf.category LIKE '%satellite%'\n\t\t AND sf.name NOT LIKE '%dispensing%'\n\t\t GROUP BY sf.id";
$query = $this->db->query($sql);
$satellites = $query->result_array();
if ($satellites) {
if ($deadline > $today) {
$diff = abs(strtotime($deadline) - strtotime($today));
$years = floor($diff / (365 * 60 * 60 * 24));
$months = floor(($diff - $years * 365 * 60 * 60 * 24) / (30 * 60 * 60 * 24));
$period = floor(($diff - $years * 365 * 60 * 60 * 24 - $months * 30 * 60 * 60 * 24) / (60 * 60 * 24));
if ($notification_days >= $period) {
$notification = "<li><a href='" . $notification_link . "'><i class='icon-th'></i>Reporting Deadline<div class='badge badge-important'>" . $period . " days</div></a><li>";
}
}
}
echo $notification;
}
public function update_reporting()
{
$deadline = date('Y-m-10');
$today = date('Y-m-d');
$notification_days = 10;
$notification = "";
$message = "";
$notification_link = site_url('order');
if ($deadline > $today) {
$diff = abs(strtotime($deadline) - strtotime($today));
$years = floor($diff / (365 * 60 * 60 * 24));
$months = floor(($diff - $years * 365 * 60 * 60 * 24) / (30 * 60 * 60 * 24));
$period = floor(($diff - $years * 365 * 60 * 60 * 24 - $months * 30 * 60 * 60 * 24) / (60 * 60 * 24));
if ($notification_days >= $period) {
$notification = "Dear webADT User,<br/>";
$notification .= "The order reporting deadline is in " . $period . " days.<br/>";
$notification .= "The Satellites List is below: <br/>";
}
//get reporting satellites
$start_date = date('Y-m-01', strtotime("-1 month"));
$facility_code = $this->session->userdata("facility");
$central_site = Sync_Facility::getId($facility_code, 0);
$central_site = $central_site['id'];
$sql = "SELECT sf.name as facility_name,sf.code as facility_code,IF(c.id,'reported','not reported') as status\n\t\t\t FROM sync_facility sf\n\t\t\t LEFT JOIN cdrr c ON c.facility_id=sf.id AND c.period_begin='{$start_date}' \n\t\t\t WHERE sf.parent_id='{$central_site}'\n\t\t\t AND sf.category LIKE '%satellite%'\n\t\t\t AND sf.name NOT LIKE '%dispensing%'\n\t\t\t GROUP BY sf.id";
$query = $this->db->query($sql);
$satellites = $query->result_array();
$notification .= "<table border='1'>";
$notification .= "<thead><tr><th>Name</th><th>Code</th><th>Status</th></tr></thead><tbody>";
if ($satellites) {
foreach ($satellites as $satellite) {
$notification .= "<tr><td>" . $satellite['facility_name'] . "</td><td>" . $satellite['facility_code'] . "</td><td>" . $satellite['status'] . "</td></tr>";
}
}
$notification .= "</tbody></table>";
//send notification via email
ini_set("SMTP", "ssl://smtp.gmail.com");
ini_set("smtp_port", "465");
$sql = "SELECT DISTINCT(Email_Address) as email \n\t\t\t FROM users u\n\t\t\t LEFT JOIN access_level al ON al.id=u.Access_Level\n\t\t\t WHERE al.Level_Name LIKE '%facility%' \n AND u.Facility_Code = '{$facility_code}'\n\t\t\t AND Email_Address !=''\n\t\t\t AND Email_Address !='*****@*****.**'";
$query = $this->db->query($sql);
$emails = $query->result_array();
if ($emails) {
foreach ($emails as $email) {
$mail_list[] = $email['email'];
}
}
if (!empty($mail_list)) {
$mail_list = implode(",", $mail_list);
$config['mailtype'] = "html";
$config['protocol'] = 'smtp';
$config['smtp_host'] = 'ssl://smtp.googlemail.com';
$config['smtp_port'] = 465;
$config['smtp_user'] = stripslashes('*****@*****.**');
$config['smtp_pass'] = stripslashes('WebAdt_052013');
$this->load->library('email', $config);
$this->email->set_newline("\r\n");
$this->email->from('*****@*****.**', "WEB_ADT CHAI");
$this->email->to("{$mail_list}");
$this->email->subject("ORDER REPORTING NOTIFICATION");
$this->email->message("{$notification}");
if ($this->email->send()) {
$message = 'Reporting Notification was sent!<br/>';
$this->email->clear(TRUE);
} else {
$message = 'Reporting Notification Failed!<br/>';
}
}
}
return $message;
}
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);
}
public function listing()
{
$access_level = $this->session->userdata('user_indicator');
$user_type = "1";
$facilities = "";
//If user is a super admin, allow him to add only facilty admin and nascop pharmacist
if ($access_level == "system_administrator") {
$user_type = "indicator='nascop_pharmacist' or indicator='facility_administrator'";
$facilities = Facilities::getAll();
$users = Users::getAll();
} else {
if ($access_level == "facility_administrator") {
$facility_code = $this->session->userdata('facility');
$user_type = "indicator='pharmacist'";
$facilities = Facilities::getCurrentFacility($facility_code);
$q = "u.Facility_Code='" . $facility_code . "' and Access_Level !='1' and Access_Level !='4'";
$users = Users::getUsersFacility($q);
}
}
$user_types = Access_Level::getAll($user_type);
$tmpl = array('table_open' => '<table class=" table table-bordered table-striped setting_table ">');
$this->table->set_template($tmpl);
$this->table->set_heading('id', 'Name', 'Email Address', 'Phone Number', 'Access Level', 'Registered By', 'Options');
foreach ($users as $user) {
$links = "";
$array_param = array('id' => $user['id'], 'role' => 'button', 'class' => 'edit_user', 'data-toggle' => 'modal');
//Is user is a system admin, allow him to edit only system admin and nascop users
if ($access_level == "system_administrator") {
if ($user['Access'] == "System Administrator" or $user['Access'] == "NASCOP Pharmacist" or $user['Access'] == "Facility Administrator") {
//$links = anchor('user_management/edit/' . $user['id'], 'Edit', array('class' => 'edit_user', 'id' => $user['id']));
//$links = anchor('#edit_user', 'Edit', $array_param);
//$links .= " | ";
} else {
$links = "";
}
} else {
//$links = anchor('user_management/edit/' . $user['id'], 'Edit', array('class' => 'edit_user', 'id' => $user['id']));
//Only show edit link for pharmacists
if ($user['Access'] == "Pharmacist" || $user['Access'] == "User") {
//$links = anchor('#edit_user', 'Edit', $array_param);
}
}
if ($user['Active'] == 1) {
if ($access_level == "system_administrator") {
//$links .= " | ";
$links .= anchor('user_management/disable/' . $user['id'], 'Disable', array('class' => 'disable_user'));
} else {
if ($access_level == "facility_administrator" and $user['Access'] == "Pharmacist") {
//$links .= " | ";
$links .= anchor('user_management/disable/' . $user['id'], 'Disable', array('class' => 'disable_user'));
}
}
} else {
//$links .= " | ";
$links .= anchor('user_management/enable/' . $user['id'], 'Enable', array('class' => 'enable_user'));
}
if ($user['Access'] == "Pharmacist") {
$level_access = "User";
} else {
$level_access = $user['Access'];
}
$this->table->add_row($user['id'], $user['Name'], $user['Email_Address'], $user['Phone_Number'], $level_access, $user['Creator'], $links);
}
$data['users'] = $this->table->generate();
$data['user_types'] = $user_types;
$data['facilities'] = $facilities;
$data['order_sites'] = Sync_Facility::get_active();
$data['title'] = "System Users";
//$data['content_view'] = "users_v";
$data['banner_text'] = "System Users";
$data['link'] = "users";
$actions = array(0 => array('Edit', 'edit'), 1 => array('Disable', 'disable'));
$data['actions'] = $actions;
$this->load->view("users_v", $data);
}