private void btnView_Click(object sender, EventArgs e)
{
if(KenhPhat._getSelectedID()<=1)
{
HelpMsgBox.ShowNotificationMessage("Vui lòng chọn kênh phát");
return;
}
var sql =
string.Format(
@"select ct.time_start,ct.time_end,ct.kenh_phat,iif(c.ct_id is null, tct.noi_dung, c.noi_dung) noi_dung,
c.nuoc_san_xuat, ct.rate_group,datediff(minute,ct.time_start,ct.time_end) timesplit
from tns_programs_grid_scr_ct ct
left join tns_chuong_trinh tct on tct.tct_id=ct.chuong_trinh
left join chuong_trinh c on c.ct_id=tct.ct_id
where 1=1");
var query = new QueryBuilder(sql);
AppCtrl.addID(query, "ct.PGS_id", FileNguon);
query.addID("CT.KENH_PHAT", KenhPhat._getSelectedID());
var dt = NgayPhatSong.DateTime;
query.addDateFromTo("CT.NGAY_PHAT", new DateTime(dt.Year, dt.Month, dt.Day, 0, 0, 0),
new DateTime(dt.Year, dt.Month, dt.Day, 23, 59, 59));
query.setAscOrderBy("ct.time_start, ct.kenh_phat");
var ds = HelpDB.getDBService().LoadDataSet(query, KE_HOACH_LPS_CT.TABLE_NAME);
gridControlDetail.DataSource = ds.Tables[0];
gridBand1.Caption = dt.ToString("ddd " + FrameworkParams.option.dateFormat);
}
private void btnView_Click(object sender, EventArgs e)
{
var sql =
string.Format(
@"select right( '00'||extract(hour from ct.time_start),2) ||':00 '|| ' - ' ||right('00'||(extract(hour from ct.time_start) +1),2) ||':00' VET,
kp.name kenh_phat,avg(ct.rate_group)
from tns_programs_grid_scr_ct ct
left join tns_kenh_phat kp on ct.kenh_phat=kp.id
where 1=1");
var query = new QueryBuilder(sql);
AppCtrl.addID(query, "ct.PGS_id", FileNguon);
AppCtrl.addID(query, "CT.KENH_PHAT", KenhPhat);
query.addDateFromTo("CT.NGAY_PHAT", TuNgay.DateTime, DenNgay.DateTime);
query.addGroupBy(@"right( '00'||extract(hour from ct.time_start),2) ||':00 '|| ' - ' ||right('00'||(extract(hour from ct.time_start) +1),2) ||':00',
kp.name");
query.setAscOrderBy("ct.time_start, ct.kenh_phat");
var ds = HelpDB.getDBService().LoadDataSet(query, KE_HOACH_LPS_CT.TABLE_NAME);
if (ds != null && ds.Tables.Count > 0)
{
var dt = ds.Tables[0];
var dtS = dt.DefaultView.ToTable(true, "VET");
}
gridControlDetail.DataSource = ds.Tables[0];
}
public static void ChonNhomTin(PLImgCombobox Input, bool? IsAdd)
{
DataSet dsNTT;
//true:add, false:edit, null:xem
QueryBuilder query = new QueryBuilder("select ID,NAME from DM_NHOM_TIN_TUC WHERE 1=1");
if (IsAdd == true)
query.addCondition("VISIBLE_BIT='Y'");
query.setAscOrderBy("NAME");
dsNTT = TinTucPermission.I._LoadDataSetWithResGroupPermission(query, "ID");
Input._init(dsNTT.Tables[0], "NAME", "ID");
}
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;
}
public static DataSet GetDataSet(bool? IsAdd)
{
string sql = string.Format(@"SELECT *
From {0}
where 1=1", TABLE_MAP);
QueryBuilder query = new QueryBuilder(sql);
if (IsAdd == true)
query.addCondition("VISIBLE_BIT='Y'");
query.setAscOrderBy("lower(NAME)");
DataSet ds = HelpDB.getDatabase().LoadDataSet(query, TABLE_MAP);
return ds;
}
public static DataTable GetUsingLoaiGhiChu()
{
string sql = string.Format(@"SELECT NAME,ID
From {0}
where exists(select first 1 1 from chuong_trinh_ghi_chu where loai_ghi_chu=id) and 1=1", TABLE_MAP);
var query = new QueryBuilder(sql);
query.addCondition("VISIBLE_BIT='Y'");
query.setAscOrderBy("ID");
DataSet ds = HelpDB.getDatabase().LoadDataSet(query, TABLE_MAP);
if (ds != null && ds.Tables.Count > 0)
return ds.Tables[0];
return null;
}
public static RepositoryItemLookUpEdit InitCot(GridColumn column, string fieldName, bool? IsAdd)
{
string sql = string.Format(@"SELECT *
From {0}
where 1=1", TABLE_MAP);
QueryBuilder query = new QueryBuilder(sql);
if (IsAdd == true)
query.addCondition("VISIBLE_BIT='Y'");
query.setAscOrderBy("lower(NAME)");
DataSet ds = HelpDB.getDatabase().LoadDataSet(query, TABLE_MAP);
return HelpGridColumn.CotCombobox(column, ds, "ID", "NAME", fieldName);
}
public static DataTable GetUsingDTH(bool? isAdd)
{
string sql = string.Format(@"SELECT NAME,id
From {0}
where (exists(select first 1 1 from chuong_trinh_npdt where dai_truyen_hinh=id)
or exists(select first 1 1 from chuong_trinh_psk where dai_truyen_hinh=id)) and
1=1", TABLE_MAP);
var query = new QueryBuilder(sql);
if (isAdd == true)
query.addCondition("VISIBLE_BIT='Y'");
query.setAscOrderBy("ID");
DataSet ds = HelpDB.getDatabase().LoadDataSet(query, TABLE_MAP);
if (ds != null && ds.Tables.Count > 0)
return ds.Tables[0];
return null;
}
public static void InitCtrl(PLCombobox Input, bool? IsAdd)
{
string sql = string.Format(@"SELECT *
From {0}
where 1=1", TABLE_MAP);
QueryBuilder query = new QueryBuilder(sql);
if (IsAdd == true)
query.addCondition("VISIBLE_BIT='Y'");
query.setAscOrderBy("lower(NAME)");
DataSet ds = HelpDB.getDatabase().LoadDataSet(query, TABLE_MAP);
Input.DataSource = ds.Tables[0];
Input.DisplayField = "NAME";
Input.ValueField = "ID";
Input._init();
}
public static void InitCtrl(PLCombobox Input, bool? IsAdd)
{
string sql = string.Format(@"SELECT *
From {0}
where 1=1", TABLE_MAP);
QueryBuilder query = new QueryBuilder(sql);
if (IsAdd == true)
{
query.addID("CONG_TY", FrameworkParams.LoginCompanyID);
query.addCondition("VISIBLE_BIT='Y'");
}
query.setAscOrderBy("lower(NAME)");
DataSet ds = HelpDB.getDatabase().LoadDataSet(query,TABLE_MAP);
Input.MainCtrl.Properties.SortColumnIndex = -1;
Input.DataSource = AppUtil.SortLogicalTable(ds.Tables[0], "NAME");
Input.DisplayField = "NAME";
Input.ValueField = "ID";
Input._init();
}
public static BarButtonItem AddXemTatCa(BarManager barManager, Bar mainBar,
BarButtonItem barButtonItemSearch,PopupControlContainer popUP,BarCheckItem filter, GridView gridViewMaster,RepositoryItemCheckedComboBoxEdit listChoseColumn, string filterString
)
{
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;
}
}
BarButtonItem viewAll = new BarButtonItem();
viewAll.Caption = "Xem tất c&ả";
viewAll.PaintStyle = BarItemPaintStyle.CaptionGlyph;
viewAll.Glyph = FrameworkParams.imageStore.GetImage2020("Picture.png");
viewAll.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(object sender, ItemClickEventArgs e)
{
try
{
if (listChoseColumn != null)
{
AppCtrl.DisplayTheoTuyBienCot(gridViewMaster, listChoseColumn);
}
QueryBuilder query = new QueryBuilder(filterString);
foreach (string sort in BIEN_MUC.DEFAUT_SORT_ASC_FIELDS)
{
query.setAscOrderBy(sort);
}
DataSet ds = HelpDB.getDBService().LoadDataSet(query);
if (ds != null && ds.Tables.Count > 0)
gridViewMaster.GridControl.DataSource = ds.Tables[0];
else gridViewMaster.GridControl.DataSource = null;
popUP.Visible = false;
filter.Checked = false;
foreach (Control ctrl in popUP.Controls)
{
if (ctrl is PLMultiCombobox)
{
PLMultiCombobox plm = ctrl as PLMultiCombobox;
plm._setSelectedIDs(new long[] {-1});
}
else if (ctrl is SpinEdit)
{
SpinEdit spin = ctrl as SpinEdit;
spin.EditValue = null;
}
else if (ctrl is TextEdit)
{
TextEdit txt = ctrl as TextEdit;
txt.Text = null;
}
else if (ctrl is DateEdit)
{
DateEdit date = ctrl as DateEdit;
date.EditValue = null;
}
else if (ctrl is CheckedListBoxControl)
{
CheckedListBoxControl check = ctrl as CheckedListBoxControl;
check.UnCheckAll();
}
else if(ctrl is PLDateSelection)
{
PLDateSelection date = ctrl as PLDateSelection;
if (date.AllowNull) date.SelectedType = SelectionTypes.None;
}
}
}
catch
{
}
};
return viewAll;
}
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 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];
}
private DataTable LoadTable(string TableName , int[] RootID)
{
QueryBuilder query = new QueryBuilder("SELECT * FROM " + TableName + " WHERE 1=1");
if (RootID != null)
{
string ids = "";
for (int i = 0 ; i < RootID.Length ; i++)
{
ids += RootID[i].ToString() + ",";
//query.addID(PLFN.ID_ROOT, RootID[i]);
//query.addID(this.ParentFieldName, RootID[i]);
}
query.addCondition(GlobalConst.ID_ROOT + " in (" + ids + "0)");
}
else
{
//query.addCondition(this.ParentFieldName + " is null ");
//query.addID(this.ParentFieldName, -1);
}
query.setAscOrderBy(this.order);
DataSet ds = DABase.getDatabase().LoadDataSet(query , TableName);
return 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()
{
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;
}
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()
{
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;
}
private QueryBuilder GetDataTreeList2(long ID)
{
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 (IdNhanVien < 0)
{
filter.addID("nv.DEPARTMENT_ID", -1 * IdNhanVien);
if (!accessPermision) filter.addID("nv.ID", FrameworkParams.currentUser.employee_id);
}
else
filter.addID("nv.ID", IdNhanVien);
//filter.addBoolean("nv.VISIBLE_BIT", true);
filter.setAscOrderBy("nv.NAME");
filter.setAscOrderBy("THANG_NAM");
//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.FocusedRowHandle = 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()
{
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;
}
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 static void InitCtrlThuTrongTuan(PLMultiCombobox input)
{
string sql = string.Format(@"SELECT *
From DAY_OF_WEEK
where 1=1");
var query = new QueryBuilder(sql);
query.setAscOrderBy("ID");
var ds = HelpDB.getDatabase().LoadDataSet(query, "DAY_OF_WEEK");
input.DataSource = ds.Tables[0];
input.DisplayField = "NAME";
input.ValueField = "ID";
input.UseSort = false;
input._init();
input.Properties.SelectAllItemCaption = "(Cả tuần)";
}
public static void InitCtrlPhongBan(PLDMTreeMultiChoice input, bool? isAdd)
{
string sql = string.Format(@"SELECT *
From DEPARTMENT
where 1=1");
var query = new QueryBuilder(sql);
if (isAdd == true)
query.addCondition("VISIBLE_BIT='Y'");
query.setAscOrderBy("lower(NAME)");
var ds = HelpDB.getDatabase().LoadDataSet(query, "DEPARTMENT");
input._Init(ds.Tables[0], "ID", "PARENT_ID", "NAME", new[] { "NAME" },
new[] { "Công ty/phòng ban" }, new[] { TreeListColumnType.TextType },
"", "", "");
}
public static void InitCtrlNhanVien(PLMultiCombobox input, bool? isAdd)
{
string sql = string.Format(@"SELECT *
From DM_NHAN_VIEN
where 1=1");
var query = new QueryBuilder(sql);
if (isAdd == true)
query.addCondition("VISIBLE_BIT='Y'");
query.setAscOrderBy("lower(NAME)");
var ds = HelpDB.getDatabase().LoadDataSet(query, "DM_NHAN_VIEN");
input.DataSource = ds.Tables[0];
input.DisplayField = "NAME";
input.ValueField = "ID";
input._init();
}
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];
}
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()
{
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 DataSet LayThang(string ThangNam)
{
try
{
QueryBuilder query = new QueryBuilder("select l.*, nv.NAME,l.tong_thu_nhap-l.tam_ung con_lai from BANG_LUONG l , DM_NHAN_VIEN nv where l.THANG_NAM = '" + ThangNam + "' and nv.ID = l.NV_ID and 1=1");
query.setAscOrderBy("nv.NAME");
DataSet ds = HelpDB.getDatabase().LoadDataSet(query, "BANG_LUONG");
return ds;
}
catch (Exception ex)
{
PLException.AddException(ex);
}
return null;
}
public void LoadData(params object[] isLoadForm)
{
String[] fields = new String[ChonNgay.Length * 2];
string[] Params = new string[ChonNgay.Length];
int i = 0;
int cn = 0;
#region 5.1.Tạo fields
foreach (DateTime item in ChonNgay)
{
Params[cn] = "P" + cn.ToString();
fields[i++] = "S" + cn.ToString();
fields[i++] = "C" + cn.ToString();
cn++;
}
#endregion
#region 5.2.Tạo bảng tương ứng và lấy dánh sách nhân viên trong bảng chấm công
DanhSachCotDaChot = new List<string>();
DataTable Kq = CreateTable(fields);
if (isLoadForm.Length > 0) {
this.CreateGridViewDetail(fields, new String[] { "", "" }, ChonNgay);
gridControlDetails.DataSource = Kq;
goto _Label;
}
//DataTable Nv = GetNV_ID(PLNhanVien._getSelectedID());
DataTable Nv = GetNhanVien(this.NhanVien._getSelectedID());
#endregion
#region 5.3.Đưa dữ liệu vào bảng
FWDBService fb = HelpDB.getDBService();
QueryBuilder sql = new QueryBuilder(@"SELECT BCC.NV_ID,BCC.NGAY,BCC.SANG,BCC.CHIEU,NV.NAME
FROM BANG_CHAM_CONG_AUTO BCC INNER JOIN DM_NHAN_VIEN NV ON NV.ID = BCC.NV_ID
WHERE 1=1");
sql.addDateFromTo("NGAY", ngayLamViec.FromDate, ngayLamViec.ToDate);
sql.setAscOrderBy("NGAY");
DataSet bangChamCongThang = new DataSet();
fb.LoadDataSet(bangChamCongThang, sql, "BANG_CHAM_CONG_AUTO");
if (bangChamCongThang != null && bangChamCongThang.Tables.Count > 0 && bangChamCongThang.Tables[0].Rows.Count > 0)
{
for (int row = 0; row < Nv.Rows.Count; row++)
{
Application.DoEvents();
#region 5.3.1.Lấy dữ liệu của nhân viên row và sắp tăng theo ngày
FWDBService fbNhanVien = HelpDB.getDBService();
sql = new QueryBuilder(@"SELECT BCC.NV_ID,BCC.NGAY,BCC.SANG,BCC.CHIEU,NV.NAME
FROM BANG_CHAM_CONG_AUTO BCC INNER JOIN DM_NHAN_VIEN NV ON NV.ID = BCC.NV_ID
WHERE 1=1");
sql.addDateFromTo("NGAY", ngayLamViec.FromDate, ngayLamViec.ToDate);
sql.setAscOrderBy("NGAY");
sql.addID("NV_ID", long.Parse(Nv.Rows[row]["NV_ID"].ToString()));
DataSet ds = new DataSet();
fbNhanVien.LoadDataSet(ds, sql, "BANG_CHAM_CONG_AUTO");
#endregion
#region 5.3.2.Đưa dữ liệu lên bảng Kq
if (ds.Tables[0].Rows.Count > 0)
{
int sc = 0;
DataRow dr = Kq.NewRow();
for (int col = 0; col < ChonNgay.Length; col++)
{
dr["NV_ID"] = long.Parse(ds.Tables[0].Rows[0]["NV_ID"].ToString());
dr["NAME"] = ds.Tables[0].Rows[0]["NAME"].ToString();
string sang = "";
string chieu = "";
foreach (DataRow r in ds.Tables[0].Rows)
{
if (NgayBangNgay((DateTime)r["NGAY"], ChonNgay[col]))
{
sang = r["SANG"].ToString();
chieu = r["CHIEU"].ToString();
break;
}
}
dr[sc] = sang; sc++;
dr[sc] = chieu; sc++;
}
Kq.Rows.Add(dr);
}
#endregion
}
}
#endregion
#region 5.4.Tạo lưới và hiển thị dữ liệu lên lưới
this.CreateGridViewDetail(fields, new String[] { "", "" }, ChonNgay);
gridControlDetails.DataSource = Kq;
#endregion
CapNhatThongKe();
if (Kq == null || Kq.Rows.Count == 0)
{
this.barButtonItem_Chot.Enabled = false;
}
ShowFooter();
_Label: ;
}
public override QueryBuilder PLBuildQueryFilter()
{
QueryBuilder filter = new QueryBuilder
(
"SELECT uv.ID as ID,hpv.DTD_ID,uv.HO_TEN,hpv.VONG_PHONG_VAN,hpv.NGAY_GIO_PHONG_VAN, hpv.LAN_MOI_PHONG_VAN,tths.NAME as TTHS_NAME, " +
" hpv.NGAY_GIO_PHONG_VAN as GIO_PHONG_VAN " +
"FROM RESUME uv, HEN_PHONG_VAN hpv,DM_TINH_TRANG_HO_SO tths " +
"WHERE uv.ID=hpv.R_ID AND tths.ID = uv.TTHS_ID AND 1=1"
);
filter.add("uv.HO_TEN", Operator.Like, "%" + PLHeplString.PLHelpFortmatName(Filter_TenUV.Text) + "%", DbType.String);
if (Filter_VongPV.Text != "")
filter.add("hpv.VONG_PHONG_VAN", Operator.Equal, (int)Filter_VongPV.Value, DbType.Int32);
filter.addDateFromTo("hpv.NGAY_GIO_PHONG_VAN", Filter_NgayPVTu.DateTime.Date, Filter_NgayPVDen.DateTime.Date);
filter.addHourFrom(Filter_GioPVTu.TimeEditSub.Time, "hpv.NGAY_GIO_PHONG_VAN");
filter.addHourTo(Filter_GioPVDen.TimeEditSub.Time, "hpv.NGAY_GIO_PHONG_VAN");
filter.addID("uv.TTHS_ID", Filter_TinhTrangHoSo._getSelectedID());
filter.setAscOrderBy("uv.HO_TEN");
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;
}
