using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using DevExpress.XtraEditors;

using DevExpress.XtraReports.UI;
using ABC.KetNoi;
using DevExpress.XtraSplashScreen;




namespace ABC.CongNo
{
    public partial class T_KhaiBaoCongNo_KetM1 : DevExpress.XtraEditors.XtraForm
    {
        Class_KetNoi db = new Class_KetNoi();
        public int ngayxem;
        public T_KhaiBaoCongNo_KetM1()
        {
            InitializeComponent();
            simpleButton1.Click += new EventHandler(simpleButton1_Click);
            simpleButton2.Click += new EventHandler(simpleButton2_Click);
            gv.CellValueChanged += new DevExpress.XtraGrid.Views.Base.CellValueChangedEventHandler(gv_CellValueChanged);
            this.Load += new EventHandler(T_KhaiBaoCongNo_SotM_Load);
            link_xoa_ket.Click += new EventHandler(link_xoa_ket_Click);
            link_xoa_sot.Click += new EventHandler(link_xoa_sot_Click);
            link_xoa_lu.Click += new EventHandler(link_xoa_lu_Click);
            link_xoa_cat.Click += new EventHandler(link_xoa_cat_Click);
            link_xoa_bom.Click += new EventHandler(link_xoa_bom_Click);
        }

        void link_xoa_ket_Click(object sender, EventArgs e)
        {
            DataRow dr = gv.GetDataRow(gv.FocusedRowHandle);
            if (msgbox.dialog_yes_no("Xóa két mượn?") == DialogResult.Yes)
            {
                string sql = "Delete from Tbl_KETNO where NgayPhatSinhNo = '" + ngayxem + "' and NguonPhatSinh = 'Muon' and ID_KhachHang = '" + dr["ID_KhachHang"].ToString().ToUpper() + "'";
                db.T_exc(sql);
                tailai();
            }
        }
        void link_xoa_sot_Click(object sender, EventArgs e)
        {
            DataRow dr = gv.GetDataRow(gv.FocusedRowHandle);
            if (msgbox.dialog_yes_no("Xóa sọt mượn?") == DialogResult.Yes)
            {
                string sql = "Delete from Tbl_SOTNO where NgayPhatSinhNo = '" + ngayxem + "' and NguonPhatSinh = 'Muon' and ID_KhachHang = '" + dr["ID_KhachHang"].ToString().ToUpper() + "'";
                db.T_exc(sql);
                tailai();
            }
        }
        void link_xoa_lu_Click(object sender, EventArgs e)
        {
            DataRow dr = gv.GetDataRow(gv.FocusedRowHandle);
            if (msgbox.dialog_yes_no("Xóa lu mượn?") == DialogResult.Yes)
            {
                string sql = "Delete from Tbl_LuNo where NgayPhatSinhNo = '" + ngayxem + "' and NguonPhatSinh = 'Muon' and ID_KhachHang = '" + dr["ID_KhachHang"].ToString().ToUpper() + "'";
                db.T_exc(sql);
                tailai();
            }
        }
        void link_xoa_cat_Click(object sender, EventArgs e)
        {
            DataRow dr = gv.GetDataRow(gv.FocusedRowHandle);
            if (msgbox.dialog_yes_no("Xóa cắt mượn?") == DialogResult.Yes)
            {
                string sql = "Delete from Tbl_CatNo where NgayPhatSinhNo = '" + ngayxem + "' and NguonPhatSinh = 'Muon' and ID_KhachHang = '" + dr["ID_KhachHang"].ToString().ToUpper() + "'";
                db.T_exc(sql);
                tailai();
            }
        }
        void link_xoa_bom_Click(object sender, EventArgs e)
        {
            DataRow dr = gv.GetDataRow(gv.FocusedRowHandle);
            if (msgbox.dialog_yes_no("Xóa bom mượn?") == DialogResult.Yes)
            {
                string sql = "Delete from Tbl_BomNo where NgayPhatSinhNo = '" + ngayxem + "' and NguonPhatSinh = 'Muon' and ID_KhachHang = '" + dr["ID_KhachHang"].ToString().ToUpper() + "'";
                db.T_exc(sql);
                tailai();
            }
        }
        void T_KhaiBaoCongNo_SotM_Load(object sender, EventArgs e)
        {
            tailai();
        }

