RuiTengJb/ICSSoft.AppSln/ICSSoft.Frame.Data.DAL/ICSStorageInfoDAL.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 ICSSoft.Base.Config.DBHelper;
using System.Data.SqlClient;

namespace ICSSoft.Frame.Data.DAL
{
    public static class ICSStorageInfoDAL
    {
        public static void import(List<string> barList)
        {
            SqlConnection conn = new SqlConnection(AppConfig.AppConnectString);
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.Text;
            SqlTransaction trans = conn.BeginTransaction();
            cmd.Transaction = trans;
            cmd.Connection = conn;

            try
            {
                string barCodeStr = "";
                string maxNo = @"select max(ToCheckNO ) as ToCheckNO  from dbo.ICSToCheck";
                DataTable dtNo = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, maxNo).Tables[0];

                for (int i = 0; i < barList.Count;i++ )
                {
                    if (barCodeStr == "")
                    {
                        barCodeStr = "'" + barList[i] + "'";
                    }
                    else
                    {
                        barCodeStr += ",'" + barList[i] + "'";
                    }
                    if (i % 200 == 0)
                    {
                        string lineSql = @"INSERT INTO [dbo].[ICSToCheckLog]
                                                   ([Serial]
                                                   ,[BarCode]
                                                   ,[Mtime])
                    SELECT NEWID(),BarCode,GETDATE() FROM dbo.ICSStockBarCode WHERE BarCode IN ("+barCodeStr+@")
                    UNION ALL
                    SELECT NEWID(),BarCode,GETDATE() FROM dbo.ICSMOBarCode WHERE BarCode IN (" + barCodeStr + ")";
                        cmd.CommandText = lineSql;
                        cmd.ExecuteNonQuery();

                        lineSql = @"insert into dbo.ICSToChecks
        select distinct NEWID(),'{0}',CASE WHEN b.ItemCode IS NULL THEN c.ItemCode ELSE b.ItemCode END AS ItemCode,
        CASE WHEN b.BatchCode IS NULL THEN c.BatchCode ELSE b.ItemCode END AS BatchCode,
        e.Free1,e.Free2,e.Free3,e.Free4,a.BarCode,
        CASE WHEN b.Quantity IS NULL THEN c.Quantity ELSE b.Quantity END AS Quantity,
        f.StorageCode,'{1}',GETDATE()
            from dbo.ICSStorageInfo a 
        left join dbo.ICSStockBarCode b on a.BarCode = b.BarCode
        left join dbo.ICSMOBarCode c on a.BarCode = c.BarCode
        left join dbo.ICSPurchasingStorage d on b.cFree1 = d.Serial
        left join dbo.ICSMO e on c.cFree1 = e.Serial
        left join dbo.ICSStorage f on a.Storage_Serial = f.Serial
        where a.barcode in (" + barCodeStr + @")";
                        lineSql = string.Format(lineSql, dtNo.Rows[0]["ToCheckNO"].ToString(), AppConfig.UserName);
                        cmd.CommandText = lineSql;
                        cmd.ExecuteNonQuery();
                        barCodeStr = "";
                    }

                }
                string Sql = @"insert into dbo.ICSToChecks
        select distinct NEWID(),'{0}',CASE WHEN b.ItemCode IS NULL THEN c.ItemCode ELSE b.ItemCode END AS ItemCode,
        CASE WHEN b.BatchCode IS NULL THEN c.BatchCode ELSE b.ItemCode END AS BatchCode,
        e.Free1,e.Free2,e.Free3,e.Free4,a.BarCode,
        CASE WHEN b.Quantity IS NULL THEN c.Quantity ELSE b.Quantity END AS Quantity,
        f.StorageCode,'{1}',GETDATE()
        from dbo.ICSStorageInfo a 
        left join dbo.ICSStockBarCode b on a.BarCode = b.BarCode
        left join dbo.ICSMOBarCode c on a.BarCode = c.BarCode
        left join dbo.ICSPurchasingStorage d on b.cFree1 = d.Serial
        left join dbo.ICSMO e on c.cFree1 = e.Serial
        left join dbo.ICSStorage f on a.Storage_Serial = f.Serial
        where a.barcode in (" + barCodeStr + @")";
                Sql = string.Format(Sql, dtNo.Rows[0]["ToCheckNO"].ToString(), AppConfig.UserName);
                cmd.CommandText = Sql;
                cmd.ExecuteNonQuery();

                Sql = @"INSERT INTO [dbo].[ICSToCheckLog]
                                                   ([Serial]
                                                   ,[BarCode]
                                                   ,[Mtime])
                    SELECT NEWID(),BarCode,GETDATE() FROM dbo.ICSStockBarCode WHERE BarCode IN (" + barCodeStr + @")
                    UNION ALL
                    SELECT NEWID(),BarCode,GETDATE() FROM dbo.ICSMOBarCode WHERE BarCode IN (" + barCodeStr + ")";
                cmd.CommandText = Sql;
                cmd.ExecuteNonQuery();
                barCodeStr = "";

                Sql = @"UPDATE dbo.ICSToCheck SET ActualQty = ISNULL(ActualQty,0)+ISNULL(b.qty,0)
 FROM dbo.ICSToCheck a 
INNER JOIN (SELECT distinct CASE WHEN d.[ItemCode] IS NULL THEN e.ItemCode ELSE d.[ItemCode] END AS ItemCode,
      isnull(e.Free1,'') as Free1,isnull(e.Free2,'') as Free2,
      isnull(e.Free3,'') as Free3,isnull(e.Free4,'') as Free4,
      sum(a.BarCodeQty) AS qty,a.StorageCode,info.ItemType
        FROM [ICSToChecks] a 
        left join dbo.ICSStorageInfo info on a.BarCode = info.BarCode
        LEFT JOIN ICSMOBarCode b 
        ON a.BarCode=b.BarCode
        LEFT JOIN dbo.ICSStockBarCode c 
        ON a.BarCode=c.BarCode
        LEFT JOIN dbo.ICSPurchasingStorage d
        ON c.cFree1 = d.Serial
        LEFT JOIN dbo.ICSMO e ON b.cFree1 = e.Serial
        GROUP BY d.[ItemCode],e.ItemCode,e.Free1,e.Free2,e.Free3,
        e.Free4,a.StorageCode,info.ItemType) b 
        ON a.ItemCode = b.ItemCode AND a.Free1 = b.Free1 AND a.Free2 = b.Free2
        AND a.Free3 = b.Free3 AND a.Free4 = b.Free4 AND a.StorageCode = b.StorageCode
        AND a.itemtype = b.ItemType";
                cmd.CommandText = Sql;
                cmd.ExecuteNonQuery();

                trans.Commit();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                throw ex;
            }
        }

