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 })); }