How to fetch parent doctype name

Ashutosh_Halape · December 10, 2019, 9:14am

I want to fetch parent doctype field such as (Doctype = Address).How do i fetch this using sql query.I dont want to fetch doctype name.Please help I am new to erpnext.

ROHAN_JAIN1 · December 10, 2019, 9:35am

By using frappe call you can fetch parent doctype field.

Ashutosh_Halape · December 10, 2019, 9:40am

@ROHAN_JAIN1 thank you for your reply. Can you give an example.I want to write a query report for the same.

ROHAN_JAIN1 · December 10, 2019, 9:42am

You want to fetch in query report? or in js file?

Ashutosh_Halape · December 10, 2019, 9:42am

In query report

ROHAN_JAIN1 · December 10, 2019, 10:11am

For Query Report you can refer this.

github.com

frappe/erpnext/blob/develop/erpnext/accounts/report/item_wise_purchase_register/item_wise_purchase_register.py

# Copyright (c) 2015, Frappe Technologies Pvt. Ltd. and Contributors
# License: GNU General Public License v3. See license.txt


import frappe
from frappe import _
from frappe.utils import flt

import erpnext
from erpnext.accounts.report.item_wise_sales_register.item_wise_sales_register import (
	add_sub_total_row,
	add_total_row,
	get_grand_total,
	get_group_by_and_display_fields,
	get_group_by_conditions,
	get_tax_accounts,
)
from erpnext.selling.report.item_wise_sales_history.item_wise_sales_history import get_item_details

This file has been truncated. show original

Ashutosh_Halape · December 10, 2019, 10:19am

I have written the following query report.Please tell me what changes need to be done to fetch doctype such as address,tax rule and item.

SELECT
AD.name AS "Doc#:Link/Doctype:120",
AD.owner AS "Created By:Link/User:120",
AD.modified AS "Modified On:Date:120"
FROM `tabAddress` AS AD
WHERE
DATEDIFF(now(),AD.modified) = 1
UNION ALL
SELECT
TR.name AS "Doc#:Link/Doctype:120",
TR.owner AS "Created By:Link/User:120",
TR.modified AS "Modified On:Date:120"
FROM `tabTax Rule` AS TR
WHERE
DATEDIFF(now(),TR.modified) = 1
UNION ALL
SELECT
IT.name AS "Doc#:Link/Doctype:120",
IT.owner AS "Created By:Link/User:120",
IT.modified AS "Modified On:Date:120"
FROM `tabItem` AS IT
WHERE
DATEDIFF(now(),IT.modified) = 1

ROHAN_JAIN1 · December 10, 2019, 10:23am

Columns are created like this.

github.com

frappe/erpnext/blob/ce42f48bfbe8ec13dc5d0da44b718eb1355091b0/erpnext/accounts/report/item_wise_purchase_register/item_wise_purchase_register.py#L72


      
          			row += [item_tax.get("tax_rate", 0), item_tax.get("tax_amount", 0)]
          			total_tax += flt(item_tax.get("tax_amount"))
          
          
		row += [total_tax, d.base_net_amount + total_tax, company_currency]
          
          
		data.append(row)
          
          
	return columns, data
          
          

          
def get_columns(additional_table_columns):
          	columns = [
          		_("Item Code") + ":Link/Item:120", _("Item Name") + "::120",
          		_("Item Group") + ":Link/Item Group:100", "Description::150", _("Invoice") + ":Link/Purchase Invoice:120",
          		_("Posting Date") + ":Date:80", _("Supplier") + ":Link/Supplier:120",
          		"Supplier Name::120"
          	]
          
          
	if additional_table_columns:
          		columns += additional_table_columns

For Writing query

github.com

frappe/erpnext/blob/ce42f48bfbe8ec13dc5d0da44b718eb1355091b0/erpnext/accounts/report/item_wise_purchase_register/item_wise_purchase_register.py#L108


      
          		("supplier", " and `tabPurchase Invoice`.supplier = %(supplier)s"),
          		("item_code", " and `tabPurchase Invoice Item`.item_code = %(item_code)s"),
          		("from_date", " and `tabPurchase Invoice`.posting_date>=%(from_date)s"),
          		("to_date", " and `tabPurchase Invoice`.posting_date<=%(to_date)s"),
          		("mode_of_payment", " and ifnull(mode_of_payment, '') = %(mode_of_payment)s")):
          			if filters.get(opts[0]):
          				conditions += opts[1]
          
          
	return conditions
          
          
