# Copyright (c) 2013, <NAME> and contributors
# For license information, please see license.txt

from __future__ import unicode_literals
import frappe
from frappe import _

def execute(filters=None):
	if not filters: filters = {}

	columns = get_columns()

	start_up_summary = get_start_up_summary(filters)
	first_off_summary = get_first_off_summary(filters)
	hun_insp_summary = get_hun_insp_summary(filters)
	shift_handovers_summary = get_shift_handovers_summary(filters)
	quality_defect_summary = get_quality_defect_summary(filters)
	production_output_summary = get_production_output_summary(filters)

	data = []
	data.extend(start_up_summary or [])
	data.extend(first_off_summary or [])
	data.extend(hun_insp_summary or [])
	data.extend(shift_handovers_summary or [])
	data.extend(quality_defect_summary or [])
	data.extend(production_output_summary or [])

	return columns, data

def get_columns():
	"""return columns"""
	columns = [{
	    "fieldname": "department",
	    "label": _("Department"),
	    "fieldtype": "Link",
	    "options": "Department",
	    "width": 150
	},
	{
	    "fieldname": "workstation",
	    "label": _("Workstation"),
	    "fieldtype": "Link",
	    "options": "Workstation",
	    "width": 150
	},
	{
	    "fieldname": "workstation_entity",
	    "label": _("Workstation Entity"),
	    "fieldtype": "Link",
	    "options": "Workstation Entity",
	    "width": 150
	},
	{
	    "fieldname": "document",
	    "label": _("Document Type"),
	    "fieldtype": "Link",
	    "options": "DocType",
	    "width": 100
	},
	{
	    "fieldname": "su_name",
	    "label": _("Document Name"),
	    "fieldtype": "Dynamic Link",
	    "options": "document",
	    "width": 100
	},
	{
	    "fieldname": "status",
	    "label": _("Status"),
	    "fieldtype": "Data",
	    "width": 150
	},
	{
	    "fieldname": "creation",
	    "label": _("Date"),
	    "fieldtype": "Date",
	    "width": 100
	},
	{
	    "fieldname": "time",
	    "label": _("Time"),
	    "fieldtype": "Time",
	    "width": 50
	},
	{
	    "fieldname": "operator",
	    "label": _("Operator"),
	    "fieldtype": "Link",
	    "options": "Employee",
	    "width": 100
	},
	{
	    "fieldname": "job_number",
	    "label": _("Job"),
	    "fieldtype": "Link",
	    "options": "Syteline Job Order",
	    "width": 100
	},
	{
	    "fieldname": "item",
	    "label": _("Item"),
	    "fieldtype": "Link",
	    "options": "Syteline Item",
	    "width": 100
	},
	{
	    "fieldname": "comments",
	    "label": _("Comments"),
	    "fieldtype": "Small Text",
	    "options": "",
	    "width": 200
	},
	{
	    "fieldname": "qty_day",
	    "label": _("Quantity on Day"),
	    "fieldtype": "Integer",
	    "options": "",
	    "width": 10
	},
	{
	    "fieldname": "qty_mtd",
	    "label": _("Quantity Month-to-Date"),
	    "fieldtype": "Integer",
	    "options": "",
	    "width": 10
	}]
	return columns

def get_start_up_summary(filters):
	company = filters.get("company")
	# date = filters.get("date")
	return frappe.db.sql("""
		SELECT
			t_data.department,
			t_data.workstation,
			t_data.name,
			'Start Up' AS document_type,
			t_data.su_name,
			IFNULL(t_su2.status,'No Start Up Completed') AS status,
			IFNULL(DATE(t_data.creation),CURDATE()) AS creation,
			IFNULL(LTRIM(LEFT(RIGHT(t_data.creation,15),5)),'00:00') AS time,
			t_su2.operator
        FROM (
        	SELECT
        		t_su.name AS su_name,
                t_we.name,
                MAX(t_su.creation) AS creation,
                LTRIM(LEFT(RIGHT(t_su.creation,15),5)) AS time,
                t_w.name as workstation,
                t_w.department AS department
            FROM `tabWorkstation Entity` t_we
            INNER JOIN `tabWorkstation` t_w ON t_w.name = t_we.workstation
            INNER JOIN `tabDepartment` t_d ON t_d.name = t_w.department AND t_d.company = '{company}'
            LEFT JOIN `tabStart Up` t_su ON t_we.name = t_su.workstation_entity AND DATE(t_su.creation) = CURDATE() AND t_su.docstatus = 1
            GROUP BY t_we.name, t_su.workstation, t_w.department
            ) AS t_data
        LEFT JOIN `tabStart Up` t_su2 ON t_data.su_name = t_su2.name
		ORDER BY t_data.department, t_data.workstation, t_data.name""".format(company=company)
	)

