import json

from ConfigParser import SafeConfigParser

from simple_salesforce import Salesforce

import openpyxl

from openpyxl.styles import PatternFill, Border, Side, Font

import dateutil.parser

from pytz import timezone

import pytz

from datetime import datetime, timedelta

### Personal Credentials

parser = SafeConfigParser()

parser.read('salesforce.conf')

sf_url = parser.get('SalesForce', 'url')

sf_usr = parser.get('SalesForce', 'username')

sf_pwd = parser.get('SalesForce', 'password')

sf_tkn = parser.get('SalesForce', 'token')

sf_account = parser.get('SalesForce', 'account')

sf_cloud = parser.get('SalesForce', 'cloud')

last_time = parser.get('SalesForce', 'last_time')

now = parser.get('SalesForce', 'now')

tz = parser.get('SalesForce', 'tz')

duration = int(parser.get('SalesForce', 'duration'))

report_file_name = parser.get('SalesForce', 'report_file_name')

### L1 Variables

font_size=10

default_font = Font(name="Open Sans",size=font_size)

defautl_font_hyperlink = Font(name="Open Sans",size=font_size, underline="single", color='4169e1')

defautl_font_bold = Font(name="Open Sans",size=font_size, bold=True)

header_color = '87cefa'

sev_colors = {

}

border = Border(

)

ticket_fields = [

]

query_field = [

]

### Collect data

sf = Salesforce(instance_url=sf_url, username=sf_usr, password=sf_pwd, security_token=sf_tkn)

record_types = sf.query("SELECT ID,NAME from RecordType")['records']

customers = sf.query("SELECT ID,NAME from User where AccountId = '%s'" % sf_account)['records']

cases = sf.query("SELECT %s from Case where %s"

% ( ','.join(ticket_fields),

' and '.join(query_field) ))['records']

for record_type in record_types:

if record_type["Name"] == "Technical Case":

tech_case_id = record_type["Id"]

if record_type["Name"] == "Change Request":

change_request_id = record_type["Id"]

if record_type["Name"] == "Closed Case":

closed_case_id = record_type["Id"]

customer_ids = []

for customer in customers:

customer_ids.append(customer['Id'])

### L2 Definition

summary_logic = [

]

summary_page = [

]

solved_tech_cases = [

]

open_tech_cases = [

]

completed_change_requests = [

]

open_change_requests = [

]

#violated_sla_cases = [

# [

# "Case Number",

# "Severity",

# "Created By Customer",

# "Escalated",

# "Created",

# "Closed",

# #"Resolution Time",

# "Subject",

# "Reason for Late resolution"

# ]

#]

## Create CSV

for i in range(len(summary_logic)):

summary=summary_logic[i]["name"]

row=[summary]

for date in [ now, last_time ]:

time_array=[ "%s %s" % (summary_logic[i]["time_query"][0],date) ]

if summary_logic[i]["duration"] > 0:

duration_date=dateutil.parser.parse(date) - timedelta(days=summary_logic[i]["duration"])

time_array.append("%s %s" % (summary_logic[i]["time_query"][1],

duration_date.strftime('%Y-%m-%dT%H:%M:%S%z') ))

query_string = "SELECT %s from Case where %s and %s" % (

summary_logic[i]["logic"] ,

' and '.join(summary_logic[i]["base_query"]) ,

' and '.join(time_array)

)

if summary_logic[i]["result"] == "totalSize":

row.append( sf.query(query_string)['totalSize'])

else:

row.append( sf.query(query_string)['records'][0][summary_logic[i]["result"]])

try:

row.append(row[1] - row[2])

except:

row.append(0)

summary_page.append(row)

for case in cases:

for record_type in record_types:

if record_type["Id"] == case["RecordTypeId"]:

if ( record_type["Name"] == "Technical Case" or record_type["Name"] == "Closed Case" ) and ( case['Status'] == 'Solved' or case['Status'] == 'Closed'):

if any(customer['Id'] == case["CreatedById"] for customer in customers):

isCustomerCreated = True

else:

isCustomerCreated = False

row = [ case['CaseNumber'], case['Severity_Level__c'], case['ParentId'], #case['Resolution_Time_is_violated__c'],

case['Customer_Wait_Time_in_Hours__c'],

isCustomerCreated, case['Id'], case['CreatedDate'], case['ClosedDate'], case['Subject'] ]

