data.i

<?php
// =================================
// PAGE 1: Monthly billing page
// =================================

function MonthlyBillingLoad($dbh, $isSimpleReport, $simpleReportMonth) {
    try {
       $warnings = array();
       $running = array();

       $monthStipulation = $isSimpleReport ? "where m.billing_date = '" . prep_save($simpleReportMonth) . "'"
                                           : "where m.billing_date > date_trunc('month', current_date) - interval '13 months'";
       $sql = "select pi.id \"preinvoiceId\", m.billing_date \"month\", m.started_date \"runDate\", m.last_step_date \"lastStep\",
               m.processing_flag \"step\", m.last_error_code \"status\", m.last_error_msg \"message\", pi.feature_detail_id \"featureBillingDetailId\",
               pi.description \"invoiceDescription\", pi.billing_system_id \"billingSystemItem\", pi.quantity \"quantity\", pi.amount \"amount\",
               trim(pi.cu_id) \"cu\", i.system_options \"options\", trim(i.name) \"cuName\",
               trim(coalesce(si.display_name, pi.billing_system_id)) \"featureDescription\",
               pi.sales_item_id \"salesItemId\", trim(coalesce(p.home_cu_desc, si.prod_id)) \"productDescription\"
               from cubillmonth m
               left join cubillpreinvoice pi on to_char(m.billing_date, 'YYYY-MM') = to_char(pi.billing_date, 'YYYY-MM')
               left join cuinfo i on lower(pi.cu_id) = lower(i.user_name)
               left join cubillsalesitem si on pi.sales_item_id = si.id
               left join cuprodlist p on trim(si.prod_id) = trim(p.home_cu_code)
               $monthStipulation order by m.billing_date desc, pi.cu_id, pi.feature_detail_id, pi.id";
        $sth = db_query($sql, $dbh);
        if (!$sth) {
            throw new exception ("Select query failed.", 1);
        }
        $queryResults = db_fetch_all($sth);
        $queryResults = $queryResults === false ? array() : $queryResults;

        $warnings = array();
        if (!$isSimpleReport) {
            // See if there are any pending processes and if so, make sure that process has not exited out due to error.
            $sql = "select t.billing_date \"month\"
                    from (select distinct pi.billing_date from cubillpreinvoice pi where pi.processing_flag > 0 and pi.processing_flag < 99) t
                    inner join cubillmonth m on to_char(t.billing_date, 'YYYY-MM') = to_char(m.billing_date, 'YYYY-MM')
                    and (m.last_error_msg is null or trim (from m.last_error_msg) = '')
                    order by t.billing_date desc";
            $sth = db_query($sql, $dbh);
            if (!$sth) {
                throw new exception ("Select query failed.", 2);
            }

            $results = db_fetch_all($sth);
            $running = $results === false ? array() : $results;

            $results = CheckForOverlapGaps($dbh, 0);
            if ($results["status"] !== "000") {
                throw new exception ($results["error"], 3);
            }
            $warnings = array_merge($warnings, $results["warnings"]);
        }

        $records = array();
        $cuTempArray = array();
        $invoiceTempArray = array();
        $totalAmountArray = array();
        $currentMonth = "";
        $currentCU = "";
        $monthChanged = false;
        $cuChanged = false;
        $noCU = false;
        $monthsInGrid = array();

        $countLabelsVariance = GetCountLabelVariance();
        $countLabels = GetCountLabels();
        $timetrackBillingItems = GetTimetrackBillingItems();
        foreach($queryResults as $record) {
            $monthChanged = false;
            $cuChanged = false;
            if ($currentMonth == "" || $currentMonth != $record["month"]) {
                $currentMonth = $record["month"];
                $monthsInGrid[] = $currentMonth;
                $monthChanged = true;
            }

            $noCU = $record["cu"] == "";
            if ($currentCU == "" || $currentCU != $record["cu"]) {
                $currentCU = $record["cu"];
                $cuChanged = true;
            }

            if ($monthChanged) {
                $step = intval($record["step"]);

                if ($step == 0) {
                    $mainStatus = "Not Started";
                } else if ($step < 100) {
                    $mainStatus = "Run Billing Started";
                } else if ($step == 100) {
                    $mainStatus = "Run Billing Finished";
                } else if ($step < 200) {
                    $mainStatus = "QBWC Started";
                } else if ($step == 200) {
                    $mainStatus = "QBWC Finished";
                } else {
                    $mainStatus = "???";
                }

                if ($step == 200) {
                    $completeDate = $record["lastStep"];
                } else {
                    $completeDate = "";
                }
                $records[] = array("month" => $currentMonth, "runDate" => $record["runDate"], "completeDate" => $completeDate, "mainStatus" => $mainStatus,
                    "status" => $record["status"], "message" => $record["message"]);
                $cuTempArray[$currentMonth] = array();
                $invoiceTempArray[$currentMonth] = array();
                $totalAmountArray[$currentMonth] = array();
            }

            if (!$noCU) { // No need to compile an empty detail grid underneath month
                if ($monthChanged || $cuChanged) {
                    $cuTempArray[$currentMonth][] = GetCuOptions($currentCU, $currentMonth, $record["options"], $record["cuName"]);
                    $invoiceTempArray[$currentMonth][$currentCU] = array();
                    $totalAmountArray[$currentMonth][$currentCU] = 0.0;
                }

                $totalAmountArray[$currentMonth][$currentCU] += floatval($record["amount"]);

                $billingId = $record["billingSystemItem"];
                $salesItemId = $record["salesItemId"];
                $featureDescription = $record["featureDescription"];
                $timetrackBillingItem = HCU_array_key_value(strtolower($billingId), $timetrackBillingItems);
                if (isset($countLabelsVariance[$billingId][$salesItemId])) {
                    $quantityLabel = $countLabelsVariance[$billingId][$salesItemId];
                } else if (isset($countLabels[$billingId])) {
                    $quantityLabel = $countLabels[$billingId];
                } else if ($timetrackBillingItem !== false) {
                    $quantityLabel = "Hours ($billingId)";
                    $featureDescription = $timetrackBillingItem;
                } else {
                    $quantityLabel = "";
                }
                $invoiceTempArray[$currentMonth][$currentCU][] = array("featureBillingDetailId" => intval($record["featureBillingDetailId"]),
                                                                       "salesItemId" => $record["salesItemId"],
                                                                       "invoiceDescription" => $record["invoiceDescription"],
                                                                       "quantity" => floatval($record["quantity"]), "quantityLabel" => $quantityLabel,
                                                                       "amount" => floatval($record["amount"]), "preInvoiceId" => $record["preinvoiceId"],
                                                                       "featureDescription" => $featureDescription,
                                                                       "productDescription" => $record["productDescription"]);
            }
        }

        foreach($cuTempArray as $month => $array) {
            for ($i = 0, $count = count($array); $i != $count; $i++) {
                $cu = $array[$i]["cu"];
                $array[$i]["invoices"] = $invoiceTempArray[$month][$cu];
                $array[$i]["totalAmount"] = $totalAmountArray[$month][$cu];
            }
            $cuTempArray[$month] = $array;
        }

        for($i = 0, $count = count($records); $i != $count; $i++) {
            $thisMonth = $records[$i]["month"];
            $records[$i]["cus"] = $cuTempArray[$thisMonth];
        }

        $returnArray = array("status" => "000", "error" => array(), "record" => $records, "operation" => "read", "running" => $running, "warnings" => $warnings);
    } catch (exception $e) {
        $returnArray = array("status" => $e->getCode(), "error" => array($e->getMessage()));
    }
    return $returnArray;
}

function CheckForOverlapGaps($dbh, $contractId) {
    try {
        $warnings = array();
        $needsOverlapCheck = GetNeedsOverlapCheck();
        $where = array("cd.prod_id in ('" . implode("', '", $needsOverlapCheck["check"]) . "')", "si.id not in (" . implode(", ", $needsOverlapCheck["not"]) . ")");
        if ($contractId != 0) {
            $where[] = "cd.sales_order_id = " . intval($contractId);
            $where[] = "fd.billing_status = 1 or (fd.billing_status = 2 and to_char(fd.start_date, 'YYYY-MM') <= to_char(now(), 'YYYY-MM')
                        and (fd.end_date is null or to_char(fd.end_date, 'YYYY-MM') >= to_char(now(), 'YYYY-MM')))";
        } else {
            // Called from monthly billing page.  Here what is missing in the pending records isn't relevant.
            $where[] = "fd.billing_status = 2 and to_char(fd.start_date, 'YYYY-MM') <= to_char(now(), 'YYYY-MM')
                        and (end_date is null or to_char(fd.end_date, 'YYYY-MM') >= to_char(now(), 'YYYY-MM'))";
        }

        $sql = "select trim(cd.prod_id) \"prodId\", fd.billing_template \"template\", trim(si.display_name) \"displayName\", fd.id \"id\", trim(c.cu_id) \"cu\",
                    fd.billing_status \"status\", fd.sales_item_id \"salesId\", trim(si.billing_system_id) \"billingId\",
                    trim(coalesce(p.home_cu_desc, si.prod_id)) \"prodName\", trim(coalesce(i.name, c.cu_id)) \"cuName\"
                from cubillsalesorderdetail cd
                inner join cubillfeaturedetail fd on cd.id = fd.sales_order_detail_id
                inner join cubillsalesorder c on cd.sales_order_id = c.id
                inner join cubillsalesitem si on fd.sales_item_id = si.id
                left join cuprodlist p on si.prod_id = p.home_cu_code
                left join cuinfo i on lower(c.cu_id) = lower(i.user_name) ";
        if (count($where) != 0) {
            $sql .= "where (" . implode(") and (", $where) . ") ";
        }
        $sql .= "order by c.cu_id, cd.prod_id, fd.billing_status";

        $sth = db_query($sql, $dbh);
        if (!$sth) {
            throw new exception ("Select query failed.", 1);
        }

        $queryResults = db_fetch_all($sth);
        $queryResults = $queryResults === false ? array() : $queryResults;


        $currentProd = "";
        $currentProdName = "";
        $prods = array();
        $cus = array();
        $currentCU = "";
        $currentCUName = "";
        $masterIntervals = array();
        $intervals = array();
        $currentStatus = -1;

        $setupRecurring = GetSetupRecurring();

        foreach($queryResults as $record) {
            if (isset($setupRecurring[$record["billingId"]]) && $setupRecurring[$record["billingId"]] != "recurring") {
                continue; // Calculation is only valid for recurring product features.
            }

            if ($contractId == 0 && ($currentCU == "" || $currentCU != $record["cu"])) {
                $currentCU = $record["cu"];
                $currentCUName = $record["cuName"];
                $cus[$currentCU] = $currentCUName;
                $masterIntervals[$currentCU] = array();
                $intervals = $masterIntervals[$currentCU];
            }

            if ($currentProd == "" || $currentProd != $record["prodId"]) {
                $currentProd = $record["prodId"];
                $currentProdName = $record["prodName"];
                $prods[$currentProd] = $currentProdName;
                $currentStatus = $record["status"];
                $intervals[$currentProd] = array();
                $intervals[$currentProd][$currentStatus] = array();
            } else if ($currentStatus == -1 || $currentStatus != $record["status"]) {
                $intervals[$currentProd][$currentStatus] = array();
            }

            $decodedTemplate = HCU_JsonDecode($record["template"]);
            $aStart = intval($decodedTemplate["qty1"]);
            $aEnd = intval($decodedTemplate["qty2"]);

            $overlapsI = -1;
            $extendsIs = array();
            $theseIntervals = HCU_array_key_value($currentProd, $intervals);
            $theseIntervals = $theseIntervals === false ? array() : HCU_array_key_value($currentStatus, $theseIntervals);
            $theseIntervals = $theseIntervals === false ? array() : $theseIntervals;
            for($i = 0, $count = count($theseIntervals); $i != $count; $i++) {
                $bStart = intval($theseIntervals[$i]["start"]);
                $bEnd = intval($theseIntervals[$i]["end"]);

                $overlaps = false;
                $thisExtends = false;

                if ($aEnd == -1) {
                    if ($bEnd == -1) {
                        $overlaps = true;
                    } else {
                        $overlaps = $bEnd > $aStart;
                        $thisExtends = $aStart == $bEnd || $aStart == $bEnd + 1;
                    }
                } else {
                    if ($bEnd == -1) {
                        $overlaps = $bStart < $aEnd;
                        $thisExtends = $aEnd == $bStart || $aEnd + 1 == $bStart;
                    } else {
                        $overlaps = ($bStart > $aStart && $bStart < $aEnd) || ($bEnd > $aStart && $bEnd < $aEnd);
                        $thisExtends = $aStart == $bEnd || $aStart == $bEnd + 1 || $aEnd == $bStart || $aEnd + 1 == $bStart;
                    }
                }
                if ($overlaps) {
                    $overlapsI = $i;
                    break;
                } else if ($thisExtends) {
                    $extendsIs[] = $i;
                }
            }

            if ($overlapsI != -1) {
                $pendingActive = $currentStatus == 1 ? "pending" : "active";

                if ($contractId == 0) {
                    if (!isset($warnings[$currentCU])) {
                        $warnings[$currentCU] = array();
                    }
                    if (!isset($warnings[$currentCU][$currentProd])) {
                        $warnings[$currentCU][$currentProd] = array();
                    }
                    $warnings[$currentCU][$currentProd][] = "$currentCUName ($currentCU): " . $record["displayName"] . " overlaps with another
                                                             $pendingActive $currentProdName record.";
                } else {
                    if (!isset($warnings[$currentProd])) {
                        $warnings[$currentProd] = array();
                    }
                    $warnings[$currentProd][] = $record["displayName"] . " overlaps with another $pendingActive $currentProdName record.";
                }

            } else if (count($extendsIs) != 0) {
                $start = $aStart;
                $end = $aEnd;
                for($i = count($extendsIs) - 1; $i != -1; $i--) {
                    $index = $extendsIs[$i];
                    $bStart = $theseIntervals[$index]["start"];
                    $bEnd = $theseIntervals[$index]["end"];
                    if ($bStart < $aStart) {
                        $start = intval($bStart);
                    }
                    if ($end == -1 || $bEnd == -1) {
                        $end = -1;
                    } else if ($bEnd > $end) {
                        $end = intval($bEnd);
                    }
                    array_splice($theseIntervals, $index, 1);
                }
                $theseIntervals[] = array("start" => $start, "end" => $end);
            } else {
                $theseIntervals[] = array("start" => $aStart, "end" => $aEnd);
            }

            // Setting a variable to a subset of an array in PHP doesn't change the original array.  Thus this is needed.
            $intervals[$currentProd][$currentStatus] = $theseIntervals;
            if ($contractId == 0) {
                $masterIntervals[$currentCU] = $intervals;
            }
        }

        if ($currentCU != "") { // In the case, that there are no records found.
            if ($contractId != 0) {
                $results = CheckForOverlapGapsEnd($intervals, $contractId, $currentCU, $warnings, $prods, $cus);
                if ($results["status"] !== "000") {
                    throw new exception ($results["error"], 2);
                }
                $warnings = $results["warnings"];
            } else {
                foreach($masterIntervals as $currentCU => $intervals) {
                    $results = CheckForOverlapGapsEnd($intervals, $contractId, $currentCU, $warnings, $prods, $cus);
                    if ($results["status"] !== "000") {
                        throw new exception ($results["error"], 2);
                    }
                    $warnings = $results["warnings"];
                }
            }
        }


        $returnArray = array("status" => "000", "error" => "", "warnings" => $warnings);
    } catch (exception $e) {
        $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage(), "warnings" => $warnings);
    }
    return $returnArray;
}

function CheckForOverlapGapsEnd($intervals, $contractId, $cu, $warnings, $prods, $cus) {
    try {
        $cuName = HCU_array_key_value($cu, $cus);
        foreach($intervals as $prodKey => $records) {
            $prodName = HCU_array_key_value($prodKey, $prods);
            foreach($records as $currentStatus => $theseRecords) {
                $pendingActive = $currentStatus == 1 ? "pending" : "active";
                if (!(count($theseRecords) == 1 && $theseRecords[0]["start"] == 0 && $theseRecords[0]["end"] == -1)) {
                    // If the calculation is from the monthly billing page, then we also need the name of the CU.
                    if ($contractId == 0) {
                        if (!HCU_array_key_exists($cu, $warnings)) {
                            $warnings[$cu] = array();
                        }
                        if (!HCU_array_key_exists($prodKey, $warnings[$cu])) {
                            $warnings[$cu][$prodKey] = array("$cuName ($cu): $prodName is not contiguous from 0 to &infin; for $pendingActive records.");
                        }
                    } else {
                        if (!HCU_array_key_exists($prodKey, $warnings)) {
                            $warnings[$prodKey] = array("$prodName is not contiguous from 0 to &infin; for $pendingActive records.");
                        }
                    }
                }
            }
        }

        $returnArray = array("status" => "000", "error" => "", "warnings" => $warnings);
    } catch (exception $e) {
        $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage(), "warnings" => $warnings);
    }
    return $returnArray;
}

function GetCuOptions($currentCU, $currentMonth, $options, $cuName) {
    // Borrowed from cu_top
    $SYS_TYPE_BATCH = 1;
    $SYS_TYPE_LIVE = 2;
    $SYS_TYPE_WEBONLY = 16;

    $isBatch = ($options & $SYS_TYPE_BATCH) != 0;
    $isLive = ($options & $SYS_TYPE_LIVE) != 0;
    $isWebOnly = ($options & $SYS_TYPE_WEBONLY) != 0;
    $type = $isWebOnly ? "Web Only" : ($isLive ? "Live" : "Batch");

    return array("cu" => $currentCU, "month" => $currentMonth, "type" => $type, "cuName" => $cuName);
}

function MonthReport($dbh, $sysenv, $asCSV) {
    try {
        extract($sysenv["BILL"]);
        $monthFormat = "Y-m-d|";
        $month = $reportDate;
        if (!isset($month) || trim($month) == "") {
          throw new exception ("Month is required.", 1);
        }
        if (!DateTime::createFromFormat($monthFormat, $month)) {
          throw new exception ("Month is invalid.", 2);
        }

        $returnArray = MonthlyBillingLoad($dbh, true, $month);
        if ($returnArray["status"] !== "000") {
            throw new exception ($returnArray["error"], 3);
        }

        if ($asCSV) {
            $csvData = array();
            $monthRecord = $returnArray["record"][0];

            $dateTime = new DateTime($monthRecord["month"]);
            $month = $dateTime->format("F Y");
            $monthFile = $dateTime->format("M_Y");

            $sql = "select id, display_name from cubillsalesitem order by display_name";
            $sth = db_query($sql, $dbh);
            if (!$sth) {
                throw new exception("display query failed.", 3);
            }
            $displayMap = array();
            for($i = 0; $row = db_fetch_assoc($sth, $i); $i++) {
                $displayMap[intval($row["id"])] = trim($row["display_name"]);
            }

            $csvData[] = "CU,Name," . implode(",", $displayMap) . ",Total";

            if (isset($monthRecord["cus"])) {
                foreach($monthRecord["cus"] as $cuRecord) {
                    $cuRow = array($cuRecord["cu"], $cuRecord["cuName"]);
                    $totalFormatted = $cuRecord["totalAmount"] < 0 ? "\$(" . number_format(abs($cuRecord["totalAmount"]), 2) . ")"
                                                                   : "\$" . number_format($cuRecord["totalAmount"], 2);
                    $cuFeatureMap = array();

                    foreach($cuRecord["invoices"] as $invoiceRecord) {
                        $key = intval($invoiceRecord["salesItemId"]);
                        if (!isset($cuFeatureMap[$key])) {
                           $cuFeatureMap[$key] = 0;
                        }
                        $cuFeatureMap[$key] += $invoiceRecord["amount"];
                    }

                    foreach($cuFeatureMap as $key => $value) {
                        $cuFeatureMap[$key] = $value < 0 ? "\"\$(" . number_format(abs($value), 2) . ")\"" : "\"\$" . number_format($value, 2) . "\"";
                    }

                    foreach($displayMap as $id => $text) {
                        $cuRow[] = isset($cuFeatureMap[$id]) ? $cuFeatureMap[$id] : "";
                    }
                    $cuRow[] = "\"$totalFormatted\"";
                    $csvData[] = implode(",", $cuRow);
                }
            }
            $csvData = implode("\r\n", $csvData);

            header("Content-length: " . strlen($csvData) );
            header("Content-type: application/octetstream");
            header("Content-disposition: inline; filename=\"monthlyReport_$monthFile.csv\"");
            print ($csvData);

            exit;
        }
    } catch (exception $e) {
        $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage());
    }
    return $returnArray;
}

