from __future__ import unicode_literals
import math
import frappe
from frappe.utils import cstr, add_days, date_diff, getdate, format_date
from frappe import _, bold
from frappe.utils.csvutils import UnicodeWriter, read_csv_content
from frappe.utils.data import format_date
from frappe.utils.file_manager import get_file
from frappe.model.document import Document
from frappe.utils.background_jobs import enqueue

from datetime import date, timedelta, datetime
import openpyxl
from openpyxl import Workbook


import openpyxl
import xlrd
import re
from openpyxl.styles import Font, Alignment, Border, Side
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
from openpyxl.styles import GradientFill, PatternFill
from six import BytesIO, string_types


@frappe.whitelist()
def download():
    filename = 'cl plan shortage'
    test = build_xlsx_response(filename)

    
# return xlsx file object
def make_xlsx(data, sheet_name=None, wb=None, column_widths=None):
    args = frappe.local.form_dict
    column_widths = column_widths or []
    if wb is None:
        wb = openpyxl.Workbook()
    ws = wb.create_sheet(sheet_name, 0)
    header = add_header(args)
    ws.append(header)

    header = add_day_header(args)
    ws.append(header)

    header = add_header_emp_type(args)
    ws.append(header)
   
    data = get_data(args)

    for row in data:
        ws.append(row)


    ws.sheet_view.zoomScale = 70

    xlsx_file = BytesIO()
    wb.save(xlsx_file)
    return xlsx_file 

def build_xlsx_response(filename):
    xlsx_file = make_xlsx(filename)
    # write out response as a xlsx type
    frappe.response['filename'] = filename + '.xlsx'
    frappe.response['filecontent'] = xlsx_file.getvalue()
    frappe.response['type'] = 'binary'


@frappe.whitelist()
def add_header(args):
    header = ["488"]
    dates = get_dates(args)
    for date in dates:
        # frappe.log_error(message=[date,type(date)])
        date = datetime.strptime(date,'%Y-%m-%d')
        date = date.strftime('%d-%b-%Y')
        header.extend([date,'','','',''])
    header.append('OVERALL TOTAL')
    return header

@frappe.whitelist()
def add_day_header(args):
    day_header = ["488"]
    dates = get_dates(args)
    for date in dates:
        date = datetime.strptime(date,'%Y-%m-%d')
        day = datetime.date(date).strftime('%a')
        day_header.extend([day,'','','',''])
    return day_header

@frappe.whitelist()
def add_header_emp_type(args):
    header = [""]
    emp_type = ['plan','actual','diff','shortage','amount to be debited']
    dates = get_dates(args)
    for i in range(len(dates)):
        header.extend(emp_type)
    header.extend(emp_type)
    return header


@frappe.whitelist()
def get_data(args):
    data = []
    contractors = frappe.get_all('Contractor',{'status':'Active'})
    for contractor in contractors:
        side_sum=0
        side_count=0
        side_diff=0
        side_short=0
        # side_amt=0
        row = [contractor.name]
        dates =get_dates(args)
        for date in dates:
            
            sum=0
            if frappe.db.exists('CL Head Count Plan',{'contractor':contractor.name,'date':date}):
                plan = frappe.get_doc('CL Head Count Plan',{'contractor':contractor.name,'date':date})
                sum = plan.shift_1 + plan.shift_2+plan.shift_3+plan.shift_pp1+plan.shift_pp2
                
            count = frappe.db.sql("""select count(*) as count from `tabQR Checkin` 
                                    left join `tabEmployee` on `tabQR Checkin`.employee = `tabEmployee`.name
                                     where `tabEmployee`.contractor='%s' and  `tabQR Checkin`.shift_date='%s' and ot = 0
                                    """%(contractor.name,date),as_dict=True)
            if count:
                count = count[0].count
            else:
                count = 0
            diff = count - sum
            try:
                shortage = (diff /sum)*100
            except ZeroDivisionError:
                shortage = 0
            float_value = math.floor(shortage)
            short_percent = str(float_value)+'%'
            amt_debt = diff * 488
            row.append(sum)
            row.append(count)
            row.append(diff)
            row.append(short_percent)
            row.append(amt_debt)
            side_sum+=sum
            side_count+=count
            side_diff = side_sum-side_count
            try:
                shortage = (side_diff/side_sum)*100
            except ZeroDivisionError:
                shortage = 0
            side_short+=shortage
            float_value = math.floor(side_short)
            short_percent = str(float_value)+'%'
            amount_debt = side_diff * 488
            # side_amt+=amount_debt
        row.append(side_sum)
        row.append(side_count)
        row.append(side_diff)
        row.append(short_percent)
        row.append(amount_debt)
        data.append(row)
    tot = total(args)
    data.append(tot)
    
    return data

def get_dates(args):
    no_of_days = date_diff(add_days(args.to_date, 1), args.from_date)
    dates = [add_days(args.from_date, i) for i in range(0, no_of_days)]
    return dates

