def task_query(cls, projectid, userid, status, iscreator):
# 0:创建,1:任务已删除,2:任务已完成
user_creator = aliased(User)
user_executor = aliased(User)
ret = Task.query.outerjoin(
user_creator, user_creator.id == Task.creator).outerjoin(
user_executor, user_executor.id == Task.executor).outerjoin(
Version, Version.id == Task.version).add_columns(
Task.id.label('id'),
Task.name.label('name'),
Task.description.label('description'),
Task.tmethod.label('tmethod'),
Task.ttype.label('ttype'),
Task.status.label('status'),
func.date_format(Task.start_time,
"%Y-%m-%d").label('start_time'),
func.date_format(Task.end_time,
"%Y-%m-%d").label('end_time'),
Task.priority.label('priority'),
Task.project_id.label('project_id'),
Version.id.label('version_id'),
Version.title.label('version_name'),
user_creator.id.label('creator_id'),
user_creator.nickname.label('creator_name'),
user_executor.id.label('executor_id'),
user_executor.nickname.label('executor_name'),
)
if projectid:
ret = ret.filter(Task.project_id == projectid)
if iscreator:
ret = ret.filter(Task.creator == userid)
else:
ret = ret.filter(Task.executor == userid)
ret = ret.filter(Task.status.in_(status)).order_by(desc(Task.id)).all()
return ret
def get_seq():
run_hist = request.json
Log.info(run_hist)
show_time_unit = '%Y,%m,%d'
yesterday = datetime.now()
tick_data = dao.query(func.date_format(CoinoneTick.create_dt, '%Y,%m,%d,%H'), func.avg(CoinoneTick.last)) \
.filter_by(currency=run_hist['currency']) \
.filter(func.date_format(CoinoneTick.create_dt, '%Y,%m,%d,%H') >= yesterday.strftime(show_time_unit))\
.group_by(func.date_format(CoinoneTick.create_dt, '%Y,%m,%d,%H')) \
.order_by(func.date_format(CoinoneTick.create_dt, '%Y,%m,%d,%H').asc()) \
.limit(24).all()
Log.debug(tick_data)
tick_list = [(time.split(','), float(tick)) for (time, tick) in tick_data]
tick_last = [float(tick) for time, tick in tick_data]
y_max = round(np.max(tick_last)) + round(np.max(tick_last) * 0.1)
y_min = round(np.min(tick_last)) - round(np.min(tick_last) * 0.1)
return json.dumps({
'status': 'OK',
'currency': run_hist['currency'],
'ticks': tick_list,
'max': y_max,
'min': y_min
})
def _query_for_requirement(cls):
return Case.query.outerjoin(
Module, Case.module_id ==
Module.id).outerjoin(User, User.id == Case.creator).outerjoin(
RequirementBindCase,
and_(RequirementBindCase.case_id == Case.id,
RequirementBindCase.status == RequirementBindCase.ACTIVE)
).outerjoin(
Requirement,
and_(RequirementBindCase.requirement_id == Requirement.id,
Requirement.status == Requirement.ACTIVE)).add_columns(
Case.id.label('id'), Case.cnumber.label('cnumber'),
Case.ctype.label('ctype'), Case.title.label('title'),
Case.precondition.label('precondition'),
Case.step_result.label('step_result'),
Case.is_auto.label('is_auto'),
Case.priority.label('priority'),
Case.status.label('status'),
func.date_format(
Case.creation_time,
"%Y-%m-%d %H:%i:%s").label('creation_time'),
func.date_format(
Case.modified_time,
"%Y-%m-%d %H:%i:%s").label('modified_time'),
Module.id.label('moduleid'),
Module.name.label('module'), User.id.label('userid'),
User.nickname.label('username'),
Requirement.id.label('requirement_id'),
Requirement.title.label('requirement_title'))
def _query(cls):
return MonkeyDeviceStatus.query.add_columns(
MonkeyDeviceStatus.id.label('id'),
MonkeyDeviceStatus.monkey_id.label('monkey_id'),
MonkeyDeviceStatus.mobile_id.label('mobile_id'),
MonkeyDeviceStatus.mobile_model.label('mobile_model'),
MonkeyDeviceStatus.mobile_serial.label('mobile_serial'),
MonkeyDeviceStatus.mobile_version.label('mobile_version'),
MonkeyDeviceStatus.process.label('process'),
MonkeyDeviceStatus.activity_count.label('activity_count'),
MonkeyDeviceStatus.activity_tested_count.label('activity_tested_count'),
MonkeyDeviceStatus.activity_all.label('activity_all'),
MonkeyDeviceStatus.activity_tested.label('activity_tested'),
MonkeyDeviceStatus.anr_count.label('anr_count'),
MonkeyDeviceStatus.crash_count.label('crash_count'),
MonkeyDeviceStatus.crash_rate.label('crash_rate'),
MonkeyDeviceStatus.exception_count.label('exception_count'),
MonkeyDeviceStatus.exception_run_time.label('exception_run_time'),
MonkeyDeviceStatus.device_connect_status.label('device_connect_status'),
MonkeyDeviceStatus.screen_lock_status.label('screen_lock_status'),
MonkeyDeviceStatus.setup_install_app_status.label('setup_install_app_status'),
MonkeyDeviceStatus.start_app_status.label('start_app_status'),
MonkeyDeviceStatus.setup_uninstall_app_status.label('setup_uninstall_app_status'),
MonkeyDeviceStatus.login_app_status.label('login_app_status'),
MonkeyDeviceStatus.running_status.label('running_status'),
MonkeyDeviceStatus.teardown_uninstall_app_status.label('teardown_uninstall_app_status'),
MonkeyDeviceStatus.current_stage.label('current_stage'),
MonkeyDeviceStatus.running_error_reason.label('running_error_reason'),
func.date_format(MonkeyDeviceStatus.begin_time, "%Y-%m-%d %H:%i:%s").label('begin_time'),
func.date_format(MonkeyDeviceStatus.end_time, "%Y-%m-%d %H:%i:%s").label('end_time'),
MonkeyDeviceStatus.run_time.label('run_time'),
MonkeyDeviceStatus.mobile_resolution.label('mobile_resolution'),
MonkeyDeviceStatus.cancel_status.label('cancel_status')
)
async def three_query(model, session, method="day", _format="%Y%m%d%H"):
"""
func.date_format(model.create_dt, format)转化日期格式，把第1个参数转化为format格式
"""
model_query = session.query(func.date_format(model.create_dt, _format),
func.min(model.percent),
func.max(model.percent),
func.avg(model.percent))
data = None
if method == "day":
# 过滤出今天的数据
# 按照小时分组
# 按照时间升序排序
data = await as_future(
model_query.filter(
func.to_days(model.create_dt) ==
func.to_days(func.now())).group_by(
func.date_format(model.create_dt, _format)).order_by(
model.create_dt.asc()).all)
if method == "month":
# 过滤出当前月的数据
# 按照天来分组
# 按照时间升序排序
data = await as_future(
model_query.filter(
func.date_format(model.create_dt, "%Y%m") ==
func.date_format(func.curdate(), "%Y%m")).group_by(
model.create_date).order_by(model.create_dt.asc()).all)
return data
def info():
device_id = request.args.to_dict().get("device_id")
ret = {}
ret['code'] = 200
ret['success'] = True
ret['result'] = {}
if not device_id:
ret['success'] = False
airs = db.session.query(
func.max(Air.humidity).label('max_humidity'),
func.min(Air.humidity).label('min_humidity'),
func.max(Air.celsius).label('max_celsius'),
func.min(Air.celsius).label('min_celsius'),
func.date_format(Air.update_date, "%Y-%m-%d").label('date')).group_by(
func.date_format(Air.update_date,
"%Y-%m-%d").label('date')).filter_by(
identification_id=device_id).all()
data = dict()
data['max_humidity'] = list()
data['min_humidity'] = list()
data['max_celsius'] = list()
data['min_celsius'] = list()
data['update_date'] = list()
for air in airs:
data['max_humidity'].append(air.max_humidity)
data['min_humidity'].append(air.min_humidity)
data['max_celsius'].append(air.max_celsius)
data['min_celsius'].append(air.min_celsius)
data['update_date'].append(air.date)
ret['result'] = data
return json.dumps(ret, ensure_ascii=False)
def getAllVideo(cls, page: int, num: int, camera_name: str,
start_date: str, end_date: str):
try:
exp_list = []
if camera_name is not None:
exp_list.append(Video.camera_name == camera_name)
if start_date is not None:
exp_list.append(
func.date_format(Video.start_date, '%Y-%m-%d %H:%i:%S') >=
start_date)
if end_date is not None:
exp_list.append(
func.date_format(Video.start_date, '%Y-%m-%d %H:%i:%S') <
end_date)
order_exp = Video.start_date.desc()
if len(exp_list) > 0:
filter_exp = and_(*exp_list)
pagination = Video.query.filter(filter_exp).order_by(
order_exp).paginate(page, num, error_out=False)
else:
pagination = Video.query.order_by(order_exp).paginate(
page, num, error_out=False)
return {
'total': pagination.total,
'pages': pagination.pages,
'has_prev': pagination.has_prev,
'has_next': pagination.has_next,
'data': [video.toDict() for video in pagination.items]
}
except Exception as e:
abort(500, "数据出错！")
def week_sum():
"""
功能: 统计近7天爬取数据量
:return: {"code": 200,
"data": {
'week_data': {
'index': ['05-26','05-24','05-23'],
'value': [5, 3, 7},
}
}
"""
try:
# 获取当前时间
localtime = datetime.datetime.now()
# 获取7天的时间
date_day = datetime.timedelta(days=7)
delta_days = localtime - date_day
# 近7天获取数据量, 按照每天分组求和, 返回的是列表
week_data = DataCounts.query.with_entities(func.date_format(DataCounts.date_created, '%m-%d'),
func.sum(DataCounts.number)).\
filter(func.date_format(DataCounts.date_created, '%Y-%m-%d') > delta_days.strftime('%Y-%m-%d')).\
group_by(DataCounts.date_created).order_by(func.date_format(DataCounts.date_created, '%Y-%m-%d')).all()
data_dict = dict(week_data=week_data)
return json.dumps({"code": 200, "data": data_dict}, cls=DecimalEncoder)
except Exception as e:
return json.dumps({
"code": 500,
"status": "error",
"msg": "本周数据获取量统计出错"
})
def _query(cls):
return Monkey.query.add_columns(
Monkey.id.label('id'),
Monkey.app_name.label('app_name'),
Monkey.app_version.label('app_version'),
Monkey.download_app_status.label('download_app_status'),
Monkey.package_name.label('package_name'),
func.date_format(Monkey.begin_time, "%Y-%m-%d %H:%i:%s").label('begin_time'),
Monkey.end_time.label('end_time'),
Monkey.user_id.label('user_id'),
Monkey.jenkins_url.label('jenkins_url'),
Monkey.report_url.label('report_url'),
Monkey.mobile_ids.label('mobile_ids'),
Monkey.parameters.label('parameters'),
Monkey.process.label('process'),
Monkey.status.label('status'),
Monkey.type_id.label('type_id'),
Monkey.run_time.label('run_time'),
Monkey.actual_run_time.label('actual_run_time'),
Monkey.app_id.label('app_id'),
func.date_format(Monkey.creation_time, "%Y-%m-%d %H:%i:%s").label('creation_time'),
Monkey.system_device.label('system_device'),
Monkey.login_required.label('login_required'),
Monkey.login_username.label('login_username'),
Monkey.login_password.label('login_password'),
Monkey.app_install_required.label('app_install_required'),
Monkey.cancel_status.label('cancel_status')
)
def _query(cls):
return PhoneRecord.query.add_columns(
PhoneRecord.id.label('id'),
PhoneRecord.phone_id.label('phone_id'),
PhoneRecord.name.label('name'),
PhoneRecord.asset_id.label('asset_id'),
PhoneRecord.vendor.label('vendor'),
func.date_format(PhoneRecord.creation_time, "%Y-%m-%d %H:%i:%s").label('creation_time'),
func.date_format(PhoneRecord.modified_time, "%Y-%m-%d %H:%i:%s").label('modified_time'),
PhoneRecord.device_number.label('device_number'),
PhoneRecord.os.label('os'),
PhoneRecord.cpu.label('cpu'),
PhoneRecord.core.label('core'),
PhoneRecord.ram.label('ram'),
PhoneRecord.rom.label('rom'),
PhoneRecord.resolution.label('resolution'),
PhoneRecord.buy_date.label('buy_date'),
PhoneRecord.region.label('region'),
PhoneRecord.status.label('status'),
PhoneRecord.borrow_id.label('borrow_id'),
PhoneRecord.creator_id.label('creator_id'),
PhoneRecord.device_source.label('device_source'),
PhoneRecord.device_belong.label('device_belong'),
PhoneRecord.editor_id.label('editor_id'),
)
def find_by_user_rowid(self, db):
return (db.query(
Sessions.session_key,
func.date_format(Sessions.created_dt,
"%Y-%m-%d %H:%i:%S").label("created_dt"),
func.date_format(Sessions.logout_dt,
"%Y-%m-%d %H:%i:%S").label("logout_dt"),
).filter(Sessions.user_rowid == self.user_rowid).order_by(
Sessions.row_id.desc()).all())
def subquery(self, session, tm, ts, startdt_str, enddt_str,
str_wodfilter) -> object:
obj = session.query(ts.study_dt).filter(
ts.member_id == tm.member_id, ts.access == "out",
ts.excluded_record.isnot(True),
F.date_format(ts.study_dt, '%Y-%m-%d') >= startdt_str,
F.date_format(ts.study_dt, '%Y-%m-%d') <= enddt_str,
ts.studytime_min.isnot(None), eval(str_wodfilter))
return obj
def _query(cls):
return JobsRecord.query.add_columns(
JobsRecord.id.label('id'),
JobsRecord.job_id.label('job_id'),
JobsRecord.result.label('result'),
JobsRecord.log.label('log'),
func.date_format(JobsRecord.creation_time, "%Y-%m-%d %H:%i:%s").label('creation_time'),
func.date_format(JobsRecord.modified_time, "%Y-%m-%d %H:%i:%s").label('modified_time'),
)
def _query(cls):
return PhoneBorrow.query.add_columns(
PhoneBorrow.id.label('id'),
PhoneBorrow.phone_id.label('phone_id'),
PhoneBorrow.user_list.label('user_list'),
PhoneBorrow.confirm_userid.label('confirm_userid'),
func.date_format(PhoneBorrow.creation_time, "%Y-%m-%d %H:%i:%s").label('creation_time'),
func.date_format(PhoneBorrow.modified_time, "%Y-%m-%d %H:%i:%s").label('modified_time'),
)
def archive():
createdates = db.session.query(
distinct(func.date_format(Article.createdate, '%Y-%m-%d'))).order_by(
func.date_format(Article.createdate, '%Y-%m-%d').desc())
articles = db.session.query(
Article.title, func.date_format(Article.createdate, '%Y-%m-%d'),
Article.id).all()
return render_template('archive.html',
createdates=createdates,
articles=articles)
def _query(cls):
return Jira.query.add_columns(
Jira.id.label('id'),
Jira.params.label('params'),
Jira.result.label('result'),
Jira.key_id.label('key_id'),
Jira.key_type.label('key_type'),
func.date_format(Jira.creation_time, "%Y-%m-%d %H:%i:%s").label('creation_time'),
func.date_format(Jira.modified_time, "%Y-%m-%d %H:%i:%s").label('modified_time'),
)
def _query(cls):
return CreditRecord.query.add_columns(
CreditRecord.id.label('id'),
CreditRecord.user_id.label('user_id'),
CreditRecord.score.label('score'),
CreditRecord.reason.label('reason'),
CreditRecord.status.label('status'),
func.date_format(CreditRecord.creation_time, "%Y-%m-%d %H:%i:%s").label('creation_time'),
func.date_format(CreditRecord.modified_time, "%Y-%m-%d %H:%i:%s").label('modified_time'),
)
def get_data_num(cls, manager_person, start_date, end_date):
if manager_person == "所有人":
return cls.query.filter(
func.date_format(cls.date_created, '%Y-%m-%d') >= start_date,
func.date_format(cls.date_created, '%Y-%m-%d') <=
end_date).count()
else:
return cls.query.filter(
func.date_format(cls.date_created, '%Y-%m-%d') >= start_date,
func.date_format(cls.date_created, '%Y-%m-%d') <= end_date,
cls.developers.contains(manager_person)).count()
def sqldata():
results = session.query(Crypto_Table.symbol, func.max(Crypto_Table.price),func.date_format(Crypto_Table.crypto_timestamp,"%Y-%m-%d %h:%i:00"))\
.group_by(Crypto_Table.symbol,func.date_format(Crypto_Table.crypto_timestamp,"%Y-%m-%d %h:%i:00"))\
.all()
totals = []
for result in results:
t = (result[0], float(result[1]), result[2])
totals.append(t)
#print(live_totals)
# Return a JSON list of Temperature Observations (tobs) for the previous year.
return jsonify({"dataset": {"data": totals}})
def count_result(self):
info = {}
video_count = self.mysql_session.query(douyin_video).count()
author_count = self.mysql_session.query(douyin_author).count()
info['all_count'] = int(video_count) + int(author_count)
video_today = self.mysql_session.query(douyin_video.aweme_id).filter(func.date_format(douyin_video.crawl_time, "%Y-%m-%d")==self.date).count()
# print(video_today)
author_today = self.mysql_session.query(douyin_author.douyin_id).filter(func.date_format(douyin_author.crawl_time, "%Y-%m-%d")==self.date).count()
# print(author_today)
info['today_count'] = int(video_today) + int(author_today)
# print(info)
return info
def index():
resp_data = {}
#获取当前日期
date = time.strftime("%Y-%m-%d")
cishu = 0
denglu_info = db.session.query(
func.sum(Denglu.cishu),
func.date_format(Denglu.created_time, "%Y-%m-%d")).group_by(
func.date_format(Denglu.created_time, "%Y-%m-%d"))
for denglu in denglu_info:
if date == denglu[1]:
cishu = denglu[0]
resp_data["cishu"] = cishu
#获取当前yue
year = datetime.date.today().year
month = datetime.date.today().month
denglu = db.session.query(
and_(
extract('year', Denglu.created_time) == year,
extract('month', Denglu.created_time) == month))
yuecishu = 0
yuecishu = denglu.count()
resp_data["yuecishu"] = yuecishu
#根据点击率顺序取出文章
wenzhang_info = db.session.query(Wenzhang.articleid, Wenzhang.title,
Wenzhang.hits).order_by(
Wenzhang.hits.desc())
wenzhang = []
wenzhangdetail = {}
count = 0
if wenzhang_info:
count = int(wenzhang_info.count())
i = 0
if count > 0:
for i in range(0, count - 1):
wenzhangdetail = {}
wenzhangdetail["articleid"] = i + 1
wenzhangdetail["title"] = wenzhang_info[i][1]
wenzhangdetail["hits"] = wenzhang_info[i][2]
wenzhang.append(wenzhangdetail)
i = i + 1
resp_data["wenzhanginfo"] = wenzhang
return ops_render("houtai/index.html", resp_data)
def GetLoginInfo(self, username):
'''
获取用户登录情况
'''
s = db.session()
try:
params = {
'username': username,
'type': 1
}
user_time = s.query(
func.count(Log.username),
func.date_format(Log.create_time, '%Y-%m-%d').label('date')
).filter_by(**params).group_by('date').all()
last_time = s.query(
func.date_format(Log.create_time, '%Y-%m-%d %H:%m:%S')
).filter_by(**params).order_by(desc('id')).first()
role_info = s.query(
Role.name,
Role.mark,
Admin.avatarUrl
).filter(
Admin.username == username,
Admin.role_id == Role.role_id
).first()
data = []
for i in user_time:
data.append({
u'日期': i[1],
username: i[0]
})
return {
'rows': data,
'columns': [u'日期', username],
'info': {
'time': last_time[0],
'role_name': role_info[0],
'mark': role_info[1],
'avatarUrl': role_info[2],
'isAdmin': role_info[1] == default['role_mark']
}
}
except Exception as e:
print e
return str(e.message)
def _query(cls):
return PerformanceTestLog.query.add_columns(
PerformanceTestLog.id.label('id'),
PerformanceTestLog.performance_test_id.label(
'performance_test_id'),
PerformanceTestLog.cpu.label('cpu'),
PerformanceTestLog.rss.label('rss'),
PerformanceTestLog.heap_size.label('heap_alloc'),
PerformanceTestLog.heap_alloc.label('heap_size'),
func.date_format(PerformanceTestLog.creation_time,
"%Y-%m-%d %H:%i:%s").label('creation_time'),
func.date_format(PerformanceTestLog.modified_time,
"%Y-%m-%d %H:%i:%s").label('modified_time'),
)
def sqllivedata():
results = session.query(Crypto_Table.symbol, func.max(Crypto_Table.price),func.date_format(Crypto_Table.crypto_timestamp,"%Y-%m-%d %h:%i:00"))\
.group_by(Crypto_Table.symbol,func.date_format(Crypto_Table.crypto_timestamp,"%Y-%m-%d %h:%i:00"))\
.all()
live_totals = []
for result in results:
row = {}
row["symbol"] = result[0]
row["price"] = float(result[1])
row["crytodatetime"] = result[2]
live_totals.append(row)
#print(live_totals)
# Return a JSON list of Temperature Observations (tobs) for the previous year.
return jsonify({"dataset": live_totals})
def collect_data_bycurrency_datetime(userSelectedCrypto1=None,
userSelectedCrypto2=None,
userSelectedDateTime1=None):
results = session.query(Crypto_Table.symbol, func.max(Crypto_Table.price),func.date_format(Crypto_Table.crypto_timestamp,"%Y-%m-%d %h:%i:00"))\
.filter(Crypto_Table.symbol.in_([userSelectedCrypto1,userSelectedCrypto2])).filter(cast(Crypto_Table.crypto_timestamp, DateTime) >= userSelectedDateTime1)\
.group_by(Crypto_Table.symbol,func.date_format(Crypto_Table.crypto_timestamp,"%Y-%m-%d %h:%i:00"))\
.all()
totals = []
for result in results:
t = (result[0], float(result[1]), result[2])
totals.append(t)
#print(live_totals)
# Return a JSON list of Temperature Observations (tobs) for the previous year.
return jsonify({"dataset": {"data": totals}})
def get(self, store_id):
self.parser.add_argument('start_at', type=lib.arrow_datetime, help="开始时间必填", required=True, location="args")
self.parser.add_argument('end_at', type=lib.arrow_datetime, help="结束时间", location="args")
self.parser.add_argument('model', type=unicode, location="args", default="month")
self.parser.add_argument('company_id', type=int, help="公司 ID", location="args")
args = self.parser.parse_args()
if args.model == 'day':
order_query = models.db.session.query(
func.count(1).label('total'),
func.date_format(models.Order.created_at, '%Y年%m月%d日').label('date'),
)
elif args.model == 'week':
order_query = models.db.session.query(
func.count(1).label('total'),
func.date_format(models.Order.created_at, '%Y年第%v周').label('date'),
)
else:
order_query = models.db.session.query(
func.count(1).label('total'),
func.date_format(models.Order.created_at, '%Y年%m月').label('date'),
)
if not args.end_at:
args.end_at = tool.now(False)
order_query = order_query.filter(
models.Order.created_at >= args.start_at.naive,
models.Order.created_at <= args.end_at.naive,
)
if args.company_id:
order_query = order_query.filter(
models.Order.company_id == args.company_id,
)
if args.model == "day":
order_query = order_query.group_by(func.day(models.Order.created_at))
elif args.model == "week":
order_query = order_query.group_by(func.week(models.Order.created_at))
else:
order_query = order_query.group_by(func.month(models.Order.created_at))
xAxis = list()
series_data = list()
for i in order_query:
xAxis.append(i.date)
series_data.append(i.total)
return tool.success({
'xAxis': xAxis,
'series_data': series_data,
})
def get_config(module, module_type, is_all, project_id=None):
_query = Config.query.add_columns(
Config.content.label('content'),
func.date_format(Config.modified_time,
"%Y-%m-%d %H:%i:%s").label('modified_time'),
Config.id.label('id')).filter(Config.module == module)
if module_type:
_query = _query.filter(Config.module_type == int(module_type))
query = _query.first()
if project_id:
query = _query.filter(Config.projectid == int(project_id)).first()
if not query:
query = _query.filter(Config.projectid == 0).first()
if not query:
raise CannotFindObjectException(
f'config {module}-{module_type}-{project_id} not found in system!')
data = query.content
if not is_all:
return 0, data
else:
return 0, {
'content': data,
'modified_time': query.modified_time,
'id': query.id
}
def generate():
users = db_session.query(User.first_name,
User.last_name,
case([(User.email != None,
User.email)], else_=''),
case([(User.dni != None,
User.dni)], else_=''),
case([(User.phone != None,
User.phone)], else_=''),
case([(User.enabled == '1', 'yes')],
else_='no'),
case([(Department.name != None,
Department.name)], else_=''),
func.date_format(User.created_at,
'%Y-%m-%d %H:%i:%s')
).outerjoin(Department)
row_columns = ('Name', 'Lastname', 'Email', 'Dni',
'Phone', 'Complete', 'Department', 'Created_at',)
if enabled:
if enabled == '1':
users = users.filter(User.enabled == True)
if enabled == '0':
users = users.filter(User.enabled == False)
yield ','.join(row_columns) + '\n'
for row in users.all():
yield ','.join(row) + '\n'
def apply_filter(cls, query, **kwargs):
# User ID
user_id = kwargs.get('user_id', '').strip()
if user_id:
query = query.filter(cls.user_id == user_id)
# User ID
language_id = kwargs.get('language', '').strip()
if language_id:
query = query.filter(cls.language.name == language_id)
# For search results
search_value = kwargs.get('search_value')
if search_value:
query = query.filter(
or_(
cls.user_id.like("%" + search_value + "%"),
cls.first_name.like("%" + search_value + "%"),
cls.last_name.like("%" + search_value + "%"),
cls.email.like("%" + search_value + "%"),
func.date_format(
cls.activation,
'%Y-%m-%d').like("%" + kwargs.get('search_value') +
"%"),
))
return query
def data_count():
"""
功能: 统计爬取数据量, 包括总数据量、今日数据量
:return: {"code": 200,
"data": {
'total_data': 56,
'today_data': 5,
}
}
"""
try:
# 获取当前时间
localtime = datetime.datetime.now()
# 数据获取总量
total_data = DataCounts.query.with_entities(func.sum(
DataCounts.number)).all()
# 当日获取数据量
today_data = DataCounts.query.with_entities(func.sum(DataCounts.number)).\
filter(func.date_format(DataCounts.date_created, '%Y-%m-%d') == localtime.strftime('%Y-%m-%d')).all()
data_dict = dict(total_data=total_data[0][0],
today_data=today_data[0][0])
return json.dumps({"code": 200, "data": data_dict}, cls=DecimalEncoder)
except Exception as e:
return json.dumps({"code": 500, "status": "error", "msg": "数据获取量统计出错"})
def HostCpuData(self, data):
ip = data.get("ip")
# current_time = time.strftime('%Y-%m-%d %H:%M:%S')
old_time = time.strftime("%Y-%m-%d_%X",
time.localtime(time.time() - 3600))
try:
abc = (db.session.query(
func.date_format(t_host_cpu.ctime, "%H:%i:%s").label("ctime"),
t_host_cpu.cpu,
).filter(t_host_cpu.ip == ip, t_host_cpu.ctime > old_time).all())
except Exception as e:
work_log.error('select db HostCpuData error')
work_log.error(str(e))
return {"recode": 9, "redata": str(e)}
x_name = []
cpu = []
work_log.debug(str(abc))
for i in abc:
x_name.append(str(i.ctime))
cpu.append(format(i.cpu, ".2f"))
value = {"x_name": x_name, "y_data": {"cpu": cpu}}
return {"recode": 0, "redata": value}
def month_active_model_situation(Model, start_month, end_month):
"""
按月统计某个模型活跃情况
时间粒度：月
返回结果:[(日期,数量),]
Model：某个模型,把需要类似统计功能的模型抽象出来，简化代码
:return:
"""
result = session.query(func.date_format(Model.login_time, "%Y-%m"), func.count("*")
).filter(Model.login_time.between(start_month, end_month)
).group_by(func.date_format(Model.login_time, "%Y-%m")
).all()
return result
def community_detail():
community_id = request.args["community_id"]
date_str = func.date_format(HouseLJ.date_to_market, '%Y-%m-%d')
col_date_to_market = case([(HouseLJ.date_to_market.is_(None), "未知")],
else_=date_str).label(HouseLJ.date_to_market.name)
query = (select([col_date_to_market, HouseLJ.area, HouseLJ.price_origin,
HouseLJ.price])
.where(HouseLJ.community_id == community_id)
.where(HouseLJ.available.is_(True))
.order_by(HouseLJ.area, HouseLJ.price))
info_raw = app.db_engine.execute(query).fetchall()
house_list = [dict(r) for r in info_raw]
return jsonify(data={"houses": house_list})
def get_ideas_count_day_by_day(self, from_date):
# count ideas day by day (not draft) since the from_date
date = IdeaData.submission_date.label('date')
db_type = urlparse(database._engines.keys()[0]).scheme
if db_type == 'sqlite':
formatted_date = func.strftime(
'%d/%m/%Y', IdeaData.submission_date).label('formatted_date')
else:
formatted_date = func.date_format(
IdeaData.submission_date, '%d/%m/%y').label('formatted_date')
q = (session.query(formatted_date, date,
func.count(IdeaData.id).label('count'))
.outerjoin(IdeaData.wf_context)
.outerjoin(IdeaWFContextData.state)
.filter(StateData.label.in_(get_workflow().get_workflow_states()))
.filter(IdeaData.submission_date >= from_date)
.group_by(formatted_date)
.order_by(formatted_date))
return q
def generate():
users = db_session.query(User.first_name,
User.last_name,
User.email,
User.dni,
User.phone,
case([(User.enabled == True,
'completed')],
else_='incomplete'),
Department.name,
func.date_format(User.created_at,
'%Y-%m-%d %H:%i:%s')
).join(Department)
if enabled:
if enabled == '1':
users = users.filter(User.enabled == True)
if enabled == '0':
users = users.filter(User.enabled == False)
for row in users.all():
yield ','.join(row) + '\n'
def get_collect_total_count():
col_session = sqlal_model('collection',60)
forum_session = sqlal_model('forum', 60)
stat_session = sqlal_model('statistics', 60)
try:
_start_ts, _end_ts = get_default_date()
ts_filter = ThreadCollect.created_at.between(_start_ts, _end_ts)
bankuai_collects = col_session.query(ThreadCollect.thread_id,
func.date_format(ThreadCollect.created_at,"%Y-%m-%d").label("ts")) \
.filter(ts_filter).all()
thread_ids = map(lambda x: x.thread_id, bankuai_collects)
thread_infos = forum_session.query(Threads.id,Threads.category_id) \
.filter(Threads.id.in_(thread_ids)).all()
thread_dict = dict(map(lambda x: (x.id, x.category_id), thread_infos))
transaction.commit()
bankuai_ids = list(set(thread_dict.values()))
bankuai_collects = map(lambda x: dict(bankuai_id = thread_dict.get(x[0], 0),ts = x[1],num = 1), bankuai_collects)
ts_list = list(set(i['ts'] for i in bankuai_collects))
ts_list = sorted(ts_list)
bankuai_thread_infos = {i: filter(lambda x:x["bankuai_id"]==i, bankuai_collects) for i in bankuai_ids}
bankuai_thread_infos = map((lambda x:{"bankuai_id": x,
"ts": i,
"num": reduce(lambda a,b: a+b,map(lambda x:x["num"],
filter(lambda y:y["ts"]==i,bankuai_thread_infos[x])) or [0])} for i in ts_list),\
bankuai_thread_infos)
_collect_statistics = CollectStatistics()
_collect_statistics_list = []
for thread_info in bankuai_thread_infos:
_collect_statistics.bankuai_id = thread_info["bankuai_id"]
_collect_statistics.ts = thread_info["ts"]
_collect_statistics.num = thread_info["num"]
_collect_statistics_list.append(_collect_statistics)
result = stat_session.add_all(_collect_statistics_list)
transaction.commit()
return result
except Exception,ex:
raise ex
def get_connection_count_day_by_day(self, from_date, platform=None):
# count first connection day by day since the from_date
date = PointData.date.label('date')
db_type = urlparse(database._engines.keys()[0]).scheme
if db_type == 'sqlite':
formatted_date = func.strftime(
'%d/%m/%Y', IdeaData.submission_date).label('formatted_date')
else:
formatted_date = func.date_format(
IdeaData.submission_date, '%d/%m/%y').label('formatted_date')
q = session.query(date,
formatted_date,
func.count(PointData.id).label('count'))
q = q.filter(
PointData.label == PointCategory.FIRST_CONNECTION_OF_THE_DAY)
q = q.filter(PointData.date >= from_date)
q = q.group_by(formatted_date)
q = q.order_by(formatted_date)
if platform:
q = q.filter(PointData.subject_id == platform)
return q
def Check():
input_shop_id = int(input("Input Shop ID to Check:"))
print("Start Checking Balance History...")
# *将balancehistory表中的所有店铺id查出来存放在列表shop_list_query1中
# *将系统中的所有status > -1的订单的数量不为0店铺的id查询出来存在一个列表shop_list_query2中
# *然后从列表shop_list_query2中除去shop_list_query1中的id
# shop_list_query1 = session.query(models.BalanceHistory.shop_id).distinct(models.BalanceHistory.shop_id).all()
shop_id_list1 = []
shop_id_list1.append(input_shop_id)
# for item in shop_list_query1:
# shop_id_list1.append(item[0])
total = len(shop_id_list1)
# *设定一个change_shop_id列表,把balancehistory表中修改过的店铺的id都存进去.
change_shop_id = []
# shop_list_query2可以先不考虑
# shop_list_query2 = session.query(models.Order.shop_id).filter(models.Order.status > -1,models.Order.pay_type.in_([2,3])).distinct(models.Order.shop_id).all()
# shop_id_list2 = []
# for item in shop_list_query2:
# if item[0] not in shop_id_list1:
# shop_id_list2.append(item[0])
# *遍历shop_id_list1每一个shop的id
# if 1 == 2:
c = 0
for shop_id in shop_id_list1:
c = c+1
print("Processing [",c,"/",total,"] => Step 1: shop_id",shop_id,"Start")
if shop_id <= 0:
continue
# *筛选出shop_id错误的记录并修正.
balance_list = session.query(models.BalanceHistory.id,models.BalanceHistory.balance_type,models.BalanceHistory.balance_record).filter_by(shop_id = shop_id).order_by(models.BalanceHistory.create_time).all()
session.query(models.BalanceHistory).filter(models.BalanceHistory.shop_id <= 0).delete()
session.query(models.BalanceHistory).filter(models.BalanceHistory.id == 19087).delete()
session.query(models.BalanceHistory).filter(models.BalanceHistory.id == 19088).delete()
session.commit()
num_modify_list = ['197000176','615000311','271000022','197000177','624001065','848000584','624001076','848000585']
balance_id_list = [698,699,700,701,719,728,729,731]
for i in range(len(balance_id_list)):
id = balance_id_list[i]
history_record = session.query(models.BalanceHistory).filter_by(id = id).with_lockmode("update").first()
history_record.balance_record = "在线支付(微信)：订单" + num_modify_list[i]
session.commit()
# # if 1 == 2:
# for i in range(len(balance_list)):
# item = balance_list[i]
# id = item[0]
# balance_type = item[1]
# balance_record = item[2]
# if balance_type in [1,3]:
# for n in range(len(balance_record)):
# if balance_record[n].isdigit():
# break
# order_num = balance_record[n : len(balance_record)]
# order_num_shop_id = int(order_num[0 : -6])
# if order_num_shop_id != shop_id:
# print("no in !",shop_id,order_num)
# elif balance_type in [4,5,6,7]:
# for n in range(len(balance_record)):
# if balance_record[n].isdigit():
# break
# order_num = balance_record[n : len(balance_record)-2]
# order_num_shop_id = int(order_num[0 : -6])
# if order_num_shop_id != shop_id:
# print("no in !",shop_id,order_num)
# *查询balancehistory表中的所有余额支付（balance_type = 1）的记录的订单号并存到列表balance_type_1中
balance_type_1 = []
query_list = session.query(models.BalanceHistory.balance_record).filter(models.BalanceHistory.balance_type == 1,models.BalanceHistory.shop_id == shop_id).all()
if len(query_list) != 0:
for item in query_list:
for i in range(len(item[0])):
if item[0][i].isdigit():
break
balance_type_1.append(item[0][i : len(item[0])])
# *查询order表中的所有余额支付（pay_type = 2）的记录的订单号并存到列表pay_type_2中
pay_type_2 = []
query_list = session.query(models.Order.num).filter(models.Order.status >= 0,models.Order.pay_type == 2,models.Order.shop_id == shop_id).all()
if len(query_list) != 0:
for item in query_list:
pay_type_2.append(item[0])
# *判断pay_type_2的长度是否大于balance_type_1，若大于，则说明order表中肯定有余额支付的订单记录没有插入到balancehistory表中，这时就要把相关记录插入到balancehistory表中
if len(pay_type_2) > len(balance_type_1):
# print("!!@@@11111111111111",shop_id)
# print("@@@@",len(pay_type_2))
# print(len(balance_type_1))
insert_order_num = [i for i in pay_type_2 if i not in balance_type_1]
insert_list = session.query(models.Order.customer_id,models.Order.shop_id,models.Accountinfo.nickname,models.Order.new_totalprice,models.Order.num,func.date_format(models.Order.create_date,'%Y-%m-%d %H:%i:%s')).\
join(models.Accountinfo,models.Accountinfo.id == models.Order.customer_id).filter(models.Order.num.in_(insert_order_num)).all()
for i in range(len(insert_list)):
shop_follow = session.query(models.CustomerShopFollow).filter_by(customer_id = insert_list[i][0],shop_id = shop_id).with_lockmode("update").first()
if not shop_follow:
return send_fail('shop_follow not found')
shop_follow.shop_balance -= insert_list[i][3] #用户对应 店铺余额减少
shop_follow.shop_balance = round(shop_follow.shop_balance,2)
session.commit()
insert_list[i] = list(insert_list[i])
create_time = datetime.datetime.strptime(insert_list[i][5],'%Y-%m-%d %H:%M:%S')
insert_list[i][4] = '余额支付：订单' + insert_list[i][4]
balance_history = models.BalanceHistory(customer_id = insert_list[i][0],shop_id = insert_list[i][1] ,name = insert_list[i][2],balance_value = insert_list[i][3] ,\
balance_record = insert_list[i][4],create_time = create_time,balance_type = 1,is_cancel = 99999)
session.add(balance_history)
session.commit()
change_shop_id.append(shop_id)
elif len(pay_type_2)< len(balance_type_1):
# print("@@@@@@@@1",len(pay_type_2),len(balance_type_1))
pass
else:
pass
balance_type_3 = []
query_list = session.query(models.BalanceHistory.balance_record).filter(models.BalanceHistory.balance_type == 3,models.BalanceHistory.shop_id == shop_id).all()
if len(query_list) != 0:
for item in query_list:
for i in range(len(item[0])):
if item[0][i].isdigit():
break
balance_type_3.append(item[0][i : len(item[0])])
pay_type_3 = []
query_list = session.query(models.Order.num).filter(models.Order.status > 0,models.Order.pay_type == 3,models.Order.shop_id == shop_id).all()
if len(query_list) != 0:
for item in query_list:
pay_type_3.append(item[0])
# *判断pay_type_3的长度是否大于balance_type_3，若大于，则说明order表中肯定有在线支付的订单记录没有插入到balancehistory表中，这时就要把相关记录插入到balancehistory表中
if len(pay_type_3) > len(balance_type_3):
insert_order_num = [i for i in pay_type_3 if i not in balance_type_3]
insert_list = session.query(models.Order.customer_id,models.Order.shop_id,models.Accountinfo.nickname,models.Order.new_totalprice,models.Order.num,func.date_format(models.Order.create_date,'%Y-%m-%d %H:%i:%s'),models.Order.online_type).\
join(models.Accountinfo,models.Accountinfo.id == models.Order.customer_id).filter(models.Order.num.in_(insert_order_num)).all()
for i in range(len(insert_list)):
shop = session.query(models.Shop).filter_by(id = shop_id).first()
if not shop:
return send_fail('shop not found')
shop.shop_balance += insert_list[i][3]
shop.shop_balance = round(shop.shop_balance,2)
session.commit()
insert_list[i] = list(insert_list[i])
create_time = datetime.datetime.strptime(insert_list[i][5],'%Y-%m-%d %H:%M:%S')
if insert_list[i][6] == 'wx':
insert_list[i][4] = '在线支付(微信)：订单' + insert_list[i][4]
elif insert_list[i][6] == 'alipay':
insert_list[i][4] = '在线支付(支付宝)：订单' + insert_list[i][4]
balance_history = models.BalanceHistory(customer_id = insert_list[i][0],shop_id = insert_list[i][1] ,name = insert_list[i][2],balance_value = insert_list[i][3] ,\
balance_record = insert_list[i][4],create_time = create_time,balance_type = 3,is_cancel = 99999)
session.add(balance_history)
session.commit()
change_shop_id.append(shop_id)
elif len(pay_type_3) < len(balance_type_3):
# print("@@@@@@@@2",len(pay_type_3),len(balance_type_3))
pass
else:
pass
balance_type_4_5 = []
query_list = session.query(models.BalanceHistory.balance_record).filter(models.BalanceHistory.balance_type.in_([4,5]),models.BalanceHistory.shop_id == shop_id).all()
if len(query_list) != 0:
for item in query_list:
for i in range(len(item[0])):
if item[0][i].isdigit():
break
balance_type_4_5.append(item[0][i : len(item[0])-2])
pay_type_2_delete = []
query_list = session.query(models.Order.num).filter(models.Order.status == 0,models.Order.pay_type == 2,models.Order.shop_id == shop_id).all()
if len(query_list) != 0:
for item in query_list:
pay_type_2_delete.append(item[0])
# *判断pay_type_2_delete的长度是否大于balance_type_4_5，若大于，则说明order表中肯定有余额支付的订单记录没有插入到balancehistory表中，这时就要把相关记录插入到balancehistory表中
if len(pay_type_2_delete) > len(balance_type_4_5):
# print("!!@@@11111111111111",shop_id)
# print("@@@@",len(pay_type_2_delete))
# print(len(balance_type_4_5))
insert_order_num = [i for i in pay_type_2_delete if i not in balance_type_4_5]
insert_list = session.query(models.Order.customer_id,models.Order.shop_id,models.Accountinfo.nickname,models.Order.new_totalprice,models.Order.num,func.date_format(models.Order.create_date,'%Y-%m-%d %H:%i:%s'),models.Order.del_reason).\
join(models.Accountinfo,models.Accountinfo.id == models.Order.customer_id).filter(models.Order.num.in_(insert_order_num)).all()
for i in range(len(insert_list)):
shop_follow = session.query(models.CustomerShopFollow).filter_by(customer_id = insert_list[i][0],shop_id = shop_id).with_lockmode("update").first()
if not shop_follow:
return send_fail('shop_follow not found')
shop_follow.shop_balance += insert_list[i][3] #用户对应 店铺余额增加 ，单位：元
shop_follow.shop_balance = round(shop_follow.shop_balance,2)
session.commit()
insert_list[i] = list(insert_list[i])
create_time = datetime.datetime.strptime(insert_list[i][5],'%Y-%m-%d %H:%M:%S')
delete_time = create_time + datetime.timedelta(minutes = 15)
if insert_list[i][6] == None:
insert_list[i][4] = '余额退款：订单' + insert_list[i][4] + '取消'
balance_type = 5
elif insert_list[i][6] != None and insert_list[i][6] != 'timeout':
insert_list[i][4] = '余额退款：订单' + insert_list[i][4] + '删除'
balance_type = 4
balance_history = models.BalanceHistory(customer_id = insert_list[i][0],shop_id = insert_list[i][1] ,name = insert_list[i][2],balance_value = insert_list[i][3] ,\
balance_record = insert_list[i][4],create_time = delete_time,balance_type = balance_type,is_cancel = 99999)
session.add(balance_history)
session.commit()
change_shop_id.append(shop_id)
elif len(pay_type_2_delete)< len(balance_type_4_5):
# print("@@@@@@@@3",len(pay_type_2_delete),len(balance_type_4_5))
pass
else:
pass
balance_type_6_7 = []
query_list = session.query(models.BalanceHistory.balance_record).filter(models.BalanceHistory.balance_type.in_([6,7]),models.BalanceHistory.shop_id == shop_id).all()
if len(query_list) != 0:
for item in query_list:
for i in range(len(item[0])):
if item[0][i].isdigit():
break
balance_type_6_7.append(item[0][i : len(item[0])-2])
pay_type_2_3_done = []
query_list = session.query(models.Order.num).filter(models.Order.status >= 5,models.Order.pay_type.in_([2,3]),models.Order.shop_id == shop_id).all()
if len(query_list) != 0:
for item in query_list:
pay_type_2_3_done.append(item[0])
# *判断pay_type_2_3_done的长度是否大于balance_type_6_7，若大于，则说明order表中肯定有余额支付完成或在线支付完成的订单记录没有插入到balancehistory表中，这时就要把相关记录插入到balancehistory表中
if len(pay_type_2_3_done) > len(balance_type_6_7):
# print("!!@@@11111111111111",shop_id)
# print("@@@@",len(pay_type_2_3_done))
# print(len(balance_type_6_7))
insert_order_num = [i for i in pay_type_2_3_done if i not in balance_type_6_7]
insert_list = session.query(models.Order.customer_id,models.Order.shop_id,models.Accountinfo.nickname,models.Order.new_totalprice,models.Order.num,func.date_format(models.Order.create_date,'%Y-%m-%d %H:%i:%s'),\
models.Order.arrival_day,models.Order.arrival_time,models.Order.pay_type,models.Order.today,models.Order.end_time).\
join(models.Accountinfo,models.Accountinfo.id == models.Order.customer_id).filter(models.Order.num.in_(insert_order_num)).all()
for i in range(len(insert_list)):
shop_follow = session.query(models.CustomerShopFollow).filter_by(customer_id = insert_list[i][0],shop_id = shop_id).with_lockmode("update").first()
if not shop_follow:
return send_fail('shop_follow not found')
if shop_follow.shop_new == 0:
shop_follow.shop_new = 1
order_count = 0
try:
order_count = session.query(models.Order).filter_by(customer_id = insert_list[i][0],shop_id = shop_id).count()
except:
send_fail("[_AccountBaseHandler]order_done: find order by customer_id and shop_id error")
#首单 积分 加5
if order_count==1:
if shop_follow.shop_point == None:
shop_follow.shop_point =0
shop_follow.shop_point += 5
try:
point_history = models.PointHistory(customer_id = insert_list[i][0],shop_id = shop_id)
except NoResultFound:
send_fail("[_AccountBaseHandler]order_done: point_history error, First_order")
if point_history:
point_history.point_type = models.POINT_TYPE.FIRST_ORDER
point_history.each_point = 5
session.add(point_history)
insert_list[i] = list(insert_list[i])
if insert_list[i][8] == 2:
if shop_follow.shop_point == None:
shop_follow.shop_point =0
shop_follow.shop_point += 2
try:
point_history = models.PointHistory(customer_id = insert_list[i][0],shop_id = shop_id)
except:
send_fail("[_AccountBaseHandler]order_done: point_history error, PREPARE_PAY")
if point_history:
point_history.point_type = models.POINT_TYPE.PREPARE_PAY
point_history.each_point = 2
session.add(point_history)
# 订单完成后，将相应店铺可提现 余额相应增加
cur_shop = session.query(models.Shop).filter_by(id = shop_id).with_lockmode("update").first()
cur_shop.available_balance += insert_list[i][3]
cur_shop.available_balance = round(cur_shop.available_balance,2)
session.commit()
if insert_list[i][6] == None or insert_list[i][7] == None:
end_date = insert_list[i][5]
end_date = datetime.datetime.strptime(end_date,'%Y-%m-%d %H:%M:%S')
if insert_list[i][9] == 2:
end_date = end_date + datetime.timedelta(days = 1)
insert_list[i][10] = str(insert_list[i][10])
hh = int(insert_list[i][10][0:2])
mm = int(insert_list[i][10][3:5])
ss = int(insert_list[i][10][6:8])
create_time = datetime.datetime(end_date.year,end_date.month,end_date.day,hh,mm,ss)
else:
create_time = insert_list[i][6] + " " + insert_list[i][7] + ":00"
create_time = datetime.datetime.strptime(create_time,'%Y-%m-%d %H:%M:%S')
insert_list[i][4] = "可提现额度入账：订单" + insert_list[i][4] + "完成"
balance_history = models.BalanceHistory(customer_id = insert_list[i][0],shop_id = insert_list[i][1] ,name = insert_list[i][2],balance_value = insert_list[i][3] ,\
balance_record = insert_list[i][4],create_time = create_time,balance_type = 6,is_cancel = 99999)
session.add(balance_history)
session.commit()
change_shop_id.append(shop_id)
if insert_list[i][8] == 3:
# 订单完成后，将相应店铺可提现 余额相应增加
cur_shop = session.query(models.Shop).filter_by(id = shop_id).with_lockmode("update").first()
cur_shop.available_balance += insert_list[i][3]
cur_shop.available_balance = round(cur_shop.available_balance,2)
session.commit()
if insert_list[i][6] == None or insert_list[i][7] == None:
end_date = insert_list[i][5]
end_date = datetime.datetime.strptime(end_date,'%Y-%m-%d %H:%M:%S')
if insert_list[i][9] == 2:
end_date = end_date + datetime.timedelta(days = 1)
insert_list[i][10] = str(insert_list[i][10])
hh = int(insert_list[i][10][0:2])
mm = int(insert_list[i][10][3:5])
ss = int(insert_list[i][10][6:8])
create_time = datetime.datetime(end_date.year,end_date.month,end_date.day,hh,mm,ss)
else:
create_time = insert_list[i][6] + " " + insert_list[i][7] + ":00"
create_time = datetime.datetime.strptime(create_time,'%Y-%m-%d %H:%M:%S')
insert_list[i][4] = "可提现额度入账：订单" + insert_list[i][4] + "完成"
balance_history = models.BalanceHistory(customer_id = insert_list[i][0],shop_id = insert_list[i][1] ,name = insert_list[i][2],balance_value = insert_list[i][3] ,\
balance_record = insert_list[i][4],create_time = create_time,balance_type = 7,is_cancel = 99999)
session.add(balance_history)
session.commit()
change_shop_id.append(shop_id)
elif len(pay_type_2_3_done)< len(balance_type_6_7):
# print("@@@@@@@@4",len(pay_type_2_3_done),len(balance_type_6_7))
pass
else:
pass
print("Processing [",c,"/",total,"] => Step 2: shop_id",shop_id,"Checking")
balance_list = session.query(models.BalanceHistory.id,func.date_format(models.BalanceHistory.create_time,"%Y-%m-%d %H:%i:%S"),models.BalanceHistory.shop_id,models.BalanceHistory.balance_type,\
models.BalanceHistory.balance_record,models.BalanceHistory.balance_value,models.BalanceHistory.customer_id,models.BalanceHistory.customer_totalPrice,\
models.BalanceHistory.shop_totalPrice,models.BalanceHistory.available_balance,models.BalanceHistory.is_cancel).filter_by(shop_id = shop_id).order_by(models.BalanceHistory.create_time).all()
cancel_list = []
query_list = session.query(models.BalanceHistory.balance_record).filter(models.BalanceHistory.shop_id == shop_id,models.BalanceHistory.balance_type.in_([4,5])).all()
for item in query_list:
for i in range(len(item[0])):
if item[0][i].isdigit():
break
cancel_list.append(item[0][i : len(item[0])-2])
shop_follow = session.query(models.CustomerShopFollow).filter(models.CustomerShopFollow.shop_id == shop_id,models.CustomerShopFollow.shop_balance != 0).with_lockmode("update").all()
if len(shop_follow) != 0:
for item in shop_follow:
item.shop_balance = 0
session.commit()
cur_shop = session.query(models.Shop).filter_by(id = shop_id).with_lockmode("update").first()
cur_shop.shop_balance = 0
cur_shop.available_balance = 0
session.commit()
for i in range(0,len(balance_list)):
item = balance_list[i]
id = item[0]
create_time = item[1]
shop_id = item[2]
balance_type = item[3]
balance_record = item[4]
balance_value = item[5]
customer_id = item[6]
customer_totalPrice = item[7]
shop_totalPrice = item[8]
available_balance = item[9]
is_cancel = item[10]
# added by jyj 2015-8-10
shop_province = session.query(models.Shop.shop_province).filter_by(id = shop_id).all()[0][0]
shop_name = session.query(models.Shop.shop_name).filter_by(id = shop_id).all()[0][0]
##
history_record = session.query(models.BalanceHistory).filter_by(id = id).with_lockmode("update").first()
# added by jyj 2015-8-10
history_record.shop_province = shop_province
history_record.shop_name = shop_name
##
if history_record.is_cancel != 1:
history_record.is_cancel = 0
if balance_type == 1:
for n in range(len(balance_record)):
if balance_record[n].isdigit():
break
order_num = balance_record[n : len(balance_record)]
if order_num in cancel_list:
history_record.is_cancel = 1
shop_follow = session.query(models.CustomerShopFollow).filter_by(customer_id = customer_id,shop_id = shop_id).with_lockmode("update").first()
if not shop_follow:
return send_fail('shop_follow not found')
cur_shop = session.query(models.Shop).filter_by(id = shop_id).with_lockmode("update").first()
# 错误记录（直接忽略）
if balance_type == -1:
pass
# 余额充值
elif balance_type == 0:
shop_follow.shop_balance += balance_value
shop_follow.shop_balance = round(shop_follow.shop_balance,2)
cur_shop.shop_balance += balance_value
cur_shop.shop_balance = round(cur_shop.shop_balance,2)
history_record.customer_totalPrice = shop_follow.shop_balance
history_record.shop_totalPrice = cur_shop.shop_balance
history_record.available_balance = cur_shop.available_balance
# 余额消费
elif balance_type == 1:
shop_follow.shop_balance -= balance_value
shop_follow.shop_balance = round(shop_follow.shop_balance,2)
history_record.customer_totalPrice = shop_follow.shop_balance
history_record.shop_totalPrice = cur_shop.shop_balance
history_record.available_balance = cur_shop.available_balance
# 提现
elif balance_type == 2:
cur_shop.shop_balance -= balance_value
cur_shop.shop_balance = round(cur_shop.shop_balance,2)
cur_shop.available_balance -= balance_value
cur_shop.available_balance = round(cur_shop.available_balance,2)
history_record.customer_totalPrice = shop_follow.shop_balance
history_record.shop_totalPrice = cur_shop.shop_balance
history_record.available_balance = cur_shop.available_balance
# 在线支付
elif balance_type == 3:
cur_shop.shop_balance += balance_value
cur_shop.shop_balance = round(cur_shop.shop_balance,2)
history_record.customer_totalPrice = shop_follow.shop_balance
history_record.shop_totalPrice = cur_shop.shop_balance
history_record.available_balance = cur_shop.available_balance
# 余额退款
elif balance_type in [4,5]:
shop_follow.shop_balance += balance_value
shop_follow.shop_balance = round(shop_follow.shop_balance,2)
history_record.customer_totalPrice = shop_follow.shop_balance
history_record.shop_totalPrice = cur_shop.shop_balance
history_record.available_balance = cur_shop.available_balance
# 可提现额度入账
elif balance_type in [6,7]:
cur_shop.available_balance += balance_value
cur_shop.available_balance = round(cur_shop.available_balance,2)
history_record.customer_totalPrice = shop_follow.shop_balance
history_record.shop_totalPrice = cur_shop.shop_balance
history_record.available_balance = cur_shop.available_balance
# 在线支付退款
elif balance_type in [8,9]:
cur_shop.shop_balance -= balance_value
cur_shop.shop_balance = round(cur_shop.shop_balance,2)
history_record.customer_totalPrice = shop_follow.shop_balance
history_record.shop_totalPrice = cur_shop.shop_balance
history_record.available_balance = cur_shop.available_balance
else:
print("balance_type错误，未知值: ",balance_type)
return send_error(404)
session.commit()
print("Processing [",c,"/",total,"] => Step 3: shop_id",shop_id,"Done")
def date_output(self, t): if self.engine == "mysql": return func.date_format(t, '%m/%d') if self.engine == "postgresql": return func.to_char(t, 'MM/DD')
def date_compare(self, t): if self.engine == "mysql": return func.date_format(t, '%Y-%m-%d') if self.engine == "postgresql": return func.to_timestamp(func.to_char(t, 'YYYY-MM-DD'), 'YYYY-MM-DD')
