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

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

namespace ICSSoft.Frame.Data.DAL
{

    public class ICSTSDAL
    {


        #region 增加修改

        public static void AddAndEdit(List<FormICSTSERRORCAUSEUIModel> errorcauseList,List<FormICSTSERRORCODE2LOCUIModel> erCode2locList,List<FormICSTSERRORCAUSE2LOCUIModel> erCause2locList,List<FormICSTSERRORCAUSE2EPARTUIModel> erCause2epartList, string dsconn)
        {

            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                foreach (FormICSTSERRORCAUSEUIModel errorcause in errorcauseList)
                {
                    var codes = db.ICSTSERRORCAUSE.Where(a => a.ECODE == errorcause.ECODE  && a.ECGCODE == errorcause.ECGCODE  && a.TSID == errorcause.TSID);
                    if (codes.ToList().Count > 0)
                    {
                        throw new Exception("不可重复保存");
                    }
                    ICSTSERRORCAUSE line1 = new ICSTSERRORCAUSE();
                    line1.ECODE = errorcause.ECODE;
                    line1.ECSCODE = errorcause.ECSCODE;
                    line1.ECGCODE = errorcause.ECGCODE;
                    line1.ECSGCODE = errorcause.ECSGCODE;
                    line1.TSID = errorcause.TSID;
                    line1.RCARD = errorcause.RCARD;
                    line1.RCARDSEQ = errorcause.RCARDSEQ;
                    line1.MODELCODE = errorcause.MODELCODE;
                    line1.ITEMCODE = errorcause.ITEMCODE;
                    line1.MOCODE = errorcause.MOCODE;
                    line1.SHIFTDAY = errorcause.SHIFTDAY;
                    line1.MOSEQ = errorcause.MOSEQ;
                    line1.RRESCODE = errorcause.RRESCODE;
                    line1.ROPCODE = errorcause.ROPCODE;
                    line1.SOLCODE = errorcause.SOLCODE;
                    line1.DUTYCODE = errorcause.DUTYCODE;
                    line1.SOLMEMO = errorcause.SOLMEMO;
                    line1.PREVENTION = errorcause.PREVENTION;
                    line1.MUSER = errorcause.MUSER;
                    line1.MUSERName = errorcause.MUSERName;
                    line1.MTIME = errorcause.MTIME;
                    line1.WorkPoint = errorcause.WorkPoint;
                    line1.EATTRIBUTE1 = errorcause.EATTRIBUTE1;

                    db.ICSTSERRORCAUSE.InsertOnSubmit(line1);
                    db.SubmitChanges();
                    UpdateTSSTATUSByRcard(errorcause.RCARD, AppConfig.AppConnectString);
                }
                foreach (FormICSTSERRORCODE2LOCUIModel erCode2loc in erCode2locList)
                {
                    ICSTSERRORCODE2LOC line2 = new ICSTSERRORCODE2LOC();
                    line2.ECODE = erCode2loc.ECODE;
                    line2.ECGCODE = erCode2loc.ECGCODE;
                    line2.ELOC = erCode2loc.ELOC;
                    line2.AB = erCode2loc.AB;
                    line2.TSID = erCode2loc.TSID;
                    line2.SUBELOC = erCode2loc.SUBELOC;
                    line2.RCARD = erCode2loc.RCARD;
                    line2.RCARDSEQ = erCode2loc.RCARDSEQ;
                    line2.MODELCODE = erCode2loc.MODELCODE;
                    line2.ITEMCODE = erCode2loc.ITEMCODE;
                    line2.MOCODE = erCode2loc.MOCODE;
                    line2.SHIFTDAY = erCode2loc.SHIFTDAY;
                    line2.MOSEQ = erCode2loc.MOSEQ;
                    line2.MEMO = erCode2loc.MEMO;
                    line2.MUSER = erCode2loc.MUSER;
                    line2.MUSERName = erCode2loc.MUSERName;
                    line2.MTIME = erCode2loc.MTIME;
                    line2.WorkPoint = erCode2loc.WorkPoint;
                    line2.EATTRIBUTE1 = erCode2loc.EATTRIBUTE1;


                    db.ICSTSERRORCODE2LOC.InsertOnSubmit(line2);
                    db.SubmitChanges();
                }
                foreach (FormICSTSERRORCAUSE2LOCUIModel erCause2loc in erCause2locList)
                {
                    ICSTSERRORCAUSE2LOC line3 = new ICSTSERRORCAUSE2LOC();
                    line3.ECSCODE = erCause2loc.ECSCODE;
                    line3.ECGCODE = erCause2loc.ECGCODE;
                    line3.ECODE = erCause2loc.ECODE;
                    line3.ELOC = erCause2loc.ELOC;
                    line3.AB = erCause2loc.AB;
                    line3.TSID = erCause2loc.TSID;
                    line3.SUBELOC = erCause2loc.SUBELOC;
                    line3.RCARD = erCause2loc.RCARD;
                    line3.RCARDSEQ = erCause2loc.RCARDSEQ;
                    line3.MODELCODE = erCause2loc.MODELCODE;
                    line3.ITEMCODE = erCause2loc.ITEMCODE;
                    line3.MOCODE = erCause2loc.MOCODE;
                    line3.RRESCODE = erCause2loc.RRESCODE;
                    line3.ROPCODE = erCause2loc.ROPCODE;
                    line3.ECSGCODE = erCause2loc.ECSGCODE;
                    line3.MOSEQ = erCause2loc.MOSEQ;
                    line3.EPART = erCause2loc.EPART;
                    line3.MUSER = erCause2loc.MUSER;
                    line3.MUSERName = erCause2loc.MUSERName;
                    line3.MTIME = erCause2loc.MTIME;
                    line3.WorkPoint = erCause2loc.WorkPoint;
                    line3.EATTRIBUTE1 = erCause2loc.EATTRIBUTE1;


                    db.ICSTSERRORCAUSE2LOC.InsertOnSubmit(line3);
                    db.SubmitChanges();
                }
                foreach (FormICSTSERRORCAUSE2EPARTUIModel erCause2epart in erCause2epartList)
                {
                    ICSTSERRORCAUSE2EPART line4 = new ICSTSERRORCAUSE2EPART();
                    line4.TSID = erCause2epart.TSID;
                    line4.ECODE = erCause2epart.ECODE;
                    line4.ECSCODE = erCause2epart.ECSCODE;
                    line4.ECGCODE = erCause2epart.ECGCODE;
                    line4.EPART = erCause2epart.EPART;
                    line4.RCARD = erCause2epart.RCARD;
                    line4.RCARDSEQ = erCause2epart.RCARDSEQ;
                    line4.MODELCODE = erCause2epart.MODELCODE;
                    line4.ITEMCODE = erCause2epart.ITEMCODE;
                    line4.MOCODE = erCause2epart.MOCODE;
                    line4.ECSGCODE = erCause2epart.ECSGCODE;
                    line4.MOSEQ = erCause2epart.MOSEQ;
                    line4.RRESCODE = erCause2epart.RRESCODE;
                    line4.ROPCODE = erCause2epart.ROPCODE;
                    line4.MUSER = erCause2epart.MUSER;
                    line4.MUSERName = erCause2epart.MUSERName;
                    line4.MTIME = erCause2epart.MTIME;
                    line4.WorkPoint = erCause2epart.WorkPoint;
                    line4.EATTRIBUTE1 = erCause2epart.EATTRIBUTE1;

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

       
        #region 班次代码是否存在
        public static bool IsIncludingShiftCode(string shiftcode, string dsconn)
        {

            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                var line = db.ICSSHIFT.SingleOrDefault(a => a.SHIFTCODE == shiftcode);
                if (line == null)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw ex;
            }

        }
        #endregion

        public static void AddECode(ICSTSERRORCODE errorCodeInfo, string Appconstr)
        {
            FramDataContext db = new FramDataContext(Appconstr);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                var line1 = db.ICSTSERRORCODE.Where(a => a.ECODE == errorCodeInfo.ECODE && a.ECGCODE == errorCodeInfo.ECGCODE && a.TSID == errorCodeInfo.TSID);
                if (line1.ToList().Count>0)
                {
                   throw new Exception("不良代码已经存在"); 
                }

                ICSTSERRORCODE line = new ICSTSERRORCODE();
                line.ECODE = errorCodeInfo.ECODE;
                line.ECGCODE = errorCodeInfo.ECGCODE;
                line.TSID = errorCodeInfo.TSID;
                line.RCARD = errorCodeInfo.RCARD;
                line.RCARDSEQ = errorCodeInfo.RCARDSEQ;
                line.MODELCODE = errorCodeInfo.MODELCODE;
                line.ITEMCODE = errorCodeInfo.ITEMCODE;
                line.MOCODE = errorCodeInfo.MOCODE;
                line.MOSEQ = errorCodeInfo.MOSEQ;
                line.SHIFTDAY = errorCodeInfo.SHIFTDAY;
                line.MUSER = errorCodeInfo.MUSER;
                line.MUSERName = errorCodeInfo.MUSERName;
                line.MTIME = errorCodeInfo.MTIME;
                line.WorkPoint = errorCodeInfo.WorkPoint;
                line.EATTRIBUTE1 = errorCodeInfo.EATTRIBUTE1;

                db.ICSTSERRORCODE.InsertOnSubmit(line);
                //UpdateTSSTATUS(errorCodeInfo.RCARD, Appconstr);
                db.SubmitChanges();
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw new Exception(ex.Message);
            }
        }

