#!/usr/bin/env python
#59 23 * * * export https_proxy="http://172.xx.xxx.xxx:3128" && /usr/bin/python3.11 /home/bots/botijo_guardias/botijo_guardias.py -run

import ssl
import urllib.request
import pymsteams
import pandas as pd
import datetime
import time
import statistics
import sys
import re
import csv
from itertools import groupby

ssl._create_default_https_context = ssl._create_unverified_context
outputFormat="csv"
protocol="https"
domain="docs.google.com"
idSpreadsheet="xxxxdkBDwOWjYWw3xxxxxxxxxxxjltULrM-94lWxxxxxK8"
url=protocol+"://"+domain+"/a/google.com/spreadsheets/d/"+idSpreadsheet+"/gviz/tq?tq=select%20*&tqx=out:"+outputFormat
outputFileName="guardias."+outputFormat
pd.options.display.max_rows = 9999
teamsWebhook="https://gfi1.webhook.office.com/webhookb2/730e3sdafasdfadsfasdfasdfsa/IncomingWebhook/8b63esadfdsafasdadsfads50260513/910d3a2a-2a00-4332-ad4d-7f781aasdfsdaf6a9"

def main():
  my = mediaLlamadasGuardiaActualYear()
  mp = mediaLlamadasGuardiaPastYear()
  mt = mediaLlamadasGuardiaTotales()
  llamadasHoy = comprobarLlamadasDiaActual()
  time.sleep(62)
  if len(llamadasHoy) != 0:
    enviarAlCanal("<b>Llamadas Ayer:</b> <br><br>"+llamadasHoy+"<br><b>Datos de llamadas semanales</b><br><br>☎ <b>Media Año Actual:</b> "+my+"<br>☎ <b>Media Año Pasado:</b> "+mp+"<br>☎ <b>Media de todos los Años:</b> "+mt)

def comprobarLlamadasDiaActual():
  getGoogleSpreadsheet()
  currentDateTime = datetime.datetime.now()
  date = currentDateTime.date()
  ActualDay = date.strftime("%d/%m/%Y")
  result = pd.read_csv(outputFileName, usecols=[1,2,3,4,5,6], header=None, index_col=False, skiprows=1)
  result.to_csv(outputFileName)
  llamadas= ""
  with open(outputFileName, 'r') as infile:
    reader = csv.reader(infile)
    for row in reader:
      if ActualDay in row[1]:
        if row[2] != "3" and len(row[2]) != 0:
          if len(row[1]) != 0:
            llamada = "📅 "+row[1]+" a las "+row[2]+" "+row[4]+" llamó a "+row[3]+". Ticket: "+row[5]+" . Motivo: "+row[6]+"<br>"
            llamadas = llamadas + llamada
    return llamadas

def mediaLlamadasGuardiaActualYear():
  getGoogleSpreadsheet()
  currentDateTime = datetime.datetime.now()
  date = currentDateTime.date()
  ActualYear = date.strftime("%Y")
  result = pd.read_csv(outputFileName, usecols=[1,3], header=None, index_col=False, skiprows=1)
  result.to_csv(outputFileName)
  list_=[]
  with open(outputFileName, 'r') as infile:
    reader = csv.reader(infile)
    for row in reader:
      if ActualYear in row[1]:
        if row[2] != "3" and len(row[2]) != 0:
          list_.append(row[2])
  count_dups = [sum(1 for _ in group) for _, group in groupby(list_)]
  meanActualYear = str(statistics.mean(count_dups))
  return meanActualYear

def mediaLlamadasGuardiaPastYear():
  getGoogleSpreadsheet()
  currentDateTime = datetime.datetime.now()
  date = currentDateTime.date()
  ActualYear = date.strftime("%Y")
  lastYear = int(ActualYear)-1
  lastYear = str(lastYear)
  result = pd.read_csv(outputFileName, usecols=[1,3], header=None, index_col=False, skiprows=1)
  result.to_csv(outputFileName)
  list_=[]
  with open(outputFileName, 'r') as infile:
    reader = csv.reader(infile)
    for row in reader:
      if lastYear in row[1]:
        if row[2] != "3" and len(row[2]) != 0:
          list_.append(row[2])
  count_dups = [sum(1 for _ in group) for _, group in groupby(list_)]
  meanActualYear = str(statistics.mean(count_dups))
  return meanActualYear

def mediaLlamadasGuardiaTotales():
  getGoogleSpreadsheet()
  result = pd.read_csv(outputFileName, usecols=[1,3], header=None, index_col=False, skiprows=1)
  result.to_csv(outputFileName)
  list_=[]
  with open(outputFileName, 'r') as infile:
    reader = csv.reader(infile)
    for row in reader:
      if row[2] != "3" and len(row[2]) != 0:
        list_.append(row[2])
  count_dups = [sum(1 for _ in group) for _, group in groupby(list_)]
  meanTotal = str(statistics.mean(count_dups))
  return meanTotal

def enviarAlCanal(msg):
  myTeamsMessage = pymsteams.connectorcard(teamsWebhook)
  myTeamsMessage.text(msg)
  myTeamsMessage.send()
  time.sleep(0.5)

def getGoogleSpreadsheet():
  urllib.request.urlretrieve(url, outputFileName)

for arg in sys.argv:
  if arg == "-run": main()