#as of 20AUG2019
#version that has stand dev helper due to unix issues
#version 2 writes proper format; removes exteraneous rows; all stat functions hardcoded

from __future__ import print_function
import os
import sys
import random
from datetime import *
from openpyxl import *
import math


#import statistics as st
#import numpy



#Global VARS
sheet1 = 'RCC Operations 2019'
sheet2 = 'RCC C4IM Services 2019'
sheet3 = 'RCC Administrative 2019'



    #vars for telling which rows are meaningful might need to find better way to do this
appOpsRow = range(7, 236,1)
appC4Row = range(7, 191,1)
appADRow = range(7, 230,1)


calcDict = {}
writeDict = {}

#Helper functions

def combineDicts(dictList):
    newDict = {}
    for Dict in dictList:
        for keys in Dict:
            newDict[keys] = Dict[keys]
    return newDict

#returns days in month IOT to populate matrix for existing days
def getDaysinMonth():
	now = datetime.now()
	return ((date(now.year, now.month+1, 1) - date(now.year, now.month, 1)).days)

def roundQuarter(num):
    return math.ceil(num *4) /4

#############################################
#standard deviation helpers
def mean(data):
	n = len(data)
	if n < 1:
		raise ValueError('mean requires at least one datapoint')
	return sum(data)/n

def _ss(data):
	#Return sum of square deviation of sequence d_%m_%y
	c = mean(data)
	ss = sum((x-c)**2 for x in data)
	return ss

def stddev(data, ddof = 0):
	#Calc pop stand dev by default; specify ddof for sample stand dev
	n = len(data)
	if n < 2:
		raise ValueError ('variance requires at least two data points')
	ss = _ss(data)
	pvar = ss/(n-ddof)
	return pvar**0.5
    