def get_items(filters, additional_query_columns):
          	conditions = get_conditions(filters)
          	match_conditions = frappe.build_match_conditions("Purchase Invoice")
          
          
	if match_conditions:
          		match_conditions = " and {0} ".format(match_conditions)
          
          
	if additional_query_columns:
          		additional_query_columns = ', ' + ', '.join(additional_query_columns)
          
          
	return frappe.db.sql("""

Ashutosh_Halape · December 10, 2019, 10:25am

I want this in query report and not in .py file.

ROHAN_JAIN1 · December 10, 2019, 10:26am

Okay wait let me see.

Ashutosh_Halape · December 10, 2019, 10:55am

Yes @ROHAN_JAIN1 please help me. Thank you for your replies.

Nahuel_Nso · December 10, 2019, 6:13pm

I think that this is what you want:

	SELECT
		AD.name AS "Doc#:Link/Address:120",
		AD.owner AS "Created By:Link/User:120",
		AD.modified AS "Modified On:Date:120",
	FROM tabAddress AS AD
	WHERE
		DATEDIFF(now(),AD.modified) = 1
UNION ALL
	SELECT
		TR.name AS "Doc#:Link/Tax Rule:120",
		TR.owner AS "Created By:Link/User:120",
		TR.modified AS "Modified On:Date:120"
	FROM `tabTax Rule` AS TR, 
	WHERE
		DATEDIFF(now(),TR.modified) = 1
UNION ALL
	SELECT
		IT.name AS "Doc#:Link/Item:120",
		IT.owner AS "Created By:Link/User:120",
		IT.modified AS "Modified On:Date:120"
	FROM tabItem AS IT
	WHERE
		DATEDIFF(now(),IT.modified) = 1

Ashutosh_Halape · December 11, 2019, 3:56am

@Nahuel_Nso the code you suggested is giving me the same output.Below image shows “Doctype = Address” I want to fetch that name using query report.I want one more column as “Doctype” under which it should mention Address,Tax Rule or Item.How do I add this using query report??

Ashutosh_Halape · December 11, 2019, 6:26am

I have made the following modifications in the query report but not getting the expected output.

SELECT
D.name AS “Doctype:Link/Doctype:120”,
AD.name AS “Doc#:Link/Doctype:120”,
AD.owner AS “Created By:Link/User:120”,
AD.modified AS “Modified On:Date:120”
FROM tabAddress AS AD , tabDocType AS D
WHERE
DATEDIFF(now(),AD.modified) BETWEEN 1 AND 30
UNION ALL
SELECT
D.name AS “Doctype:Link/Doctype:120”,
TR.name AS “Doc#:Link/Doctype:120”,
TR.owner AS “Created By:Link/User:120”,
TR.modified AS “Modified On:Date:120”
FROM tabTax Rule AS TR , tabDocType AS D
WHERE
DATEDIFF(now(),TR.modified) = 1
UNION ALL
SELECT
D.name AS “Doctype:Link/Doctype:120”,
IT.name AS “Doc#:Link/Doctype:120”,
IT.owner AS “Created By:Link/User:120”,
IT.modified AS “Modified On:Date:120”
FROM tabItem AS IT , tabDocType AS D
WHERE
DATEDIFF(now(),IT.modified) = 1

Nahuel_Nso · December 11, 2019, 3:48pm

Why fetch it when you can hardcode it? (?)

	SELECT
		"Address" AS "Doctype:Link/Doctype:120",
		AD.name AS "Doc#:Link/Address:120",
		AD.owner AS "Created By:Link/User:120",
		AD.modified AS "Modified On:Date:120"
	FROM tabAddress AS AD
	WHERE
		DATEDIFF(now(),AD.modified) = 1
UNION ALL
	SELECT
		"Tax Rule" AS "Doctype:Link/Doctype:120",
		TR.name AS "Doc#:Link/Tax Rule:120",
		TR.owner AS "Created By:Link/User:120",
		TR.modified AS "Modified On:Date:120"
	FROM `tabTax Rule` AS TR, 
	WHERE
		DATEDIFF(now(),TR.modified) = 1
UNION ALL
	SELECT
		"Item" AS "Doctype:Link/Doctype:120",
		IT.name AS "Doc#:Link/Item:120",
		IT.owner AS "Created By:Link/User:120",
		IT.modified AS "Modified On:Date:120"
	FROM tabItem AS IT
	WHERE
		DATEDIFF(now(),IT.modified) = 1

Ashutosh_Halape · December 16, 2019, 4:32am

Thank you @Nahuel_Nso it worked.