Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
0.00%
0 / 1105
0.00%
0 / 29
 CRAP
0.00%
0 / 1
zbsDAL_transactions
0.00%
0 / 1105
0.00%
0 / 29
 135792
0.00%
0 / 1
 __construct
0.00%
0 / 16
0.00%
0 / 1
 56
 add_listview_filters
0.00%
0 / 5
0.00%
0 / 1
 12
 getSingle
0.00%
0 / 1
0.00%
0 / 1
 2
 getIDList
0.00%
0 / 9
0.00%
0 / 1
 2
 getAll
0.00%
0 / 10
0.00%
0 / 1
 2
 getFullCount
0.00%
0 / 7
0.00%
0 / 1
 2
 transaction_exists
0.00%
0 / 6
0.00%
0 / 1
 12
 getTransaction
0.00%
0 / 118
0.00%
0 / 1
 1190
 getTransactionTotalByMonth
0.00%
0 / 20
0.00%
0 / 1
 56
 getTransactions
0.00%
0 / 252
0.00%
0 / 1
 12656
 getTransactionCount
0.00%
0 / 24
0.00%
0 / 1
 90
 addUpdateTransaction
0.00%
0 / 408
0.00%
0 / 1
 14280
 addUpdateTransactionTags
0.00%
0 / 29
0.00%
0 / 1
 90
 setTransactionStatus
0.00%
0 / 15
0.00%
0 / 1
 20
 deleteTransaction
0.00%
0 / 41
0.00%
0 / 1
 110
 tidy_transaction
0.00%
0 / 38
0.00%
0 / 1
 342
 tidy_external_sources
0.00%
0 / 17
0.00%
0 / 1
 20
 transactionAccountingType
0.00%
0 / 4
0.00%
0 / 1
 12
 getTransactionMeta
0.00%
0 / 12
0.00%
0 / 1
 6
 getTransactionTags
0.00%
0 / 9
0.00%
0 / 1
 6
 get_transaction_ref
0.00%
0 / 8
0.00%
0 / 1
 2
 getTransactionOwner
0.00%
0 / 11
0.00%
0 / 1
 6
 get_transaction_contacts
0.00%
0 / 6
0.00%
0 / 1
 2
 get_transaction_invoice_id
0.00%
0 / 7
0.00%
0 / 1
 2
 getTransactionStatus
0.00%
0 / 11
0.00%
0 / 1
 6
 getTransactionStatusesToInclude
0.00%
0 / 6
0.00%
0 / 1
 20
 getTransactionStatusesToIncludeQuery
0.00%
0 / 6
0.00%
0 / 1
 12
 db_ready_transaction
0.00%
0 / 1
0.00%
0 / 1
 2
 listViewObj
0.00%
0 / 8
0.00%
0 / 1
 12
