private function _getSeries($groupFrame, $from, $to, $statusId = array())
{
$select = array();
foreach ($groupFrame as $index => $time) {
$select[] = 'sum(if((created >= "' . $time['from'] . '" && created < "' . $time['to'] . '"), 1 , 0)) `' . $index . '`';
}
$where = array('active = 1');
if (count($statusId) > 0) {
$where[] = 'statusId in (' . implode(', ', $statusId) . ')';
}
$sql = "select " . implode(', ', $select) . ' from `order` where ' . implode(' AND ', $where) . ' and created >=? and created < ?';
$row = Dao::getSingleResultNative($sql, array(trim($from), trim($to)), PDO::FETCH_NUM);
$return = array();
foreach ($row as $col) {
$return[] = intval($col);
}
return $return;
}
/**
* 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);
}
/**
* Getter for totalReceivedCount
*
* @return int
*/
public function getTotalReceivedCount()
{
$sql = "select sum(qty) `qty` from receivingitem where active = 1 and purchaseOrderId = ? ";
$result = Dao::getSingleResultNative($sql, array($this->getId()));
return intval($result['qty']);
}
private function _getSeries($groupFrame, $from, $to, array $productIds, $showPrice = false)
{
$select = array();
foreach ($groupFrame as $index => $time) {
$select[] = 'sum(if((created >= "' . $time['from'] . '" && created < "' . $time['to'] . '"), ' . ($showPrice === true ? 'totalPrice' : 'qtyOrdered') . ' , 0)) `' . $index . '`';
}
$where = array('active = 1');
if (count($productIds) > 0) {
$where[] = 'productId in (' . implode(', ', $productIds) . ')';
}
$sql = "select " . implode(', ', $select) . ' from `orderitem` where ' . implode(' AND ', $where) . ' and created >=? and created <= ?';
$row = Dao::getSingleResultNative($sql, array(trim($from), trim($to)), PDO::FETCH_NUM);
$return = array();
foreach ($row as $col) {
$return[] = $showPrice === true ? (double) $col : intval($col);
}
return $return;
}