<commit_before># -*- coding: utf-8 -*-
# Copyright (c) 2019, Frappe Technologies Pvt. Ltd. and contributors
# For license information, please see license.txt

from __future__ import unicode_literals
import frappe
from frappe.model.document import Document
import json
from six import iteritems
from frappe.utils import flt, getdate
from erpnext.regional.india import state_numbers

class GSTR3BReport(Document):
	def before_save(self):

		self.get_data()

	def get_data(self):

		self.report_dict = {
			"gstin": "",
			"ret_period": "",
			"inward_sup": {
				"isup_details": [
					{
						"ty": "GST",
						"intra": 0,
						"inter": 0
					},
					{
						"ty": "NONGST",
						"inter": 0,
						"intra": 0
					}
				]
			},
			"sup_details": {
				"osup_zero": {
					"csamt": 0,
					"txval": 0,
					"iamt": 0
				},
				"osup_nil_exmp": {
					"txval": 0
				},
				"osup_det": {
					"samt": 0,
					"csamt": 0,
					"txval": 0,
					"camt": 0,
					"iamt": 0
				},
				"isup_rev": {
					"samt": 0,
					"csamt": 0,
					"txval": 0,
					"camt": 0,
					"iamt": 0
				},
				"osup_nongst": {
					"txval": 0,
				}
			},
			"inter_sup": {
				"unreg_details": [],
				"comp_details": [],
				"uin_details": []
			},
			"itc_elg": {
				"itc_avl": [
					{
						"csamt": 0,
						"samt": 0,
						"ty": "IMPG",
						"camt": 0,
						"iamt": 0
					},
					{
						"csamt": 0,
						"samt": 0,
						"ty": "IMPS",
						"camt": 0,
						"iamt": 0
					},
					{
						"samt": 0,
						"csamt": 0,
						"ty": "ISRC",
						"camt": 0,
						"iamt": 0
					},
					{
						"ty": "ISD",
						"iamt": 1,
						"camt": 1,
						"samt": 1,
						"csamt": 1
					},
					{
						"samt": 0,
						"csamt": 0,
						"ty": "OTH",
						"camt": 0,
						"iamt": 0
					}
				],
				"itc_net": {
					"samt": 0,
					"csamt": 0,
					"camt": 0,
					"iamt": 0
				},
				"itc_inelg": [
					{
						"ty": "RUL",
						"iamt": 0,
						"camt": 0,
						"samt": 0,
						"csamt": 0
					},
					{
						"ty": "OTH",
						"iamt": 0,
						"camt": 0,
						"samt": 0,
						"csamt": 0
					}
				]
			}
		}

		self.gst_details = self.get_company_gst_details()
		self.report_dict["gstin"] = self.gst_details.get("gstin")
		self.report_dict["ret_period"] = get_period(self.month, self.year)
		self.month_no = get_period(self.month)
		self.account_heads = self.get_account_heads()

		outward_supply_tax_amounts = self.get_tax_amounts("Sales Invoice")
		inward_supply_tax_amounts = self.get_tax_amounts("Purchase Invoice", reverse_charge="Y")
		itc_details = self.get_itc_details()

		self.prepare_data("Sales Invoice", outward_supply_tax_amounts, "sup_details", "osup_det", ["Registered Regular"])
		self.prepare_data("Sales Invoice", outward_supply_tax_amounts, "sup_details", "osup_zero", ["SEZ", "Deemed Export", "Overseas"])
		self.prepare_data("Purchase Invoice", inward_supply_tax_amounts, "sup_details", "isup_rev", ["Registered Regular"], reverse_charge="Y")
		self.report_dict["sup_details"]["osup_nil_exmp"]["txval"] = flt(self.get_nil_rated_supply_value(), 2)
		self.set_itc_details(itc_details)

		inter_state_supplies = self.get_inter_state_supplies(self.gst_details.get("gst_state_number"))
		inward_nil_exempt = self.get_inward_nil_exempt(self.gst_details.get("gst_state"))
		self.set_inter_state_supply(inter_state_supplies)
		self.set_inward_nil_exempt(inward_nil_exempt)

		self.missing_field_invoices = self.get_missing_field_invoices()

		self.json_output = frappe.as_json(self.report_dict)

	def set_inward_nil_exempt(self, inward_nil_exempt):

		self.report_dict["inward_sup"]["isup_details"][0]["inter"] = flt(inward_nil_exempt.get("gst").get("inter"), 2)
		self.report_dict["inward_sup"]["isup_details"][0]["intra"] = flt(inward_nil_exempt.get("gst").get("intra"), 2)
		self.report_dict["inward_sup"]["isup_details"][1]["inter"] = flt(inward_nil_exempt.get("non_gst").get("inter"), 2)
		self.report_dict["inward_sup"]["isup_details"][1]["intra"] = flt(inward_nil_exempt.get("non_gst").get("intra"), 2)

	def set_itc_details(self, itc_details):

		itc_type_map = {
			'IMPG': 'Import Of Capital Goods',
			'IMPS': 'Import Of Service',
			'ISD': 'Input Service Distributor',
			'OTH': 'All Other ITC'
		}

		net_itc = self.report_dict["itc_elg"]["itc_net"]

		for d in self.report_dict["itc_elg"]["itc_avl"]:
			if d["ty"] == 'ISRC':
				reverse_charge = "Y"
			else:
				reverse_charge = "N"

			for account_head in self.account_heads:

				d["iamt"] = flt(itc_details.get((itc_type_map.get(d["ty"]), reverse_charge, account_head.get('igst_account')), {}).get("amount"), 2)
				net_itc["iamt"] += flt(d["iamt"], 2)

				d["camt"] = flt(itc_details.get((itc_type_map.get(d["ty"]), reverse_charge, account_head.get('cgst_account')), {}).get("amount"), 2)
				net_itc["camt"] += flt(d["camt"], 2)

				d["samt"] = flt(itc_details.get((itc_type_map.get(d["ty"]), reverse_charge, account_head.get('sgst_account')), {}).get("amount"), 2)
				net_itc["samt"] += flt(d["samt"], 2)

				d["csamt"] = flt(itc_details.get((itc_type_map.get(d["ty"]), reverse_charge, account_head.get('cess_account')), {}).get("amount"), 2)
				net_itc["csamt"] += flt(d["csamt"], 2)

		for account_head in self.account_heads:

			self.report_dict["itc_elg"]["itc_inelg"][1]["iamt"] = flt(itc_details.get(("Ineligible", "N", account_head.get("igst_account")), {}).get("amount"), 2)
			self.report_dict["itc_elg"]["itc_inelg"][1]["camt"] = flt(itc_details.get(("Ineligible", "N", account_head.get("cgst_account")), {}).get("amount"), 2)
			self.report_dict["itc_elg"]["itc_inelg"][1]["samt"] = flt(itc_details.get(("Ineligible", "N", account_head.get("sgst_account")), {}).get("amount"), 2)
			self.report_dict["itc_elg"]["itc_inelg"][1]["csamt"] = flt(itc_details.get(("Ineligible", "N", account_head.get("cess_account")), {}).get("amount"), 2)

	def prepare_data(self, doctype, tax_details, supply_type, supply_category, gst_category_list, reverse_charge="N"):

		account_map = {
			'sgst_account': 'samt',
			'cess_account': 'csamt',
			'cgst_account': 'camt',
			'igst_account': 'iamt'
		}

		txval = 0
		total_taxable_value = self.get_total_taxable_value(doctype, reverse_charge)

		for gst_category in gst_category_list:
			txval += total_taxable_value.get(gst_category,0)
			for account_head in self.account_heads:
				for account_type, account_name in iteritems(account_head):
					if account_map.get(account_type) in self.report_dict.get(supply_type).get(supply_category):
						self.report_dict[supply_type][supply_category][account_map.get(account_type)] += \
							flt(tax_details.get((account_name, gst_category), {}).get("amount"), 2)

		for k, v in iteritems(account_map):
			txval -= self.report_dict.get(supply_type, {}).get(supply_category, {}).get(v, 0)

		self.report_dict[supply_type][supply_category]["txval"] += flt(txval, 2)

	def set_inter_state_supply(self, inter_state_supply):

		for d in inter_state_supply.get("Unregistered", []):
			self.report_dict["inter_sup"]["unreg_details"].append(d)
			self.report_dict["sup_details"]["osup_det"]["txval"] += flt(d["txval"], 2)
			self.report_dict["sup_details"]["osup_det"]["iamt"] += flt(d["iamt"], 2)

		for d in inter_state_supply.get("Registered Composition", []):
			self.report_dict["inter_sup"]["comp_details"].append(d)
			self.report_dict["sup_details"]["osup_det"]["txval"] += flt(d["txval"], 2)
			self.report_dict["sup_details"]["osup_det"]["iamt"] += flt(d["iamt"], 2)

		for d in inter_state_supply.get("UIN Holders", []):
			self.report_dict["inter_sup"]["uin_details"].append(d)
			self.report_dict["sup_details"]["osup_det"]["txval"] += flt(d["txval"], 2)
			self.report_dict["sup_details"]["osup_det"]["iamt"] += flt(d["iamt"], 2)

	def get_total_taxable_value(self, doctype, reverse_charge):

		return frappe._dict(frappe.db.sql("""
			select gst_category, sum(base_grand_total) as total
			from `tab{doctype}`
			where docstatus = 1 and month(posting_date) = %s
			and year(posting_date) = %s and reverse_charge = %s
			and company = %s and company_gstin = %s
			group by gst_category
			""" #nosec
			.format(doctype = doctype), (self.month_no, self.year, reverse_charge, self.company, self.gst_details.get("gstin"))))

	def get_itc_details(self, reverse_charge='N'):

		itc_amount = frappe.db.sql("""
			select s.gst_category, sum(t.tax_amount) as tax_amount, t.account_head, s.eligibility_for_itc, s.reverse_charge
			from `tabPurchase Invoice` s , `tabPurchase Taxes and Charges` t
			where s.docstatus = 1 and t.parent = s.name and s.reverse_charge = %s
			and month(s.posting_date) = %s and year(s.posting_date) = %s and s.company = %s
			and s.company_gstin = %s
			group by t.account_head, s.gst_category, s.eligibility_for_itc
			""",
			(reverse_charge, self.month_no, self.year, self.company, self.gst_details.get("gstin")), as_dict=1)

		itc_details = {}

		for d in itc_amount:
			itc_details.setdefault((d.eligibility_for_itc, d.reverse_charge, d.account_head),{
				"amount": d.tax_amount
			})

		return itc_details

	def get_nil_rated_supply_value(self):

		return frappe.db.sql("""
			select sum(i.base_amount) as total from
			`tabSales Invoice Item` i, `tabSales Invoice` s
			where s.docstatus = 1 and i.parent = s.name and i.is_nil_exempt = 1
			and month(s.posting_date) = %s and year(s.posting_date) = %s
			and s.company = %s and s.company_gstin = %s""",
			(self.month_no, self.year, self.company, self.gst_details.get("gstin")), as_dict=1)[0].total

	def get_inter_state_supplies(self, state_number):

		inter_state_supply = frappe.db.sql(""" select sum(s.net_total) as total, sum(t.tax_amount) as tax_amount, s.place_of_supply, s.gst_category
			from `tabSales Invoice` s, `tabSales Taxes and Charges` t
			where t.parent = s.name and s.docstatus = 1 and month(s.posting_date) = %s and year(s.posting_date) = %s
			and s.company = %s and s.company_gstin = %s and s.gst_category in ('Unregistered', 'Registered Composition', 'UIN Holders')
			group by s.gst_category, s.place_of_supply""", (self.month_no, self.year, self.company, self.gst_details.get("gstin")), as_dict=1, debug=1)

		inter_state_supply_details = {}

		for d in inter_state_supply:
			inter_state_supply_details.setdefault(
				d.gst_category, []
			)

			if state_number != d.place_of_supply.split("-")[0]:
				inter_state_supply_details[d.gst_category].append({
					"pos": d.place_of_supply,
					"txval": d.total,
					"iamt": d.tax_amount
				})
			else:
				self.report_dict["sup_details"]["osup_det"]["txval"] += flt(d.total/2, 2)
				self.report_dict["sup_details"]["osup_det"]["camt"] += flt(d.tax_amount/2, 2)
				self.report_dict["sup_details"]["osup_det"]["samt"] += flt(d.tax_amount/2, 2)

		return inter_state_supply_details

	def get_inward_nil_exempt(self, state):

		inward_nil_exempt = frappe.db.sql(""" select a.gst_state, sum(i.base_amount) as base_amount,
			i.is_nil_exempt, i.is_non_gst from `tabPurchase Invoice` p , `tabPurchase Invoice Item` i, `tabAddress` a
			where p.docstatus = 1 and p.name = i.parent and p.supplier_address = a.name
			and i.is_nil_exempt = 1 or i.is_non_gst = 1 and
			month(p.posting_date) = %s and year(p.posting_date) = %s and p.company = %s and p.company_gstin = %s
			group by a.gst_state """, (self.month_no, self.year, self.company, self.gst_details.get("gstin")), as_dict=1)

		inward_nil_exempt_details = {
			"gst": {
				"intra": 0.0,
				"inter": 0.0
			},
			"non_gst": {
				"intra": 0.0,
				"inter": 0.0<commit_msg>fix: Seperate queries for Taxable value and tax amounts<commit_after># -*- coding: utf-8 -*-
# Copyright (c) 2019, Frappe Technologies Pvt. Ltd. and contributors
# For license information, please see license.txt

from __future__ import unicode_literals
import frappe
from frappe.model.document import Document
import json
from six import iteritems
from frappe.utils import flt, getdate
from erpnext.regional.india import state_numbers

class GSTR3BReport(Document):
	def before_save(self):

		self.get_data()

	def get_data(self):

		self.report_dict = {
			"gstin": "",
			"ret_period": "",
			"inward_sup": {
				"isup_details": [
					{
						"ty": "GST",
						"intra": 0,
						"inter": 0
					},
					{
						"ty": "NONGST",
						"inter": 0,
						"intra": 0
					}
				]
			},
			"sup_details": {
				"osup_zero": {
					"csamt": 0,
					"txval": 0,
					"iamt": 0
				},
				"osup_nil_exmp": {
					"txval": 0
				},
				"osup_det": {
					"samt": 0,
					"csamt": 0,
					"txval": 0,
					"camt": 0,
					"iamt": 0
				},
				"isup_rev": {
					"samt": 0,
					"csamt": 0,
					"txval": 0,
					"camt": 0,
					"iamt": 0
				},
				"osup_nongst": {
					"txval": 0,
				}
			},
			"inter_sup": {
				"unreg_details": [],
				"comp_details": [],
				"uin_details": []
			},
			"itc_elg": {
				"itc_avl": [
					{
						"csamt": 0,
						"samt": 0,
						"ty": "IMPG",
						"camt": 0,
						"iamt": 0
					},
					{
						"csamt": 0,
						"samt": 0,
						"ty": "IMPS",
						"camt": 0,
						"iamt": 0
					},
					{
						"samt": 0,
						"csamt": 0,
						"ty": "ISRC",
						"camt": 0,
						"iamt": 0
					},
					{
						"ty": "ISD",
						"iamt": 1,
						"camt": 1,
						"samt": 1,
						"csamt": 1
					},
					{
						"samt": 0,
						"csamt": 0,
						"ty": "OTH",
						"camt": 0,
						"iamt": 0
					}
				],
				"itc_net": {
					"samt": 0,
					"csamt": 0,
					"camt": 0,
					"iamt": 0
				},
				"itc_inelg": [
					{
						"ty": "RUL",
						"iamt": 0,
						"camt": 0,
						"samt": 0,
						"csamt": 0
					},
					{
						"ty": "OTH",
						"iamt": 0,
						"camt": 0,
						"samt": 0,
						"csamt": 0
					}
				]
			}
		}

		self.gst_details = self.get_company_gst_details()
		self.report_dict["gstin"] = self.gst_details.get("gstin")
		self.report_dict["ret_period"] = get_period(self.month, self.year)
		self.month_no = get_period(self.month)
		self.account_heads = self.get_account_heads()

		outward_supply_tax_amounts = self.get_tax_amounts("Sales Invoice")
		inward_supply_tax_amounts = self.get_tax_amounts("Purchase Invoice", reverse_charge="Y")
		itc_details = self.get_itc_details()

		self.prepare_data("Sales Invoice", outward_supply_tax_amounts, "sup_details", "osup_det", ["Registered Regular"])
		self.prepare_data("Sales Invoice", outward_supply_tax_amounts, "sup_details", "osup_zero", ["SEZ", "Deemed Export", "Overseas"])
		self.prepare_data("Purchase Invoice", inward_supply_tax_amounts, "sup_details", "isup_rev", ["Registered Regular"], reverse_charge="Y")
		self.report_dict["sup_details"]["osup_nil_exmp"]["txval"] = flt(self.get_nil_rated_supply_value(), 2)
		self.set_itc_details(itc_details)

		inter_state_supplies = self.get_inter_state_supplies(self.gst_details.get("gst_state_number"))
		inward_nil_exempt = self.get_inward_nil_exempt(self.gst_details.get("gst_state"))
		self.set_inter_state_supply(inter_state_supplies)
		self.set_inward_nil_exempt(inward_nil_exempt)

		self.missing_field_invoices = self.get_missing_field_invoices()

		self.json_output = frappe.as_json(self.report_dict)

	def set_inward_nil_exempt(self, inward_nil_exempt):

		self.report_dict["inward_sup"]["isup_details"][0]["inter"] = flt(inward_nil_exempt.get("gst").get("inter"), 2)
		self.report_dict["inward_sup"]["isup_details"][0]["intra"] = flt(inward_nil_exempt.get("gst").get("intra"), 2)
		self.report_dict["inward_sup"]["isup_details"][1]["inter"] = flt(inward_nil_exempt.get("non_gst").get("inter"), 2)
		self.report_dict["inward_sup"]["isup_details"][1]["intra"] = flt(inward_nil_exempt.get("non_gst").get("intra"), 2)

	def set_itc_details(self, itc_details):

		itc_type_map = {
			'IMPG': 'Import Of Capital Goods',
			'IMPS': 'Import Of Service',
			'ISD': 'Input Service Distributor',
			'OTH': 'All Other ITC'
		}

		net_itc = self.report_dict["itc_elg"]["itc_net"]

		for d in self.report_dict["itc_elg"]["itc_avl"]:
			if d["ty"] == 'ISRC':
				reverse_charge = "Y"
			else:
				reverse_charge = "N"

			for account_head in self.account_heads:

				d["iamt"] = flt(itc_details.get((itc_type_map.get(d["ty"]), reverse_charge, account_head.get('igst_account')), {}).get("amount"), 2)
				net_itc["iamt"] += flt(d["iamt"], 2)

				d["camt"] = flt(itc_details.get((itc_type_map.get(d["ty"]), reverse_charge, account_head.get('cgst_account')), {}).get("amount"), 2)
				net_itc["camt"] += flt(d["camt"], 2)

				d["samt"] = flt(itc_details.get((itc_type_map.get(d["ty"]), reverse_charge, account_head.get('sgst_account')), {}).get("amount"), 2)
				net_itc["samt"] += flt(d["samt"], 2)

				d["csamt"] = flt(itc_details.get((itc_type_map.get(d["ty"]), reverse_charge, account_head.get('cess_account')), {}).get("amount"), 2)
				net_itc["csamt"] += flt(d["csamt"], 2)

		for account_head in self.account_heads:

			self.report_dict["itc_elg"]["itc_inelg"][1]["iamt"] = flt(itc_details.get(("Ineligible", "N", account_head.get("igst_account")), {}).get("amount"), 2)
			self.report_dict["itc_elg"]["itc_inelg"][1]["camt"] = flt(itc_details.get(("Ineligible", "N", account_head.get("cgst_account")), {}).get("amount"), 2)
			self.report_dict["itc_elg"]["itc_inelg"][1]["samt"] = flt(itc_details.get(("Ineligible", "N", account_head.get("sgst_account")), {}).get("amount"), 2)
			self.report_dict["itc_elg"]["itc_inelg"][1]["csamt"] = flt(itc_details.get(("Ineligible", "N", account_head.get("cess_account")), {}).get("amount"), 2)

	def prepare_data(self, doctype, tax_details, supply_type, supply_category, gst_category_list, reverse_charge="N"):

		account_map = {
			'sgst_account': 'samt',
			'cess_account': 'csamt',
			'cgst_account': 'camt',
			'igst_account': 'iamt'
		}

		txval = 0
		total_taxable_value = self.get_total_taxable_value(doctype, reverse_charge)

		for gst_category in gst_category_list:
			txval += total_taxable_value.get(gst_category,0)
			for account_head in self.account_heads:
				for account_type, account_name in iteritems(account_head):
					if account_map.get(account_type) in self.report_dict.get(supply_type).get(supply_category):
						self.report_dict[supply_type][supply_category][account_map.get(account_type)] += \
							flt(tax_details.get((account_name, gst_category), {}).get("amount"), 2)

		for k, v in iteritems(account_map):
			txval -= self.report_dict.get(supply_type, {}).get(supply_category, {}).get(v, 0)

		self.report_dict[supply_type][supply_category]["txval"] += flt(txval, 2)

	def set_inter_state_supply(self, inter_state_supply):

		for d in inter_state_supply.get("Unregistered", []):
			self.report_dict["inter_sup"]["unreg_details"].append(d)
			self.report_dict["sup_details"]["osup_det"]["txval"] += flt(d["txval"], 2)
			self.report_dict["sup_details"]["osup_det"]["iamt"] += flt(d["iamt"], 2)

		for d in inter_state_supply.get("Registered Composition", []):
			self.report_dict["inter_sup"]["comp_details"].append(d)
			self.report_dict["sup_details"]["osup_det"]["txval"] += flt(d["txval"], 2)
			self.report_dict["sup_details"]["osup_det"]["iamt"] += flt(d["iamt"], 2)

		for d in inter_state_supply.get("UIN Holders", []):
			self.report_dict["inter_sup"]["uin_details"].append(d)
			self.report_dict["sup_details"]["osup_det"]["txval"] += flt(d["txval"], 2)
			self.report_dict["sup_details"]["osup_det"]["iamt"] += flt(d["iamt"], 2)

	def get_total_taxable_value(self, doctype, reverse_charge):

		return frappe._dict(frappe.db.sql("""
			select gst_category, sum(base_grand_total) as total
			from `tab{doctype}`
			where docstatus = 1 and month(posting_date) = %s
			and year(posting_date) = %s and reverse_charge = %s
			and company = %s and company_gstin = %s
			group by gst_category
			""" #nosec
			.format(doctype = doctype), (self.month_no, self.year, reverse_charge, self.company, self.gst_details.get("gstin"))))

	def get_itc_details(self, reverse_charge='N'):

		itc_amount = frappe.db.sql("""
			select s.gst_category, sum(t.tax_amount) as tax_amount, t.account_head, s.eligibility_for_itc, s.reverse_charge
			from `tabPurchase Invoice` s , `tabPurchase Taxes and Charges` t
			where s.docstatus = 1 and t.parent = s.name and s.reverse_charge = %s
			and month(s.posting_date) = %s and year(s.posting_date) = %s and s.company = %s
			and s.company_gstin = %s
			group by t.account_head, s.gst_category, s.eligibility_for_itc
			""",
			(reverse_charge, self.month_no, self.year, self.company, self.gst_details.get("gstin")), as_dict=1)

		itc_details = {}

		for d in itc_amount:
			itc_details.setdefault((d.eligibility_for_itc, d.reverse_charge, d.account_head),{
				"amount": d.tax_amount
			})

		return itc_details

	def get_nil_rated_supply_value(self):

		return frappe.db.sql("""
			select sum(i.base_amount) as total from
			`tabSales Invoice Item` i, `tabSales Invoice` s
			where s.docstatus = 1 and i.parent = s.name and i.is_nil_exempt = 1
			and month(s.posting_date) = %s and year(s.posting_date) = %s
			and s.company = %s and s.company_gstin = %s""",
			(self.month_no, self.year, self.company, self.gst_details.get("gstin")), as_dict=1)[0].total

	def get_inter_state_supplies(self, state_number):

		inter_state_supply_taxable_value = frappe.db.sql(""" select sum(s.net_total) as total, s.place_of_supply, s.gst_category
			from `tabSales Invoice` s where s.docstatus = 1 and month(s.posting_date) = %s and year(s.posting_date) = %s
			and s.company = %s and s.company_gstin = %s and s.gst_category in ('Unregistered', 'Registered Composition', 'UIN Holders')
			group by s.gst_category, s.place_of_supply""", (self.month_no, self.year, self.company, self.gst_details.get("gstin")), as_dict=1)

		inter_state_supply_tax = frappe.db.sql(""" select sum(t.tax_amount) as tax_amount, s.place_of_supply, s.gst_category
			from `tabSales Invoice` s, `tabSales Taxes and Charges` t
			where t.parent = s.name and s.docstatus = 1 and month(s.posting_date) = %s and year(s.posting_date) = %s
			and s.company = %s and s.company_gstin = %s and s.gst_category in ('Unregistered', 'Registered Composition', 'UIN Holders')
			group by s.gst_category, s.place_of_supply""", (self.month_no, self.year, self.company, self.gst_details.get("gstin")), as_dict=1)

		inter_state_supply_tax_mapping={}
		inter_state_supply_details = {}

		for d in inter_state_supply_tax:
			inter_state_supply_tax_mapping.setdefault(d.place_of_supply, d.tax_amount)

		for d in inter_state_supply_taxable_value:
			inter_state_supply_details.setdefault(
				d.gst_category, []
			)

			if state_number != d.place_of_supply.split("-")[0]:
				inter_state_supply_details[d.gst_category].append({
					"pos": d.place_of_supply,
					"txval": flt(d.total, 2),
					"iamt": flt(inter_state_supply_tax_mapping.get(d.place_of_supply), 2)
				})
			else:
				self.report_dict["sup_details"]["osup_det"]["txval"] += flt(d.total, 2)
				self.report_dict["sup_details"]["osup_det"]["camt"] += flt(inter_state_supply_tax_mapping.get(d.place_of_supply)/2, 2)
				self.report_dict["sup_details"]["osup_det"]["samt"] += flt(inter_state_supply_tax_mapping.get(d.place_of_supply)/2, 2)

		return inter_state_supply_details

	def get_inward_nil_exempt(self, state):

		inward_nil_exempt = frappe.db.sql(""" select a.gst_state, sum(i.base_amount) as base_amount,
			i.is_nil_exempt, i.is_non_gst from `tabPurchase Invoice` p , `tabPurchase Invoice Item` i, `tabAddress` a
			where p.docstatus = 1 and p.name = i.parent and p.supplier_address = a.name
			and i.is_nil_exempt = 1 or i.is_non_gst = 1 and
			month(p.posting_date) = %s and year(p.posting_date) = %s and p.company = %s and p.company_gstin = %s
			group by a.gst_state """, (self.month_no, self.year, self.company, self.gst_details.get("gstin")), as_dict=1)

		inward_nil_exempt_details = {
			"gst": {
				"intra": 0.0,
				"inter": 0.0
			},
			"non_gst": {
				"intra": 0.0,
				"inter": 0.0
{'fraction_non_alphanumeric': 0.17290507502950597, 'fraction_numerical': 0.010369246332827516, 'mean_word_length': 4.205133830627468, 'pattern_counts': {'":': 176, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4889696', 'n_tokens_mistral': 11231, 'n_tokens_neox': 9569, 'n_words': 1592}