1<?php
2/*
3 * Jetpack CRM
4 * https://jetpackcrm.com
5 * V3.0+
6 *
7 * Copyright 2020 Automattic
8 *
9 * Date: 14/01/19
10 */
11
12// phpcs:disable Generic.WhiteSpace.DisallowSpaceIndent.SpacesUsed, WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase
13
14defined( 'ZEROBSCRM_PATH' ) || exit( 0 );
15
16use Automattic\Jetpack\CRM\Event_Manager\Events_Manager;
17
18/**
19 * ZBS DAL >> Transactions
20 *
21 * @author   Woody Hayday <hello@jetpackcrm.com>
22 * @version  2.0
23 * @access   public
24 * @see      https://jetpackcrm.com/kb
25 */
26class zbsDAL_transactions extends zbsDAL_ObjectLayer {
27
28    protected $objectType            = ZBS_TYPE_TRANSACTION;
29    protected $objectDBPrefix        = 'zbst_';
30    protected $include_in_templating = true;
31    protected $objectModel           = array(
32
33        // ID
34        'ID'             => array(
35            'fieldname' => 'ID',
36            'format'    => 'int',
37        ),
38
39        // site + team generics
40        'zbs_site'       => array(
41            'fieldname' => 'zbs_site',
42            'format'    => 'int',
43        ),
44        'zbs_team'       => array(
45            'fieldname' => 'zbs_team',
46            'format'    => 'int',
47        ),
48        'zbs_owner'      => array(
49            'fieldname' => 'zbs_owner',
50            'format'    => 'int',
51        ),
52
53        // other fields
54        'status'         => array(
55            // db model:
56            'fieldname'   => 'zbst_status',
57            'format'      => 'str',
58            // output model
59            'input_type'  => 'select',
60            'label'       => 'Status',
61            'placeholder' => '',
62            'options'     => array( 'Succeeded', 'Completed', 'Failed', 'Refunded', 'Processing', 'Pending', 'Hold', 'Cancelled' ),
63            'essential'   => true,
64            'max_len'     => 50,
65        ),
66        'type'           => array(
67            // db model:
68            'fieldname'   => 'zbst_type',
69            'format'      => 'str',
70            // output model
71            'input_type'  => 'select',
72            'label'       => 'Type',
73            'placeholder' => '',
74            'options'     => array( 'Sale', 'Refund', 'Credit Note' ),
75            'essential'   => true,
76            'default'     => 'Sale',
77            'max_len'     => 50,
78        ),
79        'ref'            => array(
80            // db model:
81            'fieldname'    => 'zbst_ref',
82            'format'       => 'str',
83            // output model
84            'input_type'   => 'text',
85            'label'        => 'Transaction ID',
86            'placeholder'  => '',
87            'essential'    => true,
88            'dal1key'      => 'orderid',
89            'force_unique' => true, // must be unique. This is required and breaking if true,
90            'not_empty'    => true,
91            'max_len'      => 120,
92        ),
93        'origin'         => array(
94            'fieldname' => 'zbst_origin',
95            'format'    => 'str',
96            'max_len'   => 100,
97        ),
98        'parent'         => array(
99            'fieldname' => 'zbst_parent',
100            'format'    => 'int',
101        ),
102        'hash'           => array(
103            'fieldname' => 'zbst_hash',
104            'format'    => 'str',
105        ),
106        'title'          => array(
107            // db model:
108            'fieldname'   => 'zbst_title',
109            'format'      => 'str',
110            // output model
111            'input_type'  => 'text',
112            'label'       => 'Transaction Title',
113            'placeholder' => 'e.g. Product ABC',
114            'dal1key'     => 'item',
115            'max_len'     => 200,
116        ),
117        'desc'           => array(
118            // db model:
119            'fieldname'   => 'zbst_desc',
120            'format'      => 'str',
121            // output model
122            'input_type'  => 'textarea',
123            'label'       => 'Description',
124            'placeholder' => '',
125            'max_len'     => 200,
126        ),
127        'date'           => array(
128            // db model:
129            'fieldname'   => 'zbst_date',
130            'format'      => 'uts',
131            'autoconvert' => 'datetime', // NOTE autoconvert makes buildObjArr autoconvert from a 'date' using localisation rules, to a GMT timestamp (UTS)
132                // output model
133            'input_type'  => 'datetime',
134            'label'       => 'Transaction Date',
135            'placeholder' => '',
136
137        ), // <-- this is a bit of a misnomer, it's basically timestamp for created
138        'customer_ip'    => array(
139            'fieldname' => 'zbst_customer_ip',
140            'format'    => 'str',
141            'max_len'   => 45,
142        ),
143        'currency'       => array(
144            'fieldname' => 'zbst_currency',
145            'format'    => 'curr',
146            'max_len'   => 4,
147        ),
148        'net'            => array(
149            // db model:
150            'fieldname'   => 'zbst_net',
151            'format'      => 'decimal',
152            // output model
153            'input_type'  => 'price',
154            'label'       => 'Net',
155            'placeholder' => '',
156            'default'     => '0.00',
157        ),
158        'fee'            => array(
159            // db model:
160            'fieldname'   => 'zbst_fee',
161            'format'      => 'decimal',
162            // output model
163            'input_type'  => 'price',
164            'label'       => 'Fee',
165            'placeholder' => '',
166            'default'     => '0.00',
167        ),
168        'discount'       => array(
169            // db model:
170            'fieldname'   => 'zbst_discount',
171            'format'      => 'decimal',
172            // output model
173            'input_type'  => 'price',
174            'label'       => 'Discount',
175            'placeholder' => '',
176            'default'     => '0.00',
177        ),
178        'shipping'       => array(
179            // db model:
180            'fieldname'   => 'zbst_shipping',
181            'format'      => 'decimal',
182            // output model
183            'input_type'  => 'price',
184            'label'       => 'Shipping',
185            'placeholder' => '',
186            'default'     => '0.00',
187        ),
188        'shipping_taxes' => array(
189            // db model:
190            'fieldname'   => 'zbst_shipping_taxes',
191            'format'      => 'str',
192            // output model
193            'input_type'  => 'tax',
194            'label'       => 'Shipping Taxes',
195            'placeholder' => '',
196        ),
197        'shipping_tax'   => array(
198            'fieldname' => 'zbst_shipping_tax',
199            'format'    => 'decimal',
200            'label'     => 'Shipping Tax',
201        ),
202        'taxes'          => array(
203            // db model:
204            'fieldname'   => 'zbst_taxes',
205            'format'      => 'str',
206            // output model
207            'input_type'  => 'tax',
208            'label'       => 'Taxes',
209            'placeholder' => '',
210
211            // replaces tax_rate, but tax_rate was a decimal, this is a string of applicable tax codes from tax table.
212        ),
213        'tax'            => array(
214            'fieldname'   => 'zbst_tax',
215            'format'      => 'decimal',
216            'label'       => 'Tax',
217            'input_type'  => 'price',
218            'placeholder' => '',
219        ),
220        'total'          => array(
221            // db model:
222            'fieldname'   => 'zbst_total',
223            'format'      => 'decimal',
224            // output model
225            'input_type'  => 'price',
226            'label'       => 'Total',
227            'placeholder' => '',
228            'default'     => '0.00',
229            'essential'   => true,
230        ),
231        'date_paid'      => array(
232            // db model:
233            'fieldname'   => 'zbst_date_paid',
234            'format'      => 'uts',
235            'autoconvert' => 'date', // NOTE autoconvert makes buildObjArr autoconvert from a 'date' using localisation rules, to a GMT timestamp (UTS)
236                // output model
237            'input_type'  => 'date',
238            'label'       => 'Date Paid',
239        ),
240        'date_completed' => array(
241            // db model:
242            'fieldname'   => 'zbst_date_completed',
243            'format'      => 'uts',
244            'autoconvert' => 'date', // NOTE autoconvert makes buildObjArr autoconvert from a 'date' using localisation rules, to a GMT timestamp (UTS)
245                // output model
246            'input_type'  => 'date',
247            'label'       => 'Date Completed',
248        ),
249        'created'        => array(
250            'fieldname' => 'zbst_created',
251            'format'    => 'uts',
252        ),
253        'lastupdated'    => array(
254            'fieldname' => 'zbst_lastupdated',
255            'format'    => 'uts',
256        ),
257
258    );
259
260        // hardtyped list of types this object type is commonly linked to
261        protected $linkedToObjectTypes = array(
262
263            ZBS_TYPE_CONTACT,
264            ZBS_TYPE_COMPANY,
265
266        );
267
268        /**
269         * Events_Manager instance. Manages CRM events.
270         *
271         * @since 6.2.0
272         *
273         * @var Events_Manager
274         */
275        private $events_manager;
276
277        function __construct( $args = array() ) {
278
279            #} =========== LOAD ARGS ==============
280            $defaultArgs = array(
281
282            // 'tag' => false,
283
284            );
285            foreach ( $defaultArgs as $argK => $argV ) {
286                $this->$argK = $argV;
287                if ( is_array( $args ) && isset( $args[ $argK ] ) ) {
288                    if ( is_array( $args[ $argK ] ) ) {
289                        $newData = $this->$argK;
290                        if ( ! is_array( $newData ) ) {
291                            $newData = array();
292                        } foreach ( $args[ $argK ] as $subK => $subV ) {
293                            $newData[ $subK ] = $subV;
294                        }$this->$argK = $newData;
295                    } else {
296                        $this->$argK = $args[ $argK ]; }
297                }
298            }
299            #} =========== / LOAD ARGS =============
300
301            $this->events_manager = new Events_Manager();
302
303            add_filter( 'jpcrm_listview_filters', array( $this, 'add_listview_filters' ) );
304        }
305
306        /**
307         * Adds items to listview filter using `jpcrm_listview_filters` hook.
308         *
309         * @param array $listview_filters Listview filters.
310         */
311        public function add_listview_filters( $listview_filters ) {
312            global $zbs;
313            // Add statuses if enabled.
314            if ( $zbs->settings->get( 'filtersfromstatus' ) === 1 ) {
315                $statuses = zeroBSCRM_getTransactionsStatuses( true );
316                foreach ( $statuses as $status ) {
317                    $listview_filters[ ZBS_TYPE_TRANSACTION ]['status'][ 'status_' . $status ] = $status;
318                }
319            }
320            return $listview_filters;
321        }
322
323        // ===============================================================================
324        // ===========   TRANSACTION  =======================================================
325
326        // generic get Company (by ID)
327        // Super simplistic wrapper used by edit page etc. (generically called via dal->contacts->getSingle etc.)
328        public function getSingle( $ID = -1 ) {
329
330            return $this->getTransaction( $ID );
331        }
332
333        // generic get (by ID list)
334        // Super simplistic wrapper used by MVP Export v3.0
335        public function getIDList( $IDs = false ) {
336
337            return $this->getTransactions(
338                array(
339                    'inArr'        => $IDs,
340                    'withOwner'    => true,
341                    'withAssigned' => true,
342                    'page'         => -1,
343                    'perPage'      => -1,
344                )
345            );
346        }
347
348        // generic get (EVERYTHING)
349        // expect heavy load!
350        public function getAll( $IDs = false ) {
351
352            return $this->getTransactions(
353                array(
354                    'withOwner'    => true,
355                    'withAssigned' => true,
356                    'sortByField'  => 'ID',
357                    'sortOrder'    => 'ASC',
358                    'page'         => -1,
359                    'perPage'      => -1,
360                )
361            );
362        }
363
364        // generic get count of (EVERYTHING)
365        public function getFullCount() {
366
367            return $this->getTransactions(
368                array(
369                    'count'   => true,
370                    'page'    => -1,
371                    'perPage' => -1,
372                )
373            );
374        }
375
376        /**
377         * Verifies there is a transaction with this ID accessible to current logged in user
378         *
379         * @param int transaction_id
380         *
381         * @return bool
382         */
383        public function transaction_exists( $transaction_id ) {
384
385            // has to be a legit int
386            if ( empty( $transaction_id ) ) {
387                return false;
388            }
389
390            // note this ignores ownership for now
391            $potential_transaction = $this->getTransaction( $transaction_id, array( 'onlyID' => true ) );
392
393            if ( $potential_transaction > 0 ) {
394
395                return true;
396
397            }
398
399            return false;
400        }
401
402        /**
403         * returns full transaction line +- details
404         *
405         * @param int id        transaction id
406         * @param array                        $args   Associative array of arguments
407         *
408         * @return array transaction object
409         */
410        public function getTransaction( $id = -1, $args = array() ) {
411
412            global $zbs;
413
414            #} =========== LOAD ARGS ==============
415            $defaultArgs = array(
416
417                // if these two passed, will search based on these
418                'externalSource'    => false,
419                'externalSourceUID' => false,
420
421                // with what?
422                'withLineItems'     => true,
423                'withCustomFields'  => true,
424                'withAssigned'      => true, // return ['contact'] & ['company'] arrays, & invoice_id field, if has link
425                'withTags'          => false,
426                'withOwner'         => false,
427
428                // permissions
429                'ignoreowner'       => zeroBSCRM_DAL2_ignoreOwnership( ZBS_TYPE_TRANSACTION ), // this'll let you not-check the owner of obj
430
431            // returns scalar ID of line
432                'onlyID'            => false,
433
434                'fields'            => false, // false = *, array = fieldnames
435
436            );
437            foreach ( $defaultArgs as $argK => $argV ) {
438                $$argK = $argV;
439                if ( is_array( $args ) && isset( $args[ $argK ] ) ) {
440                    if ( is_array( $args[ $argK ] ) ) {
441                        $newData = $$argK;
442                        if ( ! is_array( $newData ) ) {
443                            $newData = array();
444                        } foreach ( $args[ $argK ] as $subK => $subV ) {
445                            $newData[ $subK ] = $subV;
446                        }$$argK = $newData;
447                    } else {
448                        $$argK = $args[ $argK ]; }
449                }
450            }
451            #} =========== / LOAD ARGS =============
452
453            #} Check ID
454            $id = (int) $id;
455            if (
456            ( ! empty( $id ) && $id > 0 )
457            ||
458            ( ! empty( $email ) )
459            ||
460            ( ! empty( $externalSource ) && ! empty( $externalSourceUID ) )
461            ) {
462
463                global $ZBSCRM_t, $wpdb;
464                $wheres          = array( 'direct' => array() );
465                $whereStr        = '';
466                $additionalWhere = '';
467                $params          = array();
468                $res             = array();
469                $extraSelect     = '';
470
471                #} ============= PRE-QUERY ============
472
473                #} Custom Fields
474                if ( $withCustomFields && ! $onlyID ) {
475
476                    #} Retrieve any cf
477                    $custFields = $this->DAL()->getActiveCustomFields( array( 'objtypeid' => ZBS_TYPE_TRANSACTION ) );
478
479                    #} Cycle through + build into query
480                    if ( is_array( $custFields ) ) {
481                        foreach ( $custFields as $cK => $cF ) {
482
483                            // add as subquery
484                                                $extraSelect .= ',(SELECT zbscf_objval FROM ' . $ZBSCRM_t['customfields'] . " WHERE zbscf_objid = transactions.ID AND zbscf_objkey = %s AND zbscf_objtype = %d LIMIT 1) '" . $cK . "'";
485
486                            // add params
487                            $params[] = $cK;
488                            $params[] = ZBS_TYPE_TRANSACTION;
489
490                        }
491                    }
492                }
493
494                                $selector = 'transactions.*';
495                if ( is_array( $fields ) ) { // phpcs:ignore VariableAnalysis.CodeAnalysis.VariableAnalysis.UndefinedVariable
496                    $selector = '';
497
498                    // always needs id, so add if not present
499                    if ( ! in_array( 'ID', $fields, true ) ) { // phpcs:ignore VariableAnalysis.CodeAnalysis.VariableAnalysis.UndefinedVariable
500                        $selector = 'transactions.ID';
501                    }
502
503                    foreach ( $fields as $f ) { // phpcs:ignore VariableAnalysis.CodeAnalysis.VariableAnalysis.UndefinedVariable
504                        if ( ! empty( $selector ) ) {
505                            $selector .= ',';
506                        }
507                        $selector .= 'transactions.' . $f;
508                    }
509                } elseif ( $onlyID ) { // phpcs:ignore VariableAnalysis.CodeAnalysis.VariableAnalysis.UndefinedVariable
510                    $selector = 'transactions.ID';
511                }
512
513                #} ============ / PRE-QUERY ===========
514
515                #} Build query
516                        $query = 'SELECT ' . $selector . $extraSelect . ' FROM ' . $ZBSCRM_t['transactions'] . ' AS transactions'; // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase
517                #} ============= WHERE ================
518
519                if ( ! empty( $id ) && $id > 0 ) {
520
521                    #} Add ID
522                    $wheres['ID'] = array( 'ID', '=', '%d', $id );
523
524                }
525
526                if ( ! empty( $externalSource ) && ! empty( $externalSourceUID ) ) {
527
528                    $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 ) );
529
530                }
531
532                #} ============ / WHERE ==============
533
534                #} Build out any WHERE clauses
535                $wheresArr = $this->buildWheres( $wheres, $whereStr, $params );
536                $whereStr  = $wheresArr['where'];
537                $params    = $params + $wheresArr['params'];
538                #} / Build WHERE
539
540                #} Ownership v1.0 - the following adds SITE + TEAM checks, and (optionally), owner
541                $params = array_merge( $params, $this->ownershipQueryVars( $ignoreowner ) ); // merges in any req.
542                $ownQ   = $this->ownershipSQL( $ignoreowner );
543                if ( ! empty( $ownQ ) ) {
544                    $additionalWhere = $this->spaceAnd( $additionalWhere ) . $ownQ; // adds str to query
545                }
546                #} / Ownership
547
548                #} Append to sql (this also automatically deals with sortby and paging)
549                $query .= $this->buildWhereStr( $whereStr, $additionalWhere ) . $this->buildSort( 'ID', 'DESC' ) . $this->buildPaging( 0, 1 );
550
551                try {
552
553                    #} Prep & run query
554                    $queryObj     = $this->prepare( $query, $params );
555                    $potentialRes = $wpdb->get_row( $queryObj, OBJECT );
556
557                } catch ( Exception $e ) {
558
559                    #} General SQL Err
560                    $this->catchSQLError( $e );
561
562                }
563
564                #} Interpret Results (ROW)
565                if ( isset( $potentialRes ) && isset( $potentialRes->ID ) ) {
566
567                    #} Has results, tidy + return
568
569                        #} Only ID? return it directly
570                    if ( $onlyID ) {
571                        return $potentialRes->ID;
572                    }
573
574                    // tidy
575                    if ( is_array( $fields ) ) {
576                        // guesses fields based on table col names
577                        $res = $this->lazyTidyGeneric( $potentialRes );
578                    } else {
579                        // proper tidy
580                        $res = $this->tidy_transaction( $potentialRes, $withCustomFields );
581                    }
582
583                    if ( $withLineItems ) {
584
585                        // add all line item lines
586                        $res['lineitems'] = $this->DAL()->lineitems->getLineitems(
587                            array(
588                                'associatedObjType' => ZBS_TYPE_TRANSACTION,
589                                'associatedObjID'   => $potentialRes->ID,
590                                'perPage'           => 1000,
591                                'ignoreowner'       => zeroBSCRM_DAL2_ignoreOwnership( ZBS_TYPE_LINEITEM ),
592                            )
593                        );
594
595                    }
596
597                    if ( $withAssigned ) {
598
599                        /*
600                            This is for MULTIPLE (e.g. multi contact/companies assigned to an inv)
601
602                            // add all assigned contacts/companies
603                            $res['contacts'] = $this->DAL()->contacts->getContacts(array(
604                        'hasObjTypeLinkedTo'=>ZBS_TYPE_TRANSACTION,
605                        'hasObjIDLinkedTo'=>$resDataLine->ID,
606                        'perPage'=>-1,
607                        'ignoreowner'=>zeroBSCRM_DAL2_ignoreOwnership(ZBS_TYPE_CONTACT)));
608
609                            $res['companies'] = $this->DAL()->companies->getCompanies(array(
610                        'hasObjTypeLinkedTo'=>ZBS_TYPE_TRANSACTION,
611                        'hasObjIDLinkedTo'=>$resDataLine->ID,
612                        'perPage'=>-1,
613                        'ignoreowner'=>zeroBSCRM_DAL2_ignoreOwnership(ZBS_TYPE_COMPANY)));
614
615                        .. but we use 1:1, at least now: */
616
617                            // add all assigned contacts/companies
618                            $res['contact'] = $this->DAL()->contacts->getContacts(
619                                array(
620                                    'hasObjTypeLinkedTo' => ZBS_TYPE_TRANSACTION,
621                                    'hasObjIDLinkedTo'   => $potentialRes->ID,
622                                    'page'               => 0,
623                                    'perPage'            => 1, // FORCES 1
624                                    'ignoreowner'        => zeroBSCRM_DAL2_ignoreOwnership( ZBS_TYPE_CONTACT ),
625                                )
626                            );
627
628                            $res['company'] = $this->DAL()->companies->getCompanies(
629                                array(
630                                    'hasObjTypeLinkedTo' => ZBS_TYPE_TRANSACTION,
631                                    'hasObjIDLinkedTo'   => $potentialRes->ID,
632                                    'page'               => 0,
633                                    'perPage'            => 1, // FORCES 1
634                                    'ignoreowner'        => zeroBSCRM_DAL2_ignoreOwnership( ZBS_TYPE_COMPANY ),
635                                )
636                            );
637
638                            // invoice id always singular.
639                            $res['invoice_id'] = $this->DAL()->getFirstIDLinkedToObj(
640                                array(
641
642                                    'objtypefrom' => ZBS_TYPE_TRANSACTION,
643                                    'objtypeto'   => ZBS_TYPE_INVOICE,
644                                    'objfromid'   => $potentialRes->ID,
645
646                                )
647                            );
648
649                    }
650
651                    if ( $withTags ) {
652
653                        // add all tags lines
654                        $res['tags'] = $this->DAL()->getTagsForObjID(
655                            array(
656                                'objtypeid' => ZBS_TYPE_TRANSACTION,
657                                'objid'     => $potentialRes->ID,
658                            )
659                        );
660
661                    }
662
663                    return $res;
664
665                }
666            } // / if ID
667
668            return false;
669        }
670
671        /**
672         *  Returns transaction summed by field between passed timestamps
673         */
674        public function getTransactionTotalByMonth( $args = array() ) {
675
676            global $ZBSCRM_t, $wpdb, $zbs;
677
678            // ============ LOAD ARGS =============
679            $defaultArgs = array(
680
681                'paidAfter'  => strtotime( '12 month ago' ),
682                'paidBefore' => time(),
683
684            );
685            foreach ( $defaultArgs as $argK => $argV ) {
686                $$argK = $argV;
687                if ( is_array( $args ) && isset( $args[ $argK ] ) ) {
688                    if ( is_array( $args[ $argK ] ) ) {
689                        $newData = $$argK;
690                        if ( ! is_array( $newData ) ) {
691                            $newData = array();
692                        } foreach ( $args[ $argK ] as $subK => $subV ) {
693                            $newData[ $subK ] = $subV;
694                        }$$argK = $newData;
695                    } else {
696                        $$argK = $args[ $argK ]; }
697                }
698            }
699            // =========== / LOAD ARGS =============
700
701            $column_prefix = 'zbst_';
702
703            // only include transactions with statuses which should be included in total value:
704            $transStatusQueryAdd = $this->getTransactionStatusesToIncludeQuery();
705
706            $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 );
707            $res = $wpdb->get_results( $sql, ARRAY_A );
708
709            return $res;
710        }
711
712        /**
713         * returns transaction detail lines
714         *
715         * @param array $args Associative array of arguments
716         *
717         * @return array of transaction lines
718         */
719        public function getTransactions( $args = array() ) {
720
721            global $ZBSCRM_t, $wpdb, $zbs;
722
723            #} ============ LOAD ARGS =============
724            $defaultArgs = array(
725
726                // Search/Filtering (leave as false to ignore)
727                'searchPhrase'        => '', // searches zbst_title and zbst_desc
728                'inArr'               => false,
729                'isTagged'            => false, // 1x INT OR array(1,2,3)
730                'isNotTagged'         => false, // 1x INT OR array(1,2,3)
731                'ownedBy'             => false,
732                'externalSource'      => false, // e.g. paypal
733                'hasStatus'           => false, // Lead (this takes over from the quick filter post 19/6/18)
734                'otherStatus'         => false, // status other than 'Lead'
735                'assignedContact'     => false, // assigned to contact id (int)
736                'assignedCompany'     => false, // assigned to company id (int)
737                'assignedInvoice'     => false, // assigned to invoice id (int)
738                'quickFilters'        => false,
739                'external_source_uid' => false, // e.g. woo-order_10
740
741            // date ranges
742                'olderThan'           => false, // uts - checks 'date'
743                'newerThan'           => false, // uts - checks 'date'
744                'paidBefore'          => false, // uts - checks 'date_paid'
745                'paidAfter'           => false, // uts - checks 'date_paid'
746                'createdBefore'       => false, // uts - checks 'created'
747                'createdAfter'        => false, // uts - checks 'created'
748
749            // returns
750                'count'               => false,
751                'total'               => false, // returns a summed total value of transactions (scalar)
752                'withLineItems'       => true,
753                'withCustomFields'    => true,
754                'withTags'            => false,
755                'withOwner'           => false,
756                'withAssigned'        => true, // return ['contact'] & ['company'] objs, & invoice_id field, if has link
757                '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)
758
759            // order by
760                'sortByField'         => 'ID',
761                'sortOrder'           => 'ASC',
762                'page'                => 0, // this is what page it is (gets * by for limit)
763                'perPage'             => 100,
764                'whereCase'           => 'AND', // DEFAULT = AND
765
766            // permissions
767                'ignoreowner'         => zeroBSCRM_DAL2_ignoreOwnership( ZBS_TYPE_TRANSACTION ), // this'll let you not-check the owner of obj
768            );
769            foreach ( $defaultArgs as $argK => $argV ) {
770                $$argK = $argV;
771                if ( is_array( $args ) && isset( $args[ $argK ] ) ) {
772                    if ( is_array( $args[ $argK ] ) ) {
773                        $newData = $$argK;
774                        if ( ! is_array( $newData ) ) {
775                            $newData = array();
776                        } foreach ( $args[ $argK ] as $subK => $subV ) {
777                            $newData[ $subK ] = $subV;
778                        }$$argK = $newData;
779                    } else {
780                        $$argK = $args[ $argK ]; }
781                }
782            }
783            #} =========== / LOAD ARGS =============
784
785            $wheres          = array( 'direct' => array() );
786            $whereStr        = '';
787            $additionalWhere = '';
788            $params          = array();
789            $res             = array();
790            $joinQ           = '';
791            $extraSelect     = '';
792
793            #} ============= PRE-QUERY ============
794
795            // Capitalise this
796            $sortOrder = strtoupper( $sortOrder );
797
798            // If just count or total, turn off any meta detail
799            if ( $count || $total ) {
800
801                $withCustomFields = false;
802                $withTags         = false;
803                $withOwner        = false;
804                $withAssigned     = false;
805                $withLineItems    = false;
806
807            }
808
809            #} If onlyColumns, validate
810            if ( $onlyColumns ) {
811
812                #} onlyColumns build out a field arr
813                if ( is_array( $onlyColumns ) && count( $onlyColumns ) > 0 ) {
814
815                    $onlyColumnsFieldArr = array();
816                    foreach ( $onlyColumns as $col ) {
817
818                        // find db col key from field key (e.g. fname => zbsc_fname)
819                        $dbCol = '';
820                        if ( isset( $this->objectModel[ $col ] ) && isset( $this->objectModel[ $col ]['fieldname'] ) ) {
821                            $dbCol = $this->objectModel[ $col ]['fieldname'];
822                        }
823
824                        if ( ! empty( $dbCol ) ) {
825
826                            $onlyColumnsFieldArr[ $dbCol ] = $col;
827
828                        }
829                    }
830                }
831
832                // if legit cols:
833                if ( isset( $onlyColumnsFieldArr ) && is_array( $onlyColumnsFieldArr ) && count( $onlyColumnsFieldArr ) > 0 ) {
834
835                    $onlyColumns = true;
836
837                    // If onlyColumns, turn off extras
838                    $withCustomFields = false;
839                    $withTags         = false;
840                    $withOwner        = false;
841                    $withAssigned     = false;
842                    $withLineItems    = false;
843
844                } else {
845
846                    // deny
847                    $onlyColumns = false;
848
849                }
850            }
851
852            #} Custom Fields
853                        // @phan-suppress-next-line PhanImpossibleCondition -- Phan is confused; this var is initialized at the beginning of the function.
854            if ( $withCustomFields ) {
855
856                #} Retrieve any cf
857                $custFields = $this->DAL()->getActiveCustomFields( array( 'objtypeid' => ZBS_TYPE_TRANSACTION ) );
858
859                #} Cycle through + build into query
860                if ( is_array( $custFields ) ) {
861                    foreach ( $custFields as $cK => $cF ) {
862
863                        // custom field (e.g. 'third name') it'll be passed here as 'third-name'
864                        // ... problem is mysql does not like that :) so we have to chage here:
865                        // in this case we prepend cf's with cf_ and we switch - for _
866                        $cKey = 'cf_' . str_replace( '-', '_', $cK );
867
868                        // we also check the $sortByField in case that's the same cf
869                        if ( $cK == $sortByField ) {
870
871                            // sort by
872                            $sortByField = $cKey;
873
874                            // check if sort needs any CAST (e.g. numeric):
875                            $sortByField = $this->DAL()->build_custom_field_order_by_str( $sortByField, $cF );
876
877                        }
878
879                        // add as subquery
880                        $extraSelect .= ',(SELECT zbscf_objval FROM ' . $ZBSCRM_t['customfields'] . ' WHERE zbscf_objid = transactions.ID AND zbscf_objkey = %s AND zbscf_objtype = %d LIMIT 1) ' . $cKey;
881
882                        // add params
883                        $params[] = $cK;
884                        $params[] = ZBS_TYPE_TRANSACTION;
885
886                    }
887                }
888            }
889
890            if ( $external_source_uid ) {
891                $extraSelect .= ', external_source.external_source_uids, external_source.external_source_sources';
892                $joinQ       .= '  LEFT JOIN (
893                                SELECT 
894                                    extsrcs.zbss_objid external_source_objid,
895                                    ' . $this->DAL()->build_group_concat( 'extsrcs.zbss_uid', '\n' ) . ' AS external_source_uids,
896                                    ' . $this->DAL()->build_group_concat( 'extsrcs.zbss_source', '\n' ) . ' AS external_source_sources
897                                FROM 
898                                    ' . $ZBSCRM_t['externalsources'] . ' extsrcs
899                                WHERE 
900                                  extsrcs.zbss_objtype = %s
901                                GROUP BY extsrcs.zbss_objid
902                            ) external_source ON
903                                transactions.ID = external_source.external_source_objid';
904                $params[]     = ZBS_TYPE_TRANSACTION;
905            }
906
907            #} ============ / PRE-QUERY ===========
908
909            #} Build query
910            $query = 'SELECT transactions.*' . $extraSelect . ' FROM ' . $ZBSCRM_t['transactions'] . ' AS transactions' . $joinQ;
911
912            #} Count override
913            if ( $count ) { // phpcs:ignore VariableAnalysis.CodeAnalysis.VariableAnalysis.UndefinedVariable
914                $query = 'SELECT COUNT(transactions.ID) FROM ' . $ZBSCRM_t['transactions'] . ' AS transactions' . $joinQ;
915            }
916
917            #} onlyColumns override
918            if ( $onlyColumns && is_array( $onlyColumnsFieldArr ) && count( $onlyColumnsFieldArr ) > 0 ) {
919
920                $columnStr = '';
921                foreach ( $onlyColumnsFieldArr as $colDBKey => $colStr ) {
922
923                    if ( ! empty( $columnStr ) ) {
924                        $columnStr .= ',';
925                    }
926                    // this presumes str is db-safe? could do with sanitation?
927                    $columnStr .= $colDBKey;
928
929                }
930
931                $query = 'SELECT ' . $columnStr . ' FROM ' . $ZBSCRM_t['transactions'] . ' AS transactions' . $joinQ;
932
933            }
934
935            // $total only override
936            if ( $total ) {
937
938                $query = 'SELECT SUM(transactions.zbst_total) total FROM ' . $ZBSCRM_t['transactions'] . ' AS transactions' . $joinQ;
939
940            }
941
942            #} ============= WHERE ================
943
944            #} Add Search phrase
945            if ( ! empty( $searchPhrase ) ) {
946
947                // search? - ALL THESE COLS should probs have index of FULLTEXT in db?
948                $searchWheres                 = array();
949                $searchWheres['search_ID']    = array( 'ID', '=', '%d', $searchPhrase );
950                $searchWheres['search_ref']   = array( 'zbst_ref', 'LIKE', '%s', '%' . $searchPhrase . '%' );
951                $searchWheres['search_title'] = array( 'zbst_title', 'LIKE', '%s', '%' . $searchPhrase . '%' );
952                $searchWheres['search_desc']  = array( 'zbst_desc', 'LIKE', '%s', '%' . $searchPhrase . '%' );
953                $searchWheres['search_total'] = array( 'zbst_total', 'LIKE', '%s', $searchPhrase . '%' );
954
955                if ( $external_source_uid ) {
956                    $searchWheres['search_external_source_uid'] = array( 'external_source.external_source_uids', 'LIKE', '%s', '%' . $searchPhrase . '%' );
957                }
958
959                // 3.0.13 - Added ability to search custom fields (optionally)
960                $customFieldSearch = zeroBSCRM_getSetting( 'customfieldsearch' );
961                if ( $customFieldSearch == 1 ) {
962
963                    // simplistic add
964                    // NOTE: This IGNORES ownership of custom field lines.
965                    $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 . '%' );
966
967                }
968
969                // This generates a query like 'zbst_fname LIKE %s OR zbst_lname LIKE %s',
970                // which we then need to include as direct subquery (below) in main query :)
971                $searchQueryArr = $this->buildWheres( $searchWheres, '', array(), 'OR', false );
972
973                if ( is_array( $searchQueryArr ) && isset( $searchQueryArr['where'] ) && ! empty( $searchQueryArr['where'] ) ) {
974
975                    // add it
976                    $wheres['direct'][] = array( '(' . $searchQueryArr['where'] . ')', $searchQueryArr['params'] );
977
978                }
979            }
980
981            #} In array (if inCompany passed, this'll currently overwrite that?! (todo2.5))
982            if ( is_array( $inArr ) && count( $inArr ) > 0 ) {
983
984                // clean for ints
985                $inArrChecked = array();
986                foreach ( $inArr as $x ) {
987                    $inArrChecked[] = (int) $x; }
988
989                // add where
990                $wheres['inarray'] = array( 'ID', 'IN', '(' . implode( ',', $inArrChecked ) . ')' );
991
992            }
993
994            #} Owned by
995            if ( ! empty( $ownedBy ) && $ownedBy > 0 ) {
996
997                // would never hard-type this in (would make generic as in buildWPMetaQueryWhere)
998                // but this is only here until MIGRATED to db2 globally
999                // $wheres['incompany'] = array('ID','IN','(SELECT DISTINCT post_id FROM '.$wpdb->prefix."postmeta WHERE meta_key = 'zbs_company' AND meta_value = %d)",$inCompany);
1000                // Use obj links now
1001                $wheres['ownedBy'] = array( 'zbs_owner', '=', '%s', $ownedBy );
1002
1003            }
1004
1005            // External sources
1006            if ( ! empty( $externalSource ) ) {
1007
1008                // NO owernship built into this, check when roll out multi-layered ownsership
1009                $wheres['externalsource'] = array( 'ID', 'IN', '(SELECT DISTINCT zbss_objid FROM ' . $ZBSCRM_t['externalsources'] . ' WHERE zbss_objtype = ' . ZBS_TYPE_TRANSACTION . ' AND zbss_source = %s)', $externalSource );
1010
1011            }
1012
1013            // Timestamp checks:
1014            // phpcs:disable WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase,VariableAnalysis.CodeAnalysis.VariableAnalysis.UndefinedVariable,Generic.ControlStructures.InlineControlStructure.NotAllowed
1015            #} olderThan
1016            if ( ! empty( $olderThan ) && $olderThan > 0 ) $wheres['olderThan'] = array( 'zbst_date', '<=', '%d', $olderThan );
1017            #} newerThan
1018            if ( ! empty( $newerThan ) && $newerThan > 0 ) $wheres['newerThan'] = array( 'zbst_date', '>=', '%d', $newerThan );
1019
1020            #} createdBefore
1021            if ( ! empty( $createdBefore ) && $createdBefore > 0 ) $wheres['createdBefore'] = array( 'zbst_created', '<=', '%d', $createdBefore );
1022            #} createdAfter
1023            if ( ! empty( $createdAfter ) && $createdAfter > 0 ) $wheres['createdAfter'] = array( 'zbst_created', '>=', '%d', $createdAfter );
1024
1025            #} paidBefore
1026            if ( ! empty( $paidBefore ) && $paidBefore > 0 ) $wheres['paidBefore'] = array( 'zbst_date_paid', '<=', '%d', $paidBefore );
1027            #} paidAfter
1028            if ( ! empty( $paidAfter ) && $paidAfter > 0 ) $wheres['paidAfter'] = array( 'zbst_date_paid', '>=', '%d', $paidAfter );
1029
1030            // status
1031            if ( ! empty( $hasStatus ) ) $wheres['hasStatus']     = array( 'zbst_status', '=', '%s', $hasStatus );
1032            if ( ! empty( $otherStatus ) ) $wheres['otherStatus'] = array( 'zbst_status', '<>', '%s', $otherStatus );
1033
1034            // assignedContact + assignedCompany + assignedInvoice
1035            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 );
1036            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 );
1037            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 );
1038
1039            #} Quick filters - adapted from DAL1 (probs can be slicker)
1040            if ( is_array( $quickFilters ) && count( $quickFilters ) > 0 ) {
1041
1042                // cycle through
1043                foreach ( $quickFilters as $qFilter ) {
1044
1045                        // where status = x
1046                        // USE hasStatus above now...
1047                    if ( str_starts_with( $qFilter, 'status_' ) ) { // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase
1048
1049                        $quick_filter_status         = substr( $qFilter, 7 ); // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase
1050                        $wheres['quickfilterstatus'] = array( 'zbst_status', '=', 'convert(%s using utf8mb4) collate utf8mb4_bin', $quick_filter_status );
1051
1052                    } else {
1053
1054                            // if we've hit no filter query, let external logic hook in to provide alternatives
1055                            // First used in WooSync module
1056                            $wheres = apply_filters( 'jpcrm_transaction_query_quickfilter', $wheres, $qFilter );
1057
1058                    }
1059                }
1060            }// / quickfilters
1061
1062            #} Is Tagged (expects 1 tag ID OR array)
1063
1064                // catch 1 item arr
1065                if (is_array( $isTagged ) && count( $isTagged ) == 1) $isTagged = $isTagged[0];
1066
1067            if ( ! empty( $isTagged ) && ! is_array( $isTagged ) && $isTagged > 0 ) { // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase
1068
1069                // add where tagged
1070                // 1 int:
1071                $wheres['direct'][] = array(
1072                    '((SELECT COUNT(ID) FROM ' . $ZBSCRM_t['taglinks'] . ' WHERE zbstl_objtype = %d AND zbstl_objid = transactions.ID AND zbstl_tagid = %d) > 0)',
1073                    array( ZBS_TYPE_TRANSACTION, $isTagged ),
1074                );
1075
1076            } elseif ( is_array( $isTagged ) && count( $isTagged ) > 0 ) {
1077
1078                // foreach in array :)
1079                $tagStr = '';
1080                foreach ( $isTagged as $iTag ) {
1081                    $i = (int) $iTag;
1082                    if ( $i > 0 ) {
1083
1084                        if ($tagStr !== '') $tagStr . ',';
1085                        $tagStr .= $i;
1086                    }
1087                }
1088                if ( ! empty( $tagStr ) ) {
1089
1090                    $wheres['direct'][] = array(
1091                        '((SELECT COUNT(ID) FROM ' . $ZBSCRM_t['taglinks'] . ' WHERE zbstl_objtype = %d AND zbstl_objid = transactions.ID AND zbstl_tagid IN (%s)) > 0)',
1092                        array( ZBS_TYPE_TRANSACTION, $tagStr ),
1093                    );
1094
1095                }
1096            }
1097            #} Is NOT Tagged (expects 1 tag ID OR array)
1098
1099                // catch 1 item arr
1100                if (is_array( $isNotTagged ) && count( $isNotTagged ) == 1) $isNotTagged = $isNotTagged[0];
1101
1102            if ( ! empty( $isNotTagged ) && ! is_array( $isNotTagged ) && $isNotTagged > 0 ) { // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase
1103
1104                // add where tagged
1105                // 1 int:
1106                $wheres['direct'][] = array(
1107                    '((SELECT COUNT(ID) FROM ' . $ZBSCRM_t['taglinks'] . ' WHERE zbstl_objtype = %d AND zbstl_objid = transactions.ID AND zbstl_tagid = %d) = 0)',
1108                    array( ZBS_TYPE_TRANSACTION, $isNotTagged ),
1109                );
1110
1111            } elseif ( is_array( $isNotTagged ) && count( $isNotTagged ) > 0 ) {
1112
1113                // foreach in array :)
1114                $tagStr = '';
1115                foreach ( $isNotTagged as $iTag ) {
1116                    $i = (int) $iTag;
1117                    if ( $i > 0 ) {
1118
1119                        if ($tagStr !== '') $tagStr . ',';
1120                        $tagStr .= $i;
1121                    }
1122                }
1123                if ( ! empty( $tagStr ) ) {
1124
1125                    $wheres['direct'][] = array(
1126                        '((SELECT COUNT(ID) FROM ' . $ZBSCRM_t['taglinks'] . ' WHERE zbstl_objtype = %d AND zbstl_objid = transactions.ID AND zbstl_tagid IN (%s)) = 0)',
1127                        array( ZBS_TYPE_TRANSACTION, $tagStr ),
1128                    );
1129
1130                }
1131            }
1132
1133            #} ============ / WHERE ===============
1134
1135            #} ============   SORT   ==============
1136
1137            // Obj Model based sort conversion
1138            // converts 'addr1' => 'zbsco_addr1' generically
1139            if (isset( $this->objectModel[ $sortByField ] ) && isset( $this->objectModel[ $sortByField ]['fieldname'] )) $sortByField = $this->objectModel[ $sortByField ]['fieldname'];
1140
1141            // Mapped sorts
1142            // This catches listview and other exception sort cases
1143                    $sort_map = array(
1144
1145                        // 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)
1146                        '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))',
1147
1148                    );
1149
1150                    if ( array_key_exists( $sortByField, $sort_map ) ) {
1151
1152                        $sortByField = $sort_map[ $sortByField ];
1153
1154                    }
1155
1156                    if ( $external_source_uid && $sortByField === 'external_source' ) {
1157                        $sortByField = array( 'external_source_uids' => $sortOrder );
1158                    }
1159
1160                    #} ============ / SORT   ==============
1161
1162                    #} CHECK this + reset to default if faulty
1163                    if ( ! in_array( $whereCase, array( 'AND', 'OR' ) )) $whereCase = 'AND';
1164
1165                    #} Build out any WHERE clauses
1166                    $wheresArr = $this->buildWheres( $wheres, $whereStr, $params, $whereCase );
1167                    $whereStr  = $wheresArr['where'];
1168                    $params    = $params + $wheresArr['params'];
1169                    #} / Build WHERE
1170
1171                    #} Ownership v1.0 - the following adds SITE + TEAM checks, and (optionally), owner
1172                    $params                                 = array_merge( $params, $this->ownershipQueryVars( $ignoreowner ) ); // merges in any req.
1173                    $ownQ                                   = $this->ownershipSQL( $ignoreowner, 'contact' );
1174                    if ( ! empty( $ownQ )) $additionalWhere = $this->spaceAnd( $additionalWhere ) . $ownQ; // adds str to query
1175                    #} / Ownership
1176
1177                    #} Append to sql (this also automatically deals with sortby and paging)
1178                    $query .= $this->buildWhereStr( $whereStr, $additionalWhere ) . $this->buildSort( $sortByField, $sortOrder ) . $this->buildPaging( $page, $perPage );
1179
1180                    try {
1181
1182                        #} Prep & run query
1183                        $queryObj = $this->prepare( $query, $params );
1184
1185                        // Catch count/total + return if requested
1186                        if ( $count || $total ) return $wpdb->get_var( $queryObj );
1187
1188                        #} else continue..
1189                        $potentialRes = $wpdb->get_results( $queryObj, OBJECT );
1190
1191                    } catch ( Exception $e ) {
1192
1193                        #} General SQL Err
1194                        $this->catchSQLError( $e );
1195
1196                    }
1197
1198                    #} Interpret results (Result Set - multi-row)
1199                    if ( isset( $potentialRes ) && is_array( $potentialRes ) && count( $potentialRes ) > 0 ) {
1200
1201                        #} Has results, tidy + return
1202                        foreach ( $potentialRes as $resDataLine ) {
1203
1204                            // only columns?
1205                            if ( $onlyColumns && is_array( $onlyColumnsFieldArr ) && count( $onlyColumnsFieldArr ) > 0 ) {
1206
1207                                // only coumns return.
1208                                $resArr = array();
1209                                foreach ( $onlyColumnsFieldArr as $colDBKey => $colStr ) {
1210
1211                                    if (isset( $resDataLine->$colDBKey )) $resArr[ $colStr ] = $resDataLine->$colDBKey;
1212
1213                                }
1214                            } else {
1215
1216                                            // tidy
1217                                            $resArr = $this->tidy_transaction( $resDataLine, $withCustomFields );
1218
1219                            }
1220
1221                            if ( $withLineItems ) {
1222
1223                                // add all line item lines
1224                                $resArr['lineitems'] = $this->DAL()->lineitems->getLineitems(
1225                                    array(
1226                                        'associatedObjType' => ZBS_TYPE_TRANSACTION,
1227                                        'associatedObjID' => $resDataLine->ID,
1228                                        'perPage'         => 1000,
1229                                        'ignoreowner'     => zeroBSCRM_DAL2_ignoreOwnership( ZBS_TYPE_LINEITEM ),
1230                                    )
1231                                );
1232
1233                            }
1234
1235                            if ( $withTags ) {
1236
1237                                // add all tags lines
1238                                $resArr['tags'] = $this->DAL()->getTagsForObjID(
1239                                    array(
1240                                        'objtypeid' => ZBS_TYPE_TRANSACTION,
1241                                        'objid'     => $resDataLine->ID,
1242                                    )
1243                                );
1244
1245                            }
1246
1247                            if ( $withAssigned ) {
1248
1249                                /*
1250                                    This is for MULTIPLE (e.g. multi contact/companies assigned to an inv)
1251
1252                                    // add all assigned contacts/companies
1253                                    $res['contacts'] = $this->DAL()->contacts->getContacts(array(
1254                                        'hasObjTypeLinkedTo'=>ZBS_TYPE_TRANSACTION,
1255                                        'hasObjIDLinkedTo'=>$resDataLine->ID,
1256                                        'perPage'=>-1,
1257                                        'ignoreowner'=>zeroBSCRM_DAL2_ignoreOwnership(ZBS_TYPE_CONTACT)));
1258
1259                                    $res['companies'] = $this->DAL()->companies->getCompanies(array(
1260                                        'hasObjTypeLinkedTo'=>ZBS_TYPE_TRANSACTION,
1261                                        'hasObjIDLinkedTo'=>$resDataLine->ID,
1262                                        'perPage'=>-1,
1263                                        'ignoreowner'=>zeroBSCRM_DAL2_ignoreOwnership(ZBS_TYPE_COMPANY)));
1264
1265                                .. but we use 1:1, at least now: */
1266
1267                                    // add all assigned contacts/companies
1268                                    $resArr['contact'] = $this->DAL()->contacts->getContacts(
1269                                        array(
1270                                            'hasObjTypeLinkedTo' => ZBS_TYPE_TRANSACTION,
1271                                            'hasObjIDLinkedTo' => $resDataLine->ID,
1272                                            'page'        => 0,
1273                                            'perPage'     => 1, // FORCES 1
1274                                            'ignoreowner' => zeroBSCRM_DAL2_ignoreOwnership( ZBS_TYPE_CONTACT ),
1275                                        )
1276                                    );
1277
1278                                    $resArr['company'] = $this->DAL()->companies->getCompanies(
1279                                        array(
1280                                            'hasObjTypeLinkedTo' => ZBS_TYPE_TRANSACTION,
1281                                            'hasObjIDLinkedTo' => $resDataLine->ID,
1282                                            'page'        => 0,
1283                                            'perPage'     => 1, // FORCES 1
1284                                            'ignoreowner' => zeroBSCRM_DAL2_ignoreOwnership( ZBS_TYPE_COMPANY ),
1285                                        )
1286                                    );
1287
1288                            }
1289
1290                            if ( $external_source_uid ) {
1291                                $resArr['external_source_uid'] = $this->tidy_external_sources( $resDataLine );
1292                            }
1293
1294                            $res[] = $resArr;
1295
1296                        }
1297                    }
1298
1299                    return $res;
1300        }
1301
1302        /**
1303         * Returns a count of transactions (owned)
1304         * .. inc by status
1305         *
1306         * @return int count
1307         */
1308        public function getTransactionCount( $args = array() ) {
1309
1310            #} ============ LOAD ARGS =============
1311            $defaultArgs = array(
1312
1313                // Search/Filtering (leave as false to ignore)
1314                'withStatus'  => false, // will be str if used
1315
1316            // permissions
1317                'ignoreowner' => true, // this'll let you not-check the owner of obj
1318
1319            );
1320            foreach ( $defaultArgs as $argK => $argV ) {
1321                $$argK = $argV;
1322                if ( is_array( $args ) && isset( $args[ $argK ] ) ) {
1323                    if ( is_array( $args[ $argK ] ) ) {
1324                        $newData                                  = $$argK;
1325                            if ( ! is_array( $newData )) $newData = array();
1326                        foreach ( $args[ $argK ] as $subK => $subV ) {
1327                            $newData[ $subK ] = $subV;
1328                        }$$argK = $newData;
1329                    } else {
1330                                $$argK = $args[ $argK ]; }
1331                }
1332            }
1333            #} =========== / LOAD ARGS =============
1334
1335            $whereArr = array();
1336
1337            if ($withStatus !== false && ! empty( $withStatus )) $whereArr['status'] = array( 'zbst_status', '=', '%s', $withStatus );
1338
1339            return $this->DAL()->getFieldByWHERE(
1340                array(
1341                    'objtype'     => ZBS_TYPE_TRANSACTION,
1342                    'colname'     => 'COUNT(ID)',
1343                    'where'       => $whereArr,
1344                    'ignoreowner' => $ignoreowner,
1345                )
1346            );
1347        }
1348
1349        /**
1350         * adds or updates a transaction object
1351         *
1352         * @param array $args Associative array of arguments
1353         *              id (if update), owner, data (array of field data)
1354         *
1355         * @return int line ID
1356         */
1357        public function addUpdateTransaction( $args = array() ) {
1358
1359            global $ZBSCRM_t, $wpdb, $zbs;
1360
1361            #} Retrieve any cf
1362            $customFields = $this->DAL()->getActiveCustomFields( array( 'objtypeid' => ZBS_TYPE_TRANSACTION ) );
1363            // not req. $addrCustomFields = $this->DAL()->getActiveCustomFields(array('objtypeid'=>ZBS_TYPE_ADDRESS));
1364
1365            #} ============ LOAD ARGS =============
1366            $defaultArgs = array(
1367
1368                'id'                   => -1,
1369                'owner'                => -1,
1370
1371                // fields (directly)
1372                'data'                 => array(
1373
1374                    'status'          => '',
1375                    'type'            => '',
1376                    'ref'             => '',
1377                    'origin'          => '',
1378                    'parent'          => '',
1379                    'hash'            => '',
1380                    'title'           => '',
1381                    'desc'            => '',
1382                    'date'            => '',
1383                    'customer_ip'     => '',
1384                    'currency'        => '',
1385                    'net'             => '',
1386                    'fee'             => '',
1387                    'discount'        => '',
1388                    'shipping'        => '',
1389                    'shipping_taxes'  => '',
1390                    'shipping_tax'    => '',
1391                    'taxes'           => '',
1392                    'tax'             => '',
1393                    'total'           => '',
1394                    'date_paid'       => null,
1395                    'date_completed'  => null,
1396
1397                    // lineitems:
1398                    'lineitems'       => false,
1399                    // will be an array of lineitem lines (as per matching lineitem database model)
1400                    // note:    if no change desired, pass "false"
1401                    // if removal of all/change, pass empty array
1402
1403                    // Note Custom fields may be passed here, but will not have defaults so check isset()
1404
1405                    // obj links:
1406                    'contacts'        => false, // array of id's
1407                    'companies'       => false, // array of id's
1408                    'invoice_id'      => false, // ID if assigned to an invoice
1409
1410                    // tags
1411                    'tags'            => -1, // pass an array of tag ids or tag strings
1412                    'tag_mode'        => 'replace', // replace|append|remove
1413
1414                    'externalSources' => -1, // if this is an array(array('source'=>src,'uid'=>uid),multiple()) it'll add :)
1415
1416                    // allow this to be set for MS sync etc.
1417                    'created'         => -1,
1418                    'lastupdated'     => '',
1419
1420                ),
1421
1422                'limitedFields'        => -1, // if this is set it OVERRIDES data (allowing you to set specific fields + leave rest in tact)
1423            // ^^ will look like: array(array('key'=>x,'val'=>y,'type'=>'%s'))
1424
1425            // this function as DAL1 func did.
1426                'extraMeta'            => -1,
1427                'automatorPassthrough' => -1,
1428
1429                'silentInsert'         => false, // this was for init Migration - it KILLS all IA for newTransaction (because is migrating, not creating new :) this was -1 before
1430
1431                'do_not_update_blanks' => false, // this allows you to not update fields if blank (same as fieldoverride for extsource -> in)
1432                '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
1433
1434            );
1435            foreach ( $defaultArgs as $argK => $argV ) {
1436                $$argK = $argV;
1437                if ( is_array( $args ) && isset( $args[ $argK ] ) ) {
1438                    if ( is_array( $args[ $argK ] ) ) {
1439                        $newData                                  = $$argK;
1440                            if ( ! is_array( $newData )) $newData = array();
1441                        foreach ( $args[ $argK ] as $subK => $subV ) {
1442                            $newData[ $subK ] = $subV;
1443                        }$$argK = $newData;
1444                    } else {
1445                                $$argK = $args[ $argK ]; }
1446                }
1447            }
1448
1449            // Needs this to grab custom fields (if passed) too :)
1450            if (is_array( $customFields )) foreach ( $customFields as $cK => $cF ) {
1451
1452                // only for data, limited fields below
1453                if ( is_array( $data ) ) {
1454
1455                    if (isset( $args['data'][ $cK ] )) $data[ $cK ] = $args['data'][ $cK ];
1456
1457                }
1458            }
1459
1460            // this takes limited fields + checks through for custom fields present
1461            // (either as key zbst_source or source, for example)
1462            // then switches them into the $data array, for separate update
1463            // where this'll fall over is if NO normal contact data is sent to update, just custom fields
1464            if ( is_array( $limitedFields ) && is_array( $customFields ) ) {
1465
1466                    // $customFieldKeys = array_keys($customFields);
1467                    $newLimitedFields = array();
1468
1469                    // cycle through
1470                foreach ( $limitedFields as $field ) {
1471
1472                    // some weird case where getting empties, so added check
1473                    if ( isset( $field['key'] ) && ! empty( $field['key'] ) ) {
1474
1475                        $dePrefixed = ''; // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase
1476                        if ( str_starts_with( $field['key'], 'zbst_' ) ) {
1477                            $dePrefixed = substr( $field['key'], strlen( 'zbst_' ) ); // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase
1478                        }
1479
1480                        if ( isset( $customFields[ $field['key'] ] ) ) {
1481
1482                            // is custom, move to data
1483                            $data[ $field['key'] ] = $field['val'];
1484
1485                        } elseif ( ! empty( $dePrefixed ) && isset( $customFields[ $dePrefixed ] ) ) {
1486
1487                            // is custom, move to data
1488                            $data[ $dePrefixed ] = $field['val'];
1489
1490                        } else {
1491
1492                            // add it to limitedFields (it's not dealt with post-update)
1493                            $newLimitedFields[] = $field;
1494                        }
1495                    }
1496                }
1497
1498                    // move this back in
1499                    $limitedFields = $newLimitedFields;
1500                    unset( $newLimitedFields );
1501
1502            }
1503
1504            #} =========== / LOAD ARGS ============
1505
1506            #} ========== CHECK FIELDS ============
1507
1508            $id = (int) $id;
1509
1510            // here we check that the potential owner CAN even own
1511            if ($owner > 0 && ! user_can( $owner, 'admin_zerobs_usr' )) $owner = -1;
1512
1513            // if owner = -1, add current
1514            if ( ! isset( $owner ) || $owner === -1 ) {
1515                $owner = zeroBSCRM_user(); }
1516
1517            if ( is_array( $limitedFields ) ) {
1518
1519                // LIMITED UPDATE (only a few fields.)
1520                if ( ! is_array( $limitedFields ) || count( $limitedFields ) <= 0) return false;
1521                // REQ. ID too (can only update)
1522                if (empty( $id ) || $id <= 0) return false;
1523
1524            } else {
1525
1526                // NORMAL, FULL UPDATE
1527
1528            }
1529
1530            #} If no status, and default is specified in settings, add that in :)
1531            // for now, I copied this from addUpdateTransaction: 'Unknown';
1532            if ( $data['status'] === null || ! isset( $data['status'] ) || empty( $data['status'] ) ) {
1533
1534                // Default status for obj? -> this one gets for contacts ->
1535                $data['status'] = __( 'Unknown', 'zero-bs-crm' ); // zeroBSCRM_getSetting('defaultstatus');
1536
1537            }
1538
1539            #} ========= / CHECK FIELDS ===========
1540
1541            #} ========= OVERRIDE SETTING (Deny blank overrides) ===========
1542
1543            // this only functions if externalsource is set (e.g. api/form, etc.)
1544            if ( isset( $data['externalSources'] ) && is_array( $data['externalSources'] ) && count( $data['externalSources'] ) > 0 ) {
1545                if ( zeroBSCRM_getSetting( 'fieldoverride' ) == '1' ) {
1546
1547                    $do_not_update_blanks = true;
1548
1549                }
1550            }
1551
1552            // either ext source + setting, or set by the func call
1553            if ( $do_not_update_blanks ) {
1554
1555                // this setting says 'don't override filled-out data with blanks'
1556                // so here we check through any passed blanks + convert to limitedFields
1557                // only matters if $id is set (there is somt to update not add
1558                if ( isset( $id ) && ! empty( $id ) && $id > 0 ) {
1559
1560                    // get data to copy over (for now, this is required to remove 'fullname' etc.)
1561                    $dbData = $this->db_ready_transaction( $data );
1562                    // unset($dbData['id']); // this is unset because we use $id, and is update, so not req. legacy issue
1563                    // 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 :)
1564
1565                    $origData    = $data; // $data = array();
1566                    $limitedData = array(); // array(array('key'=>'zbst_x','val'=>y,'type'=>'%s'))
1567
1568                    // cycle through + translate into limitedFields (removing any blanks, or arrays (e.g. externalSources))
1569                    // we also have to remake a 'faux' data (removing blanks for tags etc.) for the post-update updates
1570                    foreach ( $dbData as $k => $v ) {
1571
1572                        $intV = (int) $v;
1573
1574                        // only add if valuenot empty
1575                        if ( ! is_array( $v ) && ! empty( $v ) && $v != '' && $v !== 0 && $v !== -1 && $intV !== -1 ) {
1576
1577                            // add to update arr
1578                            $limitedData[] = array(
1579                                'key'  => 'zbst_' . $k, // we have to add zbst_ here because translating from data -> limited fields
1580                                'val'  => $v,
1581                                'type' => $this->getTypeStr( 'zbst_' . $k ),
1582                            );
1583
1584                            // add to remade $data for post-update updates
1585                            $data[ $k ] = $v;
1586
1587                        }
1588                    }
1589
1590                    // copy over
1591                    $limitedFields = $limitedData;
1592
1593                } // / if ID
1594
1595            } // / if do_not_update_blanks
1596
1597            #} ========= / OVERRIDE SETTING (Deny blank overrides) ===========
1598
1599            #} ========= BUILD DATA ===========
1600
1601            $update  = false;
1602            $dataArr = array();
1603            $typeArr = array();
1604
1605            if ( is_array( $limitedFields ) ) {
1606
1607                // LIMITED FIELDS
1608                $update = true;
1609
1610                // cycle through
1611                foreach ( $limitedFields as $field ) {
1612
1613                    // some weird case where getting empties, so added check
1614                    if ( ! empty( $field['key'] ) ) {
1615                        $dataArr[ $field['key'] ] = $field['val'];
1616                        $typeArr[]                = $field['type'];
1617                    }
1618                }
1619
1620                // add update time
1621                if ( ! isset( $dataArr['zbst_lastupdated'] ) ) {
1622                    $dataArr['zbst_lastupdated'] = time();
1623                    $typeArr[]                   = '%d'; }
1624            } else {
1625
1626                        // FULL UPDATE/INSERT
1627
1628                        // contacts - avoid dupes
1629                if ( isset( $data['contacts'] ) && is_array( $data['contacts'] ) ) {
1630
1631                    $coArr = array();
1632                    foreach ( $data['contacts'] as $c ) {
1633                        $cI = (int) $c;
1634                        if ($cI > 0 && ! in_array( $cI, $coArr )) $coArr[] = $cI;
1635                    }
1636
1637                    // reset the main
1638                    if (count( $coArr ) > 0)
1639                    $data['contacts']      = $coArr;
1640                    else $data['contacts'] = 'unset';
1641                    unset( $coArr );
1642
1643                }
1644
1645                // companies - avoid dupes
1646                if ( isset( $data['companies'] ) && is_array( $data['companies'] ) ) {
1647
1648                    $coArr = array();
1649                    foreach ( $data['companies'] as $c ) {
1650                        $cI = (int) $c;
1651                        if ($cI > 0 && ! in_array( $cI, $coArr )) $coArr[] = $cI;
1652                    }
1653
1654                    // reset the main
1655                    if (count( $coArr ) > 0)
1656                        $data['companies']  = $coArr;
1657                    else $data['companies'] = 'unset';
1658                    unset( $coArr );
1659
1660                }
1661
1662                // UPDATE
1663                $dataArr = array(
1664
1665                    // ownership
1666                    // no need to update these (as of yet) - can't move teams etc.
1667                    // 'zbs_site' => zeroBSCRM_installSite(),
1668                    // 'zbs_team' => zeroBSCRM_installTeam(),
1669                    // 'zbs_owner' => $owner,
1670
1671                    'zbst_status'         => $data['status'],
1672                    'zbst_type'           => $data['type'],
1673                    'zbst_ref'            => $data['ref'],
1674                    'zbst_origin'         => $data['origin'],
1675                    'zbst_parent'         => $data['parent'],
1676                    'zbst_hash'           => $data['hash'],
1677                    'zbst_title'          => $data['title'],
1678                    'zbst_desc'           => $data['desc'],
1679                    'zbst_date'           => $data['date'],
1680                    'zbst_customer_ip'    => $data['customer_ip'],
1681                    'zbst_currency'       => $data['currency'],
1682                    'zbst_net'            => $data['net'],
1683                    'zbst_fee'            => $data['fee'],
1684                    'zbst_discount'       => $data['discount'],
1685                    'zbst_shipping'       => $data['shipping'],
1686                    'zbst_shipping_taxes' => $data['shipping_taxes'],
1687                    'zbst_shipping_tax'   => $data['shipping_tax'],
1688                    'zbst_taxes'          => $data['taxes'],
1689                    'zbst_tax'            => $data['tax'],
1690                    'zbst_total'          => $data['total'],
1691                    'zbst_date_paid'      => $data['date_paid'],
1692                    'zbst_date_completed' => $data['date_completed'],
1693                    'zbst_lastupdated'    => time(),
1694
1695                );
1696
1697                $typeArr = array( // field data types
1698                            // '%d',  // site
1699                            // '%d',  // team
1700                            // '%d',  // owner
1701
1702                    '%s',
1703                    '%s',
1704                    '%s',
1705                    '%s',
1706                    '%d',
1707                    '%s',
1708                    '%s',
1709                    '%s',
1710                    '%d',
1711                    '%s',
1712                    '%s',
1713                    '%s',
1714                    '%s',
1715                    '%s',
1716                    '%s',
1717                    '%s',
1718                    '%s',
1719                    '%s',
1720                    '%s',
1721                    '%s',
1722                    '%d',
1723                    '%d',
1724                    '%d',
1725
1726                );
1727
1728                if ( ! empty( $id ) && $id > 0 ) {
1729
1730                    // is update
1731                    $update = true;
1732
1733                } else {
1734
1735                    // INSERT (get's few extra :D)
1736                    $update               = false;
1737                    $dataArr['zbs_site']  = zeroBSCRM_site();
1738                    $typeArr[]            = '%d';
1739                    $dataArr['zbs_team']  = zeroBSCRM_team();
1740                    $typeArr[]            = '%d';
1741                    $dataArr['zbs_owner'] = $owner;
1742                    $typeArr[]            = '%d';
1743                    if ( isset( $data['created'] ) && ! empty( $data['created'] ) && $data['created'] !== -1 ) {
1744                        $dataArr['zbst_created'] = $data['created'];
1745                        $typeArr[]               = '%d';
1746                    } else {
1747                        $dataArr['zbst_created'] = time();
1748                        $typeArr[]               = '%d';
1749                    }
1750                    // if no transaction date is passed on creation, use time()
1751                    // allow for 0 value (valid epoch time)
1752                    if ( empty( $dataArr['zbst_date'] ) && $dataArr['zbst_date'] !== 0 ) {
1753                        $dataArr['zbst_date'] = time();
1754                    }
1755                }
1756            }
1757
1758            #} ========= / BUILD DATA ===========
1759
1760            #} ============================================================
1761            #} ========= CHECK force_uniques & not_empty & max_len ========
1762
1763            // if we're passing limitedFields we skip these, for now
1764            // #v3.1 - would make sense to unique/nonempty check just the limited fields. #gh-145
1765            if ( ! is_array( $limitedFields ) ) {
1766
1767                // verify uniques
1768                if ( ! $this->verifyUniqueValues( $data, $id )) return false; // / fails unique field verify
1769
1770                // verify not_empty
1771                if ( ! $this->verifyNonEmptyValues( $data )) return false; // / fails empty field verify
1772
1773            }
1774
1775            // whatever we do we check for max_len breaches and abbreviate to avoid wpdb rejections
1776            $dataArr = $this->wpdbChecks( $dataArr );
1777
1778            #} ========= / CHECK force_uniques & not_empty ================
1779            #} ============================================================
1780
1781            #} Check if ID present
1782            if ( $update ) {
1783
1784                #} Check if obj exists (here) - for now just brutal update (will error when doesn't exist)
1785                $originalStatus = $this->getTransactionStatus( $id );
1786
1787                // log any change of status
1788                if ( isset( $dataArr['zbst_status'] ) && ! empty( $dataArr['zbst_status'] ) && ! empty( $originalStatus ) && $dataArr['zbst_status'] != $originalStatus ) {
1789
1790                    // status change
1791                    $statusChange = array(
1792                        'from' => $originalStatus,
1793                        'to'   => $dataArr['zbst_status'],
1794                    );
1795                }
1796
1797                #} Attempt update
1798                if ( $wpdb->update(
1799                    $ZBSCRM_t['transactions'],
1800                    $dataArr,
1801                    array( // where
1802                        'ID' => $id,
1803                    ),
1804                    $typeArr,
1805                    array( // where data types
1806                        '%d',
1807                    )
1808                ) !== false ) {
1809
1810                        // if passing limitedFields instead of data, we ignore the following
1811                            // this doesn't work, because data is in args default as arr
1812                            // if (isset($data) && is_array($data)){
1813                            // so...
1814                    if ( ! isset( $limitedFields ) || ! is_array( $limitedFields ) || $limitedFields == -1 ) {
1815
1816                        // Line Items ====
1817
1818                        // line item work
1819                        if ( isset( $data['lineitems'] ) && is_array( $data['lineitems'] ) ) {
1820
1821                                // if array passed, update, even if removing
1822                            if ( count( $data['lineitems'] ) > 0 ) {
1823
1824                                // passed, for now this is BRUTAL and just clears old ones + readds
1825                                // once live, discuss how to refactor to be less brutal.
1826
1827                                // delete all lineitems
1828                                $this->DAL()->lineitems->deleteLineItemsForObject(
1829                                    array(
1830                                        'objID'   => $id,
1831                                        'objType' => ZBS_TYPE_TRANSACTION,
1832                                    )
1833                                );
1834
1835                                // addupdate each
1836                                foreach ( $data['lineitems'] as $lineitem ) {
1837
1838                                    // slight rejig of passed so works cleanly with data array style
1839                                    $lineItemID                               = false;
1840                                    if (isset( $lineitem['ID'] )) $lineItemID = $lineitem['ID'];
1841                                    $this->DAL()->lineitems->addUpdateLineitem(
1842                                        array(
1843                                            'id'          => $lineItemID,
1844                                            'linkedObjType' => ZBS_TYPE_TRANSACTION,
1845                                            'linkedObjID' => $id,
1846                                            'data'        => $lineitem,
1847                                        )
1848                                    );
1849
1850                                }
1851                            } else {
1852
1853                                // delete all lineitems
1854                                $this->DAL()->lineitems->deleteLineItemsForObject(
1855                                    array(
1856                                        'objID'   => $id,
1857                                        'objType' => ZBS_TYPE_TRANSACTION,
1858                                    )
1859                                );
1860
1861                            }
1862                        }
1863
1864                        // / Line Items ====
1865
1866                        // OBJ LINKS - to contacts/companies
1867                        $this->addUpdateObjectLinks( $id, $data['contacts'], ZBS_TYPE_CONTACT );
1868                        $this->addUpdateObjectLinks( $id, $data['companies'], ZBS_TYPE_COMPANY );
1869
1870                        // IA also gets 'againstid' historically, but we'll pass as 'against id's'
1871                        $againstIDs = array(
1872                            'contacts'  => $data['contacts'],
1873                            'companies' => $data['companies'],
1874                        );
1875
1876                        // OBJ Links - to invoices
1877                        $this->addUpdateObjectLinks( $id, array( $data['invoice_id'] ), ZBS_TYPE_INVOICE );
1878
1879                        // if not-empty inv id, check if needs to be mark paid!
1880                        if ( ! $do_not_mark_invoices && ! empty( $data['invoice_id'] ) && $data['invoice_id'] > 0 ) {
1881
1882                                        // function to check ammount due and mark invoice as paid if amount due <= 0.
1883                                        zeroBSCRM_check_amount_due_mark_paid( $data['invoice_id'] );
1884
1885                        }
1886
1887                                // tags
1888                        if ( isset( $data['tags'] ) && is_array( $data['tags'] ) ) {
1889
1890                            $this->addUpdateTransactionTags(
1891                                array(
1892                                    'id'        => $id,
1893                                    'tag_input' => $data['tags'],
1894                                    'mode'      => $data['tag_mode'],
1895                                )
1896                            );
1897
1898                        }
1899
1900                                // externalSources
1901                                $approvedExternalSource = $this->DAL()->addUpdateExternalSources(
1902                                    array(
1903                                        'obj_id'           => $id,
1904                                        'obj_type_id'      => ZBS_TYPE_TRANSACTION,
1905                                        'external_sources' => isset( $data['externalSources'] ) ? $data['externalSources'] : array(),
1906                                    )
1907                                ); // for IA below
1908
1909                        // Custom fields?
1910
1911                        #} Cycle through + add/update if set
1912                        if (is_array( $customFields )) foreach ( $customFields as $cK => $cF ) {
1913
1914                            // any?
1915                            if ( isset( $data[ $cK ] ) ) {
1916
1917                                // add update
1918                                $cfID = $this->DAL()->addUpdateCustomField(
1919                                    array(
1920                                        'data' => array(
1921                                            'objtype' => ZBS_TYPE_TRANSACTION,
1922                                            'objid'   => $id,
1923                                            'objkey'  => $cK,
1924                                            'objval'  => $data[ $cK ],
1925                                        ),
1926                                    )
1927                                );
1928
1929                            }
1930                        }
1931
1932                        // / Custom Fields
1933
1934                    } else {
1935
1936                        // limited fields
1937                        // here we set what will not have been passed as blanks, for the IA to use below.
1938                        $againstIDs             = false;
1939                        $approvedExternalSource = '';
1940
1941                    }
1942
1943                        // Any extra meta keyval pairs
1944                        // BRUTALLY updates (no checking)
1945                        $confirmedExtraMeta = false;
1946                    if ( is_array( $extraMeta ) ) { // phpcs:ignore VariableAnalysis.CodeAnalysis.VariableAnalysis.UndefinedVariable,WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase
1947
1948                        $confirmedExtraMeta = array();
1949
1950                        foreach ( $extraMeta as $k => $v ) {
1951
1952                            #} This won't fix stupid keys, just catch basic fails...
1953                            $cleanKey = strtolower( str_replace( ' ', '_', $k ) );
1954
1955                            #} Brutal update
1956                            // update_post_meta($postID, 'zbs_customer_extra_'.$cleanKey, $v);
1957                            $this->DAL()->updateMeta( ZBS_TYPE_TRANSACTION, $id, 'extra_' . $cleanKey, $v );
1958
1959                            #} Add it to this, which passes to IA
1960                            $confirmedExtraMeta[ $cleanKey ] = $v;
1961
1962                        }
1963                    }
1964
1965                        #} INTERNAL AUTOMATOR
1966                        #} &
1967                        #} FALLBACKS
1968                        // UPDATING CONTACT
1969                    if ( ! $silentInsert ) {
1970
1971                        // catch dirty flag (update of status) (note, after update_post_meta - as separate)
1972                        // if (isset($_POST['zbst_status_dirtyflag']) && $_POST['zbst_status_dirtyflag'] == "1"){
1973                        // actually here, it's set above
1974                        if ( isset( $statusChange ) && is_array( $statusChange ) ) {
1975
1976                            // status has changed
1977
1978                            // IA
1979                            zeroBSCRM_FireInternalAutomator(
1980                                'transaction.status.update',
1981                                array(
1982                                    'id'         => $id,
1983                                    'againstids' => $againstIDs,
1984                                    'data'       => $data,
1985                                    'from'       => $statusChange['from'],
1986                                    'to'         => $statusChange['to'],
1987                                )
1988                            );
1989
1990                        }
1991
1992                        // IA General transaction update (2.87+)
1993                        zeroBSCRM_FireInternalAutomator(
1994                            'transaction.update',
1995                            array(
1996                                'id'                   => $id,
1997                                'data'                 => $data,
1998                                'againstids'           => $againstIDs,
1999                                'extsource'            => $approvedExternalSource,
2000                                'automatorpassthrough' => $automatorPassthrough, #} This passes through any custom log titles or whatever into the Internal automator recipe.
2001                                'extraMeta'            => $confirmedExtraMeta, #} This is the "extraMeta" passed (as saved)
2002                            )
2003                        );
2004
2005                                $data['id'] = $id; // phpcs:ignore VariableAnalysis.CodeAnalysis.VariableAnalysis.UndefinedVariable
2006                                $this->events_manager->transaction()->updated( $data ); // phpcs:ignore VariableAnalysis.CodeAnalysis.VariableAnalysis.UndefinedVariable
2007
2008
2009                    }
2010
2011                        // Successfully updated - Return id
2012                        return $id;
2013
2014                } else {
2015
2016                    $msg = __( 'DB Update Failed', 'zero-bs-crm' );
2017                    $zbs->DAL->addError( 302, $this->objectType, $msg, $dataArr );
2018
2019                    // FAILED update
2020                    return false;
2021
2022                }
2023            } else {
2024
2025                        #} No ID - must be an INSERT
2026                if ( $wpdb->insert(
2027                    $ZBSCRM_t['transactions'],
2028                    $dataArr,
2029                    $typeArr
2030                ) > 0 ) {
2031
2032                    #} Successfully inserted, lets return new ID
2033                    $newID = $wpdb->insert_id;
2034
2035                    // Line Items ====
2036
2037                    // line item work
2038                    if ( isset( $data['lineitems'] ) && is_array( $data['lineitems'] ) ) {
2039
2040                        // if array passed, update, even if removing
2041                        if ( count( $data['lineitems'] ) > 0 ) {
2042
2043                            // passed, for now this is BRUTAL and just clears old ones + readds
2044                            // once live, discuss how to refactor to be less brutal.
2045
2046                                // delete all lineitems
2047                                $this->DAL()->lineitems->deleteLineItemsForObject(
2048                                    array(
2049                                        'objID'   => $newID,
2050                                        'objType' => ZBS_TYPE_TRANSACTION,
2051                                    )
2052                                );
2053
2054                                // addupdate each
2055                            foreach ( $data['lineitems'] as $lineitem ) {
2056
2057                                // slight rejig of passed so works cleanly with data array style
2058                                $lineItemID                               = false;
2059                                if (isset( $lineitem['ID'] )) $lineItemID = $lineitem['ID'];
2060                                $this->DAL()->lineitems->addUpdateLineitem(
2061                                    array(
2062                                        'id'            => $lineItemID,
2063                                        'linkedObjType' => ZBS_TYPE_TRANSACTION,
2064                                        'linkedObjID'   => $newID,
2065                                        'data'          => $lineitem,
2066                                    )
2067                                );
2068
2069                            }
2070                        } else {
2071
2072                                    // delete all lineitems
2073                                    $this->DAL()->lineitems->deleteLineItemsForObject(
2074                                        array(
2075                                            'objID'   => $newID,
2076                                            'objType' => ZBS_TYPE_TRANSACTION,
2077                                        )
2078                                    );
2079
2080                        }
2081                    }
2082
2083                    // / Line Items ====
2084
2085                    // OBJ LINKS - to contacts/companies
2086                    $this->addUpdateObjectLinks( $newID, $data['contacts'], ZBS_TYPE_CONTACT );
2087                    $this->addUpdateObjectLinks( $newID, $data['companies'], ZBS_TYPE_COMPANY );
2088                    // IA also gets 'againstid' historically, but we'll pass as 'against id's'
2089                    $againstIDs = array(
2090                        'contacts'  => $data['contacts'],
2091                        'companies' => $data['companies'],
2092                    );
2093
2094                    // OBJ Links - to invoices
2095                    $this->addUpdateObjectLinks( $newID, array( $data['invoice_id'] ), ZBS_TYPE_INVOICE );
2096
2097                    // if not-empty inv id, check if needs to be mark paid!
2098                    if ( ! $do_not_mark_invoices && ! empty( $data['invoice_id'] ) && $data['invoice_id'] > 0 ) {
2099
2100                        // function to check ammount due and mark invoice as paid if amount due <= 0.
2101                        zeroBSCRM_check_amount_due_mark_paid( $data['invoice_id'] );
2102
2103                    }
2104
2105                    // tags
2106                    if ( isset( $data['tags'] ) && is_array( $data['tags'] ) ) {
2107
2108                        $this->addUpdateTransactionTags(
2109                            array(
2110                                'id'        => $newID,
2111                                'tag_input' => $data['tags'],
2112                                'mode'      => $data['tag_mode'],
2113                            )
2114                        );
2115
2116                    }
2117
2118                    // externalSources
2119                    $approvedExternalSource = $this->DAL()->addUpdateExternalSources(
2120                        array(
2121                            'obj_id'           => $newID,
2122                            'obj_type_id'      => ZBS_TYPE_TRANSACTION,
2123                            'external_sources' => isset( $data['externalSources'] ) ? $data['externalSources'] : array(),
2124                        )
2125                    ); // for IA below
2126
2127                    // Custom fields?
2128
2129                    #} Cycle through + add/update if set
2130                    if (is_array( $customFields )) foreach ( $customFields as $cK => $cF ) {
2131
2132                                // any?
2133                        if ( isset( $data[ $cK ] ) ) {
2134
2135                            // add update
2136                            $cfID = $this->DAL()->addUpdateCustomField(
2137                                array(
2138                                    'data' => array(
2139                                        'objtype' => ZBS_TYPE_TRANSACTION,
2140                                        'objid'   => $newID,
2141                                        'objkey'  => $cK,
2142                                        'objval'  => $data[ $cK ],
2143                                    ),
2144                                )
2145                            );
2146
2147                        }
2148                    }
2149
2150                    // / Custom Fields
2151
2152                    #} Any extra meta keyval pairs?
2153                    // BRUTALLY updates (no checking)
2154                    $confirmedExtraMeta = false;
2155                    if ( is_array( $extraMeta ) ) { // phpcs:ignore VariableAnalysis.CodeAnalysis.VariableAnalysis.UndefinedVariable,WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase
2156
2157                        $confirmedExtraMeta = array();
2158
2159                        foreach ( $extraMeta as $k => $v ) {
2160
2161                            #} This won't fix stupid keys, just catch basic fails...
2162                            $cleanKey = strtolower( str_replace( ' ', '_', $k ) );
2163
2164                            #} Brutal update
2165                            // update_post_meta($postID, 'zbs_customer_extra_'.$cleanKey, $v);
2166                            $this->DAL()->updateMeta( ZBS_TYPE_TRANSACTION, $newID, 'extra_' . $cleanKey, $v );
2167
2168                            #} Add it to this, which passes to IA
2169                            $confirmedExtraMeta[ $cleanKey ] = $v;
2170
2171                        }
2172                    }
2173
2174                    #} INTERNAL AUTOMATOR
2175                    #} &
2176                    #} FALLBACKS
2177                    // NEW CONTACT
2178                    if ( ! $silentInsert ) {
2179
2180                        #} Add to automator
2181                        zeroBSCRM_FireInternalAutomator(
2182                            'transaction.new',
2183                            array(
2184                                'id'                   => $newID,
2185                                'data'                 => $data,
2186                                'againstids'           => $againstIDs,
2187                                'extsource'            => $approvedExternalSource,
2188                                'automatorpassthrough' => $automatorPassthrough, #} This passes through any custom log titles or whatever into the Internal automator recipe.
2189                                'extraMeta'            => $confirmedExtraMeta, #} This is the "extraMeta" passed (as saved)
2190                            )
2191                        );
2192                                        $data['id'] = $newID; // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase, VariableAnalysis.CodeAnalysis.VariableAnalysis.UndefinedVariable
2193                                        $this->events_manager->transaction()->created( $data ); // phpcs:ignore VariableAnalysis.CodeAnalysis.VariableAnalysis.UndefinedVariable
2194                    }
2195
2196                    return $newID;
2197
2198                } else {
2199
2200                    $msg = __( 'DB Insert Failed', 'zero-bs-crm' );
2201                    $zbs->DAL->addError( 303, $this->objectType, $msg, $dataArr );
2202
2203                    #} Failed to Insert
2204                    return false;
2205
2206                }
2207            }
2208
2209            return false;
2210        }
2211
2212        /**
2213         * adds or updates a transaction's tags
2214         * ... this is really just a wrapper for addUpdateObjectTags
2215         *
2216         * @param array $args Associative array of arguments
2217         *              id (if update), owner, data (array of field data)
2218         *
2219         * @return int line ID
2220         */
2221        public function addUpdateTransactionTags( $args = array() ) {
2222
2223            global $ZBSCRM_t, $wpdb;
2224
2225            #} ============ LOAD ARGS =============
2226            $defaultArgs = array(
2227
2228                'id'        => -1,
2229
2230                // generic pass-through (array of tag strings or tag IDs):
2231                'tag_input' => -1,
2232
2233                // or either specific:
2234                'tagIDs'    => -1,
2235                'tags'      => -1,
2236
2237                'mode'      => 'append',
2238
2239            );
2240            foreach ( $defaultArgs as $argK => $argV ) {
2241                $$argK = $argV;
2242                if ( is_array( $args ) && isset( $args[ $argK ] ) ) {
2243                    if ( is_array( $args[ $argK ] ) ) {
2244                        $newData                                  = $$argK;
2245                            if ( ! is_array( $newData )) $newData = array();
2246                        foreach ( $args[ $argK ] as $subK => $subV ) {
2247                            $newData[ $subK ] = $subV;
2248                        }$$argK = $newData;
2249                    } else {
2250                                $$argK = $args[ $argK ]; }
2251                }
2252            }
2253            #} =========== / LOAD ARGS ============
2254
2255            #} ========== CHECK FIELDS ============
2256
2257            // check id
2258            $id = (int) $id;
2259            if (empty( $id ) || $id <= 0) return false;
2260
2261            #} ========= / CHECK FIELDS ===========
2262
2263            return $this->DAL()->addUpdateObjectTags(
2264                array(
2265                    'objtype'   => ZBS_TYPE_TRANSACTION,
2266                    'objid'     => $id,
2267                    'tag_input' => $tag_input,
2268                    'tags'      => $tags,
2269                    'tagIDs'    => $tagIDs,
2270                    'mode'      => $mode,
2271                )
2272            );
2273        }
2274
2275        /**
2276         * updates status for a transaction (no blanks allowed)
2277         *
2278         * @param int id transaction ID
2279         * @param string transaction Status
2280         *
2281         * @return bool
2282         */
2283        public function setTransactionStatus( $id = -1, $status = -1 ) {
2284
2285            global $zbs;
2286
2287            $id = (int) $id;
2288
2289            if ( $id > 0 && ! empty( $status ) && $status !== -1 ) {
2290
2291                return $this->addUpdateTransaction(
2292                    array(
2293                        'id'            => $id,
2294                        'limitedFields' => array(
2295                            array(
2296                                'key'  => 'zbst_status',
2297                                'val'  => $status,
2298                                'type' => '%s',
2299                            ),
2300                        ),
2301                    )
2302                );
2303
2304            }
2305
2306            return false;
2307        }
2308
2309        /**
2310         * deletes a transaction object
2311         *
2312         * @param array $args Associative array of arguments
2313         *              id
2314         *
2315         * @return int success;
2316         */
2317        public function deleteTransaction( $args = array() ) {
2318
2319            global $ZBSCRM_t, $wpdb, $zbs;
2320
2321            #} ============ LOAD ARGS =============
2322            $defaultArgs = array(
2323
2324                'id'          => -1,
2325                'saveOrphans' => false,
2326
2327            );
2328            foreach ( $defaultArgs as $argK => $argV ) {
2329                $$argK = $argV;
2330                if ( is_array( $args ) && isset( $args[ $argK ] ) ) {
2331                    if ( is_array( $args[ $argK ] ) ) {
2332                        $newData                                  = $$argK;
2333                            if ( ! is_array( $newData )) $newData = array();
2334                        foreach ( $args[ $argK ] as $subK => $subV ) {
2335                            $newData[ $subK ] = $subV;
2336                        }$$argK = $newData;
2337                    } else {
2338                                $$argK = $args[ $argK ]; }
2339                }
2340            }
2341            #} =========== / LOAD ARGS ============
2342
2343            #} Check ID & Delete :)
2344            $id = (int) $id;
2345            if ( ! empty( $id ) && $id > 0 ) {
2346
2347                // delete orphans?
2348                if ( $saveOrphans === false ) {
2349
2350                    // delete any tag links
2351                    $this->DAL()->deleteTagObjLinks(
2352                        array(
2353
2354                            'objtype' => ZBS_TYPE_TRANSACTION,
2355                            'objid'   => $id,
2356
2357                        )
2358                    );
2359
2360                    // delete any external source information
2361                    $this->DAL()->delete_external_sources(
2362                        array(
2363
2364                            'obj_type'   => ZBS_TYPE_TRANSACTION,
2365                            'obj_id'     => $id,
2366                            'obj_source' => 'all',
2367
2368                        )
2369                    );
2370
2371                }
2372
2373                $del = zeroBSCRM_db2_deleteGeneric( $id, 'transactions' );
2374
2375                #} Add to automator
2376                zeroBSCRM_FireInternalAutomator(
2377                    'transaction.delete',
2378                    array(
2379                        'id'          => $id,
2380                        'saveOrphans' => $saveOrphans,
2381                    )
2382                );
2383
2384                    $this->events_manager->transaction()->deleted( $id );
2385
2386                return $del;
2387
2388            }
2389
2390            return false;
2391        }
2392
2393        /**
2394         * tidy's the object from wp db into clean array
2395         *
2396         * @param array $obj (DB obj)
2397         *
2398         * @return array transaction (clean obj)
2399         */
2400        private function tidy_transaction( $obj = false, $withCustomFields = false ) {
2401
2402            $res = false;
2403
2404            if ( isset( $obj->ID ) ) {
2405                $res       = array();
2406                $res['id'] = $obj->ID;
2407                /*
2408                `zbs_site` INT NULL DEFAULT NULL,
2409                `zbs_team` INT NULL DEFAULT NULL,
2410                `zbs_owner` INT NOT NULL,
2411                */
2412                $res['owner'] = $obj->zbs_owner;
2413
2414                $res['status'] = $this->stripSlashes( $obj->zbst_status );
2415                $res['type']   = $this->stripSlashes( $obj->zbst_type );
2416
2417                // status categorisation (basically did it succeed?)
2418                // this is dictated by Transaction Status settings: /wp-admin/admin.php?page=zerobscrm-plugin-settings&tab=transactions
2419                $res['status_bool'] = ( 'all' === $this->getTransactionStatusesToInclude() || in_array( $res['status'], $this->getTransactionStatusesToInclude() ) ? 1 : -1 );
2420
2421                // type further categorised here, because JS etc. needs it in non-lingual
2422                $res['type_accounting'] = $this->transactionAccountingType( $res['type'] );
2423
2424                $res['ref']    = $this->stripSlashes( $obj->zbst_ref );
2425                $res['origin'] = $this->stripSlashes( $obj->zbst_origin );
2426                $res['parent'] = (int) $obj->zbst_parent;
2427                $res['hash']   = $this->stripSlashes( $obj->zbst_hash );
2428                $res['title']  = $obj->zbst_title === null ? '' : $this->stripSlashes( $obj->zbst_title );
2429                $res['desc']   = $this->stripSlashes( $obj->zbst_desc );
2430                $res['date']   = (int) $obj->zbst_date;
2431                // well this naming convention makes this confusing... lol: //
2432                $res['date_date']           = ( isset( $obj->zbst_date ) && $obj->zbst_date > 0 ) ? zeroBSCRM_locale_utsToDatetime( $obj->zbst_date ) : false;
2433                $res['customer_ip']         = $this->stripSlashes( $obj->zbst_customer_ip );
2434                $res['currency']            = $this->stripSlashes( $obj->zbst_currency );
2435                $res['net']                 = $this->stripSlashes( $obj->zbst_net );
2436                $res['fee']                 = $this->stripSlashes( $obj->zbst_fee );
2437                $res['discount']            = $this->stripSlashes( $obj->zbst_discount );
2438                $res['shipping']            = $this->stripSlashes( $obj->zbst_shipping );
2439                $res['shipping_taxes']      = $this->stripSlashes( $obj->zbst_shipping_taxes );
2440                $res['shipping_tax']        = $this->stripSlashes( $obj->zbst_shipping_tax );
2441                $res['taxes']               = $this->stripSlashes( $obj->zbst_taxes );
2442                $res['tax']                 = $this->stripSlashes( $obj->zbst_tax );
2443                $res['total']               = $this->stripSlashes( $obj->zbst_total );
2444                $res['date_paid']           = $obj->zbst_date_paid === null ? null : (int) $obj->zbst_date_paid;
2445                $res['date_paid_date']      = ( isset( $obj->zbst_date_paid ) && $obj->zbst_date_paid > 0 ) ? zeroBSCRM_locale_utsToDatetime( $obj->zbst_date_paid ) : false;
2446                $res['date_completed']      = $obj->zbst_date_completed === null ? null : (int) $obj->zbst_date_completed;
2447                $res['date_completed_date'] = ( isset( $obj->zbst_date_completed ) && $obj->zbst_date_completed > 0 ) ? zeroBSCRM_locale_utsToDatetime( $obj->zbst_date_completed ) : false;
2448                $res['created']             = (int) $obj->zbst_created;
2449                $res['created_date']        = ( isset( $obj->zbst_created ) && $obj->zbst_created > 0 ) ? zeroBSCRM_date_i18n( -1, $obj->zbst_created, false, true ) : false;
2450                $res['lastupdated']         = (int) $obj->zbst_lastupdated;
2451                $res['lastupdated_date']    = ( isset( $obj->zbst_lastupdated ) && $obj->zbst_lastupdated > 0 ) ? zeroBSCRM_locale_utsToDatetime( $obj->zbst_lastupdated ) : false;
2452
2453                // custom fields - tidy any that are present:
2454                if ($withCustomFields) $res = $this->tidyAddCustomFields( ZBS_TYPE_TRANSACTION, $obj, $res, false );
2455
2456            }
2457
2458            return $res;
2459        }
2460
2461        /**
2462         * Tidies a row from a database result containing the columns external_source_uids and external_source_sources.
2463         * The result is a formatted HTML string.
2464         *
2465         * @param object $row (DB row containing the columns external_source_uids and external_source_sources)
2466         *
2467         * @return string Formatted HTML string for the external sources.
2468         */
2469        private function tidy_external_sources( $row ) {
2470            if ( $row->external_source_uids == null && $row->external_source_sources == null ) {
2471                return '';
2472            }
2473            $external_source_uids    = explode( "\n", $row->external_source_uids );
2474            $external_source_sources = explode( "\n", $row->external_source_sources );
2475            $external_source_strings = array_map(
2476                function ( $source, $uid ) {
2477                    $source_title = zeroBS_getExternalSourceTitle( $source, $uid );
2478                    // Formats the default zeroBS_getExternalSourceTitle string to look better in the table.
2479                    $source_title_explode = explode( '<br />', $source_title, 2 );
2480                    if ( count( $source_title_explode ) === 2 ) {
2481                        // Removes the trailing ':' from the source. E.g. 'WooCommerce:' becomes 'WooCommerce'.
2482                        $source_title_source = rtrim( $source_title_explode[0], ':' );
2483                        return "{$source_title_explode[1]} ({$source_title_source})";
2484                    } else {
2485                        return $source_title;
2486                    }
2487                },
2488                $external_source_sources,
2489                $external_source_uids
2490            );
2491
2492            return implode( '<br />', $external_source_strings );
2493        }
2494
2495        /**
2496         * Takes a transaction status (e.g. Sale or Credit Note), and returns debit/credit :)
2497         * wrapper that should be used throughout for inferring accounting direction for a transaction
2498         *
2499         * @param int objtype
2500         * @param int objid
2501         * @param string key
2502         *
2503         * @return array transaction meta result
2504         */
2505        public function transactionAccountingType( $transaction_type = 'Sale' ) {
2506
2507            if ( ! empty( $transaction_type ) ) {
2508
2509                if ( in_array( $transaction_type, array( __( 'Refund', 'zero-bs-crm' ), __( 'Credit Note', 'zero-bs-crm' ) ) ) ) {
2510                    return 'credit';
2511                }
2512            }
2513
2514            return 'debit';
2515        }
2516
2517        /**
2518         * Wrapper, use $this->getTransactionMeta($contactID,$key) for easy retrieval of singular transaction
2519         * Simplifies $this->getMeta
2520         *
2521         * @param int objtype
2522         * @param int objid
2523         * @param string key
2524         *
2525         * @return array transaction meta result
2526         */
2527        public function getTransactionMeta( $id = -1, $key = '', $default = false ) {
2528
2529            global $zbs;
2530
2531            if ( ! empty( $key ) ) {
2532
2533                return $this->DAL()->getMeta(
2534                    array(
2535
2536                        'objtype'     => ZBS_TYPE_TRANSACTION,
2537                        'objid'       => $id,
2538                        'key'         => $key,
2539                        'fullDetails' => false,
2540                        'default'     => $default,
2541                        'ignoreowner' => true, // for now !!
2542
2543                    )
2544                );
2545
2546            }
2547
2548            return $default;
2549        }
2550
2551        /**
2552         * Returns a Transaction's tag array
2553         *
2554         * @param int id Transaction ID
2555         *
2556         * @return mixed
2557         */
2558        public function getTransactionTags( $id = -1 ) {
2559
2560            global $zbs;
2561
2562            $id = (int) $id;
2563
2564            if ( $id > 0 ) {
2565
2566                return $this->DAL()->getTagsForObjID(
2567                    array(
2568                        'objtypeid' => ZBS_TYPE_TRANSACTION,
2569                        'objid'     => $id,
2570                    )
2571                );
2572
2573            }
2574
2575            return false;
2576        }
2577
2578        /**
2579         * Returns a reference against a transaction
2580         *
2581         * @param int id transaction ID
2582         *
2583         * @return string transaction ref
2584         */
2585        public function get_transaction_ref( $transaction_id = -1 ) {
2586
2587            global $zbs;
2588
2589            return $this->DAL()->getFieldByID(
2590                array(
2591                    'id'          => $transaction_id,
2592                    'objtype'     => ZBS_TYPE_TRANSACTION,
2593                    'colname'     => 'zbst_ref',
2594                    'ignoreowner' => true,
2595                )
2596            );
2597        }
2598
2599        /**
2600         * Returns an ownerid against a transaction
2601         *
2602         * @param int id transaction ID
2603         *
2604         * @return int transaction owner id
2605         */
2606        public function getTransactionOwner( $id = -1 ) {
2607
2608            global $zbs;
2609
2610            $id = (int) $id;
2611
2612            if ( $id > 0 ) {
2613
2614                return $this->DAL()->getFieldByID(
2615                    array(
2616                        'id'          => $id,
2617                        'objtype'     => ZBS_TYPE_TRANSACTION,
2618                        'colname'     => 'zbs_owner',
2619                        'ignoreowner' => true,
2620                    )
2621                );
2622
2623            }
2624
2625            return false;
2626        }
2627
2628        /**
2629         * Returns an array of contacts associtaed with a transaction
2630         *
2631         * @param int id transaction ID
2632         *
2633         * @return array contacts assocatied with transaction
2634         */
2635        public function get_transaction_contacts( $transaction_id ) {
2636
2637            return $this->DAL()->contacts->getContacts(
2638                array(
2639                    'hasObjTypeLinkedTo' => ZBS_TYPE_TRANSACTION,
2640                    'hasObjIDLinkedTo'   => $transaction_id,
2641                )
2642            );
2643        }
2644
2645        /**
2646         * Returns an invoice associtaed with a transaction
2647         *
2648         * @param int id transaction ID
2649         *
2650         * @return array $invoice
2651         */
2652        public function get_transaction_invoice_id( $transaction_id ) {
2653
2654            return $this->DAL()->getFirstIDLinkedToObj(
2655                array(
2656
2657                    'objtypefrom' => ZBS_TYPE_TRANSACTION,
2658                    'objtypeto'   => ZBS_TYPE_INVOICE,
2659                    'objfromid'   => $transaction_id,
2660
2661                )
2662            );
2663        }
2664
2665        /**
2666         * Returns an status against a transaction
2667         *
2668         * @param int id transaction ID
2669         *
2670         * @return string transaction status string
2671         */
2672        public function getTransactionStatus( $id = -1 ) {
2673
2674            global $zbs;
2675
2676            $id = (int) $id;
2677
2678            if ( $id > 0 ) {
2679
2680                return $this->DAL()->getFieldByID(
2681                    array(
2682                        'id'          => $id,
2683                        'objtype'     => ZBS_TYPE_TRANSACTION,
2684                        'colname'     => 'zbst_status',
2685                        'ignoreowner' => true,
2686                    )
2687                );
2688
2689            }
2690
2691            return false;
2692        }
2693
2694        /**
2695         * returns the transaction statuses to include in "total value" as per the setting on
2696         * admin.php?page=zerobscrm-plugin-settings&tab=transactions
2697         *
2698         * @return array
2699         */
2700        function getTransactionStatusesToInclude() {
2701
2702            // load (accept from cache)
2703            $setting = $this->DAL()->setting( 'transinclude_status', 'all', true );
2704
2705            if ( is_string( $setting ) && strpos( $setting, ',' ) > 0 ) {
2706
2707                return explode( ',', $setting );
2708            } elseif ( is_array( $setting ) ) {
2709
2710                return $setting;
2711
2712            }
2713
2714            return 'all';
2715        }
2716
2717        /**
2718         * returns an SQL query addition which will allow filtering of transactions
2719         * that should be included in "total value" fields
2720         * admin.php?page=zerobscrm-plugin-settings&tab=transactions
2721         *
2722         * @param string $table_alias_sql - if using a table alias pass that here, e.g. `transactions`.
2723         * @return array
2724         */
2725        function getTransactionStatusesToIncludeQuery( $table_alias_sql = '' ) {
2726
2727            // first we get the setting
2728            $transaction_statuses = $this->getTransactionStatusesToInclude();
2729
2730            // next we build the SQL
2731            // note that (in a legacy way) getTransactionStatusesToInclude() returns a string 'all'
2732            // .. if all transactions are selected
2733            // .. in that case there's no SQL to return as all statuses count.
2734            $query_addition = '';
2735            if ( is_array( $transaction_statuses ) && count( $transaction_statuses ) > 0 ) {
2736
2737                // create escaped csv
2738                $transaction_statuses_str = $this->build_csv( $transaction_statuses );
2739
2740                // build return sql
2741                $query_addition = ' AND ' . $table_alias_sql . 'zbst_status IN (' . $transaction_statuses_str . ')';
2742
2743            }
2744
2745            return $query_addition;
2746        }
2747
2748        /**
2749         * remove any non-db fields from the object
2750         * basically takes array like array('owner'=>1,'fname'=>'x','fullname'=>'x')
2751         * and returns array like array('owner'=>1,'fname'=>'x')
2752         * This does so based on the objectModel!
2753         *
2754         * @param array $obj (clean obj)
2755         *
2756         * @return array (db ready arr)
2757         */
2758        private function db_ready_transaction( $obj = false ) {
2759
2760            // use the generic? (override here if necessary)
2761            return $this->db_ready_obj( $obj );
2762        }
2763
2764        /**
2765         * Takes full object and makes a "list view" boiled down version
2766         * Used to generate listview objs
2767         *
2768         * @param array $obj (clean obj)
2769         *
2770         * @return array (listview ready obj)
2771         */
2772        public function listViewObj( $transaction = false, $columnsRequired = array() ) {
2773
2774            if ( is_array( $transaction ) && isset( $transaction['id'] ) ) {
2775
2776                $resArr = $transaction;
2777
2778                // a lot of this is legacy <DAL3 stuff just mapped. def could do with an improvement for efficacy's sake.
2779
2780                $resArr['total'] = zeroBSCRM_formatCurrency( $resArr['total'] );
2781                // $resArr['orderid'] = strlen($transaction['orderid']) > 7 ? substr($transaction['orderid'],0,7)."..." : $transaction['orderid'];
2782                // order id now = ref (use proper field)
2783                // $resArr['id'] = $transaction['id'];
2784                $resArr['status'] = ucfirst( $transaction['status'] );
2785
2786                // This wasn't working: $d = new DateTime($transaction['meta']['date']);
2787                // ... so I added the correct field (post_date) to getTransactions and piped in here
2788                // $d = new DateTime($transaction['date']);
2789                // $formatted_date = $d->format(zeroBSCRM_getDateFormat());
2790                // USE proper field $resArr['added'] = $formatted_date;
2791
2792                #} Convert $contact arr into list-view-digestable 'customer'// & unset contact for leaner data transfer
2793                $resArr['customer'] = zeroBSCRM_getSimplyFormattedContact( $transaction['contact'], ( in_array( 'assignedobj', $columnsRequired ) ) );
2794
2795                #} Convert $contact arr into list-view-digestable 'customer'// & unset contact for leaner data transfer
2796                $resArr['company'] = zeroBSCRM_getSimplyFormattedCompany( $transaction['company'], ( in_array( 'assignedobj', $columnsRequired ) ) );
2797
2798                #} Tags
2799                // if (in_array('tagged', $columnsRequired)){
2800
2801                // $resArr['tags'] = $transaction['tags'];
2802
2803                // }
2804
2805                return $resArr;
2806
2807            }
2808
2809            return false;
2810        }
2811
2812        // ===========  /   TRANSACTION  =======================================================
2813        // ===============================================================================
2814} // / class