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 = ""; 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 != "") { filter.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)); } filter.addCondition("1=1"); if (this.ASCSortClause != "") filter.setAscOrderBy(ASCSortClause); if (this.DESCSortClause != "") filter.setDescOrderBy(DESCSortClause); return filter; } }