<?php
// error reporting again
ini_set('display_errors',1);
error_reporting(E_ALL);
date_default_timezone_set('America/Phoenix');

require_once('inc/vip.common.php'); // needed to load domain-specific config vars for database connection etc
require_once('inc/common.db.php');
require_once('inc/common.util.php');
require_once('controlcenter/inc/admin.util.php');

use \Common\DB;
use \Common\Util;

// from OAuth2 examples, docs, and source here -  https://github.com/bshaffer/oauth2-server-php/tree/v0.9
require_once('inc/external/oauth2-server/src/OAuth2/Autoloader.php');
OAuth2_Autoloader::register();

$storage = new OAuth2_Storage_Pdo(DB::dbh());
$server = new OAuth2_Server($storage);
//debug("operation=" . Request('op') . "\r\n");
if (is_null(Request('op'))) die('No operation specified.  Please refer to the Contrado API documentation.');
else $operation = Request('op');
$format = Request('format','json');

function debug($msg) {
  //file_put_contents("/Users/Greg/Downloads/API_log.txt", $msg, FILE_APPEND);
}

//debug("API REQUEST=" . print_r($_REQUEST,TRUE));
if ($operation=="authtoken") {
//debug("AUTHTOKEN REQUESTED\r\n");


// AUTHTOKEN

    $server->addGrantType(new OAuth2_GrantType_ClientCredentials($storage)); // or any grant type you like!
    $request = OAuth2_Request::createFromGlobals();
    $response = new OAuth2_Response();
    $server->handleTokenRequest($request, $response);//->send();

    // save vipamdin logged-in user pass-thru for future API filtering
//debug("Request=" . print_r($_REQUEST, TRUE));
    $tmp  = $response->getParameters();
//debug("Request=" . print_r($_REQUEST, TRUE));
//debug("OAuth returns=" . print_r($tmp,TRUE));
    strip_numeric_keys($tmp);
//debug("stripped=" . print_r($tmp,TRUE));


    if (array_key_exists('access_token',$tmp) && Request('client_id')=='vipadminapiuser@contradovip.com' && Request('session_user_id')!='') {
        // save local UserID in new oauth_vipadmin_session.api_session_user_id field -  the end-user /vipadmin/ session is not the same and variables do not automagically get copied
        $sql = "INSERT INTO oauth_vipadmin_session SET access_token='" . addslashes($tmp['access_token']) . "', api_session_user_id=" . (int)Request('session_user_id');
//debug("sql=" . $sql . "\r\n");
        $db = DB::dbh();
    	$stmt = $db->prepare($sql);
    	$stmt->execute();
    }
    if ($format=='xml') $tmp2 = xml_encode($tmp);
    else $tmp2 = json_encode($tmp);
    echo $tmp2;
//debug("tmp2=" . $tmp2 . "\r\n");

} else {

//debug("validating AUTH TOKEN (" . Request('access_token','') . ")\r\n");
    // verify caller supplied a valid authtoken with the request
    if (!$server->verifyResourceRequest(OAuth2_Request::createFromGlobals(), new OAuth2_Response())) {
        $server->getResponse()->send();
//debug("INVALID AUTH TOKEN (" . Request('access_token','') . ")\r\n");
        die("INVALID AUTH TOKEN (" . print_r($_REQUEST,TRUE) . ")");
    }
//debug("IS VALID\r\n");
    // get oauth_client and corresponding User from token - revalidates access token on every API call
    $db = DB::dbh();
    if (defined('DB_API_REQUIRES_COLLATE') && DB_API_REQUIRES_COLLATE=="TRUE") $collate = " COLLATE utf8_general_ci";
    else $collate = "";
    $sql = "SELECT User.*, oauth_vipadmin_session.api_session_user_id
                , GROUP_CONCAT(DISTINCT `Client`.ClientID) AS ClientID
                , GROUP_CONCAT(DISTINCT `Client`.ClientDisplayName) AS ClientDisplayName
                , GROUP_CONCAT(IF(UserProjectPermissions.Access=1,UserProjectPermissions.ProjectID,NULL)) AS PermsAccess
                , GROUP_CONCAT(IF(UserProjectPermissions.Notification=1,UserProjectPermissions.ProjectID,NULL)) AS PermsNotifications
                , UserType.UserTypeID, UserType.UserTypeCode
                , IF(ClientPromoEndDate IS NOT NULL AND ClientPromoEndDate>CURDATE() AND ClientPromoLevel!='Disabled', ClientPromoLevel, ClientSubscriptionLevel) AS ClientSubscriptionLevel
            FROM oauth_access_tokens
            INNER JOIN oauth_clients ON oauth_clients.client_id=oauth_access_tokens.client_id " . $collate . "
            LEFT JOIN oauth_vipadmin_session ON oauth_vipadmin_session.access_token=oauth_access_tokens.access_token " . $collate . "
            INNER JOIN User ON User.UserEmail=oauth_clients.client_id " . $collate . "
            INNER JOIN UserType ON UserType.UserTypeID=User.UserTypeID
            LEFT JOIN UserProjectPermissions ON UserProjectPermissions.UserID=User.UserID
            LEFT JOIN ClientUser ON ClientUser.UserID=User.UserID
            LEFT JOIN `Client` ON ClientUser.ClientID=Client.ClientID
            WHERE oauth_access_tokens.access_token='" . addslashes(Request('access_token','')) . "'
            GROUP BY User.UserID";
            // NOTE 'COLLATE utf8_general_ci' required after 'oauth_clients.client_id ' above on some servers
//debug("sql=" . $sql . "\r\n");
	if (DB::fetchAll($users, $sql, array()) === 0) {
		if (count($users) === 1) {
		    $user = $users[0];
		    if ($user['UserTypeCode']=='superadmin' || $user['UserTypeCode']=='admin' || $user['UserTypeCode']=='manager') $user['ClientSubscriptionLevel']='Platinum';
		} else {
		    // user can get an access_token not tied to ANY oauth_client by providing EMPTY username and password params in authorization request (!)
		    // however, this could also happen if we have oauth_client records that are not properly linked to a User record (via email address).
		    die('Your access token is valid but it is not linked to any account.  Please contact Contrado API support.');
		}
	} else {
	   die('Failed fetching user information.  Please contact Contrado API support.');
	}

//debug("user=" . print_r($user,TRUE));
//debug("Session User ID: " . $user['api_session_user_id']  ."\r\n");
    // effect the pass-thru of /vipadmin/ logged-in user by overwriting the internal vipadminapiuser@contradovip.com user account variables
    if ((int)$user['api_session_user_id']!=0) {
        $_SESSION['User_UserID'] = $user['api_session_user_id']; // _SESSIONS are not maintained with curl calls so we have to restore from DB every time
        $sql = "SELECT User.*
                , GROUP_CONCAT(DISTINCT Client.ClientID) AS ClientID
                , GROUP_CONCAT(DISTINCT Client.ClientDisplayName) AS ClientName
                , GROUP_CONCAT(IF(UserProjectPermissions.Access=1,UserProjectPermissions.ProjectID,NULL)) AS PermsAccess
                , GROUP_CONCAT(IF(UserProjectPermissions.Notification=1,UserProjectPermissions.ProjectID,NULL)) AS PermsNotifications
                , UserType.UserTypeID, UserType.UserTypeCode
                , IF(ClientPromoEndDate IS NOT NULL AND ClientPromoEndDate>CURDATE() AND ClientPromoLevel!='Disabled', ClientPromoLevel, ClientSubscriptionLevel) AS ClientSubscriptionLevel
            FROM User
            INNER JOIN UserType ON UserType.UserTypeID=User.UserTypeID
            LEFT JOIN UserProjectPermissions ON UserProjectPermissions.UserID=User.UserID
            LEFT JOIN ClientUser ON ClientUser.UserID=User.UserID
            LEFT JOIN Client ON ClientUser.ClientID=Client.ClientID
            WHERE User.UserID='" . $_SESSION['User_UserID']  . "'
            GROUP BY User.UserID ";
//debug("sql=" . $sql . "\r\n");
    }
	if (DB::fetchAll($users, $sql, array()) === 0) {
		if (count($users) === 1) {
		    $user = $users[0];
		  }
    }
//debug("user=" . print_r($user,TRUE));

	if ($user['PermsAccess']=='' && $user['UserTypeCode']=='client') {
    	$user['PermsAccess'] = '0'; // SQL syntax fixer
    }
	if ($user['PermsNotifications']=='' && $user['UserTypeCode']=='client') {
    	$user['PermsNotifications'] = '0'; // SQL syntax fixer
    }

    // grab all potential parameters
    $client_id = Request('client_id','');
    $project_id = Request('project_id','');
    $kiosk_id = Request('kiosk_id','');
    $plan_id = Request('plan_id','');
    $plan_name = Request('plan_name','');
    $savedplan_id = Request('savedplan_id','');
    $site_id = Request('site_id','');
    $is_active = Request('is_active','');
    $status = Request('status','');
    $site_id = Request('site_id','');
    $areamap_id = Request('areamap_id','');
    $user_id = Request('user_id','');
    $user_email = Request('user_email','');
    $heat_maxdays = Request('heat_maxdays','');
    $heat_saveweight = Request('heat_saveweight','');
    $heat_ceiling = Request('heat_ceiling','');
    $filter = Request('filter','');
    $start_date = Request('start_date','');
    if ($start_date!='') $start_date = date('Y-m-d',strtotime($start_date)); // mysql date format
    $end_date = Request('end_date','');
    if ($end_date!='') $end_date = date('Y-m-d 23:59:59',strtotime($end_date)); // mysql date format
    $searchtext = Request('search','');
    $sort = Request('sort','');
    $sortdir = Request('sortdir','');
    $page = Request('page','');
    $pagesize = Request('pagesize','');

    $json = Request('json',''); // extra data for bulk imports etc

debug("_REQUEST=" . print_r($_REQUEST,TRUE) . "\r\n");


// LIST CLIENTS (builders) - optional args: client_id, search
    if ($operation=="list_clients" || $operation=="list_clients_verbose" || $operation=="count_clients") {

        if ($operation=="list_clients_verbose") {
    		$sql = 'SELECT Client.ClientID,ClientDisplayName, Client.ClientSkinFolder
    		       ,Client.ClientRate, Client.ClientRateType, Client.ClientURLFragment
    		       ,Client.ClientPromoReminderNotes
    		       ,CONCAT_WS(" ", mgr.UserFirstName, mgr.UserLastName) AS AccountManager
    		       ,Client.ClientPromoReminderNotes
                   ,COUNT(DISTINCT Project.ProjectID) AS ProjectCount
                   , SUM(FloorPlans.TotalPlanCount) AS TotalPlanCount
                   , SUM(FloorPlans.BillablePlanCount) AS BillablePlanCount
                   , SUM(FloorPlans.NBPlanCount) AS NBPlanCount
                   , SUM(FloorPlans.InProductionPlanCount) AS InProductionPlanCount
                   , SUM(FloorPlans.PendingApprovalPlanCount) AS PendingApprovalPlanCount
                   , SUM(FloorPlans.ApprovedPlanCount) AS ApprovedPlanCount
                   , SUM(FloorPlans.UnderRevisionPlanCount) AS UnderRevisionPlanCount
                   , SUM(FloorPlans.RevisionPendingPlanCount) AS RevisionPendingPlanCount
                   , SUM(FloorPlans.RetiredPlanCount) AS RetiredPlanCount
                   , SUM(SitePlans.TotalSitePlanCount) AS TotalSitePlanCount
                   , SUM(SitePlans.BillableSitePlanCount) AS BillableSitePlanCount
                   , SUM(SitePlans.NBSitePlanCount) AS NBSitePlanCount
                   , Kiosks.TotalKioskCount
                   , Kiosks.BillableKioskCount
                   , Kiosks.NBKioskCount
                   , SUM(AreaMaps.TotalAreaMapCount) AS TotalAreaMapCount
                   , SUM(AreaMaps.BillableAreaMapCount) AS BillableAreaMapCount
                   , SUM(AreaMaps.NBAreaMapCount) AS NBAreaMapCount
                   , SUM(FloorPlans.ProspectSavedPlanCount) AS ProspectSavedPlanCount
                   , SUM(FloorPlans.UserSavedPlanCount) AS UserSavedPlanCount
                   , SUM(FloorPlans.TotalSavedPlanCount) AS TotalSavedPlanCount
    		        FROM Client
    		        LEFT JOIN Project ON Client.ClientID=Project.ClientID
    	            LEFT JOIN
                        (SELECT Plan.PlanID, Plan.ProjectID
                                ,COUNT(DISTINCT IF(Plan.Status<>"Retired", Plan.PlanID,NULL)) AS TotalPlanCount
                                ,COUNT(DISTINCT IF(Plan.IsActive=1 AND Plan.Status="Approved", Plan.PlanID,NULL)) AS BillablePlanCount
                                ,COUNT(DISTINCT IF(Plan.Status<>"Approved" AND Plan.Status<>"Retired", Plan.PlanID,NULL)) AS NBPlanCount
                                ,COUNT(DISTINCT IF(Plan.Status="In Production", Plan.PlanID,NULL)) AS InProductionPlanCount
                                ,COUNT(DISTINCT IF(Plan.Status="Pending Approval", Plan.PlanID,NULL)) AS PendingApprovalPlanCount
                                ,COUNT(DISTINCT IF(Plan.Status="Approved", Plan.PlanID,NULL)) AS ApprovedPlanCount
                                ,COUNT(DISTINCT IF(Plan.Status="Under Revision", Plan.PlanID,NULL)) AS UnderRevisionPlanCount
                                ,COUNT(DISTINCT IF(Plan.Status="Revision Pending Approval", Plan.PlanID,NULL)) AS RevisionPendingPlanCount
                                ,COUNT(DISTINCT IF(Plan.Status="Retired", Plan.PlanID,NULL)) AS RetiredPlanCount
                                ,SUM(COALESCE(UserType.IsProspect,0)) AS ProspectSavedPlanCount
                                ,COUNT(UserType.UserTypeID)-SUM(COALESCE(UserType.IsProspect,0))  AS UserSavedPlanCount
                                ,COUNT(UserType.UserTypeID) AS TotalSavedPlanCount
                         FROM Plan
                         LEFT JOIN SavedPlan ON SavedPlan.PlanID=Plan.PlanID
                         LEFT JOIN User ON User.UserID=SavedPlan.UserIDWeb
                         LEFT JOIN UserType ON UserType.UserTypeID=User.UserTypeID
                         GROUP BY Plan.ProjectID) AS FloorPlans ON FloorPlans.ProjectID=Project.ProjectID
                    LEFT JOIN
                         (SELECT Site.SiteID, Site.ProjectID
                                 ,COUNT(DISTINCT IF(Site.Status<>"Retired", Site.SiteID,NULL)) AS TotalSitePlanCount
                                 ,COUNT(DISTINCT IF(Site.IsActive=1 AND Site.Status="Approved" AND Site.WhenCreated>FirstSite.WhenCreated, Site.SiteID,NULL)) AS BillableSitePlanCount
                                 ,COUNT(DISTINCT IF(Site.Status<>"Approved" AND (Site.Status<>"Retired" OR Site.WhenCreated<=FirstSite.WhenCreated), Site.SiteID,NULL)) AS NBSitePlanCount
                          FROM Site
                          LEFT JOIN Site AS FirstSite ON FirstSite.SiteID=1
                          GROUP BY Site.ProjectID) AS SitePlans ON SitePlans.ProjectID=Project.ProjectID
                    LEFT JOIN
                        (SELECT Kiosk.KioskID, Kiosk.KioskClientID
                                ,COUNT(DISTINCT IF(Kiosk.Status<>"Retired", Kiosk.KioskID, NULL)) AS TotalKioskCount
                                ,COUNT(DISTINCT IF(Kiosk.KioskIsActive=1 AND Kiosk.Status="Approved" AND Kiosk.WhenCreated>FirstKiosk.WhenCreated, Kiosk.KioskID,NULL)) AS BillableKioskCount
                                ,COUNT(DISTINCT IF(Kiosk.Status<>"Approved" AND (Kiosk.Status<>"Retired" OR Kiosk.WhenCreated<=FirstKiosk.WhenCreated), Kiosk.KioskID,NULL)) AS NBKioskCount
                         FROM Kiosk
                         LEFT JOIN Kiosk AS FirstKiosk ON FirstKiosk.KioskID=1
                         GROUP BY Kiosk.KioskClientID) AS Kiosks ON Kiosks.KioskClientID=Client.ClientID
                    LEFT JOIN
                          (SELECT AreaMap.AreaMapID, AreaMap.ProjectID
                                  ,COUNT(DISTINCT IF(AreaMap.Status<>"Retired", AreaMap.AreaMapID, NULL)) AS TotalAreaMapCount
                                  ,COUNT(DISTINCT IF(AreaMap.IsActive=1 AND AreaMap.Status="Approved" AND AreaMap.WhenCreated>FirstAreaMap.WhenCreated, AreaMap.AreaMapID,NULL)) AS BillableAreaMapCount
                                  ,COUNT(DISTINCT IF(AreaMap.Status<>"Approved" AND (AreaMap.Status<>"Retired" OR AreaMap.WhenCreated<=FirstAreaMap.WhenCreated), AreaMap.AreaMapID,NULL)) AS NBAreaMapCount
                           FROM AreaMap
                           LEFT JOIN AreaMap AS FirstAreaMap ON FirstAreaMap.AreaMapID=1
                           GROUP BY AreaMap.ProjectID) AS AreaMaps ON AreaMaps.ProjectID=Project.ProjectID
    	            LEFT JOIN User mgr ON Client.ClientAccountManagerUserID=mgr.UserID
    	            WHERE ClientActive=1 ';
        } else {
    		$sql = 'SELECT Client.ClientID,ClientDisplayName,Client.ClientSkinFolder
    		       ,Client.ClientRate, Client.ClientRateType, Client.ClientURLFragment
    		       ,Client.ClientPromoReminderNotes
    		        FROM Client
    	            WHERE ClientActive=1 ';
        }
		$prefix = " AND ";
		if ($user['ClientID']!='') {
            $sql .= $prefix . ' Client.ClientID IN(' . escapeString($user['ClientID']) . ')';
        }
        if ($client_id!='') {
            $sql .= $prefix . ' Client.ClientID IN(' . escapeString($client_id) . ')';
        }
		// limit to matches based on user-supplied search text
		if ($searchtext!='')  {
            $sql .= $prefix . " Client.ClientDisplayName LIKE '%" . addslashes($searchtext) . "%'";
		}

        $sql .= " GROUP BY Client.ClientID";

        if ($sort!='') {
		  if ($sort=='client') $sql .= " ORDER BY ClientDisplayName";
		  elseif ($sort=='projects') $sql .= " ORDER BY COUNT(DISTINCT Project.ProjectID)";
		  elseif ($sort=='plans') $sql .= " ORDER BY TotalPlanCount";
		  elseif ($sort=='users') $sql .= " ORDER BY COUNT(UserType.UserTypeID)-SUM(COALESCE(UserType.IsProspect,0))";
		  elseif ($sort=='prospects') $sql .= " ORDER BY SUM(COALESCE(UserType.IsProspect,0))";
		  else $sql .= " ORDER BY ClientDisplayName "; // default in case user passed an unknown sort field option
		  if ($sortdir=="asc") $sql .= " ASC "; // ascending sort (default)
		  if ($sortdir=="desc") $sql .= " DESC "; // descending sort
        } else $sql .= " ORDER BY ClientDisplayName ";


debug("sql=" . $sql . "\r\n");

        if ($operation=="count_clients") {
            $sql = "SELECT COUNT(*) AS client_count FROM (" . $sql . ") tmp";
        } elseif ((int)$page > 0 || $pagesize > 0) {
            if ((int)$pagesize<=0) $pagesize = 50;
            $sql .= " LIMIT " . ((int)$page * (int)$pagesize) . "," . $pagesize;
        }

		if (DB::fetchAll($recs, $sql, array()) === 0) {
		    if ($operation=="count_clients") $recs = $recs[0];
		    strip_numeric_keys($recs);
		    if ($format=='xml') echo xml_encode($recs);
            else echo json_encode($recs);
		}

// LIST kiosks - optional args: client_id, project_id
    } else if ($operation=="list_kiosks" || $operation=="list_kiosks_verbose" || $operation=="count_kiosks") {

		$sql = "SELECT Kiosk.KioskID,ClientDisplayName, Kiosk.KioskDisplayName
		            ,Kiosk.KioskPath,Client.ClientPath, Kiosk.KioskSkinFolder
		            ,`URL`.`URL` AS KioskURL
		            ,Client.ClientURLFragment
		            ,GROUP_CONCAT(DISTINCT Project.ProjectDisplayname ORDER BY ProjectDisplayname SEPARATOR ',') AS ProjectDisplayNames
		            ,GROUP_CONCAT(DISTINCT Project.ProjectID SEPARATOR ',') AS ProjectIDs
                    , Kiosk.KioskIsActive
                    , Kiosk.Status
                    , IF(FirstKiosk.WhenCreated<Kiosk.WhenCreated,'Billable','Non-Billable Legacy') BillableStatus
		        FROM Kiosk
                INNER JOIN Kiosk AS FirstKiosk ON FirstKiosk.KioskID=1
                INNER JOIN Client ON Client.ClientID=Kiosk.KioskClientID
		        LEFT JOIN `URL` ON `URL`.KioskID=Kiosk.KioskID
		        LEFT JOIN KioskProject ON KioskProject.KioskID=Kiosk.KioskID
		        LEFT JOIN Project ON KioskProject.ProjectID=Project.ProjectID";
        $prefix = ' WHERE ';
		if ($is_active!='' && $is_active!='-1') {
            $sql .= $prefix . " Kiosk.KioskIsActive=" . (int)$is_active;
            $prefix = ' AND ';
        }
		if ($user['ClientID']!='') {
            $sql .= $prefix . ' Kiosk.KioskClientID IN(' . escapeString($user['ClientID']) . ')';
            $prefix = ' AND ';
        }
        if ($client_id!='' && (int)$client_id>0) {
            $sql .= $prefix . ' Kiosk.KioskClientID IN(' . escapeString($client_id) . ')';
            $prefix = ' AND ';
        }
		if ($user['PermsAccess']!='') {
            $sql .= $prefix . ' Project.ProjectID IN(' . $user['PermsAccess'] . ')';
            $prefix = ' AND ';
        }
		if ($project_id!='' && (int)$project_id>0) {
            $sql .= $prefix . " Project.ProjectID IN(" . escapeString($project_id) . ")";
            $prefix = ' AND ';
        }
		if ($kiosk_id!='' && (int)$kiosk_id>0) {
            $sql .= $prefix . " KioskID IN(" . escapeString($kiosk_id) . ")";
            $prefix = ' AND ';
        }
      if ($status!='') {
            $sql .= $prefix . " Kiosk.Status='" . $status . "'";
            $prefix = ' AND ';
        }

		// limit to matches based on user-supplied search text
		if ($searchtext!='')  {
            $sql .= $prefix . " (Kiosk.KioskDisplayName LIKE '%" . addslashes($searchtext) . "%' OR Client.ClientDisplayName LIKE '%" . addslashes($searchtext) . "%' OR Project.ProjectDisplayName LIKE '%" . addslashes($searchtext) . "%')";
            $prefix = ' AND ';
		}

		$sql .= ' GROUP BY Kiosk.KioskID ';

        if ($sort!='') {
          if ($sort=='client') $sql .= " ORDER BY ClientDisplayName";
          elseif ($sort=='kiosk') $sql .= " ORDER BY KioskDisplayName";
          elseif ($sort=='status') $sql .= " ORDER BY KioskDisplayName";
          else $sql .= " ORDER BY ClientDisplayName,  KioskDisplayName"; // default in case user passed an unknown sort field option
		  if ($sortdir=="asc") $sql .= " ASC "; // ascending sort (default)
		  if ($sortdir=="desc") $sql .= " DESC "; // descending sort
        } else $sql .= " ORDER BY KioskDisplayName ";


        if ($operation=="count_kiosks") {
            $sql = "SELECT COUNT(*) AS kiosk_count FROM (" . $sql . ") tmp";
        } elseif ((int)$page > 0 || $pagesize > 0) {
            if ((int)$pagesize<=0) $pagesize = 50;
            $sql .= " LIMIT " . ((int)$page * (int)$pagesize) . "," . $pagesize;
        }

debug("SQL=" . $sql . "\r\n");

		if (DB::fetchAll($recs, $sql, array()) === 0) {
		    if ($operation=="count_kiosks") $recs = $recs[0];
		    strip_numeric_keys($recs);
debug("recs=" . print_r($recs, TRUE));
debug("format=" . $format);
debug("json=" . json_encode($recs));
		    if ($format=='xml') echo xml_encode($recs);
            else echo json_encode($recs);
            exit(0);
		}

// LIST PROJECTS (communities) - optional arg: client_id
    } else if ($operation=="list_projects" || $operation=="list_projects_verbose" || $operation=="count_projects") {

        if ($operation=="list_projects_verbose") {
    		$sql = 'SELECT Project.ProjectID,ClientDisplayName,ProjectDisplayName,ProjectSuppressDisplayName
    		        ,Project.ProjectPath,Client.ClientPath, Project.ProjectSkinFolder
    		        ,Client.ClientRateType, Client.ClientRate
    		        ,Client.ClientURLFragment
    		        ,Project.ProjectURLFragment
                    ,COUNT(DISTINCT Plan.PlanID) AS PlanCount
                    ,COUNT(DISTINCT IF(Plan.IsActive=1, Plan.PlanID,NULL)) AS ActivePlanCount
                    ,COUNT(DISTINCT IF(Plan.IsActive=0, Plan.PlanID,NULL)) AS InactivePlanCount
                    ,SUM(COALESCE(UserType.IsProspect,0)) AS ProspectSavedPlanCount
                    ,COUNT(UserType.UserTypeID)-SUM(COALESCE(UserType.IsProspect,0))  AS UserSavedPlanCount
                    ,COUNT(UserType.UserTypeID) AS TotalSavedPlanCount
                    ,COALESCE(SitePlan.SitePlanCount,0) AS SitePlanCount
    		       FROM Project
    		        INNER JOIN Client ON Client.ClientID=Project.ClientID
    	            LEFT JOIN Plan ON Plan.ProjectID=Project.ProjectID
    	            LEFT JOIN SavedPlan ON SavedPlan.PlanID=Plan.PlanID
    	            LEFT JOIN User ON User.UserID=SavedPlan.UserIDWeb
    	            LEFT JOIN UserType ON UserType.UserTypeID=User.UserTypeID
    	            LEFT JOIN (SELECT ProjectID, COUNT(*) AS SitePlanCount FROM Site GROUP BY ProjectID) SitePlan ON SitePlan.ProjectID=Project.ProjectID ';
        } else {
    		$sql = 'SELECT Project.ProjectID,ClientDisplayName,ProjectDisplayName,ProjectSuppressDisplayName
    		        ,Project.ProjectPath,Client.ClientPath, Project.ProjectSkinFolder
    		        ,Client.ClientURLFragment
    		        ,Project.ProjectURLFragment
                    ,COUNT(DISTINCT Plan.PlanID) AS PlanCount
                    ,COUNT(DISTINCT IF(Plan.IsActive=1, Plan.PlanID,NULL)) AS ActivePlanCount
    		        FROM Project
    		        INNER JOIN Client ON Client.ClientID=Project.ClientID
    	            LEFT JOIN Plan ON Plan.ProjectID=Project.ProjectID ';

        }
        $prefix = ' WHERE ';
        if ($is_active=='') $is_active = 1;
        if ($is_active!='-1') {
            $sql .= $prefix . " Project.ProjectActive=" . (int)$is_active;
            $prefix = ' AND ';
        }
		if ($user['ClientID']!='') {
            $sql .= $prefix . ' Project.ClientID IN(' . escapeString($user['ClientID']) . ')';
        }
        if ($client_id!='' && (int)$client_id>0) {
            $sql .= $prefix . ' Project.ClientID IN(' . escapeString($client_id) . ')';
        }
		if ($user['PermsAccess']!='') {
            $sql .= $prefix . ' Project.ProjectID IN(' . $user['PermsAccess'] . ')';
        }
		if ($project_id!='' && (int)$project_id>0) {
            $sql .= $prefix . " Project.ProjectID IN(" . escapeString($project_id) . ")";
        }
		// limit to matches based on user-supplied search text
		if ($searchtext!='')  {
            $sql .= $prefix . " (Project.ProjectDisplayName LIKE '%" . addslashes($searchtext) . "%' OR Client.ClientDisplayName LIKE '%" . addslashes($searchtext) . "%')";
		}

		$sql .= ' GROUP BY Project.ProjectID ';

        if ($operation=="list_projects_verbose") {
    		if ($is_active=='1') {
                $sql .= " HAVING (PlanCount>0 OR SitePlanCount>0) ";
            } elseif ($is_active=='0') {
                $sql .= " HAVING (PlanCount=0 AND SitePlanCount=0)s ";
            }
        }
        if ($sort!='') {
          if ($sort=='client') $sql .= " ORDER BY ClientDisplayName";
          elseif ($sort=='project') $sql .= " ORDER BY ProjectDisplayName";
          elseif ($sort=='activeplans') $sql .= " ORDER BY COUNT(DISTINCT IF(Plan.IsActive=1, Plan.PlanID,NULL)) ";
          elseif ($sort=='plans') $sql .= " ORDER BY COUNT(DISTINCT Plan.PlanID)";
		  elseif ($sort=='users') $sql .= " ORDER BY COUNT(UserType.UserTypeID)-SUM(COALESCE(UserType.IsProspect,0))";
		  elseif ($sort=='prospects') $sql .= " ORDER BY SUM(COALESCE(UserType.IsProspect,0))";
		  else $sql .= " ORDER BY ProjectDisplayName "; // default in case user passed an unknown sort field option
		  if ($sortdir=="asc") $sql .= " ASC "; // ascending sort (default)
		  if ($sortdir=="desc") $sql .= " DESC "; // descending sort
        } else $sql .= " ORDER BY ClientDisplayName,ProjectDisplayName ";


        if ($operation=="count_projects") {
            $sql = "SELECT COUNT(*) AS project_count FROM (" . $sql . ") tmp";
        } elseif ((int)$page > 0 || $pagesize > 0) {
            if ((int)$pagesize<=0) $pagesize = 50;
            $sql .= " LIMIT " . ((int)$page * (int)$pagesize) . "," . $pagesize;
        }

debug("SQL=" . $sql . "\r\n");

		if (DB::fetchAll($recs, $sql, array()) === 0) {
		    if ($operation=="count_projects") $recs = $recs[0];
		    strip_numeric_keys($recs);
		    if ($format=='xml') echo xml_encode($recs);
            else echo json_encode($recs);
		}

// LIST PLANS - args: client_id, project_id
    } else if ($operation=="list_plans" || $operation=="list_plans_verbose" || $operation=="list_plan_names" || $operation=="count_plans") {

        if($operation=="list_plans_verbose") {
    		$sql = 'SELECT Client.ClientID, Client.ClientDisplayName, Client.ClientDomain, Project.ProjectID, Project.ProjectDisplayName, Project.ProjectSuppressDisplayName
    		               ,Plan.PlanID, Plan.PlanDisplayName,URL.URL AS PlanBaseURL
    		               ,Plan.IsActive
    		               ,Plan.WhenCreated
    		               ,Plan.WhenDeactivated
    		               ,Plan.RevisionNumber
    		               ,Plan.Status
    		               ,prn.DateApproved
    		               ,DATEDIFF(NOW(),COALESCE(prn2.DateInStatus, Plan.WhenCreated)) AS DaysInStatus
    		               ,opt.OptionCount
    		               ,SUM(COALESCE(UserType.IsProspect,0)) AS ProspectSavedPlanCount
    		               ,COUNT(User.UserID)-SUM(COALESCE(UserType.IsProspect,0))  AS UserSavedPlanCount
    		               ,COUNT(User.UserID) AS TotalSavedPlanCount
                    FROM Plan
                    INNER JOIN Plan p2 ON p2.OriginalPlanID=Plan.OriginalPlanID AND p2.PlanID<=Plan.PlanID
    	            LEFT JOIN URL ON URL.PlanID=Plan.PlanID
    	            LEFT JOIN Project ON Plan.ProjectID=Project.ProjectID
    	            LEFT JOIN Client ON Project.ClientID=Client.ClientID
    	            LEFT JOIN SavedPlan ON SavedPlan.PlanID=p2.PlanID
    	            LEFT JOIN User ON User.UserID=SavedPlan.UserIDWeb
    	            LEFT JOIN UserType ON UserType.UserTypeID=User.UserTypeID
    	            LEFT JOIN (
    	                SELECT PlanID, MAX(WhenChanged) AS DateApproved
    	                FROM PlanRevisionNotes
    	                WHERE NewStatus=\'Approved\'
    	                GROUP BY PlanID
    	            ) AS prn ON prn.PlanID=Plan.PlanID
    	            LEFT JOIN (
    	                SELECT PlanID, MAX(WhenChanged) AS DateInStatus
    	                FROM PlanRevisionNotes
    	                GROUP BY PlanID
    	            ) AS prn2 ON prn2.PlanID=Plan.PlanID
    	            LEFT JOIN (
    		              SELECT PlanOptionGroup.PlanID, COUNT(DISTINCT OptionID) AS OptionCount
    		              FROM PlanOptionGroup
    		              INNER JOIN PlanView ON PlanOptionGroup.ViewID=PlanView.ViewID
    		              LEFT JOIN PlanOption ON PlanOption.OptionGroupID=PlanOptionGroup.OptionGroupID AND PlanOption.OptionEnabled=1
    		              WHERE PlanOptionGroup.OptionGroupTypeID=1
    		              GROUP BY  PlanOptionGroup.PlanID
    	            ) AS opt ON opt.PlanID=Plan.PlanID ';
        } elseif ($operation=='list_plan_names') {
    		$sql = 'SELECT DISTINCT Plan.PlanDisplayName
                    FROM Plan
    	            LEFT JOIN Project ON Plan.ProjectID=Project.ProjectID
    	            LEFT JOIN Client ON Project.ClientID=Client.ClientID';
        } else {
    		$sql = 'SELECT Client.ClientID, Client.ClientDisplayName, Client.ClientDomain, Project.ProjectID, Project.ProjectDisplayName, Project.ProjectSuppressDisplayName, Plan.PlanID, Plan.PlanDisplayName,URL.URL AS PlanBaseURL
    		               ,Plan.IsActive
    		               ,Plan.RevisionNumber
    		               ,Plan.Status
    		               ,DATEDIFF(NOW(),COALESCE(prn2.DateInStatus, Plan.WhenCreated)) AS DaysInStatus
                    FROM Plan
    	            LEFT JOIN Project ON Plan.ProjectID=Project.ProjectID
    	            LEFT JOIN Client ON Project.ClientID=Client.ClientID
    	            LEFT JOIN URL ON URL.PlanID=Plan.PlanID
    	            LEFT JOIN (
    	                SELECT PlanID, MAX(WhenChanged) AS DateInStatus
    	                FROM PlanRevisionNotes
    	                GROUP BY PlanID
    	            ) AS prn2 ON prn2.PlanID=Plan.PlanID';
        }
        // limit to ClientID to limit ProjectID hacks
        $prefix = ' WHERE ';
		if ($user['ClientID']!='') {
            $sql .= $prefix . ' Project.ClientID IN(' . escapeString($user['ClientID']) . ')';
            $prefix = ' AND ';
		}
		if ($client_id!='') {
            $sql .= $prefix . ' Project.ClientID IN(' . escapeString($client_id) . ')';
            $prefix = ' AND ';
		}
		if ($user['PermsAccess']!='' || $user['UserTypeCode']=='client') {
            $sql .= $prefix . ' Project.ProjectID IN(' . $user['PermsAccess'] . ')';
            $prefix = ' AND ';
        }
		if ($project_id!='') {
            $sql .= $prefix . " Project.ProjectID IN(" . escapeString($project_id) . ")";
            $prefix = ' AND ';
        }
        if ($operation=='list_plan_names') {
            $sql .= ' GROUP BY PlanDisplayName
    	              ORDER BY PlanDisplayName';
        } else {
    		if ($plan_name!='') {
                $sql .= $prefix . " Plan.PlanDisplayname LIKE '%" . escapeString($plan_name) . "%'";
                $prefix = ' AND ';
            }
    		if ($plan_id!='') {
                $sql .= $prefix . " Plan.PlanID IN(" . escapeString($plan_id) . ")";
                $prefix = ' AND ';
            }
    		if ($is_active!='') {
                $sql .= $prefix . " Plan.IsActive=" . (int)$is_active;
                $prefix = ' AND ';
            }
    		if ($status!='') {
//debug("status=" . $status . "\r\n");
        		if ($status=='Hybrid') $sql .= $prefix . " (Plan.Status='In Production' OR Plan.Status='Approved' OR Plan.Status='Under Revision' OR Plan.Status='Revision Pending Approval' OR Plan.Status='Pending Approval')";
        		elseif ($status=='Hybrid2') $sql .= $prefix . " (Plan.Status='Approved' OR Plan.Status='Under Revision' OR Plan.Status='Revision Pending Approval' OR Plan.Status='Pending Approval')";
        		elseif ($status=='Pending') $sql .= $prefix . " (Plan.Status='Revision Pending Approval' OR Plan.Status='Pending Approval')";
                else $sql .= $prefix . " Plan.Status='" . escapeString($status) . "'";
                $prefix = ' AND ';
            }
    		// limit to matches based on user-supplied search text
    		if ($searchtext!='')  {
                $sql .= $prefix . " (Plan.PlanDisplayName LIKE '%" . addslashes($searchtext) . "%' OR Project.ProjectDisplayName LIKE '%" . addslashes($searchtext) . "%' OR Client.ClientDisplayName LIKE '%" . addslashes($searchtext) . "%')";
                $prefix = ' AND ';
    		}

    		$sql .= ' GROUP BY Plan.PlanID ';

            $hybrid_sort = "";
            if ($status=='Hybrid') $hybrid_sort = ",Plan.Status NOT LIKE('%Revis%') ";
            if ($sort!='') {
              $dir = "";
    		  if ($sortdir=="asc") $dir = " ASC "; // ascending sort (default)
    		  if ($sortdir=="desc") $dir = " DESC "; // descending sort
    		  if ($sort=='plan') $sql .= " ORDER BY PlanDisplayName " . $dir . " " . $hybrid_sort . ", ProjectDisplayName";
              elseif ($sort=='client') $sql .= " ORDER BY ClientDisplayName " . $dir . " " . $hybrid_sort . ", PlanDisplayName";
              elseif ($sort=='project') $sql .= " ORDER BY ProjectDisplayName " . $dir . " " . $hybrid_sort . ", PlanDisplayName";
    		  elseif ($sort=='is_active') $sql .= " ORDER BY Plan.IsActive " . $dir . " " . $hybrid_sort . ", PlanDisplayName";
    		  elseif ($sort=='revision') $sql .= " ORDER BY Plan.RevisionNumber " . $dir . " " . $hybrid_sort . ", PlanDisplayName";
    		  elseif ($sort=='status') $sql .= " ORDER BY Plan.Status " . $dir . " " . $hybrid_sort . ", PlanDisplayName";
    		  elseif ($sort=='days') $sql .= " ORDER BY DaysInStatus " . $dir . " " . $hybrid_sort . ", PlanDisplayName";
    		  elseif ($sort=='options') $sql .= " ORDER BY opt.OptionCount " . $dir . " " . $hybrid_sort . ", PlanDisplayName";
    		  elseif ($sort=='users') $sql .= " ORDER BY COUNT(UserType.UserTypeID)-SUM(COALESCE(UserType.IsProspect,0)) " . $dir . " " . $hybrid_sort . ", PlanDisplayName";
    		  elseif ($sort=='prospects') $sql .= " ORDER BY SUM(COALESCE(UserType.IsProspect,0)) " . $dir . " " . $hybrid_sort . ", PlanDisplayName";
    		  else $sql .= " ORDER BY PlanDisplayName,ProjectDisplayName " . $hybrid_sort; // default in case user passed an unknown sort field option
            } else $sql .= " ORDER BY PlanDisplayName,ProjectDisplayName " . $hybrid_sort;

            if ($operation=="count_plans") {
                $sql = "SELECT COUNT(*) AS plan_count FROM (" . $sql . ") tmp";
            } elseif ((int)$page > 0 || $pagesize > 0) {
                if ((int)$pagesize<=0) $pagesize = 50;
                $sql .= " LIMIT " . ((int)$page * (int)$pagesize) . "," . $pagesize;
            }
        }
debug("sql=" . $sql . "\r\n");
		if (DB::fetchAll($recs, $sql, array()) === 0) {
		    if ($operation=="count_plans") $recs = $recs[0];
		    strip_numeric_keys($recs);
//debug("recs=" . print_r($recs, TRUE) . "\r\n");
		    if ($format=='xml') echo xml_encode($recs);
            else echo json_encode($recs);
		}

// UPDATE PLAN OPTION PRICING - args: json array of BuilderPlanName, BuilderProjectName, PricingDelta, [optional: SquareFootageDelta]
// THIS IS NOT ACTUALLY USED AS THE UPDATE AND LOGGING PROGRAMMING OR MORE COMPLEX AND REPEATEDLY CALLING THE API IS TOO SLOW //
    } else if ($operation=="import_plan_option_pricing") {
        $db->beginTransaction();
        $recs = json_decode($json, true);
//debug("recs=" . print_r($recs,true) . "\r\n");
        $count=0;
        foreach($recs AS $rec) {
            if (array_key_exists('Price',$rec) || array_key_exists('SquareFeet',$rec)) { // don't bother if there's nothing to update
                $sql = 'UPDATE PlanOption
                        INNER JOIN PlanOptionGroup ON PlanOptionGroup.OptionGroupID=PlanOption.OptionGroupID
                        INNER JOIN Plan ON Plan.PlanID=PlanOptionGroup.PlanID AND Plan.PlanBuilderName=:PlanBuilderName
                        INNER JOIN Project ON Project.ProjectID=Plan.ProjectID AND Project.ProjectBuilderName=:ProjectBuilderName
                        INNER JOIN Client ON Client.ClientID=Project.ClientID';
                $prefix = ' SET ';
                if (array_key_exists('Price',$rec)) {
                    $sql .= $prefix . ' PlanOption.OptionPricingDelta=:OptionPricingDelta';
                    $prefix = ',';
                }
                if (array_key_exists('SquareFeet',$rec)) {
                    $sql .= $prefix . ' PlanOption.OptionSquareFootageDelta=:OptionSquareFootageDelta';
                    $prefix = ',';
                }
                $sql .= ' WHERE PlanOption.OptionBuilderName=:OptionBuilderName ';
                $prefix = ' AND ';
                // restrict to a specific client
        		if ($user['ClientID']!='') {
                    $sql .= $prefix . ' Project.ClientID IN(' . escapeString($user['ClientID']) . ')';
                    $prefix = ' AND ';
        		}
        		if ($user['PermsAccess']!='' || $user['UserTypeCode']=='client') {
                    $sql .= $prefix . ' Project.ProjectID IN(' . $user['PermsAccess'] . ')';
                    $prefix = ' AND ';
                }
//debug("sql=" . $sql . "\r\n");
            	$stmt = $db->prepare($sql);
            	$stmt->bindValue('OptionBuilderName', $rec['OptionNumber']);
            	$stmt->bindValue('PlanBuilderName', $rec['PlanNumber']);
            	$stmt->bindValue('ProjectBuilderName', $rec['CommunityNumber']);
            	$stmt->bindValue('OptionPricingDelta', $rec['Price']);
            	if (array_key_exists('SquareFeet',$rec)) $stmt->bindValue('OptionSquareFootageDelta', $rec['SquareFeet']);
                try {
                	$stmt->execute();
                	$count++;
                } catch (Exception $ex) {
                	$db->rollBack();
                	die('Error! Error updating option pricing data (1).');
                }
            }
        }
        $db->commit();

        // recalc completion rates
        $sql= "UPDATE Plan
               INNER JOIN Project ON Project.ProjectID=Plan.ProjectID
               INNER JOIN Client ON Client.ClientID=Project.ClientID
               INNER JOIN (
        	       SELECT PlanOptionGroup.PlanID, (SUM(IF(OptionPricingDelta IS NULL OR OptionPricingDelta=0.00, 0, 1)) * 100)/COUNT(OptionID) AS PricingCompletionRate
        	       FROM PlanOptionGroup
        	       INNER JOIN PlanOption ON PlanOption.OptionGroupID=PlanOptionGroup.OptionGroupID
        	       GROUP BY PlanOptionGroup.PlanID
        	     ) AS t ON t.PlanID=Plan.PlanID
               SET Plan.PlanOptionPricingCompletionRate = t.PricingCompletionRate";
        $prefix = ' WHERE ';
        // restrict to a specific client
		if ($user['ClientID']!='') {
            $sql .= $prefix . ' Project.ClientID IN(' . escapeString($user['ClientID']) . ')';
            $prefix = ' AND ';
		}
		if ($user['PermsAccess']!='' || $user['UserTypeCode']=='client') {
            $sql .= $prefix . ' Project.ProjectID IN(' . $user['PermsAccess'] . ')';
            $prefix = ' AND ';
        }
        try {
        	$stmt->execute();
        } catch (Exception $ex) {
        	die('Error! Error updating option pricing completion rates (1).');
        }

        $status = array("Status"=>  $count . " records imported");
	    if ($format=='xml') echo xml_encode($status);
        else echo json_encode($status);

// LIST SAVED PLANS - args: project_id, plan_id
    } else if ($operation=="list_savedplans" || $operation=="count_savedplans") {

		$sql = "SELECT Project.ProjectID
		              ,Plan.PlanID
		              ,CONCAT(CONVERT(`SavedPlan`.`SavedPlanURLBase` USING utf8),`SavedPlan`.`SavedPlanGUID`) AS SavedPlanURL
    	              ,Client.ClientDisplayName
    	              ,Client.ClientDomain
		              ,Project.ProjectDisplayName
		              ,Project.ProjectSuppressDisplayName
		              ,Plan.PlanDisplayName
                      ,SavedPlan.SavedPlanID
		              ,SavedPlan.SavedPlanCreateDate
		              ,SavedPlan.SavedPlanName
                      ,SavedPlan.SavedPlanIsTest
		              ,CONCAT(UserWeb.UserFirstName,' ',UserWeb.UserLastName) AS UserFullName
		              ,COALESCE(CONVERT(SavedPlan.`SavedPlanEmail-OLD` USING utf8),UserWeb.UserEmail) AS UserEmail
		              ,CONCAT(UserAdmin.UserFirstName,' ',UserAdmin.UserLastName) AS AdminFullName
                      ,UserWeb.UserFirstName
                      ,UserWeb.UserLastName
		              ,UserWeb.UserPhone
		              ,UserWeb.UserPhoneTypeID
                      ,UserPhoneType.UserPhoneTypeDisplayName
		        FROM Client
		        INNER JOIN Project ON Project.ClientID=Client.ClientID
		        INNER JOIN Plan ON Plan.ProjectID=Project.ProjectID
		        INNER JOIN SavedPlan ON SavedPlan.PlanID=Plan.PlanID
                LEFT JOIN User AS UserWeb ON UserWeb.UserID=SavedPlan.UserIDWeb
                LEFT JOIN UserPhoneType ON UserPhoneType.UserPhoneTypeID=UserWeb.UserPhoneTypeID
		        LEFT JOIN User AS UserAdmin ON UserAdmin.UserID=SavedPlan.UserIDAdmin";
		$prefix = ' WHERE ';

		if (($user['PermsAccess'])!='' || $user['UserTypeCode']=='client') {
            $sql .= $prefix . ' Project.ProjectID IN(' . $user['PermsAccess'] . ')';
            $prefix = ' AND ';
        }
		if ($project_id!='') {
		    $sql .= $prefix . " Project.ProjectID IN(" . escapeString($project_id) . ")";
    		$prefix = ' AND ';
        }
		if ($plan_id!='' && $plan_id!=0) {
		    $sql .= $prefix . " Plan.PlanID IN(" . escapeString($plan_id) . ")";
    		$prefix = ' AND ';
        }
        if ($plan_name!='') {
            $sql .= $prefix . " Plan.PlanDisplayName='" . trim(escapeString($plan_name)) . "'";
            $prefix = ' AND ';
        }
        if ($savedplan_id!='') {
            $sql .= $prefix . " SavedPlan.SavedPlanID IN(" . escapeString($savedplan_id) . ")";
            $prefix = ' AND ';
        }
        if ($start_date!='') {
		    $sql .= $prefix . " SavedPlan.SavedPlanCreateDate >= '" . escapeString($start_date) . "'";
    		$prefix = ' AND ';
        }
        if ($end_date!='') {
		    $sql .= $prefix . " SavedPlan.SavedPlanCreateDate <= '" . escapeString($end_date) . "'";
    		$prefix = ' AND ';
        }

		if ($user['ClientID']!='') { // selected may be a subset of allowed so have to do BOTH IN() tests, not either/or
		    $sql .= $prefix . " Client.ClientID IN (" . escapeString($user['ClientID']) . ")";
    		$prefix = ' AND ';
        }
        if ($client_id!='') { // selected may be a subset of allowed so have to do BOTH IN() tests, not either/or
            $sql .= $prefix . ' Client.ClientID IN(' . escapeString($client_id) . ')';
            $prefix = ' AND ';
		}
		if ($user['UserTypeCode'] != 'superadmin' && $user['UserTypeCode'] != 'admin' && $user['UserTypeCode'] != 'manager' &&  $user['PermsAccess']!='') {
            $sql .= $prefix . ' (Project.ProjectID IN (' . escapeString($user['PermsAccess']) . ')) ';
            $prefix = ' AND ';
		}
		// limit plans based on filter keywords
        if ($filter!='') {
            if ($filter=='my' && array_key_exists('User_UserID',$_SESSION)) {
                $sql .= $prefix . ' UserWeb.UserID=' . (int)$_SESSION['User_UserID'];
                $prefix = ' AND ';
            } elseif ($filter=='prospect') {
                $sql .= $prefix . ' UserAdmin.UserID IS NULL';
                $prefix = ' AND ';
            } elseif ($filter=='user') {
                $sql .= $prefix . ' UserAdmin.UserID IS NOT NULL';
                $prefix = ' AND ';
            }
        }

        // limit to matches based on user-supplied search text
		if ($searchtext!='')  {
            $sql .= $prefix . " (
                                (Client.ClientDisplayName LIKE '%" . addslashes($searchtext) . "%')
                                OR (Project.ProjectDisplayName LIKE '%" . addslashes($searchtext) . "%')
                                OR (Plan.PlanDisplayName LIKE '%" . addslashes($searchtext) . "%')
                                OR (SavedPlan.SavedPlanName LIKE '%" . addslashes($searchtext) . "%')
                                OR (CONCAT(UserWeb.UserFirstName,' ',UserWeb.UserLastName) LIKE '%" . addslashes($searchtext) . "%')
                                OR (COALESCE(CONVERT(SavedPlan.`SavedPlanEmail-OLD` USING utf8),UserWeb.UserEmail) LIKE _utf8 '%" . addslashes($searchtext) . "%' COLLATE utf8_bin)
                                OR (CONCAT(UserAdmin.UserFirstName,' ',UserAdmin.UserLastName) LIKE '%" . addslashes($searchtext) . "%')
                                OR (UserWeb.UserPhone LIKE '%" . addslashes($searchtext) . "%')
                                ) ";
            $prefix = ' AND ';
        }

        // user-requested sort
		if ($sort!='') {
            if ($sort=='client') $sql .= " ORDER BY Client.ClientDisplayName";
            elseif ($sort=='project') $sql .= " ORDER BY Project.ProjectDisplayName";
            elseif ($sort=='plan') $sql .= " ORDER BY Plan.PlanDisplayName";
            elseif ($sort=='date') $sql .= " ORDER BY SavedPlan.SavedPlanCreateDate";
            elseif ($sort=='savename') $sql .= " ORDER BY SavedPlan.SavedPlanName";
            elseif ($sort=='prospectname') $sql .= " ORDER BY CONCAT(UserWeb.UserFirstName,' ',UserWeb.UserLastName)";
            elseif ($sort=='email') $sql .= " ORDER BY COALESCE(CONVERT(SavedPlan.`SavedPlanEmail-OLD` USING utf8),UserWeb.UserEmail)";
            elseif ($sort=='staffname') $sql .= " ORDER BY CONCAT(UserAdmin.UserFirstName,' ',UserAdmin.UserLastName)";
            elseif ($sort=='phone') $sql .= " ORDER BY UserWeb.UserPhone";
            elseif ($sort=='phonetype') $sql .= " ORDER BY UserWeb.UserPhoneTypeID";
            else $sql .= " ORDER BY SavedPlan.SavedPlanCreateDate DESC";
            if ($sortdir=="asc") $sql .= " ASC "; // ascending sort (default)
            if ($sortdir=="desc") $sql .= " DESC "; // descending sort
        } else $sql .= " ORDER BY SavedPlan.SavedPlanCreateDate DESC";


        if ($operation=="count_savedplans") {
            $sql = "SELECT COUNT(*) AS savedplan_count FROM (" . $sql . ") tmp";
        } elseif ((int)$page > 0 || $pagesize > 0) {
            if ((int)$pagesize<=0) $pagesize = 50;
            $sql .= " LIMIT " . ((int)$page * (int)$pagesize) . "," . $pagesize;
        }
debug("sql=" . $sql . "\r\n");
		if (DB::fetchAll($recs, $sql, array()) === 0) {
		    if ($operation=="count_savedplans") $recs = $recs[0];
		    strip_numeric_keys($recs);
		    if ($format=='xml') echo xml_encode($recs);
            else echo json_encode($recs);
		}

// LIST ENGAGEMENT - args: client_id, project_id, plan_id, start_date, end_date
    } elseif ($operation=="list_engaged_users" || $operation=="count_engaged_users" || $operation=="engagement_detail") {

        $where = "";
		$prefix = ' WHERE ';

		if (($user['PermsAccess'])!='' || $user['UserTypeCode']=='client') {
            $where .= $prefix . ' Project.ProjectID IN(' . $user['PermsAccess'] . ')';
            $prefix = ' AND ';
        }
		if ($project_id!='') {
		    $where .= $prefix . " Project.ProjectID IN(" . escapeString($project_id) . ")";
    		$prefix = ' AND ';
        }
		if ($plan_id!='') {
		    $where .= $prefix . " Plan.PlanID IN(" . escapeString($plan_id) . ")";
    		$prefix = ' AND ';
        }

        /*
        if ($start_date!='') {
		    $sql .= $prefix . " SavedPlan.SavedPlanCreateDate >= '" . escapeString($start_date) . "'";
    		$prefix = ' AND ';
        }
        if ($end_date!='') {
		    $sql .= $prefix . " SavedPlan.SavedPlanCreateDate <= '" . escapeString($end_date) . "'";
    		$prefix = ' AND ';
        }
        */
		if ($user['ClientID']!='') { // selected may be a subset of allowed so have to do BOTH IN() tests, not either/or
		    $where .= $prefix . " Client.ClientID IN (" . escapeString($user['ClientID']) . ")";
    		$prefix = ' AND ';
        }
        if ($client_id!='') { // selected may be a subset of allowed so have to do BOTH IN() tests, not either/or
            $where .= $prefix . ' Client.ClientID IN(' . escapeString($client_id) . ')';
            $prefix = ' AND ';
		}
		if ($user['UserTypeCode'] != 'superadmin' && $user['UserTypeCode'] != 'admin' && $user['UserTypeCode'] != 'manager' &&  $user['PermsAccess']!='') {
            $where .= $prefix . ' (Project.ProjectID IN (' . escapeString($user['PermsAccess']) . ')) ';
            $prefix = ' AND ';
		}

        // used by both listing and detail queries
        $heatscore_saves = " , 0 AS HeatScore ";
        $heatscore_views = " , 0 AS HeatScore ";
        if ((int)$heat_maxdays>0 && (int)$heat_saveweight>0) {
            $heatscore_saves = " , MAX(GREATEST(" . $heat_maxdays . "-DATEDIFF(NOW(),SavedPlan.SavedPlanCreateDate),0)*" . $heat_saveweight . ") AS HeatScore ";
            $heatscore_views = " , MAX(GREATEST(" . $heat_maxdays . "-DATEDIFF(NOW(),SoftSavedPlan.SavedPlanCreateDate),0)) AS HeatScore ";
        }

        if ($operation=="engagement_detail") {
            $user_guid = '';
            $sql = "SELECT SavedPlan.UserIDWeb,SavedPlanUserGUID
							    FROM SavedPlan
							    WHERE SavedPlanUserGUID<>'' AND UserIDWeb = " . (int)$user_id . "
							    GROUP BY SavedPlanUserGUID";
            $user_guid_where = "";
            $myprefix = "";
            // accumulate in a where/on clause for SQL that can cover multiple GUIDs for same user (it happens!)
		    if (DB::fetchAll($recs, $sql, array()) === 0) {
		        strip_numeric_keys($recs);
                foreach($recs AS $rec) {
                    $user_guid_where .= $myprefix . "SavedPlanUserGUID='" . $rec['SavedPlanUserGUID'] . "'"; // accumulate in where/on clause
                    $myprefix = " OR ";
                    /*
                    // temp to fix long running query
                    $user_guid = rec['SavedPlanUserGUID'];
                    $user_guid_where = "SavedPlanUserGUID='" . $rec['SavedPlanUserGUID'] . "'";
                    break;
                    */
                }
		    }
		    $sql = "SELECT UserWeb.UserID
                          ,COALESCE(CONVERT(Activity.`SavedPlanEmail-OLD` USING utf8),UserWeb.UserEmail) AS UserEmail
                          ,UserWeb.UserFirstName
                          ,UserWeb.UserLastName
		                  ,CONCAT(UserWeb.UserFirstName,' ',UserWeb.UserLastName) AS UserFullName
		                  ,UserWeb.UserPhone
		                  ,UserWeb.UserPhoneTypeID
                          ,Activity.*
                          ,CONCAT(CONVERT(`Activity`.`SavedPlanURLBase` USING utf8),`Activity`.`SavedPlanGUID`) AS SavedPlanURL
                          ,Plan.PlanDisplayName, Project.ProjectDisplayName, Client.ClientDisplayName
		            FROM User AS UserWeb
		            INNER JOIN (SELECT SavedPlan.*, 'Save' AS ActivityType
                                " . $heatscore_saves . "
							    FROM SavedPlan ";
                    if ($user_guid_where!="") {
                        $sql .= " WHERE SavedPlanUserGUID<>'' AND UserIDWeb = " . (int)$user_id . "
                                  GROUP BY SavedPlanID
                                UNION
                                SELECT SoftSavedPlan.*, 'View' AS ActivityType
                                " . $heatscore_views . "
							    FROM SoftSavedPlan
							    WHERE SavedPlanUserGUID<>'' AND (" . $user_guid_where . ")
                                GROUP BY SavedPlanID ) AS Activity ON (" . str_replace('SavedPlanUserGUID', 'Activity.SavedPlanUserGUID', $user_guid_where) . ")";
                    } else {
                        $sql .= " WHERE UserIDWeb = " . (int)$user_id . "
                                  GROUP BY SavedPlanID  ) AS Activity ON Activity.UserIDWeb=UserWeb.UserID";
                    }
                    $sql .=" INNER JOIN Plan on Activity.PlanID=Plan.PlanID
                             INNER JOIN Project ON Plan.ProjectID=Project.ProjectID
                             INNER JOIN Client ON Project.ClientID=Client.ClientID ";
		    if ($user_id!='') {
                $where .= $prefix . ' UserWeb.UserID = ' . (int)$user_id . ' ';
    		    $prefix = ' AND ';
		    }
            // limit to matches based on user-supplied search text
            $sql .= $where . " ORDER BY Activity.SavedPlanCreateDate DESC";

        } else {
		    $sql = "SELECT UserWeb.UserID
                          ,COALESCE(CONVERT(PlanSaves.`SavedPlanEmail-OLD` USING utf8),UserWeb.UserEmail) AS UserEmail
                          ,UserWeb.UserFirstName
                          ,UserWeb.UserLastName
		                  ,CONCAT(UserWeb.UserFirstName,' ',UserWeb.UserLastName) AS UserFullName
		                  ,UserWeb.UserPhone
		                  ,UserWeb.UserPhoneTypeID
                          ,SaveCount AS SavedPlanCount
		                  ,LatestSave AS LatestSavedPlan
                          ,PlanViews.ViewCount
		                  ,MAX(PlanViews.LatestView) LatestView
                          ,SUM(PlanSaves.HeatScore)+SUM(COALESCE(PlanViews.HeatScore,0)) AS HeatScore
                          ,ROUND((SUM(PlanSaves.HeatScore)+SUM(COALESCE(PlanViews.HeatScore,0)))/" . (int)($heat_ceiling/5) . ",0) AS HeatMeter
		            FROM User AS UserWeb
		            INNER JOIN (SELECT UserIDWeb,`SavedPlanEmail-OLD`, SUM(SaveCount) AS SaveCount, MAX(LatestSave) AS LatestSave, SUM(HeatScore) AS HeatScore, PlanID
	                                FROM (SELECT SavedPlan.UserIDWeb,SavedPlan.`SavedPlanEmail-OLD`,SavedPlanUserGUID, COUNT(SavedPlanID) AS SaveCount,MAX(SavedPlan.SavedPlanCreateDate) AS LatestSave, Plan.PlanID
                                            " . $heatscore_saves . "
							                FROM SavedPlan
                                            INNER JOIN Plan on SavedPlan.PlanID=Plan.PlanID
		                                    INNER JOIN Project ON Plan.ProjectID=Project.ProjectID
                                            INNER JOIN Client ON Project.ClientID=Client.ClientID
							                WHERE " . str_replace(" WHERE ", " ", $where) . "
							                GROUP BY SavedPlan.UserIDWeb, Plan.PlanID, DATE_FORMAT(SavedPlanCreateDate,'%Y-%m-%d %H')) T1
                                    GROUP BY UserIDWeb) AS PlanSaves ON UserWeb.UserID=PlanSaves.UserIDWeb
		            LEFT JOIN (SELECT UserIDWeb,`SavedPlanEmail-OLD`, SUM(ViewCount) AS ViewCount, MAX(LatestView) AS Latestview, SUM(HeatScore) AS HeatScore, PlanID
	                                FROM (SELECT SavedPlan.UserIDWeb,SavedPlan.`SavedPlanEmail-OLD`,FLOOR(COUNT(SoftSavedPlan.SavedPlanID)/COUNT(SavedPlan.SavedPlanID)) AS ViewCount, MAX(SoftSavedPlan.SavedPlanCreateDate) AS LatestView, Plan.PlanID
                                            " . $heatscore_views . "
							                FROM SavedPlan
							                INNER JOIN SoftSavedPlan ON SoftSavedPlan.SavedPlanUserGUID=SavedPlan.SavedPlanUserGUID
                                            INNER JOIN Plan on SoftSavedPlan.PlanID=Plan.PlanID
		                                    INNER JOIN Project ON Plan.ProjectID=Project.ProjectID
                                            INNER JOIN Client ON Project.ClientID=Client.ClientID
							                WHERE SavedPlan.SavedPlanUserGUID<>'' AND SoftSavedPlan.SavedPlanUserGUID<>'' " . str_replace(" WHERE ", " AND ", $where) . "
							                GROUP BY SavedPlan.UserIDWeb, Plan.PlanID, DATE_FORMAT(SoftSavedPlan.SavedPlanCreateDate,'%Y-%m-%d %H')) T2
                                    GROUP BY UserIDWeb) AS PlanViews ON UserWeb.UserID=PlanViews.UserIDWeb
		            INNER JOIN Plan ON PlanSaves.PlanID=Plan.PlanID
		            INNER JOIN Project ON Plan.ProjectID=Project.ProjectID
                    INNER JOIN Client ON Project.ClientID=Client.ClientID ";

		    if ($user_id!='') {
                $where .= $prefix . ' UserWeb.UserID = ' . (int)$user_id . ' ';
    		    $prefix = ' AND ';
		    }
            // limit to matches based on user-supplied search text
            $sql .= $where;
		    if ($searchtext!='')  {
                $sql .= $prefix . " (
                                    OR (CONCAT(UserWeb.UserFirstName,' ',UserWeb.UserLastName) LIKE '%" . addslashes($searchtext) . "%')
                                    OR (COALESCE(CONVERT(PlanSaves.`SavedPlanEmail-OLD` USING utf8),UserWeb.UserEmail) LIKE _utf8 '%" . addslashes($searchtext) . "%' COLLATE utf8_bin)
                                    OR (UserWeb.UserPhone LIKE '%" . addslashes($searchtext) . "%')
                                    ) ";
                $prefix = ' AND ';
            }

            $sql .= " GROUP BY UserEmail ";

            // user-requested sort
		    if ($sort!='') {
                if ($sort=='heat_meter') $sql .= " ORDER BY HeatMeter";
                elseif ($sort=='client') $sql .= " ORDER BY Client.ClientDisplayName";
                elseif ($sort=='project') $sql .= " ORDER BY Project.ProjectDisplayName";
                elseif ($sort=='plan') $sql .= " ORDER BY Plan.PlanDisplayName";
    //            elseif ($sort=='date') $sql .= " ORDER BY SavedPlan.SavedPlanCreateDate";
                elseif ($sort=='date') $sql .= " ORDER BY LatestSave";
                elseif ($sort=='name') $sql .= " ORDER BY UserFullName";
                elseif ($sort=='email') $sql .= " ORDER BY UserEmail";
                elseif ($sort=='phone') $sql .= " ORDER BY UserWeb.UserPhone";
                elseif ($sort=='phonetype') $sql .= " ORDER BY UserWeb.UserPhoneTypeID";
                elseif ($sort=='save_count') $sql .= " ORDER BY SavedPlanCount";
                elseif ($sort=='latest_save') $sql .= " ORDER BY LatestSavedPlan";
                elseif ($sort=='view_count') $sql .= " ORDER BY ViewCount";
                elseif ($sort=='latest_view') $sql .= " ORDER BY LatestView";
                elseif ($sort=='heat_score') $sql .= " ORDER BY HeatScore";
                else $sql .= " ORDER BY HeatMeter";
                if ($sortdir=="desc" || $sortdir=='') $sql .= " DESC "; // descending sort (default)
                if ($sortdir=="asc") $sql .= " ASC "; // ascending sort
            } else $sql .= " ORDER BY HeatMeter DESC";
            // tack on secondary sort for heat score/meter
            if (strpos($sql,"ORDER BY HeatScore")!==FALSE || strpos($sql,"ORDER BY HeatMeter")!==FALSE) {
                $sql .= ", LatestSavedPlan";
                if ($sortdir=="desc" || $sortdir=='') $sql .= " DESC "; // descending sort (default)
                if ($sortdir=="asc") $sql .= " ASC "; // ascending sort
            }
        }


        if ($operation=="count_engaged_users") {
            $sql = "SELECT COUNT(*) AS engaged_user_count FROM (" . $sql . ") tmp";
        } elseif ((int)$page > 0 || $pagesize > 0) {
            if ((int)$pagesize<=0) $pagesize = 50;
            $sql .= " LIMIT " . ((int)$page * (int)$pagesize) . "," . $pagesize;
        }
