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

namespace WindowsFormsApplication1 {
    public partial class FormC1MergeSpecialAnalysis : Form {
        public FormC1MergeSpecialAnalysis() {
            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);
        Color fromDBColor = Color.FromArgb(255, 255, 255);
        Color fromFileColor = Color.FromArgb(200, 255, 150);

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




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




        //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) {
                if(cc.HeaderText.Length > 2) {
                    if(cc.Name == "kanrisya" || cc.Name == "syorijou" || cc.Name == "sokutei_dt" || cc.HeaderText.Substring(0, 3) == "別分1") {
                        c[i] = new DataGridViewTextBoxColumn();
                        c[i].AutoSizeMode = DataGridViewAutoSizeColumnMode.None;
                        c[i].Resizable = DataGridViewTriState.False;
                        c[i].ReadOnly = true;
                        c[i].HeaderText = cc.HeaderText.Replace("別分1", "");

                        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;
                        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);

            dataGridViewTemp.Columns["kanrisya"].Width = ccSet[ColumnsConfigSet.nameToIndex("kanrisya")].Width;
            dataGridViewTemp.Columns["syorijou"].Width = ccSet[ColumnsConfigSet.nameToIndex("syorijou")].Width;
            dataGridViewTemp.Columns["sokutei_dt"].Width = ccSet[ColumnsConfigSet.nameToIndex("sokutei_dt")].Width;
            dataGridViewTemp.Columns["name_exbunseki_1"].Width = 80;

            foreach(DataGridViewColumn column in dataGridViewTemp.Columns) {
                if(column.Width < 50) {
                    column.Width = 50;
                }
            }

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

            //測定箇所列まで固定
            dataGridViewTemp.Columns["name_exbunseki_1"].Frozen = true;
        }




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

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




        //ファイル選択ボタンクリックイベント
        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();
                label1.Text = "ファイル：" + OpenFileDialog1.FileName;
                button2.Enabled = false;

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

                //ファイル読込
                FormA2ProgressSplash progressSplash1 = new FormA2ProgressSplash();
                progressSplash1.Owner = this;

                dataGridViewTemp.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("special_analysis", out worksheet0)) {
                                throw new Exception("このエクセルファイルにはシート「special_analysis」がありません\nデータが入ったシートの名前は「special_analysis」にしてください");
                            }

