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