using ICSSoft.Common;
using ICSSoft.Entity;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ICSSoft.DataProject
{
    /// <summary>
    /// 委外采购入库单
    /// </summary>
    public class OutsourcingReceiveDoc
    {
        private static log4net.ILog log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
        private static string connString = System.Configuration.ConfigurationManager.AppSettings["ERPConnStr"];
        private static string ERPDB = System.Configuration.ConfigurationManager.AppSettings["ERPDB"];
        private static string Type = System.Configuration.ConfigurationManager.AppSettings["Type"];
        private static string CRNALL = System.Configuration.ConfigurationManager.AppSettings["OutIssRCRDNAME"];
        private static string CRNA = System.Configuration.ConfigurationManager.AppSettings["MoICRDNAME"];
        /// <summary>
        /// 获取委外采购入库单
        /// </summary>
        /// <param name="infos"></param>
        /// <returns></returns>
        public string Get(List<ICSOutsourcingReceiveDoc> infos)
        {

            List<ICSOutsourcingReceiveDoc> szJson = new List<ICSOutsourcingReceiveDoc>();
            DataTable dt = null;
            DataTable dtNew = null;
            string connS = "";
            string json = "";
            if (infos.Count <= 0)
            {
                throw new Exception("传送数据为空！");
            }
            string res = string.Empty;
            SqlConnection conn = new SqlConnection();
            SqlCommand cmd = new SqlCommand();
            string sql = string.Empty;

              List<string> result = infos.Select(t => t.WorkPoint).Distinct().ToList();


              foreach (string WorkPoint in result)
              {
                  try
                  {
                      connS = string.Format(connString, WorkPoint);
                      conn = new System.Data.SqlClient.SqlConnection(connS);
                      conn.Open();
                      SqlTransaction sqlTran = conn.BeginTransaction();
                      cmd = new SqlCommand();
                      cmd.Transaction = sqlTran;
                      cmd.Connection = conn;
                      foreach (ICSOutsourcingReceiveDoc info in infos)
                      {
                          if (WorkPoint != info.WorkPoint)
                              continue;


                          ICSUserInfo userInfo = new ICSUserInfo();
                          userInfo = DBHelper.GetPersonInfo(info.User, cmd);

                          if (info.MTime < new DateTime(2000, 01, 01))
                              throw new Exception("请输入正确的操作时间：" + info.MTime);
                          sql = @" select a.ID,iMPoIds,iordercode,a.cCode,a.cDepCode,c.cDepName,a.cWhCode,
                            d.cWhName,a.cSource,a.cMaker,dnmaketime,cHandler,dnverifytime,cSourceCodeLs,
                            b.AutoID ,b.irowno,b.cInvCode,b.iQuantity,e.MoDetailsID,b.iNum,e.csocode
                            from rdrecord11 a
                            left join rdrecords11 b on a.ID=b.ID
                            left join Department c on a.cDepCode=c.cDepCode
                            left join Warehouse d on a.cWhCode=d.cWhCode
                            left join OM_MODetails e on b.iOMoDID=e.MoDetailsID
                           where a.cBusType='委外加工'";
                          if (!string.IsNullOrWhiteSpace(info.ORCVTCode))
                          {
                              sql += " and a.cCode='{0}'";
                          }
                          if (!string.IsNullOrWhiteSpace(info.MTime.ToString()))
                          {
                              sql += " and ISNULL(a.dnmodifytime,ISNULL(a.dnverifytime, ISNULL(a.dnmodifytime, a.dnmaketime)))>='{1}'";
                          }
                          if (!string.IsNullOrWhiteSpace(info.User))
                          {
                              sql += "and a.CMAKER='{2}'";
                          }
                          sql = string.Format(sql, info.ORCVTCode, info.MTime, userInfo.UserName);
                          dt = DBHelper.SQlReturnData(sql, cmd);

                          if (dt.Rows.Count <= 0 || dt == null)
                              throw new Exception("委外发料单：" + info.ORCVTCode + "，无信息！");
                          if (dtNew == null)
                              dtNew = dt;
                          else
                              dtNew.Merge(dt);



                      }


                      cmd.Transaction.Commit();
                  }
                  catch (Exception ex)
                  {
                      cmd.Transaction.Rollback();
                      log.Error(ex.Message);
                      throw new Exception(ex.Message);
                  }
                  finally
                  {
                      if (conn.State == ConnectionState.Open)
                      {
                          conn.Close();
                      }
                      conn.Dispose();
                  }
              }
            json = JsonConvert.SerializeObject(dtNew);
            return json;

        }
        /// <summary>
        /// 创建委外采购入(到货单)
        /// </summary>
        /// <param name="Bills"></param>
        /// <returns></returns>
        public string CreateOutsourcingReceiveDocByPOArrive(List<ICSOutsourcingReceiveDoc> Bills)
        {
            string msg = "";

            DataSet ds = null;
            string connS = "";
            string sql = "";
            string iFatherIdTwo = "";
            int num = 0;
            int isEnable = 0;
            string gys = "";
            //取得out库单的默认显示模版

            //取得采购入库单单据 表头ID，表体DID
            VouchKey key = new VouchKey();
            SqlConnection conn = new SqlConnection();
            SqlCommand cmd = new SqlCommand();
            if (Bills.Count <= 0)
            {
                throw new Exception("传送数据为空！");
            }
            LogInfo(Bills);

             List<string> result = Bills.Select(t => t.WorkPoint).Distinct().ToList();


             foreach (string WorkPoint in result)
             {
                 try
                 {
                     connS = string.Format(connString, WorkPoint);
                     conn = new System.Data.SqlClient.SqlConnection(connS);
                     conn.Open();
                     SqlTransaction sqlTran = conn.BeginTransaction();
                     cmd = new SqlCommand();
                     cmd.Transaction = sqlTran;
                     cmd.Connection = conn;

                     foreach (ICSOutsourcingReceiveDoc head in Bills)
                     {
                         iFatherIdTwo = "";
                         num = head.details.Count();
                         if (WorkPoint != head.WorkPoint)
                             continue;

                         if (!DBHelper.IsInventoryConsolidation(cmd, head.WorkPoint))
                             throw new Exception("U8正在整理现存量，请稍后再试");

                         string[] ss = head.WorkPoint.Split('_');
                         ERPDB = ss[1];

                         ICSUserInfo userInfo = new ICSUserInfo();
                         userInfo = DBHelper.GetPersonInfo(head.User, cmd);

                         string[] dd = CRNALL.Split('~');
                         string crdname = dd[0];
                         string carname = dd[1];
                         string surface = dd[2];
                         string cardnewcode = DBHelper.GetCardNumber(carname, cmd);
                         //取得out库单的默认显示模版
                         Dictionary<string, int> dic = DBHelper.GetAllCode("" + ERPDB + "", surface, "" + num + "", head.WorkPoint);
                         int iFatherId = Convert.ToInt32(dic["iFatherId"].ToString());
                         int iChildId = Convert.ToInt32(dic["iChildId"].ToString());
                         DateTime date = DateTime.Now;
                         string cRDcode = DBHelper.GetRDCode(crdname, cmd);
                         string iBaseCodeLen = DBHelper.GetAllRDCode(cardnewcode, "" + date + "", "admin", cRDcode, head.WorkPoint);

                         string DEF_ID = DBHelper.GetDefaultTemplate(cardnewcode, cmd);

                         #region 验证数量不能大于到货单数量

                         //foreach (ICSOutsourcingReceiveDocs body in head.details)
                         //    {
                         //        sql = "SELECT isnull(-iQuantity,0) FROM dbo.PU_ArrivalVouchs WHERE Autoid='" + body.ODNDetailID + "'";
                         //        DataTable dtQty = DBHelper.SQlReturnData(sql, cmd);
                         //        if (dtQty != null && dtQty.Rows.Count > 0)
                         //        {
                         //            //到货单可入数量
                         //            decimal PUQty = Convert.ToDecimal(dtQty.Rows[0][0]);
                         //            sql = "SELECT ISNULL(SUM(-iQuantity),0) FROM dbo.RdRecords01 WHERE iArrsId='" + body.ODNDetailID + "'";
                         //            DataTable dtReceQty = DBHelper.SQlReturnData(sql, cmd);
                         //            decimal ReceQty = Convert.ToDecimal(dtReceQty.Rows[0][0]);
                         //            if (ReceQty + body.Quantity > PUQty)
                         //            {
                         //                throw new Exception("入库数量大于当前到货单行可入库数量！");
                         //            }
                         //        }
                         //        else
                         //        {
                         //            throw new Exception("U8不存在该到货单行！");
                         //        }
                         //    }

                         #endregion
                         if (head.Enable == true)
                         {
                             sql = @"SELECT a.POID,a.cVenCode  FROM PO_Pomain a
                                LEFT JOIN PU_ArrivalVouch b ON a.cPOID=b.cpocode WHERE a.cBusType='代管采购' and  b.cCode  ='" + head.ODNCode + "' ";
                             DataTable dtCheck = DBHelper.SQlReturnData(sql, cmd);
                             if (dtCheck != null && dtCheck.Rows.Count > 0)
                             {
                                 gys = dtCheck.Rows[0][1].ToString();
                                 isEnable = 0;
                                 sql = @"SELECT cWhCode  FROM Warehouse WHERE bProxyWh ='1'   ";
                                 dtCheck = DBHelper.SQlReturnData(sql, cmd);
                                 if (dtCheck != null && dtCheck.Rows.Count > 0)
                                 {
                                     foreach (DataRow item in dtCheck.Rows)
                                     {
                                         if (head.WHCode == item["cWhCode"].ToString())
                                         {
                                             isEnable++;
                                         }
                                     }

                                 }
                                 if (isEnable == 0)
                                 {
                                     throw new Exception("该仓库：" + head.WHCode + @"不是代管仓，请重新输入！");
                                 }

                             }



                             sql = @"SELECT POID  FROM PO_Pomain a
                                LEFT JOIN PU_ArrivalVouch b ON a.cPOID=b.cpocode WHERE a.cBusType='固定资产' and  b.cCode  ='" + head.ODNCode + "' ";
                             dtCheck = DBHelper.SQlReturnData(sql, cmd);
                             if (dtCheck != null && dtCheck.Rows.Count > 0)
                             {
                                 int isZL = 0;
                                 sql = @"SELECT cWhCode  FROM Warehouse WHERE bWhAsset ='1'   ";
                                 dtCheck = DBHelper.SQlReturnData(sql, cmd);
                                 if (dtCheck != null && dtCheck.Rows.Count > 0)
                                 {
                                     foreach (DataRow item in dtCheck.Rows)
                                     {
                                         if (head.WHCode == item["cWhCode"].ToString())
                                         {
                                             isZL++;
                                         }
                                     }

                                 }
                                 if (isZL == 0)
                                 {
                                     throw new Exception("该仓库：" + head.WHCode + @"不是资产仓，请重新输入！");
                                 }

                             }
                         }

                         string cfcode = string.Empty;
                         string zq = string.Empty;
                         string lz = string.Empty;
                         string dz = string.Empty;

                         sql = @"select  a.cCode,a.iLZYJ,a.iLZFS,iPLNum,dblZQNum,c.dDate,
                        (case when ilzfs=1 then dateadd(day,-day(getdate()),dateadd(month,1,getdate())) else DATEADD(day,isnull(iPLNum,0),c.dDate) end)
                         lz,DATEADD(day,isnull(dblZQNum,0),DATEADD(day,isnull(iPLNum,0),c.dDate)) dz 
                        from 
                        (select b.cVenPUOMProtocol,a.cvencode,a.dDate,a.cCode from PU_ArrivalVouch a
                        left join Vendor b on a.cvencode=b.cvencode
                        where  a.cCode='" + head.ODNCode + @"'
                        ) c 
                        left join AA_Agreement a on a.cCode=c.cVenPUOMProtocol

                        ";
                         DataTable dtDisCheck1 = DBHelper.SQlReturnData(sql, cmd);
                         if (dtDisCheck1 != null && dtDisCheck1.Rows.Count > 0)
                         {
                             foreach (DataRow item in dtDisCheck1.Rows)
                             {
                                 if (item["iLZYJ"].ToString() == "10" & (item["iLZFS"].ToString() == "0" | item["iLZFS"].ToString() == "1"))
                                 {
                                     cfcode = item["cCode"].ToString();
                                     zq = item["dblZQNum"].ToString();
                                     lz = item["lz"].ToString();
                                     dz = item["dz"].ToString();
                                 }
                                 else
                                 {
                                     if (item["cCode"].ToString()=="")
                                         cfcode = item["cCode"].ToString();
                                 }
                             }
                         }
                        



                         #region 委外采购入库单 表头

                         sql = @"INSERT INTO dbo.RdRecord01
                        ( ID ,bRdFlag ,cVouchType ,cBusType ,cSource ,cWhCode ,
                          dDate ,cCode ,cRdCode ,cDepCode ,cPersonCode ,cPTCode ,
                          cVenCode ,cOrderCode ,cARVCode ,cHandler ,cMemo ,
                          bTransFlag ,cMaker ,cDefine1 ,cDefine2 ,cDefine3 ,
                          cDefine4 ,cDefine5 ,cDefine6 ,cDefine7 ,cDefine8 ,
                          cDefine9 ,cDefine10 ,dVeriDate ,bpufirst ,biafirst ,
                		  dARVDate ,VT_ID ,bIsSTQc ,cDefine11 ,cDefine12 ,
                          cDefine13 ,cDefine14 ,cDefine15 ,cDefine16 ,
                          ipurarriveid ,iTaxRate ,iExchRate ,cExch_Name ,bOMFirst,
                          bFromPreYear ,bIsComplement ,iDiscountTaxType ,
                          ireturncount ,iverifystate ,iswfcontrolled ,
                          cModifyPerson ,dnmaketime ,dnverifytime ,
                          bredvouch  ,bCredit ,iPrintCount,cVenPUOMProtocol,iCreditPeriod,dCreditStart,dGatheringDate)
                SELECT @ID,1,'01','委外加工','委外到货单',@cWhCode,
                		CONVERT(NVARCHAR(15),GETDATE(),23),@cCode,@cRdCode,@cDepCode,a.cPersonCode,'01',
                		a.cVenCode,b.cCode,a.cCode,@cHandler,a.cMemo,
                		0,@cMaker,a.cDefine1,a.cDefine2,a.cDefine3,
                		a.cDefine4,a.cDefine5,a.cDefine6,a.cDefine7,a.cDefine8,
                		a.cDefine9,a.cDefine10,CONVERT(NVARCHAR(15),GETDATE(),23),0,0,
                		a.dDate,@VT_ID,0,a.cDefine11,a.cDefine12,
                		a.cDefine13,a.cDefine14,a.cDefine15,a.cDefine16,
                		a.ID,a.iTaxRate,a.iExchRate,a.cexch_name,0,
                		0,0,0,
                		0,0,0,
                		'',GETDATE(),GETDATE(),
                		0,0,0,@cVenPUOMProtocol,@iCreditPeriod,@dCreditStart,@dGatheringDate
                 FROM dbo.PU_ArrivalVouch a LEFT JOIN dbo.OM_MOMain b ON a.cpocode=b.cCode WHERE a.cCode='" + head.ODNCode + "'";
                         cmd.Parameters.Clear();
                         cmd.Parameters.Add(new SqlParameter("@ID", iFatherId));
                         cmd.Parameters.Add(new SqlParameter("@cWhCode", head.WHCode));
                         cmd.Parameters.Add(new SqlParameter("@cCode", iBaseCodeLen));
                         cmd.Parameters.Add(new SqlParameter("@cDepCode", ""));
                         cmd.Parameters.Add(new SqlParameter("@cHandler", userInfo.UserName));
                         cmd.Parameters.Add(new SqlParameter("@cMaker", userInfo.UserName));
                         cmd.Parameters.Add(new SqlParameter("@VT_ID", DEF_ID));
                         cmd.Parameters.Add(new SqlParameter("@cRdCode", cRDcode));
                         cmd.Parameters.Add(new SqlParameter("@cVenPUOMProtocol", cfcode));
                         cmd.Parameters.Add(new SqlParameter("@iCreditPeriod", zq));
                         cmd.Parameters.Add(new SqlParameter("@dCreditStart", lz));
                         cmd.Parameters.Add(new SqlParameter("@dGatheringDate", dz));
                         cmd.CommandText = sql;
                         try
                         {
                             int count = cmd.ExecuteNonQuery();
                             if (count <= 0)
                             {
                                 log.Error("委外采购入库单表头失败，受影响行数<=0；");
                                 throw new Exception("委外采购入库单表头失败，受影响行数<=0；");
                             }
                         }
                         catch (Exception ex)
                         {
                             log.Error("委外采购入库单表头失败！SQL：\r\n" + sql, ex);
                             throw new Exception("委外采购入库单表头失败！SQL：\r\n" + sql, ex);
                         }


                         #endregion

                         #region 采购入库单 表体
                         foreach (ICSOutsourcingReceiveDocs body in head.details)
                         {

                             if (DBHelper.bInvBatch(body.InvCode, cmd) == true)
                             {
                                 if (body.BatchCode == "")
                                 {
                                     log.Error("该物料：" + body.InvCode + "已启用批次，请重新填写！");
                                     throw new Exception("该物料：" + body.InvCode + "已启用批次，请重新填写！");
                                 }
                             }
                             else
                             {
                                 if (body.BatchCode != "")
                                 {
                                     log.Error("该物料：" + body.InvCode + "未启用批次，请重新填写！");
                                     throw new Exception("该物料：" + body.InvCode + "未启用批次，请重新填写！");
                                 }
                             }
                             sql = @"INSERT INTO dbo.rdrecords01
          ( AutoID ,ID ,cInvCode ,iQuantity ,iUnitCost ,iPrice ,iAPrice ,iNum,
            iFlag ,iSQuantity ,iSNum ,iMoney ,
            cDefine22 ,cDefine23 ,cDefine24 ,cDefine25 ,cDefine26 ,cDefine27 ,
            cItem_class ,cItemCode ,iPOsID ,fACost ,cName  ,iNQuantity ,
            chVencode ,iArrsId ,iOriTaxCost ,iOriCost ,iOriMoney ,iOriTaxPrice ,
            ioriSum ,iTaxRate ,iTaxPrice ,iSum ,bTaxCost ,cPOID ,iMatSettleState ,
            iBillSettleCount ,bLPUseFree ,iOriTrackID ,bCosting ,cbarvcode ,
            dbarvdate ,iExpiratDateCalcu ,iordertype ,isotype ,irowno ,bgift,cBatch,iProcessFee, iProcessCost,cFree1,cFree2,cFree3,cFree4,cFree5,cFree6,cFree7,cFree8,cFree9,cFree10 )
  SELECT @AutoID,@ID1,@cInvCode,@iQuantity,0,0,0,@iNum,
  		0,0,0,0,
  		a.cDefine22,a.cDefine23,a.cDefine24,a.cDefine25,a.cDefine26,a.cDefine27,
  		a.cItem_class,@cItemCode,a.iPOsID,0,a.cItemName,a.iQuantity,
  		c.cVenCode,a.Autoid,0,0,0,0,
  		0,a.iTaxRate,0,0,1,d.cCode,0,
  		0,0,0,1,c.cCode,
  		c.dDate,0,0,0,@irowno,0,@cBatch,a.iCost*convert(decimal, @iQuantity),a.iCost,@cFree1,@cFree2,@cFree3,@cFree4,@cFree5,@cFree6,@cFree7,@cFree8,@cFree9,@cFree10
  FROM dbo.PU_ArrivalVouchs a LEFT JOIN dbo.OM_MODetails b ON a.iPOsID=b.MODetailsID
  INNER JOIN dbo.OM_MOMain d ON b.MOID=d.MOID
  INNER JOIN dbo.PU_ArrivalVouch c ON a.ID=c.ID where a.Autoid='" + body.ODNDetailID + @"'
                        
                        EXEC PROC_Update_OM_RdRecord01 @ID= @ID1
                        ";
                             cmd.Parameters.Clear();
                             cmd.Parameters.Add(new SqlParameter("@AutoID", iChildId));
                             cmd.Parameters.Add(new SqlParameter("@ID1", iFatherId));
                             cmd.Parameters.Add(new SqlParameter("@cInvCode", body.InvCode));
                             cmd.Parameters.Add(new SqlParameter("@iQuantity", body.Quantity));
                             cmd.Parameters.Add(new SqlParameter("@iNum", body.Amount.ToString("0.00")));
                             cmd.Parameters.Add(new SqlParameter("@irowno", body.Sequence));
                             cmd.Parameters.Add(new SqlParameter("@cBatch", body.BatchCode));
                             cmd.Parameters.Add(new SqlParameter("@cItemCode", body.ProjectCode));
                             cmd.Parameters.Add(new SqlParameter("@cFree1", body.cFree1));
                             cmd.Parameters.Add(new SqlParameter("@cFree2", body.cFree2));
                             cmd.Parameters.Add(new SqlParameter("@cFree3", body.cFree3));
                             cmd.Parameters.Add(new SqlParameter("@cFree4", body.cFree4));
                             cmd.Parameters.Add(new SqlParameter("@cFree5", body.cFree5));
                             cmd.Parameters.Add(new SqlParameter("@cFree6", body.cFree6));
                             cmd.Parameters.Add(new SqlParameter("@cFree7", body.cFree7));
                             cmd.Parameters.Add(new SqlParameter("@cFree8", body.cFree8));
                             cmd.Parameters.Add(new SqlParameter("@cFree9", body.cFree9));
                             cmd.Parameters.Add(new SqlParameter("@cFree10", body.cFree10));
                             cmd.CommandText = sql;
                             try
                             {
                                 int count = cmd.ExecuteNonQuery();
                                 if (count <= 0)
                                 {
                                     log.Error("委外采购入库单表体失败，受影响行数<=0；");
                                     throw new Exception("委外采购入库单表体失败，受影响行数<=0；");
                                 }
                             }
                             catch (Exception ex)
                             {
                                 log.Error("委外采购入库单表体失败！SQL：\r\n" + sql, ex);
                                 throw new Exception("委外采购入库单表体失败！SQL：\r\n" + sql, ex);
                             }


                             //OM_MODetails
                             sql = @"UPDATE b SET freceivedqty=ISNULL(freceivedqty,0)+" + body.Quantity + @" 
                                FROM dbo.PU_ArrivalVouchs a LEFT JOIN dbo.OM_MODetails b ON a.iPOsID=b.MODetailsID
                                WHERE a.Autoid='" + body.ODNDetailID + "'";
                             DBHelper.CmdExecuteNonQuery(sql, cmd, "回写OM_MODetails失败！");
                             //回写PU_ArrivalVouchs失败
                             sql = "UPDATE dbo.PU_ArrivalVouchs SET fValidInQuan=ISNULL(fValidInQuan,0)+" + body.Quantity + " WHERE Autoid='" + body.ODNDetailID + "'";
                             DBHelper.CmdExecuteNonQuery(sql, cmd, "回写PU_ArrivalVouchs失败！");



                             sql = @"select isnull(fValidInQuan,0)fOutQuantity,isnull(iQuantity,0)iQuantity,ISNULL(iNum, 0)iNum,ISNULL(NiNum, 0)NiNum,ISNULL(iNum, 0)-ISNULL(NiNum, 0) SiNum from PU_ArrivalVouchs a
                        LEFT JOIN (select sum(iNum) NiNum,iArrsId from rdrecords01 GROUP BY iArrsId) b on a.Autoid=b.iArrsId
                        where  a.AutoID='" + body.ODNDetailID + "'";
                             DataTable dtDisCheck = DBHelper.SQlReturnData(sql, cmd);
                             if (dtDisCheck != null && dtDisCheck.Rows.Count > 0)
                             {
                                 foreach (DataRow item in dtDisCheck.Rows)
                                 {
                                     if (Convert.ToDecimal(item["fOutQuantity"].ToString()) == Convert.ToDecimal(item["iQuantity"].ToString()))
                                     {
                                         if (Convert.ToDecimal(item["iNum"].ToString()) != 0)
                                         {
                                             if (Convert.ToDecimal(item["SiNum"].ToString()) != 0)
                                             {
                                                 sql = @"Update rdrecords09 set iNum=isnull(iNum,0)+(" + item["SiNum"].ToString() + ") where autoid='" + iChildId + "' ";
                                                 body.Amount = (Convert.ToDecimal(body.Amount.ToString("0.00"))) + (Convert.ToDecimal(item["SiNum"].ToString()));
                                                 DBHelper.CmdExecuteNonQuery(sql, cmd, "辅计量余数处理失败！");
                                             }
                                         }
                                     }
                                 }

                             }

                             //if (isEnable != 0)
                             //{
                             //    key.cBustypeUN = "委外加工";
                             //    key.cVouchTypeUN = "01";
                             //    key.TableName = "IA_ST_UnAccountVouch01";
                             //    //DBHelper.UpdateCurrentStock(cmd, body.InvCode, head.WHCode, "", body.Quantity, key);
                             //    DBHelper.UpdateCurrentStockNEWDG(cmd, body.InvCode, head.WHCode, body.BatchCode, body.Quantity, body.cFree1, body.cFree2, body.cFree3, body.cFree4, body.cFree5, body.cFree6, body.cFree7, body.cFree8, body.cFree9, body.cFree10, gys, key);
                             //}
                             //else
                             //{
                             //    //更新现存量
                             //    key.cBustypeUN = "委外加工";
                             //    key.cVouchTypeUN = "01";
                             //    key.TableName = "IA_ST_UnAccountVouch01";
                             //    //DBHelper.UpdateCurrentStock(cmd, body.InvCode, head.WHCode, "", body.Quantity, key);
                             //    DBHelper.UpdateCurrentStockNEW(cmd, body.InvCode, head.WHCode, body.BatchCode, body.Quantity, body.cFree1, body.cFree2, body.cFree3, body.cFree4, body.cFree5, body.cFree6, body.cFree7, body.cFree8, body.cFree9, body.cFree10, key);
                             //}
                             key.cBustypeUN = "委外加工";
                             key.cVouchTypeUN = "01";
                             key.TableName = "IA_ST_UnAccountVouch01";
                             DBHelper.UpdateCurrentStockCCGC(cmd, body.InvCode, head.WHCode, body.BatchCode, body.Quantity, body.Amount, body.cFree1,
                                      body.cFree2, body.cFree3, body.cFree4, body.cFree5, body.cFree6, body.cFree7, body.cFree8, body.cFree9, body.cFree10,
                                      gys, key, "", "", "", "", 0, 0, 0, 0, 0, 0, 0, 0, iFatherId, iChildId);

                         

                             iFatherIdTwo = "'" + iFatherId + "',";
                             iChildId--;

                         }

                         #endregion
                         sql = @"  select DISTINCT a.ID as ID,a.ID as IDs,a.cCode  as ORCVTCode ,a.cVenCode,d.cVenName,a.cOrderCode as OOCode, 
                       a.cMaker as CreateUser,dnmaketime as CreateDateTime,cHandler as Checker,dnverifytime as CheckDateTime
                           from rdrecord01 a
                       left join Department c on a.cDepCode=c.cDepCode
                       left join Vendor d on a.cVenCode=d.cVenCode
                          where a.cBusType='委外加工' AND A.ID in ({0}) 

                          select DISTINCT a.ID as IDs,b.AutoID as DetailID,b.irowno as Sequence,b.cInvCode as InvCode,b.iQuantity as Quantity,iNum as Amount,iArrsId as ODNDetailID
,isnull(b.cItemCode,'') ProjectCode,isnull(b.cbatch,'') cBatch,'' version ,'' brand,
isnull(b.cFree1,'') as cFree1,
isnull(b.cFree2,'') as cFree2,
isnull(b.cFree3,'') as cFree3,
isnull(b.cFree4,'') as cFree4,
isnull(b.cFree5,'') as cFree5,
isnull(b.cFree6,'') as cFree6,
isnull(b.cFree7,'') as cFree7,
isnull(b.cFree8,'') as cFree8,
isnull(b.cFree9,'') as cFree9,
isnull(b.cFree10,'') as cFree10
                           from rdrecord01 a
                           left join rdrecords01 b on a.ID=b.ID
                           left join Department c on a.cDepCode=c.cDepCode
                           left join Vendor d on a.cVenCode=d.cVenCode
                          where a.cBusType='委外加工' AND A.ID in ({0}) ";
                         sql = string.Format(sql, iFatherIdTwo.TrimEnd(','));
                         if (ds != null)
                             ds.Merge(DBHelper.SQlReturnDataSet(sql, cmd));
                         else
                             ds = DBHelper.SQlReturnDataSet(sql, cmd);


                       
                     }
                     cmd.Transaction.Commit();

                 }
                 catch (Exception ex)
                 {
                     cmd.Transaction.Rollback();
                     log.Error(ex.Message);
                     throw new Exception(ex.Message);
                 }
                 finally
                 {
                     if (conn.State == ConnectionState.Open)
                     {
                         conn.Close();
                     }
                     conn.Dispose();
                 }
             }
             msg = JSON.DataSetToJson(ds, "details", "IDs");
            return msg;
        }

        /// <summary>
        /// 创建委外采购入(委外到货单)
        /// </summary>
        /// <param name="Bills"></param>
        /// <returns></returns>
        public string CreateOutsourcingReceiveDocByPU(List<ICSOutsourcingReceiveDoc> Bills)
        {
            string msg = "";
            string gys = "";
            int isEnable = 0;
            string connS = "";
            int num = 0;
            //取得out库单的默认显示模版

            //取得采购入库单单据 表头ID，表体DID
            VouchKey key = new VouchKey();

            SqlConnection conn = new SqlConnection();
            SqlCommand cmd = new SqlCommand();


            if (Bills.Count <= 0)
            {
                throw new Exception("传送数据为空！");
            }
            LogInfo(Bills);
              List<string> result = Bills.Select(t => t.WorkPoint).Distinct().ToList();


              foreach (string WorkPoint in result)
              {
                  try
                  {
                      connS = string.Format(connString, WorkPoint);
                      conn = new System.Data.SqlClient.SqlConnection(connS);
                      conn.Open();
                      SqlTransaction sqlTran = conn.BeginTransaction();
                      cmd = new SqlCommand();
                      cmd.Transaction = sqlTran;
                      cmd.Connection = conn;
                      foreach (ICSOutsourcingReceiveDoc head in Bills)
                      {

                          num = head.details.Count();
                          if (WorkPoint != head.WorkPoint)
                              continue;

                          string[] ss = head.WorkPoint.Split('_');
                          ERPDB = ss[1];

                          string[] dd = CRNALL.Split('~');
                          string crdname = dd[0];
                          string carname = dd[1];
                          string surface = dd[2];
                          string cardnewcode = DBHelper.GetCardNumber(carname, cmd);
                          //取得out库单的默认显示模版
                          Dictionary<string, int> dic = DBHelper.GetAllCode("" + ERPDB + "", surface, "" + num + "", head.WorkPoint);
                          int iFatherId = Convert.ToInt32(dic["iFatherId"].ToString());
                          int iChildId = Convert.ToInt32(dic["iChildId"].ToString());
                          DateTime date = DateTime.Now;
                          string cRDcode = DBHelper.GetRDCode(crdname, cmd);
                          string iBaseCodeLen = DBHelper.GetAllRDCode(cardnewcode, "" + date + "", "admin", cRDcode, head.WorkPoint);

                          string DEF_ID = DBHelper.GetDefaultTemplate(cardnewcode, cmd);
                          string sql = "";

                          ICSUserInfo userInfo = new ICSUserInfo();
                          userInfo = DBHelper.GetPersonInfo(head.User, cmd);

                          #region 验证数量不能大于到货单数量

                          foreach (ICSOutsourcingReceiveDocs body in head.details)
                          {
                              sql = "SELECT isnull(-iQuantity,0) FROM dbo.PU_ArrivalVouchs WHERE Autoid='" + body.ODNDetailID + "'";
                              DataTable dtQty = DBHelper.SQlReturnData(sql, cmd);
                              if (dtQty != null && dtQty.Rows.Count > 0)
                              {
                                  //到货单可入数量
                                  decimal PUQty = Convert.ToDecimal(dtQty.Rows[0][0]);
                                  sql = "SELECT ISNULL(SUM(-iQuantity),0) FROM dbo.RdRecords01 WHERE iArrsId='" + body.ODNDetailID + "'";
                                  DataTable dtReceQty = DBHelper.SQlReturnData(sql, cmd);
                                  decimal ReceQty = Convert.ToDecimal(dtReceQty.Rows[0][0]);
                                  if (ReceQty + body.Quantity > PUQty)
                                  {
                                      throw new Exception("入库数量大于当前到货单行可入库数量！");
                                  }
                              }
                              else
                              {
                                  throw new Exception("U8不存在该到货单行！");
                              }
                          }

                          #endregion

                          string cfcode = string.Empty;
                          string zq = string.Empty;
                          string lz = string.Empty;
                          string dz = string.Empty;

                          sql = @"select  a.cCode,a.iLZYJ,a.iLZFS,iPLNum,dblZQNum,c.dDate,
                        (case when ilzfs=1 then dateadd(day,-day(getdate()),dateadd(month,1,getdate())) else DATEADD(day,isnull(iPLNum,0),c.dDate) end)
                         lz,DATEADD(day,isnull(dblZQNum,0),DATEADD(day,isnull(iPLNum,0),c.dDate)) dz 
                        from 
                        (select b.cVenPUOMProtocol,a.cvencode,a.dDate,a.cCode from PU_ArrivalVouch a
                        left join Vendor b on a.cvencode=b.cvencode
                        where  a.cCode='" + head.ODNCode + @"'
                        ) c 
                        left join AA_Agreement a on a.cCode=c.cVenPUOMProtocol

                        ";
                          DataTable dtDisCheck1 = DBHelper.SQlReturnData(sql, cmd);
                          if (dtDisCheck1 != null && dtDisCheck1.Rows.Count > 0)
                          {
                              foreach (DataRow item in dtDisCheck1.Rows)
                              {
                                  if (item["iLZYJ"].ToString() == "10" & (item["iLZFS"].ToString() == "0" | item["iLZFS"].ToString() == "1"))
                                  {
                                      cfcode = item["cCode"].ToString();
                                      zq = item["dblZQNum"].ToString();
                                      lz = item["lz"].ToString();
                                      dz = item["dz"].ToString();
                                  }
                                  else
                                  {
                                      if (item["cCode"].ToString() == "")
                                          cfcode = item["cCode"].ToString();
                                  }
                              }
                          }

                          if (head.Enable == true)
                          {
                              sql = @"SELECT a.POID,a.cVenCode  FROM PO_Pomain a
                                LEFT JOIN PU_ArrivalVouch b ON a.cPOID=b.cpocode WHERE a.cBusType='代管采购' and  b.cCode  ='" + head.ODNCode + "' ";
                              DataTable dtCheck = DBHelper.SQlReturnData(sql, cmd);
                              if (dtCheck != null && dtCheck.Rows.Count > 0)
                              {
                                  gys = dtCheck.Rows[0][1].ToString();
                                  isEnable = 0;
                                  sql = @"SELECT cWhCode  FROM Warehouse WHERE bProxyWh ='1'   ";
                                  dtCheck = DBHelper.SQlReturnData(sql, cmd);
                                  if (dtCheck != null && dtCheck.Rows.Count > 0)
                                  {
                                      foreach (DataRow item in dtCheck.Rows)
                                      {
                                          if (head.WHCode == item["cWhCode"].ToString())
                                          {
                                              isEnable++;
                                          }
                                      }

                                  }
                                  if (isEnable == 0)
                                  {
                                      throw new Exception("该仓库：" + head.WHCode + @"不是代管仓，请重新输入！");
                                  }

                              }



                              sql = @"SELECT POID  FROM PO_Pomain a
                                LEFT JOIN PU_ArrivalVouch b ON a.cPOID=b.cpocode WHERE a.cBusType='固定资产' and  b.cCode  ='" + head.ODNCode + "' ";
                              dtCheck = DBHelper.SQlReturnData(sql, cmd);
                              if (dtCheck != null && dtCheck.Rows.Count > 0)
                              {
                                  int isZL = 0;
                                  sql = @"SELECT cWhCode  FROM Warehouse WHERE bWhAsset ='1'   ";
                                  dtCheck = DBHelper.SQlReturnData(sql, cmd);
                                  if (dtCheck != null && dtCheck.Rows.Count > 0)
                                  {
                                      foreach (DataRow item in dtCheck.Rows)
                                      {
                                          if (head.WHCode == item["cWhCode"].ToString())
                                          {
                                              isZL++;
                                          }
                                      }

                                  }
                                  if (isZL == 0)
                                  {
                                      throw new Exception("该仓库：" + head.WHCode + @"不是资产仓，请重新输入！");
                                  }

                              }
                          }

                          #region 委外采购入库单 表头

                          sql = @"INSERT INTO dbo.RdRecord01
                        ( ID ,bRdFlag ,cVouchType ,cBusType ,cSource ,cWhCode ,
                          dDate ,cCode ,cRdCode ,cDepCode ,cPersonCode ,cPTCode ,
                          cVenCode ,cOrderCode ,cARVCode ,cHandler ,cMemo ,
                          bTransFlag ,cMaker ,cDefine1 ,cDefine2 ,cDefine3 ,
                          cDefine4 ,cDefine5 ,cDefine6 ,cDefine7 ,cDefine8 ,
                          cDefine9 ,cDefine10 ,dVeriDate ,bpufirst ,biafirst ,
                		  dARVDate ,VT_ID ,bIsSTQc ,cDefine11 ,cDefine12 ,
                          cDefine13 ,cDefine14 ,cDefine15 ,cDefine16 ,
                          ipurarriveid ,iTaxRate ,iExchRate ,cExch_Name ,bOMFirst,
                          bFromPreYear ,bIsComplement ,iDiscountTaxType ,
                          ireturncount ,iverifystate ,iswfcontrolled ,
                          cModifyPerson ,dnmaketime ,dnverifytime ,
                          bredvouch  ,bCredit ,iPrintCount,cVenPUOMProtocol,iCreditPeriod,dCreditStart,dGatheringDate)
                SELECT @ID,1,'01','委外加工','委外到货单',@cWhCode,
                		CONVERT(NVARCHAR(15),GETDATE(),23),@cCode,@cRdCode,a.cDepCode,a.cPersonCode,a.cPTCode,
                		a.cVenCode,b.cCode,a.cCode,@cHandler,a.cMemo,
                		0,@cMaker,a.cDefine1,a.cDefine2,a.cDefine3,
                		a.cDefine4,a.cDefine5,a.cDefine6,a.cDefine7,a.cDefine8,
                		a.cDefine9,a.cDefine10,CONVERT(NVARCHAR(15),GETDATE(),23),0,0,
                		a.dDate,@VT_ID,0,a.cDefine11,a.cDefine12,
                		a.cDefine13,a.cDefine14,a.cDefine15,a.cDefine16,
                		a.ID,a.iTaxRate,a.iExchRate,a.cexch_name,0,
                		0,0,0,
                		0,0,0,
                		'',GETDATE(),GETDATE(),
                		0,0,0,@cVenPUOMProtocol,@iCreditPeriod,@dCreditStart,@dGatheringDate
                 FROM dbo.PU_ArrivalVouch a LEFT JOIN dbo.OM_MOMain b ON a.cpocode=b.cCode WHERE a.cCode ='" + head.ODNCode + "'";
                          cmd.Parameters.Clear();
                          cmd.Parameters.Add(new SqlParameter("@ID", iFatherId));
                          cmd.Parameters.Add(new SqlParameter("@cWhCode", head.WHCode));
                          cmd.Parameters.Add(new SqlParameter("@cCode", iBaseCodeLen));
                    
                          cmd.Parameters.Add(new SqlParameter("@cHandler", userInfo.UserName));
                          cmd.Parameters.Add(new SqlParameter("@cMaker", userInfo.UserName));
                          cmd.Parameters.Add(new SqlParameter("@VT_ID", DEF_ID));
                          cmd.Parameters.Add(new SqlParameter("@cRdCode", cRDcode));
                          cmd.Parameters.Add(new SqlParameter("@cVenPUOMProtocol", cfcode));
                          cmd.Parameters.Add(new SqlParameter("@iCreditPeriod", zq));
                          cmd.Parameters.Add(new SqlParameter("@dCreditStart", lz));
                          cmd.Parameters.Add(new SqlParameter("@dGatheringDate", dz));
                          cmd.CommandText = sql;
                          try
                          {
                              int count = cmd.ExecuteNonQuery();
                              if (count <= 0)
                              {
                                  log.Error("委外采购入库单表头失败，受影响行数<=0；");
                                  throw new Exception("委外采购入库单表头失败，受影响行数<=0；");
                              }
                          }
                          catch (Exception ex)
                          {
                              log.Error("委外采购入库单表头失败！SQL：\r\n" + sql, ex);
                              throw new Exception("委外采购入库单表头失败！SQL：\r\n" + sql, ex);
                          }


                          #endregion

                          #region 采购入库单 表体
                          int irowno = 0;
                          foreach (ICSOutsourcingReceiveDocs body in head.details)
                          {

                              if (DBHelper.bInvBatch(body.InvCode, cmd) == true)
                              {
                                  if (body.BatchCode == "")
                                  {
                                      log.Error("该物料：" + body.InvCode + "已启用批次，请重新填写！");
                                      throw new Exception("该物料：" + body.InvCode + "已启用批次，请重新填写！");
                                  }
                              }
                              else
                              {
                                  if (body.BatchCode != "")
                                  {
                                      log.Error("该物料：" + body.InvCode + "未启用批次，请重新填写！");
                                      throw new Exception("该物料：" + body.InvCode + "未启用批次，请重新填写！");
                                  }
                              }
                              sql = @"INSERT INTO dbo.rdrecords01
          ( AutoID ,ID ,cInvCode ,iQuantity ,iUnitCost ,iPrice ,iAPrice ,iNum,
            iFlag ,iSQuantity ,iSNum ,iMoney ,
            cDefine22 ,cDefine23 ,cDefine24 ,cDefine25 ,cDefine26 ,cDefine27 ,
            cItem_class ,cItemCode ,iPOsID ,fACost ,cName  ,iNQuantity ,
            chVencode ,iArrsId ,iOriTaxCost ,iOriCost ,iOriMoney ,iOriTaxPrice ,
            ioriSum ,iTaxRate ,iTaxPrice ,iSum ,bTaxCost ,cPOID ,iMatSettleState ,
            iBillSettleCount ,bLPUseFree ,iOriTrackID ,bCosting ,cbarvcode ,
            dbarvdate ,iExpiratDateCalcu ,iordertype ,isotype ,irowno ,bgift,cBatch,iProcessFee, iProcessCost,cFree1,cFree2,cFree3,cFree4,cFree5,cFree6,cFree7,cFree8,cFree9,cFree10 )
  SELECT @AutoID,@ID1,@cInvCode,@iQuantity,0,0,0,@iNum,
  		0,0,0,0,
  		a.cDefine22,a.cDefine23,a.cDefine24,a.cDefine25,a.cDefine26,a.cDefine27,
  		a.cItem_class,a.cItemCode,a.iPOsID,0,a.cItemName,a.iQuantity,
  		c.cVenCode,a.Autoid,0,0,0,0,
  		0,a.iTaxRate,0,0,1,d.cCode,0,
  		0,0,0,1,c.cCode,
  		c.dDate,0,0,0,@irowno,0,@cBatch,a.iCost*convert(decimal, @iQuantity),a.iCost,@cFree1,@cFree2,@cFree3,@cFree4,@cFree5,@cFree6,@cFree7,@cFree8,@cFree9,@cFree10
  FROM dbo.PU_ArrivalVouchs a LEFT JOIN dbo.OM_MODetails b ON a.iPOsID=b.MODetailsID
  INNER JOIN dbo.OM_MOMain d ON b.MOID=d.MOID
  INNER JOIN dbo.PU_ArrivalVouch c ON a.ID=c.ID where a.Autoid='" + body.ODNDetailID + @"'
                        EXEC PROC_Update_OM_RdRecord01 @ID= @ID1
                        
                        ";
                              cmd.Parameters.Clear();
                              cmd.Parameters.Add(new SqlParameter("@AutoID", iChildId));
                              cmd.Parameters.Add(new SqlParameter("@ID1", iFatherId));
                              cmd.Parameters.Add(new SqlParameter("@cInvCode", body.InvCode));
                              cmd.Parameters.Add(new SqlParameter("@iQuantity", body.Quantity));
                              cmd.Parameters.Add(new SqlParameter("@iNum", body.Amount.ToString("0.00")));
                              cmd.Parameters.Add(new SqlParameter("@irowno", irowno));
                              cmd.Parameters.Add(new SqlParameter("@cBatch", body.BatchCode));
                              cmd.Parameters.Add(new SqlParameter("@cFree1", body.cFree1));
                              cmd.Parameters.Add(new SqlParameter("@cFree2", body.cFree2));
                              cmd.Parameters.Add(new SqlParameter("@cFree3", body.cFree3));
                              cmd.Parameters.Add(new SqlParameter("@cFree4", body.cFree4));
                              cmd.Parameters.Add(new SqlParameter("@cFree5", body.cFree5));
                              cmd.Parameters.Add(new SqlParameter("@cFree6", body.cFree6));
                              cmd.Parameters.Add(new SqlParameter("@cFree7", body.cFree7));
                              cmd.Parameters.Add(new SqlParameter("@cFree8", body.cFree8));
                              cmd.Parameters.Add(new SqlParameter("@cFree9", body.cFree9));
                              cmd.Parameters.Add(new SqlParameter("@cFree10", body.cFree10));
                              cmd.CommandText = sql;
                              try
                              {
                                  int count = cmd.ExecuteNonQuery();
                                  if (count <= 0)
                                  {
                                      log.Error("委外采购入库单表头失败，受影响行数<=0；");
                                      throw new Exception("委外采购入库单表头失败，受影响行数<=0；");
                                  }
                              }
                              catch (Exception ex)
                              {
                                  log.Error("委外采购入库单表头失败！SQL：\r\n" + sql, ex);
                                  throw new Exception("委外采购入库单表头失败！SQL：\r\n" + sql, ex);
                              }


                              //OM_MODetails
                              sql = @"UPDATE b SET freceivedqty=ISNULL(freceivedqty,0)+" + body.Quantity + @" 
                                FROM dbo.PU_ArrivalVouchs a LEFT JOIN dbo.OM_MODetails b ON a.iPOsID=b.MODetailsID
                                WHERE a.Autoid='" + body.ODNDetailID + "'";
                              DBHelper.CmdExecuteNonQuery(sql, cmd, "回写OM_MODetails失败！");
                              //回写PU_ArrivalVouchs失败
                              sql = "UPDATE dbo.PU_ArrivalVouchs SET fValidInQuan=ISNULL(fValidInQuan,0)+" + body.Quantity + " WHERE Autoid='" + body.ODNDetailID + "'";
                              DBHelper.CmdExecuteNonQuery(sql, cmd, "回写PU_ArrivalVouchs失败！");



                              sql = @"select isnull(fValidInQuan,0)fOutQuantity,isnull(iQuantity,0)iQuantity,ISNULL(iNum, 0)iNum,ISNULL(NiNum, 0)NiNum,ISNULL(iNum, 0)-ISNULL(NiNum, 0) SiNum from PU_ArrivalVouchs a
                        LEFT JOIN (select sum(iNum) NiNum,iArrsId from rdrecords01 GROUP BY iArrsId) b on a.Autoid=b.iArrsId
                        where  a.AutoID='" + body.ODNDetailID + "'";
                              DataTable dtDisCheck = DBHelper.SQlReturnData(sql, cmd);
                              if (dtDisCheck != null && dtDisCheck.Rows.Count > 0)
                              {
                                  foreach (DataRow item in dtDisCheck.Rows)
                                  {
                                      if (Convert.ToDecimal(item["fOutQuantity"].ToString()) == Convert.ToDecimal(item["iQuantity"].ToString()))
                                      {
                                          if (Convert.ToDecimal(item["iNum"].ToString()) != 0)
                                          {
                                              if (Convert.ToDecimal(item["SiNum"].ToString()) != 0)
                                              {
                                                  sql = @"Update rdrecords09 set iNum=isnull(iNum,0)+(" + item["SiNum"].ToString() + ") where autoid='" + iChildId + "' ";
                                                  body.Amount = (Convert.ToDecimal(body.Amount.ToString("0.00"))) + (Convert.ToDecimal(item["SiNum"].ToString()));
                                                  DBHelper.CmdExecuteNonQuery(sql, cmd, "辅计量余数处理失败！");
                                              }
                                          }
                                      }
                                  }

                              }



                              //更新待入库数量
                              sql = "UPDATE dbo.CurrentStock SET fInQuantity=ISNULL(fInQuantity,0)-" + body.Quantity + " WHERE cInvCode='" + body.InvCode + "' AND cWhCode ='" + head.WHCode + "'";
                              if (DBHelper.bInvBatch(body.InvCode, cmd) == true)
                              {
                                  if (!string.IsNullOrWhiteSpace(body.BatchCode))
                                  {
                                      sql += " and cBatch='" + body.BatchCode + "'";
                                  }

                              }
                              else
                              {
                                  sql += " and cBatch=''";
                              }
                              DBHelper.CmdExecuteNonQuery(sql, cmd, "更新待入库数量失败！");
                              //更新现存量
                              key.cBustypeUN = "委外加工";
                              key.cVouchTypeUN = "01";
                              key.TableName = "IA_ST_UnAccountVouch01";
                              //DBHelper.UpdateCurrentStock(cmd, body.InvCode, head.WHCode, "", body.Quantity, key);
                              //DBHelper.UpdateCurrentStockNEW(cmd, body.InvCode, head.WHCode, body.BatchCode, body.Quantity, body.cFree1, body.cFree2, body.cFree3, body.cFree4, body.cFree5, body.cFree6, body.cFree7, body.cFree8, body.cFree9, body.cFree10, key);
                              DBHelper.UpdateCurrentStockCCGC(cmd, body.InvCode, head.WHCode, body.BatchCode, body.Quantity, body.Amount, body.cFree1,
                                      body.cFree2, body.cFree3, body.cFree4, body.cFree5, body.cFree6, body.cFree7, body.cFree8, body.cFree9, body.cFree10,
                                      gys, key, "", "", "", "", 0, 0, 0, 0, 0, 0, 0, 0, iFatherId, iChildId);


                         

                              //                        #region 判断现存量是否足够
                              //                        sql = @"IF EXISTS(SELECT AutoID FROM dbo.CurrentStock WHERE iQuantity<0 OR iNum<0 OR fInQuantity<0)
                              //                        BEGIN
                              //	                        DECLARE @MSG NVARCHAR(100)
                              //	                        SELECT @MSG='ERP待入库数量不足！AutoID：'+CAST(AutoID AS NVARCHAR(100)) FROM dbo.CurrentStock WHERE iQuantity<0 OR iNum<0 OR fInQuantity<0
                              // 	                        RAISERROR(@MSG,16,1)
                              //                        END";

                              //                        cmd.CommandText = sql;
                              //                        cmd.ExecuteNonQuery();
                              //                        #endregion
                              iChildId--;
                          }

                          #endregion

                          //GetSerialCode(iFatherId.ToString());


                      }
                      cmd.Transaction.Commit();
                  }
                  catch (Exception ex)
                  {
                      cmd.Transaction.Rollback();
                      log.Error(ex.Message);
                      throw new Exception(ex.Message);
                  }
                  finally
                  {
                      if (conn.State == ConnectionState.Open)
                      {
                          conn.Close();
                      }
                      conn.Dispose();
                  }
              }
            return msg;
        }



        /// <summary>
        /// 创建委外倒冲
        /// </summary>
        /// <param name="Bills"></param>
        /// <returns></returns>
        public string CreateOutsourcingReceiveDocByPO(List<ICSOutsourcingReceiveDoc> Bills)
        {

            int moidinbatch = 0;
            int isif = 0;
            string msg = "";
            string connS = "";
            string mocode = "";
            string seq = "";
            int num = 0;
            string iFatherIdTree = "";
            int out11 = 0;
            string gys = "";
            int isEnable = 0;
            int irownnew = 0;
            int news = 0;
            int iseq = 0;
            string iFatherIdTwo = "";
            string sql = "";
            DataTable dt = null;
            
            
            DataSet ds = null;
            VouchKey key = new VouchKey();
            SqlConnection conn = new SqlConnection();
            SqlCommand cmd = new SqlCommand();
            if (Bills.Count <= 0)
            {
                throw new Exception("传送数据为空！");
            }
            LogInfo(Bills);


            foreach (ICSOutsourcingReceiveDoc head in Bills)
            {
                foreach (ICSOutsourcingReceiveDocs load in head.details)
                {
                    foreach (ICSOutsourcingReceiveDocss loads in load.detailss)
                    {
                        news++;
                    }
                }
            }

          

             List<string> result = Bills.Select(t => t.WorkPoint).Distinct().ToList();


             foreach (string WorkPoint in result)
             {
                 try
                 {
                     connS = string.Format(connString, WorkPoint);
                     conn = new System.Data.SqlClient.SqlConnection(connS);
                     conn.Open();
                     SqlTransaction sqlTran = conn.BeginTransaction();
                     cmd = new SqlCommand();
                     cmd.Transaction = sqlTran;
                     cmd.Connection = conn;
                     foreach (ICSOutsourcingReceiveDoc head in Bills)
                     {

                         moidinbatch = 0;
                         iFatherIdTwo = "";
                         iFatherIdTree = "";
                         if (WorkPoint != head.WorkPoint)
                             continue;
                         if (!DBHelper.IsInventoryConsolidation(cmd, head.WorkPoint))
                             throw new Exception("U8正在整理现存量，请稍后再试");

                         string[] ss = head.WorkPoint.Split('_');
                         ERPDB = ss[1];

                         ICSUserInfo userInfo = new ICSUserInfo();
                         userInfo = DBHelper.GetPersonInfo(head.User, cmd);

                         string[] dd = CRNALL.Split('~');
                         string crdname = dd[0];
                         string carname = dd[1];
                         string surface = dd[2];

                         string[] d = CRNA.Split('~');
                         string crdname1 = d[0];
                         string carname1 = d[1];
                         string surface1 = d[2];

                         string cardnewcode = DBHelper.GetCardNumber(carname, cmd);
                         string cardnewcode1 = DBHelper.GetCardNumber(carname1, cmd);
                         num = head.details.Count;
                         //取得out库单的默认显示模版
                         Dictionary<string, int> dic = DBHelper.GetAllCode("" + ERPDB + "", surface, "" + num + "", head.WorkPoint);
                         int iFatherId = Convert.ToInt32(dic["iFatherId"].ToString());
                         int iChildId = Convert.ToInt32(dic["iChildId"].ToString());
                         DateTime date = DateTime.Now;
                         string cRDcode = DBHelper.GetRDCode(crdname, cmd);
                         string iBaseCodeLen = DBHelper.GetAllRDCode(cardnewcode, "" + date + "", "admin", cRDcode, head.WorkPoint);

                         string DEF_ID = DBHelper.GetDefaultTemplate(cardnewcode, cmd);




                         #region 验证数量不能大于到货单数量

                         //foreach (ICSOutsourcingReceiveDocs body in head.details)
                         //{
                         //    sql = "SELECT isnull(-iQuantity,0) FROM dbo.PU_ArrivalVouchs WHERE Autoid='" + body.ODNDetailID + "'";
                         //    DataTable dtQty = DBHelper.SQlReturnData(sql, cmd);
                         //    if (dtQty != null && dtQty.Rows.Count > 0)
                         //    {
                         //        //到货单可入数量
                         //        decimal PUQty = Convert.ToDecimal(dtQty.Rows[0][0]);
                         //        sql = "SELECT ISNULL(SUM(-iQuantity),0) FROM dbo.RdRecords01 WHERE iArrsId='" + body.ODNDetailID + "'";
                         //        DataTable dtReceQty = DBHelper.SQlReturnData(sql, cmd);
                         //        decimal ReceQty = Convert.ToDecimal(dtReceQty.Rows[0][0]);
                         //        if (ReceQty + body.Quantity > PUQty)
                         //        {
                         //            throw new Exception("入库数量大于当前到货单行可入库数量！");
                         //        }
                         //    }
                         //    else
                         //    {
                         //        throw new Exception("U8不存在该到货单行！");
                         //    }
                         //}

                         #endregion

                         

                         string cfcode = string.Empty;
                         string zq = string.Empty;
                         string lz = string.Empty;
                         string dz = string.Empty;

                         sql = @"select  a.cCode,a.iLZYJ,a.iLZFS,iPLNum,dblZQNum,c.dDate,
                        (case when ilzfs=1 then dateadd(day,-day(getdate()),dateadd(month,1,getdate())) else DATEADD(day,isnull(iPLNum,0),c.dDate) end)
                         lz,DATEADD(day,isnull(dblZQNum,0),DATEADD(day,isnull(iPLNum,0),c.dDate)) dz 
                        from 
                        (select b.cVenPUOMProtocol,a.cvencode,a.dDate,a.cCode from PU_ArrivalVouch a
                        left join Vendor b on a.cvencode=b.cvencode
                        where  a.cCode='" + head.ODNCode + @"'
                        ) c 
                        left join AA_Agreement a on a.cCode=c.cVenPUOMProtocol

                        ";
                         DataTable dtDisCheck1 = DBHelper.SQlReturnData(sql, cmd);
                         if (dtDisCheck1 != null && dtDisCheck1.Rows.Count > 0)
                         {
                             foreach (DataRow item in dtDisCheck1.Rows)
                             {
                                 if (item["iLZYJ"].ToString() == "10" & (item["iLZFS"].ToString() == "0" | item["iLZFS"].ToString() == "1"))
                                 {
                                     cfcode = item["cCode"].ToString();
                                     zq = item["dblZQNum"].ToString();
                                     lz = item["lz"].ToString();
                                     dz = item["dz"].ToString();
                                 }
                                 else
                                 {
                                     if (item["cCode"].ToString() == "")
                                         cfcode = item["cCode"].ToString();
                                 }
                             }
                         }

                         #region 委外采购入库单 表头

                         sql = @"INSERT INTO dbo.RdRecord01
                        ( ID ,bRdFlag ,cVouchType ,cBusType ,cSource ,cWhCode ,
                          dDate ,cCode ,cRdCode ,cDepCode ,cPersonCode ,cPTCode ,
                          cVenCode ,cOrderCode ,cARVCode ,cHandler ,cMemo ,
                          bTransFlag ,cMaker ,cDefine1 ,cDefine2 ,cDefine3 ,
                          cDefine4 ,cDefine5 ,cDefine6 ,cDefine7 ,cDefine8 ,
                          cDefine9 ,cDefine10 ,dVeriDate ,bpufirst ,biafirst ,
                		  dARVDate ,VT_ID ,bIsSTQc ,cDefine11 ,cDefine12 ,
                          cDefine13 ,cDefine14 ,cDefine15 ,cDefine16 ,
                          ipurarriveid ,iTaxRate ,iExchRate ,cExch_Name ,bOMFirst,
                          bFromPreYear ,bIsComplement ,iDiscountTaxType ,
                          ireturncount ,iverifystate ,iswfcontrolled ,
                          cModifyPerson ,dnmaketime ,dnverifytime ,
                          bredvouch  ,bCredit ,iPrintCount,cVenPUOMProtocol,iCreditPeriod,dCreditStart,dGatheringDate)
                SELECT @ID,1,'01','委外加工','委外到货单',@cWhCode,
                		CONVERT(NVARCHAR(15),GETDATE(),23),@cCode,@RdCode,a.cDepCode,a.cPersonCode,a.cPTCode,
                		a.cVenCode,'',a.cCode,@cHandler,a.cMemo,
                		0,@cMaker,a.cDefine1,a.cDefine2,a.cDefine3,
                		a.cDefine4,a.cDefine5,a.cDefine6,a.cDefine7,a.cDefine8,
                		a.cDefine9,a.cDefine10,CONVERT(NVARCHAR(15),GETDATE(),23),0,0,
                		a.dDate,@VT_ID,0,a.cDefine11,a.cDefine12,
                		a.cDefine13,a.cDefine14,a.cDefine15,a.cDefine16,
                		'',a.iTaxRate,a.nflat,a.cexch_name,0,
                		0,0,0,
                		0,0,0,
                		'',GETDATE(),GETDATE(),
                		0,0,0,@cVenPUOMProtocol,@iCreditPeriod,@dCreditStart,@dGatheringDate
                 FROM dbo.OM_MOMain a   WHERE a.cCode='" + head.OOCode + "'";
                         cmd.Parameters.Clear();
                         cmd.Parameters.Add(new SqlParameter("@ID", iFatherId));
                         cmd.Parameters.Add(new SqlParameter("@cWhCode", head.WHCode));
                         cmd.Parameters.Add(new SqlParameter("@cCode", iBaseCodeLen));

                         cmd.Parameters.Add(new SqlParameter("@cHandler", userInfo.UserName));
                         cmd.Parameters.Add(new SqlParameter("@cMaker", userInfo.UserName));
                         cmd.Parameters.Add(new SqlParameter("@VT_ID", DEF_ID));
                         cmd.Parameters.Add(new SqlParameter("@RdCode", cRDcode));
                         cmd.Parameters.Add(new SqlParameter("@cVenPUOMProtocol", cfcode));
                         cmd.Parameters.Add(new SqlParameter("@iCreditPeriod", zq));
                         cmd.Parameters.Add(new SqlParameter("@dCreditStart", lz));
                         cmd.Parameters.Add(new SqlParameter("@dGatheringDate", dz));
                         cmd.CommandText = sql;
                         try
                         {
                             int count = cmd.ExecuteNonQuery();
                             if (count <= 0)
                             {
                                 log.Error("委外采购入库单表头失败，受影响行数<=0；");
                                 throw new Exception("委外采购入库单表头失败，受影响行数<=0；");
                             }
                         }
                         catch (Exception ex)
                         {
                             log.Error("委外采购入库单表头失败！SQL：\r\n" + sql, ex);
                             throw new Exception("委外采购入库单表头失败！SQL：\r\n" + sql, ex);
                         }

                         List<WH> LWH = new List<WH>();
                         #endregion

                         if (news != 0)
                         {
                             foreach (ICSOutsourcingReceiveDocs load in head.details)
                             {
                                 foreach (ICSOutsourcingReceiveDocss loads in load.detailss)
                                 {
                                     WH wh = new WH();
                                     wh.WHCODE = loads.WHCode;
                                     wh.seq = 0;
                                     if (LWH.Count != 0)
                                     {
                                         int whnumber = 0;
                                         foreach (WH item in LWH)
                                         {
                                             if (item.WHCODE == wh.WHCODE)
                                                 whnumber++;
                                         }
                                         if (whnumber == 0)
                                             LWH.Add(wh);
                                     }
                                     else
                                         LWH.Add(wh);

                                 }
                             }

                             foreach (WH item in LWH)
                             {
                                 Dictionary<string, int> dic1 = DBHelper.GetAllCode("" + ERPDB + "", surface1, "1", head.WorkPoint);
                                 int iFatherId1 = Convert.ToInt32(dic1["iFatherId"].ToString());
                                 out11 = iFatherId1;
                                 int iChildId1 = Convert.ToInt32(dic1["iChildId"].ToString());
                                 DateTime date1 = DateTime.Now;
                                 string cRDcode1 = DBHelper.GetRDCode(crdname1, cmd);
                                 string iBaseCodeLen1 = DBHelper.GetAllRDCode(cardnewcode1, "" + date1 + "", "admin", cRDcode1, head.WorkPoint);
                                 string DEF_ID1 = DBHelper.GetDefaultTemplate(cardnewcode1, cmd);
                                 #region 材料出库单表头
                                 sql = @"
                            INSERT INTO dbo.rdrecord11
                            ( ID ,bRdFlag ,cVouchType ,cBusType ,cSource ,cBusCode,
                            cWhCode ,dDate ,cCode ,cRdCode ,cDepCode,
                            cPersonCode,cHandler ,bTransFlag ,cMaker ,dVeriDate ,
                            bpufirst ,biafirst ,VT_ID ,bIsSTQc ,cPsPcode ,
                            cMPoCode ,iproorderid ,bFromPreYear ,bIsLsQuery ,bIsComplement ,
                            iDiscountTaxType ,ireturncount ,iverifystate ,iswfcontrolled ,dnmaketime ,
                            dnverifytime ,bredvouch ,iPrintCount,cVenCode,iMQuantity)

                            SELECT distinct @ID,0,'11','委外倒冲','采购入库单',@cBusCode,
                            @cWhCode,CONVERT(NVARCHAR(15),GETDATE(),23),@cCode,@cRdCode,c.cDepCode,
                            c.cPersonCode,@cHandler,0,@cMaker,CONVERT(NVARCHAR(15),GETDATE(),23),
                            0,0,@VT_ID,0,null,
                            c.cCode,a.MoId,0,0,0,
                            0,0,0,0,GETDATE(),
                            GETDATE(),@bredvouch,0,c.cVenCode,null
                            from dbo.OM_MOMaterials a
                            INNER JOIN dbo.OM_MODetails b ON a.MoDetailsID = b.MoDetailsID 
                            LEFT JOIN dbo.OM_MOMain c ON b.MOID = c.MOID               
                            WHERE c.cCode='" + head.OOCode + "'";
                                 cmd.Parameters.Clear();
                                 cmd.Parameters.Add(new SqlParameter("@ID", out11));
                                 cmd.Parameters.Add(new SqlParameter("@cBusCode", iBaseCodeLen));
                                 cmd.Parameters.Add(new SqlParameter("@cWhCode", head.WHCode));
                                 cmd.Parameters.Add(new SqlParameter("@cCode", iBaseCodeLen1));
                                 cmd.Parameters.Add(new SqlParameter("@cHandler", userInfo.UserName));
                                 cmd.Parameters.Add(new SqlParameter("@cMaker", userInfo.UserName));
                                 cmd.Parameters.Add(new SqlParameter("@VT_ID", DEF_ID1));
                                 cmd.Parameters.Add(new SqlParameter("@bredvouch", "0"));
                                 cmd.Parameters.Add(new SqlParameter("@cRdCode", cRDcode1));
                                 cmd.CommandText = sql;
                                 try
                                 {
                                     int count = cmd.ExecuteNonQuery();
                                     if (count <= 0)
                                     {
                                         log.Error("生成成品入库单表体失败，受影响行数<=0；");
                                         throw new Exception("生成成品入库单表体失败，受影响行数<=0；");
                                     }
                                 }
                                 catch (Exception ex)
                                 {
                                     log.Error("生成成品入库单表体失败！SQL：\r\n" + sql, ex);
                                     throw new Exception("生成成品入库单表体失败！SQL：\r\n" + sql, ex);
                                 }

                                 item.ID = iFatherId1;

                                 #endregion
                             }
                         }


                         #region 采购入库单 表体
                         foreach (ICSOutsourcingReceiveDocs body in head.details)
                         {

                             sql = @"SELECT b.cCode,a.iVouchRowNo  FROM dbo.OM_MODetails a INNER JOIN
                                dbo.OM_MOMain b ON b.MoId = a.MoId WHERE a.MODetailsID = '" + body.OODetailID + "'";
                             DataTable dtCheck = DBHelper.SQlReturnData(sql, cmd);
                             if (dtCheck != null && dtCheck.Rows.Count > 0)
                             {
                                 mocode = dtCheck.Rows[0]["cCode"].ToString();
                                 seq = dtCheck.Rows[0]["iVouchRowNo"].ToString();


                                 sql = @"   select
                            count(c.MOMaterialsID) num,d.number
                            from DBO.OM_MOMain a 
                            left join DBO.OM_MODetails b on a.moid = b.moid
                            left join DBO.OM_MOMaterials c on b.MODetailsID = c.MODetailsID
                            left join(select MODetailsID,COUNT(MOMaterialsID) number from  DBO.OM_MOMaterials where iWIPtype=1 GROUP BY MODetailsID
                            ) d on b.MODetailsID = d.MODetailsID
                            where a.cCode='{0}' and b.iVouchRowNo='{1}'
                            GROUP BY d.number ";
                                 sql = string.Format(sql, mocode, seq);
                                 dtCheck = DBHelper.SQlReturnData(sql, cmd);
                                 if (dtCheck != null && dtCheck.Rows.Count > 0)
                                 {
                                     if (dtCheck.Rows[0]["num"].ToString() != dtCheck.Rows[0]["number"].ToString())
                                     {
                                         isif = 1;
                                     }
                                     else
                                     {
                                         isif = 0;
                                     }
                                 }



                             }


                             if (DBHelper.bInvBatch(body.InvCode, cmd) == true)
                             {
                                 if (body.BatchCode == "")
                                 {
                                     log.Error("该物料：" + body.InvCode + "已启用批次，请重新填写！");
                                     throw new Exception("该物料：" + body.InvCode + "已启用批次，请重新填写！");
                                 }
                             }
                             else
                             {
                                 if (body.BatchCode != "")
                                 {
                                     log.Error("该物料：" + body.InvCode + "未启用批次，请重新填写！");
                                     throw new Exception("该物料：" + body.InvCode + "未启用批次，请重新填写！");
                                 }
                             }
                             moidinbatch = iChildId;
                             if (head.ODNCode != "")
                             {
                                 sql = @"INSERT INTO dbo.rdrecords01
          ( AutoID ,ID ,cInvCode ,iQuantity ,iUnitCost ,iPrice ,iAPrice ,iNum,
            iFlag ,iSQuantity ,iSNum ,iMoney ,
            cDefine22 ,cDefine23 ,cDefine24 ,cDefine25 ,cDefine26 ,cDefine27 ,
            cItem_class ,cItemCode ,iOMoDID ,fACost ,cName  ,iNQuantity ,
            chVencode ,iArrsId ,iOriTaxCost ,iOriCost ,iOriMoney ,iOriTaxPrice ,
            ioriSum ,iTaxRate ,iTaxPrice ,iSum ,bTaxCost ,cPOID ,iMatSettleState ,
            iBillSettleCount ,bLPUseFree ,iOriTrackID ,bCosting ,cbarvcode ,
            iExpiratDateCalcu ,iordertype ,isotype ,irowno ,bgift,cBatch,iProcessFee, iProcessCost,
            cFree1,cFree2,cFree3,cFree4,cFree5,cFree6,cFree7,cFree8,cFree9,cFree10 )
  SELECT @AutoID,@ID1,@cInvCode,@iQuantity,0,0,0,@iNum,
  		0,0,0,0,
  		a.cDefine22,a.cDefine23,a.cDefine24,a.cDefine25,a.cDefine26,a.cDefine27,
  		a.cItem_class,a.cItemCode,a.MODetailsID,0,a.cItemName,a.iQuantity,
  		@VenCode,(select autoid from PU_ArrivalVouchs a left join PU_ArrivalVouch b on a.ID=b.ID where b.cCode='" + head.ODNCode + @"' and a.ivouchrowno='" + body.ODNSequence + @"'),0,0,0,0,
  		0,d.nflat,0,0,1,'',0,
  		0,0,0,1,'',
  		0,0,0,@irowno,0,@cBatch,a.iNatUnitPrice *convert(decimal, @iQuantity),a.iNatUnitPrice ,
        @cFree1,@cFree2,@cFree3,@cFree4,@cFree5,@cFree6,@cFree7,@cFree8,@cFree9,@cFree10
  FROM dbo.OM_MODetails a
  INNER JOIN dbo.OM_MOMain d ON a.MOID=d.MOID
   where a.MODetailsID='" + body.OODetailID + @"'
";
                             }
                             else
                             {
                                 sql = @"INSERT INTO dbo.rdrecords01
          ( AutoID ,ID ,cInvCode ,iQuantity ,iUnitCost ,iPrice ,iAPrice ,iNum,
            iFlag ,iSQuantity ,iSNum ,iMoney ,
            cDefine22 ,cDefine23 ,cDefine24 ,cDefine25 ,cDefine26 ,cDefine27 ,
            cItem_class ,cItemCode ,iOMoDID ,fACost ,cName  ,iNQuantity ,
            chVencode ,iArrsId ,iOriTaxCost ,iOriCost ,iOriMoney ,iOriTaxPrice ,
            ioriSum ,iTaxRate ,iTaxPrice ,iSum ,bTaxCost ,cPOID ,iMatSettleState ,
            iBillSettleCount ,bLPUseFree ,iOriTrackID ,bCosting ,cbarvcode ,
            iExpiratDateCalcu ,iordertype ,isotype ,irowno ,bgift,cBatch,iProcessFee, iProcessCost,
            cFree1,cFree2,cFree3,cFree4,cFree5,cFree6,cFree7,cFree8,cFree9,cFree10 )
  SELECT @AutoID,@ID1,@cInvCode,@iQuantity,0,0,0,@iNum,
  		0,0,0,0,
  		a.cDefine22,a.cDefine23,a.cDefine24,a.cDefine25,a.cDefine26,a.cDefine27,
  		a.cItem_class,@cItemCode,a.MODetailsID,0,a.cItemName,a.iQuantity,
  		@VenCode,'',0,0,0,0,
  		0,d.nflat,0,0,1,'',0,
  		0,0,0,1,'',
  		0,0,0,@irowno,0,@cBatch,a.iNatUnitPrice *convert(decimal, @iQuantity),a.iNatUnitPrice ,
        @cFree1,@cFree2,@cFree3,@cFree4,@cFree5,@cFree6,@cFree7,@cFree8,@cFree9,@cFree10
  FROM dbo.OM_MODetails a
  INNER JOIN dbo.OM_MOMain d ON a.MOID=d.MOID
   where a.MODetailsID='" + body.OODetailID + @"'
";
                             }



                             cmd.Parameters.Clear();
                             cmd.Parameters.Add(new SqlParameter("@AutoID", iChildId));
                             cmd.Parameters.Add(new SqlParameter("@ID1", iFatherId));
                             cmd.Parameters.Add(new SqlParameter("@cInvCode", body.InvCode));
                             cmd.Parameters.Add(new SqlParameter("@iQuantity", body.Quantity));
                             cmd.Parameters.Add(new SqlParameter("@iNum", body.Amount.ToString("0.00")));
                             cmd.Parameters.Add(new SqlParameter("@irowno", body.Sequence));
                             cmd.Parameters.Add(new SqlParameter("@cBatch", body.BatchCode));
                             cmd.Parameters.Add(new SqlParameter("@cItemCode", body.ProjectCode));
                             cmd.Parameters.Add(new SqlParameter("@VenCode", head.VenCode));
                             cmd.Parameters.Add(new SqlParameter("@cFree1", body.cFree1));
                             cmd.Parameters.Add(new SqlParameter("@cFree2", body.cFree2));
                             cmd.Parameters.Add(new SqlParameter("@cFree3", body.cFree3));
                             cmd.Parameters.Add(new SqlParameter("@cFree4", body.cFree4));
                             cmd.Parameters.Add(new SqlParameter("@cFree5", body.cFree5));
                             cmd.Parameters.Add(new SqlParameter("@cFree6", body.cFree6));
                             cmd.Parameters.Add(new SqlParameter("@cFree7", body.cFree7));
                             cmd.Parameters.Add(new SqlParameter("@cFree8", body.cFree8));
                             cmd.Parameters.Add(new SqlParameter("@cFree9", body.cFree9));
                             cmd.Parameters.Add(new SqlParameter("@cFree10", body.cFree10));
                             cmd.CommandText = sql;
                             try
                             {
                                 int count = cmd.ExecuteNonQuery();
                                 if (count <= 0)
                                 {
                                     log.Error("委外采购入库单表体失败，受影响行数<=0；");
                                     throw new Exception("委外采购入库单表体失败，受影响行数<=0；");
                                 }
                             }
                             catch (Exception ex)
                             {
                                 log.Error("委外采购入库单表体失败！SQL：\r\n" + sql, ex);
                                 throw new Exception("委外采购入库单表体失败！SQL：\r\n" + sql, ex);
                             }
                             if (isif == 1)
                             {
                                 if (head.CompleteVerification)
                                 {
                                     #region 齐套检验
                                     sql = @" select cValue from AccInformation where cName=N'iMOProInCtrlBySet' ";
                                     DataTable qt = DBHelper.SQlReturnData(sql, cmd);
                                     if (qt != null && qt.Rows.Count > 0)
                                     {
                                         if (qt.Rows[0]["cValue"].ToString() == "1")
                                         {

                                             sql = @"select cCode,iVouchRowNo,MIN(lotqty) min_lotqty from 
                                    (
                                    select
                                    a.cCode,b.iVouchRowNo,
                                    c.MOMaterialsID  as AllocateId,sum(c.iQuantity) as Qty,SUM(c.iSendQTY) IssQty,SUM(c.iSendQTY)/(SUM(c.iQuantity)/b.iQuantity) lotqty
                                    from DBO.OM_MOMain a 
                                    left join DBO.OM_MODetails b on a.moid = b.moid
                                    left join DBO.OM_MOMaterials c on b.MODetailsID = c.MODetailsID
                             
                                    left join DBO.Inventory_Sub f on c.cInvCode=f.cInvSubCode
                                    where a.cCode='{0}' and b.iVouchRowNo='{1}'
                                    and  c.iQuantity<>0  and c.iWIPtype<>1
                                    group by a.cCode,b.iVouchRowNo,c.MOMaterialsID,b.iQuantity 
                                    ) tt
                                    group by cCode,iVouchRowNo";
                                             sql = string.Format(sql, mocode, seq);
                                             qt = DBHelper.SQlReturnData(sql, cmd);

                                             if (qt == null || qt.Rows.Count == 0)
                                             {
                                                 throw new Exception("该物料：" + body.InvCode + @"在订单内未领料！");
                                             }
                                             else
                                             {
                                                 if (Convert.ToDecimal(qt.Rows[0]["min_lotqty"]) <= 0)
                                                 {
                                                     throw new Exception("该物料：" + body.InvCode + @"在订单内未领料<=0！");
                                                 }

                                             }


                                         }
                                         else if (qt.Rows[0]["cValue"].ToString() == "2")
                                         {
                                             sql = @"select cValue from AccInformation where cName=N'bControlKeyMaterial'";
                                             qt = DBHelper.SQlReturnData(sql, cmd);
                                             if (qt != null && qt.Rows.Count > 0)
                                             {
                                                 if (qt.Rows[0]["cValue"].ToString() == "true")
                                                 {
                                                     sql = @"select cCode,iVouchRowNo,MIN(lotqty) min_lotqty from 
                                    (
                                    select
                                    a.cCode,b.iVouchRowNo,
                                    c.MOMaterialsID  as AllocateId,sum(c.iQuantity) as Qty,SUM(c.iSendQTY) IssQty,SUM(c.iSendQTY)/(SUM(c.iQuantity)/b.iQuantity) lotqty
                                    from DBO.OM_MOMain a 
                                    left join DBO.OM_MODetails b on a.moid = b.moid
                                    left join DBO.OM_MOMaterials c on b.MODetailsID = c.MODetailsID
                             
                                    left join DBO.Inventory_Sub f on c.cInvCode=f.cInvSubCode
                                    where a.cCode='{0}' and b.iVouchRowNo='{1}'
                                    and  c.iQuantity<>0 and c.iWIPtype<>1
                                    group by a.cCode,b.iVouchRowNo,c.MOMaterialsID,b.iQuantity 
                                    ) tt
                                    group by cCode,iVouchRowNo";
                                                     sql = string.Format(sql, mocode, seq);
                                                     qt = DBHelper.SQlReturnData(sql, cmd);

                                                     if (qt == null || qt.Rows.Count == 0)
                                                     {
                                                         throw new Exception("该物料：" + body.InvCode + @"在订单内未有可用领料！");
                                                     }
                                                     else
                                                     {
                                                         if (Convert.ToDecimal(qt.Rows[0]["min_lotqty"]) <= 0)
                                                         {
                                                             throw new Exception("该物料：" + body.InvCode + @"在订单内未领料！");
                                                         }
                                                         else if (Convert.ToDecimal(qt.Rows[0]["min_lotqty"]) - body.Quantity <= 0)
                                                         {
                                                             throw new Exception("该物料：" + body.InvCode + @"入库数量大于关键物料领料套数！");
                                                         }
                                                     }


                                                 }
                                                 else
                                                 {

                                                     sql = @"select cCode,iVouchRowNo,MIN(lotqty) min_lotqty from 
                                    (
                                    select
                                    a.cCode,b.iVouchRowNo,
                                    c.MOMaterialsID  as AllocateId,sum(c.iQuantity) as Qty,SUM(c.iSendQTY) IssQty,SUM(c.iSendQTY)/(SUM(c.iQuantity)/b.iQuantity) lotqty
                                    from DBO.OM_MOMain a 
                                    left join DBO.OM_MODetails b on a.moid = b.moid
                                    left join DBO.OM_MOMaterials c on b.MODetailsID = c.MODetailsID
                             
                                    left join DBO.Inventory_Sub f on c.cInvCode=f.cInvSubCode
                                    where a.cCode='{0}' and b.iVouchRowNo='{1}'
                                    and  c.iQuantity<>0 and c.iWIPtype<>1
                                    group by a.cCode,b.iVouchRowNo,c.MOMaterialsID,b.iQuantity 
                                    ) tt
                                    group by cCode,iVouchRowNo";
                                                     sql = string.Format(sql, mocode, seq);
                                                     qt = DBHelper.SQlReturnData(sql, cmd);

                                                     if (qt == null || qt.Rows.Count == 0)
                                                     {
                                                         throw new Exception("该物料：" + body.InvCode + @"在订单内未有可用领料！");
                                                     }
                                                     else
                                                     {
                                                         if (Convert.ToDecimal(qt.Rows[0]["min_lotqty"]) <= 0)
                                                         {
                                                             throw new Exception("该物料：" + body.InvCode + @"在订单内未领料！");
                                                         }
                                                         else if (Convert.ToDecimal(qt.Rows[0]["min_lotqty"]) - body.Quantity <= 0)
                                                         {
                                                             throw new Exception("该物料：" + body.InvCode + @"入库数量大于领料套数！");
                                                         }
                                                     }

                                                 }
                                             }

                                         }
                                     }

                                     #endregion
                                 }
                             }

                             //OM_MODetails
                             sql = @"UPDATE a SET freceivedqty=ISNULL(freceivedqty,0)+" + body.Quantity + @" 
                                FROM  dbo.OM_MODetails a 
                                WHERE a.MODetailsID='" + body.OODetailID + "'";
                             DBHelper.CmdExecuteNonQuery(sql, cmd, "回写OM_MODetails失败！");

                            

                             sql = @"select isnull(freceivedqty,0)fOutQuantity,isnull(iQuantity,0)iQuantity,ISNULL(iNum, 0)iNum,ISNULL(NiNum, 0)NiNum,ISNULL(iNum, 0)-ISNULL(NiNum, 0) SiNum from OM_MODetails a
                        LEFT JOIN (select sum(iNum) NiNum,iOMoDID from rdrecords01 GROUP BY iOMoDID) b on a.MODetailsID=b.iOMoDID
                        where  a.MODetailsID='" + body.OODetailID + "'";
                             DataTable dtDisCheck = DBHelper.SQlReturnData(sql, cmd);
                             if (dtDisCheck != null && dtDisCheck.Rows.Count > 0)
                             {
                                 foreach (DataRow item in dtDisCheck.Rows)
                                 {
                                     if (Convert.ToDecimal(item["fOutQuantity"].ToString()) == Convert.ToDecimal(item["iQuantity"].ToString()))
                                     {
                                         if (Convert.ToDecimal(item["iNum"].ToString()) != 0)
                                         {
                                             if (Convert.ToDecimal(item["SiNum"].ToString()) != 0)
                                             {
                                                 sql = @"Update rdrecords01 set iNum=isnull(iNum,0)+(" + item["SiNum"].ToString() + ") where autoid='" + iChildId + "' ";
                                                 body.Amount = (Convert.ToDecimal(body.Amount.ToString("0.00"))) + (Convert.ToDecimal(item["SiNum"].ToString()));
                                                 DBHelper.CmdExecuteNonQuery(sql, cmd, "辅计量余数处理失败！");
                                             }
                                         }
                                     }
                                 }

                             }

                             //更新待入库数量
                             sql = "UPDATE dbo.CurrentStock SET fInQuantity=ISNULL(fInQuantity,0)-" + body.Quantity + " WHERE cInvCode='" + body.InvCode + "' AND cWhCode ='" + head.WHCode + "'";
                             if (DBHelper.bInvBatch(body.InvCode, cmd) == true)
                             {
                                 if (!string.IsNullOrWhiteSpace(body.BatchCode))
                                 {
                                     sql += " and cBatch='" + body.BatchCode + "'";
                                 }

                             }
                             else
                             {
                                 sql += " and cBatch=''";
                             }
                             DBHelper.CmdExecuteNonQuery(sql, cmd, "更新待入库数量失败！");

                             //if (isEnable != 0)
                             //{
                             //    key.cBustypeUN = "委外加工";
                             //    key.cVouchTypeUN = "01";
                             //    key.TableName = "IA_ST_UnAccountVouch01";
                             //    //DBHelper.UpdateCurrentStock(cmd, body.InvCode, head.WHCode, "", body.Quantity, key);
                             //    DBHelper.UpdateCurrentStockNEWDG(cmd, body.InvCode, head.WHCode, body.BatchCode, body.Quantity, body.cFree1, body.cFree2, body.cFree3, body.cFree4, body.cFree5, body.cFree6, body.cFree7, body.cFree8, body.cFree9, body.cFree10, gys, key);
                             //}
                             //else
                             //{
                             //    //更新现存量
                             //    key.cBustypeUN = "委外加工";
                             //    key.cVouchTypeUN = "01";
                             //    key.TableName = "IA_ST_UnAccountVouch01";
                             //    //DBHelper.UpdateCurrentStock(cmd, body.InvCode, head.WHCode, "", body.Quantity, key);
                             //    DBHelper.UpdateCurrentStockNEW(cmd, body.InvCode, head.WHCode, body.BatchCode, body.Quantity, body.cFree1, body.cFree2, body.cFree3, body.cFree4, body.cFree5, body.cFree6, body.cFree7, body.cFree8, body.cFree9, body.cFree10, key);
                             //}
                             key.cBustypeUN = "委外加工";
                             key.cVouchTypeUN = "01";
                             key.TableName = "IA_ST_UnAccountVouch01";
                             DBHelper.UpdateCurrentStockCCGC(cmd, body.InvCode, head.WHCode, body.BatchCode, body.Quantity, body.Amount, body.cFree1,
                                      body.cFree2, body.cFree3, body.cFree4, body.cFree5, body.cFree6, body.cFree7, body.cFree8, body.cFree9, body.cFree10,
                                      "", key, "", "", "", "", 0, 0, 0, 0, 0, 0, 0, 0, iFatherId, iChildId);
                          

                             num = body.detailss.Count();
                             Dictionary<string, int> dic2 = DBHelper.GetAllCode("" + ERPDB + "", surface1, "" + num + "", head.WorkPoint);
                             int iFatherId2 = Convert.ToInt32(dic2["iFatherId"].ToString());
                             int iChildId2 = Convert.ToInt32(dic2["iChildId"].ToString());

                             irownnew = 0;
                             foreach (ICSOutsourcingReceiveDocss item in body.detailss)
                             {
                                 if (item.PickID != null && item.PickID != "")
                                 {
                                     foreach (WH wh in LWH)
                                     {
                                         if (wh.WHCODE == item.WHCode)
                                         {
                                             out11 = wh.ID;
                                             iseq = wh.seq + 1;
                                             wh.seq += 1;
                                         }
                                     }
                                     irownnew++;
                                     sql = @"INSERT INTO dbo.rdrecords11
                                ( AutoID,ID ,cInvCode,iQuantity ,cBatch ,iNum,
                                iFlag ,cItemCode ,cName ,iNQuantity ,iMPoIds ,
                                bLPUseFree ,iOriTrackID ,bCosting ,bVMIUsed ,cmocode ,
                                invcode ,imoseq ,iopseq ,iExpiratDateCalcu ,iorderdid ,
                                iordertype ,isotype ,ipesodid ,ipesotype ,cpesocode ,
                                ipesoseq ,irowno ,bcanreplace ,iposflag,iOMoDID,
                                iOMoMID,comcode,cFree1,cFree2,cFree3,cFree4,cFree5,cFree6,cFree7,cFree8,cFree9,cFree10)

                                SELECT distinct
                                @AutoID,@ID,a.cInvCode,@iQuantity,@cBatch,@iNum,
                                0,@cItemCode,null,@iQuantity,NULL,
                                0,0,1,0,NULL,
                                b.cInvCode,NULL,NULL,0,0,
                                0,0,a.MOMaterialsID,'6',c.cCode,
                                NULL, @irowno,0,NULL,b.MODetailsID,
                                a.MOMaterialsID,c.cCode,@cFree1,@cFree2,@cFree3,@cFree4,@cFree5,@cFree6,@cFree7,@cFree8,@cFree9,@cFree10
                                FROM dbo.OM_MOMaterials a
                                INNER JOIN dbo.OM_MODetails b ON a.MoDetailsID=b.MODetailsID
                                INNER JOIN dbo.OM_MOMain c ON b.MOID=c.MOID
                                WHERE a.MOMaterialsID='" + item.PickID + "'";
                                     cmd.Parameters.Clear();

                                     cmd.Parameters.Add(new SqlParameter("@AutoID", iChildId2));
                                
                                     cmd.Parameters.Add(new SqlParameter("@ID", out11));
                                     cmd.Parameters.Add(new SqlParameter("@iQuantity", item.Quantity));
                                     cmd.Parameters.Add(new SqlParameter("@iNum", item.Amount));
                                     cmd.Parameters.Add(new SqlParameter("@cBatch", item.BatchCode));
                                     cmd.Parameters.Add(new SqlParameter("@cItemCode", item.ProjectCode));
                                     cmd.Parameters.Add(new SqlParameter("@irowno", iseq));
                                     cmd.Parameters.Add(new SqlParameter("@cFree1", item.cFree1));
                                     cmd.Parameters.Add(new SqlParameter("@cFree2", item.cFree2));
                                     cmd.Parameters.Add(new SqlParameter("@cFree3", item.cFree3));
                                     cmd.Parameters.Add(new SqlParameter("@cFree4", item.cFree4));
                                     cmd.Parameters.Add(new SqlParameter("@cFree5", item.cFree5));
                                     cmd.Parameters.Add(new SqlParameter("@cFree6", item.cFree6));
                                     cmd.Parameters.Add(new SqlParameter("@cFree7", item.cFree7));
                                     cmd.Parameters.Add(new SqlParameter("@cFree8", item.cFree8));
                                     cmd.Parameters.Add(new SqlParameter("@cFree9", item.cFree9));
                                     cmd.Parameters.Add(new SqlParameter("@cFree10", item.cFree10));

                                     cmd.CommandText = sql;
                                     try
                                     {
                                         int count = cmd.ExecuteNonQuery();
                                         if (count <= 0)
                                         {
                                             log.Error("生成材料出库单表体失败，受影响行数<=0；");
                                             throw new Exception("生成材料出库单表体失败，受影响行数<=0；");
                                         }
                                     }
                                     catch (Exception ex)
                                     {
                                         log.Error("生成材料出库单表体失败" + sql, ex);
                                         throw new Exception("生成材料出库单表体失败 " + sql, ex);
                                     }

                                     #region 回写委外子件已领数量
                                     sql = "update OM_MOMaterials set iSendQTY=isnull(iSendQTY,0)+" + item.Quantity + ",fsendapplyqty=isnull(fsendapplyqty,0)+(" + item.Quantity + ") where MOMaterialsID='" + item.PickID + "' ";
                                     DBHelper.CmdExecuteNonQuery(sql, cmd, "回写委外子件已领数量失败！");
                                     #endregion


                                     sql = @"select isnull(iSendQTY,0)fOutQuantity,isnull(iQuantity,0)iQuantity,ISNULL(iNum, 0)iNum,ISNULL(NiNum, 0)NiNum,ISNULL(iNum, 0)-ISNULL(NiNum, 0) SiNum from OM_MOMaterials a
                        LEFT JOIN (select sum(iNum) NiNum,ipesodid from rdrecords11 GROUP BY ipesodid) b on a.MOMaterialsID=b.ipesodid
                        where  a.MOMaterialsID='" + item.PickID + "'";
                                     dtDisCheck = DBHelper.SQlReturnData(sql, cmd);
                                     if (dtDisCheck != null && dtDisCheck.Rows.Count > 0)
                                     {
                                         foreach (DataRow item1 in dtDisCheck.Rows)
                                         {
                                             if (Convert.ToDecimal(item1["fOutQuantity"].ToString()) == Convert.ToDecimal(item1["iQuantity"].ToString()))
                                             {
                                                 if (Convert.ToDecimal(item1["iNum"].ToString()) != 0)
                                                 {
                                                     if (Convert.ToDecimal(item1["SiNum"].ToString()) != 0)
                                                     {
                                                         sql = @"Update rdrecords11 set iNum=isnull(iNum,0)+(" + item1["SiNum"].ToString() + ") where autoid='" + iChildId2 + "' ";
                                                         body.Amount = (Convert.ToDecimal(body.Amount.ToString("0.00"))) + (Convert.ToDecimal(item1["SiNum"].ToString()));
                                                         DBHelper.CmdExecuteNonQuery(sql, cmd, "辅计量余数处理失败！");
                                                     }
                                                 }
                                             }
                                         }

                                     }

                                     //更新现存量
                                     key.cBustypeUN = "领料";
                                     key.cVouchTypeUN = "11";
                                     key.TableName = "IA_ST_UnAccountVouch11";
                                     //DBHelper.UpdateCurrentStockNEW(cmd, item.InvCode, head.WHCode, item.BatchCode, -item.Quantity, item.cFree1, item.cFree2, item.cFree3, item.cFree4, item.cFree5, item.cFree6, item.cFree7, item.cFree8, item.cFree9, item.cFree10, key);


                                     DBHelper.UpdateCurrentStockCCGC(cmd, body.InvCode, head.WHCode, body.BatchCode, -body.Quantity, -body.Amount, body.cFree1,
                                      body.cFree2, body.cFree3, body.cFree4, body.cFree5, body.cFree6, body.cFree7, body.cFree8, body.cFree9, body.cFree10,
                                      "", key, "", "", "", "", 0, 0, 0, 0, 0, 0, 0, 0, out11, iChildId2);
                                



                                     iFatherIdTree += "'" + out11 + "',";
                                     iChildId2--;
                                 }
                             }


                             iFatherIdTwo = "'" + iFatherId + "',";
                             iChildId--;
                         }

                         #endregion

                         #region 查询
                         if (news == 0)
                         {
                             sql = @"  select DISTINCT a.ID as ID,a.ID as IDs,a.cCode  as ORCVTCode ,a.cVenCode,d.cVenName,a.cOrderCode as OOCode, 
                       a.cMaker as CreateUser,dnmaketime as CreateDateTime,cHandler as Checker,dnverifytime as CheckDateTime
                           from rdrecord01 a
                       left join Department c on a.cDepCode=c.cDepCode
                       left join Vendor d on a.cVenCode=d.cVenCode
                          where  A.ID in ({0}) 

                          select DISTINCT a.ID as IDs,b.AutoID as DetailID,b.irowno as Sequence,b.cInvCode as InvCode,b.iQuantity as Quantity,iNum as Amount,b.iOMoDID as OODetailID,CAST(b.AutoID as char) IDSS
,isnull(b.cItemCode,'') ProjectCode,isnull(b.cbatch,'') cBatch,'' version ,'' brand,
isnull(b.cFree1,'') as cFree1,
isnull(b.cFree2,'') as cFree2,
isnull(b.cFree3,'') as cFree3,
isnull(b.cFree4,'') as cFree4,
isnull(b.cFree5,'') as cFree5,
isnull(b.cFree6,'') as cFree6,
isnull(b.cFree7,'') as cFree7,
isnull(b.cFree8,'') as cFree8,
isnull(b.cFree9,'') as cFree9,
isnull(b.cFree10,'') as cFree10
                           from rdrecord01 a
                           left join rdrecords01 b on a.ID=b.ID
                           left join Department c on a.cDepCode=c.cDepCode
                           left join Vendor d on a.cVenCode=d.cVenCode 
                          where  A.ID in ({0}) 

 select DISTINCT a.ID as IDs,b.AutoID as DetailID, h.MOMaterialsID  as SourceDetailID,b.irowno as Sequence ,a.cCode as MRCVCode,b.iordercode,b.cInvCode,CAST('{2}' as char) IDSS,b.iQuantity,b.iNum
,isnull(b.cItemCode,'') ProjectCode,isnull(b.cbatch,'') cBatch,'' version ,'' brand,
isnull(b.cFree1,'') as cFree1,
isnull(b.cFree2,'') as cFree2,
isnull(b.cFree3,'') as cFree3,
isnull(b.cFree4,'') as cFree4,
isnull(b.cFree5,'') as cFree5,
isnull(b.cFree6,'') as cFree6,
isnull(b.cFree7,'') as cFree7,
isnull(b.cFree8,'') as cFree8,
isnull(b.cFree9,'') as cFree9,
isnull(b.cFree10,'') as cFree10
                           from rdrecord11 a
                            left join rdrecords11 b on a.ID=b.ID
                            left join Department c on a.cDepCode=c.cDepCode
                            left join Warehouse d on a.cWhCode=d.cWhCode                          
                            left join OM_MOMaterials h on b.iOMoMID=h.MOMaterialsID
                           
                           where a.ID in ({1})


";
                             sql = string.Format(sql, iFatherIdTwo.TrimEnd(','), "''", moidinbatch);
                             if (ds != null)
                                 ds.Merge(DBHelper.SQlReturnDataSet(sql, cmd));
                             else
                                 ds = DBHelper.SQlReturnDataSet(sql, cmd);
                         }
                         else
                         {
                             sql = @"  select DISTINCT a.ID as ID,a.ID as IDs,a.cCode  as ORCVTCode ,a.cVenCode,d.cVenName,a.cOrderCode as OOCode, 
                       a.cMaker as CreateUser,dnmaketime as CreateDateTime,cHandler as Checker,dnverifytime as CheckDateTime
                           from rdrecord01 a
                       left join Department c on a.cDepCode=c.cDepCode
                       left join Vendor d on a.cVenCode=d.cVenCode
                          where  A.ID in ({0}) 

                          select DISTINCT a.ID as IDs,b.AutoID as DetailID,b.irowno as Sequence,b.cInvCode as InvCode,CAST(b.AutoID as char) IDSS,b.iQuantity as Quantity,
iNum as Amount,b.iOMoDID as OODetailID,isnull(b.cItemCode,'') ProjectCode,isnull(b.cbatch,'') cBatch,'' version ,'' brand,
isnull(b.cFree1,'') as cFree1,isnull(b.cFree2,'') as cFree2,isnull(b.cFree3,'') as cFree3,
isnull(b.cFree4,'') as cFree4,isnull(b.cFree5,'') as cFree5,isnull(b.cFree6,'') as cFree6,
isnull(b.cFree7,'') as cFree7,isnull(b.cFree8,'') as cFree8,isnull(b.cFree9,'') as cFree9,
isnull(b.cFree10,'') as cFree10
                           from rdrecord01 a
                           left join rdrecords01 b on a.ID=b.ID
                           left join Department c on a.cDepCode=c.cDepCode
                           left join Vendor d on a.cVenCode=d.cVenCode 
                          where  A.ID in ({0}) 



    select DISTINCT a.ID as IDs,b.AutoID as DetailID, h.MOMaterialsID  as SourceDetailID,b.irowno as Sequence,a.cCode as MRCVCode ,b.iordercode,
CAST('{2}' as char) IDSS,b.cInvCode,b.iQuantity,b.iNum,a.ID ERPID,b.AutoID as ERPDetailID,isnull(b.cItemCode,'') ProjectCode,isnull(b.cbatch,'') cBatch,
'' version ,'' brand,isnull(b.cFree1,'') as cFree1,isnull(b.cFree2,'') as cFree2,isnull(b.cFree3,'') as cFree3,isnull(b.cFree4,'') as cFree4,
isnull(b.cFree5,'') as cFree5,isnull(b.cFree6,'') as cFree6,isnull(b.cFree7,'') as cFree7,isnull(b.cFree8,'') as cFree8,isnull(b.cFree9,'') as cFree9,
isnull(b.cFree10,'') as cFree10
                           from rdrecord11 a
                            left join rdrecords11 b on a.ID=b.ID
                            left join Department c on a.cDepCode=c.cDepCode
                            left join Warehouse d on a.cWhCode=d.cWhCode                          
                            left join OM_MOMaterials h on b.iOMoMID=h.MOMaterialsID
                           
                           where  a.ID in ({1})

";                          if (iFatherIdTree.TrimEnd(',')=="")
                                 sql = string.Format(sql, iFatherIdTwo.TrimEnd(','), "''", moidinbatch);
                             else

                                 sql = string.Format(sql, iFatherIdTwo.TrimEnd(','), iFatherIdTree.TrimEnd(','), moidinbatch);
                             if (ds != null)
                                 ds.Merge(DBHelper.SQlReturnDataSet(sql, cmd));
                             else
                                 ds = DBHelper.SQlReturnDataSet(sql, cmd);
                         }

                         #endregion
                         if (news != 0)
                         {
                             msg = JSON.DataSetToJson(ds, "details", "IDs", "detailss", "IDSS");
                         }
                         else
                         {
                             msg = JSON.DataSetToJson(ds, "details", "IDs");
                         }

                     }
                     cmd.Transaction.Commit();
                 }

                 catch (Exception ex)
                 {
                     cmd.Transaction.Rollback();
                     log.Error(ex.Message);
                     throw new Exception(ex.Message);
                 }
                 finally
                 {
                     if (conn.State == ConnectionState.Open)
                     {
                         conn.Close();
                     }
                     conn.Dispose();
                 }
             }
            
            return msg;

        }



        /// <summary>
        /// 审核委外采购入库
        /// </summary>
        /// <param name="infos"></param>
        /// <returns></returns>
        public string Approve(List<ICSOutsourcingReceiveDoc> infos)
        {
            List<ICSSalesShipmentDoc> szJson = new List<ICSSalesShipmentDoc>();
            string json = "";
            DataTable dtNew = null;
            string connS = "";
            if (infos.Count <= 0)
            {
                throw new Exception("传送数据为空！");
            }

            string res = string.Empty;
            SqlConnection conn = new SqlConnection();
            SqlCommand cmd = new SqlCommand();
            string sql = string.Empty;

             List<string> result = infos.Select(t => t.WorkPoint).Distinct().ToList();


             foreach (string WorkPoint in result)
             {
                 try
                 {
                     connS = string.Format(connString, WorkPoint);
                     conn = new System.Data.SqlClient.SqlConnection(connS);
                     conn.Open();
                     SqlTransaction sqlTran = conn.BeginTransaction();
                     cmd = new SqlCommand();
                     cmd.Transaction = sqlTran;
                     cmd.Connection = conn;
                     foreach (ICSOutsourcingReceiveDoc info in infos)
                     {
                         if (WorkPoint != info.WorkPoint)
                             continue;

                         ICSUserInfo userInfo = new ICSUserInfo();
                         userInfo = DBHelper.GetPersonInfo(info.User, cmd);
                         if (info.MTime < new DateTime(2000, 01, 01))
                             throw new Exception("请输入正确的操作时间：" + info.MTime);
                         sql = @"UPDATE  RdRecord01 SET cHandler ='" + userInfo.UserName + @"' ,
                            dnverifytime=CONVERT(VARCHAR(50),GETDATE(),112),dVeriDate=GETDATE() WHERE ID='{0}'";
                         sql = string.Format(sql, info.ID);
                         DBHelper.CmdExecuteNonQuery(sql, cmd, "审核委外采购入库单失败！");

                     }
                     cmd.Transaction.Commit();
                 }
                 catch (Exception ex)
                 {
                     cmd.Transaction.Rollback();
                     log.Error(ex.Message);
                     throw new Exception(ex.Message);
                 }
                 finally
                 {
                     if (conn.State == ConnectionState.Open)
                     {
                         conn.Close();
                     }
                     conn.Dispose();
                 }
             }
            return json;

        }
        /// <summary>
        /// 删除委外采购入库单
        /// </summary>
        /// <param name="infos"></param>
        /// <returns></returns>
        public string Delete(List<ICSOutsourcingReceiveDoc> infos)
        {
            string connS = "";
            List<ICSSalesShipmentDoc> szJson = new List<ICSSalesShipmentDoc>();
            if (infos.Count <= 0)
            {
                throw new Exception("传送数据为空！");
            }
            string res = string.Empty;
            SqlConnection conn = new SqlConnection();
            SqlCommand cmd = new SqlCommand();
            string sql = string.Empty;
            List<string> result = infos.Select(t => t.WorkPoint).Distinct().ToList();


              foreach (string WorkPoint in result)
              {
                  try
                  {
                      connS = string.Format(connString, WorkPoint);
                      conn = new System.Data.SqlClient.SqlConnection(connS);
                      conn.Open();
                      SqlTransaction sqlTran = conn.BeginTransaction();
                      cmd = new SqlCommand();
                      cmd.Transaction = sqlTran;
                      cmd.Connection = conn;
                      foreach (ICSOutsourcingReceiveDoc info in infos)
                      {
                          if (WorkPoint != info.WorkPoint)
                              continue;
                          if (info.MTime < new DateTime(2000, 01, 01))
                              throw new Exception("请输入正确的操作时间：" + info.MTime);
                          sql = @"delete  RdRecord01  where  RdRecord01.ID='" + info.ID + "'";
                          sql += @"delete  RdRecords01  where  RdRecords01.ID='" + info.ID + "'";
                          //UPDATE dbo.rdrecord11 a SET cHandler='" + info.User + @"' ,
                          //                            dVeriDate=CONVERT(VARCHAR(50),GETDATE(),112),dnverifytime=GETDATE() 
                          //                            WHERE a.ID='" + info.ID + "'";
                          DBHelper.CmdExecuteNonQuery(sql, cmd, "删除委外采购入库单失败！");

                      }
                      cmd.Transaction.Commit();
                  }
                  catch (Exception ex)
                  {
                      cmd.Transaction.Rollback();
                      log.Error(ex.Message);
                      throw new Exception(ex.Message);
                  }
                  finally
                  {
                      if (conn.State == ConnectionState.Open)
                      {
                          conn.Close();
                      }
                      conn.Dispose();
                  }
              }
            return res;
        }
        /// <summary>
        /// 
        /// 记录日志
        /// </summary>
        /// <param name="Bills"></param>
        private void LogInfo(List<ICSOutsourcingReceiveDoc> Bills)
        {
            string HeadList = string.Empty;
            string BodyList = string.Empty;
            foreach (ICSOutsourcingReceiveDoc head in Bills)
            {

                HeadList += "\r\n 表头主键ID：" + head.ID + "，仓库：" + head.WHCode + "，用户：" + head.User + "，站点：" + head.WorkPoint;
                foreach (ICSOutsourcingReceiveDocs body in head.details)
                {
                    BodyList += "\r\n 表体主键ID： " + body.ODNDetailID + "，数量：" + body.Quantity;
                }
            }
            log.Info(HeadList);
            log.Info(BodyList);
        }
        public string GetSerialCode(string ID, string connS)
        {
            string sql = "EXEC Addins_GetSerialCode '{0}'";
            sql = string.Format(sql, new object[] { ID });
            return DBHelper.ExecuteScalar(CommandType.Text, sql, connS).ToString();
        }
    }
}
