CREATE PROCEDURE [dbo].[updatetbh_PheDuyetDonXinNghiPhep]
(@MaPheDuyetDonXinNghiPhep [numeric],
@MaDonVangMat [numeric],
@MaNhanVienDuyet [NVARCHAR](20),
@Duyet [BIT],
@NgayDuyet [DATETIME],
@GhiChu [NVARCHAR](200),
@GhiNhan BIT = NULL,
@HinhThuc BIT,
@ERR_MSG NVARCHAR(200) output,
@key NVARCHAR(100))
AS
DECLARE @nSoNgayNghi REAL, @MaNhanVien NVARCHAR(20), @cMaLyDoNghi NVARCHAR(20), @dTuNgay DATETIME,@dDenNgay DATETIME, @cMaCoCauToChuc NVARCHAR(20)
-- Declare Su dung cho tin nhan
DECLARE @SoDT NVARCHAR(50), @XacNhanDaDuyet NVARCHAR(20), @SoNgayNghi NVARCHAR(20), @NghiTuNgay DATETIME, @NghiDenNgay DATETIME
DECLARE @NoiDungTN NVARCHAR(500), @CoGuiTN BIT, @ChuongTrinhDuocPhepGui BIT
SELECT @MaNhanVien = cMaNhanVien, @nSoNgayNghi = nSoNgayNghi, @cMaLyDoNghi = cMaLyDoNghi,
@dTuNgay = dTuNgay, @dDenNgay = dDenNgay, @cMaCoCauToChuc = MaCoCauToChuc
FROM tbh_DonVangMat INNER JOIN tbh_NhanVien ON tbh_DonVangMat.cMaNhanVien = tbh_NhanVien.MaNhanVien
WHERE nMaDonVangMat=@MaDonVangMat
BEGIN TRAN d
DECLARE @DaDuyet BIT-- truong hop Don vang mat chinh sua thi khogn cap nhat duyet len = 1
SET @ERR_MSG =''
-- Lay trang thai don xin nghi phep
SELECT @DaDuyet = Duyet
FROM tbh_PheDuyetDonXinNghiPhep
WHERE MaPheDuyetDonXinNghiPhep = @MaPheDuyetDonXinNghiPhep
IF @DaDuyet = 0 AND @GhiNhan IS NULL -- truong hop bang 0 thi moi xet cho chuyen thanh 1 hay khong
SET @DaDuyet = @Duyet
ELSE
SET @DaDuyet = 1
-- Cap nhat ma nhan vien phe duyet, ngay duyet, ghi chu, duyet
UPDATE [dbo].[tbh_PheDuyetDonXinNghiPhep]
SET [MaNhanVienDuyet] = @MaNhanVienDuyet, [Duyet] = @DaDuyet,
[NgayDuyet] = @NgayDuyet, [GhiChu] = @GhiChu
WHERE [MaPheDuyetDonXinNghiPhep] = @MaPheDuyetDonXinNghiPhep
IF @@Error<>0 BEGIN
ROLLBACK TRAN d
RETURN @@Error
END
IF NOT @GhiNhan IS NULL BEGIN
IF @GhiNhan = 1 -- chap thuan
BEGIN
UPDATE tbh_DonVangMat
SET nMaTinhTrang = 3
WHERE nMaDonVangMat = @MaDonVangMat
IF @cMaLyDoNghi IN ('CO', 'RO', 'NTS', 'DS', 'KLD', 'NTS_HT', 'NTS_KT', 'O')BEGIN
UPDATE tbh_DonVangMat
SET nSoNgayNghiTruLuong = nSoNgayNghi
WHERE nMaDonVangMat = @MaDonVangMat
END
------------------ BAT DAU CAP NHAT CHAM CONG ---------------
DECLARE @SoNgayCT INT, @Count INT
SET @Count=0
SET @SoNgayCT= DATEDIFF(DAY, @dTuNgay, @dDenNgay) + 1
WHILE @COUNT < @SoNgayCT
BEGIN
IF (@cMaLyDoNghi='NTS' AND [dbo].[fx_cLaNgayNghiTheoDangCong](@dTuNgay,@MaNhanVien, 1) = 0) OR @cMaLyDoNghi<>'NTS'
EXEC dbo.inserttbh_CC @dTuNgay ,@MaNhanVien,@cMaLyDoNghi-- @TuNgay,@MaNhanVienVangMat
SET @dTuNgay= DATEADD(day,1,@dTuNgay) --DATEADD(day,1,@TuNgay)
SET @Count=@Count+1
END
--------------------KET THUC CAP NHAT CHAM CONG---------------
END
ELSE-- khong chap thuan
UPDATE tbh_DonVangMat
SET nMaTinhTrang = 4
WHERE nMaDonVangMat = @MaDonVangMat
IF @@Error<>0 BEGIN
ROLLBACK TRAN d
RETURN @@Error
END
END ----------// END IF @GhiNhan IS NOT NULL
UPDATE tbh_DonVangMat
SET bHinhThucNghi= @HinhThuc
WHERE nMaDonVangMat = @MaDonVangMat
IF @@Error<>0 BEGIN
ROLLBACK TRAN d
RETURN @@Error
END
IF @Duyet=1 AND (@cMaLyDoNghi='NTS' Or @cMaLyDoNghi='NTS_HT' Or @cMaLyDoNghi='NTS_KT' Or @cMaLyDoNghi='O' Or @cMaLyDoNghi='CO') BEGIN
EXEC [dbo].[updatetbh_PheDuyetDonXinNghiPhep_NghiHuongBHXH]
@MaDonVangMat, -- @MaDonVangMat [numeric],
@cMaLyDoNghi, -- @cMaLyDoNghi [NVARCHAR](20),
@NgayDuyet, -- @NgayDuyet [DATETIME],
@dTuNgay, -- @dTuNgay [DATETIME],
@dDenNgay, -- @dDenNgay [DATETIME],
@MaNhanVien, -- @MaNhanVien NVARCHAR(20),
@Duyet, -- @Duyet BIT,
@nSoNgayNghi, -- @nSoNgayNghi REAL,
@ERR_MSG, -- @ERR_MSG NVARCHAR(200) output)
@key -- Key
END
--TungNTT UPDATE 20110924 cap nhat gui tin nhan
SELECT @SoNgayNghi=nSoNgayNghi, @NghiTuNgay=dTuNgay, @NghiDenNgay=dDenNgay, @SoDT=DiDong
FROM tbh_DonVangMat vm INNER JOIN tbh_NhanVien nv ON vm.cMaNhanVien=nv.MaNhanVien
WHERE vm.nMaDonVangMat=@MaDonVangMat
SET @CoGuiTN=(SELECT [dbo].[fx_LayThongSo_KieuSo] ('bInformApprovingResultOfLeave' ))
SET @ChuongTrinhDuocPhepGui=(SELECT [dbo].[fx_LayThongSo_KieuSo] ('bIntegratedSMS' ))
IF @GhiNhan=1
SET @XacNhanDaDuyet='duoc duyet'
ELSE
SET @XacNhanDaDuyet='bi tu choi'
SET @NoiDungTN='Don xin nghi phep '+@SoNgayNghi+' ngay tu ngay '+Convert(varchar(30),@NghiTuNgay,103) +' den ngay '+Convert(varchar(30),@NghiDenNgay,103)+ ' cua ban da ' +@XacNhanDaDuyet
IF ((@SoDT <> '') and @CoGuiTN=1 and @ChuongTrinhDuocPhepGui=1 )
exec Inserttbs_TinNhan @NoiDungTN,@SoDT,0,0,NULL,'bInformApprovingResultOfLeave'
--END cap nhat
Commit TRAN d
RETURN @@Error
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--------------------------------------------------------------------+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE OR REPLACE FUNCTION updatetbh_PheDuyetDonXinNghiPh
(
v_MaPheDuyetDonXinNghiPhep IN NUMBER DEFAULT NULL ,
v_MaDonVangMat IN NUMBER DEFAULT NULL ,
v_MaNhanVienDuyet IN NVARCHAR2 DEFAULT NULL ,
v_Duyet IN NUMBER DEFAULT NULL ,
v_NgayDuyet IN DATE DEFAULT NULL ,
v_GhiChu IN NVARCHAR2 DEFAULT NULL ,
v_GhiNhan IN NUMBER DEFAULT NULL ,
v_HinhThuc IN NUMBER DEFAULT NULL ,
v_ERR_MSG OUT NVARCHAR2,
v_key IN NVARCHAR2 DEFAULT NULL
)
RETURN NUMBER
AS
v_sys_error NUMBER := 0;
v_nSoNgayNghi FLOAT(24);
v_MaNhanVien NVARCHAR2(20);
v_cMaLyDoNghi NVARCHAR2(20);
v_dTuNgay DATE;
v_dDenNgay DATE;
v_cMaCoCauToChuc NVARCHAR2(20);
-- Declare Su dung cho tin nhan
v_SoDT NVARCHAR2(50);
v_XacNhanDaDuyet NVARCHAR2(20);
v_SoNgayNghi NVARCHAR2(20);
v_NghiTuNgay DATE;
v_NghiDenNgay DATE;
v_NoiDungTN NVARCHAR2(500);
v_CoGuiTN NUMBER(1,0);
v_ChuongTrinhDuocPhepGui NUMBER(1,0);
v_DaDuyet NUMBER(1,0);-- truong hop Don vang mat chinh sua thi khogn cap nhat duyet len = 1
BEGIN
BEGIN
SELECT cMaNhanVien ,
nSoNgayNghi ,
cMaLyDoNghi ,
dTuNgay ,
dDenNgay ,
MaCoCauToChuc
INTO v_MaNhanVien,
v_nSoNgayNghi,
v_cMaLyDoNghi,
v_dTuNgay,
v_dDenNgay,
v_cMaCoCauToChuc
FROM tbh_DonVangMat
JOIN tbh_NhanVien
ON tbh_DonVangMat.cMaNhanVien = tbh_NhanVien.MaNhanVien
WHERE nMaDonVangMat = v_MaDonVangMat;
EXCEPTION
WHEN OTHERS THEN
v_sys_error := SQLCODE;
END;
--SQL Server BEGIN TRANSACTION;
sqlserver_utilities.incrementTrancount;
v_ERR_MSG := '' ;
-- Lay trang thai don xin nghi phep
BEGIN
SELECT Duyet
INTO v_DaDuyet
FROM tbh_PheDuyetDonXinNghiPhep
WHERE MaPheDuyetDonXinNghiPhep = v_MaPheDuyetDonXinNghiPhep;
EXCEPTION
WHEN OTHERS THEN
v_sys_error := SQLCODE;
END;
IF v_DaDuyet = 0
AND v_GhiNhan IS NULL-- truong hop bang 0 thi moi xet cho chuyen thanh 1 hay khong
THEN
v_DaDuyet := v_Duyet ;
ELSE
v_DaDuyet := 1 ;
END IF;
-- Cap nhat ma nhan vien phe duyet, ngay duyet, ghi chu, duyet
BEGIN
UPDATE tbh_PheDuyetDonXinNghiPhep
SET MaNhanVienDuyet = v_MaNhanVienDuyet,
Duyet = v_DaDuyet,
NgayDuyet = v_NgayDuyet,
GhiChu = v_GhiChu
WHERE MaPheDuyetDonXinNghiPhep = v_MaPheDuyetDonXinNghiPhep;
EXCEPTION
WHEN OTHERS THEN
v_sys_error := SQLCODE;
END;
IF v_sys_error <> 0 THEN
BEGIN
ROLLBACK;
sqlserver_utilities.resetTrancount;
RETURN v_sys_error;
END;
END IF;
IF NOT v_GhiNhan IS NULL THEN
BEGIN
IF v_GhiNhan = 1 THEN
DECLARE
------------------ BAT DAU CAP NHAT CHAM CONG ---------------
v_SoNgayCT NUMBER(10,0);
v_Count NUMBER(10,0);
-- chap thuan
BEGIN
BEGIN
UPDATE tbh_DonVangMat
SET nMaTinhTrang = 3
WHERE nMaDonVangMat = v_MaDonVangMat;
EXCEPTION
WHEN OTHERS THEN
v_sys_error := SQLCODE;
END;
IF v_cMaLyDoNghi IN ( 'CO','RO','NTS','DS','KLD','NTS_HT','NTS_KT','O' )
THEN
BEGIN
BEGIN
UPDATE tbh_DonVangMat
SET nSoNgayNghiTruLuong = nSoNgayNghi
WHERE nMaDonVangMat = v_MaDonVangMat;
EXCEPTION
WHEN OTHERS THEN
v_sys_error := SQLCODE;
END;
END;
END IF;
v_Count := 0 ;
v_SoNgayCT := sqlserver_utilities.datediff('DAY', v_dTuNgay, v_dDenNgay) + 1 ;
WHILE v_COUNT < v_SoNgayCT
LOOP
BEGIN
IF ( v_cMaLyDoNghi = 'NTS'
AND fx_cLaNgayNghiTheoDangCong(v_dTuNgay, v_MaNhanVien, 1) = 0 )
OR v_cMaLyDoNghi <> 'NTS' THEN
BEGIN
inserttbh_CC(v_dTuNgay,
v_MaNhanVien,
v_cMaLyDoNghi);
EXCEPTION
WHEN OTHERS THEN
v_sys_error := SQLCODE;
END;-- @TuNgay,@MaNhanVienVangMat
END IF;
v_dTuNgay := sqlserver_utilities.dateadd('DAY', 1, v_dTuNgay) ;--DATEADD(day,1,@TuNgay)
v_Count := v_Count + 1 ;
END;
END LOOP;
END;
--------------------KET THUC CAP NHAT CHAM CONG---------------
ELSE-- khong chap thuan
BEGIN
UPDATE tbh_DonVangMat
SET nMaTinhTrang = 4
WHERE nMaDonVangMat = v_MaDonVangMat;
EXCEPTION
WHEN OTHERS THEN
v_sys_error := SQLCODE;
END;
END IF;
IF v_sys_error <> 0 THEN
BEGIN
ROLLBACK;
sqlserver_utilities.resetTrancount;
RETURN v_sys_error;
END;
END IF;
END;
END IF;
----------// END IF @GhiNhan IS NOT NULL
BEGIN
UPDATE tbh_DonVangMat
SET bHinhThucNghi = v_HinhThuc
WHERE nMaDonVangMat = v_MaDonVangMat;
EXCEPTION
WHEN OTHERS THEN
v_sys_error := SQLCODE;
END;
IF v_sys_error <> 0 THEN
BEGIN
ROLLBACK;
sqlserver_utilities.resetTrancount;
RETURN v_sys_error;
END;
END IF;
IF v_Duyet = 1
AND ( v_cMaLyDoNghi = 'NTS'
OR v_cMaLyDoNghi = 'NTS_HT'
OR v_cMaLyDoNghi = 'NTS_KT'
OR v_cMaLyDoNghi = 'O'
OR v_cMaLyDoNghi = 'CO' ) THEN
BEGIN
BEGIN
updatetbh_PheDuyetDonXinNghiPh(v_MaDonVangMat-- @MaDonVangMat [numeric],
,
v_cMaLyDoNghi-- @cMaLyDoNghi [NVARCHAR](20),
,
v_NgayDuyet-- @NgayDuyet [DATETIME],
,
v_dTuNgay-- @dTuNgay [DATETIME],
,
v_dDenNgay-- @dDenNgay [DATETIME],
,
v_MaNhanVien-- @MaNhanVien NVARCHAR(20),
,
v_Duyet-- @Duyet BIT,
,
v_nSoNgayNghi-- @nSoNgayNghi REAL,
,
v_ERR_MSG-- @ERR_MSG NVARCHAR(200) output)
,
v_key);
EXCEPTION
WHEN OTHERS THEN
v_sys_error := SQLCODE;
END;-- Key
END;
END IF;
--TungNTT UPDATE 20110924 cap nhat gui tin nhan
BEGIN
SELECT nSoNgayNghi ,
dTuNgay ,
dDenNgay ,
DiDong
INTO v_SoNgayNghi,
v_NghiTuNgay,
v_NghiDenNgay,
v_SoDT
FROM tbh_DonVangMat vm
JOIN tbh_NhanVien nv
ON vm.cMaNhanVien = nv.MaNhanVien
WHERE vm.nMaDonVangMat = v_MaDonVangMat;
EXCEPTION
WHEN OTHERS THEN
v_sys_error := SQLCODE;
END;
SELECT fx_LayThongSo_KieuSo('bInformApprovingResultOfLeave')
INTO v_CoGuiTN
FROM DUAL ;
SELECT fx_LayThongSo_KieuSo('bIntegratedSMS')
INTO v_ChuongTrinhDuocPhepGui
FROM DUAL ;
IF v_GhiNhan = 1 THEN
v_XacNhanDaDuyet := 'duoc duyet' ;
ELSE
v_XacNhanDaDuyet := 'bi tu choi' ;
END IF;
v_NoiDungTN := 'Don xin nghi phep ' || v_SoNgayNghi || ' ngay tu ngay ' || sqlserver_utilities.convert_('VARCHAR2(30)', v_NghiTuNgay, 103) || ' den ngay ' || sqlserver_utilities.convert_('VARCHAR2(30)', v_NghiDenNgay, 103) || ' cua ban da ' || v_XacNhanDaDuyet ;
IF ( ( v_SoDT <> '' )
AND v_CoGuiTN = 1
AND v_ChuongTrinhDuocPhepGui = 1 ) THEN
BEGIN
Inserttbs_TinNhan(v_NoiDungTN,
v_SoDT,
0,
0,
NULL,
'bInformApprovingResultOfLeave');
EXCEPTION
WHEN OTHERS THEN
v_sys_error := SQLCODE;
END;
END IF;
sqlserver_utilities.commit_transaction;
--END cap nhat
RETURN v_sys_error;
END;
Bạn đang đọc truyện trên: Truyen2U.Pro