def get_first_off_summary(filters):
	company = filters.get("company")
	# date = filters.get("date")
	return frappe.db.sql("""
		SELECT DISTINCT
			t_w.department,
			IFNULL(t_fo.workstation,t_po.workstation) AS workstation,
			IFNULL(t_fo.workstation_entity,t_po.workstation_entity) AS workstation_entity,
			"First Off" AS document_type,
			t_fo.name,
			IFNULL(t_fo.status, 'No First Off Completed') AS status,
			DATE(t_fo.creation) as date,
			IFNULL(LTRIM(LEFT(RIGHT(t_fo.creation,15),5)),'00:00') AS time,
			t_fo.operator,
			t_po.job_number,
			t_sjo.item
		FROM `tabProduction Output` t_po
		INNER JOIN `tabSyteline Job Order` t_sjo ON t_sjo.name = t_po.job_number
		INNER JOIN `tabWorkstation` t_w ON t_w.name = t_po.workstation
        INNER JOIN `tabDepartment` t_d ON t_d.name = t_w.department AND t_d.company = '{company}'
		LEFT JOIN `tabFirst Off` t_fo ON t_fo.job_number = t_po.job_number AND t_fo.workstation = t_po.workstation AND DATE(t_fo.creation) = CURDATE() AND t_fo.docstatus = 1
		WHERE t_po.date_of_production = CURDATE() AND t_po.job_number <> 'No Job Scheduled'
		ORDER BY t_w.department, workstation, workstation_entity""".format(company=company)
	)

def get_hun_insp_summary(filters):
	company = filters.get("company")
	# date = filters.get("date")
	return frappe.db.sql("""
		SELECT t_his.department, t_his.workstation, t_his.inspection_item, "Hundred Percent Inspection" AS document_type, t_his.name, t_his.status, DATE(t_his.creation) AS creation, IFNULL(LTRIM(LEFT(RIGHT(t_his.creation,15),5)),'00:00') AS time
		FROM `tabHundred Percent Inspection` t_his
        INNER JOIN `tabDepartment` t_d ON t_d.name = t_his.department
		WHERE t_his.docstatus = 1 AND t_his.status = 'Failed - Action Required' AND DATE(t_his.creation) = CURDATE() AND t_d.company = '{company}'
		ORDER BY t_his.inspection_item, t_his.creation""".format(company=company)
	)

def get_shift_handovers_summary(filters):
	company = filters.get("company")
	# date = filters.get("date")
	return frappe.db.sql("""
		SELECT
			t_data.name,
		    "" AS w,
		    t_data.type,
		    "Shift Handover" AS document_type,
		    t_sh3.name,
		    IF(ISNULL(t_sh3.name),'No Shift Handover Reported','Completed') AS status,
		    IFNULL(DATE(t_sh3.creation),CURDATE()) AS creation,
		    IFNULL(LTRIM(LEFT(RIGHT(t_sh3.creation,15),5)),'00:00') AS time,
			"" AS o,
		    "" AS j,
		    "" AS i,
		    t_sh3.comments
		FROM
			(SELECT t_dep.company, t_dep.name, t_dep.require_shift_handover, t_sh.type, MAX(t_sh2.creation) AS creation FROM `tabDepartment` AS t_dep
			CROSS JOIN
				(SELECT DISTINCT type
				FROM `tabShift Handover`) AS t_sh
			LEFT JOIN `tabShift Handover` AS t_sh2 ON t_sh2.department = t_dep.name AND t_sh2.type = t_sh.type
			GROUP BY t_dep.name, t_sh.type
			HAVING t_dep.require_shift_handover = 1 AND t_dep.company = '{company}') as t_data
		LEFT JOIN `tabShift Handover` AS t_sh3
		ON t_sh3.creation = t_data.creation AND DATE(t_sh3.creation) = CURDATE() AND t_sh3.docstatus = 1
		ORDER BY t_data.name, t_data.type DESC""".format(company=company)
	)

