using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using System.Data.SqlClient;

namespace MedicalCheckInputTool
{
    public partial class Form3 : Form
    {
        private const int PAGEMAX = 10;
        private int currentPageNo = 1;
        private int maxPageNo = 1;

        public Form3()
        {
            InitializeComponent();

            this.Text = "ロット: " + EditParam.lotNo + "  ユーザ: " + EditParam.inputUser;

            this.label2.Text = "";
        }

        private void setPage()
        {
            string connstr = System.Configuration.ConfigurationManager.AppSettings["Conn"];
            MySqlConnection readerCon = new MySqlConnection(connstr);
            readerCon.Open();
            string countQuery = "select count(*) from page_compare where ";
//            string orderby = " order by `f_行政区コード`, `f_整理番号`";

            // ロットの指定は必須
            countQuery += "(`f_ロット番号` = " + EditParam.lotNo + " or `s_ロット番号` = " + EditParam.lotNo + ") ";

            if (!"".Equals(textBox1.Text))
            {
                countQuery += " and (`f_整理番号` like '%" + textBox1.Text + "%' or `s_整理番号` like '%" + textBox1.Text + "%')";
            }

            if (this.checkBox1.Checked)
            {
                countQuery += " and (`f_完了` <> 1 or `s_完了` <> 1)";
            }

            if (this.checkBox2.Checked && this.checkBox3.Checked)
            {

            }
            else
            {
                if (this.checkBox2.Checked)
                {
                    countQuery += " and `f_整理番号` is not null";
                }
                if (this.checkBox3.Checked)
                {
                    countQuery += " and `s_整理番号` is not null";
                }
            }
            // 並び順
//            countQuery += orderby;

            //ページ設定
            MySqlCommand cntCmd = new MySqlCommand(countQuery, readerCon);
            MySqlDataReader cntreader = cntCmd.ExecuteReader();
            int dataCnt = 0;
            while (cntreader.Read())
            {
                dataCnt = cntreader.GetInt32(0);
            }
            cntreader.Close();

            maxPageNo = 1;
            if (dataCnt > 0)
            {
                maxPageNo = (dataCnt / PAGEMAX) + ((dataCnt % PAGEMAX == 0) ? 0 : 1);
            }

            comboBox1.Items.Clear(); //クリアする
            for (int i = 0; i < maxPageNo; i++)
            {
                comboBox1.Items.Add((i + 1).ToString());
            }
            // 読み取り専用（テキストボックスは編集不可）にする
            comboBox1.DropDownStyle = ComboBoxStyle.DropDownList;
            comboBox1.SelectedIndex = 0; // 先頭の項目を選択
            currentPageNo = 1;
            readerCon.Close();
        }

