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

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


        Color correctColor = Color.FromArgb(0, 0, 255);
        Color errorColor = Color.FromArgb(255, 0, 0);
        DataSet DataSet1 = new DataSet();

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




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

                if(!string.IsNullOrEmpty(OpenFileDialog1.FileName) && File.Exists(OpenFileDialog1.FileName)) {
                    for(int i = flowLayoutPanel1.Controls.Count - 1; i >= 0; i--) {
                        flowLayoutPanel1.Controls[i].Dispose();
                    }

                    DataSet1.Clear();

                    int tablesCount = DataSet1.Tables.Count;

                    for(int i=0; i < tablesCount; i++) {
                        DataSet1.Tables.Remove(DataSet1.Tables[0]);
                    }

                    FormA2ProgressSplash progressSplash1 = new FormA2ProgressSplash();
                    progressSplash1.Owner = this;
                    progressSplash1.Show();
                    progressSplash1.Refresh();

                    flowLayoutPanel1.SuspendLayout();

                    try {
                        using(FileStream fileStream1 = new FileStream(OpenFileDialog1.FileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) {
                            using(XLWorkbook workbook1 = new XLWorkbook(fileStream1, XLEventTracking.Disabled)) {
                                using(var con = new SQLiteConnection(DataBaseConfig.ConnectionString)) {
                                    con.Open();

                                    using(var cmd1 = con.CreateCommand()) {
                                        cmd1.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name<>'suishitsu_kanri' AND name<>'sqlite_sequence';";

                                        using(var reader1 = cmd1.ExecuteReader()) {
                                            if(reader1.HasRows) {
                                                int enablTableCount = 0;

                                                while(reader1.Read()) {
                                                    string tableName = reader1.GetString(0);
                                                    IXLWorksheet worksheet;

                                                    if(workbook1.TryGetWorksheet(tableName, out worksheet)) {
                                                        Label newlabel = new Label();
                                                        newlabel.Text = "設定名：" + tableName + "→OK";
                                                        newlabel.Font = new Font("Meiryo UI", 9F, FontStyle.Regular, GraphicsUnit.Point, ((byte)(128)));
                                                        newlabel.AutoSize = true;
                                                        newlabel.Margin = new Padding(3, 5, 3, 0);

                                                        flowLayoutPanel1.Controls.Add(newlabel);

                                                        DataTable newDataTable = new DataTable();
                                                        newDataTable.TableName = tableName;

                                                        int lastRowNo = worksheet.LastRowUsed().RowNumber();
                                                        int lastColumnNo = worksheet.LastColumnUsed().ColumnNumber();
                                                        string[] HeaderText = new string[lastColumnNo];

                                                        //設定ファイル列ヘッダ抽出
                                                        for(int i = 0; i < lastColumnNo; i++) {
                                                            HeaderText[i] = worksheet.Cell(1, i + 1).Value.ToString();
                                                        }

                                                        List<string> columnType = new List<string>();
                                                        List<int> cellLocate = new List<int>();
                                                        int enableColumnCount = 0;
                                                        string primaryKeyColumnName = "";
                                                        int primaryKeyColumnNo = 1;

                                                        //列ヘッダをデータベースの列へ割当
                                                        using(var cmd2 = con.CreateCommand()) {
                                                            cmd2.CommandText = "PRAGMA TABLE_INFO(" + tableName + ");";

                                                            using(var reader2 = cmd2.ExecuteReader()) {
                                                                string errorMess = "";
                                                                string cellValues = "";

                                                                while(reader2.Read()) {
                                                                    if(0 <= Array.IndexOf(HeaderText, reader2.GetString(1))) {
                                                                        if(cellValues != "") {
                                                                            cellValues += ", ";
                                                                        }

                                                                        cellValues += reader2.GetString(1);

                                                                        newDataTable.Columns.Add(reader2.GetString(1));

                                                                        columnType.Add(reader2.GetString(2));
                                                                        cellLocate.Add(Array.IndexOf(HeaderText, reader2.GetString(1)) + 1);

                                                                        if(reader2.GetInt32(5) == 1) {
                                                                            primaryKeyColumnName = reader2.GetString(1);
                                                                            primaryKeyColumnNo = enableColumnCount;

                                                                            newDataTable.PrimaryKey = new DataColumn[] { newDataTable.Columns[primaryKeyColumnName] };
                                                                        }

                                                                        enableColumnCount++;
                                                                    }
                                                                }

                                                                if(enableColumnCount > 0) {
                                                                    Label newlabel1 = new Label();
                                                                    newlabel1.Text = "　項目：" + cellValues + "→OK";
                                                                    newlabel1.Font = new Font("Meiryo UI", 9F, FontStyle.Regular, GraphicsUnit.Point, ((byte)(128)));
                                                                    newlabel1.AutoSize = true;
                                                                    newlabel1.Margin = new Padding(3, 5, 3, 0);

                                                                    flowLayoutPanel1.Controls.Add(newlabel1);
                                                                }
                                                                else {
                                                                    Label newlabel1 = new Label();
                                                                    newlabel1.Text = "　項目：有効な項目が含まれていません";
                                                                    newlabel1.Font = new Font("Meiryo UI", 9F, FontStyle.Regular, GraphicsUnit.Point, ((byte)(128)));
                                                                    newlabel1.ForeColor = errorColor;
                                                                    newlabel1.AutoSize = true;
                                                                    newlabel1.Margin = new Padding(3, 5, 3, 0);

                                                                    flowLayoutPanel1.Controls.Add(newlabel1);

                                                                    throw new Exception(errorMess);
                                                                }
                                                            }
                                                        }

                                                        //設定値をデータセットに取込
                                                        List<string> primaryKeyValue = new List<string>();

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

                                                        for(int R = 2; R <= lastRowNo; R++) {
                                                            setpIntegrate += stepPer100;

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

                                                            bool error = false;
                                                            string errorMess = "";
                                                            string cellValues = "";

                                                            DataRow newRow = newDataTable.NewRow();

                                                            for(int i = 0; i < enableColumnCount; i++) {
                                                                if(cellValues != "") {
                                                                    cellValues += ", ";
                                                                }

                                                                string cellValue = worksheet.Cell(R, cellLocate[i]).Value.ToString();
                                                                cellValues += cellValue;

                                                                newRow[i] = cellValue;

                                                                if(columnType[i] == "INTEGER") {
                                                                    int cellValueInt;

                                                                    if(!(int.TryParse(cellValue, out cellValueInt))) {
                                                                        error = true;
                                                                        errorMess = "無効な値があります";
                                                                    }
                                                                }

                                                                if(i == primaryKeyColumnNo) {
                                                                    if(!primaryKeyValue.Contains(cellValue)) {
                                                                        primaryKeyValue.Add(cellValue);
                                                                    }
                                                                    else {
                                                                        error = true;
                                                                        errorMess = "設定項目に対する値が重複しています";
                                                                    }
                                                                }
                                                            }

                                                            if(!error) {
                                                                Label newlabel1 = new Label();
                                                                newlabel1.Text = "　　値：" + cellValues + "→OK";
                                                                newlabel1.Font = new Font("Meiryo UI", 9F, FontStyle.Regular, GraphicsUnit.Point, ((byte)(128)));
                                                                newlabel1.AutoSize = true;
                                                                newlabel1.Margin = new Padding(3, 5, 3, 0);

                                                                flowLayoutPanel1.Controls.Add(newlabel1);
                                                            }
                                                            else {
                                                                Label newlabel1 = new Label();
                                                                newlabel1.Text = "　　値：" + cellValues + "→NG";
                                                                newlabel1.Font = new Font("Meiryo UI", 9F, FontStyle.Regular, GraphicsUnit.Point, ((byte)(128)));
                                                                newlabel1.ForeColor = errorColor;
                                                                newlabel1.AutoSize = true;
                                                                newlabel1.Margin = new Padding(3, 5, 3, 0);

                                                                flowLayoutPanel1.Controls.Add(newlabel1);

                                                                throw new Exception(errorMess);
                                                            }

                                                            newDataTable.Rows.Add(newRow);
                                                        }

                                                        DataSet1.Tables.Add(newDataTable);
                                                        enablTableCount++;
                                                    }
                                                }

                                                if(enablTableCount > 0) {
                                                    label2.Text = string.Format("インポートできます。インポートボタンを押してください");
                                                    label2.ForeColor = correctColor;
                                                    button2.Enabled = true;
                                                }
                                                else {
                                                    throw new Exception("有効な設定が含まれていません");

                                                }
                                            }
                                            else {
                                                MessageBox.Show("設定データベースがありません");
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                    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 {
                        flowLayoutPanel1.ResumeLayout();
                        progressSplash1.Close();
                        progressSplash1.Dispose();
                        this.Activate();
                    }
                }
            }
        }




        //インポートボタンクリックイベント
        private void button2_Click(object sender, EventArgs e) {
            FormA2ProgressSplash progressSplash1 = new FormA2ProgressSplash();
            progressSplash1.Owner = this;
            progressSplash1.Show();
            progressSplash1.Refresh();

            progressSplash1.ProgressMessage = "設定適用中";
            Application.DoEvents();

            string dbTransactionCmd = "";

            foreach(DataTable cTable in DataSet1.Tables) {
                string primaryKeyName = cTable.PrimaryKey[0].ColumnName;
                string[] columnName = new string[cTable.Columns.Count];
                int primaryKeyNo = 0;

                int i = 0;
                foreach(DataColumn cColumn in cTable.Columns) {
                    if(cColumn.ColumnName == primaryKeyName) {
                        primaryKeyNo = i;
                    }

                    columnName[i] = cColumn.ColumnName;
                    i++;
                }

                decimal stepPer100 = 100M / cTable.Rows.Count;
                decimal setpIntegrate = 0;

                int rowCount = 0;
                foreach(DataRow cRow in cTable.Rows) {
                    rowCount++;
                    setpIntegrate += stepPer100;

                    if(setpIntegrate >= 10) {
                        progressSplash1.ProgressMessage = "設定適用中（" + (Math.Round(rowCount / cTable.Rows.Count * 10M, 0) * 10).ToString() + "%）";
                        Application.DoEvents();
                        setpIntegrate = 0;
                    }

                    string sql1 = "";
                    string sql2 = "";

                    int j = 0;
                    int k = 0;
                    foreach(var cItem in cRow.ItemArray) {
                        if(j != primaryKeyNo) {
                            if(k > 0) {
                                sql1 += ", ";
                            }

                            sql1 += columnName[j] + "='" + cItem.ToString() + "'";
                            k++;
                        }
                        else {
                            sql2 += columnName[j] + "='" + cItem.ToString() + "'";
                        }

                        j++;
                    }

                    dbTransactionCmd += "UPDATE " + cTable.TableName + " SET " + sql1 + " WHERE " + sql2 + ";";
                }
            }

            if(dbTransactionCmd != "") {
                using(var con = new SQLiteConnection(DataBaseConfig.ConnectionString)) {
                    con.Open();

                    using(var transaction = con.BeginTransaction()) {
                        try {
                            using(var cmd = con.CreateCommand()) {
                                cmd.CommandText = dbTransactionCmd;
                                cmd.ExecuteNonQuery();
                            }

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

            progressSplash1.Close();
            progressSplash1.Dispose();
            this.Activate();

            finInport = true;
            this.Close();
        }




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

    }
}
