posts} AS posts
LEFT JOIN {$wpdb->postmeta} AS meta
ON posts.ID = meta.post_id
WHERE
posts.post_status IN ( 'publish','private' )
AND posts.post_type IN ( 'product' )
AND meta.meta_key IN ( 'total_sales' ,'_price')
ORDER BY
posts.ID ASC,
meta.meta_key ASC
";
$phoen_product_data = $wpdb->get_results( $phoen_product_query,ARRAY_A);
/* echo "";
print_r($phoen_product_data);
echo "
";
die(); */
foreach($phoen_product_data as $key1 => $valuee){
if(!isset( $phoen_top_products[$valuee['ID']])){
$phoen_top_products[$valuee['ID']] = Array();
$phoen_top_products[$valuee['ID']] = Array(
"produc_total" => 0,
"product_price" => 0,
"product_count" => 0,
"product_views" => 0
);
}
switch ($valuee['mkey']) {
case "_price":
$phoen_top_products[$valuee['ID']]["product_price"] = $valuee['product_value'];
break;
case "total_sales":
$phoen_top_products[$valuee['ID']]["product_count"] += $valuee['product_value'];
$phoen_top_products[$valuee['ID']]["produc_total"] += (is_numeric($valuee['product_value'])?$valuee['product_value']:0)* (is_numeric($phoen_top_products[$valuee['ID']]["product_price"])?$phoen_top_products[$valuee['ID']]["product_price"]:0);
$phoen_top_products[$valuee['ID']]["product_name"] = $valuee['product_name'];
$phoen_top_products[$valuee['ID']]["ID"] = $valuee['ID'];
break;
default:
break;
}
}
function phoen_products_short($a, $b) {
return $a['product_count'] < $b['product_count'];
}
usort($phoen_top_products, "phoen_products_short");
$phoen_totle_sale_products='0';
for($i=0; $iget_results( $phoen_coupan_query,ARRAY_A);
function phoen_coupan_short($a, $b) {
return $a['coupan_count'] < $b['coupan_count'];
}
usort($phoen_coupon_data, "phoen_coupan_short");
//Category Data
$phoen_category_query = "
SELECT
posts.post_title AS product_name,
meta.meta_value * pmeta.meta_value as total_amount,
pmeta.meta_value as totle_products,
meta.meta_value as total_sale_count,
terms.name as category_name,
terms.term_id as category_id,
posts.ID AS product_ID
FROM {$wpdb->posts} AS posts
LEFT JOIN {$wpdb->postmeta} AS meta ON posts.ID = meta.post_id
LEFT JOIN {$wpdb->postmeta} AS pmeta ON(posts.ID = pmeta.post_id)
LEFT JOIN {$wpdb->term_relationships} AS rel ON(posts.ID = rel.object_id)
LEFT JOIN {$wpdb->term_taxonomy} AS taxo ON(rel.term_taxonomy_id = taxo.term_taxonomy_id)
LEFT JOIN {$wpdb->terms} AS terms ON(taxo.term_id = terms.term_id)
WHERE
posts.post_status IN ( 'publish','private' )
AND posts.post_type IN ( 'product' )
AND meta.meta_key IN ( 'total_sales' )
AND pmeta.meta_key IN ( '_price')
AND taxo.taxonomy IN ( 'product_cat' )
ORDER BY
posts.ID ASC
";
$phoen_cat_data = $wpdb->get_results( $phoen_category_query,ARRAY_A);
$phoen_all_cat_data=array();
foreach($phoen_cat_data as $kes => $phoen_category_values)
{
if(!isset( $phoen_all_cat_data[$phoen_category_values['category_id']])){
$phoen_all_cat_data[$phoen_category_values['category_id']] = Array(
"total_amount" => 0,
"total_sale_counts" => 0,
"category_names" => $phoen_category_values['category_name'],
"category_ids" => $phoen_category_values['category_id']
);
}
$phoen_all_cat_data[$phoen_category_values['category_id']]["total_sale_counts"] += $phoen_category_values['total_sale_count'];
$phoen_all_cat_data[$phoen_category_values['category_id']]["total_amount"] += (is_numeric($phoen_category_values['total_sale_count'])?$phoen_category_values['total_sale_count']:0) * (is_numeric($phoen_category_values['totle_products'])?$phoen_category_values['totle_products']:0);
}
function phoen_category_shorts($a, $b) {
return $a['total_sale_counts'] < $b['total_sale_counts'];
}
usort($phoen_all_cat_data, "phoen_category_shorts");
//Top Billing State & Top Billing Country & Top Payment Method Data
$phoen_billing_country_querys = "
SELECT
first_name_meta.meta_value as first_name,
last_name_meta.meta_value as last_name,
posts.post_status as ordr_status,
billing_amount_meta.meta_value as billing_amount,
billing_state_meta.meta_value as billing_state,
pay_method_meta.meta_value as pay_method,
payment_method_title_meta.meta_value as payment_method_title,
billing_email_meta.meta_value as billing_email,
billing_country_meta.meta_value as billing_country,
posts.ID AS ID
FROM {$wpdb->posts} AS posts
LEFT JOIN {$wpdb->postmeta} AS first_name_meta ON(posts.ID = first_name_meta.post_id)
LEFT JOIN {$wpdb->postmeta} AS last_name_meta ON(posts.ID = last_name_meta.post_id)
LEFT JOIN {$wpdb->postmeta} AS billing_amount_meta ON(posts.ID = billing_amount_meta.post_id)
LEFT JOIN {$wpdb->postmeta} AS billing_country_meta ON(posts.ID = billing_country_meta.post_id)
LEFT JOIN {$wpdb->postmeta} AS billing_state_meta ON(posts.ID = billing_state_meta.post_id)
LEFT JOIN {$wpdb->postmeta} AS payment_method_title_meta ON(posts.ID = payment_method_title_meta.post_id)
LEFT JOIN {$wpdb->postmeta} AS pay_method_meta ON(posts.ID = pay_method_meta.post_id)
LEFT JOIN {$wpdb->postmeta} AS billing_email_meta ON(posts.ID = billing_email_meta.post_id)
WHERE
posts.post_status LIKE 'wc-%'
AND posts.post_type IN ( 'shop_order' )
AND billing_amount_meta.meta_key IN ( '_order_total')
AND billing_country_meta.meta_key IN ( '_billing_country')
AND billing_state_meta.meta_key IN ( '_billing_state')
AND pay_method_meta.meta_key IN ( '_payment_method')
AND payment_method_title_meta.meta_key IN ( '_payment_method_title')
AND last_name_meta.meta_key IN ( '_billing_last_name')
AND first_name_meta.meta_key IN ( '_billing_first_name')
AND billing_email_meta.meta_key IN ( '_billing_email')
ORDER BY
posts.ID ASC
";
$phoen_Billing_data = $wpdb->get_results( $phoen_billing_country_querys,ARRAY_A);
$phoen_billings_countrys=array();
$phoen_billings_states=array();
$phoen_payment_method=array();
$phoen_order_status=array();
$phoen_top_customer=array();
foreach($phoen_Billing_data as $ky=>$billing_data)
{
if(!isset( $phoen_billings_countrys[$billing_data['billing_country']])){
$phoen_billings_countrys[$billing_data['billing_country']] = Array(
"total_amount" =>0,
"totle_order_counts" => 0,
"country_name" =>$billing_data['billing_country']
);
}
if(!isset( $phoen_billings_states[$billing_data['billing_state']])){
$phoen_billings_states[$billing_data['billing_state']] = Array(
"total_amount" =>0,
"totle_order_counts" => 0,
"state_name" =>$billing_data['billing_state']
);
}
if(!isset( $phoen_payment_method[$billing_data['pay_method']])){
$phoen_payment_method[$billing_data['pay_method']] = Array(
"total_amount" => 0,
"totle_order_counts" => 0,
"payment_name" => $billing_data['payment_method_title']
);
}
if(!isset( $phoen_order_status[$billing_data['ordr_status']])){
$phoen_order_status[$billing_data['ordr_status']] = Array(
"total_amount" => 0,
"order_Statuss"=>$billing_data['ordr_status']
);
}
if(!isset( $phoen_top_customer[$billing_data['billing_email']])){
$phoen_top_customer[$billing_data['billing_email']] = Array(
"total_customer_amoun" => 0,
"totle_cust_order_count" => 0,
"customer_fname" => $billing_data['first_name'],
"customer_lname" => $billing_data['last_name'],
"customer_email" => $billing_data['billing_email']
);
}
$phoen_billings_countrys[$billing_data['billing_country']]["totle_order_counts"] += 1;
$phoen_billings_countrys[$billing_data['billing_country']]["total_amount"] += $billing_data['billing_amount'];
$phoen_billings_states[$billing_data['billing_state']]["totle_order_counts"] += 1;
$phoen_billings_states[$billing_data['billing_state']]["total_amount"] += $billing_data['billing_amount'];
$phoen_payment_method[$billing_data['pay_method']]["totle_order_counts"] += 1;
$phoen_payment_method[$billing_data['pay_method']]["total_amount"] += $billing_data['billing_amount'];
$phoen_order_status[$billing_data['ordr_status']]["total_amount"] += $billing_data['billing_amount'];
$phoen_top_customer[$billing_data['billing_email']]["totle_cust_order_count"] += 1;
$phoen_top_customer[$billing_data['billing_email']]["total_customer_amoun"] += $billing_data['billing_amount'];
}
function phoen_country_short($c, $d) {
return $c['totle_order_counts'] < $d['totle_order_counts'];
}
usort($phoen_billings_countrys, "phoen_country_short");
function phoen_state_short($e, $f) {
return $e['totle_order_counts'] < $f['totle_order_counts'];
}
usort($phoen_billings_states, "phoen_state_short");
function phoen_payment_short($g, $h) {
return $g['totle_order_counts'] < $h['totle_order_counts'];
}
usort($phoen_payment_method, "phoen_payment_short");
function phoen_customer_short($g, $h) {
return $g['total_customer_amoun'] < $h['total_customer_amoun'];
}
usort($phoen_top_customer, "phoen_customer_short");
//Recent Order Data
$phoen_recent_order_querys = "
SELECT
first_name_meta.meta_value as first_name,
last_name_meta.meta_value as last_name,
posts.post_status as ordr_status,
tmeta.meta_value as billing_amount,
billing_email_meta.meta_value as billing_email,
cart_discount_meta.meta_value as cart_discount,
order_tex_meta.meta_value as order_tex,
order_shipping_tax_meta.meta_value as order_shipping_tax,
order_shipping_meta.meta_value as order_shipping,
customer_user_meta.meta_value as customer_user,
posts.post_date as order_date,
posts.ID AS ID
FROM {$wpdb->posts} AS posts
LEFT JOIN {$wpdb->postmeta} AS tmeta ON(posts.ID = tmeta.post_id)
LEFT JOIN {$wpdb->postmeta} AS first_name_meta ON(posts.ID = first_name_meta.post_id)
LEFT JOIN {$wpdb->postmeta} AS last_name_meta ON(posts.ID = last_name_meta.post_id)
LEFT JOIN {$wpdb->postmeta} AS billing_email_meta ON(posts.ID = billing_email_meta.post_id)
LEFT JOIN {$wpdb->postmeta} AS cart_discount_meta ON(posts.ID = cart_discount_meta.post_id)
LEFT JOIN {$wpdb->postmeta} AS order_tex_meta ON(posts.ID = order_tex_meta.post_id)
LEFT JOIN {$wpdb->postmeta} AS order_shipping_tax_meta ON(posts.ID = order_shipping_tax_meta.post_id)
LEFT JOIN {$wpdb->postmeta} AS order_shipping_meta ON(posts.ID = order_shipping_meta.post_id)
LEFT JOIN {$wpdb->postmeta} AS customer_user_meta ON(posts.ID = customer_user_meta.post_id)
WHERE
posts.post_status IN ('wc-completed','wc-on-hold','wc-cancelled','wc-refunded','wc-failed','wc-pending','wc-processing')
AND posts.post_type IN ( 'shop_order' )
AND tmeta.meta_key IN ( '_order_total')
AND last_name_meta.meta_key IN ( '_billing_last_name')
AND first_name_meta.meta_key IN ( '_billing_first_name')
AND billing_email_meta.meta_key IN ( '_billing_email')
AND cart_discount_meta.meta_key IN ( '_cart_discount')
AND order_tex_meta.meta_key IN ( '_order_tax')
AND order_shipping_tax_meta.meta_key IN ( '_order_shipping_tax')
AND order_shipping_meta.meta_key IN ( '_order_shipping')
AND customer_user_meta.meta_key IN ( '_customer_user')
ORDER BY
posts.ID DESC
";
$phoen_recent_order_datas = $wpdb->get_results( $phoen_recent_order_querys,ARRAY_A);
// Total Tax & Total Coupan Amount & Total Customers User Data
$phoen_all_tax='0';
$phoen_ord_tax='0';
$phoe_ship_tax='0';
$phoen_toatal_cupan_amount='0';
$phoen_guest_user=0;
$totle_billing_amount='0';
$phoen_order_rf_counts='0';
$totle_billing_amountt='0';
$pho_order_ship='0';
$totle_billing_refunds_amoun='0';
$pho_order_shipr='0';
$phoen_ord_taxr='0';
$phoe_ship_taxr='0';
$phoen_toatal_cupan_amountt='0';
$phoen_totle_year_amount='0';
for($i=0; $iget_results( $phoe_order_by_date_query,ARRAY_A);
$phe_datas=1.11;
$phoen_total_orders='0';
$phoen_total_amounss='0';
$phoen_yesterday_orders='0';
$phoen_yesterday_amounss='0';
foreach($phoen_totle_day_data as $phoe_vals)
{
$current_date= date("Y-m-d");
if($phoe_vals['order_date']==$current_date)
{
if($phoe_vals['mkey'] == '_order_total')
{
$phoen_total_orders+=$phoe_vals['totle_order_counts'];
$phoen_total_amounss+=$phoe_vals['total_amount'];
}
}
$phoen_date=date('d-m-Y',strtotime("-1 days"));
if($phoe_vals['order_date']==$phoen_date)
{
$phoen_yesterday_orders+=$phoe_vals['totle_order_counts'];
$phoen_yesterday_amounss+=$phoe_vals['total_amount'];
}
}
?>
|
|
|
|
$phoen_top_customers)
{
?>
|
|
|
|
|
|
|
$phoen_billings_countryss)
{
?>
|
|
|
|
|
|
|
$phoen_billings_statess)
{
?>
|
|
|
|
|
|
|
$phoen_coupon_datas)
{
?>
|
|
|
|