/// <summary>
///INTERFACE IMPLIMENT FOR TABLE t_wmsInRequest
///By wm with codesmith. 
///on 05/12/2017
/// </summary>

using System;
using System.Data.SqlClient;
using DeiNiu.Utils; 
namespace DeiNiu.wms.Data.Model
{
    [Serializable]  class WmsInRequest_Imp : WmsInRequest_base_Imp
    { 
        protected override void CmdPrepare(SqlCommand sqlCmd)
        { 
            base.CmdPrepare(sqlCmd);
            WmsInRequest mObj = ( WmsInRequest)modelObj;
            switch (_cust_op_flag)
            {      
                
                case 99:  //query orders 
                   _strSql = "SELECT *,ROW_NUMBER() OVER (ORDER BY ID DESC) as sortNo  FROM t_wmsInRequest WHERE DR = 1 " + mObj.CmdParameters[0].ToString();
                   _strSql = "SELECT * FROM (" + _strSql + " )AS SORTEDTB WHERE sortNo BETWEEN @START AND @END";
                   _strSql += ";SELECT COUNT(*) FROM t_wmsInRequest WHERE DR = 1  " + mObj.CmdParameters[0].ToString();
                   sqlCmd.CommandText = _strSql;
                   sqlCmd.Parameters.AddWithValue("@START", this._rownumStart);
                   sqlCmd.Parameters.AddWithValue("@END", this._rownumEnd);

                   break;
                case 100:  //by name
                    _strSql = "SELECT * FROM t_wmsInRequest WHERE NAME = @NAME";
                    sqlCmd.CommandText = _strSql; 
                     sqlCmd.Parameters.AddWithValue("@NAME",   mObj.CmdParameters[0]  );
                    break;
                case 110:  //initial by goodsid,batch,locationid
                    _strSql = "SELECT * FROM t_wmsInRequest WHERE ORDERNO =@ORDERNO";
                    sqlCmd.CommandText = _strSql;
                    sqlCmd.Parameters.AddWithValue("@ORDERNO", mObj.CmdParameters[0]); 
                    break;
                case 200: //initial in stock requests
                    // _strSql  = "INSER INTO  t_wmsInRequestDetail VALUES SELECT * FROM V_STOCKINREQUESTDETAIL A WHERE EXISTS(SELECT 1 FROM  V_STOCKINREQUEST B  "
                    //           + "  WHERE ORDERDATE >= @START AND  ORDERDATE <= @END AND A.ORDERNO = B.ORDERNO)";
                    //_strSql  += "INSER INTO  t_wmsInRequest VALUES SELECT * FROM V_STOCKINREQUEST WHERE ORDERDATE >= @START AND  ORDERDATE <= @END";
                    //sqlCmd.Parameters.AddWithValue("@START", mObj.CmdParameters[0]);
                    //sqlCmd.Parameters.AddWithValue("@END",   mObj.CmdParameters[0]);

                    _strSql = "INSERT INTO  t_wmsInRequestDetail (ORDERNO,GOODSID,CNT,PRODUCTDATE,BATCH,BARCODE,VALIDDATE,REGEDITCODE,MANUFACTURER ) "
                                + "SELECT  ORDERNO,GOODSID, COUNT,PRODUCTDATE,BATCH,BARCODE,VALIDDATE,REGEDITCODE,MANUFACTURER   FROM V_STOCKINREQUESTDETAIL_AVAILABLE_GOOD ";
 
                               // A WHERE EXISTS(SELECT 1 FROM  V_STOCKINREQUEST_AVAILABLE B  "
                              // + "  WHERE STATE =0 AND A.ORDERNO = B.ORDERNO);";
                    _strSql += "; INSERT INTO  t_wmsInRequest (ORDERNO,ORDERTYPENAME,ORDERDATE,VENDER,OWNER,DESCRIPTION) SELECT  ORDERNO,ORDERTYPENAME,ORDERDATE,VENDER,OWNER, DESCRIPTION "
                                 +" FROM V_STOCKINREQUEST_AVAILABLE_GOOD" ;// WHERE STATE =0 ";
                    sqlCmd.CommandText = _strSql;
                  
                    
                   break;

      
                case 400:  //query order detail by orderNo, 

                   _strSql = "SELECT * FROM V_STOCKINREQUESTDETAIL_WMS WHERE ORDERNO =@ORDERNO"
                            + " ORDER BY GOODSID,BATCH";  //同药品，按批次排序，便于分配货位。
                   
                   sqlCmd.CommandText = _strSql;
                   sqlCmd.Parameters.AddWithValue("@ORDERNO", mObj.CmdParameters[0]);

                   break;

                case 500:  //query order detail by orderNo, 
                   if (WmsConstants.IN_STOCK_PDA_SCAN)
                   { 
                       _strSql = "SELECT * FROM v_stockInRequestDetail4wince WHERE ORDERNO =@ORDERNO"
                                + " ORDER BY GOODSID,BATCH";  //同药品，按批次排序，便于分配货位。
 
                   }
                   else
                   {
                        _strSql = "SELECT * FROM v_reportStockRecord WHERE ORDERNO =@ORDERNO"
                                + " ORDER BY GOODSID,BATCH";  //同药品，按批次排序，便于分配货位。
                   }
                   sqlCmd.CommandText = _strSql;
                   sqlCmd.Parameters.AddWithValue("@ORDERNO", mObj.CmdParameters[0]);

                   break;

                case 600:  //query order with bad batch\productdate\validate details

                   _strSql = "SELECT * FROM V_STOCKINREQUEST_AVAILABLE_bad order by orderDate desc";  
                   sqlCmd.CommandText = _strSql;

                   break;
                case 610:  //query order details with bad batch\productdate\validate  

                   _strSql = " select * from v_stockInRequestDetail_Available_bad a where  orderno =@orderNo "
                            + " and exists (select 1   from v_stockInRequestDetail_Available_bad b where orderNo = a.orderNo  group by  goodsId,batch having(count(1)>1)) "
                            + " order by goodsId,batch,productDate,validDate";
  
                   sqlCmd.CommandText = _strSql;
                   sqlCmd.Parameters.AddWithValue("@orderNo", mObj.CmdParameters[0]);

                   break;
                case 700:  //delete request and details

                   _strSql = "DELETE FROM T_WMSINREQUESTDETAIL WHERE  ORDERNO =@ORDERNO "
                             + " ; DELETE FROM T_WMSINREQUEST WHERE ORDERNO =@ORDERNO";
                   sqlCmd.CommandText = _strSql;
                   sqlCmd.Parameters.AddWithValue("@ORDERNO", mObj.CmdParameters[0]);
                   break;

                case 800:  //get stock in record detail by  Id
                   // _strSql = "SELECT * FROM [v_stockOutBatchPickDetail] WHERE ID = @Id";
                   _strSql = "SELECT * FROM [v_stockInRequestDetail4wince] WHERE ID = @Id";
                   sqlCmd.CommandText = _strSql;
                   sqlCmd.Parameters.AddWithValue("@Id", mObj.CmdParameters[0]);
                   break;

                case 900:  //update request order status to 

                   _strSql = " UPDATE T_WMSINREQUEST SET STATE =@STATE WHERE ORDERNO =@ORDERNO  "
                             + " AND NOT EXISTS(SELECT 1 FROM T_WMSINUPPORT WHERE ORDERNO = @ORDERNO AND STATE=1 )";
                   _strSql += "; UPDATE t_wmsInRequestDetail SET STATE =@STATE WHERE id =@detailId  "  ;
                   sqlCmd.CommandText = _strSql;
                   sqlCmd.Parameters.AddWithValue("@ORDERNO", mObj.CmdParameters[0]);
                   sqlCmd.Parameters.AddWithValue("@detailId", mObj.CmdParameters[1]);
                   sqlCmd.Parameters.AddWithValue("@STATE", (int)enumInStockOrderStatus.已上架);
                   break;
                    
            }
        }
    }
}