ZLPMS/PMS.DBService/MessageManage/MessageManageServe.cs

using QWPlatform.IService;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using QWPlatform.SystemLibrary;
using QWPlatform.SystemLibrary.LogManager;
using System.Data;
using PMS.BusinessModels.Problem;
using PMS.BusinessModels.Account;
using PMS.EntityModels.NoticeManager;
using PMS.Plugins.Common;

namespace PMS.DBService.MessageManage
{
    public class MessageManageServe: DataServiceBase
    {
        private static Logger Log = Logger.Instance;
        /// <summary>
        /// 本部添加公告
        /// </summary>
        /// <param name="model"></param>
        /// <param name="channel"></param>
        /// <returns></returns>
        public bool AddMessageByBB(MessageManageModel model,string ID)
        {
            ///获取序列
            var number = this.SqlBuilder.SqlText("select 公告通知_编号.nextval from dual").Select<int>();
            return this.InsertBuilder.Column("ID", ID)
                                     .Column("编号", number)
                                     .Column("标题", model.Title)
                                     .Column("内容", model.Deatil)
                                     .Column("发布时间", this.DataBaseBuilder.GetDateTime)
                                     .Column("添加人", model.PersonName)
                                     .Column("添加人ID", model.PersonId)
                                     .Column("通知对象",model.NotifyTagert)
                                     .Column("通知类型", model.Type)
                                     .Column("优先级", model.Property)
                                     .Column("首页显示", model.IsShow)
                                     .Column("截止日期", model.EndDate)
                                     .Column("附件ID",model.FJID)
                                     .Insert("公告通知")
                                     .Execute()>0;
        }
        /// <summary>
        /// 渠道添加公告
        /// </summary>
        /// <param name="model"></param>
        /// <param name="channel"></param>
        /// <returns></returns>
        public bool AddMessageByQD(MessageManageModel model,UserInfo user,string ID)
        {
            ///获取序列
            var number = this.SqlBuilder.SqlText("select 公告通知_编号.nextval from dual").Select<int>();
            return this.InsertBuilder.Column("ID", ID)
                                     .Column("编号", number)
                                     .Column("标题", model.Title)
                                     .Column("内容", model.Deatil)
                                     .Column("发布时间", this.DataBaseBuilder.GetDateTime)
                                     .Column("添加人", model.PersonName)
                                     .Column("添加人ID", model.PersonId)
                                     .Column("通知对象", model.NotifyTagert)
                                     .Column("通知类型", model.Type)
                                     .Column("优先级", model.Property)
                                     .Column("首页显示", model.IsShow)
                                     .Column("渠道ID", user.CompanyID)
                                     .Column("截止日期",model.EndDate)
                                     .Column("附件ID", model.FJID)
                                     .Insert("公告通知")
                                     .Execute() > 0;
        }
        public bool DeleteMessage(MessageManageModel model)
        {
            return this.UpdateBuilder.Column("是否删除", 1)
                                     .Column("删除时间", this.DataBaseBuilder.GetDateTime)
                                     .Where("ID", model.ID)
                                     .Update("公告通知")
                                     .Execute() > 0;
        }

