import pymssql
import pandas as pd
import xlrd


def data_load(path):
        data = xlrd.open_workbook(path)
        table = data.sheets()[0]
        ncols = table.ncols
        data2 = pd.DataFrame([])
        for i in range(ncols):
            data2[i] = table.col_values(i)
        data2.rename(columns=data2.iloc[0, :], inplace=True)
        data2.drop([0], axis=0, inplace=True)
        return data2

#def judge_subclass()
def pre(matrix_text, addr):
    """将数据导出
    matrix_text：导出数据
    addr：导出地址
    """
    matrix_text.to_csv(addr, sep=',', index=0, encoding='utf_8_sig', columns=matrix_text.columns)

class similar():
    
    def __init__(self):
        pass
    
    def istrue(self):
        return False
    
    def productcode_get(self):
        return 0

 
class sql_find():
    
    def __init__ (self):
        self.conn = pymssql.connect(host='123.56.115.207', user='zgcprice3311', password='admin@2018@)!*', database='Try', autocommit=True)
        self.cursor = self.conn.cursor()
    
    
    def state_extract(self, productcode, database_name='Info_Product', state_name='State'):
        '''
        根据pcode提取状态。
        '''
        self.cursor.execute(f"select {state_name} from {database_name} where ProductCode = '{str(productcode)}'")
        try : n = self.cursor.fetchone()[0]
        except TypeError:
            n = self.cursor.fetchone()
        return n

    
    def price_get(self, productcode, database_name='ZI_Price_Quote', name='MaximumPrice'):
        '''
        根据pcode提取价格。
        '''
        self.cursor.execute(f"select {name} from {database_name} where ProductCode = '{str(productcode)}'")
        try: n = str(self.cursor.fetchone()[0])
        except TypeError:
            n = self.cursor.fetchone()
        return n

    
    def state_update(self, productcode, state_num, database_name='Info_Product', state_name='State'):
        '''
        状态字更新。
        '''
        self.cursor.execute(f"select {state_name} from {database_name} where ProductCode ='{str(productcode)}'")
        n = self.cursor.fetchone()[0]
        if n != state_num:
            self.cursor.execute(f"update {database_name} set {state_name}='{state_num}' where ProductCode ='{str(productcode)}'")
            print(f'{productcode} 状态已由 {n} 更改为 {state_num} 。')
            if state_num == '1':
                return 1
            elif state_num == '6':
                return 6
        return 0

    
    def price_update(self, productcode, price, database_name='ZI_Price_Quote', state_name='MaximumPrice'):
        '''
        价格更新。
        '''
        self.cursor.execute(f"update {database_name} set {MaximumPrice}='{price}' where ProductCode ='{str(productcode)}'")
        print(f'{productcode} 价格已经更改为 {price} 。')


    def sku_delete(self, sku, database_name='productcode_sku'):
        '''
        删除sku。  
        '''
        self.cursor.execute(f"delete from {database_name} where sku ='{sku}'")


'''
class price_import():
    def __init__(self, table):
        self._table = table
        lyst = list(self._table.columns)
        for i in range(len(lyst)):
            if lyst[i] == '库内已有产品':
                self._pcode_col = i
            if lyst[i] == 'NAME':
                self._name_col = i
            if lyst[i] == 'jd_price':
                self._price_col = i
            if lyst[i] == 'create_date':
                self._time_col = i
            if lyst[i] == 'sku_id':
                self._sku_col = i

    def data_extract(self, col):
        

'''