public function getProcurements() { $str_sql = $this->_em->createQueryBuilder()->select("DISTINCT s.referenceNumber, s.pkId, warehouse.warehouseName, " . "s.shipmentDate,s.shipmentQuantity as quantity,sh.status,ips.itemName,sa.activity")->from("ShipmentHistory", "sh")->join("sh.shipment", "s")->join("s.itemPackSize", "ips")->join("s.stakeholderActivity", "sa")->join("s.fundingSource", 'warehouse')->join("s.warehouse", 'w')->andWhere("w.pkId = " . $this->_identity->getWarehouseId()); if (!empty($this->form_values['from_date']) && !empty($this->form_values['to_date'])) { $str_sql->andWhere("DATE_FORMAT(s.shipmentDate,'%Y-%m-%d') BETWEEN '" . App_Controller_Functions::dateToDbFormat($this->form_values['from_date']) . "' AND '" . App_Controller_Functions::dateToDbFormat($this->form_values['to_date']) . "'"); } else { $date_from = date('Y-m' . '-01'); $date_to = date('Y-m-d'); $str_sql->andWhere("DATE_FORMAT(s.shipmentDate,'%Y-%m-%d') BETWEEN '" . $date_from . "' AND '" . $date_to . "'"); } if (!empty($this->form_values['item_pack_size_id'])) { $str_sql->andWhere("ips.pkId = '" . $this->form_values['item_pack_size_id'] . "'"); } if (!empty($this->form_values['from_warehouse_id'])) { $str_sql->andWhere("warehouse.pkId = '" . $this->form_values['from_warehouse_id'] . "'"); } if (!empty($this->form_values['status'])) { $str_sql->andWhere("sh.status = '" . $this->form_values['status'] . "'"); } //else { // $str_sql->andWhere("sh.status = 'Received' "); // } $str_sql->orderBy("s.pkId", "DESC"); $row = $str_sql->getQuery()->getResult(); return $row; }
public function getStartingDate() { $str_sql = $this->_em->createQueryBuilder()->select("w.startingOn as starting_date")->from("Warehouses", "w")->where("w.pkId = " . $this->form_values['wh_id']); //echo $str_sql->getQuery()->getSql(); // ->andWhere("wd.issueBalance > 0"); $row = $str_sql->getQuery()->getResult(); return App_Controller_Functions::dateToDbFormat($row[0]['starting_date']); }
public function addLog() { $data = $this->form_values; // App_Controller_Functions::pr($data); $name = $data['name']; $father_name = $data['father_name']; $age = $data['age']; $contact = $data['contact']; $address = $data['address']; $district = $data['district']; $uc = $data['uc']; $item_id = $data['item_id']; $dose_no = $data['dose_no']; $vaccination_date = $data['vaccination_date']; $reffers_to = $data['reffers_to']; $remarks = $data['remarks']; $user_id = $this->_user_id; $warehouse_id = $this->_identity->getWarehouseId(); $date = date("Y-m-d h:i:s"); foreach ($district as $key => $val) { if ($val != '' && $val >= 0) { $log_book = new LogBook(); $log_book->setName($name[$key]); $log_book->setFatherName($father_name[$key]); $log_book->setAge($age[$key]); $log_book->setContact($contact[$key]); $log_book->setAddress($address[$key]); $districtId = $this->_em->getRepository('Locations')->find($district[$key]); $log_book->setDistrictId($districtId); $ucId = $this->_em->getRepository('Locations')->find($uc[$key]); $log_book->setUnionCouncilId($ucId); $log_book->setVaccinationDate(new \DateTime(App_Controller_Functions::dateToDbFormat($vaccination_date[$key]))); $log_book->setRefferTo($reffers_to[$key]); $log_book->setRemarks($remarks[$key]); // $warehouse = $this->_em->getRepository('Warehouses')->find($warehouse_id); $log_book->setWarehouse($warehouse); $log_book->setCreatedDate(new \DateTime(App_Controller_Functions::dateToDbFormat($date))); $log_book->setCreatedBy($user_id); $this->_em->persist($log_book); $this->_em->flush(); $log_book_id = $log_book->getPkId(); foreach ($item_id as $key => $val) { $log_book_item_doses = new LogBookItemDoses(); $logBook = $this->_em->getRepository('LogBook')->find($log_book_id); $log_book_item_doses->setLogBookId($logBook); $itemPack = $this->_em->getRepository('ItemPackSizes')->find($val); $log_book_item_doses->setItemPackSize($itemPack); $log_book_item_doses->setDoses($dose_no[$key]); $this->_em->persist($log_book_item_doses); $this->_em->flush(); } } } }
public function addReportDatesAction() { $form = new Form_Iadmin_UpdateCluster(); if ($this->_request->isPost()) { $form_values = $this->_request->getPost(); if ($form_values['search'] == 'search') { $warehouses = new Model_Warehouses(); $warehouses->form_values = $form_values; $form->province->setValue($form_values['province']); $form->province_hidden->setValue($form_values['province']); $form->district_hidden->setValue($form_values['district']); $form->user_hidden->setValue($form_values['user']); $warehouse_data = $warehouses->getAllWarehousesReportDate(); $this->view->data = $warehouse_data; } else { $form_values_submit = $this->_request->getPost(); if ($form_values_submit['check'] != "") { foreach ($form_values_submit['check'] as $warehouse_id) { $warehouses_db = $this->_em->getRepository('Warehouses')->find($warehouse_id); if ($form_values_submit['from_edit'] != "") { $warehouses_db->setFromEdit(new \DateTime(App_Controller_Functions::dateToDbFormat($form_values_submit['from_edit']))); } if ($form_values_submit['starting_on'] != "") { $warehouses_db->setStartingOn(new \DateTime(App_Controller_Functions::dateToDbFormat($form_values_submit['starting_on']))); } if ($form_values_submit['working_uptil'] != "") { $warehouses_db->setWorkingUptill(new \DateTime(App_Controller_Functions::dateToDbFormat($form_values_submit['working_uptil']))); } } $created_by = $this->_em->find('Users', $this->_user_id); $warehouses_db->setCreatedBy($created_by); $warehouses_db->setCreatedDate(App_Tools_Time::now()); $warehouses_db->setModifiedBy($created_by); $warehouses_db->setModifiedDate(App_Tools_Time::now()); $this->_em->persist($warehouses_db); $this->_em->flush(); } } } $this->view->form = $form; }
public function addCampaign() { $campaign = new Campaigns(); $campaign->setCampaignName($this->form_values['campaign_name']); $campaign->setDateFrom(new \DateTime(App_Controller_Functions::dateToDbFormat($this->form_values['date_from']))); $campaign->setDateTo(new \DateTime(App_Controller_Functions::dateToDbFormat($this->form_values['date_to']))); $campaign->setCatchUpDays($this->form_values['catch_up_days']); $campaign_type_id = $this->_em->find('CampaignTypes', $this->form_values['campaign_type_id']); $campaign->setCampaignType($campaign_type_id); $created_by = $this->_em->find('Users', $this->_user_id); $campaign->setIsClosed('0'); $campaign->setCreatedBy($created_by); $campaign->setCreatedDate(new \DateTime()); $campaign->setModifiedBy($created_by); $campaign->setModifiedDate(new \DateTime()); $this->_em->persist($campaign); $this->_em->flush(); $last_id = $campaign->getPkId(); $campaign_id = $this->_em->find('Campaigns', $last_id); foreach ($this->form_values['item_id'] as $item_id) { $campaign_ips = new CampaignItemPackSizes(); $campaign_ips->setCampaign($campaign_id); $item_id_ips = $this->_em->find('ItemPackSizes', $item_id); $campaign_ips->setItemPackSize($item_id_ips); $this->_em->persist($campaign_ips); } $this->_em->flush(); //$district_ids = explode(',', $this->form_values['district_ids']); //App_Controller_Functions::pr($this->form_values['districts']); foreach ($this->form_values['districts'] as $district_id) { $campaign_d = new CampaignDistricts(); $campaign_d->setCampaign($campaign_id); $district_location_id = $this->_em->find('Locations', $district_id); $campaign_d->setDistrict($district_location_id); $this->_em->persist($campaign_d); } $this->_em->flush(); return $last_id; }
public function getLogBook() { if (!empty($this->form_values['entry_type'])) { if ($this->form_values['entry_type'] == "1") { // My Entries $where[] = "log_book.created_by = '" . $this->_user_id . "'"; } if ($this->form_values['entry_type'] == "2") { // Referrals $where[] = "log_book.created_by <> '" . $this->_user_id . "'"; } } else { // Default is My Entries $where[] = "log_book.created_by = '" . $this->_user_id . "'"; } if (!empty($this->form_values['district'])) { $where[] = "log_book.district_id = '" . $this->form_values['district'] . "'"; } else { $where[] = "log_book.district_id = 0"; } if (!empty($this->form_values['tehsil'])) { $where[] = "Uc.parent_id = '" . $this->form_values['tehsil'] . "'"; } if (!empty($this->form_values['uc'])) { $where[] = "log_book.union_council_id = '" . $this->form_values['uc'] . "'"; } if (!empty($this->form_values['vaccination_date_from']) && !empty($this->form_values['vaccination_date_to'])) { $where[] = "DATE_FORMAT(log_book.vaccination_date,'%Y-%m-%d') BETWEEN '" . App_Controller_Functions::dateToDbFormat($this->form_values['vaccination_date_from']) . "' AND '" . App_Controller_Functions::dateToDbFormat($this->form_values['vaccination_date_to']) . "' "; } else { $date_from = date('Y-m' . '-01'); $date_to = date('Y-m-d'); $where[] = "DATE_FORMAT(log_book.vaccination_date,'%Y-%m-%d') BETWEEN '" . $date_from . "' AND '" . $date_to . "'"; } if (is_array($where)) { $where_s = implode(" AND ", $where); } $str_qry = "SELECT\n log_book.union_council_id,\n Uc.location_name AS Uc,\n log_book.pk_id,\n log_book.`name`,\n log_book.father_name,\n log_book.gender,\n log_book.age,\n log_book.contact,\n log_book.address,\n log_book.district_id,\n log_book.vaccination_date,\n log_book.refer_to_warehouse_id,\n log_book.remarks,\n log_book.warehouse_id,\n log_book.created_by,\n log_book.created_date,\n log_book.modified_date,\n log_book.reporting_start_date,\n District.location_name AS District,\n Tehsil.location_name AS Tehsil,\n warehouses.warehouse_name AS RefFromEPI,\n ref_from_uc.location_name AS RefFromUc,\n ref_from_dist.location_name AS RefFromDist\n FROM\n log_book\n LEFT JOIN locations AS Uc ON Uc.pk_id = log_book.union_council_id\n INNER JOIN locations AS District ON log_book.district_id = District.pk_id\n INNER JOIN locations AS Tehsil ON Uc.parent_id = Tehsil.pk_id\n INNER JOIN warehouses ON log_book.warehouse_id = warehouses.pk_id\n INNER JOIN locations AS ref_from_uc ON ref_from_uc.pk_id = warehouses.location_id\n INNER JOIN locations AS ref_from_dist ON ref_from_dist.pk_id = warehouses.district_id WHERE \n " . "{$where_s}"; $this->_em = Zend_Registry::get('doctrine'); $row = $row = $this->_em->getConnection()->prepare($str_qry); $row->execute(); return $row->fetchAll(); }
public function updateTransport() { $form_values = $this->form_values; $user_id = $this->_em->getRepository('Users')->find($this->_user_id); $cold_chain = $this->_em->getRepository('ColdChain')->find($form_values['ccm_id']); $stakeholder = $this->_em->getRepository('Stakeholders')->find($form_values['source_id']); $cold_chain->setSource($stakeholder); $asset_type = $this->_em->getRepository('CcmAssetTypes')->find(Model_CcmAssetTypes::TRANSPORT); $cold_chain->setCcmAssetType($asset_type); $model_id = $this->_em->getRepository('CcmModels')->find($form_values['ccm_model_id']); $cold_chain->setCcmModel($model_id); $cold_chain->setManufactureYear(new \DateTime(App_Controller_Functions::dateToDbFormat($form_values['manufacture_year']))); $cold_chain->setModifiedBy($user_id); $cold_chain->setModifiedDate(App_Tools_Time::now()); $this->_em->persist($cold_chain); $this->_em->flush(); $vechicles = $this->_em->getRepository('CcmVehicles')->findBy(array('ccm' => $form_values['ccm_id'])); $ccm_vehicles = $this->_em->getRepository('CcmVehicles')->find($vechicles[0]->getPkId()); $ccm_asset_sub_type = $this->_em->getRepository('CcmAssetTypes')->find($form_values['ccm_asset_sub_type_id']); $ccm_vehicles->setCcmAssetSubType($ccm_asset_sub_type); $ccm_vehicles->setRegistrationNo($form_values['registration_no']); $ccm_vehicles->setUsedForEpi($form_values['used_for_epi']); if ($form_values['fuel_type_id']) { $fuel_type_id = $this->_em->getRepository('ListDetail')->find($form_values['fuel_type_id']); $ccm_vehicles->setFuelType($fuel_type_id); } $ccm_vehicles->setComments($form_values['comments']); $ccm_vehicles->setModifiedBy($user_id); $ccm_vehicles->setModifiedDate(App_Tools_Time::now()); $this->_em->persist($ccm_vehicles); $this->_em->flush(); }
public function multipleAdjustmentAction() { $stock_master = new Model_StockMaster(); $stock_batch = new Model_StockBatch(); $stock_detail = new Model_StockDetail(); $warehouse_data = new Model_WarehousesData(); $form = new Form_MultipleAdjustment(); $form_values = array(); $temp = array(); $arr_data = array('transaction_number' => "", 'stock_id' => "", 'transaction_date' => date("d/m/Y"), 'warehouse_name' => "", 'success' => $this->_request->success); $form_values['transaction_type_id'] = 2; $form_values['adjustment_type'] = 2; $stock_id = ""; $master_id = ""; if (!empty($this->_request->hdn_stock_id)) { $stock_id = $this->_request->hdn_stock_id; } if (!empty($this->_request->hdn_master_id)) { $master_id = $this->_request->hdn_master_id; } if (!empty($this->_request->warehouse)) { $warehouse_id = $this->_request->warehouse; } if ($this->_request->isPost()) { $em = Zend_Registry::get('doctrine'); $em->getConnection()->beginTransaction(); try { if (!empty($master_id)) { //Start update issue period $array = $this->_request->getParams(); $stock_master->updateStockPeriod($master_id, $array); //End update issue period $transaction_number = $stock_master->updateStockMasterTemp($master_id, $this->_request->comments); $stock_detail->updateStockDetailTemp($master_id); //Save Data in warehouse_data table $warehouse_data->addReport($master_id, 2); /* * Auto Receive for 6th level * $stock_master->autoReceiveData($master_id); */ $this->view->msg = 'Stock has been issued successfully. Your voucher number is '; $this->view->voucher = $transaction_number; $this->view->master_id = $master_id; $em->getConnection()->commit(); } elseif ($form->isValid($this->_request->getPost())) { $editissue = $this->_request->getPost('issueedit'); if ($editissue == "Yes") { $d_id = $this->_request->getPost('detailid'); $obj_stock_detail = $em->getRepository("StockDetail")->find($d_id); $old_batch_id = $obj_stock_detail->getStockBatch()->getPkId(); $data = $form->getValues(); $obj_stock_master = $obj_stock_detail->getStockMaster(); $master_update = false; $arr_date = explode("/", $data['transaction_date']); if (intval($arr_date[1]) . "-" . intval($arr_date[2]) != intval($obj_stock_detail->getStockMaster()->getTransactionDate()->format("m")) . "-" . intval($obj_stock_detail->getStockMaster()->getTransactionDate()->format("Y"))) { $trans = $stock_master->getTransactionNumber(2, $data['transaction_date']); $obj_stock_master->setTransactionNumber($trans['trans_no']); $obj_stock_master->setTransactionCounter($trans['id']); $master_update = true; } $obj_stock_master->setTransactionDate(new \DateTime(App_Controller_Functions::dateToDbFormat($data['transaction_date']))); if (!empty($warehouse_id)) { $to_wh = $em->getRepository("Warehouses")->find($warehouse_id); $obj_stock_master->setToWarehouse($to_wh); $master_update = true; } if ($master_update) { $em->persist($obj_stock_master); $em->flush(); } $qunty = str_replace(",", "", $data['quantity']); $obj_stock_detail->setQuantity("-" . $qunty); $vvms = $this->_em->getRepository("VvmStages")->find($data['vvm_stage']); $obj_stock_detail->setVvmStage($vvms); $stock_b = $em->getRepository("StockBatch")->find($data['number']); $obj_stock_detail->setStockBatch($stock_b); if (!empty($data['item_unit_id'])) { $item_unit = $em->getRepository("ItemUnits")->find($data['item_unit_id']); $obj_stock_detail->setItemUnit($item_unit); } $em->persist($obj_stock_detail); $em->flush(); list($dd, $mm, $yy) = explode("/", $data['transaction_date']); $item_id = $data['item_id']; $wh_id = $this->_identity->getWarehouseId(); $user_id = $this->_userid; $query = "SELECT AdjustQty(" . $data['number'] . "," . $wh_id . ") FROM DUAL"; $str_sql = $em->getConnection()->prepare($query); $str_sql->execute(); $query = "SELECT AdjustQty(" . $old_batch_id . "," . $wh_id . ") FROM DUAL"; $str_sql = $em->getConnection()->prepare($query); $str_sql->execute(); $query = "SELECT REPUpdateData({$mm},{$yy},{$item_id},{$wh_id},{$user_id}) FROM DUAL"; $str_sql = $em->getConnection()->prepare($query); $str_sql->execute(); } else { $temp = $form->getValues(); $data = array_merge($temp, $form_values); $data['warehouse'] = $warehouse_id; if (empty($this->_request->transaction_number)) { $stock_id = $stock_master->addStockMaster($data); } if (isset($data['quantity']) && !empty($data['quantity'])) { $qty = str_replace(",", "", $data['quantity']); } if (isset($data['available_quantity']) && !empty($data['available_quantity'])) { $ava_qty = str_replace(",", "", $data['available_quantity']); } if ((int) $qty > (int) $ava_qty || (int) $qty == (int) $ava_qty) { $data['quantity'] = $ava_qty; $autorun = true; } list($batch_id, $priority) = explode("|", $this->_request->number); $form_values['item_unit_id'] = $this->_request->item_unit_id; $form_values['stock_master_id'] = $stock_id; $form_values['stock_batch_id'] = $batch_id; $data = array_merge($temp, $form_values); $detail_id = $stock_detail->addStockDetail($data); $stock_batch->adjustQuantityByWarehouse($batch_id); if ($autorun == true) { $stock_batch->autoRunningLEFOBatch($form->getValue('item_id')); $stock_batch->form_values['pk_id'] = $batch_id; $stock_batch->form_values['status'] = Model_StockBatch::FINISHED; $stock_batch->changeStatus(); } /* $placement = new Model_Placements(); $placement->form_values['stock_batch_id'] = $batch_id; $placement->form_values['quantity'] = $data['quantity']; $placement->form_values['placement_location_id'] = $data['pick_from']; $placement->form_values['stock_detail_id'] = $detail_id; $placement->addPlacement(); */ } $em->getConnection()->commit(); $this->redirect("/stock/multiple-adjustment"); // For Data Show $temp_stock_list = $stock_master->getTempStocksList(); if ($temp_stock_list != false) { // $form->transaction_number->setValue($temp_stock_list[0]['transaction_number']); $form->transaction_date->setValue(date("d/m/Y h:i A", strtotime($temp_stock_list[0]['transaction_date']))); $form->warehouse_name->setValue($temp_stock_list[0]['to_warehouse']); $form->transaction_reference->setValue($temp_stock_list[0]['transaction_reference']); $form->hdn_to_warehouse_id->setValue($temp_stock_list[0]['to_warehouse_id']); $form->product->setValue($temp_stock_list[0]['to_warehouse_id']); $arr_data['warehouse_name'] = $temp_stock_list[0]['to_warehouse']; // $arr_data['warehouse_name'] = $temp_stock_list[0]['to_warehouse']; $form->activity_id->setValue($temp_stock_list[0]['activity_id']); $arr_data['tempstocks'] = $temp_stock_list; $form->makeFieldReadonly(); } else { $form->transaction_date->setValue(date("d/m/Y h:i A")); } } } catch (Exception $e) { $em->getConnection()->rollback(); $em->close(); App_FileLogger::info($e); switch ($e->getMessage()) { case 'PLCD_QTY_GREATER_THAN_ISSUE_QTY': $this->view->status = false; $this->view->msg = "Issue quantity should not greater than placed quantity!"; break; } } $this->view->form = $form; $this->view->arr_data = $arr_data; if ($this->_request->type == 's') { $this->redirect("/stock/issue-search"); } } $stock_master->form_values = $form_values; $temp_stock = $stock_master->getTempStock(); if ($temp_stock != false) { $arr_data = array_merge($arr_data, $temp_stock); } if (!empty($temp_stock['stock_id'])) { $form->hdn_stock_id->setValue($temp_stock['stock_id']); $form->hdn_master_id->setValue($temp_stock['stock_id']); } elseif (!empty($stockid)) { $form->hdn_stock_id->setValue($stock_id); $form->hdn_master_id->setValue($stock_id); } $this->view->form = $form; $base_url = Zend_Registry::get('baseurl'); switch ($this->_user_level) { case 1: case 2: case 3: $this->view->menu_type = 1; $this->view->inlineScript()->appendFile($base_url . '/js/all_level_combos.js'); break; case 4: case 5: case 6: $this->view->menu_type = 2; $this->view->inlineScript()->appendFile($base_url . '/js/level_combos.js'); break; } // Edit Issue Start if (!empty($this->_request->id)) { $detail_id = $this->_request->id; $issue = $this->_em->getRepository("StockDetail")->find($detail_id); $form->transaction_number->setValue($issue->getStockMaster()->getTransactionNumber()); $form->transaction_date->setValue($issue->getStockMaster()->getTransactionDate()->format("d/m/Y h:i A")); $form->warehouse_name->setValue($issue->getStockMaster()->getToWarehouse()->getWarehouseName()); $form->transaction_reference->setValue($issue->getStockMaster()->getTransactionReference()); //$form->activity_id->setValue($issue->getStockMaster()->getStakeholderActivity()->getPkId()); $arr_data['warehouse_name'] = $issue->getStockMaster()->getToWarehouse()->getWarehouseName(); $form->item_id->setValue($issue->getStockBatch()->getItemPackSize()->getPkId()); $form->fillBatchCombo($issue->getStockBatch()->getItemPackSize()->getPkId()); $form->number->setValue($issue->getStockBatch()->getPkId()); $form->vvm_stage->setValue($issue->getVvmStage()); $form->quantity->setValue($issue->getQuantity()); $av_qty = $issue->getStockBatch()->getQuantity() + ABS($issue->getQuantity()); $form->available_quantity->setValue($av_qty); $form->expiry_date->setValue($issue->getStockBatch()->getExpiryDate()->format("d/m/Y")); $this->view->issueedit = true; $this->view->detail_id = $this->_request->id; } // Edit Issue End $this->view->arr_data = $arr_data; $this->view->type = $this->_request->getParam("t", "issue"); $this->view->wh_id = $this->_identity->getWarehouseId(); $this->view->params = array("province" => $this->_identity->getProvinceId(), "district" => $this->_identity->getDistrictId()); $this->view->role = $this->_identity->getRoleId(); }
public function getAllIssueProductsByStakeholder() { $tran_date = $this->form_values['trans_date']; if (empty($tran_date)) { $tran_date = date("d/m/Y h:i:s A"); } $arr_data = array(); $str_sql = $this->_em->createQueryBuilder()->select('DISTINCT ips.pkId')->from('StockBatchWarehouses', 'sbw')->join("sbw.stockBatch", "sb")->join("sb.packInfo", "pi")->join("pi.stakeholderItemPackSize", "sip")->join("sip.itemPackSize", "ips")->where("sbw.warehouse = " . $this->_identity->getWarehouseId())->andWhere("DATE_FORMAT(sb.expiryDate,'%Y-%m-%d') > '" . App_Controller_Functions::dateToDbFormat($tran_date) . "'")->andWhere("sbw.quantity > 0")->orderBy("ips.listRank", "ASC"); //echo $str_sql->getQuery()->getSql(); $rows = $str_sql->getQuery()->getResult(); if (!empty($rows) && count($rows) > 0) { foreach ($rows as $row) { $item_ids[] = $row['pkId']; } $str_sql = $this->_em->createQueryBuilder()->select('DISTINCT ips.itemName as item_name, ips.pkId as item_pack_size_id')->from("ItemActivities", "ia")->join("ia.itemPackSize", "ips")->where("ips.itemCategory !=4")->andWhere("ia.stakeholderActivity = '" . $this->form_values['stakeholder_id'] . "'")->orderBy("ips.listRank", 'ASC'); $rows = $str_sql->getQuery()->getResult(); //echo $str_sql->getQuery()->getSql(); return $rows; } else { return false; } }
public function getShelfLifeMonths24($wh_id, $item_id, $time_period1) { $time_period = App_Controller_Functions::dateToDbFormat($time_period1); if (empty($wh_id)) { $wh_id = '159'; } $str_qry = "SELECT\n sum(stock_batch.quantity * item_pack_sizes.number_of_doses) AS Qty,\n Max(TIMESTAMPDIFF(MONTH, '{$time_period}', stock_batch.expiry_date)) as Max24months,\n Min(TIMESTAMPDIFF(MONTH, '{$time_period}', stock_batch.expiry_date)) as Min24months\n\n FROM\n stock_master\n INNER JOIN stock_detail ON stock_master.pk_id = stock_detail.stock_master_id\n INNER JOIN stock_batch ON stock_batch.pk_id = stock_detail.stock_batch_id\n INNER JOIN item_pack_sizes ON item_pack_sizes.pk_id = stock_batch.item_pack_size_id\n WHERE\n item_pack_sizes.pk_id = {$item_id} and\n stock_batch.warehouse_id = {$wh_id}\n AND stock_batch.expiry_date > CURDATE()\n and TIMESTAMPDIFF(MONTH, '{$time_period}', stock_batch.expiry_date) > 12\n "; $this->_em = Zend_Registry::get('doctrine'); $row = $row = $this->_em->getConnection()->prepare($str_qry); $row->execute(); return $row->fetchAll(); }
public function searchRefrigerator() { $form_values = $this->form_values; if (!empty($form_values['ccm_asset_sub_type_id'])) { $where[] = "cat.pkId = '" . $form_values['ccm_asset_sub_type_id'] . "'"; } if (!empty($form_values['source_id'])) { $where[] = "s.pkId = '" . $form_values['source_id'] . "'"; } if (!empty($form_values['ccm_status_list_id'])) { $where[] = "csl.pkId = '" . $form_values['ccm_status_list_id'] . "'"; } if (!empty($form_values['asset_id'])) { $where[] = "ccm.ccmAssetType = '" . $form_values['asset_id'] . "'"; } if (!empty($form_values['catalogue_id'])) { $where[] = "ccm.catalogueId = '" . $form_values['catalogue_id'] . "'"; } if (!empty($form_values['ccm_make_id'])) { $where[] = "ccmake.pkId = '" . $form_values['ccm_make_id'] . "'"; } if (!empty($form_values['ccm_model_id'])) { $where[] = "ccm.pkId = '" . $form_values['ccm_model_id'] . "'"; } if (!empty($form_values['serial_number'])) { $where[] = "cc.serialNumber = '" . $form_values['serial_number'] . "'"; } if (!empty($form_values['gross_capacity_from'])) { $where[] = "(ccm.grossCapacity20 >= '" . $form_values['gross_capacity_from'] . "' OR ccm.grossCapacity4 >= '" . $form_values['gross_capacity_from'] . "')"; } if (!empty($form_values['gross_capacity_to'])) { $where[] = "(ccm.grossCapacity20 <= '" . $form_values['gross_capacity_to'] . "' OR ccm.grossCapacity4 <= '" . $form_values['gross_capacity_to'] . "' )"; } if (!empty($form_values['working_since_from'])) { $where[] = "cc.workingSince >= '" . App_Controller_Functions::dateToDbFormat($form_values['working_since_from']) . "'"; } if (!empty($form_values['working_since_to'])) { $where[] = "cc.workingSince <= '" . App_Controller_Functions::dateToDbFormat($form_values['working_since_to']) . "'"; } if (!empty($form_values['cfc_free'])) { $where[] = "ccm.cfcFree = '" . $form_values['cfc_free'] . "'"; } if ($form_values['placed_at'] == 1 && !empty($form_values['warehouse'])) { $where[] = "w.pkId = '" . $form_values['warehouse'] . "'"; } if ($this->form_values['placed_at'] == 0) { $where[] = "w.pkId IS NULL "; } $where[] = "cp.pkId = '" . Model_CcmAssetTypes::REFRIGERATOR . "'"; // $where[] = "cc.createdBy = '" . $this->_user_id . "' "; if (is_array($where)) { $where_s = implode(" AND ", $where); } $str_sql = $this->_em->createQueryBuilder()->select("cc.pkId,cc.assetId,cc.quantity," . "ccm.ccmModelName,csl.ccmStatusListName," . "ccmake.ccmMakeName,cc.createdDate,cc.workingSince," . "d.locationName district, w.warehouseName facility," . "ccm.grossCapacity20, ccm.grossCapacity4," . "cat.assetTypeName")->from('ColdChain', 'cc')->leftjoin('cc.source', 's')->leftJoin('cc.ccmModel', 'ccm')->leftJoin('cc.ccmAssetType', 'cat')->leftJoin('cat.parent', 'cp')->leftJoin('ccm.ccmMake', 'ccmake')->leftJoin('cc.ccmStatusHistory', 'csh')->leftJoin('csh.ccmStatusList', 'csl'); if ($this->form_values['placed_at'] == 1) { $str_sql->join('cc.warehouse', 'w'); $str_sql->join('w.district', 'd'); } if ($this->form_values['placed_at'] == 0) { $str_sql->leftjoin('cc.warehouse', 'w'); $str_sql->leftjoin('w.district', 'd'); } $str_sql->where($where_s); // echo $str_sql->getQuery()->getSql(); $row = $str_sql->getQuery()->getResult(); if (!empty($row) && count($row) > 0) { return $row; } else { return false; } }
public function updateCampaignReadinessUcAction() { $this->_helper->layout->disableLayout(); $this->_helper->viewRenderer->setNoRender(TRUE); $form_values = $this->_request->getPost(); $campaign_readiness = $this->_em->find('CampaignReadinessUnionCouncil', $form_values['readiness_uc_id']); $campaign_readiness->setInaccessibleChildren($form_values['inaccessible_children']); $campaign_readiness->setNumberMobileTeams($form_values['no_of_mobile_teams']); $campaign_readiness->setUpecMeetingDate(new \DateTime(App_Controller_Functions::dateToDbFormat($form_values['date_upec_meeting']))); $campaign_readiness->setInaccessibleArea($form_values['inaccessible_area']); $campaign_readiness->setNumberFixedTeams($form_values['no_of_fixed_teams']); $campaign_readiness->setNumberTransitPoints($form_values['no_of_transist_points']); $campaign_readiness->setAicTrained($form_values['aics_trained']); $campaign_readiness->setNumberTeamsTrained($form_values['no_of_teams_trained']); $campaign_readiness->setMobilePopulationAreas($form_values['area_mobile_population']); $created_by = $this->_em->find('Users', $this->_user_id); $campaign_readiness->setCreatedBy($created_by); $campaign_readiness->setCreatedDate(App_Tools_Time::now()); $campaign_readiness->setModifiedBy($created_by); $campaign_readiness->setModifiedDate(App_Tools_Time::now()); $this->_em->persist($campaign_readiness); $this->_em->flush(); $this->_redirect("campaign/manage-campaigns/campaign-readiness-uc"); }
public function searchColdRooms() { if (!empty($this->form_values['ccm_asset_sub_type_id'])) { $where[] = "ccr.ccmAssetSubType = '" . $this->form_values['ccm_asset_sub_type_id'] . "'"; } if (!empty($this->form_values['ccm_asset_sub_type_id']) && $this->form_values['ccm_asset_sub_type_id'] == Model_CcmAssetTypes::SUBCOLDROOM && !empty($this->form_values['capacity_from']) && !empty($this->form_values['capacity_to'])) { $where[] = "ccm.grossCapacity4 Between '" . $this->form_values['capacity_from'] . "' AND '" . $this->form_values['capacity_to'] . "'"; } if (!empty($this->form_values['ccm_asset_sub_type_id']) && $this->form_values['ccm_asset_sub_type_id'] == Model_CcmAssetTypes::SUBFREEZERROOM && !empty($this->form_values['capacity_from']) && !empty($this->form_values['capacity_to'])) { $where[] = "ccm.grossCapacity20 Between '" . $this->form_values['capacity_from'] . "' AND '" . $this->form_values['capacity_to'] . "'"; } //Working status if (!empty($this->form_values['ccm_status_list_id'])) { $where[] = "csh.ccmStatusList = '" . $this->form_values['ccm_status_list_id'] . "'"; } //Source of Supply if (!empty($this->form_values['source_id'])) { $where[] = "cc.source = '" . $this->form_values['source_id'] . "'"; } // Number of Cooling System if (!empty($this->form_values['cooling_system'])) { $where[] = "ccr.coolingSystem = '" . $this->form_values['cooling_system'] . "'"; } //Asset Equipment Id if (!empty($this->form_values['asset_id'])) { $where[] = "cc.assetId = '" . $this->form_values['asset_id'] . "'"; } // year of supply from and to if (!empty($this->form_values['working_since_from']) && !empty($this->form_values['working_since_to'])) { $where[] = "cc.workingSince Between '" . App_Controller_Functions::dateToDbFormat($this->form_values['working_since_from']) . "' AND '" . App_Controller_Functions::dateToDbFormat($this->form_values['working_since_to']) . "' "; } if (!empty($this->form_values['make'])) { $where[] = "ccmake.pkId = '" . $this->form_values['make'] . "'"; } if (!empty($this->form_values['ccm_model_id'])) { $where[] = "ccm.pkId = '" . $this->form_values['ccm_model_id'] . "'"; } if ($this->form_values['placed_at'] == 1 && !empty($this->form_values['warehouse'])) { $where[] = "w.pkId = '" . $this->form_values['warehouse'] . "'"; } if ($this->form_values['placed_at'] == 0) { $where[] = "w.pkId IS NULL "; } // $where[] = "cp.pkId = '" . Model_CcmAssetTypes::COLDROOM . "' "; // $where[] = "cc.createdBy = '" . $this->_user_id . "' "; if (is_array($where)) { $where_s = implode(" AND ", $where); } $str_sql = $this->_em->createQueryBuilder()->select("cc.pkId,cc.assetId,cat.assetTypeName,ccm.ccmModelName,ccmake.ccmMakeName," . "cc.createdDate,csl.ccmStatusListName,w.warehouseName," . "d.locationName,ccm.grossCapacity20")->from('CcmColdRooms', 'ccr')->join('ccr.ccm', 'cc')->join('cc.ccmModel', 'ccm')->leftjoin('cc.ccmAssetType', 'cat')->leftjoin('cat.parent', 'cp')->leftjoin('cc.ccmStatusHistory', 'csh')->leftjoin('csh.ccmStatusList', 'csl')->leftjoin('ccm.ccmMake', 'ccmake'); if ($this->form_values['placed_at'] == 1) { $str_sql->join('cc.warehouse', 'w'); $str_sql->join('w.district', 'd'); } if ($this->form_values['placed_at'] == 0) { $str_sql->leftjoin('cc.warehouse', 'w'); $str_sql->leftjoin('w.district', 'd'); } $str_sql->where($where_s); // echo $str_sql->getQuery()->getSql(); $row = $str_sql->getQuery()->getResult(); if (!empty($row) && count($row) > 0) { return $row; } else { return false; } }
public function getDistinctIssueByVoucherNumber() { $str_sql = $this->_em->createQueryBuilder()->select("DISTINCT fa.voucherNumber, fa.pkId, warehouse.warehouseName,fa.referenceNumber, " . "fa.expectedArrivalDate, fa.quantity, SUM(fa.receivedQuantity) as receivedQuantity, fa.status")->from("PipelineConsignments", "fa")->join("fa.toWarehouse", 'warehouse')->andWhere("fa.fromWarehouse = " . $this->_identity->getWarehouseId()); if (!empty($this->form_values['from_date']) && !empty($this->form_values['to_date'])) { $str_sql->andWhere("DATE_FORMAT(fa.expectedArrivalDate,'%Y-%m-%d') BETWEEN '" . App_Controller_Functions::dateToDbFormat($this->form_values['from_date']) . "' AND '" . App_Controller_Functions::dateToDbFormat($this->form_values['to_date']) . "'"); } else { $date_from = date('Y-m' . '-01'); $date_to = date('Y-m-d'); $str_sql->andWhere("DATE_FORMAT(fa.expectedArrivalDate,'%Y-%m-%d') BETWEEN '" . $date_from . "' AND '" . $date_to . "'"); } if (!empty($this->form_values['item_pack_size_id'])) { $str_sql->andWhere("fa.itemPackSize = '" . $this->form_values['item_pack_size_id'] . "'"); } if (!empty($this->form_values['to_warehouse_id'])) { $str_sql->andWhere("fa.toWarehouse = '" . $this->form_values['to_warehouse_id'] . "'"); } if (!empty($this->form_values['status'])) { $str_sql->andWhere("fa.status = '" . $this->form_values['status'] . "'"); } else { $str_sql->andWhere("fa.status = 'Planned' "); } $str_sql->groupBy("fa.voucherNumber"); $str_sql->orderBy("fa.pkId", "DESC"); // echo $str_sql->getQuery()->getSql(); $row = $str_sql->getQuery()->getResult(); return $row; /* if (!empty($row) && count($row) > 0) { return $row; } else { return false; } */ }
public function ajaxAvailableIssueBatchQuantityAction() { $this->_helper->layout->disableLayout(); $stock_batch = new Model_StockBatch(); $stock_batch->form_values['pk_id'] = $this->_request->batch; $stock_batch->form_values['trans_date'] = App_Controller_Functions::dateToDbFormat($this->_request->tr_date); $batch_expiry = $stock_batch->getBatchAvailableBalanceExpiry(); $type = $this->_request->getParam('type', 0); if ($type === 'json') { $this->_helper->viewRenderer->setNoRender(TRUE); echo json_encode($batch_expiry); } else { $this->view->batch_expiry = $batch_expiry; } }
public function updateHfSessions($wh_id, $report_date, $fix_plan_sessions, $fix_actually_held_sessions, $outreach_plan_sessions, $outreach_actually_held_sessions) { $rep_date = explode('-', $report_date); $str_qry_hf = "SELECT hf_sessions.pk_id\n FROM\n hf_sessions\n where\n hf_sessions.warehouse_id ='" . $wh_id . "' \n and DATE_FORMAT(hf_sessions.reporting_start_date, '%Y-%m-%d') = '" . $report_date . "' "; $this->_em = Zend_Registry::get('doctrine'); $row_hf = $this->_em->getConnection()->prepare($str_qry_hf); $row_hf->execute(); $result_hf_edit = $row_hf->fetchAll(); $hf_session_id = $result_hf_edit[0]['pk_id']; if (!empty($hf_session_id)) { $hf_sessions = $this->_em->getRepository('HfSessions')->find($hf_session_id); } else { $hf_sessions = new HfSessions(); } $warehouse_status = $this->_em->getRepository('ListDetail')->find(Model_ListDetail::WAREHOUSE_STATUS_REPORTING); $hf_sessions->setWarehouseStatus($warehouse_status); $hf_sessions->setFixedPlannedSessions($fix_plan_sessions); $hf_sessions->setFixedActuallyHeldSessions($fix_actually_held_sessions); $hf_sessions->setOutreachPlannedSessions($outreach_plan_sessions); $hf_sessions->setOutreachActuallyHeldSessions($outreach_actually_held_sessions); $hf_sessions->setReportingStartDate(new \DateTime(App_Controller_Functions::dateToDbFormat($report_date))); $warehouse_id = $this->_em->getRepository('Warehouses')->find($wh_id); $hf_sessions->setWarehouse($warehouse_id); $user = $this->_em->getRepository('Users')->find($this->_user_id); $hf_sessions->setModifiedBy($user); $hf_sessions->setCreatedBy($user); $hf_sessions->setCreatedDate(App_Tools_Time::now()); $hf_sessions->setModifiedDate(App_Tools_Time::now()); $this->_em->persist($hf_sessions); $this->_em->flush(); }
public function activityLogSearch() { $wh_id = $this->_identity->getWarehouseId(); if (!empty($this->form_values['date_from']) && !empty($this->form_values['date_to'])) { $date_where = "and DATE_FORMAT(stock_master.created_date,'%Y-%m-%d') BETWEEN '" . App_Controller_Functions::dateToDbFormat($this->form_values['date_from']) . "' AND '" . App_Controller_Functions::dateToDbFormat($this->form_values['date_to']) . "'"; } else { $date_from = date('Y-m' . '-01'); $date_to = date('Y-m-d'); $date_where = "and DATE_FORMAT(stock_master.created_date,'%Y-%m-%d') BETWEEN '" . $date_from . "' AND '" . $date_to . "'"; } $querypro = "SELECT\n DISTINCT\n stock_master.pk_id,\n stock_master.transaction_date,\n stock_master.transaction_number,\n stock_master.transaction_type_id,\n stock_master.transaction_reference,\n ABS(stock_detail.quantity) as quantity,\n item_pack_sizes.item_name,\n stock_batch.number,\n item_units.item_unit_name,\n stock_batch.expiry_date,\n stock_master.created_date,\n from_warehouse.warehouse_name as from_warehouse_name,\n to_warehouse.warehouse_name as to_warehouse_name,\n stock_master.action_type,\n users.user_name\n FROM\n stock_master_history as stock_master\n INNER JOIN stock_detail_history as stock_detail ON stock_master.master_id = stock_detail.stock_master_id\n INNER 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 INNER JOIN item_units ON item_pack_sizes.item_unit_id = item_units.pk_id\n INNER JOIN warehouses AS `from_warehouse` ON stock_master.from_warehouse_id = from_warehouse.pk_id\n INNER JOIN warehouses AS `to_warehouse` ON stock_master.to_warehouse_id = to_warehouse.pk_id\n INNER JOIN users ON stock_master.created_by = users.pk_id\n WHERE\n stock_master.draft = 0 AND\n stock_detail.temporary = 0 AND\n stock_batch_warehouses.warehouse_id = '{$wh_id}'\n {$date_where}\n ORDER BY transaction_type_id,transaction_date"; $this->_em = Zend_Registry::get('doctrine'); $row = $this->_em->getConnection()->prepare($querypro); $row->execute(); return $row->fetchAll(); }
public function updateGenerator() { //App_Controller_Functions::pr($this->form_values); $form_values = $this->form_values; $ccm_model = $this->_em->getRepository('CcmModels')->find($form_values['ccm_model_id']); $user_id = $this->_em->getRepository('Users')->find($this->_user_id); $ccm_model->setModifiedBy($user_id); $ccm_model->setModifiedDate(new \DateTime(App_Controller_Functions::dateToDbFormat(date("d/m/Y")))); $this->_em->persist($ccm_model); $this->_em->flush(); $cold_chain = $this->_em->getRepository('ColdChain')->find($form_values['ccm_id']); $cold_chain->setAssetId($form_values['asset_id']); $model_id = $this->_em->getRepository('CcmModels')->find($form_values['ccm_model_id']); $cold_chain->setCcmModel($model_id); $cold_chain->setSerialNumber($form_values['serial_number']); $asset_type = $this->_em->getRepository('CcmAssetTypes')->find(Model_CcmAssetTypes::GENERATOR); $cold_chain->setCcmAssetType($asset_type); $cold_chain->setWorkingSince(new \DateTime(App_Controller_Functions::dateToDbFormat($form_values['working_since']))); $cold_chain->setCreatedBy($user_id); $cold_chain->setCreatedDate(new \DateTime(App_Controller_Functions::dateToDbFormat(date("d/m/Y")))); $gen = $this->_em->getRepository('CcmGenerators')->findBy(array('ccm' => $form_values['ccm_id'])); $generators = $this->_em->getRepository('CcmGenerators')->find($gen[0]->getPkId()); $power_source = $this->_em->getRepository('ListDetail')->find($form_values['power_source']); $generators->setPowerSource($power_source); $generators->setPowerRating($form_values['power_rating']); $generators->setUseFor($form_values['use_for']); $generators->setAutomaticStartMechanism($form_values['automatic_start']); $generators->setCreatedBy($user_id); $generators->setCreatedDate(new \DateTime(App_Controller_Functions::dateToDbFormat(date("d/m/Y")))); $generators->setModifiedBy($user_id); $generators->setModifiedDate(new \DateTime(App_Controller_Functions::dateToDbFormat(date("d/m/Y")))); $this->_em->persist($generators); $this->_em->flush(); }
public function getAllIssueRunningBatches() { $batch_no = implode(",", $this->form_values['batch_no']); $str_sql = $this->_em->createQueryBuilder()->select("sb.number,\n\t\t\tsbw.pkId,\n\t\t\tsb.expiryDate,\n\t\t\tsbw.quantity")->from("StockBatchWarehouses", "sbw")->join("sbw.stockBatch", "sb")->join("sb.packInfo", "pi")->join("pi.stakeholderItemPackSize", "sip")->where("sbw.quantity > 0 ")->andWhere("sip.itemPackSize = " . $this->form_values['item_pack_size_id']); if (!empty($this->form_values['transaction_date'])) { $str_sql->andWhere("sb.expiryDate >= '" . App_Controller_Functions::dateToDbFormat($this->form_values['transaction_date']) . "' "); } if (!empty($batch_no)) { $str_sql->andWhere("sbw.pkId NOT IN ({$batch_no})"); } $str_sql->andWhere("sbw.warehouse = " . $this->_identity->getWarehouseId())->orderBy("sbw.quantity", "DESC"); // echo $str_sql->getQuery()->getSql(); // exit; $row = $str_sql->getQuery()->getResult(); if (!empty($row) && count($row) > 0) { return $row; } else { return false; } }