def createReport(self):
report_data = []
remaining = 0
report_header = [_("Code") , _("Ledger name"), _("Debt"), _("Credit"), _("Remaining")]
col_width = [25, 45, 40, 40 , 40]
query = config.db.session.query(Subject).select_from(Subject)
result = query.order_by(Subject.code).all()
query1 = config.db.session.query(sum(Notebook.value))
# Check the report parameters
if self.builder.get_object("allcontent1").get_active() == True:
query1 = query1.select_from(outerjoin(Subject, Notebook, Subject.id == Notebook.subject_id))
else:
query1 = query1.select_from(outerjoin(outerjoin(Notebook, Subject, Notebook.subject_id == Subject.id),
Bill, Notebook.bill_id == Bill.id))
if self.builder.get_object("atdate1").get_active() == True:
date = self.date.getDateObject()
query1 = query1.filter(Bill.date == date)
else:
if self.builder.get_object("betweendates1").get_active() == True:
fdate = self.fdate.getDateObject()
tdate = self.tdate.getDateObject()
if tdate < fdate:
msgbox = Gtk.MessageDialog(self.window, Gtk.DialogFlags.MODAL, Gtk.MessageType.ERROR, Gtk.ButtonsType.OK,
_("Second date value shouldn't precede the first one."))
msgbox.set_title(_("Invalid date order"))
msgbox.run()
msgbox.destroy()
return
query1 = query1.filter(Bill.date.between(fdate, tdate))
for s in result:
res = query1.filter(and_(Subject.lft >= s.lft, Subject.lft <= s.rgt, Notebook.value < 0)).first()
if res[0] == None:
debt_sum = 0
else:
debt_sum = res[0]
res = query1.filter(and_(Subject.lft >= s.lft, Subject.lft <= s.rgt, Notebook.value > 0)).first()
if res[0] == None:
credit_sum = 0
else:
credit_sum = res[0]
if self.builder.get_object("chbZero").get_active()==False:
if credit_sum == 0 and debt_sum == 0:
continue
remain = credit_sum + debt_sum
if remain < 0:
remain = "( " + utility.LN(-remain) + " )"
else:
remain = utility.LN(remain)
report_data.append((s.code, s.name, utility.LN(-debt_sum), utility.LN(credit_sum), remain))
return {"data":report_data, "col-width":col_width ,"heading":report_header}
def createReport(self):
report_data = []
remaining = 0
report_header = [_("Ledger name"), _("Debt"), _("Credit"), _("Remaining")]
col_width = [31, 23, 23, 23]
query = config.db.session.query(Subject).select_from(Subject)
result = query.filter(Subject.parent_id == 0).all()
query1 = config.db.session.query(sum(Notebook.value))
# Check the report parameters
if self.builder.get_object("allcontent1").get_active() == True:
query1 = query1.select_from(outerjoin(Subject, Notebook, Subject.id == Notebook.subject_id))
else:
query1 = query1.select_from(outerjoin(outerjoin(Notebook, Subject, Notebook.subject_id == Subject.id),
Bill, Notebook.bill_id == Bill.id))
if self.builder.get_object("atdate1").get_active() == True:
date = self.date.getDateObject()
query1 = query1.filter(Bill.date == date)
else:
if self.builder.get_object("betweendates1").get_active() == True:
fdate = self.fdate.getDateObject()
tdate = self.tdate.getDateObject()
if tdate < fdate:
msgbox = gtk.MessageDialog(self.window, gtk.DIALOG_MODAL, gtk.MESSAGE_ERROR, gtk.BUTTONS_OK,
_("Second date value shouldn't precede the first one."))
msgbox.set_title(_("Invalid date order"))
msgbox.run()
msgbox.destroy()
return
query1 = query1.filter(Bill.date.between(fdate, tdate))
for s in result:
res = query1.filter(and_(Subject.lft >= s.lft, Subject.lft <= s.rgt, Notebook.value < 0)).first()
if res[0] == None:
debt_sum = 0
else:
debt_sum = res[0]
res = query1.filter(and_(Subject.lft >= s.lft, Subject.lft <= s.rgt, Notebook.value > 0)).first()
if res[0] == None:
credit_sum = 0
else:
credit_sum = res[0]
remain = credit_sum + debt_sum
if remain < 0:
remain = "( " + utility.showNumber(-remain) + " )"
else:
remain = utility.showNumber(remain)
report_data.append((s.name, utility.showNumber(-debt_sum), utility.showNumber(credit_sum), remain))
return {"data":report_data, "col-width":col_width ,"heading":report_header}
def editProductGroup(self, sender):
dialog = self.builder.get_object("addProductGroupDlg")
dialog.set_title(_("Edit group"))
selection = self.treeview.get_selection()
iter = selection.get_selected()[1]
if iter != None:
grpcode = unicode(self.treestore.get(iter, 0)[0])
#if config.digittype == 1:
#code = utility.convertToLatin(grpcode)
#else:
#code = grpcode
BuySub = aliased(Subject, name="bs")
SellSub = aliased(Subject, name="ss")
query = config.db.session.query(ProductGroups, BuySub.code,
SellSub.code)
query = query.select_from(
outerjoin(
outerjoin(ProductGroups, BuySub,
ProductGroups.buyId == BuySub.id), SellSub,
ProductGroups.sellId == SellSub.id))
(group, buy_code,
sell_code) = query.filter(ProductGroups.code == grpcode).first()
name = group.name
if config.digittype == 1:
buy_code = utility.convertToPersian(buy_code)
sell_code = utility.convertToPersian(sell_code)
self.builder.get_object("groupCodeEntry").set_text(grpcode)
self.builder.get_object("groupNameEntry").set_text(name)
self.buyCodeEntry.set_text(buy_code)
self.sellCodeEntry.set_text(sell_code)
success = False
while not success:
result = dialog.run()
if result == 1:
grpcode = self.builder.get_object(
"groupCodeEntry").get_text()
grpname = self.builder.get_object(
"groupNameEntry").get_text()
grpbuycode = self.buyCodeEntry.get_text()
grpsellcode = self.sellCodeEntry.get_text()
success = self.saveProductGroup(unicode(grpcode),
unicode(grpname),
grpbuycode, grpsellcode,
iter)
else:
break
dialog.hide()
def populateChildren(self, treeview, iter, path):
chiter = self.treestore.iter_children(iter)
if chiter != None:
#Checks name field(second) because code field(first) may have changed during parent code edition.
value = utility.convertToLatin(self.treestore.get(chiter, 1)[0])
if value == "":
value = utility.convertToLatin(self.treestore.get(iter, 0)[0])
#remove empty subledger to add real children instead
self.treestore.remove(chiter)
Sub = aliased(Subject, name="s")
Child = aliased(Subject, name="c")
Parent = aliased(Subject, name="p")
query = config.db.session.query(Sub.code, Sub.name, Sub.type,
count(Child.id), Sub.lft,
Sub.rgt)
query = query.select_from(
outerjoin(
outerjoin(Parent, Sub, Sub.parent_id == Parent.id),
Child, Sub.id == Child.parent_id))
result = query.filter(Parent.code == value).group_by(
Sub.id).all()
for row in result:
code = row[0]
if config.digittype == 1:
code = utility.convertToPersian(code)
type = _(self.__class__.subjecttypes[row[2]])
#--------
subject_sum = config.db.session.query(sum(
Notebook.value)).select_from(
outerjoin(Subject, Notebook,
Subject.id == Notebook.subject_id))
subject_sum = subject_sum.filter(
and_(Subject.lft >= row[4],
Subject.lft <= row.rgt)).first()
subject_sum = subject_sum[0]
if (subject_sum == None):
subject_sum = utility.showNumber("0")
else:
if (subject_sum < 0):
subject_sum = "(-" + utility.showNumber(
-subject_sum) + ")"
else:
subject_sum = utility.showNumber(subject_sum)
chiter = self.treestore.append(
iter, (code, row[1], type, subject_sum))
if row[3] != 0:
#add empty subledger for those children which have subledgers in turn. (to show expander)
self.treestore.append(chiter, ("", "", "", ""))
return False
def populateChildren(self, treeview, iter, path):
chiter = self.treestore.iter_children(iter)
if chiter != None:
# Checks name field(second) because code field(first) may have changed during parent code edition.
value = self.treestore.get(chiter, 1)[0]
if value == "":
value = convertToLatin(self.treestore.get(iter, 0)[0])
# remove empty subledger to add real children instead
self.treestore.remove(chiter)
parent_id = share.config.db.session.query(
Subject.id).filter(Subject.code == value).first().id
Sub = aliased(Subject, name="s")
Child = aliased(Subject, name="c")
query = share.config.db.session.query(Sub.code,
Sub.name, Sub.type,
count(Child.id), Sub.lft,
Sub.rgt, Sub.permanent)
query = query.select_from(
outerjoin(Sub, Child, Sub.id == Child.parent_id))
result = query.filter(Sub.parent_id == parent_id).group_by(
Sub.id).all()
for row in result:
code = row[0]
code = LN(code, False)
type = _(self.subjecttypes[row[2]])
# --------
subject_sum = share.config.db.session.query(
sum(Notebook.value)).select_from(
outerjoin(Subject, Notebook,
Subject.id == Notebook.subject_id))
subject_sum = subject_sum.filter(
and_(Subject.lft >= row[4],
Subject.lft <= row[5])).first()
subject_sum = subject_sum[0]
if subject_sum == None:
subject_sum = LN("0")
else:
if subject_sum < 0:
subject_sum = "(-" + LN(-subject_sum) + ")"
else:
subject_sum = LN(subject_sum)
per = _("Permanent") if row[6] else "-"
chiter = self.treestore.append(
iter, (code, row[1], type, subject_sum, per))
if row[3] != 0:
# add empty subledger for those children which have subledgers in turn. (to show expander)
self.treestore.append(chiter, ("", "", "", "", ""))
return False
def deleteProductGroup(self, sender):
selection = self.treeview.get_selection()
iter = selection.get_selected()[1]
if iter != None:
#code = utility.convertToLatin(self.treestore.get(iter, 0)[0])
code = unicode(self.treestore.get(iter, 0)[0])
query = config.db.session.query(ProductGroups, count(Products.id))
query = query.select_from(
outerjoin(ProductGroups, Products,
ProductGroups.id == Products.accGroup))
result = query.filter(ProductGroups.code == code).first()
if result[1] != 0:
msgbox = gtk.MessageDialog(
self.window, gtk.DIALOG_MODAL, gtk.MESSAGE_ERROR,
gtk.BUTTONS_CLOSE,
_("Group can not be deleted, Because there are some products registered in it."
))
msgbox.set_title(_("Error deleting group"))
msgbox.run()
msgbox.destroy()
else:
group = result[0]
config.db.session.delete(group)
config.db.session.commit()
self.treestore.remove(iter)
def highlightProduct(self, code, group):
iter = self.treestore.get_iter_first()
if code != "":
query = share.config.db.session.query(ProductGroups)
query = query.select_from(
outerjoin(ProductGroups, Products,
ProductGroups.id == Products.accGroup))
group = query.filter(Products.code == code).filter(
Products.accGroup == group).first()
if group:
tcode = group.code
# First check parents to find related group
pre = iter
while iter:
itercode = self.treestore.get_value(iter, 0)
if itercode < tcode:
pre = iter
iter = self.treestore.iter_next(iter)
elif itercode == tcode:
pre = iter
tcode = code
# Now check children(products) to find product row
iter = self.treestore.iter_children(iter)
else:
#iter = pre
break
iter = pre
path = self.treestore.get_path(iter)
self.treeview.expand_to_path(path)
self.treeview.scroll_to_cell(path, None, False, 0, 0)
self.treeview.set_cursor(path, None, False)
self.treeview.grab_focus()
def deleteLedger(self, sender):
selection = self.treeview.get_selection()
iter = selection.get_selected()[1]
if iter != None:
Subject1 = aliased(Subject, name="s1")
Subject2 = aliased(Subject, name="s2")
code = convertToLatin(self.treestore.get(iter, 0)[0])
#Check to see if there is any subledger for this ledger.
query = config.db.session.query(Subject1.id, count(Subject2.id))
query = query.select_from(
outerjoin(Subject1, Subject2,
Subject1.id == Subject2.parent_id))
result = query.filter(Subject1.code == code).first()
if result[1] != 0:
msgbox = gtk.MessageDialog(
self.window, gtk.DIALOG_MODAL, gtk.MESSAGE_ERROR,
gtk.BUTTONS_CLOSE,
_("Subject can not be deleted, because it has some child subjects."
))
msgbox.set_title(_("Error deleting subject"))
msgbox.run()
msgbox.destroy()
else:
# check to see if there is any document registered for this ledger.
query = config.db.session.query(count(Notebook.id))
query = query.filter(Notebook.subject_id == result[0])
rowcount = query.first()[0]
if rowcount != 0:
msgbox = gtk.MessageDialog(
self.window, gtk.DIALOG_MODAL, gtk.MESSAGE_ERROR,
gtk.BUTTONS_CLOSE,
_("Subject can not be deleted, because there are some documents registered for it."
))
msgbox.set_title(_("Error deleting subject"))
msgbox.run()
msgbox.destroy()
else:
# Now it's OK to delete ledger
row = config.db.session.query(Subject).filter(
Subject.id == result[0]).first()
sub_left = row.lft
config.db.session.delete(row)
rlist = config.db.session.query(Subject).filter(
Subject.rgt > sub_left).all()
for r in rlist:
r.rgt -= 2
config.db.session.add(r)
llist = config.db.session.query(Subject).filter(
Subject.lft > sub_left).all()
for l in llist:
l.lft -= 2
config.db.session.add(l)
config.db.session.commit()
self.treestore.remove(iter)
def highlightProduct(self, code):
iter = self.treestore.get_iter_first()
if code != "":
code = code.decode('utf-8')
query = config.db.session.query(ProductGroups)
query = query.select_from(outerjoin(ProductGroups, Products, ProductGroups.id == Products.accGroup))
group = query.filter(Products.code == code).first()
if group:
tcode = group.code
#First check parents to find related group
pre = iter
while iter:
itercode = self.treestore.get_value(iter, 0).decode('utf-8')
if itercode < tcode:
pre = iter
iter = self.treestore.iter_next(iter)
elif itercode == tcode:
pre = iter
tcode = code
#Now check children(products) to find product row
iter = self.treestore.iter_children(iter)
else:
#iter = pre
break
iter = pre
path = self.treestore.get_path(iter)
self.treeview.expand_to_path(path)
self.treeview.scroll_to_cell(path, None, False, 0, 0)
self.treeview.set_cursor(path, None, False)
self.treeview.grab_focus()
def deleteCustomerGroup(self, sender):
selection = self.treeview.get_selection()
iter = selection.get_selected()[1]
if iter != None:
code = utility.convertToLatin(self.treestore.get(iter, 0)[0])
query = share.config.db.session.query(CustGroups,
count(Customers.custId))
query = query.select_from(
outerjoin(CustGroups, Customers,
CustGroups.custGrpId == Customers.custGroup))
result = query.filter(CustGroups.custGrpCode == code).first()
if result[1] != 0:
msgbox = Gtk.MessageDialog(
self.window, Gtk.DialogFlags.MODAL, Gtk.MessageType.ERROR,
Gtk.ButtonsType.CLOSE,
_("Group can not be deleted, Because there are some customers registered in it."
))
msgbox.set_title(_("Error deleting group"))
msgbox.run()
msgbox.destroy()
else:
group = result[0]
share.config.db.session.delete(group)
share.config.db.session.commit()
self.treestore.remove(iter)
def createReport(self):
self.docnumber = self.number.get_text()
if self.docnumber == "":
return
report_header = []
report_data = []
col_width = []
query1 = config.db.session.query(Bill, Notebook, Subject)
query1 = query1.select_from(outerjoin(outerjoin(Notebook, Subject, Notebook.subject_id == Subject.id),
Bill, Notebook.bill_id == Bill.id))
query1 = query1.filter(Bill.number == int(unicode(self.docnumber))).order_by(Notebook.id.asc())
res = query1.all()
if len(res) == 0:
msgbox = gtk.MessageDialog(self.window, gtk.DIALOG_MODAL, gtk.MESSAGE_ERROR, gtk.BUTTONS_OK,
_("No document found with the requested number."))
msgbox.set_title(_("Invalid document number"))
msgbox.run()
msgbox.destroy()
return
self.docdate = res[0][0].date
report_header = [_("Index"), _("Subject Code"), _("Subject Name"), _("Description"), _("Debt"), _("Credit")]
#define the percentage of table width that each column needs
col_width = [5, 11, 15, 43, 13, 13 ]
index = 1
for b, n, s in res:
desc = n.desc
if n.value < 0:
credit = utility.showNumber(0)
debt = utility.showNumber(-(n.value))
else:
credit = utility.showNumber(n.value)
debt = utility.showNumber(0)
desc = " " + desc
code = s.code
strindex = str(index)
if config.digittype == 1:
code = utility.convertToPersian(code)
strindex = utility.convertToPersian(strindex)
report_data.append((strindex, code, s.name, desc, debt, credit))
index += 1
return {"data":report_data, "col-width":col_width ,"heading":report_header}
def editProductGroup(self, sender):
dialog = self.builder.get_object("addProductGroupDlg")
dialog.set_title(_("Edit group"))
selection = self.treeview.get_selection()
iter = selection.get_selected()[1]
if iter != None :
grpcode = unicode(self.treestore.get(iter, 0)[0])
#if config.digittype == 1:
#code = utility.convertToLatin(grpcode)
#else:
#code = grpcode
BuySub = aliased(Subject, name="bs")
SellSub = aliased(Subject, name="ss")
query = config.db.session.query(ProductGroups, BuySub.code, SellSub.code)
query = query.select_from( outerjoin( outerjoin(ProductGroups, BuySub, ProductGroups.buyId == BuySub.id),
SellSub, ProductGroups.sellId == SellSub.id ) )
(group, buy_code, sell_code) = query.filter(ProductGroups.code == grpcode).first()
name = group.name
if config.digittype == 1:
buy_code = utility.convertToPersian(buy_code)
sell_code = utility.convertToPersian(sell_code)
self.builder.get_object("groupCodeEntry").set_text(grpcode)
self.builder.get_object("groupNameEntry").set_text(name)
self.buyCodeEntry.set_text(buy_code)
self.sellCodeEntry.set_text(sell_code)
success = False
while not success :
result = dialog.run()
if result == 1:
grpcode = self.builder.get_object("groupCodeEntry").get_text()
grpname = self.builder.get_object("groupNameEntry").get_text()
grpbuycode = self.buyCodeEntry.get_text()
grpsellcode = self.sellCodeEntry.get_text()
success = self.saveProductGroup(unicode(grpcode), unicode(grpname), grpbuycode, grpsellcode, iter)
else:
break
dialog.hide()
def viewCustomers(self, readonly=False):
self.window = self.builder.get_object("viewCustomersWindow")
if readonly :
self.costmenu = self.builder.get_object("customersToolbar")
self.costmenu.hide()
self.treeview = self.builder.get_object("customersTreeView")
self.treestore = gtk.TreeStore(str, str, str, str)
self.treestore.clear()
self.treeview.set_model(self.treestore)
column = gtk.TreeViewColumn(_("Code"), gtk.CellRendererText(), text = 0)
column.set_spacing(5)
column.set_resizable(True)
column.set_sort_column_id(0)
column.set_sort_indicator(True)
self.treeview.append_column(column)
column = gtk.TreeViewColumn(_("Name"), gtk.CellRendererText(), text = 1)
column.set_spacing(5)
column.set_resizable(True)
column.set_sort_column_id(1)
column.set_sort_indicator(True)
self.treeview.append_column(column)
column = gtk.TreeViewColumn(_("Balance"), gtk.CellRendererText(), text = 2)
column.set_spacing(5)
column.set_resizable(True)
self.treeview.append_column(column)
column = gtk.TreeViewColumn(_("Credit"), gtk.CellRendererText(), text = 3)
column.set_spacing(5)
column.set_resizable(True)
self.treeview.append_column(column)
self.treeview.get_selection().set_mode(gtk.SELECTION_SINGLE)
#self.treestore.set_sort_func(0, self.sortGroupIds)
self.treestore.set_sort_column_id(0, gtk.SORT_ASCENDING)
#Fill groups treeview
query = config.db.session.query(CustGroups, Customers)
query = query.select_from(outerjoin(CustGroups, Customers, CustGroups.custGrpId == Customers.custGroup))
query = query.order_by(CustGroups.custGrpId.asc())
result = query.all()
last_gid = 0
grouprow = None
for g, c in result:
if g.custGrpId != last_gid:
grouprow = self.treestore.append(None, (g.custGrpCode, g.custGrpName, "", ""))
last_gid = g.custGrpId
if c != None:
self.treestore.append(grouprow, (c.custCode, c.custName, str(c.custBalance), str(c.custCredit)))
self.window.show_all()
def populateChildren(self, treeview, iter, path):
chiter = self.treestore.iter_children(iter)
if chiter != None :
#Checks name field(second) because code field(first) may have changed during parent code edition.
value = utility.convertToLatin(self.treestore.get(chiter, 1)[0])
if value == "" :
value = utility.convertToLatin(self.treestore.get(iter, 0)[0])
#remove empty subledger to add real children instead
self.treestore.remove(chiter)
Sub = aliased(Subject, name="s")
Child = aliased(Subject, name="c")
Parent = aliased(Subject, name="p")
query = config.db.session.query(Sub.code, Sub.name, Sub.type, count(Child.id), Sub.lft, Sub.rgt)
query = query.select_from(outerjoin(outerjoin(Parent, Sub, Sub.parent_id == Parent.id), Child, Sub.id == Child.parent_id))
result = query.filter(Parent.code == value).group_by(Sub.id).all()
for row in result :
code = row[0]
if config.digittype == 1:
code = utility.convertToPersian(code)
type = _(self.__class__.subjecttypes[row[2]])
#--------
subject_sum = config.db.session.query(sum(Notebook.value)).select_from(outerjoin(Subject, Notebook, Subject.id == Notebook.subject_id))
subject_sum = subject_sum.filter(and_(Subject.lft >= row[4], Subject.lft <= row.rgt)).first()
subject_sum = subject_sum[0]
if(subject_sum == None):
subject_sum = utility.showNumber("0")
else :
if(subject_sum < 0):
subject_sum = "(-" + utility.showNumber(-subject_sum) + ")"
else :
subject_sum = utility.showNumber(subject_sum)
chiter = self.treestore.append(iter, (code, row[1], type, subject_sum))
if row[3] != 0 :
#add empty subledger for those children which have subledgers in turn. (to show expander)
self.treestore.append(chiter, ("", "", "", ""))
return False
def deleteLedger(self, sender):
selection = self.treeview.get_selection()
iter = selection.get_selected()[1]
if iter != None :
Subject1 = aliased(Subject, name="s1")
Subject2 = aliased(Subject, name="s2")
code = utility.convertToLatin(self.treestore.get(iter, 0)[0])
#Check to see if there is any subledger for this ledger.
query = config.db.session.query(Subject1.id, count(Subject2.id))
query = query.select_from(outerjoin(Subject1, Subject2, Subject1.id == Subject2.parent_id))
result = query.filter(Subject1.code == code).first()
if result[1] != 0 :
msgbox = gtk.MessageDialog(self.window, gtk.DIALOG_MODAL, gtk.MESSAGE_ERROR, gtk.BUTTONS_CLOSE,
_("Subject can not be deleted, because it has some child subjects."))
msgbox.set_title(_("Error deleting subject"))
msgbox.run()
msgbox.destroy()
else :
# check to see if there is any document registered for this ledger.
query = config.db.session.query(count(Notebook.id))
query = query.filter(Notebook.subject_id == result[0])
rowcount = query.first()[0]
if rowcount != 0 :
msgbox = gtk.MessageDialog(self.window, gtk.DIALOG_MODAL, gtk.MESSAGE_ERROR, gtk.BUTTONS_CLOSE,
_("Subject can not be deleted, because there are some documents registered for it."))
msgbox.set_title(_("Error deleting subject"))
msgbox.run()
msgbox.destroy()
else :
# Now it's OK to delete ledger
row = config.db.session.query(Subject).filter(Subject.id == result[0]).first()
sub_left = row.lft
config.db.session.delete(row)
rlist = config.db.session.query(Subject).filter(Subject.rgt > sub_left).all()
for r in rlist:
r.rgt -= 2
config.db.session.add(r)
llist = config.db.session.query(Subject).filter(Subject.lft > sub_left).all()
for l in llist:
l.lft -= 2
config.db.session.add(l)
config.db.session.commit()
self.treestore.remove(iter)
def fillTreeview(self): self.treestore.clear(); query = config.db.session.query(ProductGroups, Products) query = query.select_from(outerjoin(ProductGroups, Products, ProductGroups.id == Products.accGroup)) query = query.order_by(ProductGroups.id.asc()) result = query.all() last_gid = 0 grouprow = None for g, p in result: if g.id != last_gid: grouprow = self.treestore.append(None, (g.code, g.name, "", "", "")) last_gid = g.id if p != None: self.treestore.append(grouprow, (p.code, p.name, utility.LN(p.quantity), utility.LN(p.purchacePrice), utility.LN(p.sellingPrice)))
def showRows(self, docnumber):
query = config.db.session.query(Bill).select_from(Bill)
bill = query.filter(Bill.number == docnumber).first()
self.date.showDateObject(bill.date)
self.docid = bill.id
query = config.db.session.query(Notebook, Subject)
query = query.select_from(
outerjoin(Notebook, Subject, Notebook.subject_id == Subject.id))
rows = query.filter(Notebook.bill_id == bill.id).all()
for n, s in rows:
self.numrows += 1
if n.value < 0:
value = -(n.value)
debt = utility.showNumber(value)
credit = utility.showNumber(0)
self.debt_sum += value
else:
credit = utility.showNumber(n.value)
debt = utility.showNumber(0)
self.credit_sum += n.value
code = s.code
numrows = str(self.numrows)
if config.digittype == 1:
code = utility.convertToPersian(code)
numrows = utility.convertToPersian(numrows)
self.liststore.append(
(numrows, code, s.name, debt, credit, n.desc))
docnum = str(docnumber)
if config.digittype == 1:
docnum = utility.convertToPersian(docnum)
self.builder.get_object("docnumber").set_text(docnum)
self.builder.get_object("debtsum").set_text(
utility.showNumber(self.debt_sum))
self.builder.get_object("creditsum").set_text(
utility.showNumber(self.credit_sum))
if self.debt_sum > self.credit_sum:
diff = self.debt_sum - self.credit_sum
else:
diff = self.credit_sum - self.debt_sum
self.builder.get_object("difference").set_text(
utility.showNumber(diff))
def showRows(self, docnumber):
query = config.db.session.query(Bill).select_from(Bill)
bill = query.filter(Bill.number == docnumber).first()
self.date.showDateObject(bill.date)
self.docid = bill.id
query = config.db.session.query(Notebook, Subject)
query = query.select_from(outerjoin(Notebook, Subject, Notebook.subject_id == Subject.id))
rows = query.filter(Notebook.bill_id == bill.id).all()
for n, s in rows:
self.numrows += 1
if n.value < 0:
value = -(n.value)
debt = utility.showNumber(value)
credit = utility.showNumber(0)
self.debt_sum += value
else:
credit = utility.showNumber(n.value)
debt = utility.showNumber(0)
self.credit_sum += n.value
code = s.code
numrows = str(self.numrows)
if config.digittype == 1:
code = utility.convertToPersian(code)
numrows = utility.convertToPersian(numrows)
self.liststore.append((numrows, code, s.name, debt, credit, n.desc))
docnum = str(docnumber)
if config.digittype == 1:
docnum = utility.convertToPersian(docnum)
self.builder.get_object("docnumber").set_text (docnum)
self.builder.get_object("debtsum").set_text (utility.showNumber(self.debt_sum))
self.builder.get_object("creditsum").set_text (utility.showNumber(self.credit_sum))
if self.debt_sum > self.credit_sum:
diff = self.debt_sum - self.credit_sum
else:
diff = self.credit_sum - self.debt_sum
self.builder.get_object("difference").set_text (utility.showNumber(diff))
def deleteProductGroup(self, sender):
selection = self.treeview.get_selection()
iter = selection.get_selected()[1]
if iter != None :
#code = utility.convertToLatin(self.treestore.get(iter, 0)[0])
code = unicode(self.treestore.get(iter, 0)[0])
query = config.db.session.query(ProductGroups, count(Products.id))
query = query.select_from(outerjoin(ProductGroups, Products, ProductGroups.id == Products.accGroup))
result = query.filter(ProductGroups.code == code).first()
if result[1] != 0 :
msgbox = gtk.MessageDialog(self.window, gtk.DIALOG_MODAL, gtk.MESSAGE_ERROR, gtk.BUTTONS_CLOSE,
_("Group can not be deleted, Because there are some products registered in it."))
msgbox.set_title(_("Error deleting group"))
msgbox.run()
msgbox.destroy()
else :
group = result[0]
config.db.session.delete(group)
config.db.session.commit()
self.treestore.remove(iter)
def editProductsAndGrps(self, sender):
selection = self.treeview.get_selection()
iter = selection.get_selected()[1]
if iter != None:
if self.treestore.iter_parent(iter) == None:
# iter points to a product group
self.editProductGroup(sender)
else:
# iter points to a product
dialog = self.builder.get_object("addProductDlg")
dialog.set_title(_("Edit Product"))
self.builder.get_object("qnttyLbl").set_text(_("Quantity:"))
code = self.treestore.get_value(iter, 0)
id = self.treestore.get_value(iter, 6)
query = share.config.db.session.query(Products,
ProductGroups.code)
query = query.select_from(
outerjoin(ProductGroups, Products,
ProductGroups.id == Products.accGroup))
result = query.filter(Products.id == id).first()
product = result[0]
groupcode = result[1]
quantity = str(product.quantity)
quantity_warn = str(product.qntyWarning)
p_price = str(product.purchacePrice)
s_price = str(product.sellingPrice)
if share.config.digittype == 1:
quantity = utility.convertToPersian(quantity)
quantity_warn = utility.convertToPersian(quantity_warn)
p_price = utility.convertToPersian(p_price)
s_price = utility.convertToPersian(s_price)
self.builder.get_object("proCodeEntry").set_text(product.code)
self.builder.get_object("accGrpEntry").set_text(groupcode)
self.builder.get_object("proNameEntry").set_text(product.name)
self.builder.get_object("proLocEntry").set_text(
product.location)
self.builder.get_object("proDescEntry").set_text(
product.productDesc)
self.builder.get_object("discFormulaEntry").set_text(
product.discountFormula)
self.qntyEntry.set_sensitive(False)
self.qntyEntry.set_text(quantity)
self.qntyWrnEntry.set_text(quantity_warn)
self.purchPriceEntry.set_text(p_price)
self.sellPriceEntry.set_text(s_price)
self.builder.get_object("oversell").set_active(
product.oversell)
self.builder.get_object("uMeasureEntry").set_text(
product.uMeasurement)
success = False
while not success:
result = dialog.run()
if result == 1:
code = self.builder.get_object(
"proCodeEntry").get_text()
accgrp = self.builder.get_object(
"accGrpEntry").get_text()
name = self.builder.get_object(
"proNameEntry").get_text()
location = self.builder.get_object(
"proLocEntry").get_text()
desc = self.builder.get_object(
"proDescEntry").get_text()
formula = self.builder.get_object(
"discFormulaEntry").get_text()
quantity = self.qntyEntry.get_float()
q_warn = self.qntyWrnEntry.get_float()
p_price = self.purchPriceEntry.get_float()
s_price = self.sellPriceEntry.get_float()
oversell = self.builder.get_object(
"oversell").get_active()
measurement = self.builder.get_object(
"uMeasureEntry").get_text()
success = self.saveProduct(code, accgrp, name,
location, desc, quantity,
q_warn, p_price, s_price,
oversell, formula,
measurement, id, iter)
else:
break
dialog.hide()
def viewProducts(self):
self.window = self.builder.get_object("viewProductsWindow")
self.treeview = self.builder.get_object("productsTreeView")
self.treestore = gtk.TreeStore(str, str, str, str, str)
self.treestore.clear()
self.treeview.set_model(self.treestore)
column = gtk.TreeViewColumn(_("Code"),
gtk.CellRendererText(),
text = 0)
column.set_spacing(5)
column.set_resizable(True)
column.set_sort_column_id(0)
column.set_sort_indicator(True)
self.treeview.append_column(column)
column = gtk.TreeViewColumn(_("Name"),
gtk.CellRendererText(),
text = 1)
column.set_spacing(5)
column.set_resizable(True)
column.set_sort_column_id(1)
column.set_sort_indicator(True)
self.treeview.append_column(column)
column = gtk.TreeViewColumn(_("Quantity"),
gtk.CellRendererText(),
text = 2)
column.set_spacing(5)
column.set_resizable(True)
column.set_sort_column_id(2)
column.set_sort_indicator(True)
self.treeview.append_column(column)
column = gtk.TreeViewColumn(_("Purchase Price"),
gtk.CellRendererText(),
text = 3)
column.set_spacing(5)
column.set_resizable(True)
self.treeview.append_column(column)
column = gtk.TreeViewColumn(_("Selling Price"),
gtk.CellRendererText(),
text = 4)
column.set_spacing(5)
column.set_resizable(True)
self.treeview.append_column(column)
self.treeview.get_selection().set_mode(gtk.SELECTION_SINGLE)
self.treestore.set_sort_column_id(0, gtk.SORT_ASCENDING)
#Fill groups treeview
query = config.db.session.query(ProductGroups, Products)
query = query.select_from(outerjoin(ProductGroups, Products, ProductGroups.id == Products.accGroup))
query = query.order_by(ProductGroups.id.asc())
result = query.all()
last_gid = 0
grouprow = None
for g, p in result:
if g.id != last_gid:
grouprow = self.treestore.append(None, (g.code, g.name, "", "", ""))
last_gid = g.id
if p != None:
self.treestore.append(grouprow, (p.code, p.name, utility.LN(p.quantity),
utility.LN(p.purchacePrice), utility.LN(p.sellingPrice)))
self.window.show_all()
def _create_eager_join(self, context, entity, path, adapter, parentmapper):
# check for join_depth or basic recursion,
# if the current path was not explicitly stated as
# a desired "loaderstrategy" (i.e. via query.options())
if ("loaderstrategy", path) not in context.attributes:
if self.join_depth:
if len(path) / 2 > self.join_depth:
return
else:
if self.mapper.base_mapper in path:
return
if parentmapper is None:
localparent = entity.mapper
else:
localparent = parentmapper
# whether or not the Query will wrap the selectable in a subquery,
# and then attach eager load joins to that (i.e., in the case of LIMIT/OFFSET etc.)
should_nest_selectable = context.query._should_nest_selectable
if entity in context.eager_joins:
entity_key, default_towrap = entity, entity.selectable
elif should_nest_selectable or not context.from_clause or not sql_util.search(context.from_clause, entity.selectable):
# if no from_clause, or a from_clause we can't join to, or a subquery is going to be generated,
# store eager joins per _MappedEntity; Query._compile_context will
# add them as separate selectables to the select(), or splice them together
# after the subquery is generated
entity_key, default_towrap = entity, entity.selectable
else:
# otherwise, create a single eager join from the from clause.
# Query._compile_context will adapt as needed and append to the
# FROM clause of the select().
entity_key, default_towrap = None, context.from_clause
towrap = context.eager_joins.setdefault(entity_key, default_towrap)
# create AliasedClauses object to build up the eager query. this is cached after 1st creation.
# this also allows ORMJoin to cache the aliased joins it produces since we pass the same
# args each time in the typical case.
path_key = util.WeakCompositeKey(*path)
try:
clauses = self.clauses[path_key]
except KeyError:
self.clauses[path_key] = clauses = mapperutil.ORMAdapter(mapperutil.AliasedClass(self.mapper),
equivalents=self.mapper._equivalent_columns)
if adapter:
if getattr(adapter, 'aliased_class', None):
onclause = getattr(adapter.aliased_class, self.key, self.parent_property)
else:
onclause = getattr(mapperutil.AliasedClass(self.parent, adapter.selectable), self.key, self.parent_property)
else:
onclause = self.parent_property
context.eager_joins[entity_key] = eagerjoin = mapperutil.outerjoin(towrap, clauses.aliased_class, onclause)
# send a hint to the Query as to where it may "splice" this join
eagerjoin.stop_on = entity.selectable
if not self.parent_property.secondary and context.query._should_nest_selectable and not parentmapper:
# for parentclause that is the non-eager end of the join,
# ensure all the parent cols in the primaryjoin are actually in the
# columns clause (i.e. are not deferred), so that aliasing applied by the Query propagates
# those columns outward. This has the effect of "undefering" those columns.
for col in sql_util.find_columns(self.parent_property.primaryjoin):
if localparent.mapped_table.c.contains_column(col):
if adapter:
col = adapter.columns[col]
context.primary_columns.append(col)
if self.parent_property.order_by:
context.eager_order_by += eagerjoin._target_adapter.copy_and_process(util.to_list(self.parent_property.order_by))
return clauses
def get_spendable_cheques(self):
li = config.db.session.query(Cheque, BankAccounts).select_from(
outerjoin(Cheque, BankAccounts,
Cheque.chqAccount == BankAccounts.accId)).filter(
Cheque.chqStatus == 4).all()
return li
def viewCustomers(self, readonly=False):
self.window = self.builder.get_object("viewCustomersWindow")
if readonly:
self.costmenu = self.builder.get_object("customersToolbar")
self.costmenu.hide()
self.treeview = self.builder.get_object("customersTreeView")
self.treestore = Gtk.TreeStore(str, str, str, str, str)
self.treestore.clear()
self.treeview.set_model(self.treestore)
column = Gtk.TreeViewColumn(_("Code"), Gtk.CellRendererText(), text=0)
column.set_spacing(5)
column.set_resizable(True)
column.set_sort_column_id(0)
column.set_sort_indicator(True)
self.treeview.append_column(column)
column = Gtk.TreeViewColumn(_("Name"), Gtk.CellRendererText(), text=1)
column.set_spacing(5)
column.set_resizable(True)
column.set_sort_column_id(1)
column.set_sort_indicator(True)
self.treeview.append_column(column)
column = Gtk.TreeViewColumn(_("Debt"), Gtk.CellRendererText(), text=2)
column.set_spacing(5)
column.set_resizable(True)
self.treeview.append_column(column)
column = Gtk.TreeViewColumn(_("Credit"),
Gtk.CellRendererText(),
text=3)
column.set_spacing(5)
column.set_resizable(True)
self.treeview.append_column(column)
column = Gtk.TreeViewColumn(_("Balance"),
Gtk.CellRendererText(),
text=4)
column.set_spacing(5)
column.set_resizable(True)
self.treeview.append_column(column)
self.treeview.get_selection().set_mode(Gtk.SelectionMode.SINGLE)
#self.treestore.set_sort_func(0, self.sortGroupIds)
self.treestore.set_sort_column_id(0, Gtk.SortType.ASCENDING)
# Fill groups treeview
query = config.db.session.query(CustGroups, Customers)
query = query.select_from(
outerjoin(CustGroups, Customers,
CustGroups.custGrpId == Customers.custGroup))
query = query.order_by(CustGroups.custGrpId.asc())
result = query.all()
# Fill groups treeview
query = config.db.session.query(Notebook)
notes = query.all()
creditNote = {}
debtNote = {}
for n in notes:
if n.value > 0:
try:
creditNote[n.subject_id] += n.value
except KeyError:
creditNote[n.subject_id] = n.value
else:
try:
debtNote[n.subject_id] += n.value
except KeyError:
debtNote[n.subject_id] = n.value
last_gid = 0
grouprow = None
for g, c in result:
if g.custGrpId != last_gid:
grouprow = self.treestore.append(
None, (utility.readNumber(g.custGrpCode),
utility.readNumber(g.custGrpName), "", "", ""))
last_gid = g.custGrpId
if c != None:
try:
credit = creditNote[c.custSubj]
except KeyError:
credit = 0
try:
debt = abs(debtNote[c.custSubj])
except KeyError:
debt = 0
balance = credit - debt
if balance < 0:
showBalance = "(" + utility.LN(abs(balance)) + ")"
else:
showBalance = utility.LN(balance)
self.treestore.append(
grouprow,
(utility.readNumber(c.custCode), unicode(c.custName),
utility.LN(debt), utility.LN(credit), showBalance))
self.window.show_all()
def showResult(self, chequeId=None, chqSerial=None, amountFrom=None, amountTo=None, dateFrom=None, dateTo=None, wDateTo=None, wDateFrom=None):
self.treestoreIncoming.clear()
self.treestoreOutgoing.clear()
self.treestoreNotPassed.clear()
self.treestoreNotCashed.clear()
self.treestoreDeleted.clear()
self.treestorePassed.clear()
self.treestoreCashed.clear()
self.treestoreSpent.clear()
self.treestoreBounced.clear()
self.treestoreBouncedP.clear()
self.treestoreReturnedT.clear()
self.treestoreReturnedF.clear()
result = share.config.db.session.query(Cheque, Customers.custName).select_from(
outerjoin(Cheque, Customers, Customers.custId == Cheque.chqCust))
# Apply filters
delimiter = share.config.datedelims[share.config.datedelim]
if chequeId:
result = result.filter(Cheque.chqId == chequeId)
if chqSerial:
result = result.filter(Cheque.chqSerial.like(chqSerial+"%"))
if amountFrom:
result = result.filter(Cheque.chqAmount >= amountFrom)
if amountTo:
result = result.filter(Cheque.chqAmount <= amountTo)
if dateTo:
dateTo = dateentry.stringToDate(dateTo)
if dateFrom:
dateFrom = dateentry.stringToDate(dateFrom)
if wDateTo:
wDateTo = dateentry.stringToDate(wDateTo)
if wDateFrom:
wDateFrom = dateentry.stringToDate(wDateFrom)
if dateTo:
result = result.filter(Cheque.chqDueDate <= dateTo)
if wDateTo:
result = result.filter(Cheque.chqWrtDate <= wDateTo)
if dateFrom:
#dateFrom -= timedelta(days=1)
result = result.filter(Cheque.chqDueDate >= dateFrom)
if wDateFrom:
#wDateFrom -= timedelta(days=1)
result = result.filter(Cheque.chqWrtDate >= wDateFrom)
totalIn = totalGo = totalnotcash = totalnotpass = totalPass = totalCash = totalSpent = totalBouncedp = totalBounced = totalRetT = totalRetF = totalFloat = 0
# Show
for cheque, cust in result.all():
chqWrtDate = dateentry.dateToString(cheque.chqWrtDate)
chqDueDate = dateentry.dateToString(cheque.chqDueDate)
# if (cheque.chqStatus == 2) or (cheque.chqStatus == 4):
# clear = _('Cleared')
# else:
# clear = _('Not Cleared' )
chqBill = share.config.db.session.query(Notebook.bill_id).filter(
Notebook.chqId == cheque.chqId).first()
if chqBill:
chqBill = chqBill.bill_id
else:
chqBill = 0
self.bankaccounts_class = class_bankaccounts.BankAccountsClass()
isSpended = False
stat = cheque.chqStatus
if stat == 6:
history = share.config.db.session.query(ChequeHistory).filter(
ChequeHistory.ChequeId == cheque.chqId).order_by(ChequeHistory.Id.desc()).limit(2).all()
if len(history):
history = history[1]
isSpended = True if history.Status == 5 else False
if stat in [1, 2, 6, 8] and not isSpended:
chqAccount = self.bankaccounts_class.get_account(
cheque.chqAccount).accName
else:
chqAccount = self.bankaccounts_class.get_bank_name(
cheque.chqAccount)
addingRow = (str(cheque.chqId), str(cheque.chqAmount), str(chqWrtDate), str(chqDueDate), str(cheque.chqSerial),
str(cust), str(chqAccount), str(cheque.chqDesc), str(chqBill), str(self.chequeStatus[cheque.chqStatus]))
if cheque.chqDelete == False:
if stat in [3, 4, 7, 9]:
self.treestoreIncoming.append(None, addingRow)
totalIn += cheque.chqAmount
else:
self.treestoreOutgoing.append(None, addingRow)
totalGo += cheque.chqAmount
if stat == 1:
self.treestoreNotPassed.append(None, addingRow)
totalPass += cheque.chqAmount
elif stat == 2:
self.treestorePassed.append(None, addingRow)
totalnotpass += cheque.chqAmount
elif stat == 3:
self.treestoreNotCashed.append(None, addingRow)
totalnotcash += cheque.chqAmount
elif stat == 4:
self.treestoreCashed.append(None, addingRow)
totalCash += cheque.chqAmount
elif stat == 5:
self.treestoreSpent.append(None, addingRow)
totalSpent += cheque.chqAmount
elif stat == 6:
self.treestoreReturnedF.append(None, addingRow)
totalRetF += cheque.chqAmount
elif stat == 7:
self.treestoreReturnedT.append(None, addingRow)
totalRetT += cheque.chqAmount
elif stat == 8:
self.treestoreBouncedP.append(None, addingRow)
totalBouncedp += cheque.chqAmount
elif stat == 9:
self.treestoreBounced.append(None, addingRow)
totalBounced += cheque.chqAmount
elif stat == 10:
self.treestoreFloat.append(None, addingRow)
totalFloat += cheque.chqAmount
else:
self.treestoreDeleted. append(None, addingRow)
self.totals = (totalIn, totalGo, totalnotpass, totalnotcash, totalPass, totalCash,
totalSpent, totalBounced, totalBouncedp, totalRetT, totalRetF, totalFloat, "")
self.builder.get_object("totalLbl").set_text(str(totalIn))
self.currentTreeview = 0
def __init__(self, ledgers_only=False,
parent_id=[
0,
], multiselect=False):
GObject.GObject.__init__(self)
self.builder = get_builder("notebook")
self.window = self.builder.get_object("subjectswindow")
self.window.set_modal(True)
self.treeview = self.builder.get_object("treeview")
# if Gtk.widget_get_default_direction() == Gtk.TextDirection.RTL :
# halign = 1
# else:
# halign = 0
self.treestore = Gtk.TreeStore(str, str, str, str, str)
column = Gtk.TreeViewColumn(_("Subject Code"),
Gtk.CellRendererText(),
text=0)
# column.set_alignment(halign)
column.set_spacing(5)
column.set_resizable(True)
self.treeview.append_column(column)
column = Gtk.TreeViewColumn(_("Subject Name"),
Gtk.CellRendererText(),
text=1)
# column.set_alignment(halign)
column.set_spacing(5)
column.set_resizable(True)
self.treeview.append_column(column)
column = Gtk.TreeViewColumn(_("Debtor or Creditor"),
Gtk.CellRendererText(),
text=2)
# column.set_alignment(halign)
column.set_spacing(5)
column.set_resizable(True)
self.treeview.append_column(column)
column = Gtk.TreeViewColumn(_("Sum"), Gtk.CellRendererText(), text=3)
# column.set_alignment(halign)
column.set_spacing(5)
column.set_resizable(True)
self.treeview.append_column(column)
column = Gtk.TreeViewColumn(_("Permanent"),
Gtk.CellRendererText(),
text=4)
# column.set_alignment(halign)
column.set_spacing(5)
column.set_resizable(True)
self.treeview.append_column(column)
self.treeview.get_selection().set_mode(Gtk.SelectionMode.SINGLE)
self.code = numberentry.NumberEntry()
box = self.builder.get_object("codebox")
box.add(self.code)
self.code.show()
#config.db.session = config.db.session
Subject1 = aliased(Subject, name="s1")
Subject2 = aliased(Subject, name="s2")
# Find top level ledgers (with parent_id equal to 0)
query = share.config.db.session.query(Subject1.code, Subject1.name,
Subject1.type, Subject1.lft,
Subject1.rgt, count(Subject2.id),
Subject1.permanent)
query = query.select_from(
outerjoin(Subject1, Subject2, Subject1.id == Subject2.parent_id))
if len(parent_id) == 1:
result = query.filter(Subject1.parent_id == parent_id[0]).group_by(
Subject1.id).all()
else:
result = query.filter(Subject1.id.in_(parent_id)).group_by(
Subject1.id).all()
for a in result:
type = _(self.subjecttypes[a[2]])
permanent = _("Permanent") if a[6] == True else "-"
code = LN(a[0], False)
# --------
subject_sum = share.config.db.session.query(sum(
Notebook.value)).select_from(
outerjoin(Subject, Notebook,
Subject.id == Notebook.subject_id))
subject_sum = subject_sum.filter(
and_(Subject.lft >= a.lft, Subject.lft <= a.rgt)).first()
subject_sum = subject_sum[0]
if subject_sum == None:
subject_sum = LN("0")
else:
if subject_sum < 0:
subject_sum = "( -" + LN(-subject_sum) + " )"
else:
subject_sum = LN(subject_sum)
iter = self.treestore.append(
None, (code, a[1], type, subject_sum, permanent))
if a[5] != 0 and ledgers_only == False:
# Add empty subledger to show expander for ledgers which have chidren
self.treestore.append(iter, ("", "", "", "", ""))
if ledgers_only == True:
btn = self.builder.get_object("addsubtoolbutton")
btn.hide()
self.treeview.set_model(self.treestore)
self.treestore.set_sort_column_id(0, Gtk.SortType.ASCENDING)
self.window.show_all()
self.builder.connect_signals(self)
#self.rebuild_nested_set(0, 0)
if multiselect:
self.treeview.get_selection().set_mode(Gtk.SelectionMode.MULTIPLE)
self.builder.get_object('toolbar4').hide()
self.builder.get_object('statusbar1').hide()
else:
self.builder.get_object('hbox5').hide()
def _create_eager_join(self, context, entity, path, adapter, parentmapper):
# check for join_depth or basic recursion,
# if the current path was not explicitly stated as
# a desired "loaderstrategy" (i.e. via query.options())
if ("loaderstrategy", path) not in context.attributes:
if self.join_depth:
if len(path) / 2 > self.join_depth:
return
else:
if self.mapper.base_mapper in path:
return
if parentmapper is None:
localparent = entity.mapper
else:
localparent = parentmapper
# whether or not the Query will wrap the selectable in a subquery,
# and then attach eager load joins to that (i.e., in the case of LIMIT/OFFSET etc.)
should_nest_selectable = context.query._should_nest_selectable
if entity in context.eager_joins:
entity_key, default_towrap = entity, entity.selectable
elif should_nest_selectable or not context.from_clause:
# if no from_clause, or a subquery is going to be generated,
# store eager joins per _MappedEntity; Query._compile_context will
# add them as separate selectables to the select(), or splice them together
# after the subquery is generated
entity_key, default_towrap = entity, entity.selectable
else:
index, clause = sql_util.find_join_source(context.from_clause, entity.selectable)
if clause:
# join to an existing FROM clause on the query.
# key it to its list index in the eager_joins dict.
# Query._compile_context will adapt as needed and append to the
# FROM clause of the select().
entity_key, default_towrap = index, clause
else:
# if no from_clause to join to,
# store eager joins per _MappedEntity
entity_key, default_towrap = entity, entity.selectable
towrap = context.eager_joins.setdefault(entity_key, default_towrap)
# create AliasedClauses object to build up the eager query.
clauses = mapperutil.ORMAdapter(
mapperutil.AliasedClass(self.mapper), equivalents=self.mapper._equivalent_columns
)
join_to_left = False
if adapter:
if getattr(adapter, "aliased_class", None):
onclause = getattr(adapter.aliased_class, self.key, self.parent_property)
else:
onclause = getattr(
mapperutil.AliasedClass(self.parent, adapter.selectable), self.key, self.parent_property
)
if onclause is self.parent_property:
# TODO: this is a temporary hack to account for polymorphic eager loads where
# the eagerload is referencing via of_type().
join_to_left = True
else:
onclause = self.parent_property
context.eager_joins[entity_key] = eagerjoin = mapperutil.outerjoin(
towrap, clauses.aliased_class, onclause, join_to_left=join_to_left
)
# send a hint to the Query as to where it may "splice" this join
eagerjoin.stop_on = entity.selectable
if not self.parent_property.secondary and context.query._should_nest_selectable and not parentmapper:
# for parentclause that is the non-eager end of the join,
# ensure all the parent cols in the primaryjoin are actually in the
# columns clause (i.e. are not deferred), so that aliasing applied by the Query propagates
# those columns outward. This has the effect of "undefering" those columns.
for col in sql_util.find_columns(self.parent_property.primaryjoin):
if localparent.mapped_table.c.contains_column(col):
if adapter:
col = adapter.columns[col]
context.primary_columns.append(col)
if self.parent_property.order_by:
context.eager_order_by += eagerjoin._target_adapter.copy_and_process(
util.to_list(self.parent_property.order_by)
)
return clauses
def __init__ (self, ledgers_only=False):
gobject.GObject.__init__(self)
self.builder = get_builder("notebook")
self.window = self.builder.get_object("subjectswindow")
self.window.set_modal(True)
self.treeview = self.builder.get_object("treeview")
self.treeview.set_direction(gtk.TEXT_DIR_LTR)
if gtk.widget_get_default_direction() == gtk.TEXT_DIR_RTL :
halign = 1
else:
halign = 0
self.treestore = gtk.TreeStore(str, str, str, str)
column = gtk.TreeViewColumn(_("Subject Code"), gtk.CellRendererText(), text=0)
column.set_alignment(halign)
column.set_spacing(5)
column.set_resizable(True)
self.treeview.append_column(column)
column = gtk.TreeViewColumn(_("Subject Name"), gtk.CellRendererText(), text=1)
column.set_alignment(halign)
column.set_spacing(5)
column.set_resizable(True)
self.treeview.append_column(column)
column = gtk.TreeViewColumn(_("Debtor or Creditor"), gtk.CellRendererText(), text=2)
column.set_alignment(halign)
column.set_spacing(5)
column.set_resizable(True)
self.treeview.append_column(column)
column = gtk.TreeViewColumn(_("Sum"), gtk.CellRendererText(), text=3)
column.set_alignment(halign)
column.set_spacing(5)
column.set_resizable(True)
self.treeview.append_column(column)
self.treeview.get_selection().set_mode(gtk.SELECTION_SINGLE)
self.code = numberentry.NumberEntry()
box = self.builder.get_object("codebox")
box.add(self.code)
self.code.show()
config.db.session = config.db.session
Subject1 = aliased(Subject, name="s1")
Subject2 = aliased(Subject, name="s2")
#Find top level ledgers (with parent_id equal to 0)
query = config.db.session.query(Subject1.code, Subject1.name, Subject1.type, Subject1.lft, Subject1.rgt, count(Subject2.id))
query = query.select_from(outerjoin(Subject1, Subject2, Subject1.id == Subject2.parent_id))
result = query.filter(Subject1.parent_id == 0).group_by(Subject1.id).all()
for a in result :
type = _(self.__class__.subjecttypes[a[2]])
code = a[0]
if config.digittype == 1:
code = utility.convertToPersian(code)
#--------
subject_sum = config.db.session.query(sum(Notebook.value)).select_from(outerjoin(Subject, Notebook, Subject.id == Notebook.subject_id))
subject_sum = subject_sum.filter(and_(Subject.lft >= a.lft, Subject.lft <= a.rgt)).first()
subject_sum = subject_sum[0]
if(subject_sum == None):
subject_sum = utility.showNumber("0")
else :
if(subject_sum < 0):
subject_sum = "( -" + utility.showNumber(-subject_sum) + " )"
else :
subject_sum = utility.showNumber(subject_sum)
iter = self.treestore.append(None, (code, a[1], type, subject_sum))
if (a[5] != 0 and ledgers_only == False) :
#Add empty subledger to show expander for ledgers which have chidren
self.treestore.append(iter, ("", "", "", ""))
if ledgers_only == True:
btn = self.builder.get_object("addsubtoolbutton")
btn.hide()
self.treeview.set_model(self.treestore)
self.treestore.set_sort_column_id(0, gtk.SORT_ASCENDING)
self.window.show_all()
self.builder.connect_signals(self)
def _create_eager_join(self, context, entity, path, adapter, parentmapper):
# check for join_depth or basic recursion,
# if the current path was not explicitly stated as
# a desired "loaderstrategy" (i.e. via query.options())
if ("loaderstrategy", path) not in context.attributes:
if self.join_depth:
if len(path) / 2 > self.join_depth:
return
else:
if self.mapper.base_mapper in path:
return
if parentmapper is None:
localparent = entity.mapper
else:
localparent = parentmapper
# whether or not the Query will wrap the selectable in a subquery,
# and then attach eager load joins to that (i.e., in the case of LIMIT/OFFSET etc.)
should_nest_selectable = context.query._should_nest_selectable
if entity in context.eager_joins:
entity_key, default_towrap = entity, entity.selectable
elif should_nest_selectable or not context.from_clause or not sql_util.search(
context.from_clause, entity.selectable):
# if no from_clause, or a from_clause we can't join to, or a subquery is going to be generated,
# store eager joins per _MappedEntity; Query._compile_context will
# add them as separate selectables to the select(), or splice them together
# after the subquery is generated
entity_key, default_towrap = entity, entity.selectable
else:
# otherwise, create a single eager join from the from clause.
# Query._compile_context will adapt as needed and append to the
# FROM clause of the select().
entity_key, default_towrap = None, context.from_clause
towrap = context.eager_joins.setdefault(entity_key, default_towrap)
# create AliasedClauses object to build up the eager query.
clauses = mapperutil.ORMAdapter(
mapperutil.AliasedClass(self.mapper),
equivalents=self.mapper._equivalent_columns)
join_to_left = False
if adapter:
if getattr(adapter, 'aliased_class', None):
onclause = getattr(adapter.aliased_class, self.key,
self.parent_property)
else:
onclause = getattr(
mapperutil.AliasedClass(self.parent, adapter.selectable),
self.key, self.parent_property)
if onclause is self.parent_property:
# TODO: this is a temporary hack to account for polymorphic eager loads where
# the eagerload is referencing via of_type().
join_to_left = True
else:
onclause = self.parent_property
context.eager_joins[entity_key] = eagerjoin = mapperutil.outerjoin(
towrap, clauses.aliased_class, onclause, join_to_left=join_to_left)
# send a hint to the Query as to where it may "splice" this join
eagerjoin.stop_on = entity.selectable
if not self.parent_property.secondary and context.query._should_nest_selectable and not parentmapper:
# for parentclause that is the non-eager end of the join,
# ensure all the parent cols in the primaryjoin are actually in the
# columns clause (i.e. are not deferred), so that aliasing applied by the Query propagates
# those columns outward. This has the effect of "undefering" those columns.
for col in sql_util.find_columns(self.parent_property.primaryjoin):
if localparent.mapped_table.c.contains_column(col):
if adapter:
col = adapter.columns[col]
context.primary_columns.append(col)
if self.parent_property.order_by:
context.eager_order_by += eagerjoin._target_adapter.copy_and_process(
util.to_list(self.parent_property.order_by))
return clauses
def editProductsAndGrps(self, sender):
selection = self.treeview.get_selection()
iter = selection.get_selected()[1]
if iter != None:
if self.treestore.iter_parent(iter) == None:
#iter points to a product group
self.editProductGroup(sender)
else:
#iter points to a product
dialog = self.builder.get_object("addProductDlg")
dialog.set_title(_("Edit Product"))
code = self.treestore.get_value(iter, 0)
query = config.db.session.query(Products, ProductGroups.code)
query = query.select_from(outerjoin(ProductGroups, Products, ProductGroups.id == Products.accGroup))
result = query.filter(Products.code == code).first()
product = result[0]
groupcode = result[1]
quantity = str(product.quantity)
quantity_warn = str(product.qntyWarning)
p_price = str(product.purchacePrice)
s_price = str(product.sellingPrice)
if config.digittype == 1:
quantity = utility.convertToPersian(quantity)
quantity_warn = utility.convertToPersian(quantity_warn)
p_price = utility.convertToPersian(p_price)
s_price = utility.convertToPersian(s_price)
self.builder.get_object("proCodeEntry").set_text(product.code)
self.builder.get_object("accGrpEntry" ).set_text(groupcode)
self.builder.get_object("proNameEntry").set_text(product.name)
self.builder.get_object("proLocEntry" ).set_text(product.location)
self.builder.get_object("proDescEntry").set_text(product.productDesc)
self.builder.get_object("discFormulaEntry").set_text(product.discountFormula)
self.qntyEntry.set_text(quantity)
self.qntyWrnEntry.set_text(quantity_warn)
self.purchPriceEntry.set_text(p_price)
self.sellPriceEntry.set_text(s_price)
self.builder.get_object("oversell").set_active(product.oversell)
success = False
while not success :
result = dialog.run()
if result == 1:
code = unicode(self.builder.get_object("proCodeEntry").get_text())
accgrp = unicode(self.builder.get_object("accGrpEntry" ).get_text())
name = unicode(self.builder.get_object("proNameEntry").get_text())
location = unicode(self.builder.get_object("proLocEntry" ).get_text())
desc = unicode(self.builder.get_object("proDescEntry").get_text())
formula = unicode(self.builder.get_object("discFormulaEntry").get_text())
quantity = self.qntyEntry.get_float()
q_warn = self.qntyWrnEntry.get_float()
p_price = self.purchPriceEntry.get_float()
s_price = self.sellPriceEntry.get_float()
oversell = self.builder.get_object("oversell").get_active()
success = self.saveProduct(code, accgrp, name, location, desc, quantity, q_warn, p_price, s_price, oversell, formula, iter)
else:
break
dialog.hide()
def createReport(self):
report_header = []
report_data = []
col_width = []
remaining = 1
query1 = config.db.session.query(Notebook, Subject.code, Bill)
query1 = query1.select_from(outerjoin(outerjoin(Notebook, Subject, Notebook.subject_id == Subject.id),
Bill, Notebook.bill_id == Bill.id))
query2 = config.db.session.query(sum(Notebook.value))
query2 = query2.select_from(outerjoin(outerjoin(Notebook, Subject, Notebook.subject_id == Subject.id),
Bill, Notebook.bill_id == Bill.id))
# Check if the subject code is valid in ledger and subledger reports
if self.type != self.__class__.DAILY:
code = utility.convertToLatin(self.code.get_text())
query3 = config.db.session.query(Subject.name)
query3 = query3.select_from(Subject).filter(Subject.code == code)
names = query3.first()
if names == None:
errorstr = _("No subject is registered with the code: %s") % self.code.get_text()
msgbox = gtk.MessageDialog(self.window, gtk.DIALOG_MODAL, gtk.MESSAGE_WARNING, gtk.BUTTONS_OK, errorstr)
msgbox.set_title(_("No subjects found"))
msgbox.run()
msgbox.destroy()
return
else:
self.subname = names[0]
self.subcode = code
query1 = query1.filter(Subject.code.startswith(code))
query2 = query2.filter(Subject.code.startswith(code))
searchkey = unicode(self.builder.get_object("searchentry").get_text())
if searchkey != "":
try:
value = int(utility.convertToLatin(searchkey))
except (UnicodeEncodeError, ValueError): #search key is not a number
query1 = query1.filter(Notebook.desc.like("%"+searchkey+"%"))
else:
query1 = query1.filter(or_(Notebook.desc.like("%"+searchkey+"%"), Notebook.value == value, Notebook.value == -value))
# Check the report parameters
if self.builder.get_object("allcontent").get_active() == True:
query1 = query1.order_by(Bill.date.asc(), Bill.number.asc())
remaining = 0
else:
if self.builder.get_object("atdate").get_active() == True:
date = self.date.getDateObject()
query1 = query1.filter(Bill.date == date).order_by(Bill.number.asc())
query2 = query2.filter(Bill.date < date)
else:
if self.builder.get_object("betweendates").get_active() == True:
fdate = self.fdate.getDateObject()
tdate = self.tdate.getDateObject()
if tdate < fdate:
msgbox = gtk.MessageDialog(self.window, gtk.DIALOG_MODAL, gtk.MESSAGE_ERROR, gtk.BUTTONS_OK,
_("Second date value shouldn't precede the first one."))
msgbox.set_title(_("Invalid date order"))
msgbox.run()
msgbox.destroy()
return
query1 = query1.filter(Bill.date.between(fdate, tdate)).order_by(Bill.date.asc(), Bill.number.asc())
query2 = query2.filter(Bill.date < fdate)
else:
if unicode(self.fnum.get_text()) == '' or unicode(self.tnum.get_text()) == '':
msgbox = gtk.MessageDialog(self.window, gtk.DIALOG_MODAL, gtk.MESSAGE_ERROR, gtk.BUTTONS_OK,
_("One of document numbers are empty."))
msgbox.set_title(_("Invalid document order"))
msgbox.run()
msgbox.destroy()
return
fnumber = int(unicode(self.fnum.get_text()))
tnumber = int(unicode(self.tnum.get_text()))
if tnumber < fnumber:
msgbox = gtk.MessageDialog(self.window, gtk.DIALOG_MODAL, gtk.MESSAGE_ERROR, gtk.BUTTONS_OK,
_("Second document number shouldn't be greater than the first one."))
msgbox.set_title(_("Invalid document order"))
msgbox.run()
msgbox.destroy()
return
query1 = query1.filter(Bill.number.between(fnumber, tnumber)).order_by(Bill.date.asc(), Bill.number.asc())
query2 = query2.filter(Bill.number < fnumber)
#Prepare report data for PrintReport class
res = query1.all()
if self.type == self.__class__.DAILY:
report_header = [_("Doc. Number"), _("Date"), _("Subject Code"), _("Description"), _("Debt"), _("Credit")]
#define the percentage of table width that each column needs
col_width = [10, 10, 11, 43, 13, 13 ]
for n, code, b in res:
desc = n.desc
if n.value < 0:
credit = utility.showNumber("0")
debt = utility.showNumber(-(n.value))
else:
credit = utility.showNumber(n.value)
debt = utility.showNumber("0")
desc = " " + desc
billnumber = str(b.number)
if config.digittype == 1:
code = utility.convertToPersian(code)
billnumber = utility.convertToPersian(billnumber)
report_data.append((billnumber, dateToString(b.date), code, desc, debt, credit))
else:
diagnose = ""
if remaining != 0:
remaining = query2.first()[0]
#if self.type == self.__class__.LEDGER:
report_header = [_("Doc. Number"), _("Date"), _("Description"), _("Debt"), _("Credit"), _("Diagnosis"), _("Remaining")]
#define the percentage of table width that each column needs
col_width = [10, 10, 37, 13, 13, 4, 13]
for n, code, b in res:
if n.value < 0:
credit = utility.showNumber("0")
debt = utility.showNumber(-(n.value))
else:
credit = utility.showNumber(n.value)
debt = utility.showNumber("0")
remaining += n.value
billnumber = str(b.number)
if config.digittype == 1:
billnumber = utility.convertToPersian(billnumber)
if remaining < 0:
diagnose = _("deb")
report_data.append((billnumber, dateToString(b.date), n.desc, debt, credit, diagnose, utility.showNumber(-(remaining))))
else:
if remaining == 0:
diagnose = _("equ")
else:
diagnose = _("cre")
report_data.append((billnumber, dateToString(b.date), n.desc, debt, credit, diagnose, utility.showNumber(remaining)))
# else:
# if self.type == self.__class__.SUBLEDGER:
# report_header = [_("Doc. Number"), _("Date"), _("Description"), _("Debt"), _("Credit"), _("Diagnosis"), _("Remaining")]
# col_width = [55, 64, 174, 70, 70, 20, 70]
# for n, code, b in res:
# if n.value < 0:
# credit = "0"
# debt = utility.showNumber(-(n.value))
# else:
# credit = utility.showNumber(n.value)
# debt = "0"
#
# remaining += n.value
# if remaining < 0:
# diagnose = _("deb")
# report_data.append((str(b.number), dateToString(b.date), n.desc, debt, credit, diagnose, utility.showNumber(-(remaining))))
# else:
# if remaining == 0:
# diagnose = _("equ")
# else:
# diagnose = _("cre")
# report_data.append((str(b.number), dateToString(b.date), n.desc, debt, credit, diagnose, utility.showNumber(remaining)))
return {"data":report_data, "col-width":col_width ,"heading":report_header}
def get_spendable_cheques(self):
li = config.db.session.query(Cheque,BankAccounts).select_from(outerjoin(Cheque,BankAccounts,Cheque.chqAccount == BankAccounts.accId)).filter(Cheque.chqStatus == 4).all()
return li
def editCustAndGrps(self, sender):
selection = self.treeview.get_selection()
iter = selection.get_selected()[1]
if self.treestore.iter_parent(iter) == None:
#Iter points to a customer group
self.editCustomerGroup(sender)
else:
code = self.treestore.get_value(iter, 0)
code = readNumber(code)
query = config.db.session.query(Customers, CustGroups.custGrpCode)
query = query.select_from(outerjoin(CustGroups, Customers, CustGroups.custGrpId == Customers.custGroup))
result = query.filter(Customers.custCode == code).first()
customer = result[0]
groupcode = result[1]
custCode = LN(customer.custCode, False)
custGrp = LN(groupcode, False)
custPhone = LN(customer.custPhone, False)
custCell = LN(customer.custCell, False)
custFax = LN(customer.custFax, False)
custPostalCode = LN(customer.custPostalCode, False)
self.customerForm = self.builder.get_object("customersWindow")
self.customerForm.set_title(_("Edit Customer"))
self.builder.get_object("addCustSubmitBtn").set_label(_("Save Customer"))
self.builder.get_object("custCodeEntry").set_text(custCode)
self.builder.get_object("custNameEntry").set_text(customer.custName)
self.custgrpentry.set_text(groupcode)
self.builder.get_object("custEcnmcsCodeEntry").set_text(customer.custEcnmcsCode)
self.builder.get_object("custPrsnalCodeEntry").set_text(customer.custPersonalCode)
self.builder.get_object("custPhoneEntry").set_text(custPhone)
self.builder.get_object("custCellEntry").set_text(custCell)
self.builder.get_object("custFaxEntry").set_text(custFax)
self.builder.get_object("custWebPageEntry").set_text(customer.custWebPage)
self.builder.get_object("custEmailEntry").set_text(customer.custEmail)
self.builder.get_object("custRepViaEmailChk").set_active(customer.custRepViaEmail)
self.builder.get_object("custAddressEntry").set_text(customer.custAddress)
self.builder.get_object("callResponsibleEntry").set_text(customer.custResposible)
self.builder.get_object("custConnectorEntry").set_text(customer.custConnector)
self.builder.get_object("custDescEntry").set_text(customer.custDesc)
self.builder.get_object("custTypeBuyerChk").set_active(customer.custTypeBuyer)
#----------------------------------
self.builder.get_object("custTypeSellerChk").set_active(customer.custTypeSeller)
self.builder.get_object("custTypeMateChk").set_active(customer.custTypeMate)
self.builder.get_object("custTypeAgentChk").set_active(customer.custTypeAgent)
#self.custIntroducerEntry.set_text(customer.custIntroducer)
self.boxCommissionRateEntry.set_text(customer.custCommission)
self.boxDiscRateEntry.set_text(customer.custDiscRate)
self.builder.get_object("markedChk").set_active(customer.custMarked)
self.builder.get_object("markedReasonEntry").set_text(customer.custReason)
#----------------------------------
self.boxBalanceEntry.set_text(LN(customer.custBalance, False))
self.boxCreditEntry.set_text(LN(customer.custCredit, False))
self.builder.get_object("custAccName1Entry").set_text(customer.custAccName1)
self.builder.get_object("custAccNo1Entry").set_text(customer.custAccNo1)
self.builder.get_object("custAccBank1Entry").set_text(customer.custAccBank1)
self.builder.get_object("custAccName2Entry").set_text(customer.custAccName2)
self.builder.get_object("custAccNo2Entry").set_text(customer.custAccNo2)
self.builder.get_object("custAccBank2Entry").set_text(customer.custAccBank2)
self.builder.get_object("cusPostalCodeEntry").set_text(LN(customer.custPostalCode, False))
self.builder.get_object("markedReasonEntry").set_sensitive(self.builder.get_object("markedChk").get_active())
self.customerForm.show_all()
self.editCustomer = True
self.customerId = customer.custId
self.editIter = iter
def createReport(self):
report_header = []
report_data = []
col_width = []
remaining = 1
query1 = config.db.session.query(Notebook, Subject.code, Bill)
query1 = query1.select_from(
outerjoin(
outerjoin(Notebook, Subject,
Notebook.subject_id == Subject.id), Bill,
Notebook.bill_id == Bill.id))
query2 = config.db.session.query(sum(Notebook.value))
query2 = query2.select_from(
outerjoin(
outerjoin(Notebook, Subject,
Notebook.subject_id == Subject.id), Bill,
Notebook.bill_id == Bill.id))
# Check if the subject code is valid in ledger and subledger reports
if self.type != self.__class__.DAILY:
code = utility.convertToLatin(self.code.get_text())
query3 = config.db.session.query(Subject.name)
query3 = query3.select_from(Subject).filter(Subject.code == code)
names = query3.first()
if names == None:
errorstr = _("No subject is registered with the code: %s"
) % self.code.get_text()
msgbox = gtk.MessageDialog(self.window, gtk.DIALOG_MODAL,
gtk.MESSAGE_WARNING, gtk.BUTTONS_OK,
errorstr)
msgbox.set_title(_("No subjects found"))
msgbox.run()
msgbox.destroy()
return
else:
self.subname = names[0]
self.subcode = code
query1 = query1.filter(Subject.code.startswith(code))
query2 = query2.filter(Subject.code.startswith(code))
searchkey = unicode(self.builder.get_object("searchentry").get_text())
if searchkey != "":
try:
value = int(utility.convertToLatin(searchkey))
except (UnicodeEncodeError,
ValueError): #search key is not a number
query1 = query1.filter(
Notebook.desc.like("%" + searchkey + "%"))
else:
query1 = query1.filter(
or_(Notebook.desc.like("%" + searchkey + "%"),
Notebook.value == value, Notebook.value == -value))
# Check the report parameters
if self.builder.get_object("allcontent").get_active() == True:
query1 = query1.order_by(Bill.date.asc(), Bill.number.asc())
remaining = 0
else:
if self.builder.get_object("atdate").get_active() == True:
date = self.date.getDateObject()
query1 = query1.filter(Bill.date == date).order_by(
Bill.number.asc())
query2 = query2.filter(Bill.date < date)
else:
if self.builder.get_object(
"betweendates").get_active() == True:
fdate = self.fdate.getDateObject()
tdate = self.tdate.getDateObject()
if tdate < fdate:
msgbox = gtk.MessageDialog(
self.window, gtk.DIALOG_MODAL, gtk.MESSAGE_ERROR,
gtk.BUTTONS_OK,
_("Second date value shouldn't precede the first one."
))
msgbox.set_title(_("Invalid date order"))
msgbox.run()
msgbox.destroy()
return
query1 = query1.filter(Bill.date.between(
fdate, tdate)).order_by(Bill.date.asc(),
Bill.number.asc())
query2 = query2.filter(Bill.date < fdate)
else:
if unicode(self.fnum.get_text()) == '' or unicode(
self.tnum.get_text()) == '':
msgbox = gtk.MessageDialog(
self.window, gtk.DIALOG_MODAL, gtk.MESSAGE_ERROR,
gtk.BUTTONS_OK,
_("One of document numbers are empty."))
msgbox.set_title(_("Invalid document order"))
msgbox.run()
msgbox.destroy()
return
fnumber = int(unicode(self.fnum.get_text()))
tnumber = int(unicode(self.tnum.get_text()))
if tnumber < fnumber:
msgbox = gtk.MessageDialog(
self.window, gtk.DIALOG_MODAL, gtk.MESSAGE_ERROR,
gtk.BUTTONS_OK,
_("Second document number shouldn't be greater than the first one."
))
msgbox.set_title(_("Invalid document order"))
msgbox.run()
msgbox.destroy()
return
query1 = query1.filter(
Bill.number.between(fnumber, tnumber)).order_by(
Bill.date.asc(), Bill.number.asc())
query2 = query2.filter(Bill.number < fnumber)
#Prepare report data for PrintReport class
res = query1.all()
if self.type == self.__class__.DAILY:
report_header = [
_("Doc. Number"),
_("Date"),
_("Subject Code"),
_("Description"),
_("Debt"),
_("Credit")
]
#define the percentage of table width that each column needs
col_width = [10, 10, 11, 43, 13, 13]
for n, code, b in res:
desc = n.desc
if n.value < 0:
credit = utility.showNumber("0")
debt = utility.showNumber(-(n.value))
else:
credit = utility.showNumber(n.value)
debt = utility.showNumber("0")
desc = " " + desc
billnumber = str(b.number)
if config.digittype == 1:
code = utility.convertToPersian(code)
billnumber = utility.convertToPersian(billnumber)
report_data.append((billnumber, dateToString(b.date), code,
desc, debt, credit))
else:
diagnose = ""
if remaining != 0:
remaining = query2.first()[0]
#if self.type == self.__class__.LEDGER:
report_header = [
_("Doc. Number"),
_("Date"),
_("Description"),
_("Debt"),
_("Credit"),
_("Diagnosis"),
_("Remaining")
]
#define the percentage of table width that each column needs
col_width = [10, 10, 37, 13, 13, 4, 13]
for n, code, b in res:
if n.value < 0:
credit = utility.showNumber("0")
debt = utility.showNumber(-(n.value))
else:
credit = utility.showNumber(n.value)
debt = utility.showNumber("0")
remaining += n.value
billnumber = str(b.number)
if config.digittype == 1:
billnumber = utility.convertToPersian(billnumber)
if remaining < 0:
diagnose = _("deb")
report_data.append(
(billnumber, dateToString(b.date), n.desc, debt,
credit, diagnose, utility.showNumber(-(remaining))))
else:
if remaining == 0:
diagnose = _("equ")
else:
diagnose = _("cre")
report_data.append(
(billnumber, dateToString(b.date), n.desc, debt,
credit, diagnose, utility.showNumber(remaining)))
# else:
# if self.type == self.__class__.SUBLEDGER:
# report_header = [_("Doc. Number"), _("Date"), _("Description"), _("Debt"), _("Credit"), _("Diagnosis"), _("Remaining")]
# col_width = [55, 64, 174, 70, 70, 20, 70]
# for n, code, b in res:
# if n.value < 0:
# credit = "0"
# debt = utility.showNumber(-(n.value))
# else:
# credit = utility.showNumber(n.value)
# debt = "0"
#
# remaining += n.value
# if remaining < 0:
# diagnose = _("deb")
# report_data.append((str(b.number), dateToString(b.date), n.desc, debt, credit, diagnose, utility.showNumber(-(remaining))))
# else:
# if remaining == 0:
# diagnose = _("equ")
# else:
# diagnose = _("cre")
# report_data.append((str(b.number), dateToString(b.date), n.desc, debt, credit, diagnose, utility.showNumber(remaining)))
return {
"data": report_data,
"col-width": col_width,
"heading": report_header
}
def get_notebook_rows(self):
query = share.config.db.session.query(Notebook, Subject)
query = query.select_from(
outerjoin(Notebook, Subject, Notebook.subject_id == Subject.id))
query = query.filter(Notebook.bill_id == self.id)
return query.all()
def __init__(self, ledgers_only=False):
gobject.GObject.__init__(self)
self.builder = get_builder("notebook")
self.window = self.builder.get_object("subjectswindow")
self.window.set_modal(True)
self.treeview = self.builder.get_object("treeview")
self.treeview.set_direction(gtk.TEXT_DIR_LTR)
if gtk.widget_get_default_direction() == gtk.TEXT_DIR_RTL:
halign = 1
else:
halign = 0
self.treestore = gtk.TreeStore(str, str, str, str)
column = gtk.TreeViewColumn(_("Subject Code"),
gtk.CellRendererText(),
text=0)
column.set_alignment(halign)
column.set_spacing(5)
column.set_resizable(True)
self.treeview.append_column(column)
column = gtk.TreeViewColumn(_("Subject Name"),
gtk.CellRendererText(),
text=1)
column.set_alignment(halign)
column.set_spacing(5)
column.set_resizable(True)
self.treeview.append_column(column)
column = gtk.TreeViewColumn(_("Debtor or Creditor"),
gtk.CellRendererText(),
text=2)
column.set_alignment(halign)
column.set_spacing(5)
column.set_resizable(True)
self.treeview.append_column(column)
column = gtk.TreeViewColumn(_("Sum"), gtk.CellRendererText(), text=3)
column.set_alignment(halign)
column.set_spacing(5)
column.set_resizable(True)
self.treeview.append_column(column)
self.treeview.get_selection().set_mode(gtk.SELECTION_SINGLE)
self.code = numberentry.NumberEntry()
box = self.builder.get_object("codebox")
box.add(self.code)
self.code.show()
config.db.session = config.db.session
Subject1 = aliased(Subject, name="s1")
Subject2 = aliased(Subject, name="s2")
#Find top level ledgers (with parent_id equal to 0)
query = config.db.session.query(Subject1.code, Subject1.name,
Subject1.type, Subject1.lft,
Subject1.rgt, count(Subject2.id))
query = query.select_from(
outerjoin(Subject1, Subject2, Subject1.id == Subject2.parent_id))
result = query.filter(Subject1.parent_id == 0).group_by(
Subject1.id).all()
for a in result:
type = _(self.__class__.subjecttypes[a[2]])
code = a[0]
if config.digittype == 1:
code = utility.convertToPersian(code)
#--------
subject_sum = config.db.session.query(sum(
Notebook.value)).select_from(
outerjoin(Subject, Notebook,
Subject.id == Notebook.subject_id))
subject_sum = subject_sum.filter(
and_(Subject.lft >= a.lft, Subject.lft <= a.rgt)).first()
subject_sum = subject_sum[0]
if (subject_sum == None):
subject_sum = utility.showNumber("0")
else:
if (subject_sum < 0):
subject_sum = "( -" + utility.showNumber(
-subject_sum) + " )"
else:
subject_sum = utility.showNumber(subject_sum)
iter = self.treestore.append(None, (code, a[1], type, subject_sum))
if (a[5] != 0 and ledgers_only == False):
#Add empty subledger to show expander for ledgers which have chidren
self.treestore.append(iter, ("", "", "", ""))
if ledgers_only == True:
btn = self.builder.get_object("addsubtoolbutton")
btn.hide()
self.treeview.set_model(self.treestore)
self.treestore.set_sort_column_id(0, gtk.SORT_ASCENDING)
self.window.show_all()
self.builder.connect_signals(self)
def editCustAndGrps(self, sender):
selection = self.treeview.get_selection()
iter = selection.get_selected()[1]
if self.treestore.iter_parent(iter) == None:
# Iter points to a customer group
self.editCustomerGroup(sender)
else:
code = self.treestore.get_value(iter, 0)
code = utility.convertToLatin(code)
query = config.db.session.query(Customers, CustGroups.custGrpCode)
query = query.select_from(
outerjoin(CustGroups, Customers,
CustGroups.custGrpId == Customers.custGroup))
result = query.filter(Customers.custCode == code).first()
customer = result[0]
groupcode = result[1]
custCode = utility.LN(customer.custCode, False)
custGrp = utility.LN(groupcode, False)
custPhone = utility.LN(customer.custPhone, False)
custCell = utility.LN(customer.custCell, False)
custFax = utility.LN(customer.custFax, False)
custPostalCode = utility.LN(customer.custPostalCode, False)
self.customerForm = self.builder.get_object("customersWindow")
self.customerForm.set_title(_("Edit Customer"))
self.builder.get_object("addCustSubmitBtn").set_label(
_("Save Customer"))
self.builder.get_object("custCodeEntry").set_text(custCode)
self.builder.get_object("custNameEntry").set_text(
customer.custName)
self.custgrpentry.set_text(groupcode)
self.builder.get_object("custEcnmcsCodeEntry").set_text(
customer.custEcnmcsCode)
self.builder.get_object("custPrsnalCodeEntry").set_text(
customer.custPersonalCode)
self.builder.get_object("custPhoneEntry").set_text(custPhone)
self.builder.get_object("custCellEntry").set_text(custCell)
self.builder.get_object("custFaxEntry").set_text(custFax)
self.builder.get_object("custWebPageEntry").set_text(
customer.custWebPage)
self.builder.get_object("custEmailEntry").set_text(
customer.custEmail)
self.builder.get_object("custRepViaEmailChk").set_active(
customer.custRepViaEmail)
self.builder.get_object("custAddressEntry").set_text(
customer.custAddress)
self.builder.get_object("callResponsibleEntry").set_text(
customer.custResposible)
self.builder.get_object("custConnectorEntry").set_text(
customer.custConnector)
self.builder.get_object("custDescEntry").set_text(
customer.custDesc)
self.builder.get_object("custTypeBuyerChk").set_active(
customer.custTypeBuyer)
# ----------------------------------
self.builder.get_object("custTypeSellerChk").set_active(
customer.custTypeSeller)
self.builder.get_object("custTypeMateChk").set_active(
customer.custTypeMate)
self.builder.get_object("custTypeAgentChk").set_active(
customer.custTypeAgent)
# self.custIntroducerEntry.set_text(customer.custIntroducer)
self.boxCommissionRateEntry.set_text(customer.custCommission)
self.boxDiscRateEntry.set_text(customer.custDiscRate)
self.builder.get_object("markedChk").set_active(
customer.custMarked)
self.builder.get_object("markedReasonEntry").set_text(
customer.custReason)
# ----------------------------------aaaaaaaaaaaaaa
self.builder.get_object("custAccName1Entry").set_text(
customer.custAccName1)
self.builder.get_object("custAccNo1Entry").set_text(
customer.custAccNo1)
self.builder.get_object("custAccBank1Entry").set_text(
customer.custAccBank1)
self.builder.get_object("custAccName2Entry").set_text(
customer.custAccName2)
self.builder.get_object("custAccNo2Entry").set_text(
customer.custAccNo2)
self.builder.get_object("custAccBank2Entry").set_text(
customer.custAccBank2)
self.builder.get_object("cusPostalCodeEntry").set_text(
utility.LN(customer.custPostalCode, False))
self.builder.get_object("markedReasonEntry").set_sensitive(
self.builder.get_object("markedChk").get_active())
self.customerForm.show_all()
self.editCustomer = True
self.customerId = customer.custId
self.editIter = iter
def get_notebook_rows(self):
query = share.config.db.session.query(Notebook, Subject)
query = query.select_from(outerjoin(Notebook, Subject, Notebook.subject_id == Subject.id))
query = query.filter(Notebook.bill_id == self.id)
return query.all()
def createReport(self, printFlag=True):
number = utility.convertToLatin(unicode(self.number.get_text()))
if re.match('^\d+$', number) != None:
self.docnumbers = [int(number)]
elif re.match('^(\d+)-(\d+)$', number) != None:
m = re.match('^(\d+)-(\d+)$', number)
self.docnumbers = list(range(int(m.group(1)), int(m.group(2)) + 1))
else:
self.builder.get_object("message").set_text(
_("Please enter number in correct format \nTrue Formats: '2-11' or '2' "
))
return
self.builder.get_object("message").set_text("")
if printFlag: # preparing data for csv file
report_header = [
_("Index"),
_("Subject Code"),
_("Subject Name"),
_("Description"),
_("Debt"),
_("Credit")
]
else:
report_header = [
_("Doc."),
_("Index"),
_("Subject Code"),
_("Subject Name"),
_("Description"),
_("Debt"),
_("Credit"),
_("Date")
]
html = ""
bill_ids = []
bills = config.db.session.query(Bill).filter(
Bill.number.in_(self.docnumbers)).order_by(
Bill.number.asc()).all()
for bill in bills:
bill_ids.append(bill.id)
query = config.db.session.query(Notebook, Subject).select_from(
outerjoin(
Notebook, Subject, Notebook.subject_id == Subject.id)).filter(
Notebook.bill_id.in_(bill_ids)).order_by(Notebook.id.asc())
res = query.all()
if len(res) == 0:
msgbox = Gtk.MessageDialog(
self.window, Gtk.DialogFlags.MODAL, Gtk.MessageType.ERROR,
Gtk.ButtonsType.OK,
_("No document found with the requested number."))
msgbox.set_title(_("Invalid document number"))
msgbox.run()
msgbox.destroy()
return
if config.locale == 'en_US':
doDirection = 'left'
daDirection = 'right'
text_align = "left"
else:
report_header = report_header[::-1]
doDirection = 'right'
daDirection = 'left'
text_align = "right"
todaystr = dateToString(date.today())
billCount = 1
table_h = 8
for b in bills:
if printFlag:
report_data = [("", "", "", "", "", "")] * table_h
else:
report_data = [("", "", "", "", "", "", "", "")]
query = config.db.session.query(Notebook, Subject).select_from(
outerjoin(Notebook, Subject,
Notebook.subject_id == Subject.id)).filter(
Notebook.bill_id == b.id).order_by(
Notebook.id.asc())
res = query.all()
docdate = b.date
index = 1
debt_sum = credit_sum = 0
datestr = dateToString(docdate)
docnumber = b.number
if config.digittype == 1:
docnumber = utility.convertToPersian(docnumber)
for n, s in res:
desc = n.desc
if n.value < 0:
credit = utility.LN(0)
debt = utility.LN(-(n.value))
else:
credit = utility.LN(n.value)
debt = utility.LN(0)
desc = " " + desc
code = utility.LN(s.code)
doc_number = utility.LN(b.number)
debt_sum += utility.getFloatNumber(debt)
credit_sum += utility.getFloatNumber(credit)
if printFlag:
if index < table_h:
report_data[index - 1] = (utility.LN(index, 0), code,
s.name, desc, debt, credit)
else:
report_data.append(
(utility.LN(index,
0), code, s.name, desc, debt, credit))
else:
if index < table_h:
report_data[index - 1] = (doc_number,
utility.LN(index,
0), code, s.name,
desc, debt, credit, datestr)
else:
report_data.append(
(doc_number, utility.LN(index, 0), code, s.name,
desc, debt, credit, datestr))
index += 1
if not printFlag: # preparing data for csv file
continue
col_width = [19, 25, 45, 250, 40, 40]
for i in range(0, len(report_header)):
col_width[i] = 'style="width:' + str(col_width[i]) + 'pt" '
i = 0
html += '<p ' + self.reportObj.subjectHeaderStyle + '><b>' + _(
"Accounting Document") + '</b></p>\
<div style="text-align:' + doDirection + '; font-size:12px;float:' + doDirection + '">' + _(
"Document Number") + ': ' + str(docnumber) + '</div>\
<div style="text-align:' + daDirection + '; font-size:12px;float:' + daDirection + '">' + _(
"Date") + ': ' + todaystr + '</div> <br/>'
html += '<table class="notebooks"><thead><tr>'
if config.locale == 'en_US':
for header in report_header:
html += '<th ' + col_width[i] + '>' + header + '</th>'
i += 1
html += '</tr></thead><tbody>'
for row in report_data:
html += '<tr>'
for data in row:
html += '<td>' + str(data) + '</td>'
html += '</tr>'
else:
col_width = col_width[::-1]
for header in report_header:
html += '<th ' + col_width[i] + '>' + header + '</th>'
i += 1
html += '</tr></thead><tbody>'
for row in report_data:
row = row[::-1]
html += '<tr>'
for data in row:
html += '<td>' + str(data) + '</td>'
html += '</tr>'
row = [
'<td colspan="4" >' + unicode(_("Total") + '</td>'),
'<td>' + unicode(utility.LN(debt_sum) + '</td>'),
'<td>' + unicode(utility.LN(credit_sum)) + '</td>'
]
signaturesRow = [
unicode(_("Accounting")),
unicode(_("Financial Manager")),
unicode(_("Managing Director"))
]
if config.locale != 'en_US':
row = row[::-1]
signaturesRow = signaturesRow[::-1]
html += '<tr style="border:1px solid black;">' + \
row[0] + row[1]+row[2]+'</tr>'
html += '</tbody></table> <br/><br/>'
html += '<table class="signatures" > \
<tr style="border:0px" ><td>' + signaturesRow[
0] + '</td> <td>' + signaturesRow[
1] + '</td> <td>' + signaturesRow[2] + '</td> </tr>\
<tr></tr> \
</table>'
billCount += 1
if billCount <= len(bills):
html += '<p style="page-break-before: always" ></p>'
report_data = []
if not printFlag:
return {"data": report_data, "heading": report_header}
html = '<!DOCTYPE html> <html> <head> \
<style> @font-face {font-family: "Vazir"; src: url(data/font/Vazir.woff); }\
table {border-collapse: collapse; text-align:' + text_align + '; width:100%; } \
th {border:1px solid black; padding: 10px;font-size:10px;}\
thead {display: table-header-group;} \
.notebooks td {border-left:1px solid; border-right:1px solid; padding: 10px;font-size:10px;} \
.signatures {border:0px; font-size:14px; text-align:center}\
body {font-family: "Vazir"} \
</style> <meta charset="UTF-8"> </head> <body>' + html + '</body> </html>'
return html
def createReport(self):
number = unicode(self.number.get_text())
if re.match('^\d+$', number) != None:
self.docnumbers=[int(number)]
elif re.match('^(\d+)-(\d+)$', number) != None:
m = re.match('^(\d+)-(\d+)$', number)
self.docnumbers=range(int(m.group(1)),int(m.group(2))+1)
else:
print 'error'
return
# self.docnumber = self.number.get_text()
# if self.docnumber == "":
# return
report_header = []
report_data = []
col_width = []
debt_sum = 0
credit_sum = 0
query1 = config.db.session.query(Bill, Notebook, Subject)
query1 = query1.select_from(outerjoin(outerjoin(Notebook, Subject, Notebook.subject_id == Subject.id), Bill, Notebook.bill_id == Bill.id))
query1 = query1.filter(Bill.number.in_(self.docnumbers)).order_by(Bill.number.asc(),Notebook.id.asc())
res = query1.all()
if len(res) == 0:
msgbox = gtk.MessageDialog(self.window, gtk.DIALOG_MODAL, gtk.MESSAGE_ERROR, gtk.BUTTONS_OK,
_("No document found with the requested number."))
msgbox.set_title(_("Invalid document number"))
msgbox.run()
msgbox.destroy()
return
self.docdate = res[0][0].date
report_header = [_("Index"), _("Subject Code"), _("Subject Name"), _("Description"), _("Debt"), _("Credit")]
#define the percentage of table width that each column needs
col_width = [5, 11, 15, 43, 13, 13 ]
index = 1
doc_number0 = 0
for b, n, s in res:
desc = n.desc
if n.value < 0:
credit = utility.LN(0)
debt = utility.LN(-(n.value))
else:
credit = utility.LN(n.value)
debt = utility.LN(0)
desc = " " + desc
code = utility.LN(s.code)
doc_number = utility.LN(b.number)
if doc_number != doc_number0:
index = 1
debt_sum = 0
credit_sum = 0
debt_sum += int(debt.replace(",", ""))
credit_sum += int(credit.replace(",", ""))
strindex = utility.LN(str(index))
doc_number0 = doc_number
date = dateToString(b.date)
report_data.append((strindex, code, s.name, desc, debt, credit, doc_number, date, debt_sum, credit_sum))
index += 1
return {"data":report_data, "col-width":col_width ,"heading":report_header}
