def doProcess(self, dat):
loc, group, code, desc, valind, vatcod = dat
icost, bal = getCost(self.sql, self.opts["conum"], group, code,
loc=loc, qty=1, ind="I", bal=True)
if self.outs == "N" and not bal[0]:
return
if self.ccode:
# Calculate cost price code
cost = CCD(int(icost * 100), "UI", 9)
ncost = " " * (9 - len(str(cost.work)))
for x in str(cost.work):
ncost = ncost + self.ccode[int(x)]
lab = "%-30s %13s" % (self.df.t_disp[0][0][0], ncost)
else:
lab = "%-30s" % self.df.t_disp[0][0][0]
####################################################################
lab = "%s\n%s %s %s" % (lab, loc, group, code)
lab = "%s\n%s\n" % (lab, desc)
prc = getSell(self.sql, self.opts["conum"], group, code, loc)
price1 = CCD(prc, "UD", 9.2)
vrte = getVatRate(self.sql, self.opts["conum"], vatcod, self.date)
if vrte is None:
vrte = CCD(0, "UD", 9.2)
else:
vrte = CCD(vrte, "UD", 9.2)
price2 = CCD(round((price1.work * float(ASD(100) + \
ASD(vrte.work)) / 100.0), 2), "OUD", 9.2)
lab = "%s\n%s %s %s" % (lab, vatcod, price1.disp, price2.disp)
self.fpdf.add_label(lab)
def getValues2(self, data):
vtyp = CCD(data[self.sql.ctlvtf_col.index("vtt_vtyp")], "UA", 1)
curdt = CCD(data[self.sql.ctlvtf_col.index("vtt_curdt")], "D2", 7)
styp = CCD(data[self.sql.ctlvtf_col.index("vtt_styp")], "UA", 1)
ttyp = CCD(data[self.sql.ctlvtf_col.index("vtt_ttyp")], "UI", 2)
acno = CCD(data[self.sql.ctlvtf_col.index("vtt_acno")], "NA", 7)
refno = CCD(data[self.sql.ctlvtf_col.index("vtt_refno")], "Na", 9)
refdt = CCD(data[self.sql.ctlvtf_col.index("vtt_refdt")], "D1", 10)
desc = CCD(data[self.sql.ctlvtf_col.index("vtt_desc")], "NA", 30)
exc = CCD(data[self.sql.ctlvtf_col.index("vtt_exc")], "SD", 13.2)
vtr = getVatRate(self.sql, self.opts["conum"], self.pcode, refdt.work)
vtr = CCD(vtr, "UD", 6.2)
for cat in ("C", "N", "S", "X", "Z"):
if cat not in self.ttopv:
self.ttopv[cat] = {}
self.ttopt[cat] = {}
self.ttipv[cat] = {}
self.ttipt[cat] = {}
if vtr.disp not in self.ttopv[cat]:
self.ttopv[cat][vtr.disp] = 0
self.ttopt[cat][vtr.disp] = 0
self.ttipv[cat][vtr.disp] = 0
self.ttipt[cat][vtr.disp] = 0
tax = CCD(data[self.sql.ctlvtf_col.index("vtt_tax")], "SD", 13.2)
inc = CCD((exc.work + tax.work), "SD", 13.2)
return (vtyp, curdt, styp, ttyp, acno, refno, refdt, desc, exc, vtr,
tax, inc)
def doUpdateTables(self, memno, ctyp, code, desc, trdt, amt):
batch = "PROFORM"
self.refno += 1
refno = "PF%07i" % self.refno
curdt = int(trdt / 100)
# VAT Rate and Amount
vrte = getVatRate(self.sql, self.opts["conum"], self.taxdf, trdt)
if vrte is None:
vrte = 0.0
vat = CCD(round(((amt * vrte) / 114), 2), "UD", 12.2).work
# Members Ledger Transaction (memtrn)
self.sql.insRec("memtrn",
data=[
self.opts["conum"], memno, 1, refno, batch, trdt,
amt, vat, curdt, ctyp, code, desc, self.taxdf, "",
self.opts["capnm"], self.sysdtw, 0
],
unique="mlt_refno")
# Members Category Record (memcat)
self.sql.updRec("memcat",
cols=["mlc_last"],
data=[trdt],
where=[("mlc_cono", "=", self.opts["conum"]),
("mlc_memno", "=", memno),
("mlc_type", "=", ctyp),
("mlc_code", "=", code)])
def doVatCode1(self, frt, pag, r, c, p, i, w):
vrte = getVatRate(self.sql, self.opts["conum"], w, self.trndat)
if vrte is None:
return "Invalid V.A.T Code"
self.vatcode = w
self.vatamt = round((self.trnamt * vrte / (vrte + 100)), 2)
self.df.loadEntry(frt, pag, p + 1, self.vatamt)
if not self.vatamt:
return "sk1"
def doAllCod(self, frt, pag, r, c, p, i, w):
vrte = getVatRate(self.sql, self.opts["conum"], w, self.trndat)
if vrte is None:
return "Invalid V.A.T Code"
self.allcod = w
self.allvat = round((self.allamt * vrte / (vrte + 100)), 2)
self.df.loadEntry(frt, pag, p + 1, data=self.allvat)
if not self.allvat:
self.df.loadEntry(frt, pag, p + 2, data=self.trndet)
return "sk1"
def doEnd(self):
self.df.closeProcess()
if self.alljnl == "N":
recs = getSingleRecords(self.opts["mf"],
"genjlm", ("gjm_num", "gjm_desc"),
where=self.wher)
else:
recs = self.sql.getRec("genjlm", where=self.wher)
if not recs:
self.opts["mf"].closeLoop()
return
for gjm in recs:
self.trnref = gjm[self.sql.genjlm_col.index("gjm_num")]
self.trndet = gjm[self.sql.genjlm_col.index("gjm_desc")]
start = gjm[self.sql.genjlm_col.index("gjm_start")]
last = gjm[self.sql.genjlm_col.index("gjm_last")]
dates = []
while start <= self.bh.curdt:
if start < self.s_per or start <= last:
pass
elif self.allper == "N" and start == self.bh.curdt:
dates.append(start)
elif self.allper == "Y" and start <= self.bh.curdt:
dates.append(start)
start = self.nextPeriod(start)
for self.curdt in dates:
self.trndat = mthendDate((self.curdt * 100))
data = self.sql.getRec(
"genjlt",
cols=["gjt_acno", "gjt_amnt", "gjt_vatc"],
where=[("gjt_cono", "=", self.opts["conum"]),
("gjt_num", "=", self.trnref),
("gjt_amnt", "<>", 0)])
if not data:
continue
for tran in data:
self.acno = tran[0]
self.trnamt = tran[1]
self.vatcod = tran[2]
vrte = getVatRate(self.sql, self.opts["conum"],
self.vatcod, self.trndat)
if vrte is None:
vrte = 0.0
self.vatamt = round((self.trnamt * vrte / (vrte + 100)), 2)
self.updateTables()
self.updateBatch()
self.sql.updRec("genjlm",
cols=["gjm_last"],
data=[self.curdt],
where=[("gjm_cono", "=", self.opts["conum"]),
("gjm_num", "=", self.trnref),
("gjm_freq", "=", self.freq)])
self.opts["mf"].dbm.commitDbase()
self.opts["mf"].closeLoop()
def doVCode(self, frt, pag, r, c, p, i, w):
self.vatrte = getVatRate(self.sql, self.opts["conum"], w, self.date)
if self.vatrte is None:
ask = askQuestion(self.opts["mf"].body,
"New Code",
"Code Does Not Exist, Create a New Code?",
default="no")
if ask == "no":
return "Invalid VAT Code"
w = callModule(self.opts["mf"],
self.df,
"ms1040",
coy=[self.opts["conum"], self.opts["conam"]],
user=self.opts["capnm"],
args=w,
ret="code")
self.df.loadEntry(frt, pag, p, data=w)
self.vatrte = getVatRate(self.sql, self.opts["conum"], w, self.date)
if self.vatrte is None:
return "Invalid VAT Code"
def doVatCode2(self, frt, pag, r, c, p, i, w):
ctlctl = self.gc.getCtl("ctlctl", self.opts["conum"], error=False)
if not ctlctl:
return "Missing ctlctl Record for Company"
if self.gc.chkRec(self.opts["conum"], ctlctl, ["vat_ctl"]):
return "Missing or Invalid Control Record"
self.convat = ctlctl["vat_ctl"]
self.vatrte = getVatRate(self.sql, self.opts["conum"], w, self.trndat)
if self.vatrte is None:
return "Invalid V.A.T Code"
self.vatcode = w
def doVat(self, frt, pag, r, c, p, i, w):
self.vatrte = getVatRate(self.sql, self.opts["conum"], w, self.trdt)
if self.vatrte is None:
return "Invalid V.A.T Code"
self.vatcod = w
# Last Cost Price
lcost = getCost(self.sql,
self.opts["conum"],
self.grp,
self.code,
loc=self.loc,
qty=1,
ind="L")
self.df.loadEntry(frt, pag, p + 1, data=lcost)
def doTntAmt(self, frt, pag, r, c, p, i, w):
self.trnamt = w
if self.trntyp == 1:
# Rental Raised
self.vatcode = self.vatdf
vrte = getVatRate(self.sql, self.opts["conum"], self.vatcode,
self.trndat)
if vrte is None:
vrte = 0.0
self.trnvat = round((w * vrte / (vrte + 100)), 2)
self.df.loadEntry(frt, pag, p + 1, data="Rental Raised")
else:
# Ignore VAT at this stage
self.vatcode = ""
self.trnvat = 0
def doVatCode1(self, frt, pag, r, c, p, i, w):
vrte = getVatRate(self.sql, self.opts["conum"], w, self.trndat)
if vrte is None:
return "Invalid V.A.T Code"
if vrte and not self.vatn:
ok = askQuestion(self.opts["mf"].window, "VAT Number",
"This Account Does Not Have a VAT Number.\n\nMust "\
"it be Populated?", default="yes")
if ok == "yes":
self.vatn = w
self.popv = True
self.vatcode = w
self.vatamt = round((self.trnamt * vrte / (vrte + 100)), 2)
self.df.loadEntry(frt, pag, p + 1, data=self.vatamt)
if not self.vatamt:
return "sk1"
def getValues(self, data):
grp = CCD(data[0], "UA", 3)
self.groupd = grp.disp
code = CCD(data[1], "NA", 20)
desc = CCD(data[2], "UA", 30)
uoi = CCD(data[3], "NA", 10)
# Cost Prices and Balances
cst, bal = getCost(self.sql,
self.opts["conum"],
grp.work,
code.work,
loc=self.loc,
qty=1,
ind="AL",
bal=True)
qty = CCD(bal[0], "SD", 12.2)
if self.nostock == "Y" and qty.work <= 0:
return
acst = CCD(cst[0], "SD", 10.2)
lcst = CCD(cst[1], "SD", 10.2)
if self.rtype == "C":
return (grp, code, desc, uoi, qty, acst, lcst)
# Selling Prices
vat = CCD(data[4], "UA", 1)
rte = 0
if self.vatinc == "Y":
vatrte = getVatRate(self.sql, self.opts["conum"], vat.work,
self.sysdtw)
if vatrte is not None:
rte = vatrte
prcs = []
for lev in self.levs:
prc = getSell(self.sql, self.opts["conum"], grp.work, code.work,
self.loc, lev)
prcs.append(CCD(round((prc * ((100 + rte) / 100.0)), 2), "UD",
9.2))
if self.noprice == "Y":
cont = False
for prc in prcs:
if prc.work:
cont = True
break
if not cont:
return
return (grp, code, desc, uoi, vat, qty, acst, lcst, prcs)
def doUpdateTables(self, ctyp, code, desc, trdt, amt):
batch = "PROFORM"
self.refno += 1
refno = "PF%07i" % self.refno
curdt = int(trdt / 100)
# VAT Rate and Amount
vrte = getVatRate(self.sql, self.opts["conum"], self.taxdf, trdt)
if vrte is None:
vrte = 0.0
vat = CCD(round(((amt * vrte) / 114), 2), "UD", 12.2).work
# Members Ledger Transaction (memtrn)
self.sql.insRec("memtrn",
data=[
self.opts["conum"], self.memno, 1, refno, batch,
trdt, amt, vat, curdt, ctyp, code, desc,
self.taxdf, "", self.opts["capnm"], self.sysdt, 0
],
unique="mlt_refno")
def doVatCode2(self, frt, pag, r, c, p, i, w):
ctlctl = self.gc.getCtl("ctlctl", self.allcoy, error=False)
if not ctlctl:
return "Missing ctlctl Record for Company"
if self.gc.chkRec(self.allcoy, ctlctl, ["vat_ctl"]):
return "Missing or Invalid Control Record"
self.convat = ctlctl["vat_ctl"]
self.vatrte = getVatRate(self.sql, self.allcoy, w, self.trndat)
if self.vatrte is None:
return "Invalid V.A.T Code"
if self.vatrte and not self.vatn:
ok = askQuestion(self.opts["mf"].window, "VAT Number",
"This Account Does Not Have a VAT Number.\n\nMust "\
"it be Populated?", default="yes")
if ok == "yes":
self.vatn = w
self.popv = True
self.vatcode = w
def doUpdateTables(self, ctyp, code, desc, glac, trdt, amt):
self.refnum += 1
ref = CCD("A%08i" % self.refnum, "Na", 9).work
curdt = int(trdt / 100)
# VAT Rate and Amount
vrte = getVatRate(self.sql, self.opts["conum"], self.taxdf, trdt)
if vrte is None:
vrte = 0.0
vat = CCD(round(((amt * vrte) / (vrte + 100)), 2), "UD", 12.2).work
# Members Ledger Transaction (memtrn)
data = [
self.opts["conum"], self.memno, 1, ref, self.batch, trdt, amt, vat,
curdt, ctyp, code, desc, self.taxdf, "", self.opts["capnm"],
self.sysdt, 0
]
self.sql.insRec("memtrn", data=data)
# Members Ledger Category (memcat)
self.sql.updRec("memcat",
cols=["mlc_last"],
data=[trdt],
where=[("mlc_cono", "=", self.opts["conum"]),
("mlc_memno", "=", self.memno),
("mlc_type", "=", ctyp),
("mlc_code", "=", code)])
if vat:
# VAT Transaction (ctlvtf)
val = float(ASD(0) - ASD(amt) + ASD(vat))
tax = float(ASD(0) - ASD(vat))
data = [
self.opts["conum"], self.taxdf, "O", curdt, "M", 1, self.batch,
ref, trdt, self.memno, desc, val, tax, 0, self.opts["capnm"],
self.sysdt, 0
]
self.sql.insRec("ctlvtf", data=data)
if self.glint == "N":
return
ref = "ML/MthEnd"
# General Ledger Member Control
whr = [("glt_cono", "=", self.opts["conum"]),
("glt_acno", "=", self.memctl), ("glt_batch", "=", self.batch),
("glt_curdt", "=", curdt), ("glt_trdt", "=", trdt),
("glt_type", "=", 1)]
glt = self.sql.getRec("gentrn", where=whr)
if glt and len(glt) == 1:
tmp = glt[0][self.sql.gentrn_col.index("glt_tramt")]
tmp = float(ASD(tmp) + ASD(amt))
self.sql.updRec("gentrn",
cols=["glt_tramt"],
data=[tmp],
where=whr)
else:
data = [
self.opts["conum"], self.memctl, curdt, trdt, 1, ref,
self.batch, amt, 0, "Month End Raising Charges", "", "", 0,
self.opts["capnm"], self.sysdt, 0
]
self.sql.insRec("gentrn", data=data)
val = float(ASD(0) - ASD(amt) + ASD(vat))
tax = float(ASD(0) - ASD(vat))
# General Ledger Income Account
whr[1] = ("glt_acno", "=", glac)
glt = self.sql.getRec("gentrn", where=whr)
if glt and len(glt) == 1:
tmp = glt[0][self.sql.gentrn_col.index("glt_tramt")]
tmp = float(ASD(tmp) + ASD(val))
glt[0][self.sql.gentrn_col.index("glt_tramt")] = tmp
tmp = glt[0][self.sql.gentrn_col.index("glt_taxamt")]
tmp = float(ASD(tmp) + ASD(tax))
glt[0][self.sql.gentrn_col.index("glt_taxamt")] = tmp
self.sql.updRec("gentrn", data=glt[0], where=whr)
else:
data = [
self.opts["conum"], glac, curdt, trdt, 1, ref, self.batch, val,
tax, "Month End Raising Charges", self.taxdf, "", 0,
self.opts["capnm"], self.sysdt, 0
]
self.sql.insRec("gentrn", data=data)
if not tax:
return
# General Ledger VAT Account
whr = [("glt_cono", "=", self.opts["conum"]),
("glt_acno", "=", self.vatctl), ("glt_batch", "=", self.batch),
("glt_curdt", "=", curdt), ("glt_trdt", "=", trdt),
("glt_type", "=", 1)]
glt = self.sql.getRec("gentrn", where=whr)
if glt and len(glt) == 1:
tmp = glt[0][self.sql.gentrn_col.index("glt_tramt")]
tmp = float(ASD(tmp) + ASD(tax))
self.sql.updRec("gentrn",
cols=["glt_tramt"],
data=[tmp],
where=whr)
else:
data = [
self.opts["conum"], self.vatctl, curdt, trdt, 1, ref,
self.batch, tax, 0.00, "Month End Raising Charges", "", "", 0,
self.opts["capnm"], self.sysdt, 0
]
self.sql.insRec("gentrn", data=data)
def doRaiseRental(self, num):
ref = CCD((num + 1), "Na", 9).work
amt = CCD(self.rec[7], "UD", 12.2).work
vrte = getVatRate(self.sql, self.opts["conum"], self.rec[9], self.nxtdt)
if vrte is None:
vrte = 0.0
vat = CCD(round((amt * vrte / 100), 2), "SD", 11.2).work
tot = float(ASD(amt) + ASD(vat))
data = self.rec[:4]
data.extend([1, ref, self.batch, self.nxtdt, tot, vat, self.nxtcd,
"Rental Raised", self.rec[9], "", self.opts["capnm"], self.sysdtw,
0])
self.sql.insRec("rtltrn", data=data)
if self.rec[9]:
# VAT Transaction (ctlvtf)
val = float(ASD(0) - ASD(amt))
tax = float(ASD(0) - ASD(vat))
data = [self.opts["conum"], self.rec[9], "O", self.nxtcd, "R", 1,
self.batch, ref, self.nxtdt, self.rec[2], self.rec[8],
val, tax, 0, self.opts["capnm"], self.sysdtw, 0]
self.sql.insRec("ctlvtf", data=data)
if self.glint == "N":
return
acc = self.sql.getRec("rtlprm", cols=["rtp_rtlacc",
"rtp_incacc"], where=[("rtp_cono", "=", self.opts["conum"]),
("rtp_code", "=", self.rec[1])], limit=1)
if not acc:
return
ref = "Month/End"
# General Ledger Rental Control
whr = [
("glt_cono", "=", self.opts["conum"]),
("glt_acno", "=", acc[0]),
("glt_batch", "=", self.batch),
("glt_curdt", "=", self.nxtcd),
("glt_trdt", "=", self.nxtdt),
("glt_type", "=", 1)]
glt = self.sql.getRec("gentrn", where=whr)
if glt and len(glt) == 1:
val = glt[0][self.sql.gentrn_col.index("glt_tramt")]
val = float(ASD(val) + ASD(tot))
tax = glt[0][self.sql.gentrn_col.index("glt_taxamt")]
tax = float(ASD(tax) + ASD(vat))
self.sql.updRec("gentrn", cols=["glt_tramt", "glt_taxamt"],
data=[val, tax], where=whr)
else:
data = [self.opts["conum"], acc[0], self.nxtcd, self.nxtdt, 1, ref,
self.batch, tot, vat, self.rec[8], self.rec[9], "", 0,
self.opts["capnm"], self.sysdtw, 0]
self.sql.insRec("gentrn", data=data)
amt = float(ASD(0) - ASD(amt))
vat = float(ASD(0) - ASD(vat))
# General Ledger Income Account
whr = [
("glt_cono", "=", self.opts["conum"]),
("glt_acno", "=", acc[1]),
("glt_batch", "=", self.batch),
("glt_curdt", "=", self.nxtcd),
("glt_trdt", "=", self.nxtdt),
("glt_type", "=", 1)]
glt = self.sql.getRec("gentrn", where=whr)
if glt and len(glt) == 1:
val = glt[0][self.sql.gentrn_col.index("glt_tramt")]
val = float(ASD(val) + ASD(amt))
self.sql.updRec("gentrn", cols=["glt_tramt"], data=[val], where=whr)
else:
data = [self.opts["conum"], acc[1], self.nxtcd, self.nxtdt, 1, ref,
self.batch, amt, 0.00, self.rec[8], "", "", 0,
self.opts["capnm"], self.sysdtw, 0]
self.sql.insRec("gentrn", data=data)
if not vat:
return
# General Ledger VAT Account
whr = [
("glt_cono", "=", self.opts["conum"]),
("glt_acno", "=", self.convat),
("glt_batch", "=", self.batch),
("glt_curdt", "=", self.nxtcd),
("glt_trdt", "=", self.nxtdt),
("glt_type", "=", 1)]
glt = self.sql.getRec("gentrn", where=whr)
if glt and len(glt) == 1:
val = glt[0][self.sql.gentrn_col.index("glt_tramt")]
val = float(ASD(val) + ASD(vat))
self.sql.updRec("gentrn", cols=["glt_tramt"], data=[val], where=whr)
elif vat:
data = [self.opts["conum"], self.convat, self.nxtcd, self.nxtdt, 1,
ref, self.batch, vat, 0.00, self.rec[8], "", "", 0,
self.opts["capnm"], self.sysdtw, 0]
self.sql.insRec("gentrn", data=data)
def doRaiseRental(self, num):
ref = CCD((num + 1), "Na", 9).work
amt = CCD(self.con[8], "UD", 12.2).work
vrte = getVatRate(self.sql, self.opts["conum"], self.vatdf, self.nxtdt)
if vrte is None:
vrte = 0.0
vat = CCD(round((amt * vrte / 100), 2), "SD", 11.2).work
tot = float(ASD(amt) + ASD(vat))
# Tenant Transaction
data = self.con[:5]
data.extend([
1, ref, self.batch, self.nxtdt, 1, tot, vat, self.nxtcd,
"Rental Raised", self.vatdf, "", self.opts["capnm"], self.sysdtw, 0
])
self.sql.insRec("rcatnt", data=data)
if self.glint == "Y":
# Update Tenant Control
gld = "%7s Rental Raised" % self.con[3]
data = [
self.opts["conum"], self.rcatnt, self.nxtcd, self.nxtdt, 4,
ref, self.batch, tot, 0, gld, "", "", 0, self.opts["capnm"],
self.sysdtw, 0
]
self.sql.insRec("gentrn", data=data)
rta = float(ASD(0) - ASD(tot))
rtv = float(ASD(0) - ASD(vat))
# Owner Transaction
data = self.con[:2]
data.extend([
1, ref, self.batch, self.nxtdt, rta, rtv, self.nxtcd,
"Rental on %s Premises" % self.con[2], self.vatdf, "",
self.opts["capnm"], self.sysdtw, 0
])
self.sql.insRec("rcaowt", data=data)
if self.glint == "Y":
# Update Owner Control
gld = "%7s Rental Raised" % self.con[1]
data = [
self.opts["conum"], self.rcaown, self.nxtcd, self.nxtdt, 4,
ref, self.batch, rta, 0, gld, "", "", 0, self.opts["capnm"],
self.sysdtw, 0
]
self.sql.insRec("gentrn", data=data)
if self.prm[0]:
cma = round((tot * self.prm[0] / 100.0), 2)
vrte = getVatRate(self.sql, self.opts["conum"], self.taxdf,
self.nxtdt)
if vrte is None:
vrte = 0.0
cmv = round((cma * vrte / 100.0), 2)
cmt = float(ASD(cma) + ASD(cmv))
cma = float(ASD(0) - ASD(cma))
tax = float(ASD(0) - ASD(cmv))
des = "Commission @ %3.5s%s Inclusive" % (self.prm[0], "%")
# Raise Commission
data = self.con[:2]
data.extend([
4, ref, self.batch, self.nxtdt, cmt, cmv, self.nxtcd, des,
self.taxdf, "", self.opts["capnm"], self.sysdtw, 0
])
self.sql.insRec("rcaowt", data=data)
if self.glint == "Y":
# Update Owner Control
gld = "%7s Commission Raised" % self.con[1]
data = [
self.opts["conum"], self.rcaown, self.nxtcd, self.nxtdt, 4,
ref, self.batch, cmt, 0, gld, "", "", 0,
self.opts["capnm"], self.sysdtw, 0
]
self.sql.insRec("gentrn", data=data)
# Update Commission Account
data = [
self.opts["conum"], self.rcacom, self.nxtcd, self.nxtdt, 4,
ref, self.batch, cma, tax, gld, "", "", 0,
self.opts["capnm"], self.sysdtw, 0
]
self.sql.insRec("gentrn", data=data)
if self.taxdf:
# VAT Transaction (ctlvtf)
data = [
self.opts["conum"], self.taxdf, "O", self.nxtcd, "R", 1,
self.batch, ref, self.nxtdt, self.con[3], self.con[14],
cma, tax, 0, self.opts["capnm"], self.sysdtw, 0
]
self.sql.insRec("ctlvtf", data=data)
if self.glint == "Y" and tax:
# Update VAT Control
data = [
self.opts["conum"], self.convat, self.nxtcd,
self.nxtdt, 4, ref, self.batch, tax, 0, gld, "", "", 0,
self.opts["capnm"], self.sysdtw, 0
]
self.sql.insRec("gentrn", data=data)
def updateTables(self):
curdt = int(self.trndat / 100)
amt = self.trnamt
vat = self.trnvat
if self.trntyp in (1, 4):
# Rental and Journal
gltyp = 4
elif self.trntyp == 2:
# Receipt
gltyp = 6
bnk = amt
amt = float(ASD(0) - ASD(amt))
vat = float(ASD(0) - ASD(vat))
elif self.trntyp == 3:
# Payment
gltyp = 2
bnk = float(ASD(0) - ASD(amt))
if self.df.pag == 1:
# Owners Transaction
accod = self.owner
data = [
self.opts["conum"], self.owner, self.trntyp, self.trnref,
self.batno, self.trndat, amt, vat, curdt, self.trndet,
self.vatcode, "", self.opts["capnm"], self.sysdtw, 0
]
self.sql.insRec("rcaowt", data=data)
if self.glint == "Y":
# General Ledger Transaction for Owner Control
gld = "%7s %s" % (self.owner, self.trndet[:22])
data = [
self.opts["conum"], self.rcaown, curdt, self.trndat, gltyp,
self.trnref, self.batno, amt, 0, gld, "", "", 0,
self.opts["capnm"], self.sysdtw, 0
]
self.sql.insRec("gentrn", data=data)
else:
# Tenants Transaction
accod = self.code
data = [
self.opts["conum"], self.owner, self.code, self.acno,
self.cnum, self.trntyp, self.trnref, self.batno, self.trndat,
self.movtyp, amt, vat, curdt, self.trndet, self.vatcode, "",
self.opts["capnm"], self.sysdtw, 0
]
self.sql.insRec("rcatnt", data=data)
if self.glint == "Y":
gld = "%7s %7s %7s" % (self.owner, self.code, self.acno)
if self.df.pag == 3 and self.movtyp == 2:
# General Ledger Transaction for Deposit Control
acc = self.rcadep
else:
# General Ledger Transaction for Tenant Control
acc = self.rcatnt
data = [
self.opts["conum"], acc, curdt, self.trndat, gltyp,
self.trnref, self.batno, amt, 0, gld, "", "", 0,
self.opts["capnm"], self.sysdtw, 0
]
self.sql.insRec("gentrn", data=data)
self.showTenantBalance()
if self.trntyp in (2, 3):
if self.glint == "Y":
# General Ledger Transaction for Bank Account
data = [
self.opts["conum"], self.glbnk, curdt, self.trndat, gltyp,
self.trnref, self.batno, bnk, 0, gld, "", "", 0,
self.opts["capnm"], self.sysdtw, 0
]
self.sql.insRec("gentrn", data=data)
return
if self.df.pag == 1 and self.df.pag == 1:
# Owners Journal Entries (Unallocated)
if self.glint == "Y":
amt = float(ASD(0) - ASD(amt))
data = [
self.opts["conum"], self.rcaorx, curdt, self.trndat, gltyp,
self.trnref, self.batno, amt, 0, gld, "", "", 0,
self.opts["capnm"], self.sysdtw, 0
]
self.sql.insRec("gentrn", data=data)
return
if self.df.pag == 2 and self.movtyp == 1:
# Rental and Commission Raised
rta = float(ASD(0) - ASD(amt))
rtv = float(ASD(0) - ASD(vat))
des = "Rental on %s Premises" % self.code
data = [
self.opts["conum"], self.owner, self.trntyp, self.trnref,
self.batno, self.trndat, rta, rtv, curdt, des, self.vatcode,
"", self.opts["capnm"], self.sysdtw, 0
]
self.sql.insRec("rcaowt", data=data)
if self.glint == "Y":
# General Ledger Transaction for Owner Control
gld = "%7s Rental on %7s" % (self.owner, self.code)
data = [
self.opts["conum"], self.rcaown, curdt, self.trndat, gltyp,
self.trnref, self.batno, rta, 0, gld, "", "", 0,
self.opts["capnm"], self.sysdtw, 0
]
self.sql.insRec("gentrn", data=data)
if self.crate:
# If there is a Commission Rate on the Premises Record
cma = float(ASD(0) - ASD(round((rta * self.crate / 100.0), 2)))
vrte = getVatRate(self.sql, self.opts["conum"], self.taxdf,
self.trndat)
if vrte is None:
vrte = 0.0
cmv = round((cma * vrte / 100.0), 2)
cmt = float(ASD(cma) + ASD(cmv))
cma = float(ASD(0) - ASD(cma))
tax = float(ASD(0) - ASD(cmv))
des = "Commission @ %3.5s%s Inclusive" % (self.crate, "%")
data = [
self.opts["conum"], self.owner, 4, self.trnref, self.batno,
self.trndat, cmt, cmv, curdt, des, self.vatdf, "",
self.opts["capnm"], self.sysdtw, 0
]
self.sql.insRec("rcaowt", data=data)
if self.glint == "Y":
# Update Owner Control
gld = "%7s Commission Raised" % self.owner
data = [
self.opts["conum"], self.rcaown, curdt, self.trndat,
gltyp, self.trnref, self.batno, cmt, 0, gld, "", "", 0,
self.opts["capnm"], self.sysdtw, 0
]
self.sql.insRec("gentrn", data=data)
# Update Commission Account
data = [
self.opts["conum"], self.rcacom, curdt, self.trndat,
gltyp, self.trnref, self.batno, cma, tax, gld, "", "",
0, self.opts["capnm"], self.sysdtw, 0
]
self.sql.insRec("gentrn", data=data)
if self.taxdf:
# VAT Transaction (ctlvtf)
data = [
self.opts["conum"], self.taxdf, "O", curdt, "R",
self.trntyp, self.batno, self.trnref, self.trndat,
self.acno, self.name, cma, tax, 0, self.opts["capnm"],
self.sysdtw, 0
]
self.sql.insRec("ctlvtf", data=data)
if self.glint == "Y" and tax:
# Update VAT Control
data = [
self.opts["conum"], self.convat, curdt,
self.trndat, gltyp, self.trnref, self.batno, tax,
0, gld, "", "", 0, self.opts["capnm"], self.sysdtw,
0
]
self.sql.insRec("gentrn", data=data)
return
if self.df.pag == 3 and self.movtyp == 3:
# Contract Fees
amt = float(ASD(0) - ASD(amt) + ASD(vat))
vat = float(ASD(0) - ASD(vat))
if self.glint == "Y":
# Update Contract Fee Account
data = [
self.opts["conum"], self.rcafee, curdt, self.trndat, gltyp,
self.trnref, self.batno, amt, vat, gld, "", "", 0,
self.opts["capnm"], self.sysdtw, 0
]
self.sql.insRec("gentrn", data=data)
if self.taxdf:
# VAT Transaction (ctlvtf)
data = (self.opts["conum"], self.vatcode, "O", curdt, "R",
self.trntyp, self.batno, self.trnref, self.trndat,
accod, self.trndet, amt, vat, 0, self.opts["capnm"],
self.sysdtw, 0)
self.sql.insRec("ctlvtf", data=data)
if self.glint == "Y":
# Update VAT Control
data = [
self.opts["conum"], self.convat, curdt, self.trndat,
gltyp, self.trnref, self.batno, vat, 0, gld, "", "", 0,
self.opts["capnm"], self.sysdtw, 0
]
self.sql.insRec("gentrn", data=data)
return
if self.df.pag == 3 and self.movtyp == 4:
# Services - Owner Recovery
rta = float(ASD(0) - ASD(amt))
rtv = float(ASD(0) - ASD(vat))
des = "Services Recovery on %s" % self.code
data = [
self.opts["conum"], self.owner, self.trntyp, self.trnref,
self.batno, self.trndat, rta, rtv, curdt, des, self.vatcode,
"", self.opts["capnm"], self.sysdtw, 0
]
self.sql.insRec("rcaowt", data=data)
if self.glint == "Y":
# General Ledger Transaction for Owner Control
gld = "%7s Services Recovery" % self.owner
data = [
self.opts["conum"], self.rcaown, curdt, self.trndat, gltyp,
self.trnref, self.batno, rta, 0, gld, "", "", 0,
self.opts["capnm"], self.sysdtw, 0
]
self.sql.insRec("gentrn", data=data)
return
# Tenants Journal Entries (Unallocated)
if self.glint == "Y":
amt = float(ASD(0) - ASD(amt))
data = [
self.opts["conum"], self.rcatrx, curdt, self.trndat, gltyp,
self.trnref, self.batno, amt, 0, gld, "", "", 0,
self.opts["capnm"], self.sysdtw, 0
]
self.sql.insRec("gentrn", data=data)
def doRaiseInvoice(self, number, trdt):
# Raise the Invoice
incamt = 0
vatamt = 0
curdt = int(trdt / 100)
batno = "B%s" % curdt
gls = {}
recs = self.sql.getRec("bkmrtt", where=[("brt_cono",
"=", self.opts["conum"]), ("brt_number", "=", number),
("brt_invno", "=", 0)])
if not recs:
return
invno = self.getRef(number)
self.docs.append(invno)
if number not in self.book:
self.book.append(number)
for rec in recs:
utyp = rec[self.sql.bkmrtt_col.index("brt_utype")]
ucod = rec[self.sql.bkmrtt_col.index("brt_ucode")]
rcod = rec[self.sql.bkmrtt_col.index("brt_rcode")]
rbas = rec[self.sql.bkmrtt_col.index("brt_rbase")]
quan = rec[self.sql.bkmrtt_col.index("brt_quant")]
rate = rec[self.sql.bkmrtt_col.index("brt_arate")]
days = rec[self.sql.bkmrtt_col.index("brt_bdays")]
umst = self.sql.getRec("bkmunm", where=[("bum_cono",
"=", self.opts["conum"]), ("bum_btyp", "=", utyp),
("bum_code", "=", ucod)], limit=1)
vatc = umst[self.sql.bkmunm_col.index("bum_vatc")]
if not vatc:
vatc = self.ctm_taxdf
vrte = getVatRate(self.sql, self.opts["conum"], vatc, trdt)
if vrte is None:
vrte = 0.0
if rbas == "A":
inca = quan * days * rate
elif rbas == "B":
inca = quan * rate
elif rbas == "C":
inca = days * rate
else:
inca = rate
vata = round(inca * vrte / (100 + vrte), 2)
exca = float(ASD(inca) - ASD(vata))
incamt = float(ASD(incamt) + ASD(inca))
vatamt = float(ASD(vatamt) - ASD(vata))
if self.glint == "Y":
slsa = umst[self.sql.bkmunm_col.index("bum_slsa")]
if slsa not in gls:
gls[slsa] = [0, 0, vatc]
gls[slsa][0] = float(ASD(gls[slsa][0]) - ASD(exca))
gls[slsa][1] = float(ASD(gls[slsa][1]) - ASD(vata))
data = [self.opts["conum"], number, 2, invno, batno, trdt, inca,
vata, curdt, "Booking %s-%s Raised" % (utyp, ucod), vatc, "",
self.opts["capnm"], self.sysdtw, 0]
self.sql.insRec("bkmtrn", data=data)
self.sql.updRec("bkmrtt", cols=["brt_invno", "brt_invdt",
"brt_vrate"], data=[invno, trdt, vrte],
where=[("brt_cono", "=", self.opts["conum"]),
("brt_number", "=", number), ("brt_utype", "=", utyp),
("brt_ucode", "=", ucod), ("brt_rcode", "=", rcod)])
if vata:
exc = float(ASD(0) - ASD(exca))
vat = float(ASD(0) - ASD(vata))
data = [self.opts["conum"], vatc, "O", curdt, "B", 1, batno,
invno, trdt, number, "Booking %s" % number, exc, vat, 0,
self.opts["capnm"], self.sysdtw, 0]
self.sql.insRec("ctlvtf", data=data)
if self.glint == "Y":
data = [self.opts["conum"], self.bkmctl, curdt, trdt, 1, invno,
batno, incamt, 0, "Booking %s" % number, "", "", 0,
self.opts["capnm"], self.sysdtw, 0]
self.sql.insRec("gentrn", data=data)
for acc in gls:
data = [self.opts["conum"], acc, curdt, trdt, 1, invno,
batno, gls[acc][0], gls[acc][1], "Booking %s" % number,
gls[acc][2], "", 0, self.opts["capnm"], self.sysdtw, 0]
self.sql.insRec("gentrn", data=data)
if gls[acc][1]:
data = [self.opts["conum"], self.vatctl, curdt, trdt, 1,
invno, batno, gls[acc][1], 0, "Booking %s" % number,
"", "", 0, self.opts["capnm"], self.sysdtw, 0]
self.sql.insRec("gentrn", data=data)
return True
def doEnd(self):
self.df.closeProcess()
tab = ["bkmmst", "bkmcon", "bkmrtt"]
col = ["bkm_number", "bkc_sname", "bkc_names", "bkm_arrive"]
whr = [("bkm_cono", "=", self.opts["conum"])]
if self.bkmqry == "N":
whr.append(("bkm_state", "in", ("C", "S")))
else:
whr.append(("bkm_state", "in", ("C", "Q", "S")))
whr.extend([("bkm_arrive", "between", self.sdate, self.edate),
("bkc_cono=bkm_cono", ), ("bkc_ccode=bkm_ccode", ),
("brt_cono=bkm_cono", ), ("brt_number=bkm_number", ),
("brt_invno", "=", 0)])
grp = "bkm_number, bkm_arrive, bkm_ccode, bkc_sname, bkc_names"
odr = "bkm_arrive, bkm_ccode"
dic = {}
for c in col:
for t in tab:
d = getattr(self.sql, "%s_dic" % t)
if c in d:
dic[c] = d[c]
if self.bkmdoc == "S":
recs = self.sql.getRec(tables=tab,
cols=col,
where=whr,
group=grp,
order=odr)
recs = getSingleRecords(self.opts["mf"],
tab,
col,
dic=dic,
where=recs,
ttype="D")
else:
recs = self.sql.getRec(tables=tab,
cols=col,
where=whr,
group=grp,
order=odr)
docs = []
for rec in recs:
# Raise the Invoice
bkno = rec[0]
trdt = rec[3]
incamt = 0
vatamt = 0
curdt = int(trdt / 100)
batno = "B%s" % curdt
gls = {}
trns = self.sql.getRec("bkmrtt",
where=[("brt_cono", "=",
self.opts["conum"]),
("brt_number", "=", bkno),
("brt_invno", "=", 0)])
if not trns:
continue
invno = self.getRef(bkno)
for trn in trns:
utyp = trn[self.sql.bkmrtt_col.index("brt_utype")]
ucod = trn[self.sql.bkmrtt_col.index("brt_ucode")]
rcod = trn[self.sql.bkmrtt_col.index("brt_rcode")]
rbas = trn[self.sql.bkmrtt_col.index("brt_rbase")]
quan = trn[self.sql.bkmrtt_col.index("brt_quant")]
rate = trn[self.sql.bkmrtt_col.index("brt_arate")]
days = trn[self.sql.bkmrtt_col.index("brt_bdays")]
umst = self.sql.getRec("bkmunm",
where=[("bum_cono", "=",
self.opts["conum"]),
("bum_btyp", "=", utyp),
("bum_code", "=", ucod)],
limit=1)
vatc = umst[self.sql.bkmunm_col.index("bum_vatc")]
if not vatc:
vatc = self.ctm_taxdf
vrte = getVatRate(self.sql, self.opts["conum"], vatc, trdt)
if vrte is None:
vrte = 0.0
if rbas == "A":
inca = quan * days * rate
elif rbas == "B":
inca = quan * rate
elif rbas == "C":
inca = days * rate
else:
inca = rate
vata = round(inca * vrte / (100 + vrte), 2)
exca = float(ASD(inca) - ASD(vata))
incamt = float(ASD(incamt) + ASD(inca))
vatamt = float(ASD(vatamt) - ASD(vata))
if self.glint == "Y":
slsa = umst[self.sql.bkmunm_col.index("bum_slsa")]
if slsa not in gls:
gls[slsa] = [0, 0, vatc]
gls[slsa][0] = float(ASD(gls[slsa][0]) - ASD(exca))
gls[slsa][1] = float(ASD(gls[slsa][1]) - ASD(vata))
data = [
self.opts["conum"], bkno, 2, invno, batno, trdt, inca,
vata, curdt,
"Booking %s-%s Raised" % (utyp, ucod), vatc, "",
self.opts["capnm"], self.sysdtw, 0
]
self.sql.insRec("bkmtrn", data=data)
self.sql.updRec("bkmrtt",
cols=["brt_vrate", "brt_invno", "brt_invdt"],
data=[vrte, invno, trdt],
where=[("brt_cono", "=", self.opts["conum"]),
("brt_number", "=", bkno),
("brt_utype", "=", utyp),
("brt_ucode", "=", ucod),
("brt_rcode", "=", rcod)])
if vata:
exc = float(ASD(0) - ASD(exca))
vat = float(ASD(0) - ASD(vata))
data = [
self.opts["conum"], vatc, "O", curdt, "B", 1, batno,
invno, trdt, bkno,
"Booking %s" % bkno, exc, vat, 0, self.opts["capnm"],
self.sysdtw, 0
]
self.sql.insRec("ctlvtf", data=data)
if self.glint == "Y":
data = [
self.opts["conum"], self.bkmctl, curdt, trdt, 1, invno,
batno, incamt, 0,
"Booking %s" % bkno, "", "", 0, self.opts["capnm"],
self.sysdtw, 0
]
self.sql.insRec("gentrn", data=data)
for acc in gls:
data = [
self.opts["conum"], acc, curdt, trdt, 1, invno, batno,
gls[acc][0], gls[acc][1],
"Booking %s" % bkno, gls[acc][2], "", 0,
self.opts["capnm"], self.sysdtw, 0
]
self.sql.insRec("gentrn", data=data)
if gls[acc][1]:
data = [
self.opts["conum"], self.vatctl, curdt, trdt, 1,
invno, batno, gls[acc][1], 0,
"Booking %s" % bkno, "", "", 0, self.opts["capnm"],
self.sysdtw, 0
]
self.sql.insRec("gentrn", data=data)
if invno not in docs:
docs.append(invno)
self.opts["mf"].dbm.commitDbase()
if docs:
PrintBookingInvoice(self.opts["mf"],
self.opts["conum"],
self.opts["conam"],
"I",
docs,
tname=self.tname,
repprt=self.df.repprt,
repeml=self.df.repeml,
copy="O")
self.doExit()
def doEnd(self):
self.df.closeProcess()
if self.allc == "N":
recs = getSingleRecords(self.opts["mf"], "drsrcm", ("dcm_num",
"dcm_desc"), where=self.wher)
else:
recs = self.sql.getRec("drsrcm", where=self.wher)
if recs:
if self.inv == "Y" and self.df.repeml[1] == "N":
self.fpdf = MyFpdf(orientation="L", fmat="A4",
name=self.__class__.__name__, head=128)
for dcm in recs:
num = dcm[self.sql.drsrcm_col.index("dcm_num")]
desc = dcm[self.sql.drsrcm_col.index("dcm_desc")]
day = dcm[self.sql.drsrcm_col.index("dcm_day")]
if day == 30:
self.trdt = mthendDate((self.bh.curdt * 100) + 1)
else:
self.trdt = (self.bh.curdt * 100) + day
vat = dcm[self.sql.drsrcm_col.index("dcm_vat")]
self.vatrte = getVatRate(self.sql, self.opts["conum"],
vat, self.trdt)
glac = dcm[self.sql.drsrcm_col.index("dcm_glac")]
nxt = self.sql.getRec("drstrn", cols=["max(drt_ref1)"],
where=[("drt_cono", "=", self.opts["conum"]), ("drt_ref1",
"like", "RC%03i%s" % (num, "%"))], limit=1)
if not nxt[0]:
nxt = 0
else:
nxt = int(nxt[0][5:])
tot_val = 0
tot_vat = 0
rec = self.sql.getRec("drsrct", where=[("dct_cono", "=",
self.opts["conum"]), ("dct_num", "=", num), ("dct_start",
"<=", self.bh.curdt), ("dct_end", ">=", self.bh.curdt)])
col = self.sql.drsrct_col
for dct in rec:
self.chain = dct[col.index("dct_chain")]
self.acno = dct[col.index("dct_acno")]
# Check for Redundancy
chk = self.sql.getRec("drsmst", cols=["drm_stat"],
where=[("drm_cono", "=", self.opts["conum"]),
("drm_chain", "=", self.chain), ("drm_acno", "=",
self.acno)], limit=1)
if chk[0] == "X":
continue
# Check for Valid Period
charge = False
start = dct[col.index("dct_start")]
year = int(start / 100)
month = start % 100
while start <= self.bh.curdt:
if start == self.bh.curdt:
charge = True
break
month += self.mths
if month > 12:
year += 1
month -= 12
start = (year * 100) + month
if not charge:
continue
# Create Transactions
nxt += 1
self.ref = "RC%03i%04i" % (num, nxt)
self.detail = textFormat(dct[col.index("dct_detail")], 73)
self.amnt = dct[col.index("dct_amnt")]
self.vmnt = round(self.amnt * self.vatrte / 100, 2)
self.tmnt = float(ASD(self.amnt) + ASD(self.vmnt))
tot_val = float(ASD(tot_val) + ASD(self.amnt))
tot_vat = float(ASD(tot_vat) + ASD(self.vmnt))
# Debtors (drstrn)
self.sql.insRec("drstrn", data=[self.opts["conum"],
self.chain, self.acno, 1, self.ref, self.bh.batno,
self.trdt, self.ref2, self.tmnt, self.vmnt,
self.bh.curdt, self.detail[0], vat, "Y",
self.opts["capnm"], self.sysdtw, 0])
if self.inv == "Y":
# Create Invoice
self.doInvoice()
# VAT (ctlvtf)
amnt = float(ASD(0) - ASD(self.amnt))
vmnt = float(ASD(0) - ASD(self.vmnt))
data = [self.opts["conum"], vat, "O", self.bh.curdt,
"D", 1, self.bh.batno, self.ref, self.trdt, self.acno,
self.detail[0], amnt, vmnt, 0, self.opts["capnm"],
self.sysdtw, 0]
self.sql.insRec("ctlvtf", data=data)
if self.glint == "Y":
ref = "RC%07i" % num
# Update Debtors Control
amnt = float(ASD(tot_val) + ASD(tot_vat))
data = (self.opts["conum"], self.drsctl, self.bh.curdt,
self.trdt, 1, ref, self.bh.batno, amnt, 0, desc,
"", "", 0, self.opts["capnm"], self.sysdtw, 0)
self.sql.insRec("gentrn", data=data)
# Update Sales Account
amnt = float(ASD(0) - ASD(tot_val))
data = (self.opts["conum"], glac, self.bh.curdt, self.trdt,
1, ref, self.bh.batno, amnt, 0, desc, "", "", 0,
self.opts["capnm"], self.sysdtw, 0)
self.sql.insRec("gentrn", data=data)
amnt = float(ASD(0) - ASD(tot_vat))
if amnt:
# Update VAT Control
data = (self.opts["conum"], self.vatctl, self.bh.curdt,
self.trdt, 1, ref, self.bh.batno, amnt, 0, desc,
"", "", 0, self.opts["capnm"], self.sysdtw, 0)
self.sql.insRec("gentrn", data=data)
# Update Recurring Charge (drsrcm)
self.sql.updRec("drsrcm", cols=["dcm_last"],
data=[self.bh.curdt], where=[("dcm_cono", "=",
self.opts["conum"]), ("dcm_num", "=", num), ("dcm_freq",
"=", self.freq)])
self.opts["mf"].dbm.commitDbase()
if self.inv == "Y" and self.df.repeml[1] == "N":
self.doPrint()
self.opts["mf"].closeLoop()
def doChgVCode(self, frt, pag, r, c, p, i, w):
self.vatrte = getVatRate(self.sql, self.opts["conum"], w, self.chgdte)
if self.vatrte is None:
return "Invalid VAT Code"