|
1
|
ALTER PROCEDURE CBKTTMTOPYPOTGAJIAMOUNT
|
|
2
|
(
|
|
3
|
@EmployeeID VARCHAR(20)
|
|
4
|
,@StartDate VARCHAR(20)
|
|
5
|
,@EndDate VARCHAR(20)
|
|
6
|
)
|
|
7
|
AS
|
|
8
|
|
|
9
|
--DECLARE @EmployeeID VARCHAR(20) = '10000194';
|
|
10
|
--DECLARE @StartDate VARCHAR(20) = '20221001';
|
|
11
|
--DECLARE @EndDate VARCHAR(20) = '20221031';
|
|
12
|
|
|
13
|
DECLARE @Bulan DECIMAL(18,0)
|
|
14
|
DECLARE @Hari DECIMAL(18,0)
|
|
15
|
|
|
16
|
SELECT @Bulan = DATEDIFF(MONTH, DATEADD(YEAR, 0 , MIN(pa18.StartDate)), MAX(pa18.EndDate)) - (CASE WHEN DATEADD(MONTH,DATEDIFF(MONTH, DATEADD(YEAR, 0 , MIN(pa18.StartDate)), MAX(pa18.EndDate)), DATEADD(YEAR, 0 , MIN(pa18.StartDate))) > MAX(pa18.EndDate) THEN 1 ELSE 0 END)
|
|
17
|
,@Hari = DATEDIFF(DAY, DATEADD(MONTH, @Bulan , MIN(pa18.StartDate)), MAX(pa18.EndDate)) - (CASE WHEN DATEADD(DAY, DATEDIFF(DAY, DATEADD(MONTH, @Bulan , MIN(pa18.StartDate)), MAX(pa18.EndDate)), DATEADD(MONTH, @Bulan , MIN(pa18.StartDate))) > MAX(pa18.EndDate) THEN 1 ELSE 0 END)
|
|
18
|
--pa18.EmployeeID, pa18.StartDate, pa18.EndDate
|
|
19
|
FROM PHRPA0018 AS pa18
|
|
20
|
WHERE pa18.AbsenceType = '05'
|
|
21
|
AND pa18.EmployeeID = @EmployeeID
|
|
22
|
AND pa18.EndDate <= @EndDate
|
|
23
|
GROUP BY pa18.EmployeeID
|
|
24
|
SELECT @Bulan, @Hari
|
|
25
|
|
|
26
|
DECLARE @SumMD4Amount DECIMAL(22,0)
|
|
27
|
SELECT DISTINCT @SumMD4Amount = CAST(REPLACE(ISNULL(dbo.GetPEN(Amount), '0'), ',', '.') AS DECIMAL(22,0))
|
|
28
|
FROM dbo.PHRPA0004 AS pa4
|
|
29
|
WHERE pa4.EmployeeID = @EmployeeID
|
|
30
|
AND pa4.StartDate <= @EndDate AND pa4.EndDate >= @EndDate
|
|
31
|
|
|
32
|
DECLARE @SumMD5Amount DECIMAL(22,0) = 0
|
|
33
|
SELECT DISTINCT @SumMD5Amount = CAST(REPLACE(ISNULL(dbo.GetPEN(Amount), '0'), ',', '.') AS DECIMAL(22,0))
|
|
34
|
FROM dbo.PHRPA0005 AS pa5
|
|
35
|
WHERE pa5.EmployeeID = @EmployeeID
|
|
36
|
AND pa5.StartDate <= @EndDate AND pa5.EndDate >= @EndDate
|
|
37
|
|
|
38
|
DECLARE @SumAmount DECIMAL(22,0) = (@SumMD4Amount + @SumMD5Amount)
|
|
39
|
DECLARE @PotGaji DECIMAL(22,0)
|
|
40
|
IF(@Bulan >= 12)
|
|
41
|
BEGIN
|
|
42
|
IF(@Hari > 0)
|
|
43
|
BEGIN
|
|
44
|
SET @PotGaji = @SumAmount/2
|
|
45
|
END
|
|
46
|
ELSE
|
|
47
|
BEGIN
|
|
48
|
SET @PotGaji = 0
|
|
49
|
END
|
|
50
|
END
|
|
51
|
ELSE
|
|
52
|
BEGIN
|
|
53
|
SET @PotGaji = 0
|
|
54
|
END
|
|
55
|
|
|
56
|
SELECT @PotGaji AS PotGaji
|
|
57
|
|
|
58
|
--/* rumus perhitungan bulan hari
|
|
59
|
----SELECT dbo.GetDateDiffTahunBulanHari('20211110', '20221001')
|
|
60
|
----DECLARE @FromDate VARCHAR(20) = '20211110'
|
|
61
|
----DECLARE @ToDate VARCHAR(20) = '20221001'
|
|
62
|
----DECLARE @Months INT, @Days INT, @tmpFromDate DATETIME
|
|
63
|
----SET @tmpFromDate = DATEADD(YEAR, 0 , @FromDate) SELECT @tmpFromDate
|
|
64
|
----SET @Months = DATEDIFF(MONTH, DATEADD(YEAR, 0 , @FromDate), @ToDate) - (CASE WHEN DATEADD(MONTH,DATEDIFF(MONTH, DATEADD(YEAR, 0 , @FromDate), @ToDate), DATEADD(YEAR, 0 , @FromDate)) > @ToDate THEN 1 ELSE 0 END)
|
|
65
|
----SELECT @Months
|
|
66
|
----SET @tmpFromDate = DATEADD(MONTH, @Months , @FromDate) SELECT @tmpFromDate
|
|
67
|
----SET @Days = DATEDIFF(DAY, DATEADD(MONTH, @Months , @FromDate), @ToDate) - (CASE WHEN DATEADD(DAY, DATEDIFF(DAY, DATEADD(MONTH, @Months , @FromDate), @ToDate), DATEADD(MONTH, @Months , @FromDate)) > @ToDate THEN 1 ELSE 0 END)
|
|
68
|
----SELECT @Days
|
|
69
|
--*/
|
