File: D:/HostingSpaces/SBogers10/deensekroon.komma-mediadesign.nl/wwwroot/admin/php/orders.php
<?php
include_once($_SERVER['DOCUMENT_ROOT'] . '/php/Vend/Products/ProductService.php');
/*
orders.php
Mike Ontwerpt 2012
www.mikeontwerpt.nl
*/
function initOrders(){
$output = '';
if(isset($_POST['sbm-edit-status'])){
updateStatus();
}
else if(isset($_POST['sbm_sortfilter'])){
sortFilter();
}
else if(isset($_POST['downloadExcel'])){
createSimpleExcelOrder();
}
else if(isset($_POST['askMoveToOrderTrash'])){
$output .= askMoveToOrderTrash();
}
else if(isset($_POST['moveToOrderTrash'])){
$output .= moveToOrderTrash();
}
else if(isset($_POST['updatePayments'])){
$output .= updatePayments();
}
else if(defined('URL_SUB')){
$subpage = URL_SUB;
switch($subpage){
case 'ordernr':
$output .= showOrder();
break;
default:
$output .= showDashboard();
}
}
else{
$output .= showDashboard();
}
return $output;
}
function showDashboard(){
## settings ##
//// global sql \\\\
global $mysqli;
//// language \\\\
$siteLabels = getLanguage();
## output ##
$output = '';
//// title \\\\
$output .= '<h1>'.$siteLabels['orders-title'].'</h1>';
$output .= '<form action="" method="post" enctype="multipart/form-data" class="margin-bottom-40">';
$output .= '<div class="submenu">';
$output .= '<input type="submit" name="downloadExcel" value="Download Excel Bestand" class="btn184 proceed"/>';
$output .= '<input type="submit" name="askMoveToOrderTrash" value="'.$siteLabels['products-removeproducts'].'" class="btn184"/>';
$output .= '<input type="submit" name="updatePayments" value="Update Payments" class="btn184 proceed" onclick="showLoading();"/> ';
$output .= '<div class="clear"></div>';
//loading
$output .= '<span id="loading"><img src="/admin/images/structure/loading.gif" /> updating payments, this may take a while.. do not close the page </span>';
$output .= '</div>';
/* SORT & FILTER */
# sort & filter
$i = 0;
$sortarr[$i]['val'] = 'lastUpdate';
$sortarr[$i]['label'] = 'Last Update';
$i++;
$sortarr[$i]['val'] = 'orderCode';
$sortarr[$i]['label'] = 'Order code';
$i++;
$sortarr[$i]['val'] = 'customerId';
$sortarr[$i]['label'] = 'Customer';
$i++;
$sortarr[$i]['val'] = 'orderDate';
$sortarr[$i]['label'] = 'Order date';
$i++;
$sortarr[$i]['val'] = 'paymentStatus';
$sortarr[$i]['label'] = 'Payment status';
$i++;
$sortarr[$i]['val'] = 'timest';
$sortarr[$i]['label'] = 'Added on';
$i++;
$sortby = '';
$sortdir = '';
$filterquery = '';
if(defined('URL_SUB') && substr(URL_SUB,0,6) == 'sortby'){
# check sort
$sortby = substr(URL_SUB,9);
$sortdir = substr(URL_SUB, 7,1);
$sortdir == 'a' ? $sortdir = 'ASC' : $sortdir = 'DESC';
#check filter
$filterby = substr(URL_SUB2,9);
if(!empty($filterby)){
$filterstr = str_replace('-',' ',$filterby);
$filterquery = ' AND (';
# found in customers ?
$customerkey = 0;
$customerId = '';
if($searchCustomerResult = $mysqli->query('SELECT DISTINCT l.id
FROM shop_customer_personal AS p, shop_customer_login AS l
WHERE p.id = l.personalId
AND( p.firstName LIKE "%'.$filterstr.'%"
OR p.lastName LIKE "%'.$filterstr.'%"
OR p.city LIKE "%'.$filterstr.'%"
OR p.street LIKE "%'.$filterstr.'%" )')){
while($searchCustomerRecord = $searchCustomerResult->fetch_assoc()){
$customerId = $searchCustomerRecord['id'];
if($customerkey != 0) $filterquery .= 'OR ';
$filterquery .= 'oi.customerId = '.$customerId.' ';
$customerkey++;
}
if($customerkey != 0) $filterquery .= 'OR ';
$filterquery .= 'oi.orderCode LIKE "%'.$filterstr.'%" ';
$filterquery .= ')';
}
else{
echo $mysqli->error.'<Br />';
}
}
}
## output form
$output .= '<div class="single-column sortfilter">';
# Filter input
$output .= '<strong>Filter</strong><br />';
if(!isset($filterstr)) $filterstr = '';
$output .= '<input type="text" name="filter-by" value="'.$filterstr.'" class="txt" />';
$output .= '</div>';
$output .= '<div class="single-column sortfilter">';
# Sort input
$output .= '<strong>Sort by</strong><br />';
$output .= '<select name="sort-by">';
foreach($sortarr as $key => $value){
$output .= '<option value="'.$value['val'].'" ';
if(isset($sortby) && $value['val'] == $sortby) $output .= 'selected';
$output .= '>'.$value['label'].'</option>';
}
$output .= '</select>';
$output .= '</div>';
$output .= '<div class="single-column sortfilter">';
$output .= '<br /><input type="radio" name="orderdir" value="A" ';
if (empty($sortdir) || $sortdir == 'ASC') $output .= 'checked';
$output .= '> Ascending ';
$output .= '<input type="radio" name="orderdir" value="D" ';
if(isset($sortdir) && $sortdir == 'DESC') $output .= 'checked';
$output .= '> Descending';
$output .= '</div>';
$output .= '<div class="single-column sortfilter">';
# submit button
$output .= '<br /><input type="submit" name="sbm_sortfilter" value="go" class="btn176 proceed no-margin"/>';
$output .= '</div>';
$output .= '<div class="clear"></div><br /><br />';
## onbehandeld ##
$output .= getOrderList(0 , $filterquery, $sortby, $sortdir);
$output .= '<hr class="margin-bottom-40"/>';
/* behandeld */
$output .= getOrderList(1, $filterquery, $sortby, $sortdir);
$output .= '</form>';
return $output;
}
function getOrderList($treated, $filterquery = '', $sortby = '' , $sortdir = '' ){
global $mysqli;
$output = '';
//// get info \\\\
$query = 'SELECT DISTINCT oi.id AS orderId, oi.orderCode, oi.customerId, oi.orderDate, oi.amount, oi.paymentMethod, oi.paymentStatus, oi.shippingMethod, oi.orderStatus, oi.lastUpdate,
oc.invoice_firstName as firstName, oc.invoice_middleName as middleName, oc.invoice_lastName as lastName, oc.shipping_country
FROM shop_order_info AS oi, shop_order_customer AS oc
WHERE oi.id = oc.orderId';
if($treated == 0){
$query .= ' AND ( oi.orderStatus = 0 OR oi.orderStatus = 5 )';
}
else {
$query .= ' AND ( oi.orderStatus = 1 OR oi.orderStatus = 2 OR oi.orderStatus = 3 OR oi.orderStatus = 4 OR oi.orderStatus = 6 ) ';
}
#sort & filter
if(!empty($filterquery)) $query .= $filterquery;
if(!empty($sortby)){ $query .= ' ORDER BY oi.'.$sortby.' '.$sortdir; }
else{ $query .= ' ORDER BY oi.id DESC'; }
if($result = $mysqli->query($query)){
$numOrders = $result->num_rows;
$treated == 0 ? $output .= '<span class="order-note">Er zijn '.$numOrders.' onbehandelde orders</span>' : $output .= '<span class="order-note">Er zijn '.$numOrders.' behandelde orders</span>';
/* top row */
$output .= '<div class="itemlist-row itemlist-heading">';
$output .= '<div class="itemlist-single-column order_code"><input type="checkbox" name="checkCtrl" value="checkAll" onClick="check_all(this);" class="itemlist-cbox-first">orderCode</div>';
$output .= '<div class="itemlist-double-column">name</div>';
$output .= '<div class="itemlist-single-column status-column">amount</div>';
$output .= '<div class="itemlist-single-column">date</div>';
$output .= '<div class="itemlist-single-column">shipping</div>';
$output .= '<div class="itemlist-single-column">payment</div>';
$output .= '<div class="itemlist-single-column">status</div>';
$output .= '<div class="itemlist-single-column itemlist-edit"></div>';
$output .= '</div>';
while($record = $result->fetch_assoc()){
//// store info \\\\
$orderId = $record['orderId'];
$orderCode = $record['orderCode'];
$customerId = $record['customerId'];
$orderDate = $record['orderDate'];
$amount = $record['amount'];
$paymentMethod = $record['paymentMethod'];
$paymentStatus = $record['paymentStatus'];
$shippingMethod = $record['shippingMethod'];
$orderStatus = $record['orderStatus'];
$lastUpdate = $record['lastUpdate'];
$shippingCountry = $record['shipping_country'];
//order status
$orderStatusResult = $mysqli->query('SELECT orderStatus
FROM shop_status
WHERE statusNr = '.$orderStatus.' LIMIT 1');
$orderStatusRecord = $orderStatusResult->fetch_assoc();
$displayOrderStatus = $orderStatusRecord['orderStatus'];
$name = $record['firstName'];
if(!empty($record['middleName'])) $name .= ' '.$record['middleName'];
$name .= ' '.$record['lastName'];
//// create display info \\\\
$orderDate = date('d / m / Y',$orderDate); # orderDate
$temp = explode('.',$amount); # price
isset($temp[1]) ? $displayCents = str_pad($temp[1], 2, STR_PAD_LEFT) : $displayCents = '00';
$displayPrice = '€ '.$temp[0].','.$displayCents;
$lastUpdate = date('d / m / Y',$lastUpdate); # lastUpdate
if( $orderStatus == 0 ) $displayOrderStatus = '<span class="order-untreated">'.$displayOrderStatus.'</span>';
$shippingDisplay = $shippingCountry;
if($shippingMethod == 'pickUpFromShopEindhoven') $shippingDisplay = 'Afhalen';
//display info
$output .= '<div class="itemlist-row">';
$output .= '<div class="itemlist-single-column order_code"><input type="checkbox" name="option[]" id="option" value="'.$orderId.'" class="itemlist-cbox-first"><a href="/admin/orders/ordernr/'.$orderCode.'-'.$orderId.'/">'.$orderCode.'</a></div>';
$output .= '<div class="itemlist-double-column">'.$name.'</div>';
$output .= '<div class="itemlist-single-column status-column">'.$displayPrice.'</div>';
$output .= '<div class="itemlist-single-column">'.$orderDate.'</div>';
$output .= '<div class="itemlist-single-column">'.$shippingDisplay.'</div>';
$output .= '<div class="itemlist-single-column">'.$paymentStatus.'</div>';
$output .= '<div class="itemlist-single-column" style="width: 180px">';
switch($orderStatus){
case 0 : $output .= '<span class="orderlist-orange">';
break;
case 1 :
case 6 :
$output .= '<span class="orderlist-green">';
break;
case 2:
case 4:
case 5:
$output .= '<span class="orderlist-yellow">';
break;
case 3 : $output .= '<span class="orderlist-red">';
break;
default: $output .= '<span class="orderlist-orange">';
}
$output .= $displayOrderStatus;
$output .= '</span>';
$output .= '</div>';
//$output .= '<div class="itemlist-single-column itemlist-edit"></div>';
$output .= '</div>';
}
}
else{
$output .= $query.'<br />';
$output .= $mysqli->error;
}
return $output;
}
function showOrder(){
global $mysqli;
$output = '';
if(defined('URL_SUB2')){
$temp = explode('-',URL_SUB2);
$orderCode = $temp[0];
$orderId = $temp[1];
if($result = $mysqli->query('SELECT DISTINCT oi.id AS orderId, oi.orderCode, oi.customerId, oi.orderDate, oi.amount, oi.shippingCosts, oi.paymentMethod, oi.paymentStatus, oi.paymentDate, oi.shippingMethod, oi.shippingDate, oi.orderStatus, oi.lastUpdate,
oc.customerCode, oc.shipping_title, oc.shipping_firstName, oc.shipping_lastName, oc.shipping_postal, oc.shipping_adres, oc.shipping_city, oc.shipping_country,
oc.invoice_firstName, oc.invoice_lastName, oc.invoice_postal, oc.invoice_adres, oc.invoice_city, oc.invoice_country, oc.email, oc.notes, oc.phone
FROM shop_order_info AS oi, shop_order_customer AS oc
WHERE oi.id = oc.orderId
AND oi.id = '.$orderId.'
LIMIT 1')){
if($result->num_rows == 1){
$record = $result->fetch_assoc();
$orderTimest = $record['orderDate'];
$orderDate = date('d / m / Y',$record['orderDate']);
$paymentMethod = $record['paymentMethod'];
$paymentStatus = $record['paymentStatus'];
$paymentDate = $record['paymentDate'];
if(!empty($paymentDate)) $paymentDate = date('d / m / Y',$paymentDate);
$orderStatus = $record['orderStatus'];
$shippingDate = $record['shippingDate'];
$shippingCosts = $record['shippingCosts'];
$shipping_adres = $record['shipping_adres'];
$shipping_postal = $record['shipping_postal'];
$shipping_city = $record['shipping_city'];
$shipping_country = $record['shipping_country'];
$notes = fromDatabase($record['notes']);
$customerCode = $record['customerCode'];
$customerId = $record['customerId'];
$orderStatusResult = $mysqli->query('SELECT orderStatus
FROM shop_status
WHERE statusNr = '.$orderStatus.' LIMIT 1');
$orderStatusRecord = $orderStatusResult->fetch_assoc();
$displayOrderStatus = $orderStatusRecord['orderStatus'];
if($orderStatus == 1) $displayOrderStatus = 'Verzonden op '.date('d / m / Y',$shippingDate);
switch ($paymentStatus) {
case "completed": // payment complete
$status = 'Betaald op '.$paymentDate;
$currentStatusNr = 1;
break;
case "initialized": // waiting
$status = 'Wachten op betaling';
$currentStatusNr = 2;
break;
case "uncleared": // waiting (credit cards or direct debit)
$status = 'Wachten op betaling';
$currentStatusNr = 2;
break;
case "canceled":
case "void": // canceled
case "declined": // declined
$status = 'Geannuleerd';
$currentStatusNr = 3;
break;
case "refunded": // refunded
$status = 'Refunded';
$currentStatusNr = 4;
break;
case "expired": // expired
$status = 'Verlopen';
$currentStatusNr = 5;
break;
default:
$status = 'onderbroken';
$currentStatusNr = 0;
}
$email = $record['email'];
$phone = $record['phone'];
//// title \\\\
$output .= '<h1>Orders › Order '.$orderCode.'</h1>';
//// buttons \\\\
$output .= '<div class="submenu">';
//buttons
$output .= '<span class="btn176 btn176a proceed" onclick="changeDivHeight(\'hidden-change-status\',150);">Wijzig bestelstatus</span>';
$output .= '<a href="/invoice/invoice.php?invoice='.$orderCode.'&orderId='.$orderId.'" target="_blank"><span class="btn176 btn176a">Maak factuur</span></a>';
$output .= '<a href="/admin/customers/details/'.$customerCode.'-'.$customerId.'/"><span class="btn176 btn176a">Bekijk klant</span></a>';
$output .= '<a href="/admin/orders/"><span class="btn176 btn176a">Terug naar overzicht</span></a>';
$output .= '</div>';
$output .= '<div class="single-column" id="hidden-change-status">';
$output .= '<form action="" method="post">';
//ORDER STATUS
$output .= 'Betaal status *<br />';
$output .= '<select name="new-payment-status">';
if(!$paymentStatusResult = $mysqli->query('SELECT statusNr, paymentStatus
FROM shop_status
ORDER BY statusNr'))
{
//handle errors
$errors[] = 'err';
displayErrors($errors);
}
else{
//display output
while($paymentStatusRecord = $paymentStatusResult->fetch_assoc()){
$selectPaymentStatusNr = $paymentStatusRecord['statusNr'];
$selectPaymentStatus = $paymentStatusRecord['paymentStatus'];
if( ! empty($selectPaymentStatus))
{
$output .= '<option value="'.$selectPaymentStatusNr.'"';
if($selectPaymentStatusNr == $currentStatusNr) $output .= ' selected';
$output .= '>'.$selectPaymentStatus.'</option>';
}
}
}
$output .= '</select>';
//ORDER STATUS
$output .= 'Bestel status *<br />';
$output .= '<select name="new-order-status">';
if(!$orderStatusResult = $mysqli->query('SELECT statusNr, orderStatus
FROM shop_status
ORDER BY statusNr')){
//handle errors
$errors[] = 'err';
displayErrors($errors);
}
else{
//display output
while($orderStatusRecord = $orderStatusResult->fetch_assoc()){
$selectOrderStatusNr = $orderStatusRecord['statusNr'];
$selectOrderStatus = $orderStatusRecord['orderStatus'];
$output .= '<option value="'.$selectOrderStatusNr.'"';
if($selectOrderStatusNr == $orderStatus) $output .= ' selected';
$output .= '>'.$selectOrderStatus.'</option>';
}
}
$output .= '</select>';
$output .= '<input type="hidden" name="orderId" value="'.$orderId.'"/>';
$output .= '<input type="hidden" name="oldOrderStatus" value="'.$orderStatus.'"/>';
$output .= '<input type="hidden" name="oldPaymentStatus" value="'.$currentStatusNr.'"/>';
$output .= '<input type="hidden" name="email" value="'.$email.'"/>';
$output .= '<input type="submit" name="sbm-edit-status" value="Wijzig" class="btn176 proceed"/>';
$output .= '</form>';
$output .= '</div>';
$output .= '<div class="clear"></div>';
$output .= '<div class="order-detail-column">';
$output .= '<h2>order details</h2>';
////order details \\\\
$output .= '<table class="order-details">';
$output .= '<tr>';
$output .= '<td>Order datum</td>';
$output .= '<td>'.$orderDate.'</td>';
$output .= '</tr>';
$output .= '<tr>';
$output .= '<td>Betaal methode</td>';
$output .= '<td>'.$paymentMethod.'</td>';
$output .= '</tr>';
$output .= '<tr>';
$output .= '<td>Betaal status</td>';
$output .= '<td>'.$status.'</td>';
$output .= '</tr>';
$output .= '<tr>';
$output .= '<td>Order status</td>';
$output .= '<td>';
switch($orderStatus){
case 0 : $output .= '<span class="orderlist-red">';
break;
case 1 :
case 6 :
$output .= '<span class="orderlist-green">';
break;
case 2 :
case 4 :
case 5 :
$output .= '<span class="orderlist-yellow">';
break;
default: $output .= '<span class="orderlist-red">';
}
$output .= $displayOrderStatus;
$output .= '</span>';
$output .= '</td>';
$output .= '</tr>';
if(empty($notes)) $notes = '<em>geen opmerking</em>';
$output .= '<tr>';
$output .= '<td colspan="2"> </td>';
$output .= '</tr>';
$output .= '<tr>';
$output .= '<td>Opmerkingen</td>';
$output .= '<td>'.$notes.'</td>';
$output .= '</tr>';
$output .= '</table>';
$totalProductPrice = 0;
//get info instances
$instanceResult = $mysqli->query('SELECT DISTINCT productId, title, price_original, price_current, color, numProducts, stockCode
FROM shop_order_products
WHERE orderId = '.$orderId);
while($instanceRecord = $instanceResult->fetch_assoc()){
//$thumb = '/images/structure/noimage.jpg';
$stockCode = $instanceRecord['stockCode'];
if(empty($stockCode)) $stockCode = '-';
$color = $instanceRecord['color'];
$title = $instanceRecord['title'];
$numProducts = $instanceRecord['numProducts'];
$price = $instanceRecord['price_original'] * $numProducts;
$oldPrice = '';
if($price != ($instanceRecord['price_current']*$numProducts)){
//DISCOUNT
$oldPrice = $price;
$price = $instanceRecord['price_current'] * $numProducts;
}
$totalProductPrice += $price;
if(!empty($oldPrice)) $displayOldPrice = displayPrice($oldPrice);
$displayPrice = displayPrice($price);
$linkTitle = linkname($title);
// temp
$output .= '<div class="order-instance-row';
$output .= '">';
//$output .= '<div class="order-instance-image"><img src="'.$thumb.'" alt="thumb" width="48"/></div>';
$output .= '<div class="order-instance-numproducts">'.$numProducts.'x</div>';
$output .= '<div class="order-instance-title">';
$output .= $title.' <em>( '.$stockCode.' )</em>';
$output .= '<a target="_blank" href="/toont-het-artikel/' . $linkTitle . '-' . $instanceRecord['productId'] . '"> Toon op website</a>';
$output .= '<span class="order-instance-color" id="productcolor' . $stockCode . '"></span>';
$output .= '<script type="text/javascript">colorBar(\'productcolor' . $stockCode . '\',\''.$color.'\');</script>';
$output .= '</div>';
$output .= '<div class="order-instance-price">';
if(!empty($oldPrice)) $output .= ' <span class="line-through">'.$displayOldPrice.'</span> ';
$output .= $displayPrice.'</div>';
$output .= '<div class="clear"></div>';
$output .= '</div>';
}
$output .= '<hr />';
//calculate prices
$totalPrice = $record['amount'];
$gcAmount = 0;
if($gcresult = $mysqli->query('SELECT amount FROM gc_used WHERE orderId = '.$orderId))
{
while($gcrecord = $gcresult->fetch_assoc())
{
$gcAmount += $gcrecord['amount'];
}
}
// Use invoice service
include_once $_SERVER['DOCUMENT_ROOT'] . '/App/Invoice/InvoiceService.php';
$invoiceService = new \App\Invoice\InvoiceService();
// Fix the old way:
if($shippingCosts === null)
$shippingCosts = $invoiceService->estimateShippingCosts($totalPrice,$totalProductPrice,$gcAmount,$shipping_country);
// Prepare values
$subtotalPrice = (float) $totalProductPrice;
$shippingCosts = (float) $shippingCosts;
$totalPrice = (float) $totalPrice;
// Do we have order-discount?
// Check if the total price matches the subtotal + shipping
// If not the order has a discount
$orderDiscount = $invoiceService->calculateOrderDiscount($totalPrice,$subtotalPrice,$shippingCosts,$gcAmount);
$displaySubtotalPrice = displayPrice($subtotalPrice);
$displayDiscountPrice = displayPrice($orderDiscount);
$displayShippingCost = displayPrice($shippingCosts);
$displayTotalPrice = displayPrice($totalPrice);
$invoice_postal = $record['invoice_postal'];
$invoice_adres = $record['invoice_adres'];
$invoice_city = $record['invoice_city'];
$invoice_country = $record['invoice_country'];
//VAT
if($vatresult = $mysqli->query('SELECT VAT FROM countries WHERE isocode = "'.$invoice_country.'" LIMIT 1')){
$vatrecord = $vatresult->fetch_assoc();
$vat = $vatrecord['VAT'];
// if larger then 1 oct 2012
if($orderTimest > 1349054915){
$vat = 21;
}
else{
$vat = 19;
}
$factor = 1 + ($vat / 100);
$exbtw = $totalPrice / $factor;
$btw = $totalPrice - $exbtw;
$displayExBtw = displayPrice($exbtw);
$displayBtw = displayPrice($btw);
}
$output .= '<div class="order-vat-column">';
if(isset($vat) && isset($displayExBtw) && isset($displayBtw))
{
$output .= '<div class="order-vat-label">';
$output .= '<br />ex<br />'.$vat.'% btw<br /><br />totaal';
$output .= '</div>';
$output .= '<div class="order-vat-price">';
$output .= '<br />'.$displayExBtw.'<br />'.$displayBtw.'<br /><br />'.$displayTotalPrice;
$output .= '</div>';
$output .= '<div class="clear"></div>';
}
$output .= '</div>';
// Total price
$output .= '<div class="order-totalprice-column">';
$output .= '<div class="order-totalprice-label">';
//prijs labels
$output .= '<br />subtotaal:<br />';
if($orderDiscount < 0) $output .= 'korting:<br />';
$output .= 'verzendkosten:<br />';
if($gcAmount > 0) $output .= 'Giftcards<br />';
$output .= '<br />totaalprijs';
$output .= '</div>';
$output .= '<div class="order-totalprice-price">';
//subtotaal
$output .= '<br />';
$output .= $displaySubtotalPrice.'<br />';
if($orderDiscount < 0) $output .= '- ' . $displayDiscountPrice.'<br />';
//korting
//if(isset($discount) && !empty($discount)) $output .= '€ 10,00<br />';
//verzendkosten
$output .= $displayShippingCost.'<br />';
// giftcards
if($gcAmount > 0) $output .= '- '.displayPrice($gcAmount).'<br />';
$output .= '<br />';
//totaal
$output .= $displayTotalPrice;
$output .= '</div>';
$output .= '<div class="clear"></div>';
$output .= '</div>';
$output .= '</div>';
// klant details
$shipping_title = $record['shipping_title'];
$shipping_firstname = $record['shipping_firstName'];
$shipping_lastname = $record['shipping_lastName'];
$invoice_firstname = $record['invoice_firstName'];
$invoice_lastname = $record['invoice_lastName'];
$output .= '<div class="order-customer-column">';
$output .= '<h2>klant details</h2>';
$output .= '<table class="order-customer">';
// debiteur nummer
$output .= '<tr>';
$output .= '<td>Debiteurnr</td>';
$output .= '<td class="order-customer-detail">'.$customerCode.'</td>';
$output .= '</tr>';
// voornaam
$output .= '<tr>';
$output .= '<td>Voornaam</td>';
$output .= '<td class="order-customer-detail">'.$invoice_firstname.'</td>';
$output .= '</tr>';
// naam
$output .= '<tr>';
$output .= '<td>Naam</td>';
$output .= '<td class="order-customer-detail">'.$invoice_lastname.'</td>';
$output .= '</tr>';
// email
$output .= '<tr>';
$output .= '<td>Email</td>';
$output .= '<td class="order-customer-detail">'.$email.'</td>';
$output .= '</tr>';
// tel
$output .= '<tr>';
$output .= '<td>Tel.</td>';
$output .= '<td class="order-customer-detail">'.$phone.'</td>';
$output .= '</tr>';
// space
$output .= '<tr>';
$output .= '<td colspan="2"> </td>';
$output .= '</tr>';
if($record['shippingMethod'] == 'pickUpFromShopEindhoven')
{
// space
$output .= '<tr>';
$output .= '<td colspan="2"><strong>Afhalen in Eindhoven</strong></td>';
$output .= '</tr>';
}
else
{
// afleveradres / straat
$output .= '<tr>';
$output .= '<td>Afleveradres.</td>';
$output .= '<td class="order-customer-detail">'.$shipping_firstname . ' ' . $shipping_lastname . '</td>';
$output .= '</tr>';
$output .= '<tr>';
$output .= '<td> </td>';
$output .= '<td class="order-customer-detail">'.$shipping_adres.'</td>';
$output .= '</tr>';
// postcode
$output .= '<tr>';
$output .= '<td> </td>';
$output .= '<td class="order-customer-detail">'.$shipping_postal.' '.$shipping_city.'</td>';
$output .= '</tr>';
// land
$output .= '<tr>';
$output .= '<td> </td>';
$output .= '<td class="order-customer-detail">'.$shipping_country.'</td>';
$output .= '</tr>';
// space
$output .= '<tr>';
$output .= '<td colspan="2"> </td>';
$output .= '</tr>';
// factuuradres / straat
$output .= '<tr>';
$output .= '<td>Factuuradres.</td>';
$output .= '<td class="order-customer-detail">'.$invoice_firstname . ' ' . $invoice_lastname . '</td>';
$output .= '</tr>';
$output .= '<tr>';
$output .= '<td> </td>';
$output .= '<td class="order-customer-detail">'.$invoice_adres.'</td>';
$output .= '</tr>';
// postcode
$output .= '<tr>';
$output .= '<td> </td>';
$output .= '<td class="order-customer-detail">'.$invoice_postal.' '.$invoice_city.'</td>';
$output .= '</tr>';
// land
$output .= '<tr>';
$output .= '<td> </td>';
$output .= '<td class="order-customer-detail">'.$invoice_country.'</td>';
$output .= '</tr>';
// space
$output .= '<tr>';
$output .= '<td colspan="2"> </td>';
$output .= '</tr>';
}
$output .= '</table>';
$output .= '</div>';
$output .= '<div class="clear"></div>';
$output .= '<div class="margin-bottom-40"> </div>';
$output .= '<hr class="margin-bottom-40"/>';
}
else{
$output .= 'Te weinig info over deze order<br />
<a href="/admin/orders/">terug</a>';
}
}
else{
$output .= 'Er is iets mis met deze pagina'.$mysqli->error;
}
}
return $output;
}
function updateStatus(){
global $mysqli;
//get variables
$orderId = $_POST['orderId'];
$newOrderStatus = $_POST['new-order-status'];
$newPaymentStatus = $_POST['new-payment-status'];
$oldOrderStatus = $_POST['oldOrderStatus'];
$oldPaymentStatus = $_POST['oldPaymentStatus'];
$email = $_POST['email'];
//payment status needs to be converted to a string
switch($newPaymentStatus){
case 0 :
//weggeklikt
$newPaymentString = '-';
break;
case 1 :
//betaald
$newPaymentString = 'completed';
break;
case 2 :
//wachten op betaling
$newPaymentString = 'initialized';
break;
case 3 :
//geannuleerd
$newPaymentString = 'canceled';
break;
}
$mysqli->query('UPDATE shop_order_info SET orderStatus = "'.$newOrderStatus.'", paymentStatus = "'.$newPaymentString.'" WHERE id = '.$orderId.' LIMIT 1');
// if verzonden
if($newOrderStatus == 1 && $oldOrderStatus != 1){
$mysqli->query('UPDATE shop_order_info SET shippingDate = '.time().' WHERE id = '.$orderId.' LIMIT 1');
}
// betaald
if($newPaymentStatus == 1 && $oldPaymentStatus != 1){
$mysqli->query('UPDATE shop_order_info SET paymentDate = '.time().' WHERE id = '.$orderId.' LIMIT 1');
}
//updateStock($newOrderStatus, $oldOrderStatus, $orderId);
updateVendStock($newOrderStatus, $oldOrderStatus, $orderId);
//stuur een mail wanneer de bestelstatus veranderd is , maar niet wanneer deze geannuleerd of afgehaald is.
if($newOrderStatus != $oldOrderStatus && $newOrderStatus != 3 && $newOrderStatus != 0 && $newOrderStatus != 6){
// send mail \\
require_once('../php/mailformats.php');
// get msg \\
if($newOrderStatus == 5) // Klaar om af te halen
{
$msg = getMailPickUpFromShop($orderId);
}
else
{
$msg = getChangeStatusMail($orderId);
}
$to = fromDatabase($email);
// headers \\
$headers = "From: Deense Kroon <info@deensekroon.nl>\r\n";
$headers .= "Reply-To: ".$to."\r\n";
$headers .= "MIME-Version: 1.0\r\n";
$headers .= "Content-Type: text/html; charset=ISO-8859-1\r\n";
//subjects \\
$subject = 'Wijziging in je bestelstatus.';
mail($to, $subject, $msg, $headers);
}
$_SESSION['feed-msg-wait'] = '<span class="fm-suc"><span class="fm-suc-v">√</span>De status is succesvol gewijzigd</span>';
header('location: ./');
}
function sortFilter(){
//get sort & filter
$sort = $_POST['sort-by'];
$filter = linkname($_POST['filter-by']);
$direction = linkname($_POST['orderdir']);
header('location: /admin/orders/sortby-'.$direction.'-'.$sort.'/filterby-'.$filter.'/');
}
/**
* When status change, check if we need to send an update to Vend
*
* @param $newStatusId
* @param $oldStatusId
* @param $orderId
* @return bool
*/
function updateVendStock($newStatusId, $oldStatusId, $orderId)
{
// Return false if order-status remains the same
if($newStatusId == $oldStatusId) return false;
if(environment() == 'development') return false;
// Statuses that fire update events
// 1: Verzonden
// 4: Aangemeld voor verzending
// 5: Klaar voor afhalen
if( ! in_array($newStatusId,[1,4,5])) return false;
// Create product service
$productService = new \Vend\Products\ProductService();
// Process order
$productService->processInventoryFromOrder($orderId);
}
function updateStock($newOrderStatus, $oldOrderStatus, $orderId){
global $mysqli;
if($newOrderStatus != $oldOrderStatus){
//if status changed, get all instances in this order
$getQuery = 'SELECT productId, numProducts, size FROM shop_order_products WHERE orderId = '.$orderId;
if($getResult = $mysqli->query($getQuery)){
$queries = array();
while($record = $getResult->fetch_assoc()){
$productId = $record['productId'];
$numproducts = $record['numProducts'];
$size = $record['size'];
//get current stock & inOrder
$stockQuery = 'SELECT inStock FROM shop_stock WHERE productId = '.$productId;
if( ! empty ($size)) $stockQuery .= ' AND size = "' . $size . '" ';
$stockQuery .= ' LIMIT 1';
$stockResult = $mysqli->query($stockQuery);
$stockRecord = $stockResult->fetch_assoc();
$inStock = $stockRecord['inStock'];
// When an order is shipped: (todo: add status "afhalen in winkel")
// Fire an event to VEND with the register sale
if($newOrderStatus == 1){
// if newstatus is verzonden
########## N O T E #############
//inorder has no effect, it is calculated.
$newInStock = $inStock - $numproducts;
$stockQuery = 'UPDATE shop_stock SET inStock = "'.$newInStock.'" WHERE productId = '.$productId;
if( ! empty ($size)) $stockQuery .= ' AND size = "' . $size . '" ';
$stockQuery .= ' LIMIT 1';
$queries[] = $stockQuery;
}
// When a status is set from send to waiting
// Add the products back to the stock (loose this functionality)
else if($newOrderStatus == 2){
// if newstatus is waiting
// what is old status?
if($oldOrderStatus == 1){
#new == 2, #old == 1 -> stock+numproducts
$newInStock = $inStock + $numproducts;
$stockQuery = 'UPDATE shop_stock SET inStock = "'.$newInStock.'" WHERE productId = '.$productId;
if( ! empty ($size)) $stockQuery .= ' AND size = "' . $size . '" ';
$stockQuery .= ' LIMIT 1';
$queries[] = $stockQuery;
}
}
// When a status is set from send to canceled
// Add the products back to the stock (loose this functionality)
else if($newOrderStatus == 3){
// if newstatus is canceled
if($oldOrderStatus == 1){
#new == 3, #old == 1 -> stock+numproducts
$newInStock = $inStock + $numproducts;
$stockQuery = 'UPDATE shop_stock SET inStock = "'.$newInStock.'" WHERE productId = '.$productId;
if( ! empty ($size)) $stockQuery .= ' AND size = "' . $size . '" ';
$stockQuery .= ' LIMIT 1';
$queries[] = $stockQuery;
}
}
// When a status is set from send to open
// Add the products back to the stock (loose this functionality)
else if($newOrderStatus == 0){
// if newstatus is onbehandeld
if($oldOrderStatus == 1){
#new == 0, #old == 1 -> stock+numproducts, inOrder+numproducts
$newInStock = $inStock + $numproducts;
$stockQuery = 'UPDATE shop_stock SET inStock = "'.$newInStock.'" WHERE productId = '.$productId;
if( ! empty ($size)) $stockQuery .= ' AND size = "' . $size . '" ';
$stockQuery .= ' LIMIT 1';
$queries[] = $stockQuery;
}
}
}//end for each
foreach($queries as $query)
{
if($mysqli->query($query))
{
// feedback ?
}
}
}
}
}
function askMoveToOrderTrash(){
/* access the global sql object */
global $mysqli;
/* language */
$siteLabels = getLanguage();
if(isset($_POST['option'])){
$output = '';
//ask the question
$output .= 'Do you want to remove these orders from the orderlist? <br /><br />';
foreach($_POST['option'] as $orderId){
//get info
if($result = $mysqli->query('SELECT DISTINCT oi.id AS orderId, oi.orderCode, oi.customerId, oi.orderDate, oi.amount, oi.paymentMethod, oi.paymentStatus, oi.paymentDate, oi.shippingMethod, oi.shippingDate, oi.orderStatus, oi.lastUpdate,
oc.customerCode, oc.shipping_title, oc.shipping_firstName, oc.shipping_lastName, oc.shipping_postal, oc.shipping_adres, oc.shipping_city, oc.shipping_country,
oc.invoice_firstName, oc.invoice_lastName, oc.invoice_postal, oc.invoice_adres, oc.invoice_city, oc.invoice_country, oc.email, oc.phone
FROM shop_order_info AS oi, shop_order_customer AS oc
WHERE oi.id = oc.orderId
AND oi.id = '.$orderId.'
LIMIT 1')){
$record = $result->fetch_assoc();
//// store info \\\\
$orderId = $record['orderId'];
$orderCode = $record['orderCode'];
$customerId = $record['customerId'];
$orderDate = $record['orderDate'];
$amount = $record['amount'];
$paymentMethod = $record['paymentMethod'];
$paymentStatus = $record['paymentStatus'];
$shippingMethod = $record['shippingMethod'];
$orderStatus = $record['orderStatus'];
$lastUpdate = $record['lastUpdate'];
//order status
$orderStatusResult = $mysqli->query('SELECT orderStatus
FROM shop_status
WHERE statusNr = '.$orderStatus.' LIMIT 1');
$orderStatusRecord = $orderStatusResult->fetch_assoc();
$displayOrderStatus = $orderStatusRecord['orderStatus'];
$name = $record['invoice_firstName'];
$name .= ' '.$record['invoice_lastName'];
//// create display info \\\\
$orderDate = date('d / m / Y',$orderDate); # orderDate
$temp = explode('.',$amount); # price
isset($temp[1]) ? $displayCents = str_pad($temp[1], 2, STR_PAD_LEFT) : $displayCents = '00';
$displayPrice = '€ '.$temp[0].','.$displayCents;
$lastUpdate = date('d / m / Y',$lastUpdate); # lastUpdate
if( $orderStatus == 0 ) $displayOrderStatus = '<span class="order-untreated">'.$displayOrderStatus.'</span>';
//display info
$output .= '<div class="itemlist-row">';
$output .= '<div class="itemlist-double-column">'.$orderCode.'</div>';
$output .= '<div class="itemlist-double-column">'.$name.'</div>';
$output .= '<div class="itemlist-single-column status-column">'.$displayPrice.'</div>';
$output .= '<div class="itemlist-single-column">'.$orderDate.'</div>';
$output .= '<div class="itemlist-single-column">'.$shippingMethod.'</div>';
$output .= '<div class="itemlist-single-column">'.$paymentStatus.'</div>';
$output .= '<div class="itemlist-single-column">'.$displayOrderStatus.'</div>';
$output .= '<div class="itemlist-single-column itemlist-edit"></div>';
$output .= '</div>';
}
}
$output .= '<div class="clear"></div>';
$output .= '<br /><br />';
$output .= '<form action="" name="moveToOrderTrash" method="post" enctype="multipart/form-data">';
foreach($_POST['option'] as $orderId){
$output .= '<input type="hidden" name="orders[]" value="'.$orderId.'" />';
}
$output .= '<input type="submit" name="moveToOrderTrash" value="'.$siteLabels['form-yes'].'" class="btn176"/>';
$output .= '<input type="hidden" name="location" value="../"/>';
$output .= '<input type="submit" name="back" value="'.$siteLabels['form-no'].'" class="btn176 proceed"/>';
$output .= '</form>';
return $output;
}
else{
$errors[] = $siteLabels['fm-nothingselected'];
displayErrors($errors);
//return showDashboard();
header('location: ./');
}
}
function moveToOrderTrash(){
/* access the global sql object */
global $mysqli;
/* language */
$siteLabels = getLanguage();
if(isset($_POST['orders'])){
$output = '';
foreach($_POST['orders'] as $orderId){
/* For each product; change content status to 0 */
$queries[] = 'UPDATE shop_order_info SET orderStatus = "-1" WHERE id ='.$orderId.' LIMIT 1';
}
// Set autocommit to FALSE
$mysqli->autocommit(FALSE);
foreach($queries as $query)
{
if(!$mysqli->query($query))
{
$errors[] = $siteLabels['fm-somethingwentwrong'];
}
}
// ROLLBACK of COMMIT
if(isset($errors))
{
$mysqli->rollback();
displayErrors($errors);
}
else
{
$mysqli->commit();
$msg = str_replace('[numitems]', count($_POST['orders']), $siteLabels['fm-itemsmovedtotrash']);
$_SESSION['feed-msg'] = '<span class="fm-suc"><span class="fm-suc-v">√</span>'.$msg.'</span>';
//return showDashboard();
header('location: ./');
}
}
}
############## UPDATE PAYMENTS ################
function updatePayments(){
global $mysqli;
require_once '../multisafepay/MultiSafepay.class.php';
require_once '../multisafepay/MultiSafepay.config.php';
/*
What to update ?
- All from last month
*/
$now = time();
$from = strtotime('-1 month',$now);
$query = 'SELECT id, paymentStatus
FROM shop_order_info
WHERE orderDate > '.$from.'
AND orderDate <= '.$now.'';
if($result = $mysqli->query($query)){
$updated = 0;
while($record = $result->fetch_assoc()){
# Transaction ID (orderId)
$orderId = $record['id'];
$currentStatus = $record['paymentStatus'];
# new multisafe pay
$msp = new MultiSafepay();
# merchante settings
$msp->test = MSP_TEST_API;
$msp->merchant['account_id'] = MSP_ACCOUNT_ID;
$msp->merchant['site_id'] = MSP_SITE_ID;
$msp->merchant['site_code'] = MSP_SITE_CODE;
$msp->transaction['id'] = $orderId;
# get the payment method & status
$paymentMethod = $msp->getPaymentMethod();
$status = $msp->getStatus();
if($msp->error){
//do nothing
//$output = $msp->error;
}
else if($status != $currentStatus){
# update DB
if($mysqli->query('UPDATE shop_order_info SET paymentMethod = "'.$paymentMethod.'", paymentStatus = "'.$status.'", lastUpdate = "'.time().'" WHERE id = "'.$orderId.'" LIMIT 1')){
# succes!
$updated++;
}
}
}
if($updated > 0){
$_SESSION['feed-msg-wait'] = '<span class="fm-suc"><span class="fm-suc-v">√</span>'.$updated.' order(s) succesfully updated!</span>';
}
else{
$_SESSION['feed-msg-wait'] = '<span class="fm-suc"><span class="fm-suc-v">√</span>No orders updated, all orders up to date!</span>';
}
}
else{
echo $mysqli->error;
}
header ('location: ./');
}
function createSimpleExcelOrder(){
global $mysqli;
$displayOrder = array('orderCode'=>'Bestelnr.','orderDate'=>'Datum','amount'=>'Totaalprijs','verzendkosten'=>'Verzendkosten','stockcode'=>'Product code','price_original'=>'Prijs product','price_current'=>'Prijs betaald','numProducts'=>'Aantal','giftcard'=>'giftcard','invoice_firstName'=>'Voornaam','invoice_lastName'=>'Achternaam','invoice_adres'=>'Adres','invoice_postal'=>'Postcode','invoice_city'=>'Woonplaats','invoice_country'=>'Land','email'=>'E-mail','paymentMethod'=>'Betaalmethode','paymentStatus'=>'Betaalstatus','orderStatus'=>'Status');
$allowedDouble = array('orderCode','price_original','price_current','numProducts','stockcode');
## Get info from the database ##
$query = 'SELECT DISTINCT oi.id, oi.orderCode, oi.orderDate, oi.amount, oc.invoice_firstName, oc.invoice_lastName, oc.invoice_postal, oc.invoice_adres, oc.invoice_city, oc.invoice_country, oc.email, oi.paymentMethod, oi.paymentStatus, oi.orderStatus, oi.lastUpdate
FROM shop_order_info AS oi, shop_order_customer AS oc
WHERE oi.id = oc.orderId
ORDER BY oi.lastUpdate DESC';
$contents="Deense Kroon - Orders\n\n";
if($result = $mysqli->query($query)){
## Set Column width ##
$record = $result->fetch_assoc();
## create columns ##
foreach($displayOrder as $value){
$contents.= strtoupper($value).',';
}
$contents = substr($contents,0,-1);
$contents .= "\n\n";
while($info = $result->fetch_assoc()){
$info['orderCode'] = 'order: ' . $info['orderCode'];
$info['orderDate'] = date('d-m-Y',$info['orderDate']);
// Status
$orderStatusResult = $mysqli->query('SELECT orderStatus
FROM shop_status
WHERE statusNr = '.$info['orderStatus'].' LIMIT 1');
$orderStatusRecord = $orderStatusResult->fetch_assoc();
$displayOrderStatus = $orderStatusRecord['orderStatus'];
$info['orderStatus'] = $displayOrderStatus;
// Giftcard
$info['giftcard'] = 0;
$gcResult = $mysqli->query('SELECT amount
FROM gc_used
WHERE orderId = '.$info['id'].' LIMIT 1');
if($gcResult->num_rows > 0)
{
$gcRecord = $gcResult->fetch_assoc();
$gcAmount = $gcRecord['amount'];
$info['giftcard'] = $gcAmount;
}
$displayInfo = true;
// Producten
$totalProductPrice = 0;
$productResult = $mysqli->query('SELECT DISTINCT stockcode, price_original, price_current, numProducts
FROM shop_order_products
WHERE orderId = '.$info['id']);
while($productRecord = $productResult->fetch_assoc())
{
// Merge arrays
$info = array_merge($info,$productRecord);
foreach(array_keys($displayOrder) as $key)
{
if($key == 'verzendkosten')
{
$value = '[replace_me]';
}
else if($key == 'giftcard')
{
$info['giftcard'] == 0 ? $value = '-' : $value = $info['giftcard'];
}
else
{
$value = $info[$key];
}
/*
$prices = array('price_current','amount','price_original');
if(in_array($key,$prices))
{
}*/
if($displayInfo || in_array($key,$allowedDouble))
{
$value = inDatabase($value);
$contents.= $value.',';
}
else
{
$contents.= ' ,';
}
}
$totalProductPrice = $totalProductPrice + ($productRecord['price_current'] * $productRecord['numProducts']);
$displayInfo = false;
$contents = substr($contents,0,-1);
$contents .= "\n";
}
$deliveryCost = $info['amount'] - ($totalProductPrice - $info['giftcard']);
if($deliveryCost < 0) $deliveryCost = 0;
$contents = str_replace('[replace_me]',$deliveryCost,$contents);
}
}
$contents = strip_tags($contents); // remove html and php tags etc.
Header("Content-Disposition: attachment; filename=" . date('ymd') . "_orders.csv");
print $contents;
exit();
}
?>