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()