twong329/docs/scripts/mt/funcs/products.py

import re
import sys
import json
import time
import pymysql

from datetime import datetime
from random import randint
from collections import namedtuple
from openpyxl import Workbook
from openpyxl.styles import PatternFill, colors

'''
twong 辅助工具，扫描商品表，及其关联表

自动更新不合理数据

对于价格方面的数据，则导出为 excel 表

运行需自行配置参数，并打开 commit 语句
'''

SHOW_MAX_DAYS = 800  # 超过天数自动下架

# database config type
DbConf = namedtuple('DbConf', 'type, host, port, username, password, database, prefix, charset')

conf = DbConf('mysql', '127.0.0.1', 3306, 'toor', '123456', 'twong', 'eb_', 'utf8mb4')

conn = pymysql.connect(
    host=conf.host,
    port=conf.port,
    user=conf.username,
    password=conf.password,
    database=conf.database,
    charset=conf.charset,
)

NOW = int(time.time())      # 当前时间戳

INVALID_CHAR=r'[\s，。 ,.{}()\[\]<>]+'
PTN_INVALID = r'^{}|{}$'.format(INVALID_CHAR, INVALID_CHAR)

# config excel
excel = Workbook()
sheet = excel.active
sheet.title = '问题商品'
sheet.freeze_panes = 'A2' # freeze above or left

# init sheet header
sheet['A1'] = 'ID'
sheet['B1'] = '商品'
sheet['C1'] = 'SKU'
sheet['D1'] = '成本'
sheet['E1'] = '价格'
sheet['F1'] = '原价'
sheet['G1'] = '利润率'
sheet['H1'] = '其他问题'

for cols in sheet.iter_cols(min_col=1,max_col=8,min_row=1,max_row=1):
    for cell in cols:
        cell.fill = PatternFill('solid', fgColor='C5C1AA')
sheet['A1'].fill = PatternFill('solid', fgColor='C5C1AA')

# 初始化表名
tables = {
    'product': 'store_product',
    'product_attr_result': 'store_product_attr_result',
    'product_attr_value': 'store_product_attr_value',
    'user': 'user',
    'order': 'store_order',
}

for k, v in tables.items():
    tables[k] = conf.prefix + v


def updateResult(productId, result, skuName, stock=0, price=0, otPrice=0, brokerage=0, brokerage2=0):
    '''
    更新 result 表中 result 字段中的值
    '''
    if not result:
        print('product={} result={}'.format(productId, result))
        return
    # print('updateResult:', productId, skuName)
    values = result.get('value')
    if not values:
        print('ERROR: no value field')
        return
    if isinstance(values, list):
        pass
    elif isinstance(values, dict):
        values = values.values()
    else:
        print('ERROR: bad value type')

    for value in values:
        detail = value.get('detail')
        if not detail:
            print('ERROR: no detail')
            return
        values = detail.values()
        values = [str(v) for v in values]
        sku = ','.join(sorted(values))
        if sku == skuName:
            value['stock'] = stock if stock else value.get('stock')
            value['ot_price'] = otPrice if otPrice else value['ot_price']
            value['price'] = price if price else value['price']
            value['brokerage'] = 0 if brokerage else 0
            value['brokerage_two'] = 0 if brokerage2 else 0
    return result
            

def fixResultInvalidChar(productId, result):
    '''
    检查 result 中规格中的各种非法字符
    '''
    if not result:
        return
    attrs = result.get('attr', [])
    if attrs:
        for attr in attrs:
            value = attr.get('value', '')
            attr['value'] = re.sub(PTN_INVALID, '', value)
            
            detail = attr.get('detail', [])
            for i, d in enumerate(detail):
                detail[i] = re.sub(PTN_INVALID, '', str(d))

    values = result.get('value', [])
    if isinstance(values, list):
        pass
    elif isinstance(values, dict):
        values = values.values()
    else:
        print('unknown value type')
        return
    for value in values:
        for i in range(len(attrs)):
            key = 'value{}'.format(i+1)
            valuex = value.get(key)
            value[key] = re.sub(PTN_INVALID, '', valuex)
            
        detail = value.get('detail', {})
        removing_keys = []
        adding_keys = {}
        for k, v in detail.items():
            if re.search(PTN_INVALID, k):
                removing_keys.append(k)
                newk = re.sub(PTN_INVALID, '', k)
                adding_keys[newk] = re.sub(PTN_INVALID, '', v)
            detail[k] = re.sub(PTN_INVALID, '', v)
        
        for k in removing_keys:
            del detail[k]
        for k, v in adding_keys.items():
            detail[k] = v

