Stock.php 143 KB
Newer Older
1 2
<?php
// Dobrado Content Management System
3
// Copyright (C) 2019 Malcolm Blaney
4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
//
// This program is free software: you can redistribute it and/or modify
// it under the terms of the GNU Affero General Public License as
// published by the Free Software Foundation, either version 3 of the
// License, or (at your option) any later version.
//
// This program is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU Affero General Public License for more details.
//
// You should have received a copy of the GNU Affero General Public License
// along with this program.  If not, see <http://www.gnu.org/licenses/>.

class Stock extends Base {

  public function Add($id) {

  }

  public function Callback() {
25
    if (!$this->user->canViewPage) {
26
      return ['error' => 'You don\'t have permission to view stock.'];
27 28
    }

29
    $us_action = isset($_POST['action']) ? $_POST['action'] : '';
30
    if ($us_action === 'list') return $this->AllData();
31 32
    if ($us_action === 'save') return $this->Save();
    if ($us_action === 'download') return $this->Download();
33 34 35 36 37 38 39 40 41 42
    if ($us_action === 'edit') return $this->EditProduct();
    if ($us_action === 'editMultiple') return $this->EditMultiple();
    if ($us_action === 'remove') return $this->RemoveProduct();
    if ($us_action === 'import') return $this->ImportData();
    if ($us_action === 'listAdjustments') return $this->ListAdjustments();
    if ($us_action === 'listAllAdjustments') return $this->ListAllAdjustments();
    if ($us_action === 'saveAdjustment') return $this->SaveAdjustment();
    if ($us_action === 'exportAdjustments') return $this->ExportAdjustments();
    if ($us_action === 'updateGroup') return $this->UpdateGroup();
    if ($us_action === 'changeGroup') return $this->ChangeGroup();
43 44 45
    if ($us_action === 'changeProfile') return $this->ChangeProfile();
    if ($us_action === 'addProfile') return $this->AddProfile();
    if ($us_action === 'removeProfile') return $this->RemoveProfile();
46
    return ['error' => 'Unknown action'];
47 48 49 50
  }

  public function CanAdd($page) {
    // Need admin privileges to add the stock module.
51
    if (!$this->user->canEditSite) return false;
52
    // Can only have one stock module on a page.
53
    return !$this->AlreadyOnPage('stock', $page);
54 55 56 57 58 59 60 61 62 63 64
  }

  public function CanEdit($id) {
    return false;
  }

  public function CanRemove($id) {
    return true;
  }

