ALTER TABLE WS_Account
ALTER COLUMN Account_Name varchar(256) NOT NULL;

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WS_Account_Register]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WS_Account_Register]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WS_Login_Customer]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WS_Login_Customer]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WS_Account_ADD]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WS_Account_ADD]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WS_Account_Activate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WS_Account_Activate]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[WS_Account_Activate]
    @Account_Id int,
    @Msg varchar(50) output
AS
BEGIN
	-- see if account id exists?
    IF NOT EXISTS(SELECT * FROM WS_Account WHERE (Account_Id = @Account_Id))
    BEGIN
		SET @Msg = 'NotFound';
		RETURN
	END
	
	-- see if already active
	DECLARE @Inactive char(1);
	SELECT @Inactive = Inactive
		FROM WS_Account
		WHERE (Account_Id = @Account_Id);
	IF (@Inactive = 'N')
	BEGIN
		SET @Msg = 'Already';
		RETURN
	END
	
	-- activate the login	
    UPDATE WS_Account
		SET Inactive = 'N'
		WHERE (Account_Id = @Account_Id);
	SET @Msg = 'Activated';
END
GO
/****** Object:  StoredProcedure [dbo].[WS_Account_ADD]    Script Date: 03/15/2012 12:22:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[WS_Account_ADD]
    	@Customer_Number       varchar(15),
    	@Account_Name          varchar(50),
    	@Account_PWD           varchar(15),
    	@Customer_Contact_id   int,
	@viewfieldnotes		char(1),
    	@Inactive              char(1),
    	@Account_Id            int                OUTPUT
AS
	DECLARE @Customer_Id   int

    	IF @Account_Name IS NULL
     		BEGIN
        		RETURN 0
      		END
    	ELSE IF @Account_PWD IS NULL
      		BEGIN
        		RETURN 0
      		END
    	ELSE IF @Account_Name = ''
      		BEGIN
       		RETURN 0
      		END
   	ELSE IF @Account_PWD = ''
     		BEGIN
        		RETURN 0
      		END

    	EXEC Get_Customer_Id @Customer_Number, @Customer_Id OUTPUT
    	IF @Customer_Id = 1
      		BEGIN
        		RETURN 0
      		END

   	IF EXISTS (SELECT Account_Id FROM WS_Account WHERE Customer_Id = @Customer_Id AND Account_Name = @Account_Name)
      		BEGIN
       		RETURN 0
      		END    

    	INSERT INTO WS_Account (Customer_Id, Customer_Contact_Id, Account_Name, Account_PWD, Field_Comments,Inactive)
    	VALUES (@Customer_Id, @Customer_Contact_Id, @Account_Name, @Account_PWD, @viewfieldnotes, @Inactive)

    	IF @@ROWCOUNT = 1
      		BEGIN
        		SET @Account_Id = SCOPE_IDENTITY()
        		RETURN @Account_Id
      		END
    	ELSE
     		BEGIN
        		RETURN 0
		END
GO
/****** Object:  StoredProcedure [dbo].[WS_Login_Customer]    Script Date: 03/15/2012 12:22:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[WS_Login_Customer]
    @Account_Name          varchar(256),
    @Account_PWD           varchar(15),
    @Customer_Number       varchar(15)        OUTPUT,
    @Validate_Code         int                OUTPUT,
    @Account_Id            int                OUTPUT,
    @Customer_Id           int                = NULL OUTPUT,
    @Customer_Contact_Id   int                = NULL OUTPUT,
    @Field_Comments        char(1)            = NULL OUTPUT,
    @Last_Name             varchar(50)        = NULL OUTPUT,
    @First_Name            varchar(50)        = NULL OUTPUT,
    @Phone                 varchar(12)        = NULL OUTPUT,
    @E_Mail                varchar(50)        = NULL OUTPUT,
    @Full_Name             varchar(255)       = NULL OUTPUT,
    @Is_Master_Account     int                = NULL OUTPUT,
    @Master_Account_Id     int                = NULL OUTPUT,
    @Master_Account_Code   varchar(25)        = NULL OUTPUT,
    @Master_Account_Desc   varchar(50)        = NULL OUTPUT,
    @Billing_Contact       char(1)            = NULL OUTPUT,
    @Collection_Contact    char(1)            = NULL OUTPUT,
    @Invoice_Contact       char(1)            = NULL OUTPUT
AS
/*
    Validate Codes
    0 - Validation OK
    1 - Account name doesn't match email mode
    2 - Customer Id not found in AR_CUSTOMER
    3 - Customer Number invalid
    4 - Can't find account based on Customer Number and Customer Id
    5 - Account Inactive
    6 - Invalid Password
*/

    DECLARE @Master_id    int,
            @Master_Customer int

	IF (@Customer_Number IS NULL)
	BEGIN
		SELECT @Customer_Id = Customer_Id 
			FROM WS_Account
			WHERE (Account_Name = @Account_Name);

		IF (@Customer_Id IS NULL)
		BEGIN
			SET @Validate_Code = 1;
			RETURN @Validate_Code;
		END
		
		SELECT @Customer_Number = Customer_Number
			FROM AR_Customer
			WHERE (Customer_Id = @Customer_Id);
		
		IF (@Customer_Number IS NULL)
		BEGIN
			SET @Validate_Code = 2;
			RETURN @Validate_Code;
		END 
	END
	ELSE
	BEGIN
		EXEC [dbo].[Get_Customer_Id] @Customer_Number, @Customer_Id OUTPUT

		IF (@Customer_Id = 1)
		BEGIN
			SET @Validate_Code = 3
			RETURN @Validate_Code
		END
	END

    SELECT @Account_Id = Account_Id, @Field_Comments = Field_Comments
    FROM [dbo].WS_Account
    WHERE (Customer_Id = @Customer_Id) AND (Account_Name = @Account_Name)

    IF (@Account_Id IS NULL)
      BEGIN
        SET @Field_Comments = NULL
        SET @Customer_Id = NULL
        SET @Validate_Code = 4
      END
    ELSE
      BEGIN
        IF EXISTS (SELECT Account_Id FROM [dbo].WS_Account
                   WHERE Account_Id = @Account_Id AND Inactive = 'Y')
          BEGIN
            SET @Validate_Code = 5
          END
        ELSE
          BEGIN
            IF EXISTS (SELECT Account_Id FROM [dbo].WS_Account
                       WHERE Account_Id = @Account_Id AND Account_PWD = @Account_PWD)
              BEGIN
                SET @Customer_Contact_Id = (SELECT Customer_Contact_Id FROM [dbo].WS_Account
                                            WHERE Account_Id = @Account_Id)
                IF NULLIF(@Customer_Contact_Id,1) IS NOT NULL
                  BEGIN
                    SELECT @Full_Name = Contact_Name, @Last_Name = Last_Name, @First_Name = First_Name,
                           @Phone = Phone, @E_mail = E_Mail, @Collection_Contact = Collection_Contact,
                           @Invoice_Contact = Invoice_Contact
                    FROM [dbo].AR_Customer_Contact
                    WHERE Customer_Contact_Id = @Customer_Contact_Id

                    IF EXISTS(SELECT * FROM [dbo].AR_Bill_Contact WHERE Contact_Id = @Customer_Contact_Id) 
                        SET @Billing_Contact = 'Y'
                    ELSE
                        SET @Billing_Contact = 'N'
                  END
                ELSE
                  BEGIN
                    SELECT @Full_Name = '', @Last_Name = '', @First_Name = '',
                           @Phone = '', @E_mail = '', @Collection_Contact = 'N',
                           @Invoice_Contact = 'N', @Billing_Contact = 'N'
                  END
                SET @Validate_Code = 0
                SELECT @Master_id = Master_Account_Id FROM [dbo].AR_Customer WHERE Customer_Id = @Customer_Id
                IF NULLIF(@Master_Id,1) IS NOT NULL
                  BEGIN
                    SET @Master_Account_Id = @Master_Id
                    SELECT @Master_Customer = Customer_Id, @Master_Account_Code = Master_Account_Code,
                           @Master_Account_Desc = ma.[Description]
                    FROM [dbo].AR_Master_Account ma
                    WHERE Master_Account_Id = @Master_Id
                  END
                ELSE
                  BEGIN
                    SET @Master_Account_Id = 1
                  END
                IF EXISTS(SELECT customer_Id FROM [dbo].AR_Master_Account WHERE customer_Id = @Customer_Id)
                    SET @Is_Master_Account = 1
                ELSE
                    SET @Is_Master_Account = 0
              END
            ELSE
              BEGIN
                SET @Validate_Code = 6
              END
          END
      END
    RETURN @Validate_Code
GO
/****** Object:  StoredProcedure [dbo].[WS_Account_Register]    Script Date: 03/15/2012 12:22:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[WS_Account_Register]
    @customer_Number        varchar(15),
    @customer_verify        varchar(50),
    @account_name           varchar(50),
    @account_pwd            varchar(15),
    @viewfieldnotes         char(1),
    @inactive               char(1),
    @contact_name           varchar(255),
    @title                  varchar(50),
    @phone                  varchar(12),
    @extension              varchar(5),
    @fax                    varchar(12),
    @email                  varchar(50),
    @pager                  varchar(12),
    @all_sites              char(1),
    @salutation             varchar(10),
    @first_name             varchar(50),
    @middle                 varchar(50),
    @last_name              varchar(50),
    @suffix                 varchar(12),
    @mobile                 varchar(12),
    @collection_contact     char(1),
    @invoice_contact        char(1),
    @Notes                  varchar(255),
    @Contact_Id             int OUTPUT,
    @Bill_Contact_Id        int OUTPUT,
    @Account_Id             int OUTPUT,
    @ErrCode                varchar(50) OUTPUT
AS
BEGIN
	DECLARE @Customer_Id int;
    DECLARE @Bill_Id int;
    
    -- preset outputs as zero meaning error
    SET @Contact_Id = 0;
    SET @Bill_Contact_Id = 0;
    SET @Account_Id = 0;
    SET @ErrCode = 'OK';
    
    SELECT @customer_id = cu.Customer_Id, @Bill_Id = cb.Customer_Bill_Id
		FROM [dbo].AR_Customer cu INNER JOIN [dbo].AR_Customer_Bill cb 
			ON ((cu.Customer_Id = cb.customer_id) AND (cb.Is_Primary = 'Y'))
		WHERE ((cu.Customer_Number = @customer_number) AND (cb.GE3_Description = @customer_verify));
    IF @customer_id IS NULL
    BEGIN
		SET @ErrCode = 'CustIdNotFound';
        RETURN;
    END
    
    IF EXISTS(SELECT * FROM WS_Account WHERE (Account_Name = @account_name))
    BEGIN
		SET @ErrCode = 'AcctExists';
		RETURN;
	END
    
	BEGIN TRANSACTION;
	
    EXECUTE [dbo].[Customer_Contact_ADD] @contact_name, @title, @phone, @extension,
		@fax, @email, @pager, @all_sites, @salutation, @first_name,
        @middle, @last_name, @suffix, @mobile, @collection_contact,
        @invoice_contact, @notes, 'N', @Contact_Id OUTPUT;
    IF (@Contact_Id = 1)
    BEGIN
        ROLLBACK TRANSACTION;
        SET @ErrCode = 'ContAddFailed';
		RETURN;
    END

    EXECUTE [dbo].[Bill_Contact_ADD] @bill_id, @Contact_Id, @bill_contact_id OUTPUT;
    IF (@Bill_Contact_Id = 1)
    BEGIN
        ROLLBACK TRANSACTION;
        SET @ErrCode = 'BillContAddFailed';
		RETURN;
    END

    EXECUTE [dbo].[WS_Account_ADD] @Customer_Number, @Account_Name, @Account_PWD,
            @Contact_Id, @viewfieldnotes, @Inactive, @Account_Id OUTPUT;
    IF (@Account_Id = 0)
    BEGIN
        ROLLBACK TRANSACTION;
        SET @ErrCode = 'AcctAddFailed';
		RETURN;
    END
            
    COMMIT TRANSACTION;
END
GO
ALTER TABLE WS_Account
ALTER COLUMN Account_Name varchar(256) NOT NULL;

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WS_Account_Passwd]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WS_Account_Passwd]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WS_Account_Register]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WS_Account_Register]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WS_Account_Validate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WS_Account_Validate]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WS_Login_Customer]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WS_Login_Customer]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WS_Account_Add]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WS_Account_ADD]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WS_Account_Activate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WS_Account_Activate]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[WS_Account_Activate]
    @Account_Id int,
    @Msg varchar(50) output
AS
BEGIN
	-- see if account id exists?
    IF NOT EXISTS(SELECT * FROM WS_Account WHERE (Account_Id = @Account_Id))
    BEGIN
		SET @Msg = 'NotFound';
		RETURN
	END
	
	-- see if already active
	DECLARE @Inactive char(1);
	SELECT @Inactive = Inactive
		FROM WS_Account
		WHERE (Account_Id = @Account_Id);
	IF (@Inactive = 'N')
	BEGIN
		SET @Msg = 'Already';
		RETURN
	END
	
	-- activate the login	
    UPDATE WS_Account
		SET Inactive = 'N'
		WHERE (Account_Id = @Account_Id);
	SET @Msg = 'Activated';
END
GO
/****** Object:  StoredProcedure [dbo].[WS_Account_ADD]    Script Date: 04/05/2012 13:36:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[WS_Account_ADD]
	@Customer_Number varchar(15),
    @Account_Name varchar(256),
    @Account_PWD varchar(15),
    @Customer_Contact_id int,
	@viewfieldnotes char(1),
    @Inactive char(1),
    @Account_Id int OUTPUT
AS
BEGIN
	DECLARE @Customer_Id   int

	-- validate parms
  	IF (@Account_Name IS NULL)
	BEGIN
        RETURN 0;
    END
	IF (@Account_PWD IS NULL)
	BEGIN
 		RETURN 0;
	END
	IF (@Account_Name = '')
	BEGIN
		RETURN 0;
	END
   	IF (@Account_PWD = '')
	BEGIN
   		RETURN 0;
	END

	-- get the customer id
	EXEC Get_Customer_Id @Customer_Number, @Customer_Id OUTPUT;
	IF (@Customer_Id = 1)
	BEGIN
		RETURN 0;
	END

	-- does account already exist?
   	IF (EXISTS(
   		SELECT Account_Id 
   			FROM WS_Account 
   			WHERE (Customer_Id = @Customer_Id AND Account_Name = @Account_Name)))
	BEGIN
		RETURN 0;
	END    

	BEGIN TRANSACTION

	-- add the new account
	INSERT INTO WS_Account 
		(Customer_Id, Customer_Contact_Id, Account_Name, Account_PWD, Field_Comments,Inactive)
		VALUES (@Customer_Id, @Customer_Contact_Id, @Account_Name, @Account_PWD, @viewfieldnotes, @Inactive);
	IF (@@ROWCOUNT < 1)
	BEGIN 
		ROLLBACK TRANSACTION;
		RETURN 0;
	END

	-- save the account id for caller	
	SET @Account_Id = SCOPE_IDENTITY()
	
	-- connect to all the sites for the customer
	INSERT INTO AR_Site_Contact
		(Site_Id, Contact_Id)
		SELECT cs.Customer_Site_Id, @Customer_Contact_Id
			FROM AR_Customer_Site cs
			WHERE (Customer_Id = @Customer_Id);
	
	COMMIT TRANSACTION;
	
	RETURN @Account_Id
END
GO
/****** Object:  StoredProcedure [dbo].[WS_Login_Customer]    Script Date: 04/05/2012 13:36:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[WS_Login_Customer]
    @Account_Name          varchar(256),
    @Account_PWD           varchar(15),
    @Customer_Number       varchar(15)        OUTPUT,
    @Validate_Code         int                OUTPUT,
    @Account_Id            int                OUTPUT,
    @Customer_Id           int                = NULL OUTPUT,
    @Customer_Contact_Id   int                = NULL OUTPUT,
    @Field_Comments        char(1)            = NULL OUTPUT,
    @Last_Name             varchar(50)        = NULL OUTPUT,
    @First_Name            varchar(50)        = NULL OUTPUT,
    @Phone                 varchar(12)        = NULL OUTPUT,
    @E_Mail                varchar(50)        = NULL OUTPUT,
    @Full_Name             varchar(255)       = NULL OUTPUT,
    @Is_Master_Account     int                = NULL OUTPUT,
    @Master_Account_Id     int                = NULL OUTPUT,
    @Master_Account_Code   varchar(25)        = NULL OUTPUT,
    @Master_Account_Desc   varchar(50)        = NULL OUTPUT,
    @Billing_Contact       char(1)            = NULL OUTPUT,
    @Collection_Contact    char(1)            = NULL OUTPUT,
    @Invoice_Contact       char(1)            = NULL OUTPUT
AS
/*
    Validate Codes
    0 - Validation OK
    1 - Account name doesn't match email mode
    2 - Customer Id not found in AR_CUSTOMER
    3 - Customer Number invalid
    4 - Can't find account based on Customer Number and Customer Id
    5 - Account Inactive
    6 - Invalid Password
*/

    DECLARE @Master_id    int,
            @Master_Customer int

	IF (@Customer_Number IS NULL)
	BEGIN
		SELECT @Customer_Id = Customer_Id 
			FROM WS_Account
			WHERE (Account_Name = @Account_Name);

		IF (@Customer_Id IS NULL)
		BEGIN
			SET @Validate_Code = 1;
			RETURN @Validate_Code;
		END
		
		SELECT @Customer_Number = Customer_Number
			FROM AR_Customer
			WHERE (Customer_Id = @Customer_Id);
		
		IF (@Customer_Number IS NULL)
		BEGIN
			SET @Validate_Code = 2;
			RETURN @Validate_Code;
		END 
	END
	ELSE
	BEGIN
		EXEC [dbo].[Get_Customer_Id] @Customer_Number, @Customer_Id OUTPUT

		IF (@Customer_Id = 1)
		BEGIN
			SET @Validate_Code = 3
			RETURN @Validate_Code
		END
	END

    SELECT @Account_Id = Account_Id, @Field_Comments = Field_Comments
    FROM [dbo].WS_Account
    WHERE (Customer_Id = @Customer_Id) AND (Account_Name = @Account_Name)

    IF (@Account_Id IS NULL)
      BEGIN
        SET @Field_Comments = NULL
        SET @Customer_Id = NULL
        SET @Validate_Code = 4
      END
    ELSE
      BEGIN
        IF EXISTS (SELECT Account_Id FROM [dbo].WS_Account
                   WHERE Account_Id = @Account_Id AND Inactive = 'Y')
          BEGIN
            SET @Validate_Code = 5
          END
        ELSE
          BEGIN
            IF EXISTS (SELECT Account_Id FROM [dbo].WS_Account
                       WHERE Account_Id = @Account_Id AND Account_PWD = @Account_PWD)
              BEGIN
                SET @Customer_Contact_Id = (SELECT Customer_Contact_Id FROM [dbo].WS_Account
                                            WHERE Account_Id = @Account_Id)
                IF NULLIF(@Customer_Contact_Id,1) IS NOT NULL
                  BEGIN
                    SELECT @Full_Name = Contact_Name, @Last_Name = Last_Name, @First_Name = First_Name,
                           @Phone = Phone, @E_mail = E_Mail, @Collection_Contact = Collection_Contact,
                           @Invoice_Contact = Invoice_Contact
                    FROM [dbo].AR_Customer_Contact
                    WHERE Customer_Contact_Id = @Customer_Contact_Id

                    IF EXISTS(SELECT * FROM [dbo].AR_Bill_Contact WHERE Contact_Id = @Customer_Contact_Id) 
                        SET @Billing_Contact = 'Y'
                    ELSE
                        SET @Billing_Contact = 'N'
                  END
                ELSE
                  BEGIN
                    SELECT @Full_Name = '', @Last_Name = '', @First_Name = '',
                           @Phone = '', @E_mail = '', @Collection_Contact = 'N',
                           @Invoice_Contact = 'N', @Billing_Contact = 'N'
                  END
                SET @Validate_Code = 0
                SELECT @Master_id = Master_Account_Id FROM [dbo].AR_Customer WHERE Customer_Id = @Customer_Id
                IF NULLIF(@Master_Id,1) IS NOT NULL
                  BEGIN
                    SET @Master_Account_Id = @Master_Id
                    SELECT @Master_Customer = Customer_Id, @Master_Account_Code = Master_Account_Code,
                           @Master_Account_Desc = ma.[Description]
                    FROM [dbo].AR_Master_Account ma
                    WHERE Master_Account_Id = @Master_Id
                  END
                ELSE
                  BEGIN
                    SET @Master_Account_Id = 1
                  END
                IF EXISTS(SELECT customer_Id FROM [dbo].AR_Master_Account WHERE customer_Id = @Customer_Id)
                    SET @Is_Master_Account = 1
                ELSE
                    SET @Is_Master_Account = 0
              END
            ELSE
              BEGIN
                SET @Validate_Code = 6
              END
          END
      END
    RETURN @Validate_Code