def get_quality_defect_summary(filters):
	company = filters.get("company")
	# date = filters.get("date")
	return frappe.db.sql("""
		SELECT
			t_data.name,
		    "" AS w,
		    "" AS we,
		    "Quality Defect" AS document_type,
		    IFNULL(t_data.last_submission,'Never') AS last_submission,
		    IFNULL(t_data.status,'No Data Submitted on this day') AS 'status',
		    DATE(CURDATE()) AS date,
		    "00:00" AS t,
		    "" AS o,
		    "" AS j,
		    "" AS i,
		    "" AS com,
		    IFNULL(t_qday.qty,0) AS qty_day,
		    IFNULL(t_qmtd.qty,0) AS qty_mtd
		FROM
			(SELECT
				t_dept.name,
				IF(CURDATE() - DATE(t_qd.creation) = 0,LTRIM(LEFT(RIGHT(t_qd.creation,15),5)), CONCAT(DATEDIFF(CURDATE(),DATE(t_qd.creation)),' days ago')) AS last_submission,
				IF(CURDATE() - DATE(t_qd.creation) = 0,'Defects Submitted', 'No Data Submitted on this day') AS 'status'
			FROM `tabDepartment` AS t_dept
			LEFT JOIN
				(SELECT department, MAX(creation) as creation
					FROM `tabQuality Defect`
		            WHERE docstatus = 1
					GROUP BY department) AS t_qd
				ON t_qd.department = t_dept.name
			WHERE t_dept.is_production = 1 AND t_dept.is_group = 0 AND t_dept.company = '{company}') AS t_data
		LEFT JOIN
			(SELECT department, SUM(qty) AS qty FROM `tabQuality Defect`
			WHERE docstatus = 1 AND no_defects = 0 AND DATE(creation)= CURDATE()
		    GROUP BY department) AS t_qday
		ON t_qday.department = t_data.name
		LEFT JOIN
			(SELECT department, SUM(qty) AS qty FROM `tabQuality Defect`
			WHERE docstatus = 1 AND no_defects = 0 AND (MONTH(creation) = MONTH(NOW()) AND YEAR(creation) = YEAR(NOW()))
		    GROUP BY department) AS t_qmtd
		ON t_qmtd.department = t_data.name
		ORDER BY t_data.name""".format(company=company)
	)


def get_production_output_summary(filters):
	company = filters.get("company")
	# date = filters.get("date")
	return frappe.db.sql("""
		SELECT DISTINCT
			t_data.department,
		    t_data.workstation,
		    t_data.workstation_entity,
		    "Production Output" AS document_type,
		    "" AS name,
		    'Start Up submitted but no Production Output logged' AS status,
		    DATE(CURDATE()) AS date
		FROM
			(SELECT t_su.department, t_su.workstation, t_su.workstation_entity, t_po.name FROM `tabStart Up` AS t_su
            LEFT JOIN `tabDepartment` t_d ON t_d.name = t_su.department
			LEFT JOIN `tabProduction Output` AS t_po ON t_po.workstation_entity = t_su.workstation_entity AND DATE(t_po.creation) = CURDATE()
			WHERE DATE(t_su.creation) = CURDATE() AND t_su.status <> 'No Production Scheduled' AND t_su.docstatus = 1 AND t_d.company = '{company}') AS t_data
		WHERE ISNULL(t_data.name)
		ORDER BY t_data.department, t_data.workstation, t_data.workstation_entity""".format(company=company)
	)

