public function getConsumptionMos()
{
/*
Yearly Comparision - National
*/
$monthval = array("JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEP", "OCT", "NOV", "DEC");
$post = $this->form_values;
$products = $post['products'];
$yearcomp = $post['yearcomp'];
$all_provinces = $post['all_provinces'];
$all_districts = $post['all_districts'];
$optvals = $post['optvals'];
$period = new Model_Period();
$period->form_values = array('id' => $post['period']);
$months = $period->getPeriodById();
$location = new Model_Locations();
$location->form_values['pk_id'] = $all_provinces;
$location_name = $location->getLocationName();
$title = "Vaccination vs Stock On Hand (" . $location_name . "-" . $yearcomp[0] . ")";
for ($k = 0; $k < sizeof($products); $k++) {
$product_obj = new Model_ItemPackSizes();
$product_obj->form_values['pk_id'] = $products[$k];
$product_name = $product_obj->getProductName();
$xmlstore = "<chart exportEnabled='1' labelDisplay='rotate' slantLabels='1' yAxisMaxValue='100' exportAction='Download' caption='{$product_name} {$title}' exportFileName='" . $title . " - " . date('Y-m-d H:i:s') . " - " . $product_name . "' yAxisName='Doses' numberSuffix='' showValues='1' formatNumberScale='0' theme='fint'>";
$xmlstore .= "<categories>";
for ($i = $months->getBeginMonth(); $i <= $months->getEndMonth(); $i++) {
$month_name = $monthval[$i - 1];
$xmlstore .= "<category label='{$month_name}' />";
}
$xmlstore .= "</categories>";
$start_date = $yearcomp[0] . '-' . str_pad($months->getBeginMonth(), 2, "0", STR_PAD_LEFT) . "-01";
$end_date = $yearcomp[0] . '-' . str_pad($months->getEndMonth(), 2, "0", STR_PAD_LEFT) . "-01";
$sql = "SELECT\n summary_provincial.consumption,\n summary_provincial.soh_store\n FROM\n summary_provincial\n WHERE\n summary_provincial.item_id = '{$products[$k]}' AND\n summary_provincial.province_id = {$all_provinces} AND\n summary_provincial.stakeholder_id = 1 AND\n DATE_FORMAT(summary_provincial.reporting_date,'%Y-%m-%d') BETWEEN '{$start_date}' AND '{$end_date}'";
$str_sql = $this->_em->getConnection()->prepare($sql);
$str_sql->execute();
$rowData = $str_sql->fetchAll();
$xmlstore .= "<dataset seriesName='Consumption/Vaccination'>";
foreach ($rowData as $val2) {
$xmlstore .= "<set value='" . round($val2['consumption']) . "' />";
}
$xmlstore .= "</dataset>";
$xmlstore .= "<dataset seriesName='Stock On Hand(SOH)'>";
foreach ($rowData as $val2) {
$xmlstore .= "<set value='" . round($val2['soh_store']) . "' />";
}
$xmlstore .= "</dataset>";
$xmlstore .= "</chart>";
$xmlstore_array[] = $xmlstore;
}
return $xmlstore_array;
}
public function compGraphOptionGeoDistrict()
{
/*
District
*/
$monthval = array("JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEP", "OCT", "NOV", "DEC");
$post = $this->form_values;
$products = $post['products'];
$year1 = $post['yearcomp'];
$provinces = $post['all_provinces'];
$districts = $post['all_districts'];
$optvals = $post['optvals'];
$period = new Model_Period();
$period->form_values = array('id' => $post['period']);
$months = $period->getPeriodById();
$rep_option = new Model_ReportOptions();
$rep_option->form_values = array('stakeholder' => 1, 'report_id' => $post['indicators'], 'report_comp' => $optvals);
$query = $rep_option->getReportDataSql();
$title = $query->getReportTitleSql();
$location = new Model_Locations();
$loc_name = array();
if (!empty($provinces)) {
$location->form_values = array("pk_id" => $provinces);
$province_name = $location->getLocationName();
}
foreach ($districts as $dist_id) {
$location->form_values = array("pk_id" => $dist_id);
$loc_name[] = $location->getLocationName();
}
$location_name = implode(",", $loc_name);
for ($k = 0; $k < sizeof($products); $k++) {
$product_obj = new Model_ItemPackSizes();
$product_obj->form_values['pk_id'] = $products[$k];
$product_name = $product_obj->getProductName();
list($indicator, $compare_options) = explode("-", str_replace("Report", "Graph", str_replace("Province", "Provincial", $title)));
$graph_caption = $indicator . " of " . $product_name;
if ($optvals == 1) {
$graph_subcaption = $compare_options;
} elseif ($optvals == 2) {
$graph_subcaption = "Provincial " . $compare_options . " for " . $location_name;
} elseif ($optvals == 3) {
$graph_subcaption = "District " . $compare_options . " for " . $location_name;
} elseif ($optvals == 7) {
$graph_subcaption = "" . $compare_options . " for " . $location_name;
} elseif ($optvals == 8) {
$graph_subcaption = "" . $compare_options . " for " . $province_name . "(" . $location_name . ")";
}
$xmlstore = "<chart exportEnabled='1' labelDisplay='rotate' slantLabels='1' yAxisMaxValue='100' exportAction='Download' caption='" . $graph_caption . "' subCaption='" . $graph_subcaption . "' exportFileName='" . $title . " - " . date('Y-m-d H:i:s') . " - " . $location_name . " - " . $product_name . "' yAxisName='Doses' numberSuffix='' showValues='1' formatNumberScale='0' theme='fint'>";
$xmlstore .= "<categories>";
for ($i = $months->getBeginMonth(); $i <= $months->getEndMonth(); $i++) {
$month_name = $monthval[$i - 1];
$xmlstore .= "<category label='{$month_name}' />";
for ($j = 0; $j < sizeof($districts); $j++) {
$location_obj = new Model_Locations();
$location_obj->form_values['product_id'] = $districts[$j];
$loc_name = $location_obj->getLocationById();
$sql = "select " . str_replace("\$i", $i, $query->getReportDataSql()) . " as xyz from dual ";
$sql = str_replace("\$year1", $year1, $sql);
$sql = str_replace("'\$products[\$k]'", "'" . $products[$j] . "'", $sql);
$sql = str_replace("\$seluser", 1, $sql);
$sql = str_replace("\$provinces[0]", $provinces, $sql);
$sql = str_replace("\$dists[\$j]", $districts[$j], $sql);
$dbg_sql .= $sql . '<br>';
//echo $sql."; ";
$str_sql = $this->_em->getConnection()->prepare($sql);
$str_sql->execute();
$row = $str_sql->fetchAll();
if (!empty($row)) {
$res = explode('*', $row[0]['xyz']);
$row_data = $res[$query->getReportDataPosition()] / 1;
$filedata1[$loc_name][$monthval[$i - 1]] = $row_data;
}
}
}
//exit;
//echo "<pre>";
//print_r($filedata1);
//exit;
$xmlstore .= "</categories>";
foreach ($filedata1 as $key1 => $value1) {
$xmlstore .= "<dataset seriesName='{$key1}'>";
foreach ($value1 as $val2) {
$xmlstore .= "<set value='" . $val2 . "' />";
}
$xmlstore .= "</dataset>";
}
$xmlstore .= "</chart>";
$xmlstore_array[] = $xmlstore;
}
//var_dump($xmlstore_array);
//exit;
return $xmlstore_array;
}
public function getConsumptionByUc($dist_id)
{
$date = $this->form_values['date'];
list($yy, $mm) = explode("-", $date);
if (empty($dist_id)) {
$dist_id = $this->_identity->getDistrictId();
}
$role_id = $this->form_values['province'];
$teh_id = $this->form_values['teh_id'];
$cache = Zend_Registry::get('cacheManager')->getCache('file');
$mosucdashlet = "CONSD_{$yy}{$mm}{$teh_id}" . $this->form_values['item'];
if (!($result = $cache->load($mosucdashlet))) {
$em = Zend_Registry::get('doctrine');
if ($role_id == 2) {
$str_sql_con = "CALL REPgetConsumptionHF('D','" . $yy . "-" . $mm . "-01', {$teh_id}, '" . $this->form_values['item'] . "', 1);";
} else {
$str_sql_con = "CALL REPgetConsumption('D','" . $yy . "-" . $mm . "-01', {$teh_id}, '" . $this->form_values['item'] . "', 1);";
}
$row_consumption = $em->getConnection()->prepare($str_sql_con);
$row_consumption->execute();
$result_consumption = $row_consumption->fetchAll();
$em->getConnection()->close();
$result = array();
foreach ($result_consumption as $row) {
$result[$row['location_name']]['ucName'] = $row['location_name'];
$result[$row['location_name']]['CONS'] = $row['consumption'];
$result[$row['location_name']]['target'] = $row['target'];
}
$cache->save($result, $mosucdashlet);
}
// $str_sql = "SELECT IFNULL(A.CONS,0) as CONS,
// A.ucName,
// ROUND(((((A.population / 100) * B.population_percent_increase_per_year) / 100 * B.child_surviving_percent_per_year)* B.doses_per_year) / 12) AS target
// FROM (
// SELECT DISTINCT
// IFNULL(REPgetConsumption (" . $mm . "," . $yy . "," . $this->form_values['item'] . ",1,'U',locations.pk_id),0) AS CONS,
// locations.location_name AS ucName,
// (SELECT
// IFNULL(location_populations.population,0)
// FROM
// location_populations
// WHERE
// location_populations.location_id = locations.pk_id
//
// AND DATE_FORMAT(
// location_populations.estimation_date,
// '%Y'
// ) = '$yy'
// ) AS population
// FROM
// pilot_districts
// INNER JOIN locations ON pilot_districts.district_id = locations.district_id
//
// WHERE
// locations.geo_level_id = 6
// AND locations.parent_id = $teh_id ) A,
// (
// SELECT
// item_pack_sizes.item_name,
// items.population_percent_increase_per_year,
// items.child_surviving_percent_per_year,
// items.doses_per_year
// FROM
// item_pack_sizes
// INNER JOIN items ON item_pack_sizes.item_id = items.pk_id
// WHERE
// item_pack_sizes.pk_id = 6
// ) B";
//$row = $this->_em->getConnection()->prepare($str_sql);
//$row->execute();
//$result = $row->fetchAll();
$item_pack_sizes = new Model_ItemPackSizes();
$item_pack_sizes->form_values['pk_id'] = $this->form_values['item'];
$item = $item_pack_sizes->getProductName();
$locations = new Model_Locations();
$locations->form_values['pk_id'] = $teh_id;
$tehsil_name = $this->view->location_name = $locations->getLocationName();
$xmlstore = "<chart labelDisplay='rotate' exportEnabled='1' exportAction='Download' bgColor='white' caption='Consumption status of {$item}(Doses) in {$tehsil_name} Stores and EPI centers during " . ("(" . date('M Y', strtotime($this->form_values['date'])) . ")") . "'\n exportFileName='Districts Consumption " . date('Y-m-d H:i:s') . "' yAxisName='Doses' showValues='1' formatNumberScale='0' theme='fint'>";
$xmlstore .= '<categories>';
foreach ($result as $data) {
$coverage = ROUND($data['CONS'] / $data['target'] * 100, 1);
$xmlstore .= '<category label="' . $data[ucName] . ' (' . $coverage . '%)" />';
}
$xmlstore .= '</categories>';
$xmlstore .= "<dataset seriesname='Consumption' color='0075C2'>";
foreach ($result as $data) {
$xmlstore .= "<set value='{$data['CONS']}' />";
}
$xmlstore .= "</dataset>";
$xmlstore .= "<dataset seriesname='Target' parentyaxis='S' renderas='Line' color='f8bd19'>";
foreach ($result as $data) {
$xmlstore .= "<set value='{$data['target']}' />";
}
$xmlstore .= "</dataset>";
$xmlstore .= "</chart>";
return $xmlstore;
}
public function getConsumptionByUc($dist_id)
{
$date = $this->form_values['date'];
list($yy, $mm) = explode("-", $date);
if (empty($dist_id)) {
$dist_id = $this->_identity->getDistrictId();
}
$rows = $this->_em->getRepository('Warehouses')->findBy(array('district' => $dist_id));
$role_id = $rows[0]->getProvince()->getPkId();
$teh_id = $this->form_values['teh_id'];
$cache = Zend_Registry::get('cacheManager')->getCache('file');
$mosucdashlet = "CONSD_{$yy}{$mm}{$dist_id}{$teh_id}" . $this->form_values['item'];
if (!($result = $cache->load($mosucdashlet))) {
$em = Zend_Registry::get('doctrine');
$str_sql_con = "CALL REPgetConsumptionHF('D','" . $yy . "-" . $mm . "-01', {$teh_id}, '" . $this->form_values['item'] . "', 1);";
$row_consumption = $em->getConnection()->prepare($str_sql_con);
$row_consumption->execute();
$result_consumption = $row_consumption->fetchAll();
$em->getConnection()->close();
$result = array();
foreach ($result_consumption as $row) {
$result[$row['location_name']]['ucName'] = $row['location_name'];
$result[$row['location_name']]['CONS'] = $row['consumption'];
$result[$row['location_name']]['target'] = $row['target'];
}
$cache->save($result, $mosucdashlet);
}
$item_pack_sizes = new Model_ItemPackSizes();
$item_pack_sizes->form_values['pk_id'] = $this->form_values['item'];
$item = $item_pack_sizes->getProductName();
$locations = new Model_Locations();
$locations->form_values['pk_id'] = $teh_id;
$tehsil_name = $this->view->location_name = $locations->getLocationName();
$xmlstore = "<chart labelDisplay='rotate' exportEnabled='1' exportAction='Download' bgColor='white' caption='Vaccination status of {$item}(Doses) in {$tehsil_name} Stores and EPI centers during " . ("(" . date('M Y', strtotime($this->form_values['date'])) . ")") . "'\n exportFileName='Districts Consumption " . date('Y-m-d H:i:s') . "' yAxisName='Doses' showValues='1' formatNumberScale='0' theme='fint'>";
$xmlstore .= '<categories>';
foreach ($result as $data) {
$coverage = ROUND($data['CONS'] / $data['target'] * 100, 1);
$xmlstore .= '<category label="' . $data[ucName] . ' (' . $coverage . '%)" />';
}
$xmlstore .= '</categories>';
$xmlstore .= "<dataset seriesname='Consumption' color='0075C2'>";
foreach ($result as $data) {
$xmlstore .= "<set value='{$data['CONS']}' />";
}
$xmlstore .= "</dataset>";
$xmlstore .= "<dataset seriesname='Target' parentyaxis='S' renderas='Line' color='f8bd19'>";
foreach ($result as $data) {
$xmlstore .= "<set value='{$data['target']}' />";
}
$xmlstore .= "</dataset>";
$xmlstore .= "</chart>";
return $xmlstore;
}
public function districtsYearlyReportAction()
{
$this->_helper->layout->setLayout('reports');
$this->view->report_id = 'DISTRICTWAREHOUSE';
$this->view->report_title = 'District Yearly Report';
$this->view->actionpage = 'districts-yearly-report';
$this->view->parameters = 'TS01IP';
$this->view->parameter_width = '95%';
$item_pack_sizes = new Model_ItemPackSizes();
$location = new Model_Locations();
$warehouses_data = new Model_WarehousesData();
if ($this->_request->prov_sel) {
$province = $this->_request->prov_sel;
} else {
$province = 1;
}
if (!empty($this->_request->year_sel) && !empty($this->_request->ending_month)) {
$this->view->year_sel = $year = $this->_request->year_sel;
$this->view->month_sel = $month = $this->_request->ending_month;
} else {
$year = date("Y");
// if (date('d') > 10) {
// $month = date("m", strtotime("-1"));
// } else {
$month = date("m", strtotime("-2 month"));
// }
$this->view->year_sel = $year;
$this->view->month_sel = $month;
}
if (!empty($this->_request->rep_indicators)) {
$this->view->sel_indicator = $sel_indicator = $this->_request->rep_indicators;
} else {
$this->view->sel_indicator = $sel_indicator = 1;
}
if (!empty($this->_request->prod_sel)) {
$this->view->sel_prod = $this->_request->prod_sel;
} else {
$this->view->sel_prod = 28;
}
if (!empty($this->_request->prov_sel)) {
$this->view->prov_sel = $prov_sel = $this->_request->prov_sel;
} else {
$this->view->prov_sel = 1;
}
if (isset($this->_request->stk_sel) && !empty($this->_request->stk_sel)) {
$this->view->stk_sel = $sel_stk = $this->_request->stk_sel;
} else {
$this->view->stk_sel = $sel_stk = 1;
}
$item_pack_sizes = new Model_ItemPackSizes();
if ($this->_request->prod_sel) {
$item_pack_sizes->form_values['pk_id'] = $this->_request->prod_sel;
$this->view->item_name = $item_pack_sizes->getProductName();
} else {
$item_pack_sizes->form_values['pk_id'] = '28';
$this->view->item_name = $item_pack_sizes->getProductName();
}
if (isset($this->_request->dist_id) && !empty($this->_request->dist_id)) {
$locations = new Model_Locations();
$locations->form_values['geo_level_id'] = '4';
$locations->form_values['province_id'] = $this->_request->prov_sel;
$district = $locations->getLocationsByLevelByProvince();
$this->view->district = $district;
$this->view->sel_dist = $this->_request->dist_id;
$location->form_values['province_id'] = $province;
$location->form_values['district_id'] = $this->_request->dist_id;
$location_name = $location->tehsilLocationsDistrict();
$item = $item_pack_sizes->productsReport();
$this->view->item_id = $item;
$this->view->location_id = $location_name;
$locations->form_values['pk_id'] = $this->_request->dist_id;
$this->view->district_name = "District:" . ' ' . $locations->getLocationName();
} else {
$locations = new Model_Locations();
$locations->form_values['geo_level_id'] = '4';
$locations->form_values['province_id'] = '1';
$district = $locations->getLocationsByLevelByProvince();
$this->view->district = $district;
$this->view->sel_dist = "";
$location->form_values['province_id'] = $province;
$location_name = $location->getLocations();
$item = $item_pack_sizes->productsReport();
$this->view->item_id = $item;
$this->view->location_id = $location_name;
$this->view->district_name = "";
}
$locations = new Model_Locations();
if ($this->_request->prov_sel) {
$locations->form_values['pk_id'] = $this->_request->prov_sel;
$this->view->location_name = "Province:" . ' ' . $locations->getLocationName();
// $this->view->location_name = $locations->getLocationName();
} else {
$this->view->location_name = "Province:" . ' ' . "Punjab";
}
if ($sel_indicator == 1) {
$str_indicator = "\\'Consumption\\'";
} else {
if ($sel_indicator == 2) {
$str_indicator = "\\'Stock on Hand\\'";
} else {
if ($sel_indicator == 3) {
$str_indicator = "\\'Received\\'";
} else {
if ($sel_indicator == 4) {
$str_indicator = "\\'Issued\\'";
}
}
}
}
$this->view->str_indicator = $str_indicator;
$end_date1 = $year . '-' . $month . '-01';
$end_date = date('Y-m-d', strtotime("-1 days", strtotime("+1 month", strtotime($end_date1))));
$start_date = date('Y-m-d', strtotime("-364 days", strtotime($end_date)));
// Start date and End date
$begin = new DateTime($start_date);
$end = new DateTime($end_date);
$diff = $begin->diff($end);
$interval = DateInterval::createFromDateString('1 month');
$period = new DatePeriod($begin, $interval, $end);
$this->view->period = $period;
$this->view->sel_item = $this->_request->prod_sel;
$stakeholder = new Model_Stakeholders();
$stk = $stakeholder->nationReport();
$this->view->stk = $stk;
$locations = new Model_Locations();
$lct = $locations->devisionalReport();
$this->view->location = $lct;
$locations->form_values['province_id'] = '1';
$locations->form_values['district_id'] = '';
$lct2 = $locations->tehsilLocations();
$this->view->loc = $lct2;
}
public function stockAvailabilityReportAction()
{
$this->_helper->layout->setLayout('reports');
$q_str_pro = '';
$q_str_prov = '';
$sel_prov = '';
$warehouses_data = new Model_HfDataMaster();
$item_pack_sizes = new Model_ItemPackSizes();
$locations = new Model_Locations();
$stakeholders = new Model_Stakeholders();
$request_data = Zend_Controller_Front::getInstance()->getRequest()->getParams();
$item = $item_pack_sizes->productsReport();
$this->view->item_id = $item;
$this->view->report_id = 'SNASUMSTOCKLOC';
$this->view->report_title = 'Stock Availability Report';
$this->view->parameters = 'TS01P01I';
$this->view->cwh_name = '';
$this->view->num_item2 = '';
$this->view->total = 0;
$this->view->cwhtotal = 0;
$this->view->ppiutotal = 0;
$this->view->disttotal = 0;
$this->view->old1 = '';
$this->view->actionpage = 'stock-availability-report';
// $this->view->month_sel = $this->_request->month_sel;
// $this->view->year_sel = $this->_request->year_sel;
$this->view->sel_item = $this->_request->prod_sel;
$this->view->sel_stk = 1;
$this->view->prov_sel = $this->_request->prov_sel;
$lct = $locations->devisionalReport();
$this->view->location = $lct;
$warehouses_data->temp = $request_data;
$rs_items = $warehouses_data->generateStockAvailabilityReport();
$this->view->rs_items = $rs_items;
if (!empty($this->_request->year_sel) && !empty($this->_request->month_sel)) {
$this->view->year_sel = $year = $this->_request->year_sel;
$this->view->month_sel = $month = $this->_request->month_sel;
} else {
$year = date("Y");
// if (date('d') > 10) {
// $month = date("m", strtotime("-1"));
// } else {
$month = date("m", strtotime("-2 month"));
// }
$this->view->year_sel = $year;
$this->view->month_sel = $month;
}
if (!empty($this->_request->prod_sel)) {
$item_pack_sizes->form_values['pk_id'] = $this->_request->prod_sel;
$this->view->product_name = $item_pack_sizes->getProductName();
}
if ($this->_request->prov_sel == 'all') {
$this->view->location_name = "All";
} else {
if ($this->_request->prov_sel == '') {
$this->view->location_name = "Punjab";
} else {
$locations->form_values['pk_id'] = $this->_request->prov_sel;
$this->view->location_name = $locations->getLocationName();
}
}
if ($this->_request->stk_id == 'all' || $this->_request->stk_id == "") {
$this->view->stakeholder_name = "All";
} elseif (!empty($this->_request->stk_id)) {
$stakeholders->form_values['pk_id'] = $this->_request->stk_id;
$this->view->stakeholder_name = $stakeholders->getStakeholderName();
}
}