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

import cx_Oracle
import logging
import os
from datetime import datetime
from datetime import timedelta
import xlsxwriter
import smtplib
import base64
import StringIO
import os

os.environ['NLS_LANG'] = "RUSSIAN.AL32UTF8"
LOG_FILENAME='report_service.log'
logging.basicConfig(filename=LOG_FILENAME,level=logging.DEBUG)
logging.info("started at: " + str(datetime.now()))

sqlquery = """
WITH acc_bals AS
  (SELECT VC_CODE,
    N_ACCOUNT_ID,
    N_SUBJECT_ID,
    SI_USERS_PKG_S.GET_ACCOUNT_BALANCE_SUM(N_ACCOUNT_ID) N_BAL
  FROM SI_V_SUBJ_ACCOUNTS
  WHERE N_ACCOUNT_TYPE_ID = '2042'
  ),
  raw_tbl AS
  (SELECT SS.VC_SUBJ_NAME VC_SUBJ,
    acc_bals.VC_CODE VC_ACC,
    UG.VC_DOC_CODE VC_DOC,
    UG.VC_GOOD_NAME VC_GOOD,
    SC.N_SUM N_SUM,
    acc_bals.N_BAL N_BAL,
    PT.D_TIME D_PAY,
    TRUNC(SYSDATE) - TRUNC(PT.D_TIME) N_DAYS,
    FLOOR(MONTHS_BETWEEN(SYSDATE, PT.D_TIME)) N_MONTH,
    SI_ADDRESSES_PKG_S.GET_ADDR_LIST_STR_BY_SUBJ(SS.N_SUBJECT_ID, 13006, vch_VC_SEPARATOR => '; ') VC_TEL,
    SA.N_REGION_ID,
    U.VC_TAGS VC_TAGS
  FROM SI_V_USER_GOODS UG,
    SI_V_USERS U,
    si_v_subjects SS,
    SI_V_SUBJECT_BIND_GROUPS SG,
    acc_bals,
    SD_V_PAYMENTS_T PT,
    SD_V_INVOICES_C SC,
    SD_V_INVOICES_T ST,
    SI_V_OBJ_ADDRESSES SA
  WHERE (1                   =1)
  AND SG.N_SUBJECT_ID        = U.N_SUBJECT_ID
  AND SG.N_SUBJ_GROUP_ID    IN ('2325492801','2325492901')
  AND SA.N_OBJECT_ID         = UG.N_OBJECT_ID
  AND SA.N_ADDR_TYPE_ID      = '1006'
  AND SA.N_OBJ_ADDR_TYPE_ID  = '6016'
  AND SA.C_FL_ACTUAL         = 'Y'
  AND SC.N_SUBJ_GOOD_ID      = UG.N_SUBJ_GOOD_ID
  AND ST.N_DOC_ID            = SC.N_DOC_ID
  AND ST.N_DOC_STATE_ID      = '4003'
  AND acc_bals.N_BAL         < 0
  AND acc_bals.N_SUBJECT_ID  = U.N_SUBJECT_ID
  AND SS.N_SUBJECT_ID        = U.N_BASE_SUBJECT_ID
  AND U.N_SUBJ_STATE_ID     IN('2011','3011')
  AND UG.N_SUBJECT_ID        = acc_bals.N_SUBJECT_ID
  AND UG.C_FL_CLOSED         = 'N'
  AND UG.N_PAR_SUBJ_GOOD_ID IS NULL
  AND PT.N_FORWHO_SUBJECT_ID = U.N_SUBJECT_ID
  AND PT.D_TIME              =
    (SELECT MAX(D_TIME)
    FROM SD_V_PAYMENTS_T
    WHERE N_FORWHO_ACCOUNT_ID = acc_bals.N_ACCOUNT_ID
    AND N_DOC_TYPE_ID        IN (SYS_CONTEXT('CONST','DOC_TYPE_PaymentOrder'),SYS_CONTEXT('CONST','DOC_TYPE_CashWarrant'))
    )
  AND ( SS.N_SUBJ_TYPE_ID = 1001
  OR SS.N_SUBJ_TYPE_ID    = 18001)
  ),
  regs AS
  (SELECT (
    CASE N_REGION_TYPE_ID
      WHEN 6027
      THEN VC_FULL_NAME
      ELSE VC_VISUAL_NAME
    END) VC_NAME,
    n_region_id,
    n_par_region_id,
    N_REGION_TYPE_ID,
    level             AS N_LEVEL,
    CONNECT_BY_ISLEAF AS IsLeaf
  FROM sr_v_regions
    START WITH n_par_region_id  IS NULL
    CONNECT BY PRIOR n_region_id = n_par_region_id
  ),
  regs_f AS
  (SELECT R1.VC_NAME VC_STREET,
    R2.VC_NAME VC_HOUSE,
    R2.n_region_id
  FROM regs R1,
    regs R2
  WHERE R1.n_region_id    = R2.n_par_region_id
  AND R2.IsLeaf           = 1
  AND R2.N_REGION_TYPE_ID = '6027'
  )
SELECT VC_SUBJ ,
  VC_ACC ,
  VC_DOC ,
  SUM(N_SUM) N_SUM,
  N_BAL ,
  D_PAY ,
  N_DAYS ,
  N_MONTH ,
  VC_TEL ,
  VC_TAGS ,
  regs_f.VC_STREET ,
  regs_f.VC_HOUSE
FROM raw_tbl,
  regs_f
WHERE (1                   =1)
AND regs_f.n_region_id (+) = raw_tbl.n_region_id
GROUP BY VC_SUBJ,
  VC_ACC,
  VC_DOC,
  N_BAL,
  D_PAY,
  N_DAYS,
  N_MONTH,
  VC_TEL,
  VC_TAGS,
  regs_f.VC_STREET,
  regs_f.VC_HOUSE
"""

