ChannelSaleStatistics.php 6.2 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157
  1. <?php
  2. namespace app\api\model;
  3. use app\common\model\ChannelSaleStatistics as ChannelSaleStatisticsModel;
  4. use think\facade\Db;
  5. /**
  6. * 渠道销售统计模型
  7. * @package app\api\model
  8. */
  9. class ChannelSaleStatistics extends ChannelSaleStatisticsModel
  10. {
  11. /**
  12. * 批量写入每日数据
  13. * 发布上线时,只需执行一次
  14. */
  15. public static function addInitData()
  16. {
  17. $begin_date = '2021-10-01'; // 生成初始数据开始时间,建议时间为:小程序上线时间
  18. $end_date = date('Y-m-d', strtotime('-1 days')); // 生成初始数据结束时间,建议时间为:昨天
  19. $date_arr = period_date(strtotime($begin_date), strtotime($end_date), 'Y-m-d');
  20. foreach ($date_arr as $date) {
  21. self::addData($date);
  22. echo $date . PHP_EOL;
  23. }
  24. return true;
  25. }
  26. public static function addData($date)
  27. {
  28. $startTime = strtotime("{$date} 00:00:00");
  29. $endTime = strtotime("{$date} 23:59:59");
  30. $payData = self::getPayData($startTime, $endTime);
  31. $singleRefundData = self::getSingleRefundData($startTime, $endTime);
  32. $multiRefundData = self::getMultiRefundData($startTime, $endTime);
  33. self::formatData($startTime, $endTime, $payData, $singleRefundData, $multiRefundData);
  34. }
  35. private static function formatData($startTime, $endTime, $payData, $singleRefundData = [], $multiRefundData = [])
  36. {
  37. // $idArr = array_unique(array_merge(array_keys($payData), array_keys($singleRefundData), array_keys($multiRefundData)));
  38. $idArr = Db::name('shops')->where('is_delete', '=', 0)->column('shop_id');
  39. if (empty($idArr)) {
  40. return;
  41. }
  42. $idArr[0] = 0; // 其他渠道
  43. foreach ($idArr as $id) {
  44. $payAmount = 0;
  45. $payCount = 0;
  46. $payGoodsCount = 0;
  47. $payUserCount = 0;
  48. $refundAmount = 0;
  49. $refundOrderCount = 0;
  50. $refundGoodsCount = 0;
  51. if (isset($payData[$id])) {
  52. $payAmount = $payData[$id]['pay_amount'];
  53. $payCount = $payData[$id]['pay_count'];
  54. $payGoodsCount = $payData[$id]['pay_goods_count'];
  55. $payUserCount = $payData[$id]['pay_user_count'];
  56. }
  57. if (isset($singleRefundData[$id])) {
  58. $refundAmount += $singleRefundData[$id]['refund_amount'];
  59. $refundOrderCount += $singleRefundData[$id]['refund_order_count'];
  60. $refundGoodsCount += $singleRefundData[$id]['refund_goods_count'];
  61. }
  62. if (isset($multiRefundData[$id])) {
  63. $refundAmount += $multiRefundData[$id]['refund_amount'];
  64. $refundOrderCount += $multiRefundData[$id]['refund_order_count'];
  65. $refundGoodsCount += $multiRefundData[$id]['refund_goods_count'];
  66. }
  67. $insert = [
  68. 'start_time' => $startTime,
  69. 'end_time' => $endTime,
  70. 'shop_id' => $id,
  71. 'pay_amount' => $payAmount,
  72. 'pay_count' => $payCount,
  73. 'pay_goods_count' => $payGoodsCount,
  74. 'pay_user_count' => $payUserCount,
  75. 'refund_amount' => $refundAmount,
  76. 'refund_order_count' => $refundOrderCount,
  77. 'refund_goods_count' => $refundGoodsCount,
  78. ];
  79. $info = self::where('shop_id', $id)->where('start_time', $startTime)->where('end_time', $endTime)->find();
  80. if (empty($info)) {
  81. (new self)->save($insert);
  82. } else {
  83. $info->where('id', $info['id'])->update($insert);
  84. }
  85. }
  86. }
  87. /**
  88. * 订单信息
  89. * @param $startTime
  90. * @param $endTime
  91. * @return array
  92. */
  93. private static function getPayData($startTime, $endTime)
  94. {
  95. $data = Db::name('order')->alias('order')
  96. ->whereBetween('order.pay_time', [$startTime, $endTime])
  97. ->where('order.is_delete', '=', 0)
  98. ->where('og.goods_type', '<>', 20) // // 排除赠品
  99. ->leftJoin('order_goods og', 'og.order_id=order.order_id')
  100. ->group('order.staff_shop_id')
  101. ->column('order.staff_shop_id as shop_id,count(distinct(order.order_id)) as pay_count,sum(distinct(order.pay_price+order.rice_card_money)) as pay_amount,
  102. sum(og.total_num) as pay_goods_count,count(distinct(order.user_id)) as pay_user_count', 'order.staff_shop_id');
  103. return $data;
  104. }
  105. /**
  106. * 单个退款
  107. * @param $startTime
  108. * @param $endTime
  109. * @return array
  110. */
  111. private static function getSingleRefundData($startTime, $endTime)
  112. {
  113. $data = Db::name('order_refund')->alias('r')
  114. ->where('r.refund_succ_time', '<=', $endTime)
  115. ->where('r.refund_succ_time', '>', $startTime)
  116. ->where('r.order_goods_id', '>', 0)
  117. ->where('r.status', '=', 20)
  118. ->where('r.finance_refund', '=', 10)
  119. ->leftJoin('order_goods og', 'og.order_goods_id=r.order_goods_id')
  120. ->leftJoin('order', 'order.order_id=r.order_id')
  121. ->group('order.staff_shop_id')
  122. ->column('sum(r.refund_money) as refund_amount,count(distinct(r.order_id)) as refund_order_count,sum(r.goods_num) as refund_goods_count', 'order.staff_shop_id');
  123. return $data;
  124. }
  125. /**
  126. * 整单退款
  127. * @param $startTime
  128. * @param $endTime
  129. * @return array
  130. */
  131. private static function getMultiRefundData($startTime, $endTime)
  132. {
  133. $data = Db::name('order_refund')->alias('r')
  134. ->where('r.refund_succ_time', '<=', $endTime)
  135. ->where('r.refund_succ_time', '>', $startTime)
  136. ->where('r.order_goods_id', '=', 0)
  137. ->where('r.status', '=', 20)
  138. ->where('r.finance_refund', '=', 10)
  139. ->leftJoin('order_goods og', 'og.order_id=r.order_id')
  140. ->leftJoin('order', 'order.order_id=r.order_id')
  141. ->group('order.staff_shop_id')
  142. ->column('sum(r.refund_money) as refund_amount,count(distinct(r.order_id)) as refund_order_count,sum(r.goods_num) as refund_goods_count', 'order.staff_shop_id');
  143. return $data;
  144. }
  145. }