// 下水データ管理システム
// 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.Collections.Generic;
using System.ComponentModel;
using System.Data.SQLite;
using System.IO;
using System.Windows.Forms;

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

        int cBYear = 0;

        BindingList<KanrisyaSyorijou> KSCBItem = new BindingList<KanrisyaSyorijou>();
        BindingList<SelectableBYear> SMCBItem = new BindingList<SelectableBYear>();
        List<ColumnConfig> ccSet = ColumnsConfigSet.getSet;




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

            dataSelectConstruction();
            yearSelectConstruction();
        }




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




        //年度選択ボックス事前準備
        private void yearSelectInitialize() {
            comboBox2.DisplayMember = "selectionName";
            comboBox2.ValueMember = "primaryKey";
            comboBox2.DataSource = SMCBItem;
            comboBox2.SelectedIndexChanged += new EventHandler(yearSelect_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 yearSelectConstruction(int cBYear = -1) {
            comboBox2.SelectedIndexChanged -= yearSelect_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 stBYear = minDt.Year;
                                    int edBYear = maxDt.Year;

                                    if(minDt.Month < 4) {
                                        stBYear--;
                                    }

                                    if(maxDt.Month < 4) {
                                        edBYear--;
                                    }

                                    DateTime stDt = DateTime.Parse(stBYear.ToString() + "/4/1 00:00:00");
                                    DateTime edDt = DateTime.Parse(edBYear.ToString() + "/4/1 00:00:00");

                                    int i = 0;
                                    int c = 0;
                                    for(DateTime cDt = stDt; cDt <= edDt; cDt = cDt.AddYears(1)) {
                                        DateTime cnDt = cDt.AddYears(1);

                                        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) {
                                                        //選択中の管理者処理場のデータがある年度
                                                        SMCBItem.Add(new SelectableBYear(cDt.Year));
                                                    }
                                                    else {
                                                        //選択中の管理者処理場のデータがない場合は空欄
                                                        SMCBItem.Add(new SelectableBYear(-1));
                                                    }
                                                }
                                            }
                                        }

                                        if(cBYear == -1) {
                                            //指定がない場合は前年度を選択中に
                                            int lastByear = DateTime.Now.Year - 1;

                                            if(DateTime.Now.Month < 4) {
                                                lastByear--;
                                            }

                                            if(cDt.Year == lastByear) {
                                                c = i;
                                            }
                                        }
                                        else {
                                            if(cDt.Year == cBYear) {
                                                c = i;
                                            }
                                        }

                                        i++;
                                    }

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

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




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




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

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

            okButtonSwitch();
        }




        //管理者・処理場、年度が無効な場合、OKボタンを無効に
        private void okButtonSwitch() {
            if(comboBox1.Enabled && comboBox2.Enabled) {
                SelectableBYear item2 = (SelectableBYear)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) {
            AnnualReportExportExcel();
        }




        //年報出力
        private void AnnualReportExportExcel() {
            SaveFileDialog saveFileDialog1 = new SaveFileDialog();

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

            SelectableBYear item2 = (SelectableBYear)comboBox2.SelectedItem;
            int bYear = item2.primaryKey(0);

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

            //ファイル名
            string kikan;

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

            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\annual_report.xlsx")) {
                        progressSplash1.ProgressMessage = "流入水量シート作成中";
                        Application.DoEvents();
                        makeSheet1(workbook1);

                        progressSplash1.ProgressMessage = "使用電力量シート作成中";
                        Application.DoEvents();
                        makeSheet2(workbook1);

                        progressSplash1.ProgressMessage = "薬品等使用量1シート作成中";
                        Application.DoEvents();
                        makeSheet3(workbook1);

                        progressSplash1.ProgressMessage = "薬品等使用量2シート作成中";
                        Application.DoEvents();
                        makeSheet4(workbook1);

                        progressSplash1.ProgressMessage = "水質中試験1シート作成中";
                        Application.DoEvents();
                        makeSheet5(workbook1);

                        progressSplash1.ProgressMessage = "水質中試験2シート作成中";
                        Application.DoEvents();
                        makeSheet6(workbook1);

                        progressSplash1.ProgressMessage = "水質中試験3シート作成中";
                        Application.DoEvents();
                        makeSheet7(workbook1);

                        progressSplash1.ProgressMessage = "放流水1シート作成中";
                        Application.DoEvents();
                        makeSheet8_1(workbook1);

                        progressSplash1.ProgressMessage = "放流水2シート作成中";
                        Application.DoEvents();
                        makeSheet8_2(workbook1);

                        progressSplash1.ProgressMessage = "処理工程毎の除去率シート作成中";
                        Application.DoEvents();
                        makeSheet9(workbook1);

                        progressSplash1.ProgressMessage = "反応タンク試験シート作成中";
                        Application.DoEvents();
                        makeSheet10(workbook1);

                        progressSplash1.ProgressMessage = "脱水汚泥等搬出量シート作成中";
                        Application.DoEvents();
                        makeSheet11(workbook1);

                        progressSplash1.ProgressMessage = "嫌気性消化槽運転状況シート作成中";
                        Application.DoEvents();
                        makeSheet12(workbook1);

                        progressSplash1.ProgressMessage = "沈砂・しさ搬出量シート作成中";
                        Application.DoEvents();
                        makeSheet13(workbook1);

                        progressSplash1.ProgressMessage = "主要機器運転時間1シート作成中";
                        Application.DoEvents();
                        makeSheet14(workbook1);

                        progressSplash1.ProgressMessage = "主要機器運転時間2シート作成中";
                        Application.DoEvents();
                        makeSheet15(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(2, 17).SetValue(bYear);
                    worksheet1.Cell(2, 14).SetValue(kanrisya + "　" + syorijou);

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

                        using(var cmd = con.CreateCommand()) {
                            cmd.CommandText = "SELECT " +
                                "SUM(CASE WHEN seiten='○' THEN wv_ryuunyuu ELSE NULL END) " +
                                "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(7, 26).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 0), ColumnsConfigSet.nameToFormat("wv_ryuunyuu")));
                                }
                            }
                        }
                    }

                    //月毎集計
                    using(var con = new SQLiteConnection(DataBaseConfig.ConnectionString)) {
                        con.Open();

                        using(var cmd = con.CreateCommand()) {
                            cmd.CommandText = "SELECT " +
                                "STRFTIME('%Y%m', sokutei_dt), " +
                                "SUM(wv_ryuunyuu), AVG(wv_ryuunyuu), MAX(wv_ryuunyuu), MIN(wv_ryuunyuu), " +
                                "SUM(uryou), " +
                                "SUM(CASE WHEN seiten='○' THEN 1 ELSE NULL END), " +
                                "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) " +
                                "FROM suishitsu_kanri " +
                                "WHERE del_flg IS NULL AND kanrisya='" + kanrisya + "' AND syorijou='" + syorijou + "' AND sokutei_dt>='" + stBYSTR + "' AND sokutei_dt<'" + edBYSTR + "' " +
                                "GROUP BY STRFTIME('%Y%m', sokutei_dt) " +
                                "ORDER BY STRFTIME('%Y%m', sokutei_dt)";

                            using(var reader = cmd.ExecuteReader()) {
                                DateTime cM = DateTime.Parse(stBYSTR).AddMonths(-1);
                                int d = 0;

                                while(reader.Read()) {
                                    while(reader.GetString(0) != cM.ToString("yyyyMM")) {
                                        cM = cM.AddMonths(1);
                                        d++;
                                    }

                                    int R = d + 6;

                                    worksheet1.Cell(R, 16).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 1), ColumnsConfigSet.nameToFormat("wv_ryuunyuu")));
                                    worksheet1.Cell(R, 17).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 2), ColumnsConfigSet.nameToFormat("wv_ryuunyuu")));
                                    worksheet1.Cell(R, 18).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 3), ColumnsConfigSet.nameToFormat("wv_ryuunyuu")));
                                    worksheet1.Cell(R, 19).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 4), ColumnsConfigSet.nameToFormat("wv_ryuunyuu")));
                                    worksheet1.Cell(R, 20).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 5), ColumnsConfigSet.nameToFormat("uryou")));
                                    worksheet1.Cell(R, 21).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 6));
                                    worksheet1.Cell(R, 22).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 7), ColumnsConfigSet.nameToFormat("wv_ryuunyuu")));
                                    worksheet1.Cell(R, 23).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 8), ColumnsConfigSet.nameToFormat("wv_ryuunyuu")));
                                    worksheet1.Cell(R, 24).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 9), ColumnsConfigSet.nameToFormat("wv_ryuunyuu")));
                                }
                            }
                        }
                    }

                    worksheet1.SheetView.View = XLSheetViewOptions.PageBreakPreview;
                }
                else {
                    throw new Exception();
                }

                return;
            }




            //使用電力量シートの記入
            void makeSheet2(XLWorkbook workbook1) {
                IXLWorksheet worksheet1;

                if(workbook1.TryGetWorksheet("使用電力量", out worksheet1)) {
                    //年度、管理者・処理場
                    worksheet1.Cell(2, 26).SetValue(bYear);
                    worksheet1.Cell(2, 23).SetValue(kanrisya + "　" + syorijou);

                    //名称は最新値を採用
                    using(var con = new SQLiteConnection(DataBaseConfig.ConnectionString)) {
                        con.Open();

                        for(int i = 1; i <= 16; i++) {
                            using(var cmd = con.CreateCommand()) {
                                cmd.CommandText = "SELECT " +
                                    "name_pumpjou_" + i + " " +
                                    "FROM suishitsu_kanri " +
                                    "WHERE del_flg IS NULL AND kanrisya='" + kanrisya + "' AND syorijou='" + syorijou + "' AND sokutei_dt>='" + stBYSTR + "' AND sokutei_dt<'" + edBYSTR + "'" +
                                    "AND name_pumpjou_" + i + " IS NOT NULL AND name_pumpjou_" + i + "<>''" +
                                    "ORDER BY sokutei_dt DESC LIMIT 1";

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

                                        worksheet1.Cell(4, 25 + i).SetValue(name);
                                    }
                                    else {
                                        worksheet1.Range(4, 2 + i, 5, 2 + i).SetValue("");
                                        worksheet1.Range(4, 25 + i, 4, 25 + i).SetValue("");
                                    }
                                }
                            }
                        }
                    }

                    //月毎集計
                    using(var con = new SQLiteConnection(DataBaseConfig.ConnectionString)) {
                        con.Open();

                        using(var cmd = con.CreateCommand()) {
                            cmd.CommandText = "SELECT " +
                                "STRFTIME('%Y%m', sokutei_dt), " +
                                "SUM(ee_juden)";

                            for(int i = 1; i <= 16; i++) {
                                cmd.CommandText += ", SUM(ee_pumpjou_" + i + ")";
                            }

                            cmd.CommandText += ", SUM(wv_ryuunyuu) " +
                                "FROM suishitsu_kanri " +
                                "WHERE del_flg IS NULL AND kanrisya='" + kanrisya + "' AND syorijou='" + syorijou + "' AND sokutei_dt>='" + stBYSTR + "' AND sokutei_dt<'" + edBYSTR + "' " +
                                "GROUP BY STRFTIME('%Y%m', sokutei_dt) " +
                                "ORDER BY STRFTIME('%Y%m', sokutei_dt)";

                            using(var reader = cmd.ExecuteReader()) {
                                DateTime cM = DateTime.Parse(stBYSTR).AddMonths(-1);
                                int d = 0;

                                while(reader.Read()) {
                                    while(reader.GetString(0) != cM.ToString("yyyyMM")) {
                                        cM = cM.AddMonths(1);
                                        d++;
                                    }

                                    int R = d + 6;

                                    worksheet1.Cell(R, 25).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 1), ColumnsConfigSet.nameToFormat("ee_juden")));

                                    int C = 25;
                                    int r = 1;

                                    for(int i = 1; i <= 16; i++) {
                                        worksheet1.Cell(R, C + i).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, r + i), ColumnsConfigSet.nameToFormat("ee_pumpjou_1")));
                                    }

                                    worksheet1.Cell(R, 44).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 18), ColumnsConfigSet.nameToFormat("wv_ryuunyuu")));
                                }
                            }
                        }
                    }

                    worksheet1.SheetView.View = XLSheetViewOptions.PageBreakPreview;
                }
                else {
                    throw new Exception();
                }

                return;
            }




            //薬品等使用量1シートの記入
            void makeSheet3(XLWorkbook workbook1) {
                IXLWorksheet worksheet1;

                if(workbook1.TryGetWorksheet("薬品等使用量1", out worksheet1)) {
                    //年度、管理者・処理場
                    worksheet1.Cell(2, 25).SetValue(bYear);
                    worksheet1.Cell(2, 22).SetValue(kanrisya + "　" + syorijou);

                    //名称は最新値を採用
                    using(var con = new SQLiteConnection(DataBaseConfig.ConnectionString)) {
                        con.Open();

                        for(int i = 1; i <= 16; i++) {
                            using(var cmd = con.CreateCommand()) {
                                cmd.CommandText = "SELECT " +
                                    "name_pumpjou_" + i + " " +
                                    "FROM suishitsu_kanri " +
                                    "WHERE del_flg IS NULL AND kanrisya='" + kanrisya + "' AND syorijou='" + syorijou + "' AND sokutei_dt>='" + stBYSTR + "' AND sokutei_dt<'" + edBYSTR + "'" +
                                    "AND name_pumpjou_" + i + " IS NOT NULL AND name_pumpjou_" + i + "<>''" +
                                    "ORDER BY sokutei_dt DESC LIMIT 1";

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

                                        worksheet1.Cell(24, 26 + i).SetValue(name_pumpjou);
                                    }
                                    else {
                                        worksheet1.Range(24, 4 + i, 25, 4 + i).SetValue("");
                                        worksheet1.Range(24, 26 + i, 25, 26 + i).SetValue("");
                                    }
                                }
                            }
                        }
                    }

                    //月毎集計
                    using(var con = new SQLiteConnection(DataBaseConfig.ConnectionString)) {
                        con.Open();

                        using(var cmd = con.CreateCommand()) {
                            cmd.CommandText = "SELECT " +
                                "STRFTIME('%Y%m', sokutei_dt), " +
                                "SUM(wv_jia), SUM(ug_solid_jia), SUM(ug_gyousyuuzai_nousyukuki_all), SUM(ug_kihoujozai_nousyukuki_all), ";

                            string[] excNItems = ccSet[ColumnsConfigSet.nameToIndex("type_exc1_nousyukuki_1")].Items.Split(',');

                            for(int i = 1; i <= 7; i++) {
                                for(int j = 1; j <= MaxSizeConfig.maxNousyukuki; j++) {
                                    if(j > 1) {
                                        cmd.CommandText += " + ";
                                    }

                                    cmd.CommandText += "SUM(CASE WHEN type_exc1_nousyukuki_" + j + "='" + excNItems[i] + "' THEN (CASE WHEN ug_exc1_nousyukuki_" + j + " IS NOT NULL THEN 1 ELSE 0 END) ELSE 0 END)";
                                }

                                for(int j = 1; j <= MaxSizeConfig.maxNousyukuki; j++) {
                                    cmd.CommandText += " + SUM(CASE WHEN type_exc2_nousyukuki_" + j + "='" + excNItems[i] + "' THEN (CASE WHEN ug_exc2_nousyukuki_" + j + " IS NOT NULL THEN 1 ELSE 0 END) ELSE 0 END)";
                                }

                                cmd.CommandText += ", ";

                                for(int j = 1; j <= MaxSizeConfig.maxNousyukuki; j++) {
                                    if(j > 1) {
                                        cmd.CommandText += " + ";
                                    }

                                    cmd.CommandText += "TOTAL(CASE WHEN type_exc1_nousyukuki_" + j + "='" + excNItems[i] + "' THEN ug_exc1_nousyukuki_" + j + " ELSE 0 END)";
                                }

                                for(int j = 1; j <= MaxSizeConfig.maxNousyukuki; j++) {
                                    cmd.CommandText += " + TOTAL(CASE WHEN type_exc2_nousyukuki_" + j + "='" + excNItems[i] + "' THEN ug_exc2_nousyukuki_" + j + " ELSE 0 END)";
                                }

                                cmd.CommandText += ", ";
                            }

                            cmd.CommandText += "SUM(ug_gyousyuuzai_dassuiki_bp), SUM(ug_gyousyuuzai_dassuiki_enshin), ";

                            string[] excDItems = ccSet[ColumnsConfigSet.nameToIndex("type_exc1_dassuiki_1")].Items.Split(',');

                            for(int i = 1; i <= 7; i++) {
                                for(int j = 1; j <= MaxSizeConfig.maxDassuiki; j++) {
                                    if(j > 1) {
                                        cmd.CommandText += " + ";
                                    }

                                    cmd.CommandText += "SUM(CASE WHEN type_exc1_dassuiki_" + j + "='" + excDItems[i] + "' THEN (CASE WHEN ug_exc1_dassuiki_" + j + " IS NOT NULL THEN 1 ELSE 0 END) ELSE 0 END)";
                                }

                                for(int j = 1; j <= MaxSizeConfig.maxDassuiki; j++) {
                                    cmd.CommandText += " + SUM(CASE WHEN type_exc2_dassuiki_" + j + "='" + excDItems[i] + "' THEN (CASE WHEN ug_exc2_dassuiki_" + j + " IS NOT NULL THEN 1 ELSE 0 END) ELSE 0 END)";
                                }

                                cmd.CommandText += ", ";

                                for(int j = 1; j <= MaxSizeConfig.maxDassuiki; j++) {
                                    if(j > 1) {
                                        cmd.CommandText += " + ";
                                    }

                                    cmd.CommandText += "TOTAL(CASE WHEN type_exc1_dassuiki_" + j + "='" + excDItems[i] + "' THEN ug_exc1_dassuiki_" + j + " ELSE 0 END)";
                                }

                                for(int j = 1; j <= MaxSizeConfig.maxDassuiki; j++) {
                                    cmd.CommandText += " + TOTAL(CASE WHEN type_exc2_dassuiki_" + j + "='" + excDItems[i] + "' THEN ug_exc2_dassuiki_" + j + " ELSE 0 END)";
                                }

                                cmd.CommandText += ", ";
                            }

                            cmd.CommandText += "SUM(v_hao_kanri), SUM(v_hao_jikahatsu), SUM(v_hao_syouka)";

                            for(int i = 1; i <= 16; i++) {
                                cmd.CommandText += ", SUM(v_hao_pumpjou_" + i + ")";
                            }

                            cmd.CommandText += " FROM suishitsu_kanri " +
                                "WHERE del_flg IS NULL AND kanrisya='" + kanrisya + "' AND syorijou='" + syorijou + "' AND sokutei_dt>='" + stBYSTR + "' AND sokutei_dt<'" + edBYSTR + "' " +
                                "GROUP BY STRFTIME('%Y%m', sokutei_dt) " +
                                "ORDER BY STRFTIME('%Y%m', sokutei_dt)";

                            using(var reader = cmd.ExecuteReader()) {
                                DateTime cM = DateTime.Parse(stBYSTR).AddMonths(-1);
                                int d = 0;

                                while(reader.Read()) {
                                    while(reader.GetString(0) != cM.ToString("yyyyMM")) {
                                        cM = cM.AddMonths(1);
                                        d++;
                                    }

                                    int R = d + 7;

                                    worksheet1.Cell(R, 24).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 1), ColumnsConfigSet.nameToFormat("wv_jia")));
                                    worksheet1.Cell(R, 25).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 2), ColumnsConfigSet.nameToFormat("ug_solid_jia")));
                                    worksheet1.Cell(R, 26).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 3), ColumnsConfigSet.nameToFormat("ug_gyousyuuzai_nousyukuki_all")));
                                    worksheet1.Cell(R, 27).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 4), ColumnsConfigSet.nameToFormat("ug_kihoujozai_nousyukuki_all")));

                                    int C = 27;
                                    int r = 4;

                                    for(int i = 1; i <= 7; i++) {
                                        if(reader.GetInt16(r + (i - 1) * 2 + 1) > 0) {
                                            worksheet1.Cell(R, C + i).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, r + (i - 1) * 2 + 2), ColumnsConfigSet.nameToFormat("ug_exc1_nousyukuki_1")));
                                        }
                                    }

                                    worksheet1.Cell(R, 35).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 19), ColumnsConfigSet.nameToFormat("ug_gyousyuuzai_dassuiki_bp")));
                                    worksheet1.Cell(R, 36).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 20), ColumnsConfigSet.nameToFormat("ug_gyousyuuzai_dassuiki_enshin")));

                                    C = 36;
                                    r = 20;

                                    for(int i = 1; i <= 7; i++) {
                                        if(reader.GetInt16(r + (i - 1) * 2 + 1) > 0) {
                                            worksheet1.Cell(R, C + i).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, r + (i - 1) * 2 + 2), ColumnsConfigSet.nameToFormat("ug_exc1_dassuiki_1")));
                                        }
                                    }

                                    R = d + 26;

                                    worksheet1.Cell(R, 24).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 35), ColumnsConfigSet.nameToFormat("v_hao_kanri")));
                                    worksheet1.Cell(R, 25).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 36), ColumnsConfigSet.nameToFormat("v_hao_jikahatsu")));
                                    worksheet1.Cell(R, 26).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 37), ColumnsConfigSet.nameToFormat("v_hao_syouka")));

                                    C = 26;
                                    r = 37;

                                    for(int i = 1; i <= 16; i++) {
                                        worksheet1.Cell(R, C + i).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, r + i), ColumnsConfigSet.nameToFormat("v_hao_pumpjou_1")));
                                    }
                                }
                            }
                        }
                    }

                    worksheet1.SheetView.View = XLSheetViewOptions.PageBreakPreview;
                }
                else {
                    throw new Exception();
                }

                return;
            }




            //薬品等使用量2シートの記入
            void makeSheet4(XLWorkbook workbook1) {
                IXLWorksheet worksheet1;

                if(workbook1.TryGetWorksheet("薬品等使用量2", out worksheet1)) {
                    //年度、管理者・処理場
                    worksheet1.Cell(2, 24).SetValue(bYear);
                    worksheet1.Cell(2, 21).SetValue(kanrisya + "　" + syorijou);

                    //名称は最新値を採用
                    using(var con = new SQLiteConnection(DataBaseConfig.ConnectionString)) {
                        con.Open();

                        for(int i = 1; i <= 16; i++) {
                            using(var cmd = con.CreateCommand()) {
                                cmd.CommandText = "SELECT " +
                                    "name_pumpjou_" + i + " " +
                                    "FROM suishitsu_kanri " +
                                    "WHERE del_flg IS NULL AND kanrisya='" + kanrisya + "' AND syorijou='" + syorijou + "' AND sokutei_dt>='" + stBYSTR + "' AND sokutei_dt<'" + edBYSTR + "'" +
                                    "AND name_pumpjou_" + i + " IS NOT NULL AND name_pumpjou_" + i + "<>''" +
                                    "ORDER BY sokutei_dt DESC LIMIT 1";

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

                                        worksheet1.Cell(4, 25 + i).SetValue(name_pumpjou);
                                    }
                                    else {
                                        worksheet1.Range(4, 4 + i, 5, 4 + i).SetValue("");
                                        worksheet1.Range(4, 25 + i, 5, 17 + i).SetValue("");
                                    }
                                }
                            }
                        }
                    }

                    //月毎集計
                    using(var con = new SQLiteConnection(DataBaseConfig.ConnectionString)) {
                        con.Open();

                        using(var cmd = con.CreateCommand()) {
                            cmd.CommandText = "SELECT " +
                                "STRFTIME('%Y%m', sokutei_dt), " +
                                "SUM(v_lo_kanri), SUM(v_lo_jikahatsu), SUM(v_lo_syouka)";

                            for(int i = 1; i <= 16; i++) {
                                cmd.CommandText += ", SUM(v_lo_pumpjou_" + i + ")";
                            }

                            cmd.CommandText += ", SUM(v_kerosene_syouka), SUM(v_pgas_kanri), SUM(v_pgas_syouka), SUM(v_pgas_sonota), SUM(wv_jousui), SUM(wv_sairiyou), SUM(wv_ido) " +
                                "FROM suishitsu_kanri " +
                                "WHERE del_flg IS NULL AND kanrisya='" + kanrisya + "' AND syorijou='" + syorijou + "' AND sokutei_dt>='" + stBYSTR + "' AND sokutei_dt<'" + edBYSTR + "' " +
                                "GROUP BY STRFTIME('%Y%m', sokutei_dt) " +
                                "ORDER BY STRFTIME('%Y%m', sokutei_dt)";

                            using(var reader = cmd.ExecuteReader()) {
                                DateTime cM = DateTime.Parse(stBYSTR).AddMonths(-1);
                                int d = 0;

                                while(reader.Read()) {
                                    while(reader.GetString(0) != cM.ToString("yyyyMM")) {
                                        cM = cM.AddMonths(1);
                                        d++;
                                    }

                                    int R = d + 6;

                                    worksheet1.Cell(R, 23).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 1), ColumnsConfigSet.nameToFormat("v_lo_kanri")));
                                    worksheet1.Cell(R, 24).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 2), ColumnsConfigSet.nameToFormat("v_lo_jikahatsu")));
                                    worksheet1.Cell(R, 25).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 3), ColumnsConfigSet.nameToFormat("v_lo_syouka")));

                                    int C = 25;
                                    int r = 3;

                                    for(int i = 1; i <= 16; i++) {
                                        worksheet1.Cell(R, C + i).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, r + i), ColumnsConfigSet.nameToFormat("v_lo_pumpjou_1")));
                                    }

                                    R = d + 25;

                                    worksheet1.Cell(R, 23).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 20), ColumnsConfigSet.nameToFormat("v_kerosene_syouka")));
                                    worksheet1.Cell(R, 24).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 21), ColumnsConfigSet.nameToFormat("v_pgas_kanri")));
                                    worksheet1.Cell(R, 25).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 22), ColumnsConfigSet.nameToFormat("v_pgas_syouka")));
                                    worksheet1.Cell(R, 26).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 23), ColumnsConfigSet.nameToFormat("v_pgas_sonota")));
                                    worksheet1.Cell(R, 27).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 24), ColumnsConfigSet.nameToFormat("wv_jousui")));
                                    worksheet1.Cell(R, 28).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 25), ColumnsConfigSet.nameToFormat("wv_sairiyou")));
                                    worksheet1.Cell(R, 29).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 26), ColumnsConfigSet.nameToFormat("wv_ido")));
                                }
                            }
                        }
                    }

                    worksheet1.SheetView.View = XLSheetViewOptions.PageBreakPreview;
                }
                else {
                    throw new Exception();
                }

                return;
            }




            //水質中試験1シートの記入
            void makeSheet5(XLWorkbook workbook1) {
                IXLWorksheet worksheet1;

                if(workbook1.TryGetWorksheet("水質中試験1", out worksheet1)) {
                    //年度、管理者・処理場
                    worksheet1.Cell(2, 29).SetValue(bYear);
                    worksheet1.Cell(2, 26).SetValue(kanrisya + "　" + syorijou);

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

                        using(var cmd = con.CreateCommand()) {
                            cmd.CommandText = "SELECT " +
                                "SUM(wv_ryuunyuu) " +
                                "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(8, 53).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 0), ColumnsConfigSet.nameToFormat("wv_ryuunyuu")));
                                }
                            }
                        }
                    }

                    //月毎集計
                    using(var con = new SQLiteConnection(DataBaseConfig.ConnectionString)) {
                        con.Open();

                        using(var cmd = con.CreateCommand()) {
                            cmd.CommandText = "SELECT " +
                                "STRFTIME('%Y%m', sokutei_dt), " +
                                "AVG(wv_ryuunyuu), SUM(uryou) " +
                                "FROM suishitsu_kanri " +
                                "WHERE del_flg IS NULL AND kanrisya='" + kanrisya + "' AND syorijou='" + syorijou + "' AND sokutei_dt>='" + stBYSTR + "' AND sokutei_dt<'" + edBYSTR + "' " +
                                "GROUP BY STRFTIME('%Y%m', sokutei_dt) " +
                                "ORDER BY STRFTIME('%Y%m', sokutei_dt)";

                            using(var reader = cmd.ExecuteReader()) {
                                DateTime cM = DateTime.Parse(stBYSTR).AddMonths(-1);
                                int d = 0;

                                while(reader.Read()) {
                                    while(reader.GetString(0) != cM.ToString("yyyyMM")) {
                                        cM = cM.AddMonths(1);
                                        d++;
                                    }

                                    int R = d + 7;

                                    worksheet1.Cell(R, 28).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 1), ColumnsConfigSet.nameToFormat("wv_ryuunyuu")));
                                    worksheet1.Cell(R, 29).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 2), ColumnsConfigSet.nameToFormat("uryou")));
                                }
                            }
                        }
                    }

                    //中試験抽出
                    using(var con = new SQLiteConnection(DataBaseConfig.ConnectionString)) {
                        con.Open();

                        using(var cmd = con.CreateCommand()) {
                            cmd.CommandText = "SELECT " +
                                "STRFTIME('%Y%m', sokutei_dt), " +
                                "sokutei_dt, kion, " +
                                "wt_ryuunyuu, pr_ryuunyuu, ph_ryuunyuu, cod_ryuunyuu, bod_ryuunyuu, ss_ryuunyuu, nd_ryuunyuu, " +
                                "tn_ryuunyuu, nh4n_ryuunyuu, no2n_ryuunyuu, no3n_ryuunyuu, organicn_ryuunyuu, " + 
                                "tp_ryuunyuu, " +
                                "iodineconsum_ryuunyuu, clion_ryuunyuu " +
                                "FROM suishitsu_kanri " +
                                "WHERE kanrisya='" + kanrisya + "' AND syorijou='" + syorijou + "' AND sokutei_dt IN (" +
                                "  SELECT MIN(sokutei_dt)" +
                                "  FROM suishitsu_kanri " +
                                "  WHERE del_flg IS NULL AND kanrisya='" + kanrisya + "' AND syorijou='" + syorijou + "' AND sokutei_dt>='" + stBYSTR + "' AND sokutei_dt<'" + edBYSTR + "' " +
                                "  AND tn_ryuunyuu IS NOT NULL " +
                                "  GROUP BY STRFTIME('%Y%m', sokutei_dt) " +
                                ") " +
                                "ORDER BY STRFTIME('%Y%m', sokutei_dt)";

                            using(var reader = cmd.ExecuteReader()) {
                                DateTime cM = DateTime.Parse(stBYSTR).AddMonths(-1);
                                int d = 0;

                                while(reader.Read()) {
                                    while(reader.GetString(0) != cM.ToString("yyyyMM")) {
                                        cM = cM.AddMonths(1);
                                        d++;
                                    }

                                    int R = d + 7;

                                    worksheet1.Cell(R, 30).SetValue(reader.GetDateTime(1).Day);

                                    worksheet1.Cell(R, 31).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 2), ColumnsConfigSet.nameToFormat("kion")));

                                    worksheet1.Cell(R, 32).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 3), ColumnsConfigSet.nameToFormat("wt_ryuunyuu")));
                                    worksheet1.Cell(R, 33).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 4), ColumnsConfigSet.nameToFormat("pr_ryuunyuu")));
                                    worksheet1.Cell(R, 34).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 5), ColumnsConfigSet.nameToFormat("ph_ryuunyuu")));
                                    worksheet1.Cell(R, 35).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 6), ColumnsConfigSet.nameToFormat("cod_ryuunyuu")));
                                    worksheet1.Cell(R, 36).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 7), ColumnsConfigSet.nameToFormat("bod_ryuunyuu")));
                                    worksheet1.Cell(R, 37).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 8), ColumnsConfigSet.nameToFormat("ss_ryuunyuu")));
                                    worksheet1.Cell(R, 38).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 9), ColumnsConfigSet.nameToFormat("nd_ryuunyuu")));
                                    worksheet1.Cell(R, 39).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 10), ColumnsConfigSet.nameToFormat("tn_ryuunyuu")));
                                    worksheet1.Cell(R, 40).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 11), ColumnsConfigSet.nameToFormat("nh4n_ryuunyuu")));
                                    worksheet1.Cell(R, 41).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 12), ColumnsConfigSet.nameToFormat("no2n_ryuunyuu")));
                                    worksheet1.Cell(R, 42).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 13), ColumnsConfigSet.nameToFormat("no3n_ryuunyuu")));
                                    worksheet1.Cell(R, 43).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 14), ColumnsConfigSet.nameToFormat("organicn_ryuunyuu")));
                                    worksheet1.Cell(R, 44).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 15), ColumnsConfigSet.nameToFormat("tp_ryuunyuu")));

                                    worksheet1.Cell(R, 46).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 16), ColumnsConfigSet.nameToFormat("iodineconsum_ryuunyuu")));
                                    worksheet1.Cell(R, 47).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 17), ColumnsConfigSet.nameToFormat("clion_ryuunyuu")));
                                }
                            }
                        }
                    }

                    worksheet1.SheetView.View = XLSheetViewOptions.PageBreakPreview;
                }
                else {
                    throw new Exception();
                }

                return;
            }




            //水質中試験2シートの記入
            void makeSheet6(XLWorkbook workbook1) {
                IXLWorksheet worksheet1;

                if(workbook1.TryGetWorksheet("水質中試験2", out worksheet1)) {
                    //年度、管理者・処理場
                    worksheet1.Cell(2, 29).SetValue(bYear);
                    worksheet1.Cell(2, 26).SetValue(kanrisya + "　" + syorijou);

                    //中試験抽出
                    using(var con = new SQLiteConnection(DataBaseConfig.ConnectionString)) {
                        con.Open();

                        using(var cmd = con.CreateCommand()) {
                            cmd.CommandText = "SELECT " +
                                "STRFTIME('%Y%m', sokutei_dt), " +
                                "sokutei_dt, " +
                                "wt_syo_etsuryuu, pr_syo_etsuryuu, ph_syo_etsuryuu, cod_syo_etsuryuu, bod_syo_etsuryuu, ss_syo_etsuryuu, nd_syo_etsuryuu, tn_syo_etsuryuu, nh4n_syo_etsuryuu, no2n_syo_etsuryuu, no3n_syo_etsuryuu, organicn_syo_etsuryuu, tp_syo_etsuryuu, orthop_syo_etsuryuu " +
                                "FROM suishitsu_kanri " +
                                "WHERE kanrisya='" + kanrisya + "' AND syorijou='" + syorijou + "' AND sokutei_dt IN (" +
                                "  SELECT MAX(sokutei_dt)" +
                                "  FROM suishitsu_kanri " +
                                "  WHERE del_flg IS NULL AND kanrisya='" + kanrisya + "' AND syorijou='" + syorijou + "' AND sokutei_dt>='" + stBYSTR + "' AND sokutei_dt<'" + edBYSTR + "' " +
                                "  AND tn_syo_etsuryuu IS NOT NULL " +
                                "  GROUP BY STRFTIME('%Y%m', sokutei_dt) " +
                                ") " +
                                "ORDER BY STRFTIME('%Y%m', sokutei_dt)";

                            using(var reader = cmd.ExecuteReader()) {
                                DateTime cM = DateTime.Parse(stBYSTR).AddMonths(-1);
                                int d = 0;

                                while(reader.Read()) {
                                    while(reader.GetString(0) != cM.ToString("yyyyMM")) {
                                        cM = cM.AddMonths(1);
                                        d++;
                                    }

                                    int R = d + 7;

                                    worksheet1.Cell(R, 30).SetValue(reader.GetDateTime(1).Day);

                                    worksheet1.Cell(R, 32).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 2), ColumnsConfigSet.nameToFormat("wt_syo_etsuryuu")));
                                    worksheet1.Cell(R, 33).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 3), ColumnsConfigSet.nameToFormat("pr_syo_etsuryuu")));
                                    worksheet1.Cell(R, 34).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 4), ColumnsConfigSet.nameToFormat("ph_syo_etsuryuu")));
                                    worksheet1.Cell(R, 35).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 5), ColumnsConfigSet.nameToFormat("cod_syo_etsuryuu")));
                                    worksheet1.Cell(R, 36).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 6), ColumnsConfigSet.nameToFormat("bod_syo_etsuryuu")));
                                    worksheet1.Cell(R, 37).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 7), ColumnsConfigSet.nameToFormat("ss_syo_etsuryuu")));
                                    worksheet1.Cell(R, 38).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 8), ColumnsConfigSet.nameToFormat("nd_syo_etsuryuu")));
                                    worksheet1.Cell(R, 39).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 9), ColumnsConfigSet.nameToFormat("tn_syo_etsuryuu")));
                                    worksheet1.Cell(R, 40).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 10), ColumnsConfigSet.nameToFormat("nh4n_syo_etsuryuu")));
                                    worksheet1.Cell(R, 41).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 11), ColumnsConfigSet.nameToFormat("no2n_syo_etsuryuu")));
                                    worksheet1.Cell(R, 42).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 12), ColumnsConfigSet.nameToFormat("no3n_syo_etsuryuu")));
                                    worksheet1.Cell(R, 43).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 13), ColumnsConfigSet.nameToFormat("organicn_syo_etsuryuu")));
                                    worksheet1.Cell(R, 44).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 14), ColumnsConfigSet.nameToFormat("tp_syo_etsuryuu")));
                                    worksheet1.Cell(R, 45).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 15), ColumnsConfigSet.nameToFormat("orthop_syo_etsuryuu")));
                                }
                            }
                        }
                    }

                    worksheet1.SheetView.View = XLSheetViewOptions.PageBreakPreview;
                }
                else {
                    throw new Exception();
                }

                return;
            }




            //水質中試験3シートの記入
            void makeSheet7(XLWorkbook workbook1) {
                IXLWorksheet worksheet1;

                if(workbook1.TryGetWorksheet("水質中試験3", out worksheet1)) {
                    //年度、管理者・処理場
                    worksheet1.Cell(2, 29).SetValue(bYear);
                    worksheet1.Cell(2, 26).SetValue(kanrisya + "　" + syorijou);

                    //中試験抽出
                    using(var con = new SQLiteConnection(DataBaseConfig.ConnectionString)) {
                        con.Open();

                        using(var cmd = con.CreateCommand()) {
                            cmd.CommandText = "SELECT " +
                                "STRFTIME('%Y%m', sokutei_dt), " +
                                "sokutei_dt, " +
                                "wt_syuu_etsuryuu, pr_syuu_etsuryuu, ph_syuu_etsuryuu, cod_syuu_etsuryuu, bod_syuu_etsuryuu, ss_syuu_etsuryuu, nd_syuu_etsuryuu, tn_syuu_etsuryuu, nh4n_syuu_etsuryuu, no2n_syuu_etsuryuu, no3n_syuu_etsuryuu, organicn_syuu_etsuryuu, tp_syuu_etsuryuu, orthop_syuu_etsuryuu, " +
                                "clion_syuu_etsuryuu, jouhatsuzan_syuu_etsuryuu, youkaisei_syuu_etsuryuu, kyounetsuzan_syuu_etsuryuu, kyounetsugen_syuu_etsuryuu " +
                                "FROM suishitsu_kanri " +
                                "WHERE kanrisya='" + kanrisya + "' AND syorijou='" + syorijou + "' AND sokutei_dt IN (" +
                                "  SELECT MAX(sokutei_dt)" +
                                "  FROM suishitsu_kanri " +
                                "  WHERE del_flg IS NULL AND kanrisya='" + kanrisya + "' AND syorijou='" + syorijou + "' AND sokutei_dt>='" + stBYSTR + "' AND sokutei_dt<'" + edBYSTR + "' " +
                                "  AND tn_syuu_etsuryuu IS NOT NULL " +
                                "  GROUP BY STRFTIME('%Y%m', sokutei_dt) " +
                                ") " +
                                "ORDER BY STRFTIME('%Y%m', sokutei_dt)";

                            using(var reader = cmd.ExecuteReader()) {
                                DateTime cM = DateTime.Parse(stBYSTR).AddMonths(-1);
                                int d = 0;

                                while(reader.Read()) {
                                    while(reader.GetString(0) != cM.ToString("yyyyMM")) {
                                        cM = cM.AddMonths(1);
                                        d++;
                                    }

                                    int R = d + 7;

                                    worksheet1.Cell(R, 30).SetValue(reader.GetDateTime(1).Day);

                                    worksheet1.Cell(R, 32).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 2), ColumnsConfigSet.nameToFormat("wt_syuu_etsuryuu")));
                                    worksheet1.Cell(R, 33).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 3), ColumnsConfigSet.nameToFormat("pr_syuu_etsuryuu")));
                                    worksheet1.Cell(R, 34).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 4), ColumnsConfigSet.nameToFormat("ph_syuu_etsuryuu")));
                                    worksheet1.Cell(R, 35).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 5), ColumnsConfigSet.nameToFormat("cod_syuu_etsuryuu")));
                                    worksheet1.Cell(R, 36).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 6), ColumnsConfigSet.nameToFormat("bod_syuu_etsuryuu")));
                                    worksheet1.Cell(R, 37).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 7), ColumnsConfigSet.nameToFormat("ss_syuu_etsuryuu")));
                                    worksheet1.Cell(R, 38).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 8), ColumnsConfigSet.nameToFormat("nd_syuu_etsuryuu")));
                                    worksheet1.Cell(R, 39).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 9), ColumnsConfigSet.nameToFormat("tn_syuu_etsuryuu")));
                                    worksheet1.Cell(R, 40).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 10), ColumnsConfigSet.nameToFormat("nh4n_syuu_etsuryuu")));
                                    worksheet1.Cell(R, 41).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 11), ColumnsConfigSet.nameToFormat("no2n_syuu_etsuryuu")));
                                    worksheet1.Cell(R, 42).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 12), ColumnsConfigSet.nameToFormat("no3n_syuu_etsuryuu")));
                                    worksheet1.Cell(R, 43).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 13), ColumnsConfigSet.nameToFormat("organicn_syuu_etsuryuu")));
                                    worksheet1.Cell(R, 44).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 14), ColumnsConfigSet.nameToFormat("tp_syuu_etsuryuu")));
                                    worksheet1.Cell(R, 45).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 15), ColumnsConfigSet.nameToFormat("orthop_syuu_etsuryuu")));

                                    worksheet1.Cell(R, 47).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 16), ColumnsConfigSet.nameToFormat("clion_syuu_etsuryuu")));
                                    worksheet1.Cell(R, 48).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 17), ColumnsConfigSet.nameToFormat("jouhatsuzan_syuu_etsuryuu")));
                                    worksheet1.Cell(R, 49).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 18), ColumnsConfigSet.nameToFormat("youkaisei_syuu_etsuryuu")));
                                    worksheet1.Cell(R, 50).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 19), ColumnsConfigSet.nameToFormat("kyounetsuzan_syuu_etsuryuu")));
                                    worksheet1.Cell(R, 51).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 20), ColumnsConfigSet.nameToFormat("kyounetsugen_syuu_etsuryuu")));
                                }
                            }
                        }
                    }

                    worksheet1.SheetView.View = XLSheetViewOptions.PageBreakPreview;
                }
                else {
                    throw new Exception();
                }

                return;
            }




            //放流水1シート(塩混出口)の記入
            void makeSheet8_1(XLWorkbook workbook1) {
                IXLWorksheet worksheet1;

                if(workbook1.TryGetWorksheet("放流水1", out worksheet1)) {
                    //年度、管理者・処理場
                    worksheet1.Cell(2, 16).SetValue(bYear);
                    worksheet1.Cell(2, 13).SetValue(kanrisya + "　" + syorijou);

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

                        using(var cmd = con.CreateCommand()) {
                            cmd.CommandText = "SELECT " +
                                "SUM(wv_houryuu) " +
                                "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(8, 24).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 0), ColumnsConfigSet.nameToFormat("wv_houryuu")));
                                }
                            }
                        }
                    }

                    //月毎集計
                    using(var con = new SQLiteConnection(DataBaseConfig.ConnectionString)) {
                        con.Open();

                        using(var cmd = con.CreateCommand()) {
                            cmd.CommandText = "SELECT " +
                                "STRFTIME('%Y%m', sokutei_dt), " +
                                "AVG(wv_houryuu), " +
                                "AVG(wt_syuu_etsuryuu), " +
                                "AVG(pr_enkondeguchi), " +
                                "AVG(ph_enkondeguchi), " +
                                "AVG(cod_enkondeguchi), " +
                                "AVG(bod_enkondeguchi), " +
                                "AVG(ss_enkondeguchi), " +
                                "AVG(nd_enkondeguchi) " +
                                "FROM suishitsu_kanri " +
                                "WHERE del_flg IS NULL AND kanrisya='" + kanrisya + "' AND syorijou='" + syorijou + "' AND sokutei_dt>='" + stBYSTR + "' AND sokutei_dt<'" + edBYSTR + "' " +
                                "GROUP BY STRFTIME('%Y%m', sokutei_dt) " +
                                "ORDER BY STRFTIME('%Y%m', sokutei_dt)";

                            using(var reader = cmd.ExecuteReader()) {
                                DateTime cM = DateTime.Parse(stBYSTR).AddMonths(-1);
                                int d = 0;

                                while(reader.Read()) {
                                    while(reader.GetString(0) != cM.ToString("yyyyMM")) {
                                        cM = cM.AddMonths(1);
                                        d++;
                                    }

                                    int R = d + 7;

                                    worksheet1.Cell(R, 15).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 1), ColumnsConfigSet.nameToFormat("wv_houryuu")));
                                    worksheet1.Cell(R, 16).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 2), ColumnsConfigSet.nameToFormat("wt_syuu_etsuryuu")));
                                    worksheet1.Cell(R, 17).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 3), ColumnsConfigSet.nameToFormat("pr_enkondeguchi")));
                                    worksheet1.Cell(R, 18).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 4), ColumnsConfigSet.nameToFormat("ph_enkondeguchi")));
                                    worksheet1.Cell(R, 19).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 5), ColumnsConfigSet.nameToFormat("cod_enkondeguchi")));
                                    worksheet1.Cell(R, 20).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 6), ColumnsConfigSet.nameToFormat("bod_enkondeguchi")));
                                    worksheet1.Cell(R, 21).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 7), ColumnsConfigSet.nameToFormat("ss_enkondeguchi")));
                                    worksheet1.Cell(R, 22).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 8), ColumnsConfigSet.nameToFormat("nd_enkondeguchi")));
                                }
                            }
                        }
                    }

                    worksheet1.SheetView.View = XLSheetViewOptions.PageBreakPreview;
                }
                else {
                    throw new Exception();
                }

                return;
            }




            //放流水2シート(放流口)の記入
            void makeSheet8_2(XLWorkbook workbook1) {
                IXLWorksheet worksheet1;

                if(workbook1.TryGetWorksheet("放流水2", out worksheet1)) {
                    //年度、管理者・処理場
                    worksheet1.Cell(2, 16).SetValue(bYear);
                    worksheet1.Cell(2, 13).SetValue(kanrisya + "　" + syorijou);

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

                        using(var cmd = con.CreateCommand()) {
                            cmd.CommandText = "SELECT " +
                                "SUM(wv_houryuu) " +
                                "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(8, 24).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 0), ColumnsConfigSet.nameToFormat("wv_houryuu")));
                                }
                            }
                        }
                    }

                    //月毎集計
                    using(var con = new SQLiteConnection(DataBaseConfig.ConnectionString)) {
                        con.Open();

                        using(var cmd = con.CreateCommand()) {
                            cmd.CommandText = "SELECT " +
                                "STRFTIME('%Y%m', sokutei_dt), " +
                                "AVG(wv_houryuu), " +
                                "AVG(wt_syuu_etsuryuu), " +
                                "AVG(pr_houryuukou), " +
                                "AVG(ph_houryuukou), " +
                                "AVG(cod_houryuukou), " +
                                "AVG(bod_houryuukou), " +
                                "AVG(ss_houryuukou), " +
                                "AVG(nd_houryuukou) " +
                                "FROM suishitsu_kanri " +
                                "WHERE del_flg IS NULL AND kanrisya='" + kanrisya + "' AND syorijou='" + syorijou + "' AND sokutei_dt>='" + stBYSTR + "' AND sokutei_dt<'" + edBYSTR + "' " +
                                "GROUP BY STRFTIME('%Y%m', sokutei_dt) " +
                                "ORDER BY STRFTIME('%Y%m', sokutei_dt)";

                            using(var reader = cmd.ExecuteReader()) {
                                DateTime cM = DateTime.Parse(stBYSTR).AddMonths(-1);
                                int d = 0;

                                while(reader.Read()) {
                                    while(reader.GetString(0) != cM.ToString("yyyyMM")) {
                                        cM = cM.AddMonths(1);
                                        d++;
                                    }

                                    int R = d + 7;

                                    worksheet1.Cell(R, 15).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 1), ColumnsConfigSet.nameToFormat("wv_houryuu")));
                                    worksheet1.Cell(R, 16).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 2), ColumnsConfigSet.nameToFormat("wt_syuu_etsuryuu")));
                                    worksheet1.Cell(R, 17).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 3), ColumnsConfigSet.nameToFormat("pr_houryuukou")));
                                    worksheet1.Cell(R, 18).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 4), ColumnsConfigSet.nameToFormat("ph_houryuukou")));
                                    worksheet1.Cell(R, 19).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 5), ColumnsConfigSet.nameToFormat("cod_houryuukou")));
                                    worksheet1.Cell(R, 20).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 6), ColumnsConfigSet.nameToFormat("bod_houryuukou")));
                                    worksheet1.Cell(R, 21).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 7), ColumnsConfigSet.nameToFormat("ss_houryuukou")));
                                    worksheet1.Cell(R, 22).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 8), ColumnsConfigSet.nameToFormat("nd_houryuukou")));
                                }
                            }
                        }
                    }

                    worksheet1.SheetView.View = XLSheetViewOptions.PageBreakPreview;
                }
                else {
                    throw new Exception();
                }

                return;
            }




            //処理工程毎の除去率シートの記入
            void makeSheet9(XLWorkbook workbook1) {
                IXLWorksheet worksheet1;

                if(workbook1.TryGetWorksheet("処理工程毎の除去率", out worksheet1)) {
                    //年度、管理者・処理場
                    worksheet1.Cell(2, 17).SetValue(bYear);
                    worksheet1.Cell(2, 14).SetValue(kanrisya + "　" + syorijou);

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

                        using(var cmd = con.CreateCommand()) {
                            cmd.CommandText = "SELECT " +
                                "AVG(bod_ryuunyuu), AVG(cod_ryuunyuu), AVG(ss_ryuunyuu), AVG(nd_ryuunyuu), AVG(tn_ryuunyuu), AVG(tp_ryuunyuu), " +
                                "AVG(bod_syo_ryuunyuu), AVG(cod_syo_ryuunyuu), AVG(ss_syo_ryuunyuu), " +
                                "AVG(bod_syo_etsuryuu), AVG(cod_syo_etsuryuu), AVG(ss_syo_etsuryuu), AVG(nd_syo_etsuryuu), AVG(tn_syo_etsuryuu), AVG(tp_syo_etsuryuu), " +
                                "AVG(bod_syuu_etsuryuu), AVG(cod_syuu_etsuryuu), AVG(ss_syuu_etsuryuu), AVG(nd_syuu_etsuryuu), AVG(tn_syuu_etsuryuu), AVG(tp_syuu_etsuryuu) " +
                                "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(5, 20).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 0), ColumnsConfigSet.nameToFormat("bod_ryuunyuu")));
                                    worksheet1.Cell(6, 20).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 1), ColumnsConfigSet.nameToFormat("cod_ryuunyuu")));
                                    worksheet1.Cell(7, 20).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 2), ColumnsConfigSet.nameToFormat("ss_ryuunyuu")));
                                    worksheet1.Cell(8, 20).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 3), ColumnsConfigSet.nameToFormat("nd_ryuunyuu")));
                                    worksheet1.Cell(9, 20).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 4), ColumnsConfigSet.nameToFormat("tn_ryuunyuu")));
                                    worksheet1.Cell(10, 20).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 5), ColumnsConfigSet.nameToFormat("tp_ryuunyuu")));

                                    worksheet1.Cell(5, 21).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 6), ColumnsConfigSet.nameToFormat("bod_syo_ryuunyuu")));
                                    worksheet1.Cell(6, 21).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 7), ColumnsConfigSet.nameToFormat("cod_syo_ryuunyuu")));
                                    worksheet1.Cell(7, 21).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 8), ColumnsConfigSet.nameToFormat("ss_syo_ryuunyuu")));

                                    worksheet1.Cell(5, 22).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 9), ColumnsConfigSet.nameToFormat("bod_syo_etsuryuu")));
                                    worksheet1.Cell(6, 22).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 10), ColumnsConfigSet.nameToFormat("cod_syo_etsuryuu")));
                                    worksheet1.Cell(7, 22).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 11), ColumnsConfigSet.nameToFormat("ss_syo_etsuryuu")));
                                    worksheet1.Cell(8, 22).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 12), ColumnsConfigSet.nameToFormat("nd_syo_etsuryuu")));
                                    worksheet1.Cell(9, 22).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 13), ColumnsConfigSet.nameToFormat("tn_syo_etsuryuu")));
                                    worksheet1.Cell(10, 22).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 14), ColumnsConfigSet.nameToFormat("tp_syo_etsuryuu")));

                                    worksheet1.Cell(5, 24).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 15), ColumnsConfigSet.nameToFormat("bod_syuu_etsuryuu")));
                                    worksheet1.Cell(6, 24).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 16), ColumnsConfigSet.nameToFormat("cod_syuu_etsuryuu")));
                                    worksheet1.Cell(7, 24).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 17), ColumnsConfigSet.nameToFormat("ss_syuu_etsuryuu")));
                                    worksheet1.Cell(8, 24).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 18), ColumnsConfigSet.nameToFormat("nd_syuu_etsuryuu")));
                                    worksheet1.Cell(9, 24).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 19), ColumnsConfigSet.nameToFormat("tn_syuu_etsuryuu")));
                                    worksheet1.Cell(10, 24).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 20), ColumnsConfigSet.nameToFormat("tp_syuu_etsuryuu")));
                                }
                            }
                        }
                    }

                    worksheet1.SheetView.View = XLSheetViewOptions.PageBreakPreview;
                }
                else {
                    throw new Exception();
                }

                return;
            }




            //反応タンク試験シートの記入
            void makeSheet10(XLWorkbook workbook1) {
                IXLWorksheet worksheet1;

                if(workbook1.TryGetWorksheet("反応タンク試験", out worksheet1)) {
                    //年度、管理者・処理場
                    worksheet1.Cell(2, 25).SetValue(bYear);
                    worksheet1.Cell(2, 22).SetValue(kanrisya + "　" + syorijou);

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

                        using(var cmd = con.CreateCommand()) {
                            cmd.CommandText = "SELECT " +
                                "SUM(wv_ryuunyuu) " +
                                "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(8, 50).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 0), ColumnsConfigSet.nameToFormat("wv_ryuunyuu")));
                                }
                            }
                        }
                    }

                    //月毎集計
                    using(var con = new SQLiteConnection(DataBaseConfig.ConnectionString)) {
                        con.Open();

                        using(var cmd = con.CreateCommand()) {
                            cmd.CommandText = "SELECT " +
                                "STRFTIME('%Y%m', sokutei_dt), " +
                                "AVG(wv_ryuunyuu), " +
                                "AVG(wt_hannoutank_all), AVG(ph_hannoutank_all), AVG(mldo_a_hannoutank_all), AVG(mlss_hannoutank_all), AVG(mlvss_hannoutank_all), AVG(vsspss_hannoutank_all), AVG(sv_hannoutank_all), AVG(svi_hannoutank_all), " +
                                "AVG(sv_hensou), AVG(or_hensou), AVG(rsss_hensou), AVG(vsspss_hensou), AVG(svi_hensou), " +
                                "AVG(bodpmlss_hannoutank_all), AVG(st_hannoutank_all), AVG(ar_hannoutank_all), AVG(srt_hannoutank_all), ";

                            switch(SyorijouConfigSet.getConfigString(kanrisya, syorijou, 6)) {
                                case "初沈越流水質":
                                    cmd.CommandText += "AVG(bod_syo_etsuryuu), AVG(ss_syo_etsuryuu), ";
                                    break;
                                case "流入水質":
                                    cmd.CommandText += "AVG(bod_ryuunyuu), AVG(ss_ryuunyuu), ";
                                    break;
                            }

                            cmd.CommandText += "AVG(bod_syuu_etsuryuu), AVG(ss_syuu_etsuryuu) " +
                                "FROM suishitsu_kanri " +
                                "WHERE del_flg IS NULL AND kanrisya='" + kanrisya + "' AND syorijou='" + syorijou + "' AND sokutei_dt>='" + stBYSTR + "' AND sokutei_dt<'" + edBYSTR + "' " +
                                "GROUP BY STRFTIME('%Y%m', sokutei_dt) " +
                                "ORDER BY STRFTIME('%Y%m', sokutei_dt)";

                            using(var reader = cmd.ExecuteReader()) {
                                DateTime cM = DateTime.Parse(stBYSTR).AddMonths(-1);
                                int d = 0;

                                while(reader.Read()) {
                                    while(reader.GetString(0) != cM.ToString("yyyyMM")) {
                                        cM = cM.AddMonths(1);
                                        d++;
                                    }

                                    int R = d + 7;

                                    worksheet1.Cell(R, 24).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 1), ColumnsConfigSet.nameToFormat("wv_ryuunyuu")));

                                    worksheet1.Cell(R, 25).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 2), ColumnsConfigSet.nameToFormat("wt_hannoutank_all")));
                                    worksheet1.Cell(R, 26).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 3), ColumnsConfigSet.nameToFormat("ph_hannoutank_all")));
                                    worksheet1.Cell(R, 27).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 4), ColumnsConfigSet.nameToFormat("mldo_a_hannoutank_all")));
                                    worksheet1.Cell(R, 28).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 5), ColumnsConfigSet.nameToFormat("mlss_hannoutank_all")));
                                    worksheet1.Cell(R, 29).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 6), ColumnsConfigSet.nameToFormat("mlvss_hannoutank_all")));
                                    worksheet1.Cell(R, 30).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 7), ColumnsConfigSet.nameToFormat("vsspss_hannoutank_all")));
                                    worksheet1.Cell(R, 31).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 8), ColumnsConfigSet.nameToFormat("sv_hannoutank_all")));
                                    worksheet1.Cell(R, 32).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 9), ColumnsConfigSet.nameToFormat("svi_hannoutank_all")));

                                    worksheet1.Cell(R, 33).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 10), ColumnsConfigSet.nameToFormat("sv_hensou")));
                                    worksheet1.Cell(R, 34).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 11), ColumnsConfigSet.nameToFormat("or_hensou")));
                                    worksheet1.Cell(R, 35).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 12), ColumnsConfigSet.nameToFormat("rsss_hensou")));
                                    worksheet1.Cell(R, 36).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 13), ColumnsConfigSet.nameToFormat("vsspss_hensou")));
                                    worksheet1.Cell(R, 37).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 14), ColumnsConfigSet.nameToFormat("svi_hensou")));

                                    worksheet1.Cell(R, 38).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 15), ColumnsConfigSet.nameToFormat("bodpmlss_hannoutank_all")));
                                    worksheet1.Cell(R, 41).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 16), ColumnsConfigSet.nameToFormat("st_hannoutank_all")));
                                    worksheet1.Cell(R, 42).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 17), ColumnsConfigSet.nameToFormat("ar_hannoutank_all")));
                                    worksheet1.Cell(R, 43).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 18), ColumnsConfigSet.nameToFormat("srt_hannoutank_all")));

                                    worksheet1.Cell(R, 45).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 19), ColumnsConfigSet.nameToFormat("bod_syo_etsuryuu")));
                                    worksheet1.Cell(R, 46).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 20), ColumnsConfigSet.nameToFormat("ss_syo_etsuryuu")));
                                    worksheet1.Cell(R, 47).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 21), ColumnsConfigSet.nameToFormat("bod_syuu_etsuryuu")));
                                    worksheet1.Cell(R, 48).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 22), ColumnsConfigSet.nameToFormat("ss_syuu_etsuryuu")));
                                }
                            }
                        }
                    }

                    worksheet1.Cell(3, 45).SetValue(SyorijouConfigSet.getConfigString(kanrisya, syorijou, 6));
                    worksheet1.Cell(18, 22).SetValue("　　　 入口側基準水質=" + SyorijouConfigSet.getConfigString(kanrisya, syorijou, 6));
                    worksheet1.Cell(21, 22).SetValue("　　　 基準水量=" + SyorijouConfigSet.getConfigString(kanrisya, syorijou, 2));

                    worksheet1.SheetView.View = XLSheetViewOptions.PageBreakPreview;
                }
                else {
                    throw new Exception();
                }

                return;
            }




            //脱水汚泥等搬出量シートの記入
            void makeSheet11(XLWorkbook workbook1) {
                IXLWorksheet worksheet1;

                if(workbook1.TryGetWorksheet("脱水汚泥等搬出量", out worksheet1)) {
                    //年度、管理者・処理場
                    worksheet1.Cell(2, 11).SetValue(bYear);
                    worksheet1.Cell(2, 8).SetValue(kanrisya + "　" + syorijou);

                    //月毎集計
                    using(var con = new SQLiteConnection(DataBaseConfig.ConnectionString)) {
                        con.Open();

                        using(var cmd = con.CreateCommand()) {
                            cmd.CommandText = "SELECT " +
                                "STRFTIME('%Y%m', sokutei_dt), " +
                                "SUM(ov_hansyutsu_all), SUM(wv_ryuunyuu), " +
                                "AVG(owr_dassui_output_enshin), AVG(owr_dassui_output_bp), AVG(owr_dassui_output_ex) " +
                                "FROM suishitsu_kanri " +
                                "WHERE del_flg IS NULL AND kanrisya='" + kanrisya + "' AND syorijou='" + syorijou + "' AND sokutei_dt>='" + stBYSTR + "' AND sokutei_dt<'" + edBYSTR + "' " +
                                "GROUP BY STRFTIME('%Y%m', sokutei_dt) " +
                                "ORDER BY STRFTIME('%Y%m', sokutei_dt)";

                            using(var reader = cmd.ExecuteReader()) {
                                DateTime cM = DateTime.Parse(stBYSTR).AddMonths(-1);
                                int d = 0;

                                while(reader.Read()) {
                                    while(reader.GetString(0) != cM.ToString("yyyyMM")) {
                                        cM = cM.AddMonths(1);
                                        d++;
                                    }

                                    int R = d + 7;

                                    worksheet1.Cell(R, 10).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 1), ColumnsConfigSet.nameToFormat("ov_hansyutsu_all")));
                                    worksheet1.Cell(R, 11).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 2), ColumnsConfigSet.nameToFormat("wv_ryuunyuu")));

                                    worksheet1.Cell(R, 13).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 3), ColumnsConfigSet.nameToFormat("owr_dassui_output_enshin")));
                                    worksheet1.Cell(R, 14).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 4), ColumnsConfigSet.nameToFormat("owr_dassui_output_bp")));
                                    worksheet1.Cell(R, 15).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 5), ColumnsConfigSet.nameToFormat("owr_dassui_output_ex")));
                                }
                            }
                        }
                    }

                    worksheet1.SheetView.View = XLSheetViewOptions.PageBreakPreview;
                }
                else {
                    throw new Exception();
                }

                return;
            }




            //嫌気性消化槽運転状況シートの記入
            void makeSheet12(XLWorkbook workbook1) {
                IXLWorksheet worksheet1;

                if(workbook1.TryGetWorksheet("嫌気性消化槽運転状況", out worksheet1)) {
                    //年度、管理者・処理場
                    worksheet1.Cell(2, 26).SetValue(bYear);
                    worksheet1.Cell(2, 23).SetValue(kanrisya + "　" + syorijou);

                    //名称は最新値を採用
                    using(var con = new SQLiteConnection(DataBaseConfig.ConnectionString)) {
                        con.Open();

                        for(int i = 1; i <= 4; i++) {
                            using(var cmd = con.CreateCommand()) {
                                cmd.CommandText = "SELECT " +
                                    "name_syoukatank_" + i + " " +
                                    "FROM suishitsu_kanri " +
                                    "WHERE del_flg IS NULL AND kanrisya='" + kanrisya + "' AND syorijou='" + syorijou + "' AND sokutei_dt>='" + stBYSTR + "' AND sokutei_dt<'" + edBYSTR + "'" +
                                    "AND name_syoukatank_" + i + " IS NOT NULL AND name_syoukatank_" + i + "<>''" +
                                    "ORDER BY sokutei_dt DESC LIMIT 1";

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

                                        worksheet1.Cell(4, 30 + i).SetValue(name);
                                    }
                                    else {
                                        worksheet1.Range(4, 7 + i, 5, 7 + i).SetValue("");
                                        worksheet1.Range(4, 30 + i, 4, 30 + i).SetValue("");
                                    }
                                }
                            }
                        }
                    }

                    //月毎集計
                    using(var con = new SQLiteConnection(DataBaseConfig.ConnectionString)) {
                        con.Open();

                        using(var cmd = con.CreateCommand()) {
                            cmd.CommandText = "SELECT " +
                                "STRFTIME('%Y%m', sokutei_dt), " +
                                "SUM(ov_syouka_input), AVG(oc_syouka_input), AVG(oy_syouka_input), " +
                                "SUM(ov_syouka_output), AVG(oc_syouka_output), AVG(oy_syouka_output)";

                            for(int i = 1; i <= 4; i++) {
                                cmd.CommandText += ", AVG(tt_syoukatank_" + i + ")";
                            }

                            cmd.CommandText += ", AVG(d_syouka), " +
                                "AVG(r_syouka), " +
                                "SUM(v_syoukag_hassei), SUM(v_syoukag_soukaon), SUM(v_syoukag_yogas), SUM(v_syoukag_baiden), AVG(gr_syoukagas), AVG(r_syoukagas_ch3), AVG(r_syoukagas_co2), SUM(m_datsuryuu), " +
                                "SUM(ee_kakuhanki) " +
                                "FROM suishitsu_kanri " +
                                "WHERE del_flg IS NULL AND kanrisya='" + kanrisya + "' AND syorijou='" + syorijou + "' AND sokutei_dt>='" + stBYSTR + "' AND sokutei_dt<'" + edBYSTR + "' " +
                                "GROUP BY STRFTIME('%Y%m', sokutei_dt) " +
                                "ORDER BY STRFTIME('%Y%m', sokutei_dt)";

                            using(var reader = cmd.ExecuteReader()) {
                                DateTime cM = DateTime.Parse(stBYSTR).AddMonths(-1);
                                int d = 0;

                                while(reader.Read()) {
                                    while(reader.GetString(0) != cM.ToString("yyyyMM")) {
                                        cM = cM.AddMonths(1);
                                        d++;
                                    }

                                    int R = d + 7;

                                    worksheet1.Cell(R, 25).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 1), ColumnsConfigSet.nameToFormat("ov_syouka_input")));
                                    worksheet1.Cell(R, 26).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 2), ColumnsConfigSet.nameToFormat("oc_syouka_input")));
                                    worksheet1.Cell(R, 27).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 3), ColumnsConfigSet.nameToFormat("oy_syouka_input")));

                                    worksheet1.Cell(R, 28).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 4), ColumnsConfigSet.nameToFormat("ov_syouka_output")));
                                    worksheet1.Cell(R, 29).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 5), ColumnsConfigSet.nameToFormat("oc_syouka_output")));
                                    worksheet1.Cell(R, 30).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 6), ColumnsConfigSet.nameToFormat("oy_syouka_output")));

                                    int C = 30;
                                    int r = 6;

                                    for(int i = 1; i <= 4; i++) {
                                        worksheet1.Cell(R, C + i).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, r + i), ColumnsConfigSet.nameToFormat("tt_syoukatank_1")));
                                    }

                                    worksheet1.Cell(R, 35).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 11), ColumnsConfigSet.nameToFormat("d_syouka")));

                                    worksheet1.Cell(R, 36).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 12), ColumnsConfigSet.nameToFormat("r_syouka")));

                                    worksheet1.Cell(R, 37).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 13), ColumnsConfigSet.nameToFormat("v_syoukag_hassei")));
                                    worksheet1.Cell(R, 38).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 14), ColumnsConfigSet.nameToFormat("v_syoukag_soukaon")));
                                    worksheet1.Cell(R, 39).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 15), ColumnsConfigSet.nameToFormat("v_syoukag_yogas")));
                                    worksheet1.Cell(R, 40).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 16), ColumnsConfigSet.nameToFormat("v_syoukag_baiden")));
                                    worksheet1.Cell(R, 41).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 17), ColumnsConfigSet.nameToFormat("gr_syoukagas")));
                                    worksheet1.Cell(R, 42).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 18), ColumnsConfigSet.nameToFormat("r_syoukagas_ch3")));
                                    worksheet1.Cell(R, 43).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 19), ColumnsConfigSet.nameToFormat("r_syoukagas_co2")));
                                    worksheet1.Cell(R, 44).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 20), ColumnsConfigSet.nameToFormat("m_datsuryuu")));

                                    worksheet1.Cell(R, 45).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 21), ColumnsConfigSet.nameToFormat("ee_kakuhanki")));
                                }
                            }
                        }
                    }

                    worksheet1.SheetView.View = XLSheetViewOptions.PageBreakPreview;
                }
                else {
                    throw new Exception();
                }

                return;
            }




            //沈砂・しさ搬出量シートの記入
            void makeSheet13(XLWorkbook workbook1) {
                IXLWorksheet worksheet1;

                if(workbook1.TryGetWorksheet("沈砂・しさ搬出量", out worksheet1)) {
                    //年度、管理者・処理場
                    worksheet1.Cell(2, 9).SetValue(bYear);
                    worksheet1.Cell(2, 6).SetValue(kanrisya + "　" + syorijou);

                    //月毎集計
                    using(var con = new SQLiteConnection(DataBaseConfig.ConnectionString)) {
                        con.Open();

                        using(var cmd = con.CreateCommand()) {
                            cmd.CommandText = "SELECT " +
                                "STRFTIME('%Y%m', sokutei_dt), " +
                                "SUM(ov_hansyutsu_chinsa_pumpjou_all), SUM(ov_hansyutsu_shisa_pumpjou_all), " +
                                "SUM(ov_hansyutsu_chinsa), SUM(ov_hansyutsu_shisa) " +
                                "FROM suishitsu_kanri " +
                                "WHERE del_flg IS NULL AND kanrisya='" + kanrisya + "' AND syorijou='" + syorijou + "' AND sokutei_dt>='" + stBYSTR + "' AND sokutei_dt<'" + edBYSTR + "' " +
                                "GROUP BY STRFTIME('%Y%m', sokutei_dt) " +
                                "ORDER BY STRFTIME('%Y%m', sokutei_dt)";

                            using(var reader = cmd.ExecuteReader()) {
                                DateTime cM = DateTime.Parse(stBYSTR).AddMonths(-1);
                                int d = 0;

                                while(reader.Read()) {
                                    while(reader.GetString(0) != cM.ToString("yyyyMM")) {
                                        cM = cM.AddMonths(1);
                                        d++;
                                    }

                                    int R = d + 6;

                                    worksheet1.Cell(R, 8).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 1), ColumnsConfigSet.nameToFormat("ov_hansyutsu_chinsa_pumpjou_all")));
                                    worksheet1.Cell(R, 9).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 2), ColumnsConfigSet.nameToFormat("ov_hansyutsu_shisa_pumpjou_all")));
                                    worksheet1.Cell(R, 10).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 3), ColumnsConfigSet.nameToFormat("ov_hansyutsu_chinsa")) * 1000);
                                    worksheet1.Cell(R, 11).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 4), ColumnsConfigSet.nameToFormat("ov_hansyutsu_shisa")) * 1000);
                                }
                            }
                        }
                    }

                    worksheet1.SheetView.View = XLSheetViewOptions.PageBreakPreview;
                }
                else {
                    throw new Exception();
                }

                return;
            }




            //主要機器運転時間1シートの記入
            void makeSheet14(XLWorkbook workbook1) {
                IXLWorksheet worksheet1;

                if(workbook1.TryGetWorksheet("主要機器運転時間1", out worksheet1)) {
                    //年度、管理者・処理場
                    worksheet1.Cell(2, 20).SetValue(bYear);
                    worksheet1.Cell(2, 17).SetValue(kanrisya + "　" + syorijou);

                    //名称は最新値を採用
                    using(var con = new SQLiteConnection(DataBaseConfig.ConnectionString)) {
                        con.Open();

                        int R = 3;

                        for(int i = 1; i <= 12; i++) {
                            R++;

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

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

                                        worksheet1.Cell(R, 3).SetValue(name);
                                    }
                                    else {
                                        worksheet1.Range(R, 3, R, 3).SetValue("");
                                    }
                                }
                            }
                        }

                        for(int i = 1; i <= 12; i++) {
                            R++;

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

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

                                        worksheet1.Cell(R, 3).SetValue(name);
                                    }
                                    else {
                                        worksheet1.Range(R, 3, R, 3).SetValue("");
                                    }
                                }
                            }
                        }

                        for(int i = 1; i <= 12; i++) {
                            R++;

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

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

                                        worksheet1.Cell(R, 3).SetValue(name);
                                    }
                                    else {
                                        worksheet1.Range(R, 3, R, 3).SetValue("");
                                    }
                                }
                            }
                        }
                    }

                    //月毎集計
                    using(var con = new SQLiteConnection(DataBaseConfig.ConnectionString)) {
                        con.Open();

                        using(var cmd = con.CreateCommand()) {
                            cmd.CommandText = "SELECT " +
                                "STRFTIME('%Y%m', sokutei_dt)";

                            for(int i = 1; i <= 12; i++) {
                                cmd.CommandText += ", SUM(dt_syupump_" + i + ")";
                            }

                            for(int i = 1; i <= 12; i++) {
                                cmd.CommandText += ", SUM(dt_soufuuki_" + i + ")";
                            }

                            for(int i = 1; i <= 12; i++) {
                                cmd.CommandText += ", SUM(dt_houryuupump_" + i + ")";
                            }

                            cmd.CommandText += " FROM suishitsu_kanri " +
                                "WHERE del_flg IS NULL AND kanrisya='" + kanrisya + "' AND syorijou='" + syorijou + "' AND sokutei_dt>='" + stBYSTR + "' AND sokutei_dt<'" + edBYSTR + "' " +
                                "GROUP BY STRFTIME('%Y%m', sokutei_dt) " +
                                "ORDER BY STRFTIME('%Y%m', sokutei_dt)";

                            using(var reader = cmd.ExecuteReader()) {
                                DateTime cM = DateTime.Parse(stBYSTR).AddMonths(-1);
                                int d = 0;

                                while(reader.Read()) {
                                    while(reader.GetString(0) != cM.ToString("yyyyMM")) {
                                        cM = cM.AddMonths(1);
                                        d++;
                                    }

                                    int R = 3;
                                    int C = d + 3;
                                    int r = 0;

                                    for(int i = 1; i <= 12; i++) {
                                        R++;
                                        r++;
                                        worksheet1.Cell(R, C).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, r), ColumnsConfigSet.nameToFormat("dt_syupump_1")));
                                    }

                                    for(int i = 1; i <= 12; i++) {
                                        R++;
                                        r++;
                                        worksheet1.Cell(R, C).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, r), ColumnsConfigSet.nameToFormat("dt_soufuuki_1")));
                                    }

                                    for(int i = 1; i <= 12; i++) {
                                        R++;
                                        r++;
                                        worksheet1.Cell(R, C).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, r), ColumnsConfigSet.nameToFormat("dt_houryuupump_1")));
                                    }
                                }
                            }
                        }
                    }

                    worksheet1.SheetView.View = XLSheetViewOptions.PageBreakPreview;
                }
                else {
                    throw new Exception();
                }

                return;
            }




            //主要機器運転時間2シートの記入
            void makeSheet15(XLWorkbook workbook1) {
                IXLWorksheet worksheet1;

                if(workbook1.TryGetWorksheet("主要機器運転時間2", out worksheet1)) {
                    //年度、管理者・処理場
                    worksheet1.Cell(2, 20).SetValue(bYear);
                    worksheet1.Cell(2, 17).SetValue(kanrisya + "　" + syorijou);

                    //名称は最新値を採用
                    using(var con = new SQLiteConnection(DataBaseConfig.ConnectionString)) {
                        con.Open();

                        int R = 3;

                        for(int i = 1; i <= 8; i++) {
                            R++;

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

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

                                        worksheet1.Cell(R, 3).SetValue(type + "(" + name + ")");
                                    }
                                    else {
                                        worksheet1.Range(R, 3, R, 3).SetValue("");
                                    }
                                }
                            }
                        }

                        for(int i = 1; i <= 8; i++) {
                            R++;

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

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

                                        worksheet1.Cell(R, 3).SetValue(type + "(" + name + ")");
                                    }
                                    else {
                                        worksheet1.Range(R, 3, R, 3).SetValue("");
                                    }
                                }
                            }
                        }
                    }

                    //月毎集計
                    using(var con = new SQLiteConnection(DataBaseConfig.ConnectionString)) {
                        con.Open();

                        using(var cmd = con.CreateCommand()) {
                            cmd.CommandText = "SELECT " +
                                "STRFTIME('%Y%m', sokutei_dt)";

                            for(int i = 1; i <= 8; i++) {
                                cmd.CommandText += ", SUM(dt_nousyukuki_" + i + ")";
                            }

                            for(int i = 1; i <= 8; i++) {
                                cmd.CommandText += ", SUM(dt_dassuiki_" + i + ")";
                            }

                            cmd.CommandText += " FROM suishitsu_kanri " +
                                "WHERE del_flg IS NULL AND kanrisya='" + kanrisya + "' AND syorijou='" + syorijou + "' AND sokutei_dt>='" + stBYSTR + "' AND sokutei_dt<'" + edBYSTR + "' " +
                                "GROUP BY STRFTIME('%Y%m', sokutei_dt) " +
                                "ORDER BY STRFTIME('%Y%m', sokutei_dt)";

                            using(var reader = cmd.ExecuteReader()) {
                                DateTime cM = DateTime.Parse(stBYSTR).AddMonths(-1);
                                int d = 0;

                                while(reader.Read()) {
                                    while(reader.GetString(0) != cM.ToString("yyyyMM")) {
                                        cM = cM.AddMonths(1);
                                        d++;
                                    }

                                    int R = 3;
                                    int C = d + 3;
                                    int r = 0;

                                    for(int i = 1; i <= 8; i++) {
                                        R++;
                                        r++;
                                        worksheet1.Cell(R, C).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, r), ColumnsConfigSet.nameToFormat("dt_nousyukuki_1")));
                                    }

                                    for(int i = 1; i <= 8; i++) {
                                        R++;
                                        r++;
                                        worksheet1.Cell(R, C).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, r), ColumnsConfigSet.nameToFormat("dt_dassuiki_1")));
                                    }
                                }
                            }
                        }
                    }

                    worksheet1.SheetView.View = XLSheetViewOptions.PageBreakPreview;
                }
                else {
                    throw new Exception();
                }

                return;
            }
        }

    }
}
