def edit(req):
"""
:param req: id=30&DWJC=00000C&field=DWJC&menu=zzjgjbsjxx
:return:
"""
try:
v = eval(str(req.POST.dict()))
set_to = trim(str(v[v['field']]))
if set_to.find('null') != -1:
return JsonResponse({'success': False, 'msg': '更新失败：值中含有 null'})
v['table_prefix'] = 'dr'
if v['menu'] in ['jxkhgz']:
v['table_prefix'] = 'kh'
v['set_to'] = set_to
sql_update = "UPDATE %(table_prefix)s_%(menu)s SET %(field)s='%(set_to)s' WHERE id=%(id)s" % v
cursor = connection.cursor()
logger.info(sql_update)
cursor.execute(sql_update)
return JsonResponse({'success': True, 'msg': '成功更新为：' + set_to})
except Error: # django.db.utils.Error
logger.error(sys_info())
return JsonResponse({'success': False, 'msg': '更新失败：数据库错误'})
def get_department_users(dept):
cursor = connection.cursor()
cursor.execute(
""" SELECT * FROM view_jzgjcsjxx WHERE DWH='%(dept)s' ORDER BY JZGH """
% {'dept': dept})
select_out = dictfetchall(cursor)
return select_out if select_out else []
def get_sub_departments(parent):
cursor = connection.cursor()
cursor.execute(
""" SELECT * FROM view_zzjgjbsjxx WHERE LSDWH='%(parent)s' ORDER BY DWH """
% {'parent': parent})
select_out = dictfetchall(cursor)
return select_out if select_out else []
def get_user_information(payroll):
cursor = connection.cursor()
cursor.execute(
""" SELECT * FROM view_jzgjcsjxx WHERE JZGH='%(payroll)s' """ %
{'payroll': payroll})
select_out = dictfetchall(cursor)
return select_out[0] if select_out else []
def staffinfo(req):
payroll = str(req.COOKIES.get('payroll'))
user = SysUser.objects.get(payroll=payroll)
v = eval(str(req.GET.dict()))
if 'sort' not in v or v['sort'] in ('', None):
v['sort'] = 'id'
v['order'] = 'DESC'
l_user = SysUser.objects.get(payroll=payroll)
v['role'] = l_user.role_id
sql_count = """ SELECT COUNT(*) AS count FROM view_sysuser """
sql_content = """ SELECT * FROM view_sysuser """
sql_where = " WHERE 1=1 "
if user.role_id == 1:
pass
elif user.role_id == 2:
user_info = get_user_information(payroll)
sql_where += " AND DWH='%(department)s'" % {
'department': str(user_info['DWH'])
}
else:
sql_where += ' AND 1=0'
sql_search = ""
if 'search' in v and v['search'] not in ('', None):
search_columns = ['payroll', 'XM', 'DWMC']
if search_columns:
sql_search = " AND (1=0 "
for col in search_columns:
sql_search += " OR %(col)s LIKE \'%%%(search)s%%\' " % {
'col': col,
'search': v['search']
}
sql_search += ")"
sql_olo = ' ORDER BY %(sort)s %(order)s LIMIT %(limit)s OFFSET %(offset)s' % v
cursor = connection.cursor()
logger.info(sql_count + sql_where + sql_search)
cursor.execute(sql_count + sql_where + sql_search)
count = dictfetchall(cursor)[0]["count"]
logger.info(sql_content + sql_where + sql_search + sql_olo)
cursor.execute(sql_content + sql_where + sql_search + sql_olo)
select_out = dictfetchall(cursor)
return JsonResponse({'total': count, 'rows': select_out})
def delete_data(req):
payroll = str(req.COOKIES.get('payroll'))
# TODO: check delete auth ???
v = eval(str(req.POST.dict()))
v['table'] = "dr_" + v['menu']
v['id_in'] = v['id'].replace('[', '(').replace(']', ')')
sql_delete = """ DELETE FROM %(table)s WHERE id IN %(id_in)s """ % v
# TODO: add more WHERE conditions to keep system safe
# 1. zzjgjbsjxx can't delete parent and current
# 2. jzgjcsjxx can't delete self and admin
# 3. other conditions ???
cursor = connection.cursor()
logger.info(sql_delete)
cursor.execute(sql_delete)
return JsonResponse({'success': True, 'msg': '删除数据成功'})
def get_data(req):
payroll = str(req.COOKIES.get('payroll'))
v = eval(str(req.GET.dict()))
module_name = 'module'
view_prefix = 'view'
if v['menu'] in []:
module_name = 'rule'
view_prefix = 'kh'
v['table'] = (view_prefix + "_" + v['menu']).upper().lower(
) # change to lower due to un-support upper SQL on Linux
v['start'] += "-01 00:00:00"
v['end'] = month_end(v['end'])
if 'sort' not in v or v['sort'] in ('', None):
v['sort'] = 'id'
v['order'] = 'DESC'
# code # DWH if type=='d', JZGH if type=='u'
# type # d - department, u - user
# TODO: use department and payroll to get data
if 'type' in v:
if v['type'] == 'd':
v['department'] = v['code']
v['payroll'] = payroll
if v['type'] == 'u':
user = get_user_information(v['code'])
v['department'] = str(user['DWH'])
v['payroll'] = v['code']
else:
v['type'] = 'u'
v['payroll'] = payroll
l_user = SysUser.objects.get(payroll=payroll)
v['role'] = l_user.role_id
sql_count = """ SELECT COUNT(*) AS count FROM %(table)s """ % v
sql_content = """ SELECT * FROM %(table)s """ % v
sql_where = " WHERE 1=1 "
if v['type'] == 'u':
sql_where += " AND JZGH='%(payroll)s'"
elif v['type'] == 'd':
if v['menu'] == 'zzjgjbsjxx': # 组织机构基本数据信息
sql_where += " AND (DWH='%(department)s' OR LSDWH='%(department)s')"
else:
sql_where += " AND DWH='%(department)s'"
else:
sql_where += " AND 1=0"
sql_where %= v
sql_search = ""
if 'search' in v and v['search'] not in ('', None):
from jx.views import get_module_static_method
search_columns = get_module_static_method(v['menu'],
'get_search_columns',
module_name, view_prefix)
if search_columns:
sql_search = " AND (1=0 "
for col in search_columns:
sql_search += " OR %(col)s LIKE \'%%%(search)s%%\' " % {
'col': col,
'search': v['search']
}
sql_search += ")"
sql_olo = ' ORDER BY %(sort)s %(order)s LIMIT %(limit)s OFFSET %(offset)s' % v
cursor = connection.cursor()
logger.info(sql_count + sql_where + sql_search)
cursor.execute(sql_count + sql_where + sql_search)
count = dictfetchall(cursor)[0]["count"]
logger.info(sql_content + sql_where + sql_search + sql_olo)
cursor.execute(sql_content + sql_where + sql_search + sql_olo)
select_out = dictfetchall(cursor)
return JsonResponse({'total': count, 'rows': select_out})
def get_top_departments():
cursor = connection.cursor()
cursor.execute(
""" SELECT * FROM dr_zzjgjbsjxx WHERE LSDWH='' OR LSDWH IS NULL """)
select_out = dictfetchall(cursor)
return select_out if select_out else []
def jx_upload_file(req):
from jx.sqlalchemy_env import cursor, conn
def __format_value(v, fm):
"""
TODO: format v per key[k][1] if defined
:param v:
:param fm:
:return:
"""
if fm == "DateTime":
y, m, d = '1970', '01', '01'
hh, mm, ss = '00', '00', '00'
try:
if len(v) >= 4:
y = str(int(v[:4]))
if len(v) >= 6:
m = str(int(v[4:6]))
m = ('0' + m) if len(m) == 1 else m
if len(v) >= 8:
d = str(int(v[6:8]))
d = ('0' + d) if len(d) == 1 else d
if len(v) >= 10:
hh = str(int(v[8:10]))
hh = ('0' + hh) if len(hh) == 1 else hh
if len(v) >= 12:
mm = str(int(v[10:12]))
mm = ('0' + mm) if len(mm) == 1 else mm
if len(v) >= 14:
ss = str(int(v[12:14]))
ss = ('0' + ss) if len(ss) == 1 else ss
except:
sys_info()
return y + '-' + m + '-' + d + ' ' + hh + ':' + mm + ':' + ss
return v
def __row_replace_key(__row, __key, uniq=None):
if uniq is None:
uniq = []
res = {}
cc_str, vv_str, uu_str = '', '', ''
for kk, vv in __row.items():
k = trim(str(kk))
v = trim(str(vv))
if k in __key:
if len(__key[k]) > 1:
v = __format_value(v, __key[k][1])
res[__key[k][0]] = v
cc_str += str(__key[k][0]) + ', '
vv_str += "'" + str(v).replace("'", "char(39)") + "', "
if str(__key[k][0]) not in uniq:
uu_str += str(__key[k][0]) + "='" + str(v).replace(
"'", "char(39)") + "', "
else:
# res[k] = v # ignore useless fields
pass
return res, cc_str[:-2], vv_str[:-2], uu_str[:-2]
try:
function, file_name = save_file(req)
df = pd.read_excel(file_name, na_values='') # 这个会直接默认读取到这个Excel的第一个表单
df = df.where(df.notnull(), '')
table_prefix = "dr"
try:
model_dr = __import__('jx.model_dr', fromlist=(["model_dr"]))
except ImportError:
model_dr = __import__('model_dr')
if function in ['jxkhgz']:
table_prefix = 'kh'
try:
model_dr = __import__('jx.rule', fromlist=(["rule"]))
except ImportError:
model_dr = __import__('rule')
model_dr_class = getattr(model_dr,
(table_prefix + '_' + function).upper())
table = model_dr_class.__tablename__
columns = model_dr_class.get_column_label()
unique = model_dr_class.get_unique_condition()
sql_where = " WHERE 1=1 "
for u in unique:
sql_where += " AND " + u + "='%(" + u + ")s'"
sql_count = "SELECT COUNT(*) AS count FROM %(table)s" % {
'table': table
} + sql_where
for record in df.to_dict('records'):
rec, c_str, v_str, u_str = __row_replace_key(
record, columns, unique)
cursor.execute(sql_count % rec)
result = cursor.fetchall()
if result[0][0]: # update
sql = "UPDATE %(table)s SET " % {
'table': table
} + u_str + sql_where % rec
else: # insert
sql = """
INSERT INTO %(table)s (%(columns)s) VALUES (%(values)s)
""" % {
'table': table,
'columns': c_str,
'values': v_str,
}
logger.info(sql)
cursor.execute(sql)
conn.commit(
) # NOTE: 必须commit; 否则独占数据库链接；可以考虑使用django connection自动commit
return JsonResponse({'success': True, 'msg': '文件处理成功，请检验数据!'})
except:
logger.error(sys_info())
return JsonResponse({'success': False, 'msg': '文件处理失败，请修正后重试！'})