with conn:
    with conn.cursor() as cursor:
        sql = '''SELECT id, store_name, image, slider_image, spec_type, price, cost, stock, ficti, add_time
            FROM {} 
            WHERE is_show=1 AND is_del=0 '''.format(tables['product'])
        cursor.execute(sql)
        products = cursor.fetchall()
        errors = {}
        # 遍历所有商品
        for pd in products:
            productId = pd[0]
            productName = pd[1]
            specType = pd[4]
            image =pd[2]
            images = json.loads(pd[3])
            ficti = pd[8]
            addTime = pd[9]
            chFicti = False             # 是否需要更新虚拟销量
            chProductStock = False      # 是否需要更新库存
            errors[productId] = {
                'name': productName,
            }

            sq2 = '''SELECT result 
                FROM {}
                WHERE product_id={}'''.format(tables['product_attr_result'], productId)
            cursor.execute(sq2)
            result = cursor.fetchone()
            if not result or len(result) <= 0:
                # print('{}-{} 在 result 表中无数据.'.format(productId, productName))
                errors[productId]['others'] = errors[productId].get('others', '') + 'attr_result 表中无数据;'
                result = {}
            else:
                result = json.loads(result[0])
                fixResultInvalidChar(productId, result)

            sq3 = '''SELECT suk, stock, sales, price, cost, ot_price, brokerage, brokerage_two, `unique`
                FROM {}
                WHERE product_id={}'''.format(tables['product_attr_value'], productId)
            cursor.execute(sq3)
            values = cursor.fetchall()

            # 检查商品图片是否完整
            if not image or not images:
                errors[productId]['others'] = errors[productId].get('others', '') + ' 没有设置产品图;'
            
            # 虚拟销量必须为 0-20
            if ficti < 0 or ficti > 20:
                chFicti = True

            # 上架超过一定时间自动下架，并将 add_time 改为当前日期
            days = int( (NOW - addTime) / 86400 )
            if days > SHOW_MAX_DAYS:
                sql = '''UPDATE {} SET is_show=0, add_time={} WHERE id={}'''.format(tables['product'], NOW, productId)
                with conn.cursor() as cs:
                    cs.execute(sql)
                    print('{}-{} 过期{}天 已启动下架'.format(productId, productName, days))

            # 检查 price, cost, stock 必须为正值
            chResult = False                # 是否需要更新 attr_result 表
            errors[productId]['sku'] = {}   # 商品 sku 错误记录
            totalStock = 0
            eachStock = 0                   # 每个 sku 的库存，为了让sku 一致
            for sku in values:
                chStock, chOtPrice, chBrokerage, chName = False, False, False, False
                skuName = sku[0]
                skuNameList = skuName.split(',')
                for i, p in enumerate(skuNameList):
                    if re.search(PTN_INVALID, str(p)):
                        chName = True
                        skuNameList[i] = re.sub(PTN_INVALID, '', p)
                # print(skuNameList)
                newNameList = list(set(skuNameList))
                if chName or len(newNameList) != len(skuNameList):
                    skuName = ','.join(sorted(newNameList))

                stock = sku[1]
                sales = sku[2]
                price = sku[3]
                cost = sku[4]
                otPrice = sku[5]
                brokerage = sku[6]
                brokerage2 = sku[7]
                unique = sku[8]
                errors[productId]['sku'][skuName] = {}

                # 库存为 5-200
                if stock < 5 or stock > 200: # stock
                    chStock = True
                    chProductStock = True
                else:
                    totalStock += stock

                # 利润率 >= 30%
                if price <= 0: # price
                    errors[productId]['sku'][skuName]['price'] = float(price)
                if cost <= 0: # cost
                    errors[productId]['sku'][skuName]['cost'] = float(cost)
                if price < cost:
                    errors[productId]['sku'][skuName]['price'] = float(price)
                    errors[productId]['sku'][skuName]['cost'] = float(cost)
                    errors[productId]['sku'][skuName]['others'] = errors[productId]['sku'][skuName].get('others', '') + '价格低于成本;'
                if price > 0:
                    profitRate = round(float(1 - cost/price), 2)
                    if profitRate < 0.3:
                        errors[productId]['sku'][skuName]['price'] = float(price)
                        errors[productId]['sku'][skuName]['cost'] = float(cost)
                        errors[productId]['sku'][skuName]['profit'] = profitRate
                        errors[productId]['sku'][skuName]['others'] = errors[productId]['sku'][skuName].get('others', '') + ' 利润率不足 30%;'
                if otPrice < price:
                    chOtPrice = True
                # 商品自定义返利必须为0 
                if brokerage !=0 or brokerage2 != 0:
                    chBrokerage = True

                # 更新 result 结构
                if not eachStock or eachStock < sales:
                    eachStock = randint(50, 150)+sales if chStock else 0
                newOtPrice = int(float(price) * 1.4) if chOtPrice else 0
                newBrokerage = 1 if chBrokerage else 0
                newBrokerage2 = 1 if chBrokerage else 0
                updateResult(productId, result, skuName, stock=eachStock, price=0, otPrice=newOtPrice, brokerage=newBrokerage, brokerage2=newBrokerage2)
                # print(result)
                # 更新 SQL (attr_value 表)
                parts = []
                if chName:
                    parts.append("suk='{}'".format(skuName))
                if chStock:
                    totalStock += eachStock
                    parts.append('stock={}'.format(eachStock - sales))
                if chOtPrice:
                    parts.append('ot_price={}'.format(newOtPrice))
                if parts:
                    sparts = ','.join(parts)
                    sql = ''' UPDATE {} 
                        SET {}, brokerage=0, brokerage_two=0
                        WHERE `unique`='{}' '''.format(tables['product_attr_value'], sparts, unique)
                    with conn.cursor() as cs:
                        cs.execute(sql)
                        print('{}-{}-{} 自动修改库存名称等: sql={}'.format(productId, productName, skuName, sql))
                
                chResult = (chResult or chStock or chOtPrice or chBrokerage)

            # 更新 attr_result 表
            if chResult:
                sql = '''UPDATE {}
                    SET result='{}'
                    WHERE product_id={}'''.format(tables['product_attr_result'], json.dumps(result, ensure_ascii=False), productId)

                with conn.cursor() as cs:
                    cs.execute(sql)
                    print('更新 result. reason: {}, : sql={}'.format(sparts, sql))
            # update products
            parts = []
            if chFicti:
                newVal = randint(0, 20)
                parts.append('ficti={}'.format(newVal))
            if chProductStock:
                parts.append('stock={}'.format(totalStock))
            
            if parts:
                sparts = ','.join(parts)
                sql = '''UPDATE {} 
                SET {}
                WHERE id={}'''.format(tables['product'], sparts, productId)
                with conn.cursor() as cs:
                    cs.execute(sql)
                    print('{}-{} 更改虚拟销量和库存 {}'.format(productId, productName, sparts))
        conn.commit()

