public static function init($ids = array())
{
global $wpdb;
$guest = __('Guest', 'wc_crm');
$enrolled_plain = "'No'";
if (woocommerce_crm_mailchimp_enabled()) {
$members = woocommerce_crm_get_members();
if (!empty($members)) {
$enrolled_plain = "IF(customers.email IN('" . implode("','", $members) . "'), 'Yes', 'No') ";
}
}
$filter = '';
if (!empty($ids)) {
$filter = " AND customers.c_id IN(" . implode(', ', $ids) . ")";
} else {
$user_roles = get_option('wc_crm_user_roles', array('customer'));
$guest_customers = get_option('wc_crm_guest_customers', 'no');
if (empty($user_roles) || !is_array($user_roles)) {
$user_roles = array('customer');
}
foreach ($user_roles as $value) {
if (!empty($filter)) {
$filter .= ' OR ';
}
$filter .= "customers.capabilities LIKE '%{$value}%'";
}
if ($guest_customers == 'yes') {
$filter .= " OR customers.capabilities = ''";
}
$filter = " AND (" . $filter . ')';
}
$query = "SELECT \n\t\t\t\tCONCAT(customers.first_name, ' ', customers.last_name) as 'Customer name',\n\t\t\t\tIF(u1.meta_value is not NULL , u1.meta_value, p1.meta_value) as 'Billing Company',\n\t\t\t\tIF(u2.meta_value is not NULL, u2.meta_value, p2.meta_value) as 'Billing Address 1',\n\t\t\t\tIF(u3.meta_value is not NULL, u3.meta_value, p3.meta_value) as 'Billing Address 2',\n\t\t\t\tcustomers.city as 'Billing City',\n\t\t\t\tIF(u4.meta_value is not NULL, u4.meta_value, p4.meta_value) as 'Billing Postcode',\n\t\t\t\tcustomers.country as 'Billing Country',\n\t\t\t\tcustomers.state as 'Billing State/County',\n\t\t\t\tIF(u5.meta_value is not NULL, u5.meta_value, p5.meta_value) as 'Billing Email',\n\t\t\t\tcustomers.state as 'Billing Phone',\n\t\t\t\tIF(u6.meta_value is not NULL, u6.meta_value, p6.meta_value) as 'Shipping Company',\n\t\t\t\tIF(u7.meta_value is not NULL, u7.meta_value, p7.meta_value) as 'Shipping Address 1',\n\t\t\t\tIF(u8.meta_value is not NULL, u8.meta_value, p8.meta_value) as 'Shipping Address 2',\n\t\t\t\tIF(u9.meta_value is not NULL, u9.meta_value, p9.meta_value) as 'Shipping City',\n\t\t\t\tIF(u10.meta_value is not NULL, u10.meta_value, p10.meta_value) as 'Shipping Postcode',\n\t\t\t\tIF(u11\t.meta_value is not NULL, u11.meta_value, p11.meta_value) as 'Shipping Country',\n\t\t\t\tIF(u12.meta_value is not NULL, u12.meta_value, p12.meta_value) as 'Shipping State/County',\n\t\t\t\tIF(users.user_login is not NULL, users.user_login, '{$guest}') as 'Username',\n\t\t\t\tIF( customers.last_purchase ='0000-00-00 00:00:00', '', customers.last_purchase) as 'Last purchase date',\n\t\t\t\tcustomers.num_orders as 'Number of orders',\n\t\t\t\tformat(customers.order_value, 2) as 'Total value',\n\t\t\t\t{$enrolled_plain} as 'Subscribed'\n\t\t\t\tFROM {$wpdb->prefix}wc_crm_customer_list as customers\n\t\t\t\tLEFT JOIN {$wpdb->users} as users ON ( customers.user_id = users.ID )\n\t\t\t\tLEFT JOIN {$wpdb->usermeta} as u1 ON ( customers.user_id = u1.user_id AND u1.meta_key = 'billing_company' )\n\t\t\t\tLEFT JOIN {$wpdb->usermeta} as u2 ON ( customers.user_id = u2.user_id AND u2.meta_key = 'billing_address_1' )\n\t\t\t\tLEFT JOIN {$wpdb->usermeta} as u3 ON ( customers.user_id = u3.user_id AND u3.meta_key = 'billing_address_2' )\n\t\t\t\tLEFT JOIN {$wpdb->usermeta} as u4 ON ( customers.user_id = u4.user_id AND u4.meta_key = 'billing_postcode' )\n\t\t\t\tLEFT JOIN {$wpdb->usermeta} as u5 ON ( customers.user_id = u5.user_id AND u5.meta_key = 'billing_email' )\n\t\t\t\tLEFT JOIN {$wpdb->usermeta} as u6 ON ( customers.user_id = u6.user_id AND u6.meta_key = 'shipping_company' )\n\t\t\t\tLEFT JOIN {$wpdb->usermeta} as u7 ON ( customers.user_id = u7.user_id AND u7.meta_key = 'shipping_address_1' )\n\t\t\t\tLEFT JOIN {$wpdb->usermeta} as u8 ON ( customers.user_id = u8.user_id AND u8.meta_key = 'shipping_address_2' )\n\t\t\t\tLEFT JOIN {$wpdb->usermeta} as u9 ON ( customers.user_id = u9.user_id AND u9.meta_key = 'shipping_city' )\n\t\t\t\tLEFT JOIN {$wpdb->usermeta} as u10 ON ( customers.user_id = u10.user_id AND u10.meta_key = 'shipping_postcode' )\n\t\t\t\tLEFT JOIN {$wpdb->usermeta} as u11 ON ( customers.user_id = u11.user_id AND u11.meta_key = 'shipping_country' )\n\t\t\t\tLEFT JOIN {$wpdb->usermeta} as u12 ON ( customers.user_id = u12.user_id AND u12.meta_key = 'shipping_state' )\n\t\t\t\t\n\t\t\t\tLEFT JOIN {$wpdb->postmeta} as p1 ON ( customers.order_id = p1.post_id AND p1.meta_key = '_billing_company' )\n\t\t\t\tLEFT JOIN {$wpdb->postmeta} as p2 ON ( customers.order_id = p2.post_id AND p2.meta_key = '_billing_address_1' )\n\t\t\t\tLEFT JOIN {$wpdb->postmeta} as p3 ON ( customers.order_id = p3.post_id AND p3.meta_key = '_billing_address_2' )\n\t\t\t\tLEFT JOIN {$wpdb->postmeta} as p4 ON ( customers.order_id = p4.post_id AND p4.meta_key = '_billing_postcode' )\n\t\t\t\tLEFT JOIN {$wpdb->postmeta} as p5 ON ( customers.order_id = p5.post_id AND p5.meta_key = '_billing_email' )\n\t\t\t\tLEFT JOIN {$wpdb->postmeta} as p6 ON ( customers.order_id = p6.post_id AND p6.meta_key = '_shipping_company' )\n\t\t\t\tLEFT JOIN {$wpdb->postmeta} as p7 ON ( customers.order_id = p7.post_id AND p7.meta_key = '_shipping_address_1' )\n\t\t\t\tLEFT JOIN {$wpdb->postmeta} as p8 ON ( customers.order_id = p8.post_id AND p8.meta_key = '_shipping_address_2' )\n\t\t\t\tLEFT JOIN {$wpdb->postmeta} as p9 ON ( customers.order_id = p9.post_id AND p9.meta_key = '_shipping_city' )\n\t\t\t\tLEFT JOIN {$wpdb->postmeta} as p10 ON ( customers.order_id = p10.post_id AND p10.meta_key = '_shipping_postcode' )\n\t\t\t\tLEFT JOIN {$wpdb->postmeta} as p11 ON ( customers.order_id = p11.post_id AND p11.meta_key = '_shipping_country' )\n\t\t\t\tLEFT JOIN {$wpdb->postmeta} as p12 ON ( customers.order_id = p12.post_id AND p12.meta_key = '_shipping_state' )\n\t\t\t\tWHERE 1=1\n\t\t\t\t{$filter}\n\t\t\t\t";
#echo '<textarea name="" id="" cols="30" rows="10">'.$query.'</textarea>';
$result = $wpdb->query("SET OPTION SQL_BIG_SELECTS = 1");
$result = $wpdb->get_results($query, ARRAY_A);
if ($result) {
self::download_send_headers("customers_export_" . date("Y-m-d") . ".csv");
echo self::array2csv($result);
die;
}
}
/**
* Handle CSV file download
*/
function export_csv()
{
header('Content-Type: application/csv');
header('Content-Disposition: attachment; filename=customers_' . date('Y-m-d') . '.csv');
header('Pragma: no-cache');
$__wc_crm_customer_details = new WC_Crm_Customer_Details(0, 0);
$__wc_crm_customer_details->init_address_fields('', '', false);
$__b_address = $__wc_crm_customer_details->billing_fields;
$__s_address = $__wc_crm_customer_details->shipping_fields;
$o = WC_CRM()->orders();
$data = $o->get_orders();
echo '"Customer name",';
foreach ($__b_address as $key => $label) {
if ($key == 'first_name' || $key == 'last_name') {
continue;
}
echo '"Billing ' . $label['label'] . '",';
}
foreach ($__s_address as $key => $label) {
if ($key == 'first_name' || $key == 'last_name') {
continue;
}
echo '"Shipping ' . $label['label'] . '",';
}
echo '"Username",';
echo '"Last purchase date",';
echo '"Number of orders",';
echo '"Total value",';
echo "\"Subscribed\"\n";
if (woocommerce_crm_mailchimp_enabled()) {
$members = woocommerce_crm_get_members();
}
foreach ($data as $item) {
//$item = get_object_vars ( $customer );
if ($item['user_id']) {
$user_id = $item['user_id'];
$wc_crm_customer_details = new WC_Crm_Customer_Details($user_id, 0);
$wc_crm_customer_details->init_address_fields('', '', false);
$b_address = $wc_crm_customer_details->billing_fields;
$s_address = $wc_crm_customer_details->shipping_fields;
$data = get_user_meta($item['user_id']);
echo '"' . $data['first_name'][0] . ' ' . $data['last_name'][0] . '",';
foreach ($b_address as $key => $value) {
if ($key == 'first_name' || $key == 'last_name') {
continue;
}
if ($key == 'country') {
echo '"' . $item['country'] . '",';
continue;
}
if ($key == 'email') {
echo '"' . $item['email'] . '",';
continue;
}
$field_name = 'billing_' . $key;
$field_value = get_user_meta($user_id, $field_name, true);
echo '"' . $field_value . '",';
}
foreach ($s_address as $key => $value) {
if ($key == 'first_name' || $key == 'last_name') {
continue;
}
$field_name = 'shipping_' . $key;
$field_value = get_user_meta($user_id, $field_name, true);
echo '"' . $field_value . '",';
}
$user = @get_userdata($user_id);
echo '"' . (isset($user->user_login) ? $user->user_login : __('Guest', 'wc_customer_relationship_manager')) . '",';
$item['num_orders'] = wc_crm_get_num_orders($item['user_id']);
$item['total_spent'] = wc_crm_get_order_value($item['user_id']);
} else {
$order_id = $item['order_id'];
$user_id = 0;
$order = new WC_Order($order_id);
$wc_crm_customer_details_g = new WC_Crm_Customer_Details(0, $order_id);
$wc_crm_customer_details_g->init_address_fields('', '', false);
$b_address = $wc_crm_customer_details_g->billing_fields;
$s_address = $wc_crm_customer_details_g->shipping_fields;
$first_name = get_post_meta($item['order_id'], '_billing_first_name', true);
$last_name = get_post_meta($item['order_id'], '_billing_last_name', true);
echo '"' . $first_name . ' ' . $last_name . '",';
foreach ($b_address as $key => $value) {
if ($key == 'first_name' || $key == 'last_name') {
continue;
}
if ($key == 'country') {
echo '"' . $item['country'] . '",';
continue;
}
if ($key == 'email') {
echo '"' . $item['email'] . '",';
continue;
}
$name_var = 'billing_' . $key;
$field_value = $wc_crm_customer_details_g->order->{$name_var};
echo '"' . $field_value . '",';
}
foreach ($s_address as $key => $value) {
if ($key == 'first_name' || $key == 'last_name') {
continue;
}
$var_name = 'shipping_' . $key;
$field_value = $wc_crm_customer_details_g->order->{$name_var};
echo '"' . $field_value . '",';
}
echo '"' . __('Guest', 'wc_customer_relationship_manager') . '",';
$item['num_orders'] = wc_crm_get_num_orders($item['email'], '_billing_email', true);
$item['total_spent'] = wc_crm_get_order_value($item['email'], '_billing_email', true);
}
$total_spent = wc_crm_price_num_decimals($item['total_spent']);
$last_purchase = $item['order_id'] ? woocommerce_crm_get_pretty_time($item['order_id'], true) : '';
echo '"' . $last_purchase . '",';
echo '"' . $item['num_orders'] . '",';
if (woocommerce_crm_mailchimp_enabled()) {
$enrolled_plain = in_array($item['email'], $members) ? 'yes' : 'no';
echo '"' . $total_spent . '",';
echo '"' . $enrolled_plain . "\"\n";
} else {
echo '"' . $total_spent . "\"\n";
}
}
}
function column_enrolled($item)
{
global $the_customer;
if (woocommerce_crm_mailchimp_enabled()) {
return is_array($this->mailchimp) && in_array($the_customer->email, $this->mailchimp) ? "<span class='enrolled-yes tips' data-tip='Enrolled'></span>" : "<span class='enrolled-no tips' data-tip='Not Enrolled'></span>";
}
return '';
}
function column_enrolled($item)
{
$email = '';
if ($item['user_id'] && !empty($item['user_id']) && get_userdata($item['user_id'])) {
$data = get_user_meta($item['user_id']);
if (!$data || !isset($data['billing_email'])) {
$data = get_userdata($item['user_id']);
$email = $data->user_email;
} else {
$email = $data['billing_email'];
}
} else {
if ($item['order_id'] && !empty($item['order_id'])) {
$email = get_post_meta($item['order_id'], '_billing_email', true);
}
}
if (woocommerce_crm_mailchimp_enabled()) {
return is_array($this->mailchimp) && in_array($email, $this->mailchimp) ? "<span class='enrolled-yes'></span>" : "<span class='enrolled-no'></span>";
}
}
