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

   1 <?php
   2 /**
   3  * Table Definition for recv
   4  */
   5 require_once 'DB/DataObject.php';
   6
   7 class Pman_Xtuple_DataObjects_Recvgrp extends DB_DataObject
   8 {
   9     ###START_AUTOCODE
  10     /* the code below is auto generated do not remove the above tag */
  11
  12     public $__table = 'recvgrp';                // table name
  13     public $recvgrp_id;                         // int4(4)  not_null default_nextval%28recv_recv_id_seq%29 primary_key
  14     public $recvgrp_number;               // text(-1)  not_null
  15     public $recvgrp_pohead_id;               // text(-1)  not_null
  16     public $recvgrp_date;               // text(-1)  not_null
  17     public $recvgrp_landed_cost;               // text(-1)  not_null
  18     public $recvgrp_landed_method;               // text(-1)  not_null
  19     public $recvgrp_void;               // text(-1)  not_null
  20     public $recvgrp_location_id;
  21     public $recvgrp_receipt_number;     // TEXT
  22
  23     /* the code above is auto generated do not remove the tag below */
  24     ###END_AUTOCODE
  25
  26
  27     function applyFilters($q, $au, $roo)
  28     {
  29         if (!empty($q['_landed_vohead'])) {
  30             // fill in the assigned values for
  31             $vo = (int)$q['_landed_vohead'];
  32
  33             $vohead = DB_DataObject::factory('vohead');
  34             $vohead->get($q['_landed_vohead']);
  35             $cost = $vohead->costofshipping();
  36             $assigned =  $vohead->assignedcost();
  37
  38
  39             if ( ($cost - $assigned) < 1.0) {
  40                 $this->whereAdd("
  41                  recvgrp_id IN (SELECT   recvgrpland_recvgrp_id FROM
  42                     recvgrpland  WHERE
  43                         recvgrpland_vohead_id = $vo
  44                         )
  45                 ");
  46
  47             }
  48
  49             if (empty($q['_search'])) {
  50
  51                 $this->whereAdd("
  52
  53                        recvgrp_date < ('{$vohead->vohead_gldistdate}'::date + INTERVAL '1 MONTH')::date
  54                        AND
  55                        recvgrp_date > ('{$vohead->vohead_gldistdate}'::date - INTERVAL '4 MONTH')::date
  56
  57                 ");
  58             }
  59             $this->whereAdd("
  60                     (SELECT count(recv_qty) FROM recv  WHERE recv_recvgrp_id = recvgrp_id AND recv_posted) > 0
  61                     AND
  62                     (recvgrp_void = 0 OR recvgrp_void IS NULL)
  63             ");
  64
  65             $this->autoJoinVendor();
  66
  67             $this->selectAdd("
  68
  69                 COALESCE((SELECT sum(currtobase(recvgrpland_curr_id, recvgrpland_cost, vohead_gldistdate))
  70                     FROM
  71                         recvgrpland
  72                     LEFT JOIN
  73                         vohead
  74                     ON
  75                         recvgrpland_vohead_id = vohead_id
  76                     WHERE
  77                         recvgrpland_vohead_id = $vo
  78                     AND
  79                         recvgrpland_recvgrp_id = recvgrp_id
  80                 ), 0) as assigned_landed,
  81
  82
  83                 (SELECT sum(recv_qty) FROM recv  WHERE recv_recvgrp_id = recvgrp_id AND recv_posted)  as recv_qty
  84
  85
  86             ");
  87
  88             $this->orderBy("
  89
  90                 COALESCE((SELECT sum(recvgrpland_cost) FROM   recvgrpland
  91                     WHERE
  92                         recvgrpland_vohead_id = $vo
  93                     AND
  94                         recvgrpland_recvgrp_id = recvgrp_id
  95                 ), 0) DESC,
  96
  97                 join_vendinfo.vend_name ASC
  98
  99             ");
 100
 101
 102
 103
 104
 105
 106         }
 107         $this->selectAdd("
 108             (SELECT curr_symbol from curr_symbol where curr_base LIMIT 1)  base_curr_symbol,
 109
 110             ROUND ((SELECT
 111                     sum(
 112                         CASE WHEN recvgrpland_glseries = 0 THEN 0 ELSE
 113                             currtobase(recvgrpland_curr_id,  recvgrpland_cost,   gltrans_date)
 114                         END
 115                     )
 116                 FROM
 117                     recvgrpland
 118                 LEFT JOIN
 119                     gltrans
 120                 ON
 121                     gltrans_sequence = recvgrpland_glseries
 122                     AND
 123                     gltrans_amount > 0
 124
 125                 WHERE
 126                      recvgrpland_recvgrp_id = recvgrp_id
 127
 128
 129             ),2) as total_landed_cost
 130         ");
 131
 132
 133
 134         if (!empty($q['_search'])) {
 135             $v = $this->escape($q['_search']);
 136             $this->whereAdd("
 137                       join_vendinfo.vend_name ilike '%{$v}%'
 138                       OR
 139                       recvgrp_number like '%{$v}%'
 140                       OR
 141
 142                        join_recvgrp_pohead_id_pohead_id.pohead_number ilike '%{$v}%'
 143
 144                     ");
 145
 146
 147
 148
 149         }
 150         $this->autoJoinPoheadcurr();
 151
 152     }
 153     function autoJoinPoheadcurr()
 154     {
 155
 156         $v = DB_DataObject::Factory('curr_symbol');
 157         $this->_join .= '
 158             LEFT JOIN curr_symbol join_pohead_curr_symbol ON
 159                 join_recvgrp_pohead_id_pohead_id.pohead_curr_id = join_pohead_curr_symbol.curr_id
 160
 161         ';
 162         $this->selectAs($v, 'pohead_curr_id_%s', 'join_pohead_curr_symbol');
 163
 164
 165     }
 166
 167     function autoJoinVendor()
 168     {
 169
 170         $v = DB_DataObject::Factory('vendinfo');
 171         $this->_join .= '
 172             LEFT JOIN vendinfo join_vendinfo ON join_recvgrp_pohead_id_pohead_id.pohead_vend_id = vend_id
 173
 174         ';
 175         $this->selectAs($v, '%s', 'join_vendinfo');
 176
 177
 178     }
 179
 180     function beforeInsert($q, $roo)
 181     {
 182         if (!isset($q['recv_qtys'])) {
 183             $roo->jerr("no qtys'");
 184         }
 185         // no more session stuff now...
 186         $roo->sessionState(0);
 187         $this->factory('cohead')->lockTables();
 188         $this->processReceipt($roo,$q);
 189
 190         $this->jerr("should not get here");
 191
 192
 193     }
 194     function beforeUpdate($old, $q ,$roo)
 195     {
 196         if (!empty($q['_void'])) {
 197             $this->factory('cohead')->lockTables();
 198             $this->void($roo);
 199         }
 200
 201         // only allow modification of landed stuff..
 202         foreach($this->table() as $k => $v) {
 203             if (!preg_match('/landed/', $k)) {
 204                 $this->$k = $old->$k;
 205             }
 206         }
 207
 208
 209
 210     }
 211
 212     function nextNumber($po) {
 213         $seq =1;
 214         while (true) {
 215             $rg = DB_DataObject::Factory('recvgrp');
 216             if (!$rg->get('recvgrp_number',  $po->pohead_number . '-' . $seq)) {
 217                 return  $po->pohead_number . '-' . $seq;
 218
 219             }
 220             $seq++;
 221         }
 222         // no return...
 223     }
 224
 225
 226     function createFromPO($roo, $po, $loc)
 227     {
 228
 229
 230         $items = $po->items();
 231         $dt = date('Y-m-d', strtotime($po->pohead_orderdate));
 232
 233
 234         $seq = 1;
 235
 236         $rg = DB_DataObject::Factory('recvgrp');
 237
 238         $rg->setFrom(array(
 239             'recvgrp_number' =>    $this->nextNumber($po),
 240             'recvgrp_pohead_id' =>  $po->pohead_id,
 241             'recvgrp_date' => $dt,
 242             'recvgrp_landed_cost' => 0,
 243             //'recvgrp_landed_method;               // text(-1)  not_null
 244             //$recvgrp_void;               // text(-1)  not_null
 245             'recvgrp_location_id' => $loc->pid()
 246         ));
 247         $rg->insert();
 248         $pid = $rg->pid();
 249
 250         $num = $rg->recvgrp_number;
 251         $rrg = clone($rg);
 252
 253         $rg->recvgrp_number = $num; // make sure it's not overwritten..
 254
 255         $rg->update($rrg);
 256
 257
 258         //$notes = $this->escape($q['recv_notes']);
 259
 260         foreach($items as $i) {
 261             $recv = DB_DataObject::factory('recv');
 262
 263             $recv->enterReceipt(
 264                     $roo,
 265                     $rg,
 266                     $i->poitem_id,
 267                     $i->poitem_qty_ordered,
 268                     'Auto created from Transfer',
 269                     $po->pohead_curr_id,
 270                     $dt,
 271                     $loc->pid()
 272             );
 273
 274
 275         }
 276         $rg = DB_DataObject::Factory('recvgrp');
 277         $rg->get($pid);
 278         return $rg;
 279
 280     }
 281
 282
 283     function pohead()
 284     {
 285         $d = DB_DataObjecT::factory('pohead');
 286         $d->get($this->recvgrp_pohead_id);
 287         return $d;
 288     }
 289     // update can not modify reciept...
 290
 291
 292     function processReceipt($roo, $q)
 293     {
 294
 295         $po = DB_DataObject::Factory('pohead');
 296         if (!$po->get($q['recvgrp_pohead_id'])) {
 297             $roo->jerr("invalid pohead");
 298         }
 299         $items = json_decode($q['recv_qtys']);
 300         $dt = date('Y-m-d', strtotime($q['recvgrp_date']));
 301
 302         $rg = DB_DataObject::Factory('recvgrp');
 303
 304         $rg->recvgrp_number =  $this->nextNumber($po);
 305         $rg->recvgrp_pohead_id =  $po->pohead_id;
 306         $rg->insert();
 307
 308         $num = $rg->recvgrp_number;
 309         $rrg = clone($rg);
 310
 311         $rg->setFrom($q);
 312
 313         $rg->recvgrp_number = $num; // make sure it's not overwritten..
 314
 315         $rg->update($rrg);
 316
 317         $notes = $this->escape($q['recv_notes']);
 318
 319         foreach($items as $i) {
 320             $recv = DB_DataObject::factory('recv');
 321
 322             $recv->enterReceipt(
 323                     $roo,
 324                     $rg,
 325                     $i->recv_orderitem_id,
 326                     $i->recv_qty,
 327                     $notes,
 328                     $po->pohead_curr_id,
 329                     $dt,
 330                     $q['recvgrp_location_id']
 331             );
 332
 333
 334         }
 335         $roo->addEvent('POSTED', $this);
 336
 337         $roo->jok("posted");
 338
 339
 340     }
 341     function items()
 342     {
 343         $d = DB_DataObject::Factory('recv');
 344         $d->recv_recvgrp_id = $this->pid();
 345         $d->joinAddPoitem();
 346         return $d->fetchAll();
 347
 348
 349     }
 350
 351     function void($roo, $ret = false)
 352     {
 353         if (!in_array('recvgrp_void', array_keys($this->table()))) {
 354             $roo->jerr("system has not been updated with recvgrp_void");
 355         }
 356
 357         if ($this->recvgrp_void) {
 358             $roo->jerr("Already void");
 359         }
 360
 361         //-- are there any transfers for this order??
 362         $it = DB_DataObject::Factory('invhist_transfer_item');
 363         $it->autoJoin();
 364         $it->selectAdd();
 365         $it->selectAdd('SUM(invhist_transfer_item_qty) as in_transit');
 366         $it->whereAdd("
 367                 join_invhist_transfer_item_invhist_transfer_id_invhist_transfer_id.invhist_transfer_recvgrp_id = {$this->pid()}
 368             AND
 369                 join_invhist_transfer_item_invhist_transfer_id_invhist_transfer_id.invhist_transfer_id IS NOT NULL
 370             AND
 371                invhist_transfer_item_invhist_transfer_id IS NOT NULL
 372
 373
 374         ");
 375         $it->find(true);
 376         if (!empty($it->in_transit)) {
 377             $roo->jerr("there is an open transfer relating to this order - please void it first");
 378         }
 379
 380         // check if landed cost assigned to it..
 381         $lc = DB_DataObject::Factory('recvgrpland');
 382         $lc->recvgrpland_recvgrp_id = $this->recvgrp_id;
 383
 384         $olc = clone($lc);
 385
 386         $landed_count = $lc->count();
 387
 388         $olc->whereAdd('recvgrpland_glseries > 0 OR recvgrpland_cost > 0.0');
 389         $posted_landed_count = $olc->count();
 390
 391         if ($posted_landed_count ) {
 392
 393             $head_ids = $olc->fetchAll('recvgrpland_vohead_id');
 394
 395             $vohead = DB_DataObject::factory('vohead');
 396             $vohead->whereAddIn('vohead_id', $head_ids, 'int');
 397             $vonums = $vohead->fetchAll('vohead_number');
 398
 399             $roo->jerr("reciept has landed costs assigned to it - void them first "  . $landed_count  . ' (Vouchers: ' . implode(', ', $vonums) .')');
 400         }
 401
 402         if ($landed_count) {
 403             $olc_ids = $lc->fetchAll('recvgrpland_id');
 404
 405             foreach($olc_ids as $olc_id) {
 406                 $lc = DB_DataObject::Factory('recvgrpland');
 407                 $lc->get($olc_id);
 408                 $lc->delete();
 409             }
 410
 411         }
 412
 413
 414         $poid = $this->recvgrp_pohead_id;
 415
 416         $rj = DB_DataObject::Factory('rjctcode');
 417         if (!$rj->get('rjctcode_code', 'WRONG')) {
 418             $roo->jerr('can not find reject code');
 419         }
 420
 421         //DB_DataObject::debugLevel(1);
 422
 423         //SELECT enterPoReturn(599, 30, 18) AS result;
 424         //poitem_id  qty, rejectcode
 425         $loc = $this->recvgrp_location_id;
 426
 427         $items = $this->items();
 428         if (empty($items)) {
 429             $roo->jerr("No items where delivered");
 430         }
 431
 432         $this->checkLocationStock($roo);
 433
 434         foreach($items as $i) {
 435
 436             if (!$loc) {
 437                 $ivd = $i->invdetail($roo);
 438                 $loc =  $ivd->invdetail_location_id;
 439             }
 440
 441
 442             $q = DB_DataObject::Factory($this->tableName());
 443             $q->query("SELECT enterPoReturn({$i->recv_orderitem_id}, {$i->recv_qty}, {$rj->pid()}, '{$this->recvgrp_date}'::date ) as result");
 444             $q->fetch();
 445             if (empty($q->result) || $q->result < 1) {
 446                 $roo->jerr("enterPoReturn failed - returned " . $q->result);
 447             }
 448         }
 449         $q = DB_DataObject::Factory($this->tableName());
 450         $q->query("SELECT postPoReturns({$poid},false) AS result");
 451         $q->fetch();
 452         if (empty($q->result) || $q->result < 1) {
 453             $roo->jerr("postPoReturns failed - returned " . $q->result);
 454         }
 455         $ild = $q->result;
 456
 457         $ld = DB_DataObject::Factory('itemlocdist');
 458         $ld->itemlocdist_series = $ild;
 459         $rev = $ld->fetchAll();
 460
 461
 462
 463         foreach($rev as $r)
 464         {
 465             $ld = DB_DataObject::Factory('itemlocdist');
 466
 467             $ld->setFrom( array(
 468                     'itemlocdist_itemlocdist_id' => $r->itemlocdist_id,
 469                     'itemlocdist_source_type' => 'L',
 470                     'itemlocdist_source_id' => $loc , // location.
 471                     'itemlocdist_qty' =>   $r->itemlocdist_qty,
 472                     'itemlocdist_ls_id' => empty($r->itemlocdist_ls_id) ? $this->sqlValue('NULL') : $r->itemlocdist_ls_id,
 473                    // 'itemlocdist_series' =>  $ild,
 474             ));
 475             $ld->itemlocdist_expiration = $ld->sqlValue('endOfTime()');
 476             $ld->insert();
 477
 478
 479             $q = DB_DataObject::Factory($this->tableName());
 480             $q->query("SELECT distributeToLocations({$r->itemlocdist_id}) AS result");
 481             $q->fetch();
 482             if (empty($q->result) || $q->result < 1) {
 483                 $roo->jerr("distributeToLocations failed - returned " . $q->result);
 484             }
 485         }
 486         //$ld = DB_DataObject::Factory('itemlocdist');
 487         //$ld
 488
 489
 490
 491
 492
 493         $q = DB_DataObject::Factory($this->tableName());
 494         $q->query("SELECT postItemlocseries($ild) AS result");
 495         $q->fetch();
 496         if (empty($q->result) || $q->result < 1) {
 497             $roo->jerr("postItemlocseries failed - returned " . $q->result);
 498         }
 499
 500         $old = clone($this);
 501         $this->recvgrp_void = true;
 502
 503         $this->update($old);
 504
 505         $roo->addEvent('VOIDED', $this);
 506
 507         if($ret){
 508             return true;
 509         }
 510
 511         $roo->jok("voided");
 512
 513
 514     }
 515
 516
 517     function checkLocationStock($roo)
 518     {
 519         if(empty($roo->bootLoader->Xtuple['prevent_negative'])){
 520             return;
 521         }
 522
 523         $items = $this->items();
 524
 525         $loc = $this->recvgrp_location_id;
 526
 527         $stock = array();
 528
 529         foreach ($items as $item){
 530             if(empty($item->recv_qty)){
 531                 continue;
 532             }
 533
 534             if (!$loc) {
 535                 $ivd = $item->invdetail($roo);
 536                 $loc =  $ivd->invdetail_location_id;
 537             }
 538
 539             $balance = $item->itemsite()->checkLocationStock($loc);
 540
 541             if(empty($balance) || $balance < $item->recv_qty){
 542                 $stock[] = $item->itemsite()->item()->item_number;
 543             }
 544         }
 545
 546         if(count($stock)){
 547             $roo->jerr("These items have negative stock " . implode(', ', $stock));
 548         }
 549     }
 550
 551
 552
 553
 554     // IS THIS NEEDED? - CAN WE VOID AND RE-CREATE...?
 555
 556     function voidUntransfered($roo)
 557     {
 558
 559
 560         $poid = $this->recvgrp_pohead_id;
 561
 562         $rj = DB_DataObject::Factory('rjctcode');
 563         if (!$rj->get('rjctcode_code', 'WRONG')) {
 564             $roo->jerr('can not find reject code');
 565         }
 566
 567
 568         //DB_DataObject::debugLevel(1);
 569
 570         //SELECT enterPoReturn(599, 30, 18) AS result;
 571         //poitem_id  qty, rejectcode
 572         $loc = $this->recvgrp_location_id;
 573
 574         $items = $this->items();
 575         if (empty($items)) {
 576             $roo->jerr("No items where delivered");
 577         }
 578
 579         foreach($items as $i) {
 580
 581             if (!$loc) {
 582                 $ivd = $i->invdetail($roo);
 583                 $loc =  $ivd->invdetail_location_id;
 584             }
 585
 586
 587             $q = DB_DataObject::Factory($this->tableName());
 588             $q->query("SELECT enterPoReturn({$i->recv_orderitem_id}, {$i->recv_qty}, {$rj->pid()}, '{$this->recvgrp_date}'::date ) as result");
 589             $q->fetch();
 590             if (empty($q->result) || $q->result < 1) {
 591                 $roo->jerr("enterPoReturn failed - returned " . $q->result);
 592             }
 593         }
 594         $q = DB_DataObject::Factory($this->tableName());
 595         $q->query("SELECT postPoReturns({$poid},false) AS result");
 596         $q->fetch();
 597         if (empty($q->result) || $q->result < 1) {
 598             $roo->jerr("postPoReturns failed - returned " . $q->result);
 599         }
 600         $ild = $q->result;
 601
 602         $ld = DB_DataObject::Factory('itemlocdist');
 603         $ld->itemlocdist_series = $ild;
 604         $rev = $ld->fetchAll();
 605
 606
 607
 608         foreach($rev as $r)
 609         {
 610             $ld = DB_DataObject::Factory('itemlocdist');
 611
 612             $ld->setFrom( array(
 613                     'itemlocdist_itemlocdist_id' => $r->itemlocdist_id,
 614                     'itemlocdist_source_type' => 'L',
 615                     'itemlocdist_source_id' => $loc , // location.
 616                     'itemlocdist_qty' =>   $r->itemlocdist_qty,
 617                     'itemlocdist_ls_id' => empty($r->itemlocdist_ls_id) ? $this->sqlValue('NULL') : $r->itemlocdist_ls_id,
 618                    // 'itemlocdist_series' =>  $ild,
 619             ));
 620             $ld->itemlocdist_expiration = $ld->sqlValue('endOfTime()');
 621             $ld->insert();
 622
 623
 624             $q = DB_DataObject::Factory($this->tableName());
 625             $q->query("SELECT distributeToLocations({$r->itemlocdist_id}) AS result");
 626             $q->fetch();
 627             if (empty($q->result) || $q->result < 1) {
 628                 $roo->jerr("distributeToLocations failed - returned " . $q->result);
 629             }
 630         }
 631         //$ld = DB_DataObject::Factory('itemlocdist');
 632         //$ld
 633
 634
 635
 636
 637
 638         $q = DB_DataObject::Factory($this->tableName());
 639         $q->query("SELECT postItemlocseries($ild) AS result");
 640         $q->fetch();
 641         if (empty($q->result) || $q->result < 1) {
 642             $roo->jerr("postItemlocseries failed - returned " . $q->result);
 643         }
 644
 645         $old = clone($this);
 646         $this->recvgrp_void = true;
 647
 648         $this->update($old);
 649         $roo->addEvent('VOIDED', $this);
 650
 651         $roo->jok("voided");
 652
 653
 654     }
 655
 656
 657 }
 658
 659 /*
 660
 661  FIX recvgrps
 662
 663     UPDATE recv
 664         SET recv_recvgrp_id  =
 665             CASE WHEN (SELECT count(recvgrp_id) FROM recvgrp WHERE recvgrp_pohead_id = (SELECT poitem_pohead_id FROM poitem where poitem_id = recv_orderitem_id) AND recvgrp_date = recv_date ) = 1 THEN
 666                 (SELECT recvgrp_id  FROM recvgrp WHERE recvgrp_pohead_id = (SELECT poitem_pohead_id FROM poitem where poitem_id = recv_orderitem_id ) AND recvgrp_date = recv_date)
 667             ELSE
 668                 NULL
 669             END
 670     WHERE
 671         recv_recvgrp_id IS NULL;
 672
 673
 674     UPDATE recv
 675         SET recv_recvgrp_id  =
 676
 677
 678
 679             CASE WHEN
 680                 (SELECT
 681                    count(recvgrp_id)
 682                 FROM
 683                     recvgrp
 684                 WHERE
 685                     recvgrp_number = (SELECT tranid from   netsuite_itemreceipt WHERE id =   (SELECT itemreceipt_id FROM netsuite_itemreceiptitem WHERE  id::text = recv_oldid ))
 686                     AND
 687                     recvgrp_pohead_id = (SELECT poitem_pohead_id FROM poitem where poitem_id = recv_orderitem_id) AND recvgrp_date = recv_date
 688                )  = 1
 689             THEN
 690                 (SELECT
 691                     recvgrp_id
 692                 FROM
 693                     recvgrp
 694                 WHERE
 695                     recvgrp_number = (SELECT tranid from   netsuite_itemreceipt WHERE id =   (SELECT itemreceipt_id FROM netsuite_itemreceiptitem WHERE  id::text = recv_oldid ))
 696                     AND
 697                     recvgrp_pohead_id = (SELECT poitem_pohead_id FROM poitem where poitem_id = recv_orderitem_id) AND recvgrp_date = recv_date
 698                )
 699             ELSE
 700                 NULL
 701             END
 702     WHERE
 703         recv_recvgrp_id IS NULL;
 704
 705
 706
 707     select recv_id, recv_orderitem_id, (SELECT count(recvgrp_id) FROM recvgrp WHERE recvgrp_pohead_id = (SELECT poitem_pohead_id FROM poitem where poitem_id = recv_orderitem_id) AND recvgrp_date = recv_date ) from recv where recv_recvgrp_id  IS NULL;
 708
 709
 710     // the remaining ones are our fake vendor bills...
 711
 712
 713
 714
 715
 716  */