        /// <summary>
        /// 通知类型
        /// </summary>
        /// <returns></returns>
        public DataTable NoticeType()
        {
            return SelectBuilder.From("基础编码").Where("分类ID", "0A469BFA-7BCC-4F87-9BBF-56E2FDB762F4").Columns("显示名").Columns("ID").Columns("代码").Where("启用", 1).Select();
        }
        /// <summary>
        /// 根据公告ID获取通知对象
        /// </summary>
        /// <param name="v"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataTable GetNatureByID(string id)
        {
            return SelectBuilder.From("基础编码").Where("分类ID", "D3EDEA7A-7F07-4DD3-9A6B-34DB16A6E982").Where("启用", 1).Where("代码", id).Columns("显示名").Select();
        }
        /// <summary>
        /// 修改操作
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Edit_Notice(MessageManageModel model)
        {
            try
            {
                return UpdateBuilder.Update("公告通知").Column("标题", model.Title).Column("内容", model.Deatil).Column("通知类型", model.Type).Column("通知对象", model.NotifyTagert).Column("附件ID",model.FJID).Column("优先级", model.Property).Column("首页显示", model.IsShow).Column("截止日期", model.EndDate).Where("ID",model.ID).Execute();
            }
            catch (Exception ex)
            {
                Logger.Instance.Error("调用Edit_Notice时发生异常错误", ex);
                return 0;
            }
        }
        /// <summary>
        /// 判断是否显示
        /// </summary>
        /// <param name="userInfo"></param>
        /// <returns></returns>
        public Mssage_config PanDuanIsShow(UserInfo userInfo)
        {
            var bytes = SelectBuilder.From("参数配置").Where("所属账户", userInfo.ID).Where("参数名", PMS.BusinessModels.PMSCONSTS.MESAAGECONFIG).Columns("参数内容").Select<byte[]>();
            if (bytes != null)
            {
                return QWPlatform.SystemLibrary.Serialize.SerializeHelper.DeserializeBinary(bytes) as Mssage_config;
            }
            return null;
        }
        /// <summary>
        /// 是否启用
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int NoticeStatus(string id)
        {
            var status = SelectBuilder.From("公告通知").Columns("是否启用").Where("ID", id).Select<int>();
            status=status == 1 ?0 : 1;
            UpdateBuilder.Update("公告通知").Where("ID", id).Column("是否启用", status).Execute();
            return status == 1 ? 1 : 0;
        }

        /// <summary>
        /// 添加手机公告
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public string Add_AppPush(AppPush_Model model)
        {
            string id = Guid.NewGuid().ToString();
            var r= InsertBuilder.Insert("APP图片公告")
                 .Column("ID", id)
                 .Column("标题", model.BT)
                 .Column("描述", model.MS)
                 .Column("添加人ID", model.TJRID)
                 .Column("添加时间",this.DataBaseBuilder.GetDateTime)
                 .Execute();
            if (r == 1)
            {
                return id;
            }
            return "";
        }

        /// <summary>
        /// 删除app公告
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int Del_AppNotice(string id)
        {
            return DeleteBulider.Delete("APP图片公告").Where("ID", id).Execute();
        }
        /// <summary>
        /// 获取启用的app公告
        /// </summary>
        /// <returns></returns>
        public DataTable  GetWorkNotice()
        {
            return SelectBuilder.From("APP图片公告").Columns("ID,标题,描述,图片ID").Where("是否启用", 1).OrderBy("添加时间 desc").Select();
        }
        public DataTable GetWorkNotiveById(string id)
        {
            return SelectBuilder.From("APP图片公告").Columns("ID,标题,描述,图片ID").Where("ID", id).Select();
        }

        public int Edit_NoticeFJ(string FJID,string ID)
        {
            return UpdateBuilder.Update("公告通知").Column("附件ID",FJID ).Where("ID", ID).Execute();
        }

        /// <summary>
        /// 删除附件
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DeleteFJ(string id)
        {
            return UpdateBuilder.Update("公告通知").Column("附件ID", DBNull.Value).Where("ID", id).Execute();
        }

