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;
using System.IO;

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

            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 button1_Click(object sender, EventArgs e)
        {
            this.setPage();
            this.search();
        }

        //チェック
        private void button2_Click(object sender, EventArgs e)
        {
            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();
                }
            }

        }

        //出力
        private void button5_Click(object sender, EventArgs e)
        {
            //最初の画面で選択したロット番号のデータをファイル出力する
            string connstr = System.Configuration.ConfigurationManager.AppSettings["Conn"];
            MySqlConnection readerCon = new MySqlConnection(connstr);
            readerCon.Open();

            string readQuery = "select * from `１回目` where (`ロット番号` = @lotNo) order by `保健区`";
            MySqlCommand readCmd = new MySqlCommand(readQuery, readerCon);
            readCmd.Parameters.AddWithValue("lotNo", EditParam.lotNo);

            try
            {
                // 実行
                MySqlDataReader reader = readCmd.ExecuteReader();
                if (!reader.HasRows)
                {   //無かった
                    reader.Close();
                }
                else
                {
                    StreamWriter writer = new StreamWriter(Define.OUTFILE, false, System.Text.Encoding.GetEncoding("shift_jis")); //ファイルストリーム
                    while (reader.Read())
                    {
                        string line = makeRecord(reader);
                        writer.WriteLine(line);
                    }

                    writer.Close();
                    reader.Close();

                }
                // 完了
                MessageBox.Show("データを出力しました");
            }
            catch (SqlException ex)
            {
                // 例外処理
                MessageBox.Show("例外発生:" + ex.Message);
            }

        }

        private string makeRecord(MySqlDataReader reader)
        {
            string line = "";

            line += this.isDBNull(reader, "整理番号").PadLeft(10,'0');  //整理番号
            line += this.isDBNull(reader, "世帯番号").PadLeft(10,'0');  //世帯番号
            line += this.isDBNull(reader, "健康診査").PadRight(1);  //健康診査
            line += this.isDBNull(reader, "結核肺がん").PadRight(1);  //結核肺がん
            line += this.isDBNull(reader, "胃がん").PadRight(1);  //胃がん
            line += this.isDBNull(reader, "大腸がん").PadRight(1);  //大腸がん
            line += this.isDBNull(reader, "前立腺がん").PadRight(1);  //前立腺がん
            line += this.isDBNull(reader, "骨粗鬆症").PadRight(1);  //骨粗鬆症
            line += this.isDBNull(reader, "成人歯科").PadRight(1);  //成人歯科
            line += this.isDBNull(reader, "子宮頸がん").PadRight(1);  //子宮頸がん
            line += this.isDBNull(reader, "乳がん").PadRight(1);  //乳がん
            line += this.isDBNull(reader, "希望場所").PadRight(1);  //希望場所
            line += this.isDBNull(reader, "身体不自由").PadRight(1);  //身体不自由
            line += this.isDBNull(reader, "電話番号").PadRight(15);  //電話番号
            line += this.isDBNull(reader, "携帯番号").PadRight(15);  //携帯番号

            return line;
        }

        //行政区サマリ
        public class SummaryAdmin
        {
            public string adminCode;
            public string adminName;
            public int count;
        }

        //行政区集計
        private void makeSummaryAdmin(MySqlConnection readerCon)
        {
            string readQuery = "select `行政区コード`,`行政区名称`,count(行政区コード) from `１回目` where `ロット番号` = @lotNo group by `行政区コード` order by `行政区コード`";
            MySqlCommand readCmd = new MySqlCommand(readQuery, readerCon);
            readCmd.Parameters.AddWithValue("lotNo", EditParam.lotNo);

            string countQuery = "select count(`世帯番号`) from `１回目` where `行政区コード`= @adminCode and `ロット番号` = @lotNo group by `行政区コード`";
            MySqlCommand countCmd = new MySqlCommand(countQuery, readerCon);

            try
            {
                // 実行
                MySqlDataReader reader = readCmd.ExecuteReader();
                if (!reader.HasRows)
                {   //無かった
                    reader.Close();
                }
                else
                {
                    List<SummaryAdmin> adminList = new List<SummaryAdmin>();
                    while (reader.Read())
                    {
                        SummaryAdmin tmp = new SummaryAdmin();
                        tmp.adminCode = this.isDBNull(reader, "行政区コード");
                        tmp.adminName = this.isDBNull(reader, "行政区名称");
                        tmp.count = reader.GetInt16(2);
                        adminList.Add(tmp);
                    }
                    reader.Close();

                    StreamWriter writer = new StreamWriter("SummaryAdmin.csv", false, System.Text.Encoding.GetEncoding("shift_jis")); //ファイルストリーム

                    foreach (SummaryAdmin sa in adminList)
                    {
                        countCmd.Parameters.Clear();
                        countCmd.Parameters.AddWithValue("adminCode", sa.adminCode);
                        countCmd.Parameters.AddWithValue("lotNo", EditParam.lotNo);
                        MySqlDataReader reader2 = countCmd.ExecuteReader();
                        while (reader2.Read())
                        {
                            int count2 = reader2.GetInt16(0);

                            string line = sa.adminCode + "," + sa.adminName + "," + sa.count + "," + count2;
                            writer.WriteLine(line);
                        }
                        reader2.Close();
                    }

                    writer.Close();

                }
            }
            catch (SqlException ex)
            {
                // 例外処理
                MessageBox.Show("例外発生:" + ex.Message);
            }

        }

        //保健区集計
        private void makeSummaryHealthArea(MySqlConnection readerCon)
        {
            string getQuery = "select `保健区` from `１回目` where `ロット番号` = @lotNo group by `保健区`";
            MySqlCommand getCmd = new MySqlCommand(getQuery, readerCon);
            getCmd.Parameters.AddWithValue("lotNo", EditParam.lotNo);

            string readQuery = "select `行政区コード`,`行政区名称`,count(行政区コード) from `１回目` where (`ロット番号` = @lotNo) and (`保健区` = @healthArea) group by `行政区コード` order by `行政区コード`";
            MySqlCommand readCmd = new MySqlCommand(readQuery, readerCon);

            string countQuery = "select count(`世帯番号`) from `１回目` where (`行政区コード`= @adminCode) and (`ロット番号` = @lotNo) and (`保健区` = @healthArea) group by `行政区コード`";
            MySqlCommand countCmd = new MySqlCommand(countQuery, readerCon);

            try
            {
                MySqlDataReader reader3 = getCmd.ExecuteReader();
                List<string> healthAreaList = new List<string>();
                if (!reader3.HasRows)
                {   //無かった
                    reader3.Close();
                    return;
                }
                else
                {
                    while (reader3.Read())
                    {
                        healthAreaList.Add(isDBNull(reader3, "保健区"));
                    }
                    reader3.Close();
                }

                foreach (string ha in healthAreaList)
                {
                    readCmd.Parameters.Clear();
                    readCmd.Parameters.AddWithValue("lotNo", EditParam.lotNo);
                    readCmd.Parameters.AddWithValue("healthArea", ha);
                    // 実行
                    MySqlDataReader reader = readCmd.ExecuteReader();
                    if (!reader.HasRows)
                    {   //無かった
                        reader.Close();
                    }
                    else
                    {
                        List<SummaryAdmin> adminList = new List<SummaryAdmin>();
                        while (reader.Read())
                        {
                            SummaryAdmin tmp = new SummaryAdmin();
                            tmp.adminCode = this.isDBNull(reader, "行政区コード");
                            tmp.adminName = this.isDBNull(reader, "行政区名称");
                            tmp.count = reader.GetInt16(2);
                            adminList.Add(tmp);
                        }
                        reader.Close();

                        StreamWriter writer = new StreamWriter("SummaryHealthArea_" + ha + ".csv", false, System.Text.Encoding.GetEncoding("shift_jis")); //ファイルストリーム

                        foreach (SummaryAdmin sa in adminList)
                        {
                            countCmd.Parameters.Clear();
                            countCmd.Parameters.AddWithValue("adminCode", sa.adminCode);
                            countCmd.Parameters.AddWithValue("lotNo", EditParam.lotNo);
                            countCmd.Parameters.AddWithValue("healthArea", ha);
                            MySqlDataReader reader2 = countCmd.ExecuteReader();
                            while (reader2.Read())
                            {
                                int count2 = reader2.GetInt16(0);

                                string line = sa.adminCode + "," + sa.adminName + "," + sa.count + "," + count2;
                                writer.WriteLine(line);
                            }
                            reader2.Close();
                        }

                        writer.Close();

                    }
                }
            }
            catch (SqlException ex)
            {
                // 例外処理
                MessageBox.Show("例外発生:" + ex.Message);
            }

        }


        //集計
        private void button6_Click(object sender, EventArgs e)
        {
            string connstr = System.Configuration.ConfigurationManager.AppSettings["Conn"];
            MySqlConnection readerCon = new MySqlConnection(connstr);
            readerCon.Open();

            makeSummaryHealthArea(readerCon);

            makeSummaryAdmin(readerCon);

            readerCon.Close();

            // 完了
            MessageBox.Show("集計を出力しました");

        }

    }
}