pears.NetOwnerEmployeeEdit

CREATE PROCEDURE "pears"."NetOwnerEmployeeEdit"( IN "pWebUserID" CHAR(20),IN "pObjID" CHAR(20),IN "pParentObject" CHAR(20),IN "pForenames" CHAR(50) DEFAULT NULL,IN "pSurname" CHAR(50) DEFAULT NULL,IN "pSalutation" CHAR(50) DEFAULT NULL,IN "pDirect_Phone" CHAR(50) DEFAULT NULL,IN "pMobile" CHAR(50) DEFAULT NULL,IN "pEmail" CHAR(50) DEFAULT NULL,IN "pDirect_Fax" CHAR(50) DEFAULT NULL,IN "pPosition" CHAR(50) DEFAULT NULL,IN "pDepartment" CHAR(50) DEFAULT NULL,IN "pNotes" CHAR(100) DEFAULT NULL,
  IN "pOldForenames" CHAR(50) DEFAULT NULL,IN "pOldSurname" CHAR(50) DEFAULT NULL,IN "pOldSalutation" CHAR(50) DEFAULT NULL,IN "pOldDirect_Phone" CHAR(50) DEFAULT NULL,IN "pOldMobile" CHAR(50) DEFAULT NULL,IN "pOldEmail" CHAR(50) DEFAULT NULL,IN "pOldDirect_Fax" CHAR(50) DEFAULT NULL,IN "pOldPosition" CHAR(50) DEFAULT NULL,IN "pOldDepartment" CHAR(50) DEFAULT NULL,IN "pOldNotes" CHAR(100) DEFAULT NULL ) 
