We need a jsp file to run this sql to get invoice numbers that have not been tracked
select trans_id from user_trans where ( approve='1' or paypal_approve='1' or google_approve='1') and DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= trans_date and (confirmation_no is null or confirmation_no='');
data will have invoice numbers
| 671767 |
| 671769 |
| 671770 |
| 671771 |
| 671772 |
| 671777 |
| 671778 |
| 671779 |
| 671781 |
| 671782 |
| 671784 |
| 671783 |
then it will call this url and get an xml response
[login to view URL]
where tracking number is the invoice number
then it will insert the xml data into these 2 tables
create table DHLShipment (
trans_id int not null,
trackingnumber text not null,
deliveryconfirm text not null,
address1 text not null,
address2 text,
city text not null,
state text not null,
zip text not null,
weight double not null,
shipped date not null
)
create table DHLEvent (
trans_id int not null,
code int not null,
eventDate datetime not null,
city text not null,
state text not null,
description text not null,
postalcode text not null,
country text not null
)
and update table user_trans columns - confirmation_no with <deliveryconfirm> and dhl_tracking with <trackingnumber> where
trans_id <customerconfirm>