ZLPMS/PMS.DBService/Channel/ChannelService.cs

using System;
using System.Data;
using QWPlatform.IService;
using PMS.EntityModels.Channel;
using System.Data.SqlClient;
using PMS.BusinessModels.ProManager;
using QWPlatform.SystemLibrary;

namespace PMS.DBService.Channel
{
    /// <summary>
    /// 创建者：冉利
    /// 创建日期：2018/12/10
    /// 功能描述：渠道信息的新增
    /// </summary>
    public class ChannelService : DataServiceBase
    {
        //实例化数据库
        public DataServiceBase dataservice = new DataServiceBase();

        #region 获取和查询渠道信息
        /// <summary>
        /// 获取渠道信息
        /// </summary>
        /// <param name="search"></param>
        /// <param name="currrentID"></param>
        /// <param name="startnumber"></param>
        /// <param name="endNumber"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public string SelectChannel(string search, string currrentID,int startnumber, int endNumber)
        {
            var exeResult = this.ProcedureBuilder.Procedure("b_基础管理_渠道项目.p_渠道信息_Selectsz")
                .Paramter("查询条件_In", search)
                .Paramter("登录人ID_In", currrentID)
                .Paramter("开始行_In", startnumber)
                .Paramter("结束行_In", endNumber)
                .ParamterOut("总行数_Out", DbType.Double, 5)
                .ParamterOut("Resultlist", true);
                exeResult.Execute();
            //var dt = exeResult.ParameterValue<DataTable>("Resultlist");
            //return dt;
            //exeResult.Execute();
            var dt = exeResult.ParameterValue<DataTable>("Resultlist");
            var total = exeResult.ParameterValue<decimal>("总行数_Out");
            return dt.ToEasyUIGridJson(Convert.ToInt32(total));
        }

        #endregion

        #region 新增修改渠道信息 
        /// <summary>
        /// 新增渠道信息
        /// </summary>
        /// <param name="jsonstring"></param>
        /// <param name="EditType">新增渠道</param>
        //p_渠道信息_Edit
        public string EditChannel(ChannelModel model, int editType)
        {
            var exeResult = this.ProcedureBuilder.Procedure("b_基础管理_渠道项目.p_渠道信息_Edit")
                   .Paramter("Id_In", model.ID)
                   .Paramter("编码_In", model.code)
                   .Paramter("名称_In", model.name)
                   .Paramter("简码_In", model.brevitycode)
                   .Paramter("性质_In", model.nature)
                   .Paramter("开始日期_In",model.startdate)
                   .Paramter("商务联系人_In",model.business)
                   .Paramter("技术联系人_In",model.technology)
                   .Paramter("电话_In",model.phonenumber)
                   .Paramter("地址_In",model.address)
                   .Paramter("备注_In", model.note)
                   .Paramter("商务联系人邮箱_In", model.businessMail)
                   .Paramter("商务联系人微信_In", model.businessWxin)
                   .Paramter("技术联系人邮箱_In", model.technologyMail)
                   .Paramter("技术联系人微信_In", model.technologyWxin)
                   .Paramter("编辑类型_In",editType)
                   .ParamterOut("Resultstr_Out", DbType.String, 400);
            exeResult.Execute();
            var dt = exeResult.ParameterValue<String>("Resultstr_Out");
            return dt;
        }

        #endregion

        #region 查询渠道信息
        public DataTable GetChannelInfo(string id)
        {
            var dt = this.SelectBuilder.Columns("*")
                 .From("渠道信息")
                 .Where("ID", id)
                 .Select();
            return dt;
        }
        #endregion

        #region 查询所有渠道信息得id 名称（名称+简码）

        public DataTable GetChannelIdName()
        {
            string sql= "select '' AS ID,'全部' AS 名称 From dual Union ALL select ID, concat(名称,'(' || 简码 || ')')  AS 名称 From 渠道信息";
            var dt = this.SqlBuilder.SqlText(sql).Select();
            return dt;
        }
        #endregion

        #region 所有渠道性质
        public DataTable GetChannelnature() {

            string sql = "select 代码 AS ID,显示名 AS 名称 from 基础编码 where 分类ID='7AC2C85B-04C5-49FB-95B4-9EE3F0552B4A'";
             var dt = this.SqlBuilder.SqlText(sql).Select();
            return dt;
        }
        #endregion
      
        #region 删除渠道信息
        public string DelChannelInfo(string id,int type)
        {
            var exeResult = this.ProcedureBuilder.Procedure("b_基础管理_渠道项目.p_渠道信息_Sign")
                  .Paramter("Id_In", id)
                  .Paramter("标记类型_In", type)
                  .ParamterOut("Resultstr_Out", DbType.String, 400);
            exeResult.Execute();
            string ExeResults = exeResult.ParameterValue<String>("Resultstr_Out");
            return ExeResults;
        }
        #endregion

