[Resolved] Trying to Add Another Report

[Dirty Butter]

I've successfully added some extra fields to the reports. They correctly show the data I want, along with all the stock CC data.

But what I would really like to do is create an ADDITIONAL reports page with just the information I need to export to MailChimp for a scheduled email sent to my customers whose orders will arrive in the next few days.

I have so far duplicated the reports.index.php and reports.index.inc.php files, adding mc to the names - mcreports.index.php and mcreports.index.inc.php. They are exact copies of their counterparts, just renamed.

I attempted to open my mcreports page by changing the url of (admin - url)php?_g=reports to (admin - url)php? g=mcreports. I see all my headers, but no data.

I don't know what else needs to be done to make this work.

1. Why didn't mcreports fill with data? Turned out to be a tired brain error on my part.

2. I can't find where the dashboard menu is created, I found the menu listings are in element.navigation.inc.php - but I still don't know how to add my MCReports to the menu. - figured that out, too. Needed to add new sections in language/definitions,xml - copied and renamed from the existing navigation reports lines.

 

 

 

 

[bsmither]

Just a quick observation: in mcreports.index.inc.php, second to the last line, did you edit the name of the template being called?

And line 26: currently
httpredir('?_g=reports');

 

[Dirty Butter]

I fixed those and another mistake that messed up showing the data. BUT it is still showing the reports.index.php data, NOT the mcreports.index.data. The link says it is php?_g=mcreports#results and the breadcrumb says Mcreports. But deleting some of the fields in mcreports does nothing.

The last line of mcreports.index.inc.php is foreach ($GLOBALS['hooks']->load('admin.reports.display') as $hook) include $hook;

So do I need to add my own hook? I tried changing to (admin.mcreports.display,) but that did nothing.

[Dirty Butter]

It's working now!

The only thing left is to add an if/then so If the Country is the United States THEN the file will show USA - If International it would show OTHER.

[bsmither]

In the template file element.navigation.php is where you will want to add the link to your page.

[Dirty Butter]

I finally figured the navigation part out. I'm getting confused with the if/then right now LOL. They are always trial and error for me.

NOT right:

         <td>{if {$data.country}="United States"} USA</td>
{else}
<td>{$data.country} Other</td>
{/if}

 

 

[bsmither]

If you are sure the country is getting spelled "United States", then this is the test:

{if $data.country eq "United States"}USA{else}Other{/if}

What you want printed is actually not anything of what $data.country could be. But if it happens to be equal to "United States", then print USA, elsewise, print Other.

[Dirty Butter]

I had just gotten this to work:

         <td>
{if {$data.country}!="United States"}Other
{else}
USA</td>
{/if}

But I tried yours, and it did not put anything in the field.

But now, when I used the export button, I get the full sales data, NOT subset I want from mcreports. What I want in the export is correctly showing on the mcreports page.

[bsmither]

In your file mcreports.index.inc.php, near the middle of the file:

Find:
		## Run line of external report data
		if (isset($external_report) && is_object($external_report)) $external_report->report_order_data($order_summary);

If you have added or subtracted any columns after these lines, then the stock data from the $order_summary array has already been written to the external report output.

[Dirty Butter]

The only lines that are different between stock reports.index.inc.php and MY reports.index.inc.php and mcreports.index.inc.php are:

*YOUR CHANGE line 26         httpredir('?_g=mcreports');

*********************************************************************

MY line 112 and below

    'ship_date',
    'ship_tracking'
);
## I ADDED SHIP DATE AND TRACKING TO ARRAY ABOVE

*********************************************************************
MY line 141 and below

        ##MY ADDITION
        $order_summary['ship_date'] = sprintf('"=""%s"""', $order_summary['ship_date']);
        $order_summary['ship_tracking'] = sprintf('"=""%s"""', $order_summary['ship_tracking']);
        ## END MY ADDITION
        $order_summary['country']    = (is_numeric($order_summary['country'])) ? getCountryFormat($order_summary['country']) : $order_summary['country'];

*********************************************************************

MY line 210 and below

##MY ADDED COLUMNS
for ($i = 1; $i <= 7; ++$i) {

 

********************************************************************

*YOUR CHANGE on the next to the last line:

$page_content = $GLOBALS['smarty']->fetch('templates/mcreports.index.php');

 

 

[bsmither]

Please confirm that, having added 'ship_date' and 'ship_tracking' to the array at line 112, that you also added a trailing comma after 'gateway'. That is:

Near line 109:
Was:
	'phone',
	'email',
	'gateway'
);

Now:
	'phone',
	'email',
	'gateway',     <== Note the comma
	'ship_date',
	'ship_tracking'
);

Although there is nothing wrong, per se, with the sprintf() functions in your new code, I am wondering about all the double-quotes.

[Dirty Butter]

I remember we had some difficulty getting the ship date and tracking code to show properly - will try to find that original discussion. No luck - couldn't find the old thread where you helped me add those two columns to reports.