function GetMonthlyProgressBars($dbh, $sysenv) {
    try {
        extract($sysenv["BILL"]);

        if ($reportDate != "") {
            $monthSQLPart = "and pi.billing_date = '" . prep_save($reportDate) . "' ";
        } else {
            $monthSQLPart = "";
        }

        // Gets a division of preinvoice by month and processed number if there is at least one record that is in a running state for QWBC.
        // The time from state 25 to 99 is miminal.
        $sql = "select pi.billing_date \"month\", pi.processing_flag \"flag\", count(*) \"count\"
                           from cubillpreinvoice pi
                           where pi.processing_flag >= 0 and pi.processing_flag <= 25 and exists (
                               select 'FOUND' from cubillpreinvoice i where i.processing_flag > 0 and i.processing_flag < 25
                                   and pi.billing_date = i.billing_date) $monthSQLPart
                           group by pi.billing_date, pi.processing_flag order by pi.billing_date desc, pi.processing_flag";

        $sth = db_query($sql, $dbh);
        if (!$sth) {
            throw new exception ("Select query failed.", 2);
        }
        $queryResults = db_fetch_all($sth);
        $queryResults = $queryResults === false ? array() : $queryResults;

        $averages = array();
        $resultsArray = array();

        foreach($queryResults as $record) {
            $month = $record["month"];
            $count = intval($record["count"]);
            $weight = intval($record["flag"]);
            if (!HCU_array_key_exists($month, $averages)) {
                $averages[$month] = array("num" => 0, "denom" => 0);
            }
            $averages[$month]["num"] += $count * $weight;
            $averages[$month]["denom"] += $count;
        }
        foreach($averages as $month => $record) {
            $weightedAverage = ($record["num"] / $record["denom"]);
            $weightedAverage = $weightedAverage / 25.0;
            $resultsArray[] = array("month" => $month, "progress" => $weightedAverage);
        }

        $returnArray = array("status" => "000", "error" => "", "record" => $resultsArray);
    } catch (exception $e) {
        $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage());
    }
    return $returnArray;
}

// =================================
// END PAGE 1: Monthly billing page
// =================================

// =================================
// PAGE 2: Sales Order
// =================================

function SalesOrderLoad($dbh, $sysenv, $Hu) {
    try {
        extract($sysenv["BILL"]);
        global $SYS_TYPE_CLOSED;
        $billingEmailFlag = GetBillingEmailFlag();
        $initializeDDLs = isset($initializeDDLs) ? $initializeDDLs == "Y" : false;

        $sql = "select so.id \"salesOrderId\", so.cu_id \"cuNumber\", so.date \"date\", so.reference \"refNo\", so.staff_id \"employee\",
                    so.flags & $billingEmailFlag \"emailable\", i.name \"cuName\", i.system_options & $SYS_TYPE_CLOSED \"isClosed\"
                from cubillsalesorder so inner join cuinfo i on upper(so.cu_id) = upper(i.user_name) order by so.cu_id";
        $sth = db_query($sql, $dbh);
        if (!$sth) {
            throw new exception ("Select query failed.", 1);
        }
        $queryResults = db_fetch_all($sth);
        $queryResults = $queryResults === false ? array() : $queryResults;

        foreach($queryResults as $i => $record) {
            $record["emailable"] = $record["emailable"] != 0;
            $record["isClosed"] = $record["isClosed"] != 0;
            $queryResults[$i] = $record;
        }

        $cuList = array();
        if ($initializeDDLs) {
            $sql = "select i.user_name \"value\", i.name \"text\", i.system_options & $SYS_TYPE_CLOSED \"isClosed\" from cuinfo i order by i.user_name";
            $sth = db_query($sql, $dbh);
            if (!$sth) {
                throw new exception ("Select query failed.", 2);
            }
            $cuList = db_fetch_all($sth);
            $cuList = $cuList === false ? array() : $cuList;

            foreach($cuList as $i => $record) {
                $record["isClosed"] = $record["isClosed"] != 0;
                $cuList[$i] = $record;
            }
        }

        $returnArray = array("status" => "000", "error" => "", "record" => $queryResults, "cuList" => $cuList, "operation" => "read", "currentEmployeeId" => $Hu);
    } catch (exception $e) {
        $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage(), "operation" => "read");
    }
    return $returnArray;
}

function SalesOrderCreateOrUpdate($dbh, $Hu, $isCreate, $sysenv) {
    try {
        extract($sysenv["BILL"]);
        $billingEmailFlag = GetBillingEmailFlag();
        if (!isset($salesOrderId) || trim($salesOrderId) == "") {
            throw new exception ("sales Order Id is required.", 1);
        }
        $salesOrderId = intval($salesOrderId);
        $cuNumber = isset($cuNumber) ? trim(strtoupper($cuNumber)) : "";
        if (!isset($cuNumber) || trim($cuNumber) == "") {
            throw new exception ("Cu number is required.", 2);
        }
        $reference = isset($refNo) ? trim($refNo) : "";
        $emailable = isset($emailable) ? trim($emailable) == "Y" : false;
        $emailableSQL = $emailable ? ($isCreate ? $billingEmailFlag : "flags | $billingEmailFlag") : ($isCreate ? 0 : "flags & ~$billingEmailFlag");

        $sql = "select name from cuinfo where lower(user_name) = '" . prep_save(strtolower($cuNumber), 12) . "'";
        $sth = db_query($sql, $dbh);
        if (!$sth) {
            throw new exception ("Select query failed.", 3);
        }
        if (db_num_rows($sth) <= 0) {
            throw new exception ("No Credit Union exists.", 4);
        }
        $cuName = trim(db_fetch_row($sth, 0)[0]);

        if ($isCreate) {
            $dateTime = new DateTime();
            $date = $dateTime->format("Y-m-d");
            $sql = "insert into cubillsalesorder (cu_id, date, staff_id, reference, flags)
                    values ('" . prep_save($cuNumber, 10) . "', '" . prep_save($date) . "', '" . prep_save($Hu, 10) . "', '" . prep_save($reference, 20) . "',
                    $emailableSQL)
                    returning id \"salesOrderId\", cu_id \"cuNumber\", date \"date\", reference \"refNo\",
                        staff_id \"employee\", flags & $billingEmailFlag \"emailable\", '$cuName' \"cuName\"";


        } else {
            $sql = "update cubillsalesorder set cu_id = '" . prep_save($cuNumber, 10) . "', reference = '" . prep_save($reference, 20) . "',
                    flags = $emailableSQL where id = $salesOrderId
                    returning id \"salesOrderId\", cu_id \"cuNumber\", date \"date\", reference \"refNo\",
                        staff_id \"employee\", flags & $billingEmailFlag \"emailable\", '$cuName' \"cuName\"";
        }

        $sth = db_query($sql, $dbh);
        if (!$sth) {
            throw new exception ("Upsert query failed.", 5);
        }
        $queryResults = db_fetch_assoc($sth, 0);
        $queryResults["emailable"] = $queryResults["emailable"] != 0;

        $returnArray = array("status" => "000", "error" => "", "record" => $queryResults, "operation" => $isCreate ? "add" : "update");

    } catch (exception $e) {
        $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage(), "operation" => $isCreate ? "add" : "update");
    }
    return $returnArray;
}

function GetCuView($dbh, $sysenv) {
    try {
        extract($sysenv["BILL"]);
        global $SYS_TYPE_BATCH, $SYS_TYPE_LIVE, $SYS_TYPE_WEBONLY, $SYS_TYPE_CLOSED;
        $cu = isset($cu) ? strtoupper(trim($cu)) : "";
        if ($cu == "") {
            throw new exception ("Cu is required.", 1);
        }

        $initializeDDLs = isset($initializeDDLs) ? trim($initializeDDLs) == "Y" : false;

        $sql = "select trim(ci.user_name) \"cu\", so.date \"contractEnteredDate\", trim(ci.name) \"cuName\", ci.system_options \"options\",
                    fd.start_date \"fdStartDate\", fd.end_date \"fdEndDate\", fd.billing_template \"template\", fd.sales_item_id \"salesItemId\",
                    trim(si.billing_system_id) \"billingSystemId\", trim(si.display_name) \"salesItemDescr\", fd.billing_status \"billingStatus\",
                    fd.bill_half_boolean \"billHalf\", fd.id \"featureDetailId\", sod.prod_id \"prodId\", so.id \"salesOrderId\",
                    sod.id \"orderDetailId\", fd.partially_billed_date \"partiallyBilledDate\",
                    exists (select 'FOUND' from cubillpreinvoice pi where fd.id = pi.feature_detail_id) \"linkExists\",
                    fd.partially_billed_amount \"partiallyBilledAmount\"
                from (select i.user_name, i.system_options, i.name from cuinfo i where upper(i.user_name) = '" . prep_save($cu, 10) . "') ci
                left join cubillsalesorder so on upper(ci.user_name) = upper(so.cu_id)
                left join cubillsalesorderdetail sod on so.id = sod.sales_order_id
                left join cubillfeaturedetail fd on sod.id = fd.sales_order_detail_id
                left join cubillsalesitem si on fd.sales_item_id = si.id
                order by si.display_name";
        $sth = db_query($sql, $dbh);
        if (!$sth) {
            throw new exception ("Select query failed.", 1);
        }
        $queryResults = db_fetch_all($sth);
        $queryResults = $queryResults === false ? array() : $queryResults;

        $first = true;
        $topLine = array();
        $itemLines = array();

        $setupRecurring = GetSetupRecurring();
        foreach($queryResults as $record) {
            // Create top portion
            if ($first) {
                // Extract type and is closed from system options
                $options = $record["options"];

                $isBatch = ($options & $SYS_TYPE_BATCH) != 0;
                $isLive = ($options & $SYS_TYPE_LIVE) != 0;
                $isWebOnly = ($options & $SYS_TYPE_WEBONLY) != 0;
                $isClosed = ($options & $SYS_TYPE_CLOSED) != 0;
                $type = $isWebOnly ? "Web Only" : ($isLive ? "Live" : "Batch");
                $closed = $isClosed ? "Yes" : "No";

                $topLine[] = array("cu" => $record["cu"], "cuName" => $record["cuName"], "type" => $type, "isClosed" => $isClosed,
                                   "salesOrderId" => $record["salesOrderId"]);
                $first = false;
            }

            // Create line items
            if ($record["featureDetailId"] != "") {
                // Extract template and then get description.
                $expandedTemplate = HCU_JsonDecode($record["template"]);
                $salesItemId = intval($record["salesItemId"]);
                $billingId = $record["billingSystemId"];
                $fixed = floatval($expandedTemplate["fixed"]);
                $variable = floatval($expandedTemplate["variable"]);
                $qty1 = intval($expandedTemplate["qty1"]);
                $qty2 = intval($expandedTemplate["qty2"]);
                $aDNID = HCU_array_key_exists("aDNID", $expandedTemplate) ? $expandedTemplate["aDNID"] : "";
                $originalDescription = $expandedTemplate["descr"] == null || $expandedTemplate["descr"] == "" ? $record["salesItemDescr"]
                                                                                                              : $expandedTemplate["descr"];

                $description = GetTextDescription($salesItemId, $billingId, $fixed, $variable, $qty1, $qty2, $aDNID);
                $setupLabel = $setupRecurring[$record["billingSystemId"]];
                $setup = $setupLabel == "setup";
                if (!isset($setup)) {
                    $setup = false;
                }
                $results = GetBillingStatusLabel(intval($record["billingStatus"]), $record["fdStartDate"], $record["fdEndDate"], $setupLabel);
                if ($results["status"] !== "000") {
                    throw new exception ($results["error"], 2);
                }
                extract($results["data"]);
                $billHalf = $record["billHalf"] != 0;
                $billHalfLabel = $billHalf ? "Yes" : "No";
                $initialRecurringCosts = GetInitialRecurringCosts($salesItemId, $billingId, $fixed, $variable, $qty1, $qty2);

                $results = GetHalfBillingStatus($setup, $billHalf, $record["partiallyBilledDate"], $record["fdStartDate"]);
                if ($results["status"] !== "000") {
                    throw new exception ($results["error"], 3);
                }
                extract($results["data"]);

                if ($record["linkExists"] == "t") {
                    $deletable = "N";
                } else {
                    $dateTime = new DateTime ($record["fdStartDate"]);
                    $firstOfMonth = new DateTime();
                    $firstOfMonth->modify("first day of this month");
                    $interval = $dateTime->diff($firstOfMonth);
                    $dateDiff = $interval->format('%R%a');
                    if ($dateDiff <= 0) {
                        $deletable = "Y";
                    } else {
                        $deletable = "N";
                    }
                }

                $itemLines[] = array("featureDetailId" => $record["featureDetailId"], "textDescription" => $description, "statusLabel" => $statusLabel,
                                     "billHalf" => $billHalf, "initialCosts" => $initialRecurringCosts[0], "recurringCosts" => $initialRecurringCosts[1],
                                     "qty1" => $qty1, "qty2" => $qty2, "salesItemId" => intval($record["salesItemId"]), "linkExists" => $record["linkExists"],
                                     "salesItemDescr" => $record["salesItemDescr"], "billingStatus" => intval($record["billingStatus"]), "fixed" => $fixed,
                                     "variable" => $variable, "prodId" => $record["prodId"], "setup" => $setup, "originalDescription" => $originalDescription,
                                     "actualDescription" => $record["salesItemDescr"], "description" => "", "deletable" => $deletable,
                                     "orderDetailId" => intval($record["orderDetailId"]), "templateId" => $record["featureDetailId"],
                                     "billingStatusGroup" => $billingStatusGroup, "partiallyBilledDate" => $record["partiallyBilledDate"],
                                     "partiallyBilledAmount" => $record["partiallyBilledAmount"]);
            }
        }

        $returnArray = array("status" => "000", "error" => "", "top" => $topLine, "bottom" => $itemLines);
    } catch (exception $e) {
        $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage(), "top" => array(), "bottom" => array());
    }
    return $returnArray;
}

function GetProductsWithSetups($dbh) {
    try {
        $setups = array();
        $productsWithSetupOptions = array();
        $setupRecurring = GetSetupRecurring();
        foreach($setupRecurring as $billingItem => $setupLabel) {
            if ($setupLabel == "setup")
                $setups[] = $billingItem;
        }

        if (count($setups) > 0) {
            $sql = "select id \"id\", billing_template \"template\" from cubillsalesitem where billing_system_id in ('" . implode("', '", $setups) . "')";
            $sth = db_query($sql, $dbh);
            if (!$sth) {
                throw new exception ("Select query failed.", 1);
            }
            $queryResults = db_fetch_all($sth);
            $queryResults = $queryResults === false ? array() : $queryResults;

            $setupsWithOptions = array();

            foreach($queryResults as $record) {
                $template = HCU_JsonDecode($record["template"]);
                if (intval($template["fixed"]) != 0) {
                    $setupsWithOptions[$record["id"]] = true;
                }
            }

            $productsWithSetupOptions = array();
            $productAdds = GetAutomaticProductAdds();
            foreach($productAdds as $product => $configurations) {
                $hasNonZeroSetups = false;
                foreach($configurations as $configuration) {
                    if (HCU_array_key_exists($configuration["salesItemId"], $setupsWithOptions)) {
                        $hasNonZeroSetups = true;
                        break;
                    }
                }

                $productsWithSetupOptions[$product] = $hasNonZeroSetups;
            }
        }

        $returnArray = array("status" => "000", "error" => "", "productsWithSetupOptions" => $productsWithSetupOptions);

    } catch (exception $e) {
        $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage());
    }
    return $returnArray;
}

function MostExpensiveCmpFunc($a, $b, $divideByCus = false) {
    if ($divideByCus) {
        $aCu = strtolower($a["cu"]);
        $bCu = strtolower($b["cu"]);
        if ($aCu < $bCu) {
            return -1;
        } else if ($aCu > $bCu) {
            return 1;
        }
    }

    $aFixed = $a["fixed"];
    $aVariable = $a["variable"];
    $bFixed = $b["fixed"];
    $bVariable = $b["variable"];

    if ($aFixed > $bFixed) {
        return -1;
    } else if ($aFixed < $bFixed) {
        return 1;
    }

    if ($aVariable > $bVariable) {
        return -1;
    } else if ($aVariable < $bVariable) {
        return 1;
    } else {
        return 0;
    }
}

function MostExpensiveCmpLayeredFunc($a, $b) {
    $aFixed = HCU_array_key_value("fixed", $a["featureDetailList"][0]);
    $aVariable = HCU_array_key_value("variable", $a["featureDetailList"][0]);
    $bFixed = HCU_array_key_value("fixed", $b["featureDetailList"][0]);
    $bVariable = HCU_array_key_value("variable", $b["featureDetailList"][0]);
    if (!isset($aFixed)) {
        $aFixed = -1;
    }
    if (!isset($aVariable)) {
        $aVariable = -1;
    }
    if (!isset($bFixed)) {
        $bFixed = -1;
    }
    if (!isset($bVariable)) {
        $bVariable = -1;
    }

    if ($aFixed > $bFixed) {
        return -1;
    } else if ($aFixed < $bFixed) {
        return 1;
    }

    if ($aVariable > $bVariable) {
        return -1;
    } else if ($aVariable < $bVariable) {
        return 1;
    } else {
        return 0;
    }
}

function ByProductAndSetupCmpFunc($a, $b, $sortedProdArray, $dividedByBillingStatusGroup = false, $dividedByCus = false) {
    if ($dividedByBillingStatusGroup) {
        $aBillingStatusGroup = intval($a["billingStatusGroup"]);
        $bBillingStatusGroup = intval($b["billingStatusGroup"]);
        if ($aBillingStatusGroup < $bBillingStatusGroup) {
            return -1;
        } else if ($aBillingStatusGroup > $bBillingStatusGroup) {
            return 1;
        }

        $aProductRank = $sortedProdArray[$aBillingStatusGroup][$a["prodId"]];
        $bProductRank = $sortedProdArray[$bBillingStatusGroup][$b["prodId"]];
    } else if ($dividedByCus) {
        $aCu = strtolower($a["cu"]);
        $bCu = strtolower($b["cu"]);
        if ($aCu < $bCu) {
            return -1;
        } else if ($aCu > $bCu) {
            return 1;
        }

        $aProductRank = $sortedProdArray[$aCu][$a["prodId"]];
        $bProductRank = $sortedProdArray[$bCu][$b["prodId"]];
    } else {
        $aProductRank = $sortedProdArray[$a["prodId"]];
        $bProductRank = $sortedProdArray[$b["prodId"]];
    }

    if (!isset($aProductRank)) {
        $aProductRank = -1;
    }
    if (!isset($bProductRank)) {
        $bProductRank = -1;
    }
    if ($aProductRank > $bProductRank) {
        return -1;
    } else if ($aProductRank < $bProductRank) {
        return 1;
    }

    $aSetupRank = $a["setup"] ? 1 : 0;
    $bSetupRank = $b["setup"] ? 1 : 0;
    if ($aSetupRank > $bSetupRank) {
        return -1;
    } else if ($aSetupRank < $bSetupRank) {
        return 1;
    }

    return MostExpensiveCmpFunc($a, $b);
}


// =================================
// END PAGE 2: Sales Order
// =================================

// =================================
// PAGE 3: Dashboard
// =================================

function ReadDashboard($dbh) {
    try {
        $sql = "select ci.user_name \"cu\", ci.name \"cuName\", fd.start_date \"fdStartDate\", fd.end_date \"fdEndDate\", fd.billing_template \"template\",
                    fd.sales_item_id \"salesItemId\", si.billing_system_id \"billingSystemId\", si.display_name \"salesItemDescr\", sod.prod_id \"prodId\",
                    fd.billing_status \"billingStatus\", fd.bill_half_boolean \"billHalf\", fd.id \"templateId\", so.id \"orderId\"
                from cubillsalesorder so
                inner join cuinfo ci on upper(so.cu_id) = upper(ci.user_name)
                inner join cubillsalesorderdetail sod on so.id = sod.sales_order_id
                inner join cubillfeaturedetail fd on sod.id = fd.sales_order_detail_id
                left join cubillsalesitem si on fd.sales_item_id = si.id where fd.billing_status in (0,1)";
        $sth = db_query($sql, $dbh);
        if (!$sth) {
            throw new exception ("Select query failed.", 1);
        }
        $queryResults = db_fetch_all($sth);
        $queryResults = $queryResults === false ? array() : $queryResults;

        $newRecords = array();
        $setupRecurring = GetSetupRecurring();
        foreach($queryResults as $record) {
            $setupLabel = HCU_array_key_value($record["billingSystemId"], $setupRecurring);
            if ($setupLabel == "triggered") {
                continue; // Triggered records are doing their own thing
            }
            $billHalf = trim($record["billHalf"]) == "Y";
            $salesItemId = intval($record["salesItemId"]);
            $template = HCU_JsonDecode($record["template"]);
            $fixed = floatval($template["fixed"]);
            $variable = floatval($template["variable"]);
            $qty1 = intval($template["qty1"]);
            $qty2 = intval($template["qty2"]);
            $aDNID = HCU_array_key_exists("aDNID", $template) ? $template["aDNID"] : "";
            $billingId = $record["billingSystemId"];
            $description = GetTextDescription($salesItemId, $billingId, $fixed, $variable, $qty1, $qty2, $aDNID);

            $setup = $setupLabel == "setup";
            $results = GetBillingStatusLabel(intval($record["billingStatus"]), $record["fdStartDate"], $record["fdEndDate"], $setupLabel);
            if ($results["status"] !== "000") {
                throw new exception ($results["error"], 2);
            }
            extract($results["data"]);
            $initialRecurringCosts = GetInitialRecurringCosts($salesItemId, $billingId, $fixed, $variable, $qty1, $qty2);

            $newRecords[] = array("templateId" => intval($record["templateId"]), "cu" => $record["cu"], "cuName" => $record["cuName"],
                                  "textDescription" => $description, "salesItemDescr" => $record["salesItemDescr"], "statusLabel" => $statusLabel,
                                  "billHalf" => $billHalf, "initialCosts" => $initialRecurringCosts[0], "recurringCosts" => $initialRecurringCosts[1],
                                  "orderId" => intval($record["orderId"]), "prodId" => $record["prodId"], "fixed" => $fixed, "variable" => $variable,
                                  "setup" => $setup); // For sorting
        }

        $newRecords = SortDashboard($newRecords);

        $returnArray = array("status" => "000", "error" => "", "record" => $newRecords, "operation" => "read");
    } catch (exception $e) {
        $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage());
    }
    return $returnArray;
}

