pears.NetAssignmentDatasheet

CREATE PROCEDURE "pears"."NetAssignmentDatasheet"( IN "pWebUserID" CHAR(20),IN "pLineID" CHAR(20),IN "pPersonID" CHAR(20) DEFAULT NULL ) 
RESULT( "RowTitle" CHAR(255),"RowContent" CHAR(500),"SortOrder" CHAR(2) ) 
BEGIN
  // IQXWeb
  DECLARE "HoursOfWorkExtraText" CHAR(100);
  DECLARE "ConfirmRateExtraText" CHAR(100);
  DECLARE "BranchContactDetails" CHAR(255);
  DECLARE "PayNote" CHAR(500);
  DECLARE LOCAL TEMPORARY TABLE "DSet"(
    "Column1" CHAR(500) NULL,
    "Column2" CHAR(500) NULL,
    "Column3" CHAR(500) NULL,
    "Column4" CHAR(500) NULL,
    "Column5" CHAR(500) NULL,
    "Column6" CHAR(500) NULL,
    "Column7" CHAR(500) NULL,
    "Column8" CHAR(500) NULL,
    "Column9" CHAR(500) NULL,
    "Column10" CHAR(500) NULL,
    "Column11" CHAR(500) NULL,
    "Column12" CHAR(500) NULL,
    "Column13" CHAR(500) NULL,
    "Column14" CHAR(500) NULL,
    "Column15" CHAR(500) NULL,
    "Column16" CHAR(500) NULL,
    "Column17" CHAR(500) NULL,
    "Column18" CHAR(500) NULL,
    "Column19" CHAR(500) NULL,
    "Column20" CHAR(500) NULL,
    "Column21" CHAR(500) NULL,
    "Column22" CHAR(500) NULL,
    "Column23" CHAR(500) NULL,
    ) NOT transactional;
  SET "pPersonID" = "NetCandidateValidPersonID"("pWebUserID","pPersonID");
  IF EXISTS(SELECT * FROM "sysobjects" WHERE "name" = 'SoftNetAssignmentDatasheet') THEN
    SELECT "RowTitle","RowContent","SortOrder" FROM "SoftNetAssignmentDatasheet"("pWebUserID","pLineID")
  ELSE
    SET "HoursOfWorkExtraText" = 'Standard day. Please discuss with your agent.';
    SET "ConfirmRateExtraText" = 'Please confirm rates with your consultant';
    SET "PayNote" = 'Please remember that it is your responsibility to ensure that you get a timesheet signed for the hours you have worked.';
    SET "BranchContactDetails" = "replace"((SELECT "list"("string"("name",': ',"addressetc5",'<br/>') ORDER BY "name" ASC) FROM "agencydetails" WHERE "addressetc5" IS NOT NULL),',','');
    INSERT INTO "Dset"( "Column1","Column2","Column3","Column4","Column5","Column6","Column7","Column8","Column9","Column10","Column11","Column12","Column13","Column14","Column15","Column16","Column17","Column18","Column19","Column20","Column21","Column22","Column23" ) (
      SELECT "p"."name"+'<br/>'+"p"."addr1"+(IF "p"."addr2" <> '' THEN '<br/>'+"p"."addr2" endif)+(IF "p"."addr3" <> '' THEN '<br/>'+"p"."addr3" endif)+(IF "p"."town" <> '' THEN '<br/>'+"p"."town" endif)+(IF "p"."postcode" <> '' THEN '<br/>'+"p"."postcode" endif) AS "CandidateAddress", --1
        "c"."name" AS "CompanyName", --2
        "isnull"("v"."sitecontact",'Reception / Security') AS "ReportTo", --3
        "dateformat"("isnull"("s"."shiftdate","e"."startdate","v"."startdate"),'dd/mm/yyyy') AS "StartDate", --4
        "dateformat"("isnull"((IF "s"."timeto" < "s"."timefrom" THEN "s"."shiftdate"+1 ELSE "s"."shiftdate" endif),"e"."leavedate","v"."finishdate"),'dd/mm/yyyy') AS "Duration", --5
        "v"."position" AS "Placement", --6
        "v"."sitename" AS "LocationOfWorkName", --7
        (IF "isnull"("v"."addr1",'') <> '' THEN("v"."addr1"+(IF "v"."addr2" <> '' THEN '<br/>'+"v"."addr2" endif)+(IF "v"."addr3" <> '' THEN '<br/>'+"v"."addr3" endif)+(IF "v"."town" <> '' THEN '<br/>'+"v"."town" endif)+(IF "v"."postcode" <> '' THEN '<br/>'+"v"."postcode" endif))
        ELSE("c"."addr1"+(IF "c"."addr2" <> '' THEN ', '+"c"."addr2" endif)
          +(IF "c"."addr3" <> '' THEN ', '+"c"."addr3" endif)
          +(IF "c"."town" <> '' THEN ', '+"c"."town" endif)
          +(IF "c"."postcode" <> '' THEN ', '+"c"."postcode" endif))
        endif) AS "PlaceOfWorkAddress", --8
        ("c"."addr1"+(IF "c"."addr2" <> '' THEN ', '+"c"."addr2" endif)
        +(IF "c"."addr3" <> '' THEN ', '+"c"."addr3" endif)
        +(IF "c"."town" <> '' THEN ', '+"c"."town" endif)
        +(IF "c"."postcode" <> '' THEN ', '+"c"."postcode" endif)) AS "MainOfficeAddress", --9
        CAST("dateformat"("s"."TimeFrom",'hh:nn') AS CHAR)+' - '+CAST("dateformat"("s"."TimeTo",'hh:nn') AS CHAR) AS "HoursOfWork", --10
        "isnull"('','None specified') AS "Experience", --11
        "string"('£',(IF "isnull"(CAST("NetRate" AS CHAR(10)),'0.00') = '0.00' THEN "ConfirmRateExtraText" ELSE CAST("NetRate" AS CHAR(10)) endif),'<br/>',"PayNote") AS "PayRate", --12
        (IF(SELECT top 1 "payfrequency" FROM "pay_employee" WHERE "personid" = "p"."personid" ORDER BY "WhenEntered" DESC) = '52' THEN 'Weekly' ELSE 'Monthly' endif) AS "IntervalsOfPay", --13
        "st"."name"+' ('+"st"."email"+')' AS "ConsultantContact", --14
        (SELECT top 1 "placementid" FROM "placement" AS "pla" KEY JOIN "employment" AS "emp" WHERE "pla"."vacancyid" = "v"."vacancyid" AND "emp"."personid" = "p"."personid" ORDER BY "pla"."placedate" DESC) AS "PlaID", --15
        (IF "substring"("GetMinPayRate"("PlaID","MinWage"),5) = '' THEN 0 ELSE "substring"("GetMinPayRate"("PlaID","MinWage"),5) endif) AS "MinRate", --16
        CAST("isnull"("MinRate","substring"("GetMinPayRateVacancy"("v"."vacancyid","MinWage"),2),0) AS DECIMAL(10,2)) AS "NetRate", --17
        "getminwage"("p"."personid","weekcontaining"("shiftdate")) AS "MinWage", --18
        '', --19
        '', --20
        '', --21
        '', --22
        '' --23
        FROM "tempshift" AS "s"
          KEY JOIN "vacancy" AS "v"
          KEY JOIN "employment" AS "e"
          KEY JOIN "company" AS "c"
          ,"tempshift" AS "s"
          LEFT OUTER JOIN "person" AS "p"
          ,"vacancy" AS "v"
          KEY JOIN "staff" AS "st"
        WHERE "s"."tempshiftid" = "pLineID");
    INSERT INTO "Dset"( "Column1","Column2","Column3","Column4","Column5","Column6","Column7","Column8","Column9","Column10","Column11","Column12","Column13","Column14","Column15","Column16","Column17","Column18","Column19","Column20","Column21","Column22","Column23" ) (
      SELECT "pe"."name"+'<br/>'+"pe"."addr1"+(IF "pe"."addr2" <> '' THEN '<br/>'+"pe"."addr2" endif)+(IF "pe"."addr3" <> '' THEN '<br/>'+"pe"."addr3" endif)+(IF "pe"."town" <> '' THEN '<br/>'+"pe"."town" endif)+(IF "pe"."postcode" <> '' THEN '<br/>'+"pe"."postcode" endif) AS "CandidateAddress", --1
        "c"."name" AS "CompanyName", --2
        "isnull"("v"."sitecontact",'Reception / Security') AS "ReportTo", --3
        "dateformat"("isnull"("e"."startdate","v"."startdate"),'dd/mm/yyyy') AS "StartDate", --4
        "dateformat"("isnull"("e"."leavedate","v"."finishdate"),'dd/mm/yyyy') AS "Duration", --5
        "e"."position" AS "Placement", --6
        "v"."sitename" AS "LocationOfWorkName", --7
        (IF "isnull"("v"."addr1",'') <> '' THEN("v"."addr1"+(IF "v"."addr2" <> '' THEN '<br/>'+"v"."addr2" endif)+(IF "v"."addr3" <> '' THEN '<br/>'+"v"."addr3" endif)+(IF "v"."town" <> '' THEN '<br/>'+"v"."town" endif)+(IF "v"."postcode" <> '' THEN '<br/>'+"v"."postcode" endif))
        ELSE("c"."addr1"+(IF "c"."addr2" <> '' THEN ', '+"c"."addr2" endif)
          +(IF "c"."addr3" <> '' THEN ', '+"c"."addr3" endif)
          +(IF "c"."town" <> '' THEN ', '+"c"."town" endif)
          +(IF "c"."postcode" <> '' THEN ', '+"c"."postcode" endif))
        endif) AS "PlaceOfWorkAddress", --8
        ("c"."addr1"+(IF "c"."addr2" <> '' THEN ', '+"c"."addr2" endif)
        +(IF "c"."addr3" <> '' THEN ', '+"c"."addr3" endif)
        +(IF "c"."town" <> '' THEN ', '+"c"."town" endif)
        +(IF "c"."postcode" <> '' THEN ', '+"c"."postcode" endif)) AS "MainOfficeAddress", --9
        'Normal Work Pattern: '+(IF "isnull"("pl"."workmonday","v"."workmonday") = '1' THEN 'Mon, ' endif)
        +(IF "isnull"("pl"."worktuesday","v"."worktuesday") = '1' THEN 'Tue, ' endif)
        +(IF "isnull"("pl"."workwednesday","v"."workwednesday") = '1' THEN 'Wed, ' endif)
        +(IF "isnull"("pl"."workthursday","v"."workthursday") = '1' THEN 'Thu, ' endif)
        +(IF "isnull"("pl"."workFriday","v"."workFriday") = '1' THEN 'Fri, ' endif)
        +(IF "isnull"("pl"."worksaturday","v"."worksaturday") = '1' THEN 'Sat, ' endif)
        +(IF "isnull"("pl"."worksunday","v"."worksunday") = '1' THEN 'Sun' endif)
        +'<br/>'
        +(IF CAST("pl"."workStartTime" AS CHAR) = '0' AND CAST("v"."workStartTime" AS CHAR) = '0' THEN ''
        ELSE 'Normal Start Time: '+(IF "pl"."workStartTime" <> '00:00' THEN CAST("dateformat"("pl"."workStartTime",'hh:nn') AS CHAR)
          ELSE CAST("dateformat"("v"."workStartTime",'hh:nn') AS CHAR)
          endif)
        endif)
        +'<br/>'
        +'Normal Daily Hours: '+(IF "isnull"("pl"."worknormalhours",'0') <> '0' THEN CAST("pl"."WorkNormalHours" AS CHAR) ELSE "isnull"(CAST("v"."WorkNormalHours" AS CHAR),"HoursOfWorkExtraText") endif) AS "HoursOfWork", --10
        "isnull"('','None specified') AS "Experience", --11
        "string"('£',(IF "isnull"(CAST("NetRate" AS CHAR(10)),'0.00') = '0.00' THEN "ConfirmRateExtraText" ELSE CAST("NetRate" AS CHAR(10)) endif),'<br/>',"PayNote") AS "PayRate", --12
        (IF(SELECT top 1 "payfrequency" FROM "pay_employee" WHERE "personid" = "pe"."personid" ORDER BY "whenentered" DESC) = '52' THEN 'Weekly'
        ELSE 'Monthly'
        endif) AS "IntervalsOfPay", --13
        "st"."name"+' ('+"st"."email"+')' AS "ConsultantContact", --14
        '' AS "PlaID", --15
        (IF "substring"("GetMinPayRate"("pl"."placementid","MinWage"),5) = '' THEN "MinWage" ELSE "substring"("GetMinPayRate"("pl"."placementid","MinWage"),5) endif) AS "MinRate", --16
        CAST("isnull"(CAST("isnull"("MinRate","substring"("GetMinPayRateVacancy"("v"."vacancyid","MinWage"),2),"e"."salary") AS CHAR),0) AS DECIMAL(10,2)) AS "NetRate", --17
        "getminwage"("pe"."personid","weekcontaining"("e"."startdate")) AS "MinWage", --18
        '', --19
        '', --20
        '', --21
        '', --22
        '' --23
        FROM "employment" AS "e"
          KEY JOIN "person" AS "pe"
          ,"employment" AS "e"
          KEY JOIN "company" AS "c"
          ,"employment" AS "e"
          KEY JOIN "placement" AS "pl"
          KEY JOIN "vacancy" AS "v"
          KEY JOIN "staff" AS "st"
        WHERE "e"."employmentid" = "pLineID");
    SELECT 'Name and address of Temporary Worker:' AS "RowTitle","Column1",'A' AS "OrdCol" FROM "DSet" UNION
    SELECT 'Name of the Client hirer:' AS "RowTitle","Column2",'B' AS "OrdCol" FROM "DSet" UNION
    SELECT 'Person to report to on arrival' AS "RowTitle","Column3",'C' AS "OrdCol" FROM "DSet" UNION
    SELECT 'Start of assignment:' AS "RowTitle","Column4",'D' AS "OrdCol" FROM "DSet" UNION
    SELECT 'Likely duration of the assignment (subject to change) up to: ' AS "RowTitle","Column5",'E' AS "OrdCol" FROM "DSet" UNION
    SELECT 'The type of work:' AS "RowTitle","Column6",'F' AS "OrdCol" FROM "DSet" UNION
    SELECT 'Hours of work: (check regularly for changes or cancellations)' AS "RowTitle","Column10",'G' AS "OrdCol" FROM "DSet" UNION
    SELECT 'Location of Work:' AS "RowTitle","Column8",'H' AS "OrdCol" FROM "DSet" UNION
    SELECT 'Starting Pay Rate:' AS "RowTitle","Column12",'I' AS "OrdCol" FROM "DSet" UNION
    SELECT 'Intervals of payment: (unless agreed otherwise)' AS "RowTitle","Column13",'J' AS "OrdCol" FROM "DSet" UNION
    SELECT 'Your recruitment consultant:' AS "RowTitle","Column14",'K' AS "OrdCol" FROM "DSet" UNION
    SELECT 'Contact Numbers:' AS "RowTitle","BranchContactDetails",'L' AS "OrdCol" FROM "DSet"
      ORDER BY "OrdCol" ASC
  END IF
