// 下水データ管理システム
// Copyright (C) 2018 公益財団法人とちぎ建設技術センター(https://www.tochigictc.or.jp/)
//
// This program is free software: you can redistribute it and/or modify
// it under the terms of the GNU General Public License as published by
// the Free Software Foundation, either version 3 of the License, or
// (at your option) any later version.
//
// This program is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.See the
// GNU General Public License for more details.
//
// You should have received a copy of the GNU General Public License
// along with this program.If not, see<http://www.gnu.org/licenses/>.

using ClosedXML.Excel;
using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Drawing;
using System.Windows.Forms;

namespace WindowsFormsApplication1 {
    public partial class Form31PrintPreviewPrintForm : Form30PrintPreviewBase {
        public Form31PrintPreviewPrintForm() {
            InitializeComponent();
        }


        public DataGridView dataGridView1 { get; set; }
        public ComboBox comboBox1 { get; set; }
        public ComboBox comboBox2 { get; set; }

        List<int> printColumnsNo = new List<int>();
        string printFromName = "";



        //ロード時イベント
        private void Form31PrintPreviewPrintForm_Load(object sender, EventArgs e) {
            makeInputForm();
            viewPrintPreview("printform1");
        }




        //入力フォーム作成
        private void makeInputForm() {
            for(int i = 0; i < MaxSizeConfig.maxPrintForm; i++) {
                Button newButton = new Button();
                newButton.Name = "printform" + (i + 1).ToString();
                newButton.Text = "帳票" + (i + 1).ToString();
                newButton.Size = new Size(85, 25);
                newButton.BackColor = Color.White;
                newButton.Font = new Font("メイリオ", 9F, FontStyle.Regular, GraphicsUnit.Point, ((byte)(128)));
                newButton.Top = i * 30 + 10;
                newButton.Left = 9;
                newButton.Click += new EventHandler(selectButtonClick);

                panel1.Controls.Add(newButton);
            }

            FunctionForDatabase.setPrintFormRow();

            using(var con = new SQLiteConnection(DataBaseConfig.ConnectionString)) {
                con.Open();

                using(var cmd = con.CreateCommand()) {
                    cmd.CommandText = "SELECT form_no, name FROM print_form_setting ORDER BY form_no;";

                    using(var reader = cmd.ExecuteReader()) {
                        while(reader.Read()) {
                            int form_no = reader.GetInt32(0);
                            string name = reader.GetString(1);

                            Label newLabel = new Label();
                            newLabel.Text = name;
                            newLabel.Font = new Font("メイリオ", 9F, FontStyle.Regular, GraphicsUnit.Point, ((byte)(128)));
                            newLabel.AutoSize = true;
                            newLabel.Top = (form_no - 1) * 30 + 14;
                            newLabel.Left = 100;

                            panel1.Controls.Add(newLabel);
                        }
                    }
                }
            }
        }




        //帳票選択ボタンクリックイベント
        private void selectButtonClick(object sender, EventArgs e) {
            Button b = (Button)sender;
            string printFrom = b.Name;

            viewPrintPreview(printFrom);
        }




