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

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

        public static void AddList(string oldLotNo, string ROUTECODE, int OpSeq, string OPCODE, List<ICSITEMLot> list, string FPType, string dsconn)
        {
            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                var lineOldLot = db.ICSITEMLot.SingleOrDefault(a => a.LotNO == oldLotNo);
                if (lineOldLot == null)
                {
                    throw new Exception("原条码" + oldLotNo + "不存在");
                }
                foreach (ICSITEMLot newLot in list)
                {
                    var lineLot = db.ICSITEMLot.SingleOrDefault(a => a.LotNO == newLot.LotNO);
                    if (lineLot != null)
                    {
                        throw new Exception("新条码" + newLot.LotNO + "已生成");
                    }
                    //1.ICSITEMLot新条码
                    lineLot = new ICSITEMLot();
                    lineLot.ACTIVE = lineOldLot.ACTIVE;
                    //lineLot.EATTRIBUTE3 = lineOldLot.EATTRIBUTE3;
                    //lineLot.EATTRIBUTE4 = lineOldLot.EATTRIBUTE4;
                    //lineLot.EATTRIBUTE5 = lineOldLot.EATTRIBUTE5;
                    //lineLot.EATTRIBUTE6 = lineOldLot.EATTRIBUTE6;
                    //lineLot.EATTRIBUTE7 = lineOldLot.EATTRIBUTE7;
                    lineLot.Exdate = lineOldLot.Exdate;
                    lineLot.ItemCode = lineOldLot.ItemCode;
                    lineLot.lastPrintTime = null;
                    lineLot.lastPrintUSERID = null;
                    lineLot.PrintTimes = 0;
                    lineLot.PRODUCTDATE = lineOldLot.PRODUCTDATE;
                    lineLot.tFileName = lineOldLot.tFileName;
                    lineLot.TimesPrint = lineOldLot.TimesPrint;
                    lineLot.TwoMTIME = lineOldLot.TwoMTIME;
                    lineLot.TwoMUSER = lineOldLot.TwoMUSER;
                    lineLot.VenBatch = lineOldLot.VenBatch;
                    lineLot.VenderLotNO = lineOldLot.VenderLotNO;
                    lineLot.VENDORCODE = lineOldLot.VENDORCODE;
                    lineLot.VENDORITEMCODE = lineOldLot.VENDORITEMCODE;
                    lineLot.VoucherNO = lineOldLot.VoucherNO;
                    lineLot.VoucherRow = lineOldLot.VoucherRow;
                    lineLot.TransNO = lineOldLot.TransNO;
                    lineLot.TransLine = lineOldLot.TransLine;
                    lineLot.WorkPoint = lineOldLot.WorkPoint;

                    lineLot.ID = AppConfig.GetGuid();
                    lineLot.LotNO = newLot.LotNO;
                    lineLot.LOTQTY = newLot.LOTQTY;
                    lineLot.TYPE = "跟踪单分批";
                    lineLot.EATTRIBUTE1 = newLot.EATTRIBUTE1;//原条码
                    lineLot.EATTRIBUTE2 = newLot.EATTRIBUTE2;//原条码数量
                    lineLot.EATTRIBUTE5 = newLot.EATTRIBUTE5;//原条码分批时工序序号+","+工序
                    lineLot.EATTRIBUTE6 = "0";//被分批时的,已分出数量
                    lineLot.EATTRIBUTE7 = FPType;
                    lineLot.MTIME = DateTime.Now;
                    lineLot.MUSER = AppConfig.UserCode;
                    lineLot.MUSERName = AppConfig.UserName;
                    //2.变更原跟踪单原条码数量
                    lineOldLot.LOTQTY -= newLot.LOTQTY;
                    //2.1变更原跟踪单已分批数量
                    if (string.IsNullOrEmpty(lineOldLot.EATTRIBUTE6))
                    {
                        lineOldLot.EATTRIBUTE6 = "0";
                    }
                    lineOldLot.EATTRIBUTE6 = (Convert.ToDecimal(lineOldLot.EATTRIBUTE6) + newLot.LOTQTY).ToString();

                    db.ICSITEMLot.InsertOnSubmit(lineLot);
                    db.SubmitChanges();
                    //2.2报工表记录分批数量
                    var lineOnwip = db.ICSLOTONWIP.SingleOrDefault(a => a.LOTNO == lineOldLot.LotNO && a.ROUTECODE == ROUTECODE && a.OPSEQ == OpSeq && a.OPCODE == OPCODE);
                    if (lineOnwip != null)
                    {
                        if (FPType == "正常")
                        {
                            lineOnwip.FPQTY = (lineOnwip.FPQTY == null ? 0 : lineOnwip.FPQTY) + newLot.LOTQTY;
                        }
                        else if (FPType == "返工")
                        {
                            lineOnwip.RWFPQTY = (lineOnwip.RWFPQTY == null ? 0 : lineOnwip.RWFPQTY) + newLot.LOTQTY;
                        }
                        else
                        {
                            throw new Exception("分批类型错误!");
                        }
                        db.SubmitChanges();
                    }
                    //3.ICSITEMROUTE2OPLot
                    var lineNewOpLot = db.ICSITEMROUTE2OPLot.FirstOrDefault(a => a.LotNo == newLot.LotNO);
                    if (lineNewOpLot != null)
                    {
                        throw new Exception("新条码" + newLot.LotNO + "已生成工序");
                    }
                    List<ICSITEMROUTE2OPLot> listOpLot = db.ICSITEMROUTE2OPLot.Where(a => a.LotNo == oldLotNo && a.OPSEQ > OpSeq).ToList();
                    if (listOpLot.Count == 0)
                    {
                        //throw new Exception("原条码" + oldLotNo + "已是最后一道工序,不需要分批.");
                        int fpopseq = 9999;
                        ICSITEMROUTE2OPLot v = db.ICSITEMROUTE2OPLot.Single(a => a.LotNo == oldLotNo && a.OPSEQ == OpSeq);
                        ICSOP fpop = db.ICSOP.SingleOrDefault(a => a.OPCODE == fpopseq.ToString() && a.WorkPoint == lineOldLot.WorkPoint);
                        if (fpop == null)
                        {
                            throw new Exception("跟踪单最后一道工序分批时,需要专用虚拟工序 ,工序代码9999,此工序未维护");
                        }
                        ICSITEMROUTE2OPLot opLotInfo = new ICSITEMROUTE2OPLot();
                        opLotInfo.CtrlType = "串行";
                        opLotInfo.OPAttr = "自制";
                        opLotInfo.OPSEQ = fpopseq;
                        opLotInfo.OPCODE = fpopseq.ToString();
                        opLotInfo.EATTRIBUTE1 = "0";//如果是分批出的返工跟踪单 0 表示原始状态未进行工序变更,1表示已做了跟踪单工序变更(跟踪单工序变更保存时该值为1)
                        opLotInfo.IDMERGERULE = v.IDMERGERULE;
                        opLotInfo.IDMERGETYPE = v.IDMERGETYPE;
                        opLotInfo.ITEMCODE = v.ITEMCODE;
                        opLotInfo.OPCONTROL = v.OPCONTROL;
                        opLotInfo.OPTIONALOP = v.OPTIONALOP;
                        opLotInfo.ROUTECODE = v.ROUTECODE;
                        opLotInfo.RouteMGR = v.RouteMGR;
                        opLotInfo.WorkPoint = v.WorkPoint;

                        opLotInfo.ID = AppConfig.GetGuid();
                        opLotInfo.LotNo = newLot.LotNO;
                        opLotInfo.MTIME = DateTime.Now;
                        opLotInfo.MUSER = AppConfig.UserCode;
                        opLotInfo.MUSERName = AppConfig.UserName;
                    }

                    foreach (ICSITEMROUTE2OPLot opLot in listOpLot)
                    {
                        ICSITEMROUTE2OPLot opLotInfo = new ICSITEMROUTE2OPLot();
                        opLotInfo.CtrlType = opLot.CtrlType;
                        opLotInfo.EATTRIBUTE1 = "0";
                        opLotInfo.IDMERGERULE = opLot.IDMERGERULE;
                        opLotInfo.IDMERGETYPE = opLot.IDMERGETYPE;
                        opLotInfo.ITEMCODE = opLot.ITEMCODE;
                        opLotInfo.OPAttr = opLot.OPAttr;
                        opLotInfo.OPCODE = opLot.OPCODE;
                        opLotInfo.OPCONTROL = opLot.OPCONTROL;
                        opLotInfo.OPSEQ = opLot.OPSEQ;
                        opLotInfo.OPTIONALOP = opLot.OPTIONALOP;
                        opLotInfo.ROUTECODE = opLot.ROUTECODE;
                        opLotInfo.RouteMGR = opLot.RouteMGR;
                        opLotInfo.WorkPoint = opLot.WorkPoint;
                        opLotInfo.CtrlType = opLot.CtrlType;

                        opLotInfo.ID = AppConfig.GetGuid();
                        opLotInfo.LotNo = newLot.LotNO;
                        opLotInfo.MTIME = DateTime.Now;
                        opLotInfo.MUSER = AppConfig.UserCode;
                        opLotInfo.MUSERName = AppConfig.UserName;
                        db.ICSITEMROUTE2OPLot.InsertOnSubmit(opLotInfo);
                        db.SubmitChanges();
                        var lineMo2User = db.ICSMO2User.SingleOrDefault(a => a.LOTNO == oldLotNo && a.OPCODE == opLot.OPCODE);
                        if (lineMo2User != null)
                        {
                            ICSMO2User MO2USR = new ICSMO2User();
                            MO2USR.ID = AppConfig.GetGuid();
                            MO2USR.LOTNO = newLot.LotNO;
                            MO2USR.MOCODE = lineMo2User.MOCODE;
                            MO2USR.MOSEQ = lineMo2User.MOSEQ;
                            MO2USR.SEGCODE = lineMo2User.SEGCODE;
                            MO2USR.RouteCode = lineMo2User.RouteCode;
                            MO2USR.OPCODE = lineMo2User.OPCODE;
                            MO2USR.USERCODE = lineMo2User.USERCODE;
                            MO2USR.USERName = lineMo2User.USERName;
                            MO2USR.EQPCode = lineMo2User.EQPCode;
                            MO2USR.EQPName = lineMo2User.EQPName;
                            MO2USR.MUSER = lineMo2User.MUSER;
                            MO2USR.MUSERName = lineMo2User.MUSERName;
                            MO2USR.MTIME = Convert.ToDateTime(lineMo2User.MTIME);
                            MO2USR.WorkPoint = lineMo2User.WorkPoint;
                            MO2USR.EATTRIBUTE1 = lineMo2User.EATTRIBUTE1;
                            MO2USR.StartPlanDate = lineMo2User.StartPlanDate;
                            MO2USR.EndPlanDate = lineMo2User.EndPlanDate;
                            //if (lineMo2User.PRLineID == "False" || lineMo2User.PRLineID == "0" || string.IsNullOrEmpty(lineMo2User.PRLineID))
                            //{
                            //    MO2USR.PRLineID = null;
                            //}
                            //else
                            //{
                            //    MO2USR.PRLineID = lineMo2User.PRLineID;
                            //}
                            MO2USR.PRLineID = null;
                            db.ICSMO2User.InsertOnSubmit(MO2USR);
                            List<ICSMO2UserDetail> lineMUDetail = db.ICSMO2UserDetail.Where(a => a.ID == lineMo2User.ID).ToList();
                            for (int i = 0; i < lineMUDetail.Count; i++)
                            {
                                ICSMO2UserDetail info = new ICSMO2UserDetail();
                                info.ID = MO2USR.ID;
                                info.DID = AppConfig.GetGuid();
                                info.EQPID = lineMUDetail[i].EQPID;
                                info.AVAILABLE = lineMUDetail[i].AVAILABLE;
                                info.PLANEND = lineMUDetail[i].PLANEND;
                                info.PLANSTART = lineMUDetail[i].PLANSTART;
                                info.PLANQTY = lineMUDetail[i].PLANQTY;
                                info.PLANTIME = lineMUDetail[i].PLANTIME;
                                db.ICSMO2UserDetail.InsertOnSubmit(info);
                                db.SubmitChanges();
                            }
                            db.SubmitChanges();
                        }
                    }

                    ////4.ICSLOTONWIP原条码数量,(开工时取上一道工序/上一组工序GOODQTY,完工时取上一道工序GOODQTY)
                    //List<ICSLOTONWIP> listOnwip = db.ICSLOTONWIP.Where(a => a.LOTNO == oldLotNo).ToList();
                    //if (listOnwip.Count == 0)
                    //{
                    //    //throw new Exception("原条码" + oldLotNo + "报工信息不存在");//第一道工序?
                    //}
                    //foreach (ICSLOTONWIP onwip in listOnwip)
                    //{
                    //    var lineOnwip = db.ICSLOTONWIP.SingleOrDefault(a => a.LOTNO == onwip.LOTNO && a.LOTSEQ == onwip.LOTSEQ && a.OPSEQ == onwip.OPSEQ);
                    //    //lineOnwip.LOTQTY = newLot.LOTQTY;
                    //    lineOnwip.GOODQTY -= newLot.LOTQTY;
                    //    db.SubmitChanges();
                    //}
                }
                db.SubmitChanges();
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw new Exception(ex.Message);
            }
        }

        public static void Delete(string LotNo, string FPType, string conn)
        {
            FramDataContext db = new FramDataContext(conn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                if (!LotNo.Contains("-"))
                {
                    throw new Exception("从工单分批出的条码请到工单维护界面进行删除!");
                }

                var lineSim = db.ICSLOTSIMULATION.FirstOrDefault(a => a.LOTNO == LotNo);
                if (lineSim != null)
                {
                    throw new Exception("跟踪单已开始作业不能删除");
                }

                //1.恢复原始条码数量ICSITEMLot
                //string oldLotNo = "";
                var lineNewLotNo = db.ICSITEMLot.SingleOrDefault(a => a.LotNO == LotNo);
                if (lineNewLotNo == null)
                {
                    throw new Exception(LotNo + "跟踪单不存在");
                }
                string oldLotNo = lineNewLotNo.EATTRIBUTE1;
                if (string.IsNullOrEmpty(oldLotNo))
                {
                    oldLotNo = LotNo.Substring(0, LotNo.LastIndexOf('-'));
                }
                int OPSEQ = Convert.ToInt32(lineNewLotNo.EATTRIBUTE5.Split(',')[0]);
                string OPCODE = lineNewLotNo.EATTRIBUTE5.Split(',')[1];
                if (FPType == "正常")
                {
                    var lineONWIP = db.ICSLOTONWIP.SingleOrDefault(a => a.LOTNO == LotNo && a.OPSEQ > OPSEQ && a.OPCODE == OPCODE);
                    if (lineONWIP != null)
                    {
                        throw new Exception("原跟踪单" + oldLotNo + ",工序" + OPCODE + "后续工序已开工,本跟踪单不可删除!");
                    }
                }
                var lineOldLotNo = db.ICSITEMLot.SingleOrDefault(a => a.LotNO == oldLotNo);
                if (lineOldLotNo == null)
                {
                    throw new Exception(oldLotNo + "原跟踪单不存在");
                }
                lineOldLotNo.LOTQTY += lineNewLotNo.LOTQTY;
                if (string.IsNullOrEmpty(lineOldLotNo.EATTRIBUTE6))
                {
                    lineOldLotNo.EATTRIBUTE6 = "0";
                }
                lineOldLotNo.EATTRIBUTE6 = (Convert.ToDecimal(lineOldLotNo.EATTRIBUTE6) - lineNewLotNo.LOTQTY).ToString();
                db.SubmitChanges();
                //2.恢复原始条码对应工序记录的分批数量ICSLOTONWIP
                var lineOldSim = db.ICSLOTSIMULATION.SingleOrDefault(a => a.LOTNO == oldLotNo);
                if (lineOldSim == null)
                {
                    throw new Exception(oldLotNo + "原跟踪单Sim表被删除!");
                }
                ICSLOTONWIP OldOnwip = db.ICSLOTONWIP.SingleOrDefault(a => a.LOTNO == oldLotNo && a.LOTSEQ == lineOldSim.LOTSEQ);
                if (OldOnwip == null)
                {
                    throw new Exception("原条码:" + oldLotNo + "工序" + OPCODE + "次序" + OPSEQ.ToString() + "开工记录被删除!");
                }
                OldOnwip.FPQTY = (OldOnwip.FPQTY == null ? 0 : OldOnwip.FPQTY) - lineNewLotNo.LOTQTY;
                if (FPType == "返工")
                {
                    OldOnwip.RWFPQTY = (OldOnwip.RWFPQTY == null ? 0 : OldOnwip.RWFPQTY) - lineNewLotNo.LOTQTY;
                }
                db.SubmitChanges();

                //2.删除派工
                List<ICSMO2User> lineMO2User = db.ICSMO2User.Where(a => a.LOTNO == lineNewLotNo.LotNO).ToList();
                if (lineMO2User.Count > 0)
                {
                    //2.1删除派工子表(设备)
                    for (int i = 0; i < lineMO2User.Count; i++)
                    {
                        var lineMUDetail = db.ICSMO2UserDetail.Where(a => a.ID == lineMO2User[i].ID);
                        if (lineMUDetail.Count() > 0)
                        {
                            db.ICSMO2UserDetail.DeleteAllOnSubmit(lineMUDetail);
                        }
                    }
                    //2.2删除派工主表
                    db.ICSMO2User.DeleteAllOnSubmit(lineMO2User);
                }
                //3.删除工序
                List<ICSITEMROUTE2OPLot> listOp = db.ICSITEMROUTE2OPLot.Where(a => a.LotNo == LotNo).ToList();
                db.ICSITEMROUTE2OPLot.DeleteAllOnSubmit(listOp);
                //4.删除ICSITEMLot新条码
                db.ICSITEMLot.DeleteOnSubmit(lineNewLotNo);
                db.SubmitChanges();
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw new Exception(ex.Message);
            }
        }

        public static int GetMaxFlow(string like, string notlike, string conn)
        {
            FramDataContext db = new FramDataContext(conn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                var list = db.ICSITEMLot.Where(a => a.LotNO.Contains(like) && !a.LotNO.Contains(notlike));
                if (list.Count() == 0)
                {
                    return 0;
                }
                return list.Max(a => Convert.ToInt32(a.LotNO.Substring(a.LotNO.LastIndexOf('-') + 1)));
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw new Exception(ex.Message);
            }
        }

        public static int GetMaxFlow0326(string oriLOTNO, string conn)
        {
            FramDataContext db = new FramDataContext(conn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                var list = db.ICSITEMLot.Where(a => a.EATTRIBUTE1 == oriLOTNO && a.WorkPoint == AppConfig.WorkPointCode);
                if (list.Count() == 0)
                {
                    return 0;
                }
                return list.Max(a => Convert.ToInt32(a.LotNO.Substring(a.LotNO.LastIndexOf('-') + 1)));
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 本工序是第一道工序且未开工/本工序已完工且检验结果已出且下道工序未开工
        /// </summary>
        /// <param name="LOTNO"></param>
        /// <param name="ROUTECODE"></param>
        /// <param name="OPSEQ"></param>
        /// <param name="WorkPiont"></param>
        /// <param name="conn"></param>
        /// <returns></returns>
        public static DataTable BaseQuery(string LOTNO, string ROUTECODE, int OPSEQ, string WorkPiont, string conn)
        {
            string sql = @"
SELECT
	LOT.ITEMCODE,
	iLOT.TransNO as MOCODE,
	iLOT.TransLine AS MOSEQ,
	LOT.LOTNO,
	LOT.ROUTECODE,
	LOT.OPSEQ,
	LOT.OPCODE,
	MAX(iLOT.LOTQTY) AS OriLOTQTY,
	MAX(ISNULL(WIP.GOODQTY,0)) WIPQTY,
	MAX(ISNULL(CHK.Quantity,0)) CHKQTY,
	MAX(ISNULL(CHK.OKQuantity,0)) CHKOK,
	MAX(ISNULL(CHK.NGQuantity,0)) CHKNG,
	SUM(CASE WHEN NCR.Status='让步接受' THEN CONVERT(DECIMAL,ISNULL(CHKDET.SetValue,'0')) ELSE 0 END) NCROK,
	MAX(ISNULL(WIP.FPQTY,0)) FPQTY,
    WIP.ID AS WIPID
FROM dbo.ICSITEMROUTE2OPLot LOT
INNER JOIN dbo.ICSITEMLot iLOT ON iLOT.LotNO=LOT.LotNo AND iLOT.WorkPoint=LOT.WorkPoint
LEFT JOIN dbo.ICSLOTONWIP WIP ON WIP.LOTNO=LOT.LotNo AND WIP.ROUTECODE=LOT.ROUTECODE AND WIP.OPSEQ=LOT.OPSEQ AND WIP.WorkPoint=LOT.WorkPoint
LEFT JOIN  dbo.ICSLOTONWIP B ON B.LOTNO=WIP.LOTNO AND B.ROUTECODE=WIP.ROUTECODE AND B.WorkPoint=WIP.WorkPoint AND B.OPSEQ=(
	SELECT MIN(OPSEQ) FROM dbo.ICSITEMROUTE2OPLot WHERE LotNo=WIP.LOTNO AND ROUTECODE=WIP.ROUTECODE AND OPSEQ>WIP.OPSEQ AND WorkPoint=WIP.WorkPoint
)
LEFT JOIN dbo.ICSLOTONWIPCheck CHK ON CHK.FORTRANID=WIP.ID AND CHK.Enable=1
LEFT JOIN dbo.ICSLOTONWIPCheckDetail CHKDET ON CHKDET.FORTRANID=CHK.ID AND CHKDET.Type='不良'
LEFT JOIN dbo.ICSLOTONWIPCheckNCR NCR ON NCR.CheckID=CHKDET.ID AND NCR.DownDateTime IS NOT NULL
WHERE
	(
        (WIP.ID IS NULL AND LOT.OPSEQ=(SELECT MIN(OPSEQ) FROM dbo.ICSITEMROUTE2OPLot WHERE LotNo=LOT.LOTNO AND ROUTECODE=LOT.ROUTECODE AND WorkPoint=LOT.WorkPoint)) 
		    OR (WIP.ACTIONRESULT='COLLECT_END'	AND CHK.EndDateTime IS NOT NULL AND B.ID IS NULL)
    )
	AND 1=1
	AND LOT.LotNo=@LOTNO
	AND LOT.ROUTECODE=@ROUTECODE
	AND LOT.OPSEQ=@OPSEQ
    AND LOT.WorkPoint=@WorkPiont
GROUP BY
	LOT.ITEMCODE,
	iLOT.TransNO,
	iLOT.TransLine,
	LOT.LOTNO,
	LOT.ROUTECODE,
	LOT.OPSEQ,
	LOT.OPCODE,
    WIP.ID 
HAVING COUNT(CHKDET.ID)=SUM(CASE WHEN NCR.DownDateTime IS NULL THEN 0 ELSE 1 END)
";
            SqlParameter[] pms = new SqlParameter[]{
                new SqlParameter("@LOTNO",SqlDbType.NVarChar){ Value = LOTNO },
                new SqlParameter("@ROUTECODE",SqlDbType.NVarChar){ Value = ROUTECODE },
                new SqlParameter("@OPSEQ",SqlDbType.Int){ Value = OPSEQ },
                new SqlParameter("@WorkPiont",SqlDbType.NVarChar){ Value = WorkPiont }
            };
            return DBHelper.ExecuteDataset(conn, CommandType.Text, sql, pms).Tables[0];
        }

        public static DataTable BaseQuery4RW(string WIPID, string conn)
        {
            string sql = @"

SELECT
	wip.MOCODE,
	wip.MOSEQ,
	WIP.ID WIPID,
	WIP.LOTNO,
	wip.ROUTECODE,
	wip.OPSEQ,
	wip.OPCODE,
	SUM(CASE WHEN NCR.Status='返工' THEN CONVERT(DECIMAL,ISNULL(CHKDET.SetValue,0)) ELSE 0 END)
		+ SUM(CASE WHEN NCR2.Status='返工' THEN CONVERT(DECIMAL,ISNULL(CHKDET2.SetValue,0)) ELSE 0 END) NCR_RWQTY,
	MAX(ISNULL(wip.RWFPQTY,0)) FP_RWQTY,
	MAX(ilot.LOTQTY) AS OriLOTQTY
FROM dbo.ICSLOTONWIP WIP
INNER JOIN dbo.ICSITEMLot ilot ON ilot.LotNO=wip.LOTNO AND ilot.WorkPoint=wip.WorkPoint
LEFT JOIN dbo.ICSLOTONWIPCheck CHK ON CHK.FORTRANID=WIP.ID AND CHK.Enable=1
LEFT JOIN dbo.ICSLOTONWIPCheckDetail CHKDET ON CHKDET.FORTRANID=CHK.ID
LEFT JOIN dbo.ICSLOTONWIPCheckNCR NCR ON NCR.CheckID=CHKDET.ID AND NCR.DownDateTime IS NOT NULL
LEFT JOIN dbo.ICSLOTONWIPDetail wipdet ON wipdet.LOTNO=wip.LOTNO AND wipdet.ROUTECODE=wip.ROUTECODE AND wipdet.OPCODE=wip.OPCODE AND wipdet.WorkPoint=wip.WorkPoint
LEFT JOIN dbo.ICSLOTONWIPInspection ins ON ins.DetailID=wipdet.ID
LEFT JOIN dbo.ICSLOTONWIPCheck CHK2 ON CHK2.FORTRANID=ins.ID AND CHK2.Enable=1
LEFT JOIN dbo.ICSLOTONWIPCheckDetail CHKDET2 ON CHKDET2.FORTRANID=CHK2.ID AND CHKDET2.Type='不良'
LEFT JOIN dbo.ICSLOTONWIPCheckNCR NCR2 ON NCR2.CheckID=CHKDET2.ID AND NCR2.DownDateTime IS NOT NULL
WHERE WIP.ID=@WIPID
GROUP BY
	wip.MOCODE,
	wip.MOSEQ,
	WIP.ID,
	WIP.LOTNO,
	wip.ROUTECODE,
	wip.OPSEQ,
	wip.OPCODE
HAVING 
	SUM(CASE WHEN CHKDET.Type='不良' THEN 1 ELSE 0 END)=SUM(CASE WHEN NCR.DownDateTime IS NULL THEN 0 ELSE 1 END) AND COUNT(CHKDET.ID)>0
	AND SUM(CASE WHEN NCR.Status='返工' THEN CONVERT(DECIMAL,ISNULL(CHKDET.SetValue,0)) ELSE 0 END)
	+ SUM(CASE WHEN NCR2.Status='返工' THEN CONVERT(DECIMAL,ISNULL(CHKDET2.SetValue,0)) ELSE 0 END) > 0

";
            SqlParameter pms = new SqlParameter("@WIPID", SqlDbType.NVarChar) { Value = WIPID };
            return DBHelper.ExecuteDataset(conn, CommandType.Text, sql, pms).Tables[0];
        }

        /// <summary>
        /// 自动返工分批时,生成相关单据 , 方法本身没有自动分批的判断,所以必须符合自动分批时才可调用  .
        /// </summary>
        /// <param name="db"></param>
        /// <param name="wipid">oniwp表id</param>
        /// <param name="rwQty">过程+完工判定返工总数量</param>
        /// <param name="memo">检验不良信息,ncr单号+不良原因+数量 为一组,同组以","隔开. 不同组以"~"隔开</param>
        public static void CreateRWLotNo(FramDataContext db, string wipid, decimal rwQty, string memo, string reason, string StopPRProcess)
        {
            string oldLOTNO, WorkPoint, ROUTECODE, OPCODE = "";
            int OPSEQ = -1;
            int flow = 1;
            string LOTNO = "";
            string WWRWPRLineID = "";
            string EATTRIBUTE7 = "";
            try
            {
                var lineOldwip = db.ICSLOTONWIP.SingleOrDefault(a => a.ID == wipid);
                if (lineOldwip == null)
                {
                    throw new Exception("报工表信息不存在!");
                }
                oldLOTNO = lineOldwip.LOTNO;
                WorkPoint = lineOldwip.WorkPoint;
                ROUTECODE = lineOldwip.ROUTECODE;
                OPSEQ = lineOldwip.OPSEQ;
                OPCODE = lineOldwip.OPCODE;
                var lineOldLot = db.ICSITEMLot.SingleOrDefault(a => a.LotNO == oldLOTNO);
                if (lineOldLot == null)
                {
                    throw new Exception("原条码" + oldLOTNO + "不存在");
                }
                var lineMO2User = db.ICSMO2User.SingleOrDefault(a => a.LOTNO == oldLOTNO && a.RouteCode == ROUTECODE && a.OPCODE == OPCODE && a.WorkPoint == WorkPoint);
                if (lineMO2User == null)
                {
                    throw new Exception("条码" + oldLOTNO + "工序" + OPCODE + "派工信息不存在");
                }
                if (lineMO2User.EATTRIBUTE1 == "1")
                {
                    //当前工序是委外,取本工序PRLineID
                    WWRWPRLineID = lineMO2User.PRLineID;
                    EATTRIBUTE7 = "委外," + reason;
                    var lineRouteop = db.ICSITEMROUTE2OPLot.SingleOrDefault(a => a.LotNo == oldLOTNO && a.ROUTECODE == ROUTECODE && a.OPCODE == OPCODE && a.WorkPoint == WorkPoint);
                    if (lineRouteop == null)
                    {
                        throw new Exception("工艺工序被删除!ICSITEMROUTE2OPLot");
                    }
                    if (lineRouteop.ISWWRW == true)
                    {
                        var lineOP = db.ICSOP.SingleOrDefault(a => a.OPCODE == OPCODE && a.WorkPoint == WorkPoint);
                        if (lineOP == null)
                        {
                            throw new Exception("工艺工序被删除!");
                        }
                        if (lineOP.OPDESC.Contains("退镀"))
                        {
                            //返工退镀,重新赋值为 继承原单子的PRLineID
                            WWRWPRLineID = lineOldLot.WWRWPRLineID;
                        }
                    }
                }
                else
                {
                    //否则继承原单子的PRLineID
                    WWRWPRLineID = lineOldLot.WWRWPRLineID;
                }
                var listLot = db.ICSITEMLot.Where(a => a.EATTRIBUTE1 == oldLOTNO && a.WorkPoint == WorkPoint).ToList();
                if (listLot.Count == 0)
                {
                    flow = 1;
                }
                else
                {
                    flow = listLot.Max(a => Convert.ToInt32(a.LotNO.Substring(a.LotNO.LastIndexOf('-') + 1))) + 1;
                }
                LOTNO = oldLOTNO + "-" + flow.ToString();
                //1.ICSITEMLot新条码
                ICSITEMLot lineNewLot = new ICSITEMLot();
                lineNewLot.ACTIVE = lineOldLot.ACTIVE;
                lineNewLot.Exdate = lineOldLot.Exdate;
                lineNewLot.ItemCode = lineOldLot.ItemCode;
                lineNewLot.PRODUCTDATE = lineOldLot.PRODUCTDATE;
                lineNewLot.tFileName = lineOldLot.tFileName;
                lineNewLot.VenBatch = lineOldLot.VenBatch;
                lineNewLot.VenderLotNO = lineOldLot.VenderLotNO;
                lineNewLot.VENDORCODE = lineOldLot.VENDORCODE;
                lineNewLot.VENDORITEMCODE = lineOldLot.VENDORITEMCODE;
                lineNewLot.VoucherNO = lineOldLot.VoucherNO;
                lineNewLot.VoucherRow = lineOldLot.VoucherRow;
                lineNewLot.TransNO = lineOldLot.TransNO;
                lineNewLot.TransLine = lineOldLot.TransLine;
                lineNewLot.WorkPoint = lineOldLot.WorkPoint;
                lineNewLot.RWOPChanged = false;
                lineNewLot.ID = AppConfig.GetGuid();
                lineNewLot.LotNO = LOTNO;
                lineNewLot.LOTQTY = rwQty;
                lineNewLot.TYPE = "跟踪单分批";
                lineNewLot.EATTRIBUTE1 = oldLOTNO;//原条码
                lineNewLot.EATTRIBUTE2 = lineOldLot.LOTQTY.ToString();//原条码数量
                lineNewLot.EATTRIBUTE3 = null;
                lineNewLot.EATTRIBUTE4 = null;
                lineNewLot.EATTRIBUTE5 = OPSEQ.ToString() + "," + OPCODE;//原条码分批时工序序号+","+工序
                lineNewLot.EATTRIBUTE6 = "0";//被分批时的,已分出数量
                lineNewLot.EATTRIBUTE7 = reason;
                lineNewLot.MTIME = DateTime.Now;
                lineNewLot.MUSER = AppConfig.UserCode;
                lineNewLot.MUSERName = AppConfig.UserName;
                lineNewLot.lastPrintTime = null;
                lineNewLot.lastPrintUSERID = null;
                lineNewLot.PrintTimes = 0;
                lineNewLot.TimesPrint = 0;
                lineNewLot.TwoMTIME = null;
                lineNewLot.TwoMUSER = null;
                lineNewLot.RWMEMO = memo;// EATTRIBUTE7;
                lineNewLot.RWOPChanged = false;
                lineNewLot.WWRWPRLineID = WWRWPRLineID;
                lineNewLot.StopPRProcess = string.IsNullOrEmpty(StopPRProcess) ? lineOldLot.StopPRProcess : StopPRProcess;
                db.ICSITEMLot.InsertOnSubmit(lineNewLot);
                db.SubmitChanges();
                //2.变更原跟踪单原条码数量
                lineOldLot.LOTQTY -= rwQty;
                //2.1变更原跟踪单已分批数量
                lineOldLot.EATTRIBUTE6 = (Convert.ToDecimal(string.IsNullOrEmpty(lineOldLot.EATTRIBUTE6) ? "0" : lineOldLot.EATTRIBUTE6) + rwQty).ToString();
                //2.2报工表记录返工分批数量
                lineOldwip.RWFPQTY = (lineOldwip.RWFPQTY == null ? 0 : lineOldwip.RWFPQTY) + rwQty;
                db.SubmitChanges();

                //3.ICSITEMROUTE2OPLot
                int cNewOpLot = db.ICSITEMROUTE2OPLot.Where(a => a.LotNo == LOTNO).ToList().Count;
                if (cNewOpLot > 0)
                {
                    throw new Exception("新条码" + LOTNO + "已生成工序");
                }
                List<ICSITEMROUTE2OPLot> listOpLot = db.ICSITEMROUTE2OPLot.Where(a => a.LotNo == oldLOTNO && a.OPSEQ > OPSEQ).ToList();
                if (listOpLot.Count == 0)
                {
                    int fpopseq = 9999;
                    ICSITEMROUTE2OPLot v = db.ICSITEMROUTE2OPLot.Single(a => a.LotNo == oldLOTNO && a.OPSEQ == OPSEQ);
                    ICSOP fpop = db.ICSOP.SingleOrDefault(a => a.OPCODE == fpopseq.ToString() && a.WorkPoint == lineOldLot.WorkPoint);
                    if (fpop == null)
                    {
                        throw new Exception("跟踪单最后一道工序分批时,需要专用虚拟工序 ,工序代码9999,此工序未维护");
                    }
                    ICSITEMROUTE2OPLot opLotInfo = new ICSITEMROUTE2OPLot();
                    opLotInfo.CtrlType = "串行";
                    opLotInfo.OPAttr = "自制";
                    opLotInfo.OPSEQ = fpopseq;
                    opLotInfo.OPCODE = fpopseq.ToString();
                    opLotInfo.EATTRIBUTE1 = "0";//如果是分批出的返工跟踪单 0 表示原始状态未进行工序变更,1表示已做了跟踪单工序变更(跟踪单工序变更保存时该值为1)
                    opLotInfo.IDMERGERULE = v.IDMERGERULE;
                    opLotInfo.IDMERGETYPE = v.IDMERGETYPE;
                    opLotInfo.ITEMCODE = v.ITEMCODE;
                    opLotInfo.OPCONTROL = v.OPCONTROL;
                    opLotInfo.OPTIONALOP = v.OPTIONALOP;
                    opLotInfo.ROUTECODE = v.ROUTECODE;
                    opLotInfo.RouteMGR = v.RouteMGR;
                    opLotInfo.WorkPoint = v.WorkPoint;

                    opLotInfo.ID = AppConfig.GetGuid();
                    opLotInfo.LotNo = LOTNO;
                    opLotInfo.MTIME = DateTime.Now;
                    opLotInfo.MUSER = AppConfig.UserCode;
                    opLotInfo.MUSERName = AppConfig.UserName;
                }

                foreach (ICSITEMROUTE2OPLot opLot in listOpLot)
                {
                    ICSITEMROUTE2OPLot opLotInfo = new ICSITEMROUTE2OPLot();
                    opLotInfo.CtrlType = opLot.CtrlType;
                    opLotInfo.EATTRIBUTE1 = "0";
                    opLotInfo.IDMERGERULE = opLot.IDMERGERULE;
                    opLotInfo.IDMERGETYPE = opLot.IDMERGETYPE;
                    opLotInfo.ITEMCODE = opLot.ITEMCODE;
                    opLotInfo.OPAttr = opLot.OPAttr;
                    opLotInfo.OPCODE = opLot.OPCODE;
                    opLotInfo.OPCONTROL = opLot.OPCONTROL;
                    opLotInfo.OPSEQ = opLot.OPSEQ;
                    opLotInfo.OPTIONALOP = opLot.OPTIONALOP;
                    opLotInfo.ROUTECODE = opLot.ROUTECODE;
                    opLotInfo.RouteMGR = opLot.RouteMGR;
                    opLotInfo.WorkPoint = opLot.WorkPoint;
                    opLotInfo.CtrlType = opLot.CtrlType;

                    opLotInfo.ID = AppConfig.GetGuid();
                    opLotInfo.LotNo = LOTNO;
                    opLotInfo.MTIME = DateTime.Now;
                    opLotInfo.MUSER = AppConfig.UserCode;
                    opLotInfo.MUSERName = AppConfig.UserName;
                    db.ICSITEMROUTE2OPLot.InsertOnSubmit(opLotInfo);
                    db.SubmitChanges();
                    var lineMo2User = db.ICSMO2User.SingleOrDefault(a => a.LOTNO == oldLOTNO && a.OPCODE == opLot.OPCODE);
                    if (lineMo2User != null)
                    {
                        ICSMO2User MO2USR = new ICSMO2User();
                        MO2USR.ID = AppConfig.GetGuid();
                        MO2USR.LOTNO = LOTNO;
                        MO2USR.MOCODE = lineMo2User.MOCODE;
                        MO2USR.MOSEQ = lineMo2User.MOSEQ;
                        MO2USR.SEGCODE = lineMo2User.SEGCODE;
                        MO2USR.RouteCode = lineMo2User.RouteCode;
                        MO2USR.OPCODE = lineMo2User.OPCODE;
                        MO2USR.USERCODE = lineMo2User.USERCODE;
                        MO2USR.USERName = lineMo2User.USERName;
                        MO2USR.EQPCode = lineMo2User.EQPCode;
                        MO2USR.EQPName = lineMo2User.EQPName;
                        MO2USR.MUSER = lineMo2User.MUSER;
                        MO2USR.MUSERName = lineMo2User.MUSERName;
                        MO2USR.MTIME = Convert.ToDateTime(lineMo2User.MTIME);
                        MO2USR.WorkPoint = lineMo2User.WorkPoint;
                        MO2USR.EATTRIBUTE1 = lineMo2User.EATTRIBUTE1;
                        MO2USR.StartPlanDate = lineMo2User.StartPlanDate;
                        MO2USR.EndPlanDate = lineMo2User.EndPlanDate;
                        MO2USR.PRLineID = null;
                        db.ICSMO2User.InsertOnSubmit(MO2USR);
                        List<ICSMO2UserDetail> lineMUDetail = db.ICSMO2UserDetail.Where(a => a.ID == lineMo2User.ID).ToList();
                        for (int i = 0; i < lineMUDetail.Count; i++)
                        {
                            ICSMO2UserDetail info = new ICSMO2UserDetail();
                            info.ID = MO2USR.ID;
                            info.DID = AppConfig.GetGuid();
                            info.EQPID = lineMUDetail[i].EQPID;
                            info.AVAILABLE = lineMUDetail[i].AVAILABLE;
                            info.PLANEND = lineMUDetail[i].PLANEND;
                            info.PLANSTART = lineMUDetail[i].PLANSTART;
                            info.PLANQTY = lineMUDetail[i].PLANQTY;
                            info.PLANTIME = lineMUDetail[i].PLANTIME;
                            db.ICSMO2UserDetail.InsertOnSubmit(info);
                            db.SubmitChanges();
                        }
                        db.SubmitChanges();
                    }
                }
                db.SubmitChanges();
            }
            catch (Exception ex)
            {
                throw new Exception("返工分批(自动)出错: \r\n" + ex.Message);
            }
        }

        public static void CreateRWLotNo(SqlCommand cmd, string wipid, decimal rwQty, string memo, string reason, string StopPRProcess, DateTime timeNow, string UserCode, string UserName)
        {
            string oldLOTNO, WorkPoint, ROUTECODE, OPCODE = "";
            int OPSEQ = -1;
            string LOTNO = "";
            try
            {
                string sql = "SELECT * FROM ICSLOTONWIP WHERE ID='" + wipid + "'";
                DataTable dtOldwip = U8Helper.ExecuteDataTable(sql, cmd);
                if (dtOldwip.Rows.Count == 0)
                {
                    throw new Exception("报工表信息不存在!");
                }
                oldLOTNO = dtOldwip.Rows[0]["LOTNO"].ToString();
                WorkPoint = dtOldwip.Rows[0]["WorkPoint"].ToString();
                ROUTECODE = dtOldwip.Rows[0]["ROUTECODE"].ToString();
                OPSEQ = Convert.ToInt32(dtOldwip.Rows[0]["OPSEQ"]);
                OPCODE = dtOldwip.Rows[0]["OPCODE"].ToString();
                //PRLineID 取值
                //1.如果是正常委外,返工非退镀委外 取自身
                //2.如果是自制,返工退镀委外       继承父跟踪单
                string IsWW = "0";
                string PRLineID_Current = "";
                string PRLineID_Inherit = "";
                string WWRWPRLineID = "";
                string StopPRProcess_Current = "";
                string StopPRProcess_Inherit = "";

                sql = "SELECT * FROM ICSITEMLot WHERE LotNO='{0}' AND WorkPoint='{1}'";
                sql = string.Format(sql, oldLOTNO, WorkPoint);
                DataTable dtOldLot = U8Helper.ExecuteDataTable(sql, cmd);
                if (dtOldLot.Rows.Count == 0)
                {
                    throw new Exception("原条码" + oldLOTNO + "不存在");
                }
                PRLineID_Inherit = dtOldLot.Rows[0]["WWRWPRLineID"].ToString();
                StopPRProcess_Inherit = dtOldLot.Rows[0]["StopPRProcess"].ToString();
                if (string.IsNullOrEmpty(StopPRProcess))
                {
                    StopPRProcess_Current = StopPRProcess_Inherit;
                }
                else
                {
                    StopPRProcess_Current = StopPRProcess;
                }
                sql = "SELECT EATTRIBUTE1,PRLineID FROM dbo.ICSMO2User WHERE LOTNO='{0}' AND RouteCode='{1}' AND OPCODE='{2}' AND WorkPoint='{3}'";
                sql = string.Format(sql, LOTNO, ROUTECODE, OPCODE, WorkPoint);
                DataTable dtMo2usr = U8Helper.ExecuteDataTable(sql, cmd);
                if (dtMo2usr.Rows.Count == 0)
                {
                    throw new Exception("原条码" + oldLOTNO + "派工信息不存在");
                }
                IsWW = dtMo2usr.Rows[0]["EATTRIBUTE1"].ToString();
                PRLineID_Current = dtMo2usr.Rows[0]["PRLineID"].ToString();
                if (IsWW == "1")
                {
                    //当前工序是委外,取本工序PRLineID
                    WWRWPRLineID = PRLineID_Current;
                    sql = @"
SELECT 
	ISNULL(A.ISWWRW,0) as ISWWRW,
	B.OPDESC
FROM ICSITEMROUTE2OPLot A 
INNER JOIN dbo.ICSOP B ON B.OPCODE=A.OPCODE AND B.WorkPoint=A.WorkPoint
WHERE A.LotNo='{0}' AND A.ROUTECODE='{1}' AND A.OPCODE='{2}' AND A.WorkPoint='{3}'
";
                    sql = string.Format(sql, LOTNO, ROUTECODE, OPCODE, WorkPoint);
                    DataTable dtRouteOP = U8Helper.ExecuteDataTable(sql, cmd);
                    if (dtRouteOP.Rows.Count == 0)
                    {
                        throw new Exception("原条码" + oldLOTNO + "工艺工序信息不存在," + ROUTECODE + "," + OPCODE);
                    }
                    bool ISWWRW = Convert.ToBoolean(dtRouteOP.Rows[0]["ISWWRW"]);
                    string OPDESC = dtRouteOP.Rows[0]["OPDESC"].ToString();
                    if (ISWWRW == true)
                    {
                        if (OPDESC.Contains("退镀"))
                        {
                            //返工退镀,重新赋值为 继承原单子的PRLineID
                            WWRWPRLineID = PRLineID_Inherit;
                        }
                    }
                }
                else
                {
                    //否则继承原单子的PRLineID
                    WWRWPRLineID = PRLineID_Inherit;
                }

                int flow = 1;
                sql = "SELECT * FROM ICSITEMLot WHERE EATTRIBUTE1='{0}' AND WorkPoint='{1}''";
                sql = string.Format(sql, oldLOTNO, WorkPoint);
                DataTable listLot = U8Helper.ExecuteDataTable(sql, cmd);
                if (listLot.Rows.Count == 0)
                {
                    flow = 1;
                }
                else
                {
                    flow = listLot.Select().Max(a => Convert.ToInt32(a.Field<string>("LotNO").Substring(a.Field<string>("LotNO").LastIndexOf('-') + 1))) + 1;
                }
                LOTNO = oldLOTNO + "-" + flow.ToString();

                //1.ICSITEMLot新条码
                #region Sql-插入新条码
                sql = @"
INSERT INTO [dbo].[ICSITEMLot](
            [ID]
           ,[LotNO]
           ,[ItemCode]
           ,[TransNO]
           ,[TransLine]
           ,[VENDORITEMCODE]
           ,[VENDORCODE]
           ,[VenderLotNO]
           ,[PRODUCTDATE]
           ,[LOTQTY]
           ,[ACTIVE]
           ,[Exdate]
           ,[WorkPoint]
           ,[MUSER]
           ,[MUSERName]
           ,[MTIME]
           ,[EATTRIBUTE1]
           ,[TYPE]
           ,[EATTRIBUTE2]
           ,[EATTRIBUTE3]
           ,[EATTRIBUTE4]
           ,[EATTRIBUTE5]
           ,[PrintTimes]
           ,[lastPrintUSERID]
           ,[lastPrintTime]
           ,[VoucherNO]
           ,[VoucherRow]
           ,[TwoMUSER]
           ,[TwoMTIME]
           ,[VenBatch]
           ,[EATTRIBUTE6]
           ,[EATTRIBUTE7]
           ,[tFileName]
           ,[TimesPrint]
           ,[RWMEMO]
           ,[RWOPChanged]
           ,[WWRWPRLineID]
           ,[StopPRProcess])
     SELECT
			NEWID()
		   ,@LotNO
           ,[ItemCode]
           ,[TransNO]
           ,[TransLine]
           ,[VENDORITEMCODE]
           ,[VENDORCODE]
           ,[VenderLotNO]
           ,[PRODUCTDATE]
           ,@LOTQTY
           ,'Y'
           ,[Exdate]
           ,[WorkPoint]
           ,@MUSER
           ,@MUSERName
           ,@MTIME
           ,@EATTRIBUTE1
           ,@TYPE
           ,@EATTRIBUTE2
           ,[EATTRIBUTE3]
           ,[EATTRIBUTE4]
           ,@EATTRIBUTE5
           ,0
           ,null
           ,null
           ,[VoucherNO]
           ,[VoucherRow]
           ,[TwoMUSER]
           ,[TwoMTIME]
           ,[VenBatch]
           ,@EATTRIBUTE6
           ,@EATTRIBUTE7
           ,[tFileName]
           ,[TimesPrint]
           ,@RWMEMO
           ,@RWOPChanged
           ,@WWRWPRLineID
           ,@StopPRProcess
FROM dbo.ICSITEMLot WHERE LotNO=@oldLOTNO
";
                #endregion
                cmd.Parameters.Clear();
                SqlParameter[] pms = {
                    new SqlParameter("@oldLOTNO",DbType.String){ Value=oldLOTNO },
                    new SqlParameter("@LotNO",DbType.String){ Value=LOTNO },
                    new SqlParameter("@LOTQTY",DbType.Decimal){ Value=rwQty },
                    new SqlParameter("@TYPE",DbType.String){ Value="跟踪单分批" },
                    new SqlParameter("@EATTRIBUTE1",DbType.String){ Value=oldLOTNO },//父(原)跟踪单
                    new SqlParameter("@EATTRIBUTE2",DbType.String){ Value=dtOldLot.Rows[0]["LOTQTY"].ToString() },//父(原)跟踪单的数量
                    new SqlParameter("@EATTRIBUTE5",DbType.String){ Value=OPSEQ.ToString() + "," + OPCODE },//在哪个工序进行的分批
                    new SqlParameter("@EATTRIBUTE6",DbType.String){ Value="0" },//作为父条码时被分出的数量
                    new SqlParameter("@EATTRIBUTE7",DbType.String){ Value= reason },//类型'返工'
                    new SqlParameter("@MTIME",DbType.DateTime){ Value=timeNow },
                    new SqlParameter("@MUSER",DbType.String){ Value=UserCode },
                    new SqlParameter("@MUSERName",DbType.String){ Value=UserName },
                    new SqlParameter("@RWMEMO",DbType.String){ Value=memo },//返工备注: 不良
                    new SqlParameter("@RWOPChanged",DbType.Boolean){ Value=false },//条码生成后,是否变更了 工艺工序
                    new SqlParameter("@WWRWPRLineID",DbType.String){ Value=string.IsNullOrEmpty(WWRWPRLineID)?"":WWRWPRLineID },
                    new SqlParameter("@StopPRProcess",DbType.String){ Value=string.IsNullOrEmpty(StopPRProcess_Current)?"":StopPRProcess_Current  }
                };
                cmd.Parameters.AddRange(pms);
                U8Helper.ExecuteNonQuery(sql, cmd);
                //2.0变更原跟踪单原条码数量,2.1变更原跟踪单已分批数量
                sql = @"UPDATE dbo.ICSITEMLot SET LOTQTY-={0}, EATTRIBUTE6=CONVERT(DECIMAL,ISNULL(EATTRIBUTE6,'0'))+{0}    WHERE LotNO='{1}'";
                sql = string.Format(sql, rwQty, oldLOTNO);
                U8Helper.ExecuteNonQuery(sql, cmd);
                //2.2报工表记录返工分批数量
                sql = "UPDATE ICSLOTONWIP SET RWFPQTY=ISNULL(RWFPQTY,'0')+{0}   WHERE ID='{1}'";
                sql = string.Format(sql, rwQty, wipid);
                U8Helper.ExecuteNonQuery(sql, cmd);

                //3.ICSITEMROUTE2OPLot
                sql = "SELECT 1 FROM ICSITEMROUTE2OPLot WHERE LotNo='{0}'";
                sql = string.Format(sql, rwQty, wipid);
                int cNewOpLot = U8Helper.ExecuteDataTable(sql, cmd).Rows.Count;
                if (cNewOpLot > 0)
                {
                    throw new Exception("新条码" + LOTNO + "已生成工序");
                }
                #region Sql- 工艺工序下发
                sql = @"
IF NOT EXISTS(SELECT 1 FROM ICSITEMROUTE2OPLot WHERE LotNo=@oldLOTNO AND ROUTECODE=@ROUTECODE AND OPSEQ>@OPSEQ)
BEGIN
	INSERT INTO [dbo].[ICSITEMROUTE2OPLot]
			   ([ITEMCODE]
			   ,[ROUTECODE]
			   ,[OPCODE]
			   ,[OPSEQ]
			   ,[OPCONTROL]
			   ,[OPTIONALOP]
			   ,[IDMERGETYPE]
			   ,[IDMERGERULE]
			   ,[MUSER]
			   ,[MUSERName]
			   ,[MTIME]
			   ,[WorkPoint]
			   ,[EATTRIBUTE1]
			   ,[LotNo]
			   ,[ID]
			   ,[OPAttr]
			   ,[CtrlType]
			   ,[RouteMGR]
			   ,[OPLVL]
			   ,[EQPCODE]
			   ,[WTIME]
			   ,[RTIME]
			   ,[STIME])
     SELECT
                [ITEMCODE]
			   ,[ROUTECODE]
			   ,'9999'
			   ,9999
			   ,[OPCONTROL]
			   ,[OPTIONALOP]
			   ,[IDMERGETYPE]
			   ,[IDMERGERULE]
			   ,@MUSER
			   ,@MUSERName
			   ,@MTIME
			   ,[WorkPoint]
			   ,[EATTRIBUTE1]
			   ,@LOTNO
			   ,NEWID()
			   ,'自制'
			   ,'串行'
			   ,[RouteMGR]
			   ,[OPLVL]
			   ,[EQPCODE]
			   ,[WTIME]
			   ,[RTIME]
			   ,[STIME]
	FROM ICSITEMROUTE2OPLot WHERE LotNo=@oldLOTNO AND ROUTECODE=@ROUTECODE AND OPSEQ=@OPSEQ
END
ELSE
BEGIN
	INSERT INTO [dbo].[ICSITEMROUTE2OPLot]
			   ([ITEMCODE]
			   ,[ROUTECODE]
			   ,[OPCODE]
			   ,[OPSEQ]
			   ,[OPCONTROL]
			   ,[OPTIONALOP]
			   ,[IDMERGETYPE]
			   ,[IDMERGERULE]
			   ,[MUSER]
			   ,[MUSERName]
			   ,[MTIME]
			   ,[WorkPoint]
			   ,[EATTRIBUTE1]
			   ,[LotNo]
			   ,[ID]
			   ,[OPAttr]
			   ,[CtrlType]
			   ,[RouteMGR]
			   ,[OPLVL]
			   ,[EQPCODE]
			   ,[WTIME]
			   ,[RTIME]
			   ,[STIME])
     SELECT
                [ITEMCODE]
			   ,[ROUTECODE]
			   ,[OPCODE]
			   ,[OPSEQ]
			   ,[OPCONTROL]
			   ,[OPTIONALOP]
			   ,[IDMERGETYPE]
			   ,[IDMERGERULE]
			   ,@MUSER
			   ,@MUSERName
			   ,@MTIME
			   ,[WorkPoint]
			   ,[EATTRIBUTE1]
			   ,@LOTNO
			   ,NEWID()
			   ,[OPAttr]
			   ,[CtrlType]
			   ,[RouteMGR]
			   ,[OPLVL]
			   ,[EQPCODE]
			   ,[WTIME]
			   ,[RTIME]
			   ,[STIME]
	FROM ICSITEMROUTE2OPLot WHERE LotNo=@oldLOTNO AND ROUTECODE=@ROUTECODE AND OPSEQ>@OPSEQ
END
";
                #endregion
                cmd.Parameters.Clear();
                SqlParameter[] pms2 = {
                    new SqlParameter("@oldLOTNO",DbType.String){ Value=oldLOTNO },
                    new SqlParameter("@LotNO",DbType.String){ Value=LOTNO },
                    new SqlParameter("@ROUTECODE",DbType.Decimal){ Value=ROUTECODE },
                    new SqlParameter("@OPSEQ",DbType.Int32){ Value=OPSEQ },
                    new SqlParameter("@MTIME",DbType.DateTime){ Value=timeNow },
                    new SqlParameter("@MUSER",DbType.String){ Value=UserCode },
                    new SqlParameter("@MUSERName",DbType.String){ Value=UserName }
                };
                cmd.Parameters.AddRange(pms2);
                U8Helper.ExecuteNonQuery(sql, cmd);

                #region Sql- 后续工序派工信息继承原跟踪单
                sql = @"
INSERT INTO [dbo].[ICSMO2User]
           ([ID]
           ,[MOCODE]
           ,[MOSEQ]
           ,[LOTNO]
           ,[SEGCODE]
           ,[RouteCode]
           ,[OPCODE]
           ,[USERCODE]
           ,[USERName]
           ,[EQPCode]
           ,[EQPName]
           ,[StartPlanDate]
           ,[EndPlanDate]
           ,[MUSER]
           ,[MUSERName]
           ,[MTIME]
           ,[WorkPoint]
           ,[EATTRIBUTE1]
           ,[PRLineID]
           ,[PRQTY])
     SELECT
			NEWID()
           ,B.[MOCODE]
           ,B.[MOSEQ]
           ,@LOTNO
           ,B.[SEGCODE]
           ,B.[RouteCode]
           ,B.[OPCODE]
           ,B.[USERCODE]
           ,B.[USERName]
           ,B.[EQPCode]
           ,B.[EQPName]
           ,B.[StartPlanDate]
           ,B.[EndPlanDate]
           ,B.[MUSER]
           ,B.[MUSERName]
           ,B.[MTIME]
           ,B.[WorkPoint]
           ,B.[EATTRIBUTE1]
           ,B.[PRLineID]
           ,B.[PRQTY]
FROM [dbo].[ICSITEMROUTE2OPLot] A
INNER JOIN [dbo].[ICSMO2User]  B ON B.LotNo=A.LOTNO AND B.OPCODE=A.OPCODE AND B.WorkPoint=A.WorkPoint
WHERE A.LOTNO =@oldLOTNO AND A.ROUTECODE=@ROUTECODE AND A.WorkPoint=@WorkPoint AND A.OPSEQ>@OPSEQ

INSERT INTO [dbo].[ICSMO2UserDetail]
           ([ID]
           ,[DID]
           ,[EQPID]
           ,[PLANQTY]
           ,[PLANSTART]
           ,[PLANEND]
           ,[PLANTIME]
           ,[AVAILABLE])
SELECT
			B.[ID]
           ,NEWID()
           ,C.[EQPID]
           ,C.[PLANQTY]
           ,C.[PLANSTART]
           ,C.[PLANEND]
           ,C.[PLANTIME]
           ,C.[AVAILABLE]
FROM [dbo].[ICSITEMROUTE2OPLot] A
INNER JOIN [dbo].[ICSMO2User] B  ON A.LotNo=B.LOTNO AND A.OPCODE=B.OPCODE AND A.WorkPoint=B.WorkPoint
INNER JOIN (
	SELECT A.LOTNO,A.RouteCode,A.OPCODE,A.WorkPoint,B.PLANQTY,B.EQPID,B.PLANSTART,B.PLANEND,B.PLANTIME,B.AVAILABLE 
	FROM [dbo].[ICSMO2User] A
	INNER JOIN [dbo].[ICSMO2UserDetail] B ON B.ID=A.ID
	WHERE A.LOTNO=@oldLOTNO AND A.RouteCode=@ROUTECODE AND A.OPCODE=@OPCODE AND A.WorkPoint=@WorkPoint
) C ON C.RouteCode=B.RouteCode AND C.OPCODE=B.OPCODE AND C.WorkPoint=B.WorkPoint
WHERE A.LOTNO =@LOTNO AND A.ROUTECODE=@ROUTECODE AND A.WorkPoint=@WorkPoint AND A.OPSEQ>@OPSEQ

";
                #endregion
                cmd.Parameters.Clear();
                SqlParameter[] pms3 = {
                    new SqlParameter("@oldLOTNO",DbType.String){ Value=oldLOTNO },
                    new SqlParameter("@LotNO",DbType.String){ Value=LOTNO },
                    new SqlParameter("@ROUTECODE",DbType.Decimal){ Value=ROUTECODE },
                    new SqlParameter("@OPSEQ",DbType.Int32){ Value=OPSEQ },
                    new SqlParameter("@OPCODE",DbType.Int32){ Value=OPCODE }
                };
                cmd.Parameters.AddRange(pms3);
                U8Helper.ExecuteNonQuery(sql, cmd);
            }
            catch (Exception ex)
            {
                throw new Exception("返工分批(自动)出错: \r\n" + ex.Message);
            }
        }

        /// <summary>
        /// 对于委外工序批量退货
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="WIPID"></param>
        public static void ResetWW(SqlCommand cmd, string WIPID)
        {
            cmd.Parameters.Clear();
            string WorkPoint = "";
            string ITEMCODE = "";
            string LOTNO = "";
            string LOTSEQ = "";
            string ROUTECODE = "";
            string OPCODE = "";
            int OPSEQ = -1;
            string PRLineID = "";
            int BKSEQ = 1;
            //
            string sql = @"
SELECT
	A.ITEMCODE,A.LOTNO,A.LOTSEQ,A.ROUTECODE,A.OPSEQ,A.OPCODE,A.WorkPoint,
	B.PRLineID
FROM dbo.ICSLOTONWIP A
LEFT JOIN dbo.ICSMO2User B ON B.LOTNO=A.LOTNO AND B.RouteCode=A.ROUTECODE AND B.OPCODE=A.OPCODE 
WHERE A.ID='{0}'";
            sql = string.Format(sql, WIPID);
            DataTable dtOnwip = U8Helper.ExecuteDataTable(sql, cmd);
            if (dtOnwip.Rows.Count == 0)
            {
                throw new Exception("报工主表数据不存在" + WIPID);
            }
            WorkPoint = dtOnwip.Rows[0]["WorkPoint"].ToString();
            ITEMCODE = dtOnwip.Rows[0]["ITEMCODE"].ToString();
            LOTNO = dtOnwip.Rows[0]["LOTNO"].ToString();
            LOTSEQ = dtOnwip.Rows[0]["LOTSEQ"].ToString();
            OPCODE = dtOnwip.Rows[0]["OPCODE"].ToString();
            OPSEQ = Convert.ToInt32(dtOnwip.Rows[0]["OPSEQ"]);
            PRLineID = dtOnwip.Rows[0]["PRLineID"].ToString();
            //0.获取序号
            sql = @"SELECT ISNULL(MAX(BKSEQ),0)+1 FROM  [dbo].[ICSLOTONWIP_BK] WHERE ID='{0}'";
            sql = string.Format(sql, WIPID);
            BKSEQ = Convert.ToInt32(U8Helper.ExecuteDataTable(sql, cmd).Rows[0][0]);
            //1.删除排序表,不备份
            sql = @"DELETE FROM dbo.ICSLOTONWIPCheckTop WHERE FORTRANID='{0}'";
            sql = string.Format(sql, WIPID);
            U8Helper.ExecuteNonQuery(sql, cmd);
            //2.获取检验相关表id
            sql = @"
SELECT DISTINCT
	WIP.ID AS WIPID,
	CHK.ID AS CHKID,
	CHKDET.ID AS DETID,
	NCR.ID AS NCRID
FROM dbo.ICSLOTONWIP WIP
LEFT JOIN dbo.ICSLOTONWIPCheck CHK ON CHK.FORTRANID=WIP.ID
LEFT JOIN dbo.ICSLOTONWIPCheckDetail CHKDET ON CHKDET.FORTRANID=CHK.ID
LEFT JOIN dbo.ICSLOTONWIPCheckNCR NCR ON NCR.CheckID=CHKDET.ID
WHERE WIP.ID='{0}'";
            sql = string.Format(sql, WIPID);
            DataTable dt = U8Helper.ExecuteDataTable(sql, cmd);
            string CHKID = dt.Rows[0]["CHKID"].ToString();
            foreach (DataRow dr in dt.Rows)
            {
                string NCRID = dr["NCRID"].ToString();
                string DETID = dr["DETID"].ToString();
                //备份.删除ICSLOTONWIPCheckNCR
                sql = @"
INSERT INTO [dbo].[ICSLOTONWIPCheckNCR_BK]  SELECT {0},* FROM [dbo].[ICSLOTONWIPCheckNCR] WHERE ID='{1}'
DELETE FROM [dbo].[ICSLOTONWIPCheckNCR] WHERE ID='{1}'
";
                sql = string.Format(sql, BKSEQ, NCRID);
                U8Helper.ExecuteNonQuery(sql, cmd);
                //备份.删除ICSLOTONWIPCheckDetail
                sql = @"
INSERT INTO [dbo].[ICSLOTONWIPCheckDetail_BK]  SELECT {0},* FROM [dbo].[ICSLOTONWIPCheckDetail] WHERE ID='{1}'
DELETE FROM [dbo].[ICSLOTONWIPCheckDetail] WHERE ID='{1}'
";
                sql = string.Format(sql, BKSEQ, DETID);
                U8Helper.ExecuteNonQuery(sql, cmd);
            }
            //删除ICSLOTONWIPCheckPause,不备份
            sql = @"DELETE  FROM ICSLOTONWIPCheckPause WHERE CheckID='{0}'";
            sql = string.Format(sql, CHKID);
            U8Helper.ExecuteNonQuery(sql, cmd);
            //备份.删除ICSLOTONWIPCheck
            sql = @"
INSERT INTO [dbo].[ICSLOTONWIPCheck_BK]  SELECT {0},* FROM [dbo].[ICSLOTONWIPCheck] WHERE ID='{1}'
DELETE FROM [dbo].[ICSLOTONWIPCheck] WHERE ID='{1}'
";
            sql = string.Format(sql, BKSEQ, CHKID);
            U8Helper.ExecuteNonQuery(sql, cmd);
            //恢复ICSPO_PoMain数量INQty
            sql = @"
DECLARE @POCode NVARCHAR(50),@PORow NVARCHAR(50),@TransQTY DECIMAL(18,6)
SELECT  TOP 1 @POCode=TransNO,@PORow=TransLine, @TransQTY=ISNULL(TransQTY,0) FROM dbo.ICSWareHouseLotInfoLog WHERE LotNO='{0}' AND ITEMCODE='{1}' AND BusinessCode='委外发料' AND EATTRIBUTE1='{2}'   AND WorkPoint='{3}'
UPDATE ICSPO_PoMain SET INQty=INQty-@TransQTY WHERE POCode=@POCode AND PORow=@PORow AND Free1='{2}' AND WorkPoint='{3}'
";
            sql = string.Format(sql, LOTNO, ITEMCODE + "_" + OPCODE, PRLineID, WorkPoint);
            U8Helper.ExecuteNonQuery(sql, cmd);
            //删除ICSWareHouseLotInfoLog,不备份
            sql = @"DELETE FROM dbo.ICSWareHouseLotInfoLog WHERE LotNO='{0}' AND ITEMCODE='{1}' AND (BusinessCode='委外发料' OR BusinessCode='委外收料' ) AND EATTRIBUTE1='{2}' AND WorkPoint='{3}'";
            sql = string.Format(sql, LOTNO, ITEMCODE + "_" + OPCODE, PRLineID, WorkPoint);
            U8Helper.ExecuteNonQuery(sql, cmd);
            //备份.删除ICSLOTONWIP
            sql = @"
INSERT INTO [dbo].[ICSLOTONWIP_BK]  SELECT {0},* FROM [dbo].[ICSLOTONWIP] WHERE ID='{1}'
DELETE FROM [dbo].[ICSLOTONWIP] WHERE ID='{1}'
";
            sql = string.Format(sql, BKSEQ, WIPID);
            U8Helper.ExecuteNonQuery(sql, cmd);
            //删除ICSLOTSIMULATION(如果此委外工序是第一道工序)
            //跟踪单取消结束状态,ICSLOTSIMULATION (如果此委外工序是最后一道工序)
            sql = @"SELECT MIN(OPSEQ),MAX(OPSEQ)  FROM dbo.ICSITEMROUTE2OPLot WHERE LotNo='{0}' AND ROUTECODE='{1}' AND WorkPoint='{2}' ";
            sql = string.Format(sql, LOTNO, ROUTECODE, WorkPoint);
            DataTable dtOPSEQ = U8Helper.ExecuteDataTable(sql, cmd);
            int minOPSEQ = Convert.ToInt32(dtOPSEQ.Rows[0][0]);
            int maxOPSEQ = Convert.ToInt32(dtOPSEQ.Rows[0][1]);
            if (maxOPSEQ == OPSEQ)
            {
                sql = @"UPDATE dbo.ICSLOTSIMULATION SET ISCOM='0' WHERE LOTNO='{0}' AND LOTSEQ='{1}' AND WorkPoint='{2}'";
                sql = string.Format(sql, LOTNO, LOTSEQ, WorkPoint);
                U8Helper.ExecuteNonQuery(sql, cmd);
            }
            if (minOPSEQ == OPSEQ)
            {
                sql = @"DELETE FROM dbo.ICSLOTSIMULATION WHERE LOTNO='{0}' AND LOTSEQ='{1}' AND WorkPoint='{2}'";
                sql = string.Format(sql, LOTNO, LOTSEQ, WorkPoint);
                U8Helper.ExecuteNonQuery(sql, cmd);
            }

        }

        private static class U8Helper
        {
            public static DataTable ExecuteDataTable(string sql, SqlCommand cmd)
            {
                DataTable dt = new DataTable();
                SqlDataAdapter dr = new System.Data.SqlClient.SqlDataAdapter();
                cmd.CommandText = sql;
                dr.SelectCommand = cmd;
                dr.Fill(dt);
                return dt;
            }

            public static bool ExecuteNonQuery(string sql, SqlCommand cmd)
            {
                try
                {
                    cmd.CommandText = sql;
                    int result = cmd.ExecuteNonQuery();
                    if (result > 0)
                    {
                        return true;
                    }
                    else
                    {

                        return false;
                    }
                }
                catch (Exception ex)
                {
                    string Params = string.Empty;
                    foreach (SqlParameter parameter in cmd.Parameters)
                    {
                        Params += parameter.SqlValue + "||";
                    }

                    throw new Exception("程序异常！");
                }
            }
        }
    }
}