def saveSelectedOptions(field_type, options_type, usr_id, sel_value): type = int(options_type) iN = 0 if type == 21: L, iN = get_proj_info_selected(sel_value) elif type == 23: #供应商 L, iN = get_sup_info_selected(sel_value) #elif type==24: #人员 # L,iN = get_addr_book_selected(sel_value) elif type == 26: L, iN = get_mat_info_selected(sel_value) if iN > 0: sql = "select id from user_options where usr_id=%s and option_type=%s and option_id=%s" % ( usr_id, options_type, sel_value) lT, iN1 = db.select(sql) if iN1 > 0: sql = "update user_options set ctime=now(), hits = hits +1 where id=%s" % ( lT[0][0]) else: sql = """insert into `user_options` (`usr_id`,`option_type`,`option_id`,`option_value`,`option_level`,`option_parent_id`,`option_tips`,`ctime`,`hits`) values (%s,%s,'%s','%s','%s','%s','%s',now(),1) """ % (usr_id, options_type, L[0][0], L[0][1], L[0][2], L[0][3], L[0][4]) print ToGBK(sql) db.executesql(sql) return
def get_proj_info(search, page_limit): L = [] sql = """select id,concat('(',ifnull(gc_no,''),')',ifnull(cname,'')) from out_proj where stage in (3,4,5) and ifnull(status,1)!=-1 and concat('(',ifnull(gc_no,''),')',ifnull(cname,'')) like '%%%s%%' order by id desc limit %s """ % (search, page_limit) #sql="""select id,concat('(',ifnull(gc_no,''),')',ifnull(cname,'')) from out_proj where ifnull(status,1)!=-1 and concat('(',ifnull(gc_no,''),')',ifnull(cname,'')) like '%%%s%%' order by id desc limit %s # """%(search,page_limit) print ToGBK(sql) lT, iN = db.select(sql) for e in lT: txt = e[1] L.append([e[0], txt, '', txt]) return L
def get_options_2753(search, page_limit, usr_id, request): search = search.replace("'", "''") L = [] sql = "select distinct top %s cname,cname from mat where status!=0 and cname like '%%%s%%' order by cname asc" % ( page_limit, search) print ToGBK(sql) L = get_sql_info_byerp(sql) names = 'value label checked tips'.split() if L == '' or L == None: return [] data = [dict(zip(names, d)) for d in L] data = json.dumps(data, ensure_ascii=False, cls=ComplexEncoder) return data
def getValidityResult(field_id, request): sql = "select ifnull(validity_sql,''),para_cols from menu_form_validity where field_id= '%s'" % ( field_id) lT, iN = db.select(sql) if iN == 0: return 1 sql = lT[0][0] para_cols = lT[0][1] paras = para_cols.split(',') print paras print request.POST for e in paras: if e == '': break sql = sql.replace("{%s}" % e, MySQLdb.escape_string(request.POST.get(e, ''))) print ToGBK(sql) lT, iN = db.select(sql) if iN == 0: return 1 return lT[0][0]
def index_wx_func(request): import base64, time import random random_no = '%s' % (random.randint(0, 999999)) source = 'wx' if request.META.has_key('HTTP_X_FORWARDED_FOR'): ip = request.META['HTTP_X_FORWARDED_FOR'] else: ip = request.META['REMOTE_ADDR'] print ip code = request.GET.get('code', '') func = request.GET.get('func', '') if func == '': func = request.GET.get('state', '') func = func.lower() if func in ['gwlist', 'gw_view', 'gw_audit', 'gw_sign']: agentname = '公文审批' elif func in [ 'info_send', 'info_list', 'info_upd', 'info_detail', 'info_audit' ]: agentname = '信息交换' elif func in ['myloglist', 'otherloglist', 'writelog', 'logdetail']: agentname = '工作日志' elif func in ['fa_code']: agentname = '固定资产管理' else: agentname = '通讯录' print func error_url = '%s/wx/mui/error.html' % front_url if func == 'FA_Code': FAcode = request.GET.get('facode', '') url = '%s/wx/mui/fixedAssetsInfo.html?code=%s' % (front_url, FAcode) return HttpResponseRedirect(url) url = '' if func == 'gwlist': type = request.GET.get('type', '') if str(type) in ['1', '2', '3']: url = '%s/wx/mui/examine.html?func=%s&type=%s' % (front_url, func, type) else: url = '%s/wx/mui/myExamine.html?func=%s&type=%s' % (front_url, func, type) elif func == 'gw_view': menu_id = request.GET.get('menu_id', '') pk = request.GET.get('pk', '') url = '%s/wx/examineDetail.html?menu_id=%s&pk=%s&mode=view&infotype=msg' % ( front_url, menu_id, pk) elif func == 'gw_audit': menu_id = request.GET.get('menu_id', '') pk = request.GET.get('pk', '') url = '%s/wx/examineDetail.html?menu_id=%s&pk=%s&mode=audit&infotype=msg' % ( front_url, menu_id, pk) elif func == 'gw_sign': menu_id = request.GET.get('menu_id', '') pk = request.GET.get('pk', '') url = '%s/wx/examineDetail.html?menu_id=%s&pk=%s&mode=sign&infotype=msg' % ( front_url, menu_id, pk) elif func == 'info_send': url = '%s/wx/mui/infoExchange_index.html?id=2' % (front_url) elif func == 'info_list': id = request.GET.get('id', '') type = request.GET.get('type', '') url = '%s/wx/mui/infoExchange_index.html?id=%s&type=%s' % (front_url, id, type) elif func == 'info_audit': pk = request.GET.get('pk', '') must_reply = request.GET.get('must_reply', '') url = '%s/wx/mui/infoExamine.html?seq=%s&must_reply=%s&infotype=msg' % ( front_url, pk, must_reply) elif func == 'info_detail': pk = request.GET.get('pk', '') must_reply = request.GET.get('must_reply', '') url = '%s/wx/mui/infoDetail.html?seq=%s&must_reply=%s&index=0&infotype=msg' % ( front_url, pk, must_reply) elif func == 'info_upd': pk = request.GET.get('pk', '') url = '%s/wx/mui/changeInfo.html?seq=%s&infotype=msg' % (front_url, pk) elif func == 'fa_code': FAcode = request.GET.get('facode', '') url = '%s/wx/mui/fixedAssetsInfo.html?code=%s' % (front_url, FAcode) elif func == 'myloglist': url = '%s/wx/mui/myLogList.html?source=%s' % (front_url, source) elif func == 'otherloglist': url = '%s/wx/mui/otherLogList.html?source=%s' % (front_url, source) elif func == 'writelog': url = '%s/wx/mui/writeLog.html?source=%s' % (front_url, source) elif func == 'logdetail': id = request.GET.get('id', '') url = '%s/wx/mui/logDetail.html?id=%s' % (front_url, id) else: return HttpResponseRedirect(error_url) if request.session.has_key('login_data_wx'): d = request.session.get('login_data_wx', '') #userid = request.COOKIES.get('usr_wx_id','') userid = d[0] print "userid=%s" % (userid) sql = """select token,id from users_login where source='%s' and usr_id ='%s' and time_to_sec(now()) - time_to_sec(refresh_time) < expire_time order by refresh_time desc limit 1 """ % (source, userid) print sql lT, iN = db.select(sql) if iN > 0: token = lT[0][0] id = lT[0][1] sql = "update users_login set refresh_time=now() where id=%s" % ( id) db.executesql(sql) url += "&AccessToken=%s" % token print url dt = datetime.datetime.now() + datetime.timedelta(hours=2) response = HttpResponseRedirect(url) response.set_cookie("usr_wx_id", userid, expires=dt) #sql = "select usr_id,usr_name,dept_id,login_id,d.cname from users u left join dept d on d.id=u.dept_id where usr_id='%s' and status=1"%userid #lT,iN = db.select(sql) #if iN>0: # value=[userid,lT[0][1],lT[0][2],lT[0][4],lT[0][3]] # print value # request.session['login_data_wx'] = value return response if code != '': ddata = getuserinfo(code, agentname) try: print ddata uName = ddata['UserId'] DeviceId = ddata['DeviceId'] except Exception, e: uName = '' DeviceId = '' return HttpResponseRedirect(error_url) sql = "select usr_id,usr_name,dept_id,login_id,d.cname from users u left join dept d on d.id=u.dept_id where ifnull(wxqy_id,login_id)='%s' and status=1" % uName print sql lT, iN = db.select(sql) if iN > 0: userid = lT[0][0] usr_name = lT[0][1] dept_id = lT[0][2] dept_name = lT[0][4] login_id = lT[0][3] sTimeStamp = str(time.time()) wxcpt = WXBizMsgCrypt('szoworld', m_aesKey) ret, token = wxcpt.EncryptMsg(login_id, random_no, sTimeStamp) sql = """insert into users_login (usr_id,source,token,login_ip,login_time,refresh_time,expire_time) values (%s,'%s','%s','%s',now(),now(),%s) """ % (userid, source, token, ip, int(TIME_OUT) * 60) print ToGBK(sql) db.executesql(sql) token = urllib.quote(token) url += "&AccessToken=%s" % token dt = datetime.datetime.now() + datetime.timedelta(hours=2) response = HttpResponseRedirect(url) response.set_cookie("usr_wx_id", userid, expires=dt) value = [userid, usr_name, dept_id, dept_name, login_id] print value request.session['login_data_wx'] = value return response else: return HttpResponseRedirect(error_url)
def login_labor_func(request): import base64 , time import random random_no='%s'%(random.randint(0,999999)) usr_id,usr_name,dept_id,dept_name='','','','' source = 'labor' if request.META.has_key('HTTP_X_FORWARDED_FOR'): ip = request.META['HTTP_X_FORWARDED_FOR'] else: ip = request.META['REMOTE_ADDR'] code = request.GET.get('code','') login_id = getLoginID(code) if login_id=='': errCode = 1 msg = u'用户名不存在' s = """ { "errcode": %s, "errmsg": "%s", "login_id": "%s", } """ %(errCode,msg,login_id) return s login_id=login_id.replace("'","") s1 ='' sql="""SELECT U.usr_id,U.usr_name,U.dept_id,D.cname,IFNULL(U.pic,''),U.password,U.login_id FROM users U LEFT JOIN dept D ON U.dept_id=D.id WHERE ifnull(U.wxqy_id,U.login_id)='%s' AND U.status=1 """ % (login_id) lT,iN = db.select(sql) if iN>0: usr_id=lT[0][0] login_id = lT[0][6] #求得用户的权限 dActiveUser[usr_id]={} dActiveUser[usr_id]['roles']={} #用户角色 dActiveUser[usr_id]['access_dept_data']=[] #访问部门内所有人员数据的权限,格式:['部门ID1','部门ID2',...] dActiveUser[usr_id]['access_person_data']=[] #访问人员数据的权限,格式:['人员ID1','人员ID2',...] dActiveUser[usr_id]['login_time']=time.time() #登入时间 dActiveUser[usr_id]['usr_name']=lT[0][1] #用户名 dActiveUser[usr_id]['login_id']=login_id dActiveUser[usr_id]['usr_dept']=lT[0][2],lT[0][3] #用户部门 dActiveUser[usr_id]['pic']=lT[0][4] #用户角色/访问部门内所有人员数据的权限 sql="""SELECT WUR.role_id,WR.role_name,WR.sort,WR.dept_id FROM usr_role WUR LEFT JOIN roles WR ON WUR.role_id=WR.role_id WHERE WUR.usr_id=%s """ % usr_id lT1,iN1 = db.select(sql) if iN1>0: for e in lT1: #用户角色 dActiveUser[usr_id]['roles'][e[0]]=e[1:] request.session['usr_id'] = usr_id request.session['usr_name'] = dActiveUser[usr_id]['usr_name'] request.session['dept_id'] = lT[0][2] request.session['dept_name'] = lT[0][3] request.session['dActiveUser'] = dActiveUser d_value = ['','','','',''] d_value[0] = usr_id d_value[1] = dActiveUser[usr_id]['usr_name'] d_value[2] = lT[0][2] d_value[3] = lT[0][3] d_value[4] = 0 g_data.set_value(d_value) errCode = 0 msg = 'OK' pic = lT[0][4] if pic=='': pic_url = "%s/user_pic/default.jpg"%fs_url else: pic_url = "%s/user_pic/small_"%fs_url+pic sTimeStamp = str(time.time()) wxcpt=WXBizMsgCrypt('szoworld',m_aesKey) ret,token = wxcpt.EncryptMsg(login_id,random_no,sTimeStamp) if usr_id in [1,2]: sql="""SELECT distinct WMF.menu,WMF.menu_id,WMF.menu_name, WMF.sort,WMF.parent_id,WMF.status,WMF.url,WMF.icon FROM menu_func WMF Left JOIN menu_func WMF1 on WMF.parent_id = WMF1.menu_id WHERE WMF.status=1 and WMF.menu_id>0 and WMF1.status=1 ORDER BY WMF.parent_id,WMF.menu,WMF.sort,WMF.menu_id """ else: sql="""SELECT distinct WMF.menu,WMF.menu_id,WMF.menu_name, WMF.sort,WMF.parent_id,WMF.status,WMF.url,WMF.icon FROM usr_role WUR JOIN (role_menu WRM JOIN menu_func WMF ON WRM.menu_id=WMF.menu_id) ON WUR.role_id=WRM.role_id WHERE WUR.usr_id='%s' AND WMF.status=1 and WMF.menu_id>0 and WRM.can_view=1 ORDER BY WMF.parent_id,WMF.menu,WMF.sort,WMF.menu_id """%usr_id #print sql rows,iN = db.select(sql) L1=[2] L2=[] #L = formatData(rows,L1,L2) names = 'level menu_id menu_name sort parent_id status url icon'.split() data = [dict(zip(names, d)) for d in rows] s3 = json.dumps(data,ensure_ascii=False) sql = """select id,gc_no,cname from out_proj where FIND_IN_SET(%s,labor_managers)"""%(usr_id) rows,iN = db.select(sql) names = 'proj_id proj_no proj_name'.split() data = [dict(zip(names, d)) for d in rows] s4 = json.dumps(data,ensure_ascii=False) s1 = """"userid":%s, "username":"******", "dept_id":%s, "dept_name":"%s", "pic_url":"%s", "proj_info":%s, "AccessToken":"%s" """%(lT[0][0],(lT[0][1]),lT[0][2],(lT[0][3]),pic_url,s4,token) sql = """insert into users_login (usr_id,source,token,login_ip,login_time,refresh_time,expire_time) values (%s,'%s','%s','%s',now(),now(),%s) """%(lT[0][0],source,token,ip,int(TIME_OUT)*60) #print ToGBK(sql) db.executesql(sql) else: errCode = 1 msg = u'用户名不存在' s = """ { "errcode": %s, "errmsg": "%s", "login_id": "%s", %s } """ %(errCode,msg,login_id,s1) print ToGBK(s) response = HttpResponseCORS(request,s) return response
def get_select_data(request): field_id = request.GET.get('field_id', '') or request.POST.get( 'field_id', '') btn_id = request.GET.get('btn_id', '') or request.POST.get('btn_id', '') if btn_id != '': sql = """SELECT sel_type,sel_cols,24 from menu_form_grid_button where id=%s """ % (btn_id) else: sql = """SELECT sel_type,sel_cols,field_type from menu_form_cols where id=%s """ % (field_id) print sql rows, iN = db.select(sql) if iN == 0: return HttpResponseCORS(request, '') sel_type = rows[0][0] sel_cols = rows[0][1] field_type = rows[0][2] if field_type == 24 and btn_id == '': sql = "select id from menu_form_grid_button where field_id=%s order by id asc" % ( field_id) rows, iN = db.select(sql) btn_id = rows[0][0] sql = """SELECT sel_table,ifnull(sel_sort,'') from menu_select_source where sel_type=%s""" % sel_type #print sql rows, iN = db.select(sql) if iN == 0: return HttpResponseCORS(request, '') from_table = rows[0][0] from_table = from_table.replace('\n', '') from_table = from_table.replace('\r', '') from_sort = rows[0][1] #获取筛选的参数 sql = """SELECT label,show_label,filter_name,filter_type ,sort,defalut_value,span ,field_type,field_txt,field_title,ifnull(para1,''),ifnull(para2,''),filter_sql FROM menu_select_filters where sel_type=%s order by sort""" % (sel_type) #print sql rows, iN = db.select(sql) SL = [] for e in rows: L1 = list(e) value = request.POST.get(e[2], '') if value != '': value = e[5] para1, para2 = '', '' if e[10] != '': para1 = request.POST.get(e[10], '') if e[11] != '': para2 = request.POST.get(e[11], '') L1[5] = get_filter_data(e[7], e[8], e[9], value, para1, para2) SL.append(L1) #print SL names = 'cname txt_show ename type sort data span'.split() data = [dict(zip(names, d)) for d in SL] filter = json.dumps(data, ensure_ascii=False) if btn_id != '': sql = """select mp.para_name,ap.muti_sql from menu_form_url_para mp left join menu_select_all_para ap on ap.para_name=mp.para_name and ap.sel_type=%s where mp.btn_id=%s and ap.muti_sql is not null """ % (sel_type, btn_id) elif field_type == 15: sql = """select mp.para_name,ap.filter_sql from menu_form_url_para mp left join menu_select_all_para ap on ap.para_name=mp.para_name and ap.sel_type=%s where mp.field_id=%s and ap.filter_sql is not null """ % (sel_type, field_id) else: sql = """select mp.para_name,ap.muti_sql from menu_form_url_para mp left join menu_select_all_para ap on ap.para_name=mp.para_name and ap.sel_type=%s where mp.field_id=%s and ap.muti_sql is not null """ % (sel_type, field_id) print sql FL, iN = db.select(sql) #获取排序字段参数 if btn_id != '': sql = """SELECT ms.label,ms.col_name,ms.field_order,ifnull(fc.col_name,''),ifnull(is_hide,0),ifnull(is_unique,0), ms.field_show,ifnull(can_search,0) from menu_select_all_cols ms left join menu_form_select_cols mc on mc.sel_col_id = ms.id left join menu_form_cols fc on mc.field_id1 = fc.id where mc.btn_id=%s order by ifnull(mc.sort,999) """ % (btn_id) else: sql = """SELECT ms.label,ms.col_name,ms.field_order,ifnull(fc.col_name,''),ifnull(is_hide,0),ifnull(is_unique,0), ms.field_show,ifnull(can_search,0) from menu_select_all_cols ms left join menu_form_select_cols mc on mc.sel_col_id = ms.id left join menu_form_cols fc on mc.field_id1 = fc.id where mc.field_id=%s order by ifnull(mc.sort,999) """ % (field_id) #print ToGBK(sql) NL, iN = db.select(sql) names = 'cname ename order field_name hide unique'.split() data = [dict(zip(names, d)) for d in NL] cols = json.dumps(data, ensure_ascii=False, cls=ComplexEncoder) aoData = request.POST.get('aoData', '') select_size = 10 startNo = 0 orderby = '' orderbydir = '' qqid = '' #print aoData if aoData != '': jsonData = json.loads(aoData) for e in jsonData: if e['name'] == 'sEcho': sEcho = e['value'] elif e['name'] == 'iDisplayLength': select_size = e['value'] elif e['name'] == 'iDisplayStart': startNo = e['value'] elif e['name'] == 'iSortCol_0': iCol = e['value'] orderby = NL[int(iCol)][2] elif e['name'] == 'sSortDir_0': orderbydir = e['value'] elif e['name'] == 'sSearch': qqid = e['value'] sEcho += 1 else: sEcho = 1 pageNo = (int(startNo) / int(select_size)) + 1 if pageNo == 0: pageNo = 1 sql = "select " for e in NL: sql += "%s," % (e[6]) sql = sql[:-1] sql += " %s " % from_table if qqid != '': sTemp = "CONCAT(''," for e in NL: if e[7] == 1: sTemp += "%s," % e[6] sTemp = sTemp[:-1] + ")" sql += " AND %s LIKE '%%%s%%'" % (sTemp, qqid) for e in SL: value = request.POST.get(e[2], '') if value != '': sTemp = e[12].replace("$s", str(value)) sql += " and (%s)" % (sTemp) for e in FL: value = request.POST.get(e[0], '') if value != '': sTemp = e[1].replace("$s", str(value)) sql += " and (%s)" % (sTemp) #ORDER BY if orderby != '': sql += ' ORDER BY %s %s' % (orderby, orderbydir) elif from_sort != '': sql += from_sort print ToGBK(sql) rows, iTotal_length, iTotal_Page, pageNo, select_size = db.select_for_grid( sql, pageNo, select_size) names = [] for n in range(0, len(NL)): names.append(NL[n][1]) data = [dict(zip(names, d)) for d in rows] s3 = json.dumps(data, ensure_ascii=False, cls=ComplexEncoder) s = """ { "errcode": 0, "errmsg": "获取数据成功", "filter":%s, "cols":%s, "dataList":%s, "totalLength":%s, "totalPage":%s, "pageNo":%s, "pageSize":%s } """ % (filter, cols, s3, iTotal_length, iTotal_Page, pageNo, select_size) print ToGBK(s) return HttpResponseCORS(request, s)