RuiTengJb/ICSSoft.AppSln/ICSSoft.Frame.Data.DAL/ICSWareHouseLotInfoLogDAL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ICSSoft.Frame.Data.Entity;
using ICSSoft.Base.Config.AppConfig;
using System.Data;
using System.Data.Sql;
using System.Data.Linq;
using ICSSoft.Base.Config.DBHelper;
using System.Data.SqlClient;

namespace ICSSoft.Frame.Data.DAL
{
    public class ICSWareHouseLotInfoLogDAL
    {

        #region 入库

        public static void towh(List<FormICSWareHouseLotInfoUIModel> shiftInfoList, string dsconn)
        {

            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                foreach (FormICSWareHouseLotInfoUIModel shiftInfo in shiftInfoList)
                {

                    bool isNew = false;
                    var line = db.ICSWareHouseLotInfo.SingleOrDefault(a => a.ID == shiftInfo.ID);
                    if (line == null)
                    {
                        isNew = true;
                        line = new ICSWareHouseLotInfo();
                        line.ID = AppConfig.GetGuid();


                    }
                    //var codes = db.ICSWareHouseLotInfo.Where(a => a.SHIFTSEQ == shiftInfo.SHIFTSEQ && a.SHIFTTYPEID == shiftInfo.SHIFTTYPEID && a.ID != line.ID);
                    //if (codes.ToList().Count > 0)
                    //{
                    //    throw new Exception("班次次序在该班制中已存在");
                    //}
                    line.ID = shiftInfo.ID;
                    line.LotNO = shiftInfo.LotNO;
                    line.WHGUID = shiftInfo.WHGUID;
                    line.WHCode = shiftInfo.WHCode;
                    line.BinGUID = shiftInfo.BinGUID;
                    line.BinCode = shiftInfo.BinCode;

                    line.INVGUID = shiftInfo.INVGUID;
                    line.INVCode = shiftInfo.INVCode;
                    line.LotQty = shiftInfo.LotQty;
                    line.ReceiveDate = Convert.ToDateTime(shiftInfo.ReceiveDate);

                    line.MUSER = shiftInfo.MUSER;
                    line.MUSERName = shiftInfo.MUSERName;
                    line.MTIME = Convert.ToDateTime(shiftInfo.MTIME);
                    line.WorkPoint = shiftInfo.WorkPoint;
                    line.EATTRIBUTE1 = shiftInfo.EATTRIBUTE1;

                    if (isNew)
                        db.ICSWareHouseLotInfo.InsertOnSubmit(line);
                    db.SubmitChanges();
                }

                db.SubmitChanges();
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw new Exception(ex.Message);
            }
        }
        #endregion

        #region 库存信息表

