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 = DataBase()
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
print ""
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 synzcb(self,startDate):
DB = DataBase()
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
DB.execute(sql)
DB.execute(sql_index)
DB.dataBaseClose()
def synMainData(self, productid, tablename, data):
query_sql = "select * from " + tablename + " where productId = '" + productid + "'"
# print "synMainData query_sql-->"+query_sql
db = DataBase()
query_result = db.execute(query_sql)
# currentTime_str = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
currentTime_str = PageUtils.getCurrentTime()
if len(query_result) < 1: # insert data
data["createDate"] = currentTime_str
data["updateDate"] = currentTime_str
insert_sql = db.parseInsert(tablename, data)
db.execute(insert_sql)
db.dataBaseClose()
def synzcb_loan (self,startDate):
today = PageUtils.getCurrentDate()
startDate = PageUtils.delayed(today,-1)
startDate2 = PageUtils.delayed(today,-2)
DB = DataBase()
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 !='已售罄'
'''
print sql
print "exe row -->",DB.execute(sql)
#DB.createLog({"type":"loan_synState","msg":"个人企业贷同步"+str(startDate)+"的数据成功"})#
DB.dataBaseClose()
return
def getProductId (self):
database_obj = DataBase()
# 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 zcb_loan 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"
sql = "select id,productid from zcb_loan_state where isdel = 0 limit " + str(pageno) + " , " + str(pagesize)
print "getProductId sql -->",sql
lists = database_obj.execute(sql)
database_obj.dataBaseClose()
return lists
def getProductId (self,tablenameKey):
tableName = self.DBtableNames[tablenameKey]
print tableName
database_obj = DataBase()
#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 processData(self, productid, data, tablename):
# zcb_insu_process
# 今天的日期 格式 yyyy-mm-dd
startData = PageUtils.getCurrentDate()
endData = PageUtils.delayed(startData, 1)
currentTime_str = PageUtils.getCurrentTime() # time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
db = DataBase()
query_sql = (
"select * from "
+ tablename
+ " where productId = '"
+ productid
+ "' and createDate < '"
+ endData
+ "' and createDate>='"
+ startData
+ "'"
)
# print "processData 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()
