using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

using WMS.DBUtility;
using WMS.IData.ICK;
using WMS.IData;using Oracle.ManagedDataAccess.Client;
using WMS.Model.CK;
using WMS.Model.RK;
using System.Diagnostics;

namespace WMS.SqlServerData.CKData
{
    public class PickingWaveGoodsData : IPickingWaveGoods
    {

        public bool getPickData(string locid)
        {
            
            string sqlstr = "select * from T_CK_PICKINGWAVEGOODS where LOC_ID='"+locid+ "' ";
            DataTable dt= SystemDataObject.Instance.GetDataTable(sqlstr);
            if (dt.Rows.Count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }



        public bool GetPickData_BARCODE (string BARCODE)
        {

            string sqlstr = "select * from T_CK_PICKINGWAVEGOODS where BARCODE='" + BARCODE + "' ";
            DataTable dt = SystemDataObject.Instance.GetDataTable(sqlstr);
            if (dt.Rows.Count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }

        public bool getPickDataCount()
        {

            //string sqlstr = "select count(*) from t_ck_pickingwavegoods where length(pickingid)<30 and length(outstand)<3";
            string sqlstr = "select distinct CTL from t_ck_pickingwavegoods where STATUS in(0,1)";
            DataTable dt = SystemDataObject.Instance.GetDataTable(sqlstr);
            if (dt.Rows.Count >9)
            {
                return true;
            }
            else
            {
                return false;
            }

        }


        public bool getPickDataCount(int num)
        {

            //string sqlstr = "select count(*) from t_ck_pickingwavegoods where length(pickingid)<30 and length(outstand)<3";
            string sqlstr = "select distinct CTL from t_ck_pickingwavegoods where STATUS in(0,1)";
            DataTable dt = SystemDataObject.Instance.GetDataTable(sqlstr);
            if (dt.Rows.Count >= num)
            {
                return true;
            }
            else
            {
                return false;
            }

        }
        public bool getLocS(string locid)
        {

            string sqlstr = "select * from t_base_storage_area_location where location_id='" + locid + "' and status='0'";
            DataTable dt = SystemDataObject.Instance.GetDataTable(sqlstr);
            if (dt.Rows.Count > 0)
            {
                return false;
            }
            else
            {
                return true;
            }

        }

        public string getYckLoc(string layer)
        {

            string sqlstr = "select location_id from t_base_storage_area_location where storage_id='JXJWYCK' and status='0' and layer1='"+layer+ "' order by row1 desc ";
            DataTable dt = SystemDataObject.Instance.GetDataTable(sqlstr);
            if (dt.Rows.Count > 0)
            {
                return dt.Rows[0][0].ToString();
            }
            else
            {
                return "";
            }

        }
        public DataTable getDecid(string decid)
        {

            string sqlstr = "select * from T_DECNAME where decid='" + decid + "'";
            return SystemDataObject.Instance.GetDataTable(sqlstr);

        }
        public DataTable getRemark(string locid)
        {

            string sqlstr = "select * from t_base_storage_area_location where location_id='" + locid + "'";
            return SystemDataObject.Instance.GetDataTable(sqlstr);

        }
        public DataTable getyck(string remark,string layer)
        {

            string sqlstr = "select DISTINCT D.LOCATION_ID ,D.STORAGE_ID,D.GOODS_ID,D.SHELVES_NUM,D.CTL,D.PROVIDER_ID,D.STORAGE_ID,D.SORT FROM (select a.*,B.SORT from t_mi_stock a left join t_base_storage_area_location b on a.location_id=b.location_id where b.remark='" + remark+"' and b.layer1='"+layer+ "' and b.storage_id='JXJWYCK'  )  D ORDER BY SORT DESC";
            return SystemDataObject.Instance.GetDataTable(sqlstr);

        }
        public DataTable getYK(string ctl)
        {

            string sqlstr = "select * from t_ck_pickingwavegoods where CTL='"+ ctl + "' and status in ('0','1','2') ";
            return SystemDataObject.Instance.GetDataTable(sqlstr);

        }
        /// <summary>
        /// 根据出库单查询拣选明细
        /// </summary>
        /// <param name="outId"></param>
        /// <returns></returns>
        public DataTable GetOutDataId(string outId)
        {
            string sqlStr = " select top 1 outStand from T_CK_PICKINGWAVEGOODS    where out_id='" + outId + "'";
            return SystemDataObject.Instance.GetDataTable(sqlStr);
        }
        public DataTable GetTaskOutCtStand(string stand)
        {
            string sqlStr = "select count(*)a from T_CK_PICKINGWAVEGOODS where outStand='" + stand+"'";
            return SystemDataObject.Instance.GetDataTable(sqlStr);
        }

        /// <summary>
        /// 根据托盘删除拣选任务
        /// </summary>
        /// <param name="ctl"></param>
        public void DelCtlPickGoods(string ctl, string STORAGE_ID)
        {
            string sqlStr = " Delete T_CK_PICKINGWAVEGOODS where ctl='" + ctl + "' and STORAGE_ID='" + STORAGE_ID + "'";
            SystemDataObject.Instance.ExecuteSql(sqlStr);
        }
        /// <summary>
        /// 获取站台
        /// </summary>
        /// <returns></returns>
        public DataSet GetStation(string STORAGE_ID)
        {
            string sqlstr = "select t.ConveyorID from T_ConveyorStatus t where" +
                " t.optype = '2' and   STORAGE_ID='" + STORAGE_ID + "'  and t.ConveyorID not in " +
                "(select outStand from T_CK_PICKINGWAVEGOODS   where STORAGE_ID='"+ STORAGE_ID + "' group by outStand);" +
                "select count(OUT_ID)a,outStand from T_CK_PICKINGWAVEGOODS where STORAGE_ID='"+ STORAGE_ID + "' and outStand is not null group by outStand order by a "; 
            return SystemDataObject.Instance.GetDataSet(sqlstr);
        }

        public DataTable GetLocaStationAllData(string locID)
        {
            string sqlStr =@" select outStand, LOCATION_ID,[row], cln, layer from[dbo].[T_BASE_STORAGE_AREA_LOCATION] where LOCATION_ID = '" + locID + "'"
                        + " union ALL "
                        + " select outStand1, LOCATION_ID,[row], cln, layer from[dbo].[T_BASE_STORAGE_AREA_LOCATION] where LOCATION_ID = '" + locID + "'"
                        + " union ALL "
                       + " select outStand3, LOCATION_ID,[row], cln, layer from[dbo].[T_BASE_STORAGE_AREA_LOCATION] where LOCATION_ID = '" + locID + "' and   outStand3!=0 ";


            return SystemDataObject.Instance.GetDataTable(sqlStr);
        }
        public DataTable GetTaskdataHand()
        {
            string sqlStr = @"  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 '出库' when '2' then '移库' end ck_type,
                  customermemberid, plcid, towhseloc,
                outstand, CASE WHEN STATUS = '0' THEN '未下发' WHEN STATUS = '1' THEN '正在执行' WHEN STATUS = '6'
                THEN '堆垛机过账完成' WHEN STATUS = '9' THEN '手持拣货完成'  END as STATUS from T_CK_PICKINGWAVEGOODS order by WARE_DATE";
            return SystemDataObject.Instance.GetDataTable(sqlStr);
        }

        public DataTable GetDataT_ck_orderDetall()
        {
            string sqlStr = "select * from T_CK_ORDERDETAIL where sts='8' order by ORDER_ID, endtime ";
            return SystemDataObject.Instance.GetDataTable(sqlStr);
        }

        public DataTable GetTaskOutStatus()
        {
            //PDA拣货完成状态9，EBS出库完成改成10， 等待EBS拣货完成才能扣减库存--目前改成手持完成后就ok
            string sqlStr = "select * from T_CK_PICKINGWAVEGOODS where status='9' ";
            return SystemDataObject.Instance.GetDataTable(sqlStr);
        }

        public DataTable GetTaskOutStatus_0()
        {
           //查询当前未下发的出库任务
            string sqlStr = "select * from T_CK_PICKINGWAVEGOODS where status='0' order by ware_date asc ";
            return SystemDataObject.Instance.GetDataTable(sqlStr);
        }

        public DataTable GetTaskOutStatus_Empty()
        {
            //空箱出库完成
            string sqlStr = "select * from T_CK_PICKINGWAVEGOODS where status='6'and GOOD_ID='000000' ";
            return SystemDataObject.Instance.GetDataTable(sqlStr);
        }

        public DataTable GetTaskMoveStatus()
        {
            //移库完成
            string sqlStr = "select * from T_CK_PICKINGWAVEGOODS where status='12' ";
            return SystemDataObject.Instance.GetDataTable(sqlStr);
        }

        public DataTable GetTaskLocOutStatus(string loc)
        {
            string sqlStr = "select  * from T_CK_PICKINGWAVEGOODS where LOC_ID ='" + loc + "'";
            return SystemDataObject.Instance.GetDataTable(sqlStr);
        }





        public DataTable GetLocHand()
        {
            string sqlStr = "select * from T_BASE_STORAGE_AREA_LOCATION where STATUS='1' order by sort";
            return SystemDataObject.Instance.GetDataTable(sqlStr);
        }
        //public void InsertIoErp(Model.Pak_io_complete ioComplete)//Ymd,
        //{
        //    string sqlStr =@"insert into pak_io_complete(Sheet_no,Pdc_no,Pdc_name,Io_type,
        //                      PO,Qty,Wrk_no,status,Io_status,StoreBaseUom,SrcDoc_ID,SrcDocLine_ID,
        //                      AllDoc_Type,Version,Org,ItemMaster,SPEC,Memo,Appe_user,
        //                      Appe_time,Modi_user,Modi_time,Crtd_User,Lupd_User,
        //                                         CompanyID,SiteID,Batnbr,Inventory_Line_ID,UnitDesc,Whseloc,ToWhseloc,Tooling,Manufacturedate,
        //                      PalletID,Location) values";
        //    sqlStr = sqlStr + " ('" + ioComplete.Sheet_no + "','" + ioComplete.Pdc_no + "','" + ioComplete.Pdc_name + "',"
        //                    + "'"+ ioComplete .Io_type+ "','"+ ioComplete .Po+ "','"+ ioComplete .Qty+ "','"+ ioComplete .Wrk_no+ "',"
        //                    +  "'"+ ioComplete.Status + "','" + ioComplete.Io_status + "','" + ioComplete .StoreBaseUom+ "','"+ ioComplete .SrcDoc_ID+ "',"
        //                    +" '"+ ioComplete .SrcDocLine_ID+ "','"+ ioComplete .AllDoc_Type + "','"+ ioComplete.Version+ "',"
        //                    + " '"+ ioComplete.Org + "','"+ ioComplete.ItemMaster + "','"+ ioComplete .SPEC+ "','"+ ioComplete .Memo+ "',"
        //                    +" '"+ ioComplete.Appe_user + "',convert(varchar(200),'" + ioComplete.Appe_time + "',103),'" + ioComplete .Modi_user+ "',"
        //                    + " convert(varchar(200),'" + ioComplete.Modi_time + "',103),'" + ioComplete.Crtd_User + "',"
        //                    + "'"+ ioComplete.Lupd_User + "', '" + ioComplete .CompanyID+ "',"
        //                    +"'"+ ioComplete.SiteID + "','"+ ioComplete .Batnbr+ "','"+ ioComplete .Inventory_Line_ID+ "',"
        //                    +"'"+ ioComplete.UnitDesc + "','"+ ioComplete .Whseloc+ "','"+ ioComplete .ToWhseloc+ "',"
        //                    +"'"+ ioComplete.Tooling + "',getdate(),'"+ ioComplete .PalletID+ "','" +ioComplete.ASRS_Location + "')";
                 

        //    SystemDataObject.Instance.ExecuteSql(sqlStr);
        //}

        /// <summary>
        /// 任务下发到PLC写重量
        /// </summary>
        /// <param name="locId"></param>
        public void UpDateWghMisBak(string barcode,string wgh)
        {
            string sqlStr = "update T_MI_STOCK_BACK set WGH='" + wgh + "' where BARCODE='" + barcode + "' ";
            SystemDataObject.Instance.ExecuteSql(sqlStr);
        }

        public void UpDateSort(string locid, string sort)
        {
            string sqlStr = "update t_base_storage_area_location set sort='" + sort + "' where location_id='" + locid + "' ";
            SystemDataObject.Instance.ExecuteSql(sqlStr);
        }
        /// <summary>
        /// 任务下发到PLC将Status置为完成
        /// </summary>
        /// <param name="locId"></param>
        public void UpdateStsPIckEnd(string ProDucLotId,string sts)
        {
            string sqlStr = "update T_CK_PICKINGWAVEGOODS  set STS='"+sts+"' where ProDucLotId='" + ProDucLotId + "' ";
            SystemDataObject.Instance.ExecuteSql(sqlStr);
        }
        /// <summary>
        /// 更改拣选的数量
        /// </summary>
        /// <param name="pickId"></param>
        /// <param name="goodsId"></param>
        /// <param name="picnum"></param>
        public void UpdaPickNum(string pickId, string goodsId, decimal picnum)
        {
            string sqlStr = "update T_CK_PICKINGWAVEGOODS  set GOODS_NUM_SJ=GOODS_NUM_SJ+" + picnum + " " +
                "  where PICKINGID='" + pickId + "' and goodsId='"+goodsId+"' ";
            SystemDataObject.Instance.ExecuteSql(sqlStr);
        }
        /// <summary>
        /// 出库更新库位表为空闲
        /// </summary>
        /// <param name="locId"></param>
        public void UpdateLocSts(string locId)
        {
            string sqlStr = "update T_BASE_STORAGE_AREA_LOCATION  set HASVOLUME='0',STATUS='0' where LOCATION_ID='" + locId + "' ";
            SystemDataObject.Instance.ExecuteSql(sqlStr);
        }
        /// <summary>
        /// 获取捡货明细
        /// </summary>
        /// <returns>捡货明细</returns>
        public DataTable GetPickingWaveGoodsDT(PickingWaveGoodsModel model)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.AppendLine("select 																");
            strSql.AppendLine("  a.PICKINGID,	   --拣货号                                         ");
            strSql.AppendLine("  a.OUT_ID,         --出库通知号                                     ");
            strSql.AppendLine("  a.PUTIN_ID,       --入库批次号                                     ");
            strSql.AppendLine("  a.DOCCTNUMBER,    --单据编号（调整填写调整号，入库填写入库号）     ");
            strSql.AppendLine("  a.GOOD_ID,        --GoodsNumber                                       ");
            strSql.AppendLine("  a.LOC_ID,         --库位编号                                       ");
            strSql.AppendLine("  a.GOODS_NUM,      --本波次拣货数量                                 ");
            strSql.AppendLine("  a.SPECIFICATION,  --规格                                           ");
            strSql.AppendLine("  a.PRODUCTION_DATE,--生产日期                                       ");
            strSql.AppendLine("  a.WARE_DATE,      --上架时间                                       ");
            strSql.AppendLine("  a.PROVIDER_ID,    --供应商编号                                     ");
            strSql.AppendLine("  a.REMARK,         --备注                                           ");
            strSql.AppendLine("  a.UNIT,           --UNIT                                           ");
            strSql.AppendLine("  a.SCALE_UNIT,     --重量UNIT                                       ");
            strSql.AppendLine("  b.goods_name,	   --商品名称                                       ");
            strSql.AppendLine("  a.MISTOCK_NUM,    --某库位某商品库存数                             ");
            strSql.AppendLine("  a.PICKING_NUM,	   --某商品要捡货总数                               ");
            strSql.AppendLine("  a.PRICE,          --标准价格                                       ");
            strSql.AppendLine("  c.provider_name,   --供应商名称                                     ");
            strSql.AppendLine("  a.ctl,   --供应商名称                                            ");
            strSql.AppendLine("  a.barcode,   --供应商名称                                     ");
            strSql.AppendLine("  a.ProDucLotId,    --供应商名称                                     ");
            strSql.AppendLine("  (case   a.[status]  when   '0' then '未执行' when '1'  then '下发成功' when '3' then '需要拣货' when '4' then '完成' end) as [status]                                     ");
            strSql.AppendLine("FROM                                                                 ");
            strSql.AppendLine("  T_CK_PICKINGWAVEGOODS a left join t_base_goods b                   ");
            strSql.AppendLine("  on a.good_id = b.goods_id                                          ");
            strSql.AppendLine("  left join t_base_provider_dictionary c                             ");
            strSql.AppendLine("  on a.provider_id = c.provider_id                                   ");
            strSql.AppendLine("where 1 = 1 ");
            if (!string.IsNullOrEmpty(model.PICKINGID))
            {
                strSql.AppendLine(" and a.PICKINGID = '" + model.PICKINGID + "' ");
            }
            if (!string.IsNullOrEmpty(model.OUT_ID))
            {
                strSql.AppendLine(" and a.OUT_ID = '" + model.OUT_ID + "' ");
            }
            if (!string.IsNullOrEmpty(model.PUTIN_ID))
            {
                strSql.AppendLine(" and a.PUTIN_ID = '" + model.PUTIN_ID + "' ");
            }
            if (!string.IsNullOrEmpty(model.DOCCTNUMBER))
            {
                strSql.AppendLine(" and a.DOCCTNUMBER = '" + model.DOCCTNUMBER + "' ");
            }
            if (!string.IsNullOrEmpty(model.GOOD_ID))
            {
                strSql.AppendLine(" and a.GOOD_ID = '" + model.GOOD_ID + "' ");
            }
            if (!string.IsNullOrEmpty(model.LOC_ID))
            {
                strSql.AppendLine(" and a.LOC_ID = '" + model.LOC_ID + "' ");
            }
            if (!string.IsNullOrEmpty(model.LOC_ID))
            {
                strSql.AppendLine(" and a.LOC_ID = '" + model.LOC_ID + "' ");
            }
            if (!string.IsNullOrEmpty(model.status))
            {
                strSql.AppendLine(" and a.status = '" + model.status + "' ");
            }
            strSql.AppendLine(" order by a.PICKINGID");

            return SystemDataObject.Instance.GetDataTable(strSql.ToString());
        }
        /// <summary>
        /// 获取明细
        /// </summary>
        public DataTable LtkgetScanData(string localId, string goodsId)
        {

            string sqlStr = "select * from   T_CK_PICKINGWAVEGOODS where LOC_ID='" + localId + "' and good_Id='" + goodsId + "' ";
            return SystemDataObject.Instance.GetDataTable(sqlStr.ToString());

        }
        ///
        /// <summary>
        /// 获取拣货的明细 outStand='" + outStand + "' and 
        /// </summary>
        public DataTable LtkgetPickData(string outStand)
        {

            string sqlStr = "select t.plcid,t.good_id,t.LOC_ID,t.GOODS_NUM,t.OUT_ID,t.REMARK,t.OUT_ID,t.ctl," +
                            "t.PICKING_NUM,t.PACKING_NUM,c.GOODS_NAME,b.ConveyorID as outStand,       " +
                           " c.BARCODE from T_CK_PICKINGWAVEGOODS t left join T_BASE_GOODS c on t.GOOD_ID =c.GOODS_ID join  " +
                            "T_ConveyorStatus b on t.plcid =b.PlcID where ConveyorID='"+outStand+"'   and t.status = '3'";
            return SystemDataObject.Instance.GetDataTable(sqlStr.ToString());
 
        }
       /// <summary>
       /// 
       /// </summary>
       /// <param name="goodsId"> 物料编号</param>
       /// <param name="PACKING_NUM">出库数量</param>
       /// <param name="STORAGE_ID">子库编号</param>
       /// <returns></returns>
        public DataTable GetLocDataNo(string goodsId, decimal PACKING_NUM, string STORAGE_ID)
        {

            string sqlStr = @"select  a.LOCATION_ID , a.ctl, (a.SHELVES_NUM-a.OCCUPY_NUM) as  SHELVES_NUM,b.HASVOLUME,b.outStand,a.PUTIN_ID,a.goods_sku,a.ware_date,a.barcode,a.STS
                        from T_MI_STOCK a   
                      join T_BASE_STORAGE_AREA_LOCATION b on   a.LOCATION_ID = b.LOCATION_ID
                      where a.goods_id='" + goodsId + "' and a.OCCUPY_NUM< a.SHELVES_NUM  and  a.storage_id='"+ STORAGE_ID + "' and a.sts='0'   order by a.goods_sku,a.ware_date asc ";

            return SystemDataObject.Instance.GetDataTable(sqlStr);
        }
        public DataTable GetErpCm(string locid)
        {
            string sqlStr = "select * from pak_wait_out  where  rtrim(Location)='" + locid + "' ";
            return SystemDataObject.Instance.GetDataTable(sqlStr);
        }
        /// <summary>
        /// 获取 ERP订单数据
        /// </summary>
        /// <returns></returns>
        public DataTable GetDataErpData()
        {
            string sqlStr = "select * from pak_wait_out  where status ='0' order by Location ";
            return SystemDataObject.Instance.GetDataTable(sqlStr);
        }

        public bool getPickDataProductionDate(string goodid, string production_date)
        {

            string sqlstr = "SELECT * FROM T_MI_STOCK where GOODS_ID='" + goodid + "' and GOODS_SKU<'"+ production_date + "' and sts='0' ";
            DataTable dt = SystemDataObject.Instance.GetDataTable(sqlstr);
            if (dt.Rows.Count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }
        public bool GetDataErpData(string ORDER_ID)
        {
            bool sts = false;
            string sqlStr = "select * from T_CK_ORDERS where ORDER_ID='" + ORDER_ID + "' ";
            DataTable dt= SystemDataObject.Instance.GetDataTable(sqlStr);
            if (dt != null)
            {
                if (dt.Rows.Count > 0)
                {
                    sts= true;
                }
            }
            return sts;
        }


        public bool DEL_T_CK_ORDERS(string loc,string goodid)
        {
            string sqlStr = "delete  from T_MI_STOCK  where LOCATION_ID='"+ loc + "'and  GOODS_ID='"+goodid+"' ";
            if (SystemDataObject.Instance.ExecuteSqlCommit(sqlStr) > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public bool Insert_T_CK_ORDERS(string sqlStr)
        {
            SystemDataObject.Instance.ExecuteSql(sqlStr);
            return true;
        }


        public bool insert_t_ck_ordersDetall(string sqlStr)
        {
            SystemDataObject.Instance.ExecuteSql(sqlStr);
            return true;
        }

        public bool update_t_ck_ordersDetall(string ORDER_ID, string goodsID)
        {
            string sqlStr = "  UPDATE T_CK_ORDERDETAIL SET sts='0' WHERE ORDER_ID='"+ ORDER_ID + "' AND GOOD_ID='"+ goodsID + "'";
            SystemDataObject.Instance.ExecuteSql(sqlStr);
            return true;
        }

        public bool delete_t_ckorder(string ORDER_ID)
        {
            string sqlStr = "  DELETE FROM  T_CK_ORDERDETAIL  WHERE ORDER_ID='" + ORDER_ID + "' ;  DELETE FROM T_CK_ORDERS WHERE ORDER_ID='" + ORDER_ID + "'";
            SystemDataObject.Instance.ExecuteSql(sqlStr);
            return true;
        }




        /// <summary>
        /// 更新wms数据状态
        /// </summary>
        /// <param name="Sheet_no"></param>
        /// <param name="Pdc_no"></param>
        /// <param name="PalletID"></param>
        public void SerLocWaitInSts(string ctl, string goodsid, string barcode,string GOODS_NUM_SJ,string out_id,string status,string GoodsMeaSure,string PACKING_NUM)
        {
            string sqlStr = "  Update  T_CK_PICKINGWAVEGOODS " +
                "  set Status = '"+ status + "',GOODS_NUM_SJ="+ GOODS_NUM_SJ + ", barcode='"+ barcode + "', ctl='" + ctl + "' ,PACKING_NUM='"+ PACKING_NUM + "',GOODS_MEASURE_ID='" + GoodsMeaSure + 
                "  where   GOOD_ID='" + goodsid + "' and OUT_ID='"+out_id+"'";

            SystemDataObject.Instance.ExecuteSqlCommit(sqlStr);
        }
        /// <summary>
        /// 获取拣货的明细
        /// </summary>
        public void  UpdateErpData(string CK_ID,string goodsid)
        {

            string sqlStr = "update      T_CK_ORDERDETAIL set sts='3',endtime=getdate() where ORDER_ID='" + CK_ID + "' and GOOD_ID='"+ goodsid + "'";
              SystemDataObject.Instance.ExecuteSql(sqlStr);

        }
        /// <summary>
        /// 获取拣货的明细
        /// </summary>
        public DataTable GetPickDataBarcode(string barcode)
        {

            string sqlStr = "select *  from   T_CK_PICKINGWAVEGOODS where BARCODE='" + barcode + "' ";
            return SystemDataObject.Instance.GetDataTable(sqlStr.ToString());

        }
        /// <summary>
        /// 获取拣货单明细是否已经删除
        /// </summary>
        public DataTable GetPickGoodsData(string ctl, string STORAGE_ID)
        {

            string sqlStr = "select plcid,status from   T_CK_PICKINGWAVEGOODS where ctl='" + ctl + "'  and STORAGE_ID='"+ STORAGE_ID + "'";
            return SystemDataObject.Instance.GetDataTable(sqlStr.ToString());

        }

        public DataTable GetGoodsArea (string goodsId )
        {

            string sqlStr = "select a.shipping_length,a.shipping_width,a.pcs_number from cux_wms_system_items_intf_zh a  where a.segment1='"+ goodsId + "'";
            return SystemDataObject.Instance.GetDataTable(sqlStr.ToString());

        }





        public void DeleteErpData(string pickingid)
        {
            string sqlStr = "delete  T_CK_PICKINGWAVEGOODS where BARCODE='" + pickingid + "' ";
             SystemDataObject.Instance.ExecuteSql(sqlStr.ToString());
        }
        /// <summary>
        /// 获取拣货单明细是否已经删除
        /// </summary>
        public DataTable HandGoodsSts(string locid)
        {

            string sqlStr = "select  STATUS from   T_CK_PICKINGWAVEGOODS where LOC_ID='" + locid + "' ";
            return SystemDataObject.Instance.GetDataTable(sqlStr.ToString());

        }
        public void DelHandGoods(string locid)
        {
            string sqlStr = "delete T_CK_PICKINGWAVEGOODS where LOC_ID='"+ locid + "';update  T_BASE_STORAGE_AREA_LOCATION set STATUS='1',HASVOLUME='1' where LOCATION_ID='" + locid + "'";
            SystemDataObject.Instance.ExecuteSqlCommit(sqlStr);
        }
        public void InsertHandGoods(PickingWaveGoodsModel model)
        {
            string sqlStr = "insert into T_CK_PICKINGWAVEGOODS(PICKINGID,GOOD_ID,OUT_ID,LOC_ID,plcId,typests,SCALE_UNIT,  PICKING_NUM,GOODS_NUM_SJ)" +
                "values('hd001','','','" + model.LOC_ID + "','" + model.plcId + "','1','"+ model .SCALE_UNIT+ "','1','1')  ";//    ;update  T_BASE_STORAGE_AREA_LOCATION set STATUS='0',HASVOLUME='0' where LOCATION_ID='" + model.LOC_ID + "'
            SystemDataObject.Instance.ExecuteSql(sqlStr);
        }
        /// <summary>
        /// 插入捡货明细信息
        /// </summary>
        /// <param name="PickingWaveGoodsModel">捡货明细Model</param>
        /// <returns>捡货明细编号</returns>
        public void InsertPickingWaveGoods(PickingWaveGoodsModel model)
        {
            //string orderstr = "CK" + DateTime.Now.ToString("yyyyMMddHHmmssfff");
            //PickingWaveGoodsModel or_Model = new PickingWaveGoodsModel();
            //or_Model.PICKINGID = tb.Rows[0]["Docctnumber"].ToString(); //销售订单
            //or_Model.PUTIN_ID = orderstr;
            //or_Model.OUT_ID = orderstr;
            //or_Model.GOOD_ID = tb.Rows[0]["GOODS_ID"].ToString();
            //or_Model.LOC_ID = tb.Rows[0]["LOCATION_ID"].ToString();
            //or_Model.WARE_DATE = Convert.ToDateTime(DateTime.Now.ToString());
            //or_Model.PICKING_NUM = Convert.ToDecimal(tb.Rows[0]["SHELVES_NUM"].ToString());//SHELVES_NUM
            //or_Model.BARCODE = tb.Rows[0]["BARCODE"].ToString();
            //or_Model.CTL = tb.Rows[0]["CTL"].ToString();
            //or_Model.status = "0";
            ////or_Model.outStand = this.comboBox1.Text.Trim();
            //or_Model.typests = "1";
            //or_Model.CUSTOMER_ID = tb.Rows[0]["CUSTOMER_ID"].ToString();
            //or_Model.GOODS_NUM_SJ = Convert.ToDecimal(tb.Rows[0]["SHELVES_NUM"].ToString());
            //or_Model.STORAGE_AREA_ID = tb.Rows[0]["Storage_Id"].ToString();//子库编码


            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine("insert into T_CK_PICKINGWAVEGOODS(");
            strSql.AppendLine("PICKINGID,OUT_ID,PUTIN_ID,DOCCTNUMBER,GOOD_ID,LOC_ID,");
            strSql.AppendLine(" GOODS_NUM,SPECIFICATION,PROVIDER_ID,UNIT,REMARK,");
            strSql.AppendLine(" SCALE_UNIT,MISTOCK_NUM,PICKING_NUM,PRICE,STORAGE_AREA_ID,STORAGE_ID,CTL," +
                "GOODS_NUM_SJ,ProDucLotId,BARCODE,plcid,GOODS_NAME,outStand,typests,PACKING_NUM,status,ware_date,CUSTOMER_ID,PORT,CUSTOMERMEMBERID)");
            strSql.AppendLine(" values (");
            strSql.AppendLine("'" + model.PICKINGID + "','" + model.OUT_ID + "','" + model.PUTIN_ID + "','" + model.DOCCTNUMBER + "','" + model.GOOD_ID + "',");
              strSql.AppendLine("'"+model.LOC_ID+"','"+model.GOODS_NUM+"','"+model.SPECIFICATION+"','"+model.PROVIDER_ID+"','"+model.UNIT+"','"+model.REMARK+"',");
            strSql.AppendLine(" '"+model.SCALE_UNIT+"','"+model.MISTOCK_NUM+"','"+model.PICKING_NUM+"','"+model.PRICE+"','"+model.STORAGE_AREA_ID + "','"+model.STORAGE_ID+"',");
              strSql.AppendLine(" '" + model.CTL + "','"+model.GOODS_NUM_SJ+"','"+model.ProDucLotId+"','"+model.BARCODE+"','"+model.plcId+"','"+model.GOODS_NAME+"'," +
                  "'"+model.outStand+"','"+model.typests+"','"+ model .PACKING_NUM+ "','"+model.status+ "',to_date('" + model.WARE_DATE+ "','yyyy/MM/dd hh24:mi:ss'),'"+ model.CUSTOMER_ID + "','"+ model.PORT+ "','"+ model.CUSTOMERMEMBERID + "')"); 
           
            SystemDataObject.Instance.ExecuteSql(strSql.ToString());

        }
        public void InsertTMISTOCK(string loc_id)
        {


            string sqlString = "\n" +
            "insert into t_mi_stock\n" +
            "  (storage_id,\n" +
            "   area_id,\n" +
            "   location_id,\n" +
            "   goods_id,\n" +
            "   shelves_num,\n" +
            "   production_date,\n" +
            "   ware_date,\n" +
            "   docctnumber,\n" +
            "   ctl,\n" +
            "   barcode,\n" +
            "   sts)\n" +
            "values\n" +
            "  ('JXJW',\n" +
            "   '3001',\n" +
            "   '"+loc_id+"',\n" +
            "   '111111',\n" +
            "   '1',\n" +
            "   sysdate,\n" +
            "   sysdate,\n" +
            "   '111111',\n" +
            "   '111111',\n" +
            "   '111111',\n" +
            "   '0')";


            SystemDataObject.Instance.ExecuteSql(sqlString);

        }
        public void InsertCK(string pickingid, string oldloc, string newloc,string plcid)
        {
            string sqlStr = "insert into T_CK_PICKINGWAVEGOODS (good_id,pickingid,loc_id,typests，status,outstand,plcid,ware_date,storage_id) values ('000','" + pickingid + "','" + oldloc + "','移库出库','1','" + newloc + "','" + plcid + "',to_date('" + DateTime.Now + "','YYYY-MM-DD HH24:MI:SS'),'JXJW')";

            SystemDataObject.Instance.ExecuteSql(sqlStr);
        }
        /// <summary>
        /// 删除一个捡货单的捡货商品明细
        /// </summary>
        /// <param name="pickingID">捡货单ID</param>
        public void UpdatePickingWaveSlGoods(string localtion,string goodsId,string number ,string puid)
        {
            string sqlStr = "update T_CK_PICKINGWAVEGOODS set GOODS_NUM_SJ='" + number + "' where  GOOD_ID='" + goodsId + "' and  LOC_ID='" + localtion + "' and PUTIN_ID='" + puid + "'";

            SystemDataObject.Instance.ExecuteSqlCommit(sqlStr.ToString());
        }
        ///// <summary>
        ///// 删除一个捡货单的捡货商品明细
        ///// </summary>
        ///// <param name="pickingID">捡货单ID</param>
        //public void DeletePickingWaveGoods(string pickingID)
        //{
        //    StringBuilder strSql = new StringBuilder();
        //    strSql.AppendLine("delete from T_CK_PICKINGWAVEGOODS ");
        //    strSql.AppendLine(" where PICKINGID=:PICKINGID");
        //    OracleParameter[] parameters = {
        //            new OracleParameter(":PICKINGID",OracleDbType.Varchar2)			};
        //    parameters[0].Value = pickingID;

        //    SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters); 
        //}
        /// <summary>
        /// 删除一个捡货单的捡货商品明细
        /// </summary>
        /// <param name="BARCODE"></param>
        public void DeletePickingWaveGoods(string LOC_ID,string out_id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine("delete from T_CK_PICKINGWAVEGOODS ");
            strSql.AppendLine(" where LOC_ID='"+LOC_ID+ "'  ");//and OUT_ID='"+ out_id + "'

            SystemDataObject.Instance.ExecuteSql(strSql.ToString());
        }
        public void DeletePickingWaveGoodsOut(string out_id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine("delete from T_CK_PICKINGWAVEGOODS ");
            strSql.AppendLine(" where out_id=:PICKINGID ");
            OracleParameter[] parameters = {
					new OracleParameter(":PICKINGID",OracleDbType.Varchar2)
                 };
            parameters[0].Value = out_id;
          
            SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 出库时降库存锁住
        /// </summary>
        /// <param name="barcode"></param>
        public void UpdateMisStsLock(string barcode)
        {
            string sqlstr = " update T_MI_STOCK set ABLE_NUM =1 where barcode='" + barcode + "'";

            SystemDataObject.Instance.ExecuteSql(sqlstr);
        }
        /// <summary>
        /// 删除一个捡货单的捡货商品明细
        /// </summary>
        /// <param name="pickingID">捡货单ID</param>
        public void DeleteProDucLotIdGoods(string ProDucLotId, string BARCODE, string localId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine("delete from T_CK_PICKINGWAVEGOODS ");
            strSql.AppendLine(" where ProDucLotId=:ProDucLotId and  BARCODE='" + BARCODE + "' and localId='" + localId + "' ");
            OracleParameter[] parameters = {
					new OracleParameter(":ProDucLotId",OracleDbType.Varchar2)			};
            parameters[0].Value = ProDucLotId;

            SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);
        }

      
        /// <summary>
        /// 获取该库位的是否已经满
        /// </summary>
        /// <param name="locId"></param>
        /// <returns></returns>
        public DataTable getLocIdVolume(string locId)
        {
            string sqlStr = "select HASVOLUME,VOLUME from T_BASE_STORAGE_AREA_LOCATION where LOCATION_ID='" + locId + "' ";
            return SystemDataObject.Instance.GetDataTable(sqlStr.ToString());
        }
        /// <summary>
        /// 向删除批次表（巨化使用）
        /// </summary>
        /// <param name="pickingID">捡货单ID</param>
        public void InsertProDucLotIdGoodsBack(string localId,string out_id)
        {

            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,
                               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
                                from T_CK_PICKINGWAVEGOODS 
                               where LOC_ID='" + localId + "'   )";//and OUT_ID='" + out_id + "'

            SystemDataObject.Instance.ExecuteSql(strsql);

        }
        /// <summary>
        /// 向备份表中添加一个捡货单的捡货商品明细
        /// </summary>
        /// <param name="pickingID">捡货单ID</param>
        public void InsertPickingWaveGoodsBack(string pickingID, string goodsId, string localId)
        {

            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)
                               (
                              select 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
                                from T_CK_PICKINGWAVEGOODS 
                               where PICKINGID = '" + pickingID + "'and GOOD_ID = '" + goodsId + "'    and LOC_ID = '" + localId + "')";


            SystemDataObject.Instance.ExecuteSql(strsql);

        }
    }
}