        public static void StorageIn(string barCodeStr, string storage, string stack)
        {
          SqlConnection conn = new SqlConnection(AppConfig.AppConnectString);
          conn.Open();
          SqlCommand cmd = new SqlCommand();
          SqlTransaction trans = conn.BeginTransaction();
          cmd.Transaction = trans;
          cmd.Connection = conn;
          try
          {

              string strSql = @"INSERT INTO [dbo].[ICSStorageInfo]
                               ([Serial]
                               ,[BarCode]
                               ,[Storage_Serial]
                               ,[Stack_Serial]
                               ,[Mtime]
                               ,[Muser]
                               ,[WorkPoint],ItemType)
                    SELECT NEWID(),BarCode,'{0}','{1}',GETDATE(),'{2}','{3}','原料'
                     FROM  dbo.ICSStockBarCode WHERE BarCode IN ({4})";
              strSql = string.Format(strSql, storage, stack, AppConfig.UserName, AppConfig.WorkPointCode, barCodeStr);
              cmd.CommandType = CommandType.Text;
              cmd.CommandText = strSql;
              cmd.ExecuteNonQuery();

              strSql = @" UPDATE dbo.ICSStockBarCode SET Status = '入库'
                 WHERE BarCode IN ({0})";
              strSql = string.Format(strSql, barCodeStr);
              cmd.CommandType = CommandType.Text;
              cmd.CommandText = strSql;
              cmd.ExecuteNonQuery();

              trans.Commit();
          }
          catch(Exception ex)
          {
              trans.Rollback();
              throw ex;
          }
      }

