using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using WMS.IData;
using WMS.IData.IRK;
using WMS.Model.RK;
using Oracle.ManagedDataAccess.Client;

namespace WMS.SqlServerData.RKData.Auto
{
    public class AutoRkData : IAutoRkData
    {
        #region  接口
        public DataTable GetPlcDataHigh(string hghID)
        {
            DataTable tb = new DataTable();
            string sqlStr =@"select *  from  t_hghPlc  where decid='"+hghID+"'";

            try
            {
                tb = SystemDataObject.Instance.GetDataTable(sqlStr);

            }
            catch (Exception ex)
            { return null; }
            return tb;
        }
        public void UpdateScan2Sts(string code)
        {
            string sqlStr =@"update   T_ONGOODSSHELF  set status=0 where UPGOODS_ID='"+code+"'";

            SystemDataObject.Instance.GetDataTable(sqlStr);

        }
        public void UpdatePlcData()
        {
            string sqlStr =@"update   t_hghPlc  set high=0,wgh=0,locid=0 ";

             SystemDataObject.Instance.GetDataTable(sqlStr);

           
        }
        public void UpdatePlcIdData(string locID)
        {
            string sqlStr =@"update   t_hghPlc  set locid='"+ locID + "' ";

            SystemDataObject.Instance.GetDataTable(sqlStr);


        }
        /// <summary>
        /// 根据仓库编号查询站台
        /// </summary>
        /// <param name="STORAGE_ID"></param>
        /// <returns></returns>
        public DataTable GetStandStorage(string STORAGE_ID)
        {
            string sqlStr = "select * from T_ConveyorStatus t where t.optype='1' and t.STORAGE_ID='"+ STORAGE_ID + "' ";
          return   SystemDataObject.Instance.GetDataTable(sqlStr);
        }
        /// <summary>
        /// 查询可以入库的堆垛机和站台
        /// </summary>
        /// <returns></returns>
        public DataTable GetDecData(string STORAGE_ID)
        {
            string sqlStr =@"select a.decid,a.ConveyorID,optype from [dbo].[t_decname]  t
                           join T_ConveyorStatus a on t.decid = a.decId and a.STORAGE_ID='"+ STORAGE_ID + "'  where (sts = '0' or sts = '1')  and a.optype = '1' and t.STORAGE_ID='" + STORAGE_ID + "'  order by ct";
          return   SystemDataObject.Instance.GetDataTable(sqlStr);

        }
        public DataSet GetdecStationData(string goodsId,string STORAGE_ID)
        {
            string sqlStr =@"select  top 1  t.LOCATION_ID,a.instand from T_MI_STOCK   t
                                left join T_BASE_STORAGE_AREA_LOCATION a on t.LOCATION_ID = a.LOCATION_ID
                                where t.GOODS_ID in(select  MATERIAL_ID from T_SC_GOODS_MATERIAL
                                where GOODS_ID in (select GOODS_ID from  T_SC_GOODS_MATERIAL where              MATERIAL_ID = '"+ goodsId + "')) and t.STORAGE_ID = '"+ STORAGE_ID + "';" +
                                " select  top 1  t.LOCATION_ID,a.instand from T_ONGOODSSHELF   t" +
                                "   left join T_BASE_STORAGE_AREA_LOCATION a on" +
                                "  t.LOCATION_ID = a.LOCATION_ID where t.GOODSID" +
                                " in (select  MATERIAL_ID from T_SC_GOODS_MATERIAL" +
                                "  where GOODS_ID in (select GOODS_ID from " +
                                "  T_SC_GOODS_MATERIAL where MATERIAL_ID = '"+ goodsId + "'))" +
                                "  and  t.STORAGE_ID = '"+ STORAGE_ID + "'";
            return SystemDataObject.Instance.GetDataSet(sqlStr);

        }
        public void UpdateDecDataSts(string decId,string sts,string STORAGE_ID)
        {
            string sqlStr = "update t_decname set sts ='"+sts+"' where decid='" + decId + "' ";
            if(STORAGE_ID.Trim().Length>0)
            {
                sqlStr = sqlStr + " and STORAGE_ID='" + STORAGE_ID + "' ";
            }
            SystemDataObject.Instance.ExecuteSqlCommit(sqlStr);
        }
        public DataSet GetTaksAllData()
        {
            DataSet ds = new DataSet();
            string sqStr = "select * from T_ONGOODSSHELF ;select * from T_CK_PICKINGWAVEGOODS";
            return SystemDataObject.Instance.GetDataSet(sqStr);
        }
        public DataTable GetCtlDataOn(string ctl,string STORAGE_ID)
        {
            DataTable tb = new DataTable();
            string sqlStr =@"select *  from  T_ONGOODSSHELF where ctl='" + ctl + "'  ";

            try
            {
                tb = SystemDataObject.Instance.GetDataTable(sqlStr);

            }
            catch (Exception ex)
            { return null; }
            return tb;
        }
        // <summary>
        ///  where t.Status='C' 
        /// </summary>
        /// <returns></returns>
        public DataTable GetWmsData(string ctl)
        {
            DataTable tb = new DataTable();
            string sqlStr =@"select status  from  T_ONGOODSSHELF where ctl='" + ctl+"' and PLCID is null";

            try
            {
                tb = SystemDataObject.Instance.GetDataTable(sqlStr);
                
            }
            catch (Exception ex)
            { return null; }
            return tb;
        }
        public DataTable GetPlcIdOpLoc(string plcId)
        {
            DataTable tb = new DataTable();
            string sqlStr = "select LOCATION_ID from T_ONGOODSSHELF where PlcID='" + plcId + "'";

            try
            {
                tb = SystemDataObject.Instance.GetDataTable(sqlStr);
            }
            catch (Exception ex)
            { return null; }
            return tb;
        }
        public void UpdateEcxeData(string PLCID, string status)
        {
            string sqlStr ="update T_ONGOODSSHELF set status ='"+ status + "' where plcId='" + PLCID + "'";
            //+"update T_RK_STORAGE_LOT set STATUS = '2' where LOT_ID = '"+ LOT_ID + "';"
            //+"update T_RK_CODEDISK set STS = '2' where LOT_ID = '"+ LOT_ID + "'; ";
            SystemDataObject.Instance.ExecuteSqlCommit(sqlStr);
        }
        public void UpdateEcxeOutData(string plcid,string status)
        {
            string sqlStr = "update T_CK_PICKINGWAVEGOODS set status ='"+ status + "' where plcId='" + plcid + "' and status=6";
            SystemDataObject.Instance.ExecuteSqlCommit(sqlStr);
        }
        public void DeleteEcxeData(string plcid)
        {
            string sqlStr = @" insert into T_ONGOODSSHELF_BAK          (LOT_ID,GOODSID,PROVIDER_ID,LOCATION_ID,STO_NUM,ACC_NUM,SHELF_NUM,STOCK_NUM,  
                            ONDATE,ONSHELFUSERID,STORAGE_ID,STORAGE_AREA_ID,
                              ProDucLotId,HASVOLUME,BARCODE,CTL,CUSTOMER_ID,                             PRODUCTION_DATE,UPGOODS_ID,UNIT,baozhiqi,HIGH,
                              PlcID,Whseloc,GOODSNAME,instand,STORAGE_MODE,wgh,
                              GOODS_MEASURE_ID,TASKTYPE,PACKING_NUM,REMARK)  
                              select LOT_ID,GOODSID,PROVIDER_ID,LOCATION_ID,                           STO_NUM,ACC_NUM,SHELF_NUM,STOCK_NUM,                            ONDATE,ONSHELFUSERID,STORAGE_ID,STORAGE_AREA_ID,
                              ProDucLotId,HASVOLUME,BARCODE,CTL,CUSTOMER_ID,                        PRODUCTION_DATE,UPGOODS_ID,UNIT,baozhiqi,HIGH,
                             PlcID,Whseloc,GOODSNAME,instand,STORAGE_MODE,wgh,
                             TASKTYPE,GOODS_MEASURE_ID ,PACKING_NUM,'手动删除' from  T_ONGOODSSHELF  where
                                    plcid='" + plcid + "'  ";
            
