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

   1 <?php
   2 /**
   3  * Table Definition for location
   4  */
   5 require_once 'DB/DataObject.php';
   6
   7 class Pman_Xtuple_DataObjects_Location extends DB_DataObject
   8 {
   9     ###START_AUTOCODE
  10     /* the code below is auto generated do not remove the above tag */
  11
  12     public $__table = 'location';            // table name
  13     public $location_id;                     // int4(4)  not_null default_nextval%28%28location_location_id_seq%29%3A%3Aregclass%29 primary_key primary_key
  14     public $location_warehous_id;            // int4(4)  not_null
  15     public $location_name;                   // text(-1)  not_null
  16     public $location_descrip;                // text(-1)
  17     public $location_restrict;               // bool(1)
  18     public $location_netable;                // bool(1)
  19     public $location_whsezone_id;            // int4(4)
  20     public $location_aisle;                  // text(-1)
  21     public $location_rack;                   // text(-1)
  22     public $location_bin;                    // text(-1)
  23     public $location_cust_id;                    // text(-1)
  24
  25     /* the code above is auto generated do not remove the tag below */
  26     ###END_AUTOCODE
  27
  28     function customer()
  29     {
  30         $c = DB_DataObject::Factory('custinfo');
  31         if (!$this->location_cust_id || !$c->get($this->location_cust_id)) {
  32             return false;
  33         }
  34         return $c;
  35
  36     }
  37
  38
  39     function applyFilters($q, $au)
  40     {
  41         //DB_DataObject::debugLevel(1);
  42         if (!empty($q['query']['location_name'])) {
  43
  44             $v = $this->escape($q['query']['location_name']);
  45             $this->whereAdd("location_name ilike '$v%'");
  46
  47         }
  48         if(isset($q['_with_internalcompany'])){
  49             $this->selectAdd("(SELECT charass_getvalue('C', location_cust_id  ,'INTERNALCOMPANY')) as cust_to_internalcompany");
  50         }
  51
  52         if(!empty($q['_viewType'])){
  53             $db = substr($this->database(),-2);
  54
  55             switch($q['_viewType']) {
  56                 case 'local':
  57                     $this->whereAdd("
  58                         (SELECT charass_getvalue('C', location_cust_id  ,'INTERNALCOMPANY'))  IN ('', '{$db}')
  59                         AND
  60                         location_restrict = false
  61                     ");
  62                     break;
  63
  64                 case 'remote':
  65                     $this->whereAdd("
  66                         (SELECT charass_getvalue('C', location_cust_id  ,'INTERNALCOMPANY'))  NOT IN ('', '{$db}')
  67                         AND
  68                         location_restrict = false
  69                     ");
  70                     break;
  71
  72                 case 'disabled':
  73                     $this->location_restrict = true;
  74                     break;
  75
  76
  77             }
  78
  79         }
  80
  81
  82
  83         if (isset($q['query']['for_cohead_id'])) {
  84             $cid = (int)$q['query']['for_cohead_id'];
  85             $this->whereAdd("
  86                 location_id IN (SELECT coitem_location_src FROM coitem where coitem_cohead_id = $cid )
  87                             ");
  88
  89         }
  90         if (!empty($q['_notinternalcompany'])) {
  91             $this->whereAdd(" LENGTH(charass_getvalue('C', location_cust_id,'INTERNALCOMPANY')) < 1 ");
  92         }
  93          if (!empty($q['_has_invdetail'])) {
  94             $this->whereAdd("
  95                 location_id IN (SELECT distinct(invdetail_location_id) FROM invdetail)
  96             ");
  97
  98         }
  99         if (!empty($q['_has_invdetail_item'])) {
 100             $item = DB_DataObject::Factory('item');
 101             $item->get('item_number', $q['_has_invdetail_item']);
 102             $itemsite = $item->itemsite();
 103                $this->whereAdd("
 104                 location_id IN (SELECT distinct(invdetail_location_id) FROM invdetailview where invhist_itemsite_id =  {$itemsite->pid()} )
 105             ");
 106         }
 107         if (isset($q['_stock_for_item_id'])) {
 108             $item = DB_DataObject::factory('item');
 109             $item->get($q['_stock_for_item_id']);
 110             $itemsite = $item->itemsite();
 111             if (!empty($itemsite->itemsite_id)) {
 112
 113                 $this->selectAdd(" invdetail_atdate(NOW()::date, location_id, {$itemsite->pid()}) as itemloc_realqty");
 114                 $this->whereAdd(" 0 != invdetail_atdate(NOW()::date, location_id, {$itemsite->pid()})  ");
 115             } else {
 116                 $this->selectAdd(" 0 as itemloc_realqty");
 117             }
 118
 119         }
 120
 121
 122
 123         if (isset($q['query']['item_itemsite_id'])) {
 124             if (!isset($q['query']['cohead_id'])) {
 125                 $roo->jerr("no order specified");
 126             }
 127             // we are searching for a product availability.
 128             //DB_DataObject::debugLevel(1);
 129             $itemsite_id = (int) $q['query']['item_itemsite_id'];
 130             $order_id = (int) $q['query']['cohead_id'];
 131             //$this->location_netable = 1;
 132
 133
 134             // why look in itemoc? - that get's created on the fly when
 135             // adding removing data.
 136             //$il = $this->factory('itemloc');
 137             //$il->itemloc_itemsite_id = $itemsite_id;
 138             //$locs = $il->fetchAll('itemloc_location_id');
 139             //$this->whereAddIn('location_id', $locs, 'int');
 140
 141
 142             // available..
 143
 144              //qtylocation(pLocationId, NULL, NULL, NULL, pItemsiteId, pOrderType, pOrderId, pItemlocdistId)
 145                  // itemlocdist.. - this might have to be worked out if
 146                 // items have already been distributed for this order line
 147             //
 148             $this->selectAdd("
 149                 round(qtylocation(location_id, NULL, NULL, NULL, {$itemsite_id}, 'SO', {$order_id}, 0),0) AS qty_avail
 150             ");
 151
 152             // grabbing qty at a location at a specific date..
 153             // invhist < contains all the in/out dates for inventory..
 154             // itemlocdist << contains the distribution into specific locations.
 155             /// .. joins on itemlocdist_invhist_id ...
 156
 157
 158             // select sum(itemlocdist_qty) from itemlocdist
 159             //        LEFT JOIN invhist ON itemlocdist_invhist_id = invhist
 160             //        where itemlocdist_itemsite_id = 2068 AND
 161             //        invhist_transdate < ... date;
 162
 163             // that will not account for future reservations...
 164             //  --which have already been deducted from itemlocdist....
 165
 166
 167         }
 168         if (!empty($q['_with_stock_and_value'])) {
 169             // work out the total qty of stock at the location at that time..
 170             $dt = date('Y-m-d', strtotime($q['_as_of']));
 171
 172
 173
 174             if ($q['_with_stock_and_value'] == 'dragon' || $q['_with_stock_and_value'] == 'both' ) {
 175
 176                 $this->selectAdd( "
 177                     invdetail_location_atdate('$dt'::date + INTERVAL '1 DAY', location_id, 1)
 178                     as  location_qty,
 179                     invdetail_location_atdate('$dt'::date + INTERVAL '1 DAY', location_id , -1)
 180                     as  location_qty_neg,
 181                     invcost_location_atdate('$dt'::date + INTERVAL '1 DAY', location_id)
 182                      as location_value
 183
 184
 185
 186
 187                     ");
 188             } else {
 189                 $this->selectAdd( "
 190                     0 as  location_qty,
 191                     0 location_qty_neg,
 192                     0 as location_value
 193                     ");
 194
 195             }
 196
 197             // need to fetch last invdetail_id for each itemsite_id
 198             // where date < current date..
 199             //DB_DataObject::debugLevel(1);
 200
 201            /* if ($q['_with_stock_and_value'] == 'netsuite' || $q['_with_stock_and_value'] == 'both' ) {
 202             //if ($q['_with_stock_and_value'] == 'netsuite') {
 203
 204                 // netsuite..
 205                 $this->selectAdd( "
 206                     (SELECT sum(sum_qty) FROM
 207                         (SELECT
 208                                 netsuite_stock.item_id as item_id, sum(qty) as sum_qty
 209                             FROM
 210                                 netsuite_stock
 211                             LEFT JOIN
 212                                 item
 213                             ON
 214                                 item.item_id = netsuite_stock.item_id
 215                             WHERE
 216                                 netsuite_stock.location_id = location.location_id
 217                             AND
 218                                 trans_date < ('$dt'::date + INTERVAL '1 DAY')::date
 219                             AND
 220                                 item.item_type = 'P'
 221                             GROUP BY
 222                                 netsuite_stock.item_id
 223                             HAVING
 224                                 sum(qty) > 0
 225                         ) stock_pos
 226                     ) as location_netsuite_stock,
 227
 228                     (SELECT sum(sum_qty) FROM
 229                         (SELECT
 230                                  netsuite_stock.item_id as item_id, sum(qty) as sum_qty
 231                             FROM
 232                                 netsuite_stock
 233                             LEFT JOIN
 234                                 item
 235                             ON
 236                                 item.item_id = netsuite_stock.item_id
 237                             WHERE
 238                                 netsuite_stock.location_id = location.location_id
 239                             AND
 240                                 trans_date < ('$dt'::date + INTERVAL '1 DAY')::date
 241                             AND
 242                                 item.item_type = 'P'
 243                             GROUP BY
 244                                 netsuite_stock.item_id
 245                             HAVING
 246                                 sum(qty) < 0
 247                         ) stock_pos
 248                     ) as location_netsuite_stock_neg
 249                 ");
 250             } else {
 251             */
 252                  $this->selectAdd( "
 253                     0 as location_netsuite_stock,
 254                     0 location_netsuite_stock_neg
 255                 ");
 256             //}
 257             /*
 258              *,
 259
 260                 invdetail_cost_location_atdate('$dt'::date + INTERVAL '1 DAY', location_id)
 261                 as location_value
 262                 */
 263             //$this->having( "$loc_query > 0 " );
 264
 265
 266
 267
 268
 269
 270         }
 271
 272         $this->joinAddCustomer();
 273
 274
 275     }
 276
 277
 278     function defaults()
 279     {
 280         return array(
 281             'location_warehous_id' => $this->sqlValue('(SELECT warehous_id FROM whsinfo ORDER by warehous_id LIMIT 1)'),
 282             'location_restrict' => false,
 283             'location_whsezone_id' => -1,
 284             'location_aisle' => '',
 285             'location_rack'=> '',
 286             'location_bin'=> '',
 287             'location_netable' => 1,
 288         );
 289     }
 290     function initDatabase($roo)
 291     {
 292
 293          if ($this->count()) {
 294             echo "location exists - SKIP\n";
 295             return;
 296         }
 297         $cust = DB_DataObject::Factory('custinfo');
 298         $cc = $cust->count() ;
 299         if ($cc != 1) {
 300             echo "location create - needs 1 custinfo - got $cc\n";
 301             return;
 302         }
 303         $cust->find(true);
 304         $this->setFrom($this->defaults());
 305         $this->setFrom(array(
 306             'location_name' => 'Warehouse',
 307             'location_descrip' => 'Warehouse',
 308             'location_cust_id' => $cust->pid()
 309         ));
 310         $this->insert();
 311
 312     }
 313
 314     function beforeInsert($q,$roo)
 315     {
 316         foreach($this->defaults() as $k=>$v) {
 317             if (!isset($q[$k])) {
 318                 $this->$k = $v;
 319             }
 320         }
 321
 322
 323     }
 324
 325
 326     function joinAddCustomer()
 327     {
 328
 329         $this->_join .= "
 330             LEFT JOIN custinfo AS join_location_cust_id_cust_id
 331                     ON (join_location_cust_id_cust_id.cust_id= location.location_cust_id)
 332         ";
 333         $t = DB_DataObject::Factory('custinfo');
 334         $this->selectAs($t, 'location_cust_id_%s', 'join_location_cust_id_cust_id');
 335
 336         $this->selectAdd(" charass_getvalue('C', location_cust_id,'INTERNALCOMPANY')
 337                          as location_cust_id_char_internalcompany ");
 338     }
 339
 340
 341     function postListFilter($data, $authUser, $q)
 342     {
 343         if (!isset($q['_with_stock_and_value'])) {
 344             return $data;
 345         }
 346         //if (!empty($q['_with_empty'])) {
 347             return $data;
 348         //}
 349
 350         $ret = array();
 351         foreach($data as $r) {
 352             if ($r['location_qty'] == 0.0 && $r['location_qty_neg'] == 0.0 ) {
 353                 continue;
 354             }
 355             $ret[] = $r;
 356         }
 357
 358         return $ret;
 359
 360
 361     }
 362     /**
 363      *
 364      * depricated -- should be configured in bootstarp..
 365      * probably not used now..
 366      */
 367     function defaultLocation()
 368     {
 369         // based on config...
 370
 371         return $this->defaultConfigLocation();
 372         /*
 373         $l = DB_DataObject::factory('location');
 374         $locamap = array(
 375             'hk' => 'OLL',
 376             'sg' => 'Kerry SG',
 377             'my' => 'Kerry MY'
 378         );
 379         $l->get('location_name', $locamap[substr($this->database(),-2)]);
 380         return $l;
 381         */
 382
 383     }
 384
 385     function defaultByItemsite()
 386     {
 387         // get the best location id
 388         $is = DB_DataObject::factory('itemsite');
 389         $is->selectAdd();
 390         $is->selectAdd("DISTINCT('itemsite_location_id'), count(itemsite_id) as count_id, itemsite_location_id");
 391         $is->groupBy('itemsite_location_id');
 392         $is->orderBy('count_id DESC');
 393         $is->limit(1);
 394         $is->find(true);
 395
 396         return $is;
 397     }
 398
 399     function defaultConfigLocation()
 400     {
 401         $ff = HTML_FlexyFramework::get();
 402
 403         if(!isset($ff->Xtuple['default_location']) || empty($ff->Xtuple['default_location'])){
 404             $ff->page->jerr('Xtuple default location has not been set');
 405         }
 406
 407         $l = DB_DataObject::factory('location');
 408         if(!$l->get('location_name', $ff->Xtuple['default_location'])){
 409
 410             $l = DB_DataObject::factory('location');
 411             if ($l->count()) {
 412
 413                 $ff->page->jerr('Can not found the default location : ' . $ff->Xtuple['default_location']);
 414             }
 415         }
 416
 417         return $l;
 418     }
 419
 420     function importFromArray($roo, $locations)
 421     {
 422         foreach ($locations as $location){
 423             $l = DB_DataObject::factory('location');
 424             if($l->get('location_name', $location['location_name'])){
 425                 continue;
 426             }
 427             $l->setFrom($l->defaults());
 428             $l->location_name = $location['location_name'];
 429             $l->insert();
 430         }
 431     }
 432
 433 }