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