        //印刷プレビューの表示
        private void viewPrintPreview(string printFrom) {
            PrintContainer.printType = "Datalist";
            PrintContainer.dataGridView1 = dataGridView1;
            PrintContainer.comboBox1 = comboBox1;
            PrintContainer.comboBox2 = comboBox2;

            printColumnsNo.Clear();

            int printWidth = 0;

            if(printFrom == "all") {
                printFromName = "全列";

                int j = 0;
                foreach(DataGridViewColumn column in dataGridView1.Columns) {
                    if(column.Name != "id" && column.Name != "kanrisya" && column.Name != "syorijou") {
                        printColumnsNo.Add(j);
                        printWidth += column.Width;
                    }

                    j++;
                }
            }
            else {
                printFromName = "帳票" + printFrom.Replace("printform", "");

                //DBから印刷列を取得
                printColumnsNo.Add(ColumnsConfigSet.nameToIndex("sokutei_dt"));
                printWidth += dataGridView1.Columns["sokutei_dt"].Width;

                using(var con = new SQLiteConnection(DataBaseConfig.ConnectionString)) {
                    con.Open();

                    using(var cmd = con.CreateCommand()) {

                        cmd.CommandText = "SELECT column_name FROM suishitsu_kanri_setting WHERE " + printFrom + " = 1;";

                        using(var reader = cmd.ExecuteReader()) {
                            while(reader.Read()) {
                                string column_name = reader.GetString(0);

                                printColumnsNo.Add(ColumnsConfigSet.nameToIndex(column_name));
                                printWidth += dataGridView1.Columns[column_name].Width;
                            }
                        }
                    }
                }
            }

            PrintContainer.printColumnsNo = printColumnsNo;
            PrintContainer.printWidth = printWidth;

            makePreview();
        }