        void gv_CellValueChanged(object sender, DevExpress.XtraGrid.Views.Base.CellValueChangedEventArgs e)
        {
            DataRow dr = gv.GetDataRow(gv.FocusedRowHandle);
            if (e.Column.FieldName == "TongMuon_ket")
            {
                string sql = "Delete from Tbl_KETNO where NgayPhatSinhNo = '" + ngayxem + "' and NguonPhatSinh = 'Muon' and ID_KhachHang = '" + dr["ID_KhachHang"].ToString().ToUpper() + "'";
                db.T_exc(sql);
                string sql2 = "Tbl_KETNO_insert '" + dr["ID_KhachHang"].ToString().ToUpper() + "','" + Guid.NewGuid().ToString().ToUpper() + "','Muon'," + ngayxem + ",'" + e.Value + "'";
                db.T_exc(sql2);
                tailai();
            }
            if (e.Column.FieldName == "TongMuon_sot")
            {
                string sql = "Delete from Tbl_SOTNO where NgayPhatSinhNo = '" + ngayxem + "' and NguonPhatSinh = 'Muon' and ID_KhachHang = '" + dr["ID_KhachHang"].ToString().ToUpper() + "'";
                db.T_exc(sql);
                string sql2 = "Tbl_SOTNO_insert '" + dr["ID_KhachHang"].ToString().ToUpper() + "','" + Guid.NewGuid().ToString().ToUpper() + "','Muon'," + ngayxem + ",'" + e.Value + "'";
                db.T_exc(sql2);
                tailai();
            }
            if (e.Column.FieldName == "TongMuon_lu")
            {
                string sql = "Delete from Tbl_LuNo where NgayPhatSinhNo = '" + ngayxem + "' and NguonPhatSinh = 'Muon' and ID_KhachHang = '" + dr["ID_KhachHang"].ToString().ToUpper() + "'";
                db.T_exc(sql);
                string sql2 = "Tbl_LuNo_insert '" + dr["ID_KhachHang"].ToString().ToUpper() + "','" + Guid.NewGuid().ToString().ToUpper() + "','Muon'," + ngayxem + ",'" + e.Value + "'";
                db.T_exc(sql2);
                tailai();
            }
            if (e.Column.FieldName == "TongMuon_cat")
            {
                string sql = "Delete from Tbl_CatNo where NgayPhatSinhNo = '" + ngayxem + "' and NguonPhatSinh = 'Muon' and ID_KhachHang = '" + dr["ID_KhachHang"].ToString().ToUpper() + "'";
                db.T_exc(sql);
                string sql2 = "Tbl_CatNo_insert '" + dr["ID_KhachHang"].ToString().ToUpper() + "','" + Guid.NewGuid().ToString().ToUpper() + "','Muon'," + ngayxem + ",'" + e.Value + "'";
                db.T_exc(sql2);
                tailai();
            }
            if (e.Column.FieldName == "TongMuon_bom")
            {
                string sql = "Delete from Tbl_BomNo where NgayPhatSinhNo = '" + ngayxem + "' and NguonPhatSinh = 'Muon' and ID_KhachHang = '" + dr["ID_KhachHang"].ToString().ToUpper() + "'";
                db.T_exc(sql);
                string sql2 = "Tbl_BomNo_insert '" + dr["ID_KhachHang"].ToString().ToUpper() + "','" + Guid.NewGuid().ToString().ToUpper() + "','Muon'," + ngayxem + ",'" + e.Value + "'";
                db.T_exc(sql2);
                tailai();
            }
        }