The comma IS there after gateway.

Should I be taking out all the array terms and references in MY mcreports.index.inc.php file for the columns I do NOT want in my MC Report?

 

[Dirty Butter]

Using this looks MUCH better for the sprintf:

$order_summary['ship_date'] = sprintf( $order_summary['ship_date']);
$order_summary['ship_tracking'] = sprintf($order_summary['ship_tracking']);

 

 

 

[Dirty Butter]

On the chance it would work, I deleted all the array items that were not used in mcreports and changed the name of the report to match the language edits I had added for mcreports. It now exports just the terms I want exported.

The only part that didn't work right is the USA. It shows up in the export as United States. I have two different emails I send in MC. The USA one is sent 3-5 days after shipment (depending on where the weekend falls) and the other sends in 2-4 weeks (depending on where the international delivery is). The scheduling is done manually, but this whole exercise should allow me to automatically import that day's sales into MC - ready to schedule based on country for any Customer ID information that was added/updated that day.

It's far from perfect - but I've learned a lot doing this, and it should help speed up order completion.

So it looks like the if/then for USA needs to be in the mcreports.index.inc.php file somehow, as well as the mcreports.index.php file.

 

[bsmither]

The statements:
$order_summary['ship_date'] = sprintf( $order_summary['ship_date']);
$order_summary['ship_tracking'] = sprintf($order_summary['ship_tracking']);

are not correct. There needs to be a "mask" for the data item to be applied against.

I would say do not bother with these two statements as including them in the array is enough to get them.

These statements are desirable only if you need to construct something unique that uses the data item as a base.

 

[Dirty Butter]

I want them to use in the email template on MailChimp. I've sent myself a test campaign email, using a test order export. We'll see how it goes.

I still need help with the country_d format in the export. I realized somewhere along the way that I didn't want country in the export, but country_d. The existing code takes the country_d ISO number and changes it to the text country name.

The export code currently has this, which outputs United States for 840.

$order_summary['country_d']    = (is_numeric($order_summary['country_d'])) ? getCountryFormat($order_summary['country_d']) : $order_summary['country_d'];

So whichever is easier to do - If country_d=840 then USA else Other

OR

if country_d="United States" else Other
 

I need help with how to make it output Other for anything that is not 840/United States (Either one can be fixed in MailChimp, but I have to have a way to get Other for anything NOT the United States.)

 

[bsmither]

Earlier, we were editing the mcreports.index.php skin template, testing for "United States". Obviously, we care nothing about what gets shown to the admin (well, not for the external report for MC).

So, we must focus on mcreports.index.inc.php.

Find:
$order_summary['country'] = (is_numeric($order_summary['country'])) ? getCountryFormat($order_summary['country']) : $order_summary['country'];
$order_summary['state'] = (is_numeric($order_summary['state'])) ? getStateFormat($order_summary['state']) : $order_summary['state'];

Add BEFORE:
$order_summary['country_mailchimp'] = ($order_summary['country_d'] == 840) ? "USA" : "Other";

The array of column names is only what to fetch from the database. So, no need to add 'country_mailchimp' to that array. But, it will end up in the report.

We will assume the ['country_d'] of a USA order is properly databased as 840.