# Copyright (c) 2013, <NAME> and contributors
# For license information, please see license.txt

from __future__ import unicode_literals
import frappe
from frappe import _

def execute(filters=None):
	if not filters: filters = {}

	columns = get_columns()

	start_up_summary = get_start_up_summary(filters)
	first_off_summary = get_first_off_summary(filters)
	hun_insp_summary = get_hun_insp_summary(filters)
	shift_handovers_summary = get_shift_handovers_summary(filters)
	quality_defect_summary = get_quality_defect_summary(filters)
	production_output_summary = get_production_output_summary(filters)

	data = []
	data.extend(start_up_summary or [])
	data.extend(first_off_summary or [])
	data.extend(hun_insp_summary or [])
	data.extend(shift_handovers_summary or [])
	data.extend(quality_defect_summary or [])
	data.extend(production_output_summary or [])

	return columns, data

def get_columns():
	"""return columns"""
	columns = [{
	    "fieldname": "department",
	    "label": _("Department"),
	    "fieldtype": "Link",
	    "options": "Department",
	    "width": 150
	},
	{
	    "fieldname": "workstation",
	    "label": _("Workstation"),
	    "fieldtype": "Link",
	    "options": "Workstation",
	    "width": 150
	},
	{
	    "fieldname": "workstation_entity",
	    "label": _("Workstation Entity"),
	    "fieldtype": "Link",
	    "options": "Workstation Entity",
	    "width": 150
	},
	{
	    "fieldname": "document",
	    "label": _("Document Type"),
	    "fieldtype": "Link",
	    "options": "DocType",
	    "width": 100
	},
	{
	    "fieldname": "su_name",
	    "label": _("Document Name"),
	    "fieldtype": "Dynamic Link",
	    "options": "document",
	    "width": 100
	},
	{
	    "fieldname": "status",
	    "label": _("Status"),
	    "fieldtype": "Data",
	    "width": 150
	},
	{
	    "fieldname": "creation",
	    "label": _("Date"),
	    "fieldtype": "Date",
	    "width": 100
	},
	{
	    "fieldname": "time",
	    "label": _("Time"),
	    "fieldtype": "Time",
	    "width": 50
	},
	{
	    "fieldname": "operator",
	    "label": _("Operator"),
	    "fieldtype": "Link",
	    "options": "Employee",
	    "width": 100
	},
	{
	    "fieldname": "job_number",
	    "label": _("Job"),
	    "fieldtype": "Link",
	    "options": "Syteline Job Order",
	    "width": 100
	},
	{
	    "fieldname": "item",
	    "label": _("Item"),
	    "fieldtype": "Link",
	    "options": "Syteline Item",
	    "width": 100
	},
	{
	    "fieldname": "comments",
	    "label": _("Comments"),
	    "fieldtype": "Small Text",
	    "options": "",
	    "width": 200
	},
	{
	    "fieldname": "qty_day",
	    "label": _("Quantity on Day"),
	    "fieldtype": "Integer",
	    "options": "",
	    "width": 10
	},
	{
	    "fieldname": "qty_mtd",
	    "label": _("Quantity Month-to-Date"),
	    "fieldtype": "Integer",
	    "options": "",
	    "width": 10
	}]
	return columns

def get_start_up_summary(filters):
	company = filters.get("company")
	# date = filters.get("date")
	return frappe.db.sql("""
		SELECT
			t_data.department,
			t_data.workstation,
			t_data.name,
			'Start Up' AS document_type,
			t_data.su_name,
			IFNULL(t_su2.status,'No Start Up Completed') AS status,
			IFNULL(DATE(t_data.creation),CURDATE()) AS creation,
			IFNULL(LTRIM(LEFT(RIGHT(t_data.creation,15),5)),'00:00') AS time,
			t_su2.operator
        FROM (
        	SELECT
        		t_su.name AS su_name,
                t_we.name,
                MAX(t_su.creation) AS creation,
                LTRIM(LEFT(RIGHT(t_su.creation,15),5)) AS time,
                t_w.name as workstation,
                t_w.department AS department
            FROM `tabWorkstation Entity` t_we
            INNER JOIN `tabWorkstation` t_w ON t_w.name = t_we.workstation
            INNER JOIN `tabDepartment` t_d ON t_d.name = t_w.department AND t_d.company = '{company}'
            LEFT JOIN `tabStart Up` t_su ON t_we.name = t_su.workstation_entity AND DATE(t_su.creation) = CURDATE() AND t_su.docstatus = 1
            GROUP BY t_we.name, t_su.workstation, t_w.department
            ) AS t_data
        LEFT JOIN `tabStart Up` t_su2 ON t_data.su_name = t_su2.name
		ORDER BY t_data.department, t_data.workstation, t_data.name""".format(company=company)
	)

