Does anyone know if it’s possible to configure one of the info fields on an upload field as a list (dropdown) type?

Currently, the only available field types are Text Field and Text Box, but I’d like to set info2 as a Category field with predefined list values (e.g., Option 1, Option 2, Option 3, etc.). See screenshot.

Is this supported, or is there a workaround to achieve this?

Thanks,
Ryan

Thanks,
Ryan

I'm thinking that i might be able to unset some of the field mappings if it's a duplicate record, but i'm not having much joy figuring it out.  

However i can't get the date (masterRefDate) to insert correctly. The date appears to be broken into it's year month and date components, how do i recombine them?

Thanks,

Ryan

addAction('record_postsave', 'plugin_addRevision', null, 4);

   function plugin_addRevision() {

  	global $SETTINGS, $CURRENT_USER, $tableName, $isNewRecord, $oldRecord;

  	if ($CURRENT_USER['username'] == 'admin') {

  		if ($tableName == 'mastertable')	{

  			$tablename = 'logtable';
  			$colsToValues = array(); // 
  			$colsToValues['createdDate='] = 'NOW()';
  			$colsToValues['updatedDate='] = 'NOW()';
  			$colsToValues['createdByUserNum' ] = $CURRENT_USER['num'];
  			$colsToValues['updatedByUserNum' ] = $CURRENT_USER['num'];

  			$colsToValues['ref'] = @$_REQUEST['masterRef'];
  			$colsToValues['refDate=']  = $_REQUEST['masterRefDate'];

  			$hideMissingFieldErrors = true;
  			$newRecordNum = mysql_insert( $tablename, $colsToValues, $hideMissingFieldErrors );
  		}
}
}

While it does work I'm not sure if it's the correct or most efficient approach.

