pears.NetOwnerRequirementsTempdeskList

CREATE PROCEDURE "pears"."NetOwnerRequirementsTempdeskList"( IN "pWebUserID" CHAR(20),IN "pType" CHAR(1) ) 
RESULT( "tempdeskid" CHAR(20),"tempdeskname" CHAR(100),"selected" CHAR(10) ) 
BEGIN
  DECLARE "CurrentDesk" CHAR(20);
  SET "CurrentDesk" = (SELECT top 1 "s"."tempdeskid" FROM "staff" AS "s" KEY JOIN "iqxnetuser" WHERE "iqxnetuserid" = "pWebUserID" ORDER BY "s"."staffid" ASC);
  SELECT "d"."tempdeskid",
    "d"."name" AS "tempdeskname",
    (IF "tempdeskid" = "CurrentDesk" THEN 'Selected' ELSE NULL endif) AS "Selected"
    FROM "tempdesk" AS "d"
    WHERE(("d"."desktype" IN( 'M','W' ) AND "pType" = 'C')
    OR("d"."desktype" = 'S' AND "pType" = 'S'))
    AND "d"."divisionid" = any(SELECT "divisionid" FROM "dashboardstaffdivisions"())
END /* DOC
2018-11-27 PC IW-758 provide list of tempdesks associated with a consultant
 
*/
/* TEST
update tempdesk set desktype='S' where tempdeskid  in ('test','test2');
update staff set tempdeskid='test' where staffid='test';
insert into deptmaintenance (departmentid,staffid) on existing update defaults off values ('~~','TEST');
select tempdeskid,tempdeskname,isnull(selected,'!') as A from NetOwnerRequirementsTempdeskList('test.owner','S' ) where tempdeskid in ('test','test2') order by tempdeskid;
expect tempdeskid=TEST,tempdeskname=test,A=Selected;
expect tempdeskid=TEST2,tempdeskname=test2,A=!;
expect EOF;
update tempdesk set desktype='M' where tempdeskid in ('test','test2');
update staff set tempdeskid='test2' where staffid='test';
select tempdeskid,tempdeskname,isnull(selected,'!') as A from NetOwnerRequirementsTempdeskList('test.owner','C' ) where tempdeskid in ('test','test2') order by tempdeskid;
expect tempdeskid=TEST,tempdeskname=test,A=!;
expect tempdeskid=TEST2,tempdeskname=test2,A=Selected;
update tempdesk set desktype='S' where tempdeskid  in ('test','test2');
update staff set tempdeskid='test2' where staffid='test';
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerRequirementsTempdeskList" IS 
{CREATE PROCEDURE pears."NetOwnerRequirementsTempdeskList"(IN pWebUserID CHAR(20),IN pType CHAR(1) )
RESULT(tempdeskid CHAR(20),tempdeskname CHAR(100),selected CHAR(10))
BEGIN
    DECLARE CurrentDesk CHAR(20);
    SET CurrentDesk=(SELECT top 1 s.tempdeskid FROM staff s KEY JOIN iqxnetuser WHERE iqxnetuserid=pWebUserID ORDER BY s.staffid);
 
	SELECT
        d.tempdeskid,
        d.name AS tempdeskname,
        (IF tempdeskid=CurrentDesk THEN 'Selected' ELSE NULL endif) AS Selected
    FROM 
        tempdesk d 
    WHERE 
        ((d.desktype IN ('M','W') AND pType='C')
        OR (d.desktype='S' AND pType='S'))
        AND d.divisionid IN (SELECT divisionid FROM dashboardstaffdivisions());
END
 
/* DOC
2018-11-27 PC IW-758 provide list of tempdesks associated with a consultant
 
*/
 
/* TEST
update tempdesk set desktype='S' where tempdeskid  in ('test','test2');
update staff set tempdeskid='test' where staffid='test';
insert into deptmaintenance (departmentid,staffid) on existing update defaults off values ('~~','TEST');
select tempdeskid,tempdeskname,isnull(selected,'!') as A from NetOwnerRequirementsTempdeskList('test.owner','S' ) where tempdeskid in ('test','test2') order by tempdeskid;
expect tempdeskid=TEST,tempdeskname=test,A=Selected;
expect tempdeskid=TEST2,tempdeskname=test2,A=!;
expect EOF;
update tempdesk set desktype='M' where tempdeskid in ('test','test2');
update staff set tempdeskid='test2' where staffid='test';
select tempdeskid,tempdeskname,isnull(selected,'!') as A from NetOwnerRequirementsTempdeskList('test.owner','C' ) where tempdeskid in ('test','test2') order by tempdeskid;
expect tempdeskid=TEST,tempdeskname=test,A=!;
expect tempdeskid=TEST2,tempdeskname=test2,A=Selected;
update tempdesk set desktype='S' where tempdeskid  in ('test','test2');
update staff set tempdeskid='test2' where staffid='test';
*/
}