# -*- coding: utf-8 -*-
#powered by lixora

import ConfigParser
from docxtpl import DocxTemplate
import time
import sys
from api.conn_oracle import exec_sql
from api.run_cmd import ssh_cmd
import os

reload(sys)
sys.setdefaultencoding('utf8')
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'

cf = ConfigParser.ConfigParser()
cf.read(".\db.conf")

#oracle conn str
sname    = cf.get("db", "db_sname")
user     = cf.get("db", "db_user")
password = cf.get("db", "db_pass")
ip       = cf.get("os", "os_ip")
port     = int(cf.get("db", "db_port"))
customerinfo = cf.get("doc","customerinfo").decode('utf-8')

owner =cf.get("doc","owner").decode('utf8')

print "####db connect info####"
print "sname:", sname
print "user:", user
#print "password:", password
print "ip:", ip
print "port:", port
print "customerinfo:",customerinfo

#os conn str
ip          =cf.get("os","os_ip")
ospassword  =cf.get("os","os_pass")
osuser      =cf.get("os","os_user")
osport      =int(cf.get("os","os_port"))

print "####os connect info####"
print "ip: " ,ip
#print "ospassword: ",ospassword
print "osuser: ", osuser
print "osport: ", osport

context = dict()
doc = DocxTemplate("word_template/lixora1.docx")

timeArray = time.localtime()
mydate = time.strftime("%Y-%m-%d-%S", timeArray)
mydate1 = time.strftime("%Y-%m-%d", timeArray)
context['checkdate'] = mydate1
context['firstdate'] = mydate1
context['lastdate'] = mydate1
context['owner'] = owner
context['customerinfo'] = customerinfo
context['tablespaceusages'] = []
context['diskusage'] = []
context['dbsizes'] =[]
context['hosts'] =[]
context['redonumbers'] = []
context['jobs'] = []
context['diskgroups'] = []
context['backups'] = []
context['process'] = []
context['process'] = []
context['passwords'] = []
context['dbausers'] = []

DB=sname

# #event
# context['events'] = []
# sql1='select event, count(*) from v$session group by event order by 2'
# tmp_dict=dict()
# arr = exec_sql(user,password,ip,port,sname,sql1)
# tmp_dict = dict()
# for v in arr:
#     tmp_dict[v[0]] = v[1]
# #print 'tmp_dict',type(tmp_dict)
# #print tmp_dict
# context['events'].append(tmp_dict)
# print context['events']

#dbname
context['dbname'] = exec_sql(user, password, ip, port, sname, '''select name from v$database''')[0][0]
print 'dbname : ',context['dbname']

#dbversion
context['dbversion'] = exec_sql(user, password, ip, port, sname, '''select VERSION  from v$instance''')[0][0]
print 'dbversion : ', context['dbversion']

#dbinstance
context['dbinstance'] = exec_sql(user, password, ip, port, sname, ''' select INSTANCE_NAME from v$instance''')[0][0]
print 'dbinstance : ', context['dbinstance']

# tablespaceusages
tmp_dict = exec_sql(user,password,ip,port,sname, '''SELECT d.status Status,d.tablespace_name "Name",d.contents "Type",d.extent_management "Ext-Manage",to_char(nvl(a.bytes / 1024 / 1024/1024, 0), '99999999.999') "Total Size (G)",to_char(nvl(a.bytes2 - nvl(f.bytes, 0), 0) / 1024 / 1024/1024, '99999999.99') "Used (G)",to_char(nvl(nvl(f.bytes, 0), 0) / 1024 / 1024/1024, '99999999.999') "Free (G)",to_char(nvl((a.bytes2 - nvl(f.bytes, 0)) / a.bytes * 100, 0),'990.00') "Used %"  FROM sys.dba_tablespaces d,(SELECT tablespace_name, SUM(greatest(BYTEs,MAXBYTES)) bytes,SUM(BYTES) bytes2 FROM dba_data_files GROUP BY tablespace_name) a,(SELECT tablespace_name, SUM(BYTES) bytes FROM dba_free_space GROUP BY tablespace_name) f  WHERE d.tablespace_name = a.tablespace_name(+)  AND d.tablespace_name = f.tablespace_name(+) order by 8 ''')
context['tablespaceusages'].append({'sid':DB, 'tablespaceusage':tmp_dict})
print context['tablespaceusages']

###dbsize
tmp_dict = exec_sql(user,password,ip,port,sname, '''select "DB FILE (G)","TEMP FILE (G)","SEGMENT (G)"
  from (select round(sum(bytes / 1024 / 1024 / 1024), 2) as "DB FILE (G)"  from dba_data_files),
       (select round(sum(bytes) / 1024 / 1024 / 1024, 2) as "TEMP FILE (G)" from dba_temp_files),
       (select round(sum(bytes) / 1024 / 1024 / 1024, 2) as "SEGMENT (G)"    from dba_segments)
 ''')
