Skip to main content

详情页功能实现

目录结构如下：

├── app
│   ├── __init__.py
│   ├── models.py
│   ├── routes.py
│   └── templates
│   │   ├── base.html
│   │   ├── about.html
│   │   ├── index.html
│   │   ├── detail.html (新增)
│   ├── static
│   │   ├── css
│   │   ├── images
│   │   └── js
├── manage.py
├── migrations
└── venv

详情页获取课程信息​

routes.py代码如下：

@app.route('/course/<course_id>')
def detail(course_id):
    course = Course.query.filter_by(course_id=course_id).first()
    return render_template('detail.html',course=course)

detail.html模板代码如下：

{% extends 'base.html' %}

{% block content%}

<div class="container">
    <div class="row" style="margin:20px auto;">
        <div class="col-sm-6">
            <img src="{{ course.big_img_url }}" class="card-img-top"
                 alt="..." width="450px" height="260px">
        </div>
        <div class="col-sm-6">
            <h4>{{ course.product_name }}</h4>
            <div class="course-info">课程讲师：{{ course.lector_name }}</div>
            <div class="course-info">所属机构：{{ course.provider }}</div>
            <div class="course-info">课程评分：{{ course.score }}</div>
            <div class="course-info">学习人数：{{ course.learner_count}}</div>
            <div class="course-price">
                {% if course.discount_price %}
                    ￥{{course.discount_price}}
                    <span style="color: #666666;font-size: 14px;text-decoration: line-through;">￥{{course.original_price}}</span>
                {% elif not course.original_price  %}
                    免费
                {% else %}
                    ￥{{course.original_price}}
                {% endif %}
            </div>
        </div>
    </div>
    <div class="col-sm">
    <ul class="nav nav-tabs" id="myTab" role="tablist">
      <li class="nav-item">
        <a class="nav-link active" id="home-tab" data-toggle="tab" href="#home" role="tab" aria-controls="课程介绍" aria-selected="true">课程介绍</a>
      </li>
      <li class="nav-item">
        <a class="nav-link" id="week-tab" data-toggle="tab" href="#week" role="tab" aria-controls="本周销量" aria-selected="false">最近一周销量</a>
      </li>
      <li class="nav-item">
        <a class="nav-link" id="month-tab" data-toggle="tab" href="#month" role="tab" aria-controls="本月销量" aria-selected="true">最近一个月销量</a>
      </li>
      <li class="nav-item">
        <a class="nav-link" id="year-tab" data-toggle="tab" href="#year" role="tab" aria-controls="年度销量" aria-selected="false">每月销量</a>
      </li>
    </ul>
    <div class="tab-content" id="myTabContent" style="min-height:260px;padding:20px;background:#fbfbfb;">
      <div class="tab-pane fade show active" id="home" role="tabpanel" aria-labelledby="home-tab">
          {% for line in course.description.splitlines() %}
                {{ line }} <br>
          {% endfor %}
      </div>
      <div class="tab-pane fade" id="week" role="tabpanel" aria-labelledby="week-tab">
          <div id="sale-week" style="width: 1000px;height:400px;"></div>
      </div>
      <div class="tab-pane fade" id="month" role="tabpanel" aria-labelledby="month-tab">
          <div id="sale-month" style="width: 1000px;height:400px;"></div>
      </div>
      <div class="tab-pane fade" id="year" role="tabpanel" aria-labelledby="year-tab">
          <div id="sale-year" style="width: 1000px;height:400px;"></div>
      </div>
    </div>
    </div>
</div>

{% endblock %}

Echarts基本使用​

官方网址： https://echarts.apache.org/zh/index.html

效果示例： https://echarts.apache.org/examples/zh/index.html

新版echarts不再提供社区图形了。

echarts基础示例html代码：

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
    <script src="https://cdn.bootcdn.net/ajax/libs/echarts/4.7.0/echarts-en.common.js"></script>
</head>
<body>
    <!-- 为 ECharts 准备一个定义了宽高的 DOM -->
    <div id="main" style="width: 600px;height:400px;"></div>
    <script type="text/javascript">
      // 基于准备好的dom，初始化echarts实例
      var myChart = echarts.init(document.getElementById('main'));

      // 指定图表的配置项和数据
      var option = {
        title: {
          text: 'ECharts 入门示例'
        },
        tooltip: {},
        legend: {
          data: ['销量']
        },
        xAxis: {
          data: ['衬衫', '羊毛衫', '雪纺衫', '裤子', '高跟鞋', '袜子']
        },
        yAxis: {},
        series: [
          {
            name: '销量',
            type: 'bar',
            data: [5, 20, 36, 10, 10, 20]
          }
        ]
      };

      // 使用刚指定的配置项和数据显示图表。
      myChart.setOption(option);
    </script>
  </body>
