def doEmailReps(self):
subj = "Sundry Reports"
ok = False
while not ok:
emls = self.tadd.split(",")
for eml in emls:
sp = SplashScreen(self.opts["mf"].body, "E-Mailing the "\
"Message to\n\n%s\n\nPlease Wait........" % eml)
ok = sendMail(self.smtp, self.fadd, eml, subj, mess=self.mess,
attach=self.fles, wrkdir=self.opts["mf"].rcdic["wrkdir"])
sp.closeSplash()
if not ok:
if self.skip == "Y":
ok = "SKIPPED"
else:
ok = askQuestion(self.opts["mf"].body, "E-Mail Error",
"Problem Delivering This Message.\n\nTo: "\
"%s\nSubject: %s\n\nWould You Like to Retry?" \
% (eml, subj))
if ok == "yes":
ok = False
else:
ok = "FAILED"
else:
ok = "OK"
# Log the email attempt into table emllog.
self.sql.insRec("emllog", data=[self.fadd, self.tadd, subj,
"%04i-%02i-%02i %02i:%02i" % time.localtime()[0:5], ok])
def doEnd(self):
self.df.closeProcess()
if self.opts["period"][0] == 1:
self.sql.updRec("ctlynd", cols=["cye_start", "cye_end"],
data=[self.s0, self.e0], where=[("cye_cono", "=",
self.opts["conum"]), ("cye_period", "=", 0)])
for per in self.pers:
num = per[self.sql.ctlynd_col.index("cye_period")]
if num == self.opts["period"][0]:
self.start = self.s_per
self.end = self.e_per
else:
self.start = self.getStartDate(self.end)
self.end = self.getEndDate(self.start)
self.sql.updRec("ctlynd", cols=["cye_start", "cye_end"],
data=[self.start, self.end], where=[("cye_cono", "=",
self.opts["conum"]), ("cye_period", "=", num)])
spl = SplashScreen(self.opts["mf"].body,
text="Running Year End Routine for Period %s" % num)
per = getPeriods(self.opts["mf"], self.opts["conum"], num - 1)
per = (num - 1, (per[0].work, per[0].disp), (per[1].work, per[1].disp))
callModule(self.opts["mf"], None, "msy010", coy=(self.opts["conum"],
self.opts["conam"]), period=per, user=self.opts["capnm"], args="N")
spl.closeSplash()
self.opts["mf"].dbm.commitDbase(ask=True)
self.opts["mf"].closeLoop()
def doFixAge(self):
if self.opts["mf"] and self.opts["mf"].window:
spl = SplashScreen(self.opts["mf"].body,
"Checking Age Records\n\nPlease Wait")
self.opts["mf"].updateStatus("Checking Age Records")
elif self.opts["bar"]:
print("Checking Age Records .... Please Wait")
for sss in ("crs", "drs"):
sql = Sql(self.dbm, ["%smst" % sss, "%sage" % sss], prog=__name__)
if sss == "drs":
col = ["dra_cono", "dra_chain", "dra_acno"]
grp = "dra_cono, dra_chain, dra_acno"
else:
col = ["cra_cono", "cra_acno"]
grp = "cra_cono, cra_acno"
recs = sql.getRec("%sage" % sss, cols=col, group=grp)
for rec in recs:
whr = []
for n, c in enumerate(col):
whr.append((c.replace("a_", "m_"), "=", rec[n]))
if not sql.getRec("%smst" % sss, where=whr):
sql.delRec("%sage" % sss, cols=col, data=rec)
self.dbm.commitDbase()
if self.opts["mf"] and self.opts["mf"].window:
self.opts["mf"].updateStatus("")
spl.closeSplash()
def doEnd(self):
self.df.closeProcess()
tab = ["memmst"]
col = [
"mlm_memno",
"mlm_surname",
"mlm_names",
"mlm_dob%10000 as date"]
whr = [
("mlm_cono", "=", self.opts["conum"]),
("mlm_state", "=", "A"),
("date", "between", self.frdt % 10000, self.todt % 10000)]
if self.cat:
tab.append("memcat")
whr.append(("mlc_cono=mlm_cono",))
whr.append(("mlc_memno=mlm_memno",))
whr.append(("mlc_type", "=", self.cat))
if self.cod:
whr.append(("mlc_code", "=", self.cod))
if self.sort == "N":
odr = "mlm_memno"
elif self.sort == "M":
odr = "mlm_surname"
else:
odr = "date"
sp = SplashScreen(self.opts["mf"].body,
"Preparing Report ... Please Wait")
recs = self.sql.getRec(tables=tab, cols=col, where=whr, order=odr)
sp.closeSplash()
if not recs:
self.opts["mf"].closeLoop()
return
name = self.__class__.__name__
head = ["Members Birthday Report"]
cols = [
["a", "NA", 7, "Mem-No", "y"],
["b", "NA", 50, "Surname", "y"],
["c", "NA", 50, "Names", "y"],
["d", "DM", 5, "Birthday", "y"]]
RepPrt(self.opts["mf"], conum=self.opts["conum"],
conam=self.opts["conam"], name=name, tables=recs, heads=head,
cols=cols, ttype="D", repprt=self.df.repprt,
repeml=self.df.repeml, fromad=self.fromad)
self.opts["mf"].closeLoop()
def doFinal(self):
chg = False
for tab in ("ffield", "ftable"):
chks = self.sql.getRec("ffield", where=[("ff_tabl", "=", tab)])
for num, chk in enumerate(chks):
if chk[1:] != tabdic[tab]["fld"][num]:
chg = True
break
for tab in ("ffield", "ftable"):
chks = self.sql.getRec("ftable", where=[("ft_tabl", "=", tab)])
for num, chk in enumerate(chks):
sch = chk[1:4]
for ft in chk[4:]:
if ft:
sch.append(ft)
if sch != tabdic[tab]["idx"][num]:
chg = True
break
if not chg:
return
if self.opts["mf"] and self.opts["mf"].window:
spl = SplashScreen(self.opts["mf"].body,
"Finishing Off, Please Wait ....")
self.opts["mf"].updateStatus("")
elif self.opts["bar"]:
print("Finishing Off, Please Wait ....")
# Drop Indexes
for tab in ("ffield", "ftable"):
self.doDropIndex(tab)
# Delete Record
self.sql.delRec("ffield",
where=[("ff_tabl", "in", ("ffield", "ftable"))])
self.sql.delRec("ftable",
where=[("ft_tabl", "in", ("ffield", "ftable"))])
# Populate and Create Indexes
for tab in ("ffield", "ftable"):
self.dbm.populateTable(tab)
self.doCreateIndexes(tab)
self.dbm.commitDbase()
if self.opts["mf"] and self.opts["mf"].window:
spl.closeSplash()
def doEnd(self):
self.df.closeProcess()
if self.reptyp == "P":
sp = SplashScreen(self.opts["mf"].body,
"Preparing Report ... Please Wait")
recs = self.sql.getRec("memmst",
cols=["mlm_memno"],
where=[("mlm_cono", "=",
self.opts["conum"]),
("mlm_state", "=", "A")])
for rec in recs:
data = doChkCatChg(self.opts["mf"], self.opts["conum"], rec[0],
self.start.work)
if data:
self.sql.insRec(
"chglog",
data=[
"memcat", "D",
"%03i%06i%1s%02i" %
(self.opts["conum"], rec[0], "B", data[0]), "",
self.dte, "",
str(data[1]),
str(data[2]), "", 0
])
self.sql.insRec(
"chglog",
data=[
"memcat", "N",
"%03i%06i%1s%02i" %
(self.opts["conum"], rec[0], "B", data[7]), "",
self.dte, "",
str(self.start.work),
str(0), "", 0
])
sp.closeSplash()
self.processRecords()
self.closeProcess()
def doGenerate(self):
self.opts["mf"].updateStatus("")
but = (("Members", "M"), ("Non Members", "N"), ("All", "A"), ("None",
"X"))
ok = askChoice(self.opts["mf"].body, "ARE YOU SURE???",
"Are you Certain this is what you want To Do? This will "\
"Automatically Generate New Tab Numbers For Selected Range "\
"Based On Member's Names and Gender!", butt=but, default="None")
if ok == "X":
self.df.focusField("T", 0, 1)
return
self.df.closeProcess()
splash = SplashScreen(self.opts["mf"].body,
"Generating New Tab Numbers ... Please Wait")
# Create temporary tables
for ot in self.tables:
tt = "%s_temp" % ot[0]
self.sql.sqlRec("Create table %s as Select * from %s "\
"where %s = %s" % (tt, ot[0], ot[1], self.opts["conum"]))
self.sql.delRec(ot[0], where=[(ot[1], "=", self.opts["conum"])])
mem = self.sql.sqlRec("Select * from bwltab_temp where btb_cono = %s "\
"and btb_tab < %s order by btb_surname, btb_names" %
(self.opts["conum"], self.nstart))
non = self.sql.sqlRec("Select * from bwltab_temp where btb_cono = %s "\
"and btb_tab >= %s order by btb_surname, btb_names" %
(self.opts["conum"], self.nstart))
mstart = self.mstart
fstart = self.fstart
nstart = self.nstart
key = {}
for tab in mem:
otb = tab[self.sql.bwltab_col.index("btb_tab")]
if ok in ("A", "M"):
gdr = tab[self.sql.bwltab_col.index("btb_gender")]
if gdr == "M":
new = mstart
mstart += 1
else:
new = fstart
fstart += 1
key[otb] = new
else:
key[otb] = otb
for tab in non:
otb = tab[self.sql.bwltab_col.index("btb_tab")]
if ok in ("A", "N"):
key[otb] = nstart
nstart += 1
else:
key[otb] = otb
for ot in self.tables:
tt = "%s_temp" % ot[0]
cc = getattr(self.sql, "%s_col" % ot[0])
recs = self.sql.sqlRec("Select * from %s" % tt)
for rec in recs:
for k in ot[2:]:
c = rec[cc.index(k)]
if c not in key:
continue
rec[cc.index(k)] = key[c]
self.sql.insRec(ot[0], data=rec)
self.sql.sqlRec("Drop table %s" % tt)
self.opts["mf"].dbm.commitDbase(ask=True)
splash.closeSplash()
if "wait" not in self.opts:
self.opts["mf"].closeLoop()
def doPopulate(self):
if not self.newgen:
showError(self.opts["mf"].body, "Populate Error",
"You Cannot Populate with Existing Accounts")
self.df.focusField(self.df.frt, self.df.pag, self.df.col)
return
if self.gldep == "Y":
showError(self.opts["mf"].body, "Populate Error",
"You Cannot Populate with Departments Enabled")
self.df.focusField(self.df.frt, self.df.pag, self.df.col)
return
self.igcrs = "N"
self.igdrs = "N"
self.igstr = "N"
self.df.setWidget(self.df.B3, state="disabled")
self.df.setWidget(self.df.mstFrame, state="hide")
tit = ("Integrate Subsiduary Ledgers", )
r1s = (("Yes", "Y"), ("No", "N"))
fld = []
col = 0
if "CR" in self.mods:
fld.append((("T", 0, col, 0), ("IRB", r1s), 0, "Creditor's Ledger",
"Creditor's Ledger", "N", "N", self.doIgCrs, None,
None, None, None))
col += 1
if "DR" in self.mods:
fld.append((("T", 0, col, 0), ("IRB", r1s), 0, "Debtor's Ledger",
"Debtor's Ledger", "N", "N", self.doIgDrs, None, None,
None, None))
col += 1
if "ST" in self.mods:
fld.append((("T", 0, col, 0), ("IRB", r1s), 0, "Stores's Ledger",
"Stores's Ledger", "N", "N", self.doIgStr, None, None,
None, None))
if fld:
self.ig = TartanDialog(self.opts["mf"],
title=tit,
tops=True,
eflds=fld,
tend=((self.doIgEnd, "y"), ),
txit=(self.doIgExit, ))
self.ig.mstFrame.wait_window()
if self.igexit:
self.doQuit()
return
sp = SplashScreen(self.opts["mf"].body,
"Populating Records\n\nPlease Wait ...")
# genmst
genmst = datdic["genmst"]
for dat in genmst:
dat.insert(0, self.opts["conum"])
dat.append("Y")
if dat[2] == "B":
dat.append("N")
else:
dat.append(self.taxdf)
self.sql.insRec("genmst", data=dat)
# genrpt
genrpt = datdic["genrpt"]
for dat in genrpt:
dat.insert(0, self.opts["conum"])
self.sql.insRec("genrpt", data=dat)
# ctlctl
crsctl = 0
drsctl = 0
stksoh = 0
ctlctl = datdic["ctlctl"]
for dat in ctlctl:
if dat[0] in ("crs_ctl", "dis_rec"):
if self.igcrs != "Y":
continue
if dat[0] == "crs_ctl":
crsctl = int(dat[2])
elif dat[0] in ("drs_ctl", "dis_all"):
if self.igdrs != "Y":
continue
if dat[0] == "drs_ctl":
drsctl = int(dat[2])
elif dat[0] in ("stk_soh", "stk_susp"):
if self.igstr != "Y":
continue
if dat[0] == "stk_soh":
stksoh = int(dat[2])
elif dat[0] in ("wag_ctl", "wag_slc", "wag_sli"):
continue
dat.insert(0, self.opts["conum"])
self.sql.insRec("ctlctl", data=dat)
if "CR" in self.mods:
chk = self.sql.getRec("crsctl",
where=[("ctc_cono", "=", self.opts["conum"])
])
if not chk:
self.sql.insRec("crsctl",
data=[
self.opts["conum"], self.igcrs, "E", "", 0,
0, "remittance_advice", ""
])
else:
self.sql.updRec("crsctl",
cols=["ctc_glint"],
data=[self.igcrs],
where=[("ctc_cono", "=", self.opts["conum"])])
if self.igcrs == "Y" and crsctl:
self.sql.updRec("genmst",
cols=["glm_ind"],
data=["N"],
where=[("glm_cono", "=", self.opts["conum"]),
("glm_acno", "=", crsctl)])
if "DR" in self.mods:
chk = self.sql.getRec("drsctl",
where=[("ctd_cono", "=", self.opts["conum"])
])
if not chk:
self.sql.insRec("drsctl",
data=[
self.opts["conum"], self.igdrs, "E", "N",
"statement_normal", "Y", ""
])
else:
self.sql.updRec("drsctl",
cols=["ctd_glint"],
data=[self.igdrs],
where=[("ctd_cono", "=", self.opts["conum"])])
if self.igdrs == "Y" and drsctl:
self.sql.updRec("genmst",
cols=["glm_ind"],
data=["N"],
where=[("glm_cono", "=", self.opts["conum"]),
("glm_acno", "=", drsctl)])
if "ST" in self.mods:
chk = self.sql.getRec("strctl",
where=[("cts_cono", "=", self.opts["conum"])
])
if not chk:
self.sql.insRec("strctl",
data=[
self.opts["conum"], self.igstr, "N", 1,
"N", "purchase_order", ""
])
else:
self.sql.updRec("strctl",
cols=["cts_glint"],
data=[self.igstr],
where=[("cts_cono", "=", self.opts["conum"])])
chk = self.sql.getRec("strloc",
where=[("srl_cono", "=", self.opts["conum"])
])
if not chk:
self.sql.insRec("strloc",
data=[
self.opts["conum"], "1",
"Location Number One", "", "", "", ""
])
if self.igstr == "Y" and stksoh:
self.sql.updRec("genmst",
cols=["glm_ind"],
data=["N"],
where=[("glm_cono", "=", self.opts["conum"]),
("glm_acno", "=", stksoh)])
if "SI" in self.mods:
chk = self.sql.getRec("slsctl",
where=[("ctv_cono", "=", self.opts["conum"])
])
if not chk:
self.sql.insRec(
"slsctl",
data=[self.opts["conum"], "Y", "Y", "sales_document", ""])
sp.closeSplash()
self.df.butt[1][4] = None
self.df.butt[1][5] = None
self.opts["mf"].dbm.commitDbase()
self.df.setWidget(self.df.mstFrame, state="show")
self.df.focusField(self.df.frt, self.df.pag, self.df.col)
def doEnd(self):
self.df.closeProcess()
if self.df.repeml[1] == "Y":
if not self.df.repeml[2]:
owns = self.sql.getRec("bksown",
cols=["bof_mail"],
where=[("bof_mail", "<>", ""),
("bof_stat", "=", "C")])
adds = None
for own in owns:
if not adds:
adds = own[0]
else:
adds = "%s,%s" % (adds, own[0])
self.df.repeml[2] = adds
if not self.df.repeml[3]:
self.df.repeml[3] = """Dear Member
Attached please find the latest list of books.
All books which came onto the list at the last meeting are highlighted and, if available, a precis will be printed at the end.
Thanks and Regards.
"""
tab = ["bksmst", "bksaut", "bksown"]
col = [
"bmf_stat", "bmf_titl", "bmf_code", "baf_snam", "baf_fnam",
"bmf_mnth", "bof_fnam", "bof_snam"
]
odr = ""
if self.status == "C":
whr = [("bmf_stat", "=", "C")]
elif self.status == "R":
whr = [("bmf_stat", "=", "X")]
else:
whr = []
odr = "bmf_stat"
whr.extend([("baf_code=bmf_auth", ), ("bof_code=bmf_ownr", )])
if self.order == "T":
if odr:
odr = "%s, bmf_titl" % odr
else:
odr = "bmf_titl"
elif self.order == "D":
if odr:
odr = "%s, bmf_mnth, bmf_titl" % odr
else:
odr = "bmf_mnth, bmf_titl"
elif self.order == "A":
if odr:
odr = "%s, baf_snam, baf_fnam, bmf_titl" % odr
else:
odr = "baf_snam, baf_fnam, bmf_titl"
elif self.order == "O":
if odr:
odr = "%s, bmf_ownr, bmf_titl" % odr
else:
odr = "bmf_ownr, bmf_titl"
recs = self.sql.getRec(tables=tab, cols=col, where=whr, order=odr)
if not recs:
showError(self.opts["mf"].body, "Selection Error",
"No Records Selected")
self.opts["mf"].closeLoop()
return
self.fpdf = MyFpdf(name="bs3010", head=90, auto=True)
self.fpdf.header = self.pageHeading
self.stat = recs[0][0]
self.fpdf.add_page()
new = []
for rec in recs:
stat = CCD(rec[0], "UA", 1).disp
if stat != self.stat:
self.stat = stat
self.fpdf.add_page()
titl = CCD(rec[1], "NA", 30).disp
code = CCD(rec[2], "UI", 4).disp
auth = CCD("%s, %s" % (rec[3], rec[4]), "NA", 30).disp
mnth = CCD(rec[5], "D2", 7).disp
ownr = CCD("%s %s" % (rec[6], rec[7][0]), "NA", 12).disp
if rec[5] < self.last:
fill = 0
else:
new.append([auth, titl])
fill = 1
self.fpdf.drawText("%1s %30s %4s %30s %7s %10s" %
(stat, titl, code, auth, mnth, ownr),
h=5,
fill=fill)
if new:
sp = SplashScreen(
self.opts["mf"].body,
"Preparing Summary of New Books ... Please Wait")
self.new = True
self.fpdf.add_page()
for book in new:
try:
if self.fpdf.get_y() > 260:
self.fpdf.add_page()
desc = self.getDesc(book)
if desc:
w = self.fpdf.cwth * 9
desc = desc.rstrip().encode("latin-1",
"ignore").decode(
"latin-1", "ignore")
self.fpdf.drawText(w=w, txt="Title: ", font="B", ln=0)
self.fpdf.drawText(txt=book[1].rstrip(), font="I")
self.fpdf.drawText(w=w, txt="Author: ", font="B", ln=0)
self.fpdf.drawText(txt=book[0].rstrip(), font="I")
self.fpdf.drawText(w=w,
txt="Details: ",
font="B",
ln=0)
self.fpdf.drawText(txt=desc, font="I", ctyp="M")
if book != new[-1]:
self.fpdf.drawText()
except:
pass
sp.closeSplash()
pdfnam = getModName(self.opts["mf"].rcdic["wrkdir"],
self.__class__.__name__,
self.opts["conum"],
ext="pdf")
self.fpdf.output(pdfnam, "F")
head = "Book List as at %s" % (self.curdt)
doPrinter(mf=self.opts["mf"],
conum=self.opts["conum"],
pdfnam=pdfnam,
header=head,
repprt=self.df.repprt,
repeml=self.df.repeml)
self.opts["mf"].closeLoop()
def doImport(self):
grps = []
commit = False
self.opts["mf"].updateStatus("")
self.df.mstFrame.focus_force()
self.df.mstFrame.winfo_toplevel().update()
self.df.setWidget(self.df.mstFrame, state="hide")
spl = SplashScreen(self.opts["mf"].body,
"Importing Contact Details\n\nPlease Wait")
sql = Sql(self.opts["mf"].dbm,
tables="ftable",
prog=self.__class__.__name__)
bkm = sql.getRec("ftable",
where=[("ft_tabl", "=", "bkmcon")],
cols=["count(*)"],
limit=1)
if bkm and bkm[0]:
commit = True
sql = Sql(self.opts["mf"].dbm,
tables="bkmcon",
prog=self.__class__.__name__)
bkc = sql.bkmcon_col
recs = sql.getRec("bkmcon")
for rec in recs:
if not rec[bkc.index("bkc_telno")] and not \
rec[bkc.index("bkc_faxno")] and not \
rec[bkc.index("bkc_celno")] and not \
rec[bkc.index("bkc_email")]:
continue
nam = rec[bkc.index("bkc_sname")]
fnm = rec[bkc.index("bkc_names")]
if fnm:
nam = "%s, %s" % (nam, fnm.split()[0])
data = [
nam, rec[bkc.index("bkc_addr1")],
rec[bkc.index("bkc_addr2")], rec[bkc.index("bkc_addr3")],
rec[bkc.index("bkc_pcode")], rec[bkc.index("bkc_telno")],
rec[bkc.index("bkc_faxno")], rec[bkc.index("bkc_celno")],
rec[bkc.index("bkc_email")], "BKM"
]
chk = self.sql.getRec("telmst",
where=[("tdm_name", "=", nam)],
limit=1)
if not chk:
self.sql.insRec("telmst", data=data)
elif data != chk[:len(data)]:
tdm = self.sql.telmst_col
data.append(chk[tdm.index("tdm_xflag")])
self.sql.updRec("telmst",
data=data,
where=[("tdm_name", "=", nam)])
grps.append(["BKM", "Bookings"])
bwl = sql.getRec("ftable",
where=[("ft_tabl", "=", "bwltab")],
cols=["count(*)"],
limit=1)
if bwl and bwl[0]:
commit = True
sql = Sql(self.opts["mf"].dbm,
tables="bwltab",
prog=self.__class__.__name__)
btb = sql.bwltab_col
recs = sql.getRec("bwltab")
for rec in recs:
if not rec[btb.index("btb_home")] and not \
rec[btb.index("btb_work")] and not \
rec[btb.index("btb_cell")] and not \
rec[btb.index("btb_mail")]:
continue
nam = rec[btb.index("btb_surname")]
fnm = rec[btb.index("btb_names")]
if fnm:
nam = "%s, %s" % (nam, fnm.split()[0])
data = [
nam, rec[btb.index("btb_add1")],
rec[btb.index("btb_add2")], rec[btb.index("btb_add3")],
rec[btb.index("btb_pcod")], rec[btb.index("btb_home")],
rec[btb.index("btb_work")], rec[btb.index("btb_cell")],
rec[btb.index("btb_mail")], "BWL"
]
chk = self.sql.getRec("telmst",
where=[("tdm_name", "=", nam)],
limit=1)
if not chk:
self.sql.insRec("telmst", data=data)
elif data != chk[:len(data)]:
tdm = self.sql.telmst_col
data.append(chk[tdm.index("tdm_xflag")])
self.sql.updRec("telmst",
data=data,
where=[("tdm_name", "=", nam)])
grps.append(["BWL", "Bowls"])
crs = sql.getRec("ftable",
where=[("ft_tabl", "=", "crsmst")],
cols=["count(*)"],
limit=1)
if crs and crs[0]:
commit = True
sql = Sql(self.opts["mf"].dbm,
tables="crsmst",
prog=self.__class__.__name__)
crm = sql.crsmst_col
recs = sql.getRec("crsmst", where=[("crm_stat", "<>", "X")])
for rec in recs:
nam = "%s" % rec[crm.index("crm_name")]
data = [
nam, rec[crm.index("crm_add1")],
rec[crm.index("crm_add2")], rec[crm.index("crm_add3")],
rec[crm.index("crm_pcod")], rec[crm.index("crm_tel")],
rec[crm.index("crm_fax")], "", "", "CRS"
]
chk = self.sql.getRec("telmst",
where=[("tdm_name", "=", nam)],
limit=1)
if not chk:
self.sql.insRec("telmst", data=data)
elif data != chk[:len(data)]:
tdm = self.sql.telmst_col
data.append(chk[tdm.index("tdm_xflag")])
self.sql.updRec("telmst",
data=data,
where=[("tdm_name", "=", nam)])
con = rec[crm.index("crm_mgr")]
eml = rec[crm.index("crm_mgr_email")]
if eml:
data = [nam, con, "Manager", "", "", eml]
chk = self.sql.getRec("telcon",
where=[("tdc_name", "=", nam),
("tdc_contact", "=", con)],
limit=1)
if not chk:
self.sql.insRec("telcon", data=data)
elif data != chk[:len(data)]:
tdc = self.sql.telcon_col
data.append(chk[tdc.index("tdc_xflag")])
self.sql.updRec("telcon",
data=data,
where=[("tdc_name", "=", nam),
("tdc_contact", "=", con)])
con = rec[crm.index("crm_acc")]
eml = rec[crm.index("crm_acc_email")]
if eml:
data = [nam, con, "Accounts", "", "", eml]
chk = self.sql.getRec("telcon",
where=[("tdc_name", "=", nam),
("tdc_contact", "=", con)],
limit=1)
if not chk:
self.sql.insRec("telcon", data=data)
elif data != chk[:len(data)]:
tdc = self.sql.telcon_col
data.append(chk[tdc.index("tdc_xflag")])
self.sql.updRec("telcon",
data=data,
where=[("tdc_name", "=", nam),
("tdc_contact", "=", con)])
con = rec[crm.index("crm_ord")]
eml = rec[crm.index("crm_ord_email")]
if eml:
data = [nam, con, "Orders", "", "", eml]
chk = self.sql.getRec("telcon",
where=[("tdc_name", "=", nam),
("tdc_contact", "=", con)],
limit=1)
if not chk:
self.sql.insRec("telcon", data=data)
elif data != chk[:len(data)]:
tdc = self.sql.telcon_col
data.append(chk[tdc.index("tdc_xflag")])
self.sql.updRec("telcon",
data=data,
where=[("tdc_name", "=", nam),
("tdc_contact", "=", con)])
grps.append(["CRS", "Creditors"])
drs = sql.getRec("ftable",
where=[("ft_tabl", "=", "drsmst")],
cols=["count(*)"],
limit=1)
if drs and drs[0]:
commit = True
sql = Sql(self.opts["mf"].dbm,
tables="drsmst",
prog=self.__class__.__name__)
drm = sql.drsmst_col
recs = sql.getRec("drsmst", where=[("drm_stat", "<>", "X")])
for rec in recs:
nam = "%s" % rec[drm.index("drm_name")]
data = [
nam, rec[drm.index("drm_add1")],
rec[drm.index("drm_add2")], rec[drm.index("drm_add3")],
rec[drm.index("drm_pcod")], rec[drm.index("drm_tel")],
rec[drm.index("drm_fax")], "", "", "DRS"
]
chk = self.sql.getRec("telmst",
where=[("tdm_name", "=", nam)],
limit=1)
if not chk:
self.sql.insRec("telmst", data=data)
elif data != chk[:len(data)]:
tdm = self.sql.telmst_col
data.append(chk[tdm.index("tdm_xflag")])
self.sql.updRec("telmst",
data=data,
where=[("tdm_name", "=", nam)])
con = rec[drm.index("drm_mgr")]
eml = rec[drm.index("drm_mgr_email")]
if eml:
data = [nam, con, "Manager", "", "", eml]
chk = self.sql.getRec("telcon",
where=[("tdc_name", "=", nam),
("tdc_contact", "=", con)],
limit=1)
if not chk:
self.sql.insRec("telcon", data=data)
elif data != chk[:len(data)]:
tdc = self.sql.telcon_col
data.append(chk[tdc.index("tdc_xflag")])
self.sql.updRec("telcon",
data=data,
where=[("tdc_name", "=", nam),
("tdc_contact", "=", con)])
con = rec[drm.index("drm_acc")]
eml = rec[drm.index("drm_acc_email")]
if eml:
data = [nam, con, "Accounts", "", "", eml]
chk = self.sql.getRec("telcon",
where=[("tdc_name", "=", nam),
("tdc_contact", "=", con)],
limit=1)
if not chk:
self.sql.insRec("telcon", data=data)
elif data != chk[:len(data)]:
tdc = self.sql.telcon_col
data.append(chk[tdc.index("tdc_xflag")])
self.sql.updRec("telcon",
data=data,
where=[("tdc_name", "=", nam),
("tdc_contact", "=", con)])
con = rec[drm.index("drm_sls")]
eml = rec[drm.index("drm_sls_email")]
if eml:
data = [nam, con, "Orders", "", "", eml]
chk = self.sql.getRec("telcon",
where=[("tdc_name", "=", nam),
("tdc_contact", "=", con)],
limit=1)
if not chk:
self.sql.insRec("telcon", data=data)
elif data != chk[:len(data)]:
tdc = self.sql.telcon_col
data.append(chk[tdc.index("tdc_xflag")])
self.sql.updRec("telcon",
data=data,
where=[("tdc_name", "=", nam),
("tdc_contact", "=", con)])
grps.append(["DRS", "Debtors"])
mem = sql.getRec("ftable",
where=[("ft_tabl", "=", "memmst")],
cols=["count(*)"],
limit=1)
if mem and mem[0]:
commit = True
sql = Sql(self.opts["mf"].dbm,
tables=["memmst", "memadd", "memkon"],
prog=self.__class__.__name__)
mlm = sql.memmst_col
mla = sql.memadd_col
recs = sql.getRec("memmst", where=[("mlm_state", "=", "A")])
for rec in recs:
coy = rec[mlm.index("mlm_cono")]
num = rec[mlm.index("mlm_memno")]
nam = "%s, %s" % (rec[mlm.index("mlm_surname")],
rec[mlm.index("mlm_names")])
add = sql.getRec("memadd",
where=[("mla_cono", "=", coy),
("mla_memno", "=", num),
("mla_type", "=", "P")],
limit=1)
if not add:
add = [coy, num, "P", "", "", "", "", "", "", ""]
add3 = add[mla.index("mla_add3")]
city = add[mla.index("mla_city")]
coun = add[mla.index("mla_country")]
if not add3:
if city:
add3 = city
if coun:
add3 = "%s, %s" % (add3, coun)
elif coun:
add3 = coun
kon = sql.getRec(tables=["memctk", "memkon"],
cols=["mck_type", "mlk_detail"],
where=[("mlk_cono", "=", coy),
("mlk_memno", "=", num),
("mck_code=mlk_code", )])
tel = ""
fax = ""
cel = ""
eml = ""
for k in kon:
if k[0] == "E" and not eml:
eml = k[1]
elif k[0] == "H":
tel = k[1]
elif k[0] == "W" and not tel:
tel = k[1]
elif k[0] == "M":
cel = k[1]
data = [
nam, add[mla.index("mla_add1")],
add[mla.index("mla_add2")], add3,
add[mla.index("mla_code")], tel, fax, cel, eml, "MEM"
]
chk = self.sql.getRec("telmst",
where=[("tdm_name", "=", nam)],
limit=1)
if not chk:
self.sql.insRec("telmst", data=data)
elif data != chk[:len(data)]:
tdm = self.sql.telmst_col
data.append(chk[tdm.index("tdm_xflag")])
self.sql.updRec("telmst",
data=data,
where=[("tdm_name", "=", nam)])
grps.append(["MEM", "Members"])
# Others
tabs = {
"BKS": [
"Book Club", "bksown", None,
[
"bof_snam", "bof_fnam", "bof_add1", "bof_add2", "bof_add3",
"bof_pcod", "bof_home", "bof_work", "bof_cell", "bof_mail"
]
],
"LON": [
"Loan Ledger", "lonmf1", "lm1_cono",
[
"lm1_name", "lm1_addr1", "lm1_addr2", "lm1_addr3",
"lm1_pcode", "lm1_telno", "lm1_faxno", "lm1_celno",
"lm1_email"
]
],
"OWN": [
"Property Owners", "rcaowm", "rom_cono",
[
"rom_name", "rom_add1", "rom_add2", "rom_add3", "rom_pcod",
"rom_home", "rom_office", "rom_mobile", "rom_fax",
"rom_email"
]
],
"TNT": [
"Property Tenants", "rcatnm", "rtn_cono",
[
"rtn_name", "rtn_addr1", "rtn_addr2", "rtn_addr3",
"rtn_pcode", "rtn_telno", "rtn_email"
]
],
"RTL": [
"Rental Tenants", "rtlmst", "rtm_cono",
[
"rtm_name", "rtm_addr1", "rtm_addr2", "rtm_addr3",
"rtm_pcode", "rtm_telno", "rtm_email"
]
],
"SCP": [
"Sectional Competitions", "scpmem", "scm_cono",
["scm_surname", "scm_names", "scm_email", "scm_phone"]
],
"EMP": [
"Employees", "wagmst", "wgm_cono",
[
"wgm_sname", "wgm_fname", "wgm_addr1", "wgm_addr2",
"wgm_addr3", "wgm_pcode", "wgm_telno", "wgm_emadd"
]
]
}
for grp in tabs:
des = tabs[grp][0]
tab = tabs[grp][1]
coy = tabs[grp][2]
col = tabs[grp][3]
sql = Sql(self.opts["mf"].dbm, ["telmst", tab])
if sql.error:
continue
rec = sql.getRec(tab, cols=col)
for r in rec:
snam = None
data = ["", "", "", "", "", "", "", "", "", grp]
for n, c in enumerate(col):
x = c.split("_")[1]
if r[n]:
if x == "name":
data[0] = r[n]
elif x in ("snam", "surname", "sname"):
snam = r[n]
elif snam and x in ("fnam", "names", "fname"):
data[0] = "%s, %s" % (snam, r[n].split()[0])
snam = None
elif x in ("add1", "addr1"):
data[1] = r[n]
elif x in ("add2", "addr2"):
data[2] = r[n]
elif x in ("add3", "addr3"):
data[3] = r[n]
elif x in ("pcod", "pcode"):
data[4] = r[n]
elif x in ("home", "work", "office", "phone"):
data[5] = r[n]
elif x in ("faxno", "fax"):
data[6] = r[n]
elif x in ("cell", "celno", "mobile"):
data[7] = r[n]
elif x in ("mail", "email", "emadd"):
data[8] = r[n]
if not data[5] and not data[6] and not data[7] and not data[8]:
continue
chk = sql.getRec("telmst",
where=[("tdm_name", "=", data[0])],
limit=1)
if not chk:
sql.insRec("telmst", data=data)
elif data != chk[:len(data)]:
tdm = self.sql.telmst_col
data.append(chk[tdm.index("tdm_xflag")])
sql.updRec("telmst",
data=data,
where=[("tdm_name", "=", data[0])])
grps.append([grp, des])
# Groups
for g in grps:
chk = self.sql.getRec("telgrp",
where=[("tdg_group", "=", g[0])],
limit=1)
if not chk:
self.sql.insRec("telgrp", data=g)
spl.closeSplash()
if commit:
self.opts["mf"].dbm.commitDbase(ask=True)
self.df.setWidget(self.df.mstFrame, state="show")
self.df.window.update_idletasks()
self.df.focusField("T", 0, 1)
def doEnd(self):
self.df.closeProcess()
col = [
"mlm_memno", "mlm_surname", "mlm_names", "mlm_payplan", "mlc_code"
]
whr = [("mlm_cono", "=", self.opts["conum"]), ("mlm_state", "=", "A")]
if self.plan == "N":
whr.append(("mlm_payplan", "=", "N"))
whr.extend([("mlc_cono=mlm_cono", ), ("mlc_memno=mlm_memno", ),
("mlc_type", "=", "B")])
grp = "mlm_memno, mlm_surname, mlm_names, mlm_payplan, mlc_code"
if self.sort == "N":
odr = "mlm_memno"
else:
odr = "mlm_surname"
sp = SplashScreen(self.opts["mf"].body,
"Preparing Report ... Please Wait")
recs = self.sql.getRec(tables=["memmst", "memcat"],
cols=col,
where=whr,
group=grp,
order=odr)
sp.closeSplash()
if not recs:
self.opts["mf"].closeLoop()
return
p = ProgressBar(self.opts["mf"].body,
typ="Generating the Report",
mxs=len(recs),
esc=True)
data = []
for num, rec in enumerate(recs):
p.displayProgress(num)
if p.quit:
break
whr = [("mlt_cono", "=", self.opts["conum"]),
("mlt_memno", "=", rec[0])]
trn = getTrn(self.opts["mf"].dbm, "mem", whr=whr, zer="N")
bal = 0
inv = 0
for t in trn[1]:
c = trn[0]
bal = float(ASD(bal) + ASD(t[c.index("mlt_tramt")]))
if t[c.index("mlt_type")] == 1 and \
t[c.index("mlt_curdt")] >= self.s_per:
inv = float(ASD(inv) + ASD(t[c.index("mlt_tramt")]))
if not bal or bal < inv:
continue
cat = self.sql.getRec(tables=["memctc", "memctp"],
cols=["mcc_desc", "mcp_rate_01"],
where=[("mcc_cono", "=", self.opts["conum"]),
("mcc_type", "=", "B"),
("mcc_code", "=", rec[4]),
("mcp_cono=mcc_cono", ),
("mcp_type=mcc_type", ),
("mcp_code=mcc_code", )],
order="mcp_date desc",
limit=1)
if not cat or bal < cat[1]:
continue
rec[1] = "%s, %s" % (rec[1], rec[2])
del rec[2]
rec[3] = cat[0]
rec.append(cat[1])
rec.append(inv)
rec.append(bal)
data.append(rec)
p.closeProgress()
if p.quit:
self.opts["mf"].closeLoop()
return
name = self.__class__.__name__
head = ["Members Suspension Report"]
cols = [["a", "NA", 7, "Mem-No", "y"], ["b", "NA", 50, "Name", "y"],
["d", "UA", 1, "P", "y"],
["c", "NA", 30, "Active-Category", "y"],
["e", "SD", 13.2, "Subscription", "y"],
["f", "SD", 13.2, "Tot-Invoiced", "y"],
["g", "SD", 13.2, "Tot-Balance", "y"]]
rep = RepPrt(self.opts["mf"],
conum=self.opts["conum"],
conam=self.opts["conam"],
name=name,
tables=data,
heads=head,
cols=cols,
gtots=("e", "f", "g"),
ttype="D",
repprt=self.df.repprt,
repeml=self.df.repeml,
fromad=self.fromad)
if not rep.xits:
ask = askChoice(self.opts["mf"].body,
"Suspend Members",
"Select Members to Suspend",
butt=(("All", "A"), ("Some", "S"), ("None", "N")),
default="None")
if ask in ("A", "S"):
if ask == "S":
cols = ["memno", "name"]
dics = {
"memno": ("", 0, "UI", 6, "Member Number", "Mem-No"),
"name": ("", 1, "NA", 50, "Member Name", "Name")
}
newd = []
for dat in data:
newd.append((dat[0], dat[1]))
data = getSingleRecords(self.opts["mf"],
"",
cols,
where=newd,
ttype="D",
dic=dics)
if data:
p = ProgressBar(self.opts["mf"].body,
typ="Suspending Members",
mxs=len(data))
for num, rec in enumerate(data):
p.displayProgress(num)
self.sql.updRec("memmst",
cols=["mlm_state", "mlm_sdate"],
data=["S", self.sysdtw],
where=[("mlm_cono", "=",
self.opts["conum"]),
("mlm_memno", "=", rec[0])])
self.sql.insRec("memsta",
data=[
self.opts["conum"], rec[0], "S",
self.sysdtw, self.opts["capnm"],
self.sysdtw, 0
])
self.opts["mf"].dbm.commitDbase()
p.closeProgress()
self.opts["mf"].closeLoop()
def doCleanDatabase(self):
txt = "Cleaning Database ... Please Wait"
if self.opts["mf"] and self.opts["mf"].window:
self.opts["mf"].updateStatus(txt)
elif self.opts["bar"]:
print(txt)
# Removing unused tables
self.doGetTables()
if self.opts["bar"] and self.opts["mf"] and self.opts["mf"].window:
pb = ProgressBar(self.opts["mf"].body,
mxs=(len(self.old) + len(self.tabs)),
typ=txt)
for n1, t in enumerate(self.old):
if self.opts["mf"] and self.opts["mf"].window:
self.opts["mf"].updateStatus(t)
if self.opts["bar"]:
pb.displayProgress(n1)
if t not in self.tabs:
self.dbm.dropTable(t, True)
self.dbm.commitDbase()
# Creating new tables and/or indexes
if self.opts["mf"] and self.opts["mf"].window:
self.opts["mf"].updateStatus("")
for n2, t in enumerate(self.tabs):
if self.opts["mf"] and self.opts["mf"].window:
self.opts["mf"].updateStatus(t)
if self.opts["bar"]:
pb.displayProgress(n1 + n2)
if not self.dbm.checkTable(t):
opts = copyList(self.topts)
opts.append(("-t", t))
DBCreate(dbm=self.dbm, opts=opts)
self.tabs.append(t)
else:
self.doCreateIndexes(t)
self.dbm.commitDbase()
if self.opts["mf"] and self.opts["mf"].window:
self.opts["mf"].updateStatus("")
if self.opts["bar"]:
pb.closeProgress()
# Fix ctlmst and Stores Markup
tabs = ["ctlmst", "strctl", "strgrp", "strgmu", "strcmu"]
sql = Sql(self.dbm, tabs)
coys = sql.getRec("ctlmst", cols=["ctm_cono"])
for coy in coys:
mods = sql.getRec("ctlmst",
cols=["ctm_modules"],
where=[("ctm_cono", "=", coy[0])],
limit=1)
newm = ""
for x in range(0, len(mods[0]), 2):
if mods[0][x:x + 2] == "PS":
continue
newm += mods[0][x:x + 2]
sql.updRec("ctlmst",
cols=["ctm_modules"],
data=[newm],
where=[("ctm_cono", "=", coy[0])])
sctl = sql.getRec("strctl",
cols=["cts_plevs", "cts_automu"],
where=[("cts_cono", "=", coy[0])],
limit=1)
if sctl:
if sctl[1] == "N":
sql.delRec("strgmu", where=[("smg_cono", "=", coy[0])])
sql.delRec("strcmu", where=[("smc_cono", "=", coy[0])])
else:
if sctl[1] == "Y":
sql.updRec("strctl",
cols=["cts_automu"],
data=["L"],
where=[("cts_cono", "=", coy[0])])
sctl[1] = "L"
grps = sql.getRec("strgrp",
cols=["gpm_group"],
where=[("gpm_cono", "=", coy[0])])
for grp in grps:
for lvl in range(1, sctl[0] + 1):
sql.delRec("strcmu",
where=[("smc_cono", "=", coy[0]),
("smc_group", "=", grp[0]),
("smc_level", "=", lvl),
("smc_markup", "=", 0)])
gmu = sql.getRec("strgmu",
cols=["smg_markup"],
where=[("smg_cono", "=", coy[0]),
("smg_group", "=", grp[0]),
("smg_level", "=", lvl)],
limit=1)
if gmu:
if not gmu[0]:
sql.delRec("strgmu",
where=[
("smg_cono", "=", coy[0]),
("smg_group", "=", grp[0]),
("smg_level", "=", lvl)
])
sql.delRec("strcmu",
where=[("smc_cono", "=", coy[0]),
("smc_group", "=", grp[0]),
("smc_level", "=", lvl),
("smc_markup", "=", gmu[0])])
# Standard Records
if self.opts["mf"] and self.opts["mf"].window:
spl = SplashScreen(self.opts["mf"].body,
"Creating Standard Records\n\nPlease Wait")
self.opts["mf"].updateStatus("Creating Standard Records")
elif self.opts["bar"]:
print("Creating Standard Records .... Please Wait")
sql = Sql(self.dbm, ["ffield", "frelat"],
error=False,
prog=self.__class__.__name__)
if not sql.error:
sql.sqlRec("Delete from frelat")
self.doPopulate(sql, "frelat", "_cono")
sql = Sql(self.dbm,
"wagrcv",
error=False,
prog=self.__class__.__name__)
if not sql.error:
sql.sqlRec("Delete from wagrcv")
self.doPopulate(sql, "wagrcv")
sql = Sql(self.dbm, ["tplmst", "tpldet"],
error=False,
prog=self.__class__.__name__)
if not sql.error:
for tpl in stdtpl:
sql.sqlRec("Delete from tplmst where tpm_tname = '%s'" % tpl)
sql.sqlRec("Delete from tpldet where tpd_tname = '%s'" % tpl)
for dat in datdic["tplmst"]:
sql.insRec("tplmst", data=dat)
for dat in datdic["tpldet"]:
sql.insRec("tpldet", data=dat)
sql = Sql(self.dbm, ["wagtxa", "wagtxr"],
error=False,
prog=self.__class__.__name__)
if not sql.error:
try:
from tartanWork import payeRates, siteRates, uifRates, sdlRates
for t in ("wagtxa", "wagtxr"):
if t == "wagtxa":
for year in payeRates:
sql.delRec(t, where=[("wta_year", "=", int(year))])
data = [int(year)]
for n, d in enumerate(payeRates[year]["allow"]):
data.append(d)
if n == 1 and \
len(payeRates[year]["allow"]) == 3:
data.append(d)
data.append(siteRates[year])
data.extend(uifRates[year])
data.extend(sdlRates[year])
sql.insRec("wagtxa", data=data)
elif t == "wagtxr":
for year in payeRates:
sql.delRec(t, where=[("wtr_year", "=", int(year))])
to = 9999999
for rate in payeRates[year]["rates"]:
dat = [int(year)]
if rate[0]:
dat.append(rate[0] + 1)
dat.append(to)
dat.extend(rate[1:])
to = rate[0]
else:
dat.append(0)
dat.append(to)
dat.extend(rate[1:])
sql.insRec("wagtxr", data=dat)
except:
pass
# Close Splash
if self.opts["mf"] and self.opts["mf"].window:
self.opts["mf"].updateStatus("")
spl.closeSplash()
self.dbm.commitDbase()