268 lines
11 KiB
Python
268 lines
11 KiB
Python
import ast
|
|
import sqlite3
|
|
import sys
|
|
import time
|
|
import pyodbc
|
|
import json
|
|
from woocommerce import API
|
|
|
|
import all_from_woo
|
|
|
|
conn = sqlite3.connect('imd.db') # intermediary db
|
|
c = conn.cursor()
|
|
|
|
# Create table in SLQLite - we use this for syncing
|
|
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)''')
|
|
# all_from_woo.initSQLite()
|
|
|
|
# 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"
|
|
)
|
|
# Let's check if connection with WooCommerce is OK - list a sales report
|
|
# print(wcapi.get("reports/sales?date_min=2024-01-01&date_max=2024-02-04").json())
|
|
# print("Resting for for a bit...")
|
|
# time.sleep(5)
|
|
|
|
print("Connecting to MS SQL server")
|
|
'''
|
|
cnxn = pyodbc.connect('Driver={SQL Server};'
|
|
'Server=SERVER-BS;'
|
|
'Database=abacus_raster;'
|
|
'Trusted_Connection=yes;')
|
|
'''
|
|
cnxn = pyodbc.connect('DRIVER={SQL Server};'
|
|
'SERVER=raster.mywire.org,1433;'
|
|
'DATABASE=abacus_raster;'
|
|
'UID=sa;'
|
|
'PWD=!Pos456!;'
|
|
'TDS_Version=7.3;')
|
|
|
|
cursor = cnxn.cursor()
|
|
|
|
# Prvi artikal pocetnog stanja 2019 se vodi pod brojem "306570", za 2020 pod brojem "396498".
|
|
# Ako racunamo lager za artikle od njega dobicemo pravo stanje za 2019. Ukoliko ovo ne uradimo,
|
|
# lager se racuna od pocetka rada sa Abacusom - i vodi do gresaka.
|
|
# prvo nadjemo id_document pocetnog stanja (prvog dokumenta u godini)
|
|
# /****** Script for SelectTopNRows command from SSMS ******/
|
|
# SELECT id_dokument, id_vrsta, broj_dok, datum
|
|
# FROM abacus_raster.dbo.artikal_dokument
|
|
# WHERE (datum > CONVERT(DATETIME, '2020-12-31 00:00:00', 102))
|
|
#
|
|
# A onda nadjemo artikal u lageru - Stari kod:
|
|
# SELECT id_artikal, multiply * kol AS qty
|
|
# FROM abacus_raster.dbo.artikal_dnevnik
|
|
# WHERE (multiply <> 0) AND id_dnevnik >=396498.
|
|
cursor.execute('''SELECT a.id_artikal,
|
|
SUM (a.qty) AS stanje
|
|
INTO tmplager
|
|
FROM (SELECT artikal_dnevnik.id_artikal,
|
|
artikal_dnevnik.multiply*artikal_dnevnik.kol AS 'qty'
|
|
FROM abacus_raster.dbo.artikal_dnevnik artikal_dnevnik, abacus_raster.dbo.artikal_dokument artikal_dokument
|
|
WHERE artikal_dnevnik.id_dokument = artikal_dokument.id_dokument AND ((artikal_dokument.datum_fak>={d '2024-01-01'})) AND (artikal_dokument.id_vrsta <> 18) ) \
|
|
AS a INNER JOIN
|
|
abacus_raster.dbo.artikal ON a.id_artikal = abacus_raster.dbo.artikal.id_artikal
|
|
GROUP BY a.id_artikal ''')
|
|
|
|
cursor.execute('''SELECT abacus_raster.dbo.artikal_cijenik.id_artikal, abacus_raster.dbo.artikal_cijenik.cijena,
|
|
abacus_raster.dbo.artikal.naziv, abacus_raster.dbo.artikal.sifra,
|
|
tmplager.stanje, abacus_raster.dbo.artikal.id_grupa, tmplager.id_artikal AS tid
|
|
FROM abacus_raster.dbo.artikal_cijenik INNER JOIN
|
|
abacus_raster.dbo.artikal INNER JOIN
|
|
tmplager ON abacus_raster.dbo.artikal.id_artikal = tmplager.id_artikal ON
|
|
abacus_raster.dbo.artikal_cijenik.id_artikal = abacus_raster.dbo.artikal.id_artikal
|
|
WHERE (abacus_raster.dbo.artikal_cijenik.id_mjesto = 2) AND abacus_raster.dbo.artikal.id_grupa <> 11''')
|
|
# Save products from Abacus in SQLite table
|
|
row = cursor.fetchone()
|
|
print("Abacus row in works:"), row #
|
|
snc = 0
|
|
for row in cursor:
|
|
while row is not None:
|
|
# print("Abacus row entered for loop & it is not None")
|
|
# input("Press Enter to continue...") #
|
|
aid = row[0]
|
|
aprice = float(row[1])
|
|
aname = row[2]
|
|
asifra = str(row[3])
|
|
aqty = int(row[4])
|
|
agroup = row[5]
|
|
# print("MS Sql group value:", agroup)
|
|
# This dictionary matches abacus product category id to equivalent id in woocommerce
|
|
groupdict = {
|
|
1: 21472, 2: 21473, 4: 21474, 5: 21475, 10: 21476, 11: 21477, 13: 21477, 15: 21477, 19: 21478, 24: 21479,
|
|
30: 21480, 32: 21481, 33: 21482, 37: 21483, 38: 21484, 44: 21485, 45: 21486, 47: 21477, 48: 21477
|
|
}
|
|
# print("Coverted to Woocommerce group it becomes wgroup:", groupdict[agroup])
|
|
if agroup in groupdict:
|
|
wgroup = groupdict[agroup]
|
|
# print("Group in abacusu is from dictionary:", agroup, " - while woocommerce now holds:", wgroup) #
|
|
else:
|
|
wgroup = 3557
|
|
# print("Group ", agroup, "does not exist in dictionary - we put it in woocommerce group -other-:", wgroup) #
|
|
|
|
# Test id Woo product exists in SQLite table
|
|
q = (asifra,)
|
|
c.execute('SELECT * FROM products WHERE sifra=?', q)
|
|
# print("Abacus data to be saved in SQLite:", aid, aprice, aname, asifra, aqty, wgroup)
|
|
# input("Press Enter to continue...") #
|
|
# We test SqlLite rows so that we update the table only with product that changed
|
|
trow = c.fetchone()
|
|
if trow is not None:
|
|
tnaziv = trow[3]
|
|
tprice = trow[4]
|
|
tqty = trow[5]
|
|
tgroup = trow[7]
|
|
# print("There is an SQLite trow we need to check for an update: "), tnaziv, tprice, tqty, tgroup
|
|
if aprice != tprice:
|
|
snc = 1
|
|
print(aname, "has a bad price")
|
|
elif aqty != tqty:
|
|
snc = 1
|
|
print(aname, "has a bad qty")
|
|
elif aname != tnaziv:
|
|
snc = 1
|
|
print(aname, "has bad naziv: ", tnaziv)
|
|
elif wgroup != tgroup:
|
|
snc = 1
|
|
print(wgroup, "has a bad tgroup: ", tgroup)
|
|
else:
|
|
snc = 0
|
|
if snc == 1:
|
|
print(asifra, aname, snc)
|
|
c.execute('''UPDATE products SET aid = ?, price = ?, qty =?, naziv = ?,
|
|
snc = ?, grupa = ? WHERE sifra = ?''',
|
|
(aid, aprice, aqty, aname, snc, wgroup, asifra))
|
|
conn.commit()
|
|
c.execute('SELECT * FROM products WHERE sifra=?', q)
|
|
trow = c.fetchone()
|
|
# print(trow)
|
|
# print("=======")
|
|
# input("Press Enter to continue...")
|
|
else:
|
|
print("If sqlite trow does not exist (is ", trow, "), then first create one in Woo", aid, asifra, aname, aprice, aqty, wgroup)
|
|
# input("Press Enter to continue...") #
|
|
aprice = str(aprice)
|
|
data = {
|
|
"sku": asifra,
|
|
"name": aname,
|
|
"regular_price": aprice,
|
|
"description": "",
|
|
"managing_stock": "true",
|
|
"in_stock": "true",
|
|
"status": "publish",
|
|
"stock_quantity": aqty,
|
|
"categories": [
|
|
{
|
|
"id": wgroup
|
|
}
|
|
],
|
|
}
|
|
w = wcapi.post("products", data).json()
|
|
# w = wcapi.get("products/categories").json()
|
|
|
|
class ListStream:
|
|
def __init__(self):
|
|
self.data = []
|
|
|
|
def write(self, s):
|
|
self.data.append(s)
|
|
|
|
|
|
sys.stdout = x = ListStream()
|
|
print(w)
|
|
sys.stdout = sys.__stdout__
|
|
wresponse = (x.data[0])
|
|
print("Full woo response: ", wresponse)
|
|
# print("wresponse:", wresponse[38:53])
|
|
if wresponse[38:53] == "duplicirani SKU":
|
|
# if wresponse[26:40] == "duplicated SKU":
|
|
d = ast.literal_eval(wresponse)
|
|
errd = d.get('data')
|
|
print(errd)
|
|
rogueone = errd.get('resource_id')
|
|
print("We have a duplicated product in Woo: ", rogueone)
|
|
c.execute('INSERT INTO products VALUES (?,?,?,?,?,?,?,?)',
|
|
(rogueone, aid, asifra, aname, aprice, aqty, 1, wgroup)) # if no product it will create
|
|
print("Inserting rogueone in SQLlite data:", rogueone, aid, asifra, aname, aprice, aqty, 1, wgroup)
|
|
conn.commit()
|
|
# rgpath = "\"product_batch/" + str(rogueone) + "\""
|
|
# print rgpath
|
|
# rg = wcapi.get(rogueone).json()
|
|
# print("The rogueone:", rg)
|
|
else:
|
|
print("Since trow was NONE, we had to create it on woo, get the id (woo), then save it (SQLite):",
|
|
type(w), w)
|
|
wid = w.get('id')
|
|
wsku = w.get('sku')
|
|
wname = w.get('name')
|
|
wprice = w.get('price')
|
|
wqty = w.get('qty')
|
|
if wname is None:
|
|
print("Why problems? ...:", wid, aid, wsku, wname, wprice, wqty)
|
|
else:
|
|
c.execute('INSERT INTO products VALUES (?,?,?,?,?,?,?,"")',
|
|
(wid, aid, wsku, wname, wprice, wqty, 1)) # if no product it will create
|
|
conn.commit()
|
|
row = cursor.fetchone()
|
|
# print("Resting for for a bit...")
|
|
# time.sleep(5)
|
|
conn.close()
|
|
print("Wait before sending")
|
|
# time.sleep(10)
|
|
|
|
# Update Woo with our SQLite table
|
|
conn = sqlite3.connect('imd.db') # intermediary db
|
|
c = conn.cursor()
|
|
c.execute('''SELECT * FROM products WHERE snc = 1 ORDER BY naziv''')
|
|
grupa = 0
|
|
|
|
# Make a product_list
|
|
product_list = list()
|
|
trow = c.fetchone()
|
|
while trow is not None:
|
|
# count += 1
|
|
# print("Selected for update:", trow)
|
|
tid = trow[0]
|
|
tnaziv = trow[3]
|
|
tprice = str(trow[4])
|
|
tqty = trow[5]
|
|
tgroup = trow[7]
|
|
# product = [tid, tqty, tnaziv, tprice, tgroup]
|
|
product = {'id': tid, 'manage_stock': 'true', 'stock_quantity': tqty, 'name': tnaziv, 'status': 'publish', 'regular_price': tprice, 'categories': [{'id': tgroup}]}
|
|
# print(product)
|
|
product_list.append(product)
|
|
trow = c.fetchone()
|
|
print(product_list)
|
|
# Make batches of products and send them to WooCommerce
|
|
l = len(product_list)
|
|
print("Lenght: ", l)
|
|
batch_size = 100
|
|
|
|
for i in range(0, l, batch_size):
|
|
one_product_batch = product_list[i:i + batch_size]
|
|
data = {'update': one_product_batch}
|
|
# print(one_product_batch)
|
|
print("_________________________BATCH", i, "_________________________")
|
|
response = wcapi.post("products/batch", data).json()
|
|
print(response)
|
|
|
|
c.execute('UPDATE products SET snc = 0')
|
|
conn.commit()
|
|
|
|
# Drop the temporary table
|
|
|
|
print("Preparing to drop the tmptable")
|
|
cursor.execute('''drop table tmplager''')
|
|
print("Dropped the tmptable")
|
|
cursor.close()
|
|
|
|
del cursor
|
|
conn.close() # close sqlite connection
|
|
print("All done!")
|