RESULT( "pResult" CHAR(250) ) 
BEGIN
  DECLARE "PersID" CHAR(20);
  DECLARE "ssql" CHAR(255);
  DECLARE "divid" CHAR(20);
  SET "divid" = (SELECT FIRST "staff"."divisionid" FROM "iqxnetuser" KEY JOIN "staff" WHERE "iqxnetuser"."iqxnetuserid" = "pwebuserid");
  IF "divid" IS NULL THEN
    SELECT '99:~Permission denied';
    RETURN
  END IF;
  IF "IQXNetHasPermission"("pWebUserID",'ADDCANDIDATES') = 0 THEN
    SELECT '99:~Permission denied';
    RETURN
  END IF;
  IF "pForenames" = '' OR "pSurname" = '' THEN
    SELECT '101:~Please ensure there is both a Forename and a Surname';
    RETURN
  END IF;
  IF("pObjID" = '') THEN
    SET "PersID" = "uniquekey"("pForenames"+"pSurname");
    INSERT INTO "person"( "personid","name","keyname","salutation","forenames","surname","status" ) VALUES( "PersID","pForenames"+' '+"pSurname","ucase"("pForenames")+' '+"ucase"("pSurname"),"pSalutation","pForenames","pSurname",'C' ) ;
    INSERT INTO "employment"( "employmentid","personid","companyid","temp" ) VALUES( "PersID","PersID","pParentObject",'0' ) 
  ELSE
    SET "PersID" = "pObjID";
    UPDATE "person" SET "forenames" = "pForenames","surname" = "pSurname","name" = "pForenames"+' '+"pSurname","keyname" = "ucase"("pForenames")+' '+"ucase"("pSurname"),"salutation" = "pSalutation" WHERE "personid" = "PersID"
  END IF;
  IF "pNotes" <> "pOldNotes" THEN
    UPDATE "employment" SET "note" = "pNotes" WHERE "personid" = "PersID"
  END IF;
  IF "pPosition" <> "pOldPosition" THEN
    UPDATE "employment" SET "position" = "pPosition" WHERE "personid" = "PersID"
  END IF;
  IF "pDepartment" <> "pOldDepartment" THEN
    UPDATE "employment" SET "department" = "pDepartment" WHERE "personid" = "PersID"
  END IF;
  IF "pDirect_Phone" <> "pOldDirect_Phone" THEN
    CALL "SetPhone"('CP','Direct Telephone',"PersID","pDirect_Phone")
  END IF;
  IF "pMobile" <> "pOldMobile" THEN
    CALL "SetPhone"('CP','Mobile',"PersID","pMobile")
  END IF;
  IF "pEmail" <> "pOldEmail" THEN
    CALL "SetPhone"('CP','E-mail',"PersID","pEmail")
  END IF;
  IF "pDirect_Fax" <> "pOldDirect_Fax" THEN
    CALL "SetPhone"('CP','Direct Fax',"PersID","pDirect_Fax")
  END IF;
  SELECT "string"('0:',"PersID",'#',"pParentObject",'~Success')
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerEmployeeEdit" IS 
{CREATE PROCEDURE pears."NetOwnerEmployeeEdit"( IN "pWebUserID" CHAR(20),IN "pObjID" CHAR(20),IN "pParentObject" CHAR(20),IN "pForenames" CHAR(50) DEFAULT NULL,IN "pSurname" CHAR(50) DEFAULT NULL,IN "pSalutation" CHAR(50) DEFAULT NULL,IN "pDirect_Phone" CHAR(50) DEFAULT NULL,IN "pMobile" CHAR(50) DEFAULT NULL,IN "pEmail" CHAR(50) DEFAULT NULL,IN "pDirect_Fax" CHAR(50) DEFAULT NULL,IN "pPosition" CHAR(50) DEFAULT NULL,IN "pDepartment" CHAR(50) DEFAULT NULL,IN "pNotes" CHAR(100) DEFAULT NULL,
  IN "pOldForenames" CHAR(50) DEFAULT NULL,IN "pOldSurname" CHAR(50) DEFAULT NULL,IN "pOldSalutation" CHAR(50) DEFAULT NULL,IN "pOldDirect_Phone" CHAR(50) DEFAULT NULL,IN "pOldMobile" CHAR(50) DEFAULT NULL,IN "pOldEmail" CHAR(50) DEFAULT NULL,IN "pOldDirect_Fax" CHAR(50) DEFAULT NULL,IN "pOldPosition" CHAR(50) DEFAULT NULL,IN "pOldDepartment" CHAR(50) DEFAULT NULL,IN "pOldNotes" CHAR(100) DEFAULT NULL ) 
RESULT( "pResult" CHAR(250) ) 
BEGIN
  DECLARE "PersID" CHAR(20);
  DECLARE "ssql" CHAR(255);
  DECLARE "divid" CHAR(20);
  SET "divid" = (SELECT FIRST "staff"."divisionid" FROM "iqxnetuser" KEY JOIN "staff" WHERE "iqxnetuser"."iqxnetuserid" = "pwebuserid");
  IF "divid" IS NULL THEN
    SELECT '99:~Permission denied';
    RETURN
  END IF;
  IF "IQXNetHasPermission"("pWebUserID",'ADDCANDIDATES') = 0 THEN
    SELECT '99:~Permission denied';
    RETURN
  END IF;
  IF "pForenames" = '' OR "pSurname" = '' THEN
    SELECT '101:~Please ensure there is both a Forename and a Surname';
    RETURN
  END IF;
  IF("pObjID" = '') THEN
    SET "PersID" = "uniquekey"("pForenames"+"pSurname");
    INSERT INTO "person"( "personid","name","keyname","salutation","forenames","surname","status" ) VALUES( "PersID","pForenames"+' '+"pSurname","ucase"("pForenames")+' '+"ucase"("pSurname"),"pSalutation","pForenames","pSurname",'C' ) ;
    INSERT INTO "employment"( "employmentid","personid","companyid","temp" ) VALUES( "PersID","PersID","pParentObject",'0' ) 
  ELSE
    SET "PersID" = "pObjID";
    UPDATE "person" SET "forenames" = "pForenames","surname" = "pSurname","name" = "pForenames"+' '+"pSurname","keyname" = "ucase"("pForenames")+' '+"ucase"("pSurname"),"salutation" = "pSalutation" WHERE "personid" = "PersID"
  END IF;
  IF "pNotes" <> "pOldNotes" THEN
    UPDATE "employment" SET "note" = "pNotes" WHERE "personid" = "PersID"
  END IF;
  IF "pPosition" <> "pOldPosition" THEN
    UPDATE "employment" SET "position" = "pPosition" WHERE "personid" = "PersID"
  END IF;
  IF "pDepartment" <> "pOldDepartment" THEN
    UPDATE "employment" SET "department" = "pDepartment" WHERE "personid" = "PersID"
  END IF;
  IF "pDirect_Phone" <> "pOldDirect_Phone" THEN
    CALL "SetPhone"('CP','Direct Telephone',"PersID","pDirect_Phone")
  END IF;
  IF "pMobile" <> "pOldMobile" THEN
    CALL "SetPhone"('CP','Mobile',"PersID","pMobile")
  END IF;
  IF "pEmail" <> "pOldEmail" THEN
    CALL "SetPhone"('CP','E-mail',"PersID","pEmail")
  END IF;
  IF "pDirect_Fax" <> "pOldDirect_Fax" THEN
    CALL "SetPhone"('CP','Direct Fax',"PersID","pDirect_Fax")
  END IF;
  SELECT "string"('0:',"PersID",'#',"pParentObject",'~Success')
END
}