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; }