context['dbsizes'].append({'sid':DB, 'dbsize':tmp_dict})
print context['dbsizes']

#ASM diskgroup
tmp_dict = exec_sql(user,password,ip,port,sname, '''select name,TYPE,state,total_mb/1024 ,free_mb/1024,USABLE_FILE_MB/1024,(free_mb/total_mb)*100 as fre_pct,OFFLINE_DISKS from v$asm_diskgroup''')
context['diskgroups'].append({'sid': DB, 'diskgroup': tmp_dict})
print context['diskgroups']

# redonumbers
tmp_dict = exec_sql(user,password,ip,port,sname, '''select  t1.GROUP#,t1.THREAD#,t1.BYTES/1024/1024 as "Size(M)",
t2.MEMBER,t1.STATUS  
from v$log t1,v$logfile t2 where t1.GROUP#=t2.GROUP#
order by t1.GROUP#''')
context['redonumbers'].append({'sid': DB, 'redonumber': tmp_dict})
print context['redonumbers']

#jobs
tmp_dict=[]
tmp_dict = exec_sql(user, password, ip, port, sname, '''select JOB, WHAT, NEXT_DATE, NEXT_SEC  from dba_jobs''')
context['jobs'].append({'sid': DB, 'job': tmp_dict})
print context['jobs']

#rman backup
tmp_dict=[]
tmp_dict = exec_sql(user, password, ip, port, sname, '''SELECT A.RECID "BACKUP SET",
       DECODE(B.INCREMENTAL_LEVEL,
              '',
              DECODE(BACKUP_TYPE, 'L', 'Archivelog', 'Full'),
              1,
              'Incr-1',
              0,
              'Incr-0',
              B.INCREMENTAL_LEVEL) "Type LV",
       B.CONTROLFILE_INCLUDED "including CTL",
       DECODE(A.STATUS,
              'A',
              'AVAILABLE',
              'D',
              'DELETED',
              'X',
              'EXPIRED',
              'ERROR') "STATUS",
       A.DEVICE_TYPE "Device Type",
       A.START_TIME "Start Time",
       A.ELAPSED_SECONDS "Elapsed Seconds",
       A.TAG "Tag",
       A.HANDLE "Path"
  FROM GV$BACKUP_PIECE A, GV$BACKUP_SET B
 WHERE A.SET_STAMP = B.SET_STAMP
   AND A.DELETED = 'NO'
   AND rownum <10
 ORDER BY A.COMPLETION_TIME DESC''')
context['backups'].append({'sid': DB, 'backup': tmp_dict})
print 'backup : ', context['backups']

#process session
tmp_dict=[]
tmp_dict = exec_sql(user, password, ip, port, sname, '''select RESOURCE_NAME,
       CURRENT_UTILIZATION as "CURRENT",
       MAX_UTILIZATION as "MAX",
       INITIAL_ALLOCATION as "INITIAL",
       LIMIT_VALUE as "LIMIT"
  from v$resource_limit''')
context['process'].append({'sid': DB, 'proc': tmp_dict})
print context['process']

#password
tmp_dict=[]
tmp_dict = exec_sql(user, password, ip, port, sname, '''SELECT profile,resource_name, limit FROM dba_profiles WHERE resource_type='PASSWORD' order by 1''')
context['passwords'].append({'sid': DB, 'password': tmp_dict})
print context['passwords']

#dba user
tmp_dict=[]
tmp_dict = exec_sql(user, password, ip, port, sname, '''select grantee, granted_role, admin_option adm
  from dba_role_privs
 where granted_role like 'DBA'
   and grantee not in ('SYS', 'SYSTEM')''')
context['dbausers'].append({'sid': DB, 'dbauser': tmp_dict})
print context['dbausers']

#dg check
context['dgchks'] =[]
tmp_dict=[]
tmp_dict = exec_sql(user, password, ip, port, sname, ''' SELECT
 (SELECT name FROM V$DATABASE
 ) name,
 (SELECT MAX (sequence#) FROM v$archived_log WHERE dest_id = 1
 ) Current_primary_seq,
 (SELECT MAX (sequence#)
 FROM v$archived_log
 WHERE TRUNC(next_time) > SYSDATE - 1
 AND dest_id = 2
 ) max_stby,
 (SELECT NVL (
 (SELECT MAX (sequence#) - MIN (sequence#)
 FROM v$archived_log
 WHERE TRUNC(next_time) > SYSDATE - 1
 AND dest_id = 2
 AND applied = 'NO'
 ), 0)
 FROM DUAL
 ) "To be applied",
 (
 (SELECT MAX (sequence#) FROM v$archived_log WHERE dest_id = 1
 ) -
 (SELECT MAX (sequence#) FROM v$archived_log WHERE dest_id = 2
 )) "To be Shipped"
FROM DUAL''')
context['dgchks'].append({'sid': DB, 'dgchk': tmp_dict})
print context['dgchks']