        #region 产品序列号是否存在并且需要维修
        public static bool IsIncludingInICSTS(string rcard)
        {

            FramDataContext db = new FramDataContext(AppConfig.AppConnectString);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                var line = db.ICSTS.SingleOrDefault(a => a.RCARD == rcard && (a.TSSTATUS == "tsstatus_confirm" || a.TSSTATUS == "tsstatus_ts"));
                if (line == null)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw ex;
            }

        }
        #endregion

        #region 班次代码是否在时段维护中使用
        public static bool isIncludingInICSTP(List<string> idList, string dsconn)
        {
            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                bool flag = false;
                var lines = db.ICSTP.Where(a => idList.Contains(a.SHIFTID));
                if (lines.Count() != 0)
                    flag = true;

                return flag;
            }
            catch (Exception ex)
            {
                //db.Transaction.Rollback();
                throw ex;
            }

        }
        #endregion


        public static ICSTS SearchTSInfoByRcard(string rcard, string dsconn)
        {
            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            ICSTS entity = new ICSTS();
            try
            {
                var line = db.ICSTS.SingleOrDefault(a => a.RCARD == rcard);
                return (ICSTS)line;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public static DataTable SelectShiftTypeCode()
        {
            string sql = @"select SHIFTTYPECODE as [班制代码]
            from dbo.ICSSHIFTTYPE
            where 1=1";
            sql = string.Format(sql);
            DataTable dt = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0];
            return dt;
        }

        public static DataTable SelectShiftTypeId(string str)
        {
            string sql = @"select ID
            from dbo.ICSSHIFTTYPE
            where SHIFTTYPECODE='" + str + "'";
            sql = string.Format(sql);
            DataTable dt = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0];
            return dt;
        }

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

                db.SubmitChanges();
                db.Transaction.Commit();

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

        public static DataTable GetShiftCode()
        {
            try
            {
                string sql = @"select TOP 1 [SHIFTCODE]
  FROM [dbo].[ICSSHIFT] order by SHIFTCODE desc";
                return DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        //        public static DataTable GetShiftSeqCode()
//        {
//            try
//            {
//                string sql = @"select TOP 1 [SHIFTSEQ]
//  FROM [dbo].[ICSSHIFT] order by [SHIFTTYPEID] desc";
//                return DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0];
//            }
//            catch (Exception ex)
//            {
//                throw ex;
//            }

//        }


        #region 通过ID更新维修状态
        public static void UpdateTSSTATUSByID(string id, string TSSTATUS, string dsconn)
        {
            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                var line = db.ICSTS.SingleOrDefault(a => a.TSID == id);
                line.CONFIRMUSER = AppConfig.UserCode;

                line.CONFIRMTIME = AppConfig.GetSeverDateTime("yyyy-MM-dd hh:mm:ss");
                line.COPCODE = "";
                line.CRESCODE = "";
                line.TSSTATUS = "tsstatus_confirm";
                line.MUSER = AppConfig.UserCode;
                line.MUSERName = AppConfig.UserName;
                db.SubmitChanges();
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw new Exception(ex.Message);
            }
        }
        #endregion
        #region 通过产品序列号更新维修状态
        public static void UpdateTSSTATUSByRcard(string rcard, string dsconn)
        {
            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                var line = db.ICSTS.SingleOrDefault(a => a.RCARD == rcard);
                line.TSSTATUS = "tsstatus_ts";
                db.SubmitChanges();
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw new Exception(ex.Message);
            }
        }
        #endregion
        #region 状态返回送修完
        public static void UpdateTSSTATUS(string rcard, string dsconn)
        {
            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                var line = db.ICSTS.SingleOrDefault(a => a.RCARD == rcard && a.TSSTATUS == "tsstatus_ts");
                if (line == null)
                {
                    return;
                }
                line.TSSTATUS = "tsstatus_confirm";
                db.SubmitChanges();
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw new Exception(ex.Message);
            }
        }
        #endregion

        }
    }