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