-
Notifications
You must be signed in to change notification settings - Fork 3
/
stats_userhours_year.php
131 lines (125 loc) · 6.27 KB
/
stats_userhours_year.php
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
<?php
require_once('Connections/YBDB.php');
require_once('Connections/database_functions.php');
$page_individual_history_log = INDIVIDUAL_HISTORY_LOG;
switch ($_GET['error']) {
case 'no_shop':
$error_message = 'ERROR: A Shop at this location for today does not exist: Start New Shop';
break;
case 'new_error_message': //this is a sample error message. insert error case here
$error_message = '';
break;
default:
$error_message = 'Total Hours';
break;
}
mysql_select_db($database_YBDB, $YBDB);
$query_Recordset1 = "SELECT * FROM (SELECT contacts.contact_id, CONCAT(last_name, ', ', first_name, ' ',middle_initial) AS full_name,
COUNT(shop_hours.contact_id) as sort_visits,
ROUND(SUM(HOUR(SUBTIME( TIME(time_out), TIME(time_in))) + MINUTE(SUBTIME( TIME(time_out), TIME(time_in)))/60)) AS sort_hours
FROM shop_hours
LEFT JOIN contacts ON shop_hours.contact_id = contacts.contact_id
LEFT JOIN shop_user_roles ON shop_hours.shop_user_role = shop_user_roles.shop_user_role_id
WHERE shop_user_roles.volunteer = 1 AND time_in > DATE_SUB(CURDATE(),INTERVAL 12 MONTH)
GROUP BY contact_id
ORDER BY sort_hours DESC) AS sort_hours
LEFT JOIN (SELECT contacts.contact_id AS vh_contact_id,
COUNT(shop_hours.contact_id) as th_visits,
ROUND(SUM(HOUR(SUBTIME( TIME(time_out), TIME(time_in))) + MINUTE(SUBTIME( TIME(time_out), TIME(time_in)))/60)) AS th_hours
FROM shop_hours
LEFT JOIN contacts ON shop_hours.contact_id = contacts.contact_id
LEFT JOIN shop_user_roles ON shop_hours.shop_user_role = shop_user_roles.shop_user_role_id
GROUP BY contacts.contact_id
ORDER BY last_name, first_name) AS total_hours ON sort_hours.contact_id = total_hours.vh_contact_id
LEFT JOIN (SELECT contacts.contact_id AS vh_contact_id,
COUNT(shop_hours.contact_id) as vh_visits,
ROUND(SUM(HOUR(SUBTIME( TIME(time_out), TIME(time_in))) + MINUTE(SUBTIME( TIME(time_out), TIME(time_in)))/60)) AS vh_hours
FROM shop_hours
LEFT JOIN contacts ON shop_hours.contact_id = contacts.contact_id
LEFT JOIN shop_user_roles ON shop_hours.shop_user_role = shop_user_roles.shop_user_role_id
WHERE shop_user_roles.volunteer = 1
GROUP BY contacts.contact_id
ORDER BY last_name, first_name) AS volunteer_hours ON sort_hours.contact_id = volunteer_hours.vh_contact_id
LEFT JOIN (SELECT contacts.contact_id AS th3_contact_id,
COUNT(shop_hours.contact_id) as th3_visits,
ROUND(SUM(HOUR(SUBTIME( TIME(time_out), TIME(time_in))) + MINUTE(SUBTIME( TIME(time_out), TIME(time_in)))/60)) AS th3_hours
FROM shop_hours
LEFT JOIN contacts ON shop_hours.contact_id = contacts.contact_id
LEFT JOIN shop_user_roles ON shop_hours.shop_user_role = shop_user_roles.shop_user_role_id
WHERE time_in > DATE_SUB(CURDATE(),INTERVAL 12 MONTH)
GROUP BY contacts.contact_id
ORDER BY last_name, first_name) AS total_hours3 ON sort_hours.contact_id = total_hours3.th3_contact_id
LEFT JOIN (SELECT contacts.contact_id AS vh3_contact_id,
COUNT(shop_hours.contact_id) as vh3_visits,
ROUND(SUM(HOUR(SUBTIME( TIME(time_out), TIME(time_in))) + MINUTE(SUBTIME( TIME(time_out), TIME(time_in)))/60)) AS vh3_hours
FROM shop_hours
LEFT JOIN contacts ON shop_hours.contact_id = contacts.contact_id
LEFT JOIN shop_user_roles ON shop_hours.shop_user_role = shop_user_roles.shop_user_role_id
WHERE shop_user_roles.volunteer = 1 AND time_in > DATE_SUB(CURDATE(),INTERVAL 12 MONTH)
GROUP BY contacts.contact_id
ORDER BY first_name) AS volunteer_hours3 ON sort_hours.contact_id = volunteer_hours3.vh3_contact_id";
$Recordset1 = mysql_query($query_Recordset1, $YBDB) or die(mysql_error());
//$row_Recordset1 = mysql_fetch_assoc($Recordset1); //Wait to fetch until do loop
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
?>
<?php include("include_header.html"); ?>
<table>
<tr valign="top">
<td><span class="yb_heading3red">Hours by User</span></td>
</tr>
<tr>
<td>
<table border="1" cellpadding="1" cellspacing="0">
<tr bgcolor="#99CC33" class="yb_standardCENTERbold">
<td height="25">Shop User </td>
<td height="25" colspan="3"> Last 12 Months </td>
<td height="25" colspan="3">Lifetime</td>
</tr>
<tr valign="top" bgcolor="#99CC33" class="yb_standardCENTER">
<td width="200" height="35"></td>
<td width="100">Volunteer<br />
Hours</td>
<td width="100">Total<br />
Hours</td>
<td width="100">Visits</td>
<td width="100">Volunteer<br />
Hours</td>
<td width="100">Total<br />
Hours</td>
<td width="100">Visits</td>
</tr>
<form method="post" name="FormUpdate_<?php echo $row_Recordset1['shop_id']; ?>" action="<?php echo $editFormAction; ?>">
<?php while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)) { //do {
if(1 == 2) {?>
<tr valign="bottom" bgcolor="#CCCC33">
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<input type="hidden" name="MM_insert" value="FormEdit">
<input type="hidden" name="shop_id" value="<?php echo $row_Recordset1['shop_id']; ?>">
</form>
<?php } else { // end if EDIT RECORD ?>
<tr>
<td><a href="<?php echo "{$page_individual_history_log}?contact_id=" . $row_Recordset1['contact_id']; ?>"><?php echo $row_Recordset1['full_name']; ?></a></td>
<td class="yb_standardRIGHTred"> <?php echo number_format($row_Recordset1['vh3_hours'],0); ?></td>
<td class="yb_standardRIGHT"> <?php echo number_format($row_Recordset1['th3_hours'],0); ?></td>
<td class="yb_standardRIGHT"> <?php echo number_format($row_Recordset1['th3_visits'],0); ?></td>
<td class="yb_standardRIGHTred"> <?php echo number_format($row_Recordset1['vh_hours'],0); ?></td>
<td class="yb_standardRIGHT"> <?php echo number_format($row_Recordset1['th_hours'],0); ?></td>
<td class="yb_standardRIGHT"> <?php echo number_format($row_Recordset1['th_visits'],0); ?></td>
</tr>
<?php
} // end if EDIT RECORD
} // end WHILE count of recordset ?>
</table> </td>
</tr>
</table>
<?php include("include_footer.html"); ?>
<?php
mysql_free_result($Recordset1);
?>