</html>

echarts折线图代码：

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
    <script src="https://cdn.bootcdn.net/ajax/libs/echarts/4.7.0/echarts-en.common.js"></script>
</head>
<body>
    <!-- 为 ECharts 准备一个具备大小（宽高）的 DOM -->
    <div id="main" style="width: 600px;height:400px;"></div>
    <script type="text/javascript">
        // 基于准备好的dom，初始化echarts实例
        var myChart = echarts.init(document.getElementById('main'));

        // 指定图表的配置项和数据
        option = {
    backgroundColor: new echarts.graphic.LinearGradient(0, 0, 0, 1, [{
            offset: 0,
            color: '#c86589'
        },
        {
            offset: 1,
            color: '#06a7ff'
        }
    ], false),
    title: {
        text: "OCTOBER 2015",
        left: "center",
        bottom: "5%",
        textStyle: {
            color: "#fff",
            fontSize: 16
        }
    },
    grid: {
        top: '20%',
        left: '10%',
        right: '10%',
        bottom: '15%',
        containLabel: true,
    },
    xAxis: {
        type: 'category',
        boundaryGap: false,
        data: ['14', '15', '16', '17', '18', '19', '20', '21', '22', '23'],
        axisLabel: {
            margin: 30,
            color: '#ffffff63'
        },
        axisLine: {
            show: false
        },
        axisTick: {
            show: true,
            length: 25,
            lineStyle: {
                color: "#ffffff1f"
            }
        },
        splitLine: {
            show: true,
            lineStyle: {
                color: '#ffffff1f'
            }
        }
    },
    yAxis: [{
        type: 'value',
        position: 'right',
        axisLabel: {
            margin: 20,
            color: '#ffffff63'
        },

        axisTick: {
            show: true,
            length: 15,
            lineStyle: {
                color: "#ffffff1f",
            }
        },
        splitLine: {
            show: true,
            lineStyle: {
                color: '#ffffff1f'
            }
        },
        axisLine: {
            lineStyle: {
                color: '#fff',
                width: 2
            }
        }
    }],
    series: [{
        name: '注册总量',
        type: 'line',
        smooth: true, //是否平滑曲线显示
        showAllSymbol: true,
        symbol: 'circle',
        symbolSize: 6,
        lineStyle: {
            normal: {
                color: "#fff", // 线条颜色
            },
        },
        label: {
            show: true,
            position: 'top',
            textStyle: {
                color: '#fff',
            }
        },
        itemStyle: {
            color: "red",
            borderColor: "#fff",
            borderWidth: 3
        },
        tooltip: {
            show: false
        },
        areaStyle: {
            normal: {
                color: new echarts.graphic.LinearGradient(0, 0, 0, 1, [{
                        offset: 0,
                        color: '#eb64fb'
                    },
                    {
                        offset: 1,
                        color: '#3fbbff0d'
                    }
                ], false),
            }
        },
        data: [393, 438, 485, 631, 689, 824, 987, 1000, 1100, 1200]
    }]
};

        // 使用刚指定的配置项和数据显示图表。
        myChart.setOption(option);
    </script>
</body>
</html>

详情页折线图Tab栏切换​

detail.html代码如下：

{% extends 'base.html' %}

{% block content%}

