152 lines
6.9 KiB
Python
152 lines
6.9 KiB
Python
# -*- coding: utf-8 -*-
|
|
"""
|
|
pyjeeves.jvsquery
|
|
~~~~~~~~~~~~~~~~~~~~~~
|
|
|
|
Jeeves data queries
|
|
"""
|
|
import pymssql
|
|
import datetime
|
|
import logging
|
|
|
|
|
|
class JvsQuery():
|
|
"""JvsQuery based on http://pymssql.org/en/stable/ """
|
|
def __init__(self, settings):
|
|
super(JvsQuery, self).__init__()
|
|
self.settings = settings
|
|
self.logger = logging.getLogger("PyJeeves.jvsquery")
|
|
|
|
def _execute(self, query="", params=(), iterator=True):
|
|
with pymssql.connect(**self.settings) as conn:
|
|
with conn.cursor(as_dict=True) as cursor:
|
|
cursor.execute(query, params)
|
|
if iterator:
|
|
for row in cursor:
|
|
if cursor.rownumber % 1000 == 0 and cursor.rownumber != 0:
|
|
self.logger.debug("Cursor is at pos %d" % cursor.rownumber)
|
|
yield row
|
|
else:
|
|
return cursor.fetchall()
|
|
|
|
def _ft(self, updated_dt='2000-01-01 00:00:00.000',
|
|
created_dt='2000-01-01 00:00:00.000', limit=None):
|
|
query = (
|
|
"""SELECT FaktNr, FaktRadnr, ForetagKod, FtgNr, OrderNr, OrdTyp, Saljare,
|
|
KundKategoriKod, ArtNr, EnhetsKod, VaruGruppKod, Redovisnar, Period, FaktTB,
|
|
FaktTG, FaktLevAnt, FaktLevAntAltEnh, FPris, FaktRadSumma, ValKod, ValKurs,
|
|
RowCreatedDt, RowUpdatedDt, RowUpdatedBy, FaktDat
|
|
FROM ft
|
|
WHERE ft.ForetagKod = 1
|
|
AND (ft.RowCreatedDt > %(created_dt)s
|
|
OR ft.RowUpdatedDt > %(updated_dt)s)""")
|
|
params = {'created_dt': created_dt, 'updated_dt': updated_dt}
|
|
|
|
return self._execute(query, params)
|
|
|
|
def _orp(self, updated_dt='2000-01-01 00:00:00.000',
|
|
created_dt='2000-01-01 00:00:00.000', limit=None):
|
|
query = (
|
|
"""SELECT orp.OrderNr, OrdRadnr, OrdRadNrStrPos, orp.OrdRestNr, orp.ForetagKod,
|
|
ArtNr, orp.FtgNr, vb_pris,
|
|
OrdAntal, OrdAntalAltEnh, AltEnhetKod, OrdLevAntal, OrdLevAntalAltEnh,
|
|
orp.FaktNr, orp.OrdDatum, orp.OrdBerLevDat, orp.OrdBerednDat, orp.OrdLevDat,
|
|
orp.RowUpdatedBy, orp.RowUpdatedDt, orp.RowCreatedBy, orp.RowCreatedDt,
|
|
salj.Saljare, salj.SaljareNamn,
|
|
xs.OrdRadSt, xs.OrdRStatBeskr, x6.OrdTyp, x6.OrdTypBeskr
|
|
FROM orp
|
|
LEFT OUTER JOIN oh ON oh.OrderNr = orp.OrderNr
|
|
AND oh.ForetagKod = orp.ForetagKod
|
|
LEFT OUTER JOIN salj ON salj.Saljare = orp.Saljare
|
|
AND salj.ForetagKod = orp.ForetagKod
|
|
LEFT OUTER JOIN xs ON xs.OrdRadSt = orp.OrdRadSt
|
|
AND xs.ForetagKod = orp.ForetagKod
|
|
LEFT OUTER JOIN x6 ON x6.OrdTyp = orp.OrdTyp
|
|
AND x6.ForetagKod = orp.ForetagKod
|
|
WHERE orp.ForetagKod = 1
|
|
AND (oh.RowCreatedDt > %(created_dt)s
|
|
OR oh.RowUpdatedDt > %(updated_dt)s
|
|
OR salj.RowCreatedDt > %(created_dt)s
|
|
OR salj.RowUpdatedDt > %(updated_dt)s
|
|
OR xs.RowCreatedDt > %(created_dt)s
|
|
OR xs.RowUpdatedDt > %(updated_dt)s
|
|
OR x6.RowCreatedDt > %(created_dt)s
|
|
OR x6.RowUpdatedDt > %(updated_dt)s)""")
|
|
params = {'created_dt': created_dt, 'updated_dt': updated_dt}
|
|
|
|
return self._execute(query, params)
|
|
|
|
def _ar(self, limit=None):
|
|
query = (
|
|
"""SELECT ArtNr, ar.VaruGruppKod, VaruGruppBeskr, ArtBeskr, ArtBeskr2, ArtBeskrSpec,
|
|
ar.ArtProdKlass, ArtProdklBeskr, ar.ArtProdKonto, ArtProdKontoBeskr, ar.ArtKod,
|
|
ArtTypBeskr, LagTyp, EnhetsKod, LevNr, ItemStatusCode, LagSaldoArtikel,
|
|
ar.RowUpdatedBy, ar.RowUpdatedDt, ar.RowCreatedBy, ar.RowCreatedDt, ar.ForetagKod
|
|
FROM ar
|
|
LEFT OUTER JOIN arpk ON ar.ArtProdKonto = arpk.ArtprodKonto
|
|
AND ar.ForetagKod = arpk.ForetagKod
|
|
LEFT OUTER JOIN vg ON ar.VaruGruppKod = vg.VaruGruppKod
|
|
AND ar.ForetagKod = vg.ForetagKod
|
|
AND vg.SprakKod = 0
|
|
LEFT OUTER JOIN xp ON ar.ArtProdKlass = xp.ArtProdKlass
|
|
AND ar.ForetagKod = xp.ForetagKod
|
|
LEFT OUTER JOIN xm ON ar.ArtKod = xm.ArtKod
|
|
AND ar.ForetagKod = xm.ForetagKod
|
|
WHERE ar.ForetagKod = 1""")
|
|
|
|
return self._execute(query)
|
|
|
|
def _kus(self, updated_dt='2000-01-01 00:00:00.000',
|
|
created_dt='2000-01-01 00:00:00.000', limit=None):
|
|
query = (
|
|
"""SELECT kus.FtgNr, BetKod, kus.kundbetalarenr, kus.RowUpdatedBy, kus.RowUpdatedDt,
|
|
kus.RowCreatedBy, kus.RowCreatedDt, kus.ForetagKod, kus.MakDateTime,
|
|
CAST(kus.Makulerad AS int) as Makulerad,
|
|
x1k.KundKategoriKod, x1k.KundKatBeskr,
|
|
x1kk.Kundklass, x1kk.KundKlassBeskr,
|
|
salj.Saljare, salj.SaljareNamn,
|
|
OrgNr, FtgNamn, FtgPostAdr1, FtgPostAdr2, FtgPostAdr3,
|
|
FtgPostadr4, FtgPostadr5, FtgPostnr, fr.LandsKod, FtgPostLevAdr3, FtgLevPostNr
|
|
FROM kus
|
|
LEFT OUTER JOIN fr ON fr.FtgNr = kus.FtgNr
|
|
AND fr.ForetagKod = kus.ForetagKod
|
|
LEFT OUTER JOIN x1k ON x1k.KundKategoriKod = kus.kundkategorikod
|
|
AND x1k.ForetagKod = kus.ForetagKod
|
|
LEFT OUTER JOIN x1kk ON x1kk.Kundklass = kus.kundklass
|
|
AND x1kk.ForetagKod = kus.ForetagKod
|
|
LEFT OUTER JOIN salj ON salj.Saljare = kus.Saljare
|
|
AND salj.ForetagKod = kus.ForetagKod
|
|
WHERE kus.ForetagKod = 1
|
|
AND (kus.RowCreatedDt > %(created_dt)s
|
|
OR kus.RowUpdatedDt > %(updated_dt)s
|
|
OR fr.RowCreatedDt > %(created_dt)s
|
|
OR fr.RowUpdatedDt > %(updated_dt)s
|
|
OR x1k.RowCreatedDt > %(created_dt)s
|
|
OR x1k.RowUpdatedDt > %(updated_dt)s
|
|
OR x1kk.RowCreatedDt > %(created_dt)s
|
|
OR x1kk.RowUpdatedDt > %(updated_dt)s
|
|
OR salj.RowCreatedDt > %(created_dt)s
|
|
OR salj.RowUpdatedDt > %(updated_dt)s)""")
|
|
params = {'created_dt': created_dt, 'updated_dt': updated_dt}
|
|
|
|
return self._execute(query, params)
|
|
|
|
def get(self, jvs_tbl, updated_dt, created_dt):
|
|
if not updated_dt:
|
|
updated_dt = datetime.date(2000, 1, 1)
|
|
if not created_dt:
|
|
created_dt = datetime.date(2000, 1, 1)
|
|
|
|
updated_dt = updated_dt.strftime("%Y-%m-%d %H:%M:%S")
|
|
created_dt = created_dt.strftime("%Y-%m-%d %H:%M:%S")
|
|
|
|
if jvs_tbl == 'Articles':
|
|
return self._ar()
|
|
elif jvs_tbl == 'Customers':
|
|
return self._kus(updated_dt, created_dt)
|
|
elif jvs_tbl == 'InvoiceRows':
|
|
return self._ft(updated_dt, created_dt)
|
|
elif jvs_tbl == 'OrderRows':
|
|
return self._orp(updated_dt, created_dt)
|
|
else:
|
|
self.logger.warning("%s table has no get query" % jvs_tbl)
|