singularity/CreateOrUpdateInvoiceSummary.sql at main · shuxton/singularity · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
CREATE PROCEDURE CreateOrUpdateInvoice
    @InvoiceType NVARCHAR(20), -- 'Sales', 'Credit', or 'Debit'
    @TenantId NVARCHAR(50),
    @UniqueIdentifier NVARCHAR(50),
    @CreatedDate DATETIME,
    @ModifiedDate DATETIME,
    @NetInvoiceAmount DECIMAL(38, 18),
    @SumOfInvoiceLineNetAmount DECIMAL(38, 18),
    @TotalAmountWithoutVAT DECIMAL(38, 18),
    @TotalVATAmount DECIMAL(38, 18),
    @CurrencyCode NVARCHAR(10),
    @TotalAmountWithVAT DECIMAL(38, 18),
    @PaidAmount DECIMAL(38, 18),
    @PayableAmount DECIMAL(38, 18),
    @AdvanceAmountWithoutVat DECIMAL(38, 18),
    @AdvanceVat DECIMAL(38, 18)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @TableName NVARCHAR(50);
	DECLARE @Query NVARCHAR(MAX);
    -- Determine the table name based on the invoice type
    IF @InvoiceType = 'Sales'
        SET @TableName = 'SalesInvoiceSummary';
    ELSE IF @InvoiceType = 'Credit'
        SET @TableName = 'CreditNoteSummary';
    ELSE IF @InvoiceType = 'Debit'
        SET @TableName = 'DebitInvoiceSummary';
    ELSE
        RAISERROR('Invalid InvoiceType', 16, 1);

    -- Check if a record with the UniqueIdentifier exists in the specified table
    IF EXISTS (SELECT 1 FROM InvoiceSummary WHERE UniqueIdentifier = @UniqueIdentifier)
    BEGIN
	SET @Query =  N'
            UPDATE '+ @TableName + '
            SET
                TenantId = @TenantId,
                CreatedDate = @CreatedDate,
                ModifiedDate = @ModifiedDate,
                NetInvoiceAmount = @NetInvoiceAmount,
                SumOfInvoiceLineNetAmount = @SumOfInvoiceLineNetAmount,
                TotalAmountWithoutVAT = @TotalAmountWithoutVAT,
                TotalVATAmount = @TotalVATAmount,
                CurrencyCode = @CurrencyCode,
                TotalAmountWithVAT = @TotalAmountWithVAT,
                PaidAmount = @PaidAmount,
                PayableAmount = @PayableAmount,
                AdvanceAmountWithoutVat = @AdvanceAmountWithoutVat,
                AdvanceVat = @AdvanceVat
            WHERE UniqueIdentifier = @UniqueIdentifier';
        -- Update existing record in the appropriate table
        EXEC sp_executesql @Query,
          N'@TenantId NVARCHAR(50), @CreatedDate DATETIME, @ModifiedDate DATETIME, @NetInvoiceAmount DECIMAL(38, 18), @SumOfInvoiceLineNetAmount DECIMAL(38, 18), @TotalAmountWithoutVAT DECIMAL(38, 18), @TotalVATAmount DECIMAL(38, 18), @CurrencyCode NVARCHAR(10), @TotalAmountWithVAT DECIMAL(38, 18), @PaidAmount DECIMAL(38, 18), @PayableAmount DECIMAL(38, 18), @AdvanceAmountWithoutVat DECIMAL(38, 18), @AdvanceVat DECIMAL(38, 18), @UniqueIdentifier NVARCHAR(50)',
            @TenantId, @CreatedDate, @ModifiedDate, @NetInvoiceAmount, @SumOfInvoiceLineNetAmount, @TotalAmountWithoutVAT, @TotalVATAmount, @CurrencyCode, @TotalAmountWithVAT, @PaidAmount, @PayableAmount, @AdvanceAmountWithoutVat, @AdvanceVat, @UniqueIdentifier;
    END
    ELSE
    BEGIN
        -- Insert new record into InvoiceSummary
		SET @Query= N'
        INSERT INTO '+@TableName+'
        (
            TenantId,
            UniqueIdentifier,
            CreatedDate,
            ModifiedDate,
            NetInvoiceAmount,
            SumOfInvoiceLineNetAmount,
            TotalAmountWithoutVAT,
            TotalVATAmount,
            CurrencyCode,
            TotalAmountWithVAT,
            PaidAmount,
            PayableAmount,
            AdvanceAmountWithoutVat,
            AdvanceVat
        ) VALUES (
            @TenantId,
            @UniqueIdentifier,
            @CreatedDate,
            @ModifiedDate,
            @NetInvoiceAmount,
            @SumOfInvoiceLineNetAmount,
            @TotalAmountWithoutVAT,
            @TotalVATAmount,
            @CurrencyCode,
            @TotalAmountWithVAT,
            @PaidAmount,
            @PayableAmount,
            @AdvanceAmountWithoutVat,
            @AdvanceVat
            )';

        EXEC sp_executesql @Query,
        N'@TenantId NVARCHAR(50), @CreatedDate DATETIME, @ModifiedDate DATETIME, @NetInvoiceAmount DECIMAL(38, 18), @SumOfInvoiceLineNetAmount DECIMAL(38, 18), @TotalAmountWithoutVAT DECIMAL(38, 18), @TotalVATAmount DECIMAL(38, 18), @CurrencyCode NVARCHAR(10), @TotalAmountWithVAT DECIMAL(38, 18), @PaidAmount DECIMAL(38, 18), @PayableAmount DECIMAL(38, 18), @AdvanceAmountWithoutVat DECIMAL(38, 18), @AdvanceVat DECIMAL(38, 18), @UniqueIdentifier NVARCHAR(50)',
		@TenantId, @CreatedDate, @ModifiedDate, @NetInvoiceAmount, @SumOfInvoiceLineNetAmount, @TotalAmountWithoutVAT, @TotalVATAmount, @CurrencyCode, @TotalAmountWithVAT, @PaidAmount, @PayableAmount, @AdvanceAmountWithoutVat, @AdvanceVat, @UniqueIdentifier;
END
END