        public static void Delete(string delStr)
        {
            SqlConnection conn = new SqlConnection(AppConfig.AppConnectString);
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            SqlTransaction trans = conn.BeginTransaction();
            cmd.Transaction = trans;
            cmd.Connection = conn;
            try
            {
                string strSql = @"DELETE dbo.ICSStorageInfo WHERE barCode in ({0})";
                strSql = string.Format(strSql, delStr);
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = strSql;
                cmd.ExecuteNonQuery();

                strSql = @"DELETE  dbo.ICSMOStorageINERP WHERE BarCode IN ({0})";
                strSql = string.Format(strSql, delStr);
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = strSql;
                cmd.ExecuteNonQuery();

                strSql = @" UPDATE dbo.ICSStockBarCode SET Status = '新增'
                 WHERE BarCode IN ({0})";
                strSql = string.Format(strSql, delStr);
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = strSql;
                cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                throw ex;
            }
        }

        public static void StorageInERP(string barCodeStr, storageInfoContext Context)
        {
            string sql = "";
            string ConnectString = AppConfig.AppConnectString;

            #region 默认代码
            Context.ERPCardNumber = "24";
            #endregion

            #region  取得out库单的默认显示模版
            sql = @"  SELECT DEF_ID FROM {0}.dbo.Vouchers  WHERE CardNumber = '{1}' ";
            sql = string.Format(sql, Context.ERPDataName, Context.ERPCardNumber);
            DataTable DEF_ID = DBHelper.ExecuteDataset(ConnectString, CommandType.Text, sql).Tables[0];
            if (DEF_ID.Rows.Count == 0)
            {
                throw new Exception("产成品入库单的默认显示模版取得失败");
            }
            Context.ERPDEF_ID = DEF_ID.Rows[0]["DEF_ID"].ToString();
            DEF_ID.Clear();
            #endregion

            SqlConnection conn = new System.Data.SqlClient.SqlConnection(ConnectString);
            conn.Open();
            SqlTransaction sqlTran = conn.BeginTransaction();
            SqlCommand cmd = new SqlCommand();
            cmd.Transaction = sqlTran;
            cmd.Connection = conn;
            try
            {
                #region 修改MES数据库表
                sql = @"INSERT INTO [dbo].[ICSStorageInfo]
                                       ([Serial]
                                       ,[BarCode]
                                       ,[Storage_Serial]
                                       ,[Stack_Serial]
                                       ,[Mtime]
                                       ,[Muser]
                                       ,[WorkPoint],ItemType)
                            SELECT NEWID(),BarCode,'{0}','{1}',GETDATE(),'{2}','{3}','原料'
                             FROM  dbo.ICSStockBarCode WHERE BarCode IN ({4})";
                sql = string.Format(sql, Context.Storage_Serial, Context.Stack_Serial,
                    AppConfig.UserName, AppConfig.WorkPointCode, barCodeStr);
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = @"SELECT BarCode FROM  dbo.ICSStockBarCode 
                WHERE BarCode IN ({0}) AND Status != '新增'";
                sql = string.Format(sql, barCodeStr);
                cmd.CommandText = sql;
                DataTable barDt = SQlReturnData(cmd);
                if (barDt != null && barDt.Rows.Count > 0)
                {
                    throw new Exception("请选择状态为新增的条码进行入库!!");
                }

                sql = @" UPDATE dbo.ICSStockBarCode SET Status = '入库'
                         WHERE BarCode IN ({0})";
                sql = string.Format(sql, barCodeStr);
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                #endregion



                sql = @"SELECT d.ID,d.cVenCode,d.cpocode,d.cCode,d.dDate FROM dbo.ICSStockBarCode a 
                        LEFT JOIN dbo.ICSPurchasingStorage b 
                        ON a.cFree1 = b.Serial
                        LEFT JOIN {0}.dbo.PU_ArrivalVouchs c ON b.Rd01AutoID = c.autoid
                        LEFT JOIN {0}.dbo.PU_ArrivalVouch d ON c.id=d.id
                        WHERE a.BarCode IN ({1})
                        GROUP BY d.ID,d.cVenCode,d.cpocode,d.cCode,d.dDate";
                sql = string.Format(sql, Context.ERPDataName, barCodeStr);
                cmd.CommandText = sql;
                DataTable Hdt = SQlReturnData(cmd);

                for (int j = 0; j < Hdt.Rows.Count;j++ )
                {
                    #region 修改ERP信息
                    #region 汇总信息
                    sql = @"SELECT ROW_NUMBER() OVER(ORDER BY a.ItemCode) AS row,b.Rd01AutoID,d.cVenCode,
                        d.ID,d.cCode,c.iPOsID,c.iOriCost AS 原币无税单价,c.iOriTaxCost AS 原币含税单价,
                        SUM(a.Quantity) AS qty,a.ItemCode,a.BatchCode,0 as erpdid,c.btaxcost,c.iinvexchrate,
                        SUM(a.Quantity)*(CASE WHEN iinvexchrate=0 OR iinvexchrate IS NULL THEN 1 ELSE iinvexchrate END) as sQty,
                        inv.cComUnitCode,b.AssComUnit as cAssComUnitCode into #t1 FROM dbo.ICSStockBarCode a 
                        LEFT JOIN dbo.ICSPurchasingStorage b ON a.cFree1 = b.Serial
                        LEFT JOIN {0}.dbo.PU_ArrivalVouchs c ON b.Rd01AutoID = c.autoid
                        LEFT JOIN {0}.dbo.PU_ArrivalVouch d ON c.id=d.id
                        LEFT JOIN {0}.dbo.Inventory inv ON a.ItemCode=inv.cInvCode
                        WHERE d.ID = '{1}' AND a.BarCode IN ({2})
                        GROUP BY a.ItemCode,a.BatchCode,inv.cComUnitCode,b.AssComUnit,b.Rd01AutoID
                        ,d.cVenCode,d.ID,c.iPOsID,c.iOriCost,c.iOriTaxCost,c.btaxcost,d.cCode,c.iinvexchrate
                        select *,sQty*原币无税单价 as 原币无税金额,sQty*原币含税单价 as 原币价税合计,
                        sQty*原币含税单价-sQty*原币无税单价 as  原币税额 into ##rdqty from #t1
                        select * from ##rdqty
                        drop table #t1";

                    sql = string.Format(sql, Context.ERPDataName, Hdt.Rows[j]["ID"].ToString(), barCodeStr);
                    cmd.CommandText = sql;
                    DataTable temp = SQlReturnData(cmd);
                    #endregion

                    #region 取得ERP的ID
                    string num = "1000000000";
                    sql = @"select count(*) from ##rdqty";
                    cmd.CommandText = sql;
                    DataTable RowCountDt = SQlReturnData(cmd);
                    Context.ERPrdRowCount = Convert.ToInt32(RowCountDt.Rows[0][0]);
                    int id = 0;
                    int did = 0;
                    SaveGetrdIDandAutoID(ConnectString, "采购入库单", "rd", Context.ERPDataName.Substring(7, 3), Context.ERPrdRowCount, out id, out did);
                    string strId = id.ToString();
                    Context.ERPrdID = int.Parse(num.Substring(0, num.Length - strId.Length) + strId);
                    string strDid = did.ToString();
                    Context.ERPrdDid = int.Parse(num.Substring(0, num.Length - strDid.Length) + strDid);
                    #endregion

                    #region  取得ERP的Code
                    sql = @"DECLARE  @Code nvarchar(100)
						    set @Code = ''
						    UPDATE VoucherHistory SET cNumber = cNumber + 1 
						    FROM {0}.dbo.VoucherHistory WHERE CardNumber = '{1}'
						    SELECT @Code = '0000000000' + CAST(cNumber AS NVARCHAR(50)) FROM {0}.dbo.VoucherHistory WHERE CardNumber = '{1}'
						    SET @Code = RIGHT(@Code,10) 
						    select @Code";
                    sql = string.Format(sql, Context.ERPDataName, Context.ERPCardNumber);
                    cmd.CommandText = sql;
                    DataTable codedt = SQlReturnData(cmd);
                    if (codedt.Rows[0][0].ToString() == "")
                    {
                        sql = "insert into {0}.dbo.VoucherHistory values('24',null,'日期','月','null',1,0)";
                        sql = string.Format(sql, Context.ERPDataName);
                        SQlInsertAndUpdate(sql, conn, sqlTran, cmd);
                        Context.ERPrdCode = "0000000001";
                    }
                    if (codedt.Rows[0][0].ToString() != "")
                    {
                        Context.ERPrdCode = codedt.Rows[0][0].ToString();
                    }
                    if (Context.ERPrdCode == "")
                    {
                        throw new Exception("产成品入库单号取得失败");
                    }
                    sql = @"IF EXISTS (SELECT  cCode FROM {1}.dbo.RdRecord01 WHERE ccode = '{0}'  and cVouchType = '10')
					        RAISERROR('产成品入库单号重复，保存失败',16,0)";
                    sql = string.Format(sql, Context.ERPrdCode, Context.ERPDataName);
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();
                    #endregion

                    #region 写入表头
                    sql = @"INSERT INTO {0}.dbo.RdRecord01 (id,bRdFlag,cVouchType,cBusType,cSource,cBusCode,cWhCode,dDate,cCode,cRdCode,
            cVenCode,cOrderCode,cHandler,bTransFlag,cMaker,dVeriDate,bpufirst,biafirst,VT_ID,bIsSTQc,
            ipurorderid,iTaxRate,iExchRate,cExch_Name,bOMFirst,bFromPreYear,bIsComplement,
            iDiscountTaxType,ireturncount,iverifystate,iswfcontrolled,dnmaketime,dnverifytime,
            bredvouch,bCredit,cARVCode,ipurarriveid,dARVDate)
            VALUES
            (@id,'1','01','普通采购','采购到货单',null,@cWhCode,CONVERT(varchar(15), GETDATE(), 23),@cCode,@cRdCode,
            @cVenCode,@cOrderCode,@cHandler,'0',@cMaker,GETDATE(),'0','0',@VT_ID,'0',
            @ipurorderid,'17.000000','1','人民币','0','0','0',
            '0','0','0','0',GETDATE(),GETDATE(),'0','0',@cARVCode,@ipurorderid,@dARVDate)";
                    sql = string.Format(sql, Context.ERPDataName);
                    cmd.CommandText = sql;
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue("@id", Context.ERPrdID);
                    cmd.Parameters.AddWithValue("@cWhCode", Context.ERPcWhCode);
                    cmd.Parameters.AddWithValue("@cCode", Context.ERPrdCode);
                    cmd.Parameters.AddWithValue("@cRdCode", Context.ERPrdcRdCode);
                    cmd.Parameters.AddWithValue("@cVenCode", Hdt.Rows[j]["cVenCode"].ToString());
                    cmd.Parameters.AddWithValue("@cHandler", Context.UserName);
                    cmd.Parameters.AddWithValue("@cMaker", Context.UserName);
                    cmd.Parameters.AddWithValue("@VT_ID", Context.ERPDEF_ID);
                    cmd.Parameters.AddWithValue("@cOrderCode", Hdt.Rows[j]["cpocode"].ToString());
                    cmd.Parameters.AddWithValue("@ipurorderid", Hdt.Rows[j]["ID"].ToString());
                    cmd.Parameters.AddWithValue("@cARVCode", Hdt.Rows[j]["cCode"].ToString());
                    cmd.Parameters.AddWithValue("@dARVDate", Hdt.Rows[j]["dDate"].ToString());
                    cmd.ExecuteNonQuery();

                    #endregion

                    #region 更新表体ID
                    sql = @"SELECT * FROM  ##rdqty";
                    cmd.CommandText = sql;
                    DataTable dt = SQlReturnData(cmd);

                    int rowNo = 1;
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        sql = @"update ##rdqty set erpdid = {0} where row='{1}'  ";
                        sql = string.Format(sql, Context.ERPrdDid, rowNo);
                        Context.ERPrdDid -= 1;
                        rowNo++;
                        cmd.CommandText = sql;
                        cmd.ExecuteNonQuery();
                    }
                    #endregion

                    #region 写入表体
                    sql = @"INSERT INTO {0}.dbo.rdrecords01
            (AutoID,ID,cInvCode,iNum,iQuantity,iUnitCost,iPrice,iAPrice,iFlag,iSQuantity,iSNum,iMoney,
            iPOsID,fACost,iNQuantity,iNNum,cAssUnit,chVencode,iOriTaxCost,iOriCost,iOriMoney,iOriTaxPrice,
            ioriSum,iTaxRate,iTaxPrice,iSum,bTaxCost,cPOID,iMatSettleState,iBillSettleCount,bLPUseFree,
            iOriTrackID,bCosting,iExpiratDateCalcu,iordertype,isotype,irowno,bgift,iArrsId,cbarvcode,iinvexchrate)
            SELECT erpdid,@ID,ItemCode,qty*(CASE WHEN iinvexchrate=0 OR iinvexchrate IS NULL THEN 0 ELSE 1 END),
                qty*(CASE WHEN iinvexchrate=0 OR iinvexchrate IS NULL THEN 1 ELSE iinvexchrate END),
            原币无税单价,原币无税金额,原币无税金额,'0','0','0','0',iPOsID,原币无税单价,
            qty*(CASE WHEN iinvexchrate=0 OR iinvexchrate IS NULL THEN 1 ELSE iinvexchrate END),
            qty*(CASE WHEN iinvexchrate=0 OR iinvexchrate IS NULL THEN 0 ELSE 1 END),cAssComUnitCode,
            cVenCode,原币含税单价,原币无税单价,原币无税金额,原币税额,
            原币价税合计,'17.000000',原币税额,原币价税合计,btaxcost,@cPOID,'0','0','0',
            '0','0','0','0','0',row,'0',Rd01AutoID,cCode,iinvexchrate FROM ##rdqty";

                    sql = string.Format(sql, Context.ERPDataName);

                    cmd.CommandText = sql;
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue("@ID", Context.ERPrdID);
                    cmd.Parameters.AddWithValue("@cPOID", Hdt.Rows[j]["cpocode"].ToString());
                    cmd.ExecuteNonQuery();

                    #endregion

                    #region  改变现存量

                    foreach (DataRow dr in dt.Rows)
                    {

                        int ItemID = -1;
                        double qty = 0;
                        string change = dr["iinvexchrate"].ToString();
                        if (change != "")
                        {
                            qty = double.Parse(dr["qty"].ToString()) * double.Parse(dr["iinvexchrate"].ToString());
                        }
                        else {
                            qty = double.Parse(dr["qty"].ToString());  
                        }
                        string invcode = dr["itemcode"].ToString();
                        string batchCode = dr["batchCode"].ToString();
                        string autoID = dr["itemcode"].ToString();

                        #region 取得物料的itemID
                        sql = @"IF NOT  EXISTS(
							        SELECT Id FROM  {0}.dbo.SCM_Item  WHERE 
								        cFree1 = ''  AND cFree2 = ''  AND cFree3 = ''  AND cFree4 = '' 
								        AND cFree5 = ''  AND cFree6 = ''  AND cFree7 = ''  AND cFree8 = '' 
								        AND cFree9 = ''  AND cFree10 = '' AND cinvcode = '{1}')
								        BEGIN
								        INSERT INTO {0}.dbo.SCM_Item(cInvCode ,
										        cFree1 ,
										        cFree2 ,
										        cFree3 ,
										        cFree4 ,
										        cFree5 ,
										        cFree6 ,
										        cFree7 ,
										        cFree8 ,
										        cFree9 ,
										        cFree10 ,
										        PartId) VALUES('{1}','','','','','','','','','','',0)
 
								        END
								        SELECT Id  FROM 
							        {0}.dbo.SCM_Item  WHERE 
								        cFree1 = ''  AND cFree2 = ''  AND cFree3 = ''  AND cFree4 = '' 
								        AND cFree5 = ''  AND cFree6 = ''  AND cFree7 = ''  AND cFree8 = '' 
								        AND cFree9 = ''  AND cFree10 = '' AND cinvcode = '{1}'";

                        sql = string.Format(sql, Context.ERPDataName, invcode);
                        cmd.CommandText = sql;
                        DataTable Itemdt = SQlReturnData(cmd);
                        if (Itemdt.Rows.Count == 0)
                        {
                            throw new Exception("物料的ItemID取得失败");
                        }
                        ItemID = int.Parse(Itemdt.Rows[0]["Id"].ToString());
                        #endregion

                        //将物料插入到现存两表中
                        //先更新现存量
                        #region 更新失败，插入现存量
                        sql = @"IF EXISTS (SELECT AutoID FROM  {0}.dbo.CurrentStock 
							        WHERE cWhCode = @cWhCode  AND cInvCode = @cInvCode  
                                    AND cBatch = @cBatch  AND ItemId = @ItemId)
						        BEGIN 