GO
/****** Object:  StoredProcedure [dbo].[WS_Account_Validate]    Script Date: 04/05/2012 13:36:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[WS_Account_Validate]
    @Customer_Number       varchar(15),
    @Account_Name          varchar(256),
    @Account_PWD           varchar(15),
    @Validate_Code         int                OUTPUT,
    @Account_Id            int                OUTPUT,
    @Customer_Id           int                OUTPUT,
    @Customer_Contact_Id   int                OUTPUT
AS
/*
    Validate Codes
    0:    Validation OK
    1:    Unknown Account (Or Customer)
    2:    Account Disabled
    3:    Invalid Password
    4:    Internal Error
*/

    EXEC Get_Customer_Id @Customer_Number, @Customer_Id OUTPUT

    IF (@Customer_Id = 1)
      BEGIN
        SET @Validate_Code = 1
        RETURN @Validate_Code
      END

    SET @Account_Id = (SELECT Account_Id FROM WS_Account
                       WHERE Customer_Id = @Customer_Id AND Account_Name = @Account_Name)

    IF (@Account_Id IS NULL)
      BEGIN
        SET @Customer_Id = NULL
        SET @Validate_Code = 1
      END
    ELSE
      BEGIN
        IF EXISTS (SELECT Account_Id FROM WS_Account
                   WHERE Account_Id = @Account_Id AND Inactive = 'Y')
          BEGIN
            SET @Validate_Code = 2
          END
        ELSE
          BEGIN
            IF EXISTS (SELECT Account_Id FROM WS_Account
                       WHERE Account_Id = @Account_Id AND Account_PWD = @Account_PWD)
              BEGIN
                SET @Customer_Contact_Id = (SELECT Customer_Contact_Id FROM WS_Account
                                            WHERE Account_Id = @Account_Id)
                SET @Validate_Code = 0
              END
            ELSE
              BEGIN
                SET @Validate_Code = 3
              END
          END
      END
    RETURN @Validate_Code
GO
/****** Object:  StoredProcedure [dbo].[WS_Account_Register]    Script Date: 04/05/2012 13:36:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[WS_Account_Register]
    @customer_Number        varchar(15),
    @customer_verify        varchar(50),
    @account_name           varchar(50),
    @account_pwd            varchar(15),
    @viewfieldnotes         char(1),
    @inactive               char(1),
    @contact_name           varchar(255),
    @title                  varchar(50),
    @phone                  varchar(12),
    @extension              varchar(5),
    @fax                    varchar(12),
    @email                  varchar(50),
    @pager                  varchar(12),
    @all_sites              char(1),
    @salutation             varchar(10),
    @first_name             varchar(50),
    @middle                 varchar(50),
    @last_name              varchar(50),
    @suffix                 varchar(12),
    @mobile                 varchar(12),
    @collection_contact     char(1),
    @invoice_contact        char(1),
    @Notes                  varchar(255),
    @Contact_Id             int OUTPUT,
    @Bill_Contact_Id        int OUTPUT,
    @Account_Id             int OUTPUT,
    @ErrCode                varchar(50) OUTPUT
AS
BEGIN
	DECLARE @Customer_Id int;
    DECLARE @Bill_Id int;
    
    -- preset outputs as zero meaning error
    SET @Contact_Id = 0;
    SET @Bill_Contact_Id = 0;
    SET @Account_Id = 0;
    SET @ErrCode = 'OK';
    
    SELECT @customer_id = cu.Customer_Id, @Bill_Id = cb.Customer_Bill_Id
		FROM [dbo].AR_Customer cu INNER JOIN [dbo].AR_Customer_Bill cb 
			ON ((cu.Customer_Id = cb.customer_id) AND (cb.Is_Primary = 'Y'))
		WHERE ((cu.Customer_Number = @customer_number) AND (cb.GE3_Description = @customer_verify));
    IF @customer_id IS NULL
    BEGIN
		SET @ErrCode = 'CustIdNotFound';
        RETURN;
    END
    
    IF EXISTS(SELECT * FROM WS_Account WHERE (Account_Name = @account_name))
    BEGIN
		SET @ErrCode = 'AcctExists';
		RETURN;
	END
    
	BEGIN TRANSACTION;
	
    EXECUTE [dbo].[Customer_Contact_ADD] @contact_name, @title, @phone, @extension,
		@fax, @email, @pager, @all_sites, @salutation, @first_name,
        @middle, @last_name, @suffix, @mobile, @collection_contact,
        @invoice_contact, @notes, 'N', @Contact_Id OUTPUT;
    IF (@Contact_Id = 1)
    BEGIN
        ROLLBACK TRANSACTION;
        SET @ErrCode = 'ContAddFailed';
		RETURN;
    END

    EXECUTE [dbo].[Bill_Contact_ADD] @bill_id, @Contact_Id, @bill_contact_id OUTPUT;
    IF (@Bill_Contact_Id = 1)
    BEGIN
        ROLLBACK TRANSACTION;
        SET @ErrCode = 'BillContAddFailed';
		RETURN;
    END

    EXECUTE [dbo].[WS_Account_ADD] @Customer_Number, @Account_Name, @Account_PWD,
            @Contact_Id, @viewfieldnotes, @Inactive, @Account_Id OUTPUT;
    IF (@Account_Id = 0)
    BEGIN
        ROLLBACK TRANSACTION;
        SET @ErrCode = 'AcctAddFailed';
		RETURN;
    END
            
    COMMIT TRANSACTION;
END
GO
/****** Object:  StoredProcedure [dbo].[WS_Account_Passwd]    Script Date: 04/05/2012 13:36:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[WS_Account_Passwd]
    @Customer_Number       varchar(15),
    @Account_Name          varchar(256),
    @Password_Old          varchar(15),
    @Password_New          varchar(15)
AS
    DECLARE @Account_Id    int,
            @Validate_Code int,
            @Customer_Id   int,
            @Customer_Contact_Id   int

    EXECUTE WS_Account_Validate @Customer_Number, @Account_Name, @Password_Old,
                                @Validate_Code OUTPUT, @Account_Id OUTPUT, 
                                @Customer_Id OUTPUT, @Customer_Contact_Id OUTPUT

    IF @Validate_Code = 0
      BEGIN
        IF @Password_New IS NULL
          BEGIN
            SET @Validate_Code = 3
          END
        ELSE IF @Password_New = ''
          BEGIN
            SET @Validate_Code = 3
          END
        ELSE
          BEGIN
            UPDATE WS_Account
            SET Account_PWD = @Password_New
            WHERE Account_Id = @Account_Id
            
            IF @@ERROR <> 0
              BEGIN
                SET @Validate_Code = 4
              END
          END
      END

    RETURN @Validate_Code
GO
--ALTER TABLE WS_Account
-- DROP COLUMN Field_Comments;
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WS_Page]') AND type in (N'U'))
DROP TABLE WS_Page
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WS_Page_Controls]') AND type in (N'U'))
DROP TABLE WS_PageControls
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WS_Partner]') AND type in (N'U'))
DROP TABLE WS_Partner
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WS_Setup]') AND type in (N'U'))
DROP TABLE WS_Setup
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WS_Account_Convert]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[WS_Account_Convert]
GO
CREATE PROCEDURE [dbo].[WS_Account_Convert] 
	@Account_Id int, 
	@Customer_Contact_Id int,
    @New_Account_Name varchar(256),
    @ErrorCode varchar(25) OUTPUT
AS
BEGIN
	DECLARE @Contact_Email as varchar(256);
	
	SELECT @Contact_Email = E_Mail
	FROM AR_Customer_Contact
	WHERE Customer_Contact_Id = @Customer_Contact_Id
	
	SET @ErrorCode = 'OK';
    IF (1 !=
    (SELECT COUNT(*)
       FROM WS_Account
       WHERE Account_Id = @Account_Id))
    BEGIN
		SET @ErrorCode = 'AccountNotFound';
        RETURN;
    END
    -- Does account with that email already exist?
    IF (EXISTS(
        SELECT Account_Id 
			FROM WS_Account 
            WHERE (Account_Name = @New_Account_Name)))
    BEGIN
        SET @ErrorCode = 'EmailAlreadyExists';
        RETURN;
    END    
    IF ((@Contact_Email IS NULL) OR (@Contact_Email = ''))
    BEGIN
		UPDATE AR_Customer_Contact
		SET E_Mail = @New_Account_Name
		WHERE Customer_Contact_Id = @Customer_Contact_Id;
    END
    
    UPDATE WS_Account
		SET Account_Name = @New_Account_Name,
		Inactive = 'Y'
		WHERE Account_Id = @Account_Id;
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WS_Login_Customer]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[WS_Login_Customer]
GO
CREATE PROCEDURE [dbo].[WS_Login_Customer]
    @Account_Name          varchar(256),
    @Account_PWD           varchar(15),
    @Customer_Number       varchar(15)        OUTPUT,
    @Validate_Code         int                OUTPUT,
    @Account_Id            int                OUTPUT,
    @Customer_Id           int                = NULL OUTPUT,
    @Customer_Contact_Id   int                = NULL OUTPUT,
    @Last_Name             varchar(50)        = NULL OUTPUT,
    @First_Name            varchar(50)        = NULL OUTPUT,
    @Phone                 varchar(12)        = NULL OUTPUT,
    @E_Mail                varchar(50)        = NULL OUTPUT,
    @Full_Name             varchar(255)       = NULL OUTPUT,
    @Is_Master_Account     int                = NULL OUTPUT,
    @Master_Account_Id     int                = NULL OUTPUT,
    @Master_Account_Code   varchar(25)        = NULL OUTPUT,
    @Master_Account_Desc   varchar(50)        = NULL OUTPUT,
    @Billing_Contact       char(1)            = NULL OUTPUT,
    @Collection_Contact    char(1)            = NULL OUTPUT,
    @Invoice_Contact       char(1)            = NULL OUTPUT
AS
BEGIN
	/*
    Validate Codes
    0 - Validation OK
    1 - Account name doesn't match email mode
    2 - Customer Id not found in AR_CUSTOMER
    3 - Customer Number invalid
    4 - Can't find account based on Customer Number and Customer Id
    5 - Account Inactive
    6 - Invalid Password
	*/

    DECLARE @Master_id    int,
            @Master_Customer int

	IF (@Customer_Number IS NULL)
	BEGIN
		SELECT @Customer_Id = Customer_Id 
			FROM WS_Account
			WHERE (Account_Name = @Account_Name);

		IF (@Customer_Id IS NULL)
		BEGIN
			SET @Validate_Code = 1;
			RETURN @Validate_Code;
		END
		
		SELECT @Customer_Number = Customer_Number
			FROM AR_Customer
			WHERE (Customer_Id = @Customer_Id);
		
		IF (@Customer_Number IS NULL)
		BEGIN
			SET @Validate_Code = 2;
			RETURN @Validate_Code;
		END 
	END
	ELSE
	BEGIN
		EXEC [dbo].[Get_Customer_Id] @Customer_Number, @Customer_Id OUTPUT

		IF (@Customer_Id = 1)
		BEGIN
			SET @Validate_Code = 3
			RETURN @Validate_Code
		END
	END

    SELECT @Account_Id = Account_Id
      FROM [dbo].WS_Account
      WHERE (Customer_Id = @Customer_Id) AND (Account_Name = @Account_Name)

    IF (@Account_Id IS NULL)
      BEGIN
        SET @Customer_Id = NULL
        SET @Validate_Code = 4
      END
    ELSE
      BEGIN
        IF EXISTS (SELECT Account_Id FROM [dbo].WS_Account
                   WHERE Account_Id = @Account_Id AND Inactive = 'Y')
          BEGIN
            SET @Validate_Code = 5
          END
        ELSE
          BEGIN
            IF EXISTS (SELECT Account_Id FROM [dbo].WS_Account
                       WHERE Account_Id = @Account_Id AND Account_PWD = @Account_PWD)
              BEGIN
                SET @Customer_Contact_Id = (SELECT Customer_Contact_Id FROM [dbo].WS_Account
                                            WHERE Account_Id = @Account_Id)
                IF NULLIF(@Customer_Contact_Id,1) IS NOT NULL
                  BEGIN
                    SELECT @Full_Name = Contact_Name, @Last_Name = Last_Name, @First_Name = First_Name,
                           @Phone = Phone, @E_mail = E_Mail, @Collection_Contact = Collection_Contact,
                           @Invoice_Contact = Invoice_Contact
                    FROM [dbo].AR_Customer_Contact
                    WHERE Customer_Contact_Id = @Customer_Contact_Id

                    IF EXISTS(SELECT * FROM [dbo].AR_Bill_Contact WHERE Contact_Id = @Customer_Contact_Id) 
                        SET @Billing_Contact = 'Y'
                    ELSE
                        SET @Billing_Contact = 'N'
                  END
                ELSE
                  BEGIN
                    SELECT @Full_Name = '', @Last_Name = '', @First_Name = '',
                           @Phone = '', @E_mail = '', @Collection_Contact = 'N',
                           @Invoice_Contact = 'N', @Billing_Contact = 'N'
                  END
                SET @Validate_Code = 0
                SELECT @Master_id = Master_Account_Id FROM [dbo].AR_Customer WHERE Customer_Id = @Customer_Id
                IF NULLIF(@Master_Id,1) IS NOT NULL
                  BEGIN
                    SET @Master_Account_Id = @Master_Id
                    SELECT @Master_Customer = Customer_Id, @Master_Account_Code = Master_Account_Code,
                           @Master_Account_Desc = ma.[Description]
                    FROM [dbo].AR_Master_Account ma
                    WHERE Master_Account_Id = @Master_Id
                  END
                ELSE
                  BEGIN
                    SET @Master_Account_Id = 1
                  END
                IF EXISTS(SELECT customer_Id FROM [dbo].AR_Master_Account WHERE customer_Id = @Customer_Id)
                    SET @Is_Master_Account = 1
                ELSE
                    SET @Is_Master_Account = 0
              END
            ELSE
              BEGIN
                SET @Validate_Code = 6
              END
          END
      END
    RETURN @Validate_Code
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WS_Account_UPD]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[WS_Account_UPD]
GO
CREATE PROCEDURE [dbo].[WS_Account_UPD]

    @account_id			int,
    @Customer_Number       	varchar(15),
    @Account_Name         	 varchar(15),
    @Account_PWD           	varchar(15),
    @Customer_Contact_id 	int,
    @Inactive              		char(1)

