C# Select.And方法代码示例

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public static List<string> LAYDSACH_IDKHOTHUOC_CHAN()
        {
            DataTable m_dtKhoThuoc = new DataTable();
              SqlQuery sqlQuery = new Select().From(TDmucKho.Schema);
              if (!globalVariables.IsAdmin)
              {
              sqlQuery.Where(TDmucKho.Columns.IdKho).In(new Select(QheNhanvienKho.Columns.IdKho)
                                                        .From(QheNhanvienKho.Schema).Where(QheNhanvienKho.Columns.IdNhanvien)
                                                        .IsEqualTo(globalVariables.gv_intIDNhanvien));

              }
              if (sqlQuery.HasWhere)
              sqlQuery.And(TDmucKho.Columns.KieuKho).In(lstKhochan);
              else
              {
              sqlQuery.Where(TDmucKho.Columns.KieuKho).In(lstKhochan);
              }
              sqlQuery.And(TDmucKho.Columns.KhoThuocVt).In(lstKhoThuoc);
              sqlQuery.And(TDmucKho.Columns.LaTuthuoc).IsEqualTo(0);
              sqlQuery.And(TDmucKho.Columns.LoaiKho).IsEqualTo(0);
              sqlQuery.OrderAsc(TDmucKho.Columns.SttHthi);
              m_dtKhoThuoc = sqlQuery.ExecuteDataSet().Tables[0];
              if (m_dtKhoThuoc.Rows.Count <= 0) return new List<string>();
              return m_dtKhoThuoc.AsEnumerable().Select(c => c.Field<string>(TDmucKho.Columns.IdKho)).ToList<string>();
        }

        private bool InValiData()
        {
            if (string.IsNullOrEmpty(txtMa.Text))
            {
                Utility.SetMsg(lblMessage,"Bạn phải nhập thông tin mã phòng ",true);
                txtMa.Focus();
                return false;
            }
            if (chkcodefrom.Checked)
            {
                if (Utility.DoTrim(txtCodefrom.Text) == "")
                {
                    Utility.SetMsg(lblMessage, "Bạn phải nhập mã bắt đầu", true);
                    txtCodefrom.Focus();
                    txtCodefrom.SelectAll();
                    return false;
                }
                if (Utility.DecimaltoDbnull(txtCodefrom.Text, 0) <= 0)
                {
                    Utility.SetMsg(lblMessage, "Hậu tố mã phòng bắt đầu phải >0", true);
                    txtCodefrom.Focus();
                    txtCodefrom.SelectAll();
                    return false;
                }
                if (Utility.DoTrim(txtCode2.Text) == "")
                {
                    Utility.SetMsg(lblMessage, "Bạn phải nhập mã kết thúc", true);
                    txtCode2.Focus();
                    txtCode2.SelectAll();
                    return false;
                }
                if (Utility.DecimaltoDbnull(txtCodefrom.Text, 0) > Utility.DecimaltoDbnull(txtCode2.Text, 0))
                {
                    Utility.SetMsg(lblMessage, "Hậu tố mã phòng bắt đầu < mã phòng đến", true);
                    txtCode2.Focus();
                    txtCode2.SelectAll();
                    return false;
                }
            }
             if (string.IsNullOrEmpty(txtTEN.Text))
            {
                Utility.SetMsg(lblMessage,"Bạn phải nhập thông tin tên phòng ", true);
                txtTEN.Focus();
                return false;
            }
            if(cboKhoaNoiTru.SelectedIndex<0)
            {
                Utility.SetMsg(lblMessage,"Bạn phải chọn khoa nội trú ", true);
                cboKhoaNoiTru.Focus();
                return false;
            }
            DmucKhoaphong objDmucKhoaphong = DmucKhoaphong.FetchByID(Utility.Int32Dbnull(cboKhoaNoiTru.SelectedValue));
            if(objDmucKhoaphong==null)
            {
                Utility.SetMsg(lblMessage,"Bạn chọn khoa nội trú ", true);
                cboKhoaNoiTru.Focus();
                return false;
            }
            if (m_enAct == action.Insert)
            {
                if (!chkcodefrom.Checked)//Thêm đơn lẻ từng chiếc mới kiểm tra
                {
                    SqlQuery sqlQuery = new Select().From(NoitruDmucBuong.Schema)
                   .Where(NoitruDmucBuong.Columns.IdKhoanoitru).IsEqualTo(Utility.Int32Dbnull(cboKhoaNoiTru.SelectedValue, ""));
                    sqlQuery.And(NoitruDmucBuong.Columns.MaBuong).IsEqualTo(Utility.sDbnull(txtMa.Text));
                    if (sqlQuery.GetRecordCount() > 0)
                    {
                        Utility.ShowMsg("Mã buồng đã tồn tại. Đề nghị bạn nhập mã khác", "Thông báo", MessageBoxIcon.Warning);
                        txtMa.Focus();
                        return false;
                    }
                    sqlQuery = new Select().From(NoitruDmucBuong.Schema)
                   .Where(NoitruDmucBuong.Columns.IdKhoanoitru).IsEqualTo(Utility.Int32Dbnull(cboKhoaNoiTru.SelectedValue, ""));
                    sqlQuery.And(NoitruDmucBuong.Columns.TenBuong).IsEqualTo(Utility.sDbnull(txtTEN.Text));
                    if (sqlQuery.GetRecordCount() > 0)
                    {
                        Utility.ShowMsg("Tên buồng đã tồn tại. Đề nghị bạn nhập mã khác", "Thông báo", MessageBoxIcon.Warning);
                        txtTEN.Focus();
                        return false;
                    }
                }

            }
            else
            {
                SqlQuery sqlQuery = new Select().From(NoitruDmucBuong.Schema)
              .Where(NoitruDmucBuong.Columns.IdKhoanoitru).IsEqualTo(Utility.sDbnull(cboKhoaNoiTru.SelectedValue, ""));
                sqlQuery.And(NoitruDmucBuong.Columns.MaBuong).IsEqualTo(Utility.sDbnull(txtMa.Text))
                    .And(NoitruDmucBuong.Columns.IdBuong).IsNotEqualTo(Utility.Int32Dbnull(txtID.Text));
                if (sqlQuery.GetRecordCount() > 0)
                {
                    Utility.ShowMsg("Mã buồng đã tồn tại. Đề nghị bạn nhập mã khác", "Thông báo", MessageBoxIcon.Warning);
                    txtMa.Focus();
                    return false;
                }
                sqlQuery = new Select().From(NoitruDmucBuong.Schema)
               .Where(NoitruDmucBuong.Columns.IdKhoanoitru).IsEqualTo(Utility.sDbnull(cboKhoaNoiTru.SelectedValue, ""));
                sqlQuery.And(NoitruDmucBuong.Columns.TenBuong).IsEqualTo(Utility.sDbnull(txtTEN.Text))
                    .And(NoitruDmucBuong.Columns.IdBuong).IsNotEqualTo(Utility.Int32Dbnull(txtID.Text));
                if (sqlQuery.GetRecordCount() > 0)
//.........这里部分代码省略.........

        private void TudongthemDiachinh_test(string value)
        {
            //Tạm thời khoa lại tìm giải pháp khác trực quan hơn
            return;
            bool success = false;
            bool added = false;
            string[] arrvalues = value.Split(',');
            if (arrvalues.Length != 3) return;
            string TenTP = arrvalues[2];
            string TenQH = arrvalues[1];
            string TenXP = arrvalues[0];
            string CodeTP = "";
            string CodeQH = "";
            string CodeXP = "";

            string SurveyCodeTP = Utility.GetYYMMDDHHMMSS(globalVariables.SysDate);
            string SurveyCodeQH = SurveyCodeTP + "1";
            string SurveyCodeXP = SurveyCodeQH + "1";
            DmucDiachinh _TP = null;
            var _newTP = new DmucDiachinh();

            DmucDiachinh _QH = null;
            var _newQH = new DmucDiachinh();

            DmucDiachinh _XP = null;
            var _newXP = new DmucDiachinh();

            string ShortCutXP = "kx";
            string ShortCutTP = "kx";
            string ShortCutQH = "kx";
            try
            {
                _TP =
                    new Select().From(DmucDiachinh.Schema).Where(DmucDiachinh.TenDiachinhColumn).IsEqualTo(TenTP).ExecuteSingle
                        <DmucDiachinh>();

                if (_TP == null)
                {
                    _newTP.MaDiachinh = SurveyCodeTP;
                    _newTP.TenDiachinh = TenTP;
                    _newTP.SttHthi = 1;
                    _newTP.LoaiDiachinh = 0;
                    _newTP.MotaThem = getshortcut(Utility.Bodau(BoTp(0, TenTP)));
                    ShortCutTP = _newTP.MotaThem;
                }
                else
                {
                    CodeTP = _TP.MaDiachinh;
                    ShortCutTP = _TP.MotaThem;
                }
                SqlQuery sqlQueryQH = new Select().From(DmucDiachinh.Schema).Where(DmucDiachinh.TenDiachinhColumn).IsEqualTo(TenQH);
                if (_TP != null)
                {
                    sqlQueryQH.And(DmucDiachinh.MaChaColumn).IsEqualTo(_TP.MaDiachinh);
                    _QH = sqlQueryQH.ExecuteSingle<DmucDiachinh>();
                }
                else
                    _QH = null;

                if (_QH == null)
                {
                    _newQH.MaDiachinh = SurveyCodeQH;
                    _newQH.TenDiachinh = TenQH;
                    _newQH.SttHthi = 1;
                    _newQH.LoaiDiachinh = 1;
                    _newQH.MotaThem = getshortcut(Utility.Bodau(BoTp(1, TenQH)));
                    ShortCutQH = _newQH.MotaThem;
                }
                else
                {
                    CodeQH = _QH.MaDiachinh;
                    ShortCutQH = _QH.MotaThem;
                }
                SqlQuery sqlQueryXP = new Select().From(DmucDiachinh.Schema).Where(DmucDiachinh.TenDiachinhColumn).IsEqualTo(TenXP);
                if (_QH != null)
                {
                    sqlQueryXP.And(DmucDiachinh.MaChaColumn).IsEqualTo(_QH.MaDiachinh);
                    _XP = sqlQueryXP.ExecuteSingle<DmucDiachinh>();
                }
                else
                    _XP = null;

                if (_XP == null)
                {
                    _newXP.MaDiachinh = SurveyCodeXP;
                    _newXP.TenDiachinh = TenXP;
                    _newXP.SttHthi = 1;
                    _newXP.LoaiDiachinh = 2;
                    _newXP.MotaThem = getshortcut(Utility.Bodau(BoTp(2, TenXP)));
                    ShortCutXP = _newXP.MotaThem;
                }
                else
                {
                    CodeXP = _XP.MaDiachinh;
                    ShortCutXP = _XP.MotaThem;
                }

                using (var scope = new TransactionScope())
                {
                    using (var sh = new SharedDbConnectionScope())
//.........这里部分代码省略.........

        /// <summary>
        /// HÀM THỰC HIỆN LẤY THÔNG TIN KHO LẺ
        /// </summary>
        /// <returns></returns>
        public static DataTable LAYTHONGTIN_KHOTHUOC_VATTU_NOITRU_THEOKHOA(int ID_KHOA)
        {
            DataTable m_dtKhoThuoc = new DataTable();
              SqlQuery sqlQuery = new Select().From(TDmucKho.Schema);
              if (!globalVariables.IsAdmin)
              {
              sqlQuery.Where(TDmucKho.Columns.IdKho).In(new Select(QheNhanvienKho.Columns.IdKho)
                                                        .From(QheNhanvienKho.Schema).Where(QheNhanvienKho.Columns.IdNhanvien)
                                                        .IsEqualTo(globalVariables.gv_intIDNhanvien));

              }
              if (sqlQuery.HasWhere)
              sqlQuery.And(TDmucKho.Columns.KieuKho).In(lstKhole);//Kho lẻ
              else
              {
              sqlQuery.Where(TDmucKho.Columns.KieuKho).In(lstKhole);
              }
              sqlQuery.And(TDmucKho.Columns.LaTuthuoc).IsEqualTo(0)//Không lấy tủ thuốc
              .And(TDmucKho.Columns.LoaiBnhan).In(lstKhoNoitru);//Chỉ lấy các kho nội trú
              sqlQuery.And(TDmucKho.Columns.KhoThuocVt).In(lstKhoThuocVT);

              sqlQuery.And(TDmucKho.Columns.LoaiKho).IsEqualTo(0);
              sqlQuery.OrderAsc(TDmucKho.Columns.SttHthi);
              m_dtKhoThuoc = sqlQuery.ExecuteDataSet().Tables[0];
              DataTable dt_khovattu=LAYTHONGTIN_VATTU_KHOA(ID_KHOA);
              if(dt_khovattu!=null)
              {
              foreach(DataRow dr in dt_khovattu.Rows)
              m_dtKhoThuoc.ImportRow(dr);
              }
              return m_dtKhoThuoc;
        }

 /// <summary>
 /// hàm thực hiện việc lấy thông tin của dược
 /// </summary>
 /// <returns></returns>
 /// 
 public static DataTable LAYTHONGTIN_KHOTHUOC_TATCA()
 {
     DataTable m_dtKhoThuoc=new DataTable();
       SqlQuery sqlQuery = new Select().From(TDmucKho.Schema);
       if(!globalVariables.IsAdmin)
       {
       sqlQuery.Where(TDmucKho.Columns.IdKho).In(
           new Select(QheNhanvienKho.Columns.IdKho).From(QheNhanvienKho.Schema).Where(QheNhanvienKho.Columns.IdNhanvien)
               .IsEqualTo(globalVariables.gv_intIDNhanvien));
       }
       sqlQuery.And(TDmucKho.Columns.KhoThuocVt).In(lstKhoThuoc);
        sqlQuery.OrderAsc(TDmucKho.Columns.SttHthi);
        m_dtKhoThuoc=sqlQuery.ExecuteDataSet().Tables[0];
       return m_dtKhoThuoc;
 }

        /// <summary>
        /// HÀM THỰC HIỆN LẤY THÔNG TIN KHO LẺ
        /// </summary>
        /// <returns></returns>
        public static DataTable LAYTHONGTIN_KHOTHUOC_LE_NGOAI_TRU_KEDON(string MA_DTUONG)
        {
            DataTable m_dtKhoThuoc = new DataTable();
              SqlQuery sqlQuery = new Select().From(TDmucKho.Schema);
              if (!globalVariables.IsAdmin)
              {
              sqlQuery.Where(TDmucKho.Columns.IdKho).In(new Select(QheNhanvienKho.Columns.IdKho)
                                                        .From(QheNhanvienKho.Schema).Where(QheNhanvienKho.Columns.IdNhanvien)
                                                        .IsEqualTo(globalVariables.gv_intIDNhanvien));

              }
              if (sqlQuery.HasWhere)
              sqlQuery.And(TDmucKho.Columns.KieuKho).In(lstKhole);
              else
              {
              sqlQuery.Where(TDmucKho.Columns.KieuKho).In(lstKhole);
              }
              if (MA_DTUONG.Trim().ToUpper() != "ALL")
              {
              sqlQuery.And(TDmucKho.Columns.IdKho).In(new Select(QheDoituongKho.IdKhoColumn).From(QheDoituongKho.Schema).Where(QheDoituongKho.MaDoituongKcbColumn).IsEqualTo(MA_DTUONG));
              }
              sqlQuery.And(TDmucKho.Columns.KhoThuocVt).In(lstKhoThuoc);
              sqlQuery.And(TDmucKho.Columns.LaTuthuoc).IsEqualTo(0)
              .And(TDmucKho.Columns.LoaiBnhan).In(lstKhoNgoaitru);
              sqlQuery.And(TDmucKho.Columns.LoaiKho).IsEqualTo(0);
              sqlQuery.OrderAsc(TDmucKho.Columns.SttHthi);
              m_dtKhoThuoc = sqlQuery.ExecuteDataSet().Tables[0];
              return m_dtKhoThuoc;
        }

        public static DataTable LaythongtinQuanheDoituongKhoBanTaiQuay(string MaDoiTuong)
        {
            SqlQuery sqlQuery = new Select().From(TDmucKho.Schema);
              sqlQuery.Where(TDmucKho.Columns.IdKho).In(
              new Select(QheDoituongKho.Columns.IdKho).From(QheDoituongKho.Schema).Where(QheDoituongKho.Columns.MaDoituongKcb)
                  .IsEqualTo(MaDoiTuong)).And(TDmucKho.Columns.LaQuaythuoc).IsEqualTo(1);
              if(!globalVariables.IsAdmin)
              {
              sqlQuery.And(TDmucKho.Columns.IdKho).In(
                  new Select(QheNhanvienKho.Columns.IdKho).From(QheNhanvienKho.Schema).Where(QheNhanvienKho.Columns.IdNhanvien).IsEqualTo(
                      globalVariables.gv_intIDNhanvien));

              }
              sqlQuery.And(TDmucKho.Columns.LoaiKho).IsEqualTo(0);
              sqlQuery.And(TDmucKho.Columns.LaTuthuoc).IsEqualTo(0);
              return sqlQuery.ExecuteDataSet().Tables[0];
        }

        /// <summary>
        /// Kiểm tra tính hợp lệ của dữ liệu trước khi đóng gói dữ liệu vào Entity
        /// </summary>
        /// <returns></returns>
        private bool IsValidData()
        {
            errorProvider1.Clear();
            if (String.IsNullOrEmpty(txtID.Text))
            {
                errorProvider1.SetError(txtID, "Bạn cần nhập mã loại bệnh viện.");
                txtID.Focus();
                return false;
            }
            SqlQuery q = new Select().From(DmucBenhvien.Schema).Where(DmucBenhvien.Columns.MaBenhvien).IsEqualTo(Utility.DoTrim(txtObjectCode.Text));
            if (m_enAction == action.Update)
                q.And(DmucBenhvien.Columns.IdBenhvien).IsNotEqualTo(Utility.Int32Dbnull(txtID.Text, -1));

            if (q.GetRecordCount() > 0)
            {
                errorProvider1.SetError(txtObjectCode, "Đã tồn tại bệnh viện có mã " + Utility.DoTrim(txtObjectCode.Text) + ". Mời bạn nhập mã khác");
                txtObjectCode.Focus();
                return false;
            }
            if (String.IsNullOrEmpty(txtName.Text))
            {
                errorProvider1.SetError(txtName, "Bạn cần nhập tên loại bệnh viện.");
                txtName.Focus();
                return false;
            }
            return true;
        }

 public static DataTable LaydanhmucPhong(int PhongChucnang)
 {
     try
     {
         SqlQuery sqlQuery =new Select().From(VDmucKhoaphong.Schema).Where(VDmucKhoaphong.Columns.KieuKhoaphong).IsEqualTo("PHONG");
                 if (PhongChucnang > -1)
             sqlQuery.And(VDmucKhoaphong.Columns.PhongChucnang).IsEqualTo(PhongChucnang);
                 return sqlQuery.ExecuteDataSet().Tables[0];
     }
     catch
     {
         return null;
     }
 }

        public static DataTable Laydanhmuckhoa( int idkhoa)
        {
            try
            {
                SqlQuery sqlQuery = new Select().From(VDmucKhoaphong.Schema).Where(VDmucKhoaphong.Columns.KieuKhoaphong).IsEqualTo("KHOA");
                sqlQuery.And(VDmucKhoaphong.Columns.IdKhoaphong).IsEqualTo(idkhoa);

                return sqlQuery.ExecuteDataSet().Tables[0];
            }
            catch
            {
                return null;
            }
        }

 void cboService_SelectedIndexChanged(object sender, EventArgs e)
 {
     try
     {
         if (!m_blnLoaded) return;
        SqlQuery _SqlQuery = new Select().From(VDmucDichvuclsChitiet.Schema);
        if (Utility.Int32Dbnull(cboService.SelectedValue, -1) > -1)
            _SqlQuery.Where(VDmucDichvuclsChitiet.Columns.IdDichvu).IsEqualTo(Utility.Int32Dbnull(cboService.SelectedValue, -1));
        if (_SqlQuery.HasWhere)
        {
            if (!optTatca.Checked)
                _SqlQuery.And(VDmucDichvuclsChitiet.Columns.TrangThai).IsEqualTo(optHieuluc.Checked ? 1 : 0);
            if (!optQhe_tatca.Checked)
                _SqlQuery.And(VDmucDichvuclsChitiet.Columns.CoQhe).IsEqualTo(optCoQhe.Checked ? 1 : 0);
        }
        else
        {
            if (!optTatca.Checked)
                _SqlQuery.Where(VDmucDichvuclsChitiet.Columns.TrangThai).IsEqualTo(optHieuluc.Checked ? 1 : 0);
            if (!optQhe_tatca.Checked)
                _SqlQuery.And(VDmucDichvuclsChitiet.Columns.CoQhe).IsEqualTo(optCoQhe.Checked ? 1 : 0);
        }
        m_dtDataDetailService = _SqlQuery.OrderAsc(VDmucDichvuclsChitiet.Columns.SttHthiLoaidvu, VDmucDichvuclsChitiet.Columns.SttHthiDichvu,VDmucDichvuclsChitiet.Columns.SttHthi).ExecuteDataSet().Tables[0];
        Utility.SetDataSourceForDataGridEx(grdList, m_dtDataDetailService, true, true, "1=1", "stt_hthi_dichvu,stt_hthi_chitiet," + DmucDichvuclsChitiet.Columns.TenChitietdichvu);
     }
     catch
     {
     }
 }

        private void Timkiemdulieu()
        {
            try
            {

                SqlQuery _sqlquery = new Select().From(VDmucNhanvien.Schema);
                if (Utility.Int32Dbnull(cboDepartment.SelectedValue, -1) != -1)
                    if (_sqlquery.HasWhere)
                        _sqlquery.Where(VDmucNhanvien.Columns.IdPhong).IsEqualTo(Utility.Int32Dbnull(cboDepartment.SelectedValue, -1));
                    else
                        _sqlquery.And(VDmucNhanvien.Columns.IdPhong).IsEqualTo(Utility.Int32Dbnull(cboDepartment.SelectedValue, -1));

                if (Utility.Int32Dbnull(cboParent.SelectedValue, -1) != -1)
                    if (_sqlquery.HasWhere)
                        _sqlquery.Where(VDmucNhanvien.Columns.IdKhoa).IsEqualTo(Utility.Int32Dbnull(cboParent.SelectedValue, -1));
                    else
                        _sqlquery.And(VDmucNhanvien.Columns.IdKhoa).IsEqualTo(Utility.Int32Dbnull(cboParent.SelectedValue, -1));

                if (Utility.sDbnull(cboStaffType.SelectedValue, "-1") != "-1")
                    if (_sqlquery.HasWhere)
                        _sqlquery.Where(VDmucNhanvien.Columns.MaLoainhanvien).IsEqualTo(Utility.sDbnull(cboStaffType.SelectedValue, "-1"));
                    else
                        _sqlquery.And(VDmucNhanvien.Columns.MaLoainhanvien).IsEqualTo(Utility.sDbnull(cboStaffType.SelectedValue, "-1"));

                if (chknoUID.Checked)
                    if (_sqlquery.HasWhere)
                        _sqlquery.Where(VDmucNhanvien.Columns.UserName).IsEqualTo("");
                    else
                        _sqlquery.And(VDmucNhanvien.Columns.UserName).IsEqualTo("");
                else
                    if (_sqlquery.HasWhere)
                        _sqlquery.Where(VDmucNhanvien.Columns.UserName).IsNotEqualTo("");
                    else
                        _sqlquery.And(VDmucNhanvien.Columns.UserName).IsNotEqualTo("");
                m_dtStaffList = _sqlquery.ExecuteDataSet().Tables[0];
                Utility.SetDataSourceForDataGridEx(grdStaffList, m_dtStaffList, true, true, "", "");
                ModifyCommand();
            }
            catch
            {
            }
        }

        /// <summary>
        /// hàm tìm kiếm thông tin của Form
        /// </summary>
        private void Search()
        {
            try
            {
                SqlQuery query = new Select().From(VDmucDichvukcb.Schema);

                    if (cboLoaiKham.SelectedIndex > 0)
                        if (!query.HasWhere)
                        query.Where(VDmucDichvukcb.Columns.IdKieukham).IsEqualTo(Utility.Int32Dbnull(cboLoaiKham.SelectedValue, 0));
                        else
                            query.And(VDmucDichvukcb.Columns.IdKieukham).IsEqualTo(Utility.Int32Dbnull(cboLoaiKham.SelectedValue, 0));

                    if (cboDoituong.SelectedIndex > 0)
                        if (!query.HasWhere)
                            query.Where(VDmucDichvukcb.Columns.IdDoituongKcb).IsEqualTo(Utility.Int32Dbnull(cboDoituong.SelectedValue, 0));
                        else
                            query.And(VDmucDichvukcb.Columns.IdDoituongKcb).IsEqualTo(Utility.Int32Dbnull(cboDoituong.SelectedValue, 0));

                    if (cboPhongBan.SelectedIndex > 0)
                        if (!query.HasWhere)
                            query.Where(VDmucDichvukcb.Columns.IdKhoaphong).IsEqualTo(Utility.Int32Dbnull(cboPhongBan.SelectedValue, 0));
                        else
                            query.And(VDmucDichvukcb.Columns.IdKhoaphong).IsEqualTo(Utility.Int32Dbnull(cboPhongBan.SelectedValue, 0));

                    if (cboRoomDept.SelectedIndex > 0)
                        if (!query.HasWhere)
                            query.Where(VDmucDichvukcb.Columns.IdPhongkham).IsEqualTo(Utility.Int32Dbnull(cboRoomDept.SelectedValue, 0));
                        else
                            query.And(VDmucDichvukcb.Columns.IdPhongkham).IsEqualTo(Utility.Int32Dbnull(cboRoomDept.SelectedValue, 0));

                    if (cboBacsi.SelectedIndex > 0)
                        if (!query.HasWhere)
                            query.Where(VDmucDichvukcb.Columns.IdBacsy).IsEqualTo(Utility.Int32Dbnull(cboBacsi.SelectedValue, 0));
                        else
                            query.And(VDmucDichvukcb.Columns.IdBacsy).IsEqualTo(Utility.Int32Dbnull(cboBacsi.SelectedValue, 0));

                m_dtDmucDichvu_kcb=query.ExecuteDataSet().Tables[0];
               // grd_Insurance_Objects.DataSource = m_dtDmucDichvu_kcb;
                Utility.SetDataSourceForDataGridEx(grd_Insurance_Objects, m_dtDmucDichvu_kcb,true,true,"1=1","");
                ModifyCommand();
            }
            catch(Exception ex)
            {
                ModifyCommand();
                Utility.ShowMsg(ex.Message);
            }
        }

        /// <summary>
        /// tìm kiếm thông tin của Form
        /// </summary>
        private void MethodSearch()
        {
            SqlQuery q = new Select().From(DmucNoiKCBBD.Schema);
            string filter = "";
            if (!string.IsNullOrEmpty(txtClinicCode.Text))
            {
                if (Utility.DoTrim(filter) == "")
                    filter = DmucNoiKCBBD.Columns.MaKcbbd + "='" + Utility.DoTrim(txtClinicCode.Text) + "'";
                //q.And(DmucNoiKCBBD.Columns.MaKcbbd).IsEqualTo(txtClinicCode.Text);
            }
            if (!string.IsNullOrEmpty(txtClinicName.Text) && Utility.DoTrim( filter)!="")
            {
                q.And(DmucNoiKCBBD.Columns.TenKcbbd).Like("%" + txtClinicName.Text + "%");
                if (Utility.DoTrim(filter) == "")
                filter = DmucNoiKCBBD.Columns.TenKcbbd + " like %" + txtClinicName.Text + "%";
                else
                    filter =" AND "+ DmucNoiKCBBD.Columns.TenKcbbd + " like %" + txtClinicName.Text + "%";
            }
            if (cboDieaseType.SelectedIndex > 0)
            {

                q.And(DmucNoiKCBBD.Columns.MaDiachinh).IsEqualTo(Utility.sDbnull(cboDieaseType.SelectedValue, ""));
                if (Utility.DoTrim(filter) == "")
                    filter = DmucNoiKCBBD.Columns.MaDiachinh + " ='" + Utility.sDbnull(cboDieaseType.SelectedValue, "") + "'";
                else
                    filter = " AND " + DmucNoiKCBBD.Columns.MaDiachinh + " ='" + Utility.sDbnull(cboDieaseType.SelectedValue, "") + "'";
            }
            if (Utility.DoTrim(filter) == "")
                filter = "1=1";
            globalVariables.gv_dtDmucNoiKCBBD.DefaultView.RowFilter = filter;
            Utility.SetDataSourceForDataGridEx(grdInsClinic, globalVariables.gv_dtDmucNoiKCBBD, true, true, filter, DmucNoiKCBBD.TenKcbbdColumn.ColumnName);
        }

        private bool isValidData()
        {
            Utility.SetMsg(lblMsg, "", true);
            if (string.IsNullOrEmpty(txtStaffCode.Text))
            {
                Utility.SetMsg(lblMsg,  "Bạn phải nhập mã nhân viên",true);
                txtStaffCode.Focus();
                return false;

            }
            if (string.IsNullOrEmpty(txtName.Text))
            {
                Utility.SetMsg(lblMsg, "Bạn phải nhập tên nhân viên",true);
                txtName.Focus();
                return false;

            }

            SqlQuery q = new Select().From(DmucNhanvien.Schema)
                .Where(DmucNhanvien.Columns.MaNhanvien).IsEqualTo(txtStaffCode.Text);
            if (em_Action == action.Update)
                q.And(DmucNhanvien.Columns.IdNhanvien).IsNotEqualTo(Utility.Int32Dbnull(txtID.Text, -1));
            if(q.GetRecordCount()>0)
            {
                Utility.SetMsg(lblMsg, "Tồn tại mã nhân viên",true);
                txtStaffCode.Focus();
                return false;
            }

            if (txtUID.MyID!="-1")
            {
                SqlQuery q2 = new Select().From(DmucNhanvien.Schema)
                    .Where(DmucNhanvien.Columns.UserName).IsEqualTo(txtUID.MyID);
                if (em_Action == action.Update)
                    q2.And(DmucNhanvien.Columns.IdNhanvien).IsNotEqualTo(Utility.Int32Dbnull(txtID.Text, -1));
                if (q2.GetRecordCount() > 0)
                {
                    Utility.SetMsg(lblMsg, "Tên đăng nhập đã gán cho một nhân viên khác",true);
                    txtUID.Focus();
                    return false;
                }
            }
            if (cboUserName.SelectedValue != "-1")
            {
                SqlQuery q2 = new Select().From(DmucNhanvien.Schema)
                    .Where(DmucNhanvien.Columns.UserName).IsEqualTo(cboUserName.SelectedValue);
                if (em_Action == action.Update)
                    q2.And(DmucNhanvien.Columns.IdNhanvien).IsNotEqualTo(Utility.Int32Dbnull(txtID.Text, -1));
                if (q2.GetRecordCount() > 0)
                {
                    Utility.SetMsg(lblMsg, "Tên đăng nhập đã gán cho một nhân viên khác", true);
                    cboUserName.Focus();
                    return false;
                }
            }
            return true;
        }