278 lines
11 KiB
Python
278 lines
11 KiB
Python
import frappe
|
|
from frappe import _
|
|
|
|
|
|
def _aml_location_conditions(filters, aml_alias="aml", asset_alias="a"):
|
|
conditions = ["1=1"]
|
|
params = {}
|
|
|
|
if filters.get("company"):
|
|
conditions.append(f"{aml_alias}.custom_hospital_name = %(company)s")
|
|
params["company"] = filters.get("company")
|
|
|
|
if filters.get("site_name"):
|
|
conditions.append(f"{asset_alias}.custom_site = %(site_name)s")
|
|
params["site_name"] = filters.get("site_name")
|
|
|
|
return " AND ".join(conditions), params
|
|
|
|
|
|
def asset_wise_count_execute(filters=None):
|
|
filters = filters or {}
|
|
where, params = _aml_location_conditions(filters)
|
|
|
|
query = f"""
|
|
SELECT
|
|
aml.item_name AS item_name,
|
|
aml.maintenance_status AS maintenance_status,
|
|
SUM(CASE WHEN aml.due_date = aml.completion_date AND aml.maintenance_status = 'Completed' THEN 1 ELSE 0 END) AS completed_on_time,
|
|
SUM(CASE WHEN aml.completion_date < aml.due_date AND aml.maintenance_status = 'Completed' THEN 1 ELSE 0 END) AS completed_within_time,
|
|
SUM(CASE WHEN aml.completion_date > aml.due_date THEN 1 ELSE 0 END) AS delay_in_completion,
|
|
SUM(CASE WHEN aml.maintenance_status = 'Planned' AND aml.completion_date IS NULL AND aml.due_date > CURRENT_DATE() THEN 1 ELSE 0 END) AS pending,
|
|
SUM(CASE WHEN aml.maintenance_status = 'Planned' AND aml.completion_date IS NULL AND aml.due_date < CURRENT_DATE() THEN 1 ELSE 0 END) AS overdue,
|
|
SUM(CASE WHEN aml.maintenance_status = 'Cancelled' THEN 1 ELSE 0 END) AS cancelled
|
|
FROM `tabAsset Maintenance Log` aml
|
|
LEFT JOIN `tabAsset` a ON aml.asset_name = a.name
|
|
WHERE {where}
|
|
GROUP BY aml.item_name
|
|
"""
|
|
|
|
columns = [
|
|
{"label": _("Item Name"), "fieldname": "item_name", "fieldtype": "Data", "width": 200},
|
|
{"label": _("Maintenance Status"), "fieldname": "maintenance_status", "fieldtype": "Data", "width": 150},
|
|
{"label": _("Completed On Time"), "fieldname": "completed_on_time", "fieldtype": "Int", "width": 150},
|
|
{"label": _("Completed Within Time"), "fieldname": "completed_within_time", "fieldtype": "Int", "width": 170},
|
|
{"label": _("Delay In Completion"), "fieldname": "delay_in_completion", "fieldtype": "Int", "width": 150},
|
|
{"label": _("Pending"), "fieldname": "pending", "fieldtype": "Int", "width": 100},
|
|
{"label": _("Overdue"), "fieldname": "overdue", "fieldtype": "Int", "width": 100},
|
|
{"label": _("Cancelled"), "fieldname": "cancelled", "fieldtype": "Int", "width": 100},
|
|
]
|
|
|
|
data = frappe.db.sql(query, params, as_dict=True)
|
|
return columns, data
|
|
|
|
|
|
def assignees_status_count_execute(filters=None):
|
|
filters = filters or {}
|
|
where, params = _aml_location_conditions(filters)
|
|
|
|
query = f"""
|
|
SELECT
|
|
aml.item_name AS item_name,
|
|
aml.maintenance_status AS maintenance_status,
|
|
aml.assign_to_name AS assigned_to,
|
|
SUM(CASE WHEN aml.due_date = aml.completion_date AND aml.maintenance_status = 'Completed' THEN 1 ELSE 0 END) AS completed_on_time,
|
|
SUM(CASE WHEN aml.completion_date < aml.due_date AND aml.maintenance_status = 'Completed' THEN 1 ELSE 0 END) AS completed_within_time,
|
|
SUM(CASE WHEN aml.completion_date > aml.due_date THEN 1 ELSE 0 END) AS delay_in_completion,
|
|
SUM(CASE WHEN aml.maintenance_status = 'Planned' AND aml.completion_date IS NULL AND aml.due_date > CURRENT_DATE() THEN 1 ELSE 0 END) AS pending,
|
|
SUM(CASE WHEN aml.maintenance_status = 'Planned' AND aml.completion_date IS NULL AND aml.due_date < CURRENT_DATE() THEN 1 ELSE 0 END) AS overdue,
|
|
SUM(CASE WHEN aml.maintenance_status = 'Cancelled' THEN 1 ELSE 0 END) AS cancelled
|
|
FROM `tabAsset Maintenance Log` aml
|
|
LEFT JOIN `tabAsset` a ON aml.asset_name = a.name
|
|
WHERE {where}
|
|
GROUP BY aml.assign_to_name
|
|
"""
|
|
|
|
columns = [
|
|
{"label": _("Item Name"), "fieldname": "item_name", "fieldtype": "Data", "width": 200},
|
|
{"label": _("Maintenance Status"), "fieldname": "maintenance_status", "fieldtype": "Data", "width": 150},
|
|
{"label": _("Assigned To"), "fieldname": "assigned_to", "fieldtype": "Data", "width": 150},
|
|
{"label": _("Completed On Time"), "fieldname": "completed_on_time", "fieldtype": "Int", "width": 150},
|
|
{"label": _("Completed Within Time"), "fieldname": "completed_within_time", "fieldtype": "Int", "width": 170},
|
|
{"label": _("Delay In Completion"), "fieldname": "delay_in_completion", "fieldtype": "Int", "width": 150},
|
|
{"label": _("Pending"), "fieldname": "pending", "fieldtype": "Int", "width": 100},
|
|
{"label": _("Overdue"), "fieldname": "overdue", "fieldtype": "Int", "width": 100},
|
|
{"label": _("Cancelled"), "fieldname": "cancelled", "fieldtype": "Int", "width": 100},
|
|
]
|
|
|
|
data = frappe.db.sql(query, params, as_dict=True)
|
|
return columns, data
|
|
|
|
|
|
def asset_maintenance_frequency_execute(filters=None):
|
|
filters = filters or {}
|
|
where, params = _aml_location_conditions(filters)
|
|
|
|
query = f"""
|
|
SELECT
|
|
aml.custom_asset_names AS item,
|
|
SUM(CASE WHEN aml.maintenance_status = 'Planned' THEN 1 ELSE 0 END) AS planned,
|
|
SUM(CASE WHEN aml.maintenance_status = 'Completed' THEN 1 ELSE 0 END) AS completed,
|
|
SUM(CASE WHEN aml.maintenance_status = 'Cancelled' THEN 1 ELSE 0 END) AS cancelled,
|
|
SUM(CASE WHEN aml.maintenance_status = 'Overdue' THEN 1 ELSE 0 END) AS overdue,
|
|
COUNT(aml.custom_asset_names) AS total_count
|
|
FROM `tabAsset Maintenance Log` aml
|
|
LEFT JOIN `tabAsset` a ON aml.asset_name = a.name
|
|
WHERE {where}
|
|
GROUP BY aml.custom_asset_names
|
|
"""
|
|
|
|
columns = [
|
|
{"label": _("Item"), "fieldname": "item", "fieldtype": "Data", "width": 200},
|
|
{"label": _("Planned"), "fieldname": "planned", "fieldtype": "Int", "width": 100},
|
|
{"label": _("Completed"), "fieldname": "completed", "fieldtype": "Int", "width": 100},
|
|
{"label": _("Cancelled"), "fieldname": "cancelled", "fieldtype": "Int", "width": 100},
|
|
{"label": _("Overdue"), "fieldname": "overdue", "fieldtype": "Int", "width": 100},
|
|
{"label": _("Total Count"), "fieldname": "total_count", "fieldtype": "Int", "width": 100},
|
|
]
|
|
|
|
data = frappe.db.sql(query, params, as_dict=True)
|
|
return columns, data
|
|
|
|
|
|
def asset_up_and_down_execute(filters=None):
|
|
filters = filters or {}
|
|
conditions = ["1=1"]
|
|
params = {}
|
|
|
|
if filters.get("company"):
|
|
conditions.append("company = %(company)s")
|
|
params["company"] = filters.get("company")
|
|
|
|
if filters.get("site_name"):
|
|
conditions.append("custom_site = %(site_name)s")
|
|
params["site_name"] = filters.get("site_name")
|
|
|
|
if filters.get("department"):
|
|
conditions.append("department = %(department)s")
|
|
params["department"] = filters.get("department")
|
|
|
|
if filters.get("custom_class"):
|
|
conditions.append("custom_class = %(custom_class)s")
|
|
params["custom_class"] = filters.get("custom_class")
|
|
|
|
if filters.get("name"):
|
|
conditions.append("name = %(name)s")
|
|
params["name"] = filters.get("name")
|
|
|
|
where = " AND ".join(conditions)
|
|
|
|
result = frappe.db.sql(
|
|
f"""
|
|
SELECT
|
|
name AS name,
|
|
asset_name,
|
|
custom_device_status AS status,
|
|
department,
|
|
custom_class
|
|
FROM `tabAsset`
|
|
WHERE {where}
|
|
ORDER BY asset_name
|
|
""",
|
|
params,
|
|
as_dict=True,
|
|
)
|
|
|
|
status_count = {}
|
|
for row in result:
|
|
status = row.get("status")
|
|
if status:
|
|
normalized_status = status.strip().lower()
|
|
status_count[normalized_status] = status_count.get(normalized_status, 0) + 1
|
|
|
|
chart_labels = list(status_count.keys())
|
|
chart_values = list(status_count.values())
|
|
|
|
columns = [
|
|
{"fieldname": "name", "label": "Asset ID", "fieldtype": "Link", "options": "Asset", "width": 200},
|
|
{"fieldname": "asset_name", "label": "Asset Name", "fieldtype": "Data", "width": 200},
|
|
{"fieldname": "status", "label": "Status", "fieldtype": "Data", "width": 100},
|
|
{"fieldname": "department", "label": "Department", "fieldtype": "Link", "options": "Department", "width": 150},
|
|
{"fieldname": "custom_class", "label": "Class", "fieldtype": "Data", "width": 200},
|
|
]
|
|
|
|
status_colors = [
|
|
"#2ba63d" if status == "up" else "#FF0000" if status == "down" else "#0000FF"
|
|
for status in chart_labels
|
|
]
|
|
|
|
chart = {
|
|
"data": {
|
|
"labels": chart_labels,
|
|
"datasets": [{"name": "Number of Assets", "values": chart_values}],
|
|
},
|
|
"type": "pie",
|
|
"colors": status_colors,
|
|
}
|
|
|
|
return columns, result, None, chart
|
|
|
|
|
|
def work_order_status_execute(filters=None):
|
|
filters = filters or {}
|
|
conditions = ["repair_status IN ('Open', 'Work In Progress', 'Pending Review', 'Completed', 'Closed', 'Cancelled')"]
|
|
params = {}
|
|
|
|
filter_fields = ["work_order_type", "repair_status", "asset_type", "company", "site_name"]
|
|
for field in filter_fields:
|
|
if filters.get(field):
|
|
conditions.append(f"{field} = %({field})s")
|
|
params[field] = filters.get(field)
|
|
|
|
if filters.get("wo_name"):
|
|
conditions.append("name = %(wo_name)s")
|
|
params["wo_name"] = filters.get("wo_name")
|
|
|
|
where = " AND ".join(conditions)
|
|
|
|
query = f"""
|
|
SELECT
|
|
name AS wo_name,
|
|
asset_type,
|
|
work_order_type,
|
|
SUM(CASE WHEN repair_status = 'Open' THEN 1 ELSE 0 END) AS open_count,
|
|
SUM(CASE WHEN repair_status = 'Work In Progress' THEN 1 ELSE 0 END) AS work_in_progress_count,
|
|
SUM(CASE WHEN repair_status = 'Pending Review' THEN 1 ELSE 0 END) AS pending_review_count,
|
|
SUM(CASE WHEN repair_status = 'Completed' THEN 1 ELSE 0 END) AS completed_count,
|
|
SUM(CASE WHEN repair_status = 'Closed' THEN 1 ELSE 0 END) AS closed_count
|
|
FROM `tabWork_Order`
|
|
WHERE {where}
|
|
GROUP BY work_order_type
|
|
"""
|
|
|
|
result = frappe.db.sql(query, params, as_dict=True)
|
|
|
|
columns = [
|
|
{"label": _("Work Order Type"), "fieldname": "work_order_type", "fieldtype": "Data", "width": 200},
|
|
{"label": _("Open"), "fieldname": "open_count", "fieldtype": "Int", "width": 200},
|
|
{"label": _("Work In Progress"), "fieldname": "work_in_progress_count", "fieldtype": "Int", "width": 200},
|
|
{"label": _("Pending Review"), "fieldname": "pending_review_count", "fieldtype": "Int", "width": 200},
|
|
{"label": _("Completed"), "fieldname": "completed_count", "fieldtype": "Int", "width": 200},
|
|
{"label": _("Closed"), "fieldname": "closed_count", "fieldtype": "Int", "width": 200},
|
|
]
|
|
|
|
report_summary = []
|
|
for status in ["Open", "Work In Progress", "Pending Review", "Completed", "Closed"]:
|
|
status_conditions = list(conditions)
|
|
status_conditions.append("repair_status = %(status)s")
|
|
status_params = {**params, "status": status}
|
|
count = frappe.db.sql(
|
|
f"SELECT COUNT(*) FROM `tabWork_Order` WHERE {' AND '.join(status_conditions)}",
|
|
status_params,
|
|
)[0][0]
|
|
report_summary.append({"value": count, "label": status})
|
|
|
|
total = frappe.db.sql(
|
|
f"SELECT COUNT(*) FROM `tabWork_Order` WHERE {where}",
|
|
params,
|
|
)[0][0]
|
|
report_summary.append({"value": total, "label": "Total Work Orders"})
|
|
|
|
chart = {
|
|
"data": {
|
|
"labels": [row["work_order_type"] for row in result],
|
|
"datasets": [
|
|
{"name": "Open", "values": [row["open_count"] for row in result]},
|
|
{"name": "Work In Progress", "values": [row["work_in_progress_count"] for row in result]},
|
|
{"name": "Pending Review", "values": [row["pending_review_count"] for row in result]},
|
|
{"name": "Completed", "values": [row["completed_count"] for row in result]},
|
|
{"name": "Closed", "values": [row["closed_count"] for row in result]},
|
|
],
|
|
},
|
|
"type": "bar",
|
|
"barOptions": {"stacked": 1, "spaceRatio": 0.6},
|
|
"colors": ["#CCCCB7", "#52B2BF", "#9EC1A4", "#058D7C", "#A3A5CF"],
|
|
}
|
|
|
|
return columns, result, None, chart, report_summary
|