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



        #region 增加修改

        public static void AddAndEdit(DataTable dt, string ecsgid, string ecsgcode, 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++)
                {
                    ICSECSG2ECS line = new ICSECSG2ECS();
                        line.ECSGID = ecsgid;
                        line.ECSID = dt.Rows[i]["ID"].ToString();
                    
                    line.ECSGCODE = ecsgcode;
                    line.ECSCODE = 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.ICSECSG2ECS.InsertOnSubmit(line);
                }
                   
                db.SubmitChanges();
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw new Exception(ex.Message);
            }
            
                      
        }
         #endregion




        public static List<FormICSECSG2ECSUIModel> SearchEcg2EcInfoByCode(string ecsgid, string ecsid, string dsconn)
        {
            List<FormICSECSG2ECSUIModel> returnshift = new List<FormICSECSG2ECSUIModel>();
            string sql = @"select b.ID as ECSGID,
                         c.ID as ECSID,
                         b.ECSGCODE as ECSGCODE,
                         c.ECSCODE as ECSCODE,
                         a.MUSERName as MUSERName,
                         a.MTIME as MTIME
            from ICSECSG2ECS a
            left join ICSECSG b on a.ECSGID=b.ID
            left join ICSECS c on a.ECSID=c.ID

           where b.ID='" + ecsgid + "'and c.ID='" + ecsid + "'";
            sql = string.Format(sql);
            DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
            foreach (DataRow dr in dt.Rows)
            {
                FormICSECSG2ECSUIModel shiftInfo = new FormICSECSG2ECSUIModel();
                
                shiftInfo.ecsg = new FormICSECSGUIModel();

                shiftInfo.ecsg.ID = dr["ECSGID"].ToString();
                shiftInfo.ecsg.ECSGCODE = dr["ECSGCODE"].ToString();
                shiftInfo.ecs = new FormICSECSUIModel();
                shiftInfo.ecs.ID = dr["ECSID"].ToString();
                shiftInfo.ecs.ECSCODE = dr["ECSCODE"].ToString();
                shiftInfo.MUSERName = dr["MUSERName"].ToString();
                shiftInfo.MTIME = System.DateTime.Parse(dr["MTIME"].ToString());
                
                if (!returnshift.Contains(shiftInfo))
                {
                    returnshift.Add(shiftInfo);
                }

            }
            return returnshift;

        }



        #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.ICSECSG2ECS.Where(a => guidList.Contains(a.ECSGID) && guidList.Contains(a.ECSID));
                db.ICSECSG2ECS.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;
            }
        }


        }
    }