How to use frappe.db.sql on print format .. is it Possible?

[Rahib_Hasan]

when i am using frappe.db.sql … i am facing a error
anyone help me .

				{%- for row in doc.yarn_description -%}
					
					<tr>
						<td>{{row.idx}}</td>
						<td>{{row.yarn_specification}}</td>
						<td>{{row.qnty_in_kgs}}</td>
						<td>{{row.imp_qnty}}</td>
						<td>{{row.b2b_lc_no}}</td>
						<td>{{row.balance}}</td>
						<td>{% if row.remarks %} {{row.remarks}} {% endif %}</td>
					</tr>
				{%- endfor -%}
	{% set get_sumation=frappe.db.sql("""select sum(`qnty_in_kgs`) as 'qnty_in_kgs', sum(`imp_qnty`) as 'imp_qnty'  , sum(`balance`) as 'balance' from `tabYarn Description child`""") %}
					<tr>
						<td></td>
						<td>Total :-</td>
						<td>{{get_sumation[0]}}</td>
						<td>28080</td>
						<td></td>
						<td>-970</td>
						<td></td>
					</tr>
				</tbody>
			</table>
		</div>

Sl No	Yarn Description	Qnty in Kgs.	B2B L/C no	Mills	Balance	Remark

[johnskywalker]

Hi I can see that you are looping a child table, assuming that this is item table why not do the calculation on custom script?

[Shiv_Shan]

Hi @Rahib_Hasan,

Did you manage to find a solution?, i am also in need of similar workout.

[Rahib_Hasan]

Yes I Found This Solution this way Because frappe.db.sql is not working on print format but frappe.get_all() , get_value(). its working

				{% set qty_in_kgs_total = [0] %}
				{% set balance_total=[0] %}
				{% set imp_qnty=[0] %}

				{%- for row in doc.yarn_description -%}
					
					{% set _ = qty_in_kgs_total.append(qty_in_kgs_total.pop() + row.qnty_in_kgs|int) %}

					{% set _ = balance_total.append(balance_total.pop()+row.balance|int ) %}
					{% set _ =imp_qnty.append(imp_qnty.pop()+row.imp_qnty|int ) %}
					<tr> 
						<td>{{row.idx}}</td>
						<td>{{row.yarn_specification}}</td>
						<td>{{row.qnty_in_kgs}}</td>
						<td>{% if row.imp_qnty %}   {{row.imp_qnty}}  {% endif %}</td>
						<td>{% if row.b2b_lc_no %} {{row.b2b_lc_no}} {% endif %}</td>
						<td>{% if row.mills %} {{row.mills}} {% endif %}</td>
						<td>{{row.balance}}</td>
						<td>{% if row.remarks %} {{row.remarks}} {% endif %}</td>
					</tr>
				{%- endfor -%}

					<tr>
						<td></td>
						<td>Total :-</td>
						<td>{{ qty_in_kgs_total[0] }}</td>
						<td>{{ imp_qnty[0] }}</td>
						<td></td>
						<td></td>
						<td>{{balance_total[0]}}</td>
						<td></td>
					</tr>
				</tbody>
			</table>

Sl No	Yarn Description	Qnty in Kgs.	Imp Qnty.	B2B L/C no	Mills	Balance	Remark

[Shiv_Shan]

Thank you for the update. But i need to use sql with group clause.

[Alkautsar]

@Rahib_Hasan then how can i get value from diferent doctype?

[lokesh]

You can use get_value and get_all as simply you used in .py.
Still if you want to use sql query, you can do this by calling your python function from print format. Just use doc.your_function_name On your .py file you can define this function and return sql query.

frappe.db.sql query i specifically used for a query where i need to use joins. For other purpose we have get_value and get_all.

[Alkautsar]

thank you bro @lokesh