StatisticsService.php 19 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441
  1. <?php
  2. namespace App\Service;
  3. use App\Model\EmployeeIndex;
  4. use App\Model\GiveOut;
  5. use App\Model\RevenueCost;
  6. use App\Model\RevenueCostTotal;
  7. use App\Model\SalaryEmployee;
  8. use Illuminate\Support\Facades\DB;
  9. class StatisticsService extends Service
  10. {
  11. public function statisticsRevenueCostCommon($data,$user, $field = []){
  12. if(empty($field)) $field = RevenueCostTotal::$field;
  13. $model = RevenueCostTotal::Clear($user,$data);
  14. $model = $model->where('del_time',0)
  15. ->select($field)
  16. ->orderby('id', 'desc');
  17. if(! empty($data['employee_id_1_title'])) $model->where('employee_id_1_title', 'LIKE', '%'.$data['employee_id_1_title'].'%');
  18. if(! empty($data['order_time'][0]) && ! empty($data['order_time'][1])){
  19. list($start_time, $end_time) = $this->changeDateToTimeStampAboutRange($data['order_time'],false);
  20. if ($start_time === null || $end_time === null || $start_time > $end_time) return [false, "单据日期的区间无效"];
  21. $model->where('order_time', '>=', $start_time)
  22. ->where('order_time', '<', $end_time);
  23. }
  24. if(! empty($data['order_type'])) $model->where('order_type',$data['order_type']);
  25. return [true, $model];
  26. }
  27. public function statisticsRevenueCost($data,$user){
  28. list($status, $model) = $this->statisticsRevenueCostCommon($data, $user);
  29. if(! $status) return [false, $model];
  30. $list = $this->limit($model,'',$data);
  31. $list = $this->statisticsRevenueCostFillData($list);
  32. $header_default = config("header.68") ?? [];
  33. $list['count'] = $this->countTotal($list['data'], $header_default);
  34. return [true, $list];
  35. }
  36. public function statisticsRevenueCostFillData($data){
  37. if(empty($data['data'])) return $data;
  38. foreach ($data['data'] as $key => $value){
  39. $time = date("Y-m", $value['order_time']);
  40. $data['data'][$key]['order_time'] = $time;
  41. $data['data'][$key]['order_type_title'] = RevenueCost::$order_type[$value['order_type']] ?? "";
  42. $data['data'][$key]['profit_rate'] = bcmul($value['profit_rate'], 100,2);
  43. }
  44. return $data;
  45. }
  46. public function statisticsRevenueCostOneAndTwoCommon($data,$user, $field = []){
  47. if(empty($data['order_type']) || ! isset(RevenueCost::$order_type[$data['order_type']])) return [false, '单据类型不存在或错误'];
  48. if(empty($field)) {
  49. if($data['order_type'] == 1){
  50. $field = RevenueCost::$field_xhd;
  51. }else{
  52. $field = RevenueCost::$field_xsfp;
  53. }
  54. }
  55. $model = RevenueCost::Clear($user,$data);
  56. $model = $model->where('del_time',0)
  57. ->where('order_type',$data['order_type'])
  58. ->select($field)
  59. ->orderby('id', 'desc');
  60. if(! empty($data['order_time'][0]) && ! empty($data['order_time'][1])){
  61. list($start_time, $end_time) = $this->changeDateToTimeStampAboutRange($data['order_time'],false);
  62. if ($start_time === null || $end_time === null || $start_time > $end_time) return [false, "单据日期的区间无效"];
  63. $model->where('order_time', '>=', $start_time)
  64. ->where('order_time', '<=', $end_time);
  65. }
  66. if(! empty($data['order_number'])) $model->where('order_number', 'LIKE', '%'.$data['order_number'].'%');
  67. if(! empty($data['customer_title'])) $model->where('customer_title', 'LIKE', '%'.$data['customer_title'].'%');
  68. if(! empty($data['employee_id_1_title'])) $model->where('employee_id_1_title', 'LIKE', '%'.$data['employee_id_1_title'].'%');
  69. return [true, $model];
  70. }
  71. public function statisticsRevenueCostOneAndTwo($data,$user){
  72. list($status, $model) = $this->statisticsRevenueCostOneAndTwoCommon($data, $user);
  73. if(! $status) return [false, $model];
  74. $list = $this->limit($model,'',$data);
  75. $list = $this->statisticsRevenueCostOneAndTwoFillData($list);
  76. if($data['order_type'] == 1){
  77. $header_default = config("header.69") ?? [];
  78. }else{
  79. $header_default = config("header.70") ?? [];
  80. }
  81. $list['count'] = $this->countTotal($list['data'], $header_default);
  82. return [true, $list];
  83. }
  84. public function statisticsRevenueCostOneAndTwoFillData($data){
  85. if(empty($data['data'])) return $data;
  86. foreach ($data['data'] as $key => $value){
  87. $data['data'][$key]['profit_rate'] = bcmul($value['profit_rate'], 100,2);
  88. $data['data'][$key]['order_time'] = $value['order_time'] ? date("Y-m-d", $value['order_time']) : "";
  89. }
  90. return $data;
  91. }
  92. public function statisticsRevenueCostThreeCommon($data,$user, $field = []){
  93. if(empty($field)) {
  94. $field = RevenueCost::$field_hkd_salary_main;
  95. $field[] = DB::raw('sum(payment_amount) as payment_amount');
  96. $field[] = DB::raw('sum(price_4_total) as price_4_total');
  97. $field[] = DB::raw('sum(profit) as profit');
  98. }
  99. $model = RevenueCost::Clear($user,$data);
  100. $model = $model->where('del_time',0)
  101. ->where('order_type',RevenueCost::ORDER_THREE)
  102. ->select($field)
  103. ->groupby('order_id')
  104. ->orderby('id', 'desc');
  105. if(! empty($data['order_time'][0]) && ! empty($data['order_time'][1])){
  106. list($start_time, $end_time) = $this->changeDateToTimeStampAboutRange($data['order_time'],false);
  107. if ($start_time === null || $end_time === null || $start_time > $end_time) return [false, "单据日期的区间无效"];
  108. $model->where('order_time', '>=', $start_time)
  109. ->where('order_time', '<=', $end_time);
  110. }
  111. if(! empty($data['order_number'])) $model->where('order_number', 'LIKE', '%'.$data['order_number'].'%');
  112. if(! empty($data['employee_id_1_title'])) $model->where('employee_id_1_title', 'LIKE', '%'.$data['employee_id_1_title'].'%');
  113. return [true, $model];
  114. }
  115. public function statisticsRevenueCostThree($data,$user){
  116. list($status, $model) = $this->statisticsRevenueCostThreeCommon($data, $user);
  117. if(! $status) return [false, $model];
  118. $list = $this->limit($model,'',$data);
  119. $list = $this->statisticsRevenueCostThreeFillData($list);
  120. $header_default = config("header.71") ?? [];
  121. $list['count'] = $this->countTotal($list['data'], $header_default);
  122. return [true, $list];
  123. }
  124. public function statisticsRevenueCostThreeFillData($data){
  125. if(empty($data['data'])) return $data;
  126. foreach ($data['data'] as $key => $value){
  127. $profit_rate = $value['payment_amount'] > 0 ? bcdiv($value['profit'], $value['payment_amount'],2) : 0;
  128. $data['data'][$key]['profit_rate'] = bcmul($profit_rate, 100,2);
  129. $data['data'][$key]['order_time'] = $value['order_time'] ? date("Y-m-d", $value['order_time']) : "";
  130. }
  131. return $data;
  132. }
  133. public function statisticsRevenueCostThreeDetail($data,$user){
  134. if(empty($data['order_number'])) return [false, '回款单号不能为空'];
  135. $result = RevenueCost::where('del_time',0)
  136. ->where('order_type',RevenueCost::ORDER_THREE)
  137. ->where('order_number',$data['order_number'])
  138. ->select(RevenueCost::$field_hkd_detail)
  139. ->get()->toArray();
  140. if(empty($result)) return [false, "回款单不存在或已被删除"];
  141. $detail = [];
  142. foreach ($result as $value){
  143. $detail[] = [
  144. 'order_number' => $value['order_number_upstream'],
  145. 'customer_code' => $value['customer_code'],
  146. 'customer_title' => $value['customer_title'],
  147. 'product_code' => $value['product_code'],
  148. 'product_title' => $value['product_title'],
  149. 'product_size' => $value['product_size'],
  150. 'unit' => $value['unit'],
  151. 'quantity' => $value['quantity'],
  152. 'payment_amount' => $value['payment_amount'],
  153. 'price_1' => $value['price_1'],
  154. 'price_1_total' => $value['price_1_total'],
  155. 'price_4' => $value['price_4'],
  156. 'price_4_total' => $value['price_4_total'],
  157. 'profit' => $value['profit'],
  158. 'profit_rate' => bcmul($value['profit_rate'],100,2),
  159. 'customer_profit_rate' => $value['customer_profit_rate'],
  160. ];
  161. }
  162. $first = $result[0];
  163. $order = [
  164. 'order_number' => $data['order_number'],
  165. 'order_time' => date("Y-m-d",$first['order_time']),
  166. 'employee_id_1_title' => $first['employee_id_1_title'],
  167. 'detail' => $detail
  168. ];
  169. return [true, $order];
  170. }
  171. public function statisticsProfitCommon($data,$user, $field = []){
  172. if(empty($field)) {
  173. $field = RevenueCost::$field_hkd_profit_main;
  174. $field[] = DB::raw('COALESCE(SUM(rc.payment_amount), 0) AS payment_amount');
  175. }
  176. $type = RevenueCost::ORDER_THREE;
  177. $model = EmployeeIndex::Clear($user, $data)
  178. ->where('employee_index.del_time', 0)
  179. ->where('employee_index.type', EmployeeIndex::TYPE_THREE)
  180. ->leftJoin(DB::raw('revenue_cost as rc'), function ($join) use ($type) {
  181. $join->on('rc.employee_id_2', '=', 'employee_index.employee_id')
  182. ->where('rc.del_time', 0)
  183. ->where('rc.order_type', $type)
  184. ->whereRaw('rc.order_time >= employee_index.start_time')
  185. ->whereRaw('rc.order_time <= employee_index.end_time');
  186. })
  187. ->select($field)
  188. ->groupBy('employee_index.employee_id', 'employee_index.start_time', 'employee_index.end_time')
  189. ->orderBy('employee_index.end_time', 'desc');
  190. // $type = RevenueCost::ORDER_THREE;
  191. // $model = EmployeeIndex::where('employee_index.del_time',0)
  192. // ->where('employee_index.type',EmployeeIndex::TYPE_THREE)
  193. // ->leftJoin(DB::raw('revenue_cost as rc'), function ($join) use ($type) {
  194. // $join->on('rc.employee_id_2', '=', 'employee_index.employee_id')
  195. // ->where('rc.del_time', 0)
  196. // ->where('rc.order_type', $type)
  197. // ->whereRaw('rc.order_time >= employee_index.start_time')
  198. // ->whereRaw('rc.order_time <= employee_index.end_time');
  199. // })
  200. // ->select($field)
  201. // ->groupBy('employee_index.employee_id', 'employee_index.start_time', 'employee_index.end_time')
  202. // ->orderBy('employee_index.end_time','desc');
  203. if(! empty($data['order_time'][0]) && ! empty($data['order_time'][1])){;
  204. list($start_time, $end_time) = $this->changeDateToTimeStampAboutRange($data['order_time'],false);
  205. if ($start_time === null || $end_time === null || $start_time > $end_time) return [false, "单据日期的区间无效"];
  206. $model->where('employee_index.start_time', '<=', $end_time)
  207. ->where('employee_index.end_time', '>=', $start_time);
  208. }
  209. if(! empty($data['employee_id_2_title'])) $model->where('rc.employee_id_2_title', 'LIKE', '%'.$data['employee_id_2_title'].'%');
  210. return [true, $model];
  211. }
  212. public function statisticsProfit($data,$user){
  213. list($status, $model) = $this->statisticsProfitCommon($data, $user);
  214. if(! $status) return [false, $model];
  215. $list = $this->limit($model,'',$data);
  216. $list = $this->statisticsProfitFillData($list);
  217. return [true, $list];
  218. }
  219. public function statisticsProfitFillData($data){
  220. if(empty($data['data'])) return $data;
  221. //获取应发放金额需要的指标
  222. $indexes = $this->getEmployeeIndex($data['data']);
  223. //已发
  224. $indexes_give = $this->getEmployeeGiveOut($data['data']);
  225. foreach ($data['data'] as $key => $value){
  226. $start = $value['start_time'] ? date('Y-m-d',$value['start_time']) : '';
  227. $end = $value['end_time'] ? date('Y-m-d',$value['end_time']) : '';
  228. $string = "";
  229. if(! empty($start) && ! empty($end)) $string = $start . "-" . $end;
  230. $value['check_time'] = $string;
  231. $value['index_' . EmployeeIndex::TYPE_ONE] = 0;
  232. $value['index_' . EmployeeIndex::TYPE_FIVE] = 0;
  233. //指标
  234. $this->makeIndex($indexes, $value, $data['data'][$key]);
  235. //应付
  236. $this->makeShouldPay($value,$data['data'][$key]);
  237. //已付
  238. $this->makePayed($indexes_give, $value,$data['data'][$key]);
  239. //未付
  240. $not_pay = bcsub($data['data'][$key]['should_pay'], $data['data'][$key]['payed'],2);
  241. $data['data'][$key]['not_pay'] = $not_pay;
  242. }
  243. return $data;
  244. }
  245. private function getEmployeeIndex($existingData)
  246. {
  247. if (empty($existingData)) {
  248. return collect();
  249. }
  250. // 取出所有涉及的 employee_id 和时间区间
  251. $employeeIds = array_column($existingData, 'employee_id_2');
  252. $start_time = array_column($existingData, 'start_time');
  253. $minStart = ! empty($start_time) ? min($start_time) : 0;
  254. $end_time = array_column($existingData, 'end_time');
  255. $maxEnd = ! empty($end_time) ? max($end_time) : 0;
  256. // 一次性查出这些员工在最大区间范围内的所有指标
  257. $results = EmployeeIndex::where('del_time', 0)
  258. ->whereIn('type', [EmployeeIndex::TYPE_ONE, EmployeeIndex::TYPE_FIVE])
  259. ->whereIn('employee_id', $employeeIds)
  260. ->where('start_time', '<=', $maxEnd)
  261. ->where('end_time', '>=', $minStart)
  262. ->select('start_time','end_time','employee_id','index','type')
  263. ->get();
  264. return $results;
  265. }
  266. private function getEmployeeGiveOut($existingData)
  267. {
  268. if (empty($existingData)) {
  269. return collect();
  270. }
  271. // 取出所有涉及的 employee_id 和时间区间
  272. $employeeIds = array_column($existingData, 'employee_id_2');
  273. $start_time = array_column($existingData, 'start_time');
  274. $minStart = ! empty($start_time) ? min($start_time) : 0;
  275. $end_time = array_column($existingData, 'end_time');
  276. $maxEnd = ! empty($end_time) ? max($end_time) : 0;
  277. // 一次性查出这些员工在最大区间范围内的所有指标
  278. $results = GiveOut::where('del_time', 0)
  279. ->whereIn('employee_id_1', $employeeIds)
  280. ->where('start_time', '<=', $maxEnd)
  281. ->where('end_time', '>=', $minStart)
  282. ->select('start_time','end_time','employee_id_1 as employee_id','give_out_amount')
  283. ->get();
  284. return $results;
  285. }
  286. private function makeIndex($indexes, $value, &$data){
  287. // 找到所有符合条件的 index(可能多个 type)
  288. $matchedIndexes = $indexes->filter(function ($item) use ($value) {
  289. return $item['employee_id'] == $value['employee_id_2']
  290. && $item['start_time'] <= $value['end_time']
  291. && $item['end_time'] >= $value['start_time'];
  292. });
  293. // 按 type 去重,只保留第一次出现的
  294. $uniqueByType = [];
  295. foreach ($matchedIndexes as $item) {
  296. $index = "index_" . $item['type'];
  297. if (! isset($uniqueByType[$index])) {
  298. $uniqueByType[$index] = $item['index'];
  299. }
  300. }
  301. $data = array_merge($value, $uniqueByType);
  302. }
  303. private function makeShouldPay($value, &$data){
  304. $index_1 = bcdiv($data['index_1'],100,2);
  305. $index_5 = bcdiv($data['index_5'],100,2);
  306. // 分红比例/2
  307. $a = bcdiv($index_5,2,2);
  308. // 季度预支分红比例/2
  309. $b = bcdiv($index_1,2,2);
  310. //(分红比例/2-季度预支分红比例/2)
  311. $c = bcsub($a, $b,2);
  312. $data['part_left'] = $c;
  313. if($value['payment_amount'] < $value['index']){
  314. //回款金额*(分红比例/2-季度预支分红比例/2);
  315. $shouldPay = bcmul($value['payment_amount'], $c,2);
  316. }else{
  317. //回款金额-季度指标金额
  318. $d = bcsub($value['payment_amount'], $value['index'],2);
  319. //(回款金额-季度指标金额)*15%
  320. $e = bcmul($d, 0.15,2);
  321. //(回款金额-季度指标金额)*15%/2
  322. $e = bcdiv($e,2,2);
  323. //(分红比例/2-季度预支分红比例/2+(回款金额-季度指标金额)*15%/2)
  324. $rate = bcadd($c, $e,2);
  325. // $data['data'][$key]['rate'] = $rate;
  326. //回款金额*(分红比例/2-回款金额*季度预支分红比例/2+(回款金额-季度指标金额)*15%/2)
  327. $shouldPay = bcmul($value['payment_amount'], $rate,2);
  328. }
  329. $data['should_pay'] = $shouldPay;
  330. }
  331. private function makePayed($indexes, $value, &$data){
  332. $matchedIndexes_give = $indexes->filter(function ($item) use ($value) {
  333. return $item['employee_id'] == $value['employee_id_2']
  334. && $item['start_time'] <= $value['end_time']
  335. && $item['end_time'] >= $value['start_time'];
  336. });
  337. $give_out = 0;
  338. foreach ($matchedIndexes_give as $item) {
  339. $give_out = bcadd($item['give_out_amount'], $give_out,2);
  340. }
  341. $data['payed'] = $give_out;
  342. }
  343. public function statisticsEmployeeSalaryCommon($data,$user, $field = []){
  344. if(empty($field)) {
  345. $field = SalaryEmployee::$field;
  346. }
  347. $model = SalaryEmployee::Clear($user,$data);
  348. $model = $model->where('del_time',0)
  349. ->where('order_type',RevenueCost::ORDER_THREE)
  350. ->select($field)
  351. ->orderby('order_time', 'desc');
  352. if(! empty($data['order_time'][0]) && ! empty($data['order_time'][1])){
  353. $start_time = strtotime($data['order_time'][0] . "-01");
  354. $end_time = strtotime($data['order_time'][1] . "-01");
  355. $model->where('order_time', '>=', $start_time)
  356. ->where('order_time', '<=', $end_time);
  357. }
  358. if(! empty($data['employee_id_1_title'])) $model->where('employee_id_1_title', 'LIKE', '%'.$data['employee_id_1_title'].'%');
  359. return [true, $model];
  360. }
  361. public function statisticsEmployeeSalary($data,$user){
  362. list($status, $model) = $this->statisticsEmployeeSalaryCommon($data, $user);
  363. if(! $status) return [false, $model];
  364. $list = $this->limit($model,'',$data);
  365. $list = $this->statisticsEmployeeSalaryFillData($list);
  366. return [true, $list];
  367. }
  368. public function statisticsEmployeeSalaryFillData($data){
  369. if(empty($data['data'])) return $data;
  370. foreach ($data['data'] as $key => $value){
  371. $order_time = $value['order_time'] ? date('Y-m',$value['order_time']) : '';
  372. $data['data'][$key]['order_time'] = $order_time;
  373. }
  374. return $data;
  375. }
  376. }