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 OutsourcingIssueDoNegative
    {
        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["OutIssCRDNAME"];
        public string Get(List<ICSOutsourcingIssueDoc> infos)
        {
            List<ICSOutsourcingIssueDoc> szJson = new List<ICSOutsourcingIssueDoc>();
            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 (ICSOutsourcingIssueDoc 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='委外发料'and b.iQuantity<0";
                          if (!string.IsNullOrWhiteSpace(info.IssueCode))
                          {
                              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.IssueCode, info.MTime, userInfo.UserName);
                          dt = DBHelper.SQlReturnData(sql, cmd);

                          if (dt.Rows.Count <= 0 || dt == null)
                              throw new Exception("委外发料单：" + info.IssueCode + "，无信息！");
                          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;

        }
        public string CreateOutsourcingIssueDoNegative(List<ICSOutsourcingIssueDoc> Bills)
        {
            string msg = "";
            string mocodeall = "";
            string sql = string.Empty;
            DataTable dt = null;
            DataSet ds = null;
            int isif = 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 (ICSOutsourcingIssueDoc head in Bills)
                      {

                          string mocode = "";
                          string seq = "";
                          string iFatherIdTwo = "";
                          if (WorkPoint != head.WorkPoint)
                              continue;

                          string[] ss = head.WorkPoint.Split('_');
                          ERPDB = ss[1];
                          if (!DBHelper.IsInventoryConsolidation(cmd, head.WorkPoint))
                              throw new Exception("U8正在整理现存量，请稍后再试");

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

                          if (head.details.Count <= 0)
                          {
                              throw new Exception("表体信息不存在！");
                          }

                          if (head.SourceType == "委外领料申请单")
                          {
                              #region 判断物料是否超额领取
                              foreach (ICSOutsourcingIssueDocs body in head.details)
                              {
                                  sql = @"SELECT isnull(fOutQuantity,0) as fOutQuantity,iQuantity FROM dbo.MaterialAppVouchs WHERE AutoID='" + body.SourceDetailID + "'";
                                  DataTable dtQty = DBHelper.SQlReturnData(sql, cmd);
                                  if (dtQty != null && dtQty.Rows.Count > 0)
                                  {
                                      //已领数量
                                      decimal IssQty = Convert.ToDecimal(dtQty.Rows[0]["fOutQuantity"]);
                                      //应领数量
                                      decimal Qty = Convert.ToDecimal(dtQty.Rows[0]["iQuantity"]);
                                      if (IssQty - body.Quantity < 0)
                                      {
                                          throw new Exception("物料领取小于当前可领用最大数，单据号：" + head.SourceCode + "，行号：" + body.Sequence + "");
                                      }
                                  }
                                  else
                                  {
                                      throw new Exception("领料申请单不存在！单据号：" + head.SourceCode);
                                  }
                              }
                              #endregion

                              #region 现存量是否够领
                              //                    //if (head.IsReturn != "1")
                              //                    //{
                              //                        foreach (rdrecords11 body in head.rdcords)
                              //                        {
                              //                            sql = string.Format(@"SELECT iQuantity FROM dbo.CurrentStock 
                              //                                                WHERE cInvCode='{0}' AND cWhCode='{1}'", body.cInvCode, head.cWhCode);
                              //                            if (!string.IsNullOrWhiteSpace(body.cBatch))
                              //                                sql += " and cBatch='" + body.cBatch + "'";
                              //                            DataTable dt = DBHelper.SQlReturnData(sql, cmd);
                              //                            decimal qtyCurrent = 0;
                              //                            if (dt != null && dt.Rows.Count > 0)
                              //                            {
                              //                                qtyCurrent = Convert.ToDecimal(dt.Rows[0][0]);
                              //                                if (qtyCurrent < body.Quantity)
                              //                                {
                              //                                    throw new Exception("物料编号：" + body.cInvCode + "，现存量不足！");
                              //                                }
                              //                            }
                              //                            else
                              //                            {
                              //                                throw new Exception("该物料：" + body.cInvCode + "在U8中仓库：" + head.cWhCode + "中不存在！");
                              //                            }
                              //                        }
                              //                    //}

                              #endregion
                          }
                          else
                          {
                              #region 判断委外订单是否已关闭
                              sql = @"SELECT distinct isnull(b.cbCloser,'') as CloseUser 
                            from dbo.OM_MOMaterials a
                            INNER JOIN OM_MODetails b ON a.MoDetailsID = b.MoDetailsID 
                            where a.MOMaterialsID='" + head.details[0].SourceDetailID + "'";
                              DataTable dtClose = DBHelper.SQlReturnData(sql, cmd);
                              if (dtClose != null && dtClose.Rows.Count > 0)
                              {
                                  if (!string.IsNullOrEmpty(dtClose.Rows[0][0].ToString()))
                                  {
                                      throw new Exception("该委外订单行已关闭，无法领料！");
                                  }
                              }
                              #endregion

                              #region 判断物料是否超额领取
                              foreach (ICSOutsourcingIssueDocs body in head.details)
                              {
                                  sql = @"SELECT isnull(b.fOutExcess,0) as fOutExcess,isnull(a.iSendQTY,0) as IssQty,a.iQuantity  
                                FROM dbo.OM_MOMaterials a 
                                LEFT JOIN dbo.Inventory b ON a.cInvCode=b.cInvCode
                                WHERE a.MOMaterialsID='" + body.SourceDetailID + "'";
                                  DataTable dtQty = DBHelper.SQlReturnData(sql, cmd);
                                  if (dtQty != null && dtQty.Rows.Count > 0)
                                  {
                                      //可超领比率
                                      decimal fOutExcess = Convert.ToDecimal(dtQty.Rows[0]["fOutExcess"]);
                                      //已领数量
                                      decimal IssQty = Convert.ToDecimal(dtQty.Rows[0]["IssQty"]);
                                      //应领数量
                                      decimal Qty = Convert.ToDecimal(dtQty.Rows[0]["iQuantity"]);
                                      //当前可总领数量
                                      decimal TotalQty = Qty + fOutExcess * Qty;
                                      //if (head.IsReturn != "1")
                                      //{
                                      if (IssQty - body.Quantity < 0)
                                      {
                                          throw new Exception("物料退料数量超过当前已领用数量，AllocateId：" + body.SourceDetailID);
                                      }
                                      //}
                                      //else
                                      //{
                                      //if (IssQty - body.Quantity < 0)
                                      //{
                                      //    throw new Exception("物料退料超过当前已领用最大数，AllocateId：" + body.SourceDetailID);
                                      //}
                                      //}
                                  }
                                  else
                                  {
                                      throw new Exception("委外订单行子件不存在！AllocateId：" + body.SourceDetailID);
                                  }
                              }

                              #endregion
                          }


                          #region 委外退料表头
                          num = head.details.Count();
                          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);

                          if (head.SourceType != "委外领料申请单")
                          {
                              mocodeall = head.SourceCode;
                              sql = @"
                            INSERT INTO dbo.rdrecord11
                            ( ID ,bRdFlag ,cVouchType ,cBusType ,cSource ,
                            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','委外发料','委外订单',
                            @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.SourceCode + "'";
                              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("@bredvouch", "0"));
                              cmd.Parameters.Add(new SqlParameter("@cRdCode", cRDcode));
                              cmd.CommandText = sql;
                          }
                          else
                          {

                              sql = @"select DISTINCT comcode from MaterialAppVouch a
                                left join MaterialAppVouchs b on a.ID=b.ID where csource is not null
                                and ccode='" + head.SourceCode + "'";
                              DataTable dtQty = DBHelper.SQlReturnData(sql, cmd);
                              if (dtQty != null && dtQty.Rows.Count > 0)
                              {
                                  foreach (DataRow item in dtQty.Rows)
                                  {
                                      mocodeall = item["comcode"].ToString();
                                  }
                              }

                              sql = @"INSERT INTO dbo.rdrecord11
                                    ( ID ,bRdFlag ,cVouchType ,cBusType ,cSource ,cBusCode ,cWhCode ,cMPoCode,
                                      dDate ,cCode ,cRdCode ,cDepCode ,cPersonCode ,cHandler ,cMemo ,
                                      bTransFlag ,cMaker ,cDefine1 ,cDefine2 ,cDefine3 ,cDefine4 ,
                                      cDefine5 ,cDefine6 ,cDefine7 ,cDefine8 ,cDefine9 ,cDefine10 ,
                                      dVeriDate ,bpufirst ,biafirst ,VT_ID ,bIsSTQc ,cDefine11 ,
                                      cDefine12 ,cDefine13 ,cDefine14 ,cDefine15 ,cDefine16 ,
                                      bOMFirst ,bFromPreYear ,bIsComplement ,iDiscountTaxType ,
                                      ireturncount ,iverifystate ,iswfcontrolled ,cModifyPerson ,
                                      dnmaketime ,dnverifytime ,bredvouch ,bmotran ,bHYVouch ,iPrintCount 
                                    )
                            SELECT distinct @ID,0,'11','委外发料','领料申请单',a.cCode,@cWhCode,comcode,
                            		CONVERT(NVARCHAR(15),GETDATE(),23),@cCode,@cRdCode,a.cDepCode,a.cPersonCode,@cHandler,a.cMemo,
                            		0,@cMaker,a.cDefine1,cdefine2,a.cDefine3,a.cDefine4,
                            		a.cDefine5,a.cDefine6,a.cDefine7,a.cDefine8,a.cDefine9,a.cDefine10,
                            		CONVERT(NVARCHAR(15),GETDATE(),23),0,0,@VT_ID,0,a.cDefine11,
                            		a.cDefine12,a.cDefine13,a.cDefine14,a.cDefine15,a.cDefine16,
                            		0,0,0,0,
                            		0,0,0,'',
                            		GETDATE(),GETDATE(),0,0,0,0
                            FROM dbo.MaterialAppVouch a
                            LEFT JOIN dbo.MaterialAppVouchs B ON A.ID=B.ID
                            WHERE a.cCode='" + head.SourceCode + "'";
                              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", head.User));
                              cmd.Parameters.Add(new SqlParameter("@cMaker", head.User));
                              cmd.Parameters.Add(new SqlParameter("@VT_ID", DEF_ID));
                              cmd.Parameters.Add(new SqlParameter("@cRdCode", cRDcode));
                              //DBHelper.CmdExecuteNonQuery(sql, cmd, "生成材料出库单表头失败！");
                              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 委外退料表体
                          string iNQuantity = "";
                          int irowno = 0;
                          foreach (ICSOutsourcingIssueDocs 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 =(select a.MODetailsID from OM_MODetails a left join
OM_MOMaterials b on a.MoDetailsID=b.MODetailsID where b.MOMaterialsID='" + body.SourceDetailID + @"'
)";
                              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;
                                      }
                                  }
                              }
                              irowno++;
                              body.Quantity = -body.Quantity;
                              iNQuantity = "-a.iQuantity";
                              if (head.SourceType != "委外领料申请单")
                              {
                                  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,null,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='" + body.SourceDetailID + "'";
                                  cmd.Parameters.Clear();
                                  cmd.Parameters.Add(new SqlParameter("@AutoID", iChildId));
                                  cmd.Parameters.Add(new SqlParameter("@ID", iFatherId));
                                  cmd.Parameters.Add(new SqlParameter("@iQuantity", body.Quantity));
                                  cmd.Parameters.Add(new SqlParameter("@iNum", body.Amount.ToString("0.00")));
                                  //cmd.Parameters.Add(new SqlParameter("@cCode", iBaseCodeLen));
                                  cmd.Parameters.Add(new SqlParameter("@cBatch", body.BatchCode));
                                  cmd.Parameters.Add(new SqlParameter("@irowno", irowno));
                                  cmd.Parameters.Add(new SqlParameter("@iNQuantity", iNQuantity));
                                  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;
                              }
                              else
                              {
                                  sql = @"INSERT INTO dbo.rdrecords11
                                        ( AutoID ,ID ,cInvCode ,iQuantity ,iFlag ,cDefine22 ,iOMoDID,iOMoMID,iNum,
                                          cDefine23 ,cDefine24 ,cDefine25 ,cDefine26 ,cDefine27 ,
                                          cItem_class ,cItemCode ,cName ,cItemCName ,iNQuantity ,
                                          cDefine28 ,cDefine29 ,cDefine30 ,cDefine31 ,cDefine32 ,
                                          cDefine33 ,cDefine34 ,cDefine35 ,cDefine36 ,cDefine37 ,
                                          bLPUseFree ,iOriTrackID ,bCosting ,bVMIUsed ,iMaIDs ,
                                          iExpiratDateCalcu ,iordertype ,isotype ,ipesotype ,irowno,
                                          cBatch,cFree1,cFree2,cFree3,cFree4,cFree5,cFree6,cFree7,cFree8,cFree9,cFree10)
                                SELECT @AutoID,@ID,a.cInvCode,@iQuantity,0,a.cDefine22,a.iOMoDID,a.iOMoMID,@iNum,
                                		a.cDefine23,a.cDefine24,a.cDefine25,a.cDefine26,a.cDefine27,
                                		a.cItem_class,@cItemCode,a.cName,a.cItemCName,@iQuantity,
                                		a.cDefine28,a.cDefine29,a.cDefine30,a.cDefine31,a.cDefine32,
                                		a.cDefine33,a.cDefine34,a.cDefine35,a.cDefine36,a.cDefine37,
                                		0,0,1,0,a.AutoID,
                                		0,0,0,0,@irowno,
                                        @cBatch,@cFree1,@cFree2,@cFree3,@cFree4,@cFree5,@cFree6,@cFree7,@cFree8,@cFree9,@cFree10
                                FROM dbo.MaterialAppVouchs a WHERE a.AutoID='" + body.SourceDetailID + "'";
                                  cmd.Parameters.Clear();
                                  cmd.Parameters.Add(new SqlParameter("@ID", iFatherId));
                                  cmd.Parameters.Add(new SqlParameter("@AutoID", iChildId));
                                  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("@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));
                                  //DBHelper.CmdExecuteNonQuery(sql, cmd, "生成材料出库单表头失败！");
                                  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);
                              }

                              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, mocodeall, 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, mocodeall, 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, mocodeall, 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
                                  }
                              }

                              if (head.SourceType != "委外领料申请单")
                              {
                                  #region 回写工单子件已领数量
                                  sql = "update OM_MOMaterials set iSendQTY=isnull(iSendQTY,0)+" + body.Quantity + ",fsendapplyqty=isnull(fsendapplyqty,0)+" + body.Quantity + @" where MOMaterialsID='" + body.SourceDetailID + "' ";
                                  DBHelper.CmdExecuteNonQuery(sql, cmd, "回写委外订单子件已领数量失败！");
                                  #endregion
                              }
                              else
                              {
                                  #region 回写工单子件已领数量
                                  sql = "update OM_MOMaterials set iSendQTY=isnull(iSendQTY,0)+" + body.Quantity + @",fsendapplyqty=isnull(fsendapplyqty,0)+" + body.Quantity + @" where MOMaterialsID=(select iOMoMID from  MaterialAppVouchs where AutoID='" + body.SourceDetailID + @"') 
                            update MaterialAppVouchs set fOutQuantity=isnull(fOutQuantity,0)+" + body.Quantity + @" where AutoID='" + body.SourceDetailID + @"'
                            ";
                                  DBHelper.CmdExecuteNonQuery(sql, cmd, "回写委外订单子件已领数量失败！");
                                  #endregion
                              }

                              if (head.SourceType != "委外领料申请单")
                              {
                                  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,iOMoMID from rdrecords11 GROUP BY iOMoMID) b on a.MOMaterialsID=b.iOMoMID
                        where  a.MOMaterialsID='" + body.SourceDetailID + "'";
                                  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 rdrecords11 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, "辅计量余数处理失败！");
                                                  }
                                              }
                                          }
                                      }

                                  }
                              }
                              else
                              {
                                  sql = @"select isnull(fOutQuantity,0)fOutQuantity,isnull(iQuantity,0)iQuantity,ISNULL(iNum, 0)iNum,ISNULL(NiNum, 0)NiNum,ISNULL(iNum, 0)-ISNULL(NiNum, 0) SiNum from MaterialAppVouchs a
                        LEFT JOIN (select sum(iNum) NiNum,iMaIDs from rdrecords11 GROUP BY iMaIDs) b on a.AutoID=b.iMaIDs
                        where  a.AutoID='" + body.SourceDetailID + "'";
                                  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 rdrecords11 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, "辅计量余数处理失败！");
                                                  }
                                              }
                                          }
                                      }

                                  }
                              }


                              //更新现存量
                              key.cBustypeUN = "委外发料";
                              key.cVouchTypeUN = "11";
                              key.TableName = "IA_ST_UnAccountVouch11";
                              //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,
                                      "", key, "", "", "", "", 0, 0, 0, 0, 0, 0, 0, 0, iFatherId, iChildId);

                             


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

                          #endregion
                          #region 查询
                          sql = @"select DISTINCT a.ID  as ID,a.ID as IDs,a.cCode as IssueNEGCode,a.cDepCode,c.cDepName,a.cWhCode,
                            d.cWhName,a.cSource,cMPoCode,a.cMaker,dnmaketime,cHandler,dnverifytime 
                            from rdrecord11 a
                            left join Department c on a.cDepCode=c.cDepCode
                            left join Warehouse d on a.cWhCode=d.cWhCode
                           where a.cBusType='委外发料' and a.ID in ({0})

               select DISTINCT a.ID as IDs,(case when isnull(f.AutoID,'')='' then h.MOMaterialsID else f.AutoID end) as SourceDetailID,b.irowno as Sequence ,b.iordercode,b.cInvCode,b.iQuantity,b.iNum,b.AutoID as DetailID
