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 NPOI.HSSF.UserModel;
using System.IO;
using NPOI.SS.UserModel;
using 不动产证保管系统;
using System.Data.SQLite;
using System.Configuration;
using System.Data.Common;

namespace 不动产证管理系统
{
    public partial class Form6 : Form
    {
        public Form6()
        {
            InitializeComponent();
        }
        SQLiteHelper SQLiteHelper = new SQLiteHelper();
        private void Form6_Load(object sender, EventArgs e)
        {
            tabControl1.Controls.Remove(tabPage2);
            tabControl1.Controls.Remove(tabPage3);
            tabControl1.Controls.Remove(tabPage4);
            tabControl1.Controls.Remove(tabPage5);
            tabControl1.Controls.Remove(tabPage6);
        }
        /// <summary>
        /// 登陆
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button1_Click(object sender, EventArgs e)
        {
            DataTable dataTable = new DataTable();
            dataTable = SQLiteHelper.ExecuteQueryDataTable("SELECT 密码 from password");
            
            
            if (textBox2.Text==dataTable.Rows[0][0].ToString())
            {
                MessageBox.Show(dataTable.Rows[0][0].ToString());
                tabControl1.Controls.Add(tabPage2);
                tabControl1.Controls.Add(tabPage3);
                tabControl1.Controls.Add(tabPage4);
                tabControl1.Controls.Add(tabPage6);
                tabControl1.Controls.Add(tabPage5);
                tabControl1.Controls.Remove(tabPage1);
                dataGridView2.DataSource = SQLiteHelper.ExecuteQueryDataTable("select 入库时间,count(*) as 录入笔数 from usertemp where 出库时间 is  null or 出库时间='' group by 入库时间 order by 入库时间 desc ");
                dataGridView2.Columns[0].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
                comboBox1.DataSource = SQLiteHelper.ExecuteQueryDataTable("select 保管箱子 from user group by 保管箱子");
                comboBox1.DisplayMember = "保管箱子";
                

            }
            else
            {
                MessageBox.Show("您输入的密码有误");
            }

            

        }
        /// <summary>
        /// 入库双击表格
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void dataGridView2_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            dataGridView1.Rows.Clear();
            DataTable dataTable = new DataTable();
            dataTable = SQLiteHelper.ExecuteQueryDataTable(@$"SELECT * FROM usertemp  where 入库时间 ='{dataGridView2.Rows[e.RowIndex].Cells[0].Value}'");

