/**
*
* @return array
*/
public function listAction()
{
$this->view->page = $page = $this->getRequest()->getParam('page') ?: 1;
if ($this->getRequest()->isPost()) {
$this->view->post = $post = $this->getRequest()->getParams();
}
$total = PurchaseOrderReceivedQuery::create()->filter($post)->count();
$this->view->purchaseOrderReceiveds = $purchaseOrderReceiveds = PurchaseOrderReceivedQuery::create()->filter($post)->page($page, $this->getMaxPerPage())->find();
$this->view->paginator = $this->createPaginator($total, $page);
$this->view->purchaseOrders = \Application\Query\PurchaseOrderQuery::create()->find()->toCombo();
}
public static function SapSync($idPurchaseOder)
{
$purchaseOrder = PurchaseOrderQuery::create()->findByPKOrThrow($idPurchaseOder, "The PurchaseOrder whit id {$idPurchaseOder} does not exist.");
$purchaseOrderEntries = PurchaseOrderEntryQuery::create()->whereAdd(PurchaseOrderEntry::ID_PURCHASE_ORDER, $purchaseOrder->getIdPurchaseOrder())->find();
try {
$sapConnectionClient = new SAPConnectorClient();
$result = $sapConnectionClient->createPurchaseOrder($purchaseOrder, $purchaseOrderEntries);
} catch (Exception $e) {
$result = new stdClass();
$result->SapOrdenResult->code = 500;
$result->SapOrdenResult->message = $e->getMessage();
}
return $result;
}
/**
*
* @param int $idRequisition
* @return boolean
*/
private function configureProformaInvoicesAndPurchaseOrderByRequisition($idRequisition)
{
if (!$this->proformaInvoiceByRequisition instanceof ProformaInvoiceCollection) {
$requisitionEntries = RequisitionEntryQuery::create()->whereAdd(RequisitionEntry::ID_REQUISITION, $idRequisition)->find();
$proformaInvoiceQuery = ProformaInvoiceQuery::create();
$proformaInvoiceQuery->where()->end()->setAND();
$proformaInvoiceQuery->where()->setOR();
while ($requisitionEntry = $requisitionEntries->read()) {
$proformaInvoiceQuery->whereAdd('ProformaInvoiceEntry.' . ProformaInvoiceEntry::REQUISITION_IDS, $requisitionEntry->getIdRequisitionEntry(), BaseQuery::LIKE);
}
$this->proformaInvoiceByRequisition = $proformaInvoiceQuery->find();
$this->purchaseOrderByRequisition = PurchaseOrderQuery::create()->whereAdd('PurchaseOrderEntry.' . PurchaseOrderEntry::BASE_ENTRY, $this->getProformaInvoiceByRequisition()->getPrimaryKeys())->find();
}
}
/**
* (non-PHPdoc)
* @see Application\Cron.Cronable::run()
*/
public function run()
{
$now = new \DateTime(date("Y-m-d", mktime(0, 0, 0, date("m"), date("d"), date("Y"))));
$bookings = BookingQuery::create()->whereAdd(Booking::ETA, \Zend_Date::now()->get('YYYY-MM-dd'), BookingQuery::GREATER_OR_EQUAL)->find();
$eventDispatcher = $this->getEventDispatcherService();
while ($booking = $bookings->read()) {
$eta = new \DateTime($booking->getEta());
$interval = date_diff($eta, $now);
switch ($interval->format('%r%d')) {
case self::FIFTEEN_DAYS_BEFORE:
$eventDispatcher->createBookingNotification($booking, BookingEvent::BOOKING_15_DAYS_NOTIFICATION);
break;
case self::SEVEN_DAYS_BEFORE:
$eventDispatcher->createBookingNotification($booking, BookingEvent::BOOKING_7_DAYS_NOTIFICATION);
break;
case self::THREE_DAYS_BEFORE:
$eventDispatcher->createBookingNotification($booking, BookingEvent::BOOKING_3_DAYS_NOTIFICATION);
break;
case self::TWO_DAYS_BEFORE:
$eventDispatcher->createBookingNotification($booking, BookingEvent::BOOKING_2_DAYS_NOTIFICATION);
break;
case self::TODAY:
$purchaseOrders = PurchaseOrderQuery::create()->whereAdd(PurchaseOrder::ID_FOLIO_IMPORT, $booking->getIdFolioImport())->find();
while ($purchaseOrder = $purchaseOrders->read()) {
$purchaseOrderEntries = PurchaseOrderEntryQuery::create()->whereAdd(PurchaseOrderEntry::ID_PURCHASE_ORDER, $purchaseOrder->getIdPurchaseOrder())->find();
$eventDispatcher->dispatchPurchaseOrderEvent($purchaseOrder, PurchaseOrderEvent::PURCHASEORDER_ARRIVED, $purchaseOrderEntries);
}
break;
}
}
$quarantineRecoveryDeadlines = WarehouseEntranceQuarantineRecoveryDeadlineQuery::create()->whereAdd(WarehouseEntranceQuarantineRecoveryDeadline::RECOVERY_DEADLINE, \Zend_Date::now()->get('YYYY-MM-dd'), WarehouseEntranceQuarantineRecoveryDeadlineQuery::GREATER_OR_EQUAL)->find();
while ($quarantineRecoveryDeadline = $quarantineRecoveryDeadlines->read()) {
$deadline = new \DateTime($quarantineRecoveryDeadline->getRecoveryDeadline());
$interval = date_diff($deadline, $now);
switch ($interval->format('%r%d')) {
case self::TODAY:
if (WarehouseEntranceQuery::create()->whereAdd(WarehouseEntrance::ID_WAREHOUSE_ENTRANCE, $quarantineRecoveryDeadline->getIdWarehouseEntrance())->count()) {
$warehouseEntry = WarehouseEntranceQuery::create()->whereAdd(WarehouseEntrance::ID_WAREHOUSE_ENTRANCE, $quarantineRecoveryDeadline->getIdWarehouseEntrance())->findOne();
$eventDispatcher->createWarehouseEntranceNotification($warehouseEntry, WarehouseEntranceEvent::WAREHOUSE_ENTRIES_QUARANTIES_DEADLINE, $quarantineRecoveryDeadline->getRecoveryDeadline());
}
}
}
}
/**
*
* @param int $folio
* @param int $idEvent
*/
private function getFolioInformation($folio)
{
$folioImport = FolioImportQuery::create()->whereAdd(FolioImport::FOLIO, $folio)->findOneOrThrow($this->i18n->_("Transit Document with folio {$folio} not found."));
$programmedPayments = FolioImportProgrammedPaymentQuery::create()->whereAdd(FolioImportProgrammedPayment::FOLIO, $folioImport->getFolio())->find();
$purchaseOrders = PurchaseOrderQuery::create()->whereAdd(PurchaseOrder::ID_PURCHASE_ORDER, $programmedPayments->getPurchaseOrderIds())->find();
$purchaseOrderEntries = PurchaseOrderEntryQuery::create()->whereAdd(PurchaseOrderEntry::ID_PURCHASE_ORDER, $purchaseOrders->getPrimaryKeys())->find();
$suppliers = SapSupplierQuery::create()->whereAdd(SapSupplier::CARDCODE, $purchaseOrders->getDistinctSuppliers())->find();
$response = array("transitNumber" => $folioImport->getFolio(), "status" => $folioImport->getStatusName());
while ($purchaseOrderEntries->valid()) {
$purchaseOrderEntry = $purchaseOrderEntries->read();
$purchaseOrder = $purchaseOrders->getByPK($purchaseOrderEntry->getIdPurchaseOrder());
$supplier = $suppliers->getByPK($purchaseOrder->getIdSupplier());
$response["entries"][] = array("PO" => $purchaseOrder->getIdPurchaseOrder(), "line" => $purchaseOrderEntry->getLine(), "product" => $purchaseOrderEntry->getIdProduct(), "price" => $purchaseOrderEntry->getPrice(), "currency" => $purchaseOrderEntry->getCurrency(), "supplier" => $supplier->getCardCode(), "supplierName" => $supplier->getCardname());
}
return $response;
}
public function saveProgrammedPaymentAction()
{
$id = $this->getRequest()->getParam('id');
$paymentStatus = $this->getRequest()->getParam('payment_status');
$folioImport = FolioImportQuery::create()->findByPK($id);
$sapConnector = new SAPConnectorClient();
$redir = $this->getBaseUrl() . '/' . $this->getRequest()->getParam('controller') . '/list';
$programmedPayments = $this->getRequest()->getParam('payment-term');
$date = \Zend_Date::now();
$results = array();
$this->getCatalog('ProgrammedPaymentCatalog')->beginTransaction();
foreach ($programmedPayments as $programmedPayment) {
if (empty($programmedPayment['id_folios_import_programmed_payment'])) {
$supplier = SapSupplierQuery::create()->findByPK($programmedPayment['id_company']);
$servicePurchaseOrder = PurchaseOrderFactory::createFromArray(array('id_purchase_order' => "0", 'id_supplier' => $supplier->getCardCode(), 'purchase_order_date' => $date->get("yyyy-MM-dd"), 'delivery_estimate_date' => $date->get("yyyy-MM-dd"), 'invoice_number' => 'Pending', 'id_currency' => $programmedPayment['id_currency'], 'comments' => "Folio Import Service Purchase Order", 'groupnum' => $supplier->getGroupnum(), 'status' => PurchaseOrder::$Status["NewService"], 'id_courtyard' => -1, 'id_folio_import' => $programmedPayment['id_folio_import']));
$purchaseOrderEntries = new PurchaseOrderEntryCollection();
$linesCount = 0;
$purchaseOrderEntry = PurchaseOrderEntryFactory::createFromArray(array('line' => $linesCount, "vis_order" => $linesCount, "id_product" => $programmedPayment['concept'], "quantity" => 1, "price" => $programmedPayment['ammount'], "id_warehouse_store" => '9999', "tax_code" => 'W3', 'lead_time' => -1, 'status' => PurchaseOrderEntry::$Status["ServiceEntry"], 'base_line' => -1, 'base_entry' => -1));
$purchaseOrderEntries->append($purchaseOrderEntry);
$result = $sapConnector->servicePurchaseOrder($servicePurchaseOrder, $purchaseOrderEntries);
$results[] = array('success' => $result->purchaseOrderResult->success, 'id_purchase_order' => $result->purchaseOrderResult->id, 'id_folio_import' => $id, 'message' => $result->purchaseOrderResult->message);
if ($result->purchaseOrderResult->success) {
$programmedPayment['doc_entry'] = $result->purchaseOrderResult->id;
$idFolioImportProgrammedPayment = $programmedPayment['id_folios_import_programmed_payment'];
if ($idFolioImportProgrammedPayment) {
if ($programmedPayment['status']) {
unset($programmedPayment['status']);
$folioImportProgrammedPayment = FolioImportProgrammedPaymentFactory::createFromArray($programmedPayment);
$folioImportProgrammedPayment->setType(ProgrammedPayment::$TypePayment['Folio']);
$this->getCatalog('FolioImportProgrammedPaymentCatalog')->update($folioImportProgrammedPayment);
}
} else {
$folioImportProgrammedPayment = FolioImportProgrammedPaymentFactory::createFromArray($programmedPayment);
$folioImportProgrammedPayment->setType(ProgrammedPayment::$TypePayment['Folio']);
$folioImportProgrammedPayment->setStatus(ProgrammedPayment::$TypeStatus['Pending']);
$folioImportProgrammedPayment->setIdFolioImport($id);
$folioImportProgrammedPayment->setFolio($folioImport->getFolio());
$this->getCatalog('FolioImportProgrammedPaymentCatalog')->create($folioImportProgrammedPayment);
}
}
}
}
try {
if ($this->setResponseMessage($results) && $paymentStatus == FolioImport::$PaymentStatus['Inactive']) {
$this->setFlash('warning', 'The payments were closed');
$purchaseOrderQuery = PurchaseOrderQuery::create();
$purchaseOrders = $purchaseOrderQuery->whereAdd(PurchaseOrder::ID_FOLIO_IMPORT, $id)->whereAdd(PurchaseOrder::STATUS, PurchaseOrder::$Status['NewService'], PurchaseOrderQuery::NOT_EQUAL)->find();
$result = $sapConnector->createLandedCost($purchaseOrders);
} else {
$this->setFlash('warning', 'The payments were NOT closed');
}
} catch (Exception $e) {
$this->getCatalog("ProgrammedPaymentCatalog")->rollBack();
$this->setFlash('error', $this->i18n->_($e->getMessage()));
}
$this->newLogForPaymentAdded($folioImport);
$this->getCatalog('ProgrammedPaymentCatalog')->commit();
$this->_redirect('folio-import/list');
}
/**
*
* @return \Application\Model\Collection\PurchaseOrderCollection
*/
public function getPurchaseOrderCollection()
{
if (!$this->purchaseOrderCollection instanceof PurchaseOrderCollection) {
$this->purchaseOrderCollection = PurchaseOrderQuery::create()->filter(array('startDate' => $this->getInitialDate(), 'endDate' => $this->getFinalDate()))->find();
}
return $this->purchaseOrderCollection;
}
/**
*
*
*/
public function getQuarantineListResultsAction()
{
$page = $this->getRequest()->getParam('page') ?: 1;
$warehouseEntries = WarehouseEntranceQuery::create()->whereAdd(WarehouseEntrance::ID_FOLIO_IMPORT, null, WarehouseEntranceQuery::NOT_EQUAL)->whereAdd(WarehouseEntrance::ID_FOLIO_IMPORT, '0', WarehouseEntranceQuery::NOT_EQUAL)->find();
$params = $this->getRequest()->getParams();
$params['id_purchase_order'] = PurchaseOrderQuery::create()->whereAdd(PurchaseOrder::SAP_DOCUMENT_NUMBER, $params['sap_document_number'])->fetchOne();
if (empty($params['id_purchase_order']) && !empty($params['sap_document_number'])) {
$params['id_purchase_order'] = -1;
}
$warehouseEntryProductQuery = WarehouseEntranceProductQuery::create()->whereAdd(WarehouseEntranceProduct::ID_PURCHASE_ORDER, null, WarehouseEntranceProductQuery::NOT_EQUAL)->whereAdd(WarehouseEntranceProduct::STATUS, WarehouseEntranceProduct::$Status['Quarantine'], WarehouseEntranceProductQuery::EQUAL)->whereAdd(WarehouseEntranceProduct::ID_WAREHOUSE_ENTRANCE, $warehouseEntries->getPrimaryKeys(), WarehouseEntranceProductQuery::IN)->filter($params)->orderBy(WarehouseEntranceProduct::ID_WAREHOUSE_ENTRANCE, 'DESC')->findByPage($page, $this->getMaxPerPage());
$purchaseOrderCollection = PurchaseOrderQuery::create()->whereAdd(PurchaseOrder::ID_PURCHASE_ORDER, $warehouseEntryProductQuery->getPurchaseOrderIds(), PurchaseOrderQuery::IN)->find();
$purchaseOrderEntryCollection = PurchaseOrderEntryQuery::create()->whereAdd(PurchaseOrderEntry::ID_PURCHASE_ORDER, $purchaseOrderCollection->getPrimaryKeys(), PurchaseOrderEntryQuery::IN)->find();
$i = 0;
$this->getCatalog('QuarantineCatalog')->beginTransaction();
while ($warehouseEntryLine = $warehouseEntryProductQuery->read()) {
$warehouseEntry = WarehouseEntranceQuery::create()->whereAdd(WarehouseEntrance::ID_WAREHOUSE_ENTRANCE, $warehouseEntryLine->getIdWarehouseEntrance())->findOne();
if (TransferLineLogQuery::create()->whereAdd(TransferLineLog::BASEENTRY, $warehouseEntryLine->getIdWarehouseEntrance())->whereAdd(TransferLineLog::BASELINE, $warehouseEntryLine->getIdWarehouseEntranceProduct())->count()) {
$transferLine = TransferLineLogQuery::create()->whereAdd(TransferLineLog::BASEENTRY, $warehouseEntryLine->getIdWarehouseEntrance())->whereAdd(TransferLineLog::BASELINE, $warehouseEntryLine->getIdWarehouseEntranceProduct())->findOne();
$warehouseEntryLines[$i]['warehouse'] = WarehouseStoreQuery::create()->whereAdd(WarehouseStore::CODE, $transferLine->getWhsCode())->findOne()->getName();
} else {
$warehouseEntryLines[$i]['warehouse'] = $this->i18n->_('Not Available');
}
$claimReport = $this->getClaimReport($warehouseEntryLine->getIdWarehouseEntrance(), $warehouseEntryLine->getIdWarehouseEntranceProduct());
$folioImport = $this->getWarehouseEntryFolioImport($warehouseEntry->getIdFolioImport());
$quarantine = $this->getQuarantine($warehouseEntryLine->getIdWarehouseEntrance(), $warehouseEntryLine->getIdWarehouseEntranceProduct());
$product = ProductQuery::create()->findByPK($warehouseEntryLine->getIdProduct());
$warehouseEntryLines[$i]['id_warehouse_entrance_product'] = $warehouseEntryLine->getIdWarehouseEntranceProduct();
$warehouseEntryLines[$i]['id_warehouse_entrance'] = $warehouseEntry->getIdWarehouseEntrance();
$warehouseEntryLines[$i]['id_folio_import'] = $folioImport->getIdFolioImport();
$warehouseEntryLines[$i]['folio_import'] = $folioImport->getFolio();
if (PurchaseOrderQuery::create()->whereAdd(PurchaseOrder::ID_PURCHASE_ORDER, $warehouseEntryLine->getIdPurchaseOrder())->find()->count()) {
$purchaseOrder = PurchaseOrderQuery::create()->findByPK($warehouseEntryLine->getIdPurchaseOrder());
} else {
$purchaseOrder = new PurchaseOrder();
}
$purchaseOrderEntry = PurchaseOrderEntryQuery::create()->whereAdd(PurchaseOrderEntry::ID_PURCHASE_ORDER, $purchaseOrder->getIdPurchaseOrder())->whereAdd(PurchaseOrderEntry::LINE, (string) $warehouseEntryLine->getBaseLine())->findOneOrElse(new PurchaseOrderEntry());
$proformaInvoice = ProformaInvoiceQuery::create()->whereAdd(ProformaInvoice::ID_PROFORMA_INVOICE, $purchaseOrderEntry->getBaseEntry())->findOneOrElse(new ProformaInvoice());
$warehouseEntryLines[$i]['proforma_invoice'] = $proformaInvoice->getSapDocumentNumber();
$warehouseEntryLines[$i]['purchase_order'] = $purchaseOrder->getSapDocumentNumber();
$warehouseEntryLines[$i]['item_code'] = $product->getItemCode();
$warehouseEntryLines[$i]['item_name'] = $product->getItemName();
$warehouseEntryLines[$i]['original_quantity'] = $warehouseEntryLine->getQuantity();
$warehouseEntryLines[$i]['current_quantity'] = $currentQuantity = $quarantine->getQuantity();
if ($warehouseEntryLine->getLineStatus() == 'C') {
// $warehouseEntryLines[$i]['cancelled'] = true;
// $warehouseEntryLines[$i]['status'] = 'Cancelled';
// $warehouseEntryLines[$i]['statusRow'] = ' ';
} else {
$warehouseEntryLines[$i]['cancelled'] = false;
if (!$currentQuantity) {
$warehouseEntryLines[$i]['status'] = 'Debugged';
$warehouseEntryLines[$i]['statusRow'] = 'success';
} else {
$warehouseEntryLines[$i]['status'] = $warehouseEntryLine->getStatusName();
$warehouseEntryLines[$i]['statusRow'] = 'warning';
}
}
$warehouseEntryLines[$i]['quarantine'] = $warehouseEntryLine->getStatusName();
$warehouseEntryLines[$i]['claimed_amount'] = $claimReport->getClaimedAmount();
$warehouseEntryLines[$i]['final_amount'] = $claimReport->getFinalAmount();
$warehouseEntryLines[$i]['claim_report_status'] = $claimReport->getStatusName();
$warehouseEntryLines[$i]['allow_debug'] = TransferLineLogQuery::create()->whereAdd(TransferLineLog::BASEENTRY, $warehouseEntryLine->getIdWarehouseEntrance())->whereAdd(TransferLineLog::BASELINE, $warehouseEntryLine->getIdWarehouseEntranceProduct())->count();
if ($warehouseEntryLine->getStatus() == WarehouseEntranceProduct::$Status['Quarantine']) {
$quarantineProduct = $this->getQuarantine($warehouseEntryLine->getIdWarehouseEntrance(), $warehouseEntryLine->getIdWarehouseEntranceProduct());
$warehouseEntryLines[$i]['initial_quantity'] = $quarantineProduct->getQuantity();
}
$i++;
}
$this->getCatalog('QuarantineCatalog')->commit();
die(json_encode($warehouseEntryLines));
}
public function getPurchaseOrderEntriesForNewClaimReportAction()
{
$docNum = $this->getRequest()->getParam('docNum');
$values = array();
$query = PurchaseOrderQuery::create()->whereAdd(PurchaseOrder::SAP_DOCUMENT_NUMBER, $docNum)->findOne();
$entries = PurchaseOrderEntryQuery::create()->whereAdd(PurchaseOrderEntry::ID_PURCHASE_ORDER, $query->getIdPurchaseOrder())->find();
$i = 0;
while ($entry = $entries->read()) {
$product = ProductQuery::create()->findByPK($entry->getIdProduct());
$values[$i]['itemName'] = $product->getItemName();
$values[$i]['itemCode'] = $product->getItemCode();
$i++;
}
die(json_encode($values));
}
public function exportToExcelAction()
{
$params = $this->getRequest()->getParams();
$allPurchaseOrderEntries = PurchaseOrderEntryQuery::create()->find();
$purchaseOrders = PurchaseOrderQuery::create()->filter($params)->addDescendingOrderBy(PurchaseOrder::PURCHASE_ORDER_DATE)->whereAdd("PurchaseOrder.DocType", "I")->whereAdd("PurchaseOrderEntry.ItemCode", "TR%", 'NOT LIKE')->whereAdd("PurchaseOrderEntry.WhsCode", "9999", PurchaseOrderQuery::NOT_LIKE)->whereAdd(PurchaseOrder::ID_PURCHASE_ORDER, $allPurchaseOrderEntries->getDistinctPurchaseOrder(), PurchaseOrderQuery::IN)->find();
$products = ProductQuery::create()->whereAdd(Product::ITEM_CODE, $allPurchaseOrderEntries->getProducts(), ProductQuery::IN)->find();
$suppliers = SapSupplierQuery::create()->whereAdd(SapSupplier::ID_SUPPLIER, $purchaseOrders->getDistinctSuppliers(), SapSupplierQuery::IN)->find();
$warehousesStores = WarehouseStoreQuery::create()->whereAdd(WarehouseStore::CODE, $allPurchaseOrderEntries->getDistinctIdWarehouseStore(), WarehouseStoreQuery::IN)->find();
$warehouses = WarehouseQuery::create()->whereAdd(Warehouse::CODE, $warehousesStores->getDistinctWarehouses(), WarehouseQuery::IN)->find();
$currencies = CurrencyQuery::create()->find();
$countries = CountryQuery::create()->find();
require_once 'PHPExcel.php';
$objPHPExcel = new PHPExcel();
$activeSheet = $objPHPExcel->setActiveSheetIndex(0);
$activeSheet->setCellValue('A1', $this->i18n->_('Purchase Order Folio'))->setCellValue('B1', $this->i18n->_('Destination Country'))->setCellValue('C1', $this->i18n->_('Supplier Code'))->setCellValue('D1', $this->i18n->_('Supplier Name'))->setCellValue('E1', $this->i18n->_('Creation Date'))->setCellValue('F1', $this->i18n->_('Status'))->setCellValue('G1', $this->i18n->_('Entry Line'))->setCellValue('H1', $this->i18n->_('Product Code'))->setCellValue('I1', $this->i18n->_('Product Description'))->setCellValue('J1', $this->i18n->_('Amount'))->setCellValue('K1', $this->i18n->_('Price'))->setCellValue('L1', $this->i18n->_('Import'))->setCellValue('M1', $this->i18n->_('Currency'))->setCellValue('N1', $this->i18n->_('Location'))->setCellValue('O1', $this->i18n->_('Warehouse'));
$activeSheet->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$i = 2;
while ($purchaseOrders->valid()) {
$purchaseOrder = $purchaseOrders->read();
$currency = $currencies->getByPkOrEmpty($purchaseOrder->getIdCurrency());
$supplier = $suppliers->getByPkOrElse($purchaseOrder->getIdSupplier(), new SapSupplier());
$country = $countries->getByPK($purchaseOrder->getIdDestinationCountry());
$purchaseOrderEntries = $allPurchaseOrderEntries->filterByPurchaseOrderPk($purchaseOrder->getIdPurchaseOrder());
$entriesCount = $i;
while ($purchaseOrderEntries->valid()) {
$purchaseOrderEntry = $purchaseOrderEntries->read();
$product = $products->getByPK($purchaseOrderEntry->getIdProduct()) ? $products->getByPK($purchaseOrderEntry->getIdProduct()) : new Product();
$warehouseStore = $warehousesStores->getByPkOrElse($purchaseOrderEntry->getIdWarehouseStore(), new WarehouseStore());
$warehouse = $warehouseStore->getLocation() > 0 ? $warehouses->getByPK($warehouseStore->getLocation()) : new WarehouseStore();
$activeSheet->setCellValue('A' . $entriesCount, $purchaseOrder->getSapDocumentNumber())->setCellValue('B' . $entriesCount, utf8_encode($country->getName()))->setCellValue('C' . $entriesCount, $supplier->getCardCode())->setCellValue('D' . $entriesCount, $supplier->getCardname())->setCellValue('E' . $entriesCount, $purchaseOrder->getPurchaseOrderDate())->setCellValue('F' . $entriesCount, $purchaseOrder->getStatusName())->setCellValue("G" . $entriesCount, $purchaseOrderEntry->getLine())->setCellValue("H" . $entriesCount, $product->getItemCode())->setCellValue("I" . $entriesCount, $product->getItemName())->setCellValue("J" . $entriesCount, $purchaseOrderEntry->getAmount())->setCellValue("K" . $entriesCount, $purchaseOrderEntry->getPrice())->setCellValue("L" . $entriesCount, $purchaseOrderEntry->getImport())->setCellValue("M" . $entriesCount, $currency->getCurrCode())->setCellValue("N" . $entriesCount, $warehouse->getName())->setCellValue("O" . $entriesCount, $warehouseStore->getName());
$entriesCount++;
}
$i = $entriesCount;
}
$columns = range('A', 'O');
foreach ($columns as $column) {
$activeSheet->getColumnDimension($column)->setAutoSize(true);
}
header("Content-Type: application/vnd.ms-excel");
$date = \Zend_Date::now();
$fileName = $this->i18n->_("Purchase_orders_report") . $date->get("ddMMyyyy");
header("Content-Disposition: attachment; filename=\"{$fileName}.xlsx\"");
header("Cache-Control: max-age=0");
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save("php://output");
exit;
}
/**
*
* @param int $idFolioImport
*/
public function __construct($idFolioImport, $USDToMXN, $EURtoUSD)
{
$this->folioImport = FolioImportQuery::create()->findByPK($idFolioImport);
if ($USDToMXN) {
$this->USDToMXN = $USDToMXN;
}
if ($EURtoUSD) {
$this->EURtoUSD = $EURtoUSD;
}
$this->purchaseOrders = PurchaseOrderQuery::create()->whereAdd(PurchaseOrder::ID_FOLIO_IMPORT, $idFolioImport)->find();
$this->purchaseOrdersEntries = PurchaseOrderEntryQuery::create()->whereAdd(PurchaseOrderEntry::ID_PURCHASE_ORDER, $this->getPurchaseOrders()->getPrimaryKeys())->find();
$this->products = $products = ProductQuery::create()->whereAdd(Product::ITEM_CODE, $this->getPurchaseOrdersEntries()->getProducts())->find();
$this->productAntidumpingDuties = ProductAntidumpingDutyQuery::create()->whereAdd(ProductAntidumpingDuty::PARENT_KEY, $products->getKeyMother())->find();
}
private function getPurchaseOrders($idFolioImport, $status = null, $in = true)
{
$id = $idFolioImport;
$purchaseOrderFolioQuery = PurchaseOrderFolioQuery::create()->addColumn(PurchaseOrderFolio::ID_PURCHASE_ORDER)->whereAdd(PurchaseOrderFolio::ID_FOLIO_IMPORT, $id);
if ($status == null) {
$query = PurchaseOrderQuery::create()->whereAdd(PurchaseOrder::ID_PURCHASE_ORDER, $purchaseOrderFolioQuery, PurchaseOrderQuery::IN)->count();
$purchaseOrderQuery = PurchaseOrderQuery::create()->whereAdd(PurchaseOrder::ID_PURCHASE_ORDER, $purchaseOrderFolioQuery, PurchaseOrderQuery::IN)->fetchAll();
} else {
if ($in) {
$query = PurchaseOrderQuery::create()->whereAdd(PurchaseOrder::ID_PURCHASE_ORDER, $purchaseOrderFolioQuery, PurchaseOrderQuery::IN)->whereAdd(PurchaseOrder::STATUS, PurchaseOrder::$Status[$status], PurchaseOrderQuery::IN)->count();
$purchaseOrderQuery = PurchaseOrderQuery::create()->whereAdd(PurchaseOrder::ID_PURCHASE_ORDER, $purchaseOrderFolioQuery, PurchaseOrderQuery::IN)->whereAdd(PurchaseOrder::STATUS, PurchaseOrder::$Status[$status], PurchaseOrderQuery::IN)->fetchAll();
} else {
$query = PurchaseOrderQuery::create()->whereAdd(PurchaseOrder::ID_PURCHASE_ORDER, $purchaseOrderFolioQuery, PurchaseOrderQuery::IN)->whereAdd(PurchaseOrder::STATUS, PurchaseOrder::$Status[$status], PurchaseOrderQuery::NOT_EQUAL)->count();
$purchaseOrderQuery = PurchaseOrderQuery::create()->whereAdd(PurchaseOrder::ID_PURCHASE_ORDER, $purchaseOrderFolioQuery, PurchaseOrderQuery::IN)->whereAdd(PurchaseOrder::STATUS, PurchaseOrder::$Status[$status], PurchaseOrderQuery::NOT_EQUAL)->fetchAll();
}
}
if ($query) {
$i = 0;
foreach ($purchaseOrderQuery as $purchaseOrder) {
try {
$purchaseOrderEntryQuery = PurchaseOrderEntryQuery::create()->whereAdd(PurchaseOrder::ID_PURCHASE_ORDER, $purchaseOrder['id_purchase_order'])->fetchAll();
$price = 0;
$cbm = 0;
$individual_packs = 0;
$cartons = 0;
$quantity = 0;
$weight = 0;
$valuable = array();
$j = 0;
$packingList = PurchaseOrderFileQuery::create()->whereAdd(PurchaseOrderFile::ID_PURCHASE_ORDER, $purchaseOrder['id_purchase_order'])->whereAdd(PurchaseOrderFile::DOCUMENT_TYPE, PurchaseOrderFile::$DocumentType['PackingList'])->count();
$commercialInvoice = PurchaseOrderFileQuery::create()->whereAdd(PurchaseOrderFile::ID_PURCHASE_ORDER, $purchaseOrder['id_purchase_order'])->whereAdd(PurchaseOrderFile::DOCUMENT_TYPE, PurchaseOrderFile::$DocumentType['CommercialInvoice'])->count();
if ($packingList) {
$purchaseOrders[$i]['packing_list'] = true;
} else {
$purchaseOrders[$i]['packing_list'] = false;
}
if ($commercialInvoice) {
$purchaseOrders[$i]['commercial_invoice'] = true;
} else {
$purchaseOrders[$i]['commercial_invoice'] = false;
}
foreach ($purchaseOrderEntryQuery as $purchaseOrderEntry) {
$product = ProductQuery::create()->findByPK($purchaseOrderEntry['id_product'])->toArray();
if (!empty($product['mc_parts'])) {
$cartonsEntry = ceil($purchaseOrderEntry['quantity'] / $product['mc_parts']);
} else {
$this->setFlash('warning', $this->i18n->_("You must edit MC Parts, it cannot be cero."));
$this->_redirect('product/edit/id/' . $product['id_product']);
}
$volume = $product['mc_front'] * $product['mc_height'] * $product['mc_depth'] / 1000000;
$cbm += $cartonsEntry * $volume;
$weight += $cartonsEntry * $product['mc_weight'];
$price += $purchaseOrderEntry['quantity'] * $purchaseOrderEntry['price'];
$cartons += $cartonsEntry;
$quantity += $purchaseOrderEntry['quantity'];
$entries[$j]['id_product'] = $product['id_product'];
$entries[$j]['status'] = $this->getPurchaseOrderEntryStatus($purchaseOrderEntry['status']);
$entries[$j]['important'] = $product['important'];
$entries[$j]['item_code'] = $product['item_code'];
$entries[$j]['product'] = $product['item_name'];
$entries[$j]['price'] = $purchaseOrderEntry['price'];
$entries[$j]['amount'] = $purchaseOrderEntry['quantity'] * $purchaseOrderEntry['price'];
$entries[$j]['cartons'] = $cartonsEntry;
$entries[$j]['quantity'] = $purchaseOrderEntry['quantity'];
$entries[$j]['weight'] = $product['mc_weight'] * $cartonsEntry;
$entries[$j]['volume'] = number_format($volume * $cartonsEntry, 2);
$entries[$j]['default_shipping'] = FreightTypeQuery::create()->findByPK($product['default_freight_fare'])->getName();
if (!empty($product['import_permit'])) {
$importPermit = FileQuery::create()->findByPK($product['import_permit']);
if ($importPermit) {
$entries[$j]['import_permit'] = true;
} else {
$entries[$j]['import_permit'] = false;
}
} else {
$entries[$j]['import_permit'] = false;
}
$entries[$j]['row_status'] = 'success';
$purchaseOrders[$i]['row_status'] = 'success';
if (!$entries[$j]['import_permit']) {
$entries[$j]['row_status'] = 'error';
$purchaseOrders[$i]['row_status'] = 'error';
$entries[$j]['label'] = $this->i18n->_('This product, does not have an Import Clearance Document.');
$purchaseOrders[$i]['label'] = $this->i18n->_('This Purchase Order has entries without Import Clearance.');
} else {
if (!$purchaseOrders[$i]['commercial_invoice']) {
$purchaseOrders[$i]['row_status'] = 'error';
$purchaseOrders[$i]['label'] = $this->i18n->_('This Purchase Order, does not have a Commercial Invoice Document.');
} else {
if (!$purchaseOrders[$i]['packing_list']) {
$purchaseOrders[$i]['row_status'] = 'error';
$purchaseOrders[$i]['label'] = $this->i18n->_('This Purchase Order, does not have a Packing List Document.');
} else {
if ($product['important']) {
$entries[$j]['row_status'] = 'warning';
$purchaseOrders[$i]['row_status'] = 'warning';
$entries[$j]['label'] = $this->i18n->_('This product is small and valuable.');
$purchaseOrders[$i]['label'] = $this->i18n->_('This Purchase Order has small and valuable stuff.');
} else {
$entries[$j]['row_status'] = 'success';
$purchaseOrders[$i]['row_status'] = 'success';
}
}
}
}
$j++;
}
//Checks if the entry has important stuff
if (count($valuable) == 1) {
$purchaseOrders[$i]['importance'] = $valuable[1];
} else {
$flag = 0;
$value = $valuable[1];
for ($j = 2; $j <= count($valuable); $j++) {
if ($valuable[$j] != $value) {
$value = 2;
break;
}
}
$purchaseOrders[$i]['importance'] = $value;
}
switch ($purchaseOrders[$i]['importance']) {
case '1':
$purchaseOrders[$i]['importance'] = $this->i18n->_('Yes');
break;
case '2':
$purchaseOrders[$i]['importance'] = $this->i18n->_('Mixed');
break;
default:
$purchaseOrders[$i]['importance'] = $this->i18n->_('No');
}
} catch (Exception $e) {
var_dump($e->getMessage());
}
$purchaseOrders[$i]['id_purchase_order_folio'] = PurchaseOrderFolioQuery::create()->whereAdd(PurchaseOrderFolio::ID_PURCHASE_ORDER, $purchaseOrder['id_purchase_order'])->whereAdd(PurchaseOrderFolio::ID_FOLIO_IMPORT, $idFolioImport)->findOne()->getIdPurchaseOrderFolio();
$cartons = 0;
$quantity = 0;
$weight = 0;
$cbm = 0;
$price = 0;
foreach ($entries as $entry) {
$cartons += intval($entry['cartons']);
$quantity += intval($entry['quantity']);
$weight += $entry['weight'];
$cbm += $entry['volume'];
$price += $entry['price'] * $entry['quantity'];
}
$purchaseOrders[$i]['entries'] = $entries;
$purchaseOrders[$i]['cartons'] = $cartons;
$purchaseOrders[$i]['quantity'] = $quantity;
$purchaseOrders[$i]['weight'] = $weight;
$purchaseOrders[$i]['cbm'] = number_format($cbm, 2);
$purchaseOrders[$i]['price'] = $price;
$purchaseOrders[$i]['id_purchase_order'] = $purchaseOrder['id_purchase_order'];
$purchaseOrders[$i]['courtyard_arrival_date'] = $purchaseOrder['courtyard_arrival_date'];
$i++;
}
return $purchaseOrders;
} else {
return null;
}
}
/**
* This action get all purchase order history about one product
*
* @author Erick Guevara Martínez
* @return PurchaseOrderEntry
*/
public function purchasesHistoryAction()
{
$idProduct = $this->getRequest()->getParam("idProduct");
// Get the searched product to obtain your mother key
$product = ProductQuery::create()->findByPKOrThrow($idProduct, $this->i18n->_("The Product with id {$idProduct} does not exist"));
// Search all products with the same key mother
$motherKeyProducts = ProductQuery::create()->whereAdd(Product::ITEM_CODE, $product->getKeyMother() . "%")->find();
$purchaseOrderEntries = new PurchaseOrderEntryCollection();
if ($motherKeyProducts->count() > 0) {
// Search the satisfactory purchases of all searched products previouslly
$purchaseOrderEntries = PurchaseOrderEntryQuery::create()->whereAdd(PurchaseOrderEntry::ID_PRODUCT, $motherKeyProducts->getPrimaryKeys(), BaseQuery::IN)->whereAdd(PurchaseOrderEntry::STATUS, array(PurchaseOrderEntry::$Status["Inactive"], PurchaseOrderEntry::$Status["Cancelled"]), BaseQuery::NOT_IN)->find();
}
$purchaseOrders = new PurchaseOrderCollection();
if ($purchaseOrderEntries->count() > 0) {
// Search the respective purchase order of the entries searched previouslly
$purchaseOrders = PurchaseOrderQuery::create()->whereAdd(PurchaseOrder::ID_PURCHASE_ORDER, $purchaseOrderEntries->getDistinctPurchaseOrder(), BaseQuery::IN)->whereAdd(PurchaseOrder::CANCELLED, PurchaseOrder::$Cancelled["Yes"], BaseQuery::NOT_EQUAL)->addDescendingOrderBy(PurchaseOrder::PURCHASE_ORDER_DATE)->find();
}
$suppliers = new SupplierCollection();
$currencies = new CurrencyCollection();
if ($purchaseOrders->count() > 0) {
// Search the respective suppliers of the orders samples searched previouslly
$suppliers = SapSupplierQuery::create()->whereAdd(SapSupplier::CARDCODE, $purchaseOrders->getDistinctSuppliers(), BaseQuery::IN)->find();
$currencies = CurrencyQuery::create()->whereAdd(Currency::CURRCODE, $purchaseOrders->getDistinctCurrencies(), BaseQuery::IN)->find();
}
// Get the required info to ajax response
$response = array();
$i = 0;
while ($order = $purchaseOrders->read()) {
$entries = $purchaseOrderEntries->filter(function (PurchaseOrderEntry $POEntry) use($order) {
if ($POEntry->getIdPurchaseOrder() == $order->getIdPurchaseOrder()) {
return $POEntry;
}
});
$currency = $currencies->getByPK($order->getIdCurrency());
$supplier = $suppliers->getByPK($order->getIdSupplier());
while ($entry = $entries->read()) {
$product = $motherKeyProducts->getByPK($entry->getIdProduct());
$response[$i]["status"] = $product->getStatus();
$response[$i]["motherKey"] = $product->getKeyMother();
$response[$i]["productKey"] = $product->getItemCode();
$response[$i]["productName"] = utf8_encode($product->getItemName());
$response[$i]["date"] = utf8_encode($order->getPurchaseOrderDateAsZendDate()->toString("yyyy-MM-dd hh:mm:ss"));
$response[$i]["cost"] = $entry->getPrice();
$response[$i]["quantity"] = $entry->getQuantity();
$response[$i]["currencyCode"] = htmlentities($currency->getCurrCode());
$response[$i]["currencySymbol"] = $currency->getDocCurrCode();
$i++;
}
}
die(Zend_Json::encode($response));
}