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,"
                           + "QL_DEP,"
                           + "OUGUID,"
                           + "AType,"
                           + "UPDATE_DATE,"
                           + "IsRead";
        private static string vals = "@QL_NAME,"
                           + "@QL_INNER_CODE,"
                           + "@QL_DEP,"
                           + "@OUGUID,"
                           + "@AType,"
                           + "@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),"
                           + "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('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 QL_NAME = @QL_NAME, QL_DEP = @QL_DEP, OUGUID = @OUGUID, AType = @AType, IsRead = @IsRead, UPDATE_DATE=@UPDATE_DATE";
                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> FindUnreadByPage(int itemIndex, int size)
        {
            string sql = "select " + cols + " from NotificationOfAction where IsRead = 0 order by UPDATE_DATE desc";
            return GetPagedList(sql, itemIndex, size);
        }
    }
}