        public static void WareHouseInfo(ICSWareHouseInfo ItemLot, string Appconstr)
        {

            FramDataContext db = new FramDataContext(Appconstr);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                bool isNew = false;
                var line = db.ICSWareHouseInfo.SingleOrDefault(a => a.WHGUID == ItemLot.WHGUID && a.BinGUID == ItemLot.BinGUID && a.INVGUID == ItemLot.INVGUID);

                if (line == null)
                {
                    isNew = true;
                    line = new ICSWareHouseInfo();
                    line.WHGUID = ItemLot.WHGUID;
                    line.WHCode = ItemLot.WHCode;
                    line.BinGUID = ItemLot.BinGUID;
                    line.BinCode = ItemLot.BinCode;
                    line.INVGUID = ItemLot.INVGUID;
                    line.INVCode = ItemLot.INVCode;
                    
                }

                
                line.QTY = ItemLot.QTY;
                line.WorkPoint = AppConfig.WorkPointCode; ;
                line.MUSER = ItemLot.MUSER;
                line.MUSERName = ItemLot.MUSERName;
                line.MTIME = ItemLot.MTIME;
                line.EATTRIBUTE1 = ItemLot.EATTRIBUTE1;
                

                if (isNew) db.ICSWareHouseInfo.InsertOnSubmit(line);

                db.SubmitChanges();
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw new Exception(ex.Message);
            }
        }
        #endregion



        #region 物料收发交易记录表

        public static void trans(ICSITEMTrans ItemLot, string Appconstr)
        {

            FramDataContext db = new FramDataContext(Appconstr);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {

                ICSITEMTrans line = new ICSITEMTrans();

                line.ID = ItemLot.ID;
                line.TransNO = ItemLot.TransNO;
                line.TransLine = ItemLot.TransLine;
                line.ITEMCODE = ItemLot.ITEMCODE;
                line.FRMStorageCODE = ItemLot.FRMStorageCODE;
                line.FRMStackCODE = ItemLot.FRMStackCODE;
                line.TOStorageCODE = ItemLot.TOStorageCODE;

                line.TOStackCODE = ItemLot.TOStackCODE;
                line.TransQTY = ItemLot.TransQTY;
                line.Memo = ItemLot.Memo;
                line.TransType = ItemLot.TransType;
                line.BusinessCode = ItemLot.BusinessCode;

                line.WorkPoint = AppConfig.WorkPointCode; ;
                line.MUSER = ItemLot.MUSER;
                line.MUSERName = ItemLot.MUSERName;
                line.MTIME = ItemLot.MTIME;
                line.EATTRIBUTE1 = ItemLot.EATTRIBUTE1;
                db.ICSITEMTrans.InsertOnSubmit(line);


                db.SubmitChanges();
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw new Exception(ex.Message);
            }
        }
        #endregion

        #region 物料收发交易批号记录表

        public static void transLOT(ICSITEMTransLot ItemLot, string Appconstr)
        {

            FramDataContext db = new FramDataContext(Appconstr);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {

                ICSITEMTransLot line = new ICSITEMTransLot();

                line.ID = ItemLot.ID;
                line.ITEMTransID = ItemLot.ITEMTransID;
                line.LotNO = ItemLot.LotNO;
                line.ITEMCODE = ItemLot.ITEMCODE;
                line.TransQTY = ItemLot.TransQTY;
                line.Memo = ItemLot.Memo;

                line.WorkPoint = AppConfig.WorkPointCode; ;
                line.MUSER = ItemLot.MUSER;
                line.MUSERName = ItemLot.MUSERName;
                line.MTIME = ItemLot.MTIME;

                db.ICSITEMTransLot.InsertOnSubmit(line);


                db.SubmitChanges();
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw new Exception(ex.Message);
            }
        }
        #endregion

        #region 物料收发交易详细记录表

        public static void transLOTDetail(ICSITEMTransLotDetail ItemLot, string Appconstr)
        {

            FramDataContext db = new FramDataContext(Appconstr);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {

                ICSITEMTransLotDetail line = new ICSITEMTransLotDetail();

                line.ID = ItemLot.ID;
                line.ITEMTransLotID = ItemLot.ITEMTransLotID;
                line.LotNO = ItemLot.LotNO;
                line.ITEMCODE = ItemLot.ITEMCODE;

                line.SERIALNO = ItemLot.SERIALNO;
                line.WorkPoint = AppConfig.WorkPointCode; ;
                line.MUSER = ItemLot.MUSER;
                line.MUSERName = ItemLot.MUSERName;
                line.MTIME = ItemLot.MTIME;
                line.EATTRIBUTE1 = ItemLot.EATTRIBUTE1;
                db.ICSITEMTransLotDetail.InsertOnSubmit(line);


                db.SubmitChanges();
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw new Exception(ex.Message);
            }
        }
        #endregion

        #region 送检单子表

        public static void iqcdetail(ICSASNIQCDETAIL ItemLot, string Appconstr)
        {

            FramDataContext db = new FramDataContext(Appconstr);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
           
                ICSASNIQCDETAIL line = new ICSASNIQCDETAIL();
                   
                line.STDSTATUS = ItemLot.STDSTATUS;
                
                line.MTIME = AppConfig.GetSeverDateTime("yyyy-MM-dd hh:mm:ss");
                line.WORKPOINT = AppConfig.WorkPointCode;

                db.SubmitChanges();
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw new Exception(ex.Message);
            }
        }
        #endregion

        #region 物料，产品详细信息

        public static void itemlotdetail(ICSITEMLotDetail ItemLot, string Appconstr)
        {

            FramDataContext db = new FramDataContext(Appconstr);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {

                ICSITEMLotDetail line = new ICSITEMLotDetail();

                line.SerialStatus = ItemLot.SerialStatus;

                line.MTIME = AppConfig.GetSeverDateTime("yyyy-MM-dd hh:mm:ss");
                line.WorkPoint = AppConfig.WorkPointCode;

                db.SubmitChanges();
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw new Exception(ex.Message);
            }
        }
        #endregion

        #region 到货单详细表

        public static void receiptdetail(ICSINVReceiptDetail ItemLot, string Appconstr)
        {

            FramDataContext db = new FramDataContext(Appconstr);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                ICSINVReceiptDetail line = new ICSINVReceiptDetail();
                   line.RECSTATUS = ItemLot.RECSTATUS;
                   line.MTIME = AppConfig.GetSeverDateTime("yyyy-MM-dd hh:mm:ss");
                   line.WorkPoint = AppConfig.WorkPointCode;
                   db.SubmitChanges();
                   db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw new Exception(ex.Message);
            }
        }
        #endregion

        #region 送检单主表

        public static void asniqc(ICSASNIQC ItemLot, string Appconstr)
        {

            FramDataContext db = new FramDataContext(Appconstr);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                ICSASNIQC line = new ICSASNIQC();
                line.STATUS = ItemLot.STATUS;
                line.MTIME = AppConfig.GetSeverDateTime("yyyy-MM-dd hh:mm:ss");
                line.WorkPoint = AppConfig.WorkPointCode;
                db.SubmitChanges();
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw new Exception(ex.Message);
            }
        }
        #endregion

        #region 到货单主表

        public static void invreceipt(ICSINVReceipt ItemLot, string Appconstr)
        {

            FramDataContext db = new FramDataContext(Appconstr);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                ICSINVReceipt line = new ICSINVReceipt();
                line.RECSTATUS = ItemLot.RECSTATUS;
                line.MTIME = AppConfig.GetSeverDateTime("yyyy-MM-dd hh:mm:ss");
                line.WorkPoint = AppConfig.WorkPointCode;
                db.SubmitChanges();
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw new Exception(ex.Message);
            }
        }
        #endregion

        #region 查询库位信息
        public static List<FormICSStackUIModel> SearchStackInfoList(string storageid, string dsconn)
        {
            try
            {
                List<FormICSStackUIModel> returnstack = new List<FormICSStackUIModel>();
                string sql = @"select [Serial],[StackCode],[StackName]
                    from [ICSStack]
                    where Storage_Serial='" + storageid + "'";
                sql = string.Format(sql);
                DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
                foreach (DataRow dr in dt.Rows)
                {
                    FormICSStackUIModel itemmodel = new FormICSStackUIModel();
                    itemmodel.Serial = dr["Serial"].ToString();
                    itemmodel.StackCode = dr["StackCode"].ToString();
                    itemmodel.StackName = dr["StackName"].ToString();
                    if (!returnstack.Contains(itemmodel))
                        returnstack.Add(itemmodel);
                }
                return returnstack;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion


        #region 根据入库单号、入库单行号查询物料批号
        public static DataTable SelectLotNO(string no, int line)
        {
            string sql = @"select LotNO
            from dbo.ICSITEMLot
            where TransNO='" + no + "'and TransLine="+line+"   and WorkPoint='" + AppConfig.WorkPointCode + "'";
            sql = string.Format(sql);
            DataTable dt = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0];
            return dt;
        }

        #endregion

        #region 根据批号查找产品序列号
        public static DataTable SelectSeriaNO(string lotNO)
        {
            string sql = @"select SERIALNO
            from dbo.ICSITEMLotDetail
            where LotNO='" + lotNO + "' and WorkPoint='" + AppConfig.WorkPointCode + "'";
            sql = string.Format(sql);
            DataTable dt = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0];
            return dt;
        }

        #endregion

        #region 根据批号查找原始批次数量
        public static DataTable SelectLOTQTY(string lotNO)
        {
            string sql = @"select LOTQTY
            from dbo.ICSITEMLot
            where LotNO='" + lotNO + "' and WorkPoint='" + AppConfig.WorkPointCode + "'";
            sql = string.Format(sql);
            DataTable dt = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0];
            return dt;
        }

        #endregion

        #region 根据库房代码查询库房id
        public static DataTable SelectStorageID(string storagecode)
        {
            string sql = @"select Serial
            from dbo.ICSStorage
            where StorageCode='" + storagecode + "'";
            sql = string.Format(sql);
            DataTable dt = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0];
            return dt;
        }

        #endregion

        #region 根据库位代码查询库位id
        public static DataTable SelectStackID(string stackcode)
        {
            string sql = @"select Serial
            from dbo.ICSStack
            where StackCode='" + stackcode + "'";
            sql = string.Format(sql);
            DataTable dt = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0];
            return dt;
        }

        #endregion

        #region 根据物料代码查询物料id
        public static DataTable SelectItemID(string itemcode)
        {
            string sql = @"select ID
            from dbo.ICSINVENTORY
            where INVCODE='" + itemcode + "'";
            sql = string.Format(sql);
            DataTable dt = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0];
            return dt;
        }

        #endregion

        #region 根据单号、单行号查询物料收发交易记录表id
        public static DataTable SelectTransID(string transno,int transline)
        {
            string sql = @"select ID
            from dbo.ICSITEMTrans
            where TransNO='" + transno + "'and TransLine= '" + transline + "'";
            sql = string.Format(sql);
            DataTable dt = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0];
            return dt;
        }

        #endregion

        #region 根据批号查找交易批号记录表id
        public static DataTable SelectTransLotID(string lotNO)
        {
            string sql = @"select ID
            from dbo.ICSITEMTransLot
            where LotNO='" + lotNO + "'";
            sql = string.Format(sql);
            DataTable dt = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0];
            return dt;
        }

        #endregion

        #region 根据库房库位id、库位id、物料id查询库存信息表中的库存数量
        public static DataTable SelectQTY(string storageid,string stackid,string itemid)
        {

            string sql = @"select QTY
            from dbo.ICSWareHouseInfo
            where WHGUID='" + storageid + "' and BinGUID='" + stackid + "' and INVGUID='" + itemid + "'";
            sql = string.Format(sql);
            DataTable dt = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0];
            return dt;
        }

        #endregion



        #region 更新送检单详细表的行状态
        public static void updateSTDSTATUS(string stno, int stline)
        {
            string sql = @"update dbo.ICSASNIQCDETAIL
            set STDSTATUS='已检已入'
            where STNO='" + stno + "' and STLINE=" + stline + "";
            sql = string.Format(sql);
            DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql);
        }
        #endregion

        #region 更新物料，产品详细信息产品状态
        public static void updateSerialStatus(string serialno, string itemcode)
        {
            string sql = @"update dbo.ICSITEMLotDetail
            set SerialStatus='在库'
            where SERIALNO='" + serialno + "' and ItemCode=" + itemcode + "";
            sql = string.Format(sql);
            DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql);
        }
        #endregion


        #region 更新到货单详细表的到货单状态
        public static void updateRECSTATUS(string stno, int stline)
        {
            string sql = @"update dbo.ICSINVReceiptDetail
            set RECSTATUS='已经完成'
            where ReceiptNO='" + stno + "' and ReceiptLine=" + stline + "";
            sql = string.Format(sql);
            DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql);
        }
        #endregion


        #region 更新送检单主表的单据状态
        public static void updateSTATUS(string stno,int stline)
        {
            string sql = @"update dbo.ICSASNIQC
            set STATUS='已检已入'
            where STNO='" + stno + "'and STNO_SEQ=" + stline + "";
            sql = string.Format(sql);
            DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql);
        }
        #endregion

        #region 更新到货单主表的单据状态
        public static void updatereceiptRECSTATUS(string stno)
        {
            string sql = @"update dbo.ICSINVReceipt
            set RECSTATUS='已经完成'
            where ReceiptNO='" + stno + "'";
            sql = string.Format(sql);
            DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql);
        }
        #endregion

        #region 根据单号、单行号查询送检单子表信息
        public static ICSASNIQCDETAIL selectIQCDetail(String stno, int stline, String Appconstr)
        {
            FramDataContext db = new FramDataContext(Appconstr);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            ICSASNIQCDETAIL entity = new ICSASNIQCDETAIL();
            try
            {
                var line = db.ICSASNIQCDETAIL.SingleOrDefault(a => a.STNO == stno && a.STLINE == stline);
                return (ICSASNIQCDETAIL)line;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        #endregion

        #region 根据产品序列号、存货编码查询物料产品详细表
        public static ICSITEMLotDetail selectitemlotdetail(String serailno, string itemcode, String Appconstr)
        {
            FramDataContext db = new FramDataContext(Appconstr);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            ICSITEMLotDetail entity = new ICSITEMLotDetail();
            try
            {
                var line = db.ICSITEMLotDetail.SingleOrDefault(a => a.SERIALNO == serailno && a.ItemCode == itemcode);
                return (ICSITEMLotDetail)line;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        #endregion

        #region 根据单号、行号查询送检单信息
        public static ICSASNIQC selectASNIQC(String stno,int stline, String Appconstr)
        {
            FramDataContext db = new FramDataContext(Appconstr);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            ICSASNIQC entity = new ICSASNIQC();
            try
            {
                var line = db.ICSASNIQC.SingleOrDefault(a => a.STNO == stno && a.STNO_SEQ == stline);
                return (ICSASNIQC)line;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        #endregion

        #region 根据单号查询到货单主表信息
        public static ICSINVReceipt selectreceipt(String stno, String Appconstr)
        {
            FramDataContext db = new FramDataContext(Appconstr);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            ICSINVReceipt entity = new ICSINVReceipt();
            try
            {
                var line = db.ICSINVReceipt.SingleOrDefault(a => a.ReceiptNO == stno);
                return (ICSINVReceipt)line;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        #endregion


        #region 根据单号、单行号查询到货单详细信息
        public static ICSINVReceiptDetail selectReceiptDetail(String stno, int stline, String Appconstr)
        {
            FramDataContext db = new FramDataContext(Appconstr);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            ICSINVReceiptDetail entity = new ICSINVReceiptDetail();
            try
            {
                var line = db.ICSINVReceiptDetail.SingleOrDefault(a => a.ReceiptNO == stno && a.ReceiptLine == stline);
                return (ICSINVReceiptDetail)line;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        #endregion

        #region 根据单号、单行号查询生成的批次信息
        public static ICSITEMLot selectICSITEMLot(String stno, string stline, String Appconstr)
        {
            FramDataContext db = new FramDataContext(Appconstr);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            ICSITEMLot entity = new ICSITEMLot();

            try
            {
                var line = db.ICSITEMLot.SingleOrDefault(a => a.TransNO == stno && a.TransLine == stline);
                return (ICSITEMLot)line;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        #endregion

        #region 判断某个送检单所有子表的行状态是否关闭
        public static bool IsSTDSTATUSclose(string stno, string dsconn)
        {

            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {

              
                var line = db.ICSASNIQCDETAIL.SingleOrDefault(a => a.STNO == stno && a.STDSTATUS == "已检已入");
                if (line == null)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw ex;
            }

        }
        #endregion


        #region 判断某个到货单所有子表的到货单状态是否关闭
        public static bool IsRECSTATUSclose(string stno, string dsconn)
        {

            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                var line = db.ICSINVReceiptDetail.SingleOrDefault(a => a.ReceiptNO == stno && a.RECSTATUS == "已经完成");
                if (line == null)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw ex;
            }

        }
        #endregion

        #region 状态更新
        public static void updatestatus(string receiptno, int receiptline)
        {
            FramDataContext db = new FramDataContext(AppConfig.AppConnectString);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                //var line = db.ICSASNIQC.SingleOrDefault(a => a.STNO == receiptno && a.STNO_SEQ == receiptline && a.WorkPoint == AppConfig.WorkPointCode);
                //var lines = db.ICSASNIQCDETAIL.SingleOrDefault(a => a.STNO == receiptno && a.STLINE == receiptline && a.WORKPOINT == AppConfig.WorkPointCode);
                //line.STATUS = "已检已入";
                //lines.STDSTATUS = "已检已入";

                var itemTransQty = db.ICSITEMTrans.Where(a => a.TransNO == receiptno && a.TransLine == receiptline && a.TransType=="收" && a.WorkPoint == AppConfig.WorkPointCode).Sum(a=>a.TransQTY);
                     
                var INVReceiptDetail = db.ICSINVReceiptDetail.SingleOrDefault(a => a.ReceiptNO == receiptno &&  a.ReceiptLine == receiptline && a.WorkPoint == AppConfig.WorkPointCode);
                if (itemTransQty != null && INVReceiptDetail != null && itemTransQty == INVReceiptDetail.PLANQTY)
                {
                    INVReceiptDetail.RECSTATUS = "已经完成";
                    db.SubmitChanges();
                }
                var ptDetail = db.ICSINVReceiptDetail.Where(a => a.ReceiptNO == receiptno && a.WorkPoint == AppConfig.WorkPointCode && a.RECSTATUS != "已经完成");
                if (ptDetail.Count() == 0 || ptDetail == null)
                {
                    var ICSINVReceipt = db.ICSINVReceipt.SingleOrDefault(a => a.ReceiptNO == receiptno && a.WorkPoint == AppConfig.WorkPointCode);
                    ICSINVReceipt.RECSTATUS = "已经完成";
                }
                db.SubmitChanges();
                db.Transaction.Commit();
            }
                

            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw ex;
            }
        }
        #endregion


        #region 期初导入
        public static void Insert(List<ICSITEMLot> tbinfoList, List<FormICSWareHouseLotInfoUIModel> shiftInfoList, List<ICSWareHouseInfo> WareHouseInfoList, List<ICSWareHouseLotInfoLog> ICSWareHouseLogList, string dsconn)
        {
            #region 写入WMS
            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                #region ICSITEMLot
                foreach (ICSITEMLot tbinfo in tbinfoList)
                {
                    bool isNew = false;
                    var line = db.ICSITEMLot.SingleOrDefault(a => a.ID == tbinfo.ID);
                    if (line == null)
                    {
                        isNew = true;
                        line = new ICSITEMLot();
                        line.ID = AppConfig.GetGuid();
                    }
                    line.LotNO = tbinfo.LotNO;
                    line.ItemCode = tbinfo.ItemCode;
                    line.TransNO = tbinfo.TransNO;
                    line.TransLine = tbinfo.TransLine;
                    line.VENDORITEMCODE = tbinfo.VENDORITEMCODE;
                    line.VENDORCODE = tbinfo.VENDORCODE;
                    line.VenderLotNO = tbinfo.VenderLotNO;
                    line.PRODUCTDATE = tbinfo.PRODUCTDATE;
                    line.LOTQTY = tbinfo.LOTQTY;
                    line.ACTIVE = tbinfo.ACTIVE;
                    line.Exdate = tbinfo.Exdate;
                    line.TYPE = tbinfo.TYPE;
                    line.VoucherNO = tbinfo.VoucherNO;//零件号
                    line.VoucherRow = tbinfo.VoucherRow;//工艺要求
                    line.TwoMUSER = tbinfo.TwoMUSER;//工程号
                    line.WorkPoint = tbinfo.WorkPoint;
                    line.MUSER = tbinfo.MUSER;
                    line.MUSERName = tbinfo.MUSERName;
                    line.MTIME = tbinfo.MTIME;
                    line.EATTRIBUTE1 = tbinfo.EATTRIBUTE1;
                    line.EATTRIBUTE3 = tbinfo.EATTRIBUTE3;
                    if (isNew)
                        db.ICSITEMLot.InsertOnSubmit(line);
                    db.SubmitChanges();
                }
                #endregion
                #region FormICSWareHouseLotInfoUIModel
                foreach (FormICSWareHouseLotInfoUIModel shiftInfo in shiftInfoList)
                {

                    bool isNew = false;
                    var line = db.ICSWareHouseLotInfo.SingleOrDefault(a => a.ID == shiftInfo.ID);
                    if (line == null)
                    {
                        isNew = true;
                        line = new ICSWareHouseLotInfo();
                        line.ID = AppConfig.GetGuid();


                    }
                    //var codes = db.ICSWareHouseLotInfo.Where(a => a.SHIFTSEQ == shiftInfo.SHIFTSEQ && a.SHIFTTYPEID == shiftInfo.SHIFTTYPEID && a.ID != line.ID);
                    //if (codes.ToList().Count > 0)
                    //{
                    //    throw new Exception("班次次序在该班制中已存在");
                    //}
                    line.ID = shiftInfo.ID;
                    line.LotNO = shiftInfo.LotNO;
                    line.WHGUID = shiftInfo.WHGUID;
                    line.WHCode = shiftInfo.WHCode;
                    line.BinGUID = shiftInfo.BinGUID;
                    line.BinCode = shiftInfo.BinCode;

                    line.INVGUID = shiftInfo.INVGUID;
                    line.INVCode = shiftInfo.INVCode;
                    line.LotQty = shiftInfo.LotQty;
                    line.ReceiveDate = Convert.ToDateTime(shiftInfo.ReceiveDate);

                    line.MUSER = shiftInfo.MUSER;
                    line.MUSERName = shiftInfo.MUSERName;
                    line.MTIME = Convert.ToDateTime(shiftInfo.MTIME);
                    line.WorkPoint = shiftInfo.WorkPoint;
                    line.EATTRIBUTE1 = shiftInfo.EATTRIBUTE1;

                    if (isNew)
                        db.ICSWareHouseLotInfo.InsertOnSubmit(line);
                    db.SubmitChanges();
                }
                #endregion
                #region ICSWareHouseInfo
                foreach (ICSWareHouseInfo ItemLot in WareHouseInfoList)
                {
                    bool isNew = false;
                    var line = db.ICSWareHouseInfo.SingleOrDefault(a => a.WHGUID == ItemLot.WHGUID && a.BinGUID == ItemLot.BinGUID && a.INVGUID == ItemLot.INVGUID);

                    if (line == null)
                    {
                        isNew = true;
                        line = new ICSWareHouseInfo();
                        line.WHGUID = ItemLot.WHGUID;
                        line.WHCode = ItemLot.WHCode;
                        line.BinGUID = ItemLot.BinGUID;
                        line.BinCode = ItemLot.BinCode;
                        line.INVGUID = ItemLot.INVGUID;
                        line.INVCode = ItemLot.INVCode;
                        //line.QTY = ItemLot.QTY;
                    }

                    line.QTY += ItemLot.QTY;
                    line.WorkPoint = AppConfig.WorkPointCode; ;
                    line.MUSER = ItemLot.MUSER;
                    line.MUSERName = ItemLot.MUSERName;
                    line.MTIME = ItemLot.MTIME;
                    line.EATTRIBUTE1 = ItemLot.EATTRIBUTE1;

                    if (isNew) db.ICSWareHouseInfo.InsertOnSubmit(line);

                    db.SubmitChanges();
                }
                #endregion
                #region ICSITEMTrans
                //foreach (ICSITEMTrans ItemLot in TransList)
                //{
                //    ICSITEMTrans line = new ICSITEMTrans();

                //    line.ID = ItemLot.ID;
                //    line.TransNO = ItemLot.TransNO;
                //    line.TransLine = ItemLot.TransLine;
                //    line.ITEMCODE = ItemLot.ITEMCODE;
                //    line.FRMStorageCODE = ItemLot.FRMStorageCODE;
                //    line.FRMStackCODE = ItemLot.FRMStackCODE;
                //    line.TOStorageCODE = ItemLot.TOStorageCODE;

                //    line.TOStackCODE = ItemLot.TOStackCODE;
                //    line.TransQTY = ItemLot.TransQTY;
                //    line.Memo = ItemLot.Memo;
                //    line.TransType = ItemLot.TransType;
                //    line.BusinessCode = ItemLot.BusinessCode;

                //    line.WorkPoint = AppConfig.WorkPointCode; ;
                //    line.MUSER = ItemLot.MUSER;
                //    line.MUSERName = ItemLot.MUSERName;
                //    line.MTIME = ItemLot.MTIME;
                //    line.EATTRIBUTE1 = ItemLot.EATTRIBUTE1;
                //    db.ICSITEMTrans.InsertOnSubmit(line);

                //    db.SubmitChanges();
                //}
                #endregion
                #region ICSITEMTransLot
                //foreach (ICSITEMTransLot ItemLot in ItemLotList)
                //{

                //    ICSITEMTransLot line = new ICSITEMTransLot();

                //    line.ID = ItemLot.ID;
                //    line.ITEMTransID = ItemLot.ITEMTransID;
                //    line.LotNO = ItemLot.LotNO;
                //    line.ITEMCODE = ItemLot.ITEMCODE;
                //    line.TransQTY = ItemLot.TransQTY;
                //    line.Memo = ItemLot.Memo;

                //    line.WorkPoint = AppConfig.WorkPointCode; ;
                //    line.MUSER = ItemLot.MUSER;
                //    line.MUSERName = ItemLot.MUSERName;
                //    line.MTIME = ItemLot.MTIME;

                //    db.ICSITEMTransLot.InsertOnSubmit(line);


                //    db.SubmitChanges();
                //}
                #endregion
                #region ICSWareHouseLotInfoLog
                foreach (ICSWareHouseLotInfoLog LogList in ICSWareHouseLogList)
                {
                    ICSWareHouseLotInfoLog line = new ICSWareHouseLotInfoLog();

                    line.ID = LogList.ID;
                    line.TransNO = LogList.TransNO;
                    line.TransLine = LogList.TransLine;
                    line.ITEMCODE = LogList.ITEMCODE;
                    line.LotNO = LogList.LotNO;
                    line.FRMStorageCODE = LogList.FRMStorageCODE;
                    line.FRMStackCODE = LogList.FRMStackCODE;
                    line.TOStorageCODE = LogList.TOStorageCODE;

                    line.TOStackCODE = LogList.TOStackCODE;
                    line.TransQTY = LogList.TransQTY;
                    line.Memo = LogList.Memo;
                    line.TransType = LogList.TransType;
                    line.BusinessCode = LogList.BusinessCode;

                    line.WorkPoint = AppConfig.WorkPointCode; ;
                    line.MUSER = LogList.MUSER;
                    line.MUSERName = LogList.MUSERName;
                    line.MTIME = LogList.MTIME;
                    line.EATTRIBUTE1 = LogList.EATTRIBUTE1;
                    db.ICSWareHouseLotInfoLog.InsertOnSubmit(line);

                    db.SubmitChanges();
                }
                #endregion

                
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw new Exception(ex.Message);
            }
            #endregion
            
        }
        #endregion

        #region 审核ERP入库单
        public static string updateRd10(List<Rdrecord10> dataContext, List<FormICSWareHouseLotInfoUIModel> shiftInfoList, List<ICSWareHouseInfo> WareHouseInfoList, List<ICSITEMTrans> TransList, List<ICSITEMTransLot> ItemLotList, string dsconn)
        {
            string conStr = "";
            if (dataContext.Count == 0)
            {
                return "";
            }
            else
            {
                conStr = dataContext[0].ERPConStr; 
            }
            SqlConnection conn = new System.Data.SqlClient.SqlConnection(conStr);
            conn.Open();
            SqlTransaction sqlTran = conn.BeginTransaction();
            SqlCommand cmd = new SqlCommand();
            cmd.Transaction = sqlTran;
            cmd.Connection = conn;
            try
            {
                #region 更新ERP
                foreach (Rdrecord10 Context in dataContext)
                {
                    string sql = @"
                                IF NOT EXISTS(SELECT a.ID FROM rdrecord10 a 
                                INNER JOIN rdrecords10 b ON a.ID = b.ID
                                WHERE b.AutoID = '{0}' AND a.cWhCode = '{3}')
                                BEGIN
                                    RAISERROR('入库单仓库与扫描仓库不符!!',16,0);
                                END

                                IF EXISTS(SELECT a.ID FROM rdrecord10 a 
                                INNER JOIN rdrecords10 b ON a.ID = b.ID
                                WHERE b.AutoID = '{0}' AND b.iQuantity > ISNULL(iFQuantity,0))
                                BEGIN
	                                UPDATE rdrecords10 SET iFQuantity = ISNULL(iFQuantity,0) + '{1}' 
	                                WHERE AutoID = '{0}'
	                                UPDATE b SET b.cHandler = '{2}',b.dnverifytime = GETDATE(),
	                                b.dVeriDate  = CONVERT(NVARCHAR(20),GETDATE(),23)
	                                FROM rdrecords10 a INNER JOIN rdrecord10 b ON a.ID = b.ID
	                                WHERE a.AutoID = '{0}' 

	                                IF EXISTS (SELECT AutoID FROM  dbo.CurrentStock 
		                                WHERE cWhCode = '{3}'  AND cInvCode = '{4}')
	                                BEGIN 
		                                UPDATE dbo.CurrentStock SET iQuantity  = iQuantity  + '{1}'
		                                WHERE cWhCode = '{3}' AND cInvCode = '{4}'
	                                END 
	                                ELSE
	                                BEGIN
                                        INSERT INTO dbo.CurrentStock 
		                                (cWhCode,cInvCode,ItemId,cBatch,iSoType,iSodid,iQuantity,
		                                iNum,cFree1,fOutQuantity,fOutNum,fInQuantity,fInNum,cFree2,
		                                cFree3,bStopFlag,fTransInQuantity,fTransInNum,
		                                fTransOutQuantity,fTransOutNum,fPlanQuantity,fPlanNum,fDisableQuantity,
		                                fDisableNum,fAvaQuantity,fAvaNum,BGSPSTOP,fStopQuantity,
		                                fStopNum,ipeqty,ipenum)
		                                SELECT '{3}','{4}',(SELECT Id FROM dbo.SCM_Item WHERE cInvCode = '{4}'),'','0','','{1}',
		                                '0','','0','0','0','0','',
		                                '','0','0','0','0','0','0','0','0',
		                                '0','0','0','0','0','0','0','0'
		                            END
                                                            
                                END
                                ELSE
                                BEGIN
                                    RAISERROR('ERP入库单已经入库完成!!',16,0);
                                END";
                    sql = string.Format(sql, Context.rdsID, Context.Quantity, Context.UserName, Context.whCode, Context.cInvCode);
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();
                }

                #endregion

            #region 写入WMS
                FramDataContext db = new FramDataContext(dsconn);
                db.Connection.Open();
                db.Transaction = db.Connection.BeginTransaction();
                try
                {
                    #region FormICSWareHouseLotInfoUIModel
                    foreach (FormICSWareHouseLotInfoUIModel shiftInfo in shiftInfoList)
                    {

                        bool isNew = false;
                        var line = db.ICSWareHouseLotInfo.SingleOrDefault(a => a.ID == shiftInfo.ID);
                        if (line == null)
                        {
                            isNew = true;
                            line = new ICSWareHouseLotInfo();
                            line.ID = AppConfig.GetGuid();


                        }
                        //var codes = db.ICSWareHouseLotInfo.Where(a => a.SHIFTSEQ == shiftInfo.SHIFTSEQ && a.SHIFTTYPEID == shiftInfo.SHIFTTYPEID && a.ID != line.ID);
                        //if (codes.ToList().Count > 0)
                        //{
                        //    throw new Exception("班次次序在该班制中已存在");
                        //}
                        line.ID = shiftInfo.ID;
                        line.LotNO = shiftInfo.LotNO;
                        line.WHGUID = shiftInfo.WHGUID;
                        line.WHCode = shiftInfo.WHCode;
                        line.BinGUID = shiftInfo.BinGUID;
                        line.BinCode = shiftInfo.BinCode;

                        line.INVGUID = shiftInfo.INVGUID;
                        line.INVCode = shiftInfo.INVCode;
                        line.LotQty = shiftInfo.LotQty;
                        line.ReceiveDate = Convert.ToDateTime(shiftInfo.ReceiveDate);

                        line.MUSER = shiftInfo.MUSER;
                        line.MUSERName = shiftInfo.MUSERName;
                        line.MTIME = Convert.ToDateTime(shiftInfo.MTIME);
                        line.WorkPoint = shiftInfo.WorkPoint;
                        line.EATTRIBUTE1 = shiftInfo.EATTRIBUTE1;

                        if (isNew)
                            db.ICSWareHouseLotInfo.InsertOnSubmit(line);
                        db.SubmitChanges();
                    }
	            #endregion
                    #region ICSWareHouseInfo
                    foreach (ICSWareHouseInfo ItemLot in WareHouseInfoList)
                    {
                        bool isNew = false;
                        var line = db.ICSWareHouseInfo.SingleOrDefault(a => a.WHGUID == ItemLot.WHGUID && a.BinGUID == ItemLot.BinGUID && a.INVGUID == ItemLot.INVGUID);

                        if (line == null)
                        {
                            isNew = true;
                            line = new ICSWareHouseInfo();
                            line.WHGUID = ItemLot.WHGUID;
                            line.WHCode = ItemLot.WHCode;
                            line.BinGUID = ItemLot.BinGUID;
                            line.BinCode = ItemLot.BinCode;
                            line.INVGUID = ItemLot.INVGUID;
                            line.INVCode = ItemLot.INVCode;
                            //line.QTY = ItemLot.QTY;
                        }

                        line.QTY += ItemLot.QTY;
                        line.WorkPoint = AppConfig.WorkPointCode; ;
                        line.MUSER = ItemLot.MUSER;
                        line.MUSERName = ItemLot.MUSERName;
                        line.MTIME = ItemLot.MTIME;
                        line.EATTRIBUTE1 = ItemLot.EATTRIBUTE1;
                        
                        if (isNew) db.ICSWareHouseInfo.InsertOnSubmit(line);

                        db.SubmitChanges();
                    }
	            #endregion
                    #region ICSITEMTrans
                    foreach (ICSITEMTrans ItemLot in TransList)
                    {
                        ICSITEMTrans line = new ICSITEMTrans();

                        line.ID = ItemLot.ID;
                        line.TransNO = ItemLot.TransNO;
                        line.TransLine = ItemLot.TransLine;
                        line.ITEMCODE = ItemLot.ITEMCODE;
                        line.FRMStorageCODE = ItemLot.FRMStorageCODE;
                        line.FRMStackCODE = ItemLot.FRMStackCODE;
                        line.TOStorageCODE = ItemLot.TOStorageCODE;

                        line.TOStackCODE = ItemLot.TOStackCODE;
                        line.TransQTY = ItemLot.TransQTY;
                        line.Memo = ItemLot.Memo;
                        line.TransType = ItemLot.TransType;
                        line.BusinessCode = ItemLot.BusinessCode;

                        line.WorkPoint = AppConfig.WorkPointCode; ;
                        line.MUSER = ItemLot.MUSER;
                        line.MUSERName = ItemLot.MUSERName;
                        line.MTIME = ItemLot.MTIME;
                        line.EATTRIBUTE1 = ItemLot.EATTRIBUTE1;
                        db.ICSITEMTrans.InsertOnSubmit(line);

                        db.SubmitChanges();
                    }
	            #endregion
                    #region ICSITEMTransLot
                    foreach (ICSITEMTransLot ItemLot in ItemLotList)
                    {
                        
                        ICSITEMTransLot line = new ICSITEMTransLot();

                        line.ID = ItemLot.ID;
                        line.ITEMTransID = ItemLot.ITEMTransID;
                        line.LotNO = ItemLot.LotNO;
                        line.ITEMCODE = ItemLot.ITEMCODE;
                        line.TransQTY = ItemLot.TransQTY;
                        line.Memo = ItemLot.Memo;

                        line.WorkPoint = AppConfig.WorkPointCode; ;
                        line.MUSER = ItemLot.MUSER;
                        line.MUSERName = ItemLot.MUSERName;
                        line.MTIME = ItemLot.MTIME;

                        db.ICSITEMTransLot.InsertOnSubmit(line);


                        db.SubmitChanges();
                    }
	            #endregion
                    db.Transaction.Commit();
                }
                catch (Exception ex)
                {
                    db.Transaction.Rollback();
                    throw new Exception(ex.Message);
                }
            #endregion

                cmd.Transaction.Commit();
                return "OK";
            }
            catch (Exception ex)
            {
                cmd.Transaction.Rollback();
                throw ex;
            }


        }
        #endregion

        #region 根据库位代码查询库位id,仓库
        public static DataTable SelectStack(string stackcode)
        {
            string sql = @"SELECT
	                a.Serial,b.StorageCode,b.Serial AS StorageID
                FROM
	                dbo.ICSStack a
                INNER JOIN ICSStorage b ON a.Storage_Serial=b.Serial AND a.WorkPoint=b.WorkPoint
                WHERE a.StackCode='{0}' AND a.WorkPoint='{1}'";
            sql = string.Format(sql,stackcode,AppConfig.WorkPointCode);
            DataTable dt = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0];
            return dt;
        }

        #endregion

        #region 根据存货编码判断批号是否批次管理
        public static DataTable SelectLotNo(string invcode)
        {
            string sql = @"SELECT INVCODE,INVCONTROLTYPE
                    FROM ICSINVENTORY
                    WHERE INVCODE='{0}'";
            sql = string.Format(sql, invcode, AppConfig.WorkPointCode);
            DataTable dt = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0];
            return dt;
        }

        #endregion

        #region 根据物料代码查询物料
        public static DataTable SelectItem(string itemcode)
        {
            string sql = @"select ID,INVCLASS,CASE SUBSTRING(INVCLASS, 1, 1) WHEN '2' THEN '半成品' WHEN '3' THEN '成品' ELSE '原材料' END AS Type
            from dbo.ICSINVENTORY
            where INVCODE='" + itemcode + "'";
            sql = string.Format(sql);
            DataTable dt = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0];
            return dt;
        }
        #endregion

    }
}