$CombinedTotals = mysqli()->query("SELECT 
				mydate, (SUM(field1)+SUM(field2)+SUM(field3)+SUM(field4)) AS Total
				FROM cmsb_tablename 
				WHERE mydate>= '$_SESSION[startDate]' AND production_date <= '$_SESSION[endDate]'
				GROUP BY mydate");
							
while ( $rows = $CombinedTotals ->fetch_assoc() ) {
    echo $rows['mydate'] . " | " . $rows['Total'];
    echo "<br>";
}

Ryan

I can run the following code in the MySQL console and get the results i want between my selected date range. 

I could use the report builder add on but i want to display the results on the website, not within the admin section.

SELECT 
mydate, (SUM(field1)+SUM(field2)+SUM(field3)+ AS CombinedTotal
FROM cmsb_tablename
WHERE mydate >= 'yyyy-mm-dd' AND production_date <= 'yyyy-mm-dd'
GROUP BY mydate

Not sure on how to go about using the native functions to select this data and display it out using a foreach loop.

Can anyone point me in the right direction?

Thanks,
Ryan

Ryan

All my old sections appear to work fine but when i open the Sections Editor page in CMSB it's very slow to load (23 Seconds). I've tried re-uploading all the files in case something was missed but it had no effect. DevTools is pointing to a slow TTFB when the page is loading, any ideas on what i can do to fix this?

'where'    => 'townland LIKE "%\t'.$townland.'\t%"',

Having complete brain freeze on this one. I'm trying to add a where statement to display all articles that contain a match within a list of values stored in a list field. These tables are linked.

Query 1
Returns me the Townland record. In this case the value returned is (1)

Query 2
Searches the articles table for articles that match the selected townland.

The townlands field will contain a comma separated list of values something like (1,3,4,10,11,12) as articles can be related to more than one townland. The issue I'm having is that my query will return values similar to 1 like 10,11 & 12 as well.

Any ideas on how to fix this?

<?php 
   // load record from 'townlands'
  list($townlandsRecords, $townlandsMetaData) = getRecords(array(
    'tableName'   => 'townlands',
    'where'       => whereRecordNumberInUrl(0),
    'loadUploads' => true,
    'allowSearch' => false,
    'limit'       => '1',
  ));
  $townlandsRecord = @$townlandsRecords[0]; // get first record
  if (!$townlandsRecord) { dieWith404("Record not found!"); } // show error message if no record found
  $townland = $townlandsRecord['num'];
?>

<?php
 // load records from 'articles' where the townland is found
  list($articlesRecords, $articlesMetaData) = getRecords(array(
    'tableName'   => 'articles',
    'loadUploads' => true,
    'perPage'     => '20',
    'where'    => 'townland LIKE "%'.$townland.'%"',
    //'debugSql'    => true, 
  ));
?>

Thanks,
Ryan

That worked for me thanks.

For the benefit of anyone else looking to do something similar I added the following code to the membership plugin.

//AUDIT TRACKING CODE FOR LOGINS ******************
//Fetch User Info from CMS for Selected User & Log to DB  

   $LoggedOnUserName = mysql_escape($_REQUEST['username']);

   //Fetch User Data FROM accounts table
   list($accountsRecords, $accountsMetaData) = getRecords(array(
   'tableName'   => 'accounts',
   'where'       => 'username = "'.$LoggedOnUserName.'"', 
   'loadUploads' => true,
   'allowSearch' => false,
   'limit'       => '1'

     ));
   $accountsRecord = @$accountsRecords[0]; // get first record
   $LoginUserNum = $accountsRecord['num']; // Set User Number


   // ADD record to login audit table 
   $tablename   = 'login_audit';
   $colsToValues = array(); // 
   $colsToValues['createdDate=']      = 'NOW()';
   $colsToValues['updatedDate=']      = 'NOW()';
   $colsToValues['createdByUserNum']  = $LoginUserNum;
   $colsToValues['updatedByUserNum']  = $LoginUserNum;   
   $colsToValues['user']              = $LoginUserNum;
   $colsToValues['ip_address']        = $_SERVER['REMOTE_ADDR']; // Log IP 
   $hideMissingFieldErrors = true;
   $newRecordNum = mysql_insert($tablename, $colsToValues, $hideMissingFieldErrors); 

   //***************************** END OF TRACKING CODE UPDATE

Just before where the form values get cleared.

 // clear form values
  $_REQUEST['username'] = '';
  $_REQUEST['password'] = '';

Ryan

I don't really understand how to use hooks & plugins from the frontend of my application so i'll try the IF statement approach.

Should this code be added to the websiteMembership.php page or can i add to the successful login landing page?

Ryan

It looks like the table "testingTable" has not been setup or the name has been entered incorrectly. Can you confirm the name of the table you are trying to submit the data to?

Ryan

Try updating your code to this.


<?php 
 if (@$_POST['save']) {
       $tablename   = 'testingTable';
       $colsToValues = array();
       $colsToValues['createdDate=']     = 'NOW()';
       $colsToValues['updatedDate=']     = 'NOW()';
       $colsToValues['createdByUserNum'] = 0;
       $colsToValues['updatedByUserNum'] = 0;
       $colsToValues['first_name']         = $_REQUEST['first_name'];
       $colsToValues['last_name']         = $_REQUEST['last_name'];
       $colsToValues['email']            = $_REQUEST['email'];
       $hideMissingFieldErrors = true;
       $newRecordNum = mysql_insert($tablename, $colsToValues, $hideMissingFieldErrors); 
       }
 ?>

You might want to look about escaping those form values before you add them to your database.

Ryan

The problem I'm having is that my 'record login' code is triggered each time the page is loaded and not just on successful login and session start.

So basically I need to wrap my code in an if statement that can check if it's a new session or not?

Any Ideas?

  // If not logged in redirect
   if (!$CURRENT_USER) { websiteLogin_redirectToLogin(); }
  
  // If logged in set some global variables to use elsewhere
   if ($CURRENT_USER) {
     $GlobalUserNum = $CURRENT_USER['num'];
     $GlobalUserName = $CURRENT_USER['fullname'];
     $GlobalUserAccessLevel = $CURRENT_USER['access_level'];  
           
     // add record to login audit table 
     $tablename   = 'login_audit';
     $colsToValues = array(); 
     $colsToValues['createdDate=']     = 'NOW()';
     $colsToValues['updatedDate=']     = 'NOW()';
     $colsToValues['createdByUserNum'] = $GlobalUserNum;
     $colsToValues['updatedByUserNum'] = $GlobalUserNum;      
     $colsToValues['user_name']        = $GlobalUserName;
     $hideMissingFieldErrors = true;
     $newRecordNum = mysql_insert($tablename, $colsToValues, $hideMissingFieldErrors);   
           
           
   }
 } // End If

Thanks,

Ryan

Removing NO_ZERO_IN_DATE didn't seem to work for me. However when I removed STRICT_ALL_TABLES on line 67 it seemed to do the trick.

# set MySQL strict mode - http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html
  if ($strictMode) {
    $query = "SET SESSION sql_mode = 'NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER'";
    mysql_query($query) or die("MySQL Error: " .mysql_error(). "\n");
  }

Thanks for your help.

Sorry for the late response, I'm using version 5.7.7. The issue seemed to effect all sections when you try and add a date field. I was able to work around it by creating the field as a text field first and entering the date manually I.e. 2017-02-11 00:00:01 and then changing it to a date field. Seems like newer versions of MySQL don't like 0000-00-00 00:00:00 date values. I was out of the office the last couple of days but will try out your suggestion on Monday.

Thanks!

Ryan

Incorrect datetime value '0000-00-00 00:00:00' for column 'myField' at row 1

I'm using MySQL 5.7 and have tried setting SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES' to no avail.

Anyone any ideas?

Getting a strange issue with Newsletter Builder were my confirmation emails have double dots in the URL string. The extra dot is causing 404 errors and users are unable to confirm signup.

http://www.mydomain.ie/nl-manage-subscribers..php?n=778&a=6be4e6ea8049

http://www.mydomain..ie/nl-manage-subscribers.php

I've checked newsletterBuilder.php

// define placeholders - values defined lower in the list can use placeholder defined above them in the list
  // internal placeholders (undocumented)
  $placeholders['manage_url']      = $newsletterSettings['manage_url'];

  // public placeholders - values defined lower in the list can use placeholder defined above them in the list
  $placeholders['email_header']    = ''; // placeholder removed in v2.03 - remove the placeholder if it's left in old newsletter content
  $placeholders['email_footer']    = ''; // placeholder removed in v2.03 - remove the placeholder if it's left in old newsletter content
  $placeholders['hostname']        = coalesce( @parse_url($GLOBALS['SETTINGS']['adminUrl'], PHP_URL_HOST), 'PROGRAM_URL_NOT_SET' );
  $placeholders['from_name']       = $newsletterSettings['from_name'];
  $placeholders['from_email']      = $newsletterSettings['from_email'];
  $placeholders['to_email']        = $to_email;
  $placeholders['archive_url']     = $newsletterSettings['archive_url'];
  $placeholders['confirm_url']     = $newsletterSettings['manage_url'] . "?n=$subscriberNum&a=$subscriberAuthkey";
  $placeholders['unsubscribe_url'] = $newsletterSettings['manage_url'] . "?n=$subscriberNum&a=$subscriberAuthkey&m=$messageNum&u=1"; // if usernum(n) isn't specified user will just

I checked the values in the DB and the extra dot does not appear in there either.

Anyone any ideas on what's causing this?

CMSB Version 2.62
Newsletter Builder Version: 3.01

Thanks for that, have it working now!

I'm using this plugin as part of a basic ticket support system. I'm monitoring the assignedDate (when requests are assigned to an engineer) and the closedDate (when the engineer marks the request as closed) to report on the response time for each support request. So if users have the ability to modify a request after it's been closed these fields could be changed.

However you make a good point about users accidently closing records so I will allow admin users to modify the record and change the status back.

I'm using this plugin along with a variation of the Grey hidden records plugin that color codes the record rows based on their status, pending open or closed.

<?php
/*
Plugin Name: Hide Modify Link  
Description: Hide Modify Link when $record['status'] equals closed
Version: 1.00
Requires at least: 2.50
*/

//Add button to orders menu
addFilter('listRow_actionLinks',  'hideModifyLink', null, 3);

function hideModifyLink($trStyle, $tableName, $record) {
  global $CURRENT_USER;
 
 //Set modify link
 $modifyLink   = '?menu=' .htmlencode($tableName). "&amp;action=edit&amp;num=" . @$record['num'];

 //only works for certain tables and if the user is not an admin
 if (@$tableName == 'tickets' && !@$CURRENT_USER['isAdmin'])  {

  // and only if that status is set to closed (or in this case its value) 
  if (@$record['status'] && @$record['status'] == 3) { // if record is closed (3)
   
   //str_replace function finds modify link and replaces it with ""
   $trStyle = str_replace("<a href='$modifyLink'>" .t('modify'). "</a>\n", '', $trStyle);
 
    }
  }
  //After you've updated the link, return it.
  return $trStyle;
  }

?>

My only concern now is that engineers could change the ID's in the URL and still be able to modify the record after it's closed, but that's unlikely to happen.

Thanks again for your help.

Ryan