CORE-POS/IS4C

View on GitHub
fannie/classlib2.0/lib/FormLib.php

Summary

Maintainability
F
1 wk
Test Coverage
F
53%
<?php
/*******************************************************************************

    Copyright 2012 Whole Foods Co-op

    This file is part of CORE-POS.

    IT CORE is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation; either version 2 of the License, or
    (at your option) any later version.

    IT CORE 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 General Public License for more details.

    You should have received a copy of the GNU General Public License
    in the file license.txt along with IT CORE; if not, write to the Free Software
    Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA

*********************************************************************************/

/**
  @class FormLib
*/
class FormLib extends \COREPOS\common\FormLib
{
    public static function get_form_value($name, $default='')
    {
        return self::getFormValue($name, $default);
    }


    /**
      Get form input as a formatted date
      @param $name [string] form field
      @param $default [mixed, optional] default value if
        form input is omitted or invalid
      @param $format [string, optional] date format string.
        Default is Y-m-d.
    */
    public static function getDate($name, $default='', $format='Y-m-d')
    {
        $input_value = self::getFormValue($name, $default);
        $timestamp = strtotime($input_value);
        if ($timestamp === false || $timestamp === -1) {
            // input is invalid
            // if default is invalid that's the caller's problem
            return $default;
        } else {
            return date($format, $timestamp);
        }
    }

    /**
      Get a fieldset to select certain date ranges
      Requires JQquery
      @param $one id for date input (default 'date1')
      @param $two id for date input (default 'date2')
      @param $week_start day number (default 1/Monday)
      @return HTML string
    */
    public static function dateRangePicker($one='date1',$two='date2',$week_start=1)
    {
        /**
          calculate all the applicable dates in PHP
        */

        $today = array(date('Y-m-d'),date('Y-m-d'));

        // find begining of the week
        $monday = time();
        while(date('N',$monday) != $week_start) {
            $monday = mktime(0,0,0,date('n',$monday),date('j',$monday)-1,date('Y',$monday));
        }
        $this_week = array(date('Y-m-d',$monday),
            date('Y-m-d',mktime(0,0,0,date('n',$monday),date('j',$monday)+6,date('Y',$monday)))
        );

        $this_month = array(date('Y-m-01'),date('Y-m-t'));

        $y = mktime(0,0,0,date('n'),date('j')-1,date('Y'));    
        $yesterday = array(date('Y-m-d',$y),date('Y-m-d',$y));

        // go back a week
        $monday = mktime(0,0,0,date('n',$monday),date('j',$monday)-7,date('Y',$monday));
        $last_week = array(date('Y-m-d',$monday),
            date('Y-m-d',mktime(0,0,0,date('n',$monday),date('j',$monday)+6,date('Y',$monday)))
        );

        $lm = mktime(0,0,0,date('n')-1,1,date('Y'));
        $last_month = array(date('Y-m-01',$lm),date('Y-m-t',$lm));

        $ly = mktime(0,0,0,date('n'),date('j'),date('Y')-1);
        $todayLY = array(date('Y-m-d', $ly), date('Y-m-d', $ly));

        $mondayLY = $ly;
        while(date('N',$mondayLY) != $week_start) {
            $mondayLY = mktime(0,0,0,date('n',$mondayLY),date('j',$mondayLY)+1,date('Y',$mondayLY));
        }
        $thisWeekLY = array(date('Y-m-d', $mondayLY), date('Y-m-d', mktime(0,0,0,date('n',$mondayLY),date('j',$mondayLY)+6,date('Y',$mondayLY))));
        $thisMonthLY = array(date('Y-m-01', $ly), date('Y-m-t', $ly));

        $tomLY = mktime(0,0,0,date('n',$ly),date('j',$ly)+1,date('Y',$ly));
        $tomorrowLY = array(date('Y-m-d', $tomLY), date('Y-m-d', $tomLY));
        $nextWeekLY = array(
            date('Y-m-d', mktime(0,0,0,date('n',$mondayLY),date('j',$mondayLY)+7,date('Y',$mondayLY))),
            date('Y-m-d', mktime(0,0,0,date('n',$mondayLY),date('j',$mondayLY)+13,date('Y',$mondayLY))),
        );
        $nmLY = mktime(0,0,0, date('n',$ly)+1, date('j',$ly), date('Y',$ly));
        $nextMonthLY = array(date('Y-m-01', $nmLY), date('Y-m-t', $nmLY));

        $ly = date("Y-m-d", strtotime("last week last year"));
        //$thisWeekLY = array(date('Y-m-d', $mondayLY), date('Y-m-d', mktime(0,0,0,date('n',$mondayLY),date('j',$mondayLY)+6,date('Y',$mondayLY))));
        $lastWeekLY = array(date('Y-m-d', strtotime('next monday', strtotime($ly))),date('Y-m-d', strtotime('next sunday', strtotime($ly))),date('Y',$mondayLY));

        $extra_opts = sprintf('
            <div class="panel panel-default">
                <div class="panel-heading">Other dates</div>
                <div class="panel-body">
                <table class="table">
                <tr><td>
                <label>
                    <input class="radio-inline" id="od10" type="radio" name="other_dates" 
                        onclick="$(\'#%s\').val(\'%s\');$(\'#%s\').val(\'%s\')" /> 
                    Today
                </label>
                </td><td>
                <label>
                    <input class="radio-inline" id="od11" type="radio" name="other_dates" 
                        onclick="$(\'#%s\').val(\'%s\');$(\'#%s\').val(\'%s\')" />
                    This week
                </label>
                </td><td>
                <label>
                    <input class="radio-inline" id="od12" type="radio" name="other_dates" 
                        onclick="$(\'#%s\').val(\'%s\');$(\'#%s\').val(\'%s\')" />
                    This month
                </label>
                </td></tr>
                <tr><td>
                <label>
                    <input class="radio-inline" id="od20" type="radio" name="other_dates" 
                        onclick="$(\'#%s\').val(\'%s\');$(\'#%s\').val(\'%s\')" />
                    Yesterday
                </label>
                </td><td>
                <label>
                    <input class="radio-inline" id="od21" type="radio" name="other_dates" 
                        onclick="$(\'#%s\').val(\'%s\');$(\'#%s\').val(\'%s\')" />
                    Last week
                </label>
                </td><td>
                <label>
                    <input class="radio-inline" id="od22" type="radio" name="other_dates" 
                        onclick="$(\'#%s\').val(\'%s\');$(\'#%s\').val(\'%s\')" />
                    Last month
                </label>
                </td></tr>
                <tr><td>
                <label>
                    <input class="radio-inline" id="od30" type="radio" name="other_dates" 
                        onclick="$(\'#%s\').val(\'%s\');$(\'#%s\').val(\'%s\')" />
                    Today last year
                </label>
                </td><td>
                <label>
                    <input class="radio-inline" id="od31" type="radio" name="other_dates" 
                        onclick="$(\'#%s\').val(\'%s\');$(\'#%s\').val(\'%s\')" />
                    This week last year
                </label>
                </td><td>
                <label>
                    <input class="radio-inline" id="od32" type="radio" name="other_dates" 
                        onclick="$(\'#%s\').val(\'%s\');$(\'#%s\').val(\'%s\')" />
                    This month last year
                </label>
                </td></tr>
                <tr><td>
                <label>
                    <input class="radio-inline" id="od40" type="radio" name="other_dates" 
                        onclick="$(\'#%s\').val(\'%s\');$(\'#%s\').val(\'%s\')" />
                    Tomorrow last year
                </label>
                </td><td>
                <label>
                    <input class="radio-inline" id="od41" type="radio" name="other_dates" 
                        onclick="$(\'#%s\').val(\'%s\');$(\'#%s\').val(\'%s\')" />
                    Next week last year
                </label>
                </td><td>
                <label>
                    <input class="radio-inline" id="od42" type="radio" name="other_dates" 
                        onclick="$(\'#%s\').val(\'%s\');$(\'#%s\').val(\'%s\')" />
                    Next month last year
                </label>
                </td></tr>
                <tr><td>
                <label>
                    <input class="radio-inline" id="od43" type="radio" name="other_dates" 
                        onclick="$(\'#%s\').val(\'%s\');$(\'#%s\').val(\'%s\')" />
                    Last week last year
                </label>
                </td></tr>

                </table>
            </div>
            </div>',
            $one,$today[0],$two,$today[1],
            $one,$this_week[0],$two,$this_week[1],
            $one,$this_month[0],$two,$this_month[1],
            $one,$yesterday[0],$two,$yesterday[1],
            $one,$last_week[0],$two,$last_week[1],
            $one,$last_month[0],$two,$last_month[1],
            $one,$todayLY[0],$two,$todayLY[1],
            $one,$thisWeekLY[0],$two,$thisWeekLY[1],
            $one,$thisMonthLY[0],$two,$thisMonthLY[1],
            $one,$tomorrowLY[0],$two,$tomorrowLY[1],
            $one,$nextWeekLY[0],$two,$nextWeekLY[1],
            $one,$nextMonthLY[0],$two,$nextMonthLY[1],
            $one,$lastWeekLY[0],$two,$lastWeekLY[1]
        );

        return $extra_opts;
    }

