ERP/TradeData/JC_DepartMentService.cs

using System;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Data.SqlClient;
using System.Collections.Generic;
using NetLibrary;
using NetLibrary.Data;
using NetLibrary.ReportPrint;
using TradeModel;

namespace TradeData
{
    public class JC_DepartMentService 
    {
        #region 保存
        public static int Save(JC_DepartMent Model)
        {
            string tsql= @"
if @DeptId>0
begin
Update [JC_DepartMent] set [DeptCode]=@DeptCode,[DeptName]=@DeptName,[ParentDeptId]=@ParentDeptId,[Phone]=@Phone,[Fax]=@Fax,[SortNo]=@SortNo,[CompanyId]=@CompanyId,DeptType=@DeptType where DeptId=@DeptId
end
else
begin
INSERT INTO [JC_DepartMent]([DeptCode],[DeptName],[ParentDeptId],[Phone],[Fax],[SortNo],[CompanyId],DeptType)values(@DeptCode,@DeptName,@ParentDeptId,@Phone,@Fax,@SortNo,@CompanyId,@DeptType)
set @DeptId=SCOPE_IDENTITY()
end
select @DeptId";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            db.AddInParameter(cmd,"@DeptId", DbType.Int32,Model.DeptId);
            db.AddInParameter(cmd,"@DeptCode", DbType.String,Model.DeptCode);
            db.AddInParameter(cmd, "@DeptType", DbType.String, Model.DeptType);
            db.AddInParameter(cmd,"@DeptName", DbType.String,Model.DeptName);
            db.AddInParameter(cmd,"@ParentDeptId", DbType.Int32,Model.ParentDeptId);
            db.AddInParameter(cmd,"@Phone", DbType.String,Model.Phone);
            db.AddInParameter(cmd,"@Fax", DbType.String,Model.Fax);
            db.AddInParameter(cmd, "@SortNo", DbType.String, Model.SortNo);
            db.AddInParameter(cmd,"@CompanyId", DbType.Int32,Model.CompanyId);
            int a=Convert.ToInt32(db.ExecuteScalar(cmd));
            return a;
        }
        #endregion

        #region 保存
        public static int SaveForTM(JC_DepartMent Model)
        {
            string tsql = @"
if @DeptId>0
begin
Update [JC_DepartMent] set [DeptCode]=@DeptCode,[DeptName]=@DeptName,[ParentDeptId]=@ParentDeptId,[Phone]=@Phone,[Fax]=@Fax,[SortNo]=@SortNo,[CompanyId]=@CompanyId,DeptType=@DeptType,MasterName=@MasterName where DeptId=@DeptId
end
else
begin
INSERT INTO [JC_DepartMent]([DeptCode],[DeptName],[ParentDeptId],[Phone],[Fax],[SortNo],[CompanyId],DeptType,MasterName)values(@DeptCode,@DeptName,@ParentDeptId,@Phone,@Fax,@SortNo,@CompanyId,@DeptType,@MasterName)
set @DeptId=SCOPE_IDENTITY()
end
select @DeptId";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            db.AddInParameter(cmd, "@DeptId", DbType.Int32, Model.DeptId);
            db.AddInParameter(cmd, "@DeptCode", DbType.String, Model.DeptCode);
            db.AddInParameter(cmd, "@DeptType", DbType.String, Model.DeptType);
            db.AddInParameter(cmd, "@DeptName", DbType.String, Model.DeptName);
            db.AddInParameter(cmd, "@ParentDeptId", DbType.Int32, Model.ParentDeptId);
            db.AddInParameter(cmd, "@Phone", DbType.String, Model.Phone);
            db.AddInParameter(cmd, "@Fax", DbType.String, Model.Fax);
            db.AddInParameter(cmd, "@SortNo", DbType.String, Model.SortNo);
            db.AddInParameter(cmd, "@CompanyId", DbType.Int32, Model.CompanyId);
            db.AddInParameter(cmd, "@MasterName", DbType.String, Model.MasterName);
            int a = Convert.ToInt32(db.ExecuteScalar(cmd));
            return a;
        }
        #endregion
        #region 删除
        public static void Delete(int DeptId)
        {
            string tsql= @"
delete from JC_DepartMent where DeptId=@DeptId
update JC_UserInfo set DeptId=0 where   DeptId=@DeptId
";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            db.AddInParameter(cmd,"@DeptId", DbType.Int32,DeptId);
            db.ExecuteNonQuery(cmd);
        }
        #endregion
        #region 返回Model
        public static JC_DepartMent GetModel(int DeptId)
        {
            JC_DepartMent model = null;
            string tsql="select * from JC_DepartMent where DeptId=@DeptId";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            db.AddInParameter(cmd,"@DeptId", DbType.Int32,DeptId);
            DataTable tb=db.ExecuteDataTable(cmd);
            if (tb.Rows.Count>0)model=tb.Rows[0].ToModel<JC_DepartMent>();
            return model;
        }
        #endregion
        #region 分页查询
         public static List<JC_DepartMent> GetListJC_DepartMent(RefParameterCollection where, int PageIndex, int PageSize, string Sort, out int RowCount)
        {
            if (where == null) where = new RefParameterCollection();
            QueryService ser = new QueryService();
            ser.Fields = @"DeptId=cast(a.DeptId as int),a.DeptCode,a.DeptName,a.ParentDeptId,a.Phone,a.Fax,a.SortNo,a.CompanyId,a.DeptType";
            ser.Tables = @"JC_DepartMent a";
            ser.Filter = where.GetWhere(System.Data.CommandType.Text);
            ser.PageIndex = PageIndex;
            ser.PageSize = PageSize;
            ser.Sort = Sort;
            ser.KeyName = "DeptId";
            string tsql=ser.GetText();
            where.AddOutParameter("RowCount",System.Data.DbType.Int32);;
            List<JC_DepartMent> ListModel = null;
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            db.AddInParameter(cmd, where);
            DataTable tb=db.ExecuteDataTable(cmd);
            RowCount = Convert.ToInt32(cmd.Parameters["@RowCount"].Value);
            ListModel=tb.ToList<JC_DepartMent>();
            return ListModel;
        }
        #endregion

