Ejemplo n.º 1
0
 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;
 }
Ejemplo n.º 2
0
 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;
 }
Ejemplo n.º 3
0
 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;
 }
Ejemplo n.º 4
0
 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 inventoryManagementReportingAction()
 {
     $this->_helper->layout->setLayout('reports');
     $this->view->report_id = 'SNONREPDIST';
     $this->view->report_title = 'Inventory Management Reporting Report';
     $this->view->actionpage = 'inventory-management-reporting';
     $this->view->parameters = 'TS01IP';
     $this->view->parameter_width = '100%';
     $item_pack_sizes = new Model_ItemPackSizes();
     $locations = new Model_Locations();
     //FOR BCG ITEM
     $item_pack_sizes->form_values = 6;
     $items = $item_pack_sizes->getItemForConsumptionReport();
     $this->view->items = $items;
     //For locations Combo
     $lct = $locations->conusmptionReportLocations();
     $this->view->location = $lct;
     if (!empty($this->_request->from_year_sel) && !empty($this->_request->from_month_sel)) {
         $this->view->from_year_sel = $from_year = $this->_request->from_year_sel;
         $this->view->from_month_sel = $from_month = $this->_request->from_month_sel;
     } else {
         $from_year = date("Y");
         $this->view->from_year_sel = $from_year;
         $this->view->from_month_sel = $from_month = date("m", strtotime("-4 month"));
     }
     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("-2 month"));
         } else {
             $month = date("m", strtotime("-2 month"));
         }
         $this->view->year_sel = $year;
         $this->view->month_sel = $month;
     }
     if (isset($this->_request->province) && !empty($this->_request->province)) {
         $this->view->prov_sel = $province = $this->_request->province;
     } else {
         $this->view->prov_sel = $province = 2;
     }
     if (isset($this->_request->district) && !empty($this->_request->district)) {
         $this->view->district_id = $district_id = $this->_request->district;
     } else {
         $this->view->district_id = $district_id = 30;
     }
     if (isset($this->_request->tehsil) && !empty($this->_request->tehsil)) {
         $this->view->tehsil = $this->_request->tehsil;
     } else {
         $this->view->tehsil = 1;
     }
     if (isset($this->_request->wh_type) && !empty($this->_request->wh_type)) {
         $this->view->wh_type = $wh_type = $this->_request->wh_type;
     } else {
         $this->view->wh_type = $wh_type = 4;
     }
     if ($this->_request->wh_type == 4) {
         $locations->form_values['pk_id'] = $this->_request->district;
         $this->view->loc_name = "District:" . ' ' . $locations->getLocationName();
     } elseif ($this->_request->wh_type == 5) {
         $locations->form_values['pk_id'] = $this->_request->tehsil;
         $this->view->loc_name = "Tehsil:" . ' ' . $locations->getLocationName();
     } else {
         $this->view->loc_name = "District:" . ' ' . 'Badin';
     }
     $locations->form_values['geo_level_id'] = '4';
     $locations->form_values['province_id'] = $province;
     $district = $locations->getLocationsByLevelByProvinceConsumption();
     $this->view->district = $district;
     $this->view->prov_sel = $province;
     $locations->form_values['dist_id'] = $district_id;
     $locations->form_values['year'] = $year;
     $distrctName = $locations->getLocationForReport();
     $res = $locations->getLocationsForConsumptionReport();
     $this->view->result = $res;
     //FOR Excel File Name
     $reportingDate = $year . '-' . $month . '-01';
     $fileName = 'BCG-Vaccine-Coverage' . $distrctName . '_for_' . date('M-Y', strtotime($reportingDate));
     $this->view->file_name = $fileName;
 }