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 PowerMettersBaseProvider instance;
        private static string cols = " `QL_INNER_CODE`, "
                    + "`QL_NAME` ";
        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)"
                           + "); "
                           + "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_NAME)'); ";
            using(var con = GetDbConnection())
            {
                con.Execute(sql);
            }
        }
        public PowerMettersBaseProvider() : base("gov")
        {
            
        }

        public static PowerMettersBaseProvider Instance
        {
            get
            {
                CreateInstance(ref instance);
                return instance;
            }
        }

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

        public IEnumerable<PowerMattersBase> FindByBranchGUID(string guid)
        {
            string sql = "select " + cols + " from PowerMattersBase where ouguid=@guid";
            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)";
            using (IDbConnection connection = GetDbConnection())
            {
                return connection.Query<PowerMattersBase>(sql, new { guid = guid, ql_kind = ql_kind });
            }
        }

        public IEnumerable<PowerMattersBase> Search(string ql_name, int itemIndex, int size)
        {
            string sql = "select " + cols + " from PowerMattersBase where ql_name like @ql_name";
            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 QL_NAME=@QL_NAME, QL_DEP=@QL_DEP where QL_INNER_CODE=@QL_INNER_CODE and OUGUID=@OUGUID;";
            string insert = "insert PowerMattersBase(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 Update(PowerMattersBase p)
        {
            //const string sql = "UPDATE `powermettersbase` "
            //                    + "SET "
            //                    + "`QL_MAINITEM_ID` = @QL_MAINITEM_ID, "
            //                    + "`QL_SUBITEM_ID` = @QL_SUBITEM_ID, "
            //                    + "`QL_NAME` = @QL_NAME, "
            //                    + "`QL_KIND` = @QL_KIND, "
            //                    + "`QL_STATE` = @QL_STATE, "
            //                    + "`OUGUID` = @OUGUID, "
            //                    + "`QL_DEP` = @QL_DEP, "
            //                    + "`UPDATE_DATE` = @UPDATE_DATE, "
            //                    + "`APPLYERMIN_COUNT_DESC` = @APPLYERMIN_COUNT_DESC "
            //                    + " WHERE `ROWGUID` = @ROWGUID; ";
            //using (IDbConnection connection = GetDbConnection())
            //{
            //    connection.Execute(sql, p);
            //}
        }

        public void Insert(PowerMattersBase p)
        {
            //string sql = "INSERT INTO `powermettersbase`"
            //            + "(`ROWGUID`, "
            //            + "`QL_MAINITEM_ID`, "
            //            + "`QL_SUBITEM_ID`, "
            //            + "`QL_NAME`, "
            //            + "`QL_KIND`, "
            //            + "`QL_STATE`, "
            //            + "`OUGUID`, "
            //            + "`ShortName`, "
            //            + "`QL_DEP`, "
            //            + "`UPDATE_DATE`, "
            //            + "`IsOnceRun`, "
            //            + "`APPLYERMIN_COUNT_DESC`) "
            //            + "VALUES"
            //            + "(@ROWGUID, "
            //            + "@QL_MAINITEM_ID, "
            //            + "@QL_SUBITEM_ID, "
            //            + "@QL_NAME, "
            //            + "@QL_KIND, "
            //            + "@QL_STATE, "
            //            + "@OUGUID, "
            //            + "@ShortName, "
            //            + "@QL_DEP, "
            //            + "@UPDATE_DATE, "
            //            + "@IsOnceRun, "
            //            + "@APPLYERMIN_COUNT_DESC); ";
            //using (IDbConnection connection = GetDbConnection())
            //{
            //    connection.Execute(sql, 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;
            }
        }
    }
}