def getValues(self, data):
self.grp = CCD(data[0], "UA", 3)
code = CCD(data[1], "NA", 20)
desc = CCD(data[2], "NA", 30)
uoi = CCD(data[3], "NA", 10)
bals = Balances(self.opts["mf"],
"STR",
self.opts["conum"],
self.cutoff,
keys=(self.grp.work, code.work, self.loc,
("P", self.opts["period"][0])))
m_ob, m_mv, m_cb, y_ob, y_mv, y_cb, ac, lc, ls = bals.doStrBals()
m_qty = 0
m_cst = 0
m_sls = 0
for t, q, c, s in m_mv:
if t == 7:
m_qty = q
m_cst = c
m_sls = s
break
y_qty = 0
y_cst = 0
y_sls = 0
for t, q, c, s in y_mv:
if t == 7:
y_qty = q
y_cst = c
y_sls = s
break
mq = CCD(float(ASD(0) - ASD(m_qty)), "SI", 9)
mc = CCD(float(ASD(0) - ASD(m_cst)), "SI", 9)
ms = CCD(float(ASD(0) - ASD(m_sls)), "SI", 9)
mp = float(ASD(ms.work) - ASD(mc.work))
mp = CCD(mp, "SI", 9)
if ms.work == 0:
mn = 0
else:
mn = round((mp.work * 100 / ms.work), 2)
mn = CCD(mn, "SD", 7.2)
yq = CCD(float(ASD(0) - ASD(y_qty)), "SI", 9)
yc = CCD(float(ASD(0) - ASD(y_cst)), "SI", 9)
ys = CCD(float(ASD(0) - ASD(y_sls)), "SI", 9)
yp = float(ASD(ys.work) - ASD(yc.work))
yp = CCD(yp, "SI", 9)
if not ys.work:
yn = 0
else:
yn = round((yp.work * 100.0 / ys.work), 2)
yn = CCD(yn, "SD", 7.2)
if mq.work == 0 and mc.work == 0 and ms.work == 0 and \
yq.work == 0 and yc.work == 0 and ys.work == 0:
return
self.gtot[0] = float(ASD(self.gtot[0]) + ASD(mq.work))
self.gtot[1] = float(ASD(self.gtot[1]) + ASD(ms.work))
self.gtot[2] = float(ASD(self.gtot[2]) + ASD(mp.work))
self.gtot[3] = float(ASD(self.gtot[3]) + ASD(yq.work))
self.gtot[4] = float(ASD(self.gtot[4]) + ASD(ys.work))
self.gtot[5] = float(ASD(self.gtot[5]) + ASD(yp.work))
return (code, desc, uoi, mq, ms, mp, mn, yq, ys, yp, yn)
def updateTables(self, rec):
bals = Balances(self.opts["mf"],
"STR",
self.opts["conum"],
int(self.sysdtw / 100),
keys=(rec[0], rec[1], rec[2],
("P", self.opts["period"][0])))
m_ob, m_mv, m_cb, y_ob, y_mv, y_cb, ac, lc, ls = bals.doStrBals()
if self.minus == "N" and not ac and not lc:
return
fqty = CCD(y_cb[0], "SD", 12.2)
fval = CCD(y_cb[1], "SD", 12.2)
if self.minus == "Y" and fqty.work < 0:
nqty = 0
else:
nqty = fqty.work
if self.method == "L" and lc:
nval = nqty * lc
elif self.method == "A" and ac:
nval = nqty * ac
elif lc:
nval = nqty * lc
else:
nval = nqty * ac
dqty = float(ASD(nqty) - ASD(fqty.work))
dval = float(ASD(nval) - ASD(fval.work))
if not dqty and not dval:
return
# Stores Ledger Transaction
if dqty >= 0:
rtn = 5
else:
rtn = 6
txt = "Revaluation"
self.sql.insRec("strtrn",
data=[
self.opts["conum"], rec[0], rec[1], rec[2],
self.date, rtn, self.cnt, "ST-RVAL", "", dqty,
dval, 0, self.curdt, txt, 0, "", "", "STR", 0, "",
self.opts["capnm"], self.sysdtw, 0
])
if self.glint == "N":
self.cnt = self.cnt + 1
return
# General Ledger Control Transaction (Stock On Hand)
self.sql.insRec("gentrn",
data=[
self.opts["conum"], self.stk_soh, self.curdt,
self.date, 4, self.cnt, "ST-RVAL", dval, 0, txt,
"N", "", 0, self.opts["capnm"], self.sysdtw, 0
])
# General Ledger Control Transaction (Stock Reconciliation)
val = float(ASD(0) - ASD(dval))
self.sql.insRec("gentrn",
data=[
self.opts["conum"], self.stk_susp, self.curdt,
self.date, 4, self.cnt, "ST-RVAL", val, 0, txt,
"N", "", 0, self.opts["capnm"], self.sysdtw, 0
])
def getRecipeItems(self):
tab = ["strrcp", "strmf1"]
col = ["srr_rgroup", "srr_rcode", "st1_desc", "srr_rqty"]
whr = [("srr_cono", "=", self.opts["conum"]),
("srr_group", "=", self.group), ("srr_code", "=", self.code),
("srr_loc", "=", self.loc), ("st1_cono=srr_cono", ),
("st1_group=srr_rgroup", ), ("st1_code=srr_rcode", )]
odr = "srr_rgroup, srr_rcode"
items = self.sql.getRec(tables=tab, cols=col, where=whr, order=odr)
for seq, item in enumerate(items):
bals = Balances(self.opts["mf"],
"STR",
self.opts["conum"],
int(self.sysdtw / 100),
keys=(item[0], item[1], self.loc,
("P", self.opts["period"][0])))
m_ob, m_mv, m_cb, y_ob, y_mv, y_cb, ac, lc, ls = bals.doStrBals()
items[seq].extend([y_cb[0], lc])
return ((("srr_rgroup", "UA", 3.0, "Grp"), ("srr_rcode", "NA", 20.0,
"Product-Code"),
("st1_desc", "NA", 30.0,
"Description"), ("srr_rqty", "UD", 11.2, " Quantity"),
("balance", "SD", 13.2,
" In-Stock"), ("cost", "UD", 11.2, " Last-Cost")), items)
def updateTables(self, cnt, col, rec):
grp = rec[col.index("stv_group")]
code = rec[col.index("stv_code")]
loc = rec[col.index("stv_loc")]
vqty = CCD(rec[col.index("stv_qty")], "SD", 12.2)
vprc = CCD(rec[col.index("stv_ucost")], "UD", 12.2)
sell = CCD(rec[col.index("stv_usell")], "UD", 12.2)
# Selling Price Records
if sell.work:
whr = [("stp_cono", "=", self.opts["conum"]),
("stp_group", "=", grp), ("stp_code", "=", code),
("stp_loc", "=", loc), ("stp_level", "=", 1)]
self.sql.delRec("strprc", where=whr)
self.sql.insRec(
"strprc",
data=[self.opts["conum"], grp, code, loc, 1, sell.work])
# Test for Variances
bals = Balances(self.opts["mf"],
"STR",
self.opts["conum"],
self.curdt,
keys=(grp, code, loc, ("P", self.opts["period"][0])))
m_ob, m_mv, m_cb, y_ob, y_mv, y_cb, ac, lc, ls = bals.doStrBals()
fqty = CCD(y_cb[0], "SD", 12.2)
fval = CCD(y_cb[1], "SD", 12.2)
if fval.work and fqty.work:
c = round((fval.work / fqty.work), 2)
else:
c = 0
fprc = CCD(c, "SD", 12.2)
if fprc.work != vprc.work and vprc.work:
prc = vprc.work
else:
prc = fprc.work
prc = CCD(prc, "SD", 12.2)
val = round((prc.work * vqty.work), 2)
vval = CCD(val, "SD", 12.2)
qdif = CCD(float(ASD(vqty.work) - ASD(fqty.work)), "SD", 12.2)
vdif = CCD(float(ASD(vval.work) - ASD(fval.work)), "SD", 12.2)
if not qdif.work and not vdif.work:
return
# Stores Ledger Transaction
if qdif.work >= 0:
rtn = 5
else:
rtn = 6
ref = CCD(cnt, "Na", 9).work
self.sql.insRec("strtrn",
data=[
self.opts["conum"], rec[col.index("stv_group")],
rec[col.index("stv_code")],
rec[col.index("stv_loc")], self.date, rtn, ref,
"ST-MERG", "", qdif.work, vdif.work, 0, self.curdt,
"Stock Take Adjustment", 0, "", "", "STR", 0, "",
self.opts["capnm"], self.sysdtw, 0
])
if self.glint == "N":
return
# General Ledger Control Transaction (Stock On Hand)
col = self.sql.gentrn_col
acc = self.sql.getRec("gentrn",
where=[("glt_cono", "=", self.opts["conum"]),
("glt_acno", "=", self.stk_soh),
("glt_curdt", "=", self.curdt),
("glt_trdt", "=", self.date),
("glt_type", "=", 4),
("glt_refno", "=", "STOCK-ADJ"),
("glt_batch", "=", "ST-MERG")],
limit=1)
if acc:
amnt = float(ASD(acc[col.index("glt_tramt")]) + ASD(vdif.work))
self.sql.updRec("gentrn",
cols=["glt_tramt"],
data=[amnt],
where=[("glt_seq", "=", acc[col.index("glt_seq")])
])
else:
self.sql.insRec("gentrn",
data=[
self.opts["conum"], self.stk_soh, self.curdt,
self.date, 4, "STOCK-ADJ", "ST-MERG",
vdif.work, 0, "Stock Take Adjustment", "N", "",
0, self.opts["capnm"], self.sysdtw, 0
])
# General Ledger Control Transaction (Stock Suspense)
val = float(ASD(0) - ASD(vdif.work))
acc = self.sql.getRec("gentrn",
where=[("glt_cono", "=", self.opts["conum"]),
("glt_acno", "=", self.stk_susp),
("glt_curdt", "=", self.curdt),
("glt_trdt", "=", self.date),
("glt_type", "=", 4),
("glt_refno", "=", "STOCK-ADJ"),
("glt_batch", "=", "ST-MERG")],
limit=1)
if acc:
amnt = float(ASD(acc[col.index("glt_tramt")]) + ASD(val))
self.sql.updRec("gentrn",
cols=["glt_tramt"],
data=[amnt],
where=[("glt_seq", "=", acc[col.index("glt_seq")])
])
else:
self.sql.insRec("gentrn",
data=[
self.opts["conum"], self.stk_susp, self.curdt,
self.date, 4, "STOCK-ADJ", "ST-MERG", val, 0,
"Stock Take Adjustment", "N", "", 0,
self.opts["capnm"], self.sysdtw, 0
])
def printReport(self, recs):
p = ProgressBar(self.opts["mf"].body, mxs=len(recs), esc=True)
self.head = "%03u %-108s" % (self.opts["conum"], self.opts["conam"])
self.fpdf = MyFpdf(name=self.__class__.__name__, head=self.head)
self.stot = [0] * 3
self.gtot = [0] * 3
lstgrp = ""
self.pglin = 999
for num, rec in enumerate(recs):
p.displayProgress(num)
if p.quit:
break
self.grp = CCD(rec[0], "UA", 3)
code = CCD(rec[1], "NA", 20)
desc = CCD(rec[2], "NA", 30)
uoi = CCD(rec[3], "NA", 10)
bals = Balances(self.opts["mf"],
"STR",
self.opts["conum"],
self.per,
keys=(self.grp.work, code.work, self.loc,
("P", self.opts["period"][0])))
m_ob, m_mv, m_cb, y_ob, y_mv, y_cb, ac, lc, ls = bals.doStrBals()
m_qty = 0
m_cst = 0
m_sls = 0
for t, q, c, s in m_mv:
if t == 7:
m_qty = q
m_cst = c
m_sls = s
break
mq = CCD(float(ASD(0) - ASD(m_qty)), "SD", 13.2)
mc = CCD(float(ASD(0) - ASD(m_cst)), "SD", 13.2)
ms = CCD(float(ASD(0) - ASD(m_sls)), "SD", 13.2)
mp = float(ASD(ms.work) - ASD(mc.work))
mp = CCD(mp, "SD", 13.2)
if ms.work == 0:
mn = 0
else:
mn = round((mp.work * 100.0 / ms.work), 2)
mn = CCD(mn, "SD", 7.2)
if mq.work == 0 and mc.work == 0 and ms.work == 0:
continue
if lstgrp and lstgrp != self.grp.work:
self.groupTotal()
self.pglin = 999
if self.pglin > self.fpdf.lpp:
self.pageHeading()
self.fpdf.drawText("%s %s %s %s %s %s %s" %
(code.disp, desc.disp, uoi.disp, mq.disp,
ms.disp, mp.disp, mn.disp))
self.stot[0] = float(ASD(self.stot[0]) + ASD(mq.work))
self.stot[1] = float(ASD(self.stot[1]) + ASD(ms.work))
self.stot[2] = float(ASD(self.stot[2]) + ASD(mp.work))
self.gtot[0] = float(ASD(self.gtot[0]) + ASD(mq.work))
self.gtot[1] = float(ASD(self.gtot[1]) + ASD(ms.work))
self.gtot[2] = float(ASD(self.gtot[2]) + ASD(mp.work))
self.pglin += 1
lstgrp = self.grp.work
p.closeProgress()
if self.fpdf.page and not p.quit:
self.groupTotal()
self.grandTotal()
if "args" not in self.opts or "noprint" not in self.opts["args"]:
pdfnam = getModName(self.opts["mf"].rcdic["wrkdir"],
self.__class__.__name__,
self.opts["conum"],
ext="pdf")
self.fpdf.output(pdfnam, "F")
doPrinter(mf=self.opts["mf"],
conum=self.opts["conum"],
pdfnam=pdfnam,
header=self.tit,
repprt=self.df.repprt,
fromad=self.fromad,
repeml=self.df.repeml)
def printReport(self, recs):
p = ProgressBar(self.opts["mf"].body, mxs=len(recs), esc=True)
self.head = "%03u %-87s" % (self.opts["conum"], self.opts["conam"])
self.fpdf = MyFpdf(name=self.__class__.__name__, head=self.head)
lstgrp = ""
self.pglin = 999
for num, dat in enumerate(recs):
p.displayProgress(num)
if p.quit:
break
grp = CCD(dat[0], "UA", 3)
cod = CCD(dat[1], "NA", 20)
desc = CCD(dat[2], "UA", 30)
bals = Balances(self.opts["mf"],
"STR",
self.opts["conum"],
self.edate,
keys=(grp.work, cod.work, self.loc,
("P", self.opts["period"][0])))
m_ob, m_mv, m_cb, y_ob, y_mv, y_cb, ac, lc, ls, stt_rslt = \
bals.doStrBals(self.sdate, trans="Y")
self.qfwd = y_ob[0]
self.vfwd = y_ob[1]
trs = stt_rslt[0]
dic = stt_rslt[1]
if self.qfwd or self.vfwd or trs:
if lstgrp and self.npag == "Y":
self.pageHeading(grp.disp, cod.disp, desc.disp)
elif lstgrp or self.qfwd or self.vfwd:
self.accountHeading(grp.disp, cod.disp, desc.disp)
lstgrp = grp.work
if trs:
for z in trs:
dt = CCD(z[dic["stt_trdt"][1]], "d1", 10)
qt = CCD(z[dic["stt_qty"][1]], "SD", 12.2)
cs = CCD(z[dic["stt_cost"][1]], "SD", 12.2)
bt = CCD(z[dic["stt_batch"][1]], "Na", 7)
r1 = CCD(z[dic["stt_ref1"][1]], "Na", 9)
r2 = CCD(z[dic["stt_ref1"][1]], "Na", 7)
tp = CCD(z[dic["stt_type"][1]], "UI", 1)
if self.pglin > (self.fpdf.lpp - 7):
self.pageHeading(grp.disp, cod.disp, desc.disp)
self.qfwd = float(ASD(self.qfwd) + ASD(qt.work))
self.vfwd = float(ASD(self.vfwd) + ASD(cs.work))
qb = CCD(self.qfwd, "SD", 12.2)
vb = CCD(self.vfwd, "SD", 12.2)
self.fpdf.drawText("%s %s %s %s %s %s %s %s %s" % \
(bt.disp, r1.disp, r2.disp, sttrtp[tp.work-1][0],
dt.disp, qt.disp, cs.disp, qb.disp, vb.disp))
self.pglin += 1
p.closeProgress()
if self.fpdf.page and not p.quit:
if "args" not in self.opts or "noprint" not in self.opts["args"]:
pdfnam = getModName(self.opts["mf"].rcdic["wrkdir"],
self.__class__.__name__,
self.opts["conum"],
ext="pdf")
self.fpdf.output(pdfnam, "F")
doPrinter(mf=self.opts["mf"],
conum=self.opts["conum"],
pdfnam=pdfnam,
header=self.tit,
repprt=self.df.repprt,
fromad=self.fromad,
repeml=self.df.repeml)
def printReport(self, recs):
data = []
p = ProgressBar(self.opts["mf"].body, mxs=len(recs), esc=True)
self.head = "%03u %-135s" % (self.opts["conum"], self.opts["conam"])
for num, dat in enumerate(recs):
p.displayProgress(num)
if p.quit:
break
grp = dat[self.sql.strvar_col.index("stv_group")]
code = dat[self.sql.strvar_col.index("stv_code")]
sbin = dat[self.sql.strvar_col.index("stv_bin")]
qty1 = dat[self.sql.strvar_col.index("stv_qty")]
prc = dat[self.sql.strvar_col.index("stv_ucost")]
bals = Balances(self.opts["mf"],
"STR",
self.opts["conum"],
int(self.date / 100),
keys=(grp, code, self.loc,
("P", self.opts["period"][0])))
m_ob, m_mv, m_cb, y_ob, y_mv, y_cb, ac, lc, ls = bals.doStrBals()
fq = y_cb[0]
if ac:
fp = ac
else:
fp = lc
diff = float(ASD(fq) - ASD(qty1))
rslt = self.sql.getRec("strmf1",
cols=["st1_desc", "st1_uoi"],
where=[("st1_cono", "=",
self.opts["conum"]),
("st1_group", "=", grp),
("st1_code", "=", code)],
limit=1)
desc = rslt[0]
uoi = rslt[1]
data.append([grp, code, desc, uoi, sbin, fp, prc, fq, qty1, diff])
p.closeProgress()
if not p.quit:
name = self.__class__.__name__
head = [
"Stock Take Variance Report as at %s" % self.dated,
"Location %s %s" % (self.loc, self.locd)
]
cols = [["a", "NA", 3, "Grp", "y"],
["b", "NA", 20, "Product-Code", "y"],
["c", "NA", 30, "Description", "y"],
["d", "NA", 10, "U.O.I", "y"],
["e", "NA", 8, "Bin-Loc", "y"],
["f", "SD", 12.2, "File-Cost", "y"],
["g", "UD", 12.2, "Stkt-Cost", "y"],
["h", "SD", 12.2, "File-Qty", "y"],
["i", "SD", 12.2, "Stkt-Qty", "y"],
["j", "SD", 12.2, "Difference", "y"]]
RepPrt(self.opts["mf"],
conum=self.opts["conum"],
conam=self.opts["conam"],
name=name,
tables=data,
heads=head,
cols=cols,
ttype="D",
repprt=self.df.repprt,
repeml=self.df.repeml,
fromad=self.fromad)
def loadBalances(self):
bals = Balances(self.opts["mf"],
"STR",
self.opts["conum"],
int(self.sysdtw / 100),
keys=(self.group, self.code, self.loc,
("P", self.opts["period"][0])))
m_ob, m_mv, m_cb, y_ob, y_mv, y_cb, ac, lc, ls = bals.doStrBals()
cb, oo, bo = bals.doStrOrds()
this, hist = bals.doStrHist()
rec = hist[0]
rec.append(this[0])
iss = hist[1]
iss.append(this[1])
lrec = self.sql.getRec("strtrn",
cols=["max(stt_trdt)"],
where=[("stt_cono", "=", self.opts["conum"]),
("stt_group", "=", self.group),
("stt_code", "=", self.code),
("stt_loc", "=", self.loc),
("stt_type", "in", (1, 3))])
if not lrec[0][0]:
lastrec = 0
else:
lastrec = lrec[0][0]
liss = self.sql.getRec("strtrn",
cols=["max(stt_trdt)"],
where=[("stt_cono", "=", self.opts["conum"]),
("stt_group", "=", self.group),
("stt_code", "=", self.code),
("stt_loc", "=", self.loc),
("stt_type", "in", (2, 4, 7, 8))])
if not liss or not liss[0][0]:
lastiss = 0
else:
lastiss = liss[0][0]
qbal = float(y_cb[0])
vbal = float(y_cb[1])
if qbal:
cost = round((vbal / qbal), 2)
else:
cost = 0.0
cst = CCD(cost, "SD", 10.2)
self.df.loadEntry("T", 2, 0, data=lastrec)
self.df.loadEntry("T", 2, 1, data=lastiss)
self.df.loadEntry("T", 2, 2, data=qbal)
self.df.loadEntry("T", 2, 3, data=vbal)
self.df.loadEntry("T", 2, 4, data=cst.disp)
self.df.loadEntry("T", 2, 5, data=oo.disp)
self.df.loadEntry("T", 2, 6, data=bo.disp)
p = 0
for x in range(0, 13):
i = 0
self.df.loadEntry("C", 3, p, data=rec[x][0])
p = p + 1
i = i + 1
self.df.loadEntry("C", 3, p, data=rec[x][1])
p = p + 1
i = i + 1
amt = float(ASD(0) - ASD(iss[x][0]))
self.df.loadEntry("C", 3, p, data=amt)
p = p + 1
i = i + 1
amt = float(ASD(0) - ASD(iss[x][1]))
self.df.loadEntry("C", 3, p, data=amt)
p = p + 1
i = i + 1