def total(args):
    data = ['TOTAL',]
    dates = get_dates(args)
    contractors_list = []
    contractors = frappe.get_all('Contractor',{'status':'Active'})
    for c in contractors:
        contractors_list.append(c.name)
    row = []
    for date in dates:
        plan_total = 0
        actual_total =0
        diff=0
        short =0
        amount_debt=0
        if frappe.db.exists('CL Head Count Plan',{'contractor':('in',contractors_list),'date':date}):
            plans = frappe.get_all('CL Head Count Plan',{'contractor':('in',contractors_list),'date':date})
            for p in plans:
                shift_1 = 0
                shift_2 = 0
                shift_3 = 0
                shift_pp1 = 0
                shift_pp2 = 0
                plan = frappe.get_doc("CL Head Count Plan",p.name)
                if plan.shift_1:
                    shift_1 = plan.shift_1
                if plan.shift_2:
                    shift_2 = plan.shift_2
                if plan.shift_3:
                    shift_3 = plan.shift_3
                if plan.shift_pp1:
                    shift_pp1 = plan.shift_pp1
                if plan.shift_pp2:
                    shift_pp2 = plan.shift_pp2
                plan_total += shift_1 + shift_2 + shift_3 + shift_pp1 + shift_pp2
            row.append(plan_total)
        count = frappe.db.sql("""select count(*) as count from `tabQR Checkin` 
                                left join `tabEmployee` on `tabQR Checkin`.employee = `tabEmployee`.name
                                    where `tabEmployee`.contractor in %s and  `tabQR Checkin`.shift_date='%s' and ot = 0
                                """%(tuple(contractors_list),date),as_dict=True)
        if count:
            count = count[0].count
        else:
            count = 0
        actual_total+= count        
        row.append(actual_total)
        diff_value =plan_total-actual_total
        diff+=diff_value
        row.append(diff)
        try:
            shortage = (diff /actual_total)*100
        except ZeroDivisionError:
            shortage = 0
        float_value = math.floor(shortage)
        short_percent = str(float_value)+'%'
        row.append(short_percent)
        amt_debt = diff_value*488
        amount_debt+=amt_debt
        row.append(amount_debt)
     
    data.extend(row)
    return data



from __future__ import unicode_literals
import math
import frappe
from frappe.utils import cstr, add_days, date_diff, getdate, format_date
from frappe import _, bold
from frappe.utils.csvutils import UnicodeWriter, read_csv_content
from frappe.utils.data import format_date
from frappe.utils.file_manager import get_file
from frappe.model.document import Document
from frappe.utils.background_jobs import enqueue

from datetime import date, timedelta, datetime
import openpyxl
from openpyxl import Workbook


import openpyxl
import xlrd
import re
from openpyxl.styles import Font, Alignment, Border, Side
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
from openpyxl.styles import GradientFill, PatternFill
from six import BytesIO, string_types


@frappe.whitelist()
def download():
    filename = 'cl plan shortage'
    test = build_xlsx_response(filename)

    
# return xlsx file object
def make_xlsx(data, sheet_name=None, wb=None, column_widths=None):
    args = frappe.local.form_dict
    column_widths = column_widths or []
    if wb is None:
        wb = openpyxl.Workbook()
    ws = wb.create_sheet(sheet_name, 0)
    header = add_header(args)
    ws.append(header)

    header = add_day_header(args)
    ws.append(header)

    header = add_header_emp_type(args)
    ws.append(header)
   
    data = get_data(args)

    for row in data:
        ws.append(row)


    ws.sheet_view.zoomScale = 70

    xlsx_file = BytesIO()
    wb.save(xlsx_file)
    return xlsx_file 

def build_xlsx_response(filename):
    xlsx_file = make_xlsx(filename)
    # write out response as a xlsx type
    frappe.response['filename'] = filename + '.xlsx'
    frappe.response['filecontent'] = xlsx_file.getvalue()
    frappe.response['type'] = 'binary'


@frappe.whitelist()
def add_header(args):
    header = ["488"]
    dates = get_dates(args)
    for date in dates:
        # frappe.log_error(message=[date,type(date)])
        date = datetime.strptime(date,'%Y-%m-%d')
        date = date.strftime('%d-%b-%Y')
        header.extend([date,'','','',''])
    header.append('OVERALL TOTAL')
    return header

@frappe.whitelist()
def add_day_header(args):
    day_header = ["488"]
    dates = get_dates(args)
    for date in dates:
        date = datetime.strptime(date,'%Y-%m-%d')
        day = datetime.date(date).strftime('%a')
        day_header.extend([day,'','','',''])
    return day_header

@frappe.whitelist()
def add_header_emp_type(args):
    header = [""]
    emp_type = ['plan','actual','diff','shortage','amount to be debited']
    dates = get_dates(args)
    for i in range(len(dates)):
        header.extend(emp_type)
    header.extend(emp_type)
    return header


