Scribd
Upload
4 views9 pages

Reefer Certificate Management System

The document outlines a Flask web application with routes for managing reefer container reports, including creating, submitting, updating, and deleting reports. It includes form handling for various fields, validation of required data, and database interactions to store and retrieve report information. Additionally, it provides functionality for uploading photos and fetching the latest report status.

Uploaded by

rohithloke2912
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
4 views9 pages

Reefer Certificate Management System

The document outlines a Flask web application with routes for managing reefer container reports, including creating, submitting, updating, and deleting reports. It includes form handling for various fields, validation of required data, and database interactions to store and retrieve report information. Additionally, it provides functionality for uploading photos and fetching the latest report status.

Uploaded by

rohithloke2912
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

@app.

route('/reefer', methods=['GET', 'POST'])


@login_required
def reefer():
print(">>> /reefer route triggered:", [Link])

conn = None
cursor = None

if [Link] == "POST":
print(">>> Full [Link]:", [Link].to_dict())

# --- Read form data --- #


report_number = [Link]('report_number', '').strip()
report_date = [Link]('report_date', '').strip()
applicant_name = [Link]('applicant_name', '').strip()
print(">>> Applicant Name received:", applicant_name)

type_of_survey = [Link]('type_of_survey', '').strip()


mode_of_shipment = [Link]('mode_of_shipment', '').strip()
survey_datetime_range = [Link]('survey_datetime_range',
'').strip()
place_of_survey = [Link]('place_of_survey', '').strip()
person_in_attendance = [Link]('person_in_attendance', '').strip()
invoice_number = [Link]('invoice_number', '').strip()
invoice_date = [Link]('invoice_date', '').strip()
cold_store_temp = [Link]('cold_store_temp', '').strip()
ambient_temperature = [Link]('ambient_temperature', '').strip()
stuffing_commenced = [Link]('stuffing_commenced', '').strip()
stuffing_completed = [Link]('stuffing_completed', '').strip()
seal_number = [Link]('seal_number', '').strip()
sealing_date = [Link]('sealing_date', '').strip()
sealing_time = [Link]('sealing_time', '').strip()
genset_power_on_datetime = [Link]('genset_power_on_datetime',
'').strip()
genset_temp = [Link]('genset_temp', '').strip()
description_of_cargo = [Link]('description_of_cargo', '').strip()
marks = [Link]('marks_batch_mfg_exp', '').strip()
stowage_within_container = [Link]('stowage_within_container',
'').strip()
distance_ceiling_to_cargo = [Link]('distance_ceiling_to_cargo',
'').strip()
distance_doors_to_cargo = [Link]('distance_doors_to_cargo',
'').strip()
total_quantity_stuffed = [Link]('total_quantity_stuffed',
'').strip()
packing_of_cargo = [Link]('packing_of_cargo', '').strip()
air_exchange_setting = [Link]('air_exchange_setting', '').strip()
thermostat_setting_temperature =
[Link]('thermostat_setting_temperature', '').strip()
gen_set_provided = [Link]('gen_set_provided', '').strip()
general_inspection_condition =
[Link]('general_inspection_condition', '').strip()
condition_of_cargo = [Link]('condition_of_cargo', '').strip()
remarks = [Link]('remarks', '').strip()
surveyor_name = [Link]('surveyor_name', '').strip()

# --- Action and Status --- #


action = [Link]('action', '')
status = "Draft" if action == "Save as Draft" else "Completed"
try:
conn = get_db_connection()
cursor = [Link]()

# --- Validate required fields if Completed --- #


if status == "Completed":
required_fields = {
"Report Number": report_number,
"Applicant for Survey": applicant_name,
"Type of Survey": type_of_survey,
"Place of Survey": place_of_survey,
}
for field, value in required_fields.items():
if not value:
flash(f"Error: {field} is required!", "error")
return render_template("[Link]", form=[Link])

# --- Check if report number exists --- #


