j&ddlZddlZddlmZmZmZmZejdejjejje ddZ dejfdZdZd Zd Zdeeeffd Zd eeeefdefd Z ddedeedeedeedeeeeff dZddedeedeefdZdedeefdZdeefdZdedefdZddededefdZdeeeffdZy)N)ListDictOptionalAnyDB_PATHdatazfacturas_index.dbreturnctjtjjtdt j tdd}t j|_ |jd|S#t$rY|SwxYw)NT)exist_okg>@F)timeoutcheck_same_threadzPRAGMA busy_timeout = 30000;) osmakedirspathdirnamersqlite3connectRow row_factoryexecute Exceptionconns +/var/www/html/ioplay-suite/ioplay-dcm/db.pyget_connectionrsmKK(48 ??7DE JD{{D  34 K   K s+A>> B  B c t5} |jd |jd|jd|jd|jd|jd|jd|jd|jd |jdddy#t$rYwxYw#t$rYwxYw#1swYyxYw#t$r}t d |Yd}~yd}~wwxYw) NzPRAGMA journal_mode = WAL;zPRAGMA synchronous = NORMAL;a CREATE TABLE IF NOT EXISTS invoices_index ( id INTEGER PRIMARY KEY AUTOINCREMENT, customer_id TEXT NOT NULL, document_date TEXT NOT NULL, year INTEGER NOT NULL, month INTEGER NOT NULL, document_type TEXT DEFAULT 'Facturas', cableoperador TEXT, agencia TEXT, file_name TEXT NOT NULL, file_relative_path TEXT UNIQUE NOT NULL, file_full_path TEXT NOT NULL, file_size INTEGER, file_mtime REAL, indexed_at DATETIME DEFAULT CURRENT_TIMESTAMP ); z`CREATE INDEX IF NOT EXISTS idx_customer_date ON invoices_index(customer_id, document_date DESC);zWCREATE INDEX IF NOT EXISTS idx_customer_ym ON invoices_index(customer_id, year, month);ztCREATE INDEX IF NOT EXISTS idx_customer_type_date ON invoices_index(customer_id, document_type, document_date DESC);zICREATE INDEX IF NOT EXISTS idx_doc_type ON invoices_index(document_type);zNCREATE INDEX IF NOT EXISTS idx_rel_path ON invoices_index(file_relative_path);z CREATE TABLE IF NOT EXISTS indexer_meta ( key TEXT PRIMARY KEY, value TEXT, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ); z[DB] init_db warning/error: )rrrcommitprint)res rinit_dbr s,2  )   9:  ;< LL $ LL{ | LLr s LLP Q LLd e LLi j LL  KKMS) )     ) ) T 2 ,QC0112st C+CCCBC8C+ C  C C  C CCCCC($C+(C++ D 4DD ctt5}|jd|jd |jd|j ddd tj td}|jd|jy#tj$rYkwxYw#1swY`xYw#t$rYywxYw)zDLimpia todos los registros de la base de datos sin borrar las tablaszDELETE FROM invoices_index;zDELETE FROM indexer_meta;z=DELETE FROM sqlite_sequence WHERE name IN ('invoices_index');N)isolation_levelzVACUUM;) r rrrOperationalErrorrrrcloser)rraw_conns rclear_dbr&@s I  T 23 01  LLX Y  ??7DA#''        s@#B9B  B9#SELECT DISTINCT year FROM invoices_index WHERE customer_id = ?r=rAz ORDER BY year DESCr9N)r rBrCrFrErrr,)r8r;rHrIrr/r0s rget_customer_yearsrLs I LE[)//12F]+11399;CWW 77 c-(..01 ""E  :TeV,'-'89F 9::9::s%C3 C ?C CCctt5}|jdt|j g}|j Dcgc] }|ds |dc}cdddScc}w#1swYyxYw)uIRetorna los tipos documentales disponibles para un suscriptor específicozbSELECT DISTINCT document_type FROM invoices_index WHERE customer_id = ? ORDER BY document_type ASCr;N)r rrrBrCr,)r8rr/r0s rget_customer_document_typesrNs| I  [T p   # # % & 170AZSEYO$Z [[ [ [[s#=A5 A0A0$A50A55A>ctt5}|jd}|jDcgc]}|d c}cdddScc}w#1swYyxYw)zJRetorna todos los tipos documentales registrados globalmente en el sistemazSELECT DISTINCT document_type FROM invoices_index WHERE document_type IS NOT NULL AND document_type != '' ORDER BY document_type ASCr;N)r rrr,r.s rget_all_document_typesrPsd I  CT S 170ABO$B CCC CCs$A AAAAkeyvaluectt5}|jd|t|f|j dddy#1swYyxYw)NzINSERT INTO indexer_meta (key, value, updated_at) VALUES (?, ?, CURRENT_TIMESTAMP) ON CONFLICT(key) DO UPDATE SET value = excluded.value, updated_at = CURRENT_TIMESTAMP)r rrrBr)rQrRrs rset_metarTsM I  T  d #e*  s .A  Adefaultctt5}|jd|f}|j}|r|dn|cdddS#1swYyxYw)Nz,SELECT value FROM indexer_meta WHERE key = ?rR)r rrfetchone)rQrUrr/r0s rget_metarXsN I  0TLsfUoo"s7|000s ,A  Ac .tt5}|jdjd}|jdjd}t dd}t dd}t dd}|jd }|j Dcic]}|d s |d |d}}|jd } | j Dcic] }|d |d } }|||| |||d cdddScc}wcc}w#1swYyxYw)Nz,SELECT COUNT(*) as count FROM invoices_indexcountz?SELECT COUNT(DISTINCT customer_id) as count FROM invoices_indexlast_scan_timeNuncalast_scan_count0total_dir_size_byteszfSELECT document_type, COUNT(*) as count FROM invoices_index GROUP BY document_type ORDER BY count DESCr;zSELECT cableoperador, COUNT(*) as count FROM invoices_index WHERE cableoperador IS NOT NULL AND cableoperador != '' GROUP BY cableoperador ORDER BY count DESC cableoperador)total_invoicesunique_customersby_type by_companyr[last_scan_addedr_)r rrrWrXr,) rtotalrb last_scan last_counttotal_dir_size cursor_typesr0rccursor_companiesrds r get_statsrlsA I   T KLUUWX_`<<(ijssuv}~-w7 /5 !"8#>||%MN AMAVAVAXq#\_`o\p3'W5qq <<)IJDTD]D]D_`Sc/*CL8` `$ 0$')$2   ra  s0B D ! D, D7%D D, D  D  D)NNN)N)r>)rrtypingrrrrgetenvrjoinr__file__r Connectionrr r&r(rBr-r1rDr7rJrLrNrPrTrXrlrrts ,, "))Irww||BGGOOH,EvObc d**-2^ (bDe,b 4S#X#7 C H#' 88 3-8 C=8C= 8  $sCx. 8::C: :QUVYQZ: [S[T#Y[CS C#c0#00S0 4S> rs