AS
BEGIN	
	DECLARE @Customer_Id   int

  	IF @Account_Name IS NULL
 	BEGIN
		RETURN 0
	END
    ELSE IF @Account_PWD IS NULL
    BEGIN
		RETURN 0
	END
	ELSE IF @Account_Name = ''
    BEGIN
		RETURN 0
	END
    ELSE IF @Account_PWD = ''
	BEGIN
		RETURN 0
	END

	EXEC Get_Customer_Id @Customer_Number, @Customer_Id OUTPUT

	IF @Customer_Id = 1
	BEGIN
		RETURN 0
	END

	UPDATE WS_Account
	SET Customer_Id = @customer_id,
		Customer_Contact_Id = @customer_contact_id,
		Account_Name = @account_name,
		Account_PWD = @account_pwd,
		Inactive = @inactive
	WHERE Account_Id = @account_id
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WS_Account_Register]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[WS_Account_Register]
GO
CREATE PROCEDURE [dbo].[WS_Account_Register]
    @customer_Number        varchar(15),
    @customer_verify        varchar(50),
    @account_name           varchar(50),
    @account_pwd            varchar(15),
    @inactive               char(1),
    @contact_name           varchar(255),
    @title                  varchar(50),
    @phone                  varchar(12),
    @extension              varchar(5),
    @fax                    varchar(12),
    @email                  varchar(50),
    @pager                  varchar(12),
    @all_sites              char(1),
    @salutation             varchar(10),
    @first_name             varchar(50),
    @middle                 varchar(50),
    @last_name              varchar(50),
    @suffix                 varchar(12),
    @mobile                 varchar(12),
    @collection_contact     char(1),
    @invoice_contact        char(1),
    @Notes                  varchar(255),
    @Contact_Id             int OUTPUT,
    @Bill_Contact_Id        int OUTPUT,
    @Account_Id             int OUTPUT,
    @ErrCode                varchar(50) OUTPUT
AS
BEGIN
	DECLARE @Customer_Id int;
    DECLARE @Bill_Id int;
    
    -- preset outputs as zero meaning error
    SET @Contact_Id = 0;
    SET @Bill_Contact_Id = 0;
    SET @Account_Id = 0;
    SET @ErrCode = 'OK';
    
    SELECT @customer_id = cu.Customer_Id, @Bill_Id = cb.Customer_Bill_Id
		FROM [dbo].AR_Customer cu INNER JOIN [dbo].AR_Customer_Bill cb 
			ON ((cu.Customer_Id = cb.customer_id) AND (cb.Is_Primary = 'Y'))
		WHERE ((cu.Customer_Number = @customer_number) AND (cb.GE3_Description = @customer_verify));
    IF @customer_id IS NULL
    BEGIN
		SET @ErrCode = 'CustIdNotFound';
        RETURN;
    END
    
    IF EXISTS(SELECT * FROM WS_Account WHERE (Account_Name = @account_name))
    BEGIN
		SET @ErrCode = 'AcctExists';
		RETURN;
	END
    
	BEGIN TRANSACTION;
	
    EXECUTE [dbo].[Customer_Contact_ADD] @contact_name, @title, @phone, @extension,
		@fax, @email, @pager, @all_sites, @salutation, @first_name,
        @middle, @last_name, @suffix, @mobile, @collection_contact,
        @invoice_contact, @notes, 'N', @Contact_Id OUTPUT;
    IF (@Contact_Id = 1)
    BEGIN
        ROLLBACK TRANSACTION;
        SET @ErrCode = 'ContAddFailed';
		RETURN;
    END

    EXECUTE [dbo].[Bill_Contact_ADD] @bill_id, @Contact_Id, @bill_contact_id OUTPUT;
    IF (@Bill_Contact_Id = 1)
    BEGIN
        ROLLBACK TRANSACTION;
        SET @ErrCode = 'BillContAddFailed';
		RETURN;
    END

    EXECUTE [dbo].[WS_Account_ADD] @Customer_Number, @Account_Name, @Account_PWD,
            @Contact_Id, @Inactive, @Account_Id OUTPUT;
    IF (@Account_Id = 0)
    BEGIN
        ROLLBACK TRANSACTION;
        SET @ErrCode = 'AcctAddFailed';
		RETURN;
    END
            
    COMMIT TRANSACTION;
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WS_Account_ADD]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[WS_Account_ADD]
GO
CREATE PROCEDURE [dbo].[WS_Account_ADD]
	@Customer_Number varchar(15),
    @Account_Name varchar(256),
    @Account_PWD varchar(15),
    @Customer_Contact_id int,
    @Inactive char(1),
    @Account_Id int OUTPUT
AS
BEGIN
	DECLARE @Customer_Id   int

	-- validate parms
  	IF (@Account_Name IS NULL)
	BEGIN
        RETURN 0;
    END
	IF (@Account_PWD IS NULL)
	BEGIN
 		RETURN 0;
	END
	IF (@Account_Name = '')
	BEGIN
		RETURN 0;
	END
   	IF (@Account_PWD = '')
	BEGIN
   		RETURN 0;
	END

	-- get the customer id
	EXEC Get_Customer_Id @Customer_Number, @Customer_Id OUTPUT;
	IF (@Customer_Id = 1)
	BEGIN
		RETURN 0;
	END

	-- does account already exist?
   	IF (EXISTS(
   		SELECT Account_Id 
   			FROM WS_Account 
   			WHERE (Customer_Id = @Customer_Id AND Account_Name = @Account_Name)))
	BEGIN
		RETURN 0;
	END    

	BEGIN TRANSACTION

	-- add the new account
	INSERT INTO WS_Account 
		(Customer_Id, Customer_Contact_Id, Account_Name, Account_PWD, Inactive)
		VALUES (@Customer_Id, @Customer_Contact_Id, @Account_Name, @Account_PWD, @Inactive);
	IF (@@ROWCOUNT < 1)
	BEGIN 
		ROLLBACK TRANSACTION;
		RETURN 0;
	END

	-- save the account id for caller	
	SET @Account_Id = SCOPE_IDENTITY()
	
	-- connect to all the sites for the customer
	INSERT INTO AR_Site_Contact
		(Site_Id, Contact_Id)
		SELECT cs.Customer_Site_Id, @Customer_Contact_Id
			FROM AR_Customer_Site cs
			WHERE (Customer_Id = @Customer_Id);
	
	COMMIT TRANSACTION;
	
	RETURN @Account_Id
END
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WS_Account_UPD]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[WS_Account_UPD]
GO
CREATE PROCEDURE [dbo].[WS_Account_UPD]

    @account_id			int,
    @Customer_Number       	varchar(15),
    @Account_Name         	 varchar(256),
    @Account_PWD           	varchar(15),
    @Customer_Contact_id 	int,
    @Inactive              		char(1)

AS
BEGIN	
	DECLARE @Customer_Id   int

  	IF @Account_Name IS NULL
 	BEGIN
		RETURN 0
	END
    ELSE IF @Account_PWD IS NULL
    BEGIN
		RETURN 0
	END
	ELSE IF @Account_Name = ''
    BEGIN
		RETURN 0
	END
    ELSE IF @Account_PWD = ''
	BEGIN
		RETURN 0
	END

	EXEC Get_Customer_Id @Customer_Number, @Customer_Id OUTPUT

	IF @Customer_Id = 1
	BEGIN
		RETURN 0
	END

	UPDATE WS_Account
	SET Customer_Id = @customer_id,
		Customer_Contact_Id = @customer_contact_id,
		Account_Name = @account_name,
		Account_PWD = @account_pwd,
		Inactive = @inactive
	WHERE Account_Id = @account_id