        /// <summary>
        /// 添加图片ID
        /// </summary>
        /// <param name="tPID"></param>
        /// <returns></returns>
        public int Edit_AppImg(AppPush_Model model)
        {
            return UpdateBuilder.Update("APP图片公告").Column("图片ID", model.TPID).Where("ID", model.ID).Execute();
        }
        /// <summary>
        /// App公告是否启用
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int AppNoticeStatus(string id)
        {
            var status = SelectBuilder.From("APP图片公告").Columns("是否启用").Where("ID", id).Select<int>();
            status = status == 1 ? 0 : 1;
            
             DataTable dt = SelectBuilder.From("APP图片公告").Columns("*").Where("是否启用", 1).OrderBy("添加时间 desc").Select();
                        if (dt.Rows.Count <6)
                        {
                            UpdateBuilder.Update("APP图片公告").Where("ID", id).Column("是否启用", status).Execute();
                            return status == 1 ? 1 : 0;
                        }
                        else
            {
                if (status == 0)
                {
                    UpdateBuilder.Update("APP图片公告").Where("ID", id).Column("是否启用", status).Execute();
                    return 0;
                }
            }
                        return -1;
        }
        /// <summary>
        /// APP首页推送
        /// </summary>
        /// <param name="startnumber"></param>
        /// <param name="endnumber"></param>
        /// <param name="query"></param>
        /// <param name="user"></param>
        /// <returns></returns>
        public DataTable APPDataBind(int startnumber, int endnumber,out int total)
        {
            string sql = @"select ID,标题,描述,图片ID,添加时间,decode(是否启用,'1','启用','停用') as 状态,
                            (select 姓名 from 人员信息 where id=r.添加人ID)as 添加人 from APP图片公告 r order by 添加时间 desc";
            return SqlBuilder.SqlText(sql).Paging(startnumber, endnumber).Select(out  total);
        }

        /// <summary>
        /// 显示最新公告通知
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public DataTable GetMessageOnIndexChannel(UserInfo user)
        {
            string sql = @"select * from (select * from 公告通知 
                                                    where (渠道ID is null or 渠道ID=:渠道) 
                                                    and 首页显示 =1 and instr(通知对象,:NotifyTagert)>0 
                                                    and ID not in(select 公告ID from 用户公告标记 where  标记人ID=:PersonId and 是否显示=0)
                                                    and sysdate<截止日期
                                                    and 是否删除=0 and 是否启用=1 order by 发布时间 desc) 
                                                    where rownum<4";
            return SqlBuilder.SqlText(sql).Parameters("渠道", user.CompanyID).Parameters("NotifyTagert",user.PersonProperty).Parameters("PersonId",user.PersonID).Select();
        }
        /// <summary>
        /// 通知数目
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public int GetCount(UserInfo user)
        {
            var sql = @"select Count(1) from 公告通知 
                        where （渠道ID is null or 渠道ID=:渠道） 
                        and instr(通知对象,:NotifyTagert)>0  
                        and 是否启用=1
                        and 首页显示 =1 
                        and 是否删除=0
                        and sysdate<截止日期
                        and ID not in(select 公告ID from 用户公告标记 where  标记人ID=:PersonId and 是否显示=0)";
            return this.SqlBuilder.SqlText(sql)
                                  .Parameters("渠道", user.CompanyID)
                                  .Parameters("NotifyTagert", user.PersonProperty)
                                  .Parameters("PersonId",user.PersonID)
                                  .Select<int>();
        }

        /// <summary>
        /// 根据ID获取通知信息
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public DataTable ShowNotice(UserInfo user)
        {
            return SelectBuilder.From("公告通知").Where("首页显示", 1).Select();
        }

        /// <summary>
        /// 获取渠道
        /// </summary>
        /// <param name="personProperty"></param>
        /// <returns></returns>
        public DataTable GetChannelByNature(UserInfo user)
        {
            string sql = "select ID,名称 from 渠道信息";
            if (user.PersonProperty !=1)
            {
                sql += " where  ID=:渠道ID";
                return SqlBuilder.SqlText(sql).Parameters("渠道ID", user.CompanyID).Select();
            }
            return SqlBuilder.SqlText(sql).Select();
        }