#parameters
context['parameters'] = []
tmp_dict=[]
tmp_dict = exec_sql(user, password, ip, port, sname, '''SELECT
    p.name,i.instance_name ,p.value as "VALUE"
FROM
    gv$parameter p, gv$instance  i
WHERE
    p.inst_id = i.inst_id
and  isdefault='FALSE'
ORDER BY     p.name, i.instance_name''')
context['parameters'].append({'sid': DB, 'parameter': tmp_dict})
print 'parameters : ',context['parameters']

#PSU
context['psus'] = []
tmp_dict=[]
tmp_dict = exec_sql(user, password, ip, port, sname, '''select * from dba_registry_history''')
context['psus'].append({'sid': DB, 'psu': tmp_dict})
print 'psus : ',context['psus']

#hostname
cmd='/bin/hostname'
osname=ssh_cmd(ip,osport,osuser,ospassword,cmd)[0]
context['hostname'] = osname
print 'hostname : ',context['hostname']

# #osversion
# cmd='/bin/cat /etc/redhat-release '
# context['osversion'] = ssh_cmd(ip,osport,osuser,ospassword,cmd)[0]
# print 'osversion : ',context['osversion']

# #cpu
# cmd='''/bin/grep 'model name' /proc/cpuinfo | wc -l '''
# context['cpu'] = ssh_cmd(ip,osport,osuser,ospassword,cmd)[0]
# print 'cpu : ',context['cpu']

# #memory
# cmd="/bin/cat /proc/meminfo |grep MemTotal|awk '{print $2$3}'"
# context['memory'] = ssh_cmd(ip,osport,osuser,ospassword,cmd)[0]
# print 'memory : ',context['memory']

# ###os diskusage
# tmp1_dict = dict()
# tmp1_dict['hostname'] = osname
# tmp1_dict['disk'] = []

# cmd='df -h'
# stdout = ssh_cmd(ip,osport,osuser,ospassword,cmd)
#
#
# for result in stdout:
#     tmp_dict2 = dict()
#     tmp_arr = result.split()
#     print tmp_arr
#     if len(tmp_arr) > 4 and '/' in tmp_arr[-1]:
#         tmp_dict2['path'] = tmp_arr[-1]
#         tmp_dict2['size'] = tmp_arr[-5]
#         tmp_dict2['avail'] = tmp_arr[-3]
#         tmp_dict2['usage'] = tmp_arr[-2]
#         tmp1_dict['disk'].append(tmp_dict2)
# context['diskusage'].append(tmp1_dict)
# print  'diskusage :',context['diskusage']

###host ip
# """读取hosts 配置文件"""
# tmp3_dict = dict()
# tmp3_dict['hostname'] = osname
# tmp3_dict['hosts'] = []
#
# stdout = ssh_cmd(ip,osport,osuser,ospassword,"cat /etc/hosts|grep -v ^#|grep -v ^:|awk '{print $1,$2}'")
#
# for result in stdout:
#     tmp_dict4 = dict()
#     tmp_arr = result.split()
#     #print 'tmp_arr',tmp_arr
#     #print len(tmp_arr)
#     if len(tmp_arr) >1 :
#         tmp_dict4['ip'] = tmp_arr[0]
#         tmp_dict4['name'] = tmp_arr[1]
#         tmp3_dict['hosts'].append(tmp_dict4)
# context['hosts'].append(tmp3_dict)
# print  'hosts : ', context['hosts']
# #c.close()

# #crsstat
# """获取crs 集群状态"""
# cmd='df -h'
# context['crsstat'] = ssh_cmd(ip,osport,osuser,ospassword,cmd)
# print 'crsstat : ',context['crsstat']

# #lsnrctl
# cmd='''su - oracle -c "lsnrctl stat" '''
# context['lsnrctl'] = ssh_cmd(ip,port,user,password,cmd)
# print 'lsnrctl : ',context['lsnrctl']

# #sysctl
# """读取操作系统内核参数"""
#
# cmd='/sbin/sysctl -p'
# context['sysctl'] = ssh_cmd(ip,osport,osuser,ospassword,cmd)
# print 'sysctl : ',context['sysctl']

"""
生成word 文档：时间_客户信息_db-hc.docx
"""
#print type(context)
print 'CONTEXT : ',context
doc.render(context)
doc.save(mydate+"_"+customerinfo+"_"+sname+"-dbhc.docx")