everlight.eap/backend/DllEapDal/MailSettingsDal.cs

using Cksoft.Data;
using Cksoft.Unity;
using DllEapCommon.Extensions;
using DllEapEntity;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DllEapDal
{
    public class MailSettingsDal
    {
        private IDatabase CurrDb;

        public MailSettingsDal(IDatabase db)
        {
            CurrDb = db;
        }

        #region MailSettings
        /// <summary>
        /// 获取列表
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="filter"></param>
        /// <param name="sort"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public IEnumerable<MailSettings> GetMailSettingsList(int start, int length, string filter, string sort, string order, ref string errorinfo)
        {
            var list = CurrDb.FindListForCondition<MailSettings>($" {filter} order by {sort} {order} limit {start - 1},{length}",
                ref errorinfo);
            return list;
        }

        /// <summary>
        /// 获取单个实体
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public MailSettings GetMailSettings(int id)
        {
            string errorinfo = string.Empty;
            var model = CurrDb.FindEntityFor<MailSettings>(id);
            return model;
        }

        public MailSettings GetMailSettingsByCode(string code)
        {
            var model = CurrDb.FindList<MailSettings>($"select * from MailSettings where FCode='{code}'").FirstOrDefault();
            return model;
        }

        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public int UpdateMailSettings(MailSettings model, string usercode, ref string errorinfo)
        {
            try
            {
                var sql = $"select * from MailSettings where fcode='{model.FCode}' and id<>'{model.ID}'";
                var temp = CurrDb.FindList<MailSettings>(sql).FirstOrDefault();
                if (temp != null)
                {
                    errorinfo = "邮件类别代码已存在";
                    return 0;
                }
                return CurrDb.UpdateFor<MailSettings>(model, usercode);
            }
            catch (Exception e)
            {
                errorinfo = e.Message;
                return 0;
            }
        }

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public int InsertMailSettings(MailSettings model, ref string errorinfo)
        {
            try
            {
                var sql = $"select * from mailsettings where fcode='{model.FCode}'";
                var temp = CurrDb.FindList<MailSettings>(sql).FirstOrDefault();
                if (temp != null)
                {
                    errorinfo = "邮件类别代码已存在";
                    return 0;
                }
                return CurrDb.InsertFor(model, model.RecCode);
            }
            catch (Exception e)
            {
                errorinfo = e.Message;
                return 0;
            }

        }

        /// <summary>
        /// 删除邮件配置
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<EapResponse> DeleteMailSetting(int id)
        {
            // 删除邮件配置下的收件人信息
            var sql = "delete from mailreceiver where MailSettingsId=" + id;
            CurrDb.ExecuteBySql(sql);
            // 删除邮件配置
            CurrDb.DeleteFor<MailSettings>(id);
            await Task.CompletedTask;
            return new EapResponse() { Code = 1 };   
        }

        /// <summary>
        /// 获取邮件配置总的记录数
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        public int GetMailSettingsCount(string filter)
        {
            string sql = string.Format($"SELECT COUNT(1) FROM mailsettings a WHERE 1=1 {filter}");
            return Convert.ToInt32(CurrDb.FindList<string>(sql).FirstOrDefault() ?? "0");
        }
        #endregion

        #region MailReceiver
        /// <summary>
        /// 获取列表
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="filter"></param>
        /// <param name="sort"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public IEnumerable<MailReceiver> GetMailReceiverList(int start, int length, string filter, string sort, string order, ref string errorinfo)
        {
            var list = CurrDb.FindListForCondition<MailReceiver>($" {filter} order by {sort} {order} limit {start - 1},{length}",
                ref errorinfo);
            return list;
        }

        /// <summary>
        /// 获取单个实体
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public MailReceiver GetMailReceiver(int id)
        {
            string errorinfo = string.Empty;
            var model = CurrDb.FindEntityFor<MailReceiver>(id);
            return model;
        }

        public IEnumerable<MailReceiver> GetMailReceiverByMailId(int id)
        {
            return CurrDb.FindList<MailReceiver>($"select * from MailReceiver where MailSettingsId={id} and IsValid=1");
        }

        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public int UpdateMailReceiver(MailReceiver model, string usercode, ref string errorinfo)
        {
            var sql = $@"select * from mailreceiver where MailSettingsId={model.MailSettingsId} 
                        and MailAddress='{model.MailAddress}' and IsValid=1 and id<>'{model.ID}'";
            var temp = CurrDb.FindList<MailReceiver>(sql).FirstOrDefault();
            if (temp != null)
            {
                errorinfo = "此邮箱已存在";
                return 0;
            }
            return CurrDb.UpdateFor<MailReceiver>(model, usercode);
        }

        /// <summary>
        /// 新增收件人
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public int InsertMailReceiver(MailReceiver model, ref string errorinfo)
        {
            try
            {
                var sql = $"select * from mailreceiver where MailSettingsId={model.MailSettingsId} and MailAddress='{model.MailAddress}' and IsValid=1";
                var temp = CurrDb.FindList<MailReceiver>(sql).FirstOrDefault();
                if (temp != null)
                {
                    errorinfo = "此邮箱已存在";
                    return 0;
                }
                return CurrDb.InsertFor(model, model.RecCode);
            }
            catch (Exception e)
            {
                errorinfo = e.Message;
                return 0;
            }

        }

        /// <summary>
        /// 删除收件人
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public int DeleteMailReceiver(int id, string usercode, ref string errorinfo)
        {
            if (id <= 0)
            {
                errorinfo = "收件人不存在";
                return 0;
            }
            try
            {
                var entity = CurrDb.FindEntity<MailReceiver>(id);
                if (entity == null)
                {
                    errorinfo = "收件人不存在";
                    return 0;
                }
                entity.IsValid = 0;
                entity.ModCode = usercode;
                //entity.ModTime = DateTime.Now;
                return CurrDb.UpdateFor(entity, usercode);
            }
            catch (Exception e)
            {
                errorinfo = e.Message;
                return 0;
            }
        }

        /// <summary>
        /// 获取收件人总的记录数
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        public int GetMailReceiverCount(string filter)
        {
            string sql = string.Format($"SELECT COUNT(1) FROM mailreceiver a WHERE 1=1 {filter}");
            return Convert.ToInt32(CurrDb.FindList<string>(sql).FirstOrDefault() ?? "0");
        }
        #endregion

    }
}