def get_first_off_summary(filters):
	company = filters.get("company")
	# date = filters.get("date")
	return frappe.db.sql("""
		SELECT DISTINCT
			t_w.department,
			IFNULL(t_fo.workstation,t_po.workstation) AS workstation,
			IFNULL(t_fo.workstation_entity,t_po.workstation_entity) AS workstation_entity,
			"First Off" AS document_type,
			t_fo.name,
			IFNULL(t_fo.status, 'No First Off Completed') AS status,
			DATE(t_fo.creation) as date,
			IFNULL(LTRIM(LEFT(RIGHT(t_fo.creation,15),5)),'00:00') AS time,
			t_fo.operator,
			t_po.job_number,
			t_sjo.item
		FROM `tabProduction Output` t_po
		INNER JOIN `tabSyteline Job Order` t_sjo ON t_sjo.name = t_po.job_number
		INNER JOIN `tabWorkstation` t_w ON t_w.name = t_po.workstation
        INNER JOIN `tabDepartment` t_d ON t_d.name = t_w.department AND t_d.company = '{company}'
		LEFT JOIN `tabFirst Off` t_fo ON t_fo.job_number = t_po.job_number AND t_fo.workstation = t_po.workstation AND DATE(t_fo.creation) = CURDATE() AND t_fo.docstatus = 1
		WHERE t_po.date_of_production = CURDATE() AND t_po.job_number <> 'No Job Scheduled'
		ORDER BY t_w.department, workstation, workstation_entity""".format(company=company)
	)

def get_hun_insp_summary(filters):
	company = filters.get("company")
	# date = filters.get("date")
	return frappe.db.sql("""
		SELECT t_his.department, t_his.workstation, t_his.inspection_item, "Hundred Percent Inspection" AS document_type, t_his.name, t_his.status, DATE(t_his.creation) AS creation, IFNULL(LTRIM(LEFT(RIGHT(t_his.creation,15),5)),'00:00') AS time
		FROM `tabHundred Percent Inspection` t_his
        INNER JOIN `tabDepartment` t_d ON t_d.name = t_his.department
		WHERE t_his.docstatus = 1 AND t_his.status = 'Failed - Action Required' AND DATE(t_his.creation) = CURDATE() AND t_d.company = '{company}'
		ORDER BY t_his.inspection_item, t_his.creation""".format(company=company)
	)

def get_shift_handovers_summary(filters):
	company = filters.get("company")
	# date = filters.get("date")
	return frappe.db.sql("""
		SELECT
			t_data.name,
		    "" AS w,
		    t_data.type,
		    "Shift Handover" AS document_type,
		    t_sh3.name,
		    IF(ISNULL(t_sh3.name),'No Shift Handover Reported','Completed') AS status,
		    IFNULL(DATE(t_sh3.creation),CURDATE()) AS creation,
		    IFNULL(LTRIM(LEFT(RIGHT(t_sh3.creation,15),5)),'00:00') AS time,
			"" AS o,
		    "" AS j,
		    "" AS i,
		    t_sh3.comments
		FROM
			(SELECT t_dep.company, t_dep.name, t_dep.require_shift_handover, t_sh.type, MAX(t_sh2.creation) AS creation FROM `tabDepartment` AS t_dep
			CROSS JOIN
				(SELECT DISTINCT type
				FROM `tabShift Handover`) AS t_sh
			LEFT JOIN `tabShift Handover` AS t_sh2 ON t_sh2.department = t_dep.name AND t_sh2.type = t_sh.type
			GROUP BY t_dep.name, t_sh.type
			HAVING t_dep.require_shift_handover = 1 AND t_dep.company = '{company}') as t_data
		LEFT JOIN `tabShift Handover` AS t_sh3
		ON t_sh3.creation = t_data.creation AND DATE(t_sh3.creation) = CURDATE() AND t_sh3.docstatus = 1
		ORDER BY t_data.name, t_data.type DESC""".format(company=company)
	)