def median(lst):
    n = len(lst)
    s = sorted(lst)
    return (sum(s[n//2-1:n//2+1])/2.0, s[n//2])[n % 2] if n else None
#############################################



#Working Functions

# Get list of all personnel with hours logged in folders in cwd
def getPax(dir):
    paxholder = []
    for root, dirs, files in os.walk(dir):
        for x in dirs:
			paxholder.append(x)
    return paxholder



#seed Return flat dict from master spreadsheet document
def seedDict(masterExcel):
    currDict = {}
    try:
        wb = load_workbook(filename=masterExcel)
    except:
        print("Error loading sheet")
    ws = wb.get_sheet_by_name(sheet1)
    for row in appOpsRow:
        currKey = ws['G'+str(row)].value
        currDict[currKey] = [[],[],[]]
    ws = wb.get_sheet_by_name(sheet2)
    for row in appC4Row:
        currKey = ws['G'+str(row)].value
        currDict[currKey] = [[],[],[]]
    ws = wb.get_sheet_by_name(sheet3)
    for row in appADRow:
        currKey = ws['H'+str(row)].value
        currDict[currKey] = [[],[],[]]
    return currDict


#Get data from one spreadsheet, flattens into single dict and returns
def getDayData(excel):
	try :
		wb = load_workbook(filename = excel)
	except:
		print("Error loading sheet")
	#prep containers and sheet data
	Ops = {}
	C4IM = {}
	Admin = {}

	#retrieve data from Ops sheet	
	#construct list containing rows in sheet to get data from

	ws = wb.get_sheet_by_name(sheet1)
	for x in appOpsRow:
		currkey = ws['G'+str(x)].value
		currdata = [ ws['Z'+ str(x)].value , ws['AD'+ str(x)].value, ws['AH'+ str(x)].value ]

		Ops[currkey] = currdata

	#retrieve data from C4IM sheet
	ws = wb.get_sheet_by_name(sheet2)
	for x in appC4Row:
		currkey = ws['G'+str(x)].value
		currdata = [ ws['Z'+ str(x)].value , ws['AH'+ str(x)].value , ws['AN'+ str(x)].value ]

		C4IM[currkey] = currdata
	#print (C4IM['700.4.4.1  Operate and maintain web monitoring and filtering systems IAW applicable laws and regulations'])
	#retrieve data from Admin sheet
	ws = wb.get_sheet_by_name(sheet3)
	for x in appADRow:
		currkey = ws['H'+str(x)].value
		currdata = [ ws['N'+ str(x)].value , ws['T'+ str(x)].value , ws['V'+ str(x)].value ]

		Admin[currkey] = currdata
	#print (Admin['Monitor & submit award recommendations; Initiate & route non-competitive actions via DCPDS and via AUTO-NOA for competitive actions to G1'])
	retDict = combineDicts([Ops, C4IM, Admin])
	return retDict


#Get everyday's data from an individual (designated by existing folder)	
#Takes a folder returns an array with an array containing 3x dictionary; 1 for every worksheet
def getDaysinFolder(folder):
	dayList=[]
	currfolder = ('./Submissions/'+folder+'/')
	for root,dirs, files in os.walk(currfolder):
		for x in files:
			dayList.append(getDayData('./Submissions/'+folder+'/'+x))
	return dayList



def populateAnalysisDict():
    #seed Dict
    paxsubmissionList = []
    currDict = seedDict('./RCC_Manpowerstudy-AllMaster.xlsx')
    pax = getPax('./Submissions')
    for person in pax:
        paxsubmissionList = paxsubmissionList + getDaysinFolder(person)

    for dayDict in paxsubmissionList:
        for task in dayDict:
            #print (type(task))
            if dayDict[task][0] == None:
                currDict[task][0].append(0)
            else:
                currDict[task][0].append(float(dayDict[task][0]))
            if dayDict[task][1] == None:
                currDict[task][1].append(0)
            else:
                currDict[task][1].append(float(dayDict[task][1]))
            if dayDict[task][2] == None:
                currDict[task][2].append(0)
            else:
                currDict[task][2].append(float(dayDict[task][2]))
    return currDict



#write out to excel document takes excel doc and dictionary
def outputExcel(dict, excelDoc, refExcel):
    try:
        wb = load_workbook(filename=excelDoc)
        refDoc = load_workbook(filename = refExcel)
    except:
        print("Error loading sheet")

########################################################
##########Update HERE###################################
########################################################
##DCO Division##DODIN Operations Division##Sustainment Division##Theater Operations Division 

    Div = 'Theater Operations Division'
    Month = 'July'
    POC = 'MAJ Sanchez, Simon, simon.d.sanchez.mil@mail.mil, 314-565-6902'
    

    ###OPS Sheet#####
    writesht = wb.get_sheet_by_name(sheet1)
    refsheet = refDoc.get_sheet_by_name(sheet1)
    #seed for writesheet row itterator
    counter = 1
    
    for row in refsheet.iter_rows():
        if row[6].value in dict:
            writesht['A'+str(counter)].value = Div
            writesht['B'+str(counter)].value = Month
            writesht['C'+str(counter)].value = POC
            
            writesht['D'+str(counter)].value = row[4].value
            writesht['E'+str(counter)].value = row[5].value
            writesht['F'+str(counter)].value = row[6].value

            writesht['L'+str(counter)].value = 'Y'
            writesht['M'+str(counter)].value = 'Y'
            writesht['N'+str(counter)].value = 'N'
            writesht['O'+str(counter)].value = 'N/A'
            
            freqHolder = dict[row[6].value][0][1] + dict[row[6].value][1][1] + dict[row[6].value][2][1]
            
            writesht['P'+str(counter)].value = freqHolder
            writesht['Q'+str(counter)].value = freqHolder
            
            writesht['R'+str(counter)].value = 'Advanced'
            
            writesht['Y'+str(counter)].value = dict[row[6].value][0][0]
            writesht['Z' + str(counter)].value = dict[row[6].value][0][1]
            writesht['AC' + str(counter)].value = dict[row[6].value][1][0]
            writesht['AD' + str(counter)].value = dict[row[6].value][1][1]
            writesht['AG' + str(counter)].value = dict[row[6].value][2][0]
            writesht['AH' + str(counter)].value = dict[row[6].value][2][1]
            
            #print("freqhold", freqHolder)
            #print(row[6].value)
            #print(dict[row[6].value])
            counter += 1
    
    ###C4IM Sheet#####
    writesht = wb.get_sheet_by_name(sheet2)
    refsheet = refDoc.get_sheet_by_name(sheet2)
    #seed for writesheet row itterator
    counter = 1
    
    for row in refsheet.iter_rows():

        if row[6].value in dict:
            writesht['A'+str(counter)].value = Div
            writesht['B'+str(counter)].value = Month
            writesht['C'+str(counter)].value = POC
            
            writesht['D'+str(counter)].value = row[4].value
            writesht['E'+str(counter)].value = row[5].value
            writesht['F'+str(counter)].value = row[6].value

            writesht['L'+str(counter)].value = 'Y'
            writesht['M'+str(counter)].value = 'Y'
            writesht['N'+str(counter)].value = 'N'
            writesht['O'+str(counter)].value = 'N/A'

            freqHolder = dict[row[6].value][0][1] + dict[row[6].value][1][1] + dict[row[6].value][2][1]
            
            writesht['P'+str(counter)].value = freqHolder
            writesht['Q'+str(counter)].value = freqHolder
            
            writesht['R'+str(counter)].value = 'Advanced'
            
            writesht['Y'+str(counter)].value = dict[row[6].value][0][0]
            writesht['Z' + str(counter)].value = dict[row[6].value][0][1]
            writesht['AC' + str(counter)].value = dict[row[6].value][1][0]
            writesht['AD' + str(counter)].value = dict[row[6].value][1][1]
            writesht['AG' + str(counter)].value = dict[row[6].value][2][0]
            writesht['AH' + str(counter)].value = dict[row[6].value][2][1]
            
            # print(row[6].value)
            # print(dict[row[6].value])
            counter += 1

    ####Admin####
    writesht = wb.get_sheet_by_name(sheet3)
    refsheet = refDoc.get_sheet_by_name(sheet3)
    #seed for writesheet row itterator
    counter = 1

    for row in refsheet.iter_rows():
        #print (row[7].value)
        if row[7].value in dict:
            writesht['A' + str(counter)].value = Div
            writesht['B' + str(counter)].value = Month
            writesht['C' + str(counter)].value = POC

            writesht['D' + str(counter)].value = row[4].value
            writesht['E' + str(counter)].value = row[5].value
            writesht['F' + str(counter)].value = row[6].value
            writesht['G' + str(counter)].value = row[7].value

            freqHolder = dict[row[7].value][0][1] + dict[row[7].value][1][1] + dict[row[7].value][2][1]
            writesht['J' + str(counter)].value = freqHolder
            writesht['K' + str(counter)].value = freqHolder

            writesht['L' + str(counter)].value = 'Advanced'

            # required for this sheet?
            # writesht['L' + str(counter)].value = 'Y'
            # writesht['M' + str(counter)].value = 'Y'
            # writesht['N' + str(counter)].value = 'N'
            # writesht['O' + str(counter)].value = 'N/A'

            writesht['M' + str(counter)].value = dict[row[7].value][0][0]
            writesht['N' + str(counter)].value = dict[row[7].value][0][1]
            writesht['S' + str(counter)].value = dict[row[7].value][1][0]
            writesht['T' + str(counter)].value = dict[row[7].value][1][1]
            writesht['U' + str(counter)].value = dict[row[7].value][2][0]
            writesht['V' + str(counter)].value = dict[row[7].value][2][1]

    wb.save(filename=excelDoc)
 
    return 0

def main():
    d = datetime.today()
    print ("Running")
    f = open('manpowerLog_'+ d.strftime("%d_%m_%y") +'.txt', "w")
    writeDict = {} #seedDict('./RCC_Manpowerstudy-AllMaster.xlsx')
    calcDict = populateAnalysisDict()

    TotHours = 0
    TotFreq = 0
    TaskCount = 0


    bufferDict = {}
    for task in calcDict:
        if (sum(calcDict[task][0]) + sum(calcDict[task][1]) + sum(calcDict[task][2]) != 0) and (calcDict[task] != [[],[], []]):
            #print("true - ", (calcDict[task][0] + calcDict[task][1] + calcDict[task][2]))
            bufferDict[task] = calcDict[task]
    calcDict=bufferDict

    # for x in calcDict:
        # print (x, calcDict[x])
        
    ####################SEED WRITE DISK####################
    for task in calcDict:
        writeDict[task] = [[],[],[]]

    for task in calcDict:
        #mean + random num between 0 and 1 stdev * 30 for each group

        mean0 = median(calcDict[task][0])
        mean1 = median(calcDict[task][1])
        mean2 = median(calcDict[task][2])


        stdev0 = stddev(calcDict[task][0])
        stdev1 = stddev(calcDict[task][1])
        stdev2 = stddev(calcDict[task][2])

        stddevmult = 9

        randadd0 = random.uniform(0, stdev0*stddevmult)
        randadd1 = random.uniform(0, stdev1*stddevmult)
        randadd2 = random.uniform(0, stdev2*stddevmult)

        subHours0 = roundQuarter((mean0+randadd0) * getDaysinMonth())
        subHours1 = roundQuarter((mean1+randadd1) * getDaysinMonth())
        subHours2 = roundQuarter((mean2+randadd2) * getDaysinMonth())

        freq0 = 0
        freq1 = 0
        freq2 = 0

        for entry in calcDict[task][0]:
            if entry > 0:
                freq0 += 1

        for entry in calcDict[task][1]:
            if entry > 0:
                freq1 += 1

        for entry in calcDict[task][2]:
            if entry > 0:
                freq2 += 1

        TotHours += subHours0 + subHours1 + subHours2
        TotFreq += freq0 + freq1 + freq2
        TaskCount += 1

        print (task, calcDict[task])
        print ("mean: ", mean0, mean1, mean2)
        print ("stddev: ", stdev0,stdev1, stdev2)
        print ("randadd: ", randadd0, randadd1, randadd2)
        print ("submitted hours: ", subHours0, subHours1, subHours2)
        print ("Total hours (cumulative): ", TotHours, "Total Freq (raw): ", TotFreq)
        print ("FTEs recorded = ", TotHours * .0069) 
        



        writeDict[task][0].append(subHours0)
        writeDict[task][0].append(freq0 * 5)
        writeDict[task][1].append(subHours1)
        writeDict[task][1].append(freq1*5)
        writeDict[task][2].append(subHours2)
        writeDict[task][2].append(freq2*5)



    outputExcel(writeDict, 'RCC_Manpowerstudy_Submission.xlsx', 'RCC_Manpowerstudy-AllMaster.xlsx')
    return 0




if __name__ == '__main__':
    print ("running")
    main()


