> Companies * * @author Woody Hayday * @version 2.0 * @access public * @see https://jetpackcrm.com/kb */ class zbsDAL_companies extends zbsDAL_ObjectLayer { protected $objectType = ZBS_TYPE_COMPANY; protected $objectDBPrefix = 'zbsco_'; protected $objectIncludesAddresses = true; protected $include_in_templating = true; protected $objectModel = array( // ID 'ID' => array('fieldname' => 'ID', 'format' => 'int'), // site + team generics 'zbs_site' => array('fieldname' => 'zbs_site', 'format' => 'int'), 'zbs_team' => array('fieldname' => 'zbs_team', 'format' => 'int'), 'zbs_owner' => array('fieldname' => 'zbs_owner', 'format' => 'int'), // other fields 'status' => array( // db model: 'fieldname' => 'zbsco_status', 'format' => 'str', // output model 'input_type' => 'select', 'label' => 'Status', 'placeholder'=>'', 'options' => array( 'Lead', 'Customer', 'Refused' ), 'essential' => true, 'max_len' => 50, 'do_not_show_on_portal' => true ), 'name' => array( // db model: 'fieldname' => 'zbsco_name', 'format' => 'str', // output model 'input_type' => 'text', 'label' => 'Name', 'placeholder'=> 'e.g. NewCo', 'dal1key' => 'coname', 'essential' => true, 'force_unique' => true, // must be unique. This is required and breaking if true, 'not_empty' => true, 'max_len' => 100 ), 'addr1' => array( // db model: 'fieldname' => 'zbsco_addr1', 'format' => 'str', // output model 'input_type' => 'text', 'label' => 'Address Line 1', 'placeholder'=>'', 'area'=> 'Main Address', 'migrate'=>'addresses', 'max_len' => 200 ), 'addr2' => array( // db model: 'fieldname' => 'zbsco_addr2', 'format' => 'str', // output model 'input_type' => 'text', 'label' => 'Address Line 2', 'placeholder'=>'', 'area'=> 'Main Address', 'migrate'=>'addresses', 'max_len' => 200 ), 'city' => array( // db model: 'fieldname' => 'zbsc_city', 'format' => 'str', // output model 'input_type' => 'text', 'label' => 'City', 'placeholder'=> 'e.g. New York', 'area'=> 'Main Address', 'migrate'=>'addresses', 'max_len' => 200, // phpcs:ignore Generic.WhiteSpace.DisallowSpaceIndent.SpacesUsed, WordPress.Arrays.MultipleStatementAlignment.DoubleArrowNotAligned ), 'county' => array( // db model: 'fieldname' => 'zbsco_county', 'format' => 'str', // output model 'input_type' => 'text', 'label' => 'County', 'placeholder'=> 'e.g. Kings County', 'area'=> 'Main Address', 'migrate'=>'addresses', 'max_len' => 200 ), 'postcode' => array( // db model: 'fieldname' => 'zbsco_postcode', 'format' => 'str', // output model 'input_type' => 'text', 'label' => 'Post Code', 'placeholder'=> 'e.g. 10019', 'area'=> 'Main Address', 'migrate'=>'addresses', 'max_len' => 50 ), 'country' => array( // db model: 'fieldname' => 'zbsco_country', 'format' => 'str', // output model 'input_type' => 'selectcountry', 'label' => 'Country', 'placeholder'=>'', 'area'=> 'Main Address', 'migrate'=>'addresses', 'max_len' => 200 ), 'secaddr1' => array( // db model: 'fieldname' => 'zbsco_secaddr1', 'format' => 'str', // output model 'input_type' => 'text', 'label' => 'Address Line 1', 'placeholder'=>'', 'area'=> 'Second Address', 'migrate'=>'addresses', 'opt'=>'secondaddress', 'max_len' => 200, 'dal1key' => 'secaddr_addr1' // previous field name ), 'secaddr2' => array( // db model: 'fieldname' => 'zbsco_secaddr2', 'format' => 'str', // output model 'input_type' => 'text', 'label' => 'Address Line 2', 'placeholder'=>'', 'area'=> 'Second Address', 'migrate'=>'addresses', 'opt'=>'secondaddress', 'max_len' => 200, 'dal1key' => 'secaddr_addr2' // previous field name ), 'seccity' => array( // db model: 'fieldname' => 'zbsco_seccity', 'format' => 'str', // output model 'input_type' => 'text', 'label' => 'City', 'placeholder'=> 'e.g. Los Angeles', 'area'=> 'Second Address', 'migrate'=>'addresses', 'opt'=>'secondaddress', 'max_len' => 200, // phpcs:ignore Generic.WhiteSpace.DisallowSpaceIndent.SpacesUsed, WordPress.Arrays.MultipleStatementAlignment.DoubleArrowNotAligned 'dal1key' => 'secaddr_city' // previous field name ), 'seccounty' => array( // db model: 'fieldname' => 'zbsco_seccounty', 'format' => 'str', // output model 'input_type' => 'text', 'label' => 'County', 'placeholder'=> 'e.g. Los Angeles', 'area'=> 'Second Address', 'migrate'=>'addresses', 'opt'=>'secondaddress', 'max_len' => 200, 'dal1key' => 'secaddr_county' // previous field name ), 'secpostcode' => array( // db model: 'fieldname' => 'zbsco_secpostcode', 'format' => 'str', // output model 'input_type' => 'text', 'label' => 'Post Code', 'placeholder'=> 'e.g. 90001', 'area'=> 'Second Address', 'migrate'=>'addresses', 'opt'=>'secondaddress', 'max_len' => 50, 'dal1key' => 'secaddr_postcode' // previous field name ), 'seccountry' => array( // db model: 'fieldname' => 'zbsco_seccountry', 'format' => 'str', // output model 'input_type' => 'selectcountry', 'label' => 'Country', 'placeholder'=>'', 'area'=> 'Second Address', 'migrate'=>'addresses', 'opt'=>'secondaddress', 'max_len' => 200, 'dal1key' => 'secaddr_country' // previous field name ), 'maintel' => array( // db model: 'fieldname' => 'zbsco_maintel', 'format' => 'str', // output model 'input_type' => 'tel', 'label' => 'Main Telephone', 'placeholder'=> 'e.g. 877 2733049', 'max_len' => 40 ), 'sectel' => array( // db model: 'fieldname' => 'zbsco_sectel', 'format' => 'str', // output model 'input_type' => 'tel', 'label' => 'Secondary Telephone', 'placeholder'=> 'e.g. 877 2733049', 'max_len' => 40 ), 'email' => array( // db model: 'fieldname' => 'zbsco_email', 'format' => 'str', // output model 'input_type' => 'email', 'label' => 'Main Email Address', 'placeholder'=> 'e.g. hello@company.com', 'max_len' => 200, 'force_unique' => true, // must be unique. This is required and breaking if true 'can_be_blank' => true, 'do_not_show_on_portal' => true ), // ... just removed for DAL3 :) should be custom field anyway by this point 'wpid' => array('fieldname' => 'zbsco_wpid', 'format' => 'int'), 'avatar' => array('fieldname' => 'zbsco_avatar', 'format' => 'str'), 'tw' => array( 'fieldname' => 'zbsco_tw', 'format' => 'str', 'max_len' => 100 ), 'li' => array( 'fieldname' => 'zbsco_li', 'format' => 'str', 'max_len' => 300 ), 'fb' => array( 'fieldname' => 'zbsco_fb', 'format' => 'str', 'max_len' => 200 ), 'created' => array('fieldname' => 'zbsco_created', 'format' => 'uts'), 'lastupdated' => array('fieldname' => 'zbsco_lastupdated', 'format' => 'uts'), 'lastcontacted' => array('fieldname' => 'zbsco_lastcontacted', 'format' => 'uts'), ); function __construct($args=array()) { #} =========== LOAD ARGS ============== $defaultArgs = array( //'tag' => false, ); foreach ($defaultArgs as $argK => $argV){ $this->$argK = $argV; if (is_array($args) && isset($args[$argK])) { if (is_array($args[$argK])){ $newData = $this->$argK; if (!is_array($newData)) $newData = array(); foreach ($args[$argK] as $subK => $subV){ $newData[$subK] = $subV; }$this->$argK = $newData;} else { $this->$argK = $args[$argK]; } } } #} =========== / LOAD ARGS ============= add_filter( 'jpcrm_listview_filters', array( $this, 'add_listview_filters' ) ); } /** * Adds items to listview filter using `jpcrm_listview_filters` hook. * * @param array $listview_filters Listview filters. */ public function add_listview_filters( $listview_filters ) { global $zbs; $quick_filter_settings = $zbs->settings->get( 'quickfiltersettings' ); // Add 'not-contacted-in-x-days'. if ( ! empty( $quick_filter_settings['notcontactedinx'] ) && $quick_filter_settings['notcontactedinx'] > 0 ) { $days = (int) $quick_filter_settings['notcontactedinx']; $listview_filters[ ZBS_TYPE_COMPANY ]['general'][ 'notcontactedin' . $days ] = sprintf( // translators: %s is the number of days __( 'Not Contacted in %s days', 'zero-bs-crm' ), $days ); } // Add 'olderthan-x-days'. if ( ! empty( $quick_filter_settings['olderthanx'] ) && $quick_filter_settings['olderthanx'] > 0 ) { $days = (int) $quick_filter_settings['olderthanx']; $listview_filters[ ZBS_TYPE_COMPANY ]['general'][ 'olderthan' . $days ] = sprintf( // translators: %s is the number of days __( 'Older than %s days', 'zero-bs-crm' ), $days ); } // Add statuses if enabled. if ( $zbs->settings->get( 'filtersfromstatus' ) === 1 ) { $statuses = zeroBSCRM_getCompanyStatuses(); foreach ( $statuses as $status ) { $listview_filters[ ZBS_TYPE_COMPANY ]['status'][ 'status_' . $status ] = $status; } } return $listview_filters; } // generic get Company (by ID) // Super simplistic wrapper used by edit page etc. (generically called via dal->contacts->getSingle etc.) public function getSingle($ID=-1){ return $this->getCompany($ID); } // generic get contact (by ID list) // Super simplistic wrapper used by MVP Export v3.0 public function getIDList($IDs=false){ return $this->getCompanies(array( 'inArr' => $IDs, 'withCustomFields' => true, 'page' => -1, 'perPage' => -1 )); } // generic get (EVERYTHING) // expect heavy load! public function getAll($IDs=false){ return $this->getCompanies(array( 'withCustomFields' => true, 'sortByField' => 'ID', 'sortOrder' => 'ASC', 'page' => -1, 'perPage' => -1, )); } // generic get count of (EVERYTHING) public function getFullCount(){ return $this->getCompanies(array( 'count' => true, 'page' => -1, 'perPage' => -1, )); } /** * Retrieves the company ID by its name. * * @param string $name The name of the company whose ID is to be retrieved. * @return int|bool Returns the ID of the company if found, false otherwise. * * @throws Exception Catches and handles exceptions, logging SQL errors. * * @since 6.2.0 */ public function get_company_id_by_name( $name ) { global $ZBSCRM_t; // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase global $wpdb; try { $query = $this->prepare( 'SELECT ID FROM ' . $ZBSCRM_t['companies'] . ' WHERE zbsco_name LIKE %s', $name ); // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase $result = $wpdb->get_row( $query, OBJECT ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.NotPrepared if ( isset( $result ) && ( $result->ID ) ) { return $result->ID; } } catch ( Exception $e ) { $this->catchSQLError( $e ); } return false; } /** * returns full company line +- details * * @param int id company id * @param array $args Associative array of arguments * * @return array company object */ public function getCompany($id=-1,$args=array()){ global $zbs; #} =========== LOAD ARGS ============== $defaultArgs = array( 'email' => false, // if id -1 and email given, will return based on email 'name' => false, // if id -1 and name given, will return based on name // if theset wo passed, will search based on these 'externalSource' => false, 'externalSourceUID' => false, // with what? 'withCustomFields' => true, 'withQuotes' => false, 'withInvoices' => false, 'withTransactions' => false, 'withTasks' => false, //'withLogs' => false, 'withLastLog' => false, 'withTags' => false, 'withOwner' => false, 'withValues' => false, // if passed, returns with 'total' 'invoices_total' 'transactions_total' etc. (requires getting all obj, use sparingly) 'withContacts' => true, // return ['contact'] objs 'withExternalSources' => false, 'withExternalSourcesGrouped' => false, // permissions 'ignoreowner' => zeroBSCRM_DAL2_ignoreOwnership(ZBS_TYPE_COMPANY), // this'll let you not-check the owner of obj // returns scalar ID of line 'onlyID' => false, 'fields' => false // false = *, array = fieldnames ); foreach ($defaultArgs as $argK => $argV){ $$argK = $argV; if (is_array($args) && isset($args[$argK])) { if (is_array($args[$argK])){ $newData = $$argK; if (!is_array($newData)) $newData = array(); foreach ($args[$argK] as $subK => $subV){ $newData[$subK] = $subV; }$$argK = $newData;} else { $$argK = $args[$argK]; } } } #} =========== / LOAD ARGS ============= #} Check ID $id = (int)$id; if ( (!empty($id) && $id > 0) || (!empty($email)) || ( ! empty( $name ) ) || (!empty($externalSource) && !empty($externalSourceUID)) ){ global $ZBSCRM_t,$wpdb; $wheres = array('direct'=>array()); $whereStr = ''; $additionalWhere = ''; $params = array(); $res = array(); $extraSelect = ''; #} ============= PRE-QUERY ============ #} Custom Fields if ($withCustomFields && !$onlyID){ #} Retrieve any cf $custFields = $this->DAL()->getActiveCustomFields(array('objtypeid'=>ZBS_TYPE_COMPANY)); #} Cycle through + build into query if (is_array($custFields)) foreach ($custFields as $cK => $cF){ // add as subquery $extraSelect .= ',(SELECT zbscf_objval FROM '.$ZBSCRM_t['customfields']." WHERE zbscf_objid = company.ID AND zbscf_objkey = %s AND zbscf_objtype = %d LIMIT 1) '".$cK."'"; // add params $params[] = $cK; $params[] = ZBS_TYPE_COMPANY; } } // Add any addr custom fields for addr1+addr2 $addrCustomFields = $this->DAL()->getActiveCustomFields(array('objtypeid'=>ZBS_TYPE_ADDRESS)); if ($withCustomFields && !$onlyID && is_array($addrCustomFields) && count($addrCustomFields) > 0){ foreach ($addrCustomFields as $cK => $cF){ // custom field key $cfKey = 'addr_'.$cK; $cfKey2 = 'secaddr_'.$cK; // address custom field (e.g. 'house name') it'll be passed here as 'house-name' // ... problem is mysql does not like that :) so we have to chage here: // in this case we prepend address cf's with addr_ and we switch - for _ $cKey = 'addrcf_'.str_replace('-','_',$cK); $cKey2 = 'secaddrcf_'.str_replace('-','_',$cK); // addr1 // add as subquery $extraSelect .= ',(SELECT zbscf_objval FROM '.$ZBSCRM_t['customfields']." WHERE zbscf_objid = company.ID AND zbscf_objkey = %s AND zbscf_objtype = %d) ".$cKey; // add params $params[] = $cfKey; $params[] = ZBS_TYPE_COMPANY; // addr2 // add as subquery $extraSelect .= ',(SELECT zbscf_objval FROM '.$ZBSCRM_t['customfields']." WHERE zbscf_objid = company.ID AND zbscf_objkey = %s AND zbscf_objtype = %d) ".$cKey2; // add params $params[] = $cfKey2; $params[] = ZBS_TYPE_COMPANY; } } // ==== TOTAL VALUES // Calculate total vals etc. with SQL if (!$onlyID && $withValues){ // arguably, if getting $withInvoices etc. may be more performant to calc this in php in AFTER loop, // ... for now as a fair guess, this'll be most performant: // ... we calc total by adding invs + trans below :) // only include transactions with statuses which should be included in total value: $transStatusQueryAdd = $this->DAL()->transactions->getTransactionStatusesToIncludeQuery(); // include invoices without deleted status in the total value for invoices_total_inc_deleted: $inv_status_query_add = $this->DAL()->invoices->get_invoice_status_except_deleted_for_query(); // quotes: $extraSelect .= ',(SELECT SUM(quotestotal.zbsq_value) FROM '.$ZBSCRM_t['quotes'].' as quotestotal WHERE quotestotal.ID IN (SELECT DISTINCT zbsol_objid_from FROM '.$ZBSCRM_t['objlinks']." WHERE zbsol_objtype_from = ".ZBS_TYPE_QUOTE." AND zbsol_objtype_to = ".ZBS_TYPE_COMPANY." AND zbsol_objid_to = company.ID)) as quotes_total"; // invs: $extraSelect .= ',(SELECT IFNULL(SUM(invstotal.zbsi_total),0) FROM ' . $ZBSCRM_t['invoices'] . ' as invstotal WHERE invstotal.ID IN (SELECT DISTINCT zbsol_objid_from FROM ' . $ZBSCRM_t['objlinks'] . ' WHERE zbsol_objtype_from = ' . ZBS_TYPE_INVOICE . ' AND zbsol_objtype_to = ' . ZBS_TYPE_COMPANY . ' AND zbsol_objid_to = company.ID)' . $inv_status_query_add . ') as invoices_total'; // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase // invs including deleted: $extraSelect .= ',(SELECT SUM(invstotalincdeleted.zbsi_total) FROM ' . $ZBSCRM_t['invoices'] . ' as invstotalincdeleted WHERE invstotalincdeleted.ID IN (SELECT DISTINCT zbsol_objid_from FROM ' . $ZBSCRM_t['objlinks'] . ' WHERE zbsol_objtype_from = ' . ZBS_TYPE_INVOICE . ' AND zbsol_objtype_to = ' . ZBS_TYPE_COMPANY . ' AND zbsol_objid_to = company.ID)) as invoices_total_inc_deleted'; // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase // invs count: $extraSelect .= ',(SELECT COUNT(ID) FROM ' . $ZBSCRM_t['invoices'] . ' WHERE ID IN (SELECT DISTINCT zbsol_objid_from FROM ' . $ZBSCRM_t['objlinks'] . ' WHERE zbsol_objtype_from = ' . ZBS_TYPE_INVOICE . ' AND zbsol_objtype_to = ' . ZBS_TYPE_COMPANY . ' AND zbsol_objid_to = company.ID)' . $inv_status_query_add . ') as invoices_count'; // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase // invs count including deleted: $extraSelect .= ',(SELECT COUNT(ID) FROM ' . $ZBSCRM_t['invoices'] . ' WHERE ID IN (SELECT DISTINCT zbsol_objid_from FROM ' . $ZBSCRM_t['objlinks'] . ' WHERE zbsol_objtype_from = ' . ZBS_TYPE_INVOICE . ' AND zbsol_objtype_to = ' . ZBS_TYPE_COMPANY . ' AND zbsol_objid_to = company.ID)) as invoices_count_inc_deleted'; // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase // trans (with status): $extraSelect .= ',(SELECT SUM(transtotal.zbst_total) FROM '.$ZBSCRM_t['transactions'].' as transtotal WHERE transtotal.ID IN (SELECT DISTINCT zbsol_objid_from FROM '.$ZBSCRM_t['objlinks']." WHERE zbsol_objtype_from = ".ZBS_TYPE_TRANSACTION." AND zbsol_objtype_to = ".ZBS_TYPE_COMPANY." AND zbsol_objid_to = company.ID)".$transStatusQueryAdd.") as transactions_total"; // paid balance against invs (also in getCompany) // (this allows us to subtract from totals to get a true figure where transactions are part/whole payments for invs) /* This selects transactions where there is a link to an invoice where that invoice has a link to this contact: ========== SELECT * FROM wp_zbs_transactions trans WHERE trans.ID IN ( SELECT DISTINCT zbsol_objid_from FROM `wp_zbs_object_links` WHERE zbsol_objtype_from = 5 AND zbsol_objtype_to = 4 AND zbsol_objid_to IN ( SELECT DISTINCT zbsol_objid_from FROM `wp_zbs_object_links` WHERE zbsol_objtype_from = 4 AND zbsol_objtype_to = 1 AND zbsol_objid_to = 1 ) ) */ $extraSelect .= ',(SELECT SUM(assignedtranstotal.zbst_total) FROM '.$ZBSCRM_t['transactions'].' assignedtranstotal WHERE assignedtranstotal.ID IN '; $extraSelect .= '(SELECT DISTINCT zbsol_objid_from FROM '.$ZBSCRM_t['objlinks'].' WHERE zbsol_objtype_from = '.ZBS_TYPE_TRANSACTION.' AND zbsol_objtype_to = '.ZBS_TYPE_INVOICE.' AND zbsol_objid_to IN '; $extraSelect .= '(SELECT DISTINCT zbsol_objid_from FROM '.$ZBSCRM_t['objlinks'].' WHERE zbsol_objtype_from = '.ZBS_TYPE_INVOICE.' AND zbsol_objtype_to = '.ZBS_TYPE_COMPANY.' AND zbsol_objid_to = company.ID)'; $extraSelect .= ')'.$transStatusQueryAdd.') as transactions_paid_total'; } // ==== / TOTAL VALUES $selector = 'company.*'; if ( is_array( $fields ) ) { // phpcs:ignore VariableAnalysis.CodeAnalysis.VariableAnalysis.UndefinedVariable $selector = ''; // always needs id, so add if not present if (!in_array('ID',$fields)) $selector = 'company.ID'; foreach ($fields as $f) { if (!empty($selector)) $selector .= ','; $selector .= 'company.'.$f; } } else if ($onlyID){ $selector = 'company.ID'; } #} ============ / PRE-QUERY =========== #} Build query $query = "SELECT ".$selector.$extraSelect." FROM ".$ZBSCRM_t['companies'].' as company'; #} ============= WHERE ================ if (!empty($id) && $id > 0){ #} Add ID $wheres['ID'] = array('ID','=','%d',$id); } if (!empty($email)){ $emailWheres = array(); // simple, inc AKA (even tho not in UI yet :)) //nope$wheres['emailcheck'] = array('zbsco_email','=','%s',$email); #} Add ID $emailWheres['emailcheck'] = array('zbsco_email','=','%s',$email); #} Check AKA $emailWheres['email_alias'] = array('ID','IN',"(SELECT aka_id FROM ".$ZBSCRM_t['aka']." WHERE aka_type = ".ZBS_TYPE_COMPANY." AND aka_alias = %s)",$email); // This generates a query like 'zbsc_email = %s OR zbsc_email2 = %s', // which we then need to include as direct subquery (below) in main query :) $emailSearchQueryArr = $this->buildWheres($emailWheres,'',array(),'OR',false); if (is_array($emailSearchQueryArr) && isset($emailSearchQueryArr['where']) && !empty($emailSearchQueryArr['where'])){ // add it $wheres['direct'][] = array('('.$emailSearchQueryArr['where'].')',$emailSearchQueryArr['params']); } } if (!empty($name)){ // simple $wheres['name'] = array('zbsco_name','=','%s',$name); } if (!empty($externalSource) && !empty($externalSourceUID)){ $wheres['extsourcecheck'] = array('ID','IN','(SELECT DISTINCT zbss_objid FROM '.$ZBSCRM_t['externalsources']." WHERE zbss_objtype = ".ZBS_TYPE_COMPANY." AND zbss_source = %s AND zbss_uid = %s)",array($externalSource,$externalSourceUID)); } #} ============ / WHERE ============== #} Build out any WHERE clauses $wheresArr = $this->buildWheres($wheres,$whereStr,$params); $whereStr = $wheresArr['where']; $params = $params + $wheresArr['params']; #} / Build WHERE #} Ownership v1.0 - the following adds SITE + TEAM checks, and (optionally), owner $params = array_merge($params,$this->ownershipQueryVars($ignoreowner)); // merges in any req. $ownQ = $this->ownershipSQL($ignoreowner); if (!empty($ownQ)) $additionalWhere = $this->spaceAnd($additionalWhere).$ownQ; // adds str to query #} / Ownership #} Append to sql (this also automatically deals with sortby and paging) $query .= $this->buildWhereStr($whereStr,$additionalWhere) . $this->buildSort('ID','DESC') . $this->buildPaging(0,1); try { #} Prep & run query $queryObj = $this->prepare($query,$params); $potentialRes = $wpdb->get_row($queryObj, OBJECT); } catch (Exception $e){ #} General SQL Err $this->catchSQLError($e); } #} Interpret Results (ROW) if (isset($potentialRes) && isset($potentialRes->ID)) { #} Has results, tidy + return #} Only ID? return it directly if ($onlyID) return $potentialRes->ID; // tidy if (is_array($fields)){ // guesses fields based on table col names $res = $this->lazyTidyGeneric($potentialRes); } else { // proper tidy $res = $this->tidy_company($potentialRes,$withCustomFields); } // TWO methods here, this feels more "common sense" (other commented out below.) if ($withInvoices){ #} only gets first 100? //DAL3 ver, more perf, gets all $res['invoices'] = $zbs->DAL->invoices->getInvoices(array( 'assignedCompany' => $potentialRes->ID, // assigned to company id (int) 'page' => -1, 'perPage' => -1, 'ignoreowner' => zeroBSCRM_DAL2_ignoreOwnership(ZBS_TYPE_INVOICE) )); } if ($withQuotes){ //DAL3 ver, more perf, gets all $res['quotes'] = $zbs->DAL->quotes->getQuotes(array( 'assignedCompany' => $potentialRes->ID, // assigned to company id (int) 'page' => -1, 'perPage' => -1, 'ignoreowner' => zeroBSCRM_DAL2_ignoreOwnership(ZBS_TYPE_QUOTE) )); } #} ... brutal for mvp #DB1LEGACY (TOMOVE) if ($withTransactions){ //DAL3 ver, more perf, gets all $res['transactions'] = $zbs->DAL->transactions->getTransactions(array( 'assignedCompany' => $potentialRes->ID, // assigned to company id (int) 'page' => -1, 'perPage' => -1, 'ignoreowner' => zeroBSCRM_DAL2_ignoreOwnership(ZBS_TYPE_TRANSACTION) )); } /* #} With quotes? if ($withQuotes){ // add all quotes lines $res['quotes'] = $this->DAL()->getObjsLinkedToObj(array( 'objtypefrom' => ZBS_TYPE_QUOTE, // quote 'objtypeto' => ZBS_TYPE_COMPANY, // company 'objfromid' => $potentialRes->ID)); } #} With invoices? if ($withQuotes){ // add all invoices lines $res['invoices'] = $this->DAL()->getObjsLinkedToObj(array( 'objtypefrom' => ZBS_TYPE_INVOICE, // invoice 'objtypeto' => ZBS_TYPE_COMPANY, // company 'objfromid' => $potentialRes->ID)); } #} With transactions? if ($withTransactions){ // add all transactions lines $res['transactions'] = $this->DAL()->getObjsLinkedToObj(array( 'objtypefrom' => ZBS_TYPE_TRANSACTION, // transaction 'objtypeto' => ZBS_TYPE_COMPANY, // company 'objfromid' => $potentialRes->ID)); } */ #} With most recent log? #DB1LEGACY (TOMOVE) if ($withLastLog){ $res['lastlog'] = $this->DAL()->logs->getLogsForObj(array( 'objtype' => ZBS_TYPE_COMPANY, 'objid' => $potentialRes->ID, 'incMeta' => true, 'sortByField' => 'zbsl_created', 'sortOrder' => 'DESC', 'page' => 0, 'perPage' => 1 )); } if ($withContacts){ // add all assigned contacts/companies $res['contacts'] = $this->DAL()->contacts->getContacts(array( 'isLinkedToObjType'=>ZBS_TYPE_COMPANY, 'isLinkedToObjID'=>$potentialRes->ID, 'page'=>-1, 'perPage'=>100, // commonsense cap 'ignoreowner'=>zeroBSCRM_DAL2_ignoreOwnership(ZBS_TYPE_CONTACT))); } if ($withTags){ // add all tags lines $res['tags'] = $this->DAL()->getTagsForObjID(array('objtypeid'=>ZBS_TYPE_COMPANY,'objid'=>$potentialRes->ID)); } #} With Assigned? if ($withOwner){ $res['owner'] = zeroBS_getOwner($potentialRes->ID,true,'zerobs_company',$potentialRes->zbs_owner); } #} With Tasks? if ($withTasks){ //DAL3 ver, more perf, gets all $res['tasks'] = $zbs->DAL->events->getEvents(array( 'assignedCompany' => $potentialRes->ID, // assigned to company id (int) 'page' => -1, 'perPage' => -1, 'ignoreowner' => zeroBSCRM_DAL2_ignoreOwnership(ZBS_TYPE_TASK), 'sortByField' => 'zbse_start', 'sortOrder' => 'DESC', 'withAssigned' => false // no need, it's assigned to this obj already )); } // simplistic, could be optimised (though low use means later.) if ( $withExternalSources ){ $res['external_sources'] = $zbs->DAL->getExternalSources( array( 'objectID' => $potentialRes->ID, 'objectType' => ZBS_TYPE_COMPANY, 'ignoreowner' => zeroBSCRM_DAL2_ignoreOwnership( ZBS_TYPE_COMPANY ) )); } if ( $withExternalSourcesGrouped ){ $res['external_sources'] = $zbs->DAL->getExternalSources( -1, array( 'objectID' => $potentialRes->ID, 'objectType' => ZBS_TYPE_COMPANY, 'grouped_by_source' => true, 'ignoreowner' => zeroBSCRM_DAL2_ignoreOwnership( ZBS_TYPE_COMPANY ) )); } return $res; } } // / if ID return false; } /** * returns company detail lines * * @param array $args Associative array of arguments * * @return array of company lines */ public function getCompanies($args=array()){ global $zbs; #} ============ LOAD ARGS ============= $defaultArgs = array( // Search/Filtering (leave as false to ignore) 'searchPhrase' => '', // searches which fields? 'inArr' => false, 'isTagged' => false, // 1x INT OR array(1,2,3) 'isNotTagged' => false, // 1x INT OR array(1,2,3) 'ownedBy' => false, 'externalSource' => false, // e.g. paypal 'olderThan' => false, // uts 'newerThan' => false, // uts 'hasStatus' => false, // Lead (this takes over from the quick filter post 19/6/18) 'otherStatus' => false, // status other than 'Lead' 'hasContact' => false, // has a contact of this ID associated with it 'quickFilters' => false, // booo // addr 'inCounty' => false, // Hertfordshire 'inPostCode' => false, // AL1 1AA 'inCountry' => false, // United Kingdom 'notInCounty' => false, // Hertfordshire 'notInPostCode' => false, // AL1 1AA 'notInCountry' => false, // United Kingdom // generic assignments - requires both // Where the link relationship is OBJECT -> CONTACT 'hasObjIDLinkedTo' => false, // e.g. quoteid 123 'hasObjTypeLinkedTo' => false, // e.g. ZBS_TYPE_QUOTE // generic assignments - requires both // Where the link relationship is CONTACT -> OBJECT 'isLinkedToObjID' => false, // e.g. quoteid 123 'isLinkedToObjType' => false, // e.g. ZBS_TYPE_QUOTE // returns 'count' => false, 'withCustomFields' => true, 'withQuotes' => false, // Will only be operable when Company<->Quotes established 'withInvoices' => false, 'withTransactions' => false, 'withTasks' => false, 'withTags' => false, 'withOwner' => false, 'withLogs' => false, 'withLastLog' => false, 'withValues' => false, // if passed, returns with 'total' 'invoices_total' 'transactions_total' etc. (requires getting all obj, use sparingly) 'withContacts' => true, // return ['contact'] objs 'withExternalSources' => false, 'withExternalSourcesGrouped' => false, 'simplified' => false, // returns just id,name,created (for typeaheads) 'onlyColumns' => false, // if passed (array('fname','lname')) will return only those columns (overwrites some other 'return' options). NOTE: only works for base fields (not custom fields) 'sortByField' => 'ID', 'sortOrder' => 'ASC', 'page' => 0, // this is what page it is (gets * by for limit) 'perPage' => 100, // permissions 'ignoreowner' => zeroBSCRM_DAL2_ignoreOwnership(ZBS_TYPE_COMPANY), // this'll let you not-check the owner of obj ); foreach ($defaultArgs as $argK => $argV){ $$argK = $argV; if (is_array($args) && isset($args[$argK])) { if (is_array($args[$argK])){ $newData = $$argK; if (!is_array($newData)) $newData = array(); foreach ($args[$argK] as $subK => $subV){ $newData[$subK] = $subV; }$$argK = $newData;} else { $$argK = $args[$argK]; } } } #} =========== / LOAD ARGS ============= global $ZBSCRM_t,$wpdb,$zbs; $wheres = array('direct'=>array()); $whereStr = ''; $additionalWhere = ''; $params = array(); $res = array(); $joinQ = ''; $extraSelect = ''; $whereCase = 'AND'; #} ============= PRE-QUERY ============ #} Capitalise this $sortOrder = strtoupper($sortOrder); #} If just count, or simplified, turn off any extra gumpf if ( $count || $simplified ) { $withCustomFields = false; $withTags = false; $withOwner = false; $withLogs = false; $withLastLog = false; $withContacts = false; $withTasks = false; $withExternalSources = false; $withExternalSourcesGrouped = false; } #} If onlyColumns, validate if ($onlyColumns){ #} onlyColumns build out a field arr if (is_array($onlyColumns) && count($onlyColumns) > 0){ $onlyColumnsFieldArr = array(); foreach ($onlyColumns as $col){ // find db col key from field key (e.g. fname => zbsc_fname) $dbCol = ''; if (isset($this->objectModel[$col]) && isset($this->objectModel[$col]['fieldname'])) $dbCol = $this->objectModel[$col]['fieldname']; if (!empty($dbCol)){ $onlyColumnsFieldArr[$dbCol] = $col; } } } // if legit cols: if (isset($onlyColumnsFieldArr) && is_array($onlyColumnsFieldArr) && count($onlyColumnsFieldArr) > 0){ $onlyColumns = true; // If onlyColumns, turn off extras $withCustomFields = false; $withTags = false; $withOwner = false; $withLogs = false; $withLastLog = false; $withContacts = false; $withTasks = false; $withExternalSources = false; $withExternalSourcesGrouped = false; } else { // deny $onlyColumns = false; } } #} Custom Fields // @phan-suppress-next-line PhanImpossibleCondition -- Phan is confused; this var is initialized at the beginning of the function. if ($withCustomFields){ #} Retrieve any cf $custFields = $this->DAL()->getActiveCustomFields(array('objtypeid'=>ZBS_TYPE_COMPANY)); #} Cycle through + build into query if (is_array($custFields)) foreach ($custFields as $cK => $cF){ // custom field (e.g. 'third name') it'll be passed here as 'third-name' // ... problem is mysql does not like that :) so we have to chage here: // in this case we prepend cf's with cf_ and we switch - for _ $cKey = 'cf_'.str_replace('-','_',$cK); // we also check the $sortByField in case that's the same cf if ($cK == $sortByField){ // sort by $sortByField = $cKey; // check if sort needs any CAST (e.g. numeric): $sortByField = $this->DAL()->build_custom_field_order_by_str( $sortByField, $cF ); } // add as subquery $extraSelect .= ',(SELECT zbscf_objval FROM '.$ZBSCRM_t['customfields']." WHERE zbscf_objid = company.ID AND zbscf_objkey = %s AND zbscf_objtype = %d LIMIT 1) ".$cKey; // add params $params[] = $cK; $params[] = ZBS_TYPE_COMPANY; } } // Add any addr custom fields for addr1+addr2 // no need if simpliefied or count parameters passed if (!$simplified && !$count && !$onlyColumns){ $addrCustomFields = $this->DAL()->getActiveCustomFields(array('objtypeid'=>ZBS_TYPE_ADDRESS)); if (is_array($addrCustomFields) && count($addrCustomFields) > 0){ foreach ($addrCustomFields as $cK => $cF){ // custom field key $cfKey = 'addr_'.$cK; $cfKey2 = 'secaddr_'.$cK; // address custom field (e.g. 'house name') it'll be passed here as 'house-name' // ... problem is mysql does not like that :) so we have to chage here: // in this case we prepend address cf's with addr_ and we switch - for _ $cKey = 'addrcf_'.str_replace('-','_',$cK); $cKey2 = 'secaddrcf_'.str_replace('-','_',$cK); // we also check the $sortByField in case that's the same cf if ($cfKey == $sortByField) $sortByField = $cKey; if ($cfKey2 == $sortByField) $sortByField = $cKey2; // addr 1 // add as subquery $extraSelect .= ',(SELECT zbscf_objval FROM '.$ZBSCRM_t['customfields']." WHERE zbscf_objid = company.ID AND zbscf_objkey = %s AND zbscf_objtype = %d) ".$cKey; // add params $params[] = $cfKey; $params[] = ZBS_TYPE_COMPANY; // addr 2 // add as subquery $extraSelect .= ',(SELECT zbscf_objval FROM '.$ZBSCRM_t['customfields']." WHERE zbscf_objid = company.ID AND zbscf_objkey = %s AND zbscf_objtype = %d) ".$cKey2; // add params $params[] = $cfKey2; $params[] = ZBS_TYPE_COMPANY; } } } // ==== TOTAL VALUES // Calculate total vals etc. with SQL if (!$simplified && !$count && $withValues && !$onlyColumns){ // arguably, if getting $withInvoices etc. may be more performant to calc this in php in AFTER loop, // ... for now as a fair guess, this'll be most performant: // ... we calc total by adding invs + trans below :) // only include transactions with statuses which should be included in total value: $transStatusQueryAdd = $this->DAL()->transactions->getTransactionStatusesToIncludeQuery(); // include invoices without deleted status in the total value for invoices_total_inc_deleted: $inv_status_query_add = $this->DAL()->invoices->get_invoice_status_except_deleted_for_query(); // When Company<->Quotes: // $extraSelect .= ',(SELECT SUM(quotestotal.zbsq_value) FROM '.$ZBSCRM_t['quotes'].' as quotestotal WHERE quotestotal.ID IN (SELECT DISTINCT zbsol_objid_from FROM '.$ZBSCRM_t['objlinks']." WHERE zbsol_objtype_from = ".ZBS_TYPE_QUOTE." AND zbsol_objtype_to = ".ZBS_TYPE_COMPANY." AND zbsol_objid_to = company.ID)) as quotes_total"; // invs: $extraSelect .= ',(SELECT IFNULL(SUM(invstotal.zbsi_total),0) FROM ' . $ZBSCRM_t['invoices'] . ' as invstotal WHERE invstotal.ID IN (SELECT DISTINCT zbsol_objid_from FROM ' . $ZBSCRM_t['objlinks'] . ' WHERE zbsol_objtype_from = ' . ZBS_TYPE_INVOICE . ' AND zbsol_objtype_to = ' . ZBS_TYPE_COMPANY . ' AND zbsol_objid_to = company.ID)' . $inv_status_query_add . ') as invoices_total'; // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase // invs including deleted: $extraSelect .= ',(SELECT SUM(invstotalincdeleted.zbsi_total) FROM ' . $ZBSCRM_t['invoices'] . ' as invstotalincdeleted WHERE invstotalincdeleted.ID IN (SELECT DISTINCT zbsol_objid_from FROM ' . $ZBSCRM_t['objlinks'] . ' WHERE zbsol_objtype_from = ' . ZBS_TYPE_INVOICE . ' AND zbsol_objtype_to = ' . ZBS_TYPE_COMPANY . ' AND zbsol_objid_to = company.ID)) as invoices_total_inc_deleted'; // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase // invs count: $extraSelect .= ',(SELECT COUNT(ID) FROM ' . $ZBSCRM_t['invoices'] . ' WHERE ID IN (SELECT DISTINCT zbsol_objid_from FROM ' . $ZBSCRM_t['objlinks'] . ' WHERE zbsol_objtype_from = ' . ZBS_TYPE_INVOICE . ' AND zbsol_objtype_to = ' . ZBS_TYPE_COMPANY . ' AND zbsol_objid_to = company.ID)' . $inv_status_query_add . ') as invoices_count'; // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase // invs count including deleted: $extraSelect .= ',(SELECT COUNT(ID) FROM ' . $ZBSCRM_t['invoices'] . ' WHERE ID IN (SELECT DISTINCT zbsol_objid_from FROM ' . $ZBSCRM_t['objlinks'] . ' WHERE zbsol_objtype_from = ' . ZBS_TYPE_INVOICE . ' AND zbsol_objtype_to = ' . ZBS_TYPE_COMPANY . ' AND zbsol_objid_to = company.ID)) as invoices_count_inc_deleted'; // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase // trans (with status): $extraSelect .= ',(SELECT SUM(transtotal.zbst_total) FROM '.$ZBSCRM_t['transactions'].' as transtotal WHERE transtotal.ID IN (SELECT DISTINCT zbsol_objid_from FROM '.$ZBSCRM_t['objlinks']." WHERE zbsol_objtype_from = ".ZBS_TYPE_TRANSACTION." AND zbsol_objtype_to = ".ZBS_TYPE_COMPANY." AND zbsol_objid_to = company.ID)".$transStatusQueryAdd.") as transactions_total"; // paid balance against invs (also in getCompany) // (this allows us to subtract from totals to get a true figure where transactions are part/whole payments for invs) /* This selects transactions where there is a link to an invoice where that invoice has a link to this contact: ========== SELECT * FROM wp_zbs_transactions trans WHERE trans.ID IN ( SELECT DISTINCT zbsol_objid_from FROM `wp_zbs_object_links` WHERE zbsol_objtype_from = 5 AND zbsol_objtype_to = 4 AND zbsol_objid_to IN ( SELECT DISTINCT zbsol_objid_from FROM `wp_zbs_object_links` WHERE zbsol_objtype_from = 4 AND zbsol_objtype_to = 1 AND zbsol_objid_to = 1 ) ) */ $extraSelect .= ',(SELECT SUM(assignedtranstotal.zbst_total) FROM '.$ZBSCRM_t['transactions'].' assignedtranstotal WHERE assignedtranstotal.ID IN '; $extraSelect .= '(SELECT DISTINCT zbsol_objid_from FROM '.$ZBSCRM_t['objlinks'].' WHERE zbsol_objtype_from = '.ZBS_TYPE_TRANSACTION.' AND zbsol_objtype_to = '.ZBS_TYPE_INVOICE.' AND zbsol_objid_to IN '; $extraSelect .= '(SELECT DISTINCT zbsol_objid_from FROM '.$ZBSCRM_t['objlinks'].' WHERE zbsol_objtype_from = '.ZBS_TYPE_INVOICE.' AND zbsol_objtype_to = '.ZBS_TYPE_COMPANY.' AND zbsol_objid_to = company.ID)'; $extraSelect .= ')'.$transStatusQueryAdd.') as transactions_paid_total'; } // ==== / TOTAL VALUES #} ============ / PRE-QUERY =========== #} Build query $query = "SELECT company.*".$extraSelect." FROM ".$ZBSCRM_t['companies'].' as company'.$joinQ; #} Count override if ($count) $query = "SELECT COUNT(company.ID) FROM ".$ZBSCRM_t['companies'].' as company'.$joinQ; #} simplified override if ($simplified) $query = "SELECT company.ID as id,company.zbsco_name as name,company.zbsco_created as created,company.zbsco_email as email FROM ".$ZBSCRM_t['companies'].' as company'.$joinQ; #} onlyColumns override if ($onlyColumns && is_array($onlyColumnsFieldArr) && count($onlyColumnsFieldArr) > 0){ $columnStr = ''; foreach ($onlyColumnsFieldArr as $colDBKey => $colStr){ if (!empty($columnStr)) $columnStr .= ','; // this presumes str is db-safe? could do with sanitation? $columnStr .= $colDBKey; } $query = "SELECT ".$columnStr." FROM ".$ZBSCRM_t['companies'].' as company'.$joinQ; } #} ============= WHERE ================ #} Add Search phrase if (!empty($searchPhrase)){ // search? - ALL THESE COLS should probs have index of FULLTEXT in db? $searchWheres = array(); $searchWheres['search_ID'] = array('ID','=','%d',$searchPhrase); $searchWheres['search_name'] = array('zbsco_name','LIKE','%s','%'.$searchPhrase.'%'); $searchWheres['search_email'] = array('zbsco_email','LIKE','%s','%'.$searchPhrase.'%'); $searchWheres['search_maintel'] = array('zbsco_maintel','LIKE','%s','%'.$searchPhrase.'%'); $searchWheres['search_sectel'] = array('zbsco_sectel','LIKE','%s','%'.$searchPhrase.'%'); // address elements $searchWheres['search_addr1'] = array('zbsco_addr1','LIKE','%s','%'.$searchPhrase.'%'); $searchWheres['search_addr2'] = array('zbsco_addr2','LIKE','%s','%'.$searchPhrase.'%'); $searchWheres['search_city'] = array('zbsco_city','LIKE','%s','%'.$searchPhrase.'%'); $searchWheres['search_county'] = array('zbsco_county','LIKE','%s','%'.$searchPhrase.'%'); $searchWheres['search_country'] = array('zbsco_country','LIKE','%s','%'.$searchPhrase.'%'); $searchWheres['search_postcode'] = array('zbsco_postcode','LIKE','%s','%'.$searchPhrase.'%'); $searchWheres['search_secaddr1'] = array('zbsco_secaddr1','LIKE','%s','%'.$searchPhrase.'%'); $searchWheres['search_secaddr2'] = array('zbsco_secaddr2','LIKE','%s','%'.$searchPhrase.'%'); $searchWheres['search_seccity'] = array('zbsco_seccity','LIKE','%s','%'.$searchPhrase.'%'); $searchWheres['search_seccounty'] = array('zbsco_seccounty','LIKE','%s','%'.$searchPhrase.'%'); $searchWheres['search_seccountry'] = array('zbsco_seccountry','LIKE','%s','%'.$searchPhrase.'%'); $searchWheres['search_secpostcode'] = array('zbsco_secpostcode','LIKE','%s','%'.$searchPhrase.'%'); // social $searchWheres['search_tw'] = array('zbsco_tw','LIKE','%s','%'.$searchPhrase.'%'); $searchWheres['search_li'] = array('zbsco_li','LIKE','%s','%'.$searchPhrase.'%'); $searchWheres['search_fb'] = array('zbsco_fb','LIKE','%s','%'.$searchPhrase.'%'); // 3.0.13 - Added ability to search custom fields (optionally) $customFieldSearch = zeroBSCRM_getSetting('customfieldsearch'); if ($customFieldSearch == 1){ // simplistic add // NOTE: This IGNORES ownership of custom field lines. $searchWheres['search_customfields'] = array('ID','IN',"(SELECT zbscf_objid FROM ".$ZBSCRM_t['customfields']." WHERE zbscf_objval LIKE %s AND zbscf_objtype = ".ZBS_TYPE_COMPANY.")",'%'.$searchPhrase.'%'); } // This generates a query like 'zbsco_fname LIKE %s OR zbsco_lname LIKE %s', // which we then need to include as direct subquery (below) in main query :) $searchQueryArr = $this->buildWheres($searchWheres,'',array(),'OR',false); if (is_array($searchQueryArr) && isset($searchQueryArr['where']) && !empty($searchQueryArr['where'])){ // add it $wheres['direct'][] = array('('.$searchQueryArr['where'].')',$searchQueryArr['params']); } } #} In array (if inCompany passed, this'll currently overwrite that?! (todo2.5)) if (is_array($inArr) && count($inArr) > 0){ // clean for ints $inArrChecked = array(); foreach ($inArr as $x){ $inArrChecked[] = (int)$x; } // add where $wheres['inarray'] = array('ID','IN','('.implode(',',$inArrChecked).')'); } #} Owned by if (is_int($ownedBy) && !empty($ownedBy) && $ownedBy > 0){ // would never hard-type this in (would make generic as in buildWPMetaQueryWhere) // but this is only here until MIGRATED to db2 globally //$wheres['incompany'] = array('ID','IN','(SELECT DISTINCT post_id FROM '.$wpdb->prefix."postmeta WHERE meta_key = 'zbs_company' AND meta_value = %d)",$inCompany); // Use obj links now $wheres['ownedBy'] = array('zbs_owner','=','%s',$ownedBy); } // External sources if ( !empty( $externalSource ) ){ // NO owernship built into this, check when roll out multi-layered ownsership $wheres['externalsource'] = array('ID','IN','(SELECT DISTINCT zbss_objid FROM '.$ZBSCRM_t['externalsources']." WHERE zbss_objtype = ".ZBS_TYPE_COMPANY." AND zbss_source = %s)",$externalSource); } // phpcs:disable WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase,VariableAnalysis.CodeAnalysis.VariableAnalysis.UndefinedVariable,Generic.ControlStructures.InlineControlStructure.NotAllowed // quick addition for mike #} olderThan if ( ! empty( $olderThan ) && $olderThan > 0 ) $wheres['olderThan'] = array( 'zbsco_created', '<=', '%d', $olderThan ); #} newerThan if ( ! empty( $newerThan ) && $newerThan > 0 ) $wheres['newerThan'] = array( 'zbsco_created', '>=', '%d', $newerThan ); // status if ( ! empty( $hasStatus ) ) $wheres['hasStatus'] = array( 'zbsco_status', '=', '%s', $hasStatus ); if ( ! empty( $otherStatus ) ) $wheres['otherStatus'] = array( 'zbsco_status', '<>', '%s', $otherStatus ); #} inCounty if ( ! empty( $inCounty ) ) { $wheres['inCounty'] = array( 'zbsco_county', '=', '%s', $inCounty ); $wheres['inCountyAddr2'] = array( 'zbsco_secaddrcounty', '=', '%s', $inCounty ); } #} inPostCode if ( ! empty( $inPostCode ) ) { $wheres['inPostCode'] = array( 'zbsco_postcode', '=', '%s', $inPostCode ); $wheres['inPostCodeAddr2'] = array( 'zbsco_secaddrpostcode', '=', '%s', $inPostCode ); } #} inCountry if ( ! empty( $inCountry ) ) { $wheres['inCountry'] = array( 'zbsco_country', '=', '%s', $inCountry ); $wheres['inCountryAddr2'] = array( 'zbsco_secaddrcountry', '=', '%s', $inCountry ); } #} notInCounty if ( ! empty( $notInCounty ) ) { $wheres['notInCounty'] = array( 'zbsco_county', '<>', '%s', $notInCounty ); $wheres['notInCountyAddr2'] = array( 'zbsco_secaddrcounty', '<>', '%s', $notInCounty ); } #} notInPostCode if ( ! empty( $notInPostCode ) ) { $wheres['notInPostCode'] = array( 'zbsco_postcode', '<>', '%s', $notInPostCode ); $wheres['notInPostCodeAddr2'] = array( 'zbsco_secaddrpostcode', '<>', '%s', $notInPostCode ); } #} notInCountry if ( ! empty( $notInCountry ) ) { $wheres['notInCountry'] = array( 'zbsco_country', '<>', '%s', $notInCountry ); $wheres['notInCountryAddr2'] = array( 'zbsco_secaddrcountry', '<>', '%s', $notInCountry ); } // has contact associated with it if ( ! empty( $hasContact ) && $hasContact > 0 ) $wheres['hasContact'] = array( 'ID', 'IN', '(SELECT zbsol_objid_from FROM ' . $ZBSCRM_t['objlinks'] . ' WHERE zbsol_objtype_from = ' . ZBS_TYPE_CONTACT . ' AND zbsol_objtype_to = ' . ZBS_TYPE_COMPANY . ' AND zbsol_objid_to = company.ID)' ); // generic obj links, e.g. quotes, invs, trans // e.g. contact(s) assigned to inv 123 // Where the link relationship is OBJECT -> CONTACT if ( ! empty( $hasObjIDLinkedTo ) && $hasObjIDLinkedTo > 0 && ! empty( $hasObjTypeLinkedTo ) && $hasObjTypeLinkedTo > 0 ) { $wheres['hasObjIDLinkedTo'] = array( 'ID', 'IN', '(SELECT zbsol_objid_to FROM ' . $ZBSCRM_t['objlinks'] . ' WHERE zbsol_objtype_from = %d AND zbsol_objtype_to = ' . ZBS_TYPE_COMPANY . ' AND zbsol_objid_from = %d AND zbsol_objid_to = company.ID)', array( $hasObjTypeLinkedTo, $hasObjIDLinkedTo ) ); } // generic obj links, e.g. companies // Where the link relationship is CONTACT -> OBJECT if ( ! empty( $isLinkedToObjID ) && $isLinkedToObjID > 0 && ! empty( $isLinkedToObjType ) && $isLinkedToObjType > 0 ) { $wheres['isLinkedToObjID'] = array( 'ID', 'IN', '(SELECT zbsol_objid_from FROM ' . $ZBSCRM_t['objlinks'] . ' WHERE zbsol_objtype_from = ' . ZBS_TYPE_COMPANY . ' AND zbsol_objtype_to = %d AND zbsol_objid_from = company.ID AND zbsol_objid_to = %d)', array( $isLinkedToObjType, $isLinkedToObjID ) ); } // phpcs:enable WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase,VariableAnalysis.CodeAnalysis.VariableAnalysis.UndefinedVariable,Generic.ControlStructures.InlineControlStructure.NotAllowed #} Quick filters - adapted from DAL1 (probs can be slicker) if (is_array($quickFilters) && count($quickFilters) > 0){ // cycle through foreach ($quickFilters as $qFilter){ // where status = x // USE hasStatus above now... if ( str_starts_with( $qFilter, 'status_' ) ) { // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase $quick_filter_status = substr( $qFilter, 7 ); // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase $wheres['quickfilterstatus'] = array( 'zbsco_status', '=', 'convert(%s using utf8mb4) collate utf8mb4_bin', $quick_filter_status ); } elseif ( str_starts_with( $qFilter, 'notcontactedin' ) ) { // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase // check $notcontactedinDays = (int)substr($qFilter,14); $notcontactedinDaysSeconds = $notcontactedinDays*86400; $wheres['notcontactedinx'] = array('zbsco_lastcontacted','<','%d',time()-$notcontactedinDaysSeconds); } elseif ( str_starts_with( $qFilter, 'olderthan' ) ) { // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase // check $olderThanDays = (int)substr($qFilter,9); $olderThanDaysSeconds = $olderThanDays*86400; $wheres['olderthanx'] = array('zbsco_created','<','%d',time()-$olderThanDaysSeconds); } else { // normal/hardtyped switch ($qFilter){ case 'lead': // hack "leads only" - adapted from DAL1 (probs can be slicker) $wheres['quickfilterlead'] = array('zbsco_status','LIKE','%s','Lead'); break; case 'customer': // hack - adapted from DAL1 (probs can be slicker) $wheres['quickfiltercustomer'] = array( 'zbsco_status', 'LIKE', '%s', 'Customer' ); break; } // / switch } // / hardtyped } } // / quickfilters #} Is Tagged (expects 1 tag ID OR array) // catch 1 item arr if (is_array($isTagged) && count($isTagged) == 1) $isTagged = $isTagged[0]; if ( ! empty( $isTagged ) && ! is_array( $isTagged ) && $isTagged > 0 ) { // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase // add where tagged // 1 int: $wheres['direct'][] = array('((SELECT COUNT(ID) FROM '.$ZBSCRM_t['taglinks'].' WHERE zbstl_objtype = %d AND zbstl_objid = company.ID AND zbstl_tagid = %d) > 0)',array(ZBS_TYPE_COMPANY,$isTagged)); } else if (is_array($isTagged) && count($isTagged) > 0){ // foreach in array :) $tagStr = ''; foreach ($isTagged as $iTag){ $i = (int)$iTag; if ($i > 0){ if ($tagStr !== '') $tagStr .','; $tagStr .= $i; } } if (!empty($tagStr)){ $wheres['direct'][] = array('((SELECT COUNT(ID) FROM '.$ZBSCRM_t['taglinks'].' WHERE zbstl_objtype = %d AND zbstl_objid = company.ID AND zbstl_tagid IN (%s)) > 0)',array(ZBS_TYPE_COMPANY,$tagStr)); } } #} Is NOT Tagged (expects 1 tag ID OR array) // catch 1 item arr if (is_array($isNotTagged) && count($isNotTagged) == 1) $isNotTagged = $isNotTagged[0]; if ( ! empty( $isNotTagged ) && ! is_array( $isNotTagged ) && $isNotTagged > 0 ) { // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase // add where tagged // 1 int: $wheres['direct'][] = array('((SELECT COUNT(ID) FROM '.$ZBSCRM_t['taglinks'].' WHERE zbstl_objtype = %d AND zbstl_objid = company.ID AND zbstl_tagid = %d) = 0)',array(ZBS_TYPE_COMPANY,$isNotTagged)); } else if (is_array($isNotTagged) && count($isNotTagged) > 0){ // foreach in array :) $tagStr = ''; foreach ($isNotTagged as $iTag){ $i = (int)$iTag; if ($i > 0){ if ($tagStr !== '') $tagStr .','; $tagStr .= $i; } } if (!empty($tagStr)){ $wheres['direct'][] = array('((SELECT COUNT(ID) FROM '.$ZBSCRM_t['taglinks'].' WHERE zbstl_objtype = %d AND zbstl_objid = company.ID AND zbstl_tagid IN (%s)) = 0)',array(ZBS_TYPE_COMPANY,$tagStr)); } } #} ============ / WHERE =============== #} ============ SORT ============== // Obj Model based sort conversion // converts 'addr1' => 'zbsco_addr1' generically if (isset($this->objectModel[$sortByField]) && isset($this->objectModel[$sortByField]['fieldname'])) $sortByField = $this->objectModel[$sortByField]['fieldname']; // include invoices without deleted status in the total value for invoices_total_inc_deleted: $inv_status_query_add = $this->DAL()->invoices->get_invoice_status_except_deleted_for_query(); // Mapped sorts // This catches listview and other exception sort cases $sort_map = array( 'id' => 'ID', 'assigned' => 'zbsco_owner', 'fullname' => 'zbsco_name', 'status' => 'zbsco_status', 'email' => 'zbsco_email', // contacts (count) 'contacts' => '(SELECT COUNT(ID) FROM '.$ZBSCRM_t['contacts'].' WHERE ID IN (SELECT zbsol_objid_from FROM '.$ZBSCRM_t['objlinks'].' WHERE zbsol_objtype_from = '.ZBS_TYPE_CONTACT.' AND zbsol_objtype_to = '.ZBS_TYPE_COMPANY.' AND zbsol_objid_to = company.ID))', // has & counts (same queries) // phpcs:disable WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase 'hasinvoice' => '(SELECT COUNT(ID) FROM ' . $ZBSCRM_t['invoices'] . ' WHERE ID IN (SELECT DISTINCT zbsol_objid_from FROM ' . $ZBSCRM_t['objlinks'] . ' WHERE zbsol_objtype_from = ' . ZBS_TYPE_INVOICE . ' AND zbsol_objtype_to = ' . ZBS_TYPE_COMPANY . ' AND zbsol_objid_to = company.ID))', 'hastransaction' => '(SELECT COUNT(ID) FROM ' . $ZBSCRM_t['transactions'] . ' WHERE ID IN (SELECT DISTINCT zbsol_objid_from FROM ' . $ZBSCRM_t['objlinks'] . ' WHERE zbsol_objtype_from = ' . ZBS_TYPE_TRANSACTION . ' AND zbsol_objtype_to = ' . ZBS_TYPE_COMPANY . ' AND zbsol_objid_to = company.ID))', 'invoicecount_inc_deleted' => '(SELECT COUNT(ID) FROM ' . $ZBSCRM_t['invoices'] . ' WHERE ID IN (SELECT DISTINCT zbsol_objid_from FROM ' . $ZBSCRM_t['objlinks'] . ' WHERE zbsol_objtype_from = ' . ZBS_TYPE_INVOICE . ' AND zbsol_objtype_to = ' . ZBS_TYPE_COMPANY . ' AND zbsol_objid_to = company.ID))', 'invoicecount' => '(SELECT COUNT(ID) FROM ' . $ZBSCRM_t['invoices'] . ' WHERE ID IN (SELECT DISTINCT zbsol_objid_from FROM ' . $ZBSCRM_t['objlinks'] . ' WHERE zbsol_objtype_from = ' . ZBS_TYPE_INVOICE . ' AND zbsol_objtype_to = ' . ZBS_TYPE_COMPANY . ' AND zbsol_objid_to = company.ID)' . $inv_status_query_add . ')', 'transactioncount' => '(SELECT COUNT(ID) FROM ' . $ZBSCRM_t['transactions'] . ' WHERE ID IN (SELECT DISTINCT zbsol_objid_from FROM ' . $ZBSCRM_t['objlinks'] . ' WHERE zbsol_objtype_from = ' . ZBS_TYPE_TRANSACTION . ' AND zbsol_objtype_to = ' . ZBS_TYPE_COMPANY . ' AND zbsol_objid_to = company.ID))', // phpcs:enable WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase // following will only work if obj total value subqueries triggered above ^ 'totalvalue' => '((IFNULL(invoices_total,0) + IFNULL(transactions_total,0)) - IFNULL(transactions_paid_total,0))', // custom sort by total invoice value + transaction value - paid transactions (as mimicking tidy_contact php logic into SQL) 'transactiontotal' => 'transactions_total', 'invoicetotal' => 'invoices_total', // When Company<->Quotes: /* 'hasquote' => '(SELECT COUNT(ID) FROM '.$ZBSCRM_t['quotes'].' WHERE ID IN (SELECT DISTINCT zbsol_objid_from FROM '.$ZBSCRM_t['objlinks'].' WHERE zbsol_objtype_from = '.ZBS_TYPE_QUOTE.' AND zbsol_objtype_to = '.ZBS_TYPE_COMPANY.' AND zbsol_objid_to = company.ID))', 'quotetotal' => 'quotes_total', 'quotecount' => '(SELECT COUNT(ID) FROM '.$ZBSCRM_t['quotes'].' WHERE ID IN (SELECT DISTINCT zbsol_objid_from FROM '.$ZBSCRM_t['objlinks'].' WHERE zbsol_objtype_from = '.ZBS_TYPE_QUOTE.' AND zbsol_objtype_to = '.ZBS_TYPE_COMPANY.' AND zbsol_objid_to = company.ID))', */ ); if ( array_key_exists( $sortByField, $sort_map ) ) { $sortByField = $sort_map[ $sortByField ]; } #} ============ / SORT ============== #} CHECK this + reset to default if faulty if (!in_array($whereCase,array('AND','OR'))) $whereCase = 'AND'; #} Build out any WHERE clauses $wheresArr = $this->buildWheres($wheres,$whereStr,$params,$whereCase); $whereStr = $wheresArr['where']; $params = $params + $wheresArr['params']; #} / Build WHERE #} Ownership v1.0 - the following adds SITE + TEAM checks, and (optionally), owner $params = array_merge($params,$this->ownershipQueryVars($ignoreowner)); // merges in any req. $ownQ = $this->ownershipSQL($ignoreowner,'contact'); if (!empty($ownQ)) $additionalWhere = $this->spaceAnd($additionalWhere).$ownQ; // adds str to query #} / Ownership #} Append to sql (this also automatically deals with sortby and paging) $query .= $this->buildWhereStr($whereStr,$additionalWhere) . $this->buildSort($sortByField,$sortOrder) . $this->buildPaging($page,$perPage); try { #} Prep & run query $queryObj = $this->prepare($query,$params); #} Catch count + return if requested if ($count) return $wpdb->get_var($queryObj); #} else continue.. $potentialRes = $wpdb->get_results($queryObj, OBJECT); } catch (Exception $e){ #} General SQL Err $this->catchSQLError($e); } #} Interpret results (Result Set - multi-row) if (isset($potentialRes) && is_array($potentialRes) && count($potentialRes) > 0) { #} Has results, tidy + return foreach ($potentialRes as $resDataLine) { #} simplified override if ($simplified){ $resArr = array( 'id' => $resDataLine->id, 'name' => $resDataLine->name, 'created' => $resDataLine->created, 'email' => $resDataLine->email ); } else if ($onlyColumns && is_array($onlyColumnsFieldArr) && count($onlyColumnsFieldArr) > 0){ // only coumns return. $resArr = array(); foreach ($onlyColumnsFieldArr as $colDBKey => $colStr){ if (isset($resDataLine->$colDBKey)) $resArr[$colStr] = $resDataLine->$colDBKey; } } else { // tidy $resArr = $this->tidy_company($resDataLine,$withCustomFields); } if ($withTags){ // add all tags lines $resArr['tags'] = $this->DAL()->getTagsForObjID(array('objtypeid'=>ZBS_TYPE_COMPANY,'objid'=>$resDataLine->ID)); } #} With most recent log? #DB1LEGACY (TOMOVE) if ($withLastLog){ // doesn't return singular, for now using arr $potentialLogs = $this->DAL()->logs->getLogsForObj(array( 'objtype' => ZBS_TYPE_COMPANY, 'objid' => $resDataLine->ID, 'incMeta' => true, 'sortByField' => 'zbsl_created', 'sortOrder' => 'DESC', 'page' => 0, 'perPage' => 1 )); if (is_array($potentialLogs) && count($potentialLogs) > 0) $resArr['lastlog'] = $potentialLogs[0]; // COMPANY logs specifically // doesn't return singular, for now using arr $potentialLogs = $this->DAL()->logs->getLogsForObj( // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase array( 'objtype' => ZBS_TYPE_COMPANY, 'objid' => $resDataLine->ID, // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase 'notetypes' => $zbs->DAL->logs->contact_log_types, // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase 'incMeta' => true, 'sortByField' => 'zbsl_created', 'sortOrder' => 'DESC', 'page' => 0, 'perPage' => 1, ) ); if (is_array($potentialLogs) && count($potentialLogs) > 0) $resArr['lastcontactlog'] = $potentialLogs[0]; } #} With Assigned? if ($withOwner){ $resArr['owner'] = zeroBS_getOwner($resDataLine->ID,true,'zerobs_company',$resDataLine->zbs_owner); } if ($withContacts){ // add all assigned contacts/companies $resArr['contacts'] = $this->DAL()->contacts->getContacts(array( 'isLinkedToObjType'=>ZBS_TYPE_COMPANY, 'isLinkedToObjID'=>$resDataLine->ID, 'page'=>-1, 'perPage'=>-1, 'ignoreowner'=>zeroBSCRM_DAL2_ignoreOwnership(ZBS_TYPE_CONTACT))); } if ($withInvoices){ //DAL3 ver, more perf, gets all $resArr['invoices'] = $zbs->DAL->invoices->getInvoices(array( 'assignedCompany' => $resDataLine->ID, // assigned to company id (int) 'page' => -1, 'perPage' => -1, 'ignoreowner' => zeroBSCRM_DAL2_ignoreOwnership(ZBS_TYPE_INVOICE) )); } if ( $withQuotes ) { //DAL3 ver, more perf, gets all $resArr['quotes'] = $zbs->DAL->quotes->getQuotes(array( 'assignedCompany' => $resDataLine->ID, // assigned to company id (int) 'page' => -1, 'perPage' => -1, 'ignoreowner' => zeroBSCRM_DAL2_ignoreOwnership(ZBS_TYPE_QUOTE) )); } #} ... brutal for mvp #DB1LEGACY (TOMOVE) if ($withTransactions){ //DAL3 ver, more perf, gets all $resArr['transactions'] = $zbs->DAL->transactions->getTransactions(array( 'assignedCompany' => $resDataLine->ID, // assigned to company id (int) 'page' => -1, 'perPage' => -1, 'ignoreowner' => zeroBSCRM_DAL2_ignoreOwnership(ZBS_TYPE_TRANSACTION) )); } #} ... brutal for mvp #DB1LEGACY (TOMOVE) if ($withTasks){ $res['tasks'] = $zbs->DAL->events->getEvents(array( 'assignedCompany' => $resDataLine->ID, // assigned to company id (int) 'page' => -1, 'perPage' => -1, 'ignoreowner' => zeroBSCRM_DAL2_ignoreOwnership(ZBS_TYPE_TASK), 'sortByField' => 'zbse_start', 'sortOrder' => 'DESC', 'withAssigned' => false // no need, it's assigned to this obj already )); } // simplistic, could be optimised (though low use means later.) if ( $withExternalSources ){ $res['external_sources'] = $zbs->DAL->getExternalSources( array( 'objectID' => $resDataLine->ID, 'objectType' => ZBS_TYPE_COMPANY, 'ignoreowner' => zeroBSCRM_DAL2_ignoreOwnership(ZBS_TYPE_CONTACT) )); } if ( $withExternalSourcesGrouped ){ $res['external_sources'] = $zbs->DAL->getExternalSources( -1, array( 'objectID' => $resDataLine->ID, 'objectType' => ZBS_TYPE_COMPANY, 'grouped_by_source' => true, 'ignoreowner' => zeroBSCRM_DAL2_ignoreOwnership( ZBS_TYPE_COMPANY ) )); } //} // =================================================== // ========== / #DB1LEGACY (TOMOVE) // =================================================== $res[] = $resArr; } } return $res; } /** * Returns a count of companies (owned) * .. inc by status * * @return int count */ public function getCompanyCount($args=array()){ #} ============ LOAD ARGS ============= $defaultArgs = array( // Search/Filtering (leave as false to ignore) 'withStatus' => false, // will be str if used // permissions 'ignoreowner' => true, // this'll let you not-check the owner of obj ); foreach ($defaultArgs as $argK => $argV){ $$argK = $argV; if (is_array($args) && isset($args[$argK])) { if (is_array($args[$argK])){ $newData = $$argK; if (!is_array($newData)) $newData = array(); foreach ($args[$argK] as $subK => $subV){ $newData[$subK] = $subV; }$$argK = $newData;} else { $$argK = $args[$argK]; } } } #} =========== / LOAD ARGS ============= $whereArr = array(); if ($withStatus !== false && !empty($withStatus)) $whereArr['status'] = array('zbsco_status','=','%s',$withStatus); return $this->DAL()->getFieldByWHERE(array( 'objtype' => ZBS_TYPE_COMPANY, 'colname' => 'COUNT(ID)', 'where' => $whereArr, 'ignoreowner' => $ignoreowner)); } /** * adds or updates a company object * * @param array $args Associative array of arguments * id (if update), owner, data (array of field data) * * @return int line ID */ public function addUpdateCompany($args=array()){ global $ZBSCRM_t,$wpdb,$zbs; #} Retrieve any cf $customFields = $this->DAL()->getActiveCustomFields(array('objtypeid'=>ZBS_TYPE_COMPANY)); $addrCustomFields = $this->DAL()->getActiveCustomFields(array('objtypeid'=>ZBS_TYPE_ADDRESS)); #} ============ LOAD ARGS ============= $defaultArgs = array( 'id' => -1, 'owner' => -1, // fields (directly) 'data' => array( 'status' => '', 'name' => '', 'email' => '', 'addr1' => '', 'addr2' => '', 'city' => '', 'county' => '', 'country' => '', 'postcode' => '', 'secaddr1' => '', 'secaddr2' => '', 'seccity' => '', 'seccounty' => '', 'seccountry' => '', 'secpostcode' => '', 'maintel' => '', 'sectel' => '', 'wpid' => '', 'avatar' => '', 'tw' => '', 'li' => '', 'fb' => '', 'lastcontacted' => '', // Note Custom fields may be passed here, but will not have defaults so check isset() // tags 'tags' => -1, // pass an array of tag ids or tag strings 'tag_mode' => 'replace', // replace|append|remove 'externalSources' => -1, // if this is an array(array('source'=>src,'uid'=>uid),multiple()) it'll add :) // allow this to be set for MS sync etc. 'created' => -1, 'lastupdated' => '', // obj links: 'contacts' => false, // array of id's ), 'limitedFields' => -1, // if this is set it OVERRIDES data (allowing you to set specific fields + leave rest in tact) // ^^ will look like: array(array('key'=>x,'val'=>y,'type'=>'%s')) // this function as DAL1 func did. 'extraMeta' => -1, 'automatorPassthrough' => -1, 'fallBackLog' => -1, 'silentInsert' => false, // this was for init Migration - it KILLS all IA for newCompany (because is migrating, not creating new :) this was -1 before 'do_not_update_blanks' => false // this allows you to not update fields if blank (same as fieldoverride for extsource -> in) ); foreach ($defaultArgs as $argK => $argV){ $$argK = $argV; if (is_array($args) && isset($args[$argK])) { if (is_array($args[$argK])){ $newData = $$argK; if (!is_array($newData)) $newData = array(); foreach ($args[$argK] as $subK => $subV){ $newData[$subK] = $subV; }$$argK = $newData;} else { $$argK = $args[$argK]; } } } // Needs this to grab custom fields (if passed) too :) if (is_array($customFields)) foreach ($customFields as $cK => $cF){ // only for data, limited fields below if (is_array($data)) { if (isset($args['data'][$cK])) $data[$cK] = $args['data'][$cK]; } } // this takes limited fields + checks through for custom fields present // (either as key zbsco_source or source, for example) // then switches them into the $data array, for separate update // where this'll fall over is if NO normal contact data is sent to update, just custom fields if (is_array($limitedFields) && is_array($customFields)){ //$customFieldKeys = array_keys($customFields); $newLimitedFields = array(); // cycle through foreach ($limitedFields as $field){ // some weird case where getting empties, so added check if (isset($field['key']) && !empty($field['key'])){ $dePrefixed = ''; // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase if ( str_starts_with( $field['key'], 'zbsco_' ) ) { $dePrefixed = substr( $field['key'], strlen( 'zbsco_' ) ); // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase } if (isset($customFields[$field['key']])){ // is custom, move to data $data[$field['key']] = $field['val']; } else if (!empty($dePrefixed) && isset($customFields[$dePrefixed])){ // is custom, move to data $data[$dePrefixed] = $field['val']; } else { // add it to limitedFields (it's not dealt with post-update) $newLimitedFields[] = $field; } } } // move this back in $limitedFields = $newLimitedFields; unset($newLimitedFields); } #} =========== / LOAD ARGS ============ #} ========== CHECK FIELDS ============ $id = (int)$id; // here we check that the potential owner CAN even own if ($owner > 0 && !user_can($owner,'admin_zerobs_usr')) $owner = -1; // if owner = -1, add current if (!isset($owner) || $owner === -1) { $owner = zeroBSCRM_user(); } if (is_array($limitedFields)){ // LIMITED UPDATE (only a few fields.) if (!is_array($limitedFields) || count ($limitedFields) <= 0) return false; // REQ. ID too (can only update) if (empty($id) || $id <= 0) return false; } else { // NORMAL, FULL UPDATE // check email + load that user if present if (!isset($data['email']) || empty($data['email'])){ // no email // Allow users without emails? WH removed this for db1->2 migration // leaving this in breaks MIGRATIONS from DAL 1 // in that those companies without emails will not be copied in // return false; } else { // email present, check if it matches ID? if (!empty($id) && $id > 0){ // if ID + email, check if existing contact with email, (e.g. in use) // ... allow it if the ID of that email contact matches the ID given here // (else e.g. add email x to ID y without checking) $potentialUSERID = (int)$this->getCompany(-1,array('email'=>$data['email'],'ignoreOwner'=>1,'onlyID'=>1)); if (!empty($potentialUSERID) && $potentialUSERID > 0 && $id > 0 && $potentialUSERID != $id){ // email doesn't match ID return false; } // also check if has rights?!? Could be just email passed here + therefor got around owner check? hmm. } else { // no ID, check if email present, and then update that co if so $potentialUSERID = (int)$this->getCompany(-1,array('email'=>$data['email'],'ignoreOwner'=>1,'onlyID'=>1)); if (isset($potentialUSERID) && !empty($potentialUSERID) && $potentialUSERID > 0) { $id = $potentialUSERID; } } } } #} If no status, and default is specified in settings, add that in :) if (is_null($data['status']) || !isset($data['status']) || empty($data['status'])){ // Default status for obj? -> this one gets for contacts -> $zbsCustomerMeta['status'] = zeroBSCRM_getSetting('defaultstatus'); } #} ========= / CHECK FIELDS =========== #} ========= OVERRIDE SETTING (Deny blank overrides) =========== // this only functions if externalsource is set (e.g. api/form, etc.) if (isset($data['externalSources']) && is_array($data['externalSources']) && count($data['externalSources']) > 0) { if (zeroBSCRM_getSetting('fieldoverride') == "1"){ $do_not_update_blanks = true; } } // either ext source + setting, or set by the func call if ($do_not_update_blanks){ // this setting says 'don't override filled-out data with blanks' // so here we check through any passed blanks + convert to limitedFields // only matters if $id is set (there is somt to update not add if (isset($id) && !empty($id) && $id > 0){ // get data to copy over (for now, this is required to remove 'fullname' etc.) $dbData = $this->db_ready_company($data); //unset($dbData['id']); // this is unset because we use $id, and is update, so not req. legacy issue //unset($dbData['created']); // this is unset because this uses an obj which has been 'updated' against original details, where created is output in the WRONG format :) $origData = $data; //$data = array(); $limitedData = array(); // array(array('key'=>'zbsco_x','val'=>y,'type'=>'%s')) // cycle through + translate into limitedFields (removing any blanks, or arrays (e.g. externalSources)) // we also have to remake a 'faux' data (removing blanks for tags etc.) for the post-update updates foreach ($dbData as $k => $v){ $intV = (int)$v; // only add if valuenot empty if (!is_array($v) && !empty($v) && $v != '' && $v !== 0 && $v !== -1 && $intV !== -1){ // add to update arr $limitedData[] = array( 'key' => 'zbsco_'.$k, // we have to add zbsco_ here because translating from data -> limited fields 'val' => $v, 'type' => $this->getTypeStr('zbsco_'.$k) ); // add to remade $data for post-update updates $data[$k] = $v; } } // copy over $limitedFields = $limitedData; } // / if ID } // / if do_not_update_blanks #} ========= / OVERRIDE SETTING (Deny blank overrides) =========== #} ========= BUILD DATA =========== $update = false; $dataArr = array(); $typeArr = array(); if (is_array($limitedFields)){ // LIMITED FIELDS $update = true; // cycle through foreach ($limitedFields as $field){ // some weird case where getting empties, so added check if (!empty($field['key'])){ $dataArr[$field['key']] = $field['val']; $typeArr[] = $field['type']; } } // add update time if (!isset($dataArr['zbsco_lastupdated'])){ $dataArr['zbsco_lastupdated'] = time(); $typeArr[] = '%d'; } } else { // FULL UPDATE/INSERT // contacts - avoid dupes if (isset($data['contacts']) && is_array($data['contacts'])){ $coArr = array(); foreach ($data['contacts'] as $c){ $cI = (int)$c; if ($cI > 0 && !in_array($cI, $coArr)) $coArr[] = $cI; } // reset the main if (count($coArr) > 0) $data['contacts'] = $coArr; else $data['contacts'] = 'unset'; unset($coArr); } // UPDATE $dataArr = array( // ownership // no need to update these (as of yet) - can't move teams etc. //'zbs_site' => zeroBSCRM_installSite(), //'zbs_team' => zeroBSCRM_installTeam(), //'zbs_owner' => $owner, 'zbsco_status' => $data['status'], 'zbsco_name' => $data['name'], 'zbsco_email' => $data['email'], 'zbsco_addr1' => $data['addr1'], 'zbsco_addr2' => $data['addr2'], 'zbsco_city' => $data['city'], 'zbsco_county' => $data['county'], 'zbsco_country' => $data['country'], 'zbsco_postcode' => $data['postcode'], 'zbsco_secaddr1' => $data['secaddr1'], 'zbsco_secaddr2' => $data['secaddr2'], 'zbsco_seccity' => $data['seccity'], 'zbsco_seccounty' => $data['seccounty'], 'zbsco_seccountry' => $data['seccountry'], 'zbsco_secpostcode' => $data['secpostcode'], 'zbsco_maintel' => $data['maintel'], 'zbsco_sectel' => $data['sectel'], 'zbsco_wpid' => $data['wpid'], 'zbsco_avatar' => $data['avatar'], 'zbsco_tw' => $data['tw'], 'zbsco_li' => $data['li'], 'zbsco_fb' => $data['fb'], 'zbsco_lastupdated' => time(), 'zbsco_lastcontacted' =>$data['lastcontacted'], ); $typeArr = array( // field data types //'%d', // site //'%d', // team //'%d', // owner '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%s', '%s', '%s', '%s', '%d', '%d', ); if (!empty($id) && $id > 0){ // is update $update = true; // got owner change? - allow all basically? if (isset($owner) && $owner > 0){ $dataArr['zbs_owner'] = $owner; $typeArr[] = '%d'; } } else { // INSERT (get's few extra :D) $update = false; $dataArr['zbs_site'] = zeroBSCRM_site(); $typeArr[] = '%d'; $dataArr['zbs_team'] = zeroBSCRM_team(); $typeArr[] = '%d'; $dataArr['zbs_owner'] = $owner; $typeArr[] = '%d'; if (isset($data['created']) && !empty($data['created']) && $data['created'] !== -1){ $dataArr['zbsco_created'] = $data['created'];$typeArr[] = '%d'; } else { $dataArr['zbsco_created'] = time(); $typeArr[] = '%d'; } } } #} ========= / BUILD DATA =========== #} ============================================================ #} ========= CHECK force_uniques & not_empty & max_len ======== // if we're passing limitedFields we skip these, for now // #v3.1 - would make sense to unique/nonempty check just the limited fields. #gh-145 if (!is_array($limitedFields)){ // verify uniques if (!$this->verifyUniqueValues($data,$id)) return false; // / fails unique field verify // verify not_empty if (!$this->verifyNonEmptyValues($data)) return false; // / fails empty field verify } // whatever we do we check for max_len breaches and abbreviate to avoid wpdb rejections $dataArr = $this->wpdbChecks($dataArr); #} ========= / CHECK force_uniques & not_empty ================ #} ============================================================ #} Check if ID present if ($update){ #} Check if obj exists (here) - for now just brutal update (will error when doesn't exist) $originalStatus = $this->getCompanyStatus($id); $previous_company_obj = $this->getCompany( $id ); // log any change of status if (isset($dataArr['zbsco_status']) && !empty($dataArr['zbsco_status']) && !empty($originalStatus) && $dataArr['zbsco_status'] != $originalStatus){ // status change $statusChange = array( 'from' => $originalStatus, 'to' => $dataArr['zbsco_status'] ); } #} Attempt update if ($wpdb->update( $ZBSCRM_t['companies'], $dataArr, array( // where 'ID' => $id ), $typeArr, array( // where data types '%d' )) !== false){ // if passing limitedFields instead of data, we ignore the following // this doesn't work, because data is in args default as arr //if (isset($data) && is_array($data)){ // so... if (!isset($limitedFields) || !is_array($limitedFields) || $limitedFields == -1){ // OBJ LINKS - contacts -> companies // This would link Company -> Contacts, $this->addUpdateObjectLinks($id,$data['contacts'],ZBS_TYPE_CONTACT); // ... but we need it Contacts -> Company, so use a direct call in this case: if (is_array($data['contacts'])) foreach ($data['contacts'] as $contactID){ // if is ID if ($contactID > 0){ // append to it's links (in case it has other companies too) $this->DAL()->addUpdateObjLinks(array( 'objtypefrom' => ZBS_TYPE_CONTACT, 'objtypeto' => ZBS_TYPE_COMPANY, 'objfromid' => $contactID, 'objtoids' => array($id), 'mode' => 'append')); } } // tags if (isset($data['tags']) && is_array($data['tags'])) { $this->addUpdateCompanyTags( array( 'id' => $id, 'tag_input' => $data['tags'], 'mode' => $data['tag_mode'] ) ); } // externalSources $approvedExternalSource = $this->DAL()->addUpdateExternalSources( array( 'obj_id' => $id, 'obj_type_id' => ZBS_TYPE_COMPANY, 'external_sources' => isset($data['externalSources']) ? $data['externalSources'] : array(), ) ); // for IA below // Custom fields? #} Cycle through + add/update if set if (is_array($customFields)) foreach ($customFields as $cK => $cF){ // any? if (isset($data[$cK])){ // add update $cfID = $this->DAL()->addUpdateCustomField(array( 'data' => array( 'objtype' => ZBS_TYPE_COMPANY, 'objid' => $id, 'objkey' => $cK, 'objval' => $data[$cK] ))); } } // Also got to catch any 'addr' custom fields :) if (is_array($addrCustomFields) && count($addrCustomFields) > 0){ // cycle through addr custom fields + save // see #ZBS-518, not easy until addr's get DAL2 // WH deferring here // WH later added via the addUpdateContactField method - should work fine if we catch properly in get foreach ($addrCustomFields as $cK => $cF){ // v2: //$cKN = (int)$cK+1; //$cKey = 'addr_cf'.$cKN; //$cKey2 = 'secaddr_cf'.$cKN; // v3: $cKey = 'addr_'.$cK; $cKey2 = 'secaddr_'.$cK; // any? if (isset($data[$cKey])){ // add update $cfID = $this->DAL()->addUpdateCustomField(array( 'data' => array( 'objtype' => ZBS_TYPE_COMPANY, 'objid' => $id, 'objkey' => $cKey, 'objval' => $data[$cKey] ))); } // any? if (isset($data[$cKey2])){ // add update $cfID = $this->DAL()->addUpdateCustomField(array( 'data' => array( 'objtype' => ZBS_TYPE_COMPANY, 'objid' => $id, 'objkey' => $cKey2, 'objval' => $data[$cKey2] ))); } } } // / Custom Fields } // / if $data #} Any extra meta keyval pairs? // BRUTALLY updates (no checking) $confirmedExtraMeta = false; if ( is_array( $extraMeta ) ) { // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase,VariableAnalysis.CodeAnalysis.VariableAnalysis.UndefinedVariable $confirmedExtraMeta = array(); foreach ($extraMeta as $k => $v){ #} This won't fix stupid keys, just catch basic fails... $cleanKey = strtolower(str_replace(' ','_',$k)); #} Brutal update //update_post_meta($postID, 'zbs_customer_extra_'.$cleanKey, $v); $this->DAL()->updateMeta(ZBS_TYPE_COMPANY,$id,'extra_'.$cleanKey,$v); #} Add it to this, which passes to IA $confirmedExtraMeta[$cleanKey] = $v; } } #} INTERNAL AUTOMATOR #} & #} FALLBACKS // UPDATING CONTACT if (!$silentInsert){ #} FALLBACK #} (This fires for customers that weren't added because they already exist.) #} e.g. x@g.com exists, so add log "x@g.com filled out form" #} Requires a type and a shortdesc if ( is_array( $fallBackLog ) && ! empty( $fallBackLog['type'] ) && ! empty( $fallBackLog['shortdesc'] ) // phpcs:ignore VariableAnalysis.CodeAnalysis.VariableAnalysis.UndefinedVariable,WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase ){ #} Brutal add, maybe validate more?! #} Long desc if present: $zbsNoteLongDesc = ''; if (isset($fallBackLog['longdesc']) && !empty($fallBackLog['longdesc'])) $zbsNoteLongDesc = $fallBackLog['longdesc']; #} Only raw checked... but proceed. zeroBS_addUpdateObjLog( ZBS_TYPE_COMPANY, $id, -1, -1, array( // Anything here will get wrapped into an array and added as the meta vals 'type' => $fallBackLog['type'], // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase,VariableAnalysis.CodeAnalysis.VariableAnalysis.UndefinedVariable 'shortdesc' => $fallBackLog['shortdesc'], // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase,VariableAnalysis.CodeAnalysis.VariableAnalysis.UndefinedVariable 'longdesc' => $zbsNoteLongDesc, // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase ) ); } // catch dirty flag (update of status) (note, after update_post_meta - as separate) //if (isset($_POST['zbsco_status_dirtyflag']) && $_POST['zbsco_status_dirtyflag'] == "1"){ // actually here, it's set above if (isset($statusChange) && is_array($statusChange)){ // status has changed // IA zeroBSCRM_FireInternalAutomator('company.status.update',array( 'id'=>$id, 'againstid' => $id, 'data'=> $dataArr, 'from' => $statusChange['from'], 'to' => $statusChange['to'] )); } // IA General company update (2.87+) zeroBSCRM_FireInternalAutomator('company.update',array( 'id'=>$id, 'againstid' => $id, 'data' => $dataArr, // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase 'prev_company' => $previous_company_obj, )); } // Successfully updated - Return id return $id; } else { $msg = __('DB Update Failed','zero-bs-crm'); $zbs->DAL->addError(302,$this->objectType,$msg,$dataArr); // FAILED update return false; } } else { #} No ID - must be an INSERT if ($wpdb->insert( $ZBSCRM_t['companies'], $dataArr, $typeArr ) > 0){ #} Successfully inserted, lets return new ID $newID = $wpdb->insert_id; // OBJ LINKS - contacts -> companies // This would link Company -> Contacts, $this->addUpdateObjectLinks($newID,$data['contacts'],ZBS_TYPE_CONTACT); // ... but we need it Contacts -> Company, so use a direct call in this case: if (is_array($data['contacts'])) foreach ($data['contacts'] as $contactID){ // if is ID if ($contactID > 0){ // append to it's links (in case it has other companies too) $this->DAL()->addUpdateObjLinks(array( 'objtypefrom' => ZBS_TYPE_CONTACT, 'objtypeto' => ZBS_TYPE_COMPANY, 'objfromid' => $contactID, 'objtoids' => array($newID), 'mode' => 'append')); } } // tags if (isset($data['tags']) && is_array($data['tags'])) { $this->addUpdateCompanyTags( array( 'id' => $newID, 'tag_input' => $data['tags'], 'mode' => $data['tag_mode'] ) ); } // externalSources $approvedExternalSource = $this->DAL()->addUpdateExternalSources( array( 'obj_id' => $newID, 'obj_type_id' => ZBS_TYPE_COMPANY, 'external_sources' => isset($data['externalSources']) ? $data['externalSources'] : array(), ) ); // for IA below // Custom fields? #} Cycle through + add/update if set if (is_array($customFields)) foreach ($customFields as $cK => $cF){ // any? if (isset($data[$cK])){ // add update $cfID = $this->DAL()->addUpdateCustomField(array( 'data' => array( 'objtype' => ZBS_TYPE_COMPANY, 'objid' => $newID, 'objkey' => $cK, 'objval' => $data[$cK] ))); } } // / Custom Fields // Also got to catch any 'addr' custom fields :) if (is_array($addrCustomFields) && count($addrCustomFields) > 0){ foreach ($addrCustomFields as $cK => $cF){ $cKey = 'addr_'.$cK; $cKey2 = 'secaddr_'.$cK; if (isset($data[$cKey])){ // add update $cfID = $this->DAL()->addUpdateCustomField(array( 'data' => array( 'objtype' => ZBS_TYPE_COMPANY, 'objid' => $newID, 'objkey' => $cKey, 'objval' => $data[$cKey] ))); } // any? if (isset($data[$cKey2])){ // add update $cfID = $this->DAL()->addUpdateCustomField(array( 'data' => array( 'objtype' => ZBS_TYPE_COMPANY, 'objid' => $newID, 'objkey' => $cKey2, 'objval' => $data[$cKey2] ))); } } } #} Any extra meta keyval pairs? // BRUTALLY updates (no checking) $confirmedExtraMeta = false; if ( is_array( $extraMeta ) ) { // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase,VariableAnalysis.CodeAnalysis.VariableAnalysis.UndefinedVariable $confirmedExtraMeta = array(); foreach ($extraMeta as $k => $v){ #} This won't fix stupid keys, just catch basic fails... $cleanKey = strtolower(str_replace(' ','_',$k)); #} Brutal update //update_post_meta($postID, 'zbs_customer_extra_'.$cleanKey, $v); $this->DAL()->updateMeta(ZBS_TYPE_COMPANY,$newID,'extra_'.$cleanKey,$v); #} Add it to this, which passes to IA $confirmedExtraMeta[$cleanKey] = $v; } } #} INTERNAL AUTOMATOR #} & #} FALLBACKS // NEW CONTACT if (!$silentInsert){ #} Add to automator zeroBSCRM_FireInternalAutomator('company.new',array( 'id'=>$newID, 'data'=>$dataArr, 'extsource'=>$approvedExternalSource, 'automatorpassthrough'=>$automatorPassthrough, #} This passes through any custom log titles or whatever into the Internal automator recipe. 'extraMeta'=>$confirmedExtraMeta #} This is the "extraMeta" passed (as saved) )); } return $newID; } else { $msg = __('DB Insert Failed','zero-bs-crm'); $zbs->DAL->addError(303,$this->objectType,$msg,$dataArr); #} Failed to Insert return false; } } return false; } /** * adds or updates a company's tags * ... this is really just a wrapper for addUpdateObjectTags * * @param array $args Associative array of arguments * id (if update), owner, data (array of field data) * * @return int line ID */ public function addUpdateCompanyTags($args=array()){ global $ZBSCRM_t,$wpdb; #} ============ LOAD ARGS ============= $defaultArgs = array( 'id' => -1, // co id // generic pass-through (array of tag strings or tag IDs): 'tag_input' => -1, // or either specific: 'tagIDs' => -1, 'tags' => -1, 'mode' => 'append' ); foreach ($defaultArgs as $argK => $argV){ $$argK = $argV; if (is_array($args) && isset($args[$argK])) { if (is_array($args[$argK])){ $newData = $$argK; if (!is_array($newData)) $newData = array(); foreach ($args[$argK] as $subK => $subV){ $newData[$subK] = $subV; }$$argK = $newData;} else { $$argK = $args[$argK]; } } } #} =========== / LOAD ARGS ============ #} ========== CHECK FIELDS ============ // check id $id = (int)$id; if (empty($id) || $id <= 0) return false; #} ========= / CHECK FIELDS =========== return $this->DAL()->addUpdateObjectTags( array( 'objtype' => ZBS_TYPE_COMPANY, 'objid' => $id, 'tag_input' => $tag_input, 'tags' => $tags, 'tagIDs' => $tagIDs, 'mode' => $mode ) ); } /** * deletes a company object * * @param array $args Associative array of arguments * id * * @return int success; */ public function deleteCompany($args=array()){ global $ZBSCRM_t,$wpdb,$zbs; #} ============ LOAD ARGS ============= $defaultArgs = array( 'id' => -1, 'saveOrphans' => true ); foreach ($defaultArgs as $argK => $argV){ $$argK = $argV; if (is_array($args) && isset($args[$argK])) { if (is_array($args[$argK])){ $newData = $$argK; if (!is_array($newData)) $newData = array(); foreach ($args[$argK] as $subK => $subV){ $newData[$subK] = $subV; }$$argK = $newData;} else { $$argK = $args[$argK]; } } } #} =========== / LOAD ARGS ============ #} Check ID & Delete :) $id = (int)$id; if (!empty($id) && $id > 0) { // delete orphans? if ($saveOrphans === false){ // delete any tag links $this->DAL()->deleteTagObjLinks(array( 'objtype' => ZBS_TYPE_COMPANY, 'objid' => $id )); // delete any external source information $this->DAL()->delete_external_sources( array( 'obj_type' => ZBS_TYPE_COMPANY, 'obj_id' => $id, 'obj_source' => 'all', )); } $del = zeroBSCRM_db2_deleteGeneric($id,'companies'); #} Add to automator zeroBSCRM_FireInternalAutomator('company.delete',array( 'id'=>$id, 'saveOrphans'=>$saveOrphans )); return $del; } return false; } /** * tidy's the object from wp db into clean array * * @param array $obj (DB obj) * * @return array company (clean obj) */ private function tidy_company($obj=false,$withCustomFields=false){ $res = false; if (isset($obj->ID)){ $res = array(); $res['id'] = $obj->ID; /* `zbs_site` INT NULL DEFAULT NULL, `zbs_team` INT NULL DEFAULT NULL, `zbs_owner` INT NOT NULL, */ $res['owner'] = $obj->zbs_owner; $res['status'] = $this->stripSlashes($obj->zbsco_status); $res['name'] = $this->stripSlashes($obj->zbsco_name); $res['email'] = $this->stripSlashes($obj->zbsco_email); $res['addr1'] = $this->stripSlashes($obj->zbsco_addr1); $res['addr2'] = $this->stripSlashes($obj->zbsco_addr2); $res['city'] = $this->stripSlashes($obj->zbsco_city); $res['county'] = $this->stripSlashes($obj->zbsco_county); $res['country'] = $this->stripSlashes($obj->zbsco_country); $res['postcode'] = $this->stripSlashes($obj->zbsco_postcode); $res['secaddr1'] = $this->stripSlashes($obj->zbsco_secaddr1); $res['secaddr2'] = $this->stripSlashes($obj->zbsco_secaddr2); $res['seccity'] = $this->stripSlashes($obj->zbsco_seccity); $res['seccounty'] = $this->stripSlashes($obj->zbsco_seccounty); $res['seccountry'] = $this->stripSlashes($obj->zbsco_seccountry); $res['secpostcode'] = $this->stripSlashes($obj->zbsco_secpostcode); $res['maintel'] = $this->stripSlashes($obj->zbsco_maintel); $res['sectel'] = $this->stripSlashes($obj->zbsco_sectel); $res['wpid'] = (int)$obj->zbsco_wpid; $res['avatar'] = $this->stripSlashes($obj->zbsco_avatar); $res['tw'] = $this->stripSlashes($obj->zbsco_tw); $res['li'] = $this->stripSlashes($obj->zbsco_li); $res['fb'] = $this->stripSlashes($obj->zbsco_fb); $res['created'] = (int)$obj->zbsco_created; $res['created_date'] = (isset($obj->zbsco_created) && $obj->zbsco_created > 0) ? zeroBSCRM_date_i18n(-1,$obj->zbsco_created,false,true) : false; $res['lastupdated'] = (int)$obj->zbsco_lastupdated; $res['lastupdated_date'] = (isset($obj->zbsco_lastupdated) && $obj->zbsco_lastupdated > 0) ? zeroBSCRM_date_i18n(-1,$obj->zbsco_lastupdated,false,true) : false; $res['lastcontacted'] = (int)$obj->zbsco_lastcontacted; $res['lastcontacted_date'] = (isset($obj->zbsco_lastcontacted) && $obj->zbsco_lastcontacted > 0) ? zeroBSCRM_date_i18n(-1,$obj->zbsco_lastcontacted,false,true) : false; // if have totals, pass them :) if (isset($obj->quotes_total)) $res['quotes_total'] = $obj->quotes_total; if ( isset( $obj->invoices_total ) ) { $res['invoices_total'] = $obj->invoices_total; $res['invoices_total_inc_deleted'] = $obj->invoices_total_inc_deleted; $res['invoices_count'] = $obj->invoices_count; $res['invoices_count_inc_deleted'] = $obj->invoices_count_inc_deleted; } if ( isset( $obj->transactions_total ) ) { $res['transactions_total'] = $obj->transactions_total; } if (isset($obj->transactions_paid_total)) $res['transactions_paid_total'] = $obj->transactions_paid_total; // and if have invs + trans totals, add to make total val // This now accounts for "part payments" where trans are part/whole payments against invs if (isset($res['invoices_total']) || isset($res['transactions_total'])){ $res['total_value'] = jpcrm_get_total_value_from_contact_or_company( $res ); } // custom fields - tidy any that are present: if ($withCustomFields) $res = $this->tidyAddCustomFields(ZBS_TYPE_COMPANY,$obj,$res,true); } return $res; } /** * Wrapper, use $this->getCompanyMeta($contactID,$key) for easy retrieval of singular company * Simplifies $this->getMeta * * @param int objtype * @param int objid * @param string key * * @return array company meta result */ public function getCompanyMeta($id=-1,$key='',$default=false){ global $zbs; if (!empty($key)){ return $this->DAL()->getMeta(array( 'objtype' => ZBS_TYPE_COMPANY, 'objid' => $id, 'key' => $key, 'fullDetails' => false, 'default' => $default, 'ignoreowner' => true // for now !! )); } return $default; } /** * Returns an email addr against a Company * * @param int id Company ID * * @return string Company email */ public function getCompanyEmail($id=-1){ global $zbs; $id = (int)$id; if ($id > 0){ return $this->DAL()->getFieldByID(array( 'id' => $id, 'objtype' => ZBS_TYPE_COMPANY, 'colname' => 'zbsco_email', 'ignoreowner' => true)); } return false; } /** * Returns an ownerid against a company * * @param int id company ID * * @return int company owner id */ public function getCompanyOwner($id=-1){ global $zbs; $id = (int)$id; if ($id > 0){ return $this->DAL()->getFieldByID(array( 'id' => $id, 'objtype' => ZBS_TYPE_COMPANY, 'colname' => 'zbs_owner', 'ignoreowner'=>true)); } return false; } /** * Returns an status against a company * * @param int $id company ID. * * @return string company status string */ public function getCompanyStatus($id=-1){ global $zbs; $id = (int)$id; if ($id > 0){ return $this->DAL()->getFieldByID(array( 'id' => $id, 'objtype' => ZBS_TYPE_COMPANY, 'colname' => 'zbsco_status', 'ignoreowner'=>true)); } return false; } /** * Returns a formatted fullname (optionally including ID + first line of addr) * Replaces zeroBS_companyName * * @param int id Company ID * @param array Company array (if already loaded can pass) * @param array args (see format_fullname func) * * @return string Company full name */ public function getCompanyNameEtc($id=-1,$companyArr=false,$args=array()){ global $zbs; $id = (int)$id; // this makes sure it uses name not 'fname' $args['company'] = true; if ($id > 0){ // get a limited-fields contact obj $company = $zbs->DAL->companies->getCompany($id,array('withCustomFields' => false,'fields'=>array('zbsco_addr1','zbsco_name'),'ignoreowner' => true)); if (isset($company) && is_array($company) && isset($company['name'])) return $this->format_name_etc($company,$args); } elseif (is_array($companyArr)){ // pass through return $this->format_name_etc($companyArr,$args); } return false; } /** * Returns a formatted address of a contact * Replaces zeroBS_companyAddr * * @param int id Company ID * @param array Company array (if already loaded can pass) * @param array args (see format_address func) * * @return string Company addr html */ public function getCompanyAddress($id=-1,$companyArr=false,$args=array()){ global $zbs; $id = (int)$id; if ($id > 0){ // get a limited-fields company obj // this is hacky, but basically get whole basic company record for this for now, because // this doesn't properly get addr custom fields: // $company = $this->getContact($id,array('withCustomFields' => false,'fields'=>$this->field_list_address2,'ignoreowner'=>true)); $company = $this->getCompany($id,array('withCustomFields' => true,'ignoreowner'=>true)); if (isset($company) && is_array($company) && isset($company['addr1'])) return $this->format_address($company,$args); } elseif (is_array($companyArr)){ // pass through return $this->format_address($companyArr,$args); } return false; } /** * Returns a formatted address of a Company (2nd addr) * Replaces zeroBS_companySecondAddr * * @param int id Company ID * @param array Company array (if already loaded can pass) * @param array args (see format_address func) * * @return string Company addr html */ public function getCompany2ndAddress($id=-1,$companyArr=false,$args=array()){ global $zbs; $id = (int)$id; $args['secondaddr'] = true; if ($id > 0){ // get a limited-fields company obj // this is hacky, but basically get whole basic company record for this for now, because // this doesn't properly get addr custom fields: // $company = $this->getContact($id,array('withCustomFields' => false,'fields'=>$this->field_list_address2,'ignoreowner'=>true)); $company = $this->getCompany($id,array('withCustomFields' => true,'ignoreowner'=>true)); if (isset($company) && is_array($company) && isset($company['addr1'])) return $this->format_address($company,$args); } elseif (is_array($companyArr)){ // pass through return $this->format_address($companyArr,$args); } return false; } /** * Returns a Company's tag array * * @param int id Company ID * * @return mixed */ public function getCompanyTags($id=-1){ global $zbs; $id = (int)$id; if ($id > 0){ return $this->DAL()->getTagsForObjID(array('objtypeid'=>ZBS_TYPE_COMPANY,'objid'=>$id)); } return false; } /** * Returns last contacted uts against a Company * * @param int id Company ID * * @return int Contact last contacted date as uts (or -1) */ public function getCompanyLastContactUTS($id=-1){ global $zbs; $id = (int)$id; if ($id > 0){ return $this->DAL()->getFieldByID(array( 'id' => $id, 'objtype' => ZBS_TYPE_COMPANY, 'colname' => 'zbsco_lastcontacted', 'ignoreowner' => true)); } return false; } /** * updates lastcontacted date for a Company * * @param int id Company ID * @param int uts last contacted * * @return bool */ public function setCompanyLastContactUTS($id=-1,$lastContactedUTS=-1){ global $zbs; $id = (int)$id; if ($id > 0){ return $this->addUpdateCompany(array( 'id'=>$id, 'limitedFields'=>array( array('key'=>'zbsco_lastcontacted','val' => $lastContactedUTS,'type' => '%d') ))); } return false; } /** * Returns a set of social accounts for a Company (tw,li,fb) * * @param int id Company ID * * @return array social acc's */ // this is not used yet, ahead of time :) - will work tho ;) public function getCompanySocials($id=-1){ global $zbs; $id = (int)$id; if ($id > 0){ // lazy 3 queries, optimise later $tw = $this->DAL()->getFieldByID(array( 'id' => $id, 'objtype' => ZBS_TYPE_COMPANY, 'colname' => 'zbsco_tw', 'ignoreowner' => true)); $li = $this->DAL()->getFieldByID(array( 'id' => $id, 'objtype' => ZBS_TYPE_COMPANY, 'colname' => 'zbsco_li', 'ignoreowner' => true)); $fb = $this->DAL()->getFieldByID(array( 'id' => $id, 'objtype' => ZBS_TYPE_COMPANY, 'colname' => 'zbsco_fb', 'ignoreowner' => true)); return array('tw'=>$tw,'li' => $li, 'fb' => $fb); } return false; } /** * Returns the next company ID and the previous company ID * Used for the navigation between companies. * * @param int id * * @return array int id */ public function getCompanyPrevNext($id=-1){ global $ZBSCRM_t, $wpdb; if($id > 0){ //then run the queries.. $nextSQL = $this->prepare("SELECT MIN(ID) FROM ".$ZBSCRM_t['companies']." WHERE ID > %d", $id); $res['next'] = $wpdb->get_var($nextSQL); $prevSQL = $this->prepare("SELECT MAX(ID) FROM ".$ZBSCRM_t['companies']." WHERE ID < %d", $id); $res['prev'] = $wpdb->get_var($prevSQL); return $res; } return false; } /** * Takes full object and makes a "list view" boiled down version * Used to generate listview objs * * @param array $obj (clean obj) * * @return array (listview ready obj) */ public function listViewObj($company=false,$columnsRequired=array()){ if (is_array($company) && isset($company['id'])){ // copy whole obj $resArr = $company; // here I've translated from DAL2 version (from AJAX) so commented out is no longer req. // ... or they'll naturally be in DAL3 model already //$resArr['id'] = $company['id']; //$resArr['name'] = $company['coname']; $resArr['avatar'] = false; // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase #} Format the date in the list view.. //$formatted_date = zeroBSCRM_date_i18n(-1, strtotime($obj['created'])); // let it use proper obj. //$resArr['added'] = $company['created_date']; #} Custom columns #} Tags if (in_array('tagged', $columnsRequired)){ $resArr['tags'] = $company['tags']; } // Total value if (in_array('totalvalue', $columnsRequired)){ #} Calc total value + add to return array $resArr['totalvalue'] = zeroBSCRM_formatCurrency(0); if (isset($company['total_value'])) $resArr['totalvalue'] = zeroBSCRM_formatCurrency($company['total_value']); } // When Company<->Quotes: // Quotes /*if ( in_array( 'quotetotal', $columnsRequired ) ) { if ( isset( $company['quotes_total'] ) ) { $resArr['quotestotal'] = zeroBSCRM_formatCurrency( $company['quotes_total'] ); } else { $resArr['quotestotal'] = zeroBSCRM_formatCurrency( 0 ); } }*/ // Invoices if ( in_array('invoicetotal', $columnsRequired ) ) { if ( isset( $company['invoices_total'] ) ) { $resArr['invoicestotal'] = zeroBSCRM_formatCurrency( $company['invoices_total'] ); // also pass total without formatting (used for hasinvoices check) $resArr['invoices_total_value'] = $company['invoices_total']; } else { $resArr['invoicestotal'] = zeroBSCRM_formatCurrency( 0 ); } } // Transactions if (in_array('transactiontotal', $columnsRequired)){ $resArr['transactionstotal'] = zeroBSCRM_formatCurrency(0); if (isset($company['transactions_value'])) $resArr['transactionstotal'] = zeroBSCRM_formatCurrency($company['transactions_value']); } // v3.0 if (isset($company['transactions_total'])){ // DAL2 way, brutal effort. $resArr['transactions_total'] = zeroBSCRM_formatCurrency($company['transactions_total']); // also pass total without formatting (used for hastransactions check) $resArr['transactions_total_value'] = $company['transactions_total']; } // avatar mode $avatarMode = zeroBSCRM_getSetting( 'avatarmode' ); #} Contacts at company $contactsAtCo = zeroBS_getCustomers(true,1000,0,false,false,'',false,false,$company['id']); // build as str $resArr['contacts'] = ''; // when a company had many contacts the UI was breached, here we max out at 4... $contactCount = 0; foreach ( $contactsAtCo as $contact ){ // stop at 4 if ($contactCount >= 4){ $resArr['contacts'] .= '...'; break; } // add avatar/label if ( $avatarMode !== 3 ) { $resArr['contacts'] .= zeroBS_getCustomerIcoLinked($contact['id']); // or zeroBS_getCustomerIcoLinkedLabel? } else { // no avatars, use labels $resArr['contacts'] .= zeroBS_getCustomerLinkedLabel($contact['id']); } $contactCount++; } return $resArr; } return false; } /** * remove any non-db fields from the object * basically takes array like array('owner'=>1,'fname'=>'x','fullname'=>'x') * and returns array like array('owner'=>1,'fname'=>'x') * This does so based on the objectModel! * * @param array $obj (clean obj) * * @return array (db ready arr) */ private function db_ready_company($obj=false){ // use the generic? (override here if necessary) return $this->db_ready_obj($obj); } // =============================================================================== // ============ Formatting =================================================== /** * Returns a formatted company name +- id, address (e.g. Automattic Ltd. 12 London Street #23) * * @param array $obj (tidied db obj) * * @return string name */ public function format_name_etc( $companyArr=array(), $args=array() ){ #} =========== LOAD ARGS ============== $defaultArgs = array( 'incFirstLineAddr' => false, 'incID' => false ); foreach ($defaultArgs as $argK => $argV){ $$argK = $argV; if (is_array($args) && isset($args[$argK])) { if (is_array($args[$argK])){ $newData = $$argK; if (!is_array($newData)) $newData = array(); foreach ($args[$argK] as $subK => $subV){ $newData[$subK] = $subV; }$$argK = $newData;} else { $$argK = $args[$argK]; } } } #} =========== / LOAD ARGS ============= $str = ''; if (isset($companyArr['name'])) $str = $companyArr['name']; // First line of addr? if ($incFirstLineAddr) if (isset($companyArr['addr1']) && !empty($companyArr['addr1'])) $str .= ' ('.$companyArr['addr1'].')'; // ID? if ($incID) $str .= ' #'.$companyArr['id']; return $str; } // =========== / Formatting =================================================== // =============================================================================== } // / class