Project

General

Profile

Feature #2131 » CBIATMTOPYPOTKEHADIRAN.sql

Tri Rizqiaty, 05/02/2023 04:43 PM

 
1
ALTER PROCEDURE CBIATMTOPYPOTKEHADIRAN
2
(
3
	@EmployeeID VARCHAR(20)
4
	,@StartDate VARCHAR(20)
5
	,@EndDate VARCHAR(20)
6
)
7
AS
8

    
9
--DECLARE @EmployeeID VARCHAR(20) = '17010491'
10
--DECLARE @StartDate VARCHAR(20) = '20221201'
11
--DECLARE @EndDate VARCHAR(20) = '20221231'
12

    
13
DECLARE @WTBase VARCHAR(250)
14
SELECT @WTBase = Value1
15
FROM dbo.PCMEPGENPARAM WITH (NOLOCK)
16
WHERE Parameter = 'CBIATMTOPYPOTKEHADIRANBASE'
17

    
18
DECLARE @AbsType VARCHAR(250)
19
SELECT @AbsType = Value1
20
FROM dbo.PCMEPGENPARAM WITH (NOLOCK)
21
WHERE Parameter = 'CBIATMTOPYPOTKEHADIRANABSENCETYPE'
22

    
23
DECLARE @BaseAmount DECIMAL(22,0) = 0
24
SELECT DISTINCT @BaseAmount = cu304.Amount
25
FROM dbo.PHRPA0003 AS pa3 WITH (NOLOCK)
26
LEFT JOIN dbo.PHRPA0004 AS pa4 WITH (NOLOCK)
27
	ON pa3.EmployeeID = pa4.EmployeeID
28
	AND pa4.StartDate <= @EndDate AND pa4.EndDate >= @EndDate
29
LEFT JOIN dbo.PHRPYCU0304 AS cu304 WITH (NOLOCK)
30
	ON (CASE WHEN cu304.PayrollType = '*' THEN pa3.PayrollType ELSE cu304.PayrollType END) = pa3.PayrollType
31
	AND (CASE WHEN cu304.PayrollArea = '*' THEN pa3.PayrollArea ELSE cu304.PayrollArea END) = pa3.PayrollArea
32
	AND (CASE WHEN cu304.PayrollClass = '*' THEN pa3.PayrollClass ELSE cu304.PayrollClass END) = pa3.PayrollClass
33
	AND (CASE WHEN cu304.PayrollGrade = '*' THEN pa3.PayrollGrade ELSE cu304.PayrollGrade END) = pa3.PayrollGrade
34
	AND pa4.WageType = cu304.WageType
35
WHERE pa3.EmployeeID = @EmployeeID
36
	AND pa3.StartDate <= @EndDate AND pa3.EndDate >= @EndDate
37
	AND pa4.WageType IN (SELECT DISTINCT * FROM dbo.Split(',', @WTBase))
38

    
39
SELECT DISTINCT @BaseAmount = @BaseAmount + cu304.Amount
40
FROM dbo.PHRPA0003 AS pa3 WITH (NOLOCK)
41
LEFT JOIN dbo.PHRPA0005 AS pa5 WITH (NOLOCK)
42
	ON pa3.EmployeeID = pa5.EmployeeID
43
	AND pa5.StartDate <= @EndDate AND pa5.EndDate >= @EndDate
44
LEFT JOIN dbo.PHRPYCU0304 AS cu304 WITH (NOLOCK)
45
	ON (CASE WHEN cu304.PayrollType = '*' THEN pa3.PayrollType ELSE cu304.PayrollType END) = pa3.PayrollType
46
	AND (CASE WHEN cu304.PayrollArea = '*' THEN pa3.PayrollArea ELSE cu304.PayrollArea END) = pa3.PayrollArea
47
	AND (CASE WHEN cu304.PayrollClass = '*' THEN pa3.PayrollClass ELSE cu304.PayrollClass END) = pa3.PayrollClass
48
	AND (CASE WHEN cu304.PayrollGrade = '*' THEN pa3.PayrollGrade ELSE cu304.PayrollGrade END) = pa3.PayrollGrade
49
	AND pa5.WageType = cu304.WageType
50
WHERE pa3.EmployeeID = @EmployeeID
51
	AND pa3.StartDate <= @EndDate AND pa3.EndDate >= @EndDate
52
	AND pa5.WageType IN (SELECT DISTINCT * FROM dbo.Split(',', @WTBase))