<div class="container">
    <div class="row" style="margin:20px auto;">
        <div class="col-sm-6">
            <img src="{{ course.big_img_url }}" class="card-img-top"
                 alt="..." width="450px" height="260px">
        </div>
        <div class="col-sm-6">
            <h4>{{ course.product_name }}</h4>
            <div class="course-info">课程讲师：{{ course.lector_name }}</div>
            <div class="course-info">所属机构：{{ course.provider }}</div>
            <div class="course-info">课程评分：{{ course.score }}</div>
            <div class="course-info">学习人数：{{ course.learner_count}}</div>
            <div class="course-price">
                {% if course.discount_price %}
                    ￥{{course.discount_price}}
                    <span style="color: #666666;font-size: 14px;text-decoration: line-through;">￥{{course.original_price}}</span>
                {% elif not course.original_price  %}
                    免费
                {% else %}
                    ￥{{course.original_price}}
                {% endif %}
            </div>
        </div>
    </div>
    <div class="col-sm">
    <ul class="nav nav-tabs" id="myTab" role="tablist">
      <li class="nav-item">
        <a class="nav-link active" id="home-tab" data-toggle="tab" href="#home" role="tab" aria-controls="课程介绍" aria-selected="true">课程介绍</a>
      </li>
      <li class="nav-item">
        <a class="nav-link" id="week-tab" data-toggle="tab" href="#week" role="tab" aria-controls="本周销量" aria-selected="false">最近一周销量</a>
      </li>
      <li class="nav-item">
        <a class="nav-link" id="month-tab" data-toggle="tab" href="#month" role="tab" aria-controls="本月销量" aria-selected="true">最近一个月销量</a>
      </li>
      <li class="nav-item">
        <a class="nav-link" id="year-tab" data-toggle="tab" href="#year" role="tab" aria-controls="年度销量" aria-selected="false">每月销量</a>
      </li>
    </ul>
    <div class="tab-content" id="myTabContent" style="min-height:260px;padding:20px;background:#fbfbfb;">
      <div class="tab-pane fade show active" id="home" role="tabpanel" aria-labelledby="home-tab">
          {% for line in course.description.splitlines() %}
                {{ line }} <br>
          {% endfor %}
      </div>
      <div class="tab-pane fade" id="week" role="tabpanel" aria-labelledby="week-tab">
          <div id="sale-week" style="width: 1000px;height:400px;"></div>
      </div>
      <div class="tab-pane fade" id="month" role="tabpanel" aria-labelledby="month-tab">
          <div id="sale-month" style="width: 1000px;height:400px;"></div>
      </div>
      <div class="tab-pane fade" id="year" role="tabpanel" aria-labelledby="year-tab">
          <div id="sale-year" style="width: 1000px;height:400px;"></div>
      </div>
    </div>
    </div>
</div>

<script src="https://cdn.bootcdn.net/ajax/libs/echarts/4.7.0/echarts-en.common.js"></script>
    <script type="text/javascript">
        $('.nav-link').click(function(){
            var id = $(this).attr('id')
            var type =  id.split("-")[0]
            var echarts_id = 'sale-' + type

            // 基于准备好的dom，初始化echarts实例
            var myChart = echarts.init(document.getElementById(echarts_id));

            // 指定图表的配置项和数据
            myChart.setOption({
                backgroundColor: new echarts.graphic.LinearGradient(0, 0, 0, 1, [{
                        offset: 0,
                        color: '#c86589'
                    },
                    {
                        offset: 1,
                        color: '#06a7ff'
                    }
                ], false),
                title: {
                    text: "OCTOBER 2015",
                    left: "center",
                    bottom: "5%",
                    textStyle: {
                        color: "#fff",
                        fontSize: 16
                    }
                },
                grid: {
                    top: '20%',
                    left: '10%',
                    right: '10%',
                    bottom: '15%',
                    containLabel: true,
                },
                xAxis: {
                    type: 'category',
                    boundaryGap: false,
                    data: ['14', '15', '16', '17', '18', '19', '20', '21', '22', '23'],
                    axisLabel: {
                        margin: 30,
                        color: '#ffffff63'
                    },
                    axisLine: {
                        show: false
                    },
                    axisTick: {
                        show: true,
                        length: 25,
                        lineStyle: {
                            color: "#ffffff1f"
                        }
                    },
                    splitLine: {
                        show: true,
                        lineStyle: {
                            color: '#ffffff1f'
                        }
                    }
                },
                yAxis: [{
                    type: 'value',
                    position: 'right',
                    axisLabel: {
                        margin: 20,
                        color: '#ffffff63'
                    },

                    axisTick: {
                        show: true,
                        length: 15,
                        lineStyle: {
                            color: "#ffffff1f",
                        }
                    },
                    splitLine: {
                        show: true,
                        lineStyle: {
                            color: '#ffffff1f'
                        }
                    },
                    axisLine: {
                        lineStyle: {
                            color: '#fff',
                            width: 2
                        }
                    }
                }],
                series: [{
                    name: '注册总量',
                    type: 'line',
                    smooth: true, //是否平滑曲线显示
                    showAllSymbol: true,
                    symbol: 'circle',
                    symbolSize: 6,
                    lineStyle: {
                        normal: {
                            color: "#fff", // 线条颜色
                        },
                    },
                    label: {
                        show: true,
                        position: 'top',
                        textStyle: {
                            color: '#fff',
                        }
                    },
                    itemStyle: {
                        color: "red",
                        borderColor: "#fff",
                        borderWidth: 3
                    },
                    tooltip: {
                        show: false
                    },
                    areaStyle: {
                        normal: {
                            color: new echarts.graphic.LinearGradient(0, 0, 0, 1, [{
                                    offset: 0,
                                    color: '#eb64fb'
                                },
                                {
                                    offset: 1,
                                    color: '#3fbbff0d'
                                }
                            ], false),
                        }
                    },
                    data: [393, 438, 485, 631, 689, 824, 987, 1000, 1100, 1200]
                }]
            });

        });
    </script>


