Order Search

Date Start: * – Date End: * Search by Order Id:

Get Results

‘;

if((($date_start<>”) && ($date_end<>”)) || ($order_search<>”)){

$servername = “localhost”;
$username = “spellbri_fran”;
$password = “Minda123.”;
$dbname = “spellbri_livesite”;

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die(“Connection failed: ” . $conn->connect_error);
}
// Create connection
/*$conn2 = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn2->connect_error) {
die(“Connection failed: ” . $conn2->connect_error);
}*/
// Create connection
/*$conn3 = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn3->connect_error) {
die(“Connection failed: ” . $conn3->connect_error);
}*/

$filter_by_date=””;
if(($date_start<>”) && ($date_end<>”)){
$filter_by_date=”post_date BETWEEN ‘”.$date_start.”‘ AND ‘”.$date_end.”‘ and “;
}

$filter_by_id=””;
if($order_search<>”){
$filter_by_id=” p.ID='”.$order_search.”‘ and “;
}

$sql = “select
p.ID as order_id,
p.post_date,
p.post_status,
max( CASE WHEN pm.meta_key = ‘_billing_email’ and p.ID = pm.post_id THEN pm.meta_value END ) as billing_email,
max( CASE WHEN pm.meta_key = ‘_billing_first_name’ and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_first_name,
max( CASE WHEN pm.meta_key = ‘_billing_last_name’ and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_last_name,
max( CASE WHEN pm.meta_key = ‘_billing_address_1’ and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_address_1,
max( CASE WHEN pm.meta_key = ‘_billing_address_2’ and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_address_2,
max( CASE WHEN pm.meta_key = ‘_billing_city’ and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_city,
max( CASE WHEN pm.meta_key = ‘_billing_state’ and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_state,
max( CASE WHEN pm.meta_key = ‘_billing_postcode’ and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_postcode,
max( CASE WHEN pm.meta_key = ‘_shipping_first_name’ and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_first_name,
max( CASE WHEN pm.meta_key = ‘_shipping_last_name’ and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_last_name,
max( CASE WHEN pm.meta_key = ‘_shipping_address_1’ and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_address_1,
max( CASE WHEN pm.meta_key = ‘_shipping_address_2’ and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_address_2,
max( CASE WHEN pm.meta_key = ‘_shipping_city’ and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_city,
max( CASE WHEN pm.meta_key = ‘_shipping_state’ and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_state,
max( CASE WHEN pm.meta_key = ‘_shipping_postcode’ and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_postcode,
max( CASE WHEN pm.meta_key = ‘_order_total’ and p.ID = pm.post_id THEN pm.meta_value END ) as order_total,
max( CASE WHEN pm.meta_key = ‘_order_tax’ and p.ID = pm.post_id THEN pm.meta_value END ) as order_tax,
max( CASE WHEN pm.meta_key = ‘_paid_date’ and p.ID = pm.post_id THEN pm.meta_value END ) as paid_date,
( select group_concat( order_item_name separator ‘|’ ) from wp_woocommerce_order_items where order_id = p.ID ) as order_items
from
wp_posts p
join wp_postmeta pm on p.ID = pm.post_id
join wp_woocommerce_order_items oi on p.ID = oi.order_id
where
post_type = ‘shop_order’ and
“.$filter_by_date.” “.$filter_by_id.”
(post_status = ‘wc-partial-payment’ or post_status = ‘wc-processing’)
group by
p.ID”;

//echo $sql;
$result = $conn->query($sql);

$count_order=0;

if ($result->num_rows > 0) {
// output data of each row

echo ”

“;

echo ‘

‘;

echo ”

“;

/*while($row = $result->fetch_assoc()) {
echo $row[“order_id”].’, ‘.$row[“post_date”].’, ‘.$row[“post_status”].’, ‘.$row[“billing_email”].’
‘;
}*/
} else {
echo “0 results”;
}
$conn->close();

}
[/insert_php]