public static string KiemTraPhuThuoc(string boPhan)
{
string maBoPhan = DAO_BoPhan.LoadMaBoPhan(boPhan);
string sChuoiTruyVan = string.Format("SELECT COUNT(UserFullCode) FROM UserInfo WHERE UserIDD IN ({0})", maBoPhan);
return(DataProvider.TruyVanExecuteScalar(sChuoiTruyVan));
}
public static DataTable LoadTangCa(string boPhan, string maNV, string mode)
{
string maBoPhan = DAO_BoPhan.LoadMaBoPhan(boPhan);
string sChuoiTruyVan;
if (mode == "Đăng ký cho hôm nay")
{
sChuoiTruyVan = string.Format("SELECT * FROM dbo.fn_get_oveertime_registering1('{0}','{1}')", maBoPhan, maNV);
}
else
{
sChuoiTruyVan = string.Format("SELECT * FROM dbo.fn_get_oveertime_registering2('{0}','{1}')", maBoPhan, maNV);
}
DataTable dt = new DataTable();
dt = DataProvider.TruyVanDataTable(sChuoiTruyVan);
if (dt != null && dt.Rows.Count > 0) //Nếu datatable hợp lê và có giá trị
{
return(dt);
}
else
{
return(null);
}
}
public static DataTable ReportPhanCa(string fromDate, string toDate, string mode, string boPhan, string maCa)
{
string maBoPhan = DAO_BoPhan.LoadMaBoPhan(boPhan);
string sChuoiTruyVan;
int i = Convert.ToInt16(mode);
if (i == 0)
{
sChuoiTruyVan = string.Format("SELECT * FROM view_report_phanca_notyet WHERE dept_id IN ({0}) ORDER BY staff_id ASC", maBoPhan);
}
else
{
if (Convert.ToInt16(maCa) == 0)
{
sChuoiTruyVan = string.Format("SELECT * from view_report_phanca_done WHERE dept_id IN ({0}) AND phanca_effective_date BETWEEN '{1}' AND '{2}' ORDER BY staff_id ASC", maBoPhan, fromDate, toDate);
}
else
{
sChuoiTruyVan = string.Format("SELECT * from view_report_phanca_done WHERE dept_id IN ({0}) AND shift_id = '{1}' AND phanca_effective_date BETWEEN '{2}' AND '{3}' ORDER BY staff_id ASC", maBoPhan, maCa, fromDate, toDate);
}
}
DataTable dt = new DataTable();
dt = DataProvider.TruyVanDataTable(sChuoiTruyVan);
if (dt != null && dt.Rows.Count > 0)
{
return(dt);
}
else
{
return(null);
}
}
public static DataTable LoadMeal(string fromDate, string toDate, string mode, string boPhan, string maCa)
{
string maBoPhan = DAO_BoPhan.LoadMaBoPhan(boPhan);
string sChuoiTruyVan = null;
int i = Convert.ToInt16(mode);
switch (i)
{
case 0:
{
if (Convert.ToInt16(maCa) == 0)
{
sChuoiTruyVan = string.Format("SELECT * FROM view_attcount_sum WHERE dept_id IN ({0}) ORDER BY dept_id ASC", maBoPhan);
}
else
{
sChuoiTruyVan = string.Format("SELECT * from view_attcount_sum WHERE dept_id IN ({0}) AND shift_id = '{1}' ORDER BY dept_id ASC", maBoPhan, maCa);
}
break;
}
case 1:
{
if (Convert.ToInt16(maCa) == 0)
{
sChuoiTruyVan = string.Format("SELECT * FROM view_mealorder WHERE dept_id IN ({0}) AND att_date BETWEEN '{1}' AND '{2}' ORDER BY dept_id ASC", maBoPhan, fromDate, toDate);
}
else
{
sChuoiTruyVan = string.Format("SELECT * from view_mealorder WHERE dept_id IN ({0}) AND shift_id = '{1}' AND att_date BETWEEN '{2}' AND '{3}' ORDER BY dept_id ASC", maBoPhan, maCa, fromDate, toDate);
}
break;
}
case 2:
{
if (Convert.ToInt16(maCa) == 0)
{
sChuoiTruyVan = string.Format("SELECT * FROM view_attcount_sum WHERE dept_id IN ({0}) ORDER BY dept_id ASC", maBoPhan);
}
else
{
sChuoiTruyVan = string.Format("SELECT * from view_attcount_sum WHERE dept_id IN ({0}) AND shift_id = '{1}' ORDER BY dept_id ASC", maBoPhan, maCa);
}
break;
}
}
DataTable dt = new DataTable();
dt = DataProvider.TruyVanDataTable(sChuoiTruyVan);
if (dt != null && dt.Rows.Count > 0)
{
return(dt);
}
else
{
return(null);
}
}
public static DataTable LoadCheckin(string attDate, string tinhTrang, string boPhan, string maca)
{
string maBoPhan = DAO_BoPhan.LoadMaBoPhan(boPhan);
string sChuoiTruyVan;
int i = Convert.ToInt16(tinhTrang);
if (i == 0)
{
sChuoiTruyVan = string.Format("SELECT * FROM view_attcount_notprocess WHERE dept_id IN ({0}) AND shift_id = '{1}' ORDER BY staff_id ASC", maBoPhan, maca);
}
else
{
sChuoiTruyVan = string.Format("SELECT * FROM view_attcount WHERE dept_id IN ({0}) AND shift_id LIKE '{1}' AND att_date = '{2}' ORDER BY staff_id ASC", maBoPhan, maca, attDate);
}
DataTable dt = new DataTable();
dt = DataProvider.TruyVanDataTable(sChuoiTruyVan);
if (dt != null && dt.Rows.Count > 0)
{
return(dt);
}
else
{
return(null);
}
}
public static DataTable LoadNhanVien(string boPhan, int mode)
{
string maBoPhan = DAO_BoPhan.LoadMaBoPhan(boPhan);
string sChuoiTruyVan;
if (mode == 1)
{
sChuoiTruyVan = string.Format("SELECT * FROM view_staff WHERE dept_id IN ({0})", maBoPhan);
}
else
{
sChuoiTruyVan = string.Format("SELECT * FROM view_staff_copy WHERE dept_id_c IN ({0})", maBoPhan);
}
DataTable dt = new DataTable();
dt = DataProvider.TruyVanDataTable(sChuoiTruyVan);
if (dt != null && dt.Rows.Count > 0) //Nếu datatable hợp lê và có giá trị
{
return(dt);
}
else
{
return(null);
}
}
//Phương thức load bảng tổng hợp dữ liệu phục vụ báo cơm
public static DataTable LoadAttendance(string dtBoPhan)
{
string maBoPhan = DAO_BoPhan.LoadMaBoPhan(dtBoPhan);
string sChuoiTruyVan = string.Format("select * from v_daily_attendance where deptid IN ({0})", maBoPhan);
DataTable dt = new DataTable();
dt = DataProvider.TruyVanDataTable(sChuoiTruyVan);
if (dt != null && dt.Rows.Count > 0) //Nếu datatable hợp lê và có giá trị
{
return(dt);
}
return(null);
}
public static DataTable GetTargetActualLine(string deptID, string fromDate, string toDate)
{
string maBoPhan = DAO_BoPhan.LoadMaBoPhan(deptID);
string sChuoiTruyVan = string.Format("SELECT * FROM view_target_actual_line WHERE dept_id IN ({0}) AND documentdate >= '{1}' AND documentdate <= '{2}' ORDER BY documentdate DESC", maBoPhan, fromDate, toDate);
DataTable dt = new DataTable();
dt = DataProvider.TruyVanDataTable(sChuoiTruyVan);
if (dt != null && dt.Rows.Count > 0) //Nếu datatable hợp lê và có giá trị
{
return(dt);
}
else
{
return(null);
}
}
public static DataTable GetInout(string ad_ord_id, string startDate, string endDate)
{
string maBoPhan = DAO_BoPhan.LoadMaBoPhan(ad_ord_id);
string sChuoiTruyVan = string.Format("SELECT * FROM view_m_inout WHERE dept_id IN ({0}) AND documentdate >= '{1}' AND documentdate <= '{2}' ORDER BY m_inout_id DESC", maBoPhan, startDate, endDate);
DataTable dt = new DataTable();
dt = DataProvider.TruyVanDataTable(sChuoiTruyVan);
if (dt != null && dt.Rows.Count > 0) //Nếu datatable hợp lê và có giá trị
{
return(dt);
}
else
{
return(null);
}
}
public static DataTable LoadAttrecord(string startDate, string endDate, string maNV, string boPhan)
{
string maBoPhan = DAO_BoPhan.LoadMaBoPhan(boPhan);
string sChuoiTruyVan = string.Format("SELECT * FROM view_Attrecord WHERE attrecord_rectime BETWEEN '{0}' AND '{1}' AND staff_id LIKE N'%{2}%' AND dept_id IN ({3})", startDate, endDate, maNV, maBoPhan);
DataTable dt = new DataTable();
dt = DataProvider.TruyVanDataTable(sChuoiTruyVan);
if (dt != null && dt.Rows.Count > 0) //Nếu datatable hợp lê và có giá trị
{
return(dt);
}
else
{
return(null);
}
}