here i have set datatype as currency but it can’t show the total value .i also tried currency as int , float but it not working
#here is my code i have no function called add_colum , i am getting confused what should be change.
from future import unicode_literals
import frappe
from frappe import _
import math
import datetime
def execute(filters=None):
return columns(filters), data(filters)
def columns(filters):
from_date=filters.get(‘from_date’)
to_date=filters.get(‘to_date’)
day=frappe.utils.date_diff(to_date,from_date)
if(day<30 or day<31):
month=1
else:
month=math.ceil(float(day)/30)
if(from_date =="" or to_date ==""):
frappe.throw("Please Set From Date to Date First")
if(to_date<from_date):
frappe.throw("From Date Must be Smaller Than To Date")
# if filters.get('from_date'):
# filters['schedule_date'] = ('>=', filters.from_date)
# del filters['from_date']
# if filters.get('to_date'):
# filters['schedule_date'] = ('<=', filters.to_date)
# del filters['to_date']
# conditions, values = frappe.db.build_conditions(filters)
# sql="""SELECT * FROM `tabDepreciation Schedule` {}""".format("WHERE "+ conditions if conditions else "")
# schedule_date_get=frappe.db.sql(sql,values=values)
test_data=[]
for x in range(0,int(month)):
if(x==0):
test={
"fieldname":"schedule"+str(x)+"",
"fieldtype":"Data",
"label":_(from_date),
"width":120,
}
else:
test={
"fieldname":"schedule"+str(x)+"",
"fieldtype":"Data",
"label":_(frappe.utils.get_last_day(frappe.utils.data.add_months(from_date,x))),
"width":120,
}
test_data.append(test)
colum=[
{
"fieldname":"asset_name",
"fieldtype":"Link",
"label":_("Particulars"),
"width":120,
"options":"Asset"
},
{
"fieldname":"warehouse",
"fieldtype":"Data",
"label":_("Location"),
"width":120,
},
{
"fieldname":"id_no",
"fieldtype":"Data",
"label":_("ID NO"),
"width":120,
},
{
"fieldname":"rate",
"fieldtype":"Data",
"label":_("Rate"),
"width":120,
},
{
"fieldname":"quantity",
"fieldtype":"Data",
"label":_("Quantity"),
"width":120,
},
{
"fieldname":"purchase_date",
"fieldtype":"Data",
"label":_("Acquisition Date"),
"width":120,
},
{
"fieldname":"gross_purchase_amount",
"fieldtype":"int",
"label":_("Acquisition Value"),
"width":120,
},
{
"fieldname":"opening_accumulated_depreciation",
"fieldtype":"currency",
"label":_("Accu. Depr.Opening"),
"width":120,
},
# {
# "fieldname":"schedule[]",
# "fieldtype":"Data",
# "label":_(frappe.utils.data.get_last_day(from_date)),
# "width":120,
# },
{
"fieldname":"total_depreciation",
"fieldtype":"currency",
"label":_("Total Deprection"),
"width":120,
},
{
"fieldname":"accumulated_depreciation_closing",
"fieldtype":"currency",
"label":_("Accu. Dep.Closing"),
"width":120,
},
{
"fieldname":"wdv",
"fieldtype":"currency",
"label":_("W. D. V"),
"width":120,
},
]
c_list=[]
c_list.extend(colum[:8])
c_list.extend(test_data)
c_list.extend(colum[8:])
return c_list
def data(filters):
data=[]
from_date=filters.get('from_date')
to_date=filters.get('to_date')
day=frappe.utils.date_diff(to_date,from_date)
if filters.get('from_date'):
filters['schedule_date'] = ('>=', filters.from_date)
del filters['from_date']
if filters.get('to_date'):
filters['schedule_date'] = ('<=', filters.to_date)
del filters['to_date']
category=filters.get('asset_category')
asset=frappe.get_all('Asset',fields=['asset_name','warehouse','id','quantity','purchase_date','gross_purchase_amount','opening_accumulated_depreciation','asset_category','name'],filters={'name':filters.get('name'),'asset_category':category})
##frappe.throw(str(asset))
for asset_info in asset:
row = {"asset_name":asset_info.asset_name}
row.update({"warehouse":asset_info.warehouse})
row.update({"id_no":asset_info.id})
asset_category_data=frappe.get_value("Asset Category",{'name':asset_info.asset_category},'*')
row.update({"rate":asset_category_data.rate})
row.update({"quantity":asset_info.quantity})
row.update({"purchase_date":asset_info.purchase_date})
row.update({"gross_purchase_amount":asset_info.gross_purchase_amount})
row.update({"opening_accumulated_depreciation":asset_info.opening_accumulated_depreciation})
if filters.get('from_date'):
filters['schedule_date'] = ('>=', filters.from_date)
del filters['from_date']
if filters.get('to_date'):
filters['schedule_date'] = ('<=', filters.to_date)
del filters['to_date']
if filters.get('asset_category'):
del filters['asset_category']
filters['parent']=asset_info.name
conditions, values = frappe.db.build_conditions(filters)
sql="""SELECT depreciation_amount,schedule_date FROM `tabDepreciation Schedule` {}""".format("WHERE "+ conditions if conditions else "")
schedule_date_get=frappe.db.sql(sql,values=values)
if(day<30 or day<31):
month=1
else:
month=math.ceil(float(day)/30)
total_dep=0
for x in range(0,int(month)):
date=frappe.utils.get_last_day(frappe.utils.data.add_months(from_date,x))
for i in schedule_date_get:
if(date==i[1]):
row.update({"schedule"+str(x)+"":i[0]})
total_dep=total_dep+i[0]
row.update({"total_depreciation":int(total_dep)})
total_dep_amount=total_dep+asset_info.opening_accumulated_depreciation
row.update({"accumulated_depreciation_closing":int(total_dep_amount)})
wdb=int(asset_info.gross_purchase_amount)-int(total_dep_amount)
row.update({"wdv":wdb})
data.append(row)
return data
Acquisition value , Accu Depr opening , Total Depreciation , Acu Dep Closing , W.D.V
Try as setting up Int or Float field type on those columns. May be it works
Already try it , but still now faced this problem