  public function Content($id) {
65
    $default_group = $this->user->group;
66 67
    $purchase_group = isset($_SESSION['purchase-group']) ?
      $_SESSION['purchase-group'] : '';
68
    $content = '<p class="stock-display-group">';
69 70 71
    // Display a group select if this user has created invite groups.
    $invite = new Invite($this->user, $this->owner);
    $created = $invite->Created();
72 73 74 75
    if (count($created) > 0) {
      if (in_array($purchase_group, $created)) {
        $this->user->group = $purchase_group;
      }
76 77 78 79 80
      $content .= 'Displaying stock for ' .
        '<select id="stock-group-select">' .
          '<option value="' . $default_group . '">' .
            $this->Substitute('group-name', '', '', $default_group) .
          '</option>';
81
      foreach ($created as $group) {
82
        if ($group === $this->user->group) {
83
          $content .= '<option selected="selected" value="' . $group . '">' .
84
            $this->Substitute('group-name') . '</option>';
85 86
        }
        else {
87 88
          $content .= '<option value="' . $group . '">' .
            $this->Substitute('group-name', '', '', $group) . '</option>';
89 90
        }
      }
91 92 93
      $content .= '</select>';
    }

94
    if ($this->Substitute('stock-order-update') !== '') {
95 96 97 98 99
      $current = 'order';
      $switch = 'purchase';
      if (isset($_GET['price']) && $_GET['price'] === 'purchase') {
        $current = 'purchase';
        $switch = 'order';
100
      }
101 102 103
      $content .= '<span class="stock-price-info">Viewing ' . $current .
        ' price. (<a href="' . $this->Url('price=' . $switch) . '">Switch to ' .
        $switch . ' price</a>)</span>';
104
      $_SESSION['stock-price'] = $current;
105
    }
106 107 108 109 110
    $content .= '</p>';

    $base = '';
    $extra_pricing = '';
    $extra_pricing_columns = '';
111
    if ($this->Substitute('stock-wholesale-percent') !== '') {
112
      $base = 'Cost ';
113 114 115
      $extra_pricing .= '<div class="form-spacing">' .
          '<label for="stock-wholesale-input">Wholesale Price:</label>' .
          '<input id="stock-wholesale-input" type="text" maxlength="12">' .
116
        '</div>';
117
      $extra_pricing_columns .=
118
        '<input type="checkbox" id="stock-column-wholesale">' .
119
        '<label for="stock-column-wholesale">Wholesale Price</label>';
120
    }
121
    if ($this->Substitute('stock-retail-percent') !== '') {
122
      $base = 'Cost ';
123 124 125
      $extra_pricing .= '<div class="form-spacing">' .
          '<label for="stock-retail-input">Retail Price:</label>' .
          '<input id="stock-retail-input" type="text" maxlength="12">' .
126
        '</div>';
127
      $extra_pricing_columns .=
128
        '<input type="checkbox" id="stock-column-retail">' .
129 130 131 132 133
        '<label for="stock-column-retail">Retail Price</label>';
    }
    $image_input = '';
    $image_column = '';
    if ($this->Substitute('stock-display-images') === 'true') {
134
      $image_column = '<input type="checkbox" id="stock-column-image">' .
135
        '<label for="stock-column-image">Image</label>';
136 137 138
      $image_input = '<div class="form-spacing">' .
          '<label for="stock-image-input">Image:</label>' .
          '<input id="stock-image-input" type="text" maxlength="200">' .
139
        '</div>';
140
    }
141 142 143 144 145 146

    $order_label = 'Available to Members';
    $order_column_label = 'Member Availability';
    // The checkboxes in the product form are hidden so that user can select
    // which options they want to update, without changing all of them.
    // (Note that the value of these options must match the checkbox id.)
147 148
    $checkbox_options = '<option value="">Select a field...</option>' .
      '<option value="order-available">' . $order_label . '</option>';
149
    $purchase_available = '';
150
    $purchase_available_column = '';
151
    if ($this->Substitute('stock-order-available') === 'true') {
152 153
      $order_label = 'Available to Order';
      $order_column_label = 'Order Availability';
154 155 156 157
      $purchase_available = '<div class="form-spacing hidden">' .
          '<label for="stock-purchase-available-input">' .
            'Available to Purchase:</label>' .
          '<input id="stock-purchase-available-input" type="checkbox">' .
158
        '</div>';
159
      $purchase_available_column =
160 161
        '<input type="checkbox" id="stock-column-purchaseAvailable">' .
        '<label for="stock-column-purchaseAvailable">Purchase Availability' .
162
        '</label>';
163 164
      $checkbox_options = '<option value="">Select a field...</option>' .
        '<option value="order-available">' . $order_label . '</option>' .
165 166 167 168 169 170 171 172 173
        '<option value="purchase-available">Available to Purchase</option>';
    }
    $checkbox_options .=
      '<option value="supplier-available">Available from Supplier</option>';

    $taxable = '';
    $taxable_column = '';
    $taxable_label = $this->Substitute('stock-taxable');
    if ($taxable_label !== '') {
174 175 176 177
      $taxable = '<div class="form-spacing hidden">' .
          '<label for="stock-taxable-input">' .
            $taxable_label . ':</label>' .
          '<input id="stock-taxable-input" type="checkbox">' .
178
        '</div>';
179
      $taxable_column = '<input type="checkbox" id="stock-column-taxable">' .
180 181
        '<label for="stock-column-taxable">Tax Status</label>';
      $checkbox_options .=
182
        '<option value="taxable">' . $taxable_label . '</option>';
183
    }
184
    $composite = '';
185
    $composite_column = '';
186
    if ($this->Substitute('stock-show-composite') === 'true') {
187 188 189
      $composite = '<div class="form-spacing hidden">' .
          '<label for="stock-composite-input">Composite Item:</label>' .
          '<input id="stock-composite-input" type="checkbox">' .
190
        '</div>';
191 192 193
      $composite_column = '<input type="checkbox" ' .
        'id="stock-column-composite"><label for="stock-column-composite">' .
        'Composite Status</label>';
194 195 196 197 198
      $checkbox_options .= '<option value="composite">Composite Item</option>';
    }
    $bulk = '';
    $bulk_column = '';
    if ($this->Substitute('stock-bulk') === 'true') {
199 200 201
      $bulk = '<div class="form-spacing hidden">' .
          '<label for="stock-bulk-input">Bulk Item:</label>' .
          '<input id="stock-bulk-input" type="checkbox">' .
202
        '</div>';
203
      $bulk_column = '<input type="checkbox" id="stock-column-bulk">' .
204 205 206 207 208 209
        '<label for="stock-column-bulk">Bulk Status</label>';
      $checkbox_options .= '<option value="bulk">Bulk Item</option>';
    }
    $cart = '';
    $cart_column = '';
    if ($this->Substitute('stock-cart') === 'true') {
210
      $cart = '<div class="form-spacing hidden">' .
211
          '<label for="stock-cart-input">Sync to Cart:</label>' .
212
          '<input id="stock-cart-input" type="checkbox">' .
213
        '</div>';
214
      $cart_column = '<input type="checkbox" id="stock-column-cart">' .
215 216
        '<label for="stock-column-cart">Sync to Cart</label>';
      $checkbox_options .= '<option value="cart">Sync to Cart</option>';
217
    }
218
    $checkbox_options .= '<option value="hidden">Hidden</option>';
219

220 221 222 223 224
    list($quantity, $quantity_columns, $quantity_dialog) =
      $this->TrackQuantity();
    $add_supplier = $this->GroupMember('admin', 'admin') ?
      '<button id="stock-add-supplier">Add Supplier</button>' : '';

225 226 227 228 229
    $content .= '<div id="stock-buttons">' .
        '<button id="stock-import-help" title="help">Help</button>' .
        '<button id="stock-open-product-form">Open product form</button>' .
        '<label for="stock-show-import">Import</label>' .
        '<input id="stock-show-import" type="checkbox">' .
230 231
        '<span id="stock-import-wrapper"><select id="stock-import-supplier">' .
          $this->SupplierSelect() . '</select>' .
232
          '<input type="text" id="stock-import-input">' .
233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254
          '<input id="stock-import-file" type="file"></span>' .
      '</div>' .
      '<input type="checkbox" id="stock-show-columns">' .
      '<label for="stock-show-columns">Edit columns</label>' .
      '<input type="checkbox" id="stock-wide-grid">' .
      '<label for="stock-wide-grid">Wide grid</label>' .
      '<button id="stock-download">Download</button> ' .
      '<select id="stock-profiles">' .
        '<option value="">Profiles</option>' .
        $this->CustomProfiles() .
        '<option value="available">Export Available</option>' .
        '<option value="all">Export All</option>' .
        '<option value="edit">Edit...</option>' .
      '</select> ' .
      '<div id="stock-column-display">' .
        '<button id="stock-column-left">left</button>' .
        '<div id="stock-column-wrapper"><div id="stock-columns">' .
          '<input type="checkbox" id="stock-column-supplier">' .
          '<label for="stock-column-supplier">Supplier</label>' .
          '<input type="checkbox" id="stock-column-unit">' .
          '<label for="stock-column-unit">Unit</label>' .
          '<input type="checkbox" id="stock-column-unitPrice">' .
255
          '<label for="stock-column-unitPrice">Pack Price</label>' .
256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283
          $quantity_columns .
          '<input type="checkbox" id="stock-column-size">' .
          '<label for="stock-column-size">Pack Size</label>' .
          '<input type="checkbox" id="stock-column-alternative">' .
          '<label for="stock-column-alternative">Alternative</label>' .
          '<input type="checkbox" id="stock-column-price">' .
          '<label for="stock-column-price">' . $base . 'Price</label>' .
          $extra_pricing_columns .
          '<input type="checkbox" id="stock-column-category">' .
          '<label for="stock-column-category">Category</label>' .
          '<input type="checkbox" id="stock-column-description">' .
          '<label for="stock-column-description">Description</label>' .
          $image_column .
          '<input type="checkbox" id="stock-column-grower">' .
          '<label for="stock-column-grower">Grower</label>' .
          '<input type="checkbox" id="stock-column-available">' .
          '<label for="stock-column-available">' . $order_column_label .
          '</label>' .
          $purchase_available_column .
          '<input type="checkbox" id="stock-column-supplierAvailable">' .
          '<label for="stock-column-supplierAvailable">' .
            'Supplier Availability</label>' .
          $taxable_column . $composite_column . $bulk_column . $cart_column .
          '<input type="checkbox" id="stock-column-hidden">' .
          '<label for="stock-column-hidden">Hidden Status</label>' .
        '</div></div>' .
        '<button id="stock-column-right">right</button>' .
      '</div><div id="stock-clear"></div>' .
284
      '<form id="stock-form" class="hidden">' .
285 286 287 288 289 290 291 292 293 294 295 296 297 298 299
        '<div id="stock-form-buttons">' .
          '<button id="stock-form-previous">Previous</button>' .
          '<button id="stock-form-clear">New Product</button>' .
          $add_supplier .
          '<button id="stock-form-next">Next</button>' .
        '</div>' .
        '<div class="form-spacing">' .
          '<label for="stock-name-input">Product:</label>' .
          '<input id="stock-name-input" type="text" maxlength="100">' .
        '</div>' .
        '<div class="form-spacing">' .
          '<label for="stock-user-input">Supplier:</label>' .
          '<input id="stock-user-input" type="text" maxlength="50">' .
        '</div>' .
        $quantity .
300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320
        '<div class="form-spacing">' .
          '<label for="stock-checkbox-select">Update:</label>' .
          '<select id="stock-checkbox-select">' . $checkbox_options .
          '</select>' .
        '</div>' .
        '<div class="form-spacing hidden">' .
          '<label for="stock-order-available-input">' .
            $order_label . ':</label>' .
          '<input id="stock-order-available-input" type="checkbox">' .
        '</div>' .
        $purchase_available .
        '<div class="form-spacing hidden">' .
          '<label for="stock-supplier-available-input">' .
            'Available from Supplier:</label>' .
          '<input id="stock-supplier-available-input" type="checkbox">' .
        '</div>' .
        $taxable . $composite . $bulk . $cart .
        '<div class="form-spacing hidden">' .
          '<label for="stock-hidden-input">Hidden:</label>' .
          '<input id="stock-hidden-input" type="checkbox">' .
        '</div>' .
321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364
        '<div class="form-spacing">' .
          '<label for="stock-unit-select">Unit:</label>' .
          '<select id="stock-unit-select">' .
            '<option value="none"></option>' .
            '<option value="each">each</option>' .
            '<option value="kg">kg</option>' .
            '<option value="g">grams</option>' .
            '<option value="L">litre</option>' .
            '<option value="variable">as marked</option>' .
            '<option value="adjusted">random weight</option>' .
          '</select>' .
        '</div>' .
        '<div class="form-spacing">' .
          '<label for="stock-size-input">Pack Size:</label>' .
          '<input id="stock-size-input" type="text" maxlength="12">' .
          '<label for="stock-alternative">alternative</label>' .
          '<input type="checkbox" id="stock-alternative">' .
        '</div>' .
        '<div class="form-spacing">' .
          '<label for="stock-price-input">' . $base . 'Price:</label>' .
          '<input id="stock-price-input" type="text" maxlength="12">' .
          '<span id="stock-order-price-info"></span>' .
        '</div>' .
        $extra_pricing .
        '<div class="form-spacing">' .
          '<label for="stock-category-input">Category:</label>' .
          '<input id="stock-category-input" type="text" maxlength="100">' .
        '</div>' .
        '<div class="form-spacing">' .
          '<label for="stock-description-textarea">Description:</label>' .
          '<textarea id="stock-description-textarea"></textarea>' .
        '</div>' .
        $image_input .
        '<div class="form-spacing">' .
          '<label for="stock-grower-input">Grower:</label>' .
          '<input id="stock-grower-input" type="text" maxlength="200">' .
        '</div>' .
        '<button class="submit">submit</button>' .
        '<button class="remove">remove</button>' .
      '</form>' .
      '<div class="stock-info">' .
        '<span class="stock-new-available"></span>' .
        '<button class="stock-list-all hidden">show all</button>' .
      '</div>' .
365
      '<div class="stock-import-info hidden">' .
366 367 368 369 370 371 372 373 374 375 376 377
        $this->Substitute('stock-import-info') .
      '</div>' .
      '<div class="stock-profile-dialog hidden">' .
        '<p>The currently selected columns will be saved as a profile, ' .
          'please provide a name so that it can be added to the menu:</p>' .
        '<div class="form-spacing">' .
          '<label for="stock-profile-input">Profile Name:</label>' .
          '<input id="stock-profile-input" type="text">' .
          '<button id="stock-profile-add">add</button>' .
        '</div>' .
        $this->CustomProfiles('remove') .
      '</div>' .
378
      $quantity_dialog;
379 380 381

    $this->user->group = $default_group;
    return $content;
382 383
  }

384
  public function Copy($id, $new_page, $old_owner, $old_id) {
385 386 387

  }

388
  public function Cron() {
389
    if (!$this->Run(date('H:00:00'))) return;
390

391
    $mysqli = connect_db();
392 393
    // Order availability is imported by the user and then used here to
    // overwrite purchase availabilty.
394
    $run_groups = $this->RunGroups('stock-order-update');
395
    for ($i = 0; $i < count($run_groups); $i++) {
396 397 398
      $group = $run_groups[$i]['group'];
      $timezone = $run_groups[$i]['timezone'];
      $this->user = new User($this->owner, $group, $timezone);
399 400
      $category_query = '';
      // Allow the update to be done for only a limited list of categories.
401
      // Note that templates are html encoded so decode them here.
402 403 404 405 406
      $us_categories = $this->Substitute('stock-update-categories');
      if ($us_categories !== '') {
        $us_category_list =
          explode(',', htmlspecialchars_decode($us_categories));
        foreach ($us_category_list as $us_category) {
407 408
          if ($category_query !== '') {
            $category_query .= ' OR ';
409
          }
410 411
          $category = $mysqli->escape_string(trim($us_category));
          $category_query .= 'category = "' . $category . '"';
412
        }
413
        if ($category_query !== '') {
414
          $category_query = '(' . $category_query . ') AND ';
415 416
        }
      }
417 418 419
      $query = 'UPDATE stock LEFT JOIN users ON stock.user = users.user SET ' .
        'purchase_available = order_available WHERE ' . $category_query .
        'system_group = "' . $group . '"';
420
      if (!$mysqli->query($query)) {
421
        $this->Log('Stock->Cron 1: ' . $mysqli->error);
422
      }
423
      if ($this->Substitute('stock-order-available') === 'true') {
424
        $query = 'UPDATE stock LEFT JOIN users ON stock.user = users.user ' .
425
          'INNER JOIN stock_order_price ON stock.name = stock_order_price.name'.
426 427
          ' AND stock.user = stock_order_price.user SET unit = order_unit, ' .
          'pack_size = order_pack_size, base_price = order_base_price, ' .
428 429
          'wholesale_price = order_wholesale_price, retail_price = ' .
          'order_retail_price WHERE system_group = "' . $group . '"';
430
        if (!$mysqli->query($query)) {
431
          $this->Log('Stock->Cron 2: ' . $mysqli->error);
432
        }
433 434 435 436 437 438
        // Automatically update items tracked by the Cart module too.
        $query = 'SELECT stock_order_price.name, stock_order_price.user FROM ' .
          'stock_order_price LEFT JOIN stock ON stock_order_price.name = ' .
          'stock.name AND stock_order_price.user = stock.user LEFT JOIN ' .
          'users ON stock_order_price.user = users.user WHERE ' .
          'system_group = "' . $group . '" AND cart = 1';
439 440
        if ($mysqli_result = $mysqli->query($query)) {
          while ($stock_order_price = $mysqli_result->fetch_assoc()) {
441 442 443
            $name = $mysqli->escape_string($stock_order_price['name']);
            $user = $mysqli->escape_string($stock_order_price['user']);
            $this->UpdateCart($name, $user);
444
          }
445
          $mysqli_result->close();
446 447 448 449
        }
        else {
          $this->Log('Stock->Cron 3: ' . $mysqli->error);
        }
450 451
        // Delete from stock_order_price table after the update, so that prices
        // are only updated each week for items found in any imported lists.
452 453 454
        $query = 'DELETE stock_order_price FROM stock_order_price LEFT JOIN ' .
          'users ON stock_order_price.user = users.user WHERE ' .
          'system_group = "' . $group . '"';
455
        if (!$mysqli->query($query)) {
456
          $this->Log('Stock->Cron 4: ' . $mysqli->error);
457
        }
458 459 460
      }
    }
    $mysqli->close();
461 462
  }

463 464
  public function Factory($fn, $p = NULL) {
    if (is_array($p)) {
465 466
      $count = count($p);
      if ($fn === 'AddSupplyGroup' && $count === 2) {
467 468 469 470
        $user = $p[0];
        $group = $p[1];
        return $this->AddSupplyGroup($user, $group);
      }
471
      if ($fn === 'RemoveSupplyGroup' && $count === 2) {
472 473 474 475
        $user = $p[0];
        $group = $p[1];
        return $this->RemoveSupplyGroup($user, $group);
      }
476 477 478 479 480
      if ($fn === 'UpdateCart' && $count === 2) {
        $product = $p[0];
        $supplier = $p[1];
        return $this->UpdateCart($product, $supplier);
      }
481 482
      return;
    }
483 484 485 486 487
    if ($fn === 'AvailableProducts') {
      // These parameters are set for the Cart module, which is the only module
      // currently calling this function via the Factory method.
      return $this->AvailableProducts(false, false, true);
    }
488 489
    if ($fn === 'ShowSupplyGroups') return $this->ShowSupplyGroups();
    if ($fn === 'AllSuppliers') return $this->AllSuppliers($p);
490 491
  }

492 493 494 495 496 497 498 499 500 501
  public function Group() {

  }