solved_tech_cases.append(row)

#if case['Resolution_Time_is_violated__c'] == True:

# row = [ case['CaseNumber'], case['Severity_Level__c'], isCustomerCreated, case['L2__c'], case['CreatedDate'], case['ClosedDate'], # case['Resolution_Time_DDHHMM__c'],

# case['Subject'] , "" ]

# violated_sla_cases.append(row)

if record_type["Name"] == "Technical Case" and case['Status'] != 'Solved':

if any(customer['Id'] == case["CreatedById"] for customer in customers):

isCustomerCreated = True

else:

isCustomerCreated = False

row = [ case['CaseNumber'], case['Status'], case['Severity_Level__c'], #case['Resolution_Time_is_violated__c'],

#case['Customer_Wait_Time_in_Hours__c'],

isCustomerCreated, case['L2__c'], case['Id'], case['CreatedDate'], case['Subject'] ]

open_tech_cases.append(row)

if record_type["Name"] == "Change Request" and case['Status'] == 'Completed':

maint_start = case['CreatedDate']

maint_end = case['ClosedDate']

for sub_case in cases:

if sub_case['Id'] == case['Maintenance_Window_Link__c']:

maint_start = sub_case['MW_Start__c']

maint_end = sub_case['MW_Actual_End__c']

break

row = [ case['CaseNumber'], case['Case_Link__c'], case['Implemented_Result__c'], maint_start, maint_end, case['Subject'] ]

completed_change_requests.append(row)

if record_type["Name"] == "Change Request" and case['Status'] != 'Completed':

row = [ case['CaseNumber'], case['Case_Link__c'], case['Status'], case['Risk_Level__c'], case['CreatedDate'], case['Subject'] ]

open_change_requests.append(row)

formated_cases = [

]

## Create Excel

wb = openpyxl.Workbook()

for sheet_num in range(len(formated_cases)):

case_type = formated_cases[sheet_num]["name"]

wb.create_sheet(case_type, sheet_num)

wb.active = sheet_num

ws = wb.active

for i in range(len(formated_cases[sheet_num]["csv"])):

for j in range(len(formated_cases[sheet_num]["csv"][i])):

try:

convert_date = dateutil.parser.parse(formated_cases[sheet_num]["csv"][i][j])

ws.cell(row=i+1, column=j+1).value = convert_date.astimezone(timezone(tz))

except:

ws.cell(row=i+1, column=j+1).value=formated_cases[sheet_num]["csv"][i][j]

ws.cell(row=i+1, column=j+1).border=border

ws.cell(row=i+1, column=j+1).font=default_font

if i == 0:

ws.cell(row=i+1, column=j+1).fill = PatternFill(fill_type='solid', fgColor=header_color)

ws.cell(row=i+1, column=j+1).font= defautl_font_bold

if i != 0 and formated_cases[sheet_num]["csv"][0][j] == "Severity":

ws.cell(row=i+1, column=j+1).fill = PatternFill(fill_type='solid', fgColor=sev_colors[formated_cases[sheet_num]["csv"][i][j].split("Sev ")[1]])

if i != 0 and formated_cases[sheet_num]["csv"][0][j] == "SLA Violated" and formated_cases[sheet_num]["csv"][i][j] == True:

ws.cell(row=i+1, column=j+1).fill = PatternFill(fill_type='solid', fgColor=sev_colors["1"])

if i != 0 and formated_cases[sheet_num]["csv"][0][j] == "Title":

ws.cell(row=i+1, column=j+1).fill = PatternFill(fill_type='solid', fgColor=header_color)

if i != 0 and formated_cases[sheet_num]["csv"][0][j] == "Risk":

if formated_cases[sheet_num]["csv"][i][j] == "High":

ws.cell(row=i+1, column=j+1).fill = PatternFill(fill_type='solid', fgColor=sev_colors["1"])

if formated_cases[sheet_num]["csv"][i][j] == "Medium":

ws.cell(row=i+1, column=j+1).fill = PatternFill(fill_type='solid', fgColor=sev_colors["2"])

if formated_cases[sheet_num]["csv"][i][j] == "Low":

