# ERPNext - web based ERP (http://erpnext.com)
# Copyright (C) 2012 Web Notes Technologies Pvt Ltd
# 
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
# 
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
# 
# You should have received a copy of the GNU General Public License
# along with this program.  If not, see <http://www.gnu.org/licenses/>.
# validate Filters
from __future__ import unicode_literals
flt_dict = {'fiscal_year': 'Fiscal Year', 'period': 'Period', 'under' : 'Under', 'sales_person':'Sales Person', 'target_on':'Target On'}
for f in flt_dict:
  if not filter_values.get(f):
    msgprint("Please Select " + cstr(flt_dict[f]))
    raise Exception

# Get Values from fliters
fiscal_year = filter_values.get('fiscal_year')[0]
period = filter_values.get('period')
under = filter_values.get('under')
sales_person = filter_values.get('sales_person')
target_on = filter_values.get('target_on')


# set colnames
for d in ['Item Group', 'Total Target Allocated', 'Distribution Id']:
  colnames.append(d)
  coltypes.append('Data')
  colwidths.append('150px')
  coloptions.append('')
  col_idx[d] = len(colnames) - 1
	


# Set required field names 
based_on_fn = 'sales_person'

date_fn  = (under == 'Sales Order' ) and 'transaction_date' or 'posting_date' 

mon_list = []

data = {'start_date':0, 'end_date':1}

def make_month_list(append_colnames, start_date, mon_list, period, colnames, coltypes, colwidths, coloptions, col_idx):
  count = 1
  if period == 'Quarterly' or period == 'Half Yearly' or period == 'Annual': mon_list.append([str(start_date)])
  for m in range(12):
    # get last date
    last_date = str(sql("select LAST_DAY('%s')" % start_date)[0][0])
    
    # make mon_list for Monthly Period
    if period == 'Monthly' :
      mon_list.append([start_date, last_date])
      # add months as Column names
      month_name = sql("select MONTHNAME('%s')" % start_date)[0][0]
      append_colnames(str(month_name)[:3], colnames, coltypes, colwidths, coloptions, col_idx)
      
    # get start date
    start_date = str(sql("select DATE_ADD('%s',INTERVAL 1 DAY)" % last_date)[0][0])
    
    # make mon_list for Quaterly Period
    if period == 'Quarterly' and count % 3 == 0: 
      mon_list[len(mon_list) - 1 ].append(last_date)
      # add Column names
      append_colnames('Q '+ str(count / 3), colnames, coltypes, colwidths, coloptions, col_idx)
      if count != 12: mon_list.append([start_date])
    
    # make mon_list for Half Yearly Period
    if period == 'Half Yearly' and count % 6 == 0 :
      mon_list[len(mon_list) - 1 ].append(last_date)
      # add Column Names
      append_colnames('H'+str(count / 6), colnames, coltypes, colwidths, coloptions, col_idx)
      if count != 12: mon_list.append([start_date])

    # make mon_list for Annual Period
    if period == 'Annual' and count % 12 == 0:
      mon_list[len(mon_list) - 1 ].append(last_date)
      # add Column Names
      append_colnames('', colnames, coltypes, colwidths, coloptions, col_idx)
    count = count +1

def append_colnames(name, colnames, coltypes, colwidths, coloptions, col_idx):
  col = ['Target', 'Actual', 'Variance']
  for c in col:
    n = str(name) and ' (' + str(name) +')' or ''
    colnames.append(str(c) + n )
    coltypes.append('Currency')
    colwidths.append('150px')
    coloptions.append('')
    col_idx[str(c) + n ] = len(colnames) - 1




# get start date
start_date = webnotes.conn.get_value('Fiscal Year', fiscal_year, 'year_start_date')
if not start_date:
  msgprint("Please Define Year Start Date for Fiscal Year " + str(fiscal_year))
  raise Exception
start_date = start_date.strftime('%Y-%m-%d')

# make month list and columns
make_month_list(append_colnames, start_date, mon_list, period, colnames, coltypes, colwidths, coloptions, col_idx)


bc_obj = get_obj('Budget Control')
for r in res:

  count = 0

  for idx in range(3, len(colnames), 3):

    cidx = 2
    # ================= Calculate Target ==========================================
    r.append(bc_obj.get_monthly_budget(r[cidx], fiscal_year, mon_list[count][data['start_date']], mon_list[count][data['end_date']], r[cidx-1]))

    #================== Actual Amount =============================================
    actual = 0


    #----------------------------------------------------------    
    if target_on == "Quantity":

      actual = sql("select sum(ifnull(t2.qty,0) * ifnull(t3.allocated_percentage,0) / 100) from `tab%s` t1, `tab%s Item` t2, `tabSales Team` t3 where t2.parent = t1.name and t3.parent = t1.name and t3.%s = '%s' and t2.item_group = '%s' and t1.docstatus = 1 and t1.%s between '%s' and '%s' "%(under, under, based_on_fn, sales_person, r[0].strip(), date_fn, mon_list[count][data['start_date']], mon_list[count][data['end_date']]))
    
    #----------------------------------------------------------  
    if target_on == "Amount":

      actual = sql("select sum(ifnull(t2.amount,0) * ifnull(t3.allocated_percentage,0) / 100) from `tab%s` t1, `tab%s Item` t2, `tabSales Team` t3 where t2.parent = t1.name and t3.parent = t1.name and t3.%s = '%s' and t2.item_group = '%s' and t1.docstatus = 1 and t1.%s between '%s' and '%s' "%(under, under, based_on_fn, sales_person, r[0].strip(), date_fn, mon_list[count][data['start_date']], mon_list[count][data['end_date']]))
    #----------------------------------------------------------

    actual = actual and flt(actual[0][0]) or 0 
    r.append(actual)
    # ================ Variance ===================================================

    r.append(r[idx] - r[idx + 1])

    count = count +1