  public function IncludeScript() {
    return true;
  }

  public function Install($path) {
    // Append dobrado.stock.js to the existing dobrado.js file.
502
    // Note that the module is only available when logged in.
503
    $this->AppendScript($path, 'dobrado.stock.js', false);
504
    $mysqli = connect_db();
505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527
    $query = 'CREATE TABLE IF NOT EXISTS stock (' .
      'name VARCHAR(100) NOT NULL,' .
      'user VARCHAR(50) NOT NULL,' .
      'unit ENUM("each", "kg", "L", "variable", "g", "adjusted") NOT NULL,' .
      'pack_size DECIMAL(8,3) NOT NULL,' .
      'base_price DECIMAL(8,2) NOT NULL,' .
      'wholesale_price DECIMAL(8,2) NOT NULL,' .
      'retail_price DECIMAL(8,2) NOT NULL,' .
      'category VARCHAR(100),' .
      'description TEXT,' .
      'image VARCHAR(200),' .
      'grower VARCHAR(200),' .
      'order_available TINYINT(1),' .
      'purchase_available TINYINT(1),' .
      'supplier_available TINYINT(1),' .
      'taxable TINYINT(1),' .
      'quantity DECIMAL(8,3) NOT NULL,' .
      'composite TINYINT(1) NOT NULL,' .
      'track TINYINT(1),' .
      'bulk TINYINT(1),' .
      'hidden TINYINT(1),' .
      'cart TINYINT(1),' .
      'PRIMARY KEY(name, user)' .
528 529
      ') ENGINE=MyISAM';
    if (!$mysqli->query($query)) {
530
      $this->Log('Stock->Install 1: ' . $mysqli->error);
531
    }
532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557
    $query = 'CREATE TABLE IF NOT EXISTS stock_history (' .
      'name VARCHAR(100) NOT NULL,' .
      'user VARCHAR(50) NOT NULL,' .
      'unit ENUM("each", "kg", "L", "variable", "g", "adjusted") NOT NULL,' .
      'pack_size DECIMAL(8,3) NOT NULL,' .
      'base_price DECIMAL(8,2) NOT NULL,' .
      'wholesale_price DECIMAL(8,2) NOT NULL,' .
      'retail_price DECIMAL(8,2) NOT NULL,' .
      'category VARCHAR(100),' .
      'description TEXT,' .
      'image VARCHAR(200),' .
      'grower VARCHAR(200),' .
      'order_available TINYINT(1),' .
      'purchase_available TINYINT(1),' .
      'supplier_available TINYINT(1),' .
      'taxable TINYINT(1),' .
      'quantity DECIMAL(8,3) NOT NULL,' .
      'composite TINYINT(1) NOT NULL,' .
      'track TINYINT(1),' .
      'bulk TINYINT(1),' .
      'hidden TINYINT(1),' .
      'cart TINYINT(1),' .
      'action ENUM("edit", "remove"),' .
      'modified_by VARCHAR(50),' .
      'timestamp INT(10) UNSIGNED NOT NULL,' .
      'PRIMARY KEY(name, user, timestamp)' .
558 559
      ') ENGINE=MyISAM';
    if (!$mysqli->query($query)) {
560
      $this->Log('Stock->Install 2: ' . $mysqli->error);
561
    }
562 563 564 565
    $query = 'CREATE TABLE IF NOT EXISTS stock_supply_group (' .
      'user VARCHAR(50) NOT NULL,' .
      'system_group VARCHAR(50),' .
      'PRIMARY KEY(user, system_group)' .
566 567
      ') ENGINE=MyISAM';
    if (!$mysqli->query($query)) {
568
      $this->Log('Stock->Install 3: ' . $mysqli->error);
569
    }
570 571 572 573 574 575 576 577
    $query = 'CREATE TABLE IF NOT EXISTS stock_adjustment (' .
      'name VARCHAR(100) NOT NULL,' .
      'user VARCHAR(50) NOT NULL,' .
      'adjustment DECIMAL(8,3) NOT NULL,' .
      'description TEXT,' .
      'modified_by VARCHAR(50),' .
      'timestamp INT(10) UNSIGNED NOT NULL,' .
      'PRIMARY KEY(name, user, timestamp)' .
578 579
      ') ENGINE=MyISAM';
    if (!$mysqli->query($query)) {
580
      $this->Log('Stock->Install 4: ' . $mysqli->error);
581
    }
582 583 584 585 586 587 588 589
    $query = 'CREATE TABLE IF NOT EXISTS stock_alternative (' .
      'name VARCHAR(100) NOT NULL,' .
      'user VARCHAR(50) NOT NULL,' .
      'half_size DECIMAL(8,3) NOT NULL,' .
      'half_price DECIMAL(8,2) NOT NULL,' .
      'full_size DECIMAL(8,3) NOT NULL,' .
      'full_price DECIMAL(8,2) NOT NULL,' .
      'PRIMARY KEY(name, user)' .
590 591
      ') ENGINE=MyISAM';
    if (!$mysqli->query($query)) {
592
      $this->Log('Stock->Install 5: ' . $mysqli->error);
593
    }
594 595 596 597 598 599 600 601 602 603
    $query = 'CREATE TABLE IF NOT EXISTS stock_order_price (' .
      'name VARCHAR(100) NOT NULL,' .
      'user VARCHAR(50) NOT NULL,' .
      'order_unit ENUM("each", "kg", "L", "variable", "g", "adjusted") ' .
        'NOT NULL,' .
      'order_pack_size DECIMAL(8,3) NOT NULL,' .
      'order_base_price DECIMAL(8,2) NOT NULL,' .
      'order_wholesale_price DECIMAL(8,2) NOT NULL,' .
      'order_retail_price DECIMAL(8,2) NOT NULL,' .
      'PRIMARY KEY(name, user)' .
604 605
      ') ENGINE=MyISAM';
    if (!$mysqli->query($query)) {
606
      $this->Log('Stock->Install 6: ' . $mysqli->error);
607
    }
608 609 610 611 612 613 614
    $query = 'CREATE TABLE IF NOT EXISTS stock_column_profile (' .
      'name VARCHAR(100) NOT NULL,' .
      'value VARCHAR(100) NOT NULL,' .
      'user VARCHAR(50) NOT NULL,' .
      'columns TEXT NOT NULL,' .
      'sort_columns TEXT NOT NULL,' .
      'PRIMARY KEY(name, user)' .
615 616
      ') ENGINE=MyISAM';
    if (!$mysqli->query($query)) {
617
      $this->Log('Stock->Install 7: ' . $mysqli->error);
618
    }
619

620
    $us_info = '<p><b>Using Import</b><br><br>To use the import function you ' .
621
      'first need to have created accounts for each of your suppliers. If you '.
622 623 624 625 626
      'haven\'t done that, you can create them using the <b>add supplier</b> ' .
      'button in the product form. Once you\'ve done this, browse for the ' .
      'file you want to import. After a successful import the grid will show ' .
      'you products that have become available. This will be the full list ' .
      'the first time you import a file for a supplier, but usually only a ' .
627
      'few products change their availability week to week. You can return to '.
628 629 630 631 632 633 634
      'the full list of products by clicking the <b>show all</b> button.</p>' .
      '<p><b>Availability</b><br><br>Next you can update any products that ' .
      'should be made available to your members. Clicking in the grid will ' .
      'allow you to edit a checkbox inline for each product. This will save ' .
      'automatically as you move to the next product. You can also hold down ' .
      '<b>shift</b> or <b>ctrl</b> as you click to select multiple rows, and ' .
      'the product form will open automatically for you. When changing ' .
635
      'multiple items at once, only the fields that you enter values for will '.
636 637 638 639
      'be updated. The rest will keep the existing values for each item. By ' .
      'default only products that are available from suppliers are shown in ' .
      'the grid. To display both available and unavailable products click ' .
      '<b>Edit Columns</b> and then <b>Supplier Availability</b>.</p>' .
640
      '<p><b>Hidden Products</b><br><br>Sometimes the availability lists from '.
641 642 643 644 645
      'suppliers contain more products than you\'re interested in. You can ' .
      'hide these products using the <b>Hidden</b> checkbox option. This is ' .
      'also found by clicking the <b>Edit columns</b> button, then the <b>' .
      'Hidden Status</b> button. When this option is selected the hidden ' .
      'products will also be added to the grid.</p>' .
646
      '<p><b>Alternatives</b><br><br>Products with alternative pack sizes and '.
647 648 649 650 651
      'prices can be listed in the import file (they must be on consecutive ' .
      'lines). To see which products have alternatives edit the displayed  ' .
      'columns and click the <b>Alternative</b> button. You can switch ' .
      'between the two options by opening the product form and clicking the ' .
      '<b>alternative</b>, which is found next to Pack Size. Your preferred ' .
652
      'values will be saved, along with any other changes you\'ve made to the '.
653 654 655 656 657
      'product, when you click submit.</p>' .
      '<p><b>File Types</b><br><br>For those wanting to know more about the ' .
      'file types used when importing, there are two different formats ' .
      'available. One is for products from a single supplier, the other ' .
      'contains products from multiple suppliers. The format for the first ' .
658
      'file is csv <i>(comma separated values)</i> with the first entry being '.
659 660 661
      'the header row containing:<br>Name,Description,Quantity,Size,Unit,' .
      'UnitPrice,Total,Taxable,Grower,Category</p>' .
      '<p>Note that the supplier is not listed here. When using this format ' .
662 663
      'you pick the supplier from a list <i>before importing</i>. If you want '.
      'to use this format to share your product list, also note that the unit '.
664 665 666 667
      'price is calculated by multiplying the pack size by the <i>wholesale ' .
      'price</i>.</p>' .
      '<p>The second import format is also csv, however since it contains ' .
      'the usernames of your suppliers, you don\'t need to select one when ' .
668
      'importing. The header row for this format is:<br>Name,User,Description,'.
669 670 671
      'Quantity,Price,Wholesale,Retail,Size,Unit,Taxable,Grower,Category,<br>' .
      'Available,PurchaseAvailable,SupplierAvailable</p><p>These formats can ' .
      'also be exported by switching to one of the two default profiles and ' .
672
      'then clicking <b>Download</b>.</p>';
673
    $template = ['"stock-taxable","","Attracts GST"',
674
                 '"stock-not-taxable","","Exempt from GST"',
675 676
                 '"stock-import-info","",' .
                   '"' . $mysqli->escape_string($us_info) . '"'];
677
    $mysqli->close();
678 679

    $this->AddTemplate($template);
680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704
    $description = ['stock-order-available' => 'The string \'true\' or ' .
                      '\'false\', when \'true\' two availability checkboxes ' .
                      'are shown on the stock page and the purchase page ' .
                      'will show only items that are available depending on ' .
                      'which mode it is in.',
                    'stock-order-update' => 'When stock-order-available is ' .
                      'true, this is the day when available to purchase is ' .
                      'updated from available to order.',
                    'stock-taxable' => 'The label used for the tax checkbox ' .
                      'on the stock page.',
                    'stock-wholesale-percent' => 'The default percent markup ' .
                      'when automatically setting the wholesale price.',
                    'stock-retail-percent' => 'The default percent markup ' .
                      'when automatically setting the retail price.',
                    'stock-group-select' => 'The string \'true\' or ' .
                      '\'false\' to show options to select which groups a ' .
                      'member can supply in the \'Manage Accounts\' dialog.',
                    'stock-import-orders-only' => 'The string \'true\' or ' .
                      '\'false\' to show a checkbox on the stock page that ' .
                      'when true, will only import rows that have a value in ' .
                      'their quantity column.',
                    'stock-track-quantity' => 'The string \'true\' or ' .
                      '\'false\' to show extra stock tracking options on the ' .
                      'stock page.'];
    $this->AddTemplateDescription($description);
705

706
    $site_style = ['"","#stock-form label","width","14em"',
707
                   '"","#stock-form label[for=stock-alternative]",' .
708
                     '"width","auto"',
709
                   '"","#stock-form label[for=stock-alternative]",' .
710
                     '"float","none"',
711
                   '"","#stock-form label[for=stock-alternative]",' .
712
                     '"margin-left","5px"',
713 714
                   '"","label[for=stock-track-input]","float","none"',
                   '"","label[for=stock-track-input]","margin-left","5px"',
715 716 717 718 719
                   '"","#stock-form .submit","float","right"',
                   '"",".stock-quantity-dialog .export","float","right"',
                   '"",".stock-adjust","margin-top","15px"',
                   '"",".stock-info","padding","5px"',
                   '"",".stock-info > button","margin-left","10px"',
720 721 722 723 724 725 726 727 728 729 730
                   '"",".stock-price-info","float","right"',
                   '"",".stock-price-info","font-size","0.9em"',
                   '"",".stock-price-info","color","#555555"',
                   '"",".stock-price-info","margin-top","5px"',
                   '"",".stock-price-info a","color","#555555"',
                   '"","#stock-group-select-button","width","180px"',
                   '"","#stock-buttons","margin-bottom","5px"',
                   '"","#stock-open-product-form","float","right"',
                   '"","#stock-import-help","float","right"',
                   '"","#stock-import-wrapper","display","inline-block"',
                   '"","#stock-import-supplier-button","width","150px"',
731
                   '"","#stock-import-input","width","170px"',
732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747
                   '"","#stock-import-file","max-width","240px"',
                   '"","#stock-download","float","right"',
                   '"","#stock-profiles-button","float","right"',
                   '"","#stock-profiles-button","width","110px"',
                   '"","#stock-column-display","padding-top","10px"',
                   '"","#stock-column-display","display","none"',
                   '"","#stock-column-left","float","left"',
                   '"","#stock-column-wrapper","overflow","hidden"',
                   '"","#stock-column-wrapper","float","left"',
                   '"","#stock-columns","transition","margin-left 1s"',
                   '"","#stock-column-right","float","right"',
                   '"","#stock-clear","clear","both"',
                   '"","#stock-form-buttons","display","flex"',
                   '"","#stock-form-buttons","justify-content","space-between"',
                   '"","#stock-form-buttons","margin-bottom","10px"',
                   '"","#stock-profile-add","margin-left","10px"',
748 749
                   '"",".stock-profile-remove","margin-right","10px"',
                   '"","#stock-quantity-reason-input","width","98%"'];
750
    $this->AddSiteStyle($site_style);
751
    return $this->Dependencies(['detail', 'invite', 'purchase']);
752 753 754
  }