counter = 1 # skip header line
cell = lambda ch,c: '{}{}'.format(ch, c)

for pId, v in errors.items():
    writen = False
    if len(v) > 2:
        counter += 1
        sheet[cell('A', counter)] = pId
        sheet[cell('B', counter)] = v['name']
        sheet[cell('H', counter)] = v.get('others', '')
        writen = True

    for k, s in v['sku'].items():
        if len(s) > 0:
            counter += 1
            if not writen:
                sheet[cell('A', counter)] = pId
                sheet[cell('B', counter)] = v['name']
                sheet[cell('H', counter)] = v.get('other', '')
                writen = True
            sheet[cell('C', counter)] = k
            sheet[cell('D', counter)] = s.get('cost', '')
            sheet[cell('E', counter)] = s.get('price', '')
            sheet[cell('F', counter)] = s.get('ot_price', '')
            sheet[cell('G', counter)] = s.get('profit', '')
            sheet[cell('H', counter)] = s.get('others', '')
            
# sheet['A1'] = 'ID'
# sheet['B1'] = '商品'
# sheet['C1'] = 'SKU'
# sheet['D1'] = '成本'
# sheet['E1'] = '价格'
# sheet['F1'] = '原价'
# sheet['G1'] = '利润率'
# sheet['H1'] = '其他问题'
excel.save(filename=datetime.now().strftime('%Y-%m-%d.xls'))
sys.exit()