debug("sql=" . $sql . "\r\n");
		if (DB::fetchAll($recs, $sql, array()) === 0) {
		    if ($operation=="count_engaged_users") $recs = $recs[0];
		    strip_numeric_keys($recs);
		    if ($format=='xml') echo xml_encode($recs);
            else echo json_encode($recs);
		}

// LIST SITE PLANS - args: project_id
    } else if ($operation=="list_siteplans" || $operation=="count_siteplans") {

      $sql = 'SELECT GROUP_CONCAT(DISTINCT Project.ProjectID) AS ProjectID, GROUP_CONCAT(DISTINCT Project.ProjectDisplayName SEPARATOR ", ") AS ProjectDisplayName, Project.ProjectSuppressDisplayName, Client.ClientID, Client.ClientDisplayName, Client.ClientDomain, Site.SiteID, Site.SiteDisplayName, Site.SiteIsPrivate, Site.IsActive, Site.Status, URL.URL AS SiteBaseURL, IF(FirstSite.WhenCreated<Site.WhenCreated,"Billable","Non-Billable Legacy") BillableStatus
                     , opt.OptionCount, opt.CurrentlyAvailableCount, opt.FutureAvailableCount, opt.UnavailableCount, opt.ViewCount, LegendClientID
               FROM Site
               INNER JOIN Site AS FirstSite ON FirstSite.SiteID=1
               LEFT JOIN URL ON URL.SiteID=Site.SiteID
               INNER JOIN Project ON FIND_IN_SET(Project.ProjectID,Site.ProjectID)>0 AND Project.ProjectActive=1
               LEFT JOIN Client ON Project.ClientID=Client.ClientID
               LEFT JOIN (
                    SELECT SiteView.SiteID
                        , GROUP_CONCAT(DISTINCT ClientID) AS LegendClientID
                        , COUNT(DISTINCT OptionID) AS OptionCount
                        , SUM(IF(ItemAvailability=\'Current\',1,0)) AS CurrentlyAvailableCount
                        , SUM(IF(ItemAvailability=\'Future\',1,0)) AS FutureAvailableCount
                        , SUM(IF(ItemAvailability=\'None\',1,0)) AS UnavailableCount
                        , COUNT(DISTINCT SiteView.ViewID) AS ViewCount
                    FROM  SiteView
                    LEFT JOIN ViewLegend ON SiteView.LegendID=ViewLegend.LegendID
                    LEFT JOIN SiteOptionGroup ON SiteOptionGroup.ViewID=SiteView.ViewID
                    LEFT JOIN SiteOption ON SiteOption.OptionGroupID=SiteOptionGroup.OptionGroupID
                    LEFT JOIN ViewLegendItem ON SiteOption.LegendItemID=ViewLegendItem.ItemID
                    GROUP BY  SiteView.SiteID
               ) AS opt ON opt.SiteID=Site.SiteID ';
        // limit to ClientID to limit ProjectID hacks
        $prefix = ' WHERE ';
      if ($user['ClientID']!='') {
            $sql .= $prefix . ' Project.ClientID IN(' . escapeString($user['ClientID']) . ')';
            $prefix = ' AND ';
      }
      if ($client_id!='' && (int)$client_id>0) {
            $sql .= $prefix . ' Project.ClientID IN(' . escapeString($client_id) . ')';
            $prefix = ' AND ';
      }
      if ($user['PermsAccess']!='' || $user['UserTypeCode']=='client') {
            $sql .= $prefix . ' Project.ProjectID IN(' . $user['PermsAccess'] . ')';
            $prefix = ' AND ';
        }
      if ($project_id!='' && (int)$project_id>0) {
            $sql .= $prefix . " Project.ProjectID IN(" . escapeString($project_id) . ")";
            $prefix = ' AND ';
        }
      if ($site_id!='' && (int)$site_id>0) {
            $sql .= $prefix . " Site.SiteID IN(" . escapeString($site_id) . ")";
            $prefix = ' AND ';
        }
      if ($status!='') {
            $sql .= $prefix . " Site.Status='" . $status . "'";
            $prefix = ' AND ';
        }
      if ($is_active!='' && $is_active!='-1') {
            $sql .= $prefix . " Site.IsActive=" . (int)$is_active;
            $prefix = ' AND ';
        }

      // limit to matches based on user-supplied search text
      if ($searchtext!='')  {
            $sql .= $prefix . " (Site.SiteDisplayName LIKE '%" . addslashes($searchtext) . "%' OR Client.ClientDisplayName LIKE '%" . addslashes($searchtext) . "%' OR Project.ProjectDisplayName LIKE '%" . addslashes($searchtext) . "%')";
            $prefix = ' AND ';
      }

      $sql .= ' GROUP BY Site.SiteID ';

      if ($sort!='') {
        if ($sort=='client') $sql .= " ORDER BY ClientDisplayName";
        elseif ($sort=='project') $sql .= " ORDER BY ProjectDisplayName";
        elseif ($sort=='site') $sql .= " ORDER BY SiteDisplayName";
        elseif ($sort=='options') $sql .= " ORDER BY opt.OptionCount";
        elseif ($sort=='currentlyavailable') $sql .= " ORDER BY opt.CurrentlyAvailableCount";
        elseif ($sort=='futureavailable') $sql .= " ORDER BY opt.FutureAvailableCount";
        elseif ($sort=='unavailable') $sql .= " ORDER BY opt.UnavailableCount";
        elseif ($sort=='options') $sql .= " ORDER BY opt.OptionCount";
        elseif ($sort=='options') $sql .= " ORDER BY opt.OptionCount";
        elseif ($sort=='views') $sql .= " ORDER BY opt.ViewCount";
        else $sql .= " ORDER BY ProjectDisplayName "; // default in case user passed an unknown sort field option
        if ($sortdir=="asc") $sql .= " ASC "; // ascending sort (default)
        if ($sortdir=="desc") $sql .= " DESC "; // descending sort
      } else $sql .= " ORDER BY ProjectDisplayName ";

        if ($operation=="count_siteplans") {
            $sql = "SELECT COUNT(*) AS siteplan_count FROM (" . $sql . ") tmp";
        } elseif ((int)$page > 0 || $pagesize > 0) {
            if ((int)$pagesize<=0) $pagesize = 50;
            $sql .= " LIMIT " . ((int)$page * (int)$pagesize) . "," . $pagesize;
        }
debug("sql=" . $sql . "\r\n");
debug("user=" . print_r($user, TRUE) . "\r\n");

        if (DB::fetchAll($recs, $sql, array()) === 0) {
          if ($operation=="count_siteplans") $recs = $recs[0];
          strip_numeric_keys($recs);
          if ($format=='xml') echo xml_encode($recs);
          else echo json_encode($recs);
        }

// LIST SITE PLAN OPTIONS - args: site_id
      } else if ($operation=="list_siteplanoptions" || $operation=="count_siteplanoptions") {

        $sql = 'SELECT SiteOption.OptionDisplayName AS OptionDisplayName
                     ,SiteOption.OptionTitle1 AS Subhead
                     ,SiteOption.OptionBuilderName AS BuilderLotID
                     ,ViewLegendItem.ItemDisplayName AS \'Status\'
                     ,COALESCE(GROUP_CONCAT(Plan.PlanDisplayName),GROUP_CONCAT(p1.PlanDisplayName)) AS AvailablePlans
                     ,p2.PlanDisplayName AS SelectedPlan
                     ,PlanOption.OptionDisplayName AS SelectedExterior
                     ,SiteOption.OptionSelectedOrientation AS SelectedOrientation
                     ,SiteOption.OptionLotPremium AS LotPremium
                     ,SiteOption.OptionSalesPrice AS SalesPrice
                     ,SiteOption.OptionText1 AS Notes
               FROM Site
               INNER JOIN SiteView ON SiteView.SiteID=Site.SiteID
               INNER JOIN SiteOptionGroup ON SiteOptionGroup.ViewID=SiteView.ViewID
               INNER JOIN SiteOption ON SiteOption.OptionGroupID=SiteOptionGroup.OptionGroupID
               LEFT JOIN ViewLegendItem ON SiteOption.LegendItemID=ViewLegendItem.ItemID
               LEFT JOIN Plan ON Plan.PlanID IN(SiteOption.OptionAvailablePlans)
               LEFT JOIN Plan p1 ON p1.isActive=1 AND p1.ProjectID=Site.ProjectID
               LEFT JOIN Plan p2 ON p2.PlanID=SiteOption.OptionSelectedPlanID
               LEFT JOIN PlanOption ON PlanOption.OptionID=SiteOption.OptionSelectedExteriorOptionID
               WHERE SiteOptionGroup.OptionGroupTypeID=1 ';
        // limit to ClientID to limit ProjectID hacks
      $prefix = ' AND ';
      if ($site_id!='' && (int)$site_id>0) {
            $sql .= $prefix . " Site.SiteID IN(" . escapeString($site_id) . ")";
            $prefix = ' AND ';
      }
      if ($is_active!='' && $is_active!='-1') {
            $sql .= $prefix . " Site.IsActive=" . (int)$is_active;
            $prefix = ' AND ';
      }

      // limit to matches based on user-supplied search text
      if ($searchtext!='')  {
            $sql .= $prefix . " (SiteOption.OptionDisplayName LIKE '%" . addslashes($searchtext) . "%')";
            $prefix = ' AND ';
      }


      $sql .= ' GROUP BY SiteOption.OptionID ';

      $sql .= " ORDER BY  SiteOption.OptionGroupID, SiteOption.OptionDisplayOrder ";

      if ($operation=="count_siteplanoptions") {
          $sql = "SELECT COUNT(*) AS siteplanoption_count FROM (" . $sql . ") tmp";
      } elseif ((int)$page > 0 || $pagesize > 0) {
          if ((int)$pagesize<=0) $pagesize = 50;
          $sql .= " LIMIT " . ((int)$page * (int)$pagesize) . "," . $pagesize;
      }
debug("sql=" . $sql . "\r\n");
//debug("user=" . print_r($user, TRUE) . "\r\n");

      if (DB::fetchAll($recs, $sql, array()) === 0) {
          if ($operation=="count_siteplanoptions") $recs = $recs[0];
          strip_numeric_keys($recs);
          if ($format=='xml') echo xml_encode($recs);
            else echo json_encode($recs);
      }

// LIST AREA MAPS - args: project_id
    } else if ($operation=="list_areamaps" || $operation=="count_areamaps") {
		$sql = 'SELECT Project.ProjectID, Project.ProjectDisplayName, Project.ProjectSuppressDisplayName, Client.ClientID, Client.ClientDisplayName, Client.ClientDomain, AreaMap.AreaMapID, AreaMap.MapName,URL.URL AS AreaMapBaseURL, AreaMap.IsActive, AreaMap.Status, IF(FirstAreaMap.WhenCreated<AreaMap.WhenCreated,"Billable","Non-Billable Legacy") BillableStatus
                FROM AreaMap
                INNER JOIN AreaMap AS FirstAreaMap ON FirstAreaMap.AreaMapID=1
	            LEFT JOIN URL ON URL.AreaMapID=AreaMap.AreaMapID
	            LEFT JOIN Project ON AreaMap.ProjectID=Project.ProjectID
	            LEFT JOIN Client ON Project.ClientID=Client.ClientID';
        // limit to ClientID to limit ProjectID hacks
        $prefix = ' WHERE ';
		if ($user['ClientID']!='') {
            $sql .= $prefix . ' Project.ClientID IN(' . escapeString($user['ClientID']) . ')';
            $prefix = ' AND ';
		}
		if ($client_id!='' && (int)$client_id>0) {
            $sql .= $prefix . ' Project.ClientID IN(' . escapeString($client_id) . ')';
            $prefix = ' AND ';
		}
		if ($user['PermsAccess']!='' || $user['UserTypeCode']=='client') {
            $sql .= $prefix . ' Project.ProjectID IN(' . $user['PermsAccess'] . ')';
            $prefix = ' AND ';
        }
		if ($project_id!='' && (int)$project_id>0) {
            $sql .= $prefix . " Project.ProjectID IN(" . escapeString($project_id) . ")";
            $prefix = ' AND ';
        }
		if ($areamap_id!='' && (int)$areamap_id>0) {
            $sql .= $prefix . " AreaMap.AreaMapID IN(" . escapeString($areamap_id) . ")";
            $prefix = ' AND ';
        }
        if ($status!='') {
            $sql .= $prefix . " AreaMap.Status='" . $status . "'";
            $prefix = ' AND ';
        }

		// limit to matches based on user-supplied search text
		if ($searchtext!='')  {
            $sql .= $prefix . " (AreaMap.MapName LIKE '%" . addslashes($searchtext) . "%')";
            $prefix = ' AND ';
		}

		$sql .= ' GROUP BY AreaMap.AreaMapID ';

        if ($sort!='') {
          if ($sort=='client') $sql .= " ORDER BY Client.ClientDisplayName";
          elseif ($sort=='project') $sql .= " ORDER BY Project.ProjectDisplayName";
		  elseif ($sort=='areamap') $sql .= " ORDER BY MapName";
		  else $sql .= " ORDER BY Client.ClientDisplayName,Project.ProjectDisplayName, MapName "; // default in case user passed an unknown sort field option
		  if ($sortdir=="asc") $sql .= " ASC "; // ascending sort (default)
		  if ($sortdir=="desc") $sql .= " DESC "; // descending sort
        } else $sql .= " ORDER BY MapName ";

        if ($operation=="count_areamaps") {
            $sql = "SELECT COUNT(*) AS areamap_count FROM (" . $sql . ") tmp";
        } elseif ((int)$page > 0 || $pagesize > 0) {
            if ((int)$pagesize<=0) $pagesize = 50;
            $sql .= " LIMIT " . ((int)$page * (int)$pagesize) . "," . $pagesize;
        }
debug("sql=" . $sql . "\r\n");
//debug("user=" . print_r($user, TRUE) . "\r\n");

		if (DB::fetchAll($recs, $sql, array()) === 0) {
		    if ($operation=="count_areamaps") $recs = $recs[0];
		    strip_numeric_keys($recs);
debug("recs=" . print_r($recs, TRUE) . "\r\n");
		    if ($format=='xml') echo xml_encode($recs);
            else echo json_encode($recs);
		}

// LIST COMPONENT GROUPS - args: none
    } else if ($operation=="list_componentgroups" || $operation=="count_componentgroups") {

		$sql = 'SELECT ComponentGroup.ComponentGroupID, ComponentGroup.ComponentGroupDisplayName, ComponentGroup.ComponentGroupComment
		             , ComponentGroupType.ComponentGroupTypeDisplayName AS ComponentGroupTypeName, COUNT(Component.ComponentID) AS ComponentCount
		             , ComponentGroupSVGFile, Client.ClientDisplayName
                FROM ComponentGroup
	            LEFT JOIN ComponentGroupType ON ComponentGroup.ComponentGroupTypeID=ComponentGroupType.ComponentGroupTypeID
	            LEFT JOIN Client ON ComponentGroup.ClientID IS NOT NULL AND ComponentGroup.ClientID=Client.ClientID
                LEFT JOIN Component ON Component.ComponentGroupID=ComponentGroup.ComponentGroupID';
        $prefix = ' WHERE ';
		if ($user['ClientID']!='') {
            $sql .= $prefix . ' ComponentGroup.ClientID IN(' . escapeString($user['ClientID']) . ')';
            $prefix = ' AND ';
		}
		if ($client_id!='' && (int)$client_id>0) {
            $sql .= $prefix . ' ComponentGroup.ClientID IN(' . escapeString($client_id) . ')';
            $prefix = ' AND ';
		}
		// limit to matches based on user-supplied search text
		if ($searchtext!='')  {
            $sql .= $prefix . "( (ComponentGroupDisplayName LIKE '%" . addslashes($searchtext) . "%')";
            $sql .= " OR (ComponentGroupComment LIKE '%" . addslashes($searchtext) . "%') )";
            $prefix = ' AND ';
		}

		$sql .= ' GROUP BY ComponentGroup.ComponentGroupID ';

        if ($sort!='') {
		  if ($sort=='ClientDisplayName') $sql .= " ORDER BY ClientDisplayName";
		  elseif ($sort=='ComponentGroupDisplayName') $sql .= " ORDER BY ComponentGroupDisplayName";
		  elseif ($sort=='ComponentGroupTypeName') $sql .= " ORDER BY ComponentGroupTypeName";
		  elseif ($sort=='ComponentGroupComment') $sql .= " ORDER BY ComponentGroupComment";
		  elseif ($sort=='ComponentCount') $sql .= " ORDER BY ComponentCount";
		  else $sql .= " ORDER BY ClientDisplayName,ComponentGroupDisplayName "; // default in case user passed an unknown sort field option
		  if ($sortdir=="asc") $sql .= " ASC "; // ascending sort (default)
		  if ($sortdir=="desc") $sql .= " DESC "; // descending sort
        } else $sql .= " ORDER BY ClientDisplayName,ComponentGroupDisplayName ";

        if ($operation=="count_componentgroups") {
            $sql = "SELECT COUNT(*) AS componentgroup_count FROM (" . $sql . ") tmp";
        } elseif ((int)$page > 0 || $pagesize > 0) {
            if ((int)$pagesize<=0) $pagesize = 50;
            $sql .= " LIMIT " . ((int)$page * (int)$pagesize) . "," . $pagesize;
        }
debug("sql=" . $sql . "\r\n");
//debug("user=" . print_r($user, TRUE) . "\r\n");

		if (DB::fetchAll($recs, $sql, array()) === 0) {
		    if ($operation=="count_componentgroups") $recs = $recs[0];
		    strip_numeric_keys($recs);
		    if ($format=='xml') echo xml_encode($recs);
            else echo json_encode($recs);
		}

// LIST PROMO TAGS - args: none
    } else if ($operation=="list_promotags" || $operation=="count_promotags") {

		$sql = 'SELECT PromoTagID, PromoTagDisplayName, PromoTag, PromoTagMinValue, PromoTagMaxValue
                FROM PromoTag ';
        $prefix = ' WHERE ';
		// limit to matches based on user-supplied search text
		if ($searchtext!='')  {
            $sql .= $prefix . "( (PromoTagDisplayName LIKE '%" . addslashes($searchtext) . "%')";
            $sql .= " OR (PromoTag LIKE '%" . addslashes($searchtext) . "%') )";
            $prefix = ' AND ';
		}

		$sql .= ' GROUP BY PromoTag.PromoTagID ';

        if ($sort!='') {
		  if ($sort=='PromoTagDisplayName') $sql .= " ORDER BY PromoTagDisplayName";
		  elseif ($sort=='PromoTag') $sql .= " ORDER BY PromoTag";
		  elseif ($sort=='PromoTagMinValue') $sql .= " ORDER BY PromoTagMinValue";
		  elseif ($sort=='PromoTagMaxValue') $sql .= " ORDER BY PromoTagMaxValue";
		  else $sql .= " ORDER BY PromoTagDisplayName "; // default in case user passed an unknown sort field option
		  if ($sortdir=="asc") $sql .= " ASC "; // ascending sort (default)
		  if ($sortdir=="desc") $sql .= " DESC "; // descending sort
        } else $sql .= " ORDER BY PromoTagDisplayName ";

        if ($operation=="count_promotags") {
            $sql = "SELECT COUNT(*) AS promotag_count FROM (" . $sql . ") tmp";
        } elseif ((int)$page > 0 || $pagesize > 0) {
            if ((int)$pagesize<=0) $pagesize = 50;
            $sql .= " LIMIT " . ((int)$page * (int)$pagesize) . "," . $pagesize;
        }
debug("sql=" . $sql . "\r\n");
//debug("user=" . print_r($user, TRUE) . "\r\n");

		if (DB::fetchAll($recs, $sql, array()) === 0) {
		    if ($operation=="count_promotags") $recs = $recs[0];
		    strip_numeric_keys($recs);
		    if ($format=='xml') echo xml_encode($recs);
            else echo json_encode($recs);
		}

// LIST USERTYPES
    } else if ($operation=="list_usertypes") {
		$sql = 'SELECT * FROM UserType ';
		$prefix = ' WHERE ';

		if ($user['UserTypeCode']!='superadmin' && $user['UserTypeCode']!='admin' && $user['UserTypeCode']!='manager') {
            $sql .= $prefix . ' UserTypeCode <> "superadmin" AND UserTypeCode <> "admin" AND UserTypeCode <> "manager" AND UserTypeCode <> "clientadmin" ';
            $prefix = ' AND ';
		}
		$sql .= " ORDER BY UserTypeID ";

        if ((int)$page > 0 || $pagesize > 0) {
            if ((int)$pagesize<=0) $pagesize = 50;
            $sql .= " LIMIT " . ((int)$page * (int)$pagesize) . "," . $pagesize;
        }

		if (DB::fetchAll($recs, $sql, array()) === 0) {
		    strip_numeric_keys($recs);
		    if ($format=='xml') echo xml_encode($recs);
            else echo json_encode($recs);
		}

// LIST USERS
    } else if ($operation=="list_users" || $operation=="count_users") {
//debug("op=" . $operation . "\r\n");
		$sql = 'SELECT User.*,UserType.UserTypeDisplayName,UserType.UserTypeCode
    	        , GROUP_CONCAT(DISTINCT ClientUser.ClientID) AS AssignedClients
		        , GROUP_CONCAT(DISTINCT Client.ClientDisplayName) AS AssignedClientNames
		        FROM User
	            LEFT JOIN UserType ON User.UserTypeID=UserType.UserTypeID
	            LEFT JOIN ClientUser ON User.UserID=ClientUser.UserID
	            LEFT JOIN SavedPlan ON SavedPlan.UserIDWeb=User.UserID
	            LEFT JOIN Plan ON Plan.PlanID=SavedPlan.PlanID
	            LEFT JOIN Project ON Project.ProjectID=Plan.ProjectID
	            LEFT JOIN Client ON (ClientUser.ClientID IS NOT NULL AND Client.ClientID=ClientUser.ClientID) OR (UserType.UserTypeCode="web" AND Project.ClientID IS NOT NULL AND Client.ClientID=Project.ClientID)
	            WHERE UserDeleted=0 AND User.UserTypeID>=' .$user['UserTypeID'];

        // limit to ClientID to prevent filter hacks
debug("user[ClientID]=" . $user['ClientID'] . "\r\n");
		if ($user['ClientID']!='' || ($user['UserTypeCode']!='admin' && $user['UserTypeCode']!='superadmin' && $user['UserTypeCode']!='manager')) {
            $sql .= ' AND ClientUser.ClientID IN(' . escapeString($user['ClientID']) . ')';
		}
debug("client_id=" . $client_id . "\r\n");
		if ($client_id!='') {
            $sql .= ' AND ClientUser.ClientID IN(' . escapeString($client_id) . ')';
		}
//debug("sql=" . $sql . "\r\n");
		if ($user['UserTypeCode']!='admin' && $user['UserTypeCode']!='superadmin' && $user['UserTypeCode']!='clientadmin' && $user['UserTypeCode']!='manager') {
            $sql .= ' AND User.UserID IN (' . escapeString($user['UserID']) . ') ';
		}
		if ($user_id!='') {
            $sql .= ' AND User.UserID IN (' . escapeString($user_id) . ') ';
		}
        if ((int)$filter!=0) {
            $sql .= ' AND User.UserTypeID = ' . (int)$filter;
        }
		if ($is_active=='1') {
            $sql .= " AND (UserActive IS NOT NULL AND UserActive>0) ";
        } elseif ($is_active=='0') {
            $sql .= " AND (UserActive IS NULL OR UserActive=0) ";
        }

        // only contrado users can see clientadmin users in listings - changed 7/13/2020 to allow clientadmins to SEE clientadmins, API caller will have to limit editting abilities
        if ($user['UserTypeCode']!='superadmin' && $user['UserTypeCode']!='admin' && $user['UserTypeCode']!='manager' && $user['UserTypeCode']!='clientadmin') $sql .= ' AND UserType.UserTypeCode <> "clientadmin"';

        // only platinum users can see web users in listings
        if ($user['ClientSubscriptionLevel']!='Platinum') $sql .= ' AND UserType.UserTypeCode <> "web"';

		// limit to matches based on user-supplied search text
		if ($searchtext!='')  {
            $sql .= " AND (
                (UserTypeDisplayName LIKE '%" . addslashes($searchtext) . "%')
                OR (UserFirstName LIKE '%" . addslashes($searchtext) . "%')
                OR (UserLastName LIKE '%" . addslashes($searchtext) . "%')
                OR (UserEmail LIKE '%" . addslashes($searchtext) . "%')
                OR (UserPhone LIKE '%" . addslashes($searchtext) . "%')
                OR (ClientDisplayName LIKE '%" . addslashes($searchtext) . "%')
            ) ";
		}

        $sql .= " GROUP BY User.UserID";

        if ($sort!='') {
            if ($sort=='usertype') $sql .= " ORDER BY UserTypeDisplayName";
            elseif ($sort=='first') $sql .= " ORDER BY UserFirstName";
            elseif ($sort=='last') $sql .= " ORDER BY UserLastName";
            elseif ($sort=='email') $sql .= " ORDER BY UserEmail";
            elseif ($sort=='password') $sql .= " ORDER BY UserPassword";
            elseif ($sort=='phone') $sql .= " ORDER BY UserPhone";
            elseif ($sort=='phonetype') $sql .= " ORDER BY UserPhoneTypeID";
            elseif ($sort=='client') $sql .= " ORDER BY ClientDisplayName";
            if ($sortdir=="asc") $sql .= " ASC "; // ascending sort (default)
            if ($sortdir=="desc") $sql .= " DESC "; // descending sort
        } else $sql .= " ORDER BY UserTypeDisplayName DESC, UserFirstName, UserLastName";

        if ($operation=="count_users") {
            $sql = "SELECT COUNT(*) AS user_count FROM (" . $sql . ") tmp";
        } elseif ((int)$page > 0 || $pagesize > 0) {
            if ((int)$pagesize<=0) $pagesize = 50;
            $sql .= " LIMIT " . ((int)$page * (int)$pagesize) . "," . $pagesize;
        }
debug("sql=" . $sql . "\r\n");

		if (DB::fetchAll($recs, $sql, array()) === 0) {
		    if ($operation=="count_users") $recs = $recs[0];
		    strip_numeric_keys($recs);
		    if ($format=='xml') echo xml_encode($recs);
            else echo json_encode($recs);
		}

// GET USER DETAIL
    } else if ($operation=="get_user") {

		if ($user_id=='' && $user_email=='') die('A valid user_id or user_email parameter is required.');

		$sql = 'SELECT User.*,UserType.UserTypeDisplayName,UserType.UserTypeCode
		        , GROUP_CONCAT(DISTINCT ClientUser.ClientID) AS AssignedClients
		        , GROUP_CONCAT(DISTINCT Client.ClientDisplayName) AS AssignedClientNames
		        , GROUP_CONCAT(ProjectID) AS PermsProjectID
		        , GROUP_CONCAT(IF(UserProjectPermissions.Access=1,UserProjectPermissions.ProjectID,NULL)) AS PermsAccess
		        , GROUP_CONCAT(IF(UserProjectPermissions.Notification=1,UserProjectPermissions.ProjectID,NULL)) AS PermsNotification
		        FROM User
	            LEFT JOIN UserProjectPermissions ON UserProjectPermissions.UserID=User.UserID
	            LEFT JOIN UserType ON User.UserTypeID=UserType.UserTypeID
	            LEFT JOIN ClientUser ON User.UserID=ClientUser.UserID
	            LEFT JOIN Client ON Client.ClientID=ClientUser.ClientID
                WHERE UserDeleted=0 ';
		if ($user_id!='') {
            $sql .= ' AND User.UserID IN (' . escapeString($user_id) . ')';
            // limit to ClientID to prevent filter hacks - but only if NOT searching by email to allow location of conflicting/dupe email addresses when creating new users
            // always allow universal access to web users
    		if ($user['ClientID']!='' || ($user['UserTypeCode']!='admin' && $user['UserTypeCode']!='superadmin' && $user['UserTypeCode']!='manager')) {
                $sql .= ' AND (Client.ClientID IS NULL OR Client.ClientID IN (' . escapeString($user['ClientID']) . '))';
    		}  elseif ($client_id!='') {
                $sql .= ' AND (Client.ClientID IS NULL OR Client.ClientID IN(' . escapeString($client_id) . '))';
    		}
        }/* redundant
		if ($user['UserTypeCode']!='admin' && $user['UserTypeCode']!='superadmin' && $user['UserTypeCode']!='manager' && $user['UserTypeCode']!='clientadmin') {
            $sql .= ' AND User.UserID IN (' . escapeString($user['UserID']) . ') ';
		}
		*/
		if ($user_email!='') {
            $sql .= " AND User.UserEmail LIKE '" . escapeString($user_email) . "'";
		}

        $sql .= " GROUP BY User.UserID ";
		$sql .= " ORDER BY UserTypeDisplayName ";

        if ((int)$page > 0 || $pagesize > 0) {
            if ((int)$pagesize<=0) $pagesize = 50;
            $sql .= " LIMIT " . ((int)$page * (int)$pagesize) . "," . $pagesize;
        }

debug("SQL=" . $sql . "\r\n");
		if (DB::fetchAll($recs, $sql, array()) === 0) {
		    strip_numeric_keys($recs);
		    // reorganize project permissions CSV into sub-array
		    foreach($recs AS &$rec) {
		      $pids = explode(',', $rec['PermsProjectID']);
		      $access = explode(',', $rec['PermsAccess']);
		      $notifications = explode(',', $rec['PermsNotification']);
		      if (count($pids)>0) {
		          $rec['ProjectPermissions'] = array();
    		      for ($index=0; $index<count($pids); $index++) {
    		          $rec['ProjectPermissions'][$index] = array();
    		          $rec['ProjectPermissions'][$index]['ProjectID'] = $pids[$index];
    		          $rec['ProjectPermissions'][$index]['Access'] = in_array($pids[$index],$access);
    		          $rec['ProjectPermissions'][$index]['Notification'] =  in_array($pids[$index],$notifications);
    		      }
		      }
		      // remove original permission CSVs
		      unset($rec['PermsProjectID']);
		      unset($rec['PermsAccess']);
		      unset($rec['PermsNotification']);
		    }

//debug("reformatted recs=" . print_r($recs,TRUE));
//debug("format=" . $format);
		    if ($format=='xml') $tmp = xml_encode($recs);
            else $tmp = json_encode($recs);
//debug("tmp=" . $tmp);

            echo $tmp;
		}

// UPDATE USER DETAIL / CREATE A NEW USER
    } else if ($operation=="create_user" || $operation=="update_user") {

        if ($operation!='create_user') {
    		if ($user_id=='') die('A valid user_id parameter is required.');

    		// first make sure the caller is even allowed to update the specified User
    		$sql = 'SELECT User.*,UserType.UserTypeDisplayName,UserType.UserTypeCode, Client.ClientDisplayName
    		        FROM User
    	            LEFT JOIN UserType ON User.UserTypeID=UserType.UserTypeID
    	            LEFT JOIN ClientUser ON User.UserID=ClientUser.UserID
    	            LEFT JOIN Client ON Client.ClientID=ClientUser.ClientID ';
    		$sql .= ' WHERE User.UserID = ' . (int)$user_id;

            // limit to ClientID to prevent filter hacks
    		if ($user['ClientID']!='' || ($user['UserTypeCode']!='admin' && $user['UserTypeCode']!='superadmin' && $user['UserTypeCode']!='manager')) {
                $sql .= ' AND (Client.ClientID IS NULL OR Client.ClientID IN(' . escapeString($user['ClientID']) . '))';
    		}
    		// regular client users can only update their own profiel
    		if ($user['UserTypeCode']!='admin' && $user['UserTypeCode']!='superadmin' && $user['UserTypeCode']!='clientadmin' && $user['UserTypeCode']!='manager') {
                $sql .= ' AND User.UserID IN (' . escapeString($user['UserID']) . ') ';
    		}
    		if (DB::fetchAll($recs, $sql, array())!==0 || !is_array($recs) || count($recs)<1 || $recs[0]['UserID']!=$user_id) die('You do not have permission to update the specified user.  This activity has been logged along with your IP address.');
        }

        // get all params
        $user_active = Request('user_active','');
        $user_caneditpeers= Request('user_caneditpeers','');
        $user_deleted = Request('user_deleted','0');
        $user_firstname = Request('user_firstname','');
        $user_lastname = Request('user_lastname','');
        $user_email = Request('user_email','');
        $user_option_import_emails = Request('option_import_emails','');
        $user_password = Request('user_password','');
        $user_type = Request('user_type','');
        $user_phone = Request('user_phone','');
        $user_phonetype = Request('user_phonetype','');
        $userperm_project = Request('userperm_project','');
        $userperm_access = Request('userperm_access','');
        $userperm_notification = Request('userperm_notification','');


        // validate and translate $user_type to a UserTypeID
        if (!is_null($user_type)) {
            $sql = "SELECT * FROM UserType WHERE UserTypeCode='" . addslashes($user_type) . "'";
            if (DB::fetchAll($usertypes, $sql, array())!==0 || $usertypes[0]['UserTypeCode']!=$user_type) die('Invalid user type specified.  This activity has been logged along with your IP address.');
            $user_typeid = $usertypes[0]['UserTypeID'];
        } else $user_typeid = NULL;

        // add surrounding quotes to email -OR- modify email address if user is being deleted to avoid future conflicts
        if (!is_null($user_deleted) && (int)$user_deleted==1 && ($user['UserTypeCode']=='clientadmin' || $user['UserTypeCode']=='admin' || $user['UserTypeCode']=='superadmin' || $user['UserTypeCode']=='manager')) {
            $user_email = $user_email . ' DELETED ' .date('Y-m-d H:i:s');
        }

        // build the SQL statement to insert/update a User record
        if ($operation=='create_user') $sql = "INSERT User SET ";
        elseif ($operation=='update_user') $sql = "UPDATE User SET ";
        $sql .= " UserEmail='" . addslashes($user_email) . "'";

        if (!is_null($user_firstname)) {
            $sql .= ", UserFirstName='" . escapeString($user_firstname) . "'";
        }
        if (!is_null($user_lastname)) {
            $sql .= ", UserLastName='" . escapeString($user_lastname) . "'";
        }
        if (!is_null($user_password)) {
            $sql .= ", UserPassword='" . addslashes(AdminUtil::vipEncrypt($user_password)) . "'";
        }
        if (!is_null($user_option_import_emails)) {
            $sql .= ", SendPlanOptionImportEmails=" . (int)$user_option_import_emails;
        }

        // if the caller is updating their own user record (i.e. profile), do not allow them to modify usertype
        if ($user['UserID']==$user_id) {
            // do nothing with usertype
            $user_typeid = $user['UserTypeID'];
        } elseif ($user['UserTypeCode']=='clientadmin' && $user_typeid=4) {
            // caller is a clientadmin updating another clientadmin - do nothing with usertype
        // if the caller is a contrado admin or superadmin user - they can specify the user type and clients, otherwise use defaults
        } elseif (!is_null($user_typeid) && ($user['UserTypeCode']=='admin' || $user['UserTypeCode']=='superadmin' || $user['UserTypeCode']=='manager')) {
            $sql .= ", UserTypeID=" . (int)$user_typeid;
        } elseif ($operation=='create_user') {
            if ($user['ClientID']=='' && $client_id=='') {
                $sql .= ", UserTypeID=8";  // if caller is not associated with a client and has not specified a client, default to web user
                $user_typeid = 8;
            } else {
                $sql .= ", UserTypeID=5"; // else default to client user
                $user_typeid = 5;
            }
        } else {
            // catch-all scenario
            $sql .= ", UserTypeID=8";  // if caller is not associated with a client and has not specified a client, default to web user
            $user_typeid = 8;
        }

debug("user_typeid=" . $user_typeid . ", user=<pre>" . print_r($user, TRUE) . "</pre>");
        // if the caller is a contrado admin or superadmin user - they can specify the user type and clients, otherwise use defaults
        if (is_null($user_typeid) || $user_typeid==8 || $user_deleted==1) {
            $sql .= ", UserActive=0, UserCanEditPeers=0"; // web users and deleted users are ALWAYS denied access to vip admin
        } elseif (!is_null($user_typeid) && ($user['UserTypeCode']=='clientadmin' || $user['UserTypeCode']=='admin' || $user['UserTypeCode']=='superadmin' || $user['UserTypeCode']=='manager')) {
            if ($user['UserTypeCode']=='admin' || $user['UserTypeCode']=='superadmin') {
                if ($user['UserID']!=$user_id) $sql .= ", UserCanEditPeers=" . (int)$user_caneditpeers;
            }
            if ($user['UserID']!=$user_id) $sql .= ", UserActive=" . (int)$user_active;
//debug("user_type=" . $user_type . "\r\n");
            // same conditions apply to user phone updates - EXCEPT user can update their own phone number!
            $sql .= ", UserPhone='" . $user_phone . "'";
            $sql .= ", UserPhoneTypeID='" . $user_phonetype . "'";
        }
        /* DUPE FIELD DEFINITION
        if ($operation=='create_user' || $user_type!='') {  // creating a new user or specified client user (possibly promoting web user)
            if ($user['ClientID']=='' && $client_id=='') $sql .= ", UserTypeID=8";  // if caller is not associated with a client and has not specified a client, default to web user
            else $sql .= ", UserTypeID=5"; // else default to client user
        }
        */

        if (!is_null($user_deleted) && ($user['UserTypeCode']=='clientadmin' || $user['UserTypeCode']=='admin' || $user['UserTypeCode']=='superadmin' || $user['UserTypeCode']=='manager')) {
            $sql .= ", UserDeleted=" . (int)$user_deleted;
        }

        if ($operation!='create_user') $sql .= " WHERE UserID=" . (int)$user_id;
        // insert/update the user rec
debug("SQL=" . $sql . "\r\n");
        try {
            $db->beginTransaction();
        	$stmt = $db->prepare($sql);
        	$stmt->execute();
        } catch (Exception $ex) {
        	$db->rollBack();
        	die('Error! Error saving user information (1).');
        }

        // get new $user_id if this was a user creation op
        if ($operation=="create_user") $user_id = $db->lastInsertId();

        // if client_id wasn't passed in and the vipadmin user is a clientadmin with only one client association - use the vipadmin users' client_id
        if ((int)$client_id==0 && $user['UserTypeCode']=='clientadmin') $client_id = (int)$user['ClientID'];

        // if the caller is an admin user or superadmin user - they can specify client_id(s), otherwise we use the caller's client_id
//debug("_REQUEST=" . print_r($_REQUEST,TRUE) . "\r\n");
//debug("user=" . print_r($user, TRUE) . "\r\n");
        if ($user['UserTypeCode']=='admin' || $user['UserTypeCode']=='superadmin' || $user['UserTypeCode']=='clientadmin' || $user['UserTypeCode']=='manager') {
            try {
                // first remove all existing ClientUser records - in case admin user has removed client association(s)
                if ($operation!="create_user") {
                    $sql = "DELETE FROM ClientUser WHERE UserID=" . (int)$user_id;
//debug("SQL=" . $sql . "\r\n");
                	$stmt = $db->prepare($sql);
                	$stmt->execute();
                }
                // now insert a ClientUser record for each selected ClientID (or for the callers selected ClientID if caller is a client admin)
                if ($client_id!='') {
                    $sql = "INSERT INTO ClientUser(UserID, ClientID) ";
                    $prefix = "VALUES";
                    $clients = explode(",",$client_id);
                    foreach($clients AS $client) {
                        $sql .= $prefix . "(" . (int)$user_id . "," . (int)$client . ")";
                        $prefix = ",";
                    }
//debug("SQL=" . $sql . "\r\n");
                	$stmt = $db->prepare($sql);
                	$stmt->execute();
                }
            } catch (Exception $ex) {
            	$db->rollBack();
            	die('Error! Error saving user information (2).');
            }
        }

        // add/update permission flags
        if ($userperm_project!='') $pids = explode(',', $userperm_project);
        else $pids = NULL;
//debug("pids=" . print_r($pids,TRUE) . "\r\n");
        if ($userperm_access) $access = explode(',', $userperm_access);
        else $access = NULL;
        if ($userperm_notification) $notifications = explode(',', $userperm_notification);
        else $notifications = NULL;
        if (is_array($pids) && count($pids)>0) {
            // zero out any existing community perms to start fresh
            try {
                $sql = "UPDATE UserProjectPermissions SET Access=0";
                if ($user['UserTypeCode']=='superadmin' || $user['UserTypeCode']=='admin' || $user['ClientSubscriptionLevel']=='Platinum' || $user['ClientSubscriptionLevel']=='Gold') $sql .= ", Notification=0";
                $sql .= " WHERE UserID=" . (int)$user_id;
//debug("SQL=" . $sql . "\r\n");
                $stmt = $db->prepare($sql);
                $stmt->execute();
            } catch (Exception $ex) {
            	$db->rollBack();
            	die('Error! Error saving user information (3).');
            }

            // get community list from DB
            //  - tells us which Projects have existing UserProjectPermissions for this user and which do not
            //  - insures the user didn't hack the html post data to add extra community IDs to which they shouldn't have access to (filtered in the WHERE clause below)

            $sql = 'SELECT Project.*, UserProjectPermissionsID
                    FROM Project
                    LEFT JOIN UserProjectPermissions ON UserProjectPermissions.ProjectID=Project.ProjectID AND UserProjectPermissions.UserID=' . (int)$user_id;
            $prefix = ' WHERE ';

            $params = array();

            // restrict to project the selected user could access by default
            if ($user['ClientID']!='') {
                $sql .= $prefix . ' ClientID IN (' . escapeString($user['ClientID']) . ')';
                $prefix = ' AND ';
            }
            if ($client_id!='') {
                $sql .= $prefix . ' ClientID IN (' . escapeString($client_id) . ')';
                $prefix = ' AND ';
            }

            $sql .= " ORDER BY ProjectDisplayName";
            $recCount = 0;
//debug("_REQUEST=" . print_r($_REQUEST,TRUE) . "\r\n");
//debug("access=" . print_r($access,TRUE) . "\r\n");
//debug("notification=" . print_r($notifications,TRUE) . "\r\n");

//debug("SQL=" . $sql . "\r\n");
        	if (DB::fetchAll($dbr, $sql, $params) === 0) {
//debug("dbr=" . print_r($dbr,TRUE) . "\r\n");

                foreach($dbr AS $rec) {
                    if (in_array($rec['ProjectID'],$pids)) {
                        $index = array_search($rec['ProjectID'],$pids);
                        if ($access[$index]+$notifications[$index] > 0) {
                            if (is_null($rec['UserProjectPermissionsID'])) {
                                // insert new UserProjectPermissions record
                                $sql = "INSERT INTO UserProjectPermissions";
                                $where = "";
                            } else {
                                // update existing UserProjectPermissions record
                                $sql = "UPDATE UserProjectPermissions";
                                $where = " WHERE UserProjectPermissionsID=" . (int)$rec['UserProjectPermissionsID'];
                            }
                            $sql .= " SET UserID=:UserID, ProjectID=:ProjectID, Access=:Access";
                            if ($user['UserTypeCode']=='superadmin' || $user['UserTypeCode']=='admin' || $user['ClientSubscriptionLevel']=='Platinum' || $user['ClientSubscriptionLevel']=='Gold') $sql .= ", Notification=:Notification";
                            $stmt = $db->prepare($sql . $where);
                            $stmt->bindValue(':UserID', (int)$user_id , PDO::PARAM_INT);
                            $stmt->bindValue(':ProjectID', (int)$rec['ProjectID'], PDO::PARAM_INT);
                            $stmt->bindValue(':Access', (int)$access[$index], PDO::PARAM_INT);
                            if ($user['UserTypeCode']=='superadmin' || $user['UserTypeCode']=='admin' || $user['ClientSubscriptionLevel']=='Platinum' || $user['ClientSubscriptionLevel']=='Gold') $stmt->bindValue(':Notification', (int)$notifications[$index], PDO::PARAM_INT);
$tmp = pdo_sql_debug($sql,array('ClientID'=>$client_id
    ,'UserID'=> (int)$user_id
    ,'ProjectID'=> (int)$rec['ProjectID']
    ,'Access'=> (int)$access[$index]
    ,'Notification'=> (int)$notifications[$index]
    ));
//debug("SQL=" . $tmp . "\r\n");

                            try {
                                $stmt->execute();
                            } catch (Exception $ex) {
                            	$db->rollBack();
                            	die('Error! Error saving user information (4).');
                            }
                        }
                    }
                }
            }
        }
        $db->commit();
        $status = array("Status"=>"OK");
	    if ($format=='xml') echo xml_encode($status);
        else echo json_encode($status);

    } else {

        die('Unknown operation requested');

    }
}

// helper function gets _REQUEST var if it exists, else default
function Request($fieldname, $default=NULL)
{
    if (array_key_exists($fieldname,$_REQUEST)) return $_REQUEST[$fieldname];
    else return $default;
}

// helper function strips out numeric field indexes from within each record
function strip_numeric_keys(&$recs)
{
    if (is_array($recs)) foreach($recs AS $count => $rec) {
        if (is_array($rec)) foreach($rec AS $k => $v) if (is_numeric($k)) unset($recs[$count][$k]);
    }
}

// helper function to generate xml string from array
function xml_encode($arr)
{
    // create instance of SimpleXMLElement
    $xml_result = new SimpleXMLElement("<?xml version=\"1.0\"?><root></root>");

    // convert array to xml
    array_to_xml($arr,$xml_result);

    // return generated xml as a string
    return $xml_result->asXML();
}

// function defination to convert array to xml
function array_to_xml($arr, &$xml_result) {
    foreach($arr as $key => $value) {
        if (is_array($value)) {
            if (!is_numeric($key)) {
                $subnode = $xml_result->addChild("$key");
                array_to_xml($value, $subnode);
            } else {
                $subnode = $xml_result->addChild("item$key");
                array_to_xml($value, $subnode);
            }
        } else {
            $xml_result->addChild("$key",htmlspecialchars("$value"));
        }
    }
}

// debug helper
function pdo_sql_debug($sql,$placeholders) {
    foreach($placeholders as $k => $v){
        $sql = preg_replace('/:'.$k.'/',"'".$v."'",$sql);
    }
    return $sql;
}


$escapedbh = NULL;

// DB lib replacement without deprecated mysql library calls.
function escapeString($theString, $host = NULL, $user = NULL, $password = NULL, $port=NULL)
{
    global $escapedbh;
    // this function is problematic with
    return addslashes($theString);

	if (!isset($host) && isset($_SESSION['DB_HOST'])) $host = $_SESSION['DB_HOST'];
	if (!isset($host) && defined('DB_HOST')) $host = DB_HOST;
	if (!isset($host)) throw new Exception('No database host defined.');

	if (!isset($user) && isset($_SESSION['DB_USER'])) $user = $_SESSION['DB_USER'];
	if (!isset($user) && defined('DB_USER')) $user = DB_USER;
	if (!isset($user)) throw new Exception('No database user defined.');

	if (!isset($password) && isset($_SESSION['DB_PASSWORD'])) $password = $_SESSION['DB_PASSWORD'];
	if (!isset($password) && defined('DB_PASSWORD')) $password = DB_PASSWORD;
	if (!isset($password)) throw new Exception('No database password defined.');

	if (!isset($port) && isset($_SESSION['DB_PORT'])) $port = $_SESSION['DB_PORT'];
	if (!isset($port) && defined('DB_PORT')) $port = DB_PORT;

    if (is_null($escapedbh)) $escapedbh = mysqli_connect($host, $user, $password, NULL, $port)  ;
	return mysqli_real_escape_string($escapedbh, $theString);
}