[Link]("SELECT COUNT(*) AS count FROM reefer_container WHERE
report_number = %s", (report_number,))
result = [Link]()
if result[0] == 0:
flash(f"Error: Report Number {report_number} not found!", "error")
return render_template("[Link]", form=[Link])

# --- Handle photo uploads --- #


photos = []
for i in range(1, 8):
file = [Link](f'photo_{i}')
if file and allowed_file([Link]):
filename = secure_filename(f"{report_number}_photo_{i}_" +
[Link])
[Link]([Link](UPLOAD_FOLDER, filename))
[Link](filename)
else:
[Link](None)

# --- Update database --- #


@[Link]('/reeferreport')
@login_required
def reeferreport():
try:
conn = get_db_connection()
cursor = [Link]([Link])

# Fetch key fields from reefer_container


[Link]("""
SELECT
report_number,
report_date,
applicant_name,
consignor_details,
consignee_details,
total_quantity_stuffed,
status
FROM reefer_container
ORDER BY created_at DESC
""")
cer_data = [Link]()

[Link]()

return render_template('[Link]', cer_data=cer_data)


except Exception as e:
print(f"Error: {e}")
flash('An error occurred while fetching the reports.', 'error')
return redirect(url_for('reeferreport'))

# Delete multiple reefer reports (bulk delete)


@[Link]('/delete-reefer-certificates', methods=['POST'])
@login_required
def delete_reefer_certificates():
try:
data = request.get_json()
certificate_ids = [Link]('ids', [])

if not certificate_ids:
return jsonify({"error": "No report IDs provided"}), 400

conn = get_db_connection()
cursor = [Link]()

format_strings = ','.join(['%s'] * len(certificate_ids))


query = f"DELETE FROM reefer_container WHERE report_number IN
({format_strings})"
[Link](query, tuple(certificate_ids))
[Link]()

[Link]()
[Link]()

return jsonify({"message": "Certificates deleted successfully"}), 200


except Exception as e:
print(f"Error: {e}")
return jsonify({"error": "An error occurred while deleting certificates"}),
500

# Delete a single reefer report


@[Link]('/delete_reefer_container/<string:report_number>', methods=['POST'])
@login_required
def delete_reefer_container(report_number):
try:
conn = get_db_connection()
cursor = [Link]()

[Link]("DELETE FROM reefer_container WHERE report_number = %s",


(report_number,))
[Link]()

[Link]()
flash('Certificate deleted successfully.', 'success')
except Exception as e:
print(f"Error: {e}")
flash('An error occurred while deleting the certificate.', 'error')
return redirect(url_for('reeferreport'))

# Get latest reefer certificate


@[Link]('/get_latest_reefer_certificate')
@login_required
def get_latest_reefer_certificate():
try:
with get_db_connection() as conn:
with [Link]([Link]) as cursor:
[Link]("SELECT report_number, status FROM reefer_container
ORDER BY id DESC LIMIT 1")
certificate_data = [Link]()

if not certificate_data:
certificate_data = {"report_number": "N/A", "status": "Unknown"}

except Exception as e:
print(f"Error fetching latest certificate: {e}")
certificate_data = {"report_number": "Error", "status": "Error"}

return jsonify(certificate_data)

# Generate new reefer form ([Link])


@[Link]('/reefer')
@login_required
def reefer():
try:
with get_db_connection() as conn:
with [Link]([Link]) as cursor:
[Link]()

current_year_month = [Link]().strftime("%Y%m")

[Link]("SELECT report_number FROM reefer_container ORDER BY


id DESC LIMIT 1 FOR UPDATE")
last_record = [Link]()

if last_record and last_record["report_number"]:


last_report = last_record["report_number"]
last_year_month = last_report[:6]

if last_year_month == current_year_month:
last_number = int(last_report[6:])
next_number = last_number + 1
else:
next_number = 1
else:
next_number = 1

new_report_number = f"{current_year_month}
{str(next_number).zfill(6)}"

[Link](
"INSERT INTO reefer_container (report_number, status) VALUES
(%s, %s)",
(new_report_number, "draft")
)
[Link]()

report_data = {"report_number": new_report_number, "status":


"draft"}

return render_template('[Link]', report=report_data)

except Exception as e:
if 'conn' in locals(): [Link]()
print(f"Error generating report: {e}")
return "Error generating report", 500

@[Link]('/submit_reefer_container', methods=['POST'])
@login_required
def submit_reefer_container():
try:
# ========== Extract Fields ==========
report_number = [Link]('report_number', '').strip()
report_date = [Link]('report_date', '').strip()
applicant_name = [Link]('applicant_name', '').strip()
type_of_survey = [Link]('type_of_survey', '').strip()
mode_of_shipment = [Link]('mode_of_shipment', '').strip()
survey_date = [Link]('survey_date', '').strip()
survey_time = [Link]('survey_time', '').strip()
survey_start = [Link]('survey_start', None)
survey_end = [Link]('survey_end', None)
place_of_survey = [Link]('place_of_survey', '').strip()
person_in_attendance = [Link]('person_in_attendance', '').strip()

invoice_raw = [Link]('invoice_raw', '').strip()


invoice_number = [Link]('invoice_number', '').strip()
invoice_date = [Link]('invoice_date', None)

container_number = [Link]('container_number', '').strip()


container_type = [Link]('container_type', '').strip()
gross_weight = [Link]('gross_weight', None)
cold_store_temp = [Link]('cold_store_temp', '').strip()
ambient_temperature = [Link]('ambient_temperature', '').strip()
stuffing_commenced = [Link]('stuffing_commenced', '').strip()
stuffing_completed = [Link]('stuffing_completed', '').strip()
seal_number = [Link]('seal_number', '').strip()
sealing_datetime = [Link]('sealing_datetime', '').strip()
genset_power_on_datetime = [Link]('genset_power_on_datetime',
'').strip()
unit_temperature = [Link]('unit_temperature', '').strip()
description_of_cargo = [Link]('description_of_cargo', '').strip()
marks = [Link]('marks', '').strip()
batch_nos = [Link]('batch_nos', '').strip()
mfg_dates = [Link]('mfg_dates', '').strip()
exp_date = [Link]('exp_date', '').strip()
pallet_nos = [Link]('pallet_nos', '').strip()
stowage_within_container = [Link]('stowage_within_container',
'').strip()
distance_ceiling_to_cargo = [Link]('distance_ceiling_to_cargo',
'').strip()
distance_doors_to_cargo = [Link]('distance_doors_to_cargo',
'').strip()
total_quantity_stuffed = [Link]('total_quantity_stuffed',
'').strip()
packing_of_cargo = [Link]('packing_of_cargo', '').strip()

# JSON structured
consignor_details = [Link]('consignor_details', '{}').strip()
consignee_details = [Link]('consignee_details', '{}').strip()

port_of_loading = [Link]('port_of_loading', '').strip()


port_of_discharge = [Link]('port_of_discharge', '').strip()

air_exchange_setting = [Link]('air_exchange_setting', '').strip()


thermostat_setting_temperature =
[Link]('thermostat_setting_temperature', '').strip()
gen_set_provided = 1 if [Link]('gen_set_provided') == 'on' else 0
general_inspection_condition =
[Link]('general_inspection_condition', '').strip()
additional_container_conditions =
[Link]('additional_container_conditions', '[]').strip()

condition_of_cargo = [Link]('condition_of_cargo', '').strip()

issued_without_prejudice = 1 if
[Link]('issued_without_prejudice') == 'on' else 0
surveyor_name = [Link]('surveyor_name', '').strip()
surveyor_signature_path = [Link]('surveyor_signature_path',
'').strip()

photos = [Link]('photos', '[]').strip()


remarks = [Link]('remarks', '').strip()
extra_metadata = [Link]('extra_metadata', '{}').strip()

action = [Link]('action') # "Save as Draft" / "Submit" / "Submit


and New"

# ========== Determine Status ==========


if action == "Save as Draft":
status = "draft"
elif action == "Submit and New":
status = "submitted_new"
else:
status = "submitted"

# ========== Validation ==========


if status in ["submitted", "submitted_new"]:
required_fields = {
"Report Number": report_number,
"Report Date": report_date,
"Applicant Name": applicant_name,
"Surveyor Name": surveyor_name,
}
for field, value in required_fields.items():
if not value or value in ["{}", "[]"]:
flash(f"Error: {field} is required!", "error")
return render_template("[Link]", report=[Link])

# ========== Database Save ==========


with get_db_connection() as conn:
with [Link]() as cursor:
[Link]("SELECT id FROM reefer_container WHERE report_number
= %s", (report_number,))
exists = [Link]()

if exists:
# Update existing record
update_query = """
UPDATE reefer_container SET
report_date=%s, applicant_name=%s, type_of_survey=%s,
mode_of_shipment=%s,
survey_datetime_range=%s, survey_start=%s, survey_end=
%s, place_of_survey=%s, person_in_attendance=%s,
invoice_raw=%s, invoice_number=%s, invoice_date=%s,
container_number=%s, container_type=%s, gross_weight=
%s, cold_store_temp=%s, ambient_temperature=%s,
stuffing_commenced=%s, stuffing_completed=%s,
seal_number=%s, sealing_datetime=%s,
genset_power_on_datetime=%s, unit_temperature=%s,
description_of_cargo=%s, marks=%s, batch_nos=%s,
mfg_dates=%s, exp_date=%s, pallet_nos=%s,
stowage_within_container=%s, distance_ceiling_to_cargo=
%s, distance_doors_to_cargo=%s,
total_quantity_stuffed=%s, packing_of_cargo=%s,
consignor_details=%s, consignee_details=%s,
port_of_loading=%s, port_of_discharge=%s,
air_exchange_setting=%s,
thermostat_setting_temperature=%s, gen_set_provided=%s,
general_inspection_condition=%s,
additional_container_conditions=%s,
condition_of_cargo=%s, status=%s,
issued_without_prejudice=%s,
surveyor_name=%s, surveyor_signature_path=%s, photos=
%s,
remarks=%s, extra_metadata=%s
WHERE report_number=%s
"""
[Link](update_query, (
report_date, applicant_name, type_of_survey,
mode_of_shipment,
survey_date,survey_time, survey_start, survey_end,
place_of_survey, person_in_attendance,
invoice_raw, invoice_number, invoice_date,
container_number, container_type, gross_weight,
cold_store_temp, ambient_temperature,
stuffing_commenced, stuffing_completed, seal_number,
sealing_datetime,
genset_power_on_datetime, unit_temperature,
description_of_cargo, marks, batch_nos, mfg_dates,
exp_date, pallet_nos,
stowage_within_container, distance_ceiling_to_cargo,
distance_doors_to_cargo,
total_quantity_stuffed, packing_of_cargo,
consignor_details, consignee_details,
port_of_loading, port_of_discharge,
air_exchange_setting, thermostat_setting_temperature,
gen_set_provided,
general_inspection_condition,
additional_container_conditions,
condition_of_cargo, status, issued_without_prejudice,
surveyor_name, surveyor_signature_path, photos,
remarks, extra_metadata,
report_number
))
else:
# Insert new record
insert_query = """
INSERT INTO reefer_container (
report_number, report_date, applicant_name,
type_of_survey, mode_of_shipment,
survey_date,survey_time, survey_start, survey_end,
place_of_survey, person_in_attendance,
invoice_raw, invoice_number, invoice_date,
container_number, container_type, gross_weight,
cold_store_temp, ambient_temperature,
stuffing_commenced, stuffing_completed, seal_number,
sealing_datetime,
genset_power_on_datetime, unit_temperature,
description_of_cargo, marks, batch_nos, mfg_dates,
exp_date, pallet_nos,
stowage_within_container, distance_ceiling_to_cargo,
distance_doors_to_cargo,
total_quantity_stuffed, packing_of_cargo,
consignor_details, consignee_details,
port_of_loading, port_of_discharge,
air_exchange_setting, thermostat_setting_temperature,
gen_set_provided,
general_inspection_condition,
additional_container_conditions,
condition_of_cargo, status, issued_without_prejudice,
surveyor_name, surveyor_signature_path, photos,
remarks, extra_metadata
) VALUES (
%s,%s,%s,%s,%s,
%s,%s,%s,%s,%s,
%s,%s,%s,%s,
%s,%s,%s,%s,%s,
%s,%s,%s,%s,
%s,%s,
%s,%s,%s,%s,%s,%s,
%s,%s,%s,
%s,%s,
%s,%s,
%s,%s,%s,
%s,%s,
%s,%s,%s,%s,%s,%s
)
"""
[Link](insert_query, (
report_number, report_date, applicant_name, type_of_survey,
mode_of_shipment,
survey_date,survey_time, survey_start, survey_end,
place_of_survey, person_in_attendance,
invoice_raw, invoice_number, invoice_date,
container_number, container_type, gross_weight,
cold_store_temp, ambient_temperature,
stuffing_commenced, stuffing_completed, seal_number,
sealing_datetime,
genset_power_on_datetime, unit_temperature,
description_of_cargo, marks, batch_nos, mfg_dates,
exp_date, pallet_nos,
stowage_within_container, distance_ceiling_to_cargo,
distance_doors_to_cargo,
total_quantity_stuffed, packing_of_cargo,
consignor_details, consignee_details,
port_of_loading, port_of_discharge,
air_exchange_setting, thermostat_setting_temperature,
gen_set_provided,
general_inspection_condition,
additional_container_conditions,
condition_of_cargo, status, issued_without_prejudice,
surveyor_name, surveyor_signature_path, photos,
remarks, extra_metadata
))
[Link]()

# ========== Success Redirect ==========#


flash("Form saved as draft!" if status == "draft" else "Form submitted
successfully!", "success")

if status == "submitted_new":
return redirect(url_for("reefer"))
return redirect(url_for("reefer"))

except Exception as e:
print(f"Error: {e}")
flash(f"An error occurred: {e}", "error")
return render_template("[Link]", report=[Link])

You might also like