#!/usr/bin/env python
# -*- coding: utf-8 -*-

#  CRON PARA CALCULAR STOCK EN INVENTARIO
#  (c) Juan Rúa - 2016
#  jruacastro@gmail.com.pe

#Modulos y Librerias que se requieren para esta tarea programada

#LIBRERIA PARA CONECTAR A LA BASE DE DATOS
import psycopg2

#LIBRERIA PARA EXPORTACION A EXCEL
import xlsxwriter

#LIBRERIAS PARA ENVIO DE CORREOS
import sys, smtplib, MimeWriter, base64, StringIO
import gettext; _ = gettext.gettext
import time

###QUERYS A CONSULTAR

query_Stock="""select inv_cod_producto,tp.prod_descripcion, inv_cantidad
from tb_inventario ti inner join tb_producto tp on ti.inv_cod_producto = tp.prod_codigo
where tp.prod_tipo = 1 and inv_cantidad < 20"""

###FUNCION DE EXPORTACION QUERY A EXCEL
def exportar_query_excel(r):

        wb = xlsxwriter.Workbook('Stock.xlsx')
        ws = wb.add_worksheet('Stock')

        style0 = wb.add_format()
        style0.set_font_color('black')
        style0.set_bold()
        style0.set_align('center')

        style0.set_left()
        style0.set_top()
        style0.set_right()
        style0.set_bottom()

        #CONTENIDO
        style4 = wb.add_format()
        style4.set_font_color('black')
        style4.set_left()
        style4.set_top()
        style4.set_right()
        style4.set_bottom()

        data = []
        i = 0

        ws.write(1, 0, "Codigo".encode('ascii', 'replace'),style0)
        ws.write(1, 1, "Nombre".encode('ascii', 'replace'),style0)
        ws.write(1, 2, "Stock".encode('ascii', 'replace'),style0)



        #ws.merge_range(0,0,0,12, 'Lista de Productos', style0)

        ws.set_column(0,0,10)
        ws.set_column(1,1,20)
        ws.set_column(2,2,8)

        e = 2

        for i in r:
            i = map(str, i)
            ws.write(e, 0, str(i[0]).encode('ascii', 'replace'),style4)
            ws.write(e, 1, str(i[1]).encode('ascii', 'replace'),style4)
            ws.write(e, 2, str(i[2]).encode('ascii', 'replace'),style4)

            e = e + 1

        wb.close()
        print "workbook"

###FUNCION MAIL
class mail:
    def __init__(self,subject="",body="", tipo="text/plain"):
        self.message = StringIO.StringIO()
        self.writer = MimeWriter.MimeWriter(self.message)
        self.writer.addheader('Subject',subject)
        self.writer.startmultipartbody('mixed')

        # start off with a text/plain part
        part = self.writer.nextpart()
        self.body = part.startbody(tipo)
        self.body.write(body)
    def add_attachment(self, arch = ""):
        # now add an image part
        part = self.writer.nextpart()
        part.addheader('Content-Transfer-Encoding', 'base64')
        self.body = part.startbody('application/pdf')
        base64.encode(open(arch, 'rb'), self.body)

    def add_attachment_xls(self, arch = ""):
        # now add an image part
        part = self.writer.nextpart()
        part.addheader('Content-Transfer-Encoding', 'base64')
        self.body = part.startbody('application/vnd.ms-excel')
        #self.body = part.startbody('application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
        base64.encode(open(arch, 'rb'), self.body)

    def add_attachment_xlsx(self, arch = ""):
        # now add an image part
        part = self.writer.nextpart()
        part.addheader('Content-Transfer-Encoding', 'base64')
        #self.body = part.startbody('application/vnd.ms-excel')
        self.body = part.startbody('application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;name=%s'%(arch))
        base64.encode(open(arch, 'rb'), self.body)

    def add_attachment_doc(self, arch = ""):
        # now add an image part
        part = self.writer.nextpart()
        part.addheader('Content-Transfer-Encoding', 'base64')
        #self.body = part.startbody('application/vnd.ms-excel')
        self.body = part.startbody('application/msword')
        base64.encode(open(arch, 'rb'), self.body)

    def add_attachment_docx(self, arch = ""):
        # now add an image part
        part = self.writer.nextpart()
        part.addheader('Content-Transfer-Encoding', 'base64')
        #self.body = part.startbody('application/vnd.ms-excel')
        self.body = part.startbody('application/vnd.openxmlformats-officedocument.wordprocessingml.document')
        base64.encode(open(arch, 'rb'), self.body)

    def send(self,desde="", para =""):
        # finish off
        if desde  in('', None):
            desde='jruacastro@gmail.com'
        self.writer.lastpart()
        para = para.replace(",",";").split(";")
        para.append(desde)
        # send the mail
        correo_sistema = "jruacastro@gmail.com"
        clave_sistema = '**********'
        smtp = smtplib.SMTP('smtp.gmail.com', 587)
        smtp.ehlo()
        smtp.starttls()
        smtp.ehlo()
        smtp.login(correo_sistema, clave_sistema)
        smtp.sendmail(desde, para, self.message.getvalue())
        smtp.quit()

print "Empezó la secuencia..."
print "..."

try:
    conexion = psycopg2.connect(database="spsac6",user="postgres",password="postgres",host="localhost",port="5432")
    cursor = conexion.cursor()
    cursor.execute(query_Stock)
    record = cursor.fetchall()
    if len(record)==0:
        print "la consulta no contiene datos"
        pass
    else:
        print "consulta con datos"
        # Mostrar la fecha en formato DIA/MES/AÑO
        print (time.strftime("%d/%m/%y"))

        exportar_query_excel(record)
        ###
        #cursor.execute(query_fechas)
        r = cursor.fetchall()

        for j in r:
            tabla_fechas = """<tr>
                                        <td align=center width=20>%s</td>
                                        <td align=center width=50>%s</td>
                                        <td align=center width=20>%s</td>

                                </tr>""" %(j[0], j[1], j[2])


        a = mail('Productos con Stock Minimo', "Lista de productos con stock menor a 20 unidades <br>Documento generado para la Fecha: %s" %time.strftime("%d/%m/%y"), "text/html")
        a.add_attachment_xlsx("Stock.xlsx")

        #cursor.execute("select comun.fn_tt(6,0)")
        correo = cursor.fetchall()
        a.send("jruacastro@gmail.com", "juan_32_36@hotmail.com")

        ###ACTUALIZAMOS LA TABLA OPR.CIERREDIA
        #cursor.execute("update opr.cierredia set fecha_cierre_enviado=fecha_cierre")
        conexion.commit()

    cursor.close()
    conexion.close()
except RuntimeError:
    conexion.rollback()
    print "Se presentaron errores en la ejecucion de las sentencias", sys.exc_info()[0]