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];
}
/// <summary>CHAUTV : Tính tổng số giờ nhân viên làm được trong khoảng thời gian xác định
/// </summary>
public static double GetSumDaysWorked(DateTime from, DateTime to, long employKey)
{
QueryBuilder query = new QueryBuilder(@"SELECT distinct NV_ID,NGAY,SANG,CHIEU
FROM BANG_CHAM_CONG_AUTO
WHERE 1=1");
query.addID("NV_ID", employKey);
query.addDateFromTo("NGAY", from, to);
DataSet ds = HelpDB.getDatabase().LoadDataSet(query, "BANG_CHAM_CONG_AUTO");
double cntV = 0;
double sumTime = 0;
double cntN = 0;
//Duyệt đếm thời gian, vắng, nghỉ
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
if (ds.Tables[0].Rows[i]["SANG"].ToString() == "V")
cntV++;
if (ds.Tables[0].Rows[i]["CHIEU"].ToString() == "V")
cntV++;
if (ds.Tables[0].Rows[i]["SANG"].ToString() == "N")
cntN++;
if (ds.Tables[0].Rows[i]["CHIEU"].ToString() == "N")
cntN++;
if (checkNumber(ds.Tables[0].Rows[i]["SANG"].ToString()))
sumTime += double.Parse(ds.Tables[0].Rows[i]["SANG"].ToString());
if (checkNumber(ds.Tables[0].Rows[i]["CHIEU"].ToString()))
sumTime += double.Parse(ds.Tables[0].Rows[i]["CHIEU"].ToString());
}
//Làm tròn 4 chữ số
return Math.Round(sumTime / 8 - cntV / 2, 4);
}
public DataSet DsChamCongNhanVien(string strThangNam,string Table)
{
string[] M = strThangNam.Split('/');
int mm = HelpNumber.ParseInt32(M[0]);
int yy = HelpNumber.ParseInt32(M[1]);
QueryBuilder query = new QueryBuilder(@"SELECT NV_ID FROM " + Table + " WHERE 1=1" );
query.addDateFromTo("NGAY", HelpDate.GetStartOfMonth(mm, yy), HelpDate.GetEndOfMonth(mm, yy));
query.addGroupBy("NV_ID");
DataSet ds = HelpDB.getDatabase().LoadDataSet(query, Table);
return ds;
}
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 void MoChotBangChamCong(DateTime []dsNgayMoChot,string TableName)
{
//foreach (DateTime item in dsNgayMoChot)
//{
// DOChot info = new DOChot { Ngay = item };
// MoChot(info,TableName);
//}
QueryBuilder query = new QueryBuilder("DELETE FROM " + DAChamCongAutoChot.TABLE_MAP + " WHERE 1=1");
query.addDateFromTo("NGAY", dsNgayMoChot[0], dsNgayMoChot[dsNgayMoChot.Length - 1]);
DatabaseFB db = HelpDB.getDatabase();
DbCommand cmd = db.GetSQLStringCommand(query.generateParamSQL());
cmd.Parameters.AddRange(query.generateDbParam().ToArray());
db.ExecuteNonQuery(cmd);
}
private void btnView_Click(object sender, EventArgs e)
{
var sql = string.Format(
@"select ct.time_start ||' - '|| ct.time_end timeline,ct.kenh_phat, dth.name kenh_phat_name, avg(ct.rate_group) RATE,
list( distinct nsx.name,'; ') nuoc_sx,
list( distinct c.noi_dung,'; ') chuong_trinh
from tns_programs_grid_scr_ct ct
inner join tns_programs_grid_scr t on t.pgs_id=ct.pgs_id
left join tns_chuong_trinh c on c.tct_id=ct.chuong_trinh
left join tns_nuoc_sx nsx on ct.nuoc_sx=nsx.id
left join dm_dai_truyen_hinh dth on dth.id=ct.kenh_phat
where (t.is_delete='N' or t.is_delete is null) and ct.chuong_trinh is not null and 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);
var minStart = TimeSpanHelper.Parse(TuVet.EditValue.ToString()).TotalMinutes;
var minEnd = TimeSpanHelper.Parse(DenVet.EditValue.ToString()).TotalMinutes;
query.add("CT.MINS_START", Operator.GreaterEqual, minStart, DbType.Decimal);
query.add("CT.MINS_END", Operator.LessEqual, minEnd, DbType.Decimal);
query.addGroupBy(@"ct.time_start ||' - '|| ct.time_end, ct.kenh_phat,dth.name");
query.addCondition("1=1");
var ds = HelpDB.getDBService().LoadDataSet(query, KE_HOACH_LPS_CT.TABLE_NAME);
RemoveCols();
if (ds != null && ds.Tables.Count > 0)
{
DataTable dtSource = ds.Tables[0];
DataTable dtKenh = dtSource.DefaultView.ToTable(true, "kenh_phat", "kenh_phat_name");
GenColumn(dtKenh);
DataTable dtRp = CreateReportTable(dtSource, dtKenh);
gridControlDetail.DataSource = dtRp;
}
}
private void InitMRUEditYeuCau()
{
mruEditYeuCau.Text = "";
mruEditYeuCau.Properties.Items.Clear();
QueryBuilder sql = new QueryBuilder(@"SELECT CHU_DE FROM YEU_CAU YC LEFT JOIN YEU_CAU_TRA_LOI YCTL ON YCTL.YEU_CAU_ID = YC.ID WHERE 1=1");
sql.addDateFromTo("YC.NGAY_GUI", ngayLamViec.FromDate, ngayLamViec.ToDate);
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)
{
sql.addCondition(cond.ToString());
}
if (PLTinhtrang._getSelectedID() > 0)
{
sql.addID("TINH_TRANG", PLTinhtrang._getSelectedID());
}
if (PLMucuutien._getSelectedID() > 0)
{
sql.addID("MUC_UU_TIEN", PLMucuutien._getSelectedID());
}
DataSet ds = HelpDB.getDatabase().LoadDataSet(sql);
foreach (DataRow row in ds.Tables[0].Rows)
{
mruEditYeuCau.Properties.Items.Add(row["CHU_DE"]);
}
}
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;
}
/// <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 = null;
query = new QueryBuilder(
"SELECT * " +
"FROM PHIEU_THU_CHI " +
"WHERE 1=1"
);
query.addDateFromTo(PhieuThuChiExtFields.NGAY_PHAT_SINH, this.dateFrom.DateTime, this.dateTo.DateTime);
query.addSoPhieu(PhieuThuChiExtFields.CODE, this.txtCode.Text.Trim());
query.addID(PhieuThuChiExtFields.EMP_ID, this.cmbEmp._getSelectedID());
if (this.chkThu.Checked != this.chkChi.Checked)
{
if(this.chkThu.Checked)
query.addBoolean(PhieuThuChiExtFields.THU_CHI_BIT, true);
else
query.addBoolean(PhieuThuChiExtFields.THU_CHI_BIT, false);
}
query.addCondition("(1=1)");
return query;
}
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 override QueryBuilder PLBuildQueryFilter()
{
string chuoi = @"Select ID,NAME,
(Select Name from dm_nhan_vien Nv where Nv.id=B.NGUOI_QUAN_LY) TEN_NGUOI_QL,
(Select Name from DM_LOAI_DU_AN Da where Da.id=B.LOAI_DU_AN) TEN_DA,
NGAY_BAT_DAU,NGAY_KET_THUC,
MUC_UU_TIEN,TIEN_DO,TINH_TRANG
from DU_AN B where 1=1";
if (LoaiCV._getSelectedID() != -1 && LoaiCV._getSelectedID() != 0)
chuoi = @"Select ID,NAME,
(Select Name from dm_nhan_vien Nv where Nv.id=B.NGUOI_QUAN_LY) TEN_NGUOI_QL,
(Select Name from DM_LOAI_DU_AN Da where Da.id=B.LOAI_DU_AN) TEN_DA,
NGAY_BAT_DAU,NGAY_KET_THUC,
MUC_UU_TIEN,TIEN_DO,TINH_TRANG
from DU_AN B where exists(SELECT * FROM CONG_VIEC_DU_AN cvda,phan_cong_cong_viec pccv where B.ID=cvda.DU_AN_ID and cvda.PCCV_ID=pccv.pccv_id and pccv.lcv_id='" + LoaiCV._getSelectedID() + "') and 1=1";
QueryBuilder query = new QueryBuilder(chuoi);
query.addID("LOAI_DU_AN", LoaiDA._getSelectedID());
query.addID("MUC_UU_TIEN", mucuutien._getSelectedID());
query.addID("TINH_TRANG", tintrang._getSelectedID());
query.addDateFromTo("NGAY_BAT_DAU", Tungay.DateTime, denngay.DateTime);
query.addDateFromTo("NGAY_KET_THUC", KetThucTu.DateTime, KetThucTu.DateTime);
GridDataSet = DABase.getDatabase().LoadDataSet(query,"griddataset");
DataTable Bang_uu_tien = DAYeuCau.Bang_uu_tien_new();
DataTable Bang_tinh_trang = DAYeuCau.Bang_tinh_trang_DA_new();
GridDataSet.Tables.AddRange(new DataTable[] { Bang_uu_tien, Bang_tinh_trang });
GridDataSet.Relations.Add("MUC_UU_TIEN",Bang_uu_tien.Columns["ID"],GridDataSet.Tables[0].Columns["MUC_UU_TIEN"]);
GridDataSet.Tables[0].Columns.Add("TEN_MUC", Type.GetType("System.String"), "Parent(MUC_UU_TIEN).NAME");
GridDataSet.Relations.Add("TEN_TINH_TRANG", Bang_tinh_trang.Columns["ID"], GridDataSet.Tables[0].Columns["TINH_TRANG"]);
GridDataSet.Tables[0].Columns.Add("TEN_TT", Type.GetType("System.String"), "Parent(TEN_TINH_TRANG).NAME");
gridControlMaster.DataSource = GridDataSet.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()
{
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;
}
private void InitMRUEditTenCongViec()
{
mruEditTenCongViec.Text = "";
mruEditTenCongViec.Properties.Items.Clear();
QueryBuilder sql = new QueryBuilder(@"SELECT CONG_VIEC FROM PHAN_CONG_CONG_VIEC where 1=1");
sql.addDateFromTo("NGAY_BAT_DAU", ngayLamViec.FromDate, ngayLamViec.ToDate);
DataSet ds = HelpDB.getDatabase().LoadDataSet(sql);
foreach (DataRow row in ds.Tables[0].Rows)
{
mruEditTenCongViec.Properties.Items.Add(row["CONG_VIEC"]);
}
}
private void Load_Data(DateTime Ngay_lam_viec)
{
string sql = @"Select CN.ID,D.NAME as PHONG_BAN,NV.ID as NV_ID,NV.NAME as TEN_NV,NGAY_LAM_VIEC,GIO_BAT_DAU,GIO_KET_THUC,THOI_GIAN_LAM_VIEC,THOI_GIAN_SANG,THOI_GIAN_CHIEU
from (DM_NHAN_VIEN NV inner join CAPNHAT_NGAYLAMVIEC CN on NV.ID=CN.NV_ID) left join DEPARTMENT D on D.ID=NV.DEPARTMENT_ID where CN.LOAI=1 and 1=1";
QueryBuilder query = new QueryBuilder(sql);
query.addDateFromTo("NGAY_LAM_VIEC", dateNgay_LV.DateTime, dateNgay_LV.DateTime);
ds = DABase.getDatabase().LoadDataSet(query);
ds.Tables[0].Columns.Add("TGDC_1", Type.GetType("System.DateTime"));
ds_updated = ds.Clone();
gridControlSource.DataSource = ds.Tables[0];
}
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;
}
public override QueryBuilder PLBuildQueryFilter()
{
FWWaitingMsg wait = new FWWaitingMsg();
QueryBuilder query = new QueryBuilder(UpdateRow());
try
{
StringBuilder cond = new StringBuilder("");
if (cboNguoi_gui._getSelectedID() != -1) cond.Append(string.Format("NGUOI_GUI_ID = {0}", cboNguoi_gui._getSelectedID()));
long[] arrNguoiNhan = cboNguoi_nhan._SelectedIDs;
if (arrNguoiNhan.Length > 0 && cond.Length > 0) cond.Append(" OR ");
int temp = arrNguoiNhan.Length;
foreach (long id in arrNguoiNhan)
{
cond.Append(string.Format(@"(NGUOI_NHAN_ID LIKE '{0}%')
OR (NGUOI_NHAN_ID LIKE '%,{0},%') OR (NGUOI_NHAN_ID LIKE '%,{0}')", id));
temp--;
if (temp > 0)
{
cond.Append(" OR ");
}
}
if (cond.Length > 0)
{
query.addCondition(cond.ToString());
}
query.addDateFromTo("THOI_GIAN_CAP_NHAT", ngayKT.FromDate, ngayKT.ToDate);
query.addID("MUC_DO_UU_TIEN_ID", cboMuc_uu_tien._getSelectedID());
query.addID("TINH_TRANG_IN_ID", PLImgTinhTrang._getSelectedID());
query.addCondition(" 1=1 ");
DataSet Master_ds = HelpDB.getDatabase().LoadDataSet(query);
gridControlMaster.DataSource = Master_ds.Tables[0];
if (splitContainerControl1.SplitterPosition == 308)
splitContainerControl1.SplitterPosition = 257;
}
catch (Exception ex)
{
PLException.AddException(ex);
}
wait.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()
{
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()
{
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;
}
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 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()
{
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());
gridControlMaster.DataSource = null;
StringBuilder cond = new StringBuilder("");
if (nguoiGui._getSelectedID() != -1) cond.Append(string.Format("NGUOI_GOP_Y = {0}", nguoiGui._getSelectedID()));
long[] arrNguoiNhan = NguoiNhan._SelectedIDs;
if (arrNguoiNhan.Length > 0 && cond.Length > 0) cond.Append(" OR ");
int temp = arrNguoiNhan.Length;
foreach (long id in arrNguoiNhan)
{
cond.Append(string.Format(@"(NGUOI_NHAN_GOP_Y LIKE '{0},%')
OR (NGUOI_NHAN_GOP_Y LIKE '%,{0},%') OR (NGUOI_NHAN_GOP_Y LIKE '%,{0}')", id)); ;
temp--;
if (temp > 0)
{
cond.Append(" OR ");
}
}
if (cond.Length > 0)
{
filter.addCondition(cond.ToString());
}
filter.addDateFromTo(HomThuGopYFiedls.NGAY, ngayKT.FromDate, ngayKT.ToDate);
filter.addCondition(" 1=1 ");
DsData = HelpDB.getDatabase().LoadDataSet(filter);
if (DsData != null && DsData.Tables[0].Rows.Count > 0)
{
DsData.Tables[0].Columns.AddRange(new DataColumn[]{
new DataColumn("NOI_DUNG_TEXT",typeof(String))
,new DataColumn("TEN_NGUOI_NHAN",typeof(String))});
DataSet dsCopy = DsData.Copy();
if (chkNguoiGopY.Checked)
{
foreach (DataRow row in dsCopy.Tables[0].Rows)
{
row["NOI_DUNG_TEXT"] = HelpByte.BytesToUTF8String((byte[])row[HomThuGopYFiedls.NOI_DUNG]);
if (HelpNumber.ParseInt64(row["NGUOI_GOP_Y"]) != FrameworkParams.currentUser.employee_id)
row["NGUOI_GOP_Y"] =
string.Compare(row["IS_HIEN"].ToString(), "Y") == 0 ? row["NGUOI_GOP_Y"] : -1;
row["TEN_NGUOI_NHAN"] = GetNameOfRecipient(row["NGUOI_NHAN_GOP_Y"].ToString());
}
}
else {
foreach (DataRow row in dsCopy.Tables[0].Rows)
{
row["NOI_DUNG_TEXT"] = HelpByte.BytesToUTF8String((byte[])row[HomThuGopYFiedls.NOI_DUNG]);
row["NGUOI_GOP_Y"] =
string.Compare(row["IS_HIEN"].ToString(), "Y") == 0 ? row["NGUOI_GOP_Y"] : -1;
row["TEN_NGUOI_NHAN"] = GetNameOfRecipient(row["NGUOI_NHAN_GOP_Y"].ToString());
}
}
gridControlMaster.DataSource = dsCopy.Tables[0];
}
msg.Finish();
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()
{
FWWaitingMsg msg = new FWWaitingMsg();
QueryBuilder filter = new QueryBuilder(UpdateRow());
filter.addID("NV_ID", NhanVien._getSelectedID());
filter.addDateFromTo("NGAY_LAM_VIEC", ngayLamViec.FromDate, ngayLamViec.ToDate);
filter.addDuyet(PLConst.FIELD_DUYET, DuyetSelect.layTrangThai());
filter.addIn("LOAI_XAC_NHAN", this.getLoaiXacNhan());
msg.Finish();
return filter;
}
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 static void addFilterFromTo(QueryBuilder filter, string opLeft, PLDateSelection Date)
{
if (Date.SelectedType == SelectionTypes.None) return;
filter.addDateFromTo(opLeft, Date.FromDate, Date.ToDate);
}
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;
}
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()
{
FWWaitingMsg wait = new FWWaitingMsg();
if (dtRecipient == null) dtRecipient = HelpDB.getDatabase().LoadDataSet("SELECT ID,NAME FROM DM_NHAN_VIEN").Tables[0];
DataSet ds;
QueryBuilder filter = new QueryBuilder(UpdateRow());
StringBuilder cond = new StringBuilder("");
if (NhanVien._getSelectedID() != -1) cond.Append(string.Format("NGUOI_GUI = {0}", NhanVien._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(@"(NGUOI_NHAN LIKE '{0}%')
OR (NGUOI_NHAN LIKE '%,{0},%') OR (NGUOI_NHAN LIKE '%,{0}')", id));
temp--;
if (temp > 0)
{
cond.Append(" OR ");
}
}
if (cond.Length > 0)
{
filter.addCondition(cond.ToString());
}
filter.addID("LOAI_VAN_DE", loaiVanDe._getSelectedID());
filter.addID("TINH_TRANG", Tinh_Trang._getSelectedID());
filter.addDateFromTo("NGAY_GUI", ngayLamViec.FromDate, ngayLamViec.ToDate);
ds = HelpDB.getDatabase().LoadDataSet(filter, "BUG");
DataTable Bang_tinh_trang = DAYeuCau.Bang_tinh_trangBug();
ds.Tables.AddRange(new DataTable[] { Bang_tinh_trang.Copy() });
ds.Relations.Add("TEN_TINH_TRANG", ds.Tables["DMC_TINH_TRANG"].Columns["ID"], ds.Tables["BUG"].Columns["TINH_TRANG"]);
ds.Tables[0].Columns.Add("TEN_TT", Type.GetType("System.String"), "Parent(TEN_TINH_TRANG).NAME");
foreach (DataRow row in ds.Tables[0].Rows)
row["TEN_NGUOI_NHAN"] = GetNameOfRecipient(row["NGUOI_NHAN"].ToString());
gridControlMaster.DataSource = ds.Tables[0];
Web_Mo_Ta.DocumentText = null;
wait.Finish();
return null;
}
