Re: better solution for AR GST + PST +other Tax mass enable

[David Godfrey <..hidden..>]

Bob Miller wrote:

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

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

    

BTW: You can retrieve the Account Numbers to use in the above with

SELECT id as chart_id
FROM chart
WHERE link LIKE '%AR_tax%'

Here is a better solution as it doesn't require that you know the Tax Account Numbers,
and doesn't need to be changed for a different number of Tax Accounts.

  DELETE FROM ONLY customertax;  -- Start by emptying customertax
  INSERT INTO customertax ( -- Add customer entries for GST
      SELECT c.id AS customer_id,
             ch.id AS chart_id
      FROM customer c, chart ch
      WHERE ch.link LIKE '%AR_tax%'
  );

This block will Display Results if Required

SELECT -- Display Results Verbosely
    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;

Regards
David Godfrey