git.roojs.org Git - Pman.Xtuple/blob - DataObjects/Custinfo.php

   1 <?php
   2 /**
   3  * Table Definition for custinfo
   4  */
   5 require_once 'DB/DataObject.php';
   6
   7 class Pman_Xtuple_DataObjects_Custinfo extends DB_DataObject
   8 {
   9     ###START_AUTOCODE
  10     /* the code below is auto generated do not remove the above tag */
  11
  12     public $__table = 'custinfo';            // table name
  13     public $cust_id;                         // int4(4)  not_null default_nextval%28%28cust_cust_id_seq%29%3A%3Aregclass%29 primary_key
  14     public $cust_active;                     // bool(1)  not_null
  15     public $cust_custtype_id;                // int4(4)
  16     public $cust_salesrep_id;                // int4(4)
  17     public $cust_commprcnt;                  // numeric(-1)
  18     public $cust_name;                       // text(-1)
  19     public $cust_creditlmt;                  // int4(4)
  20     public $cust_creditrating;               // text(-1)
  21     public $cust_financecharge;              // bool(1)
  22     public $cust_backorder;                  // bool(1)  not_null
  23     public $cust_partialship;                // bool(1)  not_null
  24     public $cust_terms_id;                   // int4(4)
  25     public $cust_discntprcnt;                // numeric(-1)  not_null
  26     public $cust_balmethod;                  // bpchar(-1)  not_null
  27     public $cust_ffshipto;                   // bool(1)  not_null
  28     public $cust_shipform_id;                // int4(4)
  29     public $cust_shipvia;                    // text(-1)
  30     public $cust_blanketpos;                 // bool(1)  not_null
  31     public $cust_shipchrg_id;                // int4(4)  not_null
  32     public $cust_creditstatus;               // bpchar(-1)  not_null
  33     public $cust_comments;                   // text(-1)
  34     public $cust_ffbillto;                   // bool(1)  not_null
  35     public $cust_usespos;                    // bool(1)  not_null
  36     public $cust_number;                     // text(-1)  unique_key unique_key
  37     public $cust_dateadded;                  // date(4)  default_%28now%29%3A%3Adate
  38     public $cust_exported;                   // bool(1)  default_false
  39     public $cust_emaildelivery;              // bool(1)  default_false
  40     public $cust_ediemail;                   // text(-1)
  41     public $cust_edisubject;                 // text(-1)
  42     public $cust_edifilename;                // text(-1)
  43     public $cust_ediemailbody;               // text(-1)
  44     public $cust_autoupdatestatus;           // bool(1)  not_null
  45     public $cust_autoholdorders;             // bool(1)  not_null
  46     public $cust_edicc;                      // text(-1)
  47     public $cust_ediprofile_id;              // int4(4)
  48     public $cust_preferred_warehous_id;      // int4(4)  not_null default_%28-1%29
  49     public $cust_curr_id;                    // int4(4)  default_basecurrid%28%29
  50     public $cust_creditlmt_curr_id;          // int4(4)  default_basecurrid%28%29
  51     public $cust_cntct_id;                   // int4(4)
  52     public $cust_corrcntct_id;               // int4(4)
  53     public $cust_soemaildelivery;            // bool(1)  default_false
  54     public $cust_soediemail;                 // text(-1)
  55     public $cust_soedisubject;               // text(-1)
  56     public $cust_soedifilename;              // text(-1)
  57     public $cust_soediemailbody;             // text(-1)
  58     public $cust_soedicc;                    // text(-1)
  59     public $cust_soediprofile_id;            // int4(4)
  60     public $cust_gracedays;                  // int4(4)
  61     public $cust_ediemailhtml;               // bool(1)  not_null default_false
  62     public $cust_soediemailhtml;             // bool(1)  not_null default_false
  63     public $cust_taxzone_id;                 // int4(4)
  64     public $cust_passwd;
  65     public $cust_login_email;
  66
  67
  68    /**
  69     * Getter / Setter for $cust_creditlmt_curr_id
  70     *
  71     * @param    mixed   (optional) value to assign
  72     * @access   public
  73     */
  74     public function creditlmt_curr() {
  75         return func_num_args() ? $this->link('cust_creditlmt_curr_id', func_get_arg(0)) : $this->link('cust_creditlmt_curr_id');
  76     }
  77
  78    /**
  79     * Getter / Setter for $cust_curr_id
  80     *
  81     * @param    mixed   (optional) value to assign
  82     * @access   public
  83     */
  84     public function curr() {
  85         return func_num_args() ? $this->link('cust_curr_id', func_get_arg(0)) : $this->link('cust_curr_id');
  86     }
  87
  88    /**
  89     * Getter / Setter for $cust_cntct_id
  90     *
  91     * @param    mixed   (optional) value to assign
  92     * @access   public
  93     */
  94     public function cntct() {
  95         return func_num_args() ? $this->link('cust_cntct_id', func_get_arg(0)) : $this->link('cust_cntct_id');
  96     }
  97
  98    /**
  99     * Getter / Setter for $cust_corrcntct_id
 100     *
 101     * @param    mixed   (optional) value to assign
 102     * @access   public
 103     */
 104     public function corrcntct() {
 105         return func_num_args() ? $this->link('cust_corrcntct_id', func_get_arg(0)) : $this->link('cust_corrcntct_id');
 106     }
 107
 108    /**
 109     * Getter / Setter for $cust_custtype_id
 110     *
 111     * @param    mixed   (optional) value to assign
 112     * @access   public
 113     */
 114     public function custtype() {
 115         return func_num_args() ? $this->link('cust_custtype_id', func_get_arg(0)) : $this->link('cust_custtype_id');
 116     }
 117
 118    /**
 119     * Getter / Setter for $cust_salesrep_id
 120     *
 121     * @param    mixed   (optional) value to assign
 122     * @access   public
 123     */
 124     public function salesrep() {
 125         return func_num_args() ? $this->link('cust_salesrep_id', func_get_arg(0)) : $this->link('cust_salesrep_id');
 126     }
 127
 128    /**
 129     * Getter / Setter for $cust_shipform_id
 130     *
 131     * @param    mixed   (optional) value to assign
 132     * @access   public
 133     */
 134     public function shipform() {
 135         return func_num_args() ? $this->link('cust_shipform_id', func_get_arg(0)) : $this->link('cust_shipform_id');
 136     }
 137
 138    /**
 139     * Getter / Setter for $cust_taxzone_id
 140     *
 141     * @param    mixed   (optional) value to assign
 142     * @access   public
 143     */
 144     public function taxzone() {
 145         return func_num_args() ? $this->link('cust_taxzone_id', func_get_arg(0)) : $this->link('cust_taxzone_id');
 146     }
 147
 148    /**
 149     * Getter / Setter for $cust_terms_id
 150     *
 151     * @param    mixed   (optional) value to assign
 152     * @access   public
 153     */
 154     public function terms() {
 155         return func_num_args() ? $this->link('cust_terms_id', func_get_arg(0)) : $this->link('cust_terms_id');
 156     }
 157
 158
 159     /* the code above is auto generated do not remove the tag below */
 160     ###END_AUTOCODE
 161
 162     public function crmacct() {
 163         $c = DB_DataObject::factory('crmacct');
 164         $c->get('crmacct_cust_id',  $this->cust_id);
 165         return $c;
 166     }
 167
 168     function applyFilters($q, $au, $roo)
 169     {
 170         if(isset($q['_filterTop'])){
 171             $top = $this->filterTop();
 172             $roo->jok($top);
 173         }
 174
 175         if (isset($q['_with_lastyear_total'])) {
 176
 177             // this is the figure that displays next to the customer, the number of sales of selected brand if _with_lastyear total is not empty..
 178             // it should be based on the selected brand.
 179             $brandStr = '';
 180             $groupStr = '';
 181             if(!empty($q['_charass_brand_value'])){
 182                 $brand_ids = implode(',', DB_DataObject::factory('charass')->lookupIds('I', 'BRAND', $q['_charass_brand_value']));
 183                 $brandStr = "AND invcitem_item_id IN ({$brand_ids})";
 184
 185             }
 186             if(!empty($q['_charass_group_value'])){
 187                 $group_ids = implode(',', DB_DataObject::factory('charass')->lookupIds('I', 'PRODUCTGROUP', $q['_charass_group_value']));
 188                 $groupStr = "AND invcitem_item_id IN ({$group_ids})";
 189             }
 190             $this->selectAdd();
 191             $this->selectAdd("
 192                 cust_id,
 193                 cust_name,
 194                 (SELECT
 195                         ROUND(COALESCE(SUM(invcitem_billed),0))
 196                     FROM
 197                         invcitem
 198                     LEFT JOIN
 199                         invchead
 200                     ON
 201                         invcitem_invchead_id = invchead_id
 202                     LEFT JOIN
 203                         itemsite
 204                     ON
 205                         invcitem_item_id = itemsite_item_id
 206                     WHERE
 207                         invchead_cust_id = cust_id
 208                         AND
 209                         extract(year from invchead_invcdate) = extract(year from NOW() - INTERVAL '1 YEAR')
 210                         AND
 211                         itemsite_stocked = TRUE
 212                         $brandStr
 213                         $groupStr
 214                 ) as lastyear_total
 215             ");
 216
 217             $this->whereAddIn('cust_id', $this->filterTop(), 'int');
 218
 219             $this->orderBy("lastyear_total DESC");
 220         }
 221
 222         if (isseT($_REQUEST['_report'])) {
 223             $this->applyFiltersReport($roo, $_REQUEST['_report'], $_REQUEST['date_from'],$_REQUEST['date_to'] );
 224             return $this->jerr("oops");
 225         }
 226
 227         if (!empty($q['search']['_country'])) {
 228             $this->whereAdd("join_cust_addr_id.addr_country LIKE '{$this->escape($q['search']['_country'])}%'");
 229         }
 230
 231         if (!empty($q['query']['cust_name'])) {
 232             $v = $this->escape($q['query']['cust_name']);
 233             $this->whereAdd("cust_name ILIKE '%$v%' OR cust_number ILIKE '%$v%'");
 234         }
 235         if (!empty($q['search']['cust_name'])) {
 236             $v = $this->escape($q['search']['cust_name']);
 237             $this->whereAdd("cust_name ILIKE '%$v%' OR cust_number ILIKE '%$v%'");
 238         }
 239         if (!empty($q['query']['cust_name_begin'])) {
 240             $v = $this->escape($q['query']['cust_name_begin']);
 241             $this->whereAdd("cust_name ILIKE '$v%' OR cust_number ILIKE '$v%' ");
 242         }
 243
 244         if (!empty($q['search']['_mix'])) {
 245             $v = $this->escape($q['search']['_mix']);
 246             $this->whereAdd("
 247                     cust_name ILIKE '%$v%'
 248                 OR
 249                     cust_number ILIKE '%$v%'
 250                 OR
 251                     join_cust_cntct_id_cntct_id.cntct_name ILIKE '%$v%'
 252                 OR
 253                     join_cust_cntct_id_cntct_id.cntct_phone ILIKE '%$v%'
 254                 OR
 255                     join_cust_cntct_id_cntct_id.cntct_phone2 ILIKE '%$v%'
 256             ");
 257         }
 258
 259         if (!empty($q['search']['orders_since'])) {
 260             $dt = date('Y-m-d',  strtotime($q['search']['orders_since']));
 261             $this->whereAdd("cust_id IN (SELECT cohead_cust_id FROM cohead where  cohead_orderdate > ' $dt')");
 262         }
 263         if (!empty($q['search']['no_orders_since'])) {
 264             $dt = date('Y-m-d',  strtotime($q['search']['no_orders_since']));
 265             $this->whereAdd("cust_id NOT IN (SELECT cohead_cust_id FROM cohead where  cohead_orderdate > ' $dt')");
 266         }
 267
 268         if (!empty($q['search']['with_orders_since'])) {
 269             $this->selectAdd("(SELECT max(cohead_orderdate ) FROM cohead where  cohead_cust_id = cust_id) as last_order");
 270         }
 271         if (!empty($q['search']['with_address'])) {
 272             $this->selectAddAddresses();
 273         }
 274         if (!empty($q['cust_aropen_active'])) {
 275             $this->whereAdd('cust_id IN (SELECT distinct(aropen_cust_id) FROM aropen)');
 276
 277         }
 278
 279         if (!empty($q['_with_char'])) {
 280             $ch = DB_DAtaObject::Factory('char');
 281             $ch->char_customers = 1;
 282
 283             if (!$ch->count() < 2) {
 284                 $ch->initDatabase();
 285             }
 286             $ch->find();
 287             while ($ch->fetch()) {
 288                 $nm = 'cust_char_' . strtolower(preg_replace('/[^a-z]+/i','_', $ch->char_name));
 289
 290                 if (!empty($q[$nm])) {
 291                     $this->whereAdd("
 292                         charass_getvalue('C', cust_id, '{$ch->char_name}') = '{$this->escape($q[$nm])}'
 293                     ");
 294                 }
 295
 296
 297                 $this->selectAdd("
 298                     charass_getvalue('C', cust_id, '{$ch->char_name}') as $nm
 299                 ");
 300                 if (!empty($q['sort']) && $q['sort'] == $nm) {
 301                     $dir = (empty($q['dir']) || $q['dir'] == 'ASC') ? 'ASC' : 'DESC';
 302                     $this->orderBy("charass_getvalue('C', cust_id, '{$ch->char_name}') $dir");
 303                 }
 304             }
 305
 306         }
 307
 308         if(isset($q['_with_group_data'])){
 309             $this->autoJoinCoheadLastSale();
 310         }
 311
 312         if (!empty($q['sort']) && $q['sort'] == 'cntct_addr_country') {
 313             $dir = (empty($q['dir']) || $q['dir'] == 'ASC') ? 'ASC' : 'DESC';
 314             $this->orderBy(" cntct_addr_country $dir ");
 315         }
 316
 317         if (!empty($q['search']['with_balance'])) {
 318             // this exclude cash receipts which have not been applied.
 319             //DB_DataObject::DebugLevel(1);
 320             $this->selectAdd("
 321                 (SELECT
 322                     COALESCE(
 323                         SUM(
 324                            currtocurr(
 325                                 aropen_curr_id,
 326                                cust_curr_id ,
 327                                 CASE WHEN (aropen_doctype IN ('C', 'R')) THEN
 328                                         (aropen_amount - aropen_paid) * -1.0
 329                                     ELSE
 330                                         (aropen_amount - aropen_paid)
 331                                     END,
 332                                 aropen_distdate
 333                             )
 334                         )
 335                         ,0.0)
 336                     FROM aropen
 337                     WHERE
 338                         aropen_cust_id = cust_id
 339                         AND
 340                         aropen_open
 341                 ) as balance
 342             ");
 343
 344         }
 345
 346
 347         $l = DB_DataObject::factory('location')->defaultConfigLocation();
 348         if ($l->location_id) {
 349             $this->selectAdd("
 350                 {$l->pid()} AS default_location_id,
 351                 '{$this->escape($l->location_name)}' AS default_location_name
 352             ");
 353         } else {
 354             $this->selectAdd("
 355                0 AS default_location_id,
 356                 '' AS default_location_name
 357             ");
 358         }
 359         $this->selectAdd("
 360             (SELECT ipsass_ipshead_id FROM ipsass WHERE ipsass_cust_id = cust_id ORDER BY ipsass_id ASC LIMIT 1) as ipshead_id,
 361             (SELECT ipshead_name FROM ipshead WHERE ipshead_id =
 362                 (SELECT ipsass_ipshead_id FROM ipsass WHERE ipsass_cust_id = cust_id ORDER BY ipsass_id ASC LIMIT 1)
 363             ) as ipshead_id_name
 364         ");
 365
 366
 367     }
 368
 369     function autoJoinCoheadLastSale()
 370     {
 371         $this->_join .= "
 372             LEFT JOIN
 373                 cohead AS join_cust_cohead_id
 374             ON
 375                 join_cust_cohead_id.cohead_cust_id = cust_id
 376                 AND
 377                 join_cust_cohead_id.cohead_id = (SELECT cohead_id FROM cohead WHERE cohead_cust_id = cust_id ORDER BY cohead_orderdate DESC LIMIT 1)
 378             ";
 379
 380         $add = DB_DataObject::Factory('cohead');
 381         $this->selectAs($add, 'cust_%s', 'join_cust_cohead_id');
 382     }
 383
 384     function selectAddAddresses()
 385     {
 386         // not sure where this is used. -- looks like only customertab..
 387         //DB_DAtaObject::DebugLevel(1);
 388
 389         $this->_join .= "
 390             LEFT JOIN
 391                 addr   join_cust_addr_id
 392
 393             ON
 394                 join_cust_addr_id.addr_id = join_cust_cntct_id_cntct_id.cntct_addr_id";
 395
 396         $add = DB_DataObject::Factory('addr');
 397         $this->selectAs($add, 'cntct_%s', 'join_cust_addr_id');
 398
 399         return;
 400
 401     }
 402
 403     function onUpdate($old,$req,$roo)
 404     {
 405         // update includes information on ship addresses.
 406
 407         // we need to sync this up with shiptoinfo..
 408
 409         if (isset($req['shiplist'])) {
 410             $ar = explode(',', $req['shiplist']);
 411             $this->updateShipList($ar);
 412
 413         }
 414         if (isset($req['ipshead_id'])) {
 415             $this->updatePriceList($req['ipshead_id']);
 416         }
 417
 418         $this->updateCharass($req);
 419
 420         if(($this->cust_login_email != $old->cust_login_email) || $this->cust_cntct_id != $old->cust_cntct_id){
 421             $this->updateCntct($roo);
 422         }
 423     }
 424
 425     function char($str) {
 426
 427         $ca = DB_DAtaObject::factory('charass');
 428         $ca->query("SELECT
 429                 charass_getvalue(
 430                    'C',
 431                    {$this->pid()},
 432                    '".$this->escape($str). "'
 433                 ) as result");
 434         $ca->fetch();
 435         return empty($ca->result) ? '' : $ca->result;
 436
 437     }
 438
 439     function defaults()
 440     {
 441         $curr = DB_DataObject::factory('curr_symbol')->base();
 442         $ret = array(
 443             'cust_custtype_id'  =>  $this->sqlValue("fetchmetricvalue('DefaultCustType'::text)"),
 444             'cust_salesrep_id'  =>  $this->sqlValue("fetchmetricvalue('DefaultSalesRep'::text)"),
 445             'cust_commprcnt' => 0,
 446             'cust_creditrating'  =>  $this->sqlValue("fetchmetrictext('SOCreditRate'::text)"),
 447             'cust_active' => true,
 448
 449             'cust_backorder' => true,  // must accept partial and backorder..
 450             'cust_partialship' => true,
 451             'cust_discntprcnt' => 0,
 452             'cust_balmethod'  =>'B',
 453
 454             'cust_ffshipto' => true, //
 455             'cust_shipform_id' => $this->sqlValue("fetchmetricvalue('DefaultShipFormId'::text)"),
 456
 457             'cust_shipvia' => $this->sqlValue("fetchdefaultshipvia()"),
 458             'cust_blanketpos' => false,
 459
 460             'cust_shipchrg_id' => -1,
 461             'cust_creditstatus' => 'G',
 462             'cust_comments' => '',
 463             'cust_ffbillto' => true,
 464
 465             'cust_usespos' => false, // uses purchase orders'
 466
 467             'cust_autoupdatestatus' => false,
 468             'cust_autoholdorders' => false,
 469
 470             //'cust_preferred_warehous_id' => -1, // default warehouse...
 471
 472             //'cust_cntct_id' => -1, /// created later by
 473             //'cust_corrcntct_id' => -1,
 474             'cust_taxzone_id' => $this->sqlValue("gettaxzoneid('NO TAX')"),
 475             'cust_gracedays' => 0,
 476             'cust_terms_id' => $this->sqlValue("fetchmetricvalue('DefaultTerms'::text)"),
 477             'cust_curr_id' =>   $curr->pid(),//// currency
 478             'cust_creditlmt_curr_id' => $this->sqlValue("getcurrid('HKD'::text)"),/// currency again?
 479
 480             'cust_subscribed' => true,
 481
 482             'cust_passwd' => $this->generatePassword(), /// ???? should this be here??
 483
 484         );
 485         return $ret;
 486
 487
 488     }
 489
 490     function initDatabase($roo)
 491     {
 492         if ($this->count()) {
 493             return;
 494         }
 495         $companies = DB_DataObject::factory('companies');
 496         $enum = DB_DataObject::Factory('core_enum')->lookup('COMPTYPE',  'OWNER');
 497
 498         if (empty($enum)) {
 499             echo "SKIP - custinfo - create - missing core_enum = OWNER";
 500             return;
 501         }
 502
 503
 504         $companies = DB_DataObject::factory('core_company');
 505         $companies->comptype_id = $enum;
 506         if ($companies->count() != 1) {
 507             echo "SKIP - custinfo - create - missing owner company";
 508             return;
 509         }
 510         $companies->find(true);
 511
 512
 513
 514         $this->setFrom(array(
 515             'cust_name' => $companies->name,
 516             'cust_number' => empty($companies->code) ? strtoupper(preg_replace('/[^a-z0-9]+/i', '', $companies->name)) : $companies->code,
 517         ));
 518         $this->beforeInsert(array(), $roo);
 519         $this->insert();
 520
 521
 522     }
 523
 524     function beforeInsert($req,$roo)
 525     {
 526         $defs = $this->defaults() ;
 527         foreach($defs as $k=>$v) {
 528             if (!isset($req[$k])) {
 529                 $this->$k = $v;
 530             }
 531             if (in_array($k, array( 'cust_creditlmt_curr_id', 'cust_curr_id')) && empty($r[$k])) {
 532                 $this->$k = $v;
 533             }
 534
 535         }
 536         if ($this->cust_creditlmt_curr_id != $this->cust_curr_id) {
 537             $this->cust_creditlmt_curr_id = $this->cust_curr_id;
 538         }
 539         if (empty($this->cust_cntct_id)) {
 540             $this->cust_cntct_id = $this->sqlValue('NULL');
 541         }
 542         if($this->cust_number){
 543             if(!$this->custNumberDupeCheck()){
 544                 $roo->jerr("Customer code {$this->cust_number} already exists!");
 545             }
 546         }
 547         if($this->cust_login_email){
 548             if(!$this->loginEmailDupeCheck()){
 549                 $roo->jerr("Login Email {$this->cust_login_email} already exists!");
 550             }
 551         }
 552
 553     }
 554
 555     function beforeUpdate($old, $q, $roo)
 556     {
 557         if(empty($old->cust_passwd)){
 558             $this->generatePassword(); // fix the existing costomers..
 559         }
 560
 561         if ($old->cust_curr_id != $this->cust_curr_id) {
 562             $this->cust_creditlmt_curr_id = $this->cust_curr_id;
 563         }
 564         if (empty($this->cust_cntct_id)) {
 565             $this->cust_cntct_id = $this->sqlValue('NULL');
 566         }
 567         if($old->cust_number != $this->cust_number) {
 568             if(!$this->custNumberDupeCheck()){
 569                 $roo->jerr("Customer code {$this->cust_number} already exists!");
 570             }
 571         }
 572         if($old->cust_login_email != $this->cust_login_email) {
 573             if(!$this->loginEmailDupeCheck()){
 574                 $roo->jerr("Login Email {$this->cust_login_email} already exists!");
 575             }
 576         }
 577     }
 578
 579     function onInsert($req,$roo)
 580     {
 581         // update includes information on ship addresses.
 582
 583         // we need to sync this up with shiptoinfo..
 584         $c = DB_DataObject::factory('custinfo');
 585         $c->cust_name = $this->cust_name;
 586         if($c->count() > 1){
 587             $roo->jerr('Customer name already exists!');
 588         }
 589         $c = DB_DataObject::factory('custinfo');
 590         $c->cust_number = $this->cust_number;
 591         if($c->count() > 1){
 592             $roo->jerr('Customer number already exists!');
 593         }
 594
 595
 596         if (isset($req['shiplist'])) {
 597             $ar = explode(',', $req['shiplist']);
 598             $this->updateShipList($ar);
 599
 600         }
 601         if (isset($req['ipshead_id'])) {
 602             $this->updatePriceList($req['ipshead_id']);
 603         }
 604
 605         $this->updateCharass($req);
 606
 607         $this->updateCntct($roo);
 608
 609     }
 610
 611     function  findInternal($int_name, $cur)
 612     {
 613
 614         //DB_DataObject::DebugLevel(1);
 615         $ve = $this->factory($this->tableName());
 616         $ve->cust_curr_id = $cur->pid();
 617         $ve->whereAdd(" charass_getvalue('C',
 618                         cust_id,
 619                         'INTERNALCOMPANY') = '{$this->escape($int_name)}'");
 620         // fixme INTERNALCOMPANY must  be added to contacts
 621         $matches = $ve->count();
 622         if (!$matches || $matches > 1) {
 623             return false;
 624         }
 625         $ve->find(true);
 626         return $ve;
 627     }
 628
 629
 630     /**
 631      * create or return the default contact..
 632      * if none exists.. try and find one..
 633      * @return Pman_Xtuple_DataObjects_Cntct the contact..
 634      */
 635     function defaultContact($order = false)
 636     {
 637         if ($this->cust_cntct_id) {
 638             return $this->cntct();
 639         }
 640         if (empty($order)) {
 641             $order = array('ba', 'ca');
 642         }
 643
 644
 645         $crm = $this->crmacct();
 646         $cnt = $this->cntct();
 647         $cnt->cntct_crmacct_id = $crm->crmacct_id;
 648
 649         // we could be smarter and look for 'default *'
 650
 651         foreach($order as $pr) {
 652             // prefer ba..
 653             $c = clone($cnt);
 654             $c->whereAdd("cntct_number' like '{$pr}-%'");
 655             if ($cnt->count()) {
 656                 $cnt->find(true);
 657                 $old = clone($this);
 658                 $this->cust_cntct_id = $cnt->cntct_id;
 659                 $this->update($old);
 660                 return $cnt;
 661             }
 662         }
 663
 664         $c = clone($cnt);
 665         // finally anybody else..
 666         if ($cnt->count()) {
 667             $cnt->find(true);
 668             $old = clone($this);
 669             $this->cust_cntct_id = $cnt->cntct_id;
 670             $this->update($old);
 671             return $cnt;
 672         }
 673         //
 674         $c = clone($cnt);
 675         //create a dummy address..... = since there are no contacts that has to be created as well.
 676         throw new Exception("no default contact");
 677         /*
 678
 679     public $cntct_addr_id;                   // int4(4)
 680     public $cntct_first_name;                // text(-1)
 681     public $cntct_last_name;                 // text(-1)
 682     public $cntct_honorific;                 // text(-1)
 683     public $cntct_initials;                  // text(-1)
 684     public $cntct_active;                    // bool(1)  default_true
 685     public $cntct_phone;                     // text(-1)
 686     public $cntct_phone2;                    // text(-1)
 687     public $cntct_fax;                       // text(-1)
 688     public $cntct_email;                     // text(-1)
 689     public $cntct_webaddr;                   // text(-1)
 690     public $cntct_notes;                     // text(-1)
 691     public $cntct_title;                     // text(-1)
 692     public $cntct_number;                    // text(-1)  not_null unique_key
 693     public $cntct_middle;                    // text(-1)
 694     public $cntct_suffix;                    // text(-1)
 695     public $cntct_owner_username;            // text(-1)
 696     public $cntct_name;
 697         */
 698
 699
 700
 701
 702
 703
 704     }
 705
 706
 707     function updateShipList($contacts)
 708     {
 709         // find all the current shipping contacts.
 710         //DB_DataObject::debugLevel(1);
 711         $sh = DB_DataObject::factory('shiptoinfo');
 712         $sh->shipto_cust_id= $this->cust_id;
 713         $sh->shipto_active = true;
 714         $exist = $sh->fetchAll();
 715
 716
 717
 718         // new contacts..
 719         $c = DB_DataObject::factory('cntct');
 720         $c->whereAddIn('cntct_id', $contacts,'int');
 721         $cnts = $c->fetchAll();
 722
 723
 724         // insert if not referenced...
 725         $used = array();
 726
 727         foreach($cnts as $c) {
 728
 729             $sh = $c->shipto();
 730
 731
 732             if (!$sh->shipto_id) {
 733                 // then a shipinfo does not exist..
 734                 $sh = DB_DataObject::factory('shiptoinfo');
 735                 $sh = $sh->createFromCustomerContact($this, $c);
 736                 $used[] = $sh->shipto_id;
 737                 continue;
 738             }
 739             // dumb postgres...
 740             //if ($sh->shipto_active && $sh->shipto_active != 'f') {
 741
 742
 743             //    $used[] = $sh->shipto_id;
 744             //    continue;
 745             //}
 746             $ss = clone($sh);
 747             $sh->shipto_active = true;
 748             $sh->shipto_name =  $c->cntct_name;
 749             $sh->update($ss);
 750             $used[] = $sh->shipto_id;
 751
 752             // skip... - updating..
 753         }
 754
 755         // look at exist, and see if we can delete unused items..
 756         foreach($exist as $sh) {
 757             if (in_array($sh->shipto_id, $used)) {
 758                 continue;
 759             }
 760             $ss = clone($sh);
 761             $sh->shipto_active = false;
 762             $sh->update($ss);
 763
 764         }
 765
 766
 767     }
 768     function priceList()
 769     {
 770
 771         $p = DB_DataObject::Factory('ipsass');
 772         $p->ipsass_cust_id = $this->pid();
 773         $old = $p->fetchAll();
 774         if (empty($old)) {
 775             return false;
 776         }
 777         $ipsass = $old[0];
 778         $ipshead = DB_DataObject::Factory('ipshead');
 779         $ipshead->get($old[0]->ipsass_ipshead_id);
 780         return $ipshead;
 781     }
 782
 783
 784     function updatePriceList($ipshead)
 785     {
 786
 787         if (empty($ipshead)) {
 788             return false;
 789         }
 790         // get the current ones..
 791         $p = DB_DataObject::Factory('ipsass');
 792         $p->ipsass_cust_id = $this->pid();
 793         $old = $p->fetchAll();
 794         foreach($old as $o) {
 795             if ($o->ipsass_ipshead_id != $ipshead) {
 796                 $o->delete();
 797                 continue;
 798             }
 799             return; // got it and no change.. // does not handle multiple..
 800         }
 801         $p = DB_DataObject::Factory('ipsass');
 802         $p->ipsass_cust_id = $this->pid();
 803         $p->ipsass_ipshead_id = $ipshead;
 804         $p->insert();
 805     }
 806
 807     function postListFilter($ar, $au, $q)
 808     {
 809         if(isset($q['_my_json'])){
 810             if(!count($ar)){
 811                 return;
 812             }
 813             require_once 'pear/System.php';
 814             $tmpdir  = System::mktemp("-d abitodocx");
 815             $path = $tmpdir . '/Customers-' . $q['search']['_country'] . '-' . date('Y-m-d') . '.json';
 816             header("Content-Disposition: attachment; filename=\"".basename($path)."\";" );
 817             echo json_encode($ar);
 818             exit;
 819         }
 820         if (isset($q['_with_lastyear_total'])) {
 821
 822             $old_ar = $ar;
 823
 824             $always_display = DB_DataObject::factory('charass')->lookupIds('C', 'SALESFORECAST', 'always display');
 825
 826             foreach ($old_ar as $k => $v){
 827                 if(in_array($v['cust_id'], $always_display)){ // put 'always display' cust into the top list
 828                     unset($ar[$k]);
 829                     array_unshift($ar, $old_ar[$k]);
 830                 }
 831             }
 832
 833             $all_total = $this->totalSales($q['_charass_brand_value'],$q['_charass_group_value']);
 834
 835             $allcompanies = array(
 836               'cust_id' => '-1',
 837               'cust_name' => 'All Companies',
 838               'lastyear_total' => $all_total,
 839               'view_type' => 'All'
 840             );
 841
 842             if(count($ar) == 0){
 843                 array_unshift($ar,$allcompanies);
 844                 return $ar;
 845             }
 846
 847             $top_total = 0;
 848             foreach ($ar as $a){
 849                 $top_total += $a['lastyear_total'];
 850             }
 851
 852             $line = array(
 853                 'cust_id' => '-2',
 854                 'cust_name' => '----------Others----------',
 855                 'lastyear_total' => $all_total - $top_total,
 856                 'view_type' => 'others'
 857             );
 858             array_push($ar, $line);
 859             array_unshift($ar, $allcompanies);
 860
 861             return $ar;
 862         }
 863
 864         if(isset($q['_with_group_data'])){
 865             $new_ar = array();
 866             foreach ($ar as $a){
 867                 $addBillAddress = array();
 868                 $addShipAddress = array();
 869
 870                 $cols = array('address1', 'address2', 'address3', 'city', 'state', 'country');
 871
 872                 foreach ($cols as $col){
 873                     if($a['cust_cohead_billto' . $col] != ''){
 874                         $addBillAddress[] = $a['cust_cohead_billto' . $col];
 875                     }
 876                     if($a['cust_cohead_shipto' . $col] != ''){
 877                         $addShipAddress[] = $a['cust_cohead_shipto' . $col];
 878                     }
 879                 }
 880                 $a['cust_bill_info'] = implode("\r\n", $addBillAddress);
 881                 $a['cust_ship_info'] = implode("\r\n", $addShipAddress);
 882                 $new_ar[] = $a;
 883             }
 884             return $new_ar;
 885         }
 886
 887         return $ar;
 888     }
 889
 890     function topCustomerIdsBySalesQty()
 891     {
 892         $c = DB_DataObject::factory('custinfo');
 893         $c->selectAdd();
 894         $c->selectAdd("
 895             cust_id,
 896             (SELECT
 897                     ROUND(COALESCE(SUM(invcitem_billed),0))
 898                 FROM
 899                     invcitem
 900                 LEFT JOIN
 901                     invchead
 902                 ON
 903                     invcitem_invchead_id = invchead_id
 904                 LEFT JOIN
 905                     itemsite
 906                 ON
 907                     invcitem_item_id = itemsite_item_id
 908
 909                 WHERE
 910                     invchead_cust_id = cust_id
 911                     AND
 912                     extract(year from invchead_invcdate) = extract(year from NOW() - INTERVAL '1 YEAR')
 913                     AND
 914                     itemsite_stocked = TRUE
 915
 916             ) as lastyear_total
 917         ");
 918         $c->orderBy('lastyear_total DESC');
 919
 920         $c->find();
 921         $total = 0;
 922         $total_sales = $this->totalSales();
 923         $ids = array();
 924         while($c->fetch()){
 925             if($total > ceil($total_sales * 0.8)){
 926                 break;
 927             }
 928             $total += $c->lastyear_total;
 929             $ids[] = $c->cust_id;
 930         }
 931         return $ids;
 932     }
 933
 934     function totalSales($brand = '', $group = '')
 935     {
 936         $ic = DB_DataObject::factory('invcitem');
 937         $ic->autoJoin();
 938         $ic->_join = "
 939             LEFT JOIN
 940                 invchead
 941             ON
 942                 invcitem_invchead_id = invchead_id
 943             LEFT JOIN
 944                 itemsite
 945             ON
 946                 invcitem_item_id = itemsite_item_id
 947         ";
 948         $ic->selectAdd();
 949         $ic->selectAdd("
 950             ROUND(COALESCE(SUM(invcitem_billed),0)) as total_sales
 951         ");
 952         $ic->whereAdd("
 953             extract(year from invchead_invcdate) = extract(year from NOW() - INTERVAL '1 YEAR')
 954             AND
 955             itemsite_stocked = TRUE
 956         ");
 957         if(!empty($brand)){
 958             $brand_ids = DB_DataObject::factory('charass')->lookupIds('I', 'BRAND', $brand);
 959             $ic->whereAddIn('invcitem_item_id', $brand_ids, 'int');
 960         }
 961         if(!empty($group)){
 962             $group_ids = DB_DataObject::factory('charass')->lookupIds('I', 'PRODUCTGROUP', $group);
 963             $ic->whereAddIn('invcitem_item_id', $group_ids, 'int');
 964         }
 965         $ic->find(true);
 966         return $ic->total_sales;
 967     }
 968
 969     function custNumberDupeCheck()
 970     {
 971         $custinfo = DB_DataObject::factory('custinfo');
 972         if($this->cust_id){
 973             $custinfo->cust_number = $this->cust_number;
 974         }
 975         $custinfo->whereAdd("
 976             cust_number = '{$this->escape($this->cust_number)}'
 977         ");
 978
 979         if($custinfo->count()){
 980             return false;
 981         }
 982
 983         return true;
 984     }
 985
 986     function loginEmailDupeCheck()
 987     {
 988         $custinfo = DB_DataObject::factory('custinfo');
 989         $custinfo->whereAdd("
 990             cust_login_email = '{$this->escape($this->cust_login_email)}'
 991         ");
 992         if($custinfo->count()){
 993             return false;
 994         }
 995
 996         return true;
 997     }
 998
 999
1000     function updateCharass($req)
1001     {
1002         if (isset($req['cust_char_internalcompany'])) {
1003             // update the char...
1004             $ca = DB_DAtaObject::factory('charass');
1005             $ca->query("SELECT
1006                     charass_setvalue(
1007                        'C',{$this->pid()},
1008                        'INTERNALCOMPANY', '{$this->escape($req['cust_char_internalcompany'])}'
1009                     );");
1010
1011
1012         }
1013         if (isset($req['cust_char_salesforecast'])) {
1014             // update the char...
1015             $ca = DB_DAtaObject::factory('charass');
1016             $ca->query("SELECT
1017                     charass_setvalue(
1018                        'C',{$this->pid()},
1019                        'SALESFORECAST', '{$this->escape($req['cust_char_salesforecast'])}'
1020                     );");
1021
1022
1023         }
1024         if (isset($req['cust_char_au_post_accno'])) {
1025             // update the char...
1026             $ca = DB_DAtaObject::factory('charass');
1027             $ca->query("SELECT
1028                     charass_setvalue(
1029                        'C',{$this->pid()},
1030                        'AU-POST-ACCNO', '{$this->escape($req['cust_char_au_post_accno'])}'
1031                     );");
1032
1033
1034         }
1035     }
1036
1037     function filterTop()
1038     {
1039         $top = $this->topCustomerIdsBySalesQty();
1040         $top = array_merge($top , DB_DataObject::factory('charass')->lookupIds('C', 'SALESFORECAST', 'always display'));
1041         $top = array_diff($top , DB_DataObject::factory('charass')->lookupIds('C', 'SALESFORECAST', 'hide'));
1042
1043         return $top;
1044
1045     }
1046
1047     function priceListCurrency($roo)
1048     {
1049         $ipsass = DB_DataObject::Factory('ipsass');
1050
1051         if (!$ipsass->get('ipsass_cust_id', $this->pid())){
1052             $roo->jerr("Customer {$this->cust_name} does not have a pricelist associated with it.");
1053         }
1054
1055         $ipshead = DB_DataObject::factory('ipshead');
1056
1057         if(!$ipshead->get($ipsass->ipsass_ipshead_id)){
1058             $roo->jerr("Cound not find the pricelist for {$this->cust_name}");
1059         }
1060
1061         $curr = DB_DataObject::factory('curr_symbol');
1062         if(!$curr->get($ipshead->ipshead_curr_id)){
1063             $roo->jerr("Cound not find currency id : {$ipshead->ipshead_curr_id}");
1064         }
1065
1066         return $curr;
1067     }
1068
1069     function checkPassword($val)
1070     {
1071
1072         if (substr($this->cust_passwd,0,1) == '$') {
1073             return crypt($val,$this->cust_passwd) == $this->cust_passwd ;
1074         }
1075         // old style md5 passwords...- cant be used with courier....
1076         return md5($val) == $this->cust_passwd;
1077     }
1078
1079     function setPassword($value)
1080     {
1081         $salt='';
1082         while(strlen($salt)<9) {
1083             $salt.=chr(rand(64,126));
1084         }
1085
1086         $this->cust_passwd = crypt($value, '$1$'. $salt. '$');
1087
1088     }
1089     function generatePassword() // genearte a password (add set 'rawPasswd' to it's value)
1090     {
1091         require_once 'Text/Password.php';
1092         $this->rawPasswd = strtr(ucfirst(Text_Password::create(5)).ucfirst(Text_Password::create(5)), array(
1093         "a"=>"4", "e"=>"3",  "i"=>"1",  "o"=>"0", "s"=>"5",  "t"=>"7"));
1094         $this->setPassword($this->rawPasswd);
1095         return $this->rawPasswd;
1096     }
1097
1098     function sendMail($roo)
1099     {
1100         if(empty($this->type)){
1101             return false;
1102         }
1103
1104         $md5 = md5(date('r', time()));
1105         $e = $roo->addEvent($this->type, $this, $md5);
1106
1107         $content = array(
1108             'template'      => $this->type,
1109             'person'        => $this,
1110             'md5'           => $md5,
1111             'event'         => $e,
1112             'serverName'    => $_SERVER['SERVER_NAME'],
1113             'baseURL'       => $roo->baseURL
1114         );
1115
1116         $sent = DB_DataObject::factory('core_email')->send($content);
1117
1118         if(!is_object($sent)){
1119             return true;
1120         }
1121
1122         return false;
1123
1124     }
1125
1126     function updateCntct($roo)
1127     {
1128         if(empty($this->cust_cntct_id)){
1129             return;
1130         }
1131
1132         $cntct = DB_DataObject::factory('cntct');
1133         $cntct->get($this->cust_cntct_id);
1134         $old = clone ($cntct);
1135
1136         $cntct->cntct_email = $this->cust_login_email;
1137         $cntct->update($old);
1138     }
1139
1140     function myOrderDetails($type, $offset, $limit)
1141     {
1142         $ret = array();
1143
1144         $cohead = DB_DataObject::factory('cohead');
1145         $cohead->_join .= "
1146             LEFT JOIN
1147                 cobmisc AS join_cobmisc
1148             ON
1149                 join_cobmisc.cobmisc_cohead_id = cohead_id AND cobmisc_posted = true
1150             LEFT JOIN
1151                 invchead AS join_invchead
1152             ON
1153                 join_invchead.invchead_id = join_cobmisc.cobmisc_invchead_id
1154             LEFT JOIN
1155                 aropen AS join_aropen
1156             ON
1157                 join_aropen.aropen_doctype = 'I' AND join_aropen.aropen_docnumber = join_invchead.invchead_invcnumber
1158             LEFT JOIN
1159                 shiphead AS join_shiphead
1160             ON
1161                 join_shiphead.shiphead_order_id = cohead_id AND join_shiphead.shiphead_shipdate IS NOT NULL AND join_shiphead.shiphead_shipped
1162         ";
1163         $cohead->selectAdd("
1164             calcsalesorderamt(cohead_id::integer) AS amount,
1165             CASE WHEN (join_cobmisc.cobmisc_id IS NULL OR (join_aropen.aropen_amount - join_aropen.aropen_paid) > 0) THEN
1166                 'Payment pending'
1167             WHEN join_shiphead.shiphead_id IS NOT NULL THEN
1168                 join_shiphead.shiphead_delivery_note
1169             ELSE
1170                 'Being Processed'
1171             END AS waybills
1172
1173         ");
1174         $cohead->whereAdd("
1175                 cohead_cust_id = {$this->pid()}
1176             AND
1177                 cohead_status != 'X'
1178         ");
1179
1180         $cohead->orderBy('cohead_number DESC');
1181
1182         if($type == 'History'){
1183             $cohead->whereAdd("
1184                     cohead_orderdate < NOW()::date - INTERVAL '7 DAY'
1185                 AND
1186                     join_cobmisc.cobmisc_id IS NOT NULL
1187                 AND
1188                     join_aropen.aropen_amount - join_aropen.aropen_paid = 0
1189                 AND
1190                     join_shiphead.shiphead_id IS NOT NULL
1191             ");
1192         }
1193
1194         $count = ceil($cohead->count() / $limit);
1195         $ret['count'] = (!$count) ? 1 : $count;
1196
1197
1198         $cohead->limit($offset, $limit);
1199
1200         $ret['details'] = $cohead->fetchAll();
1201
1202         return $ret;
1203     }
1204
1205     function myOrderSummary()
1206     {
1207         $cohead = DB_DataObject::factory('cohead');
1208         $cohead->whereAdd("
1209                 cohead_cust_id = {$this->pid()}
1210             AND
1211                 cohead_status != 'X'
1212         ");
1213
1214         $cohead->find();
1215
1216         $ret = array(
1217             'pending' => 0,
1218             'processing' => 0,
1219             'processed' => 0,
1220         );
1221
1222
1223         while ($cohead->fetch()){
1224             $c = clone ($cohead);
1225
1226             $cobmisc = DB_DataObject::factory('cobmisc');
1227             $cobmisc->cobmisc_cohead_id = $c->pid();
1228             $cobmisc->cobmisc_posted = 1;
1229
1230             if(!$cobmisc->find(true)){
1231                 $ret['pending'] = $ret['pending'] + 1;
1232                 continue;
1233             }
1234
1235             $invoice = $cobmisc->invchead();
1236             $aropen = $invoice->aropen();
1237
1238             $unpaid = ($aropen->aropen_amount - $aropen->aropen_paid) * 1;
1239
1240             if($unpaid > 0){
1241                 $ret['pending'] = $ret['pending'] + 1;
1242                 continue;
1243             }
1244
1245             $shiphead = DB_DataObject::factory('shiphead');
1246             $shiphead->whereAdd("
1247                     shiphead_order_id = {$cohead->pid()}
1248                 AND
1249                     shiphead_shipdate IS NOT NULL
1250                 AND
1251                     shiphead_shipped
1252             ");
1253
1254             if(!$shiphead->count()){
1255                 $ret['processing'] = $ret['processing'] + 1;
1256                 continue;
1257             }
1258
1259             $ret['processed'] = $ret['processed'] + 1;
1260         }
1261
1262         return $ret;
1263     }
1264
1265     function genNumber($prefix = 'CUST')
1266     {
1267         if(!empty($this->cust_name)){
1268             return;
1269         }
1270
1271         $l = $prefix;
1272
1273         $x = DB_DataObject::Factory('custinfo');
1274         $suf = 0;
1275
1276         while ($x->get('cust_name', $l . (!$suf ? '' : '-' . $suf))) {
1277             $x = DB_DataObject::Factory('custinfo');
1278             $suf++;
1279         }
1280
1281         $this->cust_name =  $l . (!$suf ? '' : '-' . $suf);
1282         $this->cust_number =  $l . (!$suf ? '' : '-' . $suf);
1283
1284
1285     }
1286
1287     function getEmailFrom()
1288     {
1289         return $this->cust_login_email;
1290
1291 //        if (empty($this->cust_name)) {
1292 //            return $this->cust_login_email;
1293 //        }
1294 //        return '"' . addslashes($this->cust_name) . '" <' . $this->cust_login_email . '>';
1295     }
1296
1297     function nameColumn()
1298     {
1299         return 'cust_name';
1300     }
1301
1302     function emailColumn()
1303     {
1304         return 'cust_login_email';
1305     }
1306 }
1307