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

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

namespace ICSSoft.Frame.Data.DAL
{
    public class ICSHeatTreatmentDAL
    {
        #region 新增炉号
        public static void AddAndEditList(List<ICSOpFile> equipmentInfoList,string Transno,string dsconn)
        {
            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                foreach (ICSOpFile equipmentInfo in equipmentInfoList)
                {
                    //var op = db.ICSOpFile.SingleOrDefault(a => a.ID == equipmentInfo.ID );
                    //if (op != null)
                    //{
                    //    throw new Exception("单据号：" + equipmentInfo.TransNO + "已存在！");
                    //}
                    bool isNew = false;
                    var line = db.ICSOpFile.SingleOrDefault(a => a.ID == equipmentInfo.ID);
                    if (line == null)
                    {
                        isNew = true;
                        line = new ICSOpFile();
                        line.ID = AppConfig.GetGuid();
                    }
                    if (!string.IsNullOrWhiteSpace(equipmentInfo.MOCODE))
                    {
                        var opcode = db.ICSOP.SingleOrDefault(a => a.OPCODE == equipmentInfo.EATTRIBUTE4.TrimEnd(';'));
                        if (opcode != null)
                        {
                            if (opcode.OPDESC.Contains("渗碳"))
                            {
                                #region 工序:渗碳
                                var mo1 = db.ICSOpFile.Where(a => a.EQPCode == equipmentInfo.EQPCode.TrimEnd(';') && a.EATTRIBUTE1 == "炉号" && a.TransNO.Length <= 8).OrderByDescending(a => a.TransNO);
                                if (Transno == null)
                                {
                                    if (mo1 != null && mo1.Count() > 0)
                                    {
                                        string code = mo1.FirstOrDefault().TransNO;
                                        line.TransNO = equipmentInfo.EQPCode.TrimEnd(';') + (Convert.ToInt32(code.Substring(code.Length - 3)) + 1).ToString().PadLeft(3, '0');
                                    }
                                    else
                                    {
                                        line.TransNO = equipmentInfo.EQPCode.TrimEnd(';') + "001";
                                    }
                                }
                                else
                                {
                                    line.TransNO = Transno;
                                }
                                #endregion
                            }
                            else
                            {
                                #region 工序:其他
                                var mo1 = db.ICSOpFile.Where(a => a.EQPCode == equipmentInfo.EQPCode.TrimEnd(';') && a.EATTRIBUTE1 == "炉号" && a.TransNO.Length > 8).OrderByDescending(a => a.TransNO);
                                if (Transno == null)
                                {
                                    if (mo1 != null && mo1.Count() > 0)
                                    {
                                        string code = mo1.FirstOrDefault().TransNO;
                                        line.TransNO = equipmentInfo.EQPCode.TrimEnd(';') + (Convert.ToInt32(code.Substring(code.Length - 6)) + 1).ToString().PadLeft(6, '0');
                                    }
                                    else
                                    {
                                        line.TransNO = equipmentInfo.EQPCode.TrimEnd(';') + "000001";
                                    }
                                }
                                else
                                {
                                    line.TransNO = Transno;
                                }
                                #endregion
                            }
                        }
                        //line.LOTNOs = equipmentInfo.LOTNOs;
                        if (equipmentInfo.LOTNOs == null)
                        {
                            throw new Exception("产品产品跟踪单号不能为空");
                        }
                        else
                        {
                            line.LOTNOs = equipmentInfo.LOTNOs.TrimEnd(';');
                        }
                        line.MOCODE = equipmentInfo.MOCODE.TrimEnd(';');
                        line.MOSEQ = equipmentInfo.MOSEQ.TrimEnd(';');
                        line.EQPCode = equipmentInfo.EQPCode.TrimEnd(';');
                        line.EATTRIBUTE2 = equipmentInfo.EATTRIBUTE2.TrimEnd(';');//零件号
                        line.EATTRIBUTE3 = equipmentInfo.EATTRIBUTE3.TrimEnd(';');//设备号
                        line.EATTRIBUTE4 = equipmentInfo.EATTRIBUTE4.TrimEnd(';');//工序
                    }
                    else
                    {
                        var opcodes = db.ICSOP.SingleOrDefault(a => a.OPCODE == equipmentInfo.EATTRIBUTE4);
                        if (opcodes != null)
                        {
                            if (opcodes.OPDESC.Contains("渗碳"))
                            {
                                #region 工序:渗碳
                                var mo1 = db.ICSOpFile.Where(a => a.EQPCode == equipmentInfo.EQPCode && a.EATTRIBUTE1 == "炉号"&&a.TransNO.Length<=8).OrderByDescending(a => a.TransNO);
                                if (mo1 != null && mo1.Count() > 0)
                                {
                                    string code = mo1.FirstOrDefault().TransNO;
                                    line.TransNO = equipmentInfo.EQPCode + (Convert.ToInt32(code.Substring(code.Length - 3)) + 1).ToString().PadLeft(3, '0');
                                }
                                else
                                {
                                    line.TransNO = equipmentInfo.EQPCode + "001";
                                }
                                #endregion
                            }
                            else
                            {
                                #region 工序:其他
                                var mo1 = db.ICSOpFile.Where(a => a.EQPCode == equipmentInfo.EQPCode && a.EATTRIBUTE1 == "炉号"&&a.TransNO.Length>8).OrderByDescending(a => a.TransNO);
                                if (mo1 != null && mo1.Count() > 0)
                                {
                                    string code = mo1.FirstOrDefault().TransNO;
                                    line.TransNO = equipmentInfo.EQPCode + (Convert.ToInt32(code.Substring(code.Length - 6)) + 1).ToString().PadLeft(6, '0');
                                }
                                else
                                {
                                    line.TransNO = equipmentInfo.EQPCode + "000001";
                                }
                                #endregion
                            }
                        }
                        line.EQPCode = equipmentInfo.EQPCode;
                        line.EATTRIBUTE2 = equipmentInfo.EATTRIBUTE2;
                        line.EATTRIBUTE4 = equipmentInfo.EATTRIBUTE4;
                    }
                    line.WorkPoint = equipmentInfo.WorkPoint;//传入固定值
                    line.MUSER = equipmentInfo.MUSER;
                    line.MUSERName = equipmentInfo.MUSERName;
                    line.MTIME = Convert.ToDateTime(equipmentInfo.MTIME);
                    line.EATTRIBUTE1 = "炉号";
                    line.EATTRIBUTE5 = equipmentInfo.EATTRIBUTE5;
                    if (isNew)
                        db.ICSOpFile.InsertOnSubmit(line);
                    db.SubmitChanges();
                }
                db.SubmitChanges();
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw new Exception(ex.Message);
            }
        }
        #endregion