        //エクセル出力ボタンクリックイベント
        private void outputExcel(object sender, EventArgs e) {
            SaveFileDialog saveFileDialog1 = new SaveFileDialog();

            KanrisyaSyorijou item1 = (KanrisyaSyorijou)comboBox1.SelectedItem;
            string kanrisya = item1.primaryKey(0);
            string syorijou = item1.primaryKey(1);

            SelectableMonth item2 = (SelectableMonth)comboBox2.SelectedItem;
            int cDtY = item2.primaryKey(0);
            int cDtM = item2.primaryKey(1);

            string kikan;

            if(cDtY < 1 || cDtM < 1) {
                kikan = "全期間";
            }
            else {
                kikan = ("0000" + cDtY.ToString()).Substring(cDtY.ToString().Length, 4) + ("00" + cDtM.ToString()).Substring(cDtM.ToString().Length, 2);
            }

            string fileName = printFromName + "_" + kanrisya + "_" + syorijou + "_" + kikan;
            string fileNameExtension = "xlsx";

            saveFileDialog1.FileName = fileName + "." + fileNameExtension;
            saveFileDialog1.InitialDirectory = @"%USERPROFILE%\Desktop";
            saveFileDialog1.Filter = "Excel ブック(*.xlsx)|*.xlsx";
            saveFileDialog1.FilterIndex = 1;
            saveFileDialog1.Title = "帳票出力(エクセル形式)";
            saveFileDialog1.RestoreDirectory = true;
            saveFileDialog1.OverwritePrompt = true;
            saveFileDialog1.CheckPathExists = true;

            //ダイアログの表示
            if(saveFileDialog1.ShowDialog() == DialogResult.OK) {
                FormA2ProgressSplash progressSplash1 = new FormA2ProgressSplash();
                progressSplash1.Owner = this;
                progressSplash1.Show();
                progressSplash1.Refresh();

                fileName = saveFileDialog1.FileName;
                fileNameExtension = fileName.Substring(fileName.Length - 5);

                if(fileNameExtension != ".xlsx") {
                    fileName += ".xlsx";
                }

                XLWorkbook workbook1 = new XLWorkbook();

                IXLWorksheet worksheet1 = workbook1.Worksheets.Add("Sheet1");

                //見出し行
                int R = 1;
                int C = 0;

                foreach(int j in printColumnsNo) {
                    C++;
                    worksheet1.Cell(R, C).SetValue(dataGridView1.Columns[j].HeaderText);
                    worksheet1.Cell(R, C).SetDataType(XLDataType.Text);
                    worksheet1.Cell(R, C).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                    worksheet1.Cell(R, C).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                    worksheet1.Cell(R, C).Style.Font.Bold = true;
                }

                //データ行
                decimal stepPer100 = 100M / dataGridView1.RowCount;
                decimal setpIntegrate = 0;

                for(int i = 0; i < dataGridView1.RowCount; i++) {
                    R = i + 2;
                    C = 0;

                    setpIntegrate += stepPer100;

                    if(setpIntegrate >= 10) {
                        progressSplash1.ProgressMessage = "データ出力中（" + (Math.Round((i + 1M) / dataGridView1.ColumnCount * 10M, 0) * 10).ToString() + "%）";
                        Application.DoEvents();
                        setpIntegrate = 0;
                    }

                    foreach(int j in printColumnsNo) {
                        C++;

                        float cW = dataGridView1.Columns[j].Width;
                        string cA = dataGridView1.Columns[j].DefaultCellStyle.Alignment.ToString();
                        string cF = dataGridView1.Columns[j].DefaultCellStyle.Format.ToString();
                        string cV = dataGridView1.Rows[i].Cells[j].Value.ToString();
                        DateTime cVT;
                        decimal cVD;

                        if(cF == "yyyy/MM/dd ddd HH:mm:ss") {
                            if(DateTime.TryParse(cV, out cVT)) {
                                worksheet1.Cell(R, C).Value = cVT;
                                worksheet1.Cell(R, C).SetDataType(XLDataType.DateTime);
                                worksheet1.Cell(R, C).Style.NumberFormat.Format = "yyyy/MM/dd aaa HH:mm:ss";
                            }
                        }
                        else if(cF == "yyyy/MM/dd ddd") {
                            if(DateTime.TryParse(cV, out cVT)) {
                                worksheet1.Cell(R, C).Value = cVT;
                                worksheet1.Cell(R, C).SetDataType(XLDataType.DateTime);
                                worksheet1.Cell(R, C).Style.NumberFormat.Format = "yyyy/MM/dd aaa";
                            }
                        }
                        else if(decimal.TryParse(cV, out cVD)) {
                            worksheet1.Cell(R, C).Value = cVD;

                            if(cF == "N0") {
                                worksheet1.Cell(R, C).Style.NumberFormat.Format = "#,##0";
                            }
                            else if(cF == "N1") {
                                worksheet1.Cell(R, C).Style.NumberFormat.Format = "#,##0.0";
                            }
                            else if(cF == "N2") {
                                worksheet1.Cell(R, C).Style.NumberFormat.Format = "#,##0.00";
                            }
                            else if(cF == "N3") {
                                worksheet1.Cell(R, C).Style.NumberFormat.Format = "#,##0.000";
                            }
                            else if(cF == "N4") {
                                worksheet1.Cell(R, C).Style.NumberFormat.Format = "#,##0.0000";
                            }
                            else if(cF == "N5") {
                                worksheet1.Cell(R, C).Style.NumberFormat.Format = "#,##0.00000";
                            }
                        }
                        else {
                            worksheet1.Cell(R, C).SetValue(cV);
                            worksheet1.Cell(R, C).SetDataType(XLDataType.Text);
                        }

                        if(cA == "MiddleCenter") {
                            worksheet1.Cell(R, C).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                            worksheet1.Cell(R, C).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                        }
                        else if(cA == "MiddleRight") {
                            worksheet1.Cell(R, C).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
                            worksheet1.Cell(R, C).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                        }
                        else {
                            worksheet1.Cell(R, C).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
                            worksheet1.Cell(R, C).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                        }
                    }
                }

                progressSplash1.ProgressMessage = "ワークシート成型中";
                Application.DoEvents();

                worksheet1.Range(1, 1, R, C).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
                worksheet1.Range(1, 1, R, C).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
                worksheet1.Range(1, 1, R, C).Style.Font.SetFontSize(9.0).Font.SetFontName("Meiryo UI");
                worksheet1.ColumnsUsed().AdjustToContents();

                progressSplash1.ProgressMessage = "ファイル保存中";
                Application.DoEvents();

                try {
                    workbook1.SaveAs(fileName);
                    MessageBox.Show("帳票のエクセル出力が完了しました");
                }
                catch {
                    MessageBox.Show("ファイルが使用中等の理由により、保存できませんでした");
                }

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

    }
}