						        UPDATE {0}.dbo.CurrentStock SET iQuantity  = iQuantity  + @iQuantity
						        WHERE cWhCode = @cWhCode  AND cInvCode = @cInvCode  AND cBatch = @cBatch
                                AND ItemId = @ItemId

						        END 
						        ELSE
						        BEGIN

                                INSERT INTO {0}.dbo.CurrentStock 
                            (cWhCode,cInvCode,ItemId,cBatch,iSoType,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 @cWhCode,@cInvCode,@ItemId,@cBatch,'0',@iQuantity,
                            '0',@cFree1,'0','0','0','0',@cFree2,
                            @cFree3,'0','0','0','0','0','0','0','0',
                            '0','0','0','0','0','0','0','0' FROM ##rdqty
                            where batchCode = '{1}'
                               END ";
                        sql = string.Format(sql, Context.ERPDataName, batchCode);

                        cmd.CommandText = sql;
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddWithValue("@cWhCode", Context.ERPcWhCode);
                        cmd.Parameters.AddWithValue("@cInvCode", invcode);
                        cmd.Parameters.AddWithValue("@ItemId", ItemID);
                        cmd.Parameters.AddWithValue("@cBatch", batchCode);
                        cmd.Parameters.AddWithValue("@iQuantity", qty);
                        cmd.Parameters.AddWithValue("@cFree1", "");
                        cmd.Parameters.AddWithValue("@cFree2", "");
                        cmd.Parameters.AddWithValue("@cFree3", "");

                        cmd.ExecuteNonQuery();
                        #endregion
                    }
                    #endregion

