function validate_fiscal_year($next_fy, $next_period, $next_start_date, $num_periods = 12) { global $db; for ($i = 0; $i < $num_periods; $i++) { $fy_array = array('period' => $next_period, 'fiscal_year' => $next_fy, 'start_date' => $next_start_date, 'end_date' => gen_specific_date($next_start_date, $day_offset = -1, $month_offset = 1), 'date_added' => date('Y-m-d')); db_perform(TABLE_ACCOUNTING_PERIODS, $fy_array, 'insert'); $next_period++; $next_start_date = gen_specific_date($next_start_date, $day_offset = 0, $month_offset = 1); } return $next_period--; }
function add_item_journal_rows($debit_credit) { // read in line items and add to journal row array if ($debit_credit == 'debit' || $debit_credit == 'credit') { $total = 0; for ($i = 0; $i < count($this->item_rows); $i++) { switch ($this->journal_id) { // determine to pick from the qty or pstd value case 3: case 4: $qty_pstd = 'qty'; $terminal_date = gen_specific_date($this->post_date, $this->item_rows[$i]['lead_time']); break; case 9: case 10: $qty_pstd = 'qty'; $terminal_date = $this->terminal_date; break; case 6: case 7: $qty_pstd = 'pstd'; $terminal_date = $this->post_date; break; case 12: case 13: $qty_pstd = 'pstd'; $terminal_date = $this->terminal_date; break; default: } if ($this->item_rows[$i][$qty_pstd]) { // make sure the quantity line is set and not zero $this->journal_rows[] = array('id' => $this->item_rows[$i]['id'], 'item_cnt' => $this->item_rows[$i]['item_cnt'], 'so_po_item_ref_id' => $this->item_rows[$i]['so_po_item_ref_id'], 'gl_type' => $this->gl_type, 'sku' => $this->item_rows[$i]['sku'], 'qty' => $this->item_rows[$i][$qty_pstd], 'description' => $this->item_rows[$i]['desc'], $debit_credit . '_amount' => $this->item_rows[$i]['total'], 'full_price' => $this->item_rows[$i]['full'], 'gl_account' => $this->item_rows[$i]['acct'], 'taxable' => $this->item_rows[$i]['tax'], 'serialize_number' => $this->item_rows[$i]['serial'], 'project_id' => $this->item_rows[$i]['proj'], 'purch_package_quantity' => $this->item_rows[$i]['purch_package_quantity'], 'post_date' => $this->post_date, 'date_1' => $this->item_rows[$i]['date_1'] ? $this->item_rows[$i]['date_1'] : $terminal_date); $total += $this->item_rows[$i]['total']; } } return $total; } else { die('Bad parameter passed to add_item_journal_rows in class orders!'); } }
function repost_journals($journals, $start_date, $end_date) { global $db, $messageStack; if (sizeof($journals) > 0) { $sql = "select id from " . TABLE_JOURNAL_MAIN . " \r\n\t\twhere journal_id in (" . implode(',', $journals) . ") \r\n\t\tand post_date >= '" . $start_date . "' and post_date < '" . gen_specific_date($end_date, 1) . "' \r\n\t\torder by id"; $result = $db->Execute($sql); $cnt = 0; $db->transStart(); while (!$result->EOF) { $gl_entry = new journal($result->fields['id']); $gl_entry->remove_cogs_rows(); // they will be regenerated during the re-post if (!$gl_entry->Post('edit', true)) { $db->transRollback(); $messageStack->add('<br /><br />Failed Re-posting the journals, try a smaller range. The record that failed was # ' . $gl_entry->id, 'error'); return false; } $cnt++; $result->MoveNext(); } $db->transCommit(); return $cnt; } }
case '3': $month_offset = ($i + 1) * 1; break; // Monthly // Monthly case '4': $month_offset = ($i + 1) * 3; break; // Quarterly // Quarterly case '5': $year_offset = ($i + 1) * 1; break; // Yearly } $glEntry->post_date = gen_specific_date($post_date, $day_offset, $month_offset, $year_offset); $glEntry->period = gen_calculate_period($glEntry->post_date, true); if (!$glEntry->period && $i < $glEntry->recur_id - 1) { // recur falls outside of available periods, ignore last calculation $messageStack->add(ORD_PAST_LAST_PERIOD, 'error'); $error = true; break; } $glEntry->journal_main_array['post_date'] = $glEntry->post_date; $glEntry->journal_main_array['period'] = $glEntry->period; $glEntry->purchase_invoice_id = string_increment($glEntry->journal_main_array['purchase_invoice_id']); } } // restore the first values to continue with post process $glEntry->id = $first_id; $glEntry->journal_main_array['id'] = $first_id;
function gather_history() { global $db; $dates = gen_get_dates(); $cur_month = $dates['ThisYear'] . '-' . substr('0' . $dates['ThisMonth'], -2) . '-01'; $temp_year = $dates['ThisYear']; $temp_month = $dates['ThisMonth']; for ($i = 0; $i < 13; $i++) { $index = substr($cur_month, 0, 7); $this->purchases_history[$index] = array('post_date' => $cur_month, 'MonthName' => $dates['MonthName'], 'ThisYear' => $dates['ThisYear'], 'qty' => 0, 'total_amount' => 0); $this->sales_history[$index] = array('post_date' => $cur_month, 'MonthName' => $dates['MonthName'], 'ThisYear' => $dates['ThisYear'], 'qty' => 0, 'usage' => 0, 'total_amount' => 0); $cur_month = gen_specific_date($cur_month, 0, -1, 0); $dates = gen_get_dates($cur_month); } $last_year = $temp_year - 1 . '-' . substr('0' . $temp_month, -2) . '-01'; // load the SO's and PO's and get order, expected del date $sql = "select m.id, m.journal_id, m.store_id, m.purchase_invoice_id, i.qty, i.post_date, i.date_1,\ti.id as item_id \n\t \t from " . TABLE_JOURNAL_MAIN . " m inner join " . TABLE_JOURNAL_ITEM . " i on m.id = i.ref_id \n\t \t where m.journal_id in (4, 10) and i.sku = '" . $this->sku . "' and m.closed = '0' \n\t \t order by i.date_1"; $result = $db->Execute($sql); while (!$result->EOF) { switch ($result->fields['journal_id']) { case 4: $gl_type = 'por'; $hist_type = 'open_po'; break; case 10: $gl_type = 'sos'; $hist_type = 'open_so'; break; } $sql = "select sum(qty) as qty from " . TABLE_JOURNAL_ITEM . " \n\t\t\t where gl_type = '" . $gl_type . "' and so_po_item_ref_id = " . $result->fields['item_id']; $adj = $db->Execute($sql); // this looks for partial received to make sure this item is still on order if ($result->fields['qty'] > $adj->fields['qty']) { $this->history[$hist_type][] = array('id' => $result->fields['id'], 'store_id' => $result->fields['store_id'], 'purchase_invoice_id' => $result->fields['purchase_invoice_id'], 'post_date' => $result->fields['post_date'], 'qty' => $result->fields['qty'], 'date_1' => $result->fields['date_1']); } $result->MoveNext(); } // load the units received and sold, assembled and adjusted $sql = "select m.journal_id, m.post_date, i.qty, i.gl_type, i.credit_amount, i.debit_amount \n\t\t from " . TABLE_JOURNAL_MAIN . " m inner join " . TABLE_JOURNAL_ITEM . " i on m.id = i.ref_id \n\t\t where m.journal_id in (6, 12, 14, 16, 19, 21) and i.sku = '" . $this->sku . "' and m.post_date >= '" . $last_year . "' \n\t\t order by m.post_date DESC"; $result = $db->Execute($sql); while (!$result->EOF) { $month = substr($result->fields['post_date'], 0, 7); switch ($result->fields['journal_id']) { case 6: case 21: $this->purchases_history[$month]['qty'] += $result->fields['qty']; $this->purchases_history[$month]['total_amount'] += $result->fields['debit_amount']; break; case 12: case 19: $this->sales_history[$month]['qty'] += $result->fields['qty']; $this->sales_history[$month]['usage'] += $result->fields['qty']; $this->sales_history[$month]['total_amount'] += $result->fields['credit_amount']; break; case 14: if ($result->fields['gl_type'] == 'asi') { // only if part of an assembly $this->sales_history[$month]['usage'] -= $result->fields['qty']; // need to negate quantity since assy. } break; case 16: $this->sales_history[$month]['usage'] += $result->fields['qty']; break; } $result->MoveNext(); } // calculate average usage $cnt = 0; foreach ($this->sales_history as $key => $value) { if ($cnt == 0) { $cnt++; continue; // skip current month since we probably don't have the full months worth } $this->history['averages']['12month'] += $this->sales_history[$key]['usage']; if ($cnt < 7) { $this->history['averages']['6month'] += $this->sales_history[$key]['usage']; } if ($cnt < 4) { $this->history['averages']['3month'] += $this->sales_history[$key]['usage']; } if ($cnt < 2) { $this->history['averages']['1month'] += $this->sales_history[$key]['usage']; } $cnt++; } $this->history['averages']['12month'] = round($this->history['averages']['12month'] / 12, 2); $this->history['averages']['6month'] = round($this->history['averages']['6month'] / 6, 2); $this->history['averages']['3month'] = round($this->history['averages']['3month'] / 3, 2); }
function calculate_aging($id, $type = 'c', $special_terms = '0') { global $db; $output = array(); if (!$id) { return $output; } $today = date('Y-m-d'); $terms = explode(':', $special_terms); $credit_limit = $terms[4] ? $terms[4] : constant(($type == 'v' ? 'AP' : 'AR') . '_CREDIT_LIMIT_AMOUNT'); $due_days = $terms[3] ? $terms[3] : constant(($type == 'v' ? 'AP' : 'AR') . '_NUM_DAYS_DUE'); $due_date = gen_specific_date($today, -$due_days); $late_30 = gen_specific_date($today, $type == 'v' ? -AP_AGING_DATE_1 : -AR_AGING_PERIOD_1); $late_60 = gen_specific_date($today, $type == 'v' ? -AP_AGING_DATE_2 : -AR_AGING_PERIOD_2); $late_90 = gen_specific_date($today, $type == 'v' ? -AP_AGING_DATE_3 : -AR_AGING_PERIOD_3); $output = array('balance_0' => '0', 'balance_30' => '0', 'balance_60' => '0', 'balance_90' => '0'); $inv_jid = $type == 'v' ? '6, 7' : '12, 13'; $pmt_jid = $type == 'v' ? '20' : '18'; $total_outstanding = 0; $past_due = 0; $sql = "select id from " . TABLE_JOURNAL_MAIN . " \n\t\twhere bill_acct_id = " . $id . " and journal_id in (" . $inv_jid . ") and closed = '0'"; $open_inv = $db->Execute($sql); while (!$open_inv->EOF) { $result = $db->Execute("select debit_amount, credit_amount from " . TABLE_JOURNAL_ITEM . " where gl_type = 'ttl' and ref_id = " . $open_inv->fields['id']); $result2 = $db->Execute("select journal_id, post_date from " . TABLE_JOURNAL_MAIN . " where id = " . $open_inv->fields['id']); $total_billed = $result->fields['debit_amount'] - $result->fields['credit_amount']; $post_date = $result2->fields['post_date']; $result = $db->Execute("select sum(debit_amount) as debits, sum(credit_amount) as credits \n\t from " . TABLE_JOURNAL_ITEM . " where so_po_item_ref_id = '" . $open_inv->fields['id'] . "' and gl_type in ('pmt', 'chk')"); $total_paid = $result->fields['credits'] - $result->fields['debits']; $balance = $total_billed - $total_paid; if ($type == 'v') { $balance = -$balance; } // start the placement in aging array if ($post_date < $due_date) { $past_due += $balance; } if ($post_date < $late_90) { $output['balance_90'] += $balance; $total_outstanding += $balance; } elseif ($post_date < $late_60) { $output['balance_60'] += $balance; $total_outstanding += $balance; } elseif ($post_date < $late_30) { $output['balance_30'] += $balance; $total_outstanding += $balance; } elseif ($post_date <= $today) { $output['balance_0'] += $balance; $total_outstanding += $balance; } // else it's in the future $open_inv->MoveNext(); } $output['total'] = $total_outstanding; $output['past_due'] = $past_due; $output['credit_limit'] = $credit_limit; $output['terms_lang'] = gen_terms_to_language($special_terms, false, $type == 'v' ? 'AP' : 'AR'); return $output; }
function processInventory($upload_name) { global $coa, $db, $currencies, $messageStack; if (!$this->cyberParse($upload_name)) { return false; } $post_date = gen_specific_date(date('Y-m-d'), $day_offset = -1); $glEntry = new journal(); $sku_list = array(); $coa_list = array(); $affected_accounts = array(); for ($row_id = 0, $j = 2; $row_id < count($this->records); $row_id++, $j++) { $row = $this->records[$row_id]; $total_amount = $currencies->clean_value($row['total_amount']); $qty = $currencies->clean_value($row['quantity']); // check for errors and report/exit if error found if (!gen_validate_sku($row['sku'])) { $messageStack->add(GL_ERROR_UPDATING_INVENTORY_STATUS . $row['sku'] . GL_BEG_BAL_ERROR_0 . $j, 'error'); return false; } if (!in_array($row['inv_gl_acct'], $coa) || !in_array($row['gl_acct'], $coa)) { $messageStack->add(GL_BEG_BAL_ERROR_6 . $j, 'error'); return false; } if ($qty == 0) { $messageStack->add(GL_BEG_BAL_ERROR_7 . $j, 'caution'); } else { $affected_accounts[$row['inv_gl_acct']] = true; // need list of accounts to update history $affected_accounts[$row['gl_acct']] = true; // both credit and debit $sku_list[$row['sku']]['qty'] += $qty; // load quantity indexed by sku $sku_list[$row['sku']]['total'] += $total_amount; // load total_value indexed by sku $coa_list[$row['inv_gl_acct']] += $total_amount; // add to debit total by coa $coa_list[$row['gl_acct']] -= $total_amount; // add to credit total by coa } } if (is_array($sku_list)) { $glEntry->affected_accounts = $affected_accounts; // *************** START TRANSACTION ************************* $db->transStart(); // update inventory balances on hand foreach ($sku_list as $sku => $details) { $sql = "update " . TABLE_INVENTORY . " set quantity_on_hand = quantity_on_hand + {$details['qty']} where sku = '{$sku}'"; $result = $db->Execute($sql); if ($result->AffectedRows() != 1) { $messageStack->add(sprintf(GL_BEG_BAL_ERROR_8, $sku), 'error'); $db->transRollback(); return false; } $history_array = array('ref_id' => 0, 'sku' => $sku, 'qty' => $details['qty'], 'remaining' => $details['qty'], 'unit_cost' => $details['total'] / $details['qty'], 'avg_cost' => $details['total'] / $details['qty'], 'post_date' => $post_date); $result = db_perform(TABLE_INVENTORY_HISTORY, $history_array, 'insert'); } // update chart of account beginning balances for period 1 foreach ($coa_list as $account => $amount) { $sql = "update " . TABLE_CHART_OF_ACCOUNTS_HISTORY . " set beginning_balance = beginning_balance + {$amount}\n\t\t\twhere account_id = '{$account}' and period = 1"; $result = $db->Execute($sql); if ($result->AffectedRows() != 1) { $messageStack->add(sprintf(GL_BEG_BAL_ERROR_9, $account), 'error'); $db->transRollback(); return false; } } // update the chart of accounts history through the existing periods if (!$glEntry->update_chart_history_periods($period = 1)) { if (DEBUG) { $messageStack->write_debug(); } return $glEntry->fail_message(GL_ERROR_UPDATE_COA_HISTORY); } $db->transCommit(); // post the chart of account values if (DEBUG) { $messageStack->write_debug(); } // *************** END TRANSACTION ************************* } $this->line_count = $row_id; return true; }
function calculate_terms_due_dates($post_date, $terms_encoded, $type = 'AR') { $terms = explode(':', $terms_encoded); $date_details = gen_get_dates($post_date); $result = array(); switch ($terms[0]) { default: case '0': // Default terms $result['discount'] = constant($type . '_PREPAYMENT_DISCOUNT_PERCENT') / 100; $result['net_date'] = gen_specific_date($post_date, constant($type . '_NUM_DAYS_DUE')); if ($result['discount'] != 0) { $result['early_date'] = gen_specific_date($post_date, constant($type . '_PREPAYMENT_DISCOUNT_DAYS')); } else { $result['early_date'] = gen_specific_date($post_date, 1000); // move way out } break; case '1': // Cash on Delivery (COD) // Cash on Delivery (COD) case '2': // Prepaid $result['discount'] = 0; $result['early_date'] = $post_date; $result['net_date'] = $post_date; break; case '3': // Special terms $result['discount'] = $terms[1] / 100; $result['early_date'] = gen_specific_date($post_date, $terms[2]); $result['net_date'] = gen_specific_date($post_date, $terms[3]); break; case '4': // Due on day of next month $result['discount'] = $terms[1] / 100; $result['early_date'] = gen_specific_date($post_date, $terms[2]); $result['net_date'] = gen_db_date_short($terms[3]); break; case '5': // Due at end of month $result['discount'] = $terms[1] / 100; $result['early_date'] = gen_specific_date($post_date, $terms[2]); $result['net_date'] = date('Y-m-d', mktime(0, 0, 0, $date_details['ThisMonth'], $date_details['TotalDays'], $date_details['ThisYear'])); break; } return $result; }
function gather_history($sku) { global $db, $messageStack; $inv_history = array(); $dates = gen_get_dates(); $cur_month = $dates['ThisYear'] . '-' . substr('0' . $dates['ThisMonth'], -2) . '-01'; for ($i = 0; $i < 13; $i++) { $index = substr($cur_month, 0, 7); $history['purchases'][$index] = array('post_date' => $cur_month, 'qty' => 0, 'total_amount' => 0); $history['sales'][$index] = array('post_date' => $cur_month, 'qty' => 0, 'usage' => 0, 'total_amount' => 0); $cur_month = gen_specific_date($cur_month, 0, -1, 0); } $last_year = $dates['ThisYear'] - 1 . '-' . substr('0' . $dates['ThisMonth'], -2) . '-01'; // load the SO's and PO's and get order, expected del date $sql = "SELECT m.id, m.journal_id, m.store_id, m.purchase_invoice_id, i.qty, i.post_date, i.date_1,\n\ti.id AS item_id FROM " . TABLE_JOURNAL_MAIN . " m JOIN " . TABLE_JOURNAL_ITEM . " i ON m.id=i.ref_id\n\t WHERE m.journal_id IN (4, 10) AND i.sku='{$sku}' AND m.closed = '0' ORDER BY i.date_1"; $result = $db->Execute($sql); while (!$result->EOF) { switch ($result->fields['journal_id']) { case 4: $gl_type = 'por'; $hist_type = 'open_po'; break; case 10: $gl_type = 'sos'; $hist_type = 'open_so'; break; } $sql = "SELECT SUM(qty) AS qty from " . TABLE_JOURNAL_ITEM . "\n\t\tWHERE gl_type='{$gl_type}' AND so_po_item_ref_id=" . $result->fields['item_id']; $adj = $db->Execute($sql); // this looks for partial received to make sure this item is still on order if ($result->fields['qty'] > $adj->fields['qty']) { $history[$hist_type][] = array('id' => $result->fields['id'], 'store_id' => $result->fields['store_id'], 'purchase_invoice_id' => $result->fields['purchase_invoice_id'], 'post_date' => $result->fields['post_date'], 'qty' => $result->fields['qty'], 'date_1' => $result->fields['date_1']); } $result->MoveNext(); } // load the units received and sold, assembled and adjusted $sql = "SELECT m.journal_id, m.post_date, i.qty, i.gl_type, i.credit_amount, i.debit_amount\n\t FROM " . TABLE_JOURNAL_MAIN . " m JOIN " . TABLE_JOURNAL_ITEM . " i ON m.id=i.ref_id\n\t WHERE m.journal_id IN (6, 12, 14, 16, 19, 21) AND i.sku='{$sku}' AND m.post_date >= '{$last_year}'\n\t ORDER BY m.post_date DESC"; $result = $db->Execute($sql); while (!$result->EOF) { $month = substr($result->fields['post_date'], 0, 7); switch ($result->fields['journal_id']) { case 6: case 21: $history['purchases'][$month]['qty'] += $result->fields['qty']; $history['purchases'][$month]['total_amount'] += $result->fields['debit_amount']; break; case 12: case 19: $history['sales'][$month]['qty'] += $result->fields['qty']; $history['sales'][$month]['usage'] += $result->fields['qty']; $history['sales'][$month]['total_amount'] += $result->fields['credit_amount']; break; case 14: if ($result->fields['gl_type'] == 'asi') { // only if part of an assembly $history['sales'][$month]['usage'] -= $result->fields['qty']; // need to negate quantity since assy. } break; case 16: $history['sales'][$month]['usage'] += $result->fields['qty']; break; } $result->MoveNext(); } // calculate average usage $percent_diff = 0.1; // the percentage difference from current value to notify for adjustment $months_of_data = 12; // valid values are 1, 3, 6, or 12 $med_avg_diff = 0.25; // the maximum percentage difference from the median and average, for large swings $cnt = 0; $sales = array(); $history['averages'] = array(); foreach ($history['sales'] as $key => $value) { if ($cnt == 0) { $cnt++; continue; } // skip current month since we probably don't have the full months worth $history['averages']['12month'] += $history['sales'][$key]['usage']; if ($cnt < 7) { $history['averages']['6month'] += $history['sales'][$key]['usage']; } if ($cnt < 4) { $history['averages']['3month'] += $history['sales'][$key]['usage']; } if ($cnt < 2) { $history['averages']['1month'] += $history['sales'][$key]['usage']; } if ($cnt <= $months_of_data) { $sales[] = $value['usage']; } $cnt++; } $history['averages']['12month'] = round($history['averages']['12month'] / 12, 2); $history['averages']['6month'] = round($history['averages']['6month'] / 6, 2); $history['averages']['3month'] = round($history['averages']['3month'] / 3, 2); sort($sales); $sql = "SELECT minimum_stock_level, lead_time FROM " . TABLE_INVENTORY . " WHERE sku='{$sku}'"; $inv = $db->Execute($sql); $idx = ceil(count($sales) / 2); $median_sales = $sales[$idx]; $average_sales = ceil($history['averages'][$months_of_data . 'month']); $new_min_stock = ceil($inv->fields['lead_time'] / 30) * $average_sales; $high_band = $inv->fields['minimum_stock_level'] * (1 + $percent_diff); $low_band = $inv->fields['minimum_stock_level'] * (1 - $percent_diff); $high_avg = $average_sales * (1 + $med_avg_diff); $low_avg = $average_sales * (1 - $med_avg_diff); if ($new_min_stock > $high_band || $new_min_stock < $low_band) { $messageStack->add(sprintf(INV_STOCK_LEVEL_ADJ, $new_min_stock), 'caution'); } if ($median_sales > $high_avg || $median_sales < $low_avg) { $messageStack->add(sprintf(INV_STOCK_MEDIAN, $median_sales, $average_sales), 'caution'); } return $history; }
function gen_build_sql_date($date_prefs, $df) { global $db; $dates = gen_get_dates(); $DateArray = explode(':', $date_prefs); $t = time(); $ds = '0000-00-00'; $de = '2199-00-00'; switch ($DateArray[0]) { default: case "a": // All, skip the date addition to the where statement, all dates in db $d = ''; $fildesc = ''; break; case "b": // Date Range $d = ''; $fildesc = RW_RPT_DATERANGE; if ($DateArray[1] != '') { $ds = gen_db_date($DateArray[1]); $d .= $df . " >= '" . $ds . "'"; $fildesc .= ' ' . TEXT_FROM . ' ' . $DateArray[1]; } if ($DateArray[2] != '') { // a value entered, check if (strlen($d) > 0) { $d .= ' and '; } $de = gen_specific_date(gen_db_date($DateArray[2]), 1); $d .= $df . " < '" . $de . "'"; $fildesc .= ' ' . TEXT_TO . ' ' . $DateArray[2]; } $fildesc .= '; '; break; case "c": // Today (specify range for datetime type fields to match for time parts) $ds = $dates['Today']; $de = gen_specific_date($dates['Today'], 1); $d = $df . " >= '" . $ds . "' and " . $df . " < '" . $de . "'"; $fildesc = RW_RPT_DATERANGE . ' = ' . gen_locale_date($dates['Today']) . '; '; break; case "d": // This Week $ds = date('Y-m-d', mktime(0, 0, 0, $dates['ThisMonth'], date('j', $t) - date('w', $t), $dates['ThisYear'])); $de = gen_specific_date(date('Y-m-d', mktime(0, 0, 0, $dates['ThisMonth'], date('j', $t) - date('w', $t) + 6, $dates['ThisYear'])), 1); $d = $df . " >= '" . $ds . "' and " . $df . " < '" . $de . "'"; $fildesc = RW_RPT_DATERANGE . ' ' . TEXT_FROM . ' ' . gen_locale_date($ds) . ' ' . TEXT_TO . ' ' . gen_locale_date(gen_specific_date($de, -1)) . '; '; break; case "e": // This Week to Date $ds = date('Y-m-d', mktime(0, 0, 0, $dates['ThisMonth'], date('j', $t) - date('w', $t), $dates['ThisYear'])); $de = gen_specific_date($dates['Today'], 1); $d = $df . " >= '" . $ds . "' and " . $df . " < '" . $de . "'"; $fildesc = RW_RPT_DATERANGE . ' ' . TEXT_FROM . ' ' . gen_locale_date($ds) . ' ' . TEXT_TO . ' ' . gen_locale_date($dates['Today']) . '; '; break; case "f": // This Month $ds = date('Y-m-d', mktime(0, 0, 0, $dates['ThisMonth'], 1, $dates['ThisYear'])); $de = gen_specific_date(date('Y-m-d', mktime(0, 0, 0, $dates['ThisMonth'], $dates['TotalDays'], $dates['ThisYear'])), 1); $d = $df . " >= '" . $ds . "' and " . $df . " < '" . $de . "'"; $fildesc = RW_RPT_DATERANGE . ' ' . TEXT_FROM . ' ' . gen_locale_date($ds) . ' ' . TEXT_TO . ' ' . gen_locale_date(gen_specific_date($de, -1)) . '; '; break; case "g": // This Month to Date $ds = date('Y-m-d', mktime(0, 0, 0, $dates['ThisMonth'], 1, $dates['ThisYear'])); $de = gen_specific_date($dates['Today'], 1); $d = $df . " >= '" . $ds . "' and " . $df . " < '" . $de . "'"; $fildesc = RW_RPT_DATERANGE . ' ' . TEXT_FROM . ' ' . gen_locale_date($ds) . ' ' . TEXT_TO . ' ' . gen_locale_date($dates['Today']) . '; '; break; case "h": // This Quarter $QtrStrt = CURRENT_ACCOUNTING_PERIOD - (CURRENT_ACCOUNTING_PERIOD - 1) % 3; $temp = gen_calculate_fiscal_dates($QtrStrt); $ds = $temp['start_date']; $temp = gen_calculate_fiscal_dates($QtrStrt + 2); $de = gen_specific_date($temp['end_date'], 1); $d = $df . " >= '" . $ds . "' and " . $df . " < '" . $de . "'"; $fildesc = RW_RPT_DATERANGE . ' ' . TEXT_FROM . ' ' . gen_locale_date($ds) . ' ' . TEXT_TO . ' ' . gen_locale_date($temp['end_date']) . '; '; break; case "i": // Quarter to Date $QtrStrt = CURRENT_ACCOUNTING_PERIOD - (CURRENT_ACCOUNTING_PERIOD - 1) % 3; $temp = gen_calculate_fiscal_dates($QtrStrt); $ds = $temp['start_date']; $de = gen_specific_date($dates['Today'], 1); $d = $df . " >= '" . $ds . "' and " . $df . " < '" . $de . "'"; $fildesc = RW_RPT_DATERANGE . ' ' . TEXT_FROM . ' ' . gen_locale_date($ds) . ' ' . TEXT_TO . ' ' . gen_locale_date($dates['Today']) . '; '; break; case "j": // This Year $YrStrt = CURRENT_ACCOUNTING_PERIOD - (CURRENT_ACCOUNTING_PERIOD - 1) % 12; $temp = gen_calculate_fiscal_dates($YrStrt); $ds = $temp['start_date']; $temp = gen_calculate_fiscal_dates($YrStrt + 11); $de = gen_specific_date($temp['end_date'], 1); $d = $df . " >= '" . $ds . "' and " . $df . " < '" . $de . "'"; $fildesc = RW_RPT_DATERANGE . ' ' . TEXT_FROM . ' ' . gen_locale_date($ds) . ' ' . TEXT_TO . ' ' . gen_locale_date($temp['end_date']) . '; '; break; case "k": // Year to Date $YrStrt = CURRENT_ACCOUNTING_PERIOD - (CURRENT_ACCOUNTING_PERIOD - 1) % 12; $temp = gen_calculate_fiscal_dates($YrStrt); $ds = $temp['start_date']; $de = gen_specific_date($dates['Today'], 1); $d = $df . " >= '" . $ds . "' and " . $df . " < '" . $de . "'"; $fildesc = RW_RPT_DATERANGE . ' ' . TEXT_FROM . ' ' . gen_locale_date($ds) . ' ' . TEXT_TO . ' ' . gen_locale_date($dates['Today']) . '; '; break; case "l": // This Period $ds = CURRENT_ACCOUNTING_PERIOD_START; $de = gen_specific_date(CURRENT_ACCOUNTING_PERIOD_END, 1); $d = $df . " >= '" . $ds . "' and " . $df . " < '" . $de . "'"; $fildesc = TEXT_PERIOD . ' ' . CURRENT_ACCOUNTING_PERIOD . ' (' . gen_locale_date(CURRENT_ACCOUNTING_PERIOD_START) . ' ' . TEXT_TO . ' ' . gen_locale_date(CURRENT_ACCOUNTING_PERIOD_END) . '); '; break; case "z": // date by period $temp = gen_calculate_fiscal_dates($DateArray[1]); $ds = $temp['start_date']; $de = $temp['end_date']; $d = 'period = ' . $DateArray[1]; $fildesc = TEXT_PERIOD . ' ' . $DateArray[1] . ' (' . gen_locale_date($ds) . ' ' . TEXT_TO . ' ' . gen_locale_date($de) . '); '; break; } $dates = array('sql' => $d, 'description' => $fildesc, 'start_date' => $ds, 'end_date' => $de); return $dates; }
function calulate_special_fields($id) { global $db; $today = date('Y-m-d'); $new_data = array(); $result = $db->Execute("select debit_amount, credit_amount from " . TABLE_JOURNAL_ITEM . " where gl_type = 'ttl' and ref_id = " . $id); $result2 = $db->Execute("select journal_id, post_date from " . TABLE_JOURNAL_MAIN . " where id = " . $id); $post_date = $result2->fields['post_date']; $negate = false; if (in_array($result2->fields['journal_id'], array(6, 7))) { $late_30 = gen_specific_date($today, -AP_AGING_DATE_1); $late_60 = gen_specific_date($today, -AP_AGING_DATE_2); $late_90 = gen_specific_date($today, -AP_AGING_DATE_3); if ($result2->fields['journal_id'] != 7) { $negate = true; } } else { $late_30 = gen_specific_date($today, -AR_AGING_PERIOD_1); $late_60 = gen_specific_date($today, -AR_AGING_PERIOD_2); $late_90 = gen_specific_date($today, -AR_AGING_PERIOD_3); if ($result2->fields['journal_id'] != 12) { $negate = true; } } $result3 = $db->Execute("select sum(debit_amount) as debits, sum(credit_amount) as credits \n\t from " . TABLE_JOURNAL_ITEM . " where so_po_item_ref_id = '" . $id . "' and gl_type in ('pmt', 'chk')"); if ($negate) { $total_billed = $result->fields['credit_amount'] - $result->fields['debit_amount']; $total_paid = $result3->fields['debits'] - $result3->fields['credits']; } else { $total_billed = $result->fields['debit_amount'] - $result->fields['credit_amount']; $total_paid = $result3->fields['credits'] - $result3->fields['debits']; } $balance = $total_billed - $total_paid; $new_data['balance_due'] = $balance; $new_data['balance_futre'] = 0; $new_data['balance_0'] = 0; $new_data['balance_30'] = 0; $new_data['balance_60'] = 0; $new_data['balance_90'] = 0; if ($post_date < $late_90) { $new_data['balance_90'] = $balance; } elseif ($post_date < $late_60) { $new_data['balance_60'] = $balance; } elseif ($post_date < $late_30) { $new_data['balance_30'] = $balance; } elseif ($post_date > $today) { $new_data['balance_futre'] = $balance; } else { $new_data['balance_0'] = $balance; } return $new_data; }
$id = (int) $_GET['id']; /*************** Act on the action request *************************/ // Load the customer status $customer = $db->Execute("select c.type, c.inactive, c.special_terms, a.notes \r\n from " . TABLE_CONTACTS . " c inner join " . TABLE_ADDRESS_BOOK . " a on c.id = a.ref_id \r\n where c.id = " . $id . " and a.type like '%m'"); $notes = str_replace(chr(10), "<br />", $customer->fields['notes']); $type = $customer->fields['type'] == 'v' ? 'AP' : 'AR'; $special_terms = gen_terms_to_language($customer->fields['special_terms'], $short = false, $type); $terms = explode(':', $customer->fields['special_terms']); $credit_limit = $terms[4] ? $terms[4] : constant($type . '_CREDIT_LIMIT_AMOUNT'); $due_days = $terms[3] ? $terms[3] : constant($type . '_NUM_DAYS_DUE'); $due_date = gen_specific_date($today, -$due_days); // load the aged status $today = date('Y-m-d'); $late_30 = gen_specific_date($today, $type == 'AP' ? -AP_AGING_DATE_1 : -AR_AGING_PERIOD_1); $late_60 = gen_specific_date($today, $type == 'AP' ? -AP_AGING_DATE_2 : -AR_AGING_PERIOD_2); $late_90 = gen_specific_date($today, $type == 'AP' ? -AP_AGING_DATE_3 : -AR_AGING_PERIOD_3); $new_data = array('balance_0' => '0', 'balance_30' => '0', 'balance_60' => '0', 'balance_90' => '0'); $inv_jid = $type == 'AP' ? '6, 7' : '12, 13'; $pmt_jid = $type == 'AP' ? '20' : '18'; $total_outstanding = 0; $past_due = 0; $sql = "select id from " . TABLE_JOURNAL_MAIN . " \r\n\twhere bill_acct_id = " . $id . " and journal_id in (" . $inv_jid . ") and closed = '0'"; $open_inv = $db->Execute($sql); //echo 'records = ' . $open_inv->RecordCount() . '<br />'; while (!$open_inv->EOF) { $sql = "select m.post_date, sum(i.debit_amount) as debits, sum(i.credit_amount) as credits \r\n from " . TABLE_JOURNAL_MAIN . " m inner join " . TABLE_JOURNAL_ITEM . " i on m.id = i.ref_id\r\n where m.id = " . $open_inv->fields['id'] . " and journal_id in (" . $inv_jid . ") and i.gl_type <> 'ttl' group by m.id"; $result = $db->Execute($sql); $total_billed = $result->fields['credits'] - $result->fields['debits']; $post_date = $result->fields['post_date']; $sql = "select sum(i.debit_amount) as debits, sum(i.credit_amount) as credits \r\n from " . TABLE_JOURNAL_MAIN . " m inner join " . TABLE_JOURNAL_ITEM . " i on m.id = i.ref_id\r\n where i.so_po_item_ref_id = " . $open_inv->fields['id'] . " and m.journal_id = " . $pmt_jid . " and i.gl_type = 'pmt'"; $result = $db->Execute($sql);
$now->modify('-1 day'); } $cal_order = array('name' => 'dateOrdered', 'form' => 'orders', 'fieldname' => 'post_date', 'imagename' => 'btn_date_1', 'default' => isset($order->post_date) ? gen_locale_date($order->post_date) : $now->format(DATE_FORMAT), 'params' => array('align' => 'left')); $cal_terminal = array('name' => 'dateRequired', 'form' => 'orders', 'fieldname' => 'terminal_date', 'imagename' => 'btn_date_2', 'default' => isset($order->terminal_date) ? gen_locale_date($order->terminal_date) : $req_date, 'params' => array('align' => 'left')); // build the display options based on JOURNAL_ID $template_options = array(); switch (JOURNAL_ID) { case 3: case 4: $req_date = gen_locale_date(gen_specific_date('', 0, 1, 0)); $template_options['terminal_date'] = true; $template_options['terms'] = true; $template_options['closed'] = array('title' => TEXT_CLOSE, 'field' => html_checkbox_field('closed', '1', $order->closed ? true : false, '', '')); break; case 6: $req_date = gen_locale_date(gen_specific_date('', 0, 1, 0)); $template_options['terms'] = true; $template_options['waiting'] = array('title' => ORD_WAITING_FOR_INVOICE, 'field' => html_checkbox_field('waiting', '1', $order->waiting ? true : false, '', '')); break; case 7: $req_date = date(DATE_FORMAT); $template_options['terms'] = true; $template_options['waiting'] = array('title' => ORD_WAITING_FOR_INVOICE, 'field' => html_checkbox_field('waiting', '1', $order->waiting ? true : false, '', '')); break; case 9: case 10: $template_options['closed'] = array('title' => TEXT_CLOSE, 'field' => html_checkbox_field('closed', '1', $order->closed ? true : false, '', '')); case 12: $req_date = date(DATE_FORMAT); $template_options['terminal_date'] = true; $template_options['terms'] = true;
$db->Execute("delete from " . TABLE_PRICE_SHEETS . " where id = '{$id}'"); $db->Execute("delete from " . TABLE_INVENTORY_SPECIAL_PRICES . " where price_sheet_id = '{$id}'"); gen_add_audit_log(PRICE_SHEETS_LOG . TEXT_DELETE, $sheet_name); gen_redirect(html_href_link(FILENAME_DEFAULT, gen_get_all_get_params(array('psID', 'action')) . '&type=' . $type, 'SSL')); break; case 'revise': validate_security($security_level, 2); $old_id = db_prepare_input($_GET['psID']); $result = $db->Execute("select * from " . TABLE_PRICE_SHEETS . " where id = {$old_id}"); $old_rev = $result->fields['revision']; $output_array = array('sheet_name' => $result->fields['sheet_name'], 'type' => $type, 'revision' => $result->fields['revision'] + 1, 'effective_date' => gen_specific_date($result->fields['effective_date'], 1), 'default_sheet' => $result->fields['default_sheet'], 'default_levels' => $result->fields['default_levels']); db_perform(TABLE_PRICE_SHEETS, $output_array, 'insert'); $id = db_insert_id(); // this is used by the edit function later on. // expire the old sheet $db->Execute("UPDATE " . TABLE_PRICE_SHEETS . " SET expiration_date='" . gen_specific_date($result->fields['effective_date'], 1) . "' WHERE id={$old_id}"); // Copy special pricing information to new sheet $levels = $db->Execute("select inventory_id, price_levels from " . TABLE_INVENTORY_SPECIAL_PRICES . " where price_sheet_id = {$old_id}"); while (!$levels->EOF) { $db->Execute("insert into " . TABLE_INVENTORY_SPECIAL_PRICES . " set inventory_id = {$levels->fields}['inventory_id'],\n\t\t price_sheet_id = {$id}, price_levels = '{$levels->fields}['price_levels']'"); $levels->MoveNext(); } gen_add_audit_log(PRICE_SHEETS_LOG . TEXT_REVISE, $result->fields['sheet_name'] . ' Rev. ' . $old_rev . ' => ' . ($old_rev + 1)); $_REQUEST['action'] = 'edit'; // continue with edit. // continue with edit. case 'edit': if (!isset($id)) { $id = db_prepare_input($_POST['rowSeq']); } $result = $db->Execute("select * from " . TABLE_PRICE_SHEETS . " where id = {$id}");
function trackPackages($track_date = '0000-00-00', $log_id = false) { global $db, $messageStack; $result = array(); if (MODULE_SHIPPING_FEDEX_V7_TEST_MODE == 'Test') { $messageStack->add('Tracking only works on the FedEx production server!', 'error'); return false; } else { $client = new SoapClient(PATH_TO_TRACK_WSDL, array('trace' => 1)); } if ($log_id) { $shipments = $db->Execute("select id, ref_id, deliver_date, actual_date, tracking_id, notes \n\t\t\t\tfrom " . TABLE_SHIPPING_LOG . " \n\t\t\t\twhere carrier = '" . $this->code . "' and id = '" . $log_id . "'"); } else { $start_date = $track_date; $end_date = gen_specific_date($track_date, $day_offset = 1); $shipments = $db->Execute("select id, ref_id, deliver_date, actual_date, tracking_id, notes \n\t\t\t\tfrom " . TABLE_SHIPPING_LOG . " \n\t\t\t\twhere carrier = '" . $this->code . "' \n\t\t\t\t\tand ship_date >= '" . $start_date . "' and ship_date < '" . $end_date . "'"); } while (!$shipments->EOF) { $tracking_number = $shipments->fields['tracking_id']; if ($shipments->fields['actual_date'] != '0000-00-00 00:00:00') { // skip if already tracked $shipments->MoveNext(); continue; } $request = $this->FormatFedExTrackRequest($shipments->fields['tracking_id']); if (!$request) { continue; } //echo 'request = '; print_r($request); echo '<br />'; try { $response = $client->track($request); //echo 'Request <pre>' . htmlspecialchars($client->__getLastRequest()) . '</pre>'; //echo 'Response <pre>' . htmlspecialchars($client->__getLastResponse()) . '</pre>'; if ($response->HighestSeverity != 'FAILURE' && $response->HighestSeverity != 'ERROR') { $actual_date = str_replace('T', ' ', $response->TrackDetails->ActualDeliveryTimestamp); $actual_date = substr($actual_date, 0, -6); // see if the package was late, flag if so $late = '0'; // no data if ($shipments->fields['deliver_date'] < $actual_date) { $late = 'L'; // $messageStack->add(SHIPPING_FEDEX_V7_TRACK_FAIL . $shipments->fields['ref_id'], 'error'); } elseif ($response->TrackDetails->StatusCode != 'DL') { $late = 'T'; $messageStack->add(sprintf(SHIPPING_FEDEX_V7_TRACK_STATUS, $shipments->fields['ref_id'], $response->TrackDetails->StatusCode, $response->TrackDetails->StatusDescription), 'caution'); } // update the log file with the actual delivery timestamp, append notes $db->Execute("update " . TABLE_SHIPPING_LOG . " \n\t\t\t\t\t set actual_date = '" . $actual_date . "', deliver_late = '" . $late . "' where id = " . $shipments->fields['id']); // $messageStack->add(SHIPPING_FEDEX_V7_TRACK_SUCCESS . $response->TrackDetails->ActualDeliveryTimestamp, 'success'); } else { foreach ($response->Notifications as $notification) { if (is_object($notification)) { $message .= ' (' . $notification->Severity . ') ' . $notification->Message; } else { $message .= ' ' . $notification; } } $messageStack->add(SHIPPING_FEDEX_V7_TRACK_ERROR . $message, 'error'); return false; } } catch (SoapFault $exception) { //echo 'Error Request <pre>' . htmlspecialchars($client->__getLastRequest()) . '</pre>'; //echo 'Error Response <pre>' . htmlspecialchars($client->__getLastResponse()) . '</pre>'; $message = " ({$exception->faultcode}) {$exception->faultstring}"; $messageStack->add(SHIPPING_FEDEX_CURL_ERROR . $message, 'error'); return false; } $shipments->MoveNext(); } return true; }
$template_options = array(); switch (JOURNAL_ID) { case 3: $req_date = gen_spiffycal_db_date_short(gen_specific_date('', 0, 1, 0)); $template_options['terminal_date'] = array('title' => constant('ORD_TEXT_' . JOURNAL_ID . '_EXPIRES'), 'field' => '<script type="text/javascript">dateRequired.writeControl(); dateRequired.displayLeft=true; dateRequired.dateFormat="' . DATE_FORMAT_SPIFFYCAL . '";</script>'); $template_options['terms'] = array('title' => ACT_TERMS_DUE, 'field' => html_input_field('terms_text', gen_terms_to_language('0', true, 'ap'), 'readonly="readonly" size="25"') . ' ' . html_icon('apps/accessories-text-editor.png', ACT_TERMS_DUE, 'small', 'align="top" style="cursor:pointer" onclick="TermsList()"')); $template_options['closed'] = array('title' => TEXT_CLOSE, 'field' => html_checkbox_field('closed', '1', $order->closed ? true : false, '', '')); break; case 4: $req_date = gen_spiffycal_db_date_short(gen_specific_date('', 0, 1, 0)); $template_options['terminal_date'] = array('title' => constant('ORD_TEXT_' . JOURNAL_ID . '_EXPIRES'), 'field' => '<script type="text/javascript">dateRequired.writeControl(); dateRequired.displayLeft=true; dateRequired.dateFormat="' . DATE_FORMAT_SPIFFYCAL . '";</script>'); $template_options['terms'] = array('title' => ACT_TERMS_DUE, 'field' => html_input_field('terms_text', gen_terms_to_language('0', true, 'ap'), 'readonly="readonly" size="25"') . ' ' . html_icon('apps/accessories-text-editor.png', ACT_TERMS_DUE, 'small', 'align="top" style="cursor:pointer" onclick="TermsList()"')); $template_options['closed'] = array('title' => TEXT_CLOSE, 'field' => html_checkbox_field('closed', '1', $order->closed ? true : false, '', '')); break; case 6: $req_date = gen_spiffycal_db_date_short(gen_specific_date('', 0, 1, 0)); $template_options['terms'] = array('title' => ACT_TERMS_DUE, 'field' => html_input_field('terms_text', gen_terms_to_language('0', true, 'ap'), 'readonly="readonly" size="25"') . ' ' . html_icon('apps/accessories-text-editor.png', ACT_TERMS_DUE, 'small', 'align="top" style="cursor:pointer" onclick="TermsList()"')); $template_options['waiting'] = array('title' => ORD_WAITING_FOR_INVOICE, 'field' => html_checkbox_field('waiting', '1', $order->waiting ? true : false, '', '')); break; case 7: $req_date = date(DATE_FORMAT, time()); $template_options['terms'] = array('title' => ACT_TERMS_DUE, 'field' => html_input_field('terms_text', gen_terms_to_language('0', true, 'ap'), 'readonly="readonly" size="25"') . ' ' . html_icon('apps/accessories-text-editor.png', ACT_TERMS_DUE, 'small', 'align="top" style="cursor:pointer" onclick="TermsList()"')); $template_options['waiting'] = array('title' => ORD_WAITING_FOR_INVOICE, 'field' => html_checkbox_field('waiting', '1', $order->waiting ? true : false, '', '')); break; case 9: $req_date = date(DATE_FORMAT, time()); $template_options['terminal_date'] = array('title' => constant('ORD_TEXT_' . JOURNAL_ID . '_EXPIRES'), 'field' => '<script type="text/javascript">dateRequired.writeControl(); dateRequired.displayLeft=true; dateRequired.dateFormat="' . DATE_FORMAT_SPIFFYCAL . '";</script>'); $template_options['terms'] = array('title' => ACT_TERMS_DUE, 'field' => html_input_field('terms_text', gen_terms_to_language('0', true, 'ap'), 'readonly="readonly" size="25"') . ' ' . html_icon('apps/accessories-text-editor.png', ACT_TERMS_DUE, 'small', 'align="top" style="cursor:pointer" onclick="TermsList()"')); $template_options['closed'] = array('title' => TEXT_CLOSE, 'field' => html_checkbox_field('closed', '1', $order->closed ? true : false, '', '')); break; case 10:
function calulate_special_fields($id) { global $db; $today = date('Y-m-d'); $new_data = array(); $result = $db->Execute("select m.journal_id, m.post_date, sum(i.debit_amount) as debits, sum(i.credit_amount) as credits \r\n\t from " . TABLE_JOURNAL_MAIN . " m inner join " . TABLE_JOURNAL_ITEM . " i on m.id = i.ref_id\r\n\t where m.id = " . $id . " and i.gl_type <> 'ttl' group by m.id"); if (in_array($result->fields['journal_id'], array(6, 7))) { $total_billed = $result->fields['debits'] - $result->fields['credits']; $late_30 = gen_specific_date($today, -AP_AGING_DATE_1); $late_60 = gen_specific_date($today, -AP_AGING_DATE_2); $late_90 = gen_specific_date($today, -AP_AGING_DATE_3); $negate = true; } else { $total_billed = $result->fields['credits'] - $result->fields['debits']; $late_30 = gen_specific_date($today, -AR_AGING_PERIOD_1); $late_60 = gen_specific_date($today, -AR_AGING_PERIOD_2); $late_90 = gen_specific_date($today, -AR_AGING_PERIOD_3); $negate = false; } $post_date = $result->fields['post_date']; $result = $db->Execute("select sum(i.debit_amount) as debits, sum(i.credit_amount) as credits \r\n\t from " . TABLE_JOURNAL_MAIN . " m inner join " . TABLE_JOURNAL_ITEM . " i on m.id = i.ref_id\r\n\t where i.so_po_item_ref_id = " . $id . " and m.journal_id in (18, 20) and i.gl_type in ('pmt', 'chk')"); $total_paid = $result->fields['credits'] - $result->fields['debits']; $balance = $total_billed - $total_paid; $new_data['balance_0'] = 0; $new_data['balance_30'] = 0; $new_data['balance_60'] = 0; $new_data['balance_90'] = 0; if ($post_date < $late_90) { $new_data['balance_90'] = $balance; } elseif ($post_date < $late_60) { $new_data['balance_60'] = $balance; } elseif ($post_date < $late_30) { $new_data['balance_30'] = $balance; } else { $new_data['balance_0'] = $balance; } return $new_data; }
} // Reset all other price sheet default flags if set to this price sheet if ($default_sheet) { $db->Execute("update " . TABLE_PRICE_SHEETS . " \r\n\t\t\tset default_sheet = '0' \r\n\t\t\twhere sheet_name <> '" . $sheet_name . "'"); } $sql = $action == 'save' ? 'insert into ' : 'update '; $sql .= TABLE_PRICE_SHEETS . " set \r\n\t\tsheet_name = '" . $sheet_name . "', \r\n\t\trevision = '" . $revision . "', \r\n\t\teffective_date = '" . $effective_date . "', \r\n\t\tdefault_sheet = '" . $default_sheet . "', \r\n\t\tdefault_levels = '" . $default_levels . "'"; $sql .= $action == 'save' ? '' : ' where id = ' . $id; $result = $db->Execute($sql); // Set all price sheets with this name to default if ($default_sheet) { $db->Execute("update " . TABLE_PRICE_SHEETS . " \r\n\t\t\tset default_sheet = '1' \r\n\t\t\twhere sheet_name = '" . $sheet_name . "'"); } // set expiration date of previous rev if there is a older rev of this price sheet if ($effective_date != '') { $db->Execute("update " . TABLE_PRICE_SHEETS . " \r\n\t\t\tset expiration_date = '" . gen_specific_date($effective_date, -1) . "' \r\n\t\t\twhere sheet_name = '" . $sheet_name . "' and revision = " . ($revision - 1)); } gen_add_audit_log(PRICE_SHEETS_LOG . ($action == 'save') ? TEXT_SAVE : TEXT_UPDATE, $sheet_name); gen_redirect(html_href_link(FILENAME_DEFAULT, gen_get_all_get_params(array('psID', 'action')), 'SSL')); break; case 'delete': if ($security_level < 4) { $messageStack->add_session(ERROR_NO_PERMISSION, 'error'); gen_redirect(html_href_link(FILENAME_DEFAULT, gen_get_all_get_params(array('action')), 'SSL')); break; } $id = (int) db_prepare_input($_GET['psID']); $result = $db->Execute("select sheet_name from " . TABLE_PRICE_SHEETS . " where id = " . $id); $sheet_name = $result->fields['sheet_name']; // TBD check to see if this is the default if so, remind to pick another $db->Execute("delete from " . TABLE_PRICE_SHEETS . " where id = '" . $id . "'");
function get_chart_data($operation, $data) { global $db, $currencies; $output = array('type' => 'pie', 'width' => '600', 'height' => '400'); switch ($operation) { case 'annual_sales': $output['type'] = 'column'; $output['title'] = CONTACTS_CHART_SALES_TITLE; $output['label_text'] = TEXT_DATE; $output['value_text'] = TEXT_TOTAL; $id = $data[0]; if (!$id) { return false; } $dates = gen_get_dates(gen_specific_date(date(Y - m - d), 0, 0, -1)); $result = $db->Execute("SELECT month(post_date) as month, year(post_date) as year,\n sum(total_amount) as total from " . TABLE_JOURNAL_MAIN . " \n \t\t where bill_acct_id = {$id} and journal_id in (12,13) and post_date >= '" . $dates['ThisYear'] . '-' . $dates['ThisMonth'] . "-01' \n \t\t group by year, month limit 12"); for ($i = 0; $i < 12; $i++) { if ($result->fields['year'] == $dates['ThisYear'] && $result->fields['month'] == $dates['ThisMonth']) { $value = $result->fields['total']; $result->MoveNext(); } else { $value = 0; } $output['data'][] = array('label' => $dates['ThisYear'] . '-' . $dates['ThisMonth'], 'value' => $value); $dates['ThisMonth']++; if ($dates['ThisMonth'] == '13') { $dates['ThisYear']++; $dates['ThisMonth'] = '01'; } } break; default: return false; } return $output; }
function load_report_data($report, $Seq, $sql = '', $GrpField = '') { global $db; // find list of accounts within search filter $today = date('Y-m-d'); $late_30 = gen_specific_date($today, -AR_AGING_PERIOD_1); $late_60 = gen_specific_date($today, -AR_AGING_PERIOD_2); $late_90 = gen_specific_date($today, -AR_AGING_PERIOD_3); $sql_fields = substr($sql, strpos($sql, 'select ') + 7, strpos($sql, ' from ') - 7); // prepare the sql by temporarily replacing calculated fields with real fields $this->sql_field_array = explode(', ', $sql_fields); for ($i = 0; $i < count($this->sql_field_array); $i++) { $this->sql_field_karray['c' . $i] = substr($this->sql_field_array[$i], 0, strpos($this->sql_field_array[$i], ' ')); } $temp_sql = str_replace(' FROM ', ', ' . TABLE_JOURNAL_MAIN . '.id, journal_id, post_date, total_amount, bill_acct_id FROM ', $sql); $temp_sql = $this->replace_special_fields($temp_sql); $result = $db->Execute($temp_sql); if ($result->RecordCount() == 0) { return false; } // No data so bail now while (!$result->EOF) { for ($i = 0; $i < sizeof($this->sql_field_karray); $i++) { $this->accounts[$result->fields['bill_acct_id']]['c' . $i] = $result->fields['c' . $i]; } $negate = in_array($result->fields['journal_id'], array(7, 13)) ? true : false; $balance = $negate ? -$result->fields['total_amount'] : $result->fields['total_amount']; $balance -= $this->fetch_paid_amounts($result->fields['id']); if ($result->fields['post_date'] < $late_90) { $this->accounts[$result->fields['bill_acct_id']]['balance_90'] += $balance; } elseif ($result->fields['post_date'] < $late_60) { $this->accounts[$result->fields['bill_acct_id']]['balance_60'] += $balance; } elseif ($result->fields['post_date'] < $late_30) { $this->accounts[$result->fields['bill_acct_id']]['balance_30'] += $balance; } else { $this->accounts[$result->fields['bill_acct_id']]['balance_0'] += $balance; } $result->MoveNext(); } // Generate the output data array $RowCnt = 0; // Row counter for output data $ColCnt = 1; $GrpWorking = false; foreach ($this->accounts as $myrow) { // Check to see if a total row needs to be displayed if (isset($GrpField)) { // we're checking for group totals, see if this group is complete if ($myrow[$GrpField] != $GrpWorking && $GrpWorking !== false) { // it's a new group so print totals $OutputArray[$RowCnt][0] = 'g:' . $GrpWorking; foreach ($Seq as $offset => $TotalCtl) { $OutputArray[$RowCnt][$offset + 1] = ProcessData($TotalCtl['grptotal'], $TotalCtl['processing']); $Seq[$offset]['grptotal'] = ''; // reset the total } $RowCnt++; // go to next row } $GrpWorking = $myrow[$GrpField]; // set to new grouping value } $OutputArray[$RowCnt][0] = 'd'; // let the display class know its a data element //echo 'orig myrow = '; print_r($myrow); echo '<br /><br />'; $myrow = $this->replace_data_fields($myrow, $Seq); //echo 'new myrow = '; print_r($myrow); echo '<br /><br />'; foreach ($Seq as $key => $TableCtl) { // // insert data into output array and set to next column $OutputArray[$RowCnt][$ColCnt] = ProcessData($myrow[$TableCtl['fieldname']], $TableCtl['processing']); $ColCnt++; if ($TableCtl['total']) { // add to the running total if need be $Seq[$key]['grptotal'] += $myrow[$TableCtl['fieldname']]; $Seq[$key]['rpttotal'] += $myrow[$TableCtl['fieldname']]; } } $RowCnt++; $ColCnt = 1; } if ($GrpWorking !== false) { // if we collected group data show the final group total $OutputArray[$RowCnt][0] = 'g:' . $GrpWorking; foreach ($Seq as $TotalCtl) { $OutputArray[$RowCnt][$ColCnt] = $TotalCtl['total'] == '1' ? ProcessData($TotalCtl['grptotal'], $TotalCtl['processing']) : ' '; $ColCnt++; } $RowCnt++; $ColCnt = 1; } // see if we have a total to send $ShowTotals = false; foreach ($Seq as $TotalCtl) { if ($TotalCtl['total'] == '1') { $ShowTotals = true; } } if ($ShowTotals) { $OutputArray[$RowCnt][0] = 'r:' . $report->title; foreach ($Seq as $TotalCtl) { if ($TotalCtl['total']) { $OutputArray[$RowCnt][$ColCnt] = ProcessData($TotalCtl['rpttotal'], $TotalCtl['processing']); } else { $OutputArray[$RowCnt][$ColCnt] = ' '; } $ColCnt++; } } return $OutputArray; }
function trackPackages($track_date = '0000-00-00', $log_id = false) { // single tracking # or date range global $db, $messageStack; // $result = array(); if ($log_id) { $shipments = $db->Execute("select id, ref_id, deliver_date, actual_date, tracking_id, notes \n\t\tfrom " . TABLE_SHIPPING_LOG . " where carrier = '{$this->code}' and id = '{$log_id}'"); } else { $start_date = $track_date; $end_date = gen_specific_date($track_date, $day_offset = 1); $shipments = $db->Execute("select id, ref_id, deliver_date, actual_date, tracking_id, notes \n\t\tfrom " . TABLE_SHIPPING_LOG . " where carrier = '{$this->code}' and ship_date >= '{$start_date}' and ship_date < '{$end_date}'"); } if ($shipments->RecordCount() == 0) { return 'No records were found!'; } $xml = "<StatusRequest>\n"; $xml .= xmlEntry('AccountID', MODULE_SHIPPING_ENDICIA_ACCOUNT_NUMBER); $xml .= xmlEntry('PassPhrase', MODULE_SHIPPING_ENDICIA_PASS_PHRASE); $xml .= xmlEntry('Test', MODULE_SHIPPING_ENDICIA_TEST_MODE == 'Test' ? 'Y' : 'N'); $xml .= xmlEntry('FullStatus', 'N'); while (!$shipments->EOF) { $xml .= " <StatusList>\n"; $xml .= xmlEntry('PICNumber', $shipments->fields['tracking_id']); $xml .= " </StatusList>\n"; $shipments->MoveNext(); } $xml .= "</StatusRequest>\n"; $client = new SoapClient(MODULE_SHIPPING_ENDICIA_ELS_URL, array('trace' => 1)); try { $response = $client->StatusRequest($xml); //echo 'Request <pre>' . htmlspecialchars($client->__getLastRequest()) . '</pre>'; //echo 'Response <pre>' . htmlspecialchars($client->__getLastResponse()) . '</pre>'; if ($response->StatusResponse->Status == 0) { if (!is_array($response->StatusResponse->StatusList)) { $response->StatusResponse->StatusList = array($response->StatusResponse->StatusList); } foreach ($response->StatusResponse->StatusList as $status) { $description = $status->PICNumber->Status; $status_code = $status->PICNumber->StatusCode; $message = sprintf(SHIPPING_ENDICIA_TRACK_STATUS, $shipments->fields['ref_id'], $shipments->fields['tracking_id'], $description); $messageStack->add($message, $status_code == 'D' ? 'success' : 'caution'); } } else { $message = TEXT_ERROR . ' (' . $response->StatusResponse->Status . ') ' . $response->StatusResponse->ErrorMsg; $messageStack->add($message, 'error'); } } catch (SoapFault $exception) { //echo 'Error Request <pre>' . htmlspecialchars($client->__getLastRequest()) . '</pre>'; //echo 'Error Response <pre>' . htmlspecialchars($client->__getLastResponse()) . '</pre>'; $message = "SOAP Error ({$exception->faultcode}) {$exception->faultstring}"; $messageStack->add($message, 'error'); } return $message; }