Get Some Data for Sync to ERP System

[Mas-J]

I want to get the data of Invoice that has been Paid.

It consist of data :

1. Invoice Number : tblinvoices
2. Client Custom Field : tblcustomfieldsvalues
3. Customer Name : tblclients
4. Product ID : tblproducts
5. Invoice Item : tblinvoiceitems
6. Invoice Date : tblinvoices
7. Due Date : tblinvoices
8. Tax : tblinvoices
9. Discount : tblpromotions
10. Total : tblinvoices

This result will be used for input in our ERP, I faced the issue when I want to export Product ID from tblproducts and Discount from tblpromotion.

I've try some query but didn't found any relation in tblproducts and tblpromotions with tblinvoices.

For now, I just can export Invoice Number, Client Custom Field, Customer Name, Invoice Item, Invoice Date, Due Date, Tax, Total

Here's the query:

SELECT 
    tblinvoices.invoicenum AS "No Invoice", 
    tblcustomfieldsvalues.value AS "Customer Code",
    CONCAT(tblclients.firstname, " ", tblclients.lastname) AS "Customer Name", 
    tblinvoiceitems.description AS "Specification", 
    DATE_FORMAT(tblinvoices.date,'%d/%m/%Y') AS "Posting Date", 
    DATE_FORMAT(tblinvoices.duedate,'%d/%m/%Y') AS "Due Date", 
    tblinvoices.tax AS "Tax", 
    tblinvoices.total AS "Total" 

FROM 
    tblinvoices 

INNER JOIN 
    tblinvoiceitems ON tblinvoices.id = tblinvoiceitems.invoiceid

INNER JOIN 
    tblclients ON tblinvoices.userid = tblclients.id 

INNER JOIN
    tblcustomfieldsvalues ON tblclients.id = tblcustomfieldsvalues.relid

WHERE tblinvoices.status = "Paid" AND tblcustomfieldsvalues.fieldid = "5";

 

Here's the output

Anyone can help me to get that productid & discount value ?

[brian!]

On 21/05/2019 at 08:43, Mas-J said:

I've try some query but didn't found any relation in tblproducts and tblpromotions with tblinvoices.

I don't think there would be a direct relationship.

I think the important database columns for you to check are 'type' and 'relid' in tblinvoiceitems...

because the value 'relid' relates to the 'id' of other database tables, depending upon the value to the 'type' field... so for example, the first 'relid' of 41 relates to the id column #41 in tbldomains; the second 'relid' of 29 relates to 'id' column # 29 in tblhosting and so on.

On 21/05/2019 at 08:43, Mas-J said:

Anyone can help me to get that productid & discount value ?

so if the invoice line refers to a service/product, that relid value will relate to tblhosting... then within tblhosting, you can get the 'packageid' (Product ID) value which relates to the id field within tblproducts...

with regards to promotion discount, that should be a line item in tblinvoiceitems...

if you had wanted the name of the promo code (and not value), then you could have got the orderid value from tblhosting and that relates to the id value in tblorders... which contains a column called 'promocode' which stores the name of the code used in the order.... you could then used that value in tblpromotions if you needed details of the promocode requirements.conditions etc - but you don't want that, just the value - so you can just pull the value from tblinvoiceitems as above.

[Kian]

As far as I know these should be all the available tblinvoiceitems.type:

• Setup
• Hosting
• Domain, DomainRegister, DomainTransfer
• Upgrade
• Item
• Addon
• PromoHosting, PromoDomain
• "Empty" for manually created lines

I suggest you to run multiple queries by "groups" of types like follows:

SELECT [...] FROM tblinvoiceitems AS t1 LEFT JOIN tblhosting AS t2 ON t1.relid = t2.id WHERE t1.type IN ("Hosting")
SELECT [...] FROM tblinvoiceitems AS t1 LEFT JOIN tbldomains AS t2 ON t1.relid = t2.id WHERE t1.type IN ("Domain", "DomainRegister", "DomainTransfer")

This to avoid ending up with a single huge query with tens of JOIN based on HAVING clauses.

[Mas-J]

On 5/22/2019 at 7:52 PM, brian! said:

I don't think there would be a direct relationship.

I think the important database columns for you to check are 'type' and 'relid' in tblinvoiceitems...

because the value 'relid' relates to the 'id' of other database tables, depending upon the value to the 'type' field... so for example, the first 'relid' of 41 relates to the id column #41 in tbldomains; the second 'relid' of 29 relates to 'id' column # 29 in tblhosting and so on.

so if the invoice line refers to a service/product, that relid value will relate to tblhosting... then within tblhosting, you can get the 'packageid' (Product ID) value which relates to the id field within tblproducts...

with regards to promotion discount, that should be a line item in tblinvoiceitems...

if you had wanted the name of the promo code (and not value), then you could have got the orderid value from tblhosting and that relates to the id value in tblorders... which contains a column called 'promocode' which stores the name of the code used in the order.... you could then used that value in tblpromotions if you needed details of the promocode requirements.conditions etc - but you don't want that, just the value - so you can just pull the value from tblinvoiceitems as above.

Thanks Brian, I've try your suggest and it work but I just realize the promotion/discount will be an item in invoice.

Discuss with our ERP dev, so there's little change what data will be exported like below:

1. Invoice Number : tblinvoices
2. Client Custom Field : tblcustomfieldsvalues
3. Product Group ID : tblproductgroups
4. Product Specification : tblinvoiceitems
5. Invoice Date : tblinvoices
6. Due Date : tblinvoices
7. Tax : tblinvoices
8. Quantity : which table ? maybe tblinvoiceitems
9. Unit Price : tblinvoiceitems
10. Total : tblinvoices

Would you give me a clue what should the query for get the Group ID, Quantity and Unit Price from the query before ?

 

On 5/22/2019 at 8:26 PM, Kian said:

As far as I know these should be all the available tblinvoiceitems.type:

• Setup
• Hosting
• Domain, DomainRegister, DomainTransfer
• Upgrade
• Item
• Addon
• PromoHosting, PromoDomain
• "Empty" for manually created lines

I suggest you to run multiple queries by "groups" of types like follows:

SELECT [...] FROM tblinvoiceitems AS t1 LEFT JOIN tblhosting AS t2 ON t1.relid = t2.id WHERE t1.type IN ("Hosting")
SELECT [...] FROM tblinvoiceitems AS t1 LEFT JOIN tbldomains AS t2 ON t1.relid = t2.id WHERE t1.type IN ("Domain", "DomainRegister", "DomainTransfer")

This to avoid ending up with a single huge query with tens of JOIN based on HAVING clauses.

Thanks for the suggestion, I'll try after I got the goal 🙂

 

[brian!]

On 27/05/2019 at 09:59, Mas-J said:

Would you give me a clue what should the query for get the Group ID, Quantity and Unit Price from the query before ?

in your context...

• Product Group ID will be the 'gid' value from the tblhosting table.
• Quantity doesn't exist as a column in its own right, and is usually obtained by doing a count of that line item in the tblinvoiceitems table.
• Unit Price should be the 'amount' column from the tblinvoiceitems table.