/**
* Getting the items
*
* @param unknown $sender
* @param unknown $param
* @throws Exception
*
*/
public function getItems($sender, $param)
{
$results = $errors = array();
try {
if (!isset($param->CallbackParameter->searchCriteria->supplierId) || !($supplier = Supplier::get(trim($param->CallbackParameter->searchCriteria->supplierId))) instanceof Supplier) {
throw new Exception('Invalid Supplier provided');
}
if (!isset($param->CallbackParameter->searchCriteria->invoiceNo) || ($invoiceNo = trim($param->CallbackParameter->searchCriteria->invoiceNo)) === '') {
throw new Exception('Invalid Invoice number provided');
}
ReceivingItem::getQuery()->eagerLoad('ReceivingItem.purchaseOrder', 'inner join', 'rec_item_po', 'rec_item_po.id = rec_item.purchaseOrderId');
if (ReceivingItem::countByCriteria('rec_item_po.supplierId = ? and rec_item.invoiceNo = ?', array($supplier->getId(), $invoiceNo)) === 0) {
throw new Exception('There is no such a invoice(invoice No=' . $invoiceNo . ') for supplier:' . $supplier->getName());
}
$sql = 'select ri.productId,
ri.unitPrice `unitPrice`,
sum(ri.qty) `qty`,
group_concat(distinct ri.id) `itemIds`,
group_concat(distinct po.id) `poIds`
from receivingitem ri
inner join purchaseorder po on (po.id = ri.purchaseOrderId)
where ri.active = 1 and ri.invoiceNo = ? and po.supplierId = ?
group by ri.productId, ri.unitPrice';
$params = array($invoiceNo, $supplier->getId());
$rows = Dao::getResultsNative($sql, $params);
$results['supplier'] = $supplier->getJson();
$results['items'] = array();
foreach ($rows as $row) {
$items = count($itemIds = explode(',', $row['itemIds'])) === 0 ? array() : ReceivingItem::getAllByCriteria('id in (' . implode(',', array_fill(0, count($itemIds), '?')) . ')', $itemIds);
$pos = count($poIds = explode(',', $row['poIds'])) === 0 ? array() : PurchaseOrder::getAllByCriteria('id in (' . implode(',', array_fill(0, count($poIds), '?')) . ')', $poIds);
$results['items'][] = array('product' => Product::get($row['productId'])->getJson(), 'totalQty' => $row['qty'], 'totalPrice' => $row['unitPrice'] * $row['qty'], 'items' => array_map(create_function('$a', 'return $a->getJson();'), $items), 'purchaseOrders' => array_map(create_function('$a', 'return $a->getJson();'), $pos));
}
} catch (Exception $ex) {
$errors[] = $ex->getMessage();
}
$param->ResponseData = StringUtilsAbstract::getJson($results, $errors);
}
/**
* Searching PO
*
* @param unknown $sender
* @param unknown $param
*
* @throws Exception
*
*/
public function searchPO($sender, $param)
{
$results = $errors = array();
try {
$items = array();
$searchTxt = isset($param->CallbackParameter->searchTxt) ? trim($param->CallbackParameter->searchTxt) : '';
if ($searchTxt === '') {
$results['items'] = '';
} else {
PurchaseOrder::getQuery()->eagerLoad('PurchaseOrder.supplier');
$pos = PurchaseOrder::getAllByCriteria('(po.purchaseOrderNo like :searchTxt OR po.supplierRefNo like :searchTxt OR po_sup.name like :suplierName) AND (status = :statusReceiving OR status = :statusOrdered)', array('searchTxt' => $searchTxt . '%', 'suplierName' => '%' . $searchTxt . '%', 'statusReceiving' => PurchaseOrder::STATUS_RECEIVING, 'statusOrdered' => PurchaseOrder::STATUS_ORDERED), true, null, DaoQuery::DEFAUTL_PAGE_SIZE, array('id' => 'desc'));
foreach ($pos as $po) {
if (!$po instanceof PurchaseOrder) {
throw new Exception('Invalid PurchaseOrder passed in!');
}
$items[] = $this->_getPOJson($po);
}
$results['items'] = $items;
}
} catch (Exception $ex) {
$errors[] = $ex->getMessage();
}
$param->ResponseData = StringUtilsAbstract::getJson($results, $errors);
}
private function _getPurchaseOrders(array $params)
{
$searchTxt = trim(isset($params['searchTxt']) ? $params['searchTxt'] : '');
if ($searchTxt === '') {
throw new Exception('SearchTxt is needed');
}
$pageSize = isset($params['pageSize']) && ($pageSize = trim($params['pageSize'])) !== '' ? $pageSize : DaoQuery::DEFAUTL_PAGE_SIZE;
$pageNo = isset($params['pageNo']) && ($pageNo = trim($params['pageNo'])) !== '' ? $pageNo : null;
$orderBy = isset($params['orderBy']) ? $params['orderBy'] : array();
$where = array('purchaseOrderNo like :searchTxt');
$sqlParams = array('searchTxt' => '%' . $searchTxt . '%');
$stats = array();
$items = PurchaseOrder::getAllByCriteria(implode(' AND ', $where), $sqlParams, true, $pageNo, $pageSize, $orderBy, $stats);
$results = array();
$results['items'] = array_map(create_function('$a', 'return $a->getJson();'), $items);
$results['pageStats'] = $stats;
return $results;
}
/**
* Getting the items
*
* @param unknown $sender
* @param unknown $param
* @throws Exception
*
*/
public function getItems($sender, $param)
{
$results = $errors = array();
try {
$class = trim($this->_focusEntity);
$pageNo = 1;
$pageSize = DaoQuery::DEFAUTL_PAGE_SIZE;
if (isset($param->CallbackParameter->pagination)) {
$pageNo = $param->CallbackParameter->pagination->pageNo;
$pageSize = $param->CallbackParameter->pagination->pageSize;
}
$where = array('ri.active = :active');
$params = array('active' => 1);
if (isset($param->CallbackParameter->searchCriteria)) {
$criteria = $param->CallbackParameter->searchCriteria;
if (isset($criteria->invoiceNo) && ($invNo = trim($criteria->invoiceNo)) !== '') {
$where[] = 'ri.invoiceNo like :invNo';
$params['invNo'] = '%' . $invNo . '%';
}
if (isset($criteria->purchaseOrderIds) && count($purchaseOrderIds = array_filter(explode(',', trim($criteria->purchaseOrderIds)))) > 0) {
$poWhere = array();
foreach ($purchaseOrderIds as $index => $purchaseOrderId) {
$key = 'purchaseOrderId' . $index;
$poWhere[] = ':' . $key;
$params[$key] = $purchaseOrderId;
}
$where[] = 'ri.purchaseOrderId in(' . implode(', ', $poWhere) . ')';
}
if (isset($criteria->supplierIds) && count($supplierIds = array_filter(explode(',', trim($criteria->supplierIds)))) > 0) {
$suppWhere = array();
foreach ($supplierIds as $index => $supplierId) {
$key = 'supplierId' . $index;
$suppWhere[] = ':' . $key;
$params[$key] = $supplierId;
}
$where[] = 'po.supplierId in(' . implode(', ', $suppWhere) . ')';
}
}
$sql = 'select sql_calc_found_rows ri.invoiceNo,
po.supplierId,
sum(ri.qty) `qty`,
sum(ri.unitPrice * ri.qty) `price`,
group_concat(distinct po.id) `poIds`,
group_concat(distinct ri.id) `itemIds`,
min(ri.created) `created`
from receivingitem ri
inner join purchaseorder po on (po.id = ri.purchaseOrderId)
where ' . implode(' AND ', $where) . '
group by po.supplierId, ri.invoiceNo
order by ri.id desc
limit ' . ($pageNo - 1) * $pageSize . ', ' . $pageSize;
$rows = Dao::getResultsNative($sql, $params);
$stats = array();
$statsResult = Dao::getSingleResultNative('select found_rows()', array(), PDO::FETCH_NUM);
$stats['totalRows'] = intval($statsResult[0]);
$stats['pageSize'] = $pageSize;
$stats['pageNumber'] = $pageNo;
$stats['totalPages'] = intval(ceil($stats['totalRows'] / $stats['pageSize']));
$results['items'] = array();
foreach ($rows as $row) {
$pos = count($poIds = explode(',', $row['poIds'])) === 0 ? array() : PurchaseOrder::getAllByCriteria('id in (' . implode(',', array_fill(0, count($poIds), '?')) . ')', $poIds);
$results['items'][] = array('invoiceNo' => $row['invoiceNo'], 'supplier' => Supplier::get($row['supplierId'])->getJson(), 'created' => $row['created'], 'totalQty' => $row['qty'], 'totalPrice' => $row['price'], 'purchaseOrders' => array_map(create_function('$a', 'return $a->getJson();'), $pos), 'poIds' => explode(',', $row['poIds']), 'itemIds' => explode(',', $row['itemIds']));
}
$results['pageStats'] = $stats;
} catch (Exception $ex) {
$errors[] = $ex->getMessage();
}
$param->ResponseData = StringUtilsAbstract::getJson($results, $errors);
}
/**
* Getting the items
*
* @param unknown $sender
* @param unknown $param
* @throws Exception
*
*/
public function getItems($sender, $param)
{
$results = $errors = array();
try {
$pageNo = 1;
$pageSize = DaoQuery::DEFAUTL_PAGE_SIZE;
$class = trim($this->_focusEntity);
if (isset($param->CallbackParameter->pagination)) {
$pageNo = $param->CallbackParameter->pagination->pageNo;
$pageSize = $param->CallbackParameter->pagination->pageSize;
}
$serachCriteria = isset($param->CallbackParameter->searchCriteria) ? json_decode(json_encode($param->CallbackParameter->searchCriteria), true) : array();
$stats = array();
$where = array(1);
$params = array();
$noSearch = true;
foreach ($serachCriteria as $field => $value) {
if (is_array($value) && count($value) === 0 || is_string($value) && ($value = trim($value)) === '') {
continue;
}
$query = $class::getQuery();
switch ($field) {
case 'po.purchaseOrderNo':
$where[] = $field . " like ? ";
$params[] = '%' . $value . '%';
break;
case 'po.supplierRefNo':
$where[] = $field . " like ? ";
$params[] = '%' . $value . '%';
break;
case 'po.orderDate_from':
$where[] = 'po.orderDate >= ?';
$params[] = $value;
break;
case 'po.orderDate_to':
$where[] = 'po.orderDate <= ?';
$params[] = str_replace(' 00:00:00', ' 23:59:59', $value);
break;
case 'po.supplierIds':
if (count($value) > 0) {
$value = explode(',', $value);
$where[] = 'po.supplierId IN (' . implode(", ", array_fill(0, count($value), "?")) . ')';
$params = array_merge($params, $value);
}
break;
case 'po.status':
if (count($value) > 0) {
$where[] = 'po.status IN (' . implode(", ", array_fill(0, count($value), "?")) . ')';
$params = array_merge($params, $value);
}
break;
case 'po.active':
if (trim($value) !== '') {
$where[] = 'po.active = ?';
$params[] = trim($value);
}
break;
case 'rec_item.invoiceNo':
if (trim($value) !== '') {
$where[] = 'id in (select purchaseOrderId from receivingitem rec_item where po.id = rec_item.purchaseOrderId and rec_item.active =1 and rec_item.invoiceNo like ?)';
$params[] = '%' . trim($value) . '%';
}
break;
case 'pro.ids':
$value = explode(',', $value);
$query->eagerLoad("PurchaseOrder.items", 'inner join', 'po_item', 'po_item.purchaseOrderId = po.id and po_item.active = 1');
$where[] = 'po_item.productId in (' . implode(", ", array_fill(0, count($value), "?")) . ')';
$params = array_merge($params, $value);
break;
}
$noSearch = false;
}
$objects = PurchaseOrder::getAllByCriteria(implode(' AND ', $where), $params, false, $pageNo, $pageSize, array('po.id' => 'desc'), $stats);
$results['pageStats'] = $stats;
$results['items'] = array();
foreach ($objects as $obj) {
$PoId = $obj->getId();
$results['items'][] = array('totalProdcutCount' => $obj->getTotalProductCount(), 'item' => $obj->getJson());
}
} catch (Exception $ex) {
$errors[] = $ex->getMessage() . $ex->getTraceAsString();
}
$param->ResponseData = StringUtilsAbstract::getJson($results, $errors);
}