def do_rec(cur,cur_mysql,in_sql): while 1: one = cur.fetchone() id = '' summary_id = 0 if one!=None: sql = in_sql cnt = len(one) for i in range(cnt): if i!=0: sql += ',' else: id = one[0] sql += '"'+str(one[i])+'"' else: break sql += ")" if utils.is_include(cur_mysql,'col_user_message',id)==0: #print("++++") #print(sql) cur_mysql.execute(sql)
def do_rec(cur, cur_mysql, in_sql): line_id = None while 1: subject = '' start_date = '2000-01-01 00:00:00' one = cur.fetchone() if one != None: sql = in_sql #print(':\n-------------------------------%s' % type(one)) cnt = len(one) #print one for i in range(cnt): #print i,one[i] if i != 0: if i == 12: form_appid = one[12] if form_appid == None: #print(">>>form_appid=%s" % str(form_appid)) line_id = None else: # 注意:针对优先办理等特权情况,无法用此方法获取line_id,因为这些form可针对所有业务 line_id = utils.get_lineid_by_formid( cur_mysql, form_appid) #print(">>>line_id=%s<<<" % line_id) sql += ',' else: summary_id = one[0] if i == 3: # 新增记录,设定法定期限为8天 sql += '8' elif i == 13: # for VOUCH sql += '0' else: sql += '"' + str(one[i]) + '"' if i == 2: subject = str(one[i]).replace('"', '-') if i == 8: start_member = str(one[i]) if i == 6: start_date = str(one[i]) else: break # 设定岗位期限为8天 sql += ",8," if utils.is_include(cur_mysql, 'col_summary', summary_id) == 0: print("++++") # 2015-12-19: 新加入的summary没有优先办理特权 if line_id != None: if "分管领导指派优先办理" in subject: sql += "0,5,0)" else: # 设置新增的申请单在 业务线的第一个(sn=0)岗位上 sql += "%s,1,0)" % line_id else: sql += "0,1,0)" print sql # 保存记录 cur_mysql.execute(sql) # 添加流水号 yw_sn = utils.get_sn(cur_mysql, summary_id) print(">>>流水号:%s<<<" % yw_sn) if yw_sn != None: rec = utils.build_ServiceObject(cur_mysql, summary_id) sql = 'update col_summary set yw_sn="%s",' \ 'org_code="%s",org_name="%s",org_addr="%s",org_capital="%s",org_reg_number="%s",org_reg_addr="%s",' \ 'legal_person="%s",legal_person_tel="%s",legal_person_cid="%s" where id="%s"' % \ (yw_sn,rec['bar_code'],rec['name'],rec['addr'],rec['registered_capital'], rec['registeration_number'],rec['registered_addr'], rec['legal_representative_name'],rec['legal_representative_tel'],rec['legal_representative_id'],summary_id) print sql cur_mysql.execute(sql)
def do_rec(cur,cur_mysql,in_sql): line_id = None while 1: subject = '' start_date = '2000-01-01 00:00:00' one = cur.fetchone() if one!=None: sql = in_sql #print(':\n-------------------------------%s' % type(one)) cnt = len(one) #print one for i in range(cnt): #print i,one[i] if i!=0: if i==12: form_appid = one[12] if form_appid == None: #print(">>>form_appid=%s" % str(form_appid)) line_id = None else: # 注意:针对优先办理等特权情况,无法用此方法获取line_id,因为这些form可针对所有业务 line_id = utils.get_lineid_by_formid(cur_mysql,form_appid) #print(">>>line_id=%s<<<" % line_id) sql += ',' else: summary_id = one[0] if i==3: # 新增记录,设定法定期限为8天 sql += '8' elif i==13: # for VOUCH sql += '0' else: sql += '"'+str(one[i])+'"' if i==2: subject = str(one[i]).replace('"','-') if i==8: start_member = str(one[i]) if i==6: start_date = str(one[i]) else: break # 设定岗位期限为8天 sql += ",8," if utils.is_include(cur_mysql,'col_summary',summary_id)==0: print("++++") # 2015-12-19: 新加入的summary没有优先办理特权 if line_id!=None: if "分管领导指派优先办理" in subject: sql += "0,5,0)" else: # 设置新增的申请单在 业务线的第一个(sn=0)岗位上 sql += "%s,1,0)" % line_id else: sql += "0,1,0)" print sql # 保存记录 cur_mysql.execute(sql) # 添加流水号 yw_sn = utils.get_sn(cur_mysql,summary_id) print(">>>流水号:%s<<<" % yw_sn) if yw_sn!=None: rec = utils.build_ServiceObject(cur_mysql,summary_id) sql = 'update col_summary set yw_sn="%s",' \ 'org_code="%s",org_name="%s",org_addr="%s",org_capital="%s",org_reg_number="%s",org_reg_addr="%s",' \ 'legal_person="%s",legal_person_tel="%s",legal_person_cid="%s" where id="%s"' % \ (yw_sn,rec['bar_code'],rec['name'],rec['addr'],rec['registered_capital'], rec['registeration_number'],rec['registered_addr'], rec['legal_representative_name'],rec['legal_representative_tel'],rec['legal_representative_id'],summary_id) print sql cur_mysql.execute(sql)
if len(o_current_nodes_info)>1: # 如果该节点包含多人“竞争”办理,则需要做一个标记 #print(">>> multi_user <<<") multi_users = True # 取第一个人员做为下一节点办理人 o_current_nodes_info = o_current_nodes_info[0] o_state = str(one[2]) #print(">>>o_state=%s<<<" % o_state) if o_state == "None" or o_state == "NONE": # 在政务大厅提交后的 申请单,其state好像是None continue if utils.is_include(cur_mysql,'col_summary',summary_id) != 0: # 针对已有的申请单 sql = 'select current_nodes_info,pri,subject,line_id,sn,finish_date,state,cnt,yw_sn from col_summary where state=0 and id="%s" order by create_date' % summary_id cnt = cur_mysql.execute(sql) if cnt>0: one = cur_mysql.fetchone() m_current_nodes_info = str(one[0]) m_pri = str(one[1]) m_subject = str(one[2]) line_id = str(one[3]) sn = str(one[4]) finish_date = str(one[5]) m_state = str(one[6]) # 剩余天数 m_cnt = str(one[7])
def do_rec(cur, cur_mysql, in_sql): line_id = None while 1: subject = '' start_date = '2000-01-01 00:00:00' one = cur.fetchone() if one != None: sql = in_sql #print(':\n-------------------------------%s' % type(one)) cnt = len(one) #print one for i in range(cnt): #print i,one[i] if i != 0: if i == 12: form_appid = one[12] if form_appid == None: #print(">>>form_appid=%s" % str(form_appid)) line_id = None else: # 注意:针对优先办理等特权情况,无法用此方法获取line_id,因为这些form可针对所有业务 line_id = utils.get_lineid_by_formid( cur_mysql, form_appid) #print(">>>line_id=%s<<<" % line_id) sql += ',' else: summary_id = one[0] if i == 3: # 新增记录,设定法定期限为8天 sql += '8' elif i == 13: # for VOUCH sql += '0' else: sql += '"' + str(one[i]) + '"' if i == 2: subject = str(one[i]) if i == 8: start_member = str(one[i]) if i == 6: start_date = str(one[i]) else: break # 设定岗位期限为8天 sql += ",8," if utils.is_include(cur_mysql, 'col_summary', summary_id) == 0: print("++++") # 2015-12-19: 新加入的summary没有优先办理特权 if line_id != None: if "分管领导指派优先办理" in subject: sql += "0,5,0)" else: # 设置新增的申请单在 业务线的第一个(sn=0)岗位上 sql += "%s,1,0)" % line_id else: sql += "0,1,0)" print sql # 保存记录 cur_mysql.execute(sql) # 添加流水号 yw_sn = utils.get_sn(cur_mysql, summary_id) print(">>>流水号:%s<<<" % yw_sn) if yw_sn != None: sql = 'update col_summary set yw_sn="%s" where id="%s"' % ( yw_sn, summary_id) #print sql cur_mysql.execute(sql) else: yw_sn = "" #-------------------------------------------------- # !!!若这是一个“特权”事件,则需执行其特权行为 !!! # utils.set_summary_pri(cur_mysql, summary_id, yw_sn, start_member) # for resent_time,用于放置 受理时间 if (("人力资源服务许可" in subject) or ("劳务派遣" in subject) or ("自动发起" in subject)) and (line_id is not None): # 获取业务节点(受理)期限(天) nday = utils.get_post_deadline(cur_mysql, line_id, 1) if nday is not None: sql = 'update col_summary set cnt="%s" where id="%s"' % ( nday, summary_id) #print sql cur_mysql.execute(sql) # 获取该申请的受理时间 acc_time = utils.get_summary_feild_value( cur_mysql, summary_id, "受理时间") #print(">>>受理时间:%s" % val ) if acc_time is not None: sql = 'update col_summary set resent_time="%s" where id="%s"' % ( acc_time, summary_id) #print sql cur_mysql.execute(sql) else: # 若无受理时间,则认为是当前时间 acc_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time())) sql = 'update col_summary set resent_time="%s" where id="%s"' % ( acc_time, summary_id) #print sql cur_mysql.execute(sql) # 通过业务线line_id,检查申请是否带齐必要的附件 # 如果未带齐,则发出“风险”警告 # #print(">>>1<<<") # 2015-12-27 针对“补正”系统会自动发起新的summary记录,此时,不用判断附件情况 # if ("(自动发起)" not in subject) and (not utils.chk_file( cur_mysql, summary_id, line_id)): #print(">>>Here!<<<") user_name = utils.get_user_name(cur_mysql, start_member) utils.send_alarm(cur_mysql,start_member,summary_id,line_id,yw_sn,"【数据铁笼风险提示】:%s,在受理《%s》<%s>时,提交材料存在缺失项,请知悉。" \ % (user_name,subject,yw_sn)) # 向上级汇报 members = utils.get_leader(cur_mysql, line_id, 0, 0) members += utils.get_leader(cur_mysql, line_id, 0, 1) for member in members: leader_name = utils.get_user_name(cur_mysql, member) #send_alarm(cur_mysql1,member,"【数据铁笼提示】:%s,好:%s在办的《%s》%s已超期,请知悉。" % (leader_name,user_name,subject,post_name)) # 负数表示超出的天数 utils.send_info(cur_mysql,member,"【数据铁笼风险通报】%s,好:%s在受理《%s》时,提交材料存在缺失项,请知悉。" \ % (leader_name,user_name,subject)) # 检查提交时间start_date与“受理时间”acc_time之间的间隔是否超过一天? # 若超过,则报风险 now_d = datetime.datetime.now() if acc_time == "None" or acc_time == None or acc_time == "NONE": acc_time = now_d.strftime("%Y-%m-%d %H:%M:%S") acc_d = datetime.datetime.strptime(acc_time, "%Y-%m-%d %H:%M:%S") # 时间戳(分钟计) now_ts = int(time.mktime(now_d.timetuple())) / 60 acc_ts = int(time.mktime(acc_d.timetuple())) / 60 # 记录 某人 在 该业务线节点上 处理申报所占用时间 now_ts - last_ts min_cnt = utils.cal_workdays( cur_mysql, acc_time, now_d.strftime('%Y-%m-%d %H:%M:%S')) if "(自动发起)" in subject: sql = 'insert into kpi_001(member,summary_id,line_id,sn,dtime,start_date) values("%s","%s",%s,%s,%d,"%s")' \ % (start_member,summary_id,line_id,1,min_cnt,acc_time) else: sql = 'insert into kpi_001(member,summary_id,line_id,sn,dtime,start_date) values("%s","%s",%s,%s,%d,"%s")' \ % (start_member,summary_id,line_id,0,min_cnt,acc_time) #sql = 'insert into kpi_001(member,summary_id,line_id,sn,dtime,start_date) values("%s","%s",%s,%s,%d,"%s")' \ # % (start_member,summary_id,line_id,0,now_ts-acc_ts,acc_time) cur_mysql.execute(sql) # 添加 业务日志 utils.yw_log(cur_mysql, summary_id, start_member, 0, acc_time, now_d.strftime('%Y-%m-%d %H:%M:%S'), min_cnt) #if (now_ts>acc_ts) and ((now_ts-acc_ts)>(24*60)): # 工作耗时超过7.5小时 if min_cnt >= 450: # 提交日期 超过 受理日期 一天以上 # 风险提示 user_name = utils.get_user_name(cur_mysql, start_member) utils.send_alarm(cur_mysql,start_member,summary_id,line_id,yw_sn,"【数据铁笼风险提示】:%s,在受理《%s》<%s>时超出一天期限,请知悉。" \ % (user_name,subject,yw_sn)) # 向上级汇报 members = utils.get_leader(cur_mysql, line_id, 0, 0) members += utils.get_leader(cur_mysql, line_id, 0, 1) for member in members: leader_name = utils.get_user_name(cur_mysql, member) utils.send_info(cur_mysql,member,"【数据铁笼风险通报】%s,好:%s在受理《%s》时,超出岗位工作期限,请知悉。" \ % (leader_name,user_name,subject)) # 需要根据此情况调整 deadline 的值 n = 8 - (now_ts - acc_ts) / (24 * 60) sql = 'update col_summary set deadline=%d where id="%s"' % ( n, summary_id) cur_mysql.execute(sql) # 设置第一个时间点 # 把finish_date用于记录本节点的完成时间 # now = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') sql = 'update col_summary set finish_date="%s" where id="%s"' % ( now, summary_id) cur_mysql.execute(sql)
if len(o_current_nodes_info) > 1: # 如果该节点包含多人“竞争”办理,则需要做一个标记 #print(">>> multi_user <<<") multi_users = True # 取第一个人员做为下一节点办理人 o_current_nodes_info = o_current_nodes_info[0] o_state = str(one[2]) #print(">>>o_state=%s<<<" % o_state) if o_state == "None" or o_state == "NONE": # 在政务大厅提交后的 申请单,其state好像是None continue if utils.is_include(cur_mysql, 'col_summary', summary_id) != 0: # 针对已有的申请单 sql = 'select current_nodes_info,pri,subject,line_id,sn,finish_date,state,cnt,yw_sn from col_summary where state=0 and id="%s" order by create_date' % summary_id cnt = cur_mysql.execute(sql) if cnt > 0: one = cur_mysql.fetchone() m_current_nodes_info = str(one[0]) m_pri = str(one[1]) m_subject = str(one[2]) line_id = str(one[3]) sn = str(one[4]) finish_date = str(one[5]) m_state = str(one[6]) # 剩余天数 m_cnt = str(one[7])
def do_rec(cur,cur_mysql,in_sql): while 1: text = '' create_date = '2000-01-01 00:00:00' out_flg = False id = '' one = cur.fetchone() summary_id = 0 sender_id = '' if one is not None: sql = in_sql cnt = len(one) for i in range(cnt): if i != 0: sql += ',' if i == 1: sender_id = str(one[i]) if i == 2: text = str(one[i]) # 过滤掉 政务大厅发起协同 的信息 if "发起协同" in text: out_flg = True break summary_id = get_summary_id_by_subject(cur_mysql,text) if sender_id == "8764456166134006930": if "数据铁笼预警" in text: # 预警中包含这些关键词 flg = 2 elif "数据铁笼风险提示" in text: # 由数据铁笼发出信息,不是预警就是风险了 flg = 3 else: # 应该是向上级的汇报信息 flg = 1 else: flg = 1 if i==3: create_date=str(one[3]) else: id = one[0] sql += '"'+str(one[i])+'"' else: break if out_flg: continue if summary_id is not None: sql += ",%d,%s)" % (flg,str(summary_id)) else: sql += ",%d,0)" % flg if utils.is_include(cur_mysql,'ctp_user_message',id)==0: # 问题:即使ID不同,也会有很多内容相同的信息,也应该滤去 #sql1 = 'select * from ctp_user_message where message="%s" and create_date="%s"' % (text, create_date) #cnt = cur_mysql.execute(sql1) #if cnt > 0: # continue #print("++++") #print(sql) cur_mysql.execute(sql)
# # 2015.12.10 by shenwei @GuiYang # ============================== # 从OA同步ctp_attachment表 # import utils cur_mysql = utils.mysql_conn() oracle_conn = utils.oracle_conn() cur_oracle = oracle_conn.cursor() cur = cur_oracle.execute('select ID,REFERENCE,SUB_REFERENCE,FILENAME,FILE_URL,CREATEDATE,ATTACHMENT_SIZE from ctp_attachment order by createdate') while 1: one = cur.fetchone() if one!=None: id = str(one[0]) if utils.is_include(cur_mysql,'ctp_attachment',id)==0: sql = 'insert into ctp_attachment(ID,REFERENCE,SUB_REFERENCE,FILENAME,FILE_URL,CREATEDATE,ATTACHMENT_SIZE) values(' for i in range(len(one)): if i==0: sql += str(one[0]) elif i==3 or i==5: sql += ',"'+ str(one[i])+'"' else: sql += ','+ str(one[i]) sql += ')' #print sql cur_mysql.execute(sql) else: break
def do_rec(cur, cur_mysql, in_sql): line_id = None while 1: subject = "" start_date = "2000-01-01 00:00:00" one = cur.fetchone() if one != None: sql = in_sql # print(':\n-------------------------------%s' % type(one)) cnt = len(one) # print one for i in range(cnt): # print i,one[i] if i != 0: if i == 12: form_appid = one[12] if form_appid == None: # print(">>>form_appid=%s" % str(form_appid)) line_id = None else: # 注意:针对优先办理等特权情况,无法用此方法获取line_id,因为这些form可针对所有业务 line_id = utils.get_lineid_by_formid(cur_mysql, form_appid) # print(">>>line_id=%s<<<" % line_id) sql += "," else: summary_id = one[0] if i == 3: # 新增记录,设定法定期限为8天 sql += "8" elif i == 13: # for VOUCH sql += "0" else: sql += '"' + str(one[i]) + '"' if i == 2: subject = str(one[i]) if i == 8: start_member = str(one[i]) if i == 6: start_date = str(one[i]) else: break # 设定岗位期限为8天 sql += ",8," if utils.is_include(cur_mysql, "col_summary", summary_id) == 0: print ("++++") # 2015-12-19: 新加入的summary没有优先办理特权 if line_id != None: if "分管领导指派优先办理" in subject: sql += "0,5,0)" else: # 设置新增的申请单在 业务线的第一个(sn=0)岗位上 sql += "%s,1,0)" % line_id else: sql += "0,1,0)" print sql # 保存记录 cur_mysql.execute(sql) # 添加流水号 yw_sn = utils.get_sn(cur_mysql, summary_id) print (">>>流水号:%s<<<" % yw_sn) if yw_sn != None: sql = 'update col_summary set yw_sn="%s" where id="%s"' % (yw_sn, summary_id) # print sql cur_mysql.execute(sql) else: yw_sn = "" # -------------------------------------------------- # !!!若这是一个“特权”事件,则需执行其特权行为 !!! # utils.set_summary_pri(cur_mysql, summary_id, yw_sn, start_member) # for resent_time,用于放置 受理时间 if (("人力资源服务许可" in subject) or ("劳务派遣" in subject) or ("自动发起" in subject)) and (line_id is not None): # 获取业务节点(受理)期限(天) nday = utils.get_post_deadline(cur_mysql, line_id, 1) if nday is not None: sql = 'update col_summary set cnt="%s" where id="%s"' % (nday, summary_id) # print sql cur_mysql.execute(sql) # 获取该申请的受理时间 acc_time = utils.get_summary_feild_value(cur_mysql, summary_id, "受理时间") # print(">>>受理时间:%s" % val ) if acc_time is not None: sql = 'update col_summary set resent_time="%s" where id="%s"' % (acc_time, summary_id) # print sql cur_mysql.execute(sql) else: # 若无受理时间,则认为是当前时间 acc_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time())) sql = 'update col_summary set resent_time="%s" where id="%s"' % (acc_time, summary_id) # print sql cur_mysql.execute(sql) # 通过业务线line_id,检查申请是否带齐必要的附件 # 如果未带齐,则发出“风险”警告 # # print(">>>1<<<") # 2015-12-27 针对“补正”系统会自动发起新的summary记录,此时,不用判断附件情况 # if ("(自动发起)" not in subject) and (not utils.chk_file(cur_mysql, summary_id, line_id)): # print(">>>Here!<<<") user_name = utils.get_user_name(cur_mysql, start_member) utils.send_alarm( cur_mysql, start_member, summary_id, line_id, yw_sn, "【数据铁笼风险提示】:%s,在受理《%s》<%s>时,提交材料存在缺失项,请知悉。" % (user_name, subject, yw_sn), ) # 向上级汇报 members = utils.get_leader(cur_mysql, line_id, 0, 0) members += utils.get_leader(cur_mysql, line_id, 0, 1) for member in members: leader_name = utils.get_user_name(cur_mysql, member) # send_alarm(cur_mysql1,member,"【数据铁笼提示】:%s,好:%s在办的《%s》%s已超期,请知悉。" % (leader_name,user_name,subject,post_name)) # 负数表示超出的天数 utils.send_info( cur_mysql, member, "【数据铁笼风险通报】%s,好:%s在受理《%s》时,提交材料存在缺失项,请知悉。" % (leader_name, user_name, subject), ) # 检查提交时间start_date与“受理时间”acc_time之间的间隔是否超过一天? # 若超过,则报风险 now_d = datetime.datetime.now() if acc_time == "None" or acc_time == None or acc_time == "NONE": acc_time = now_d.strftime("%Y-%m-%d %H:%M:%S") acc_d = datetime.datetime.strptime(acc_time, "%Y-%m-%d %H:%M:%S") # 时间戳(分钟计) now_ts = int(time.mktime(now_d.timetuple())) / 60 acc_ts = int(time.mktime(acc_d.timetuple())) / 60 # 记录 某人 在 该业务线节点上 处理申报所占用时间 now_ts - last_ts min_cnt = utils.cal_workdays(cur_mysql, acc_time, now_d.strftime("%Y-%m-%d %H:%M:%S")) if "(自动发起)" in subject: sql = ( 'insert into kpi_001(member,summary_id,line_id,sn,dtime,start_date) values("%s","%s",%s,%s,%d,"%s")' % (start_member, summary_id, line_id, 1, min_cnt, acc_time) ) else: sql = ( 'insert into kpi_001(member,summary_id,line_id,sn,dtime,start_date) values("%s","%s",%s,%s,%d,"%s")' % (start_member, summary_id, line_id, 0, min_cnt, acc_time) ) # sql = 'insert into kpi_001(member,summary_id,line_id,sn,dtime,start_date) values("%s","%s",%s,%s,%d,"%s")' \ # % (start_member,summary_id,line_id,0,now_ts-acc_ts,acc_time) cur_mysql.execute(sql) # 添加 业务日志 utils.yw_log( cur_mysql, summary_id, start_member, 0, acc_time, now_d.strftime("%Y-%m-%d %H:%M:%S"), min_cnt ) # if (now_ts>acc_ts) and ((now_ts-acc_ts)>(24*60)): # 工作耗时超过7.5小时 if min_cnt >= 450: # 提交日期 超过 受理日期 一天以上 # 风险提示 user_name = utils.get_user_name(cur_mysql, start_member) utils.send_alarm( cur_mysql, start_member, summary_id, line_id, yw_sn, "【数据铁笼风险提示】:%s,在受理《%s》<%s>时超出一天期限,请知悉。" % (user_name, subject, yw_sn), ) # 向上级汇报 members = utils.get_leader(cur_mysql, line_id, 0, 0) members += utils.get_leader(cur_mysql, line_id, 0, 1) for member in members: leader_name = utils.get_user_name(cur_mysql, member) utils.send_info( cur_mysql, member, "【数据铁笼风险通报】%s,好:%s在受理《%s》时,超出岗位工作期限,请知悉。" % (leader_name, user_name, subject), ) # 需要根据此情况调整 deadline 的值 n = 8 - (now_ts - acc_ts) / (24 * 60) sql = 'update col_summary set deadline=%d where id="%s"' % (n, summary_id) cur_mysql.execute(sql) # 设置第一个时间点 # 把finish_date用于记录本节点的完成时间 # now = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S") sql = 'update col_summary set finish_date="%s" where id="%s"' % (now, summary_id) cur_mysql.execute(sql)