RuiTengJb/ICSSoft.AppSln/ICSSoft.Frame.Data.DAL/ICSECG2ECDAL.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 ICSECG2ECDAL
    {



        #region 增加修改

        public static void AddAndEdit(DataTable dt, string ecgid, string ecgcode, string Appconstr)
        {

            FramDataContext db = new FramDataContext(Appconstr);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    ICSECG2EC line = new ICSECG2EC();
                        line.ECGID = ecgid;
                        line.ECID = dt.Rows[i]["ID"].ToString();
                    
                    line.ECGCODE = ecgcode;
                    line.ECODE = dt.Rows[i]["不良代码"].ToString();

                    line.MUSER = AppConfig.UserId;
                    line.MUSERName = AppConfig.UserName;
                    line.WorkPoint = AppConfig.WorkPointCode;
                    line.MTIME = AppConfig.GetSeverDateTime("yyyy-MM-dd HH:mm:ss");
                    db.ICSECG2EC.InsertOnSubmit(line);
                }
                   
                db.SubmitChanges();
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw new Exception(ex.Message);
            }
            
                      
        }
         #endregion




        public static List<FormICSECG2ECUIModel> SearchEcg2EcInfoByCode(string ecgid, string ecid, string dsconn)
        {
            List<FormICSECG2ECUIModel> returnshift = new List<FormICSECG2ECUIModel>();
            string sql = @"select b.ID as ECGID,
c.ID as ECID,
b.ECGCODE as ECGCODE,
c.ECODE as ECODE,

a.MUSERName as MUSERName,
a.MTIME as MTIME


from ICSECG2EC a
left join ICSECG b on a.ECGID=b.ID
left join ICSEC c on a.ECID=c.ID

           where b.ID='" + ecgid + "'and c.ID='" + ecid + "'";
            sql = string.Format(sql);
            DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
            foreach (DataRow dr in dt.Rows)
            {
                FormICSECG2ECUIModel shiftInfo = new FormICSECG2ECUIModel();
                
                shiftInfo.ecg = new FormICSECGUIModel();

                shiftInfo.ecg.ID = dr["ECGID"].ToString();
                shiftInfo.ecg.ECGCODE = dr["ECGCODE"].ToString();
                shiftInfo.ec = new FormICSECUIModel();
                shiftInfo.ec.ID = dr["ECID"].ToString();
                shiftInfo.ec.ECODE = dr["ECODE"].ToString();
                shiftInfo.MUSERName = dr["MUSERName"].ToString();
                shiftInfo.MTIME = System.DateTime.Parse(dr["MTIME"].ToString());
                
                if (!returnshift.Contains(shiftInfo))
                {
                    returnshift.Add(shiftInfo);
                }

            }
            return returnshift;

        }


//        public static List<FormICSOPUIModel> SearchOPInfoList(string str,string dsconn)
//        {
//            try
//            {
//                List<FormICSOPUIModel> returntype = new List<FormICSOPUIModel>();
//                string sql = @"select a.OPCODE,a.OPDESC
//                    from dbo.ICSITEMROUTE2OP b
//                    left join dbo.ICSOP a on b.OPID=a.ID
//                    WHERE ITEMCODE='" + str + "'";
//                sql = string.Format(sql);
//                DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
//                foreach (DataRow dr in dt.Rows)
//                {
//                    FormICSOPUIModel typemodel = new FormICSOPUIModel();

//                    typemodel.OPCODE = dr["OPCODE"].ToString();
//                    typemodel.OPDESC = dr["OPDESC"].ToString();
//                    if (!returntype.Contains(typemodel))
//                        returntype.Add(typemodel);
//                }
//                return returntype;

//            }
//            catch (Exception ex)
//            {
//                throw ex;
//            }
        //        }

        #region 根据不良代码组代码查找不良代码组id
        public static DataTable SelectECGId(string str, string wp)
        {
            string sql = @"select ID
            from dbo.ICSECG
            where ECGCODE='" + str + "' and WorkPoint='" + wp + "'";
            sql = string.Format(sql);
            DataTable dt = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0];
            return dt;
        }

        #endregion

        #region 根据不良代码代码查找不良代码id
        public static DataTable SelectECId(string str, string wp)
        {
            string sql = @"select ID
            from dbo.ICSEC
            where ECODE='" + str + "'and WorkPoint='" + wp + "'";
            sql = string.Format(sql);
            DataTable dt = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0];
            return dt;
        }

        #endregion

      

        

        
         #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.ICSECG2EC.Where(a => guidList.Contains(a.ECGID) && guidList.Contains(a.ECID));
                db.ICSECG2EC.DeleteAllOnSubmit(lines);

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

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

        public static void Save(List<string> ecidList, List<string> ecgidList)
        {
            try
            {
                for (int i = 0; i < ecidList.Count; i++)
                {
                    string sql = @"UPDATE dbo.ICSECG2EC SET ECGID='{0}' WHERE ECID='{1}'";
                    sql = string.Format(sql,ecgidList[i],ecidList[i]);
                    DBHelper.ExecuteNonQuery(AppConfig.AppConnectString, CommandType.Text, sql);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        }
    }