import frappe
from frappe import _
from frappe.utils import nowdate, add_days
from datetime import date, time, datetime, timedelta


#Get list of all statups for all workstations and then filter for the workstation
@frappe.whitelist()
def get_start_up_summary_data(dept):
    """
    Gets today's Start Up Data for a Department
    """
    data = frappe.db.sql("""
    SELECT t_data.name, IFNULL(t_su2.status,'No Start Up Completed') AS status, IFNULL(DATE(t_data.creation),CURDATE()) AS creation, LTRIM(LEFT(RIGHT(t_data.creation,15),5)) AS time, t_su2.operator, t_data.workstation, t_data.department
        FROM (
        	SELECT
        		t_su.name AS first_off_name,
                t_we.name,
                MAX(t_su.creation) AS creation,
                LTRIM(LEFT(RIGHT(t_su.creation,15),5)) AS time,
                t_su.workstation,
                IFNULL(t_su.department,'%s') AS department
            FROM `tabWorkstation Entity` t_we
            INNER JOIN `tabWorkstation` t_w ON t_w.name = t_we.workstation
            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
            WHERE t_w.department = '%s'
            GROUP BY t_we.name, t_su.workstation, IFNULL(t_su.department,'%s')
            ) AS t_data
        LEFT JOIN `tabStart Up` t_su2 ON t_data.first_off_name = t_su2.name"""%(dept,dept,dept),as_list=1)
    entities = []
    #Each entity will be a concantenated string of column values eg department, date, time, status, job number. The entities will be inserted into a list(Entities list)
    for d in data:
        myentity = str(d[0]) +"," + str(d[1]) + "," + str(d[2]) + "," + str(d[3])  + "," + str(d[4])  + "," + str(d[5]) + "," + str(d[6])
        entities.append(myentity)
    return entities

@frappe.whitelist()
def get_first_off_summary_data(dept):
    """
    Gets today's First Off Data for a Department
    """
    data = frappe.db.sql("""
    SELECT DISTINCT
        IFNULL(t_fo.workstation_entity,t_po.workstation_entity) AS workstation_entity,
        IFNULL(t_fo.status, 'No First Off Completed') AS status,
        DATE(t_fo.creation) as date,
        LTRIM(LEFT(RIGHT(t_fo.creation,15),5)) AS time,
        t_po.job_number,
        t_sjo.item,
        t_w.department
    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
    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_w.department = '%s'
    """%(dept),as_list=1)
    entities = []
    #    #Each entity will be a concantenated string of column values eg department, date, time, status, job number. The entities will be inserted into a list(Entities list)
    for d in data:
        myentity = str(d[0]) +"," + str(d[1]) + "," + str(d[2]) + "," + str(d[3])  + "," + str(d[4])  + "," + str(d[5]) + "," + str(d[6])
        entities.append(myentity)
    return entities;

@frappe.whitelist()
def get_shift_handover_comments(dept):
    data = frappe.db.sql("""
    SELECT sh.type as shift_type,sh.creation as time_stamp,sh.modified_by as name,sh.comments as comment
    FROM `tabShift Handover` as sh
    WHERE sh.department = '%s' AND sh.docstatus = 1 AND sh.creation = (SELECT MAX(creation) FROM `tabShift Handover`)
    """%(dept),as_dict=True)

    absentees = frappe.db.sql("""
    SELECT COUNT(e.employee_name) AS absentees FROM `tabShift Handover Employee` AS e
    LEFT OUTER JOIN `tabShift Handover` as s ON e.parent = s.name
    WHERE s.department = '%s' AND s.docstatus = 1 AND e.present = 'No' AND s.creation = (SELECT MAX(creation)
    FROM `tabShift Handover`)
    """%(dept),as_dict=True)
    for d in data:
        for a in absentees:
            d.update(a)
    return data

import frappe
from frappe import _
from frappe.utils import nowdate, add_days
from datetime import date, time, datetime, timedelta


#Get list of all statups for all workstations and then filter for the workstation
@frappe.whitelist()
def get_start_up_summary_data(dept):
    """
    Gets today's Start Up Data for a Department
    """
    data = frappe.db.sql("""
    SELECT t_data.name, IFNULL(t_su2.status,'No Start Up Completed') AS status, IFNULL(DATE(t_data.creation),CURDATE()) AS creation, LTRIM(LEFT(RIGHT(t_data.creation,15),5)) AS time, t_su2.operator, t_data.workstation, t_data.department
        FROM (
        	SELECT
        		t_su.name AS first_off_name,
                t_we.name,
                MAX(t_su.creation) AS creation,
                LTRIM(LEFT(RIGHT(t_su.creation,15),5)) AS time,
                t_su.workstation,
                IFNULL(t_su.department,'%s') AS department
            FROM `tabWorkstation Entity` t_we
            INNER JOIN `tabWorkstation` t_w ON t_w.name = t_we.workstation
            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
            WHERE t_w.department = '%s'
            GROUP BY t_we.name, t_su.workstation, IFNULL(t_su.department,'%s')
            ) AS t_data
        LEFT JOIN `tabStart Up` t_su2 ON t_data.first_off_name = t_su2.name"""%(dept,dept,dept),as_list=1)
    entities = []
    #Each entity will be a concantenated string of column values eg department, date, time, status, job number. The entities will be inserted into a list(Entities list)
    for d in data:
        myentity = str(d[0]) +"," + str(d[1]) + "," + str(d[2]) + "," + str(d[3])  + "," + str(d[4])  + "," + str(d[5]) + "," + str(d[6])
        entities.append(myentity)
    return entities