         #region 分页查询
         public static List<JC_DepartMent> GetListJC_DepartMentForTM(RefParameterCollection where, int PageIndex, int PageSize, string Sort, out int RowCount)
         {
             if (where == null) where = new RefParameterCollection();
             QueryService ser = new QueryService();
             ser.Fields = @"DeptId=cast(a.DeptId as int),a.DeptCode,a.DeptName,a.ParentDeptId,a.Phone,a.Fax,a.SortNo,a.CompanyId,a.DeptType,a.MasterName";
             ser.Tables = @"JC_DepartMent a";
             ser.Filter = where.GetWhere(System.Data.CommandType.Text);
             ser.PageIndex = PageIndex;
             ser.PageSize = PageSize;
             ser.Sort = Sort;
             ser.KeyName = "DeptId";
             string tsql = ser.GetText();
             where.AddOutParameter("RowCount", System.Data.DbType.Int32); ;
             List<JC_DepartMent> ListModel = null;
             Database db = DatabaseFactory.CreateDatabase();
             DbCommand cmd = db.GetSqlStringCommand(tsql);
             db.AddInParameter(cmd, where);
             DataTable tb = db.ExecuteDataTable(cmd);
             RowCount = Convert.ToInt32(cmd.Parameters["@RowCount"].Value);
             ListModel = tb.ToList<JC_DepartMent>();
             return ListModel;
         }
         #endregion

         #region 判断名称是否重复
         public static bool CheckDeptName(int DeptID, string DeptName)
         {
             string tsql = @"select count(0) from JC_DepartMent where DeptID<>@DeptID and DeptName=@DeptName";
             Database db = DatabaseFactory.CreateDatabase();
             DbCommand cmd = db.GetSqlStringCommand(tsql);
             db.AddInParameter(cmd, "@DeptID", DbType.Int32, DeptID);
             db.AddInParameter(cmd, "@DeptName", DbType.String, DeptName);
             int a = Convert.ToInt32(db.ExecuteScalar(cmd));
             if (a == 0) return true;
             return false;
         }
         #endregion
         #region 判断代码是否重复
         public static bool CheckDeptCode(int DeptID, string DeptCode)
         {
             string tsql = @"select count(0) from JC_DepartMent where DeptID<>@DeptID and DeptCode=@DeptCode";
             Database db = DatabaseFactory.CreateDatabase();
             DbCommand cmd = db.GetSqlStringCommand(tsql);
             db.AddInParameter(cmd, "@DeptID", DbType.Int32, DeptID);
             db.AddInParameter(cmd, "@DeptCode", DbType.String, DeptCode);
             int a = Convert.ToInt32(db.ExecuteScalar(cmd));
             if (a == 0) return true;
             return false;
         }
         #endregion

