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()