@frappe.whitelist()
def get_first_off_summary_data(dept):
    """
    Gets today's First Off Data for a Department
    """
    data = frappe.db.sql("""
    SELECT DISTINCT
        IFNULL(t_fo.workstation_entity,t_po.workstation_entity) AS workstation_entity,
        IFNULL(t_fo.status, 'No First Off Completed') AS status,
        DATE(t_fo.creation) as date,
        LTRIM(LEFT(RIGHT(t_fo.creation,15),5)) AS time,
        t_po.job_number,
        t_sjo.item,
        t_w.department
    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
    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_w.department = '%s'
    """%(dept),as_list=1)
    entities = []
    #    #Each entity will be a concantenated string of column values eg department, date, time, status, job number. The entities will be inserted into a list(Entities list)
    for d in data:
        myentity = str(d[0]) +"," + str(d[1]) + "," + str(d[2]) + "," + str(d[3])  + "," + str(d[4])  + "," + str(d[5]) + "," + str(d[6])
        entities.append(myentity)
    return entities;

@frappe.whitelist()
def get_shift_handover_comments(dept):
    data = frappe.db.sql("""
    SELECT sh.type as shift_type,sh.creation as time_stamp,sh.modified_by as name,sh.comments as comment
    FROM `tabShift Handover` as sh
    WHERE sh.department = '%s' AND sh.docstatus = 1 AND sh.creation = (SELECT MAX(creation) FROM `tabShift Handover`)
    """%(dept),as_dict=True)

    absentees = frappe.db.sql("""
    SELECT COUNT(e.employee_name) AS absentees FROM `tabShift Handover Employee` AS e
    LEFT OUTER JOIN `tabShift Handover` as s ON e.parent = s.name
    WHERE s.department = '%s' AND s.docstatus = 1 AND e.present = 'No' AND s.creation = (SELECT MAX(creation)
    FROM `tabShift Handover`)
    """%(dept),as_dict=True)
    for d in data:
        for a in absentees:
            d.update(a)
    return data

#Get list of all statups for all workstations and then filter for the workstation Gets today's Start Up Data for a Department SELECT t_data.name, IFNULL(t_su2.status,'No Start Up Completed') AS status, IFNULL(DATE(t_data.creation),CURDATE()) AS creation, LTRIM(LEFT(RIGHT(t_data.creation,15),5)) AS time, t_su2.operator, t_data.workstation, t_data.department         FROM (         	SELECT         		t_su.name AS first_off_name,                 t_we.name,                 MAX(t_su.creation) AS creation,                 LTRIM(LEFT(RIGHT(t_su.creation,15),5)) AS time,                 t_su.workstation,                 IFNULL(t_su.department,'%s') AS department             FROM `tabWorkstation Entity` t_we             INNER JOIN `tabWorkstation` t_w ON t_w.name = t_we.workstation             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             WHERE t_w.department = '%s'             GROUP BY t_we.name, t_su.workstation, IFNULL(t_su.department,'%s')             ) AS t_data         LEFT JOIN `tabStart Up` t_su2 ON t_data.first_off_name = t_su2.name #Each entity will be a concantenated string of column values eg department, date, time, status, job number. The entities will be inserted into a list(Entities list) Gets today's First Off Data for a Department SELECT DISTINCT         IFNULL(t_fo.workstation_entity,t_po.workstation_entity) AS workstation_entity,         IFNULL(t_fo.status, 'No First Off Completed') AS status,         DATE(t_fo.creation) as date,         LTRIM(LEFT(RIGHT(t_fo.creation,15),5)) AS time,         t_po.job_number,         t_sjo.item,         t_w.department     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     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_w.department = '%s' #    #Each entity will be a concantenated string of column values eg department, date, time, status, job number. The entities will be inserted into a list(Entities list) SELECT sh.type as shift_type,sh.creation as time_stamp,sh.modified_by as name,sh.comments as comment     FROM `tabShift Handover` as sh     WHERE sh.department = '%s' AND sh.docstatus = 1 AND sh.creation = (SELECT MAX(creation) FROM `tabShift Handover`) SELECT COUNT(e.employee_name) AS absentees FROM `tabShift Handover Employee` AS e     LEFT OUTER JOIN `tabShift Handover` as s ON e.parent = s.name     WHERE s.department = '%s' AND s.docstatus = 1 AND e.present = 'No' AND s.creation = (SELECT MAX(creation)     FROM `tabShift Handover`)