<?php
/*!
* Jetpack CRM
* https://jetpackcrm.com
* V3.0+
*
* Copyright 2020 Automattic
*
* Date: 14/01/19
*/
// phpcs:disable Generic.WhiteSpace.DisallowSpaceIndent.SpacesUsed, WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase
defined( 'ZEROBSCRM_PATH' ) || exit( 0 );
use Automattic\Jetpack\CRM\Event_Manager\Events_Manager;
/**
* ZBS DAL >> Transactions
*
* @author Woody Hayday <hello@jetpackcrm.com>
* @version 2.0
* @access public
* @see https://jetpackcrm.com/kb
*/
class zbsDAL_transactions extends zbsDAL_ObjectLayer {
protected $objectType = ZBS_TYPE_TRANSACTION;
protected $objectDBPrefix = 'zbst_';
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' => 'zbst_status', 'format' => 'str',
// output model
'input_type' => 'select',
'label' => 'Status',
'placeholder'=>'',
'options'=>array('Succeeded','Completed','Failed','Refunded','Processing','Pending','Hold','Cancelled'),
'essential' => true,
'max_len' => 50
),
'type' => array(
// db model:
'fieldname' => 'zbst_type', 'format' => 'str',
// output model
'input_type' => 'select',
'label' => 'Type',
'placeholder'=>'',
'options'=>array('Sale','Refund','Credit Note'),
'essential' => true,
'default' => 'Sale',
'max_len' => 50
),
'ref' => array(
// db model:
'fieldname' => 'zbst_ref', 'format' => 'str',
// output model
'input_type' => 'text',
'label' => 'Transaction ID',
'placeholder'=>'',
'essential' => true,
'dal1key' => 'orderid',
'force_unique' => true, // must be unique. This is required and breaking if true,
'not_empty' => true,
'max_len' => 120
),
'origin' => array(
'fieldname' => 'zbst_origin',
'format' => 'str',
'max_len' => 100
),
'parent' => array('fieldname' => 'zbst_parent', 'format' => 'int'),
'hash' => array('fieldname' => 'zbst_hash', 'format' => 'str'),
'title' => array(
// db model:
'fieldname' => 'zbst_title', 'format' => 'str',
// output model
'input_type' => 'text',
'label' => 'Transaction Title',
'placeholder' => 'e.g. Product ABC',
'dal1key' => 'item',
'max_len' => 200
),
'desc' => array(
// db model:
'fieldname' => 'zbst_desc', 'format' => 'str',
// output model
'input_type' => 'textarea',
'label' => 'Description',
'placeholder' => '',
'max_len' => 200
),
'date' => array(
// db model:
'fieldname' => 'zbst_date', 'format' => 'uts',
'autoconvert'=>'datetime', // NOTE autoconvert makes buildObjArr autoconvert from a 'date' using localisation rules, to a GMT timestamp (UTS)
// output model
'input_type' => 'datetime',
'label' => 'Transaction Date',
'placeholder'=>''
), // <-- this is a bit of a misnomer, it's basically timestamp for created
'customer_ip' => array(
'fieldname' => 'zbst_customer_ip',
'format' => 'str',
'max_len' => 45
),
'currency' => array(
'fieldname' => 'zbst_currency',
'format' => 'curr',
'max_len' => 4
),
'net' => array(
// db model:
'fieldname' => 'zbst_net', 'format' => 'decimal',
// output model
'input_type' => 'price',
'label' => 'Net',
'placeholder'=>'',
'default' => '0.00'
),
'fee' => array(
// db model:
'fieldname' => 'zbst_fee', 'format' => 'decimal',
// output model
'input_type' => 'price',
'label' => 'Fee',
'placeholder'=>'',
'default' => '0.00'
),
'discount' => array(
// db model:
'fieldname' => 'zbst_discount', 'format' => 'decimal',
// output model
'input_type' => 'price',
'label' => 'Discount',
'placeholder'=>'',
'default' => '0.00'
),
'shipping' => array(
// db model:
'fieldname' => 'zbst_shipping', 'format' => 'decimal',
// output model
'input_type' => 'price',
'label' => 'Shipping',
'placeholder'=>'',
'default' => '0.00'
),
'shipping_taxes' => array(
// db model:
'fieldname' => 'zbst_shipping_taxes', 'format' => 'str',
// output model
'input_type' => 'tax',
'label' => 'Shipping Taxes',
'placeholder'=>''
),
'shipping_tax' => array('fieldname' => 'zbst_shipping_tax', 'format' => 'decimal', 'label' => 'Shipping Tax',),
'taxes' => array(
// db model:
'fieldname' => 'zbst_taxes',
'format' => 'str',
// output model
'input_type' => 'tax',
'label' => 'Taxes',
'placeholder'=>''
// replaces tax_rate, but tax_rate was a decimal, this is a string of applicable tax codes from tax table.
),
'tax' => array(
'fieldname' => 'zbst_tax',
'format' => 'decimal',
'label' => 'Tax',
'input_type' => 'price',
'placeholder'=>'',
),
'total' => array(
// db model:
'fieldname' => 'zbst_total', 'format' => 'decimal',
// output model
'input_type' => 'price',
'label' => 'Total',
'placeholder'=>'',
'default' => '0.00',
'essential' => true
),
'date_paid' => array(
// db model:
'fieldname' => 'zbst_date_paid', 'format' => 'uts',
'autoconvert'=>'date', // NOTE autoconvert makes buildObjArr autoconvert from a 'date' using localisation rules, to a GMT timestamp (UTS)
// output model
'input_type' => 'date',
'label' => 'Date Paid',
),
'date_completed' => array(
// db model:
'fieldname' => 'zbst_date_completed', 'format' => 'uts',
'autoconvert'=>'date', // NOTE autoconvert makes buildObjArr autoconvert from a 'date' using localisation rules, to a GMT timestamp (UTS)
// output model
'input_type' => 'date',
'label' => 'Date Completed'
),
'created' => array('fieldname' => 'zbst_created', 'format' => 'uts'),
'lastupdated' => array('fieldname' => 'zbst_lastupdated', 'format' => 'uts'),
);
// hardtyped list of types this object type is commonly linked to
protected $linkedToObjectTypes = array(
ZBS_TYPE_CONTACT,
ZBS_TYPE_COMPANY
);
/**
* Events_Manager instance. Manages CRM events.
*
* @since 6.2.0
*
* @var Events_Manager
*/
private $events_manager;
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 =============
$this->events_manager = new Events_Manager();
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;
// Add statuses if enabled.
if ( $zbs->settings->get( 'filtersfromstatus' ) === 1 ) {
$statuses = zeroBSCRM_getTransactionsStatuses( true );
foreach ( $statuses as $status ) {
$listview_filters[ ZBS_TYPE_TRANSACTION ]['status'][ 'status_' . $status ] = $status;
}
}
return $listview_filters;
}
// ===============================================================================
// =========== TRANSACTION =======================================================
// 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->getTransaction($ID);
}
// generic get (by ID list)
// Super simplistic wrapper used by MVP Export v3.0
public function getIDList($IDs=false){
return $this->getTransactions(array(
'inArr' => $IDs,
'withOwner' => true,
'withAssigned' => true,
'page' => -1,
'perPage' => -1
));
}
// generic get (EVERYTHING)
// expect heavy load!
public function getAll($IDs=false){
return $this->getTransactions(array(
'withOwner' => true,
'withAssigned' => true,
'sortByField' => 'ID',
'sortOrder' => 'ASC',
'page' => -1,
'perPage' => -1,
));
}
// generic get count of (EVERYTHING)
public function getFullCount(){
return $this->getTransactions(array(
'count' => true,
'page' => -1,
'perPage' => -1,
));
}
/**
* Verifies there is a transaction with this ID accessible to current logged in user
*
* @param int transaction_id
*
* @return bool
*/
public function transaction_exists( $transaction_id ){
// has to be a legit int
if ( empty( $transaction_id ) ){
return false;
}
// note this ignores ownership for now
$potential_transaction = $this->getTransaction( $transaction_id, array( 'onlyID' => true ) );
if ( $potential_transaction > 0 ){
return true;
}
return false;
}
/**
* returns full transaction line +- details
*
* @param int id transaction id
* @param array $args Associative array of arguments
*
* @return array transaction object
*/
public function getTransaction($id=-1,$args=array()){
global $zbs;
#} =========== LOAD ARGS ==============
$defaultArgs = array(
// if these two passed, will search based on these
'externalSource' => false,
'externalSourceUID' => false,
// with what?
'withLineItems' => true,
'withCustomFields' => true,
'withAssigned' => true, // return ['contact'] & ['company'] arrays, & invoice_id field, if has link
'withTags' => false,
'withOwner' => false,
// permissions
'ignoreowner' => zeroBSCRM_DAL2_ignoreOwnership(ZBS_TYPE_TRANSACTION), // 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($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_TRANSACTION));
#} 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 = transactions.ID AND zbscf_objkey = %s AND zbscf_objtype = %d LIMIT 1) '" . $cK . "'";
// add params
$params[] = $cK; $params[] = ZBS_TYPE_TRANSACTION;
}
}
$selector = 'transactions.*';
if ( is_array( $fields ) ) { // phpcs:ignore VariableAnalysis.CodeAnalysis.VariableAnalysis.UndefinedVariable
$selector = '';
// always needs id, so add if not present
if ( ! in_array( 'ID', $fields, true ) ) { // phpcs:ignore VariableAnalysis.CodeAnalysis.VariableAnalysis.UndefinedVariable
$selector = 'transactions.ID';
}
foreach ( $fields as $f ) { // phpcs:ignore VariableAnalysis.CodeAnalysis.VariableAnalysis.UndefinedVariable
if ( ! empty( $selector ) ) {
$selector .= ',';
}
$selector .= 'transactions.' . $f;
}
} elseif ( $onlyID ) { // phpcs:ignore VariableAnalysis.CodeAnalysis.VariableAnalysis.UndefinedVariable
$selector = 'transactions.ID';
}
#} ============ / PRE-QUERY ===========
#} Build query
$query = 'SELECT ' . $selector . $extraSelect . ' FROM ' . $ZBSCRM_t['transactions'] . ' AS transactions'; // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase
#} ============= WHERE ================
if (!empty($id) && $id > 0){
#} Add ID
$wheres['ID'] = array('ID','=','%d',$id);
}
if (!empty($externalSource) && !empty($externalSourceUID)){
$wheres['extsourcecheck'] = array('ID','IN','(SELECT DISTINCT zbss_objid FROM '.$ZBSCRM_t['externalsources']." WHERE zbss_objtype = ".ZBS_TYPE_TRANSACTION." 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_transaction($potentialRes,$withCustomFields);
}
if ($withLineItems){
// add all line item lines
$res['lineitems'] = $this->DAL()->lineitems->getLineitems(array('associatedObjType'=>ZBS_TYPE_TRANSACTION,'associatedObjID'=>$potentialRes->ID,'perPage'=>1000,'ignoreowner'=>zeroBSCRM_DAL2_ignoreOwnership(ZBS_TYPE_LINEITEM)));
}
if ($withAssigned){
/* This is for MULTIPLE (e.g. multi contact/companies assigned to an inv)
// add all assigned contacts/companies
$res['contacts'] = $this->DAL()->contacts->getContacts(array(
'hasObjTypeLinkedTo'=>ZBS_TYPE_TRANSACTION,
'hasObjIDLinkedTo'=>$resDataLine->ID,
'perPage'=>-1,
'ignoreowner'=>zeroBSCRM_DAL2_ignoreOwnership(ZBS_TYPE_CONTACT)));
$res['companies'] = $this->DAL()->companies->getCompanies(array(
'hasObjTypeLinkedTo'=>ZBS_TYPE_TRANSACTION,
'hasObjIDLinkedTo'=>$resDataLine->ID,
'perPage'=>-1,
'ignoreowner'=>zeroBSCRM_DAL2_ignoreOwnership(ZBS_TYPE_COMPANY)));
.. but we use 1:1, at least now: */
// add all assigned contacts/companies
$res['contact'] = $this->DAL()->contacts->getContacts(array(
'hasObjTypeLinkedTo'=>ZBS_TYPE_TRANSACTION,
'hasObjIDLinkedTo'=>$potentialRes->ID,
'page' => 0,
'perPage'=>1, // FORCES 1
'ignoreowner'=>zeroBSCRM_DAL2_ignoreOwnership(ZBS_TYPE_CONTACT)));
$res['company'] = $this->DAL()->companies->getCompanies(array(
'hasObjTypeLinkedTo'=>ZBS_TYPE_TRANSACTION,
'hasObjIDLinkedTo'=>$potentialRes->ID,
'page' => 0,
'perPage'=>1, // FORCES 1
'ignoreowner'=>zeroBSCRM_DAL2_ignoreOwnership(ZBS_TYPE_COMPANY)));
// invoice id always singular.
$res['invoice_id'] = $this->DAL()->getFirstIDLinkedToObj(array(
'objtypefrom' => ZBS_TYPE_TRANSACTION,
'objtypeto' => ZBS_TYPE_INVOICE,
'objfromid' => $potentialRes->ID,
));
}
if ($withTags){
// add all tags lines
$res['tags'] = $this->DAL()->getTagsForObjID(array('objtypeid'=>ZBS_TYPE_TRANSACTION,'objid'=>$potentialRes->ID));
}
return $res;
}
} // / if ID
return false;
}
/**
* Returns transaction summed by field between passed timestamps
*/
public function getTransactionTotalByMonth( $args=array() ) {
global $ZBSCRM_t, $wpdb, $zbs;
// ============ LOAD ARGS =============
$defaultArgs = array(
'paidAfter' => strtotime( '12 month ago' ),
'paidBefore' => time(),
);
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 =============
$column_prefix = 'zbst_';
// only include transactions with statuses which should be included in total value:
$transStatusQueryAdd = $this->getTransactionStatusesToIncludeQuery();
$sql = $wpdb->prepare( 'SELECT SUM(' . $column_prefix . 'total) as total, MONTH(FROM_UNIXTIME(' . $column_prefix . 'date)) as month, YEAR(FROM_UNIXTIME(' . $column_prefix . 'date)) as year FROM ' . $ZBSCRM_t['transactions'] . ' WHERE ' . $column_prefix . 'date > %d AND ' . $column_prefix . 'date < %d' . $transStatusQueryAdd . ' GROUP BY month, year ORDER BY year, month', $paidAfter, $paidBefore );
$res = $wpdb->get_results( $sql, ARRAY_A );
return $res;
}
/**
* returns transaction detail lines
*
* @param array $args Associative array of arguments
*
* @return array of transaction lines
*/
public function getTransactions($args=array()){
global $ZBSCRM_t,$wpdb,$zbs;
#} ============ LOAD ARGS =============
$defaultArgs = array(
// Search/Filtering (leave as false to ignore)
'searchPhrase' => '', // searches zbst_title and zbst_desc
'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
'hasStatus' => false, // Lead (this takes over from the quick filter post 19/6/18)
'otherStatus' => false, // status other than 'Lead'
'assignedContact' => false, // assigned to contact id (int)
'assignedCompany' => false, // assigned to company id (int)
'assignedInvoice' => false, // assigned to invoice id (int)
'quickFilters' => false,
'external_source_uid' => false, // e.g. woo-order_10
// date ranges
'olderThan' => false, // uts - checks 'date'
'newerThan' => false, // uts - checks 'date'
'paidBefore' => false, // uts - checks 'date_paid'
'paidAfter' => false, // uts - checks 'date_paid'
'createdBefore' => false, // uts - checks 'created'
'createdAfter' => false, // uts - checks 'created'
// returns
'count' => false,
'total' => false, // returns a summed total value of transactions (scalar)
'withLineItems' => true,
'withCustomFields' => true,
'withTags' => false,
'withOwner' => false,
'withAssigned' => true, // return ['contact'] & ['company'] objs, & invoice_id field, if has link
'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)
// order by
'sortByField' => 'ID',
'sortOrder' => 'ASC',
'page' => 0, // this is what page it is (gets * by for limit)
'perPage' => 100,
'whereCase' => 'AND', // DEFAULT = AND
// permissions
'ignoreowner' => zeroBSCRM_DAL2_ignoreOwnership(ZBS_TYPE_TRANSACTION), // 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 =============
$wheres = array('direct'=>array()); $whereStr = ''; $additionalWhere = ''; $params = array(); $res = array(); $joinQ = ''; $extraSelect = '';
#} ============= PRE-QUERY ============
// Capitalise this
$sortOrder = strtoupper($sortOrder);
// If just count or total, turn off any meta detail
if ( $count || $total ) {
$withCustomFields = false;
$withTags = false;
$withOwner = false;
$withAssigned = false;
$withLineItems = 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;
$withAssigned = false;
$withLineItems = 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_TRANSACTION));
#} 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 = transactions.ID AND zbscf_objkey = %s AND zbscf_objtype = %d LIMIT 1) ' . $cKey;
// add params
$params[] = $cK; $params[] = ZBS_TYPE_TRANSACTION;
}
}
if ( $external_source_uid ) {
$extraSelect .= ', external_source.external_source_uids, external_source.external_source_sources';
$joinQ .= ' LEFT JOIN (
SELECT
extsrcs.zbss_objid external_source_objid,
' . $this->DAL()->build_group_concat( 'extsrcs.zbss_uid', '\n' ) . ' AS external_source_uids,
' . $this->DAL()->build_group_concat( 'extsrcs.zbss_source', '\n' ) . ' AS external_source_sources
FROM
' . $ZBSCRM_t['externalsources'] . ' extsrcs
WHERE
extsrcs.zbss_objtype = %s
GROUP BY extsrcs.zbss_objid
) external_source ON
transactions.ID = external_source.external_source_objid';
$params[] = ZBS_TYPE_TRANSACTION;
}
#} ============ / PRE-QUERY ===========
#} Build query
$query = 'SELECT transactions.*' . $extraSelect . ' FROM ' . $ZBSCRM_t['transactions'] . ' AS transactions' . $joinQ;
#} Count override
if ( $count ) { // phpcs:ignore VariableAnalysis.CodeAnalysis.VariableAnalysis.UndefinedVariable
$query = 'SELECT COUNT(transactions.ID) FROM ' . $ZBSCRM_t['transactions'] . ' AS transactions' . $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['transactions'] . ' AS transactions' . $joinQ;
}
// $total only override
if ( $total ){
$query = 'SELECT SUM(transactions.zbst_total) total FROM ' . $ZBSCRM_t['transactions'] . ' AS transactions' . $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_ref'] = array('zbst_ref','LIKE','%s','%'.$searchPhrase.'%');
$searchWheres['search_title'] = array('zbst_title','LIKE','%s','%'.$searchPhrase.'%');
$searchWheres['search_desc'] = array('zbst_desc','LIKE','%s','%'.$searchPhrase.'%');
$searchWheres['search_total'] = array('zbst_total','LIKE','%s', $searchPhrase.'%');
if ( $external_source_uid ) {
$searchWheres['search_external_source_uid'] = array( 'external_source.external_source_uids', '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_TRANSACTION.")",'%'.$searchPhrase.'%');
}
// This generates a query like 'zbst_fname LIKE %s OR zbst_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 (!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_TRANSACTION." AND zbss_source = %s)",$externalSource);
}
// Timestamp checks:
// phpcs:disable WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase,VariableAnalysis.CodeAnalysis.VariableAnalysis.UndefinedVariable,Generic.ControlStructures.InlineControlStructure.NotAllowed
#} olderThan
if ( ! empty( $olderThan ) && $olderThan > 0 ) $wheres['olderThan'] = array( 'zbst_date', '<=', '%d', $olderThan );
#} newerThan
if ( ! empty( $newerThan ) && $newerThan > 0 ) $wheres['newerThan'] = array( 'zbst_date', '>=', '%d', $newerThan );
#} createdBefore
if ( ! empty( $createdBefore ) && $createdBefore > 0 ) $wheres['createdBefore'] = array( 'zbst_created', '<=', '%d', $createdBefore );
#} createdAfter
if ( ! empty( $createdAfter ) && $createdAfter > 0 ) $wheres['createdAfter'] = array( 'zbst_created', '>=', '%d', $createdAfter );
#} paidBefore
if ( ! empty( $paidBefore ) && $paidBefore > 0 ) $wheres['paidBefore'] = array( 'zbst_date_paid', '<=', '%d', $paidBefore );
#} paidAfter
if ( ! empty( $paidAfter ) && $paidAfter > 0 ) $wheres['paidAfter'] = array( 'zbst_date_paid', '>=', '%d', $paidAfter );
// status
if ( ! empty( $hasStatus ) ) $wheres['hasStatus'] = array( 'zbst_status', '=', '%s', $hasStatus );
if ( ! empty( $otherStatus ) ) $wheres['otherStatus'] = array( 'zbst_status', '<>', '%s', $otherStatus );
// assignedContact + assignedCompany + assignedInvoice
if ( ! empty( $assignedContact ) && $assignedContact > 0 ) $wheres['assignedContact'] = array( 'ID', 'IN', '(SELECT zbsol_objid_from FROM ' . $ZBSCRM_t['objlinks'] . ' WHERE zbsol_objtype_from = ' . ZBS_TYPE_TRANSACTION . ' AND zbsol_objtype_to = ' . ZBS_TYPE_CONTACT . ' AND zbsol_objid_to = %d)', $assignedContact );
if ( ! empty( $assignedCompany ) && $assignedCompany > 0 ) $wheres['assignedCompany'] = array( 'ID', 'IN', '(SELECT 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 = %d)', $assignedCompany );
if ( ! empty( $assignedInvoice ) && $assignedInvoice > 0 ) $wheres['assignedInvoice'] = array( 'ID', 'IN', '(SELECT 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 = %d)', $assignedInvoice );
#} 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( 'zbst_status', '=', 'convert(%s using utf8mb4) collate utf8mb4_bin', $quick_filter_status );
} else {
// if we've hit no filter query, let external logic hook in to provide alternatives
// First used in WooSync module
$wheres = apply_filters( 'jpcrm_transaction_query_quickfilter', $wheres, $qFilter );
}
}
}// / 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 = transactions.ID AND zbstl_tagid = %d) > 0)',
array( ZBS_TYPE_TRANSACTION, $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 = transactions.ID AND zbstl_tagid IN (%s)) > 0)',
array( ZBS_TYPE_TRANSACTION, $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 = transactions.ID AND zbstl_tagid = %d) = 0)',
array( ZBS_TYPE_TRANSACTION, $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 = transactions.ID AND zbstl_tagid IN (%s)) = 0)',
array( ZBS_TYPE_TRANSACTION, $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'];
// Mapped sorts
// This catches listview and other exception sort cases
$sort_map = array(
// Note: "customer" here could be company or contact, so it's not a true sort (as no great way of doing this beyond some sort of prefix comparing)
'customer' => '(SELECT ID FROM ' . $ZBSCRM_t['contacts'] . ' WHERE ID IN (SELECT zbsol_objid_to FROM ' . $ZBSCRM_t['objlinks'] . ' WHERE zbsol_objtype_from = ' . ZBS_TYPE_TRANSACTION . ' AND zbsol_objtype_to = ' . ZBS_TYPE_CONTACT . ' AND zbsol_objid_from = transactions.ID))',
);
if ( array_key_exists( $sortByField, $sort_map ) ) {
$sortByField = $sort_map[ $sortByField ];
}
if ( $external_source_uid && $sortByField === "external_source" ){
$sortByField = ["external_source_uids" => $sortOrder];
}
#} ============ / 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/total + return if requested
if ( $count || $total ) 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) {
// only columns?
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_transaction($resDataLine,$withCustomFields);
}
if ($withLineItems){
// add all line item lines
$resArr['lineitems'] = $this->DAL()->lineitems->getLineitems(array('associatedObjType'=>ZBS_TYPE_TRANSACTION,'associatedObjID'=>$resDataLine->ID,'perPage'=>1000,'ignoreowner'=>zeroBSCRM_DAL2_ignoreOwnership(ZBS_TYPE_LINEITEM)));
}
if ($withTags){
// add all tags lines
$resArr['tags'] = $this->DAL()->getTagsForObjID(array('objtypeid'=>ZBS_TYPE_TRANSACTION,'objid'=>$resDataLine->ID));
}
if ($withAssigned){
/* This is for MULTIPLE (e.g. multi contact/companies assigned to an inv)
// add all assigned contacts/companies
$res['contacts'] = $this->DAL()->contacts->getContacts(array(
'hasObjTypeLinkedTo'=>ZBS_TYPE_TRANSACTION,
'hasObjIDLinkedTo'=>$resDataLine->ID,
'perPage'=>-1,
'ignoreowner'=>zeroBSCRM_DAL2_ignoreOwnership(ZBS_TYPE_CONTACT)));
$res['companies'] = $this->DAL()->companies->getCompanies(array(
'hasObjTypeLinkedTo'=>ZBS_TYPE_TRANSACTION,
'hasObjIDLinkedTo'=>$resDataLine->ID,
'perPage'=>-1,
'ignoreowner'=>zeroBSCRM_DAL2_ignoreOwnership(ZBS_TYPE_COMPANY)));
.. but we use 1:1, at least now: */
// add all assigned contacts/companies
$resArr['contact'] = $this->DAL()->contacts->getContacts(array(
'hasObjTypeLinkedTo'=>ZBS_TYPE_TRANSACTION,
'hasObjIDLinkedTo'=>$resDataLine->ID,
'page' => 0,
'perPage'=>1, // FORCES 1
'ignoreowner'=>zeroBSCRM_DAL2_ignoreOwnership(ZBS_TYPE_CONTACT)));
$resArr['company'] = $this->DAL()->companies->getCompanies(array(
'hasObjTypeLinkedTo'=>ZBS_TYPE_TRANSACTION,
'hasObjIDLinkedTo'=>$resDataLine->ID,
'page' => 0,
'perPage'=>1, // FORCES 1
'ignoreowner'=>zeroBSCRM_DAL2_ignoreOwnership(ZBS_TYPE_COMPANY)));
}
if ( $external_source_uid ) {
$resArr['external_source_uid'] = $this->tidy_external_sources( $resDataLine );
}
$res[] = $resArr;
}
}
return $res;
}
/**
* Returns a count of transactions (owned)
* .. inc by status
*
* @return int count
*/
public function getTransactionCount($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('zbst_status','=','%s',$withStatus);
return $this->DAL()->getFieldByWHERE(array(
'objtype' => ZBS_TYPE_TRANSACTION,
'colname' => 'COUNT(ID)',
'where' => $whereArr,
'ignoreowner' => $ignoreowner));
}
/**
* adds or updates a transaction object
*
* @param array $args Associative array of arguments
* id (if update), owner, data (array of field data)
*
* @return int line ID
*/
public function addUpdateTransaction($args=array()){
global $ZBSCRM_t,$wpdb,$zbs;
#} Retrieve any cf
$customFields = $this->DAL()->getActiveCustomFields(array('objtypeid'=>ZBS_TYPE_TRANSACTION));
// not req. $addrCustomFields = $this->DAL()->getActiveCustomFields(array('objtypeid'=>ZBS_TYPE_ADDRESS));
#} ============ LOAD ARGS =============
$defaultArgs = array(
'id' => -1,
'owner' => -1,
// fields (directly)
'data' => array(
'status' => '',
'type' => '',
'ref' => '',
'origin' => '',
'parent' => '',
'hash' => '',
'title' => '',
'desc' => '',
'date' => '',
'customer_ip' => '',
'currency' => '',
'net' => '',
'fee' => '',
'discount' => '',
'shipping' => '',
'shipping_taxes' => '',
'shipping_tax' => '',
'taxes' => '',
'tax' => '',
'total' => '',
'date_paid' => null,
'date_completed' => null,
// lineitems:
'lineitems' => false,
// will be an array of lineitem lines (as per matching lineitem database model)
// note: if no change desired, pass "false"
// if removal of all/change, pass empty array
// Note Custom fields may be passed here, but will not have defaults so check isset()
// obj links:
'contacts' => false, // array of id's
'companies' => false, // array of id's
'invoice_id' => false, // ID if assigned to an invoice
// 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' => '',
),
'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,
'silentInsert' => false, // this was for init Migration - it KILLS all IA for newTransaction (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)
'do_not_mark_invoices' => false // by default all trans associated with an INV will fire a check "should this inv be marked paid" on add/update of trans. If this is true, check will not run
); 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 zbst_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'], 'zbst_' ) ) {
$dePrefixed = substr( $field['key'], strlen( 'zbst_' ) ); // 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
}
#} If no status, and default is specified in settings, add that in :)
// for now, I copied this from addUpdateTransaction: 'Unknown';
if (is_null($data['status']) || !isset($data['status']) || empty($data['status'])){
// Default status for obj? -> this one gets for contacts ->
$data['status'] = __('Unknown','zero-bs-crm'); //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_transaction($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'=>'zbst_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' => 'zbst_'.$k, // we have to add zbst_ here because translating from data -> limited fields
'val' => $v,
'type' => $this->getTypeStr('zbst_'.$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['zbst_lastupdated'])){ $dataArr['zbst_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);
}
// companies - avoid dupes
if (isset($data['companies']) && is_array($data['companies'])){
$coArr = array();
foreach ($data['companies'] as $c){
$cI = (int)$c;
if ($cI > 0 && !in_array($cI, $coArr)) $coArr[] = $cI;
}
// reset the main
if (count($coArr) > 0)
$data['companies'] = $coArr;
else
$data['companies'] = '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,
'zbst_status' => $data['status'],
'zbst_type' => $data['type'],
'zbst_ref' => $data['ref'],
'zbst_origin' => $data['origin'],
'zbst_parent' => $data['parent'],
'zbst_hash' => $data['hash'],
'zbst_title' => $data['title'],
'zbst_desc' => $data['desc'],
'zbst_date' => $data['date'],
'zbst_customer_ip' => $data['customer_ip'],
'zbst_currency' => $data['currency'],
'zbst_net' => $data['net'],
'zbst_fee' => $data['fee'],
'zbst_discount' => $data['discount'],
'zbst_shipping' => $data['shipping'],
'zbst_shipping_taxes' => $data['shipping_taxes'],
'zbst_shipping_tax' => $data['shipping_tax'],
'zbst_taxes' => $data['taxes'],
'zbst_tax' => $data['tax'],
'zbst_total' => $data['total'],
'zbst_date_paid' => $data['date_paid'],
'zbst_date_completed' => $data['date_completed'],
'zbst_lastupdated' => time(),
);
$typeArr = array( // field data types
//'%d', // site
//'%d', // team
//'%d', // owner
'%s',
'%s',
'%s',
'%s',
'%d',
'%s',
'%s',
'%s',
'%d',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%d',
'%d',
'%d',
);
if (!empty($id) && $id > 0){
// is update
$update = true;
} 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['zbst_created'] = $data['created'];$typeArr[] = '%d';
} else {
$dataArr['zbst_created'] = time(); $typeArr[] = '%d';
}
// if no transaction date is passed on creation, use time()
// allow for 0 value (valid epoch time)
if ( empty($dataArr['zbst_date']) && $dataArr['zbst_date'] !== 0 ) {
$dataArr['zbst_date'] = time();
}
}
}
#} ========= / 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->getTransactionStatus($id);
// log any change of status
if (isset($dataArr['zbst_status']) && !empty($dataArr['zbst_status']) && !empty($originalStatus) && $dataArr['zbst_status'] != $originalStatus){
// status change
$statusChange = array(
'from' => $originalStatus,
'to' => $dataArr['zbst_status']
);
}
#} Attempt update
if ($wpdb->update(
$ZBSCRM_t['transactions'],
$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){
// Line Items ====
// line item work
if (isset($data['lineitems']) && is_array($data['lineitems'])){
// if array passed, update, even if removing
if (count($data['lineitems']) > 0){
// passed, for now this is BRUTAL and just clears old ones + readds
// once live, discuss how to refactor to be less brutal.
// delete all lineitems
$this->DAL()->lineitems->deleteLineItemsForObject(array('objID'=>$id,'objType'=>ZBS_TYPE_TRANSACTION));
// addupdate each
foreach ($data['lineitems'] as $lineitem) {
// slight rejig of passed so works cleanly with data array style
$lineItemID = false; if (isset($lineitem['ID'])) $lineItemID = $lineitem['ID'];
$this->DAL()->lineitems->addUpdateLineitem(array(
'id'=>$lineItemID,
'linkedObjType' => ZBS_TYPE_TRANSACTION,
'linkedObjID' => $id,
'data'=>$lineitem
));
}
} else {
// delete all lineitems
$this->DAL()->lineitems->deleteLineItemsForObject(array('objID'=>$id,'objType'=>ZBS_TYPE_TRANSACTION));
}
}
// / Line Items ====
// OBJ LINKS - to contacts/companies
$this->addUpdateObjectLinks($id,$data['contacts'],ZBS_TYPE_CONTACT);
$this->addUpdateObjectLinks($id,$data['companies'],ZBS_TYPE_COMPANY);
// IA also gets 'againstid' historically, but we'll pass as 'against id's'
$againstIDs = array('contacts'=>$data['contacts'],'companies'=>$data['companies']);
// OBJ Links - to invoices
$this->addUpdateObjectLinks($id,array($data['invoice_id']),ZBS_TYPE_INVOICE);
// if not-empty inv id, check if needs to be mark paid!
if (!$do_not_mark_invoices && !empty($data['invoice_id']) && $data['invoice_id'] > 0){
//function to check ammount due and mark invoice as paid if amount due <= 0.
zeroBSCRM_check_amount_due_mark_paid($data['invoice_id']);
}
// tags
if (isset($data['tags']) && is_array($data['tags'])) {
$this->addUpdateTransactionTags(
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_TRANSACTION,
'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_TRANSACTION,
'objid' => $id,
'objkey' => $cK,
'objval' => $data[$cK]
)));
}
}
// / Custom Fields
} else {
// limited fields
// here we set what will not have been passed as blanks, for the IA to use below.
$againstIDs = false;
$approvedExternalSource = '';
}
// Any extra meta keyval pairs
// BRUTALLY updates (no checking)
$confirmedExtraMeta = false;
if ( is_array( $extraMeta ) ) { // phpcs:ignore VariableAnalysis.CodeAnalysis.VariableAnalysis.UndefinedVariable,WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase
$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_TRANSACTION,$id,'extra_'.$cleanKey,$v);
#} Add it to this, which passes to IA
$confirmedExtraMeta[$cleanKey] = $v;
}
}
#} INTERNAL AUTOMATOR
#} &
#} FALLBACKS
// UPDATING CONTACT
if (!$silentInsert){
// catch dirty flag (update of status) (note, after update_post_meta - as separate)
//if (isset($_POST['zbst_status_dirtyflag']) && $_POST['zbst_status_dirtyflag'] == "1"){
// actually here, it's set above
if (isset($statusChange) && is_array($statusChange)){
// status has changed
// IA
zeroBSCRM_FireInternalAutomator('transaction.status.update',array(
'id' => $id,
'againstids' => $againstIDs,
'data' => $data,
'from' => $statusChange['from'],
'to' => $statusChange['to']
));
}
// IA General transaction update (2.87+)
zeroBSCRM_FireInternalAutomator('transaction.update',array(
'id' => $id,
'data' => $data,
'againstids' => $againstIDs,
'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)
));
$data['id'] = $id; // phpcs:ignore VariableAnalysis.CodeAnalysis.VariableAnalysis.UndefinedVariable
$this->events_manager->transaction()->updated( $data ); // phpcs:ignore VariableAnalysis.CodeAnalysis.VariableAnalysis.UndefinedVariable
}
// 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['transactions'],
$dataArr,
$typeArr ) > 0){
#} Successfully inserted, lets return new ID
$newID = $wpdb->insert_id;
// Line Items ====
// line item work
if (isset($data['lineitems']) && is_array($data['lineitems'])){
// if array passed, update, even if removing
if (count($data['lineitems']) > 0){
// passed, for now this is BRUTAL and just clears old ones + readds
// once live, discuss how to refactor to be less brutal.
// delete all lineitems
$this->DAL()->lineitems->deleteLineItemsForObject(array('objID'=>$newID,'objType'=>ZBS_TYPE_TRANSACTION));
// addupdate each
foreach ($data['lineitems'] as $lineitem) {
// slight rejig of passed so works cleanly with data array style
$lineItemID = false; if (isset($lineitem['ID'])) $lineItemID = $lineitem['ID'];
$this->DAL()->lineitems->addUpdateLineitem(array(
'id'=>$lineItemID,
'linkedObjType' => ZBS_TYPE_TRANSACTION,
'linkedObjID' => $newID,
'data'=>$lineitem
));
}
} else {
// delete all lineitems
$this->DAL()->lineitems->deleteLineItemsForObject(array('objID'=>$newID,'objType'=>ZBS_TYPE_TRANSACTION));
}
}
// / Line Items ====
// OBJ LINKS - to contacts/companies
$this->addUpdateObjectLinks($newID,$data['contacts'],ZBS_TYPE_CONTACT);
$this->addUpdateObjectLinks($newID,$data['companies'],ZBS_TYPE_COMPANY);
// IA also gets 'againstid' historically, but we'll pass as 'against id's'
$againstIDs = array('contacts'=>$data['contacts'],'companies'=>$data['companies']);
// OBJ Links - to invoices
$this->addUpdateObjectLinks($newID,array($data['invoice_id']),ZBS_TYPE_INVOICE);
// if not-empty inv id, check if needs to be mark paid!
if (!$do_not_mark_invoices && !empty($data['invoice_id']) && $data['invoice_id'] > 0){
//function to check ammount due and mark invoice as paid if amount due <= 0.
zeroBSCRM_check_amount_due_mark_paid($data['invoice_id']);
}
// tags
if (isset($data['tags']) && is_array($data['tags'])) {
$this->addUpdateTransactionTags(
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_TRANSACTION,
'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_TRANSACTION,
'objid' => $newID,
'objkey' => $cK,
'objval' => $data[$cK]
)));
}
}
// / Custom Fields
#} Any extra meta keyval pairs?
// BRUTALLY updates (no checking)
$confirmedExtraMeta = false;
if ( is_array( $extraMeta ) ) { // phpcs:ignore VariableAnalysis.CodeAnalysis.VariableAnalysis.UndefinedVariable,WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase
$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_TRANSACTION,$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('transaction.new',array(
'id'=>$newID,
'data'=>$data,
'againstids'=>$againstIDs,
'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)
));
$data['id'] = $newID; // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase, VariableAnalysis.CodeAnalysis.VariableAnalysis.UndefinedVariable
$this->events_manager->transaction()->created( $data ); // phpcs:ignore VariableAnalysis.CodeAnalysis.VariableAnalysis.UndefinedVariable
}
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 transaction'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 addUpdateTransactionTags($args=array()){
global $ZBSCRM_t,$wpdb;
#} ============ LOAD ARGS =============
$defaultArgs = array(
'id' => -1,
// 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_TRANSACTION,
'objid' => $id,
'tag_input' => $tag_input,
'tags' => $tags,
'tagIDs' => $tagIDs,
'mode' => $mode
)
);
}
/**
* updates status for a transaction (no blanks allowed)
*
* @param int id transaction ID
* @param string transaction Status
*
* @return bool
*/
public function setTransactionStatus($id=-1,$status=-1){
global $zbs;
$id = (int)$id;
if ($id > 0 && !empty($status) && $status !== -1){
return $this->addUpdateTransaction(array(
'id'=>$id,
'limitedFields'=>array(
array('key'=>'zbst_status','val' => $status,'type' => '%s')
)));
}
return false;
}
/**
* deletes a transaction object
*
* @param array $args Associative array of arguments
* id
*
* @return int success;
*/
public function deleteTransaction($args=array()){
global $ZBSCRM_t,$wpdb,$zbs;
#} ============ LOAD ARGS =============
$defaultArgs = array(
'id' => -1,
'saveOrphans' => 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 ============
#} 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_TRANSACTION,
'objid' => $id,
));
// delete any external source information
$this->DAL()->delete_external_sources( array(
'obj_type' => ZBS_TYPE_TRANSACTION,
'obj_id' => $id,
'obj_source' => 'all',
));
}
$del = zeroBSCRM_db2_deleteGeneric($id,'transactions');
#} Add to automator
zeroBSCRM_FireInternalAutomator('transaction.delete',array(
'id'=>$id,
'saveOrphans'=>$saveOrphans
));
$this->events_manager->transaction()->deleted( $id );
return $del;
}
return false;
}
/**
* tidy's the object from wp db into clean array
*
* @param array $obj (DB obj)
*
* @return array transaction (clean obj)
*/
private function tidy_transaction($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->zbst_status);
$res['type'] = $this->stripSlashes($obj->zbst_type);
// status categorisation (basically did it succeed?)
// this is dictated by Transaction Status settings: /wp-admin/admin.php?page=zerobscrm-plugin-settings&tab=transactions
$res['status_bool'] = ( 'all' === $this->getTransactionStatusesToInclude() || in_array( $res['status'], $this->getTransactionStatusesToInclude() ) ? 1 : -1 );
// type further categorised here, because JS etc. needs it in non-lingual
$res['type_accounting'] = $this->transactionAccountingType($res['type']);
$res['ref'] = $this->stripSlashes($obj->zbst_ref);
$res['origin'] = $this->stripSlashes($obj->zbst_origin);
$res['parent'] = (int)$obj->zbst_parent;
$res['hash'] = $this->stripSlashes($obj->zbst_hash);
$res['title'] = is_null($obj->zbst_title)?'':$this->stripSlashes($obj->zbst_title);
$res['desc'] = $this->stripSlashes($obj->zbst_desc);
$res['date'] = (int)$obj->zbst_date;
// well this naming convention makes this confusing... lol: //
$res['date_date'] = (isset($obj->zbst_date) && $obj->zbst_date > 0) ? zeroBSCRM_locale_utsToDatetime($obj->zbst_date) : false;
$res['customer_ip'] = $this->stripSlashes($obj->zbst_customer_ip);
$res['currency'] = $this->stripSlashes($obj->zbst_currency);
$res['net'] = $this->stripSlashes($obj->zbst_net);
$res['fee'] = $this->stripSlashes($obj->zbst_fee);
$res['discount'] = $this->stripSlashes($obj->zbst_discount);
$res['shipping'] = $this->stripSlashes($obj->zbst_shipping);
$res['shipping_taxes'] = $this->stripSlashes($obj->zbst_shipping_taxes);
$res['shipping_tax'] = $this->stripSlashes($obj->zbst_shipping_tax);
$res['taxes'] = $this->stripSlashes($obj->zbst_taxes);
$res['tax'] = $this->stripSlashes($obj->zbst_tax);
$res['total'] = $this->stripSlashes($obj->zbst_total);
$res['date_paid'] = is_null( $obj->zbst_date_paid ) ? null : (int)$obj->zbst_date_paid;
$res['date_paid_date'] = (isset($obj->zbst_date_paid) && $obj->zbst_date_paid > 0) ? zeroBSCRM_locale_utsToDatetime($obj->zbst_date_paid) : false;
$res['date_completed'] = is_null( $obj->zbst_date_completed ) ? null : (int)$obj->zbst_date_completed;
$res['date_completed_date'] = (isset($obj->zbst_date_completed) && $obj->zbst_date_completed > 0) ? zeroBSCRM_locale_utsToDatetime($obj->zbst_date_completed) : false;
$res['created'] = (int)$obj->zbst_created;
$res['created_date'] = (isset($obj->zbst_created) && $obj->zbst_created > 0) ? zeroBSCRM_date_i18n(-1,$obj->zbst_created,false,true) : false;
$res['lastupdated'] = (int)$obj->zbst_lastupdated;
$res['lastupdated_date'] = (isset($obj->zbst_lastupdated) && $obj->zbst_lastupdated > 0) ? zeroBSCRM_locale_utsToDatetime($obj->zbst_lastupdated) : false;
// custom fields - tidy any that are present:
if ($withCustomFields) $res = $this->tidyAddCustomFields(ZBS_TYPE_TRANSACTION,$obj,$res,false);
}
return $res;
}
/**
* Tidies a row from a database result containing the columns external_source_uids and external_source_sources.
* The result is a formatted HTML string.
*
* @param object $row (DB row containing the columns external_source_uids and external_source_sources)
*
* @return string Formatted HTML string for the external sources.
*/
private function tidy_external_sources( $row ) {
if ($row->external_source_uids == null && $row->external_source_sources == null) {
return "";
}
$external_source_uids = explode("\n", $row->external_source_uids);
$external_source_sources = explode("\n", $row->external_source_sources);
$external_source_strings = array_map(
function($source, $uid) {
$source_title = zeroBS_getExternalSourceTitle( $source, $uid );
// Formats the default zeroBS_getExternalSourceTitle string to look better in the table.
$source_title_explode = explode( "<br />", $source_title, 2 );
if ( count($source_title_explode) === 2 ) {
// Removes the trailing ':' from the source. E.g. 'WooCommerce:' becomes 'WooCommerce'.
$source_title_source = rtrim($source_title_explode[0], ":");
return "{$source_title_explode[1]} ({$source_title_source})";
} else {
return $source_title;
}
},
$external_source_sources,
$external_source_uids
);
return implode( '<br />', $external_source_strings );
}
/**
* Takes a transaction status (e.g. Sale or Credit Note), and returns debit/credit :)
* wrapper that should be used throughout for inferring accounting direction for a transaction
*
* @param int objtype
* @param int objid
* @param string key
*
* @return array transaction meta result
*/
public function transactionAccountingType( $transaction_type='Sale' ){
if ( !empty( $transaction_type ) ){
if ( in_array( $transaction_type, array( __( 'Refund', 'zero-bs-crm' ), __( 'Credit Note', 'zero-bs-crm' ) ) ) ){
return 'credit';
}
}
return 'debit';
}
/**
* Wrapper, use $this->getTransactionMeta($contactID,$key) for easy retrieval of singular transaction
* Simplifies $this->getMeta
*
* @param int objtype
* @param int objid
* @param string key
*
* @return array transaction meta result
*/
public function getTransactionMeta($id=-1,$key='',$default=false){
global $zbs;
if (!empty($key)){
return $this->DAL()->getMeta(array(
'objtype' => ZBS_TYPE_TRANSACTION,
'objid' => $id,
'key' => $key,
'fullDetails' => false,
'default' => $default,
'ignoreowner' => true // for now !!
));
}
return $default;
}
/**
* Returns a Transaction's tag array
*
* @param int id Transaction ID
*
* @return mixed
*/
public function getTransactionTags($id=-1){
global $zbs;
$id = (int)$id;
if ($id > 0){
return $this->DAL()->getTagsForObjID(array('objtypeid'=>ZBS_TYPE_TRANSACTION,'objid'=>$id));
}
return false;
}
/**
* Returns a reference against a transaction
*
* @param int id transaction ID
*
* @return string transaction ref
*/
public function get_transaction_ref( $transaction_id = -1 ){
global $zbs;
return $this->DAL()->getFieldByID( array(
'id' => $transaction_id,
'objtype' => ZBS_TYPE_TRANSACTION,
'colname' => 'zbst_ref',
'ignoreowner' => true
));
}
/**
* Returns an ownerid against a transaction
*
* @param int id transaction ID
*
* @return int transaction owner id
*/
public function getTransactionOwner($id=-1){
global $zbs;
$id = (int)$id;
if ($id > 0){
return $this->DAL()->getFieldByID(array(
'id' => $id,
'objtype' => ZBS_TYPE_TRANSACTION,
'colname' => 'zbs_owner',
'ignoreowner'=>true));
}
return false;
}
/**
* Returns an array of contacts associtaed with a transaction
*
* @param int id transaction ID
*
* @return array contacts assocatied with transaction
*/
public function get_transaction_contacts( $transaction_id ){
return $this->DAL()->contacts->getContacts(array(
'hasObjTypeLinkedTo'=>ZBS_TYPE_TRANSACTION,
'hasObjIDLinkedTo'=>$transaction_id
));
}
/**
* Returns an invoice associtaed with a transaction
*
* @param int id transaction ID
*
* @return array $invoice
*/
public function get_transaction_invoice_id( $transaction_id ){
return $this->DAL()->getFirstIDLinkedToObj(array(
'objtypefrom' => ZBS_TYPE_TRANSACTION,
'objtypeto' => ZBS_TYPE_INVOICE,
'objfromid' => $transaction_id,
));
}
/**
* Returns an status against a transaction
*
* @param int id transaction ID
*
* @return string transaction status string
*/
public function getTransactionStatus($id=-1){
global $zbs;
$id = (int)$id;
if ($id > 0){
return $this->DAL()->getFieldByID(array(
'id' => $id,
'objtype' => ZBS_TYPE_TRANSACTION,
'colname' => 'zbst_status',
'ignoreowner'=>true));
}
return false;
}
/**
* returns the transaction statuses to include in "total value" as per the setting on
* admin.php?page=zerobscrm-plugin-settings&tab=transactions
*
* @return array
*/
function getTransactionStatusesToInclude(){
// load (accept from cache)
$setting = $this->DAL()->setting( 'transinclude_status', 'all', true );
if (is_string($setting) && strpos($setting, ',') > 0){
return explode(',', $setting);
} elseif (is_array($setting)){
return $setting;
}
return 'all';
}
/**
* returns an SQL query addition which will allow filtering of transactions
* that should be included in "total value" fields
* admin.php?page=zerobscrm-plugin-settings&tab=transactions
*
* @param string $table_alias_sql - if using a table alias pass that here, e.g. `transactions`.
* @return array
*/
function getTransactionStatusesToIncludeQuery( $table_alias_sql = '' ){
// first we get the setting
$transaction_statuses = $this->getTransactionStatusesToInclude();
// next we build the SQL
// note that (in a legacy way) getTransactionStatusesToInclude() returns a string 'all'
// .. if all transactions are selected
// .. in that case there's no SQL to return as all statuses count.
$query_addition = '';
if ( is_array( $transaction_statuses ) && count( $transaction_statuses ) > 0 ){
// create escaped csv
$transaction_statuses_str = $this->build_csv( $transaction_statuses );
// build return sql
$query_addition = ' AND ' . $table_alias_sql . 'zbst_status IN ('.$transaction_statuses_str.')';
}
return $query_addition;
}
/**
* 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_transaction($obj=false){
// use the generic? (override here if necessary)
return $this->db_ready_obj($obj);
}
/**
* 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($transaction=false,$columnsRequired=array()){
if (is_array($transaction) && isset($transaction['id'])){
$resArr = $transaction;
// a lot of this is legacy <DAL3 stuff just mapped. def could do with an improvement for efficacy's sake.
$resArr['total'] = zeroBSCRM_formatCurrency($resArr['total']);
//$resArr['orderid'] = strlen($transaction['orderid']) > 7 ? substr($transaction['orderid'],0,7)."..." : $transaction['orderid'];
// order id now = ref (use proper field)
//$resArr['id'] = $transaction['id'];
$resArr['status'] = ucfirst($transaction['status']);
// This wasn't working: $d = new DateTime($transaction['meta']['date']);
// ... so I added the correct field (post_date) to getTransactions and piped in here
//$d = new DateTime($transaction['date']);
//$formatted_date = $d->format(zeroBSCRM_getDateFormat());
// USE proper field $resArr['added'] = $formatted_date;
#} Convert $contact arr into list-view-digestable 'customer'// & unset contact for leaner data transfer
$resArr['customer'] = zeroBSCRM_getSimplyFormattedContact($transaction['contact'],(in_array('assignedobj', $columnsRequired)));
#} Convert $contact arr into list-view-digestable 'customer'// & unset contact for leaner data transfer
$resArr['company'] = zeroBSCRM_getSimplyFormattedCompany($transaction['company'],(in_array('assignedobj', $columnsRequired)));
#} Tags
//if (in_array('tagged', $columnsRequired)){
// $resArr['tags'] = $transaction['tags'];
//}
return $resArr;
}
return false;
}
// =========== / TRANSACTION =======================================================
// ===============================================================================
} // / class