        /// <summary>
        /// 根据ID获取通知信息
        /// </summary>
        /// <param name="v"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataTable GetNoticeByID(string id)
        {
            return SelectBuilder.From("公告通知").Where("ID", id).Columns("*").Select();
        }
        /// <summary>
        /// 通知对象
        /// </summary>
        /// <returns></returns>
        public DataTable NoticePerson(int PersonProperty)
        {
            string sql = "select ID,显示名,代码 from 基础编码 where 分类ID='D3EDEA7A-7F07-4DD3-9A6B-34DB16A6E982' and 启用=1";
            if(PersonProperty!=1)
            {
                sql += " and 代码!=1";
            }
            return SqlBuilder.SqlText(sql).Select();
        }
        /// <summary>
        /// 通知优先级
        /// </summary>
        /// <returns></returns>
        public DataTable NoticeOrder()
        {
            return SelectBuilder.From("基础编码").Where("分类ID", "A10D6027-DEDD-4B6D-9549-33830DE52AEA").Columns("显示名").Columns("ID").Columns("代码").Where("启用", 1).Select();
        }
        public DataTable SelecteMessageById(string id)
        {
            return this.SelectBuilder.From("公告通知").Where("ID", id).Columns("编号,标题,内容,发布时间,添加人,附件ID").Select();
        }
        /// <summary>
        /// 公告信息绑定
        /// </summary>
        /// <param name="page"></param>
        /// <param name="rows"></param>
        /// <param name="query"></param>
        /// <returns></returns>
        public string DataBind(int page, int rows, string query,UserInfo user)
        {
            var exe = this.ProcedureBuilder
                  .Procedure("B_公告通知管理.p_公告信息_Select")
                  .Paramter("查询条件_In", query)
                  .Paramter("人员性质_In", user.PersonProperty)
                   .Paramter("渠道ID_In", user.CompanyID)
                  .Paramter("开始行_In", page)
                  .Paramter("结束行_In", rows)
                  .ParamterOut("total", DbType.Double, 0)
                  .ParamterOut("Resultlist", true);
            exe.Execute();
            var dt = exe.ParameterValue<DataTable>("Resultlist");
            var total = exe.ParameterValue<decimal>("total");
            return dt.ToEasyUIGridJson(int.Parse(total.ToString()));
        }
        /// <summary>
        /// 首页通知公告信息
        /// </summary>
        /// <param name="PersonProperty"></param>
        /// <returns></returns>
        public DataTable GetHome(UserInfo user)
        {
            var SqlBuilder = this.SqlBuilder;
            var sql = @"select * from 
                        (select 标题,ID,ROUND(sysdate - 发布时间,2)*24 as 发布时间,通知类型 from 公告通知 where  是否删除=0 and 是否启用=1 and instr(通知对象,:PersonProperty)>0 ";
            SqlBuilder.Parameters("PersonProperty", user.PersonProperty);
            //如果不为总部人员显示配置信息
            if (user.PersonProperty != 1)
            {
                sql += " and (渠道ID=:渠道ID or 渠道ID is null)";
                SqlBuilder.Parameters("渠道ID", user.CompanyID);
            }
            else
            {
                sql += " and  (渠道ID is null ) ";
            }
            sql += " order by 发布时间 asc) where rownum<=5";
           return SqlBuilder.SqlText(sql).Select();

        }
        /// <summary>
        /// 首页获取公告列表
        /// </summary>
        /// <param name="page"></param>
        /// <param name="rows"></param>
        /// <param name="PersonProperty"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public DataTable HomeGetList(int page, int rows, UserInfo user, out int total)
        {
             total = 0;
            var SqlBuilder = this.SqlBuilder;
            var sql = @"select 编号,标题,内容,ID,发布时间,ROUND(sysdate - 发布时间,2)*24 as 发布时间小时,decode(通知类型,1,'公告','通知') as 类型,添加人 from 公告通知 where 通知类型=1 and 是否删除=0 and 是否启用=1 and instr(通知对象,:PersonProperty)>0  ";
            SqlBuilder.Parameters("PersonProperty", user.PersonProperty);
            //如果不为总部人员显示配置信息
            if (user.PersonProperty != 1)
            {
                sql += " and (渠道ID=:渠道ID_In or 渠道ID is null)  order by 发布时间 desc";
                SqlBuilder.Parameters("渠道ID_In",user.CompanyID);
            }
            else
            {
                sql += " and ( 渠道ID is null) order by 发布时间 desc";
            }
            return SqlBuilder.SqlText(sql).Paging(page,rows).Select(out total);
        }
        /// <summary>
        /// 首页获通知列表
        /// </summary>
        /// <param name="page"></param>
        /// <param name="rows"></param>
        /// <param name="PersonProperty"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public DataTable HomeGetNotice(int page, int rows, UserInfo user, out int total)
        {
            total = 0;
            var SqlBuilder = this.SqlBuilder;
            var sql = @"select 编号,标题,ID,发布时间,decode(通知类型,1,'公告','通知') as 类型,添加人 from 公告通知 where 通知类型=2 and 是否删除=0 and 是否启用=1 and instr(通知对象,:PersonProperty)>0 ";
            SqlBuilder.Parameters("PersonProperty", user.PersonProperty);
            //如果不为总部人员显示配置信息
            if (user.PersonProperty != 1)
            {
                sql += " and (渠道ID=:渠道ID_In or 渠道ID is null) order by 发布时间 desc";
                SqlBuilder.Parameters("渠道ID_In", user.CompanyID);
            }
            else
            {
                sql += " and ( 渠道ID is null) order by 发布时间 desc";
            }
            return SqlBuilder.SqlText(sql).Paging(page, rows).Select(out total);
        }
        public bool SaveNoticeConfig(string PersonId, string NoticeId,bool IsOpen)
        {
            var r = this.SqlBuilder.SqlText("select ID from 用户公告标记 where 公告ID=:NoticeId and 标记人ID=:PersonId")
                                       .Parameters("NoticeId", NoticeId)
                                       .Parameters("PersonId", PersonId)
                                       .Exists();
            if (r)
            {
                return this.UpdateBuilder.Column("是否显示", IsOpen?0:1)
                                         .Column("标记时间", this.DataBaseBuilder.GetDateTime)
                                         .Where("公告ID", NoticeId)
                                         .Where("标记人ID", PersonId)
                                         .Update("用户公告标记")
                                         .Execute() > 0;
            }
            else
            {
                return this.InsertBuilder.Column("是否显示", IsOpen?0:1)
                                         .Column("标记时间", this.DataBaseBuilder.GetDateTime)
                                         .Insert("用户公告标记")
                                         .Column("公告ID", NoticeId)
                                         .Column("标记人ID", PersonId)
                                         .Column("ID",Guid.NewGuid().ToString())
                                         .Execute() > 0;
            }
        }
        /// <summary>
        /// 模板转换
        /// </summary>
        /// <param name="type">1阿里短信模板,2微信消息模板</param>
        /// <param name="TemplateID">模板id</param>
        /// <param name="TempParame">模板内容</param>
        /// <param name="sign_name">短信签名</param>
        /// <returns></returns>
        public string ConvertWeChatTemp(int type, string TemplateID, Dictionary<string, string> TempParame, string sign_name)
        {
            var TemplateContent = "";
            //从数据库查询数据
            var _messageTemplate = new List<MessageTemplate>();
            try
            {
                var dataTable = this.SqlBuilder.SqlText("select * from 消息模板 t where t.模版CODE=:TemplateID and t.数据表示=1")
                           .Parameters("TemplateID", TemplateID)
                           .Select();
                if (dataTable.Rows.Count > 0)
                {
                   _messageTemplate = Tools.ConvertToList<MessageTemplate>(dataTable);
                }

                TemplateContent = _messageTemplate.FirstOrDefault().内容;//替换后的模板内容
                foreach (var item in TempParame)
                {
                    TemplateContent = TemplateContent.Replace("${" + item.Key + "}", item.Value);  // 替换.Replace("被替换字符", "替换的字符")
                }
                if (type == 1)
                {
                    var ParameterContents = "【" + sign_name + "】" + TemplateContent;
                    var lenths = ParameterContents.Length;
                }
                Log.Info("组装后的消息模板：" + TemplateContent);

            }
            catch (Exception ex)
            {
            }
            return TemplateContent;

        }
    }
}