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()
{
QueryBuilder filter = null;
string sql = UpdateRow();
filter = new QueryBuilder(sql);
filter.addCondition("1=1");
if (this.ASCSortClause != "")
filter.setAscOrderBy(ASCSortClause);
if (this.DESCSortClause != "")
filter.setDescOrderBy(DESCSortClause);
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 = 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;
}
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()
{
DataSet ds;
if (!checkTin_noi_bat.Checked)
{
QueryBuilder query = new QueryBuilder
(
" SELECT ID,PRIOR,TT.NHOM_TIN,TIEU_DE, NGAY_CAP_NHAT,TT.NGUOI_CAP_NHAT,NHOM_TIN,DUYET," +
" CASE WHEN PRIOR='Y' THEN 'Tin tức nổi bật' ELSE NULL END TIN_NOI_BAT" +
" FROM TIN_TUC TT WHERE 1=1"
);
query.addID("NHOM_TIN", PLNhomTT._getSelectedID());
query.addDateFromTo("NGAY_CAP_NHAT", dateTungay.DateTime, dateDenngay.DateTime);
query.setDescOrderBy("NGAY_CAP_NHAT");
if (DATinTuc.Instance.getNguoiDuyet(DATinTuc.quyenDuyet).Contains(FrameworkParams.currentUser.employee_id))
query.addDuyet(PLDBUtil.FIELD_DUYET, DuyetSelect.layTrangThai());
else
query.addCondition(string.Format("{0} = 2 or NGUOI_CAP_NHAT = {1}", PLDBUtil.FIELD_DUYET, FrameworkParams.currentUser.employee_id));
ds = DABase.getDatabase().LoadDataSet(query);
barSubItem1.Enabled = ds.Tables[0].Rows.Count > 0;
gridControlMaster.DataSource = ds.Tables[0];
HookFocusRow();
}
else
{
ds = DATinTuc.Instance.Get_5_tin(PLNhomTT._getSelectedID(), dateTungay.DateTime, dateDenngay.DateTime,DuyetSelect);
barSubItem1.Enabled = ds.Tables[0].Rows.Count > 0;
gridControlMaster.DataSource = ds.Tables[0];
HookFocusRow();
}
if (ds.Tables[0].Rows.Count == 0)
{
this.ThongTinNoiBat(null, null);
barButtonItemDuyet.Enabled = false;
barButtonItemK_Duyet.Enabled = false;
}
return null;
}
public void SetNext()
{
//LockControls = false;
string idT = GetIDsThu();
_doLichPhatSong = DALichPhatSongNew.I.LoadAll(-2);
_rootLichPhatSong = null;
_isAdd = true;
btnCreateDetail.Text = "Tạo chi tiết LPS >>";
var qr = new QueryBuilder(@"select first 1 ngay_phat_song from ql_lich_phat_song_ct lpsct
inner join ql_lich_phat_song lps on lps.lps_id=lpsct.lps_id
where extract(weekday from ngay_phat_song) in(" + idT + ") and 1=1");
qr.add(QL_LICH_PHAT_SONG_CT.NGAY_PHAT_SONG, Operator.GreaterEqual, NgayPhatCuoi.DateTime, DbType.DateTime);
qr.addID("lps." + QL_LICH_PHAT_SONG.KENH_PHAT, KenhPhat._getSelectedID());
qr.add(string.Format("extract(hour from {0})", "LPS." + QL_LICH_PHAT_SONG.GIO_PHAT_SONG), Operator.Equal, GioPhat.Time.Hour, DbType.Int32);
qr.add(string.Format("extract(minute from {0})", "LPS." + QL_LICH_PHAT_SONG.GIO_PHAT_SONG), Operator.Equal, GioPhat.Time.Minute, DbType.Int32);
qr.setDescOrderBy(QL_LICH_PHAT_SONG_CT.NGAY_PHAT_SONG);
var ds = HelpDB.getDatabase().LoadDataSet(qr);
var thus = AppCtrl.GetThuTrongTuan(ThuTrongTuan._getSelectedIDs());
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
NgayBatDau.DateTime = AppCtrl.GetNextDate((DateTime)ds.Tables[0].Rows[0][0], thus);
}
else
NgayBatDau.DateTime = AppCtrl.GetNextDate(NgayPhatCuoi.DateTime, thus);
_replaceType = ReplaceType.NONE;
NgayPhatCuoi.EditValue = null;
SoNgayPhat.EditValue = null;
GhiChu.Text = "";
NoiDung._setSelectedID(-1);
TenGoc.Text = "";
Category.Text = "";
TongTapPhat.EditValue = null;
TongTapCT.EditValue = null;
NuocSX._setSelectedIDs(new long[] { -1 });
NamSX.EditValue = null;
TietMuc._setSelectedID(-1);
PostMaster._setSelectedID(-1);
ThoiLuong.Text = "";
// LockControls = false;
_dsDetail.Clear();
gridControlDetail.DataSource = _dsDetail.Tables[0];
btnNext.Enabled = false;
btnDelete.Enabled = false;
btnCopy.Enabled = false;
Title.Text = "TẠO MỚI LỊCH PHÁT SÓNG";
ColStart.Visible = false;
NgayBatDau.Properties.ReadOnly = false;
KenhPhat.MainCtrl.Properties.ReadOnly = false;
ThuTrongTuan.Properties.ReadOnly = false;
GioPhat.Properties.ReadOnly = false;
_error.ClearErrors();
groupBox1.Visible = false;
}
/// <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 query = new QueryBuilder(UpdateRow());
query.addSoPhieu("LOWER(" + BANG_GIA_BUDGET.MA_BGBG + ")", MaKeHoach.Text.ToLower());
AppCtrl.addID(query, BANG_GIA_BUDGET.NGUOI_LAP, NguoiLap);
AppCtrl.addFilterFromTo(query, BANG_GIA_BUDGET.NGAY_LAP, NgayLap);
AppCtrl.addID(query, BANG_GIA_BUDGET.NGUOI_CAP_NHAT, NguoiXoa);
AppCtrl.addFilterFromTo(query, BANG_GIA_BUDGET.NGAY_CAP_NHAT,NgayXoa);
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();
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;
}
public override QueryBuilder PLBuildQueryFilter()
{
FWWaitingMsg msg = new FWWaitingMsg();
if (dtRecipient == null) dtRecipient = HelpDB.getDatabase().LoadDataSet("SELECT ID,NAME FROM DM_NHAN_VIEN").Tables[0];
QueryBuilder query = new QueryBuilder(UpdateRow());
StringBuilder cond = new StringBuilder("");
if (cmbNguoiYC._getSelectedID() != -1) cond.Append(string.Format("YC.NGUOI_GUI_ID = {0}", cmbNguoiYC._getSelectedID()));
long[] arrNguoiNhan = cmbNguoiNhan._SelectedIDs;
if (arrNguoiNhan.Length > 0 && cond.Length > 0) cond.Append(" OR ");
int temp = arrNguoiNhan.Length;
foreach (long id in arrNguoiNhan)
{
cond.Append(string.Format(@"(YC.NGUOI_NHAN_ID LIKE '{0}%')
OR (YC.NGUOI_NHAN_ID LIKE '%,{0},%') OR (YC.NGUOI_NHAN_ID LIKE '%,{0}')
OR (YCTL.NGUOI_NHAN_ID LIKE '{0}%')
OR (YCTL.NGUOI_NHAN_ID LIKE '%,{0},%') OR (YCTL.NGUOI_NHAN_ID LIKE '%,{0}')", id));
temp--;
if (temp > 0)
{
cond.Append(" OR ");
}
}
if (cond.Length > 0)
{
query.addCondition(cond.ToString());
}
if (mruEditYeuCau.Text.Trim() != "" && mruEditYeuCau.Text.Trim() != null)
{
query.addCondition(string.Format("CHU_DE = '{0}'", mruEditYeuCau.Text.Trim()));
}
if (PLMucuutien._getSelectedID() != -1)
query.add("MUC_UU_TIEN", Operator.Equal,PLMucuutien._getSelectedID() , DbType.Int32);
if (PLTinhtrang._getSelectedID() != -1)
query.add("TINH_TRANG", Operator.Equal, PLTinhtrang._getSelectedID(), DbType.Int32);
query.addDateFromTo("YC.NGAY_GUI", ngayLamViec.FromDate, ngayLamViec.ToDate);
query.setDescOrderBy("YC.NGAY_GUI");
DataSet ds = HelpDB.getDatabase().LoadDataSet(query);
foreach (DataRow row in ds.Tables[0].Rows)
row["NGUOI_NHAN"] = GetNameOfRecipient(row["NGUOI_NHAN_ID"].ToString());
gridControlMaster.DataSource = ds.Tables[0];
msg.Finish();
return null;
}
public override QueryBuilder PLBuildQueryFilter()
{
QueryBuilder filter = new QueryBuilder(UpdateRow());
filter.addID("NLV.NV_ID", PLNhanVien._getSelectedID());
filter.addCondition("NLV.LOAI=" + (Int32)TimeInOutType.NghiPhep);
filter.addDateFromTo("NLV.NGAY_LAM_VIEC", TuNgay.DateTime, DenNgay.DateTime);
filter.setDescOrderBy("NLV.NGAY_LAM_VIEC");
filter.setAscOrderBy("NV.TEN_NV");
filter.addDuyet(PLDBUtil.FIELD_DUYET, DuyetSelect.layTrangThai());
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()
{
AppCtrl.DisplayTheoTuyBienCot(gridViewMaster, repositoryItemCheckedCotHienThi);
//if (repositoryItemCheckedCotHienThi.GetCheckedItems().ToString() != string.Empty)
//{
// if (colKenhPhat.Visible) colKenhPhat.Group();
// if (colNgay.Visible) colNgay.Group();
// if (colTietMuc.Visible) colTietMuc.Group();
//}
QueryBuilder filter = new QueryBuilder(UpdateRow());
AppCtrl.addID(filter,LichPhatSong.KENH_PHAT,kenhPhat);
AppCtrl.addFilterFromTo(filter,LichPhatSong.NGAY_PHAT_SONG, ngayNhap);
filter.setDescOrderBy(LichPhatSong.NGAY_PHAT_SONG);
filter.setAscOrderBy(LichPhatSong.GIO_PHAT_SONG);
filter.addCondition("1=1");
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.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()
{
QueryBuilder filter = null;
string sql = @"select ctccv_id,(select name from dm_loai_cong_viecn lcv where lcv.id=ctcv.lcv_id) TEN_CV,
nv.ten_nv NHANVIEN,
ctcv.ngay_lam_viec,ctcv.ngay_cap_nhat,ctcv.lcv_id,ctcv.nv_id,ctcv.mo_ta,ctcv.thoi_gian_thuc_hien,ctcv.duyet
from chi_tiet_cong_viec ctcv inner join dm_nhan_vien nv on nv.id=ctcv.nv_id
where nv.visible_bit='Y' and 1=1";
filter = new QueryBuilder(sql);
filter.addID("LCV_ID", Loai_Cv._getSelectedID());
filter.addID("NV_ID", Nhan_vien._getSelectedID());
filter.addDateFromTo("NGAY_LAM_VIEC", TuNgay.DateTime, DenNgay.DateTime);
filter.addDuyet("DUYET", DuyetSelect.layTrangThai());
filter.addGroupBy("ctccv_id,lcv_id,nv.ten_nv,ngay_lam_viec,ngay_cap_nhat,nv_id,mo_ta,thoi_gian_thuc_hien,duyet");
filter.setDescOrderBy("NGAY_LAM_VIEC");
DataSet ds = DABase.getDatabase().LoadDataSet(filter);
gridControlMaster.DataSource = ds.Tables[0];
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()
{
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()
{
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;
}
public override QueryBuilder PLBuildQueryFilter()
{
FWWaitingMsg wait = new FWWaitingMsg();
//Viết theo cách này không áp dụng tìm kiếm nâng cao được
QueryBuilder query = new QueryBuilder(UpdateRow());
query.addID("PC.LCV_ID", LoaiCV._getSelectedID());
if (mruEditTenCongViec.Text.Trim() != "" && mruEditTenCongViec.Text.Trim() != null)
{
query.addCondition(string.Format("PC.CONG_VIEC = '{0}'", mruEditTenCongViec.Text.Trim()));
}
StringBuilder cond = new StringBuilder("");
if (NhanVien._getSelectedID() != -1) cond.Append(string.Format("NGUOI_GIAO = {0}", NhanVien._getSelectedID()));
long [] NTH_ID=NguoiThucHien._SelectedIDs;
if (NTH_ID.Length > 0 && cond.Length > 0) cond.Append(" OR ");
int temp = NTH_ID.Length;
foreach (long id in NTH_ID)
{
cond.Append("CT.MA_NV = " + id + "");
temp--;
if (temp > 0)
{
cond.Append(" OR ");
}
}
if (cond.Length > 0)
{
query.addCondition(cond.ToString());
}
query.addID("TINH_TRANG", Tinhtrang._getSelectedID());
query.addID("MUC_UU_TIEN", cmbDoUuTien._getSelectedID());
query.setDescOrderBy("PC.NGAY_BAT_DAU");
query.addDateFromTo("PC.NGAY_BAT_DAU", ngayLamViec.FromDate, ngayLamViec.ToDate);
GridDataSet = HelpDB.getDatabase().LoadDataSet(query);
GridDataSet.Tables[0].Columns.Add("NV_THAM_GIA");
//DataSet dùng cho việc tính tiến độ công việc
string sql = @"SELECT C.PCCV_ID,PHAN_TRAM_THAM_GIA,MAX(TIEN_DO) TIEN_DO,T.TONG_PHAN_TRAM
FROM CHI_TIET_PHAN_CONG C,
(SELECT PCCV_ID,SUM(PHAN_TRAM_THAM_GIA) TONG_PHAN_TRAM
FROM CHI_TIET_PHAN_CONG WHERE TIEN_DO = 0 GROUP BY PCCV_ID) T
WHERE TIEN_DO > 0 AND T.PCCV_ID = C.PCCV_ID AND
C.THOI_GIAN_CAP_NHAT IN (SELECT MAX(CT.THOI_GIAN_CAP_NHAT) FROM CHI_TIET_PHAN_CONG CT WHERE CT.PCCV_ID=T.PCCV_ID GROUP BY CT.MA_NV)
GROUP BY C.PCCV_ID,C.MA_NV,PHAN_TRAM_THAM_GIA,T.TONG_PHAN_TRAM";
DataSet ds = HelpDB.getDatabase().LoadDataSet(sql);
foreach (DataRow dr in GridDataSet.Tables[0].Rows)
{
String ten = "";
String sqlString = "SELECT CT.MA_NV, NV.NAME, CT.PHAN_TRAM_THAM_GIA FROM CHI_TIET_PHAN_CONG CT, DM_NHAN_VIEN NV WHERE CT.MA_NV = NV.ID AND CT.PCCV_ID = " + dr["PCCV_ID"].ToString() + " GROUP BY MA_NV,NAME, PHAN_TRAM_THAM_GIA";
DataTable dt = HelpDB.getDatabase().LoadDataSet(sqlString).Tables[0];
foreach (DataRow row in dt.Rows)
{
if (ten == "")
ten = ten + row["NAME"].ToString() + " (" + row["PHAN_TRAM_THAM_GIA"].ToString() + "%)";
else
ten = ten + "\n" + row["NAME"].ToString() + " (" + row["PHAN_TRAM_THAM_GIA"].ToString() + "%)";
}
dr["NV_THAM_GIA"] = ten;
//Tính giá trị cho cột tính độ dựa vào DataSet (ds) ở trên
long tam=HelpNumber.ParseInt64(dr["PCCV_ID"]);
DataRow[] arrRow = ds.Tables[0].Select(string.Format("PCCV_ID = {0}", HelpNumber.ParseInt64(dr["PCCV_ID"])));
decimal tongTienDo = 0;
foreach (DataRow row in arrRow)
tongTienDo += (HelpNumber.ParseInt64(row["TIEN_DO"]) * HelpNumber.ParseInt64(row["PHAN_TRAM_THAM_GIA"]));
if (arrRow.Length > 0) HelpNumber.RoundDecimal(tongTienDo /= HelpNumber.ParseInt64(arrRow[0]["TONG_PHAN_TRAM"]),2);
dr["TONG_TIEN_DO"] = tongTienDo.ToString() + "%";
}
try
{
if (GridDataSet != null && GridDataSet.Tables[0].Rows.Count > 0)
{
gridControlMaster.DataSource = GridDataSet.Tables[0];
splitContainerControl1.SplitterPosition = 171;
}
else
{
gridControlMaster.DataSource = null;
}
}
catch
{}
wait.Finish();
return null;
}
public override QueryBuilder PLBuildQueryFilter()
{
FWWaitingMsg msg = new FWWaitingMsg();
DataSet ds = new DataSet();
long nhom_tin = PLNhomTT._getSelectedID();
Feature fQuanTri = Permission.loadFeature(AppPermission.FQuanTriTinTuc.id
, AppPermission.FQuanTriTinTuc.featureName, FrameworkParams.currentUser.username);
if (!checkTin_noi_bat.Checked)
{
//Lấy tin nổi bật đã được duyệt.
QueryBuilder query = new QueryBuilder
(
@" SELECT ID,PRIOR,NHOM_TIN,TIEU_DE, NGAY_CAP_NHAT,NGUOI_CAP_NHAT,DUYET, CASE WHEN SO_NGAY_HIEU_LUC > 0 THEN DATEADD(DAY,SO_NGAY_HIEU_LUC,NGAY_CAP_NHAT) ELSE NULL END AS NGAY_HIEU_LUC,
CASE WHEN PRIOR='Y' THEN 'Tin tức nổi bật' ELSE NULL END TIN_NOI_BAT
FROM TIN_TUC WHERE PRIOR = 'Y' AND DUYET = '2' AND 1=1"
);
query.addDateFromTo("NGAY_CAP_NHAT", ngay.FromDate, ngay.ToDate);
if (fQuanTri.isRead)//(DATinTuc.Instance.getNguoiDuyet(DATinTuc.quyenDuyet).Contains(FrameworkParams.currentUser.employee_id))
query.addDuyet(PLConst.FIELD_DUYET, DuyetSelect.layTrangThai());
else
query.addCondition(string.Format("1=1 or NGUOI_CAP_NHAT = {0}", FrameworkParams.currentUser.employee_id));
query.addCondition("NHOM_TIN IN (SELECT ID FROM DM_NHOM_TIN_TUC WHERE VISIBLE_BIT ='Y')");
ds = TinTucPermission.I._LoadDataSetWithResGroupPermission(query, "NHOM_TIN", nhom_tin);
//Lấy tin mới nhất đã được duyệt.
QueryBuilder query1 = new QueryBuilder
(
@" SELECT ID,PRIOR,NHOM_TIN,TIEU_DE, NGAY_CAP_NHAT,NGUOI_CAP_NHAT,DUYET,CASE WHEN SO_NGAY_HIEU_LUC > 0 THEN DATEADD(DAY,SO_NGAY_HIEU_LUC,NGAY_CAP_NHAT) ELSE NULL END AS NGAY_HIEU_LUC,
CASE WHEN PRIOR='Y' THEN 'Tin tức nổi bật' ELSE NULL END TIN_NOI_BAT
FROM TIN_TUC WHERE PRIOR = 'N' AND DUYET = '2'
AND NGAY_CAP_NHAT = (SELECT MAX(NGAY_CAP_NHAT)
FROM TIN_TUC
WHERE PRIOR = 'N' AND DUYET = '2')
AND 1=1"
);
query1.addDateFromTo("NGAY_CAP_NHAT", ngay.FromDate, ngay.ToDate);
query1.addCondition("NHOM_TIN IN (SELECT ID FROM DM_NHOM_TIN_TUC WHERE VISIBLE_BIT ='Y')");
if (fQuanTri.isRead)//(DATinTuc.Instance.getNguoiDuyet(DATinTuc.quyenDuyet).Contains(FrameworkParams.currentUser.employee_id))
query1.addDuyet(PLConst.FIELD_DUYET, DuyetSelect.layTrangThai());
else
query1.addCondition(string.Format("1=1 or NGUOI_CAP_NHAT = {0}", FrameworkParams.currentUser.employee_id));
DataTable dtTT_Max = TinTucPermission.I._LoadDataSetWithResGroupPermission(query1, "NHOM_TIN", nhom_tin).Tables[0];
//Lấy các tin còn lại
QueryBuilder query2 = new QueryBuilder
(
@" SELECT ID,PRIOR,NHOM_TIN,TIEU_DE, NGAY_CAP_NHAT,NGUOI_CAP_NHAT,DUYET,CASE WHEN SO_NGAY_HIEU_LUC > 0 THEN DATEADD(DAY,SO_NGAY_HIEU_LUC,NGAY_CAP_NHAT) ELSE NULL END AS NGAY_HIEU_LUC,
CASE WHEN PRIOR='Y' THEN 'Tin tức nổi bật' ELSE NULL END TIN_NOI_BAT
FROM TIN_TUC WHERE NGAY_CAP_NHAT <> (SELECT MAX(NGAY_CAP_NHAT)
FROM TIN_TUC
WHERE PRIOR = 'N' AND DUYET = '2')
AND 1=1"
);
query2.addDateFromTo("NGAY_CAP_NHAT", ngay.FromDate, ngay.ToDate);
query2.addCondition("NHOM_TIN IN (SELECT ID FROM DM_NHOM_TIN_TUC WHERE VISIBLE_BIT ='Y')");
query2.setDescOrderBy("NGAY_CAP_NHAT");
if (ds.Tables[0].Rows.Count > 0)
query2.addCondition(string.Format("ID != '{0}'", ds.Tables[0].Rows[0]["ID"].ToString()));
if (fQuanTri.isRead)//(DATinTuc.Instance.getNguoiDuyet(DATinTuc.quyenDuyet).Contains(FrameworkParams.currentUser.employee_id))
query2.addDuyet(PLConst.FIELD_DUYET, DuyetSelect.layTrangThai());
else
query2.addCondition(string.Format("{0}='2' or NGUOI_CAP_NHAT = {1}", PLConst.FIELD_DUYET, FrameworkParams.currentUser.employee_id));
DataTable dtTT = TinTucPermission.I._LoadDataSetWithResGroupPermission(query2, "NHOM_TIN", nhom_tin).Tables[0];
foreach (DataRow row in dtTT_Max.Rows) ds.Tables[0].ImportRow(row);
foreach (DataRow row in dtTT.Rows) ds.Tables[0].ImportRow(row);
gridControlMaster.DataSource = ds.Tables[0];
barSubItem1.Enabled = ds.Tables[0].Rows.Count > 0;
HookFocusRow();
}
else
{
ds = DATinTuc.Instance.Get_5_tin(PLNhomTT._getSelectedID(), ngay.FromDate, ngay.ToDate, DuyetSelect);
barSubItem1.Enabled = ds.Tables[0].Rows.Count > 0;
gridControlMaster.DataSource = ds.Tables[0];
barSubItem1.Enabled = ds.Tables[0].Rows.Count > 0;
HookFocusRow();
}
if (ds.Tables[0].Rows.Count == 0)
{
this.ThongTinNoiBat(null);
barButtonItemDuyet.Enabled = false;
barButtonItemK_Duyet.Enabled = false;
}
msg.Finish();
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()
{
QueryBuilder query = new QueryBuilder(UpdateRow());
query.addSoPhieu("LOWER(" + BANG_GIA_BUDGET.MA_BGBG + ")", MaKeHoach.Text.ToLower());
AppCtrl.addID(query, BANG_GIA_BUDGET.NGUOI_LAP, NguoiLap);
AppCtrl.addFilterFromTo(query, BANG_GIA_BUDGET.NGAY_LAP, NgayLap);
if (ThoiGianApDung.SelectedType != ProtocolVN.Framework.Win.Trial.SelectionTypes.None)
{
query.add(BANG_GIA_BUDGET.TU_NGAY, Operator.GreaterEqual, ThoiGianApDung.FromDate.Date, DbType.Date);
query.add(BANG_GIA_BUDGET.DEN_NGAY, Operator.LessEqual, ThoiGianApDung.ToDate.Date, DbType.Date);
}
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()
{
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;
}
//Hiện form confirm trước khi lưu
private ConfirmSaveLPSResult ShowConfirm()
{
string strthus = GetIDsThu();
var qr = new QueryBuilder(@"select first 1 LPS_CT_ID from ql_lich_phat_song_ct lpsct
inner join ql_lich_phat_song lps on lps.lps_id=lpsct.lps_id
where extract(weekday from ngay_phat_song) in(" + strthus + ") and 1=1");
if (_doLichPhatSong.NGAY_BAT_DAU != null)
qr.add(QL_LICH_PHAT_SONG_CT.NGAY_PHAT_SONG, Operator.GreaterEqual, _doLichPhatSong.NGAY_BAT_DAU.Value.Date, DbType.DateTime);
qr.add("lps." + QL_LICH_PHAT_SONG.LPS_ID, Operator.NotEqual, _doLichPhatSong.LPS_ID, DbType.Int64);
qr.addID("lps." + QL_LICH_PHAT_SONG.KENH_PHAT, _doLichPhatSong.KENH_PHAT);
if (_doLichPhatSong.GIO_PHAT_SONG != null)
{
qr.add(string.Format("extract(hour from {0})", "LPS." + QL_LICH_PHAT_SONG.GIO_PHAT_SONG), Operator.Equal, _doLichPhatSong.GIO_PHAT_SONG.Value.Hour, DbType.Int32);
qr.add(string.Format("extract(minute from {0})", "LPS." + QL_LICH_PHAT_SONG.GIO_PHAT_SONG), Operator.Equal, _doLichPhatSong.GIO_PHAT_SONG.Value.Minute, DbType.Int32);
}
DataSet ds = HelpDB.getDatabase().LoadDataSet(qr);
bool show = ds != null && ds.Tables.Count != 0 && ds.Tables[0].Rows.Count > 0;
var rs = ConfirmSaveLPSResult.SAVE_NORMAL;
if (_doLichPhatSong.IsInsert == false)
{
#region Kiểm tra tính liên tục so với lịch phía trước
_doLichPhatSong.DSDetail.Tables[0].DefaultView.RowFilter = "STATE IS NULL or STATE <> 'DEL'";
_doLichPhatSong.DSDetail.Tables[0].DefaultView.Sort = QL_LICH_PHAT_SONG_CT.NGAY_PHAT_SONG;
DateTime dtNBD = ((DateTime)_doLichPhatSong.DSDetail.Tables[0].DefaultView[0][QL_LICH_PHAT_SONG_CT.NGAY_PHAT_SONG]).Date;
qr = new QueryBuilder(@"select first 1 ngay_phat_song from ql_lich_phat_song_ct lpsct
inner join ql_lich_phat_song lps on lps.lps_id=lpsct.lps_id
where extract(weekday from ngay_phat_song) in(" + strthus + ") and 1=1");
qr.add(QL_LICH_PHAT_SONG_CT.NGAY_PHAT_SONG, Operator.LessEqual, dtNBD, DbType.DateTime);
qr.add("lps." + QL_LICH_PHAT_SONG.LPS_ID, Operator.NotEqual, _doLichPhatSong.LPS_ID, DbType.Int64);
qr.addID("lps." + QL_LICH_PHAT_SONG.KENH_PHAT, KenhPhat._getSelectedID());
qr.add(string.Format("extract(hour from {0})", "LPS." + QL_LICH_PHAT_SONG.GIO_PHAT_SONG), Operator.Equal, GioPhat.Time.Hour, DbType.Int32);
qr.add(string.Format("extract(minute from {0})", "LPS." + QL_LICH_PHAT_SONG.GIO_PHAT_SONG), Operator.Equal, GioPhat.Time.Minute, DbType.Int32);
qr.setDescOrderBy(QL_LICH_PHAT_SONG_CT.NGAY_PHAT_SONG);
ds = HelpDB.getDatabase().LoadDataSet(qr);
int c = 0;
DateTime okdate = DateTime.MinValue;
if (ds != null && ds.Tables.Count != 0 && ds.Tables[0].Rows.Count > 0)
{
List<DayOfWeek> thutt = AppCtrl.GetThuTrongTuan(_doLichPhatSong.DSDetail.Tables[0].DefaultView[0][QL_LICH_PHAT_SONG_CT.THU_TRONG_TUAN].ToString());
var predt = (DateTime)ds.Tables[0].Rows[0][0];
predt = predt.AddDays(1);
okdate = predt;
while (predt < dtNBD)
{
if (thutt.Contains(predt.DayOfWeek))
{
if (c == 0)
okdate = predt;
c++;
break;
}
predt = predt.AddDays(1);
}
#endregion
}
if (c > 0 || show)
{
rs = ConfirmSaveLPSDialog.ShowConfirm(this);
}
if (c > 0 && rs == ConfirmSaveLPSResult.SAVE_ARRANGE)
{
string where = QL_LICH_PHAT_SONG_CT.NGAY_PHAT_SONG + ">='" + dtNBD + "'";
ArrangeDays(okdate, where);
_doLichPhatSong.DSDetail.Clear();
HelpDB.getDBService().LoadDataSet(_doLichPhatSong.DSDetail, string.Format(
"select * from QL_LICH_PHAT_SONG_CT where LPS_ID={0}", _doLichPhatSong.LPS_ID), "QL_LICH_PHAT_SONG_CT");
HelpDataSet.MergeDataSet(new[] { "LPS_CT_ID" }, _doLichPhatSong.DSDetail, _dsDetail, true);
_doLichPhatSong.DSDetail.Tables[0].DefaultView.RowFilter = "STATE IS NULL or STATE <> 'DEL'";
_doLichPhatSong.DSDetail.Tables[0].DefaultView.Sort = QL_LICH_PHAT_SONG_CT.NGAY_PHAT_SONG;
DataTable dtRS = _doLichPhatSong.DSDetail.Tables[0].DefaultView.ToTable(true, QL_LICH_PHAT_SONG_CT.NGAY_PHAT_SONG);
if (dtRS.Rows.Count > 0)
{
_doLichPhatSong.NGAY_BAT_DAU = (DateTime)dtRS.Rows[0][0];
_doLichPhatSong.SO_NGAY_PHAT = dtRS.Rows.Count;
_doLichPhatSong.NGAY_PHAT_CUOI = (DateTime)dtRS.Rows[dtRS.Rows.Count - 1][0];
}
}
}
else
{
if (show)
rs = ConfirmSaveLPSDialog.ShowConfirm(this);
}
return rs;
}
private void LoadData()
{
QueryBuilder query = new QueryBuilder(LichPhatSong.SQL_XEM_LICH_PHAT_SONG);
AppCtrl.addID(query, LichPhatSong.KENH_PHAT, kenhPhat);
AppCtrl.addFilterFromTo(query, LichPhatSong.NGAY_PHAT_SONG, ngayPhatSong);
query.setDescOrderBy(LichPhatSong.NGAY_PHAT_SONG);
query.setAscOrderBy(LichPhatSong.GIO_PHAT_SONG);
//query.addCondition(string.Format(" NGAY_PHAT_SONG BETWEEN '{0}' AND '{1}'", ngayPhatSong[0].ToString("MM/dd/yyyy"), ngayPhatSong[1].ToString("MM/dd/yyyy")));
gridDataSet = HelpDB.getDatabase().LoadDataSet(query);
if (gridDataSet.Tables[0].Rows.Count > 0) barButtonItemPrint.Enabled = barButtonItemCapNhat.Enabled = true;
else barButtonItemPrint.Enabled = barButtonItemCapNhat.Enabled = false;
gridControlMaster.DataSource = gridDataSet.Tables[0];
}
public override QueryBuilder PLBuildQueryFilter()
{
QueryBuilder query = new QueryBuilder(UpdateRow());
AppCtrl.addID(query, LichPhatSong.KENH_PHAT, kenhPhat);
AppCtrl.addFilterFromTo(query, LichPhatSong.NGAY_PHAT_SONG, ngayNhap);
query.setDescOrderBy(LichPhatSong.NGAY_PHAT_SONG);
query.setAscOrderBy(LichPhatSong.GIO_PHAT_SONG);
query.addCondition("1=1");
return query;
}
private static BarButtonItem AddXemTatCa(BarManager barManager, Bar mainBar,
BarButtonItem barButtonItemSearch, PopupControlContainer popUP,
BarCheckItem filter, GridView gridViewMaster, string filterString,
string ascSortClause, string descSortClause, string groupClause
)
{
int index = 0;
for (int i = 0; i < mainBar.LinksPersistInfo.Count; i++)
{
LinkPersistInfo info = mainBar.LinksPersistInfo[i];
if (info.Item.Name == barButtonItemSearch.Name)
{
info.BeginGroup = false;
index = i;
break;
}
}
var viewAll = new BarButtonItem
{
Caption = "Xem tất c&ả",
PaintStyle = BarItemPaintStyle.CaptionGlyph,
Glyph = FrameworkParams.imageStore.GetImage2020("Picture.png"),
Visibility = BarItemVisibility.Always
};
viewAll.Appearance.ForeColor = Color.Red;
Font bk = viewAll.Font;
viewAll.Appearance.Font = new Font(bk, FontStyle.Bold);
viewAll.Appearance.Options.UseFont = true;
viewAll.Appearance.Options.UseForeColor = true;
mainBar.LinksPersistInfo.Insert(index, new LinkPersistInfo(viewAll, true));
barManager.Items.Add(viewAll);
viewAll.ItemClick += delegate
{
var wait = new FWWaitingMsg();
try
{
var query = new QueryBuilder(filterString);
if (!string.IsNullOrEmpty(groupClause))
{
query.addGroupBy(groupClause);
}
if (ascSortClause != "")
query.setAscOrderBy(ascSortClause);
if (descSortClause != "")
query.setDescOrderBy(descSortClause);
var ds = HelpDB.getDBService().LoadDataSet(query);
gridViewMaster.ClearSelection();
if (ds != null && ds.Tables.Count > 0)
{
gridViewMaster.GridControl.DataSource = ds.Tables[0];
if (gridViewMaster.RowCount > 0)
{
gridViewMaster.FocusedRowHandle = -1;
gridViewMaster.SelectRow(0);
gridViewMaster.FocusedRowHandle = 0;
}
else
{
gridViewMaster.SelectRow(-1);
}
}
else gridViewMaster.GridControl.DataSource = null;
filter.Checked = false;
//if (frm == null
// || !(frm is PhieuQuanLyBandedChange)
// || ((PhieuQuanLyBandedChange)frm)._UsingCheckFiler)
//{
// popUP.Visible = false;
//}
ClearDataCtrl(popUP.Controls);
}
catch (Exception ex)
{
PLException.AddException(ex);
}
finally
{
wait.Finish();
}
};
return viewAll;
}
public void InitDanhSachThangTinhLuongLeft()
{
#region Lấy dữ liệu
QueryBuilder query = new QueryBuilder("select thang_nam , right(thang_nam,4) as nam, is_chot from bang_luong where 1=1");
query.setDescOrderBy("THANG_NAM");
query.addGroupBy("THANG_NAM,IS_CHOT");
DataSet ds = HelpDB.getDatabase().LoadDataSet(query, "BANG_LUONG");
DataTable Kq = new XLSortThangNam().Sort(ds.Tables[0]);
treeListBangLuong.ParentFieldName = "NAM";
query = new QueryBuilder("select right(thang_nam,4) as nam from bang_luong where 1=1");
query.setDescOrderBy("NAM");
query.addGroupBy("NAM");
DataSet dsyy = DABase.getDatabase().LoadDataSet(query, "BANG_LUONG");
foreach (DataRow dong in dsyy.Tables[0].Rows)
{
TreeListNode n = treeListBangLuong.AppendNode(new object[] { "Năm " + dong["NAM"].ToString(), "N" }, -1);
foreach (DataRow item in Kq.Rows)
{
if (HelpNumber.ParseInt32(item["NAM"]) == HelpNumber.ParseInt32(dong["NAM"]))
{
treeListBangLuong.AppendNode(new object[] { item["THANG_NAM"], item["IS_CHOT"] }, n.Id);
strThangNam.Append(item["THANG_NAM"].ToString() + ",");
}
}
}
if (treeListBangLuong.FocusedNode != null)
{
barButtonItemChot.Enabled = treeListBangLuong.FocusedNode.GetDisplayText("THANG_NAM").Contains("Năm") ? false : true;
strBangLuong = treeListBangLuong.FocusedNode.FirstNode.GetValue("THANG_NAM").ToString();
treeListBangLuong.SetFocusedNode(treeListBangLuong.FocusedNode.FirstNode);
}
treeListBangLuong.ExpandAll();
#endregion
#region Tạo sự kiện
treeListBangLuong.FocusedNodeChanged += new FocusedNodeChangedEventHandler(treeList1_FocusedNodeChanged);
#endregion
}
/// <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;
}
public override QueryBuilder PLBuildQueryFilter()
{
AppCtrl.DisplayTheoTuyBienCot(gridViewMaster, repositoryItemCheckedCotHienThi);
QueryBuilder query = new QueryBuilder(UpdateRow());
AppCtrl.addID(query, LichPhatSong.KENH_PHAT, kenhPhat);
AppCtrl.addFilterFromTo(query, LichPhatSong.NGAY_PHAT_SONG, ngayNhap);
query.setDescOrderBy(LichPhatSong.NGAY_PHAT_SONG);
query.setAscOrderBy(LichPhatSong.GIO_PHAT_SONG);
query.addCondition("1=1");
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()
{
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;
}
}