        #region 新增试样号
        public static void AddSample(List<ICSOpFile> equipmentInfoList, string ID,string Transno,int flag, string dsconn)
        {
            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                foreach (ICSOpFile equipmentInfo in equipmentInfoList)
                {
                    //var op = db.ICSOpFile.SingleOrDefault(a => a.ID == equipmentInfo.ID);
                    //if (op != null)
                    //{
                    //    throw new Exception("单据号：" + equipmentInfo.TransNO + "已存在！");
                    //}
                    bool isNew = false;
                    var line = db.ICSOpFile.SingleOrDefault(a => a.ID == equipmentInfo.ID);
                    if (line == null)
                    {
                        isNew = true;
                        line = new ICSOpFile();
                        line.ID = equipmentInfo.ID;
                    }
                    if (flag == 1)
                    {
                        var mo2 = db.ICSOpFile.Where(a => a.TransNO.Substring(0, Transno.Length) == Transno && a.EATTRIBUTE1 == "试样号").OrderByDescending(a => a.TransNO);
                        if (mo2 != null && mo2.Count() > 0)
                        {
                            string code1 = mo2.FirstOrDefault().TransNO;
                            line.TransNO = Transno + "-" + (Convert.ToInt32(code1.Substring(code1.LastIndexOf("-") + 1)) + 1).ToString().PadLeft(1, '0');
                        }
                        else
                        {
                            line.TransNO = Transno + "-" + "1";
                        }
                    }
                    else
                    {
                        line.TransNO = Transno;
                    }
                    line.MOCODE = equipmentInfo.MOCODE.TrimEnd(';');
                    line.MOSEQ = equipmentInfo.MOSEQ.TrimEnd(';');
                    line.EQPCode = equipmentInfo.EQPCode.TrimEnd(';');
                    line.LOTNOs = equipmentInfo.LOTNOs.TrimEnd(';');
                    line.WorkPoint = equipmentInfo.WorkPoint;//传入固定值
                    line.MUSER = equipmentInfo.MUSER;
                    line.MUSERName = equipmentInfo.MUSERName;
                    line.MTIME = Convert.ToDateTime(equipmentInfo.MTIME);
                    line.EATTRIBUTE1 = "试样号";
                    line.EATTRIBUTE2 = equipmentInfo.EATTRIBUTE2.TrimEnd(';');//零件号
                    line.EATTRIBUTE3 = equipmentInfo.EATTRIBUTE3.TrimEnd(';');//设备名称
                    line.EATTRIBUTE4 = equipmentInfo.EATTRIBUTE4.TrimEnd(';');//工序
                    line.EATTRIBUTE5 = equipmentInfo.EATTRIBUTE5;
                    if (isNew)
                        db.ICSOpFile.InsertOnSubmit(line);
                    db.SubmitChanges();
                }

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

        #region select
        public static ICSToCheck select(String guid, String Appconstr)
        {
            FramDataContext db = new FramDataContext(Appconstr);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                var line = db.ICSToCheck.SingleOrDefault(a => a.Serial == guid);
                return (ICSToCheck)line;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        #endregion

        #region FindAll
        public static DataTable FindAll(string Appconstr)
        {
            try
            {
                string sql = @"SELECT 
                            a.TransNO as TransNO,
                            c.EnumText as FileType,
                            b.FileName,
                            b.MUSERName,
                            b.MTIME
                            FROM ICSOpFile a
                            LEFT JOIN ICSOpFileDetail b  ON a.TransNO=b.TransNO
                            , Sys_EnumValues c 
                            WHERE  c.EnumKey='00007'
                        ";
                return DBHelper.ExecuteDataset(Appconstr, CommandType.Text, sql).Tables[0];

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region delete
        public static void delete(string ToCheckNO, string Appconstr)
        {
            SqlConnection conn = new SqlConnection(Appconstr);
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            SqlTransaction trans = conn.BeginTransaction();
            cmd.Transaction = trans;
            cmd.Connection = conn;
            try
            {
                string sqls = @"DELETE dbo.ICSToChecks WHERE ToCheckNO='" + ToCheckNO + "'";
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sqls;
                cmd.ExecuteNonQuery();

                string sql = @"DELETE dbo.ICSToCheck WHERE ToCheckNO='" + ToCheckNO + "'";
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
                trans.Commit();

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

        #region 保存PDF
        public static void AddPDF(string id, string transno,string filetype,string fname, string dsconn)
        {

            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                bool isNew = false;
                var line = db.ICSOpFileDetail.SingleOrDefault(a => a.TransNO == transno && a.FileType == filetype);
                if (line == null)
                {
                    isNew = true;
                    line = new ICSOpFileDetail();
                    line.ID = AppConfig.GetGuid();
                }
                line.TransNO = transno;
                line.FileName = fname;
                line.FileType = filetype;
                line.MTIME = AppConfig.GetSeverDateTime("yyyy-MM-dd HH:mm:ss");
                line.MUSER = AppConfig.UserId;
                line.MUSERName = AppConfig.UserName;
                line.WorkPoint = AppConfig.WorkPointCode;
                if (isNew)
                    db.ICSOpFileDetail.InsertOnSubmit(line);
                db.SubmitChanges();
                db.Transaction.Commit();
            }

            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw new Exception(ex.Message);
            }
        }
        #endregion

        #region 保存PDF
        public static void AddINPDF(string INCode, string tFileName, string dsconn)
        {
            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                var line = db.ICSINFile.SingleOrDefault(a => a.INCode == INCode);
                line.tFileName = tFileName;
                if (line == null)
                {
                    db.ICSINFile.InsertOnSubmit(line);
                }
                db.SubmitChanges();
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw new Exception(ex.Message);
            }
        }
        #endregion

        #region 删除PDF
        public static void DeletePDF(string TransNO, string dsconn)
        {
            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                var line = db.ICSOpFileDetail.Single(a => a.TransNO == TransNO);
                if (line != null)
                {
                    db.ICSOpFileDetail.DeleteOnSubmit(line);
                }                
                db.SubmitChanges();
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw new Exception(ex.Message);
            }
        }

        public static void DeletePDFAll(string TransNO, string dsconn)
        {
            SqlConnection conn = new SqlConnection(dsconn);
            SqlCommand com = conn.CreateCommand();
            SqlTransaction tran;
            conn.Open();
            tran = conn.BeginTransaction();
            com.Transaction = tran;
            try
            {
                string sql = @"DELETE FROM ICSOpFileDetail WHERE TransNO = '" + TransNO + "'";
                com.CommandText = sql;
                com.ExecuteNonQuery();

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

        public static void DeletePDF1(string id, string dsconn)
        {
            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                var line = db.ICSOpFileDetail.SingleOrDefault(a => a.ID == id);
                if (line != null)
                {
                    db.ICSOpFileDetail.DeleteOnSubmit(line);
                }
                db.SubmitChanges();
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw new Exception(ex.Message);
            }
        }

        public static void DeleteINPDF(string id, string dsconn)
        {
            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                var line = db.ICSINFile.SingleOrDefault(a => a.ID == id);
                line.tFileName = "";
                //db.ICSINFile.InsertOnSubmit(line);
                db.SubmitChanges();
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw new Exception(ex.Message);
            }
        }
        #endregion
        
        public static ICSOpFileDetail GetModel1(string id, string dsconn)
        {
            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                var line = db.ICSOpFileDetail.SingleOrDefault(a => a.ID == id);
                if (line != null)
                {
                    return line;

                }
                return null;
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw new Exception(ex.Message);
            }

        }

        public static ICSOpFileDetail GetModel(string TransNO, string dsconn)
        {
            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                var line = db.ICSOpFileDetail.SingleOrDefault(a => a.TransNO == TransNO);
                if (line != null)
                {
                    return line;
                }
                return null;
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw new Exception(ex.Message);
            }
        }