try:
  con = cx_Oracle.Connection(user="user", password="password", dsn="db_tns_name");
  cursor = con.cursor()
  cursor.execute(sqlquery)
  result = cursor.fetchall()
  cursor.close()
except ValueError:
  logging.debug('db connection error')

con.close()

outfile = StringIO.StringIO()
filename = "report.xlsx"
reciever = 'receiver@example.ru'
sender = 'robot@sender.example.ru'
subj = 'Отчет по задолженности'

workbook = xlsxwriter.Workbook(outfile, {'in_memory': True})

worksheet = workbook.add_worksheet()
#rownum, colnum, text
worksheet.write(0,0,u"Субьект")
worksheet.write(0,1,u"Лицевой счет")
worksheet.write(0,2,u"Договор")
worksheet.write(0,3,u"Сумма по подпискам")
worksheet.write(0,4,u"Баланс")
worksheet.write(0,5,u"Дата последнего платежа")
worksheet.write(0,6,u"Дни задолженности")
worksheet.write(0,7,u"Месяцы задолженности")
worksheet.write(0,8,u"Контактный телефон")
worksheet.write(0,9,u"Теги")
worksheet.write(0,10,u"Улица")
worksheet.write(0,11,u"Дом")

rownum = 1

for row in result:
  #result data processing
  (vc_subj, vc_acc, vc_doc, n_sum, n_bal, d_pay, n_days, n_month, vc_tel, vc_tags, vc_street, vc_house) = row
  worksheet.write(rownum, 0, vc_subj.decode('utf-8'))
  worksheet.write(rownum, 1, vc_acc.decode('utf-8'))
  worksheet.write(rownum, 2, vc_doc.decode('utf-8'))
  worksheet.write(rownum, 3, n_sum)
  worksheet.write(rownum, 4, n_bal)
  worksheet.write(rownum, 5, str(d_pay))
  worksheet.write(rownum, 6, n_days)
  worksheet.write(rownum, 7, n_month)
  if vc_tel is not None:
    worksheet.write(rownum, 8, vc_tel.decode('utf-8'))
  if vc_tags is not None:
    worksheet.write(rownum, 9, vc_tags.decode('utf-8'))
  if vc_street is not None:
    worksheet.write(rownum, 10, vc_street.decode('utf-8'))
  if vc_house is not None:
    worksheet.write(rownum, 11, vc_house.decode('utf-8'))

  rownum += 1

workbook.close()

encodedcontent = base64.b64encode(outfile.getvalue())
outfile.close()


marker = "AUNIQUEMARKER"
body ="""
Отчет сформирован %s
""" % (datetime.now().strftime("%d.%m.%Y в %H:%M"))
body = base64.b64encode(body)
# Define the main headers.
part1 = """From: database report <%s>
To: <%s>
Subject: %s
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary=%s

--%s
""" % (sender, reciever, subj, marker, marker)

# Define the message action
part2 = """Content-Type: text/plain;
Content-Transfer-Encoding: base64

%s
--%s
""" % (body,marker)

# Define the attachment section
part3 = """Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; name=\"%s\"
Content-Transfer-Encoding: base64
Content-Disposition: inline; filename=%s

%s
--%s--
""" %(filename, filename, encodedcontent, marker)
message = part1 + part2 + part3


smtpObj = smtplib.SMTP('smtp.example.ru',25)
smtpObj.set_debuglevel(True)
smtpObj.ehlo()
smtpObj.login(sender,'sender_smpt_password')
#smtpObj.starttls()
smtpObj.sendmail(sender, reciever, message)
smtpObj.quit()

logging.info("ended at: " + str(datetime.now()))