    public static function date_range_picker($one='date1',$two='date2',$week_start=1)
    {
        $week_start = (!FannieConfig::config('FANNIE_WEEK_START')) ? 1 :  FannieConfig::config('FANNIE_WEEK_START');
        return self::dateRangePicker($one, $two, $week_start);
    }

    /**
      Get <select> box for the store ID
      @param $field_name [string] select.name (default 'store')
      $param $all [string] include an "all" option (default true)
      @return keyed [array]
        - html => [string] select box
        - names => [array] store names
        - onchange => [string] optional onchange action
    */
    public static function storePicker($field_name='store', $all=true, $onchange='')
    {
        $op_db = FannieConfig::config('OP_DB');
        $dbc = FannieDB::getReadOnly($op_db);

        $stores = new StoresModel($dbc);
        $byIP = COREPOS\Fannie\API\lib\Store::getIdByIp();
        $current = FormLib::get($field_name, false);
        if ($current !== false) {
            $byIP = false;
        }
        $ret = '<select name="' . $field_name . '" class="form-control" onchange="' . $onchange . '">';
        if ($all) {
            $labels = array(0 => _('All Stores'));
            $ret .= '<option value="0">' . $labels[0] . '</option>';
        } else {
            $labels = array();
        }
        foreach($stores->find('storeID') as $store) {
            $selected = '';
            if ($store->storeID() == $current) {
                $selected = 'selected';
            } elseif ($byIP !== false && $byIP == $store->storeID()) {
                $selected = 'selected';
            }
            $ret .= sprintf('<option %s value="%d">%s</option>',
                    $selected,
                    $store->storeID(),
                    $store->description()
            );
            $labels[$store->storeID()] = $store->description();
        }
        $ret .= '</select>';

        return array(
            'html' => $ret,
            'names' => $labels, 
        );
    }