  public function Placement() {
755
    return 'middle';
756 757
  }

758 759 760 761
  public function Publish($id, $update) {

  }

762
  public function Remove($id) {
763 764 765
    // Return if Remove was called for a specific module,
    // only want to remove stock when deleting an account.
    if (isset($id)) return;
766

767 768 769
    // Note that deleting accounts with existing stock must be done
    // very carefully because it can effect the balance of other users.
    $mysqli = connect_db();
770
    $query = 'DELETE FROM stock WHERE user = "' . $this->owner . '"';
771
    if (!$mysqli->query($query)) {
772
      $this->Log('Stock->Remove 1: ' . $mysqli->error);
773
    }
774
    $query = 'DELETE FROM stock_history WHERE user = "' . $this->owner . '"';
775
    if (!$mysqli->query($query)) {
776
      $this->Log('Stock->Remove 2: ' . $mysqli->error);
777
    }
778
    $query = 'DELETE FROM stock_supply_group WHERE user = "' . $this->owner.'"';
779
    if (!$mysqli->query($query)) {
780
      $this->Log('Stock->Remove 3: ' . $mysqli->error);
781
    }
782
    $query = 'DELETE FROM stock_adjustment WHERE user = "' . $this->owner . '"';
783
    if (!$mysqli->query($query)) {
784
      $this->Log('Stock->Remove 4: ' . $mysqli->error);
785
    }
786
    $query = 'DELETE FROM stock_alternative WHERE user = "' . $this->owner .'"';
787
    if (!$mysqli->query($query)) {
788
      $this->Log('Stock->Remove 5: ' . $mysqli->error);
789
    }
790
    $query = 'DELETE FROM stock_order_price WHERE user = "' . $this->owner .'"';
791
    if (!$mysqli->query($query)) {
792
      $this->Log('Stock->Remove 6: ' . $mysqli->error);
793
    }
794
    $mysqli->close();
795 796 797 798 799 800 801
  }

