{
"actions": [
{
"action": "documents.report",
"name": "EXPORT",
"params": {
"template": "\n\n <!DOCTYPE html>\n <table>\n {{#delivery_instance.documents}}\n <tr>\n <td>Водитель</td>\n <td>{{driver_name}}</td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n </tr>\n\n <tr>\n <td>Дата доставки</td>\n <td>{{delivery_date}}</td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n </tr>\n\n <tr>\n <td>Номер ТС</td>\n <td>{{vehicle_number}}</td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n </tr>\n {{/delivery_instance.documents}}\n\n <tr>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n </tr>\n\n <tr>\n <td>Дата доставки</td>\n <td>Менеджер</td>\n <td>Согласованно</td>\n <td>Клиент</td>\n <td>Адрес доставки</td>\n <td>Комментарий к адресу</td>\n <td>Наименование продукта</td>\n <td>Тип тары</td>\n <td>Количество ведер</td>\n <td>Количество кг</td>\n <td>Цена за кг, руб</td>\n <td>Сумма к оплате, руб</td>\n <td>Условия оплаты</td>\n <td>Примечания</td>\n </tr>\n\n {{#deals.documents}}\n <tr>\n <td>{{expected_delivery_date}}</td>\n <td>{{manager_name}}</td>\n <td>{{confirmed_at}}</td>\n <td>{{client_name}}</td>\n <td>{{point_address}}</td>\n <td>{{point_comment}}</td>\n <td>{{product_name}}</td>\n <td>{{product_variant_name}}</td>\n <td>{{b_quantity}}</td>\n <td>{{weight}}</td>\n <td>{{#special_price}}{{.}}{{/special_price}}{{^special_price}}{{../b_price}}{{/special_price}}</td>\n <td>{{cost}}</td>\n <td>{{payment_type_name}}</td>\n <td>{{notes}}</td>\n </tr>\n {{/deals.documents}}\n\n {{#deals_sum.documents}}\n <tr>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td>{{total_quantity}}</td>\n <td>{{total_weight}}</td>\n <td></td>\n <td>{{total_sum}}</td>\n <td></td>\n <td></td>\n </tr>\n {{/deals_sum.documents}}\n\n <tr>\n <td colspan='14'></td>\n </tr>\n\n <tr>\n <td colspan='14'>Продукты</td>\n </tr>\n\n <tr>\n <td>Продукт</td>\n <td>Тара</td>\n <td>Всего, шт</td>\n <td>Всего, кг</td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n </tr>\n\n {{#products.documents}}\n {{#containers}}\n <tr>\n <td>{{product_name}}</td>\n <td>{{container_name}}</td>\n <td>{{container_count}}</td>\n <td>{{container_weight}}</td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n </tr>\n {{/containers}}\n\n <tr>\n <td></td>\n <td></td>\n <td>{{container_count_sum}}</td>\n <td>{{container_weight_sum}}</td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n </tr>\n\n <tr>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n </tr>\n {{/products.documents}}\n\n <tr>\n <td colspan='14'>Товарный запас</td>\n </tr>\n\n <tr>\n <td>Продукт</td>\n <td>Тара</td>\n <td>Всего, шт</td>\n <td>Всего, кг</td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n </tr>\n\n {{#delivery_instance_product_reserves.documents}}\n {{#containers}}\n <tr>\n <td>{{product_name}}</td>\n <td>{{container_name}}</td>\n <td>{{container_count}}</td>\n <td>{{container_weight}}</td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n </tr>\n {{/containers}}\n\n <tr>\n <td></td>\n <td></td>\n <td>{{container_count_sum}}</td>\n <td>{{container_weight_sum}}</td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n </tr>\n\n <tr>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n </tr>\n {{/delivery_instance_product_reserves.documents}}\n\n <tr><td colspan='14'></td></tr>\n <tr>\n <td colspan='14'>Оплаты</td>\n </tr>\n\n <tr>\n <td>Тип</td>\n <td>Сумма</td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n </tr>\n {{#payments.documents}}\n <tr>\n <td>{{type_name}}</td>\n <td>{{amount}}</td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n <td></td>\n </tr>\n {{/payments.documents}}\n </table>\n ",
"queries": {
"delivery_instance": {
"columns": {
"id": "delivery_instances.id",
"driver_name": "drivers.name",
"vehicle_number": "vehicles.number",
"delivery_date": "delivery_instances.delivery_date"
},
"from": "s:delivery_instances",
"from_as": "delivery_instances",
"joins": [
{
"join": "s:employees",
"type": "left",
"as": "drivers",
"on": {
"$eq": [
"drivers.id",
"delivery_instances.driver_id"
]
}
},
{
"join": "s:vehicles",
"type": "left",
"as": "vehicles",
"on": {
"$eq": [
"vehicles.id",
"delivery_instances.vehicle_id"
]
}
}
],
"where": {
"$in": [
"delivery_instances.id",
{
"$": [
"d:delivery_instances:21",
"d:delivery_instances:22"
]
}
]
}
},
"deals": {
"columns": {
"id": "deal.id",
"client_name": "client.name",
"manager_name": "manager.name",
"expected_delivery_date": "deal.expected_delivery_date",
"confirmed_at": "deal.confirmed_at",
"point_address": "point.address",
"point_comment": "point.comment",
"payment_type_name": "ptype.name",
"product_name": "p.name",
"product_variant_name": "pc.name",
"b_quantity": "dcp.container_quantity",
"weight": {
"$mul": [
"pc.weight",
"dcp.container_quantity"
]
},
"b_price": "pc.price",
"special_price": "personal_settings.container_price",
"cost": "dcp.container_price",
"notes": "dcp.notes"
},
"from": "s:deals",
"from_as": "deal",
"joins": [
{
"join": "s:delivery_instance_deals",
"as": "delivery_instance_deals",
"on": {
"$eq": [
"delivery_instance_deals.deal_id",
"deal.id"
]
}
},
{
"join": "s:employees",
"type": "left",
"as": "manager",
"on": {
"$eq": [
"manager.id",
"deal.manager_id"
]
}
},
{
"join": "s:clients",
"type": "left",
"as": "client",
"on": {
"$eq": [
"client.id",
"deal.client_id"
]
}
},
{
"join": "s:payment_deferment_periods",
"type": "left",
"as": "pdp",
"on": {
"$eq": [
"pdp.id",
"client.payment_deferment_period_id"
]
}
},
{
"join": "s:payment_types",
"type": "left",
"as": "ptype",
"on": {
"$eq": [
"deal.payment_type_id",
"ptype.id"
]
}
},
{
"join": "s:delivery_points",
"type": "left",
"as": "point",
"on": {
"$eq": [
"deal.delivery_point_id",
"point.id"
]
}
},
{
"join": "s:cities",
"type": "left",
"as": "city",
"on": {
"$eq": [
"point.city_id",
"city.id"
]
}
},
{
"join": "s:warehouses",
"type": "left",
"as": "warehouse",
"on": {
"$eq": [
"deal.delivery_warehouse_id",
"warehouse.id"
]
}
},
{
"join": "s:deal_product_containers",
"type": "left",
"as": "dcp",
"on": {
"$eq": [
"dcp.deal_id",
"deal.id"
]
}
},
{
"join": "s:product_containers",
"type": "left",
"as": "pc",
"on": {
"$eq": [
"pc.id",
"dcp.product_container_id"
]
}
},
{
"join": "s:products",
"type": "left",
"as": "p",
"on": {
"$eq": [
"p.id",
"pc.product_id"
]
}
},
{
"join": "s:client_product_containers",
"type": "left",
"as": "personal_settings",
"on": {
"$and": [
{
"$eq": [
"personal_settings.product_container_id",
"pc.id"
]
},
{
"$eq": [
"personal_settings.client_id",
"client.id"
]
}
]
}
}
],
"order": [
{
"$asc": [
"delivery_instance_deals.position"
]
}
],
"where": {
"$in": [
"delivery_instance_deals.delivery_instance_id",
{
"$": [
"d:delivery_instances:21",
"d:delivery_instances:22"
]
}
]
}
},
"deals_sum": {
"columns": {
"total_weight": {
"$sum": {
"$mul": [
"pc.weight",
"dcp.container_quantity"
]
}
},
"total_sum": {
"$sum": "dcp.container_price"
},
"total_quantity": {
"$sum": "dcp.container_quantity"
}
},
"from": "s:deals",
"from_as": "deal",
"joins": [
{
"join": "s:delivery_instance_deals",
"as": "delivery_instance_deals",
"on": {
"$eq": [
"delivery_instance_deals.deal_id",
"deal.id"
]
}
},
{
"join": "s:employees",
"type": "left",
"as": "manager",
"on": {
"$eq": [
"manager.id",
"deal.manager_id"
]
}
},
{
"join": "s:clients",
"type": "left",
"as": "client",
"on": {
"$eq": [
"client.id",
"deal.client_id"
]
}
},
{
"join": "s:payment_deferment_periods",
"type": "left",
"as": "pdp",
"on": {
"$eq": [
"pdp.id",
"client.payment_deferment_period_id"
]
}
},
{
"join": "s:payment_types",
"type": "left",
"as": "ptype",
"on": {
"$eq": [
"deal.payment_type_id",
"ptype.id"
]
}
},
{
"join": "s:delivery_points",
"type": "left",
"as": "point",
"on": {
"$eq": [
"deal.delivery_point_id",
"point.id"
]
}
},
{
"join": "s:cities",
"type": "left",
"as": "city",
"on": {
"$eq": [
"point.city_id",
"city.id"
]
}
},
{
"join": "s:deal_product_containers",
"type": "left",
"as": "dcp",
"on": {
"$eq": [
"dcp.deal_id",
"deal.id"
]
}
},
{
"join": "s:product_containers",
"type": "left",
"as": "pc",
"on": {
"$eq": [
"pc.id",
"dcp.product_container_id"
]
}
},
{
"join": "s:products",
"type": "left",
"as": "p",
"on": {
"$eq": [
"p.id",
"pc.product_id"
]
}
}
],
"where": {
"$in": [
"delivery_instance_deals.delivery_instance_id",
{
"$": [
"d:delivery_instances:21",
"d:delivery_instances:22"
]
}
]
}
},
"products": {
"columns": {
"product_id": "containers.product_id",
"containers": {
"$array_agg": {
"$obj": {
"product_name": "containers.product_name",
"container_name": "containers.name",
"container_count": "containers.count",
"container_weight": "containers.weight"
}
}
},
"container_count_sum": {
"$sum": "containers.count"
},
"container_weight_sum": {
"$sum": "containers.weight"
}
},
"from": {
"columns": {
"product_id": "p.id",
"product_name": "p.name",
"name": "pc.name",
"count": {
"$sum": "dpc.container_quantity"
},
"weight": {
"$sum": {
"$mul": [
"pc.weight",
"dpc.container_quantity"
]
}
}
},
"from": "s:deal_product_containers",
"from_as": "dpc",
"joins": [
{
"join": "s:product_containers",
"as": "pc",
"on": {
"$eq": [
"pc.id",
"dpc.product_container_id"
]
}
},
{
"join": "s:products",
"as": "p",
"on": {
"$eq": [
"p.id",
"pc.product_id"
]
}
}
],
"where": {
"$in": [
"dpc.deal_id",
{
"columns": {
"id": "deal.id"
},
"from": "s:deals",
"from_as": "deal",
"joins": [
{
"join": "s:delivery_instance_deals",
"as": "delivery_instance_deals",
"on": {
"$eq": [
"delivery_instance_deals.deal_id",
"deal.id"
]
}
},
{
"join": "s:delivery_points",
"type": "left",
"as": "point",
"on": {
"$eq": [
"deal.delivery_point_id",
"point.id"
]
}
},
{
"join": "s:cities",
"type": "left",
"as": "city",
"on": {
"$eq": [
"point.city_id",
"city.id"
]
}
}
],
"where": {
"$in": [
"delivery_instance_deals.delivery_instance_id",
{
"$": [
"d:delivery_instances:21",
"d:delivery_instances:22"
]
}
]
}
}
]
},
"groups": [
"p.id",
"p.name",
"pc.id",
"pc.name"
]
},
"from_as": "containers",
"groups": [
"containers.product_id",
"containers.product_name"
]
},
"delivery_instance_product_reserves": {
"columns": {
"product_id": "containers.product_id",
"containers": {
"$array_agg": {
"$obj": {
"product_name": "containers.product_name",
"container_name": "containers.name",
"container_count": "containers.count",
"container_weight": "containers.weight"
}
}
},
"container_count_sum": {
"$sum": "containers.count"
},
"container_weight_sum": {
"$sum": "containers.weight"
}
},
"from": {
"columns": {
"product_id": "p.id",
"product_name": "p.name",
"name": "pc.name",
"count": {
"$sum": "dipr.container_quantity"
},
"weight": {
"$sum": {
"$mul": [
"pc.weight",
"dipr.container_quantity"
]
}
}
},
"from": "s:delivery_instance_product_reserves",
"from_as": "dipr",
"joins": [
{
"join": "s:product_containers",
"as": "pc",
"on": {
"$eq": [
"pc.id",
"dipr.product_container_id"
]
}
},
{
"join": "s:products",
"as": "p",
"on": {
"$eq": [
"p.id",
"pc.product_id"
]
}
}
],
"where": {
"$in": [
"dipr.delivery_instance_id",
{
"$": [
"d:delivery_instances:21",
"d:delivery_instances:22"
]
}
]
},
"groups": [
"p.id",
"p.name",
"pc.id",
"pc.name"
]
},
"from_as": "containers",
"groups": [
"containers.product_id",
"containers.product_name"
]
},
"payments": {
"columns": {
"type_name": "pt.name",
"amount": {
"$sum": "dpc.container_price"
}
},
"from": "s:deal_product_containers",
"from_as": "dpc",
"joins": [
{
"join": "s:deals",
"as": "d",
"on": {
"$eq": [
"d.id",
"dpc.deal_id"
]
}
},
{
"join": "s:payment_types",
"as": "pt",
"type": "left",
"on": {
"$eq": [
"pt.id",
"d.payment_type_id"
]
}
}
],
"where": {
"$in": [
"dpc.deal_id",
{
"columns": {
"id": "deal.id"
},
"from": "s:deals",
"from_as": "deal",
"joins": [
{
"join": "s:delivery_instance_deals",
"as": "delivery_instance_deals",
"on": {
"$eq": [
"delivery_instance_deals.deal_id",
"deal.id"
]
}
},
{
"join": "s:delivery_points",
"type": "left",
"as": "point",
"on": {
"$eq": [
"deal.delivery_point_id",
"point.id"
]
}
},
{
"join": "s:cities",
"type": "left",
"as": "city",
"on": {
"$eq": [
"point.city_id",
"city.id"
]
}
}
],
"where": {
"$in": [
"delivery_instance_deals.delivery_instance_id",
{
"$": [
"d:delivery_instances:21",
"d:delivery_instances:22"
]
}
]
}
}
]
},
"groups": [
"pt.id",
"pt.name"
]
}
},
"format": "xlsx"
}
}
]
}