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

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;
using System.Configuration;
using System.IO;
using ICSSoft.Frame.Data.Entity.NcApiEntity;
using Newtonsoft.Json;

namespace ICSSoft.Frame.Data.DAL
{

    public class ICSMO2UserDAL
    {

        #region 增加修改
        public static void Add(List<FormICSMO2UserUIModel> MO2UserInfoList, string dsconn)
        {
            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                foreach (FormICSMO2UserUIModel MO2UserInfo in MO2UserInfoList)
                {

                    bool isNew = false;
                    var line = db.ICSMO2User.SingleOrDefault(a => a.LOTNO == MO2UserInfo.LOTNO && a.OPCODE == MO2UserInfo.OPCODE);
                    //var line = db.ICSMO2User.SingleOrDefault(a => a.LOTNO == MO2UserInfo.LOTNO );

                    if (line == null)
                    {
                        isNew = true;
                        line = new ICSMO2User();
                        line.ID = AppConfig.GetGuid();
                    }
                    line.MOCODE = MO2UserInfo.MOCODE;
                    line.MOSEQ = MO2UserInfo.MOSEQ;
                    line.LOTNO = MO2UserInfo.LOTNO;
                    line.SEGCODE = MO2UserInfo.SEGCODE;
                    line.RouteCode = MO2UserInfo.RouteCode;
                    line.OPCODE = MO2UserInfo.OPCODE;
                    line.USERCODE = MO2UserInfo.USERCODE;
                    line.USERName = MO2UserInfo.USERName;
                    line.EQPCode = MO2UserInfo.EQPCode;
                    line.EQPName = MO2UserInfo.EQPName;
                    line.MUSER = MO2UserInfo.MUSER;
                    line.MUSERName = MO2UserInfo.MUSERName;
                    line.MTIME = Convert.ToDateTime(MO2UserInfo.MTIME);
                    line.WorkPoint = MO2UserInfo.WorkPoint;
                    line.EATTRIBUTE1 = MO2UserInfo.EATTRIBUTE1;
                    line.StartPlanDate = MO2UserInfo.StartPlanDate;
                    line.EndPlanDate = MO2UserInfo.EndPlanDate;
                    if (MO2UserInfo.PRLineID == "False" || MO2UserInfo.PRLineID == "0" || string.IsNullOrWhiteSpace(MO2UserInfo.PRLineID))
                    {
                        line.PRLineID = null;
                    }
                    else
                    {
                        line.PRLineID = MO2UserInfo.PRLineID;
                    }

                    if (isNew)
                        db.ICSMO2User.InsertOnSubmit(line);
                    //log
                    db.SubmitChanges();
                }

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

        #region 删除
        public static void Delete(List<string> idList, string dsconn)
        {
            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                var lines = db.ICSMO2User.Where(a => idList.Contains(a.ID));
                db.ICSMO2User.DeleteAllOnSubmit(lines);
                db.SubmitChanges();
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw ex;
            }
            finally
            {
                db.Connection.Close();
            }
        }

        #endregion

        #region 取消下发
        public static void cancelSend(List<string> codeList, string dsconn)
        {
            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                var lotonwip = db.ICSLOTONWIP.Where(a => codeList.Contains(a.LOTNO));
                if (lotonwip != null && lotonwip.ToList().Count > 0)
                {
                    throw new Exception("批次已经采集，不可取消派工");
                }
                var lines = db.ICSMO2User.Where(a => codeList.Contains(a.LOTNO));
                db.ICSMO2User.DeleteAllOnSubmit(lines);
                db.SubmitChanges();
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw ex;
            }

        }
        #endregion

        public static void cancelSend(List<string> lotno1, List<string> opcode1, string dsconn)
        {
            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {

                var lotonwip1 = (from a in db.ICSLOTONWIP
                                 where lotno1.Contains(a.LOTNO)
                                 select a).ToList();
                var lotonwip = lotonwip1.Where(a => opcode1.Contains(a.OPCODE));
                if (lotonwip != null && lotonwip.ToList().Count > 0)
                {
                    throw new Exception("批次已经采集，不可取消派工");
                }
                List<ICSMO2User> list = new List<ICSMO2User>();
                for (int i = 0; i < lotno1.Count(); i++)
                {
                    ICSMO2User[] b = (from a in db.ICSMO2User
                                      where a.LOTNO.Equals(lotno1[i]) && a.OPCODE.Equals(opcode1[i])
                                      select a).ToArray();
                    list.Add(b[0]);
                }

                db.ICSMO2User.DeleteAllOnSubmit(list);
                db.SubmitChanges();
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw ex;
            }

        }


