<style>
.bold{
    font-weight: bolder;   
}
.green{
    background-color: lightgreen;
}
.yellow{
    background-color: lightyellow;
}
</style>
<?php 
//error_reporting(E_ALL);
//ini_set('display_errors', 1);
require_once('wp-load.php'); 
session_start();
date_default_timezone_set("Asia/Hong_Kong"); 
global $wpdb, $sitepress;
$k11_date = array('2022-10-24', '2022-10-25', '2022-10-26', '2022-10-27', '2022-10-28', '2022-10-29', '2022-10-30');
$post_status_string = implode("','", array('wc-pending', 'wc-processing', 'wc-completed') );
$k11_date_string = implode("','", $k11_date);
$weekarray = array("日","一","二","三","四","五","六");
$method_array = array("團購","送貨","自取","交收","K11交收","K11 Walk-in");

$result = $wpdb->get_results("SELECT * FROM wp_posts order_master left join wp_postmeta order_detail ON order_master.ID = order_detail.post_id WHERE order_master.post_type = 'shop_order' AND order_master.post_status IN ('{$post_status_string}') AND order_detail.meta_key = 'delivery_date' AND order_detail.meta_value IN ('{$k11_date_string}') ");	

$array_product_list = array();

$arra_result = array();


foreach ($result as $order_obj ){
    $order = wc_get_order( $order_obj->ID );
    $delivery_date = $order->get_meta('delivery_date', true);
    $ship_method = $order->get_shipping_method();

    if($order->get_billing_first_name()=="K11"){
        $order_type = "K11 Walk-in";
    } elseif(is_wholesaler_order($order_obj->ID)){
        $order_type = "團購";
    } elseif(strpos($ship_method, '送貨') !== false || strpos($ship_method, 'Delivery') !== false) {
        $order_type = "送貨";
    } elseif($ship_method=="自取" || $ship_method=="Pick-up"){
        $order_type = "自取";				
    } elseif(strpos($ship_method, '交收') !== false || strpos($ship_method, 'Collect') !== false) {			
        if($ship_method=="Collect 1" || $ship_method=="交收 1" || $ship_method=="Collect 13" || $ship_method=="交收 13"){
            $order_type = "交收";
        }elseif($ship_method=="Collect 2" || $ship_method=="交收 2" || $ship_method=="Collect 14" || $ship_method=="交收 14"){
            $order_type = "交收";
        }elseif($ship_method=="Collect 3" || $ship_method=="交收 3" || $ship_method=="Collect 5" || $ship_method=="交收 5"){
            $order_type = "交收";
        }elseif($ship_method=="Collect 4" || $ship_method=="交收 4" || $ship_method=="Collect 6" || $ship_method=="交收 6"){
            $order_type = "交收";
        }elseif($ship_method=="Collect 11" || $ship_method=="交收 11"){
            $order_type = "交收";
        }elseif($ship_method=="Collect 12" || $ship_method=="交收 12"){
            $order_type = "交收";
        }elseif($ship_method=="Collect 15" || $ship_method=="交收 15"){
            $order_type = "K11交收";
        }			
    } else {
        $order_type = "送貨";
    }

    foreach ( $order->get_items() as $item ){
        $prod = $item->get_product();               

        $trid = $sitepress->get_element_trid($prod->get_id(), 'post_product');				
        $translations = $sitepress->get_element_translations($trid, 'product');
        foreach( $translations as $lang=>$translation){
            if($translation->language_code == "zh-hant")
                $zh_post_id = $translation->element_id;
        }			

        $zh_product_name = get_the_title($zh_post_id);

        $qty = $item->get_quantity();
        $price = $item->get_total();

        $array_result[$zh_product_name][$delivery_date][$order_type]['qty'] += $qty;
        $array_result[$zh_product_name][$delivery_date][$order_type]['amount'] += $price;

        $array_result[$zh_product_name][$delivery_date]['total']['qty'] += $qty;
        $array_result[$zh_product_name][$delivery_date]['total']['amount'] += $price;

        $array_result[$zh_product_name]['total'][$order_type]['qty'] += $qty;
        $array_result[$zh_product_name]['total'][$order_type]['amount'] += $price;

        $array_result[$zh_product_name]['total']['total']['qty'] += $qty;
        $array_result[$zh_product_name]['total']['total']['amount'] += $price;   
        
        $array_result['total'][$delivery_date][$order_type]['qty'] += $qty;
        $array_result['total'][$delivery_date][$order_type]['amount'] += $price;     
        
        $array_result['total'][$delivery_date]['total']['qty'] += $qty;
        $array_result['total'][$delivery_date]['total']['amount'] += $price;     
        
        $array_result['total']['total'][$order_type]['qty'] += $qty;
        $array_result['total']['total'][$order_type]['amount'] += $price;      
        
        $array_result['total']['total']['total']['qty'] += $qty;
        $array_result['total']['total']['total']['amount'] += $price;             


        array_push($array_product_list, $zh_product_name);
    }
    
}

$array_product_list = array_unique($array_product_list);
sort($array_product_list);

