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_INNER_CODE`, "
                    + "`QL_NAME` ";
        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)"
                           + "); "
                           + "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('PowerMattersBase', 'index4', 'index4(QL_NAME)'); ";
            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 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 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 });
            //    }
            //}
        }
    }
}