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 office_12 = Microsoft.Office.Core;
using Microsoft.Office.Interop.Excel;
using excel_12 = Microsoft.Office.Interop.Excel;
using System.Data.SqlClient;


using Excel = Microsoft.Office.Interop.Excel;
using System.IO;

namespace DXApplication1
{
    public partial class frmTKHocSinh : Form
    {
        public frmTKHocSinh()
        {
            InitializeComponent();
        }

        private SqlConnection conn;
        private SqlCommand cmd;
        private SqlDataAdapter adapter;
        private SqlDataReader re;
        private DataSet dtset;



        private class clsCom
        {
            private string Ten;
            private string Ma;

            public clsCom(string Ma, string Ten)
            {
                this.Ten1 = Ten;
                this.Ma1 = Ma;
            }

            public string Ten1
            {
                get
                {
                    return Ten;
                }

                set
                {
                    Ten = value;
                }
            }

            public string Ma1
            {
                get
                {
                    return Ma;
                }

                set
                {
                    Ma = value;
                }
            }

        }

        private void LoadComboBox()
        {
            conn.Open();
            String lenh = "SELECT * FROM LOP";

            cmd = new SqlCommand(lenh, conn);

            re = cmd.ExecuteReader();
            clsCom cbx;
            /* Trong khi DataReader đọc dữ liệu */
            while (re.Read())
            {
                /* gán 2 giá trị là Tên và Mã Lớp vào ComboBox*/
                cbx = new clsCom(re["MaLop"].ToString(), re["TenLop"].ToString());
                cbxMaLop.Items.Add(cbx);

            }

            /* ComboBox hiển thị Tên lớp*/
            cbxMaLop.DisplayMember = "Ten1";
            /* ComboBox ẩn Mã lớp*/
            cbxMaLop.ValueMember = "Ma1";

            /*Đóng kết nối*/
            conn.Close();
            re.Close();

        }

        

        private void frmTK_HocSinh_Load(object sender, EventArgs e)
        {
            conn = new SqlConnection(@"Data Source=DESKTOP-QTMF8KH\SQLEXPRESS;Initial Catalog=QLHSTHPT;Integrated Security=True");
            
            LoadComboBox();


            this.dataGridView1.DefaultCellStyle.ForeColor = Color.Black;
            this.dataGridView1.DefaultCellStyle.BackColor = Color.Beige;
        }

        private void cbxMaLop_SelectedValueChanged(object sender, EventArgs e)
        {

        }

        private void cbxMaLop_SelectedIndexChanged(object sender, EventArgs e)
        {
            /*Đối tượng ComboBox*/
            clsCom cb = (clsCom)cbxMaLop.SelectedItem;
            /*Lấy được Mã lớp*/
            string maLop = cb.Ma1;


            conn.Open();
            cmd = new SqlCommand("SELECT HOCSINH.MaHocSinh AS N'Mã học sinh', HOCSINH.HoTen AS N'Họ và tên', HOCSINH.GioiTinh AS N'Giới tính', HOCSINH.NgaySinh AS N'Ngày sinh', HOCSINH.NoiSinh AS N'Nơi sinh' FROM HOCSINH INNER JOIN LOP ON HOCSINH.MaLop = LOP.MaLop AND LOP.MaLop = '" + maLop + "'", conn);

            /*LOP.TenLop AS N'Tên lớp'*/
            dtset = new DataSet();
            SqlDataAdapter sda = new SqlDataAdapter(cmd);

            sda.Fill(dtset);
            dataGridView1.DataSource = dtset.Tables[0];

            conn.Close();
        }

        private void dataGridView1_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e)
        {
            
            using (SolidBrush b = new SolidBrush(dataGridView1.RowHeadersDefaultCellStyle.ForeColor))
            {
                e.Graphics.DrawString((e.RowIndex + 1).ToString(), e.InheritedRowStyle.Font, b, e.RowBounds.Location.X + 10, e.RowBounds.Location.Y + 4);
            }
        }

        private void btnExel_Click(object sender, EventArgs e)
        {
            Microsoft.Office.Interop.Excel.Application Excel = new Microsoft.Office.Interop.Excel.Application();

            Workbook wb = Excel.Workbooks.Add(XlSheetType.xlWorksheet);
            Worksheet ws = (Worksheet)Excel.ActiveSheet;
            

            Excel.Visible = true;

            for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
            {
                ws.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
            }

            for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
            {
                for (int j = 0; j < dataGridView1.Columns.Count; j++)
                {
                    ws.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
                }
            }
        }

        private void dataGridView1_DataBindingComplete(object sender, DataGridViewBindingCompleteEventArgs e)
        {

        }


       

        private void btnTimKiem_Click(object sender, EventArgs e)
        {
            


        }
    }
}