def UploadINDUSTRY(path, c):
c.p('正在启动校验程序...')
c.sleep(1)
E = ExcelLoader.ExcelLoader(path, 0)
first_row = E.row_values(0)
industry_index = {}
for i in ["行业名称", "关键字"]:
if i not in first_row:
c.p('校验失败，' + i + '字段不存在！', 2)
return
else:
industry_index[i] = first_row.index(i)
c.p('校验通过，正在启动解析程序...')
results = []
for i in range(1, E.row_size):
row = E.row_values(i)
vo = {
"行业名称": row[industry_index['行业名称']],
"关键字": row[industry_index['关键字']]
}
results.append(vo)
c.p('解析成功，正在启动上传程序...')
try:
M = MySQLConnector.MySQLConnector()
M.execute('TRUNCATE TABLE industry')
M.commit()
M.upload_dir('industry', results)
c.p('数据上传成功', 3)
except:
c.p('数据上传失败，请检查网络连接或联系管理员!', 2)
def UploadMARKETING(path, c):
c.p('正在启动校验程序...')
c.sleep(1)
E = ExcelLoader.ExcelLoader(path, 0)
first_row = E.row_values(0)
market_index = {}
for i in ['手机号码', '活动标签']:
if i not in first_row:
c.p('校验失败，' + i + '字段不存在！', 2)
return
else:
market_index[i] = first_row.index(i)
c.p('校验成功，正在启动解析程序...')
results = []
for i in range(1, E.row_size):
row = E.row_values(i)
try:
a = '%.0f' % int(row[market_index["手机号码"]])
except:
a = row[market_index["手机号码"]]
vo = {"计费号码": a, "活动标签": row[market_index["活动标签"]]}
results.append(vo)
c.p('文件解析成功,正在启动上传程序')
try:
M = MySQLConnector.MySQLConnector()
M.upload_dir('marketing', results)
M.close()
c.p('数据上传成功!', 3)
except:
c.p('数据上传失败，请检查网络连接或联系管理员', 2)
return
def UploadCAPITAL(path, c):
'''
上传综资清单
:param path:
:param c:
:return:
'''
c.p('正在启动校验程序...')
c.sleep(1)
E = ExcelLoader.ExcelLoader(path, 0)
first_row = E.row_values(0)
capital_index = {}
for i in ['产品实例', '日期']:
if i not in first_row:
c.p('校验失败，未包含' + i + '字段', 2)
return
else:
capital_index[i] = first_row.index(i)
c.p('校验成功，正在启动解析程序...')
results = []
for i in range(1, E.row_size):
row = E.row_values(i)
try:
a = '%.0f' % int(row[capital_index["产品实例"]])
except:
a = row[capital_index["产品实例"]]
vo = {
"产品实例":
a,
"日期":
xldate_as_datetime(row[capital_index["日期"]],
0).strftime("%Y-%m-%d")
}
results.append(vo)
c.p('文件解析成功,正在启动上传程序')
try:
M = MySQLConnector.MySQLConnector()
M.execute("DELETE FROM capital WHERE `日期` = %s", results[0]["日期"])
M.commit()
M.upload_dir('capital', results)
M.close()
c.p('数据上传成功！', 3)
except:
c.p('数据上传失败，请检查网络连接或联系管理员！', 2)
return
def UploadINDUSTRY_t(path):
E = ExcelLoader.ExcelLoader(path, 0)
first_row = E.row_values(0)
industry_index = {}
for i in ["行业名称", "关键字"]:
if i not in first_row:
return
else:
industry_index[i] = first_row.index(i)
results = []
for i in range(1, E.row_size):
row = E.row_values(i)
vo = {
"行业名称": row[industry_index['行业名称']],
"关键字": row[industry_index['关键字']]
}
results.append(vo)
try:
M = MySQLConnector.MySQLConnector()
M.execute('TRUNCATE TABLE industry')
M.commit()
M.upload_dir('industry', results)
except:
return
def Calcullate_RO(dateMonth, c):
c.p('正在检测日期月份是否合规')
try:
this_month = datetime.strptime(dateMonth + "-1",
"%Y-%m-%d").strftime("%Y-%m-%d")
last_month = getLastMonth(this_month)
last_quarter = getLastQuarter(this_month)
last_year = getLastYear(this_month)
print(last_month, last_quarter, last_year)
c.p('日期月份格式正确，当前计算的日期月份为：' + dateMonth)
except:
c.p('填写的月份格式错误，标准格式为:2018-12', 2)
return
c.p('正在连接数据库拉取清单数据...')
M = MySQLConnector.MySQLConnector()
sql = 'SELECT `集团编号`,`计费号码`,`产品实例`,`区县-按集团归属`,`区县-按客户经理`,`集团归属地市`,`集团名称`,`集团等级`,`专线类型`,`集团专线收入` FROM ro_list WHERE `数据日期` = '
try:
this_month_data = M.query_dir(sql + '"' + this_month + '"')
last_year_data = M.query_dir(sql + '"' + last_year + '"')
last_quarter_data = M.query_dir(sql + '"' + last_quarter + '"')
last_month_data = M.query_dir(sql + '"' + last_month + '"')
capital_data = [
i['产品实例'] for i in
M.query_dir('SELECT `产品实例`,`日期` FROM capital WHERE `日期` = "' +
this_month + '"')
]
marketing_data = {}
for i in M.query_dir('SELECT `计费号码`,`活动标签` FROM marketing'):
marketing_data[i["计费号码"]] = i['活动标签']
industry_data = {}
M.execute('SELECT DISTINCT `行业名称` FROM industry')
industry_all = [i[0] for i in M.fetall()]
for i in industry_all:
M.execute('SELECT `关键字` FROM industry WHERE `行业名称`="' + i + '"')
industry_data[i] = [i[0] for i in M.fetall()]
M.execute(
'''
SELECT
`集团编号`,
`数据日期`,
COUNT(DISTINCT `产品实例`)
FROM
ro_list
GROUP BY
`集团编号`,
`数据日期`
HAVING
COUNT(`产品实例`) >= 10
AND `数据日期` = %s
''', this_month)
big_pro_ids = [l[0] for l in M.fetall()]
c.p('数据拉取成功，正在执行计算，消耗时间较长请勿关闭程序...')
except:
c.p('数据拉取失败，请检查网络连接或联系管理员', 2)
return
# 生成产品实例编码列表
this_ids = [i['产品实例'] for i in this_month_data]
year_ids = [i['产品实例'] for i in last_year_data]
quarter_ids = [i['产品实例'] for i in last_quarter_data]
month_ids = [i['产品实例'] for i in last_month_data]
results = []
# 开始遍历本月清单
for i in this_month_data:
vo_year = {**i}
vo_year['状态口径'] = "年度"
vo_year['数据日期'] = this_month
vo_month = {**i}
vo_month['状态口径'] = "月度"
vo_month['数据日期'] = this_month
vo_quarter = {**i}
vo_quarter['状态口径'] = "季度"
vo_quarter['数据日期'] = this_month
if i['产品实例'] in year_ids:
vo_year['状态'] = "保有"
else:
vo_year['状态'] = "新增"
if i['产品实例'] in month_ids:
vo_month['状态'] = "保有"
else:
vo_month['状态'] = "新增"
if i['产品实例'] in quarter_ids:
vo_quarter['状态'] = "保有"
else:
vo_quarter['状态'] = "新增"
results.append(vo_month)
results.append(vo_year)
results.append(vo_quarter)
# 开始遍历上月清单
for i in last_month_data:
vo = {**i}
vo['状态口径'] = "月度"
vo['数据日期'] = this_month
if i['产品实例'] not in this_ids:
vo['状态'] = "离网"
results.append(vo)
# 开始遍历上季度清单
for i in last_quarter_data:
vo = {**i}
vo['状态口径'] = "季度"
vo['数据日期'] = this_month
if i['产品实例'] not in this_ids:
vo['状态'] = "离网"
results.append(vo)
# 开始遍历上年清单
for i in last_year_data:
vo = {**i}
vo['状态口径'] = "年度"
vo['数据日期'] = this_month
if i['产品实例'] not in this_ids:
vo['状态'] = "离网"
results.append(vo)
# 遍历结果数组
for i in results:
# 数据一致
if i['状态'] != "离网":
if i['产品实例'] in capital_data:
i['数据一致'] = '已匹配'
else:
i['数据一致'] = '未匹配'
else:
i['数据一致'] = '-'
# 营销活动
if i['计费号码'] in marketing_data.keys():
i["营销活动"] = marketing_data[i["计费号码"]]
else:
i['营销活动'] = "无营销活动记录"
# 行业
i["行业"] = "其他"
for j in industry_data:
for k in industry_data[j]:
if k in i["集团名称"]:
i["行业"] = j
# 大项目
if i['产品实例'] in big_pro_ids:
i["是否大项目"] = '是'
else:
i['是否大项目'] = '否'
c.p('计算完成，正在将计算结果保存至数据库...')
try:
M.execute('DELETE FROM ro_results WHERE `数据日期`=%s', this_month)
M.commit()
M.upload_dir('ro_results', results)
c.p('结果数据保存成功！', 3)
except:
c.p('结果数据保存失败，请联系管理员！', 2)
return
def UploadRo(path, c):
c.p('正在启动校验程序...')
c.sleep(1)
E = ExcelLoader.ExcelLoader(path, 0)
validata = {
'数据日期': -1,
'集团编号': -1,
'计费号码': -1,
'产品实例': -1,
'区县-按集团归属': -1,
'区县-按客户经理': -1,
'集团归属地市': -1,
'地市编号': -1,
'集团名称': -1,
'集团等级': -1,
'专线类型': -1,
'专线办理时间': -1,
'专线归档时间': -1,
'带宽': -1,
'当月出账': -1,
'集团专线收入': -1
}
first_row = E.row_values(0)
for i in validata:
for j, k in enumerate(first_row):
if i in k:
validata[i] = j
for i in validata:
if validata[i] == -1:
c.p('校验未通过，"' + i + '"字段不存在或书写错误！', 2)
return
c.sleep(1)
c.p('校验通过，正在启动解析程序...')
results = []
for row_index in range(1, E.row_size):
row = E.row_values(row_index)
vo = {}
for i in validata:
if i in ["数据日期", "专线办理时间", "专线归档时间"]:
try:
vo[i] = xldate_as_datetime(row[validata[i]],
0).strftime("%Y-%m-%d")
except:
vo[i] = "1900-1-1"
else:
if i in ['当月出账', '集团专线收入']:
try:
vo[i] = float(row[validata[i]])
except:
vo[i] = 0
elif i in ['集团编号', '计费号码', '产品实例']:
try:
a = '%.0f' % int(row[validata[i]])
except:
a = row[validata[i]]
vo[i] = a
else:
vo[i] = row[validata[i]]
results.append(vo)
print(results)
c.p('数据解析成功，正在连接数据库...')
try:
M = MySQLConnector.MySQLConnector()
c.p('连接成功正在上传数据...')
except:
c.p('连接失败，请检查网络连接或联系管理员！', 2)
return
try:
M.execute("DELETE FROM ro_list WHERE `数据日期` = %s", results[0]['数据日期'])
M.commit()
M.upload_dir('ro_list', results)
M.close()
c.p('数据上传成功！', 3)
except:
c.p('数据上传失败，程序异常退出请联系管理员...', 2)
return
from flask import Flask, request, jsonify
from flask_restful import Resource, Api
from service import UserListService, UserModifyService, UserInsertService, UserDeleteService
from util import MySQLConnector
app = Flask(__name__)
api = Api(app)
connector = MySQLConnector(host='localhost',
database='user_manager',
port='3306',
user='******',
password='******')
connector.connect()
api.add_resource(UserListService,
'/user/<string:user_id>',
resource_class_kwargs={'database_connector': connector.con})
api.add_resource(UserInsertService,
'/user/register',
resource_class_kwargs={'database_connector': connector.con})
api.add_resource(UserModifyService,
'/user/change',
resource_class_kwargs={'database_connector': connector.con})
api.add_resource(UserDeleteService,
'/user/delete/<string:user_id>',
resource_class_kwargs={'database_connector': connector.con})
if __name__ == '__main__':
app.run(debug=True)