00001 <?php
00002 if(!defined('__PRAGYAN_CMS'))
00003 {
00004 header($_SERVER['SERVER_PROTOCOL'].' 403 Forbidden');
00005 echo "<h1>403 Forbidden<h1><h4>You are not authorized to access the page.</h4>";
00006 echo '<hr/>'.$_SERVER['SERVER_SIGNATURE'];
00007 exit(1);
00008 }
00034 function getLastUpdateDate($moduleComponentId, $userId) {
00035 $query = 'SELECT `form_lastupdated` FROM `form_regdata` WHERE `page_modulecomponentid` = ' . $moduleComponentId . ' AND `user_id` = ' . $userId;
00036 $result = mysql_query($query);
00037 $row = mysql_fetch_row($result);
00038 return $row[0];
00039 }
00040
00041 function getRegistrationDate($moduleComponentId, $userId) {
00042 $query = 'SELECT `form_firstupdated` FROM `form_regdata` WHERE `page_modulecomponentid` = ' . $moduleComponentId . ' AND `user_id` = ' . $userId;
00043 $result = mysql_query($query);
00044 $row = mysql_fetch_row($result);
00045 return $row[0];
00046 }
00047
00048 function generateFormDataRow($moduleCompId, $userId, $columnList, $showProfileData = false) {
00049 $display = array();
00050 $elementRow = array();
00051
00052 $elementDataQuery = 'SELECT `form_elementdata`, `form_elementdesc`.`form_elementid`, `form_elementdesc`.`form_elementtype` FROM `form_elementdesc`, `form_elementdata` WHERE ' .
00053 "`form_elementdata`.`page_modulecomponentid` = $moduleCompId AND `user_id` = $userId AND " .
00054 '`form_elementdata`.`page_modulecomponentid` = `form_elementdesc`.`page_modulecomponentid` AND ' .
00055 '`form_elementdata`.`form_elementid` = `form_elementdesc`.`form_elementid` ' .
00056 'ORDER BY `form_elementrank` ASC';
00057 $elementDataResult = mysql_query($elementDataQuery) or die($elementDataQuery . ' ' . mysql_error());
00058 while($elementDataRow = mysql_fetch_row($elementDataResult)) {
00059 $elementRow['elementid_' . $elementDataRow[1]] = $elementDataRow[0];
00060 if($elementDataRow[2] == 'file') {
00061 $elementRow['elementid_' . $elementDataRow[1]] = '<a href="./'.$elementDataRow[0].'">' . $elementDataRow[0] . '</a>';
00062 }
00063 }
00064
00065 if($showProfileData) {
00066 if($userId > 0) {
00067 $elementDataQuery = 'SELECT `form_elementdata`, `form_elementdesc`.`form_elementid`, `form_elementdesc`.`form_elementname`, `form_elementdesc`.`form_elementtype` FROM `form_elementdesc`, `form_elementdata` WHERE ' .
00068 "`form_elementdata`.`page_modulecomponentid` = 0 AND `user_id` = $userId AND " .
00069 "`form_elementdata`.`page_modulecomponentid` = `form_elementdesc`.`page_modulecomponentid` AND " .
00070 "`form_elementdata`.`form_elementid` = `form_elementdesc`.`form_elementid` ORDER BY `form_elementrank`";
00071 $elementDataResult = mysql_query($elementDataQuery) or die($elementDataQuery . '<br />' . mysql_error());
00072 while($elementDataRow = mysql_fetch_assoc($elementDataResult)) {
00073 $elementRow['form0_' . $elementDataRow['form_elementname']] = $elementDataRow['form_elementdata'];
00074 if($elementDataRow['form_elementtype'] == 'file') {
00075 $elementRow['form0_' . $elementDataRow['form_elementname']] = '<a href="./'.$elementDataRow['form_elementdata'].'">' . $elementDataRow['form_elementdata'] . '</a>';
00076 }
00077 }
00078
00079
00080 }
00081 else {
00082 $elementDataQuery = 'SELECT `form_elementname` FROM `form_elementdesc` WHERE `page_modulecomponentid` = 0';
00083 $elementDataResult = mysql_query($elementDataQuery);
00084 while($elementDataRow = mysql_fetch_row($elementDataResult)) {
00085 $elementDataRow['form0_' . $elementDataRow['form_elementname']] = ' ';
00086 }
00087 }
00088 }
00089
00090
00091 if(in_array('useremail', $columnList)) {
00092 $elementRow['useremail'] = getUserEmail($userId);
00093 }
00094 if(in_array('username', $columnList)) {
00095 $elementRow['username'] = getUserName($userId);
00096 }
00097 if(in_array('userfullname', $columnList)) {
00098 $elementRow['userfullname'] = getUserFullName($userId);
00099 }
00100 if(in_array('lastupdated', $columnList)) {
00101 $elementRow['lastupdated'] = getLastUpdateDate($moduleCompId, $userId);
00102 }
00103 if(in_array('registrationdate', $columnList)) {
00104 $elementRow['registrationdate'] = getRegistrationDate($moduleCompId, $userId);
00105 }
00106
00107 $columnCount = count($columnList);
00108 for($i = 0; $i < count($columnList); $i++) {
00109 if(isset($elementRow[$columnList[$i]])) {
00110 $display[] = $elementRow[$columnList[$i]];
00111 }
00112 else {
00113 $display[] = ' ';
00114 }
00115 }
00116
00117 return $display;
00118 }
00119
00120
00121
00122
00123 function getColumnList($moduleCompId, $showUserEmail, $showUserFullName, $showRegistrationDate, $showLastUpdateDate, $showUserProfileData=false) {
00124 $columns = array();
00125
00126 if($showUserEmail)
00127 $columns['useremail'] = 'User Email';
00128 if($showUserFullName)
00129 $columns['userfullname'] = 'User Full Name';
00130 if($showRegistrationDate)
00131 $columns['registrationdate'] = 'Registration Date';
00132 if($showLastUpdateDate)
00133 $columns['lastupdated'] = 'Last Updated';
00134 if($showUserProfileData) {
00135 $profileQuery = 'SELECT `form_elementname` FROM `form_elementdesc` WHERE `page_modulecomponentid` = 0 ORDER BY `form_elementrank`';
00136 $profileResult = mysql_query($profileQuery);
00137 while($profileRow = mysql_fetch_row($profileResult)) {
00138 $columns['form0_' . $profileRow[0]] = $profileRow[0];
00139 }
00140 }
00141
00142 $columnQuery = 'SELECT `form_elementid`, `form_elementname` FROM `form_elementdesc` WHERE `page_modulecomponentid` = ' .
00143 $moduleCompId . ' ORDER BY `form_elementrank` ASC';
00144 $columnResult = mysql_query($columnQuery);
00145
00146 while($columnRow = mysql_fetch_assoc($columnResult)) {
00147 $columns['elementid_' . $columnRow['form_elementid']] = $columnRow['form_elementname'];
00148
00149 }
00150
00151 return $columns;
00152 }
00153
00154 function getDistinctRegistrants($moduleCompId, $rowSortField, $rowSortOrder) {
00155 if($rowSortOrder != 'asc' && $rowSortOrder != 'desc') $rowSortOrder = 'asc';
00156 $users = array();
00157 $userQuery = '';
00158
00159 if($rowSortField == 'useremail' || $rowSortField == 'userfullname' || $rowSortField == 'username') {
00160 $col = 'user_fullname';
00161 if($rowSortField == 'useremail') $col = 'user_email';
00162 elseif($rowSortField == 'username') $col = 'user_name';
00163 $userTable = MYSQL_DATABASE_PREFIX . 'users';
00164
00165 $userQuery = "SELECT `form_regdata`.`user_id` FROM `$userTable`, `form_regdata` WHERE " .
00166 "`page_modulecomponentid` = $moduleCompId AND `$userTable`.`user_id` = `form_regdata`.`user_id` " .
00167 "ORDER BY `$col` $rowSortOrder";
00168
00172 }
00173 elseif($rowSortField == 'registrationdate' || $rowSortField == 'lastupdated') {
00174 $col = 'form_lastupdated';
00175 if($rowSortField == 'registrationdate') $col = 'form_firstupdated';
00176
00177 $userQuery = "SELECT `user_id` FROM `form_regdata` WHERE " .
00178 "`page_modulecomponentid` = $moduleCompId ORDER BY `$col` $rowSortOrder";
00179 }
00180 elseif(substr($rowSortField, 0, 6) == 'form0_') {
00182 }
00183 else {
00184 $elementId = split('_', $rowSortField);
00185 $elementId = $elementId[1];
00195 $userQuery = "SELECT `reg`.`user_id` FROM `form_elementdesc` des LEFT JOIN (form_regdata reg LEFT JOIN form_elementdata dat ON reg.page_modulecomponentid = dat.page_modulecomponentid AND reg.user_id = dat.user_id AND reg.page_modulecomponentid = $moduleCompId) " .
00196 "ON des.page_modulecomponentid = dat.page_modulecomponentid AND des.form_elementid = dat.form_elementid " .
00197 "WHERE dat.`form_elementid` = $elementId ORDER BY dat.form_elementdata $rowSortOrder";
00198 }
00199
00200
00201 $userResult = mysql_query($userQuery) or die ($userQuery . ' ' . mysql_error());
00202 while($userRow = mysql_fetch_row($userResult)) {
00203 $users[] = $userRow[0];
00204 }
00205
00206 return $users;
00207 }
00208
00209
00218 function generateFormDataTable($moduleComponentId, $sortField, $sortOrder, $action = 'viewregistrants') {
00219 global $sourceFolder, $templateFolder, $urlRequestRoot, $cmsFolder, $moduleFolder;
00220
00221 $formDescQuery = 'SELECT `form_showuseremail`, `form_showuserfullname`, `form_showregistrationdate`, `form_showlastupdatedate`, `form_showuserprofiledata`,`form_heading` FROM `form_desc` ' .
00222 'WHERE `page_modulecomponentid` = ' . $moduleComponentId;
00223 $formDescResult = mysql_query($formDescQuery);
00224
00225 $showUserEmail = $showUserFullName = false;
00226 $showRegistrationDate = $showLastUpdateDate = true;
00227 $showUserProfileData = false;
00228 $formName='';
00229 if($formDescRow = mysql_fetch_row($formDescResult)) {
00230
00231 $showUserEmail = $formDescRow[0] == 1;
00232 $showUserFullName = $formDescRow[1] == 1;
00233 $showRegistrationDate = $formDescRow[2] == 1;
00234 $showLastUpdateDate = $formDescRow[3] == 1;
00235 $showUserProfileData = $formDescRow[4] == 1;
00236 $formName = $formDescRow[5];
00237 }
00238 $showEditButtons = $action == 'editregistrants';
00239
00240 $columnList = getColumnList($moduleComponentId, $showUserEmail, $showUserFullName, $showRegistrationDate, $showLastUpdateDate, $showUserProfileData);
00241 $columnNames = array();
00242
00243
00244
00245
00246
00247
00248
00249
00250
00251
00252
00253
00254
00255 $tableCaptions = "<thead><tr>\n<th>S. No.</th>\n";
00256 $toggleColumns = "<fieldset><legend>Select Columns</legend><table><tr>";
00257 $tableJqueryStuff = "";
00258 $c=0;
00259 $d=0;
00260 if($showEditButtons) {
00261 $tableCaptions .= '<th>Actions</th>';
00262 $tableJqueryStuff = "null,";
00263 $c++;
00264 }
00265 foreach($columnList as $columnName => $columnTitle) {
00266 $tableCaptions .= "<th>$columnTitle</th>\n";
00267 $columnNames[] = $columnName;
00268
00269 $c=$c+1;
00270 $checked="checked";
00271 if(!($columnName=="useremail" || $columnName=="registrationdate" || $columnName=="lastupdated"))
00272 {
00273 $tableJqueryStuff.="/* $columnTitle */ { \"bVisible\": false },";
00274 $checked="";
00275 }
00276 else $tableJqueryStuff.="null,";
00277
00278 if($d!=0 && $d%5==0)
00279 $toggleColumns.="</tr><tr>";
00280 $d=$d+1;
00281 $toggleColumns.="<td><input type='checkbox' onclick='fnShowHide($c);' $checked />$columnTitle <br/></td>";
00282 }
00283
00284 $tableCaptions .= "</tr></thead>\n";
00285 $toggleColumns .= "</tr></table></fieldset>";
00286
00287 $userIds = getDistinctRegistrants($moduleComponentId, $sortField, $sortOrder);
00288 $userCount = count($userIds);
00289
00290 global $ICONS;
00291 $editImage = $ICONS['Edit']['small'];
00292 $deleteImage = $ICONS['Delete']['small'];
00293
00294 $tableBody = '<tbody>';
00295 for($i = 0; $i < $userCount; $i++) {
00296 $tableBody .= '<tr><td>'.($i + 1).'</td>';
00297 if($showEditButtons) {
00298 if($userIds[$i] <= 0) {
00299 $tableBody .= '<td align="center"> </td>';
00300 }
00301 else {
00302 $tableBody .= '<td align="center"><a title="Edit" href="./+editregistrants&subaction=edit&useremail='.getUserEmail($userIds[$i]).'" />' . $editImage . '</a> ';
00303 }
00304 if($userIds[$i] <= 0) {
00305 $tableBody .= '<a style="cursor:pointer" title="Delete" onclick="return gotopage(\'./+editregistrants&subaction=delete&&useremail='.getUserEmail($userIds[$i]).'®istrantid='.$userIds[$i].'\',\''.getUserEmail($userIds[$i]).'\')" />' . $deleteImage . '</a></td>';
00306 }
00307 else {
00308 $tableBody .= '<a style="cursor:pointer" title="Delete" onclick="return gotopage(\'./+editregistrants&subaction=delete&useremail='.getUserEmail($userIds[$i]).'\',\''.getUserEmail($userIds[$i]).'\')" />' . $deleteImage . '</a></td>';
00309 }
00310 }
00311 $tableBody .= '<td>' . join(generateFormDataRow($moduleComponentId, $userIds[$i], $columnNames, $showUserProfileData), '</td><td>') . "</td></tr>\n";
00312 }
00313 $tableBody.="</tbody>";
00314
00315 $tableJqueryStuff = " null, $tableJqueryStuff";
00316
00317 $smarttable = smarttable::render(array('registrantstable'),array('registrantstable'=>array('aoColumns'=>"$tableJqueryStuff")));
00318 global $STARTSCRIPTS;
00319 $STARTSCRIPTS.="initSmartTable();";
00320 $javascriptBody = <<<JAVASCRIPTBODY
00321 $smarttable
00322 <script>
00323 function fnShowHide( iCol )
00324 {
00325 var bVis = oTable.fnSettings().aoColumns[iCol].bVisible;
00326 oTable.fnSetColumnVis( iCol, bVis ? false : true );
00327 }
00328
00329 </script>
00330 <script language="javascript">
00331 function gotopage(pagepath,useremail) {
00332 if(confirm("Are you sure you want to remove "+useremail+" from this form?"))
00333 window.location = pagepath;
00334 }
00335 </script>
00336 JAVASCRIPTBODY;
00337 $imagesFolder=$urlRequestRoot."/".$cmsFolder."/templates/common/images";
00338 $editRegistrantsView = '<br />';
00339 if($action == 'editregistrants') {
00340 $editRegistrantsView .= <<<EDITREGISTRANTSVIEW
00341 <form name="addusertoformform" method="POST" action="./+editregistrants" style="float:left">
00342 <script type="text/javascript" language="javascript" src="$urlRequestRoot/$cmsFolder/$templateFolder/common/scripts/ajaxsuggestionbox.js">
00343 </script>
00344
00345 <input type="text" name="useremail" id="userEmail" autocomplete="off" style="width: 256px" />
00346 <div id="suggestionsBox" class="suggestionbox"></div>
00347
00348
00349 <input type="submit" name="btnAddUserToForm" value="Add User to Form" />
00350 <script language="javascript" type="text/javascript">
00351 <!--
00352 var userBox = new SuggestionBox(document.getElementById('userEmail'), document.getElementById('suggestionsBox'), "./+editregistrants&subaction=getsuggestions&forwhat=%pattern%");
00353 userBox.loadingImageUrl = '$imagesFolder/ajaxloading.gif';
00354 -->
00355 </script>
00356 </form>
00357 <br /><br />
00358 EDITREGISTRANTSVIEW;
00359 }
00360 //$editRegistrantsView .= $javascriptBody.'<table border="1" id="registrantstable" class="paginate-20 max-pages-5 no-arrow rowstyle-alt colstyle-alt sortable display">' . $tableCaptions . $tableBody . '</table><br />';
00361 $editRegistrantsView .= $javascriptBody.$toggleColumns.'<table border="1" id="registrantstable" class="display">' . $tableCaptions . $tableBody . '</table><br />';
00362
00363 if(!$showEditButtons && isset($_POST['save_as_excel']))
00364 {
00365 header("Pragma: public");
00366 header("Expires: 0");
00367 header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
00368 header("Cache-Control: private",false);
00369 header("Content-Type: application/vnd.ms-excel");
00370 header("Content-Disposition: attachment; filename=\"$formName.xls\";" );
00371 header("Content-Transfer-Encoding: binary");
00372 echo '<table>' . $tableCaptions . $tableBody . '</table>';
00373 exit(1);
00374 }
00375
00376 if($action == 'editregistrants') {
00377 $editRegistrantsView .= '<form name="emptyregistrants" method="POST" action="./+editregistrants" onsubmit="return confirm(\'Are you sure you wish to remove all registrants from this form? This will also remove the users from any groups associated with this form.\')">' .
00378 '<input type="submit" name="btnEmptyRegistrants" value="Delete All Registrants" title="Deletes all registrations to this form" />' .
00379 '</form>';
00380 }
00381 return $editRegistrantsView;
00382 }
00383
00384