function SortDashboard($records) {
    $sortedProdArray = array();

    usort($records, function($a, $b) {
        return MostExpensiveCmpFunc($a, $b, true);
    });
    $index = count($records) + 1;
    $indices = array();
    foreach($records as $record) {
        $prodId = $record["prodId"];
        $cu = strtolower($record["cu"]);
        if (!isset($sortedProdArray[$cu])) {
            $sortedProdArray[$cu] = array();
            $indices[$cu] = $index;
        }
        if (!isset($sortedProdArray[$cu][$prodId])) {
            $sortedProdArray[$cu][$prodId] = $indices[$cu]--;
        }
    }
    usort($records, function($a, $b) use ($sortedProdArray) {
        return ByProductAndSetupCmpFunc($a, $b, $sortedProdArray, false, true);
    });

    for($i = 0, $count = count($records); $i != $count; $i++) {
        $records[$i]["sortingId"] = $i + 1;
    }

    return $records;
}

function ReadWorkflowStatusInfo($dbh, $sysenv) {
    try {
        extract($sysenv["BILL"]);

        if (!isset($featureId)) {
            throw new exception ("Feature id is required.", 1);
        }
        $featureId = intval($featureId);

        $sql = "select t.track_id \"trackId\", t.issue \"issue\", t.status \"issueStatus\", t.assigned_to \"issueOwner\", t.entry_date \"entryDate\",
                    t.last_activity_date \"lastActivityDate\", temp.title \"nextTask\", temp.assigned_to \"nextTaskOwner\"
                from cutrack t
                left join (
                    select row_number() over (partition by ti.track_id, ti.parent, ti.task_status order by ti.view_order, ti.trackitem_id) as number,
                        ti.title, ti.assigned_to, ti.track_id
                    from cutrackitem ti
                    where ti.task_status = 1 and ti.parent = 0
                ) temp on t.track_id = temp.track_id and temp.number = 1
                where t.billing_feature_id = " . intval($featureId);
        $sth = db_query($sql, $dbh);
        if (!$sth) {
            throw new exception ("Select query failed.", 1);
        }
        $queryResults = db_fetch_all($sth);
        $queryResults = $queryResults === false ? array() : $queryResults;

        $returnArray = array("status" => "000", "error" => "", "record" => $queryResults);
    } catch (exception $e) {
        $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage());
    }
    return $returnArray;
}

// =================================
// END PAGE 3: Dashboard
// =================================

// =================================
// PAGE 4: Sales Items
// =================================

function SalesItemLoad($dbh) {
    try {
        $sql = "select trim(coalesce(p.home_cu_desc, si.prod_id)) \"prodDescr\", si.id \"productId\",
                trim(si.display_name) \"description\", trim(si.billing_system_id) \"billingId\",
                si.billing_template \"template\", trim(si.prod_id) \"prodName\", si.calc_id \"calcId\", si.frequency \"frequency\", si.months_to_bill \"billsOn\"
                from cubillsalesitem si
                left join cuprodlist p on si.prod_id = p.home_cu_code
                where si.deleted_date is null
                order by coalesce(p.home_cu_desc, si.prod_id), si.display_name";
        $sth = db_query($sql, $dbh);
        if (!$sth) {
            throw new exception ("Select query failed.", 1);
        }
        $salesItems = db_fetch_all($sth);
        $salesItems = $salesItems === false ? array() : $salesItems;

        $billingItems = GetBillingItems();
        for($i = 0, $count = count($salesItems); $i != $count; $i++) {
            $billingId = $salesItems[$i]["billingId"];
            $salesItems[$i]["billingIdDescr"] = HCU_array_key_value($billingId, $billingItems);
        }

        $sql = "select trim(p.home_cu_code) \"value\", trim(p.home_cu_desc) \"text\" from cuprodlist p order by p.home_cu_desc";
        $sth = db_query($sql, $dbh);
        if (!$sth) {
            throw new exception ("Select query failed.", 2);
        }
        $prodDDL = db_fetch_all($sth);
        $prodDDL = $prodDDL === false ? array() : $prodDDL;

        $setupConfig = GetSetupRecurring();
        $quantityLabelTypeVariance = GetQuantityLabelTypeVariance();
        $quantityLabelType = GetQuantityLabelType();
        $quantityLabelsVariance = GetQuantityLabelsVariance();
        $quantityLabels = GetQuantityLabels();
        $fixedVariableLabelTypeVariance = GetFixedVariableLabelTypeVariance();
        $fixedVariableLabelType = GetFixedVariableLabelType();
        $fixedVariableLabels = GetFixedVariableLabels();
        $fixedVariableLabelsVariance = GetFixedVariableLabelsVariance();
        $useDNID = GetUseDNID();
        $decimals = GetSaveDigits();

        $billingItemsDDL = array();
        foreach($billingItems as $billingId => $description) {
            $configuration = array();
            if (HCU_array_key_exists($billingId, $setupConfig)) {
                $configuration["setup"] = $setupConfig[$billingId] == "setup";
            }
            if (HCU_array_key_exists($billingId, $quantityLabelTypeVariance)) {
                $configuration["quantityLabelTypeVariance"] = $quantityLabelTypeVariance[$billingId];
            }
            if (HCU_array_key_exists($billingId, $quantityLabelType)) {
                $configuration["quantityLabelType"] = $quantityLabelType[$billingId];
            }
            if (HCU_array_key_exists($billingId, $quantityLabelsVariance)) {
                $configuration["quantityLabelsVariance"] = $quantityLabelsVariance[$billingId];
            }
            if (HCU_array_key_exists($billingId, $quantityLabels)) {
                $configuration["quantityLabels"] = $quantityLabels[$billingId];
            }
            if (HCU_array_key_exists($billingId, $fixedVariableLabelTypeVariance)) {
                $configuration["fixedVariableLabelTypeVariance"] = $fixedVariableLabelTypeVariance[$billingId];
            }
            if (HCU_array_key_exists($billingId, $fixedVariableLabelType)) {
                $configuration["fixedVariableLabelType"] = $fixedVariableLabelType[$billingId];
            }
            if (HCU_array_key_exists($billingId, $fixedVariableLabelsVariance)) {
                $configuration["fixedVariableLabelsVariance"] = $fixedVariableLabelsVariance[$billingId];
            }
            if (HCU_array_key_exists($billingId, $fixedVariableLabels)) {
                $configuration["fixedVariableLabels"] = $fixedVariableLabels[$billingId];
            }

            $configuration["useDNID"] = HCU_array_key_exists($billingId, $useDNID) ? $useDNID[$billingId] : false;
            $configuration["decimals"] = HCU_array_key_value($billingId, $decimals);
            $configuration["decimals"] = $configuration["decimals"] === false ? 2 : intval($configuration["decimals"]);

            $billingItemsDDL[] = array("text" => $description, "value" => $billingId, "configuration" => $configuration);
        }

        for($i = 0, $count = count($salesItems); $i != $count; $i++) {
            $billsOn = intval($salesItems[$i]["billsOn"]);
            $billingId = $salesItems[$i]["billingId"];
            $setup = HCU_array_key_value($billingId, $setupConfig);

            $results = GetFrequencyLabel($billsOn, $setup);
            extract($results["data"]);
            $salesItems[$i]["frequencyDescr"] = $frequencyLabel;
            $salesItems[$i]["billsOn"] = $billsOnKendo;
        }

        $returnArray = array("status" => "000", "error" => "", "billingItemDDL" => $billingItemsDDL, "record" => $salesItems,
                             "productDDL" => $prodDDL, "operation" => "read");
    } catch (exception $e) {
        $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage(), "operation" => "read");
    }
    return $returnArray;
}

function SalesItemCreateOrUpdate($dbh, $isCreate, $sysenv) {
    try {
        extract($sysenv["BILL"]);

        if (!$isCreate) {
            if (!isset($productId) || trim($productId) == "") {
                throw new exception ("Product is required.", 1);
            }
        }
        $productId = isset($productId) ? intval($productId) : 0;

        if (!isset($prodName) || trim($prodName) == "") {
            throw new exception ("Name is required.", 2);
        }
        $name = trim($prodName);

        $description = isset($description) ? trim($description) : "";
        $billingId = isset($billingId) ? trim($billingId) : "";
        $fixed = isset($fixed) ? floatval($fixed) : 0.0;
        $variable = isset($variable) ? floatval($variable) : 0.0;
        $qty1 = isset($qty1) ? intval($qty1) : 0;
        $qty2 = isset($qty2) ? intval($qty2) : 0;
        $calcId = isset($calcId) ? intval($calcId) : 0;
        $billsOn = isset($billsOn) ? trim($billsOn) : "";

        $templateArray = array();
        $templateArray["descr"] = "";
        // Templates have the fixed and variable with two decimal places.
        $templateArray["fixed"] = $fixed;
        $templateArray["variable"] = $variable;
        $templateArray["qty1"] = $qty1;
        $templateArray["qty2"] = $qty2;
        $template = HCU_JsonEncode($templateArray);

        $results = GetFrequencyBillsOn($billsOn);
        if ($results["status"] !== "000") {
            throw new exception($results["error"], 3);
        }
        extract($results["data"]);

        $sql = "select home_cu_desc from cuprodlist where home_cu_code = '$name'";
        $sth = db_query($sql, $dbh);
        if (!$sth) {
            throw new exception ("Select query failed.", 4);
        }
        $prodListDesc = db_num_rows($sth) > 0 ? trim(db_fetch_row($sth, 0)[0]) : "";

        if ($isCreate) {
            $sql = "insert into cubillsalesitem (prod_id, display_name, billing_system_id, billing_template, calc_id, frequency, months_to_bill)
                    values ('" . prep_save($name, 10) . "', '" . prep_save($description, 20) . "', '" . prep_save($billingId, 20) . "', '" . prep_save($template) . "',
                    " . intval($calcId) . ", " . intval($frequency) . ", " . intval($billsOnInt) . ")
                    returning coalesce('$prodListDesc', prod_id) \"prodDescr\", id \"productId\", display_name \"description\", billing_system_id \"billingId\",
                        prod_id \"prodName\", billing_template \"template\", calc_id \"calcId\", frequency \"frequency\", months_to_bill \"billsOn\"";
        } else {
            $sql = "update cubillsalesitem set prod_id = '" . prep_save($name, 10) . "', billing_system_id = '" . prep_save($billingId, 20) . "',
                    billing_template = '" . prep_save($template) . "', display_name = '" . prep_save($description, 20) . "',
                       calc_id = " . intval($calcId) . ", frequency = " . intval($frequency) . ", months_to_bill = " . intval($billsOnInt) . "
                    where id = " . intval($productId) . "
                    returning coalesce('$prodListDesc', prod_id) \"prodDescr\", id \"productId\", display_name \"description\", billing_system_id \"billingId\",
                        prod_id \"prodName\", billing_template \"template\", calc_id \"calcId\", frequency \"frequency\", months_to_bill \"billsOn\"";
        }
        $sth = db_query($sql, $dbh);
        if (!$sth) {
            throw new exception ("Upsert query failed.", 5);
        }
        $queryResults = db_fetch_all($sth);
        $queryResults = $queryResults === false ? array() : $queryResults;

        $billingItems = GetBillingItems();
        $setupRecurring = GetSetupRecurring();
        for($i = 0, $count = count($queryResults); $i != $count; $i++) {
            $billingId = $queryResults[$i]["billingId"];
            $setupLabel = HCU_array_key_value($billingId, $setupRecurring);
            $setupLabel = $setupLabel == "setup";
            $queryResults[$i]["billingIdDescr"] = HCU_array_key_value($billingId, $billingItems);
            $billsOn = intval($queryResults[$i]["billsOn"]);

            $results = GetFrequencyLabel($billsOn, $setupLabel);
            extract($results["data"]);

            $queryResults[$i]["frequencyDescr"] = $frequencyLabel;
            $queryResults[$i]["billsOn"] = $billsOnKendo;
        }

        $returnArray = array("status" => "000", "error" => "", "record" => $queryResults, "operation" => $isCreate ? "add" : "update");

    } catch (exception $e) {
        $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage(), "operation" => $isCreate ? "add" : "update");
    }
    return $returnArray;
}

function SalesItemRemove($dbh, $sysenv) {
    try {
        extract($sysenv["BILL"]);
        if (!isset($productId) || trim($productId) == "") {
            throw new exception ("Product is required.", 1);
        }
        $productId = intval($productId);

        $sql = "update cubillsalesitem set deleted_date = now() where id = " . intval($productId);
        $sth = db_query($sql, $dbh);
        if (!$sth) {
            throw new exception ("Update query failed.", 2);
        }

        $returnArray = array("status" => "000", "error" => "");

    } catch (exception $e) {
        $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage());
    }
    return $returnArray;
}

function GetDeletedSalesItems($dbh) {
    try {
        $sql = "select coalesce(p.home_cu_desc, si.prod_id) \"productName\", si.id \"salesItemId\", si.display_name \"displayName\",
                    si.billing_system_id \"billingId\", si.deleted_date \"deletedDate\"
                from cubillsalesitem si
                left join cuprodlist p on si.prod_id = p.home_cu_code
                where si.deleted_date is not null
                order by coalesce(p.home_cu_desc, si.prod_id), si.display_name";
        $sth = db_query($sql, $dbh);
        if (!$sth) {
            throw new exception ("Select query failed.", 1);
        }
        $queryResults = db_fetch_all($sth);
        $queryResults = $queryResults === false ? array() : $queryResults;

        $billingItems = GetBillingItems();
        for($i = 0, $count = count($queryResults); $i != $count; $i++) {
            $billingId = $queryResults[$i]["billingId"];
            $queryResults[$i]["billingName"] = HCU_array_key_value($billingId, $billingItems);
        }

        $returnArray = array("status" => "000", "error" => "", "record" => $queryResults);
    } catch (exception $e) {
        $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage());
    }
    return $returnArray;
}

// =================================
// END PAGE 4: Sales Items
// =================================

// =================================
// PAGE 5: Sales Order Detail
// =================================

/**
 * function GetWhereForPendingLineItems()
 * @return String of the where clause for pending line items.
 */
function GetWhereForPendingLineItems() {
    $setupRecurring = GetSetupRecurring();
    $setups = array();
    $recurrings = array();
    $triggereds = array();
    foreach ($setupRecurring as $billingId => $value) {
        switch($value) {
            case "triggered":
                $setups[] = prep_save($billingId, 20);
                break;
        }
    }
    $triggereds = count($triggereds) == 0 ? false : "'" . implode("', '", $triggereds) . "'";

    $whereOr = array();
    $whereOr[] = "fd.billing_status not in (2,3)";

    $complicatedAnd = array();
    $complicatedAnd[] = "fd.billing_status in (2,3)"; // Both mean "ready to bill."  3 is if there was supposed to be a workflow attached.

    if ($triggereds !== false) {
        $complicatedAnd[] = "si.billing_system_id not in ($triggereds)"; // Only for setups and recurring features.
    }

    $complicatedAnd[] = "date_trunc('month', fd.start_date) >= date_trunc('month', now())"; // Feature starts this month or later.

    $complicatedAnd = "(" . implode(") and (", $complicatedAnd) . ")";

    $whereOr[] = $complicatedAnd;
    $whereOr = "(" . implode(") or (", $whereOr) . ")";

    return $whereOr;
}

/**
 * function GetWhereForActiveLineItems()
 * @return String of the where clause for active line items.
 */
function GetWhereForActiveLineItems() {
    // Is active status and ends this month or later.
    $whereAnd = array();
    $whereAnd[] = "fd.billing_status in (2,3)"; // Must be active.
    $whereAnd[] = "(fd.end_date is null or date_trunc('month', fd.end_date) >= date_trunc('month', now()))"; // Does not end or ends this month or later.
    $whereAnd[] = "date_trunc('month', fd.start_date) < date_trunc('month', now())"; // Feature started before this month.

    $where = "(" . implode(") and (", $whereAnd) . ")";

    return $where;
}

/**
 * function GetWhereForCompletedLineItems()
 * @return String of the where clause for completed line items.
 */
function GetWhereForCompletedLineItems() {
    // Is active status and ends before this month.
    $where = "(fd.billing_status in (2,3) and date_trunc('month', fd.end_date) < date_trunc('month', now()))";

    return $where;
}

/**
 * function ReadPendingDetailsTop($dbh, $sysenv, $Hu)
 * This gets the values for the pending grid.
 * This DOES NOT INCLUDE the details of the pending grid.
 *
 * @param $dbh -- the database connection.
 * @param $sysenv -- the system environment variables.
 * @param $Hu -- the logged in user.
 *
 * @return $status -- "000" if successful, nonzero otherwise.
 * @return $error -- "" if successful, nonempty otherwise.
 * @return $record -- the records for the grid.
 * @return $operation -- "read"
 */
function ReadPendingDetailsTop($dbh, $sysenv, $Hu) {
    try {
        extract($sysenv["BILL"]);
        if (!isset($salesOrderId) || trim($salesOrderId) == "") {
            throw new exception ("Contract id is required.", 1);
        }
        $salesOrderId = intval($salesOrderId);
        $orderDetailIds = isset($orderDetailIds) ? trim($orderDetailIds) : "[]";
        $orderDetailIds = HCU_JsonDecode($orderDetailIds);

        $whereOr = GetWhereForPendingLineItems();

        // Returns all contract details where there are no line items OR there are one or more pending line items.
        $sql = "select d.id \"orderDetailId\", trim(coalesce(p.home_cu_desc, d.prod_id)) \"detailDescription\",
                trim(d.last_modified_by) \"lastModifiedBy\", d.last_modified_date \"lastModifiedDate\", trim(d.prod_id) \"productId\",
                coalesce(cb.pendingcount, 0) \"numDetails\"
                from cubillsalesorderdetail d
                left join cuprodlist p on d.prod_id = p.home_cu_code
                left join (
                    select count(*), fd.sales_order_detail_id from cubillfeaturedetail fd
                    group by fd.sales_order_detail_id
                ) as ca (allcount, detail_id) on d.id = ca.detail_id
                left join (
                    select count(*), fd.sales_order_detail_id from cubillfeaturedetail fd
                    inner join cubillsalesitem si on fd.sales_item_id = si.id
                    where $whereOr
                    group by fd.sales_order_detail_id
                ) as cb (pendingcount, detail_id) on d.id = cb.detail_id
                where d.sales_order_id = " . intval($salesOrderId) . " and (coalesce(ca.allcount, 0) = 0 or cb.pendingcount > 0)
                order by 1";
        $sth = db_query($sql, $dbh);
        if (!$sth) {
            throw new exception ("Query failed", 1);
        }

        $pendingDetails = db_fetch_all($sth);
        $pendingDetails = $pendingDetails === false ? array() : $pendingDetails;

        foreach($pendingDetails as $i => $pendingDetail) {
            $pendingDetails[$i]["opened"] = in_array($pendingDetail["orderDetailId"], $orderDetailIds) && $pendingDetail["numDetails"] > 0;
        }

        $returnArray = array("status" => "000", "error" => "", "record" => $pendingDetails, "operation" => "read");
    } catch (exception $e) {
        $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage(), "record" => array(), "operation" => "read");
    }
    return $returnArray;
}

/**
 * function ReadActiveDetails($dbh, $sysenv, $Hu)
 * This gets the values for the active grid.
 *
 * @param $dbh -- the database connection.
 * @param $sysenv -- the system environment variables.
 * @param $Hu -- the logged in user.
 *
 * @return $status -- "000" if successful, nonzero otherwise.
 * @return $error -- "" if successful, nonempty otherwise.
 * @return $record -- the records for the grid.
 * @return $operation -- "read"
 */
