public static DataTable LayTableCIO_A(int[] ArrDSMaCC_Checked, DateTime ngayBD, DateTime ngayKT)
{
#region query
string query = @" select UserEnrollNumber, TimeStr, MachineNo, Source
from CheckInOut
where ( (TimeStr between @BDVao and @KTVao and MachineNo % 2 = 1)
or (TimeStr between @BDRaa and @KTRaa and MachineNo % 2 = 0) )
and IDXacNhanCaVaLamThem is null
and ( UserEnrollNumber = {0} )
order by UserEnrollNumber asc, TimeStr asc ";
query = String.Format(query, String.Join(" or UserEnrollNumber = ", ArrDSMaCC_Checked));
#endregion
DataTable tableCIO_A = SqlDataAccessHelper.ExecuteQueryString(query
, new[] { "@BDVao", "@KTVao", "@BDRaa", "@KTRaa" }
, new object[] { ngayBD, ngayKT, ngayBD, ngayKT });
return(tableCIO_A);
}
public static DataTable LayTableLamViecNgayNghi(int[] ArrDSMaCC_Checked, DateTime ngayBD, DateTime ngayKT)
{
string query = @" SELECT ID, LamViecNgayNghi.UserEnrollNumber, UserFullName, Ngay, HeSoPC,
case when HeSoPC = 1 then N'100% Lương'
when HeSoPC = 2 then N'200% Lương'
when HeSoPC = 0.5 then N'50% Lương'
end as PhanTramHeSo
FROM LamViecNgayNghi, UserInfo
WHERE ( Ngay between @NgayBD and @NgayKT ) and ( LamViecNgayNghi.UserEnrollNumber = {0} )
and UserInfo.UserEnrollNumber = LamViecNgayNghi.UserEnrollNumber
order by LamViecNgayNghi.UserEnrollNumber asc, Ngay asc" ;
query = String.Format(query, String.Join(" or LamViecNgayNghi.UserEnrollNumber = ", ArrDSMaCC_Checked));
DataTable tableLamViecNgayNghi = SqlDataAccessHelper.ExecuteQueryString(query
, new string[] { "@NgayBD", "@NgayKT" }
, new object[] { ngayBD, ngayKT });
return(tableLamViecNgayNghi);
}
public static DataTable LayTableVang(int[] ArrDSMaCC_Checked, DateTime ngayBD, DateTime ngayKT)
{
string query = @" SELECT ID, UserInfo.UserEnrollNumber, UserInfo.UserFullCode, UserInfo.UserFullName
, TimeDate, LoaiVang.AbsentCode, LoaiVang.AbsentSymbol,LoaiVang.AbsentDescription
, Thang, Nam, Absent.Workingday, Absent.WorkingTime
FROM Absent, LoaiVang , UserInfo
WHERE (Absent.AbsentCode = LoaiVang.AbsentCode)
and (UserInfo.UserEnrollNumber = Absent.UserEnrollNumber)
and (TimeDate between @NgayBD and @NgayKT)
and ( UserInfo.UserEnrollNumber = {0} )
ORDER BY UserInfo.UserEnrollNumber ASC,Nam DESC, Thang DESC, TimeDate ASC ";
query = String.Format(query, String.Join(" or UserInfo.UserEnrollNumber = ", ArrDSMaCC_Checked));
DataTable tableVang = SqlDataAccessHelper.ExecuteQueryString(query
, new string[] { "@NgayBD", "@NgayKT" }
, new object[] { ngayBD, ngayKT });
return(tableVang);
}
public static DataTable LayDSPhong(int userID)
{
#region query
string query = @" SELECT r1.ID as ID,r1.RelationID as RelationID,r1.Description as Description
,r2.ID as ID_2,r2.Description as Description_2
,r3.ID as ID_3,r3.Description as Description_3
FROM RelationDept r1
LEFT OUTER JOIN RelationDept r2 ON (r1.RelationID = r2.ID)
LEFT OUTER JOIN RelationDept r3 ON (r2.RelationID = r3.ID)
,DeptPrivilege
WHERE DeptPrivilege.UserID = @UserID
AND DeptPrivilege.IsYes = 1
AND DeptPrivilege.IDD = r1.ID ";
#endregion
DataTable kq = SqlDataAccessHelper.ExecuteQueryString(query, new[] { "@UserID" }, new object[] { userID });
return(kq);
}
public static DataTable LayTableCIO_V(int[] ArrDSMaCC_Checked, DateTime ngayBD, DateTime ngayKT)
{
string query = @" SELECT CIO.UserEnrollNumber
,CIO.TimeStr, CIO.Source, CIO.MachineNo
,XN.ID,ShiftID,ShiftCode,Onduty,Offduty
,LateGrace,EarlyGrace,AfterOT
,DayCount,WorkingTime,Workingday
,OTMin,TinhPC150,Note
FROM XacNhanCaVaLamThem XN, CheckInOut CIO
where CIO.IDXacNhanCaVaLamThem = XN.ID
and ( (TimeStr between @BDVao and @KTVao and MachineNo % 2 = 1)
or (TimeStr between @BDRaa and @KTRaa and MachineNo % 2 = 0) )
and ( CIO.UserEnrollNumber = {0} )
order by CIO.UserEnrollNumber asc, CIO.TimeStr asc ";
query = String.Format(query, String.Join(" or CIO.UserEnrollNumber = ", ArrDSMaCC_Checked));
DataTable tableCIO_V = SqlDataAccessHelper.ExecuteQueryString(query
, new[] { "@BDVao", "@KTVao", "@BDRaa", "@KTRaa" }
, new object[] { ngayBD, ngayKT, ngayBD, ngayKT });
return(tableCIO_V);
}