AcumaticaSQLQueriesAndFunctions/UtilityFunctions.sql

CREATE OR ALTER FUNCTION dbo.GetProjectOpenInvoices(@CompanyID int,
@ContractID int) RETURNS decimal(19,
6) AS
BEGIN
	DECLARE @OpenInvoices decimal(19,
	6);

select
	@OpenInvoices = SUM(case when AR.DocType = 'INV' then AR.CuryOrigDocAmt else 0 end) - SUM(case when AR.DocType = 'CRM' then AR.CuryOrigDocAmt else 0 end)
from
	Contract con
left join ARInvoice pmc on
	con.CompanyID = pmc.CompanyID
	and con.ContractID = pmc.ProjectID
left join ARRegister AR on
	pmc.CompanyID = AR.CompanyID
	and pmc.DocType = AR.DocType
	and pmc.RefNbr = AR.RefNbr
where
	con.CompanyID = @CompanyID
	and con.ContractID = @ContractID
	and ( AR.Status = 'N'
		or AR.Status = 'C') RETURN IsNull(@OpenInvoices,
	0.00)
END

GO

CREATE OR ALTER FUNCTION dbo.SumReleasedTransactions(
    @CompanyID int, 
    @projectID int,  
    @released int
)
RETURNS decimal(19,6)
AS
BEGIN
    DECLARE @BudAmount decimal(19,6);
    SELECT @BudAmount = ISNULL(SUM(p.amount),0)
    FROM PMTran p 
     join PMAccountGroup pg on pg.CompanyID = p.CompanyID and p.AccountGroupID = pg.GroupID 
    WHERE p.CompanyID = @CompanyID
    AND p.ProjectID = @projectID
    and p.Released = @released

    RETURN @BudAmount;
END;

GO

CREATE OR ALTER FUNCTION dbo.GetTransactions(
    @CompanyID int, 
    @projectID int, 
    @expense int, 
    @released int,
    @allocated int 
)
RETURNS decimal(19,6)
AS
BEGIN
    DECLARE @BudAmount decimal(19,6);
    SELECT @BudAmount = ISNULL(SUM(p.amount),0)
    FROM PMTran p 
     join PMAccountGroup pg on pg.CompanyID = p.CompanyID and p.AccountGroupID = pg.GroupID 
    WHERE p.CompanyID = @CompanyID
    AND p.ProjectID = @projectID
    and pg.IsExpense = @expense
    and p.Released = @released
    and P.Allocated = @allocated

    RETURN @BudAmount;
END;

GO

CREATE OR ALTER FUNCTION dbo.GetRevisedBudgetAmount(
    @CompanyID int, 
    @projectID int, 
    @type varchar(50) 
)
RETURNS decimal(19,6)
AS
BEGIN
    DECLARE @BudAmount decimal(19,6);

    -- Using a common table expression (CTE) to split the @type string into individual characters
    WITH TypeList AS (
        SELECT value AS [Type]
        FROM STRING_SPLIT(@type, ',')
    )

    SELECT @BudAmount = ISNULL(SUM(p.RevisedAmount),0)
    FROM PMBudget p 
    WHERE p.CompanyID = @CompanyID
    AND p.ProjectID = @projectID
    AND p.[Type] IN (SELECT [Type] FROM TypeList) -- Use the split values directly in the IN clause

    RETURN @BudAmount;
END;

GO

