// 下水データ管理システム
// Copyright (C) 2018 公益財団法人とちぎ建設技術センター(https://www.tochigictc.or.jp/)
//
// This program is free software: you can redistribute it and/or modify
// it under the terms of the GNU General Public License as published by
// the Free Software Foundation, either version 3 of the License, or
// (at your option) any later version.
//
// This program is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.See the
// GNU General Public License for more details.
//
// You should have received a copy of the GNU General Public License
// along with this program.If not, see<http://www.gnu.org/licenses/>.

using ClosedXML.Excel;
using System;
using System.ComponentModel;
using System.Data.SQLite;
using System.IO;
using System.Windows.Forms;

namespace WindowsFormsApplication1 {
    public partial class FormD3QuarterDataSelect : Form {
        public FormD3QuarterDataSelect() {
            InitializeComponent();
        }

        int cBYear = 0;
        int cQuarter = 0;

        BindingList<KanrisyaSyorijou> KSCBItem = new BindingList<KanrisyaSyorijou>();
        BindingList<SelectableQuarter> SMCBItem = new BindingList<SelectableQuarter>();




        //ロード時イベント
        private void FormD3ResearchDataSelect_Load(object sender, EventArgs e) {
            //選択リスト準備
            dataSelectInitialize();
            quarterSelectInitialize();

            dataSelectConstruction();
            quarterSelectConstruction();
        }




        //管理者・処理場選択ボックス事前準備
        private void dataSelectInitialize() {
            comboBox1.DisplayMember = "selectionName";
            comboBox1.ValueMember = "primaryKey";
            comboBox1.DataSource = KSCBItem;
            comboBox1.SelectedIndexChanged += new EventHandler(dataSelect_SelectedIndexChanged);
        }




        //四半期選択ボックス事前準備
        private void quarterSelectInitialize() {
            comboBox2.DisplayMember = "selectionName";
            comboBox2.ValueMember = "primaryKey";
            comboBox2.DataSource = SMCBItem;
            comboBox2.SelectedIndexChanged += new EventHandler(quarterSelect_SelectedIndexChanged);
        }




        //管理者・処理場選択ボックス生成
        private void dataSelectConstruction(string cKanrisya = null, string cSyorijou = null) {
            comboBox1.SelectedIndexChanged -= dataSelect_SelectedIndexChanged;
            comboBox1.BeginUpdate();
            KSCBItem.Clear();

            using(var con = new SQLiteConnection(DataBaseConfig.ConnectionString)) {
                con.Open();

                using(var cmd = con.CreateCommand()) {
                    cmd.CommandText = "SELECT t1.kanrisya, t1.syorijou, t2.ck FROM suishitsu_kanri AS t1 " +
                        "INNER JOIN (SELECT kanrisya, COUNT(kanrisya) AS ck FROM suishitsu_kanri WHERE del_flg IS NULL GROUP BY kanrisya) AS t2 ON t1.kanrisya = t2.kanrisya " +
                        "GROUP BY t1.kanrisya, t1.syorijou HAVING t2.ck > 0 ORDER BY t2.ck DESC, COUNT(t1.syorijou) DESC";

                    using(var reader = cmd.ExecuteReader()) {
                        int i = 0;
                        int c = 0;
                        while(reader.Read()) {
                            string kanrisya = reader.GetString(0);
                            string syorijou = reader.GetString(1);
                            KSCBItem.Add(new KanrisyaSyorijou(kanrisya, syorijou));

                            if(kanrisya == cKanrisya && syorijou == cSyorijou) {
                                c = i;
                            }

                            i++;
                        }

                        if(i > 0) {
                            comboBox1.Enabled = true;
                            comboBox1.SelectedIndex = c;
                        }
                        else {
                            comboBox1.Enabled = false;
                        }
                    }
                }
            }

            comboBox1.EndUpdate();
            comboBox1.SelectedIndexChanged += new EventHandler(dataSelect_SelectedIndexChanged);
        }




        //四半期選択ボックス生成
        private void quarterSelectConstruction(int cBYear = -1, int cQuarter = -1) {
            comboBox2.SelectedIndexChanged -= quarterSelect_SelectedIndexChanged;
            comboBox2.BeginUpdate();
            SMCBItem.Clear();

            if(comboBox1.SelectedItem != null) {
                comboBox2.Enabled = true;

                KanrisyaSyorijou item1 = (KanrisyaSyorijou)comboBox1.SelectedItem;
                string kanrisya = item1.primaryKey(0);
                string syorijou = item1.primaryKey(1);

                using(var con = new SQLiteConnection(DataBaseConfig.ConnectionString)) {
                    con.Open();

                    using(var cmd = con.CreateCommand()) {
                        cmd.CommandText = "SELECT MIN(sokutei_dt) AS min_dt, MAX(sokutei_dt) AS max_dt FROM suishitsu_kanri WHERE del_flg IS NULL;";

                        using(var reader = cmd.ExecuteReader()) {
                            if(reader.Read()) {
                                if(!reader.IsDBNull(0) && !reader.IsDBNull(1)) {
                                    //存在する四半期の最小と最大
                                    DateTime minDt = DateTime.Parse(reader.GetString(0));
                                    DateTime maxDt = DateTime.Parse(reader.GetString(1));

                                    int stMonth = minDt.Month;
                                    int stYear = minDt.Year;

                                    if(stMonth < 4) {
                                        stMonth = 1;
                                    }
                                    else if(stMonth < 7) {
                                        stMonth = 4;
                                    }
                                    else if(stMonth < 10) {
                                        stMonth = 7;
                                    }
                                    else {
                                        stMonth = 10;
                                    }

                                    int edMonth = maxDt.Month;
                                    int edYear = maxDt.Year;

                                    if(edMonth < 4) {
                                        edMonth = 4;
                                    }
                                    else if(edMonth < 7) {
                                        edMonth = 7;
                                    }
                                    else if(edMonth < 10) {
                                        edMonth = 10;
                                    }
                                    else {
                                        edMonth = 1;
                                        edYear++;
                                    }

                                    DateTime stDt = DateTime.Parse(stYear.ToString() + "/" + stMonth.ToString() + "/1 00:00:00");
                                    DateTime edDt = DateTime.Parse(edYear.ToString() + "/" + edMonth.ToString() + "/1 00:00:00");

                                    int i = 0;
                                    int c = 0;
                                    for(DateTime cDt = stDt; cDt < edDt; cDt = cDt.AddMonths(3)) {
                                        DateTime cnDt = cDt.AddMonths(3);
                                        int cDtYear = cDt.Year;
                                        int quarter = 0;

                                        using(var cmd2 = con.CreateCommand()) {
                                            cmd2.CommandText = "SELECT COUNT(*) FROM suishitsu_kanri WHERE kanrisya='" + kanrisya + "' AND syorijou='" + syorijou + "'  AND sokutei_dt>='" + cDt.ToString("yyyy-MM-dd HH:mm:ss") + "' AND sokutei_dt<'" + cnDt.ToString("yyyy-MM-dd HH:mm:ss") + "' AND del_flg IS NULL";

                                            using(var reader2 = cmd2.ExecuteReader()) {
                                                if(reader2.Read()) {
                                                    if(reader2.GetInt32(0) > 0) {
                                                        //選択中の管理者処理場のデータがある四半期
                                                        int cDMonth = cDt.Month;

                                                        if(cDMonth == 4) {
                                                            quarter = 1;
                                                        }
                                                        else if(cDMonth == 7) {
                                                            quarter = 2;
                                                        }
                                                        else if(cDMonth == 10) {
                                                            quarter = 3;
                                                        }
                                                        else {
                                                            quarter = 4;
                                                            cDtYear--;
                                                        }

                                                        SMCBItem.Add(new SelectableQuarter(cDtYear, quarter));
                                                    }
                                                    else {
                                                        //選択中の管理者処理場のデータがない場合は空欄
                                                        SMCBItem.Add(new SelectableQuarter(-1, -1));
                                                    }
                                                }
                                            }
                                        }

                                        if(cBYear == -1 || cQuarter == -1) {
                                            //指定がない場合は前四半期を選択中に
                                            int nowYear = DateTime.Now.Year;
                                            int nowQuarter = 0;
                                            
                                            if(DateTime.Now.Month < 4) {
                                                nowQuarter = 3;
                                                nowYear--;
                                            }
                                            else if(DateTime.Now.Month < 7) {
                                                nowQuarter = 4;
                                                nowYear--;
                                            }
                                            else if(DateTime.Now.Month < 10) {
                                                nowQuarter = 1;
                                            }
                                            else {
                                                nowQuarter = 2;
                                            }

                                            if(cDtYear == nowYear && quarter == nowQuarter) {
                                                c = i;
                                            }
                                        }
                                        else {
                                            if(cDtYear == cBYear && quarter == cQuarter) {
                                                c = i;
                                            }
                                        }

                                        i++;
                                    }

                                    comboBox2.SelectedIndex = c;
                                }
                                else {
                                    comboBox2.Enabled = false;
                                }
                            }
                        }
                    }
                }
            }
            else {
                comboBox2.Enabled = false;
            }

            comboBox2.EndUpdate();
            comboBox2.SelectedIndexChanged += new EventHandler(quarterSelect_SelectedIndexChanged);
        }




