<pre name="code" class="sql"><pre name="code" class="sql">select
s.number,
(select text from t_dict_general where number=s.category)category,
(select text from t_dict_general where number=s.subCategory)subCategory,
s.stocktakingTime,
(select text from t_dict_general where number=s.state)state,
t.number taskNumber,
t.isSecondStocktaking secondStocktaking,
(select text from t_dict_general where number=t.state) taskState,
(
select ta.number from t_stocktaking_task_area ta
where ta.stocktakingTask=t.number
)warehouseAreaList,
(
select cell.number from t_stocktaking_task_user_defined_cell udcell,t_warehouse_cell cell
where udcell.stocktakingTask=t.number
and convert(SUBSTR(cell.number,1,19),SIGNED)>=convert(SUBSTR(udcell.cellNumStart,1,19),SIGNED)
and convert(SUBSTR(cell.number,1,19),SIGNED)<=convert(SUBSTR(udcell.cellNumEnd,1,19),SIGNED)
)warehouseCellList,
t_unit.name seller,
(
select detail.warehouseCell from t_stock_detail detail
where seller.seller=detail.seller
)sellerWarehouseCellList
from t_stocktaking s,t_stocktaking_task_employee e,t_stocktaking_task t,
t_stocktaking_task_user_defined_seller seller,
t_seller ,t_unit
<where>
t.stocktaking=s.number
and t.number=seller.stocktakingTask
and seller.seller=t_seller.number
and t_seller.unit=t_unit.number
and e.stocktakingTask=t.number
<if test="userCode!=null and userCode!=''">
and e.employee=#{userCode}
</if>
</where>
union
select
s.number,
(select text from t_dict_general where number=s.category)category,
(select text from t_dict_general where number=s.subCategory)subCategory,
s.stocktakingTime,
(select text from t_dict_general where number=s.state)state,
t.number taskNumber,
t.isSecondStocktaking secondStocktaking,
(select text from t_dict_general where number=t.state) taskState,
(
select ta.number from t_stocktaking_task_area ta
where ta.stocktakingTask=t.number
)warehouseAreaList,
(
select cell.number from t_stocktaking_task_user_defined_cell udcell,t_warehouse_cell cell
where udcell.stocktakingTask=t.number
and convert(SUBSTR(cell.number,1,19),SIGNED)>=convert(SUBSTR(udcell.cellNumStart,1,19),SIGNED)
and convert(SUBSTR(cell.number,1,19),SIGNED)<=convert(SUBSTR(udcell.cellNumEnd,1,19),SIGNED)
)warehouseCellList,
t_goods_sku.barCode sku,
(
select detail.warehouseCell from t_stock_detail detail
where sku.sku=detail.sku
)skuWarehouseCellList,
from t_stocktaking s,t_stocktaking_task_employee e,t_stocktaking_task t,
t_stocktaking_task_user_defined_sku sku,
t_goods_sku
<where>
t.stocktaking=s.number
and t.number=sku.stocktakingTask
and sku.sku =t_goods_sku.number
and e.stocktakingTask=t.number
<if test="userCode!=null and userCode!=''">
and e.employee=#{userCode}
</if>
</where>
union
select
s.number,
(select text from t_dict_general where number=s.category)category,
(select text from t_dict_general where number=s.subCategory)subCategory,
s.stocktakingTime,
(select text from t_dict_general where number=s.state)state,
scd.number taskNumber,
scd.isSecondStocktaking secondStocktaking,
(select text from t_dict_general where number=scd.state) taskState,
(
select ta.number from t_stocktaking_task_area ta
where ta.stocktakingTask=scd.number
)warehouseAreaList,
(
select cell.number from t_stocktaking_task_user_defined_cell udcell,t_warehouse_cell cell
where udcell.stocktakingTask=scd.number
and convert(SUBSTR(cell.number,1,19),SIGNED)>=convert(SUBSTR(udcell.cellNumStart,1,19),SIGNED)
and convert(SUBSTR(cell.number,1,19),SIGNED)<=convert(SUBSTR(udcell.cellNumEnd,1,19),SIGNED)
)warehouseCellList,
r.number firstRecord,
r.warehouseCell secondListWarehouseCell,
r.sku secondListSku
from t_stocktaking s,t_stocktaking_task_employee e,t_stocktaking_task t,t_stocktaking_task scd
t_stocktaking_task_record r
<where>
t.stocktaking=s.number
and scd.stocktaking=s.number
and e.stocktakingTask=t.number
and r.stocktakingTask=t.number
and r.stockQuantity!=r.stocktakingQuantity
and scd.isSecondStocktaking=1
<if test="userCode!=null and userCode!=''">
and e.employee=#{userCode}
</if>
</where>
