C# QueryBuilder.addIn方法代码示例

        public override QueryBuilder PLBuildQueryFilter()
        {
            QueryBuilder filter = new QueryBuilder(BIEN_MUC.FILTER_STRING);
            filter.addSoPhieu(BIEN_MUC.MA_BANG, MaBang.Text);
            filter.addLike("LOWER(" + BIEN_MUC.NOI_DUNG + ")", NoiDung.Text.ToLower());
            AppCtrl.addID(filter, BIEN_MUC.SO_DKCB_HD, SoDKHDCB);
            AppCtrl.addID(filter, BIEN_MUC.TIET_MUC, TietMuc);
            AppCtrl.addID(filter, BIEN_MUC.LOAI_LUU_TRU, LoaiBang);
            AppCtrl.addID(filter, BIEN_MUC.DON_VI_CUNG_CAP, DonViCungCap);
            AppCtrl.addID(filter, BIEN_MUC.DON_VI_SO_HUU, DonViSoHuu);
            filter.addIn(BIEN_MUC.POST_MASTER, AppCtrl.GetCheckedValues(TinhTrang, true));
            filter.addIn(BIEN_MUC.IS_THANH_LY, AppCtrl.GetCheckedValues(ThanhLy, true));
            AppCtrl.addFilterFromTo(filter, BIEN_MUC.NGAY_NHAP, ngayNhap);
            AppCtrl.addFilterFromTo(filter, BIEN_MUC.NAM_SAN_XUAT, NamSXTu.Value, NamSXDen.Value);
            long[] IDs = QuocGia._getSelectedIDs();
            if (IDs.Length > 0 && IDs.Length < QuocGia.Properties.GetItems().Count)
            {
                string cond = "(";
                foreach (long id in IDs)
                {
                    cond += BIEN_MUC.NUOC_SAN_XUAT + " like '%," + id + ",%' or ";
                }
                cond = cond.TrimEnd(' ', 'r', 'o');
                cond = cond += ")";
                filter.addCondition(cond);
            }
            filter.addCondition("1=1");
            filter.setDescOrderBy(BIEN_MUC.NGAY_CAP_NHAT);

            return filter;
        }

 public override QueryBuilder PLBuildQueryFilter()
 {
     FWWaitingMsg msg = new FWWaitingMsg();
     QueryBuilder filter = new QueryBuilder(UpdateRow());
     filter.addID("NV_ID", NhanVien._getSelectedID());
     filter.addDateFromTo("NGAY_LAM_VIEC", ngayLamViec.FromDate, ngayLamViec.ToDate);
     filter.addDuyet(PLConst.FIELD_DUYET, DuyetSelect.layTrangThai());
     filter.addIn("LOAI_XAC_NHAN", this.getLoaiXacNhan());
     msg.Finish();
     return filter;
 }

        /// <summary>Step 5: Xây dựng Query Buider cho việc tìm kiếm
        /// Xây dựng một QueryBuilder từ những chọn lựa trong phần filter.
        /// Từ QueryBuilder này ta có thể lấy được dữ liệu thỏa điều kiện.
        /// Nếu hỗ trợ duyệt thì trong câu truy vấn trả về 
        /// phải có cột là DUYET_BIT
        /// </summary>
        public override QueryBuilder PLBuildQueryFilter()
        {
            error.ClearErrors();
            gridViewMaster.ClearGrouping();
            if (TruongCap1.Text != string.Empty)
            {
                gridViewMaster.Columns[(int)TruongCap1._getSelectedID()].Group();
                gridViewMaster.Columns[(int)TruongCap1._getSelectedID()].Visible = false;
            }

            if (TruongCap2.Text != string.Empty)
            {
                if (TruongCap2.Text != TruongCap1.Text)
                {
                    gridViewMaster.Columns[(int)TruongCap2._getSelectedID()].Group();
                    gridViewMaster.Columns[(int)TruongCap2._getSelectedID()].Visible = false;
                }
                else TruongCap2.SetError(error, "Trường thống kê này đã được sử dụng!");
            }
            if (TruongCap3.Text != string.Empty)
            {
                if (TruongCap3.Text != TruongCap1.Text && TruongCap3.Text != TruongCap2.Text)
                {
                    gridViewMaster.Columns[(int)TruongCap3._getSelectedID()].Group();
                    gridViewMaster.Columns[(int)TruongCap3._getSelectedID()].Visible = false;
                }
                else TruongCap3.SetError(error, "Trường thống kê này đã được sử dụng!");
            }

            QueryBuilder filter = new QueryBuilder(UpdateRow());
            filter.addSoPhieu(BIEN_MUC.MA_BANG, MaBang.Text);
            filter.addLike("LOWER(" + BIEN_MUC.NOI_DUNG + ")", NoiDung.Text.ToLower());
            filter.addID(BIEN_MUC.SO_DKCB_HD, SoDKHDCB._getSelectedIDs());
            filter.addID(BIEN_MUC.TIET_MUC, TietMuc._getSelectedIDs());
            filter.addID(BIEN_MUC.LOAI_LUU_TRU, LoaiBang._getSelectedIDs());
            filter.addIn(BIEN_MUC.POST_MASTER, AppCtrl.GetCheckedValues(TinhTrang, true));
            filter.addID(BIEN_MUC.DON_VI_CUNG_CAP, DonViCungCap._getSelectedIDs());
            filter.addID(BIEN_MUC.DON_VI_SO_HUU, DonViSoHuu._getSelectedIDs());
            filter.addDateFromTo(BIEN_MUC.NGAY_NHAP, ngayNhap.FromDate, ngayNhap.ToDate);
            long[] IDs = QuocGia._getSelectedIDs();
            if (IDs.Length > 0 && IDs.Length < QuocGia.Properties.GetItems().Count)
            {
                string cond = "(";
                foreach (long id in IDs)
                {
                    cond += BIEN_MUC.NUOC_SAN_XUAT + " like '%," + id + "' or " + BIEN_MUC.NUOC_SAN_XUAT + " like '" + id + ",%' or " + BIEN_MUC.NUOC_SAN_XUAT + " like '%," + id + ",%' or ";
                }
                cond = cond.TrimEnd(' ', 'r', 'o');
                cond = cond += ")";
                filter.addCondition(cond);
            }
            filter.addCondition("1=1");
            filter.setDescOrderBy(BIEN_MUC.NGAY_CAP_NHAT);
            return filter;
        }

        /// <summary>Step 5: Xây dựng Query Buider cho việc tìm kiếm
        /// Xây dựng một QueryBuilder từ những chọn lựa trong phần filter.
        /// Từ QueryBuilder này ta có thể lấy được dữ liệu thỏa điều kiện.
        /// Nếu hỗ trợ duyệt thì trong câu truy vấn trả về 
        /// phải có cột là DUYET_BIT
        /// </summary>
        public override QueryBuilder PLBuildQueryFilter()
        {
            error.ClearErrors();
            gridViewMaster.ClearGrouping();
            GridColumn col1 = null;
            GridColumn col2 = null;
            GridColumn col3 = null;
            foreach (GridSummaryItem item in timkiem.CotNoiDung.SummaryItem.Collection)
            {
                item.SummaryType = DevExpress.Data.SummaryItemType.None;
            }
            foreach (CheckedListBoxItem item in ckcCotHienThi.Items)
            {
                if (!(item.Value is BandedGridColumn)) continue;
                ((BandedGridColumn)item.Value).Visible = item.CheckState == System.Windows.Forms.CheckState.Checked;
            }
            if (TruongCap1.Text != string.Empty ||
                TruongCap2.Text != string.Empty ||
                TruongCap3.Text != string.Empty)
            {
                gridViewMaster.OptionsView.ShowFooter = true;
            }
            if (TruongCap1.Text != string.Empty)
            {
                col1 = gridViewMaster.Columns[(int)TruongCap1._getSelectedID()];
                col1.Group();
                col1.Visible = true;
            }
            if (TruongCap2.Text != string.Empty)
            {
                if (TruongCap2.Text != TruongCap1.Text)
                {
                    col2 = gridViewMaster.Columns[(int)TruongCap2._getSelectedID()];
                    col2.Group();
                    col2.Visible = true;
                }
                else TruongCap2.SetError(error, "Trường thống kê này đã được sử dụng!");
            }
            if (TruongCap3.Text != string.Empty)
            {
                if (TruongCap3.Text != TruongCap1.Text && TruongCap3.Text != TruongCap2.Text)
                {
                    col3 = gridViewMaster.Columns[(int)TruongCap3._getSelectedID()];
                    col3.Group();
                    col3.Visible = true;
                }
                else TruongCap3.SetError(error, "Trường thống kê này đã được sử dụng!");
            }

            QueryBuilder filter = new QueryBuilder(UpdateRow());
            filter.addSoPhieu(BIEN_MUC.MA_BANG, MaBang.Text);
            filter.addLike("LOWER(" + BIEN_MUC.NOI_DUNG + ")", NoiDung.Text.ToLower());
            filter.addID(BIEN_MUC.SO_DKCB_HD, SoDKHDCB._getSelectedIDs());
            filter.addID(BIEN_MUC.TIET_MUC, TietMuc._getSelectedIDs());
            filter.addID(BIEN_MUC.LOAI_LUU_TRU, LoaiBang._getSelectedIDs());
            filter.addIn(BIEN_MUC.POST_MASTER, AppCtrl.GetCheckedValues(TinhTrang, true));
            filter.addID(BIEN_MUC.DON_VI_CUNG_CAP, DonViCungCap._getSelectedIDs());
            filter.addID(BIEN_MUC.DON_VI_SO_HUU, DonViSoHuu._getSelectedIDs());
            AppCtrl.addFilterFromTo(filter, BIEN_MUC.NGAY_NHAP, ngayNhap);
            long[] IDs = QuocGia._getSelectedIDs();
            if (IDs.Length > 0 && IDs.Length < QuocGia.Properties.GetItems().Count)
            {
                string cond = "(";
                foreach (long id in IDs)
                {
                    cond += BIEN_MUC.NUOC_SAN_XUAT + " like '%," + id + ",%' or ";
                }
                cond = cond.TrimEnd(' ', 'r', 'o');
                cond = cond += ")";
                filter.addCondition(cond);
            }
            filter.addCondition("1=1");
            DataSet ds = HelpDB.getDatabase().LoadDataSet(filter);
            gridControlMaster.DataSource = ds.Tables[0];
            SetSumary(col1);
            SetSumary(col2);
            SetSumary(col3);
               // gridViewMaster.UpdateSummary();

            return null;
        }

        /// <summary>Step 5: Xây dựng Query Buider cho việc tìm kiếm
        /// Xây dựng một QueryBuilder từ những chọn lựa trong phần filter.
        /// Từ QueryBuilder này ta có thể lấy được dữ liệu thỏa điều kiện.
        /// Nếu hỗ trợ duyệt thì trong câu truy vấn trả về 
        /// phải có cột là DUYET_BIT
        /// </summary>
        public override QueryBuilder PLBuildQueryFilter()
        {
            var query = new QueryBuilder(UpdateRow());
            if (!string.IsNullOrEmpty(TenCT.Text.Trim()))
            {
                query.addCondition(string.Format(@"(lower(ct.{0}) like '%{1}%' or lower(lps.{2}) like '%{1}%' )",
                    CHUONG_TRINH.NOI_DUNG, TenCT.Text.ToLower(), QL_LICH_PHAT_SONG.TEN_CT));
            }
            if (!string.IsNullOrEmpty(Category.Text.Trim()))
            {
                query.addCondition(string.Format(@"(lower(ct.{0}) like '%{1}%' or lower(lps.{2}) like '%{1}%' )",
                 CHUONG_TRINH.CATEGORY, Category.Text.ToLower(), QL_LICH_PHAT_SONG.CATEGORY));
            }
            if (TietMuc._IsNotCheckAnyOrCheckedAll == false)
            {
                query.addCondition(string.Format(@"(ct.{0} in {1} or lps.{2} in {1} )", CHUONG_TRINH.TIET_MUC,
                    TietMuc._getStrSelectedIDs(), QL_LICH_PHAT_SONG.TIET_MUC));
            }
            if (PostMaster._IsNotCheckAnyOrCheckedAll == false)
            {
                query.addCondition(string.Format(@"(CTPM.{0} in {1} or lps.{2} in {1} )",
                   CHUONG_TRINH_POST_MASTER.POST_MASTER_ID, PostMaster._getStrSelectedIDs(), QL_LICH_PHAT_SONG.POST_MASTER)
                  );
            }
            AppCtrl.addID(query, "LPS." + QL_LICH_PHAT_SONG.KENH_PHAT, KenhPhat);

            string fieldGio = string.Format("right( '00'|| extract(hour from lpsct.{0}),2)||':' ||right( '00'|| extract(minute from lpsct.{0}),2)", QL_LICH_PHAT_SONG_CT.GIO_PHAT_SONG);

            if (GioPhatTu.EditValue != null && GioPhatTu.Text != "")
            {
                query.addCondition(string.Format(@"({0}>='{1}')", fieldGio, GioPhatTu.Text));
            }

            if (GioPhatDen.EditValue != null && GioPhatDen.Text != "")
            {
                query.addCondition(string.Format(@"({0}<='{1}')", fieldGio, GioPhatDen.Text));
            }
            if (ThongTinThem.CheckedItems.Count == 1)
            {
                query.addIn("LPS." + QL_LICH_PHAT_SONG.IS_PHAT_LAI, AppCtrl.GetCheckedValues(ThongTinThem, true));
            }
            AppCtrl.addFilterFromTo(query, "LPSCT." + QL_LICH_PHAT_SONG_CT.NGAY_PHAT_SONG, NgayBatDauPhat);
            AppCtrl.addFilterFromTo(query, "LPS." + QL_LICH_PHAT_SONG.NGAY_TAO, ngayNhap);
            AppCtrl.addID(query, "LPS." + QL_LICH_PHAT_SONG.NGUOI_TAO, NguoiNhap);
            AppCtrl.addID(query, "LPS." + QL_LICH_PHAT_SONG.PHONG_BAN, PhongBan);

            long[] ds = ThuTrongTuan._getSelectedIDs();
            if (ThuTrongTuan._IsNotCheckAnyOrCheckedAll == false)
            {
                string cond = ds.Aggregate("(", (current, id) => current + ("LPS." + QL_LICH_PHAT_SONG.THU_TRONG_TUAN + " like '%," + id + ",%' or "));
                cond = cond.TrimEnd(' ', 'r', 'o');
                cond = cond + ")";
                query.addCondition(cond);
            }

            query.addCondition("1=1");
            query.addGroupBy(GroupByClause);
            if (ASCSortClause != "")
                query.setAscOrderBy(ASCSortClause);
            if (DESCSortClause != "")
                query.setDescOrderBy(DESCSortClause);

            long[] kp=KenhPhat._getSelectedIDs();
            if ( kp.Length == 1 && kp[0] != -1)
            {
                Col_LPS_KenhPhat.UnGroup();
            }
            else Col_LPS_KenhPhat.Group();

            if (NgayBatDauPhat.SelectedType == Framework.Win.Trial.SelectionTypes.OneDate)
            {
                Col_LPS_NgayPhat.UnGroup();
            }
            else
            {
                Col_LPS_NgayPhat.Group();
            }
            gridViewMaster.ViewCaption = "CHƯƠNG TRÌNH PHÁT SÓNG TRÊN " + (KenhPhat.Text == "" ? "CÁC KÊNH " : " KÊNH " + KenhPhat.Text) + " - " + NgayBatDauPhat.Caption.ToUpper();
            return query;
        }

        /// <summary>Step 5: Xây dựng Query Buider cho việc tìm kiếm
        /// Xây dựng một QueryBuilder từ những chọn lựa trong phần filter.
        /// Từ QueryBuilder này ta có thể lấy được dữ liệu thỏa điều kiện.
        /// Nếu hỗ trợ duyệt thì trong câu truy vấn trả về 
        /// phải có cột là DUYET_BIT
        /// </summary>
        public override QueryBuilder PLBuildQueryFilter()
        {
            string sql = UpdateRow();
            var query = new QueryBuilder(sql);
            query.addLike("LOWER(" + CHUONG_TRINH.NOI_DUNG + ")", NoiDung.Text.ToLower());
            query.addLike("LOWER(" + CHUONG_TRINH.TEN_GOC + ")", TenGoc.Text.ToLower());
            query.addLike("LOWER(" + CHUONG_TRINH.CATEGORY + ")", Category.Text.ToLower());
            AppCtrl.addID(query, CHUONG_TRINH.TIET_MUC, TietMuc);
            AppCtrl.addID(query, CHUONG_TRINH.DON_VI_CUNG_CAP, DonViCungCap);
            AppCtrl.addFilterFromTo(query, BIEN_MUC.NAM_SAN_XUAT, NamSXTu.Value, NamSXDen.Value);
            long[] ids = QuocGia._getSelectedIDs();
            if (ids.Length > 0 && ids.Length < QuocGia.Properties.GetItems().Count)
            {
                string cond = ids.Aggregate("(", (current, id) => current + (BIEN_MUC.NUOC_SAN_XUAT + " like '%," + id + ",%' or "));
                cond = cond.TrimEnd(' ', 'r', 'o');
                cond = cond + ")";
                query.addCondition(cond);
            }
            AppCtrl.addID(query, CHUONG_TRINH.BAN_QUYEN_THUOC, BanQuyenThuoc);
            query.addDateFromTo(CHUONG_TRINH.BAN_QUYEN_DID_END, BQDTTNEndTu.DateTime, BQDTTNEndDen.DateTime);
            query.addDateFromTo(CHUONG_TRINH.BAN_QUYEN_DTNN_END, BQDTNNEndTu.DateTime, BQDTNNEndDen.DateTime);
            AppCtrl.addFilterFromTo(query, CHUONG_TRINH.TONG_SO_RUN, TongSoRunTu.Value, TongSoRunDen.Value);
            AppCtrl.addFilterFromTo(query, CHUONG_TRINH.SO_LAN_DA_PHAT, SoRunDaDungTu.Value, SoRunDaDungDen.Value);
            AppCtrl.addFilterFromTo(query, "(" + CHUONG_TRINH.TONG_SO_RUN + "-" + CHUONG_TRINH.SO_LAN_DA_PHAT + ")", SoRunConLaiTu.Value, SoRunConLaiDen.Value);
            AppCtrl.addFilterFromTo(query, CHUONG_TRINH.NGAY_NHAP, ngayNhap);
            AppCtrl.addID(query, CHUONG_TRINH.PHONG_BAN, PhongBan);
            query.addIn(CHUONG_TRINH.IS_TRONG_KHO, AppCtrl.GetCheckedValues(TrongKho, true));
            string ftTimeslot = AppCtrl.getFilterFromTo("CTNPDT.TIMESLOT", TimeslotTu, TimeslotDen);
            if (ftTimeslot != "")
            {
                query.addCondition(string.Format(@"exists(select ctnpdt.ct_id from chuong_trinh_npdt   ctnpdt
                                                    where ctnpdt.timeslot is not null and ctnpdt.ct_id= ct.ct_id {0})", ftTimeslot));
            }
            string w1 = "";
            if(!LoaiLuuTru._IsNotCheckAnyOrCheckedAll)
            {
                w1 += " AND " + CHUONG_TRINH_POST_MASTER.LOAI_LUU_TRU + " in " + LoaiLuuTru._getStrSelectedIDs();
            }
            if (!SoDKCBHD._IsNotCheckAnyOrCheckedAll )
            {
                w1 += " AND exists(select chd.ctpm_hd_id from chuong_trinh_post_Master_hd chd where chd.ctpm_id=cp.ctpm_id and chd.so_dkcb_hd in " + SoDKCBHD._getStrSelectedIDs() + ")";

            }
            if (w1 != "")
                query.addCondition(string.Format(@"exists(select cp.ctpm_id from chuong_trinh_post_Master cp where (is_delete='N' or is_delete is null) and  cp.ct_id = ct.ct_id {0})", w1));

            string w2 = "";
            if (TapSoTu.Value > 0)
            {
                w2 += " AND bm.tap_so >= " + TapSoTu.Value;
            }
            if (TapSoDen.Value > 0)
            {
                w2 += " AND bm.tap_so <=" + TapSoDen.Value;
            }

            if (TenTap.Text.Trim() != "")
            {
                w2 += " AND lower(bm.ten_tap) like '%" + TenTap.Text.Trim().ToLower() + "%'";
            }

            if (w2 != "")
            {
                query.addCondition(string.Format("exists(select bm.bm_id from bien_muc bm where (is_delete='N' or is_delete is null) and bm.ct_id=ct.ct_id {0})", w2));
            }

            query.addCondition("1=1");
            if (ASCSortClause != "")
                query.setAscOrderBy(ASCSortClause);
            if (DESCSortClause != "")
                query.setDescOrderBy(DESCSortClause);
            return query;
        }

 public override QueryBuilder PLBuildQueryFilter()
 {
     AppCtrl.DisplayTheoTuyBienCot(gridViewMaster, checkListCol);
     QueryBuilder filter = new QueryBuilder(UpdateRow());
     filter.addSoPhieu(BIEN_MUC.MA_BANG, MaBang.Text);
     filter.addLike("LOWER(" + BIEN_MUC.NOI_DUNG + ")", NoiDung.Text.ToLower());
     AppCtrl.addID(filter, BIEN_MUC.SO_DKCB_HD, SoDKHDCB);
     AppCtrl.addID(filter, BIEN_MUC.TIET_MUC, TietMuc);
     AppCtrl.addID(filter, BIEN_MUC.LOAI_LUU_TRU, LoaiBang);
     AppCtrl.addID(filter, BIEN_MUC.DON_VI_CUNG_CAP, DonViCungCap);
     AppCtrl.addID(filter, BIEN_MUC.DON_VI_SO_HUU, DonViSoHuu);
     filter.addIn(BIEN_MUC.POST_MASTER, AppCtrl.GetCheckedValues(TinhTrang,true));
     filter.addIn(BIEN_MUC.IS_THANH_LY, AppCtrl.GetCheckedValues(ThanhLy, true));
     AppCtrl.addFilterFromTo(filter, BIEN_MUC.NGAY_NHAP, ngayNhap);
     AppCtrl.addFilterFromTo(filter, BIEN_MUC.NAM_SAN_XUAT, NamSXTu.Value, NamSXDen.Value);
     long[] IDs=QuocGia._getSelectedIDs();
     if (IDs.Length > 0 && IDs.Length < QuocGia.Properties.GetItems().Count)
     {
         string cond = "(";
         foreach (long id in IDs)
         {
             cond += BIEN_MUC.NUOC_SAN_XUAT + " like '%," + id + ",%' or ";
         }
         cond = cond.TrimEnd(' ', 'r', 'o');
         cond = cond += ")";
         filter.addCondition(cond);
     }
     filter.addCondition("1=1");
     foreach (string field in BIEN_MUC.DEFAUT_SORT_ASC_FIELDS)
     {
         filter.setAscOrderBy(field);
     }
     //CotTietMuc.GroupIndex = 0;
     //CotNoiDung.GroupIndex = 1;
     return filter;
 }

 public override QueryBuilder PLBuildQueryFilter()
 {
     PLOptions.ReSetData();
     QueryBuilder filter = new QueryBuilder(UpdateRow());
     filter.addID("NLV.NV_ID", PLNhanVien._getSelectedID());
     if (this.getISChamCong().Length == 3)
         filter.addCondition("(IS_CHAM_CONG in ('Y','N','') or IS_CHAM_CONG is null )");
     else
         filter.addIn("IS_CHAM_CONG", this.getISChamCong());
     StringBuilder s_condition = new StringBuilder("");
     if (chkDi_tre.Checked)
         s_condition.Append("GIO_BAT_DAU>='" + PLOptions.GIO_DI_TRE.TimeOfDay.ToString() + "'");
     if (chkVe_som.Checked && chkDi_tre.Checked)
         s_condition.Append("OR GIO_KET_THUC<CASE WHEN EXTRACT(WEEKDAY FROM NLV.NGAY_LAM_VIEC)=6 THEN '" + PLOptions.GIO_NGHI_TRUA.TimeOfDay.ToString() + "' ELSE '" + PLOptions.GIO_VE_SOM.TimeOfDay.ToString() + "' END");
     else if (chkVe_som.Checked)
         s_condition.Append("GIO_KET_THUC<CASE WHEN EXTRACT(WEEKDAY FROM NLV.NGAY_LAM_VIEC)=6 THEN '" + PLOptions.GIO_NGHI_TRUA.TimeOfDay.ToString() + "' ELSE '" + PLOptions.GIO_VE_SOM.TimeOfDay.ToString() + "' END");
     if (s_condition.Length > 0)
         filter.addCondition(s_condition.ToString());
     filter.addDateFromTo("NLV.NGAY_LAM_VIEC", TuNgay.DateTime, DenNgay.DateTime);
     filter.setDescOrderBy("NLV.NGAY_LAM_VIEC");
     filter.setAscOrderBy("NV.TEN_NV");
     return filter;
 }

 public override QueryBuilder PLBuildQueryFilter()
 {
     QueryBuilder filter = null;
     string sql = @"SELECT r.HO_TEN,r.MA_HO_SO,TT.ID,TT.NAME as TTHS, H.* FROM DS_HL_TV_TT H,RESUME r,DM_TINH_TRANG_HO_SO TT WHERE
                  r.id = H.r_id
                  and r.tths_id = TT.ID
                  and 1=1";
     filter = new QueryBuilder(sql);
     filter.addLike("r.MA_HO_SO", MaHoSo.Text.Trim().ToUpper());
     filter.addIn("r.TTHS_ID", DMTinhTrangHoSo.I.dsID_TT_HL_TRN());
     filter.addDateFromTo("H.NGAY_BAT_DAU", TuNgay.DateTime, DenNgay.DateTime);
     filter.addID("r.TTHS_ID", TinhTrangHoSo._getSelectedID());
     filter.addID("r.ID", TenUngVien._getSelectedID());
     if(SoNgayKetThuc.EditValue!=null)
         filter.addDateFromTo("H.NGAY_KET_THUC", TuNgay.DateTime, DateTime.Today.AddDays((double)SoNgayKetThuc.Value));
     return filter;
 }

 public override QueryBuilder PLBuildQueryFilter()
 {
     FWWaitingMsg msg = new FWWaitingMsg();
     QueryBuilder filter = new QueryBuilder(UpdateRow());
     filter.addID("NLV.NV_ID", NhanVien._getSelectedID());
     if (this.getISChamCong().Length == 3)
         filter.addCondition("(IS_CHAM_CONG in ('Y','N','') or IS_CHAM_CONG is null )");
     else
         filter.addIn("IS_CHAM_CONG", this.getISChamCong());
     if (chkBinhThuong.Checked ==  true &&
         chkDi_tre.Checked == true && chkVe_som.Checked == true) goto Nhan;
     StringBuilder s_condition = new StringBuilder("");
     if (chkDi_tre.Checked)
         s_condition.Append("GIO_BAT_DAU>='" + AppGetSysParam.GetGIO_BAT_DAU_SANG.ToString() + "'");
     if (chkVe_som.Checked && chkDi_tre.Checked)
         s_condition.Append(" OR GIO_KET_THUC<CASE WHEN EXTRACT(WEEKDAY FROM NLV.NGAY_LAM_VIEC)=6 THEN '" + AppGetSysParam.GetGIO_KET_THUC_SANG.ToString() + "' ELSE '" + ((DateTime)frmAppParamsHelp.GetThamSo("GIO_KET_THUC_CHIEU")).TimeOfDay.ToString() + "' END");
     else if (chkVe_som.Checked)
         s_condition.Append("GIO_KET_THUC<CASE WHEN EXTRACT(WEEKDAY FROM NLV.NGAY_LAM_VIEC)=6 THEN '" + AppGetSysParam.GetGIO_KET_THUC_SANG.ToString() + "' ELSE '" + ((DateTime)frmAppParamsHelp.GetThamSo("GIO_KET_THUC_CHIEU")).TimeOfDay.ToString() + "' END");
     if (s_condition.Length > 0)
     {
         if (chkBinhThuong.Checked)
             s_condition.Append(" OR (GIO_BAT_DAU<'" + AppGetSysParam.GetGIO_BAT_DAU_SANG.ToString() + "' AND GIO_KET_THUC >=CASE WHEN EXTRACT(WEEKDAY FROM NLV.NGAY_LAM_VIEC)=6 THEN '" + AppGetSysParam.GetGIO_KET_THUC_SANG.ToString() + "' ELSE '" + ((DateTime)frmAppParamsHelp.GetThamSo("GIO_KET_THUC_CHIEU")).TimeOfDay.ToString() + "'END)");
     }
     else {
         if (chkBinhThuong.Checked)
             s_condition.Append("(GIO_BAT_DAU<'" + AppGetSysParam.GetGIO_BAT_DAU_SANG.ToString() + "' AND GIO_KET_THUC >=CASE WHEN EXTRACT(WEEKDAY FROM NLV.NGAY_LAM_VIEC)=6 THEN '" + AppGetSysParam.GetGIO_KET_THUC_SANG.ToString() + "' ELSE '" + ((DateTime)frmAppParamsHelp.GetThamSo("GIO_KET_THUC_CHIEU")).TimeOfDay.ToString() + "' END)");
     }
     filter.addCondition(s_condition.ToString());
     //if (this.chkDi_tre.Checked || chkVe_som.Checked)
         //filter.addID("LOAI", 1);
     Nhan:
     if (ngayLamViec.Types != SelectionTypes.None)
         filter.addDateFromTo("NLV.NGAY_LAM_VIEC", ngayLamViec.FromDate, ngayLamViec.ToDate);
     filter.setDescOrderBy("NLV.NGAY_LAM_VIEC");
     filter.setAscOrderBy("NAME");
     msg.Finish();
     return filter;
 }

        /// <summary>Step 5: Xây dựng Query Buider cho việc tìm kiếm
        /// Xây dựng một QueryBuilder từ những chọn lựa trong phần filter.
        /// Từ QueryBuilder này ta có thể lấy được dữ liệu thỏa điều kiện.
        /// Nếu hỗ trợ duyệt thì trong câu truy vấn trả về 
        /// phải có cột là DUYET_BIT
        /// </summary>
        public override QueryBuilder PLBuildQueryFilter()
        {
            if (isAdvance)
            {
                try
                {

                    //if (filterControl1.FilterString != "")
                    //    filterControl1.FilterCriteria = PrepairFilter(filterControl1.FilterCriteria);
                    FilterControlHelper fch = new FirebirdFilterToSQLStatement(filterControl1);
                    SQLDATA sqlData = fch.GetSQLFilter(true, true);
                    DataSet ds = null;
                    if (sqlData.Filters != "")
                    {

                        ds = filterCase.DataSetFilterFromDatabase(sqlData, "category, noi_dung");
                    }
                    else
                    {
                        QueryBuilder query = new QueryBuilder(UpdateRow());
                        query.addCondition("1=1");
                        if (this.ASCSortClause != "")
                            query.setAscOrderBy(ASCSortClause);
                        if (this.DESCSortClause != "")
                            query.setDescOrderBy(DESCSortClause);
                        ds = HelpDB.getDatabase().LoadDataSet(query);
                    }
                    if (ds != null && ds.Tables.Count > 0)
                    {
                        gridControlMaster.DataSource = ds.Tables[0];
                        // filterControl1.FilterColumns.Remove(filterControl1.FilterColumns[BIEN_MUC.NGAY_PHAT_DAU_TIEN]);
                        //filterControl1.FilterColumns.Remove(filterControl1.FilterColumns[BIEN_MUC.THOI_HAN_BAN_QUYEN_DISPLAY]);

                    }
                }
                catch
                {

                }
                return null;

            }
            else
            {
                QueryBuilder filter = null;
                string sql = UpdateRow();
                filter = new QueryBuilder(sql);
                filter.addLike("LOWER(" + CHUONG_TRINH.NOI_DUNG + ")", NoiDung.Text.ToLower());
                filter.addLike("LOWER(" + CHUONG_TRINH.TEN_GOC + ")", TenGoc.Text.ToLower());
                filter.addLike("LOWER(" + CHUONG_TRINH.CATEGORY + ")", Category.Text.ToLower());
                AppCtrl.addID(filter, CHUONG_TRINH.TIET_MUC, TietMuc);
                AppCtrl.addID(filter, CHUONG_TRINH.DON_VI_CUNG_CAP, DonViCungCap);
                AppCtrl.addFilterFromTo(filter, BIEN_MUC.NAM_SAN_XUAT, NamSXTu.Value, NamSXDen.Value);
                long[] IDs = QuocGia._getSelectedIDs();
                if (IDs.Length > 0 && IDs.Length < QuocGia.Properties.GetItems().Count)
                {
                    string cond = "(";
                    foreach (long id in IDs)
                    {
                        cond += BIEN_MUC.NUOC_SAN_XUAT + " like '%," + id + ",%' or ";
                    }
                    cond = cond.TrimEnd(' ', 'r', 'o');
                    cond = cond += ")";
                    filter.addCondition(cond);
                }
                AppCtrl.addID(filter, CHUONG_TRINH.BAN_QUYEN_THUOC, BanQuyenThuoc);
                filter.addDateFromTo(CHUONG_TRINH.BAN_QUYEN_DID_END, BQDTTNEndTu.DateTime, BQDTTNEndDen.DateTime);
                filter.addDateFromTo(CHUONG_TRINH.BAN_QUYEN_DTNN_END, BQDTNNEndTu.DateTime, BQDTNNEndDen.DateTime);
                AppCtrl.addFilterFromTo(filter, CHUONG_TRINH.TONG_SO_RUN, TongSoRunTu.Value, TongSoRunDen.Value);
                AppCtrl.addFilterFromTo(filter, CHUONG_TRINH.SO_LAN_DA_PHAT, SoRunDaDungTu.Value, SoRunDaDungDen.Value);
                AppCtrl.addFilterFromTo(filter, "(" + CHUONG_TRINH.TONG_SO_RUN + "-" + CHUONG_TRINH.SO_LAN_DA_PHAT + ")", SoRunConLaiTu.Value, SoRunConLaiDen.Value);
                AppCtrl.addFilterFromTo(filter, CHUONG_TRINH.NGAY_NHAP, ngayNhap);
                AppCtrl.addID(filter, CHUONG_TRINH.PHONG_BAN, PhongBan);
                filter.addIn(Alias + CHUONG_TRINH.IS_TRONG_KHO, AppCtrl.GetCheckedValues(TrongKho, true));
                string ftTimeslot = AppCtrl.getFilterFromTo("CTNPDT.TIMESLOT", TimeslotTu, TimeslotDen);
                if (ftTimeslot != "")
                {
                    filter.addCondition(string.Format(@"exists(select ctnpdt.ct_id from chuong_trinh_npdt   ctnpdt
                                                    where ctnpdt.timeslot is not null and ctnpdt.ct_id= ct.ct_id {0})", ftTimeslot));
                }
                string w1 = "";
                if (!LoaiLuuTru._IsNotCheckAnyOrCheckedAll)
                {
                    w1 += " AND " + CHUONG_TRINH_POST_MASTER.LOAI_LUU_TRU + " in " + LoaiLuuTru._getStrSelectedIDs();
                }
                if (!SoDKCBHD._IsNotCheckAnyOrCheckedAll)
                {
                    w1 += " AND exists(select chd.ctpm_hd_id from chuong_trinh_post_Master_hd chd where chd.ctpm_id=cp.ctpm_id and chd.so_dkcb_hd in " + SoDKCBHD._getStrSelectedIDs() + ")";

                }
                if (w1 != "")
                    filter.addCondition(string.Format(@"exists(select cp.ctpm_id from chuong_trinh_post_Master cp where (is_delete='N' or is_delete is null) and  cp.ct_id = ct.ct_id {0})", w1));

                string w2 = "";
//.........这里部分代码省略.........