        private void search()
        {
            string connstr = System.Configuration.ConfigurationManager.AppSettings["Conn"];
            MySqlConnection readerCon = new MySqlConnection(connstr);
            readerCon.Open();
            string readQuery = "select * from page_compare where ";
            string orderby = " order by `f_行政区コード`, `f_整理番号`";
            string paging = " limit " + PAGEMAX + " offset " + ((currentPageNo - 1) * PAGEMAX);

            // ロットの指定は必須
            readQuery += "(`f_ロット番号` = " + EditParam.lotNo + " or `s_ロット番号` = " + EditParam.lotNo + ") ";

            if (!"".Equals(textBox1.Text))
            {
                readQuery += " and (`f_整理番号` like '%" + textBox1.Text + "%' or `s_整理番号` like '%" + textBox1.Text + "%')";
            }

            if (this.checkBox1.Checked)
            {
//                readQuery += " and (`f_完了` <> 1 or `s_完了` <> 1)";
                readQuery += " and (`f_完了` is null or `s_完了` is null)";
            }

            if (this.checkBox2.Checked && this.checkBox3.Checked)
            {

            }
            else
            {
                if (this.checkBox2.Checked)
                {
                    readQuery += " and `f_整理番号` is not null";
                }
                if (this.checkBox3.Checked)
                {
                    readQuery += " and `s_整理番号` is not null";
                }
            }
            // 並び順
            readQuery += orderby;

            //ページ指定
            readQuery += paging;

            MySqlCommand readCmd = new MySqlCommand(readQuery, readerCon);
            MySqlDataReader reader = readCmd.ExecuteReader();

            this.dataGridView1.Rows.Clear();
            while (reader.Read())
            {
                DataGridViewRow row = new DataGridViewRow();
                row.CreateCells(this.dataGridView1);
                row.Cells[0].Value = (this.isDBNull(reader, "f_完了") == "1") ? "一致" : "不一致";
                this.dataGridView1.Rows.Add(row);
                foreach (string prefix in new string[2] { "f_", "s_" })
                {
                    row.Cells[prefix + "行政区コード"].Value = this.isDBNull(reader, prefix + "行政区コード");
                    row.Cells[prefix + "整理番号"].Value = this.isDBNull(reader, prefix + "整理番号");
                    row.Cells[prefix + "世帯番号"].Value = this.isDBNull(reader, prefix + "世帯番号");
                    row.Cells[prefix + "電話番号"].Value = this.isDBNull(reader, prefix + "電話番号");
                    row.Cells[prefix + "携帯番号"].Value = this.isDBNull(reader, prefix + "携帯番号");
                    row.Cells[prefix + "健康診査"].Value = this.isDBNull(reader, prefix + "健康診査");
                    row.Cells[prefix + "結核肺がん"].Value = this.isDBNull(reader, prefix + "結核肺がん");
                    row.Cells[prefix + "胃がん"].Value = this.isDBNull(reader, prefix + "胃がん");
                    row.Cells[prefix + "大腸がん"].Value = this.isDBNull(reader, prefix + "大腸がん");
                    row.Cells[prefix + "前立腺がん"].Value = this.isDBNull(reader, prefix + "前立腺がん");
                    row.Cells[prefix + "骨粗鬆症"].Value = this.isDBNull(reader, prefix + "骨粗鬆症");
                    row.Cells[prefix + "成人歯科"].Value = this.isDBNull(reader, prefix + "成人歯科");
                    row.Cells[prefix + "子宮頸がん"].Value = this.isDBNull(reader, prefix + "子宮頸がん");
                    row.Cells[prefix + "乳がん"].Value = this.isDBNull(reader, prefix + "乳がん");
                    row.Cells[prefix + "希望場所"].Value = this.isDBNull(reader, prefix + "希望場所");
                    row.Cells[prefix + "身体不自由"].Value = this.isDBNull(reader, prefix + "身体不自由");
                }
                if (!row.Cells["f_整理番号"].Value.ToString().Equals(row.Cells["s_整理番号"].Value.ToString()))
                {
                    row.Cells["f_整理番号"].Style.BackColor = Color.Pink;
                    row.Cells["s_整理番号"].Style.BackColor = Color.Pink;
                }
                if (!row.Cells["f_世帯番号"].Value.ToString().Equals(row.Cells["s_世帯番号"].Value.ToString()))
                {
                    row.Cells["f_世帯番号"].Style.BackColor = Color.Pink;
                    row.Cells["s_世帯番号"].Style.BackColor = Color.Pink;
                }
                if (!row.Cells["f_電話番号"].Value.ToString().Equals(row.Cells["s_電話番号"].Value.ToString()))
                {
                    row.Cells["f_電話番号"].Style.BackColor = Color.Pink;
                    row.Cells["s_電話番号"].Style.BackColor = Color.Pink;
                }
                if (!row.Cells["f_携帯番号"].Value.ToString().Equals(row.Cells["s_携帯番号"].Value.ToString()))
                {
                    row.Cells["f_携帯番号"].Style.BackColor = Color.Pink;
                    row.Cells["s_携帯番号"].Style.BackColor = Color.Pink;
                }
                if (!row.Cells["f_健康診査"].Value.ToString().Equals(row.Cells["s_健康診査"].Value.ToString()))
                {
                    row.Cells["f_健康診査"].Style.BackColor = Color.Pink;
                    row.Cells["s_健康診査"].Style.BackColor = Color.Pink;
                }
                if (!row.Cells["f_結核肺がん"].Value.ToString().Equals(row.Cells["s_結核肺がん"].Value.ToString()))
                {
                    row.Cells["f_結核肺がん"].Style.BackColor = Color.Pink;
                    row.Cells["s_結核肺がん"].Style.BackColor = Color.Pink;
                }
                if (!row.Cells["f_胃がん"].Value.ToString().Equals(row.Cells["s_胃がん"].Value.ToString()))
                {
                    row.Cells["f_胃がん"].Style.BackColor = Color.Pink;
                    row.Cells["s_胃がん"].Style.BackColor = Color.Pink;
                }
                if (!row.Cells["f_大腸がん"].Value.ToString().Equals(row.Cells["s_大腸がん"].Value.ToString()))
                {
                    row.Cells["f_大腸がん"].Style.BackColor = Color.Pink;
                    row.Cells["s_大腸がん"].Style.BackColor = Color.Pink;
                }
                if (!row.Cells["f_前立腺がん"].Value.ToString().Equals(row.Cells["s_前立腺がん"].Value.ToString()))
                {
                    row.Cells["f_前立腺がん"].Style.BackColor = Color.Pink;
                    row.Cells["s_前立腺がん"].Style.BackColor = Color.Pink;
                }
                if (!row.Cells["f_骨粗鬆症"].Value.ToString().Equals(row.Cells["s_骨粗鬆症"].Value.ToString()))
                {
                    row.Cells["f_骨粗鬆症"].Style.BackColor = Color.Pink;
                    row.Cells["s_骨粗鬆症"].Style.BackColor = Color.Pink;
                }
                if (!row.Cells["f_成人歯科"].Value.ToString().Equals(row.Cells["s_成人歯科"].Value.ToString()))
                {
                    row.Cells["f_成人歯科"].Style.BackColor = Color.Pink;
                    row.Cells["s_成人歯科"].Style.BackColor = Color.Pink;
                }
                if (!row.Cells["f_子宮頸がん"].Value.ToString().Equals(row.Cells["s_子宮頸がん"].Value.ToString()))
                {
                    row.Cells["f_子宮頸がん"].Style.BackColor = Color.Pink;
                    row.Cells["s_子宮頸がん"].Style.BackColor = Color.Pink;
                }
                if (!row.Cells["f_乳がん"].Value.ToString().Equals(row.Cells["s_乳がん"].Value.ToString()))
                {
                    row.Cells["f_乳がん"].Style.BackColor = Color.Pink;
                    row.Cells["s_乳がん"].Style.BackColor = Color.Pink;
                }
                if (!row.Cells["f_希望場所"].Value.ToString().Equals(row.Cells["s_希望場所"].Value.ToString()))
                {
                    row.Cells["f_希望場所"].Style.BackColor = Color.Pink;
                    row.Cells["s_希望場所"].Style.BackColor = Color.Pink;
                }
                if (!row.Cells["f_身体不自由"].Value.ToString().Equals(row.Cells["s_身体不自由"].Value.ToString()))
                {
                    row.Cells["f_身体不自由"].Style.BackColor = Color.Pink;
                    row.Cells["s_身体不自由"].Style.BackColor = Color.Pink;
                }
            }
            readerCon.Close();

            readerCon.Open();
            MySqlCommand countCmd = new MySqlCommand("select count(`完了`) as cnt from `２回目` where `完了` = 1 and `ロット番号` = " + EditParam.lotNo, readerCon);
            MySqlDataReader countReader = countCmd.ExecuteReader();

            while (countReader.Read())
            {
                this.label2.Text = countReader.GetString("cnt");
            }
            countReader.Close();
            readerCon.Close();

        }

