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

[Bob Miller <..hidden..>]

David,
You are a genius.  Thank you, that did work.  As per your first mail to
me, I took two backups first, though ;)


> > 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.  


> 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
> 
> 
> 
> 
Bob Miller
334-7117/633-3760
http://computerisms.ca
..hidden..
Network, Internet, Server,
and Open Source Solutions