#coding: utf-8
# +-------------------------------------------------------------------
# | 宝塔Windows面板
# +-------------------------------------------------------------------
# | Copyright (c) 2015-2099 宝塔软件(http://bt.cn) All rights reserved.
# +-------------------------------------------------------------------
# | Author: 沐落 <cjx@bt.cn>
# +-------------------------------------------------------------------

import re,os,sys,public

class panelMssql:
    __DB_PASS = None
    __DB_USER = 'sa'
    __DB_PORT = 1433
    __DB_HOST = '127.0.0.1'
    __DB_CONN = None
    __DB_CUR  = None
    __DB_ERR  = None
    __DB_SERVER = 'MSSQLSERVER'

    __DB_CLOUD = 0 #远程数据库
    def __init__(self):
        self.__DB_SERVER = self.get_server()
        self.__DB_CLOUD = 0


    def set_host(self,host,port,name,username,password,prefix = ''):
        self.__DB_HOST = host
        self.__DB_PORT = int(port)
        self.__DB_NAME = name
        if self.__DB_NAME: self.__DB_NAME = str(self.__DB_NAME)
        self.__DB_USER = str(username)
        self._USER = str(username)
        self.__DB_PASS = str(password)
        self.__DB_PREFIX = prefix
        self.__DB_CLOUD = 1
        return self

    def __Conn(self):
        """
        连接MSSQL数据库
        """
        try:
            import pymssql
        except :
            os.system(public.get_run_pip("[PIP] install pymssql==2.1.4"))
            import pymssql        
               
        
        if not self.__DB_CLOUD:            
            sa_path = 'data/sa.pl'
            if os.path.exists(sa_path): self.__DB_PASS = public.readFile(sa_path)       
            self.__DB_PORT = self.get_port()

        try:
            print(self.__DB_CLOUD,self.__DB_HOST,self.__DB_PORT,self.__DB_USER,self.__DB_PASS)
            if self.__DB_CLOUD:                
                self.__DB_CONN = pymssql.connect(server = self.__DB_HOST, port= str(self.__DB_PORT),user=self.__DB_USER,password=self.__DB_PASS,database = None,login_timeout = 30,timeout = 0,autocommit = True)      
            else:
                self.__DB_CONN = pymssql.connect(server = self.__DB_HOST, port= str(self.__DB_PORT),login_timeout = 30,timeout = 0,autocommit = True)      
            self.__DB_CUR = self.__DB_CONN.cursor()  #将数据库连接信息，赋值给cur。
            self.__DB_CUR = self.__DB_CONN.cursor()  #将数据库连接信息，赋值给cur。

          
            if self.__DB_CUR:
                return True
            else:
                self.__DB_ERR = '连接数据库失败,请检查是否安装SQL Server'
                return False
        except Exception as ex:
            print(public.get_error_info())
 
            version = public.readFile(os.getenv("BT_SETUP") + '/sqlserver/version.pl')
            if version == '2005':
                self.__DB_ERR = '2005_login_error'
            else:
                self.__DB_ERR = public.get_error_info()
       
        return False



    #获取实例名
    def get_server(self):        
        try:
            sdata = public.ReadReg('SOFTWARE\Microsoft\Microsoft SQL Server','InstalledInstances')
            if sdata: self.__DB_SERVER =  sdata[0]   
        except :
            self.__DB_SERVER = 'MSSQLSERVER'

        if public.get_server_status(self.__DB_SERVER) == 0 :  public.set_server_status(self.__DB_SERVER,'start')
        return self.__DB_SERVER

    def get_version(self):
        """
        获取数据库版本
        """        
        try:
            versions = { "9":2005,"10":2008,"11":2012,"12":2014,"13":2016,"14":2017,"15":2019 }

            version = None
            skey = public.ReadReg('SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL',self.get_server())
            if skey:
                key = 'SOFTWARE\Microsoft\Microsoft SQL Server\{}\Setup'.format(skey)
                v =  public.ReadReg(key,'Version')
                if v:
                    arrs = v.split('.')
                    version = versions[arrs[0]]
            return version
        except :
            return False      

    #获取端口
    def get_port(self):     
        
        port =  public.ReadReg('SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\MSSQL.1\{}\SuperSocketNetLib\Tcp\IPAll'.format(self.__DB_SERVER),'TcpPort')
        if port: return port
        
        skey = public.ReadReg('SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL',self.get_server())
        if skey:
            key = 'SOFTWARE\Microsoft\Microsoft SQL Server\{}\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'.format(skey)
            port =  public.ReadReg(key,'TcpPort')
            if port: return port
   
        return 1433

    #获取端口
    def set_port(self,port):     
        
        key = 'SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\MSSQL.1\{}\SuperSocketNetLib\Tcp\IPAll'.format(self.__DB_SERVER)
        val =  public.ReadReg(key,'TcpPort')
        if not val:                
            skey = public.ReadReg('SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL',self.get_server())      
            if skey:
                key = 'SOFTWARE\Microsoft\Microsoft SQL Server\{}\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'.format(skey)
                val =  public.ReadReg(key,'TcpPort')
                           
        if not val : return False
       
        public.WriteReg(key,'TcpPort',port)
        return True

    
    def get_sql_name(self):
        """
        获取SQL Server服务名称
        """
        server_name = 'MSSQLSERVER'
        if public.get_server_status(server_name) >= 0:                
            server_name = 'MSSQLSERVER'
            return server_name
        else:
            server_name = 'MSSQL${}'.format(self.get_server())
            if  public.get_server_status(server_name) >= 0:                           
                return server_name
        return False

    def execute(self,sql):

        #执行SQL语句返回受影响行
        if not self.__Conn(): return self.__DB_ERR
        try:
            result = self.__DB_CUR.execute(sql)
            print(result)
            self.__Close()
            return result;    
        except Exception as ex:
            self.__DB_ERR = public.get_error_info()
            print(self.__DB_ERR)
            return self.__DB_ERR
    
    def query(self,sql):
        #执行SQL语句返回数据集
        if not self.__Conn(): return self.__DB_ERR
        try:
            self.__DB_CUR.execute(sql)
            result = self.__DB_CUR.fetchall()
            
            
            #将元组转换成列表
            data = list(map(list,result))
            self.__Close()
            return data
        except Exception as ex:          
            self.__DB_ERR = public.get_error_info()
            #public.WriteLog('SQL Server查询异常', self.__DB_ERR);
            return str(ex)
        
     
    #关闭连接        
    def __Close(self):
        self.__DB_CUR.close()
        self.__DB_CONN.close()