API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Sep 21st, 2017
56
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 11.38 KB | None | 0 0
raw download clone embed print report
  1. diff --git a/app/functions/fn.sales_reports.php b/app/functions/fn.sales_reports.php
  2. index 8ee3b762af..ae65909a44 100644
  3. --- a/app/functions/fn.sales_reports.php
  4. +++ b/app/functions/fn.sales_reports.php
  5. @@ -802,13 +802,15 @@ function fn_get_report_statistics(&$table)
  6. $time_start = $first_elm['time_from'];
  7. $time_end = $last_elm['time_to'];
  8. $new_data = array();
  9. + $data = array();
  10.  
  11. foreach ($table['elements'] as $element) {
  12. + $hash = $element['element_hash'];
  13.  
  14. - $a = $element['element_hash'];
  15. if (empty($element['auto_generated'])) {
  16. $element['request'] = fn_get_parameter_request($table['table_id'], $element['element_hash']);
  17. }
  18. +
  19. $time_condition = db_quote(" timestamp BETWEEN ?i AND ?i", $time_start, $time_end);
  20. $group_condition = ' GROUP BY `interval`';
  21.  
  22. @@ -821,7 +823,7 @@ function fn_get_report_statistics(&$table)
  23. } elseif ($interval_code == 'day') {
  24. $add_field = db_quote(", DATE_FORMAT(FROM_UNIXTIME(timestamp), '%Y-%m-%d') as `interval`, timestamp");
  25. } else {
  26. - $add_field = db_quote(", 1 as `interval`, timestamp");
  27. + $add_field = db_quote(", 1 as `interval`, `timestamp`");
  28. $group_condition = '';
  29. }
  30.  
  31. @@ -829,63 +831,62 @@ function fn_get_report_statistics(&$table)
  32. $fields = !empty($element['fields']) ? $element['fields'] : 'SUM(total)';
  33. $tables = !empty($element['tables']) ? $element['tables'] : '?:orders';
  34.  
  35. - $data[$a] = db_get_hash_array("SELECT $fields as total $add_field FROM $tables WHERE $element[request] AND $time_condition $order_ids $group_condition", 'interval');
  36. + $data[$hash] = db_get_hash_array("SELECT $fields as total $add_field FROM $tables WHERE $element[request] AND $time_condition $order_ids $group_condition", 'interval');
  37. } elseif ($table['display'] == 'order_number') {
  38. - $data[$a] = db_get_hash_array("SELECT COUNT(total) as total $add_field FROM ?:orders WHERE $element[request] AND $time_condition $order_ids $group_condition", 'interval');
  39. + $data[$hash] = db_get_hash_array("SELECT COUNT(total) as total $add_field FROM ?:orders WHERE $element[request] AND $time_condition $order_ids $group_condition", 'interval');
  40. } elseif ($table['display'] == 'shipping') {
  41. - $data[$a] = db_get_hash_array("SELECT SUM(shipping_cost) as total $add_field FROM ?:orders WHERE $element[request] AND $time_condition $order_ids $group_condition", 'interval');
  42. + $data[$hash] = db_get_hash_array("SELECT SUM(shipping_cost) as total $add_field FROM ?:orders WHERE $element[request] AND $time_condition $order_ids $group_condition", 'interval');
  43. } elseif ($table['display'] == 'discount') {
  44. - $data[$a] = db_get_hash_array("SELECT SUM(subtotal_discount) as total, ?:order_details.extra $add_field FROM ?:order_details LEFT JOIN ?:orders ON ?:orders.order_id = ?:order_details.order_id WHERE $element[request] AND $time_condition $order_ids $group_condition", 'interval');
  45. + $where = db_quote(
  46. + '?p AND ?p ?p ?p',
  47. + $element['request'],
  48. + $time_condition,
  49. + $order_ids,
  50. + $group_condition
  51. + );
  52.  
  53. - foreach ($data[$a] as $int => $interval_data) {
  54. - $extra = @unserialize($interval_data['extra']);
  55. - if (!empty($extra['discount'])) {
  56. - $data[$a][$int]['total'] += $extra['discount'];
  57. - }
  58. - unset($interval_data['extra']);
  59. - $data[$a][$int]['total'] = fn_format_price($data[$a][$int]['total']);
  60. - }
  61. + $data = fn_sales_reports_get_orders_subtotal_discount($data, $add_field, $where, $hash);
  62.  
  63. } elseif ($table['display'] == 'tax') {
  64. $all_taxes = db_get_hash_array("SELECT ?:order_data.data $add_field FROM ?:order_data LEFT JOIN ?:orders ON ?:orders.order_id = ?:order_data.order_id WHERE ?:order_data.type = 'T' AND $element[request] AND $time_condition $order_ids $group_condition", 'interval');
  65.  
  66. foreach ($all_taxes as $int => $interval_data) {
  67. - $data[$a][$int] = $interval_data;
  68. - $data[$a][$int]['total'] = 0;
  69. + $data[$hash][$int] = $interval_data;
  70. + $data[$hash][$int]['total'] = 0;
  71. $taxes = @unserialize($interval_data['data']);
  72. if (is_array($taxes)) {
  73. foreach ($taxes as $tax_data) {
  74. if (!empty($tax_data['tax_subtotal'])) {
  75. - $data[$a][$int]['total'] += $tax_data['tax_subtotal'];
  76. + $data[$hash][$int]['total'] += $tax_data['tax_subtotal'];
  77. }
  78. }
  79. }
  80. - unset($data[$a][$int]['data']);
  81. - $data[$a][$int]['total'] = fn_format_price($data[$a][$int]['total']);
  82. + unset($data[$hash][$int]['data']);
  83. + $data[$hash][$int]['total'] = fn_format_price($data[$hash][$int]['total']);
  84. }
  85.  
  86. } elseif ($table['display'] == 'product_cost') {
  87. $product_cost = (empty($element['product_ids'])) ? '' : db_quote(" AND ?:order_details.product_id IN (?p)", $element['product_ids']);
  88. - $data[$a] = db_get_hash_array("SELECT SUM(amount * price) as total $add_field FROM ?:order_details LEFT JOIN ?:orders ON ?:orders.order_id = ?:order_details.order_id WHERE $element[request] AND $time_condition $order_ids ?p $group_condition", 'interval', $product_cost);
  89. + $data[$hash] = db_get_hash_array("SELECT SUM(amount * price) as total $add_field FROM ?:order_details LEFT JOIN ?:orders ON ?:orders.order_id = ?:order_details.order_id WHERE $element[request] AND $time_condition $order_ids ?p $group_condition", 'interval', $product_cost);
  90.  
  91. } elseif ($table['display'] == 'product_number') {
  92. $product_count = (empty($element['product_ids'])) ? '' : db_quote(" AND ?:order_details.product_id IN (?p)", $element['product_ids']);
  93. - $data[$a] = db_get_hash_array("SELECT SUM(amount) as total $add_field FROM ?:order_details LEFT JOIN ?:orders ON ?:orders.order_id = ?:order_details.order_id WHERE $element[request] AND $time_condition $order_ids ?p $group_condition", 'interval', $product_count);
  94. + $data[$hash] = db_get_hash_array("SELECT SUM(amount) as total $add_field FROM ?:order_details LEFT JOIN ?:orders ON ?:orders.order_id = ?:order_details.order_id WHERE $element[request] AND $time_condition $order_ids ?p $group_condition", 'interval', $product_count);
  95. }
  96.  
  97. foreach ($table['intervals'] as $interval) {
  98. $b = $interval['interval_id'];
  99. - if (isset($data[$a])) {
  100. - foreach ($data[$a] as $interval_data) {
  101. + if (isset($data[$hash])) {
  102. + foreach ($data[$hash] as $interval_data) {
  103. if ($interval_data['timestamp'] >= $interval['time_from'] && $interval_data['timestamp'] <= $interval['time_to']) {
  104. - $new_data[$a][$b] = $interval_data['total'];
  105. + $new_data[$hash][$b] = $interval_data['total'];
  106. break;
  107. }
  108. }
  109. }
  110.  
  111. - if (!isset($new_data[$a][$b])) {
  112. - $new_data[$a][$b] = 0;
  113. + if (!isset($new_data[$hash][$b])) {
  114. + $new_data[$hash][$b] = 0;
  115. }
  116. }
  117. }
  118. @@ -1320,8 +1321,8 @@ function fn_get_order_totals($table)
  119. $table_totals = array();
  120.  
  121. foreach ($table['elements'] as $element) {
  122. -
  123. $data = array();
  124. +
  125. if (empty($element['auto_generated'])) {
  126. $element['request'] = fn_get_parameter_request($table['table_id'], $element['element_hash']);
  127. }
  128. @@ -1351,16 +1352,15 @@ function fn_get_order_totals($table)
  129. } elseif ($table['display'] == 'shipping') {
  130. $data = db_get_hash_array("SELECT SUM(shipping_cost) as total $add_field FROM ?:orders WHERE {$element['request']} AND $time_condition $order_ids $group_condition", 'interval');
  131. } elseif ($table['display'] == 'discount') {
  132. - $data = db_get_hash_array("SELECT SUM(subtotal_discount) as total, ?:order_details.extra $add_field FROM ?:order_details LEFT JOIN ?:orders ON ?:orders.order_id = ?:order_details.order_id WHERE {$element['request']} AND $time_condition $order_ids $group_condition", 'interval');
  133. + $where = db_quote(
  134. + '?p AND ?p ?p ?p',
  135. + $element['request'],
  136. + $time_condition,
  137. + $order_ids,
  138. + $group_condition
  139. + );
  140.  
  141. - foreach ($data as $int => $interval_data) {
  142. - $extra = @unserialize($interval_data['extra']);
  143. - if (!empty($extra['discount'])) {
  144. - $data[$int]['total'] += $extra['discount'];
  145. - }
  146. - unset($data[$int]['extra']);
  147. - $data[$int]['total'] = fn_format_price($data[$int]['total']);
  148. - }
  149. + $data = fn_sales_reports_get_orders_subtotal_discount($data, $add_field, $where);
  150.  
  151. } elseif ($table['display'] == 'tax') {
  152. $data = db_get_hash_array("SELECT ?:order_data.data $add_field FROM ?:order_data LEFT JOIN ?:orders ON ?:orders.order_id = ?:order_data.order_id WHERE ?:order_data.type = 'T' AND {$element['request']} AND $time_condition $order_ids $group_condition", 'interval');
  153. @@ -1573,3 +1573,55 @@ function fn_get_max_value_report_interval($report, $table_id)
  154.  
  155. return $max_value;
  156. }
  157. +
  158. +/**
  159. + * Calculates total discount for product in orders or an order itself
  160. + *
  161. + * @param string $additional_fields Additional fields fot fetch from the database
  162. + * @param string $where Where clause for query
  163. + * @param null|string $hash Item hash that the totals is calculated for
  164. + *
  165. + * @return array|mixed
  166. + */
  167. +function fn_sales_reports_get_orders_subtotal_discount($data, $additional_fields, $where, $hash = null)
  168. +{
  169. + $original_hash = $hash;
  170. + $hash = !is_null($hash) ? $hash : 0;
  171. +
  172. + $orders_data = db_get_array(
  173. + "SELECT ?:orders.order_id, subtotal_discount as total, ?:order_details.extra {$additional_fields}"
  174. + . ' FROM ?:order_details'
  175. + . ' LEFT JOIN ?:orders ON ?:orders.order_id = ?:order_details.order_id'
  176. + . ' WHERE ?p',
  177. + $where
  178. + );
  179. +
  180. + $applied_discounts_order_ids = array();
  181. +
  182. + foreach ($orders_data as $order_item_data) {
  183. + $interval = $order_item_data['interval'];
  184. +
  185. + if (!isset($data[$hash][$interval]['total'])) {
  186. + $data[$hash][$interval]['total'] = 0;
  187. + $data[$hash][$interval]['interval'] = $interval;
  188. + $data[$hash][$interval]['timestamp'] = $order_item_data['timestamp'];
  189. + }
  190. +
  191. + if (!empty($order_item_data['total'])
  192. + && !in_array($order_item_data['order_id'], $applied_discounts_order_ids)
  193. + ) {
  194. + $data[$hash][$interval]['total'] += $order_item_data['total'];
  195. + $applied_discounts_order_ids[] = $order_item_data['order_id'];
  196. + }
  197. +
  198. + $extra = @unserialize($order_item_data['extra']);
  199. +
  200. + if (!empty($extra['discount'])) {
  201. + $data[$hash][$interval]['total'] += $extra['discount'];
  202. + }
  203. +
  204. + $data[$hash][$interval]['total'] = fn_format_price($data[$hash][$interval]['total']);
  205. + }
  206. +
  207. + return is_null($original_hash) ? reset($data) : $data;
  208. +}
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!