Tracking sales associates referrals

Jerome,

I have a client that wants to provide coupons (or discounts) to different sale associates that they can give to prospects. If a prospects uses the coupon (or discount), they want to know how many that sales associate referred for each day.

As neither the hikashop_order_product_coupon table nor the hikashop_discount table have any ability to track this information, I was thinking that the best way accomplish this is to use the hikashop_order table as it has the order_discount_code field which is linked to the hikashop_discount table. As the client wants to assign a fixed unique 'code' for each sales associates, I could just use the discount_id field n the hikashop_discount table to store this unique 'code' for each sales associate and it would be in each hikashop_order record when an order is created. Then I could simply do a SQL command to retrieve order records from the hikashop_order table by the discount_id and the order_invoice_created fields to provide the client with the information via an export CSV format which I could run on a daily cron job and automatically email to the client's email address.

Is my logic sound?

Nicolas,

I do agree that all of the information my client needs is in the order table. Then I can just setup a cron job to generate a daily data dump of the table into a Excel CSV formatted file and email the file to my client. From that point on, she can import and view her sales with what type of discount was used (these are using discount code names to match up with individuals).

However, I have one hiccup in providing her the information. Date formatting.

When I view the order in Hikashop, it shows a date "2017-04-10 18:56". However, in the MySQL table it shows "1491872184". If I export the table to 'any' Excel importable format, that is the value that is imported into Excel. I have been unable to find a way to convert "1491872184" to "2017-04-10 18:56" in Excel. If you know how I can get this converted it would resolve a lot of issues.

Nicolas,

Well, I assume the date is stored in that value because of the way MySQL works. I wish it was stored as "MM-DD-YYYY HH:MM:SS".

However, the formula to change it to a readable value is =(((A1/60)/60)/24)+DATE(1970,1,1).

1491872184 = Tuesday, April 11, 2017