Hi there,
I recently took over a site from an agency as the client required regular updates and management. The website is www.mytowerlabels.com and creates custom labels for school kids with their name, school badge, etc.
The website originally had only one product, and now has four, which has created quite the complication in terms of the previous methods that were used.
There is a PHP Script that was created to perform a Cronjob that exports all orders with specific details to an excel sheet for the production team to use when printing the product with the customer information.
Now that additional products have been added and the site has been updated, this cronjob no longer works.
I’ve now resulted to using a plugin for Wordpress called WP All Export, which is great and seems to almost solve the problem, however, I cannot manage to add “Logo Path” as per below, which is used during production to specify which logo should be added during printing.
Any asisstance would be greatly appreciated as I am currently doing these exports manually and it is quite time consuming to do this every day before 8am for the client.
The script can be found below:
<?php
include "../wp-blog-header.php";
$PRODUCTS = k8_mtl_xl_fetch_products();
$OUTPUT_ORDERS = array();
$OUTPUT_FILES = array();
$THESCHOOLS = array();
while(have_rows('the_schools', 'option')): the_row();
$THESCHOOLS[trim(get_sub_field('school_name'))] = get_sub_field('relative_path');
endwhile;
if(count($PRODUCTS) > 0):
foreach($PRODUCTS as $P):
$ORDERS = k8_mtl_xl_fetch_orders( $P );
if(count($ORDERS) > 0):
$objPHPExcel = new PHPExcel();
$objPHPExcel->removeSheetByIndex(0);
$index = 0;
$worksheet = new PHPExcel_Worksheet($objPHPExcel, $P->post_title);
$objPHPExcel->addSheet($worksheet, $index);
$data = array();
$row = array();
$row[] = 'Name';
$row[] = 'Surname';
$row[] = 'Name & Surname';
$row[] = 'School Name';
$row[] = "Waybill Number";
$row[] = 'Tracking Number';
$row[] = 'Voucher Code';
$row[] = 'Logo Path';
$data_keys[] = $row;
$data_schools = array();
$data_not = array();
foreach($ORDERS as $O):
$_THIS = new WC_Order($O);
$items = $_THIS->get_items();
$coupons_list = array();
foreach( $_THIS->get_used_coupons() as $coupon) {
$coupons_list[] = $coupon;
}
$coupons = implode(", ", $coupons_list);
foreach($items as $K=>$I):
if($I['name'] == $P->post_title):
$info = array();
$name = $_THIS->get_item_meta($K, 'Name', true);
$names = explode(" ", $name, 2);
$school = trim($_THIS->get_item_meta($K, 'School', true));
$path = $THESCHOOLS[$school];
$info[] = $names[0];
$info[] = $names[1];
$info[] = $_THIS->get_item_meta($K, 'Name', true);
$info[] = $school;
$info[] = $_THIS->get_item_meta($K, 'pp_waybill_no', true);
$info[] = $_THIS->get_item_meta($K, 'pp_tracking_no', true);
$info[] = $coupons;
$info[] = $path;
if($info[7] != ""){
$data_schools[] = $info;
}else{
$data_not[] = $info;
}
endif;
endforeach;
$OUTPUT_ORDERS[] = $_THIS->id;
endforeach;
function compareByName($a, $b) {
return strcmp($a[7], $b[7]);
}
usort($data_schools, 'compareByName');
$data_data = array_merge($data_schools, $data_not);
$data = array_merge($data_keys, $data_data);
echo '<pre>';
print_r($data);
echo '</pre>';
$objPHPExcel->setActiveSheetIndex($index)->fromArray($data, null, 'A1');
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setAutoSize(true);
$date = getdate();
$title = sanitize_title($P->post_title).'_'.$date['0'];
$objPHPExcel->setActiveSheetIndex(0);
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->save($title.".xlsx");
$OUTPUT_FILES[] = $title.".xlsx";
endif;
endforeach;
endif;
if(count($OUTPUT_FILES) > 0):
k8_mtl_xl_send_emails($OUTPUT_FILES);
endif;
if(count($OUTPUT_ORDERS) > 0):
k8_mtl_xl_update_orders($OUTPUT_ORDERS);
endif;
function k8_mtl_xl_fetch_products(){
$args = array(
'post_type' => 'product',
'posts_per_page' => '-1'
);
$products = get_posts($args);
return $products;
}
function k8_mtl_xl_fetch_orders($P){
global $wpdb, $table_prefix;
$SQL = "SELECT DISTINCT order_id
FROM {$table_prefix}woocommerce_order_itemmeta woim
LEFT JOIN {$table_prefix}woocommerce_order_items oi ON woim.order_item_id = oi.order_item_id
WHERE meta_key = '_product_id' AND meta_value = '{$P->ID}'";
$RES = $wpdb->get_results($SQL, ARRAY_A);
$IDS = wp_list_pluck($RES, 'order_id');
$args = array(
'post_type' => 'shop_order',
'posts_per_page' => '-1',
'post_status' => 'wc-processing',
'post__in' => $IDS,
'meta_query' => array(
"RELATION" => "AND",
array(
'key' => 'sent_to_excel',
'compare' => 'NOT EXISTS',
),
array(
'key' => 'parcel_perfect_instance',
'compare' => 'EXISTS',
),
array(
'key' => 'parcel_perfect_waybill',
'compare' => 'EXISTS',
),
)
);
$ORDERS = get_posts($args);
return $ORDERS;
}
function k8_mtl_xl_send_emails($FILES){
$attachments = array();
foreach($FILES as $F){
$attachments = ABSPATH.'/wp-cron/'.$F;
}
$from_name = 'MyTOWERLabels Website';
$from_email = '[email protected]';
$to_email = 'EMAIL ACCOUNTS';
$subject = 'MyTOWERLabels Excel Output';
$content = "<p>Please find latest order excel's attached.</p>";
$headers = array('From: '.$from_name.' <'.$from_email.'>', 'Content-Type: text/html; charset=UTF-8');
wp_mail($to_email, $subject, $content, $headers, $attachments);
}
function k8_mtl_xl_update_orders($ORDERS){
foreach($ORDERS as $ID){
update_post_meta($ID, 'sent_to_excel', date('Y-m-d'));
}
}
function k8_mtl_xl_get_school_relative_path($SCHOOL){
while(have_rows('the_schools', 'option')): the_row();
$name = get_sub_field('school_name');
if($SCHOOL === $name){
return get_sub_field('relative_path');
break;
}
endwhile;
}
function k8_mtl_xl_get_school_relative_path_exists($SCHOOL){
while(have_rows('the_schools', 'option')): the_row();
$name = get_sub_field('school_name');
if($SCHOOL === $name){
return 'found';
break;
}
endwhile;
return 'nope';
}
?>Preformatted text