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