pears.NetClientVacanciesLineTimesUpdate

CREATE PROCEDURE "pears"."NetClientVacanciesLineTimesUpdate"( IN "pWebUserID" CHAR(20),IN "pRowData" CHAR(40) DEFAULT NULL ) 
RESULT( "retval" CHAR(250) ) 
-- 20120705 update shift times
-- 20120710 suppress popup
-- 20130213 created from GenevaNetClientVacanciesLineTimesUpdate
BEGIN
  DECLARE "TableID" CHAR(1);
  DECLARE "RowID" CHAR(20);
  DECLARE "NewTime" CHAR(6);
  DECLARE "WhichTime" CHAR(8);
  DECLARE "IntString" CHAR(20);
  DECLARE "VacID" CHAR(20);
  DECLARE "EmploymentID" CHAR(20);
  DECLARE "ShiftRef" CHAR(10);
  SET "TableID" = "substring"("pRowData",1,2);
  SET "RowID" = "substring"("pRowData",2,"charindex"('~',"pRowData")-1);
  SET "IntString" = "substring"("pRowData","charindex"('~',"pRowData")+1);
  SET "NewTime" = "substring"("IntString",1,"charindex"('~',"IntString")-1);
  SET "WhichTime" = "substring"("IntString","charindex"('~',"IntString")+1);
  IF "isdate"("NewTime") = 0 OR "charindex"(':',"NewTime") = 0 THEN
    SELECT '1:~Not a valid time'
  END IF;
  IF "TableID" = 'P' THEN
    (SELECT "t"."vacancyid","ShiftSerialNumber","e"."employmentid" INTO "VacID","ShiftRef","EmploymentID" FROM "tempshiftplan" AS "t" KEY JOIN "vacancy" KEY JOIN "employment" AS "e" WHERE "tempshiftplanid" = "RowID")
  ELSE
    (SELECT "t"."vacancyid","e"."employmentid","p"."ShiftSerialNumber" INTO "VacID","EmploymentID","ShiftRef" FROM "tempshiftplan" AS "p" KEY JOIN "tempshift" AS "t" KEY JOIN "vacancy" KEY JOIN "employment" AS "e" LEFT OUTER JOIN "person" AS "p" ON "p"."personid" = "t"."personid" WHERE "t"."tempshiftid" = "RowID")
  END IF;
  IF "TableID" = 'V' THEN
    IF "WhichTime" = 'S' THEN
      UPDATE "tempshift" SET "TimeFrom" = "NewTime" WHERE "tempshiftid" = "RowID"
    ELSE
      UPDATE "tempshift" SET "TimeTo" = "NewTime" WHERE "tempshiftid" = "RowID"
    END IF
  ELSE IF "WhichTime" = 'S' THEN
      UPDATE "tempshiftplan" SET "TimeFrom" = "NewTime" WHERE "tempshiftplanid" = "RowID"
    ELSE
      UPDATE "tempshiftplan" SET "TimeTo" = "NewTime" WHERE "tempshiftplanid" = "RowID"
    END IF END IF;
  SET "NewTime" = "dateformat"("NewTime",'hh:mm');
  IF "WhichTime" = 'S' THEN
    SET "WhichTime" = 'Start'
  ELSE
    SET "WhichTime" = 'End'
  END IF;
  CALL "GenevaIQXNetPopup"("pWebUserID",NULL,"EmploymentID","VacID",NULL,NULL,NULL,'Shift '+"WhichTime"+' time modified - '+"ShiftRef",'Shift '+"WhichTime"+' time modified - '+"NewTime",'N');
  SELECT '1:~success:'+"NewTime"
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetClientVacanciesLineTimesUpdate" IS 
{CREATE PROCEDURE pears."NetClientVacanciesLineTimesUpdate"( IN "pWebUserID" CHAR(20),IN "pRowData" CHAR(40) DEFAULT NULL ) 
RESULT( "retval" CHAR(250) ) 
-- 20120705 update shift times
-- 20120710 suppress popup
-- 20130213 created from GenevaNetClientVacanciesLineTimesUpdate
BEGIN
  DECLARE "TableID" CHAR(1);
  DECLARE "RowID" CHAR(20);
  DECLARE "NewTime" CHAR(6);
  DECLARE "WhichTime" CHAR(8);
  DECLARE "IntString" CHAR(20);
  DECLARE "VacID" CHAR(20);
  DECLARE "EmploymentID" CHAR(20);
  DECLARE "ShiftRef" CHAR(10);
  SET "TableID" = "substring"("pRowData",1,2);
  SET "RowID" = "substring"("pRowData",2,"charindex"('~',"pRowData")-1);
  SET "IntString" = "substring"("pRowData","charindex"('~',"pRowData")+1);
  SET "NewTime" = "substring"("IntString",1,"charindex"('~',"IntString")-1);
  SET "WhichTime" = "substring"("IntString","charindex"('~',"IntString")+1);
  IF "isdate"("NewTime") = 0 OR "charindex"(':',"NewTime") = 0 THEN
    SELECT '1:~Not a valid time'
  END IF;
  IF "TableID" = 'P' THEN
    (SELECT "t"."vacancyid","ShiftSerialNumber","e"."employmentid" INTO "VacID","ShiftRef","EmploymentID" FROM "tempshiftplan" AS "t" KEY JOIN "vacancy" KEY JOIN "employment" AS "e" WHERE "tempshiftplanid" = "RowID")
  ELSE
    (SELECT "t"."vacancyid","e"."employmentid","p"."ShiftSerialNumber" INTO "VacID","EmploymentID","ShiftRef" FROM "tempshiftplan" AS "p" KEY JOIN "tempshift" AS "t" KEY JOIN "vacancy" KEY JOIN "employment" AS "e" LEFT OUTER JOIN "person" AS "p" ON "p"."personid" = "t"."personid" WHERE "t"."tempshiftid" = "RowID")
  END IF;
  IF "TableID" = 'V' THEN
    IF "WhichTime" = 'S' THEN
      UPDATE "tempshift" SET "TimeFrom" = "NewTime" WHERE "tempshiftid" = "RowID"
    ELSE
      UPDATE "tempshift" SET "TimeTo" = "NewTime" WHERE "tempshiftid" = "RowID"
    END IF
  ELSE IF "WhichTime" = 'S' THEN
      UPDATE "tempshiftplan" SET "TimeFrom" = "NewTime" WHERE "tempshiftplanid" = "RowID"
    ELSE
      UPDATE "tempshiftplan" SET "TimeTo" = "NewTime" WHERE "tempshiftplanid" = "RowID"
    END IF END IF;
  SET "NewTime" = "dateformat"("NewTime",'hh:mm');
  IF "WhichTime" = 'S' THEN
    SET "WhichTime" = 'Start'
  ELSE
    SET "WhichTime" = 'End'
  END IF;
  CALL "GenevaIQXNetPopup"("pWebUserID",NULL,"EmploymentID","VacID",NULL,NULL,NULL,'Shift '+"WhichTime"+' time modified - '+"ShiftRef",'Shift '+"WhichTime"+' time modified - '+"NewTime",'N');
  SELECT '1:~success:'+"NewTime"
END
}