1.8 Alpha - Trouble With Some API Calls

[dan]

@cslaughter

Also tried mutipart after reading this; same result.

You are using separate calls so I don’t think you have a conflict there

As far as I can see from the API doc, the new version of Guzzle only takes 3 parameters, but you are providing 4 - you should change to:

$client->request($params['api_method'], SERVER_BASE.'/'.$params['api_path'], [
    'headers' => [
    'Authorization' => 'Bearer '.$_SESSION['client_access_token']
    ],
    'form_params' => [
        'fromDt' => $fromDt,
        'toDt' => $toDt,
        'displayId' => intval($displayId),
        'mediaId' => intval($mediaId)
    ]
]

that syntax might not be 100% accurate, but you get the idea?

[cslaughter]

@dan

the new version of Guzzle only takes 3 parameters, but you are providing 4

Doh… yes that is what the problem is. Thank You!

Update: Or maybe that is not the problem. I tried the following code and it also seemed to return an empty array. I do see a 200 response in the returned header.

$request = $client->request($params['api_method'], SERVER_BASE.'/'.$params['api_path'], [
	'headers' => [
		'Authorization' => 'Bearer '.$_SESSION['client_access_token']
	],
	'form_params'=>[
		'fromDt'=>'2015-01-01 00:00:00',
		'toDt'=>'2015-10-10 23:59:59'
	]
]);

Update 2: Tried to pull up the stats in the CMS 1.8 Alpha, and it also is blank. I manually checked the database and there is tons of data in there.

In addition to this we are also having trouble with the Displays api. No matter what we enter it always returns every display. In the CMS if we try and search by name no matter what we enter it returns nothing.

[dan]

@cslaughter

In the CMS if we try and search by name no matter what we enter it returns nothing.

Well spotted: Can't filter Display Page by Name · Issue #614 · xibosignage/xibo · GitHub

@cslaughter

No matter what we enter it always returns every display

This plays into this: Ensure the ID/Name are filterable for all get routes · Issue #594 · xibosignage/xibo · GitHub - i.e. we need to rationalise the filter criteria and how they are provided.

At the moment filter_display in the query will get you a filter by display name. This needs rationalising across all GET calls so that the parameters are named as per the object they relate to - in this case it should be “display”

@cslaughter

Update 2: Tried to pull up the stats in the CMS 1.8 Alpha, and it also is blank. I manually checked the database and there is tons of data in there.

You may need to enable test mode and pull out the SQL it is running to see what’s wrong.

[cslaughter]

@dan

You may need to enable test mode and pull out the SQL it is running to see what’s wrong.

Enabled Test mode and went Display - Manage. We started seeing this under “Errors in the Last 12 hours”:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'xml' in 'field list' 

I have the collect and save data too if it helps. It is too big to fit here.

I wonder if the data I am seeing in the database is simply the download stats and what not, and there is really nothing about the proof of display being recorded, which is why we get an empty array back from the API when calling the stats.

[dan]

Display Down events and Proof of Play are the only things in that table. Bandwidth is kept in the bandwidth table.

The message makes me think you have the latest develop code now (after release?) and are running fowl of this commit.

I’ve just made another commit that should fix it.

---

Back to the original problem - the stats SQL should be logged - i.e. clear the log, run your API call (and only your API call), grab the log.

[cslaughter]

@dan

The message makes me think you have the latest develop code now (after release?)

Yes you are correct.

We will try the latest commit later today.

@dan

Back to the original problem - the stats SQL should be logged - i.e. clear the log, run your API call (and only your API call), grab the log.

I tried that… I got 5 pages of SQL logs. Not sure why that is yet, maybe our script.

UPDATE: CMS log after trying to return stats via the API:

12	fb8cef0	2015-10-23 21:42	WEB	GET	DEBUG			SQL = SELECT ModuleID, Module, Name, Enabled, Description, render_as, settings, RegionSpecific, ValidExtensions, ImageUri, PreviewEnabled, assignable, SchemaVersion, viewPath, `class` FROM `module` WHERE 1 = 1 ORDER BY Module. Params = array ( ).
11	4ee5ee7	2015-10-23 21:42	API	GET	DEBUG		/stats	SQL = SELECT display.displayId, display.display, display.defaultLayoutId, layout.layout AS defaultLayout, display.license, display.licensed, display.licensed AS currentlyLicenced, display.loggedIn, display.lastAccessed, display.isAuditing, display.inc_schedule AS incSchedule, display.email_alert AS emailAlert, display.alert_timeout AS alertTimeout, display.clientAddress, display.mediaInventoryStatus, display.macAddress, display.macAddress AS currentMacAddress, display.lastChanged, display.numberOfMacAddressChanges, display.lastWakeOnLanCommandSent, display.wakeOnLan AS wakeOnLanEnabled, display.wakeOnLanTime, display.broadCastAddress, display.secureOn, display.cidr, X(display.GeoLocation) AS latitude, Y(display.GeoLocation) AS longitude, display.version_instructions AS versionInstructions, display.client_type AS clientType, display.client_version AS clientVersion, display.client_code AS clientCode, display.displayProfileId, display.currentLayoutId, currentLayout.layout AS currentLayout, display.screenShotRequested, display.storageAvailableSpace, display.storageTotalSpace, displaygroup.displayGroupId, displaygroup.description FROM `display` INNER JOIN `lkdisplaydg` ON lkdisplaydg.displayid = display.displayId INNER JOIN `displaygroup` ON displaygroup.displaygroupid = lkdisplaydg.displaygroupid LEFT OUTER JOIN layout ON layout.layoutid = display.defaultlayoutid LEFT OUTER JOIN layout currentLayout ON currentLayout.layoutId = display.currentLayoutId WHERE 1 = 1 AND displaygroup.isDisplaySpecific = 1 ORDER BY display. Params = array ( ).
10	4ee5ee7	2015-10-23 21:42	API	GET	DEBUG		/stats	Converted Times received are: FromDt=2015-10-22 21:42:08. ToDt=2015-10-23 21:42:08
9	4ee5ee7	2015-10-23 21:42	API	GET	DEBUG		/stats	SQL = SELECT `group`.group, `group`.groupId, `group`.isUserSpecific, `group`.isEveryone, `group`.libraryQuota FROM `group` WHERE 1 = 1 AND `group`.groupId IN (SELECT groupId FROM `lkusergroup` WHERE userId = :userId) AND isUserSpecific = :isUserSpecific AND isEveryone = :isEveryone . Params = array ( 'userId' => 1, 'isUserSpecific' => 0, 'isEveryone' => 0, ).
8	4ee5ee7	2015-10-23 21:42	API	GET	DEBUG		/stats	Loading 1. All Objects = 0
7	4ee5ee7	2015-10-23 21:42	API	GET	DEBUG		/stats	SQL = SELECT `user`.userId, userName, userTypeId, loggedIn, email, `user`.homePageId, pages.title AS homePage, lastAccessed, newUserWizard, retired, CSPRNG, UserPassword AS password, group.groupId, group.group, IFNULL(group.libraryQuota, 0) AS libraryQuota FROM `user` INNER JOIN lkusergroup ON lkusergroup.userId = user.userId INNER JOIN `group` ON `group`.groupId = lkusergroup.groupId AND isUserSpecific = 1 LEFT OUTER JOIN `pages` ON pages.pageId = `user`.homePageId WHERE 1 = 1 AND user.userId = :userId ORDER BY userName. Params = array ( 'userId' => 1, ).

If we run the sql of id 7 and 9 from the log in phpMyAdmin we get errors like this:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':userId ORDER BY userName'

Which made us look at this from the log:

ID 7

:userId

Traced to line 188 in lib/Factory/UserFactory.php

Also…

@dan

I’ve just made another commit that should fix it.

Applied and still getting:

286	2015-10-24 00:22	SQLSTATE[42S22]: Column not found: 1054 Unknown column 'xml' in 'field list'

[dan]

@cslaughter

Applied and still getting:

Sorry, i’ve not merged my fork into the main one - i’ve got some other pending changes that I don’t want in there - can you patch in the code? Stop updating XML in Layout update · dasgarner/xibo-cms@71e49fa · GitHub

Logs

Regarding logs - you can identify the log messages in all unique call using the Run Number - in the case above 4ee5ee7 is associated with your API call.

ID 11 corrisponds with this statement in the Stats controller: xibo-cms/lib/Controller/Stats.php at develop · dasgarner/xibo-cms · GitHub.

Unfortunately the main query isn’t logged, you can add a line on 212: Log::sql($sql, $params) to get that SQL out.

Regarding the SQL statements in the logs - they have a text SQL string and then a parameters array - you will need to manually substitute the :param with the parameter value from the parameters array, in your example :userId is 1.

[cslaughter]

@dan

can you patch in the code?

Not a problem. It was line 790, it has now been removed.

Thank you for the information regarding the logs.

[cslaughter]

@dan

Unfortunately the main query isn’t logged, you can add a line on 212: Log::sql($sql, $params) to get that SQL out.

Added

212: Log::sql($sql, $params);

New Log:

38	9082913	2015-10-30 10:02	XMDS	POST	DEBUG		RegisterDisplay	Config loaded [0]: []
37	9082913	2015-10-30 10:02	XMDS	POST	DEBUG		RegisterDisplay	SQL = SELECT displayProfileId, name, type, config, isDefault, userId FROM `displayprofile` WHERE 1 = 1 AND displayProfileId = :displayProfileId . Params = array ( 'displayProfileId' => 3, ).
36	9082913	2015-10-30 10:02	XMDS	POST	DEBUG		RegisterDisplay	SQL = SELECT display.displayId, display.display, display.defaultLayoutId, layout.layout AS defaultLayout, display.license, display.licensed, display.licensed AS currentlyLicenced, display.loggedIn, display.lastAccessed, display.isAuditing, display.inc_schedule AS incSchedule, display.email_alert AS emailAlert, display.alert_timeout AS alertTimeout, display.clientAddress, display.mediaInventoryStatus, display.macAddress, display.macAddress AS currentMacAddress, display.lastChanged, display.numberOfMacAddressChanges, display.lastWakeOnLanCommandSent, display.wakeOnLan AS wakeOnLanEnabled, display.wakeOnLanTime, display.broadCastAddress, display.secureOn, display.cidr, X(display.GeoLocation) AS latitude, Y(display.GeoLocation) AS longitude, display.version_instructions AS versionInstructions, display.client_type AS clientType, display.client_version AS clientVersion, display.client_code AS clientCode, display.displayProfileId, display.currentLayoutId, currentLayout.layout AS currentLayout, display.screenShotRequested, display.storageAvailableSpace, display.storageTotalSpace, displaygroup.displayGroupId, displaygroup.description FROM `display` INNER JOIN `lkdisplaydg` ON lkdisplaydg.displayid = display.displayId INNER JOIN `displaygroup` ON displaygroup.displaygroupid = lkdisplaydg.displaygroupid LEFT OUTER JOIN layout ON layout.layoutid = display.defaultlayoutid LEFT OUTER JOIN layout currentLayout ON currentLayout.layoutId = display.currentLayoutId WHERE 1 = 1 AND displaygroup.isDisplaySpecific = 1 AND display.license = :license ORDER BY display. Params = array ( 'license' => 'af48608c-XXXX-XXXX-9977-8465b0da63d1', ).
35	9082913	2015-10-30 10:02	XMDS	POST	DEBUG		RegisterDisplay	serverKey: XXXXXXXXX, hardwareKey: af48608c-XXXX-XXXX-9977-8465b0da63d1, displayName: tablet
34	9082913	2015-10-30 10:02	XMDS		DEBUG			SQL = SELECT `user`.userId, userName, userTypeId, loggedIn, email, `user`.homePageId, pages.title AS homePage, lastAccessed, newUserWizard, retired, CSPRNG, UserPassword AS password, group.groupId, group.group, IFNULL(group.libraryQuota, 0) AS libraryQuota FROM `user` INNER JOIN lkusergroup ON lkusergroup.userId = user.userId INNER JOIN `group` ON `group`.groupId = lkusergroup.groupId AND isUserSpecific = 1 LEFT OUTER JOIN `pages` ON pages.pageId = `user`.homePageId WHERE 1 = 1 AND user.userId = :userId ORDER BY userName. Params = array ( 'userId' => 1, ).
33	9082913	2015-10-30 10:02	XMDS		DEBUG			SQL = SELECT ModuleID, Module, Name, Enabled, Description, render_as, settings, RegionSpecific, ValidExtensions, ImageUri, PreviewEnabled, assignable, SchemaVersion, viewPath, `class` FROM `module` WHERE 1 = 1 ORDER BY Module. Params = array ( ).

[dan]

Your new log is all from XMDS, but I think we are still trying to debug API ?

I’ve written a quick manual page to help you understand the logs, what they come from and what to expect when you hit the various endpoints.

You can find it here: https://github.com/dasgarner/xibo-manual/blob/develop/source/en/advanced_logging.md. Hopefully this points you in the right direction.

[cslaughter]

@dan

Your new log is all from XMDS, but I think we are still trying to debug API ?

Yes, I thought it was really strange to get XMDS. Not sure what I did there. This should be correct:

21	c0a5a31	2015-11-01 15:00	API	GET	DEBUG		/stats	SQL = SELECT stat.type, display.Display, layout.Layout, IFNULL(widgetoption.value, widget.type) AS Name, COUNT(StatID) AS NumberPlays, SUM(TIME_TO_SEC(TIMEDIFF(end, start))) AS Duration, MIN(start) AS MinStart, MAX(end) AS MaxEnd FROM stat INNER JOIN display ON stat.DisplayID = display.DisplayID INNER JOIN layout ON layout.LayoutID = stat.LayoutID INNER JOIN `widget` ON `widget`.widgetId = stat.MediaID LEFT OUTER JOIN `widgetoption` ON `widgetoption`.widgetId = `widget`.widgetId AND `widgetoption`.type = 'attribute' AND `widgetoption`.option = 'name' WHERE stat.type <> 'displaydown' AND stat.end > :fromDt AND stat.start <= :toDt AND stat.displayID IN (2,1) GROUP BY stat.type, display.Display, layout.Layout, IFNULL(widgetoption.value, widget.type) ORDER BY stat.type, display.Display, layout.Layout, IFNULL(widgetoption.value, widget.type) . Params = array ( 'fromDt' => Jenssegers\Date\Date::__set_state(array( 'date' => '2015-10-31 15:00:06', 'timezone_type' => 3, 'timezone' => 'America/Los_Angeles', )), 'toDt' => Jenssegers\Date\Date::__set_state(array( 'date' => '2015-11-01 15:00:06', 'timezone_type' => 3, 'timezone' => 'America/Los_Angeles', )), ).
20	c0a5a31	2015-11-01 15:00	API	GET	DEBUG		/stats	SQL = SELECT display.displayId, display.display, display.defaultLayoutId, layout.layout AS defaultLayout, display.license, display.licensed, display.licensed AS currentlyLicenced, display.loggedIn, display.lastAccessed, display.isAuditing, display.inc_schedule AS incSchedule, display.email_alert AS emailAlert, display.alert_timeout AS alertTimeout, display.clientAddress, display.mediaInventoryStatus, display.macAddress, display.macAddress AS currentMacAddress, display.lastChanged, display.numberOfMacAddressChanges, display.lastWakeOnLanCommandSent, display.wakeOnLan AS wakeOnLanEnabled, display.wakeOnLanTime, display.broadCastAddress, display.secureOn, display.cidr, X(display.GeoLocation) AS latitude, Y(display.GeoLocation) AS longitude, display.version_instructions AS versionInstructions, display.client_type AS clientType, display.client_version AS clientVersion, display.client_code AS clientCode, display.displayProfileId, display.currentLayoutId, currentLayout.layout AS currentLayout, display.screenShotRequested, display.storageAvailableSpace, display.storageTotalSpace, displaygroup.displayGroupId, displaygroup.description FROM `display` INNER JOIN `lkdisplaydg` ON lkdisplaydg.displayid = display.displayId INNER JOIN `displaygroup` ON displaygroup.displaygroupid = lkdisplaydg.displaygroupid LEFT OUTER JOIN layout ON layout.layoutid = display.defaultlayoutid LEFT OUTER JOIN layout currentLayout ON currentLayout.layoutId = display.currentLayoutId WHERE 1 = 1 AND displaygroup.isDisplaySpecific = 1 ORDER BY display. Params = array ( ).
19	c0a5a31	2015-11-01 15:00	API	GET	DEBUG		/stats	Converted Times received are: FromDt=2015-10-31 15:00:06. ToDt=2015-11-01 15:00:06
18	c0a5a31	2015-11-01 15:00	API	GET	DEBUG		/stats	SQL = SELECT `group`.group, `group`.groupId, `group`.isUserSpecific, `group`.isEveryone, `group`.libraryQuota FROM `group` WHERE 1 = 1 AND `group`.groupId IN (SELECT groupId FROM `lkusergroup` WHERE userId = :userId) AND isUserSpecific = :isUserSpecific AND isEveryone = :isEveryone . Params = array ( 'userId' => 1, 'isUserSpecific' => 0, 'isEveryone' => 0, ).
17	c0a5a31	2015-11-01 15:00	API	GET	DEBUG		/stats	Loading 1. All Objects = 0
16	c0a5a31	2015-11-01 15:00	API	GET	DEBUG		/stats	SQL = SELECT `user`.userId, userName, userTypeId, loggedIn, email, `user`.homePageId, pages.title AS homePage, lastAccessed, newUserWizard, retired, CSPRNG, UserPassword AS password, group.groupId, group.group, IFNULL(group.libraryQuota, 0) AS libraryQuota FROM `user` INNER JOIN lkusergroup ON lkusergroup.userId = user.userId INNER JOIN `group` ON `group`.groupId = lkusergroup.groupId AND isUserSpecific = 1 LEFT OUTER JOIN `pages` ON pages.pageId = `user`.homePageId WHERE 1 = 1 AND user.userId = :userId ORDER BY userName. Params = array ( 'userId' => 1, ).

Thank you for the manual on the log.

[dan]

Ok, i’ve got something for you:

Tweaked stats so that the date filter works.

Sorry its taken so long to get to the root of the issue!

[cslaughter]

Awesome. No worries on the time factor, after all it is a developing project.

I will see about trying out the changes and see what happens.

Update: Made the changes. Tried the date filter again and still nothing on the return but an empty array.
Log:

12	3f7f247	2015-11-05 20:28	API	GET	DEBUG		/stats	SQL = SELECT stat.type, display.Display, layout.Layout, IFNULL(widgetoption.value, CONCAT(widget.type, '-', widget.widgetId)) AS Name, COUNT(StatID) AS NumberPlays, SUM(TIME_TO_SEC(TIMEDIFF(end, start))) AS Duration, MIN(start) AS MinStart, MAX(end) AS MaxEnd FROM stat INNER JOIN display ON stat.DisplayID = display.DisplayID INNER JOIN layout ON layout.LayoutID = stat.LayoutID LEFT OUTER JOIN `widget` ON `widget`.widgetId = stat.MediaID LEFT OUTER JOIN `widgetoption` ON `widgetoption`.widgetId = `widget`.widgetId AND `widgetoption`.type = 'attrib' AND `widgetoption`.option = 'name' WHERE stat.type <> 'displaydown' AND stat.end > :fromDt AND stat.start <= :toDt AND stat.displayID IN (2,1) GROUP BY stat.type, display.Display, layout.Layout, CONCAT(widget.type, '-', widget.widgetId) ORDER BY stat.type, display.Display, layout.Layout, Name . Params = array ( 'fromDt' => Jenssegers\Date\Date::__set_state(array( 'date' => '2015-11-04 20:28:32', 'timezone_type' => 3, 'timezone' => 'America/Los_Angeles', )), 'toDt' => Jenssegers\Date\Date::__set_state(array( 'date' => '2015-11-05 20:28:32', 'timezone_type' => 3, 'timezone' => 'America/Los_Angeles', )), ).
11	3f7f247	2015-11-05 20:28	API	GET	DEBUG		/stats	SQL = SELECT display.displayId, display.display, display.defaultLayoutId, layout.layout AS defaultLayout, display.license, display.licensed, display.licensed AS currentlyLicenced, display.loggedIn, display.lastAccessed, display.isAuditing, display.inc_schedule AS incSchedule, display.email_alert AS emailAlert, display.alert_timeout AS alertTimeout, display.clientAddress, display.mediaInventoryStatus, display.macAddress, display.macAddress AS currentMacAddress, display.lastChanged, display.numberOfMacAddressChanges, display.lastWakeOnLanCommandSent, display.wakeOnLan AS wakeOnLanEnabled, display.wakeOnLanTime, display.broadCastAddress, display.secureOn, display.cidr, X(display.GeoLocation) AS latitude, Y(display.GeoLocation) AS longitude, display.version_instructions AS versionInstructions, display.client_type AS clientType, display.client_version AS clientVersion, display.client_code AS clientCode, display.displayProfileId, display.currentLayoutId, currentLayout.layout AS currentLayout, display.screenShotRequested, display.storageAvailableSpace, display.storageTotalSpace, displaygroup.displayGroupId, displaygroup.description FROM `display` INNER JOIN `lkdisplaydg` ON lkdisplaydg.displayid = display.displayId INNER JOIN `displaygroup` ON displaygroup.displaygroupid = lkdisplaydg.displaygroupid LEFT OUTER JOIN layout ON layout.layoutid = display.defaultlayoutid LEFT OUTER JOIN layout currentLayout ON currentLayout.layoutId = display.currentLayoutId WHERE 1 = 1 AND displaygroup.isDisplaySpecific = 1 ORDER BY display. Params = array ( ).
10	3f7f247	2015-11-05 20:28	API	GET	DEBUG		/stats	Converted Times received are: FromDt=2015-11-04 20:28:32. ToDt=2015-11-05 20:28:32
9	3f7f247	2015-11-05 20:28	API	GET	DEBUG		/stats	SQL = SELECT `group`.group, `group`.groupId, `group`.isUserSpecific, `group`.isEveryone, `group`.libraryQuota FROM `group` WHERE 1 = 1 AND `group`.groupId IN (SELECT groupId FROM `lkusergroup` WHERE userId = :userId) AND isUserSpecific = :isUserSpecific AND isEveryone = :isEveryone . Params = array ( 'userId' => 1, 'isUserSpecific' => 0, 'isEveryone' => 0, ).
8	3f7f247	2015-11-05 20:28	API	GET	DEBUG		/stats	Loading 1. All Objects = 0
7	3f7f247	2015-11-05 20:28	API	GET	DEBUG		/stats	SQL = SELECT `user`.userId, userName, userTypeId, loggedIn, email, `user`.homePageId, pages.title AS homePage, lastAccessed, newUserWizard, retired, CSPRNG, UserPassword AS password, group.groupId, group.group, IFNULL(group.libraryQuota, 0) AS libraryQuota FROM `user` INNER JOIN lkusergroup ON lkusergroup.userId = user.userId INNER JOIN `group` ON `group`.groupId = lkusergroup.groupId AND isUserSpecific = 1 LEFT OUTER JOIN `pages` ON pages.pageId = `user`.homePageId WHERE 1 = 1 AND user.userId = :userId ORDER BY userName. Params = array ( 'userId' => 1, ).

The dates that were entered - fromDt: 10/01/2015 00:00:00 and toDt: 11/05/2015 00:00:00

[dan]

The times it got from that are:

@cslaughter

FromDt=2015-11-04 20:28:32. ToDt=2015-11-05 20:28:32

Dates are always passed around in ISO format - Y-m-d H:i:s regardless of your date format setting (which is a setting that controls output).

Assuming it still doesn’t work when you give it correctly formatted dates - perhaps you can try changing this line:

$params = [
    'fromDt' => $fromDt,
    'toDt' => $toDt
];

to

$params = [
    'fromDt' => Date::getLocalDate($fromDt),
    'toDt' => Date::getLocalDate($toDt)
];

I really can’t understand why this does’t work as it is

[cslaughter]

Dan,

I have to apologize. I had forgotten that during some testing we disabled part of our code that would do the actual passing of the dates in ISO format. We re-enabled that part of our code, and the dates do get passed to the form_params correctly. But still the returned response was an empty array and the log shows that the API did not get the dates. So we then tried with the suggested changes to the fromDt and toDt using the Class. But all that did was tell me that the Date class was invalid. So we reverted the change and this is what we see in the log now.

12	66b0d50	2015-11-06 14:45	API	GET	DEBUG		/stats	SQL = SELECT stat.type, display.Display, layout.Layout, IFNULL(widgetoption.value, CONCAT(widget.type, '-', widget.widgetId)) AS Name, COUNT(StatID) AS NumberPlays, SUM(TIME_TO_SEC(TIMEDIFF(end, start))) AS Duration, MIN(start) AS MinStart, MAX(end) AS MaxEnd FROM stat INNER JOIN display ON stat.DisplayID = display.DisplayID INNER JOIN layout ON layout.LayoutID = stat.LayoutID LEFT OUTER JOIN `widget` ON `widget`.widgetId = stat.MediaID LEFT OUTER JOIN `widgetoption` ON `widgetoption`.widgetId = `widget`.widgetId AND `widgetoption`.type = 'attrib' AND `widgetoption`.option = 'name' WHERE stat.type <> 'displaydown' AND stat.end > :fromDt AND stat.start <= :toDt AND stat.displayID IN (2,1) GROUP BY stat.type, display.Display, layout.Layout, CONCAT(widget.type, '-', widget.widgetId) ORDER BY stat.type, display.Display, layout.Layout, Name . Params = array ( 'fromDt' => Jenssegers\Date\Date::__set_state(array( 'date' => '2015-11-05 14:45:06', 'timezone_type' => 3, 'timezone' => 'America/Los_Angeles', )), 'toDt' => Jenssegers\Date\Date::__set_state(array( 'date' => '2015-11-06 14:45:06', 'timezone_type' => 3, 'timezone' => 'America/Los_Angeles', )), ).
11	66b0d50	2015-11-06 14:45	API	GET	DEBUG		/stats	SQL = SELECT display.displayId, display.display, display.defaultLayoutId, layout.layout AS defaultLayout, display.license, display.licensed, display.licensed AS currentlyLicenced, display.loggedIn, display.lastAccessed, display.isAuditing, display.inc_schedule AS incSchedule, display.email_alert AS emailAlert, display.alert_timeout AS alertTimeout, display.clientAddress, display.mediaInventoryStatus, display.macAddress, display.macAddress AS currentMacAddress, display.lastChanged, display.numberOfMacAddressChanges, display.lastWakeOnLanCommandSent, display.wakeOnLan AS wakeOnLanEnabled, display.wakeOnLanTime, display.broadCastAddress, display.secureOn, display.cidr, X(display.GeoLocation) AS latitude, Y(display.GeoLocation) AS longitude, display.version_instructions AS versionInstructions, display.client_type AS clientType, display.client_version AS clientVersion, display.client_code AS clientCode, display.displayProfileId, display.currentLayoutId, currentLayout.layout AS currentLayout, display.screenShotRequested, display.storageAvailableSpace, display.storageTotalSpace, displaygroup.displayGroupId, displaygroup.description FROM `display` INNER JOIN `lkdisplaydg` ON lkdisplaydg.displayid = display.displayId INNER JOIN `displaygroup` ON displaygroup.displaygroupid = lkdisplaydg.displaygroupid LEFT OUTER JOIN layout ON layout.layoutid = display.defaultlayoutid LEFT OUTER JOIN layout currentLayout ON currentLayout.layoutId = display.currentLayoutId WHERE 1 = 1 AND displaygroup.isDisplaySpecific = 1 ORDER BY display. Params = array ( ).
10	66b0d50	2015-11-06 14:45	API	GET	DEBUG		/stats	Converted Times received are: FromDt=2015-11-05 14:45:06. ToDt=2015-11-06 14:45:06
9	66b0d50	2015-11-06 14:45	API	GET	DEBUG		/stats	SQL = SELECT `group`.group, `group`.groupId, `group`.isUserSpecific, `group`.isEveryone, `group`.libraryQuota FROM `group` WHERE 1 = 1 AND `group`.groupId IN (SELECT groupId FROM `lkusergroup` WHERE userId = :userId) AND isUserSpecific = :isUserSpecific AND isEveryone = :isEveryone . Params = array ( 'userId' => 1, 'isUserSpecific' => 0, 'isEveryone' => 0, ).
8	66b0d50	2015-11-06 14:45	API	GET	DEBUG		/stats	Loading 1. All Objects = 0
7	66b0d50	2015-11-06 14:45	API	GET	DEBUG		/stats	SQL = SELECT `user`.userId, userName, userTypeId, loggedIn, email, `user`.homePageId, pages.title AS homePage, lastAccessed, newUserWizard, retired, CSPRNG, UserPassword AS password, group.groupId, group.group, IFNULL(group.libraryQuota, 0) AS libraryQuota FROM `user` INNER JOIN lkusergroup ON lkusergroup.userId = user.userId INNER JOIN `group` ON `group`.groupId = lkusergroup.groupId AND isUserSpecific = 1 LEFT OUTER JOIN `pages` ON pages.pageId = `user`.homePageId WHERE 1 = 1 AND user.userId = :userId ORDER BY userName. Params = array ( 'userId' => 1, ).

The forms_params array output:

array (size=1)
‘form_params’ =>
array (size=4)
‘fromDt’ => string ‘2015-10-01 00:00:00’ (length=19)
‘toDt’ => string ‘2015-11-30 23:59:59’ (length=19)
‘displayId’ => int 1
‘mediaId’ => int 0

The returned repoonse:

string '200 OK' (length=6)

And the actual return from the API:

array (size=0)
  empty

So I think I will focus on trying to figure out why the API does not seem to be seeing the dates being passed.

Also just to note, on the code to get the date stats fixed. Can’t remember if the date selection in the CMS within the Alpha and Dev versions of the CMS were working, but they surely do now. However we don’t see any media in the date selections of the CMS:

UPDATE: I no more than got done typing this, and things changed. We decided to reboot everything. The server and the two test units.10 minutes later, we have stats from the api. Although not complete, and still the dates are not being passed. And also the results are not what we are expecting, As I outlined above it doesn’t look like the stats return media. However, I am thinking where “text-1”, and “text-2” are showing, this is actually the two media items we have scheduled on the layout, it is just that the names are not right. We will make a few changes to the schedule and compare the results.

Update 2: Media change on the layout did not change the stats at all. So Text-1 and Text-2 must be unrelated. Or maybe related to this:

Layouts Are Not Updated On Displays - 1.8 Dev

At some point it looks like the ability to update layouts on displays stopped working. When checking the Manage page of a display we see this: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '// `xml` = NULL, `status` = '1', Part of the Log: 665 7517529 2015-11-07 09:39 WEB GET DEBUG /layout/status/:id Storage rollback. 664 7517529 2015-11-07 09:39 WE…

Update 3: With the layout problem fixed and updating the client to R57, we now have the correct names on media in the Statistics page. (We also found out that currently you cannot register a R56 device on a 1.8 Dev) All form_params are still non-functional for us; tried the latest Dev code too. (Had to bypass react/qzm, It would not install.)

Sorry for the long post.

[cslaughter]

We thought the names were correct on the statics, turns out they are not, both in the CMS and with API. We see names like this: image-13, image-14, image-15.

On a different note, we are still unable to get dates or anything else passed correctly for stats. We applied the same code logic and formatting that worked on adding media to a region and creating the widgets, but no luck.

[dan]

@cslaughter

We also found out that currently you cannot register a R56 device on a 1.8 Dev

Yes, this is unfortunately true - there was an unfixable issue with the way the Android player selected which version of the XMDS schema it used to communicate. We quietly fixed it in R57 hoping more people would have this version by the time it all got released.

As for the media name not being shown, i’ve submitted a bug for it:

Statistics do not show media name

I’ve been round and round on the dates - no matter what I do I always get the dates I pass in to match the dates logged by the line: Converted Times received are: FromDt=2015-11-01 14:40:00. ToDt=2015-11-13 08:13:08.

May I suggest you add a log statement to see what’s in $_GET?

[cslaughter]

Sorry for the late reply on this.

var_dump for $_GET after calling the API:

array (size=6)
  'client_action' => string 'statistics' (length=10)
  'stats_start_date' => string '12/01/2015' (length=10)
  'stats_end_date' => string '12/31/2015' (length=10)
  'stats_displayId' => string '' (length=0)
  'stats_mediaId' => string '' (length=0)
  'submit' => string 'Submit' (length=6)

So apparently the API does get the dates, it just does not return what is expected. I think this somehow is related to being on Windows vs linux.

[dan]

Perhaps we can revisit this after checking the other two topics on Android/Stats/etc - I think those topics might resolve this anyway

[cslaughter]

OK, sounds like that way to go. I will look at this again once we apply the other code fixes and have R58 installed.