ws.cell(row=i+1, column=j+1).fill = PatternFill(fill_type='solid', fgColor=sev_colors["3"])

if i != 0 and formated_cases[sheet_num]["csv"][0][j] == "Resolution Time":

if formated_cases[sheet_num]["csv"][i][1] == "Sev 1":

ws.cell(row=i+1, column=j+1).fill = PatternFill(fill_type='solid', fgColor=sev_colors["1"])

if formated_cases[sheet_num]["csv"][i][1] == "Sev 2" and formated_cases[sheet_num]["csv"][i][j] > 24:

ws.cell(row=i+1, column=j+1).fill = PatternFill(fill_type='solid', fgColor=sev_colors["3"])

if formated_cases[sheet_num]["csv"][i][1] == "Sev 3" and formated_cases[sheet_num]["csv"][i][j] > 120:

ws.cell(row=i+1, column=j+1).fill = PatternFill(fill_type='solid', fgColor=sev_colors["3"])

if formated_cases[sheet_num]["csv"][i][1] == "Sev 4" and formated_cases[sheet_num]["csv"][i][j] > 240:

ws.cell(row=i+1, column=j+1).fill = PatternFill(fill_type='solid', fgColor=sev_colors["3"])

if formated_cases[sheet_num]["csv"][i][2] != None:

ws.cell(row=i+1, column=j+1).fill = PatternFill(fill_type='solid', fgColor="808080")

if i != 0 and formated_cases[sheet_num]["csv"][0][j] == "Merged To" and formated_cases[sheet_num]["csv"][i][j] != None:

value=sf.query("SELECT CaseNumber from Case where Id='%s'"% formated_cases[sheet_num]["csv"][i][j])['records'][0]['CaseNumber']

ws.cell(row=i+1, column=j+1).value=value

if i != 0 and formated_cases[sheet_num]["csv"][0][j] == "Change Request":

try:

value=sf.query("SELECT CaseNumber from Case where Environment2__c = '%s' and RecordTypeId = '%s' and Case_Link__c ='%s'"% ( sf_cloud, change_request_id, formated_cases[sheet_num]["csv"][i][j]))['records'][0]['CaseNumber']

ws.cell(row=i+1, column=j+1).value=value

except:

ws.cell(row=i+1, column=j+1).value=""

if j == 0 and i != 0:

for case in cases:

if case['CaseNumber'] == formated_cases[sheet_num]["csv"][i][j]:

ws.cell(row=i+1, column=j+1).value="=HYPERLINK(\"%s\",\"%s\")" % ( case['URL__c'] , formated_cases[sheet_num]["csv"][i][j] )

ws.cell(row=i+1, column=j+1).font= defautl_font_hyperlink

break

if i != 0 and formated_cases[sheet_num]["csv"][0][j] == "Parent Case":

try:

value=sf.query("SELECT CaseNumber from Case where Id='%s'"% formated_cases[sheet_num]["csv"][i][j])['records'][0]['CaseNumber']

ws.cell(row=i+1, column=j+1).value=value

except:

ws.cell(row=i+1, column=j+1).value=""

#for case in cases:

# if case['Id'] == formated_cases[sheet_num]["csv"][i][j]:

# ws.cell(row=i+1, column=j+1).value=case['CaseNumber']

# break

if type(formated_cases[sheet_num]["csv"][i][j]) is bool:

if formated_cases[sheet_num]["csv"][i][j] == True:

ws.cell(row=i+1, column=j+1).value="Y"

else:

ws.cell(row=i+1, column=j+1).value=""

if isinstance(formated_cases[sheet_num]["csv"][i][j],float):

ws.cell(row=i+1, column=j+1).value=round(formated_cases[sheet_num]["csv"][i][j],2)

dims = {}

i=0

for row in ws.rows:

j=0

for cell in row:

if cell.value:

dims[cell.column] = max((dims.get(cell.column, 0), len(str(formated_cases[sheet_num]["csv"][i][j]))))

j+=1

i+=1

for col, value in dims.items():

ws.column_dimensions[col].width = value

ws.active = 0

for sheet in wb:

if sheet.title == 'summary_page':

sheet.sheet_view.tabSelected = True

else:

sheet.sheet_view.tabSelected = False

wb.save(report_file_name)