o }sÃil?ã@s ddlZddlZddlmZdZdd„Zdd„Zded efd d „Z    dJd ed ed ededededede fdd„Z d edefdd„Z dKd efdd„Z d efdd„Z d ed efdd„Zd ed ed efdd„Zd eded efd d!„Zd ed efd"d#„Zd ed efd$d%„Zd efd&d'„Zd(ed efd)d*„Zd+ed efd,d-„Zd edefd.d/„Zd0ed efd1d2„Zd3ed efd4d5„Zd efd6d7„Zd3ed8ed efd9d:„Zd0ed efd;d<„Zd ede fd=d>„ZdKd?ed efd@dA„Zd?edBed efdCdD„Zd ede fdEdF„Z!dGe d efdHdI„Z"dS)LéN)Údatetimea3 CREATE TABLE IF NOT EXISTS orders ( number TEXT PRIMARY KEY, date TEXT, status TEXT, buyerName TEXT, buyerName1 TEXT, buyerStreet TEXT, buyerPostalCode TEXT, buyerCity TEXT, buyerCountry TEXT, buyerEMail TEXT, buyerPhone TEXT, buyerTaxNumber TEXT, deliveryName TEXT, deliveryStreet TEXT, deliveryPostalCode TEXT, deliveryCity TEXT, deliveryCountry TEXT, items TEXT, deliveryMethod TEXT, methodOfPayment TEXT, totalAmountInDomCurr REAL, documentCurrency TEXT, remarks TEXT, shopify_id TEXT, shopify_order_name TEXT, shopify_customer_name TEXT, shopify_financial_status TEXT, shopify_fulfillment_status TEXT, language TEXT, updated_at TEXT DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS products ( productCode TEXT PRIMARY KEY, name TEXT, englishName TEXT, germanName TEXT, barcode TEXT, defaultWarehouseLocation TEXT ); CREATE TABLE IF NOT EXISTS skipped_orders ( order_number TEXT PRIMARY KEY ); CREATE TABLE IF NOT EXISTS users ( id INTEGER PRIMARY KEY AUTOINCREMENT, email TEXT UNIQUE NOT NULL, password_hash TEXT NOT NULL, created_at TEXT DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS settings ( key TEXT PRIMARY KEY, value TEXT ); c Csbt |¡}| t¡dD]\}}z | d|›d|›¡Wq ty&Yq w| ¡| ¡dS)N) )ÚlanguageÚTEXT)Ú deliveryNamer)ÚdeliveryStreetr)ÚdeliveryPostalCoder)Ú deliveryCityr)ÚdeliveryCountryr)Úshopify_order_namer)Úshopify_customer_namer)Úshopify_financial_statusr)Úshopify_fulfillment_statusrzALTER TABLE orders ADD COLUMN ú )Úsqlite3ÚconnectÚ executescriptÚSCHEMAÚexecuteÚ ExceptionÚcommitÚclose)Údb_pathÚconÚcolÚ definition©rú)/var/www/warehouse.soldered.com/app/db.pyÚinit_dbCs     ÿ rcCst |¡}tj|_|S©N)rrÚRowÚ row_factory)rrrrrÚget_db[s r!ÚordersrcCs‚t|ƒ}z·|D]©}| d¡}t|ttfƒrt |¡}| did| d¡“d| d¡“d| d¡“d| d¡“d| d¡“d| d¡“d | d ¡“d | d ¡“d | d ¡“d | d ¡“d | d ¡“d| d¡“d| dd¡“d| dd¡“d| dd¡“d| dd¡“d| dd¡“|| d¡| d¡| d¡| d¡| d¡| d¡| d¡t  ¡  ¡dœ ¥¡q|  ¡W|  ¡dS|  ¡w)NÚitemsa? INSERT INTO orders ( number, date, status, buyerName, buyerName1, buyerStreet, buyerPostalCode, buyerCity, buyerCountry, buyerEMail, buyerPhone, buyerTaxNumber, deliveryName, deliveryStreet, deliveryPostalCode, deliveryCity, deliveryCountry, items, deliveryMethod, methodOfPayment, totalAmountInDomCurr, documentCurrency, remarks, shopify_id, language, updated_at ) VALUES ( :number, :date, :status, :buyerName, :buyerName1, :buyerStreet, :buyerPostalCode, :buyerCity, :buyerCountry, :buyerEMail, :buyerPhone, :buyerTaxNumber, :deliveryName, :deliveryStreet, :deliveryPostalCode, :deliveryCity, :deliveryCountry, :items, :deliveryMethod, :methodOfPayment, :totalAmountInDomCurr, :documentCurrency, :remarks, :shopify_id, :language, :updated_at ) ON CONFLICT(number) DO UPDATE SET date=excluded.date, status=excluded.status, buyerName=excluded.buyerName, buyerName1=excluded.buyerName1, buyerStreet=excluded.buyerStreet, buyerPostalCode=excluded.buyerPostalCode, buyerCity=excluded.buyerCity, buyerCountry=excluded.buyerCountry, buyerEMail=excluded.buyerEMail, buyerPhone=excluded.buyerPhone, buyerTaxNumber=excluded.buyerTaxNumber, deliveryName=excluded.deliveryName, deliveryStreet=excluded.deliveryStreet, deliveryPostalCode=excluded.deliveryPostalCode, deliveryCity=excluded.deliveryCity, deliveryCountry=excluded.deliveryCountry, items=excluded.items, deliveryMethod=excluded.deliveryMethod, methodOfPayment=excluded.methodOfPayment, totalAmountInDomCurr=excluded.totalAmountInDomCurr, documentCurrency=excluded.documentCurrency, remarks=excluded.remarks, shopify_id=COALESCE(excluded.shopify_id, orders.shopify_id), language=excluded.language, updated_at=excluded.updated_atÚnumberÚdateÚstatusÚ buyerNameÚ buyerName1Ú buyerStreetÚbuyerPostalCodeÚ buyerCityÚ buyerCountryÚ buyerEMailÚ buyerPhoneÚbuyerTaxNumberrÚrrrr ÚdeliveryMethodÚmethodOfPaymentÚtotalAmountInDomCurrÚdocumentCurrencyÚremarksÚ shopify_idr) r#r1r2r3r4r5r6rÚ updated_at) r!ÚgetÚ isinstanceÚdictÚlistÚjsonÚdumpsrrÚutcnowÚ isoformatrr)r"rrÚorderr#rrrÚ upsert_orderscsp  $ ÿ þ ý ü û ú ù ø ÷ ö õ ô ó ò ñ ð ï æÛ BrAr$r6r r r r Úreturnc CsHt|ƒ}z| d||||||f¡}| ¡|jdkW| ¡S| ¡w)u~Update shopify_id (and optional Shopify metadata) for an order by its e-raÄuni number. Returns True if the order existed.a•UPDATE orders SET shopify_id = ?, shopify_order_name = COALESCE(?, shopify_order_name), shopify_customer_name = COALESCE(?, shopify_customer_name), shopify_financial_status = COALESCE(?, shopify_financial_status), shopify_fulfillment_status = COALESCE(?, shopify_fulfillment_status) WHERE number = ?r)r!rrÚrowcountr) r$r6rr r r r rÚcurrrrÚset_order_shopify_id±sÿø rEcCó:t|ƒ}z| d¡ ¡}dd„|DƒW| ¡S| ¡w)zOReturn all orders (with or without a Shopify link), ordered by date descending.anSELECT number, date, buyerName, status, shopify_id, shopify_order_name, shopify_customer_name, shopify_financial_status, shopify_fulfillment_status, totalAmountInDomCurr, documentCurrency FROM orders WHERE status != 'Draft' ORDER BY date DESC, number DESCcSóg|]}t|ƒ‘qSr©r:©Ú.0ÚrrrrÚ Øóz%get_sync_mappings..©r!rÚfetchallr©rrÚrowsrrrÚget_sync_mappingsËsÿø rRcCsf|durdg}t|ƒ}z"d dd„|Dƒ¡}| d|›d|¡ ¡}dd„|DƒW| ¡S| ¡w) NÚDraftú,css|]}dVqdS)ú?Nr)rJÚ_rrrÚ âs€z!get_all_orders..z*SELECT * FROM orders WHERE status NOT IN (z!) ORDER BY date DESC, number DESCcSrGrrHrIrrrrLçrMz"get_all_orders..)r!ÚjoinrrOr)rÚexclude_statusesrÚ placeholdersrQrrrÚget_all_ordersÝs þýr[cCrF)NzîSELECT o.* FROM orders o WHERE o.status = 'Processing' AND o.number LIKE '%S%' AND o.number NOT IN (SELECT order_number FROM skipped_orders) ORDER BY o.date ASC, o.number ASCcSrGrrHrIrrrrLörMz)get_processing_orders..rNrPrrrÚget_processing_ordersìsÿúr\cCó@t|ƒ}z| d|f¡ ¡}|rt|ƒndW| ¡S| ¡w)Nz%SELECT * FROM orders WHERE number = ?©r!rÚfetchoner:r)r$rrÚrowrrrÚ get_orderûó racCs:t|ƒ}z| d||f¡| ¡W| ¡dS| ¡w)Nz1UPDATE orders SET shopify_id = ? WHERE number = ?©r!rrr)r$r6rrrrrÚupdate_order_shopify_ids  rdr&cCsDt|ƒ}z| d|t ¡ ¡|f¡| ¡W| ¡dS| ¡w)Nz=UPDATE orders SET status = ?, updated_at = ? WHERE number = ?)r!rrr>r?rr)r$r&rrrrrÚupdate_order_status sþ recCó8t|ƒ}z| d|f¡| ¡W| ¡dS| ¡w)Nz>INSERT OR IGNORE INTO skipped_orders (order_number) VALUES (?)rc©r$rrrrrÚ skip_orderó  rhcCrf)Nz1DELETE FROM skipped_orders WHERE order_number = ?rcrgrrrÚ unskip_order"rirjcCrF)Nz'SELECT order_number FROM skipped_orderscSsg|]}|d‘qS)Ú order_numberrrIrrrrL/rMz&get_skipped_orders..rNrPrrrÚget_skipped_orders+ó rlÚproductsc Csrt|ƒ}z/|D]!}| d| d¡| d¡| d¡| d¡| d¡| d¡dœ¡q| ¡W| ¡dS| ¡w) NzòINSERT OR REPLACE INTO products (productCode, name, englishName, germanName, barcode, defaultWarehouseLocation) VALUES (:productCode, :name, :englishName, :germanName, :barcode, :defaultWarehouseLocation)Ú productCodeÚnameÚ englishNameÚ germanNameÚbarcodeÚdefaultWarehouseLocation)rorprqrrrsrt)r!rr8rr)rnrrÚprrrÚupsert_products6súü rvÚ product_codecCr])Nz,SELECT * FROM products WHERE productCode = ?r^)rwrrr`rrrÚ get_productLrbrxcCrF)Nz/SELECT * FROM products ORDER BY productCode ASCcSrGrrHrIrrrrLYrMz$get_all_products..rNrPrrrÚget_all_productsUrmryÚuser_idcCr])Nz SELECT * FROM users WHERE id = ?r^)rzrrr`rrrÚget_user_by_id`rbr{ÚemailcCr])Nz#SELECT * FROM users WHERE email = ?r^)r|rrr`rrrÚget_user_by_emailirbr}cCrF)Nz?SELECT id, email, created_at FROM users ORDER BY created_at ASCcSrGrrHrIrrrrLvrMz!get_all_users..rNrPrrrÚ get_all_usersrrmr~Ú password_hashcCó:t|ƒ}z| d||f¡| ¡W| ¡dS| ¡w)Nz@INSERT OR IGNORE INTO users (email, password_hash) VALUES (?, ?)rc)r|rrrrrrÚ create_user{óþ rcCrf)NzDELETE FROM users WHERE id = ?rc)rzrrrrrÚ delete_user‡rirƒcCs8t|ƒ}z| d¡ ¡}|ddkW| ¡S| ¡w)z0Return True if at least one user account exists.zSELECT COUNT(*) as c FROM usersÚcr©r!rr_r)rrr`rrrÚ user_existss  r†ÚkeycCs@t|ƒ}z| d|f¡ ¡}|r|dn|W| ¡S| ¡w)Nz(SELECT value FROM settings WHERE key = ?Úvaluer…)r‡rÚdefaultrr`rrrÚ get_settingœrbrŠrˆcCr€)Nú:INSERT OR REPLACE INTO settings (key, value) VALUES (?, ?)rc)r‡rˆrrrrrÚ set_setting¥r‚rŒcCrF)z$Return all settings as a plain dict.zSELECT key, value FROM settingscSsi|] }|d|d“qS)r‡rˆrrIrrrÚ ¶sz$get_all_settings..rNrPrrrÚget_all_settings±s rŽÚdatacCsTt|ƒ}z | ¡D]\}}|dkr| d||f¡q | ¡W| ¡dS| ¡w)u>Bulk-save a dict of key→value pairs, skipping empty strings.r0r‹N)r!r#rrr)rrrr‡rˆrrrÚ save_settings»sþ€ r)NNNNr)#rr<rrrr!r;ÚstrrAÚboolrErRr[r\rardrerhrjrlrvrxryÚintr{r}r~rrƒr†rŠrŒr:rŽrrrrrÚsX =Oüÿþýü ü