def processData (self,productid,data,tablename): #zcb_insu_process #今天的日期 格式 yyyy-mm-dd startTime = str(time.strftime("%Y-%m-%d", time.localtime())) endTime = str(datetime.date.today() + datetime.timedelta(days=1)) currentTime_str = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) db = dataBaseOperator() query_sql = "select * from "+tablename+" where productId = '"+productid+"' and createDate < '"+endTime+"' and createDate>='"+startTime+"'" #print "query_sql-->"+query_sql query_result = db.execute(query_sql); data["updateDate"] = currentTime_str if len(query_result)<1: #当天没有数据,执行插入 #zcb_insu_process data["createDate"] = currentTime_str sql = db.parseInsert(tablename, data) #print "processData-->insert sql==>"+sql else : id = query_result[0]["id"] #print id sql = db.parseUpdate(tablename, data, "id='"+str(id)+"'") #print "processData-->update sql==>"+sql db.execute(sql); db.dataBaseClose()
def updateDetails(self, tableName, type, data,id): #startDate = str(time.strftime("%Y-%m-%d", time.localtime())) #endDate = str(datetime.date.today() + datetime.timedelta(days=1)) tableName = self.DBtableNames[type] database_obj = dataBaseOperator() currTime = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) d = {} b = True b2 = True try : bishu = data.pop("bishu") print "bishu-->"+bishu d["bishu"] = bishu #data["transNumber"]=bishu except Exception,e: b = False print e
def synzcb_loan (self,startDate): today = PageUtils.getCurrentDate() startDate = PageUtils.delayed(today,-1) startDate2 = PageUtils.delayed(today,-2) DB = dataBaseOperator() sql = ''' insert into zcb_loan_state (productid) select productid from zcb_loan where productid in ( select productid from zcb_loan_process where createDate >= \''''+str(startDate2)+'''\' and createDate < \''''+str(startDate)+'''\' and productid not in (select productid from zcb_loan_process where createDate >= \''''+str(startDate)+'''\' and createDate < \''''+str(today)+'''\')) and state != '已下架' and state !='已售罄' and productid not in (select productid from zcb_loan_state ) ''' print sql print "exe row -->",DB.execute(sql) #DB.createLog({"type":"loan_synState","msg":"个人企业贷同步"+str(startDate)+"的数据成功"})# DB.dataBaseClose() return
def synzcb(self,startDate): DB = dataBaseOperator() t_startDate = time.strptime(startDate, "%Y-%m-%d") d_startDate = datetime.datetime(* t_startDate[:6]) d_endDate = d_startDate + datetime.timedelta(days=7) s = str(time.strftime("%Y%m%d", t_startDate)) e=datetime.datetime.strftime((d_endDate - datetime.timedelta(days=1)), '%Y%m%d') for tableName in self.tablenames: tn = tableName+"_"+s+"_"+e print tableName sql = ''' create table '''+tn+''' select productid,max(ifnull(bishu,0))as bishu,max(ifnull(wanchenglv ,0))as wanchenglv from ( select * from '''+tableName+"_process"+''' where state = '已售罄' and createDate >= \''''+str(d_startDate)+'''\' and createDate < \''''+str(d_endDate)+'''\' )a group by productid''' sql_index='''alter table '''+tn+''' add index index_'''+tn+''' (`productid`) ''' #sqw_engine = "ALTER "+tableName+" engineTest ENGINE = 'INNODB'" print sql
def getProductId (self,tablenameKey): tableName = self.DBtableNames[tablenameKey] print tableName database_obj = dataBaseOperator() #sql = "select id,productid from "+tableName +" WHERE state != '已下架' and state !='已售罄' order by updateDate asc limit 0 ,5000 " #sql = "select id,productid from "+tableName +" WHERE (state != '已下架' and state !='已售罄') or state is null order by updateDate desc limit 0 ,10000 " pagesize = 20 '''if self.g_index%2 == 0: pageno = (self.g_index - self.g_index/2)*pagesize else: pageno = (self.g_index - 1 - self.g_index/2)*pagesize print pageno ''' pageno = self.g_index*pagesize #createDate desc,updateDate asc, sql = "select id,productid from "+tableName +" WHERE (state != '已下架' and state !='已售罄') or state is null order by updateNum asc limit "+str(pageno)+" , "+str(pagesize) #sql = "select id,productid from "+tableName +" WHERE length(productName)=0 or productName is null limit 0 , 111000" print sql lists = database_obj.execute(sql) database_obj.dataBaseClose() return lists
def tjDay(self,startDate): #startDate = "2015-09-13" startDate = startDate[:10] #print "startDate-->",startDate[:10] days = 1 endDate = str(PageUtils.delayed(startDate[:10],days)) #相隔的天数 tiqianDay = -1 tiqianStartDate = str(PageUtils.delayed(startDate,tiqianDay)) tiqianendDate = str(PageUtils.delayed(endDate[:10],tiqianDay)) print startDate,":",endDate,"====",tiqianStartDate,":",tiqianendDate db = dataBaseOperator() f=file("report/loan_"+str(startDate)+".txt","w+") #main_tablename = "zcb_insu" #slave_tablename = "zcb_insu_process" main_tablename = "zcb_loan"#,"zcb_insu","zcb_others" head = "产品类型,投资期限,产品数量(昨天),产品数量(今天),笔数(今天),增加笔数,最小利率,最大利率,起投金额,金额增长" slave_tablename = main_tablename+"_process" sql = ''' SELECT projectType, period, count(*)as total, sum( bishu_today )as bishu_today , sum( addedTransNumber ) as bishu , min( annualRate ) as min_lilv, max( annualRate ) as max_lilv , min( amountAtLeast ) as min_qitou, max( amountAtLeast ) as max_qitou, sum(jezz) as jezz,'已下架' as type FROM ( SELECT productid, CASE WHEN period <=90 THEN '03' WHEN period >90 AND period <=180 THEN '03-06' WHEN period >180 AND period <=365 THEN '06-12' WHEN period >365 AND period <=730 THEN '12-24' WHEN period >730 THEN '24+' END AS period, projectType, addedTransNumber,amountAtLeast,annualRate,bishu_today,jezz FROM ( SELECT productid, case when LOCATE('天',period)>0 then cast(SUBSTRING_INDEX( period, '天', 1 ) as signed) when LOCATE('年',period)>0 then cast(SUBSTRING_INDEX( period, '年', 1 ) as signed)*365 else null end as period, projectType, addedTransNumber,amountAtLeast,annualRate,bishu_today,jezz FROM ( select l1.productid, l1.projectType, l1.period, l1.TransNumber addedTransNumber,l1.TransNumber bishu_today,l1.annualRate, cast( SUBSTRING_INDEX( l1.amountAtLeast, '元起', 1 ) AS signed ) AS amountAtLeast, l1.productScale_yuan as jezz from zcb_loan l1 where state = '已下架' and endSellDate >= \''''+str(startDate)+'''\' and endSellDate < \''''+str(endDate)+'''\' )a )b )c GROUP BY projectType, period ''' #and createDate >= \''''+str(startDate)+'''\' and createDate < \''''+str(endDate)+'''\' 已售罄 已下架 print sql maps = db.execute(sql) for map in maps : map["startDate"] = startDate map["endDate"] = endDate sql = db.parseInsert("zcb_loan_report", map) print sql db.execute(sql) f.writelines(head) for map in maps : result = "\n"+str(map["projectType"])+","+\ str(map["period"])+"个月,0,"+\ str(map["total"])\ +","+str(map["bishu_today"])\ +","+str(map["bishu"])\ +","+str(map["min_lilv"])\ +","+str(map["max_lilv"])\ +","+str(map["min_qitou"]) +"-"+str(map["max_qitou"])\ +","+str(map["jezz"]) ''' result.append(map["projectType"]) result.append(map["period"]) result.append(0) result.append(map["total"]) result.append(map["bishu_today"]) result.append(map["bishu"]) result.append(map["min_lilv"]) result.append(map["max_lilv"]) result.append(str(map["min_qitou"]) +"-"+str(map["max_qitou"]))''' f.write(result) f.write("\n\n\n") f.flush() f.close()
def tjWeek(self): reload(sys) sys.setdefaultencoding('utf8') # @UndefinedVariable preWeek = "_20150914_20150920" currWeek = "_20150921_20150927" head = "产品类型,投资期限,产品数量(昨天),产品数量(今天),笔数(今天),增加笔数,最小利率,最大利率,起投金额,增长金额" tablenames = ["zcb_insu","zcb_others","zcb_loan"]# f=file("report/"+currWeek+".txt","w+") for tablename in tablenames : main_tablename = tablename preTablename = main_tablename+preWeek currTablename = main_tablename+currWeek db = dataBaseOperator() sql = ''' SELECT projectType, period, count(*)as total, sum( bishu_today )as bishu_today , sum( addedTransNumber ) as bishu , min( annualRate ) as min_lilv, max( annualRate ) as max_lilv , min( amountAtLeast ) as min_qitou, max( amountAtLeast ) as max_qitou, sum(jezz)as jezz FROM ( SELECT productid, CASE WHEN period <=90 THEN '03' WHEN period >90 AND period <=180 THEN '03-06' WHEN period >180 AND period <=365 THEN '06-12' WHEN period >365 AND period <=730 THEN '12-24' WHEN period >730 THEN '24+' END AS period, projectType, addedTransNumber,amountAtLeast,annualRate,bishu_today,jezz FROM ( SELECT productid, case when LOCATE('天',period)>0 then cast(SUBSTRING_INDEX( period, '天', 1 ) as signed) when LOCATE('年',period)>0 then cast(SUBSTRING_INDEX( period, '年', 1 ) as signed)*365 else null end as period, projectType, addedTransNumber,amountAtLeast,annualRate,bishu_today,jezz FROM ( select p.productid, i.projectType, i.period,p.bishu as 'addedTransNumber',p.bishu_today,i.annualRate, cast( SUBSTRING_INDEX( amountAtLeast, '元起', 1 ) AS signed ) AS amountAtLeast,(i.productScale_yuan*wanchenglv/100)as jezz from ( select a1.productid , (IFNULL(a1.bishu,0)- IFNULL(a2.bishu,0))as bishu , IFNULL(a1.bishu,0)as bishu_today, (IFNULL(a1.wanchenglv,0)- IFNULL(a2.wanchenglv,0))as wanchenglv from `'''+currTablename+'''` a1 left join `'''+preTablename+'''` a2 on a1.productid = a2.productid ) p left join '''+main_tablename+''' i on p.productid = i.productid )c )a )b GROUP BY projectType, period ''' print sql maps = db.execute(sql) f.writelines(head) for map in maps : result = "\n"+str(map["projectType"])+","+\ str(map["period"])+",0,"+\ str(map["total"])\ +","+str(map["bishu_today"])\ +","+str(map["bishu"])\ +","+str(map["min_lilv"])\ +","+str(map["max_lilv"])\ +","+str(map["min_qitou"]) +"-"+str(map["max_qitou"])\ +","+str(map["jezz"]) f.write(result) f.write("\n\n\n") f.close()
def updateData (self,data): _main = data.get("m") _slaves = data.get("s") main = {} slave = {} print _main print _slaves db = dataBaseOperator() today = self.pageUtils.getCurrentDate() tomorrow = self.pageUtils.delayed(today,-1) #取昨天的数据 sql = "select * from zcb_report_master where date(createdate)='"+str(tomorrow)+"'" tomorrow_main = db.execute(sql) if len(tomorrow_main)>0: tomorrow_main = tomorrow_main[0] print "sql==>",sql for _d in _main: print "-->",_main.get(_d),"==",tomorrow_main.get(_d) if tomorrow_main.get(_d)==None or _main.get(_d)>tomorrow_main.get(_d): main[_d] = _main.get(_d) #今天是否有数据 sql = "select * from zcb_report_master where date(createdate)='"+str(today)+"'" today_main = db.execute(sql) if len(today_main)<1: #主表数据插入 sql = db.parseInsert("zcb_report_master", main) print "insert master sql -->",sql else: today_main = today_main[0] sql = db.parseUpdate("zcb_report_master", main, " id = "+str(today_main.get("id"))) print "update master sql -->",sql db.execute(sql) else : sql = "select * from zcb_report_master where date(createdate)='"+str(today)+"'" today_main = db.execute(sql) if len(today_main)<1: sql = db.parseInsert("zcb_report_master",_main) print "insert master sql -->",sql db.execute(sql) else: today_main = today_main[0] sql = db.parseUpdate("zcb_report_master",_main,"id="+str(today_main.get("id"))) print "update master sql -->",sql db.execute(sql) #处理从表数据 for _slave in _slaves: sql = "select * from zcb_report_slave where date(createdate)='"+str(tomorrow)+"' and type='"+_slave.get("type")+"' and tzqx = '"+_slave.get("tzqx")+"'" print sql tomorrow_slave = db.execute(sql) if len(tomorrow_slave)>0: tomorrow_slave = tomorrow_slave[0] slave = {} slave["type"] = _slave.get("type") slave["tzqx"] = _slave.get("tzqx") for _d in _slave: print "-->",_slave.get(_d),"==",tomorrow_slave.get(_d) if tomorrow_slave.get(_d)==None or _slave.get(_d)!=tomorrow_slave.get(_d): print _d,"==>",_slave.get(_d),"!!!!!",tomorrow_slave.get(_d) slave[_d] = _slave.get(_d) if len(slave) < 1 : continue print "slave-->",slave sql = "select * from zcb_report_slave where date(createdate)='"+str(today)+"' and type='"+_slave.get("type")+"' and tzqx = '"+_slave.get("tzqx")+"'" today_slave = db.execute(sql) if len(today_slave)<1: #insert sql = db.parseInsert("zcb_report_slave", slave) print "insert slave sql -->",sql else: #update today_slave = today_slave[0] sql = db.parseUpdate("zcb_report_slave", slave," id = "+str(today_slave.get("id"))) print "update slave sql -->",sql db.execute(sql) else: sql = "select * from zcb_report_slave where date(createdate)='"+str(today)+"' and type='"+_slave.get("type")+"' and tzqx = '"+_slave.get("tzqx")+"'" today_slave = db.execute(sql) if len(today_slave)<1: sql = db.parseInsert("zcb_report_slave", _slave) print "insert slave sql -->",sql db.execute(sql) else : today_slave = today_slave[0] sql = db.parseUpdate("zcb_report_slave", _slave,"id = "+str(today_slave.get("id"))) print "update slave sql -->",sql db.execute(sql)
def tjDay(self, startDate): # startDate = "2015-09-13" startDate = startDate[:10] # print "startDate-->",startDate[:10] days = 1 endDate = str(PageUtils.delayed(startDate[:10], days)) # 相隔的天数 tiqianDay = -1 tiqianStartDate = str(PageUtils.delayed(startDate, tiqianDay)) tiqianendDate = str(PageUtils.delayed(endDate[:10], tiqianDay)) print startDate, ":", endDate, "====", tiqianStartDate, ":", tiqianendDate reload(sys) sys.setdefaultencoding("utf8") # @UndefinedVariable db = dataBaseOperator() f = file("report/" + str(startDate) + ".txt", "w+") # main_tablename = "zcb_insu" # slave_tablename = "zcb_insu_process" tablename = ["zcb_loan", "zcb_insu", "zcb_others"] # "zcb_loan","zcb_insu","zcb_others" head = "产品类型,投资期限,产品数量(昨天),产品数量(今天),笔数(今天),增加笔数,最小利率,最大利率,起投金额,金额增长" for main_tablename in tablename: slave_tablename = main_tablename + "_process" sql = ( """ SELECT projectType, period, count(*)as total, sum( bishu_today )as bishu_today , sum( addedTransNumber ) as bishu , min( annualRate ) as min_lilv, max( annualRate ) as max_lilv , min( amountAtLeast ) as min_qitou, max( amountAtLeast ) as max_qitou, sum(jezz) as jezz FROM ( SELECT productid, CASE WHEN period <=90 THEN '03' WHEN period >90 AND period <=180 THEN '03-06' WHEN period >180 AND period <=365 THEN '06-12' WHEN period >365 AND period <=730 THEN '12-24' WHEN period >730 THEN '24+' END AS period, projectType, addedTransNumber,amountAtLeast,annualRate,bishu_today,jezz FROM ( SELECT productid, case when LOCATE('天',period)>0 then cast(SUBSTRING_INDEX( period, '天', 1 ) as signed) when LOCATE('年',period)>0 then cast(SUBSTRING_INDEX( period, '年', 1 ) as signed)*365 else null end as period, projectType, addedTransNumber,amountAtLeast,annualRate,bishu_today,jezz FROM ( select p.productid, i.projectType, i.period, p.bishu as 'addedTransNumber',p.bishu_today,i.annualRate, cast( SUBSTRING_INDEX( amountAtLeast, '元起', 1 ) AS signed ) AS amountAtLeast, (i.productScale_yuan*wanchenglv/100)as jezz from ( select a1.productid , (IFNULL(a1.bishu,0)- IFNULL(a2.bishu,0))as bishu ,IFNULL(a1.bishu,0)as bishu_today,(a1.wanchenglv-a2.wanchenglv)as wanchenglv from (select productid,bishu,IFNULL(b1.wanchenglv,0)as wanchenglv from `""" + slave_tablename + """` b1 where createDate >= \'""" + startDate + """\' and createDate < \'""" + endDate + """\')a1 left join (select productid,bishu,IFNULL(b2.wanchenglv,0)as wanchenglv from `""" + slave_tablename + """` b2 where createDate >= \'""" + tiqianStartDate + """\' and createDate < \'""" + tiqianendDate + """\')a2 on a1.productid = a2.productid ) p left join """ + main_tablename + """ i on p.productid = i.productid )c )a )b GROUP BY projectType, period """ ) print sql maps = db.execute(sql) f.writelines(head) for map in maps: result = ( "\n" + str(map["projectType"]) + "," + str(map["period"]) + "个月,0," + str(map["total"]) + "," + str(map["bishu_today"]) + "," + str(map["bishu"]) + "," + str(map["min_lilv"]) + "," + str(map["max_lilv"]) + "," + str(map["min_qitou"]) + "-" + str(map["max_qitou"]) + "," + str(map["jezz"]) ) """ result.append(map["projectType"]) result.append(map["period"]) result.append(0) result.append(map["total"]) result.append(map["bishu_today"]) result.append(map["bishu"]) result.append(map["min_lilv"]) result.append(map["max_lilv"]) result.append(str(map["min_qitou"]) +"-"+str(map["max_qitou"]))""" f.write(result) f.write("\n\n\n") f.flush() f.close()