pears.NetVacancyFill

CREATE PROCEDURE "pears"."NetVacancyFill"( IN "pWebUserID" CHAR(20),IN "pvacancyid" CHAR(20),IN "pshiftplanid" CHAR(20),IN "ppersonid" CHAR(20) ) 
RESULT( "pResult" CHAR(250) ) 
BEGIN
  DECLARE "sid" CHAR(20);
  DECLARE "swebusername" CHAR(50);
  DECLARE "availability" CHAR(20);
  DECLARE "spopup" CHAR(100);
  DECLARE LOCAL TEMPORARY TABLE "IDs"(
    "ID" CHAR(20) NULL,
    ) NOT transactional;
  INSERT INTO "IDs"( "ID" ) 
    SELECT DISTINCT "e"."companyid" FROM "employment" AS "e" KEY JOIN "iqxnetuserlink" AS "i" WHERE "i"."iqxnetuserid" = "pWebUserID";
  IF NOT "pvacancyid" = any(SELECT "v"."vacancyid" FROM "vacancy" AS "v" KEY JOIN "employment" AS "e" JOIN "IDs" ON "e"."CompanyID" = "IDs"."ID") THEN
    SELECT '99:~Permission denied';
    RETURN
  END IF;
  IF "IQXNetHasPermission"("pWebUserID",'FILLJOBS') = 0 THEN
    SELECT '99:~Permission denied';
    RETURN
  END IF;
  SET "sid" = "uniquekey"("ppersonid");
  SET "swebusername" = (SELECT "name" FROM "iqxnetuser" WHERE "iqxnetuserid" = "pwebuserid");
  IF "trim"("isnull"("pshiftplanid",'')) = '' THEN
    INSERT INTO "employment"( "temp","employmentid","personid","companyid","startdate","leavedate","position","department" ) 
      SELECT "v"."temp","sid","ppersonid","e"."companyid","v"."startdate","v"."finishdate","v"."position","v"."clientdepartment"
        FROM "vacancy" AS "v" KEY JOIN "employment" AS "e" WHERE "v"."vacancyid" = "pvacancyid";
    INSERT INTO "placement"( "placementid","placedate","employmentid","vacancyid","departmentid","staffid","temp","daysperweek","salary",
      "TheirRef","ContractRef","Currency","WorkMonday","WorkTuesday","WorkWednesday","WorkThursday","WorkFriday","WorkSaturday","WorkSunday",
      "WorkNormalHours","WorkStartTime" ) 
      SELECT "sid",CURRENT DATE,"sid","pvacancyid","v"."departmentid","userstaffid","v"."temp",5,"v"."salary","v"."theirref","v"."contractref","v"."currency",
        "v"."WorkMonday","v"."WorkTuesday","v"."WorkWednesday","v"."WorkThursday","v"."WorkFriday","v"."WorkSaturday","v"."WorkSunday",
        "v"."WorkNormalHours","v"."WorkStartTime"
        FROM "vacancy" AS "v" WHERE "v"."vacancyid" = "pvacancyid";
    CALL "IQXNetPopup"("pWebUserID",NULL,NULL,"pvacancyid","sid",NULL,'CLIENTBOOKING','Client made placement',NULL);
    CALL "IQXNetRequestAction"("pWebUserID",'ClientFill','L',"sid")
  ELSE
    SET "availability" = "isnull"((SELECT "personavailableforplan"("ppersonid","p"."ShiftDate","p"."TimeFrom","p"."TimeTo","p"."RecoveryHours",0,0,NULL,NULL,NULL,NULL,NULL)
        FROM "tempshiftplan" AS "p" WHERE "p"."tempshiftplanid" = "pshiftplanid"),'');
    IF NOT("trim"("availability") = '' OR "availability" LIKE '[AM]%') THEN
      SELECT '1:~Candidate not available';
      RETURN
    END IF;
    INSERT INTO "tempshift"( "TempShiftID","VacancyID","PersonID","ShiftDate","TimeFrom","TimeTo","State","TempShiftPlanID",
      "BreakMinutes","EssentialSkillGradeID","ReferenceCode","ClientConfirmed","TempConfirmed","StaffID",
      "ConfirmedWith","TempShiftTypeID","AnalysisCode","RecoveryHours" ) 
      SELECT "sid","pVacancyID","pPersonID","p"."ShiftDate","p"."TimeFrom","p"."TimeTo",'P',"pShiftPlanID",
        "p"."BreakMinutes","p"."EssentialSkillGradeID","p"."ReferenceCode",1,0,"userStaffID",
        "swebusername","p"."TempShiftTypeID","p"."AnalysisCode","p"."RecoveryHours"
        FROM "tempshiftplan" AS "p" WHERE "p"."tempshiftplanid" = "pshiftplanid";
    SET "spopup" = (SELECT "dateformat"("p"."shiftdate",'Ddd dd/mm/yyyy')+' '+"dateformat"("p"."timefrom",'hh:nn')+' with '+"person"."name" FROM "tempshiftplan" AS "p","person"
        WHERE "p"."tempshiftplanid" = "pshiftplanid" AND "person"."personid" = "ppersonid");
    CALL "IQXNetPopup"("pWebUserID",NULL,NULL,"pvacancyid",NULL,NULL,'CLIENTBOOKING','Client filled shift',"spopup");
    CALL "IQXNetRequestAction"("pWebUserID",'ClientFill','SHIFT',"sid")
  END IF;
  SELECT '0:~Success'
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetVacancyFill" IS 
{CREATE PROCEDURE pears."NetVacancyFill"( IN "pWebUserID" CHAR(20),IN "pvacancyid" CHAR(20),IN "pshiftplanid" CHAR(20),IN "ppersonid" CHAR(20) ) 
RESULT( "pResult" CHAR(250) ) 
BEGIN
  DECLARE "sid" CHAR(20);
  DECLARE "swebusername" CHAR(50);
  DECLARE "availability" CHAR(20);
  DECLARE "spopup" CHAR(100);
  DECLARE LOCAL TEMPORARY TABLE "IDs"(
    "ID" CHAR(20) NULL,
    ) NOT transactional;
  INSERT INTO "IDs"( "ID" ) 
    SELECT DISTINCT "e"."companyid" FROM "employment" AS "e" KEY JOIN "iqxnetuserlink" AS "i" WHERE "i"."iqxnetuserid" = "pWebUserID";
  IF NOT "pvacancyid" = any(SELECT "v"."vacancyid" FROM "vacancy" AS "v" KEY JOIN "employment" AS "e" JOIN "IDs" ON "e"."CompanyID" = "IDs"."ID") THEN
    SELECT '99:~Permission denied';
    RETURN
  END IF;
  IF "IQXNetHasPermission"("pWebUserID",'FILLJOBS') = 0 THEN
    SELECT '99:~Permission denied';
    RETURN
  END IF;
  SET "sid" = "uniquekey"("ppersonid");
  SET "swebusername" = (SELECT "name" FROM "iqxnetuser" WHERE "iqxnetuserid" = "pwebuserid");
  IF "trim"("isnull"("pshiftplanid",'')) = '' THEN
    INSERT INTO "employment"( "temp","employmentid","personid","companyid","startdate","leavedate","position","department" ) 
      SELECT "v"."temp","sid","ppersonid","e"."companyid","v"."startdate","v"."finishdate","v"."position","v"."clientdepartment"
        FROM "vacancy" AS "v" KEY JOIN "employment" AS "e" WHERE "v"."vacancyid" = "pvacancyid";
    INSERT INTO "placement"( "placementid","placedate","employmentid","vacancyid","departmentid","staffid","temp","daysperweek","salary",
      "TheirRef","ContractRef","Currency","WorkMonday","WorkTuesday","WorkWednesday","WorkThursday","WorkFriday","WorkSaturday","WorkSunday",
      "WorkNormalHours","WorkStartTime" ) 
      SELECT "sid",CURRENT DATE,"sid","pvacancyid","v"."departmentid","userstaffid","v"."temp",5,"v"."salary","v"."theirref","v"."contractref","v"."currency",
        "v"."WorkMonday","v"."WorkTuesday","v"."WorkWednesday","v"."WorkThursday","v"."WorkFriday","v"."WorkSaturday","v"."WorkSunday",
        "v"."WorkNormalHours","v"."WorkStartTime"
        FROM "vacancy" AS "v" WHERE "v"."vacancyid" = "pvacancyid";
    CALL "IQXNetPopup"("pWebUserID",NULL,NULL,"pvacancyid","sid",NULL,'CLIENTBOOKING','Client made placement',NULL);
    CALL "IQXNetRequestAction"("pWebUserID",'ClientFill','L',"sid")
  ELSE
    SET "availability" = "isnull"((SELECT "personavailableforplan"("ppersonid","p"."ShiftDate","p"."TimeFrom","p"."TimeTo","p"."RecoveryHours",0,0,NULL,NULL,NULL,NULL,NULL)
        FROM "tempshiftplan" AS "p" WHERE "p"."tempshiftplanid" = "pshiftplanid"),'');
    IF NOT("trim"("availability") = '' OR "availability" LIKE '[AM]%') THEN
      SELECT '1:~Candidate not available';
      RETURN
    END IF;
    INSERT INTO "tempshift"( "TempShiftID","VacancyID","PersonID","ShiftDate","TimeFrom","TimeTo","State","TempShiftPlanID",
      "BreakMinutes","EssentialSkillGradeID","ReferenceCode","ClientConfirmed","TempConfirmed","StaffID",
      "ConfirmedWith","TempShiftTypeID","AnalysisCode","RecoveryHours" ) 
      SELECT "sid","pVacancyID","pPersonID","p"."ShiftDate","p"."TimeFrom","p"."TimeTo",'P',"pShiftPlanID",
        "p"."BreakMinutes","p"."EssentialSkillGradeID","p"."ReferenceCode",1,0,"userStaffID",
        "swebusername","p"."TempShiftTypeID","p"."AnalysisCode","p"."RecoveryHours"
        FROM "tempshiftplan" AS "p" WHERE "p"."tempshiftplanid" = "pshiftplanid";
    SET "spopup" = (SELECT "dateformat"("p"."shiftdate",'Ddd dd/mm/yyyy')+' '+"dateformat"("p"."timefrom",'hh:nn')+' with '+"person"."name" FROM "tempshiftplan" AS "p","person"
        WHERE "p"."tempshiftplanid" = "pshiftplanid" AND "person"."personid" = "ppersonid");
    CALL "IQXNetPopup"("pWebUserID",NULL,NULL,"pvacancyid",NULL,NULL,'CLIENTBOOKING','Client filled shift',"spopup");
    CALL "IQXNetRequestAction"("pWebUserID",'ClientFill','SHIFT',"sid")
  END IF;
  SELECT '0:~Success'
END
}