function ReadActiveDetails($dbh, $sysenv, $Hu) {
    try {
        extract($sysenv["BILL"]);
        if (!isset($salesOrderId) || trim($salesOrderId) == "") {
            throw new exception ("Contract id is required.", 1);
        }
        $salesOrderId = intval($salesOrderId);

        if (!isset($includeIds) || trim($includeIds) == "") {
            throw new exception ("Include ids are required.", 5);
        }

        if (!isset($excludeIds) || trim($excludeIds) == "") {
            throw new exception ("Exclude ids are required.", 6);
        }

        $includeIds = HCU_JsonDecode($includeIds);
        $includeIds = array_map("intval", $includeIds);

        $excludeIds = HCU_JsonDecode($excludeIds);
        $excludeIds = array_map("intval", $excludeIds);

        $includePhrase = count($includeIds) > 0 ? "or fd.id in (" . implode(", ", $includeIds) . ")" : "";
        $excludePhrase = count($excludeIds) > 0 ? "and fd.id not in (" . implode(", ", $excludeIds) . ")" : "";

        $setupRecurring = GetSetupRecurring();

        $where = GetWhereForActiveLineItems();

        $sql = "select trim(coalesce(p.home_cu_desc, d.prod_id)) \"detailDescription\", fd.id \"featureDetailId\", fd.start_date \"startDate\",
                fd.end_date \"endDate\", fd.billing_template \"template\",
                trim(si.display_name) \"salesItemDescr\", fd.sales_item_id \"salesItemId\", fd.billing_status \"billingStatus\", fd.bill_half_boolean \"billHalf\",
                trim(si.billing_system_id) \"billingId\", fd.partially_billed_date \"partiallyBilledDate\", fd.partially_billed_amount \"partiallyBilledAmount\",
                fd.frequency \"frequency\", fd.months_to_bill \"billsOn\", trim(d.prod_id) \"feature\"
                from cubillfeaturedetail fd
                inner join cubillsalesorderdetail d on fd.sales_order_detail_id = d.id
                left join cubillsalesitem si on fd.sales_item_id = si.id
                left join cuprodlist p on d.prod_id = p.home_cu_code
                where d.sales_order_id = " . intval($salesOrderId) . " and ($where $includePhrase) $excludePhrase
                order by 1, fd.id";

        $sth = db_query($sql, $dbh);
        if (!$sth) {
            throw new exception ("Query failed.", 2);
        }

        $rawRecords = db_fetch_all($sth);
        $rawRecords = $rawRecords === false ? array() : $rawRecords;
        $featureRecords = array();

        foreach($rawRecords as $record) {

            $featureRecord = array("featureDetailId" => $record["featureDetailId"]);

            $template = HCU_JsonDecode($record["template"]);

            $fixed = floatval($template["fixed"]);
            $variable = floatval($template["variable"]);
            $qty1 = intval($template["qty1"]);
            $qty2 = intval($template["qty2"]);
            $aDNID = HCU_array_key_exists("aDNID", $template) ? $template["aDNID"] : "";

            $featureRecord["featureDescription"] = GetTextDescription($record["salesItemId"], $record["billingId"], $fixed,
                                                                   $variable, $qty1, $qty2, $aDNID);
            $featureRecord["featureName"] = $template["descr"] == null || $template["descr"] == "" ? $record["salesItemDescr"] : $template["descr"];
            $featureRecord["productName"] = $record["detailDescription"];

            $billingStatus = intval($record["billingStatus"]);
            $setupLabel = HCU_array_key_value($record["billingId"], $setupRecurring);
            $setup = $setupLabel !== false ? $setupLabel == "setup" : false;

            $results = GetBillingStatusLabel($billingStatus, $record["startDate"], $record["endDate"], $setupLabel, "active");
            if ($results["status"] !== "000") {
                throw new exception ("Get billing status label failed.", 3);
            }

            $featureRecord["status"] = $results["data"]["statusLabel"];

            $results = GetHalfBillingStatus($setup, $record["billHalf"], $record["partiallyBilledDate"], $record["startDate"]);
            if ($results["status"] !== "000") {
                throw new exception ("Get half billing status failed.", 4);
            }
            $featureRecord["partialBillingStatus"] = $results["data"]["statusLabel"];

            $results = GetFrequencyLabel(intval($record["billsOn"]), $setupLabel);
            $featureRecord["frequencyDescr"] = $results["data"]["frequencyLabel"];

            $showCompleteButton = false;
            $completeButtonText = "";

            if ($setupLabel !== "triggered") {
                $showCompleteButton = true;

                // You can UNDO it if it is going to be completed this month or next.
                if (in_array($featureRecord["status"], array("Ending next month", "Ending this month"))) {
                    $completedButtonText = "Undo";
                // You can also UNDO it if this session, you specified an end date.
                // (This catches the case where the end date is farther in the future than next month.)
                } else if (in_array($featureRecord["featureDetailId"], $includeIds) && $record["endDate"] != null) {
                    $completeButtonText = "Undo";

                    $endDateTime = new DateTime($record["endDate"]);
                    $featureRecord["status"] = "Ending " . $endDateTime->format("m/Y");
                } else {
                    $completeButtonText = "Complete";
                }
            }

            $featureRecord["showCompleteButton"] = $showCompleteButton;
            $featureRecord["completeButtonText"] = $completeButtonText;
            $featureRecord["endDate"] = $record["endDate"];

            $featureRecords[] = $featureRecord;
        }

        $returnArray = array("status" => "000", "error" => "", "record" => $featureRecords, "operation" => "read");
    } catch (exception $e) {
        $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage(), "record" => array(), "operation" => "read");
    }
    return $returnArray;
}

/**
 * function ReadCompletedDetails($dbh, $sysenv, $Hu)
 * This gets the values for the completed grid.
 *
 * @param $dbh -- the database connection.
 * @param $sysenv -- the system environment variables.
 * @param $Hu -- the logged in user.
 *
 * @return $status -- "000" if successful, nonzero otherwise.
 * @return $error -- "" if successful, nonempty otherwise.
 * @return $record -- the records for the grid.
 * @return $operation -- "read"
 */
function ReadCompletedDetails($dbh, $sysenv, $Hu) {
    try {
        extract($sysenv["BILL"]);
        if (!isset($salesOrderId) || trim($salesOrderId) == "") {
            throw new exception ("Contract id is required.", 1);
        }
        $salesOrderId = intval($salesOrderId);

        if (!isset($includeIds) || trim($includeIds) == "") {
            throw new exception ("Include ids are required.", 5);
        }

        if (!isset($excludeIds) || trim($excludeIds) == "") {
            throw new exception ("Exclude ids are required.", 6);
        }

        $includeIds = HCU_JsonDecode($includeIds);
        $includeIds = array_map("intval", $includeIds);

        $excludeIds = HCU_JsonDecode($excludeIds);
        $excludeIds = array_map("intval", $excludeIds);

        $includePhrase = count($includeIds) > 0 ? "or fd.id in (" . implode(", ", $includeIds) . ")" : "";
        $excludePhrase = count($excludeIds) > 0 ? "and fd.id not in (" . implode(", ", $excludeIds) . ")" : "";

        $setupRecurring = GetSetupRecurring();

        $where = GetWhereForCompletedLineItems();

        $sql = "select trim(coalesce(p.home_cu_desc, d.prod_id)) \"detailDescription\", fd.id \"featureDetailId\", fd.start_date \"startDate\",
                fd.end_date \"endDate\", fd.billing_template \"template\",
                trim(si.display_name) \"salesItemDescr\", fd.sales_item_id \"salesItemId\", fd.billing_status \"billingStatus\", fd.bill_half_boolean \"billHalf\",
                trim(si.billing_system_id) \"billingId\", fd.partially_billed_date \"partiallyBilledDate\", fd.partially_billed_amount \"partiallyBilledAmount\",
                fd.frequency \"frequency\", fd.months_to_bill \"billsOn\", trim(d.prod_id) \"feature\"
                from cubillfeaturedetail fd
                inner join cubillsalesorderdetail d on fd.sales_order_detail_id = d.id
                left join cubillsalesitem si on fd.sales_item_id = si.id
                left join cuprodlist p on d.prod_id = p.home_cu_code
                where d.sales_order_id = " . intval($salesOrderId) . " and ($where $includePhrase) $excludePhrase
                order by 1, fd.id";

        $sth = db_query($sql, $dbh);
        if (!$sth) {
            throw new exception ("Query failed.", 2);
        }

        $rawRecords = db_fetch_all($sth);
        $rawRecords = $rawRecords === false ? array() : $rawRecords;
        $featureRecords = array();

        foreach($rawRecords as $record) {

            $featureRecord = array("featureDetailId" => $record["featureDetailId"]);

            $template = HCU_JsonDecode($record["template"]);

            $fixed = floatval($template["fixed"]);
            $variable = floatval($template["variable"]);
            $qty1 = intval($template["qty1"]);
            $qty2 = intval($template["qty2"]);
            $aDNID = HCU_array_key_exists("aDNID", $template) ? $template["aDNID"] : "";

            $featureRecord["featureDescription"] = GetTextDescription($record["salesItemId"], $record["billingId"], $fixed,
                                                                   $variable, $qty1, $qty2, $aDNID);
            $featureRecord["featureName"] = $template["descr"] == null || $template["descr"] == "" ? $record["salesItemDescr"] : $template["descr"];
            $featureRecord["productName"] = $record["detailDescription"];

            $billingStatus = intval($record["billingStatus"]);
            $setupLabel = HCU_array_key_value($record["billingId"], $setupRecurring);
            $setup = $setupLabel !== false ? $setupLabel == "setup" : false;

            $results = GetBillingStatusLabel($billingStatus, $record["startDate"], $record["endDate"], $setupLabel, "completed");
            if ($results["status"] !== "000") {
                throw new exception ("Get billing status label failed.", 3);
            }

            $featureRecord["status"] = $results["data"]["statusLabel"];

            $results = GetHalfBillingStatus($setup, $record["billHalf"], $record["partiallyBilledDate"], $record["startDate"]);
            if ($results["status"] !== "000") {
                throw new exception ("Get half billing status failed.", 4);
            }
            $featureRecord["partialBillingStatus"] = $results["data"]["statusLabel"];

            $results = GetFrequencyLabel(intval($record["billsOn"]), $setupLabel);
            $featureRecord["frequencyDescr"] = $results["data"]["frequencyLabel"];

            $showAdvanceWorkflow = false;
            $advanceWorkflowText = "";

            if ($featureRecord["status"] == "Completed") {
                $showAdvanceWorkflow = true;
                $advanceWorkflowText = "Reactivate";
            } else {
                $showAdvanceWorkflow = true;
                $advanceWorkflowText = "Undo";
            }

            $featureRecord["showAdvanceWorkflow"] = $showAdvanceWorkflow;
            $featureRecord["advanceWorkflowText"] = $advanceWorkflowText;

            $featureRecords[] = $featureRecord;
        }

        $returnArray = array("status" => "000", "error" => "", "record" => $featureRecords, "operation" => "read");
    } catch (exception $e) {
        $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage(), "record" => array(), "operation" => "read");
    }
    return $returnArray;
}

/**
 * function ReadPendingDetailsBottom($dbh, $sysenv, $Hu)
 * This gets the values for the detail grid.
 * This is for only one detail in the pending grid.
 * Each detail grid will call this function with the orderDetailId.
 *
 * @param $dbh -- the database connection.
 * @param $sysenv -- the system environment variables.
 * @param $Hu -- the logged in user.
 *
 * @return $status -- "000" if successful, nonzero otherwise.
 * @return $error -- "" if successful, nonempty otherwise.
 * @return $record -- the records for the grid.
 * @return $operation -- "read"
 */
function ReadPendingDetailsBottom($dbh, $sysenv, $Hu) {
    try {
        extract($sysenv["BILL"]);
        if (!isset($orderDetailId) || trim($orderDetailId) == "") {
            throw new exception ("Detail id is required.", 1);
        }
        $orderDetailId = intval($orderDetailId);

        if (!isset($includeIds) || trim($includeIds) == "") {
            throw new exception ("Include ids are required.", 5);
        }

        if (!isset($excludeIds) || trim($excludeIds) == "") {
            throw new exception ("Exclude ids are required.", 6);
        }

        $includeIds = HCU_JsonDecode($includeIds);
        $includeIds = array_map("intval", $includeIds);

        $excludeIds = HCU_JsonDecode($excludeIds);
        $excludeIds = array_map("intval", $excludeIds);

        $includePhrase = count($includeIds) > 0 ? "or fd.id in (" . implode(", ", $includeIds) . ")" : "";
        $excludePhrase = count($excludeIds) > 0 ? "and fd.id not in (" . implode(", ", $excludeIds) . ")" : "";

        $whereOr = GetWhereForPendingLineItems();

        $setupRecurring = GetSetupRecurring();

        $sql = "select fd.id \"featureDetailId\", fd.start_date \"startDate\", fd.end_date \"endDate\", fd.billing_template \"template\",
                trim(si.display_name) \"salesItemDescr\", fd.sales_item_id \"salesItemId\", fd.billing_status \"billingStatus\", fd.bill_half_boolean \"billHalf\",
                trim(si.billing_system_id) \"billingId\",
                fd.partially_billed_date \"partiallyBilledDate\",
                exists (select 'FOUND' from cubillpreinvoice pi where fd.id = pi.feature_detail_id) \"linkExists\",
                exists (select 'FOUND' from cutrack t where fd.id = t.billing_feature_id) \"jobExists\", fd.partially_billed_amount \"partiallyBilledAmount\",
                fd.frequency \"frequency\", fd.months_to_bill \"billsOn\"
                from cubillfeaturedetail fd
                left join cubillsalesitem si on fd.sales_item_id = si.id
                where fd.sales_order_detail_id = " . intval($orderDetailId) . "
                and ($whereOr $includePhrase) $excludePhrase
                order by fd.id";

        $sth = db_query($sql, $dbh);
        if (!$sth) {
            throw new exception ("Query failed.", 2);
        }

        $rawRecords = db_fetch_all($sth);
        $rawRecords = $rawRecords === false ? array() : $rawRecords;
        $featureRecords = array();

        foreach($rawRecords as $record) {

            $featureRecord = array("featureDetailId" => $record["featureDetailId"]);

            $template = HCU_JsonDecode($record["template"]);

            $fixed = floatval($template["fixed"]);
            $variable = floatval($template["variable"]);
            $qty1 = intval($template["qty1"]);
            $qty2 = intval($template["qty2"]);
            $aDNID = HCU_array_key_exists("aDNID", $template) ? $template["aDNID"] : "";

            $featureRecord["actualDescription"] = $template["descr"] == null || $template["descr"] == "" ? $record["salesItemDescr"] : $template["descr"];
            $featureRecord["textDescription"] = GetTextDescription($record["salesItemId"], $record["billingId"], $fixed,
                                                                   $variable, $qty1, $qty2, $aDNID);

            $billingStatus = intval($record["billingStatus"]);
            $setupLabel = HCU_array_key_value($record["billingId"], $setupRecurring);
            $setup = $setupLabel !== false ? $setupLabel == "setup" : false;

            $results = GetBillingStatusLabel($billingStatus, $record["startDate"], $record["endDate"], $setupLabel, "pending");
            if ($results["status"] !== "000") {
                throw new exception ("Get billing status label failed.", 3);
            }

            $featureRecord["billingStatusLabel"] = $results["data"]["statusLabel"];

            $results = GetHalfBillingStatus($setup, $record["billHalf"], $record["partiallyBilledDate"], $record["startDate"]);
            if ($results["status"] !== "000") {
                throw new exception ("Get half billing status failed.", 4);
            }
            $featureRecord["partialBillingStatus"] = $results["data"]["statusLabel"];

            $results = GetFrequencyLabel(intval($record["billsOn"]), $setupLabel);
            $featureRecord["frequencyDescr"] = $results["data"]["frequencyLabel"];

            if ($record["linkExists"] == "t" || $record["jobExists"] == "t") {
                $featureRecord["deletable"] = false;
            } else {
                if ($record["billingStatus"] == 2) {
                    $dateTime = new DateTime ($record["startDate"]);
                    $firstOfMonth = new DateTime();
                    $firstOfMonth->modify("first day of this month");
                    $interval = $dateTime->diff($firstOfMonth);
                    $dateDiff = $interval->format('%R%a');
                    if ($dateDiff <= 0) {
                        $featureRecord["deletable"] = true;
                    } else {
                        $featureRecord["deletable"] = false;
                    }
                } else {
                    $featureRecord["deletable"] = true;
                }
            }

            $showAdvanceWorkflow = false;
            $advanceWorkflowText = "";

            if (in_array($featureRecord["billingStatusLabel"],
                         array("Not triggered", "Pending", "Pending Workflow", "Pending Workflow, Billing Half", "No Workflow"))) {
                switch($setupLabel) {
                    case "recurring":
                        $showAdvanceWorkflow = true;
                        $advanceWorkflowText = "Start";
                        break;
                    case "triggered":
                        $showAdvanceWorkflow = true;
                        $advanceWorkflowText = "Trigger";
                        break;
                }
            } else if ($featureRecord["billingStatusLabel"] !== "Ready to Bill Half") {
                switch($setupLabel) {
                    case "recurring":
                        $showAdvanceWorkflow = true;
                        $advanceWorkflowText = "Undo";
                        break;
                    case "triggered":
                        $showAdvanceWorkflow = true;
                        $advanceWorkflowText = "Undo Trigger";
                        break;
                    case "setup":
                        $showAdvanceWorkflow = true;
                        $advanceWorkflowText = "Change Start";
                        break;
                }
            }

            $featureRecord["showAdvanceWorkflow"] = $showAdvanceWorkflow;
            $featureRecord["advanceWorkflowText"] = $advanceWorkflowText;

            $featureRecord["startDate"] = $record["startDate"];

            $featureRecords[] = $featureRecord;
        }

        $returnArray = array("status" => "000", "error" => "", "record" => $featureRecords, "operation" => "read");
    } catch (exception $e) {
        $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage(), "record" => array(), "operation" => "read");
    }
    return $returnArray;
}

/**
 * function ReadSalesOrderLineItem($dbh, $sysenv, $Hu, $isActive)
 * This gets all the data need to add/update a line item.
 * For the most part, the detail grid call ONLY returns the display labels.
 *
 * @param $dbh -- the database connection.
 * @param $sysenv -- the system environment variables.
 * @param $Hu -- the logged in user.
 * @param $isActive -- this can be called from the active grid.
 *    In this case, workflow, bill half, and frequency values cannot be changed.
 *
 * @return $status -- "000" if successful, nonzero otherwise.
 * @return $error -- "" if successful, nonempty otherwise.
 * @return $lineItemData -- this contains all the values of the line item.
 * @return $configuration -- this contains the labels and what to show for the template.
 * @return $bindOptions -- this contains dropdownlist values to select.
 *    For any DIVs that can be changed, the visibility of those DIVs are shown here.
 *    That is for bill half, frequency, and workflow.
 * @return $ddls.workflowUnattachedJobs -- a list of workflows not attached for that billing system id.
 * @return $ddls.workflows -- a list of workflow options for the line item.
 *    This starts with the default workflow option list.
 *    If there are no unattached jobs, then the option to associate it to a job is eliminated.
 */