END
GO
/****** Object:  Table [dbo].[WS_CustomerList]    Script Date: 09/21/2012 10:07:16 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WS_CustomerList]') AND type in (N'U'))
DROP TABLE [dbo].[WS_CustomerList]
GO

/****** Object:  Table [dbo].[WS_CustomerList]    Script Date: 09/21/2012 10:07:16 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[WS_CustomerList](
	[CustomerList_Id] [int] IDENTITY(1,1) NOT NULL,
	[Account_Id] [int] NOT NULL,
	[Customer_Id] [int] NOT NULL,
	[Customer_Contact_Id] [int] NOT NULL
) ON [PRIMARY]

GO

INSERT INTO WS_CustomerList
 (Account_Id, Customer_Id, Customer_Contact_Id)
 SELECT Account_Id, Customer_Id, Customer_Contact_Id
  FROM WS_Account;

/****** Object:  StoredProcedure [dbo].[WS_Account_ADD]    Script Date: 09/20/2012 16:36:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WS_Account_ADD]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WS_Account_ADD]
GO

/****** Object:  StoredProcedure [dbo].[WS_Account_ADD]    Script Date: 09/20/2012 16:36:31 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[WS_Account_ADD]
	@Customer_Number varchar(15),
    @Account_Name varchar(256),
    @Account_PWD varchar(15),
    @Customer_Contact_id int,
    @Inactive char(1),
    @Account_Id int OUTPUT
  AS
BEGIN
	DECLARE @Customer_Id   int

	-- validate parms
  	IF (@Account_Name IS NULL)
	BEGIN
        RETURN 0;
    END
	IF (@Account_PWD IS NULL)
	BEGIN
 		RETURN 0;
	END
	IF (@Account_Name = '')
	BEGIN
		RETURN 0;
	END
   	IF (@Account_PWD = '')
	BEGIN
   		RETURN 0;
	END

	-- get the customer id
	EXEC Get_Customer_Id @Customer_Number, @Customer_Id OUTPUT;
	IF (@Customer_Id = 1)
	BEGIN
		RETURN 0;
	END

	-- does account already exist?
	IF(@Inactive = 'Y')
	BEGIN
   		IF (EXISTS(
   			SELECT Account_Id 
   				FROM WS_Account 
   				WHERE (Customer_Id = @Customer_Id AND Account_Name = @Account_Name)))
		BEGIN
			RETURN 0;
		END    

		BEGIN TRANSACTION

		-- add the new account
		INSERT INTO WS_Account 
			(Customer_Id, Customer_Contact_Id, Account_Name, Account_PWD, Inactive)
			VALUES (@Customer_Id, @Customer_Contact_Id, @Account_Name, @Account_PWD, @Inactive);
		IF (@@ROWCOUNT < 1)
		BEGIN 
			ROLLBACK TRANSACTION;
			RETURN 0;
		END
		
		-- save the account id for caller	
		SET @Account_Id = SCOPE_IDENTITY()
		-- add the account to the customerList
		INSERT INTO WS_CustomerList
		(Account_Id, Customer_Id, Customer_Contact_Id)
		VALUES (@Account_Id, @Customer_Id, @Customer_Contact_Id);
	END

	-- connect to all the sites for the customer
	INSERT INTO AR_Site_Contact
		(Site_Id, Contact_Id)
		SELECT cs.Customer_Site_Id, @Customer_Contact_Id
			FROM AR_Customer_Site cs
			WHERE (Customer_Id = @Customer_Id);
	
	COMMIT TRANSACTION;
	
	RETURN @Account_Id
END

GO

/****** Object:  StoredProcedure [dbo].[WS_Login_Customer]    Script Date: 09/20/2012 16:43:03 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WS_Login_Customer]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WS_Login_Customer]
GO

/****** Object:  StoredProcedure [dbo].[WS_Login_Customer]    Script Date: 09/20/2012 16:43:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[WS_Login_Customer]
	@Account_Name			varchar(256),
    @Account_PWD			varchar(15),
    @Customer_Number		varchar(15)			OUTPUT,
    @Validate_Code			int					OUTPUT,
    @Account_Id				int					OUTPUT,
    @Customer_Id			int					= NULL OUTPUT,
    @Customer_Contact_Id	int					= NULL OUTPUT,
    @Last_Name				varchar(50)			= NULL OUTPUT,
    @First_Name				varchar(50)			= NULL OUTPUT,
    @Phone					varchar(12)			= NULL OUTPUT,
    @E_Mail					varchar(50)			= NULL OUTPUT,
    @Full_Name				varchar(255)		= NULL OUTPUT,
    @Is_Master_Account		int					= NULL OUTPUT,
    @Master_Account_Id		int					= NULL OUTPUT,
    @Master_Account_Code	varchar(25)			= NULL OUTPUT,
    @Master_Account_Desc	varchar(50)			= NULL OUTPUT,
    @Billing_Contact		char(1)				= NULL OUTPUT,
    @Collection_Contact		char(1)				= NULL OUTPUT,
    @Invoice_Contact		char(1)				= NULL OUTPUT
AS
BEGIN
	/*
    Validate Codes
    0 - Validation OK
    1 - Account name doesn't match email mode
    2 - Customer Id not found in AR_CUSTOMER
    3 - Customer Number invalid
    4 - Can't find account based on Customer Number and Customer Id
    5 - Account Inactive
    6 - Invalid Password
	*/

    DECLARE @Master_id    int,
            @Master_Customer int

	IF (@Customer_Number IS NULL)
	BEGIN
		SELECT @Customer_Id = Customer_Id 
			FROM WS_Account
			WHERE (Account_Name = @Account_Name);

		IF (@Customer_Id IS NULL)
		BEGIN
			SET @Validate_Code = 1;
			RETURN @Validate_Code;
		END
		
		SELECT @Customer_Number = Customer_Number
			FROM AR_Customer
			WHERE (Customer_Id = @Customer_Id);
		
		IF (@Customer_Number IS NULL)
		BEGIN
			SET @Validate_Code = 2;
			RETURN @Validate_Code;
		END 
	END
	ELSE
	BEGIN
		EXEC [dbo].[Get_Customer_Id] @Customer_Number, @Customer_Id OUTPUT

		IF (@Customer_Id = 1)
		BEGIN
			SET @Validate_Code = 3
			RETURN @Validate_Code
		END
	END

    SELECT @Account_Id = Account_Id
      FROM [dbo].WS_Account
      WHERE (Customer_Id = @Customer_Id) AND (Account_Name = @Account_Name)

    IF (@Account_Id IS NULL)
      BEGIN
        SET @Customer_Id = NULL
        SET @Validate_Code = 4
      END
    ELSE
      BEGIN
        IF EXISTS (SELECT Account_Id FROM [dbo].WS_Account
                   WHERE Account_Id = @Account_Id AND Inactive = 'Y')
          BEGIN
            SET @Validate_Code = 5
          END
        ELSE
          BEGIN
            IF EXISTS(
             SELECT Account_Id 
              FROM [dbo].WS_Account
              WHERE 
               (
                (Account_Id = @Account_Id) AND
                (Account_PWD = @Account_PWD)
               )
               OR
               (
				 @Account_PWD = '!!skip()check!!'
               ))
              BEGIN
                SET @Customer_Contact_Id = (SELECT Customer_Contact_Id FROM [dbo].WS_Account
                                            WHERE Account_Id = @Account_Id)
                IF NULLIF(@Customer_Contact_Id,1) IS NOT NULL
                  BEGIN
                    SELECT @Full_Name = Contact_Name, @Last_Name = Last_Name, @First_Name = First_Name,
                           @Phone = Phone, @E_mail = E_Mail, @Collection_Contact = Collection_Contact,
                           @Invoice_Contact = Invoice_Contact
                    FROM [dbo].AR_Customer_Contact
                    WHERE Customer_Contact_Id = @Customer_Contact_Id

                    IF EXISTS(SELECT * FROM [dbo].AR_Bill_Contact WHERE Contact_Id = @Customer_Contact_Id) 
                        SET @Billing_Contact = 'Y'
                    ELSE
                        SET @Billing_Contact = 'N'
                  END
                ELSE
                  BEGIN
                    SELECT @Full_Name = '', @Last_Name = '', @First_Name = '',
                           @Phone = '', @E_mail = '', @Collection_Contact = 'N',
                           @Invoice_Contact = 'N', @Billing_Contact = 'N'
                  END
                SET @Validate_Code = 0
                SELECT @Master_id = Master_Account_Id FROM [dbo].AR_Customer WHERE Customer_Id = @Customer_Id
                IF NULLIF(@Master_Id,1) IS NOT NULL
                  BEGIN
                    SET @Master_Account_Id = @Master_Id
                    SELECT @Master_Customer = Customer_Id, @Master_Account_Code = Master_Account_Code,
                           @Master_Account_Desc = ma.[Description]
                    FROM [dbo].AR_Master_Account ma
                    WHERE Master_Account_Id = @Master_Id
                  END
                ELSE
                  BEGIN
                    SET @Master_Account_Id = 1
                  END
                IF EXISTS(SELECT customer_Id FROM [dbo].AR_Master_Account WHERE customer_Id = @Customer_Id)
                    SET @Is_Master_Account = 1
                ELSE
                    SET @Is_Master_Account = 0
              END
            ELSE
              BEGIN
                SET @Validate_Code = 6
              END
          END
      END
    RETURN @Validate_Code
END