        //管理者・処理場選択ボックス選択変更イベント
        private void dataSelect_SelectedIndexChanged(object sender, EventArgs e) {
            quarterSelectConstruction(cBYear);
            okButtonSwitch();
        }




        //年度選択ボックス選択変更イベント
        private void quarterSelect_SelectedIndexChanged(object sender, EventArgs e) {
            SelectableQuarter item2 = (SelectableQuarter)comboBox2.SelectedItem;

            if(item2 != null) {
                if(item2.primaryKey(0) != -1) {
                    cBYear = item2.primaryKey(0);
                }
            }

            okButtonSwitch();
        }




        //管理者・処理場、四半期が無効な場合、OKボタンを無効に
        private void okButtonSwitch() {
            if(comboBox1.Enabled && comboBox2.Enabled) {
                SelectableQuarter item2 = (SelectableQuarter)comboBox2.SelectedItem;

                if(item2 != null) {
                    if(item2.primaryKey(0) != -1) {
                        button1.Enabled = true;
                        return;
                    }
                }
            }

            button1.Enabled = false;
        }




        //OKボタンクリックイベント
        private void button1_Click(object sender, EventArgs e) {
            quarterDataExportExcel();
        }




        //四半期毎集計データ出力
        private void quarterDataExportExcel() {
            SaveFileDialog saveFileDialog1 = new SaveFileDialog();

            KanrisyaSyorijou item1 = (KanrisyaSyorijou)comboBox1.SelectedItem;
            string kanrisya = item1.primaryKey(0);
            string syorijou = item1.primaryKey(1);

            SelectableQuarter item2 = (SelectableQuarter)comboBox2.SelectedItem;
            int bYear = item2.primaryKey(0);
            int quarter = item2.primaryKey(1);

            //年度範囲
            string stBYSTR = bYear.ToString() + "-04-01 00:00:00";
            string edBYSTR = (bYear + 1).ToString() + "-04-01 00:00:00";

            //四半期範囲
            string stQSTR = "";
            string edQSTR = "";

            if(quarter == 1) {
                stQSTR = bYear.ToString() + "-04-01 00:00:00";
                edQSTR = bYear.ToString() + "-07-01 00:00:00";
            }
            else if(quarter == 2) {
                stQSTR = bYear.ToString() + "-07-01 00:00:00";
                edQSTR = bYear.ToString() + "-10-01 00:00:00";
            }
            else if(quarter == 3) {
                stQSTR = bYear.ToString() + "-10-01 00:00:00";
                edQSTR = (bYear + 1).ToString() + "-01-01 00:00:00";
            }
            else if(quarter == 4) {
                stQSTR = (bYear + 1).ToString() + "-01-01 00:00:00";
                edQSTR = (bYear + 1).ToString() + "-04-01 00:00:00";
            }
            else {
                return;
            }

            //年度集計の範囲は年度開始日から四半期集計を出力する四半期の末までとする
            edBYSTR = edQSTR;

            //ファイル名
            string kikan;

            if(bYear < 1) {
                return;
            }
            else {
                kikan = ("0000" + bYear.ToString()).Substring(bYear.ToString().Length, 4) + "年度Q" + quarter.ToString();
            }

            string fileName = "四半期毎集計データ_" + kanrisya + "_" + syorijou + "_" + kikan;
            string fileNameExtension = "xlsx";

            saveFileDialog1.FileName = fileName + "." + fileNameExtension;
            saveFileDialog1.InitialDirectory = @"%USERPROFILE%\Desktop";
            saveFileDialog1.Filter = "Excel ブック(*.xlsx)|*.xlsx";
            saveFileDialog1.FilterIndex = 1;
            saveFileDialog1.Title = "四半期毎集計データ出力";
            saveFileDialog1.RestoreDirectory = true;
            saveFileDialog1.OverwritePrompt = true;
            saveFileDialog1.CheckPathExists = true;

            //ダイアログの表示
            if(saveFileDialog1.ShowDialog() == DialogResult.OK) {
                FormA2ProgressSplash progressSplash1 = new FormA2ProgressSplash();
                progressSplash1.Owner = this;
                progressSplash1.Show();
                progressSplash1.Refresh();

                progressSplash1.ProgressMessage = "ファイル出力準備中";
                Application.DoEvents();

                fileName = saveFileDialog1.FileName;
                fileNameExtension = fileName.Substring(fileName.Length - 5);

                if(fileNameExtension != ".xlsx") {
                    fileName += ".xlsx";
                }

                try {
                    using(XLWorkbook workbook1 = new XLWorkbook(@"Template\quarter_data.xlsx")) {
                        progressSplash1.ProgressMessage = "四半期毎集計シート作成中";
                        Application.DoEvents();
                        makeSheet1(workbook1);

                        //ファイルの保存
                        progressSplash1.ProgressMessage = "ファイル保存中";
                        Application.DoEvents();

                        workbook1.SaveAs(fileName);
                        MessageBox.Show("四半期毎集計データの出力が完了しました");
                    }
                }
                catch(DirectoryNotFoundException) {
                    MessageBox.Show("テンプレートファイルのディレクトリがありません");
                }
                catch(FileNotFoundException) {
                    MessageBox.Show("テンプレートファイルがありません");
                }
                catch(IOException) {
                    MessageBox.Show("ファイルが使用中等の理由により、保存できませんでした");
                }
                catch(Exception ex) {
                    MessageBox.Show(ex.Message);
                }
                finally {
                    progressSplash1.Close();
                    progressSplash1.Dispose();
                    this.Activate();
                }
            }




            //四半期毎集計シートの記入
            void makeSheet1(XLWorkbook workbook1) {
                IXLWorksheet worksheet1;

                if(workbook1.TryGetWorksheet("四半期毎集計", out worksheet1)) {
                    //年度、管理者・処理場
                    worksheet1.Cell(1, 1).SetValue(bYear.ToString() + "年度　第" + quarter.ToString() + "四半期");
                    worksheet1.Cell(1, 3).SetValue(kanrisya + "　" + syorijou);

                    //維持管理概要(四半期集計)
                    using(var con = new SQLiteConnection(DataBaseConfig.ConnectionString)) {
                        con.Open();

                        using(var cmd = con.CreateCommand()) {
                            cmd.CommandText = "SELECT " +
                                "AVG(wv_ryuunyuu), MAX(wv_ryuunyuu), MIN(wv_ryuunyuu), " +
                                "AVG(CASE WHEN seiten='○' THEN wv_ryuunyuu ELSE NULL END), MAX(CASE WHEN seiten='○' THEN wv_ryuunyuu ELSE NULL END), MIN(CASE WHEN seiten='○' THEN wv_ryuunyuu ELSE NULL END), " +
                                "COUNT(CASE WHEN uryou>0 THEN 1 ELSE NULL END), " +
                                "COUNT(CASE WHEN seiten IS NULL THEN 1 ELSE NULL END), " +
                                "SUM(uryou), " +
                                "SUM(ov_dassui_output_all), " +
                                "TOTAL(ov_dassui_output_enshin * owr_dassui_output_enshin / 100) + TOTAL(ov_dassui_output_bp * owr_dassui_output_bp / 100) + TOTAL(ov_dassui_output_ex * owr_dassui_output_ex / 100) " +
                                "FROM suishitsu_kanri " +
                                "WHERE del_flg IS NULL AND kanrisya='" + kanrisya + "' AND syorijou='" + syorijou + "' AND sokutei_dt>='" + stQSTR + "' AND sokutei_dt<'" + edQSTR + "'";

                            using(var reader = cmd.ExecuteReader()) {
                                if(reader.Read()) {
                                    worksheet1.Cell(3, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 0), ColumnsConfigSet.nameToFormat("wv_ryuunyuu")));
                                    worksheet1.Cell(4, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 1), ColumnsConfigSet.nameToFormat("wv_ryuunyuu")));
                                    worksheet1.Cell(5, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 2), ColumnsConfigSet.nameToFormat("wv_ryuunyuu")));
                                    worksheet1.Cell(6, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 3), ColumnsConfigSet.nameToFormat("wv_ryuunyuu")));
                                    worksheet1.Cell(7, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 4), ColumnsConfigSet.nameToFormat("wv_ryuunyuu")));
                                    worksheet1.Cell(8, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 5), ColumnsConfigSet.nameToFormat("wv_ryuunyuu")));
                                    worksheet1.Cell(9, 4).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 6));
                                    worksheet1.Cell(10, 4).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 7));
                                    worksheet1.Cell(11, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 8), ColumnsConfigSet.nameToFormat("uryou")));

                                    decimal? ov_dassui_output_all = FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 9), ColumnsConfigSet.nameToFormat("ov_dassui_output_all"));
                                    decimal? owv_dassui_output_all = FunctionForDatabase.decConvFromDB(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 10), ColumnsConfigSet.nameToFormat("ov_dassui_output_all")), ColumnsConfigSet.nameToFormat("owr_dassui_output_enshin"));

                                    worksheet1.Cell(12, 4).SetValue(ov_dassui_output_all);

                                    if(ov_dassui_output_all!=null&& owv_dassui_output_all != null) {
                                        if(ov_dassui_output_all != 0) {
                                            worksheet1.Cell(13, 4).SetValue(owv_dassui_output_all / ov_dassui_output_all * 100);
                                        }
                                    }
                                }
                            }
                        }
                    }

                    //別途分析
                    for(int i = 1; i <= MaxSizeConfig.maxSpecialAnalysis; i++) {
                        //名称は最新値を採用
                        using(var con = new SQLiteConnection(DataBaseConfig.ConnectionString)) {
                            con.Open();

                            using(var cmd = con.CreateCommand()) {
                                cmd.CommandText = "SELECT " +
                                    "name_exbunseki_" + i + " " +
                                    "FROM suishitsu_kanri " +
                                    "WHERE del_flg IS NULL AND kanrisya='" + kanrisya + "' AND syorijou='" + syorijou + "' AND sokutei_dt>='" + stQSTR + "' AND sokutei_dt<'" + edQSTR + "'" +
                                    "AND name_exbunseki_" + i + " IS NOT NULL AND name_exbunseki_" + i + "<>''" +
                                    "ORDER BY sokutei_dt DESC LIMIT 1";

                                using(var reader = cmd.ExecuteReader()) {
                                    if(reader.Read()) {
                                        string name = FunctionForDatabase.getStringOrNull(reader, 0);

                                        worksheet1.Cell(17 + (i - 1) * 48, 3).SetValue(name);
                                    }
                                }
                            }
                        }

                        //年度集計(年度累計回数)
                        using(var con = new SQLiteConnection(DataBaseConfig.ConnectionString)) {
                            con.Open();

                            using(var cmd = con.CreateCommand()) {
                                cmd.CommandText = "SELECT " +
                                    "COUNT(ph_exbunseki_" + i + "), " +
                                    "COUNT(bod_exbunseki_" + i + "), " +
                                    "COUNT(cbod_exbunseki_" + i + "), " +
                                    "COUNT(cod_exbunseki_" + i + "), " +
                                    "COUNT(ss_exbunseki_" + i + "), " +
                                    "COUNT(nd_exbunseki_" + i + "), " +
                                    "COUNT(nh4n_exbunseki_" + i + "), " +
                                    "COUNT(no3n_exbunseki_" + i + "), " +
                                    "COUNT(no2n_exbunseki_" + i + "), " +
                                    "COUNT(tn_exbunseki_" + i + "), " +
                                    "COUNT(tp_exbunseki_" + i + "), " +
                                    "COUNT(phenol_exbunseki_" + i + "), " +
                                    "COUNT(cu_exbunseki_" + i + "), " +
                                    "COUNT(zn_exbunseki_" + i + "), " +
                                    "COUNT(ssfe_exbunseki_" + i + "), " +
                                    "COUNT(ssmn_exbunseki_" + i + "), " +
                                    "COUNT(cr_exbunseki_" + i + "), " +
                                    "COUNT(cd_exbunseki_" + i + "), " +
                                    "COUNT(cyan_exbunseki_" + i + "), " +
                                    "COUNT(organicp_exbunseki_" + i + "), " +
                                    "COUNT(pb_exbunseki_" + i + "), " +
                                    "COUNT(hcr_exbunseki_" + i + "), " +
                                    "COUNT(as_exbunseki_" + i + "), " +
                                    "COUNT(hg_exbunseki_" + i + "), " +
                                    "COUNT(alkylhg_exbunseki_" + i + "), " +
                                    "COUNT(pcb_exbunseki_" + i + "), " +
                                    "COUNT(trice_exbunseki_" + i + "), " +
                                    "COUNT(tetce_exbunseki_" + i + "), " +
                                    "COUNT(dcm_exbunseki_" + i + "), " +
                                    "COUNT(ccl4_exbunseki_" + i + "), " +
                                    "COUNT(x12dce_exbunseki_" + i + "), " +
                                    "COUNT(x11dce_exbunseki_" + i + "), " +
                                    "COUNT(cis12dce_exbunseki_" + i + "), " +
                                    "COUNT(x111tce_exbunseki_" + i + "), " +
                                    "COUNT(x112tce_exbunseki_" + i + "), " +
                                    "COUNT(x13dcp_exbunseki_" + i + "), " +
                                    "COUNT(thiuram_exbunseki_" + i + "), " +
                                    "COUNT(simazine_exbunseki_" + i + "), " +
                                    "COUNT(thiobencarb_exbunseki_" + i + "), " +
                                    "COUNT(benzene_exbunseki_" + i + "), " +
                                    "COUNT(se_exbunseki_" + i + "), " +
                                    "COUNT(b_exbunseki_" + i + "), " +
                                    "COUNT(f_exbunseki_" + i + "), " +
                                    "COUNT(x14dox_exbunseki_" + i + ") " +
                                    "FROM suishitsu_kanri " +
                                    "WHERE del_flg IS NULL AND kanrisya='" + kanrisya + "' AND syorijou='" + syorijou + "' AND sokutei_dt>='" + stBYSTR + "' AND sokutei_dt<'" + edBYSTR + "'";

                                using(var reader = cmd.ExecuteReader()) {
                                    if(reader.Read()) {
                                        worksheet1.Cell(18 + (i - 1) * 48, 7).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 0));
                                        worksheet1.Cell(19 + (i - 1) * 48, 7).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 1));
                                        worksheet1.Cell(20 + (i - 1) * 48, 7).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 2));
                                        worksheet1.Cell(21 + (i - 1) * 48, 7).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 3));
                                        worksheet1.Cell(22 + (i - 1) * 48, 7).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 4));
                                        worksheet1.Cell(23 + (i - 1) * 48, 7).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 5));
                                        worksheet1.Cell(24 + (i - 1) * 48, 7).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 6));
                                        worksheet1.Cell(25 + (i - 1) * 48, 7).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 7));
                                        worksheet1.Cell(26 + (i - 1) * 48, 7).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 8));
                                        worksheet1.Cell(27 + (i - 1) * 48, 7).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 9));
                                        worksheet1.Cell(28 + (i - 1) * 48, 7).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 10));
                                        worksheet1.Cell(29 + (i - 1) * 48, 7).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 11));
                                        worksheet1.Cell(30 + (i - 1) * 48, 7).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 12));
                                        worksheet1.Cell(31 + (i - 1) * 48, 7).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 13));
                                        worksheet1.Cell(32 + (i - 1) * 48, 7).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 14));
                                        worksheet1.Cell(33 + (i - 1) * 48, 7).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 15));
                                        worksheet1.Cell(34 + (i - 1) * 48, 7).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 16));
                                        worksheet1.Cell(35 + (i - 1) * 48, 7).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 17));
                                        worksheet1.Cell(36 + (i - 1) * 48, 7).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 18));
                                        worksheet1.Cell(37 + (i - 1) * 48, 7).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 19));
                                        worksheet1.Cell(38 + (i - 1) * 48, 7).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 20));
                                        worksheet1.Cell(39 + (i - 1) * 48, 7).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 21));
                                        worksheet1.Cell(40 + (i - 1) * 48, 7).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 22));
                                        worksheet1.Cell(41 + (i - 1) * 48, 7).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 23));
                                        worksheet1.Cell(42 + (i - 1) * 48, 7).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 24));
                                        worksheet1.Cell(43 + (i - 1) * 48, 7).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 25));
                                        worksheet1.Cell(44 + (i - 1) * 48, 7).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 26));
                                        worksheet1.Cell(45 + (i - 1) * 48, 7).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 27));
                                        worksheet1.Cell(46 + (i - 1) * 48, 7).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 28));
                                        worksheet1.Cell(47 + (i - 1) * 48, 7).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 29));
                                        worksheet1.Cell(48 + (i - 1) * 48, 7).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 30));
                                        worksheet1.Cell(49 + (i - 1) * 48, 7).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 31));
                                        worksheet1.Cell(50 + (i - 1) * 48, 7).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 32));
                                        worksheet1.Cell(51 + (i - 1) * 48, 7).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 33));
                                        worksheet1.Cell(52 + (i - 1) * 48, 7).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 34));
                                        worksheet1.Cell(53 + (i - 1) * 48, 7).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 35));
                                        worksheet1.Cell(54 + (i - 1) * 48, 7).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 36));
                                        worksheet1.Cell(55 + (i - 1) * 48, 7).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 37));
                                        worksheet1.Cell(56 + (i - 1) * 48, 7).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 38));
                                        worksheet1.Cell(57 + (i - 1) * 48, 7).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 39));
                                        worksheet1.Cell(58 + (i - 1) * 48, 7).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 40));
                                        worksheet1.Cell(59 + (i - 1) * 48, 7).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 41));
                                        worksheet1.Cell(60 + (i - 1) * 48, 7).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 42));
                                        worksheet1.Cell(61 + (i - 1) * 48, 7).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 43));
                                    }
                                }
                            }
                        }

                        //四半期集計(測定回数)
                        using(var con = new SQLiteConnection(DataBaseConfig.ConnectionString)) {
                            con.Open();

                            using(var cmd = con.CreateCommand()) {
                                cmd.CommandText = "SELECT " +
                                    "COUNT(ph_exbunseki_" + i + "), " +
                                    "COUNT(bod_exbunseki_" + i + "), " +
                                    "COUNT(cbod_exbunseki_" + i + "), " +
                                    "COUNT(cod_exbunseki_" + i + "), " +
                                    "COUNT(ss_exbunseki_" + i + "), " +
                                    "COUNT(nd_exbunseki_" + i + "), " +
                                    "COUNT(nh4n_exbunseki_" + i + "), " +
                                    "COUNT(no3n_exbunseki_" + i + "), " +
                                    "COUNT(no2n_exbunseki_" + i + "), " +
                                    "COUNT(tn_exbunseki_" + i + "), " +
                                    "COUNT(tp_exbunseki_" + i + "), " +
                                    "COUNT(phenol_exbunseki_" + i + "), " +
                                    "COUNT(cu_exbunseki_" + i + "), " +
                                    "COUNT(zn_exbunseki_" + i + "), " +
                                    "COUNT(ssfe_exbunseki_" + i + "), " +
                                    "COUNT(ssmn_exbunseki_" + i + "), " +
                                    "COUNT(cr_exbunseki_" + i + "), " +
                                    "COUNT(cd_exbunseki_" + i + "), " +
                                    "COUNT(cyan_exbunseki_" + i + "), " +
                                    "COUNT(organicp_exbunseki_" + i + "), " +
                                    "COUNT(pb_exbunseki_" + i + "), " +
                                    "COUNT(hcr_exbunseki_" + i + "), " +
                                    "COUNT(as_exbunseki_" + i + "), " +
                                    "COUNT(hg_exbunseki_" + i + "), " +
                                    "COUNT(alkylhg_exbunseki_" + i + "), " +
                                    "COUNT(pcb_exbunseki_" + i + "), " +
                                    "COUNT(trice_exbunseki_" + i + "), " +
                                    "COUNT(tetce_exbunseki_" + i + "), " +
                                    "COUNT(dcm_exbunseki_" + i + "), " +
                                    "COUNT(ccl4_exbunseki_" + i + "), " +
                                    "COUNT(x12dce_exbunseki_" + i + "), " +
                                    "COUNT(x11dce_exbunseki_" + i + "), " +
                                    "COUNT(cis12dce_exbunseki_" + i + "), " +
                                    "COUNT(x111tce_exbunseki_" + i + "), " +
                                    "COUNT(x112tce_exbunseki_" + i + "), " +
                                    "COUNT(x13dcp_exbunseki_" + i + "), " +
                                    "COUNT(thiuram_exbunseki_" + i + "), " +
                                    "COUNT(simazine_exbunseki_" + i + "), " +
                                    "COUNT(thiobencarb_exbunseki_" + i + "), " +
                                    "COUNT(benzene_exbunseki_" + i + "), " +
                                    "COUNT(se_exbunseki_" + i + "), " +
                                    "COUNT(b_exbunseki_" + i + "), " +
                                    "COUNT(f_exbunseki_" + i + "), " +
                                    "COUNT(x14dox_exbunseki_" + i + ") " +
                                    "FROM suishitsu_kanri " +
                                    "WHERE del_flg IS NULL AND kanrisya='" + kanrisya + "' AND syorijou='" + syorijou + "' AND sokutei_dt>='" + stQSTR + "' AND sokutei_dt<'" + edQSTR + "'";

                                using(var reader = cmd.ExecuteReader()) {
                                    if(reader.Read()) {
                                        worksheet1.Cell(18 + (i - 1) * 48, 6).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 0));
                                        worksheet1.Cell(19 + (i - 1) * 48, 6).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 1));
                                        worksheet1.Cell(20 + (i - 1) * 48, 6).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 2));
                                        worksheet1.Cell(21 + (i - 1) * 48, 6).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 3));
                                        worksheet1.Cell(22 + (i - 1) * 48, 6).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 4));
                                        worksheet1.Cell(23 + (i - 1) * 48, 6).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 5));
                                        worksheet1.Cell(24 + (i - 1) * 48, 6).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 6));
                                        worksheet1.Cell(25 + (i - 1) * 48, 6).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 7));
                                        worksheet1.Cell(26 + (i - 1) * 48, 6).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 8));
                                        worksheet1.Cell(27 + (i - 1) * 48, 6).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 9));
                                        worksheet1.Cell(28 + (i - 1) * 48, 6).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 10));
                                        worksheet1.Cell(29 + (i - 1) * 48, 6).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 11));
                                        worksheet1.Cell(30 + (i - 1) * 48, 6).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 12));
                                        worksheet1.Cell(31 + (i - 1) * 48, 6).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 13));
                                        worksheet1.Cell(32 + (i - 1) * 48, 6).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 14));
                                        worksheet1.Cell(33 + (i - 1) * 48, 6).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 15));
                                        worksheet1.Cell(34 + (i - 1) * 48, 6).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 16));
                                        worksheet1.Cell(35 + (i - 1) * 48, 6).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 17));
                                        worksheet1.Cell(36 + (i - 1) * 48, 6).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 18));
                                        worksheet1.Cell(37 + (i - 1) * 48, 6).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 19));
                                        worksheet1.Cell(38 + (i - 1) * 48, 6).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 20));
                                        worksheet1.Cell(39 + (i - 1) * 48, 6).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 21));
                                        worksheet1.Cell(40 + (i - 1) * 48, 6).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 22));
                                        worksheet1.Cell(41 + (i - 1) * 48, 6).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 23));
                                        worksheet1.Cell(42 + (i - 1) * 48, 6).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 24));
                                        worksheet1.Cell(43 + (i - 1) * 48, 6).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 25));
                                        worksheet1.Cell(44 + (i - 1) * 48, 6).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 26));
                                        worksheet1.Cell(45 + (i - 1) * 48, 6).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 27));
                                        worksheet1.Cell(46 + (i - 1) * 48, 6).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 28));
                                        worksheet1.Cell(47 + (i - 1) * 48, 6).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 29));
                                        worksheet1.Cell(48 + (i - 1) * 48, 6).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 30));
                                        worksheet1.Cell(49 + (i - 1) * 48, 6).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 31));
                                        worksheet1.Cell(50 + (i - 1) * 48, 6).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 32));
                                        worksheet1.Cell(51 + (i - 1) * 48, 6).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 33));
                                        worksheet1.Cell(52 + (i - 1) * 48, 6).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 34));
                                        worksheet1.Cell(53 + (i - 1) * 48, 6).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 35));
                                        worksheet1.Cell(54 + (i - 1) * 48, 6).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 36));
                                        worksheet1.Cell(55 + (i - 1) * 48, 6).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 37));
                                        worksheet1.Cell(56 + (i - 1) * 48, 6).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 38));
                                        worksheet1.Cell(57 + (i - 1) * 48, 6).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 39));
                                        worksheet1.Cell(58 + (i - 1) * 48, 6).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 40));
                                        worksheet1.Cell(59 + (i - 1) * 48, 6).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 41));
                                        worksheet1.Cell(60 + (i - 1) * 48, 6).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 42));
                                        worksheet1.Cell(61 + (i - 1) * 48, 6).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 43));
                                    }
                                }
                            }
                        }

                        //四半期集計(平均)
                        using(var con = new SQLiteConnection(DataBaseConfig.ConnectionString)) {
                            con.Open();

                            using(var cmd = con.CreateCommand()) {
                                cmd.CommandText = "SELECT " +
                                    "AVG(ph_exbunseki_" + i + "), " +
                                    "AVG(bod_exbunseki_" + i + "), " +
                                    "AVG(cbod_exbunseki_" + i + "), " +
                                    "AVG(cod_exbunseki_" + i + "), " +
                                    "AVG(ss_exbunseki_" + i + "), " +
                                    "AVG(nd_exbunseki_" + i + "), " +
                                    "AVG(nh4n_exbunseki_" + i + "), " +
                                    "AVG(no3n_exbunseki_" + i + "), " +
                                    "AVG(no2n_exbunseki_" + i + "), " +
                                    "AVG(tn_exbunseki_" + i + "), " +
                                    "AVG(tp_exbunseki_" + i + "), " +
                                    "AVG(phenol_exbunseki_" + i + "), " +
                                    "AVG(cu_exbunseki_" + i + "), " +
                                    "AVG(zn_exbunseki_" + i + "), " +
                                    "AVG(ssfe_exbunseki_" + i + "), " +
                                    "AVG(ssmn_exbunseki_" + i + "), " +
                                    "AVG(cr_exbunseki_" + i + "), " +
                                    "AVG(cd_exbunseki_" + i + "), " +
                                    "AVG(cyan_exbunseki_" + i + "), " +
                                    "AVG(organicp_exbunseki_" + i + "), " +
                                    "AVG(pb_exbunseki_" + i + "), " +
                                    "AVG(hcr_exbunseki_" + i + "), " +
                                    "AVG(as_exbunseki_" + i + "), " +
                                    "AVG(hg_exbunseki_" + i + "), " +
                                    "AVG(alkylhg_exbunseki_" + i + "), " +
                                    "AVG(pcb_exbunseki_" + i + "), " +
                                    "AVG(trice_exbunseki_" + i + "), " +
                                    "AVG(tetce_exbunseki_" + i + "), " +
                                    "AVG(dcm_exbunseki_" + i + "), " +
                                    "AVG(ccl4_exbunseki_" + i + "), " +
                                    "AVG(x12dce_exbunseki_" + i + "), " +
                                    "AVG(x11dce_exbunseki_" + i + "), " +
                                    "AVG(cis12dce_exbunseki_" + i + "), " +
                                    "AVG(x111tce_exbunseki_" + i + "), " +
                                    "AVG(x112tce_exbunseki_" + i + "), " +
                                    "AVG(x13dcp_exbunseki_" + i + "), " +
                                    "AVG(thiuram_exbunseki_" + i + "), " +
                                    "AVG(simazine_exbunseki_" + i + "), " +
                                    "AVG(thiobencarb_exbunseki_" + i + "), " +
                                    "AVG(benzene_exbunseki_" + i + "), " +
                                    "AVG(se_exbunseki_" + i + "), " +
                                    "AVG(b_exbunseki_" + i + "), " +
                                    "AVG(f_exbunseki_" + i + "), " +
                                    "AVG(x14dox_exbunseki_" + i + ") " +
                                    "FROM suishitsu_kanri " +
                                    "WHERE del_flg IS NULL AND kanrisya='" + kanrisya + "' AND syorijou='" + syorijou + "' AND sokutei_dt>='" + stQSTR + "' AND sokutei_dt<'" + edQSTR + "'";

                                using(var reader = cmd.ExecuteReader()) {
                                    if(reader.Read()) {
                                        worksheet1.Cell(18 + (i - 1) * 48, 3).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 0), ColumnsConfigSet.nameToFormat("ph_exbunseki_1")));
                                        worksheet1.Cell(19 + (i - 1) * 48, 3).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 1), ColumnsConfigSet.nameToFormat("bod_exbunseki_1")));
                                        worksheet1.Cell(20 + (i - 1) * 48, 3).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 2), ColumnsConfigSet.nameToFormat("cbod_exbunseki_1")));
                                        worksheet1.Cell(21 + (i - 1) * 48, 3).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 3), ColumnsConfigSet.nameToFormat("cod_exbunseki_1")));
                                        worksheet1.Cell(22 + (i - 1) * 48, 3).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 4), ColumnsConfigSet.nameToFormat("ss_exbunseki_1")));
                                        worksheet1.Cell(23 + (i - 1) * 48, 3).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 5), ColumnsConfigSet.nameToFormat("nd_exbunseki_1")));
                                        worksheet1.Cell(24 + (i - 1) * 48, 3).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 6), ColumnsConfigSet.nameToFormat("nh4n_exbunseki_1")));
                                        worksheet1.Cell(25 + (i - 1) * 48, 3).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 7), ColumnsConfigSet.nameToFormat("no3n_exbunseki_1")));
                                        worksheet1.Cell(26 + (i - 1) * 48, 3).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 8), ColumnsConfigSet.nameToFormat("no2n_exbunseki_1")));
                                        worksheet1.Cell(27 + (i - 1) * 48, 3).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 9), ColumnsConfigSet.nameToFormat("tn_exbunseki_1")));
                                        worksheet1.Cell(28 + (i - 1) * 48, 3).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 10), ColumnsConfigSet.nameToFormat("tp_exbunseki_1")));
                                        worksheet1.Cell(29 + (i - 1) * 48, 3).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 11), ColumnsConfigSet.nameToFormat("phenol_exbunseki_1")));
                                        worksheet1.Cell(30 + (i - 1) * 48, 3).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 12), ColumnsConfigSet.nameToFormat("cu_exbunseki_1")));
                                        worksheet1.Cell(31 + (i - 1) * 48, 3).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 13), ColumnsConfigSet.nameToFormat("zn_exbunseki_1")));
                                        worksheet1.Cell(32 + (i - 1) * 48, 3).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 14), ColumnsConfigSet.nameToFormat("ssfe_exbunseki_1")));
                                        worksheet1.Cell(33 + (i - 1) * 48, 3).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 15), ColumnsConfigSet.nameToFormat("ssmn_exbunseki_1")));
                                        worksheet1.Cell(34 + (i - 1) * 48, 3).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 16), ColumnsConfigSet.nameToFormat("cr_exbunseki_1")));
                                        worksheet1.Cell(35 + (i - 1) * 48, 3).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 17), ColumnsConfigSet.nameToFormat("cd_exbunseki_1")));
                                        worksheet1.Cell(36 + (i - 1) * 48, 3).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 18), ColumnsConfigSet.nameToFormat("cyan_exbunseki_1")));
                                        worksheet1.Cell(37 + (i - 1) * 48, 3).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 19), ColumnsConfigSet.nameToFormat("organicp_exbunseki_1")));
                                        worksheet1.Cell(38 + (i - 1) * 48, 3).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 20), ColumnsConfigSet.nameToFormat("pb_exbunseki_1")));
                                        worksheet1.Cell(39 + (i - 1) * 48, 3).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 21), ColumnsConfigSet.nameToFormat("hcr_exbunseki_1")));
                                        worksheet1.Cell(40 + (i - 1) * 48, 3).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 22), ColumnsConfigSet.nameToFormat("as_exbunseki_1")));
                                        worksheet1.Cell(41 + (i - 1) * 48, 3).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 23), ColumnsConfigSet.nameToFormat("hg_exbunseki_1")));
                                        worksheet1.Cell(42 + (i - 1) * 48, 3).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 24), ColumnsConfigSet.nameToFormat("alkylhg_exbunseki_1")));
                                        worksheet1.Cell(43 + (i - 1) * 48, 3).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 25), ColumnsConfigSet.nameToFormat("pcb_exbunseki_1")));
                                        worksheet1.Cell(44 + (i - 1) * 48, 3).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 26), ColumnsConfigSet.nameToFormat("trice_exbunseki_1")));
                                        worksheet1.Cell(45 + (i - 1) * 48, 3).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 27), ColumnsConfigSet.nameToFormat("tetce_exbunseki_1")));
                                        worksheet1.Cell(46 + (i - 1) * 48, 3).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 28), ColumnsConfigSet.nameToFormat("dcm_exbunseki_1")));
                                        worksheet1.Cell(47 + (i - 1) * 48, 3).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 29), ColumnsConfigSet.nameToFormat("ccl4_exbunseki_1")));
                                        worksheet1.Cell(48 + (i - 1) * 48, 3).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 30), ColumnsConfigSet.nameToFormat("x12dce_exbunseki_1")));
                                        worksheet1.Cell(49 + (i - 1) * 48, 3).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 31), ColumnsConfigSet.nameToFormat("x11dce_exbunseki_1")));
                                        worksheet1.Cell(50 + (i - 1) * 48, 3).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 32), ColumnsConfigSet.nameToFormat("cis12dce_exbunseki_1")));
                                        worksheet1.Cell(51 + (i - 1) * 48, 3).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 33), ColumnsConfigSet.nameToFormat("x111tce_exbunseki_1")));
                                        worksheet1.Cell(52 + (i - 1) * 48, 3).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 34), ColumnsConfigSet.nameToFormat("x112tce_exbunseki_1")));
                                        worksheet1.Cell(53 + (i - 1) * 48, 3).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 35), ColumnsConfigSet.nameToFormat("x13dcp_exbunseki_1")));
                                        worksheet1.Cell(54 + (i - 1) * 48, 3).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 36), ColumnsConfigSet.nameToFormat("thiuram_exbunseki_1")));
                                        worksheet1.Cell(55 + (i - 1) * 48, 3).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 37), ColumnsConfigSet.nameToFormat("simazine_exbunseki_1")));
                                        worksheet1.Cell(56 + (i - 1) * 48, 3).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 38), ColumnsConfigSet.nameToFormat("thiobencarb_exbunseki_1")));
                                        worksheet1.Cell(57 + (i - 1) * 48, 3).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 39), ColumnsConfigSet.nameToFormat("benzene_exbunseki_1")));
                                        worksheet1.Cell(58 + (i - 1) * 48, 3).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 40), ColumnsConfigSet.nameToFormat("se_exbunseki_1")));
                                        worksheet1.Cell(59 + (i - 1) * 48, 3).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 41), ColumnsConfigSet.nameToFormat("b_exbunseki_1")));
                                        worksheet1.Cell(60 + (i - 1) * 48, 3).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 42), ColumnsConfigSet.nameToFormat("f_exbunseki_1")));
                                        worksheet1.Cell(61 + (i - 1) * 48, 3).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 43), ColumnsConfigSet.nameToFormat("x14dox_exbunseki_1")));
                                    }
                                }
                            }
                        }

                        //四半期集計(最大)
                        using(var con = new SQLiteConnection(DataBaseConfig.ConnectionString)) {
                            con.Open();

                            using(var cmd = con.CreateCommand()) {
                                cmd.CommandText = "SELECT " +
                                    "MAX(ph_exbunseki_" + i + "), " +
                                    "MAX(bod_exbunseki_" + i + "), " +
                                    "MAX(cbod_exbunseki_" + i + "), " +
                                    "MAX(cod_exbunseki_" + i + "), " +
                                    "MAX(ss_exbunseki_" + i + "), " +
                                    "MAX(nd_exbunseki_" + i + "), " +
                                    "MAX(nh4n_exbunseki_" + i + "), " +
                                    "MAX(no3n_exbunseki_" + i + "), " +
                                    "MAX(no2n_exbunseki_" + i + "), " +
                                    "MAX(tn_exbunseki_" + i + "), " +
                                    "MAX(tp_exbunseki_" + i + "), " +
                                    "MAX(phenol_exbunseki_" + i + "), " +
                                    "MAX(cu_exbunseki_" + i + "), " +
                                    "MAX(zn_exbunseki_" + i + "), " +
                                    "MAX(ssfe_exbunseki_" + i + "), " +
                                    "MAX(ssmn_exbunseki_" + i + "), " +
                                    "MAX(cr_exbunseki_" + i + "), " +
                                    "MAX(cd_exbunseki_" + i + "), " +
                                    "MAX(cyan_exbunseki_" + i + "), " +
                                    "MAX(organicp_exbunseki_" + i + "), " +
                                    "MAX(pb_exbunseki_" + i + "), " +
                                    "MAX(hcr_exbunseki_" + i + "), " +
                                    "MAX(as_exbunseki_" + i + "), " +
                                    "MAX(hg_exbunseki_" + i + "), " +
                                    "MAX(alkylhg_exbunseki_" + i + "), " +
                                    "MAX(pcb_exbunseki_" + i + "), " +
                                    "MAX(trice_exbunseki_" + i + "), " +
                                    "MAX(tetce_exbunseki_" + i + "), " +
                                    "MAX(dcm_exbunseki_" + i + "), " +
                                    "MAX(ccl4_exbunseki_" + i + "), " +
                                    "MAX(x12dce_exbunseki_" + i + "), " +
                                    "MAX(x11dce_exbunseki_" + i + "), " +
                                    "MAX(cis12dce_exbunseki_" + i + "), " +
                                    "MAX(x111tce_exbunseki_" + i + "), " +
                                    "MAX(x112tce_exbunseki_" + i + "), " +
                                    "MAX(x13dcp_exbunseki_" + i + "), " +
                                    "MAX(thiuram_exbunseki_" + i + "), " +
                                    "MAX(simazine_exbunseki_" + i + "), " +
                                    "MAX(thiobencarb_exbunseki_" + i + "), " +
                                    "MAX(benzene_exbunseki_" + i + "), " +
                                    "MAX(se_exbunseki_" + i + "), " +
                                    "MAX(b_exbunseki_" + i + "), " +
                                    "MAX(f_exbunseki_" + i + "), " +
                                    "MAX(x14dox_exbunseki_" + i + ") " +
                                    "FROM suishitsu_kanri " +
                                    "WHERE del_flg IS NULL AND kanrisya='" + kanrisya + "' AND syorijou='" + syorijou + "' AND sokutei_dt>='" + stQSTR + "' AND sokutei_dt<'" + edQSTR + "'";

                                using(var reader = cmd.ExecuteReader()) {
                                    if(reader.Read()) {
                                        worksheet1.Cell(18 + (i - 1) * 48, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 0), ColumnsConfigSet.nameToFormat("ph_exbunseki_1")));
                                        worksheet1.Cell(19 + (i - 1) * 48, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 1), ColumnsConfigSet.nameToFormat("bod_exbunseki_1")));
                                        worksheet1.Cell(20 + (i - 1) * 48, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 2), ColumnsConfigSet.nameToFormat("cbod_exbunseki_1")));
                                        worksheet1.Cell(21 + (i - 1) * 48, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 3), ColumnsConfigSet.nameToFormat("cod_exbunseki_1")));
                                        worksheet1.Cell(22 + (i - 1) * 48, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 4), ColumnsConfigSet.nameToFormat("ss_exbunseki_1")));
                                        worksheet1.Cell(23 + (i - 1) * 48, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 5), ColumnsConfigSet.nameToFormat("nd_exbunseki_1")));
                                        worksheet1.Cell(24 + (i - 1) * 48, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 6), ColumnsConfigSet.nameToFormat("nh4n_exbunseki_1")));
                                        worksheet1.Cell(25 + (i - 1) * 48, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 7), ColumnsConfigSet.nameToFormat("no3n_exbunseki_1")));
                                        worksheet1.Cell(26 + (i - 1) * 48, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 8), ColumnsConfigSet.nameToFormat("no2n_exbunseki_1")));
                                        worksheet1.Cell(27 + (i - 1) * 48, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 9), ColumnsConfigSet.nameToFormat("tn_exbunseki_1")));
                                        worksheet1.Cell(28 + (i - 1) * 48, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 10), ColumnsConfigSet.nameToFormat("tp_exbunseki_1")));
                                        worksheet1.Cell(29 + (i - 1) * 48, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 11), ColumnsConfigSet.nameToFormat("phenol_exbunseki_1")));
                                        worksheet1.Cell(30 + (i - 1) * 48, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 12), ColumnsConfigSet.nameToFormat("cu_exbunseki_1")));
                                        worksheet1.Cell(31 + (i - 1) * 48, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 13), ColumnsConfigSet.nameToFormat("zn_exbunseki_1")));
                                        worksheet1.Cell(32 + (i - 1) * 48, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 14), ColumnsConfigSet.nameToFormat("ssfe_exbunseki_1")));
                                        worksheet1.Cell(33 + (i - 1) * 48, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 15), ColumnsConfigSet.nameToFormat("ssmn_exbunseki_1")));
                                        worksheet1.Cell(34 + (i - 1) * 48, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 16), ColumnsConfigSet.nameToFormat("cr_exbunseki_1")));
                                        worksheet1.Cell(35 + (i - 1) * 48, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 17), ColumnsConfigSet.nameToFormat("cd_exbunseki_1")));
                                        worksheet1.Cell(36 + (i - 1) * 48, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 18), ColumnsConfigSet.nameToFormat("cyan_exbunseki_1")));
                                        worksheet1.Cell(37 + (i - 1) * 48, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 19), ColumnsConfigSet.nameToFormat("organicp_exbunseki_1")));
                                        worksheet1.Cell(38 + (i - 1) * 48, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 20), ColumnsConfigSet.nameToFormat("pb_exbunseki_1")));
                                        worksheet1.Cell(39 + (i - 1) * 48, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 21), ColumnsConfigSet.nameToFormat("hcr_exbunseki_1")));
                                        worksheet1.Cell(40 + (i - 1) * 48, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 22), ColumnsConfigSet.nameToFormat("as_exbunseki_1")));
                                        worksheet1.Cell(41 + (i - 1) * 48, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 23), ColumnsConfigSet.nameToFormat("hg_exbunseki_1")));
                                        worksheet1.Cell(42 + (i - 1) * 48, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 24), ColumnsConfigSet.nameToFormat("alkylhg_exbunseki_1")));
                                        worksheet1.Cell(43 + (i - 1) * 48, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 25), ColumnsConfigSet.nameToFormat("pcb_exbunseki_1")));
                                        worksheet1.Cell(44 + (i - 1) * 48, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 26), ColumnsConfigSet.nameToFormat("trice_exbunseki_1")));
                                        worksheet1.Cell(45 + (i - 1) * 48, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 27), ColumnsConfigSet.nameToFormat("tetce_exbunseki_1")));
                                        worksheet1.Cell(46 + (i - 1) * 48, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 28), ColumnsConfigSet.nameToFormat("dcm_exbunseki_1")));
                                        worksheet1.Cell(47 + (i - 1) * 48, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 29), ColumnsConfigSet.nameToFormat("ccl4_exbunseki_1")));
                                        worksheet1.Cell(48 + (i - 1) * 48, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 30), ColumnsConfigSet.nameToFormat("x12dce_exbunseki_1")));
                                        worksheet1.Cell(49 + (i - 1) * 48, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 31), ColumnsConfigSet.nameToFormat("x11dce_exbunseki_1")));
                                        worksheet1.Cell(50 + (i - 1) * 48, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 32), ColumnsConfigSet.nameToFormat("cis12dce_exbunseki_1")));
                                        worksheet1.Cell(51 + (i - 1) * 48, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 33), ColumnsConfigSet.nameToFormat("x111tce_exbunseki_1")));
                                        worksheet1.Cell(52 + (i - 1) * 48, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 34), ColumnsConfigSet.nameToFormat("x112tce_exbunseki_1")));
                                        worksheet1.Cell(53 + (i - 1) * 48, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 35), ColumnsConfigSet.nameToFormat("x13dcp_exbunseki_1")));
                                        worksheet1.Cell(54 + (i - 1) * 48, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 36), ColumnsConfigSet.nameToFormat("thiuram_exbunseki_1")));
                                        worksheet1.Cell(55 + (i - 1) * 48, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 37), ColumnsConfigSet.nameToFormat("simazine_exbunseki_1")));
                                        worksheet1.Cell(56 + (i - 1) * 48, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 38), ColumnsConfigSet.nameToFormat("thiobencarb_exbunseki_1")));
                                        worksheet1.Cell(57 + (i - 1) * 48, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 39), ColumnsConfigSet.nameToFormat("benzene_exbunseki_1")));
                                        worksheet1.Cell(58 + (i - 1) * 48, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 40), ColumnsConfigSet.nameToFormat("se_exbunseki_1")));
                                        worksheet1.Cell(59 + (i - 1) * 48, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 41), ColumnsConfigSet.nameToFormat("b_exbunseki_1")));
                                        worksheet1.Cell(60 + (i - 1) * 48, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 42), ColumnsConfigSet.nameToFormat("f_exbunseki_1")));
                                        worksheet1.Cell(61 + (i - 1) * 48, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 43), ColumnsConfigSet.nameToFormat("x14dox_exbunseki_1")));
                                    }
                                }
                            }
                        }

                        //四半期集計(最大)
                        using(var con = new SQLiteConnection(DataBaseConfig.ConnectionString)) {
                            con.Open();

                            using(var cmd = con.CreateCommand()) {
                                cmd.CommandText = "SELECT " +
                                    "MIN(ph_exbunseki_" + i + ") " +
                                    "FROM suishitsu_kanri " +
                                    "WHERE del_flg IS NULL AND kanrisya='" + kanrisya + "' AND syorijou='" + syorijou + "' AND sokutei_dt>='" + stQSTR + "' AND sokutei_dt<'" + edQSTR + "'";

                                using(var reader = cmd.ExecuteReader()) {
                                    if(reader.Read()) {
                                        worksheet1.Cell(18 + (i - 1) * 48, 5).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 0), ColumnsConfigSet.nameToFormat("ph_exbunseki_1")));
                                    }
                                }
                            }
                        }
                    }
                }
                else {
                    throw new Exception();
                }

                return;
            }
        }

    }
}