                    #region 将入库单的ID，DID，Code，ERPName 回写 MES数据库
                    sql = @"INSERT INTO [dbo].[ICSMOStorageINERP]
                                   ([Serial]
                                   ,[BarCode]
                                   ,[rdTypeName]
                                   ,[rdID]
                                   ,[rdDID]
                                   ,[rdCode]
                                   ,[rdDate]
                                   ,[WorkPoint])
                        SELECT NEWID(),a.BarCode,'采购入库','{0}',b.erpdid,'{1}','{2}','{3}'
                         FROM dbo.ICSStockBarCode a inner join ##rdqty b on a.BatchCode = b.BatchCode
                         where BarCode in ({4}) ";
                    sql = string.Format(sql, id, Context.ERPrdCode, Context.UserTime, AppConfig.WorkPointCode, barCodeStr);
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();

                    #endregion

                    #region 更新采购,到货单入库数量
                    sql = @"SELECT Rd01AutoID,SUM(qty*(CASE WHEN iinvexchrate=0 OR iinvexchrate IS NULL THEN 1 ELSE iinvexchrate END)) AS qty,
                                sum(qty*(CASE WHEN iinvexchrate=0 OR iinvexchrate IS NULL THEN 0 ELSE 1 END)) as num INTO ##sumQty FROM ##rdqty 
                            GROUP BY Rd01AutoID";
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();

