Notifying Manufacturers

[savage]

Hi all,
I have a downloadable game site ( http://games.savagesoftware.com.au ) and as such have contracts with developers aka manufacturers. Some of these developers would like to be notified each time a game is sold while others want a monthly email to be sent to them of how many units of their game have been sold.

How have others achieved this sort of functionality?

[Ajeh]

You could build in an automatic email into the orders class to send to the manufactuer of each product an email when their product(s) sell ...

[savage]

You could build in an automatic email into the orders class to send to the manufactuer of each product an email when their product(s) sell ...

Yes that is essentially what I'm trying to achieve.

I have added a contact_email field to the zen_manufacturers_info table and believe I can get a list of email addresses that need to be notified by running the following query....

select zm.manufacturers_name, zmi.contact_email, zpd.products_name from 
zen_orders_products zop 
inner join zen_products zp on ( zop.products_id = zp.products_id )
inner join zen_products_description zpd on ( zp.products_id = zpd.products_id )
inner join zen_manufacturers zm on ( zp.manufacturers_id = zm.manufacturers_id )
inner join zen_manufacturers_info zmi on ( zm.manufacturers_id = zmi.manufacturers_id ) 
where zop.orders_id = $zf_insert_id;

Would something like the following be ok...

$developers_query = "zm.manufacturers_name , zmi.contact_email, zpd.products_name from " . 
TABLE_ORDERS_PRODUCTS . " zop inner join " .
TABLE_PRODUCTS . " zp on ( zop.products_id = zp.products_id ) inner join " .
TABLE_PRODUCTS_DESCRIPTION . " zpd on ( zpd.products_id = zp.products_id ) inner join " .
TABLE_MANUFACTURERS . " zm on  ( zp.manufacturers_id = zm.manufacturers_id ) inner join " .
TABLE_MANUFACTURERS_INFO . " zmi on 
                         where zop.orders_id = '" . (int)$zf_insert_id . "';

    $developers_emails = $db->Execute($developers_query);

    while (!$developers_emails->EOF) 
    {
      zen_mail( $developers_emails->fields['manufacturers_name'], $developers_emails->fields['contact_email'],EMAIL_TEXT_SUBJECT . EMAIL_ORDER_NUMBER_SUBJECT . $zf_insert_id, $email_order, STORE_NAME, EMAIL_FROM, $developers_emails->fields['products_name'], 'checkout');
      $developers_emails->MoveNext();
    }

Is there a more efficient way to do this and is the best place at the bottom of the send_order_email() function?

Thanks.

[Ajeh]

You would need to test this on Orders with multiple products from multiple manufacturers and multiple products from the same manufacturer all in the same Order to ensure that the right product(s) is sent to the right manufacturer(s) ...

[Chuckl]

http://www.zen-cart.com/forum/showthread.php?t=29466

[savage]

My final code looks like this...

// Send email to developers
    $developers_query = "select zm.manufacturers_name , zmi.contact_email, zpd.products_name
      from " . TABLE_ORDERS_PRODUCTS . " zop 
      inner join " . TABLE_PRODUCTS . " zp on ( zop.products_id = zp.products_id ) 
      inner join " . TABLE_PRODUCTS_DESCRIPTION . " zpd on ( zpd.products_id = zp.products_id ) 
      inner join " . TABLE_MANUFACTURERS . " zm on  ( zp.manufacturers_id = zm.manufacturers_id ) 
      inner join " . TABLE_MANUFACTURERS_INFO . " zmi on ( zm.manufacturers_id = zmi.manufacturers_id )
      where zop.orders_id = '" . (int)$zf_insert_id . "'
      and zmi.notification_of_sale = 1";

    $developers_emails = $db->Execute($developers_query);
    
    while (!$developers_emails->EOF) 
    {
      zen_mail( $developers_emails->fields['manufacturers_name'], $developers_emails->fields['contact_email'], EMAIL_TEXT_SUBJECT . EMAIL_ORDER_NUMBER_SUBJECT . $zf_insert_id, $developers_emails->fields['products_name'], STORE_NAME, EMAIL_FROM, '<b>Just Sold!</b>', 'checkout');
      $developers_emails->MoveNext();
    }

But the emails are not being sent out even though it is the last thing to be executed in send_order_email().

Any ideas? I tried debugging it by adding a print_r( $developers_emails ); followed by die(); but that did not display anything. How do others debug this type of thing?

[savage]

Any suggestions because I can't seem to get this small piece of code to work.

What is the best way to debug that function?

[Chuckl]

I might be wildly wrong, but shouldn't that be

$developers_query = "select zm.manufacturers_name as manufacturers_name , zmi.contact_email as contact_email, zpd.products_name as product_name
from " . TABLE_ORDERS_PRODUCTS . " zop etc

[savage]

Hi Chuckl, thanks for your reply.
In phpMyAdmin, it does not require field name aliasing to work. I was always under the impression that if the column names were unique you did not need to alias them, but I might be wrong. Can anyone else confirm?

[DrByte]

If it's not sending anything but not giving any errors, then you're likely not getting the right results back from the query.

What happens if you run the query directly in phpMyAdmin? How many records does it bring back? If it's not getting any records, then your code is running exactly as you told it ... do nothing if nothing is found.

What happens if you remove the "and" clause?