  public function SetContent($id, $us_content) {

  }

  public function Update() {
802
    $mysqli = connect_db();
803 804
    $query = 'ALTER TABLE stock ADD COLUMN description TEXT AFTER category';
    if (!$mysqli->query($query)) {
805
      $this->Log('Stock->Update 1: ' . $mysqli->error);
806
    }
807
    $query = 'ALTER TABLE stock ADD COLUMN image VARCHAR(200) AFTER ' .
808 809
      'description';
    if (!$mysqli->query($query)) {
810
      $this->Log('Stock->Update 2: ' . $mysqli->error);
811 812 813
    }
    $query = 'ALTER TABLE stock ADD COLUMN bulk TINYINT(1) AFTER track';
    if (!$mysqli->query($query)) {
814
      $this->Log('Stock->Update 3: ' . $mysqli->error);
815 816 817
    }
    $query = 'ALTER TABLE stock ADD COLUMN hidden TINYINT(1) AFTER bulk';
    if (!$mysqli->query($query)) {
818
      $this->Log('Stock->Update 4: ' . $mysqli->error);
819 820 821
    }
    $query = 'ALTER TABLE stock ADD COLUMN cart TINYINT(1) AFTER hidden';
    if (!$mysqli->query($query)) {
822
      $this->Log('Stock->Update 5: ' . $mysqli->error);
823
    }
824
    $query = 'ALTER TABLE stock_history ADD COLUMN description TEXT AFTER ' .
825 826
      'category';
    if (!$mysqli->query($query)) {
827
      $this->Log('Stock->Update 6: ' . $mysqli->error);
828
    }
829
    $query = 'ALTER TABLE stock_history ADD COLUMN image VARCHAR(200) AFTER ' .
830 831
      'description';
    if (!$mysqli->query($query)) {
832
      $this->Log('Stock->Update 7: ' . $mysqli->error);
833
    }
834
    $query = 'ALTER TABLE stock_history ADD COLUMN bulk TINYINT(1) AFTER ' .
835 836
      'track';
    if (!$mysqli->query($query)) {
837
      $this->Log('Stock->Update 8: ' . $mysqli->error);
838
    }
839
    $query = 'ALTER TABLE stock_history ADD COLUMN hidden TINYINT(1) AFTER ' .
840 841
      'bulk';
    if (!$mysqli->query($query)) {
842
      $this->Log('Stock->Update 9: ' . $mysqli->error);
843
    }
844
    $query = 'ALTER TABLE stock_history ADD COLUMN cart TINYINT(1) AFTER ' .
845 846
      'hidden';
    if (!$mysqli->query($query)) {
847
      $this->Log('Stock->Update 10: ' . $mysqli->error);
848
    }
849
    $query = 'UPDATE stock SET description = "", image = "", bulk = 0, ' .
850 851
      'hidden = 0, cart = 0';
    if (!$mysqli->query($query)) {
852
      $this->Log('Stock->Update 11: ' . $mysqli->error);
853 854 855
    }
    $query = 'UPDATE stock SET hidden = 1 WHERE category = "hidden"';
    if (!$mysqli->query($query)) {
856
      $this->Log('Stock->Update 12: ' . $mysqli->error);
857 858 859
    }
    $query = 'UPDATE stock SET category = "" WHERE category = "hidden"';
    if (!$mysqli->query($query)) {
860 861 862 863 864 865 866 867 868
      $this->Log('Stock->Update 13: ' . $mysqli->error);
    }
    $query = 'CREATE TABLE IF NOT EXISTS stock_column_profile (' .
      'name VARCHAR(100) NOT NULL,' .
      'value VARCHAR(100) NOT NULL,' .
      'user VARCHAR(50) NOT NULL,' .
      'columns TEXT NOT NULL,' .
      'sort_columns TEXT,' .
      'PRIMARY KEY(name, user)' .
869 870
      ') ENGINE=MyISAM';
    if (!$mysqli->query($query)) {
871
      $this->Log('Stock->Update 14: ' . $mysqli->error);
872 873
    }
    $mysqli->close();
874 875 876 877 878 879 880 881 882 883 884 885 886 887 888 889 890 891 892 893 894 895 896 897 898 899 900 901 902 903

    $site_style = ['"","label[for=stock-track-input]","float","none"',
                   '"","label[for=stock-track-input]","margin-left","5px"',
                   '"",".stock-price-info","float","right"',
                   '"",".stock-price-info","font-size","0.9em"',
                   '"",".stock-price-info","color","#555555"',
                   '"",".stock-price-info","margin-top","5px"',
                   '"",".stock-price-info a","color","#555555"',
                   '"","#stock-group-select-button","width","180px"',
                   '"","#stock-buttons","margin-bottom","5px"',
                   '"","#stock-open-product-form","float","right"',
                   '"","#stock-import-help","float","right"',
                   '"","#stock-import-wrapper","display","inline-block"',
                   '"","#stock-import-supplier-button","width","150px"',
                   '"","#stock-import-file","max-width","240px"',
                   '"","#stock-download","float","right"',
                   '"","#stock-profiles-button","float","right"',
                   '"","#stock-profiles-button","width","90px"',
                   '"","#stock-column-display","padding-top","10px"',
                   '"","#stock-column-display","display","none"',
                   '"","#stock-column-left","float","left"',
                   '"","#stock-column-wrapper","overflow","hidden"',
                   '"","#stock-column-wrapper","float","left"',
                   '"","#stock-columns","transition","margin-left 1s"',
                   '"","#stock-column-right","float","right"',
                   '"","#stock-clear","clear","both"',
                   '"","#stock-form-buttons","display","flex"',
                   '"","#stock-form-buttons","justify-content","space-between"',
                   '"","#stock-form-buttons","margin-bottom","10px"',
                   '"","#stock-profile-add","margin-left","10px"',
904 905
                   '"",".stock-profile-remove","margin-right","10px"',
                   '"","#stock-quantity-reason-input","width","98%"'];
906
    $this->AddSiteStyle($site_style);
907 908 909
  }