GO
/****** Object:  StoredProcedure [dbo].[WS_Account_Register]    Script Date: 09/28/2012 09:42:51 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WS_Account_Register]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WS_Account_Register]
GO

/****** Object:  StoredProcedure [dbo].[WS_Account_Register]    Script Date: 09/28/2012 09:42:51 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[WS_Account_Register]
    @customer_Number        varchar(15),
    @customer_verify        varchar(50),
    @account_name           varchar(50),
    @account_pwd            varchar(15),
    @inactive               char(1),
    @contact_name           varchar(255),
    @title                  varchar(50),
    @phone                  varchar(12),
    @extension              varchar(5),
    @fax                    varchar(12),
    @email                  varchar(50),
    @pager                  varchar(12),
    @all_sites              char(1),
    @salutation             varchar(10),
    @first_name             varchar(50),
    @middle                 varchar(50),
    @last_name              varchar(50),
    @suffix                 varchar(12),
    @mobile                 varchar(12),
    @collection_contact     char(1),
    @invoice_contact        char(1),
    @Notes                  varchar(255),
    @Contact_Id             int OUTPUT,
    @Bill_Contact_Id        int OUTPUT,
    @Account_Id             int OUTPUT,
    @ErrCode                varchar(50) OUTPUT
AS
BEGIN
	DECLARE @Customer_Id int;
    DECLARE @Bill_Id int;
    
    -- preset outputs as zero meaning error
    SET @Contact_Id = 0;
    SET @Bill_Contact_Id = 0;
    SET @Account_Id = 0;
    SET @ErrCode = 'OK';
    
    SELECT @customer_id = cu.Customer_Id, @Bill_Id = cb.Customer_Bill_Id
		FROM [dbo].AR_Customer cu INNER JOIN [dbo].AR_Customer_Bill cb 
			ON ((cu.Customer_Id = cb.customer_id) AND (cb.Is_Primary = 'Y'))
		WHERE ((cu.Customer_Number = @customer_number) AND (cb.GE3_Description = @customer_verify));
    IF @customer_id IS NULL
    BEGIN
		SET @ErrCode = 'CustIdNotFound';
        RETURN;
    END
    
    IF EXISTS(SELECT * FROM WS_Account WHERE (Account_Name = @account_name))
    BEGIN
		SET @ErrCode = 'AcctExists';
		RETURN;
	END
    
	BEGIN TRANSACTION;
	
    EXECUTE [dbo].[Customer_Contact_ADD] @contact_name, @title, @phone, @extension,
		@fax, @email, @pager, @all_sites, @salutation, @first_name,
        @middle, @last_name, @suffix, @mobile, @collection_contact,
        @invoice_contact, @notes, 'N', @Contact_Id OUTPUT;
    IF (@Contact_Id = 1)
    BEGIN
        ROLLBACK TRANSACTION;
        SET @ErrCode = 'ContAddFailed';
		RETURN;
    END

    EXECUTE [dbo].[Bill_Contact_ADD] @bill_id, @Contact_Id, @bill_contact_id OUTPUT;
    IF (@Bill_Contact_Id = 1)
    BEGIN
        ROLLBACK TRANSACTION;
        SET @ErrCode = 'BillContAddFailed';
		RETURN;
    END
    
	IF(@inactive = 'Y')
	BEGIN
		EXECUTE [dbo].[WS_Account_ADD] @Customer_Number, @Account_Name, @Account_PWD,
				@Contact_Id, @Inactive, @Account_Id OUTPUT;
		IF (@Account_Id = 0)
		BEGIN
			ROLLBACK TRANSACTION;
			SET @ErrCode = 'AcctAddFailed';
			RETURN;
		END
    END
	ELSE
    INSERT INTO AR_Site_Contact
		(Site_Id, Contact_Id)
		SELECT cs.Customer_Site_Id, @Contact_Id
			FROM AR_Customer_Site cs
			WHERE (Customer_Id = @customer_Id); 
			
    COMMIT TRANSACTION;
END

GO

/****** Object:  StoredProcedure [dbo].[WS_Account_ADD]    Script Date: 09/28/2012 09:43:06 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WS_Account_ADD]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WS_Account_ADD]
GO
/****** Object:  StoredProcedure [dbo].[WS_Account_ADD]    Script Date: 09/28/2012 09:43:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[WS_Account_ADD]
	@Customer_Number varchar(15),
    @Account_Name varchar(256),
    @Account_PWD varchar(15),
    @Customer_Contact_id int,
    @Inactive char(1),
    @Account_Id int OUTPUT
  AS
BEGIN
	DECLARE @Customer_Id   int

	-- validate parms
  	IF (@Account_Name IS NULL)
	BEGIN
        RETURN 0;
    END
	IF (@Account_PWD IS NULL)
	BEGIN
 		RETURN 0;
	END
	IF (@Account_Name = '')
	BEGIN
		RETURN 0;
	END
   	IF (@Account_PWD = '')
	BEGIN
   		RETURN 0;
	END

	-- get the customer id
	EXEC Get_Customer_Id @Customer_Number, @Customer_Id OUTPUT;
	IF (@Customer_Id = 1)
	BEGIN
		RETURN 0;
	END

	-- does account already exist?
	IF (EXISTS(
		SELECT Account_Id 
			FROM WS_Account 
			WHERE (Customer_Id = @Customer_Id AND Account_Name = @Account_Name)))
	BEGIN
		RETURN 0;
	END    

	BEGIN TRANSACTION

	-- add the new account
	INSERT INTO WS_Account 
		(Customer_Id, Customer_Contact_Id, Account_Name, Account_PWD, Inactive)
		VALUES (@Customer_Id, @Customer_Contact_Id, @Account_Name, @Account_PWD, @Inactive);
	IF (@@ROWCOUNT < 1)
	BEGIN 
		ROLLBACK TRANSACTION;
		RETURN 0;
	END
	
	-- save the account id for caller	
	SET @Account_Id = SCOPE_IDENTITY()
	-- add the account to the customerList
	INSERT INTO WS_CustomerList
	(Account_Id, Customer_Id, Customer_Contact_Id)
	VALUES (@Account_Id, @Customer_Id, @Customer_Contact_Id);

	-- connect to all the sites for the customer
	INSERT INTO AR_Site_Contact
		(Site_Id, Contact_Id)
		SELECT cs.Customer_Site_Id, @Customer_Contact_Id
			FROM AR_Customer_Site cs
			WHERE (Customer_Id = @Customer_Id);
	
	COMMIT TRANSACTION;
	
	RETURN @Account_Id
END


GO
/****** Object:  StoredProcedure [dbo].[WS_Account_Register]    Script Date: 09/28/2012 09:42:51 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WS_Account_Register]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WS_Account_Register]
GO

/****** Object:  StoredProcedure [dbo].[WS_Account_Register]    Script Date: 09/28/2012 09:42:51 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[WS_Account_Register]
    @customer_Number        varchar(15),
    @customer_verify        varchar(50),
    @account_name           varchar(50),
    @account_pwd            varchar(15),
    @inactive               char(1),
    @contact_name           varchar(255),
    @title                  varchar(50),
    @phone                  varchar(12),
    @extension              varchar(5),
    @fax                    varchar(12),
    @email                  varchar(50),
    @pager                  varchar(12),
    @all_sites              char(1),
    @salutation             varchar(10),
    @first_name             varchar(50),
    @middle                 varchar(50),
    @last_name              varchar(50),
    @suffix                 varchar(12),
    @mobile                 varchar(12),
    @collection_contact     char(1),
    @invoice_contact        char(1),
    @Notes                  varchar(255),
    @Contact_Id             int OUTPUT,
    @Bill_Contact_Id        int OUTPUT,
    @Account_Id             int OUTPUT,
    @ErrCode                varchar(50) OUTPUT
AS
BEGIN
	DECLARE @Customer_Id int;
    DECLARE @Bill_Id int;
    
    -- preset outputs as zero meaning error
    SET @Contact_Id = 0;
    SET @Bill_Contact_Id = 0;
    SET @Account_Id = 0;
    SET @ErrCode = 'OK';
    
    SELECT @customer_id = cu.Customer_Id, @Bill_Id = cb.Customer_Bill_Id
		FROM [dbo].AR_Customer cu INNER JOIN [dbo].AR_Customer_Bill cb 
			ON ((cu.Customer_Id = cb.customer_id) AND (cb.Is_Primary = 'Y'))
		WHERE ((cu.Customer_Number = @customer_number) AND (cb.GE3_Description = @customer_verify));
    IF @customer_id IS NULL
    BEGIN
		SET @ErrCode = 'CustIdNotFound';
        RETURN;
    END
    
    IF EXISTS(SELECT * FROM WS_Account WHERE (Account_Name = @account_name))
    BEGIN
		SET @ErrCode = 'AcctExists';
		RETURN;
	END
    
	BEGIN TRANSACTION;
	
    EXECUTE [dbo].[Customer_Contact_ADD] @contact_name, @title, @phone, @extension,
		@fax, @email, @pager, @all_sites, @salutation, @first_name,
        @middle, @last_name, @suffix, @mobile, @collection_contact,
        @invoice_contact, @notes, 'N', @Contact_Id OUTPUT;
    IF (@Contact_Id = 1)
    BEGIN
        ROLLBACK TRANSACTION;
        SET @ErrCode = 'ContAddFailed';
		RETURN;
    END

    EXECUTE [dbo].[Bill_Contact_ADD] @bill_id, @Contact_Id, @bill_contact_id OUTPUT;
    IF (@Bill_Contact_Id = 1)
    BEGIN
        ROLLBACK TRANSACTION;
        SET @ErrCode = 'BillContAddFailed';
		RETURN;
    END
    
	IF(@inactive = 'Y')
	BEGIN
		EXECUTE [dbo].[WS_Account_ADD] @Customer_Number, @Account_Name, @Account_PWD,
				@Contact_Id, @Inactive, @Account_Id OUTPUT;
		IF (@Account_Id = 0)
		BEGIN
			ROLLBACK TRANSACTION;
			SET @ErrCode = 'AcctAddFailed';
			RETURN;
		END
    END
	ELSE
    INSERT INTO AR_Site_Contact
		(Site_Id, Contact_Id)
		SELECT cs.Customer_Site_Id, @Contact_Id
			FROM AR_Customer_Site cs
			WHERE (Customer_Id = @customer_Id); 
			
    COMMIT TRANSACTION;
END

GO

/****** Object:  StoredProcedure [dbo].[WS_Account_ADD]    Script Date: 09/28/2012 09:43:06 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WS_Account_ADD]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WS_Account_ADD]
GO
/****** Object:  StoredProcedure [dbo].[WS_Account_ADD]    Script Date: 09/28/2012 09:43:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[WS_Account_ADD]
	@Customer_Number varchar(15),
    @Account_Name varchar(256),
    @Account_PWD varchar(15),
    @Customer_Contact_id int,
    @Inactive char(1),
    @Account_Id int OUTPUT
  AS
BEGIN
	DECLARE @Customer_Id   int

	-- validate parms
  	IF (@Account_Name IS NULL)
	BEGIN
        RETURN 0;
    END
	IF (@Account_PWD IS NULL)
	BEGIN
 		RETURN 0;
	END
	IF (@Account_Name = '')
	BEGIN
		RETURN 0;
	END
   	IF (@Account_PWD = '')
	BEGIN
   		RETURN 0;
	END

	-- get the customer id
	EXEC Get_Customer_Id @Customer_Number, @Customer_Id OUTPUT;
	IF (@Customer_Id = 1)
	BEGIN
		RETURN 0;
	END

	-- does account already exist?
	IF (EXISTS(
		SELECT Account_Id 
			FROM WS_Account 
			WHERE (Customer_Id = @Customer_Id AND Account_Name = @Account_Name)))
	BEGIN
		RETURN 0;
	END    

	BEGIN TRANSACTION

	-- add the new account
	INSERT INTO WS_Account 
		(Customer_Id, Customer_Contact_Id, Account_Name, Account_PWD, Inactive)
		VALUES (@Customer_Id, @Customer_Contact_Id, @Account_Name, @Account_PWD, @Inactive);
	IF (@@ROWCOUNT < 1)
	BEGIN 
		ROLLBACK TRANSACTION;
		RETURN 0;
	END
	
	-- save the account id for caller	
	SET @Account_Id = SCOPE_IDENTITY()
	-- add the account to the customerList
	INSERT INTO WS_CustomerList
	(Account_Id, Customer_Id, Customer_Contact_Id)
	VALUES (@Account_Id, @Customer_Id, @Customer_Contact_Id);

	-- connect to all the sites for the customer
	INSERT INTO AR_Site_Contact
		(Site_Id, Contact_Id)
		SELECT cs.Customer_Site_Id, @Customer_Contact_Id
			FROM AR_Customer_Site cs
			WHERE (Customer_Id = @Customer_Id);
	
	COMMIT TRANSACTION;
	
	RETURN @Account_Id
END


GO
/****** Object:  StoredProcedure [dbo].[WS_Login_Customer]    Script Date: 07/04/2013 15:19:28 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WS_Login_Customer]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WS_Login_Customer]
GO

CREATE PROCEDURE [dbo].[WS_Login_Customer]
	@Account_Name			varchar(256),
    @Account_PWD			varchar(15),
    @Customer_Number		varchar(15)			OUTPUT,
    @Validate_Code			int					OUTPUT,
    @Account_Id				int					OUTPUT,
    @Customer_Id			int					= NULL OUTPUT,
    @Customer_Contact_Id	int					= NULL OUTPUT,
    @Customer_Name			varchar(60)         = NULL OUTPUT,
    @Last_Name				varchar(50)			= NULL OUTPUT,
    @First_Name				varchar(50)			= NULL OUTPUT,
    @Phone					varchar(12)			= NULL OUTPUT,
    @E_Mail					varchar(50)			= NULL OUTPUT,
    @Full_Name				varchar(255)		= NULL OUTPUT,
    @Is_Master_Account		int					= NULL OUTPUT,
    @Master_Account_Id		int					= NULL OUTPUT,
    @Master_Account_Code	varchar(25)			= NULL OUTPUT,
    @Master_Account_Desc	varchar(50)			= NULL OUTPUT,
    @Invoice_Contact		char(1)				= NULL OUTPUT
-- WITH ENCRYPTION
AS
BEGIN
	/*
    Validate Codes
    0 - Validation OK
    1 - Account name doesn't match email mode
    2 - Customer Id not found in AR_CUSTOMER
    3 - Customer Number invalid
    4 - Can't find account based on Customer Number and Customer Id
    5 - Account Inactive
    6 - Invalid Password
	*/
	
	DECLARE @Master_id    int,
            @Master_Customer int

	IF (@Customer_Number IS NULL)
	BEGIN
		SELECT @Customer_Id = Customer_Id 
			FROM WS_Account
			WHERE (Account_Name = @Account_Name);

		IF (@Customer_Id IS NULL)
		BEGIN
			SET @Validate_Code = 1;
			RETURN @Validate_Code;
		END
		
		SELECT @Customer_Number = Customer_Number, @Customer_Name = Customer_Name
			FROM AR_Customer
			WHERE (Customer_Id = @Customer_Id);
		
		IF (@Customer_Number IS NULL)
		BEGIN
			SET @Validate_Code = 2;
			RETURN @Validate_Code;
		END 
	END
	ELSE
	BEGIN
		EXEC [dbo].[Get_Customer_Id] @Customer_Number, @Customer_Id OUTPUT

		IF (@Customer_Id = 1)
		BEGIN
			SET @Validate_Code = 3
			RETURN @Validate_Code
		END
	END

    SELECT @Account_Id = Account_Id
      FROM [dbo].WS_Account
      WHERE (Customer_Id = @Customer_Id) AND (Account_Name = @Account_Name)

    IF (@Account_Id IS NULL)
      BEGIN
        SET @Customer_Id = NULL
        SET @Validate_Code = 4
      END
    ELSE
      BEGIN
        IF EXISTS (SELECT Account_Id FROM [dbo].WS_Account
                   WHERE Account_Id = @Account_Id AND Inactive = 'Y')
          BEGIN
            SET @Validate_Code = 5
          END
        ELSE
          BEGIN
            IF EXISTS(
             SELECT Account_Id 
              FROM [dbo].WS_Account
              WHERE 
               (
                (Account_Id = @Account_Id) AND
                (Account_PWD = @Account_PWD COLLATE SQL_Latin1_General_CP1_CS_AS)
               ))
              BEGIN
                SET @Customer_Contact_Id = (SELECT Customer_Contact_Id FROM [dbo].WS_Account
                                            WHERE Account_Id = @Account_Id)
                IF NULLIF(@Customer_Contact_Id,1) IS NOT NULL
                  BEGIN
                    SELECT @Full_Name = Contact_Name, @Last_Name = Last_Name, @First_Name = First_Name,
                           @Phone = Phone, @E_mail = E_Mail,
                           @Invoice_Contact = Invoice_Contact
                    FROM [dbo].AR_Customer_Contact
                    WHERE Customer_Contact_Id = @Customer_Contact_Id                    
                  END
                ELSE
                  BEGIN
                    SELECT @Full_Name = '', @Last_Name = '', @First_Name = '',
                           @Phone = '', @E_mail = '', @Invoice_Contact = 'N'
                  END
                SET @Validate_Code = 0
                SELECT @Master_id = Master_Account_Id, @Customer_Name = Customer_Name FROM [dbo].AR_Customer WHERE Customer_Id = @Customer_Id
                IF NULLIF(@Master_Id,1) IS NOT NULL
                  BEGIN
                    SET @Master_Account_Id = @Master_Id
                    SELECT @Master_Customer = Customer_Id, @Master_Account_Code = Master_Account_Code,
                           @Master_Account_Desc = ma.[Description]
                    FROM [dbo].AR_Master_Account ma
                    WHERE Master_Account_Id = @Master_Id
                  END
                ELSE
                  BEGIN
                    SET @Master_Account_Id = 1
                  END
                IF EXISTS(SELECT customer_Id FROM [dbo].AR_Master_Account WHERE customer_Id = @Customer_Id)
                    SET @Is_Master_Account = 1
                ELSE
                    SET @Is_Master_Account = 0
              END
            ELSE
              BEGIN
                SET @Validate_Code = 6
              END
          END
      END
    RETURN @Validate_Code

	/* daves@sedonaoffice.com removed unused fields 7/4/2013.
    DECLARE @Master_id    int,
            @Master_Customer int

	IF (@Customer_Number IS NULL)
	BEGIN
		SELECT @Customer_Id = Customer_Id 
			FROM WS_Account
			WHERE (Account_Name = @Account_Name);

		IF (@Customer_Id IS NULL)
		BEGIN
			SET @Validate_Code = 1;
			RETURN @Validate_Code;
		END
		
		SELECT @Customer_Number = Customer_Number
			FROM AR_Customer
			WHERE (Customer_Id = @Customer_Id);
		
		IF (@Customer_Number IS NULL)
		BEGIN
			SET @Validate_Code = 2;
			RETURN @Validate_Code;
		END 
	END
	ELSE
	BEGIN
		EXEC [dbo].[Get_Customer_Id] @Customer_Number, @Customer_Id OUTPUT

		IF (@Customer_Id = 1)
		BEGIN
			SET @Validate_Code = 3
			RETURN @Validate_Code
		END
	END

    SELECT @Account_Id = Account_Id
      FROM [dbo].WS_Account
      WHERE (Customer_Id = @Customer_Id) AND (Account_Name = @Account_Name)

    IF (@Account_Id IS NULL)
      BEGIN
        SET @Customer_Id = NULL
        SET @Validate_Code = 4
      END
    ELSE
      BEGIN
        IF EXISTS (SELECT Account_Id FROM [dbo].WS_Account
                   WHERE Account_Id = @Account_Id AND Inactive = 'Y')
          BEGIN
            SET @Validate_Code = 5
          END
        ELSE
          BEGIN
            IF EXISTS(
             SELECT Account_Id 
              FROM [dbo].WS_Account
              WHERE 
               (
                (Account_Id = @Account_Id) AND
                (Account_PWD = @Account_PWD COLLATE SQL_Latin1_General_CP1_CS_AS)
               ))
              BEGIN
                SET @Customer_Contact_Id = (SELECT Customer_Contact_Id FROM [dbo].WS_Account
                                            WHERE Account_Id = @Account_Id)
                IF NULLIF(@Customer_Contact_Id,1) IS NOT NULL
                  BEGIN
                    SELECT @Full_Name = Contact_Name, @Last_Name = Last_Name, @First_Name = First_Name,
                           @Phone = Phone, @E_mail = E_Mail, @Collection_Contact = Collection_Contact,
                           @Invoice_Contact = Invoice_Contact
                    FROM [dbo].AR_Customer_Contact
                    WHERE Customer_Contact_Id = @Customer_Contact_Id

                    IF EXISTS(SELECT * FROM [dbo].AR_Bill_Contact WHERE Contact_Id = @Customer_Contact_Id) 
                        SET @Billing_Contact = 'Y'
                    ELSE
                        SET @Billing_Contact = 'N'
                  END
                ELSE
                  BEGIN
                    SELECT @Full_Name = '', @Last_Name = '', @First_Name = '',
                           @Phone = '', @E_mail = '', @Collection_Contact = 'N',
                           @Invoice_Contact = 'N', @Billing_Contact = 'N'
                  END
                SET @Validate_Code = 0
                SELECT @Master_id = Master_Account_Id FROM [dbo].AR_Customer WHERE Customer_Id = @Customer_Id
                IF NULLIF(@Master_Id,1) IS NOT NULL
                  BEGIN
                    SET @Master_Account_Id = @Master_Id
                    SELECT @Master_Customer = Customer_Id, @Master_Account_Code = Master_Account_Code,
                           @Master_Account_Desc = ma.[Description]
                    FROM [dbo].AR_Master_Account ma
                    WHERE Master_Account_Id = @Master_Id
                  END
                ELSE
                  BEGIN
                    SET @Master_Account_Id = 1
                  END
                IF EXISTS(SELECT customer_Id FROM [dbo].AR_Master_Account WHERE customer_Id = @Customer_Id)
                    SET @Is_Master_Account = 1
                ELSE
                    SET @Is_Master_Account = 0
              END
            ELSE
              BEGIN
                SET @Validate_Code = 6
              END
          END
      END
    RETURN @Validate_Code
    */
END

GO
-- daves@sedonaoffice.com adds login info for SedonaWeb
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG = db_name() AND TABLE_NAME = 'WS_Account'
 AND COLUMN_NAME = 'Last_Login')
	BEGIN
	ALTER TABLE dbo.WS_Account ADD
	Last_Login datetime NULL,
	Inactive_Since datetime NULL,
	Inactivated_By_Account_Id int NULL
	END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WS_Login_Customer]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WS_Login_Customer]
GO

CREATE PROCEDURE [dbo].[WS_Login_Customer]
	@Account_Name			varchar(256),
    @Account_PWD			varchar(15),
    @Customer_Number		varchar(15)			OUTPUT,
    @Validate_Code			int					OUTPUT,
    @Account_Id				int					OUTPUT,
    @Customer_Id			int					= NULL OUTPUT,
    @Customer_Contact_Id	int					= NULL OUTPUT,
    @Customer_Name			varchar(60)         = NULL OUTPUT,
    @Last_Name				varchar(50)			= NULL OUTPUT,
    @First_Name				varchar(50)			= NULL OUTPUT,
    @Phone					varchar(12)			= NULL OUTPUT,
    @E_Mail					varchar(50)			= NULL OUTPUT,
    @Full_Name				varchar(255)		= NULL OUTPUT,
    @Is_Master_Account		int					= NULL OUTPUT,
    @Master_Account_Id		int					= NULL OUTPUT,
    @Master_Account_Code	varchar(25)			= NULL OUTPUT,
    @Master_Account_Desc	varchar(50)			= NULL OUTPUT,
    @Invoice_Contact		char(1)				= NULL OUTPUT