                            using(IXLWorksheet worksheet1 = worksheet0) {
                                //共通データ取得
                                string kanrisya = worksheet1.Cell(1, 2).Value.ToString();
                                string syorijou = worksheet1.Cell(2, 2).Value.ToString();
                                string sokutei_dtSTR = worksheet1.Cell(3, 2).Value.ToString();

                                DateTime sokutei_dt;
                                string settedDateSTR = "";

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

                                //項目リスト取得
                                int lastRowNo = worksheet1.LastRowUsed().RowNumber();
                                string[] headerText = new string[lastRowNo + 1];

                                for(int i = 6; i <= lastRowNo; i++) {
                                    headerText[i] = worksheet1.Cell(i, 1).Value.ToString();
                                }

                                //1分析ずつ追加
                                for(int i = 1; i <= MaxSizeConfig.maxSpecialAnalysis; i++) {
                                    if(worksheet1.Cell(5, i + 1).Value.ToString() != "別途分析" + i.ToString()) {
                                        continue;
                                    }

                                    //行追加
                                    dataGridViewTemp.Rows.Add();
                                    int newRowNo = dataGridViewTemp.Rows.Count - 1;

                                    dataGridViewTemp.Rows[newRowNo].HeaderCell.Value = "別途分析" + i.ToString();

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

                                    //同じ管理者、処理場、測定日のデータが存在するか
                                    using(var con = new SQLiteConnection(DataBaseConfig.ConnectionString)) {
                                        con.Open();

                                        using(var cmd = con.CreateCommand()) {
                                            cmd.CommandText = "SELECT * FROM suishitsu_kanri WHERE del_flg IS NULL AND kanrisya = '" + kanrisya + "' AND syorijou = '" + syorijou + "' AND sokutei_dt = '" + settedDateSTR + "';";

                                            using(var reader = cmd.ExecuteReader()) {
                                                if(reader.Read()) {
                                                    if(reader.IsDBNull(0)) {
                                                        dataGridViewTemp.Rows[newRowNo].Cells["kanrisya"].Style.BackColor = errorColor;
                                                        dataGridViewTemp.Rows[newRowNo].Cells["syorijou"].Style.BackColor = errorColor;
                                                        dataGridViewTemp.Rows[newRowNo].Cells["sokutei_dt"].Style.BackColor = errorColor;
                                                        throw new Exception("システム内に管理者、処理場、測定日を同じくするデータが存在しないので読込できません");
                                                    }
                                                }
                                                else {
                                                    dataGridViewTemp.Rows[newRowNo].Cells["kanrisya"].Style.BackColor = errorColor;
                                                    dataGridViewTemp.Rows[newRowNo].Cells["syorijou"].Style.BackColor = errorColor;
                                                    dataGridViewTemp.Rows[newRowNo].Cells["sokutei_dt"].Style.BackColor = errorColor;
                                                    throw new Exception("システム内に管理者、処理場、測定日を同じくするデータが存在しないので読込できません");
                                                }
                                            }
                                        }
                                    }

                                    foreach(ColumnConfig cc in ccSet) {
                                        if(cc.HeaderText.Length > 2) {
                                            if(cc.HeaderText.Substring(0, 3) == "別分1") {
                                                int R = Array.IndexOf(headerText, cc.HeaderText.Replace("別分1", ""));

                                                if(R > 0) {
                                                    string cellValueSTR = worksheet1.Cell(R, i + 1).Value.ToString();
                                                    decimal cellValueDEC;

                                                    if(cc.Format == "N0" || cc.Format == "N1" || cc.Format == "N2" || cc.Format == "N3" || cc.Format == "N4" || cc.Format == "N5") {
                                                        if(String.IsNullOrEmpty(cellValueSTR)) {
                                                            dataGridViewTemp.Rows[newRowNo].Cells[cc.Name].Value = null;
                                                        }
                                                        else if(decimal.TryParse(cellValueSTR, out cellValueDEC)) {
                                                            if(cellValueDEC < ccSet[cc.index].Minimum) {
                                                                dataGridViewTemp.Rows[newRowNo].Cells[cc.Name].Value = cellValueSTR;
                                                                dataGridViewTemp.Rows[newRowNo].Cells[cc.Name].Style.BackColor = errorColor;
                                                                throw new Exception("数値データの列の値が下限以下の行があります\nセル：" + FunctionForExcel.columnNoToLetter(i + 1) + R.ToString() + "\n値：" + cellValueSTR);
                                                            }
                                                            else if(cellValueDEC > ccSet[cc.index].Maximum) {
                                                                dataGridViewTemp.Rows[newRowNo].Cells[cc.Name].Value = cellValueSTR;
                                                                dataGridViewTemp.Rows[newRowNo].Cells[cc.Name].Style.BackColor = errorColor;
                                                                throw new Exception("数値データの列の値が上限以上の行があります\nセル：" + FunctionForExcel.columnNoToLetter(i + 1) + R.ToString() + "\n値：" + cellValueSTR);
                                                            }

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

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

                                if(dataGridViewTemp.RowCount > 0) {
                                    label2.Text = string.Format("読込できます（{0}箇所）　読込ボタンを押してください", dataGridViewTemp.RowCount);
                                    label2.ForeColor = correctColor;
                                    button2.Enabled = true;
                                }
                                else {
                                    throw new Exception("読込できる行がありません");
                                }
                            }
                        }
                    }
                }
                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 {
                    dataGridViewTemp.ResumeLayout();
                    progressSplash1.Close();
                    progressSplash1.Dispose();
                    this.Activate();
                }
            }
        }




        //読込ボタンクリックイベント
        private void button2_Click(object sender, EventArgs e) {
            button1.Enabled = false;
            button2.Enabled = false;
            button3.Enabled = false;

            FormA2ProgressSplash progressSplash1 = new FormA2ProgressSplash();
            progressSplash1.Owner = this;
            progressSplash1.Show();
            progressSplash1.Refresh();
            progressSplash1.ProgressMessage = "別途分析ファイル読込中";

            //データのシート
            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()) {
                    try {
                        //1行づつ追加
                        foreach(DataGridViewRow dgvTRow in dataGridViewTemp.Rows) {
                            int id = -1;

                            //管理者・処理場・測定日でレコードのidを取得
                            using(var cmd = con.CreateCommand()) {
                                cmd.CommandText = "SELECT id FROM suishitsu_kanri WHERE kanrisya = @kanrisya AND syorijou = @syorijou AND sokutei_dt = @sokutei_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));

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

                            //なければエラー
                            if(id < 1) {
                                throw new Exception("システム内に管理者、処理場、測定日を同じくするデータが存在しないので読込できません");
                            }

                            //取得したidのレコードにデータを記入
                            string exBunsekiNoSTR = dgvTRow.HeaderCell.Value.ToString().Replace("別途分析", "");

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

                                sql2 += "input_dt = @input_dt";
                                cmd.Parameters.Add(new SQLiteParameter("@input_dt", input_dtSTR));

                                for(int k = 0; k < dataGridViewTemp.Columns.Count; k++) {
                                    string columnName = dataGridViewTemp.Columns[k].Name;

                                    if(columnName != "kanrisya" && columnName != "syorijou" && columnName != "sokutei_dt") {
                                        string columnNamePExNo = columnName.Substring(0, columnName.Length - 1) + exBunsekiNoSTR;
                                        string cellValueSTR = null;

                                        if(dgvTRow.Cells[k].Value != null) {
                                            cellValueSTR = dgvTRow.Cells[k].Value.ToString();
                                        }

                                        ColumnConfig cc = ccSet[ColumnsConfigSet.nameToIndex(columnNamePExNo)];
                                        string valueSTR = null;

                                        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;
                                            }
                                        }

                                        sql2 += ", " + columnNamePExNo + " = @p" + k.ToString();
                                        cmd.Parameters.Add(new SQLiteParameter("@p" + k.ToString(), valueSTR));
                                    }
                                }

                                cmd.CommandText = sql1 + sql2 + sql3;
                                cmd.Parameters.Add(new SQLiteParameter("@id", id));

                                cmd.ExecuteNonQuery();
                            }
                        }

                        transaction.Commit();
                    }
                    catch(Exception ex) {
                        transaction.Rollback();
                        MessageBox.Show(ex.Message);
                    }
                    finally {
                        con.Close();
                    }
                }
            }

            progressSplash1.Close();
            progressSplash1.Dispose();

            finInport = true;
            this.Close();
        }




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

    }
}
