80 lines
3.5 KiB
Python
80 lines
3.5 KiB
Python
def initSQLite():
|
|
# The following will download all existing woocommerce products and save them in SQLite.
|
|
# From that point the synchronization can start in local.
|
|
from woocommerce import API
|
|
import sqlite3
|
|
import pyodbc
|
|
|
|
conn = sqlite3.connect('imd.db') # intermediary db
|
|
c = conn.cursor()
|
|
# Create table in SLQLite - we use this for syncing
|
|
c.execute('DROP TABLE products')
|
|
c.execute('''CREATE TABLE IF NOT EXISTS products (id INTEGER NOT NULL PRIMARY KEY ON CONFLICT IGNORE, aid INTEGER,
|
|
sifra TEXT, naziv TEXT, price REAL, qty INTEGER, snc INTEGER, grupa INTEGER)''')
|
|
|
|
|
|
# Connect to WooCommerce API
|
|
wcapi = API(
|
|
url="https://www.rasterdoo.com/",
|
|
consumer_key="ck_b3169b1723f6f39a965ed04ecfa77860fb89bbf5",
|
|
consumer_secret="cs_a83f0217ed8d6191ab7de9df06a0c2b652f6bd57",
|
|
wp_api=True,
|
|
timeout=360,
|
|
version="wc/v3"
|
|
)
|
|
r = wcapi.get("products") # get woo web page where products are
|
|
h = int(r.headers['X-WP-TotalPages']) # in the header we see number of tot pages
|
|
print("Total pages:", h)
|
|
|
|
for page_no in range(1, h+1): # lets go through pages
|
|
goto_page = 'products?page=' + str(page_no)
|
|
print(page_no, "(", h, ")")
|
|
r = wcapi.get(goto_page)
|
|
page_txt = r.json() # contents of one page ar stored in variable
|
|
# We fill our SQLite table with Woo Products
|
|
for product in page_txt: # first we go through product in this page
|
|
wsku = product.get('sku')
|
|
wid = product.get('id')
|
|
wname = product.get('name')
|
|
wprice = product.get('regular_price')
|
|
wqty = product.get('stock_quantity')
|
|
if wqty is None:
|
|
wqty = 0
|
|
# FIX THIS! - It does not update - just inserts if new ...
|
|
c.execute('INSERT INTO products VALUES (?,?,?,?,?,?,?,?)',
|
|
(wid, "", wsku, wname, wprice, wqty, 0, "")) # if no product it will create
|
|
c.execute('UPDATE products SET aid = ?, sifra = ?, naziv = ?, price = ?, qty = ?, snc = ? WHERE id = ?',
|
|
("", wsku, wname, wprice, wqty, 0, wid))
|
|
print("Insert woocommerce data to SQLlite:", wid, "", wsku, wname, wprice, wqty, 0)
|
|
conn.commit()
|
|
|
|
print("")
|
|
print("Fixing some problems with decimals...")
|
|
c.execute('''UPDATE products SET price = replace( price, ',', '.' ) WHERE price LIKE ?''', ('%,%',))
|
|
conn.commit()
|
|
|
|
# Sometimes woo has a product, but abacus does not, meaning we don't have them anymore. SO, qty needs to be 0.
|
|
print("Checking if products on woo exist on abacus")
|
|
# list of woo products
|
|
c.execute('''SELECT sifra, naziv, qty FROM products''')
|
|
trow = c.fetchone()
|
|
# find them on abacus
|
|
cnxn = pyodbc.connect('Driver={SQL Server};'
|
|
'Server=SERVER-BS;'
|
|
'Database=abacus_raster;'
|
|
'Trusted_Connection=yes;')
|
|
cursor = cnxn.cursor()
|
|
|
|
while trow is not None:
|
|
sifra = trow[0]
|
|
naziv = trow[1]
|
|
cursor.execute('''SELECT id_artikal, sifra, naziv FROM abacus_raster.dbo.artikal WHERE sifra = ?''', (sifra, ))
|
|
row = cursor.fetchone()
|
|
if row is None:
|
|
print(sifra, naziv, " - does not exist on abacus, at least we should set qty to 0")
|
|
c.execute('''UPDATE products SET qty = 0 WHERE sifra = ?''', (sifra,))
|
|
conn.commit()
|
|
trow = c.fetchone()
|
|
|
|
conn.close()
|