echo "<table style='border-collapse: collapse; border:1px solid black' cellpadding='10' cellspacing='10' border='1'>";
    // header
    echo "<thead>";
        echo "<tr>";
            echo "<th></th>";
            foreach($k11_date as $k11Date) {
                $display_date = date('j/n',strtotime($k11Date))." (".$weekarray[date("w", strtotime($k11Date))].")";
                echo "<th colspan='".((count($method_array)+1)*2)."'>".$display_date."</th>";
            }
            echo "<th colspan='".((count($method_array)+1)*2)."'>24/10 -30/10 加總</th>";
        echo "</tr>";

        echo "<tr>";
            echo "<th></th>";
            foreach($k11_date as $k11Date) {
                $display_date = date('j/n',strtotime($k11Date))." (".$weekarray[date("w", strtotime($k11Date))].")";
                foreach($method_array as $methodName) {
                    echo "<th colspan='2'>".$methodName."</th>";
                }
                echo "<th colspan='2'>".$display_date." 所有交貨方式加總</th>";
            }

            foreach($method_array as $methodName) {
                echo "<th colspan='2'>".$methodName."</th>";
            }
            echo "<th colspan='2'>所有交貨方式加總</th>";

        echo "</tr>";

        echo "<tr>";
            echo "<th>產品</th>";
            foreach($k11_date as $k11Date) {
                foreach($method_array as $methodName) {
                    echo "<th>銷售數量(份)</th><th>銷售金額</th>";
                }
                echo "<th>銷售數量(份)</th><th>銷售金額</th>";
            }

            foreach($method_array as $methodName) {
                echo "<th>銷售數量(份)</th><th>銷售金額</th>";
            }  
            
            echo "<th>銷售數量(份)</th><th>銷售金額</th>";
            
        echo "</tr>";
    echo "</thead>";


    //body
    echo "<tbody>";
        foreach($array_product_list as $productName){
            echo "<tr>";
                echo "<td nowrap>".$productName."</td>";
                foreach($k11_date as $k11Date) {
                    foreach($method_array as $methodName) {
                        echo "<td align='right'>".(isset($array_result[$productName][$k11Date][$methodName]['qty'])?$array_result[$productName][$k11Date][$methodName]['qty']:0)."</td>";
                        echo "<td align='right'>".(isset($array_result[$productName][$k11Date][$methodName]['qty'])?number_format($array_result[$productName][$k11Date][$methodName]['amount'],2,'.',','):number_format(0,2,'.',','))."</td>";
                    }
                    echo "<td align='right'>".(isset($array_result[$productName][$k11Date]['total']['qty'])?$array_result[$productName][$k11Date]['total']['qty']:0)."</td>";
                    echo "<td align='right'>".(isset($array_result[$productName][$k11Date]['total']['qty'])?number_format($array_result[$productName][$k11Date]['total']['amount'],2,'.',','):number_format(0,2,'.',','))."</td>";
                }

                foreach($method_array as $methodName) {
                    echo "<td align='right' class='bold yellow'>".(isset($array_result[$productName]['total'][$methodName]['qty'])?$array_result[$productName]['total'][$methodName]['qty']:0)."</td>";
                    echo "<td align='right' class='bold yellow'>".(isset($array_result[$productName]['total'][$methodName]['qty'])?number_format($array_result[$productName]['total'][$methodName]['amount'],2,'.',','):number_format(0,2,'.',','))."</td>";
                }
                echo "<td align='right' class='bold green'>".(isset($array_result[$productName]['total']['total']['qty'])?$array_result[$productName]['total']['total']['qty']:0)."</td>";
                echo "<td align='right' class='bold green'>".(isset($array_result[$productName]['total']['total']['qty'])?number_format($array_result[$productName]['total']['total']['amount'],2,'.',','):number_format(0,2,'.',','))."</td>";

            echo "</tr>";
        }

        echo "<tr class='bold green'>";
            echo "<td>加總</td>";
            foreach($k11_date as $k11Date) {
                foreach($method_array as $methodName) {
                    echo "<td align='right'>".(isset($array_result['total'][$k11Date][$methodName]['qty'])?$array_result['total'][$k11Date][$methodName]['qty']:0)."</td>";
                    echo "<td align='right'>".(isset($array_result['total'][$k11Date][$methodName]['qty'])?number_format($array_result['total'][$k11Date][$methodName]['amount'],2,'.',','):number_format(0,2,'.',','))."</td>";
                }
                echo "<td align='right'>".(isset($array_result['total'][$k11Date]['total']['qty'])?$array_result['total'][$k11Date]['total']['qty']:0)."</td>";
                echo "<td align='right'>".(isset($array_result['total'][$k11Date]['total']['qty'])?number_format($array_result['total'][$k11Date]['total']['amount'],2,'.',','):number_format(0,2,'.',','))."</td>";    
            }

            foreach($method_array as $methodName) {
                echo "<td align='right'>".(isset($array_result['total']['total'][$methodName]['qty'])?$array_result['total']['total'][$methodName]['qty']:0)."</td>";
                echo "<td align='right'>".(isset($array_result['total']['total'][$methodName]['qty'])?number_format($array_result['total']['total'][$methodName]['amount'],2,'.',','):number_format(0,2,'.',','))."</td>";
            }
            echo "<td align='right'>".(isset($array_result['total']['total']['total']['qty'])?$array_result['total']['total']['total']['qty']:0)."</td>";
            echo "<td align='right'>".(isset($array_result['total']['total']['total']['qty'])?number_format($array_result['total']['total']['total']['amount'],2,'.',','):number_format(0,2,'.',','))."</td>";    
            
        echo "</tr>";

    echo "</tbody>";

echo "</table>";


?>