        public static DataTable GetModeldt(string TransNO, string Appconstr)
        {

            try
            {
                string lineSql = @"SELECT FileName FROM dbo.ICSOpFileDetail WHERE TransNO = '" + TransNO + "'";
                return DBHelper.ExecuteDataset(Appconstr, CommandType.Text, lineSql).Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public static ICSINFile GetINModel(string id, string dsconn)
        {
            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                var line = db.ICSINFile.SingleOrDefault(a => a.ID == id);
                if (line != null)
                {
                    return line;
                }
                return null;
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw new Exception(ex.Message);
            }
        }

        public static DataTable Check(string BarCode, string ToCheckNo, string Appconstr)
        {

            try
            {

                string lineSql = @"SELECT a.ItemCode 
				FROM ICSToCheck a        
                left join ICSWareHouseLotInfo b on a.ItemCode=b.INVCode
                 WHERE b.LotNO = '" + BarCode + "'and a.ToCheckNO='" + ToCheckNo + "'";
                return DBHelper.ExecuteDataset(Appconstr, CommandType.Text, lineSql).Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public static DataTable CheckNo(string BarCode, string Appconstr)
        {

            try
            {

                string lineSql = @"SELECT LotNO,WHCode FROM dbo.ICSWareHouseLotInfo WHERE LotNO = '" + BarCode + "'";
                return DBHelper.ExecuteDataset(Appconstr, CommandType.Text, lineSql).Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        private static DataTable SQlReturnData(SqlCommand cmd)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter dr = new System.Data.SqlClient.SqlDataAdapter();
            dr.SelectCommand = cmd;
            dr.Fill(dt);
            return dt;
        }

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

        #region 删除
        public static void delete(List<string> guidList, string Appconstr)
        {
            SqlConnection conn = new SqlConnection(Appconstr);
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            SqlTransaction trans = conn.BeginTransaction();
            cmd.Transaction = trans;
            cmd.Connection = conn;
            try
            {
                foreach(var guid in guidList)
                {
                    string sql = @"DELETE FROM ICSOpFile WHERE TransNO='{0}' and  WorkPoint='{1}'
                                   DELETE FROM ICSOpFileDetail  WHERE TransNO='{0}' AND WorkPoint='{1}'";
                    sql = string.Format(sql,guid,AppConfig.WorkPointCode);
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();
                }
                cmd.Transaction.Commit();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                throw ex;
            }
        }
        #endregion

        public static void AddINList(List<ICSINFile> datas, string dsconn)
        {
            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                foreach (ICSINFile data in datas)
                {
                    bool isNew = false;
                    var line = db.ICSINFile.SingleOrDefault(a => a.ID == data.ID);
                    if (line == null)
                    {
                        isNew = true;
                        line = new ICSINFile();
                        line.ID = data.ID;
                    }
                    line.INCode = data.INCode;
                    line.VenBatch = data.VenBatch;
                    line.VenderLotNO = data.VenderLotNO;
                    line.LotNOs = data.LotNOs;
                    line.WorkPoint = data.WorkPoint;
                    line.MUSER = data.MUSER;
                    line.MUSERName = data.MUSERName;
                    line.MTIME = data.MTIME;
                    if (isNew)
                        db.ICSINFile.InsertOnSubmit(line);
                    db.SubmitChanges();
                }
                db.SubmitChanges();
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw new Exception(ex.Message);
            }
        }

        #region 删除
        public static void deleteIN(List<String> guidList)
        {
            FramDataContext db = new FramDataContext(AppConfig.AppConnectString);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                var lines = db.ICSINFile.Where(a => guidList.Contains(a.ID));
                db.ICSINFile.DeleteAllOnSubmit(lines);

                db.SubmitChanges();
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw ex;
            }
        }
        #endregion
    }
}