Winsoft.GOV/Winsoft.GOV.Framework/Provider/NotificationOfActionProvider.cs

using Dapper;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Winsoft.GOV.Framework.Model;

namespace Winsoft.GOV.Framework.Provider
{
    public class NotificationOfActionProvider : BaseProvider<NotificationOfAction>
    {
        private static string cols = "QL_NAME, QL_INNER_CODE, OUGUID, QL_DEP, AType, QL_KIND, UPDATE_DATE, IsRead";
        private static string sets = "QL_NAME=@QL_NAME, QL_DEP=@QL_DEP, OUGUID=@OUGUID, AType=@AType, QL_KIND=@QL_KIND, UPDATE_DATE=@UPDATE_DATE, IsRead=@IsRead";
        private static string vals = "@QL_NAME, @QL_INNER_CODE, @OUGUID, @QL_DEP, @AType, @QL_KIND, @UPDATE_DATE, @IsRead";
      
        public void CreateTable()
        {
            string sql = "create table if not exists notificationofaction("
                           + "QL_NAME varchar(200),"
                           + "QL_INNER_CODE varchar(50),"
                           + "QL_DEP varchar(50),"
                           + "OUGUID varchar(50),"
                           + "QL_KIND varchar(2),"
                           + "AType int,"
                           + "UPDATE_DATE datetime,"
                           + "IsRead bool,"
                           + "PRIMARY KEY (QL_INNER_CODE)"
                           + "); "
                           + "call add_index('notificationofaction', 'index1', 'index1(IsRead,UPDATE_DATE)'); "
                           + "call add_index('notificationofaction', 'index2', 'index2(QL_INNER_CODE)'); "
                           + "call add_index('notificationofaction', 'index3', 'index3(IsRead)'); "
                           + "call add_index('notificationofaction', 'index4', 'index4(UPDATE_DATE)'); "
                           + "call add_index('notificationofaction', 'index5', 'index5(QL_KIND)'); ";
            //+ "call add_index('PowerMattersBase', 'index4', 'index4(QL_NAME)'); ";
            using (var con = GetDbConnection())
            {
                con.Execute(sql);
            }
        }

        public NotificationOfActionProvider(County county) : base(county)
        {

        }

        public void InserOrUpdate(NotificationOfAction n)
        {
            using (var con = GetDbConnection())
            {
                string sql = "insert into notificationofaction(" + cols + ") values("+ vals + ") on duplicate key update " + sets;
                con.Execute(sql, n);
            }
        }



        public void Read(string QL_INNER_CODE)
        {
            using (var con = GetDbConnection())
            {
                string sql = "update notificationofaction set IsRead = 1 where QL_INNER_CODE='" + QL_INNER_CODE + "'";
                con.Execute(sql);
            }
        }

        public int UnreadCount()
        {
            using (var con = GetDbConnection())
            {
                string sql = "select count(*) notificationofaction where IsRead = 0";
                return con.QueryFirstOrDefault<int>(sql);
            }
        }

        public PagedResult<NotificationOfAction> FindByPage(int itemIndex, int size)
        {
            string sql = "select " + cols + " from notificationofaction order by UPDATE_DATE desc";
            return GetPagedList(sql, itemIndex, size);
        }

        public PagedResult<NotificationOfAction> FindByPage(int itemIndex, int size, string[] ql_kinds)
        {
            string sql = "select  " + cols + " from notificationofaction ";

            if (ql_kinds.Length > 0)
            {
                sql = sql + " where ql_kind in ('" + string.Join("','", ql_kinds) + "')";
            }
            sql = sql + " order by UPDATE_DATE desc";
            return GetPagedList(sql, itemIndex, size);
        }

        public PagedResult<NotificationOfAction> FindByPage(string key, int itemIndex, int size, string[] ql_kinds, bool isHidRead)
        {
            string sql = "select  " + cols + " from notificationofaction where 1=1";

            if (ql_kinds.Length > 0)
            {
                sql = sql + " and ql_kind in ('" + string.Join("','", ql_kinds) + "')";
            }
            if (!String.IsNullOrWhiteSpace(key))
                sql = sql + " and (ql_name like '%" + key + "%' or ql_dep like '%" + key + "%')";
            if (isHidRead)
                sql = sql + " and IsRead = 0";
            sql = sql + " order by UPDATE_DATE desc";
            return GetPagedList(sql, itemIndex, size);
        }

        public PagedResult<NotificationOfAction> FindUnreadByPage(int itemIndex, int size)
        {
            string sql = "select " + cols + " from notificationofaction where IsRead = 0 order by UPDATE_DATE desc";
            return GetPagedList(sql, itemIndex, size);
        }
    }
}