using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;

using System.Xml;
using System.IO;
using ABC.DanhMuc.NCC;
using System.Data;
using ABC.DanhMuc.NhomNCC;
using ABC.File;
using DevExpress.XtraSpreadsheet;
using DevExpress.XtraEditors;
using ABC.KetNoi;

namespace ABC.DanhMuc.NCC
{
    
    public partial class NCC_Forms : DevExpress.XtraEditors.XtraUserControl {
        Main_Form _parent;
        public NCC_Forms()
        {           
            InitializeComponent();
            this.Text = "Danh mục thương lái";
            clsConnect cn = new clsConnect();
            repositoryItemNhomToa.DataSource = cn.LoadTable("select * from Tbl_NhomToa");
            repositoryItemNhomToa.DisplayMember = "TenNhomToa";
            repositoryItemNhomToa.ValueMember = "IDNhomToa";
            event_click();
            _NhomNCC_Class.get_store("sp_NhomNCC1_Load");
           // data();
            gv.CellValueChanged += new DevExpress.XtraGrid.Views.Base.CellValueChangedEventHandler(gv_CellValueChanged);
        }
        
        #region gv_value_changed
        void gv_CellValueChanged(object sender, DevExpress.XtraGrid.Views.Base.CellValueChangedEventArgs e)
        {
            if (e.Column.FieldName != "Select")
            {
                DataRow dr = gv.GetDataRow(gv.FocusedRowHandle);
                if (e.Column.FieldName != "Name")
                {
                    save(dr);
                }
                else
                {
                    DataRow[] dr_name = DuLieu.List_NCC.Tables[0].Select("Name = '" + e.Value.ToString().ToUpper() + "' AND ID <> '" + dr["ID"].ToString().ToUpper() + "'");
                    if (dr_name.Length > 0)
                    {
                        msgbox.dialog_OK_Questios("Trùng tên!");
                        gv.CancelUpdateCurrentRow();
                    }
                    else
                    {
                        save(dr);
                    }
                }
            }
        }
        private void gv_CellValueChanging(object sender, DevExpress.XtraGrid.Views.Base.CellValueChangedEventArgs e)
        {
            //if (e.Column.FieldName == "Select")
            //{
            //    DataRow dr = gv.GetDataRow(gv.FocusedRowHandle);
            //    save(dr);
            //}    
        }
        public void save(DataRow dr)
        {
            if (Pow.kiemtra("NCC_Forms", "Sua",Get_CauHinh.Dt_Login.Rows[0]["ID"].ToString().ToUpper()) == true)
            {
                #region
                try
                {
                    DataRowView row = repositoryItemLookUpEdit_ID1.GetDataSourceRowByKeyValue(dr["_ID1_Name"].ToString()) as DataRowView;
                    DataRowView row1 = repositoryItemNhomToa.GetDataSourceRowByKeyValue(dr["IDNhomToa"].ToString()) as DataRowView;
                    string sql = "Tbl_NCC_insert2019 ";
                    sql += "N'" + dr["ID"].ToString().ToUpper() + "',";
                    sql += "N'" + dr["Name"].ToString().ToUpper() + "',";
                    sql += "N'" + dr["Note"].ToString() + "',";

                    sql += "N'" + Get_CauHinh.Ngay_DN + "',";
                    sql += "N'" + Get_CauHinh.Dt_Login.Rows[0]["ID"].ToString().ToUpper() + "',";
                    if (dr["IS_User"].ToString() == "True")
                    {
                        sql += "N'1',";
                    }
                    else
                    {
                        sql += "N'0',";
                    }

                    sql += "N'" + dr["_Text1"].ToString() + "',";
                    sql += "N'" + dr["_Text2"].ToString() + "',";
                    sql += "N'" + dr["_Text3"].ToString() + "',";
                    sql += "N'" + dr["_Text4"].ToString() + "',";
                    sql += "N'',";
                    sql += "N'',";
                    sql += "N'',";
                    sql += "N'',";
                    sql += "N'',";

                    sql += "NULL,";
                    sql += "NULL,";
                    sql += "NULL,";

                    sql += "N'" + row["ID"].ToString().ToUpper() + "',";
                    sql += "NULL,";
                    sql += "NULL,";
                    sql += "N'" + dr["Mail"].ToString() + "',";
                    sql += "N'" + dr["Zalo"].ToString() + "',";
                    string _IDToa = (row1 == null) ? "0" : row1["IDNhomToa"].ToString().ToUpper();
                    sql += "N'" + _IDToa + "'";

                    _NCC_Class.set(sql);
                    Class_KetNoi cls = new Class_KetNoi();
                    DataTable dt = cls.T_opendataset("select * from Tbl_NCC where ID='" + dr["ID"].ToString() + "'").Tables[0];
                    string ndCu = "", ndMoi = "";
                    if (dt.Rows.Count > 0)
                    {
                        for (int i = 0; i < dt.Columns.Count; i++)
                        {
                            ndCu += dt.Columns[i].ColumnName + "(" + dt.Rows[0][dt.Columns[i].ColumnName].ToString().Trim() + ") ";
                            ndMoi += dt.Columns[i].ColumnName + "(" + dr[dt.Columns[i].ColumnName].ToString().Trim() + ") ";
                        }
                    }
                    DuLieu.LuuLuocSu(Main_Form._tk, this.Name, ndCu, ndMoi, "Sửa");
                    tailai();
                    data();
                }
                catch (Exception)
                {

                }
              



                #endregion
            }
            else
            {
                gv.CancelUpdateCurrentRow();
            }
        }
        #endregion

