RuiTengJb/ICSSoft.AppSln/ICSSoft.Frame.Data.DAL/ICSSSDAL.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 ICSSoft.Base.Config.AppConfig;

namespace ICSSoft.Frame.Data.DAL
{
    public class ICSSSDAL
    {
        public static void AddAndEdit(List<ICSSS> mandayInfoList, string dsconn)
        {

            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                foreach (ICSSS mandayInfo in mandayInfoList)
                {


                    ///jia pan duan 

                    bool isNew = false;
                    var line = db.ICSSS.SingleOrDefault(a => a.ID == mandayInfo.ID);
                    if (line == null)
                    {
                        isNew = true;
                        line = new ICSSS();
                        line.ID = AppConfig.GetGuid();
                    }
                    line.SSCODE = mandayInfo.SSCODE;
                    line.SSDESC = mandayInfo.SSDESC;
                    line.SHIFTTYPEID = "";
                    line.SEGID = mandayInfo.SEGID;
                    line.SSTYPE = "";
                    line.MUSER = mandayInfo.MUSER;
                    line.MTIME = mandayInfo.MTIME;
                    line.MUSERName = mandayInfo.MUSERName;
                    line.WorkPoint = mandayInfo.WorkPoint;
                    if (isNew)
                        db.ICSSS.InsertOnSubmit(line);
                    db.SubmitChanges();
                }

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

        public static List<ICSSS> SearchPersonInfoByCode(string personCode, string dsconn)
        {
            List<ICSSS> returnperson = new List<ICSSS>();
            string sql = @"select ID,SSCODE,SSDESC,SEGID,SSTYPE,SHIFTTYPEID,MUSER,MUSERName,MTIME
            from ICSSS
            where ID='{0}' and WorkPoint='"+AppConfig.WorkPointCode+"'";
            sql = string.Format(sql, personCode);
            DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
            foreach (DataRow dr in dt.Rows)
            {
                ICSSS line = new ICSSS();
                line.ID = dr["ID"].ToString();
                line.SSCODE = dr["SSCODE"].ToString();
                line.SHIFTTYPEID = dr["SHIFTTYPEID"].ToString();
                line.SSDESC = dr["SSDESC"].ToString();
                line.SEGID = dr["SEGID"].ToString();
                line.SSTYPE = dr["SSTYPE"].ToString();
                line.MUSERName = dr["MUSERName"].ToString();
                line.MUSER = dr["MUSER"].ToString();
                line.MTIME = System.DateTime.Parse(dr["MTIME"].ToString());
                returnperson.Add(line);

            }
            return returnperson;
        }

        public static DataTable SelectShiftTypeCode()
        {
            string sql = @"select SHIFTTYPECODE as [班制代码]
            from dbo.ICSSHIFTTYPE
            where WorkPoint='" + AppConfig.WorkPointCode + "'";
            sql = string.Format(sql);
            DataTable dt = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0];
            return dt;
        }
        public static DataTable SelectShiftTypeCode(string str)
        {
            string sql = @"select SHIFTTYPECODE as [班制代码]
            from dbo.ICSSHIFTTYPE
            where ID='" + str + "' and WorkPoint='" + AppConfig.WorkPointCode + "'";
            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 + "' and WorkPoint='" + AppConfig.WorkPointCode + "'";
            sql = string.Format(sql);
            DataTable dt = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0];
            return dt;
        }
        public static DataTable SelectSegCode()
        {
            string sql = @"select SEGCODE as [车间代码]
            from dbo.ICSSEG
            where WorkPoint='" + AppConfig.WorkPointCode + "'";
            sql = string.Format(sql);
            DataTable dt = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0];
            return dt;
        }
        public static DataTable SelectSegCode(string str)
        {
            string sql = @"select SEGCODE as [车间代码]
            from dbo.ICSSEG
            where ID='" + str + "' and WorkPoint='" + AppConfig.WorkPointCode + "'";
            sql = string.Format(sql);
            DataTable dt = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0];
            return dt;
        }
        public static DataTable SelectSegId(string str)
        {
            string sql = @"select ID
            from dbo.ICSSEG
            where SEGCODE='" + str + "' and WorkPoint='" + AppConfig.WorkPointCode + "'";
            sql = string.Format(sql);
            DataTable dt = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0];
            return dt;
        }

        public static DataTable SelectShiftTypeId1(string str)
        {
            string sql = @"select SHIFTTYPEID
            from dbo.ICSSEG
            where SEGCODE='" + str + "' and WorkPoint='" + AppConfig.WorkPointCode + "'";
            sql = string.Format(sql);
            DataTable dt = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0];
            return dt;
        }

        public static DataTable SelectICSSSCode()
        {
            string sql = @"select SSCODE
            from dbo.ICSSS
            where WorkPoint='" + AppConfig.WorkPointCode + "'";
            sql = string.Format(sql);
            DataTable dt = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0];
            return dt;
        }

        public static void deleteInfo(string dsconn, List<string> codeList)
        {

            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                var lines = db.ICSSS.Where(a => codeList.Contains(a.ID));
                db.ICSSS.DeleteAllOnSubmit(lines);

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

        }
    }
}