(N.B. The style of programming where a source variable is used to calculate a new result, then the new result is substituted into that same variable is a style I seriously disagree with. That's why it is necessary to put the new statement ahead of these other statements in this group because $order_summary['country_id'] holds 840 to start and something else afterwards. Wanting to use $order_summary['country_d'] after these statements is fraught with uncertainty.)

[Dirty Butter]

I was trying to create a if/else statement AFTER the country_d in mcreports.index.inc.php - so that's why it would not work.

That was the last piece of the puzzle.

I now have a way to export/import to MailChimp that properly fills all the merge fields I have in the email, plus makes it easy to choose the scheduling of the emails based on USA/Other. I've tested it and it works perfectly!!!

Thank you SO much for your kind patience with me. I've been wanting to do this for a long time and finally got up the nerve to try it.

[Dirty Butter]

I tried refreshing the page before the second export, but the browser re-sends the filter - and it triggers the CSRF anyway. I have to click on the dashboard menu MC Reports again to keep it from doing that.

Also, I've realized I need one more change to mcreports.index.inc? so the Filtering is done on the ship_date, NOT the order_date.

I'm guessing the section that needs change is this, but I had no luck just guessing where to change to ship_date:

$i = 0;
## Date filtering
foreach ($date_range as $key => $value) {
    $date   = (!empty($value) && preg_match('#^([\d]{2,4}[/-][\d]{1,2}[/-][\d]{1,2})$#', $value)) ? $value : $default_date[$key];
    $parts   = preg_split('#[^\d]#', $date);
    $timestamp  = ($i) ? mktime(23, 59, 59, $parts[1], $parts[2], $parts[0]) : mktime(false, false, false, $parts[1], $parts[2], $parts[0]);
    $dates[$key]  = $timestamp;
    $human_date[]  = date('j M Y', $timestamp);
    ++$i;
}
unset($date, $i, $parts, $timestamp);
$where = sprintf('order_date >= %d AND order_date <= %d', $dates['from'], $dates['to']);

 

[Dirty Butter]

Trying to cut down on the number of clicks required - is there a way to use the current date by default for the "to" date on the stock Sales Reports and my modified MC Reports? How about the "from" date as well for the MC Report? (I only need the data from that given day.)

How can I modify the MC Reports export to automatically SAVE to a location?

[bsmither]

In the database table CubeCart_order_summary, the 'order_date' uses the epoch timestamp, while the 'ship_date' uses the standard SQL DATE-type value (human readable YYYY-MM-DD).

The stock code above takes in the dates (which look exactly like the SQL format), and calculates the epoch timestamp from them, so as to get a proper parameter for the WHERE clause.

But, making a WHERE clause on the 'ship_date', we can use the dates as accepted.

From:
$where = sprintf('order_date >= %d AND order_date <= %d', $dates['from'], $dates['to']);

To:
$where = sprintf('ship_date >= "%s" AND ship_date <= "%s"', $date_range['from'], $date_range['to']);

A few lines above that:

From:
$default_date = array('from' => strftime('%Y-%m-01'), 'to' => strftime('%Y-%m-%d'));

To:
$default_date = array('from' => strftime('%Y-%m-%d'), 'to' => strftime('%Y-%m-%d')); // from today to today

We can try saving to a location. It will save to the root folder of CubeCart unless you code it differently:

Find:
		deliverFile(false, false, $file_content, $file_name.'.csv');
		exit;
	}
	## Show table footer

Change to:
		$filepathname = CC_ROOT_DIR.'/'.$file_name.'.csv';
		if(file_exists($filepathname)) unlink($filepathname);
		if (file_put_contents($filepathname, $file_content)) {
			$GLOBALS['main']->setACPNotify("Sales Report saved to ".$filepathname);
		} else {
			$GLOBALS['main']->setACPWarning("Failed saving Sales Report");
		}
		httpredir('?_g=mcreports');
	}
	## Show table footer

 

Edited July 1, 2016 by bsmither
Re-edited because prior edit didn't take.

[Dirty Butter]

Date change worked as expected. I've tried the export to file section several times, and it crashes the page.

//ORIGINAL
		deliverFile(false, false, $file_content, $file_name.'.csv');
		exit;
	}
	## Show table footer

/*$filepathname = CC_ROOT_DIR.'/'.$file_name.'.csv';
		if(file_exists($filepathname)) unlink($filepathname);
		if (file_put_contents($filename, $mapdata)) {
			$GLOBALS['main']->setACPNotify("Sales Report saved to ".$filepathname);
		} else {
			$GLOBALS['main']->setACPWarning(Failed saving Sales Report");
		}
		httpredir('?_g=mcreports');
	}
	## Show table footer */


	$tally['orders'] = count($orders);
	foreach ($tally as $key => $value) {
		$tallyformatted[$key] = ($key=='orders') ? $value : sprintf('%.2F', $value);

 

[bsmither]

Something didn't get edited adequately:

Change to:
		$filepathname = CC_ROOT_DIR.'/'.$file_name.'.csv';
		if(file_exists($filepathname)) unlink($filepathname);
		if (file_put_contents($filepathname, $file_content)) {
			$GLOBALS['main']->setACPNotify("Sales Report saved to ".$filepathname);
		} else {
			$GLOBALS['main']->setACPWarning("Failed saving Sales Report");
		}
		httpredir('?_g=mcreports');
	}
	## Show table footer

I have edited the prior post.

[Dirty Butter]

That worked! I did tweak the creation of the mcreports filename to simplify it and get all the spaces out of it.

	$GLOBALS['smarty']->assign('REPORT_DATE', $smarty_data['report_date']);
	if (isset($_POST['download']) || (isset($_POST['external_report']) && is_array($_POST['external_report']))) {
		$GLOBALS['debug']->supress(true);
		if (isset($_POST['download'])) {
			$file_content  = implode("\r\n", $data);
		$file_name   = $lang['mcreports']['mcreports_filename'];
		} else {
			$file_content  = $external_report->_report_data;
			$file_name   = ucfirst($module_name[0]).' '.$lang['mcreports']['data'].' '.$download_range;
		}

But fiddling with this makes me wonder what an external report is....

[Dirty Butter]

Still wondering what an external report is, if someone has time to explain.

Although the code to save the mcreports file works properly, I've temporarily gone back to the original export that has to be saved to my hard drive. It occurred to me that there was no expectation of privacy for the email addresses of our customer's data in the export if it were saved to the store root.

What do you think?