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;
}
private void btnView_Click(object sender, EventArgs e)
{
var sql =string.Format(@"SELECT PGS_ID,NAME,
TU_NGAY, DEN_NGAY, MA_PGS,IS_DELETE,FILE_NAME, NGUOI_TAO,
NGAY_TAO, NGUOI_CAP_NHAT, NGAY_CAP_NHAT,CONG_TY,
PHONG_BAN,GHI_CHU,NULL CONTENT_FILE,'' PATH_FILE
FROM TNS_PROGRAMS_GRID_SCR pgs
WHERE (is_delete='N' or is_delete is null) and 1=1");
var query = new QueryBuilder(sql);
query.addLike("LOWER(" + TNS_PROGRAMS_GRID_SCR.NAME + ")", TenNguon.Text.ToLower());
query.addLike("LOWER(" + TNS_PROGRAMS_GRID_SCR.FILE_NAME + ")", TenFile.Text.ToLower());
AppCtrl.addID(query, TNS_PROGRAMS_GRID_SCR.NGUOI_TAO, NguoiLap);
AppCtrl.addFilterFromTo(query, TNS_PROGRAMS_GRID_SCR.NGAY_TAO, NgayLap);
query.addDateFromTo(TNS_PROGRAMS_GRID_SCR.TU_NGAY, StartDateFrom.DateTime, StartDateTo.DateTime);
query.addDateFromTo(TNS_PROGRAMS_GRID_SCR.DEN_NGAY, EndDateFrom.DateTime, EndDateTo.DateTime);
query.addCondition("1=1");
query.setDescOrderBy("NGAY_TAO");
var ds = HelpDB.getDBService().LoadDataSet(query);
if(ds!=null&&ds.Tables.Count>0)
{
gridControlDetail.DataSource = ds.Tables[0];
}
}
public override QueryBuilder PLBuildQueryFilter()
{
QueryBuilder filter = new QueryBuilder(UpdateRow());
AppCtrl.addID(filter, "HD.SO_DKCB_HD", SO_DKCB_HD);
filter.addLike("LOWER(HD.NOI_LUU_TRU)", FileDinhKem.Text.Trim().ToLower());
AppCtrl.addID(filter, "HD.LOAI_FILE", LoaiFile);
filter.addLike("LOWER(CT." + CHUONG_TRINH.NOI_DUNG + ")", NoiDung.Text.ToLower());
filter.addLike("LOWER(CT." + CHUONG_TRINH.CATEGORY + ")", Category.Text.ToLower());
AppCtrl.addID(filter, "CT." + CHUONG_TRINH.TIET_MUC, TietMuc);
AppCtrl.addID(filter, "PM." + CHUONG_TRINH_POST_MASTER.POST_MASTER_ID, PostMaster);
AppCtrl.addFilterFromTo(filter, CHUONG_TRINH_POST_MASTER.NGAY_NHAP, NgayNhap);
filter.addCondition("1=1");
return filter;
}
private QueryBuilder GetDataTreeList1(string strBangLuong)
{
QueryBuilder filter = null;
// string _SQL = @"SELECT l.*,nv.NAME ,
// (select SUM(ct.SO_NGAY) from BANG_LUONG_CT ct where l.ID = ct.LUONG_ID) as SO_NGAY FROM BANG_LUONG l
// LEFT JOIN DM_NHAN_VIEN nv on l.NV_ID = nv.ID
// WHERE 1=1";
filter = new QueryBuilder(UpdateRow());
if (!accessPermision) filter.addID("l.NV_ID", FrameworkParams.currentUser.employee_id);
//filter.addBoolean("nv.VISIBLE_BIT", true);
if (strBangLuong.Contains("Năm"))
strBangLuong = strBangLuong.Substring(strBangLuong.IndexOf(' ') + 1);
filter.addLike("THANG_NAM", strBangLuong.Trim());
filter.setAscOrderBy("THANG_NAM");
filter.setAscOrderBy("nv.NAME");
//DatabaseFB db = DABase.getDatabase();
//DataSet ds1 = new DataSet();
//ds1 = db.LoadDataSet(filter);
//gridControlMaster.DataSource = ds1.Tables[0];
//if (ds1.Tables[0].Rows.Count > 0)
// gridViewMaster.SelectRow(0);
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()
{
QueryBuilder filter = null;
string sql = UpdateRow();
filter = new QueryBuilder(sql);
filter.addSoPhieu("CT." + CHUONG_TRINH.MA_CT, MaCT.Text);
filter.addLike("LOWER(CT." + CHUONG_TRINH.NOI_DUNG + ")", NoiDung.Text.ToLower());
AppCtrl.addID(filter, "CT." + CHUONG_TRINH.TIET_MUC, TietMuc);
AppCtrl.addFilterFromTo(filter, "PM." + CHUONG_TRINH_POST_MASTER.NGAY_NHAP, ngayNhap);
AppCtrl.addID(filter, "PM." + CHUONG_TRINH_POST_MASTER.PHONG_BAN, PhongBan);
AppCtrl.addID(filter, "PM." + CHUONG_TRINH_POST_MASTER.NGUOI_NHAP, NguoiNhap);
AppCtrl.addID(filter, "PM." + CHUONG_TRINH_POST_MASTER.POST_MASTER_ID, PostMaster);
AppCtrl.addID(filter, "PM." + CHUONG_TRINH_POST_MASTER.LOAI_LUU_TRU, LoaiLuuTru);
filter.addCondition("1=1");
return filter;
}
private void SearchBudget()
{
string where = "";
if (KeHoachBudgetPermission.I.IsPermission)
where = " exists(select is_per from CHECK_KEHOACHBUDGET_ISREAD(" + FrameworkParams.currentUser.employee_id + ",LPS.khbg_id) where is_per='Y') AND ";
string sql = string.Format(@"SELECT LPS.*, 'N' IS_CHON
FROM KE_HOACH_BUDGET LPS where {0} 1=1", where);
QueryBuilder filter = new QueryBuilder(sql);
filter.addLike("LOWER(" + KE_HOACH_BUDGET.MA_KHBG + ")", MaKeHoach.Text.ToLower());
filter.addLike("LOWER(" + KE_HOACH_BUDGET.TEN_KE_HOACH + ")", TenKeHoach.Text.ToLower());
AppCtrl.addID(filter, KE_HOACH_BUDGET.TRANG_THAI, TrangThai);
AppCtrl.addID(filter, KE_HOACH_BUDGET.KENH_PHAT, kenhPhat);
filter.addDateFromTo(KE_HOACH_BUDGET.NGAY_LAP, NgayLapTu.DateTime, NgayLapDen.DateTime);
filter.addCondition("1=1");
DataSet ds = HelpDB.getDatabase().LoadDataSet(filter);
if (ds != null && ds.Tables.Count > 0)
gridControlBudget.DataSource = ds.Tables[0];
else gridControlBudget.DataSource = 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()
{
QueryBuilder filter = null;
string sql = UpdateRow();
filter = new QueryBuilder(sql);
filter.addSoPhieu("CT."+CHUONG_TRINH.MA_CT, MaCT.Text);
filter.addLike("LOWER(CT." + CHUONG_TRINH.NOI_DUNG + ")", NoiDung.Text.ToLower());
AppCtrl.addID(filter, "CT." + CHUONG_TRINH.TIET_MUC, TietMuc);
AppCtrl.addFilterFromTo(filter, "PM." + CHUONG_TRINH_POST_MASTER.NGAY_NHAP, ngayNhap);
AppCtrl.addID(filter, "PM." + CHUONG_TRINH_POST_MASTER.PHONG_BAN, PhongBan);
AppCtrl.addID(filter, "PM." + CHUONG_TRINH_POST_MASTER.NGUOI_NHAP, NguoiNhap);
AppCtrl.addID(filter, "PM." + CHUONG_TRINH_POST_MASTER.POST_MASTER_ID, PostMaster);
AppCtrl.addID(filter, "PM." + CHUONG_TRINH_POST_MASTER.LOAI_LUU_TRU, LoaiLuuTru);
if (!SoDKCBHD._IsNotCheckAnyOrCheckedAll)
{
filter.addCondition("exists(select chd.ctpm_hd_id from chuong_trinh_post_Master_hd chd where chd.ctpm_id=pm.ctpm_id and chd.so_dkcb_hd in " + SoDKCBHD._getStrSelectedIDs() + ")");
}
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.ctpm_id=pm.ctpm_id {0})", w2));
}
filter.addCondition("1=1");
if (this.ASCSortClause != "")
filter.setAscOrderBy(ASCSortClause);
if (this.DESCSortClause != "")
filter.setDescOrderBy(DESCSortClause);
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()
{
QueryBuilder filter = null;
string sql = UpdateRow();
filter = new QueryBuilder(sql);
filter.addSoPhieu("PMT." + PHIEU_MUON_TRA.MA_PMT, MaPMT.Text);
filter.addLike("LOWER(PMT." + PHIEU_MUON_TRA.NGUOI_MUON + ")", NguoiMuon.Text.ToLower());
filter.addLike("LOWER(PMT." + PHIEU_MUON_TRA.BO_PHAN_MUON + ")", BoPhanMuon.Text.ToLower());
filter.addLike("LOWER(PMT." + PHIEU_MUON_TRA.CONG_TY_MUON + ")", CongTyMuon.Text.ToLower());
AppCtrl.addFilterFromTo(filter, "PMT." + PHIEU_MUON_TRA.NGAY_MUON, NgayMuon);
AppCtrl.addID(filter, PHIEU_MUON_TRA.PHONG_BAN, PhongBan);
string subSql = @"select pmtct.pmtct_id
from phieu_muon_tra_ct pmtct
left join chuong_trinh ct on pmtct.ct_id=ct.ct_id
left join PHIEU_MUON_TRA pm on pm.ctpm_id=pmtct.ctpm_id
left join bien_muc bm on bm.bm_id=pmtct.bm_id
where pmtct.pmt_id=pmt.pmt_id and 1=1";
QueryBuilder querySub = new QueryBuilder(subSql);
querySub.addLike("LOWER(CT." + CHUONG_TRINH.NOI_DUNG + ")", TenChuongTrinh.Text.ToLower());
querySub.addLike("lOWER(CT." + CHUONG_TRINH.CATEGORY + ")", Category.Text.ToLower());
AppCtrl.addID(querySub, "CT." + CHUONG_TRINH.TIET_MUC, TietMuc);
long[] IDs = NuocSX._getSelectedIDs();
if (IDs.Length > 0 && IDs.Length < NuocSX.Properties.GetItems().Count)
{
string cond = "(";
foreach (long id in IDs)
{
cond += "CT." + CHUONG_TRINH.NUOC_SAN_XUAT + " like '%," + id + ",%' or ";
}
cond = cond.TrimEnd(' ', 'r', 'o');
cond = cond += ")";
querySub.addCondition(cond);
}
AppCtrl.addID(querySub, "PM." + CHUONG_TRINH_POST_MASTER.POST_MASTER_ID, PostMaster);
AppCtrl.addID(querySub, "PM." + CHUONG_TRINH_POST_MASTER.LOAI_LUU_TRU, LoaiLuuTru);
AppCtrl.addFilterFromTo(querySub, "BM." + BIEN_MUC.TAP_SO, TapSoTu.Value, TapSoDen.Value);
filter.addExistQuery(querySub, "PMTCT");
filter.addCondition("1=1");
return filter;
}
public override QueryBuilder PLBuildQueryFilter()
{
#region dathq
QueryBuilder filter = new QueryBuilder(UpdateRow());
filter.addSoPhieu("UPPER(MA_HO_SO)", txtMaHoSo.Text.Trim().ToUpper());
filter.addLike("UPPER(HO_TEN)", Filter_Ten.Text.Trim().ToUpper());
filter.addLike("UPPER(EMAIL)", Filter_Email.Text.Trim().ToUpper());
filter.addID("TTHS_ID", Filter_TinhTrangHoSo._getSelectedID());
filter.addID("LOAI_HO_SO", PLLoaiHoSo._getSelectedID());
filter.addCondition(" EXTRACT(YEAR FROM NGAY_SINH) BETWEEN " + Filter_NamSinhTu.Value + " AND " + Filter_NamSinhDen.Value);
if (Filter_GTNam.Checked && Filter_GTNu.Checked) filter.addCondition("GIOI_TINH IN ('Y','N')");
else
{
if (Filter_GTNam.Checked) filter.addCondition("GIOI_TINH ='Y'");
else if (Filter_GTNu.Checked) filter.addCondition("GIOI_TINH ='N'");
}
StringBuilder textTuKhoa = FilterCheckBox();
filter.addCondition(textTuKhoa.ToString());
filter.addCondition(string.Format("NGAY_CAP_NHAT_HO_SO >= '{0}'", ngayCapNhat.DateTime.ToString("MM/dd/yyyy")));
long[] IDs = Filter_VTUngTuyen._getSelectedIDs();
if (IDs.Length > 0 && IDs.Length < Filter_VTUngTuyen.Properties.GetItems().Count)
{
string cond = "(";
foreach (long id in IDs)
{
cond += "VT_ID" + " like '%," + id + "' or " + "VT_ID" + " like '" + id + ",%' or " + "VT_ID" + " like '%," + id + ",%' or ";
}
cond = cond.TrimEnd(' ', 'r', 'o');
cond = cond += ")";
filter.addCondition(cond);
}
filter.addCondition("TTHS_ID IN (SELECT ID FROM DM_TINH_TRANG_HO_SO WHERE VISIBLE_BIT = 'Y')");
filter.addCondition(" 1=1 ");
FrameworkParams.wait = new WaitingMsg();
DataTable Resume = HelpDB.getDatabase().LoadDataSet(filter).Tables[0];
if (Resume != null)
{
//DataTable Resume_Ung_Tuyen = DABase.getDatabase().LoadDataSet(
//@"SELECT REUT.R_ID,REUT.VTUT_ID,NAME FROM RESUME_UNG_TUYEN REUT INNER JOIN DM_VI_TRI_UNG_TUYEN VT ON REUT.VTUT_ID=VT.ID").Tables[0];
//StringBuilder Str = new StringBuilder("");
//for (int i = 0; i < Filter_VTUngTuyen.ItemCount; i++)
//{
// if (Filter_VTUngTuyen.GetItemCheckState(i) == CheckState.Checked)
// Str.Append(Filter_VTUngTuyen.GetItemValue(i) + ",");
// Application.DoEvents();
//}
//if (Str.Length > 0) Str.Remove(Str.Length - 1, 1);
//for (int i = Resume.Rows.Count - 1; i >= 0; i--)
//{
// StringBuilder Str_cond = new StringBuilder("R_ID = " + HelpNumber.ParseInt64(Resume.Rows[i]["ID"]));
// if (Str.Length > 0)
// Str_cond.Append(" and VTUT_ID in (" + Str + ")");
// DataRow[] Arr_row = Resume_Ung_Tuyen.Select(Str_cond.ToString());
// if (Arr_row.Length > 0)
// {
// StringBuilder Str_Name = new StringBuilder("");
// foreach (DataRow row_InArr in Arr_row)
// {
// Str_Name.Append(row_InArr["NAME"] + ",");
// }
// if (Str_Name.Length > 0) Str_Name.Remove(Str_Name.Length - 1, 1);
// Resume.Rows[i]["VT_TUYEN_DUNG"] = Str_Name.ToString();
// }
// else Resume.Rows.RemoveAt(i);
// Application.DoEvents();
//}
//gridControlMaster.DataSource = Resume;
gridViewMaster.OptionsBehavior.Editable = true;
//pager = new PagerGrid(gridControlMaster, this.gridViewMaster, 5);
//New update for get detail of Ung Vien
//pager.DetailOfFirtsRows += new PagerGrid.GetDetail(PLLoadDataDetailPart);
//------------------------
//pg = new ProtocolVN.Framework.Win.PagerInfo();
this.splitContainerControl1.SplitterPosition = 200;
Cot_Ten.SortOrder = DevExpress.Data.ColumnSortOrder.Ascending;
if (FrameworkParams.wait != null) FrameworkParams.wait.Finish();
}
#endregion
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()
{
QueryBuilder filter = null;
string sql = UpdateRow();
filter = new QueryBuilder(sql);
filter.addSoPhieu("CT." + CHUONG_TRINH.MA_CT, MaCT.Text);
filter.addLike("LOWER(CT." + CHUONG_TRINH.NOI_DUNG + ")", NoiDung.Text.ToLower());
filter.addLike("LOWER(CT." + CHUONG_TRINH.TEN_GOC + ")", TenGoc.Text.ToLower());
filter.addLike("LOWER(CT." + CHUONG_TRINH.CATEGORY + ")", Category.Text.ToLower());
AppCtrl.addID(filter, "CT." + CHUONG_TRINH.TIET_MUC, TietMuc);
AppCtrl.addID(filter, "CT." + CHUONG_TRINH.NUOC_SAN_XUAT, NuocSX);
AppCtrl.addFilterFromTo(filter, "PM." + CHUONG_TRINH_POST_MASTER.NGAY_NHAP, ngayNhap);
AppCtrl.addID(filter, "PM." + CHUONG_TRINH_POST_MASTER.PHONG_BAN, PhongBan);
AppCtrl.addID(filter, "PM." + CHUONG_TRINH_POST_MASTER.NGUOI_NHAP, NguoiNhap);
AppCtrl.addID(filter, "PM." + CHUONG_TRINH_POST_MASTER.POST_MASTER_ID, PostMaster);
AppCtrl.addID(filter, "PM." + CHUONG_TRINH_POST_MASTER.LOAI_LUU_TRU, LoaiLuuTru);
filter.addCondition("1=1");
if (this.ASCSortClause != "")
filter.setAscOrderBy(ASCSortClause);
if (this.DESCSortClause != "")
filter.setDescOrderBy(DESCSortClause);
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;
}
}
public override QueryBuilder PLBuildQueryFilter()
{
AppCtrl.DisplayTheoTuyBienCot(gridViewMaster, checkListCol);
QueryBuilder filter = new QueryBuilder(UpdateRow());
filter.addID(BIEN_MUC.SO_DKCB_HD, SO_DKCB_HD._getSelectedIDs());
filter.addLike(string.Format("UPPER({0})",BIEN_MUC.NOI_LUU_TRU), FileDinhKem.Text.Trim().ToUpper());
AppCtrl.addFilterFromTo(filter, BIEN_MUC.NGAY_NHAP, NgayNhap);
filter.addCondition("1=1");
return filter;
}
private void BuildFilterString_Object(QueryBuilder filter, Field field)
{
Control ctr = filter_panel.Controls[0];
if (filter_type == 1)
{
CondText Text_ctr = (CondText)ctr;
filter.addLike(field.NAME, Text_ctr.GiaTri.Text);
}
else if (filter_type == 2)
{
CondNumber So_ctr = (CondNumber)ctr;
addNumFromTo(filter, field.NAME, So_ctr.SpinTu, So_ctr.SpinDen);
}
else if (filter_type == 3)
{
CondDate Ngay_ctr = (CondDate)ctr;
filter.addDateFromTo(field.NAME, Ngay_ctr.DateTu.DateTime, Ngay_ctr.DateDen.DateTime);
}
else if (filter_type == 4)
{
CondComboBox Combo_ctr = (CondComboBox)ctr;
filter.addID(field.NAME, Combo_ctr.ComboID._getSelectedID());
}
}
private void barbtTimKiem_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
GUIValidation.TrimAllData(new object[] { txtTenTaiLieu });
string str = @"SELECT * FROM DM_TAI_LIEU Where 1=1";
QueryBuilder query = new QueryBuilder(str);
query.addDuyet(PLDBUtil.FIELD_DUYET, DuyetSelect.layTrangThai());
query.addLike("upper(TEN_TAI_LIEU)", txtTenTaiLieu.Text.ToUpper());
if (node_ID != 1)
query.addID("LOAI_TAI_LIEU_ID", node_ID);
DataSet ds = DABase.getDatabase().LoadDataSet(query);
gridDSCongviec.DataSource = ds.Tables[0];
}
/// <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.addSoPhieu("CT." + CHUONG_TRINH.MA_CT, MaCT.Text);
filter.addLike("LOWER(CT." + CHUONG_TRINH.NOI_DUNG + ")", NoiDung.Text.ToLower());
AppCtrl.addID(filter, "CT." + CHUONG_TRINH.TIET_MUC, TietMuc);
AppCtrl.addFilterFromTo(filter, "PM." + CHUONG_TRINH_POST_MASTER.NGAY_NHAP, ngayNhap);
AppCtrl.addID(filter, "PM." + CHUONG_TRINH_POST_MASTER.PHONG_BAN, PhongBan);
AppCtrl.addID(filter, "PM." + CHUONG_TRINH_POST_MASTER.NGUOI_NHAP, NguoiNhap);
AppCtrl.addID(filter, "PM." + CHUONG_TRINH_POST_MASTER.POST_MASTER_ID, PostMaster);
AppCtrl.addID(filter, "PM." + CHUONG_TRINH_POST_MASTER.LOAI_LUU_TRU, LoaiLuuTru);
if (!SoDKCBHD._IsNotCheckAnyOrCheckedAll)
{
filter.addCondition("exists(select chd.ctpm_hd_id from chuong_trinh_post_Master_hd chd where chd.ctpm_id=pm.ctpm_id and chd.so_dkcb_hd in " + SoDKCBHD._getStrSelectedIDs() + ")");
}
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.ctpm_id=pm.ctpm_id {0})", w2));
}
filter.addCondition("1=1");
if (this.ASCSortClause != "")
filter.setAscOrderBy(ASCSortClause);
if (this.DESCSortClause != "")
filter.setDescOrderBy(DESCSortClause);
return filter;
}
}
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()
{
string str = @"SELECT DISTINCT KH.KH_ID, KH.TEN_KHACH_HANG , KH.WEBSITE WS,
KH.LINH_VUC_HOAT_DONG LVHD, KH.THONG_TIN_THEM TTT,KH.NKH_ID
FROM KHACH_HANG KH LEFT JOIN THONG_TIN_LIEN_HE TT ON TT.KH_ID = KH.KH_ID
WHERE 1=1";
QueryBuilder query = new QueryBuilder(str);
query.addID("KH.KH_ID", Khachhang._getSelectedID());
query.addID("TT.ID", NguoiLienHe._getSelectedID());
//if (plCbbWebsite.Text != "" && plCbbWebsite.Text != "Chọn giá trị") query.addLike("kh.website", plCbbWebsite.Text);
query.addID("KH.NKH_ID", plCbbWebsite._getSelectedID());
query.addLike("UPPER(KH.LINH_VUC_HOAT_DONG)", txtLVHD.Text.Trim().ToUpper());
DataSet ds = DABase.getDatabase().LoadDataSet(query);
gridControlMaster.DataSource = ds.Tables[0];
return null;
}
public override QueryBuilder PLBuildQueryFilter()
{
QueryBuilder filter = new QueryBuilder(UpdateRow());
filter.addLike("lower(CT." + CHUONG_TRINH.NOI_DUNG + ")", NoiDung.Text.ToLower());
filter.addLike("LOWER(CT." + CHUONG_TRINH.CATEGORY + ")", Category.Text.ToLower());
AppCtrl.addID(filter, "CT." + CHUONG_TRINH.TIET_MUC, TietMuc);
AppCtrl.addID(filter, "PM." + CHUONG_TRINH_POST_MASTER.POST_MASTER_ID, PostMaster);
AppCtrl.addID(filter, "PM." + CHUONG_TRINH_POST_MASTER.LOAI_LUU_TRU, LoaiLuuTru);
AppCtrl.addFilterFromTo(filter, CHUONG_TRINH_POST_MASTER.NGAY_NHAP, ngay);
AppCtrl.addID(filter, "CT." + CHUONG_TRINH.PHONG_BAN, PhongBan);
long ke = cboKe._getSelectedID();
long ngan = cboNgan._getSelectedID();
long tang = cboTang._getSelectedID();
if (ke == -2)
{
filter.addCondition("PM."+CHUONG_TRINH_POST_MASTER.KE + " IS NULL");
}
else if (ke > -1)
{
filter.addID("PM." + CHUONG_TRINH_POST_MASTER.KE, ke);
}
if (ngan == -2)
{
filter.addCondition("PM." + CHUONG_TRINH_POST_MASTER.NGAN + " IS NULL");
}
else if (ngan > -1)
{
filter.addID("PM." + CHUONG_TRINH_POST_MASTER.NGAN, ngan);
}
if (tang == -2)
{
filter.addCondition("PM." + CHUONG_TRINH_POST_MASTER.TANG + " IS NULL");
}
else if (tang > -1)
{
filter.addID("PM." + CHUONG_TRINH_POST_MASTER.TANG, tang);
}
filter.addCondition("1=1");
return filter;
}
private void Search(bool isEmpty)
{
string where = "";
if (isEmpty == false && ChuongTrinhPermission.I.IsPermission)
where = " exists(select is_per from check_chuongtrinh_isread(" + FrameworkParams.currentUser.employee_id + ",c.ct_id) where is_per='Y') AND ";
string sql = string.Format(@"select distinct iif(c.code_ct is null or char_length(c.code_ct )=0 or bm.code_bm is null or char_length( bm.code_bm)=0,null, c.code_ct||'_'||bm.code_bm) CODE_CT, c.NOI_DUNG TEN_CT,tm.NAME TIET_MUC,
(select LIST_NAME from get_nuoc_san_xuat(c.nuoc_san_xuat)) NUOC_SX,
cast( c.nam_san_xuat as a_str_20) NAM_SX, pm.name POST_MASTER,cast( bm.TAP_SO as a_str_10) TAP_SO, bm.ten_tap,'' RUN, '' GIA, bm.bm_id
from bien_muc bm
inner join chuong_trinh c on c.ct_id=bm.ct_id
left join dm_post_master pm on bm.post_master_id=pm.id
left join dm_tiet_muc tm on tm.id=c.tiet_muc
where {0} 1=1", where);
QueryBuilder filter = new QueryBuilder(sql);
if (isEmpty == false)
{
if (xtraTabControl1.SelectedTabPage.Name == xtraTabPageBasic.Name)
{
AppCtrl.addID(filter, "C." + CHUONG_TRINH.TIET_MUC, TietMuc);
AppCtrl.addID(filter, "C." + CHUONG_TRINH.PHONG_BAN, PhongBan);
AppCtrl.addID(filter, "bm." + BIEN_MUC.POST_MASTER_ID, PostMaster);
filter.addLike("LOWER(" + "C." + CHUONG_TRINH.NOI_DUNG + ")", NoiDung.Text.ToLower());
filter.addLike("LOWER(" + "C." + CHUONG_TRINH.TEN_GOC + ")", TenGoc.Text.ToLower());
filter.addLike("LOWER(" + "C." + CHUONG_TRINH.CATEGORY + ")", Category.Text.ToLower());
filter.addDateFromTo("C." + CHUONG_TRINH.NGAY_NHAP, ngayNhapCTTu.DateTime, ngayLapCTDen.DateTime);
filter.addCondition("(c.is_delete is null or c.is_delete='N') and (bm.is_delete is null or bm.is_delete='N') ");
filter.setAscOrderBy("C.NOI_DUNG, BM.TAP_SO");
}
else if (xtraTabControl1.SelectedTabPage.Name == xtraTabPageBudget.Name)
{
bool none = false;
if (gridViewBudget.RowCount == 0)
{
none = true;
}
DataRow[] rows = null;
if (none == false)
{
DataTable dt = gridControlBudget.DataSource as DataTable;
rows = dt.Select("IS_CHON='" + "Y'");
if (rows.Length == 0)
none = true;
}
if (none)
{
HelpMsgBox.ShowNotificationMessage("Vui lòng chọn kế hoạch budget trước khi liệt kê chương trình!");
return;
}
string khids = "";
foreach (DataRow r in rows)
{
khids += r[KE_HOACH_BUDGET.KHBG_ID] + ",";
}
khids = khids.TrimEnd(',');
string cond = string.Format(@" bm.bm_id in ( select abc.bm_id
from
(select ct.bm_id_2 bm_id
from ke_hoach_budget_ct ct
where ct.bm_id_2 is not null and ct.khbg_id in ({0})
union
select ct.bm_id_3 bm_id
from ke_hoach_budget_ct ct
where ct.bm_id_3 is not null and ct.khbg_id in ({0})
union
select ct.bm_id_4 bm_id
from ke_hoach_budget_ct ct
where ct.bm_id_4 is not null and ct.khbg_id in ({0})
union
select ct.bm_id_5 bm_id
from ke_hoach_budget_ct ct
where ct.bm_id_5 is not null and ct.khbg_id in ({0})
union
select ct.bm_id_6 bm_id
from ke_hoach_budget_ct ct
where ct.bm_id_6 is not null and ct.khbg_id in ({0})
union
select ct.bm_id_7 bm_id
from ke_hoach_budget_ct ct
where ct.bm_id_7 is not null and ct.khbg_id in ({0})
union
select ct.bm_id_8 bm_id
from ke_hoach_budget_ct ct
where ct.bm_id_8 is not null and ct.khbg_id in ({0})
) abc )", khids);
filter.addCondition(cond);
filter.setAscOrderBy("C.NOI_DUNG, BM.TAP_SO");
}
else
{
sql = @"select distinct iif(c.code_ct is null or char_length(c.code_ct )=0 or bm.code_bm is null or char_length( bm.code_bm)=0,null, c.code_ct||'_'||bm.code_bm) CODE_CT, c.ten_ct TEN_CT,tm.NAME TIET_MUC,
(select LIST_NAME from get_nuoc_san_xuat(c.nuoc_san_xuat)) NUOC_SX,
cast( c.nam_san_xuat as a_str_20) NAM_SX, pm.name POST_MASTER,cast( bm.TAP_SO as a_str_10) TAP_SO, bm.ten_tap,'' RUN, '' GIA, bm.bm_id
from ql_lich_phat_song_ct bm
inner join ql_lich_phat_song c on c.lps_id=bm.lps_id
left join dm_post_master pm on c.post_master =pm.id
left join dm_tiet_muc tm on tm.id=c.tiet_muc
where 1=1";
filter = new QueryBuilder(sql);
filter.addID("C.KENH_PHAT", plCombobox1._getSelectedID());
filter.addDateFromTo("BM.NGAY_PHAT_SONG", NgayPhatSong.SelectionStart, NgayPhatSong.SelectionEnd);
filter.setAscOrderBy("C.TEN_CT, BM.TAP_SO");
}
}
else
{
filter.addCondition("1=-1");
}
DataSet ds = HelpDB.getDBService().LoadDataSet(filter, AppConst.BANG_GIA_KE_TOAN_NAME);
if (ds != null && ds.Tables.Count > 0)
gridControlDetail.DataSource = ds.Tables[0];
}
public override QueryBuilder PLBuildQueryFilter()
{
var query = new QueryBuilder(UpdateRow());
query.addLike("LOWER(" + TNS_PROGRAMS_GRID_SCR.NAME + ")", TenNguon.Text.ToLower());
query.addLike("LOWER(" + TNS_PROGRAMS_GRID_SCR.FILE_NAME + ")", TenFile.Text.ToLower());
AppCtrl.addID(query, TNS_PROGRAMS_GRID_SCR.NGUOI_TAO, NguoiLap);
AppCtrl.addFilterFromTo(query, TNS_PROGRAMS_GRID_SCR.NGAY_TAO, NgayLap);
query.addDateFromTo(TNS_PROGRAMS_GRID_SCR.TU_NGAY, StartDateFrom.DateTime, StartDateTo.DateTime);
query.addDateFromTo(TNS_PROGRAMS_GRID_SCR.DEN_NGAY, EndDateFrom.DateTime, EndDateTo.DateTime);
query.addCondition("1=1");
query.setDescOrderBy(this.DESCSortClause);
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()
{
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()
{
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()
{
QueryBuilder filter = null;
string sql = UpdateRow();
filter = new QueryBuilder(sql);
filter.addLike("LOWER(" + "CT." + CHUONG_TRINH.NOI_DUNG + ")", NoiDung.Text.ToLower());
filter.addLike("LOWER(" + "CT." + CHUONG_TRINH.TEN_GOC + ")", TenGoc.Text.ToLower());
filter.addLike("LOWER(" + "CT." + CHUONG_TRINH.CATEGORY + ")", Category.Text.ToLower());
AppCtrl.addID(filter, "CT." + CHUONG_TRINH.TIET_MUC, TietMuc);
AppCtrl.addID(filter, "CT." + CHUONG_TRINH.DON_VI_CUNG_CAP, DonViCungCap);
AppCtrl.addFilterFromTo(filter, "CT." + CHUONG_TRINH.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 += "CT." + CHUONG_TRINH.NUOC_SAN_XUAT + " like '%," + id + ",%' or ";
}
cond = cond.TrimEnd(' ', 'r', 'o');
cond = cond += ")";
filter.addCondition(cond);
}
AppCtrl.addID(filter, "CT." + CHUONG_TRINH.BAN_QUYEN_THUOC, BanQuyenThuoc);
filter.addDateFromTo("CT." + CHUONG_TRINH.BAN_QUYEN_DID_END, BQDTTNEndTu.DateTime, BQDTTNEndDen.DateTime);
filter.addDateFromTo("CT." + CHUONG_TRINH.BAN_QUYEN_DTNN_END, BQDTNNEndTu.DateTime, BQDTNNEndDen.DateTime);
AppCtrl.addFilterFromTo(filter, "CT." + CHUONG_TRINH.TONG_SO_RUN, TongSoRunTu.Value, TongSoRunDen.Value);
AppCtrl.addFilterFromTo(filter, "CT." + CHUONG_TRINH.SO_LAN_DA_PHAT, SoRunDaDungTu.Value, SoRunDaDungDen.Value);
AppCtrl.addFilterFromTo(filter, "(" + "CT." + CHUONG_TRINH.TONG_SO_RUN + "-" + "CT." + CHUONG_TRINH.SO_LAN_DA_PHAT + ")", SoRunConLaiTu.Value, SoRunConLaiDen.Value);
AppCtrl.addFilterFromTo(filter, "CT." + CHUONG_TRINH.NGAY_NHAP, ngayNhap);
AppCtrl.addID(filter, "CT." + CHUONG_TRINH.PHONG_BAN, PhongBan);
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));
}
AppCtrl.addID(filter, "PM." + CHUONG_TRINH_POST_MASTER.LOAI_LUU_TRU, LoaiLuuTru);
if (!SoDKCBHD._IsNotCheckAnyOrCheckedAll)
{
filter.addCondition("exists(select chd.ctpm_hd_id from chuong_trinh_post_Master_hd chd where chd.ctpm_id=pm.ctpm_id and chd.so_dkcb_hd in " + SoDKCBHD._getStrSelectedIDs() + ")");
}
AppCtrl.addFilterFromTo(filter, "BM." + BIEN_MUC.TAP_SO, TapSoTu.Value, TapSoDen.Value);
filter.addLike("LOWER(" + "BM." + BIEN_MUC.TEN_TAP + ")", TenTap.Text.ToLower());
filter.addCondition("1=1");
return filter;
}
public override QueryBuilder PLBuildQueryFilter()
{
FWWaitingMsg msg = new FWWaitingMsg();
QueryBuilder filter = new QueryBuilder(UpdateRow());
filter.addLike("UPPER(HO_TEN)",Filter_TenUV.Text.Trim().ToUpper());
if (Filter_VongPV.Text != "")
filter.add("VONG_PHONG_VAN", Operator.Equal, (int)Filter_VongPV.Value, DbType.Int32);
filter.addDateFromTo("NGAY_GIO_PHONG_VAN", ngayLamViec.FromDate, ngayLamViec.ToDate);
filter.addHourFrom(Filter_GioPVTu.Time, "NGAY_GIO_PHONG_VAN");
filter.addHourTo(Filter_GioPVDen.Time, "NGAY_GIO_PHONG_VAN");
filter.addID("TTHS_ID", Filter_TinhTrangHoSo._getSelectedID());
filter.setAscOrderBy("HO_TEN");
filter.addCondition(" 1=1 ");
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()
{
CreateGridMaster();//Khởi tạo lại cột tương ứng với thời gian được chọn.
QueryBuilder query = new QueryBuilder(@"SELECT * FROM BANG_CHAM_CONG_64 WHERE 1=1");
query.addID("EMP_ID", PLMulNhanVien._getSelectedIDs());
query.addLike("THANG_NAM", dateThangNam._timeEdit.Text);
query.addCondition("1=1");
return query;
}
private void Search(bool isEmpty)
{
string where = "";
if (isEmpty == false && ChuongTrinhPermission.I.IsPermission)
where = " exists(select is_per from check_chuongtrinh_isread(" + FrameworkParams.currentUser.employee_id + ",c.ct_id) where is_per='Y') AND ";
string sql = string.Format(@"select c.*, c.code_ct code_ct_old
from chuong_trinh c
where (c.is_delete is null or c.is_delete='N') and 1=1");
QueryBuilder filter = new QueryBuilder(sql);
if (isEmpty == false)
{
AppCtrl.addID(filter, "C." + CHUONG_TRINH.TIET_MUC, TietMuc);
AppCtrl.addID(filter, "C." + CHUONG_TRINH.PHONG_BAN, PhongBan);
filter.addLike("LOWER(" + CHUONG_TRINH.CATEGORY + ")", Category.Text.ToLower());
AppCtrl.addFilterFromTo(filter, CHUONG_TRINH.NGAY_NHAP, NgayTao);
long[] IDs = NuocSX._getSelectedIDs();
if (IDs.Length > 0 && IDs.Length < NuocSX.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);
}
if (CodeFill.Checked != CodeEmpty.Checked)
{
if (CodeEmpty.Checked)
{
filter.addCondition(" (c.code_ct is null or char_length(c.code_ct )=0) ");
}
else filter.addCondition(" c.code_ct is not null and char_length(c.code_ct )>0");
}
filter.addCondition("1=1");
filter.setAscOrderBy("C.NOI_DUNG");
}
else
{
filter.addCondition("1=-1");
}
DataSet ds = HelpDB.getDBService().LoadDataSet(filter, CHUONG_TRINH.TABLE_NAME);
if (ds != null && ds.Tables.Count > 0)
gridControlDetail.DataSource = ds.Tables[0];
}
public override QueryBuilder PLBuildQueryFilter()
{
QueryBuilder filter = new QueryBuilder(UpdateRow());
filter.addLike("LOWER(" + KE_HOACH_LPS.MA_KHLPS + ")", MaKeHoach.Text.ToLower());
filter.addLike("LOWER(" + KE_HOACH_LPS.TEN_KE_HOACH + ")", TenKeHoach.Text.ToLower());
AppCtrl.addID(filter, KE_HOACH_LPS.NGUOI_LAP, NguoiLap);
AppCtrl.addID(filter, KE_HOACH_LPS.KENH_PHAT, kenhPhat);
AppCtrl.addFilterFromTo(filter, KE_HOACH_LPS.NGAY_LAP, NgayLap);
AppCtrl.addID(filter, CHUONG_TRINH.PHONG_BAN, PhongBan);
filter.addCondition("1=1");
filter.setDescOrderBy(this.DESCSortClause);
return filter;
}
public override QueryBuilder PLBuildQueryFilter()
{
var query = new QueryBuilder(UpdateRow());
query.addLike("LOWER(pgs." + TNS_SALE_REV_SCR.NAME + ")", TenNguon.Text.ToLower());
query.addLike("LOWER(" + TNS_SALE_REV_SCR.FILE_NAME + ")", TenFile.Text.ToLower());
AppCtrl.addID(query, TNS_SALE_CPP_SCR.TARGET, Target);
AppCtrl.addID(query, TNS_SALE_CPP_SCR.THI_TRUONG, ThiTruong);
AppCtrl.addID(query, TNS_SALE_REV_SCR.NGUOI_TAO, NguoiLap);
AppCtrl.addFilterFromTo(query, TNS_SALE_REV_SCR.NGAY_TAO, NgayLap);
if (TuNam.Text != "" && TuThang.Text != "")
{
query.add(TNS_SALE_REV_SCR.TU_NGAY, Operator.GreaterEqual,
new DateTime((int) TuNam.Value, (int) TuThang.Value, 1, 0, 0, 0), DbType.DateTime);
}
if (DenThang.Text != "" && DenNam.Text != "")
{
query.add(TNS_SALE_REV_SCR.DEN_NGAY, Operator.LessEqual,
new DateTime((int) DenNam.Value, (int) DenThang.Value, 1, 23, 59, 59), DbType.DateTime);
}
AppCtrl.addID(query, TNS_SALE_CPP_SCR.PHONG_BAN, PhongBan);
query.addCondition("1=1");
query.setDescOrderBy(this.DESCSortClause);
return query;
}
