public void addNumFromTo(QueryBuilder filter, string opLeft, CalcEdit FromCalc, CalcEdit ToCalc)
{
if (FromCalc.EditValue != null)
{
filter.add(opLeft, Operator.GreaterEqual, FromCalc.Value, DbType.Decimal);
}
if (ToCalc.EditValue != null)
{
filter.add(opLeft, Operator.LessEqual, ToCalc.Value, DbType.Decimal);
}
}
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;
}
}
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
(
"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;
}
public override DataTable PLLoadDataDetailPart(long MasterID)
{
try
{
QueryBuilder query = new QueryBuilder("select * from RESUME where 1=1");
query.add("ID", Operator.Equal, MasterID, DbType.Int32);
DataTable dt = DABase.getDatabase().LoadDataSet(query, "DETAIL").Tables[0];
this.Web_TrinhDoChuyenMon.DocumentText = HelpByte.BytesToUTF8String((Byte[])dt.Rows[0]["TRINH_DO_CHUYEN_MON"]);
this.Web_TrinhDoNgoaiNgu.DocumentText = HelpByte.BytesToUTF8String((Byte[])dt.Rows[0]["TRINH_DO_NGOAI_NGU"]);
this.Web_ThongTinKhac.DocumentText = HelpByte.BytesToUTF8String((Byte[])dt.Rows[0]["THONG_TIN_KHAC"]);
this.Web_QuaTrinhCongTac.DocumentText = HelpByte.BytesToUTF8String((Byte[])dt.Rows[0]["QUA_TRINH_CONG_TAC"]);
this.Web_QuaTrinhDaoTao.DocumentText = HelpByte.BytesToUTF8String((Byte[])dt.Rows[0]["QUA_TRINH_DAO_TAO"]);
return dt;
}
catch (Exception)
{
return null;
}
}
public static DataTable GetYeuCauOfYCTraLoi(long ID)
{
DataSet ds;
QueryBuilder query = new QueryBuilder(@"SELECT YC.*,CASE YC.TINH_TRANG
WHEN 1 THEN 'Mới tạo'WHEN 2 THEN 'Đang xử lý' WHEN 3 THEN 'Không hỗ trợ' ELSE 'Hoàn tất' END TEN_TINH_TRANG
FROM YEU_CAU YC INNER JOIN YEU_CAU_TRA_LOI YCTL ON YC.ID = YCTL.YEU_CAU_ID WHERE 1=1");
query.add("YCTL.ID", Operator.Equal, ID, DbType.Int64);
ds = HelpDB.getDatabase().LoadDataSet(query);
return ds.Tables[0];
}
public static void InitCtrlDuyet(PLDMGrid Input, bool ReadOnly, bool? IsAdd, string FeatureName)
{
QueryBuilder query = new QueryBuilder(@"
SELECT e.ID,e.NAME,e.DEPARTMENT_ID FROM USER_FEATURE_REL rel, USER_CAT u, DM_NHAN_VIEN e ,FEATURE_CAT fcat
WHERE e.ID = u.EMPLOYEE_ID AND rel.USERID=u.USERID AND fcat.ID=rel.FEATUREID AND 1=1");
query.add("fcat.NAME", Operator.Equal, FeatureName, DbType.String);
query.addBoolean("rel.ISREAD_BIT", true);
if (IsAdd != null)
{
query.addBoolean("e.VISIBLE_BIT", true);
}
Input._init((ReadOnly == true ? GroupElementType.ONLY_CHOICE : GroupElementType.CHOICE_N_ADD), HelpDB.getDatabase().LoadDataSet(query, "DM_NHAN_VIEN").Tables[0], "ID", "NAME",
new string[] { "ID" }, new string[] { "ID" }, DMNhanVienX.I.CreatePLDMGroupElementNhan_vien, DMNhanVienX.I.GetRuleDM_NHAN_VIEN, null, null, null, null);
if (ReadOnly)
{
try
{
((System.Windows.Forms.ToolStrip)Input.Controls[0].Controls[0].Controls[1]).Items[4].Visible = true;// Ẩn nút sửa
((System.Windows.Forms.ToolStrip)Input.Controls[0].Controls[0].Controls[1]).Items[5].Visible = true;// Ẩn nút Xóa
}
catch (Exception ex)
{
PLException.AddException(ex);
}
}
}
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;
}
public DataSet GetData()
{
QueryBuilder filter = null;
string sql = "select CTCCV_ID,DA_ID,CV_ID,NV_ID,MO_TA,BAT_DAU,KET_THUC,THOI_GIAN_THUC_HIEN,NGAY_LAM_VIEC,NGAY_CAP_NHAT,case when DUYET = 0 then 'Duyệt' else( case when DUYET = 1 then 'Chưa Duyệt' else 'Chờ Duyệt' end)end duyeta from KE_HOACH_LAM_VIEC where 1=1";
filter = new QueryBuilder(sql);
filter.add("NGAY_LAM_VIEC",Operator.Equal,NgayNhap.DateTime,DbType.DateTime);
filter.addID("NV_ID",NhanVien._getSelectedID());
filter.addID("DUYET",comboBoxEdit1.SelectedIndex);
filter.setAscOrderBy("NGAY_CAP_NHAT");
DataSet ds = HelpDB.getDatabase().LoadDataSet(filter, "ABC");
return HelpDB.getDatabase().LoadDataSet(filter, "ABC");
}
//Chuyen thoi gian lam viec cua nhan vien vao db
public bool UpdateThoiGianLamViec(long NVID, DateTime NgayLamViec, double ThoiGian)
{
DataSet DatailDataSet = new DataSet();
QueryBuilder query = new QueryBuilder( "select * from BANG_CHAM_CONG where 1=1");
query.addID("NV_ID", NVID);
query.add(new Condition("NGAY", Operator.Equal,NgayLamViec.Date, DbType.Date));
HelpDB.getDatabase().LoadDataSet(query, "BANG_CHAM_CONG", DatailDataSet);
if(DatailDataSet.Tables[0].Rows.Count>0 && ThoiGian!=-1)
{
DatailDataSet.Tables[0].Rows[0]["THOI_GIAN_LAM_VIEC"] = ThoiGian;
}
DatabaseFB db = DABase.getDatabase();
db.OpenConnection();
DbTransaction dbTrans = db.BeginTransaction(db.OpenConnection());
try
{
if (db.UpdateDataSet(DatailDataSet, dbTrans) == false) return false;
}
catch
{
db.RollbackTransaction(dbTrans);
}
db.CommitTransaction(dbTrans);
return true;
}
public override QueryBuilder PLBuildQueryFilter()
{
var query = new QueryBuilder(UpdateRow());
query.addLike("LOWER(pgs." + TNS_SALE_REV_SCR.NAME + ")", TenNguon.Text.ToLower());
query.addLike("LOWER(" + TNS_SALE_REV_SCR.FILE_NAME + ")", TenFile.Text.ToLower());
AppCtrl.addID(query, TNS_SALE_CPP_SCR.TARGET, Target);
AppCtrl.addID(query, TNS_SALE_CPP_SCR.THI_TRUONG, ThiTruong);
AppCtrl.addID(query, TNS_SALE_REV_SCR.NGUOI_TAO, NguoiLap);
AppCtrl.addFilterFromTo(query, TNS_SALE_REV_SCR.NGAY_TAO, NgayLap);
if (TuNam.Text != "" && TuThang.Text != "")
{
query.add(TNS_SALE_REV_SCR.TU_NGAY, Operator.GreaterEqual,
new DateTime((int) TuNam.Value, (int) TuThang.Value, 1, 0, 0, 0), DbType.DateTime);
}
if (DenThang.Text != "" && DenNam.Text != "")
{
query.add(TNS_SALE_REV_SCR.DEN_NGAY, Operator.LessEqual,
new DateTime((int) DenNam.Value, (int) DenThang.Value, 1, 23, 59, 59), DbType.DateTime);
}
AppCtrl.addID(query, TNS_SALE_CPP_SCR.PHONG_BAN, PhongBan);
query.addCondition("1=1");
query.setDescOrderBy(this.DESCSortClause);
return query;
}
public static DataTable Get_Bang_yeu_cau(long ID)
{
DataSet ds;
QueryBuilder query = new QueryBuilder(@"SELECT YC.*,CASE YC.TINH_TRANG
WHEN 1 THEN 'Mới tạo'WHEN 2 THEN 'Đang xử lý' WHEN 3 THEN 'Không hỗ trợ' ELSE 'Hoàn tất' END TEN_TINH_TRANG
,(SELECT COUNT(*) FROM YEU_CAU_TRA_LOI YCTL WHERE YCTL.YEU_CAU_ID = YC.ID) SO_PHAN_HOI
FROM YEU_CAU YC WHERE 1=1");
query.add("ID", Operator.Equal, ID, DbType.Int64);
ds = HelpDB.getDatabase().LoadDataSet(query);
return ds.Tables[0];
}
public static DataTable Get_Bang_yc_tra_loi(long ID)
{
DataSet ds;
QueryBuilder query = new QueryBuilder(@"SELECT NGUOI_GUI_ID, NGUOI_NHAN_ID, NOI_DUNG, NGAY_GUI
FROM YEU_CAU_TRA_LOI WHERE 1=1");
query.add("ID", Operator.Equal, ID, DbType.Int64);
ds = HelpDB.getDatabase().LoadDataSet(query);
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 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;
}
//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;
}
public void _setSelectedID(long id)
{
string command = "SELECT " + _idField + "," + _memberField + " FROM " + _tableName + " WHERE 1=1";
QueryBuilder query = new QueryBuilder(command);
query.add(_idField, Operator.Equal, id, DbType.Int64);
ds = DABase.getDatabase().LoadDataSet(query, "DANHMUC");
comboBoxEdit1.Text = ds.Tables[0].Rows[0][_memberField].ToString();
isCreateButton = false;
}
private bool ValidateOnDB()
{
bool valid = true;
try
{
#region Kiểm tra có thay đổi thông tin phát sóng
//if (IsAdd == false)
//{
bool isChange = ((_bkNgayBatDau != null && _bkNgayBatDau.Value != NgayBatDau.DateTime.Date) ||
(_bkGioPhat != null && (_bkGioPhat.Value.Hour != GioPhat.Time.Hour
|| _bkGioPhat.Value.Minute != GioPhat.Time.Minute))) ||
_bkSoTapNgay != SoTapNgay.Value ||
_bkThuTrongTuan != AppCtrl.GetMultiValues(ThuTrongTuan) ||
_bktongtapphat != TongTapPhat.Value;
if (isChange)
{
if (HelpMsgBox.ShowConfirmMessage("Thông tin phát sóng có sự thay đổi. Bạn có muốn cập nhật lại chi tiết lịch phát sóng và tiếp tục lưu?") == DialogResult.Yes)
{
UpdateLPS_CT();
}
else return false;
}
//}
#endregion
#region Kiểm tra có phát đồng thời trên kênh khác
string sql = string.Format(@"select list( dth.name,', ' ) KENH_PHAT
from ql_lich_phat_song lps
left join dm_dai_truyen_hinh dth on dth.id=lps.kenh_phat
where 1=1");
var qr = new QueryBuilder(sql);
if (_doLichPhatSong.CT_ID != -1)
{
qr.addID("LPS." + QL_LICH_PHAT_SONG.CT_ID, _doLichPhatSong.CT_ID);
}
else
{
qr.add("lower(" + QL_LICH_PHAT_SONG.TEN_CT + ")", Operator.Equal, NoiDung.Text.ToLower().Trim(), DbType.String);
qr.add("lower(" + QL_LICH_PHAT_SONG.NUOC_SAN_XUAT + ")", Operator.Equal, AppCtrl.GetMultiValues(NuocSX), DbType.String);
qr.add(QL_LICH_PHAT_SONG.NAM_SAN_XUAT, Operator.Equal, NamSX.Value, DbType.Decimal);
qr.addID(QL_LICH_PHAT_SONG.TIET_MUC, _doLichPhatSong.TIET_MUC);
}
qr.add("LPS." + QL_LICH_PHAT_SONG.KENH_PHAT, Operator.NotEqual, KenhPhat._getSelectedID(), DbType.Int64);
if (_doLichPhatSong.NGAY_BAT_DAU != null)
qr.add(QL_LICH_PHAT_SONG.NGAY_BAT_DAU, Operator.LessEqual, _doLichPhatSong.NGAY_BAT_DAU.Value.Date, DbType.DateTime);
if (_doLichPhatSong.NGAY_PHAT_CUOI != null)
qr.add(QL_LICH_PHAT_SONG.NGAY_PHAT_CUOI, Operator.GreaterEqual, _doLichPhatSong.NGAY_PHAT_CUOI.Value.Date, DbType.DateTime);
if (_doLichPhatSong.LPS_ID != -1)
qr.add(QL_LICH_PHAT_SONG.LPS_ID, Operator.NotEqual, _doLichPhatSong.LPS_ID, DbType.Int64);
DataSet ds = HelpDB.getDBService().LoadDataSet(qr);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
string kenh = ds.Tables[0].Rows[0][0].ToString();
if (kenh != "")
{
if (HelpMsgBox.ShowConfirmMessage("Chương trình này đang được phát trên kênh " + kenh + ". Bạn có chắc là tiếp tục lưu?") == DialogResult.No)
valid = false;
}
}
#endregion
}
catch (Exception ex)
{
PLException.AddException(ex);
}
return valid;
}
public static DataTable LoadItems(string tableName, string catalogName)
{
//DatabaseFB db = DABase.getDatabase();
//DbCommand command = db.GetSQLStringCommand("SELECT NOI_DUNG_DM FROM " + tableName +
// " WHERE TEN_DANH_MUC = '"+ catalogName+ "'");
//IDataReader reader = db.ExecuteReader(command);
//DataTable tempTable = new DataTable("TEMPTABLE");
//tempTable.Columns.Add("NOIDUNG");
//while (reader.Read())
//{
// DataRow dr = tempTable.NewRow();
// dr[0] = reader[0];
// tempTable.Rows.Add(dr);
//}
//reader.Close();
//return tempTable;
QueryBuilder query = new QueryBuilder("SELECT NOI_DUNG_DM FROM " + tableName + " WHERE 1=1");
query.add("TEN_DANH_MUC", Operator.Equal, catalogName, DbType.String);
DataSet ds = DABase.getDatabase().LoadDataSet(query, "TEMPTABLE");
return ds.Tables[0];
}
private void btnSave_Click(object sender, EventArgs e)
{
try
{
string Month = this.plMonth._timeEdit.Text;
if (strThangNam.ToString().Contains("," + Month + ","))
{
HelpMsgBox.ShowNotificationMessage(string.Format("Bảng lương của tháng \"{0}\" đã được tạo, vui lòng chọn tháng khác!", Month));
return;
}
Application.DoEvents();
FrameworkParams.wait = new FWWaitingMsg();
///1.Lấy danh sách nhân viên trong bảng chấm công tự động
DataSet dsEmployee = DABangLuong.Instance.GetEmployeeChamCongAuto(Month);
List<string> l = new List<string>();
foreach (DataRow row in dsEmployee.Tables[0].Rows)
{
///2.Lấy thông tin tạm ứng của nhân viên trong bảng tạm ứng của tháng cần tính lương
DOBangLuong phieu = DABangLuong.Instance.LoadAll(HelpNumber.ParseInt64(row["NV_ID"]), Month);
//phieu.TAM_UNG = DABangLuong.Instance.GetSoTienTamUng(HelpNumber.ParseInt64(row["NV_ID"]), Month); //CHAUTV : Chuyển qua nút Tính lương
phieu.NV_ID = HelpNumber.ParseInt64(row["NV_ID"]);
l.Add(phieu.NV_ID.ToString());
phieu.THANG_NAM = Month;
phieu.IS_CHOT = "N";
///3.Tạo dữ liệu trống cho bảng lương
DABangLuong.Instance.Update(phieu);
///4.Cập nhật đã chuyển qua bảng lương cho dữ liệu tạm ứng (Chuyển qua nút Tính lương)
//DAPhieuTamUng.I.UpdatePhieuDuyet(phieu.NV_ID, phieu.THANG_NAM);
}
//Add các nhân viên có tạm ứng được duyệt mà không có trong bảng chấm công
string notIn = "";
if (l.Count > 0) {
for (int i = 0; i < l.Count-1; i++)
{
notIn += l[i] + ",";
}
notIn += l[l.Count - 1];
}
//BỔ SUNG
if (notIn == "") notIn = "-1";
QueryBuilder query = new QueryBuilder("SELECT NGUOI_DE_NGHI_ID FROM PHIEU_TAM_UNG WHERE 1=1");
query.addCondition("(NGUOI_DE_NGHI_ID not in (" + notIn + "))");
query.addCondition("DUYET='2'");
query.add("THANG_TAM_UNG", Operator.Equal, Month, DbType.String);
query.addGroupBy("NGUOI_DE_NGHI_ID");
DataSet ds = HelpDB.getDatabase().LoadDataSet(query);
foreach (DataRow item in ds.Tables[0].Rows)
{
DOBangLuong phieu = DABangLuong.Instance.LoadAll(HelpNumber.ParseInt64(item["NGUOI_DE_NGHI_ID"]), Month);
phieu.NV_ID = HelpNumber.ParseInt64(item["NGUOI_DE_NGHI_ID"]);
phieu.THANG_NAM = Month;
phieu.IS_CHOT = "N";
DABangLuong.Instance.Update(phieu);
}
PLGUIUtil.ClosePhieu(this, true);
}
catch (Exception ex)
{
HelpSysLog.AddException(ex, "Tạo/Cập nhật bảng lương.");
ErrorMsg.ErrorSave("Lưu không thành công.");
}
finally {
if (FrameworkParams.wait != null) FrameworkParams.wait.Finish();
}
}
private void btnView_Click(object sender, EventArgs e)
{
var sql =
string.Format(
@"select k.kenh_phat, kh.gio_phat_song,kh.thu_trong_tuan,ct.class,ct.category,ct.nuoc_san_xuat,ct.period,ct.noi_dung,ct.tong_so_run market_run, ct.run_thu did_run,kh.ghi_chu,null stock,
(select list_month from get_months(kh.ngay_bat_dau,kh.ngay_phat_cuoi)) MONTHS,ct.ban_quyen_thuoc,kh.tong_tap,kh.so_tap_ngay,
iif(kh.thieu_thua=0,'Đủ ', iif(kh.thieu_thua<0, 'Thiếu '||abs(kh.thieu_thua) ||'/','Thừa '||kh.thieu_thua ||'/')) ||kh.so_tap_can THIEU_THUA
from ke_hoach_lps_ct kh
inner join ke_hoach_lps k on kh.khlps_id=k.khlps_id
left join chuong_trinh ct on ct.ct_id=kh.ct_id
where (k.is_delete='N' or k.is_delete is null) and 1=1");
var query = new QueryBuilder(sql);
AppCtrl.addID(query, "k.khlps_id", fileNguon);
AppCtrl.addID(query, "k.kenh_phat", KenhPhat);
if (ThoiGian.SelectedType!=SelectionTypes.None)
{
var df = ThoiGian.FromDate;
var dFom = new DateTime(df.Year, df.Month, df.Day, 0, 0, 0);
query.add("kh.ngay_bat_dau", Operator.GreaterEqual, dFom, DbType.DateTime);
var dt = ThoiGian.ToDate;
var dTo = new DateTime(dt.Year, dt.Month, dt.Day, 23, 59, 59);
query.add("kh.ngay_phat_cuoi", Operator.LessEqual, dTo, DbType.DateTime);
}
var ts = TuVet.Time;
var tStart = new DateTime(1900, 1, 1, ts.Hour, ts.Minute, 0);
query.add("kh.GIO_PHAT_SONG", Operator.GreaterEqual, tStart, DbType.DateTime);
var te = DenVet.Time;
var tEnd = new DateTime(1900, 1, 1, te.Hour, te.Minute, 59);
query.add("kh.gio_phat_song", Operator.LessEqual, tEnd, DbType.DateTime);
query.setAscOrderBy("kh.gio_phat_song");
var ds = HelpDB.getDBService().LoadDataSet(query, KE_HOACH_LPS_CT.TABLE_NAME);
if (ds != null && ds.Tables.Count > 0)
{
_dataTable = ds.Tables[0];
_dtGioPhat = _dataTable.DefaultView.ToTable(true, KE_HOACH_LPS_CT.GIO_PHAT_SONG);
gridControlDetail.DataSource = ds.Tables[0];
}
}