  public function UpdateScript($path) {
910
    $this->AppendScript($path, 'dobrado.stock.js', false);
911 912
  }

913 914
  // Public functions that aren't part of interface here /////////////////////

915 916
  public function AllProducts($array = true,
                              $purchase_group = '', $show_hidden = true) {
917
    $organiser = new Organiser($this->user, $this->owner);
918
    $invite = new Invite($this->user, $this->owner);
919
    $hidden_query = $show_hidden === true ? '' : 'hidden = 0 AND ';
920
    // Some modules want all products in all groups, others want all products
921
    // in a limited set of groups.
922 923 924
    $group_query = '';
    if ($purchase_group === '') {
      $group_query .= $organiser->GroupQuery();
925
      foreach ($invite->AllBuyingGroups() as $group) {
926
        $group_query .= ' OR system_group = "' . $group . '"';
927 928 929 930 931 932
      }
    }
    else if ($purchase_group === $this->user->group) {
      $group_query .= $organiser->GroupQuery();
    }
    else if (in_array($purchase_group, $invite->AllBuyingGroups())) {
933
      $group_query .= 'system_group = "' . $purchase_group . '"';
934 935 936
    }
    else {
      // This shouldn't happen so just set to the user's own group.
937
      $group_query .= 'system_group = "' . $this->user->group . '"';
938
    }
939 940

    $result = [];
941
    $mysqli = connect_db();
942 943 944 945
    $query = 'SELECT name, stock.user, unit, base_price, wholesale_price, ' .
      'retail_price, category, order_available, purchase_available, taxable, ' .
      'composite FROM stock LEFT JOIN users ON stock.user = users.user WHERE ' .
      $hidden_query . '(' . $group_query . ') ORDER BY name, user';
946 947
    if ($mysqli_result = $mysqli->query($query)) {
      while ($stock = $mysqli_result->fetch_assoc()) {
Malcolm Blaney's avatar
Malcolm Blaney committed
948 949
        $available = (int)$stock['order_available'] === 1 ||
          (int)$stock['purchase_available'] === 1;
950
        $product = [];
951
        if ($stock['unit'] === 'adjusted') {
952 953 954 955
          $product = $this->ChangeUnits($stock);
          $product['available'] = $available;
        }
        else {
956 957 958 959 960 961 962 963
          $product = ['unit' => $stock['unit'],
                      'price' => (float)$stock['base_price'],
                      'wholesale' => (float)$stock['wholesale_price'],
                      'retail' => (float)$stock['retail_price'],
                      'category' => $stock['category'],
                      'available' => $available,
                      'taxable' => (int)$stock['taxable'],
                      'composite' => (int)$stock['composite']];
964
        }
965
        if ($array) {
966 967
          $product['user'] = $stock['user'];
          $product['name'] = $stock['name'];
968
          $result[] = $product;
969 970
        }
        else {
971 972
          $user = $stock['user'];
          $name = $stock['name'];
973 974
          if (!isset($result[$user])) {
            $result[$user] = [];
975
          }
976
          $result[$user][$name] = $product;
977
        }
978
      }
979
      $mysqli_result->close();
980 981
    }
    else {
982
      $this->Log('Stock->AllProducts: ' . $mysqli->error);
983 984
    }
    $mysqli->close();
985
    return $result;
986 987
  }

988 989 990 991 992 993 994 995
  public function AllSuppliers($all_groups = false) {
    $query = '';
    // Suppliers across all groups are required for multi-group buys.
    if ($all_groups) {
      $query = 'SELECT DISTINCT user FROM stock';
    }
    else {
      $organiser = new Organiser($this->user, $this->owner);
996 997
      $query = 'SELECT DISTINCT stock.user FROM stock LEFT JOIN users ON ' .
        'stock.user = users.user WHERE ' . $organiser->GroupQuery();
998
    }
999 1000

    $result = [];
1001
    $mysqli = connect_db();
1002 1003 1004
    if ($mysqli_result = $mysqli->query($query)) {
      while ($stock = $mysqli_result->fetch_assoc()) {
        $result[] = $stock['user'];
1005
      }
1006
      $mysqli_result->close();
1007 1008
    }
    else {
1009
      $this->Log('Stock->AllSuppliers: ' . $mysqli->error);
1010 1011
    }
    $mysqli->close();
1012
    return $result;
1013
  }
1014

1015 1016 1017
  public function AllTaxable() {
    $organiser = new Organiser($this->user, $this->owner);
    $invite = new Invite($this->user, $this->owner);
1018 1019
    $group_query = $organiser->GroupQuery();
    foreach ($invite->AllBuyingGroups() as $group) {
1020
      $group_query .= ' OR system_group = "' . $group . '"';
1021
    }
1022 1023

    $result = [];
1024
    $mysqli = connect_db();
1025 1026
    $query = 'SELECT name, stock.user FROM stock LEFT JOIN users ON ' .
      'stock.user = users.user WHERE (' . $group_query . ') AND taxable = 1';
1027 1028
    if ($mysqli_result = $mysqli->query($query)) {
      while ($stock = $mysqli_result->fetch_assoc()) {
1029 1030
        $user = $stock['user'];
        $name = $stock['name'];
1031 1032
        if (!isset($result[$user])) {
          $result[$user] = [];
1033
        }
1034
        $result[$user][$name] = true;
1035
      }
1036
      $mysqli_result->close();
1037 1038
    }
    else {
1039
      $this->Log('Stock->AllTaxable: ' . $mysqli->error);
1040 1041
    }
    $mysqli->close();
1042
    return $result;
1043 1044
  }

1045 1046
  public function AvailableProducts($array = true,
                                    $purchase = true, $cart = false) {
1047
    $organiser = new Organiser($this->user, $this->owner);
1048
    $available_query = 'AND order_available = 1';
1049
    $order_available = $this->Substitute('stock-order-available') === 'true';
1050 1051
    // Only use purchase_available if requested and both availability options
    // are being used. Otherwise order_available is the default.
1052
    if ($purchase && $order_available) {
1053 1054
      $available_query = 'AND purchase_available = 1';
    }
1055 1056 1057
    if ($cart) {
      $available_query .= ' AND cart = 1';
    }
1058 1059 1060 1061
    // The first part of this query retrieves prices from the stock table
    // and possibly also the stock_order_price table depending on group
    // settings (and the purchase parameter).
    $query = '';
1062
    if (!$purchase && $order_available) {
1063 1064 1065 1066 1067
      $query = 'SELECT stock.name, stock.user, order_unit, unit, ' .
        'order_pack_size, pack_size, order_base_price, base_price, ' .
        'order_wholesale_price, wholesale_price, order_retail_price, ' .
        'retail_price, category, grower, quantity, track ' .
        'FROM stock LEFT JOIN stock_order_price ON stock.name = ' .
1068 1069 1070
        'stock_order_price.name AND stock.user = stock_order_price.user ';
    }
    else {
1071 1072
      $query = 'SELECT name, stock.user, unit, pack_size, base_price, ' .
        'wholesale_price, retail_price, category, grower, quantity, track ' .
1073 1074 1075 1076 1077 1078 1079 1080 1081 1082
        'FROM stock ';
    }
    // The second part of the query finds all available products where the
    // suppliers are members of the same organisation, and then further filters
    // the set by making sure each supplier found supplies to the current user's
    // group (or the empty group, which is used to specify that they supply all
    // groups). Note that the system_group check will produce duplicates if
    // both are found, so the stock_supply_group table should never store both
    // the empty system_group and a list of specific groups (must be one or the
    // other).
1083 1084 1085 1086 1087
    $query .= 'LEFT JOIN users ON stock.user = users.user LEFT JOIN ' .
      'stock_supply_group ON stock_supply_group.user = stock.user WHERE ' .
      'composite = 0 AND ' . $organiser->GroupQuery() . ' AND ' .
      '(stock_supply_group.system_group = "' . $this->user->group . '" OR ' .
      'stock_supply_group.system_group = "") ' . $available_query .
1088
      ' ORDER BY name, user';
1089 1090 1091 1092 1093

    $result = [];
    $mysqli = connect_db();
    if ($mysqli_result = $mysqli->query($query)) {
      while ($stock = $mysqli_result->fetch_assoc()) {
1094 1095 1096
        // If this product has values set in the stock_order_price table,
        // use them in preference to the stock table here. (Assume that if
        // the first value is not null, that this row is safe to use).
1097
        if (!$purchase && $order_available && isset($stock['order_unit'])) {
1098 1099 1100 1101 1102 1103 1104
          $stock['unit'] = $stock['order_unit'];
          $stock['pack_size'] = $stock['order_pack_size'];
          $stock['base_price'] = $stock['order_base_price'];
          $stock['wholesale_price'] = $stock['order_wholesale_price'];
          $stock['retail_price'] = $stock['order_retail_price'];
        }
        $product = [];
1105 1106 1107
        // When unit is 'adjusted' the purchase page shows 'each' when ordering
        // and per weight when purchasing. The stock page makes sure the
        // quantity and units are part of the product name to do this.
1108
        if ($stock['unit'] === 'adjusted') {
1109 1110 1111
          $product = $this->ChangeUnits($stock, $purchase, false);
        }
        else {
1112 1113 1114 1115 1116 1117 1118 1119 1120 1121
          $product = ['user' => $stock['user'],
                      'unit' => $stock['unit'],
                      'size' => (float)$stock['pack_size'],
                      'price' => (float)$stock['base_price'],
                      'wholesale' => (float)$stock['wholesale_price'],
                      'retail' => (float)$stock['retail_price'],
                      'category' => $stock['category'],
                      'grower' => $stock['grower'],
                      'quantity' => $stock['quantity'],
                      'track' => (int)$stock['track'] === 1];
1122
        }
1123 1124 1125
        // The grid module requires this data as an indexed array, but it's
        // also useful to have an associative array keyed on product names.
        if ($array) {
1126
          $product['name'] = $stock['name'];
1127
          $result[] = $product;
1128 1129
        }
        else {
1130 1131 1132
          // Note here that even though product names aren't unique, we can
          // still key off them because we make sure only one product is
          // available at a time where the names are the same.
1133
          $result[$stock['name']] = $product;
1134
        }
1135
      }
1136
      $mysqli_result->close();
1137 1138
    }
    else {
1139
      $this->Log('Stock->AvailableProducts: ' . $mysqli->error);
1140 1141
    }
    $mysqli->close();
1142
    return $result;
1143 1144
  }

1145 1146 1147
  public function Composite($product, $user) {
    $composite = false;
    $mysqli = connect_db();
1148 1149
    $query = 'SELECT composite FROM stock WHERE name = "' . $product . '" ' .
      'AND user = "' . $user . '"';
1150 1151
    if ($mysqli_result = $mysqli->query($query)) {
      if ($stock = $mysqli_result->fetch_assoc()) {
Malcolm Blaney's avatar
Malcolm Blaney committed
1152
        $composite = (int)$stock['composite'] === 1;
1153
      }
1154
      $mysqli_result->close();
1155 1156
    }
    else {
1157
      $this->Log('Stock->Composite: ' . $mysqli->error);
1158 1159 1160 1161 1162 1163
    }
    $mysqli->close();
    return $composite;
  }

