\r\n";
$headers .= "Reply-To: $admin_email\r\n";
$sql = "SELECT id,first_name,surname,email FROM customer WHERE $active_condition AND $region_condition AND $rand_condition AND $unique_condition";
$rs = $conn->Execute($sql);
$customer_count = 0;
while (!$rs->EOF) {
$customer_count++;
$customer_id = $rs->fields['id'];
$email = $rs->fields['email'];
$customised_body = "Hi ".$rs->fields['first_name']."\n\n$email_body"; //Postfix hack removes the standard \r before the \n to avoid duplicate linebreaks
mail($email, $subject, $customised_body,$headers);
// To ensure mails only get sent once in case script is re-run
if ($unique == '1') {
$sql = "UPDATE customer SET last_email_sent = '$next_delivery_date' WHERE customer.id='$customer_id'";
$rs2 = $conn->Execute($sql);
}
$customer_array[] = $customer_count.' '.$rs->fields['first_name'].' '.$rs->fields['surname'].' '.$rs->fields['email'];
$rs->MoveNext();
}
return $customer_array;
}
function customer_dropdown($conn,$region='',$dropdown_name='customer_id',$select_customer_id='',$login='',$transaction_details='',$manual_login_condition='') {
if ($region == '') {
$region_condition = '1';
}
else {
$region_condition = "region_id='$region'";
}
if ($login == '') {
$login_condition = '1';
}
else {
$login_condition = "last_login >= '$login'";
}
if ($manual_login_condition == '') {
$manual_login_condition = 1;
}
$sql = "SELECT first_name,surname,rand_balance,id AS customer_id FROM customer WHERE $region_condition AND $login_condition AND $manual_login_condition ORDER BY surname,first_name";
$rs = $conn->Execute($sql);
$customer_dropdown = " ($customer_count customers) ";
return $customer_dropdown;
}
function supplier_id_dropdown($conn) {
$sql = "SELECT supplier,id FROM supplier WHERE active ='1' ORDER BY supplier";
$rs = $conn->Execute($sql);
$supplier_id_dropdown = "";
return $supplier_id_dropdown;
}
function product_dropdown($conn,$supplier_flag=0) {
$sql = "SELECT product.description,product.id,supplier FROM product LEFT JOIN supplier ON product.supplier_id=supplier.id WHERE visible = 1 ORDER BY supplier,description";
$rs = $conn->Execute($sql);
$product_dropdown = "";
return $product_dropdown;
}
//Check to see if customer has already ordered
function customer_ordered($conn,$customer_id,$date) {
$sql = "SELECT customer_id,order_id,homedelivery,delivery_instructions,collection_point FROM orders WHERE delivery_date = '$date' AND customer_id='$customer_id'";
$rs = $conn->Execute($sql);
if (($rs->fields['customer_id'] == $customer_id) AND ($customer_id != '')) {
$order_id = $rs->fields['order_id'];
$homedelivery = $rs->fields['homedelivery'];
$collection_point = $rs->fields['collection_point'];
$delivery_instructions = stripslashes($rs->fields['delivery_instructions']);
$old_order = array('prior_order' => 1, 'order_id' => $order_id, 'homedelivery' => "$homedelivery", 'delivery_instructions' => "$delivery_instructions", 'collection_point' => "$collection_point");
}
else {
$old_order = array('prior_order' => 0, 'order_id' => '');
}
return $old_order;
}
function currency_dropdown() {
$text = "";
return $text;
}
function payment_method_dropdown($conn,$username,$default='') {
$sql = "SELECT accept_electronic_payments FROM distributor WHERE username='$username'";
$rs = $conn->Execute($sql);
$allow = $rs->fields['accept_electronic_payments'];
if ($allow == 1) {
if ($default != '') {
$default = "";
$rs->MoveNext();
}
if ($notitle == "") {
$region_dropdown .= "";
}
return $region_dropdown;
}
function active_dropdown($active_only=1) {
if ($active_only == 1) {
$html="";
}
else {
$html="";
}
return $html;
}
function check_login($status=0) {
if ($status == 0) {
if (!isset ($_SESSION['customer_id']) || !$_SESSION['customer_id']) {
//header('Location: http://www.greenman.co.za/sane/login.php');
}
}
else {
if (!isset ($_SESSION['admin_id']) || !$_SESSION['admin_id']) {
header("Location: ".$GLOBALS['admindr']."index.php");
}
return 0;
}
return 1;
}
function summary_details($conn,$date,$end_date,$region_condition,$region_name,$fclass) {
// RETURN COUNT OF ORDERS
$sql = "SELECT COUNT(orders.order_id) AS order_count FROM orders LEFT JOIN customer ON customer.id=orders.customer_id WHERE orders.delivery_date >= '$date' AND orders.delivery_date <= '$end_date' AND $region_condition";
$rs = $conn->Execute($sql);
$order_count = $rs->fields['order_count'];
// RETURN DELIVERY TOTAL
$sql = "SELECT SUM(deliveryrands_cents) AS deliveryrands_cents FROM orders WHERE orders.delivery_date >= '$date' AND orders.delivery_date <= '$end_date' AND $region_condition";
$rs = $conn->Execute($sql);
$deliveryrands_cents = $rs->fields['deliveryrands_cents'];
// RETURN OUTSTANDING AMOUNT
$customer_region_condition = str_replace('orders.','',$region_condition);
$sql = "SELECT SUM(rand_balance) AS rand_balance FROM customer WHERE $customer_region_condition AND rand_balance <0";
$rs = $conn->Execute($sql);
$outstanding_balance = number_format($rs->fields['rand_balance']/100,2);
// RETURN OWING AMOUNT
$sql = "SELECT SUM(rand_balance) AS rand_balance FROM customer WHERE $customer_region_condition AND rand_balance >0";
$rs = $conn->Execute($sql);
$owing_balance = number_format($rs->fields['rand_balance']/100,2);
// RETURN SUMMARY DETAILS
$sql = "SELECT quantity, costprice, salesprice, rands,ubus FROM delivery_product LEFT JOIN delivery ON delivery_product.delivery_id = delivery.delivery_id LEFT JOIN orders ON delivery.order_id = orders.order_id LEFT JOIN customer ON orders.customer_id=customer.id WHERE orders.delivery_date >= '$date' AND orders.delivery_date <= '$end_date' AND $region_condition";
$rs = $conn->Execute($sql);
$rand_sales_grandtotal = 0;
$rand_cost_grandtotal = 0;
// RETURN DISTRIBUTOR CASH COLLECTED
$sql = "";
while (!$rs->EOF) {
$salesprice = $rs->fields['salesprice'];
$costprice = $rs->fields['costprice'];
$quantity = $rs->fields['quantity'];
$rands = $rs->fields['rands'];
$ubus = $rs->fields['ubus'];
if ($rands == 1) {
$rand_product_sales_total = $quantity*$salesprice;
$rand_product_cost_total = $quantity*$costprice;
$rand_sales_grandtotal += $rand_product_sales_total;
$rand_cost_grandtotal += $rand_product_cost_total;
}
$rs->MoveNext(); // Moves to the next row
}
$rand_profit_grandtotal = $rand_sales_grandtotal - $rand_cost_grandtotal;
//$percent15 = $rand_profit_grandtotal/100*15;
//$percent75 = $rand_profit_grandtotal/4*3;
$percent10 = $rand_profit_grandtotal/10;
$percent9 = $rand_profit_grandtotal/100*9;
$percent7 = $rand_profit_grandtotal/100*7;
$percent6 = $rand_profit_grandtotal/100*6;
$percent50 = $rand_profit_grandtotal/2;
$percent5 = $rand_profit_grandtotal/20;
$percent49 = $rand_profit_grandtotal/100*49;
$percent2 = $rand_profit_grandtotal/50;
$percent35 = $rand_profit_grandtotal/100*35;
if ($order_count > 0) {
$avsales = $rand_sales_grandtotal/$order_count;
}
else {
$avsales = 0;
}
/* FRESH, DRY and COLD calculations no longer needed since buyers being paid fixed rate
$sql = "SELECT delivery_product.costprice, quantity FROM product LEFT JOIN delivery_product ON delivery_product.product_id = product.id LEFT JOIN delivery ON delivery.delivery_id = delivery_product.delivery_id LEFT JOIN orders ON orders.order_id=delivery.order_id WHERE packing_category_id = '3' AND delivery.delivery_date >= '$date' AND delivery.delivery_date <= '$end_date' AND $region_condition";
$rs = &$conn->Execute($sql);
$total_f = 0; //fresh
while (!$rs->EOF) {
$buyers_percent = $rs->fields['costprice'] - $rs->fields['costprice']/105*100;
$total_f = $total_f + ($buyers_percent*$rs->fields['quantity']);
$rs->MoveNext(); // Moves to the next row
}
$total_f = number_format($total_f/100,2);
$sql = "SELECT delivery_product.costprice, quantity FROM product LEFT JOIN delivery_product ON delivery_product.product_id = product.id LEFT JOIN delivery ON delivery.delivery_id = delivery_product.delivery_id LEFT JOIN orders ON orders.order_id=delivery.order_id WHERE packing_category_id = '1' AND delivery.delivery_date >= '$date' AND delivery.delivery_date <= '$end_date' AND $region_condition";
$rs = &$conn->Execute($sql);
$total_c = 0; //cold
while (!$rs->EOF) {
$buyers_percent = $rs->fields['costprice'] - $rs->fields['costprice']/105*100;
$total_c = $total_c + ($buyers_percent*$rs->fields['quantity']);
$rs->MoveNext(); // Moves to the next row
}
$total_c = number_format($total_c/100,2);
$sql = "SELECT delivery_product.costprice, quantity FROM product LEFT JOIN delivery_product ON delivery_product.product_id = product.id LEFT JOIN delivery ON delivery.delivery_id = delivery_product.delivery_id LEFT JOIN orders ON orders.order_id = product.id WHERE (packing_category_id = '2') AND delivery.delivery_date >= '$date' AND delivery.delivery_date <= '$end_date' AND $region_condition";
$rs = &$conn->Execute($sql);
$total_d = 0; //dry
while (!$rs->EOF) {
$buyers_percent = $rs->fields['costprice'] - $rs->fields['costprice']/105*100;
$total_d = $total_d + ($buyers_percent*$rs->fields['quantity']);
$rs->MoveNext(); // Moves to the next row
}
$total_d = number_format($total_d/100,2);
*/
/* UPDATED TABLE WITH OLD IRRELEVANT PERCENTAGES REMOVED
$table .= "
$region_name
$order_count
R".number_format($deliveryrands_cents/100,2)."
R".number_format($rand_sales_grandtotal/100,2)."
R".number_format($rand_cost_grandtotal/100,2)."
R".number_format($rand_profit_grandtotal/100,2)."
R".number_format($avsales/100,2)."
R".number_format($percent49/100,2)."
R".number_format($percent10/100,2)."
R".number_format($percent5/100,2)."
R".number_format($percent5/100,2)."
R".number_format($percent2/100,2)."
R$total_f
R$total_c
R$total_d
R$outstanding_balance
R$owing_balance
";
*/
$table .= "
$region_name
$order_count
R".number_format($deliveryrands_cents/100,2)."
R".number_format($rand_sales_grandtotal/100,2)."
R".number_format($rand_cost_grandtotal/100,2)."
R".number_format($rand_profit_grandtotal/100,2)."
R".number_format($avsales/100,2)."
R$outstanding_balance
R$owing_balance
";
return $table;
}
function summary_orders_details($conn,$date,$end_date,$region_condition,$region_name,$fclass) {
// RETURN COUNT OF ORDERS
$sql = "SELECT COUNT(orders.order_id) AS order_count FROM orders LEFT JOIN customer ON customer.id=orders.customer_id WHERE orders.delivery_date >= '$date' AND orders.delivery_date <= '$end_date' AND $region_condition";
$rs = $conn->Execute($sql);
$order_count = $rs->fields['order_count'];
// RETURN NO. OF DELIVERIES
$sql = "SELECT COUNT(orders.order_id) AS deliveries FROM orders WHERE orders.delivery_date >= '$date' AND orders.delivery_date <= '$end_date' AND $region_condition AND orders.homedelivery='yes'";
$rs = $conn->Execute($sql);
$deliveries = $rs->fields['deliveries'];
//RETURN NO. OF COLLECTIONS
$sql = "SELECT COUNT(orders.order_id) AS collections FROM orders WHERE orders.delivery_date >= '$date' AND orders.delivery_date <= '$end_date' AND $region_condition AND orders.homedelivery='no'";
$rs = $conn->Execute($sql);
$collections = $rs->fields['collections'];
// RETURN DELIVERY TOTAL
$sql = "SELECT SUM(deliveryrands_cents) AS deliveryrands_cents FROM orders WHERE orders.delivery_date >= '$date' AND orders.delivery_date <= '$end_date' AND $region_condition";
$rs = $conn->Execute($sql);
$deliveryrands_cents = $rs->fields['deliveryrands_cents'];
// RETURN SUMMARY DETAILS
$sql = "SELECT quantity, costprice, salesprice, rands,ubus FROM delivery_product LEFT JOIN delivery ON delivery_product.delivery_id = delivery.delivery_id LEFT JOIN orders ON delivery.order_id = orders.order_id LEFT JOIN customer ON orders.customer_id=customer.id WHERE orders.delivery_date >= '$date' AND orders.delivery_date <= '$end_date' AND $region_condition";
$rs = $conn->Execute($sql);
$rand_sales_grandtotal = 0;
$rand_cost_grandtotal = 0;
while (!$rs->EOF) {
$salesprice = $rs->fields['salesprice'];
$costprice = $rs->fields['costprice'];
$quantity = $rs->fields['quantity'];
$rands = $rs->fields['rands'];
$ubus = $rs->fields['ubus'];
if ($rands == 1) {
$rand_product_sales_total = $quantity*$salesprice;
$rand_product_cost_total = $quantity*$costprice;
$rand_sales_grandtotal += $rand_product_sales_total;
$rand_cost_grandtotal += $rand_product_cost_total;
}
$rs->MoveNext(); // Moves to the next row
}
$rand_profit_grandtotal = $rand_sales_grandtotal - $rand_cost_grandtotal;
if ($order_count > 0) {
$avsales = $rand_sales_grandtotal/$order_count;
}
else {
$avsales = 0;
}
$table .= "
$region_name
$order_count
$deliveries
$collections
R".number_format($deliveryrands_cents/100,2)."
R".number_format($rand_sales_grandtotal/100,2)."
R".number_format($rand_cost_grandtotal/100,2)."
R".number_format($rand_profit_grandtotal/100,2)."
R".number_format($avsales/100,2)."
";
return $table;
}
function num_regions($active,$conn) {
$sql = "SELECT COUNT(id) AS num_regions FROM region WHERE active='$active'";
$rs = $conn->Execute($sql);
return $rs->fields['num_regions'];
}
function region_array($active,$conn) {
$sql = "SELECT id,shortname FROM region WHERE active='$active' ORDER BY shortname ASC";
$rs = $conn->Execute($sql);
$i = 0;
while (!$rs->EOF) {
$reg_id = $rs->fields['id'];
$shortname = $rs->fields['shortname'];
$region['id'][$i] = $reg_id;
$region['shortname'][$i] = $shortname;
$i++;
$rs->MoveNext();
}
return $region;
}
function view_transactions($conn,$customer_condition,$region_condition,$date,$end_date,$order1,$order2,$fclass,$hclass,$head,$name=1,$admin=1,$longdate=1,$summary=0) {
//Return quantities for current order
$sql = "SELECT customer.id,first_name,surname,amount, currency, type, details, administrator, capture_datetime,method FROM transaction LEFT JOIN customer ON transaction.customer_id=customer.id WHERE capture_datetime >= '$date 00:00:00' AND capture_datetime<='$end_date 23:59:59' AND $region_condition AND $customer_condition ORDER BY $order1,$order2";
$rs = $conn->Execute($sql);
$table_head = $head;
if ($name) {
$name_head = "
";
}
$rs->MoveNext(); // Moves to the next row
} //end while
$table = $table_head . $table;
$transaction_array = array('table'=>$table,'payment_ubu_total'=>$payment_ubu_total,'payment_rand_total'=>$payment_rand_total,'invoice_ubu_total'=>$invoice_ubu_total,'invoice_rand_total'=>$invoice_rand_total,'balance'=>$balance);
return $transaction_array;
}
function insert_transaction($conn,$customer_id,$amount,$currency,$type,$details,$admin,$method) {
$datetime = date('Y-m-d H:i:s');
$sql = "INSERT INTO transaction(customer_id,amount,currency,type,details,administrator,capture_datetime,method) VALUES('$customer_id','$amount','$currency','$type','$details','$admin','$datetime','$method')";
$rs = $conn->Execute($sql);
// Return auto_increment value for transaction id
$sql = "SELECT LAST_INSERT_ID() AS lid";
$rs = $conn->Execute($sql);
$last_insert_id = $rs->fields['lid'];
// Now update customer total
if ($currency == 'R') {$currency_string = 'rand_balance';}
else {$currency_string = 'ubu_balance';}
if (($type=='payment') || ($type =='credit')) {
$operator = '+';
}
else {$operator = '-';}
$sql = "UPDATE customer SET $currency_string = $currency_string $operator $amount WHERE id='$customer_id'";
$rs = $conn->Execute($sql);
return $last_insert_id;
}
function get_distributor($conn,$region_id,$customer_id='',$username='') {
if ($customer_id != '') {
$sql = "SELECT region_id FROM customer WHERE id = '$customer_id'";
$rs = $conn->Execute($sql);
$region_id = $rs->fields['region_id'];
}
else {
if ($username != '') {
$sql = "SELECT email FROM distributor WHERE username='$username'";
$rs = $conn->Execute($sql);
return $rs->fields['email'];
}
}
$sql = "SELECT email FROM distributor WHERE region_id='$region_id'";
$rs = $conn->Execute($sql);
$distributor_email = $rs->fields['email'];
return $distributor_email;
}
function get_customer($conn,$customer_id) {
$sql = "SELECT first_name,surname,email,password_reminder,home_phone,work_phone,cellphone,fax,customer.address,work_address,delivery_instructions,customer.region_id,region,default_collection_point,collection_point,customer.active,reminder,how_discovered,rand_balance,wholesaler,customer.blocked FROM customer LEFT JOIN region ON customer.region_id=region.id LEFT JOIN collection_point ON collection_point.id=customer.default_collection_point WHERE customer.id = '$customer_id'";
$rs = $conn->Execute($sql);
$full_name = $rs->fields['first_name'].' '.$rs->fields['surname'];
$customer_array = array('full_name'=>$full_name,'first_name'=>$rs->fields['first_name'],'surname'=>$rs->fields['surname'],'email'=>$rs->fields['email'],'password_reminder'=>$rs->fields['password_reminder'],'home_phone'=>$rs->fields['home_phone'],'work_phone'=>$rs->fields['work_phone'],'cellphone'=>$rs->fields['cellphone'],'fax'=>$rs->fields['fax'],'address'=>$rs->fields['address'],'work_address'=>$rs->fields['work_address'],'delivery_instructions'=>$rs->fields['delivery_instructions'],'region_id'=>$rs->fields['region_id'],'region'=>$rs->fields['region'],'default_collection_point'=>$rs->fields['default_collection_point'],'default_collection_point_string'=>"Collect - ".$rs->fields['collection_point'],'reminder'=>$rs->fields['reminder'],'active'=>$rs->fields['active'],'how'=>$rs->fields['how_discovered'],'rand_balance'=>$rs->fields['rand_balance'],'wholesaler'=>$rs->fields['wholesaler'],'blocked'=>$rs->fields['blocked']);
if ($rs->fields['default_collection_point'] == '999') {
$customer_array['default_collection_point_string'] = 'Deliver - Work address';
}
else if ($rs->fields['default_collection_point'] == '0') {
$customer_array['default_collection_point_string'] = 'Deliver - Home address';
}
return $customer_array;
}
function get_next_delivery_date($conn) {
$sql = "SELECT next_delivery_date FROM settings";
$rs = $conn->Execute($sql);
return $rs->fields['next_delivery_date'];
}
function get_qty_ordered($conn,$order_id,$product_id) {
$sql = "SELECT quantity FROM order_product WHERE order_id='$order_id' AND product_id='$product_id'";
$rs = $conn->Execute($sql);
$original_quantity = $rs->fields['quantity'];
if ($original_quantity == '') {
$original_quantity = 0;
}
return $original_quantity;
}
function get_delivery($conn,$order_id) {
$sql = "SELECT delivery_id FROM delivery WHERE order_id='$order_id'";
$rs = $conn->Execute($sql);
$delivery_id = $rs->fields['delivery_id'];
return $delivery_id;
}
function get_products($conn,$customer_id,$date) {
$product_id_string = "";
$sql = "SELECT product_id FROM order_product LEFT JOIN orders ON orders.order_id=order_product.order_id WHERE customer_id='$customer_id' AND delivery_date='$date'";
$rs = $conn->Execute($sql);
$i = 0;
while (!$rs->EOF) {
if ($i>0) {
$product_id_string .= ',';
}
$product_id_string .= $rs->fields['product_id'];
$i++;
$rs->MoveNext();
} //end while
return $product_id_string;
}
function get_product($conn,$product_id) {
$sql = "SELECT product_category_new_id,category,product.id,description, costprice, stock, image, rands, brand, supplier, cert_status FROM product LEFT JOIN product_category_new ON product_category_new_id=product_category_new.id LEFT JOIN supplier ON supplier_id = supplier.id LEFT JOIN cert_status ON cert_status.id = product.cert_status_id LEFT JOIN brand ON product.brand_id=brand.id WHERE product.id='$product_id'";
$rs = $conn->Execute($sql);
if ($rs->fields['description'] != '') {
//Create and return product array
}
}
function region_blocked($conn,$region_id) {
$sql = "SELECT blocked FROM region WHERE id = '$region_id'";
$rs = $conn->Execute($sql);
$blocked = $rs->fields['blocked'];
return $blocked;
}
function admin_array($conn,$username) {
$sql = "SELECT first_name,surname,region_id,view_all_regions,accept_electronic_payments FROM distributor WHERE username='$username'";
$rs = $conn->Execute($sql);
$found = 0;
while (!$rs->EOF) {
$found = 1;
$first_name = $rs->fields['first_name'];
$surname = $rs->fields['surname'];
$region_id = $rs->fields['region_id'];
$accept_electronic_payments = $rs->fields['accept_electronic_payments'];
$view_all_regions = $rs->fields['view_all_regions'];
$rs->MoveNext();
} //end while
if ($found == 0) {
echo 'You need to change your password, or you have not been set up as an administrator. Alternatively, the server is under extreme load. Please contact Ian (084-917-0318) to restore access.';
exit;
}
$admin_array = array('first_name' => "$first_name", 'surname' => $surname, 'region_id' => $region_id, 'accept_electronic_payments' => $accept_electronic_payments, 'view_all_regions' => $view_all_regions);
return $admin_array;
}
function b4nl($string) {
return preg_replace("!(\r|\n).+$!sU", '', $string);
}
function is_valid_email($email) {
return preg_match('#^[a-z0-9.!\#$%&\'*+-/=?^_`{|}~]+@([0-9.]+|([^\s]+\.+[a-z]{2,6}))$#si', $email);
}
function region_minimum($conn,$region_id) {
$sql = "SELECT minimum_delivery_fee FROM region WHERE id = '$region_id'";
$rs = $conn->Execute($sql);
$region_minimum = $rs->fields['minimum_delivery_fee'];
if ($region_minimum == '') {$region_minimum=0;}
return $region_minimum;
}
//returns true if fails credit check
function credit_check($conn,$customer_id,$next_delivery_date) {
$sql = "SELECT rand_balance,credit_control FROM customer WHERE id = '$customer_id'";
$rs = $conn->Execute($sql);
$rand_balance = $rs->fields['rand_balance'];
$credit_control = $rs->fields['credit_control'];
if (($rand_balance >= 0) || ($credit_control == '0')) {
return false;
}
else {
$sql = "SELECT DATE_ADD('$next_delivery_date',INTERVAL -16 DAY) AS two_weeks_ago FROM customer";
$rs = $conn->Execute($sql);
$two_weeks_ago = $rs->fields['two_weeks_ago'];
$sql = "SELECT SUM(totalrands_cents) AS recent_orders FROM orders WHERE delivery_date >= '$two_weeks_ago' AND delivery_date != '$next_delivery_date' AND customer_id='$customer_id'";
$rs = $conn->Execute($sql);
$recent_orders = $rs->fields['recent_orders'];
if ($recent_orders == '') {
$recent_orders = 0;
}
if (abs($rand_balance) > ($recent_orders+1000)) {
return true;
}
else {
return false;
}
}
}
function get_delivery_price($conn,$region_id,$price) {
$sql = "SELECT minimum_delivery_fee FROM region WHERE id='$region_id'";
$rs = $conn->Execute($sql);
$delivery_fee = $rs->fields['minimum_delivery_fee'];
return $delivery_fee;
}
function update_wholesale_price($conn,$prod_id='') {
if ($prod_id != '') {
$sql_condition = " AND product.id='$prod_id'";
}
else {
$sql_condition = '';
}
$sql = "SELECT id,price,costprice FROM product WHERE auto_wholesale='1'$sql_condition";
$rs = $conn->Execute($sql);
$settings_array = get_settings($conn);
while (!$rs->EOF) {
$id = $rs->fields['id'];
$price = $rs->fields['price'];
$costprice = $rs->fields['costprice'];
if ($price <= '1000') {
$wholesale_price = round($costprice + ($price-$costprice)/100*$settings_array['wholesale_ratio'],-1);
}
else {
$wholesale_price = round($costprice + ($price-$costprice)/100*$settings_array['wholesale_ratio'],-2);
}
$sql = "UPDATE product SET wholesale_price = '$wholesale_price' WHERE id='$id'";
$rs2 = $conn->Execute($sql);
$rs->MoveNext();
}
}
function write_file($fn,$str) {
$fh = fopen($fn, 'w') or die("There was a problem opening the file");
fwrite($fh, $str);
fclose($fh);
}
function read_file($fn) {
$table = '';
if (!($filearray = file ($fn))) {
print "There's a problem. Can't open file $fn";
}
else {
while (list ($line_number, $line_contents) = each ($filearray)) {
$table .= $line_contents;
}
}
return $table;
}
function get_categories($conn) {
$sql = "SELECT product_category.id,category FROM product_category LEFT JOIN product ON product.category_id = product_category.id WHERE product.visible='1' AND product.stock >0 GROUP BY category ORDER BY priority";
$rs = $conn->Execute($sql);
while (!$rs->EOF) {
$category = $rs->fields['category'];
$id = $rs->fields['id'];
$category_array[$id] = $category;
$rs->MoveNext();
}
return $category_array;
}
function contains_bad_string($str_to_test) {
$bad_strings = array(
"content-type:"
,"mime-version:"
,"multipart/mixed"
,"Content-Transfer-Encoding:"
,"bcc:"
,"cc:"
,"to:"
);
foreach($bad_strings as $bad_string) {
if (stristr($bad_string, strtolower($str_to_test))) {
//if(eregi($bad_string, strtolower($str_to_test))) {
return true;
}
}
return false;
}
function contains_newlines($str_to_test) {
if(preg_match("/(%0A|%0D|\\n+|\\r+)/i", $str_to_test) != 0) {
echo "newline found in $str_to_test. Suspected injection attempt - mail not being sent.";
exit;
}
}
//Check to see if customer has already ordered
function prior_standing_order($conn,$customer_id) {
$sql = "SELECT standing_order.id,customer_id,default_collection_point FROM standing_order LEFT JOIN customer ON customer.id=standing_order.customer_id WHERE customer_id='$customer_id'";
$rs = $conn->Execute($sql);
if ($rs->fields['customer_id'] == $customer_id) {
$order_id = $rs->fields['id'];
$default_collection_point = $rs->fields['default_collection_point'];
$old_order = array('prior_order' => 1, 'order_id' => $order_id, 'default_collection_point' => "$default_collection_point");
}
else {
$old_order = array('prior_order' => 0, 'order_id' => '');
}
return $old_order;
}
function get_qty_standing_ordered($conn,$order_id,$product_id) {
$sql = "SELECT quantity FROM standing_order_product WHERE standing_order_id='$order_id' AND product_id='$product_id'";
$rs = $conn->Execute($sql);
$original_quantity = $rs->fields['quantity'];
if ($original_quantity == '') {
$original_quantity = 0;
}
return $original_quantity;
}
function get_standing_products($conn,$customer_id) {
$product_id_string = '';
$sql = "SELECT product_id FROM standing_order_product LEFT JOIN standing_order ON standing_order.id=standing_order_product.standing_order_id WHERE customer_id='$customer_id'" ;
$rs = $conn->Execute($sql);
$i = 0;
while (!$rs->EOF) {
if ($i>0) {
$product_id_string .= ',';
}
$product_id_string .= $rs->fields['product_id'];
$i++;
$rs->MoveNext();
} //end while
return $product_id_string;
}
//Check to see if customer has already ordered via courier
//One courier order is allowed every hour
function prior_courier_order($conn,$customer_id) {
$hour_ago = date("Y-m-d H:i",strtotime("1 hour ago"));
$sql = "SELECT courier_order.id,customer_id,default_collection_point FROM courier_order LEFT JOIN customer ON customer.id=courier_order.customer_id WHERE customer_id='$customer_id' AND placement_time >='$hour_ago'";
$rs = $conn->Execute($sql);
if ($rs->fields['customer_id'] == $customer_id) {
$order_id = $rs->fields['id'];
$default_collection_point = $rs->fields['default_collection_point'];
$old_order = array('prior_order' => 1, 'order_id' => $order_id);
}
else {
$old_order = array('prior_order' => 0, 'order_id' => '');
}
return $old_order;
}
//Checks to see if the customer is registered in a region that is only for couriers, ie, no cold and fresh
function check_courier_only($conn,$customer_id) {
$sql = "SELECT courier_only FROM region LEFT JOIN customer ON customer.region_id = region.id WHERE customer.id='$customer_id'";
$rs = $conn->Execute($sql);
return $rs->fields['courier_only'];
}
function get_accuracy($conn,$product_id="",$weeks="") {
if ($product_id != '') {
$order_product_criteria = "order_product.product_id='$product_id'";
$delivery_product_criteria = "delivery_product.product_id='$product_id'";
}
else {
$order_product_criteria = '1';
$delivery_product_criteria = '1';
}
if ($weeks == '') {
$sql = "SELECT SUM(order_product.quantity) AS ordered FROM order_product WHERE $order_product_criteria";
$rs = $conn->Execute($sql);
$ordered = $rs->fields['ordered'];
$sql = "SELECT SUM(delivery_product.quantity) AS delivered FROM delivery_product WHERE $delivery_product_criteria";
$rs = $conn->Execute($sql);
$delivered = $rs->fields['delivered'];
}
else {
$next_delivery_date = get_next_delivery_date($conn);
$timestamp = strtotime($next_delivery_date);
$weeks_date = date("Y-m-d",strtotime("$weeks week ago",$timestamp));
$sql = "SELECT SUM(order_product.quantity) AS ordered FROM order_product LEFT JOIN orders ON orders.order_id=order_product.order_id WHERE $order_product_criteria AND orders.delivery_date >= '$weeks_date' AND orders.delivery_date < '$next_delivery_date'";
$rs = $conn->Execute($sql);
$ordered = $rs->fields['ordered'];
$sql = "SELECT SUM(delivery_product.quantity) AS delivered FROM delivery_product LEFT JOIN delivery ON delivery.delivery_id=delivery_product.delivery_id WHERE $delivery_product_criteria AND delivery.delivery_date >= '$weeks_date' AND delivery.delivery_date < '$next_delivery_date'";
$rs = $conn->Execute($sql);
$delivered = $rs->fields['delivered'];
}
if ($ordered != 0) {
$accuracy = number_format($delivered/$ordered*100,0);
$accuracy = "$accuracy%";
}
else {
$accuracy = "NA";
}
return $accuracy;
}
function get_packing_category_accuracy($conn,$packing_category_id,$weeks='0') {
if ($weeks == '0') {
$sql = "SELECT SUM(order_product.quantity) AS ordered FROM order_product LEFT JOIN product ON product.id=order_product.product_id WHERE packing_category_id='$packing_category_id'";
$rs = $conn->Execute($sql);
$ordered = $rs->fields['ordered'];
$sql = "SELECT SUM(delivery_product.quantity) AS delivered FROM delivery_product LEFT JOIN product ON product.id=delivery_product.product_id WHERE packing_category_id='$packing_category_id'";
$rs = $conn->Execute($sql);
$delivered = $rs->fields['delivered'];
}
else {
$next_delivery_date = get_next_delivery_date($conn);
$timestamp = strtotime($next_delivery_date);
$weeks_date = date("Y-m-d",strtotime("$weeks week ago",$timestamp));
$sql = "SELECT SUM(order_product.quantity) AS ordered FROM order_product LEFT JOIN product ON product.id=order_product.product_id LEFT JOIN orders ON orders.order_id=order_product.order_id WHERE packing_category_id='$packing_category_id' AND orders.delivery_date >= '$weeks_date' AND orders.delivery_date < '$next_delivery_date'";
$rs = $conn->Execute($sql);
$ordered = $rs->fields['ordered'];
$sql = "SELECT SUM(delivery_product.quantity) AS delivered FROM delivery_product LEFT JOIN product ON product.id=delivery_product.product_id LEFT JOIN delivery ON delivery.delivery_id=delivery_product.delivery_id WHERE packing_category_id='$packing_category_id' AND delivery.delivery_date >= '$weeks_date' AND delivery.delivery_date < '$next_delivery_date'";
$rs = $conn->Execute($sql);
$delivered = $rs->fields['delivered'];
}
if ($ordered != 0) {
$accuracy = number_format($delivered/$ordered*100,0);
$accuracy = "$accuracy%";
}
else {
$accuracy = "NA";
}
return $accuracy;
}
function get_supplier_accuracy($conn, $supplier_id, $weeks='0') {
if ($weeks == '0') {
$sql = "SELECT SUM( order_product.quantity ) AS ordered FROM supplier LEFT JOIN product ON supplier.id = product.supplier_id LEFT JOIN order_product ON order_product.product_id = product.id WHERE supplier.id='$supplier_id'";
$rs = $conn->Execute($sql);
$ordered = $rs->fields['ordered'];
$sql = "SELECT SUM(delivery_product.quantity) AS delivered FROM supplier LEFT JOIN product ON supplier.id = product.supplier_id LEFT JOIN delivery_product ON delivery_product.product_id=product.id WHERE supplier.id='$supplier_id'";
$rs = $conn->Execute($sql);
$delivered = $rs->fields['delivered'];
}
else {
$next_delivery_date = get_next_delivery_date($conn);
$timestamp = strtotime($next_delivery_date);
$weeks_date = date("Y-m-d",strtotime("$weeks week ago",$timestamp));
$sql = "SELECT SUM(order_product.quantity ) AS ordered FROM supplier LEFT JOIN product ON supplier.id = product.supplier_id LEFT JOIN order_product ON order_product.product_id = product.id LEFT JOIN orders ON orders.order_id = order_product.order_id WHERE supplier.id='$supplier_id' AND orders.delivery_date >= '$weeks_date' AND orders.delivery_date < '$next_delivery_date'";
$rs = $conn->Execute($sql);
$ordered = $rs->fields['ordered'];
$sql = "SELECT SUM(delivery_product.quantity ) AS delivered FROM supplier LEFT JOIN product ON supplier.id = product.supplier_id LEFT JOIN delivery_product ON delivery_product.product_id = product.id LEFT JOIN delivery ON delivery.delivery_id = delivery_product.delivery_id WHERE supplier.id='$supplier_id' AND delivery.delivery_date >= '$weeks_date' AND delivery.delivery_date < '$next_delivery_date'";
$rs = $conn->Execute($sql);
$delivered = $rs->fields['delivered'];
}
if ($ordered != 0) {
$accuracy = number_format($delivered/$ordered*100,0);
$accuracy = "$accuracy%";
}
else {
$accuracy = "NA";
}
return $accuracy;
}
function get_settings($conn) {
$sql = "SELECT next_delivery_date, active, text1,text2, vegweekimage, wholesale_ratio, active_status_change,next_delivery_date_following,last_invoiced FROM settings";
$rs = $conn->Execute($sql);
$settings_array = array('next_delivery_date'=>$rs->fields['next_delivery_date'],'active'=>$rs->fields['active'],'text1'=>$rs->fields['text1'],'text2'=>$rs->fields['text2'],'vegweekimage'=>$rs->fields['vegweekimage'],'wholesale_ratio'=>$rs->fields['wholesale_ratio'],'active_status_change'=>$rs->fields['active_status_change'],'next_delivery_date_following'=>$rs->fields['next_delivery_date_following'],'last_invoiced'=>$rs->fields['last_invoiced']);
return $settings_array;
}
function get_delivery_day_settings($conn,$customer_region_id) {
$sql = "SELECT delivery_day.active, delivery_day.active_status_change,day_delivery_date FROM delivery_day LEFT JOIN region ON delivery_day.day = region.delivery_day WHERE region.id = '$customer_region_id'";
$rs = $conn->Execute($sql);
$settings_array = array('active'=>$rs->fields['active'],'active_status_change'=>$rs->fields['active_status_change'],'day_delivery_date'=>$rs->fields['day_delivery_date']);
return $settings_array;
}
function cancel_order($conn,$order_id,$admin_email,$admin='0') {
$title = "";
//Get customer details for mailing
$sql = "SELECT order_id,first_name,surname,customer.email,customer.last_invoiced,orders.delivery_date FROM customer LEFT JOIN orders ON customer.id=orders.customer_id WHERE order_id='$order_id'";
$rs = $conn->Execute($sql);
$customer_email = $rs->fields['email'];
$first_name = stripslashes($rs->fields['first_name']);
$surname = stripslashes($rs->fields['surname']);
$db_order_id = $rs->fields['order_id'];
$delivery_date = $rs->fields['delivery_date'];
$last_invoiced = $rs->fields['last_invoiced'];
if ($db_order_id != $order_id) {
echo "We have encountered an error, and this order does not seem to exist.";
return false;
}
if ($admin == '1') {
// Can only cancel if the order has not yet been invoiced
$admin_email_body = "The order for $first_name $surname has been cancelled on their behalf by ".$_SERVER['PHP_AUTH_USER'].".";
if ($last_invoiced >= $delivery_date) {
echo "We have encountered an error, as this order appears to have already been invoiced, and cannot be cancelled. The customer was last invoiced on $last_invoiced while this order was due for delivery/collection on $delivery_date";
return false;
}
}
else {
$admin_email_body = "$first_name $surname has cancelled their order";
}
$sql = "SELECT delivery_id FROM delivery WHERE order_id='$order_id'";
$rs = $conn->Execute($sql);
$delivery_id = $rs->fields['delivery_id'];
$sql = "DELETE FROM orders WHERE order_id = '$order_id'";
$rs = $conn->Execute($sql);
// Restore the deleted items to stock
$sql = "SELECT product_id,quantity FROM order_product WHERE order_id='$order_id'";
$rs = $conn->Execute($sql);
while (!$rs->EOF) {
$restored_product_id = $rs->fields['product_id'];
$restored_quantity = $rs->fields['quantity'];
$sql = "UPDATE product SET stock = stock + $restored_quantity WHERE id='$restored_product_id'";
$rs2 = $conn->Execute($sql);
$rs->MoveNext();
}
$sql = "DELETE FROM order_product WHERE order_id = '$order_id'";
$rs = $conn->Execute($sql);
$sql = "DELETE FROM delivery WHERE delivery_id = '$delivery_id'";
$rs = $conn->Execute($sql);
$sql = "DELETE FROM delivery_product WHERE delivery_id = '$delivery_id'";
$rs = $conn->Execute($sql);
$headers = "From: $title <$admin_email>\r\n";
$headers .= "Reply-To: $admin_email\r\n";
$customer_email_body = "Hi $first_name\r\n\r\nYour order has been cancelled. We hope we can be of service again in the future.";
mail($admin_email, "Ethical Co-op order cancelled", $admin_email_body,$headers);
mail($customer_email, "Ethical Co-op order cancelled", $customer_email_body,$headers);
return true;
}
function cancel_courier_order($conn,$order_id,$admin_email,$admin='0') {
//Get customer details for mailing
$sql = "SELECT courier_order.id AS order_id,first_name,surname,customer.email FROM customer LEFT JOIN courier_order ON customer.id=courier_order.customer_id WHERE courier_order.id='$order_id'";
$rs = $conn->Execute($sql);
$customer_email = $rs->fields['email'];
$first_name = stripslashes($rs->fields['first_name']);
$surname = stripslashes($rs->fields['surname']);
$db_order_id = $rs->fields['order_id'];
if ($db_order_id != $order_id) {
echo "We have encountered an error, and this order does not seem to exist.";
return false;
}
$sql = "DELETE FROM courier_order WHERE id = '$order_id'";
$rs = $conn->Execute($sql);
$sql = "DELETE FROM courier_order_product WHERE courier_order_id = '$order_id'";
$rs = $conn->Execute($sql);
$headers = "From: $title <$admin_email>\r\n";
$headers .= "Reply-To: $admin_email\r\n";
$admin_email_body = "$first_name $surname has cancelled their courier order";
$customer_email_body = "Hi $first_name\r\n\r\nYour courier order has been cancelled. We hope we can be of service again in the future.";
mail($admin_email, "Ethical Co-op courier order cancelled", $admin_email_body,$headers);
mail($customer_email, "Ethical Co-op courier order cancelled", $customer_email_body,$headers);
return true;
}
function cancel_standing_order($conn,$order_id,$admin_email,$admin='0') {
//Get customer details for mailing
$sql = "SELECT standing_order.id AS order_id, first_name,surname,customer.email FROM customer LEFT JOIN standing_order ON customer.id=standing_order.customer_id WHERE standing_order.id='$order_id'";
$rs = $conn->Execute($sql);
$customer_email = $rs->fields['email'];
$first_name = stripslashes($rs->fields['first_name']);
$surname = stripslashes($rs->fields['surname']);
$db_order_id = $rs->fields['order_id'];
if ($db_order_id != $order_id) {
echo "We have encountered an error, and this order does not seem to exist.";
return false;
}
$sql = "DELETE FROM standing_order WHERE id = '$order_id'";
$rs = $conn->Execute($sql);
$sql = "DELETE FROM standing_order_product WHERE standing_order_id = '$order_id'";
$rs = $conn->Execute($sql);
$headers = "From: $title <$admin_email>\r\n";
$headers .= "Reply-To: $admin_email\r\n";
if ($admin == '1') {
$admin_email_body = "The standing order for $first_name $surname has been cancelled on their behalf by ".$_SERVER['PHP_AUTH_USER'].".";
}
else {
$admin_email_body = "$first_name $surname has cancelled their standing order.";
}
$customer_email_body = "Hi $first_name\r\n\r\nYour standing order has been cancelled. We hope we can be of service again in the future.";
mail($admin_email, "Ethical Co-op standing order cancelled", $admin_email_body,$headers);
mail($customer_email, "Ethical Co-op standing order cancelled", $customer_email_body,$headers);
return true;
}
function get_qty_products($conn,$stock,$category_id,$visible='1',$standing_orders='',$courier_orders='') {
// category_id can also be a string of the category
if ((!(is_numeric($category_id))) AND (is_string($category_id))) {
$sql = "SELECT id FROM product_category_new WHERE category='$category_id'";
$rs = $conn->Execute($sql);
$category_id = $rs->fields['id'];
}
if ($standing_orders!= '') {
$standing_orders_criteria = "standing_orders='1'";
}
else {
$standing_orders_criteria = "1";
}
if ($courier_orders!= '') {
$courier_orders_criteria = "product.packing_category_id='2' AND weight != ''";
}
else {
$courier_orders_criteria = "1";
}
$sql = "SELECT COUNT(*) AS tot FROM product WHERE (stock > '$stock') AND (visible='$visible') AND (product_category_new_id='$category_id') AND ($standing_orders_criteria) AND ($courier_orders_criteria)";
$rs = $conn->Execute($sql);
$tot = $rs->fields['tot'];
return $tot;
}
function delivery_date_dropdown($conn,$fieldname="",$today=0,$default="") {
if ($fieldname == '') {
$fieldname = 'delivery_date';
}
$sql = "SELECT DISTINCT delivery_date FROM orders ORDER BY delivery_date DESC";
$rs = $conn->Execute($sql);
$delivery_date_dropdown = "";
return $delivery_date_dropdown;
}
function replace_newline($string) {
return (string)str_replace(array("\r", "\r\n", "\n"), '', $string);
}
function in_mixed_box($conn,$date,$product_id) {
$sql = "SELECT id FROM mixed_box_product WHERE product_id='$product_id' AND delivery_date='$date'";
$rs = $conn->Execute($sql);
$result = false;
while (!$rs->EOF) {
$result = true;
$rs->MoveNext();
}
return $result;
}
function mixed_box_product_qty($conn,$date,$product_id,$delivery_day) {
if (($delivery_day != 'Wednesday') AND ($delivery_day != 'Thursday')) {
$delivery_day = 'All';
}
$total_quantity = 0;
// All mixed bozes containing this product
$sql = "SELECT mixed_box_id, quantity AS multiplier FROM mixed_box_product WHERE delivery_date='$date' AND product_id='$product_id'";
$rs = $conn->Execute($sql);
while (!$rs->EOF) {
$mixed_box_id = $rs->fields['mixed_box_id'];
$multiplier = $rs->fields['multiplier'];
$sql = "SELECT product_id FROM mixed_box WHERE id='$mixed_box_id'";
$rs2 = $conn->Execute($sql);
$mixed_box_product_id = $rs2->fields['product_id'];
if ($delivery_day == 'All') {
$delivery_day_criteria = '1';
}
else if (($delivery_day == 'Wednesday') OR ($delivery_day == 'Thursday')) {
$delivery_day_criteria = "delivery_day='$delivery_day'";
}
else {
echo "Delivery Day error";
exit;
}
// Qty sold of each mixed box containing this product
$sql = "SELECT SUM(quantity) as quantity, product.description, product.id AS product_id FROM product LEFT JOIN order_product ON product.id=order_product.product_id LEFT JOIN orders ON order_product.order_id=orders.order_id LEFT JOIN customer ON customer.id = orders.customer_id LEFT JOIN region ON region.id = orders.region_id WHERE delivery_date ='$date' AND product.id='$mixed_box_product_id' AND $delivery_day_criteria GROUP BY product.id HAVING SUM(quantity) <> 'NULL'";
//echo "$sql ";
$rs2 = $conn->Execute($sql);
$quantity = $rs2->fields['quantity'] * $multiplier;
$total_quantity += $quantity;
$rs->MoveNext();
}
return $total_quantity;
}
// Returns array of all products and qty's in mixed boxes from a particular supplier.
function mixed_box_product_array($conn,$date,$supplier_id,$delivery_day='All') {
$mixed_box_product_array='';
//first, find mixed boxes
$sql = "SELECT mixed_box.id, mixed_box.product_id, description FROM mixed_box LEFT JOIN product ON product.id = mixed_box.product_id";
$rs = $conn->Execute($sql);
while (!$rs->EOF) {
$mixed_box_id = $rs->fields['id'];
$mixed_box_product_id = $rs->fields['product_id'];
$box_description = $rs->fields['description'];
//second, find all the products from this supplier in each box
$sql = "SELECT product_id, description FROM mixed_box_product LEFT JOIN product ON product.id = mixed_box_product.product_id LEFT JOIN supplier ON supplier.id = product.supplier_id WHERE mixed_box_id = '$mixed_box_id' AND supplier.id='$supplier_id' AND delivery_date='$date'";
$rs2 = $conn->Execute($sql);
while (!$rs2->EOF) {
$product_id = $rs2->fields['product_id'];
$description = $rs2->fields['description'];
$quantity = mixed_box_product_qty($conn,$date,$product_id,$delivery_day);
$mixed_box_product_array[] = array('product_id'=>$product_id,'description'=>$description,'quantity'=>$quantity,'processed'=>0);
$rs2->MoveNext();
}
$rs->MoveNext();
}
return $mixed_box_product_array;
}
// Gets suppliers that have an htaccess set to allow admins to log in with supplier view
function supplier_login_dropdown($conn) {
$sql = "SELECT supplier, htaccess_username FROM supplier WHERE active ='1' AND htaccess_username!='' ORDER BY supplier";
$rs = $conn->Execute($sql);
$supplier_login_dropdown = "";
return $supplier_login_dropdown;
}
function delivery_day_dropdown($conn) {
$sql = "SELECT day FROM delivery_day ORDER BY FIELD(day, 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday' )";
$rs = $conn->Execute($sql);
$delivery_day_dropdown = "";
while (!$rs->EOF) {
$delivery_day = $rs->fields['day'];
$delivery_day_dropdown .= "";
$rs->MoveNext();
}
return $delivery_day_dropdown;
}
function region_day_criteria($conn,$day) {
$sql = "SELECT region, id FROM region WHERE delivery_day='$day'";
$rs = $conn->Execute($sql);
$regioncriteria = '(1=0 ';
while (!$rs->EOF) {
$regioncriteria .= "OR region.id='".$rs->fields['id']."' ";
$rs->MoveNext(); // Moves to the next row
}
$regioncriteria .= ")";
return $regioncriteria;
}
?>