338 lines
12 KiB
Python
338 lines
12 KiB
Python
# -*- coding:utf-8 -*-
|
||
"""
|
||
Author: 陈江
|
||
Email: chenjiang@sparkedu.com
|
||
Create Date: 2022/09/23 2:06 下午
|
||
"""
|
||
from base_framework.public_tools.sqlhelper import MySqLHelper
|
||
|
||
import json
|
||
|
||
obj_mysql_helper = MySqLHelper()
|
||
|
||
|
||
class CoursePackage:
|
||
def __init__(self):
|
||
pass
|
||
|
||
@staticmethod
|
||
def __get_data_name(data_list):
|
||
temp_data_list = []
|
||
if data_list:
|
||
for data in data_list:
|
||
temp_data_list.append(
|
||
{"course_package_id": data.get('value'), "cc显示_course_package_name": data.get('name'),
|
||
"course_package_name": data.get('text')})
|
||
return temp_data_list
|
||
return None
|
||
|
||
def get_course_package_by_cc_crm(self, post_data_input):
|
||
"""
|
||
| 功能说明： | 获取套餐信息，根据cc/crm购买可选套餐 |
|
||
| 输入参数： |
|
||
| 作者信息： | 陈江 | 修改时间 | 2022-9-28 |
|
||
"""
|
||
sql_crm_vispark_subscribe_chinese = """SELECT
|
||
IF (abbreviation="",NAME,abbreviation) AS 'text',name,
|
||
id AS 'value',
|
||
type,business_line,business_line_type,teaching_method,sale_status,saleable_status,course_subject,course_type,sale_type,course_teaching_method,STATUS,view_authority_scope
|
||
FROM
|
||
peppa.course_package
|
||
WHERE
|
||
type = 1
|
||
AND business_line = 1
|
||
AND business_line_type = 2
|
||
AND teaching_method = 0
|
||
AND sale_status = 1
|
||
AND saleable_status = 1
|
||
AND course_subject = ',2,'
|
||
AND course_type IN ( ',5,', ',1,' )
|
||
AND sale_type = 2
|
||
AND course_teaching_method = 1
|
||
AND STATUS = 1
|
||
AND view_authority_scope LIKE '%,7,%'
|
||
ORDER BY
|
||
id DESC LIMIT 3;"""
|
||
crm_vispark_subscribe_chinese_list = self.__get_data_name(
|
||
obj_mysql_helper.select_all(sql_crm_vispark_subscribe_chinese))
|
||
|
||
sql_crm_vispark_subscribe_sg = """SELECT
|
||
IF (abbreviation="",NAME,abbreviation) AS 'text',name,
|
||
id AS 'value',
|
||
type,business_line,business_line_type,teaching_method,sale_status,saleable_status,course_subject,course_type,sale_type,course_teaching_method,STATUS,view_authority_scope
|
||
FROM
|
||
peppa.course_package
|
||
WHERE
|
||
type = 1
|
||
AND business_line = 1
|
||
AND business_line_type = 1
|
||
AND teaching_method = 0
|
||
AND sale_status = 1
|
||
AND saleable_status = 1
|
||
AND course_subject = ',1,'
|
||
AND course_type IN ( ',5,' )
|
||
AND sale_type = 2
|
||
AND course_teaching_method = 1
|
||
AND STATUS = 1
|
||
AND view_authority_scope LIKE '%,10,%'
|
||
ORDER BY
|
||
id DESC limit 3;"""
|
||
crm_vispark_subscribe_sg_list = self.__get_data_name(
|
||
obj_mysql_helper.select_all(sql_crm_vispark_subscribe_sg))
|
||
|
||
sql_crm_vispark_subscribe_oc = """SELECT
|
||
IF (abbreviation="",NAME,abbreviation) AS 'text',name,
|
||
id AS 'value',
|
||
type,business_line,business_line_type,teaching_method,sale_status,saleable_status,course_subject,course_type,sale_type,course_teaching_method,STATUS,view_authority_scope
|
||
FROM
|
||
peppa.course_package
|
||
WHERE
|
||
type = 1
|
||
AND business_line = 1
|
||
AND business_line_type in( 1,10 )
|
||
AND teaching_method = 0
|
||
AND sale_status = 1
|
||
AND saleable_status = 1
|
||
AND course_subject = ',1,'
|
||
AND course_type IN ( ',5,' )
|
||
AND sale_type = 2
|
||
AND course_teaching_method = 1
|
||
AND STATUS = 1
|
||
AND view_authority_scope LIKE '%,13,%'
|
||
ORDER BY
|
||
id DESC limit 3;"""
|
||
crm_vispark_subscribe_oc_list = self.__get_data_name(
|
||
obj_mysql_helper.select_all(sql_crm_vispark_subscribe_oc))
|
||
|
||
sql_crm_vispark_buy_chinese = """SELECT
|
||
IF (abbreviation="",NAME,abbreviation) AS 'text',name,
|
||
id AS 'value',
|
||
type,business_line,business_line_type,teaching_method,sale_status,saleable_status,course_subject,course_type,sale_type,course_teaching_method,STATUS,view_authority_scope
|
||
FROM
|
||
peppa.course_package
|
||
WHERE
|
||
type = 1
|
||
AND business_line = 1
|
||
AND business_line_type = 2
|
||
AND teaching_method = 0
|
||
AND sale_status = 1
|
||
AND saleable_status = 1
|
||
AND course_subject = ',2,'
|
||
AND course_type IN ( ',5,', ',1,', ',1,5,', ',5,1,')
|
||
AND sale_type = 1
|
||
AND course_teaching_method = 1
|
||
AND STATUS = 1
|
||
AND view_authority_scope LIKE '%,7,%'
|
||
ORDER BY
|
||
IF (abbreviation="",NAME,abbreviation) asc limit 3;"""
|
||
crm_vispark_buy_chinese_list = self.__get_data_name(
|
||
obj_mysql_helper.select_all(sql_crm_vispark_buy_chinese))
|
||
|
||
sql_crm_vispark_buy_us = """SELECT
|
||
IF (abbreviation="",NAME,abbreviation) AS 'text',name,
|
||
id AS 'value',
|
||
type,business_line,business_line_type,teaching_method,sale_status,saleable_status,course_subject,course_type,sale_type,course_teaching_method,STATUS,view_authority_scope
|
||
FROM
|
||
peppa.course_package
|
||
WHERE
|
||
type = 1
|
||
AND business_line = 1
|
||
AND business_line_type = 10
|
||
AND teaching_method = 0
|
||
AND sale_status = 1
|
||
AND saleable_status = 1
|
||
AND course_subject = ',1,'
|
||
AND course_type IN ( ',5,', ',1,', ',1,5,', ',5,1,')
|
||
AND sale_type = 1
|
||
AND course_teaching_method = 1
|
||
AND STATUS = 1
|
||
AND view_authority_scope LIKE '%,11,%'
|
||
ORDER BY
|
||
id DESC limit 3;"""
|
||
crm_vispark_buy_us_list = self.__get_data_name(
|
||
obj_mysql_helper.select_all(sql_crm_vispark_buy_us))
|
||
|
||
sql_crm_vispark_buy_canada = """SELECT
|
||
IF (abbreviation="",NAME,abbreviation) AS 'text',name,
|
||
id AS 'value',
|
||
type,business_line,business_line_type,teaching_method,sale_status,saleable_status,course_subject,course_type,sale_type,course_teaching_method,STATUS,view_authority_scope
|
||
FROM
|
||
peppa.course_package
|
||
WHERE
|
||
type = 1
|
||
AND business_line = 1
|
||
AND business_line_type = 10
|
||
AND teaching_method = 0
|
||
AND sale_status = 1
|
||
AND saleable_status = 1
|
||
AND course_subject = ',1,'
|
||
AND course_type IN ( ',5,', ',1,', ',1,5,', ',5,1,')
|
||
AND sale_type = 1
|
||
AND course_teaching_method = 1
|
||
AND STATUS = 1
|
||
AND view_authority_scope LIKE '%,1012,%'
|
||
ORDER BY
|
||
id DESC limit 3;"""
|
||
crm_vispark_buy_canada_list = self.__get_data_name(
|
||
obj_mysql_helper.select_all(sql_crm_vispark_buy_canada))
|
||
|
||
sql_crm_vispark_buy_sg = """SELECT
|
||
IF (abbreviation="",NAME,abbreviation) AS 'text',name,
|
||
id AS 'value',
|
||
type,business_line,business_line_type,teaching_method,sale_status,saleable_status,course_subject,course_type,sale_type,course_teaching_method,STATUS,view_authority_scope
|
||
FROM
|
||
peppa.course_package
|
||
WHERE
|
||
type = 1
|
||
AND business_line = 1
|
||
AND business_line_type = 1
|
||
AND teaching_method = 0
|
||
AND sale_status = 1
|
||
AND saleable_status = 1
|
||
AND course_subject = ',1,'
|
||
AND course_type IN ( ',5,', ',1,', ',1,5,', ',5,1,')
|
||
AND sale_type = 1
|
||
AND course_teaching_method = 1
|
||
AND STATUS = 1
|
||
AND view_authority_scope LIKE '%,10,%'
|
||
ORDER BY
|
||
id DESC limit 3;"""
|
||
crm_vispark_buy_sg_list = self.__get_data_name(
|
||
obj_mysql_helper.select_all(sql_crm_vispark_buy_sg))
|
||
|
||
sql_crm_vispark_buy_oc = """SELECT
|
||
IF (abbreviation="",NAME,abbreviation) AS 'text',name,
|
||
id AS 'value',
|
||
type,business_line,business_line_type,teaching_method,sale_status,saleable_status,course_subject,course_type,sale_type,course_teaching_method,STATUS,view_authority_scope
|
||
FROM
|
||
peppa.course_package
|
||
WHERE
|
||
type = 1
|
||
AND business_line = 1
|
||
AND business_line_type in( 1,10 )
|
||
AND teaching_method = 0
|
||
AND sale_status = 1
|
||
AND saleable_status = 1
|
||
AND course_subject = ',1,'
|
||
AND course_type IN ( ',5,', ',1,', ',1,5,', ',5,1,')
|
||
AND sale_type = 1
|
||
AND course_teaching_method = 1
|
||
AND STATUS = 1
|
||
AND view_authority_scope LIKE '%,13,%'
|
||
ORDER BY
|
||
id DESC limit 3;"""
|
||
crm_vispark_buy_oc_list = self.__get_data_name(
|
||
obj_mysql_helper.select_all(sql_crm_vispark_buy_oc))
|
||
|
||
sql_crm_vispark_buy_huohua = """SELECT
|
||
IF (abbreviation="",NAME,abbreviation) AS 'text',name,
|
||
id AS 'value',
|
||
type,business_line,business_line_type,teaching_method,sale_status,saleable_status,course_subject,course_type,sale_type,course_teaching_method,STATUS,view_authority_scope
|
||
FROM
|
||
peppa.course_package
|
||
WHERE
|
||
teaching_method = 0
|
||
AND business_line_type in( 0,4,9 )
|
||
-- AND business_line = 0 (以及业务线)
|
||
AND sale_status = 1
|
||
AND saleable_status = 1
|
||
AND course_subject like '%,2,%'
|
||
-- AND course_type IN ( ',1,',',5,' ) （课程类型）
|
||
AND sale_type = 1
|
||
AND course_teaching_method = 1
|
||
-- AND STATUS = 1 （状态）
|
||
AND view_authority_scope LIKE '%,7,%'
|
||
ORDER BY
|
||
id DESC limit 3;"""
|
||
crm_vispark_buy_huohua_list = self.__get_data_name(
|
||
obj_mysql_helper.select_all(sql_crm_vispark_buy_huohua))
|
||
|
||
sql_crm_vispark_subscribe_huohua = """SELECT
|
||
IF (abbreviation="",NAME,abbreviation) AS 'text',name,
|
||
id AS 'value',
|
||
type,business_line,business_line_type,teaching_method,sale_status,saleable_status,course_subject,course_type,sale_type,course_teaching_method,STATUS,view_authority_scope
|
||
FROM
|
||
peppa.course_package
|
||
WHERE
|
||
teaching_method = 0
|
||
AND business_line_type in( 0,4,9 )
|
||
AND business_line = 1
|
||
AND sale_status = 1
|
||
AND saleable_status = 1
|
||
AND course_subject like '%,2,%'
|
||
-- AND course_type IN ( ',1,',',5,' ) （课程类型）
|
||
AND sale_type = 2
|
||
AND course_teaching_method = 1
|
||
-- AND STATUS = 1 （状态）
|
||
AND view_authority_scope LIKE '%,7,%'
|
||
ORDER BY
|
||
id DESC limit 3;"""
|
||
crm_vispark_subscribe_huohua_list = self.__get_data_name(
|
||
obj_mysql_helper.select_all(sql_crm_vispark_subscribe_huohua))
|
||
|
||
sql_cc_vispark_subscribe_huohua = """SELECT
|
||
IF (abbreviation="",NAME,abbreviation) AS 'text',name,
|
||
id AS 'value',
|
||
type,business_line,business_line_type,teaching_method,sale_status,saleable_status,course_subject,course_type,sale_type,course_teaching_method,STATUS,view_authority_scope
|
||
FROM
|
||
peppa.course_package
|
||
WHERE
|
||
teaching_method = 0
|
||
AND business_line_type in( 0,3,9 )
|
||
AND business_line = 1
|
||
AND sale_status = 1
|
||
AND saleable_status = 1
|
||
AND course_subject like '%,1,%'
|
||
-- AND course_type IN ( ',1,',',5,' ) （课程类型）
|
||
AND sale_type = 2
|
||
AND course_teaching_method = 1
|
||
-- AND STATUS = 1 （状态）
|
||
AND view_authority_scope LIKE '%,2,%'
|
||
ORDER BY
|
||
id DESC limit 3;"""
|
||
cc_vispark_subscribe_huohua_list = self.__get_data_name(
|
||
obj_mysql_helper.select_all(sql_cc_vispark_subscribe_huohua))
|
||
|
||
sql_cc_vispark_buy_huohua = """SELECT
|
||
IF (abbreviation="",NAME,abbreviation) AS 'text',name,
|
||
id AS 'value',
|
||
type,business_line,business_line_type,teaching_method,sale_status,saleable_status,course_subject,course_type,sale_type,course_teaching_method,STATUS,view_authority_scope
|
||
FROM
|
||
peppa.course_package
|
||
WHERE
|
||
teaching_method = 0
|
||
AND business_line_type in( 0,3,9 )
|
||
AND business_line = 1
|
||
AND sale_status = 1
|
||
AND saleable_status = 1
|
||
AND course_subject like '%,1,%'
|
||
-- AND course_type IN ( ',1,',',5,' ) （课程类型）
|
||
AND sale_type = 1
|
||
AND course_teaching_method = 1
|
||
-- AND STATUS = 1 （状态）
|
||
AND view_authority_scope LIKE '%,2,%'
|
||
ORDER BY
|
||
id DESC limit 3;"""
|
||
cc_vispark_buy_huohua_list = self.__get_data_name(
|
||
obj_mysql_helper.select_all(sql_cc_vispark_buy_huohua))
|
||
data = {"crm中订阅vispark业务的对外汉语-套餐": crm_vispark_subscribe_chinese_list,
|
||
"crm中订阅vispark业务的sg-套餐": crm_vispark_subscribe_sg_list,
|
||
"crm中订阅vispark业务的海华-套餐": crm_vispark_subscribe_oc_list,
|
||
"crm中直购vispark业务的对外汉语-套餐": crm_vispark_buy_chinese_list,
|
||
"crm中直购vispark业务的us-套餐": crm_vispark_buy_us_list,
|
||
"crm中直购vispark业务的加拿大-套餐": crm_vispark_buy_canada_list,
|
||
"crm中直购vispark业务的sg-套餐": crm_vispark_buy_sg_list,
|
||
"crm中直购vispark业务的海华-套餐": crm_vispark_buy_oc_list,
|
||
"crm中直购vispark业务的火花-套餐": crm_vispark_buy_huohua_list,
|
||
"crm中订阅vispark业务的火花-套餐": crm_vispark_subscribe_huohua_list,
|
||
"cc中订阅vispark业务的火花-套餐": cc_vispark_subscribe_huohua_list,
|
||
"cc中直购vispark业务的火花-套餐": cc_vispark_buy_huohua_list}
|
||
|
||
return data
|
||
|
||
|
||
if __name__ == '__main__':
|
||
cc = CoursePackage()
|
||
bb = cc.get_course_package_by_cc_crm('')
|
||
print(bb)
|