pears.NetOwnerAccountEdit

CREATE PROCEDURE "pears"."NetOwnerAccountEdit"( IN "pWebUserID" CHAR(20),IN "pClient_Code" CHAR(12),IN "pObjID" CHAR(20),IN "pParentObject" CHAR(20),IN "pAccount_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 "pOldClient_Code" CHAR(12),IN "pOldAccount_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 @AccountCode CHAR(12);
  DECLARE @DivisionID CHAR(20);
  SET "ssql" = '';
  SET @DivisionID = (SELECT "staff"."divisionid" FROM "staff" KEY JOIN "iqxnetuser" WHERE "iqxnetuserid" = "pwebuserid");
  IF @DivisionID IS NULL THEN
    SELECT '99:~Permission denied';
    RETURN
  END IF;
  IF "pAccount_Name" = '' THEN
    SELECT '101:~Please ensure there is a Company Name';
    RETURN
  END IF;
  IF "pObjID" = '' THEN
    SET @CompID = "uniquekey"("pAccount_Name");
    IF "pClient_Code" IS NULL OR "pClient_Code" = 'New' THEN
      SELECT '102:~Please ensure there is a valid Account Code';
      RETURN
    END IF;
    IF "pClient_Code" = any(SELECT "clientcode" FROM "company") THEN
      SELECT '103:~Please use a unique Account Code';
      RETURN
    END IF;
    SET @AccountCode = "pClient_Code";
    INSERT INTO "company"( "companyid","name","keyname","clientcode","invoiceaddress","divisionid" ) VALUES( @CompID,"pAccount_Name","ucase"("pAccount_Name"),@AccountCode,'1',@DivisionID ) 
  ELSE
    SET @CompID = "pObjID";
    IF "pAccount_Name" <> "pOldAccount_Name" THEN
      SET "ssql" = "ssql"+',name=pAccount_Name,keyname = ucase(pAccount_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,'#',@AccountCode,'~Success')
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerAccountEdit" IS 
{CREATE PROCEDURE pears."NetOwnerAccountEdit"( IN "pWebUserID" CHAR(20),IN "pClient_Code" CHAR(12),IN "pObjID" CHAR(20),IN "pParentObject" CHAR(20),IN "pAccount_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 "pOldClient_Code" CHAR(12),IN "pOldAccount_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 @AccountCode CHAR(12);
  DECLARE @DivisionID CHAR(20);
  SET "ssql" = '';
  SET @DivisionID = (SELECT "staff"."divisionid" FROM "staff" KEY JOIN "iqxnetuser" WHERE "iqxnetuserid" = "pwebuserid");
  IF @DivisionID IS NULL THEN
    SELECT '99:~Permission denied';
    RETURN
  END IF;
  IF "pAccount_Name" = '' THEN
    SELECT '101:~Please ensure there is a Company Name';
    RETURN
  END IF;
  IF "pObjID" = '' THEN
    SET @CompID = "uniquekey"("pAccount_Name");
    IF "pClient_Code" IS NULL OR "pClient_Code" = 'New' THEN
      SELECT '102:~Please ensure there is a valid Account Code';
      RETURN
    END IF;
    IF "pClient_Code" = any(SELECT "clientcode" FROM "company") THEN
      SELECT '103:~Please use a unique Account Code';
      RETURN
    END IF;
    SET @AccountCode = "pClient_Code";
    INSERT INTO "company"( "companyid","name","keyname","clientcode","invoiceaddress","divisionid" ) VALUES( @CompID,"pAccount_Name","ucase"("pAccount_Name"),@AccountCode,'1',@DivisionID ) 
  ELSE
    SET @CompID = "pObjID";
    IF "pAccount_Name" <> "pOldAccount_Name" THEN
      SET "ssql" = "ssql"+',name=pAccount_Name,keyname = ucase(pAccount_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,'#',@AccountCode,'~Success')
END
}