53

    
54
DECLARE @TWSCALEMP TABLE
55
(
56
	EmployeeID VARCHAR(20)
57
	,WSType VARCHAR(20)
58
	,DateSpecified VARCHAR(20)
59
	,DayType VARCHAR(20)
60
	,Flag VARCHAR(20)
61
)
62

    
63
INSERT INTO @TWSCALEMP
64
SELECT DISTINCT pa25.EmployeeID, pa25.WorkScheduleType, wscal.DateSpecified, wscal.DayType, daytype.Flag
65
FROM dbo.PHRPA0025 AS pa25 WITH (NOLOCK)
66
LEFT JOIN dbo.PHRTMWSCAL AS wscal WITH (NOLOCK)
67
	ON wscal.WorkScheduleType = pa25.WorkScheduleType
68
LEFT JOIN dbo.PHRTMDAYTYP AS daytype
69
	ON wscal.DayType = daytype.DayType
70
WHERE EmployeeID = @EmployeeID
71
	AND pa25.StartDate <= @EndDate AND pa25.EndDate = @EndDate
72
	AND (wscal.DateSpecified BETWEEN @StartDate AND @EndDate)
73

    
74
DECLARE @TPA18 TABLE
75
(
76
	StartDate VARCHAR(20)
77
	,EndDate VARCHAR(20)
78
	,EmployeeID VARCHAR(20)
79
	,AbsenceType VARCHAR(20)
80
	,DateSpecified VARCHAR(20)
81
)
82

    
83
INSERT INTO @TPA18
84
SELECT DISTINCT pa18.StartDate, pa18.EndDate, pa18.EmployeeID, pa18.AbsenceType, wscal.DateSpecified
85
FROM dbo.PHRPA0018 AS pa18 WITH (NOLOCK)
86
LEFT JOIN @TWSCALEMP AS wscal
87
	ON wscal.DateSpecified BETWEEN pa18.StartDate AND pa18.EndDate
88
WHERE pa18.EmployeeID = @EmployeeID
89
	AND (pa18.StartDate BETWEEN @StartDate AND @EndDate OR pa18.EndDate BETWEEN @StartDate AND @EndDate)
90
	AND wscal.Flag <> 'FREE'
91
	
92
DECLARE @TPA19 TABLE
93
(
94
	StartDate VARCHAR(20)
95
	,EndDate VARCHAR(20)
96
	,EmployeeID VARCHAR(20)
97
	,AbsenceType VARCHAR(20)
98
	,DateSpecified VARCHAR(20)
99
)
100

    
101
INSERT INTO @TPA19
102
SELECT DISTINCT pa19.StartDate, pa19.EndDate, pa19.EmployeeID, pa19.AttendanceType, wscal.DateSpecified
103
FROM dbo.PHRPA0019 AS pa19 WITH (NOLOCK)
104
LEFT JOIN @TWSCALEMP AS wscal
105
	ON wscal.DateSpecified BETWEEN pa19.StartDate AND pa19.EndDate
106
WHERE pa19.EmployeeID = @EmployeeID
107
	AND (pa19.StartDate BETWEEN @StartDate AND @EndDate OR pa19.EndDate BETWEEN @StartDate AND @EndDate)
108
	AND wscal.Flag <> 'FREE'
109

    
110
DECLARE @TResult TABLE
111
(
112
	EmployeeID VARCHAR(20)
113
	,AbsType VARCHAR(50)
114
	,SumAbsence DECIMAL(18,0)
115
)
116

    
117
INSERT INTO @TResult
118
SELECT DISTINCT wscal.EmployeeID, 'mangkir' AS AbsType, COUNT(wscal.DateSpecified) AS mangkir
119
FROM @TWSCALEMP AS wscal
120
WHERE wscal.DateSpecified NOT IN ( SELECT DISTINCT DateSpecified FROM @TPA18)
121
	AND wscal.DateSpecified NOT IN ( SELECT DISTINCT DateSpecified FROM @TPA19)
122
	AND wscal.Flag <> 'FREE'
123
GROUP BY wscal.EmployeeID
124

    
125
INSERT INTO @TResult
126
SELECT DISTINCT EmployeeID, 'izin sakit' AS AbsType, COUNT([@TPA18].DateSpecified)
127
FROM @TPA18 
128
WHERE AbsenceType IN (SELECT DISTINCT * FROM dbo.Split(',', @AbsType))
129
GROUP BY EmployeeID
130

    
131
SELECT SUM(SumAbsence) AS SumAbsence , @BaseAmount AS AmountBase, SUM(SumAbsence) * @BaseAmount AS PotonganKehadiran
132
FROM @TResult
(2-2/6)