{% endblock %}

详情页折线图Ajax异步加载​

detail.html 代码如下：

<script src="https://cdn.bootcdn.net/ajax/libs/jquery/3.5.1/jquery.js"></script>
<script src="https://cdn.bootcdn.net/ajax/libs/echarts/4.7.0/echarts-en.common.js"></script>
    <script type="text/javascript">
        $('.nav-link').click(function(){
            var id = $(this).attr('id')
            var type =  id.split("-")[0]
            var echarts_id = 'sale-' + type

            // 基于准备好的dom，初始化echarts实例
            var myChart = echarts.init(document.getElementById(echarts_id));

            // 指定图表的配置项和数据
            myChart.setOption({
                backgroundColor: new echarts.graphic.LinearGradient(0, 0, 0, 1, [{
                        offset: 0,
                        color: '#c86589'
                    },
                    {
                        offset: 1,
                        color: '#06a7ff'
                    }
                ], false),
                title: {
                    text: "OCTOBER 2015",
                    left: "center",
                    bottom: "5%",
                    textStyle: {
                        color: "#fff",
                        fontSize: 16
                    }
                },
                grid: {
                    top: '20%',
                    left: '10%',
                    right: '10%',
                    bottom: '15%',
                    containLabel: true,
                },
                xAxis: {
                    type: 'category',
                    boundaryGap: false,
                    data: ['14', '15', '16', '17', '18', '19', '20', '21', '22', '23'],
                    axisLabel: {
                        margin: 30,
                        color: '#ffffff63'
                    },
                    axisLine: {
                        show: false
                    },
                    axisTick: {
                        show: true,
                        length: 25,
                        lineStyle: {
                            color: "#ffffff1f"
                        }
                    },
                    splitLine: {
                        show: true,
                        lineStyle: {
                            color: '#ffffff1f'
                        }
                    }
                },
                yAxis: [{
                    type: 'value',
                    position: 'right',
                    axisLabel: {
                        margin: 20,
                        color: '#ffffff63'
                    },

                    axisTick: {
                        show: true,
                        length: 15,
                        lineStyle: {
                            color: "#ffffff1f",
                        }
                    },
                    splitLine: {
                        show: true,
                        lineStyle: {
                            color: '#ffffff1f'
                        }
                    },
                    axisLine: {
                        lineStyle: {
                            color: '#fff',
                            width: 2
                        }
                    }
                }],
                series: [{
                    name: '注册总量',
                    type: 'line',
                    smooth: true, //是否平滑曲线显示
                    showAllSymbol: true,
                    symbol: 'circle',
                    symbolSize: 6,
                    lineStyle: {
                        normal: {
                            color: "#fff", // 线条颜色
                        },
                    },
                    label: {
                        show: true,
                        position: 'top',
                        textStyle: {
                            color: '#fff',
                        }
                    },
                    itemStyle: {
                        color: "red",
                        borderColor: "#fff",
                        borderWidth: 3
                    },
                    tooltip: {
                        show: false
                    },
                    areaStyle: {
                        normal: {
                            color: new echarts.graphic.LinearGradient(0, 0, 0, 1, [{
                                    offset: 0,
                                    color: '#eb64fb'
                                },
                                {
                                    offset: 1,
                                    color: '#3fbbff0d'
                                }
                            ], false),
                        }
                    },
                    data: [393, 438, 485, 631, 689, 824, 987, 1000, 1100, 1200]
                }]
            });

            // 异步加载数据
            var url = '/course_data/' + {{ course.course_id }} + '/type/'+ type
            $.get(url).done(function (data) {
                data = JSON.parse(data)
                // 填入数据
                myChart.setOption({
                    title: {
                        text: data.title,
                    },
                    xAxis: {
                        data: data.categories
                    },
                    series: [{
                        // 根据名字对应到相应的系列
                        name: '销量',
                        data: data.data
                    }]
                });
            });
        });
    </script>

