Winsoft.GOV/Winsoft.GOV.Framework/Provider/OnceRunPowerMattersProvider.cs

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

namespace Winsoft.GOV.Framework.Provider
{
    public class OnceRunPowerMattersProvider : 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 oncerunpowermatters("
                           + "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('oncerunpowermatters', 'index1', 'index1(QL_INNER_CODE, OUGUID)'); "
                           + "call add_index('oncerunpowermatters', 'index2', 'index2(QL_INNER_CODE)'); "
                           + "call add_index('oncerunpowermatters', 'index3', 'index3(OUGUID)'); "
                           + "call add_index('oncerunpowermatters', 'index4', 'index4(QL_STATE, OUGUID); "
                           + "call add_index('oncerunpowermatters', 'index5', 'index5(QL_STATE, QL_KIND); ";

            using (var con = GetDbConnection())
            {
                con.Execute(sql);
            }
        }

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

        }

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

        public void Insert(IList<PowerMattersBase> list)
        {
            string select = "select count(*) from oncerunpowermatters where QL_INNER_CODE=@QL_INNER_CODE and OUGUID=@OUGUID;";
            string update = "update oncerunpowermatters set QL_NAME=@QL_NAME, QL_DEP=@QL_DEP where QL_INNER_CODE=@QL_INNER_CODE and OUGUID=@OUGUID;";
            string insert = "insert oncerunpowermatters(QL_NAME, QL_INNER_CODE, OUGUID, QL_DEP) values(@QL_NAME, @QL_INNER_CODE, @OUGUID, @QL_DEP);";

            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 bool IsExistByQL_INNER_CODE(string ql_inner_code)
        {
            string sql = "select ouguid 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;
            }
        }

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

        public void Insert(PowerMattersBase p)
        {
            string select = "select count(*) from oncerunpowermatters where QL_INNER_CODE=@QL_INNER_CODE and OUGUID=@OUGUID;";
            string update = "update oncerunpowermatters set QL_NAME=@QL_NAME, QL_DEP=@QL_DEP where QL_INNER_CODE=@QL_INNER_CODE and OUGUID=@OUGUID;";
            string insert = "delete from oncerunpowermatters where QL_INNER_CODE=@QL_INNER_CODE and OUGUID=@OUGUID; "
                + "insert oncerunpowermatters(QL_NAME, QL_INNER_CODE, OUGUID, QL_DEP) values(@QL_NAME, @QL_INNER_CODE, @OUGUID, @QL_DEP);";

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

                }
            }
        }

        public PowerMattersBase FindByQLNameAndOUORGCODE(string ql_name, string ouguid)
        {
            string sql = "select  " + cols + " from oncerunpowermatters where ql_name=@ql_name and ouguid=@ouguid";
            using (IDbConnection connection = GetDbConnection())
            {
                return connection.QueryFirstOrDefault<PowerMattersBase>(sql, new { ouguid = ouguid, ql_name = ql_name });
            }
        }
        public IEnumerable<PowerMattersBase> FindOnceRunPowerMattersByBranchGUID(string guid)
        {
            string sql = "select " + cols + " from oncerunpowermatters where ouguid=@guid";
            using (IDbConnection connection = GetDbConnection())
            {
                return connection.Query<PowerMattersBase>(sql, new { guid = guid });
            }
        }

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

        public IEnumerable<PowerMattersBase> FindByBranchGUIDAndApplyType(string guid, int type)
        {
            string sql = " SELECT QL_INNER_CODE, QL_NAME FROM powermattertype WHERE ouguid=@ouguid and ApplyCode=@code";
            using (IDbConnection connection = GetDbConnection())
            {
                return connection.Query<PowerMattersBase>(sql, new { code = type, ouguid = guid });
            }
            //if (type > -1 && type < ApplyType.Names.Length)
            //{
            //    string sql = "select " + cols + " from oncerunpowermatters where ouguid=@ouguid and QL_INNER_CODE in (SELECT QL_INNER_CODE FROM powermattertype WHERE ouguid=@ouguid and ApplyCode=@code)";
            //    using (IDbConnection connection = GetDbConnection())
            //    {
            //        return connection.Query<PowerMattersBase>(sql, new { code = type, ouguid = guid });
            //    }
            //}
            //else
            //{
            //    string sql = "select " + cols + " from oncerunpowermatters where ouguid=@ouguid and QL_INNER_CODE not in (SELECT QL_INNER_CODE FROM powermattertype WHERE ouguid=@ouguid)";
            //    using (IDbConnection connection = GetDbConnection())
            //    {
            //        return connection.Query<PowerMattersBase>(sql, new { ouguid = guid });
            //    }
            //}
        }
    }
}