def get_quality_defect_summary(filters):
	company = filters.get("company")
	# date = filters.get("date")
	return frappe.db.sql("""
		SELECT
			t_data.name,
		    "" AS w,
		    "" AS we,
		    "Quality Defect" AS document_type,
		    IFNULL(t_data.last_submission,'Never') AS last_submission,
		    IFNULL(t_data.status,'No Data Submitted on this day') AS 'status',
		    DATE(CURDATE()) AS date,
		    "00:00" AS t,
		    "" AS o,
		    "" AS j,
		    "" AS i,
		    "" AS com,
		    IFNULL(t_qday.qty,0) AS qty_day,
		    IFNULL(t_qmtd.qty,0) AS qty_mtd
		FROM
			(SELECT
				t_dept.name,
				IF(CURDATE() - DATE(t_qd.creation) = 0,LTRIM(LEFT(RIGHT(t_qd.creation,15),5)), CONCAT(DATEDIFF(CURDATE(),DATE(t_qd.creation)),' days ago')) AS last_submission,
				IF(CURDATE() - DATE(t_qd.creation) = 0,'Defects Submitted', 'No Data Submitted on this day') AS 'status'
			FROM `tabDepartment` AS t_dept
			LEFT JOIN
				(SELECT department, MAX(creation) as creation
					FROM `tabQuality Defect`
		            WHERE docstatus = 1
					GROUP BY department) AS t_qd
				ON t_qd.department = t_dept.name
			WHERE t_dept.is_production = 1 AND t_dept.is_group = 0 AND t_dept.company = '{company}') AS t_data
		LEFT JOIN
			(SELECT department, SUM(qty) AS qty FROM `tabQuality Defect`
			WHERE docstatus = 1 AND no_defects = 0 AND DATE(creation)= CURDATE()
		    GROUP BY department) AS t_qday
		ON t_qday.department = t_data.name
		LEFT JOIN
			(SELECT department, SUM(qty) AS qty FROM `tabQuality Defect`
			WHERE docstatus = 1 AND no_defects = 0 AND (MONTH(creation) = MONTH(NOW()) AND YEAR(creation) = YEAR(NOW()))
		    GROUP BY department) AS t_qmtd
		ON t_qmtd.department = t_data.name
		ORDER BY t_data.name""".format(company=company)
	)


def get_production_output_summary(filters):
	company = filters.get("company")
	# date = filters.get("date")
	return frappe.db.sql("""
		SELECT DISTINCT
			t_data.department,
		    t_data.workstation,
		    t_data.workstation_entity,
		    "Production Output" AS document_type,
		    "" AS name,
		    'Start Up submitted but no Production Output logged' AS status,
		    DATE(CURDATE()) AS date
		FROM
			(SELECT t_su.department, t_su.workstation, t_su.workstation_entity, t_po.name FROM `tabStart Up` AS t_su
            LEFT JOIN `tabDepartment` t_d ON t_d.name = t_su.department
			LEFT JOIN `tabProduction Output` AS t_po ON t_po.workstation_entity = t_su.workstation_entity AND DATE(t_po.creation) = CURDATE()
			WHERE DATE(t_su.creation) = CURDATE() AND t_su.status <> 'No Production Scheduled' AND t_su.docstatus = 1 AND t_d.company = '{company}') AS t_data
		WHERE ISNULL(t_data.name)
		ORDER BY t_data.department, t_data.workstation, t_data.workstation_entity""".format(company=company)
	)

