def get_addresses(self,ids,context={}):
vals={}
comp_id=access.get_active_company()
for obj in self.browse(ids):
res=get_model("address").search([["settings_id","=",obj.id],["or",["company_id","=",None],["company_id","child_of",comp_id]]])
vals[obj.id]=res
return vals
def get_report_data(self, ids, context={}):
company_id = get_active_company()
comp = get_model("company").browse(company_id)
if ids:
params = self.read(ids, load_m2o=False)[0]
else:
params = self.default_get(load_m2o=False, context=context)
settings = get_model("settings").browse(1)
if not params.get("account_id"):
return
account_id = int(params.get("account_id"))
date_from = params.get("date_from")
date_to = params.get("date_to")
if not date_from and not date_to:
date_from = date.today().strftime("%Y-%m-01")
date_to = (date.today() + relativedelta(day=31)).strftime("%Y-%m-%d")
acc = get_model("account.account").browse(account_id, context={"date_to": date_to})
acc_balance = acc.balance
acc_unrec = get_model("account.account").browse(
account_id, context={"date_to": date_to, "bank_rec_state": "not_reconciled"})
unrec_paid_amt = acc_unrec.credit
unrec_received_amt = acc_unrec.debit
rec_paid = []
condition = [["account_id", "=", account_id], ["move_id.state", "=", "posted"], ["move_id.date", ">=", date_from], [
"move_id.date", "<=", date_to], ["state", "=", "reconciled"], ["credit", ">", 0]]
for line in get_model("account.move.line").search_browse(condition, order="move_id.date,id"):
vals = {
"date": line.move_id.date,
"description": line.description,
"ref": line.move_id.number,
"amount": line.credit - line.debit,
}
rec_paid.append(vals)
rec_received = []
condition = [["account_id", "=", account_id], ["move_id.state", "=", "posted"], ["move_id.date", ">=", date_from], [
"move_id.date", "<=", date_to], ["state", "=", "reconciled"], ["debit", ">", 0]]
for line in get_model("account.move.line").search_browse(condition, order="move_id.date,id"):
vals = {
"date": line.move_id.date,
"description": line.description,
"ref": line.move_id.number,
"amount": line.debit - line.credit,
}
rec_received.append(vals)
db = database.get_connection()
data = {
"company_name": comp.name,
"date_from": date_from,
"date_to": date_to,
"account_name": acc.name,
"rec_paid": rec_paid,
"total_rec_paid": sum([l["amount"] for l in rec_paid]),
"rec_received": rec_received,
"total_rec_received": sum([l["amount"] for l in rec_received]),
"acc_balance": acc_balance,
"unrec_paid_amt": unrec_paid_amt,
"unrec_received_amt": unrec_received_amt,
"st_balance": acc_balance + unrec_paid_amt - unrec_received_amt,
}
return data
def get_rate(self, ids, date=None, rate_type="buy", context={}):
obj_id = ids[0]
dbname = database.get_active_db()
company_id = access.get_active_company()
key = (dbname, company_id, obj_id, date, rate_type)
if key in _cache and not context.get("no_cache"):
return _cache[key]
obj = self.browse(obj_id)
res = None
for rate in obj.rates:
if rate.company_id.id != company_id:
continue
if date and rate.date > date:
continue
if rate_type == "buy":
res = rate.buy_rate
break
else:
res = rate.sell_rate
break
if res is None:
for rate in obj.rates:
if date and rate.date > date:
continue
if rate_type == "buy":
res = rate.buy_rate
break
else:
res = rate.sell_rate
break
_cache[key] = res
return res
def get_report_data(self, ids, context={}):
company_id = get_active_company()
company_ids = get_model("company").search([["id", "child_of", company_id]])
comp = get_model("company").browse(company_id)
if ids:
params = self.read(ids, load_m2o=False)[0]
else:
params = self.default_get(load_m2o=False, context=context)
settings = get_model("settings").browse(1)
date_from = params.get("date_from")
if not date_from:
date_from = date.today().strftime("%Y-%m-01")
date_to = params.get("date_to")
if not date_to:
date_to = (date.today() + relativedelta(day=31)).strftime("%Y-%m-%d")
data = {
"company_name": comp.name,
"date_from": date_from,
"date_to": date_to,
"by_rate": params.get("by_rate"),
"by_comp": params.get("by_comp"),
}
db = database.get_connection()
if params.get("by_comp"):
res = db.query("SELECT c.id AS comp_id,c.name AS comp_name,c.rate AS comp_rate,r.name AS rate_name,SUM(l.credit-l.debit) AS tax_total,SUM(l.tax_base*sign(l.credit-l.debit)) AS base_total FROM account_move_line l,account_move m,account_tax_component c,account_tax_rate r WHERE m.id=l.move_id AND m.state='posted' AND m.date>=%s AND m.date<=%s AND c.id=l.tax_comp_id AND r.id=c.tax_rate_id AND m.company_id IN %s GROUP BY comp_id,comp_name,comp_rate,rate_name ORDER BY comp_name,rate_name",
date_from, date_to, tuple(company_ids))
data["comp_taxes"] = [dict(r) for r in res]
if params.get("by_rate"):
res = db.query("SELECT c.id AS comp_id,c.name AS comp_name,c.rate AS comp_rate,r.name AS rate_name,SUM(l.credit-l.debit) AS tax_total,SUM(l.tax_base*sign(l.credit-l.debit)) AS base_total FROM account_move_line l,account_move m,account_tax_component c,account_tax_rate r WHERE m.id=l.move_id AND m.state='posted' AND m.date>=%s AND m.date<=%s AND c.id=l.tax_comp_id AND r.id=c.tax_rate_id AND m.company_id IN %s GROUP BY comp_id,comp_name,comp_rate,rate_name ORDER BY rate_name,comp_name",
date_from, date_to, tuple(company_ids))
data["rate_taxes"] = [dict(r) for r in res]
return data
def get_report_data(self, ids, context={}):
if ids:
params = self.read(ids, load_m2o=False)[0]
else:
params = self.default_get(load_m2o=False, context=context)
company_id = get_active_company()
comp = get_model("company").browse(company_id)
settings = get_model("settings").browse(1)
date_from = params.get("date_from")
date_to = params.get("date_to")
if not date_from:
date_from = date.today().strftime("%Y-%m-01")
if not date_to:
date_to = (date.today() + relativedelta(day=31)).strftime("%Y-%m-%d")
data = {
"company_name": comp.name,
"date_from": date_from,
"date_to": date_to,
"total_qty": 0,
"total_amount": 0,
}
db = get_connection()
res = db.query(
"SELECT l.product_id,p.name AS product_name,p.sale_price AS product_price,SUM(l.amount) AS amount,SUM(l.qty) AS qty FROM account_invoice_line l,account_invoice i,product p WHERE i.id=l.invoice_id AND p.id=l.product_id AND i.date>=%s AND i.date<=%s GROUP BY l.product_id,p.name,p.sale_price ORDER BY p.name", date_from, date_to)
lines = []
for r in res:
line = r
line["avg_price"] = line["amount"] / line["qty"] if line["qty"] else None
lines.append(line)
data["total_qty"] += line["qty"]
data["total_amount"] += line["amount"]
data["lines"] = lines
data["total_avg_price"] = data["total_amount"] / data["total_qty"] if data["total_qty"] else None
pprint(data)
return data
def get_rate(self, ids, date=None, rate_type="buy", context={}):
obj_id = ids[0]
if not obj_id:
return
dbname = database.get_active_db()
company_id = access.get_active_company()
key = (dbname, company_id, obj_id, date, rate_type)
if key in _cache and not context.get("no_cache"):
return _cache[key]
obj = self.browse(obj_id)
res = None
for rate in obj.rates:
if rate.company_id.id != company_id:
continue
if date and rate.date > date:
continue
if rate_type == "buy":
res = rate.buy_rate
break
else:
res = rate.sell_rate
break
if res is None:
for rate in obj.rates:
if date and rate.date > date:
continue
if rate_type == "buy":
res = rate.buy_rate
break
else:
res = rate.sell_rate
break
_cache[key] = res
return res
def money_out(self, context={}):
company_id = get_active_company()
company_ids = get_model("company").search([["id", "child_of", company_id]])
db = get_connection()
res = db.query(
"SELECT COALESCE(l.due_date,l.move_date) AS due_date,SUM(l.credit-l.debit) as amount FROM account_move_line l JOIN account_account a ON a.id=l.account_id LEFT JOIN account_reconcile r ON r.id=l.reconcile_id WHERE l.move_state='posted' AND a.type='payable' AND l.reconcile_id IS NULL AND a.company_id IN %s GROUP BY COALESCE(l.due_date,l.move_date)",
tuple(company_ids),
)
amounts = {}
for r in res:
amounts[r.due_date] = r.amount
values = []
d0 = date.today()
d1 = d0 + timedelta(days=60)
d = d0
while d < d1:
ds = d.strftime("%Y-%m-%d")
values.append((js_time(d), amounts.get(ds, 0)))
d += timedelta(days=1)
data = {"value": [{"key": "Payable", "values": values}]}
res = db.get(
"SELECT count(*) AS count,SUM(amount_total_cur) AS amount FROM account_invoice WHERE type='in' AND inv_type='invoice' AND state='draft' AND company_id IN %s",
tuple(company_ids),
)
if res:
data["draft_count"] = res.count
data["draft_amount"] = res.amount
res = db.get(
"SELECT count(*) AS count,SUM(amount_total_cur) AS amount FROM account_invoice WHERE type='in' AND inv_type='invoice' AND state='waiting_payment' AND due_date<now() AND company_id IN %s",
tuple(company_ids),
)
if res:
data["overdue_count"] = res.count
data["overdue_amount"] = res.amount
return data
def get_report_data_custom(self, ids, context={}):
company_id = get_active_company()
comp = get_model("company").browse(company_id)
if ids:
params = self.read(ids, load_m2o=False)[0]
else:
params = self.default_get(load_m2o=False, context=context)
settings = get_model("settings").browse(1)
date_from = params.get("date_from")
date_to = params.get("date_to")
d0 = datetime.strptime(date_from, "%Y-%m-%d")
year_date_from = d0.strftime("%Y-01-01") # XXX: get company fiscal year
prev_date_from = (d0 - timedelta(days=1) - relativedelta(day=1)).strftime("%Y-%m-%d")
prev_date_to = (d0 - timedelta(days=1) + relativedelta(day=31)).strftime("%Y-%m-%d")
year_date_from_prev_year = (
datetime.strptime(year_date_from, "%Y-%m-%d") - relativedelta(years=1)).strftime("%Y-%m-%d")
date_from_prev_year = (datetime.strptime(date_from, "%Y-%m-%d") - relativedelta(years=1)).strftime("%Y-%m-%d")
date_to_prev_year = (datetime.strptime(date_to, "%Y-%m-%d") - relativedelta(years=1)).strftime("%Y-%m-%d")
data = {
"date_from": date_from,
"date_to": date_to,
"year_date_from": year_date_from,
"prev_date_from": prev_date_from,
"prev_date_to": prev_date_to,
"company_name": comp.name,
"year_date_from_prev_year": year_date_from_prev_year,
"date_from_prev_year": date_from_prev_year,
"date_to_prev_year": date_to_prev_year,
}
print("data", data)
return data
def get_report_data(self, ids, context={}):
company_id = get_active_company()
comp = get_model("company").browse(company_id)
if ids:
params = self.read(ids, load_m2o=False)[0]
else:
params = self.default_get(load_m2o=False, context=context)
date_from = params["date_from"]
date_to = params["date_to"]
track_id = params.get("track_id")
assets = {}
cond = [["state", "=", "registered"]]
if track_id:
cond.append(["track_id", "=", track_id])
for asset in get_model("account.fixed.asset").search_browse(cond, context={"date": date_from}):
vals = {
"asset_id": asset.id,
"asset_name": asset.name,
"asset_number": asset.number,
"type_name": asset.type_id.name,
"rate": asset.dep_rate,
"purchase_price": asset.price_purchase,
"purchase_date": asset.date_purchase,
"book_val_from": asset.book_val,
"track_id": asset.track_id.id,
}
assets[asset.id] = vals
for asset in get_model("account.fixed.asset").search_browse(cond, context={"date": date_to}):
vals = assets[asset.id]
vals["book_val_to"] = asset.book_val
vals["accum_dep"] = vals["book_val_from"] - vals["book_val_to"]
lines = sorted(assets.values(), key=lambda v: (v["type_name"], v["asset_name"]))
groups = []
cur_group = None
for line in lines:
if not cur_group or line["type_name"] != cur_group["type_name"]:
cur_group = {
"type_name": line["type_name"],
"lines": [],
}
groups.append(cur_group)
cur_group["lines"].append(line)
for group in groups:
group.update({
"total_book_val_from": sum([l["book_val_from"] for l in group["lines"]]),
"total_accum_dep": sum([l["accum_dep"] for l in group["lines"]]),
"total_book_val_to": sum([l["book_val_to"] for l in group["lines"]]),
})
pprint(groups)
data = {
"company_name": comp.name,
"date_from": date_from,
"date_to": date_to,
"groups": groups,
"total_book_val_from": sum([l["book_val_from"] for l in lines]),
"total_accum_dep": sum([l["accum_dep"] for l in lines]),
"total_book_val_to": sum([l["book_val_to"] for l in lines]),
}
return data
def top_debtors(self, context={}):
company_id = get_active_company()
company_ids = get_model("company").search([["id", "child_of", company_id]])
db = get_connection()
res = db.query(
"SELECT p.id AS contact_id,p.name AS contact_name,SUM(amount_due_cur) AS amount_due,SUM(CASE WHEN i.due_date<now()::date THEN amount_due_cur ELSE 0 END) AS amount_overdue FROM account_invoice i JOIN contact p ON p.id=i.contact_id WHERE i.type='out' AND i.inv_type='invoice' AND i.state='waiting_payment' AND i.company_id IN %s GROUP BY p.id,p.name ORDER BY amount_due DESC LIMIT 10", tuple(company_ids))
data = [dict(r) for r in res]
return data
def debtor_exposure(self, context={}):
company_id = get_active_company()
company_ids = get_model("company").search([["id", "child_of", company_id]])
db = get_connection()
res = db.query(
"SELECT p.id AS contact_id,p.name AS contact_name,SUM(amount_due_cur) AS amount_due FROM account_invoice i JOIN contact p ON p.id=i.contact_id WHERE i.type='out' AND i.inv_type='invoice' AND i.state='waiting_payment' AND i.company_id IN %s GROUP BY p.id,p.name ORDER BY amount_due DESC LIMIT 10", tuple(company_ids))
data = [(r.contact_name, r.amount_due) for r in res]
return {"value": data}
def gen_payslips(self, ids, context={}):
print("gen_payslips", ids)
obj = self.browse(ids)[0]
date = obj.date_from
emp_ids = get_model("hr.employee").search(
[["work_status", "=", "working"]])
for emp in get_model("hr.employee").browse(emp_ids):
period = 12
res = get_model("hr.payslip").search([["run_id", "=", obj.id],
["employee_id", "=",
emp.id]])
# TODO: check if payslip exists already
if res:
continue
vals = {
"employee_id": emp.id,
"date": date,
"run_id": obj.id,
'company_id': get_active_company(),
}
lines = []
ctx = {
"employee_id": emp.id,
"date": date,
"date_from": obj.date_from,
"date_to": obj.date_to,
}
for item in get_model("hr.payitem").search_browse([]): # XXX
if item.tax_type == "thai":
ctx["year_income"] = (emp.salary or 0.0) * period
qty, rate = item.compute(context=ctx)
if not item.show_default:
continue
line_vals = {
"payitem_id": item.id,
"qty": qty,
"rate": rate,
}
lines.append(line_vals)
if emp.profile_id:
lines = []
for item in emp.profile_id.pay_items:
if item.tax_type == "thai":
ctx["year_income"] = (emp.salary or 0.0) * period
qty, rate = item.compute(context=ctx)
line_vals = {
"payitem_id": item.id,
"qty": qty,
"rate": rate,
}
lines.append(line_vals)
vals["lines"] = [("create", line_vals) for line_vals in lines]
get_model("hr.payslip").create(vals)
def get_report_data(self, ids, context={}):
company_id = get_active_company()
comp = get_model("company").browse(company_id)
if ids:
params = self.read(ids, load_m2o=False)[0]
else:
params = self.default_get(load_m2o=False, context=context)
settings = get_model("settings").browse(1)
db = database.get_connection()
if not params.get("account_id"):
return
account_id = int(params.get("account_id"))
date = params.get("date")
if not date:
date = time.strftime("%Y-%m-%d")
ctx = {
"date_to": date,
}
acc = get_model("account.account").browse([account_id], context=ctx)[0]
payments = []
for obj in get_model("account.move.line").search_browse(
[["account_id", "=", account_id], ["state", "!=", "reconciled"],
["move_id.state", "=", "posted"], ["move_id.date", "<=", date]],
order="move_id.date"):
vals = {
"date": obj.move_id.date,
"description": obj.description,
"ref": obj.move_id.number,
"amount": obj.credit - obj.debit,
}
payments.append(vals)
st_lines = []
for obj in get_model("account.statement.line").search_browse(
[["statement_id.account_id", "=", account_id],
["state", "!=", "reconciled"], ["date", "<=", date]],
order="date"):
vals = {
"date": obj.date,
"description": obj.description,
"ref": "", # XXX
"amount": obj.received - obj.spent,
}
st_lines.append(vals)
data = {
"company_name": comp.name,
"date": date,
"account_name": acc.name,
"account_balance": acc.balance,
"move_lines": payments,
"total_move_lines": sum([p["amount"] for p in payments]),
"statement_lines": st_lines,
"total_statement_lines": sum([l["amount"] for l in st_lines]),
}
data["statement_balance"] = data["account_balance"] + data[
"total_move_lines"] + data["total_statement_lines"]
return data
class ProductLocation(Model):
_name = "product.location"
_string = "Product Location"
_key = ["product_id", "sequence"]
_multi_company = True
_fields = {
"product_id":
fields.Many2One("product",
"Product",
required=True,
on_delete="cascade"),
"sequence":
fields.Integer("Sequence", required=True),
"location_id":
fields.Many2One("stock.location", "Warehouse", required=True),
"bin_location":
fields.Char("Bin Location"),
"stock_qty":
fields.Decimal("Stock Qty", function="get_stock_qty"),
"company_id":
fields.Many2One("company", "Company", search=True),
}
_order = "sequence"
_defaults = {
"sequence": 1,
'company_id': lambda *a: get_active_company(),
}
def get_stock_qty(self, ids, context={}):
keys = []
for obj in self.browse(ids):
if obj.product_id.type == "bundle":
for comp in obj.product_id.components:
key = (comp.component_id.id, None, obj.location_id.id,
None)
keys.append(key)
else:
key = (obj.product_id.id, None, obj.location_id.id, None)
keys.append(key)
bals = get_model("stock.balance").compute_key_balances(
keys, context={"virt_stock": True})
vals = {}
for obj in self.browse(ids):
if obj.product_id.type == "bundle":
comps_qty = []
for comp in obj.product_id.components:
key = (comp.component_id.id, None, obj.location_id.id,
None)
qty = bals[key][0]
comps_qty.append(int(qty / comp.qty))
vals[obj.id] = min(comps_qty or [0])
else:
key = (obj.product_id.id, None, obj.location_id.id, None)
qty = bals[key][0]
vals[obj.id] = qty
return vals
def top_debtors(self, context={}):
company_id = get_active_company()
company_ids = get_model("company").search(
[["id", "child_of", company_id]])
db = get_connection()
res = db.query(
"SELECT p.id AS contact_id,p.name AS contact_name,SUM(amount_due_cur) AS amount_due,SUM(CASE WHEN i.due_date<now()::date THEN amount_due_cur ELSE 0 END) AS amount_overdue FROM account_invoice i JOIN contact p ON p.id=i.contact_id WHERE i.type='out' AND i.inv_type='invoice' AND i.state='waiting_payment' AND i.company_id IN %s GROUP BY p.id,p.name ORDER BY amount_due DESC LIMIT 10",
tuple(company_ids))
data = [dict(r) for r in res]
return data
def debtor_exposure(self, context={}):
company_id = get_active_company()
company_ids = get_model("company").search(
[["id", "child_of", company_id]])
db = get_connection()
res = db.query(
"SELECT p.id AS contact_id,p.name AS contact_name,SUM(amount_due_cur) AS amount_due FROM account_invoice i JOIN contact p ON p.id=i.contact_id WHERE i.type='out' AND i.inv_type='invoice' AND i.state='waiting_payment' AND i.company_id IN %s GROUP BY p.id,p.name ORDER BY amount_due DESC LIMIT 10",
tuple(company_ids))
data = [(r.contact_name, r.amount_due) for r in res]
return {"value": data}
def gen_payslips(self, ids, context={}):
print("gen_payslips", ids)
obj = self.browse(ids)[0]
date = obj.date_from
emp_ids = get_model("hr.employee").search([["work_status", "=", "working"]])
for emp in get_model("hr.employee").browse(emp_ids):
period = 12
res = get_model("hr.payslip").search([["run_id", "=", obj.id], ["employee_id", "=", emp.id]])
# TODO: check if payslip exists already
if res:
continue
vals = {
"employee_id": emp.id,
"date": date,
"run_id": obj.id,
'company_id': get_active_company(),
}
lines = []
ctx = {
"employee_id": emp.id,
"date": date,
"date_from": obj.date_from,
"date_to": obj.date_to,
}
for item in get_model("hr.payitem").search_browse([]): # XXX
if item.tax_type == "thai":
ctx["year_income"] = (emp.salary or 0.0) * period
qty, rate = item.compute(context=ctx)
if not item.show_default:
continue
line_vals = {
"payitem_id": item.id,
"qty": qty,
"rate": rate,
}
lines.append(line_vals)
if emp.profile_id:
lines = []
for item in emp.profile_id.pay_items:
if item.tax_type == "thai":
ctx["year_income"] = (emp.salary or 0.0) * period
qty, rate = item.compute(context=ctx)
line_vals = {
"payitem_id": item.id,
"qty": qty,
"rate": rate,
}
lines.append(line_vals)
vals["lines"] = [("create", line_vals) for line_vals in lines]
get_model("hr.payslip").create(vals)
def get_report_data(self, ids, context={}):
if ids:
params = self.read(ids, load_m2o=False)[0]
else:
params = self.default_get(load_m2o=False, context=context)
company_id = get_active_company()
comp = get_model("company").browse(company_id)
settings = get_model("settings").browse(1)
date_from = params["date_from"]
date_to = params["date_to"]
contact_id = params.get("contact_id")
account_id = params.get("account_id")
data = {
"company_name": comp.name,
"date_from": date_from,
"date_to": date_to,
}
cond = [[
"account_id.type", "in",
["cost_sales", "expense", "other_expense"]
], ["move_id.date", ">=", date_from], ["move_id.date", "<=", date_to]]
if contact_id:
cond.append(["contact_id", "=", contact_id])
if account_id:
cond.append(["account_id", "=", account_id])
groups = {}
for obj in get_model("account.move.line").search_browse(
cond, order="move_id.date"):
line_vals = {
"id": obj.id,
"date": obj.move_id.date,
"number": obj.move_id.number,
"description": obj.description,
"account_id": obj.account_id.id,
"account_code": obj.account_id.code,
"account_name": obj.account_id.name,
"amount": obj.debit - obj.credit,
}
contact_id = obj.contact_id.id
if contact_id in groups:
group = groups[contact_id]
else:
group = {
"contact_id": contact_id,
"contact_name": obj.contact_id.name,
"lines": [],
}
groups[contact_id] = group
group["lines"].append(line_vals)
data["groups"] = sorted(groups.values(),
key=lambda g: g["contact_name"] or "")
for group in data["groups"]:
group["total"] = sum([l["amount"] for l in group["lines"]])
pprint(data)
return data
def post(self, ids, context={}):
obj = self.browse(ids)[0]
if obj.total_credit != obj.total_credit:
raise Exception("Debit & Credit is not balance!")
payrun = obj.payrun_id
company_id = get_active_company()
move = payrun.move_id
if move:
move.to_draft()
for line in move.lines:
line.delete()
pst = get_model("hr.payroll.settings").browse(1)
journal = pst.journal_id
if not journal:
raise Exception("Please define journal in payroll setting.")
if not move:
move_vals = {
"journal_id": journal.id,
"number": payrun.number,
"date": payrun.date_pay,
"narration": 'Paid-%s' % (payrun.date_pay),
"related_id": "hr.payrun,%s" % payrun.id,
"company_id": company_id,
}
move_id = get_model("account.move").create(move_vals)
move = get_model("account.move").browse(move_id)
lines = []
for line in obj.lines:
lines.append(('create', {
'description': line.description or "",
'debit': line.debit or 0,
'credit': line.credit or 0,
'account_id': line.account_id.id,
}))
move.write({
'lines': lines,
})
# XXX
for payslip in payrun.payslips:
payslip.write({
'move_id': move.id,
'state': 'posted',
})
payrun.write({
'move_id': move.id,
'state': 'posted',
})
return {
'next': {
'name': 'payrun',
'mode': 'form',
'active_id': payrun.id,
},
'flash': 'payrun %s has been posted!' % payrun.number,
}
def post(self, ids, context={}):
obj = self.browse(ids)[0]
if obj.total_credit != obj.total_credit:
raise Exception("Debit & Credit is not balance!")
payrun = obj.payrun_id
company_id = get_active_company()
move = payrun.move_id
if move:
move.to_draft()
for line in move.lines:
line.delete()
pst = get_model("hr.payroll.settings").browse(1)
journal = pst.journal_id
if not journal:
raise Exception("Please define journal in payroll setting.")
if not move:
move_vals = {
"journal_id": journal.id,
"number": payrun.number,
"date": payrun.date_pay,
"narration": 'Paid-%s' % (payrun.date_pay),
"related_id": "hr.payrun,%s" % payrun.id,
"company_id": company_id,
}
move_id = get_model("account.move").create(move_vals)
move = get_model("account.move").browse(move_id)
lines = []
for line in obj.lines:
lines.append(('create', {
'description': line.description or "",
'debit': line.debit or 0,
'credit': line.credit or 0,
'account_id': line.account_id.id,
}))
move.write({
'lines': lines,
})
# XXX
for payslip in payrun.payslips:
payslip.write({
'move_id': move.id,
'state': 'posted',
})
payrun.write({
'move_id': move.id,
'state': 'posted',
})
return {
'next': {
'name': 'payrun',
'mode': 'form',
'active_id': payrun.id,
},
'flash': 'payrun %s has been posted!' % payrun.number,
}
def get_report_data(self, ids, context={}):
company_id = get_active_company()
company_ids = get_model("company").search(
[["id", "child_of", company_id]])
comp = get_model("company").browse(company_id)
if ids:
params = self.read(ids, load_m2o=False)[0]
else:
params = self.default_get(load_m2o=False, context=context)
settings = get_model("settings").browse(1)
company_name = comp.name
contact_id = params.get("contact_id")
if contact_id:
contact_id = int(contact_id)
date_from = params.get("date_from")
date_to = params.get("date_to")
show_details = params.get("show_details")
db = get_connection()
q = "SELECT l.id,m.date AS move_date,l.contact_id,p.name AS contact_name,m.number AS move_number,l.description,COALESCE(l.due_date,m.date) AS due_date,l.debit-l.credit AS total_amount,r.number AS reconcile_number,l.reconcile_id FROM account_move_line l JOIN account_account a ON a.id=l.account_id JOIN account_move m ON m.id=l.move_id LEFT JOIN contact p ON p.id=l.contact_id LEFT JOIN account_reconcile r ON r.id=l.reconcile_id WHERE l.move_state='posted' AND a.type='receivable' AND a.company_id IN %s"
args = [tuple(company_ids)]
if date_from:
q += " AND COALESCE(l.due_date,l.move_date)>=%s"
args.append(date_from)
if date_to:
q += " AND COALESCE(l.due_date,l.move_date)<=%s"
args.append(date_to)
if contact_id:
q += " AND l.contact_id=%s"
args.append(contact_id)
else:
q += " AND l.contact_id IS NULL" # XXX
if not show_details:
q += " AND (l.reconcile_id IS NULL OR r.balance!=0)"
q += " ORDER BY COALESCE(l.due_date,m.date),l.id"
res = db.query(q, *args)
lines = []
for r in res:
vals = dict(r)
if vals["reconcile_number"] and not vals[
"reconcile_number"].endswith("*"):
vals["due_amount"] = 0
else:
vals["due_amount"] = vals["total_amount"]
lines.append(vals)
data = {
"company_name": company_name,
"date_from": date_from,
"date_to": date_to,
"lines": lines,
"totals": {
"amount_total": sum(l["due_amount"] for l in lines),
}
}
return data
def get_company_currency(self, ids, context={}):
comp_cur = {}
comp_id = get_active_company()
for comp in get_model("company").search_browse([]):
set_active_company(comp.id)
comp_settings = get_model("settings").browse(1)
comp_cur[comp.id] = comp_settings.currency_id.id
set_active_company(comp_id)
vals = {}
for obj in self.browse(ids):
vals[obj.id] = comp_cur.get(obj.company_id.id)
return vals
def get_addresses(self, ids, context={}):
vals = {}
comp_id = access.get_active_company()
for obj in self.browse(ids):
res = get_model("address").search(
[["settings_id", "=", obj.id],
[
"or", ["company_id", "=", None],
["company_id", "child_of", comp_id]
]])
vals[obj.id] = res
return vals
def get_company_currency(self, ids, context={}):
comp_cur = {}
comp_id = get_active_company()
for comp in get_model("company").search_browse([]):
set_active_company(comp.id)
comp_settings = get_model("settings").browse(1)
comp_cur[comp.id] = comp_settings.currency_id.id
set_active_company(comp_id)
vals = {}
for obj in self.browse(ids):
vals[obj.id] = comp_cur.get(obj.company_id.id)
return vals
def get_report_data(self, ids, context={}):
company_id = get_active_company()
company_ids = get_model("company").search([["id", "child_of", company_id]])
comp = get_model("company").browse(company_id)
if ids:
params = self.read(ids, load_m2o=False)[0]
else:
params = self.default_get(load_m2o=False, context=context)
settings = get_model("settings").browse(1)
company_name = comp.name
contact_id = params.get("contact_id")
if contact_id:
contact_id = int(contact_id)
date_from = params.get("date_from")
date_to = params.get("date_to")
show_details = params.get("show_details")
db = get_connection()
q = "SELECT l.id,m.date AS move_date,l.contact_id,p.name AS contact_name,m.number AS move_number,l.description,COALESCE(l.due_date,m.date) AS due_date,l.credit-l.debit AS total_amount,r.number AS reconcile_number,l.reconcile_id FROM account_move_line l JOIN account_account a ON a.id=l.account_id JOIN account_move m ON m.id=l.move_id LEFT JOIN contact p ON p.id=l.contact_id LEFT JOIN account_reconcile r ON r.id=l.reconcile_id WHERE l.move_state='posted' AND a.type='payable' AND a.company_id IN %s"
args = [tuple(company_ids)]
if date_from:
q += " AND COALESCE(l.due_date,l.move_date)>=%s"
args.append(date_from)
if date_to:
q += " AND COALESCE(l.due_date,l.move_date)<=%s"
args.append(date_to)
if contact_id:
q += " AND l.contact_id=%s"
args.append(contact_id)
else:
q += " AND l.contact_id IS NULL" # XXX
if not show_details:
q += " AND (l.reconcile_id IS NULL OR r.balance!=0)"
q += " ORDER BY COALESCE(l.due_date,m.date),l.id"
res = db.query(q, *args)
lines = []
for r in res:
vals = dict(r)
if vals["reconcile_number"] and not vals["reconcile_number"].endswith("*"):
vals["due_amount"] = 0
else:
vals["due_amount"] = vals["total_amount"]
lines.append(vals)
data = {
"company_name": company_name,
"date_from": date_from,
"date_to": date_to,
"lines": lines,
"totals": {
"amount_total": sum(l["due_amount"] for l in lines),
}
}
return data
def get_report_data(self, ids, context={}):
if ids:
params = self.read(ids, load_m2o=False)[0]
else:
params = self.default_get(load_m2o=False, context=context)
company_id = get_active_company()
comp = get_model("company").browse(company_id)
settings = get_model("settings").browse(1)
date_from = params["date_from"]
date_to = params["date_to"]
contact_id = params.get("contact_id")
account_id = params.get("account_id")
data = {
"company_name": comp.name,
"date_from": date_from,
"date_to": date_to,
}
cond = [["account_id.type", "in", ["cost_sales", "expense", "other_expense"]],
["move_id.date", ">=", date_from], ["move_id.date", "<=", date_to]]
if contact_id:
cond.append(["contact_id", "=", contact_id])
if account_id:
cond.append(["account_id", "=", account_id])
groups = {}
for obj in get_model("account.move.line").search_browse(cond, order="move_id.date"):
line_vals = {
"id": obj.id,
"date": obj.move_id.date,
"number": obj.move_id.number,
"description": obj.description,
"account_id": obj.account_id.id,
"account_code": obj.account_id.code,
"account_name": obj.account_id.name,
"amount": obj.debit - obj.credit,
}
contact_id = obj.contact_id.id
if contact_id in groups:
group = groups[contact_id]
else:
group = {
"contact_id": contact_id,
"contact_name": obj.contact_id.name,
"lines": [],
}
groups[contact_id] = group
group["lines"].append(line_vals)
data["groups"] = sorted(groups.values(), key=lambda g: g["contact_name"] or "")
for group in data["groups"]:
group["total"] = sum([l["amount"] for l in group["lines"]])
pprint(data)
return data
def get_report_data(self, ids, context={}):
company_id = get_active_company()
comp = get_model("company").browse(company_id)
if ids:
params = self.read(ids, load_m2o=False)[0]
else:
params = self.default_get(load_m2o=False, context=context)
settings = get_model("settings").browse(1)
date_from = params.get("date_from")
date_to = params.get("date_to")
pay_method_id = params.get("pay_method_id")
lines = []
cond = [["state", "=", "confirmed"]]
if date_from:
cond.append(["date", ">=", date_from])
if date_to:
cond.append(["date", "<=", date_to])
if pay_method_id:
cond.append(["pay_method_id", "=", pay_method_id])
for sale in get_model("sale.order").search_browse(cond,
order="date,id"):
if sale.is_paid:
continue
contact = sale.contact_id
phone = contact.phone
for addr in contact.addresses:
if phone:
break
phone = addr.phone
line = {
"id": sale.id,
"number": sale.number,
"related_id": sale.related_id.id if sale.related_id else None,
"related_number":
sale.related_id.number if sale.related_id else None,
"date": sale.date,
"customer_name": contact.name,
"phone": phone,
"pay_method":
sale.pay_method_id.name if sale.pay_method_id else None,
"amount": sale.amount_total,
}
lines.append(line)
data = {
"company_name": comp.name,
"date_from": date_from,
"date_to": date_to,
"lines": lines,
"total": sum(l["amount"] for l in lines),
}
return data
def payable_status(self, context={}):
company_id = get_active_company()
company_ids = get_model("company").search([["id", "child_of", company_id]])
data = {}
for st in ("draft", "waiting_approval", "waiting_payment"):
data[st] = {"count": 0, "amount": 0}
db = get_connection()
res = db.query(
"SELECT state,COUNT(*) as count,SUM(amount_due_cur) as amount FROM account_invoice WHERE type='in' AND inv_type='invoice' AND company_id IN %s GROUP BY state",
tuple(company_ids),
)
for r in res:
data[r["state"]] = {"count": r["count"], "amount": r["amount"]}
return data
def gen_tax_no(self, exclude=None, context={}):
company_id = get_active_company() # XXX: improve this?
seq_id = get_model("sequence").find_sequence(type="tax_no")
if not seq_id:
return None
while 1:
num = get_model("sequence").get_next_number(seq_id, context=context)
if exclude and num in exclude:
get_model("sequence").increment_number(seq_id, context=context)
continue
res = get_model("account.move.line").search([["tax_no", "=", num], ["move_id.company_id", "=", company_id]])
if not res:
return num
get_model("sequence").increment_number(seq_id, context=context)
def gen_tax_no(self, exclude=None, context={}):
company_id = get_active_company() # XXX: improve this?
seq_id = get_model("sequence").find_sequence(type="tax_no")
if not seq_id:
return None
while 1:
num = get_model("sequence").get_next_number(seq_id, context=context)
if exclude and num in exclude:
get_model("sequence").increment_number(seq_id, context=context)
continue
res = get_model("account.move.line").search([["tax_no", "=", num], ["move_id.company_id", "=", company_id]])
if not res:
return num
get_model("sequence").increment_number(seq_id, context=context)
def get_report_data(self, ids, context={}):
company_id = get_active_company()
company_ids = get_model("company").search(
[["id", "child_of", company_id]])
comp = get_model("company").browse(company_id)
if ids:
params = self.read(ids, load_m2o=False)[0]
else:
params = self.default_get(load_m2o=False, context=context)
settings = get_model("settings").browse(1)
tax_comp_id = params.get("tax_comp_id")
trans_type = params.get("trans_type")
if tax_comp_id:
tax_comp_id = int(tax_comp_id)
tax_comp = get_model("account.tax.component").browse(tax_comp_id)
tax_comp_ids = [tax_comp_id]
tax_type = tax_comp.tax_type
else:
tax_type = params.get("tax_type")
if not tax_type or not trans_type:
return
tax_comp_ids = get_model("account.tax.component").search(
[["type", "=", tax_type], ["trans_type", "=", trans_type]])
tax_comp = None
date_from = params.get("date_from")
if not date_from:
date_from = date.today().strftime("%Y-%m-01")
date_to = params.get("date_to")
if not date_to:
date_to = (date.today() +
relativedelta(day=31)).strftime("%Y-%m-%d")
data = {
"company_name": comp.name,
"rate_name": tax_comp.tax_rate_id.name if tax_comp else None,
"comp_name": tax_comp.name if tax_comp else None,
"tax_type": tax_type,
"date_from": date_from,
"date_to": date_to,
}
db = database.get_connection()
res = db.query(
"SELECT m.id AS move_id,m.date,a.name AS account_name,a.code AS account_code,m.number,m.ref,l.description,case when 'in' = %s then l.debit-l.credit else l.credit-l.debit end AS tax_amount, case when 'in' = %s then l.tax_base*sign(l.debit-l.credit) else l.tax_base*sign(l.credit-l.debit) end AS base_amount,c.name as contact_name,c.id as contact_id FROM account_move_line l JOIN account_move m ON m.id=l.move_id JOIN account_account a ON a.id=l.account_id LEFT JOIN contact c ON c.id=l.contact_id WHERE m.state='posted' AND m.date>=%s AND m.date<=%s AND l.tax_comp_id IN %s AND m.company_id IN %s ORDER BY date",
trans_type, trans_type, date_from, date_to, tuple(tax_comp_ids),
tuple(company_ids))
data["lines"] = [dict(r) for r in res]
data["base_total"] = sum(l["base_amount"] for l in data["lines"])
data["tax_total"] = sum(l["tax_amount"] for l in data["lines"])
data["credit_lines"] = []
return data
def get_report_data(self, ids, context={}):
company_id = get_active_company()
comp = get_model("company").browse(company_id)
if ids:
params = self.read(ids, load_m2o=False)[0]
else:
params = self.default_get(load_m2o=False, context=context)
settings = get_model("settings").browse(1)
db = database.get_connection()
if not params.get("account_id"):
return
account_id = int(params.get("account_id"))
date = params.get("date")
if not date:
date = time.strftime("%Y-%m-%d")
ctx = {
"date_to": date,
}
acc = get_model("account.account").browse([account_id], context=ctx)[0]
payments = []
for obj in get_model("account.move.line").search_browse([["account_id", "=", account_id], ["state", "!=", "reconciled"], ["move_id.state", "=", "posted"], ["move_id.date", "<=", date]], order="move_id.date"):
vals = {
"date": obj.move_id.date,
"description": obj.description,
"ref": obj.move_id.number,
"amount": obj.credit - obj.debit,
}
payments.append(vals)
st_lines = []
for obj in get_model("account.statement.line").search_browse([["statement_id.account_id", "=", account_id], ["state", "!=", "reconciled"], ["date", "<=", date]], order="date"):
vals = {
"date": obj.date,
"description": obj.description,
"ref": "", # XXX
"amount": obj.received - obj.spent,
}
st_lines.append(vals)
data = {
"company_name": comp.name,
"date": date,
"account_name": acc.name,
"account_balance": acc.balance,
"move_lines": payments,
"total_move_lines": sum([p["amount"] for p in payments]),
"statement_lines": st_lines,
"total_statement_lines": sum([l["amount"] for l in st_lines]),
}
data["statement_balance"] = data["account_balance"] + data["total_move_lines"] + data["total_statement_lines"]
return data
def purchases_per_supplier(self, context={}):
db = get_connection()
company_id = get_active_company()
res = db.query(
"SELECT p.name,SUM(o.amount_total_cur) as amount FROM purchase_order o,contact p WHERE p.id=o.contact_id AND o.state in ('confirmed','done') and o.company_id=%s GROUP BY p.name ORDER BY amount DESC",
company_id)
data = []
for r in res[:5]:
data.append((r.name, r.amount))
amt = 0
for r in res[5:]:
amt += r.amount
if amt > 0:
data.append(("Other", amt))
return {"value": data}
def purchases_per_product_categ(self, context={}):
db = get_connection()
company_id = get_active_company()
res = db.query(
"SELECT c.name,SUM(l.amount_cur) as amount FROM purchase_order_line l,purchase_order o,product p,product_categ c WHERE p.categ_id=c.id AND p.id=l.product_id AND o.id=l.order_id AND o.state in ('confirmed','done') and o.company_id=%s GROUP BY c.name ORDER BY amount DESC",
company_id)
data = []
for r in res[:5]:
data.append((r.name, r.amount))
amt = 0
for r in res[5:]:
amt += r.amount
if amt > 0:
data.append(("Other", amt))
return {"value": data}
def get_report_data(self, ids, context={}):
company_id = get_active_company()
comp = get_model("company").browse(company_id)
if ids:
params = self.read(ids, load_m2o=False)[0]
else:
params = self.default_get(load_m2o=False, context=context)
settings = get_model("settings").browse(1)
date_from = params.get("date_from")
date_to = params.get("date_to")
cond = [["date", ">=", date_from], ["date", "<=", date_to], ["purchase_type_id.commission_po", "=", True]]
groups = {}
for purch in get_model("purchase.order").search_browse(cond, order="date"):
group = groups.get(purch.customer_id.id)
if group is None:
group = {
"customer": purch.customer_id.name or "N/A",
"lines": [],
}
groups[purch.customer_id.id] = group
line = {
"id": purch.id,
"date": purch.date,
"number": purch.number,
"supplier": purch.contact_id.name,
"number": purch.number,
"amount_subtotal": purch.amount_subtotal,
"commission_percent": purch.customer_id.commission_po_percent, # XXX
"commission_amount": purch.amount_subtotal * (purch.customer_id.commission_po_percent or 0) / 100,
}
group["lines"].append(line)
groups = sorted(groups.values(), key=lambda g: g["customer"])
for group in groups:
totals = {}
totals["amount_subtotal"] = sum(l["amount_subtotal"] or 0 for l in group["lines"])
totals["commission_amount"] = sum(l["commission_amount"] or 0 for l in group["lines"])
group["totals"] = totals
totals = {}
totals["amount_subtotal"] = sum(g["totals"]["amount_subtotal"] for g in groups)
totals["commission_amount"] = sum(g["totals"]["commission_amount"] for g in groups)
data = {
"company_name": comp.name,
"date_from": date_from,
"date_to": date_to,
"groups": groups,
"totals": totals,
}
return data
def get_report_data(self, ids, context={}):
company_id = get_active_company()
comp = get_model("company").browse(company_id)
if ids:
params = self.read(ids, load_m2o=False)[0]
else:
params = self.default_get(load_m2o=False, context=context)
settings = get_model("settings").browse(1)
date_from = params.get("date_from")
date_to = params.get("date_to")
pay_method_id = params.get("pay_method_id")
lines = []
cond = [["state", "=", "confirmed"]]
if date_from:
cond.append(["date", ">=", date_from])
if date_to:
cond.append(["date", "<=", date_to])
if pay_method_id:
cond.append(["pay_method_id", "=", pay_method_id])
for sale in get_model("sale.order").search_browse(cond, order="date,id"):
if sale.is_paid:
continue
contact = sale.contact_id
phone = contact.phone
for addr in contact.addresses:
if phone:
break
phone = addr.phone
line = {
"id": sale.id,
"number": sale.number,
"related_id": sale.related_id.id if sale.related_id else None,
"related_number": sale.related_id.number if sale.related_id else None,
"date": sale.date,
"customer_name": contact.name,
"phone": phone,
"pay_method": sale.pay_method_id.name if sale.pay_method_id else None,
"amount": sale.amount_total,
}
lines.append(line)
data = {
"company_name": comp.name,
"date_from": date_from,
"date_to": date_to,
"lines": lines,
"total": sum(l["amount"] for l in lines),
}
return data
def purchases_per_month(self, context={}):
db = get_connection()
company_id = get_active_company()
res = db.query(
"SELECT to_char(date,'YYYY-MM') AS month,SUM(amount_total_cur) as amount FROM purchase_order as po WHERE po.state in ('confirmed','done') and po.company_id=%s GROUP BY month",
company_id)
amounts = {}
for r in res:
amounts[r.month] = r.amount
data = []
months = get_months(6)
for y, m in months:
amt = amounts.get("%d-%.2d" % (y, m), 0)
d = date(year=y, month=m, day=1)
data.append((d.strftime("%B"), amt))
return {"value": data}
def get_data_pick_internal_form(self, context={}):
company_id = get_active_company()
comp = get_model("company").browse(company_id)
obj_id = int(context["refer_id"])
obj = get_model("stock.picking").browse(obj_id)
settings = get_model("settings").browse(1)
comp_addr = settings.get_address_str()
comp_name = comp.name
comp_phone = settings.phone
comp_fax = settings.fax
comp_tax_no = settings.tax_no
contact = obj.contact_id
cust_addr = contact.get_address_str()
cust_name = contact.name
cust_fax = contact.fax
cust_phone = contact.phone
cust_tax_no = contact.tax_no
data = {
"comp_name": comp_name,
"comp_addr": comp_addr,
"comp_phone": comp_phone or "-",
"comp_fax": comp_fax or "-",
"comp_tax_no": comp_tax_no or "-",
"cust_name": cust_name,
"cust_addr": cust_addr,
"cust_phone": cust_phone or "-",
"cust_fax": cust_fax or "-",
"cust_tax_no": cust_tax_no or "-",
"date": obj.date,
"number": obj.number,
"ref": obj.ref,
"lines": [],
}
if settings.logo:
data["logo"] = get_file_path(settings.logo)
for line in obj.lines:
data["lines"].append({
"product": line.product_id.name,
"description": line.product_id.description,
"qty": line.qty,
"uom": line.uom_id.name,
"location_from": line.location_from_id.name,
"location_to": line.location_to_id.name,
"cost_price": line.cost_price,
})
return data
def get_data_pick_internal_form(self, context={}):
company_id = get_active_company()
comp = get_model("company").browse(company_id)
obj_id = int(context["refer_id"])
obj = get_model("stock.picking").browse(obj_id)
settings = get_model("settings").browse(1)
comp_addr = settings.get_address_str()
comp_name = comp.name
comp_phone = settings.phone
comp_fax = settings.fax
comp_tax_no = settings.tax_no
contact = obj.contact_id
cust_addr = contact.get_address_str()
cust_name = contact.name
cust_fax = contact.fax
cust_phone = contact.phone
cust_tax_no = contact.tax_no
data = {
"comp_name": comp_name,
"comp_addr": comp_addr,
"comp_phone": comp_phone or "-",
"comp_fax": comp_fax or "-",
"comp_tax_no": comp_tax_no or "-",
"cust_name": cust_name,
"cust_addr": cust_addr,
"cust_phone": cust_phone or "-",
"cust_fax": cust_fax or "-",
"cust_tax_no": cust_tax_no or "-",
"date": obj.date,
"number": obj.number,
"ref": obj.ref,
"lines": [],
}
if settings.logo:
data["logo"] = get_file_path(settings.logo)
for line in obj.lines:
data["lines"].append({
"product": line.product_id.name,
"description": line.product_id.description,
"qty": line.qty,
"uom": line.uom_id.name,
"location_from": line.location_from_id.name,
"location_to": line.location_to_id.name,
"cost_price": line.cost_price,
})
return data
def get_report_data(self, ids, context={}):
settings = get_model("settings").browse(1)
company_id = get_active_company()
company = get_model("company").browse(company_id)
if ids:
params = self.read(ids, load_m2o=False)[0]
else:
params = self.default_get(load_m2o=False, context=context)
date_from = params.get("date_from")
date_to = params.get("date_to")
contact_id = params.get("contact_id")
state = params.get("state")
lines = []
cond = [["type", "=", "out"]]
if date_from:
cond.append([["date", ">=", date_from]])
if date_to:
cond.append([["date", "<=", date_to]])
if contact_id:
cond.append([["contact_id", "=", contact_id]])
if state:
cond.append([["state", "=", state]])
for inv in get_model("account.invoice").search_browse(cond,
order="date"):
line_vals = {
"id": inv.id,
"number": inv.number,
"ref": inv.ref,
"contact_name": inv.contact_id.name,
"date": inv.date,
"due_date": inv.due_date,
"amount_total": inv.amount_total,
"amount_paid": inv.amount_paid,
"amount_due": inv.amount_due,
"state": inv.state, # XXX
}
lines.append(line_vals)
data = {
"company_name": company.name,
"date_from": date_from,
"date_to": date_to,
"lines": lines,
"total_invoice": sum([l["amount_total"] for l in lines]),
"total_paid": sum([l["amount_paid"] for l in lines]),
"total_due": sum([l["amount_due"] for l in lines]),
}
return data
def get_report_data(self, ids, context={}):
settings = get_model("settings").browse(1)
company_id = get_active_company()
company = get_model("company").browse(company_id)
if ids:
params = self.read(ids, load_m2o=False)[0]
else:
params = self.default_get(load_m2o=False, context=context)
date_from = params.get("date_from")
date_to = params.get("date_to")
contact_id = params.get("contact_id")
state = params.get("state")
lines = []
cond = [["type", "=", "out"]]
if date_from:
cond.append([["date", ">=", date_from]])
if date_to:
cond.append([["date", "<=", date_to]])
if contact_id:
cond.append([["contact_id", "=", contact_id]])
if state:
cond.append([["state", "=", state]])
for inv in get_model("account.invoice").search_browse(cond, order="date"):
line_vals = {
"id": inv.id,
"number": inv.number,
"ref": inv.ref,
"contact_name": inv.contact_id.name,
"date": inv.date,
"due_date": inv.due_date,
"amount_total": inv.amount_total,
"amount_paid": inv.amount_paid,
"amount_due": inv.amount_due,
"state": inv.state, # XXX
}
lines.append(line_vals)
data = {
"company_name": company.name,
"date_from": date_from,
"date_to": date_to,
"lines": lines,
"total_invoice": sum([l["amount_total"] for l in lines]),
"total_paid": sum([l["amount_paid"] for l in lines]),
"total_due": sum([l["amount_due"] for l in lines]),
}
return data
def get_report_data(self, ids, context={}):
company_id = get_active_company()
comp = get_model("company").browse(company_id)
if ids:
params = self.read(ids, load_m2o=False)[0]
else:
params = self.default_get(load_m2o=False, context=context)
settings = get_model("settings").browse(1)
date_from = params.get("date_from")
date_to = params.get("date_to")
lines = []
cond = [["date", ">=", date_from], ["date", "<=", date_to]]
for sale in get_model("sale.order").search_browse(cond, order="date"):
line = {
"id": sale.id,
"date": sale.date,
"number": sale.number,
"customer": sale.contact_id.name,
"sale": sale.number,
"amount_subtotal": sale.amount_subtotal,
"est_cost_total": sale.est_cost_total,
"est_profit": sale.est_profit,
"est_profit_percent": sale.est_profit_percent,
"act_cost_total": sale.act_cost_amount,
"act_profit_amount": decimal.Decimal(sale.act_profit_amount or 0),
"act_margin_percent": decimal.Decimal(sale.act_margin_percent or 0),
}
lines.append(line)
totals = {}
totals["amount_subtotal"] = sum(l["amount_subtotal"] or 0 for l in lines)
totals["est_cost_total"] = sum(l["est_cost_total"] or 0 for l in lines)
totals["est_profit"] = sum(l["est_profit"] or 0 for l in lines)
totals["est_profit_percent"] = totals["est_profit"] * 100 / \
totals["amount_subtotal"] if totals["amount_subtotal"] else None
totals["act_cost_total"] = sum(l["act_cost_total"] or 0 for l in lines)
totals["act_profit_amount"] = sum(l["act_profit_amount"] or 0 for l in lines)
totals["act_margin_percent"] = totals["act_profit_amount"] * 100 / \
totals["amount_subtotal"] if totals["amount_subtotal"] else None
data = {
"company_name": comp.name,
"date_from": date_from,
"date_to": date_to,
"lines": lines,
"totals": totals,
}
return data
def get_report_data(self, ids, context={}):
company_id = get_active_company()
comp = get_model("company").browse(company_id)
if ids:
params = self.read(ids, load_m2o=False)[0]
else:
params = self.default_get(load_m2o=False, context=context)
settings = get_model("settings").browse(company_id)
contact_id = params.get("contact_id")
cond = [["date", ">=", params["date_from"]],
["date", "<=", params["date_to"]]]
if params.get("contact_id"):
cond.append(["contact_id", "=", params["contact_id"]])
if params.get("ship_pay_by"):
cond.append(["ship_pay_by", "=", params["ship_pay_by"]])
if params.get("ship_method_id"):
cond.append(["ship_method_id", "=", params["ship_method_id"]])
lines = []
for pick in get_model("stock.picking").search_browse(cond):
line = {
"id": pick.id,
"date": pick.date,
"number": pick.number,
"contact": pick.contact_id.name,
"ship_method": pick.ship_method_id.name,
"ship_tracking": pick.ship_tracking,
"ship_cost": pick.ship_cost,
"ship_pay_by": pick.ship_pay_by, # XXX
}
try:
lines["related"] = pick.related_id.name_get(
)[0][1] if pick.related_id else None
except:
pass # XXX
lines.append(line)
totals = {}
totals["ship_cost"] = sum(l["ship_cost"] or 0 for l in lines)
data = {
"company_name": comp.name,
"date_from": params["date_from"],
"date_to": params["date_to"],
"lines": lines,
"totals": totals,
}
return data
def find_sequence(self, type=None, name=None, context={}):
if type and name:
cond = [["type", "=", type], ["name", "=", name]]
elif type:
cond = [["type", "=", type]]
elif name:
cond = [["name", "=", name]]
company_id=context.get("company_id")
if not company_id:
company_id = get_active_company()
comp_cond = cond + [["company_id", "=", company_id]]
res = self.search(comp_cond, order="id")
if res:
return res[0]
res = self.search(cond, order="id")
if res:
return res[0]
return None
def find_sequence(self, type=None, name=None, context={}):
if type and name:
cond = [["type", "=", type], ["name", "=", name]]
elif type:
cond = [["type", "=", type]]
elif name:
cond = [["name", "=", name]]
company_id=context.get("company_id")
if not company_id:
company_id = get_active_company()
comp_cond = cond + [["company_id", "=", company_id]]
res = self.search(comp_cond, order="id")
if res:
return res[0]
res = self.search(cond, order="id")
if res:
return res[0]
return None
def get_report_data(self, ids, context={}):
company_id = get_active_company()
company_ids = get_model("company").search([["id", "child_of", company_id]])
comp = get_model("company").browse(company_id)
if ids:
params = self.read(ids, load_m2o=False)[0]
else:
params = self.default_get(load_m2o=False, context=context)
settings = get_model("settings").browse(1)
tax_comp_id = params.get("tax_comp_id")
trans_type=params.get("trans_type")
if tax_comp_id:
tax_comp_id=int(tax_comp_id)
tax_comp = get_model("account.tax.component").browse(tax_comp_id)
tax_comp_ids=[tax_comp_id]
tax_type=tax_comp.tax_type
else:
tax_type=params.get("tax_type")
if not tax_type or not trans_type:
return
tax_comp_ids=get_model("account.tax.component").search([["type","=",tax_type],["trans_type", "=", trans_type]])
tax_comp=None
date_from = params.get("date_from")
if not date_from:
date_from = date.today().strftime("%Y-%m-01")
date_to = params.get("date_to")
if not date_to:
date_to = (date.today() + relativedelta(day=31)).strftime("%Y-%m-%d")
data = {
"company_name": comp.name,
"rate_name": tax_comp.tax_rate_id.name if tax_comp else None,
"comp_name": tax_comp.name if tax_comp else None,
"tax_type": tax_type,
"date_from": date_from,
"date_to": date_to,
}
db = database.get_connection()
res = db.query("SELECT m.id AS move_id,m.date,a.name AS account_name,a.code AS account_code,m.number,m.ref,l.description,case when 'in' = %s then l.debit-l.credit else l.credit-l.debit end AS tax_amount, case when 'in' = %s then l.tax_base*sign(l.debit-l.credit) else l.tax_base*sign(l.credit-l.debit) end AS base_amount,c.name as contact_name,c.id as contact_id FROM account_move_line l JOIN account_move m ON m.id=l.move_id JOIN account_account a ON a.id=l.account_id LEFT JOIN contact c ON c.id=l.contact_id WHERE m.state='posted' AND m.date>=%s AND m.date<=%s AND l.tax_comp_id IN %s AND m.company_id IN %s ORDER BY date",
trans_type, trans_type, date_from, date_to, tuple(tax_comp_ids), tuple(company_ids))
data["lines"] = [dict(r) for r in res]
data["base_total"]=sum(l["base_amount"] for l in data["lines"])
data["tax_total"]=sum(l["tax_amount"] for l in data["lines"])
data["credit_lines"]=[]
return data
def get_data(self, context={}):
company_id = get_active_company()
comp = get_model("company").browse(company_id)
month = context.get("month")
lines = []
no = 1
for slip in self.get_payslip_line(month):
if slip.amount_provident < 1:
continue
emp = slip.employee_id
emp_name = ' '.join(
s for s in [(emp.title and emp.title + '.' or ''), (emp.first_name or ''), (emp.last_name or '')])
line = {
'no': no,
'tran_code': '',
'employee_code': emp.code,
'prov_code': emp.prov_fund_no,
'employee_name': emp_name,
'id_code': emp.id_no,
'tax_id': emp.tax_no,
'date_cal_year': '',
'start_work_date': emp.hire_date and emp.hire_date.replace("-", "/") or '',
'open_ac_date': emp.prov_open_date,
'salary': emp.salary,
'num_of_restdays': 0,
'employee_rate': emp.prov_rate_employee,
'employee_amt': slip.amount_provident,
'employer_rate': emp.prov_rate_employer,
'employer_amt': slip.amount_provident,
}
no += 1
lines.append(line)
# settings=get_model("settings").browse(1)
setting = get_model("hr.payroll.settings").browse(1)
document_date = utils.date2thai(time.strftime("%Y-%m-%d"), '%(d)s/%(m)s/%(BY)s')
data = {
'company_name': comp.name,
'document_date': document_date,
'fund_name': setting.prov_name,
'lines': lines,
'norecord': False if lines else True,
}
return data
class Journal(Model):
_name = "account.journal"
_string = "Journal"
_key = ["code"]
_multi_company = True
_fields = {
"name": fields.Char("Name", required=True, search=True),
"sequence_id": fields.Many2One("sequence",
"Sequence",
multi_company=True),
"comments": fields.One2Many("message", "related_id", "Comments"),
"active": fields.Boolean("Active"),
"code": fields.Char("Code", search=True),
"company_id": fields.Many2One("company", "Company"),
}
_defaults = {
"active": True,
"company_id": lambda *a: get_active_company(),
}
def opport_stage(self, context={}):
db = get_connection()
company_id = get_active_company()
res = db.query(
"SELECT s.name,SUM(o.amount*o.probability/100) AS amount,COUNT(*) AS num FROM sale_opportunity o,sale_stage s WHERE s.id=o.stage_id AND o.state in ('open','won') and o.company_id=%s GROUP BY s.name",
company_id)
amounts = {}
counts = {}
for r in res:
amounts[r.name] = r.amount
counts[r.name] = r.num
data = []
for stage in get_model("sale.stage").search_browse([]):
amt = amounts.get(stage.name, 0)
count = counts.get(stage.name, 0)
#label="%s (%d)"%(stage.name,count)
label = stage.name
data.append((label, amt))
return {"value": data}
def money_in(self, context={}):
company_id = get_active_company()
company_ids = get_model("company").search(
[["id", "child_of", company_id]])
db = get_connection()
res = db.query(
"SELECT to_char(COALESCE(l.due_date,l.move_date),'YYYY-MM') AS month,SUM(l.debit-l.credit) as amount FROM account_move_line l JOIN account_account a ON a.id=l.account_id LEFT JOIN account_reconcile r ON r.id=l.reconcile_id WHERE l.move_state='posted' AND a.type='receivable' AND l.reconcile_id IS NULL AND a.company_id IN %s GROUP BY month",
tuple(company_ids))
amounts = {}
for r in res:
amounts[r.month] = r.amount
months = get_months(4)
month_start = "%d-%.2d" % (months[0][0], months[0][1])
month_stop = "%d-%.2d" % (months[-1][0], months[-1][1])
amt_older = 0
amt_future = 0
for month, amt in amounts.items():
if month < month_start:
amt_older += amt
elif month > month_stop:
amt_future += amt
data2 = [("Older", amt_older)]
for y, m in months:
d = date(year=y, month=m, day=1)
amt = amounts.get("%d-%.2d" % (y, m), 0)
data2.append((d.strftime("%B"), amt))
data2.append(("Future", amt_future))
data = {
"value": data2,
}
res = db.get(
"SELECT count(*) AS count,SUM(amount_total_cur) AS amount FROM account_invoice WHERE type='out' AND inv_type='invoice' AND state='draft' AND company_id IN %s",
tuple(company_ids))
if res:
data["draft_count"] = res.count
data["draft_amount"] = res.amount
res = db.get(
"SELECT count(*) AS count,SUM(amount_total_cur) AS amount FROM account_invoice WHERE type='out' AND inv_type='invoice' AND state='waiting_payment' AND due_date<now() AND company_id IN %s",
tuple(company_ids))
if res:
data["overdue_count"] = res.count
data["overdue_amount"] = res.amount
return data
def copy_to_pick_in(self, ids, context={}):
obj=self.browse(ids)[0]
settings = get_model("settings").browse(1)
if not settings.product_borrow_journal_id:
raise Exception("Missing borrow request journal in Inventory Setting")
if not settings.product_borrow_journal_id.location_from_id:
raise Exception("Missing 'Location From' for journal '%s'"%settings.product_borrow_journal_id.name)
if not settings.product_borrow_journal_id.location_to_id:
raise Exception("Missing 'Location To' for journal '%s'"%settings.product_borrow_journal_id.name)
user_id=get_active_user()
user=get_model("base.user").browse(user_id)
pick_vals = {
"type": "in",
"ref": obj.number,
"journal_id": settings.product_borrow_journal_id.id,
"related_id": "product.borrow,%s" % obj.id,
"contact_id": user.contact_id.id,
"lines": [],
"state": "draft",
"company_id": get_active_company(),
}
for line in obj.lines:
line_vals = {
"product_id": line.product_id.id,
"qty": line.qty,
"uom_id": line.uom_id.id,
"location_from_id": settings.product_borrow_journal_id.location_to_id.id,
"location_to_id": settings.product_borrow_journal_id.location_from_id.id,
"lot_id": line.lot_id.id,
"related_id": "product.borrow,%s" % obj.id,
}
pick_vals["lines"].append(("create", line_vals))
pick_id = get_model("stock.picking").create(pick_vals, context=pick_vals)
pick = get_model("stock.picking").browse(pick_id)
return {
"next": {
"name": "pick_in",
"mode": "form",
"active_id": pick_id,
},
"flash": "Picking %s created from borrow request %s" % (pick.number, obj.number),
"picking_id": pick_id,
}
def get_report_data(self, ids, context={}):
company_id = get_active_company()
comp = get_model("company").browse(company_id)
if ids:
params = self.read(ids, load_m2o=False)[0]
else:
params = self.default_get(load_m2o=False, context=context)
settings = get_model("settings").browse(company_id)
contact_id = params.get("contact_id")
cond = [["date", ">=", params["date_from"]], ["date", "<=", params["date_to"]]]
if params.get("contact_id"):
cond.append(["contact_id", "=", params["contact_id"]])
if params.get("ship_pay_by"):
cond.append(["ship_pay_by", "=", params["ship_pay_by"]])
if params.get("ship_method_id"):
cond.append(["ship_method_id", "=", params["ship_method_id"]])
lines = []
for pick in get_model("stock.picking").search_browse(cond):
line = {
"id": pick.id,
"date": pick.date,
"number": pick.number,
"contact": pick.contact_id.name,
"ship_method": pick.ship_method_id.name,
"ship_tracking": pick.ship_tracking,
"ship_cost": pick.ship_cost,
"ship_pay_by": pick.ship_pay_by, # XXX
}
try:
lines["related"] = pick.related_id.name_get()[0][1] if pick.related_id else None
except:
pass # XXX
lines.append(line)
totals = {}
totals["ship_cost"] = sum(l["ship_cost"] or 0 for l in lines)
data = {
"company_name": comp.name,
"date_from": params["date_from"],
"date_to": params["date_to"],
"lines": lines,
"totals": totals,
}
return data
def copy_to_picking(self, ids, context={}):
obj=self.browse(ids[0])
settings = get_model("settings").browse(1)
if not settings.product_borrow_journal_id:
raise Exception("Missing borrow request journal in Inventory Setting")
if not settings.product_borrow_journal_id.location_from_id:
raise Exception("Missing 'Location From' for journal '%s'"%settings.product_borrow_journal_id.name)
if not settings.product_borrow_journal_id.location_to_id:
raise Exception("Missing 'Location To' for journal '%s'"%settings.product_borrow_journal_id.name)
user_id=get_active_user()
user=get_model("base.user").browse(user_id)
pick_vals = {
"type": "out",
"ref": obj.number,
"journal_id": settings.product_borrow_journal_id.id,
"related_id": "product.borrow,%s" % obj.id,
"contact_id": user.contact_id.id,
"lines": [],
"state": "draft",
"company_id": get_active_company(),
}
for line in obj.lines:
line_vals = {
"product_id": line.product_id.id,
"qty": line.qty,
"uom_id": line.uom_id.id,
"location_from_id": settings.product_borrow_journal_id.location_from_id.id,
"location_to_id": settings.product_borrow_journal_id.location_to_id.id,
"lot_id": line.lot_id.id,
"related_id": "product.borrow,%s" % obj.id,
}
pick_vals["lines"].append(("create", line_vals))
pick_id = get_model("stock.picking").create(pick_vals, context=pick_vals)
pick = get_model("stock.picking").browse(pick_id)
return {
"next": {
"name": "pick_out",
"mode": "form",
"active_id": pick_id,
},
"flash": "Picking %s created from borrow request %s" % (pick.number, obj.number),
"picking_id": pick_id,
}
def get_report_data(self, ids, context={}):
company_id = get_active_company()
comp = get_model("company").browse(company_id)
if ids:
params = self.read(ids, load_m2o=False)[0]
else:
params = self.default_get(load_m2o=False, context=context)
settings = get_model("settings").browse(1)
date_from = params.get("date_from")
date_to = params.get("date_to")
lines = []
cond = [["date", ">=", date_from], ["date", "<=", date_to]]
for sale in get_model("sale.order").search_browse(cond, order="date"):
line = {
"id": sale.id,
"date": sale.date,
"number": sale.number,
"customer": sale.contact_id.name,
"sale": sale.number,
"amount_subtotal": sale.amount_subtotal,
"est_cost_total": sale.est_cost_total,
"est_profit": sale.est_profit,
"est_profit_percent": sale.est_profit_percent,
"act_cost_total": sale.act_cost_total,
"act_profit": sale.act_profit,
"act_profit_percent": sale.act_profit_percent,
}
lines.append(line)
totals = {}
totals["amount_subtotal"] = sum(l["amount_subtotal"] or 0 for l in lines)
totals["est_cost_total"] = sum(l["est_cost_total"] or 0 for l in lines)
totals["est_profit"] = sum(l["est_profit"] or 0 for l in lines)
totals["est_profit_percent"] = (
totals["est_profit"] * 100 / totals["amount_subtotal"] if totals["amount_subtotal"] else None
)
totals["act_cost_total"] = sum(l["act_cost_total"] or 0 for l in lines)
totals["act_profit"] = sum(l["act_profit"] or 0 for l in lines)
totals["act_profit_percent"] = (
totals["act_profit"] * 100 / totals["amount_subtotal"] if totals["amount_subtotal"] else None
)
data = {"company_name": comp.name, "date_from": date_from, "date_to": date_to, "lines": lines, "totals": totals}
return data
def receivable_status(self, context={}):
company_id = get_active_company()
company_ids = get_model("company").search(
[["id", "child_of", company_id]])
data = {}
for st in ("draft", "waiting_approval", "waiting_payment"):
data[st] = {
"count": 0,
"amount": 0,
}
db = get_connection()
res = db.query(
"SELECT state,COUNT(*) as count,SUM(amount_due_cur) AS amount FROM account_invoice WHERE type='out' AND inv_type='invoice' AND company_id IN %s GROUP BY state",
tuple(company_ids))
for r in res:
data[r["state"]] = {
"count": r["count"],
"amount": r["amount"],
}
return data
def get_report_data_custom(self, ids, context={}):
company_id = get_active_company()
comp = get_model("company").browse(company_id)
if ids:
params = self.read(ids, load_m2o=False)[0]
else:
params = self.default_get(load_m2o=False, context=context)
settings = get_model("settings").browse(1)
date_to = params.get("date")
d0 = datetime.strptime(date_to, "%Y-%m-%d")
prev_month_date_to = (d0 - relativedelta(day=1) - timedelta(days=1)).strftime("%Y-%m-%d")
prev_year_date_to = (d0 - relativedelta(years=1)).strftime("%Y-%m-%d")
data = {
"date_to": date_to,
"prev_month_date_to": prev_month_date_to,
"prev_year_date_to": prev_year_date_to,
"company_name": comp.name,
}
print("data", data)
return data
def get_total_qtys(prod_ids, loc_id, date_from, date_to, states, categ_id):
db = get_connection()
q = "SELECT " \
" t1.product_id,t1.location_from_id,t1.location_to_id,t1.uom_id,SUM(t1.qty) AS total_qty " \
" FROM stock_move t1 " \
" LEFT JOIN product t2 on t1.product_id=t2.id " \
" WHERE t1.state IN %s"
q_args = [tuple(states)]
if date_from:
q += " AND t1.date>=%s"
q_args.append(date_from + " 00:00:00")
if date_to:
q += " AND t1.date<=%s"
q_args.append(date_to + " 23:59:59")
if prod_ids:
q += " AND t1.product_id IN %s"
q_args.append(tuple(prod_ids))
if loc_id:
q += " AND (t1.location_from_id=%s OR t1.location_to_id=%s)"
q_args += [loc_id, loc_id]
if categ_id:
q += " AND t2.categ_id=%s"
q_args.append(categ_id)
company_id = access.get_active_company()
if company_id:
q += " AND t1.company_id=%s"
q_args.append(company_id)
q += " GROUP BY t1.product_id,t1.location_from_id,t1.location_to_id,t1.uom_id"
print("q",q)
print("q_args",q_args)
res = db.query(q, *q_args)
totals = {}
for r in res:
prod = get_model("product").browse(r.product_id)
uom = get_model("uom").browse(r.uom_id)
qty = r.total_qty * uom.ratio / prod.uom_id.ratio
k = (r.product_id, r.location_from_id, r.location_to_id)
totals.setdefault(k, 0)
totals[k] += qty
return totals
def money_in(self, context={}):
company_id = get_active_company()
company_ids = get_model("company").search([["id", "child_of", company_id]])
db = get_connection()
res = db.query(
"SELECT to_char(COALESCE(l.due_date,l.move_date),'YYYY-MM') AS month,SUM(l.debit-l.credit) as amount FROM account_move_line l JOIN account_account a ON a.id=l.account_id LEFT JOIN account_reconcile r ON r.id=l.reconcile_id WHERE l.move_state='posted' AND a.type='receivable' AND l.reconcile_id IS NULL AND a.company_id IN %s GROUP BY month", tuple(company_ids))
amounts = {}
for r in res:
amounts[r.month] = r.amount
months = get_months(4)
month_start = "%d-%.2d" % (months[0][0], months[0][1])
month_stop = "%d-%.2d" % (months[-1][0], months[-1][1])
amt_older = 0
amt_future = 0
for month, amt in amounts.items():
if month < month_start:
amt_older += amt
elif month > month_stop:
amt_future += amt
data2 = [("Older", amt_older)]
for y, m in months:
d = date(year=y, month=m, day=1)
amt = amounts.get("%d-%.2d" % (y, m), 0)
data2.append((d.strftime("%B"), amt))
data2.append(("Future", amt_future))
data = {
"value": data2,
}
res = db.get(
"SELECT count(*) AS count,SUM(amount_total_cur) AS amount FROM account_invoice WHERE type='out' AND inv_type='invoice' AND state='draft' AND company_id IN %s", tuple(company_ids))
if res:
data["draft_count"] = res.count
data["draft_amount"] = res.amount
res = db.get(
"SELECT count(*) AS count,SUM(amount_total_cur) AS amount FROM account_invoice WHERE type='out' AND inv_type='invoice' AND state='waiting_payment' AND due_date<now() AND company_id IN %s", tuple(company_ids))
if res:
data["overdue_count"] = res.count
data["overdue_amount"] = res.amount
return data