function ReadSalesOrderLineItem($dbh, $sysenv, $Hu, $isActive) {
    try {
        extract($sysenv["BILL"]);
        if (!isset($featureDetailId) || trim($featureDetailId) == "") {
            throw new exception ("Line item id is required.", 1);
        }

        $sql = "select fd.id \"featureDetailId\", fd.start_date \"startDate\", fd.end_date \"endDate\",
                    fd.billing_template \"template\", si.display_name \"salesItemDescr\", fd.sales_item_id \"salesItemId\",
                    fd.billing_status \"billingStatus\", fd.bill_half_boolean \"billHalf\", si.billing_system_id \"billingId\",
                    fd.partially_billed_date \"partiallyBilledDate\",
                    exists (select 'FOUND' from cubillpreinvoice pi where fd.id = pi.feature_detail_id) \"linkExists\",
                    exists (select 'FOUND' from cutrack t where fd.id = t.billing_feature_id) \"jobExists\",
                    fd.partially_billed_amount \"partiallyBilledAmount\", fd.frequency \"frequency\", fd.months_to_bill \"billsOn\"
                from cubillfeaturedetail fd
                left join cubillsalesitem si on fd.sales_item_id = si.id
                where fd.id = " . intval($featureDetailId);

        $sth = db_query($sql, $dbh);
        if (!$sth) {
            throw new exception ("Query failed.", 2);
        }

        $rawRecord = db_num_rows($sth) > 0 ? db_fetch_assoc($sth, 0) : array();

        $featureRecord = array("featureDetailId" => $rawRecord["featureDetailId"], "startDate" => $rawRecord["startDate"], "endDate" => $rawRecord["endDate"],
                               "salesItemId" => intval($rawRecord["salesItemId"]),
                               "billingStatus" => intval($rawRecord["billingStatus"]), "partiallyBilledAmount" => floatval($rawRecord["partiallyBilledAmount"]),
                               "partiallyBilledDate" => $rawRecord["partiallyBilledDate"]);

        $template = HCU_JsonDecode($rawRecord["template"]);
        $featureRecord["actualDescription"] = $template["descr"] == null || $template["descr"] == "" ? $rawRecord["salesItemDescr"] : $template["descr"];
        $featureRecord["overrideDescription"] = $template["descr"] == null || $template["descr"] == "" ? "" : $template["descr"];
        $featureRecord["fixed"] = floatval($template["fixed"]);
        $featureRecord["variable"] = floatval($template["variable"]);
        $featureRecord["qty1"] = intval($template["qty1"]);
        $featureRecord["qty2"] = intval($template["qty2"]);
        // Positive infinity is marked as null in the application (with a placeholder) and -1 in the database.
        $featureRecord["qty2"] = $featureRecord["qty2"] == -1 ? null : $featureRecord["qty2"];
        $featureRecord["aDNID"] = HCU_array_key_exists("aDNID", $template) ? $template["aDNID"] : "";

        $bindOptions = GetDefaultLineItemBindOptions();

        if ($rawRecord["billHalf"] === "Y") {
            $bindOptions["billHalfValue"] = "future";
            $bindOptions["billHalfCheckReceivedRowVisible"] = false;
            $bindOptions["billHalfDateRowVisible"] = true;
            $bindOptions["billHalfAmountRowVisible"] = false;
            $bindOptions["billHalfDateLabel"] = "Date to Bill Half:";
        } else if (isset($rawRecord["partiallyBilledDate"])) {
            $bindOptions["billHalfValue"] = "partial";
            $bindOptions["billHalfCheckReceivedRowVisible"] = true;
            $bindOptions["billHalfDateRowVisible"] = false;
            $bindOptions["billHalfAmountRowVisible"] = true;
            $bindOptions["billHalfDateLabel"] = "Check Received:";
        }

        if ($rawRecord["frequency"] !== 0) {
            $billsOn1Value = -1;
            $billsOn2Value = -1;
            $billsOn = $rawRecord["billsOn"];

            for ($i = 0; $i != 12; $i++) {
                if ($billsOn1Value == -1) {
                    $monthSet = $billsOn & (1 << $i);

                    if ($monthSet !== 0) {
                        $billsOn1Value = $i;
                    }
                } else if ($billsOn2Value == -1) {
                    $monthSet = $billsOn & (1 << $i);

                    if ($monthSet !== 0) {
                        $billsOn2Value = $i;
                    }
                } else {
                    break;
                }
            }

            switch($rawRecord["frequency"]) {
                case 1:
                    $bindOptions["frequencyValue"] = 1;
                    $bindOptions["billsOn1RowVisible"] = true;
                    $bindOptions["billsOn2RowVisible"] = true;
                    $bindOptions["billsOn1Value"] = $billsOn1Value;
                    $bindOptions["billsOn2Value"] = $billsOn2Value;
                    break;
                case 2:
                    $bindOptions["frequencyValue"] = 2;
                    $bindOptions["billsOn1RowVisible"] = true;
                    $bindOptions["billsOn2RowVisible"] = false;
                    $bindOptions["billsOn1Value"] = $billsOn1Value;
                    break;
                default:
                    break;
            }
        }


        $billingId = $rawRecord["billingId"];
        $salesItemId = $rawRecord["salesItemId"];

        $results = CompileLineItemTemplateBooleans($billingId, $salesItemId, $rawRecord["jobExists"] === "t");
        if ($results["status"] !== "000") {
            throw new exception ("Configuration failed.", 3);
        }

        $configuration = $results["templateBooleans"];

        if ($configuration["showQuantityRangeRow"]) {
            $bindOptions["qtyRangeVisible"] = true; // Needed to save infinity correctly.
        }

        $setupConfig = GetSetupRecurring();

        if (HCU_array_key_exists($billingId, $setupConfig)) {
            $setupLabel = $setupConfig[$billingId];
        } else {
            $setupLabel = null;
        }

        $workflowUnattachedJobs = array();
        $workflowDDL = array();

        if ($setupLabel === "setup" && !$isActive) {
            $sql = "select t.issue \"text\", t.track_id \"value\"
                    from cubillfeaturedetail fd
                    inner join cubillsalesorderdetail sod on fd.id = " . intval($featureDetailId) . " and fd.sales_order_detail_id = sod.id
                    inner join cuissues i on sod.prod_id = i.gen_for_product
                    inner join cubillsalesorder so on sod.sales_order_id = so.id
                    inner join cutrack t on i.issue_id = t.issue_id and trim(lower(so.cu_id)) = trim(lower(t.user_name)) and t.billing_feature_id = 0
                    order by t.issue";
            $sth = db_query($sql, $dbh);

            if (!$sth) {
                throw new exception ("Query failed.", 4);
            }

            $workflowUnattachedJobs = db_fetch_all($sth);
            $workflowUnattachedJobs = $workflowUnattachedJobs === false ? array() : $workflowUnattachedJobs;

            $workflowDDL = GetWorkflowOptions();

            if (count($workflowUnattachedJobs) === 0) {
                $newWorkflowDDL = array();
                foreach($workflowDDL as $record) {

                    if ($record["value"] !== "associate") {
                        $newWorkflowDDL[] = $record;
                    }
                }

                $workflowDDL = $newWorkflowDDL;
            } else {
                $bindOptions["unattachedJobValue"] = intval($workflowUnattachedJobs[0]["value"]);
            }
        }

        if ($isActive) {
            $configuration["showOverrideRow"] = false;
            $configuration["showBillHalfDiv"] = false;
            $configuration["showWorkflowDiv"] = false;
            $configuration["showFrequencyDiv"] = false;
        } else {
            $configuration["showOverrideRow"] = true;
        }

        $ddls = array("workflowUnattachedJobs" => $workflowUnattachedJobs, "workflows" => $workflowDDL);

        $returnArray = array("status" => "000", "error" => "", "lineItemData" => $featureRecord, "configuration" => $configuration,
                             "bindOptions" => $bindOptions, "ddls" => $ddls, "type" => "read");

    } catch (exception $e) {
        $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage(), "type" => "read");
    }
    return $returnArray;
}

/**
 * function GetDefaultLineItemBindOptions()
 * @return the initial dropdownlist and visibility options.
 */
function GetDefaultLineItemBindOptions() {
    $bindOptions = array();

    $bindOptions["isOverrideCheckboxShown"] = true;
    $bindOptions["isOverrideDescrShown"] = false;
    $bindOptions["qtyRangeVisible"] = false;

    // BillHalf
    $bindOptions["billHalfValue"] = "full";
    $bindOptions["billHalfCheckReceivedRowVisible"] = false;
    $bindOptions["billHalfDateRowVisible"] = false;
    $bindOptions["billHalfAmountRowVisible"] = false;
    $bindOptions["billHalfDateLabel"] = "";
    $bindOptions["billHalfDDLEnabled"] = true;

    // Workflow
    $bindOptions["workflowValue"] = "create";
    $bindOptions["workflowRowVisible"] = true;
    $bindOptions["associateToJobRowVisible"] = false;
    $bindOptions["immediateBillRowVisible"] = false;
    $bindOptions["unattachedJobValue"] = -1;

    // Frequency
    $bindOptions["frequencyValue"] = 0;
    $bindOptions["billsOn1RowVisible"] = false;
    $bindOptions["billsOn2RowVisible"] = false;
    $bindOptions["billsOn1Value"] = 0;
    $bindOptions["billsOn2Value"] = 0;

    return $bindOptions;
}

/**
 * function GetDefaultProductOptions($ddls)
 * @param $ddls -- the current dropdownlist values.
 *    (To set the initial values to the first in the lists.)
 * @return the initial dropdownlist and visibility options.
 */
function GetDefaultProductOptions($ddls) {
    $bindOptions = array();
    $source = array();

    $bindOptions["productWarningVisible"] = false;
    $bindOptions["configRowVisible"] = false;
    $bindOptions["billHalfRowVisible"] = false;
    $bindOptions["billHalfCheckReceivedRowVisible"] = false;
    $bindOptions["billHalfDateRowVisible"] = false;
    $bindOptions["workflowRowVisible"] = false;
    $bindOptions["associateToJobRowVisible"] = false;
    $bindOptions["immediateBillRowVisible"] = false;
    $bindOptions["dnidRowVisible"] = false;
    $bindOptions["billHalfDDLEnabled"] = true;
    $bindOptions["productId"] = "";
    $bindOptions["productOption"] = "";
    $bindOptions["workflowValue"] = "none";
    $bindOptions["billHalfValue"] = "full";
    $bindOptions["unattachedJobValue"] = 0;
    $bindOptions["hasSetup"] = false;

    $source["aDNID"] = "";
    $source["partiallyBilledDate"] = null;

    $productItem = count($ddls["productItems"]) > 0 ? $ddls["productItems"][0] : null;

    if ($productItem != null) {

        if (HCU_array_key_value("hasOptions", $productItem)) {
            $bindOptions["configRowVisible"] = true;
            $optionItem = $ddls["productOptions"][0];
            $bindOptions["productOption"] = $optionItem["value"];

            if (HCU_array_key_value("hasSetup", $optionItem)) {
                $bindOptions["billHalfRowVisible"] = true;
                $bindOptions["workflowRowVisible"] = true;
                $bindOptions["workflowValue"] = "create";
                $bindOptions["hasSetup"] = true;
            }
        } else if (HCU_array_key_value("hasSetup", $productItem)) {
            $bindOptions["billHalfRowVisible"] = true;
            $bindOptions["workflowRowVisible"] = true;
            $bindOptions["workflowValue"] = "create";
            $bindOptions["hasSetup"] = true;
        }

        if (HCU_array_key_value("useDNID", $productItem)) {
            $bindOptions["dnidRowVisible"] = true;
        }

        if (HCU_array_key_value("productExists", $productItem)) {
            $bindOptions["productWarningVisible"] = true;
            $bindOptions["billHalfValue"] = "nosetup";
            $bindOptions["workflowRowVisible"] = false;
            $bindOptions["workflowValue"] = "none";
        }

        $bindOptions["productId"] = $productItem["value"];
    }

    return array("bindOptions" => $bindOptions, "source" => $source);
}

/**
 * function CompileLineItemTemplateBooleans($billingId, $salesItemId, $jobExists)
 * This compiles all the label and configuration options into what the template can understand.
 *
 * @param $billingId -- the billing system id of the line item.
 * @param $salesItemId -- the sales item id of the line item.
 * @param $jobExists -- true if there exists a workflow for the line item.
 *
 * @return $status -- "000" if successful, nonzero otherwise.
 * @return $error -- "" if successful, nonempty otherwise.
 * @return $templateBooleans -- a list of configuration booleans & labels.
 */
function CompileLineItemTemplateBooleans($billingId, $salesItemId, $jobExists) {
    try {
        // Get values from the configuration.
        $setupConfig = GetSetupRecurring();
        $quantityLabelTypeVariance = GetQuantityLabelTypeVariance();
        $quantityLabelType = GetQuantityLabelType();
        $quantityLabelsVariance = GetQuantityLabelsVariance();
        $quantityLabels = GetQuantityLabels();
        $fixedVariableLabelTypeVariance = GetFixedVariableLabelTypeVariance();
        $fixedVariableLabelType = GetFixedVariableLabelType();
        $fixedVariableLabels = GetFixedVariableLabels();
        $fixedVariableLabelsVariance = GetFixedVariableLabelsVariance();
        $useDNID = GetUseDNID();
        $decimals = GetSaveDigits();
        $fullQuantityLabelTypes = GetFullLabelTypes();

        if (HCU_array_key_exists($billingId, $setupConfig)) {
            $setupLabel = $setupConfig[$billingId];
        } else {
            $setupLabel = null;
        }

        if (HCU_array_key_exists($billingId, $quantityLabelTypeVariance) && HCU_array_key_exists($salesItemId, $quantityLabelTypeVariance[$billingId])) {
            $quantityLabelType = $quantityLabelTypeVariance[$billingId][$salesItemId];
        } else if (HCU_array_key_exists($billingId, $quantityLabelType)) {
            $quantityLabelType = $quantityLabelType[$billingId];
        } else {
            $quantityLabelType = null;
        }

        if (HCU_array_key_exists($billingId, $quantityLabelsVariance) && HCU_array_key_exists($salesItemId, $quantityLabelsVariance[$billingId])) {
            $quantityLabels = $quantityLabelsVariance[$billingId][$salesItemId];
        } else if (HCU_array_key_exists($billingId, $quantityLabels)) {
            $quantityLabels = $quantityLabels[$billingId];
        } else {
            $quantityLabels = null;
        }

        if (HCU_array_key_exists($billingId, $fixedVariableLabelTypeVariance)
            && HCU_array_key_exists($salesItemId, $fixedVariableLabelTypeVariance[$billingId])) {
            $fixedVariableLabelType = $fixedVariableLabelTypeVariance[$billingId][$salesItemId];
        } else if (HCU_array_key_exists($billingId, $fixedVariableLabelType)) {
            $fixedVariableLabelType = $fixedVariableLabelType[$billingId];
        } else {
            $fixedVariableLabelType = null;
        }

        if (HCU_array_key_exists($billingId, $fixedVariableLabelsVariance) && HCU_array_key_exists($salesItemId, $fixedVariableLabelsVariance[$billingId])) {
            $fixedVariableLabels = $fixedVariableLabelsVariance[$billingId][$salesItemId];
        } else if (HCU_array_key_exists($billingId, $fixedVariableLabels)) {
            $fixedVariableLabels = $fixedVariableLabels[$billingId];
        } else {
            $fixedVariableLabels = null;
        }

        $useDNID = HCU_array_key_value($billingId, $useDNID);
        $decimals = HCU_array_key_value($billingId, $decimals);
        $decimals = $decimals === false ? 2 : intval($decimals); // Default is to use 2 decimals.

        // Compile boolean variables for the template
        $templateBooleans = array();

        $templateBooleans["showDNIDDiv"] = $setupLabel === "recurring" && $useDNID;
        $templateBooleans["decimals"] = $decimals;
        $templateBooleans["format"] = "{0:c" . $decimals . "}";
        $templateBooleans["showBillHalfDiv"] = $setupLabel === "setup";
        $templateBooleans["showWorkflowDiv"] = $setupLabel === "setup" && !$jobExists;

        $templateBooleans["showQuantity1Row"] = false;
        $templateBooleans["showQuantity2Row"] = false;
        $templateBooleans["showQuantityRangeRow"] = false;
        $templateBooleans["quantity1Label1"] = "";
        $templateBooleans["quantity1Label2"] = "";
        $templateBooleans["quantity2Label1"] = "";
        $templateBooleans["quantity2Label2"] = "";
        $templateBooleans["quantityRangeLabel1"] = "";

        if ($setupLabel === "recurring") {
            switch($quantityLabelType) {

                case "regular":
                    $templateBooleans["showQuantity1Row"] = true;
                    $templateBooleans["showQuantity2Row"] = true;

                    if ($quantityLabels == null) {
                        $templateBooleans["quantity1Label1"] = "Quantity 1:";
                        $templateBooleans["quantity2Label1"] = "Quantity 2:";
                    } else {
                        $templateBooleans["quantity1Label1"] = $quantityLabels[0];
                        $templateBooleans["quantity2Label1"] = $quantityLabels[1];
                    }
                    break;
                case "none":
                    break;
                case "no qty1":
                    $templateBooleans["showQuantity2Row"] = true;

                    if ($quantityLabels == null) {
                        $templateBooleans["quantity2Label1"] = "Quantity 2:";
                    } else {
                        $templateBooleans["quantity2Label1"] = $quantityLabels[0];
                        $templateBooleans["quantity2Label2"] = $quantityLabels[1];
                    }
                    break;
                case "no qty2":
                    $templateBooleans["showQuantity1Row"] = true;

                    if ($quantityLabels == null) {
                        $templateBooleans["quantity1Label1"] = "Quantity 1:";
                    } else {
                        $templateBooleans["quantity1Label1"] = $quantityLabels[0];
                        $templateBooleans["quantity1Label2"] = $quantityLabels[1];
                    }
                    break;
                case "range":
                    $templateBooleans["showQuantityRangeRow"] = true;

                    if ($quantityLabels == null) {
                        $templateBooleans["quantityRangeLabel1"] = "Range:";
                    } else {
                        $templateBooleans["quantityRangeLabel1"] = $quantityLabels[0];
                    }
                    break;
                case "left":
                    $templateBooleans["showQuantity1Row"] = true;
                    $templateBooleans["showQuantity2Row"] = true;

                    if ($quantityLabels == null) {
                        $templateBooleans["quantity1Label1"] = "Quantity 1:";
                        $templateBooleans["quantity2Label1"] = "Quantity 2:";
                    } else {
                        $templateBooleans["quantity1Label1"] = $quantityLabels[0];
                        $templateBooleans["quantity2Label1"] = $quantityLabels[1];
                    }
                    break;
                case "left and right":
                    $templateBooleans["showQuantity1Row"] = true;
                    $templateBooleans["showQuantity2Row"] = true;

                    if ($quantityLabels == null) {
                        $templateBooleans["quantity1Label1"] = "Quantity 1:";
                        $templateBooleans["quantity2Label1"] = "Quantity 2:";
                    } else {
                        $templateBooleans["quantity1Label1"] = $quantityLabels[0];
                        $templateBooleans["quantity1Label2"] = $quantityLabels[1];
                        $templateBooleans["quantity2Label1"] = $quantityLabels[2];
                        $templateBooleans["quantity2Label2"] = $quantityLabels[3];
                    }
                    break;
                case null:
                default:
                    $templateBooleans["showQuantity1Row"] = true;
                    $templateBooleans["showQuantity2Row"] = true;

                    $templateBooleans["quantity1Label1"] = "Quantity 1:";
                    $templateBooleans["quantity2Label1"] = "Quantity 2:";
                    break;
            }


        }

        $templateBooleans["showFixedRow"] = false;
        $templateBooleans["showVariableRow"] = false;
        $templateBooleans["fixedLabel"] = "";
        $templateBooleans["variableLabel"] = "";

        switch($fixedVariableLabelType) {

            case "regular":
            case "left":
                $templateBooleans["showFixedRow"] = true;
                $templateBooleans["fixedLabel"] = $fixedVariableLabels == null ? "Fixed:" : $fixedVariableLabels[0];

                if ($setupLabel === "recurring") {
                    $templateBooleans["showVariableRow"] = true;
                    $templateBooleans["variableLabel"] = $fixedVariableLabels == null ? "Variable:" : $fixedVariableLabels[1];
                }
                break;
            case "none":
                break;
            case "no fixed":
                if ($setupLabel === "recurring") {
                    $templateBooleans["showVariableRow"] = true;
                    $templateBooleans["variableLabel"] = $fixedVariableLabels == null ? "Variable:" : $fixedVariableLabels[0];
                }
                break;
            case "no variable":
                $templateBooleans["showFixedRow"] = true;
                $templateBooleans["fixedLabel"] = $fixedVariableLabels == null ? "Fixed:" : $fixedVariableLabels[0];
                break;
            case null:
            default:
                $templateBooleans["showFixedRow"] = true;
                $templateBooleans["fixedLabel"] = "Fixed:";

                if ($setupLabel === "recurring") {
                    $templateBooleans["showVariableRow"] = true;
                    $templateBooleans["variableLabel"] = "Variable:";
                }
                break;
        }

        $templateBooleans["showFrequencyDiv"] = $setupLabel === "recurring";
        $templateBooleans["showOverrideRow"] = true;

        $returnArray = array("status" => "000", "error" => "", "templateBooleans" => $templateBooleans);
    } catch (exception $e) {
        $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage());
    }
    return $returnArray;
}

/**
 * function ReadSalesOrderDetailInit($dbh, $sysenv, $Hu)
 * Gets all the data for static dropdownlists on the sales order detail page.
 *
 * @param $dbh -- the database connection.
 * @param $sysenv -- the system environment variables.
 * @param $Hu -- the logged in user.
 *
 * @return $status -- "000" if successful, nonzero otherwise.
 * @return $error -- "" if successful, nonempty otherwise.
 * @return $cu -- the Credit Union code.  (Displays at the top of the page.)
 * @return $cuName -- the Credit Union name.  (Displays at the top of the page.)
 * @return $operation -- "read"
 * @return $defaultProductOptions -- the starting bind options for the product.
 *    Whenever, the add product dialog is opened or the product is changed,
 *    this gets reset to this value.
 *
 * Product DDLs
 * ----------------------------
 * @return $ddls.unattachedJobs -- a full list of unattached jobs for the credit union.
 *    This is filtered to the product id in the add product dialog.
 * @return $ddls.productItems -- a list of products.
 *    This drives the values of the other dropdownlists.
 * @return $ddls.productWorkflowOptions -- This is the full list of workflow options.
 * @return $ddls.productOptions -- This is the product configurations available.
 *
 * Line Item DDLs
 * ----------------------------
 * @return $ddls.billingTemplateItems -- this is a list of default line item values.
 *    They are in the same format as "ReadSalesOrderLineItem."
 * @return $ddls.setupBillingOptions -- the options for the bill half dropdownlist.
 * @return $ddls.monthDDL -- the options for the billsOn1 and billsOn2 dropdownlists.
 * @return $ddls.frequencyDDL -- the options for the frequency dropdownlist.
 * @return $ddls.startMonthOptions -- the options for the start month/end month dropdownlists.
 */
