Ejemplo n.º 1
0
 public function searchProcurementsAction()
 {
     $form = new Form_ProcurementFilters();
     $shipments = new Model_Shipments();
     if ($this->_request->isPost()) {
         $res = $this->_request->getPost();
         $shipments->form_values = $res;
         $form->from_warehouse_id->setValue($res['from_warehouse_id']);
         $form->from_date->setValue($res['from_date']);
         $form->to_date->setValue($res['to_date']);
         $form->item_pack_size_id->setValue($res['item_pack_size_id']);
         $form->status->setValue($res['status']);
     }
     $arr_data = $shipments->getProcurements();
     $this->view->arr_data = $arr_data;
     $this->view->form = $form;
 }
Ejemplo n.º 2
0
 public function getSufficientProductReport()
 {
     $form_values = $this->form_values;
     $role_id = $this->_identity->getRoleId();
     $today_date = new DateTime();
     $last_year = $today_date->modify("-1 year");
     $from_date = $last_year->format('Y-m-d');
     $to_date = date("Y-m-d");
     //$from_date = "2014-01-01";
     //$to_date = date("Y-m-d");
     $shipment = new Model_Shipments();
     $min_date = $shipment->getMinDate($to_date);
     $max_date = $shipment->getMaxDate($to_date);
     if ($role_id == 2 || $role_id == 22) {
         $warehouse = $form_values['warehouse'];
     } else {
         $warehouse = $this->_identity->getWarehouseId();
     }
     $str_qry = "SELECT\n\tA.item_name,\n\tB.soh,\n        A.amc,\n        A.item_pack_size_id,\n        A.warehouse_id\nFROM\n\t(\n\t\tSELECT\n\t\t\tIFNULL(\n\t\t\t\tSum(stock_detail.quantity),\n\t\t\t\t0\n\t\t\t) * item_pack_sizes.number_of_doses AS issue,\n\t\t\tstakeholder_item_pack_sizes.item_pack_size_id,\n\t\t\titem_pack_sizes.item_name,\n                        epi_amc.amc,\n                        epi_amc.warehouse_id\n\t\tFROM\n\t\t\tstock_detail\n\t\tINNER JOIN stock_batch_warehouses ON stock_detail.stock_batch_warehouse_id = stock_batch_warehouses.pk_id\n                INNER JOIN stock_batch ON stock_batch.pk_id = stock_batch_warehouses.stock_batch_id\n                INNER JOIN pack_info ON stock_batch.pack_info_id = pack_info.pk_id\n                INNER JOIN stakeholder_item_pack_sizes ON pack_info.stakeholder_item_pack_size_id = stakeholder_item_pack_sizes.pk_id\n                INNER JOIN item_pack_sizes ON stakeholder_item_pack_sizes.item_pack_size_id = item_pack_sizes.pk_id\n\t\tINNER JOIN stock_master ON stock_detail.stock_master_id = stock_master.pk_id\n\n                INNER JOIN epi_amc ON epi_amc.item_id = item_pack_sizes.pk_id\n\t\tWHERE\n\t\t\tstock_master.transaction_type_id = 2\n\t\tAND stock_master.from_warehouse_id = {$warehouse}\n\t\tAND DATE_FORMAT(\n\t\t\tstock_master.transaction_date,\n\t\t\t'%Y-%m-%d'\n\t\t) BETWEEN '{$from_date}'\n\t\tAND '{$to_date}' AND\n                item_pack_sizes.multiplier = 1 AND epi_amc.warehouse_id = {$warehouse}\n\t\tGROUP BY\n\t\t\tstakeholder_item_pack_sizes.item_pack_size_id\n                ORDER BY item_pack_sizes.list_rank\n\t) A\nINNER JOIN (\n\tSELECT\n\t\tIFNULL(\n\t\t\tSum(stock_detail.quantity),\n\t\t\t0\n\t\t) * item_pack_sizes.number_of_doses AS soh,\n\t\tstakeholder_item_pack_sizes.item_pack_size_id,\n\t\titem_pack_sizes.item_name\n\tFROM\n\t\tstock_detail\n\tINNER JOIN stock_batch_warehouses ON stock_detail.stock_batch_warehouse_id = stock_batch_warehouses.pk_id\n        INNER JOIN stock_batch ON stock_batch.pk_id = stock_batch_warehouses.stock_batch_id\n        INNER JOIN pack_info ON stock_batch.pack_info_id = pack_info.pk_id\n        INNER JOIN stakeholder_item_pack_sizes ON pack_info.stakeholder_item_pack_size_id = stakeholder_item_pack_sizes.pk_id\n        INNER JOIN item_pack_sizes ON stakeholder_item_pack_sizes.item_pack_size_id = item_pack_sizes.pk_id\n\tINNER JOIN stock_master ON stock_detail.stock_master_id = stock_master.pk_id\n\n\tWHERE\n\t\tstock_batch_warehouses.warehouse_id = {$warehouse}\n\tGROUP BY\n\t\tstakeholder_item_pack_sizes.item_pack_size_id\n) B ON A.item_pack_size_id = B.item_pack_size_id";
     /* INNER JOIN
        (SELECT
        DATE_FORMAT(
        shipments.shipment_date,
        '%M, %Y'
        ) eta,
        Sum(
        shipments.shipment_quantity
        ) AS quantity,
        shipments.item_pack_size_id,
        item_pack_sizes.item_name,
        stakeholder_activities.activity
        FROM
        shipments
        INNER JOIN item_pack_sizes ON shipments.item_pack_size_id = item_pack_sizes.pk_id
        INNER JOIN stakeholder_activities ON shipments.stakeholder_activity_id = stakeholder_activities.pk_id
        GROUP BY
        shipments.item_pack_size_id,
        DATE_FORMAT(
        shipments.shipment_date,
        '%Y-%m'
        ),
        shipments.stakeholder_activity_id) C
        ON C.item_pack_size_id = B.item_pack_size_id WHERE C.eta > '$to_date'"; */
     //echo $str_qry;
     $this->_em = Zend_Registry::get('doctrine');
     $row = $this->_em->getConnection()->prepare($str_qry);
     $row->execute();
     return $row->fetchAll();
 }