123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485 |
- <?php
- namespace addons\customcharts\library;
- use think\Db;
- class Core
- {
- /**
- * 数量统计
- * \addons\customcharts\library\Core::totalNumber();
- * @author Created by Xing <464401240@qq.com>
- */
- public static function totalNumber()
- {
- $result = [];
- $list = \app\admin\model\customcharts\Totalnumber::order('weigh ASC')->select();
- foreach ($list as $row) {
- $result[$row->id] = [
- 'icon_color' => $row->icon_color,
- 'is_money' => $row->is_money,
- 'total' => 0,
- 'lastTotal' => 0,
- 'ratio' => 404, //不等于404页面上才会显示
- 'title' => $row->title,
- 'icon' => $row->icon
- ];
- if ($row->type_total == 'sum') {
- if ($row->type_time == 'all') {
- $result[$row->id]['total'] = round(Db::table($row->name)
- ->where($row->where)
- ->sum($row->field_total), 2);
- } else {
- //本期
- $result[$row->id]['total'] = round(Db::table($row->name)
- ->where($row->where)
- ->whereTime($row->field_time, $row->type_time)
- ->sum($row->field_total), 2);
- //上期
- if ($row->type_time == 'today') {
- $lastTotal = round(Db::table($row->name)->where($row->where)
- ->whereTime($row->field_time, 'yesterday')
- ->sum($row->field_total), 2);
- } else {
- $lastTotal = round(Db::table($row->name)->where($row->where)
- ->whereTime($row->field_time, 'last ' . $row->type_time)
- ->sum($row->field_total), 2);
- }
- }
- } else {
- if ($row->type_time == 'all') {
- $result[$row->id]['total'] = round(Db::table($row->name)
- ->where($row->where)
- ->count($row->field_total), 2);
- } else {
- //本期
- $result[$row->id]['total'] = round(Db::table($row->name)
- ->where($row->where)
- ->whereTime($row->field_time, $row->type_time)
- ->count($row->field_total), 2);
- //上期
- if ($row->type_time == 'today') {
- $lastTotal = round(Db::table($row->name)->where($row->where)
- ->whereTime($row->field_time, 'yesterday')
- ->count($row->field_total), 2);
- } else {
- $lastTotal = round(Db::table($row->name)->where($row->where)
- ->whereTime($row->field_time, 'last ' . $row->type_time)
- ->count($row->field_total), 2);
- }
- }
- }
- if (isset($lastTotal)) {
- $result[$row->id]['lastTotal'] = $lastTotal;
- //同比增长
- if ($lastTotal > 0) {
- $result[$row->id]['ratio'] = ceil((($result[$row->id]['total'] - $lastTotal) / $lastTotal) * 100);
- } else {
- $result[$row->id]['ratio'] = ($result[$row->id]['total'] > 0 ? 100 : 0);
- }
- }
- }
- return $result;
- }
- /**
- * 图表统计
- * \addons\customcharts\library\Core::totalChart();
- * @author Created by Xing <464401240@qq.com>
- */
- public static function totalChart($date = '')
- {
- try {
- \think\Db::execute("SET @@sql_mode='';");
- } catch (\Exception $e) {
- }
- if ($date) {
- list($start, $end) = explode(' - ', $date);
- $starttime = strtotime($start);
- $endtime = strtotime($end);
- } else {
- $starttime = \fast\Date::unixtime('day', -29, 'begin');
- $endtime = \fast\Date::unixtime('day', 0, 'end');
- }
- $result = [];
- $list = \app\admin\model\customcharts\Chart::order('weigh ASC')->select();
- foreach ($list as $key => $row) {
- if ($row->chart_type == 'pie') {
- //饼状统计
- $result[$key] = self::totalChartPie($row, $starttime, $endtime);
- } else {
- //图表统计数据
- $result[$key] = self::totalChartGraph($row, $starttime, $endtime);
- }
- }
- return $result;
- }
- /**
- * 排行统计
- * \addons\customcharts\library\Core::totalRanking();
- * @author Created by Xing <464401240@qq.com>
- */
- public static function totalRanking()
- {
- try {
- \think\Db::execute("SET @@sql_mode='';");
- } catch (\Exception $e) {
- }
- $result = [];
- $list = \app\admin\model\customcharts\Ranking::order('weigh ASC')->select();
- foreach ($list as $row) {
- $row->show_num = $row->show_num < 1 ? 1 : intval($row->show_num);
- if ($row->type_total == 'sum') {
- //按和统计
- $result[$row->id] = self::totalRankingSum($row);
- } else {
- //按行统计
- $result[$row->id] = self::totalRankingCount($row);
- }
- }
- return $result;
- }
- /**
- * 饼状图统计
- * @author Created by Xing <464401240@qq.com>
- */
- private static function totalChartPie($row, $starttime, $endtime)
- {
- if ($row->type_total == 'sum') {
- //按和统计
- if ($row->join_table) {
- $data = Db::table($row->name)
- ->join($row->join_table, $row->join_table . '.' . $row->local_key . ' = ' . $row->name . '.' . $row->foreign_key)
- ->where($row->where)
- ->where($row->name . '.' . $row->field_time, 'between time', [$starttime, $endtime])
- ->field("{$row->join_table}.{$row->field_show} as field_show,{$row->name}.{$row->group_field},SUM({$row->name}.{$row->field_total}) as nums")
- ->group("{$row->name}.{$row->group_field}")
- ->select();
- } else {
- $data = Db::table($row->name)
- ->where($row->where)
- ->where($row->field_time, 'between time', [$starttime, $endtime])
- ->field("{$row->group_field},SUM({$row->field_total}) as nums")
- ->group($row->group_field)
- ->select();
- }
- } else {
- //按行统计
- if ($row->join_table) {
- $data = Db::table($row->name)
- ->join($row->join_table, $row->join_table . '.' . $row->local_key . ' = ' . $row->name . '.' . $row->foreign_key)
- ->where($row->where)
- ->where($row->name . '.' . $row->field_time, 'between time', [$starttime, $endtime])
- ->field("{$row->join_table}.{$row->field_show} as field_show,{$row->name}.{$row->group_field},COUNT({$row->name}.{$row->field_total}) as nums")
- ->group("{$row->name}.{$row->group_field}")
- ->select();
- } else {
- $data = Db::table($row->name)
- ->where($row->where)
- ->where($row->field_time, 'between time', [$starttime, $endtime])
- ->field("{$row->group_field},COUNT({$row->field_total}) as nums")
- ->group($row->group_field)
- ->select();
- }
- }
- $result = [
- 'category' => [],
- 'data' => [],
- 'id' => $row->id,
- 'legend_title' => $row->legend_title,
- 'unit' => $row->unit,
- 'chart_type' => $row->chart_type,
- 'title' => $row->title,
- 'subtext' => $row->subtext
- ];
- foreach ($data as $index => $item) {
- if ($row->join_table) {
- $name = $item['field_show'];
- } else {
- $dictionary = json_decode($row->dictionary, true);
- $name = $dictionary[$item[$row->group_field]] ?? $row->group_field . '(' . $item[$row->group_field] . ')';
- }
- $result['category'][] = $name;
- $result['data'][] = ['value' => $item['nums'], 'name' => $name];
- }
- return $result;
- }
- /**
- * 柱状图和曲线图统计
- * @author Created by Xing <464401240@qq.com>
- */
- private static function totalChartGraph($row, $starttime, $endtime)
- {
- $totalseconds = $endtime - $starttime;
- if ($totalseconds > 86400 * 30 * 2) {
- $format = '%Y-%m';
- } else {
- if ($totalseconds > 86400) {
- $format = '%Y-%m-%d';
- } else {
- $format = '%H:00';
- }
- }
- if ($row->type_total == 'sum') {
- if ($row->field_time_type == 'int' || $row->field_time_type == 'bigint') {
- $sql = $row->name . '.' . $row->group_field . ',SUM(' . $row->name . '.' . $row->field_total . ') AS amount,DATE_FORMAT(FROM_UNIXTIME(' . $row->name . '.' . $row->field_time . '), "' . $format . '") AS field_time';
- } else {
- $sql = $row->name . '.' . $row->group_field . ',SUM(' . $row->name . '.' . $row->field_total . ') AS amount,DATE_FORMAT(' . $row->name . '.' . $row->field_time . ', "' . $format . '") AS field_time';
- }
- } else {
- if ($row->field_time_type == 'int' || $row->field_time_type == 'bigint') {
- $sql = $row->name . '.' . $row->group_field . ',COUNT(' . $row->name . '.' . $row->field_total . ') AS amount,DATE_FORMAT(FROM_UNIXTIME(' . $row->name . '.' . $row->field_time . '), "' . $format . '") AS field_time';
- } else {
- $sql = $row->name . '.' . $row->group_field . ',COUNT(' . $row->name . '.' . $row->field_total . ') AS amount,DATE_FORMAT(' . $row->name . '.' . $row->field_time . ', "' . $format . '") AS field_time';
- }
- }
- if ($row->join_table) {
- $data = Db::table($row->name)
- ->join($row->join_table, $row->join_table . '.' . $row->local_key . ' = ' . $row->name . '.' . $row->foreign_key)
- ->where($row->where)
- ->where($row->name . '.' . $row->field_time, 'between time', [$starttime, $endtime])
- ->field("{$row->join_table}.{$row->field_show} as field_show,{$sql}")
- ->group("field_time,{$row->name}.{$row->group_field}")
- ->select();
- } else {
- $data = Db::table($row->name)
- ->where($row->where)
- ->where($row->field_time, 'between time', [$starttime, $endtime])
- ->field("{$sql}")
- ->group('field_time,' . $row->group_field)
- ->select();
- }
- if ($totalseconds > 84600 * 30 * 2) {
- $starttime = strtotime('last month', $starttime);
- while (($starttime = strtotime('next month', $starttime)) <= $endtime) {
- $column[] = date('Y-m', $starttime);
- }
- } else {
- if ($totalseconds > 86400) {
- for ($time = $starttime; $time <= $endtime;) {
- $column[] = date("Y-m-d", $time);
- $time += 86400;
- }
- } else {
- for ($time = $starttime; $time <= $endtime;) {
- $column[] = date("H:00", $time);
- $time += 3600;
- }
- }
- }
- $datalist = array_fill_keys($column, 0);
- $series = [];
- $names = [];
- foreach ($data as $k => $v) {
- if (!isset($series[$v[$row->group_field]])) {
- $series[$v[$row->group_field]] = $datalist;
- }
- $series[$v[$row->group_field]][$v['field_time']] = round($v['amount'], 2);
- if ($row->join_table) {
- $name = $v['field_show'];
- } else {
- $dictionary = json_decode($row->dictionary, true);
- $name = $dictionary[$v[$row->group_field]] ?? $row->group_field . '(' . $v[$row->group_field] . ')';
- }
- $names[$v[$row->group_field]] = $name;
- }
- foreach ($datalist as $tim => $v) {
- foreach ($series as &$item) {
- if (!isset($item[$tim])) {
- $item[$tim] = 0;
- }
- }
- unset($item);
- }
- //初始化结果数组
- $result = [
- 'category' => [],
- 'data' => [],
- 'id' => $row->id,
- 'legend_title' => [],
- 'unit' => $row->unit,
- 'chart_type' => $row->chart_type,
- 'title' => $row->title,
- 'subtext' => $row->subtext
- ];
- foreach ($series as $id => $item) {
- $name = $names[$id] ?? $row->group_field . '(' . $id . ')';
- $result['legend_title'][] = $name;
- $result['series'][] = [
- 'name' => $name,
- 'type' => $row->chart_type == 'graph' ? 'line' : 'bar',
- 'data' => array_values($item),
- 'markPoint' => [
- 'data' => [
- ['type' => 'max', 'name' => '最大值'],
- ['type' => 'min', 'name' => '最小值']
- ]
- ],
- 'markLine' => [
- 'data' => [
- ['type' => 'average', 'name' => '平均值']
- ]
- ]
- ];
- }
- $unit = '{b}';
- foreach ($result['legend_title'] as $lt_key => $lt) {
- $unit .= '<br>{a' . $lt_key . '} : ' . $lt . ' {c' . $lt_key . '} ' . $row->unit;
- }
- $result['unit'] = $unit;
- $result['category'] = array_keys($datalist);
- return $result;
- }
- /**
- * @notes 排行统计-和
- * @return array
- * @author 兴
- * @date 2022/9/19 14:17
- */
- private static function totalRankingSum($row)
- {
- if ($row->join_table) {
- //有关联
- $total = Db::table($row->name)
- ->join($row->join_table, $row->join_table . '.' . $row->local_key . ' = ' . $row->name . '.' . $row->foreign_key)
- ->where($row->where)
- ->whereTime("{$row->name}.{$row->field_time}", $row->type_time)
- ->sum("{$row->name}.{$row->field_total}");
- $data = Db::table($row->name)
- ->join($row->join_table, $row->join_table . '.' . $row->local_key . ' = ' . $row->name . '.' . $row->foreign_key)
- ->where($row->where)
- ->whereTime("{$row->name}.{$row->field_time}", $row->type_time)
- ->field("{$row->join_table}.{$row->field_show} as field_show,SUM({$row->name}.{$row->field_total}) as nums,{$row->name}.{$row->group_field}")
- ->group("{$row->name}.{$row->group_field}")
- ->order("nums", "desc")
- ->limit($row->show_num)
- ->select();
- } else {
- $total = Db::table($row->name)
- ->where($row->where)
- ->whereTime($row->field_time, $row->type_time)
- ->sum($row->field_total);
- $data = Db::table($row->name)
- ->where($row->where)
- ->whereTime($row->field_time, $row->type_time)
- ->field("SUM({$row->field_total}) as nums,{$row->group_field}")
- ->group($row->group_field)
- ->order("nums", "desc")
- ->limit($row->show_num)
- ->select();
- }
- foreach ($data as $index => $item) {
- if ($row->join_table) {
- $name = $item['field_show'];
- } else {
- $dictionary = json_decode($row->dictionary, true);
- $name = $dictionary[$item[$row->group_field]] ?? $row->group_field . '(' . $item[$row->group_field] . ')';
- }
- $data[$index]['name'] = $name;
- $data[$index]['ratio'] = $total > 0 ? round(($item['nums'] / $total) * 100, 2) : 0;
- }
- $typeTime = ['today' => '今日', 'week' => '本周', 'month' => '本月'];
- $row->title .= isset($typeTime[$row->type_time]) ? "({$typeTime[$row->type_time]})" : '';
- return [
- 'total' => $total,
- 'data' => $data,
- 'title' => $row->title,
- 'unit' => $row->unit,
- ];
- }
- /**
- * @notes 排行统计-行
- * @return array
- * @author 兴
- * @date 2022/9/19 14:17
- */
- private static function totalRankingCount($row)
- {
- if ($row->join_table) {
- //有关联
- $total = Db::table($row->name)
- ->join($row->join_table, $row->join_table . '.' . $row->local_key . ' = ' . $row->name . '.' . $row->foreign_key)
- ->where($row->where)
- ->whereTime("{$row->name}.{$row->field_time}", $row->type_time)
- ->count("{$row->name}.{$row->field_total}");
- $data = Db::table($row->name)
- ->join($row->join_table, $row->join_table . '.' . $row->local_key . ' = ' . $row->name . '.' . $row->foreign_key)
- ->where($row->where)
- ->whereTime("{$row->name}.{$row->field_time}", $row->type_time)
- ->field("{$row->join_table}.{$row->field_show} as field_show,COUNT({$row->name}.{$row->field_total}) as nums,{$row->name}.{$row->group_field}")
- ->group("{$row->name}.{$row->group_field}")
- ->order("nums", "desc")
- ->limit($row->show_num)
- ->select();
- } else {
- $total = Db::table($row->name)
- ->where($row->where)
- ->whereTime($row->field_time, $row->type_time)
- ->count($row->field_total);
- $data = Db::table($row->name)
- ->where($row->where)
- ->whereTime($row->field_time, $row->type_time)
- ->field("COUNT({$row->field_total}) as nums,{$row->group_field}")
- ->group($row->group_field)
- ->order("nums", "desc")
- ->limit($row->show_num)
- ->select();
- }
- foreach ($data as $index => $item) {
- if ($row->join_table) {
- $name = $item['field_show'];
- } else {
- $dictionary = json_decode($row->dictionary, true);
- $name = $dictionary[$item[$row->group_field]] ?? $row->group_field . '(' . $item[$row->group_field] . ')';
- }
- $data[$index]['name'] = $name;
- $data[$index]['ratio'] = $total > 0 ? round(($item['nums'] / $total) * 100, 2) : 0;
- }
- $typeTime = ['today' => '今日', 'week' => '本周', 'month' => '本月'];
- $row->title .= isset($typeTime[$row->type_time]) ? "({$typeTime[$row->type_time]})" : '';
- return [
- 'total' => $total,
- 'data' => $data,
- 'title' => $row->title,
- 'unit' => $row->unit,
- ];
- }
- }
|