function ReadSalesOrderDetailInit($dbh, $sysenv, $Hu) {
    try {
        extract($sysenv["BILL"]);
        if (!isset($salesOrderId) || trim($salesOrderId) == "") {
            throw new exception ("Contract id is required.", 1);
        }
        $salesOrderId = intval($salesOrderId);

        // Values for top of page
        // ----------------------------------

        $sql = "select trim(so.cu_id) \"cu\", trim(i.name) \"cuName\"
                from cubillsalesorder so
                inner join cuinfo i on trim(lower(so.cu_id)) = trim(lower(i.user_name))
                where so.id = " . intval($salesOrderId) . "
                order by so.cu_id";
        $sth = db_query($sql, $dbh);
        if (!$sth) {
            throw new exception ("Select query failed.", 2);
        }
        $cu = db_num_rows($sth) > 0 ? trim(db_fetch_row($sth, 0)[0]) : "";
        $cuName = db_num_rows($sth) > 0 ? trim(db_fetch_row($sth, 0)[1]) : "";

        // Values for product add
        // ------------------------------------
        $productAddDDLs = array();

        // Note: if there no product in the cuprodlist table, then it is using the first product name from the sales item table (in Alphabetical order)
        $sql = "select trim(si.prod_id) \"value\", trim(coalesce(p.home_cu_desc, si.billing_system_id)) \"text\"
                from (
                    select trim(isi.billing_system_id), trim(isi.prod_id), row_number() over(partition by isi.prod_id order by isi.billing_system_id)
                    from cubillsalesitem isi where isi.deleted_date is null
                ) si (billing_system_id, prod_id, number)
                left join cuprodlist p on trim(si.prod_id) = trim(p.home_cu_code)
                where si.number = 1
                order by 2";
        $sth = db_query($sql, $dbh);
        if (!$sth) {
            throw new exception ("Select query failed.", 3);
        }

        $unusedQueryResult = db_fetch_all($sth);
        $unusedQueryResult = $unusedQueryResult === false ? array() : $unusedQueryResult;

        $addProductOptions = GetAutomaticProductAddConfigOptions();
        $productsUsingDNID = GetUseDNIDProduct();
        $automaticProductAdds = GetAutomaticProductAdds();
        $setupConfig = GetSetupRecurring();
        $workflowDDL = GetWorkflowOptions();

        $sql = "select trim(t.issue) \"text\", t.track_id \"value\", trim(i.gen_for_product) \"product\"
                from cutrack t
                left join cuissues i on t.issue_id = i.issue_id
                where t.billing_feature_id = 0 and lower(t.user_name) = '" . prep_save(strtolower($cu), 12) . "'
                order by i.gen_for_product, t.issue, t.issue_id, t.track_id";
        $sth = db_query($sql, $dbh);
        if (!$sth) {
            throw new exception ("Select query failed.", 6);
        }
        $unattachedJobs = db_fetch_all($sth);
        $unattachedJobMap = array();

        if ($unattachedJobs !== false) {
            $currentProduct = "";
            foreach($unattachedJobs as $record) {
                if ($record["product"] != $currentProduct) {
                    $currentProduct = $record["product"];
                    $unattachedJobMap[$currentProduct] = array();
                }
                $unattachedJobMap[$currentProduct][] = array("text" => $record["text"], "value" => $record["value"]);
            }
        } else {
            $unattachedJobs = array();
        }

        $productAddDDLs["unattachedJobs"] = $unattachedJobs;

        $sql = "select trim(sod.prod_id) \"product\"
                from cubillsalesorderdetail sod
                where sales_order_id = " . intval($salesOrderId);
        $sth = db_query($sql, $dbh);
        if (!$sth) {
            throw new exception ("Select query failed.", 8);
        }

        $products = db_fetch_all($sth);
        $productMap = array();
        if ($products !== false) {
            foreach($products as $productRow) {
                $productMap[$productRow["product"]] = true;
            }
        }

        $productOptions = array();
        $productWorkflowOptions = array();

        foreach($unusedQueryResult as $record) {
            $optionArray = array();
            $product = trim($record["value"]);
            $options = HCU_array_key_value($product, $addProductOptions);
            $productHasSetup = false; // If product has configurations, check it there.
            $record["useDNID"] = HCU_array_key_value($product, $productsUsingDNID);
            $record["productExists"] = HCU_array_key_value($product, $productMap);
            $record["hasOptions"] = $options !== false;

            if ($options !== false) {
                foreach($options as $key => $value) {
                    $configHasSetup = false;
                    $itemArray = HCU_array_key_value($key, $automaticProductAdds);
                    if ($itemArray !== false) {
                        foreach($itemArray as $item) {
                            $setupLabel = HCU_array_key_value($item["billingId"], $setupConfig);
                            if ($setupLabel === "setup") {
                                $configHasSetup = true;
                                break;
                            }
                        }
                    }
                    $productOptions[] = array("text" => $value, "value" => $key, "hasSetup" => $configHasSetup, "product" => $product);
                }
            } else {
                $itemArray = HCU_array_key_value($product, $automaticProductAdds);
                if ($itemArray !== false) {
                    foreach($itemArray as $item) {
                        $setupLabel = HCU_array_key_value($item["billingId"], $setupConfig);
                        if ($setupLabel === "setup") {
                            $productHasSetup = true;
                            break;
                        }
                    }
                }
            }

            $record["hasUnattachedJobs"] = HCU_array_key_exists($record["value"], $unattachedJobMap);

            foreach($workflowDDL as $workflowRecord) {

                if ($record["hasUnattachedJobs"] || $workflowRecord["value"] !== "associate") {
                    $productWorkflowOptions[] = array("value" => $workflowRecord["value"], "text" => $workflowRecord["text"], "product" => $product);
                }
            }

            $record["hasSetup"] = $productHasSetup;
            $productArray[] = $record;
        }

        $productAddDDLs["productItems"] = $productArray;
        $productAddDDLs["productWorkflowOptions"] = $productWorkflowOptions;
        $productAddDDLs["productOptions"] = $productOptions;

        $defaultProductOptions = GetDefaultProductOptions($productAddDDLs);

        // Values for line items
        // ------------------------------------
        $lineItemDDLs = array();

        $sql = "select trim(si.display_name) \"salesItemText\", si.id \"salesItemValue\", trim(si.prod_id) \"productId\",
                si.billing_template \"template\", trim(si.billing_system_id) \"billingId\"
                from cubillsalesitem si
                where si.deleted_date is null and si.billing_template is not null and trim(from si.billing_template) <> ''
                order by si.display_name";
        $sth = db_query($sql, $dbh);
        if (!$sth) {
            throw new exception ("Select query failed.", 5);
        }
        $templateItems = db_fetch_all($sth);
        $templateItems = $templateItems === false ? array() : $templateItems;

        $expandedResults = array();
        foreach($templateItems as $rawRecord) {
            $lineItemData = array("featureDetailId" => 0, "billingStatus" => 1);

            $template = HCU_JsonDecode($rawRecord["template"]);
            unset($record["template"]);
            // Ignore description here.  Use the description on the sales_item table.
            $lineItemData["fixed"] = floatval($template["fixed"]);
            $lineItemData["variable"] = floatval($template["variable"]);
            $lineItemData["qty1"] = intval($template["qty1"]);
            $lineItemData["qty2"] = intval($template["qty2"]);
            // Positive infinity is marked as null in the application (with a placeholder) and -1 in the database.
            $lineItemData["qty2"] = $lineItemData["qty2"] == -1 ? null : $lineItemData["qty2"];
            $lineItemData["aDNID"] = HCU_array_key_exists("aDNID", $template) ? $template["aDNID"] : "";
            $lineItemData["overrideDescription"] = trim($template["descr"]);
            $lineItemData["actualDescription"] = $rawRecord["salesItemText"];
            $lineItemData["partiallyBilledAmount"] = 0;
            $lineItemData["partiallyBilledDate"] = null;

            $billingId = $rawRecord["billingId"];
            $salesItemId = $rawRecord["salesItemValue"];

            $results = CompileLineItemTemplateBooleans($billingId, $salesItemId, false);
            if ($results["status"] !== "000") {
                throw new exception ("Configuration failed.", 7);
            }
            $configuration = $results["templateBooleans"];

            $bindOptions = GetDefaultLineItemBindOptions();

            if ($configuration["showQuantityRangeRow"]) {
                $bindOptions["qtyRangeVisible"] = true; // Needed to save infinity correctly.
            }

            $workflowDDL = array();
            $workflowUnattachedJobs = array();

            if (HCU_array_key_exists($billingId, $setupConfig)) {
                $setupLabel = $setupConfig[$billingId];
            } else {
                $setupLabel = null;
            }

            if ($setupLabel === "setup") {
                $workflowDDL = GetWorkflowOptions();

                $workflowUnattachedJobs = HCU_array_key_value($rawRecord["productId"], $unattachedJobMap);
                $workflowUnattachedJobs = $workflowUnattachedJobs === false ? array() : $workflowUnattachedJobs;
                if (count($workflowUnattachedJobs) == 0) {
                    $newWorkflowDDL = array();
                    foreach($workflowDDL as $record) {

                        if ($record["value"] !== "associate") {
                            $newWorkflowDDL[] = $record;
                        }
                    }

                    $workflowDDL = $newWorkflowDDL;
                } else {
                    $bindOptions["unattachedJobValue"] = intval($workflowUnattachedJobs[0]["value"]);
                }
            }

            $ddls = array("workflows" => $workflowDDL, "workflowUnattachedJobs" => $workflowUnattachedJobs);

            $record = array("salesItemText" => $rawRecord["salesItemText"], "salesItemValue" => $rawRecord["salesItemValue"],
                            "configuration" => $configuration, "lineItemData" => $lineItemData, "bindOptions" => $bindOptions,
                            "productId" => $rawRecord["productId"], "ddls" => $ddls);

            $expandedResults[] = $record;
        }

        $lineItemDDLs["billingTemplateItems"] = $expandedResults;
        $lineItemDDLs["setupBillingOptions"] = GetSetupBillingOptions();

        // Frequency
        $lineItemDDLs["monthDDL"] = GetMonthOptions();
        $lineItemDDLs["frequencyDDL"] = GetFrequencyOptions();

        $monthOptions = array();
        $timezone = new DateTimeZone("America/Denver");
        $monthToAdd = new DateTime("now", $timezone);
        $year = intval($monthToAdd->format("Y"));
        $month = intval($monthToAdd->format("m"));
        $monthToAdd = DateTime::createFromFormat("Y-m-d|", "$year-$month-01");
        $monthToAdd->modify("-3 months");

        for($i = 0; $i != 9; $i++) {
            $text = $monthToAdd->format("m/Y");
            $value = $monthToAdd->format("Y-m-d");
            $monthOptions[] = array("text" => $text, "value" => $value);
            $monthToAdd->modify("+1 month");
        }

        $lineItemDDLs["startMonthOptions"] = $monthOptions;


        $ddls = $productAddDDLs + $lineItemDDLs;

        $returnArray = array("status" => "000", "error" => "", "operation" => "read", "cu" => $cu, "cuName" => $cuName, "ddls" => $ddls,
                             "defaultProductOptions" => $defaultProductOptions);
    } catch (exception $e) {
        $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage());
    }
    return $returnArray;
}

/**
 * function ReadSalesOrderDetailWarnings($dbh, $sysenv, $Hu)
 * Gets the warnings for the sales order detail page.
 *
 * @param $dbh -- the database connection.
 * @param $sysenv -- the system environment variables.
 * @param $Hu -- the logged in user.
 *
 * @return $status -- "000" if successful, nonzero otherwise.
 * @return $error -- "" if successful, nonempty otherwise.
 * @return $warnings -- the warnings to show.
 */
function ReadSalesOrderDetailWarnings($dbh, $sysenv, $Hu) {
    try {
        extract($sysenv["BILL"]);
        if (!isset($salesOrderId) || trim($salesOrderId) == "") {
            throw new exception ("Contract id is required.", 1);
        }
        $salesOrderId = intval($salesOrderId);

        $results = CheckForOverlapGaps($dbh, $salesOrderId);
        if ($results["status"] !== "000") {
            throw new exception ($results["error"], 2);
        }
        $rawWarnings = $results["warnings"];
        $warnings = array();

        foreach($rawWarnings as $productId => $newWarnings) {
            $warnings = array_merge($warnings, $newWarnings);
        }

        $returnArray = array("status" => "000", "error" => "", "warnings" => $warnings);

    } catch (exception $e) {
        $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage());
    }
    return $returnArray;
}

/**
 * function ValidateProductSelected ($product, $validProductIds)
 * @param $product -- the product record to verify.
 * @param $validProductIds -- a list of valid product ids in the system.
 *
 * @return $status -- "000" if successful, nonzero otherwise.
 * @return $error -- "" if successful, nonempty otherwise.
 * @return $productAdds -- a list of line items to create for product.
 * @return $productId -- the product id extracted from $product.
 */
function ValidateProductSelected ($product, $validProductIds) {
    try {
        $autoAdds = GetAutomaticProductAdds();
        $autoAddOptions = GetAutomaticProductAddConfigOptions();
        $setupConfig = GetSetupRecurring();
        $useDNID = GetUseDNID();
        $rawWorkflowOptions = GetWorkflowOptions();

        $rawSetupBillingOptions = GetSetupBillingOptions();
        $setupBillingOptions = array();

        foreach($rawSetupBillingOptions as $record) {
            $setupBillingOptions[] = $record["value"];
        }

        foreach($rawWorkflowOptions as $record) {
            $workflowOptions[] = $record["value"];
        }
        $workflowOptions[] = "none"; // Ensure that this option is valid as well.

        $productId = HCU_array_key_value("productId", $product);
        if ($productId === false) {
            throw new exception ("Product id is not found.", 1);
        }
        if (!in_array($productId, $validProductIds)) {
            throw new exception ("Product id is not known.", 2);
        }
        $productOption = HCU_array_key_value("productOption", $product);
        if ($productOption === false) {
            throw new exception ("Product option is not found.", 3);
        }

        $productOptions = HCU_array_key_value($productId, $autoAddOptions);
        $productAdds = array();
        if ($productOptions !== false) {
            $productAdds = HCU_array_key_value($productOption, $autoAdds);
            if ($productAdds == false) {
                throw new exception ("Product add array is not found.", 4);
            }
        } else {
            $productAdds = HCU_array_key_value($productId, $autoAdds);
            if ($productAdds == false) {
                throw new exception ("Product add array is not found.", 5);
            }
        }

        foreach($productAdds as $i => $addItem) {
            $billingId = $addItem["billingId"];
            if (HCU_array_key_value($billingId, $setupConfig) === "setup") {
                $productAdds[$i]["isSetup"] = true;

                $partiallyBilledDate = HCU_array_key_value("partiallyBilledDate", $product);
                if ($partiallyBilledDate === false) {
                    throw new exception ("partiallyBilledDate is required.", 7);
                }

                $billHalfValue = HCU_array_key_value("billHalfValue", $product);
                if ($billHalfValue === false) {
                    throw new exception ("billHalfValue is required.", 8);
                }

                $workflowValue = HCU_array_key_value("workflowValue", $product);
                if ($workflowValue === false) {
                    throw new exception ("WorkflowValue is required.", 9);
                }

                $unattachedJobValue = HCU_array_key_value("unattachedJobValue", $product);
                if ($unattachedJobValue === false) {
                    throw new exception ("UnattachedJobValue is required.", 10);
                }

                if (!in_array($billHalfValue, $setupBillingOptions)) {
                    throw new exception ("BillHalfValue is invalid.", 11);
                }

                if (!in_array($workflowValue, $workflowOptions)) {
                    throw new exception ("WorkflowValue is invalid.", 12);
                }

                $productAdds[$i]["billHalfValue"] = $billHalfValue;
                $productAdds[$i]["workflowValue"] = $workflowValue;
                $productAdds[$i]["unattachedJobValue"] = $unattachedJobValue;
                $productAdds[$i]["partiallyBilledDate"] = $partiallyBilledDate;
            } else {
                $productAdds[$i]["isSetup"] = false;
            }

            if (HCU_array_key_value($billingId, $useDNID)) {
                $productAdds[$i]["useDNID"] = false;

                $DNID = HCU_array_key_value("aDNID", $product);
                if ($DNID === false) {
                    throw new exception ("DNID is not found.", 6);
                }
                $productAdds[$i]["DNID"] = $DNID;
            } else {
                $productAdds[$i]["useDNID"] = false;
            }
        }

        $returnArray = array("status" => "000", "error" => "", "productAdds" => $productAdds, "productId" => $productId);
    } catch (exception $e) {

        $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage());
    }
    return $returnArray;
}

/**
 * function CreateSalesOrderDetails ($dbh, $sysenv, $staffId)
 * This function creates sales order details with its line items.
 *
 * @param $dbh -- the database connection.
 * @param $sysenv -- the system environment variables.
 * @param $Hu -- the logged in user.
 *
 * @return $status -- "000" if successful, nonzero otherwise.
 * @return $error -- "" if successful, nonempty otherwise.
 * @return $type -- "modify."
 * @return $orderDetailIds -- list of order detail ids created.
 *    This is used to auto-open these details in the pending grid.
 * @return $workflowStatuses -- list of product names and created boolean
 *    for products that should have created a workflow.
 */
