/// <summary>
///INTERFACE IMPLIMENT FOR TABLE t_wmsImportSeeds
///By wm with codesmith. 
///on 07/21/2019
/// </summary>

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

                    break;
                case 100:  //by seedsNo
                    _strSql = "SELECT * FROM t_wmsImportSeeds WHERE seedsNo = @seedsNo order by custName";
                    sqlCmd.CommandText = _strSql;
                    sqlCmd.Parameters.AddWithValue("@seedsNo", mObj.CmdParameters[0]);
                    break;
                case 201:// start load seeds with deskId, 适合不同仓库不同seedsno的情况
                    String cols = "[deskId], [elabId] ,[port],[seedsNo] ,[barcode] ,[custName] ,[itemName]  ,[count] ,[countOut] ,[unit] ,[category1] ,[category2],[category3] ,[spec] ,[elabAddress] ,[description]";
                    _strSql = "insert t_wmsImportSeeds_tmp({0}) select {1} from v_availableSeeds "
                                + " WHERE deskId = @deskId and seedsNo =@seedsNo ";
                    _strSql = String.Format(_strSql, cols, cols);
                    _strSql += ";";
                    _strSql += "delete from  t_wmsImportSeeds WHERE deskId = @deskId and seedsNo =@seedsNo ";
                    sqlCmd.CommandText = _strSql; 
                      
                     sqlCmd.Parameters.AddWithValue("@deskId", mObj.CmdParameters[0]);                  
                     sqlCmd.Parameters.AddWithValue("@seedsNo", mObj.CmdParameters[1]);
                      
                    break;
                case 301:  //start light an item, 适合不同仓库不同seedsno的情况，deskid 已分配好
                    _strSql = "update t_wmsImportSeeds_tmp "
                        + "set color= @color,operater =@operater,state=1"
                        + "WHERE deskId = @deskId and seedsNo=@seedsNo and barcode =@barcode";
                    sqlCmd.CommandText = _strSql;
                    sqlCmd.Parameters.AddWithValue("@deskId", mObj.CmdParameters[0]);
                    sqlCmd.Parameters.AddWithValue("@seedsNo", mObj.CmdParameters[1]);
                    sqlCmd.Parameters.AddWithValue("@barcode", mObj.CmdParameters[2]);
                    sqlCmd.Parameters.AddWithValue("@operater", mObj.CmdParameters[3]);
                    sqlCmd.Parameters.AddWithValue("@color", mObj.CmdParameters[4]);
                    break;

                case 200:// start load seeds ，不分配deskId
                    cols = "[deskId], [elabId] ,[port],[seedsNo] ,[barcode] ,[custName] ,[itemName]  ,[count] ,[countOut] ,[unit] ,[category1] ,[category2],[category3] ,[spec] ,[elabAddress] ,[description],[picktime],[createtime]";
                     _strSql = "insert t_wmsImportSeeds_tmp({0},[state]) select {1},{2} from t_wmsImportSeeds "
                                + " WHERE  seedsNo =@seedsNo ";
                    
                    _strSql += ";delete from  t_wmsImportSeeds WHERE seedsNo =@seedsNo ";
                    _strSql += ";insert t_wmsImportSeeds_history({0},[state],[operater],[lastmodified]) "
                               + " select {1},[state],[operater],[lastmodified] from t_wmsImportSeeds_tmp";//把历史已完成分拣的数据导入history
                    _strSql += " where state={3}";
                    _strSql += ";delete from  t_wmsImportSeeds_tmp WHERE state={3} ";
                    _strSql = String.Format(_strSql, cols, cols, (int)enumSeedsState.处理中, (int)enumSeedsState.已按灭); 
                    sqlCmd.CommandText = _strSql;

                    //sqlCmd.Parameters.AddWithValue("@deskId", mObj.CmdParameters[0]);
                    sqlCmd.Parameters.AddWithValue("@seedsNo", mObj.CmdParameters[1]);

                    break;

                case 300:  //start light an item,根据deskId ->custNames, 结合 barcode找到亮灯数据
                    _strSql = "select * from t_wmsImportSeeds_tmp "  
                        + "WHERE  seedsNo=@seedsNo and barcode =@barcode "
                        + "and state <@state"
                        // + " and (/*elabId > 0*/ or exists(select 1 from t_wmsDeskCustomer where deskId=@deskId and custName = t_wmsImportSeeds_tmp.custName))";
                       +" and (  exists(select 1 from t_wmsDeskCustomer where deskId=@deskId and custName = t_wmsImportSeeds_tmp.custName))"; 

                        sqlCmd.CommandText = _strSql;
                     sqlCmd.Parameters.AddWithValue("@deskId", mObj.CmdParameters[0]);                  
                     sqlCmd.Parameters.AddWithValue("@seedsNo", mObj.CmdParameters[1]);
                     sqlCmd.Parameters.AddWithValue("@barcode", mObj.CmdParameters[2]);
                     sqlCmd.Parameters.AddWithValue("@state", enumSeedsState.已按灭);
                    break;

                case 302:  //反查亮灯
                    _strSql = "select * from v_seeds "
                        + "WHERE  seedsNo=@seedsNo and barcode =@barcode "
                        + " and state =@state"
                        + " and operater =@operater"

                        + " and exists(select 1 from t_wmsDeskCustomer where deskId=@deskId and custName = v_seeds.custName)";
                    sqlCmd.CommandText = _strSql;
                    sqlCmd.Parameters.AddWithValue("@deskId", mObj.CmdParameters[0]);
                    sqlCmd.Parameters.AddWithValue("@seedsNo", mObj.CmdParameters[1]);
                    sqlCmd.Parameters.AddWithValue("@barcode", mObj.CmdParameters[2]);
                    sqlCmd.Parameters.AddWithValue("@operater", mObj.CmdParameters[3]);
                    sqlCmd.Parameters.AddWithValue("@state", enumSeedsState.已按灭);
                    break;
                case 400:  //get seeds by barcode
                    _strSql = "update t_wmsImportSeeds_tmp "
                        + "set color= @color,operater =@operater,state=1"
                        + "WHERE deskId = @deskId and seedsNo=@seedsNo and barcode =@barcode";
                    sqlCmd.CommandText = _strSql;
                    sqlCmd.Parameters.AddWithValue("@deskId", mObj.CmdParameters[0]);
                    sqlCmd.Parameters.AddWithValue("@seedsNo", mObj.CmdParameters[1]);
                    sqlCmd.Parameters.AddWithValue("@barcode", mObj.CmdParameters[2]);
                    sqlCmd.Parameters.AddWithValue("@operater", mObj.CmdParameters[3]);
                    sqlCmd.Parameters.AddWithValue("@color", mObj.CmdParameters[4]);
                    break;

                case 500:  //更新亮灯状态 相应灯光按灭事件
                    _strSql = "update t_wmsImportSeeds_tmp set state = @stateLight, picktime = getdate()  WHERE id =@id "
                          + " and state!= @stateLight";
                //    _strSql += " and state < @statePicked";
                    sqlCmd.CommandText = _strSql;
                    sqlCmd.Parameters.AddWithValue("@id", mObj.CmdParameters[0]);
                    sqlCmd.Parameters.AddWithValue("@stateLight", mObj.CmdParameters[1]);
                 //   sqlCmd.Parameters.AddWithValue("@statePicked", enumSeedsState.已亮灯);
                    break;

                case 600:  //删除分拣单
                    _strSql = "delete from t_wmsImportSeeds where seedsNo =@seedsNo ; ";
                    _strSql += " delete from t_wmsImportSeeds_tmp where seedsNo =@seedsNo ; "; 
                    sqlCmd.CommandText = _strSql;
                    sqlCmd.Parameters.AddWithValue("@seedsNo", mObj.CmdParameters[0]); 
                    break;
                case 601:  //查询可删除分拣单
                    _strSql = " select count(1) from v_seeds where seedsNo =@seedsNo and state >= @state; "; 
                    sqlCmd.CommandText = _strSql;
                    sqlCmd.Parameters.AddWithValue("@seedsNo", mObj.CmdParameters[0]);
                    sqlCmd.Parameters.AddWithValue("@state",(int)enumSeedsState.已亮灯);
                    break;
                case 700:  //查询可删除分拣单
                    _strSql = " select sum(countOut),sum(count) from v_seeds where seedsNo =@seedsNo and barcode = @barcode; ";
                    sqlCmd.CommandText = _strSql;
                    sqlCmd.Parameters.AddWithValue("@seedsNo", mObj.CmdParameters[0]);
                    sqlCmd.Parameters.AddWithValue("@barcode", mObj.CmdParameters[1]);
                    break;



            }
        }
    }
}