#!/usr/bin/env python3 import os, json, math, time, html from pathlib import Path from datetime import date, timedelta import requests from openpyxl import Workbook from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from openpyxl.utils import get_column_letter OUTDIR = Path('/var/www/hermes-artifacts/wb_lyskogor_stock_analysis_2026-08-25') OUTDIR.mkdir(parents=True, exist_ok=True) TODAY = date(2026, 8, 25) END = TODAY - timedelta(days=1) # полный день WB START_30 = END - timedelta(days=29) START_7 = END - timedelta(days=6) TOKEN_KEYS = ['WB_ANALYTICS_TOKEN','WB_MAIN_ANALYTICS_TOKEN','WB_API_KEY','WB_MAIN_API_KEY'] def load_env(): env = {} for p in ['/root/.hermes/.env', '/root/Hermes_doc/tools/marketplaces/api_accounts.env']: pp = Path(p) if not pp.exists(): continue for line in pp.read_text(encoding='utf-8', errors='ignore').splitlines(): s=line.strip() if not s or s.startswith('#') or '=' not in s: continue k,v=s.split('=',1) env[k.strip()] = v.strip().strip('"').strip("'") return env def get_token(): env=load_env() for k in TOKEN_KEYS: if env.get(k): return env[k], k raise SystemExit('No WB token found') URL = 'https://seller-analytics-api.wildberries.ru/api/v2/stocks-report/products/products' AVAIL = ['deficient','actual','balanced','nonActual','nonLiquid','invalidData'] def fetch_period(token, start, end, label): headers={'Authorization': token, 'Content-Type': 'application/json'} items=[]; offset=0; limit=1000 while True: payload={ 'currentPeriod': {'start': start.isoformat(), 'end': end.isoformat()}, 'stockType': '', 'availabilityFilters': AVAIL, 'skipDeletedNm': True, 'orderBy': {'field':'ordersCount', 'mode':'desc'}, 'limit': limit, 'offset': offset, } r=requests.post(URL,json=payload,headers=headers,timeout=60) if r.status_code == 429: time.sleep(60); continue if r.status_code != 200: raise RuntimeError(f'{label}: HTTP {r.status_code}: {r.text[:500]}') data=r.json().get('data',{}) batch=data.get('items',[]) or [] items.extend(batch) if len(batch) < limit: break offset += limit time.sleep(20) return items def m(item): return item.get('metrics',{}) or {} def safe_div(a,b): return a/b if b else None def classify(row): stock=row['stock'] avg30=row['avg_orders_30'] cover=row['days_cover_30'] lost=row['lost_orders_30'] availability=row['availability'] if stock <= 0: return 'нет остатка' if row['orders_30'] == 0: return 'замороженный остаток' if lost and lost > 0: return 'дефицит/упущенные заказы' if cover is not None and cover < 14: return 'риск скорого OOS' if availability == 'deficient': return 'дефицит' if cover is not None and cover > 120: return 'избыток >120 дней' if availability == 'nonLiquid': return 'неликвид/избыток' if cover is not None and cover > 60: return 'избыток 60+ дней' return 'рабочий остаток' def fmt_money(x): return f'{x:,.0f} ₽'.replace(',', ' ') def fmt_num(x): return f'{x:,.0f}'.replace(',', ' ') def fmt_float(x, nd=1): return '—' if x is None else f'{x:.{nd}f}' def pct(a,b): if b in (0,None): return None if not a else math.inf return (a-b)/b*100 def main(): token, token_key = get_token() items30=fetch_period(token, START_30, END, '30d') items7=fetch_period(token, START_7, END, '7d') by7={i.get('nmID'): i for i in items7} rows=[] for i in items30: mm=m(i); stock=mm.get('stockCount') or 0 if stock <= 0: continue nm=i.get('nmID') m7=m(by7.get(nm,{})) orders30=mm.get('ordersCount') or 0 orders7=m7.get('ordersCount') or 0 avg30=orders30/30 avg7=orders7/7 cover=stock/avg30 if avg30>0 else None price=(mm.get('currentPrice') or {}).get('maxPrice') or (mm.get('currentPrice') or {}).get('minPrice') or 0 row={ 'nmID': nm, 'vendorCode': i.get('vendorCode') or '', 'brand': i.get('brandName') or '', 'name': i.get('name') or '', 'subject': i.get('subjectName') or '', 'stock': stock, 'stock_sum': mm.get('stockSum') or 0, 'price': price, 'orders_30': orders30, 'orders_sum_30': mm.get('ordersSum') or 0, 'avg_orders_30': avg30, 'orders_7': orders7, 'orders_sum_7': m7.get('ordersSum') or 0, 'avg_orders_7': avg7, 'trend_7_vs_30_pct': pct(avg7, avg30), 'days_cover_30': cover, 'lost_orders_30': mm.get('lostOrdersCount') or 0, 'lost_orders_sum_30': mm.get('lostOrdersSum') or 0, 'to_client_30': mm.get('toClientCount') or 0, 'from_client_30': mm.get('fromClientCount') or 0, 'buyout_count_30': mm.get('buyoutCount') or 0, 'buyout_percent_30': mm.get('buyoutPercent') or 0, 'availability': mm.get('availability') or '', } row['status']=classify(row) rows.append(row) rows.sort(key=lambda r: (r['status'] not in ['дефицит/упущенные заказы','риск скорого OOS','дефицит'], -(r['orders_sum_30'] or 0))) # summary total_stock=sum(r['stock'] for r in rows) total_stock_sum=sum(r['stock_sum'] for r in rows) total_orders30=sum(r['orders_30'] for r in rows) total_rev30=sum(r['orders_sum_30'] for r in rows) by_status={} by_subject={} for r in rows: d=by_status.setdefault(r['status'], {'sku':0,'stock':0,'stock_sum':0,'orders30':0,'rev30':0}) d['sku']+=1; d['stock']+=r['stock']; d['stock_sum']+=r['stock_sum']; d['orders30']+=r['orders_30']; d['rev30']+=r['orders_sum_30'] s=by_subject.setdefault(r['subject'], {'sku':0,'stock':0,'stock_sum':0,'orders30':0,'rev30':0}) s['sku']+=1; s['stock']+=r['stock']; s['stock_sum']+=r['stock_sum']; s['orders30']+=r['orders_30']; s['rev30']+=r['orders_sum_30'] # xlsx xlsx=OUTDIR/'WB_Лысогор_анализ_остатков_25-08-2026.xlsx' wb=Workbook() ws=wb.active; ws.title='Все с остатком' headers=['status','nmID','vendorCode','brand','name','subject','stock','stock_sum','price','orders_30','orders_sum_30','avg_orders_30','orders_7','orders_sum_7','avg_orders_7','trend_7_vs_30_pct','days_cover_30','lost_orders_30','lost_orders_sum_30','to_client_30','from_client_30','buyout_count_30','buyout_percent_30','availability'] ws.append(headers) for r in rows: ws.append([r.get(h) for h in headers]) # styles fill_header=PatternFill('solid', fgColor='1F2937'); font_header=Font(color='FFFFFF', bold=True) for c in ws[1]: c.fill=fill_header; c.font=font_header; c.alignment=Alignment(wrap_text=True) fills={'риск скорого OOS':'FEE2E2','дефицит':'FEE2E2','дефицит/упущенные заказы':'FCA5A5','замороженный остаток':'E0E7FF','избыток >120 дней':'FEF3C7','неликвид/избыток':'FEF3C7','избыток 60+ дней':'FEF9C3','рабочий остаток':'DCFCE7'} for row in ws.iter_rows(min_row=2): status=row[0].value if status in fills: row[0].fill=PatternFill('solid', fgColor=fills[status]) for cell in row: cell.alignment=Alignment(vertical='top') widths=[20,12,30,14,44,24,10,14,10,10,14,12,10,14,12,14,14,12,14,12,12,12,12,14] for idx,w in enumerate(widths,1): ws.column_dimensions[get_column_letter(idx)].width=w ws.freeze_panes='A2'; ws.auto_filter.ref=ws.dimensions ws2=wb.create_sheet('Сводка') ws2.append(['Период продаж','30 дней',f'{START_30}—{END}']) ws2.append(['SKU с остатком',len(rows),'']) ws2.append(['Остаток, шт',total_stock,'']) ws2.append(['Остаток в ценах WB',total_stock_sum,'RUB']) ws2.append(['Заказы 30д',total_orders30,'']) ws2.append(['Выручка заказов 30д',total_rev30,'RUB']) ws2.append([]); ws2.append(['Статус','SKU','Остаток','Остаток ₽','Заказы 30д','Выручка 30д']) for k,v in sorted(by_status.items(), key=lambda kv: -kv[1]['stock_sum']): ws2.append([k,v['sku'],v['stock'],v['stock_sum'],v['orders30'],v['rev30']]) ws3=wb.create_sheet('По категориям') ws3.append(['Категория','SKU','Остаток','Остаток ₽','Заказы 30д','Выручка 30д','Заказы/день','Покрытие дней']) for k,v in sorted(by_subject.items(), key=lambda kv: -kv[1]['stock_sum']): avg=v['orders30']/30 ws3.append([k,v['sku'],v['stock'],v['stock_sum'],v['orders30'],v['rev30'],avg,(v['stock']/avg if avg else None)]) for sh in [ws2,ws3]: for c in sh[1]: c.font=Font(bold=True) for col in range(1, sh.max_column+1): sh.column_dimensions[get_column_letter(col)].width=22 wb.save(xlsx) # html critical=[r for r in rows if r['status'] in ['дефицит/упущенные заказы','риск скорого OOS','дефицит']] frozen=[r for r in rows if r['status']=='замороженный остаток'] surplus=[r for r in rows if r['status'] in ['избыток >120 дней','неликвид/избыток','избыток 60+ дней']] top_surplus=sorted(surplus, key=lambda r:r['stock_sum'], reverse=True)[:30] top_critical=sorted(critical, key=lambda r:(r['days_cover_30'] if r['days_cover_30'] is not None else 999, -r['orders_sum_30']))[:30] top_frozen=sorted(frozen, key=lambda r:r['stock_sum'], reverse=True)[:30] top_sales=sorted(rows, key=lambda r:r['orders_sum_30'], reverse=True)[:30] def rows_html(items): s='' for r in items: trend=r['trend_7_vs_30_pct'] trend_s='—' if trend is None else ('+∞' if trend==math.inf else f'{trend:+.0f}%') s += f"{html.escape(r['status'])}{r['nmID']}
{html.escape(r['vendorCode'])}{html.escape(r['name'])}
{html.escape(r['subject'])}{fmt_num(r['stock'])}
{fmt_money(r['stock_sum'])}{r['orders_30']}
{fmt_money(r['orders_sum_30'])}{r['orders_7']}
{trend_s}{fmt_float(r['days_cover_30'],0)}{r['lost_orders_30']}" return s status_cards=''.join(f"
{html.escape(k)}
{v['sku']}
{fmt_num(v['stock'])} шт · {fmt_money(v['stock_sum'])}
" for k,v in sorted(by_status.items(), key=lambda kv:-kv[1]['stock_sum'])) html_doc=f'''

