NDBGJK5.0/IDEForNDBG/Helper/BaseTableHelper.cs

using KGIS.Framework.DBOperator;
using KGIS.Framework.Utils;
using KGIS.Framework.Utils.Helper;
using Kingo.PluginServiceInterface;
using Kingo.PluginServiceInterface.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace IDGForNDBG
{
    public class BaseTableHelper
    {
        /// <summary>
        /// 获取基础统计表
        /// </summary>
        /// <returns></returns>
        public static void GetTheBaseSumTable(IDGParameter pParm)
        {
            IRDBHelper rdbHelper = null;
            try
            {
                // 链接数据库
                string dbPath = $"{pParm.PrjInfo.ProjDir}\\BGTJ.sqlite";
                rdbHelper = RDBFactory.CreateDbHelper("Data Source=" + dbPath, DatabaseType.SQLite);

                if (rdbHelper == null)
                {
                    Console.WriteLine("增量汇总失败，数据库连接错误！");
                    return;
                }

                // 得到 -准- 基础统计表(平方米)(此时的表空值和空字符串没有合并，而且相应的权属没有映射)
                //string sql = "SELECT " +
                //    "BGQDLBM, BGQQSXZ, SUBSTR(BGQZLDWDM,1,12) AS BGQZLDWDM, BGQGDLX, BGQGDPDJB, BGQTBXHDM, BGQZZSXDM, BGQCZCSXM, BGQMSSM, " +
                //    "BGHDLBM, BGHQSXZ, SUBSTR(BGHZLDWDM,1,12) AS BGHZLDWDM, BGHGDLX, BGHGDPDJB, BGHTBXHDM, BGHZZSXDM, BGHCZCSXM, BGHMSSM, " +
                //    "SUM(BGMJ) AS BGMJ,XZQTZLX FROM BGYLB " +
                //    "GROUP BY " +
                //    "BGQDLBM, BGQQSXZ, SUBSTR(BGQZLDWDM,1,12), BGQGDLX, BGQGDPDJB, BGQTBXHDM, BGQZZSXDM, BGQCZCSXM, BGQMSSM, " +
                //    "BGHDLBM, BGHQSXZ, SUBSTR(BGHZLDWDM,1,12), BGHGDLX, BGHGDPDJB, BGHTBXHDM, BGHZZSXDM, BGHCZCSXM, BGHMSSM,XZQTZLX";
                string sql = "SELECT BGQDLBM, BGQQSXZ,BGQZLDWDM,BGQGDLX,BGQGDPDJB,BGQTBXHDM, BGQZZSXDM,BGQCZCSXM,BGQMSSM, BGHDLBM,BGHQSXZ,BGHZLDWDM,BGHGDLX,BGHGDPDJB,BGHTBXHDM,BGHZZSXDM,BGHCZCSXM,BGHMSSM, XZQTZLX,SUM(BGMJ)BGMJ FROM (SELECT BGQDLBM,case when BGQQSXZ = '10' or BGQQSXZ = '20' or BGQQSXZ = '21' then '国有' ELSE '集体'  end BGQQSXZ,case when BGHQSXZ = '10' or BGHQSXZ = '20' or BGHQSXZ = '21' then '国有' ELSE '集体'  end BGHQSXZ,SUBSTR(BGQZLDWDM, 1, 12) BGQZLDWDM,BGQGDLX,BGQGDPDJB,BGQTBXHDM,BGQZZSXDM, BGQCZCSXM,BGQMSSM,BGHDLBM,SUBSTR(BGHZLDWDM, 1, 12) BGHZLDWDM,BGHGDLX, BGHGDPDJB,BGHTBXHDM, BGHZZSXDM,BGHCZCSXM,TRIM(IFNULL(BGHMSSM, '00') ) BGHMSSM,XZQTZLX,BGMJ FROM BGYLB ) GROUP BY BGQDLBM,BGQQSXZ,BGQZLDWDM,BGQGDLX,BGQGDPDJB,BGQTBXHDM,BGQZZSXDM,BGQCZCSXM, BGQMSSM,BGHDLBM,BGHQSXZ,BGHZLDWDM,BGHGDLX,BGHGDPDJB,BGHTBXHDM,BGHZZSXDM,BGHCZCSXM,BGHMSSM,XZQTZLX";
                DataTable dt = rdbHelper.ExecuteDatatable2("BASETABLE", sql, true);
                string dropTableSql = "DROP TABLE JCTJB_Temp";
                rdbHelper.ExecuteSQL(dropTableSql);
                string createTableSql = "Create TABLE JCTJB_Temp ( " +
                    "BGQZLDWDM text(100)," +
                    "BGQQSXZ text(100)," +
                    "BGQGDLX text(100)," +
                    "BGQGDPDJB text(100)," +
                    "BGQCZCSXM text(100)," +
                    "BGQTBXHDM text(100)," +
                    "BGQZZSXDM text(100)," +
                    "BGQMSSM text(100)," +
                    "BGQDLBM text(100)," +
                    "BGHZLDWDM text(100)," +
                    "BGHQSXZ text(100)," +
                    "BGHGDLX text(100)," +
                    "BGHGDPDJB text(100)," +
                    "BGHCZCSXM text(100)," +
                    "BGHTBXHDM text(100)," +
                    "BGHZZSXDM text(100)," +
                    "BGHMSSM text(100)," +
                    "BGHDLBM text(100)," +
                    "BGMJ double(16,10), " +
                    "XZQTZLX text(100)" +
                    ") ";
                rdbHelper.ExecuteSQL(createTableSql);

                Dictionary<string, string> dic = new Dictionary<string, string>();
                foreach (DataColumn item in dt.Columns)
                {
                    dic.Add(item.ColumnName, "空");
                }

                string insertSql = string.Empty;
                List<string> lstSql_1 = new List<string>();
                lstSql_1.Clear();
                foreach (DataRow row in dt.Rows)
                {
                    for (int i = 0; i < dic.Count; i++)
                    {
                        KeyValuePair<string, string> pair = dic.ElementAt(i);
                        dic[pair.Key] = "空";
                        if (row[pair.Key] == null || string.IsNullOrWhiteSpace(row[pair.Key].ToString())) continue;
                        //if (pair.Key == "BGQQSXZ" || pair.Key == "BGHQSXZ")
                        //{
                        //    dic[pair.Key] = row[pair.Key].ToString() == "10" || row[pair.Key].ToString() == "20" ? "国有" : "集体";
                        //    continue;
                        //}
                        dic[pair.Key] = row[pair.Key].ToString();
                    }

                    insertSql = string.Format("INSERT INTO JCTJB_Temp ( " +
                        "BGQZLDWDM , " +
                        "BGQQSXZ , " +
                        "BGQGDLX , " +
                        "BGQGDPDJB , " +
                        "BGQCZCSXM , " +
                        "BGQTBXHDM , " +
                        "BGQZZSXDM , " +
                        "BGQMSSM , " +
                        "BGQDLBM , " +
                        "BGHZLDWDM , " +
                        "BGHQSXZ , " +
                        "BGHGDLX , " +
                        "BGHGDPDJB , " +
                        "BGHCZCSXM , " +
                        "BGHTBXHDM , " +
                        "BGHZZSXDM , " +
                        "BGHMSSM , " +
                        "BGHDLBM , " +
                        "BGMJ,XZQTZLX ) " +
                        "VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}' , '{9}', '{10}', '{11}', '{12}', '{13}', '{14}', '{15}', '{16}', '{17}' , '{18}','{19}')",
                    dic["BGQZLDWDM"],
                    dic["BGQQSXZ"],
                    dic["BGQGDLX"],
                    dic["BGQGDPDJB"],
                    dic["BGQCZCSXM"],
                    dic["BGQTBXHDM"],
                    dic["BGQZZSXDM"],
                    dic["BGQMSSM"],
                    dic["BGQDLBM"],
                    dic["BGHZLDWDM"],
                    dic["BGHQSXZ"],
                    dic["BGHGDLX"],
                    dic["BGHGDPDJB"],
                    dic["BGHCZCSXM"],
                    dic["BGHTBXHDM"],
                    dic["BGHZZSXDM"],
                    dic["BGHMSSM"],
                    dic["BGHDLBM"],
                    dic["BGMJ"],
                    dic["XZQTZLX"]
                    );
                    //int it = rdbHelper.ExecuteSQL(insertSql);
                    lstSql_1.Add(insertSql);
                }

                //ExcuteInsertSqlList(lstSql_1, rdbHelper);
                InsertSQLiteData(dbPath, lstSql_1);
                lstSql_1.Clear();

                sql = "SELECT " +
                    "BGQDLBM, BGQQSXZ, BGQZLDWDM, BGQGDLX, BGQGDPDJB, BGQTBXHDM, BGQZZSXDM, BGQCZCSXM, BGQMSSM, " +
                    "BGHDLBM, BGHQSXZ, BGHZLDWDM, BGHGDLX, BGHGDPDJB, BGHTBXHDM, BGHZZSXDM, BGHCZCSXM, BGHMSSM, " +
                    "round(sum(ifnull(BGMJ/10000 + 0.0000001,0)),2) AS BGMJ,XZQTZLX FROM JCTJB_Temp " +
                    "GROUP BY " +
                    "BGQDLBM, BGQQSXZ, BGQZLDWDM, BGQGDLX, BGQGDPDJB, BGQTBXHDM, BGQZZSXDM, BGQCZCSXM, BGQMSSM, " +
                    "BGHDLBM, BGHQSXZ, BGHZLDWDM, BGHGDLX, BGHGDPDJB, BGHTBXHDM, BGHZZSXDM, BGHCZCSXM, BGHMSSM,XZQTZLX";
                dt = rdbHelper.ExecuteDatatable2("BASETABLE", sql, true);

                // 公顷表制作
                dropTableSql = "DROP TABLE JCTJB_GQ";
                rdbHelper.ExecuteSQL(dropTableSql);
                createTableSql = "Create TABLE JCTJB_GQ ( " +
                    "ID INTEGER PRIMARY KEY AUTOINCREMENT," +
                    "BGQZLDWDM text(100)," +
                    "BGQQSXZ text(100)," +
                    "BGQGDLX text(100)," +
                    "BGQGDPDJB text(100)," +
                    "BGQCZCSXM text(100)," +
                    "BGQTBXHDM text(100)," +
                    "BGQZZSXDM text(100)," +
                    "BGQMSSM text(100)," +
                    "BGQDLBM text(100)," +
                    "BGHZLDWDM text(100)," +
                    "BGHQSXZ text(100)," +
                    "BGHGDLX text(100)," +
                    "BGHGDPDJB text(100)," +
                    "BGHCZCSXM text(100)," +
                    "BGHTBXHDM text(100)," +
                    "BGHZZSXDM text(100)," +
                    "BGHMSSM text(100)," +
                    "BGHDLBM text(100)," +
                    "BGMJ double(16,10), " +
                    "XZQTZLX text(100) " +
                    ") ";
                rdbHelper.ExecuteSQL(createTableSql);

                List<string> lstSql_2 = new List<string>();
                lstSql_2.Clear();
                foreach (DataRow row in dt.Rows)
                {
                    for (int i = 0; i < dic.Count; i++)
                    {
                        KeyValuePair<string, string> pair = dic.ElementAt(i);
                        dic[pair.Key] = "空";
                        if (row[pair.Key] == null || string.IsNullOrWhiteSpace(row[pair.Key].ToString())) continue;
                        //if (pair.Key == "BGMJ")
                        //{
                        //    dic[pair.Key] = Math.Round(Convert.ToDecimal(row[pair.Key].ToString()) * Convert.ToDecimal(0.0001), 2, MidpointRounding.AwayFromZero).ToString();
                        //    continue;
                        //}
                        dic[pair.Key] = row[pair.Key].ToString();
                    }

                    insertSql = string.Format("INSERT INTO JCTJB_GQ ( " +
                        "BGQZLDWDM , " +
                        "BGQQSXZ , " +
                        "BGQGDLX , " +
                        "BGQGDPDJB , " +
                        "BGQCZCSXM , " +
                        "BGQTBXHDM , " +
                        "BGQZZSXDM , " +
                        "BGQMSSM , " +
                        "BGQDLBM , " +
                        "BGHZLDWDM , " +
                        "BGHQSXZ , " +
                        "BGHGDLX , " +
                        "BGHGDPDJB , " +
                        "BGHCZCSXM , " +
                        "BGHTBXHDM , " +
                        "BGHZZSXDM , " +
                        "BGHMSSM , " +
                        "BGHDLBM , " +
                        "BGMJ,XZQTZLX ) " +
                        "VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}' , '{9}', '{10}', '{11}', '{12}', '{13}', '{14}', '{15}', '{16}', '{17}' , '{18}','{19}')",
                    dic["BGQZLDWDM"],
                    dic["BGQQSXZ"],
                    dic["BGQGDLX"],
                    dic["BGQGDPDJB"],
                    dic["BGQCZCSXM"],
                    dic["BGQTBXHDM"],
                    dic["BGQZZSXDM"],
                    dic["BGQMSSM"],
                    dic["BGQDLBM"],
                    dic["BGHZLDWDM"],
                    dic["BGHQSXZ"],
                    dic["BGHGDLX"],
                    dic["BGHGDPDJB"],
                    dic["BGHCZCSXM"],
                    dic["BGHTBXHDM"],
                    dic["BGHZZSXDM"],
                    dic["BGHMSSM"],
                    dic["BGHDLBM"],
                    dic["BGMJ"],
                    dic["XZQTZLX"]
                    );
                    //int it = rdbHelper.ExecuteSQL(insertSql);
                    lstSql_2.Add(insertSql);
                }
                InsertSQLiteData(dbPath, lstSql_2);

       //         rdbHelper.ExecuteSQL(@"DROP VIEW V_TZHNCMJ_PCS");
       //         rdbHelper.ExecuteSQL(@" CREATE VIEW V_TZHNCMJ_PCS as
       //         SELECT round(kzmj-trmj-ifnull(tcmj,0),2)*100 mjc from 
			    //(
				   // (select round(sum(ifnull(BGMJ/10000 + 0.0000001,0)),2) kzmj from JCTJB_Temp where XZQTZLX='1' or XZQTZLX='3' or XZQTZLX='2' or XZQTZLX='4'),
				   // (select sum(bgmj) trmj from JCTJB_GQ where XZQTZLX='1' or XZQTZLX='3'),
				   // (select sum(bgmj) tcmj from JCTJB_GQ where XZQTZLX='2' or XZQTZLX='4')
			    //)");
       //         DataTable isTrTb = rdbHelper.ExecuteDatatable("IsTR", "select sum(bgmj) trmj from JCTJB_GQ where XZQTZLX='1' or XZQTZLX='3'", true);
       //         if (isTrTb.Rows.Count > 0 && isTrTb.Rows[0][0].ToInt() > 0)
       //         {
       //             rdbHelper.ExecuteSQL(@"update JCTJB_GQ set bgmj=bgmj+0.01 where id in 
       //                                (
       //                                 select * from JCTJB_GQ ORDER by BGMJ desc,BGHDLBM desc,BGHQSXZ,BGHZLDWDM,BGHGDLX,BGHGDPDJB,BGHTBXHDM,BGHZZSXDM,BGHCZCSXM,BGHMSSM
       //                                  LIMIT (select cast(mjc as int) from V_TZHNCMJ_PCS)
       //                                 )");
       //         }
       //         else
       //         {
                    
       //         }
                //string sql_gq = "update JCTJB_GQ set bgmj=round(ifnull(bgmj/10000 + 0.0000001,0),2)";

                //rdbHelper.ExecuteSQL(sql_gq);
                //InsertSQLiteData(dbPath, lstSql_2);
                lstSql_2.Clear();

                //海岛汇总
                rdbHelper.ExecuteSQL(" DROP TABLE HDTDLYXZBHPHTJB_TEMP ");
                rdbHelper.ExecuteSQL(@" CREATE TABLE HDTDLYXZBHPHTJB_TEMP as select substr(BGQDLBM,1,4) as BGQDLBM,substr(BGHDLBM,1,4) as BGHDLBM,Sum(BGMJ) as MJ from JCTJB_GQ where substr(BGQDLBM,1,4)<>substr(BGHDLBM,1,4) and BGQMSSM='01' and BGHMSSM='01' group by substr(BGQDLBM,1,4),substr(BGHDLBM,1,4) ");

                //土地利用现状一级分类面积按权属性质汇总表
                rdbHelper.ExecuteSQL(" DROP TABLE TDLYXZBHPHTJB_TEMP ");
                rdbHelper.ExecuteSQL(@" CREATE TABLE TDLYXZBHPHTJB_TEMP as select substr(BGQDLBM,1,4) as BGQDLBM,substr(BGHDLBM,1,4) as BGHDLBM,Sum(BGMJ) as MJ from JCTJB_GQ where substr(BGQDLBM,1,4)<>substr(BGHDLBM,1,4) group by substr(BGQDLBM,1,4),substr(BGHDLBM,1,4) ");

                return;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rdbHelper != null && rdbHelper.State != System.Data.ConnectionState.Closed)
                    rdbHelper.DisConnect();
            }
        }

        public static void ExcuteInsertSqlList(List<string> lstSql, IRDBHelper rdbHelper)
        {
            //if (lstSql != null && lstSql.Count() > 0)
            //{
            //    LogAPI.Debug("开始写入 " + lstSql.Count() + " 条数据;\r\n ");
            //}

            int i = 0;
            try
            {
                foreach (string sql in lstSql)
                {
                    try
                    {
                        i = rdbHelper.ExecuteNonQueryWithException(sql, System.Data.CommandType.Text);
                    }
                    catch (Exception sqlEx)
                    {
                        LogAPI.Debug(sqlEx);
                        LogAPI.Debug("出错语句" + sql);
                    }
                }
            }
            catch (Exception ex)
            {
                LogAPI.Debug("批量执行SQL语句时失败，异常原因: " + ex + " ; ");
                //if (rdbHelper != null)
                //    rdbHelper.DisConnect();
            }
            finally
            {
                //LogAPI.Debug(lstSql.Count() + " 条数据写入完成 ;\r\n ");
                //if (rdbHelper != null)
                //    rdbHelper.DisConnect();
            }
        }

        /// <summary>
        /// 批量插入SQLite数据
        /// </summary>
        /// <param name="dbPath"></param>
        /// <param name="sqlList"></param>
        public static void InsertSQLiteData(string dbPath, List<string> sqlList)
        {
            try
            {
                //DateTime dtS = DateTime.Now;
                //LogAPI.Debug(sqlList.Count() + " 条; ");
                using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("Data Source=" + dbPath))
                {
                    conn.Open();
                    using (System.Data.SQLite.SQLiteTransaction pTrans = conn.BeginTransaction())
                    {
                        using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn))
                        {
                            for (int i = 0; i < sqlList.Count(); i++)
                            {
                                cmd.CommandText = sqlList[i];
                                cmd.ExecuteNonQuery();
                            }
                            pTrans.Commit();
                        }
                    }
                    conn.Close();
                }

                //string strTimeSpan = Convert.ToInt32(((TimeSpan)(DateTime.Now - dtS)).TotalSeconds).ToString();
                ////写入成功失败的要素个数
                //string strLog = "用时：" + strTimeSpan + "秒";
                //LogAPI.Debug(" 完成," + strLog);
            }
            catch (Exception ex)
            {
                LogAPI.Debug("批量插入SQLite数据(地址:" + dbPath + ") 执行失败,异常原因: " + ex + " ; ");
                return;
            }
        }

    }
}