            SystemDataObject.Instance.ExecuteSqlCommit(sqlStr);
            sqlStr = "delete from T_ONGOODSSHELF where plcid='" + plcid + "'";
            SystemDataObject.Instance.ExecuteSqlCommit(sqlStr);
        }
        public void DeleteEcxeOutData(string plcid)
        {
            string strsqlloc = @"update T_MI_STOCK set STS=0 where LOCATION_ID=(select distinct LOC_ID from T_CK_PICKINGWAVEGOODS where PLCID='"+plcid+"')";
            SystemDataObject.Instance.ExecuteSqlCommit(strsqlloc);
            string strsql = @"insert into T_CK_PICKINGWAVEGOODS_BACK
                              ( PICKINGID,
                               OUT_ID,
                               PUTIN_ID,
                               DOCCTNUMBER,
                               GOOD_ID,
                               LOC_ID,
                               GOODS_NUM,
                               SPECIFICATION,
                               PRODUCTION_DATE,
                               WARE_DATE,
                               PROVIDER_ID,
                               UNIT,
                               REMARK,
                               SCALE_UNIT,
                               MISTOCK_NUM,
                               PICKING_NUM,
                               PRICE,
                               STORAGE_AREA_ID,
                               STORAGE_ID,
                               CTL,
                               GOODS_NUM_SJ,ProDucLotId,BARCODE,GOODS_NAME,ToWhseloc,plcId,outStand,typests)
                               (
                              select PICKINGID,
                               OUT_ID,
                               PUTIN_ID,
                               DOCCTNUMBER,
                               GOOD_ID,
                               LOC_ID,
                               GOODS_NUM,
                               SPECIFICATION,
                               PRODUCTION_DATE,
                               WARE_DATE,
                               PROVIDER_ID,
                               UNIT,
                               '手动删除',
                               SCALE_UNIT,
                               MISTOCK_NUM,
                               PICKING_NUM,
                               PRICE,
                               STORAGE_AREA_ID,
                               STORAGE_ID,
                               CTL,
                                  GOODS_NUM_SJ,ProDucLotId,BARCODE,GOODS_NAME,ToWhseloc,plcId,outStand,typests
                                from T_CK_PICKINGWAVEGOODS 
                               where plcId='" + plcid + "'   )";//and OUT_ID='" + out_id + "'
            SystemDataObject.Instance.ExecuteSqlCommit(strsql);
            string sqlStr = "delete from T_CK_PICKINGWAVEGOODS  where plcId='" + plcid + "'";
            SystemDataObject.Instance.ExecuteSqlCommit(sqlStr);
           
        }
        public DataTable GetexceData()
        {
            string sqlStr = "select lot_id, goodsid, provider_id, location_id, sto_num, acc_num, shelf_num, stock_num, ondate, onshelfuserid, storage_id, storage_area_id, upgoods_id, hasvolume, goods_typeid, storage_mode, production_date, goodsvolume, ctl, barcode, customer_id, goodsname, plcid, high,  produclotid, unit, baozhiqi, whseloc, instand, wgh, decid, tasktype, goods_measure_id, packing_num, damage_num, damage_location, warehousing_id, net_weigh, rough_weight, scale_unit, total_volume, total_rough_weight, total_net_weigh, total_money, remark,CASE WHEN STATUS ='1' THEN '未下发' WHEN STATUS = '3' THEN '正在执行'  WHEN STATUS = '2' THEN '已完成' END  as STATUS  from T_ONGOODSSHELF   order by ONDATE";
          
           return  SystemDataObject.Instance.GetDataTable(sqlStr);
        }
        public DataTable GetExceData2()
        {
            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.Append(@"
    SELECT 
        pickingid, 
        putin_id, 
        docctnumber, 
        good_id, 
        loc_id, 
        goods_num, 
        specification,
        production_date, 
        ware_date, 
        provider_id, 
        unit, 
        remark, 
        out_id, 
        scale_unit,
        mistock_num, 
        picking_num, 
        price, 
        storage_area_id, 
        storage_id, 
        ctl, 
        goods_num_sj,  
        typests, 
        packing_num, 
        produclotid, 
        barcode, 
        goods_name, 
        port, 
        goods_measure_id,
        datatype, 
        CASE ck_type 
            WHEN '1' THEN '手动置顶' 
            ELSE '正常' 
        END AS ck_type,
        customermemberid, 
        plcid, 
        towhseloc,
        outstand,
        CASE 
            WHEN STATUS = '0' THEN '未下发' 
            WHEN STATUS = '1' THEN '正在执行' 
            WHEN STATUS = '6' THEN '堆垛机出库完成' 
            WHEN STATUS = '9' THEN '装箱线完成'
            ELSE '未知状态' 
        END AS STATUS,
        CASE 
            WHEN PORT = '0' OR PORT IS NULL THEN '平均分' 
            ELSE PORT 
        END AS PORT 
    FROM 
        T_CK_PICKINGWAVEGOODS  
    ORDER BY 
        WARE_DATE DESC");  // 默认降序，提高查询效率（可根据业务调整）

            return SystemDataObject.Instance.GetDataTable(sqlBuilder.ToString());
        }
        public DataTable GetCodeData(string palletID)
        {
            DataTable tb = new DataTable();
            string sqlStr =@"select *  from  pak_wait_in   t where RTRIM(t. Sheet_no)='" + palletID + "'";

            try
            {
                tb = SystemDataObject.Instance.GetDataTable(sqlStr);
            }
            catch (Exception ex)
            { return null; }
            return tb;
        }
        public DataTable GetCodeWmsData(string palletID)
        {
            DataTable tb = new DataTable();
            string sqlStr =@"select *  from  pak_wait_in   where RTRIM(Sheet_no)='" + palletID + "'";

            try
            {
                tb = SystemDataObject.Instance.GetDataTable(sqlStr);
            }
            catch (Exception ex)
            { return null; }
            return tb;
        }
        public void DeleteGetdata(string PalletID, string spid, string pihao)
        {
            string sqlStr =@" delete   pak_wait_in  where  PalletID='" + PalletID + "' ;delete T_ONGOODSSHELF PalletID='" + PalletID + "'";
 
        }
        
       
         
        #endregion
        /// <summary>
        /// 查询上架确认数据
        /// </summary>
        /// <returns></returns>
        public DataTable GetRkData(string taskType)
        {
            string sqlStr = " select t.*,a.GOODS_NAME,b.STORAGE_NAME,c.STORAGE_AREA_NAME,d.VC_DICTIONARY_NAME from T_ONGOODSSHELF t left join T_BASE_GOODS a on t.GOODSID = a.GOODS_ID " +
                "left join T_BASE_STORAGEINFO b on t.STORAGE_ID = b.STORAGE_ID " +
                " left join T_BASE_STORAGE_AREA c on t.STORAGE_AREA_ID = c.STORAGE_AREA_ID" +
                " left join T_SYS_DICTIONARY_TAB d on d.VC_DICTIONARY_ID = t.UNIT where t.status = '0'";
            return SystemDataObject.Instance.GetDataTable(sqlStr);
        }



        public void DELETEt_mistock(string locId, string ctl)
        {
            string sqlStr = " DELETE t_mi_stock where LOCATION_ID='" + locId + "' AND CTL='"+ ctl + "' ";

            SystemDataObject.Instance.GetDataSet(sqlStr);
        }
        /// <summary>
        ///  入库选择库位后更改库位的状态，1代表占用
        /// </summary>
        /// <param name="locId"></param>
        public void UpdateLocCtlSts(string locId, string STATUS,string STORAGE_ID)
        {
            string sqlStr = " update T_BASE_STORAGE_AREA_LOCATION set STATUS= '"+ STATUS + "',HASVOLUME='"+ STATUS + "' where LOCATION_ID='" + locId+ "' ";

            SystemDataObject.Instance.ExecuteSql(sqlStr);
        }
        public void UpdateLocislockSts(string locId, string STATUS, string STORAGE_ID)
        {
            string sqlStr = " update T_BASE_STORAGE_AREA_LOCATION set islock= '" + STATUS + "' where LOCATION_ID='" + locId + "' and STORAGE_ID='" + STORAGE_ID + "'";

            SystemDataObject.Instance.ExecuteSql(sqlStr);
        }
        public DataTable GetFdData(string decId,string station)
        {
            string sqlStr = "select count(1)as a from T_ONGOODSSHELF where instand='"+ station + "'  and status='1'";
            return  SystemDataObject.Instance.GetDataTable(sqlStr);
        }
        public DataTable GetTasks(string decId)
        {
            string sqlStr = "";
            if (decId == "A2")
            {
                //sqlStr = "select * from T_ONGOODSSHELF where decid='" + decId + "' or  (decid='A1'  and substr(location_id,-1,1)!=1 ) ";
                sqlStr = "select * from T_ONGOODSSHELF where decid='" + decId + "' ";
            }
            else
            {
               // sqlStr = "select * from T_ONGOODSSHELF where decid='" + decId + "'  or (decid='A4'  and substr(location_id,-1,1)!=1 ) ";
                sqlStr = "select * from T_ONGOODSSHELF where decid='" + decId + "'   ";
            }
            
            return SystemDataObject.Instance.GetDataTable(sqlStr);
        }
        public DataTable GetTasks2(string decId)
        {
            string sqlStr = string.Empty;
            if (decId == "A2")
            {
               // sqlStr = "select * from T_CK_PICKINGWAVEGOODS where outstand='" + decId + "' or outstand='拆2' or (outstand ='A1' and length(pickingid)<30 and substr(loc_id,-1,1)!=1 )  ";
                sqlStr = "select * from T_CK_PICKINGWAVEGOODS where outstand='" + decId + "' or outstand='拆2'   ";
            }
            else {
                //sqlStr = "select * from T_CK_PICKINGWAVEGOODS where outstand='" + decId + "' or outstand='拆1' or (outstand ='A4' and length(pickingid)<30 and substr(loc_id,-1,1)!=1) ";
                sqlStr = "select * from T_CK_PICKINGWAVEGOODS where outstand='" + decId + "' or outstand='拆1'  ";

            }
            return SystemDataObject.Instance.GetDataTable(sqlStr);
        }
        public void UpdateOpSts(string plcID)
        {
            string sqlStr = " update T_ONGOODSSHELF set STATUS= '9' where plcID='" + plcID + "'";

            SystemDataObject.Instance.ExecuteSql(sqlStr);
            
        }
        /// <summary>
        /// 更新订单状态
        /// </summary>
        /// <param name="ctl"></param>
        public void UpdateOrderSts(string ctl,string status)
        {
            string sqlStr = " update T_ONGOODSSHELF set STATUS= '"+ status + "' where CTL='" + ctl + "'";

            SystemDataObject.Instance.ExecuteSql(sqlStr);

        }
        public bool UpdateConveSts(List<string> lis, string sqlstr)
        {
           // string[] MOdeLis = { "202", "204", "205", "208", "209", "212", "213", "216", "217", "219" };
          
            int flag = 0;
            bool hasUpflag = false;
            //string sqlstr = string.Empty;
            //if (MOdeLis.Contains(conveID))
            //{

            //    sqlstr = "update [T_ConveyorStatus] set AutoMode='" + lis[0] + "',ManualMode='" + lis[1] + "',AlarmMode='" + lis[2] + "',LoadMode='" + lis[3] + "',OverSize='" + lis[4] + "',OutEnable='" + lis[5] + "',InEnable='" + lis[6] + "' ,plcid='" + lis[8] + "' ,Mode='" + lis[7] + "'where ConveyorID='" + conveID + "'";
            //}
            //else
            //{
            //    sqlstr = "update [T_ConveyorStatus] set AutoMode='" + lis[0] + "',ManualMode='" + lis[1] + "',AlarmMode='" + lis[2] + "',LoadMode='" + lis[3] + "',OverSize='" + lis[4] + "',OutEnable='" + lis[5] + "',InEnable='" + lis[6] + "' ,plcid='" + lis[7] + "'where ConveyorID='" + conveID + "'";
            //}
            try
            {
                flag = SystemDataObject.Instance.ExecuteSqlCommit(sqlstr);
                if (flag > 0)
                {
                    hasUpflag = true;

                }

            }
            catch (Exception ex)
            {
                hasUpflag = false;

            }

            return hasUpflag;
        }
        /// <summary>
        /// 获取库位编号
        /// </summary>
        /// <param name="decId"></param>
        /// <returns></returns>
        public DataTable GetLocDataRow(string decId,string Hgh)
        {
            string sqlStr =@" select  top 1 t.LOCATION_ID from [dbo].[T_BASE_STORAGE_AREA_LOCATION] t where STATUS='0'  and 
                          t.LOCATION_HEIGHT>=" + Hgh + "  and t.decId='"+decId+"'  and  t.LOCATION_ID not in(select locid from t_hghPlc)   ";

            return SystemDataObject.Instance.GetDataTable(sqlStr);
        }
 
        /// <summary>
        /// 备份入库数据
        /// </summary>
        /// <param name="Sheet_no"></param>
        /// <param name="Pdc_no"></param>
        public void InserLocWaitIn(string PalletID)
        {
            string sqlStr = " insert into  pak_wait_in_bak " +
                " select top 1 * from  pak_wait_in  where PalletID='"+ PalletID + "'";

            SystemDataObject.Instance.ExecuteSql(sqlStr);

        }

        public void DelteWaitIn(string PalletID)
        {
            string sqlStr = " delete  pak_wait_in  where  PalletID='" + PalletID + "'";

            SystemDataObject.Instance.ExecuteSql(sqlStr);
        }
        /// <summary>
        /// 更新wms数据状态
        /// </summary>
        /// <param name="Sheet_no"></param>
        /// <param name="Pdc_no"></param>
        /// <param name="PalletID"></param>
        public void SerLocWaitInSts(string Sheet_no)
        {
            string sqlStr = "  Update  pak_wait_in  set Status = 'S' where   Sheet_no='" + Sheet_no + "'";

            SystemDataObject.Instance.ExecuteSql(sqlStr);
        }
        /// 
        /// <summary>
        /// 查询批次明细信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetProduceDataList()
        {
            string sqlStr =@"SELECT T_RK_lotGoods.ProDucLotId

                                          ,T_RK_lotGoods.BarCode
                                          ,T_RK_lotGoods.GOODSID
            	                          ,T_BASE_GOODS.GOODS_NAME 
                                          ,T_RK_lotGoods.GOODS_TYPEID
                                          ,T_RK_lotGoods.REMARK
                                          ,T_RK_lotGoods.UNIT
                                          ,T_RK_lotGoods.pritid
                                          ,T_RK_lotGoods.PRODUCTION_DATE
                                          ,T_RK_lotGoods.ONSHELFUSERID
                                          ,T_RK_lotGoods.STOCK_NUM
                                          ,T_RK_lotGoods.CTL
                                          ,case T_RK_lotGoods.STS when '0' then '未下发' when '1' then '已下发' when '' then '已入库' end STS
                                          ,T_RK_lotGoods.indate
                                      FROM T_RK_lotGoods,T_BASE_GOODS where T_RK_lotGoods.GOODSID=T_BASE_GOODS.GOODS_ID order by ProDucLotId,BarCode";
            return SystemDataObject.Instance.GetDataTable(sqlStr);
        }

       

       
        /// <summary>
        /// 查询同一个物料是否同一个库位
        /// </summary>
        /// <returns></returns>
        public DataTable GetNotNullGoodIdLocal(decimal hgt)
        {
     
            string sqlStr =@"   select top 1 row, from t_base_storage_area_location

                       where     HASVOLUME = '0' and satus ='0' and 	  LOCATION_HEIGHT=cast('" + hgt + "' as numeric(18,2))"; 
                   return SystemDataObject.Instance.GetDataTable(sqlStr.ToString());
        }


        /// <summary>
        /// 获取Role Name
        /// </summary>
        /// <returns></returns>
        public string GetPlcId()
        {
            string sql = " select SEQ_PLC_TASKINVAL.nextval from dual";//"select NEXT VALUE FOR SEQ_PLC_TASKVAL";
            DataTable dt = SystemDataObject.Instance.GetDataTable(sql);
            return dt.Rows[0][0].ToString();
        }
        /// <summary>
        /// 查询同一个物料是否同一个库位
        /// </summary>
        /// <returns></returns>
        public DataTable GetNotNullLocal()
        {

            string sqlStr =@"   select a.LOCATION_ID,a.VOLUME,a.PY_NAME from t_mi_stock t
                          join t_base_storage_area_location a on t.LOCATION_ID = a.LOCATION_ID 
                          where    a. HASVOLUME = '0' ";

            return SystemDataObject.Instance.GetDataTable(sqlStr.ToString());
        }


        /// <summary>
        /// 查询空库位
        /// </summary>
        /// <returns></returns>
        public DataTable GetNullLocal(string sts)
        {
            ///
       //     select LOCATION_ID from t_mi_stock t where GOODS_ID = '202120002' and LOCATION_ID
						 //in  (select LOCATION_ID from t_base_storage_area_location a where t.LOCATION_ID = a.LOCATION_ID and
       //                    HASVOLUME = '0')
            string sqlStr = "  select top 1 location_id,location_long,location_width,location_height,volume,hasvolume"
                             + "   from t_base_storage_area_location  where  STATUS='" + sts + "' and   location_id "
                         + " not in (select min(location_id) from t_mi_stock group by location_id)    order by LOCATION_ID ";
             
            return SystemDataObject.Instance.GetDataTable(sqlStr.ToString());
        }

        /// <summary>
        /// 查询空库位
        /// </summary>
        /// <returns></returns>
        public DataTable GetNullLocalData()
        {
            string sqlStr =@"  select location_id,location_long,location_width,location_height,volume,hasvolume,
                                 (case STATUS when '0'then'无托盘' when '1'then'有托盘' end) as STATUS
                                from t_base_storage_area_location  where location_id 
                         not in (select min(location_id) from t_mi_stock group by location_id) order by LOCATION_ID ";
            //select LOCATION_ID,a from (select LOCATION_ID,SUBSTRING(LOCATION_ID,2,1) as a from  T_BASE_STORAGE_AREA_LOCATION where HASVOLUME ='0') ab group by a,LOCATION_ID
            return SystemDataObject.Instance.GetDataTable(sqlStr);
        }

        /// <summary>
        /// 查询小车的位置
        /// </summary>
        /// <returns></returns>
        public DataTable GetCarLocalData(string ctl)
        {
            string sqlStr = @"select * from T_ONGOODSSHELF t where t.ctl='"+ ctl + "'";


            //select LOCATION_ID,a from (select LOCATION_ID,SUBSTRING(LOCATION_ID,2,1) as a from  T_BASE_STORAGE_AREA_LOCATION where HASVOLUME ='0') ab group by a,LOCATION_ID
            return SystemDataObject.Instance.GetDataTable(sqlStr);
        }

        /// <summary>
        /// 查容积能够满足的库位
        /// </summary>
        /// <returns></returns>
        public DataTable GetInMzLocalData(string locID)
        {
            string sqlStr =@"select top 1 LOCATION_ID from  T_BASE_STORAGE_AREA_LOCATION
                               where  (VOLUME*2-" + locID + ")>=0 and SUBSTRING(LOCATION_ID,2,1)='2' and HASVOLUME=0 order by VOLUME,SORT asc  ";


            //select LOCATION_ID,a from (select LOCATION_ID,SUBSTRING(LOCATION_ID,2,1) as a from  T_BASE_STORAGE_AREA_LOCATION where HASVOLUME ='0') ab group by a,LOCATION_ID
            return SystemDataObject.Instance.GetDataTable(sqlStr);
        }
        /// <summary>
        /// 查询库位的个数和库位
        /// </summary>
        /// <param name="product"></param>
        /// <returns></returns>
        public DataTable GetMisLocProduct(string productId)
        {
            string sqlStr =@" select  a.LOCATION_ID,min(b.HASVOLUME)HASVOLUME,min(b.VOLUME)VOLUME from T_MI_STOCK a
                                     left join T_BASE_STORAGE_AREA_LOCATION b on a.LOCATION_ID = b.LOCATION_ID  
                                   where ProDucLotId='" + productId + "' group by a.LOCATION_ID";
            return SystemDataObject.Instance.GetDataTable(sqlStr);
        }
        /// <summary>
        /// 查询该库位是否已经存放满
        /// </summary>
        /// <returns></returns>
        public DataTable GetLocIdVolume(string locid_id)
        {
            string sqlStr = "select HASVOLUME from  T_BASE_STORAGE_AREA_LOCATION where LOCATION_ID ='" + locid_id + "'";
            //select LOCATION_ID,a from (select LOCATION_ID,SUBSTRING(LOCATION_ID,2,1) as a from  T_BASE_STORAGE_AREA_LOCATION where HASVOLUME ='0') ab group by a,LOCATION_ID
            return SystemDataObject.Instance.GetDataTable(sqlStr);
        }
        /// <summary>
        /// 更新库位表为占用
        /// </summary>
        /// <param name="locId"></param>
        public void UpdateLocSts(string locId)
        {
            string sqlStr = "update T_BASE_STORAGE_AREA_LOCATION  set STATUS='0'  where LOCATION_ID='" + locId + "' ";
            SystemDataObject.Instance.ExecuteSqlCommit(sqlStr);
        }
        /// <summary>
        /// 更改改批次的任务为下发
        /// </summary>
        /// <param name="lotId"></param>
        public void UpdateProductSts(string lotid, string ProDucLotId, string goodsId, decimal num)
        {
            string sqlStr = " update T_RK_lotGoods set indate=getdate(), SHELF_NUM= SHELF_NUM + convert(decimal,'" + num + "') ,stock_num =stock_num - convert(decimal,'" + num + "') where lot_id ='" + lotid + "' and ProDucLotId='" + ProDucLotId + "' and goodsId='" + goodsId + "'  ";
            SystemDataObject.Instance.ExecuteSql(sqlStr);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="lotId"></param>
        public void UpdateProductInSts(string lotId, string goodsId, string ProDucLotId)
        {
            string sqlStr = " update T_RK_lotGoods set sts='2' where lot_id='" + lotId + "' and ProDucLotId='" + ProDucLotId + "' and goodsId='" + goodsId + "'  ";
            SystemDataObject.Instance.ExecuteSql(sqlStr);
        }


        /// <summary>
        /// 入库完成删除生产明细
        /// </summary>
        /// <param name="lotId"></param>
        public void DeleProductInfoInSts(string goodsId)
        {
            string sqlStr = " delete T_RK_lotGoods   where goodsId='" + goodsId + "'  ";
            SystemDataObject.Instance.ExecuteSql(sqlStr);
        }


        /// <summary>
        /// 入库完成删除生产表
        /// </summary>
        /// <param name="lotId"></param>
        public void DeleProductInSts(string ProDucLotId)
        {
            string sqlStr = " delete T_Rk_scLot  where ProDucLotId='" + ProDucLotId + "'   ";
            SystemDataObject.Instance.ExecuteSql(sqlStr);
        }
        // <summary>
        /// 入库完成删除生产明细 写入历史数据库
        /// </summary>
        /// <param name="barcode"></param>
        /// <param name="lotid"></param>
        public void InsertProductInfoBak(string goodsId,string lotid)
        {
            string sqlStr =@" insert into T_RK_lotGoods_bak select * from  T_RK_lotGoods  where
                                             goodsId='" + goodsId + "' and LOT_ID='" + lotid + "' ";

            SystemDataObject.Instance.ExecuteSql(sqlStr);
        }
        // <summary>
        /// 入库完成删除生产表 写入历史数据库
        /// </summary>
        /// <param name="barcode"></param>
        /// <param name="lotid"></param>
        public void InsertProductBak(string ProDucLotId)
        {
            string sqlStr =@" insert into T_Rk_scLot_BAK select * from  T_Rk_scLot  where
                                             ProDucLotId='" + ProDucLotId + "' ";

            SystemDataObject.Instance.ExecuteSql(sqlStr);
        }
        /// <summary>
        /// 更新主表Status
        /// </summary>
        /// <param name="lotId">生产批号</param>
        public void UpdateInSts(string ProDucLotId)
        {
            string sqlStr = " update T_ONSHELF set STATUS='1' where ProDucLotId='" + ProDucLotId + "'  ";
            SystemDataObject.Instance.ExecuteSql(sqlStr);
        }

        /// <summary>
        /// 查询该批次有多少需要入口的条码
        /// </summary>
        /// <returns></returns>
        public DataTable GetLocMisVolume(string ProdcutId)
        {

            string sqlStr = "select  count(*)a from T_ONGOODSSHELF where ProDucLotId='" + ProdcutId + "' ";
            return SystemDataObject.Instance.GetDataTable(sqlStr);
        }

        /// <summary>
        /// 获取上架单信息
        /// </summary>
        /// <param name="sqlWhere">查询条件t.instand in (" + station + ") and </param>
        /// <returns></returns>
        public DataTable GetUpShelfGoodsData(string station )
        {
            string sqlStr = @"select t.lot_id, t.GOODSID,t.LOCATION_ID,t.SHELF_NUM,t.ONDATE,
                            t.CTL,t.PlcID,t.ProDucLotId,t.goods_typeid,t.barcode,t.goodsname,t.baozhiqi,t.tasktype,  
                             (case t.STATUS when '0' then '未执行' when '1' then '已下发输送线' when '2' then '任务已下发堆垛机'   
                             when '10' then '堆垛机入库完成' end) as STATUS from T_ONGOODSSHELF t
                             order by t.ondate desc";


            DataTable table = SystemDataObject.Instance.GetDataTable(sqlStr);

            return table;
        }
     
        public DataTable GetConveyorModeData(string decid)
        {
            string sqlStr =@"select mode from T_ConveyorStatus   where ConveyorID='"+decid+"' ";


            DataTable table = SystemDataObject.Instance.GetDataTable(sqlStr);

            return table;

        }



        /// <summary>
        /// 获取上架单明细信息
        /// </summary>
        /// <param name="sqlWhere"></param>
        /// <returns></returns>
        public DataTable GetOnShelfGoodsData()
        {
            string sqlStr = @"select * from T_ONGOODSSHELF  where status='10' and LOCATION_ID IS NOT NULL  ";


            DataTable table = SystemDataObject.Instance.GetDataTable(sqlStr);

            return table;
        }

        public DataTable GetScan2Data(string code)
        {
            string sqlStr =@"select   *      from T_ONGOODSSHELF
                                      
                              
                               where UPGOODS_ID='"+ code + "' ";


            DataTable table = SystemDataObject.Instance.GetDataTable(sqlStr);

            return table;

        }

        /// <summary>
        /// 插入明细商品
        /// </summary>
        /// <param name="goodsInfo"></param>
        public void InsertGoodsInfoShelf(AutoRkGoodsInfo goodsInfo)
        {

            string sqlStr = "insert into T_ONGOODSSHELF(LOT_ID,GOODSID," +
                          "PROVIDER_ID,LOCATION_ID,STO_NUM,ACC_NUM,SHELF_NUM,STOCK_NUM, "
                          + "   ONSHELFUSERID,STORAGE_ID,STORAGE_AREA_ID," +
                          " ProDucLotId,HASVOLUME,BARCODE,CTL,CUSTOMER_ID," +
                          " PRODUCTION_DATE,UPGOODS_ID,UNIT,baozhiqi,HIGH,PlcID,Whseloc,GOODSNAME," +
                          " instand,STORAGE_MODE,wgh,decId,WAREHOUSING_ID,TASKTYPE,status)"
                          + "  values('" + goodsInfo.LOT_ID + "','" + goodsInfo.GOODSID.Trim() + "',"
                          + "   '" + goodsInfo.PROVIDER_ID + "','" + goodsInfo.LOCATION_ID + "',"
                          + "   '" + goodsInfo.STO_NUM + "','" + goodsInfo.ACC_NUM + "','" + goodsInfo.SHELF_NUM + "','" + goodsInfo.STOCK_NUM + "',"                    
                          + "    '" + goodsInfo.ONSHELFUSERID + "',"
                          + "    '" + goodsInfo.STORAGE_ID + "', '" + goodsInfo.STORAGE_AREA_ID + "',"
                          + "    '" + goodsInfo.ProDucLotId + "','" + goodsInfo.HASVOLUME + "','" + goodsInfo.BarCode + "','" + goodsInfo.CTL + "','" + goodsInfo.Customer_Id + "',"
                          + "    sysdate,'" + goodsInfo.UPGOODS_ID + "','" + goodsInfo.UNIT + "'," +
                          "'" + goodsInfo .baozhiqi+ "','"+goodsInfo.High+"','"+ goodsInfo.PlcID + "','"+ goodsInfo .Whseloc+ "'," +
                          "'"+ goodsInfo.GOODSNAME.Trim() + "','"+ goodsInfo.instand + "'," +
                          "'"+ goodsInfo .STORAGE_MODE+ "','"+ goodsInfo .WGH+ "','"+ goodsInfo .decid+ "','"+ goodsInfo .WAREHOUSING_ID+ "','"+goodsInfo.TASKTYPE + "','"+ goodsInfo.STATUS+ "')";
            SystemDataObject.Instance.ExecuteSql(sqlStr);

        }

        /// <summary>
        /// 写入历史数据库
        /// </summary>
        /// <param name="barcode"></param>
        /// <param name="lotid"></param>
        public void InsertUpGoodsBak(string ctl,string goodsId)
        {
            string sqlStr = @" insert into T_ONGOODSSHELF_BAK          (LOT_ID,GOODSID,PROVIDER_ID,LOCATION_ID,STO_NUM,ACC_NUM,SHELF_NUM,STOCK_NUM,  
                            ONDATE,ONSHELFUSERID,STORAGE_ID,STORAGE_AREA_ID,
                              ProDucLotId,HASVOLUME,BARCODE,CTL,CUSTOMER_ID,                             PRODUCTION_DATE,UPGOODS_ID,UNIT,baozhiqi,HIGH,
                              PlcID,Whseloc,GOODSNAME,instand,STORAGE_MODE,wgh,
                              GOODS_MEASURE_ID,TASKTYPE,PACKING_NUM,SCALE_UNIT)  
                              select LOT_ID,GOODSID,PROVIDER_ID,LOCATION_ID,                           STO_NUM,ACC_NUM,SHELF_NUM,STOCK_NUM,                            ONDATE,ONSHELFUSERID,STORAGE_ID,STORAGE_AREA_ID,
                              ProDucLotId,HASVOLUME,BARCODE,CTL,CUSTOMER_ID,                        PRODUCTION_DATE,UPGOODS_ID,UNIT,baozhiqi,HIGH,
                             PlcID,Whseloc,GOODSNAME,instand,STORAGE_MODE,wgh,
                             GOODS_MEASURE_ID,TASKTYPE,PACKING_NUM,SCALE_UNIT  from  T_ONGOODSSHELF  where
                                    ctl='" + ctl + "'  ";

            if(goodsId.Trim().Length==0)
            {
                sqlStr = string.Format(sqlStr, " and BARCODE='" + goodsId + "'");
            }
            else
            {
                sqlStr = string.Format(sqlStr, " ");
            }
            SystemDataObject.Instance.ExecuteSql(sqlStr.ToString());
        }


        /// <summary>
        /// 更新订单Status
        /// </summary>
        /// <param name="lotId"></param>
        /// <param name="barcode"></param>
        /// <param name="sts"></param>
        public void DeleteGoodInfo(string ctl,string goodsId)
        {
            string sqlStr = " delete T_ONGOODSSHELF   where  CTL='" + ctl + "' {0}  ";
            if(goodsId.Trim().Length>0)
            {
                sqlStr = string.Format(sqlStr, " and BARCODE='" + goodsId+"'");

            }
            else
            {
                sqlStr = string.Format(sqlStr, "  ");

            }
            SystemDataObject.Instance.ExecuteSql(sqlStr.ToString());
        }
        /// <summary>
        /// 删除数据库
        /// </summary>
        /// <param name="barcode"></param>
        /// <param name="lotid"></param>
        public void DelTaskGoodInfo(string lotId, string goodsId, string ProDucLotId)
        {

            string sqlStr = " delete T_ONGOODSSHELF where lot_id='" + lotId + "' and ProDucLotId ='" + ProDucLotId + "'and goodsId ='" + goodsId + "'";
            SystemDataObject.Instance.ExecuteSql(sqlStr);
        }
        /// <summary>
        /// 取消任务 删除上架任务
        /// </summary>
        /// <param name="barcode"></param>
        /// <param name="lotid"></param>
        public void DelTaskGoodInfo(string lotId)
        {

            string sqlStr = " delete T_ONGOODSSHELF where lot_id ='" + lotId + "'";
            SystemDataObject.Instance.ExecuteSql(sqlStr);
        }
        /// <summary>
        /// 取消任务 更改改批次的任务为下发
        /// </summary>
        /// <param name="lotId"></param>
        public void UpdateProductSts(string lot_id)
        {
            string sqlStr = " update T_RK_lotGoods set sts='0' where lot_id='" + lot_id + "' ";
            SystemDataObject.Instance.ExecuteSql(sqlStr);
        }
       
        /// <summary>
        /// 查询入库任务
        /// </summary>
        /// <returns></returns>
        public DataTable GetTaskInData(string sts)
        {

            string sqlStr = "select * from T_ONGOODSSHELF where sts='"+sts+"' ";
            return SystemDataObject.Instance.GetDataTable(sqlStr);
        }
        /// <summary>
        /// 查询改条码是否已经入库
        /// </summary>
        /// <returns></returns>
        public DataTable GetMisLocId(string goodsId)
        {

            string sqlStr = "select LOCATION_ID from T_MI_STOCK where goods_id='" + goodsId + "' ";
            return SystemDataObject.Instance.GetDataTable(sqlStr);
        }
        /// <summary>
        /// 查询根据库位查询入库任务
        /// </summary>
        /// <returns></returns>
        public DataTable GetTaskLocInTask(string locId)
        {

            string sqlStr = "select * from T_ONGOODSSHELF where LOCATION_ID='" + locId + "' and  sts='扫描'";
            return SystemDataObject.Instance.GetDataTable(sqlStr);
        }
        /// <summary>
        /// 删除单据
        /// </summary>
        /// <param name="Sheet_no"></param>
        /// <param name="Pdc_no"></param>
        public void DeleteTaskData(string Sheet_no, string Pdc_no)
        {
            string sqlStr = "delete  T_ONGOODSSHELF where LOT_ID='" + Sheet_no + "' and  GOODSID='"+ Pdc_no + "'";
             SystemDataObject.Instance.GetDataTable(sqlStr);

        }
        /// <summary>
        /// 删除单据
        /// </summary>
        /// <param name="Sheet_no"></param>
        /// <param name="Pdc_no"></param>
        public void DeleteTaskData(string ctl)
        {
            string sqlStr = "delete  T_ONGOODSSHELF where ctl='" + ctl + "'";
            SystemDataObject.Instance.ExecuteSqlCommit(sqlStr);

        }

        public void update_location(string ctl)
        {
            string sqlStr = "update T_BASE_STORAGE_AREA_LOCATION SET status=0 where LOCATION_ID=(SELECT LOCATION_ID FROM T_ONGOODSSHELF WHERE  ctl='" + ctl + "')";
            SystemDataObject.Instance.ExecuteSqlCommit(sqlStr);
            string sqlStr1 = "delete  T_ONGOODSSHELF where ctl='" + ctl + "'";
            SystemDataObject.Instance.ExecuteSqlCommit(sqlStr1);
        }
        /// <summary>
        /// 更新上架的物料信息
        /// </summary>
        /// <param name="ctl"></param>
        /// <param name="locId"></param>
        /// <param name="goodsId"></param>
        /// <param name="sheNum"></param>
        public void UpadateTaskData(string ctl,string locId,string barcode,string pskw,decimal sheNum,decimal psNum)
        {
            string sqlStr = " update T_ONGOODSSHELF set SHELF_NUM='"+ sheNum + "',LOCATION_ID='"+locId+"',status='2'" +
                " DAMAGE_LOCATION ='"+ pskw + "',DAMAGE_NUM='"+ psNum + "' where CTL='" + ctl+ "' and BARCODE='" + barcode + "'";
              SystemDataObject.Instance.ExecuteSqlCommit(sqlStr);
        }

    }
}