API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jul 4th, 2017
88
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 8.79 KB | None | 0 0
raw download clone embed print report
  1. #!/usr/bin/env python3
  2. import argparse
  3. import io
  4. from configparser import RawConfigParser
  5. import time
  6. import datetime
  7. import os
  8. import stat
  9. import subprocess
  10. import base64
  11.  
  12. import psycopg2
  13. import psycopg2.extras
  14.  
  15.  
  16. def load_config(config_file_path):
  17. ini_str = open(config_file_path, 'r').read()
  18. ini_fp = io.StringIO(ini_str)
  19. config = RawConfigParser(strict=False, allow_no_value=True)
  20. config.readfp(ini_fp)
  21. return config
  22.  
  23.  
  24. def parse_args():
  25. parser = argparse.ArgumentParser(description="CDR Export")
  26. parser.add_argument('-c', '--config', required=True,
  27. dest="config", help="Config File")
  28. parser.add_argument('-i', '--log', required=True, type=int,
  29. dest='log_id', help='Log ID')
  30. args = parser.parse_args()
  31. return args
  32.  
  33.  
  34. def export_cdr(log_id):
  35. conn = psycopg2.connect(host='192.168.112.30',
  36. port=5432,
  37. database='class4_teleinx',
  38. user='class4_teleinx',
  39. password='N43f8ds4Ir3')
  40. conn.autocommit = True
  41. cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
  42. cur.execute("SELECT * FROM cdr_export_log WHERE id = %s", (log_id, ))
  43. cdr_export_log = cur.fetchone()
  44.  
  45. cur.execute("UPDATE cdr_export_log SET status = 1 WHERE id = %s", (log_id, ))
  46.  
  47. export_path = os.path.realpath(os.path.join(os.path.dirname(__file__), os.path.pardir, 'db_nfs_path', 'cdr_download'))
  48. if not os.path.exists(export_path):
  49. os.makedirs(export_path)
  50. try:
  51. os.chmod(export_path, stat.S_IRWXO+stat.S_IRWXU+stat.S_IRWXG)
  52. except:
  53. print ("chmod 777 failed")
  54.  
  55.  
  56. cdr_start = datetime.datetime.strptime(str(cdr_export_log['cdr_start_time'])[0:19], "%Y-%m-%d %H:%M:%S")
  57. cdr_end = datetime.datetime.strptime(str(cdr_export_log['cdr_end_time'])[0:19], "%Y-%m-%d %H:%M:%S")
  58.  
  59. print ("start %s end %s" % (cdr_start,cdr_end))
  60.  
  61. cur.execute("select TABLE_NAME as name from INFORMATION_SCHEMA.TABLES where TABLE_NAME like'client_cdr2%' order by TABLE_NAME limit 1")
  62. table_info = cur.fetchone()
  63. last_time_name = table_info['name'][10:]
  64. last_table_time = datetime.datetime.strptime(last_time_name, "%Y%m%d")
  65.  
  66. # print (type(last_table_time),last_table_time)
  67. # print (type(cdr_start),cdr_start)
  68.  
  69. if cdr_start < last_table_time:
  70. cdr_start = last_table_time
  71. now = datetime.datetime.now()
  72. if cdr_end > datetime.datetime(now.year,now.month,now.day):
  73. cdr_end = datetime.datetime(now.year,now.month,now.day)
  74.  
  75. print ("start %s end %s" % (cdr_start,cdr_end))
  76.  
  77. this_download_path_name = str(cdr_export_log['id'])+'_'+str(int(time.time()))
  78.  
  79. log_file_path_name = os.path.join(export_path, this_download_path_name)
  80. if not os.path.exists(log_file_path_name):
  81. os.makedirs(log_file_path_name)
  82. try:
  83. os.chmod(log_file_path_name, stat.S_IRWXO+stat.S_IRWXU+stat.S_IRWXG)
  84. except:
  85. print ("chmod 777 failed")
  86. print (log_file_path_name)
  87.  
  88.  
  89. cur.execute("UPDATE cdr_export_log SET status = 2,finished_date = 0 WHERE id = %s", (log_id, ))
  90. total_row = 0
  91. while cdr_start <= cdr_end:
  92.  
  93. time_str = cdr_start.strftime('%Y%m%d')
  94. # print ("this time is %s time str %s" % (cdr_start,time_str))
  95. # this_where = cdr_export_log['where_sql'].replace('client_cdr.','client_cdr'+time_str+'.')
  96. # this_show_fields = cdr_export_log['show_fields_sql'].replace('client_cdr.','client_cdr'+time_str+'.')
  97. this_show_fields = cdr_export_log['show_fields_sql']
  98. sql = this_show_fields
  99.  
  100. #分天导入文件
  101. this_file_name = os.path.join(log_file_path_name, time_str+'.csv')
  102. copy_sql = "COPY (%s) TO STDOUT WITH CSV HEADER " % (sql)
  103.  
  104. error_flg = False
  105. print(copy_sql)
  106. try:
  107. handle = open(this_file_name, "w")
  108. except:
  109. error_flg = True
  110. error_msg = 'Download file path do not have write permissions'
  111. # cur.execute("UPDATE cdr_export_log SET status = -1 , error_msg = 'Download file path do not have write permissions' WHERE id = %s", (log_id, ))
  112. break
  113.  
  114. error_msg = ''
  115. # cur.execute(copy_sql)
  116. try:
  117. cur.copy_expert(copy_sql,handle)
  118. except (psycopg2.extensions.QueryCanceledError, psycopg2.OperationalError):
  119. # print(psycopg2.extensions.QueryCanceledError)
  120. # print(psycopg2.OperationalError)
  121. error_flg = True
  122. error_msg = psycopg2.extensions.QueryCanceledError + "\n" + psycopg2.OperationalError
  123. except psycopg2.DatabaseError:
  124. print(psycopg2.DatabaseError)
  125. error_flg = True
  126. # error_msg = psycopg2.DatabaseError
  127. error_msg = 'Database error'
  128. handle.close()
  129. if error_flg == True:
  130. break
  131. else:
  132. rows_cmd = "wc -l %s" % (this_file_name)
  133. rows_result = subprocess.check_output(rows_cmd, shell=True)
  134. rows = int(rows_result.decode().split( )[0]) - 1
  135. total_row += rows
  136. cur.execute("UPDATE cdr_export_log SET finished_date = finished_date + 1 WHERE id = %s", (log_id, ))
  137. #compress
  138. # cmd = "cat %s | gzip > %s.gz" % (export_file_path, export_file_path)
  139.  
  140.  
  141.  
  142. cdr_start = cdr_start + datetime.timedelta(days=1)
  143.  
  144.  
  145. # return
  146.  
  147. if error_flg == True:
  148. cur.execute("UPDATE cdr_export_log SET status = -1 , error_msg = %s WHERE id = %s", (error_msg,log_id, ))
  149. cur.close()
  150. conn.close()
  151. return
  152.  
  153. cur.execute("UPDATE cdr_export_log SET status = 3, file_rows = %s WHERE id = %s", (total_row,log_id, ))
  154.  
  155. os.chdir(export_path)
  156.  
  157. result_file_name = cdr_export_log['file_name'].replace('.csv','.tar.bz2')
  158.  
  159. cmd = "tar -jcvf %s %s" % (result_file_name,this_download_path_name)
  160. os.system(cmd)
  161. print (cmd)
  162. os.system('rm -rf %s' % this_download_path_name)
  163. cur.execute("UPDATE cdr_export_log SET status = 4 WHERE id = %s", (log_id, ))
  164.  
  165.  
  166. cur.close()
  167. conn.close()
  168.  
  169.  
  170.  
  171.  
  172.  
  173. def get_smtp_info(cursor):
  174. sql = """SELECT smtphost as host,smtpport as port,emailusername as username,emailpassword as password,loginemail as is_auth,
  175. fromemail as from_email, smtp_secure as smtp_secure FROM system_parameter LIMIT 1"""
  176. cursor.execute(sql)
  177. smtp_setting = cursor.fetchone()
  178. return smtp_setting
  179.  
  180.  
  181. def get_smtp_info_by_send(cur,send_mail_id):
  182. sql = """SELECT smtp_host AS host, smtp_port AS port,username,password as password,loginemail as is_auth,
  183. email as from_email,name as name, secure as smtp_secure FROM mail_sender where id = %s"""
  184. cur.execute(sql,(send_mail_id,))
  185. smtp_setting = cur.fetchone()
  186. return smtp_setting
  187.  
  188.  
  189. def get_cdr_download_template(cur):
  190. sql = """SELECT download_cdr_from,download_cdr_subject,download_cdr_content,download_cdr_cc FROM mail_tmplate limit 1"""
  191. cur.execute(sql)
  192. return cur.fetchone()
  193.  
  194.  
  195. def cdr_send_mail(cur,log_id,send_mail,web_base_url):
  196. template_info = get_cdr_download_template(cur)
  197. if template_info['download_cdr_from'] == 'Default' or template_info['download_cdr_from'] == 'default':
  198. smtp_setting = get_smtp_info(cur)
  199. else:
  200. smtp_setting = get_smtp_info_by_send(cur,template_info['download_cdr_from'])
  201. if smtp_setting is None:
  202. smtp_setting = get_smtp_info(cur)
  203. mail_info = {}
  204. for (d,x) in smtp_setting.items():
  205. mail_info[d] = x
  206.  
  207. content = template_info['download_cdr_content']
  208. download_url = web_base_url+'cdrreports_db/export_log_down?key='+ base64.b64encode(str(log_id).encode()).decode()
  209. download_btn = "<a href='{}'>Download Link</a>".format(download_url)
  210. if content is not None and '{download_link}' in content:
  211. content = content.replace('{download_link}',download_btn)
  212. else:
  213. content += '<br />download link is :'+download_btn
  214.  
  215. mail_info['subject'] = template_info['download_cdr_subject']
  216. mail_info['to'] = send_mail
  217. mail_info['cc'] = template_info['download_cdr_cc']
  218. mail_info['content'] = content
  219. return_info = SendMail.send_mail(mail_info)
  220. print (return_info)
  221. save_email_log(cur,return_info,mail_info)
  222.  
  223.  
  224. def save_email_log(cur,return_info,mail_info):
  225. sql = """INSERT INTO email_log (send_time,type,email_addresses,status,error,subject,content)
  226. values (current_timestamp(0),5,%s,%s,%s,%s,%s )"""
  227. if return_info['status'] == True:
  228. status = 0
  229. else:
  230. status = 1
  231. cur.execute(sql,(mail_info['to'],status,return_info['msg'],mail_info['subject'],mail_info['content']))
  232.  
  233.  
  234. def main():
  235. args = parse_args()
  236. export_cdr(args.log_id)
  237.  
  238. if __name__ == "__main__":
  239. main()
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!