    /**
      Generate a very standard form with date and department fields
      @return [string] html form
    */
    public static function dateAndDepartmentForm($standardFieldNames=false)
    {
        ob_start();
        ?>
<form method="get" class="form-horizontal">
<div class="row">
    <div class="col-sm-6">
        <?php echo $standardFieldNames ? self::standardDepartmentFields('super-dept', 'departments', 'dept-start', 'dept-end') : self::standardDepartmentFields('buyer');  ?>
        <div class="form-group">
            <label class="col-sm-4 control-label">Store</label>
            <div class="col-sm-8">
            <?php
            $stores = FormLib::storePicker();
            echo $stores['html'];
            ?>
            </div>
        </div>
        <div id="date-dept-form-left-col"></div> 
    </div>
    <?php echo self::standardDateFields(); ?>
</div>
<p>
    <button type="submit" class="btn btn-default btn-core">Submit</button>
    <button type="reset" class="btn btn-default btn-reset"
        onclick="$('#super-id').val('').trigger('change');">Start Over</button>
</p>
        <?php
        return ob_get_clean();
    }

    /**
      Build a standardized set of for querying items
      Currently includes tabs for:
      * department / superdepartment
      * brand
      * vendor
      * like code
    */
    public static function standardItemFields()
    {
        $dbc = FannieDB::getReadOnly(FannieConfig::config('OP_DB'));
        ob_start();
        ?>
        <div class="col-sm-5">
            <ul class="nav nav-tabs" role="tablist">
                <li class="active"><a href="#dept-tab" data-toggle="tab"
                    onclick="$('#supertype').val('dept');">Department</a></li>
                <li><a href="#manu-tab" data-toggle="tab"
                    onclick="$('#supertype').val('manu');">Brand</a></li>
                <li><a href="#vendor-tab" data-toggle="tab"
                    onclick="$('#supertype').val('vendor');">Vendor</a></li>
                <li><a href="#likecode-tab" data-toggle="tab"
                    onclick="$('#supertype').val('likecode');">Like Code</a></li>
            </ul>
            <input id="supertype" name="lookup-type" type="hidden" value="dept" />
            <div class="tab-content"><p>
                <div class="tab-pane active" id="dept-tab">
                    <div class="form-horizontal">
                        <?php echo self::standardDepartmentFields('super-dept', 'departments', 'dept-start', 'dept-end'); ?>
                    </div>
                </div>
                <div class="tab-pane" id="manu-tab">
                    <div class="form-group form-inline">
                        <label><?php echo _('Brand'); ?></label>
                        <input type=text name=manufacturer class="form-control" />
                    </div>
                    <div class="form-group form-inline">
                        <label><input type=radio name=mtype value=prefix checked />
                            UPC prefix</label>
                        <label><input type=radio name=mtype value=name />
                            <?php echo _('Brand name'); ?></label>
                    </div>
                </div>
                <div class="tab-pane" id="vendor-tab">
                    <div class="form-group form-inline">
                        <label>Vendor</label>
                        <select name="vendor" class="form-control">
                        <?php
                        $vendors = $dbc->query('
                            SELECT vendorID, vendorName
                            FROM vendors
                            ORDER BY vendorName');
                        while ($w = $dbc->fetch_row($vendors)) {
                            printf('<option value="%d">%s</option>',
                                $w['vendorID'], $w['vendorName']);
                        }
                        ?>
                        </select>
                    </div>
                </div>
                <div class="tab-pane" id="likecode-tab">
                    <div class="row form-group form-horizontal">
                        <label class="control-label col-sm-3">Likecode Start</label>
                        <div class="col-sm-6">
                            <select onchange="$('#lc-start').val(this.value);" class="form-control input-sm">
                            <?php
                            $likecodes = $dbc->query('
                                SELECT likeCode, likeCodeDesc
                                FROM likeCodes
                                ORDER BY likeCode');
                            $lc_list = array();
                            while ($w = $dbc->fetch_row($likecodes)) {
                                $lc_list[$w['likeCode']] = $w['likeCodeDesc'];
                            }
                            foreach ($lc_list as $id => $name) {
                                printf('<option value="%d">%d %s</option>',
                                    $id, $id, $name);
                            }
                            ?>
                            </select>
                        </div>
                        <div class="col-sm-2">
                            <input type=text id=lc-start name=lc-start 
                                class="form-control input-sm" value=1>
                        </div>
                    </div>
                    <div class="row form-group form-horizontal">
                        <label class="control-label col-sm-3">Likecode End</label>
                        <div class="col-sm-6">
                            <select onchange="$('#lc-end').val(this.value);" class="form-control input-sm">
                            <?php
                            foreach ($lc_list as $id => $name) {
                                printf('<option value="%d">%d %s</option>',
                                    $id, $id, $name);
                            }
                            ?>
                            </select>
                        </div>
                        <div class="col-sm-2">
                            <input type=text id=lc-end name=lc-end 
                                class="form-control input-sm" value=1>
                        </div>
                    </div>
                </div>
            </p></div>
        </div>
        <?php

        return ob_get_clean();
    }

    /**
      Draw a standard set of bootstrap'd department fields
      with javascript chaining as they change
      @param $super [string, default 'super'] name of the super department <select>
      @param $multi [string, default 'departments'] name of the department multi <select>
      @param $start [string, default 'deptStart'] name of the department start single <select>
      @param $end [string, default 'deptEnd'] name of the department end single <select>
      @param $subs [string, default 'subdepts'] name of the subdepartment multi <select>
      @return [string] HTML
    */
    public static function standardDepartmentFields($super='super',$multi='departments',$start='deptStart',$end='deptEnd', $subs='subdepts')
    {
        /**
          Precalculate options for superdept and dept selects
        */
        $dbc = FannieDB::getReadOnly(FannieConfig::config('OP_DB'));
        $def = $dbc->tableDefinition('superDeptNames');
        $superQ = 'SELECT superID, super_name FROM superDeptNames';
        if (isset($def['deleted'])) {
            $superQ .= ' WHERE deleted=0 ';
        }
        $superR = $dbc->query($superQ);
        $super_opts = '';
        while ($w = $dbc->fetchRow($superR)) {
            $super_opts .= sprintf('<option value="%d">%s</option>',
                $w['superID'], $w['super_name']) . "\n";
        }
        $deptR = $dbc->query('SELECT dept_no, dept_name FROM departments ORDER BY dept_no');
        $dept_opts = '';
        while ($w = $dbc->fetchRow($deptR)) {
            $dept_opts .= sprintf('<option value="%d">%d %s</option>',
                $w['dept_no'], $w['dept_no'], $w['dept_name']) . "\n";
        }

        /**
          Store javascript chaining function calls in variables
          the sub chaining one is repeated a bunch. The super chaining
          one depends which type of department <select>s are shown
          They're also ridiculously long argument lists.
        */
        $url = FannieConfig::config('URL');
        $chainsubs = "chainSubDepartments('{$url}ws/', {super_id:'#super-id', dept_start:'#dept-start-txt', dept_end:'#dept-end-txt', sub_multiple:'#subdepts'})";
        $onchange = "chainSuperDepartment('{$url}ws/', this.value, {dept_multiple:'#departments',dept_start:'#dept-start',dept_end:'#dept-end',dept_start_id:'#dept-start-txt',dept_end_id:'#dept-end-txt',callback:function(){ $chainsubs; }})";

        /**
          The rest of this method uses HEREDOC style strings with
          {{PLACEHOLDERS}} and substitutes PHP variables in after the
          fact. This is an uncommon coding style in the overall project
          but the HTML is easier to read
        */

        $ret = <<<HTML
<div class="form-group">
    <label class="col-sm-4 control-label">Super Department</label>
    <div class="col-sm-8">
        <select name="{{SUPER_FIELD_NAME}}" id="super-id" class="form-control" onchange="{{SUPER_ONCHANGE}};">
            <option value="">Select super department</option>
            {{SUPER_OPTS}}
            <option value="-2">All Retail</option><option value="-1">All</option>
        </select>
    </div>
</div>
HTML;
        $ret = str_replace('{{SUPER_FIELD_NAME}}', $super, $ret);
        $ret = str_replace('{{SUPER_ONCHANGE}}', $onchange, $ret);
        $ret = str_replace('{{SUPER_OPTS}}', $super_opts, $ret);

        $m_collapse = '';
        $p_collapse = '';
        $m_disabled = '';
        $p_disabled = '';
        if (FannieConfig::config('REPORT_DEPT_MODE') == 'multi') {
            $p_collapse = 'collapse';
            $p_disabled = 'disabled';
        } else {
            $m_collapse = 'collapse';
            $m_disabled = 'disabled';
        }
        $ret .= <<<HTML
<div class="form-group {{m_collapse}}" id="multi-dept">
    <label class="col-sm-4 control-label">
        <a href="" 
            onclick="$('#multi-dept :input').prop('disabled', true);$('.pair-dept :input').prop('disabled', false);$('#multi-dept').hide();$('.pair-dept').show();return false;">
            <span class="fas fa-sync"
                title="Choose range of departments"></span>
        </a>
        Department(s)
    </label>
    <div class="col-sm-8">
        <select id="departments" name="{{DEPT_MULTI}}[]" class="form-control" 
            multiple size="10" onchange="{{DEPT_ONCHANGE}};" {{m_disabled}}>';
            {{DEPT_OPTS}}
        </select>
    </div>
</div>
HTML;
        $ret = str_replace('{{DEPT_MULTI}}', $multi, $ret);
        $ret = str_replace('{{m_collapse}}', $m_collapse, $ret);
        $ret = str_replace('{{m_disabled}}', $m_disabled, $ret);

        $ret .= <<<HTML
<div class="form-group pair-dept {{p_collapse}}">
    <label class="col-sm-4 control-label">
        <a href="" 
            onclick="$('.pair-dept :input').prop('disabled', true);$('#multi-dept :input').prop('disabled', false);$('.pair-dept').hide();$('#multi-dept').show();return false;">
            <span class="fas fa-sync"
                title="Choose individual department(s)"></span>
        </a>
        Department Start
    </label>
    <div class="col-sm-6">
        <select id="dept-start" class="form-control" {{p_disabled}}
            onchange="$('#dept-start-txt').val(this.value); $('#dept-end').val(this.value); 
            $('#dept-end-txt').val(this.value); {{DEPT_ONCHANGE}};">
            {{DEPT_OPTS}}
        </select>
    </div>
    <div class="col-sm-2">
        <input type="text" name="{{DEPT_START}}" id="dept-start-txt" 
            onchange="$('#dept-start').val(this.value); $('#dept-end').val(this.value);
            $('#dept-end-txt').val(this.value); {{DEPT_ONCHANGE}};"
            class="form-control" value="1" {{p_disabled}} />
    </div>
</div>
<div class="form-group pair-dept {{p_collapse}}">
    <label class="col-sm-4 control-label">Department End</label>
    <div class="col-sm-6">
        <select id="dept-end" class="form-control" {{p_disabled}}
            onchange="$('#dept-end-txt').val(this.value); {{DEPT_ONCHANGE}};">
            {{DEPT_OPTS}}
        </select>
    </div>
    <div class="col-sm-2">
    <input type="text" name="{{DEPT_END}}" id="dept-end-txt" 
        onchange="$('#dept-end').val(this.value); {{DEPT_ONCHANGE}};"
        class="form-control" value="1" {{p_disabled}} />
    </div>
</div>
HTML;
        $ret = str_replace('{{DEPT_START}}', $start, $ret);
        $ret = str_replace('{{DEPT_END}}', $end, $ret);

        $ret = str_replace('{{DEPT_OPTS}}', $dept_opts, $ret);
        $ret = str_replace('{{DEPT_ONCHANGE}}', $chainsubs, $ret);
        $ret = str_replace('{{p_collapse}}', $p_collapse, $ret);
        $ret = str_replace('{{p_disabled}}', $p_disabled, $ret);

        $ret .= <<<HTML
<div class="form-group">
    <label class="col-sm-4 control-label">Sub Dept(s)</label>
    <div class="col-sm-8">
        <select id="subdepts" name="{{SUBS_NAME}}[]" class="form-control" multiple size="5">
        </select>
    </div>
</div>
HTML;
        $ret = str_replace('{{SUBS_NAME}}', $subs, $ret);

        return $ret;
    }

    /**
      Generate standard date fields with date_range_picker
    */
    public static function standardDateFields()
    {
        return '
        <div class="col-sm-5 form-horizontal">
            <div class="form-group">
                <label class="col-sm-4 control-label">Start Date</label>
                <div class="col-sm-8">
                    <input type="text" id="date1" name="date1" class="form-control date-field" 
                        autocomplete="off" required />
                </div>
            </div>
            <div class="form-group">
                <label class="col-sm-4 control-label">End Date</label>
                <div class="col-sm-8">
                    <input type="text" id="date2" name="date2" class="form-control date-field"
                        autocomplete="off" required />
                </div>
            </div>
            <div class="form-group">
                ' . self::date_range_picker() . '
            </div>
        </div>
        ';
    }

    /**
      Generate FROM and WHERE clauses with appropriate parameters
      and joins based on the standard form submissions.
      @return [keyed array]
      - query [string] from and where clauses
      - args [array] corresponding parameters
    */
    static public function standardItemFromWhere()
    {
        $op_db = FannieConfig::config('OP_DB');
        $dbc = FannieDB::getReadOnly($op_db);
        $start_date = self::getDate('date1', date('Y-m-d'));
        $end_date = self::getDate('date2', date('Y-m-d'));
        $dlog = DTransactionsModel::selectDlog($start_date, $end_date);
        $lookupType = self::get('lookup-type', 'dept');
        $store = self::get('store', false);
        $ignoreMemType = DTrans::memTypeIgnore($dbc);
        if ($store === false) {
            $store = COREPOS\Fannie\API\lib\Store::getIdByIp();
            if ($store === false) {
                $store = 0;
            }
        }

        $query = '
            FROM ' . $dlog . ' AS t 
                LEFT JOIN departments AS d ON t.department=d.dept_no
                ' . DTrans::joinProducts('t') . '
                LEFT JOIN MasterSuperDepts AS m ON t.department=m.dept_ID 
                LEFT JOIN subdepts AS b ON p.subdept=b.subdept_no
                LEFT JOIN vendors AS v ON p.default_vendor_id=v.vendorID
                LEFT JOIN vendorItems AS i ON p.upc=i.upc AND p.default_vendor_id=i.vendorID';
        $args = array();
        switch ($lookupType) {
            case 'dept':
                $super = FormLib::get('super-dept');
                if ($super !== '' && $super >= 0) {
                    $query .= ' LEFT JOIN superdepts AS s ON t.department=s.dept_ID ';
                }
                break;
            case 'manu':
                break;
            case 'likecode':
                $query .= ' LEFT JOIN upcLike AS u ON t.upc=u.upc ';
                break;
        }

        $query .= ' WHERE t.tdate BETWEEN ? AND ? ';
        $query .= ' AND t.memType NOT IN ' . $ignoreMemType;
        $args[] = $start_date . ' 00:00:00';
        $args[] = $end_date . ' 23:59:59';
        $query .= ' AND ' . DTrans::isStoreID($store, 't') . ' ';
        $args[] = $store;

        switch ($lookupType) {
            case 'dept':
                $super = FormLib::get('super-dept');
                if ($super !== '' && $super >= 0) {
                    $query .= ' AND s.superID=? ';
                    $args[] = $super;
                    if (is_array(FormLib::get('departments')) && count(FormLib::get('departments')) > 0) {
                        $query .= ' AND t.department IN (';
                        foreach (FormLib::get('departments') as $d) {
                            $query .= '?,';
                            $args[] = $d;
                        }
                        $query = substr($query, 0, strlen($query)-1) . ')';
                    } elseif (FormLib::get('dept-start') !== '' && FormLib::get('dept-end') !== '') {
                        $query .= ' AND t.department BETWEEN ? AND ? ';
                        $args[] = FormLib::get('dept-start');
                        $args[] = FormLib::get('dept-end');
                    }
                } elseif ($super !== '' && $super == -2) {
                    $query .= ' AND m.superID <> 0 ';
                    if (is_array(FormLib::get('departments')) && count(FormLib::get('departments')) > 0) {
                        $query .= ' AND t.department IN (';
                        foreach (FormLib::get('departments') as $d) {
                            $query .= '?,';
                            $args[] = $d;
                        }
                        $query = substr($query, 0, strlen($query)-1) . ')';
                    } elseif (FormLib::get('dept-start') !== '' && FormLib::get('dept-end') !== '') {
                        $query .= ' AND t.department BETWEEN ? AND ? ';
                        $args[] = FormLib::get('dept-start');
                        $args[] = FormLib::get('dept-end');
                    }
                } elseif ($super === '') {
                    if (is_array(FormLib::get('departments')) && count(FormLib::get('departments')) > 0) {
                        $query .= ' AND t.department IN (';
                        foreach (FormLib::get('departments') as $d) {
                            $query .= '?,';
                            $args[] = $d;
                        }
                        $query = substr($query, 0, strlen($query)-1) . ')';
                    } else {
                        $query .= ' AND t.department BETWEEN ? AND ? ';
                        $args[] = FormLib::get('dept-start', 1);
                        $args[] = FormLib::get('dept-end', 1);
                    }
                }
                if (is_array(FormLib::get('subdepts')) && count(FormLib::get('subdepts')) > 0) {
                    $query .= ' AND p.subdept IN (';
                    foreach (FormLib::get('subdepts') as $s) {
                        $query .= '?,';
                        $args[] = $s;
                    }
                    $query = substr($query, 0, strlen($query)-1) . ')';
                }
                break;
            case 'manu':
                $mtype = FormLib::get('mtype');
                if ($mtype == 'prefix') {
                    $query .= ' AND t.upc LIKE ? ';
                    $args[] = '%' . FormLib::get('manufacturer') . '%';
                } else {
                    $query .= ' AND (p.brand LIKE ?) ';
                    $manu = '%' . FormLib::get('manufacturer') . '%';
                    $args[] = $manu;
                    $optimizeP = $dbc->prepare('
                        SELECT p.department
                        FROM products AS p
                        WHERE p.brand LIKE ?
                        GROUP BY p.department');
                    $optimizeR = $dbc->execute($optimizeP, array($manu));
                    $dept_in = '';
                    while ($optimizeW = $dbc->fetch_row($optimizeR)) {
                        $dept_in .= '?,';
                        $args[] = $optimizeW['department'];
                    }
                    if ($dept_in !== '') {
                        $dept_in = substr($dept_in, 0, strlen($dept_in)-1);
                        $query .= ' AND t.department IN (' . $dept_in . ') ';
                    }
                }
                break;
            case 'vendor':
                $query .= ' AND (p.default_vendor_id=?) ';
                $vID = FormLib::get('vendor', 1);
                $args[] = $vID;
                $optimizeP = $dbc->prepare('
                    SELECT p.department
                    FROM products AS p
                    WHERE p.default_vendor_id=?
                    GROUP BY p.department');
                $optimizeR = $dbc->execute($optimizeP, array($vID));
                $dept_in = '';
                while ($optimizeW = $dbc->fetch_row($optimizeR)) {
                    $dept_in .= '?,';
                    $args[] = $optimizeW['department'];
                }
                if ($dept_in !== '') {
                    $dept_in = substr($dept_in, 0, strlen($dept_in)-1);
                    $query .= ' AND t.department IN (' . $dept_in . ') ';
                }
                break;
            case 'likecode':
                $query .= ' AND u.likeCode BETWEEN ? AND ? ';
                $args[] = FormLib::get('lc-start', 1);
                $args[] = FormLib::get('lc-end', 1);
                $optimizeP = $dbc->prepare('
                    SELECT p.department
                    FROM products AS p
                        INNER JOIN upcLike AS u ON p.upc=u.upc
                    WHERE u.likeCode BETWEEN ? AND ?
                    GROUP BY p.department');
                $optimizeR = $dbc->execute($optimizeP, array(FormLib::get('lc-start', 1), FormLib::get('lc-end', 1)));
                $dept_in = '';
                while ($optimizeW = $dbc->fetch_row($optimizeR)) {
                    $dept_in .= '?,';
                    $args[] = $optimizeW['department'];
                }
                if ($dept_in !== '') {
                    $dept_in = substr($dept_in, 0, strlen($dept_in)-1);
                    $query .= ' AND t.department IN (' . $dept_in . ') ';
                }
                break;
            case 'u':
                $upcs = FormLib::get('u', array());
                if (count($upcs) == 0) {
                    $upcs[] = 'NOTREALUPC';
                }
                $query .= ' AND t.upc IN (';
                foreach ($upcs as $u) {
                    $query .= '?,';
                    $args[] = BarcodeLib::padUPC($u);
                }
                $query = substr($query, 0, strlen($query)-1) . ') ';
                break;
        }

        return array('query'=>$query, 'args'=>$args);
    }

    /**
      Generate FROM and WHERE clauses with appropriate parameters
      and joins based on the standard form submissions.
      @param $form [ValueContainer]
      @return [keyed array]
      - query [string] from and where clauses
      - args [array] corresponding parameters
    */
    static public function queueableItemFromWhere($form)
    {
        $op_db = FannieConfig::config('OP_DB');
        $dbc = FannieDB::getReadOnly($op_db);
        try {
            $start_date = $form->date1;
            $end_date = $form->date2;
        } catch (Exception $ex) {
            $start_date = date('Y-m-d');
            $end_date = date('Y-m-d');
        }
        $dlog = DTransactionsModel::selectDlog($start_date, $end_date);
        $lookupType = $form->tryGet('lookup-type', 'dept');
        $store = $form->tryGet('store', false);
        $ignoreMemType = DTrans::memTypeIgnore($dbc);
        if ($store === false) {
            $store = COREPOS\Fannie\API\lib\Store::getIdByIp();
            if ($store === false) {
                $store = 0;
            }
        }

        $query = '
            FROM ' . $dlog . ' AS t 
                LEFT JOIN departments AS d ON t.department=d.dept_no
                ' . DTrans::joinProducts('t') . '
                LEFT JOIN MasterSuperDepts AS m ON t.department=m.dept_ID 
                LEFT JOIN subdepts AS b ON p.subdept=b.subdept_no
                LEFT JOIN vendors AS v ON p.default_vendor_id=v.vendorID
                LEFT JOIN vendorItems AS i ON p.upc=i.upc AND p.default_vendor_id=i.vendorID';
        $args = array();
        switch ($lookupType) {
            case 'dept':
                $super = $form->tryGet('super-dept');
                if ($super !== '' && $super >= 0) {
                    $query .= ' LEFT JOIN superdepts AS s ON t.department=s.dept_ID ';
                }
                break;
            case 'manu':
                break;
            case 'likecode':
                $query .= ' LEFT JOIN upcLike AS u ON t.upc=u.upc ';
                break;
        }

        $query .= ' WHERE t.tdate BETWEEN ? AND ? ';
        $query .= ' AND t.memType NOT IN ' . $ignoreMemType;
        $args[] = $start_date . ' 00:00:00';
        $args[] = $end_date . ' 23:59:59';
        $query .= ' AND ' . DTrans::isStoreID($store, 't') . ' ';
        $args[] = $store;

        switch ($lookupType) {
            case 'dept':
                $super = $form->tryGet('super-dept');
                if ($super !== '' && $super >= 0) {
                    $query .= ' AND s.superID=? ';
                    $args[] = $super;
                    if (is_array($form->tryGet('departments')) && count($form->tryGet('departments')) > 0) {
                        $query .= ' AND t.department IN (';
                        foreach ($form->tryGet('departments') as $d) {
                            $query .= '?,';
                            $args[] = $d;
                        }
                        $query = substr($query, 0, strlen($query)-1) . ')';
                    } elseif ($form->tryGet('dept-start') !== '' && $form->tryGet('dept-end') !== '') {
                        $query .= ' AND t.department BETWEEN ? AND ? ';
                        $args[] = $form->tryGet('dept-start');
                        $args[] = $form->tryGet('dept-end');
                    }
                } elseif ($super !== '' && $super == -2) {
                    $query .= ' AND m.superID <> 0 ';
                    if (is_array($form->tryGet('departments')) && count($form->tryGet('departments')) > 0) {
                        $query .= ' AND t.department IN (';
                        foreach ($form->tryGet('departments') as $d) {
                            $query .= '?,';
                            $args[] = $d;
                        }
                        $query = substr($query, 0, strlen($query)-1) . ')';
                    } elseif ($form->tryGet('dept-start') !== '' && $form->tryGet('dept-end') !== '') {
                        $query .= ' AND t.department BETWEEN ? AND ? ';
                        $args[] = $form->tryGet('dept-start');
                        $args[] = $form->tryGet('dept-end');
                    }
                } elseif ($super === '') {
                    if (is_array($form->tryGet('departments')) && count($form->tryGet('departments')) > 0) {
                        $query .= ' AND t.department IN (';
                        foreach ($form->tryGet('departments') as $d) {
                            $query .= '?,';
                            $args[] = $d;
                        }
                        $query = substr($query, 0, strlen($query)-1) . ')';
                    } else {
                        $query .= ' AND t.department BETWEEN ? AND ? ';
                        $args[] = $form->tryGet('dept-start', 1);
                        $args[] = $form->tryGet('dept-end', 1);
                    }
                }
                if (is_array($form->tryGet('subdepts')) && count($form->tryGet('subdepts')) > 0) {
                    $query .= ' AND p.subdept IN (';
                    foreach ($form->tryGet('subdepts') as $s) {
                        $query .= '?,';
                        $args[] = $s;
                    }
                    $query = substr($query, 0, strlen($query)-1) . ')';
                }
                break;
            case 'manu':
                $mtype = $form->tryGet('mtype');
                if ($mtype == 'prefix') {
                    $query .= ' AND t.upc LIKE ? ';
                    $args[] = '%' . $form->tryGet('manufacturer') . '%';
                } else {
                    $query .= ' AND (p.brand LIKE ?) ';
                    $manu = '%' . $form->tryGet('manufacturer') . '%';
                    $args[] = $manu;
                    $optimizeP = $dbc->prepare('
                        SELECT p.department
                        FROM products AS p
                        WHERE p.brand LIKE ?
                        GROUP BY p.department');
                    $optimizeR = $dbc->execute($optimizeP, array($manu));
                    $dept_in = '';
                    while ($optimizeW = $dbc->fetch_row($optimizeR)) {
                        $dept_in .= '?,';
                        $args[] = $optimizeW['department'];
                    }
                    if ($dept_in !== '') {
                        $dept_in = substr($dept_in, 0, strlen($dept_in)-1);
                        $query .= ' AND t.department IN (' . $dept_in . ') ';
                    }
                }
                break;
            case 'vendor':
                $query .= ' AND (p.default_vendor_id=?) ';
                $vID = $form->tryGet('vendor', 1);
                $args[] = $vID;
                $optimizeP = $dbc->prepare('
                    SELECT p.department
                    FROM products AS p
                    WHERE p.default_vendor_id=?
                    GROUP BY p.department');
                $optimizeR = $dbc->execute($optimizeP, array($vID));
                $dept_in = '';
                while ($optimizeW = $dbc->fetch_row($optimizeR)) {
                    $dept_in .= '?,';
                    $args[] = $optimizeW['department'];
                }
                if ($dept_in !== '') {
                    $dept_in = substr($dept_in, 0, strlen($dept_in)-1);
                    $query .= ' AND t.department IN (' . $dept_in . ') ';
                }
                break;
            case 'likecode':
                $query .= ' AND u.likeCode BETWEEN ? AND ? ';
                $args[] = $form->tryGet('lc-start', 1);
                $args[] = $form->tryGet('lc-end', 1);
                $optimizeP = $dbc->prepare('
                    SELECT p.department
                    FROM products AS p
                        INNER JOIN upcLike AS u ON p.upc=u.upc
                    WHERE u.likeCode BETWEEN ? AND ?
                    GROUP BY p.department');
                $optimizeR = $dbc->execute($optimizeP, array($form->tryGet('lc-start', 1), $form->tryGet('lc-end', 1)));
                $dept_in = '';
                while ($optimizeW = $dbc->fetch_row($optimizeR)) {
                    $dept_in .= '?,';
                    $args[] = $optimizeW['department'];
                }
                if ($dept_in !== '') {
                    $dept_in = substr($dept_in, 0, strlen($dept_in)-1);
                    $query .= ' AND t.department IN (' . $dept_in . ') ';
                }
                break;
            case 'u':
                $upcs = $form->tryGet('u', array());
                if (count($upcs) == 0) {
                    $upcs[] = 'NOTREALUPC';
                }
                $query .= ' AND t.upc IN (';
                foreach ($upcs as $u) {
                    $query .= '?,';
                    $args[] = BarcodeLib::padUPC($u);
                }
                $query = substr($query, 0, strlen($query)-1) . ') ';
                break;
        }

        return array('query'=>$query, 'args'=>$args);
    }

    /**
      Method gets a value from container or returns
      a default if the value does not exist
      @c [object] container for values
      @field [string] field name
      @default [mixed] default value
      @retun field value OR default value
    */
    public static function extract(\COREPOS\common\mvc\ValueContainer $c, $field, $default='')
    {
        try {
            return $c->$field;
        } catch (Exception $ex) {
            return $default;
        }
    }

    /**
      Convert a query string to a JSON object
      representing field names and values
    */
    public static function queryStringToJSON($str)
    {
        $ret = array();
        $pairs = explode('&', $str);
        foreach ($pairs as $pair) {
            if (substr($pair, -1) == '=') {
                $ret[substr($pair, 0, strlen($pair)-1)] = '';
            } elseif (strstr($pair, '=')) {
                list($key, $val) = explode('=', $pair, 2);
                $ret[$key] = $val;
            }
        }

        return json_encode($ret);
    }

    /**
      Generate javascript that will initialize fields
      based on names and values in the JSON object
    */
    public static function fieldJSONtoJavascript($json)
    {
        $arr = json_decode($json, true);
        if (!is_array($arr)) {
            return false;
        }
        return array_reduce(array_keys($arr), function($carry, $key) use ($arr) {
            $val = $arr[$key];
            $carry .= sprintf("if (\$('input[type=\"checkbox\"][name=\"%s\"]').length) {\n", $key);
            $carry .= sprintf("\$('input[type=\"checkbox\"][name=\"%s\"]').prop('checked',true);\n", $key);
            $carry .= "} else {\n";
            $carry .= sprintf("\$('[name=\"%s\"]').filter(':input').val('%s');\n", $key, $val);
            $carry .= "}\n";
            return $carry;
        }, '');
    }
}