pears.NetActionDialogGetDetails

CREATE PROCEDURE "pears"."NetActionDialogGetDetails"( IN "pWebUserID" CHAR(20),IN "pTheID" CHAR(20),IN "pActionDialogType" CHAR(50) ) 
RESULT( 
  "name" CHAR(255),
  "notes" long VARCHAR,
  "othernotes" long VARCHAR,
  "addr1" CHAR(255),
  "addr2" CHAR(255),
  "addr3" CHAR(255),
  "town" CHAR(255),
  "county" CHAR(255),
  "postcode" CHAR(255),
  "statusName" CHAR(255),
  "alert" CHAR(255),
  "warning" CHAR(255),
  "headline" CHAR(255),
  "adtype" CHAR(255) ) 
// IQXWeb
BEGIN
  CASE "pActionDialogType"
  WHEN 'person' THEN
    SELECT "name",
      "notes",
      "othernotes",
      "addr1",
      "addr2",
      "addr3",
      "town",
      "county",
      "postcode",
      (SELECT "status"."name" FROM "status" WHERE "status"."type" = 'p' AND "status"."status" = "person"."status") AS "statusName",
      "alert",
      "PersonWarning" AS "warning",
      (SELECT "PersonHeadline"("pTheID")) AS "headline",
      'Candidate' AS "adtype"
      FROM "Person"
      WHERE "Person"."personid" = "pTheID"
  WHEN 'company' THEN
    SELECT "name",
      "notes",
      "extranotes",
      "addr1",
      "addr2",
      "addr3",
      "town",
      "county",
      "postcode",
      (SELECT "cs"."name" FROM "companystatus" AS "cs" WHERE "cs"."companystatusid" = "company"."status") AS "statusName",
      "alert",
      "CompanyWarning" AS "warning",
      (SELECT "CompanyHeadline"("pTheID")) AS "headline",
      'Company' AS "adtype"
      FROM "Company"
      WHERE "company"."companyID" = "pTheID"
  WHEN 'contact' THEN
    SELECT "p"."name",
      "e"."note",
      "c"."notes",
      "c"."addr1",
      "c"."addr2",
      "c"."addr3",
      "c"."town",
      "c"."county",
      "c"."postcode",
      (SELECT "status"."name" FROM "status" WHERE "status"."type" = 'p' AND "status"."status" = "p"."status") AS "statusName",
      "p"."alert",
      "p"."PersonWarning" AS "warning",
      (SELECT "PersonHeadline"("pTheID")) AS "headline",
      'Contact' AS "adtype"
      FROM "employment" AS "e"
        KEY JOIN "company" AS "c"
        ,"employment" AS "e"
        KEY JOIN "person" AS "p"
      WHERE "e"."employmentid" = "pTheID"
  ELSE
    SELECT '','','','','','','','','','','','' FROM "dummy"
  END CASE
END /* DOC
2018-04-30 IW-460 PC Client and Contact Details need to show CLIENT state  
2018-04-30 IW-474 PC Address needs to be Client address, Notes client or employment 
 
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetActionDialogGetDetails" IS 
{CREATE PROCEDURE pears."NetActionDialogGetDetails"(pWebUserID CHAR(20),IN pTheID CHAR(20), IN pActionDialogType CHAR(50))
RESULT(
    name CHAR(255),
	notes long VARCHAR,
    othernotes long VARCHAR,
    addr1 CHAR(255),
    addr2 CHAR(255),
    addr3 CHAR(255),
    town CHAR(255),
    county CHAR(255),
    postcode CHAR(255),
    statusName CHAR(255),
    alert CHAR(255),
    warning CHAR(255),
    headline CHAR(255),
    adtype CHAR(255)
)
// IQXWeb
BEGIN
    CASE pActionDialogType
        WHEN 'person' THEN
            SELECT
                name,
                notes,
                othernotes,
                addr1,
                addr2,
                addr3,
                town,
                county,
                postcode,
                (SELECT STATUS.name FROM STATUS WHERE STATUS.type = 'p' AND STATUS.status = person.status) AS statusName,
                alert,
                PersonWarning AS warning,
                (SELECT PersonHeadline(pTheID)) AS headline,
                'Candidate' AS adtype
            FROM
                Person
            WHERE
                Person.personid=pTheID;
        WHEN 'company' THEN
            SELECT
                name,
                notes,
                extranotes,
                addr1,
                addr2,
                addr3,
                town,
                county,
                postcode,
                (SELECT cs.name FROM companystatus cs WHERE cs.companystatusid = company.status) AS statusName,
                alert,
                CompanyWarning AS warning,
                (SELECT CompanyHeadline(pTheID)) AS headline,
                'Company' AS adtype
            FROM
                Company
            WHERE
                company.companyID=pTheID;
        WHEN 'contact' THEN
            SELECT
                p.name,
                e.note,
                c.notes,
                c.addr1,
                c.addr2,
                c.addr3,
                c.town,
                c.county,
                c.postcode,
                (SELECT STATUS.name FROM STATUS WHERE STATUS.type = 'p' AND STATUS.status = p.status) AS statusName,
                p.alert,
                p.PersonWarning AS warning,
                (SELECT PersonHeadline(pTheID)) AS headline,
                'Contact' AS adtype
            FROM
                employment e 
                    KEY JOIN company c,
                employment e
                    KEY JOIN person p
            WHERE
                e.employmentid=pTheID;
        ELSE
            SELECT '','','','','','','','','','','','' FROM dummy;
    END CASE;
END
 
/* DOC
2018-04-30 IW-460 PC Client and Contact Details need to show CLIENT state  
2018-04-30 IW-474 PC Address needs to be Client address, Notes client or employment 
 
*/
}