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

namespace WindowsFormsApplication1 {
    public partial class FormB1MergeData : Form {
        public FormB1MergeData() {
            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) {
                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;
        }




        //ファイル選択ボタンクリックイベント
        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();
                label6.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("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("このエクセルファイルには処理場の列がありません");
                                }

                                //マージ方式選択
                                var win = new FormB2MergeMethodSelect();
                                win.Owner = this;
                                win.StartPosition = FormStartPosition.CenterParent;
                                win.ShowDialog();

                                //フォームクローズ後の処理
                                bool okClick = win.okClick;
                                bool prioritizeSystemData = win.prioritizeSystemData;
                                win.Dispose();

                                if(!okClick) {
                                    return;
                                }

                                //ファイル読込
                                progressSplash1.Show();
                                progressSplash1.Refresh();
                                progressSplash1.ProgressMessage = "ファイル読込準備中";
                                Application.DoEvents();

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

                                //1行づつ読込
                                int newRow = 0;

                                for(int j = 2; j <= lastRowNo; j++) {
                                    progressSplash1.ProgressMessage = "データファイル読込中（" + (j - 1M).ToString() +  "/" + (lastRowNo - 1M).ToString() + "）";
                                    Application.DoEvents();

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

                                    DateTime sokutei_dt;
                                    string settedDateSTR = "";
                                    string kanrisya = worksheet1.Cell(j, kanrisyaColumnNo).Value.ToString();
                                    string syorijou = worksheet1.Cell(j, syorijouColumnNo).Value.ToString();

                                    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["kanrisya"].Value = kanrisya;
                                        dataGridViewTemp.Rows[newRowNo].Cells["syorijou"].Value = syorijou;
                                        dataGridViewTemp.Rows[newRowNo].Cells["sokutei_dt"].Value = sokutei_dt;

                                        bool dbHasRow;

                                        if(prioritizeSystemData) {
                                            //システム優先
                                            fileToDataGridViewTemp();
                                            dbHasRow = dbToDataGridViewTempAndHasRow();
                                        }
                                        else {
                                            //ファイル優先
                                            dbHasRow = dbToDataGridViewTempAndHasRow();
                                            fileToDataGridViewTemp();
                                        }

                                        //DBに行がない場合(新規追加)、管理者・処理場・測定日の色を変える
                                        if(!dbHasRow) {
                                            dataGridViewTemp.Rows[newRowNo].Cells["kanrisya"].Style.BackColor = fromFileColor;
                                            dataGridViewTemp.Rows[newRowNo].Cells["syorijou"].Style.BackColor = fromFileColor;
                                            dataGridViewTemp.Rows[newRowNo].Cells["sokutei_dt"].Style.BackColor = fromFileColor;
                                            newRow++;
                                        }


                                        //【ローカル関数】DBからデータを取得しGridViewTempに記入する
                                        bool dbToDataGridViewTempAndHasRow() {
                                            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) {
                                                            return false;
                                                        }

                                                        while(reader.Read()) {
                                                            for(int i = 0; i < ccSet.Count; i++) {
                                                                if(ccSet[i].Name == "kanrisya" || ccSet[i].Name == "syorijou" || ccSet[i].Name == "sokutei_dt" || ccSet[i].calculatedDecimal || ccSet[i].calculatedString) {
                                                                    continue;
                                                                }

                                                                if(reader.IsDBNull(i)) {
                                                                    continue;
                                                                }

                                                                if(ccSet[i].Format == "N0" || ccSet[i].Format == "N1" || ccSet[i].Format == "N2" || ccSet[i].Format == "N3" || ccSet[i].Format == "N4" || ccSet[i].Format == "N5") {
                                                                    dataGridViewTemp.Rows[newRowNo].Cells[i].Value = FunctionForDatabase.decConvFromDB(reader.GetDecimal(i), ccSet[i].Format);
                                                                    dataGridViewTemp.Rows[newRowNo].Cells[i].Style.BackColor = fromDBColor;
                                                                }
                                                                else if(string.IsNullOrEmpty(reader.GetString(i))) {
                                                                    continue;
                                                                }
                                                                else {
                                                                    dataGridViewTemp.Rows[newRowNo].Cells[i].Value = reader.GetString(i);
                                                                    dataGridViewTemp.Rows[newRowNo].Cells[i].Style.BackColor = fromDBColor;
                                                                }
                                                            }
                                                        }
                                                    }
                                                }
                                            }

                                            return true;
                                        }


                                        //【ローカル関数】ファイルからデータを取得しGridViewTempに記入する
                                        void fileToDataGridViewTemp() {
                                            for(int i = 0; i < lastColumnNo; i++) {
                                                if(i == kanrisyaColumnNo - 1 || i == syorijouColumnNo - 1 || i == sokutei_dtColumnNo - 1) {
                                                    continue;
                                                }

                                                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) {
                                                        continue;
                                                    }
                                                    else if(String.IsNullOrEmpty(cellValueSTR)) {
                                                        continue;
                                                    }
                                                    else if(ccFormat == "N0" || ccFormat == "N1" || ccFormat == "N2" || ccFormat == "N3" || ccFormat == "N4" || ccFormat == "N5") {
                                                        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;
                                                            dataGridViewTemp.Rows[newRowNo].Cells[ccName].Style.BackColor = fromFileColor;
                                                        }
                                                        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;
                                                            dataGridViewTemp.Rows[newRowNo].Cells[ccName].Style.BackColor = fromFileColor;
                                                        }
                                                        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;
                                                        dataGridViewTemp.Rows[newRowNo].Cells[ccName].Style.BackColor = fromFileColor;
                                                    }
                                                }
                                            }
                                        }
                                    }

                                    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) {
                                    label1.Text = string.Format("マージできます（マージ：{0}行　新規追加：{1}行）　マージボタンを押してください", dataGridViewTemp.RowCount - newRow, newRow);
                                    label1.ForeColor = correctColor;
                                    button2.Enabled = true;
                                }
                                else {
                                    throw new Exception("マージできる行がありません");
                                }
                            }
                        }
                    }

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

                }
                catch(Exception ex) {
                    label1.Text = "マージできません。ファイルを修正するか他のファイルを選択してください";
                    label1.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;

            //データのシート
            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 = "データファイルマージ準備中";
                    Application.DoEvents();

                    try {
                        //1行づつ追加
                        int j = 0;
                        foreach(DataGridViewRow dgvTRow in dataGridViewTemp.Rows) {
                            j++;
                            progressSplash1.ProgressMessage = "データファイルマージ命令発行中（" + j.ToString() + "/" + dataGridViewTemp.RowCount.ToString() + "）";
                            Application.DoEvents();

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

                            //なければ管理者・処理場・測定日・入力日のみで挿入しレコードのidを取得
                            if(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;";

                                        if(i == 0) {
                                            sql2 += "input_dt = @input_dt";
                                            cmd.Parameters.Add(new SQLiteParameter("@input_dt", input_dtSTR));
                                            changedColumn++;
                                        }

                                        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(dgvTRow.Cells["kanrisya"].Value.ToString(), dgvTRow.Cells["syorijou"].Value.ToString(), 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(cellValueSTR != null) {
                                                        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();
        }

    }
}