        #region lệnh dùng chung
        NCC_Form _NCC_Form;

        public void them()
        {
            if (Pow.kiemtra("NCC_Forms", "Them", Get_CauHinh.Dt_Login.Rows[0]["ID"].ToString().ToUpper()) == true)
            {
                _NCC_Form = new NCC_Form();
                _NCC_Form.TextChanged += new EventHandler(_NCC_Form_TextChanged);
                _NCC_Form.FormClosed += new FormClosedEventHandler(_NCC_Form_FormClosed);
                _NCC_Form.ShowDialog();
            }
        }

        void _NCC_Form_FormClosed(object sender, FormClosedEventArgs e)
        {
            tailai();
        } 
        void _NCC_Form_TextChanged(object sender, EventArgs e)
        {
            tailai();
            gv.RefreshData();

        }
        public void excel()
        {
        }
        public void xoa()
        {
            if (Pow.kiemtra("NCC_Forms", "Xoa", Get_CauHinh.Dt_Login.Rows[0]["ID"].ToString().ToUpper()) == true)
            {
                if (msgbox.dialog_yes_no("Xóa dòng chọn?") == DialogResult.Yes)
                {
                    //  DataRow[] dr = DuLieu.List_NCC.Tables[0].Select("Select = " + true + "");
                    // for (int i = 0; i < dr.Length; i++)
                    try
                    {
                        for (int i = 0; i < gv.RowCount; i++)
                        {
                            if (gv.GetRowCellValue(i, "ID") != null)
                            {
                                string _ID = gv.GetRowCellValue(i, "ID").ToString().ToUpper().Trim();
                                string _Chon = gv.GetRowCellValue(i, "Select").ToString().ToUpper().Trim();
                                if (_Chon == "TRUE")
                                {
                                    string sql = "Delete from Tbl_NCC where ID = '" + _ID + "'";
                                    _NCC_Class.set(sql);
                                    //
                                    string ndCu = "";
                                    for (int j = 0; j < gv.Columns.Count; j++)
                                    {
                                        ndCu += gv.Columns[j].FieldName + "(" + gv.GetRowCellValue(i, gv.Columns[j].FieldName).ToString().Trim() + ") ";
                                    }
                                    DuLieu.LuuLuocSu(Main_Form._tk, this.Name, ndCu, "", "Xoá");
                                }
                            }
                        }
                    }
                    catch (Exception)
                    {

                    }
                   
                    tailai();
                    data();

                }
            }
        }
        public void sua()
        {
        }       
        public void chon()
        {
            for (int i = 0; i < DuLieu.List_NCC.Tables[0].Rows.Count; i++)
            {
                DuLieu.List_NCC.Tables[0].Rows[i]["Select"] = true;
            }
            DuLieu.List_NCC.Tables[0].AcceptChanges();
            gv.RefreshData();
        }
        public void bochon()
        {
            for (int i = 0; i < DuLieu.List_NCC.Tables[0].Rows.Count; i++)
            {
                DuLieu.List_NCC.Tables[0].Rows[i]["Select"] = false;
            }
            DuLieu.List_NCC.Tables[0].AcceptChanges();
            gv.RefreshData();
        }
        public void chep()
        {
            DataRow dr = gv.GetDataRow(gv.FocusedRowHandle);
            if (Pow.kiemtra("NCC_Forms", "Them", Get_CauHinh.Dt_Login.Rows[0]["ID"].ToString().ToUpper()) == true)
            {
                _NCC_Form = new NCC_Form();
                _NCC_Form.TextChanged += new EventHandler(_NCC_Form_TextChanged);
                _NCC_Form.dr = dr;
                _NCC_Form.ShowDialog();
            }
        }
        public void tailai()
        {
            _NCC_Class.get_store("sp_NhaCC_Toa_load");
            data();
        }
       
        #endregion

