Script Query Issue

ERPNext
1

The following query picks data from Budget table, Project table then run a subquery to get total wages. The columns Project, Project Name, Budget Amount is displaying TotWages showing zero. The SQL work fine when I tested in other tools and returns values. I am not familiar with script report, may be I am doing something wrong. Please help.

columns = [
_(“Project”) + “:Link/Project:150”,
_(“Project Name”) + “::300”,
_(“Budget Amount”) + “:Currency:150”,
_(“TotWages”) + “:Currency:150”,

]

2. Fetch the data

data = frappe.db.sql(“”"
SELECT
b.project,
p.project_name,
ba.budget_amount,
att.TotWages
FROM tabBudget b
INNER JOIN tabBudget Account ba ON ba.parent = b.name
INNER JOIN tabProject p ON p.name = b.project
LEFT JOIN (
SELECT
sda.project,
SUM(sda.hourly_rate * sda.total_hours) AS TotWages
FROM tabSite Daily Attendance sda
WHERE sda.docstatus = 1
GROUP BY sda.project
) att ON att.project = b.project
WHERE ba.account = ‘Wages - VE’;
“”", as_dict=True)

data = (columns, data) # Proper tuple formatting

2

everything you have looks correct. Try the same py code in System Console and check the output. also attach screenshots if possible, something may standout

3

Excecuted from System console. Could not trace out anything.

image
image1548×901 53.5 KB

Kindly provide more insights.
Thanks

4

Hi @Asha:

Use return columns, data on your script report file

This way:

columns = [
_(“Project”) + “:Link/Project:150”,
_(“Project Name”) + “::300”,
_(“Budget Amount”) + “:Currency:150”,
_(“TotWages”) + “:Currency:150”,
]

data = frappe.db.sql('''
SELECT
b.project,
p.project_name,
ba.budget_amount,
att.TotWages
FROM tabBudget b
INNER JOIN tabBudget Account ba ON ba.parent = b.name
INNER JOIN tabProject p ON p.name = b.project
LEFT JOIN (
SELECT
sda.project,
SUM(sda.hourly_rate * sda.total_hours) AS TotWages
FROM tabSite Daily Attendance sda
WHERE sda.docstatus = 1
GROUP BY sda.project
) att ON att.project = b.project
WHERE ba.account = ‘Wages - VE’;
''', as_dict=True)

return columns, data

Hope this helps.

5

Can you print(data), print (columns) and show the output

6

Try this way… :slight_smile:

columns = [
_(“Project”) + “:Link/Project:150”,
_(“Project Name”) + “::300”,
_(“Budget Amount”) + “:Currency:150”,
_(“TotWages”) + “:Currency:150”,
]

sql_query = “”"
SELECT
b.project,
p.project_name,
ba.budget_amount,
att.TotWages
FROM tabBudget b
INNER JOIN tabBudget Account ba ON ba.parent = b.name
INNER JOIN tabProject p ON p.name = b.project
LEFT JOIN (
SELECT
sda.project,
SUM(sda.hourly_rate * sda.total_hours) AS TotWages
FROM tabSite Daily Attendance sda
WHERE sda.docstatus = 1
GROUP BY sda.project
) att ON att.project = b.project
WHERE ba.account = ‘Wages - VE’
“”"

lett = frappe.db.sql(sql_query, as_dict=True)
data = columns, lett

7

pymysql.err.ProgrammingError: (1064, “You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘ba ON ba.parent = b.name\n INNER JOIN tabProject p ON p.name = b.project\n …’ at line 7”) – i think sub query not allowed ?

8

image
image841×868 62.9 KB

These columns are correct.

9

image
image1728×939 97.5 KB

Thanks for step by step guidance,
Data is populating.

10

Says return outside function

11

You cannot use return in server script… the output has to be assigned to data

Your code is working in System Console… so it should work in script report… use same code as before…


columns = [
    "Project:Link/Project:150",
    "Project Name::300",
    "Budget Amount:Currency:150",
    "TotWages:Currency:150",
]

rows = frappe.db.sql(""",
    SELECT
        b.project,
        p.project_name,
        ba.budget_amount,
        att.TotWages
    FROM tabBudget b
    INNER JOIN tabBudget Account ba ON ba.parent = b.name
    INNER JOIN tabProject p ON p.name = b.project
    LEFT JOIN (
        SELECT
        sda.project,
        SUM(sda.hourly_rate * sda.total_hours) AS TotWages
        FROM tabSite Daily Attendance sda
        WHERE sda.docstatus = 1
        GROUP BY sda.project
    ) att ON att.project = b.project
    WHERE ba.account = 'Wages - VE';
""", as_dict=True)

data = (columns, rows)

the other option is to define columns in the UI and in py do result = [..] . refer this

12

Column Name: TotWages

for above column name sql return should be totwages.

For Example:

columns = [
_(“Budget Amount”) + “:Currency:150”,
_(“TotWages”) + “:Currency:150”
]

data = frappe.db.sql("""
Select
budget_amount,
totwages 
from table
""", as_dict = 1
)

for each column frappe.scrub operation will be performed and that we need to mention in sql,

For Budget Amount Column we have added budget_amount in SQL, same for TotWages we have added totwages.

This will solve your issue. :+1:

2 Likes
13

Thank you Sahil, it works perfectly.