# Copyright (c) 2013, <NAME> and contributors # For license information, please see license.txt return columns # date = filters.get("date") SELECT 			t_data.department, 			t_data.workstation, 			t_data.name, 			'Start Up' AS document_type, 			t_data.su_name, 			IFNULL(t_su2.status,'No Start Up Completed') AS status, 			IFNULL(DATE(t_data.creation),CURDATE()) AS creation, 			IFNULL(LTRIM(LEFT(RIGHT(t_data.creation,15),5)),'00:00') AS time, 			t_su2.operator         FROM (         	SELECT         		t_su.name AS su_name,                 t_we.name,                 MAX(t_su.creation) AS creation,                 LTRIM(LEFT(RIGHT(t_su.creation,15),5)) AS time,                 t_w.name as workstation,                 t_w.department AS department             FROM `tabWorkstation Entity` t_we             INNER JOIN `tabWorkstation` t_w ON t_w.name = t_we.workstation             INNER JOIN `tabDepartment` t_d ON t_d.name = t_w.department AND t_d.company = '{company}'             LEFT JOIN `tabStart Up` t_su ON t_we.name = t_su.workstation_entity AND DATE(t_su.creation) = CURDATE() AND t_su.docstatus = 1             GROUP BY t_we.name, t_su.workstation, t_w.department             ) AS t_data         LEFT JOIN `tabStart Up` t_su2 ON t_data.su_name = t_su2.name 		ORDER BY t_data.department, t_data.workstation, t_data.name # date = filters.get("date") SELECT DISTINCT 			t_w.department, 			IFNULL(t_fo.workstation,t_po.workstation) AS workstation, 			IFNULL(t_fo.workstation_entity,t_po.workstation_entity) AS workstation_entity, 			"First Off" AS document_type, 			t_fo.name, 			IFNULL(t_fo.status, 'No First Off Completed') AS status, 			DATE(t_fo.creation) as date, 			IFNULL(LTRIM(LEFT(RIGHT(t_fo.creation,15),5)),'00:00') AS time, 			t_fo.operator, 			t_po.job_number, 			t_sjo.item 		FROM `tabProduction Output` t_po 		INNER JOIN `tabSyteline Job Order` t_sjo ON t_sjo.name = t_po.job_number 		INNER JOIN `tabWorkstation` t_w ON t_w.name = t_po.workstation         INNER JOIN `tabDepartment` t_d ON t_d.name = t_w.department AND t_d.company = '{company}' 		LEFT JOIN `tabFirst Off` t_fo ON t_fo.job_number = t_po.job_number AND t_fo.workstation = t_po.workstation AND DATE(t_fo.creation) = CURDATE() AND t_fo.docstatus = 1 		WHERE t_po.date_of_production = CURDATE() AND t_po.job_number <> 'No Job Scheduled' 		ORDER BY t_w.department, workstation, workstation_entity # date = filters.get("date") SELECT t_his.department, t_his.workstation, t_his.inspection_item, "Hundred Percent Inspection" AS document_type, t_his.name, t_his.status, DATE(t_his.creation) AS creation, IFNULL(LTRIM(LEFT(RIGHT(t_his.creation,15),5)),'00:00') AS time 		FROM `tabHundred Percent Inspection` t_his         INNER JOIN `tabDepartment` t_d ON t_d.name = t_his.department 		WHERE t_his.docstatus = 1 AND t_his.status = 'Failed - Action Required' AND DATE(t_his.creation) = CURDATE() AND t_d.company = '{company}' 		ORDER BY t_his.inspection_item, t_his.creation # date = filters.get("date") SELECT 			t_data.name, 		    "" AS w, 		    t_data.type, 		    "Shift Handover" AS document_type, 		    t_sh3.name, 		    IF(ISNULL(t_sh3.name),'No Shift Handover Reported','Completed') AS status, 		    IFNULL(DATE(t_sh3.creation),CURDATE()) AS creation, 		    IFNULL(LTRIM(LEFT(RIGHT(t_sh3.creation,15),5)),'00:00') AS time, 			"" AS o, 		    "" AS j, 		    "" AS i, 		    t_sh3.comments 		FROM 			(SELECT t_dep.company, t_dep.name, t_dep.require_shift_handover, t_sh.type, MAX(t_sh2.creation) AS creation FROM `tabDepartment` AS t_dep 			CROSS JOIN 				(SELECT DISTINCT type 				FROM `tabShift Handover`) AS t_sh 			LEFT JOIN `tabShift Handover` AS t_sh2 ON t_sh2.department = t_dep.name AND t_sh2.type = t_sh.type 			GROUP BY t_dep.name, t_sh.type 			HAVING t_dep.require_shift_handover = 1 AND t_dep.company = '{company}') as t_data 		LEFT JOIN `tabShift Handover` AS t_sh3 		ON t_sh3.creation = t_data.creation AND DATE(t_sh3.creation) = CURDATE() AND t_sh3.docstatus = 1 		ORDER BY t_data.name, t_data.type DESC # date = filters.get("date") SELECT 			t_data.name, 		    "" AS w, 		    "" AS we, 		    "Quality Defect" AS document_type, 		    IFNULL(t_data.last_submission,'Never') AS last_submission, 		    IFNULL(t_data.status,'No Data Submitted on this day') AS 'status', 		    DATE(CURDATE()) AS date, 		    "00:00" AS t, 		    "" AS o, 		    "" AS j, 		    "" AS i, 		    "" AS com, 		    IFNULL(t_qday.qty,0) AS qty_day, 		    IFNULL(t_qmtd.qty,0) AS qty_mtd 		FROM 			(SELECT 				t_dept.name, 				IF(CURDATE() - DATE(t_qd.creation) = 0,LTRIM(LEFT(RIGHT(t_qd.creation,15),5)), CONCAT(DATEDIFF(CURDATE(),DATE(t_qd.creation)),' days ago')) AS last_submission, 				IF(CURDATE() - DATE(t_qd.creation) = 0,'Defects Submitted', 'No Data Submitted on this day') AS 'status' 			FROM `tabDepartment` AS t_dept 			LEFT JOIN 				(SELECT department, MAX(creation) as creation 					FROM `tabQuality Defect` 		            WHERE docstatus = 1 					GROUP BY department) AS t_qd 				ON t_qd.department = t_dept.name 			WHERE t_dept.is_production = 1 AND t_dept.is_group = 0 AND t_dept.company = '{company}') AS t_data 		LEFT JOIN 			(SELECT department, SUM(qty) AS qty FROM `tabQuality Defect` 			WHERE docstatus = 1 AND no_defects = 0 AND DATE(creation)= CURDATE() 		    GROUP BY department) AS t_qday 		ON t_qday.department = t_data.name 		LEFT JOIN 			(SELECT department, SUM(qty) AS qty FROM `tabQuality Defect` 			WHERE docstatus = 1 AND no_defects = 0 AND (MONTH(creation) = MONTH(NOW()) AND YEAR(creation) = YEAR(NOW())) 		    GROUP BY department) AS t_qmtd 		ON t_qmtd.department = t_data.name 		ORDER BY t_data.name # date = filters.get("date") SELECT DISTINCT 			t_data.department, 		    t_data.workstation, 		    t_data.workstation_entity, 		    "Production Output" AS document_type, 		    "" AS name, 		    'Start Up submitted but no Production Output logged' AS status, 		    DATE(CURDATE()) AS date 		FROM 			(SELECT t_su.department, t_su.workstation, t_su.workstation_entity, t_po.name FROM `tabStart Up` AS t_su             LEFT JOIN `tabDepartment` t_d ON t_d.name = t_su.department 			LEFT JOIN `tabProduction Output` AS t_po ON t_po.workstation_entity = t_su.workstation_entity AND DATE(t_po.creation) = CURDATE() 			WHERE DATE(t_su.creation) = CURDATE() AND t_su.status <> 'No Production Scheduled' AND t_su.docstatus = 1 AND t_d.company = '{company}') AS t_data 		WHERE ISNULL(t_data.name) 		ORDER BY t_data.department, t_data.workstation, t_data.workstation_entity