                    sql = @"SELECT iPOsID,SUM(qty*(CASE WHEN iinvexchrate=0 OR iinvexchrate IS NULL THEN 1 ELSE iinvexchrate END)) AS qty,
                                sum(qty*(CASE WHEN iinvexchrate=0 OR iinvexchrate IS NULL THEN 0 ELSE 1 END)) as num INTO ##POsumQty FROM ##rdqty 
                            GROUP BY iPOsID";
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();

                    sql = @"UPDATE b SET fValidInQuan = ISNULL(fValidInQuan,0)+a.qty,fValidInNum = ISNULL(fValidInNum,0)+a.num
                    FROM ##sumQty a INNER JOIN {0}.dbo.PU_ArrivalVouchs b ON a.Rd01AutoID= b.AutoID";
                    sql = string.Format(sql, Context.ERPDataName);
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();

                    sql = @"UPDATE b SET freceivedqty = ISNULL(freceivedqty,0)+a.qty,freceivednum = ISNULL(freceivednum,0)+a.num 
                    FROM ##POsumQty a INNER JOIN {0}.dbo.PO_Podetails b ON a.iPOsID= b.ID ";
                    sql = string.Format(sql, Context.ERPDataName);
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();
                    #endregion

                    #region 写入记账表
                    sql = @"  INSERT INTO {0}.[dbo].[IA_ST_UnAccountVouch01]
                    SELECT '{1}',erpdid,'{2}','{3}' FROM ##rdqty  
                    drop table ##rdqty
                    drop table ##sumQty
                    drop table ##POsumQty";
                    sql = string.Format(sql, Context.ERPDataName, Context.ERPrdID, "01", "普通采购");
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();
                    #endregion

