pears.NetClientInvoiceTotal

CREATE PROCEDURE "pears"."NetClientInvoiceTotal"( IN "pWebUserID" CHAR(20) ) 
RESULT( "pBalance" DECIMAL(10,2),"pSum" DECIMAL(10,2) ) 
BEGIN
  DECLARE "pClientCode" CHAR(20);
  DECLARE "pCompanyID" CHAR(20);
  SET "pClientCode" = (SELECT top 1 "c"."clientcode" FROM "iqxnetuserlink" AS "l" KEY JOIN "employment" AS "e" KEY JOIN "company" AS "c" WHERE "l"."iqxnetuserid" = "pWebUserID");
  SET "pCompanyID" = (SELECT top 1 "e"."companyid" FROM "iqxnetuserlink" AS "l" KEY JOIN "employment" AS "e" WHERE "l"."iqxnetuserid" = "pWebUserID");
  SELECT "amount",
    "sum"("IQacDocumentOutstandingAmount"("documentid",NULL)) AS "Amount"
    FROM "iqacdocument"
    WHERE "ledgerid" = 'Sales'
    AND(("accountcode" = "pClientCode") OR("xrefid2" = "pCompanyID")) AND "fullyallocated" IS NULL
END /* DOC
2016-11-30 PC Doc and Test
2017-08-03 PC possible more than one row in subquery resolved 
 
Inputs:
pWebUserID
 
Outputs:
pBalance
 
Tests:
invalid pWebUserID
valid pWebUserID
 
*/
/* TEST
call NetTestSetup('');
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.client','test.agency','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.client';
insert into iqacaccount (ledgerid,accountcode,name) on existing update values ('Sales','TESTAC','TestAccount');
insert into iqacdocument (documentid,LedgerID,AccountCode,XrefID,Xref,accountingdate,documentdate,duedate,documentclass,documenttype,amount) on existing update values ('TEST1','Sales','TESTAC','TEST','P',dateformat(now(*),'yyyy-mm-dd'),dateformat(now(*),'yyyy-mm-dd'),dateformat(now(*),'yyyy-mm-dd'),'Invoice','Invoice',50.00);
insert into iqacdocument (documentid,LedgerID,AccountCode,XrefID,Xref,accountingdate,documentdate,duedate,documentclass,documenttype,amount) on existing update values ('TEST2','Sales','TESTAC','TEST','P',dateformat(now(*),'yyyy-mm-dd'),dateformat(now(*),'yyyy-mm-dd'),dateformat(now(*),'yyyy-mm-dd'),'Invoice','Credit Note',10.00);
select isnull(pBalance,0) as A,isnull(pSum,0) as B from NetClientInvoiceTotal(null);
expect A=0,B=0;
select isnull(pBalance,0) as A,isnull(pSum,0) as B from NetClientInvoiceTotal('test.client');
expect A=60,B=60;
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetClientInvoiceTotal" IS 
{CREATE PROCEDURE pears."NetClientInvoiceTotal"(IN pWebUserID CHAR(20))
RESULT(pBalance DECIMAL(10,2),pSum DECIMAL(10,2))
BEGIN
  DECLARE pClientCode CHAR(20);
  DECLARE pCompanyID CHAR(20);
  SET pClientCode=(SELECT top 1 c.clientcode FROM iqxnetuserlink AS l KEY JOIN employment AS e KEY JOIN company AS c WHERE l.iqxnetuserid = pWebUserID);
  SET pCompanyID=(SELECT top 1 e.companyid FROM iqxnetuserlink AS l KEY JOIN employment AS e WHERE l.iqxnetuserid = pWebUserID);
  SELECT 
    amount,    
    SUM(IQacDocumentOutstandingAmount(documentid,NULL)) AS Amount
  FROM 
    iqacdocument 
  WHERE 
    ledgerid='Sales' 
    AND ((accountcode=pClientCode) OR (xrefid2=pCompanyID)) AND fullyallocated IS NULL;
END
 
/* DOC
2016-11-30 PC Doc and Test
2017-08-03 PC possible more than one row in subquery resolved 
 
Inputs:
    pWebUserID
 
Outputs:
    pBalance
 
Tests:
    invalid pWebUserID
    valid pWebUserID
 
*/
 
/* TEST
call NetTestSetup('');
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.client','test.agency','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.client';
insert into iqacaccount (ledgerid,accountcode,name) on existing update values ('Sales','TESTAC','TestAccount');
insert into iqacdocument (documentid,LedgerID,AccountCode,XrefID,Xref,accountingdate,documentdate,duedate,documentclass,documenttype,amount) on existing update values ('TEST1','Sales','TESTAC','TEST','P',dateformat(now(*),'yyyy-mm-dd'),dateformat(now(*),'yyyy-mm-dd'),dateformat(now(*),'yyyy-mm-dd'),'Invoice','Invoice',50.00);
insert into iqacdocument (documentid,LedgerID,AccountCode,XrefID,Xref,accountingdate,documentdate,duedate,documentclass,documenttype,amount) on existing update values ('TEST2','Sales','TESTAC','TEST','P',dateformat(now(*),'yyyy-mm-dd'),dateformat(now(*),'yyyy-mm-dd'),dateformat(now(*),'yyyy-mm-dd'),'Invoice','Credit Note',10.00);
select isnull(pBalance,0) as A,isnull(pSum,0) as B from NetClientInvoiceTotal(null);
expect A=0,B=0;
select isnull(pBalance,0) as A,isnull(pSum,0) as B from NetClientInvoiceTotal('test.client');
expect A=60,B=60;
*/
}