function CreateSalesOrderDetails ($dbh, $sysenv, $staffId) {
    try {
        $taskEmails = array();
        $issueEmails = array();
        $workflowStatuses = array();
        $orderDetailIds = array();

        extract($sysenv["BILL"]);

        $salesOrderId = isset($salesOrderId) ? intval($salesOrderId) : 0;
        $productsSelected = isset($productsSelected) ? trim($productsSelected) : "";

        if ($salesOrderId == 0) {
            throw new exception("Sales order id is required.", 1);
        }
        if ($productsSelected == "") {
            throw new exception("Products selected is required.", 2);
        }

        // Here, get values from the database that are needed here for labels.
        $sql = "select trim(so.cu_id), trim(i.name) \"cuName\"
                from cubillsalesorder so
                left join cuinfo i on lower(so.cu_id) = lower(i.user_name)
                where so.id = " . intval($salesOrderId);
        $sth = db_query($sql, $dbh);
        if (!$sth || db_num_rows($sth) <= 0) {
            throw new exception ("Select query failed.", 3);
        }
        list($cu, $cuName) = db_fetch_row($sth, 0);

        $sql = "select distinct trim(prod_id) \"productId\"
                from cubillsalesitem";
        $sth = db_query($sql, $dbh);
        if (!$sth) {
            throw new exception ("Select query failed.", 4);
        }
        $validProductIds = array();
        $results = db_fetch_all($sth);
        $results = $results === false ? array() : $results;
        foreach($results as $record) {
            $validProductIds[] = $record["productId"];
        }

        $products = HCU_JsonDecode($productsSelected);
        if (!is_array($products)) {
            throw new exception ("Products are encoded correctly.", 5);
        }

        $validatedProducts = array();
        foreach($products as $product) {
            $results = ValidateProductSelected ($product, $validProductIds);
            if ($results["status"] !== "000") {
                throw new exception ("Product is not valid.", 6);
            }

            $validatedProducts[] = array("productId" => $results["productId"], "productAdds" => $results["productAdds"]);
        }

        if (!db_work( $dbh, HOMECU_WORK_BEGIN )) {
            throw new exception("begin query failed.", 7);
        }

        foreach($validatedProducts as $validatedProductsRecord) {
            $productId = $validatedProductsRecord["productId"];
            $productAdds = $validatedProductsRecord["productAdds"];

            $sql = "select trim(home_cu_desc)
                    from cuprodlist
                    where home_cu_code = '" . prep_save($productId, 10) . "'";
            $sth = db_query($sql, $dbh);
            if (!$sth || db_num_rows($sth) <= 0) {
                throw new exception ("Select query failed.", 23);
            }
            list($prodText) = db_fetch_row($sth, 0);

            $sql = "select sod.id from cubillsalesorderdetail sod
                    where sod.prod_id = '" . prep_save($productId, 10) . "' and sod.sales_order_id = " . intval($salesOrderId);
            $sth = db_query($sql, $dbh);
            if (!$sth) {
                throw new exception("id query failed.", 8);
            }
            $orderDetailId = db_num_rows($sth) >= 0 ? intval(db_fetch_row($sth, 0)[0]) : 0;

            if ($orderDetailId === 0) {

                $sql = "insert into cubillsalesorderdetail (sales_order_id, prod_id, last_modified_by, last_modified_date)
                        values (" . intval($salesOrderId) . ", '" . prep_save($productId, 10) . "', '" . prep_save($staffId, 12) . "', now())
                        returning id";
                $sth = db_query($sql, $dbh);
                if (!$sth || db_num_rows($sth) <= 0) {
                    throw new exception ("Insert query failed.", 9);
                }
                $orderDetailId = intval(db_fetch_row($sth, 0)[0]);
            } else {
                $sql = "update cubillsalesorderdetail
                        set last_modified_by = '" . prep_save($staffId, 12) . "', last_modified_date = now()
                        where id = " . intval($orderDetailId);
                $sth = db_query($sql, $dbh);
                if (!$sth) {
                    throw new exception ("Update query failed.", 10);
                }
            }

            $orderDetailIds[] = $orderDetailId;

            $nextMonth = new DateTime();
            $m = $nextMonth->format('m');
            $Y = $nextMonth->format('Y');
            $nextMonth->setDate($Y, $m + 1, 1);
            $nextMonth = $nextMonth->format("Y-m-d");

            foreach($productAdds as $productAddRecord) {
                $isSetup = $productAddRecord["isSetup"];

                $salesItemId = intval($productAddRecord["salesItemId"]);

                if ($isSetup &&  $productAddRecord["billHalfValue"] === "nosetup") {
                    continue; // There is a setup record but do not create it.
                }

                $fieldsToSave = array();

                $fieldsToSave["sales_order_detail_id"] = intval($orderDetailId);
                $fieldsToSave["sales_item_id"] = intval($salesItemId);
                $fieldsToSave["start_date"] = "'" . prep_save($nextMonth) . "'";
                $billingStatus = 1;

                $sql = "select billing_template from cubillsalesitem
                        where id = " . intval($salesItemId);
                $sth = db_query($sql, $dbh);
                if (!$sth || db_num_rows($sth) <= 0) {
                    throw new exception ("Select query failed.", 12);
                }
                $rawBillingTemplate = db_fetch_row($sth, 0)[0];
                $rawBillingTemplate = HCU_JsonDecode($rawBillingTemplate);
                $billingTemplate = array();
                $descr = HCU_array_key_value("descr", $rawBillingTemplate);
                $descr = $descr === false ? "" : trim($descr);
                $fixed = HCU_array_key_value("fixed", $rawBillingTemplate);
                $fixed = $fixed === false ? "0.000" : strval(floatval($fixed));
                $variable = HCU_array_key_value("variable", $rawBillingTemplate);
                $variable = $variable === false ? "0.000" : strval(floatval($variable));
                $qty1 = HCU_array_key_value("qty1", $rawBillingTemplate);
                $qty1 = $qty1 === false ? "0" : strval(intval($qty1));
                $qty2 = HCU_array_key_value("qty2", $rawBillingTemplate);
                $qty2 = $qty2 === false ? "0" : strval(intval($qty2));

                $billingTemplate = array("descr" => $descr, "fixed" => $fixed, "variable" => $variable, "qty1" => $qty1, "qty2" => $qty2);

                if ($productAddRecord["useDNID"]) {
                    $billingTemplate["aDNID"] = $productAddRecord["DNID"];
                }

                $fieldsToSave["billing_template"] = "'" . prep_save(HCU_JsonEncode($billingTemplate)) . "'";

                if ($isSetup) {
                    $billHalfValue = $productAddRecord["billHalfValue"];
                    $workflowValue = $productAddRecord["workflowValue"];
                    $unattachedJobValue = $productAddRecord["unattachedJobValue"];

                    $fieldsToSave["partially_billed_date"] = "null";
                    $fieldsToSave["partially_billed_amount"] = "null";

                    $partiallyBilledDate = $productAddRecord["partiallyBilledDate"];

                    switch($productAddRecord["billHalfValue"]) {
                        case "upfront":
                            $billingStatus = 2;
                            $fieldsToSave["start_date"] = "'" . prep_save($partiallyBilledDate) . "'";
                            $fieldsToSave["end_date"] = "'" . prep_save($partiallyBilledDate) . "'";
                            $fieldsToSave["workflow_completed"] = "'Y'";
                            break;
                        case "future":
                            $billingStatus = 0;
                            $fieldsToSave["bill_half_boolean"] = "'Y'";
                            $fieldsToSave["partially_billed_date"] = "'" . prep_save($partiallyBilledDate) . "'";
                            break;
                        case "partial":
                            $partiallyBilledAmount = round($fixed / 2.0, 2);
                            $fieldsToSave["partially_billed_date"] = "'" . prep_save($partiallyBilledDate) . "'";
                            $fieldsToSave["partially_billed_amount"] = floatval($partiallyBilledAmount);
                            break;
                        case "full":
                            break;
                        default:
                            throw new exception ("Bill Half value is invalid.", 11); // Should get here.  Should be validated above.
                            break;

                    }

                    if ($workflowValue === "without") {
                        $billingStatus = 2;
                        $fieldsToSave["start_date"] = "'" . prep_save($partiallyBilledDate) . "'";
                        $fieldsToSave["end_date"] = "'" . prep_save($partiallyBilledDate) . "'";
                    }

                }

                $sql = "insert into cubillfeaturedetail (" . implode(", ", array_keys($fieldsToSave)) . ")
                        values (" . implode(", ", array_values($fieldsToSave)) . ")
                        returning id";
                $sth = db_query($sql, $dbh);
                if (!$sth || db_num_rows($sth) <= 0) {
                    throw new exception ("Insert query failed.", 13);
                }
                $featureDetailId = intval(db_fetch_row($sth, 0)[0]);

                if ($isSetup && !in_array($workflowValue, array("none", "without"))) {

                    switch($workflowValue) {
                        case "create":
                            $sql = "select 'FOUND' from cutrack where billing_feature_id = " . intval($featureDetailId);
                            $sth = db_query($sql, $dbh);
                            if (!$sth) {
                                throw new exception("Cutrack found query failed.", 14);
                            }
                            if (db_num_rows($sth) > 0) {
                                throw new exception("Workflow already exists.", 15);
                            }

                            $results = SetupWorkflow($dbh, $productId, $prodText, $featureDetailId, $cu, $cuName, $staffId);
                            if ($results["status"] !== "000") {
                                throw new exception ($results["error"], 16);
                            }
                            $taskEmails = array_merge($taskEmails, $results["data"]["taskEmails"]);
                            $issueEmails = array_merge($issueEmails, $results["data"]["issueEmails"]);
                            $workflowCreated = $results["data"]["workflowCreated"];
                            $workflowStatuses[] = array("created" => $workflowCreated, "text" => $prodText);

                            // Update billingStatus 4 (Pending workflow but no workflow created) to 1 (pending) if it doesn't need to be billed half.
                            // Update billingStatus 4 (Pending workflow but no workflow created) to 0 (bill half) if it does need to be billed half.
                            // Update billingStatus 5 (Pending workflow, no workflow created, and billed half) to 1 (pending.)
                            if ($workflowCreated && in_array($billingStatus, array(4,5))) {
                                $billingStatus = $billingStatus == 5 || $billHalfValue !== "partial" ? 1 : 0;
                            } else if (!$workflowCreated) {

                                // There was no workflow created but we still want to bill it upfront.
                                if ($billHalfValue == "upfront") {
                                    $billingStatus = 2;
                                } else {
                                    $billingStatus = 4;
                                }
                            }
                            break;
                        case "associate":
                            if ($unattachedJobValue > 0) {
                                throw new exception ("Job id is required.", 17);
                            }
                            $sql = "update cutrack set billing_feature_id = " . intval($featureDetailId) . " where track_id = " . intval($unattachedJobValue);
                            $sth = db_query($sql, $dbh);
                            if (!$sth) {
                                throw new exception("Update cutrack query failed.", 18);
                            }

                            // Need to update the billing_ids of the triggered sales items.
                            $sql = "update cutrackitem set billing_item_id = coalesce(fd.id, 0) from cutrackitem ti
                                    inner join cutrack t on ti.track_id = t.track_id and t.track_id = " . intval($$unattachedJobValue) . "
                                    left join cuissuesitem ii on ti.issue_item_id = ii.item_id and ii.issue_id = t.issue_id
                                    left join cubillfeaturedetail tfd on tfd.id = " . intval($featureDetailId) . "
                                    left join cubillfeaturedetail fd on fd.sales_order_detail_id = tfd.sales_order_detail_id and fd.sales_item_id = ii.sales_item_id
                                    where ti.trackitem_id = cutrackitem.trackitem_id";
                            $sth = db_query($sql, $dbh);
                            if (!$sth) {
                                throw new exception("Update cutrackitem query failed.", 19);
                            }

                            // Update billingStatus 4 (Pending workflow but no workflow created) to 1 (pending) if it doesn't need to be billed half.
                            // Update billingStatus 4 (Pending workflow but no workflow created) to 0 (bill half) if it does need to be billed half.
                            // Update billingStatus 5 (Pending workflow, no workflow created, and billed half) to 1 (pending.)
                            if (in_array($billingStatus, array(4,5))) {
                                $billingStatus = $billingStatus == 5 || $billHalfValue !== "partial" ? 1 : 0;
                            }
                            break;
                        case "without":
                        case "none":
                            break;
                        default:
                            throw new exception ("Workflow option is invalid.", 20); // Should be caught above in validation.
                    }
                }

                $sql = "update cubillfeaturedetail set billing_status = " . intval($billingStatus) . " where id = " . intval($featureDetailId);
                $sth = db_query($sql, $dbh);
                if (!$sth) {
                    throw new exception("Update cubillfeaturedetail query failed.", 21);
                }
            }
        }

        if (!db_work( $dbh, HOMECU_WORK_COMMIT )) {
            throw new exception("commit work failed.", 22);
        }

        foreach($taskEmails as $taskEmail) {
            CreateTaskEmail($taskEmail["to"], $taskEmail["subject"], $taskEmail["text"], $taskEmail["issue"], $taskEmail["title"], $taskEmail["description"],
                            $cuName, $cu, $taskEmail["issueId"]);
        }
        foreach($issueEmails as $issueEmail) {
            CreateIssueEmail($issueEmail["to"], $issueEmail["subject"], $issueEmail["text"], $issueEmail["issueName"], "Active", $cuName, $cu,
                             $issueEmail["issueId"]);
        }

        $returnArray = array("status" => "000", "error" => "", "type" => "modify",
                             "orderDetailIds" => $orderDetailIds, "workflowStatuses" => $workflowStatuses);
    } catch (exception $e) {
        db_work( $dbh, HOMECU_WORK_ROLLBACK ); // Got greater problems if this fails.
        $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage(), "type" => "modify");
    }
    return $returnArray;
}

/**
 * function ModifyBillingFeature($dbh, $sysenv, $staffId, $isAdd, $isActive)
 * This modifies a line item.
 *
 * @param $dbh -- the database connection.
 * @param $sysenv -- the system environment variables.
 * @param $staffId -- the logged in user.
 * @param $isAdd -- if true, then add the line item.
 *    Otherwise, update the line item.
 * @param $isActive -- if true, this is from the active grid.
 *    In this case, make sure to not create workflow,
 *    or change bill half or frequency settings.
 *
 * @return $status -- "000" if successful, nonzero otherwise.
 * @return $error -- "" if successful, nonempty otherwise.
 * @return $type -- "modify."
 * @return $orderDetailIds -- list of order detail ids created.
 *    This is used to auto-open these details in the pending grid.
 * @return $workflowStatuses -- list of product names and created boolean
 *    for products that should have created a workflow.
 */
function ModifyBillingFeature($dbh, $sysenv, $staffId, $isAdd, $isActive) {
    try {
        $savepointBilling = "billingFeatureUpdate_Billing";
        $savepointWorkflow = "billingFeatureUpdate_Workflow";
        $fieldsToSave = array();

        extract($sysenv["BILL"]);

        // Id
        // ------------------------------
        $featureDetailId = isset($featureDetailId) ? intval($featureDetailId) : 0;

        // Brand new record.
        if ($isAdd) {
            $billingStatus = 1; // Start with "pending".

            $orderDetailId = isset($orderDetailId) ? trim($orderDetailId) : "";
            $salesItemId = isset($salesItemId) ? trim($salesItemId) : "";

            if ($orderDetailId == "") {
                throw new exception ("orderDetailId is required.", 18);
            }
            if ($salesItemId == "") {
                throw new exception ("salesItemId is required.", 19);
            }

            // Here, get values from the database that are needed here for labels.
            $sql = "select trim(si.billing_system_id), trim(si.display_name),
                    trim(so.cu_id), trim(i.name) \"cuName\", trim(sod.prod_id), trim(p.home_cu_desc)
                    from cubillsalesorderdetail sod
                    inner join cubillsalesorder so on sod.sales_order_id = so.id
                    inner join cubillsalesitem si on si.id = " . intval($salesItemId) . "
                    left join cuinfo i on lower(so.cu_id) = lower(i.user_name)
                    left join cuprodlist p on sod.prod_id = p.home_cu_code
                    where sod.id = " . intval($orderDetailId);
            $sth = db_query($sql, $dbh);

            if (!$sth) {
                throw new exception ("Query failed.", 2);
            }
            if (db_num_rows($sth) <= 0) {
                throw new exception ("billingFeatureId is not found.", 17);
            }
            list($billingId, $originalDescription, $cu, $cuName, $prodId, $prodText) = db_fetch_row($sth, 0);

        // Update existing record.
        } else {
            if ($featureDetailId == 0) {
                throw new exception("billingFeatureId is required.", 1);
            }

            // Here, get values from the database that are needed here for labels.
            $sql = "select fd.billing_status, trim(si.billing_system_id), fd.sales_order_detail_id, trim(si.display_name),
                    trim(so.cu_id), trim(i.name) \"cuName\", trim(sod.prod_id), trim(p.home_cu_desc)
                    from cubillfeaturedetail fd
                    inner join cubillsalesorderdetail sod on fd.sales_order_detail_id = sod.id
                    inner join cubillsalesorder so on sod.sales_order_id = so.id
                    inner join cubillsalesitem si on fd.sales_item_id = si.id
                    left join cuinfo i on lower(so.cu_id) = lower(i.user_name)
                    left join cuprodlist p on sod.prod_id = p.home_cu_code
                    where fd.id = " . intval($featureDetailId);
            $sth = db_query($sql, $dbh);

            if (!$sth) {
                throw new exception ("Query failed.", 2);
            }
            if (db_num_rows($sth) <= 0) {
                throw new exception ("billingFeatureId is not found.", 17);
            }
            list($billingStatus, $billingId, $orderDetailId, $originalDescription, $cu, $cuName, $prodId, $prodText) = db_fetch_row($sth, 0);
        }

        // Template
        // -------------------------------
        $overrideDescription = isset($overrideDescription) ? trim($overrideDescription) : "";
        $fixed = isset($fixed) ? floatval($fixed) : 0.00;
        $variable = isset($variable) ? floatval($variable) : 0.00;
        $qty1 = isset($qty1) ? intval($qty1) : 0;
        $qty2 = isset($qty2) ? intval($qty2) : 0;
        $aDNID = isset($aDNID) ? trim($aDNID) : "";

        $billingTemplate = array();

        $billingTemplate["descr"] = $overrideDescription == "" || $overrideDescription == $originalDescription ? "" : $overrideDescription;
        $billingTemplate["fixed"] = $fixed;
        $billingTemplate["variable"] = $variable;
        $billingTemplate["qty1"] = $qty1;
        $billingTemplate["qty2"] = $qty2;
        $aDNID == "" ? null : $billingTemplate["aDNID"] = $aDNID;

        $fieldsToSave["billing_template"] = "'" . prep_save(HCU_JsonEncode($billingTemplate)) . "'";

        $setupConfig = GetSetupRecurring();
        if (HCU_array_key_exists($billingId, $setupConfig)) {
            $setupLabel = $setupConfig[$billingId];
        } else {
            $setupLabel = null;
        }

        if ($setupLabel === "setup" && !$isActive) {
            // Bill Half & workflow
            // ------------------------------
            $billHalfValue = isset($billHalfValue) ? trim($billHalfValue) : "";
            $workflowValue = isset($workflowValue) ? trim($workflowValue) : "";
            $unattachedJobValue = isset($unattachedJobValue) ? trim($unattachedJobValue) : "";
            $partiallyBilledAmount = isset($partiallyBilledAmount) ? trim($partiallyBilledAmount) : "";
            $partiallyBilledDate = isset($partiallyBilledDate) ? trim($partiallyBilledDate) : "";

            if ($billHalfValue == "") {
                throw new exception ("Bill Half option is required.", 3);
            }
            if ($workflowValue == "") {
                throw new exception ("Workflow option is required.", 4);
            }

            $fieldsToSave["partially_billed_date"] = "null";
            $fieldsToSave["partially_billed_amount"] = "null";
            switch($billHalfValue) {
                case "upfront":
                    if ($partiallyBilledDate == "") {
                        throw new exception ("Partially billed date is required.", 5);
                    }
                    $billingStatus = 2;
                    $fieldsToSave["start_date"] = "'" . prep_save($partiallyBilledDate) . "'";
                    $fieldsToSave["end_date"] = "'" . prep_save($partiallyBilledDate) . "'";
                    $fieldsToSave["workflow_completed"] = "'Y'";
                    break;
                case "future":
                    if ($partiallyBilledDate == "") {
                        throw new exception ("Partially billed date is required.", 6);
                    }
                    $billingStatus = 0;
                    $fieldsToSave["bill_half_boolean"] = "'Y'";
                    $fieldsToSave["partially_billed_date"] = "'" . prep_save($partiallyBilledDate) . "'";
                    break;
                case "partial":
                    if ($partiallyBilledDate == "") {
                        throw new exception ("Partially billed date is required.", 7);
                    }
                    if ($partiallyBilledAmount == "") {
                        throw new exception ("Partially billed amount is required.", 8);
                    }
                    $fieldsToSave["partially_billed_date"] = "'" . prep_save($partiallyBilledDate) . "'";
                    $fieldsToSave["partially_billed_amount"] = floatval($partiallyBilledAmount);
                    break;
                case "full":
                    break;
                default:
                    throw new exception ("Bill Half value is invalid.", 9);
                    break;

            }

            if ($workflowValue === "without") {

                $billingStatus = 2;
                $fieldsToSave["start_date"] = "'" . prep_save($partiallyBilledDate) . "'";
                $fieldsToSave["end_date"] = "'" . prep_save($partiallyBilledDate) . "'";
            }
        } else if ($setupLabel === "recurring" && !$isActive) {
            // Frequency
            // ------------------------
            $frequencyValue = isset($frequencyValue) ? trim($frequencyValue) : "";
            $billsOn1Value = isset($billsOn1Value) ? trim($billsOn1Value) : "";
            $billsOn2Value = isset($billsOn2Value) ? trim($billsOn2Value) : "";
            if ($frequencyValue == "") {
                throw new exception ("Frequency is required.", 10);
            }
            if ($billsOn1Value == "") {
                throw new exception ("Bills on 1 value is required.", 11);
            }
            if ($billsOn2Value == "") {
                throw new exception ("Bills on 2 value is required.", 12);
            }
            if ($billsOn1Value < 0 || $billsOn1Value >= 12) {
                throw new exception ("Bills on 1 value is invalid.", 13);
            }
            if ($billsOn2Value < 0 || $billsOn2Value >= 12) {
                throw new exception ("Bills on 2 value is invalid.", 14);
            }
            if ($frequencyValue == 2 && $billsOn1Value == $billsOn2Value) {
                throw new exception ("Bills on values cannot be the same.", 15);
            }
            switch($frequencyValue) {
                case 0:
                    $fieldsToSave["frequency"] = 0;
                    $fieldsToSave["months_to_bill"] = -1;
                    break;
                case 1:
                    $fieldsToSave["frequency"] = 1;
                    $fieldsToSave["months_to_bill"] = ((1 << intval($billsOn1Value)) | (1 << intval($billsOn2Value)));
                    break;
                case 2:
                    $fieldsToSave["frequency"] = 2;
                    $fieldsToSave["months_to_bill"] = (1 << intval($billsOn1Value));
                    break;
            }
        }

        if (!db_work( $dbh, HOMECU_WORK_BEGIN )) {
            throw new exception("begin query failed.", 16);
        }

        $sql = "savepoint $savepointBilling";
        $sth = db_query($sql, $dbh);
        if (!$sth) {
            throw new exception("savepoint2 query failed.", 116);
        }

        if ($isAdd) {
            $nextMonth = new DateTime();
            $m = $nextMonth->format('m');
            $Y = $nextMonth->format('Y');
            $nextMonth->setDate($Y, $m + 1, 1);
            $startDate = $nextMonth->format("Y-m-d");

            $sql = "insert into cubillfeaturedetail (sales_order_detail_id, sales_item_id, start_date)
                    values ('" . intval($orderDetailId) . "', '" . intval($salesItemId) . "', '" . prep_save($startDate) . "')
                    returning id";
            $sth = db_query($sql, $dbh);
            if (!$sth) {
                throw new exception ("Insert query failed.", 125);
            }
            $featureDetailId = db_num_rows($sth) > 0 ? intval(db_fetch_row($sth, 0)[0]) : 0;
            if ($featureDetailId == 0) {
                throw new exception ("Insert query failed.", 126);
            }
        }

        $setQuery = array();
        foreach($fieldsToSave as $field => $value) {
            $setQuery[] = "$field = $value"; // Already sanitized above.
        }
        $sql = "update cubillfeaturedetail set " . implode(", ", $setQuery) . " where id = " . intval($featureDetailId);
        $sth = db_query($sql, $dbh);
        if (!$sth) {
            throw new exception("Update cubillfeaturedetail query failed.", 117);
        }

        $sql = "update cubillsalesorderdetail set last_modified_by = '" . prep_save($staffId, 12) . "', last_modified_date = now()
                where id = " . intval($orderDetailId);
        $sth = db_query($sql, $dbh);
        if (!$sth) {
            throw new exception("Update cubillsalesorderdetail query failed.", 118);
        }

        $workflowShouldBeCreated = false;
        $workflowCreated = false;
        $workflowUsed = false;
        $taskEmails = array();
        $issueEmails = array();
        if ($setupLabel === "setup" && !$isActive && !in_array($workflowValue, array("none", "without"))) {

            switch($workflowValue) {
                case "create":
                    $sql = "select 'FOUND' from cutrack where billing_feature_id = " . intval($featureDetailId);
                    $sth = db_query($sql, $dbh);
                    if (!$sth) {
                        throw new exception("Cutrack found query failed.", 115);
                    }
                    if (db_num_rows($sth) > 0) {
                        throw new exception("Workflow already exists.", 116);
                    }

                    $workflowShouldBeCreated = true;

                    $results = SetupWorkflow($dbh, $prodId, $prodText, $featureDetailId, $cu, $cuName, $staffId);
                    if ($results["status"] !== "000") {
                        throw new exception ($results["error"], 117);
                    }
                    $taskEmails = $results["data"]["taskEmails"];
                    $issueEmails = $results["data"]["issueEmails"];
                    $workflowCreated = $results["data"]["workflowCreated"];

                    // Update billingStatus 4 (Pending workflow but no workflow created) to 1 (pending) if it doesn't need to be billed half.
                    // Update billingStatus 4 (Pending workflow but no workflow created) to 0 (bill half) if it does need to be billed half.
                    // Update billingStatus 5 (Pending workflow, no workflow created, and billed half) to 1 (pending.)
                    if ($workflowCreated && in_array($billingStatus, array(4,5))) {
                        $billingStatus = $billingStatus == 5 || $billHalfValue !== "future" ? 1 : 0;
                    } else if (!$workflowCreated) {

                        // There was no workflow created but we still want to bill it upfront.
                        if ($billHalfValue == "upfront") {
                           $billingStatus = 2;
                        } else {
                           $billingStatus = 4;
                        }

                    }
                    break;
                case "associate":
                    if ($unattachedJobValue == -1) {
                        throw new exception ("Job id is required.", 118);
                    }
                    $workflowUsed = true;
                    $sql = "update cutrack set billing_feature_id = " . intval($featureDetailId) . " where track_id = " . intval($unattachedJobValue);
                    $sth = db_query($sql, $dbh);
                    if (!$sth) {
                        throw new exception("Update cutrack query failed.", 119);
                    }

                    // Need to update the billing_ids of the triggered sales items.
                    $sql = "update cutrackitem set billing_item_id = coalesce(fd.id, 0) from cutrackitem ti
                            inner join cutrack t on ti.track_id = t.track_id and t.track_id = " . intval($unattachedJobValue) . "
                            left join cuissuesitem ii on ti.issue_item_id = ii.item_id and ii.issue_id = t.issue_id
                            left join cubillfeaturedetail tfd on tfd.id = " . intval($featureDetailId) . "
                            left join cubillfeaturedetail fd on fd.sales_order_detail_id = tfd.sales_order_detail_id and fd.sales_item_id = ii.sales_item_id
                            where ti.trackitem_id = cutrackitem.trackitem_id";
                    $sth = db_query($sql, $dbh);
                    if (!$sth) {
                        throw new exception("Update cutrackitem query failed.", 120);
                    }

                    // Update billingStatus 4 (Pending workflow but no workflow created) to 1 (pending) if it doesn't need to be billed half.
                    // Update billingStatus 4 (Pending workflow but no workflow created) to 0 (bill half) if it does need to be billed half.
                    // Update billingStatus 5 (Pending workflow, no workflow created, and billed half) to 1 (pending.)
                    if (in_array($billingStatus, array(4,5))) {
                        $billingStatus = $billingStatus == 5 || $billHalfValue !== "future" ? 1 : 0;
                    }
                    break;
                case "without":
                case "none":
                    break;
                default:
                    throw new exception ("Workflow option is invalid.", 121);
            }
        }

        $sql = "update cubillfeaturedetail set billing_status = " . intval($billingStatus) . " where id = " . intval($featureDetailId);
        $sth = db_query($sql, $dbh);
        if (!$sth) {
            throw new exception("Update cubillfeaturedetail query failed.", 123);
        }

        if (!db_work( $dbh, HOMECU_WORK_COMMIT )) {
            throw new exception("commit work failed.", 124);
        }

        foreach($taskEmails as $taskEmail) {
            CreateTaskEmail($taskEmail["to"], $taskEmail["subject"], $taskEmail["text"], $taskEmail["issue"], $taskEmail["title"], $taskEmail["description"],
                            $cuName, $cu, $taskEmail["issueId"]);
        }
        foreach($issueEmails as $issueEmail) {
            CreateIssueEmail($issueEmail["to"], $issueEmail["subject"], $issueEmail["text"], $issueEmail["issueName"], "Active", $cuName, $cu,
                             $issueEmail["issueId"]);
        }

        $workflowStatuses = array();

        if ($workflowShouldBeCreated) {
            $workflowStatuses[] = array("created" => $workflowCreated, "text" => $prodText);
        }

        $returnArray = array("status" => "000", "error" => "", "type" => "modify", "orderDetailId" => $orderDetailId, "workflowStatuses" => $workflowStatuses);

    } catch (exception $e) {
        if ($e->getCode() >= 100) {
            db_work( $dbh, HOMECU_WORK_ROLLBACK ); // Got greater problems if this fails.
        }
        $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage(), "type" => "modify");
    }
    return $returnArray;
}

