Exemplo n.º 1
0
        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);
        }
Exemplo n.º 2
0
        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);
        }
Exemplo n.º 3
0
        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);
        }
Exemplo n.º 4
0
        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);
        }
Exemplo n.º 5
0
        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);
        }
Exemplo n.º 6
0
        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);
        }
Exemplo n.º 7
0
        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);
        }
Exemplo n.º 8
0
        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);
        }
Exemplo n.º 9
0
        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 }));
        }
Exemplo n.º 10
0
        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 }));
        }