Winsoft.GOV/Winsoft.GOV.Framework/Provider/PowerMettersBaseProvider.cs

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

namespace Winsoft.GOV.Framework.Provider
{
    public class PowerMettersBaseProvider : BaseProvider<PowerMattersBase>
    {
        private static string cols = "QL_NAME, QL_INNER_CODE, OUGUID, QL_DEP, QL_STATE, QL_KIND";
        private static string sets = "QL_NAME=@QL_NAME, QL_DEP=@QL_DEP, OUGUID=@OUGUID, QL_STATE=@QL_STATE, QL_KIND=@QL_KIND";
        private static string vals = "@QL_NAME, @QL_INNER_CODE, @OUGUID, @QL_DEP, @QL_STATE, @QL_KIND";
        public void CreateTable()
        {
            string sql = "create table if not exists powermattersbase("
                           + "QL_NAME varchar(200),"
                           + "QL_INNER_CODE varchar(50),"
                           + "QL_DEP varchar(50),"
                           + "OUGUID varchar(50),"
                           + "QL_KIND varchar(2),"
                           + "QL_STATE varchar(1) DEFAULT '1'"
                           + "); "
                           + "call add_index('powermattersbase', 'index1', 'index1(QL_INNER_CODE, OUGUID)'); "
                           + "call add_index('powermattersbase', 'index2', 'index2(QL_INNER_CODE)'); "
                           + "call add_index('powermattersbase', 'index3', 'index3(OUGUID)'); "
                           + "call add_index('powermattersbase', 'index4', 'index4(QL_STATE, OUGUID); "
                           + "call add_index('powermattersbase', 'index5', 'index5(QL_STATE, QL_KIND); ";
            using (var con = GetDbConnection())
            {
                con.Execute(sql);
            }
        }

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

        }

        public void DeleteAll()
        {
            using (IDbConnection connection = GetDbConnection())
            {
                connection.Execute("delete from powermattersbase");
            }
        }

        public IEnumerable<PowerMattersBase> FindByBranchGUID(string guid)
        {
            //string sql = "select " + cols + " from powerMettersDetail where  QL_INNER_CODE in (select QL_INNER_CODE PowerMattersBase where ouguid=@guid) and QL_STATE = '1'";
            string sql = "select " + cols + " from powermattersbase where  ouguid=@guid and QL_STATE = '1'";
            using (IDbConnection connection = GetDbConnection())
            {
                return connection.Query<PowerMattersBase>(sql, new { guid = guid });
            }
        }

        public IEnumerable<PowerMattersBase> Find(string guid, string ql_kind)
        {
            string sql = "select " + cols + " from powermettersdetail where OUGUID=@guid and ql_kind = @ql_kind and QL_INNER_CODE in (SELECT QL_INNER_CODE FROM powermattersbase WHERE ouguid=@guid and ql_state='1')";
            using (IDbConnection connection = GetDbConnection())
            {
                return connection.Query<PowerMattersBase>(sql, new { guid = guid, ql_kind = ql_kind });
            }
        }

        public PagedResult<PowerMattersBase> FindByPage(int itemIndex, int size)
        {
            string sql = "select " + cols + " from powermattersbase where ql_state='1'";
            return GetPagedList(sql, itemIndex, size);
        }

        public PagedResult<PowerMattersBase> FindByPage(int itemIndex, int size, string[] ql_kinds)
        {
            string sql = "select  " + cols + " from powermattersbase where ql_state='1'";

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

        public void UpdateOrInsert(PowerMattersBase p)
        {
            using (var con = GetDbConnection())
            {
                string sql = "insert into powermattersbase(" + cols + ") values(" + vals + ") on duplicate key update " + sets;
                con.Execute(sql, p);
            }
        }

        public PagedResult<PowerMattersBase> FindByPage(string key, int itemIndex, int size, string[] ql_kinds)
        {
            string sql = "select  " + cols + " from powermattersbase where ql_state='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 + "%')";
            return GetPagedList(sql, itemIndex, size);
        }

        public PagedResult<PowerMattersBase> FindByPage(string key, int itemIndex, int size)
        {
            if (String.IsNullOrWhiteSpace(key))
                return FindByPage(itemIndex, size);
            string sql = "select " + cols + " from powermattersbase where ql_state='1' and (ql_name like '%" + key + "%' or ql_dep like '%" + key + "%')";
            return GetPagedList(sql, itemIndex, size);
        }

        public IEnumerable<PowerMattersBase> Search(string ql_name, int itemIndex, int size)
        {
            string sql = "select " + cols + " from powermattersbase where ql_name like @ql_name and ql_state='1'";
            return GetItemList(sql, itemIndex, size, new { ql_name = "%" + ql_name + "%" });
        }

        public bool IsExistByQL_INNER_CODE(string ql_inner_code)
        {
            string sql = "select ouguid from powermattersbase where ql_inner_code=@ql_inner_code";
            using (IDbConnection connection = GetDbConnection())
            {
                IEnumerable<string> tmp = connection.Query<string>(sql, new { ql_inner_code = ql_inner_code });
                return tmp != null && tmp.Count() > 0;
            }
        }

        public void Insert(IList<PowerMattersBase> list)
        {
            string select = "select count(*) from powermattersbase where QL_INNER_CODE=@QL_INNER_CODE and OUGUID=@OUGUID;";
            string update = "update powermattersbase set " + sets + " where QL_INNER_CODE=@QL_INNER_CODE and OUGUID=@OUGUID;";
            string insert = "insert powermattersbase("+cols+") values("+vals+");";

            using (var conn = GetDbConnection())
            {
                foreach(PowerMattersBase p in list)
                {
                    try
                    {
                        //int count = conn.QueryFirstOrDefault<int>(select, p);
                        //if (count <= 0)
                        conn.Execute(insert, p);
                        //else
                        //    conn.Execute(update, p);
                    }
                    catch (Exception e)
                    {
                        
                    }
                }             
            }
        }

        public void Update(PowerMattersBase p)
        {
            string update = "update powermattersbase set " + sets + " where QL_INNER_CODE=@QL_INNER_CODE and OUGUID=@OUGUID;";
            using (IDbConnection connection = GetDbConnection())
            {
                connection.Execute(update, p);
            }
        }

        public void Insert(PowerMattersBase p)
        {
            string insert = "insert powermattersbase(" + cols + ") values(" + vals + ");";

            using (IDbConnection connection = GetDbConnection())
            {
                connection.Execute(insert, p);
            }
        }

        public bool IsOnceRun(string ql_inner_code)
        {
            string sql = "select ql_inner_code from oncerunpowermatters where ql_inner_code=@ql_inner_code";
            using (IDbConnection connection = GetDbConnection())
            {
                IEnumerable<string> tmp = connection.Query<string>(sql, new { ql_inner_code = ql_inner_code });
                return tmp != null && tmp.Count() > 0;
            }
        }
    }
}