/**
 * function RemoveBillingFeature($dbh, $sysenv, $staffId)
 * This removes a line item.
 *
 * @param $dbh -- the database connection.
 * @param $sysenv -- the system environment variables.
 * @param $staffId -- the logged in user.
 *
 * @return $status -- "000" if successful, nonzero otherwise.
 * @return $error -- "" if successful, nonempty otherwise.
 * @return $type -- "delete."
 * @return $removeContractDetail -- If true, then also delete the sales order detail.
 *    If true, then refresh the pending grid.
 * @return $orderDetailId -- The id of the sales order detail.
 *    Used to identify and refresh the detail grid.
 */
function RemoveBillingFeature($dbh, $sysenv, $staffId) {
    try {
        extract($sysenv["BILL"]);

        if (!isset($featureDetailId)) {
            throw new exception ("Id is required.", 7);
        }

        $sql = "select sales_order_detail_id from cubillfeaturedetail where id = " . intval($featureDetailId);
        $sth = db_query($sql, $dbh);
        if (!$sth) {
            throw new exception ("Select query failed.", 8);
        }
        $orderDetailId = db_num_rows($sth) > 0 ? intval(db_fetch_row($sth, 0)[0]) : 0;

        $sql = "select count(*) from cubillfeaturedetail fd
                where fd.sales_order_detail_id = " . intval($orderDetailId) . "
                limit 1";
        $sth = db_query($sql, $dbh);
        if (!$sth) {
            throw new exception ("Select query failed.", 1);
        }
        $num = db_num_rows($sth) > 0 ? intval(db_fetch_row($sth, 0)[0]) : 0;

        $removeContractDetail = $num <= 1;

        if (!db_work( $dbh, HOMECU_WORK_BEGIN )) {
            throw new exception("Begin query failed.", 2);
        }

        $sql = "delete from cubillfeaturedetail where id = " . intval($featureDetailId);
        $sth = db_query($sql, $dbh);

        if (!$sth) {
            throw new exception ("Delete query failed.", 103);
        }

        if ($removeContractDetail) {
            $sql = "delete from cubillsalesorderdetail
                    where id = " . intval($orderDetailId) . " and not exists (select 'FOUND' from cubillfeaturedetail
                    where sales_order_detail_id = " . intval($orderDetailId) . ")";
            $sth = db_query($sql, $dbh);

            if (!$sth) {
                throw new exception ("Delete query failed.", 104);
            }
        }

        $sql = "update cubillsalesorderdetail set last_modified_by = '" . prep_save($staffId, 12) . "', last_modified_date = now()
                where id = " . intval($orderDetailId);
        $sth = db_query($sql, $dbh);

        if (!$sth) {
            throw new exception ("Update query failed.", 105);
        }

        if (!db_work( $dbh, HOMECU_WORK_COMMIT )) {
            throw new exception("Commit work failed.", 106);
        }

        $returnArray = array("status" => "000", "error" => "", "removeContractDetail" => $removeContractDetail, "type" => "delete", "orderDetailId" => $orderDetailId);
    } catch (exception $e) {
        if ($e->getCode() >= 100) {
            db_work( $dbh, HOMECU_WORK_ROLLBACK ); // Got greater problems if this fails.
        }
        $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage(), "type" => "delete");
    }
    return $returnArray;
}

/**
 * function RemoveSalesOrderDetail($dbh, $sysenv, $staffId)
 * This removes a sales order detail.
 *
 * @param $dbh -- the database connection.
 * @param $sysenv -- the system environment variables.
 * @param $Hu -- the logged in user
 *
 * @return $status -- "000" if successful, nonzero otherwise.
 * @return $error -- "" if successful, nonempty otherwise.
 * @return $type -- "delete."
 */
function RemoveSalesOrderDetail($dbh, $sysenv, $staffId) {
    try {
        extract($sysenv["BILL"]);

        if (!isset($orderDetailId)) {
            throw new exception ("Detail id is required.", 1);
        }
        $orderDetailId = intval($orderDetailId);

        $sql = "delete from cubillsalesorderdetail where id = " . intval($orderDetailId);
        $sth = db_query($sql, $dbh);
        if (!$sth) {
            throw new exception ("Delete query failed.", 2);
        }

        $returnArray = array("status" => "000", "error" => "", "type" => "delete");
    } catch (exception $e) {
        $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage(), "type" => "delete");
    }
    return $returnArray;
}

/**
 * function ModifyStatus($dbh, $sysenv, $staffId)
 * This "advances" the line item as it where.
 * From the pending grid, it can be started.
 * From the active grid, it can be completed.
 * From the completed grid, it can be reactivated.
 * Each of those subactions have a "revert" option.
 *
 * @param $dbh -- the database connection.
 * @param $sysenv -- the system environment variables.
 * @param $Hu -- the logged in user
 *
 * @return $status -- "000" if successful, nonzero otherwise.
 * @return $error -- "" if successful, nonempty otherwise.
 * @return $type -- "changeStart", "revertStatus", or "advanceStatus."
 *     "changeStart" or "advanceStatus" -- the grid includes these ids (for "undo" statuses.)
 *     "revertStatus" -- the grid no longer includes the ids (the ids are already part of the query.)
 * @return $ids -- the id(s) that were changed.
 * @return $orderDetailId -- the order detail id where the line item is.
 *    (To refresh the detail grid.)
 * @return $isRecurringBtn -- if coming from the "Show Recurring" button.
 *    If true, then I have to update this label.
 *    (It doesn't change when refreshing grid.)
 */
function ModifyStatus($dbh, $sysenv, $staffId) {
    try {
        extract($sysenv["BILL"]);

        if (!isset($childType)) {
            throw new exception ("Child Type is required.", 1);
        }

        $pendingWhere = GetWhereForPendingLineItems();
        $activeWhere = GetWhereForActiveLineItems();
        $completedWhere = GetWhereForCompletedLineItems();

        $ids = array();
        $fieldsToSave = array();
        $isRecurringBtn = false;

        switch($childType) {
            case "startPending":
                if (!isset($startDate)) {
                    throw new exception ("Start date is required.", 2);
                }
                if (!isset($featureDetailId)) {
                    throw new exception ("Feature detail id is required.", 3);
                }

                $sql = "select fd.id, fd.billing_status, fd.sales_order_detail_id
                        from cubillfeaturedetail fd
                        left join cubillsalesitem si on fd.sales_item_id = si.id
                        where fd.id = " . intval($featureDetailId) . " and ($pendingWhere)";
                $sth = db_query($sql, $dbh);
                if (!$sth) {
                    throw new exception ("Select query failed.", 4);
                }
                if (db_num_rows($sth) <= 0) {
                    throw new exception ("Feature detail is not in pending detail grid.", 5);
                }
                list($featureDetailId, $billingStatus, $orderDetailId) = db_fetch_row($sth, 0);

                $fieldsToSave["start_date"] = "'" . prep_save($startDate) . "'";
                $fieldsToSave["end_date"] = "null";
                $fieldsToSave["billing_status"] = 2;
                $ids[] = intval($featureDetailId);
                $type = "advanceStatus";

                break;
            case "undoPending":
                if (!isset($featureDetailId)) {
                    throw new exception ("Feature detail id is required.", 3);
                }

                $sql = "select fd.id, fd.billing_status, fd.sales_order_detail_id
                        from cubillfeaturedetail fd
                        left join cubillsalesitem si on fd.sales_item_id = si.id
                        where fd.id = " . intval($featureDetailId);
                $sth = db_query($sql, $dbh);
                if (!$sth) {
                    throw new exception ("Select query failed.", 9);
                }
                if (db_num_rows($sth) <= 0) {
                    throw new exception ("No records.", 10);
                }
                list($featureDetailId, $billingStatus, $orderDetailId) = db_fetch_row($sth, 0);

                $fieldsToSave["billing_status"] = 1;
                $ids[] = intval($featureDetailId);
                $type = "revertStatus";

                break;
            case "startRecurring":
                if (!isset($startDate)) {
                    throw new exception ("Start date is required.", 12);
                }
                if (!isset($orderDetailId)) {
                    throw new exception ("Order detail id is required.", 13);
                }
                $sql = "select fd.id, fd.billing_status \"billingStatus\", fd.sales_order_detail_id \"orderDetailId\", trim(si.billing_system_id) \"billingId\"
                        from cubillfeaturedetail fd
                        left join cubillsalesitem si on fd.sales_item_id = si.id
                        where fd.sales_order_detail_id = " . intval($orderDetailId) . " and ($pendingWhere)
                        order by fd.id";
                $sth = db_query($sql, $dbh);

                if (!$sth) {
                    throw new exception ("Select query failed.", 15);
                }
                $results = db_fetch_all($sth);
                $results = $results === false ? array() : $results;
                $ids = array();
                $setupRecurring = GetSetupRecurring();
                foreach($results as $record) {

                    $setupLabel = HCU_array_key_value($record["billingId"], $setupRecurring);
                    if ($setupLabel === "recurring") {
                        $ids[] = intval($record["id"]);

                        if ($record["billingStatus"] != 1) {
                            throw new exception ("Feature detail is not in pending detail grid.", 14);
                        }
                    }
                }

                $fieldsToSave["start_date"] = "'" . prep_save($startDate) . "'";
                $fieldsToSave["end_date"] = "null";
                $fieldsToSave["billing_status"] = 2;
                $type = "advanceStatus";
                $isRecurringBtn = true;

                break;
            case "undoRecurring":
                if (!isset($orderDetailId)) {
                    throw new exception ("Order detail id is required.", 16);
                }
                if (!isset($includeIds)) {
                    throw new exception ("Include ids are required.", 17);
                }

                $includeIds = HCU_JsonDecode($includeIds);
                $includeIds = array_map("intval", $includeIds);

                $includePhrase = count($includeIds) > 0 ? "and fd.id in (" . implode(", ", $includeIds) . ")" : "and false";

                $sql = "select fd.id, fd.billing_status \"billingStatus\", fd.sales_order_detail_id \"orderDetailId\", trim(si.billing_system_id) \"billingId\"
                        from cubillfeaturedetail fd
                        left join cubillsalesitem si on fd.sales_item_id = si.id
                        where fd.sales_order_detail_id = " . intval($orderDetailId) . " $includePhrase
                        order by fd.id";
                $sth = db_query($sql, $dbh);

                if (!$sth) {
                    throw new exception ("Select query failed.", 18);
                }
                $results = db_fetch_all($sth);
                $results = $results === false ? array() : $results;
                $ids = array();
                $setupRecurring = GetSetupRecurring();
                foreach($results as $record) {

                    $setupLabel = HCU_array_key_value($record["billingId"], $setupRecurring);
                    if ($setupLabel === "recurring" && $record["billingStatus"] == 2) {
                        $ids[] = intval($record["id"]);
                    }
                }

                $fieldsToSave["billing_status"] = 1;
                $type = "revertStatus";
                $isRecurringBtn = true;

                break;
            case "changeStart":
                if (!isset($startDate)) {
                    throw new exception ("Start date is required.", 19);
                }
                if (!isset($featureDetailId)) {
                    throw new exception ("Feature detail id is required.", 20);
                }

                $sql = "select fd.id, fd.billing_status, fd.sales_order_detail_id
                        from cubillfeaturedetail fd
                        left join cubillsalesitem si on fd.sales_item_id = si.id
                        where fd.id = " . intval($featureDetailId) . " and ($pendingWhere)";
                $sth = db_query($sql, $dbh);
                if (!$sth) {
                    throw new exception ("Select query failed.", 21);
                }
                if (db_num_rows($sth) <= 0) {
                    throw new exception ("Feature detail is not in pending detail grid.", 22);
                }
                list($featureDetailId, $billingStatus, $orderDetailId) = db_fetch_row($sth, 0);

                $fieldsToSave["start_date"] = "'" . prep_save($startDate) . "'";
                $fieldsToSave["end_date"] = "'" . prep_save($startDate) . "'";
                $ids[] = intval($featureDetailId);
                $type = "changeStart";

                break;
            case "reactivateCompleted":
                if (!isset($featureDetailId)) {
                    throw new exception ("Feature detail id is required.", 23);
                }

                $sql = "select fd.id, fd.billing_status, fd.sales_order_detail_id
                        from cubillfeaturedetail fd
                        left join cubillsalesitem si on fd.sales_item_id = si.id
                        where fd.id = " . intval($featureDetailId) . " and ($completedWhere)";
                $sth = db_query($sql, $dbh);
                if (!$sth) {
                    throw new exception ("Select query failed.", 24);
                }
                if (db_num_rows($sth) <= 0) {
                    throw new exception ("Feature detail is not in completed detail grid.", 25);
                }
                list($featureDetailId, $billingStatus, $orderDetailId) = db_fetch_row($sth, 0);

                $fieldsToSave["end_date"] = "null";
                $ids[] = intval($featureDetailId);
                $type = "advanceStatus";

                break;
            case "undoCompleted":
                if (!isset($featureDetailId)) {
                    throw new exception ("Feature detail id is required.", 26);
                }

                $sql = "select fd.id, fd.billing_status, fd.sales_order_detail_id, fd.start_date
                        from cubillfeaturedetail fd
                        left join cubillsalesitem si on fd.sales_item_id = si.id
                        where fd.id = " . intval($featureDetailId);
                $sth = db_query($sql, $dbh);
                if (!$sth) {
                    throw new exception ("Select query failed.", 27);
                }
                if (db_num_rows($sth) <= 0) {
                    throw new exception ("Feature detail is not in completed detail grid.", 28);
                }
                list($featureDetailId, $billingStatus, $orderDetailId, $startDate) = db_fetch_row($sth, 0);

                $fieldsToSave["end_date"] = "'" . prep_save($startDate) . "'";
                $ids[] = intval($featureDetailId);
                $type = "revertStatus";

                break;
            case "completeActive":
                if (!isset($featureDetailId)) {
                    throw new exception ("Feature detail id is required.", 29);
                }
                if (!isset($endDate)) {
                    throw new exception ("End date is required.", 35);
                }

                $sql = "select fd.id, fd.billing_status, fd.sales_order_detail_id, fd.start_date
                        from cubillfeaturedetail fd
                        left join cubillsalesitem si on fd.sales_item_id = si.id
                        where fd.id = " . intval($featureDetailId) . " and ($activeWhere)";
                $sth = db_query($sql, $dbh);
                if (!$sth) {
                    throw new exception ("Select query failed.", 30);
                }
                if (db_num_rows($sth) <= 0) {
                    throw new exception ("Feature detail is not in active detail grid.", 31);
                }
                list($featureDetailId, $billingStatus, $orderDetailId, $startDate) = db_fetch_row($sth, 0);

                $fieldsToSave["end_date"] = "'" . prep_save($endDate) . "'";
                $ids[] = intval($featureDetailId);
                $type = "advanceStatus";

                break;
            case "undoActive":
                if (!isset($featureDetailId)) {
                    throw new exception ("Feature detail id is required.", 32);
                }

                $sql = "select fd.id, fd.billing_status, fd.sales_order_detail_id
                        from cubillfeaturedetail fd
                        left join cubillsalesitem si on fd.sales_item_id = si.id
                        where fd.id = " . intval($featureDetailId);
                $sth = db_query($sql, $dbh);
                if (!$sth) {
                    throw new exception ("Select query failed.", 33);
                }
                if (db_num_rows($sth) <= 0) {
                    throw new exception ("Feature detail is not in completed detail grid.", 34);
                }
                list($featureDetailId, $billingStatus, $orderDetailId) = db_fetch_row($sth, 0);

                $fieldsToSave["end_date"] = "null";
                $ids[] = intval($featureDetailId);
                $type = "revertStatus";

                break;
            default:
                throw new exception ("Child type is unknown.", 6);
        }

        if (count($ids) > 0) {
            $setQuery = array();
            foreach($fieldsToSave as $field => $value) {
                $setQuery[] = "$field = $value"; // Already sanitized above.
            }
            $sql = "update cubillfeaturedetail set " . implode(", ", $setQuery) . " where id in (" . implode(", ", $ids) . ")";
            $sth = db_query($sql, $dbh);
            if (!$sth) {
                throw new exception("Update cubillfeaturedetail query failed.", 8);
            }
        }

        $returnArray = array("status" => "000", "error" => "", "ids" => $ids, "type" => $type, "orderDetailId" => $orderDetailId, "isRecurringBtn" => $isRecurringBtn);
    } catch (exception $e) {
        $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage(), "type" => $type);
    }
    return $returnArray;
}

// =================================
// END PAGE 5: Sales Order Detail
// =================================