ldj/epickx/BLL/wms/data/WmsOutPickRequest_Imp.cs

576 lines
35 KiB
C#
Raw Permalink Blame History

This file contains invisible Unicode characters

This file contains invisible Unicode characters that are indistinguishable to humans but may be processed differently by a computer. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

/// <summary>
///INTERFACE IMPLIMENT FOR TABLE t_wmsOutPickRequest
///By wm with codesmith.
///on 05/22/2017
/// </summary>
using System;
using System.Data.SqlClient;
using DeiNiu.Utils;
namespace DeiNiu.wms.Data.Model
{
[Serializable] class WmsOutPickRequest_Imp : WmsOutPickRequest_base_Imp
{
protected override void CmdPrepare(SqlCommand sqlCmd)
{
base.CmdPrepare(sqlCmd);
WmsOutPickRequest mObj = ( WmsOutPickRequest)modelObj;
switch (_cust_op_flag)
{
case 99: //query with dic
int lineId = WmsConstants.WAVE_LINE;
string sales = WmsConstants.WAVE_SALE;
string whsql = mObj.CmdParameters[0].ToString();
if(lineId >0)
{
whsql += " and exists(select 1 from t_tmsLineDetail where custId =r.customerId and lineid =@lineId ) ";
sqlCmd.Parameters.AddWithValue("@lineId", lineId);
}
if(!string.IsNullOrEmpty(sales))
{
whsql += " and exists(select 1 from t_wmsOutRequest where pickOrderNo = r.pickorderNo and salesperson =@sales ) ";
sqlCmd.Parameters.AddWithValue("@sales", sales);
}
_strSql = "SELECT *,ROW_NUMBER() OVER (ORDER BY PRIORITY DESC, ID) as sortNo FROM t_wmsOutPickRequest r WHERE DR = 1 " + whsql;
_strSql = "SELECT * FROM (" + _strSql + " )AS SORTEDTB WHERE sortNo BETWEEN @START AND @END" + " ORDER BY PRIORITY DESC,ID ";
_strSql += ";SELECT COUNT(*) FROM t_wmsOutPickRequest r WHERE DR = 1 " + whsql;
sqlCmd.CommandText = _strSql;
sqlCmd.Parameters.AddWithValue("@START", this._rownumStart);
sqlCmd.Parameters.AddWithValue("@END", this._rownumEnd);
sqlCmd.CommandText = _strSql;
break;
case 100: //by orderNo
_strSql = "SELECT * FROM t_wmsOutPickRequest WHERE PICKORDERNO = @PICKORDERNO";
sqlCmd.CommandText = _strSql;
sqlCmd.Parameters.AddWithValue("@PICKORDERNO", mObj.CmdParameters[0]);
break;
case 200: //get order out detail by pick ORDERNO
_strSql = "SELECT * FROM v_stockOutPickDetail WHERE PICKORDERNO = @ORDERNO";
sqlCmd.CommandText = _strSql;
sqlCmd.Parameters.AddWithValue("@ORDERNO", mObj.CmdParameters[0]);
break;
case 210: //get order out detail by pick ORDERNO
_strSql = "SELECT * FROM v_stockOutPickDetail WHERE PICKORDERNO = @ORDERNO";
sqlCmd.CommandText = _strSql;
sqlCmd.Parameters.AddWithValue("@ORDERNO", mObj.CmdParameters[0]);
break;
case 300: //get pickrequest by ordertype,satus
// _strSql = "SELECT * FROM [t_wmsoutdetail] a where exists(select 1 from t_wmsoutrequest where orderNo = a.orderNo and orderType =@orderType and status =@status)";
_strSql = "SELECT * FROM [t_wmsOutPickRequest] "
// + " where orderType =@orderType and state =@state order by priority desc,custAddress, createtime "; //按创建时间排序fifo保证缺零货的订单补货后会优先进下个波次。 //bulkcnt desc" 按散货数量排序 ,先出散货多的,这样为了均衡复合台工作量,由于复合台和拣选单子是随机组合,所有没有必要了 ;
+ " where orderType =@orderType and state =@state order by priority desc , createtime "; //按创建时间排序fifo保证缺零货的订单补货后会优先进下个波次。 //bulkcnt desc" 按散货数量排序 ,先出散货多的,这样为了均衡复合台工作量,由于复合台和拣选单子是随机组合,所有没有必要了 ;
sqlCmd.Parameters.AddWithValue("@orderType", mObj.CmdParameters[0]);
sqlCmd.Parameters.AddWithValue("@state", mObj.CmdParameters[1]);
sqlCmd.CommandText = _strSql;
break;
case 301: //get pickrequest by ordertype,satus
// _strSql = "SELECT * FROM [t_wmsoutdetail] a where exists(select 1 from t_wmsoutrequest where orderNo = a.orderNo and orderType =@orderType and status =@status)";
_strSql = "SELECT * FROM [t_wmsOutPickRequest] r "
// + " where orderType =@orderType and state =@state order by priority desc,custAddress, createtime "; //按创建时间排序fifo保证缺零货的订单补货后会优先进下个波次。 //bulkcnt desc" 按散货数量排序 ,先出散货多的,这样为了均衡复合台工作量,由于复合台和拣选单子是随机组合,所有没有必要了 ;
+ " where orderType =@orderType and state =@state " ; //按创建时间排序fifo保证缺零货的订单补货后会优先进下个波次。 //bulkcnt desc" 按散货数量排序 ,先出散货多的,这样为了均衡复合台工作量,由于复合台和拣选单子是随机组合,所有没有必要了 ;
lineId = Convert.ToInt16(mObj.CmdParameters[2]);
sales = mObj.CmdParameters[3].ToString();
if(lineId >0)
{
_strSql += " and exists(select 1 from t_tmsLineDetail where custId =r.customerId and lineid =@lineId ) ";
sqlCmd.Parameters.AddWithValue("@lineId", lineId);
}
if(!string.IsNullOrEmpty(sales))
{
_strSql += " and exists(select 1 from t_wmsOutRequest where pickOrderNo = r.pickorderNo and salesperson =@sales ) ";
sqlCmd.Parameters.AddWithValue("@sales", sales);
}
_strSql += " order by priority desc , createtime ";
sqlCmd.Parameters.AddWithValue("@orderType", mObj.CmdParameters[0]);
sqlCmd.Parameters.AddWithValue("@state", mObj.CmdParameters[1]);
sqlCmd.CommandText = _strSql;
break;
case 302: //get pickrequest for stock reduct
// _strSql = "SELECT * FROM [t_wmsoutdetail] a where exists(select 1 from t_wmsoutrequest where orderNo = a.orderNo and orderType =@orderType and status =@status)";
_strSql = "SELECT * FROM [t_wmsOutPickRequest] "
// + " where orderType =@orderType and state =@state order by priority desc,custAddress, createtime "; //按创建时间排序fifo保证缺零货的订单补货后会优先进下个波次。 //bulkcnt desc" 按散货数量排序 ,先出散货多的,这样为了均衡复合台工作量,由于复合台和拣选单子是随机组合,所有没有必要了 ;
+ " where orderType =@orderType and state =@state order by priority desc , createtime "; //按创建时间排序fifo保证缺零货的订单补货后会优先进下个波次。 //bulkcnt desc" 按散货数量排序 ,先出散货多的,这样为了均衡复合台工作量,由于复合台和拣选单子是随机组合,所有没有必要了 ;
sqlCmd.Parameters.AddWithValue("@orderType", mObj.CmdParameters[0]);
sqlCmd.Parameters.AddWithValue("@state", mObj.CmdParameters[1]);
sqlCmd.CommandText = _strSql;
break;
case 400: //update order status and bulkPickState by waveOrder
//update bulkPickState
_strSql = "UPDATE t_wmsOutPickRequest SET bulkPickState =@bulkSTATE, waveEnd = getDate() ,operater =@operater,lastmodified = getdate() WHERE WAVEORDER =@WAVEORDER;";
_strSql += " UPDATE t_wmsOutPickDetail SET bulkPickState =@bulkSTATE ,operater =@operater,lastmodified = getdate() from t_wmsOutPickDetail a "
+ " WHERE exists(select 1 from t_wmsOutPickRequest b where a.pickOrderNo = b.pickOrderNo and b.WAVEORDER =@WAVEORDER);";
//update state when bulkCount = count 全散货
_strSql += " UPDATE t_wmsOutPickDetail SET state = @pickSTATE ,operater =@operater,lastmodified = getdate() from t_wmsOutPickDetail a "
+ " WHERE exists(select 1 from t_wmsOutPickRequest b where a.pickOrderNo = b.pickOrderNo and b.WAVEORDER =@WAVEORDER ) and bulkcount =count;";
//_strSql += "UPDATE t_wmsOutPickRequest SET state =@requestSTATE from t_wmsOutPickRequest a WHERE WAVEORDER =@WAVEORDER "
// + " and( not exists(select 1 from t_wmsOutPickDetail b where a.pickOrderNo = b.pickOrderNo and b.bulkcount !=b.count ));";
_strSql += "UPDATE t_wmsOutPickRequest SET state =@requestSTATE ,operater =@operater,lastmodified = getdate() WHERE WAVEORDER =@WAVEORDER and batchPickState in(-1,2);";
//_strSql += " UPDATE t_wmsOutRequest SET STATE =@requestSTATE from t_wmsOutRequest a "
// + " WHERE EXISTS(select 1 from [v_stockOutRequestPick] where pickOrderNo= a.pickOrderNo and WAVEORDER =@WAVEORDER and bulkcount =count);";
//_strSql += " UPDATE t_wmsOutRequest SET STATE =select state from t_wmsOutPickRequest where pickOrderNo =a.pickorderNo where WAVEORDER =@WAVEORDER ) from t_wmsOutRequest a ";
_strSql += " UPDATE t_wmsOutRequest SET STATE = b.state ,operater =@operater,lastmodified = getdate() from t_wmsOutRequest a ,t_wmsOutPickRequest b where a.pickOrderNo = b.pickorderNo and WAVEORDER =@WAVEORDER";
// _strSql += "UPDATE t_wmsOutRequest SET STATE =@requestSTATE where orderno in( select orderno from [v_stockOutRequestPick] where WAVEORDER =@WAVEORDER and bulkcount =count)";
sqlCmd.CommandText = _strSql;
sqlCmd.Parameters.AddWithValue("@WAVEORDER", mObj.CmdParameters[0]);
sqlCmd.Parameters.AddWithValue("@bulkSTATE", (int)DeiNiu.Utils.enumOutStockPickStatus.);
sqlCmd.Parameters.AddWithValue("@requestSTATE", (int)DeiNiu.Utils.enumOutStockRequestStatus.);
sqlCmd.Parameters.AddWithValue("@pickSTATE", (int)DeiNiu.Utils.enumOutStockDetailStatus.);
sqlCmd.Parameters.AddWithValue("@operater", mObj.CmdParameters[1]);
break;
case 401: //update order status and bulkPickState by pickorder
//update bulkPickState
_strSql = "UPDATE t_wmsOutPickRequest SET bulkPickState =@bulkSTATE, waveEnd = getDate() ,operater =@operater,lastmodified = getdate() WHERE WAVEORDER =@WAVEORDER;";
_strSql += " UPDATE t_wmsOutPickDetail SET bulkPickState =@bulkSTATE ,operater =@operater,lastmodified = getdate() from t_wmsOutPickDetail a "
+ " WHERE exists(select 1 from t_wmsOutPickRequest b where a.pickOrderNo = b.pickOrderNo and b.pickOrderNo =@pickOrderNo);";
//update state when bulkCount = count 全散货
_strSql += " UPDATE t_wmsOutPickDetail SET state = @pickSTATE ,operater =@operater,lastmodified = getdate() from t_wmsOutPickDetail a "
+ " WHERE exists(select 1 from t_wmsOutPickRequest b where a.pickOrderNo = b.pickOrderNo and b.pickOrderNo =@pickOrderNo ) and bulkcount =count;";
//_strSql += "UPDATE t_wmsOutPickRequest SET state =@requestSTATE from t_wmsOutPickRequest a WHERE WAVEORDER =@WAVEORDER "
// + " and( not exists(select 1 from t_wmsOutPickDetail b where a.pickOrderNo = b.pickOrderNo and b.bulkcount !=b.count ));";
_strSql += "UPDATE t_wmsOutPickRequest SET state =@requestSTATE ,operater =@operater,lastmodified = getdate() WHERE pickOrderNo =@pickOrderNo and batchPickState in(-1,2);";
//_strSql += " UPDATE t_wmsOutRequest SET STATE =@requestSTATE from t_wmsOutRequest a "
// + " WHERE EXISTS(select 1 from [v_stockOutRequestPick] where pickOrderNo= a.pickOrderNo and WAVEORDER =@WAVEORDER and bulkcount =count);";
//_strSql += " UPDATE t_wmsOutRequest SET STATE =select state from t_wmsOutPickRequest where pickOrderNo =a.pickorderNo where WAVEORDER =@WAVEORDER ) from t_wmsOutRequest a ";
_strSql += " UPDATE t_wmsOutRequest SET STATE = b.state ,operater =@operater,lastmodified = getdate() from t_wmsOutRequest a ,t_wmsOutPickRequest b where a.pickOrderNo = b.pickorderNo and pickOrderNo =@pickOrderNo";
// _strSql += "UPDATE t_wmsOutRequest SET STATE =@requestSTATE where orderno in( select orderno from [v_stockOutRequestPick] where WAVEORDER =@WAVEORDER and bulkcount =count)";
sqlCmd.CommandText = _strSql;
sqlCmd.Parameters.AddWithValue("@pickOrderNo", mObj.CmdParameters[0]);
sqlCmd.Parameters.AddWithValue("@bulkSTATE", (int)DeiNiu.Utils.enumOutStockPickStatus.);
sqlCmd.Parameters.AddWithValue("@requestSTATE", (int)DeiNiu.Utils.enumOutStockRequestStatus.);
sqlCmd.Parameters.AddWithValue("@pickSTATE", (int)DeiNiu.Utils.enumOutStockDetailStatus.);
sqlCmd.Parameters.AddWithValue("@operater", mObj.CmdParameters[1]);
break;
case 410: //进入波次拣选更新订单和明细bulkStatus
_strSql = "UPDATE t_wmsOutPickRequest SET bulkPickState =@bulkSTATE, waveStart = getDate(),operater =@operater,lastmodified = getdate() WHERE pickOrderNo =@pickOrderNo;";
_strSql += " UPDATE t_wmsOutPickDetail SET bulkPickState =@bulkSTATE ,operater =@operater,lastmodified = getdate() where pickOrderNo =@pickOrderNo and (bulkCount>0 or bulkPickState >=0)"; //只更新有零货的记录
sqlCmd.CommandText = _strSql;
sqlCmd.Parameters.AddWithValue("@pickOrderNo", mObj.CmdParameters[0]);
sqlCmd.Parameters.AddWithValue("@bulkSTATE", mObj.CmdParameters[1]);
sqlCmd.Parameters.AddWithValue("@operater", mObj.CmdParameters[2]);
break;
case 420: //进入波次拣选更新订单和明细batchPickStatus
_strSql = "UPDATE t_wmsOutPickRequest SET batchPickState =@batchSTATE, waveStart = getDate(),operater =@operater ,lastmodified = getdate() WHERE pickOrderNo =@pickOrderNo;";
_strSql += " UPDATE t_wmsOutPickDetail SET batchPickState =@batchSTATE,operater =@operater,lastmodified = getdate() where pickOrderNo =@pickOrderNo and (batchPickState >=0 or count -bulkCount > 0)"; //只更新有整货的记录
sqlCmd.CommandText = _strSql;
sqlCmd.Parameters.AddWithValue("@pickOrderNo", mObj.CmdParameters[0]);
sqlCmd.Parameters.AddWithValue("@batchSTATE", mObj.CmdParameters[1]);
sqlCmd.Parameters.AddWithValue("@operater", mObj.CmdParameters[2]);
break;
case 430: // 更新订单和明细 orderStatus, detailStatus
_strSql = "UPDATE t_wmsOutPickRequest SET state =@orderState,operater =@operater ,lastmodified = getdate() WHERE pickOrderNo =@pickOrderNo";
_strSql += "; UPDATE t_wmsOutPickDetail SET state =@detailState,operater =@operater,lastmodified = getdate() where pickOrderNo =@pickOrderNo ";
_strSql += "; UPDATE t_wmsOutRequest SET state =@orderState,operater =@operater,lastmodified = getdate() where pickOrderNo =@pickOrderNo ";
sqlCmd.CommandText = _strSql;
sqlCmd.Parameters.AddWithValue("@pickOrderNo", mObj.CmdParameters[0]);
sqlCmd.Parameters.AddWithValue("@orderState", mObj.CmdParameters[1]);
sqlCmd.Parameters.AddWithValue("@detailState", mObj.CmdParameters[2]);
sqlCmd.Parameters.AddWithValue("@operater", mObj.CmdParameters[3]);
break;
case 500: //判断是否有正在拣选数据
_strSql = "SELECT top 1 1 FROM t_wmsOutPickLable_tmp ";
sqlCmd.CommandText = _strSql;
break;
case 550: //判断是否有待拣选数据
_strSql = "SELECT top 1 1 FROM t_wmsOutPickRequest where state= @state ";
sqlCmd.CommandText = _strSql;
sqlCmd.Parameters.AddWithValue("@state",DeiNiu.Utils.enumOutStockRequestStatus.);
break;
case 600: //query with orderNo or pickOrderNo
DeiNiu.Utils.enumOutOrderType type = (DeiNiu.Utils.enumOutOrderType)mObj.CmdParameters[0];
_strSql = "SELECT top 500 * FROM v_stockOutRequestDetail where 1=1 ";
sqlCmd.Parameters.AddWithValue("@orderNo", mObj.CmdParameters[1]);
if (type == Utils.enumOutOrderType.)
{
_strSql += " and orderNo= @orderNo ";
}
else
{
_strSql = "SELECT top 500 * FROM v_stockOutRequestPick where 1=1 "
+ " and pickOrderNo= @pickOrderNo ;"
+ _strSql
+ " and pickOrderNo= @pickOrderNo ;"
+ " select top 500 * from v_reportStockRecord where orderno=@pickOrderNo";
sqlCmd.Parameters.AddWithValue("@pickOrderNo", mObj.CmdParameters[1]);
}
sqlCmd.CommandText = _strSql;
break;
case 700: //get pick details by waveno
_strSql = "SELECT * from [v_stockOutWavePickDetail] where waveOrder = @waveOrder ";
sqlCmd.CommandText = _strSql;
sqlCmd.Parameters.AddWithValue("@waveOrder", mObj.CmdParameters[0]);
break;
case 710: //get bulk pick details in current wave
_strSql = "SELECT * FROM v_stockOutBulkInCurrentWave ";
sqlCmd.CommandText = _strSql;
break;
case 800: //取零货待复核订单
// _strSql = "SELECT * FROM [t_wmsOutPickDetail] a where exists(select 1 from t_wmsOutPickRequest where pickOrderNo = a.pickOrderNo and desk =@desk and (state = 6 or state =7 )) order by orderNo; ";
_strSql = "select * from v_stockOutPickDetailValidation where desk=@desk ";
_strSql += " and orderState <= " + (int)DeiNiu.Utils.enumOutStockPickStatus.;
_strSql += ";";
//播种数据
/*
* _strSql += "; SELECT seedsPickNo, goodsId,goodsName, batch, SUM(bulkCount) AS bulkcount, orderState, desk"
+ " FROM dbo.[v_stockOutPickDetailValidation] "
+ " WHERE (seedsPickNo IS NOT NULL) and desk=@desk and orderState <= " + (int)DeiNiu.Utils.enumOutStockPickStatus.复核完成
+ " GROUP BY seedsPickNo, goodsId,goodsName, batch, orderState, desk ";
*/
_strSql += " select * from [v_stockOutPickDetailSeeds] where desk=@desk ";
_strSql += " and orderState <= " + (int)DeiNiu.Utils.enumOutStockPickStatus.;
/*
#if DEBUG
_strSql += " or desk >0) and orderState <= " + (int)DeiNiu.Utils.enumOutStockPickStatus.复核完成; ; //TODO: remove desk >0,test only
#else
_strSql += " ) and orderState <= " + (int)DeiNiu.Utils.enumOutStockPickStatus.复核完成;
#endif
*/
sqlCmd.CommandText = _strSql;
sqlCmd.Parameters.AddWithValue("@desk", mObj.CmdParameters[0]);
break;
case 810: //get original out request details by pickorder
_strSql = "SELECT * from [v_stockOutDetail_wms] v where exists (select 1 from t_wmsOutRequest where orderNo = v.orderNo and pickOrderNo =@pickOrderNo ) ";
sqlCmd.CommandText = _strSql;
sqlCmd.Parameters.AddWithValue("@pickOrderNo", mObj.CmdParameters[0]);
break;
case 900: //保存复核用数据
//_strSql = " insert into t_wmsOutPickDetail_tmp select * from t_wmsOutPickDetail a where "
// +" exists(select 1 from t_wmsOutPickRequest where pickOrderNo = a.pickOrderNo and waveOrder = @waveNo)"
_strSql = " insert into t_wmsOutPickDetail_tmp "
+ " select a.desk,a.bulkPickState as orderState,a.waveOrder,a.seedsPickNo,a.seedsPort,a.seedsLabelId,b.* "
+ " from t_wmsOutPickRequest a left outer join t_wmsOutPickDetail b on a.pickOrderNo = b.pickOrderNo "
+ " where waveOrder = @waveNo "
+ " and b.bulkCount > 0"
+ " and not exists (select 1 from t_wmsOutPickDetail_tmp where id = b.id); ";
sqlCmd.CommandText = _strSql;
sqlCmd.Parameters.AddWithValue("@waveNo", mObj.CmdParameters[0]);
break;
case 1000: //取待复核订单明细
// _strSql = "SELECT * FROM [t_wmsOutPickDetail] a where exists(select 1 from t_wmsOutPickRequest where pickOrderNo = a.pickOrderNo and desk =@desk and (state = 6 or state =7 )) order by orderNo; ";
_strSql = "select * from t_wmsOutPickDetail_tmp where pickOrder=@pickOrder;";
sqlCmd.CommandText = _strSql;
sqlCmd.Parameters.AddWithValue("@pickOrder", mObj.CmdParameters[0]);
break;
case 1100: //get pick details by pickOrderNo
_strSql = "SELECT * from [v_stockOutWavePickDetail] where pickOrderNo = @pickOrderNo ";
sqlCmd.CommandText = _strSql;
sqlCmd.Parameters.AddWithValue("@pickOrderNo", mObj.CmdParameters[0]);
break;
case 1101: //get seeds data for light
_strSql = " select seedsLabelId as ele_lab_id,1 as ele_address, seedsPort as port,bulkCount as pick_count,"+
" bulkcount - confirmedCount as send_count,desk as color,desk as ele_order ";
_strSql += " from t_wmsOutPickDetail_tmp";
_strSql += " where seedsPickNo = @seedsPickNo";
_strSql += " and goodsId =@goodsId and batch =@batch";
_strSql += " and bulkcount - confirmedCount > 0";
//取分播拣货单列表,实时数据
_strSql += "; select * from v_stockOutPickDetailValidation ";
_strSql += " where seedsPickNo = @seedsPickNo";
_strSql += " and goodsId =@goodsId and batch =@batch";
// _strSql += " and bulkcount - confirmedCount > 0";
sqlCmd.CommandText = _strSql;
sqlCmd.Parameters.AddWithValue("@seedsPickNo", mObj.CmdParameters[0]);
sqlCmd.Parameters.AddWithValue("@goodsId", mObj.CmdParameters[1]);
sqlCmd.Parameters.AddWithValue("@batch", mObj.CmdParameters[2]);
break;
case 1102:
// 某商品剩余待分播数量
_strSql = "select count(*) as cnt from v_stockOutPickDetailValidation ";
_strSql += " where seedsPickNo = @seedsPickNo";
_strSql += " and goodsId =@goodsId and batch =@batch";
_strSql += " and bulkPickState < @orderState";
sqlCmd.CommandText = _strSql;
sqlCmd.Parameters.AddWithValue("@seedsPickNo", mObj.CmdParameters[0]);
sqlCmd.Parameters.AddWithValue("@goodsId", mObj.CmdParameters[1]);
sqlCmd.Parameters.AddWithValue("@batch", mObj.CmdParameters[2]);
sqlCmd.Parameters.AddWithValue("@orderState",(int)DeiNiu.Utils.enumOutStockPickStatus. );
break;
case 1103:
// 待分播数量
_strSql = "select count(*) as cnt from v_stockOutPickDetailValidation ";
_strSql += " where seedsPickNo = @seedsPickNo";
_strSql += " and orderState < @orderState";
sqlCmd.CommandText = _strSql;
sqlCmd.Parameters.AddWithValue("@seedsPickNo", mObj.CmdParameters[0]);
sqlCmd.Parameters.AddWithValue("@orderState", (int)DeiNiu.Utils.enumOutStockPickStatus.);
break;
case 1104:
//播种详细
_strSql = "select * from v_stockOutPickDetailValidation ";
_strSql += " where seedsPickNo = @seedsPickNo";
 
sqlCmd.CommandText = _strSql;
sqlCmd.Parameters.AddWithValue("@seedsPickNo", mObj.CmdParameters[0]); 
break;
case 1200: //删除复核临时数据 pickOrderNo
_strSql = "DELETE FROM t_wmsOutPickDetail_tmp where pickOrderNo=@pickOrder; ";
sqlCmd.CommandText = _strSql;
sqlCmd.Parameters.AddWithValue("@pickOrder", mObj.CmdParameters[0]);
break;
case 1300: //get validation exception list
_strSql = "SELECT * FROM v_stockOutValidationException where bulkPickState =@state order by waveOrder,desk desc";
sqlCmd.CommandText = _strSql;
sqlCmd.Parameters.AddWithValue("@state", mObj.CmdParameters[0]);
break;
case 1400: // get validation exception list with solution
_strSql = "SELECT * FROM v_stockOutValidationException where bulkPickState =" + (int)DeiNiu.Utils.enumOutStockPickStatus.;
/*
#if DEBUG
_strSql +=" and ( desk=@desk or desk >0) order by waveOrder desc"; //TODO: remove desk >0,test only
#else
_strSql += " and desk=@desk order by waveOrder desc";
#endif
*/
_strSql += " and desk=@desk order by waveOrder desc";
// _strSql += " order by waveOrder desc";
sqlCmd.CommandText = _strSql;
sqlCmd.Parameters.AddWithValue("@desk", mObj.CmdParameters[0]);
break;
case 1500: //get batch out orders by status
_strSql = "SELECT * FROM [t_wmsOutPickRequest] where state =@status and batchpickstate >=@batchpickstate1 and batchpickstate <=@batchpickstate2 order by waveOrder desc";
sqlCmd.CommandText = _strSql;
sqlCmd.Parameters.AddWithValue("@status", mObj.CmdParameters[0]);
sqlCmd.Parameters.AddWithValue("@batchpickstate1", mObj.CmdParameters[1]);
sqlCmd.Parameters.AddWithValue("@batchpickstate2", mObj.CmdParameters[2]);
break;
case 1510: //get today's rep order details by order type
_strSql = "SELECT * FROM [t_wmsoutpickdetail] a where 1=1 "
+ " and exists(select 1 from [t_wmsOutPickRequest] where pickorderNo = a.pickorderNo and orderType =@orderType and orderDate =getDate())"
+ " and batchpickstate <@pickStatus" ;//+ (int)Utils.enumOutStockPickStatus.复核完成; //复核完成=完成零货上架
sqlCmd.Parameters.AddWithValue("@orderType",(int) Utils.enumOutOrderType.);
sqlCmd.Parameters.AddWithValue("@pickStatus", mObj.CmdParameters[0]);
sqlCmd.CommandText = _strSql;
break;
case 1600: //get batch out order pick details
_strSql = "SELECT * FROM [v_stockOutWavePickDetail] where pickOrderNo= @pickOrderNo and state =@status and volType > @volType and batchpickstate >=@batchpickstate";
sqlCmd.CommandText = _strSql;
sqlCmd.Parameters.AddWithValue("@status", mObj.CmdParameters[0]);
sqlCmd.Parameters.AddWithValue("@batchpickstate", mObj.CmdParameters[1]);
sqlCmd.Parameters.AddWithValue("@pickOrderNo", mObj.CmdParameters[2]);
sqlCmd.Parameters.AddWithValue("@volType",(int) Utils.enumWhLocVol. );
break;
case 1610: //get batch out order pick details by pickOrderNo
_strSql = "SELECT * FROM [v_stockOutBatchPickDetail] where pickOrderNo= @pickOrderNo ";
sqlCmd.CommandText = _strSql;
sqlCmd.Parameters.AddWithValue("@pickOrderNo", mObj.CmdParameters[0]);
break;
case 1620: //get batch out order pick details by pickOrderNo
_strSql = "SELECT * FROM [v_tmsPickRequest] where pickOrderNo= @pickOrderNo ";
sqlCmd.CommandText = _strSql;
sqlCmd.Parameters.AddWithValue("@pickOrderNo", mObj.CmdParameters[0]);
break;
case 1700: //get batch out order details
_strSql = "SELECT * FROM [v_stockOutPickDetail] where pickOrderNo= @pickOrderNo and state =@status and batchpickstate >=@batchpickstate";
sqlCmd.CommandText = _strSql;
sqlCmd.Parameters.AddWithValue("@status", mObj.CmdParameters[0]);
sqlCmd.Parameters.AddWithValue("@batchpickstate", mObj.CmdParameters[1]);
sqlCmd.Parameters.AddWithValue("@pickOrderNo", mObj.CmdParameters[2]);
break;
/*
case 1800: //update batch pick detail status
_strSql = "update t_wmsOutPickDetail set batchpickstate = @newBatchpickstate where pickOrderNo= @pickOrderNo and bulkcount<count ";// and batchpickstate =@oldBatchpickstate";
_strSql += ";update t_wmsOutPickRequest set batchpickstate = @newBatchpickstate where pickOrderNo= @pickOrderNo ";// and batchpickstate =@oldBatchpickstate";
sqlCmd.CommandText = _strSql;
sqlCmd.Parameters.AddWithValue("@newBatchpickstate", mObj.CmdParameters[0]);
// sqlCmd.Parameters.AddWithValue("@oldBatchpickstate", mObj.CmdParameters[1]);
sqlCmd.Parameters.AddWithValue("@pickOrderNo", mObj.CmdParameters[2]);
break;
*/
case 1900: //get pick detail by storckRecord ID
_strSql = "SELECT * FROM [v_stockOutWavePickDetail] where id =@recid";
sqlCmd.CommandText = _strSql;
sqlCmd.Parameters.AddWithValue("@recid", mObj.CmdParameters[0]);
break;
case 2000: //get batch out order pick details by pickStatus (exclude reporders)
_strSql = "SELECT * FROM [v_stockOutBatchPickDetail] where orderType !=@repOrder and state= @pickstate ";
sqlCmd.CommandText = _strSql;
sqlCmd.Parameters.AddWithValue("@pickstate", mObj.CmdParameters[0]);
sqlCmd.Parameters.AddWithValue("@repOrder", (int)Utils.enumOutOrderType. );
break;
case 2100: //get batch out order list ready for validation 4wince only
_strSql = "SELECT * from v_stockOutBatchPickOrder4Wince";
sqlCmd.CommandText = _strSql;
break;
case 2200: //get batch out order list ready for validation 4wince only
_strSql = "SELECT [商品],[批号],[货位],[数量],[商品类别],[厂家] from v_stockOutBatchPickDetail4Wince where pickOrderNo = @pickOrderNo";
sqlCmd.CommandText = _strSql;
sqlCmd.Parameters.AddWithValue("@pickOrderNo", mObj.CmdParameters[0]);
break;
case 2300: //get order details by order state and order type
_strSql = "SELECT * from v_stockOutBatchPickDetail4Wince where ordertype = @orderType and state =@state and orderDate >= @orderDate";
string orderDate = mObj.CmdParameters[2].ToString();
int days = -1;
#if DEBUG
days =-100;
#else
days =-1;
#endif
if (string.IsNullOrEmpty(orderDate))
{
orderDate = DateTime.Now.Date.AddDays(days).ToShortDateString();//this.getDateTime();
}
sqlCmd.CommandText = _strSql;
sqlCmd.Parameters.AddWithValue("@orderDate", orderDate);
sqlCmd.Parameters.AddWithValue("@ordertype", mObj.CmdParameters[0]);
sqlCmd.Parameters.AddWithValue("@state", mObj.CmdParameters[1]);
break;
case 2400: //update priority
_strSql = " update t_wmsOutPickRequest set priority =@priority ,operater =@operId,lastmodified = getdate() where pickOrderNo in " + mObj.CmdParameters[0];
sqlCmd.CommandText = _strSql;
sqlCmd.Parameters.AddWithValue("@priority", mObj.CmdParameters[1]);
sqlCmd.Parameters.AddWithValue("@operId", mObj.CmdParameters[2]);
break;
}
}
}
}