        #region 写入日志
        public static void WriteLogFile(string input, string txtName)
        {
            try
            {
                string logAdress = ConfigurationManager.AppSettings["logAdress"].ToString() + "\\Log\\";
                if (!System.IO.Directory.Exists(logAdress))
                {
                    System.IO.Directory.CreateDirectory(logAdress);//不存在就创建目录   
                }

                string adress = logAdress + txtName;
                if (!System.IO.Directory.Exists(adress))
                {
                    System.IO.Directory.CreateDirectory(adress);//不存在就创建目录   
                }


                // string logAdress = ConfigurationManager.AppSettings["logAdress"].ToString();
                /**/
                ///指定日志文件的目录
                string fname = adress + "\\" + "log" + DateTime.Now.ToString("yy-MM-dd") + ".txt";
                /**/
                ///定义文件信息对象

                FileInfo finfo = new FileInfo(fname);

                if (!finfo.Exists)
                {
                    FileStream fs;
                    fs = File.Create(fname);
                    fs.Close();
                    finfo = new FileInfo(fname);
                }

                /**/
                ///判断文件是否存在以及是否大于2K
                if (finfo.Length > 1024 * 1024 * 10)
                {
                    /**/
                    ///文件超过10MB则重命名
                    File.Move(logAdress + "\\Log\\" + txtName + ".txt", Directory.GetCurrentDirectory() + DateTime.Now.TimeOfDay + "\\Log\\" + txtName + ".txt");
                    /**/
                    ///删除该文件
                    //finfo.Delete();
                }
                //finfo.AppendText();
                /**/
                ///创建只写文件流

                using (FileStream fs = finfo.OpenWrite())
                {
                    /**/
                    ///根据上面创建的文件流创建写数据流
                    StreamWriter w = new StreamWriter(fs);

                    /**/
                    ///设置写数据流的起始位置为文件流的末尾
                    w.BaseStream.Seek(0, SeekOrigin.End);

                    w.WriteLine("*****************Start*****************");
                    w.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                    /**/
                    ///写入当前系统时间并换行

                    /**/
                    ///写入日志内容并换行
                    w.WriteLine(input);

                    /**/
                    ///写入------------------------------------“并换行
                    w.WriteLine("------------------END------------------------");

                    /**/
                    ///清空缓冲区内容，并把缓冲区内容写入基础流
                    w.Flush();

                    /**/
                    ///关闭写数据流
                    w.Close();
                }
            }
            catch (Exception ex)
            { throw ex; }

        }
        #endregion


