pears.NetOwnerCompanyEdit

CREATE PROCEDURE "pears"."NetOwnerCompanyEdit"( IN "pWebUserID" CHAR(20),IN "pObjID" CHAR(20),IN "pParentObject" CHAR(20),IN "pCompany_Name" CHAR(60),IN "pAddress_Line_1" CHAR(40),IN "pAddress_Line_2" CHAR(40),IN "pAddress_Line_3" CHAR(40),IN "pTown" CHAR(30),IN "pCounty" CHAR(30),IN "pCountry" CHAR(30),IN "pPostcode" CHAR(20),IN "pPhone" CHAR(100),IN "pFax" CHAR(100),IN "pWeb_Site" CHAR(100),IN "pOldCompany_Name" CHAR(60),IN "pOldAddress_Line_1" CHAR(40),IN "pOldAddress_Line_2" CHAR(40),IN "pOldAddress_Line_3" CHAR(40),IN "pOldTown" CHAR(30),IN "pOldCounty" CHAR(30),IN "pOldCountry" CHAR(30),IN "pOldPostcode" CHAR(20),IN "pOldPhone" CHAR(100),IN "pOldFax" CHAR(100),IN "pOldWeb_Site" CHAR(100) ) 
RESULT( "pResult" CHAR(250) ) 
BEGIN
  DECLARE "CompID" CHAR(20);
  DECLARE "ssql" CHAR(255);
  DECLARE "divid" CHAR(20);
  SET "ssql" = '';
  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 "pCompany_Name" = '' THEN
    SELECT '101:~Please ensure there is a Company Name';
    RETURN
  END IF;
  IF "pObjID" = '' THEN
    SET "CompID" = "uniquekey"("pCompany_Name");
    INSERT INTO "company"( "companyid","name","keyname","clientcode","divisionid" ) VALUES( "CompID","pCompany_Name","ucase"("pCompany_Name"),"pParentObject","divid" ) 
  ELSE
    SET "CompID" = "pObjID";
    IF "pCompany_Name" <> "pOldCompany_Name" THEN
      SET "ssql" = "ssql"+',name=pCompany_Name,keyname = ucase(pCompany_Name)'
    END IF END IF;
  IF "pAddress_Line_1" <> "pOldAddress_Line_1" THEN
    SET "ssql" = "ssql"+',addr1=pAddress_Line_1'
  END IF;
  IF "pAddress_Line_2" <> "pOldAddress_Line_2" THEN
    SET "ssql" = "ssql"+',addr2=pAddress_Line_2'
  END IF;
  IF "pAddress_Line_3" <> "pOldAddress_Line_3" THEN
    SET "ssql" = "ssql"+',addr3=pAddress_Line_3'
  END IF;
  IF "pTown" <> "pOldTown" THEN
    SET "ssql" = "ssql"+',town=pTown'
  END IF;
  IF "pCounty" <> "pOldCounty" THEN
    SET "ssql" = "ssql"+',county=pCounty'
  END IF;
  IF "pCountry" <> "pOldCountry" THEN
    SET "ssql" = "ssql"+',country=pCountry'
  END IF;
  IF "pPostcode" <> "pOldPostcode" THEN
    SET "ssql" = "ssql"+',Postcode=pPostcode'
  END IF;
  IF "ssql" > '' THEN
    EXECUTE IMMEDIATE 'update company set '+"stuff"("ssql",1,1,'')+' where companyid = CompID'
  END IF;
  IF "pPhone" <> "pOldPhone" THEN
    CALL "SetPhone"('C','Telephone',"CompID","pPhone")
  END IF;
  IF "pFax" <> "pOldFax" THEN
    CALL "SetPhone"('C','Fax',"CompID","pFax")
  END IF;
  IF "pWeb_Site" <> "pOldWeb_Site" THEN
    CALL "SetPhone"('C','Web Site',"CompID","pWeb_Site")
  END IF;
  SELECT "string"('0:',"CompID",'#',"pParentObject",'~Success')
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerCompanyEdit" IS 
{CREATE PROCEDURE pears."NetOwnerCompanyEdit"( IN "pWebUserID" CHAR(20),IN "pObjID" CHAR(20),IN "pParentObject" CHAR(20),IN "pCompany_Name" CHAR(60),IN "pAddress_Line_1" CHAR(40),IN "pAddress_Line_2" CHAR(40),IN "pAddress_Line_3" CHAR(40),IN "pTown" CHAR(30),IN "pCounty" CHAR(30),IN "pCountry" CHAR(30),IN "pPostcode" CHAR(20),IN "pPhone" CHAR(100),IN "pFax" CHAR(100),IN "pWeb_Site" CHAR(100),IN "pOldCompany_Name" CHAR(60),IN "pOldAddress_Line_1" CHAR(40),IN "pOldAddress_Line_2" CHAR(40),IN "pOldAddress_Line_3" CHAR(40),IN "pOldTown" CHAR(30),IN "pOldCounty" CHAR(30),IN "pOldCountry" CHAR(30),IN "pOldPostcode" CHAR(20),IN "pOldPhone" CHAR(100),IN "pOldFax" CHAR(100),IN "pOldWeb_Site" CHAR(100) ) 
RESULT( "pResult" CHAR(250) ) 
BEGIN
  DECLARE "CompID" CHAR(20);
  DECLARE "ssql" CHAR(255);
  DECLARE "divid" CHAR(20);
  SET "ssql" = '';
  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 "pCompany_Name" = '' THEN
    SELECT '101:~Please ensure there is a Company Name';
    RETURN
  END IF;
  IF "pObjID" = '' THEN
    SET "CompID" = "uniquekey"("pCompany_Name");
    INSERT INTO "company"( "companyid","name","keyname","clientcode","divisionid" ) VALUES( "CompID","pCompany_Name","ucase"("pCompany_Name"),"pParentObject","divid" ) 
  ELSE
    SET "CompID" = "pObjID";
    IF "pCompany_Name" <> "pOldCompany_Name" THEN
      SET "ssql" = "ssql"+',name=pCompany_Name,keyname = ucase(pCompany_Name)'
    END IF END IF;
  IF "pAddress_Line_1" <> "pOldAddress_Line_1" THEN
    SET "ssql" = "ssql"+',addr1=pAddress_Line_1'
  END IF;
  IF "pAddress_Line_2" <> "pOldAddress_Line_2" THEN
    SET "ssql" = "ssql"+',addr2=pAddress_Line_2'
  END IF;
  IF "pAddress_Line_3" <> "pOldAddress_Line_3" THEN
    SET "ssql" = "ssql"+',addr3=pAddress_Line_3'
  END IF;
  IF "pTown" <> "pOldTown" THEN
    SET "ssql" = "ssql"+',town=pTown'
  END IF;
  IF "pCounty" <> "pOldCounty" THEN
    SET "ssql" = "ssql"+',county=pCounty'
  END IF;
  IF "pCountry" <> "pOldCountry" THEN
    SET "ssql" = "ssql"+',country=pCountry'
  END IF;
  IF "pPostcode" <> "pOldPostcode" THEN
    SET "ssql" = "ssql"+',Postcode=pPostcode'
  END IF;
  IF "ssql" > '' THEN
    EXECUTE IMMEDIATE 'update company set '+"stuff"("ssql",1,1,'')+' where companyid = CompID'
  END IF;
  IF "pPhone" <> "pOldPhone" THEN
    CALL "SetPhone"('C','Telephone',"CompID","pPhone")
  END IF;
  IF "pFax" <> "pOldFax" THEN
    CALL "SetPhone"('C','Fax',"CompID","pFax")
  END IF;
  IF "pWeb_Site" <> "pOldWeb_Site" THEN
    CALL "SetPhone"('C','Web Site',"CompID","pWeb_Site")
  END IF;
  SELECT "string"('0:',"CompID",'#',"pParentObject",'~Success')
END
}