public function getReportedWastages()
{
$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'];
$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 = "Reporting Rate and Wastage Comparison (" . $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='Percentage' 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.wastages_percentage\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();
$rowWastage = $str_sql->fetchAll();
$xmlstore .= "<dataset seriesName='Wastage'>";
foreach ($rowWastage as $val2) {
$xmlstore .= "<set value='" . round($val2['wastages_percentage']) . "' />";
}
$xmlstore .= "</dataset>";
$sql = "SELECT\n summary_provincial.reporting_rate\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();
$rowRR = $str_sql->fetchAll();
$xmlstore .= "<dataset seriesName='Reporting Rate'>";
foreach ($rowRR as $val2) {
$xmlstore .= "<set value='" . round($val2['reporting_rate']) . "' />";
}
$xmlstore .= "</dataset>";
$obj_product = new Model_ItemPackSizes();
$prod_result = $obj_product->getProductById($products[0]);
$xmlstore .= "<trendlines>\n <line startvalue='" . $prod_result->getWastageRateAllowed() . "' color='EE2000' displayvalue='Wastage Allowed:" . $prod_result->getWastageRateAllowed() . "%' valueonright='1' />\n </trendlines>";
$xmlstore .= "</chart>";
$xmlstore_array[] = $xmlstore;
}
return $xmlstore_array;
}
public function ajaxWastagesComparisonAction()
{
$province = $this->_request->getParam("province");
$district = $this->_request->getParam("district");
$date = $this->_request->getParam("date");
$item = $this->_request->getParam("item");
$option = $this->_request->getParam("allowed");
$obj_product = new Model_ItemPackSizes();
$prod_result = $obj_product->getProductById($item);
$allowed = $prod_result->getWastageRateAllowed();
$wh_data = new Model_WarehousesData();
$wh_data->form_values = array('prov_id' => $province, 'dist_id' => $district, 'date' => $date, 'item' => $item, 'option' => $option, 'allowed' => $allowed);
$xmlstore = $wh_data->wastagesComparison();
$this->view->xmlstore = $xmlstore;
}
public function MSGraphReportedWastage()
{
/*
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 = "Reporting Rate and Wastage Comparison (" . $location_name . "-" . $yearcomp[0] . ")";
$cache = Zend_Registry::get('cacheManager')->getCache('file');
$reportedwastages = "REPORTEDWASTAGES_{$post['period']}{$yearcomp['0']}{$products['0']}{$all_provinces}";
if (!($xmlstore_array = $cache->load($reportedwastages))) {
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='Percentage' 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}' />";
}
$start_date = $yearcomp[0] . '-' . $months->getBeginMonth() . "-01";
$end_date = $yearcomp[0] . '-' . $months->getEndMonth() . "-01";
$sql = "select REPgetWastage('P','{$start_date}','{$end_date}',1,'{$products[$k]}',{$all_provinces},0) as xyz from dual ";
$str_sql = $this->_em->getConnection()->prepare($sql);
$str_sql->execute();
$row = $str_sql->fetchAll();
if (!empty($row)) {
$filedata1 = explode('*', $row[0]['xyz']);
}
$sql = "select REPgetRR('P','{$start_date}','{$end_date}',1,'{$products[$k]}',{$all_provinces},0) as xyz from dual ";
$str_sql = $this->_em->getConnection()->prepare($sql);
$str_sql->execute();
$row = $str_sql->fetchAll();
if (!empty($row)) {
$filedata2 = explode('*', $row[0]['xyz']);
}
$xmlstore .= "</categories>";
$xmlstore .= "<dataset seriesName='Wastage'>";
foreach ($filedata1 as $val2) {
$xmlstore .= "<set value='" . round($val2) . "' />";
}
$xmlstore .= "</dataset>";
$xmlstore .= "<dataset seriesName='Reporting Rate'>";
foreach ($filedata2 as $val2) {
$xmlstore .= "<set value='" . round($val2) . "' />";
}
$xmlstore .= "</dataset>";
$obj_product = new Model_ItemPackSizes();
$prod_result = $obj_product->getProductById($products[0]);
$xmlstore .= "<trendlines>\n <line startvalue='" . $prod_result->getWastageRateAllowed() . "' color='EE2000' displayvalue='Wastage Allowed:" . $prod_result->getWastageRateAllowed() . "%' valueonright='1' />\n </trendlines>";
$xmlstore .= "</chart>";
$xmlstore_array[] = $xmlstore;
}
$cache->save($xmlstore_array, $reportedwastages);
}
return $xmlstore_array;
}
public function wastagesRate()
{
$obj_product = new Model_ItemPackSizes();
$prod_result = $obj_product->getProductById($this->form_values['item']);
$allowed = $prod_result->getWastageRateAllowed() / 100 * 80;
$str_sql = "SELECT * FROM (SELECT\n\tlocations.location_name,\n\tCOALESCE (A.wastagePer, NULL, 0) AS wastagePer\n FROM\n\t(\n\t\tSELECT\n\tROUND(\n\t\t(\n\t\t\tsum(warehouses_data.wastages) / (\n\t\t\t\tsum(\n\t\t\t\t\twarehouses_data.issue_balance\n\t\t\t\t) + sum(warehouses_data.wastages)\n\t\t\t)\n\t\t) * 100,\n\t\t1\n\t) AS wastagePer,\n\twarehouses.location_id\nFROM\n\twarehouses\nINNER JOIN warehouses_data ON warehouses.pk_id = warehouses_data.warehouse_id\nINNER JOIN stakeholders ON warehouses.stakeholder_office_id = stakeholders.pk_id\nWHERE\n\twarehouses.stakeholder_id = 1\nAND warehouses_data.reporting_start_date = '" . $this->form_values[' date '] . "-01'\nAND warehouses_data.issue_balance IS NOT NULL\nAND warehouses_data.item_pack_size_id = " . $this->form_values['item'] . "\nAND stakeholders.pk_id = 6\nAND warehouses. STATUS = 1\nGROUP BY\n\twarehouses.location_id\nUNION\nSELECT\n\tROUND(\n\t\t(\n\t\t\tsum(hf_data_master.wastages) / (\n\t\t\t\tsum(\n\t\t\t\t\thf_data_master.issue_balance\n\t\t\t\t) + sum(hf_data_master.wastages)\n\t\t\t)\n\t\t) * 100,\n\t\t1\n\t) AS wastagePer,\n\twarehouses.location_id\nFROM\n\twarehouses\nINNER JOIN hf_data_master ON warehouses.pk_id = hf_data_master.warehouse_id\nINNER JOIN stakeholders ON warehouses.stakeholder_office_id = stakeholders.pk_id\nWHERE\n\twarehouses.stakeholder_id = 1\nAND hf_data_master.reporting_start_date = '" . $this->form_values[' date '] . "-01'\nAND hf_data_master.issue_balance IS NOT NULL\nAND hf_data_master.item_pack_size_id = " . $this->form_values['item'] . "\nAND stakeholders.pk_id = 6\nAND warehouses. STATUS = 1\nGROUP BY\n\twarehouses.location_id\n\t) A\nRIGHT JOIN locations ON locations.pk_id = A.location_id\nWHERE\n\tlocations.geo_level_id = 6\nAND locations.province_id = " . $this->form_values['prov_id'] . "\nAND locations.district_id = " . $this->form_values['dist_id'] . ") AS A\nWHERE A.wastagePer > {$allowed}";
$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'] = $this->form_values['dist_id'];
$district_name = $this->view->location_name = $locations->getLocationName();
$xmlstore = "<chart exportEnabled='1' labelDisplay='rotate' slantLabels='1' yAxisMaxValue='100' exportAction='Download' caption= 'Wastages Camparison of {$item}(Doses) in {$district_name} UCs(" . date('M Y', strtotime($this->form_values['date'])) . ")' exportFileName='UCs wise Wastages Comparison " . date('Y-m-d H:i:s') . "' yAxisName='Reporting Rate' numberSuffix='%' showValues='1' formatNumberScale='0' theme='fint'>";
foreach ($result as $row) {
$xmlstore .= "<set label='{$row['location_name']}' value='{$row['wastagePer']}' />";
}
$xmlstore .= "<trendlines>\n <line startvalue='" . $prod_result->getWastageRateAllowed() . "' color='EE2000' displayvalue='Allowed' valueonright='1' />\n </trendlines>";
$xmlstore .= "</chart>";
return $xmlstore;
}
