def search(self, args):
if not isinstance(args, dict):
return send_error(message="Please give the params to search")
name, acronym = None, None
if 'name' in args:
name = args['name']
if 'acronym' in args:
acronym = args['acronym']
query = db.session.query(Region)
is_filter = False
if name is not None and not str(name).strip().__eq__(''):
query = query.filter(Region.name == name)
is_filter = True
if acronym is not None and not str(acronym).strip().__eq__(''):
query = query.filter(Region.acronym == acronym)
is_filter = True
if is_filter:
try:
vessels = query.all()
data = marshal(vessels, DtoRegion.model)
return send_result(data=data)
except Exception as e:
print(e.__str__())
return send_result(message="Could not find any result.")
else:
return send_result(message="Could not find any result.")
def get_timeline(self, args):
if not isinstance(args, dict):
return send_error(message='Please enter params for searching')
vessel_id, vessel_name, voyage, hash_value = None, None, None, None
# get values of params
if 'vessel_id' in args:
vessel_id = args['vessel_id']
if 'vessel_name' in args:
vessel_name = args['vessel_name']
if 'voyage' in args:
voyage = args['voyage']
if 'hash_value' in args:
hash_value = args['hash_value']
if vessel_id is None and vessel_name is None and voyage is None and hash_value is None:
return send_error(message='Please enter params to search')
query = db.session.query(LoadPlanning)
query_volum = db.session.query(LoadPlanning)
is_filter = False
if vessel_id is not None and not str(vessel_id).strip().__eq__(''):
query = query.filter(LoadPlanning.vessel_id == vessel_id)
query_volum = query_volum.filter(LoadPlanning.vessel_name == 'VOLUMENPROGNOSE')
is_filter = True
if vessel_name is not None and not str(vessel_name).strip().__eq__(''):
query = query.filter(LoadPlanning.vessel_name == vessel_name)
query_volum = query_volum.filter(LoadPlanning.vessel_name == 'VOLUMENPROGNOSE')
is_filter = True
if voyage is not None and not str(voyage).strip().__eq__(''):
query = query.filter(LoadPlanning.voyage == voyage)
query_volum = query_volum.filter(LoadPlanning.voyage == voyage)
is_filter = True
if hash_value is not None and not str(hash_value).strip().__eq__(''):
query = query.filter(LoadPlanning.hash_value == hash_value)
query_volum = query_volum.filter(LoadPlanning.hash_value == hash_value)
is_filter = True
if is_filter:
try:
# lay ra nhung records ko phai la VOLUMENPROGNOSE
query_total = query.union(query_volum)
# lay ra record la VOLUMENPROGNOSE
load_plannings = query_total.order_by(LoadPlanning.date_created.asc()).all()
data = marshal(load_plannings, DtoLoadPlanning.model)
return send_result(data=data)
except Exception as e:
print(e.__str__())
return send_result(message="Could not find any result")
else:
send_result(message="Could not find any result")
def search_by_voyage(self, voyage):
if voyage is None or str(voyage).strip().__eq__(''):
return send_error(message="Voyage can not be null or empty")
load_planning_vessels = LoadPlanning.query.filter_by(voyage=voyage).all()
if load_planning_vessels is None:
return send_error(message="Not found any vessel according to voyage {}".format(voyage))
return send_result(data=marshal(load_planning_vessels, DtoLoadPlanning.model))
def get_by_name(self, region_name):
if region_name is None or str(region_name).strip().__eq__(''):
return send_error(message="Region name cannot be null or empty")
regions = Region.query.filter_by(name=region_name).all()
if regions is None:
return send_error(message="Not found anything")
return send_result(data=marshal(regions, DtoRegion.model))
def delete(self, object_id):
load_planning = LoadPlanning.query.filter_by(load_planning_id=object_id).first()
if load_planning is None:
return send_error("Could not delete, object not found.")
db.session.delete(load_planning)
db.session.commit()
return send_result(message="Load planning was deleted.")
def update(self, object_id, data):
load_planning = LoadPlanning.query.filter_by(load_planning_id=object_id).first()
if load_planning is None:
return send_error("Could not update, object not found.")
load_planning = self._parse_load_planning(data=data, load_planning=load_planning)
db.session.commit()
return send_result(data=marshal(load_planning, DtoLoadPlanning.model))
def create(self, data):
"""
"""
load_planning = self._parse_load_planning(data=data, load_planning=None)
db.session.add(load_planning)
db.session.commit()
return send_result(data=marshal(load_planning, DtoLoadPlanning.model))
def delete(self, object_id):
load_planning_discharge = LoadPlanningDischarge.query.filter_by(load_planning_discharge_id=object_id).first()
if load_planning_discharge is None:
return send_error(message="Object not found!")
db.session.delete(load_planning_discharge)
db.session.commit()
return send_result("Object was deleted!")
def get_by_id(self, object_id):
vehicle = Vehicle.query.filter_by(vehicle_id=object_id).first()
if vehicle is None:
return send_error(
message='Could not find any vehicle according to this ID {}'.
format(object_id))
return send_result(data=marshal(vehicle, DtoVehicle.model))
def get_by_id(self, object_id):
try:
region = Region.query.filter_by(region_id=object_id).first()
return send_result(data=marshal(region, DtoRegion.model))
except Exception as e:
print(e.__str__())
return send_error(message="Could not find any result.")
def get_by_acronym(self, acronym):
if acronym is None or str(acronym).strip().__eq__(''):
return send_error(message="Acronym cannot be null or empty")
regions = Region.query.filter_by(acronym=acronym).all()
if regions is None:
return send_error(message="Not found anything")
return send_result(data=marshal(regions, DtoRegion.model))
def get_port_by_name(self, name):
if name is None or str(name).strip().__eq__(''):
return send_error(message='Port name cannot be null or empty')
port = Port.query.filter_by(name=name).first()
if port is None:
return send_error(
message="Not found anything according to your request")
return send_result(data=marshal(port, DtoPort.model))
def update(self, object_id, data):
port = Port.query.filter_by(port_id=object_id).first()
if port is None:
return send_error(message='Port not found')
else:
port = self._parse_port(data=data, port=port)
db.session.commit()
return send_result(data=marshal(port, DtoPort.model))
def update(self, object_id, data):
region = Region.query.filter_by(region_id=object_id)
if region is None:
return send_error(message="Region not found")
else:
region = self._parse_region(data=data, region=region)
db.session.commit()
return send_result(data=marshal(region, DtoRegion.model))
def delete(self, object_id):
vehicle = Vehicle.query.filter_by(vehicle_id=object_id).first()
if vehicle is None:
return send_error(
message='Could not find any vehicle according to this ID {}'.
format(object_id))
db.session.delete(vehicle)
db.session.commit()
return send_result(message="Vehicle was deleted.")
def update(self, object_id, data):
load_planning_discharge = LoadPlanningDischarge.query.filter_by(load_planning_discharge_id=object_id).first()
if load_planning_discharge is None:
return send_error(message="Object not found!")
load_planning_discharge = self._parse_load_planning_dischare(data=data,
load_planning_discharge=load_planning_discharge)
db.session.commit()
return send_result(data=marshal(load_planning_discharge, DtoLoadPlanningDischarge.model))
def download_export(self, args):
if not isinstance(args, dict):
return send_error(message='Please enter params for searching')
self.search(args=args, returned=False)
file_type = None
if 'file_type' in args:
file_type = args['file_type']
if file_type is None:
file_type = 'xls'
if self.query_data is None:
return send_result(message="Could not process")
# return self.query_data
try:
column_names = ['port_load_name', 'port_discharge_name', 'vessel_name', 'region_name', 'ETS',
'date_planned',
'date_transit', 'voyage', 'load_type', 'ETA', 'volume', 'date_created']
df1 = pd.DataFrame(self._query_to_dict(self.query_data), columns=column_names)
df1['date_created'] = df1['date_created'].dt.date
# df1 = pd.DataFrame(self.query_data, columns=column_names)
output = BytesIO()
writer = pd.ExcelWriter(output, engine='xlsxwriter')
# taken from the original question
df1.to_excel(writer, startrow=0, merge_cells=False, sheet_name="Sheet_1")
workbook = writer.book
worksheet = writer.sheets["Sheet_1"]
format = workbook.add_format()
format.set_bg_color('#eeeeee')
worksheet.set_column(0, 0, 5)
worksheet.set_column(1, 3, 20)
worksheet.set_column(4, 4, 12)
worksheet.set_column(5, 10, 15)
worksheet.set_column(11, 11, 10)
formatdict = {'num_format': 'yyyy-MM-dd'}
fmt = workbook.add_format(formatdict)
worksheet.set_column(12, 12, 15, fmt)
# the writer has done its job
writer.close()
# go back to the beginning of the stream
output.seek(0)
# finally return the file
return send_file(output, attachment_filename="load_planning.xlsx", as_attachment=True)
# excel_file = excel.make_response_from_query_sets(query_sets=self.query_data, column_names=column_names,
# file_type=file_type, file_name='load_planning')
# return excel_file
# return self.query_data
except Exception as e:
print(e.__str__())
return None # send_result(message="Could not process your request")
def update(self, object_id, data):
vehicle = Vehicle.query.filter_by(vehicle_id=object_id).first()
if vehicle is None:
return send_error(
message='Could not find any vehicle according to this ID {}'.
format(object_id))
vehicle = self._parse_vehicle(data=data, vehicle=vehicle)
db.session.commit()
return send_result(data=marshal(vehicle, DtoVehicle.model))
def search_port_load(self, args):
if not isinstance(args, dict):
return send_error(message='Please enter params for searching')
port_load_id, port_load_name = None, None
if 'port_load_id' in args:
port_load_id = args['port_load_id']
if 'port_load_name' in args:
port_load_name = args['port_load_name']
if port_load_id is None and port_load_name is None:
return send_error(message="Please enter the params to search")
query = db.session.query(LoadPlanning)
# get port_discharge
is_filter = False
port_discharges = list()
regions = list()
status = list()
vessels = list()
voyages = list()
if port_load_id is not None and not str(port_load_id).strip().__eq__(''):
query = query.filter(LoadPlanning.port_load_id == port_load_id)
is_filter = True
if port_load_name is not None and not str(port_load_name).strip().__eq__(''):
query = query.filter(LoadPlanning.port_load_name == port_load_name)
is_filter = True
if is_filter:
try:
load_plannings = query.all()
if load_plannings is not None and len(load_plannings) > 0:
for load_planning in load_plannings:
port_discharge_name = load_planning.port_discharge_name
port_discharges.append(port_discharge_name)
region = load_planning.region_name
regions.append(region)
load_type = load_planning.load_type
status.append(load_type)
vessel = load_planning.vessel_name
vessels.append(vessel)
voyage = load_planning.voyage
voyages.append(voyage)
data = dict()
data['port_discharge'] = sorted(list(set(port_discharges)))
data['region'] = sorted(list(set(regions)))
data['vessel'] = sorted(list(set(vessels)))
data['voyage'] = sorted(list(set(voyages)))
data['status'] = list(set(status))
return send_result(data=data)
except Exception as e:
print(e.__str__())
return send_error(message="Could not find any result.")
else:
return send_error(message="Could not find any result.")
def delete(self, object_id):
try:
region = Region.query.filter_by(region_id=object_id).first()
if region is None:
return send_error(message='Region not found')
db.session.delete(region)
db.session.commit()
return send_result(message='Region ware deleted')
except Exception as e:
print(e.__str__())
return send_error(message='Could not delete region.')
def search(self, args):
if not isinstance(args, dict):
return send_error(message="Please enter the params")
name, port_type = None, None
if 'name' in args:
name = args['name']
if 'type' in args:
port_type = args['type']
query = db.session.query(Port)
is_filter = False
if name is not None and not str(name).strip().__eq__(''):
query = query.filter(Port.name == name)
is_filter = True
if port_type is not None and not str(port_type).strip().__eq__(''):
query = query.filter(Port.type == port_type)
is_filter = True
if is_filter:
ports = query.all()
data = marshal(ports, DtoPort.model)
return send_result(data=data)
else:
return send_result(message='Could not find any result')
def search_vessel_by_voyage(self, voyage):
if voyage is None or str(voyage).strip().__eq__(''):
return send_error(message="Voyage can not be null or empty")
load_planning_vessels = LoadPlanning.query.filter_by(voyage=voyage).all()
if load_planning_vessels is None:
return send_error(message="Not found any vessel according to voyage {}".format(voyage))
vessels = list()
for load_planning_vessel in load_planning_vessels:
vessel_id = load_planning_vessel.vessel_id
vessel = Vessel.query.filter_by(vessel_id).first()
if vessel is not None:
vessels.append(vessel)
return send_result(data=marshal(vessels, DtoVessel.model))
def delete(self, object_id):
try:
port = Port.query.filter_by(port_id=object_id).first()
if port is None:
return send_error(
message='Port not found'
) # False # send_error(message='User not found')
else:
db.session.delete(port)
db.session.commit()
return send_result(
message='Port was deleted'
) # True # send_result(message='Delete user successfully')
except Exception as e:
print(e.__str__())
return send_error(message='Could not delete port'
) # False # send_error(message=e)
def get(self):
vehicles = Vehicle.query.all()
return send_result(data=marshal(vehicles, DtoVehicle.model))
def create(self, data):
vehicle = self._parse_vehicle(data=data, vehicle=None)
db.session.add(vehicle)
db.session.commit()
return send_result(data=marshal(vehicle, DtoVehicle.model))
def get(self):
load_planning_discharges = LoadPlanningDischarge.query.all()
return send_result(data=marshal(load_planning_discharges, DtoLoadPlanningDischarge.model))
def create(self, data):
load_planning_discharge = self._parse_load_planning_dischare(data=data, load_planning_discharge=None)
db.session.add(load_planning_discharge)
db.session.commit()
return send_result(data=marshal(load_planning_discharge, DtoLoadPlanningDischarge.model))
def get_by_id(self, object_id):
load_planning_discharge = LoadPlanningDischarge.query.filter_by(load_planning_discharge_id=object_id).first()
if load_planning_discharge is None:
return send_error(message="Object not found!")
return send_result(data=marshal(load_planning_discharge, DtoLoadPlanningDischarge.model))
def search(self, args, returned=True):
if not isinstance(args, dict):
return send_error(message='Please enter params for searching')
load_planning_id, port_load_id, port_load_name, port_discharge_id, port_discharge_name, vessel_id, vessel_name, region_id, region_name, voyage, status, from_date, to_date = None, None, None, None, None, None, None, None, None, None, None, None, None
if 'load_planning_id' in args:
load_planning_id = args['load_planning_id']
if 'port_load_id' in args:
port_load_id = args['port_load_id']
if 'port_load_name' in args:
port_load_name = args['port_load_name']
if 'port_discharge_id' in args:
port_discharge_id = args['port_discharge_id']
if 'port_discharge_name' in args:
port_discharge_name = args['port_discharge_name']
if 'vessel_id' in args:
vessel_id = args['vessel_id']
if 'vessel_name' in args:
vessel_name = args['vessel_name']
# if 'region_id' in args:
# region_id = args['region_id']
# if 'region_name' in args:
# region_name = args['region_name']
if 'voyage' in args:
voyage = args['voyage']
# if 'status' in args:
# status = args['status']
if 'from_date' in args:
try:
from_date = datetime.datetime.fromisoformat(args['from_date'])
except Exception as e:
print(e.__str__())
from_date = None
if 'to_date' in args:
try:
to_date = datetime.datetime.fromisoformat(args['to_date'])
except Exception as e:
print(e.__str__())
to_date = None
if load_planning_id is None and port_load_id is None and port_load_name is None and port_discharge_id is None and port_discharge_name is None and vessel_id is None and vessel_name is None and region_id is None and region_name is None and voyage is None and status is None and from_date is None and to_date is None:
return send_error(message='Please enter params to search')
query = db.session.query(Vehicle)
is_filter = False
if load_planning_id is not None and not str(
load_planning_id).strip().__eq__(''):
query = query.filter(Vehicle.load_planning_id == load_planning_id)
is_filter = True
if port_load_id is not None and not str(port_load_id).strip().__eq__(
''):
query = query.filter(Vehicle.port_load_id == port_load_id)
is_filter = True
if port_load_name is not None and not str(
port_load_name).strip().__eq__(''):
query = query.filter(
Vehicle.port_load_original_name == port_load_name)
is_filter = True
if port_discharge_id is not None and not str(
port_discharge_id).strip().__eq__(''):
query = query.filter(
Vehicle.port_discharge_id == port_discharge_id)
is_filter = True
if port_discharge_name is not None and not str(
port_discharge_name).strip().__eq__(''):
query = query.filter(
Vehicle.port_discharge_name == port_discharge_name)
is_filter = True
if vessel_id is not None and not str(vessel_id).strip().__eq__(''):
query = query.filter(Vehicle.vessel_id == vessel_id)
is_filter = True
if vessel_name is not None and not str(vessel_name).strip().__eq__(''):
query = query.filter(Vehicle.vessel_name == vessel_name)
is_filter = True
# if region_id is not None and not str(region_id).strip().__eq__(''):
# query = query.filter(Vehicle.region_id == region_id)
# is_filter = True
# if region_name is not None and not str(region_name).strip().__eq__(''):
# query = query.filter(Vehicle.region_name == region_name)
# is_filter = True
if voyage is not None and not str(voyage).strip().__eq__(''):
query = query.filter(Vehicle.vessel_voyage == voyage)
is_filter = True
# if status is not None and not str(status).strip().__eq__(''):
# query = query.filter(Vehicle.load_type == status)
# is_filter = True
if from_date is not None:
query = query.filter(Vehicle.date_created >= from_date)
is_filter = True
if to_date is not None:
query = query.filter(
Vehicle.date_created <= (to_date + datetime.timedelta(days=1)))
is_filter = True
if is_filter:
try:
vehicles = query.all()
if returned:
data = marshal(vehicles, DtoVehicle.model)
return send_result(data=data)
else:
self.query_data = vehicles
except Exception as e:
print(e.__str__())
if returned:
return send_result(message="Could not find any result")
else:
self.query_data = None
else:
if returned:
send_result(message="Could not find any result")
else:
self.query_data = None
def get(self):
regions = Region.query.all()
return send_result(data=marshal(regions, DtoRegion.model))
