"""Auxiliary leaf summaries. Existing 23 workbook sheets and formulas stay intact.""" import hashlib import json import sqlite3 from ...catalog import CLASS_SUMMARY, MODEL_SUMMARY, AMOUNT, QUANTITY, PRICE from ...query_indexes import create_query_indexes, quote from ..contract import SheetInfo from .numbers import number, ratio, to_text BASE_DIMS = ['direction','year','month','c1','c2','c3','c4'] BASE_HEADERS = ['进/出口','年度','月度','一级类目','二级类目','三级类目','描述'] def build_query_summaries(source, output_dir): db=sqlite3.connect(str(output_dir/'stat.sqlite')) sheets=[] try: for name, extras in [(CLASS_SUMMARY, []), (MODEL_SUMMARY, ['brand','model'])]: dims=BASE_DIMS+extras headers=BASE_HEADERS+(['品牌','型号'] if extras else [])+[AMOUNT,QUANTITY,'记录数',PRICE] table='q_class_month' if not extras else 'q_model_month' numeric=['年度','月度',AMOUNT,QUANTITY,'记录数',PRICE] cols=['"_row_index" INTEGER PRIMARY KEY','"_row_hash" TEXT']+[quote(h)+' TEXT' for h in headers]+[quote('_num_'+h)+' REAL' for h in numeric] db.execute(f'CREATE TABLE {quote(table)}({",".join(cols)})') insert=f'INSERT INTO {quote(table)} VALUES({",".join("?" for _ in cols)})' sql=f'SELECT {",".join(dims)},dsum(amount) amount,dsum(quantity) quantity,SUM(n) n FROM monthly GROUP BY {",".join(dims)}' batch=[];count=0 for row in source.execute(sql): amount=number(row['amount']);quantity=number(row['quantity']) values=[row['direction'],row['year'],str(int(row['month'][-2:])),row['c1'],row['c2'],row['c3'],row['c4']]+[row[e] for e in extras]+[to_text(amount),to_text(quantity),str(row['n']),to_text(ratio(amount,quantity))] rh='sha256:'+hashlib.sha256(json.dumps(values,ensure_ascii=False).encode()).hexdigest() num=[] for h in numeric: value=number(values[headers.index(h)]) num.append(float(value) if value is not None else None) batch.append((count,rh,*values,*num));count+=1 if len(batch)>=1000: db.executemany(insert,batch);batch.clear() if batch:db.executemany(insert,batch) create_query_indexes(db,table,headers+['_num_'+h for h in numeric]) sheets.append(SheetInfo(name,'query_summary',count,headers,table)) db.execute('ANALYZE') db.commit() finally:db.close() return sheets