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"