# 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()

	data = get_process_flow_steps(filters)

	return columns, data

def get_columns():
	"""return columns"""
	columns = [
	{
	    "fieldname": "department",
	    "label": _("Department"),
	    "fieldtype": "Link",
	    "options": "Department",
	    "width": 175
	},
	{
	    "fieldname": "sequence",
	    "label": _("Sequence"),
	    "fieldtype": "Data",
	    "options": "",
	    "width": 50
	},
	{
	    "fieldname": "workstation",
	    "label": _("Workstation"),
	    "fieldtype": "Link",
	    "options": "Workstation",
	    "width": 120
	},
	{
	    "fieldname": "step",
	    "label": _("Step"),
	    "fieldtype": "Link",
	    "options": "Step",
	    "width": 100
	},
	{
	    "fieldname": "description",
	    "label": _("Description"),
	    "fieldtype": "",
	    "options": "",
	    "width": 350
	},
	{
	    "fieldname": "type",
	    "label": _("Type"),
	    "fieldtype": "",
	    "options": "",
	    "width": 150
	},
	{
	    "fieldname": "control",
	    "label": _("Control ID"),
	    "fieldtype": "",
	    "options": "",
	    "width": 70
	},
	{
	    "fieldname": "control_description",
	    "label": _("Control"),
	    "fieldtype": "",
	    "options": "",
	    "width": 300
	},
	{
	    "fieldname": "test_method",
	    "label": _("Test Method"),
	    "fieldtype": "",
	    "options": "",
	    "width": 100
	},
	{
	    "fieldname": "control_method",
	    "label": _("Control Method"),
	    "fieldtype": "",
	    "options": "",
	    "width": 180
	},
	{
	    "fieldname": "tolerance",
	    "label": _("Tolerance"),
	    "fieldtype": "",
	    "options": "",
	    "width": 100
	},
	{
	    "fieldname": "customer_requirement",
	    "label": _("SC/CC"),
	    "fieldtype": "Link",
	    "options": "Customer Requirement",
	    "width": 80
	},
	{
	    "fieldname": "sample_size",
	    "label": _("Sample Size"),
	    "fieldtype": "",
	    "options": "",
	    "width": 50
	},
	{
	    "fieldname": "sample_frequency",
	    "label": _("Sample Frequency"),
	    "fieldtype": "",
	    "options": "",
	    "width": 80
	},
	{
	    "fieldname": "reaction_plan",
	    "label": _("Reaction Plan"),
	    "fieldtype": "",
	    "options": "",
	    "width": 200
	},
	{
	    "fieldname": "title",
	    "label": _("Title"),
	    "fieldtype": "",
	    "options": "",
	    "width": 1
	}]

	return columns

def get_process_flow_steps(filters):

	company = filters.get("company")
	product_line_process_flow = filters.get("product_line_process_flow")

	return frappe.db.sql("""
			SELECT
				t_ws.department,
			    CONCAT(t_plpfw.idx,'.',t_wsstep.idx) AS sequence,
				t_wsstep.parent AS workstation,
				t_wsstep.step,
				t_step.description AS step_description,
				t_controls.type,
				t_controls.control_name,
				t_controls.description AS control_description,
				t_controls.test_method,
				t_controls.control_method,
				t_controls.tolerance,
                t_controls.customer_requirement,
				t_controls.sample_size,
				t_controls.sample_frequency,
				t_controls.reaction_plan,
                t_plpf.title
			FROM `tabWorkstation Step` t_wsstep
			INNER JOIN `tabProduct Line Process Flow Workstation` t_plpfw ON t_plpfw.workstation = t_wsstep.parent
			INNER JOIN `tabStep` t_step ON t_step.name = t_wsstep.step
			INNER JOIN `tabWorkstation` t_ws ON t_ws.name = t_wsstep.parent
            INNER JOIN `tabProduct Line Process Flow` t_plpf ON t_plpf.name = t_plpfw.parent
			LEFT JOIN
				(SELECT
					t_stepchar.name AS control_name,
					t_stepchar.description,
					t_stepchar.type,
					t_stepchar.source,
					t_stepchar.tolerance,
					t_stepchar.test_method,
					t_stepchar.sample_size,
					t_stepchar.sample_frequency,
					t_stepchar.control_method,
					t_stepchar.reaction_plan,
					t_stepchar.parent,
					t_stepchar.idx,
                    t_creq.name AS customer_requirement
				FROM `tabStep Characteristic` t_stepchar
				LEFT JOIN `tabCustomer Requirement Control` t_creqcontrol ON t_creqcontrol.control = t_stepchar.name
				LEFT JOIN `tabCustomer Requirement` t_creq ON t_creq.name = t_creqcontrol.parent
				LEFT JOIN `tabStep Failure Mode` t_stepfmea ON t_stepfmea.current_control = t_stepchar.name
				WHERE t_creq.product = '{product_line_process_flow}' OR (t_creq.product IS NULL AND t_stepfmea.name IS NOT NULL)
				) AS t_controls ON t_controls.parent = t_wsstep.step
			WHERE t_plpfw.parent = '{product_line_process_flow}'
			ORDER BY t_plpfw.idx, t_wsstep.idx, t_controls.idx""".format(
				product_line_process_flow=product_line_process_flow
			)
	)
