// 下水データ管理システム
// 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 FormD4YearDataSelect : Form {
        public FormD4YearDataSelect() {
            InitializeComponent();
        }

        int cBYear = 0;

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




        //ロード時イベント
        private void FormD3ResearchDataSelect_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) {
            yearDataExportExcel();
        }




        //年度毎集計データ出力
        private void yearDataExportExcel() {
            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\year_data.xlsx")) {
                        progressSplash1.ProgressMessage = "年度毎集計シート作成中";
                        Application.DoEvents();
                        makeSheet1(workbook1);

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

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




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

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

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

                        for(int i = 1; i <= MaxSizeConfig.maxPumpjou; 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, 3 + i).SetValue(name);
                                    }
                                }
                            }
                        }

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

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

                                        worksheet1.Cell(55, 3 + i).SetValue(name);
                                    }
                                }
                            }
                        }

                        for(int i = 1; i <= MaxSizeConfig.maxNousyukuki; i++) {
                            using(var cmd = con.CreateCommand()) {
                                cmd.CommandText = "SELECT " +
                                    "name_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);

                                        worksheet1.Cell(71, 3 + i).SetValue(name);
                                    }
                                }
                            }
                        }

                        for(int i = 1; i <= MaxSizeConfig.maxNousyukuki; i++) {
                            using(var cmd = con.CreateCommand()) {
                                cmd.CommandText = "SELECT " +
                                    "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 type_nousyukuki_" + i + " IS NOT NULL AND type_nousyukuki_" + i + "<>''" +
                                    "ORDER BY sokutei_dt DESC LIMIT 1";

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

                                        worksheet1.Cell(72, 3 + i).SetValue(name);
                                    }
                                }
                            }
                        }

                        for(int i = 1; i <= MaxSizeConfig.maxDassuiki; i++) {
                            using(var cmd = con.CreateCommand()) {
                                cmd.CommandText = "SELECT " +
                                    "name_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);

                                        worksheet1.Cell(121, 3 + i).SetValue(name);
                                    }
                                }
                            }
                        }

                        for(int i = 1; i <= MaxSizeConfig.maxDassuiki; i++) {
                            using(var cmd = con.CreateCommand()) {
                                cmd.CommandText = "SELECT " +
                                    "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 type_dassuiki_" + i + " IS NOT NULL AND type_dassuiki_" + i + "<>''" +
                                    "ORDER BY sokutei_dt DESC LIMIT 1";

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

                                        worksheet1.Cell(122, 3 + i).SetValue(name);
                                    }
                                }
                            }
                        }
                    }

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

                        //ポンプ場
                        for(int i = 1; i <= MaxSizeConfig.maxPumpjou; i++) {
                            using(var cmd = con.CreateCommand()) {
                                cmd.CommandText = "SELECT " +
                                    "SUM(wv_pumpjou_" + i + "), SUM(ee_pumpjou_" + i + "), SUM(v_hao_pumpjou_" + i + "), SUM(v_lo_pumpjou_" + i + "), SUM(v_chinsa_pumpjou_" + i + "), SUM(v_shisa_pumpjou_" + i + ") " +
                                    "FROM suishitsu_kanri " +
                                    "WHERE del_flg IS NULL AND kanrisya='" + kanrisya + "' AND syorijou='" + syorijou + "' AND sokutei_dt>='" + stBYSTR + "' AND sokutei_dt<'" + edBYSTR + "'";

                                using(var reader = cmd.ExecuteReader()) {
                                    if(reader.Read()) {
                                        worksheet1.Cell(5, 3 + i).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 0), ColumnsConfigSet.nameToFormat("wv_pumpjou_1")));
                                        worksheet1.Cell(6, 3 + i).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 1), ColumnsConfigSet.nameToFormat("ee_pumpjou_1")) / 1000);
                                        worksheet1.Cell(7, 3 + i).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 2), ColumnsConfigSet.nameToFormat("v_hao_pumpjou_1")));
                                        worksheet1.Cell(8, 3 + i).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 3), ColumnsConfigSet.nameToFormat("v_lo_pumpjou_1")));
                                        worksheet1.Cell(9, 3 + i).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 4), ColumnsConfigSet.nameToFormat("v_chinsa_pumpjou_1")) / 1000);
                                        worksheet1.Cell(10, 3 + i).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 5), ColumnsConfigSet.nameToFormat("v_shisa_pumpjou_1")) / 1000);
                                    }
                                }
                            }
                        }

                        //処理場その1
                        using(var cmd = con.CreateCommand()) {
                            cmd.CommandText = "SELECT " +
                                "SUM(v_chinsa_syorijou), SUM(v_shisa_syorijou), " +
                                "SUM(wv_ryuunyuu), AVG(wv_ryuunyuu), SUM(CASE WHEN seiten='○' THEN wv_ryuunyuu ELSE NULL END), AVG(CASE WHEN seiten='○' THEN wv_ryuunyuu ELSE NULL END), SUM(wv_kani), SUM(wv_houryuu), " +
                                "SUM(ov_nama), SUM(ov_nama * oc_nama / 100), SUM(ov_yojou), SUM(ov_yojou * oc_yojou / 100), " +
                                "SUM(ee_jounaipump), SUM(ee_mizusyori), SUM(ee_odeisyori), SUM(ee_extra), SUM(ee_kakuhanki), " +
                                "SUM(v_hao_kanri), SUM(v_hao_syouka), SUM(v_hao_jikahatsu), SUM(v_hao_sonota), " +
                                "SUM(v_lo_kanri), SUM(v_lo_syouka), SUM(v_lo_jikahatsu), SUM(v_lo_sonota), " +
                                "SUM(v_kerosene_kanri), SUM(v_kerosene_syouka), SUM(v_kerosene_jikahatsu), SUM(v_kerosene_sonota), " +
                                "SUM(v_gasoline_kanri), SUM(v_gasoline_syouka), SUM(v_gasoline_jikahatsu), SUM(v_gasoline_sonota), " +
                                "SUM(v_pgas_kanri), SUM(v_pgas_syouka), SUM(v_pgas_jikahatsu), SUM(v_pgas_sonota) " +
                                "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(13, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 0), ColumnsConfigSet.nameToFormat("v_chinsa_syorijou")) / 1000);
                                    worksheet1.Cell(14, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 1), ColumnsConfigSet.nameToFormat("v_shisa_syorijou")) / 1000);

                                    worksheet1.Cell(15, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 2), ColumnsConfigSet.nameToFormat("wv_ryuunyuu")));
                                    worksheet1.Cell(16, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 3), ColumnsConfigSet.nameToFormat("wv_ryuunyuu")));
                                    worksheet1.Cell(17, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 4), ColumnsConfigSet.nameToFormat("wv_ryuunyuu")));
                                    worksheet1.Cell(18, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 5), ColumnsConfigSet.nameToFormat("wv_ryuunyuu")));
                                    worksheet1.Cell(19, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 6), ColumnsConfigSet.nameToFormat("wv_kani")));
                                    worksheet1.Cell(20, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 7), ColumnsConfigSet.nameToFormat("wv_houryuu")));

                                    decimal? ov_nama = FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 8), ColumnsConfigSet.nameToFormat("ov_nama"));
                                    decimal? osv_nama = FunctionForDatabase.decConvFromDB(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 9), ColumnsConfigSet.nameToFormat("ov_nama")), ColumnsConfigSet.nameToFormat("oc_nama"));

                                    worksheet1.Cell(21, 4).SetValue(ov_nama);

                                    if(ov_nama != null && osv_nama != null) {
                                        if(ov_nama != 0) {
                                            worksheet1.Cell(22, 4).SetValue(osv_nama / ov_nama * 100);
                                        }
                                    }

                                    decimal? ov_yojou = FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 10), ColumnsConfigSet.nameToFormat("ov_yojou"));
                                    decimal? osv_yojou = FunctionForDatabase.decConvFromDB(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 11), ColumnsConfigSet.nameToFormat("ov_yojou")), ColumnsConfigSet.nameToFormat("oc_yojou"));

                                    worksheet1.Cell(23, 4).SetValue(ov_yojou);

                                    if(ov_yojou != null && osv_yojou != null) {
                                        if(ov_yojou != 0) {
                                            worksheet1.Cell(24, 4).SetValue(osv_yojou / ov_yojou * 100);
                                        }
                                    }

                                    worksheet1.Cell(25, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 12), ColumnsConfigSet.nameToFormat("ee_jounaipump")) / 1000);
                                    worksheet1.Cell(26, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 13), ColumnsConfigSet.nameToFormat("ee_mizusyori")) / 1000);
                                    worksheet1.Cell(27, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 14), ColumnsConfigSet.nameToFormat("ee_odeisyori")) / 1000);
                                    worksheet1.Cell(28, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 15), ColumnsConfigSet.nameToFormat("ee_extra")) / 1000);
                                    worksheet1.Cell(29, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 16), ColumnsConfigSet.nameToFormat("ee_kakuhanki")) / 1000);

                                    worksheet1.Cell(32, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 17), ColumnsConfigSet.nameToFormat("v_hao_kanri")));
                                    worksheet1.Cell(32, 5).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 18), ColumnsConfigSet.nameToFormat("v_hao_syouka")));
                                    worksheet1.Cell(32, 6).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 19), ColumnsConfigSet.nameToFormat("v_hao_jikahatsu")));
                                    worksheet1.Cell(32, 7).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 20), ColumnsConfigSet.nameToFormat("v_hao_sonota")));

                                    worksheet1.Cell(33, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 21), ColumnsConfigSet.nameToFormat("v_lo_kanri")));
                                    worksheet1.Cell(33, 5).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 22), ColumnsConfigSet.nameToFormat("v_lo_syouka")));
                                    worksheet1.Cell(33, 6).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 23), ColumnsConfigSet.nameToFormat("v_lo_jikahatsu")));
                                    worksheet1.Cell(33, 7).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 24), ColumnsConfigSet.nameToFormat("v_lo_sonota")));

                                    worksheet1.Cell(34, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 25), ColumnsConfigSet.nameToFormat("v_kerosene_kanri")));
                                    worksheet1.Cell(34, 5).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 26), ColumnsConfigSet.nameToFormat("v_kerosene_syouka")));
                                    worksheet1.Cell(34, 6).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 27), ColumnsConfigSet.nameToFormat("v_kerosene_jikahatsu")));
                                    worksheet1.Cell(34, 7).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 28), ColumnsConfigSet.nameToFormat("v_kerosene_sonota")));

                                    worksheet1.Cell(35, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 29), ColumnsConfigSet.nameToFormat("v_gasoline_kanri")));
                                    worksheet1.Cell(35, 5).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 30), ColumnsConfigSet.nameToFormat("v_gasoline_syouka")));
                                    worksheet1.Cell(35, 6).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 31), ColumnsConfigSet.nameToFormat("v_gasoline_jikahatsu")));
                                    worksheet1.Cell(35, 7).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 32), ColumnsConfigSet.nameToFormat("v_gasoline_sonota")));

                                    worksheet1.Cell(36, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 33), ColumnsConfigSet.nameToFormat("v_pgas_kanri")));
                                    worksheet1.Cell(36, 5).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 34), ColumnsConfigSet.nameToFormat("v_pgas_syouka")));
                                    worksheet1.Cell(36, 6).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 35), ColumnsConfigSet.nameToFormat("v_pgas_jikahatsu")));
                                    worksheet1.Cell(36, 7).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 36), ColumnsConfigSet.nameToFormat("v_pgas_sonota")));
                                }
                            }
                        }

                        //処理場その2
                        using(var cmd = con.CreateCommand()) {
                            cmd.CommandText = "SELECT " +
                                "SUM(wv_syochin), AVG(wv_syochin), " +
                                "SUM(av_hannoutank_all), SUM(wv_syochin) ,SUM(wv_kani), AVG(av_hannoutank_all), AVG(bodpmlss_hannoutank_all), AVG(srt_hannoutank_all), AVG(mlss_hannoutank_all), AVG(svi_hannoutank_all), " +
                                "SUM(ov_hensou), SUM(ov_yojou), " +
                                "SUM(wv_jia), MAX(mr_jia), AVG(mr_jia) " +
                                "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(39, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 0), ColumnsConfigSet.nameToFormat("wv_syochin")));
                                    worksheet1.Cell(40, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 1), ColumnsConfigSet.nameToFormat("wv_syochin")));

                                    decimal? av_hannoutank_all = FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 2), ColumnsConfigSet.nameToFormat("av_hannoutank_all"));
                                    decimal? wv_syochin = FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 3), ColumnsConfigSet.nameToFormat("wv_syochin"));
                                    decimal? wv_kani = FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 4), ColumnsConfigSet.nameToFormat("wv_kani"));

                                    if(wv_syochin != null) {
                                        decimal wv_kei = (decimal)wv_syochin;

                                        if(wv_kani != null) {
                                            wv_kei -= (decimal)wv_kani;
                                        }

                                        if(wv_syochin > 0 && av_hannoutank_all != null) {
                                            worksheet1.Cell(41, 4).SetValue(av_hannoutank_all / wv_kei);
                                        }
                                    }

                                    worksheet1.Cell(42, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 5), ColumnsConfigSet.nameToFormat("av_hannoutank_all")));
                                    worksheet1.Cell(43, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 6), ColumnsConfigSet.nameToFormat("bodpmlss_hannoutank_all")));
                                    worksheet1.Cell(44, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 7), ColumnsConfigSet.nameToFormat("srt_hannoutank_all")));
                                    worksheet1.Cell(45, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 8), ColumnsConfigSet.nameToFormat("mlss_hannoutank_all")));
                                    worksheet1.Cell(46, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 9), ColumnsConfigSet.nameToFormat("svi_hannoutank_all")));

                                    decimal? ov_hensou = FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 10), ColumnsConfigSet.nameToFormat("ov_hensou"));

                                    if(wv_syochin != null) {
                                        decimal wv_kei = (decimal)wv_syochin;

                                        if(wv_kani != null) {
                                            wv_kei -= (decimal)wv_kani;
                                        }

                                        if(wv_syochin > 0 && av_hannoutank_all != null) {
                                            worksheet1.Cell(47, 4).SetValue(ov_hensou / wv_kei * 100);
                                        }
                                    }
                                    
                                    worksheet1.Cell(48, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 11), ColumnsConfigSet.nameToFormat("ov_yojou")));

                                    worksheet1.Cell(49, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 12), ColumnsConfigSet.nameToFormat("wv_jia")));
                                    worksheet1.Cell(50, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 13), ColumnsConfigSet.nameToFormat("mr_jia")));
                                    worksheet1.Cell(51, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 14), ColumnsConfigSet.nameToFormat("mr_jia")));
                                }
                            }
                        }

                        //水質試験成績
                        for(int i = 1; i <= MaxSizeConfig.maxSpecialAnalysis; i++) {
                            using(var cmd = con.CreateCommand()) {
                                cmd.CommandText = "SELECT " +
                                    "AVG(kion_exbunseki_" + i + "), AVG(wt_exbunseki_" + i + "), MAX(wt_exbunseki_" + i + "), MIN(wt_exbunseki_" + i + "), " +
                                    "AVG(pr_exbunseki_" + i + "), AVG(ph_exbunseki_" + i + "), AVG(bod_exbunseki_" + i + "), AVG(cod_exbunseki_" + i + "), AVG(ss_exbunseki_" + i + "), AVG(nd_exbunseki_" + i + "), AVG(tn_exbunseki_" + i + "), AVG(tp_exbunseki_" + i + ") " +
                                    "FROM suishitsu_kanri " +
                                    "WHERE del_flg IS NULL AND kanrisya='" + kanrisya + "' AND syorijou='" + syorijou + "' AND sokutei_dt>='" + stBYSTR + "' AND sokutei_dt<'" + edBYSTR + "'";

                                using(var reader = cmd.ExecuteReader()) {
                                    if(reader.Read()) {
                                        worksheet1.Cell(56, 3 + i).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 0), ColumnsConfigSet.nameToFormat("kion_exbunseki_1")));
                                        worksheet1.Cell(57, 3 + i).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 1), ColumnsConfigSet.nameToFormat("wt_exbunseki_1")));
                                        worksheet1.Cell(58, 3 + i).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 2), ColumnsConfigSet.nameToFormat("wt_exbunseki_1")));
                                        worksheet1.Cell(59, 3 + i).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 3), ColumnsConfigSet.nameToFormat("wt_exbunseki_1")));

                                        worksheet1.Cell(60, 3 + i).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 4), ColumnsConfigSet.nameToFormat("pr_exbunseki_1")));
                                        worksheet1.Cell(61, 3 + i).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 5), ColumnsConfigSet.nameToFormat("ph_exbunseki_1")));
                                        worksheet1.Cell(62, 3 + i).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 6), ColumnsConfigSet.nameToFormat("bod_exbunseki_1")));
                                        worksheet1.Cell(63, 3 + i).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 7), ColumnsConfigSet.nameToFormat("cod_exbunseki_1")));
                                        worksheet1.Cell(64, 3 + i).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 8), ColumnsConfigSet.nameToFormat("ss_exbunseki_1")));
                                        worksheet1.Cell(65, 3 + i).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 9), ColumnsConfigSet.nameToFormat("nd_exbunseki_1")));
                                        worksheet1.Cell(66, 3 + i).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 10), ColumnsConfigSet.nameToFormat("tn_exbunseki_1")));
                                        worksheet1.Cell(67, 3 + i).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 11), ColumnsConfigSet.nameToFormat("tp_exbunseki_1")));
                                    }
                                }
                            }
                        }

                        //汚泥濃縮設備
                        string[] excNItems = ccSet[ColumnsConfigSet.nameToIndex("type_exc1_nousyukuki_1")].Items.Split(',');
                        string[] otypeNItems = ccSet[ColumnsConfigSet.nameToIndex("otype_nousyukuki_1")].Items.Split(',');

                        for(int i = 1; i <= MaxSizeConfig.maxNousyukuki; i++) {
                            using(var cmd = con.CreateCommand()) {
                                cmd.CommandText = "SELECT " +
                                    "SUM(CASE WHEN dt_nousyukuki_" + i + ">0 THEN 1 ELSE NULL END), SUM(dt_nousyukuki_" + i + "), " +
                                    "SUM(ug_gyousyuuzai_nousyukuki_" + i + "), SUM(ug_kihoujozai_nousyukuki_" + i + ")";

                                for(int j = 1; j <= 7; j++) {
                                    cmd.CommandText += ", SUM(CASE WHEN type_exc1_nousyukuki_" + i + "='" + excNItems[j] + "' THEN ug_exc1_nousyukuki_" + i + " ELSE 0 END) + SUM(CASE WHEN type_exc2_nousyukuki_" + i + "='" + excNItems[j] + "' THEN ug_exc2_nousyukuki_" + i + " ELSE 0 END)";
                                }

                                for(int j = 1; j <= 5; j++) {
                                    cmd.CommandText += ", SUM(CASE WHEN otype_nousyukuki_" + i + "='" + otypeNItems[j] + "' THEN ov_nousyukuki_" + i + " ELSE NULL END)";
                                    cmd.CommandText += ", SUM(CASE WHEN otype_nousyukuki_" + i + "='" + otypeNItems[j] + "' THEN ov_nousyukuki_" + i + " * oci_nousyukuki_" + i + " / 100 ELSE NULL END)";
                                }

                                cmd.CommandText += " 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(73, 3 + i).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 0));

                                        decimal? dt_nousyukuki = FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 1), ColumnsConfigSet.nameToFormat("dt_nousyukuki_1"));
                                        worksheet1.Cell(74, 3 + i).SetValue(dt_nousyukuki);

                                        TimeSpan nendo = new DateTime(bYear + 1, 4, 1) - new DateTime(bYear, 4, 1);
                                        int nendoDays = nendo.Days;

                                        if(dt_nousyukuki != null) {
                                            worksheet1.Cell(75, 3 + i).SetValue(dt_nousyukuki / nendoDays);
                                        }

                                        worksheet1.Cell(76, 3 + i).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 2), ColumnsConfigSet.nameToFormat("ug_gyousyuuzai_nousyukuki_1")));
                                        worksheet1.Cell(77, 3 + i).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 3), ColumnsConfigSet.nameToFormat("ug_kihoujozai_nousyukuki_1")));

                                        for(int j = 1; j <= 7; j++) {
                                            decimal? ug_exc = FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 3 + j), ColumnsConfigSet.nameToFormat("ug_exc1_nousyukuki_1"));

                                            if(ug_exc != null && ug_exc > 0) {
                                                worksheet1.Cell(77 + j, 3 + i).SetValue(ug_exc);
                                            }
                                        }

                                        for(int j = 1; j <= 5; j++) {
                                            decimal? ov = FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 9 + j * 2), ColumnsConfigSet.nameToFormat("ov_nousyukuki_1"));
                                            decimal? sv = FunctionForDatabase.decConvFromDB(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 10 + j * 2), ColumnsConfigSet.nameToFormat("ov_nousyukuki_1")), ColumnsConfigSet.nameToFormat("oci_nousyukuki_1"));

                                            worksheet1.Cell(83 + j * 2, 3 + i).SetValue(ov);

                                            if(ov != null && sv != null && ov > 0) {
                                                worksheet1.Cell(84 + j * 2, 3 + i).SetValue(sv / ov * 100);
                                            }
                                        }
                                    }
                                }
                            }
                        }

                        using(var cmd = con.CreateCommand()) {
                            cmd.CommandText = "SELECT " +
                                "SUM(ov_nousyuku_output_juuryoku), SUM(ov_nousyuku_output_juuryoku * oc_nousyuku_output_juuryoku / 100), " +
                                "SUM(ov_nousyuku_output_ex), SUM(ov_nousyuku_output_ex * oc_nousyuku_output_ex / 100) " +
                                "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()) {
                                    decimal? ov = FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 0), ColumnsConfigSet.nameToFormat("ov_nousyuku_output_juuryoku"));
                                    decimal? sv = FunctionForDatabase.decConvFromDB(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 1), ColumnsConfigSet.nameToFormat("ov_nousyuku_output_juuryoku")), ColumnsConfigSet.nameToFormat("oc_nousyuku_output_juuryoku"));

                                    worksheet1.Cell(96, 4).SetValue(ov);

                                    if(ov != null && sv != null && ov > 0) {
                                        worksheet1.Cell(98, 4).SetValue(sv / ov * 100);
                                    }

                                    ov = FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 2), ColumnsConfigSet.nameToFormat("ov_nousyuku_output_ex"));
                                    sv = FunctionForDatabase.decConvFromDB(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 3), ColumnsConfigSet.nameToFormat("ov_nousyuku_output_ex")), ColumnsConfigSet.nameToFormat("oc_nousyuku_output_ex"));

                                    worksheet1.Cell(97, 4).SetValue(ov);

                                    if(ov != null && sv != null && ov > 0) {
                                        worksheet1.Cell(99, 4).SetValue(sv / ov * 100);
                                    }
                                }
                            }
                        }

                        //汚泥消化設備
                        string[] sotItems = ccSet[ColumnsConfigSet.nameToIndex("otype_syouka_input")].Items.Split(',');
                        int maxStank = MaxSizeConfig.maxSyoukatank;

                        for(int i = 1; i <= 4; i++) {
                            using(var cmd = con.CreateCommand()) {
                                cmd.CommandText = "SELECT " +
                                    "AVG(d_syouka)";

                                for(int j = 1; j<= maxStank; j++) {
                                    cmd.CommandText += ", SUM(tt_syoukatank_" + j + ")";
                                    cmd.CommandText += ", COUNT(tt_syoukatank_" + j + ")";
                                }

                                cmd.CommandText += ", SUM(ov_syouka_input), " +
                                    "SUM(ov_syouka_input * oc_syouka_input / 100)," +
                                    "SUM(ov_syouka_input * oy_syouka_input / 100)," +
                                    "SUM(v_syoukag_hassei) " +
                                    "FROM suishitsu_kanri " +
                                    "WHERE del_flg IS NULL AND kanrisya='" + kanrisya + "' AND syorijou='" + syorijou + "' AND sokutei_dt>='" + stBYSTR + "' AND sokutei_dt<'" + edBYSTR + "' " +
                                    "AND otype_syouka_input='" + sotItems[i] + "'";

                                using(var reader = cmd.ExecuteReader()) {
                                    if(reader.Read()) {
                                        worksheet1.Cell(103, 3 + i).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 0), ColumnsConfigSet.nameToFormat("d_syouka")));

                                        decimal tt = 0;
                                        decimal tc = 0;

                                        for(int j = 1; j <= maxStank; j++) {
                                            decimal? ttn = FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, -1 + j * 2), ColumnsConfigSet.nameToFormat("tt_syoukatank_1"));
                                            decimal? tcn = FunctionForDatabase.getDecimalOrNull(reader, 0 + j * 2);

                                            if(tcn != null && tcn > 0) {
                                                tt += (decimal)ttn;
                                                tc += (decimal)tcn;
                                            }
                                        }

                                        if(tc > 0) {
                                            worksheet1.Cell(104, 3 + i).SetValue(tt / tc);
                                        }

                                        decimal? ov = FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, maxStank * 2 + 1), ColumnsConfigSet.nameToFormat("ov_syouka_input"));
                                        decimal? sv = FunctionForDatabase.decConvFromDB(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, maxStank * 2 + 2), ColumnsConfigSet.nameToFormat("ov_syouka_input")), ColumnsConfigSet.nameToFormat("oc_syouka_input"));
                                        decimal? yv = FunctionForDatabase.decConvFromDB(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, maxStank * 2 + 3), ColumnsConfigSet.nameToFormat("ov_syouka_input")), ColumnsConfigSet.nameToFormat("oy_syouka_input"));

                                        worksheet1.Cell(105, 3 + i).SetValue(ov);

                                        if(ov != null && sv != null && ov > 0) {
                                            worksheet1.Cell(106, 3 + i).SetValue(sv / ov * 100);
                                        }

                                        if(ov != null && yv != null && ov > 0) {
                                            worksheet1.Cell(107, 3 + i).SetValue(yv / ov * 100);
                                        }

                                        decimal? sg = FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, maxStank * 2 + 4), ColumnsConfigSet.nameToFormat("v_syoukag_hassei"));

                                        if(sg != null && sg > 0) {
                                            worksheet1.Cell(108, 3 + i).SetValue(sg);
                                        }
                                    }
                                }
                            }
                        }

                        using(var cmd = con.CreateCommand()) {
                            cmd.CommandText = "SELECT " +
                                "SUM(ov_syouka_output), " +
                                "SUM(ov_syouka_output * oc_syouka_output / 100), SUM(ov_syouka_output * oy_syouka_output / 100), " +
                                "SUM(v_syoukag_soukaon), SUM(v_syoukag_yogas), SUM(v_syoukag_baiden), SUM(v_syoukag_kanri), SUM(v_syoukag_jikahatsu), SUM(v_syoukag_sonota) " +
                                "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()) {
                                    decimal? ov = FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 0), ColumnsConfigSet.nameToFormat("ov_syouka_output"));
                                    decimal? sv = FunctionForDatabase.decConvFromDB(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 1), ColumnsConfigSet.nameToFormat("ov_syouka_output")), ColumnsConfigSet.nameToFormat("oc_syouka_output"));
                                    decimal? yv = FunctionForDatabase.decConvFromDB(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 2), ColumnsConfigSet.nameToFormat("ov_syouka_output")), ColumnsConfigSet.nameToFormat("oy_syouka_output"));

                                    worksheet1.Cell(110, 4).SetValue(ov);

                                    if(ov != null && sv != null && ov > 0) {
                                        worksheet1.Cell(111, 4).SetValue(sv / ov * 100);
                                    }

                                    if(ov != null && yv != null && ov > 0) {
                                        worksheet1.Cell(112, 4).SetValue(yv / ov * 100);
                                    }

                                    worksheet1.Cell(113, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 3), ColumnsConfigSet.nameToFormat("v_syoukag_soukaon")));
                                    worksheet1.Cell(114, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 4), ColumnsConfigSet.nameToFormat("v_syoukag_yogas")));
                                    worksheet1.Cell(115, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 5), ColumnsConfigSet.nameToFormat("v_syoukag_baiden")));
                                    worksheet1.Cell(116, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 6), ColumnsConfigSet.nameToFormat("v_syoukag_kanri")));
                                    worksheet1.Cell(117, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 7), ColumnsConfigSet.nameToFormat("v_syoukag_jikahatsu")));
                                    worksheet1.Cell(118, 4).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 8), ColumnsConfigSet.nameToFormat("v_syoukag_sonota")));
                                }
                            }
                        }

                        //汚泥脱水設備
                        string[] excDItems = ccSet[ColumnsConfigSet.nameToIndex("type_exc1_dassuiki_1")].Items.Split(',');
                        string[] otypeDItems = ccSet[ColumnsConfigSet.nameToIndex("otype_dassuiki_1")].Items.Split(',');

                        for(int i = 1; i <= MaxSizeConfig.maxNousyukuki; i++) {
                            using(var cmd = con.CreateCommand()) {
                                cmd.CommandText = "SELECT " +
                                    "SUM(CASE WHEN dt_dassuiki_" + i + ">0 THEN 1 ELSE NULL END), SUM(dt_dassuiki_" + i + "), " +
                                    "SUM(ug_gyousyuuzai_dassuiki_" + i + ")";

                                for(int j = 1; j <= 7; j++) {
                                    cmd.CommandText += ", SUM(CASE WHEN type_exc1_dassuiki_" + i + "='" + excDItems[j] + "' THEN ug_exc1_dassuiki_" + i + " ELSE 0 END) + SUM(CASE WHEN type_exc2_dassuiki_" + i + "='" + excDItems[j] + "' THEN ug_exc2_dassuiki_" + i + " ELSE 0 END)";
                                }

                                for(int j = 1; j <= 7; j++) {
                                    cmd.CommandText += ", SUM(CASE WHEN otype_dassuiki_" + i + "='" + otypeDItems[j] + "' THEN ov_dassuiki_" + i + " ELSE NULL END)";
                                    cmd.CommandText += ", SUM(CASE WHEN otype_dassuiki_" + i + "='" + otypeDItems[j] + "' THEN ov_dassuiki_" + i + " * oci_dassuiki_" + i + " / 100 ELSE NULL END)";
                                }

                                cmd.CommandText += " 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(124, 3 + i).SetValue(FunctionForDatabase.getDecimalOrNull(reader, 0));

                                        decimal? dt_dassuiki = FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 1), ColumnsConfigSet.nameToFormat("dt_dassuiki_1"));
                                        worksheet1.Cell(125, 3 + i).SetValue(dt_dassuiki);

                                        TimeSpan nendo = new DateTime(bYear + 1, 4, 1) - new DateTime(bYear, 4, 1);
                                        int nendoDays = nendo.Days;

                                        if(dt_dassuiki != null) {
                                            worksheet1.Cell(126, 3 + i).SetValue(dt_dassuiki / nendoDays);
                                        }

                                        worksheet1.Cell(131, 3 + i).SetValue(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 2), ColumnsConfigSet.nameToFormat("ug_gyousyuuzai_dassuiki_1")));

                                        for(int j = 1; j <= 7; j++) {
                                            decimal? ug_exc = FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 3 + j), ColumnsConfigSet.nameToFormat("ug_exc1_dassuiki_1"));

                                            if(ug_exc != null && ug_exc > 0) {
                                                if(j < 5) {
                                                    worksheet1.Cell(126 + j, 3 + i).SetValue(ug_exc);
                                                }
                                                else {
                                                    worksheet1.Cell(127 + j, 3 + i).SetValue(ug_exc);
                                                }
                                            }
                                        }

                                        for(int j = 1; j <= 7; j++) {
                                            decimal? ov = FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 8 + j * 2), ColumnsConfigSet.nameToFormat("ov_dassuiki_1"));
                                            decimal? sv = FunctionForDatabase.decConvFromDB(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 9 + j * 2), ColumnsConfigSet.nameToFormat("ov_dassuiki_1")), ColumnsConfigSet.nameToFormat("oci_dassuiki_1"));

                                            worksheet1.Cell(133 + j * 2, 3 + i).SetValue(ov);

                                            if(ov != null && sv != null && ov > 0) {
                                                worksheet1.Cell(134 + j * 2, 3 + i).SetValue(sv / ov * 100);
                                            }
                                        }
                                    }
                                }
                            }
                        }

                        using(var cmd = con.CreateCommand()) {
                            cmd.CommandText = "SELECT " +
                                "SUM(ov_dassui_output_enshin), SUM(ov_dassui_output_enshin * owr_dassui_output_enshin / 100), " +
                                "SUM(ov_dassui_output_bp), SUM(ov_dassui_output_bp * owr_dassui_output_bp / 100), " +
                                "SUM(ov_dassui_output_ex), SUM(ov_dassui_output_ex * owr_dassui_output_ex / 100) " +
                                "FROM suishitsu_kanri " +
                                "WHERE del_flg IS NULL AND kanrisya='" + kanrisya + "' AND syorijou='" + syorijou + "' AND sokutei_dt>='" + stBYSTR + "' AND sokutei_dt<'" + edBYSTR + "'";

                            using(var reader = cmd.ExecuteReader()) {
                                if(reader.Read()) {
                                    decimal? ov = FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 0), ColumnsConfigSet.nameToFormat("ov_dassui_output_enshin"));
                                    decimal? wv = FunctionForDatabase.decConvFromDB(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 1), ColumnsConfigSet.nameToFormat("ov_dassui_output_enshin")), ColumnsConfigSet.nameToFormat("owr_dassui_output_enshin"));

                                    worksheet1.Cell(150, 4).SetValue(ov);

                                    if(ov != null && wv != null && ov > 0) {
                                        worksheet1.Cell(153, 4).SetValue(wv / ov * 100);
                                    }

                                    ov = FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 2), ColumnsConfigSet.nameToFormat("ov_dassui_output_bp"));
                                    wv = FunctionForDatabase.decConvFromDB(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 3), ColumnsConfigSet.nameToFormat("ov_dassui_output_bp")), ColumnsConfigSet.nameToFormat("owr_dassui_output_bp"));

                                    worksheet1.Cell(151, 4).SetValue(ov);

                                    if(ov != null && wv != null && ov > 0) {
                                        worksheet1.Cell(154, 4).SetValue(wv / ov * 100);
                                    }

                                    ov = FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 4), ColumnsConfigSet.nameToFormat("ov_dassui_output_ex"));
                                    wv = FunctionForDatabase.decConvFromDB(FunctionForDatabase.decConvFromDB(FunctionForDatabase.getDecimalOrNull(reader, 5), ColumnsConfigSet.nameToFormat("ov_dassui_output_ex")), ColumnsConfigSet.nameToFormat("owr_dassui_output_ex"));

                                    worksheet1.Cell(152, 4).SetValue(ov);

                                    if(ov != null && wv != null && ov > 0) {
                                        worksheet1.Cell(155, 4).SetValue(wv / ov * 100);
                                    }
                                }
                            }
                        }
                    }
                }
                else {
                    throw new Exception();
                }

                return;
            }
        }

    }
}