-- WITH ENCRYPTION
AS
BEGIN
	/*
    Validate Codes
    0 - Validation OK
    1 - Account name doesn't match email mode
    2 - Customer Id not found in AR_CUSTOMER
    3 - Customer Number invalid
    4 - Can't find account based on Customer Number and Customer Id
    5 - Account Inactive
    6 - Invalid Password
	*/
	
	DECLARE @Master_id    int,
            @Master_Customer int

	IF (@Customer_Number IS NULL)
	BEGIN
		SELECT @Customer_Id = Customer_Id 
			FROM WS_Account
			WHERE (Account_Name = @Account_Name);

		IF (@Customer_Id IS NULL)
		BEGIN
			SET @Validate_Code = 1;
			RETURN @Validate_Code;
		END
		
		SELECT @Customer_Number = Customer_Number, @Customer_Name = Customer_Name
			FROM AR_Customer
			WHERE (Customer_Id = @Customer_Id);
		
		IF (@Customer_Number IS NULL)
		BEGIN
			SET @Validate_Code = 2;
			RETURN @Validate_Code;
		END 
	END
	ELSE
	BEGIN
		EXEC [dbo].[Get_Customer_Id] @Customer_Number, @Customer_Id OUTPUT

		IF (@Customer_Id = 1)
		BEGIN
			SET @Validate_Code = 3
			RETURN @Validate_Code
		END
	END

    SELECT @Account_Id = Account_Id
      FROM [dbo].WS_Account
      WHERE (Customer_Id = @Customer_Id) AND (Account_Name = @Account_Name)

    IF (@Account_Id IS NULL)
      BEGIN
        SET @Customer_Id = NULL
        SET @Validate_Code = 4
      END
    ELSE
      BEGIN
        IF EXISTS (SELECT Account_Id FROM [dbo].WS_Account
                   WHERE Account_Id = @Account_Id AND Inactive = 'Y')
          BEGIN
            SET @Validate_Code = 5
          END
        ELSE
          BEGIN
            IF EXISTS(
             SELECT Account_Id 
              FROM [dbo].WS_Account
              WHERE 
               (
                (Account_Id = @Account_Id) AND
                (Account_PWD = @Account_PWD COLLATE SQL_Latin1_General_CP1_CS_AS)
               ))
              BEGIN
                SET @Customer_Contact_Id = (SELECT Customer_Contact_Id FROM [dbo].WS_Account
                                            WHERE Account_Id = @Account_Id)
                IF NULLIF(@Customer_Contact_Id,1) IS NOT NULL
                  BEGIN
                    SELECT @Full_Name = Contact_Name, @Last_Name = Last_Name, @First_Name = First_Name,
                           @Phone = Phone, @E_mail = E_Mail,
                           @Invoice_Contact = Invoice_Contact
                    FROM [dbo].AR_Customer_Contact
                    WHERE Customer_Contact_Id = @Customer_Contact_Id                    
                  END
                ELSE
                  BEGIN
                    SELECT @Full_Name = '', @Last_Name = '', @First_Name = '',
                           @Phone = '', @E_mail = '', @Invoice_Contact = 'N'
                  END
                
                SET @Validate_Code = 0
                -- daves@sedonaoffice.com 9/4/2013
                UPDATE WS_Account SET Last_Login = GETDATE() WHERE Account_Id = @Account_Id
                
                SELECT @Master_id = Master_Account_Id, @Customer_Name = Customer_Name FROM [dbo].AR_Customer WHERE Customer_Id = @Customer_Id
                IF NULLIF(@Master_Id,1) IS NOT NULL
                  BEGIN
                    SET @Master_Account_Id = @Master_Id
                    SELECT @Master_Customer = Customer_Id, @Master_Account_Code = Master_Account_Code,
                           @Master_Account_Desc = ma.[Description]
                    FROM [dbo].AR_Master_Account ma
                    WHERE Master_Account_Id = @Master_Id
                  END
                ELSE
                  BEGIN
                    SET @Master_Account_Id = 1
                  END
                IF EXISTS(SELECT customer_Id FROM [dbo].AR_Master_Account WHERE customer_Id = @Customer_Id)
                    SET @Is_Master_Account = 1
                ELSE
                    SET @Is_Master_Account = 0
              END
            ELSE
              BEGIN
                SET @Validate_Code = 6
              END
          END
      END
    RETURN @Validate_Code
END
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Customer_CC_Count_Similar]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Customer_CC_Count_Similar]
GO

-- =============================================
-- Author:		daves@sedonaoffice.com
-- Create date: 1/23/2014
-- Description:	Counts similar credit cards.
-- =============================================
CREATE PROCEDURE [dbo].[Customer_CC_Count_Similar] 
	@cardType varchar(4) = '', 
	@lastFourDigits varchar(4) = '',
	@accountName varchar(22) = ''
-- WITH ENCRYPTION
AS
BEGIN
	DECLARE @count int
	
	SELECT @count = COUNT(Customer_CC_Id) 
	FROM AR_Customer_CC 
	WHERE Card_Type = @cardType AND Last_Four_Digits = @lastFourDigits AND Account_Name = @accountName
	
	-- Returns count as a result set if that is easier for VB.
	SELECT @count as SimilarCardCount 
	
	RETURN @count
END

GO
/* START daves@sedonaoffice.com */
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[web_Customer_CC_ReassignPending]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[web_Customer_CC_ReassignPending]
GO

-- =============================================
-- Author:		daves@sedonaoffice.com
-- Create date: 2/5/2014
-- Description:	Updates pending credit card transactions to use info from the specified card.
-- Returns:     Number of records updated.
-- =============================================
CREATE PROCEDURE [dbo].[web_Customer_CC_ReassignPending] 
	@creditCardId int = 0, 
	@customerId int = 0
-- WITH ENCRYPTION
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @cardType varchar(4)
	DECLARE @cardNumber varchar(16)
	DECLARE @expirationMonth varchar(2)
	DECLARE @expirationYear varchar(2)
	DECLARE @lastFourDigits varchar(4)
	DECLARE @securityCode varchar(8)
	DECLARE @ACH_Direct_Token varchar(50)
	DECLARE @Account_Name varchar(22)
	
	SELECT @cardType = Card_Type, @cardNumber = Card_Number, @expirationMonth = Expiration_Month, @expirationYear = Expiration_Year, @lastFourDigits = Last_Four_Digits, @securityCode = Security_Code, @ACH_Direct_Token = ACH_Direct_Token, @Account_Name = Account_Name
	FROM AR_Customer_CC
	WHERE Customer_CC_Id = @creditCardId
	
	IF @cardType IS NULL RETURN 0
	
	UPDATE AR_ACH
	SET Card_Type = @cardType, Card_Number = @cardNumber, Expiration_Month = @expirationMonth, Expiration_Year = @expirationYear, Last_Four_Digits = @lastFourDigits, Security_Code = @securityCode, ACH_Direct_Token = @ACH_Direct_Token, Name_On_Card = @Account_Name
	WHERE
	Customer_Id = @customerId AND Submitted <> 'Y' AND Credit_Card = 'Y' AND Voided <> 'Y'
	
	RETURN @@ROWCOUNT
END

GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WS_Account_Password]') AND type in (N'U'))
BEGIN
   CREATE TABLE [dbo].[WS_Account_Password](
	[Password_Id] [int] IDENTITY(1,1) NOT NULL,
	[Account_Id] [int] NOT NULL,
	[Salt] [nvarchar](88) NOT NULL,
	[Hash] [nvarchar](44) NOT NULL,
	[Creation_Time] [datetime] NOT NULL DEFAULT(GetDate()),
   CONSTRAINT [PK_WS_Account_Password] PRIMARY KEY CLUSTERED 
   (
	   [Password_Id] ASC
   )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
   ) ON [PRIMARY]
   
   CREATE NONCLUSTERED INDEX [IX_WS_Account_Password] ON [dbo].[WS_Account_Password] 
   (
	   [Account_Id] ASC
   )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

END

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WS_Account_Password_Lookup]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WS_Account_Password_Lookup]
GO

-- =============================================
-- Author:		 daves@sedonaoffice.com
-- Create date: 6/25/2014
-- Description: Gets the most recent password for the account. Or gets password history for auditing.	
-- =============================================
CREATE PROCEDURE [dbo].[WS_Account_Password_Lookup] 
	@account_name varchar(256),
	@multiple bit = 0
AS
BEGIN
	IF @multiple = 0
   BEGIN
      SELECT p1.Password_Id, p1.[Account_Id], Salt, [Hash] 
      FROM WS_Account_Password p1 
      INNER JOIN (SELECT Account_Id, MAX(Password_Id) as Id FROM WS_Account_Password GROUP BY Account_ID) p2 ON p1.Password_Id = p2.Id
      WHERE p1.[Account_Id] IN
      (SELECT [Account_Id] FROM WS_Account WHERE Account_Name = @account_name)
   END
   
   SELECT p1.Password_Id, p1.[Account_Id], Salt, [Hash] 
   FROM WS_Account_Password p1 
   WHERE p1.[Account_Id] IN
   (SELECT [Account_Id] FROM WS_Account WHERE Account_Name = @account_name)
   ORDER BY p1.Password_Id DESC
END

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WS_Account_ADD]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WS_Account_ADD]
GO

CREATE PROCEDURE [dbo].[WS_Account_ADD]
	 @Customer_Number varchar(15),
    @Account_Name varchar(256),
    @Account_PWD varchar(15), -- Not used. Kept for backwards compatibility.
    @Customer_Contact_id int,
    @Inactive char(1),
    @Account_Id int OUTPUT
  AS
BEGIN
	DECLARE @Customer_Id   int

	-- validate parms
  	IF (@Account_Name IS NULL)
	BEGIN
        RETURN 0;
    END
	IF (@Account_Name = '')
	BEGIN
		RETURN 0;
	END
   
	-- get the customer id
	EXEC Get_Customer_Id @Customer_Number, @Customer_Id OUTPUT;
	IF (@Customer_Id = 1)
	BEGIN
		RETURN 0;
	END

	-- does account already exist?
	IF (EXISTS(
		SELECT Account_Id 
			FROM WS_Account 
			WHERE (Customer_Id = @Customer_Id AND Account_Name = @Account_Name)))
	BEGIN
		RETURN 0;
	END    

	BEGIN TRANSACTION

	-- add the new account
	INSERT INTO WS_Account 
	(Customer_Id, Customer_Contact_Id, Account_Name, Account_PWD, Inactive)
	VALUES (@Customer_Id, @Customer_Contact_Id, @Account_Name, '', @Inactive);
	IF (@@ROWCOUNT < 1)
	BEGIN 
		ROLLBACK TRANSACTION;
		RETURN 0;
	END
	
	-- save the account id for caller	
	SET @Account_Id = SCOPE_IDENTITY()
	
	-- add the account to the customerList
	INSERT INTO WS_CustomerList
	(Account_Id, Customer_Id, Customer_Contact_Id)
	VALUES (@Account_Id, @Customer_Id, @Customer_Contact_Id);

	-- connect to all the sites for the customer
	INSERT INTO AR_Site_Contact
		(Site_Id, Contact_Id)
		SELECT cs.Customer_Site_Id, @Customer_Contact_Id
			FROM AR_Customer_Site cs
			WHERE (Customer_Id = @Customer_Id);
	
	COMMIT TRANSACTION;
	
	RETURN @Account_Id
END

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WS_Account_Password_Add]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WS_Account_Password_Add]
GO

CREATE PROCEDURE [dbo].[WS_Account_Password_Add]
    @Account_Id      int,
    @Salt            varchar(88),
    @Hash            varchar(44),
    @Must_Be_Active  bit = 0 
AS

   IF @Must_Be_Active <> 0
   BEGIN
      DECLARE @exists int
      DECLARE @inactive char(1)
      
      SELECT @exists = Account_Id, @inactive = IsNull(Inactive, 'Y') FROM WS_Account WHERE Account_Id = @Account_Id
      
      IF @exists is null 
      BEGIN
         RETURN -1
      END
      
      If @inactive = 'Y'
      BEGIN
         RETURN -2
      END
   END

   DELETE FROM WS_Account_Password
   WHERE
   Account_Id = @Account_Id AND
   Password_Id NOT IN
   (SELECT TOP 4 Password_Id FROM WS_Account_Password WHERE Account_Id = @Account_Id ORDER BY Password_Id DESC)

   INSERT INTO WS_Account_Password
   (Account_Id, Salt, Hash)
   VALUES
   (@Account_Id, @Salt, @Hash)

   RETURN Scope_Identity()
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WS_Account_Register]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WS_Account_Register]
GO

CREATE PROCEDURE [dbo].[WS_Account_Register]
    @customer_Number        varchar(15),
    @customer_verify        varchar(50),
    @account_name           varchar(50),
    @inactive               char(1),
    @contact_name           varchar(255),
    @title                  varchar(50),
    @phone                  varchar(12),
    @extension              varchar(5),
    @fax                    varchar(12),
    @email                  varchar(50),
    @pager                  varchar(12),
    @all_sites              char(1),
    @salutation             varchar(10),
    @first_name             varchar(50),
    @middle                 varchar(50),
    @last_name              varchar(50),
    @suffix                 varchar(12),
    @mobile                 varchar(12),
    @collection_contact     char(1),
    @invoice_contact        char(1),
    @Notes                  varchar(255),
    @Contact_Id             int OUTPUT,
    @Bill_Contact_Id        int OUTPUT,
    @Account_Id             int OUTPUT,
    @ErrCode                varchar(50) OUTPUT
AS
BEGIN
	DECLARE @Customer_Id int;
    DECLARE @Bill_Id int;
    
    -- preset outputs as zero meaning error
    SET @Contact_Id = 0;
    SET @Bill_Contact_Id = 0;
    SET @Account_Id = 0;
    SET @ErrCode = 'OK';
    
    SELECT @customer_id = cu.Customer_Id, @Bill_Id = cb.Customer_Bill_Id
		FROM [dbo].AR_Customer cu INNER JOIN [dbo].AR_Customer_Bill cb 
			ON ((cu.Customer_Id = cb.customer_id) AND (cb.Is_Primary = 'Y'))
		WHERE ((cu.Customer_Number = @customer_number) AND (cb.GE3_Description = @customer_verify));
    IF @customer_id IS NULL
    BEGIN
		SET @ErrCode = 'CustIdNotFound';
        RETURN;
    END
    
    IF EXISTS(SELECT * FROM WS_Account WHERE (Account_Name = @account_name))
    BEGIN
		SET @ErrCode = 'AcctExists';
		RETURN;
	END
    
	BEGIN TRANSACTION;
	
    EXECUTE [dbo].[Customer_Contact_ADD] @contact_name, @title, @phone, @extension,
		@fax, @email, @pager, @all_sites, @salutation, @first_name,
        @middle, @last_name, @suffix, @mobile, @collection_contact,
        @invoice_contact, @notes, 'N', @Contact_Id OUTPUT;
    IF (@Contact_Id = 1)
    BEGIN
        ROLLBACK TRANSACTION;
        SET @ErrCode = 'ContAddFailed';
		RETURN;
    END

    EXECUTE [dbo].[Bill_Contact_ADD] @bill_id, @Contact_Id, @bill_contact_id OUTPUT;
    IF (@Bill_Contact_Id = 1)
    BEGIN
        ROLLBACK TRANSACTION;
        SET @ErrCode = 'BillContAddFailed';
		RETURN;
    END
    
	IF(@inactive = 'Y')
	BEGIN
		EXECUTE [dbo].[WS_Account_ADD] @Customer_Number, @Account_Name, '',
				@Contact_Id, @Inactive, @Account_Id OUTPUT;
		IF (@Account_Id = 0)
		BEGIN
			ROLLBACK TRANSACTION;
			SET @ErrCode = 'AcctAddFailed';
			RETURN;
		END
    END
	ELSE
    INSERT INTO AR_Site_Contact
		(Site_Id, Contact_Id)
		SELECT cs.Customer_Site_Id, @Contact_Id
			FROM AR_Customer_Site cs
			WHERE (Customer_Id = @customer_Id); 
			
    COMMIT TRANSACTION;
END

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WS_Account_UPD]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WS_Account_UPD]
GO

CREATE PROCEDURE [dbo].[WS_Account_UPD]

    @account_id            int,
    @Customer_Number       varchar(15),
    @Account_Name          varchar(256),
    @Password              varchar(15), -- Not used. Kept for backwards compatibility.
    @Customer_Contact_id   int,
    @Inactive              char(1)

