# -*- coding:utf-8 -*-
#!/bin/env python
'''
#Auth:karl
#Function: released version
#Date:2017/6/27
#Version:V1.0
'''
import  sys,re,time,datetime
import  paramiko
import logging
import ConfigParser
import traceback
import MySQLdb
import platform
import xlrd
reload(sys)
sys.setdefaultencoding('utf-8')
if "Linux" == platform.system():
    Excelpath="/home/appdeploy/version/Version"
else:
    Excelpath = "D:\\auto\\release\Version"
class Mysql_connect(object):
    def __init__(self,*agre):
        self.port=agre[0]
        self.passwd=agre[1]
        self.username=agre[2]
        self.host=agre[3]
        self.version=agre[4]
    def File_get(self):
    Excelfilepath="{path}_{version}/Filelist.xlsx".format(path=Excelpath,version=self.version)
        workbook=xlrd.open_workbook(Excelfilepath)
        booksheet = workbook.sheet_by_name('Sheet1')
        self.p=list()
        for row in range(booksheet.nrows):
            row_data = []
            for col in range(booksheet.ncols):
                cel = booksheet.cell(row, col)
                val = cel.value
                try:
                    val = cel.value
                    val = re.sub(r'\s+', '', val)
                except:
                    pass
                if type(val) == float:
                    val = int(val)
                else:
                    val = str(val)
                row_data.append(val)
            self.p.append(row_data)
        self.__Mysql_deal()
    def __Mysql_deal(self):
        try:
            con = MySQLdb.connect(user=self.username,passwd=self.passwd,db="version_release",port=int(self.port),charset="utf8")
            dt = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            cursor=con.cursor()
            cursor.execute("CREATE TABLE IF NOT EXISTS version(Id INT PRIMARY  KEY  AUTO_INCREMENT,\
                            File VARCHAR (30) DEFAULT  null,Release_date TIMESTAMP,Instruction text)")
            sql="""INSERT INTO version (File,Release_date,Instruction) VALUES (%s, %s, %s) """
            for value in self.p:
                valuse=(value[0],dt,value[1])
                result=cursor.execute(sql,valuse)
            cursor.close()
            con.commit()
            con.close()
        except MySQLdb.Error, e:
            print "Error %d: %s" % (e.args[0], e.args[1])
            sys.exit(1)
    def File_deal(self):
        try:
            ssh = paramiko.SSHClient()
            ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
            ssh.connect(self.host,22, "root","****", timeout=5)
            for name in self.p:
                route_0=name[0]
                route=name[1]
                cmd="\cp -vr {path}_{version}/{filename}.xls  {dir_new}".format(path=Excelpath,version=self.version,filename=route_0,dir_new=route)
                stdin, stdout, stderr = ssh.exec_command(cmd)
                for out_msg in stdout.readlines():
                    print out_msg
        except Exception, e:
            print '%s\tError\n' % (self.host) ,e
        ssh.close()
def main():
    agre = ["3306", "*****", "root","xx.xx.xx.xx","3.0"]
    stg = Mysql_connect(*agre)
    stg.File_get()
    stg.File_deal()
if __name__ == '__main__':
    try:
      main()
    except Exception as e:
        print traceback.format_exc()