pears.NetAgencyCandidateProgress

CREATE PROCEDURE "pears"."NetAgencyCandidateProgress"( IN "pWebUserID" CHAR(20),IN "pPersonID" CHAR(20) ) 
RESULT( "dFromDate" DATE,"tFromTime" TIME,"CompanyName" CHAR(100),"JobDescription" CHAR(100),"sFrom" CHAR(20),"sTo" CHAR(20),"State" CHAR(50) ) 
BEGIN
  SELECT "vacancy"."startdate" AS "d1",CAST('00:00' AS TIME) AS "t1","company"."name","vacancy"."position","dateformat"("vacancy"."startdate",'dd/mm/yyyy'),"dateformat"("vacancy"."finishdate",'dd/mm/yyyy'),"status"."name"
    FROM "company" KEY JOIN "employment" KEY JOIN "vacancy" KEY JOIN "progress" JOIN "status" ON "status"."type" = 'R' AND "status"."status" = "progress"."status"
    WHERE "progress"."personid" = "pPersonID" UNION ALL
  SELECT "p"."shiftdate" AS "d1","p"."timefrom" AS "t1","company"."name","string"("vacancy"."position",' - ',"p"."description"),
    "string"("dateformat"("p"."shiftdate",'dd/mm/yyyy'),' ',"dateformat"("p"."timefrom",'hh:nn')),
    "string"("dateformat"((IF "p"."TimeFrom" < "p"."TimeTo" THEN "p"."ShiftDate" ELSE "p"."ShiftDate"+1 endif),
    'dd/mm/yyyy'),' ',"dateformat"("p"."timeto",'hh:nn')),
    "status"."name"
    FROM "company" KEY JOIN "employment" KEY JOIN "vacancy" KEY JOIN "tempshiftplan" AS "p" KEY JOIN "tempshiftprogress" JOIN "status" ON "status"."type" = 'S' AND "status"."status" = "tempshiftprogress"."status"
    WHERE "tempshiftprogress"."personid" = "pPersonID"
    ORDER BY 1 ASC,2 ASC
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetAgencyCandidateProgress" IS 
{CREATE PROCEDURE pears."NetAgencyCandidateProgress"( IN "pWebUserID" CHAR(20),IN "pPersonID" CHAR(20) ) 
RESULT( "dFromDate" DATE,"tFromTime" TIME,"CompanyName" CHAR(100),"JobDescription" CHAR(100),"sFrom" CHAR(20),"sTo" CHAR(20),"State" CHAR(50) ) 
BEGIN
  SELECT "vacancy"."startdate" AS "d1",CAST('00:00' AS TIME) AS "t1","company"."name","vacancy"."position","dateformat"("vacancy"."startdate",'dd/mm/yyyy'),"dateformat"("vacancy"."finishdate",'dd/mm/yyyy'),"status"."name"
    FROM "company" KEY JOIN "employment" KEY JOIN "vacancy" KEY JOIN "progress" JOIN "status" ON "status"."type" = 'R' AND "status"."status" = "progress"."status"
    WHERE "progress"."personid" = "pPersonID" UNION ALL
  SELECT "p"."shiftdate" AS "d1","p"."timefrom" AS "t1","company"."name","string"("vacancy"."position",' - ',"p"."description"),
    "string"("dateformat"("p"."shiftdate",'dd/mm/yyyy'),' ',"dateformat"("p"."timefrom",'hh:nn')),
    "string"("dateformat"((IF "p"."TimeFrom" < "p"."TimeTo" THEN "p"."ShiftDate" ELSE "p"."ShiftDate"+1 endif),
    'dd/mm/yyyy'),' ',"dateformat"("p"."timeto",'hh:nn')),
    "status"."name"
    FROM "company" KEY JOIN "employment" KEY JOIN "vacancy" KEY JOIN "tempshiftplan" AS "p" KEY JOIN "tempshiftprogress" JOIN "status" ON "status"."type" = 'S' AND "status"."status" = "tempshiftprogress"."status"
    WHERE "tempshiftprogress"."personid" = "pPersonID"
    ORDER BY 1 ASC,2 ASC
END
}