        private string isDBNull(MySqlDataReader reader, string column)
        {
            int index = reader.GetOrdinal(column);
            if (reader.IsDBNull(index))
            {
                return "";
            }
            else
            {
                return reader.GetString(column);
            }
        }

        // 行マッチ判定
        private void check()
        {
            // 行マッチングデータを取得
            string connstr = System.Configuration.ConfigurationManager.AppSettings["Conn"];

            // 更新用Connection
            MySqlConnection updateCon = new MySqlConnection(connstr);
            updateCon.Open();

            /*
            // 一度フラグを落とす
            string resetFsql = "update `１回目` set `完了` = 0 where `ロット番号` = @lot";
            if (!"".Equals(textBox1.Text))
            {
                resetFsql += " and `整理番号` like '%" + textBox1.Text + "%'";
            }
            MySqlCommand resetFirst = new MySqlCommand(resetFsql, updateCon);
            resetFirst.Parameters.AddWithValue("lot", EditParam.lotNo);
            resetFirst.ExecuteNonQuery();

            string resetSsql = "update `２回目` set `完了` = 0 where `ロット番号` = @lot";
            if (!"".Equals(textBox1.Text))
            {
                resetSsql += " and `整理番号` like '%" + textBox1.Text + "%'";
            }
            MySqlCommand resetSecond = new MySqlCommand(resetSsql, updateCon);
            resetSecond.Parameters.AddWithValue("lot", EditParam.lotNo);
            resetSecond.ExecuteNonQuery();
             */

            // フラグ更新用コマンド
            MySqlCommand updateFirst = new MySqlCommand("update `１回目` set `完了` = 1 where " +
              "`ロット番号` = @lot and " +
              "`整理番号` = @serialCode", updateCon);
            // フラグ更新用コマンド
            MySqlCommand updateSecond = new MySqlCommand("update `２回目` set `完了` = 1 where " +
              "`ロット番号` = @lot and " +
              "`整理番号` = @serialCode", updateCon);

            // 行突合データを取得する
            // 参照用Connection
            MySqlConnection readerCon = new MySqlConnection(connstr);
            readerCon.Open();
            string readQuery = "select * from page_compare where (`f_ロット番号` = @f_lot or `s_ロット番号` = @s_lot) and (`f_完了` <> 1 or `s_完了` <> 1)";
            string orderby = " order by `f_行政区コード`, `f_整理番号`, `s_行政区コード`, `s_整理番号`";
            if (!"".Equals(textBox1.Text))
            {
                readQuery += " and (`f_整理番号` like '%" + textBox1.Text + "%' or `s_整理番号` like '%" + textBox1.Text + "%')";
            }
            readQuery += orderby;
            MySqlCommand readCmd = new MySqlCommand(readQuery, readerCon);
            readCmd.Parameters.AddWithValue("f_lot", EditParam.lotNo);
            readCmd.Parameters.AddWithValue("s_lot", EditParam.lotNo);
            MySqlDataReader reader = readCmd.ExecuteReader();

            while (reader.Read())
            {
                // 各種値のチェック
                bool IsMatch = true;
                // 各項目の一致をチェックする
                // 整理番号
                if (!this.isDBNull(reader, "f_整理番号").Equals(this.isDBNull(reader, "s_整理番号")))
                {
                    IsMatch = false;
                }
                // 世帯番号
                if (!this.isDBNull(reader, "f_世帯番号").Equals(this.isDBNull(reader, "s_世帯番号")))
                {
                    IsMatch = false;
                }
                // 健康診査
                if (!this.isDBNull(reader, "f_健康診査").Equals(this.isDBNull(reader, "s_健康診査")))
                {
                    IsMatch = false;
                }
                // 結核肺がん
                if (!this.isDBNull(reader, "f_結核肺がん").Equals(this.isDBNull(reader, "s_結核肺がん")))
                {
                    IsMatch = false;
                }
                // 胃がん
                if (!this.isDBNull(reader, "f_胃がん").Equals(this.isDBNull(reader, "s_胃がん")))
                {
                    IsMatch = false;
                }
                // 大腸がん
                if (!this.isDBNull(reader, "f_大腸がん").Equals(this.isDBNull(reader, "s_大腸がん")))
                {
                    IsMatch = false;
                }
                // 前立腺がん
                if (!this.isDBNull(reader, "f_前立腺がん").Equals(this.isDBNull(reader, "s_前立腺がん")))
                {
                    IsMatch = false;
                }
                // 骨粗鬆症
                if (!this.isDBNull(reader, "f_骨粗鬆症").Equals(this.isDBNull(reader, "s_骨粗鬆症")))
                {
                    IsMatch = false;
                }
                // 成人歯科
                if (!this.isDBNull(reader, "f_成人歯科").Equals(this.isDBNull(reader, "s_成人歯科")))
                {
                     IsMatch = false;
                }
                // 子宮頸がん
                if (!this.isDBNull(reader, "f_子宮頸がん").Equals(this.isDBNull(reader, "s_子宮頸がん")))
                {
                    IsMatch = false;
                }
                // 乳がん
                if (!this.isDBNull(reader, "f_乳がん").Equals(this.isDBNull(reader, "s_乳がん")))
                {
                    IsMatch = false;
                }
                // 希望場所
                if (!this.isDBNull(reader, "f_希望場所").Equals(this.isDBNull(reader, "s_希望場所")))
                {
                    IsMatch = false;
                }
                // 身体不自由
                if (!this.isDBNull(reader, "f_身体不自由").Equals(this.isDBNull(reader, "s_身体不自由")))
                {
                    IsMatch = false;
                }
                // 電話番号
                if (!this.isDBNull(reader, "f_電話番号").Equals(this.isDBNull(reader, "s_電話番号")))
                {
                    IsMatch = false;
                }
                // 携帯番号
                if (!this.isDBNull(reader, "f_携帯番号").Equals(this.isDBNull(reader, "s_携帯番号")))
                {
                    IsMatch = false;
                }

                // マッチはフラグを更新する
                if (IsMatch)
                {
                    updateFirst.Parameters.Clear();
                    updateFirst.Parameters.AddWithValue("lot", EditParam.lotNo);
                    updateFirst.Parameters.AddWithValue("serialCode", this.isDBNull(reader, "f_整理番号"));
                    updateFirst.ExecuteNonQuery();

                    updateSecond.Parameters.Clear();
                    updateSecond.Parameters.AddWithValue("lot", EditParam.lotNo);
                    updateSecond.Parameters.AddWithValue("serialCode", this.isDBNull(reader, "f_整理番号"));
                    updateSecond.ExecuteNonQuery();
                }
            }

            updateCon.Close();
            readerCon.Close();
        }