routes.py中添加如下代码：

import json

@app.route('/course_data/<course_id>/type/<type>')
def course_data(course_id, type):
    dict_value = {
        'title': '一周销量',
        'categories': ['2023-12-27', '2023-12-28', '2023-12-29'],
        'data': [500, 600, 700]
    }
    result = json.dumps(dict_value)
    return result

详情页折线图中三种类型时间查询的SQL语句​

查询最近一周的SQL语句:

select create_time,learner_count from sale where course_id = 1004735001 and DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= date(create_time) order by create_time

注意：course_id = 1004735001 需要修改为数据表中的具体的id。 如果没有查询结果，手动修改一下这条记录的create_time, 修改为当前最近的时期。

查询最近一个月的SQL语句：

select create_time,learner_count from sale where course_id = 1004735001 and DATE_SUB(CURDATE(),INTERVAL 1 MONTH) <= date(create_time) order by create_time

查询一年

select create_time,learner_count from sale where course_id = 1004735001 and create_time in ['2023-01-01',
 '2023-02-01',
 '2023-03-01',
 '2023-04-01',
 '2023-05-01',
 '2023-06-01',
 '2023-07-01',
 '2023-08-01',
 '2023-09-01',
 '2023-10-01',
 '2023-11-01',
 '2023-12-01'] order by create_time

获取折线图中销量数据​

routes.py中修改course_data()代码如下：

import datetime
from app import db


@app.route('/course_data/<course_id>/type/<type>')
def course_data(course_id,type):
    if type == 'week':
        title = '最近一周销量'
        # 查询最近一周数据
        condition = 'DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= date(create_time)'
        sql = f'select create_time,learner_count from sale where course_id = {course_id} and {condition} order by create_time'
        sale_data = db.session.execute(sql)
    elif type == 'month':
        title = '最近一个月销量'
        # 查询最近一个月的数据
        condition = 'DATE_SUB(CURDATE(),INTERVAL 1 MONTH) <= date(create_time)'
        sql = f'select create_time,learner_count from sale where course_id = {course_id} and {condition} order by create_time'
        sale_data = db.session.execute(sql)
    else:
        title = '本年度每月销量'
        # 查询每月1号的数据
        days = []
        current_year = datetime.datetime.today().year
        for i in range(1,13):
            # 2020-01-01
            days.append(f'{current_year}-{i:02}-01')
        condition = f'create_time in {tuple(days)}'
        sql = f'select create_time,learner_count from sale where course_id = {course_id} and {condition} order by create_time'
        sale_data = db.session.execute(sql)

    data = {}
    create_time = []
    learner_count = []
    for item in sale_data:
        create_time.append(item[0].strftime('%m-%d'))
        learner_count.append(item[1])

    data['title'] = title
    data['categories'] = create_time
    data['data'] = learner_count

    # dict_value = {
    #     'title': '一周销量',
    #     'categories':['2020-1-1','2020-1-2','2020-1-3'],
    #     'data':[500,600,700]
    # }
    result = json.dumps(data)
    return result

如果提示如下错误：

sqlalchemy.exc.ArgumentError: Textual SQL expression 'select create_time,learne...' should be explicitly declared as text('select create_time,learne...')

这个错误表示在使用SQLAlchemy时,传入了一个纯文本的SQL语句,但没有用text()函数把它标记为文本。

可以这样修正routes.py中的course_data， 将每一个sql都修改为sql = text(sql)， 例如：

from sqlalchemy import desc, text

@app.route('/course_data/<course_id>/type/<type>')
def course_data(course_id,type):
    if type == 'week':
        title = '最近一周销量'
        # 查询最近一周数据
        condition = 'DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= date(create_time)'
        sql = f'select create_time,learner_count from sale where course_id = {course_id} and {condition} order by create_time'
        sql = text(sql) # 新增代码
        sale_data = db.session.execute(sql)