END /* DOC
2018-11-06 IW-619 PC create Standard Soft routine. If SoftNetAssignemtDatasheet() exists, logic will defer to it, otherwise will use embedded logic.
2018-11-09 PC add multiple user handler, and standardise output
*/
/* TEST
update vacancy set staffid='test' where vacancyid='test';
select * from NetAssignmentDatasheet('test.candidate','test',null);
expect RowTitle=No*,RowContent=No*;
update vacancy set staffid=null where vacancyid='test';
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetAssignmentDatasheet" IS 
{CREATE PROCEDURE pears."NetAssignmentDatasheet"( IN "pWebUserID" CHAR(20),IN "pLineID" CHAR(20),IN pPersonID CHAR(20) DEFAULT NULL )
RESULT(RowTitle CHAR(255),RowContent CHAR(500),SortOrder CHAR(2)) 
BEGIN
  // IQXWeb
 
DECLARE "HoursOfWorkExtraText" CHAR(100);
DECLARE "ConfirmRateExtraText" CHAR(100);
DECLARE BranchContactDetails CHAR(255);
DECLARE PayNote CHAR(500);
DECLARE LOCAL TEMPORARY TABLE DSet (
Column1 CHAR(500) NULL,
Column2 CHAR(500) NULL,
Column3 CHAR(500) NULL,
Column4 CHAR(500) NULL,
Column5 CHAR(500) NULL,
Column6 CHAR(500) NULL,
Column7 CHAR(500) NULL,
Column8 CHAR(500) NULL,
Column9 CHAR(500) NULL,
Column10 CHAR(500) NULL,
Column11 CHAR(500) NULL,
Column12 CHAR(500) NULL,
Column13 CHAR(500) NULL,
Column14 CHAR(500) NULL,
Column15 CHAR(500) NULL,
Column16 CHAR(500) NULL,
Column17 CHAR(500) NULL,
Column18 CHAR(500) NULL,
Column19 CHAR(500) NULL,
Column20 CHAR(500) NULL,
Column21 CHAR(500) NULL,
Column22 CHAR(500) NULL,
Column23 CHAR(500) NULL
) NOT transactional;
 
SET pPersonID=NetCandidateValidPersonID(pWebUserID,pPersonID);
 
IF EXISTS(SELECT * FROM sysobjects WHERE name = 'SoftNetAssignmentDatasheet') THEN
    SELECT RowTitle,RowContent,SortOrder FROM SoftNetAssignmentDatasheet(pWebUserID,pLineID)
ELSE 
 
SET "HoursOfWorkExtraText" = 'Standard day. Please discuss with your agent.';
SET "ConfirmRateExtraText" = 'Please confirm rates with your consultant';
SET PayNote = 'Please remember that it is your responsibility to ensure that you get a timesheet signed for the hours you have worked.';
SET BranchContactDetails = REPLACE((SELECT list(string(name,': ',addressetc5,'<br/>') ORDER BY name ASC) FROM agencydetails WHERE addressetc5 IS NOT NULL),',','');
 
INSERT INTO Dset (Column1,Column2,Column3,Column4,Column5,Column6,Column7,Column8,Column9,Column10,Column11,Column12,Column13,Column14,Column15,Column16,Column17,Column18,Column19,Column20,Column21,Column22,Column23)
(SELECT 
    "p"."name" + '<br/>'+ "p"."addr1"+(IF "p"."addr2" <> '' THEN '<br/>'+"p"."addr2" endif)+(IF "p"."addr3" <> '' THEN '<br/>'+"p"."addr3" endif)+(IF "p"."town" <> '' THEN '<br/>'+"p"."town" endif)+(IF "p"."postcode" <> '' THEN '<br/>'+"p"."postcode" endif) AS "CandidateAddress", --1
    "c"."name" AS "CompanyName",    --2
    "isnull"("v"."sitecontact",'Reception / Security') AS "ReportTo",   --3
    "dateformat"("isnull"("s"."shiftdate","e"."startdate","v"."startdate"),'dd/mm/yyyy') AS "StartDate",    --4
    "dateformat"("isnull"((IF "s"."timeto" < "s"."timefrom" THEN "s"."shiftdate"+1 ELSE "s"."shiftdate" endif),"e"."leavedate","v"."finishdate"),'dd/mm/yyyy') AS "Duration",   --5
    "v"."position" AS "Placement",  --6
    "v"."sitename" AS "LocationOfWorkName", --7
    (IF "isnull"("v"."addr1",'') <> '' THEN("v"."addr1"+(IF "v"."addr2" <> '' THEN '<br/>'+"v"."addr2" endif)+(IF "v"."addr3" <> '' THEN '<br/>'+"v"."addr3" endif)+(IF "v"."town" <> '' THEN '<br/>'+"v"."town" endif)+(IF "v"."postcode" <> '' THEN '<br/>'+"v"."postcode" endif))
    ELSE("c"."addr1"+(IF "c"."addr2" <> '' THEN ', '+"c"."addr2" endif)
      +(IF "c"."addr3" <> '' THEN ', '+"c"."addr3" endif)
      +(IF "c"."town" <> '' THEN ', '+"c"."town" endif)
      +(IF "c"."postcode" <> '' THEN ', '+"c"."postcode" endif))
    endif) AS "PlaceOfWorkAddress", --8
    ("c"."addr1"+(IF "c"."addr2" <> '' THEN ', '+"c"."addr2" endif)
    +(IF "c"."addr3" <> '' THEN ', '+"c"."addr3" endif)
    +(IF "c"."town" <> '' THEN ', '+"c"."town" endif)
    +(IF "c"."postcode" <> '' THEN ', '+"c"."postcode" endif)) AS "MainOfficeAddress",  --9
    CAST("dateformat"("s"."TimeFrom",'hh:nn') AS CHAR)+' - '+CAST("dateformat"("s"."TimeTo",'hh:nn') AS CHAR) AS "HoursOfWork", --10
    "isnull"('','None specified') AS "Experience",  --11
    string('£',(IF "isnull"(CAST("NetRate" AS CHAR(10)),'0.00') = '0.00' THEN "ConfirmRateExtraText" ELSE CAST("NetRate" AS CHAR(10)) endif),'<br/>',PayNote) AS "PayRate", --12
    (IF(SELECT top 1 "payfrequency" FROM "pay_employee" WHERE "personid" = "p"."personid" ORDER BY WhenEntered DESC) = '52' THEN 'Weekly' ELSE 'Monthly' endif) AS "IntervalsOfPay",    --13
    "st"."name"+' ('+"st"."email"+')' AS "ConsultantContact",   --14
    (SELECT top 1 "placementid" FROM "placement" AS "pla" KEY JOIN "employment" AS "emp" WHERE "pla"."vacancyid" = "v"."vacancyid" AND "emp"."personid" = "p"."personid" ORDER BY pla.placedate DESC) AS "PlaID", --15
    (IF "substring"("GetMinPayRate"("PlaID","MinWage"),5) = '' THEN 0 ELSE "substring"("GetMinPayRate"("PlaID","MinWage"),5) endif) AS "MinRate",    --16
    CAST("isnull"("MinRate","substring"("GetMinPayRateVacancy"("v"."vacancyid","MinWage"),2),0) AS DECIMAL(10,2)) AS "NetRate",  --17
    getminwage(p.personid,weekcontaining(shiftdate)) AS MinWage, --18
    '', --19
    '', --20
    '', --21
    '', --22
    ''  --23
    FROM 
        "tempshift" AS "s"
            KEY JOIN "vacancy" AS "v"
            KEY JOIN "employment" AS "e"
            KEY JOIN "company" AS "c",
        "tempshift" AS "s" 
            LEFT OUTER JOIN "person" AS "p",
            "vacancy" AS "v" 
            KEY JOIN "staff" AS "st"
    WHERE 
        "s"."tempshiftid" = "pLineID"); 
 
  INSERT INTO Dset (Column1,Column2,Column3,Column4,Column5,Column6,Column7,Column8,Column9,Column10,Column11,Column12,Column13,Column14,Column15,Column16,Column17,Column18,Column19,Column20,Column21,Column22,Column23)
  (SELECT 
    "pe"."name"  + '<br/>'+ "pe"."addr1"+(IF "pe"."addr2" <> '' THEN '<br/>'+"pe"."addr2" endif)+(IF "pe"."addr3" <> '' THEN '<br/>'+"pe"."addr3" endif)+(IF "pe"."town" <> '' THEN '<br/>'+"pe"."town" endif)+(IF "pe"."postcode" <> '' THEN '<br/>'+"pe"."postcode" endif) AS "CandidateAddress", --1
    "c"."name" AS "CompanyName", --2
    "isnull"("v"."sitecontact",'Reception / Security') AS "ReportTo", --3
    "dateformat"("isnull"("e"."startdate","v"."startdate"),'dd/mm/yyyy') AS "StartDate", --4
    "dateformat"("isnull"("e"."leavedate","v"."finishdate"),'dd/mm/yyyy') AS "Duration", --5
    "e"."position" AS "Placement", --6
    "v"."sitename" AS "LocationOfWorkName", --7
    (IF "isnull"("v"."addr1",'') <> '' THEN("v"."addr1"+(IF "v"."addr2" <> '' THEN '<br/>'+"v"."addr2" endif)+(IF "v"."addr3" <> '' THEN '<br/>'+"v"."addr3" endif)+(IF "v"."town" <> '' THEN '<br/>'+"v"."town" endif)+(IF "v"."postcode" <> '' THEN '<br/>'+"v"."postcode" endif))
    ELSE("c"."addr1"+(IF "c"."addr2" <> '' THEN ', '+"c"."addr2" endif)
      +(IF "c"."addr3" <> '' THEN ', '+"c"."addr3" endif)
      +(IF "c"."town" <> '' THEN ', '+"c"."town" endif)
      +(IF "c"."postcode" <> '' THEN ', '+"c"."postcode" endif))
    endif) AS "PlaceOfWorkAddress", --8
    ("c"."addr1"+(IF "c"."addr2" <> '' THEN ', '+"c"."addr2" endif)
    +(IF "c"."addr3" <> '' THEN ', '+"c"."addr3" endif)
    +(IF "c"."town" <> '' THEN ', '+"c"."town" endif)
    +(IF "c"."postcode" <> '' THEN ', '+"c"."postcode" endif)) AS "MainOfficeAddress", --9
    'Normal Work Pattern: '+(IF "isnull"("pl"."workmonday","v"."workmonday") = '1' THEN 'Mon, ' endif)
    +(IF "isnull"("pl"."worktuesday","v"."worktuesday") = '1' THEN 'Tue, ' endif)
    +(IF "isnull"("pl"."workwednesday","v"."workwednesday") = '1' THEN 'Wed, ' endif)
    +(IF "isnull"("pl"."workthursday","v"."workthursday") = '1' THEN 'Thu, ' endif)
    +(IF "isnull"("pl"."workFriday","v"."workFriday") = '1' THEN 'Fri, ' endif)
    +(IF "isnull"("pl"."worksaturday","v"."worksaturday") = '1' THEN 'Sat, ' endif)
    +(IF "isnull"("pl"."worksunday","v"."worksunday") = '1' THEN 'Sun' endif)
    +'<br/>'
    +(IF CAST("pl"."workStartTime" AS CHAR) = '0' AND CAST("v"."workStartTime" AS CHAR) = '0' THEN ''
    ELSE 'Normal Start Time: '+(IF "pl"."workStartTime" <> '00:00' THEN CAST("dateformat"("pl"."workStartTime",'hh:nn') AS CHAR)
      ELSE CAST("dateformat"("v"."workStartTime",'hh:nn') AS CHAR)
      endif)
    endif)
    +'<br/>'
    +'Normal Daily Hours: '+(IF "isnull"("pl"."worknormalhours",'0') <> '0' THEN CAST("pl"."WorkNormalHours" AS CHAR) ELSE "isnull"(CAST("v"."WorkNormalHours" AS CHAR),"HoursOfWorkExtraText") endif) AS "HoursOfWork", --10
    "isnull"('','None specified') AS "Experience", --11
    string('£',(IF "isnull"(CAST("NetRate" AS CHAR(10)),'0.00') = '0.00' THEN "ConfirmRateExtraText" ELSE CAST("NetRate" AS CHAR(10)) endif),'<br/>',PayNote) AS "PayRate", --12
    (IF(SELECT top 1 "payfrequency" FROM "pay_employee" WHERE "personid" = "pe"."personid" ORDER BY whenentered DESC) = '52' THEN 'Weekly'
    ELSE 'Monthly'
    endif) AS "IntervalsOfPay", --13
    "st"."name"+' ('+"st"."email"+')' AS "ConsultantContact", --14
    '' AS "PlaID", --15
    (IF "substring"("GetMinPayRate"("pl"."placementid","MinWage"),5) = '' THEN "MinWage" ELSE "substring"("GetMinPayRate"("pl"."placementid","MinWage"),5) endif) AS "MinRate", --16
    CAST("isnull"(CAST("isnull"("MinRate","substring"("GetMinPayRateVacancy"("v"."vacancyid","MinWage"),2),"e"."salary") AS CHAR),0) AS DECIMAL(10,2)) AS "NetRate", --17
    getminwage(pe.personid,weekcontaining(e.startdate)) AS MinWage, --18
    '', --19
    '', --20
    '', --21
    '', --22
    ''  --23
    FROM 
        "employment" AS "e" 
            KEY JOIN "person" AS "pe",
        "employment" AS "e" 
            KEY JOIN "company" AS "c",
        "employment" AS "e" 
            KEY JOIN "placement" AS "pl"
            KEY JOIN "vacancy" AS "v"
            KEY JOIN "staff" AS "st"
    WHERE 
        "e"."employmentid" = "pLineID");
 
    SELECT 'Name and address of Temporary Worker:' AS RowTitle,Column1,'A' AS OrdCol FROM DSet
        UNION SELECT 'Name of the Client hirer:' AS RowTitle,Column2,'B' AS OrdCol FROM DSet
        UNION SELECT 'Person to report to on arrival' AS RowTitle,Column3,'C' AS OrdCol FROM DSet
        UNION SELECT 'Start of assignment:' AS RowTitle,Column4,'D' AS OrdCol FROM DSet
        UNION SELECT 'Likely duration of the assignment (subject to change) up to: ' AS RowTitle,Column5,'E' AS OrdCol FROM DSet
        UNION SELECT 'The type of work:' AS RowTitle,Column6,'F' AS OrdCol FROM DSet
        UNION SELECT 'Hours of work: (check regularly for changes or cancellations)' AS RowTitle,Column10,'G' AS OrdCol FROM DSet
        UNION SELECT 'Location of Work:' AS RowTitle,Column8,'H' AS OrdCol FROM DSet
        UNION SELECT 'Starting Pay Rate:' AS RowTitle,Column12,'I' AS OrdCol FROM DSet
        UNION SELECT 'Intervals of payment: (unless agreed otherwise)' AS RowTitle,Column13,'J' AS OrdCol FROM DSet
        UNION SELECT 'Your recruitment consultant:' AS RowTitle,Column14,'K' AS OrdCol FROM DSet
        UNION SELECT 'Contact Numbers:' AS RowTitle,BranchContactDetails,'L' AS OrdCol FROM DSet
    ORDER BY 
        OrdCol ASC
 
END IF;
 
END
 
/* DOC
2018-11-06 IW-619 PC create Standard Soft routine. If SoftNetAssignemtDatasheet() exists, logic will defer to it, otherwise will use embedded logic.
2018-11-09 PC add multiple user handler, and standardise output
*/
 
/* TEST
update vacancy set staffid='test' where vacancyid='test';
select * from NetAssignmentDatasheet('test.candidate','test',null);
expect RowTitle=No*,RowContent=No*;
update vacancy set staffid=null where vacancyid='test';
*/
}