@frappe.whitelist()
def get_data(args):
    data = []
    contractors = frappe.get_all('Contractor',{'status':'Active'})
    for contractor in contractors:
        side_sum=0
        side_count=0
        side_diff=0
        side_short=0
        # side_amt=0
        row = [contractor.name]
        dates =get_dates(args)
        for date in dates:
            
            sum=0
            if frappe.db.exists('CL Head Count Plan',{'contractor':contractor.name,'date':date}):
                plan = frappe.get_doc('CL Head Count Plan',{'contractor':contractor.name,'date':date})
                sum = plan.shift_1 + plan.shift_2+plan.shift_3+plan.shift_pp1+plan.shift_pp2
                
            count = frappe.db.sql("""select count(*) as count from `tabQR Checkin` 
                                    left join `tabEmployee` on `tabQR Checkin`.employee = `tabEmployee`.name
                                     where `tabEmployee`.contractor='%s' and  `tabQR Checkin`.shift_date='%s' and ot = 0
                                    """%(contractor.name,date),as_dict=True)
            if count:
                count = count[0].count
            else:
                count = 0
            diff = count - sum
            try:
                shortage = (diff /sum)*100
            except ZeroDivisionError:
                shortage = 0
            float_value = math.floor(shortage)
            short_percent = str(float_value)+'%'
            amt_debt = diff * 488
            row.append(sum)
            row.append(count)
            row.append(diff)
            row.append(short_percent)
            row.append(amt_debt)
            side_sum+=sum
            side_count+=count
            side_diff = side_sum-side_count
            try:
                shortage = (side_diff/side_sum)*100
            except ZeroDivisionError:
                shortage = 0
            side_short+=shortage
            float_value = math.floor(side_short)
            short_percent = str(float_value)+'%'
            amount_debt = side_diff * 488
            # side_amt+=amount_debt
        row.append(side_sum)
        row.append(side_count)
        row.append(side_diff)
        row.append(short_percent)
        row.append(amount_debt)
        data.append(row)
    tot = total(args)
    data.append(tot)
    
    return data

def get_dates(args):
    no_of_days = date_diff(add_days(args.to_date, 1), args.from_date)
    dates = [add_days(args.from_date, i) for i in range(0, no_of_days)]
    return dates

def total(args):
    data = ['TOTAL',]
    dates = get_dates(args)
    contractors_list = []
    contractors = frappe.get_all('Contractor',{'status':'Active'})
    for c in contractors:
        contractors_list.append(c.name)
    row = []
    for date in dates:
        plan_total = 0
        actual_total =0
        diff=0
        short =0
        amount_debt=0
        if frappe.db.exists('CL Head Count Plan',{'contractor':('in',contractors_list),'date':date}):
            plans = frappe.get_all('CL Head Count Plan',{'contractor':('in',contractors_list),'date':date})
            for p in plans:
                shift_1 = 0
                shift_2 = 0
                shift_3 = 0
                shift_pp1 = 0
                shift_pp2 = 0
                plan = frappe.get_doc("CL Head Count Plan",p.name)
                if plan.shift_1:
                    shift_1 = plan.shift_1
                if plan.shift_2:
                    shift_2 = plan.shift_2
                if plan.shift_3:
                    shift_3 = plan.shift_3
                if plan.shift_pp1:
                    shift_pp1 = plan.shift_pp1
                if plan.shift_pp2:
                    shift_pp2 = plan.shift_pp2
                plan_total += shift_1 + shift_2 + shift_3 + shift_pp1 + shift_pp2
            row.append(plan_total)
        count = frappe.db.sql("""select count(*) as count from `tabQR Checkin` 
                                left join `tabEmployee` on `tabQR Checkin`.employee = `tabEmployee`.name
                                    where `tabEmployee`.contractor in %s and  `tabQR Checkin`.shift_date='%s' and ot = 0
                                """%(tuple(contractors_list),date),as_dict=True)
        if count:
            count = count[0].count
        else:
            count = 0
        actual_total+= count        
        row.append(actual_total)
        diff_value =plan_total-actual_total
        diff+=diff_value
        row.append(diff)
        try:
            shortage = (diff /actual_total)*100
        except ZeroDivisionError:
            shortage = 0
        float_value = math.floor(shortage)
        short_percent = str(float_value)+'%'
        row.append(short_percent)
        amt_debt = diff_value*488
        amount_debt+=amt_debt
        row.append(amount_debt)
     
    data.extend(row)
    return data



# return xlsx file object # write out response as a xlsx type # frappe.log_error(message=[date,type(date)]) # side_amt=0 select count(*) as count from `tabQR Checkin`                                      left join `tabEmployee` on `tabQR Checkin`.employee = `tabEmployee`.name                                      where `tabEmployee`.contractor='%s' and  `tabQR Checkin`.shift_date='%s' and ot = 0 # side_amt+=amount_debt select count(*) as count from `tabQR Checkin`                                  left join `tabEmployee` on `tabQR Checkin`.employee = `tabEmployee`.name                                     where `tabEmployee`.contractor in %s and  `tabQR Checkin`.shift_date='%s' and ot = 0