mysql语句集(一)
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), ); }