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

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

namespace ICSSoft.Frame.Data.DAL
{
    public class ICSUserInfoDAL
    {
        public static void DeleteThenInsert(List<ICSUserInfo> list)
        {
            FramDataContext db = new FramDataContext(AppConfig.AppConnectString);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                var lines = db.ICSUserInfo.Where(a => a.UserCode == list[0].UserCode && a.WorkPoint == list[0].WorkPoint);
                db.ICSUserInfo.DeleteAllOnSubmit(lines);
                db.SubmitChanges();
                db.ICSUserInfo.InsertAllOnSubmit(list);
                db.SubmitChanges();
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw ex;
            }
            finally
            {
                db.Connection.Close();
            }

        }

        public static int[] AddAndEdit(List<ICSUserInfo> users)
        {
            int insert = 0;
            int update = 0;
            FramDataContext db = new FramDataContext(AppConfig.AppConnectString);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                foreach (ICSUserInfo user in users)
                {
                    bool isNew = false;
                    var line = db.ICSUserInfo.FirstOrDefault(a => a.UserCode == user.UserCode && a.WorkPoint == AppConfig.WorkPointCode);
                    //var line = db.ICSUserInfo.FirstOrDefault(a => a.UserCode == user.UserCode && a.SEGCode == user.SEGCode && a.SGroup == user.SGroup && a.WorkPoint == AppConfig.WorkPointCode);
                    if (line == null)
                    {
                        isNew = true;
                        line = new ICSUserInfo();
                        line.ID = AppConfig.GetGuid();
                    }
                    line.UserCode = user.UserCode;
                    line.SEGCode = user.SEGCode;
                    line.SGroup = user.SGroup;
                    line.FrontLine = user.FrontLine;
                    line.Phone = user.Phone;
                    line.Mail = user.Mail;
                    line.ModifyDate = DateTime.Now;
                    line.ModifyUserCode = AppConfig.UserCode;
                    line.WorkPoint = AppConfig.WorkPointCode;
                    if (isNew)
                    {
                        insert++;
                        line.CreateDate = line.ModifyDate;
                        line.CreateUserCode = line.ModifyUserCode;
                        db.ICSUserInfo.InsertOnSubmit(line);
                    }
                    else
                    {
                        update++;
                    }
                    db.SubmitChanges();
                }
                db.SubmitChanges();
                db.Transaction.Commit();
                return new int[2] { insert, update };
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw ex;
            }
            finally
            {
                db.Connection.Close();
            }
        }

        /// <summary>
        /// 删除用户所有关联的车间
        /// </summary>
        /// <param name="listUserCode"></param>
        public static void DeleteAllLine(List<string> listUserCode)
        {
            FramDataContext db = new FramDataContext(AppConfig.AppConnectString);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                var lines = db.ICSUserInfo.Where(a => listUserCode.Contains(a.UserCode) && a.WorkPoint == AppConfig.WorkPointCode);
                db.ICSUserInfo.DeleteAllOnSubmit(lines);

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

        /// <summary>
        /// 删除用户一条关联的车间
        /// </summary>
        /// <param name="listUserCode"></param>
        public static void DeleteOneLine(List<string> listID)
        {
            FramDataContext db = new FramDataContext(AppConfig.AppConnectString);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                var lines = db.ICSUserInfo.Where(a => listID.Contains(a.ID) && a.WorkPoint == AppConfig.WorkPointCode);
                db.ICSUserInfo.DeleteAllOnSubmit(lines);

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

        public static DataTable Select(string UserCode)
        {
            string sql = @"
SELECT
    convert(bit,1) as isSelect,
    A.ID,
	A.UserCode,B.UserName,
	A.FrontLine,
	A.SEGCODE,C.SEGDESC,
	A.SGroup,
	A.Phone,
	A.Mail,
    A.CreateUserCode,A.CreateDate,
	A.ModifyUserCode,A.ModifyDate ,
    CONVERT(BIT,0) AS [ADD]
FROM dbo.ICSUserInfo A
LEFT JOIN dbo.Sys_User B ON A.UserCode=B.UserCode AND A.WorkPoint=B.WorkPointCode
LEFT JOIN dbo.ICSSEG C ON A.SEGCODE=C.SEGCODE  AND A.WorkPoint=C.WorkPoint
WHERE A.UserCode='{0}' AND A.WorkPoint ='" + AppConfig.WorkPointCode + "'";
            sql = string.Format(sql, UserCode == null ? "" : UserCode);
            DataSet ds = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql);
            if (ds.Tables.Count == 0)
            {
                return null;
            }
            return ds.Tables[0];
        }


        public static bool AllowAdd(string UserCode)
        {
            FramDataContext db = new FramDataContext(AppConfig.AppConnectString);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                var line = db.ICSUserInfo.SingleOrDefault(a => a.UserCode == UserCode && a.WorkPoint == AppConfig.WorkPointCode);
                return line == null;
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw ex;
            }
        }
        public static bool ExistSGroup(string Sgroup)
        {
            string sql = @"SELECT 1 FROM dbo.Sys_EnumValues WHERE EnumKey='00022' AND StartFlag=1 AND WorkPointCode='" + AppConfig.WorkPointCode + "' AND EnumValue='{0}'";
            sql = string.Format(sql, Sgroup);
            DataSet ds = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql);
            if (ds.Tables.Count == 0)
            {
                return false;
            }
            if (ds.Tables[0].Rows.Count == 0)
            {
                return false;
            }
            return true;
        }

        public static bool ExistUserCode(string userCode)
        {
            string sql = @"SELECT 1 FROM dbo.Sys_User WHERE UserCode ='{0}' AND WorkPointCode='" + AppConfig.WorkPointCode + "'";
            sql = string.Format(sql, userCode);
            DataSet ds = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql);
            if (ds.Tables.Count == 0)
            {
                return false;
            }
            if (ds.Tables[0].Rows.Count == 0)
            {
                return false;
            }
            return true;
        }

        public static string QueryTeamUsed(List<string> teamList)
        {
            string sql = @"SELECT STUFF((SELECT ',' + T.SGroup FROM  (SELECT DISTINCT SGroup FROM dbo.ICSUserInfo WHERE SGroup IN('{0}') AND WorkPoint='{1}')T  FOR XML PATH('')), 1, 1, '') AS sgs ";
            sql = string.Format(sql, string.Join("','", teamList), AppConfig.WorkPointCode);
            DataTable dt = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0];
            return dt.Rows[0][0].ToString();
        }

        public static DataTable QueryUsersInTeams(List<string> teamList)
        {
            string sql = @"
            SELECT DISTINCT 
                A.SGroup,
                STUFF((SELECT ',' + UserCode FROM dbo.ICSUserInfo WHERE SGroup=A.SGroup AND WorkPoint='{1}'  FOR XML PATH('')), 1, 1, '') AS USERS 
            FROM ICSUserInfo A
            WHERE A.SGroup in ('{0}') AND A.WorkPoint='{1}'
            ";
            sql = string.Format(sql, string.Join("','", teamList), AppConfig.WorkPointCode);
            return DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0];
        }

        public static void Test(string testCode)
        {
            FramDataContext db = new FramDataContext(AppConfig.AppConnectString);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                IDbCommand cmd = db.GetCommand(db.ICSITEMLot);
                cmd.CommandText = "SELECT * FROM dbo.ICSITEMLot WHERE LotNO='55A22022011000000004100001'";
                cmd.CommandType = CommandType.Text;
                cmd.Connection = db.Connection;
                IDataReader r = cmd.ExecuteReader();

                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw ex;
            }
            finally
            {
                db.Connection.Close();
            }

        }

    }
}