#coding: utf8
from math import ceil
from datetime import timedelta,date
import time
import tablib
from decimal import Decimal
import cStringIO as StringIO
from flask import make_response
from DB import DB
db = DB()
def pages(total, limit):
# print int(ceil(total / float(limit)))
return int(ceil(total / float(limit)))
def checkcount(sql):
# sql = "select count(*) from gcic_t_avl_testdata"
countarr = db.query(sql)
for countlist in countarr:
for count in countlist:
pass
# print count
return count
def nextDay(ymd,num=1):
(year, month, day) = ymd.split('-')
x = timedelta(days = num)
y = date(int(year), int(month),int(day))
return y+x
# print y+x
#格式化时间格式
def date_format(ymd):
ymd = str(ymd)#对datetime.date类型数据进行转换
(year,month,day) = ymd.split('-')
assert len(month)==2
if month[0] == '0':
monthchg= month[1]
return '/'.join((year,monthchg,day))
else:
return '/'.join((year,month,day))
def pgData(orderby,sort_order,limit,select_page,isql,date_from,date_to):
if orderby == "":
orderby = "d.id"
# if selected_item =="":
# pass
# else:
# if selected_item in ["speed","pwr_kw"]:
# selected_item = "d."+selected_item
# restrict = "and %(selected_item)s like '%%%(query_str)s%%'"%{"selected_item":selected_item,"query_str":query_str}
# else:
# selected_item = "h."+selected_item
# restrict = "and %(selected_item)s like '%%%(query_str)s%%'"%{"selected_item":selected_item,"query_str":query_str}
restrict = isql
if date_from == "" or date_to =="":
# ISOTIMEFORMAT = '%Y-%m-%d'
# timestr = time.strftime(ISOTIMEFORMAT,time.localtime())
# date_from = nextDay(timestr,-90)
# date_to = nextDay(timestr)
# restrict = restrict + " and h.testdate>='%(date_from)s' and h.testdate<='%(date_to)s'"%{"date_from":date_from,"date_to":date_to}
pass
else:
date_to = nextDay(date_to)
restrict = restrict + " and h.testdate>='%(date_from)s' and h.testdate<='%(date_to)s'"%{"date_from":date_from,"date_to":date_to}
if isql == "":
sql = "select count(*) from gcic_t_avl_testdata"
total = checkcount(sql)
# print total
else:
sql = "select count(*) from gcic_t_avl_testheader h,gcic_t_avl_testdata d where h.esn=d.esn %(restrict)s"%{"restrict":restrict}
total = checkcount(sql)
# print total
totalpage = pages(total,limit)
offset = limit * (select_page - 1)
orderby = "order by %s %s"%(orderby,sort_order)
sql = "select h.testtype, d.speed, d.pwr_kw, h.testcell, h.pallet, h.esn, h.testdate from gcic_t_avl_testheader h,gcic_t_avl_testdata d where h.esn=d.esn %(restrict)s %(orderby)s limit %(limit)s offset %(offset)s "% {"restrict":restrict,"orderby":orderby,"limit":limit,"offset":offset}
# print sql
result = db.query(sql)
keys = result.keys()
print keys
print "*"*20
conarr = result.fetchall()
data = {}
for key in keys:
k = key.encode("utf-8")
data[k]=[]
for con in conarr:
print con
print "*"*20
for key in keys:
obj = con[key]
if isinstance(obj, date):
data[key].append(obj.isoformat())
# elif isinstance(obj, Decimal):
# data[key].append(int(obj))
else :
data[key].append(obj)
data['cols'] = keys
data['totalpage']=totalpage
print data
# return data
def view(esn):
sql = "select type, status, comments, testcell, pallet, esn, testdate, speed, pwr_kw, torque, bsfc, fuel_rate, oil_filter_p, blowby_l_p, in_manifold_l_p, coolant_in_t, cell_air_t, fuel_in_p, fuel_in_t, fuel_out_p, coolant_out_t, coolant_out_p, coolant_in_p, smoke, turbo_tur_out_l_t, turbo_tur_out_l_p, opacity from gcic_t_avl_data where esn='%s'"%esn
# print sql
result = db.query(sql)
keys = result.keys()
conarr = result.fetchall()
data = {}
for key in keys:
k = key.encode("utf-8")
data[k]=[]
for con in conarr:
for key in keys:
obj = con[key]
if isinstance(obj,date):
data[key]=obj.isoformat()
elif isinstance(obj,Decimal):
data[key]=int(obj)
else:
data[key]=obj
data['cols'] = keys
print data
return data
def export(date_from,date_to,test_run_name,line_flag):
date_to=nextDay(date_to)
sql = "select MeasurementName,MeasurementDescription,MeasurementNumber,MeasurementStartTime,MeasurementVersion,TestName,ReleaseNumber,RunNumber,TestcellParameterFile,EngineParameterFile,TestParameterFile,Frequency,LastAppendingTime,NumberofAppends,TestcellID,Project,EStopChannel,CELL_NAME,CELL_SITE,CELL_TYPE,GRAV_DRIFT,VISC_DRIFT,BARO_DRIFT,HUMI_DRIFT,EngineStartedRecognitionSpeed,EngineIdleMode,EngineIdleDynoSP,EngineIdleEngSP,EngineID,EngineSpeedRampLimit,EngineTorqueRampLimit,EngineThrottleRampLimit,EngineAuxRampLimit,EngineMaxSpeed,EngineMinSpeed,EngineMaxTorque,EngineMinTorque,EngineMaxAux,EngineMinAux,EngineIdleDynoRampTime,EngineIdleEngRampTime,APPLICATION_TYPE,CPL,CPL_REV,CUBIC_CAP,FUEL_PUMP_CODE,FUEL_PUMP_REV,FUEL_PUMP_TYPE,MODEL,TestID,XAxisChannelName,XAxisIncrementValue,XAxisIncrementValueUnit,XAxisStartValue,XAxisStartValueUnit,XAxisType,DateTime,SPEED,PWR_KW,TORQUE,BSFC,FUEL_RATE,OIL_FILTER_P,BLOWBY_L_P,CELL_AIR_T,IN_MANIFOLD_L_P,SMOKE,OPACITY,TURBO_TUR_OUT_L_P,FUEL_IN_P,FUEL_IN_T,FUEL_OUT_P,FUEL_OUT_T,COOLANT_IN_P,COOLANT_IN_T,COOLANT_OUT_P,COOLANT_OUT_T,TEST_RUN_NAME,TEST_COMPLETE_STATUS from gcic_t_csv_data where MeasurementStartTime > '%(date_from)s' and MeasurementStartTime <='%(date_to)s' and test_run_name='%(test_run_name)s' and line_flag='%(line_flag)s'"%{"date_from":date_from,"date_to":date_to,"test_run_name":test_run_name,"line_flag":line_flag}
result = db.query(sql)
datalist = []
conarr = result.fetchall()
for con in conarr:
data=[]
for i in con:
if isinstance(i,date):
data.append(i.isoformat())
elif isinstance(i,Decimal):
data.append(int(i))
else:
data.append(i)
#~ print data
datalist.append(data)
# print datalist
return datalist
def exportall(date_from,date_to):
#data patten in database:2013/6/25 20:14:14'
date_to=nextDay(date_to)
sql = "select MeasurementName,MeasurementDescription,MeasurementNumber,MeasurementStartTime,MeasurementVersion,TestName,ReleaseNumber,RunNumber,TestcellParameterFile,EngineParameterFile,TestParameterFile,Frequency,LastAppendingTime,NumberofAppends,TestcellID,Project,EStopChannel,CELL_NAME,CELL_SITE,CELL_TYPE,GRAV_DRIFT,VISC_DRIFT,BARO_DRIFT,HUMI_DRIFT,EngineStartedRecognitionSpeed,EngineIdleMode,EngineIdleDynoSP,EngineIdleEngSP,EngineID,EngineSpeedRampLimit,EngineTorqueRampLimit,EngineThrottleRampLimit,EngineAuxRampLimit,EngineMaxSpeed,EngineMinSpeed,EngineMaxTorque,EngineMinTorque,EngineMaxAux,EngineMinAux,EngineIdleDynoRampTime,EngineIdleEngRampTime,APPLICATION_TYPE,CPL,CPL_REV,CUBIC_CAP,FUEL_PUMP_CODE,FUEL_PUMP_REV,FUEL_PUMP_TYPE,MODEL,TestID,XAxisChannelName,XAxisIncrementValue,XAxisIncrementValueUnit,XAxisStartValue,XAxisStartValueUnit,XAxisType,DateTime,SPEED,PWR_KW,TORQUE,BSFC,FUEL_RATE,OIL_FILTER_P,BLOWBY_L_P,CELL_AIR_T,IN_MANIFOLD_L_P,SMOKE,OPACITY,TURBO_TUR_OUT_L_P,FUEL_IN_P,FUEL_IN_T,FUEL_OUT_P,FUEL_OUT_T,COOLANT_IN_P,COOLANT_IN_T,COOLANT_OUT_P,COOLANT_OUT_T,TEST_RUN_NAME,TEST_COMPLETE_STATUS from gcic_t_csv_data where MeasurementStartTime > '%(date_from)s' and MeasurementStartTime <='%(date_to)s' "%{"date_from":date_from,"date_to":date_to}
print sql
result = db.query(sql)
datalist = []
conarr = result.fetchall()
for con in conarr:
data=[]
for i in con:
if isinstance(i,date):
data.append(i.isoformat())
elif isinstance(i,Decimal):
data.append(int(i))
else:
data.append(i)
#~ print data
datalist.append(data)
print datalist
return datalist
headers = ('MeasurementName', 'MeasurementDescription', 'MeasurementNumber', 'MeasurementStartTime', 'MeasurementVersion', 'TestName', 'ReleaseNumber', 'RunNumber', 'TestcellParameterFile', 'EngineParameterFile', 'TestParameterFile', 'Frequency', 'LastAppendingTime', 'NumberofAppends', 'TestcellID', 'Project', 'EStopChannel', 'CELL_NAME', 'CELL_SITE', 'CELL_TYPE', 'GRAV_DRIFT', 'VISC_DRIFT', 'BARO_DRIFT', 'HUMI_DRIFT', 'EngineStartedRecognitionSpeed', 'EngineIdleMode', 'EngineIdleDynoSP', 'EngineIdleEngSP', 'EngineID', 'EngineSpeedRampLimit', 'EngineTorqueRampLimit', 'EngineThrottleRampLimit', 'EngineAuxRampLimit', 'EngineMaxSpeed', 'EngineMinSpeed', 'EngineMaxTorque', 'EngineMinTorque', 'EngineMaxAux', 'EngineMinAux', 'EngineIdleDynoRampTime', 'EngineIdleEngRampTime', 'APPLICATION_TYPE', 'CPL', 'CPL_REV', 'CUBIC_CAP', 'FUEL_PUMP_CODE', 'FUEL_PUMP_REV', 'FUEL_PUMP_TYPE', 'MODEL', 'TestID', 'XAxisChannelName', 'XAxisIncrementValue', 'XAxisIncrementValueUnit', 'XAxisStartValue', 'XAxisStartValueUnit', 'XAxisType', 'DateTime', 'SPEED', 'PWR_KW', 'TORQUE', 'BSFC', 'FUEL_RATE', 'OIL_FILTER_P', 'BLOWBY_L_P', 'CELL_AIR_T', 'IN_MANIFOLD_L_P', 'SMOKE', 'OPACITY', 'TURBO_TUR_OUT_L_P', 'FUEL_IN_P', 'FUEL_IN_T', 'FUEL_OUT_P', 'FUEL_OUT_T', 'COOLANT_IN_P', 'COOLANT_IN_T', 'COOLANT_OUT_P', 'COOLANT_OUT_T', 'TEST_RUN_NAME', 'TEST_COMPLETE_STATUS')
def export_csv_data(date_from,date_to):
ISOTIMEFORMAT='%Y%m%d%H%M%S' #设置显示的时间格式
timestr = time.strftime( ISOTIMEFORMAT, time.localtime())
filename ="TestMeasurement_{0}.xls" .format(timestr)
#时间区间内所有数据
title = filename.split('.')[0]
data = exportall(date_from,date_to)
dataset = tablib.Dataset(*data, headers=headers,title=title)
#9min类型
test_run_name = "Hot_Test"
#时间区间内9min第4行Hot_High Idle
data_Hot_Test_4 = export(date_from,date_to,test_run_name,4)
dataset_9min_4 = tablib.Dataset(*data_Hot_Test_4,headers=headers,title="Hot_High Idle")
#时间区间内9min第8行Hot_Rated
data_Hot_Test_8 = export(date_from,date_to,test_run_name,8)
dataset_9min_8 = tablib.Dataset(*data_Hot_Test_8,headers=headers,title="Hot_Rated")
#时间区间内9min第10行Hot_Torque Peak
data_Hot_Test_10 = export(date_from,date_to,test_run_name,10)
dataset_9min_10 = tablib.Dataset(*data_Hot_Test_10,headers=headers,title="Hot_Torque Peak")
#时间区间内9min第12行Hot_Low Idle
data_Hot_Test_12 = export(date_from,date_to,test_run_name,12)
dataset_9min_12 = tablib.Dataset(*data_Hot_Test_12,headers=headers,title="Hot_Low Idle")
#30min类型
test_run_name = "EQA"
#时间区间内30min第10行
data_EQA_10 = export(date_from,date_to,test_run_name,10)
dataset_30min_10 = tablib.Dataset(*data_EQA_10,headers=headers,title="EQA_Rated")
#时间区间内30min第12行
data_EQA_12 = export(date_from,date_to,test_run_name,12)
dataset_30min_12 = tablib.Dataset(*data_EQA_12,headers=headers,title="EQA_Torque Peak")
#时间区间内30min第16行
data_EQA_16 = export(date_from,date_to,test_run_name,16)
dataset_30min_16 = tablib.Dataset(*data_EQA_16,headers=headers,title="EQA_High Idle")
#时间区间内30min第18行
data_EQA_18 = export(date_from,date_to,test_run_name,18)
dataset_30min_18 = tablib.Dataset(*data_EQA_18,headers=headers,title="EQA_Low Idle")
# data2 = [('1', 'Rooney', 20),('2', 'John', 30)]
# dataset2 = tablib.Dataset(*data2, headers=('area', 'user', 'recharge'),title="hello")
# dataset_list = [dataset,dataset2]
dataset_list = [dataset,dataset_9min_8,dataset_9min_10,dataset_9min_12,dataset_9min_4,dataset_30min_10,dataset_30min_12,dataset_30min_16,dataset_30min_18]
databook = tablib.Databook(dataset_list)
output=StringIO.StringIO()
output.write(databook.xls)
response = make_response(output.getvalue())
response.headers['Content-Type'] = 'application/vnd.ms-excel'
response.headers['Content-Disposition'] = 'attachment; filename=' + filename
return response
if __name__=="__main__":
export_csv_data('2013-09-19','2013-12-30')
# pgData("","desc",10,1,"and h.esn like '%87%'",'2013-09-19','2013-10-10')
# pgData("","desc",10,1,"",'2013-09-19','2013-10-10')
# view(7)
# checkcount()
# pages(12,5)
扫一扫
评论
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
查看更多评论
添加红包
