pears.NetClientInvoiceList

CREATE PROCEDURE "pears"."NetClientInvoiceList"( IN "pWebUserID" CHAR(20),IN "pSlice" INTEGER DEFAULT 0,IN "pSliceSize" INTEGER DEFAULT 10,IN "pStartDate" CHAR(10) DEFAULT NULL,IN "pEndDate" CHAR(10) DEFAULT NULL,IN "pFilterRef" CHAR(20) DEFAULT '%' ) 
RESULT( "DocID" CHAR(20),"DocPath" CHAR(128),"PaymentDate" CHAR(10),"DocName" CHAR(50),"DocDescription" CHAR(100),"Amount" CHAR(15),"TSNo" CHAR(10),"OurRef" CHAR(10),"CompanyName" CHAR(60),"PersName" CHAR(60) ) 
// IQXWeb
BEGIN
  DECLARE "DocPathRoot" CHAR(254);
  DECLARE "dStartDate" DATE;
  DECLARE "dEndDate" DATE;
  DECLARE "x" INTEGER;
  DECLARE "y" INTEGER;
  DECLARE "BackSlash" CHAR(2);
  DECLARE LOCAL TEMPORARY TABLE "IDs"(
    "ID" CHAR(20) NULL,
    ) NOT transactional;
  INSERT INTO "IDs"( "ID" ) 
    SELECT DISTINCT "e"."companyid" FROM "iqxnetuserlink" AS "l" KEY JOIN "employment" AS "e" WHERE "l"."iqxnetuserid" = "pWebUserID";
  SET "BackSlash" = '\\';
  SET "DocPathRoot" = (SELECT top 1 "WPKLocalSwitchValue"('PDFINDB','Global','L',0,1))+"BackSlash";
  SET "dStartDate" = "isnull"("IQXNetStringToDate"("pStartDate"),"dateadd"("mm",-1,"now"()));
  SET "dEndDate" = "isnull"("IQXNetStringToDate"("pEndDate"),"dateadd"("mm",1,"now"()));
  SET "x" = "isnull"("pSliceSize",10);
  SET "y" = ("x"*"isnull"("pSlice",0))+1;
  IF(SELECT top 1 "isnull"("value",0) FROM "iqxnetswitch" WHERE "iqxnetswitchid" = 'ESCAPEPATHWAY') = 1 THEN
    SET "BackSlash" = '\\\\'
  END IF;
  SELECT top "x" START at "y" "DocumentID","Pathway","InvoiceDate","DocName","DocType","Amount","TSNo","RefNo","CompanyName","PersName"
    FROM(SELECT "d"."DocumentID",
        "string"("DocPathRoot","d"."accountcode","BackSlash","dateformat"("d"."documentdate",'yyyymmdd'),"BackSlash","d"."ourref",'.pdf') AS "Pathway",
        "dateformat"("d"."documentdate",'dd/mm/yyyy') AS "InvoiceDate",
        "string"("d"."ourref",'.pdf') AS "DocName",
        (IF "d"."amount" < 0 THEN 'Credit Note' ELSE "d"."documenttype" endif) AS "DocType",
        "d"."amount" AS "Amount",
        "t"."serialnumber" AS "TSNo",
        "d"."OurRef" AS "RefNo",
        "c"."name" AS "CompanyName",
        "p"."name" AS "PersName",
        "d"."documentdate"
        FROM "iqacdocument" AS "d" JOIN "temptimesheet" AS "t" ON "t"."temptimesheetid" = "d"."xrefid" AND "d"."xref" = 'T'
          KEY JOIN "placement" KEY JOIN "employment" KEY JOIN("person" AS "p","company" AS "c")
        WHERE "c"."companyid" = any(SELECT "ID" FROM "IDs")
        AND "d"."documentdate" BETWEEN "dStartDate" AND "dEndDate"
        AND "d"."documenttype" IN( 'Invoice','Credit Note' ) 
        AND("pFilterRef" = '' OR "d"."OurRef" LIKE '%'+"pFilterRef"+'%') UNION ALL
      SELECT "d"."DocumentID",
        "string"("DocPathRoot","d"."accountcode","BackSlash","dateformat"("d"."documentdate",'yyyymmdd'),"BackSlash","d"."ourref",'.pdf') AS "Pathway",
        "dateformat"("d"."documentdate",'dd/mm/yyyy') AS "InvoiceDate",
        "string"("d"."ourref",'.pdf') AS "DocName",
        "d"."DocumentType",
        "d"."amount" AS "Amount",
        NULL AS "TSNo",
        "d"."OurRef" AS "RefNo",
        "c"."name" AS "CompanyName",
        "p"."name" AS "PersName",
        "d"."documentdate"
        FROM "iqacdocument" AS "d" JOIN "placement" AS "pl" ON "pl"."placementid" = "d"."xrefid" AND "d"."xref" = 'P'
          KEY JOIN "employment" KEY JOIN("person" AS "p","company" AS "c")
        WHERE "c"."companyid" = any(SELECT "ID" FROM "IDs")
        AND "d"."documentdate" BETWEEN "dStartDate" AND "dEndDate"
        AND "d"."documenttype" IN( 'Invoice','Credit Note' ) 
        AND("pFilterRef" = '' OR "d"."OurRef" LIKE '%'+"pFilterRef"+'%')
        ORDER BY "documentdate" DESC) AS "docs"
