// 下水データ管理システム
// 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.Drawing;
using System.IO;
using System.Reflection;
using System.Windows.Forms;

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


        List<ColumnConfig> ccSet = ColumnsConfigSet.getSet;
        List<KanrisyaSyorijou> kanrisyaSyorijouList = new List<KanrisyaSyorijou>();

        Color sortColumnHeaderBackColorASC = Color.FromArgb(230, 230, 162);
        Color sortColumnHeaderBackColorDESC = Color.FromArgb(198, 198, 230);
        Color sortColumnBackColorASC = Color.FromArgb(255, 255, 180);
        Color sortColumnBackColorDESC = Color.FromArgb(220, 220, 255);
        Color correctColor = Color.FromArgb(0, 0, 255);
        Color errorColor = Color.FromArgb(255, 0, 0);

        public bool finInport { get; set; } = false;




        //ロード時イベント
        private void Form6ImportFile_Load(object sender, EventArgs e) {
            dataGridViewTempInitialize();
            dataGridViewSeitenInitialize();
        }




        //dataGridViewTemp準備
        private void dataGridViewTempInitialize() {
            //DataGirdViewのプロパティDoubleBufferedをtrueに設定
            Type dataGridViewType = typeof(DataGridView);
            PropertyInfo dataGridViewProperty = dataGridViewType.GetProperty("DoubleBuffered", System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.NonPublic);
            dataGridViewProperty.SetValue(dataGridViewTemp, true, null);
            dataGridViewTemp.EnableHeadersVisualStyles = false;
            dataGridViewTemp.ColumnHeadersHeight = 34;

            //列作成
            DataGridViewTextBoxColumn[] c = new DataGridViewTextBoxColumn[ccSet.Count];

            int i = 0;
            foreach(ColumnConfig cc in ccSet) {
                c[i] = new DataGridViewTextBoxColumn();
                c[i].AutoSizeMode = DataGridViewAutoSizeColumnMode.None;
                c[i].Resizable = DataGridViewTriState.False;
                c[i].ReadOnly = true;
                c[i].HeaderText = cc.HeaderText;

                if(!string.IsNullOrEmpty(cc.unit)) {
                    c[i].HeaderText += "\n" + cc.unit;
                }
                else {
                    c[i].HeaderCell.Style.WrapMode = DataGridViewTriState.False;
                }

                c[i].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;

                c[i].Name = cc.Name;
                c[i].SortMode = DataGridViewColumnSortMode.NotSortable;

                if(!cc.Visible || cc.calculatedDecimal || cc.calculatedString) {
                    c[i].Visible = false;
                }
                else {
                    c[i].Visible = true;
                }

                c[i].DefaultCellStyle.Alignment = cc.Alignment;
                c[i].DefaultCellStyle.Format = cc.Format;
                c[i].DefaultCellStyle.NullValue = null;

                Type t = Type.GetType("System.String");

                if(cc.Format == "") {
                    t = Type.GetType("System.String");
                }
                else if(cc.Format == "N0") {
                    t = Type.GetType("System.Int32");
                }
                else if(cc.Format == "N1") {
                    t = Type.GetType("System.Decimal");
                }
                else if(cc.Format == "N2") {
                    t = Type.GetType("System.Decimal");
                }
                else if(cc.Format == "N3") {
                    t = Type.GetType("System.Decimal");
                }
                else if(cc.Format == "N4") {
                    t = Type.GetType("System.Decimal");
                }
                else if(cc.Format == "N5") {
                    t = Type.GetType("System.Decimal");
                }
                else if(cc.Format.Substring(0, 10) == "yyyy/MM/dd") {
                    t = Type.GetType("System.DateTime");
                }

                c[i].ValueType = t;
                dataGridViewTemp.Columns.Add(c[i]);
                i++;
            }

            //列幅調整
            dataGridViewTemp.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.ColumnHeader);

            foreach(ColumnConfig cc in ccSet) {
                if(cc.Width != 0) {
                    dataGridViewTemp.Columns[cc.Name].Width = cc.Width;
                }
            }

            dataGridViewTemp.Columns["kanrisya"].Visible = true;
            dataGridViewTemp.Columns["syorijou"].Visible = true;
            dataGridViewTemp.Columns["input_dt"].Visible = false;

            //ソートモード設定
            foreach(DataGridViewColumn column in dataGridViewTemp.Columns) {
                column.SortMode = DataGridViewColumnSortMode.Programmatic;
            }

            //測定日列まで固定
            dataGridViewTemp.Columns["sokutei_dt"].Frozen = true;
        }




        //「列の FillWeight 値の合計が 65535 を超えることはできません」例外の対処
        private void dataGridViewTemp_ColumnAdded(object sender, DataGridViewColumnEventArgs e) {
            e.Column.FillWeight = 1;
        }

        
        
        
        //dataGridViewTemp列ヘッダークリックイベント
        private void dataGridView1_ColumnHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e) {
            if(e.Button == MouseButtons.Left) {
                DataGridViewColumn newSortColumn = dataGridViewTemp.Columns[e.ColumnIndex];
                DataGridViewColumn oldSortColumn = dataGridViewTemp.SortedColumn;

                bool sortASC;

                if(oldSortColumn != null) {
                    if(oldSortColumn == newSortColumn) {
                        if(dataGridViewTemp.SortOrder == SortOrder.Ascending) {
                            sortASC = false;
                        }
                        else {
                            sortASC = true;
                        }
                    }
                    else {
                        sortASC = true;
                    }
                }
                else {
                    sortASC = true;
                }

                dataGridViewSort(dataGridViewTemp, newSortColumn, sortASC);
            }
        }




        //dataGridViewTempのソート
        private void dataGridViewSort(DataGridView e, DataGridViewColumn sortColumn, bool sortASC) {
            if(sortASC) {
                e.Sort(sortColumn, ListSortDirection.Ascending);
                sortColumn.HeaderCell.Style.BackColor = sortColumnHeaderBackColorASC;
                sortColumn.DefaultCellStyle.BackColor = sortColumnBackColorASC;
            }
            else {
                e.Sort(sortColumn, ListSortDirection.Descending);
                sortColumn.HeaderCell.Style.BackColor = sortColumnHeaderBackColorDESC;
                sortColumn.DefaultCellStyle.BackColor = sortColumnBackColorDESC;
            }

            foreach(DataGridViewColumn column in e.Columns) {
                if(column != sortColumn) {
                    column.HeaderCell.Style.BackColor = e.ColumnHeadersDefaultCellStyle.BackColor;
                    column.DefaultCellStyle.BackColor = e.DefaultCellStyle.BackColor;
                }
            }
        }




        //dataGridViewTemp行描写前イベント
        private void dataGridViewTemp_RowPrePaint(object sender, DataGridViewRowPrePaintEventArgs e) {
            //選択状態にさせない
            dataGridViewTemp.CurrentCell = null;
        }




        //dataGridViewSeiten行描写前イベント
        private void dataGridViewSeiten_RowPrePaint(object sender, DataGridViewRowPrePaintEventArgs e) {
            //選択状態にさせない
            dataGridViewSeiten.CurrentCell = null;
        }




        //dataGridViewSeiten準備
        private void dataGridViewSeitenInitialize() {
            //DataGirdViewのプロパティDoubleBufferedをtrueに設定
            Type dataGridViewType = typeof(DataGridView);
            PropertyInfo dataGridViewProperty = dataGridViewType.GetProperty("DoubleBuffered", System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.NonPublic);
            dataGridViewProperty.SetValue(dataGridViewSeiten, true, null);
            dataGridViewSeiten.EnableHeadersVisualStyles = false;

            //列作成
            DataGridViewTextBoxColumn c1 = new DataGridViewTextBoxColumn();
            c1 = new DataGridViewTextBoxColumn();
            c1.AutoSizeMode = DataGridViewAutoSizeColumnMode.None;
            c1.ReadOnly = true;
            c1.HeaderText = "管理者";
            c1.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
            c1.Name = "kanrisya";
            c1.SortMode = DataGridViewColumnSortMode.NotSortable;
            c1.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft;
            c1.DefaultCellStyle.NullValue = null;
            c1.ValueType = Type.GetType("System.String");
            c1.Width = 50;
            dataGridViewSeiten.Columns.Add(c1);

            DataGridViewTextBoxColumn c2 = new DataGridViewTextBoxColumn();
            c2 = new DataGridViewTextBoxColumn();
            c2.AutoSizeMode = DataGridViewAutoSizeColumnMode.None;
            c2.ReadOnly = true;
            c2.HeaderText = "処理場";
            c2.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
            c2.Name = "syorijou";
            c2.SortMode = DataGridViewColumnSortMode.NotSortable;
            c2.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft;
            c2.DefaultCellStyle.NullValue = null;
            c2.ValueType = Type.GetType("System.String");
            c2.Width = 57;
            dataGridViewSeiten.Columns.Add(c2);

            for(int i = 0; i < 3; i++) {
                string dayName;

                if(i == 0) {
                    dayName = "当日";
                }
                else {
                    dayName = i.ToString() + "日前";
                }

                DataGridViewTextBoxColumn newColumn = new DataGridViewTextBoxColumn();
                newColumn = new DataGridViewTextBoxColumn();
                newColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.None;
                newColumn.ReadOnly = true;
                newColumn.HeaderText = dayName;
                newColumn.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
                newColumn.Name = "lluryouDay" + i.ToString();
                newColumn.SortMode = DataGridViewColumnSortMode.NotSortable;
                newColumn.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                newColumn.DefaultCellStyle.NullValue = null;
                newColumn.ValueType = Type.GetType("System.String");
                newColumn.Width = 48;
                dataGridViewSeiten.Columns.Add(newColumn);
            }
        }




        //ファイル選択ボタンクリックイベント
        private void button1_Click(object sender, EventArgs e) {
            OpenFileDialog OpenFileDialog1 = new OpenFileDialog();

            OpenFileDialog1.InitialDirectory = @"%USERPROFILE%\Desktop";
            OpenFileDialog1.Filter = "Excel ブック(*.xlsx)|*.xlsx";
            OpenFileDialog1.FilterIndex = 1;
            OpenFileDialog1.Title = "インポートファイル選択";
            OpenFileDialog1.RestoreDirectory = true;
            OpenFileDialog1.CheckFileExists = true;
            OpenFileDialog1.CheckPathExists = true;

            //ファイル選択ダイアログの表示
            if(OpenFileDialog1.ShowDialog() == DialogResult.OK) {
                dataGridViewTemp.Rows.Clear();
                dataGridViewSeiten.Rows.Clear();
                label1.Text = "ファイル：" + OpenFileDialog1.FileName;
                button2.Enabled = false;

                if(string.IsNullOrEmpty(OpenFileDialog1.FileName) || !File.Exists(OpenFileDialog1.FileName)) {
                    return;
                }

                //ファイル読込
                FormA2ProgressSplash progressSplash1 = new FormA2ProgressSplash();
                progressSplash1.Owner = this;
                progressSplash1.Show();
                progressSplash1.Refresh();
                progressSplash1.ProgressMessage = "ファイル読込準備中";

                dataGridViewTemp.SuspendLayout();
                dataGridViewSeiten.SuspendLayout();

                try {
                    using(FileStream fileStream1 = new FileStream(OpenFileDialog1.FileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) {
                        using(XLWorkbook workbook1 = new XLWorkbook(fileStream1, XLEventTracking.Disabled)) {
                            //データのシート
                            IXLWorksheet worksheet0;

                            if(!workbook1.TryGetWorksheet("data", out worksheet0)) {
                                throw new Exception("このエクセルファイルにはシート「data」がありません\nデータが入ったシートの名前は「data」にしてください");
                            }

                            using(IXLWorksheet worksheet1 = worksheet0) {
                                //項目リスト取得
                                int lastColumnNo = worksheet1.LastColumnUsed().ColumnNumber();
                                string[] headerText = new string[lastColumnNo];
                                int[] headerccIndex = new int[lastColumnNo];

                                for(int i = 0; i < lastColumnNo; i++) {
                                    headerText[i] = worksheet1.Cell(1, i + 1).Value.ToString();
                                    headerccIndex[i] = ColumnsConfigSet.headerTextAndUnitToIndex(headerText[i]);
                                }

                                //ファイル内容チェック
                                int sokutei_dtColumnNo = Array.IndexOf(headerText, "測定日") + 1;

                                if(sokutei_dtColumnNo == 0) {
                                    throw new Exception("このエクセルファイルには測定日の列がありません");
                                }

                                int kanrisyaColumnNo = Array.IndexOf(headerText, "管理者") + 1;

                                if(kanrisyaColumnNo == 0) {
                                    throw new Exception("このエクセルファイルには管理者の列がありません");
                                }

                                int syorijouColumnNo = Array.IndexOf(headerText, "処理場") + 1;

                                if(syorijouColumnNo == 0) {
                                    throw new Exception("このエクセルファイルには処理場の列がありません");
                                }

                                int lastRowNo = worksheet1.Column(sokutei_dtColumnNo).LastCellUsed().Address.RowNumber;
                                List<DateTime> settedDate = new List<DateTime>();

                                decimal stepPer100 = 100M / (lastRowNo - 1M);
                                decimal setpIntegrate = 0;

                                //1行づつ読込
                                for(int j = 2; j <= lastRowNo; j++) {
                                    setpIntegrate += stepPer100;

                                    if(setpIntegrate >= 10) {
                                        progressSplash1.ProgressMessage = "データファイル読込中（" + (Math.Round((j - 1M) / (lastRowNo - 1M) * 10M, 0) * 10).ToString() + "%）";
                                        Application.DoEvents();
                                        setpIntegrate = 0;
                                    }

                                    string sokutei_dtSTR = worksheet1.Cell(j, sokutei_dtColumnNo).Value.ToString();

                                    DateTime sokutei_dt;
                                    string settedDateSTR = "";
                                    string kanrisya = "";
                                    string syorijou = "";

                                    if(DateTime.TryParse(sokutei_dtSTR, out sokutei_dt) && worksheet1.Cell(j, kanrisyaColumnNo).Value.ToString() != "" && worksheet1.Cell(j, syorijouColumnNo).Value.ToString() != "") {
                                        //測定日の時刻は全て00:00:00とする
                                        settedDateSTR = sokutei_dt.ToString("yyyy-MM-dd") + " 00:00:00";
                                        sokutei_dt = DateTime.Parse(settedDateSTR);

                                        dataGridViewTemp.Rows.Add();
                                        int newRowNo = dataGridViewTemp.Rows.Count - 1;

                                        dataGridViewTemp.Rows[newRowNo].Cells["sokutei_dt"].Value = sokutei_dt;

                                        for(int i = 0; i < lastColumnNo; i++) {
                                            if(i != sokutei_dtColumnNo - 1) {
                                                int ccIndex = headerccIndex[i];

                                                if(ccIndex > -1) {
                                                    bool ccCalculatedDecimal = ccSet[ccIndex].calculatedDecimal;
                                                    bool ccCalculatedString = ccSet[ccIndex].calculatedString;
                                                    string ccName = ccSet[ccIndex].Name;
                                                    string ccFormat = ccSet[ccIndex].Format;
                                                    string ccInputType = ccSet[ccIndex].inputType;
                                                    string cellValueSTR = worksheet1.Cell(j, i + 1).Value.ToString();
                                                    decimal cellValueDEC;

                                                    if(ccCalculatedDecimal || ccCalculatedString) {
                                                        dataGridViewTemp.Rows[newRowNo].Cells[ccName].Value = null;
                                                    }
                                                    else if(ccFormat == "N0" || ccFormat == "N1" || ccFormat == "N2" || ccFormat == "N3" || ccFormat == "N4" || ccFormat == "N5") {
                                                        if(String.IsNullOrEmpty(cellValueSTR)) {
                                                            dataGridViewTemp.Rows[newRowNo].Cells[ccName].Value = null;
                                                        }
                                                        else if(decimal.TryParse(cellValueSTR, out cellValueDEC)) {
                                                            cellValueDEC = ColumnsConfigSet.roundingByName(cellValueDEC, ccName);

                                                            if(cellValueDEC < ccSet[ccIndex].Minimum) {
                                                                dataGridViewTemp.Rows[newRowNo].Cells[ccName].Value = cellValueSTR;
                                                                dataGridViewTemp.Rows[newRowNo].Cells[ccName].Style.BackColor = errorColor;
                                                                throw new Exception("数値データの列の値が下限以下の行があります\nセル：" + FunctionForExcel.columnNoToLetter(i + 1) + j.ToString() + "\n値：" + cellValueSTR);
                                                            }
                                                            else if(cellValueDEC > ccSet[ccIndex].Maximum) {
                                                                dataGridViewTemp.Rows[newRowNo].Cells[ccName].Value = cellValueSTR;
                                                                dataGridViewTemp.Rows[newRowNo].Cells[ccName].Style.BackColor = errorColor;
                                                                throw new Exception("数値データの列の値が上限以上の行があります\nセル：" + FunctionForExcel.columnNoToLetter(i + 1) + j.ToString() + "\n値：" + cellValueSTR);
                                                            }

                                                            dataGridViewTemp.Rows[newRowNo].Cells[ccName].Value = cellValueDEC;
                                                        }
                                                        else {
                                                            dataGridViewTemp.Rows[newRowNo].Cells[ccName].Value = cellValueSTR;
                                                            dataGridViewTemp.Rows[newRowNo].Cells[ccName].Style.BackColor = errorColor;
                                                            throw new Exception("数値データの列の値が数値でない行があります\nセル：" + FunctionForExcel.columnNoToLetter(i + 1) + j.ToString() + "\n値：" + cellValueSTR);
                                                        }
                                                    }
                                                    else if(ccInputType == "ComboBox") {
                                                        string[] ccItem = ccSet[ccIndex].Items.Split(',');

                                                        if(Array.IndexOf(ccItem, cellValueSTR) > -1) {
                                                            dataGridViewTemp.Rows[newRowNo].Cells[ccName].Value = cellValueSTR;
                                                        }
                                                        else {
                                                            dataGridViewTemp.Rows[newRowNo].Cells[ccName].Value = cellValueSTR;
                                                            dataGridViewTemp.Rows[newRowNo].Cells[ccName].Style.BackColor = errorColor;
                                                            throw new Exception("選択データの列に選択対象外の値が入っている行があります\nセル：" + FunctionForExcel.columnNoToLetter(i + 1) + j.ToString() + "\n値：" + cellValueSTR);
                                                        }

                                                    }
                                                    else {
                                                        dataGridViewTemp.Rows[newRowNo].Cells[ccName].Value = cellValueSTR;

                                                        if(ccName == "kanrisya") {
                                                            kanrisya = cellValueSTR;
                                                        }
                                                        else if(ccName == "syorijou") {
                                                            syorijou = cellValueSTR;
                                                        }
                                                    }
                                                }
                                            }
                                        }
                                    }

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

                                        using(var cmd = con.CreateCommand()) {

                                            cmd.CommandText = "SELECT * FROM suishitsu_kanri WHERE kanrisya = '" + kanrisya + "' AND syorijou = '" + syorijou + "' AND sokutei_dt = '" + settedDateSTR + "' AND del_flg IS NULL;";

                                            using(var reader = cmd.ExecuteReader()) {
                                                if(reader.HasRows) {
                                                    dataGridViewTemp.Rows[dataGridViewTemp.Rows.Count - 1].Cells["sokutei_dt"].Style.BackColor = errorColor;
                                                    throw new Exception("管理者、処理場、測定日が同じ既存のデータがシステム内に存在します\n測定日：" + sokutei_dt.ToString("yyyy-MM-dd"));
                                                }
                                            }
                                        }
                                    }

                                    if(settedDate.Contains(sokutei_dt)) {
                                        dataGridViewTemp.Rows[dataGridViewTemp.Rows.Count - 1].Cells["sokutei_dt"].Style.BackColor = errorColor;
                                        throw new Exception("管理者、処理場、測定日が同じ既存のデータがファイル内に存在します\n測定日：" + sokutei_dt.ToString("yyyy-MM-dd"));
                                    }
                                    else {
                                        settedDate.Add(sokutei_dt);
                                    }
                                }

                                if(dataGridViewTemp.RowCount > 0) {
                                    label2.Text = string.Format("インポートできます（{0}行）　インポートボタンを押してください", dataGridViewTemp.RowCount);
                                    label2.ForeColor = correctColor;
                                    button2.Enabled = true;
                                }
                                else {
                                    throw new Exception("インポートできる行がありません");
                                }
                            }

                            //設定のシート
                            IXLWorksheet worksheet2;

                            if(workbook1.TryGetWorksheet("uten_judge_uryou_setting", out worksheet2)) {
                                using(worksheet2) {
                                    int lastRowNo = worksheet2.Column(1).LastCellUsed().Address.RowNumber;

                                    for(int j = 2; j <= lastRowNo; j++) {
                                        string utenKanrisya = worksheet2.Cell(j, 1).Value.ToString();
                                        string utenSyorijou = worksheet2.Cell(j, 2).Value.ToString();
                                        string utenLLUryouDay0STR = worksheet2.Cell(j, 3).Value.ToString();
                                        string utenLLUryouDay1STR = worksheet2.Cell(j, 4).Value.ToString();
                                        string utenLLUryouDay2STR = worksheet2.Cell(j, 5).Value.ToString();

                                        decimal utenLLUryouDay0;
                                        decimal utenLLUryouDay1;
                                        decimal utenLLUryouDay2;

                                        if(utenLLUryouDay0STR != "") {
                                            if(!decimal.TryParse(utenLLUryouDay0STR, out utenLLUryouDay0)) {
                                                continue;
                                            }
                                        }

                                        if(utenLLUryouDay1STR != "") {
                                            if(!decimal.TryParse(utenLLUryouDay1STR, out utenLLUryouDay1)) {
                                                continue;
                                            }
                                        }

                                        if(utenLLUryouDay2STR != "") {
                                            if(!decimal.TryParse(utenLLUryouDay2STR, out utenLLUryouDay2)) {
                                                continue;
                                            }
                                        }

                                        dataGridViewSeiten.Rows.Add(new string[] { utenKanrisya, utenSyorijou, utenLLUryouDay0STR, utenLLUryouDay1STR, utenLLUryouDay2STR });
                                    }
                                }
                            }
                        }
                    }

                    DataGridViewColumn sortColumn = dataGridViewTemp.Columns["sokutei_dt"];
                    dataGridViewSort(dataGridViewTemp, sortColumn, true);
                    dataGridViewTemp.CurrentCell = null;
                }
                catch(FormatException ex) {
                    if(ex.GetType().ToString() == "System.IO.FileFormatException") {
                        label2.Text = "インポートできません。ファイルを修正するか他のファイルを選択してください";
                        label2.ForeColor = errorColor;
                        MessageBox.Show("有効なエクセルファイルではありません");
                    }
                    else {
                        label2.Text = "インポートできません。ファイルを修正するか他のファイルを選択してください";
                        label2.ForeColor = errorColor;
                        MessageBox.Show(ex.Message);
                    }

                }
                catch(Exception ex) {
                    label2.Text = "インポートできません。ファイルを修正するか他のファイルを選択してください";
                    label2.ForeColor = errorColor;
                    MessageBox.Show(ex.Message);
                }
                finally {
                    dataGridViewSeiten.ResumeLayout();
                    dataGridViewTemp.ResumeLayout();
                    progressSplash1.Close();
                    progressSplash1.Dispose();
                    this.Activate();
                }
            }
        }




        //インポートボタンクリックイベント
        private void button2_Click(object sender, EventArgs e) {
            //設定のシート
            foreach(DataGridViewRow dgvSRow in dataGridViewSeiten.Rows) {
                string sKanrisya = dgvSRow.Cells["kanrisya"].Value.ToString();
                string sSyorijou = dgvSRow.Cells["syorijou"].Value.ToString();
                string lluryouDay0STR = dgvSRow.Cells["lluryouDay0"].Value.ToString();
                string lluryouDay1STR = dgvSRow.Cells["lluryouDay1"].Value.ToString();
                string lluryouDay2STR = dgvSRow.Cells["lluryouDay2"].Value.ToString();

                bool defFlg = false;

                if(SeitenConfigSet.getUtenLLUryouDay0(sKanrisya, sSyorijou) == null) {
                    //元の設定がNULLの場合、NULLか
                    if(!string.IsNullOrEmpty(lluryouDay0STR)) {
                        defFlg = true;
                    }
                }
                else {
                    //元の設定がNULLでない場合、同じ値か
                    decimal lluryouDay;

                    if(!decimal.TryParse(lluryouDay0STR, out lluryouDay)) {
                        defFlg = true;
                    }
                    else {
                        if(lluryouDay != SeitenConfigSet.getUtenLLUryouDay0(sKanrisya, sSyorijou)) {
                            defFlg = true;
                        }
                    }
                }

                if(SeitenConfigSet.getUtenLLUryouDay1(sKanrisya, sSyorijou) == null) {
                    //元の設定がNULLの場合、NULLか
                    if(!string.IsNullOrEmpty(lluryouDay1STR)) {
                        defFlg = true;
                    }
                }
                else {
                    //元の設定がNULLでない場合、同じ値か
                    decimal lluryouDay;

                    if(!decimal.TryParse(lluryouDay1STR, out lluryouDay)) {
                        defFlg = true;
                    }
                    else {
                        if(lluryouDay != SeitenConfigSet.getUtenLLUryouDay1(sKanrisya, sSyorijou)) {
                            defFlg = true;
                        }
                    }
                }

                if(SeitenConfigSet.getUtenLLUryouDay2(sKanrisya, sSyorijou) == null) {
                    //元の設定がNULLの場合、NULLか
                    if(!string.IsNullOrEmpty(lluryouDay2STR)) {
                        defFlg = true;
                    }
                }
                else {
                    //元の設定がNULLでない場合、同じ値か
                    decimal lluryouDay;

                    if(!decimal.TryParse(lluryouDay2STR, out lluryouDay)) {
                        defFlg = true;
                    }
                    else {
                        if(lluryouDay != SeitenConfigSet.getUtenLLUryouDay2(sKanrisya, sSyorijou)) {
                            defFlg = true;
                        }
                    }
                }

                //晴天判定設定が変更になる場合、確認
                if(defFlg) {
                    if(MessageBox.Show(sKanrisya + "：" + sSyorijou + "の\n晴天判定設定を変更してよろしいですか\n変更する場合、晴天日は過去も含めて全て再判定されます", "", MessageBoxButtons.OKCancel) == DialogResult.Cancel) {
                        return;
                    }
                }
            }

            button1.Enabled = false;
            button2.Enabled = false;
            button3.Enabled = false;

            int s = 0;
            foreach(DataGridViewRow dgvSRow in dataGridViewSeiten.Rows) {
                string sKanrisya = dgvSRow.Cells["kanrisya"].Value.ToString();
                string sSyorijou = dgvSRow.Cells["syorijou"].Value.ToString();
                string lluryouDay0STR = dgvSRow.Cells["lluryouDay0"].Value.ToString();
                string lluryouDay1STR = dgvSRow.Cells["lluryouDay1"].Value.ToString();
                string lluryouDay2STR = dgvSRow.Cells["lluryouDay2"].Value.ToString();

                string ccFormat = ColumnsConfigSet.nameToFormat("uryou");

                decimal? uten_ll_uryou_day0 = null;

                if(!String.IsNullOrEmpty(lluryouDay0STR)) {
                    decimal lluryouDay;

                    if(decimal.TryParse(lluryouDay0STR, out lluryouDay)) {
                        uten_ll_uryou_day0 = FunctionForDatabase.decConvForDB(lluryouDay, ccFormat);
                    }
                }

                decimal? uten_ll_uryou_day1 = null;

                if(!String.IsNullOrEmpty(lluryouDay1STR)) {
                    decimal lluryouDay;

                    if(decimal.TryParse(lluryouDay1STR, out lluryouDay)) {
                        uten_ll_uryou_day1 = FunctionForDatabase.decConvForDB(lluryouDay, ccFormat);
                    }
                }

                decimal? uten_ll_uryou_day2 = null;

                if(!String.IsNullOrEmpty(lluryouDay2STR)) {
                    decimal lluryouDay;

                    if(decimal.TryParse(lluryouDay2STR, out lluryouDay)) {
                        uten_ll_uryou_day2 = FunctionForDatabase.decConvForDB(lluryouDay, ccFormat);
                    }
                }

                int changedColumnNo = 0;

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

                    try {
                        //最初にUPDATEを試行
                        using(var cmd = con.CreateCommand()) {
                            string sql1 = "UPDATE uten_judge_uryou_setting SET ";
                            string sql2 = "uten_ll_uryou_day0 = @uten_ll_uryou_day0, uten_ll_uryou_day1 = @uten_ll_uryou_day1, uten_ll_uryou_day2 = @uten_ll_uryou_day2 ";
                            string sql3 = "WHERE kanrisya = @kanrisya AND syorijou = @syorijou;";

                            cmd.CommandText = sql1 + sql2 + sql3;

                            cmd.Parameters.Add(new SQLiteParameter("@kanrisya", sKanrisya));
                            cmd.Parameters.Add(new SQLiteParameter("@syorijou", sSyorijou));
                            cmd.Parameters.Add(new SQLiteParameter("@uten_ll_uryou_day0", uten_ll_uryou_day0));
                            cmd.Parameters.Add(new SQLiteParameter("@uten_ll_uryou_day1", uten_ll_uryou_day1));
                            cmd.Parameters.Add(new SQLiteParameter("@uten_ll_uryou_day2", uten_ll_uryou_day2));

                            cmd.ExecuteNonQuery();
                        }

                        using(var cmd = con.CreateCommand()) {
                            cmd.CommandText = "SELECT CHANGES();";

                            using(var reader = cmd.ExecuteReader()) {
                                while(reader.Read()) {
                                    changedColumnNo = reader.GetInt32(0);
                                }
                            }
                        }

                        //UPDATEで更新件数が0の場合INSERT
                        if(changedColumnNo == 0) {
                            using(var cmd = con.CreateCommand()) {
                                string sql1 = "INSERT INTO uten_judge_uryou_setting(";
                                string sql2 = "kanrisya, syorijou, uten_ll_uryou_day0, uten_ll_uryou_day1, uten_ll_uryou_day2";
                                string sql3 = ") VALUES(";
                                string sql4 = "@kanrisya, @syorijou, @uten_ll_uryou_day0, @uten_ll_uryou_day1, @uten_ll_uryou_day2";
                                string sql5 = ");";

                                cmd.CommandText = sql1 + sql2 + sql3 + sql4 + sql5;

                                cmd.Parameters.Add(new SQLiteParameter("@kanrisya", sKanrisya));
                                cmd.Parameters.Add(new SQLiteParameter("@syorijou", sSyorijou));
                                cmd.Parameters.Add(new SQLiteParameter("@uten_ll_uryou_day0", uten_ll_uryou_day0));
                                cmd.Parameters.Add(new SQLiteParameter("@uten_ll_uryou_day1", uten_ll_uryou_day1));
                                cmd.Parameters.Add(new SQLiteParameter("@uten_ll_uryou_day2", uten_ll_uryou_day2));

                                cmd.ExecuteNonQuery();
                            }
                        }
                    }
                    catch(Exception ex) {
                        MessageBox.Show(ex.Message);
                    }
                    finally {
                        con.Close();
                    }
                }

                s++;
            }

            if(s > 0) {
                //晴天日設定用リストのリフレッシュ
                SeitenConfigSet.presetSeitenConfigSet();
            }

            //データのシート
            using(var con = new SQLiteConnection(DataBaseConfig.ConnectionString)) {
                con.Open();

                try {
                    //削除フラグのあるレコードをDELETE
                    using(var cmd = con.CreateCommand()) {
                        cmd.CommandText = "DELETE FROM suishitsu_kanri WHERE del_flg = 1;";
                        cmd.ExecuteNonQuery();
                    }

                    //バキューム
                    using(var cmd = con.CreateCommand()) {
                        cmd.CommandText = "VACUUM;";
                        cmd.ExecuteNonQuery();
                    }
                }
                catch(Exception ex) {
                    MessageBox.Show(ex.Message);
                    return;
                }
                finally {
                    con.Close();
                }
            }

            string input_dtSTR = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

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

                using(var transaction = con.BeginTransaction()) {
                    //DB追加処理
                    FormA2ProgressSplash progressSplash1 = new FormA2ProgressSplash();
                    progressSplash1.Owner = this;
                    progressSplash1.Show();
                    progressSplash1.Refresh();
                    progressSplash1.ProgressMessage = "データインポート準備中";

                    try {
                        //1行づつ追加
                        decimal stepPer100 = 100M / (dataGridViewTemp.RowCount);
                        decimal setpIntegrate = 0;

                        int j = 0;
                        foreach(DataGridViewRow dgvTRow in dataGridViewTemp.Rows) {
                            setpIntegrate += stepPer100;
                            j++;

                            if(setpIntegrate >= 10) {
                                progressSplash1.ProgressMessage = "データ格納命令発行中（" + (Math.Round((decimal)j / dataGridViewTemp.RowCount * 10M, 0) * 10).ToString() + "%）";
                                Application.DoEvents();
                                setpIntegrate = 0;
                            }

                            //管理者・処理場・測定日のみで挿入しレコードのidを取得
                            int id = -1;

                            using(var cmd = con.CreateCommand()) {
                                cmd.CommandText = "INSERT INTO suishitsu_kanri(kanrisya, syorijou, sokutei_dt, input_dt) VALUES(@kanrisya, @syorijou, @sokutei_dt, @input_dt);";
                                
                                cmd.Parameters.Add(new SQLiteParameter("@kanrisya", dgvTRow.Cells["kanrisya"].Value));
                                cmd.Parameters.Add(new SQLiteParameter("@syorijou", dgvTRow.Cells["syorijou"].Value));
                                cmd.Parameters.Add(new SQLiteParameter("@sokutei_dt", dgvTRow.Cells["sokutei_dt"].Value));
                                cmd.Parameters.Add(new SQLiteParameter("@input_dt", input_dtSTR));

                                cmd.ExecuteNonQuery();
                            }

                            using(var cmd = con.CreateCommand()) {
                                cmd.CommandText = "SELECT id FROM suishitsu_kanri WHERE ROWID = last_insert_rowid();";

                                using(var reader = cmd.ExecuteReader()) {
                                    if(reader.Read()) {
                                        if(!reader.IsDBNull(0)) {
                                            id = reader.GetInt32(0);
                                        }
                                    }
                                }
                            }

                            //取得したidのレコードにデータを記入
                            if(id > 0) {
                                int syncColumnNo = 900;

                                for(int i = 0; i <= (int)Math.Floor((decimal)ccSet.Count / syncColumnNo); i++) {
                                    int changedColumn = 0;

                                    using(var cmd = con.CreateCommand()) {
                                        string sql1 = "UPDATE suishitsu_kanri SET ";
                                        string sql2 = "";
                                        string sql3 = " WHERE id = @id;";

                                        for(int k = 1; k <= syncColumnNo; k++) {
                                            if(i * syncColumnNo + k >= ccSet.Count) {
                                                break;
                                            }

                                            ColumnConfig cc = ccSet[i * syncColumnNo + k];
                                            string valueSTR = null;

                                            if(cc.Name != "id" && cc.Name != "del_flg" && cc.Name != "kanrisya" && cc.Name != "syorijou" && cc.Name != "sokutei_dt" && cc.Name != "input_dt" && cc.Name != "seiten") {
                                                if(cc.calculatedDecimal) {
                                                    //二次データ(数値)
                                                    string newValueSTR = null;
                                                    decimal? newValue = Calculate2ndValue.calc2ndDecimal(cc.Name, dgv1Row: dgvTRow, ccSet: null);

                                                    newValue = FunctionForDatabase.decConvForDB(newValue, cc.Format);

                                                    if(newValue != null) {
                                                        newValueSTR = newValue.ToString();
                                                    }

                                                    valueSTR = newValueSTR;
                                                }
                                                else if(cc.calculatedString) {
                                                    //二次データ(文字)


                                                }
                                                else if(dgvTRow.Cells[cc.Name].Value != null) {
                                                    //一次データ
                                                    string cellValueSTR = dgvTRow.Cells[cc.Name].Value.ToString();

                                                    if(!string.IsNullOrEmpty(cellValueSTR)) {
                                                        if(cc.inputType == "DateTimePicker") {
                                                            string newValueSTR = null;

                                                            if(!string.IsNullOrEmpty(cellValueSTR)) {
                                                                DateTime ccDateTime;

                                                                if(DateTime.TryParse(cellValueSTR, out ccDateTime)) {
                                                                    if(cc.Format == "yyyy/MM/dd ddd") {
                                                                        newValueSTR = ccDateTime.ToString("yyyy-MM-dd") + " 00:00:00";
                                                                    }
                                                                    else if(cc.Format == "yyyy/MM/dd ddd HH:mm:ss") {
                                                                        newValueSTR = ccDateTime.ToString("yyyy-MM-dd HH:mm:ss");
                                                                    }
                                                                }
                                                            }

                                                            valueSTR = newValueSTR;
                                                        }
                                                        else if(cc.inputType == "NumericUpDown") {
                                                            string newValueSTR = null;

                                                            if(!string.IsNullOrEmpty(cellValueSTR)) {
                                                                decimal newValue;

                                                                if(decimal.TryParse(cellValueSTR, out newValue)) {
                                                                    newValueSTR = FunctionForDatabase.decConvForDB(newValue, cc.Format).ToString();
                                                                }
                                                            }

                                                            valueSTR = newValueSTR;
                                                        }
                                                        else {
                                                            valueSTR = cellValueSTR;
                                                        }
                                                    }
                                                }

                                                if(valueSTR != null) {
                                                    if(changedColumn > 0) {
                                                        sql2 += ", ";
                                                    }

                                                    sql2 += cc.Name + " = @p" + changedColumn.ToString();

                                                    cmd.Parameters.Add(new SQLiteParameter("@p" + changedColumn.ToString(), valueSTR));

                                                    changedColumn++;
                                                }
                                            }
                                        }

                                        if(changedColumn > 0) {
                                            cmd.CommandText = sql1 + sql2 + sql3;
                                            cmd.Parameters.Add(new SQLiteParameter("@id", id));

                                            cmd.ExecuteNonQuery();
                                        }
                                    }
                                }
                            }
                        }
                        progressSplash1.ProgressMessage = "データベース格納中";
                        Application.DoEvents();

                        transaction.Commit();

                        //晴天日判定書込み
                        progressSplash1.ProgressMessage = "晴天日判定中";
                        Application.DoEvents();

                        for(int i = 0; i < dataGridViewTemp.RowCount; i++) {
                            DataGridViewRow row = dataGridViewTemp.Rows[i];
                            KanrisyaSyorijou kanrisyaSyorijou = new KanrisyaSyorijou((string)row.Cells["kanrisya"].Value, (string)row.Cells["syorijou"].Value);

                            if(!kanrisyaSyorijouList.Contains(kanrisyaSyorijou)) {
                                kanrisyaSyorijouList.Add(kanrisyaSyorijou);
                            }
                        }

                        foreach(KanrisyaSyorijou kanrisyaSyorijou in kanrisyaSyorijouList) {
                            FunctionForDatabase.judgeSeitenUpdateDB(kanrisyaSyorijou.kanrisya, kanrisyaSyorijou.syorijou);
                        }
                    }
                    catch(Exception ex) {
                        transaction.Rollback();
                        MessageBox.Show(ex.Message);
                    }
                    finally {
                        progressSplash1.Close();
                        progressSplash1.Dispose();
                        this.Activate();
                        con.Clone();
                    }
                }
            }

            finInport = true;
            this.Close();
        }




        //キャンセルボタンクリックイベント
        private void button3_Click(object sender, EventArgs e) {
            this.Close();
        }

    }
}
