ZLPMS/PMS.DBService/SysManager/XTZHDBService.cs

using QWPlatform.IService;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using PMS.EntityModels.SysManager;
using System.Data;
using QWPlatform.SystemLibrary;
using QWPlatform.SystemLibrary.LogManager;

namespace PMS.DBService.SysManager
{
    public class XTZHDBService : DataServiceBase
    {
        //实例化数据库
        public DataServiceBase dataservice = new DataServiceBase();

        public string XtZHEdit(XTZHModel xtzhModel, int editType)
        {
            var dt = this.ProcedureBuilder.Procedure("B_基础管理_系统账户.P_系统账户_Edit")
                   .Paramter("id_in", xtzhModel.ID)
                   .Paramter("编辑类型_in", editType)
                   .Paramter("账户_in", xtzhModel.ZH)
                  .Paramter("密码_in", QWPlatform.SystemLibrary.Utils.Strings.MD5(xtzhModel.MM))
                   .Paramter("姓名_in", xtzhModel.XM)
                   .Paramter("邮箱_in", xtzhModel.YX)
                   .Paramter("状态_in", xtzhModel.QY)
                   .Paramter("类型_in", xtzhModel.LX)
                   .Paramter("公司_in", xtzhModel.GSID)
                   .Paramter("人员ID_in", xtzhModel.RYID)
                   .ParamterOut("Result_out", DbType.String, 400);
            dt.Execute();
            return dt.ParameterValue<string>("Result_out");
        }

        /// <summary>
        /// 人员信息的查询
        /// </summary>
        /// <param name="zh"></param>
        /// <returns></returns>
        public DataTable SelectPersonInfo(string zh)
        {

            return this.SelectBuilder.From("人员信息")
                .Columns("*")
                .Where("联系电话", zh)
                .Select();
        }

        /// <summary>
        /// 获取公司信息
        /// </summary>
        /// <returns></returns>
        public DataTable XtZHGs()
        {
            string sql = "select 'ID' as ID,'全部' as 公司ID from dual union select ID,名称 from 渠道信息";
            return this.SqlBuilder.SqlText(sql)
                .Select();
        }

        /// <summary>
        /// 根据id查找账户信息
        /// </summary>
        /// <param name="iD"></param>
        /// <returns></returns>
        public DataTable GetSystemUserInfo(int ID)
        {
            return this.SelectBuilder.From("系统账户")
                .Columns("*")
                .Where("ID", ID).Select();
        }

        /// <summary>
        /// 获取系统账户类型
        /// </summary>
        /// <returns></returns>
        public DataTable XtZHLx()
        {
            string sql = "select '' AS ID,'全部' AS 类型 From dual Union select  '|' || 类型, replace(replace(类型, 1, '超级管理员'), 0, '普通用户') from 系统账户";
            var dt = this.SqlBuilder.SqlText(sql).Select();
            return dt;
        }
        /// <summary>
        /// 获取公司下的人员
        /// </summary>
        /// <param name="gSID"></param>
        /// <returns></returns>
        public DataTable XtZHRY(string gSID)
        {
            string sql = "select ID, 姓名 人员名称 from 人员信息 where 渠道ID =:渠道ID";
            var dt = this.SqlBuilder.SqlText(sql).Parameters("渠道ID", gSID).Select();
            return dt;
        }

        /// <summary>
        /// 系统账户重置密码
        /// </summary>
        /// <param name="id"></param>
        /// <param name="pwd"></param>
        public void XGMm(int id, string pwd)
        {
            this.UpdateBuilder.Update("系统账户")
                 .Column("密码", QWPlatform.SystemLibrary.Utils.Strings.MD5(pwd))
                 .Where("id", id)
                 .Execute();
        }

        /// <summary>
        /// 删除系统账户
        /// </summary>
        /// <param name="id">id</param>
        public void XtZHDel(int id)
        {
            using (var tran = this.DBTransaction.BeginTrans())
            {
                try
                {

                    this.DeleteBulider.Delete("系统账户权限").Where("账户ID", id).Execute(tran);
                    this.DeleteBulider.Delete("系统角色关系").Where("账户ID", id).Execute(tran);
                    this.DeleteBulider.Delete("系统账户").Where("ID", id).Execute(tran);
                    tran.CommitTrans();

                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    Logger.Instance.Error("删除账户失败，原因:" + ex);
                }
            }
        }
        /// <summary>
        /// 更新用户访问渠道项目权限
        /// </summary>
        /// <param name="id"></param>
        /// <param name="authorize">1是，0否</param>
        public bool UpdataAuthorize(int id,int authorize)
        {
            var state = false;
            try
            {
                state =this.UpdateBuilder.Update("系统账户").Where("id", id).Column("渠道所有项目", authorize).Execute() > 0;
            }
            catch (Exception ex)
            {
             
                Logger.Instance.Error("更新账户访问渠道下所有项目失败，原因:" + ex);
            }
            return state;
        }
        

        public string XtZHSelect(string search, int start_number, int end_number)
        {
            var dt = this.ProcedureBuilder
                .Procedure("B_基础管理_系统账户.p_系统账户_Select")
                .Paramter("查询条件_In", search)
                .Paramter("开始行_In", start_number)
                .Paramter("结束行_In", end_number)
                .ParamterOut("总行数_Out", DbType.Double, 5)
                .ParamterOut("Resultlist", true);
            dt.Execute();
            var dts = dt.ParameterValue<DataTable>("Resultlist");
            var total = dt.ParameterValue<decimal>("总行数_Out");
            return dts.ToEasyUIGridJson(Convert.ToInt32(total));
        }
        /// <summary>
        /// 查询系统账户得名称
        /// </summary>
        /// <param name="XM"></param>
        /// <returns></returns>
        public string UserNameCheck(string XM)
        {
            //            Procedure p_系统账户姓名_Select

            //(
            //姓名_In In 系统账户.姓名 % type,
            //Result_Out      Out  varchar2
            //  ) 
            var dt = this.ProcedureBuilder
                .Procedure("B_基础管理_系统账户.p_系统账户姓名_Select")
                .Paramter("姓名_In", XM)
                .ParamterOut("Result_Out", DbType.String, 400);
            dt.Execute();
            return dt.ParameterValue<string>("Result_Out");
        }
    }
}