            foreach (DataRow item in dataTable.Rows)
            {
                dataGridView1.Rows.Add(item[6], item[1], item[2], item[3], item[7],item[8], item[4]);
            }
            toolStripStatusLabel1.Text = "  入库总数为:" + dataGridView1.RowCount;
           
        }
        /// <summary>
        /// 确认入库
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button2_Click(object sender, EventArgs e)
        {
            foreach (DataGridViewRow item in dataGridView1.Rows)
            {
                if (string.IsNullOrEmpty(item.Cells[4].Value.ToString()) || string.IsNullOrEmpty(item.Cells[5].Value.ToString()))
                {
                    MessageBox.Show("保管箱子和箱子序号不能为空");
                    return;
                }
            }
            using (SQLiteConnection conn=new SQLiteConnection($"Data Source={ConfigurationManager.ConnectionStrings[1].ConnectionString}"))
            {
                conn.Open();
                using(DbTransaction transaction=conn.BeginTransaction())
                {
                    try
                    {
                        using (SQLiteCommand cmd=new SQLiteCommand(conn))
                        {
                            foreach (DataGridViewRow item in dataGridView1.Rows)
                            {
                                cmd.CommandText= $"insert into user(序列号,入库时间,姓名,凭证种类,凭证编号,保管箱子,箱子序号,备注) select (SELECT count(*) FROM user)+1,'{DateTime.Now.ToString("yyy/MM/dd")}','{item.Cells[1].Value}','{item.Cells[2].Value}','{item.Cells[3].Value}','{item.Cells[4].Value}','{item.Cells[5].Value}','{item.Cells[6].Value}' FROM usertemp where 序列号 = '{item.Cells[0].Value}'";
                                cmd.ExecuteNonQuery();
                                cmd.CommandText = $"delete from usertemp where 序列号 = '{item.Cells[0].Value}'";
                                cmd.ExecuteNonQuery();
                                //SQLiteHelper.ExecuteNonQuery($"insert into user(序列号,入库时间,姓名,凭证种类,凭证编号,保管箱子,箱子序号,备注) select (SELECT count(*) FROM user)+1,'{DateTime.Now.ToString("yyy/MM/dd")}',姓名,凭证种类,凭证编号,'{item.Cells[4].Value}','{item.Cells[5].Value}',备注 FROM usertemp where 序列号 = '{item.Cells[0].Value}'");
                                //SQLiteHelper.ExecuteNonQuery($"delete from usertemp where 序列号 = '{item.Cells[0].Value}'");
                            }
                        }
                        transaction.Commit();
                        MessageBox.Show($"入库一共有: {dataGridView1.Rows.Count}条");
                    }
                    catch (Exception)
                    {
                        transaction.Rollback();
                        MessageBox.Show($"入库失败");
                        throw;
                    }
                }
            }

            
            dataGridView2.DataSource = SQLiteHelper.ExecuteQueryDataTable("select 入库时间,count(*) as 录入笔数 from usertemp where 出库时间 is  null or 出库时间='' group by 入库时间 order by 入库时间 desc ");

            dataGridView1.Rows.Clear();


            label1.Text = null;
            label1.Text = "累计凭证数量:" + SQLiteHelper.ExecuteQueryDataTable($"select count(*) from user where 保管箱子 ='{comboBox1.Text}'").Rows[0].ItemArray[0].ToString();

        }
        /// <summary>
        /// 入库右击弹出菜单
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void dataGridView2_CellMouseClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            if (e.Button == MouseButtons.Right)
            {
                if (e.RowIndex >= 0)
                {
                    //弹出操作菜单
                   // contextMenuStrip1.Show(MousePosition.X, MousePosition.Y);
                }
            }


        }
        /// <summary>
        /// 入库审核 右击列表表格删除
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void toolStripMenuItem1_Click(object sender, EventArgs e)
        {

            // MessageBox.Show(e.ToString());
            MessageBox.Show($"将删除入库时间为:{dataGridView2.CurrentRow.Cells[0].Value.ToString()}的记录");
            SQLiteHelper.ExecuteNonQuery($"DELETE FROM usertemp where 入库时间 = '{dataGridView2.CurrentRow.Cells[0].Value}'");
            dataGridView2.DataSource = SQLiteHelper.ExecuteQueryDataTable("select 入库时间,count(*) as 录入笔数 from usertemp where 出库时间 is  null or 出库时间='' group by 入库时间 order by 入库时间 desc ");
            

            dataGridView1.Rows.Clear();
        }

        private void tabControl1_SelectedIndexChanged(object sender, EventArgs e)
        {
            switch (tabControl1.SelectedTab.Text)
            {
                case "入库审核":
                    dataGridView2.DataSource = SQLiteHelper.ExecuteQueryDataTable("select 入库时间,count(*) as 录入笔数 from usertemp where 出库时间 is  null or 出库时间='' group by 入库时间 order by 入库时间 desc ");
                    dataGridView2.Columns[0].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
                    comboBox1.DataSource = SQLiteHelper.ExecuteQueryDataTable("select 保管箱子 from user  group by 保管箱子");
                    comboBox1.DisplayMember = "保管箱子";

                    break;
                case "出库审核":
                    dataGridView3.DataSource = SQLiteHelper.ExecuteQueryDataTable("select 出库时间,count(*) as 录入笔数 from usertemp where 入库时间 is  null or 入库时间='' group by 出库时间 order by 出库时间 desc ");
                    dataGridView3.Columns[0].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;


                    break;
                case "sql全数据修改":
                    comboBox2.SelectedIndex = 0;
                    

                    break;
                case "xls导入导出":

                    break;
                case "密码修改":

                    break;



            }
        }
        /// <summary>
        /// 出库双击
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void dataGridView3_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            dataGridView4.Rows.Clear();
            DataTable dataTable = new DataTable();
            dataTable = SQLiteHelper.ExecuteQueryDataTable(@$"SELECT * FROM usertemp  where 出库时间 ='{dataGridView3.Rows[e.RowIndex].Cells[0].Value}'");

            foreach (DataRow item in dataTable.Rows)
            {
                dataGridView4.Rows.Add(item[6], item[1], item[2], item[3], item[7], item[8]);
            }
            toolStripStatusLabel1.Text = "  出库总数为:" + dataGridView4.RowCount;
        }
        /// <summary>
        /// 出库审核 右击列表表格删除
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void 出库删除ToolStripMenuItem_Click(object sender, EventArgs e)
        {

            

            SQLiteHelper.ExecuteNonQuery($"DELETE FROM usertemp where 出库时间 = '{dataGridView3.CurrentRow.Cells[0].Value}'");
            dataGridView3.DataSource = SQLiteHelper.ExecuteQueryDataTable("select 出库时间,count(*) as 录入笔数 from usertemp where 入库时间 is  null or 入库时间='' group by 出库时间 order by 出库时间 desc ");
            MessageBox.Show($"已删除出库时间为:{dataGridView3.CurrentRow.Cells[0].Value.ToString()}的记录");
            dataGridView4.Rows.Clear();
        }
        /// <summary>
        /// 确认出库
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button3_Click(object sender, EventArgs e)
        {
            using (SQLiteConnection conn=new SQLiteConnection($"Data Source={ConfigurationManager.ConnectionStrings[1].ConnectionString}"))
            {
                conn.Open();
                using (DbTransaction transaction=conn.BeginTransaction())
                {
                    try
                    {
                        using (SQLiteCommand cmd=new SQLiteCommand(conn))
                        {
                            foreach (DataGridViewRow item in dataGridView4.Rows)
                            {
                                //SQLiteHelper.ExecuteNonQuery($"update user set 出库时间 = '{DateTime.Now.ToString("yyyy年MM月dd日")}' where 序列号 = {item.Cells[0].Value}");
                                //SQLiteHelper.ExecuteNonQuery($"delete from usertemp where 序列号 = '{item.Cells[0].Value}'");
                                cmd.CommandText = $"update user set 出库时间 = '{DateTime.Now.ToString("yyyy年MM月dd日")}',备注=ifnull(备注,'null')||'{textBox6.Text}' where 序列号 = {item.Cells[0].Value}";
                                cmd.ExecuteNonQuery();
                                cmd.CommandText = $"delete from usertemp where 序列号 = '{item.Cells[0].Value}'";
                                cmd.ExecuteNonQuery();
                            }
                        }
                        transaction.Commit();
                        MessageBox.Show($"出库一共有: {dataGridView4.Rows.Count}条");
                    }
                    catch (Exception)
                    {
                        transaction.Rollback();
                        MessageBox.Show("出库失败");
                        throw;
                    }
                }
            }
           

            dataGridView3.DataSource = SQLiteHelper.ExecuteQueryDataTable("select 出库时间,count(*) as 录入笔数 from usertemp where 入库时间 is  null or 入库时间='' group by 出库时间 order by 出库时间 desc ");
            dataGridView4.Rows.Clear();
            textBox6.Text = "";


        }

        private void tabPage2_Click(object sender, EventArgs e)
        {

        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            //MessageBox.Show("累计凭证数量:" + SQLiteHelper.ExecuteQueryDataTable($"select count(*) from user where 保管箱子 ='{comboBox1.Text}'").Rows[0].ItemArray[0].ToString());
            label1.Text = null;
            label1.Text = "累计凭证数量:"+ SQLiteHelper.ExecuteQueryDataTable($"select count(*) from user where 保管箱子 ='{comboBox1.Text}'").Rows[0].ItemArray[0].ToString() ;
            
        }

        private void dataGridView1_CellToolTipTextNeeded(object sender, DataGridViewCellToolTipTextNeededEventArgs e)
        {                      

        }
        /// <summary>
        /// sql全局修改－查询
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button4_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrWhiteSpace(textBox1.Text))
            {
                MessageBox.Show("请输入要查询的内容");
                dataGridView5.DataSource = SQLiteHelper.ExecuteQueryDataTable($"select * from user");
                
            }
            else
            {
                if (comboBox2.SelectedIndex==5)
                {
                    dataGridView5.DataSource=SQLiteHelper.ExecuteQueryDataTable($"{textBox1.Text}");
                }
                else
                {
                    dataGridView5.DataSource = SQLiteHelper.ExecuteQueryDataTable($"select * from user where {comboBox2.Text} like '%{textBox1.Text}%'");
                }
                

            }

            toolStripStatusLabel1.Text = "  出库总数为:" + dataGridView5.RowCount;
        }
        /// <summary>
        /// sql全局修改－查询-自定义语句
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void comboBox2_SelectedIndexChanged(object sender, EventArgs e)
        {
            textBox1.Focus();
        }

        private void button5_Click(object sender, EventArgs e)
        {
            DialogResult result=  MessageBox.Show("是否把当前行修改好的数据更新到数据库?","击双可以修改单元格数据",MessageBoxButtons.OKCancel);
            if (result==DialogResult.OK)
            {
                
                foreach (DataGridViewRow item in dataGridView5.SelectedRows)
                {
                    //MessageBox.Show($"{item.Cells}");
                    SQLiteHelper.ExecuteNonQuery($"update user set 入库时间 = '{item.Cells[1].Value}',姓名 = '{item.Cells[2].Value}',凭证种类 = '{item.Cells[3].Value}',凭证编号 = '{item.Cells[4].Value}',保管箱子 = '{item.Cells[5].Value}',箱子序号 ='{item.Cells[6].Value}',出库时间 = '{item.Cells[7].Value}',备注 ='{item.Cells[8].Value}' where 序列号 ='{item.Cells[0].Value}'");
                    
                }
                dataGridView5.DataSource = SQLiteHelper.ExecuteQueryDataTable($"select * from user");

            }
            else
            {
                return;
            }

        }
        /// <summary>
        /// 导出xls
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button7_Click(object sender, EventArgs e)
        {
            SaveFileDialog saveFileDialog = new SaveFileDialog();
            saveFileDialog.Filter = "xls表格文件|*.xls";
            saveFileDialog.RestoreDirectory = true;
            
            if (saveFileDialog.ShowDialog()==DialogResult.OK)
            {
                DataTable dataTable = new DataTable();
                dataTable = SQLiteHelper.ExecuteQueryDataTable("select * from user");
                using (FileStream fileStream=new FileStream(saveFileDialog.FileName,FileMode.Create,FileAccess.ReadWrite))
                {
                    HSSFWorkbook hSSFWorkbook = new HSSFWorkbook();
                    ISheet sheet = hSSFWorkbook.CreateSheet();
                    IRow rows = sheet.CreateRow(0);
                    rows.CreateCell(0).SetCellValue("序列号");
                    rows.CreateCell(1).SetCellValue("入库时间");
                    rows.CreateCell(2).SetCellValue("姓名");
                    rows.CreateCell(3).SetCellValue("凭证种类");
                    rows.CreateCell(4).SetCellValue("凭证编号");
                    rows.CreateCell(5).SetCellValue("保管箱子");
                    rows.CreateCell(6).SetCellValue("箱子序号");
                    rows.CreateCell(7).SetCellValue("出库时间");
                    rows.CreateCell(8).SetCellValue("备注");

                    for (int i = 0; i < dataTable.Rows.Count; i++)
                    {
                        rows= sheet.CreateRow(i+1);
                        rows.CreateCell(0).SetCellValue(dataTable.Rows[i].ItemArray[0].ToString());
                        rows.CreateCell(1).SetCellValue(dataTable.Rows[i].ItemArray[1].ToString());
                        rows.CreateCell(2).SetCellValue(dataTable.Rows[i].ItemArray[2].ToString());
                        rows.CreateCell(3).SetCellValue(dataTable.Rows[i].ItemArray[3].ToString());
                        rows.CreateCell(4).SetCellValue(dataTable.Rows[i].ItemArray[4].ToString());
                        rows.CreateCell(5).SetCellValue(dataTable.Rows[i].ItemArray[5].ToString());
                        rows.CreateCell(6).SetCellValue(dataTable.Rows[i].ItemArray[6].ToString());
                        rows.CreateCell(7).SetCellValue(dataTable.Rows[i].ItemArray[7].ToString());
                        rows.CreateCell(8).SetCellValue(dataTable.Rows[i].ItemArray[8].ToString());
                    }

                    hSSFWorkbook.Write(fileStream);
                }
                MessageBox.Show("导出xls文件成功");
            }

        }
        /// <summary>
        /// 导入
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void  button6_Click(object sender, EventArgs e)
        {
            OpenFileDialog openFileDialog = new OpenFileDialog();
            openFileDialog.Filter = "xls表格文件|*.xls";
            openFileDialog.RestoreDirectory = true;
            if (openFileDialog.ShowDialog()==DialogResult.OK)
            {
                //import(openFileDialog);
                Task.Run(() =>
                {
                    using (FileStream fileStream = new FileStream(openFileDialog.FileName, FileMode.Open, FileAccess.Read))
                    {
                        
                        HSSFWorkbook hSSFWorkbook = new HSSFWorkbook(fileStream);
                        ISheet sheet = hSSFWorkbook.GetSheetAt(0);
                        IRow irows;
                        int sum1 = 0;
                        for (int i = 1; i < sheet.PhysicalNumberOfRows; i++)
                        {
                            irows = sheet.GetRow(i);
                            //MessageBox.Show( irows.GetCell(1).CellStyle.GetDataFormatString());
                            //return;
                            SQLiteHelper.ExecuteNonQuery($"insert into user values('{Convert.ToInt32(SQLiteHelper.ExecuteQueryDataTable("select count(*) from user").Rows[0].ItemArray[0]) + 1}','{irows.GetCell(1)}','{irows.GetCell(2)}','{irows.GetCell(3)}','{irows.GetCell(4)}','{irows.GetCell(5)}','{irows.GetCell(6)}','{irows.GetCell(7)}','{irows.GetCell(8)}')");
                            sum1++;
                            MethodInvoker mi = new MethodInvoker(() =>
                            {
                                button6.Text = $"已导入{sum1}条数据";
                            });
                            this.BeginInvoke(mi);
                        }
                        //MessageBox.Show($"一共导入了{sum1}条数据");
                       // button6.Text = "xls导入";
                        
                    }
                });        

            }
        }
        public static async Task import(OpenFileDialog openFileDialog)
        {

            SQLiteHelper SQLiteHelper = new SQLiteHelper();
            await Task.Run(() =>
            {
                          



            });
        }





        private void button8_Click(object sender, EventArgs e)
        {
     
                if (textBox4.Text== textBox5.Text)
                {
                    SQLiteHelper.ExecuteNonQuery($"update password set 密码 = '{textBox4.Text}'");
                    MessageBox.Show("密码修改成功,请下次用新密码登陆");
                    return;
                }
                else
                {
                    MessageBox.Show("请输入两次一样的密码");
                }
          


        }
    }
}