Re: various minor issues solution for GST + PST mass enable

[David Godfrey <..hidden..>]

Bob Miller wrote:

It turns out Luke was on to something after all, and David pointed out
to me what it was.  On the admin.pl page, you can set different user
levels.  any user set to "user" level cannot modify the salesperson
field in the edit customer page.  Change it to supervisor, manager, or
administrator, and then you can.
The remaining issue of importance is finding the extra tax fields.
There are over 700 customers in the database, it would be preferred if
we did not have to visit each customer on the edit page to enable the
tax.  
As per my conversation with David, it seems the trick is that I have
multiple tax fields on the edit customer page, the only one of which is
represented in the database customer table is taxincluded.  I believe I
have examined all table descriptions in the database, and not found the
correct column yet.  Any clues what it might be called?

Thanks too all for your support, it is much appreciated!!

On Tue, 2010-01-19 at 17:42 -0500, Luke wrote:
  

On Tue, 19 Jan 2010, Bob Miller wrote:

    

Presumably you have the "sales" checkbox selected for those multiple 
employees?
        

There does not appear to be such a chek box.  Thank you for the
suggestion though, I will look to see if I can find something similar...
      

I am starting to think that your permissions may be more limited than they 
need to be.

I am talking about the user permissions under admin.pl.

Make sure that everything is enabled there, and you're set to admin.

You can disable some later, but for now, enable all permissions, and see 
what that does for you.

Luke

------------------------------------------------------------------------------
    

 Miller
334-7117/633-3760
http://computerisms.ca
..hidden..
Network, Internet, Server,
and Open Source Solutions

  

Bob,

Have some info for you.

If you run this SQL

SELECT
    c.id as "Customer ID",
    c.customernumber as "Customer Number",
    c.name as "Customer Name",
    ct.chart_id as "Tax ID",
    ch.description as "Tax Name",
    ch.accno as "Chart Number"
FROM customer c
LEFT JOIN customertax ct on ct.customer_id=c.id
LEFT JOIN chart ch on ch.id = ct.chart_id;

It will return all customers and the Tax settings for them.
The key to all of this is the customertax table
it has two fields
  customer_id   which is = customer.id
  chart_id      which is = chart.id

Hopefully this enough info for you.

I "THINK" the following SQL will set both fields for you, but test carefully.

DELETE FROM ONLY customertax;  -- Start by emptying customertax

INSERT INTO customertax ( -- Add customer entries for GST
    SELECT id AS customer_id,
    (SELECT id FROM chart WHERE accno = '2310') AS chart_id
    FROM customer
  );

INSERT INTO customertax ( -- Add customer entries for PST
    SELECT id AS customer_id,
    (SELECT id FROM chart WHERE accno = '2320') AS chart_id
    FROM customer
  );

SELECT * FROM customertax; -- show you the results

I have tested this on a demo database here and it seems to work fine.
Instead of the final "SELECT * FROM customertax;"
you could use the first SQL block, which will show you much more detail.

This obviously removes all tax assignments for customers and creates the same assignments for all customers currently in the database.
Of course if any new customers are added you will either need to do this again (loosing any changes that have been made in the meantime) or manually set the tax for the new customer.
It should be possible to create a TRIGGER that automatically sets this for a new customer.

Hope this solves your problems.

Regards
David