,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
                            left join MaterialAppVouchs f on b.iMaIDs=f.autoid
                           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);

                          //                            sql = @" select a.ID  as 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='委外发料'and b.iQuantity<0 and a.ID in ({0})"; 
                          //                sql = string.Format(sql, iFatherIdTwo.TrimEnd(','));
                          //                dt = DBHelper.SQlReturnData(sql, cmd);
                          #endregion



                      }
                      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;
        }
        public string Approve(List<ICSOutsourcingIssueDoc> infos)
        {
            List<ICSOutsourcingIssueDoc> szJson = new List<ICSOutsourcingIssueDoc>();
            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 (ICSOutsourcingIssueDoc 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  dbo.rdrecord11 SET cHandler ='" + userInfo.UserName + @"' ,
                            dnmodifytime  =CONVERT(VARCHAR(50),GETDATE(),112),dModifyDate  =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<ICSOutsourcingIssueDoc> infos)
        {
            List<ICSOutsourcingIssueDoc> szJson = new List<ICSOutsourcingIssueDoc>();
            if (infos.Count <= 0)
            {
                throw new Exception("传送数据为空！");
            }
            DataTable dtNew = null;
            string connS = "";
            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 (ICSOutsourcingIssueDoc info in infos)
                     {
                         if (WorkPoint != info.WorkPoint)
                             continue;
                         if (info.MTime < new DateTime(2000, 01, 01))
                             throw new Exception("请输入正确的操作时间：" + info.MTime);
                         sql = @" DELETE dbo.rdrecord11 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 res;


        }
        /// <summary>
        /// 
        /// 记录日志
        /// </summary>
        /// <param name="Bills"></param>
        private void LogInfo(List<ICSOutsourcingIssueDoc> Bills)
        {
            string HeadList = string.Empty;
            string BodyList = string.Empty;
            foreach (ICSOutsourcingIssueDoc head in Bills)
            {
                HeadList += "\r\n 表头主键ID：" + head.ID + "，仓库：" + head.WHCode + "，用户：" + head.User + "，站点：" + head.WorkPoint;
                foreach (ICSOutsourcingIssueDocs body in head.details)
                {
                    BodyList += "\r\n 表体主键ID： " + body.SourceDetailID + "，数量：" + body.Quantity;
                }
            }
            log.Info(HeadList);
            log.Info(BodyList);
        }
    }
}

