//else then always use previous day as period
ReportBookkeeping::setOptionArchiveQueryAsExcel(true);
$logger->write('Generating Period');
$period_timestamp = date('G') >= 23 ? time() : strtotime('-1 day');
$period = date(ReportBookkeeping::PERIOD_DATE_FORMAT, $period_timestamp);
$logger->write('Query Invoices in Period');
$invoice_quantity = ReportBookkeeping::getInvoiceTotalQuantity($period);
$invoice_amount = ReportBookkeeping::getInvoiceTotalAmount($period);
$logger->write('Query Receipts in Period');
$receipts = ReportBookkeeping::getReceiptTotal($period);
$receipts_late_from_prev_month = ReportBookkeeping::getReceiptTotalLateFromPreviousPeriod($period);
$logger->write('Query Returns');
$returns = ReportBookkeeping::getReturnTotal($period);
$returns_same_period = ReportBookkeeping::getTotalReturnsSamePeriod($period);
$logger->write('Query Open Amount');
$open_amount = ReportBookkeeping::getOpenInvoiceTotalAmount($period, ReportBookkeeping::OPEN_INVOICE_MODE_CURRENT);
$open_amount_prev0 = ReportBookkeeping::getOpenInvoiceTotalAmount($period, ReportBookkeeping::OPEN_INVOICE_MODE_CURRENT_PREV_PERIOD0);
$open_amount_prev1 = ReportBookkeeping::getOpenInvoiceTotalAmount($period, ReportBookkeeping::OPEN_INVOICE_MODE_CURRENT_PREV_PERIOD1);
$open_amount_prev2 = ReportBookkeeping::getOpenInvoiceTotalAmount($period, ReportBookkeeping::OPEN_INVOICE_MODE_CURRENT_PREV_PERIOD2);
$logger->write('Query Stock Total');
$stock_total_qty = ReportBookkeeping::getStockQuantityTotal(WAREHOUSE_ID_FOR_HANDLING_ORDERS);
$stock_total_value = ReportBookkeeping::getStockValueTotal(WAREHOUSE_ID_FOR_HANDLING_ORDERS);
$logger->write('Query Stock Incoming');
$stock_incoming_qty = ReportBookkeeping::getStockIncomingQuantityTotal();
$stock_incoming_value = ReportBookkeeping::getStockIncomingValueTotal();
$logger->write('Save bookkeeping data for period ' . $period);
ReportBookkeeping::add($period, $invoice_quantity, $invoice_amount, $receipts, $receipts_late_from_prev_month, $returns, $returns_same_period, $open_amount, $open_amount_prev0, $open_amount_prev1, $open_amount_prev2, $stock_total_qty, $stock_total_value, $stock_incoming_qty, $stock_incoming_value);
}
$logger->close();
echo 'Done!';
tep_db_close();
$row6_total_invoice_amount[] = $bkdata[$period]['invoice_amount'];
$row7_total_returns_amount[] = $bkdata[$period]['returns_amount'];
$row8_blank_line[] = '';
$row9_calculated_open_amount[] = $open_amount;
$row10_open_amount[] = $bkdata[$period]['open_amount'];
$row11_blank_line[] = '';
$row12_open_amount_diff[] = $open_amount_diff;
$row13_blank_line[] = '';
$row14_open_amount_prev_0[] = $bkdata[$period]['open_amount_prev_0'];
$row15_open_amount_prev_1[] = $bkdata[$period]['open_amount_prev_1'];
$row16_open_amount_prev_2[] = $bkdata[$period]['open_amount_prev_2'];
$row17_blank_line[] = '';
$row18_returns_same_period[] = $bkdata[$period]['returns_amount_same_period'];
$row19_returns_previous_period[] = $bkdata[$period]['returns_amount'] - $bkdata[$period]['returns_amount_same_period'];
$row20_blank_line[] = '';
$row21_confirmed_payment_prev[] = ReportBookkeeping::getTotalReceiptsConfirmedFromPreviousPeriod($period);
$row22_sent_to_atriga[] = $sent_to_atriga['invoice_amount_in_period'];
$row23_total_after_returns[] = $total_after_returns['invoice_amount_in_period'];
}
}
$table = array();
$table[] = $row1_period;
$table[] = $row2_blank_line;
$table[] = $row3_previous_open_amount;
$table[] = $row4_incoming_amount_real;
$table[] = $row5_outgoing_amount_real;
$table[] = $row6_total_invoice_amount;
$table[] = $row7_total_returns_amount;
$table[] = $row8_blank_line;
$table[] = $row9_calculated_open_amount;
$table[] = $row10_open_amount;
<?php
use_class('ReportBookkeeping');
$period = tep_db_prepare_input($_GET['period']);
ReportBookkeeping::$output_query_as_table = true;
ReportBookkeeping::queryInvoices($period);
exit;
private static function queryStockIncoming()
{
$archive_table = self::$archive_query_in_excel;
$total_incoming_qty = 0;
$total_incoming_value = 0;
if ($archive_table) {
//Create new excel object
$excel_row = 1;
$obj_excel = new PHPExcel();
$obj_sheet = $obj_excel->getActiveSheet();
$obj_sheet->getColumnDimension('A')->setWidth(15);
$obj_sheet->getColumnDimension('B')->setWidth(15);
$obj_sheet->getColumnDimension('C')->setWidth(15);
$obj_sheet->getColumnDimension('D')->setWidth(15);
$obj_sheet->getColumnDimension('E')->setWidth(15);
$obj_sheet->getColumnDimension('F')->setWidth(15);
//Set Header Row
$obj_sheet->setCellValue('A1', 'Product ID');
$obj_sheet->setCellValue('B1', 'EAN');
$obj_sheet->setCellValue('C1', 'Purchase Price');
$obj_sheet->setCellValue('D1', 'Stock Incoming Qty');
$obj_sheet->setCellValue('E1', 'Stock Incoming Value');
}
$q = "SELECT products_id, products_ean" . ", SUM(total_qty) AS total_qty FROM ((" . " SELECT products_id, products_ean" . ", SUM(quantity) AS total_qty" . " FROM depot_orders" . " WHERE status = 7" . " GROUP BY products_ean" . " ) UNION ALL (" . " SELECT products_id, products_ean" . ", SUM(order_quantity) AS total_qty" . " FROM jng_sp_orders_items" . " WHERE status = 7" . " GROUP BY products_ean" . " ) UNION ALL (" . " SELECT products_id, products_ean" . ", SUM(products_quantity) AS total_qty" . " FROM orders_products op" . " WHERE op.status = 7" . " GROUP BY products_id" . " )) temp_table" . " GROUP BY products_ean";
$r = tep_db_query($q);
while ($row = tep_db_fetch_array($r)) {
$pid = $row['products_id'];
$ean = $row['products_ean'];
$stock_qty = $row['total_qty'];
$obj_product = new Product($pid);
$purchase_price = $obj_product->getCOGSObject()->purchase_price;
$stock_value = $stock_qty * $purchase_price;
$total_incoming_qty += $stock_qty;
$total_incoming_value += $stock_value;
if ($archive_table) {
$excel_row++;
//Set Data Row
$obj_sheet->setCellValue('A' . $excel_row, $pid);
$obj_sheet->setCellValue('B' . $excel_row, $ean);
$obj_sheet->setCellValue('C' . $excel_row, $purchase_price);
$obj_sheet->setCellValue('D' . $excel_row, $stock_qty);
$obj_sheet->setCellValue('E' . $excel_row, $stock_value);
}
}
if ($archive_table) {
$period = date(self::PERIOD_DATE_FORMAT, strtotime('-1 day'));
self::saveArchiveFile($obj_excel, $period, self::ARCHIVE_FILENAME_STOCK_INCOMING);
}
self::$current_stock_incoming_qty = $total_incoming_qty;
self::$current_stock_incoming_value = $total_incoming_value;
}
}
echo '<tr>';
foreach ($row as $cn => $cv) {
echo '<td>' . $cv . '</td>';
}
echo '</tr>';
}
}
if ($display_table) {
echo '</table>';
}
$result = array();
$result['invoice_qty_queried'] = $invoice_qty_queried;
$result['invoice_qty_in_period'] = $invoice_qty_in_period;
$result['invoice_amount_in_period'] = $invoice_amount_in_period;
return $result;
}
}
$year = isset($_GET['y']) && $_GET['y'] != '' ? tep_db_prepare_input($_GET['y']) : date('Y');
$periods = array();
for ($m = 1; $m <= 12; $m++) {
$periods[] = $year . str_pad($m, 2, '0', STR_PAD_LEFT);
}
echo '<h1>Total Invoice after Returns (OTTO only)</h1>';
foreach ($periods as $p) {
$after_returns = ReportBookkeeping::getTotalAfterReturns($p);
echo '<h3>';
echo 'Period ' . $p . ': ' . displayCurrency('EUR', $after_returns['invoice_amount_in_period']);
echo '</h3>';
}
tep_db_close();
} else {
$confirmed_payment_from_prev_period = ReportBookkeeping::getReceiptTotalLateFromPreviousPeriod($period);
}
$sent_to_atriga = ReportBookkeeping::getTotalAtriga($period);
$total_after_returns = ReportBookkeeping::getTotalAfterReturns($period);
$detail_icon_returns_amount = '<span class="ui-icon ui-icon-triangle-1-s pointer returns-amount-trigger" title="Click to show/hide detail of total returns amount"></span>';
$detail_icon_open_amount = '<span class="ui-icon ui-icon-triangle-1-s pointer open-amount-prev-trigger" title="Click to show/hide detail open amount per invoice period"></span>';
$archive_icon = '<span class="ui-icon ui-icon-disk pointer" title="Click here to download detail data of this value"></span>';
$archive_icon_atriga = '<a target="_blank" href="?open=report-bookkeeping&download=atriga-detail-list&period=' . $period . '">' . $archive_icon . '</a>';
if (is_file(DIR_FS_ARCHIVES . ReportBookkeeping::ARCHIVE_PATH . $bkdata['archive_invoice_in_period'])) {
$archive_icon_invoice_in_period = ReportBookkeeping::displayArchiveDownloadLinkAsHTML($bkdata['archive_invoice_in_period'], $archive_icon);
$archive_icon_invoice_open = ReportBookkeeping::displayArchiveDownloadLinkAsHTML($bkdata['archive_invoice_open'], $archive_icon);
$archive_icon_receipt_in_period = ReportBookkeeping::displayArchiveDownloadLinkAsHTML($bkdata['archive_receipt_late_in_period'], $archive_icon);
$archive_icon_return_in_period = ReportBookkeeping::displayArchiveDownloadLinkAsHTML($bkdata['archive_return_in_period'], $archive_icon);
$archive_icon_stock_end_of_period = ReportBookkeeping::displayArchiveDownloadLinkAsHTML($bkdata['archive_stock_end_of_period'], $archive_icon);
$archive_icon_stock_incoming_end_of_period = ReportBookkeeping::displayArchiveDownloadLinkAsHTML($bkdata['archive_stock_incoming_end_of_period'], $archive_icon);
} else {
$archive_icon_invoice_in_period = ' ';
$archive_icon_invoice_open = ' ';
$archive_icon_receipt_in_period = ' ';
$archive_icon_return_in_period = ' ';
$archive_icon_stock_end_of_period = ' ';
$archive_icon_stock_incoming_end_of_period = ' ';
}
$result .= '<div class="ui-corner-top bold tac" style="background:#ccc;padding:10px 13px;"><h3 style="margin:0;">' . $period_text . '</h3></div>';
$result .= '<div class="box ui-corner-bottom">';
$result .= '<table border="0" cellpadding="0" cellspacing="0">';
$result .= '<tr><td class="w250">Previous Open Amount</td><td class="w100 tar">' . displayCurrency('EUR', $bkdata_prev['open_amount'], false) . '</td><td> </td><td> </td></tr>';
$result .= '<tr><td class="red"><em>Incoming Amount (real)</em></td><td id="paid-formatted-period-' . $period . '" class="tar red">' . displayCurrency('EUR', $bkdata['manual_input_paid_amount'], false) . '</td><td>' . $edit_icon_paid . '</td><td> </td></tr>';
$result .= '<tr><td><em>Outgoing Amount (real)</em></td><td id="refund-formatted-period-' . $period . '" class="tar">' . displayCurrency('EUR', $bkdata['manual_input_refund_amount'], false) . '</td><td>' . $edit_icon_refund . '</td><td> </td></tr>';
$result .= '<tr><td>Total Invoice Amount</td><td class="tar">' . displayCurrency('EUR', $bkdata['invoice_amount'], false) . '</td><td> </td><td>' . $archive_icon_invoice_in_period . '</td></tr>';