WB Лысогор — анализ всех артикулов с остатком

Остаток по WB Analytics Stocks Report. Продажи: {START_30}—{END}; тренд: последние 7 дней vs средние 30 дней. Токены в отчёт не попадают.
SKU с остатком
{len(rows)}
Остаток
{fmt_num(total_stock)} шт
{fmt_money(total_stock_sum)}
Заказы 30д
{fmt_num(total_orders30)}
{fmt_money(total_rev30)}
Критично к проверке
{len(critical)}
дефицит / риск OOS / упущенные заказы

Разбивка по статусам

{status_cards}

Критично: дефицит / риск OOS / упущенные заказы

{rows_html(top_critical)}
СтатусnmID / артикулТоварОстаток30д7д трендПокрытие днейУпущ. заказы

Самый дорогой избыток / неликвид

{rows_html(top_surplus)}
СтатусnmID / артикулТоварОстаток30д7д трендПокрытие днейУпущ. заказы

Замороженный остаток без заказов за 30 дней

{rows_html(top_frozen)}
СтатусnmID / артикулТоварОстаток30д7д трендПокрытие днейУпущ. заказы

Топ по выручке за 30 дней среди товаров с остатком

{rows_html(top_sales)}
СтатусnmID / артикулТоварОстаток30д7д трендПокрытие днейУпущ. заказы

Полная таблица всех {len(rows)} SKU — в Excel: {xlsx.name}

''' html_path=OUTDIR/'WB_Лысогор_анализ_остатков_25-08-2026.html' html_path.write_text(html_doc,encoding='utf-8') result={ 'dates': {'sales_30': [START_30.isoformat(), END.isoformat()], 'sales_7':[START_7.isoformat(), END.isoformat()]}, 'source': 'WB Analytics /api/v2/stocks-report/products/products', 'token_key_used': token_key, 'counts': {'items30_total': len(items30), 'items7_total': len(items7), 'with_stock': len(rows)}, 'summary': {'stock': total_stock, 'stock_sum': total_stock_sum, 'orders30': total_orders30, 'revenue30': total_rev30}, 'by_status': by_status, 'top_critical': top_critical[:10], 'top_surplus': top_surplus[:10], 'top_frozen': top_frozen[:10], 'top_sales': top_sales[:10], 'files': {'xlsx': str(xlsx), 'html': str(html_path)} } (OUTDIR/'summary.json').write_text(json.dumps(result,ensure_ascii=False,indent=2,default=str),encoding='utf-8') print(json.dumps(result,ensure_ascii=False,indent=2,default=str)) if __name__ == '__main__': main()