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 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 bool KhaiBaoLVNgayNghiChoNV(int[] arrDSNVCheck, DateTime NgayKhaiBao, float PCThem, float PCDem, int currentUserID)
{
string query = @" INSERT INTO LamViecNgayNghi (UserEnrollNumber, Ngay, PCThem, PCDem, UserID, Duyet)
VALUES (@UserEnrollNumber, @Ngay, @PCThem, @PCDem,@UserID, 0 )" ;// mặc định số 0 là khai báo thì chưa được duyệt, chờ được duyệt
for (int i = 0; i < arrDSNVCheck.Length; i++)
{
int UserEnrollNumber = arrDSNVCheck[i];
int kqThuchien = SqlDataAccessHelper.ExecNoneQueryString(query,
new string[] { "@UserEnrollNumber", "@Ngay", "@PCThem", "@PCDem", "@UserID" },
new object[] { UserEnrollNumber, NgayKhaiBao, PCThem, PCDem, currentUserID });
if (kqThuchien == 0)
{
return(false);
}
}
return(true);
}
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);
}
internal static bool CapNhatHeSoLuong(int iMaCC, Single hslCB, Single hslSP, Single hsBHcongthem)
{
int kqThaotac = 0;
bool flagError = false;
#region query
string query = @" UPDATE UserInfo SET HeSoLuongCB = @HeSoLuongCB, HeSoLuongSP = @HeSoLuongSP, HSBHCongThem=@HSBHCongThem
WHERE UserEnrollNumber = @UserEnrollNumber" ;
#endregion
kqThaotac = SqlDataAccessHelper.ExecNoneQueryString(query
, new string[] { "@UserEnrollNumber", "@HeSoLuongCB", "@HeSoLuongSP", "@HSBHCongThem" }
, new object[] { iMaCC, hslCB, hslSP, hsBHcongthem });
if (kqThaotac == 0)
{
flagError = true;
}
if (flagError == true)
{
return(false);
}
return(true);
}
public static bool XoaNgayVangNV(DataRow[] arrRecord)
{
int kqThaotac = 0;
bool flagError = false;
string query = @"DELETE FROM Absent WHERE ID = @ID";
foreach (DataRow row in arrRecord)
{
int ID = (int)row["ID"];
kqThaotac = SqlDataAccessHelper.ExecNoneQueryString(query, new string[] { "@ID" }, new object[] { ID });
if (kqThaotac == 0)
{
flagError = true;
break;
}
}
if (flagError == true)
{
return(false);
}
return(true);
}
public static bool XoaLamViecNgayNghiDaKhaiBao(DataRow[] arrRecord)
{
int kqThaotac = 0;
bool flagError = false;
string query = @" DELETE FROM LamViecNgayNghi WHERE ID = @ID";
//duyet qua cac record, neu ngay nao da
foreach (DataRow row in arrRecord)
{
int ID = (int)row["ID"];
kqThaotac = SqlDataAccessHelper.ExecNoneQueryString(query, new string[] { "@ID" }, new object[] { ID });
if (kqThaotac == 0)
{
flagError = true;
break;
}
}
if (flagError == true)
{
return(false);
}
return(true);
}
public static int XacNhanGio(int iUserEnrollNumber, int ShiftID, string ShiftCode
, string onnduty, string offduty, int lategrace, int earlygrace, int afterot, int daycount
, float wktime, float wkdayy, int sophutOT, bool tinhPC150
, DateTime timestrInn, DateTime timestrOut, string SourceInn, string SourceOut, int MachineNoInn, int MachineNoOut)
{
#region query
string query = @" declare @ID int ;
INSERT INTO XacNhanCaVaLamThem
( UserEnrollNumber, ShiftID, ShiftCode,
Onduty, Offduty, LateGrace, EarlyGrace, AfterOT,
DayCount, WorkingTime, Workingday,
TimeStrIn, TimeStrOut,
OTMin, TinhPC150, Note )
VALUES
( @UserEnrollNumber, @ShiftID, @ShiftCode,
@Onduty, @Offduty, @LateGrace, @EarlyGrace, @AfterOT,
@DayCount, @WorkingTime, @Workingday,
@TimeStrIn, @TimeStrOut,
@OTMin, @TinhPC150, @Note )
select @ID = @@Identity
UPDATE CheckInOut
SET IDXacNhanCaVaLamThem = @ID
WHERE UserEnrollNumber = @UserEnrollNumber
AND (MachineNo % 2 = @MachineNoIn % 2)
AND (TimeStr between @TimeStrIn and @TimeStrInn30)
UPDATE CheckInOut
SET IDXacNhanCaVaLamThem = @ID
WHERE UserEnrollNumber = @UserEnrollNumber
AND (MachineNo % 2 = @MachineNoOut%2)
AND (TimeStr between @TimeStrOut and @TimeStrOut30)
INSERT INTO LichSuSuaGioVaoRa
( UserEnrollNumber, TimeStrOld, TimeStrNew,
SourceOld, SourceNew, MachineNoOld, MachineNoNew,
UserID, Explain, Note,
ExecuteTime, CommandType
)
VALUES
( @UserEnrollNumber, @TimeStrIn, @TimeStrIn,
@SourceInn, N'PC', @MachineNoIn,21,
@UserID, @Explain, @Note,
GetDate(), 1)
INSERT INTO LichSuSuaGioVaoRa
( UserEnrollNumber, TimeStrOld, TimeStrNew,
SourceOld, SourceNew, MachineNoOld, MachineNoNew,
UserID, Explain, Note,
ExecuteTime, CommandType
)
VALUES
( @UserEnrollNumber, @TimeStrOut, @TimeStrOut,
@SourceOut, N'PC', @MachineNoOut,22,
@UserID, @Explain, @Note,
GetDate(), 1)
" ;
#endregion
DateTime TimeStrInn30 = timestrInn.Add(ThamSo._30phut);
DateTime TimeStrOut30 = timestrOut.Add(ThamSo._30phut);
return(SqlDataAccessHelper.ExecNoneQueryString(query
, new string[] { "@UserEnrollNumber", "@ShiftID", "@ShiftCode",
"@Onduty", "@Offduty", "@LateGrace", "@EarlyGrace", "@AfterOT",
"@DayCount", "@WorkingTime", "@Workingday",
"@OTMin", "@TinhPC150",
"@TimeStrIn", "@TimeStrOut", "@TimeStrInn30", "@TimeStrOut30",
"SourceInn", "SourceOut", "@MachineNoIn", "@MachineNoOut",
"@Note", "@Explain", "@UserID" }
, new object[] { iUserEnrollNumber, ShiftID, ShiftCode,
onnduty, offduty, lategrace, earlygrace, afterot,
daycount, wktime, wkdayy,
sophutOT, tinhPC150,
timestrInn, timestrOut, TimeStrInn30, TimeStrOut30,
SourceInn, SourceOut, MachineNoInn, MachineNoOut,
"Xác nhận giờ chấm công", "", ThamSo.currUserID }));
}
public static int ThemChamCongTay(int iUserEnrollNumber, DateTime timeBD, DateTime timeKT, int ShiftID, string ShiftCode, string onnduty, string offduty, int lategrace, int earlygrace, int afterot, int daycount, float wktime, float wkdayy, int sophutOT, bool tinhPC150, string lydo, string ghichu)
{
#region query
string query = @" declare @ID int ;
INSERT INTO XacNhanCaVaLamThem
( UserEnrollNumber, ShiftID, ShiftCode,
Onduty, Offduty, LateGrace, EarlyGrace, AfterOT,
DayCount, WorkingTime, Workingday,
TimeStrIn, TimeStrOut,
OTMin, TinhPC150, Note )
VALUES
( @UserEnrollNumber, @ShiftID, @ShiftCode,
@Onduty, @Offduty, @LateGrace, @EarlyGrace, @AfterOT,
@DayCount, @WorkingTime, @Workingday,
@TimeStrIn, @TimeStrOut,
@OTMin, @TinhPC150, N'Chấm công tay' )
select @ID = @@Identity
INSERT INTO CheckInOut
( UserEnrollNumber, TimeDate, TimeStr,
OriginType, Source, MachineNo, WorkCode, IDXacNhanCaVaLamThem )
VALUES
( @UserEnrollNumber, @TimeDateIn, @TimeStrIn,
N'I', N'PC', 21, 0, @ID)
INSERT INTO CheckInOut
( UserEnrollNumber, TimeDate, TimeStr,
OriginType, Source, MachineNo, WorkCode, IDXacNhanCaVaLamThem )
VALUES
( @UserEnrollNumber, @TimeDateOut, @TimeStrOut,
N'O', N'PC', 22, 0, @ID)
INSERT INTO LichSuSuaGioVaoRa
( UserEnrollNumber, TimeStrOld, TimeStrNew,
SourceOld, SourceNew, MachineNoOld, MachineNoNew,
UserID, Explain, Note,
ExecuteTime, CommandType
)
VALUES
( @UserEnrollNumber, @TimeStrIn, @TimeStrIn,
N'PC', N'PC', 21,21,
@UserID, @Explain, @Note,
GetDate(), 1)
INSERT INTO LichSuSuaGioVaoRa
( UserEnrollNumber, TimeStrOld, TimeStrNew,
SourceOld, SourceNew, MachineNoOld, MachineNoNew,
UserID, Explain, Note,
ExecuteTime, CommandType
)
VALUES
( @UserEnrollNumber, @TimeStrOut, @TimeStrOut,
N'PC', N'PC', 22,22,
@UserID, @Explain, @Note,
GetDate(), 1)
" ;
#endregion
return(SqlDataAccessHelper.ExecNoneQueryString(query
, new string[] { "@UserEnrollNumber", "@ShiftID", "@ShiftCode",
"@Onduty", "@Offduty", "@LateGrace", "@EarlyGrace", "@AfterOT",
"@DayCount", "@WorkingTime", "@Workingday",
"@OTMin", "@TinhPC150",
"@TimeStrIn", "@TimeStrOut", "@TimeDateIn", "@TimeDateOut",
"@Note", "@Explain", "@UserID" }
, new object[] { iUserEnrollNumber, ShiftID, ShiftCode,
onnduty, offduty, lategrace, earlygrace, afterot,
daycount, wktime, wkdayy,
sophutOT, tinhPC150,
timeBD, timeKT, timeBD.Date, timeKT.Date,
ghichu, lydo, ThamSo.currUserID }));
}