AS
BEGIN	
	DECLARE @Customer_Id   int

  	IF @Account_Name IS NULL
 	BEGIN
		RETURN 0
	END
   IF @Account_Name = ''
    BEGIN
		RETURN 0
	END
   
	EXEC Get_Customer_Id @Customer_Number, @Customer_Id OUTPUT

	IF @Customer_Id = 1
	BEGIN
		RETURN 0
	END

	UPDATE WS_Account
	SET Customer_Id = @customer_id,
		Customer_Contact_Id = @customer_contact_id,
		Account_Name = @account_name,
		Account_PWD = '',
		Inactive = @inactive
	WHERE Account_Id = @account_id
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WS_Login_Customer]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WS_Login_Customer]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WS_Account_Password_Verify]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WS_Account_Password_Verify]
GO

CREATE PROCEDURE [dbo].[WS_Account_Password_Verify]
	 @Account_Name			varchar(256),
    @Salt			      varchar(88),
    @Hash               varchar(44),
    @Customer_Number		varchar(15)			OUTPUT,
    @Validate_Code			int					OUTPUT,
    @Account_Id				int					OUTPUT,
    @Customer_Id			int					= NULL OUTPUT,
    @Customer_Contact_Id	int					= NULL OUTPUT,
    @Customer_Name			varchar(60)         = NULL OUTPUT,
    @Last_Name				varchar(50)			= NULL OUTPUT,
    @First_Name				varchar(50)			= NULL OUTPUT,
    @Phone					varchar(12)			= NULL OUTPUT,
    @E_Mail					varchar(50)			= NULL OUTPUT,
    @Full_Name				varchar(255)		= NULL OUTPUT,
    @Is_Master_Account		int					= NULL OUTPUT,
    @Master_Account_Id		int					= NULL OUTPUT,
    @Master_Account_Code	varchar(25)			= NULL OUTPUT,
    @Master_Account_Desc	varchar(50)			= NULL OUTPUT,
    @Invoice_Contact		char(1)				= NULL OUTPUT
-- WITH ENCRYPTION
AS
BEGIN
	/*
    Validate Codes
    0 - Validation OK
    1 - Account name doesn't match email mode
    2 - Customer Id not found in AR_CUSTOMER
    3 - Customer Number invalid
    4 - Can't find account based on Customer Number and Customer Id
    5 - Account Inactive
    6 - Invalid Password
	*/
	
	DECLARE @Master_id    int,
            @Master_Customer int

	IF (@Customer_Number IS NULL)
	BEGIN
		SELECT @Customer_Id = Customer_Id 
			FROM WS_Account
			WHERE (Account_Name = @Account_Name);

		IF (@Customer_Id IS NULL)
		BEGIN
			SET @Validate_Code = 1;
			RETURN @Validate_Code;
		END
		
		SELECT @Customer_Number = Customer_Number, @Customer_Name = Customer_Name
			FROM AR_Customer
			WHERE (Customer_Id = @Customer_Id);
		
		IF (@Customer_Number IS NULL)
		BEGIN
			SET @Validate_Code = 2;
			RETURN @Validate_Code;
		END 
	END
	ELSE
	BEGIN
		EXEC [dbo].[Get_Customer_Id] @Customer_Number, @Customer_Id OUTPUT

		IF (@Customer_Id = 1)
		BEGIN
			SET @Validate_Code = 3
			RETURN @Validate_Code
		END
	END

    SELECT @Account_Id = Account_Id
      FROM [dbo].WS_Account
      WHERE (Customer_Id = @Customer_Id) AND (Account_Name = @Account_Name)

    IF (@Account_Id IS NULL)
      BEGIN
        SET @Customer_Id = NULL
        SET @Validate_Code = 4
      END
    ELSE
      BEGIN
        IF EXISTS (SELECT Account_Id FROM [dbo].WS_Account
                   WHERE Account_Id = @Account_Id AND Inactive = 'Y')
          BEGIN
            SET @Validate_Code = 5
          END
        ELSE
          BEGIN
            IF EXISTS(
             SELECT Account_Id 
              FROM [dbo].WS_Account_Password
              WHERE 
              Account_Id = @Account_Id AND Salt = @Salt AND Hash = @Hash)
              BEGIN
                SET @Customer_Contact_Id = (SELECT Customer_Contact_Id FROM [dbo].WS_Account
                                            WHERE Account_Id = @Account_Id)
                IF NULLIF(@Customer_Contact_Id,1) IS NOT NULL
                  BEGIN
                    SELECT @Full_Name = Contact_Name, @Last_Name = Last_Name, @First_Name = First_Name,
                           @Phone = Phone, @E_mail = E_Mail,
                           @Invoice_Contact = Invoice_Contact
                    FROM [dbo].AR_Customer_Contact
                    WHERE Customer_Contact_Id = @Customer_Contact_Id                    
                  END
                ELSE
                  BEGIN
                    SELECT @Full_Name = '', @Last_Name = '', @First_Name = '',
                           @Phone = '', @E_mail = '', @Invoice_Contact = 'N'
                  END
                
                SET @Validate_Code = 0
                -- daves@sedonaoffice.com 9/4/2013
                UPDATE WS_Account SET Last_Login = GETDATE() WHERE Account_Id = @Account_Id
                
                SELECT @Master_id = Master_Account_Id, @Customer_Name = Customer_Name FROM [dbo].AR_Customer WHERE Customer_Id = @Customer_Id
                IF NULLIF(@Master_Id,1) IS NOT NULL
                  BEGIN
                    SET @Master_Account_Id = @Master_Id
                    SELECT @Master_Customer = Customer_Id, @Master_Account_Code = Master_Account_Code,
                           @Master_Account_Desc = ma.[Description]
                    FROM [dbo].AR_Master_Account ma
                    WHERE Master_Account_Id = @Master_Id
                  END
                ELSE
                  BEGIN
                    SET @Master_Account_Id = 1
                  END
                IF EXISTS(SELECT customer_Id FROM [dbo].AR_Master_Account WHERE customer_Id = @Customer_Id)
                    SET @Is_Master_Account = 1
                ELSE
                    SET @Is_Master_Account = 0
              END
            ELSE
              BEGIN
                SET @Validate_Code = 6
              END
          END
      END
    RETURN @Validate_Code
END

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WS_Account_PSWD]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WS_Account_PSWD]
GO

