def get(self):
#TODO set requests
# from_container = 0
to_container = 3
from_container = self.request.get('from_container')
#query = Suggestion.query().order(-Suggestion.when)
if from_container:
from_container = self.request.get('from_container')
else:
from_container = 0
#TODO select key from request get
blob_key="-3cfYPZI8Rx1VLEkofj-DQ=="
blob_reader = blobstore.BlobReader(blob_key)
wb = xlrd.open_workbook(file_contents=blob_reader.read())
#sh = wb.sheet_by_index(0)
#TODO add cape town durban etc
sh = wb.sheet_by_name("FDEC")
con_range = range(C_START, C_START+(to_container*2),2)
dictd = lambda: defaultdict(dictd)
y = dictd()
for i in range(from_container,to_container):
y["voyage"] = sh.cell_value(9,1)
y["date_of_loading"] = (datetime(*(xlrd.xldate_as_tuple(sh.cell_value(11,1), 0))[0:6])).strftime('%d-%m-%Y')
y["product"] = sh.cell_value(10,1)
y["port"] = sh.cell_value(11,0)
y["vesselname"] = sh.cell_value(8,1)
y["container"][i] = sh.cell_value(con_range[i],0)
y["container"]["ppecbcode"][i] = sh.cell_value(con_range[i],1)
y["container"]["vent"][i] = sh.cell_value(con_range[i],2)
y["container"]["setpoint"][i] = sh.cell_value(con_range[i],3)
for j in range(0,4):
y["container"]["DAtemp"][i][j] = sh.cell_value(con_range[i],5+j)
y["container"]["RAtemp"][i][j] = sh.cell_value(con_range[i]+1,5+j)
y["container"]["date_"][i][j] = (datetime(*(xlrd.xldate_as_tuple(sh.cell_value(29, 5 + j), 0))[0:6])).strftime('%d-%m-%Y')
y["container"]["day"][i][j] = str(j+1)+" Day "
params = {
"y": y,
"from_container": from_container,
"to_container": to_container,
}
return self.render_template('shiplist.html', **params)
def addVesselData(wb, filename):
dictd = lambda: defaultdict(dictd)
data = dictd()
for i, x in enumerate(wb.sheets()):
header_cells = x.row(0)
sh = wb.sheet_by_index(i)
num_rows = x.nrows - 1
num_cells = sh.ncols - 1
curr_row = 0
mid_row = 0
header = [each.value for each in header_cells]
# delete_all_in_index(filename)
while curr_row < num_rows:
curr_row += 1
row = [int(each.value)
if isinstance(each.value, float)
else each.value
for each in sh.row(curr_row)]
value_dict = dict(zip(header, row))
value_dict['title'] = x.name
curr_cell = -1
while curr_cell < num_cells:
curr_cell += 1
# Cell Types: 0=Empty, 1=Text, 2=Number, 3=Date, 4=Boolean, 5=Error, 6=Blank
cell_type = sh.cell_type(curr_row, curr_cell)
cell_value = sh.cell_value(curr_row, curr_cell)
if (cell_type==1):
search.Index(name=filename, namespace="text").put(CreateDocument(cell_value.strip() ,datetime.now(),0 ,sh.name, int(curr_row), int(curr_cell)))
if (cell_type==3):
try:
a1_as_datetime = datetime(*xlrd.xldate_as_tuple(cell_value, 0)[0:6])
search.Index(name=filename, namespace="date").put(CreateDocument("", a1_as_datetime,0 ,sh.name,int(curr_row), int(curr_cell)))
except:
pass #if i cant read it i cant use it
if (cell_type==2):
search.Index(name=filename, namespace="number").put(CreateDocument("",datetime.now(),int(cell_value),sh.name, int(curr_row), int(curr_cell)))
return True
num_rows = worksheet.nrows - 1
num_cells = worksheet.ncols - 1
curr_row = -1
while curr_row < num_rows:
curr_row += 1
row = worksheet.row(curr_row)
# print 'Row:', curr_row
curr_cell = -1
while curr_cell < num_cells:
curr_cell += 1
# Cell Types: 0=Empty, 1=Text, 2=Number, 3=Date, 4=Boolean, 5=Error, 6=Blank
cell_type = worksheet.cell_type(curr_row, curr_cell)
if (cell_type > -1):
cell_value = worksheet.cell_value(curr_row, curr_cell)
# print ' ', cell_type, ':', cell_value
if (cell_type == 3):
try:
print(datetime(*(xlrd.xldate_as_tuple(cell_value, 0))[0:6])
).strftime('%d-%m-%Y')
except:
print worksheet.cell_value(curr_row, curr_cell)
# print (datetime(*(xlrd.xldate_as_tuple(cell_value, 0))[0:6])).strftime('%d-%m-%Y')
#if (cell_type == 2):
# print('%0.2f %s %s' % (cell_value, curr_row, curr_cell) )
if (cell_type == 1):
print('%s %s %s' % (cell_value, curr_row, curr_cell))
def get(self, filekey, sheet_name, row):
dictd = lambda: defaultdict(dictd)
y = dictd()
start_col = 1
wb = xlrd.open_workbook(file_contents=blobstore.BlobReader(filekey).read())
sh = wb.sheet_by_index(int(sheet_name))
y["filekey"] = filekey
y["sheet_name"] = sheet_name
y["row"] = row
# whats happening with this -1?
row = int(row)
cols = sh.ncols
y["filename"] = blobstore.BlobInfo.get(filekey).filename
y["product"] = sh.cell_value(10, 1)
# find col were readings start
for a in range(0, 10):
y["DAT"] = sh.cell_value(row, a)
try:
if "Dat/Sup" in y["DAT"]:
start_col = a + 1
except:
pass
# find col were readings end
for a in range(start_col, 40):
try:
y["DAT"] = sh.cell_value(row, a)
# if "Dat/Sup" in y["DAT"]:
# pass
except:
end_col = 21
# find start date
for c in range(start_col, 20):
if "Dat/Sup" in y["DAT"]:
start_col = a + 1
end_col = 22
y["start_col"] = start_col
y["end_col"] = end_col
y["container"] = sh.cell_value(row, 0)
for j in range(start_col, end_col):
y["count"][j] = j - start_col + 1
try:
y["DAtemp"][j] = sh.cell_value(row, j)
foo = sh.cell_value(row, j)
y["DAtempAM"][j], y["DAtempPM"][j] = foo.split("/")
except:
y["DAtemp"][j] = "NA"
try:
y["RAtemp"][j] = sh.cell_value(row + 1, j)
foo = sh.cell_value(row + 1, j)
y["RAtempAM"][j], y["RAtempPM"][j] = foo.split("/")
except:
y["RAtemp"][j] = "NA"
try:
y["date_"][j] = (datetime(*(xlrd.xldate_as_tuple(sh.cell_value(28, j), 0))[0:6])).strftime("%d-%m-%Y")
y["day_"][j] = (datetime(*(xlrd.xldate_as_tuple(sh.cell_value(28, j), 0))[0:6])).strftime("%d")
y["month_"][j] = (datetime(*(xlrd.xldate_as_tuple(sh.cell_value(28, j), 0))[0:6])).strftime("%m")
y["year_"][j] = (datetime(*(xlrd.xldate_as_tuple(sh.cell_value(28, j), 0))[0:6])).strftime("%Y")
except:
y["date_"][j] = "Date error"
# diffences
try:
foo = sh.cell_value(row, j)
amDAtemp, pmDAtemp = foo.split("/")
foo2 = sh.cell_value(row + 1, j)
amRAtemp, pmRAtemp = foo2.split("/")
# amt = amDAtemp - amRAtemp
AMdiff = Decimal(amDAtemp) - Decimal(amRAtemp)
PMdiff = Decimal(pmDAtemp) - Decimal(pmRAtemp)
y["AMDiff"][j] = AMdiff
y["PMDiff"][j] = PMdiff
y["AMDiff"]["class"][j] = "default"
y["PMDiff"]["class"][j] = "default"
if Decimal(AMdiff) <= Decimal(-1.0):
y["AMDiff"]["class"][j] = "lightred"
if Decimal(AMdiff) >= Decimal(-0.5):
y["AMDiff"]["class"][j] = "lightgreen"
if Decimal(AMdiff) >= Decimal(-0.2):
y["AMDiff"]["class"][j] = "darkgreen"
if Decimal(AMdiff) <= Decimal(-2.0):
y["AMDiff"]["class"][j] = "darkred"
# PMdiff
if Decimal(PMdiff) <= Decimal(-1.0):
y["PMDiff"]["class"][j] = "lightred"
if Decimal(PMdiff) >= Decimal(-0.5):
y["PMDiff"]["class"][j] = "lightgreen"
if Decimal(PMdiff) >= Decimal(-0.2):
y["PMDiff"]["class"][j] = "darkgreen"
if Decimal(PMdiff) <= Decimal(-2.0):
y["PMDiff"]["class"][j] = "darkred"
except:
y["AMDiff"][j] = "NA"
params = {"y": y}
return self.render_template("readingslist.html", **params)
def get(self, filekey, sheet_name, row):
dictd = lambda: defaultdict(dictd)
y = dictd()
start_col =1
wb = xlrd.open_workbook(file_contents=blobstore.BlobReader(filekey).read())
sh = wb.sheet_by_index(int(sheet_name))
y["filekey"] = filekey
y["sheet_name"] = sheet_name
y["row"] = row
#whats happening with this -1?
row = int(row)
cols = sh.ncols
y["filename"] = blobstore.BlobInfo.get(filekey).filename
y["product"] = sh.cell_value(10,1)
#find col were readings start
for a in range(0, 10):
y["DAT"] = sh.cell_value(row, a)
try:
if "Dat/Sup" in y["DAT"]:
start_col = a+1
except:
pass
#find col were readings end
for a in range(start_col, 40):
try:
y["DAT"] = sh.cell_value(row, a)
# if "Dat/Sup" in y["DAT"]:
# pass
except:
end_col = 21
#find start date
for c in range(start_col, 20):
if "Dat/Sup" in y["DAT"]:
start_col = a+1
end_col = 22
y["start_col"] = start_col
y["end_col"] = end_col
y["container"] = sh.cell_value(row,0)
for j in range(start_col, end_col):
y["count"][j] = j - start_col + 1
try:
y["DAtemp"][j] = sh.cell_value(row, j)
foo = sh.cell_value(row, j)
y["DAtempAM"][j], y["DAtempPM"][j] = foo.split("/")
except:
y["DAtemp"][j] = "NA"
try:
y["RAtemp"][j] = sh.cell_value(row+1, j)
foo = sh.cell_value(row+1, j)
y["RAtempAM"][j], y["RAtempPM"][j] = foo.split("/")
except:
y["RAtemp"][j] = "NA"
try:
y["date_"][j] = (datetime(*(xlrd.xldate_as_tuple(sh.cell_value(28, j), 0))[0:6])).strftime('%d-%m-%Y')
y["day_"][j] = (datetime(*(xlrd.xldate_as_tuple(sh.cell_value(28, j), 0))[0:6])).strftime('%d')
y["month_"][j] = (datetime(*(xlrd.xldate_as_tuple(sh.cell_value(28, j), 0))[0:6])).strftime('%m')
y["year_"][j] = (datetime(*(xlrd.xldate_as_tuple(sh.cell_value(28, j), 0))[0:6])).strftime('%Y')
except:
y["date_"][j] = "Date error"
#diffences
try:
foo = sh.cell_value(row, j)
amDAtemp, pmDAtemp = foo.split("/")
foo2 = sh.cell_value(row+1, j)
amRAtemp, pmRAtemp = foo2.split("/")
# amt = amDAtemp - amRAtemp
AMdiff = Decimal(amDAtemp) - Decimal(amRAtemp)
PMdiff = Decimal(pmDAtemp) - Decimal(pmRAtemp)
y["AMDiff"][j] = AMdiff
y["PMDiff"][j] = PMdiff
y["AMDiff"]["class"][j] = "default"
y["PMDiff"]["class"][j] = "default"
if (Decimal(AMdiff) <= Decimal(-1.0)):
y["AMDiff"]["class"][j] = "lightred"
if (Decimal(AMdiff) >= Decimal(-0.5)):
y["AMDiff"]["class"][j] = "lightgreen"
if (Decimal(AMdiff) >= Decimal(-0.2)):
y["AMDiff"]["class"][j] = "darkgreen"
if (Decimal(AMdiff) <= Decimal(-2.0)):
y["AMDiff"]["class"][j] = "darkred"
# PMdiff
if (Decimal(PMdiff) <= Decimal(-1.0)):
y["PMDiff"]["class"][j] = "lightred"
if (Decimal(PMdiff) >= Decimal(-0.5)):
y["PMDiff"]["class"][j] = "lightgreen"
if (Decimal(PMdiff) >= Decimal(-0.2)):
y["PMDiff"]["class"][j] = "darkgreen"
if (Decimal(PMdiff) <= Decimal(-2.0)):
y["PMDiff"]["class"][j] = "darkred"
except:
y["AMDiff"][j] = "NA"
params = {
"y": y,
}
return self.render_template('readingslist.html', **params)
def makePickle(filekey):
dictd = lambda: defaultdict(dictd)
y = dictd()
wb = xlrd.open_workbook(file_contents=blobstore.BlobReader(filekey).read())
for i, x in enumerate(wb.sheets()):
header_cells = x.row(0)
sh = wb.sheet_by_index(i)
num_rows = x.nrows - 1
curr_row = 0
mid_row = 0
header = [each.value for each in header_cells]
if 'MOL REEFER MANIFEST' in header:
y["manifest"] = 'MOL REEFER MANIFEST'
while curr_row < num_rows:
curr_row += 1
row = [int(each.value)
if isinstance(each.value, float)
else each.value
for each in sh.row(curr_row)]
value_dict = dict(zip(header, row))
value_dict['title'] = x.name
if 'Vessel:' in row:
y["voyage"] = row[row.index('Voyage:')+1]
y["vessel"] = row[row.index('Vessel:')+1]
y["port"] = row[row.index('Port:')+1]
if 'BOOKING NO' in row:
y["labels"] = row
mid_row = curr_row+1
y["acv"][curr_row] = row
y["myfloat"][curr_row] = sh.cell_value(curr_row,5)
y["numrows"] = num_rows+1
else:
y["key"] = blobstore.BlobInfo.get(filekey).key()
y["sheetname"][i] = sh.name
y["index"][i] = i
y["nsheets"] = wb.nsheets
for a in range(5, 15):
if "VESSEL" in sh.cell_value(a,0):
if (len(sh.cell_value(a,1)) > 0):
y["vesselname"][i] = sh.cell_value(a,1)
else:
y["vesselname"][i] = "NA"
if "VOYAGE" in sh.cell_value(a,0):
if (len(sh.cell_value(a,1)) > 0):
y["voyage"][i] = sh.cell_value(a,1)
y["voyage_fixed"] = sh.cell_value(a,1)
else:
y["voyage"][i] = "NA"
if "DATE of LOADING" in sh.cell_value(a,0):
try:
if (validate((datetime(*(xlrd.xldate_as_tuple(sh.cell_value(a, 1), 0))[0:6])).strftime('%d-%m-%Y'))):
y["date_of_loading"][i] = (datetime(*(xlrd.xldate_as_tuple(sh.cell_value(a, 1), 0))[0:6])).strftime('%d-%m-%Y')
except:
y["date_of_loading"][i] = "None"
y["colour"] = "lightred"
#y["bool"] = addDataFilename(y["vesselname"][i], y["voyage_fixed"], "", i, y["date_of_loading"][i])
return y
def makeVesselPickle(wb):
dictd = lambda: defaultdict(dictd)
y = dictd()
for i, x in enumerate(wb.sheets()):
header_cells = x.row(0)
sh = wb.sheet_by_index(i)
num_rows = x.nrows - 1
curr_row = 0
header = [each.value for each in header_cells]
if 'MOL REEFER MANIFEST' in header:
y["header"] = "manifest"
else:
# y["key"] = blobstore.BlobInfo.get(filekey).key()
y["sheetname"][i] = sh.name
y["index"][i] = i
y["nsheets"] = wb.nsheets
for a in range(5, 15):
if "VESSEL" in sh.cell_value(a,0):
if (len(sh.cell_value(a,1)) > 0):
y["vesselname"][i] = sh.cell_value(a,1)
vessel_name = sh.cell_value(a,1)
a_vessel = models.Vessel()
a_vessel.vessel=sh.cell_value(a,1).strip()
a_vessel.port=sh.name
# a_vessel.put()
else:
y["vesselname"][i] = "NA"
if "VOYAGE" in sh.cell_value(a,0):
if (len(sh.cell_value(a,1)) > 0):
y["voyage"][i] = sh.cell_value(a,1)
a_vessel.voyage=str(sh.cell_value(a,1)).strip()
# a_vessel.put()
# a_vessel.manifest = models.Vessel().find_manifest(a_vessel.voyage, a_vessel.port, vessel_name).key
a_vessel.manifest = models.Vessel().find_manifest(a_vessel.voyage, a_vessel.port).key
# a_vessel.put()
y["voyage_fixed"] = sh.cell_value(a,1)
else:
y["voyage"][i] = "NA"
if "DATE of LOADING" in sh.cell_value(a,0):
try:
if (validate((datetime(*(xlrd.xldate_as_tuple(sh.cell_value(a, 1), 0))[0:6])).strftime('%d-%m-%Y'))):
y["date_of_loading"][i] = (datetime(*(xlrd.xldate_as_tuple(sh.cell_value(a, 1), 0))[0:6])).strftime('%d-%m-%Y')
a_vessel.loaded=(datetime(*(xlrd.xldate_as_tuple(sh.cell_value(a, 1), 0))[0:6])).strftime('%d-%m-%Y')
a_vessel.put()
qry = models.Vessel.query(ancestor=a_vessel.key).get()
# qrey = models.Vessel.query(ancestor=qry.key).get()
zz = models.Vessel().update_manifest(qry)
except:
y["date_of_loading"][i] = "None"
#_vessel = Vessel().query().get()
# manifest = Manifest(models.Manifest.key == ).query().get()
# manifest.vessel = vessel.key
# manifest.put()
#models.Vessel().update_manifest("_vessel", "_vessel.manifest")
# _vessel.put()
y["colour"] = "lightred"
return y
num_cells = worksheet.ncols - 1
curr_row = -1
while curr_row < num_rows:
curr_row += 1
row = worksheet.row(curr_row)
# print 'Row:', curr_row
curr_cell = -1
while curr_cell < num_cells:
curr_cell += 1
# Cell Types: 0=Empty, 1=Text, 2=Number, 3=Date, 4=Boolean, 5=Error, 6=Blank
cell_type = worksheet.cell_type(curr_row, curr_cell)
if (cell_type > -1):
cell_value = worksheet.cell_value(curr_row, curr_cell)
# print ' ', cell_type, ':', cell_value
if (cell_type == 3):
try:
print (datetime(*(xlrd.xldate_as_tuple(cell_value, 0))[0:6])).strftime('%d-%m-%Y')
except:
print worksheet.cell_value(curr_row, curr_cell)
# print (datetime(*(xlrd.xldate_as_tuple(cell_value, 0))[0:6])).strftime('%d-%m-%Y')
#if (cell_type == 2):
# print('%0.2f %s %s' % (cell_value, curr_row, curr_cell) )
if (cell_type == 1):
print('%s %s %s' % (cell_value, curr_row, curr_cell) )
