def remove_break_log():
try:
# break_card_list = cursor.execute(bak_log_sql).fetchall()
cursor = conn.cursor()
pos_log_sql = get_sql("ic_pos_utils",
sqlid="filter_pos_log_sql",
app="pos",
params={},
id_part={})
pos_log_list = customSql(pos_log_sql, False).fetchall()
for p_obj in pos_log_list:
sys_card_no = p_obj[0]
card_serial_num = p_obj[1]
# delete_sql = delete_break_log%({"sys_card_no":sys_card_no,"card_serial_num":card_serial_num})
params = {
"sys_card_no": sys_card_no,
"card_serial_num": card_serial_num
}
delete_sql = get_sql("ic_pos_utils",
sqlid="delete_break_log",
app="pos",
params=params,
id_part={})
cursor.execute(delete_sql)
conn._commit()
except:
conn.close()
import traceback
traceback.print_exc()
pass
def MakeData(self,request,**arg):
st=request.REQUEST.get('ComeTime','')
et=request.REQUEST.get('EndTime','')
st=datetime.strptime(st,'%Y-%m-%d')
et=datetime.strptime(et,'%Y-%m-%d')
et=et+timedelta(days=1)
if st and et:
params={"st":st,"et":et}
if get_option("POS_ID"):
self.grid.sql = sql_utils.get_sql('id_report_sql',sqlid='get_id_manage_report_sql',app='pos',params=params)
else:
self.grid.sql = sql_utils.get_sql('ic_report_sql',sqlid='get_ic_manage_report_sql',app='pos',params=params)
else:
self.SetBlank()
def get_card_number_select(*args):
u"""
实时获取控制器中未注册的卡
params:
args[0]:log_id
args[1]:time_now
args[2]:door_list
"""
sql=''
params={}
id_part={}
if args[0] == 'undefined':
if args[1] == 'undefined':
args[1] = datetime.datetime.now()
args[1] = time_now.strftime("%Y-%m-%d %X")
params={'time_now':args[1],'door_list':args[2]}
id_part['where']='undefined_true'
else:
params={'log_id':args[0],'door_list':args[2]}
id_part['where']='undefined_false'
sql=sql_utils.get_sql('sql','get_card_number_select','iaccess',params,id_part)
return sql
def id_SZ_summary_report(st,et,check_opreate):
# sql='''
# select
# sum(case when type_id=1 then money else 0 end) as recharge_money,
# sum(case when type_id=1 then 1 else 0 end) as recharge_count,
# sum(case when type_id=5 then money else 0 end) as refund_money,
# sum(case when type_id=5 then 1 else 0 end) as refund_count,
# sum(case when type_id=7 then money else 0 end) as cost_money,
# sum(case when type_id=11 then money else 0 end) as manage_money,
# sum(case when type_id=7 then 1 else 0 end) as hairpin_count,
# sum(case when type_id=4 then 1 else 0 end) as back_card_count,
# sum(case when type_id=4 then money else 0 end) as back_card_money %(where)s
#
# '''
params={}
id_part={}
params['st'] = st
params['et'] = et
if check_opreate == 'checked':
id_part['where'] = "check_opreate"
# sql_db = sql%({'where':" ,create_operator from pos_CarCashSZ where checktime>='%s' and checktime <'%s' and create_operator !='0' group by create_operator"%(st,et)})
else:
id_part['where'] = "not_check_opreate"
# sql_db = sql%({'where':" from pos_carcashsz where checktime>='%s' and checktime <'%s'"%(st,et)})
db_sql = get_sql("id_report_sql",sqlid="id_SZ_summary_report",app = "pos",params = params ,id_part = id_part )
return db_sql
def get_ic_list_summary__sql(table_name, st, et, tag, operate, pos_model,
dining, ids, userids):
params = {}
id_part = {}
params["table_name"] = table_name
params["st"] = st
params["et"] = et
id_part["and"] = []
if operate != "9999":
params["operate"] = operate
id_part["and"].append("operate")
if pos_model != "0": #消费模式
params["pos_model"] = pos_model
id_part["and"].append("pos_model")
if dining != "0": #餐厅
params["dining"] = dining
id_part["and"].append("dining")
if len(ids) == 0: #没有选择人
if tag == "NoEmp":
id_part["and"].append("no_emp")
else:
params["userids"] = userids
id_part["and"].append("userids")
sql = get_sql("ic_report_sql",
sqlid="get_ic_list_summary__sql",
app="pos",
params=params,
id_part=id_part)
return sql
def get_ic_card_blance_report(userids,
ids,
operate,
tag,
begin_time=None,
end_time=None,
card_type=None):
# sql="select UserID_id,cardno,type_id,blance,create_operator,sys_card_no from personnel_issuecard where cardstatus in (1,3,4,5) and card_privage = 0 and status = 0 "
params = {}
id_part = {}
id_part["and"] = []
if card_type: #无卡退卡表
params["card_type"] = card_type
params["st"] = begin_time
params["et"] = end_time
id_part["and"].append("card_type")
else:
if operate != "9999":
params["operate"] = operate
id_part["and"].append("operate")
if len(ids) == 0: #没有选择人
if tag == "NoEmp":
id_part["and"].append("no_emp")
else:
params["userids"] = userids
id_part["and"].append("userids")
sql = get_sql("ic_report_sql",
sqlid="get_ic_card_blance_report",
app="pos",
params=params,
id_part=id_part)
return sql
def MakeData(self,request,**arg):
userids,d1,d2 = parse_grid_arg(request)
if userids and d1 and d1:
params={"userids": ','.join(userids),"st":d1,"et":d2}
self.grid.sql = sql_utils.get_sql('sql',sqlid='cardtimes',app='att',params=params)
else:
self.SetBlank()
def id_dining_or_dept_or_device_report_sql(dbname,op_id,begin_time,end_time):
sql='''
select
sum(case when type_id=9 then money else 0 end) as back_money,
sum(case when type_id=9 then 1 else 0 end) as back_count,
sum(case when type_id in (6,8) then 1 else 0 end) as pos_count,
sum(case when type_id in (6,8) then money else 0 end) as summary_money,
sum(case when type_id=10 then 1 else 0 end) as total_count ,
sum(case when type_id=8 then money else 0 end) as add_single_money,
sum(case when type_id=10 then money else 0 end) as total_money,
sum(case when type_id=12 then 1 else 0 end) as total_back_count ,
sum(case when type_id=12 then money else 0 end) as total_back_money %(where)s
'''
params={}
id_part={}
params['op_id'] = op_id
params['st'] = begin_time
params['et'] = end_time
if dbname == 'Dininghall':
id_part['where'] = "Dininghall"
# sql_db = sql%({'where':" from pos_carcashsz where dining_id = %s and checktime>='%s' and checktime <'%s'"%(op_id,begin_time,end_time)})
elif dbname == 'Device':
id_part['where'] = "Device"
# sql_db = sql%({'where':" from pos_carcashsz where sn_name = '%s' and checktime>='%s' and checktime <'%s'"%(op_id,begin_time,end_time)})
elif dbname == 'Department':
id_part['where'] = "Department"
# sql_db = sql%({'where':" from pos_carcashsz where dept_id = %s and checktime>='%s' and checktime <'%s'"%(op_id,begin_time,end_time)})
sql_db = get_sql("id_report_sql",sqlid="id_dining_or_dept_or_device_report_sql",app = "pos",params = params ,id_part = id_part )
return sql_db
def parse_event_to_sql_insert():
u"""
插入数据库的sql语句
"""
sql=''
sql=sql_utils.get_sql('sql','parse_event_to_sql_insert','iaccess',only_content=True)
return sql
def ic_dining_or_dept_or_device_report_sql(dbname, op_id, begin_time,
end_time):
# sql = '''
# select
# sum(case when pos_model=9 then money else 0 end) as back_money,
# sum(case when pos_model=9 then 1 else 0 end) as back_count,
# sum(case when type_name=6 then 1 else 0 end) as pos_count,
# sum(case when type_name in (6,8) then money else 0 end) as summary_money,
# sum(case when pos_model=4 then 1 else 0 end) as total_count,
# sum(case when type_name=8 then money else 0 end) as add_single_money %(where)s
# '''
params = {}
id_part = {}
params['op_id'] = op_id
params['st'] = begin_time
params['et'] = end_time
if dbname == 'Dininghall':
id_part['where'] = "Dininghall"
# sql_db = sql%({'where':" from pos_icconsumerlist where dining_id = %s and pos_time>='%s' and pos_time <'%s'"%(op_id,begin_time,end_time)})
elif dbname == 'Device':
id_part['where'] = "Device"
# sql_db = sql%({'where':" from pos_icconsumerlist where dev_sn = '%s' and pos_time>='%s' and pos_time <'%s'"%(op_id,begin_time,end_time)})
elif dbname == 'Department':
id_part['where'] = "Department"
# sql_db = sql%({'where':" from pos_icconsumerlist where dept_id = %s and pos_time>='%s' and pos_time <'%s'"%(op_id,begin_time,end_time)})
sql_db = get_sql("ic_report_sql",
sqlid="ic_dining_or_dept_or_device_report_sql",
app="pos",
params=params,
id_part=id_part)
return sql_db
def check_repeated_data(stamp_name, data_obj):
if stamp_name == 'pos_log_stamp':
# SELECT_ICCONSUMERLIST = "if not exists(select user_id from pos_icconsumerlist where (dev_sn='%s' and sys_card_no='%s' and money='%s' and card_serial_num='%s'and dev_serial_num='%s' and pos_time ='%s'))" % (data_obj.dev_sn,data_obj.sys_card_no,data_obj.money,data_obj.card_serial_num,data_obj.dev_serial_num,data_obj.pos_time)
params = {}
id_part = {}
params["dev_sn"] = data_obj.dev_sn
params["sys_card_no"] = data_obj.sys_card_no
params["money"] = data_obj.money
params["card_serial_num"] = data_obj.card_serial_num
params["dev_serial_num"] = data_obj.dev_serial_num
params["pos_time"] = data_obj.pos_time
SELECT_ICCONSUMERLIST = get_sql("ic_pos_utils",
sqlid="pos_log_exists",
app="pos",
params=params,
id_part=id_part)
return SELECT_ICCONSUMERLIST
elif stamp_name == 'full_log_stamp':
# SELECT_FULL = "if not exists(select user_id from pos_carcashsz where (sn_name='%s' and hide_column='%s' and sys_card_no='%s' and money='%s' and checktime='%s'))" %(data_obj.sn_name,data_obj.hide_column,data_obj.sys_card_no,data_obj.money,data_obj.checktime)
params = {}
id_part = {}
params["sn_name"] = data_obj.sn_name
params["hide_column"] = data_obj.hide_column
params["sys_card_no"] = data_obj.sys_card_no
params["money"] = data_obj.money
params["checktime"] = data_obj.checktime
SELECT_FULL = get_sql("ic_pos_utils",
sqlid="full_log_exists",
app="pos",
params=params,
id_part=id_part)
return SELECT_FULL
elif stamp_name == 'allow_log_stamp':
# SELECT_ALLOW ="if not exists(select user_id from pos_carcashsz where (sn_name='%s' and hide_column='%s' and sys_card_no='%s' and money='%s' and checktime='%s'))" %(data_obj.sn_name,data_obj.hide_column,data_obj.sys_card_no,data_obj.money,data_obj.checktime)
params = {}
id_part = {}
params["sn_name"] = data_obj.sn_name
params["hide_column"] = data_obj.hide_column
params["sys_card_no"] = data_obj.sys_card_no
params["money"] = data_obj.money
params["checktime"] = data_obj.checktime
SELECT_ALLOW = get_sql("ic_pos_utils",
sqlid="allow_log_exists",
app="pos",
params=params,
id_part=id_part)
return SELECT_ALLOW
def get_id_cost_report_sql(type_id,begin_time,end_time):
# sql="select user_id,card,cardserial,checktime,create_operator,money from pos_carcashsz where type_id='%s' and checktime>='%s' and checktime <'%s' "%(type_id,begin_time,end_time)
params={}
id_part={}
params["st"] = begin_time
params["et"] = end_time
params["type_id"] = type_id
sql = get_sql("id_report_sql",sqlid="get_id_cost_report_sql",app = "pos",params = params ,id_part = id_part )
return sql
def process_event_log_select3():
u"""
查询全部人员的编号，id
"""
sql=''
params={}
id_part={}
sql=sql_utils.get_sql('sql','process_event_log_select3','iaccess',params,id_part)
return sql
def get_tjjgxq_report_sql(userids, d1, d2):
'''
统计结果详情表
'''
params = {"userids": userids, "st": d1, "et": d2}
# id_part={"where":["date","uids"]}
sql = sql_utils.get_sql('sql',
sqlid='get_tjjgxq_report_sqla',
app='att',
params=params)
return sql
def MakeData(self,request,**arg):
from mysite.att.calculate.global_cache import C_ATT_RULE,C_LEAVE_CLASS
C_ATT_RULE.action_init()
C_LEAVE_CLASS.action_init()
self.SetPageSize(0)
ids = request.REQUEST.get('ids','')
if ids:
params={"ids": ids}
self.grid.sql = sql_utils.get_sql('sql',sqlid='leave_exception_attshifts',app='att',params=params)
else:
self.SetBlank()
def get_calc_report_sql(userids, d1, d2):
'''
考勤汇总表/每日考勤统计表 sql
'''
params = {"userids": userids, "st": d1, "et": d2}
# id_part={"where":["date","uids"]}
sql = sql_utils.get_sql('sql',
sqlid='get_calc_report_sqla',
app='att',
params=params)
return sql
def process_event_log_insert(*args):
u"""
插入原始记录表
"""
sql=''
params={}
id_part={}
params={'sn_name':args[0], 'pin':args[1],'checktime':args[2]}
sql=sql_utils.get_sql('sql','process_event_log_insert','iaccess',params,id_part)
return sql
def GenerateEmpFlow_sql(request,deptids,d1,d2):
params={"deptids":deptids,"d1":d1,"d2":d2}
id_part={}
if deptids:
id_part["where"]="hasdeptids"
else:
depts = userDeptList(request.user)
if depts:
params["depts"]=",".join([str(i.id) for i in depts])
id_part["where"]="nodeptids"
sql=sql_utils.get_sql('sql',sqlid='GenerateEmpFlow_sql',app='personnel',params=params,id_part=id_part)
return sql
def process_event_log_select2(*args):
u"""
查询大于这个时间的考勤原始记录信息
"""
sql=''
params={}
id_part={}
dateStr=strtodatetime(args[0])
params={'dateStr':dateStr}
sql=sql_utils.get_sql('sql','process_event_log_select2','iaccess',params,id_part)
return sql
def sync_to_att_insert(*args):
u"""
门禁控制器事件记录写入考勤原始记录表
"""
sql =" "
params={}
id_part={}
params={'pin':args[2],'sn_name':args[0],'checktime':args[1]}
sql=sql_utils.get_sql('sql','sync_to_att_insert','iaccess',params,id_part)
return sql
def search_accuser_bydevice_select(*args):
u"""
params:
args[0]:设备id
"""
sql=''
params={}
id_part={}
params={'devID':args[0]}
sql=sql_utils.get_sql('sql','search_accuser_bydevice_select','iaccess',params,id_part)
return sql
def get_ic_recharge_report_sql(begin_time, end_time):
# sql="select user_id,card,cardserial,money,blance,checktime,create_operator,convey_time,sys_card_no,sn_name,log_flag,type_id,serialnum from pos_carcashsz where type_id in (1,13) and checktime>='%s' and checktime <'%s' "%(begin_time,end_time)
params = {}
id_part = {}
params["st"] = begin_time
params["et"] = end_time
sql = get_sql("ic_report_sql",
sqlid="get_ic_recharge_report_sql",
app="pos",
params=params,
id_part=id_part)
return sql
def __init__(self, request):
super(CheckInOutGrid, self).__init__()
#设置sql
self.grid.sql = sql_utils.get_sql('sql', sqlid='checkinout', app='att')
#设置 colum 属性
self.SetHide("id")
self.grid.fields["name"]["width"] = 100
self.grid.fields["badgenumber"]["width"] = 100
self.grid.fields["name"]["width"] = 100
self.grid.fields["DeptName"]["width"] = 100
self.grid.fields["checktime"]["width"] = 150
self.grid.fields["checktype"]["width"] = 100
self.grid.fields["sn_name"]["width"] = 120
def ic_device_or_dining_error_log_sql(dbname, op_id, begin_time, end_time):
params = {}
id_part = {}
params['op_id'] = op_id
params['st'] = begin_time
params['et'] = end_time
if dbname == 'Dininghall':
id_part['where'] = "Dininghall"
sql_db = get_sql("ic_report_sql",
sqlid="ic_device_or_dining_error_log_sql",
app="pos",
params=params,
id_part=id_part)
elif dbname == 'Device':
id_part['where'] = "Device"
sql_db = get_sql("ic_report_sql",
sqlid="ic_device_or_dining_error_log_sql",
app="pos",
params=params,
id_part=id_part)
return sql_db
def process_event_log_select1(*args):
u"""
获取记录，第一条为最旧的数据，查询时间比它大的记录
"""
sql=''
params={}
id_part={}
time1=args[1] and args[1][0]
params={'DevID':args[0],'time1':time1}
sql=sql_utils.get_sql('sql','process_event_log_select1','iaccess',params,id_part)
return sql
def get_id_device_query(device_sn, begin_time, end_time):
# sql="select user_id,card,cardserial,money,blance,checktime,create_operator from pos_carcashsz where type_id='%s' and checktime>='%s' and checktime <'%s' "%(type_id,begin_time,end_time)
params = {}
id_part = {}
params["st"] = begin_time
params["et"] = end_time
params["device_sn"] = device_sn
sql = get_sql("id_posdevview_sql",
sqlid="get_id_device_query",
app="pos",
params=params,
id_part=id_part)
return sql
def checkdevice_and_savecache_update(*args):
u"""
修改最后连接时间
params:
last_activity
devobj.id
"""
sql=''
params={}
id_part={}
params={'last_activity':args[0],'DevID':args[1]}
sql=sql_utils.get_sql('sql','checkdevice_and_savecache_update','iaccess',params,id_part)
return sql
def set_multicard_select(*args):
u"""
同步设备的多卡开卡
params:
args[0]:门id
"""
sql=''
id_part={}
params={}
params={'door_id':args[0]}
sql=sql_utils.get_sql('sql','set_multicard_select','iaccess',params,id_part)
return sql
def MakeData(self,request,**arg):
sql_add = ""
st=request.REQUEST.get('ComeTime','')
et=request.REQUEST.get('EndTime','')
st=datetime.strptime(st,'%Y-%m-%d')
et=datetime.strptime(et,'%Y-%m-%d')
et=et+timedelta(days=1)
operate=request.REQUEST.get('operate','')
if operate!="9999":
sql_add +=" and l.create_operator='%s' "%operate
et=et+timedelta(days=1)
if st and et:
params={"st":st,"et":et}
if get_option("POS_ID"):
sql = sql_utils.get_sql('id_report_sql',sqlid='get_id_lost_card_report_sql',app='pos',params=params)
sql+= sql_add
else:
sql = sql_utils.get_sql('ic_report_sql',sqlid='get_ic_lost_card_report_sql',app='pos',params=params)
sql+= sql_add
self.grid.sql = sql
else:
self.SetBlank()
def get_emp_num_run_sql():
'''
获取所有班次
'''
# sql = '''
# select Num_runID,Units,Cyle from num_run
# '''
params = {}
id_part = {}
sql = sql_utils.get_sql('sql',
'get_emp_num_run_sql',
'att',
params=params,
id_part=id_part)
return sql
