SELECT
UcenterTdouLog.user_id, UcenterTdouLog.subject, UcenterTdouLog.tdou,UcenterTdouLog.created,
UcenterTdouLog.out_id, ifnull(SaleOrder.order_sn, 0) AS order_sn,
(
SELECT
SUM(tdou)
FROM
ucenter_tdou_log AS a
WHERE
a.created <= UcenterTdouLog.created
AND a.user_id = UcenterTdouLog.user_id
GROUP BY
a.user_id
) AS tdou_total
FROM
ucenter_tdou_log AS UcenterTdouLog
LEFT JOIN sale_order AS SaleOrder ON SaleOrder.order_id = UcenterTdouLog.out_id
WHERE
1=1 %s
ORDER BY
UcenterTdouLog.user_id asc,UcenterTdouLog.created asc
收发存汇总
SELECT
GoodsSku.sku_id,
GoodsSku.barcode,
GoodsSku.sku_name,
GoodsTypeSpec1.spec_name AS sku_spec1,
GoodsTypeSpec2.spec_name AS sku_spec2,
GoodsCategory1.cat_name AS catName1,
GoodsCategory2.cat_name AS catName2,
GoodsCategory3.cat_name AS catName3,
GoodsUnit.sale_unit,
SellerBase.seller_name,
IFNULL((SELECT SUM(WmsBatchItem.sku_num) FROM wms_batch_base AS WmsBatchBase INNER JOIN wms_batch_item AS WmsBatchItem ON WmsBatchItem.batch_id = WmsBatchBase.batch_id WHERE WmsBatchItem.sku_id = GoodsSku.sku_id %s),0) AS qichuNum,
IFNULL((SELECT SUM(WmsBatchItem.sku_num * WmsBatchItem.sku_price ) FROM wms_batch_base AS WmsBatchBase INNER JOIN wms_batch_item AS WmsBatchItem ON WmsBatchItem.batch_id = WmsBatchBase.batch_id WHERE WmsBatchItem.sku_id = GoodsSku.sku_id %s),0) AS qichuPrice,
IFNULL((SELECT SUM(WmsBatchItem.sku_num) FROM wms_batch_base AS WmsBatchBase INNER JOIN wms_batch_item AS WmsBatchItem ON WmsBatchItem.batch_id = WmsBatchBase.batch_id WHERE WmsBatchItem.sku_id = GoodsSku.sku_id AND WmsBatchBase.type_id not in(02,05) %s),0) AS batchNum,
IFNULL((SELECT SUM(WmsBatchItem.sku_num * WmsBatchItem.sku_price ) FROM wms_batch_base AS WmsBatchBase INNER JOIN wms_batch_item AS WmsBatchItem ON WmsBatchItem.batch_id = WmsBatchBase.batch_id WHERE WmsBatchItem.sku_id = GoodsSku.sku_id AND WmsBatchBase.type_id not in(02,05) %s),0) AS batchPrice,
IFNULL((SELECT SUM(WmsBatchItem.sku_num) FROM wms_batch_base AS WmsBatchBase INNER JOIN wms_batch_item AS WmsBatchItem ON WmsBatchItem.batch_id = WmsBatchBase.batch_id WHERE WmsBatchItem.sku_id = GoodsSku.sku_id AND WmsBatchBase.type_id = 02 %s),0) AS allocBatchNum,
IFNULL((SELECT SUM(WmsBatchItem.sku_num * WmsBatchItem.sku_price) FROM wms_batch_base AS WmsBatchBase INNER JOIN wms_batch_item AS WmsBatchItem ON WmsBatchItem.batch_id = WmsBatchBase.batch_id WHERE WmsBatchItem.sku_id = GoodsSku.sku_id AND WmsBatchBase.type_id = 02 %s),0) AS allocBatchPrice,
IFNULL((SELECT SUM(WmsBatchItem.sku_num) FROM wms_batch_base AS WmsBatchBase INNER JOIN wms_batch_item AS WmsBatchItem ON WmsBatchItem.batch_id = WmsBatchBase.batch_id WHERE WmsBatchItem.sku_id = GoodsSku.sku_id AND WmsBatchBase.type_id = 05 %s),0) AS takeBatchNum,
IFNULL((SELECT SUM(WmsBatchItem.sku_num * WmsBatchItem.sku_price) FROM wms_batch_base AS WmsBatchBase INNER JOIN wms_batch_item AS WmsBatchItem ON WmsBatchItem.batch_id = WmsBatchBase.batch_id WHERE WmsBatchItem.sku_id = GoodsSku.sku_id AND WmsBatchBase.type_id = 05 %s),0) AS takeBatchPrice,
IFNULL((SELECT SUM(WmsOrderItem.sku_num) FROM wms_order_base AS WmsOrderBase INNER JOIN wms_order_item AS WmsOrderItem ON WmsOrderItem.order_id = WmsOrderBase.order_id WHERE WmsOrderItem.sku_id = GoodsSku.sku_id AND WmsOrderBase.type_id not in (01,05) %s),0) AS OrderNum,
IFNULL((SELECT SUM(WmsOrderItem.sku_num * WmsOrderItem.stock_price) FROM wms_order_base AS WmsOrderBase INNER JOIN wms_order_item AS WmsOrderItem ON WmsOrderItem.order_id = WmsOrderBase.order_id WHERE WmsOrderItem.sku_id = GoodsSku.sku_id AND WmsOrderBase.type_id not in (01,05) %s),0) AS OrderPrice,
IFNULL((SELECT SUM(WmsOrderItem.sku_num) FROM wms_order_base AS WmsOrderBase INNER JOIN wms_order_item AS WmsOrderItem ON WmsOrderItem.order_id = WmsOrderBase.order_id WHERE WmsOrderItem.sku_id = GoodsSku.sku_id AND WmsOrderBase.type_id = 01 %s),0) AS allocOrderNum,
IFNULL((SELECT SUM(WmsOrderItem.sku_num * WmsOrderItem.stock_price) FROM wms_order_base AS WmsOrderBase INNER JOIN wms_order_item AS WmsOrderItem ON WmsOrderItem.order_id = WmsOrderBase.order_id WHERE WmsOrderItem.sku_id = GoodsSku.sku_id AND WmsOrderBase.type_id = 01 %s), 0) AS allocOrderPrice,
IFNULL((SELECT SUM(WmsOrderItem.sku_num) FROM wms_order_base AS WmsOrderBase INNER JOIN wms_order_item AS WmsOrderItem ON WmsOrderItem.order_id = WmsOrderBase.order_id WHERE WmsOrderItem.sku_id = GoodsSku.sku_id AND WmsOrderBase.type_id = 05 %s),0) AS takeOrderNum,
IFNULL((SELECT SUM(WmsOrderItem.sku_num * WmsOrderItem.stock_price) FROM wms_order_base AS WmsOrderBase INNER JOIN wms_order_item AS WmsOrderItem ON WmsOrderItem.order_id = WmsOrderBase.order_id WHERE WmsOrderItem.sku_id = GoodsSku.sku_id AND WmsOrderBase.type_id = 05 %s), 0) AS takeOrderPrice
FROM
goods_sku AS GoodsSku
INNER JOIN goods_base AS GoodsBase ON GoodsSku.goods_id = GoodsBase.goods_id
LEFT JOIN goods_category AS GoodsCategory1 ON GoodsCategory1.cat_id = GoodsBase.cat_id1
LEFT JOIN goods_category AS GoodsCategory2 ON GoodsCategory2.cat_id = GoodsBase.cat_id2
LEFT JOIN goods_category AS GoodsCategory3 ON GoodsCategory3.cat_id = GoodsBase.cat_id3
LEFT JOIN goods_unit AS GoodsUnit ON GoodsUnit.unit_id = GoodsBase.unit_id
LEFT JOIN seller_base AS SellerBase ON SellerBase.seller_id = GoodsBase.seller_id
LEFT JOIN goods_type_spec AS GoodsTypeSpec1 ON GoodsTypeSpec1.spec_id = GoodsSku.sku_spec1
LEFT JOIN goods_type_spec AS GoodsTypeSpec2 ON GoodsTypeSpec2.spec_id = GoodsSku.sku_spec2
LIMIT %d,%d
/**
* 收发存汇总报表
* @param $houseID
* @param $startDate
* @param $endDate
* @param $page
* @param int $size
* @return array
*/
public function fetchOrderBatchAllReport($houseID, $startDate, $endDate, $page, $size = 20)
{
$database = $this->database();
$where1 = '';
$where2 = '';
$where3 = '';
$where4 = '';
$where5 = '';
$where6 = '';
$where7 = '';
$where8 = '';
$where9 = '';
$where10 = '';
$where11 = '';
$where12 = '';
$where13 = '';
$where14 = '';
$params1 = array();
$params2 = array();
$params3 = array();
$params4 = array();
$params5 = array();
$params6 = array();
$params7 = array();
$params8 = array();
$params9 = array();
$params10 = array();
$params11 = array();
$params12 = array();
$params13 = array();
$params14 = array();
if ($houseID) {
$where1 .= ' AND WmsBatchBase.house_id = ?';
$where2 .= ' AND WmsBatchBase.house_id = ?';
$where3 .= ' AND WmsBatchBase.house_id = ?';
$where4 .= ' AND WmsBatchBase.house_id = ?';
$where5 .= ' AND WmsBatchBase.house_id = ?';
$where6 .= ' AND WmsBatchBase.house_id = ?';
$where7 .= ' AND WmsBatchBase.house_id = ?';
$where8 .= ' AND WmsBatchBase.house_id = ?';
$where9 .= ' AND WmsOrderBase.house_id = ?';
$where10 .= ' AND WmsOrderBase.house_id = ?';
$where11 .= ' AND WmsOrderBase.house_id = ?';
$where12 .= ' AND WmsOrderBase.house_id = ?';
$where13 .= ' AND WmsOrderBase.house_id = ?';
$where14.= ' AND WmsOrderBase.house_id = ?';
array_push($params1, $houseID);
array_push($params2, $houseID);
array_push($params3, $houseID);
array_push($params4, $houseID);
array_push($params5, $houseID);
array_push($params6, $houseID);
array_push($params7, $houseID);
array_push($params8, $houseID);
array_push($params9, $houseID);
array_push($params10, $houseID);
array_push($params11, $houseID);
array_push($params12, $houseID);
array_push($params13, $houseID);
array_push($params14, $houseID);
}
if ($startDate && $endDate) {
$started = strtotime($startDate);
$ended = strtotime($endDate);
$where1 .= ' AND WmsBatchBase.created <= ?';
$where2 .= ' AND WmsBatchBase.created <= ?';
$where3 .= ' AND WmsBatchBase.created >= ? AND WmsBatchBase.created - (24*60*60) <= ? ';
$where4 .= ' AND WmsBatchBase.created >= ? AND WmsBatchBase.created - (24*60*60) <= ? ';
$where5 .= ' AND WmsBatchBase.created >= ? AND WmsBatchBase.created - (24*60*60) <= ? ';
$where6 .= ' AND WmsBatchBase.created >= ? AND WmsBatchBase.created - (24*60*60) <= ? ';
$where7 .= ' AND WmsBatchBase.created >= ? AND WmsBatchBase.created - (24*60*60) <= ? ';
$where8 .= ' AND WmsBatchBase.created >= ? AND WmsBatchBase.created - (24*60*60) <= ? ';
$where9 .= ' AND WmsOrderBase.created >= ? AND WmsOrderBase.created - (24*60*60) <= ? ';
$where10 .= ' AND WmsOrderBase.created >= ? AND WmsOrderBase.created - (24*60*60) <= ? ';
$where11 .= ' AND WmsOrderBase.created >= ? AND WmsOrderBase.created - (24*60*60) <= ? ';
$where12 .= ' AND WmsOrderBase.created >= ? AND WmsOrderBase.created - (24*60*60) <= ? ';
$where13 .= ' AND WmsOrderBase.created >= ? AND WmsOrderBase.created - (24*60*60) <= ? ';
$where14 .= ' AND WmsOrderBase.created >= ? AND WmsOrderBase.created - (24*60*60) <= ? ';
array_push($params1, $started);
array_push($params2, $started);
array_push($params3, $started, $ended);
array_push($params4, $started, $ended);
array_push($params5, $started, $ended);
array_push($params6, $started, $ended);
array_push($params7, $started, $ended);
array_push($params8, $started, $ended);
array_push($params9, $started, $ended);
array_push($params10,$started, $ended);
array_push($params11,$started, $ended);
array_push($params12,$started, $ended);
array_push($params13,$started, $ended);
array_push($params14,$started, $ended);
}
$params =array_merge($params1,$params2,$params3,$params4,$params5,$params6,$params7,$params8,$params9,$params10,$params11,$params12,$params13,$params14);
return array(
$database->query(sprintf(self::ORDER_BATCH_ALL_REPORT_SQL, $where1, $where2, $where3, $where4, $where5, $where6, $where7, $where8, $where9, $where10, $where11, $where12,$where13,$where14, ($page - 1) * $size, $size), $params, DatabaseProvider::FETCH_STYLE_OBJ),
$database->query(self::ORDER_BATCH_ALL_REPORT_COUNT_SQL),
);
}