CREATE OR ALTER FUNCTION dbo.GetTopContractUserDefinedFieldDescription
(
    @companyID INT,
    @recordID UNIQUEIDENTIFIER,
    @attributeName NVARCHAR(128)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @result NVARCHAR(MAX);

    -- Static SQL query (no dynamic SQL)
    SELECT top 1 @result = cd.Description 
    FROM ContractKvExt c
    left join  CSAttributeDetail cd on cd.CompanyID = c.CompanyID and cd.AttributeID  = SUBSTRING(c.FieldName, 10, LEN(c.FieldName) - 9) and cd.ValueID = c.ValueString 
    WHERE c.FieldName = @attributeName 
      AND c.CompanyID = @companyID
      AND c.RecordID = @recordID;

    RETURN @result;
END;

GO

CREATE OR ALTER FUNCTION dbo.ProjectRental
(
    @companyID INT,
    @contractID INT
)
RETURNS BIT
AS
BEGIN
    -- Return TRUE if 'rental' exists in the description, FALSE otherwise
    IF EXISTS (
        SELECT 1
        FROM Contract c
        JOIN ContractKvExt e ON c.CompanyID = e.CompanyID AND c.NoteID = e.RecordID
        JOIN CSAttributeDetail d ON d.CompanyID = e.CompanyID AND e.ValueString = d.ValueID
        WHERE c.CompanyID = @companyID
        AND c.ContractID = @contractID
        AND e.FieldName = 'AttributeCATEGORY'
        AND CHARINDEX('rental', LOWER(d.Description)) > 0
    )
    BEGIN
        RETURN 1; -- TRUE
    END

    RETURN 0; -- FALSE
END;
GO

CREATE OR ALTER FUNCTION dbo.GetContractUserDefinedField
(
    @companyID INT,
    @recordID UNIQUEIDENTIFIER,
    @attributeName NVARCHAR(128),
    @isANumber BIT
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @result NVARCHAR(MAX);

    -- Static SQL query (no dynamic SQL)
    SELECT @result = ValueString 
    FROM ContractKvExt
    WHERE FieldName = @attributeName 
      AND CompanyID = @companyID
      AND RecordID = @recordID;

    -- If isANumber is true, check if ValueString is a valid number and clean it up
    IF @isANumber = 1
    BEGIN
        -- Remove any special characters except digits and '.'
        IF ISNUMERIC(@result) = 0
        BEGIN
            -- If not numeric, set to NULL
            SET @result = NULL;
        END
        ELSE
        BEGIN
            -- Strip out any characters except digits and '.'
            WHILE PATINDEX('%[^0-9.]%', @result) > 0
            BEGIN
                SET @result = STUFF(@result, PATINDEX('%[^0-9.]%', @result), 1, '');
            END
        END
    END

    RETURN @result;
END;

GO

CREATE or ALTER FUNCTION dbo.CountCustomerContracts(
    @beginDate DATETIME,
    @endDate DATETIME,
    @companyId INT,
    @customerId INT
)
RETURNS INT
AS
BEGIN
    DECLARE @contractCount INT;

    SELECT @contractCount = COUNT(*)
    FROM Contract
    WHERE CompanyId = @companyId
      AND CustomerId = @customerId
      AND CreatedDateTime <= @endDate
      AND CreatedDateTime >= @beginDate 

    RETURN @contractCount
END;

GO


CREATE OR ALTER FUNCTION dbo.GetBudgetAmount(
    @CompanyID int, 
    @projectID int, 
    @type varchar(50), -- Use varchar to handle multiple characters
    @targetDate DateTime
)
RETURNS decimal(19,6)
AS
BEGIN
    DECLARE @BudAmount decimal(19,6);

    -- Using a common table expression (CTE) to split the @type string into individual characters
    WITH TypeList AS (
        SELECT value AS [Type]
        FROM STRING_SPLIT(@type, ',')
    )

    SELECT @BudAmount = ISNULL(SUM(p.Amount),0)
    FROM PMBudget p 
    WHERE p.CompanyID = @CompanyID
    AND p.ProjectID = @projectID
    AND p.[Type] IN (SELECT [Type] FROM TypeList) -- Use the split values directly in the IN clause
    AND p.CreatedDateTime >= @targetDate; -- Filter by the current year

    RETURN @BudAmount;
END;

GO

CREATE OR Alter FUNCTION dbo.SumProjectInvoiceAmt(
    @companyId INT,
    @projectId INT,
    @beginDate DATETIME,
    @endDate DATETIME
)
RETURNS DECIMAL(18, 2)
AS
BEGIN
    DECLARE @totalOrigDocAmt DECIMAL(18, 2);

    SELECT @totalOrigDocAmt = ISNULL(SUM(i.CuryLineTotal), 0.00)
    FROM ARRegister a 
    join ARInvoice i on a.CompanyID = i.CompanyID and a.DocType = i.DocType and a.RefNbr = i.RefNbr 
    WHERE a.CompanyID = @companyId
      AND i.ProjectID = @projectId
      AND a.DocType = 'INV'
      AND a.DocDate >= @beginDate
      AND a.DocDate <= @endDate;

    RETURN @totalOrigDocAmt;
END;

GO


CREATE OR Alter FUNCTION dbo.SumCustomerInvoiceAmt(
    @companyId INT,
    @customerId INT,
    @beginDate DATETIME,
    @endDate DATETIME
)
RETURNS DECIMAL(18, 2)
AS
BEGIN
    DECLARE @totalOrigDocAmt DECIMAL(18, 2);

    SELECT @totalOrigDocAmt = ISNULL(SUM(i.CuryLineTotal), 0.00)
    FROM ARRegister a 
    join ARInvoice i on a.CompanyID = i.CompanyID and a.DocType = i.DocType and a.RefNbr = i.RefNbr 
    WHERE a.CompanyID = @companyId
      AND a.CustomerID = @customerId
      AND a.DocType = 'INV'
      AND a.DocDate >= @beginDate
      AND a.DocDate <= @endDate;

    RETURN @totalOrigDocAmt;
END;

GO


CREATE OR ALTER FUNCTION dbo.SumProjectInvoicePayments(
    @companyId INT,
    @projectId INT,
    @startDate DATETIME,
    @endDate DATETIME
)
RETURNS DECIMAL(18, 2)
AS
BEGIN
    DECLARE @totalCuryAdjdAmt DECIMAL(18, 2);

    SELECT @totalCuryAdjdAmt = ISNULL(SUM(a.CuryAdjdAmt),0.00)
    FROM ARAdjust a 
    join ARInvoice i on a.CompanyID = i.CompanyID and a.AdjdRefNbr = i.RefNbr and a.AdjdDocType = i.DocType 
    WHERE a.CompanyID = @companyId
     AND a.AdjdDocType = 'INV'
      AND a.AdjgDocType = 'PMT'
      AND i.ProjectID = @projectID
      AND a.AdjgDocDate >= @startDate
      AND a.AdjgDocDate <= @endDate;

    RETURN @totalCuryAdjdAmt;
END;

GO


CREATE OR ALTER FUNCTION dbo.SumCustomerInvoicePayments(
    @companyId INT,
    @customerId INT,
    @startDate DATETIME,
    @endDate DATETIME
)
RETURNS DECIMAL(18, 2)
AS
BEGIN
    DECLARE @totalCuryAdjdAmt DECIMAL(18, 2);

    SELECT @totalCuryAdjdAmt = ISNULL(SUM(a.CuryAdjdAmt),0.00)
    FROM ARAdjust a 
    WHERE a.CompanyID = @companyId
     AND a.AdjdDocType = 'INV'
      AND a.AdjgDocType = 'PMT'
      AND a.CustomerID = @customerId
      AND a.AdjgDocDate >= @startDate
      AND a.AdjgDocDate <= @endDate;

    RETURN @totalCuryAdjdAmt;
END;

GO 
CREATE OR ALTER FUNCTION dbo.GetFirstDayOfPeriod(
    @inputDate DATETIME,
    @periodType VARCHAR(10)
)
RETURNS DATETIME
AS
BEGIN
    DECLARE @firstDay DATETIME;

    IF @periodType = 'WEEK'
    BEGIN
        -- Get the first day of the week (assuming Sunday as the first day)
        SET @firstDay = DATEADD(day, -DATEDIFF(day, 0, @inputDate) % 7, CAST(@inputDate AS DATE));
    END
    ELSE IF @periodType = 'MONTH'
    BEGIN
        -- Get the first day of the month
        SET @firstDay = DATEFROMPARTS(YEAR(@inputDate), MONTH(@inputDate), 1);
    END
    ELSE IF @periodType = 'QUARTER'
    BEGIN
        DECLARE @inputMonth INT;
        DECLARE @quarterStartMonth INT;

        SET @inputMonth = MONTH(@inputDate);

        -- Determine the starting month of the quarter for the given date
        IF @inputMonth IN (1, 2, 3)
            SET @quarterStartMonth = 1;
        ELSE IF @inputMonth IN (4, 5, 6)
            SET @quarterStartMonth = 4;
        ELSE IF @inputMonth IN (7, 8, 9)
            SET @quarterStartMonth = 7;
        ELSE
            SET @quarterStartMonth = 10;

        -- Construct the first day of the quarter for the given date
        SET @firstDay = DATEFROMPARTS(YEAR(@inputDate), @quarterStartMonth, 1);
    END
    ELSE IF @periodType = 'YEAR'
    BEGIN
        -- Get the first day of the year
        SET @firstDay = DATEFROMPARTS(YEAR(@inputDate), 1, 1);
    END

    RETURN CAST(@firstDay AS DATETIME);
END;

GO

CREATE or ALTER FUNCTION dbo.GetCustomerRevenueByProjectDate(@CompanyID int, @CustomerID int, @targetDate DATETIME)
RETURNS decimal(19,6)
AS
BEGIN
    DECLARE @RevAmount decimal(19,6);

SELECT @RevAmount = SUM(p.Amount)
FROM PMTran p 
join Contract c on p.CompanyID = c.CompanyID and p.ProjectID = c.ContractID
join PMAccountGroup pg on pg.CompanyID = p.CompanyID and p.AccountGroupID = pg.GroupID 
WHERE p.CompanyID = @CompanyID
AND c.CustomerID = @CustomerID
and pg.[Type] = 'I'
and pg.GroupID <> 25
and c.CreatedDateTime >= @targetDate;
    RETURN @RevAmount;
END;

GO

CREATE or ALTER FUNCTION dbo.GetCustomerRevenueByProjectBilled(@CompanyID int, @CustomerID int, @targetDate DATETIME)
RETURNS decimal(19,6)
AS
BEGIN
    DECLARE @RevAmount decimal(19,6);

SELECT @RevAmount = SUM(p.Amount)
FROM PMTran p 
join Contract c on p.CompanyID = c.CompanyID and p.ProjectID = c.ContractID
join PMAccountGroup pg on pg.CompanyID = p.CompanyID and p.AccountGroupID = pg.GroupID 
WHERE p.CompanyID = @CompanyID
AND c.CustomerID = @CustomerID
and c.ContractID in (SELECT a.ProjectID from ARInvoice a where a.CompanyID= @CompanyID and a.DocType = 'INV' and a.InvoiceDate > @targetDate group by a.ProjectID)
and pg.[Type] = 'I'
and pg.GroupID <> 25
and c.CreatedDateTime >= @targetDate;
    RETURN @RevAmount;
END;

GO

CREATE or ALTER FUNCTION dbo.GetCustomerRevenueByProjectPaid(@CompanyID int, @CustomerID int, @targetDate DATETIME)
RETURNS decimal(19,6)
AS
BEGIN
    DECLARE @RevAmount decimal(19,6);

SELECT @RevAmount = SUM(p.Amount)
FROM PMTran p 
join Contract c on p.CompanyID = c.CompanyID and p.ProjectID = c.ContractID
join PMAccountGroup pg on pg.CompanyID = p.CompanyID and p.AccountGroupID = pg.GroupID 
WHERE p.CompanyID = @CompanyID
AND c.CustomerID = @CustomerID
and c.ContractID in ( 
    SELECT i.ProjectID 
    FROM ARAdjust a 
    join ARInvoice i on a.CompanyID = i.CompanyID and a.AdjdDocType = i.DocType and a.AdjdRefNbr = i.RefNbr 
    WHERE a.CompanyID = @CompanyID
    AND a.AdjdDocType = 'INV'
    AND a.AdjgDocType = 'PMT'
    AND a.AdjgDocDate >=  @targetDate
    group by i.ProjectID )
and pg.[Type] = 'I'
and pg.GroupID <> 25
and c.CreatedDateTime >= @targetDate;
    RETURN @RevAmount;
END;