  public function Decrease($product_list) {
1164
    if ($this->Substitute('stock-track-quantity') !== 'true') return;
1165

1166
    $organiser = new Organiser($this->user, $this->owner);
1167
    $invite = new Invite($this->user, $this->owner);
1168 1169
    $mysqli = connect_db();
    foreach ($product_list as $user => $product) {
1170
      if (!$organiser->MatchUser($user, $invite->AllBuyingGroups())) {
1171
        $this->Log('Stock->Decrease 1: Supplier not found');
1172 1173 1174
        continue;
      }
      foreach ($product as $name => $quantity) {
1175 1176 1177
        $query = 'UPDATE stock SET quantity = quantity - ' . $quantity .
          ' WHERE name = "' . $name . '" AND user = "' . $user . '" AND ' .
          'track = 1';
1178
        if (!$mysqli->query($query)) {
1179
          $this->Log('Stock->Decrease 2: ' . $mysqli->error);
1180 1181 1182
        }
      }
    }
1183 1184
    // TODO: Need to check total quantity of tracked items that are synced with
    // the cart module and update their availability if stock-limited is used.
1185 1186 1187
    $mysqli->close();
  }

1188 1189 1190 1191
  public function Grower($name, $user) {
    // Return the supplier name if grower isn't set.
    $grower = $user;
    $mysqli = connect_db();
1192 1193
    $query = 'SELECT grower FROM stock WHERE name = "' . $name . '" AND ' .
      'user = "' . $user . '"';
1194 1195
    if ($mysqli_result = $mysqli->query($query)) {
      if ($stock = $mysqli_result->fetch_assoc()) {
1196 1197
        if ($stock['grower'] !== '') {
          $grower = $stock['grower'];
1198 1199
        }
      }
1200
      $mysqli_result->close();
1201 1202
    }
    else {
1203
      $this->Log('Stock->Grower: ' . $mysqli->error);
1204 1205 1206 1207 1208
    }
    $mysqli->close();
    return $grower;
  }

1209
  public function Increase($product_list) {
1210
    if ($this->Substitute('stock-track-quantity') !== 'true') return;
1211

1212
    $organiser = new Organiser($this->user, $this->owner);
1213
    $invite = new Invite($this->user, $this->owner);
1214 1215
    $mysqli = connect_db();
    foreach ($product_list as $user => $product) {
1216
      if (!$organiser->MatchUser($user, $invite->AllBuyingGroups())) {
1217
        $this->Log('Stock->Increase 1: Supplier not found');
1218 1219
        continue;
      }
1220 1221 1222
      // TODO: Need to get the quantity before the increase for each product,
      // and if adding the new quantity makes total quantity greater than zero
      // update the cart module when the item is synced and stock-limited used.
1223
      foreach ($product as $name => $quantity) {
1224 1225 1226
        $query = 'UPDATE stock SET quantity = quantity + ' . $quantity .
          ' WHERE name = "' . $name . '" AND user = "' . $user . '" AND ' .
          'track = 1';
1227
        if (!$mysqli->query($query)) {
1228
          $this->Log('Stock->Increase 2: ' . $mysqli->error);
1229 1230 1231 1232 1233 1234
        }
      }
    }
    $mysqli->close();
  }

1235
  public function LastUpdate() {
1236
    $mysqli = connect_db();
1237
    $timestamp = 0;
1238 1239 1240
    $query = 'SELECT MAX(timestamp) AS timestamp FROM stock_history ' .
      'LEFT JOIN users ON stock_history.user = users.user WHERE ' .
      'system_group = "' . $this->user->group . '"';
1241 1242
    if ($mysqli_result = $mysqli->query($query)) {
      if ($stock_history = $mysqli_result->fetch_assoc()) {
1243
        $timestamp = (int)$stock_history['timestamp'];
1244
      }
1245
      $mysqli_result->close();
1246 1247
    }
    else {
1248
      $this->Log('Stock->LastUpdate: ' . $mysqli->error);
1249 1250
    }
    $mysqli->close();
1251
    return $timestamp;
1252 1253
  }

1254
  public function Supplier($user = '', $no_variable_pricing = false) {
1255 1256 1257
    if ($user === '') $user = $this->user->name;

    $result = [];
1258
    $mysqli = connect_db();
1259
    $query = 'SELECT name FROM stock WHERE user = "' . $user . '"';
1260 1261 1262
    if ($no_variable_pricing) {
      $query .= ' AND unit != "variable"';
    }
1263 1264 1265
    if ($mysqli_result = $mysqli->query($query)) {
      while ($stock = $mysqli_result->fetch_assoc()) {
        $result[] = $stock['name'];
1266
      }
1267
      $mysqli_result->close();
1268 1269
    }
    else {
1270
      $this->Log('Stock->Supplier: ' . $mysqli->error);