pears.LSMLuserValue

CREATE FUNCTION "pears"."LSMLuserValue"( IN "stype" CHAR(1),IN "sid" CHAR(20) ) 
RETURNS CHAR(20)
BEGIN
  DECLARE "rv" CHAR(20);
  DECLARE "branch" CHAR(20);
  DECLARE "depart" CHAR(20);
  DECLARE "divis" CHAR(20);
  DECLARE "consult" CHAR(20);
  DECLARE "consultid" CHAR(20);
  DECLARE "desk" CHAR(20);
  DECLARE "lv" long VARCHAR;
  DECLARE "vatonmargin" CHAR(1);
  DECLARE "suspendcheck" CHAR(1);
  DECLARE "c" CHAR(1);
  DECLARE "i" INTEGER;
  DECLARE "x" CHAR(20);
  DECLARE "PayslipDist" CHAR(1);
  DECLARE "InvoiceDist" CHAR(1);
  -- stype= company-C vacancy-V person-P Payband-R Timesheet-T TimesheetLine-L ProvTimesheet-E ProvTimesheetLine-F Expenses-X
  SELECT FIRST "lsmagencyid" INTO "rv" FROM "params";
  CASE
  WHEN "stype" = 'C' THEN
    SELECT "invoicefrequency","groupinvoice" INTO "c","i" FROM "companyaccount" WHERE "companyid" = "sid";
    IF "isnull"("i",0) = 2 THEN
      SET "rv" = '1' -- Group by their ref
    ELSE
      SET "rv" = '0' -- Group by client or not at all - the sepinv field will be set appropriately
    END IF;
    IF "isnull"("c",' ') IN( '4','M' ) THEN
      SET "rv" = "rv"+'01'
    ELSE
      IF "isnull"("c",' ') = 'W' THEN
        SET "rv" = "rv"+'02'
      ELSE
        SET "rv" = "rv"+'00'
      END IF END IF;
    -- Get Payslip Distribution Behaviour
    SELECT "max"("tagchoiceid") INTO "PayslipDist" FROM "tagvalue" WHERE "taglocation" = 'C' AND "tagid" = 'INV' AND "id" = "sid";
    IF "isnull"("PayslipDist",'') = '' THEN
      SET "PayslipDist" = '0'
    END IF;
    SET "rv" = "string"("rv","PayslipDist");
    RETURN("padstr"("rv",20,'_'))
  WHEN "stype" = 'T' OR "stype" = 'E' THEN
    IF "stype" = 'T' THEN
      SELECT "d"."departmentid","d"."defanalysiscode","isnull"("placement"."staffid","person"."staffid"),
        "placementelementvalue"("t"."placementid",'VATONMARGIN'),
        (IF EXISTS(SELECT * FROM "temptimesheetline" AS "L" KEY JOIN "temppayband" AS "B" WHERE "L"."temptimesheetid" = "t"."temptimesheetid" AND "B"."payrollflag" LIKE '_1%') THEN '1'
        ELSE '0'
        endif) INTO "depart","desk","consultid","lv","suspendcheck" FROM "temptimesheet" AS "t" KEY LEFT OUTER JOIN "tempdesk" AS "d"
          ,"temptimesheet" AS "t" KEY LEFT OUTER JOIN "placement"
          ,"temptimesheet" AS "t" KEY JOIN "person"
        WHERE "t"."temptimesheetid" = "sid"
    ELSE
      SELECT "d"."departmentid","d"."defanalysiscode","isnull"("placement"."staffid","isnull"("vacancy"."staffid","person"."staffid")),
        "placementelementvalue"("t"."placementid",'VATONMARGIN'),
        (IF EXISTS(SELECT * FROM "tempprovtimesheetline" AS "L" KEY JOIN "temppayband" AS "B" WHERE "L"."tempprovtimesheetid" = "t"."tempprovtimesheetid" AND "B"."payrollflag" LIKE '_1%') THEN '1'
        ELSE '0'
        endif) INTO "depart","desk","consultid","lv","suspendcheck" FROM "tempprovtimesheet" AS "t" KEY LEFT OUTER JOIN "tempdesk" AS "d"
          ,"tempprovtimesheet" AS "t" KEY LEFT OUTER JOIN "vacancy"
          ,"tempprovtimesheet" AS "t" KEY LEFT OUTER JOIN "placement"
          ,"tempprovtimesheet" AS "t" KEY JOIN "person"
        WHERE "t"."tempprovtimesheetid" = "sid"
    END IF;
    SELECT(SELECT "analysis" FROM "agencydetails" WHERE "agencyid" = "c"."agencyid"),
      (SELECT "analysis" FROM "division" WHERE "divisionid" = "c"."divisionid"),
      "c"."analysiscode" INTO "branch","divis","consult" FROM "staff" AS "c" WHERE "c"."staffid" = "consultid";
    SET "vatonmargin" = "isnull"("left"("lv",1),'');
    IF "vatonmargin" = 'Y' THEN
      SET "vatonmargin" = '1'
    END IF;
    IF "vatonmargin" <> '1' THEN
      SET "vatonmargin" = '0'
    END IF;
    SET "suspendcheck" = "isnull"("suspendcheck",'0');
    SET "rv" = "padstr"("divis",2,'_')+"padstr"("branch",3,'_')+"padstr"("depart",2,'_')+"padstr"("desk",3,'_')+"padstr"("consult",5,'_')+"vatonmargin"+"suspendcheck";
    RETURN("padstr"("rv",20,'_'))
  WHEN "stype" = 'L' OR "stype" = 'F' THEN
    IF "stype" = 'L' THEN
      SELECT "b"."payrollflag" INTO "x" FROM "temptimesheetline" AS "l" KEY JOIN "temppayband" AS "b" WHERE "l"."temptimesheetlineid" = "sid"
    ELSE
      SELECT "b"."payrollflag" INTO "x" FROM "tempprovtimesheetline" AS "l" KEY JOIN "temppayband" AS "b" WHERE "l"."tempprovtimesheetlineid" = "sid"
    END IF;
    RETURN("padstr"("x",20,'0'))
  WHEN "stype" = 'P' THEN
    SET "x" = NULL;
    SELECT "max"("tagchoiceid") INTO "x" FROM "tagvalue" WHERE "taglocation" = 'P' AND "tagid" = 'INS' AND "id" = "sid";
    IF "isnull"("x",'') = 'Y' THEN
      SET "x" = '1'
    ELSE
      SET "x" = '0'
    END IF;
    SELECT(SELECT "analysis" FROM "agencydetails" WHERE "agencyid" = "c"."agencyid"),
      (SELECT "analysis" FROM "division" WHERE "divisionid" = "person"."divisionid"),
      "c"."analysiscode" INTO "branch","divis","consult" FROM "staff" AS "c" KEY JOIN "person" WHERE "person"."personid" = "sid";
    SET "branch" = "padstr"("branch",3,'_');
    SET "divis" = "padstr"("divis",2,'_');
    SET "consult" = "padstr"("consult",5,'_');
    -- Get Invoice Distribution Behaviour
    SELECT "max"("tagchoiceid") INTO "InvoiceDist" FROM "tagvalue" WHERE "taglocation" = 'P' AND "tagid" = 'INV' AND "id" = "sid";
    IF "isnull"("InvoiceDist",'') = '' THEN
      SET "InvoiceDist" = '0'
    END IF;
    RETURN("padstr"("string"("x","divis","branch","consult","InvoiceDist"),20,'_'))
  END CASE;
  RETURN("rv")
END