                    #endregion
                }


                //throw new Exception("操作完成那个，取消保存，来进行下一次测试");
                cmd.Transaction.Commit();
            }
            catch (Exception ex)
            {
                cmd.Transaction.Rollback();
                throw ex;
            }


        }

        #region SQL事物操作函数以及ERP的ID的读取与写入的
        /// 插入或更新的操作
        /// </summary>
        /// <param name="SQl"></param>
        /// <param name="conn"></param>
        /// <param name="sqlTran"></param>
        /// <returns></returns>
        private static int SQlInsertAndUpdate(string SQl, SqlConnection conn, SqlTransaction sqlTran, SqlCommand cmd)
        {
            cmd.Connection = conn;
            cmd.CommandText = SQl;
            cmd.Transaction = sqlTran;
            return cmd.ExecuteNonQuery();
        }

        /// <summary>
        /// 查询SQL
        /// </summary>
        /// <param name="SQl"></param>
        /// <param name="conn"></param>
        /// <param name="sqlTran"></param>
        /// <returns></returns>
        private static DataTable SQlReturnData(SqlCommand cmd)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter dr = new System.Data.SqlClient.SqlDataAdapter();
            dr.SelectCommand = cmd;
            dr.Fill(dt);
            return dt;
        }

        private static void SaveGetrdIDandAutoID(string connectstring, string returnTxt, string IDtype, string cAcc_id, int rowCount, out int id, out int autoID)
        {
            try
            {
                string str = @"DECLARE @ID int
								DECLARE @DID int
								SET @ID = 0
								SET @DID = 0
								IF NOT EXISTS (SELECT * FROM ufsystem..ua_identity   WHERE cacc_id = '{0}'  AND cVouchType = '{1}')
								INSERT INTO ufsystem..ua_identity(cAcc_Id,cVouchType,iFatherId,iChildId) VALUES('{0}','{1}',1,1)
								SELECT @ID = ifatherID + 1 ,@DID = ichildID + {2}
								 FROM  ufsystem..ua_identity  
								 WHERE cVouchType = '{1}'  
								 AND cAcc_id = '{0}'
 
								 UPDATE ufsystem..ua_identity  
								  SET ifatherID = ifatherID + 1,ichildID = ichildID + {2}
								 WHERE cVouchType = '{1}'  AND cAcc_id = '{0}' 
								select @ID as ID,@DID as DID";
                str = string.Format(str, cAcc_id, IDtype, rowCount.ToString());
                DataTable dt = DBHelper.ExecuteDataset(connectstring, CommandType.Text, str).Tables[0];
                if (dt.Rows.Count == 0)
                {
                    throw new Exception("ID取得失败");
                }
                id = Convert.ToInt32(dt.Rows[0]["ID"]);
                autoID = Convert.ToInt32(dt.Rows[0]["DID"]);
            }
            catch (Exception ex)
            {
                throw new Exception(returnTxt + ex.Message);
            }
        }

        #endregion
    
    }

    public class storageInfoContext
    {
        public string ERPDataName { get; set; }
        public string WorkPointCode { get; set; }
        public string UserName { get; set; }
        public DateTime UserTime { get; set; }
        public string Storage_Serial { get; set; }
        public string Stack_Serial { get; set; }
        public string ERPDeptCode { get; set; }
        public string ERPrdCode { get; set; }
        public string ERPrdcRdCode { get; set; }
        public int ERPrdID { get; set; }
        public int ERPrdDid { get; set; }
        public int ERPrdRowCount { get; set; }
        public string ERPcWhCode { get; set; }
        public string ERPrdPerson { get; set; }
        public string ERPcMaker { get; set; }
        public string ERPrdcDate { get; set; }
        public string ERPDEF_ID { get; set; }
        public string ERPCardNumber { get; set; }
        public string ERPcPersonCode { get; set; }


    }
}