END /* DOC
2016-11-07 PC create a list of invoices for a client
2016-11-23 PC add date search criteria
2017-06-29 PC modify to refer to pdf directory in general settings
2017-08-03 PC IW-267 make double or single backslashes in pathway switchable on EscapeBackslash
2017-09-07 ET added filtering by OurRef
2017-10-30 PC OP-5 RC2 where an amount is less than zero, then display as 'Credit Note' otherwise display document type
 
IW-80
 
Test:
invalid pWebUserID
valid pWebUserID, pSlice, pSliceSize, pStartDate and invalid pEndDate
valid pWebUserID, pSlice, pSliceSize, pStartDate, pEndDate inside range
valid pWebUserID, pSlice, pSliceSize, pStartDate, pEndDate outside range
valid pWebUserID, pSlice=2, pSliceSize, pStartDate=null, pEndDate=null
*/
/* 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 iqacdocument (documentid,AccountCode,XrefID,Xref,accountingdate,documentdate,duedate,documentclass,documenttype) on existing update values ('TEST1','TESTAC','TEST','P',dateformat(now(*),'yyyy-mm-dd'),dateformat(now(*),'yyyy-mm-dd'),dateformat(now(*),'yyyy-mm-dd'),'Invoice','Invoice');
insert into iqacdocument (documentid,AccountCode,XrefID,Xref,accountingdate,documentdate,duedate,documentclass,documenttype) on existing update values ('TEST2','TESTAC','TEST','P',dateformat(now(*),'yyyy-mm-dd'),dateformat(now(*),'yyyy-mm-dd'),dateformat(now(*),'yyyy-mm-dd'),'Invoice','Credit Note');
select * from NetClientInvoiceList('test.client',0,10,'zz',null);
expect EOF;
select * from NetClientInvoiceList('test.client',0,10,dateformat(dateadd(mm,-3,now(*)),'dd/mm/yyyy'),dateformat(dateadd(mm,-2,now(*)),'dd/mm/yyyy'));
expect EOF;
select * from NetClientInvoiceList('test.client',1,10,null,null);
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetClientInvoiceList" IS 
{CREATE PROCEDURE pears."NetClientInvoiceList"(IN pWebUserID CHAR(20),IN pSlice INTEGER DEFAULT 0,IN pSliceSize INTEGER DEFAULT 10,IN pStartDate CHAR(10) DEFAULT NULL,IN pEndDate CHAR(10) DEFAULT NULL,IN pFilterRef CHAR(20) DEFAULT '%')
RESULT(DocID CHAR(20),DocPath CHAR(128),PaymentDate CHAR(10),DocName CHAR(50),DocDescription CHAR(100),Amount CHAR(15),TSNo CHAR(10),OurRef CHAR(10),CompanyName CHAR(60),PersName CHAR(60))
// IQXWeb
BEGIN
  DECLARE DocPathRoot CHAR(254);
  DECLARE dStartDate DATE;
  DECLARE dEndDate DATE;
  DECLARE x INTEGER;
  DECLARE y INTEGER;
  DECLARE BackSlash CHAR(2);
  DECLARE LOCAL TEMPORARY TABLE IDs(
    ID CHAR(20) NULL,
    ) NOT transactional;
  INSERT INTO IDs( ID) 
    SELECT DISTINCT e.companyid FROM iqxnetuserlink AS l KEY JOIN employment AS e WHERE l.iqxnetuserid = pWebUserID;
  SET BackSlash='\\';
  SET DocPathRoot = (SELECT top 1 WPKLocalSwitchValue('PDFINDB','Global','L',0,1)) + BackSlash;
  SET dStartDate=isnull(IQXNetStringToDate(pStartDate),dateadd(mm,-1,now(*)));
  SET dEndDate=isnull(IQXNetStringToDate(pEndDate),dateadd(mm,1,now(*)));
  SET x=isnull(pSliceSize,10);
  SET y=(x*isnull(pSlice,0))+1;
  IF (SELECT top 1 isnull(VALUE,0) FROM iqxnetswitch WHERE iqxnetswitchid='ESCAPEPATHWAY') = 1 THEN 
    SET BackSlash='\\\\' 
  END IF;
  SELECT top x START at y DocumentID,Pathway,InvoiceDate,DocName,DocType,Amount,TSNo,RefNo,CompanyName,PersName FROM
  (SELECT   
    d.DocumentID,
    string(DocPathRoot,d.accountcode,BackSlash,dateformat(d.documentdate,'yyyymmdd'),BackSlash,d.ourref,'.pdf') AS Pathway,
    dateformat(d.documentdate,'dd/mm/yyyy') AS InvoiceDate,
    string(d.ourref,'.pdf') AS DocName,
    (IF d.amount < 0 THEN 'Credit Note' ELSE d.documenttype endif) AS DocType,
    d.amount AS Amount,
    t.serialnumber AS TSNo,
    d.OurRef AS RefNo,
    c.name AS CompanyName,
    p.name AS PersName,
    d.documentdate
    FROM iqacdocument d JOIN temptimesheet t ON t.temptimesheetid=d.xrefid AND d.xref='T'
        KEY JOIN placement KEY JOIN employment KEY JOIN (person p, company c) 
    WHERE c.companyid IN (SELECT ID FROM IDs)
        AND d.documentdate BETWEEN dStartDate AND dEndDate 
        AND d.documenttype IN ('Invoice','Credit Note')
        AND (pFilterRef = '' OR d.OurRef LIKE '%' + pFilterRef + '%')
   UNION ALL
   SELECT
    d.DocumentID,
    string(DocPathRoot,d.accountcode,BackSlash,dateformat(d.documentdate,'yyyymmdd'),BackSlash,d.ourref,'.pdf') AS Pathway,
    dateformat(d.documentdate,'dd/mm/yyyy') AS InvoiceDate,
    string(d.ourref,'.pdf') AS DocName,
    d.DocumentType,
    d.amount AS Amount,
    NULL AS TSNo,
    d.OurRef AS RefNo,
    c.name AS CompanyName,
    p.name AS PersName,
    d.documentdate
    FROM iqacdocument d JOIN placement pl ON pl.placementid=d.xrefid AND d.xref='P'
        KEY JOIN employment KEY JOIN (person p, company c)  
    WHERE c.companyid IN (SELECT ID FROM IDs)
        AND d.documentdate BETWEEN dStartDate AND dEndDate 
        AND d.documenttype IN ('Invoice','Credit Note')
        AND (pFilterRef = '' OR d.OurRef LIKE '%' + pFilterRef + '%')
    ORDER BY
        documentdate DESC) AS docs
END
 
/* DOC
2016-11-07 PC create a list of invoices for a client
2016-11-23 PC add date search criteria
2017-06-29 PC modify to refer to pdf directory in general settings
2017-08-03 PC IW-267 make double or single backslashes in pathway switchable on EscapeBackslash
2017-09-07 ET added filtering by OurRef
2017-10-30 PC OP-5 RC2 where an amount is less than zero, then display as 'Credit Note' otherwise display document type
 
IW-80
 
Test:
    invalid pWebUserID
    valid pWebUserID, pSlice, pSliceSize, pStartDate and invalid pEndDate
    valid pWebUserID, pSlice, pSliceSize, pStartDate, pEndDate inside range
    valid pWebUserID, pSlice, pSliceSize, pStartDate, pEndDate outside range
    valid pWebUserID, pSlice=2, pSliceSize, pStartDate=null, pEndDate=null
*/
 
/* 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 iqacdocument (documentid,AccountCode,XrefID,Xref,accountingdate,documentdate,duedate,documentclass,documenttype) on existing update values ('TEST1','TESTAC','TEST','P',dateformat(now(*),'yyyy-mm-dd'),dateformat(now(*),'yyyy-mm-dd'),dateformat(now(*),'yyyy-mm-dd'),'Invoice','Invoice');
insert into iqacdocument (documentid,AccountCode,XrefID,Xref,accountingdate,documentdate,duedate,documentclass,documenttype) on existing update values ('TEST2','TESTAC','TEST','P',dateformat(now(*),'yyyy-mm-dd'),dateformat(now(*),'yyyy-mm-dd'),dateformat(now(*),'yyyy-mm-dd'),'Invoice','Credit Note');
select * from NetClientInvoiceList('test.client',0,10,'zz',null);
expect EOF;
select * from NetClientInvoiceList('test.client',0,10,dateformat(dateadd(mm,-3,now(*)),'dd/mm/yyyy'),dateformat(dateadd(mm,-2,now(*)),'dd/mm/yyyy'));
expect EOF;
select * from NetClientInvoiceList('test.client',1,10,null,null);
*/
}