        void simpleButton2_Click(object sender, EventArgs e)
        {
          
            this.DialogResult = DialogResult.Cancel;
            this.Close();
        }

        void simpleButton1_Click(object sender, EventArgs e)
        {
         
            this.DialogResult = DialogResult.OK;
            this.Close();
        }



        public DataSet dt_main;
    
        #region load_bandau

        public DataTable dt_kh;
        public void tailai()
        {
            //  DataTable dtTemp;
            DataTable dtKH = Main_Form._dtKH;
           
            DataTable dtTotal = new DataTable("Table");
            dtTotal.Columns.Add("STT");
            dtTotal.Columns.Add("ID_KhachHang");
            dtTotal.Columns.Add("Tbl_KhachHang_Name");
            dtTotal.Columns.Add("TongMuon_sot", typeof(int));
            dtTotal.Columns.Add("TongMuon_ket", typeof(int));
            dtTotal.Columns.Add("TongMuon_lu", typeof(int));
            dtTotal.Columns.Add("TongMuon_cat", typeof(int));
            dtTotal.Columns.Add("TongMuon_bom", typeof(int));
            //
            string sql_ket = "select distinct ID_KhachHang,Tbl_KhachHang_Name,sum(TongMuon)as TongMuon from Ket_muon where NgayPhatSinhNo = '" + ngayxem + "' group by ID_KhachHang,Tbl_KhachHang_Name ";
            DataTable dt_ket = db.T_opendataset(sql_ket).Tables[0];
          //  dtTemp = dt_ket;
            //
            string sql_sot = "select distinct ID_KhachHang,Tbl_KhachHang_Name,sum(TongMuon)as TongMuon from Sot_muon where NgayPhatSinhNo = '" + ngayxem + "' group by ID_KhachHang,Tbl_KhachHang_Name ";
            DataTable dt_sot = db.T_opendataset(sql_sot).Tables[0];
           // dtTemp.Merge(dt_sot);
            //
            string sql_lu = "select distinct ID_KhachHang,Tbl_KhachHang_Name,sum(TongMuon)as TongMuon from Lu_muon where NgayPhatSinhNo = '" + ngayxem + "' group by ID_KhachHang,Tbl_KhachHang_Name ";
            DataTable dt_lu = db.T_opendataset(sql_lu).Tables[0];
          //  dtTemp.Merge(dt_lu);
            //
            string sql_cat = "select distinct ID_KhachHang,Tbl_KhachHang_Name,sum(TongMuon)as TongMuon from Cat_muon where NgayPhatSinhNo = '" + ngayxem + "' group by ID_KhachHang,Tbl_KhachHang_Name ";
            DataTable dt_cat = db.T_opendataset(sql_cat).Tables[0];
          //  dtTemp.Merge(dt_cat);
            //
            string sql_bom = "select distinct ID_KhachHang,Tbl_KhachHang_Name,sum(TongMuon)as TongMuon from Bom_muon where NgayPhatSinhNo = '" + ngayxem + "' group by ID_KhachHang,Tbl_KhachHang_Name ";
            DataTable dt_bom = db.T_opendataset(sql_bom).Tables[0];
           // dtTemp.Merge(dt_bom);
            DataView view = dtKH.Copy().DefaultView;
            view.Sort = "ID_KhachHang asc";
            if (view.ToTable().Rows.Count > 0)
            {
                string _ID = "";
                for (int i = 0; i < view.ToTable().Rows.Count; i++)
                {
                   
                        _ID = view.ToTable().Rows[i]["ID_KhachHang"].ToString().Trim();
                        DataRow row = dtTotal.NewRow();
                        row["ID_KhachHang"] = _ID;
                        row["Tbl_KhachHang_Name"] = view.ToTable().Rows[i]["Tbl_KhachHang_Name"].ToString().Trim();
                        #region sot
                        DataView view_sot = dt_sot.Copy().DefaultView;
                        view_sot.RowFilter = "[ID_KhachHang]='" + _ID + "'";
                        if(view_sot.ToTable().Rows.Count>0)
                          row["TongMuon_sot"] = (view_sot.ToTable().Rows[0]["TongMuon"].ToString()=="")?0:double.Parse(view_sot.ToTable().Rows[0]["TongMuon"].ToString());
                        #endregion
                        #region ket
                        DataView view_ket = dt_ket.Copy().DefaultView;
                        view_ket.RowFilter = "[ID_KhachHang]='" + _ID + "'";
                        if (view_ket.ToTable().Rows.Count > 0)
                        row["TongMuon_ket"] =(view_ket.ToTable().Rows[0]["TongMuon"].ToString() == "") ? 0 : double.Parse(view_ket.ToTable().Rows[0]["TongMuon"].ToString());
                        #endregion
                        #region lu
                        DataView view_lu = dt_lu.Copy().DefaultView;
                        view_lu.RowFilter = "[ID_KhachHang]='" + _ID + "'";
                        if (view_lu.ToTable().Rows.Count > 0)
                         row["TongMuon_lu"] = (view_lu.ToTable().Rows[0]["TongMuon"].ToString() == "") ? 0 : double.Parse(view_lu.ToTable().Rows[0]["TongMuon"].ToString());                 
                        #endregion
                        #region cat
                        DataView view_cat = dt_cat.Copy().DefaultView;
                        view_cat.RowFilter = "[ID_KhachHang]='" + _ID + "'";
                        if (view_cat.ToTable().Rows.Count > 0)
                          row["TongMuon_cat"] = (view_cat.ToTable().Rows[0]["TongMuon"].ToString() == "") ? 0 : double.Parse(view_cat.ToTable().Rows[0]["TongMuon"].ToString());
                        #endregion
                        #region bom
                        DataView view_bom = dt_bom.Copy().DefaultView;
                        view_bom.RowFilter = "[ID_KhachHang]='" + _ID + "'";
                        if (view_bom.ToTable().Rows.Count > 0)
                           row["TongMuon_bom"] = (view_bom.ToTable().Rows[0]["TongMuon"].ToString() == "") ? 0 : double.Parse(view_bom.ToTable().Rows[0]["TongMuon"].ToString());
                        #endregion
                        dtTotal.Rows.Add(row);
                        dtTotal.AcceptChanges();
                   
                }
            }
            DataView viewTotal = dtTotal.Copy().DefaultView;
            viewTotal.RowFilter = "TongMuon_sot>0 or TongMuon_ket>0 or TongMuon_lu>0 or TongMuon_bom>0";
            gc.DataSource = viewTotal.ToTable();
           // dt_main.Tables[0].Columns.Add("Select", typeof(int));
           // data();
        }
        public void data()
        {
           // gc.DataSource = dt_main.Tables[0];
           // thongkenodau.Text = clum_tienno.SummaryText;
        }
      
        #endregion
      
        private void gridView1_CellValueChanged(object sender, DevExpress.XtraGrid.Views.Base.CellValueChangedEventArgs e)
        {
            //string col = e.Column.FieldName.ToString();
            //DataRow dtv = gridView1.GetDataRow(gridView1.FocusedRowHandle);
            //string idcapnhat = dtv["ID"].ToString();
            //string tencapnhat = dtv["Ten"].ToString();
            //string KhachHang_INDEX = dtv["KhachHang_INDEX"].ToString();
            //    string text = e.Value.ToString();
            //    string TTF = String.Format("{0:N2}", text).Replace(",", "");
            //    T_Class_form_congno cn = new T_Class_form_congno();
            //    cn.updatecongnodau(T_Remember_sys.Ngay_DN, idcapnhat, tencapnhat, KhachHang_INDEX, text);
                
            //    initview();
            
          
        }

   
      
      
      
    }
}