        //検索
        private void button1_Click(object sender, EventArgs e)
        {
            this.setPage();
            this.check();
            this.search();
        }

        //チェック
        private void button2_Click(object sender, EventArgs e)
        {
            this.check();
            this.search();
        }

        private void checkBox1_CheckedChanged(object sender, EventArgs e)
        {
            this.setPage();
            this.search();
        }

        private void checkBox2_CheckedChanged(object sender, EventArgs e)
        {
            this.setPage();
            this.search();
        }

        private void checkBox3_CheckedChanged(object sender, EventArgs e)
        {
            this.setPage();
            this.search();
        }

        //前へ
        private void button3_Click(object sender, EventArgs e)
        {
            if (currentPageNo <= 1)
            {
                return;
            }
            currentPageNo--;
            comboBox1.SelectedIndex = currentPageNo - 1;

            this.search();
        }

        //次へ
        private void button4_Click(object sender, EventArgs e)
        {
            if (currentPageNo >= maxPageNo)
            {
                return;
            }
            currentPageNo++;
            comboBox1.SelectedIndex = currentPageNo - 1;

            this.search();
        }

        //ページジャンプ
        private void comboBox1_SelectionChangeCommitted(object sender, EventArgs e)
        {
            currentPageNo = int.Parse(comboBox1.SelectedItem.ToString());
            this.search();
        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            DataGridView dgv = (DataGridView)sender;
            if (dgv.Columns[e.ColumnIndex].Name == "編集1")
            {
                // 整理番号を取得
                string serialNo = dgv.Rows[e.RowIndex].Cells["f_整理番号"].Value.ToString();
                Global.lotNo = EditParam.lotNo;
                Global.times = "１回目";
                Global.inputUser = EditParam.inputUser;
                if (serialNo != "")
                {
                    Form form2 = new Form2(serialNo);
                    form2.ShowDialog();
                    form2.Dispose();
                }
            }
            if (dgv.Columns[e.ColumnIndex].Name == "編集2")
            {
                // 整理番号を取得
                string serialNo = dgv.Rows[e.RowIndex].Cells["f_整理番号"].Value.ToString();
                Global.lotNo = EditParam.lotNo;
                Global.times = "２回目";
                Global.inputUser = EditParam.inputUser;
                if (serialNo != "")
                {
                    Form form2 = new Form2(serialNo);
                    form2.ShowDialog();
                    form2.Dispose();
                }
            }

        }

    }
}