// clean up
unlink($file_name);
$file_size = strlen($contents);
header("Content-type: {$backup_mime}");
header("Content-disposition: attachment; filename=" . $backup_filename . "; size=" . $file_size);
header('Pragma: cache');
header('Cache-Control: public, must-revalidate, max-age=0');
header('Connection: close');
header('Expires: ' . date('r', time() + 60 * 60));
header('Last-Modified: ' . date('r', time()));
print $contents;
exit;
}
break;
case 'clean_log':
$temp = gen_get_dates(date('Y-m-d'));
$current_date = date('Y-m-d', mktime(0, 0, 0, $temp['ThisMonth'], 1, $temp['ThisYear']));
$result = $db->Execute("delete from " . TABLE_AUDIT_LOG . " where action_date < '" . $current_date . "'");
$messageStack->add('The number of records deleted was:' . ' ' . $result->AffectedRows(), 'success');
gen_add_audit_log(GEN_AUDIT_DB_DATA_CLEAN);
break;
case 'ordr_nums':
if ($security_level < 3) {
$messageStack->add_session(ERROR_NO_PERMISSION, 'error');
gen_redirect(html_href_link(FILENAME_DEFAULT, gen_get_all_get_params(array('action')), 'SSL'));
break;
}
// read in the requested status values
$sequence_array = array('next_ar_quote_num' => db_prepare_input($_POST['next_ar_quote_num']), 'next_ap_quote_num' => db_prepare_input($_POST['next_ap_quote_num']), 'next_deposit_num' => db_prepare_input($_POST['next_deposit_num']), 'next_so_num' => db_prepare_input($_POST['next_so_num']), 'next_po_num' => db_prepare_input($_POST['next_po_num']), 'next_check_num' => db_prepare_input($_POST['next_check_num']), 'next_inv_num' => db_prepare_input($_POST['next_inv_num']), 'next_cm_num' => db_prepare_input($_POST['next_cm_num']), 'next_vcm_num' => db_prepare_input($_POST['next_vcm_num']), 'next_shipment_num' => db_prepare_input($_POST['next_shipment_num']), 'next_cust_id_num' => db_prepare_input($_POST['next_cust_id_num']), 'next_vend_id_num' => db_prepare_input($_POST['next_vend_id_num']));
// post them to the current_status table
$result = db_perform(TABLE_CURRENT_STATUS, $sequence_array, 'update', 'id > 0');
function build_audit_xml($date_from, $date_to, $select)
{
global $db, $messageStack, $coa_types_list, $currencies;
$tax_auths = gen_build_tax_auth_array();
$dates = gen_get_dates($date_from);
$output = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' . chr(10);
$output .= '<auditfile>' . chr(10);
$output .= '<header>' . chr(10);
$output .= xmlEntry('auditfileVersion', 'CLAIR2.00.00', true);
$output .= xmlEntry('companyID', substr(htmlspecialchars(AUDIT_DEBIT_NUMBER), 0, 20), true);
$output .= xmlEntry('taxRegistrationNr', substr(htmlspecialchars(TAX_ID), 0, 15), true);
$output .= xmlEntry('companyName', substr(htmlspecialchars(COMPANY_NAME), 0, 50), true);
$output .= xmlEntry('companyAddress', substr(htmlspecialchars(COMPANY_ADDRESS1), 0, 50), true);
$output .= xmlEntry('companyCity', substr(htmlspecialchars(COMPANY_CITY_TOWN), 0, 50), true);
$output .= xmlEntry('companyPostalCode', substr(htmlspecialchars(COMPANY_POSTAL_CODE), 0, 10), true);
$output .= xmlEntry('fiscalYear', $dates['ThisYear'], true);
$output .= xmlEntry('startDate', $date_from, true);
$output .= xmlEntry('endDate', $date_to, true);
$output .= xmlEntry('currencyCode', DEFAULT_CURRENCY, true);
$output .= xmlEntry('dateCreated', date('Y-m-d'), true);
$output .= xmlEntry('productID', 'Phreebooks', true);
$output .= xmlEntry('productVersion', 'Phreebooks =' . MODULE_PHREEBOOKS_STATUS . ' audit=' . MODULE_AUDIT_STATUS, true);
//$output .= xmlEntry('',);
$output .= '</header>' . chr(10);
$output .= '<generalLedger>' . chr(10);
//all general ledger account
$income_types = array(30, 32, 34);
//$output .= xmlEntry('taxonomy','',true); //Zie toelichting *)
$result = $db->Execute("select * from " . TABLE_CHART_OF_ACCOUNTS . " where heading_only = '0'");
while (!$result->EOF) {
$temp = $coa_types_list[$result->fields['account_type']]['text'];
$output .= "\t" . '<ledgerAccount>' . chr(10);
$output .= "\t" . xmlEntry('accountID', $result->fields['id'], true);
//generalLedger id
$output .= "\t" . xmlEntry('accountDesc', substr(htmlspecialchars($result->fields['description']), 0, 50), true);
//generalLedger description
$output .= "\t" . xmlEntry('accountType', in_array($result->fields['account_type'], $income_types) ? TEXT_INCOME_STATEMENT : TEXT_BALANCE_SHEET, true);
//generalLedger Type balance or income
$output .= "\t" . xmlEntry('leadCode', $result->fields['account_type'], true);
//gl account type id *)
$output .= "\t" . xmlEntry('leadDescription', constant($coa_types_list[$result->fields['account_type']]['text']), true);
//GL account Type description *)
$output .= "\t" . '</ledgerAccount>' . chr(10);
$result->MoveNext();
}
$output .= '</generalLedger>' . chr(10);
$output .= '<customersSuppliers>' . chr(10);
// all contacts
$contacts = array();
$result = $db->Execute("select * from " . TABLE_CONTACTS . " where inactive = '0' and type in('v','c') ");
while (!$result->EOF) {
$contacts[$result->fields['id']] = $result->fields['short_name'];
$output .= "\t" . '<customerSupplier>' . chr(10);
$output .= "\t" . xmlEntry('custSupID', $result->fields['short_name'], true);
// vendor- of customer id
$output .= "\t" . xmlEntry('type', $result->fields['type'] == 'v' ? ACT_V_TYPE_NAME : ACT_C_TYPE_NAME, true);
// type Vendor or customer
$output .= "\t" . xmlEntry('taxRegistrationNr', htmlspecialchars($result->fields['gov_id_number']), true);
//tax id
$output .= "\t" . xmlEntry('taxVerificationDate', $result->fields['gov_id_number_date'], true);
//tax verification date (not present in phreedom) maybe in custom fields
$address = $db->Execute("select * from " . TABLE_ADDRESS_BOOK . " where ref_id = '" . $result->fields['id'] . "'");
while (!$address->EOF) {
if (substr($address->fields['type'], 1, 2) == 'm') {
$output .= "\t" . xmlEntry('companyName', htmlspecialchars($address->fields['primary_name']), true);
//company name
$output .= "\t" . xmlEntry('contact', htmlspecialchars($address->fields['contact']), true);
//contact person
$output .= "\t" . xmlEntry('telephone', htmlspecialchars($address->fields['telephone1']), true);
//company telephone
$output .= "\t" . xmlEntry('fax', htmlspecialchars($address->fields['telephone3']), true);
//company fax
$output .= "\t" . xmlEntry('email', htmlspecialchars($address->fields['email']), true);
//company email
$output .= "\t" . xmlEntry('website', htmlspecialchars($address->fields['website']), true);
//company URL website
//company billing address
$output .= "\t\t" . '<postalAddress>' . chr(10);
$output .= "\t\t" . xmlEntry('address', substr(htmlspecialchars($address->fields['address1'] . ' ' . $address->fields['address2']), 0, 50), true);
$output .= "\t\t" . xmlEntry('city', htmlspecialchars($address->fields['city_town']), true);
$output .= "\t\t" . xmlEntry('postalCode', htmlspecialchars($address->fields['postal_code']), true);
$output .= "\t\t" . xmlEntry('region', htmlspecialchars($address->fields['state_province']), true);
$output .= "\t\t" . xmlEntry('country', $address->fields['country_code'], true);
$output .= "\t\t" . '</postalAddress>' . chr(10);
} else {
if (substr($address->fields['type'], 1, 2) == 's') {
//company shipping address
$output .= "\t\t" . '<streetAddress>' . chr(10);
$output .= "\t\t" . xmlEntry('address', substr(htmlspecialchars($address->fields['address1'] . ' ' . $address->fields['address2']), 0, 50), true);
$output .= "\t\t" . xmlEntry('city', htmlspecialchars($address->fields['city_town']), true);
$output .= "\t\t" . xmlEntry('postalCode', htmlspecialchars($address->fields['postal_code']), true);
$output .= "\t\t" . xmlEntry('region', htmlspecialchars($address->fields['state_province']), true);
$output .= "\t\t" . xmlEntry('country', $address->fields['country_code'], true);
$output .= "\t\t" . '</streetAddress>' . chr(10);
}
}
$address->MoveNext();
}
$output .= "\t" . '</customerSupplier>' . chr(10);
$result->MoveNext();
}
$output .= '</customersSuppliers>' . chr(10);
$output .= '<transactions>' . chr(10);
// all journal lines.
if ($select == '1') {
$where = " and journal_id not in ('3','4','9','10') and waiting = '0' ";
}
$totals = $db->Execute("select sum(i.debit_amount) as totalDebit, sum(i.credit_amount) as totalCredit from " . TABLE_JOURNAL_MAIN . " m join " . TABLE_JOURNAL_ITEM . " i on m.id=i.ref_id where m.post_date >= '" . $date_from . "' and m.post_date<='" . $date_to . "'" . $where);
$result = $db->Execute("select * from " . TABLE_JOURNAL_MAIN . " where post_date >= '" . $date_from . "' and post_date<='" . $date_to . "' " . $where . " order by journal_id ASC");
$output .= xmlEntry('numberEntries', $result->RecordCount(), true);
$total_credit = 0;
$total_credit = 0;
//$output .= xmlEntry('totalDedit', $totals->fields['totalDebit'] ,true);
//$output .= xmlEntry('totalCredit', $totals->fields['totalCredit'] ,true);
//if(number_format($totals->fields['totalDebit'],2) <> number_format($totals->fields['totalCredit'],2)) return false;
$previous_journal_id = '';
$output .= "\t" . '<journal>' . chr(10);
while (!$result->EOF) {
$line_debit = 0;
$line_credit = 0;
if ($previous_journal_id != $result->fields['journal_id']) {
if ($previous_journal_id != '') {
$output .= "\t" . '</journal>' . chr(10);
$output .= "\t" . '<journal>' . chr(10);
}
$output .= "\t" . xmlEntry('journalID', $result->fields['journal_id'], true);
//the journal id
$output .= "\t" . xmlEntry('description', constant('GEN_ADM_TOOLS_J' . str_pad($result->fields['journal_id'], 2, '0', STR_PAD_LEFT)), true);
//the journal description
$output .= "\t" . xmlEntry('type', '', true);
//type of journal
}
$output .= "\t\t" . '<transaction>' . chr(10);
$output .= "\t\t" . xmlEntry('transactionID', $result->fields['id'], true);
$output .= "\t\t" . xmlEntry('description', htmlspecialchars($result->fields['description']), true);
$output .= "\t\t" . xmlEntry('period', $result->fields['period'], true);
$output .= "\t\t" . xmlEntry('transactionDate', $result->fields['post_date'], true);
$output .= "\t\t" . xmlEntry('sourceID', $result->fields['admin_id'], true);
$line = $db->Execute("select id, gl_account, post_date, description, ROUND(debit_amount," . $currencies->currencies[DEFAULT_CURRENCY]['decimal_places'] . ") as debit_amount, ROUND(credit_amount," . $currencies->currencies[DEFAULT_CURRENCY]['decimal_places'] . ") as credit_amount, taxable from " . TABLE_JOURNAL_ITEM . " where ref_id= '" . $result->fields['id'] . "'");
while (!$line->EOF) {
$output .= "\t\t\t" . '<line>' . chr(10);
$output .= "\t\t\t" . xmlEntry('recordID', $line->fields['id'], true);
// Uniek regelnummer
$output .= "\t\t\t" . xmlEntry('accountID', $line->fields['gl_account'], true);
// Grootboekrekeningcode (zie hiervoor)
$output .= "\t\t\t" . xmlEntry('custSupID', $contacts[$result->fields['bill_acct_id']], true);
// Debiteuren- of crediteurennummer (zie hiervoor)
$output .= "\t\t\t" . xmlEntry('documentID', $result->fields['purchase_invoice_id'], true);
// Boekstuknummer (verwijzing naar brondocument)
$output .= "\t\t\t" . xmlEntry('effectiveDate', $line->fields['post_date'], true);
// Mutatiedatum *)
$output .= "\t\t\t" . xmlEntry('description', htmlspecialchars($line->fields['description']), true);
// Omschrijving
$line_debit += $line->fields['debit_amount'];
$output .= "\t\t\t" . xmlEntry('debitAmount', $line->fields['debit_amount'], true);
// Debetbedrag in lokale valuta (zie hiervoor)
$line_credit += $line->fields['credit_amount'];
$output .= "\t\t\t" . xmlEntry('creditAmount', $line->fields['credit_amount'], true);
// Creditbedrag in lokale valuta (zie hiervoor)
// $output .= "\t\t\t" . xmlEntry('costDesc', $line->fields[''] ,true);// Kostenplaats
// $output .= "\t\t\t" . xmlEntry('productDesc', $line->fields[''] ,true);// Kostendrager
$output .= "\t\t\t" . xmlEntry('projectDesc', $line->fields['project_id'], true);
// Projectcode (i.p.v. kostensoort)
//De BTW (vat) wordt als volgt uitgesplitst:
if ($line->fields['taxable'] != '0') {
$output .= "\t\t\t\t" . '<vat>' . chr(10);
$output .= "\t\t\t\t" . xmlEntry('vatCode', $line->fields['taxable'], true);
// BTW-code (leeg betekent geen BTW)
$output .= "\t\t\t\t" . xmlEntry('vatPercentage', $tax_auths[$line->fields['taxable']]['tax_rate'] / 100, true);
// BTW-percentage, of in plaats daarvan BTW-bedrag
// $output .= "\t\t\t\t" . xmlEntry('vatAmount', $line->fields[''] ,true);// BTW-bedrag (bij bijzondere transacties)
$output .= "\t\t\t\t" . '</vat>' . chr(10);
}
//De valuta (currency) wordt vervolgens als volgt weergegeven:
if ($result->fields['currencies_code'] != DEFAULT_CURRENCY) {
$output .= "\t\t\t\t" . '<currency>' . chr(10);
$output .= "\t\t\t\t" . xmlEntry('currencyCode', $result->fields['currencies_code'], true);
// Valutacode (leeg betekent lokale valuta)
$output .= "\t\t\t\t" . xmlEntry('currencyDebitAmount', $result->fields['currencies_value'], true);
// Debetbedrag in vreemde valuta (i.p.v. koers)
// $output .= "\t\t\t\t" . xmlEntry('currencyCreditAmount', $result->fields['currencies_value'] ,true);// Creditbedrag in vreemde valuta (i.p.v. koers)
$output .= "\t\t\t\t" . '</currency>' . chr(10);
}
$output .= "\t\t\t" . '</line>' . chr(10);
$previous_journal_id = $result->fields['journal_id'];
$line->MoveNext();
}
if ((double) (string) $line_debit != (double) (string) $line_credit) {
if (DEBUG) {
$output .= '<lineError>' . chr(10);
$output .= xmlEntry('recordID', $result->fields['id'], true);
// Uniek regelnummer
$output .= xmlEntry('lineDebit', $line_debit, true);
$output .= xmlEntry('lineCredit', $line_credit, true);
$output .= '</lineError>' . chr(10);
}
$error = $messageStack->add('The journal with id ' . $result->fields['id'] . ' is out of balance total Debit = ' . $line_debit . ' total Credit = ' . $line_credit, 'error');
}
$total_debit += $line_debit;
$total_credit += $line_credit;
$output .= "\t\t" . '</transaction>' . chr(10);
$result->MoveNext();
}
$output .= "\t" . '</journal>' . chr(10);
if ((double) (string) $total_debit != (double) (string) $total_credit) {
$error = $messageStack->add('Totals are out of balance total Debit = ' . $total_debit . ' total Credit = ' . $total_credit, 'error');
}
$output .= xmlEntry('totalDedit', $total_debit, true);
$output .= xmlEntry('totalCredit', $total_credit, true);
$output .= '</transactions>' . chr(10);
$output .= '</auditfile>' . chr(10);
if ($error) {
return false;
}
return $output;
}
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_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 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 gen_specific_date($start_date, $day_offset = 0, $month_offset = 0, $year_offset = 0)
{
global $messageStack;
$date_details = gen_get_dates($start_date);
if ($date_details['ThisYear'] > '1900' && $date_details['ThisYear'] < '2099') {
// check for current day greater than the month will allow (for recurs)
$days_in_month = date('t', mktime(0, 0, 0, $date_details['ThisMonth'] + $month_offset, 1, $date_details['ThisYear'] + $year_offset));
$mod_this_day = min($days_in_month, $date_details['ThisDay']);
return date('Y-m-d', mktime(0, 0, 0, $date_details['ThisMonth'] + $month_offset, $mod_this_day + $day_offset, $date_details['ThisYear'] + $year_offset));
} else {
$messageStack->add(sprintf(GEN_CALENDAR_FORMAT_ERROR, $raw_date, DATE_FORMAT, DATE_FORMAT_CALENDAR), 'error');
return date('Y-m-d');
}
}
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;
}
// skip, should not happen
}
}
// install reports now that categories are set up
if ($_POST['phreeform_action'] != 'data') {
// if=data reports have been copied, else load basic reports
foreach ($copy_modules as $entry) {
admin_add_reports($entry, DIR_FS_MY_FILES . $db_name . '/phreeform/');
}
}
}
if (!$error && $_POST['phreebooks_action'] != 'data') {
// install fiscal year if the phreebooks data is not copied
$db->Execute("TRUNCATE TABLE " . TABLE_ACCOUNTING_PERIODS);
require_once DIR_FS_MODULES . 'phreebooks/functions/phreebooks.php';
$dates = gen_get_dates();
validate_fiscal_year($dates['ThisYear'], '1', $dates['ThisYear'] . '-' . $dates['ThisMonth'] . '-01');
build_and_check_account_history_records();
gen_auto_update_period(false);
}
if (!$error) {
// reset SESSION['company'] to new company and redirect to install->store_setup
$db->Execute("update " . TABLE_CONFIGURATION . " set configuration_value = '" . $co_name . "' \n\t where configuration_key = 'COMPANY_NAME'");
$messageStack->add(SETUP_CO_MGR_CREATE_SUCCESS, 'success');
gen_add_audit_log(SETUP_CO_MGR_LOG . TEXT_COPY, $db_name);
$_SESSION['db_server'] = $db_server;
$_SESSION['company'] = $db_name;
$_SESSION['db_user'] = $db_user;
$_SESSION['db_pw'] = $db_pw;
gen_redirect(html_href_link(FILENAME_DEFAULT, $get_parmas, ENABLE_SSL_ADMIN ? 'SSL' : 'NONSSL'));
} else {
function gen_build_sql_date($date_prefs, $df)
{
global $db;
$dates = gen_get_dates();
$DateArray = explode(':', $date_prefs);
$t = time();
$ds = '0000-00-00';
// pick a start date a long time ago
$de = '2199-00-00';
// pick an end date a long time from now
switch ($DateArray[0]) {
// based on the date choice selected
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_short($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_short($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_date_short($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_date_short($ds) . ' ' . TEXT_TO . ' ' . gen_date_short(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_date_short($ds) . ' ' . TEXT_TO . ' ' . gen_date_short($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_date_short($ds) . ' ' . TEXT_TO . ' ' . gen_date_short(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_date_short($ds) . ' ' . TEXT_TO . ' ' . gen_date_short($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_date_short($ds) . ' ' . TEXT_TO . ' ' . gen_date_short($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_date_short($ds) . ' ' . TEXT_TO . ' ' . gen_date_short($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_date_short($ds) . ' ' . TEXT_TO . ' ' . gen_date_short($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_date_short($ds) . ' ' . TEXT_TO . ' ' . gen_date_short($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_date_short(CURRENT_ACCOUNTING_PERIOD_START) . ' ' . TEXT_TO . ' ' . gen_date_short(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_date_short($ds) . ' ' . TEXT_TO . ' ' . gen_date_short($de) . '); ';
break;
}
$dates = array('sql' => $d, 'description' => $fildesc, 'start_date' => $ds, 'end_date' => $de);
return $dates;
}
/*************** hook for custom actions ***************************/
$custom_path = DIR_FS_MODULES . 'audit/custom/pages/main/extra_actions.php';
if (file_exists($custom_path)) {
include $custom_path;
}
/*************** Act on the action request *************************/
switch ($_REQUEST['action']) {
case 'export_audit':
//search for contacts, gl_accounts and journals
$output = build_audit_xml($date_from, $date_to, $select);
if ($output == false) {
$messageStack->add(GL_ERROR_BALANCE, 'error');
gen_redirect(html_href_link(FILENAME_DEFAULT, gen_get_all_get_params(array('action')), 'SSL'));
break;
}
$dates = gen_get_dates($date_from);
header("Content-type: plain/txt;");
header("Content-disposition: attachment; filename=aud_" . $dates['ThisYear'] . ".xaf; size=" . strlen($output));
header('Pragma: cache');
header('Cache-Control: public, must-revalidate, max-age=0');
header('Connection: close');
header('Expires: ' . date('r', time() + 3600));
header('Last-Modified: ' . date('r'));
print $output;
exit;
default:
}
/***************** prepare to display templates *************************/
$sel_options = array(array('id' => '0', 'text' => TEXT_ALL), array('id' => '1', 'text' => TEXT_EXCLUDE));
$cal_from = array('name' => 'dateFrom', 'form' => 'site_search', 'fieldname' => 'date_from', 'imagename' => 'btn_date_1', 'default' => $_GET['date_from'], 'params' => array('align' => 'left'));
$cal_to = array('name' => 'dateTo', 'form' => 'site_search', 'fieldname' => 'date_to', 'imagename' => 'btn_date_2', 'default' => $_GET['date_to'], 'params' => array('align' => 'left'));
