pears.NetOwnerTimesheetCreate

CREATE PROCEDURE "pears"."NetOwnerTimesheetCreate"( IN "pWebUserID" CHAR(20),IN "pStartDate" CHAR(25),IN "pLineData" long VARCHAR ) 
RESULT( "AcName" CHAR(60),"SiteAddr" CHAR(150),"JobTitle" CHAR(60),"TempName" CHAR(60),"PayBand" CHAR(100),"Units" DOUBLE,"TimesheetNumber" CHAR(20) ) 
BEGIN
  DECLARE "provtsid" CHAR(20);
  DECLARE "persid" CHAR(20);
  DECLARE "vacid" CHAR(20);
  DECLARE "rateid" CHAR(20);
  DECLARE "deskid" CHAR(20);
  DECLARE "divid" CHAR(20);
  DECLARE "perd" INTEGER;
  DECLARE "lineid" CHAR(20);
  DECLARE "bandid" CHAR(20);
  DECLARE "lineno" INTEGER;
  DECLARE "nUnits" DOUBLE;
  DECLARE "nPayrate" DOUBLE;
  DECLARE "nChargerate" DOUBLE;
  DECLARE "chained" CHAR(3);
  DECLARE "checkval" CHAR(50);
  DECLARE "startdate" DATE;
  DECLARE "errmsg" CHAR(60);
  DECLARE "thisline" CHAR(150);
  DECLARE "i" INTEGER;
  DECLARE "cnt" INTEGER;
  DECLARE "cr" CHAR(1);
  DECLARE "tab" CHAR(1);
  DECLARE LOCAL TEMPORARY TABLE "IDs"(
    "ID" CHAR(20) NULL,
    "RowCnt" INTEGER NULL,
    "Msg" CHAR(50) NULL,
    ) NOT transactional;
  SET "chained" = (SELECT "connection_property"('chained'));
  SET "errmsg" = NULL;
  SET "cr" = "char"(13);
  SET "tab" = "char"(9);
  SET "checkval" = '.';
  SET "startdate" = "iqxnetstringtodate"("pStartDate");
  IF "startdate" IS NULL THEN
    SET "errmsg" = 'Invalid start date'
  ELSE
    IF "abs"("datediff"("week",CURRENT DATE,"startdate")) > 5 THEN
      SET "errmsg" = 'Start date out of range'
    ELSE
      SET "perd" = "weekcontaining"("startdate");
      IF "perd" IS NULL THEN
        SET "errmsg" = 'Payroll week out of range'
      END IF END IF END IF;
  SET "divid" = (SELECT FIRST "staff"."divisionid" FROM "iqxnetuser" KEY JOIN "staff" WHERE "iqxnetuser"."iqxnetuserid" = "pwebuserid");
  IF "divid" IS NULL THEN
    SET "errmsg" = 'No staff division set'
  END IF;
  SET "deskid" = (SELECT FIRST "staff"."tempdeskid" FROM "iqxnetuser" KEY JOIN "staff" WHERE "iqxnetuser"."iqxnetuserid" = "pwebuserid");
  IF "deskid" IS NULL THEN
    SET "errmsg" = 'No staff temp desk set'
  END IF;
  IF "errmsg" IS NOT NULL THEN
    SELECT "errmsg",NULL,NULL,NULL,NULL,NULL;
    RETURN
  END IF;
  SET "Cnt" = 0;
  "outerloop": loop
    SET "i" = "charindex"("cr","plinedata");
    IF "i" = 0 THEN
      leave "outerloop"
    END IF;
    SET "thisline" = "left"("plinedata","i"-1);
    SET "plinedata" = "stuff"("plinedata",1,"i",NULL);
    SET "errmsg" = NULL;
    SET "Cnt" = "Cnt"+1;
    SET "LineID" = "uniquekey"("Cnt");
    SET "i" = "charindex"("tab","thisline");
    IF "i" = 0 THEN
      SET "vacid" = NULL
    ELSE
      SET "vacid" = "left"("thisline","i"-1);
      SET "thisline" = "stuff"("thisline",1,"i",NULL);
      SET "vacid" = (SELECT FIRST "vacancy"."vacancyid" FROM "vacancy" KEY JOIN "employment" KEY JOIN "company" WHERE "vacancy"."vacancyid" = "vacid" AND "company"."divisionid" = "divid");
      IF "vacid" IS NULL THEN
        SET "errmsg" = 'Invalid vacancy ID'
      END IF END IF;
    SET "i" = "charindex"("tab","thisline");
    IF "i" = 0 THEN
      SET "persid" = NULL
    ELSE
      SET "persid" = "left"("thisline","i"-1);
      SET "thisline" = "stuff"("thisline",1,"i",NULL);
      SET "persid" = (SELECT FIRST "personid" FROM "person" WHERE "personid" = "persid" AND "divisionid" = "divid");
      IF "persid" IS NULL THEN
        SET "errmsg" = "isnull"("errmsg",'Invalid person ID')
      END IF END IF;
    SET "i" = "charindex"("tab","thisline");
    IF "i" = 0 THEN
      SET "rateid" = NULL
    ELSE
      SET "rateid" = "left"("thisline","i"-1);
      SET "thisline" = "stuff"("thisline",1,"i",NULL);
      SET "bandid" = NULL;
      SELECT "temppaybandid","payrate","chargerate" INTO "bandid","npayrate","nchargerate" FROM "tempjobrate" WHERE "tempjobrateid" = "rateid" AND "vacancyid" = "vacid";
      IF "bandid" IS NULL THEN
        SET "errmsg" = "isnull"("errmsg",'Invalid rate ID')
      END IF END IF;
    BEGIN
      SET "nunits" = CAST("trim"("thisline") AS DOUBLE)
    exception
      WHEN others THEN SET "errmsg" = "isnull"("errmsg",'Invalid unit value')
    END;
    INSERT INTO "IDs"( "ID","RowCnt","msg" ) VALUES( "LineID","Cnt","errmsg" ) ;
    IF "errmsg" IS NULL THEN
      IF "vacid"+"persid" <> "checkval" THEN
        SET "lineno" = 0;
        SET "provtsid" = "uniquekey"("checkval");
        SET "checkval" = "vacid"+"persid";
        INSERT INTO "TempProvTimeSheet"( "TempProvTimeSheetID","PersonID","VacancyID","TempDeskID","Period","PeriodLength","ExtNumber","TheirRef" ) VALUES
          ( "provtsid","persid","vacid","deskid","perd",'W',2,NULL ) ;
        IF "chained" = 'On' THEN
          BEGIN atomic
            CALL "NetNumberProvTimesheet"("provtsid")
          END
        ELSE CALL "NetNumberProvTimesheet"("provtsid")
        END IF END IF;
      SET "lineno" = "lineno"+1;
      INSERT INTO "TempProvTimesheetLine"( "TempProvTimeSheetLineID","TempProvTimeSheetID","TempPayBandID","LineNumber","UnitsPaid","UnitsCharged","PayRate","ChargeRate" ) VALUES
        ( "lineid","provtsid","bandid","lineno","nUnits","nUnits","nPayrate","nChargeRate" ) 
    END IF
  END loop "outerloop";
  SELECT "isnull"((SELECT "name" FROM "company" WHERE "companyid" = "getinvoicecompanyid"("co"."clientcode")),"ids"."msg") AS "acname","string"("co"."name",', ',"co"."addr1") AS "siteaddr","vacancy"."position","person"."name",
    "string"((SELECT "Description" FROM "temppayband" WHERE "temppaybandid" = "l"."temppaybandid"),' ',"trim"("str"("l"."payrate",12,2)),'/',"trim"("str"("l"."chargerate",12,2))) AS "payband","l"."unitspaid","t"."serialnumber"
    FROM "ids" LEFT OUTER JOIN "tempprovtimesheetline" AS "l" ON "ids"."id" = "l"."tempprovtimesheetlineid" KEY JOIN "tempprovtimesheet" AS "t" KEY JOIN("person",("vacancy" KEY JOIN "employment" KEY JOIN "company" AS "co"))
    ORDER BY "ids"."rowcnt" ASC
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerTimesheetCreate" IS 
{CREATE PROCEDURE pears."NetOwnerTimesheetCreate"( IN "pWebUserID" CHAR(20),IN "pStartDate" CHAR(25),IN "pLineData" long VARCHAR ) 
RESULT( "AcName" CHAR(60),"SiteAddr" CHAR(150),"JobTitle" CHAR(60),"TempName" CHAR(60),"PayBand" CHAR(100),"Units" DOUBLE,"TimesheetNumber" CHAR(20) ) 
BEGIN
  DECLARE "provtsid" CHAR(20);
  DECLARE "persid" CHAR(20);
  DECLARE "vacid" CHAR(20);
  DECLARE "rateid" CHAR(20);
  DECLARE "deskid" CHAR(20);
  DECLARE "divid" CHAR(20);
  DECLARE "perd" INTEGER;
  DECLARE "lineid" CHAR(20);
  DECLARE "bandid" CHAR(20);
  DECLARE "lineno" INTEGER;
  DECLARE "nUnits" DOUBLE;
  DECLARE "nPayrate" DOUBLE;
  DECLARE "nChargerate" DOUBLE;
  DECLARE "chained" CHAR(3);
  DECLARE "checkval" CHAR(50);
  DECLARE "startdate" DATE;
  DECLARE "errmsg" CHAR(60);
  DECLARE "thisline" CHAR(150);
  DECLARE "i" INTEGER;
  DECLARE "cnt" INTEGER;
  DECLARE "cr" CHAR(1);
  DECLARE "tab" CHAR(1);
  DECLARE LOCAL TEMPORARY TABLE "IDs"(
    "ID" CHAR(20) NULL,
    "RowCnt" INTEGER NULL,
    "Msg" CHAR(50) NULL,
    ) NOT transactional;
  SET "chained" = (SELECT "connection_property"('chained'));
  SET "errmsg" = NULL;
  SET "cr" = "char"(13);
  SET "tab" = "char"(9);
  SET "checkval" = '.';
  SET "startdate" = "iqxnetstringtodate"("pStartDate");
  IF "startdate" IS NULL THEN
    SET "errmsg" = 'Invalid start date'
  ELSE
    IF "abs"("datediff"("week",CURRENT DATE,"startdate")) > 5 THEN
      SET "errmsg" = 'Start date out of range'
    ELSE
      SET "perd" = "weekcontaining"("startdate");
      IF "perd" IS NULL THEN
        SET "errmsg" = 'Payroll week out of range'
      END IF
    END IF END IF;
  SET "divid" = (SELECT FIRST "staff"."divisionid" FROM "iqxnetuser" KEY JOIN "staff" WHERE "iqxnetuser"."iqxnetuserid" = "pwebuserid");
  IF "divid" IS NULL THEN
    SET "errmsg" = 'No staff division set'
  END IF;
  SET "deskid" = (SELECT FIRST "staff"."tempdeskid" FROM "iqxnetuser" KEY JOIN "staff" WHERE "iqxnetuser"."iqxnetuserid" = "pwebuserid");
  IF "deskid" IS NULL THEN
    SET "errmsg" = 'No staff temp desk set'
  END IF;
  IF "errmsg" IS NOT NULL THEN
    SELECT "errmsg",NULL,NULL,NULL,NULL,NULL;
    RETURN
  END IF;
  SET "Cnt" = 0;
  "outerloop": loop
    SET "i" = "charindex"("cr","plinedata");
    IF "i" = 0 THEN
      leave "outerloop"
    END IF;
    SET "thisline" = "left"("plinedata","i"-1);
    SET "plinedata" = "stuff"("plinedata",1,"i",NULL);
    SET "errmsg" = NULL;
    SET "Cnt" = "Cnt"+1;
    SET "LineID" = "uniquekey"("Cnt");
    SET "i" = "charindex"("tab","thisline");
    IF "i" = 0 THEN
      SET "vacid" = NULL
    ELSE
      SET "vacid" = "left"("thisline","i"-1);
      SET "thisline" = "stuff"("thisline",1,"i",NULL);
      SET "vacid" = (SELECT FIRST "vacancy"."vacancyid" FROM "vacancy" KEY JOIN "employment" KEY JOIN "company" WHERE "vacancy"."vacancyid" = "vacid" AND "company"."divisionid" = "divid");
      IF "vacid" IS NULL THEN
        SET "errmsg" = 'Invalid vacancy ID'
      END IF END IF;
    SET "i" = "charindex"("tab","thisline");
    IF "i" = 0 THEN
      SET "persid" = NULL
    ELSE
      SET "persid" = "left"("thisline","i"-1);
      SET "thisline" = "stuff"("thisline",1,"i",NULL);
      SET "persid" = (SELECT FIRST "personid" FROM "person" WHERE "personid" = "persid" AND "divisionid" = "divid");
      IF "persid" IS NULL THEN
        SET "errmsg" = "isnull"("errmsg",'Invalid person ID')
      END IF END IF;
    SET "i" = "charindex"("tab","thisline");
    IF "i" = 0 THEN
      SET "rateid" = NULL
    ELSE
      SET "rateid" = "left"("thisline","i"-1);
      SET "thisline" = "stuff"("thisline",1,"i",NULL);
      SET "bandid" = NULL;
      SELECT "temppaybandid","payrate","chargerate" INTO "bandid","npayrate","nchargerate" FROM "tempjobrate" WHERE "tempjobrateid" = "rateid" AND "vacancyid" = "vacid";
      IF "bandid" IS NULL THEN
        SET "errmsg" = "isnull"("errmsg",'Invalid rate ID')
      END IF END IF;
    BEGIN
      SET "nunits" = CAST("trim"("thisline") AS DOUBLE)
    exception
      WHEN others THEN SET "errmsg" = "isnull"("errmsg",'Invalid unit value')
    END;
    INSERT INTO "IDs"( "ID","RowCnt","msg" ) VALUES( "LineID","Cnt","errmsg" ) ;
    IF "errmsg" IS NULL THEN
      IF "vacid"+"persid" <> "checkval" THEN
        SET "lineno" = 0;
        SET "provtsid" = "uniquekey"("checkval");
        SET "checkval" = "vacid"+"persid";
        INSERT INTO "TempProvTimeSheet"( "TempProvTimeSheetID","PersonID","VacancyID","TempDeskID","Period","PeriodLength","ExtNumber","TheirRef" ) VALUES
          ( "provtsid","persid","vacid","deskid","perd",'W',2,NULL ) ;
        IF "chained" = 'On' THEN
          BEGIN atomic
            CALL "NetNumberProvTimesheet"("provtsid")
          END
        ELSE CALL "NetNumberProvTimesheet"("provtsid")
        END IF END IF;
      SET "lineno" = "lineno"+1;
      INSERT INTO "TempProvTimesheetLine"( "TempProvTimeSheetLineID","TempProvTimeSheetID","TempPayBandID","LineNumber","UnitsPaid","UnitsCharged","PayRate","ChargeRate" ) VALUES
        ( "lineid","provtsid","bandid","lineno","nUnits","nUnits","nPayrate","nChargeRate" ) 
    END IF
  END loop "outerloop";
  SELECT "isnull"((SELECT "name" FROM "company" WHERE "companyid" = "getinvoicecompanyid"("co"."clientcode")),"ids"."msg") AS "acname","string"("co"."name",', ',"co"."addr1") AS "siteaddr","vacancy"."position","person"."name",
    "string"((SELECT "Description" FROM "temppayband" WHERE "temppaybandid" = "l"."temppaybandid"),' ',"trim"("str"("l"."payrate",12,2)),'/',"trim"("str"("l"."chargerate",12,2))) AS "payband","l"."unitspaid","t"."serialnumber"
    FROM "ids" LEFT OUTER JOIN "tempprovtimesheetline" AS "l" ON "ids"."id" = "l"."tempprovtimesheetlineid" KEY JOIN "tempprovtimesheet" AS "t" KEY JOIN("person",("vacancy" KEY JOIN "employment" KEY JOIN "company" AS "co"))
    ORDER BY "ids"."rowcnt" ASC
END
}