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);
}
public static void addID(QueryBuilder filter, string opLeft, PLMultiChoiceGrid input)
{
if (!input._IsNotCheckAnyOrCheckedAll)
{
filter.addID(opLeft, input._GetSelectedIds());
}
}
/// <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 static void InitChonChuyenMuc(PLCombobox PLChuyenMuc, long ID_Dien_Dan)
{
QueryBuilder query = null;
query = new QueryBuilder("SELECT * FROM CHUYEN_MUC where 1=1");
query.addCondition("VISIBLE_BIT='Y'");
query.addID("ID_NHOM_DIEN_DAN", ID_Dien_Dan);
DataSet ds = DienDanPermission.I._LoadDataSetWithResPermission(query, "ID");
PLChuyenMuc._init(ds.Tables[0], "NAME", "ID");
}
public override QueryBuilder PLBuildQueryFilter()
{
QueryBuilder query = null;
query = new QueryBuilder(
"SELECT * " +
"FROM TEST_PRODUCT_GROUP " +
"WHERE 1=1"
);
query.addID("NOOP", -1);
return query;
}
/// <summary>
/// Tạo DataTable(HH_ID, SO_LUONG, TRONG_LUONG) lưu trữ hàng hóa, kèm số lượng, trọng lượng
/// Thuộc 1 phiếu bất kì có loại phiếu và ID
/// </summary>
/// <param name="type">Loại phiếu</param>
/// <param name="id">ID</param>
/// <returns></returns>
public static DataTable CreateSoTien(PhieuType type, long id)
{
try
{
string sql = "select Tong_tien as \"SO_TIEN\" from "
+ type.GetTableName() + " where 1=1";
QueryBuilder filter = new QueryBuilder(sql);
filter.addID(type.GetIDField(), id);
DataSet ds = DABase.getDatabase().LoadDataSet(filter);
return ds.Tables[0];
}
catch (Exception ex)
{
PLException.AddException(ex);
return null;
}
}
private void frmHopDong_Load(object sender, EventArgs e)
{
QueryBuilder query = new QueryBuilder("delete from LOAI_DANH_BA where 1=1");
query.addID("ID", 10110);
DBService db = new DABaseFb().SQLStmt;
db.Begin();
try
{
db.DbCommand.Parameters.AddRange(query.generateDbParam().ToArray());
db.ExecuteCommand(query.generateParamSQL());
db.Commit();
}
catch (Exception ex)
{
db.RollBack();
}
}
public static AddressList GetAddressList(long[] Keys)
{
QueryBuilder query = new QueryBuilder(@"SELECT e.ID, e.NAME, cat.USERID as ID, cat.USERNAME, e.EMAIL FROM USER_CAT cat left join DM_NHAN_VIEN e on e.ID=cat.EMPLOYEE_ID WHERE 1=1");
query.addBoolean("e.VISIBLE_BIT", true);
query.addCondition("(EMAIL<>'')");
if (Keys.Length > 0)
query.addID("e.ID", Keys);
DataSet dsTo = HelpDB.getDatabase().LoadDataSet(query, "CAT");
AddressList to = new AddressList();
foreach (DataRow row in dsTo.Tables[0].Rows)
{
if (!to.ToAddressListString().Contains(row["EMAIL"].ToString()))
to.Add(new MailboxAddress(row["NAME"].ToString(), row["EMAIL"].ToString()));
}
return to;
}
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();
}
/// <summary>
/// Tạo DataTable(SO_TIEN) lưu trữ số tiền,
/// </summary>
/// <param name="LObj">Danh sách phiếu liên quan (Loại phiếu, ID)</param>
/// <param name="Des">Loại phiếu đích</param>
/// <param name="DesID">ID phiếu đích</param>
/// <returns>DataTable(SO_TIEN)</returns>
public static DataTable CreateSoTien(List<object[]> LObj,long SrcID, long DesID)
{
DataTable dt_rough = new DataTable();
DataColumn[] dc_arr = new DataColumn[] { new DataColumn("SO_TIEN") };
dt_rough.Columns.AddRange(dc_arr);
dt_rough.Columns["SO_TIEN"].DataType = Type.GetType("System.Decimal");
try
{
foreach (object[] obj_arr in LObj)
{
PhieuType PLQ_type = (PhieuType)obj_arr[0];
long PLQ_id = (long)obj_arr[1];
if (DesID == -1 || (DesID != -1 && DesID != PLQ_id))
{
QueryBuilder filter = new QueryBuilder("select " + PLQ_type.GetSOLGField() +
" as \"SO_TIEN\" from " + PLQ_type.GetTableName()
+ "_CT" + " where 1=1");
filter.addID(PLQ_type.GetIDField(), PLQ_id);
//filter.addID("", SrcID);
DataSet ds = DABase.getDatabase().LoadDataSet(filter);
DataRow[] dr_arr = ds.Tables[0].Select();
foreach (DataRow dr in dr_arr)
dt_rough.ImportRow(dr);
}
}
DataTable dt_last = dt_rough.Clone();
if (dt_rough.Rows.Count > 0)
{
decimal tong_so_tien = HelpNumber.ParseDecimal(dt_rough.Compute("Sum(SO_TIEN)", ""));
DataRow row = dt_last.NewRow();
row["SO_TIEN"] = tong_so_tien;
dt_last.Rows.Add(row);
}
return dt_last;
}
catch (Exception ex)
{
PLException.AddException(ex);
return null;
}
}
public static DataSet dsGetQuyen(long keyFeature)
{
QueryBuilder query = new QueryBuilder(@"
SELECT e.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.addID("fcat.ID", keyFeature);
query.addBoolean("rel.ISREAD_BIT", true);
return HelpDB.getDatabase().LoadDataSet(query);
}
public bool _LoadDataSetWithResGroupPermission(DataSet ds, string tableName, string KeyFieldRes, long ResGroupID)
{
if (ds == null)
{
return false;
}
try
{
QueryBuilder query = new QueryBuilder("SELECT * FROM " + tableName + "where 1=1");
if (isPermission)
{
if (_PerResGroup_READ_Ids != null && _PerResGroup_READ_Ids.Length > 0)
{
if (ResGroupID != -1)
{
bool isPerMis = false;
foreach (long id in _PerResGroup_READ_Ids)
{
if (id == ResGroupID)
{
isPerMis = true;
break;
}
}
if (isPerMis)
query.addID(KeyFieldRes, ResGroupID);
else query.addCondition("1=-1");
}
else
{
query.addID(KeyFieldRes, _PerResGroup_READ_Ids);
}
}
else
{
query.addCondition("1=-1");
}
}
else
{
if (ResGroupID != -1)
query.addID(KeyFieldRes, ResGroupID);
}
return DABase.getDatabase().LoadDataSet(query, tableName, ds);
}
catch (Exception exception)
{
PLException.AddException(exception);
return false;
}
}
public DataSet _LoadDataSetWithResGroupPermission(QueryBuilder query, string tableName, string KeyFieldResGroup, long ResGroupID)
{
if (isPermission)
{
if (_PerResGroup_READ_Ids != null && _PerResGroup_READ_Ids.Length > 0)
{
if (ResGroupID != -1)
{
bool IsPerMit = false;
foreach (long ID in _PerResGroup_READ_Ids)
{
if (ID == ResGroupID)
{
IsPerMit = true;
break;
}
}
if (IsPerMit)
query.addID(KeyFieldResGroup, ResGroupID);
else query.addCondition("-1=1");
}
else
{
query.addID(KeyFieldResGroup, _PerResGroup_READ_Ids);
}
}
else
{
query.addCondition("-1=1");
}
}
else
{
query.addID(KeyFieldResGroup, ResGroupID);
}
return DABase.getDatabase().LoadDataSet(query, (tableName == null || tableName == "" ? "NO_NAME" : tableName));
}
/// <summary>Sự kiện khi chọn "Thêm".
/// </summary>
protected void barButtonItemAdd_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
try
{
long[] ids = that.ShowAddForm();
if (ids != null)
{
string query = that.UpdateRow();
if (query != null && query != "")
{
DataTable table = ((DataTable)(that.gridControlMaster.DataSource)).DataSet.Tables[0];
for (int i = 0; i < ids.Length; i++)
{
QueryBuilder filter = new QueryBuilder(query);
filter.addID(that.IDField, ids[i]);
DataSet ds = DABase.getDatabase().LoadReadOnlyDataSet(filter);
if (ds.Tables[0].Rows.Count == 1)
{
DataRow newRow = table.NewRow();
newRow.ItemArray = ds.Tables[0].Rows[0].ItemArray;
table.Rows.Add(newRow);
}
else
{
}
}
}
}
else
{
//PLRefresh();
}
}
catch (Exception ex)
{
PLException.AddException(ex);
}
}
public bool UpdateRow()
{
try
{
string query = that.UpdateRow();
if (query != null && query != "" && that.gridViewMaster.FocusedRowHandle >= 0)
{
DataRow row = that.gridViewMaster.GetDataRow(that.gridViewMaster.FocusedRowHandle);
long id = HelpNumber.ParseInt64(row[that.IDField].ToString());
QueryBuilder filter = new QueryBuilder(query);
filter.addID(that.IDField, id);
DataSet ds = DABase.getDatabase().LoadReadOnlyDataSet(filter);
if (ds.Tables[0].Rows.Count == 1)
{
row.ItemArray = ds.Tables[0].Rows[0].ItemArray;
PLDaChonMasterRecord(true);
//that.gridControlDetail.DataSource = that.PLLoadDataDetailPart(HelpNumber.ParseInt64(row[that.IDField].ToString()));
that.HookFocusRow();
}
else
{
that.gridViewMaster.DeleteRow(that.gridViewMaster.FocusedRowHandle);
}
return true;
}
else
return false;
}
catch
{
return false;
}
}
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;
}
private void InitData(List<long> ids)
{
#region 1.Lấy thông tin bên bảng RESUME (Thông tin ứng viên)
QueryBuilder query = new QueryBuilder(@"
SELECT MA_HO_SO as MA_NV,
HO_TEN as NAME,
NGAY_SINH,DIA_CHI,EMAIL,DIEN_THOAI
FROM RESUME
WHERE 1=1");
query.addID("ID", ids.ToArray());
DataSet ds = HelpDB.getDatabase().LoadDataSet(query, "DM_NHAN_VIEN");
ds.Tables["DM_NHAN_VIEN"].Columns.Add("ID", typeof(System.Int64));
#endregion
#region 2.Lấy cấu trúc bảng DM_NHAN_VIEN & Merge dữ liệu từ thông tin ứng viên sang
DataSet dsMain = DABase.getDatabase().LoadDataSet(new QueryBuilder(@"SELECT * FROM DM_NHAN_VIEN WHERE 1=0 and 1=1"), "DM_NHAN_VIEN");
HelpDataSet.MergeDataSet(new string[] { "ID" }, dsMain, ds);
foreach (DataRow row in dsMain.Tables["DM_NHAN_VIEN"].Rows)
{
row["VISIBLE_BIT"] = "Y";
}
#endregion
this.gridControlMaster.DataSource = dsMain.Tables["DM_NHAN_VIEN"];
}
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;
}
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()
{
CreateGridMaster();//Khởi tạo lại cột tương ứng với thời gian được chọn.
QueryBuilder query = new QueryBuilder(@"SELECT * FROM BANG_CHAM_CONG_64 WHERE 1=1");
query.addID("EMP_ID", PLMulNhanVien._getSelectedIDs());
query.addLike("THANG_NAM", dateThangNam._timeEdit.Text);
query.addCondition("1=1");
return query;
}
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;
}
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;
}
/// <summary>Step 5: Xây dựng Query Buider cho việc tìm kiếm
/// Xây dựng một QueryBuilder từ những chọn lựa trong phần filter.
/// Từ QueryBuilder này ta có thể lấy được dữ liệu thỏa điều kiện.
/// Nếu hỗ trợ duyệt thì trong câu truy vấn trả về
/// phải có cột là DUYET_BIT
/// </summary>
public override QueryBuilder PLBuildQueryFilter()
{
error.ClearErrors();
gridViewMaster.ClearGrouping();
if (TruongCap1.Text != string.Empty)
{
gridViewMaster.Columns[(int)TruongCap1._getSelectedID()].Group();
gridViewMaster.Columns[(int)TruongCap1._getSelectedID()].Visible = false;
}
if (TruongCap2.Text != string.Empty)
{
if (TruongCap2.Text != TruongCap1.Text)
{
gridViewMaster.Columns[(int)TruongCap2._getSelectedID()].Group();
gridViewMaster.Columns[(int)TruongCap2._getSelectedID()].Visible = false;
}
else TruongCap2.SetError(error, "Trường thống kê này đã được sử dụng!");
}
if (TruongCap3.Text != string.Empty)
{
if (TruongCap3.Text != TruongCap1.Text && TruongCap3.Text != TruongCap2.Text)
{
gridViewMaster.Columns[(int)TruongCap3._getSelectedID()].Group();
gridViewMaster.Columns[(int)TruongCap3._getSelectedID()].Visible = false;
}
else TruongCap3.SetError(error, "Trường thống kê này đã được sử dụng!");
}
QueryBuilder filter = new QueryBuilder(UpdateRow());
filter.addSoPhieu(BIEN_MUC.MA_BANG, MaBang.Text);
filter.addLike("LOWER(" + BIEN_MUC.NOI_DUNG + ")", NoiDung.Text.ToLower());
filter.addID(BIEN_MUC.SO_DKCB_HD, SoDKHDCB._getSelectedIDs());
filter.addID(BIEN_MUC.TIET_MUC, TietMuc._getSelectedIDs());
filter.addID(BIEN_MUC.LOAI_LUU_TRU, LoaiBang._getSelectedIDs());
filter.addIn(BIEN_MUC.POST_MASTER, AppCtrl.GetCheckedValues(TinhTrang, true));
filter.addID(BIEN_MUC.DON_VI_CUNG_CAP, DonViCungCap._getSelectedIDs());
filter.addID(BIEN_MUC.DON_VI_SO_HUU, DonViSoHuu._getSelectedIDs());
filter.addDateFromTo(BIEN_MUC.NGAY_NHAP, ngayNhap.FromDate, ngayNhap.ToDate);
long[] IDs = QuocGia._getSelectedIDs();
if (IDs.Length > 0 && IDs.Length < QuocGia.Properties.GetItems().Count)
{
string cond = "(";
foreach (long id in IDs)
{
cond += BIEN_MUC.NUOC_SAN_XUAT + " like '%," + id + "' or " + BIEN_MUC.NUOC_SAN_XUAT + " like '" + id + ",%' or " + BIEN_MUC.NUOC_SAN_XUAT + " like '%," + id + ",%' or ";
}
cond = cond.TrimEnd(' ', 'r', 'o');
cond = cond += ")";
filter.addCondition(cond);
}
filter.addCondition("1=1");
filter.setDescOrderBy(BIEN_MUC.NGAY_CAP_NHAT);
return filter;
}
public static void InitCtrl(PLDMGrid Input, bool ReadOnly, bool? IsAdd)
{
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.addID("fcat.ID", PLConst.QUYEN_DUYET_NGHI_PHEP);
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), DABase.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);
}
}
}
private void btnSave_Click(object sender, EventArgs e)
{
DatabaseFB db = DABase.getDatabase();
#region Thông tin trên lưới
DataSet ds = ((DataTable)this.gridControlMaster.DataSource).DataSet;
foreach (DataRow row in ds.Tables["DM_NHAN_VIEN"].Rows)
row.ClearErrors();
foreach (DataRow row in ds.Tables["DM_NHAN_VIEN"].Rows)
{
row["ID"] = db.GetID(PLConst.G_DANH_MUC);
row["TEN_NV"] = row["NAME"].ToString();
//row["CMND"] = " ";
if (string.Compare(row["DEPARTMENT_ID"].ToString(), string.Empty) == 0)
row.SetColumnError("DEPARTMENT_ID", "Vui lòng vào thông tin \"Phòng ban\"!");
}
if (ds.Tables[0].HasErrors) return;
#endregion
#region Thông tin ứng viên
QueryBuilder query = new QueryBuilder(@"SELECT * FROM RESUME WHERE 1=1");
query.addID("ID", this.Ids.ToArray());
DataSet dsResume = DABase.getDatabase().LoadDataSet(query, "RESUME");
foreach (DataRow _row in dsResume.Tables["RESUME"].Rows)
{
_row["IS_EMPLOYEE"] = "Y";
}
#endregion
DatabaseFB dbUpdate = DABase.getDatabase();
PLTransaction trans = PLTransaction.I(dbUpdate);
try
{
bool bFlag = dbUpdate.UpdateDataSet(ds, trans.dbTrans);
bFlag = dbUpdate.UpdateDataSet(dsResume, trans.dbTrans);
trans.Commit();
if (bFlag)
{
_Refresh();
PLGUIUtil.ClosePhieu(this, true);
}
}
catch (Exception ex)
{
trans.Rollback();
PLException.AddException(ex);
ErrorMsg.ErrorSave("Lưu không thành công.");
}
}
public static void addID(QueryBuilder filter, string opLeft, PLMultiCombobox multicombo)
{
if (multicombo.Text == "") return;
filter.addID(opLeft, multicombo._getSelectedIDs());
}
//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 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;
}
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;
}
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;
}