/****** Object:  StoredProcedure [dbo].[web_Customer_Bank_Add]    Script Date: 04/23/2014 13:01:10 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[web_Customer_Bank_Add]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[web_Customer_Bank_Add]
GO

SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[web_Customer_Bank_Add]
	@customer_number	varchar(15),
	@bank_code		varchar(25),
	@account_number	varchar(20),
	@prenote_required	char(1),
	@prenote_done		char(1),
	@checking_or_savings	char(1),
	@payment_method	varchar(25),
	@bank_account_name	varchar(22),
	@auto_process		char(1),
	@auto_day		int,
	@last_four		varchar(4),
	@scanned			char(1),
	@ach_direct_token	varchar(50),
	@UsedForNonRecurAuto	char(1) = 'N',
	@MaxNonRecurAmount	money = 0.00,
	@Print_Cycle_Invoices char(1),
	@customer_bank_id	int OUTPUT
WITH ENCRYPTION AS

	DECLARE 	@customer_id	int,
			@bank_id	int,
			@payment_method_id int

	EXEC Get_Customer_Id @customer_number, @customer_id OUTPUT
	EXEC Get_Payment_Method_Id @payment_method, @payment_method_id OUTPUT

	SET @bank_id = ISNULL((SELECT TOP 1 Bank_Id FROM AR_Bank WHERE Code = @bank_code), 1)

	SET @customer_bank_id = 0

	IF EXISTS (SELECT Customer_Bank_Id FROM AR_Customer_Bank WHERE Customer_Id = @customer_id AND Bank_Id = @bank_id AND Account_Number = @account_number)
		BEGIN
		RETURN
		END


	INSERT AR_Customer_Bank (Customer_Id, Bank_Id, Account_Number, PreNote_Required, PreNote_Done, Checking_Or_Savings, Payment_Method_Id, Bank_Account_Name,
UsedForAutoProcess, Auto_Process_Day, Last_Four_Digits, Scanned, ACH_Direct_Token, UsedForNonRecurAuto, MaxNonRecurAmount)
	VALUES(@customer_id, @bank_id, @account_number, @prenote_required, @prenote_done, @checking_or_savings, @payment_method_id, @bank_account_name, @auto_process,
@auto_day, @last_four, @scanned, @ach_direct_token, @UsedForNonRecurAuto, @MaxNonRecurAmount)

	IF @@error = 0	SET @customer_bank_id = SCOPE_IDENTITY()

	IF @auto_process = 'Y'
		BEGIN
		UPDATE AR_Customer_Bank
		SET UsedForAutoProcess = 'N'
		WHERE Customer_Id = @customer_id
		AND Customer_Bank_Id <> @customer_bank_id

		UPDATE AR_Customer_CC
		SET UsedForAutoProcess = 'N'
		WHERE Customer_Id = @customer_id
		END
		
	IF @UsedForNonRecurAuto = 'Y'
		BEGIN
		UPDATE AR_Customer_Bank
		SET UsedForNonRecurAuto = 'N'
		WHERE Customer_Id = @customer_id
		AND Customer_Bank_Id <> @customer_bank_id

		UPDATE AR_Customer_CC
		SET UsedForNonRecurAuto = 'N'
		WHERE Customer_Id = @customer_id
		END
		
	UPDATE AR_Customer
	SET Print_Cycle_Invoices = @Print_Cycle_Invoices
	WHERE Customer_Id = @customer_id

RETURN

GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[web_Customer_Bank_Refresh]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[web_Customer_Bank_Refresh]
GO


SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[web_Customer_Bank_Refresh]
	@customer_bank_id 	int

-- WITH ENCRYPTION
AS

	SELECT cu.Customer_Number, bk.Code, bk.Description, cb.Account_Number, cb.PreNote_Required, cb.PreNote_Done, cb.Checking_Or_Savings,
 pm.Payment_Method_Code, cb.Bank_Account_Name, cb.UsedForAutoProcess, cb.Auto_Process_Day, cb.Last_Four_Digits, cb.Scanned, cb.ACH_Direct_Token,
 ISNULL(cb.UsedForNonRecurAuto, 'N') AS UsedForNonRecurAuto, ISNULL(cb.MaxNonRecurAmount,0.00) as MaxNonRecurAmount, cu.Print_Cycle_Invoices
	FROM AR_Customer_Bank cb
	INNER JOIN AR_Customer cu ON cu.Customer_Id = cb.Customer_Id
	INNER JOIN AR_Bank bk ON bk.Bank_Id = cb.Bank_Id
	INNER JOIN AR_Payment_Method pm ON pm.Payment_Method_Id = cb.Payment_Method_Id
	WHERE cb.Customer_Bank_Id = @customer_bank_id
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[web_Customer_Bank_Upd]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[web_Customer_Bank_Upd]
GO

SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[web_Customer_Bank_Upd]
	@customer_bank_id	int,
	@customer_number	varchar(15),
	@bank_code		varchar(25),
	@account_number	varchar(20),
	@prenote_required	char(1),
	@prenote_done		char(1),
	@checking_or_savings	char(1),
	@payment_method	varchar(25),
	@bank_account_name	varchar(22),
	@auto_process		char(1),
	@auto_day		int,
	@last_four		varchar(4),
	@ach_direct_token	varchar(50),
	@UsedForNonRecurAuto	char(1) = 'N',
	@MaxNonRecurAmount	money = 0.00,
	@Print_Cycle_Invoices char(1)
--WITH ENCRYPTION 
AS

	DECLARE 	@customer_id	int,
			@bank_id	int,
			@payment_method_id int

	EXEC Get_Customer_Id @customer_number, @customer_id OUTPUT
	EXEC Get_Payment_Method_Id @payment_method, @payment_method_id OUTPUT

	SET @bank_id = ISNULL((SELECT TOP 1 Bank_Id FROM AR_Bank WHERE Code = @bank_code), 1)

	UPDATE AR_Customer_Bank
	SET Customer_Id = @customer_id,
		Bank_Id = @bank_id,
		Account_Number = @account_number,
		PreNote_Required = @prenote_required,
		PreNote_Done = @prenote_done,
		Checking_Or_Savings = @checking_or_savings,
		Payment_Method_Id = @payment_method_id,
		Bank_Account_Name = @bank_account_name,
		UsedForAutoProcess = @auto_process,
		Auto_Process_Day = @auto_day,
		Last_Four_Digits = @last_four,
		ACH_Direct_Token = @ach_direct_token,
		Scanned = 'N',
		UsedForNonRecurAuto = @UsedForNonRecurAuto,
		MaxNonRecurAmount = @MaxNonRecurAmount
	WHERE Customer_Bank_Id = @customer_bank_id

	IF @auto_process = 'Y'
		BEGIN
		UPDATE AR_Customer_Bank
		SET UsedForAutoProcess = 'N'
		WHERE Customer_Id = @customer_id
		AND Customer_Bank_Id <> @customer_bank_id

		UPDATE AR_Customer_CC
		SET UsedForAutoProcess = 'N'
		WHERE Customer_Id = @customer_id
		END

		
	IF @UsedForNonRecurAuto = 'Y'
		BEGIN
		UPDATE AR_Customer_Bank
		SET UsedForNonRecurAuto = 'N'
		WHERE Customer_Id = @customer_id
		AND Customer_Bank_Id <> @customer_bank_id

		UPDATE AR_Customer_CC
		SET UsedForNonRecurAuto = 'N'
		WHERE Customer_Id = @customer_id
		END
		
	UPDATE AR_Customer
	SET Print_Cycle_Invoices = @Print_Cycle_Invoices
	WHERE Customer_Id = @customer_id
	
RETURN
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[web_Customer_CC_Add]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[web_Customer_CC_Add]
GO

SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[web_Customer_CC_Add]
	@customer_number	varchar(15),
	@card_type		varchar(4),
	@card_number		varchar(16),
	@expiration_month	varchar(2),
	@expiration_year	varchar(2),
	@account_name	varchar(22),
	@payment_method	varchar(25),
	@customer_address	varchar(35),
	@customer_postalcode	varchar(10),
	@security_code		varchar(8),
	@auto_process		char(1),
	@auto_day		int,
	@last_four		varchar(4),
	@ach_direct_token	varchar(50),
	@UsedForNonRecurAuto	char(1) = 'N',
	@MaxNonRecurAmount	money = 0.00,
	@Print_Cycle_Invoices char(1),
	@customer_cc_id	int OUTPUT
	
--WITH ENCRYPTION 
AS

	DECLARE	@customer_id int,
			@payment_method_id int

	if ((@card_number = '') and (@ach_direct_token = ''))
	BEGIN
		RAISERROR (N'Cannot add card info without either card number or ACH token',
		11, -- Severity.
		1)
	END
	
	-- These lines are required by SedonaWeb to initialize values of non-supported parameters.
	SET @UsedForNonRecurAuto = ISNULL(@UsedForNonRecurAuto, 'N')
	SET @MaxNonRecurAmount = ISNULL(@MaxNonRecurAmount, 0)
	
	EXEC Get_Customer_Id @customer_number, @customer_id OUTPUT
	EXEC Get_Payment_Method_Id @payment_method, @payment_method_id OUTPUT

	SET @customer_cc_id = 0

	IF EXISTS (SELECT Customer_CC_Id FROM AR_Customer_CC WHERE Customer_Id = @customer_id AND Card_Type = @card_type AND Card_Number = @card_number AND ACH_Direct_Token = @ach_direct_token)
		BEGIN
		RETURN
		END

	INSERT AR_Customer_CC (Customer_Id, Card_Type, Card_Number, Expiration_Month, Expiration_Year, Account_Name, Payment_Method_Id,
Customer_Address, Customer_PostalCode, Security_Code, UsedForAutoProcess, Auto_Process_Day, Last_Four_Digits, ACH_Direct_Token, UsedForNonRecurAuto, MaxNonRecurAmount)
	VALUES (@customer_id, @card_type, @card_number, @expiration_month, @expiration_year, @account_name, @payment_method_id,
@customer_address, @customer_postalcode, @security_code, @auto_process, @auto_day, @last_four, @ach_direct_token, @UsedForNonRecurAuto, @MaxNonRecurAmount)

	IF @@error = 0 SET @customer_cc_id = SCOPE_IDENTITY()

	IF @auto_process = 'Y'
		BEGIN
		UPDATE AR_Customer_CC
		SET UsedForAutoProcess = 'N'
		WHERE Customer_Id = @customer_id
		AND Customer_CC_Id <> @customer_cc_id

		UPDATE AR_Customer_Bank
		SET UsedForAutoProcess = 'N'
		WHERE Customer_Id = @customer_id
		END
		
	IF @UsedForNonRecurAuto = 'Y'
		BEGIN
		UPDATE AR_Customer_CC
		SET UsedForNonRecurAuto = 'N'
		WHERE Customer_Id = @customer_id
		AND Customer_CC_Id <> @customer_cc_id

		UPDATE AR_Customer_Bank
		SET UsedForNonRecurAuto = 'N'
		WHERE Customer_Id = @customer_id
		END
		
	UPDATE AR_Customer
	SET Print_Cycle_Invoices = @Print_Cycle_Invoices
	WHERE Customer_Id = @customer_id
	
RETURN
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[web_Customer_CC_Refresh]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[web_Customer_CC_Refresh]
GO

SET ANSI_NULLS, QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[web_Customer_CC_Refresh]
	@customer_cc_id	int

--WITH ENCRYPTION
AS

	SELECT  cu.Customer_Number, cc.Card_Type, cc.Card_Number, cc.Expiration_Month, cc.Expiration_Year, cc.Account_Name,
pm.Payment_Method_Code, cc.Customer_Address, cc.Customer_PostalCode, cc.Security_Code, cc.UsedForAutoProcess, cc.Auto_Process_Day,
cc.Last_Four_Digits, cc.ACH_Direct_Token, ISNULL(cc.UsedForNonRecurAuto,'N') AS UsedForNonRecurAuto, ISNULL(cc.MaxNonRecurAmount,0) AS MaxNonRecurAmount,
cu.Print_Cycle_Invoices
	FROM AR_Customer_CC cc
	INNER JOIN AR_Customer cu ON cu.Customer_Id = cc.Customer_Id
	INNER JOIN AR_Payment_Method pm ON pm.Payment_Method_Id = cc.Payment_Method_Id
	WHERE cc.Customer_CC_Id = @customer_cc_id
	
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[web_Customer_CC_Upd]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[web_Customer_CC_Upd]
GO

SET ANSI_NULLS, QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[web_Customer_CC_Upd]
	@customer_cc_id	int,
	@customer_number	varchar(15),
	@card_type		varchar(4),
	@card_number		varchar(16),
	@expiration_month	varchar(2),
	@expiration_year	varchar(2),
	@account_name	varchar(22),
	@payment_method	varchar(25),
	@customer_address	varchar(35),
	@customer_postalcode	varchar(10),
	@security_code		varchar(8),
	@auto_process		char(1),
	@auto_day		int,
	@last_four		varchar(4),
	@ach_direct_token	varchar(50),
	@UsedForNonRecurAuto	char(1),
	@MaxNonRecurAmount	money,
	@Print_Cycle_Invoices char(1)

-- WITH ENCRYPTION
AS

	DECLARE	@customer_id int,
			@payment_method_id int,
			@PCICompliant char(1)

	if ((@card_number = '') and (@ach_direct_token = ''))
	BEGIN
		RAISERROR (N'Cannot add card info without either card number or ACH Direct token assignment!',
		16, -- Severity.
		1)
	END
	
	EXEC Get_Customer_Id @customer_number, @customer_id OUTPUT
	EXEC Get_Payment_Method_Id @payment_method, @payment_method_id OUTPUT
	
	SELECT @PCICompliant = ISNULL((SELECT TOP 1 PCI_Compliant FROM AR_ACH_Direct), 'X')

	UPDATE AR_Customer_CC
	SET	Customer_Id = @customer_id,
		Card_Type = @card_type,
		Card_Number = @card_number,
		Expiration_Month = @expiration_month,
		Expiration_Year = @expiration_year,
		Account_Name = @account_name,
		Payment_Method_Id = @payment_method_id,
		Customer_Address = @customer_address,
		Customer_PostalCode = @customer_postalcode,
		Security_Code = @security_code,
		UsedForAutoProcess = @auto_process,
		Auto_Process_Day = @auto_day,
		Last_Four_Digits = @last_four,
		ACH_Direct_Token = @ach_direct_token,
		UsedForNonRecurAuto = @UsedForNonRecurAuto,
		MaxNonRecurAmount = @MaxNonRecurAmount
	WHERE Customer_CC_Id = @customer_cc_id

	IF @auto_process = 'Y'
		BEGIN
		UPDATE AR_Customer_CC
		SET UsedForAutoProcess = 'N'
		WHERE Customer_Id = @customer_id
		AND Customer_CC_Id <> @customer_cc_id

		UPDATE AR_Customer_Bank
		SET UsedForAutoProcess = 'N'
		WHERE Customer_Id = @customer_id
		END

	IF @UsedForNonRecurAuto = 'Y'
		BEGIN
		UPDATE AR_Customer_CC
		SET UsedForNonRecurAuto = 'N'
		WHERE Customer_Id = @customer_id
		AND Customer_CC_Id <> @customer_cc_id

		UPDATE AR_Customer_Bank
		SET UsedForNonRecurAuto = 'N'
		WHERE Customer_Id = @customer_id
		END

	IF @PCICompliant = "Y"
		BEGIN
		UPDATE AR_ACH
		SET Expiration_Month = @expiration_month,
		        Expiration_Year = @expiration_year
		WHERE Customer_Id = @customer_id
		AND ACH_Direct_Token = @ach_direct_token
		AND Submitted <> 'Y'
		END
	ELSE
		BEGIN
		UPDATE AR_ACH
		SET Expiration_Month = @expiration_month,
		        Expiration_Year = @expiration_year
		WHERE Customer_Id = @customer_id
		AND Card_Number = @card_number
		AND Submitted <> 'Y'
		END
		
	UPDATE AR_Customer
	SET Print_Cycle_Invoices = @Print_Cycle_Invoices
	WHERE Customer_Id = @customer_id

RETURN
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WS_Account_ADD2]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WS_Account_ADD2]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[WS_Account_ADD2]
	 @Customer_Number varchar(15),
    @Account_Name varchar(256),
    @Customer_Contact_id int,
    @Inactive char(1),
    @AddToAllSites bit = 1,
    @Account_Id int OUTPUT
  AS
BEGIN
	DECLARE @Customer_Id   int

	-- validate parms
  	IF (@Account_Name IS NULL)
	BEGIN
        RETURN 0;
    END
	IF (@Account_Name = '')
	BEGIN
		RETURN 0;
	END
   
	-- get the customer id
	EXEC Get_Customer_Id @Customer_Number, @Customer_Id OUTPUT;
	IF (@Customer_Id = 1)
	BEGIN
		RETURN 0;
	END

	-- does account already exist?
	IF (EXISTS(
		SELECT Account_Id 
			FROM WS_Account 
			WHERE (Customer_Id = @Customer_Id AND Account_Name = @Account_Name)))
	BEGIN
		RETURN 0;
	END    

	BEGIN TRANSACTION

	-- add the new account
	INSERT INTO WS_Account 
	(Customer_Id, Customer_Contact_Id, Account_Name, Account_PWD, Inactive)
	VALUES (@Customer_Id, @Customer_Contact_Id, @Account_Name, '', @Inactive);
	IF (@@ROWCOUNT < 1)
	BEGIN 
		ROLLBACK TRANSACTION;
		RETURN 0;
	END
	
	-- save the account id for caller	
	SET @Account_Id = SCOPE_IDENTITY()
	
	-- add the account to the customerList
	INSERT INTO WS_CustomerList
	(Account_Id, Customer_Id, Customer_Contact_Id)
	VALUES (@Account_Id, @Customer_Id, @Customer_Contact_Id);

	-- connect to all the sites for the customer
	IF @AddToAllSites = 1
	BEGIN
	   INSERT INTO AR_Site_Contact(Site_Id, Contact_Id)
		SELECT cs.Customer_Site_Id, @Customer_Contact_Id FROM AR_Customer_Site cs WHERE (Customer_Id = @Customer_Id)
	END
	COMMIT TRANSACTION;
	
	RETURN @Account_Id
END

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WS_Account_Register]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WS_Account_Register]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[WS_Account_Register]
    @customer_Number        varchar(15),
    @customer_verify        varchar(50),
    @account_name           varchar(50),
    @inactive               char(1),
    @contact_name           varchar(255),
    @title                  varchar(50),
    @phone                  varchar(12),
    @extension              varchar(5),
    @fax                    varchar(12),
    @email                  varchar(50),
    @pager                  varchar(12),
    @all_sites              char(1),
    @salutation             varchar(10),
    @first_name             varchar(50),
    @middle                 varchar(50),
    @last_name              varchar(50),
    @suffix                 varchar(12),
    @mobile                 varchar(12),
    @collection_contact     char(1),
    @invoice_contact        char(1),
    @Notes                  varchar(255),
    @AddToBillContacts      bit = 1,
    @AddToAllSites          bit = 1,
    @Contact_Id             int OUTPUT,
    @Bill_Contact_Id        int OUTPUT,
    @Account_Id             int OUTPUT,
    @ErrCode                varchar(50) OUTPUT
AS
BEGIN
	DECLARE @Customer_Id int;
    DECLARE @Bill_Id int;
    
    -- preset outputs as zero meaning error
    SET @Contact_Id = 0;
    SET @Bill_Contact_Id = 0;
    SET @Account_Id = 0;
    SET @ErrCode = 'OK';
    
    SELECT @customer_id = cu.Customer_Id, @Bill_Id = cb.Customer_Bill_Id
		FROM [dbo].AR_Customer cu INNER JOIN [dbo].AR_Customer_Bill cb 
			ON ((cu.Customer_Id = cb.customer_id) AND (cb.Is_Primary = 'Y'))
		WHERE ((cu.Customer_Number = @customer_number) AND (cb.GE3_Description = @customer_verify) AND (cu.Customer_Status_Id <> 3));
    IF @customer_id IS NULL
    BEGIN
		SET @ErrCode = 'CustIdNotFound';
        RETURN;
    END
    
    IF EXISTS(SELECT * FROM WS_Account WHERE (Account_Name = @account_name))
    BEGIN
		SET @ErrCode = 'AcctExists';
		RETURN;
	END
    
	BEGIN TRANSACTION;
	
    EXECUTE [dbo].[Customer_Contact_ADD] @contact_name, @title, @phone, @extension,
		@fax, @email, @pager, @all_sites, @salutation, @first_name,
        @middle, @last_name, @suffix, @mobile, @collection_contact,
        @invoice_contact, @notes, 'N', @Contact_Id OUTPUT;
    IF (@Contact_Id = 1)
    BEGIN
        ROLLBACK TRANSACTION;
        SET @ErrCode = 'ContAddFailed';
		RETURN;
    END

   IF @AddToBillContacts = 1
      BEGIN
      EXECUTE [dbo].[Bill_Contact_ADD] @bill_id, @Contact_Id, @bill_contact_id OUTPUT;
      IF (@Bill_Contact_Id = 1)
         BEGIN
         ROLLBACK TRANSACTION;
         SET @ErrCode = 'BillContAddFailed';
		   RETURN;
      END
    END
    
	IF(@inactive = 'Y')
	BEGIN
		EXECUTE [dbo].[WS_Account_ADD2] @Customer_Number, @Account_Name, 
				@Contact_Id, @Inactive, @AddToAllSites, @Account_Id OUTPUT;
		IF (@Account_Id = 0)
		BEGIN
			ROLLBACK TRANSACTION;
			SET @ErrCode = 'AcctAddFailed';
			RETURN;
		END
    END
	ELSE
    INSERT INTO AR_Site_Contact
		(Site_Id, Contact_Id)
		SELECT cs.Customer_Site_Id, @Contact_Id
			FROM AR_Customer_Site cs
			WHERE (Customer_Id = @customer_Id); 
			
    COMMIT TRANSACTION;
END

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[web_Customer_CC_ReassignPending]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[web_Customer_CC_ReassignPending]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		daves@sedonaoffice.com
-- Create date: 2/5/2014
-- Description:	Updates pending credit card transactions to use info from the specified card.
-- Returns:     Number of records updated.
-- =============================================
-- Edited:		4/2/2015 daves@sedonaoffice.com - Updates response codes per Jim.
-- =============================================
CREATE PROCEDURE [dbo].[web_Customer_CC_ReassignPending] 
	@creditCardId int = 0, 
	@customerId int = 0
-- WITH ENCRYPTION
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @cardType varchar(4)
	DECLARE @cardNumber varchar(16)
	DECLARE @expirationMonth varchar(2)
	DECLARE @expirationYear varchar(2)
	DECLARE @lastFourDigits varchar(4)
	DECLARE @securityCode varchar(8)
	DECLARE @ACH_Direct_Token varchar(50)
	DECLARE @Account_Name varchar(22)
	
	SELECT @cardType = Card_Type, @cardNumber = Card_Number, @expirationMonth = Expiration_Month, @expirationYear = Expiration_Year, @lastFourDigits = Last_Four_Digits, @securityCode = Security_Code, @ACH_Direct_Token = ACH_Direct_Token, @Account_Name = Account_Name
	FROM AR_Customer_CC
	WHERE Customer_CC_Id = @creditCardId
	
	IF @cardType IS NULL RETURN 0
	
	UPDATE AR_ACH
	SET Card_Type = @cardType, Card_Number = @cardNumber, Expiration_Month = @expirationMonth, Expiration_Year = @expirationYear, Last_Four_Digits = @lastFourDigits, Security_Code = @securityCode, ACH_Direct_Token = @ACH_Direct_Token, Name_On_Card = @Account_Name
	WHERE
	Customer_Id = @customerId AND Credit_Card = 'Y' AND Submitted = 'N' AND Posted <> 'Y' AND Voided <> 'Y' AND Response_Type NOT IN ('D', 'E', 'R')
	
	RETURN @@ROWCOUNT
END

GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WS_PendingCCAmount_Customer]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WS_PendingCCAmount_Customer]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[WS_PendingCCAmount_Customer]
      @customerId int,
      @amount     money OUTPUT
      
AS
      -- daves@sedonaoffice.com 4/1/2015: Includes additional response types per Jim's script.
      SELECT @amount = ISNULL((SELECT SUM(ai.Amount) FROM AR_ACH_Invoice ai INNER JOIN AR_ACH a ON a.ACH_Id = ai.ACH_Id
      WHERE a.Customer_Id = @customerId AND a.Credit_Card <> 'N' AND a.Posted <> 'Y' AND a.Voided <> 'Y' AND a.Response_Type NOT IN ('D', 'E', 'R')),0)
      
RETURN

GO