        #region 根据条件查询渠道信息
        public string SearchModuleInfos(string search, string currrentID, int page, int rows)
        {
            //          Procedure p_渠道信息_Select

            //  (
            //    查询条件_In In Varchar2,
            //    登录人ID_In In Varchar2,
            //    Resultlist Out Sys_Refcursor
            //) 
            if(search == "null" )
            {
                search = "";
            }
            var exeResult = this.ProcedureBuilder.Procedure("b_基础管理_渠道项目.p_渠道信息_Selectsz")
                           .Paramter("查询条件_In", search)
                           .Paramter("登录人ID_In", currrentID)
                           .Paramter("开始行_In", page)
                           .Paramter("结束行_In", rows)
                           .ParamterOut("总行数_Out", DbType.Double, 0)
                           .ParamterOut("Resultlist", true);
            exeResult.Execute();
            var dt = exeResult.ParameterValue<DataTable>("Resultlist");
            var total = exeResult.ParameterValue<decimal>("总行数_Out");
            return dt.ToEasyUIGridJson(Convert.ToInt32(total));
        }

        public DataTable GetChannelProjectTree()
        {
            var exeResult = this.ProcedureBuilder.Procedure("b_平台管理_权限管理.p_获取渠道项目树形菜单")
                  .Paramter("In_空id", DBNull.Value)
                  .Paramter("In_空值", DBNull.Value)
                  .ParamterOut("Resultlist", true);
            exeResult.Execute();
            var ExeResults = exeResult.ParameterValue<DataTable>("Resultlist");
            return ExeResults;
        }
        public DataTable GetChannelCombo()
        {
            return this.SelectBuilder.Columns("ID,名称").From("渠道信息").Where("状态", 1).OrderBy("编码 asc").Select();
        }
        public DataTable GetProjectCombobox(string ChannelId)
        {
            return this.SelectBuilder.Columns("ID,名称").From("项目信息").Where("渠道ID", ChannelId).Where("启用",1).Select();
        }
        public DataTable GetInstitution(string id)
        {
            return this.SelectBuilder.Columns("ID,名称").From("站点信息").Where("项目ID", id).Select();
        }
        #endregion

        /// <summary>
        /// 查询渠道编码
        /// </summary>
        /// <returns></returns>
        public string ChannelCode()
        {
            string sql = "select * from(select 编码 from 渠道信息 order by 编码 desc)where rownum = 1 ";
            var result = this.SqlBuilder.SqlText(sql).Select<string>();
            return result;
        }
        /// <summary>
        /// 查询渠道名称
        /// </summary>
        /// <param name="Name"></param>
        /// <returns></returns>
        public string ChannelName(string Name)
        {
            //select 名称 from 渠道信息 where 名称 = '中联西安公司'id=:iD
            //select 名称 from 渠道信息 where 名称 = '中联总部'
            //string sql = "select count(*) from 渠道信息 where 名称=:Name";
            //var result = this.SqlBuilder.SqlText(sql).Parameters("名称",Name).Select<string>();
            //return result;
            var dt = this.SelectBuilder.Columns("名称")
               .From("渠道信息")
               .Where("名称", Name)
               .Select<string>();
            if (dt==null)
            {
                return "";
            }
            return dt;
        }

        /// <summary>
        /// 根据渠道id查询人员信息
        /// </summary>
        /// <param name="channelid"></param>
        /// <returns></returns>
        public DataTable Channelbusiness(string channelid)
        {
            //select 姓名 from 人员信息 where 渠道ID = '937C9A4F-99EF-4D0D-B6CA-FC12CA06E05C'
            var result = this.SelectBuilder.Columns("姓名")
                .From("人员信息")
                .Where("渠道ID", channelid)
                .Select();
            return result;
        }
        /// <summary>
        /// 通过渠道信息id值得到渠道信息并返回分页结果
        /// </summary>
        /// <param name="data"></param>
        /// <param name="startnumber"></param>
        /// <param name="endNumber"></param>
        /// <returns></returns>
        public string ChannelDataGridsM(string data, int startnumber, int endNumber)
        {
            var dt = this.ProcedureBuilder.Procedure("b_基础管理_渠道项目.p_当前ID查询渠道信息_Selectsz")
            .Paramter("StringId_In", data)
            .Paramter("开始行_In", startnumber)
            .Paramter("结束行_In", endNumber)
            .ParamterOut("总行数_Out",DbType.Double,5)
            .ParamterOut("Resultlist", true);
                dt.Execute();
            var result = dt.ParameterValue<DataTable>("Resultlist");
            var total = dt.ParameterValue<decimal>("总行数_Out");
            return result.ToEasyUIGridJson(Convert.ToInt32(total));
        }

        /// <summary>
        /// 获取项目渠道树（含权限）
        /// </summary>
        /// <param name="Channel"></param>
        /// <param name="Item"></param>
        /// <returns></returns>
        public DataTable GetChannelComboTree(string  Channel,string Item)
        {
            var exeResult = this.ProcedureBuilder.Procedure("b_平台管理_权限管理.p_获取渠道项目Combotree")
                  .Paramter("渠道ID_In", Channel)
                  .Paramter("项目ID_In", Item)
                  .ParamterOut("Resultlist", true);
            exeResult.Execute();
            var ExeResults = exeResult.ParameterValue<DataTable>("Resultlist");
            return ExeResults;
        }
    }
   
    }