public static DataTable LayTableCIO_V(int[] ArrDSMaCC_Checked, DateTime ngayBD, DateTime ngayKT)
{
string query = @" SELECT distinct 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} )
group by CIO.UserEnrollNumber,XN.ID
,CIO.TimeStr,CIO.MachineNo,CIO.Source
,ShiftID,ShiftCode,Onduty,Offduty
,LateGrace,EarlyGrace,AfterOT
,DayCount,WorkingTime,Workingday
,OTMin,TinhPC150,Note
order by CIO.UserEnrollNumber asc, ID 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);
}
public static DataTable LayDSTatCaNV()
{
//note: lấy ds nhân viên đang enable và UserIDD > 0 vì UserIDD = 0 là chưa được phân vào phòng ban nào hết
#region
string query = @" select UserEnrollNumber,UserFullName,UserFullCode
,HeSoLuongCB,HeSoLuongSP,u.SchID,SchName
,UserIDTitle
, case when u.UserIDTitle = 0 then N'Chưa SX'
else TitleName
end as TitleName
,d1.ID as IDD_1, d1.Description as Description_1, d1.TinhPC150, d1.TinhPCNgayLe
,d2.ID as IDD_2, d2.Description as Description_2
--,d3.ID as IDD_3, d3.Description as Description_3
from UserInfo u left join Title on u.UserIDTitle = Title.IDT
left join RelationDept d1 on u.UserIDD = d1.ID
left join RelationDept d2 on d1.RelationID = d2.ID
--left join RelationDept d3 on d2.RelationID = d3.ID
left join Schedule on u.SchID = Schedule.SchID
where UserEnabled = 1 and UserIDD > 0
order by UserEnrollNumber asc ";
#endregion
DataTable table = SqlDataAccessHelper.ExecuteQueryString(query, null, null);
return(table);
}
internal static DataTable DocNgayLe(DateTime ngayBD, DateTime ngayKT)
{
string query = @" select * from Holiday where HDate between @NgayBD and @NgayKT";
DataTable table = SqlDataAccessHelper.ExecuteQueryString(query, new string[] { "@NgayBD", "@NgayKT" }, new object[] { ngayBD, ngayKT });
return(table);
}
internal static DataTable DocHSBHCongThem()
{
string query = @" select UserEnrollNumber, UserFullCode, HSBHCongThem
from UserInfo
where HSBHCongThem > 0" ;
DataTable table = SqlDataAccessHelper.ExecuteQueryString(query, null, null);
return(table);
}
public static DataTable LogIn(string tempUsername, string passEncrypt)
{
string query = @" select UserAccount, UserID from NewUserAccount
where UserAccount = @UserAccount and Password = @Password " ;
DataTable dt = SqlDataAccessHelper.ExecuteQueryString(query
, new[] { "@UserAccount", "@Password" }, new object[] { tempUsername, passEncrypt });
return(dt);
}
public static DataTable LayDSLichTrinh()
{
string query = @" SELECT ID,SchID, T1,T2,T3,T4,T5,T6,T7
FROM ShiftSch
group by SchID, T1,T2,T3,T4,T5,T6,T7, ID" ;
DataTable kq = SqlDataAccessHelper.ExecuteQueryString(query, null, null);
return(kq);
}
internal static DataTable LayDSLamViecNgayNghiChoDuyet()
{
string query = @" select ID , UserInfo.UserEnrollNumber, UserInfo.UserFullCode, UserInfo.UserFullName, Ngay, PCThem, PCDem, UserID
from UserInfo, LamViecNgayNghi
where UserInfo.UserEnrollNumber = LamViecNgayNghi.UserEnrollNumber
and Duyet = 0" ;
DataTable table = SqlDataAccessHelper.ExecuteQueryString(query, null, null);
return(table);
}
public static DataTable PhanQuyenMenu(int userid)
{
#region query
string query = @" select * from MenuPrivilege where MenuPrivilege.UserID = @UserID
order by MenuID ASC, IsYes ASC";
#endregion
DataTable table = SqlDataAccessHelper.ExecuteQueryString(query, new string[] { "@UserID" }, new object[] { userid });
return(table);
}
public static DataTable LayDSCa()
{
string query = @" SELECT ShiftID,ShiftCode,Onduty,Offduty,DayCount,OnTimeIn,OnTimeOut,CutIn,CutOut
,OnLunch,OffLunch, DATEDIFF(MINUTE, 0, Shifts.Onduty) as OndutyMinute, DATEDIFF(MINUTE, 0, Shifts.Offduty) as OffdutyMinute
,WorkingTime,Workingday,LateGrace,EarlyGrace,AfterOT,ShowPosition
FROM Shifts
group by Onduty,Offduty,ShiftID,ShiftCode,DayCount,OnTimeIn,OnTimeOut,CutIn,CutOut
,OnLunch,OffLunch
,WorkingTime,Workingday,LateGrace,EarlyGrace,AfterOT,ShowPosition" ;
DataTable kq = SqlDataAccessHelper.ExecuteQueryString(query, null, null);
return(kq);
}
internal static DataTable SelLichSuSuaGioChamCong(int userID)
{
string query = @" SELECT UserInfo.UserEnrollNumber,UserInfo.UserFullName,UserFullCode
TimeStrOld,TimeStrNew,
SourceOld,SourceNew,MachineNoOld,MachineNoNew,
NewUserAccount.UserID,NewUserAccount.UserAccount,
Explain,Note,ExecuteTime,CommandType
FROM LichSuSuaGioVaoRa, UserInfo, NewUserAccount
where UserInfo.UserEnrollNumber = LichSuSuaGioVaoRa.UserEnrollNumber
and LichSuSuaGioVaoRa.UserID = NewUserAccount.UserID
and NewUserAccount.UserID = @UserID
order by ExecuteTime desc" ;
DataTable table = SqlDataAccessHelper.ExecuteQueryString(query, new[] { "@UserID" }, new object[] { userID });
return(table);
}
public static DataTable LietKeLamViecNgayNghiDaKhaiBao(int[] arrDSNVCheck, DateTime ngayBD, DateTime ngayKT)
{
#region query
string query = @"SELECT ID, LamViecNgayNghi.UserEnrollNumber, UserFullName, Ngay, PCThem, PCDem, Duyet, UserFullCode
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 = ", arrDSNVCheck));
#endregion
DataTable table = SqlDataAccessHelper.ExecuteQueryString(query
, new string[] { "@NgayBD", "@NgayKT" }
, new object[] { ngayBD, ngayKT });
return(table);
}
public static DataTable LietKeNgayVangChoNV(object[] arrDSNVCheck, 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 = ", arrDSNVCheck));
DataTable table = SqlDataAccessHelper.ExecuteQueryString(query
, new string[] { "@NgayBD", "@NgayKT" }
, new object[] { ngayBD, ngayKT });
return(table);
}
public static DataTable LayTableLamViecNgayNghi(int[] ArrDSMaCC_Checked, DateTime ngayBD, DateTime ngayKT)
{
// lấy danh sách ngày nghỉ đã được duyệt để tính công
string query = @" SELECT ID, LamViecNgayNghi.UserEnrollNumber, UserFullName, Ngay, PCThem, PCDem, Duyet
FROM LamViecNgayNghi, UserInfo
WHERE ( Ngay between @NgayBD and @NgayKT ) and ( LamViecNgayNghi.UserEnrollNumber = {0} )
and UserInfo.UserEnrollNumber = LamViecNgayNghi.UserEnrollNumber
and Duyet = 1
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 LayTableCIO_A(int[] ArrDSMaCC_Checked, DateTime ngayBD, DateTime ngayKT)
{
#region query
string query = @" select distinct CheckInOut.UserEnrollNumber, TimeStr, MachineNo, Source, UserFullCode, UserFullName
from CheckInOut, UserInfo
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 UserInfo.UserEnrollNumber = CheckInOut.UserEnrollNumber
and ( CheckInOut.UserEnrollNumber = {0} )
order by CheckInOut.UserEnrollNumber asc, TimeStr asc ";
query = String.Format(query, String.Join(" or CheckInOut.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 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);
}