        #region menu click
        public void event_click()
        {          
            btn_new.Click += new EventHandler(btn_new_Click);
            btn_del.Click += new EventHandler(btn_del_Click);
            btn_copy.Click += new EventHandler(btn_copy_Click);
            btn_all.Click += new EventHandler(btn_all_Click);
            btn_none.Click += new EventHandler(btn_none_Click);
            // btn_close.Click += new EventHandler(btn_close_Click);
            // btn_load.Click += new EventHandler(btn_load_Click);
            tailai();
        }

        void btn_load_Click(object sender, EventArgs e)
        {
            tailai();
        }

        void btn_close_Click(object sender, EventArgs e)
        {
          //  this.Close();
        }

        void btn_none_Click(object sender, EventArgs e)
        {
            bochon();
        }

        void btn_all_Click(object sender, EventArgs e)
        {
            chon();
        }

        void btn_copy_Click(object sender, EventArgs e)
        {
            chep();
        }

        void btn_del_Click(object sender, EventArgs e)
        {
            xoa();
        }

        void btn_new_Click(object sender, EventArgs e)
        {
            them();
        }

        void btn_copy_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            chep();
        }

        void btn_none_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            bochon();
        }

        void btn_all_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            chon();
        }

        void btn_del_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            xoa();
        }

        void btn_excel_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            excel();
        }

      
        #endregion

        #region data
        NCC_Class _NCC_Class = new NCC_Class();
        NhomNCC_Class _NhomNCC_Class = new NhomNCC_Class();
        public void data()
        {

            //string sql_nhom_ncc = "Select * from Tbl_NhomNCC order by Name ASC";
            //_NhomNCC_Class.get(sql_nhom_ncc);

            Class_KetNoi db = new Class_KetNoi();
            //_NCC_Class.get("Select Tbl_NCC.*,Tbl_NhomNCC.Name as _ID1_Name from Tbl_NCC INNER JOIN Tbl_NhomNCC on Tbl_NCC._ID1 = Tbl_NhomNCC.ID order by Tbl_NhomNCC.Name asc, Tbl_NCC.Name ASC");
            string sql_ThuongLai = @"Select Tbl_NCC.*,Tbl_NhomNCC.Name as _ID1_Name,cast(0 as bit) as 'Select'
                 from Tbl_NCC
                        INNER JOIN Tbl_NhomNCC on Tbl_NCC._ID1 = Tbl_NhomNCC.ID 
                        order by Tbl_NhomNCC.Name asc, Tbl_NCC.Name ASC";
            //gc.DataSource = DuLieu.List_NCC.Tables[0];
            gc.DataSource = db.T_opendataset(sql_ThuongLai).Tables[0];
            repositoryItemLookUpEdit_ID1.DataSource = DuLieu.List_Nhom_NCC.Tables[0];
            gv.ExpandAllGroups();
            gv.RefreshData();
           
        }
        #endregion

        private void NCC_Forms_Load(object sender, EventArgs e)
        {
            dtpTu.DateTime = DateTime.Now;
            dtpDen.DateTime = DateTime.Now;
        }

        private void simpleButton1_Click(object sender, EventArgs e)
        {
            FolderBrowserDialog dialog = new FolderBrowserDialog();
            if (dialog.ShowDialog() == DialogResult.OK)
            {
                string path = dialog.SelectedPath;
                gv.ExportToXls(path + "\\NhaCC.xls");
            }
        }

        private void simpleButton2_Click(object sender, EventArgs e)
        {
            clsConnect cls = new clsConnect();
            SpreadsheetControl sheet = new SpreadsheetControl();
            OpenFileDialog dialog = new OpenFileDialog();
            dialog.Filter = "Excel Files(.xls)|*.xls|Excel Files(.xlsx)| *.xlsx";
            if (dialog.ShowDialog() == DialogResult.OK)
            {
                sheet.LoadDocument(dialog.FileName);
                #region code insert
                int count = sheet.ActiveWorksheet.Rows.LastUsedIndex;
                for (int i = 1; i <= count; i++)
                {
                    string _nhom = sheet.ActiveWorksheet.Rows[i]["A"].Value.ToString();
                    string _name = sheet.ActiveWorksheet.Rows[i]["B"].Value.ToString();
                    string _Text1 = sheet.ActiveWorksheet.Rows[i]["C"].Value.ToString();
                    string _Text2 = sheet.ActiveWorksheet.Rows[i]["D"].Value.ToString();
                    string _Text3 = sheet.ActiveWorksheet.Rows[i]["E"].Value.ToString();
                    string _Text4 = sheet.ActiveWorksheet.Rows[i]["F"].Value.ToString();
                    string _email = sheet.ActiveWorksheet.Rows[i]["J"].Value.ToString();
                    string _zalo = sheet.ActiveWorksheet.Rows[i]["K"].Value.ToString();
                    if (_name != "")
                    {
                        string _IDNhom = "";
                        DataTable dt = cls.LoadTable("select * from Tbl_NhomNCC where Name=N'" + _nhom + "'");
                        if (dt.Rows.Count > 0)
                            _IDNhom = dt.Rows[0]["ID"].ToString();
                        //
                        string sql1 = "select * from Tbl_NCC where Name=@Name";
                        int thamso1 = 1;
                        string[] name1 = new string[thamso1];
                        object[] value1 = new object[thamso1];
                        name1[0] = "@Name";
                        value1[0] = _name;
                        DataTable dt1 = cls.LoadTable(sql1, name1, value1, thamso1).Tables[0];
                        if (dt1.Rows.Count == 0)
                        {
                            //
                            string sql = "insert into Tbl_NCC(_ID1,ID,Name,_Text1,_Text2,_Text3,_Text4,Mail,Zalo,IS_User,Date_Num_Final_UpDate,User_ID_Final_UpDate)" +
                            "values(@_ID1,@ID,@Name,@_Text1,@_Text2,@_Text3,@_Text4,@Mail,@Zalo,@IS_User,@Date_Num_Final_UpDate,@User_ID_Final_UpDate)";
                            int thamso = 12;
                            string[] name = new string[thamso];
                            object[] value = new object[thamso];
                            name[0] = "@ID";
                            name[1] = "@Name";
                            name[2] = "@_Text1";
                            name[3] = "@_Text2";
                            name[4] = "@_Text3";
                            name[5] = "@_Text4";
                            name[6] = "@Mail";
                            name[7] = "@Zalo";
                            name[8] = "@IS_User";
                            name[9] = "@Date_Num_Final_UpDate";
                            name[10] = "@User_ID_Final_UpDate";
                            name[11] = "@_ID1";
                            value[0] = Guid.NewGuid().ToString().ToUpper();
                            value[1] = _name;
                            value[2] = _Text1;
                            value[3] = _Text2;
                            value[4] = _Text3;
                            value[5] = _Text4;
                            value[6] = _email;
                            value[7] = _zalo;
                            value[8] = 1;
                            value[9] = Get_CauHinh.Ngay_DN;
                            value[10] = Get_CauHinh.Dt_Login.Rows[0]["ID"].ToString().ToUpper();
                            value[11] = _IDNhom;
                            cls.UpdateTable(sql, name, value, thamso);
                        }
                    }
                }
                MessageBox.Show("Đã đưa vào được " + count.ToString() + " dòng!");
                tailai();
                data();
                #endregion
            }
        }

        private void simpleButton3_Click(object sender, EventArgs e)
        {
            tailai();
        }

        private void simpleButton5_Click(object sender, EventArgs e)
        {
            int ng1 = (int)(dtpTu.DateTime - new DateTime(1900, 1, 1)).TotalDays;
            int ng2 = (int)(dtpDen.DateTime - new DateTime(1900, 1, 1)).TotalDays;
            Class_KetNoi db = new Class_KetNoi();
            string sql = "Select TenNhomToa, Tbl_NCC.*,Tbl_NhomNCC.Name as _ID1_Name, "+
                "(select top 1 ID from Tbl_GIAODICH where ID_NCC=Tbl_NCC.ID and Date_Num_Final_UpDate between "+ ng1.ToString()+" and "+ng2.ToString()+")as GG " +
                " from Tbl_NCC "+
                    " left join Tbl_NhomToa on Tbl_NhomToa.IDNhomToa = Tbl_NCC.IDNhomToa "+
                    " LEFT JOIN Tbl_NhomNCC on Tbl_NCC._ID1 = Tbl_NhomNCC.ID order by Tbl_NhomNCC.Name asc, Tbl_NCC.Name ASC  ";
            DuLieu.List_NCC = db.T_opendataset(sql);
            DuLieu.List_NCC.Tables[0].Columns.Add("Select", typeof(bool));
            DataView view1 = DuLieu.List_NCC.Tables[0].Copy().DefaultView;
            view1.RowFilter = "GG is null";
            DuLieu.List_NCC.Tables[0].Rows.Clear();
            DuLieu.List_NCC.Tables[0].Merge(view1.ToTable());


            Main_Form._dtNhaCC = DuLieu.List_NCC.Tables[0];
            gc.DataSource = DuLieu.List_NCC.Tables[0];
            repositoryItemLookUpEdit_ID1.DataSource = DuLieu.List_Nhom_NCC.Tables[0];
            gv.ExpandAllGroups();
            gv.RefreshData();
        }
    }
}