        public static void AddAndEdit(List<FormICSMO2UserUIModel> list, List<ICSMO2UserDetail> list_detail, string dsconn)
        {
            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                foreach (FormICSMO2UserUIModel MO2UserInfo in list)
                {

                    bool isNew = false;
                    var line = db.ICSMO2User.SingleOrDefault(a => a.LOTNO == MO2UserInfo.LOTNO && a.OPCODE == MO2UserInfo.OPCODE);
                    //var line = db.ICSMO2User.SingleOrDefault(a => a.LOTNO == MO2UserInfo.LOTNO );

                    if (line == null)
                    {
                        isNew = true;
                        line = new ICSMO2User();
                        line.ID = AppConfig.GetGuid();
                    }
                    line.MOCODE = MO2UserInfo.MOCODE;
                    line.MOSEQ = MO2UserInfo.MOSEQ;
                    line.LOTNO = MO2UserInfo.LOTNO;
                    line.SEGCODE = MO2UserInfo.SEGCODE;
                    line.RouteCode = MO2UserInfo.RouteCode;
                    line.OPCODE = MO2UserInfo.OPCODE;
                    line.USERCODE = MO2UserInfo.USERCODE;
                    line.USERName = MO2UserInfo.USERName;
                    line.EQPCode = MO2UserInfo.EQPCode;
                    line.EQPName = MO2UserInfo.EQPName;
                    line.MUSER = MO2UserInfo.MUSER;
                    line.MUSERName = MO2UserInfo.MUSERName;
                    line.MTIME = Convert.ToDateTime(MO2UserInfo.MTIME);
                    line.WorkPoint = MO2UserInfo.WorkPoint;
                    line.EATTRIBUTE1 = MO2UserInfo.EATTRIBUTE1;
                    line.StartPlanDate = MO2UserInfo.StartPlanDate;
                    line.EndPlanDate = MO2UserInfo.EndPlanDate;
                    if (MO2UserInfo.PRLineID == "False" || MO2UserInfo.PRLineID == "0" || string.IsNullOrWhiteSpace(MO2UserInfo.PRLineID))
                    {
                        line.PRLineID = null;
                    }
                    else
                    {
                        line.PRLineID = MO2UserInfo.PRLineID;
                    }

                    if (isNew)
                    {
                        db.ICSMO2User.InsertOnSubmit(line);
                    }
                    db.SubmitChanges();
                    foreach (ICSMO2UserDetail detail in list_detail)
                    {
                        bool isNewDetail = false;
                        var lineDetail = db.ICSMO2UserDetail.SingleOrDefault(a => a.DID == detail.DID);
                        if (lineDetail == null)
                        {
                            isNewDetail = true;
                            lineDetail = new ICSMO2UserDetail();
                        }
                        lineDetail.ID = line.ID;
                        lineDetail.EQPID = detail.EQPID;
                        lineDetail.DID = detail.DID;
                        lineDetail.AVAILABLE = detail.AVAILABLE;
                        lineDetail.PLANEND = detail.PLANEND;
                        lineDetail.PLANSTART = detail.PLANSTART;
                        lineDetail.PLANQTY = detail.PLANQTY;
                        lineDetail.PLANTIME = detail.PLANTIME;
                        if (isNewDetail)
                        {
                            db.ICSMO2UserDetail.InsertOnSubmit(lineDetail);
                        }
                        db.SubmitChanges();
                    }
                }
                db.SubmitChanges();
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw new Exception(ex.Message);
            }
        }

        public static void AddAndEdit(FormICSMO2UserUIModel MO2UserInfo, string dsconn)
        {
            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                bool isNew = false;
                var line = db.ICSMO2User.SingleOrDefault(a => a.LOTNO == MO2UserInfo.LOTNO && a.OPCODE == MO2UserInfo.OPCODE);
                if (line == null)
                {
                    isNew = true;
                    line = new ICSMO2User();
                    line.ID = AppConfig.GetGuid();
                }
                line.MOCODE = MO2UserInfo.MOCODE;
                line.MOSEQ = MO2UserInfo.MOSEQ;
                line.LOTNO = MO2UserInfo.LOTNO;
                line.SEGCODE = MO2UserInfo.SEGCODE;
                line.RouteCode = MO2UserInfo.RouteCode;
                line.OPCODE = MO2UserInfo.OPCODE;
                line.USERCODE = MO2UserInfo.USERCODE;
                line.USERName = MO2UserInfo.USERName;
                line.EQPCode = MO2UserInfo.EQPCode;
                line.EQPName = MO2UserInfo.EQPName;
                line.MUSER = MO2UserInfo.MUSER;
                line.MUSERName = MO2UserInfo.MUSERName;
                line.MTIME = Convert.ToDateTime(MO2UserInfo.MTIME);
                line.WorkPoint = MO2UserInfo.WorkPoint;
                line.EATTRIBUTE1 = MO2UserInfo.EATTRIBUTE1;
                line.StartPlanDate = MO2UserInfo.StartPlanDate;
                line.EndPlanDate = MO2UserInfo.EndPlanDate;
                if (MO2UserInfo.PRLineID == "False" || MO2UserInfo.PRLineID == "0" || string.IsNullOrWhiteSpace(MO2UserInfo.PRLineID))
                {
                    line.PRLineID = null;
                }
                else
                {
                    line.PRLineID = MO2UserInfo.PRLineID;
                }
                foreach (ICSMO2UserDetail detail in MO2UserInfo.ListDetail)
                {
                    var lineDetail = db.ICSMO2UserDetail.SingleOrDefault(a => a.DID == detail.DID);
                    if (lineDetail != null)
                    {
                        lineDetail.AVAILABLE = detail.AVAILABLE;
                    }
                    else
                    {
                        lineDetail = new ICSMO2UserDetail();
                        lineDetail.ID = line.ID;
                        lineDetail.EQPID = detail.EQPID;
                        lineDetail.DID = detail.DID;
                        lineDetail.PLANEND = detail.PLANEND;
                        lineDetail.PLANSTART = detail.PLANSTART;
                        lineDetail.PLANQTY = detail.PLANQTY;
                        lineDetail.PLANTIME = detail.PLANTIME;
                        lineDetail.AVAILABLE = detail.AVAILABLE;
                        db.ICSMO2UserDetail.InsertOnSubmit(lineDetail);
                    }
                    db.SubmitChanges();
                }
                if (isNew)
                    db.ICSMO2User.InsertOnSubmit(line);
                db.SubmitChanges();
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw new Exception(ex.Message);
            }
            finally
            {
                db.Connection.Close();
            }
        }

        public static void CancelSended(List<string> list, string StrCon)
        {
            FramDataContext db = new FramDataContext(StrCon);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                for (int i = 0; i < list.Count; i++)
                {
                    var line = db.ICSMO2User.SingleOrDefault(a => a.ID == list[i]);
                    if (line != null)
                    {
                        //1.已开工
                        var linesOnwip = db.ICSLOTONWIP.Where(a => a.LOTNO == line.LOTNO && a.OPCODE == line.OPCODE);
                        if (linesOnwip.Count() != 0)
                        {
                            throw new Exception("跟踪单" + line.LOTNO + "工序" + line.OPCODE + "已生产,不可取消");
                        }
                        //2.删除派工设备表
                        var lineDetail = db.ICSMO2UserDetail.Where(a => a.ID == list[i]);
                        if (lineDetail.Count() != 0)
                        {
                            db.ICSMO2UserDetail.DeleteAllOnSubmit(lineDetail);
                            db.SubmitChanges();
                        }
                        //3.删除派工主表
                        db.ICSMO2User.DeleteOnSubmit(line);
                        db.SubmitChanges();
                    }
                }
                db.SubmitChanges();
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw new Exception(ex.Message);
            }
            finally
            {
                db.Connection.Close();
            }
        }

        public static void CancelSendedWW(List<string> list, string StrCon)
        {
            FramDataContext db = new FramDataContext(StrCon);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                for (int i = 0; i < list.Count; i++)
                {
                    var line = db.ICSMO2User.SingleOrDefault(a => a.ID == list[i]);
                    if (line != null)
                    {
                        //1.已产生PR单
                        if (!string.IsNullOrEmpty(line.PRLineID))
                        {
                            throw new Exception("跟踪单" + line.LOTNO + "工序" + line.OPCODE + "已产生PR单,不可撤销" + line.PRLineID);
                        }
                        var linesOnwip = db.ICSLOTONWIP.Where(a => a.LOTNO == line.LOTNO && a.OPCODE == line.OPCODE);
                        if (linesOnwip.Count() != 0)
                        {
                            throw new Exception("跟踪单" + line.LOTNO + "工序" + line.OPCODE + "已发料,不可撤销");
                        }
                        //2.
                        //ICSMO2UserLog log = new ICSMO2UserLog();
                        //log.
                        //3.删除派工主表
                        db.ICSMO2User.DeleteOnSubmit(line);
                        db.SubmitChanges();
                    }
                }
                db.SubmitChanges();
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw new Exception(ex.Message);
            }
            finally
            {
                db.Connection.Close();
            }
        }

        public static DataTable SelectMO2USER(string MOCODE, string MOSEQ, string StrCon, string wp)
        {
            string sql = @"SELECT MOCODE,MOSEQ,LOTNO,OPCODE FROM dbo.ICSMO2User WHERE MOCODE='{0}' AND MOSEQ={1}  AND WorkPoint='{2}'";
            sql = string.Format(sql, MOCODE, MOSEQ, wp);
            return DBHelper.ExecuteDataset(StrCon, CommandType.Text, sql).Tables[0];
        }


        public static void Import(List<ICSMO2User> mo2userList, string appConnectString)
        {
            FramDataContext db = new FramDataContext(appConnectString);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                foreach (ICSMO2User info in mo2userList)
                {
                    bool add = false;
                    string EQPID = "";
                    string EQPNAME = "";
                    var lineItemLot = db.ICSITEMLot.SingleOrDefault(a => a.LotNO == info.LOTNO && a.WorkPoint == info.WorkPoint && (a.TYPE == "工单" || a.TYPE == "跟踪单分批"));
                    if (lineItemLot == null)
                    {
                        throw new Exception("跟踪单:" + info.LOTNO + ",不存在");
                    }
                    var lineRoute = db.ICSROUTE.Where(a => a.ROUTECODE == info.RouteCode && a.WorkPoint == info.WorkPoint);
                    if (lineRoute == null)
                    {
                        throw new Exception("工艺:" + info.RouteCode + ",不存在");
                    }

                    var lineOP = db.ICSOP.SingleOrDefault(a => a.OPCODE == info.OPCODE && a.WorkPoint == info.WorkPoint);
                    if (lineOP == null)
                    {
                        throw new Exception("工序:" + info.OPCODE + ",不存在");
                    }
                    //分批时,route中可能不包含此op,是额外添加的,以ICSITEMROUTE2OPLot为准.
                    //var lineRouteOP = db.ICSROUTE2OP.SingleOrDefault(a => a.OPCODE == info.OPCODE && a.ROUTECODE == info.RouteCode && a.WORKPOINT == info.WorkPoint);
                    //if (lineRouteOP == null)
                    //{
                    //    throw new Exception("工艺:" + info.RouteCode + ",不包含工序:" + info.OPCODE);
                    //}
                    var lineItemR2OPLot = db.ICSITEMROUTE2OPLot.SingleOrDefault(a => a.LotNo == info.LOTNO && a.ROUTECODE == info.RouteCode && a.OPCODE == info.OPCODE);
                    if (lineItemR2OPLot == null)
                    {
                        throw new Exception("跟踪单:" + info.LOTNO + ",工艺:" + info.RouteCode + ",工序:" + info.OPCODE + ",不存在");
                    }

                    List<ICSEQPSTP> listTime = db.ICSEQPSTP.Where(a => a.ITEMCODE == lineItemLot.ItemCode && a.EATTRIBUTE1 == info.RouteCode && a.OPCODE == info.OPCODE && a.ISREF == "是").ToList();
                    if (listTime.Count == 0)
                    {
                        throw new Exception("跟踪单:" + info.LOTNO + "对应物料:" + lineItemLot.ItemCode + ",工艺:" + info.OPCODE + ",工序:" + info.OPCODE + ",没有默认的标准工时");
                    }
                    if (listTime.Count > 1)
                    {
                        throw new Exception("跟踪单:" + info.LOTNO + "对应物料:" + lineItemLot.ItemCode + ",工艺:" + info.OPCODE + ",工序:" + info.OPCODE + ",有多个默认标准工时");
                    }
                    var lineTime = listTime[0];
                    if (info.EATTRIBUTE1 == "0")
                    {
                        var lineEQ = db.ICSEquipment.SingleOrDefault(a => a.EQPCode == info.EQPCode && a.WorkPoint == info.WorkPoint);
                        if (lineEQ == null)
                        {
                            throw new Exception("设备不存在:" + info.EQPCode);
                        }
                        EQPID = lineEQ.EQPID;
                        EQPNAME = lineEQ.EQPDESC;
                        //ahwit委外工序只能委外派工
                        if (lineOP.EATTRIBUTE1 == "1")
                        {
                            throw new Exception("工序:" + info.OPCODE + "是自制工序,不可转委外");
                        }
                        if (lineTime.EQPTypeCode == "固定设备")
                        {
                            if (lineTime.EQPCODE != info.EQPCode)
                            {
                                throw new Exception("标准工时维护的是固定设备:" + lineTime.EQPCODE);
                            }
                            //info.EQPCode = lineTime.EQPCODE;
                        }
                        else
                        {
                            if (lineTime.cClass != lineEQ.cClass || Convert.ToInt32(lineTime.cHandles) > lineEQ.cHandles || Convert.ToInt32(lineTime.cAxis) > lineEQ.cAxis ||
                                Convert.ToInt32(lineTime.cMachinableProfiles) > lineEQ.cMachinableProfiles || Convert.ToInt32(lineTime.cToolMagazine) > lineEQ.cToolMagazine || string.Compare(lineTime.cAccuracy.ToUpper(), lineEQ.cAccuracy.ToUpper()) == -1)
                            {
                                throw new Exception("设备" + info.EQPCode + "\r\n"
                                    + "类型" + lineEQ.cClass + "刀柄数" + lineEQ.cHandles + "轴数" + lineEQ.cAxis + "刀库数" + lineEQ.cToolMagazine + "可加工外形" + lineEQ.cMachinableProfiles + "精度" + lineEQ.cAccuracy + "\r\n"
                                    + "对应的6大维度不满足标准工时:\r\n"
                                    + "类型" + lineTime.cClass + "刀柄数" + lineTime.cHandles + "轴数" + lineTime.cAxis + "刀库数" + lineTime.cToolMagazine + "可加工外形" + lineTime.cMachinableProfiles + "精度" + lineTime.cAccuracy);
                            }
                        }
                    }

                    var linePR = db.ICSMO2User.SingleOrDefault(a => a.LOTNO == info.LOTNO && a.OPCODE == info.OPCODE && a.PRLineID != null);
                    if (linePR != null)
                    {
                        throw new Exception("跟踪单:" + info.LOTNO + "工序:" + info.OPCODE + "已请购");
                    }
                    var lineOnwip = db.ICSLOTONWIP.SingleOrDefault(a => a.LOTNO == info.LOTNO && a.OPCODE == info.OPCODE);
                    if (lineOnwip != null)
                    {
                        throw new Exception("跟踪单:" + info.LOTNO + "工序:" + info.OPCODE + "已开工");
                    }
                    var lineMo2User = db.ICSMO2User.SingleOrDefault(a => a.LOTNO == info.LOTNO && a.OPCODE == info.OPCODE);
                    if (lineMo2User == null)
                    {
                        add = true;
                        lineMo2User = new ICSMO2User();
                        lineMo2User.ID = AppConfig.GetGuid();
                        lineMo2User.MOCODE = lineItemLot.TransNO;
                        lineMo2User.MOSEQ = Convert.ToInt32(lineItemLot.TransLine);
                        lineMo2User.LOTNO = info.LOTNO;
                        lineMo2User.RouteCode = info.RouteCode;
                        lineMo2User.OPCODE = info.OPCODE;
                        lineMo2User.WorkPoint = info.WorkPoint;
                        lineMo2User.USERCODE = "";
                        lineMo2User.USERName = "";
                        lineMo2User.EQPCode = "";
                        lineMo2User.EQPName = "";
                    }
                    lineMo2User.MTIME = info.MTIME;
                    lineMo2User.MUSER = info.MUSER;
                    lineMo2User.MUSERName = info.MUSERName;
                    lineMo2User.EATTRIBUTE1 = info.EATTRIBUTE1;

                    if (info.EATTRIBUTE1 == "0")
                    {
                        var lineMD = db.ICSMO2UserDetail.Where(a => a.ID == lineMo2User.ID);
                        foreach (ICSMO2UserDetail item in lineMD)
                        {
                            item.AVAILABLE = false;
                        }
                        db.SubmitChanges();
                        ICSMO2UserDetail md = new ICSMO2UserDetail();
                        md.ID = lineMo2User.ID;
                        md.EQPID = EQPID;
                        md.DID = AppConfig.GetGuid();
                        md.PLANSTART = (DateTime)info.StartPlanDate;
                        md.PLANEND = (DateTime)info.EndPlanDate;
                        md.PLANQTY = lineItemLot.LOTQTY;
                        md.PLANTIME = Convert.ToInt32(Math.Round((md.PLANEND - md.PLANSTART).TotalMinutes, 0));
                        md.AVAILABLE = true;

                        db.ICSMO2UserDetail.InsertOnSubmit(md);
                    }
                    if (add)
                    {
                        db.ICSMO2User.InsertOnSubmit(lineMo2User);
                    }
                    db.SubmitChanges();
                }
                db.SubmitChanges();
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw new Exception(ex.Message);
            }
            finally
            {
                db.Connection.Close();
            }
        }

        public static DataTable ExportTemplate(string appConnectString, string workpoint, bool onlyNotSend, string mocode)
        {
            string sql = @"
SELECT
	LOT.LotNo AS 跟踪单号,
	LOT.ROUTECODE AS 工艺路线代码,
	LOT.OPSEQ 工序次序,
	LOT.OPCODE AS 工序代码,
	op.OPDESC as 工序名称,
	(CASE LOT.OPAttr WHEN '委外' THEN 'Y' ELSE 'N' END) AS 是否委外,
	NULL AS 设备代码,
	CONVERT(DATETIME,NULL) AS 计划开始,
	CONVERT(DATETIME,NULL) AS 计划结束
FROM dbo.ICSITEMROUTE2OPLot LOT
INNER JOIN dbo.ICSOP op ON op.OPCODE=LOT.OPCODE AND op.WorkPoint=lot.WorkPoint
LEFT JOIN dbo.ICSMO2User US ON US.LOTNO=LOT.LotNo AND US.RouteCode=LOT.ROUTECODE AND US.OPCODE=LOT.OPCODE AND US.WorkPoint=LOT.WorkPoint
WHERE US.ID IS NULL 
ORDER BY LOT.LotNo,LOT.OPSEQ
";
            //if (!string.IsNullOrEmpty(mocode))
            //{
            //    sql = sql.Replace("--REPLACE--", " AND MO.MOCODE ='" + mocode + "'");
            //}
            return DBHelper.ExecuteDataset(appConnectString, CommandType.Text, sql).Tables[0];
        }


        static string APIURL = System.Configuration.ConfigurationSettings.AppSettings["APIURL"].ToString();
        static string urlCreatErpPR = APIURL + "sendRequisition";//生成erp请购单
        static string urlCreatVisualItem = APIURL + "sendXML";//生成虚拟物料

        public static void SendAhwit(string LOTNO, string ROUTECODE, int OPSEQ, string OPCODE, string WorkPoint,DateTime timeCreated,string appConnectString)
        {
            string msgHead = "";
            decimal PRQTY = 0;
            decimal CHKOK = 0;
            decimal NCROK = 0;
            decimal WTIME = 3;
            FramDataContext db = new FramDataContext(appConnectString);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                //1
                var line_cInvCCode = db.Sys_EnumValues.SingleOrDefault(a => a.EnumKey == "00010" && a.WorkPointCode == WorkPoint);
                if (line_cInvCCode == null)
                {
                    throw new Exception("未配置虚拟物料类型,00010.");
                }
                var line_cTranTypeID = db.Sys_EnumValues.SingleOrDefault(a => a.EnumKey == "00110" && a.WorkPointCode == WorkPoint);
                if (line_cTranTypeID == null)
                {
                    throw new Exception("未配置请购类型,00110.");
                }
                //2
                var lineITEMLot = db.ICSITEMLot.SingleOrDefault(a => a.LotNO == LOTNO && a.WorkPoint == WorkPoint);
                if (lineITEMLot == null)
                {
                    throw new Exception("跟踪单条码已被删除" + LOTNO);
                }
                var lineInv = db.ICSINVENTORY.SingleOrDefault(a => a.INVCODE == lineITEMLot.ItemCode && a.WorkPoint == WorkPoint);
                if (lineInv == null)
                {
                    throw new Exception("物料不存在" + lineITEMLot.ItemCode);
                }
                var lineOP = db.ICSOP.SingleOrDefault(a => a.OPCODE == OPCODE && a.WorkPoint == WorkPoint);
                if (lineOP == null)
                {
                    throw new Exception("工序不存在" + OPCODE);
                }
                msgHead = "跟踪单:" + LOTNO + ",工序: " + OPCODE + "(" + OPSEQ.ToString() + "), ";
                var lineMO2User = db.ICSMO2User.SingleOrDefault(a => a.LOTNO == LOTNO && a.RouteCode == ROUTECODE && a.OPCODE == OPCODE && a.WorkPoint == WorkPoint);
                if (lineMO2User == null)
                {
                    throw new Exception("请先派工.");
                }
                if (!string.IsNullOrEmpty(lineMO2User.PRLineID))
                {
                    throw new Exception("已产生请购单.");
                }
                if (lineMO2User.EATTRIBUTE1 != "1")
                {
                    throw new Exception("不是委外工序.");
                }
                var linePreOPCode = db.ICSITEMROUTE2OPLot.SingleOrDefault(a => a.LotNo == LOTNO && a.ROUTECODE == ROUTECODE && a.OPSEQ < OPSEQ && a.WorkPoint == WorkPoint);
                if (linePreOPCode == null)
                {
                    //此处的LOTQTY 是分批之后的数量,不用再减去分批数
                    PRQTY = lineITEMLot.LOTQTY;
                }
                else
                {
                    //无论上工序是否是委外工序, 都要走开工表 , 对于委外工序不必单独判断
                    //另外 阿威特 要求即使上工序是委外,也要等委外收料后, 检验完成才能请购本次委外工序
                    //(注)委外收料时的欠料数(ICSLOTONWIP.RcvNGQTY),记为不良
                    var linePreOnwip = db.ICSLOTONWIP.SingleOrDefault(a => a.LOTNO == linePreOPCode.LotNo && a.ROUTECODE == linePreOPCode.ROUTECODE && a.OPSEQ == linePreOPCode.OPSEQ && a.OPCODE == linePreOPCode.OPCODE && a.WorkPoint == WorkPoint);
                    msgHead = "跟踪单:" + LOTNO + ",上道工序: " + linePreOPCode.OPCODE + "(" + linePreOPCode.OPSEQ.ToString() + "), ";
                    if (linePreOPCode == null)
                    {
                        throw new Exception("未开工. ");
                    }
                    if (linePreOnwip.ACTIONRESULT != "COLLECT_END")
                    {
                        throw new Exception("未完工. ");
                    }
                    var linePreCheck = db.ICSLOTONWIPCheck.SingleOrDefault(a => a.FORTRANID == linePreOnwip.ID);
                    if (linePreCheck == null)
                    {
                        throw new Exception("未做完工检验.");
                    }
                    if (linePreCheck.EndDateTime == null)
                    {
                        throw new Exception("完工检验还在进行中.");
                    }
                    CHKOK = (decimal)linePreCheck.OKQuantity;
                    if (linePreCheck.NGQuantity > 0)
                    {
                        List<ICSLOTONWIPCheckDetail> linePreCheckDet = db.ICSLOTONWIPCheckDetail.Where(a => a.FORTRANID == linePreCheck.ID && a.Type == "不良").ToList();
                        if (linePreCheckDet.Count == 0)
                        {
                            throw new Exception("数据获取错误,检验不良详情数据被删除.");
                        }
                        foreach (ICSLOTONWIPCheckDetail det in linePreCheckDet)
                        {
                            var lineNcr = db.ICSLOTONWIPCheckNCR.SingleOrDefault(a => a.CheckID == det.ID);
                            if (lineNcr == null)
                            {
                                throw new Exception("NCR结果未出.");
                            }
                            if (lineNcr.Status == "让步接受")
                            {
                                NCROK += Convert.ToDecimal(det.SetValue);
                            }
                        }
                    }
                    PRQTY = CHKOK + NCROK;
                    if (PRQTY == 0)
                    {
                        throw new Exception("完工检验OK数为 0 .");
                    }
                }
                //获取委外工时
                var lineSTP = db.ICSEQPSTP.SingleOrDefault(a => a.ITEMCODE == lineITEMLot.ItemCode && a.EATTRIBUTE1 == ROUTECODE && a.OPCODE == OPCODE && a.ISREF == "是" && a.WorkPoint == WorkPoint);
                if (lineSTP == null)
                {
                    var lineSTP1 = db.ICSITEMROUTE2OPLot.SingleOrDefault(a => a.LotNo == LOTNO && a.ROUTECODE == ROUTECODE && a.OPSEQ == OPSEQ && a.WorkPoint == WorkPoint);
                    if (lineSTP1 != null && lineSTP1.WTIME != null)
                    {
                        WTIME = (decimal)lineSTP1.WTIME;
                    }
                }
                else
                {
                    if (lineSTP.WTIME != null)
                    {
                        WTIME = (decimal)lineSTP.WTIME;
                    }
                }
                NcApiInputArguments intpuInfo = new NcApiInputArguments();
                intpuInfo.datasource = "design";
                intpuInfo.usercode = "mestoerp";
                intpuInfo.password = "sH05R21u1";
                intpuInfo.list = new List<object>();

                string pk_group = "Ahwit";
                string pk_org = WorkPoint;
                string billmaker = "mestoerp";
                string code = lineInv.INVCODE + "_" + OPCODE;
                string name = lineInv.INVDESC + "_" + lineOP.OPDESC;
                string pk_marbasclass = line_cInvCCode.EnumText;
                string ctrantypeid = line_cTranTypeID.EnumText;
           
                msgHead = "调用生成虚拟物料接口,";//(注意:此接口只能单个物料调用)               
                CreateVitemIntity cvItemInfo = new CreateVitemIntity();
                cvItemInfo.pk_org = pk_org;
                cvItemInfo.code = code;
                cvItemInfo.name = name;
                cvItemInfo.materialtype = lineInv.INVTYPE;
                cvItemInfo.pk_measdoc = lineInv.INVUOM;
                cvItemInfo.pk_marbasclass = pk_marbasclass;// "原材料";
                cvItemInfo.pk_mattaxes = "一般纳税商品";
                cvItemInfo.graphid = "";
                intpuInfo.list.Add(cvItemInfo);
                string jsonCreatItem = JsonConvert.SerializeObject(intpuInfo);
                string resStrCreatItem = HttpPost(urlCreatVisualItem, jsonCreatItem);
                NcApiOutArguments res1 = new NcApiOutArguments();
                res1 = JsonConvert.DeserializeObject<NcApiOutArguments>(resStrCreatItem);
                if (res1.flat != "1")
                {
                    throw new Exception("产生虚拟物料失败！" + res1.list_info);
                }
                msgHead = "调用生成请购单接口,";
                intpuInfo.list.Clear();
                CreatePRIntity cPrInfo = new CreatePRIntity();
                cPrInfo.pk_group = pk_group;
                cPrInfo.pk_org = pk_org;
                cPrInfo.billmaker = billmaker;
                cPrInfo.ccurrencyid = "人民币";
                cPrInfo.ctrantypeid = ctrantypeid;
                cPrInfo.dbilldate = timeCreated.ToString("yyyy-MM-dd HH:mm:ss");
                cPrInfo.time = timeCreated.ToString("yyyy-MM-dd HH:mm:ss");
                cPrInfo.list = new List<CreatePRIntityLine>();
                CreatePRIntityLine line = new CreatePRIntityLine();
                line.pk_group = cPrInfo.pk_group;
                line.pk_org = cPrInfo.pk_org;
                line.crowno = "1";
                line.pk_purchaseorg = cPrInfo.pk_org;
                line.pk_srcmaterial = code;
                line.pk_material = code;
                line.nastnum = PRQTY.ToString();
                line.castunitid = "个";
                line.vchangerate = "1/1";
                line.dreqdate = timeCreated.AddDays((double)WTIME).ToString();
                line.dsuggestdate = timeCreated.ToString("yyyy-MM-dd HH:mm:ss");
                line.vbmemo = LOTNO;
                line.vsourcecode = "";
                line.vsourcerowno = "";
                cPrInfo.list.Add(line);
                intpuInfo.list.Add(cPrInfo);
                string jsonCreatErpPR = JsonConvert.SerializeObject(intpuInfo);
                string resStrCreatErpPR = HttpPost(urlCreatErpPR, jsonCreatErpPR);
                NcApiOutArguments res2 = new NcApiOutArguments();
                res2 = JsonConvert.DeserializeObject<NcApiOutArguments>(resStrCreatErpPR);
                if (res2.flat != "1")
                {
                    throw new Exception("产生委外请购单失败！" + res2.list_info);
                }
                msgHead = "回写PRLineID至mes,";
                List<CreatePRIntityReturn> _list = new List<CreatePRIntityReturn>();
                if (string.IsNullOrWhiteSpace(res2.list_info.ToString()))
                {
                    throw new Exception("未获取到请购单数据！");
                }
                _list = JsonConvert.DeserializeObject<List<CreatePRIntityReturn>>(res2.list_info.ToString());
                foreach (var item in _list)
                {
                    string itemcodeReturn = item.VirtualItem.Split('_')[0].ToUpper();
                    string opcodeReturn = item.VirtualItem.Split('_')[1].ToUpper();
                    lineMO2User.PRLineID = item.PrLine;
                    lineMO2User.PRQTY = PRQTY;
                    db.SubmitChanges();
                }
                db.SubmitChanges();
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw new Exception(msgHead + ex.Message);
            }
            finally
            {
                db.Connection.Close();
            }
        }

        private static string HttpPost(string url, string body)
        {
            try
            {
                Encoding encoding = Encoding.UTF8;
                System.Net.HttpWebRequest request = (System.Net.HttpWebRequest)System.Net.WebRequest.Create(url);
                request.Method = "POST";
                request.Accept = "application/json, text/javascript, */*"; //"text/html, application/xhtml+xml, */*";
                request.ContentType = "application/json; charset=utf-8";

                byte[] buffer = encoding.GetBytes(body);
                request.ContentLength = buffer.Length;
                request.GetRequestStream().Write(buffer, 0, buffer.Length);
                System.Net.HttpWebResponse response = (System.Net.HttpWebResponse)request.GetResponse();
                using (StreamReader reader = new StreamReader(response.GetResponseStream(), encoding))
                {
                    return reader.ReadToEnd();
                }
            }
            catch (System.Net.WebException ex)
            {
                throw new Exception(ex.Message);
            }
        }

    }
}