         #region 保存部门负责人
         public static void SaveDeptMaster(int DeptID, List<JC_UserInfo> ListModel)
         {
             string tsql = @"
delete  from JC_DepartUser where DeptID=@DeptID
";
             Database db = DatabaseFactory.CreateDatabase();
             DbCommand cmd = db.GetSqlStringCommand(tsql);
             db.AddInParameter(cmd, "@DeptID", DbType.Int32, DeptID);
             db.ExecuteNonQuery(cmd);
             tsql = @"
insert JC_DepartUser(DeptId,UserId,Duty)values(@DeptId,@UserId,@Duty)
";
             cmd = db.GetSqlStringCommand(tsql);
             foreach (var item in ListModel)
             {
                 
                 
                 cmd.Parameters.Clear();
                 db.AddInParameter(cmd, "@DeptID", DbType.Int32, DeptID);
                 db.AddInParameter(cmd, "@UserId", DbType.Int32, item.UserId);
                 db.AddInParameter(cmd, "@Duty", DbType.String, "M");
                 db.ExecuteNonQuery(cmd);
             }
         }
         #endregion

         #region 保存部门下属员工
         public static void SaveDeptUser(int DeptID, List<BaseModel> ListModel)
         {
             string tsql = @"
select * from JC_DepartUser where DeptID=@DeptID
delete from JC_DepartUser where DeptID=@DeptID
";
             Database db = DatabaseFactory.CreateDatabase();
             DbCommand cmd = db.GetSqlStringCommand(tsql);
             db.AddInParameter(cmd, "@DeptID", DbType.Int32, DeptID);
             DataTable tb = db.ExecuteDataTable(cmd);
             tsql = @"
delete from JC_DepartUser where UserId=@UserId
insert JC_DepartUser(DeptId,UserId,Duty)values(@DeptId,@UserId,@Duty)
";
             cmd = db.GetSqlStringCommand(tsql);
             DataRow[] drow = null;
             foreach (var item in ListModel)
             {
                 string Duty = "P";
                 drow = tb.Select("UserId="+item.ID);
                 if (drow.Length>0)Duty=Convert.ToString(drow[0]["Duty"]);
                 cmd.Parameters.Clear();
                 db.AddInParameter(cmd, "@DeptID", DbType.Int32, DeptID);
                 db.AddInParameter(cmd, "@UserId", DbType.Int32, item.ID);
                 db.AddInParameter(cmd, "@Duty", DbType.String, Duty);
                 db.ExecuteNonQuery(cmd);
             }
         }
         #endregion
         #region 保存部门下属员工
         public static void SaveDeptUserForTM(int DeptID, List<BaseModel> ListModel)
         {
             string tsql = @"
update JC_UserInfo set DeptId=0 where DeptID=@DeptID
";
             Database db = DatabaseFactory.CreateDatabase();
             DbCommand cmd = db.GetSqlStringCommand(tsql);
             db.AddInParameter(cmd, "@DeptID", DbType.Int32, DeptID);
             db.ExecuteNonQuery(cmd);
            
           
             foreach (var item in ListModel)
             {


                  tsql = @"
declare @DeptName nvarchar(100)
select @DeptName=DeptName from JC_DepartMent where DeptId=@DeptId
update JC_UserInfo set DeptId=@DeptID,DeptName=@DeptName where UserId=@UserId";
             
                 cmd.Parameters.Clear();
                 cmd = db.GetSqlStringCommand(tsql);
                 db.AddInParameter(cmd, "@DeptID", DbType.Int32, DeptID);
                 db.AddInParameter(cmd, "@UserId", DbType.Int32, item.ID);
                 db.ExecuteNonQuery(cmd);
             }
         }
         #endregion

         #region 返回部门列表
         public List<JC_DepartMent> GetDepartList(int CompanyId, string DeptType)
         {
             List<JC_DepartMent> list = null;
             string tsql = "select * from JC_DepartMent where CompanyId=@CompanyId and DeptType=@DeptType";
             Database db = DatabaseFactory.CreateDatabase();
             DbCommand cmd = db.GetSqlStringCommand(tsql);
             db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
             db.AddInParameter(cmd, "@DeptType", DbType.String, DeptType);
             DataSet ds = db.ExecuteDataSet(cmd);
             list = ds.Tables[0].ToList<JC_DepartMent>();
             return list;
         }
         #endregion
    }
}