Sending order info to other database for keeping a ledger

So I have my Zen-Cart working great but I needed a ledger to keep track of my income and expenses. I wrote one up in PHP and it's nice because it allows me to enter transactions that record important info.

Here is a picture of what it looks like:

This is a simple database that has a table named 'transactions' and then about 10 fields of various info I want to keep track of per transaction.

Now to make things easier I would love it if through the Zen admin area I could have Zen connect to and write an order's data as a new transaction. I think a good place to put this would be on the orders screen. A simple link that would say something like "Insert this into Ledger DB".

I looked at the orders.php file but couldn't figure out how to do it. Basically I just need someone to tell me where in the admin code to look to pull a transaction's info (for this example let's say order id, price and order date)... if I knew that then I could just pass those variables to my script and let it insert them, something like <a href="/myLedgerScript.php?action=insertTrans&orderID=$zenOrderID&price=$zenPrice">Insert into Ledger DB</a>. You get the idea?

I think this would be a pretty slick little system and I just need a little help figuring out where I can get what I need from the code. Thanks guys! :-)

Hi everyone. I don't think this would be that hard to do but I've gotten no responses.

I was thinking if I could add my code to the code that runs when the IPN hits then it could just send the fields of the purchase to the ledger. It would be pretty simple but I just need to know what file to look at and where in the code would be safe to put some php code to connect to my ledger's DB and insert the variable data for the new order.

Help is greatly appreciated!

You will need to create another routine for this and use a select for things like order id, price and order date for the correct tables/records that you desire. and then also a connect to your other DB and an insert

Use your DB tool or phpmyadmin and have a look at the tables & records contained for the information that you want.

a select might be like:

    $ledger_update = $db->Execute("select order_id, another_value, yet_another_value, and_the_last_value
                                    from " . TABLE_THE_TABLE_YOU_WANT . "
                                    where orders_id = '" . zen_db_input($oID) . "'
                                    order by date_added");

Get this working as a stand alone before embarking on trying to include it in the admin....

You should also have a way to mitigate if the order is cancelled or returned

As you know Zen Cart is not an accounting package

Kobra,

Thanks for the help! Well I see your strategy here but I think that's not really what I need.

See I already have a seperate PHP code that is used as the ledger (see the picture in my first post). It will be totally seperate from Zen.

I just want to be able to have fields (such as order number, transaction amount, date) be inserted in the database of the ledger (a totally unique and seperate DB I have created for my custom ledger program.

So I figured I can have the Paypal IPN script that does this for Zen also insert that info into my DB! I need help on where would be a good place to put a bit of code that connects to my ledger's DB and puts the variables into the DB.

This is intended to be used as a working ledger for the company. It will be nice because it will allow us to keep the books because one of us is in Canada and the other in the US... and is a much sleeker idea than emailing an Excel file back and forth!

myself:

and then also a connect to your other DB and an insert
I think that I got what you wanted and indicated that you will need to connect to the other DB before you can write to it.

If you insist on doing this as part of the IPN then this is where you would place the additional code

Thanks Kobra, I'll try and fiddle with this and see what I come up with. I'll post all my findings here so it might help others!

If anyone else has any ideas please let me know. :-)

As for "where" to put such a link in the admin orders page in order to push it to your other system, perhaps adding it on this line next to the order number might be suitable?```
<tr>
<td class="main"><strong><?php echo ENTRY_ORDER_ID . $oID; ?></strong>[B] <a href="....add stuff here....." target="_blank">Insert to Ledger</a>[/B]</td>
</tr>

In terms of what content to push, if you do a var_dump() on the $order object, you'll find tons of useful info there to pick from and build your code with.   Most of what you need will be in the $order->info array.

this sounds cool did you get it working?