PageRenderTime 64ms CodeModel.GetById 34ms RepoModel.GetById 0ms app.codeStats 0ms

/admin/reports/count.php

https://github.com/anodyne/sms
PHP | 368 lines | 254 code | 62 blank | 52 comment | 21 complexity | 1a40daad88b36c2da7d47f192b57aa77 MD5 | raw file
Possible License(s): LGPL-2.1
  1. <?php
  2. /**
  3. This is a necessary system file. Do not modify this page unless you are highly
  4. knowledgeable as to the structure of the system. Modification of this file may
  5. cause SMS to no longer function.
  6. Author: David VanScott [ davidv@anodyne-productions.com ]
  7. File: admin/reports/count.php
  8. Purpose: Page that shows recent post counts for the sim
  9. System Version: 2.6.10
  10. Last Modified: 2009-11-12 0021 EST
  11. **/
  12. /* access check */
  13. if( in_array( "r_count", $sessionAccess ) ) {
  14. /* set the page class */
  15. $pageClass = "admin";
  16. $subMenuClass = "reports";
  17. /**
  18. Do not edit below this line unless you are highly
  19. knowledgeable with PHP date functions. Modification
  20. of this may inadvertantly break the count function.
  21. **/
  22. /* get today's date */
  23. $today = getdate();
  24. /* create variables to be used in the query */
  25. $monthStartRaw = $today['year'] . "-" . $today['mon'] . "-01 00:00:00";
  26. $monthEndRaw = $today['year'] . "-" . ( $today['mon']+1 ) . "-01 00:00:00";
  27. /* do some logic to make sure it doesn't break at the end of each month */
  28. if( $monthEndRaw == $today['year'] . "-13-01 00:00:00" ) {
  29. $monthEndRaw = ( $today['year'] +1 ) . "-01-01 00:00:00";
  30. }
  31. /* convert month start and end to a timestamp */
  32. $monthStart = strtotime( $monthStartRaw );
  33. $monthEnd = strtotime( $monthEndRaw );
  34. /* take the number of days and multiply it times */
  35. /* the number of seconds in those days */
  36. $daysInSeconds = $postCountDefault * ( 86400 );
  37. /* take the number of seconds and subtract it from */
  38. /* the current date's UNIX timestamp */
  39. $daysPrior = $today[0] - ( $daysInSeconds );
  40. /* take the UNIX timestamp and convert it into something */
  41. /* that SQL can use in the query */
  42. $dateShift = dateFormat( "sql", $daysPrior );
  43. /* format today's date for SQL */
  44. $todayDate = dateFormat( "sql", $today[0] );
  45. ?>
  46. <script type="text/javascript">
  47. $(document).ready(function(){
  48. $('table.zebra tbody > tr:nth-child(odd)').addClass('alt');
  49. $('#loading').hide();
  50. $('#loaded').show();
  51. });
  52. </script>
  53. <div class="body">
  54. <span class="fontTitle">Post Count Report</span><br /><br />
  55. <div id="loading" style="text-align:center;">
  56. <img src="<?=$webLocation;?>images/loader.gif" alt="Loading..." class="image" /><br />
  57. <span class="fontMedium bold">Loading...</span>
  58. </div>
  59. <div id="loaded" style="display:none;">
  60. <?
  61. if( $jpCount == "n" ) {
  62. /* do a count query on the missionposts table */
  63. $posts = "SELECT count( postid ) FROM sms_posts WHERE postPosted > '$monthStart' ";
  64. $posts.= "AND postPosted < '$monthEnd' AND postStatus = 'activated'";
  65. $postsResult = mysql_query( $posts );
  66. $postcount = mysql_fetch_array( $postsResult );
  67. /* do a count query on the personallogs table */
  68. $logs = "SELECT count( logid ) FROM sms_personallogs WHERE logPosted > '$monthStart' ";
  69. $logs.= "AND logPosted < '$monthEnd' AND logStatus = 'activated'";
  70. $logsResult = mysql_query( $logs );
  71. $logcount = mysql_fetch_array( $logsResult );
  72. echo "<b>Total Mission Posts For the Month of " . $today['month'] . "</b>: " . $postcount['0'] . "<br />";
  73. echo "<b>Total Personal Logs For the Month of " . $today['month'] . "</b>: " . $logcount['0'] . "<br />";
  74. echo "<b>Total Posts For the Month of " . $today['month'] . "</b>: " . ( $postcount['0'] + $logcount['0'] );
  75. } elseif( $jpCount == "y" ) {
  76. /* do a count query on the missionposts table */
  77. $posts = "SELECT postAuthor FROM sms_posts WHERE postPosted > '$monthStart' ";
  78. $posts.= "AND postPosted < '$monthEnd' AND postStatus = 'activated'";
  79. $postsResult = mysql_query( $posts );
  80. $countRows = mysql_num_rows( $postsResult );
  81. $authorArray = array();
  82. while( $counting = mysql_fetch_array( $postsResult ) ) {
  83. extract( $counting, EXTR_OVERWRITE );
  84. /* explode the array from the query */
  85. $authorString = explode( ",", $postAuthor );
  86. /* count the number of elements */
  87. $arrayCount = count( $authorString );
  88. /* loop through the elements in the array and add them */
  89. /* to the end of the master array */
  90. for( $i=0; $i<$arrayCount; $i++ ) {
  91. $authorArray[] = $authorString[$i];
  92. }
  93. }
  94. /* count the elements in the array */
  95. $postcount = count( $authorArray );
  96. /* do a count query on the personallogs table */
  97. $logs = "SELECT count( logid ) FROM sms_personallogs WHERE logPosted > '$monthStart' ";
  98. $logs.= "AND logPosted < '$monthEnd' AND logStatus = 'activated'";
  99. $logsResult = mysql_query( $logs );
  100. $logcount = mysql_fetch_array( $logsResult );
  101. echo "<b>Total Mission Posts For the Month of " . $today['month'] . "</b>: " . $postcount . "<br />";
  102. echo "<b>Total Personal Logs For the Month of " . $today['month'] . "</b>: " . $logcount['0'] . "<br />";
  103. echo "<b>Total Posts For the Month of " . $today['month'] . "</b>: " . ( $postcount + $logcount['0'] );
  104. }
  105. /* do a count query on the missionposts table for saved posts */
  106. $savedPosts = "SELECT count( postid ) FROM sms_posts WHERE postPosted > '$monthStart' ";
  107. $savedPosts.= "AND postPosted < '$monthEnd' AND postStatus = 'saved'";
  108. $savedPostsResult = mysql_query( $savedPosts );
  109. $savedPostCount = mysql_fetch_array( $savedPostsResult );
  110. /* do a count query on the personallogs table for saved logs */
  111. $savedLogs = "SELECT count( logid ) FROM sms_personallogs WHERE logPosted > '$monthStart' ";
  112. $savedLogs.= "AND logPosted < '$monthEnd' AND logStatus = 'saved'";
  113. $savedLogsResult = mysql_query( $savedLogs );
  114. $savedLogCount = mysql_fetch_array( $savedLogsResult );
  115. echo "<br /><br />";
  116. echo "<b>Total Saved Posts &amp; Logs</b>: " . ( $savedPostCount['0'] + $savedLogCount['0'] );
  117. ?>
  118. <br /><br /><br />
  119. <span class="fontLarge"><b>Individual Crew Counts</b></span><br /><br />
  120. <table cellspacing="0" cellpadding="0">
  121. <tr class="fontMedium">
  122. <td><b>Crew Member</b></td>
  123. <td colspan="2" align="center"><b>Mission Posts</b></td>
  124. <td colspan="2" align="center"><b>Personal Logs</b></td>
  125. <td colspan="2" align="center"><b>Totals</b></td>
  126. </tr>
  127. <tr class="fontSmall">
  128. <td>&nbsp;</td>
  129. <td align="center"><i><?=$postCountDefault;?> Days</i></td>
  130. <td align="center"><i><?=$today['month'];?></i></td>
  131. <td align="center"><i><?=$postCountDefault;?> Days</i></td>
  132. <td align="center"><i><?=$today['month'];?></i></td>
  133. <td align="center"><i><?=$postCountDefault;?> Days</i></td>
  134. <td align="center"><i><?=$today['month'];?></i></td>
  135. </tr>
  136. <?php
  137. /* query the users table for firstname, lastname, and id */
  138. $users = "SELECT crew.crewid, crew.firstName, crew.lastName, crew.loa, crew.strikes, ";
  139. $users.= "rank.rankName FROM sms_crew AS crew, sms_ranks AS rank WHERE ";
  140. $users.= "crew.crewType = 'active' AND crew.rankid = rank.rankid ORDER BY ";
  141. $users.= "crew.rankid, crew.positionid ASC";
  142. $usersResult = mysql_query( $users );
  143. $rowCount = "0";
  144. $color1 = "rowColor1";
  145. $color2 = "rowColor2";
  146. /* loop through them and create an array to feed the following two queries */
  147. while( $usercount = mysql_fetch_array( $usersResult ) ) {
  148. extract( $usercount, EXTR_OVERWRITE );
  149. $rowColor = ($rowCount % 2) ? $color1 : $color2;
  150. /* do a query on the missionposts table again, this time checking the author */
  151. $posts2 = "SELECT count( postid ) FROM sms_posts WHERE ( postAuthor LIKE '" . $crewid . ",%' OR ";
  152. $posts2.= "postAuthor LIKE '%," . $crewid . "' OR postAuthor LIKE '%," . $crewid . ",%' OR postAuthor = '" . $crewid . "' ) ";
  153. $posts2.= "AND postPosted > '$monthStart' AND postPosted < '$monthEnd' AND postStatus = 'activated'";
  154. $postsSingleResult = mysql_query( $posts2 );
  155. $postcountSingle = mysql_fetch_array( $postsSingleResult );
  156. /* do a query on the personallogs table again, this time checking the author */
  157. $logs2 = "SELECT count( logid ) FROM sms_personallogs WHERE logAuthor = '" . $crewid . "' AND logPosted > '$monthStart' ";
  158. $logs2.= "AND logPosted < '$monthEnd' AND logStatus = 'activated'";
  159. $logsSingleResult = mysql_query( $logs2 );
  160. $logcountSingle = mysql_fetch_array( $logsSingleResult );
  161. /* do a query on the missionposts table to find the number of posts in the defined time */
  162. $postsTimeDefined = "SELECT count( postid ) FROM sms_posts WHERE ( postAuthor LIKE '" . $crewid . ",%' OR ";
  163. $postsTimeDefined.= "postAuthor LIKE '%," . $crewid . "' OR postAuthor LIKE '%," . $crewid . ",%' OR postAuthor = '" . $crewid . "' ) ";
  164. $postsTimeDefined.= "AND postPosted > '$daysPrior' AND postPosted < '$todayDate' AND postStatus = 'activated'";
  165. $postsTimeDefinedResult = mysql_query( $postsTimeDefined );
  166. $postcountWeeks = mysql_fetch_array( $postsTimeDefinedResult );
  167. /* do a query on the missionposts table to find the number of logs in the defined time */
  168. $logsTimeDefined = "SELECT count( logid ) FROM sms_personallogs WHERE logAuthor = '" . $crewid . "' ";
  169. $logsTimeDefined.= "AND logPosted > '$daysPrior' AND logPosted < '$todayDate' AND logStatus = 'activated'";
  170. $logsTimeDefinedResult = mysql_query( $logsTimeDefined );
  171. $logcountWeeks = mysql_fetch_array( $logsTimeDefinedResult );
  172. echo "<tr class='" . $rowColor . "'>";
  173. $totalCount = $postcountWeeks[0] + $logcountWeeks[0];
  174. /* if the user is on LOA, display their name and post counts in RED */
  175. if( $usercount['loa'] == "1" ) {
  176. $prefix = "<b class='red'>[LOA]</b> ";
  177. $color = "red";
  178. } if( $usercount['loa'] == "2" ) {
  179. $prefix = "<b class='orange'>[ELOA]</b> ";
  180. $color = "orange";
  181. } if( $usercount['loa'] == "0" ) {
  182. if( $totalCount == 0 ) {
  183. $color = "yellow";
  184. } elseif( $totalCount >= 1 ) {
  185. $color = "";
  186. }
  187. $prefix = "";
  188. }
  189. ?>
  190. <td>
  191. <? printText( $prefix . $rankName . " " . $firstName . " " . $lastName ); ?>
  192. </td>
  193. <td align="center" class="<?=$color;?>"><?=$postcountWeeks[0];?></td>
  194. <td align="center" class="countTableBorder"><?=$postcountSingle['0'];?></td>
  195. <td align="center" class="<?=$color;?>"><?=$logcountWeeks['0'];?></td>
  196. <td align="center" class="countTableBorder"><?=$logcountSingle['0'];?></td>
  197. <td align="center" class="<?=$color;?>"><b><?=$postcountWeeks['0'] + $logcountWeeks['0'];?></b></td>
  198. <td align="center"><b><?=$postcountSingle['0'] + $logcountSingle['0'];?></b></td>
  199. </tr>
  200. <?php $rowCount++; } ?>
  201. </table>
  202. <br /><br />
  203. <span class="fontLarge"><b>Total Posts</b></span><br />
  204. <span class="fontNormal"><i>Includes Previous Players</i></span>
  205. <br /><br />
  206. <?php
  207. /* query the users table for firstname, lastname, and id */
  208. $usersTotal = "SELECT crew.crewid, crew.firstName, crew.lastName, crew.crewType, ";
  209. $usersTotal.= "crew.loa, rank.rankName, crew.email FROM sms_crew AS crew, sms_ranks AS rank ";
  210. $usersTotal.= "WHERE crew.rankid = rank.rankid AND ( crew.crewType = 'active' OR crew.crewType = 'inactive' )";
  211. $usersTotal.= "ORDER BY crew.rankid, crew.positionid ASC";
  212. $usersTotalResult = mysql_query( $usersTotal );
  213. /* loop through them and create an array to feed the following two queries */
  214. while ( $usercountTotal = mysql_fetch_array( $usersTotalResult ) ) {
  215. extract( $usercountTotal, EXTR_OVERWRITE );
  216. /* do a query on the missionposts table again, this time checking the author */
  217. $posts3 = "SELECT count( postid ) FROM sms_posts WHERE ( postAuthor LIKE '" . $crewid . ",%' ";
  218. $posts3.= "OR postAuthor LIKE '%," . $crewid . "' OR postAuthor LIKE '%," . $crewid . ",%' ";
  219. $posts3.= "OR postAuthor = '" . $crewid . "' ) AND postStatus = 'activated'";
  220. $posts3Result = mysql_query( $posts3 );
  221. $postcountTotal = mysql_fetch_array( $posts3Result );
  222. /* do a query on the missionposts table again, this time checking the author */
  223. $logs3 = "SELECT count( logid ) FROM sms_personallogs WHERE logAuthor = '" . $crewid . "' ";
  224. $logs3.= "AND logStatus = 'activated'";
  225. $logs3Result = mysql_query( $logs3 );
  226. $logcountTotal = mysql_fetch_array( $logs3Result );
  227. $players[$email][] = array(
  228. 'name' => $firstName .' '. $lastName,
  229. 'status' => $crewType,
  230. 'posts' => $postcountTotal[0],
  231. 'logs' => $logcountTotal[0]
  232. );
  233. }
  234. foreach ($players as $key => $value)
  235. {
  236. if (count($value) < 2)
  237. {
  238. $array[$key]['name'] = $players[$key][0]['name'];
  239. $array[$key] = array(
  240. 'name' => $players[$key][0]['name'],
  241. 'posts' => $players[$key][0]['posts'],
  242. 'logs' => $players[$key][0]['logs'],
  243. 'status' => $players[$key][0]['status'],
  244. 'characters' => FALSE
  245. );
  246. }
  247. else
  248. {
  249. $array[$key] = array(
  250. 'name' => $players[$key][0]['name'],
  251. 'posts' => 0,
  252. 'logs' => 0,
  253. 'status' => $players[$key][0]['status'],
  254. 'characters' => array(),
  255. );
  256. foreach ($value as $k => $v)
  257. {
  258. $array[$key]['posts'] += $v['posts'];
  259. $array[$key]['logs'] += $v['logs'];
  260. $array[$key]['characters'][] = $v['name'];
  261. if ($v['status'] == 'active')
  262. {
  263. $array[$key]['name'] = $v['name'];
  264. $array[$key]['status'] = $v['status'];
  265. }
  266. }
  267. }
  268. }
  269. ?>
  270. <table cellpadding="0" cellspacing="0" class="zebra">
  271. <thead>
  272. <tr class="fontMedium">
  273. <td><b>Crew Member</b></td>
  274. <td align="center"><b>Mission Posts</b></td>
  275. <td align="center"><b>Personal Logs</b></td>
  276. <td align="center"><b>Totals</b></td>
  277. </tr>
  278. <tr height="5">
  279. <td colspan="4"></td>
  280. </tr>
  281. </thead>
  282. <tbody>
  283. <?php foreach ($array as $a): ?>
  284. <tr height="40">
  285. <td width="50%">
  286. <strong><?php echo $a['name'];?></strong>
  287. <?php if (is_array($a['characters'])): ?>
  288. <br />
  289. <span class="fontSmall" style="color:#666">
  290. <strong>Includes the following characters:</strong>
  291. <?php echo implode(', ', $a['characters']);?>
  292. </span>
  293. <?php endif;?>
  294. </td>
  295. <td align="center" class="countTableBorder"><?php echo $a['posts'];?></td>
  296. <td align="center" class="countTableBorder"><?php echo $a['logs'];?></td>
  297. <td align="center"><?php echo $a['posts'] + $a['logs'];?></td>
  298. </tr>
  299. <?php endforeach;?>
  300. </tbody>
  301. </table>
  302. </div>
  303. </div>
  304. <? } else { errorMessage( "post count" ); } ?>