/classes/class.putler-connector-woocommerce.php

  1. <?php 
  2.  
  3. if ( ! defined( 'ABSPATH' ) ) exit; // Exit if accessed directly 
  4.  
  5. if ( ! class_exists( 'WooCommerce_Putler_Connector' ) ) { 
  6.  
  7. class WooCommerce_Putler_Connector { 
  8.  
  9. private $name = 'woocommerce'; 
  10.  
  11. public function __construct() { 
  12. add_filter('putler_connector_get_order_count', array( &$this, 'get_order_count') ); 
  13. add_filter('putler_connector_get_orders', array( &$this, 'get_orders') ); 
  14. add_filter('putler_connector_sub_updated', array( &$this, 'get_sub') ); 
  15.  
  16. //Flag for woo2.2+ 
  17. if (version_compare ( WOOCOMMERCE_VERSION, '2.2.0', '<' )) { 
  18. define ( 'SM_IS_WOO22', "false" ); 
  19. } else { 
  20. define ( 'SM_IS_WOO22', "true" ); 
  21.  
  22.  
  23.  
  24. public function new_status_map($v) { return $v[0]; } 
  25.  
  26. //Function to get sub data when updated 
  27. public function get_sub_status( $status ) { 
  28.  
  29. $sub_status = array(); 
  30.  
  31. $sub_status = array('wc-active' => 'Active',  
  32. 'wc-cancelled' => 'Canceled',  
  33. 'wc-expired' => 'Expired',  
  34. 'wc-pending' => 'Pending',  
  35. 'wc-pending-cancel' => 'Pending Canceled',  
  36. 'wc-on-hold' => 'Held',  
  37. 'wc-suspend' => 'Suspend',  
  38. 'wc-reactivated' => 'Reactivated',  
  39. 'wc-created' => 'Created',  
  40. 'wc-switched' => 'Switched', //added if subscription not properly updated to v2.0 
  41. 'trash' => 'Delete'); 
  42.  
  43. $new_status = ( !empty($sub_status[$status]) ) ? $sub_status[$status] : (( !empty($sub_status['wc-'.$status]) ) ? $sub_status['wc-'.$status] : $status); 
  44.  
  45. return $new_status; 
  46.  
  47. //Function to get sub data when updated 
  48. public function get_sub( $params ) { 
  49.  
  50. if ( empty($params) || empty($params['sub_id']) ) { 
  51. return; 
  52.  
  53. global $wpdb; 
  54.  
  55. $new_status = (!empty($params['sub_details']) && !empty($params['sub_details']['post_status']) ) ? $params['sub_details']['post_status'] : ( !empty( $params['new_status'] ) ? $params['new_status'] : '' ); 
  56. $new_status = (!empty($params['sub_details']) && $params['sub_details']['post_status'] == 'trash') ? $params['sub_details']['post_status'] : $new_status; //added for handling trashed status 
  57. $new_status = $this->get_sub_status($new_status); 
  58. $new_status_abbr = implode('', array_map( array($this, 'new_status_map'), explode(' ', $new_status)) ); 
  59.  
  60. $response = array( 'Item_Title' => 'Unknown', 'Quantity' => 1 ); 
  61.  
  62. if( (empty($params['sub_details'])) ) { 
  63. //query to get the sub details 
  64. $query = "SELECT date_format(post_modified_gmt, '%Y-%m-%d %T') AS modified_date,  
  65. post_parent AS order_id 
  66. FROM {$wpdb->prefix}posts 
  67. WHERE id = ".$params['sub_id']; 
  68. $results = $wpdb->get_results($query, 'ARRAY_A'); 
  69.  
  70. if( count($results) > 0 ) { 
  71. foreach ($results as $result) { 
  72. $response ['Date'] = date('m/d/Y', (int)strtotime($result['modified_date'])); 
  73. $response ['Time'] = date('H:i:s', (int)strtotime($result['modified_date'])); 
  74. $response ['Time_Zone'] = 'GMT'; 
  75. $response ['Source'] = $this->name; 
  76. $response ['Type'] = 'Recurring Payment'; 
  77. $response ['Status'] = $new_status; 
  78. $response ['Transaction_ID'] = $result['order_id'] .'_'. $new_status_abbr .'_'. time(); 
  79. $response ['Subscription_ID'] = $params['sub_id']; 
  80. } else { 
  81.  
  82. $response = array(); 
  83.  
  84. $response ['Date'] = date('m/d/Y', (int)strtotime($params['sub_details']['post_modified_gmt'])); 
  85. $response ['Time'] = date('H:i:s', (int)strtotime($params['sub_details']['post_modified_gmt'])); 
  86. $response ['Time_Zone'] = 'GMT'; 
  87. $response ['Source'] = $this->name; 
  88. $response ['Name'] = ''; 
  89. $response ['Type'] = 'Recurring Payment'; 
  90. $response ['Status'] = $new_status; 
  91. $response ['Currency'] = 'USD'; 
  92. $response ['Gross'] = '0.00'; 
  93. $response ['Fee'] = '0.00'; 
  94. $response ['Net'] = '0.00'; 
  95. $response ['From_Email_Address'] = ''; 
  96. $response ['To_Email_Address'] = ''; 
  97. $response ['Transaction_ID'] = ( $new_status != 'Created' ) ? $params['sub_details']['post_parent'] .'_'. $new_status_abbr .'_'. time() : $params['sub_id']; 
  98. $response ['Counterparty_Status'] = ''; 
  99. $response ['Address_Status'] = ''; 
  100. $response ['Item_Title'] = ''; 
  101. $response ['Item_ID'] = ''; 
  102. $response ['Shipping_and_Handling_Amount'] = '0.00'; 
  103. $response ['Insurance_Amount'] = '0.00'; 
  104. $response ['Discount'] = '0.00'; 
  105. $response ['Sales_Tax'] = '0.00'; 
  106. $response ['Option_1_Name'] = ''; 
  107. $response ['Option_1_Value'] = ''; 
  108. $response ['Option_2_Name'] = ''; 
  109. $response ['Option_2_Value'] = ''; 
  110. $response ['Auction_Site'] = ''; 
  111. $response ['Buyer_ID'] = ''; 
  112. $response ['Item_URL'] = ''; 
  113. $response ['Closing_Date'] = ''; 
  114. $response ['Escrow_ID'] = ''; 
  115. $response ['Invoice_ID'] = ''; 
  116. $response ['Reference_Txn_ID'] = ''; 
  117. $response ['Invoice_Number'] = ''; 
  118. $response ['Custom_Number'] = ''; 
  119. $response ['Quantity'] = ''; 
  120. $response ['Receipt_ID'] = ''; 
  121. $response ['Balance'] = '0.00'; 
  122. $response ['Note'] = ''; 
  123. $response ['Address_Line_1'] = ''; 
  124. $response ['Address_Line_2'] = ''; 
  125. $response ['Town_City'] = ''; 
  126. $response ['State_Province'] = ''; 
  127. $response ['Zip_Postal_Code'] = ''; 
  128. $response ['Country'] = ''; 
  129. $response ['Contact_Phone_Number'] = ''; 
  130. $response ['Subscription_ID'] = $params['sub_id']; 
  131.  
  132. $pm_cond = (!empty($params['sub_details'])) ? " AND meta_key NOT IN ('_edit_lock', '_edit_last') " : " AND meta_key IN ('_order_currency', '_billing_email', '_billing_first_name', '_billing_last_name', '_payment_method_title', '_billing_interval', '_billing_period', '_schedule_trial_end', '_schedule_end') "; 
  133.  
  134. $sub_details = ( !empty($params['sub_meta']) ) ? $params['sub_details'] : array(); 
  135. $sub_meta = ( !empty($params['sub_meta']) ) ? $params['sub_meta'] : array(); 
  136.  
  137. if( empty($sub_details) && empty($sub_meta) ) {//for non-initial data sync 
  138.  
  139. $sub_details = array( 'created_date' => ($response ['Date'] .' '. $response ['Time'] ), 'status' => $new_status ); 
  140.  
  141. $sub_meta_keys = array(); 
  142.  
  143. $sub_meta_keys['_payment_method_title'] = 'payment_method'; 
  144. $sub_meta_keys['_billing_interval'] = 'interval'; 
  145. $sub_meta_keys['_billing_period'] = 'period'; 
  146. $sub_meta_keys['_schedule_trial_end'] = 'trial_end'; 
  147. $sub_meta_keys['_schedule_end'] = 'sub_end'; 
  148.  
  149. //query to get the sub meta details 
  150. $query = "SELECT meta_key,  
  151. meta_value 
  152. FROM {$wpdb->prefix}postmeta 
  153. WHERE post_id = ".$params['sub_id']." 
  154. $pm_cond 
  155. GROUP BY post_id, meta_key"; 
  156. $results = $wpdb->get_results($query, 'ARRAY_A'); 
  157.  
  158. if( count($results) > 0 ) { 
  159.  
  160. $name = ''; 
  161.  
  162. foreach ($results as $result) { 
  163. if( $result['meta_key'] == '_order_currency' ) { 
  164. $response ['Currency'] = $result['meta_value']; 
  165. } else if( $result['meta_key'] == '_billing_email' ) { 
  166. $response ['From_Email_Address'] = $result['meta_value']; 
  167. } else if( $result['meta_key'] == '_billing_first_name' ) { 
  168. $name = $result['meta_value']; 
  169. } else if( $result['meta_key'] == '_billing_last_name' ) { 
  170. $name .= ' '. $result['meta_value']; 
  171.  
  172. $response ['Name'] = $name; 
  173.  
  174. //code for sub meta when sub is updated 
  175. if( !empty($params['sub_details']) ) { 
  176. $sub_details[$result['meta_key']] = $result['meta_value']; 
  177.  
  178. if( !empty($result['meta_key']) && !empty($sub_meta_keys[$result['meta_key']]) ) { 
  179. $sub_meta[$sub_meta_keys[$result['meta_key']]] = $result['meta_value']; 
  180. } else { 
  181.  
  182. if( !empty($sub_details['post_modified_gmt']) ) { 
  183. $sub_details['created_date'] = $sub_details['post_modified_gmt']; 
  184. unset($sub_details['post_created_gmt']); 
  185. unset($sub_details['post_modified_gmt']); 
  186. unset($sub_details['post_parent']); 
  187.  
  188. $response ['Raw_Data'] = ''; 
  189.  
  190. if( !empty($params['sub_details']) && empty($params['new_status']) ) { 
  191. // $response ['Raw_Data'] = maybe_serialize(array( 'subscriptions' => array( $params['sub_id'] => $sub_details ) )); 
  192. if( empty($params['trash']) ) { 
  193. $response ['Raw_Data'] = json_encode(array( 'subscriptions' => array( $params['sub_id'] => $sub_details ) )); 
  194.  
  195. $response ['Payment_Source'] = ''; 
  196. $response ['External_Trans_ID'] = ''; 
  197. $response ['IP_Address'] = ''; 
  198.  
  199. if( !empty($sub_meta) ) { 
  200. $sub_meta['status'] = $new_status; 
  201. $response ['Sub_Meta'] = json_encode($sub_meta); 
  202. } else { 
  203. $response ['Sub_Meta'] = json_encode(json_decode ("{}")); 
  204.  
  205. $params['data'] = array($response); 
  206. return $params; 
  207.  
  208. public function get_order_count( $count ) { 
  209. global $wpdb; 
  210. $order_count = 0; 
  211. $post_order_cond = ''; 
  212.  
  213. //Flag for woo2.2+ 
  214. if ( (defined('SM_IS_WOO22') && SM_IS_WOO22) ) { 
  215. $post_order_cond = " AND posts.post_status NOT IN ('trash', 'auto-draft', 'draft')"; 
  216. } else {  
  217. $post_order_cond = " AND posts.post_status IN ('publish', 'draft')"; 
  218.  
  219. $query_to_fetch_order_count = "SELECT COUNT(posts.ID) as id 
  220. FROM {$wpdb->prefix}posts AS posts  
  221. WHERE posts.post_type IN ('shop_order', 'shop_order_refund')  
  222. AND posts.post_date_gmt != '0000-00-00 00:00:00' 
  223. $post_order_cond"; 
  224.  
  225. $order_count_result = $wpdb->get_col( $query_to_fetch_order_count ); 
  226.  
  227. if( !empty( $order_count_result ) ) { 
  228. $order_count = $order_count_result[0]; 
  229.  
  230. return $count + $order_count; 
  231.  
  232. public function get_orders( $params ) { 
  233. global $wpdb; 
  234. $orders = array(); 
  235.  
  236. $wc_order_status = (defined('SM_IS_WOO22') && SM_IS_WOO22 == 'true') ? wc_get_order_statuses() : array(); 
  237.  
  238. //Code to get the last order sent 
  239.  
  240. $cond = ''; 
  241.  
  242. if ( empty($params['order_id']) ) { 
  243. // $start_limit = (isset($params[ $this->name ]['start_limit'])) ? $params[ $this->name ]['start_limit'] : 0; 
  244. $start_limit = (isset($params['offset'])) ? $params['offset'] : 0; 
  245. $batch_limit = (isset($params['limit'])) ? $params['limit'] : 50;  
  246. } else { 
  247. $start_limit = 0; 
  248. $batch_limit = 1; 
  249.  
  250. //For Handling Refund Transactions 
  251. if( get_post_status($params['order_id']) == 'wc-refunded' ) { 
  252. $query = "SELECT MAX(id) AS id 
  253. FROM {$wpdb->prefix}posts 
  254. WHERE post_type = 'shop_order_refund' 
  255. AND post_parent = ".$params['order_id']; 
  256. $refund_id = $wpdb->get_var($query); 
  257.  
  258. if( !empty($refund_id) ) { 
  259. $params ['refund_parent_id'] = $params['order_id']; 
  260. $params ['order_id'] = $refund_id; 
  261.  
  262. $cond = ' AND posts.ID IN(' .intval($params['order_id']). ') ';  
  263.  
  264. //Flag for woo2.2+ 
  265. if (defined('SM_IS_WOO22') && SM_IS_WOO22 == 'true') { 
  266. $terms_post_join = ''; 
  267. $terms_select = "posts.post_status AS order_status"; 
  268.  
  269. if( !empty($params['trash']) ) { 
  270. $post_order_cond = " AND posts.post_status = 'trash'";  
  271. } else { 
  272. $post_order_cond = " AND posts.post_status NOT IN ('trash', 'auto-draft', 'draft')"; 
  273.  
  274.  
  275. } else { 
  276. //Code to get all the term_names along with the term_taxonomy_id in an array 
  277. $query_order_status = "SELECT terms.name as order_status,  
  278. term_taxonomy.term_taxonomy_id  
  279. FROM {$wpdb->prefix}term_taxonomy AS term_taxonomy 
  280. JOIN {$wpdb->prefix}terms AS terms ON (terms.term_id = term_taxonomy.term_id) 
  281. WHERE taxonomy LIKE 'shop_order_status'"; 
  282.  
  283. $results_order_status = $wpdb->get_results( $query_order_status, 'ARRAY_A' ); 
  284.  
  285. $order_status = array(); 
  286.  
  287. foreach ($results_order_status as $results_order_status1) { 
  288. $order_status[$results_order_status1['term_taxonomy_id']] = $results_order_status1['order_status']; 
  289.  
  290. $terms_post_join = "JOIN ".$wpdb->prefix ."term_relationships AS term_relationships ON (term_relationships.object_id = posts.ID AND posts.post_status IN ('publish', 'draft'))"; 
  291. $terms_select = "term_relationships.term_taxonomy_id AS term_taxonomy_id"; 
  292.  
  293. if( !empty($params['trash']) ) { 
  294. $post_order_cond = " AND posts.post_status = 'trash'"; 
  295. } else { 
  296. $post_order_cond = " AND posts.post_status IN ('publish', 'draft')"; 
  297.  
  298. //Code for handling manual refunds 
  299. if( !empty($params ['refund_parent_id']) ) { 
  300. $cond_type = " AND posts.post_type = 'shop_order_refund' "; 
  301. } else if( !empty($params['order_id']) ) { 
  302. $cond_type = " AND posts.post_type = 'shop_order' "; 
  303. } else if( empty($params['order_id']) ) { 
  304. $cond_type = " AND posts.post_type IN ('shop_order', 'shop_order_refund') "; 
  305.  
  306. $query_order_details = "SELECT posts.ID as id,  
  307. posts.post_type as type,  
  308. posts.post_parent as parent_id,  
  309. posts.post_excerpt as order_note,  
  310. date_format(posts.post_date_gmt, '%Y-%m-%d %T') AS date,  
  311. date_format(posts.post_modified_gmt, '%Y-%m-%d %T') AS modified_date,  
  312. $terms_select 
  313. FROM {$wpdb->prefix}posts AS posts  
  314. $terms_post_join 
  315. WHERE posts.post_date_gmt != '0000-00-00 00:00:00' 
  316. AND post_modified_gmt BETWEEN '". $params['start_date'] ."' AND '". $params['end_date'] ."' 
  317. $cond_type 
  318. $cond 
  319. $post_order_cond 
  320. GROUP BY posts.ID 
  321. LIMIT ". $start_limit .", ". $batch_limit;  
  322.  
  323. $results_order_details = $wpdb->get_results( $query_order_details, 'ARRAY_A' ); 
  324. $results_order_details_count = $wpdb->num_rows; 
  325.  
  326. if ( $results_order_details_count > 0 ) { 
  327.  
  328. $order_ids = array();  
  329. $man_refund_ids = array(); //array for storing the manual refund ids 
  330.  
  331. foreach ( $results_order_details as $results_order_detail ) { 
  332. $order_ids[] = (!empty($results_order_detail['type']) && $results_order_detail['type'] == 'shop_order_refund') ? $results_order_detail['parent_id'] : $results_order_detail['id']; 
  333.  
  334. if( !empty($results_order_detail['type']) && $results_order_detail['type'] == 'shop_order_refund' ) { 
  335. $man_refund_ids[$results_order_detail['id']] = $results_order_detail['parent_id']; 
  336.  
  337. $order_ids = (!empty($params ['refund_parent_id'])) ? array($params ['refund_parent_id']) : $order_ids; //for handling meta data for manual refunds 
  338.  
  339. //Query to get the Order_items 
  340.  
  341. $item_details = array(); 
  342.  
  343. $query_cart_items = "SELECT orderitems.order_id,  
  344. orderitems.order_item_name,  
  345. orderitems.order_item_id,  
  346. orderitems.order_item_type AS item_type,  
  347. itemmeta.meta_value AS meta_value,  
  348. itemmeta.meta_key AS meta_key 
  349. FROM {$wpdb->prefix}woocommerce_order_items AS orderitems  
  350. JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS itemmeta  
  351. ON (orderitems.order_item_id = itemmeta.order_item_id) 
  352. WHERE orderitems.order_item_type IN ('line_item', 'shipping', 'coupon') 
  353. AND orderitems.order_id IN (". implode(", ", $order_ids) .") 
  354. GROUP BY orderitems.order_id, orderitems.order_item_id, meta_key"; 
  355. $results_cart_items = $wpdb->get_results ( $query_cart_items, 'ARRAY_A' ); 
  356. $results_cart_items_count = $wpdb->num_rows; 
  357.  
  358. $variation_ids = array(); 
  359. $product_ids = array(); 
  360. $coupon_codes = array(); 
  361.  
  362. if ( $results_cart_items_count > 0 ) { 
  363.  
  364. foreach ( $results_cart_items as $cart_item ) { 
  365. $order_id = $cart_item['order_id'];  
  366. $order_item_id = $cart_item['order_item_id']; 
  367.  
  368. if( !isset( $item_details[$order_id] ) ) { 
  369. $item_details[$order_id] = array(); 
  370. $item_details[$order_id]['tot_qty'] = 0; 
  371. $item_details[$order_id]['cart_items'] = array(); 
  372. $item_details[$order_id]['coupons'] = array(); 
  373. $item_details[$order_id]['_shipping_method_title'] = ''; 
  374.  
  375. // for getting the shipping related data 
  376. if ( $cart_item['item_type'] == 'shipping' ) { 
  377. $item_details[$order_id]['_shipping_method_title'] = $cart_item['order_item_name']; 
  378. continue; 
  379.  
  380. // for getting the coupon related data 
  381. if ( $cart_item['item_type'] == 'coupon' ) { 
  382. if ( !isset($item_details[$order_id]['coupons'][$cart_item['order_item_name']]) ) { 
  383. $item_details[$order_id]['coupons'][$cart_item['order_item_name']] = array(); 
  384.  
  385. // storing the distinct coupon codes 
  386. if ( array_search($cart_item['order_item_name'], $coupon_codes) === false ) { 
  387. $coupon_codes[] = $cart_item['order_item_name']; 
  388.  
  389. $item_details[$order_id]['coupons'][$cart_item['order_item_name']]['code'] = $cart_item['order_item_name']; 
  390. $item_details[$order_id]['coupons'][$cart_item['order_item_name']]['amt'] = ($cart_item['meta_key'] == "discount_amount" && !empty($cart_item['meta_value'])) ? $cart_item['meta_value'] : 0; 
  391. continue; 
  392.  
  393. if ( !isset($item_details[$order_id]['cart_items'][$order_item_id]) ) { 
  394. $item_details[$order_id]['cart_items'][$order_item_id] = array(); 
  395. $item_details[$order_id]['tot_qty']++; 
  396. $item_details[$order_id]['cart_items'][$order_item_id]['product_name'] = $cart_item['order_item_name']; 
  397. }  
  398.  
  399. $item_details[$order_id]['cart_items'][$order_item_id][$cart_item['meta_key']] = $cart_item['meta_value']; 
  400.  
  401. if ( $cart_item['meta_key'] == "_variation_id" && !empty($cart_item['meta_value']) ) { 
  402. $variation_ids [] = $cart_item['meta_value']; 
  403. $product_ids [] = $cart_item['meta_value']; 
  404.  
  405. if ( $cart_item['meta_key'] == "_product_id" && !empty($cart_item['meta_value']) ) { 
  406. $product_ids [] = $cart_item['meta_value']; 
  407. }  
  408.  
  409.  
  410. // Code to get the coupon meta data 
  411. $coupon_data = array(); 
  412.  
  413. if ( !empty($coupon_codes) ) { 
  414. $query = "SELECT p.post_date as created_date,  
  415. p.post_name as slug,  
  416. p.post_title as code,  
  417. p.post_excerpt as descrip,  
  418. pm.meta_value AS meta_value,  
  419. pm.meta_key AS meta_key 
  420. FROM {$wpdb->prefix}posts AS p 
  421. JOIN {$wpdb->prefix}postmeta AS pm ON (pm.post_id = p.id  
  422. AND p.post_type = 'shop_coupon' 
  423. AND pm.meta_key NOT IN ('_edit_lock', '_edit_last')) 
  424. WHERE p.post_name IN ('". implode("', '", $coupon_codes) ."') 
  425. GROUP BY p.id, meta_key"; 
  426. $results = $wpdb->get_results ( $query, 'ARRAY_A' ); 
  427.  
  428. if ( count($results) > 0 ) { 
  429. foreach ($results as $result) { 
  430. if ( empty($coupon_data[$result['slug']]) ) { 
  431. $coupon_data[$result['slug']] = array(); 
  432. $coupon_data[$result['slug']]['code'] = $result['code']; 
  433. $coupon_data[$result['slug']]['description'] = $result['descrip']; 
  434. $coupon_data[$result['slug']]['created_date'] = $result['created_date']; 
  435. $coupon_data[$result['slug']][$result['meta_key']] = $result['meta_value']; 
  436.  
  437.  
  438. $products_sku = $sub_item_ids = array(); 
  439.  
  440. if( !empty($product_ids) ) { 
  441.  
  442. //Code to get the SKU for the products 
  443.  
  444. $query_sku = "SELECT postmeta.post_id AS id,  
  445. postmeta.meta_value AS sku 
  446. FROM {$wpdb->prefix}posts AS posts 
  447. JOIN {$wpdb->prefix}postmeta AS postmeta ON (posts.id = postmeta.post_id) 
  448. WHERE posts.id IN (". implode(", ", array_unique($product_ids)) .") 
  449. AND postmeta.meta_key IN ('_sku') 
  450. AND postmeta.meta_value <> '' 
  451. GROUP BY posts.id"; 
  452. $results_sku = $wpdb->get_results ( $query_sku , 'ARRAY_A'); 
  453. $results_sku_count = $wpdb->num_rows; 
  454.  
  455. if ( $results_sku_count > 0 ) { 
  456. foreach ($results_sku as $product_sku) { 
  457. $products_sku [$product_sku['id']] = $product_sku['sku']; 
  458. }  
  459.  
  460. //Code to get the type for the products 
  461. $query = "SELECT tt.term_taxonomy_id AS taxonomy_id 
  462. FROM {$wpdb->prefix}term_taxonomy AS tt 
  463. JOIN {$wpdb->prefix}terms AS t 
  464. ON (t.term_id = tt.term_id AND tt.taxonomy = 'product_type') 
  465. WHERE t.slug IN ('subscription', 'variable-subscription')"; 
  466. $sub_taxonomy_ids = $wpdb->get_col ( $query ); 
  467.  
  468. if( count($sub_taxonomy_ids) > 0 ) { 
  469. $query = "SELECT tr.object_id AS id 
  470. FROM {$wpdb->prefix}term_relationships AS tr 
  471. JOIN {$wpdb->prefix}term_taxonomy AS tt  
  472. ON (tt.term_taxonomy_id = tr.term_taxonomy_id AND tt.taxonomy = 'product_type') 
  473. WHERE tt.term_taxonomy_id IN (". implode(', ', array_unique($sub_taxonomy_ids)) .")"; 
  474. $sub_item_ids = $wpdb->get_col ( $query ); 
  475.  
  476.  
  477.  
  478. //Query to get the variation Attributes 
  479.  
  480. if (!empty($variation_ids)) { 
  481. $query_variation_att = "SELECT post_id AS id,  
  482. meta_value AS meta_value,  
  483. meta_key AS meta_key 
  484. FROM {$wpdb->prefix}postmeta 
  485. WHERE meta_key LIKE 'attribute_%' 
  486. AND post_id IN (". implode(", ", array_unique($variation_ids)) .") 
  487. GROUP BY id, meta_key"; 
  488.  
  489. $results_variation_att = $wpdb->get_results ( $query_variation_att , 'ARRAY_A'); 
  490. $results_variation_att_count = $wpdb->num_rows; 
  491.  
  492. if ( $results_variation_att_count > 0) { 
  493.  
  494. $i = 0; 
  495.  
  496. $query_terms = "SELECT terms.slug as slug, terms.name as term_name 
  497. FROM {$wpdb->prefix}terms AS terms 
  498. JOIN {$wpdb->prefix}postmeta AS postmeta  
  499. ON ( postmeta.meta_value = terms.slug  
  500. AND postmeta.meta_key LIKE 'attribute_%' )  
  501. GROUP BY terms.slug"; 
  502. $attributes_terms = $wpdb->get_results( $query_terms, 'ARRAY_A' ); 
  503.  
  504. $attributes = array(); 
  505. foreach ( $attributes_terms as $attributes_term ) { 
  506. $attributes[$attributes_term['slug']] = $attributes_term['term_name']; 
  507.  
  508. $variations = array(); 
  509. // Formatting of the Variations Names 
  510. foreach( $results_variation_att as $variation_att ) { 
  511.  
  512. $att_name = ''; 
  513. $att_val = ''; 
  514.  
  515. // if ( strpos($variation_att['meta_key'], 'custom') === false ) {  
  516. // $variation_att['meta_value'] = $attributes[$variation_att['meta_value']]; 
  517. // } 
  518.  
  519.  
  520. if ( empty($variations[$variation_att['id']]) ) { 
  521. $i = 0; 
  522. $variations[$variation_att['id']] = array(); 
  523.  
  524. if ( strpos($variation_att['meta_key'], 'pa') !== false ) { 
  525. $att_name = substr($variation_att['meta_key'], strpos($variation_att['meta_key'], 'pa')+3); 
  526. $att_val = (!empty($attributes[$variation_att['meta_value']])) ? $attributes[$variation_att['meta_value']] : ''; 
  527. // } else if ( strpos($variation_att['meta_key'], 'custom') !== false ) { 
  528. } else { 
  529. // $att_name = 'custom'; 
  530. $att_name = ucfirst(substr($variation_att['meta_key'], strpos($variation_att['meta_key'], '_')+1)); 
  531. $att_val = $variation_att['meta_value']; 
  532. }  
  533.  
  534. if ( $i > 1 ) { 
  535.  
  536. if ( $i == 2 ) { 
  537. $variations[$variation_att['id']][0]['option1_value'] = $variations[$variation_att['id']][0]['option1_name'] . ' : ' . $variations[$variation_att['id']][0]['option1_value'] . ', ' 
  538. . $variations[$variation_att['id']][1]['option1_name'] . ' : ' . $variations[$variation_att['id']][1]['option1_value']; 
  539. unset($variations[$variation_att['id']][1]); 
  540.  
  541. $variations[$variation_att['id']][0]['option1_name'] = ''; 
  542. $variations[$variation_att['id']][0]['option1_value'] = $variations[$variation_att['id']][0]['option1_value'] . ', '  
  543. . $att_name . ' : ' . $att_val; 
  544.  
  545. } else { 
  546. $variations[$variation_att['id']][$i]['option1_name'] = $att_name; 
  547. $variations[$variation_att['id']][$i]['option1_value'] = $att_val; 
  548.  
  549. $i++; 
  550.  
  551.  
  552. //Code for handling subscriptions meta data 
  553. if ( in_array( 'woocommerce-subscriptions/woocommerce-subscriptions.php', apply_filters( 'active_plugins', get_option( 'active_plugins' ) ) ) ) { 
  554.  
  555. $sub_ids = $sub_item_ids = $sub_details = array(); 
  556.  
  557. $ps_cond = " ('trash', 'auto-draft', 'draft') "; 
  558.  
  559. if( !empty($params['trash']) ) { 
  560. $ps_cond = " ('auto-draft', 'draft') "; 
  561.  
  562. //query to get subscriptions and renewal subscription ids 
  563. $query = "SELECT id as sub_id 
  564. FROM {$wpdb->prefix}posts 
  565. WHERE post_type = 'shop_subscription' 
  566. AND post_parent IN (". implode(", ", $order_ids) .") 
  567. AND post_status NOT IN $ps_cond 
  568. AND post_date_gmt != '0000-00-00 00:00:00' 
  569. GROUP BY id 
  570. UNION 
  571. SELECT pm.meta_value as sub_id 
  572. FROM {$wpdb->prefix}postmeta AS pm 
  573. JOIN {$wpdb->prefix}posts AS p ON (pm.post_id = p.id  
  574. AND p.post_type = 'shop_order' 
  575. AND p.post_parent = 0) 
  576. WHERE pm.meta_key IN ('_subscription_renewal', '_subscription_switch') 
  577. AND pm.post_id IN (". implode(", ", $order_ids) .") 
  578. AND p.post_status NOT IN $ps_cond 
  579. AND p.post_date_gmt != '0000-00-00 00:00:00' 
  580. GROUP BY pm.meta_value, pm.post_id"; 
  581. $results = $wpdb->get_results( $query, 'ARRAY_A' ); 
  582.  
  583. if ( count($results) > 0 ) { 
  584. foreach ($results as $result) { 
  585. // storing the distinct subscription ids 
  586. if ( array_search($result['sub_id'], $sub_ids) === false ) { 
  587. $sub_ids[] = $result['sub_id'];  
  588.  
  589. if (!empty($sub_ids)) {  
  590. //query to get sub data 
  591. $query = "SELECT id as sub_id,  
  592. post_parent as order_id,  
  593. date_format(post_date_gmt, '%Y-%m-%d %T') AS date,  
  594. date_format(post_modified_gmt, '%Y-%m-%d %T') AS modified_date,  
  595. post_status as status 
  596. FROM {$wpdb->prefix}posts 
  597. WHERE post_type = 'shop_subscription' 
  598. AND id IN (". implode(", ", $sub_ids) .") 
  599. AND post_status NOT IN $ps_cond 
  600. AND post_date_gmt != '0000-00-00 00:00:00' 
  601. GROUP BY id"; 
  602. $results = $wpdb->get_results( $query, 'ARRAY_A' ); 
  603.  
  604.  
  605. if ( count($results) > 0 ) { 
  606. foreach ($results as $result) { 
  607. if (!empty($item_details[$result['order_id']]['subscriptions'])) { 
  608. $item_details[$result['order_id']]['subscriptions'] = array(); 
  609.  
  610. if( !isset($sub_details[$result['sub_id']]) && empty($params['order_id']) ) { 
  611. $sub_details[$result['sub_id']] = array('post_created_gmt' => $result['date'],  
  612. 'post_modified_gmt' => $result['modified_date'],  
  613. 'post_status' => $this->get_sub_status($result['status']),  
  614. 'post_parent' => $result['order_id']); 
  615.  
  616. //Code for handling multiple subscriptions in single order 
  617. if( !empty($sub_order_ids[$result['order_id']]) && !is_array($sub_order_ids[$result['order_id']]) ) { 
  618. $temp = $sub_order_ids[$result['order_id']]; 
  619. $sub_order_ids[$result['order_id']] = array(); 
  620. $sub_order_ids[$result['order_id']][0] = $temp; 
  621. $sub_order_ids[$result['order_id']][1] = $result['sub_id']; 
  622. } else if( !empty($sub_order_ids[$result['order_id']]) && is_array($sub_order_ids[$result['order_id']]) ) { 
  623. $sub_order_ids[$result['order_id']][] = $result['sub_id']; 
  624. } else { 
  625. $sub_order_ids[$result['order_id']] = $result['sub_id'];  
  626.  
  627. $item_details[$result['order_id']]['subscriptions'] [$result['sub_id']] = array( 'created_date' => $result['date'],  
  628. 'modified_date' => $result['modified_date'],  
  629. 'status' => $this->get_sub_status($result['status']) ); 
  630. }  
  631.  
  632. //Code for getting the subscription meta data 
  633.  
  634. $query = "SELECT post_id as id,  
  635. meta_value as meta_value,  
  636. meta_key as meta_key  
  637. FROM {$wpdb->prefix}postmeta 
  638. WHERE post_id IN (". implode(", ", $sub_ids) .") 
  639. AND meta_key NOT IN ('_edit_lock', '_edit_last') 
  640. GROUP BY id, meta_key"; 
  641.  
  642. $results = $wpdb->get_results( $query, 'ARRAY_A' ); 
  643.  
  644. if ( count($results) > 0 ) { 
  645.  
  646. foreach ($item_details as $order_id => &$item_detail) { 
  647.  
  648. if ( empty($item_detail['subscriptions']) ) { 
  649. continue; 
  650.  
  651. foreach ($results as $result) { 
  652.  
  653. if (isset($item_detail['subscriptions'][$result['id']])) { 
  654. $item_detail['subscriptions'][$result['id']][$result['meta_key']] = $result['meta_value'];  
  655.  
  656. //code for storing sub meta for 'Recurring Payment' transaction 
  657. if( !empty($sub_details[$result['id']]) && empty($params['order_id']) ) { 
  658. $sub_details[$result['id']][$result['meta_key']] = $result['meta_value']; 
  659.  
  660. }  
  661.  
  662. //Code for creating 'Recurring Payment' transactions for subscriptions 
  663. if ( empty($params['order_id']) ) { //only for data sync 
  664.  
  665. foreach ($sub_ids as $sub_id) { 
  666.  
  667. //Code for creating 'Recurring Payment' 'Created' transaction for subscriptions 
  668.  
  669. $sub_details_og = $sub_details[$sub_id]; 
  670.  
  671. $sub_details[$sub_id]['post_status'] = 'Created'; 
  672. $sub_details[$sub_id]['post_modified_gmt'] = $sub_details[$sub_id]['post_created_gmt'];  
  673.  
  674. $args = array( 'sub_id' => $sub_id,  
  675. 'sub_details' => ((!empty($sub_details[$sub_id])) ? $sub_details[$sub_id] : array()),  
  676. 'sub_meta' => array( 'payment_method' => ((!empty($sub_details[$sub_id]) && !empty($sub_details[$sub_id]['_payment_method_title'])) ? $sub_details[$sub_id]['_payment_method_title'] : ''),  
  677. 'interval' => ((!empty($sub_details[$sub_id]) && !empty($sub_details[$sub_id]['_billing_interval'])) ? $sub_details[$sub_id]['_billing_interval'] : ''),  
  678. 'period' => ((!empty($sub_details[$sub_id]) && !empty($sub_details[$sub_id]['_billing_period'])) ? $sub_details[$sub_id]['_billing_period'] : ''),  
  679. 'trial_end' => ((!empty($sub_details[$sub_id]) && !empty($sub_details[$sub_id]['_schedule_trial_end'])) ? $sub_details[$sub_id]['_schedule_trial_end'] : ''),  
  680. 'sub_end' => ((!empty($sub_details[$sub_id]) && !empty($sub_details[$sub_id]['_schedule_end'])) ? $sub_details[$sub_id]['_schedule_end'] : '') 
  681. )  
  682. ); 
  683.  
  684.  
  685. $params = apply_filters( 'putler_connector_sub_updated', $args ); 
  686.  
  687. if( !empty($params['data']) ) { 
  688. $sub_transactions[] = $params['data'][0]; 
  689.  
  690.  
  691. //Code for creating 'Recurring Payment' current status transaction for subscriptions 
  692.  
  693. $sub_details[$sub_id] = $sub_details_og; 
  694.  
  695. $args = array( 'sub_id' => $sub_id,  
  696. 'new_status' => ((!empty($sub_details[$sub_id]) && !empty($sub_details[$sub_id]['post_status'])) ? $sub_details[$sub_id]['post_status'] : ''),  
  697. 'sub_details' => ((!empty($sub_details[$sub_id])) ? $sub_details[$sub_id] : array()),  
  698. 'sub_meta' => array( 'payment_method' => ((!empty($sub_details[$sub_id]) && !empty($sub_details[$sub_id]['_payment_method_title'])) ? $sub_details[$sub_id]['_payment_method_title'] : ''),  
  699. 'interval' => ((!empty($sub_details[$sub_id]) && !empty($sub_details[$sub_id]['_billing_interval'])) ? $sub_details[$sub_id]['_billing_interval'] : ''),  
  700. 'period' => ((!empty($sub_details[$sub_id]) && !empty($sub_details[$sub_id]['_billing_period'])) ? $sub_details[$sub_id]['_billing_period'] : ''),  
  701. 'trial_end' => ((!empty($sub_details[$sub_id]) && !empty($sub_details[$sub_id]['_schedule_trial_end'])) ? $sub_details[$sub_id]['_schedule_trial_end'] : ''),  
  702. 'sub_end' => ((!empty($sub_details[$sub_id]) && !empty($sub_details[$sub_id]['_schedule_end'])) ? $sub_details[$sub_id]['_schedule_end'] : '') 
  703. )  
  704. ); 
  705.  
  706. $params = apply_filters( 'putler_connector_sub_updated', $args ); 
  707.  
  708. if( !empty($params['data']) ) { 
  709. $sub_transactions[] = $params['data'][0]; 
  710.  
  711.  
  712. // query to get the subscription order item ids 
  713. if (!empty($sub_order_ids)) { 
  714.  
  715. $sub_pids = $sub_prod_ids = array(); 
  716.  
  717. $query = "SELECT oi.order_id as id,  
  718. GROUP_CONCAT(oim.meta_key order by oim.meta_id SEPARATOR ' #wpc# ') AS meta_key,  
  719. GROUP_CONCAT(oim.meta_value order by oim.meta_id SEPARATOR ' #wpc# ') AS meta_value 
  720. FROM {$wpdb->prefix}woocommerce_order_items AS oi 
  721. JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS oim  
  722. ON (oim.order_item_id = oi.order_item_id 
  723. AND oi.order_item_type = 'line_item') 
  724. WHERE oi.order_id IN (". implode(", ", $sub_ids) .") 
  725. AND oim.meta_key IN ('_product_id', '_variation_id') 
  726. GROUP BY id"; 
  727.  
  728. $results = $wpdb->get_results( $query, 'ARRAY_A' ); 
  729.  
  730. if( count($results) > 0 ) { 
  731. foreach( $results as $result ) { 
  732. $meta_values = explode(' #wpc# ', $result['meta_value']); 
  733. $meta_key = explode(' #wpc# ', $result['meta_key']); 
  734.  
  735. if (count($meta_values) != count($meta_key)) 
  736. continue; 
  737.  
  738. $temp = array_combine($meta_key, $meta_values); 
  739.  
  740. $sub_pids[] = (!empty($temp['_variation_id'])) ? $temp['_variation_id'] : $temp['_product_id']; 
  741. $sub_prod_ids[$result['id']] = (!empty($temp['_variation_id'])) ? $temp['_variation_id'] : $temp['_product_id']; 
  742.  
  743. // query to get the order item ids for subscription orders 
  744. $query = "SELECT oi.order_id AS id,  
  745. oi.order_item_id AS item_id,  
  746. oim.meta_value AS pid 
  747. FROM {$wpdb->prefix}woocommerce_order_items AS oi 
  748. JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS oim  
  749. ON (oim.order_item_id = oi.order_item_id 
  750. AND oi.order_item_type = 'line_item') 
  751. WHERE oi.order_id IN (". implode(", ", array_keys($sub_order_ids)) .") 
  752. AND oim.meta_value IN (". implode(", ", $sub_pids) .") 
  753. AND oim.meta_key IN ('_product_id', '_variation_id')"; 
  754.  
  755. $results = $wpdb->get_results( $query, 'ARRAY_A' ); 
  756.  
  757. if( count($results) > 0 ) { 
  758. foreach( $results as $result ) { 
  759.  
  760. if( empty($sub_order_ids[$result['id']]) ) { 
  761. continue; 
  762.  
  763. if( is_array($sub_order_ids[$result['id']]) ) { 
  764. foreach ($sub_order_ids[$result['id']] as $sub_id) { 
  765.  
  766. if( empty($sub_prod_ids[$sub_id]) ) { 
  767. continue; 
  768.  
  769. if( $sub_prod_ids[$sub_id] == $result['pid'] ) { 
  770. $sub_item_ids[$result['item_id']] = $sub_id; 
  771. break; 
  772. } else { 
  773. $sub_item_ids[$result['item_id']] = $sub_order_ids[$result['id']]; 
  774.  
  775. // AND meta_key IN ('_billing_first_name' , '_billing_last_name' , '_billing_email',  
  776. // '_shipping_first_name', '_shipping_last_name', '_billing_address_1', '_billing_address_2',  
  777. // '_billing_city', '_billing_state', '_billing_country', '_billing_postcode',  
  778. // '_shipping_method', '_payment_method', '_order_items', '_order_total',  
  779. // '_shipping_method_title', '_payment_method_title', '_customer_user', '_billing_phone',  
  780. // '_order_shipping', '_order_discount', '_cart_discount', '_order_tax', '_order_shipping_tax', '_order_currency', 'coupons') 
  781.  
  782. $sub_renewal_id = $sub_parent_id = $sub_meta = array(); 
  783.  
  784. if( !empty($sub_item_ids) ) { 
  785.  
  786. //Code for generating subscription meta 
  787. $sub_meta_keys = array(); 
  788.  
  789. $sub_meta_keys['_payment_method_title'] = 'payment_method'; 
  790. $sub_meta_keys['_billing_interval'] = 'interval'; 
  791. $sub_meta_keys['_billing_period'] = 'period'; 
  792. $sub_meta_keys['_schedule_trial_end'] = 'trial_end'; 
  793. $sub_meta_keys['_schedule_end'] = 'sub_end'; 
  794.  
  795. //query to get the sub meta details 
  796. $query = "SELECT post_id,  
  797. meta_key,  
  798. meta_value 
  799. FROM {$wpdb->prefix}postmeta 
  800. WHERE post_id IN ( ".implode(', ', $sub_item_ids)." ) 
  801. AND meta_key IN ('_payment_method_title', '_billing_interval', '_billing_period', '_schedule_trial_end', '_schedule_end')  
  802. GROUP BY post_id, meta_key"; 
  803. $results = $wpdb->get_results($query, 'ARRAY_A'); 
  804.  
  805.  
  806.  
  807.  
  808.  
  809. if( count($results) > 0 ) { 
  810. foreach ($results as $result) { 
  811. if( !empty($result['meta_key']) && !empty($sub_meta_keys[$result['meta_key']]) ) { 
  812.  
  813. if( empty($sub_meta[$result['post_id']]) ) { 
  814. $sub_meta[$result['post_id']] = array(); 
  815.  
  816. $sub_meta[$result['post_id']][$sub_meta_keys[$result['meta_key']]] = $result['meta_value']; 
  817.  
  818. if( !isset($sub_meta[$result['post_id']]['status']) ) { 
  819. $sub_meta[$result['post_id']]['status'] = $this->get_sub_status(get_post_status($result['post_id'])); 
  820. }  
  821.  
  822.  
  823. //code to get sub meta for switch subscriptions 
  824. $query = "SELECT pm.meta_value AS sub_id,  
  825. MAX(pm.post_id) AS order_id 
  826. FROM {$wpdb->prefix}postmeta AS pm 
  827. JOIN {$wpdb->prefix}posts AS p ON (p.id = pm.post_id  
  828. AND p.post_type = 'shop_order'  
  829. AND pm.meta_key = '_subscription_switch') 
  830. WHERE pm.meta_value IN ( ".implode(', ', $sub_item_ids)." ) 
  831. GROUP BY sub_id"; 
  832. $results = $wpdb->get_results($query, ARRAY_A); 
  833.  
  834.  
  835.  
  836.  
  837.  
  838. if( count($results) > 0 ) { 
  839. foreach ($results as $result) { 
  840. $sub_renewal_id[ $result['sub_id'] ] = $result['order_id']; 
  841.  
  842.  
  843.  
  844. //code to get the parent ids for switch subscriptions 
  845. $query = "SELECT id AS sub_id,  
  846. post_parent AS order_id 
  847. FROM {$wpdb->prefix}posts 
  848. WHERE post_type = 'shop_subscription' 
  849. AND id IN ( ".implode(', ', $sub_item_ids)." ) 
  850. GROUP BY sub_id"; 
  851. $results = $wpdb->get_results($query, ARRAY_A); 
  852.  
  853.  
  854.  
  855.  
  856.  
  857. if( count($results) > 0 ) { 
  858. foreach ($results as $result) { 
  859. $sub_parent_id[ $result['sub_id'] ] = $result['order_id']; 
  860.  
  861.  
  862.  
  863. //Query to get the Order Details 
  864. $query_order_item_details = "SELECT post_id as id,  
  865. meta_value as meta_value,  
  866. meta_key as meta_key  
  867. FROM {$wpdb->prefix}postmeta 
  868. WHERE post_id IN (". implode(", ", $order_ids) .") 
  869. AND meta_key NOT IN ('_edit_lock', '_edit_last') 
  870. GROUP BY id, meta_key"; 
  871.  
  872. $results_order_item_details = $wpdb->get_results( $query_order_item_details, 'ARRAY_A' ); 
  873. $results_order_item_details_count = $wpdb->num_rows; 
  874.  
  875. if( $results_order_item_details_count > 0 ) { 
  876.  
  877. $order_items = array(); 
  878. // Structuring the order items 
  879. foreach( $results_order_item_details as $detail ) { 
  880.  
  881. if( !isset( $order_items[$detail['id']] ) ) { 
  882. $order_items[$detail['id']] = array(); 
  883.  
  884. $order_items[$detail['id']][$detail['meta_key']] = $detail['meta_value']; 
  885.  
  886.  
  887. //Code for Data Mapping as per Putler 
  888. foreach( $results_order_details as $order_detail ) { 
  889.  
  890. $sub_trans = false; //flag for handling subscription transactions 
  891.  
  892. $order_id = (!empty($params['refund_parent_id'])) ? $params['refund_parent_id'] : (($order_detail['type'] == 'shop_order_refund') ? $man_refund_ids[$order_detail['id']] : $order_detail['id']); 
  893. $order_total = (!empty($params['refund_parent_id']) || $order_detail['type'] == 'shop_order_refund' ) ? round ( get_post_meta( $order_detail['id'], '_refund_amount', true ), 2 ) : round ( $order_items[$order_id]['_order_total'], 2 ); 
  894. $date_gmt = $order_detail['date']; 
  895. $dateInGMT = date('m/d/Y', (int)strtotime($date_gmt)); 
  896. $timeInGMT = date('H:i:s', (int)strtotime($date_gmt)); 
  897.  
  898. if (defined('SM_IS_WOO22') && SM_IS_WOO22 == 'true') { 
  899. $order_status_new = (!empty($order_detail['order_status'])) ? substr($order_detail['order_status'], 3) : ''; 
  900. } else { 
  901. $order_status_new = (!empty($order_status[$order_detail['term_taxonomy_id']])) ? $order_status[$order_detail['term_taxonomy_id']] : ''; 
  902.  
  903. $verbose_status = ''; 
  904. if ($order_status_new== "on-hold" || $order_status_new== "pending" || $order_status_new== "failed") { 
  905. $order_status_display = ($order_status_new != "failed") ? 'Pending' : 'Failed'; 
  906. $verbose_status = ($order_status_new != "pending") ? $order_status_new : ''; 
  907. } else if ($order_status_new== "completed" || $order_status_new== "processing") { 
  908. $order_status_display = 'Completed'; 
  909. $verbose_status = ($order_status_new != "completed") ? $order_status_new : ''; 
  910. } else if ($order_status_new== "refunded") { 
  911. $order_status_display = 'Refunded'; 
  912. } else if ($order_status_new== "cancelled") { 
  913. $order_status_display = 'Cancelled'; 
  914. } else { 
  915. if (defined('SM_IS_WOO22') && SM_IS_WOO22 == 'true') { //for handling any custom order statuses 
  916. $order_status_display = (!empty($order_detail['order_status']) && !empty($wc_order_status[$order_detail['order_status']])) ? $wc_order_status[$order_detail['order_status']] : ''; 
  917.  
  918. if( !empty( $order_detail['order_status'] ) && $order_detail['order_status'] == 'trash' ) { 
  919. $order_status_display = 'Delete'; 
  920. $verbose_status = $order_detail['order_status']; 
  921.  
  922. } else { 
  923. $order_status_display = $order_status_new; 
  924.  
  925. if( !empty($params['trash']) ) { 
  926. $order_status_display = 'Delete'; 
  927.  
  928. // $response['date_time'] = $date_gmt; 
  929. $response ['Date'] = $dateInGMT; 
  930. $response ['Time'] = $timeInGMT; 
  931. $response ['Time_Zone'] = 'GMT'; 
  932.  
  933. $response ['Source'] = $this->name; 
  934. $response ['Name'] = $order_items[$order_id]['_billing_first_name'] . ' ' . $order_items[$order_id]['_billing_last_name']; 
  935. // $response ['Type'] = ( $status == "refunded") ? 'Refund' : 'Shopping Cart Payment Received'; 
  936. $response ['Type'] = 'Shopping Cart Payment Received'; 
  937. $response ['Status'] = ucfirst( $order_status_display ); 
  938. $response ['Currency'] = $order_items[$order_id]['_order_currency']; 
  939. $response ['Gross'] = $order_total; 
  940. $response ['Fee'] = 0.00; 
  941. $response ['Net'] = $order_total; 
  942. $response ['From_Email_Address'] = $order_items[$order_id]['_billing_email']; 
  943. $response ['To_Email_Address'] = ''; 
  944. $response ['Transaction_ID'] = $order_id; 
  945. $response ['Counterparty_Status'] = ''; 
  946. $response ['Address_Status'] = ''; 
  947. $response ['Item_Title'] = 'Shopping Cart'; 
  948. $response ['Item_ID'] = 0; // Set to 0 for main Order Transaction row 
  949. $response ['Shipping_and_Handling_Amount'] = ( isset( $order_items[$order_id]['_order_shipping'] ) && (empty($params['refund_parent_id']) && $order_detail['type'] != 'shop_order_refund') ) ? round ( $order_items[$order_id]['_order_shipping'], 2 ) : 0.00; 
  950. $response ['Insurance_Amount'] = ''; 
  951. $response ['Discount'] = ( isset( $order_items[$order_id]['_order_discount'] ) && (empty($params['refund_parent_id']) && $order_detail['type'] != 'shop_order_refund') ) ? round ( $order_items[$order_id]['_order_discount'], 2 ) : 0.00; 
  952. $response ['Discount'] += ( isset( $order_items[$order_id]['_cart_discount'] ) && (empty($params['refund_parent_id']) && $order_detail['type'] != 'shop_order_refund') ) ? round ( $order_items[$order_id]['_cart_discount'], 2 ) : 0.00; 
  953. $response ['Sales_Tax'] = ( isset( $order_items[$order_id]['_order_tax'] ) && (empty($params['refund_parent_id']) && $order_detail['type'] != 'shop_order_refund' ) ) ? round ( $order_items[$order_id]['_order_tax'], 2 ) : 0.00; 
  954. $response ['Option_1_Name'] = ''; 
  955. $response ['Option_1_Value'] = ''; 
  956. $response ['Option_2_Name'] = ''; 
  957. $response ['Option_2_Value'] = ''; 
  958. $response ['Auction_Site'] = ($verbose_status != '') ? $verbose_status : ''; 
  959. $response ['Buyer_ID'] = ''; 
  960. $response ['Item_URL'] = ''; 
  961. $response ['Closing_Date'] = ''; 
  962. $response ['Escrow_ID'] = ''; 
  963. $response ['Invoice_ID'] = ''; 
  964. $response ['Reference_Txn_ID'] = ''; 
  965. $response ['Invoice_Number'] = ''; 
  966. $response ['Custom_Number'] = ''; 
  967. $response ['Quantity'] = (!empty($item_details[$order_id]['tot_qty'])) ? $item_details[$order_id]['tot_qty'] : '1'; 
  968. $response ['Receipt_ID'] = ''; 
  969. $response ['Balance'] = ''; 
  970. $response ['Note'] = $order_detail['order_note']; 
  971. $response ['Address_Line_1'] = ( !empty( $order_items[$order_id]['_billing_address_1'] ) ) ? $order_items[$order_id]['_billing_address_1'] : ''; 
  972. $response ['Address_Line_2'] = !empty( $order_items[$order_id]['_billing_address_2'] ) ? $order_items[$order_id]['_billing_address_2'] : ''; 
  973. $response ['Town_City'] = !empty( $order_items[$order_id]['_billing_city'] ) ? $order_items[$order_id]['_billing_city'] : '' ; 
  974. $response ['State_Province'] = $order_items[$order_id]['_billing_state']; 
  975. $response ['Zip_Postal_Code'] = !empty( $order_items[$order_id]['_billing_postcode'] ) ? $order_items[$order_id]['_billing_postcode'] : ''; 
  976. $response ['Country'] = !empty( $order_items[$order_id]['_billing_country'] ) ? $order_items[$order_id]['_billing_country'] : ''; 
  977. $response ['Contact_Phone_Number'] = !empty( $order_items[$order_id]['_billing_phone']) ? $order_items[$order_id]['_billing_phone'] : ''; 
  978. $response ['Subscription_ID'] = ''; 
  979.  
  980. // coupon data 
  981. if ( !empty($item_details[$order_id]['coupons']) ) { 
  982. foreach ($item_details[$order_id]['coupons'] as $key => &$value) { 
  983. $coupon_data[$key]['amt'] = ( !empty($value['amt']) ) ? round ( $value['amt'], 2 ) : 0.00; 
  984. $value = (!empty($coupon_data[$key])) ? $coupon_data[$key] : $value; 
  985.  
  986. $order_items[$order_id]['coupons'] = ( !empty( $item_details[$order_id]['coupons'] ) ) ? $item_details[$order_id]['coupons'] : array(); 
  987. $order_items[$order_id]['_shipping_method_title'] = ( !empty( $item_details[$order_id]['_shipping_method_title'] ) ) ? $item_details[$order_id]['_shipping_method_title'] : '-'; 
  988.  
  989. $order_items[$order_id]['subscriptions'] = array(); 
  990.  
  991. if( !empty( $item_details[$order_id]['subscriptions'] ) ) { 
  992. $order_items[$order_id]['subscriptions'] = $item_details[$order_id]['subscriptions']; 
  993.  
  994. if( !empty($sub_item_ids) ) { 
  995.  
  996. $sub = $prod = 0; 
  997. $order_sub_ids = array(); 
  998.  
  999. foreach( $item_details[$order_id]['cart_items'] as $key => $value ) { 
  1000. if( !empty($sub_item_ids[$key]) ) { 
  1001.  
  1002. //For handling renewal orders in subscription meta 
  1003. if( !empty($order_items[$order_id]['_subscription_renewal']) ) { 
  1004. $sub_meta[$sub_item_ids[$key]]['is_renewal'] = 1; 
  1005. } else { 
  1006. if( isset($sub_meta[$sub_item_ids[$key]]['is_renewal']) ) { 
  1007. unset($sub_meta[$sub_item_ids[$key]]['is_renewal']); 
  1008.  
  1009. //For handling switched subscription orders in subscription meta 
  1010. if( !empty($order_items[$order_id]['_subscription_switch']) ) { 
  1011. $sub_meta[$sub_item_ids[$key]]['sub_switch'] = ( !empty($sub_renewal_id[$sub_item_ids[$key]]) ) ? $sub_renewal_id[$sub_item_ids[$key]] : ''; 
  1012. $sub_meta[$sub_item_ids[$key]]['sub_switch'] = ( empty( $sub_meta[$sub_item_ids[$key]]['sub_switch'] ) && !empty($sub_parent_id[$sub_item_ids[$key]]) ) ? $sub_parent_id[$sub_item_ids[$key]] : $sub_meta[$sub_item_ids[$key]]['sub_switch']; 
  1013. } else { 
  1014. if( isset($sub_meta[$sub_item_ids[$key]]['sub_switch']) ) { 
  1015. unset($sub_meta[$sub_item_ids[$key]]['sub_switch']); 
  1016.  
  1017. $sub++; 
  1018. } else { 
  1019. $prod++; 
  1020.  
  1021.  
  1022.  
  1023. //code for getting the subscription meta data [subscription reports] 
  1024. if( !empty($order_sub_ids) ) { 
  1025.  
  1026.  
  1027.  
  1028.  
  1029. //For handling switched subscription orders 
  1030. if( !empty($order_items[$order_id]['_subscription_switch']) ) { 
  1031.  
  1032.  
  1033.  
  1034. $query = $wpdb->prepare("SELECT MAX(order_id)  
  1035. FROM {$wpdb->prefix}wsr_orders 
  1036. WHERE sub_id = %d 
  1037. AND order_id < %d", $sub_details[0]['sub_id'], $order->id); 
  1038. $switch = $wpdb->get_var($query); 
  1039.  
  1040.  
  1041.  
  1042. //code for handling different subscription order conditions 
  1043. if( $sub == 1 && $prod == 0 ) { 
  1044. $response ['Type'] = 'Subscription Payment Received'; 
  1045. $sub_id = array_keys($item_details[$order_id]['subscriptions']); 
  1046. $response ['Subscription_ID'] = (!empty($sub_id)) ? $sub_id[0] : ''; 
  1047. $sub_trans = true; 
  1048. } else if( ($sub > 1 && $prod == 0) || ($sub >= 1 && $prod > 0) ) { 
  1049. $response ['Type'] = 'Subscription Shopping Cart Payment Received'; 
  1050.  
  1051. if( empty($params['trash']) ) { 
  1052. $response ['Raw_Data'] = json_encode($order_items[$order_id]);  
  1053. } else { 
  1054. $response ['Raw_Data'] = ''; 
  1055.  
  1056. //Payment Title & Transaction id -- added from v2.6 
  1057. if( $order_status_new != "refunded" && $order_detail['type'] != 'shop_order_refund' ) { 
  1058. $response ['Payment_Source'] = !empty( $order_items[$order_id]['_payment_method_title']) ? $order_items[$order_id]['_payment_method_title'] : ''; 
  1059. $response ['External_Trans_ID'] = !empty( $order_items[$order_id]['_transaction_id']) ? $order_items[$order_id]['_transaction_id'] : ''; 
  1060.  
  1061. //for older woo versions > 2.0 
  1062. $response ['External_Trans_ID'] = ( empty($response ['External_Trans_ID']) && !empty( $order_items[$order_id]['Transaction ID'] ) ) ? $order_items[$order_id]['Transaction ID'] : $response ['External_Trans_ID']; 
  1063.  
  1064. //trans id for 2Checkout 
  1065. $response ['External_Trans_ID'] = ( !empty( $response ['Payment_Source'] ) && ( $response ['Payment_Source'] == "2Checkout" ) && !empty( $order_items[$order_id]['two_checkout_sale_id']) ) ? $order_items[$order_id]['two_checkout_sale_id'] : $response ['External_Trans_ID'];  
  1066. } else { 
  1067. $response ['Payment_Source'] = $response ['External_Trans_ID'] = ''; 
  1068.  
  1069. //customer ip_address 
  1070. $response ['IP_Address'] = !empty( $order_items[$order_id]['_customer_ip_address']) ? $order_items[$order_id]['_customer_ip_address'] : ''; 
  1071.  
  1072. $response ['Sub_Meta'] = json_encode(json_decode ("{}")); //for subscription meta 
  1073.  
  1074. $cart_items = (!empty($item_details[$order_id]['cart_items'])) ? $item_details[$order_id]['cart_items'] : array(); 
  1075. $sub = (!empty($item_details[$order_id]['subscriptions'])) ? $item_details[$order_id]['subscriptions'] : array(); 
  1076.  
  1077. if( (!empty($params['order_id']) && ($order_status_new == "refunded" || !empty($params ['refund_parent_id'])) ) ||  
  1078. (empty($params['order_id']) && $order_detail['type'] == 'shop_order_refund') ) { 
  1079.  
  1080. $refund_id = ''; //for handling manual refunds 
  1081.  
  1082. if( !empty($params ['refund_parent_id']) || $order_detail['type'] == 'shop_order_refund' ) { 
  1083. $refund_id = (!empty($params ['refund_parent_id'])) ? $params['order_id'] : $order_detail['id']; 
  1084.  
  1085. $date_gmt_modified = $order_detail['modified_date']; 
  1086.  
  1087. $response ['Date'] = date('m/d/Y', (int)strtotime($date_gmt_modified)); 
  1088. $response ['Time'] = date('H:i:s', (int)strtotime($date_gmt_modified)); 
  1089.  
  1090. $response ['Type'] = 'Refund'; 
  1091. $response ['Status'] = 'Completed'; 
  1092. $response ['Auction_Site'] = ''; 
  1093. $response ['Item_Title'] = 'Unknown'; 
  1094.  
  1095. // $response ['Gross'] = (!empty($params ['refund_parent_id']) || $order_detail['type'] == 'shop_order_refund') ? $order_total : -$order_total; 
  1096. // $response ['Net'] = (!empty($params ['refund_parent_id']) || $order_detail['type'] == 'shop_order_refund') ? $order_total : -$order_total; 
  1097.  
  1098. $response ['Gross'] = -$order_total; //for handling partial & full refunds 
  1099. $response ['Net'] = -$order_total; 
  1100.  
  1101. $response ['Transaction_ID'] = ((!empty($params ['refund_parent_id']) || $order_detail['type'] == 'shop_order_refund') && !empty($refund_id)) ? $order_id .'_R_'. $refund_id : $order_id . '_R'; 
  1102. $response ['Reference_Txn_ID'] = $order_id; 
  1103. $response ['Raw_Data'] = ''; 
  1104.  
  1105. if( empty($cart_items) || count($cart_items) != 1 ) { 
  1106. $transactions [] = $response; 
  1107. continue;  
  1108.  
  1109. } else if( $sub_trans === false && empty($params['order_id']) && $order_status_new == "refunded" ) { //For sending parent transactions for refunded orders during data sync 
  1110. $transactions [] = $response; 
  1111.  
  1112. if( $sub_trans === false && ($order_status_new != "refunded" && empty($params ['refund_parent_id']) && $order_detail['type'] != 'shop_order_refund') ) { //for handling orders not having only 1 subscription 
  1113. $transactions [] = $response; 
  1114.  
  1115. //Code for line items 
  1116. foreach( $cart_items as $item_id => $cart_item ) { 
  1117.  
  1118. $order_item = array(); 
  1119. $order_item ['Type'] = ($sub_trans === false && $response ['Type'] != 'Refund') ? 'Shopping Cart Item' : $response ['Type']; 
  1120. $order_item ['Item_Title'] = $cart_item['product_name']; 
  1121.  
  1122. if ( !empty($cart_item['_variation_id']) ) { 
  1123. $order_item ['Item_ID'] = (isset( $products_sku[$cart_item['_variation_id']] )) ? $products_sku[$cart_item['_variation_id']] : $cart_item['_variation_id']; 
  1124. $product_id = $cart_item['_variation_id']; 
  1125. } else { 
  1126. $order_item ['Item_ID'] = (isset( $products_sku[$cart_item['_product_id']] )) ? $products_sku[$cart_item['_product_id']] : $cart_item['_product_id']; 
  1127. $product_id = $cart_item['_product_id']; 
  1128.  
  1129. $order_item ['Gross'] = ($response ['Type'] != 'Refund') ? round ( $cart_item['_line_total'], 2 ) : $response ['Gross']; 
  1130. $order_item ['Quantity'] = $cart_item['_qty']; 
  1131.  
  1132. //code to handle special case where order_total & Discount both are zero make the line_total = 0 
  1133. if( $response ['Discount'] == 0.00 && $response ['Gross'] == 0.00 ) { 
  1134. $order_item ['Gross'] = 0.00;  
  1135.  
  1136. if ( isset($variations[$product_id]) ) { 
  1137. if ( $variations[$product_id][0]['option1_name'] != 'attributes' ) { 
  1138. $order_item ['Option_1_Name'] = (!empty($variations[$product_id][0]['option1_name'])) ? $variations[$product_id][0]['option1_name'] : ''; 
  1139. $order_item ['Option_1_Value'] = (!empty($variations[$product_id][0]['option1_value'])) ? $variations[$product_id][0]['option1_value'] : ''; 
  1140. $order_item ['Option_2_Name'] = (!empty($variations[$product_id][1]['option1_name'])) ? $variations[$product_id][1]['option1_name'] : ''; 
  1141. $order_item ['Option_2_Value'] = (!empty($variations[$product_id][1]['option1_value'])) ? $variations[$product_id][1]['option1_value'] : ''; 
  1142. } else { 
  1143. $order_item ['Option_1_Name'] = (!empty($variations[$product_id][0]['option1_name'])) ? $variations[$product_id][0]['option1_name'] : ''; 
  1144. $order_item ['Option_1_Value'] = (!empty($variations[$product_id][0]['option1_value'])) ? $variations[$product_id][0]['option1_value'] : ''; 
  1145. }  
  1146.  
  1147. $order_item ['Subscription_ID'] = (!empty($sub_item_ids[$item_id])) ? $sub_item_ids[$item_id] : ''; //Assigning the subscription id 
  1148.  
  1149. if( empty($params['trash']) ) { 
  1150. $response ['Raw_Data'] = ($sub_trans === false || $response ['Type'] == 'Refund') ? '' : $response ['Raw_Data']; 
  1151. } else { 
  1152. $response ['Raw_Data'] = ''; 
  1153.  
  1154. if( !empty($sub_item_ids[$item_id]) && empty($params['trash']) ) { //for handling subscription meta 
  1155. $response ['Sub_Meta'] = json_encode( ( !empty($sub_meta[$sub_item_ids[$item_id]]) ) ? $sub_meta[$sub_item_ids[$item_id]] : json_decode ("{}")); 
  1156. } else { 
  1157. $response ['Sub_Meta'] = json_encode(json_decode ("{}")); 
  1158.  
  1159. $response = array_merge ( $response, $order_item ); 
  1160.  
  1161. $transactions [] = $response; 
  1162.  
  1163. if ( empty($params['order_id']) ) { 
  1164. $order_count = (is_array($results_order_details)) ? count($results_order_details) : 0 ; 
  1165. $data = (!empty($transactions)) ? $transactions : array(); 
  1166. $data = (!empty($sub_transactions)) ? array_merge($data, $sub_transactions) : $data; 
  1167. $params[ $this->name ] = array('count' => $order_count, 'last_start_limit' => $start_limit, 'data' => $data ); 
  1168. } else { 
  1169. $params['data'] = $transactions; 
  1170. return $params; 
.