ModuleMaster/demurrage/mail_prepare.aspx.vb - main - ivan/ModMas

ModuleMaster/demurrage/mail_prepare.aspx.vb
2 236 строк133 KB
i.krivokon
Initial commit
13 мар 2026, 13:49
13 мар 2026, 13:4900d9fb6

Imports System.Data
Imports System.Data.SqlClient
Imports System.Globalization
Imports System.IO
Imports System.Web.UI.DataVisualization.Charting
Imports sharedClasses.dbFunctions

Partial Class r
    Inherits System.Web.UI.Page
    Private line As String
    Public monthly_table As DataTable = New DataTable
    Private db As New SqlFunctions
    Private mail_title_demurrage, mail_title_warning As String
    Private day_diff As Integer = 32


    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        Dim dv_container_trend As DataView
        Dim prepare_sql As New StringBuilder '- SQL для подготовки данных (выборка из таблиц item_information, container_details - и сохранение в результирующей таблице mail_prepare
        Dim prepare_sql_1 As New StringBuilder
        Dim sql, sql1, sql2, sql3, sql4, sql5 As New StringBuilder ' sql - для графика тренда, sql1 - для таблицы(ежедневная часть), sql2 -для таблицы(ежемесячная часть), sql3- для таблицы с demurrage по портам и направлениям, sql4 - для таблицы по warnings (менее 5ти дней до простоя), sql5 - для таблицы с контейнерами по типу материала

        Dim sql_mail_title_demurrage, sql_mail_title_warning As New StringBuilder 'Запросы для формирования заголовка письма



        'Dim message, title As String
        'Dim myValue As Object
        'message = "Intput qty for GR(-)"
        'title = "GR(-)"
        'myValue = InputBox(message, title)





        'Строка подготовки данных и сохранения в результирующей таблице mail_prepare. Этот sql запускается SQL-батчем каждый день на всякий случай
        prepare_sql.AppendLine("if not exists(select entry_date from [UserModules].[user].[mail_prepare] where cast(entry_date as date) = cast(getdate() as date))")
        prepare_sql.AppendLine("begin")
        prepare_sql.AppendLine("insert into [UserModules].[user].[mail_prepare] (entry_date) values (cast(getdate() as date))")
        prepare_sql.AppendLine("end")
        prepare_sql.AppendLine("")
        prepare_sql.AppendLine("")
        prepare_sql.AppendLine("")
        prepare_sql.AppendLine("")
        prepare_sql.AppendLine("")
        prepare_sql.AppendLine("--Containers")
        prepare_sql.AppendLine("update [UserModules].[user].[mail_prepare]")
        prepare_sql.AppendLine("set container =")
        prepare_sql.AppendLine("(")
        prepare_sql.AppendLine("	select count (container) as container")
        prepare_sql.AppendLine("	from [UserModules].[user].[ITEM_INFORMATION]")
        prepare_sql.AppendLine("	where")
        prepare_sql.AppendLine("	(")
        prepare_sql.AppendLine("	[STATUS] = 'CC finished'")
        prepare_sql.AppendLine("	or [status] = 'not CC'")
        prepare_sql.AppendLine("	or ([status] = 'In transit' and cast(arrival_date as date) = cast(getdate()as date))")
        prepare_sql.AppendLine("	)")
        prepare_sql.AppendLine("	and LEN(container) = 11")
        prepare_sql.AppendLine("	and cast(do_date as date) <> cast(getdate() as date)")
        prepare_sql.AppendLine(")")
        prepare_sql.AppendLine("where cast(entry_date as date) = cast(getdate() as date)")
        prepare_sql.AppendLine("")
        prepare_sql.AppendLine("")
        prepare_sql.AppendLine("")
        prepare_sql.AppendLine("--Add(+)")
        prepare_sql.AppendLine("update [UserModules].[user].[mail_prepare]")
        prepare_sql.AppendLine("set [add] =")
        prepare_sql.AppendLine("(")
        prepare_sql.AppendLine("	select count (container) as container")
        prepare_sql.AppendLine("	from [UserModules].[user].[ITEM_INFORMATION]")
        prepare_sql.AppendLine("	where")
        prepare_sql.AppendLine("	(")
        prepare_sql.AppendLine("	([status] = 'In transit' and cast(arrival_date as date) = cast(getdate()as date))")
        prepare_sql.AppendLine("	)")
        prepare_sql.AppendLine("	and LEN(container) = 11")
        prepare_sql.AppendLine(")")
        prepare_sql.AppendLine("where cast(entry_date as date) = cast(getdate() as date)")
        prepare_sql.AppendLine("")
        prepare_sql.AppendLine("")


        'Значение GR(-) вводится руками

        prepare_sql.AppendLine("update [UserModules].[user].[mail_prepare]")
        prepare_sql.AppendLine("set [GR] =" + Request.QueryString("gr"))
        prepare_sql.AppendLine("where cast(entry_date as date) = cast(getdate() as date)")


        'Логика расчёта GR(-) из таблицы
        'prepare_sql.AppendLine("--GR(-)")
        'prepare_sql.AppendLine("update [UserModules].[user].[mail_prepare]")
        'prepare_sql.AppendLine("set [GR] =")
        'prepare_sql.AppendLine("(")
        'prepare_sql.AppendLine("	select count (container) as container")
        'prepare_sql.AppendLine("	from [UserModules].[user].[ITEM_INFORMATION]")
        'prepare_sql.AppendLine("	where")
        'prepare_sql.AppendLine("	(")
        'prepare_sql.AppendLine("	[STATUS] = 'CC finished'")
        'prepare_sql.AppendLine("	or [status] = 'not CC'")
        'prepare_sql.AppendLine("	or ([status] = 'In transit' and cast(arrival_date as date) = cast(getdate()as date))")
        'prepare_sql.AppendLine("	)")
        'prepare_sql.AppendLine("	and LEN(container) = 11")
        'prepare_sql.AppendLine("	and cast(do_date as date) <= cast(getdate() as date)")
        'prepare_sql.AppendLine(")")
        'prepare_sql.AppendLine("where cast(entry_date as date) = cast(getdate() as date)")
        'prepare_sql.AppendLine("")
        'prepare_sql.AppendLine("")
        'prepare_sql.AppendLine("")
        prepare_sql.AppendLine("--Demmurage")
        prepare_sql.AppendLine("update [UserModules].[user].[mail_prepare]")
        prepare_sql.AppendLine("set [demurrage] =")
        prepare_sql.AppendLine("(")
        prepare_sql.AppendLine("	--Общий результат")
        prepare_sql.AppendLine("	select")
        prepare_sql.AppendLine("	count(container)")
        prepare_sql.AppendLine("	--container")
        prepare_sql.AppendLine("	--,arrival_date")
        prepare_sql.AppendLine("	--,total_free_days")
        prepare_sql.AppendLine("	--,keep_time")
        prepare_sql.AppendLine("	from")
        prepare_sql.AppendLine("	(")
        prepare_sql.AppendLine("")
        prepare_sql.AppendLine("	--Выборка контейнеров и необходимой информации для них, которые НЕ НАХОДЯТСЯ на ODCY")
        prepare_sql.AppendLine("			select")
        prepare_sql.AppendLine("			info.container")
        prepare_sql.AppendLine("			,info.ARRIVAL_DATE")
        prepare_sql.AppendLine("			,info.do_DATE")
        prepare_sql.AppendLine("			,case")
        prepare_sql.AppendLine("					when patindex('%/%', info.CONDITION) <> 0 then")
        prepare_sql.AppendLine("					substring(info.CONDITION, 1, patindex('%/%', info.CONDITION)-1)")
        prepare_sql.AppendLine("					when patindex('%/%', info.CONDITION) = 0 then")
        prepare_sql.AppendLine("					info.CONDITION")
        prepare_sql.AppendLine("			end as total_free_days")
        prepare_sql.AppendLine("			,datediff(day, cast(arrival_date as date), cast(getdate() as date)) + 1 as keep_time")
        prepare_sql.AppendLine("")
        prepare_sql.AppendLine("")
        prepare_sql.AppendLine("			from [UserModules].[user].[ITEM_INFORMATION] info")
        prepare_sql.AppendLine("")
        prepare_sql.AppendLine("			left join [UserModules].[user].[DESTINATION_MASTER] [master]")
        prepare_sql.AppendLine("			on info.CARRIER_ID = [master].id")
        prepare_sql.AppendLine("			where")
        prepare_sql.AppendLine("			info.[status] is not null")
        prepare_sql.AppendLine("			and info.[status] <> ''")
        prepare_sql.AppendLine("			and info.[status] <> 'N/A'")
        prepare_sql.AppendLine("			and info.[status] <> 'In transit'")
        prepare_sql.AppendLine("			and ([master].IS_ODCY <> 'Y' or [master].IS_ODCY is null)")
        prepare_sql.AppendLine("")
        prepare_sql.AppendLine("			UNION ALL")
        prepare_sql.AppendLine("			--Выборка контейнеров и необходимой информации для них, которые НАХОДЯТСЯ на ODCY")
        prepare_sql.AppendLine("			select")
        prepare_sql.AppendLine("			CONTAINER")
        prepare_sql.AppendLine("			,ARRIVAL_DATE")
        prepare_sql.AppendLine("			,DO_DATE")
        prepare_sql.AppendLine("			,total_free_days")
        prepare_sql.AppendLine("			,datediff(day, cast(arrival_date as date), cast(getdate() as date)) + 1 as keep_time")
        prepare_sql.AppendLine("			from")
        prepare_sql.AppendLine("			(")
        prepare_sql.AppendLine("")
        prepare_sql.AppendLine("					--Парсинг условий из ранее сформированной таблицы и преобразование их в int. Вычисление общей суммы бесплатного срока хранения")
        prepare_sql.AppendLine("					select")
        prepare_sql.AppendLine("					container")
        prepare_sql.AppendLine("					,arrival_date")
        prepare_sql.AppendLine("					,do_date")
        prepare_sql.AppendLine("					,condition")
        prepare_sql.AppendLine("					,parrent_id")
        prepare_sql.AppendLine("					,parrent_condition")
        prepare_sql.AppendLine("					--Условия хранения")
        prepare_sql.AppendLine("					,case when isnumeric --Если это число")
        prepare_sql.AppendLine("					(")
        prepare_sql.AppendLine("						condition_parc")
        prepare_sql.AppendLine("					) = 1")
        prepare_sql.AppendLine("")
        prepare_sql.AppendLine("					and isnumeric --И это число")
        prepare_sql.AppendLine("					(")
        prepare_sql.AppendLine("						parrent_condition_parc")
        prepare_sql.AppendLine("					) = 1")
        prepare_sql.AppendLine("					then --Тогда считаем общую сумму бесплатного хранения")
        prepare_sql.AppendLine("						cast(condition_parc as int)")
        prepare_sql.AppendLine("						+")
        prepare_sql.AppendLine("						cast(parrent_condition_parc as int)")
        prepare_sql.AppendLine("					end as total_free_days")
        prepare_sql.AppendLine("")
        prepare_sql.AppendLine("					from")
        prepare_sql.AppendLine("					--Выборка контейнеров ТОЛЬКО на ODCY, определение их родительских портов и условий хранение в родительском порту + ODCY")
        prepare_sql.AppendLine("					(")
        prepare_sql.AppendLine("							select")
        prepare_sql.AppendLine("							info.CONTAINER")
        prepare_sql.AppendLine("							,info.ARRIVAL_DATE")
        prepare_sql.AppendLine("							,info.DO_DATE")
        prepare_sql.AppendLine("							,info.CONDITION")
        prepare_sql.AppendLine("							,[master].parrent_port_id as parrent_id")
        prepare_sql.AppendLine("							--Следующая строка повторяется несколько раз в case. При изменении её нужно подставить в case")
        prepare_sql.AppendLine("							,(select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id) as parrent_condition")
        prepare_sql.AppendLine("							,case")
        prepare_sql.AppendLine("								when patindex('%/%', info.CONDITION) <> 0 then")
        prepare_sql.AppendLine("								substring(info.CONDITION, 1, patindex('%/%', info.CONDITION)-1)")
        prepare_sql.AppendLine("								when patindex('%/%', info.CONDITION) = 0 then")
        prepare_sql.AppendLine("								info.CONDITION")
        prepare_sql.AppendLine("							end as condition_parc")
        prepare_sql.AppendLine("							,case")
        prepare_sql.AppendLine("								when patindex('%/%', (select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id)) <> 0 then")
        prepare_sql.AppendLine("								substring((select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id), 1, patindex('%/%', (select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id))-1)")
        prepare_sql.AppendLine("								when patindex('%/%', (select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id)) = 0 then")
        prepare_sql.AppendLine("								(select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id)")
        prepare_sql.AppendLine("							end as parrent_condition_parc")
        prepare_sql.AppendLine("")
        prepare_sql.AppendLine("							from [UserModules].[user].[ITEM_INFORMATION] info")
        prepare_sql.AppendLine("")
        prepare_sql.AppendLine("							left join [UserModules].[user].[DESTINATION_MASTER] [master]")
        prepare_sql.AppendLine("							on [master].id = info.CARRIER_ID")
        prepare_sql.AppendLine("")
        prepare_sql.AppendLine("							--left join [UserModules].[user].[DESTINATION_MASTER] slave")
        prepare_sql.AppendLine("							--on [master].id = [slave].ODCY_ID")
        prepare_sql.AppendLine("")
        prepare_sql.AppendLine("						where [master].IS_ODCY = 'Y'")
        prepare_sql.AppendLine("					) t")
        prepare_sql.AppendLine("			)tt")
        prepare_sql.AppendLine("	)ttt")
        prepare_sql.AppendLine("	where keep_time > 0")
        prepare_sql.AppendLine("	and keep_time > total_free_days")
        prepare_sql.AppendLine("	and LEN(container) = 11")
        prepare_sql.AppendLine("	and cast(do_date as date) <> cast(getdate() as date)")
        prepare_sql.AppendLine(")")
        prepare_sql.AppendLine("where cast(entry_date as date) = cast(getdate() as date)")
        prepare_sql.AppendLine("")
        prepare_sql.AppendLine("")
        prepare_sql.AppendLine("")
        prepare_sql.AppendLine("")
        prepare_sql.AppendLine("")

        prepare_sql.AppendLine("--Vorsino_current")
        prepare_sql.AppendLine("update [UserModules].[user].[mail_prepare]")
        prepare_sql.AppendLine("set [vorsino_current] =")
        prepare_sql.AppendLine("(")
        prepare_sql.AppendLine("	select count (container) as container")
        prepare_sql.AppendLine("	from [UserModules].[user].[ITEM_INFORMATION]")
        prepare_sql.AppendLine("	where carrier_id = 52")
        prepare_sql.AppendLine("	and status <> 'in transit'")
        prepare_sql.AppendLine("	and LEN(container) = 11")
        prepare_sql.AppendLine(")")
        prepare_sql.AppendLine("where cast(entry_date as date) = cast(getdate() as date)")


        prepare_sql.AppendLine("--Total calculated")
        prepare_sql.AppendLine("update [UserModules].[user].[mail_prepare]")
        prepare_sql.AppendLine("set [total_calculated] =")
        prepare_sql.AppendLine("(")
        prepare_sql.AppendLine("	--Суммируем все неустойки за сегодняшний день")
        prepare_sql.AppendLine("	select")
        prepare_sql.AppendLine("	sum (price) as total_fee")
        prepare_sql.AppendLine("	from")
        prepare_sql.AppendLine("	(")
        prepare_sql.AppendLine("			--Общий результат")
        prepare_sql.AppendLine("			select")
        prepare_sql.AppendLine("			ttt.CARRIER_ID")
        prepare_sql.AppendLine("			,container")
        prepare_sql.AppendLine("			,arrival_date")
        prepare_sql.AppendLine("			,total_free_days")
        prepare_sql.AppendLine("			,demurrage")
        prepare_sql.AppendLine("			,demurrage - total_free_days as ddd")
        prepare_sql.AppendLine("			,max([days]) as [days]")
        prepare_sql.AppendLine("			,max(cast([price] as numeric(20,3))) as price")
        prepare_sql.AppendLine("			from")
        prepare_sql.AppendLine("			(")
        prepare_sql.AppendLine("")
        prepare_sql.AppendLine("			--Выборка контейнеров и необходимой информации для них, которые НЕ НАХОДЯТСЯ на ODCY")
        prepare_sql.AppendLine("					select")
        prepare_sql.AppendLine("					info.CARRIER_ID")
        prepare_sql.AppendLine("					,info.container")
        prepare_sql.AppendLine("					,info.ARRIVAL_DATE")
        prepare_sql.AppendLine("					,case")
        prepare_sql.AppendLine("							when patindex('%/%', info.CONDITION) <> 0 then")
        prepare_sql.AppendLine("							substring(info.CONDITION, 1, patindex('%/%', info.CONDITION)-1)")
        prepare_sql.AppendLine("							when patindex('%/%', info.CONDITION) = 0 then")
        prepare_sql.AppendLine("							info.CONDITION")
        prepare_sql.AppendLine("					end as total_free_days")
        prepare_sql.AppendLine("					,datediff(day, cast(arrival_date as date), cast(getdate() as date)) as demurrage")
        prepare_sql.AppendLine("")
        prepare_sql.AppendLine("					from [UserModules].[user].[ITEM_INFORMATION] info")
        prepare_sql.AppendLine("")
        prepare_sql.AppendLine("					left join [UserModules].[user].[DESTINATION_MASTER] [master]")
        prepare_sql.AppendLine("					on info.CARRIER_ID = [master].id")
        prepare_sql.AppendLine("					where")
        prepare_sql.AppendLine("					info.[status] is not null")
        prepare_sql.AppendLine("					and info.[status] <> ''")
        prepare_sql.AppendLine("					and info.[status] <> 'N/A'")
        prepare_sql.AppendLine("					and info.[status] <> 'In transit'")
        prepare_sql.AppendLine("					and ([master].IS_ODCY <> 'Y' or [master].IS_ODCY is null)")
        prepare_sql.AppendLine("")
        prepare_sql.AppendLine("					UNION ALL")
        prepare_sql.AppendLine("					--Выборка контейнеров и необходимой информации для них, которые НАХОДЯТСЯ на ODCY")
        prepare_sql.AppendLine("					select")
        prepare_sql.AppendLine("					CARRIER_ID")
        prepare_sql.AppendLine("					,CONTAINER")
        prepare_sql.AppendLine("					,ARRIVAL_DATE")
        prepare_sql.AppendLine("					,total_free_days")
        prepare_sql.AppendLine("					,datediff(day, cast(arrival_date as date), cast(getdate() as date)) as demurrage")
        prepare_sql.AppendLine("					from")
        prepare_sql.AppendLine("					(")
        prepare_sql.AppendLine("")
        prepare_sql.AppendLine("							--Парсинг условий из ранее сформированной таблицы и преобразование их в int. Вычисление общей суммы бесплатного срока хранения")
        prepare_sql.AppendLine("							select")
        prepare_sql.AppendLine("							CARRIER_ID")
        prepare_sql.AppendLine("							,container")
        prepare_sql.AppendLine("							,arrival_date")
        prepare_sql.AppendLine("							,condition")
        prepare_sql.AppendLine("							,parrent_id")
        prepare_sql.AppendLine("							,parrent_condition")
        prepare_sql.AppendLine("							--Условия хранения")
        prepare_sql.AppendLine("							,case when isnumeric --Если это число")
        prepare_sql.AppendLine("							(")
        prepare_sql.AppendLine("								condition_parc")
        prepare_sql.AppendLine("							) = 1")
        prepare_sql.AppendLine("")
        prepare_sql.AppendLine("							and isnumeric --И это число")
        prepare_sql.AppendLine("							(")
        prepare_sql.AppendLine("								parrent_condition_parc")
        prepare_sql.AppendLine("							) = 1")
        prepare_sql.AppendLine("							then --Тогда считаем общую сумму бесплатного хранения")
        prepare_sql.AppendLine("								cast(condition_parc as int)")
        prepare_sql.AppendLine("								+")
        prepare_sql.AppendLine("								cast(parrent_condition_parc as int)")
        prepare_sql.AppendLine("							end as total_free_days")
        prepare_sql.AppendLine("")
        prepare_sql.AppendLine("							from")
        prepare_sql.AppendLine("							--Выборка контейнеров ТОЛЬКО на ODCY, определение их родительских портов и условий хранение в родительском порту + ODCY")
        prepare_sql.AppendLine("							(")
        prepare_sql.AppendLine("									select")
        prepare_sql.AppendLine("									info.CARRIER_ID")
        prepare_sql.AppendLine("									,info.CONTAINER")
        prepare_sql.AppendLine("									,info.ARRIVAL_DATE")
        prepare_sql.AppendLine("									,info.CONDITION")
        prepare_sql.AppendLine("									,[master].parrent_port_id as parrent_id")
        prepare_sql.AppendLine("									--Следующая строка повторяется несколько раз в case. При изменении её нужно подставить в case")
        prepare_sql.AppendLine("									,(select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id) as parrent_condition")
        prepare_sql.AppendLine("									,case")
        prepare_sql.AppendLine("										when patindex('%/%', info.CONDITION) <> 0 then")
        prepare_sql.AppendLine("										substring(info.CONDITION, 1, patindex('%/%', info.CONDITION)-1)")
        prepare_sql.AppendLine("										when patindex('%/%', info.CONDITION) = 0 then")
        prepare_sql.AppendLine("										info.CONDITION")
        prepare_sql.AppendLine("									end as condition_parc")
        prepare_sql.AppendLine("									,case")
        prepare_sql.AppendLine("										when patindex('%/%', (select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id)) <> 0 then")
        prepare_sql.AppendLine("										substring((select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id), 1, patindex('%/%', (select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id))-1)")
        prepare_sql.AppendLine("										when patindex('%/%', (select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id)) = 0 then")
        prepare_sql.AppendLine("										(select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id)")
        prepare_sql.AppendLine("									end as parrent_condition_parc")
        prepare_sql.AppendLine("")
        prepare_sql.AppendLine("									from [UserModules].[user].[ITEM_INFORMATION] info")
        prepare_sql.AppendLine("")
        prepare_sql.AppendLine("									left join [UserModules].[user].[DESTINATION_MASTER] [master]")
        prepare_sql.AppendLine("									on [master].id = info.CARRIER_ID")
        prepare_sql.AppendLine("")
        prepare_sql.AppendLine("									--left join [UserModules].[user].[DESTINATION_MASTER] slave")
        prepare_sql.AppendLine("									--on [master].id = [slave].ODCY_ID")
        prepare_sql.AppendLine("")
        prepare_sql.AppendLine("								where [master].IS_ODCY = 'Y'")
        prepare_sql.AppendLine("							) t")
        prepare_sql.AppendLine("					)tt")
        prepare_sql.AppendLine("			)ttt")
        prepare_sql.AppendLine("")
        prepare_sql.AppendLine("			left join [UserModules].[user].[DEMURRAGE_PRICE]")
        prepare_sql.AppendLine("			on ttt.CARRIER_ID = [DEMURRAGE_PRICE].carrier_id")
        prepare_sql.AppendLine("			where demurrage > 0")
        prepare_sql.AppendLine("			and demurrage > total_free_days")
        prepare_sql.AppendLine("			and LEN(container) = 11")
        prepare_sql.AppendLine("			and [days] <= demurrage")
        prepare_sql.AppendLine("")
        prepare_sql.AppendLine("			group by ttt.CARRIER_ID, container, ARRIVAL_DATE, total_free_days, demurrage")
        prepare_sql.AppendLine("	) tttt")
        prepare_sql.AppendLine(")")
        prepare_sql.AppendLine("where cast(entry_date as date) = cast(getdate() as date)")
        prepare_sql.AppendLine("")
        prepare_sql.AppendLine("")
        prepare_sql.AppendLine("")
        prepare_sql.AppendLine("--STP->ODCY(qty)")
        prepare_sql.AppendLine("update [UserModules].[user].[mail_prepare]")
        prepare_sql.AppendLine("set [stp_qty_] =")
        prepare_sql.AppendLine("(")
        prepare_sql.AppendLine("	select")
        prepare_sql.AppendLine("	count (container)")
        prepare_sql.AppendLine("	--container")
        prepare_sql.AppendLine("	--,ARRIVAL_DATE")
        prepare_sql.AppendLine("	--,dateadd(day, cast(parrent_condition_parc as int) ,cast(arrival_date as date)) ODCY_date")
        prepare_sql.AppendLine("	from")
        prepare_sql.AppendLine("	(")
        prepare_sql.AppendLine("			select")
        prepare_sql.AppendLine("			info.CARRIER_ID")
        prepare_sql.AppendLine("			,info.CONTAINER")
        prepare_sql.AppendLine("			,info.ARRIVAL_DATE")
        prepare_sql.AppendLine("			,(select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id) as parrent_condition")
        prepare_sql.AppendLine("			,case")
        prepare_sql.AppendLine("			when patindex('%/%', (select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id)) <> 0 then")
        prepare_sql.AppendLine("			substring((select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id), 1, patindex('%/%', (select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id))-1)")
        prepare_sql.AppendLine("			when patindex('%/%', (select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id)) = 0 then")
        prepare_sql.AppendLine("			(select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id)")
        prepare_sql.AppendLine("			end as parrent_condition_parc")
        prepare_sql.AppendLine("")
        prepare_sql.AppendLine("")
        prepare_sql.AppendLine("			from [UserModules].[user].[ITEM_INFORMATION] info")
        prepare_sql.AppendLine("			left join [UserModules].[user].[DESTINATION_MASTER] [master]")
        prepare_sql.AppendLine("			on [master].id = info.CARRIER_ID")
        prepare_sql.AppendLine("			where IS_ODCY = 'Y'")
        prepare_sql.AppendLine("		) t")
        prepare_sql.AppendLine("	where dateadd(day, cast(parrent_condition_parc as int) ,cast(arrival_date as date)) = cast(getdate() as date)")
        prepare_sql.AppendLine(")")
        prepare_sql.AppendLine("where cast(entry_date as date) = cast(getdate() as date)")
        prepare_sql.AppendLine("")
        prepare_sql.AppendLine("")
        prepare_sql.AppendLine("")
        prepare_sql.AppendLine("--STP->ODCY")
        prepare_sql.AppendLine("update [UserModules].[user].[mail_prepare]")
        prepare_sql.AppendLine("set [stp_money_] =")
        prepare_sql.AppendLine("(")
        prepare_sql.AppendLine("	select")
        prepare_sql.AppendLine("	sum (cast(MOVING_PRICE as numeric(20,5)))")
        prepare_sql.AppendLine("	--container")
        prepare_sql.AppendLine("	--,ARRIVAL_DATE")
        prepare_sql.AppendLine("	--,dateadd(day, cast(parrent_condition_parc as int) ,cast(arrival_date as date)) ODCY_date")
        prepare_sql.AppendLine("	--,moving_price")
        prepare_sql.AppendLine("	from")
        prepare_sql.AppendLine("	(")
        prepare_sql.AppendLine("			select")
        prepare_sql.AppendLine("			info.CARRIER_ID")
        prepare_sql.AppendLine("			,info.CONTAINER")
        prepare_sql.AppendLine("			,info.ARRIVAL_DATE")
        prepare_sql.AppendLine("			,(select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id) as parrent_condition")
        prepare_sql.AppendLine("			,case")
        prepare_sql.AppendLine("			when patindex('%/%', (select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id)) <> 0 then")
        prepare_sql.AppendLine("			substring((select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id), 1, patindex('%/%', (select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id))-1)")
        prepare_sql.AppendLine("			when patindex('%/%', (select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id)) = 0 then")
        prepare_sql.AppendLine("			(select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id)")
        prepare_sql.AppendLine("			end as parrent_condition_parc")
        prepare_sql.AppendLine("			,[master].MOVING_PRICE")
        prepare_sql.AppendLine("")
        prepare_sql.AppendLine("")
        prepare_sql.AppendLine("			from [UserModules].[user].[ITEM_INFORMATION] info")
        prepare_sql.AppendLine("			left join [UserModules].[user].[DESTINATION_MASTER] [master]")
        prepare_sql.AppendLine("			on [master].id = info.CARRIER_ID")
        prepare_sql.AppendLine("			where IS_ODCY = 'Y'")
        prepare_sql.AppendLine("		) t")
        prepare_sql.AppendLine("	where dateadd(day, cast(parrent_condition_parc as int) ,cast(arrival_date as date)) = cast(getdate() as date)")
        prepare_sql.AppendLine(")")
        prepare_sql.AppendLine("where cast(entry_date as date) = cast(getdate() as date)")


        'Количество контейнеров по материалам, находящихся в данный момент на станциях(не заказанные на текущий рабочий день)
        prepare_sql_1.AppendLine("update")
        prepare_sql_1.AppendLine("[UserModules].[user].[MAIL_PREPARE]")
        prepare_sql_1.AppendLine("set LCD_PANELS_TOTAL_ = t1.[LCD PANEL]")
        prepare_sql_1.AppendLine(",RESIN_VD_TOTAL_ = t1.[Resin VD]")
        prepare_sql_1.AppendLine(",RESIN_WM_TOTAL_ = t1.[Resin WM]")
        prepare_sql_1.AppendLine(",TV_WM_PARTS_TOTAL_	 = t1.[TV/WM Parts]")
        prepare_sql_1.AppendLine(",WM_PARTS_TOTAL_ = t1.[WM PARTS]")
        prepare_sql_1.AppendLine(",TV_PARTS_TOTAL_ = t1.[TV PARTS]")
        prepare_sql_1.AppendLine("")
        prepare_sql_1.AppendLine("from")
        prepare_sql_1.AppendLine("(")
        prepare_sql_1.AppendLine("	select")
        prepare_sql_1.AppendLine("	isnull([LCD PANEL],'-') as [LCD PANEL]")
        prepare_sql_1.AppendLine("	,isnull([Resin VD],'-') as [Resin VD]")
        prepare_sql_1.AppendLine("	,isnull([Resin WM],'-') as [Resin WM]")
        prepare_sql_1.AppendLine("	,isnull([TV/WM Parts],'-') as [TV/WM Parts]")
        prepare_sql_1.AppendLine("	,isnull([TV PARTS],'-') as [TV PARTS]")
        prepare_sql_1.AppendLine("	,isnull([WM PARTS],'-') as [WM PARTS]")
        prepare_sql_1.AppendLine("	from")
        prepare_sql_1.AppendLine("	(")
        prepare_sql_1.AppendLine("		select")
        prepare_sql_1.AppendLine("		info.CARGO as [cargo]")
        prepare_sql_1.AppendLine("		,count (info.container) as qty")
        prepare_sql_1.AppendLine("		from [UserModules].[user].[ITEM_INFORMATION] info")
        prepare_sql_1.AppendLine("		where")
        prepare_sql_1.AppendLine("		(")
        prepare_sql_1.AppendLine("		[STATUS] = 'CC finished'")
        prepare_sql_1.AppendLine("		or [status] = 'not CC'")
        prepare_sql_1.AppendLine("		or ([status] = 'In transit' and cast(arrival_date as date) = cast(getdate()as date))")
        prepare_sql_1.AppendLine("		)")
        prepare_sql_1.AppendLine("		and LEN(info.container) = 11")
        prepare_sql_1.AppendLine("		and cast(do_date as date) <> cast(getdate() as date)")
        prepare_sql_1.AppendLine("	")
        prepare_sql_1.AppendLine("		group by info.CARGO")
        prepare_sql_1.AppendLine("		)src")
        prepare_sql_1.AppendLine("")
        prepare_sql_1.AppendLine("		pivot")
        prepare_sql_1.AppendLine("		(")
        prepare_sql_1.AppendLine("		max(qty)")
        prepare_sql_1.AppendLine("		for cargo in ([LCD PANEL], [Resin VD], [Resin WM], [TV/WM Parts], [TV PARTS], [WM PARTS])")
        prepare_sql_1.AppendLine("		) pvt")
        prepare_sql_1.AppendLine(") t1")
        prepare_sql_1.AppendLine("where cast(entry_date as date) = cast(getdate() as date)")
        prepare_sql_1.AppendLine("")










        'Количество простойных контейнеров по материалам, находящихся в данный момент на станциях(не заказанные на текущий рабочий день)
        prepare_sql_1.AppendLine("update")
        prepare_sql_1.AppendLine("[UserModules].[user].[MAIL_PREPARE]")
        prepare_sql_1.AppendLine("set LCD_PANELS_DEMURRAGE_ = t1.[LCD PANEL]")
        prepare_sql_1.AppendLine(",RESIN_VD_DEMURRAGE_ = t1.[Resin VD]")
        prepare_sql_1.AppendLine(",RESIN_WM_DEMURRAGE_ = t1.[Resin WM]")
        prepare_sql_1.AppendLine(",TV_WM_PARTS_DEMURRAGE_	 = t1.[TV/WM Parts]")
        prepare_sql_1.AppendLine(",WM_PARTS_DEMURRAGE_ = t1.[WM PARTS]")
        prepare_sql_1.AppendLine(",TV_PARTS_DEMURRAGE_ = t1.[TV PARTS]")
        prepare_sql_1.AppendLine("")
        prepare_sql_1.AppendLine("from")
        prepare_sql_1.AppendLine("(")
        prepare_sql_1.AppendLine("select")
        prepare_sql_1.AppendLine("[LCD PANEL]")
        prepare_sql_1.AppendLine(",[Resin VD]")
        prepare_sql_1.AppendLine(",[Resin WM]")
        prepare_sql_1.AppendLine(",[TV/WM Parts]")
        prepare_sql_1.AppendLine(",[TV PARTS]")
        prepare_sql_1.AppendLine(",[WM PARTS]")
        prepare_sql_1.AppendLine("from")
        prepare_sql_1.AppendLine("	(")
        prepare_sql_1.AppendLine("	--Общий результат")
        prepare_sql_1.AppendLine("	select")
        prepare_sql_1.AppendLine("	cargo")
        prepare_sql_1.AppendLine("	,count(container) as qty")
        prepare_sql_1.AppendLine("	from")
        prepare_sql_1.AppendLine("	(")
        prepare_sql_1.AppendLine("")
        prepare_sql_1.AppendLine("	--Выборка контейнеров и необходимой информации для них, которые НЕ НАХОДЯТСЯ на ODCY")
        prepare_sql_1.AppendLine("			select")
        prepare_sql_1.AppendLine("			info.container")
        prepare_sql_1.AppendLine("			,info.ARRIVAL_DATE")
        prepare_sql_1.AppendLine("			,info.do_DATE")
        prepare_sql_1.AppendLine("			,case")
        prepare_sql_1.AppendLine("					when patindex('%/%', info.CONDITION) <> 0 then")
        prepare_sql_1.AppendLine("					substring(info.CONDITION, 1, patindex('%/%', info.CONDITION)-1)")
        prepare_sql_1.AppendLine("					when patindex('%/%', info.CONDITION) = 0 then")
        prepare_sql_1.AppendLine("					info.CONDITION")
        prepare_sql_1.AppendLine("			end as total_free_days")
        prepare_sql_1.AppendLine("			,datediff(day, cast(arrival_date as date), cast(getdate() as date)) + 1 as keep_time")
        prepare_sql_1.AppendLine("")
        prepare_sql_1.AppendLine("			, info.CARGO")
        prepare_sql_1.AppendLine("			from [UserModules].[user].[ITEM_INFORMATION] info")
        prepare_sql_1.AppendLine("")
        prepare_sql_1.AppendLine("			left join [UserModules].[user].[DESTINATION_MASTER] [master]")
        prepare_sql_1.AppendLine("			on info.CARRIER_ID = [master].id")
        prepare_sql_1.AppendLine("			where")
        prepare_sql_1.AppendLine("			info.[status] is not null")
        prepare_sql_1.AppendLine("			and info.[status] <> ''")
        prepare_sql_1.AppendLine("			and info.[status] <> 'N/A'")
        prepare_sql_1.AppendLine("			and info.[status] <> 'In transit'")
        prepare_sql_1.AppendLine("			and ([master].IS_ODCY <> 'Y' or [master].IS_ODCY is null)")
        prepare_sql_1.AppendLine("")
        prepare_sql_1.AppendLine("			UNION ALL")
        prepare_sql_1.AppendLine("			--Выборка контейнеров и необходимой информации для них, которые НАХОДЯТСЯ на ODCY")
        prepare_sql_1.AppendLine("			select")
        prepare_sql_1.AppendLine("			CONTAINER")
        prepare_sql_1.AppendLine("			,ARRIVAL_DATE")
        prepare_sql_1.AppendLine("			,DO_DATE")
        prepare_sql_1.AppendLine("			,total_free_days")
        prepare_sql_1.AppendLine("			,datediff(day, cast(arrival_date as date), cast(getdate() as date)) + 1 as keep_time")
        prepare_sql_1.AppendLine("			,cargo")
        prepare_sql_1.AppendLine("			from")
        prepare_sql_1.AppendLine("			(")
        prepare_sql_1.AppendLine("")
        prepare_sql_1.AppendLine("					--Парсинг условий из ранее сформированной таблицы и преобразование их в int. Вычисление общей суммы бесплатного срока хранения")
        prepare_sql_1.AppendLine("					select")
        prepare_sql_1.AppendLine("					container")
        prepare_sql_1.AppendLine("					,arrival_date")
        prepare_sql_1.AppendLine("					,do_date")
        prepare_sql_1.AppendLine("					,condition")
        prepare_sql_1.AppendLine("					,parrent_id")
        prepare_sql_1.AppendLine("					,parrent_condition")
        prepare_sql_1.AppendLine("					--Условия хранения")
        prepare_sql_1.AppendLine("					,case when isnumeric --Если это число")
        prepare_sql_1.AppendLine("					(")
        prepare_sql_1.AppendLine("						condition_parc")
        prepare_sql_1.AppendLine("					) = 1")
        prepare_sql_1.AppendLine("")
        prepare_sql_1.AppendLine("					and isnumeric --И это число")
        prepare_sql_1.AppendLine("					(")
        prepare_sql_1.AppendLine("						parrent_condition_parc")
        prepare_sql_1.AppendLine("					) = 1")
        prepare_sql_1.AppendLine("					then --Тогда считаем общую сумму бесплатного хранения")
        prepare_sql_1.AppendLine("						cast(condition_parc as int)")
        prepare_sql_1.AppendLine("						+")
        prepare_sql_1.AppendLine("						cast(parrent_condition_parc as int)")
        prepare_sql_1.AppendLine("					end as total_free_days")
        prepare_sql_1.AppendLine("					,cargo")
        prepare_sql_1.AppendLine("")
        prepare_sql_1.AppendLine("					from")
        prepare_sql_1.AppendLine("					--Выборка контейнеров ТОЛЬКО на ODCY, определение их родительских портов и условий хранение в родительском порту + ODCY")
        prepare_sql_1.AppendLine("					(")
        prepare_sql_1.AppendLine("							select")
        prepare_sql_1.AppendLine("							info.CONTAINER")
        prepare_sql_1.AppendLine("							,info.ARRIVAL_DATE")
        prepare_sql_1.AppendLine("							,info.DO_DATE")
        prepare_sql_1.AppendLine("							,info.CONDITION")
        prepare_sql_1.AppendLine("							,[master].parrent_port_id as parrent_id")
        prepare_sql_1.AppendLine("							--Следующая строка повторяется несколько раз в case. При изменении её нужно подставить в case")
        prepare_sql_1.AppendLine("							,(select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id) as parrent_condition")
        prepare_sql_1.AppendLine("							,case")
        prepare_sql_1.AppendLine("								when patindex('%/%', info.CONDITION) <> 0 then")
        prepare_sql_1.AppendLine("								substring(info.CONDITION, 1, patindex('%/%', info.CONDITION)-1)")
        prepare_sql_1.AppendLine("								when patindex('%/%', info.CONDITION) = 0 then")
        prepare_sql_1.AppendLine("								info.CONDITION")
        prepare_sql_1.AppendLine("							end as condition_parc")
        prepare_sql_1.AppendLine("							,case")
        prepare_sql_1.AppendLine("								when patindex('%/%', (select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id)) <> 0 then")
        prepare_sql_1.AppendLine("								substring((select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id), 1, patindex('%/%', (select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id))-1)")
        prepare_sql_1.AppendLine("								when patindex('%/%', (select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id)) = 0 then")
        prepare_sql_1.AppendLine("								(select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id)")
        prepare_sql_1.AppendLine("							end as parrent_condition_parc")
        prepare_sql_1.AppendLine("							,info.CARGO")
        prepare_sql_1.AppendLine("")
        prepare_sql_1.AppendLine("							from [UserModules].[user].[ITEM_INFORMATION] info")
        prepare_sql_1.AppendLine("")
        prepare_sql_1.AppendLine("							left join [UserModules].[user].[DESTINATION_MASTER] [master]")
        prepare_sql_1.AppendLine("							on [master].id = info.CARRIER_ID")
        prepare_sql_1.AppendLine("")
        prepare_sql_1.AppendLine("							--left join [UserModules].[user].[DESTINATION_MASTER] slave")
        prepare_sql_1.AppendLine("							--on [master].id = [slave].ODCY_ID")
        prepare_sql_1.AppendLine("")
        prepare_sql_1.AppendLine("						where [master].IS_ODCY = 'Y'")
        prepare_sql_1.AppendLine("					) t")
        prepare_sql_1.AppendLine("			)tt")
        prepare_sql_1.AppendLine("	)ttt")
        prepare_sql_1.AppendLine("	where keep_time > 0")
        prepare_sql_1.AppendLine("	and keep_time > total_free_days")
        prepare_sql_1.AppendLine("	and LEN(container) = 11")
        prepare_sql_1.AppendLine("	and cast(do_date as date) <> cast(getdate() as date)")
        prepare_sql_1.AppendLine("	group by CARGO")
        prepare_sql_1.AppendLine("	) src")
        prepare_sql_1.AppendLine("	pivot")
        prepare_sql_1.AppendLine("	(")
        prepare_sql_1.AppendLine("	max(qty)")
        prepare_sql_1.AppendLine("	for cargo in ([LCD PANEL], [Resin VD], [Resin WM], [TV/WM Parts], [TV PARTS], [WM PARTS])")
        prepare_sql_1.AppendLine("	) pvt")
        prepare_sql_1.AppendLine(") t1")
        prepare_sql_1.AppendLine("where cast(entry_date as date) = cast(getdate() as date)")
        prepare_sql_1.AppendLine("")









        'Строка запроса к базе для графика тренда
        sql.AppendLine("select")
        sql.AppendLine("datepart(day,cast(entry_date as date)) as [date]")
        sql.AppendLine(",container")
        sql.AppendLine(",demurrage")
        sql.AppendLine("from [UserModules].[user].[mail_prepare]")
        sql.AppendLine("where datepart(month,cast(entry_date as date)) = datepart(month, cast(getdate() as date))")
        sql.AppendLine("and datepart(year,cast(entry_date as date)) = datepart(year, cast(getdate() as date))")
        sql.AppendLine("order by datepart(day,cast(entry_date as date))")


        'Строка запроса для формирования таблицы
        sql1.AppendLine("declare @sql as nvarchar(max)")
        sql1.AppendLine("declare @day_diff char(2)")
        sql1.AppendLine("set @day_diff = " & day_diff.ToString & "")
        sql1.AppendLine("")
        sql1.AppendLine("Declare @days_for_select As nvarchar(max)")
        sql1.AppendLine("Set @days_for_select = (")
        sql1.AppendLine(" SELECT")
        sql1.AppendLine(" distinct")
        sql1.AppendLine(" QUOTENAME(")
        sql1.AppendLine(" cast(datepart(year, cast(entry_date as date)) as varchar) + '-' + right('0' + cast(datepart(month, cast(entry_date as date)) as varchar),2) + '-' + right('0' + cast(datepart(day, cast(entry_date as date)) as varchar), 2)")
        sql1.AppendLine(") + ' as ''' + right('0' + cast(datepart(day, cast(entry_date as date)) as varchar), 2) + ''','")
        sql1.AppendLine(" from [UserModules].[user].[mail_prepare]")
        sql1.AppendLine("	where cast(entry_date as date) <= cast(getdate() as date)")
        sql1.AppendLine("	and cast(entry_date as date) >= dateadd(day,-cast(@day_diff as int), cast(getdate() as date))")
        sql1.AppendLine(" FOR XML PATH('')")
        sql1.AppendLine(")")
        sql1.AppendLine("SET @days_for_select = SUBSTRING(@days_for_select,0,LEN(@days_for_select))")
        sql1.AppendLine("")
        sql1.AppendLine("")
        sql1.AppendLine("declare @days as nvarchar(max)")
        sql1.AppendLine("set @days = (")
        sql1.AppendLine(" SELECT")
        sql1.AppendLine(" distinct")
        sql1.AppendLine(" QUOTENAME(")
        sql1.AppendLine(" cast(datepart(year, cast(entry_date as date)) as varchar) + '-' + right('0' + cast(datepart(month, cast(entry_date as date)) as varchar),2) + '-' + right('0' + cast(datepart(day, cast(entry_date as date)) as varchar), 2)")
        sql1.AppendLine(") + ','")
        sql1.AppendLine(" from [UserModules].[user].[mail_prepare]")
        sql1.AppendLine("")
        sql1.AppendLine("	where cast(entry_date as date) <= cast(getdate() as date)")
        sql1.AppendLine("	and cast(entry_date as date) >= dateadd(day,-cast(@day_diff as int), cast(getdate() as date))")
        sql1.AppendLine("")
        sql1.AppendLine(" FOR XML PATH('')")
        sql1.AppendLine(")")
        sql1.AppendLine("SET @days = SUBSTRING(@days,0,LEN(@days))")
        sql1.AppendLine("")
        sql1.AppendLine("")
        sql1.AppendLine("set @sql='")
        sql1.AppendLine("select")
        sql1.AppendLine("''Container'' as [Day],")
        sql1.AppendLine("' + @days_for_select + '")
        sql1.AppendLine("from")
        sql1.AppendLine("(")
        sql1.AppendLine("select")
        sql1.AppendLine("container")
        sql1.AppendLine(",entry_date")
        sql1.AppendLine("from [UserModules].[user].[mail_prepare]")
        sql1.AppendLine("where cast(entry_date as date) <= cast(getdate() as date)")
        sql1.AppendLine("and cast(entry_date as date) >= dateadd(day,-' + @day_diff + ', cast(getdate() as date))")
        sql1.AppendLine(") src")
        sql1.AppendLine("pivot")
        sql1.AppendLine("(")
        sql1.AppendLine("max(container)")
        sql1.AppendLine("for entry_date in (' + @days + ')")
        sql1.AppendLine(") pvt_cont")
        sql1.AppendLine("")
        sql1.AppendLine("union all")
        sql1.AppendLine("")
        sql1.AppendLine("select")
        sql1.AppendLine("''Add(+)'' as Row_name,")
        sql1.AppendLine("' + @days_for_select + '")
        sql1.AppendLine("from")
        sql1.AppendLine("(")
        sql1.AppendLine("select")
        sql1.AppendLine("[add]")
        sql1.AppendLine(",entry_date")
        sql1.AppendLine("from [UserModules].[user].[mail_prepare]")
        sql1.AppendLine("where cast(entry_date as date) <= cast(getdate() as date)")
        sql1.AppendLine("and cast(entry_date as date) >= dateadd(day,-' + @day_diff + ', cast(getdate() as date))")
        sql1.AppendLine(") src")
        sql1.AppendLine("pivot")
        sql1.AppendLine("(")
        sql1.AppendLine("max([add])")
        sql1.AppendLine("for entry_date in (' + @days + ')")
        sql1.AppendLine(") pvt_cont")
        sql1.AppendLine("")
        sql1.AppendLine("union all")
        sql1.AppendLine("")
        sql1.AppendLine("select")
        sql1.AppendLine("''GR(-)'' as Row_name,")
        sql1.AppendLine("' + @days_for_select + '")
        sql1.AppendLine("from")
        sql1.AppendLine("(")
        sql1.AppendLine("select")
        sql1.AppendLine("[gr]")
        sql1.AppendLine(",entry_date")
        sql1.AppendLine("from [UserModules].[user].[mail_prepare]")
        sql1.AppendLine("where cast(entry_date as date) <= cast(getdate() as date)")
        sql1.AppendLine("and cast(entry_date as date) >= dateadd(day,-' + @day_diff + ', cast(getdate() as date))")
        sql1.AppendLine(") src")
        sql1.AppendLine("pivot")
        sql1.AppendLine("(")
        sql1.AppendLine("max([gr])")
        sql1.AppendLine("for entry_date in (' + @days + ')")
        sql1.AppendLine(") pvt_cont")
        sql1.AppendLine("")
        sql1.AppendLine("union all")
        sql1.AppendLine("")
        sql1.AppendLine("select")
        sql1.AppendLine("''Demurrage'' as Row_name,")
        sql1.AppendLine("' + @days_for_select + '")
        sql1.AppendLine("from")
        sql1.AppendLine("(")
        sql1.AppendLine("select")
        sql1.AppendLine("[demurrage]")
        sql1.AppendLine(",entry_date")
        sql1.AppendLine("from [UserModules].[user].[mail_prepare]")
        sql1.AppendLine("where cast(entry_date as date) <= cast(getdate() as date)")
        sql1.AppendLine("and cast(entry_date as date) >= dateadd(day,-' + @day_diff + ', cast(getdate() as date))")
        sql1.AppendLine(") src")
        sql1.AppendLine("pivot")
        sql1.AppendLine("(")
        sql1.AppendLine("max([demurrage])")
        sql1.AppendLine("for entry_date in (' + @days + ')")
        sql1.AppendLine(") pvt_cont")
        sql1.AppendLine("")
        sql1.AppendLine("union all")
        sql1.AppendLine("")
        sql1.AppendLine("select")
        sql1.AppendLine("''Containers at Vorsino station'' as Row_name,")
        sql1.AppendLine("' + @days_for_select + '")
        sql1.AppendLine("from")
        sql1.AppendLine("(")
        sql1.AppendLine("select")
        sql1.AppendLine("[vorsino_current]")
        sql1.AppendLine(",entry_date")
        sql1.AppendLine("from [UserModules].[user].[mail_prepare]")
        sql1.AppendLine("where cast(entry_date as date) <= cast(getdate() as date)")
        sql1.AppendLine("and cast(entry_date as date) >= dateadd(day,-' + @day_diff + ', cast(getdate() as date))")
        sql1.AppendLine(") src")
        sql1.AppendLine("pivot")
        sql1.AppendLine("(")
        sql1.AppendLine("max([vorsino_current])")
        sql1.AppendLine("for entry_date in (' + @days + ')")
        sql1.AppendLine(") pvt_cont")
        sql1.AppendLine("")


        'Выборка строк Total
        sql1.AppendLine("union all")
        sql1.AppendLine("")
        sql1.AppendLine("select")
        sql1.AppendLine("''LCD Panels(Total)'' as Row_name,")
        sql1.AppendLine("' + @days_for_select + '")
        sql1.AppendLine("from")
        sql1.AppendLine("(")
        sql1.AppendLine("select")
        sql1.AppendLine("LCD_PANELS_TOTAL_")
        sql1.AppendLine(",entry_date")
        sql1.AppendLine("from [UserModules].[user].[mail_prepare]")
        sql1.AppendLine("where cast(entry_date as date) <= cast(getdate() as date)")
        sql1.AppendLine("and cast(entry_date as date) >= dateadd(day,-' + @day_diff + ', cast(getdate() as date))")
        sql1.AppendLine(") src")
        sql1.AppendLine("pivot")
        sql1.AppendLine("(")
        sql1.AppendLine("max([LCD_PANELS_TOTAL_])")
        sql1.AppendLine("for entry_date in (' + @days + ')")
        sql1.AppendLine(") pvt_cont")
        sql1.AppendLine("")
        sql1.AppendLine("")
        sql1.AppendLine("union all")
        sql1.AppendLine("")
        sql1.AppendLine("select")
        sql1.AppendLine("''Resin VD(Total)'' as Row_name,")
        sql1.AppendLine("' + @days_for_select + '")
        sql1.AppendLine("from")
        sql1.AppendLine("(")
        sql1.AppendLine("select")
        sql1.AppendLine("RESIN_VD_TOTAL_")
        sql1.AppendLine(",entry_date")
        sql1.AppendLine("from [UserModules].[user].[mail_prepare]")
        sql1.AppendLine("where cast(entry_date as date) <= cast(getdate() as date)")
        sql1.AppendLine("and cast(entry_date as date) >= dateadd(day,-' + @day_diff + ', cast(getdate() as date))")
        sql1.AppendLine(") src")
        sql1.AppendLine("pivot")
        sql1.AppendLine("(")
        sql1.AppendLine("max([RESIN_VD_TOTAL_])")
        sql1.AppendLine("for entry_date in (' + @days + ')")
        sql1.AppendLine(") pvt_cont")
        sql1.AppendLine("")
        sql1.AppendLine("")
        sql1.AppendLine("union all")
        sql1.AppendLine("")
        sql1.AppendLine("select")
        sql1.AppendLine("''Resin WM(Total)'' as Row_name,")
        sql1.AppendLine("' + @days_for_select + '")
        sql1.AppendLine("from")
        sql1.AppendLine("(")
        sql1.AppendLine("select")
        sql1.AppendLine("RESIN_WM_TOTAL_")
        sql1.AppendLine(",entry_date")
        sql1.AppendLine("from [UserModules].[user].[mail_prepare]")
        sql1.AppendLine("where cast(entry_date as date) <= cast(getdate() as date)")
        sql1.AppendLine("and cast(entry_date as date) >= dateadd(day,-' + @day_diff + ', cast(getdate() as date))")
        sql1.AppendLine(") src")
        sql1.AppendLine("pivot")
        sql1.AppendLine("(")
        sql1.AppendLine("max([RESIN_WM_TOTAL_])")
        sql1.AppendLine("for entry_date in (' + @days + ')")
        sql1.AppendLine(") pvt_cont")
        sql1.AppendLine("")
        sql1.AppendLine("")
        sql1.AppendLine("union all")
        sql1.AppendLine("")
        sql1.AppendLine("select")
        sql1.AppendLine("''TV/WM Parts(Total)'' as Row_name,")
        sql1.AppendLine("' + @days_for_select + '")
        sql1.AppendLine("from")
        sql1.AppendLine("(")
        sql1.AppendLine("select")
        sql1.AppendLine("TV_WM_PARTS_TOTAL_")
        sql1.AppendLine(",entry_date")
        sql1.AppendLine("from [UserModules].[user].[mail_prepare]")
        sql1.AppendLine("where cast(entry_date as date) <= cast(getdate() as date)")
        sql1.AppendLine("and cast(entry_date as date) >= dateadd(day,-' + @day_diff + ', cast(getdate() as date))")
        sql1.AppendLine(") src")
        sql1.AppendLine("pivot")
        sql1.AppendLine("(")
        sql1.AppendLine("max([TV_WM_PARTS_TOTAL_])")
        sql1.AppendLine("for entry_date in (' + @days + ')")
        sql1.AppendLine(") pvt_cont")
        sql1.AppendLine("")
        sql1.AppendLine("")
        sql1.AppendLine("")
        sql1.AppendLine("union all")
        sql1.AppendLine("")
        sql1.AppendLine("select")
        sql1.AppendLine("''WM Parts(Total)'' as Row_name,")
        sql1.AppendLine("' + @days_for_select + '")
        sql1.AppendLine("from")
        sql1.AppendLine("(")
        sql1.AppendLine("select")
        sql1.AppendLine("WM_PARTS_TOTAL_")
        sql1.AppendLine(",entry_date")
        sql1.AppendLine("from [UserModules].[user].[mail_prepare]")
        sql1.AppendLine("where cast(entry_date as date) <= cast(getdate() as date)")
        sql1.AppendLine("and cast(entry_date as date) >= dateadd(day,-' + @day_diff + ', cast(getdate() as date))")
        sql1.AppendLine(") src")
        sql1.AppendLine("pivot")
        sql1.AppendLine("(")
        sql1.AppendLine("max([WM_PARTS_TOTAL_])")
        sql1.AppendLine("for entry_date in (' + @days + ')")
        sql1.AppendLine(") pvt_cont")
        sql1.AppendLine("")
        sql1.AppendLine("")
        sql1.AppendLine("")
        sql1.AppendLine("union all")
        sql1.AppendLine("")
        sql1.AppendLine("select")
        sql1.AppendLine("''TV Parts(Total)'' as Row_name,")
        sql1.AppendLine("' + @days_for_select + '")
        sql1.AppendLine("from")
        sql1.AppendLine("(")
        sql1.AppendLine("select")
        sql1.AppendLine("TV_PARTS_TOTAL_")
        sql1.AppendLine(",entry_date")
        sql1.AppendLine("from [UserModules].[user].[mail_prepare]")
        sql1.AppendLine("where cast(entry_date as date) <= cast(getdate() as date)")
        sql1.AppendLine("and cast(entry_date as date) >= dateadd(day,-' + @day_diff + ', cast(getdate() as date))")
        sql1.AppendLine(") src")
        sql1.AppendLine("pivot")
        sql1.AppendLine("(")
        sql1.AppendLine("max([TV_PARTS_TOTAL_])")
        sql1.AppendLine("for entry_date in (' + @days + ')")
        sql1.AppendLine(") pvt_cont")







        'Выборка строк demurrage
        sql1.AppendLine("union all")
        sql1.AppendLine("")
        sql1.AppendLine("select")
        sql1.AppendLine("''LCD Panels(Demurrage)'' as Row_name,")
        sql1.AppendLine("' + @days_for_select + '")
        sql1.AppendLine("from")
        sql1.AppendLine("(")
        sql1.AppendLine("select")
        sql1.AppendLine("LCD_PANELS_Demurrage_")
        sql1.AppendLine(",entry_date")
        sql1.AppendLine("from [UserModules].[user].[mail_prepare]")
        sql1.AppendLine("where cast(entry_date as date) <= cast(getdate() as date)")
        sql1.AppendLine("and cast(entry_date as date) >= dateadd(day,-' + @day_diff + ', cast(getdate() as date))")
        sql1.AppendLine(") src")
        sql1.AppendLine("pivot")
        sql1.AppendLine("(")
        sql1.AppendLine("max([LCD_PANELS_Demurrage_])")
        sql1.AppendLine("for entry_date in (' + @days + ')")
        sql1.AppendLine(") pvt_cont")
        sql1.AppendLine("")
        sql1.AppendLine("")
        sql1.AppendLine("union all")
        sql1.AppendLine("")
        sql1.AppendLine("select")
        sql1.AppendLine("''Resin VD(Demurrage)'' as Row_name,")
        sql1.AppendLine("' + @days_for_select + '")
        sql1.AppendLine("from")
        sql1.AppendLine("(")
        sql1.AppendLine("select")
        sql1.AppendLine("RESIN_VD_Demurrage_")
        sql1.AppendLine(",entry_date")
        sql1.AppendLine("from [UserModules].[user].[mail_prepare]")
        sql1.AppendLine("where cast(entry_date as date) <= cast(getdate() as date)")
        sql1.AppendLine("and cast(entry_date as date) >= dateadd(day,-' + @day_diff + ', cast(getdate() as date))")
        sql1.AppendLine(") src")
        sql1.AppendLine("pivot")
        sql1.AppendLine("(")
        sql1.AppendLine("max([RESIN_VD_Demurrage_])")
        sql1.AppendLine("for entry_date in (' + @days + ')")
        sql1.AppendLine(") pvt_cont")
        sql1.AppendLine("")
        sql1.AppendLine("")
        sql1.AppendLine("union all")
        sql1.AppendLine("")
        sql1.AppendLine("select")
        sql1.AppendLine("''Resin WM(Demurrage)'' as Row_name,")
        sql1.AppendLine("' + @days_for_select + '")
        sql1.AppendLine("from")
        sql1.AppendLine("(")
        sql1.AppendLine("select")
        sql1.AppendLine("RESIN_WM_Demurrage_")
        sql1.AppendLine(",entry_date")
        sql1.AppendLine("from [UserModules].[user].[mail_prepare]")
        sql1.AppendLine("where cast(entry_date as date) <= cast(getdate() as date)")
        sql1.AppendLine("and cast(entry_date as date) >= dateadd(day,-' + @day_diff + ', cast(getdate() as date))")
        sql1.AppendLine(") src")
        sql1.AppendLine("pivot")
        sql1.AppendLine("(")
        sql1.AppendLine("max([RESIN_WM_Demurrage_])")
        sql1.AppendLine("for entry_date in (' + @days + ')")
        sql1.AppendLine(") pvt_cont")
        sql1.AppendLine("")
        sql1.AppendLine("")
        sql1.AppendLine("union all")
        sql1.AppendLine("")
        sql1.AppendLine("select")
        sql1.AppendLine("''TV/WM Parts(Demurrage)'' as Row_name,")
        sql1.AppendLine("' + @days_for_select + '")
        sql1.AppendLine("from")
        sql1.AppendLine("(")
        sql1.AppendLine("select")
        sql1.AppendLine("TV_WM_PARTS_Demurrage_")
        sql1.AppendLine(",entry_date")
        sql1.AppendLine("from [UserModules].[user].[mail_prepare]")
        sql1.AppendLine("where cast(entry_date as date) <= cast(getdate() as date)")
        sql1.AppendLine("and cast(entry_date as date) >= dateadd(day,-' + @day_diff + ', cast(getdate() as date))")
        sql1.AppendLine(") src")
        sql1.AppendLine("pivot")
        sql1.AppendLine("(")
        sql1.AppendLine("max([TV_WM_PARTS_Demurrage_])")
        sql1.AppendLine("for entry_date in (' + @days + ')")
        sql1.AppendLine(") pvt_cont")
        sql1.AppendLine("")
        sql1.AppendLine("")
        sql1.AppendLine("")
        sql1.AppendLine("union all")
        sql1.AppendLine("")
        sql1.AppendLine("select")
        sql1.AppendLine("''WM Parts(Demurrage)'' as Row_name,")
        sql1.AppendLine("' + @days_for_select + '")
        sql1.AppendLine("from")
        sql1.AppendLine("(")
        sql1.AppendLine("select")
        sql1.AppendLine("WM_PARTS_Demurrage_")
        sql1.AppendLine(",entry_date")
        sql1.AppendLine("from [UserModules].[user].[mail_prepare]")
        sql1.AppendLine("where cast(entry_date as date) <= cast(getdate() as date)")
        sql1.AppendLine("and cast(entry_date as date) >= dateadd(day,-' + @day_diff + ', cast(getdate() as date))")
        sql1.AppendLine(") src")
        sql1.AppendLine("pivot")
        sql1.AppendLine("(")
        sql1.AppendLine("max([WM_PARTS_Demurrage_])")
        sql1.AppendLine("for entry_date in (' + @days + ')")
        sql1.AppendLine(") pvt_cont")
        sql1.AppendLine("")
        sql1.AppendLine("")
        sql1.AppendLine("")
        sql1.AppendLine("union all")
        sql1.AppendLine("")
        sql1.AppendLine("select")
        sql1.AppendLine("''TV Parts(Demurrage)'' as Row_name,")
        sql1.AppendLine("' + @days_for_select + '")
        sql1.AppendLine("from")
        sql1.AppendLine("(")
        sql1.AppendLine("select")
        sql1.AppendLine("TV_PARTS_Demurrage_")
        sql1.AppendLine(",entry_date")
        sql1.AppendLine("from [UserModules].[user].[mail_prepare]")
        sql1.AppendLine("where cast(entry_date as date) <= cast(getdate() as date)")
        sql1.AppendLine("and cast(entry_date as date) >= dateadd(day,-' + @day_diff + ', cast(getdate() as date))")
        sql1.AppendLine(") src")
        sql1.AppendLine("pivot")
        sql1.AppendLine("(")
        sql1.AppendLine("max([TV_PARTS_Demurrage_])")
        sql1.AppendLine("for entry_date in (' + @days + ')")
        sql1.AppendLine(") pvt_cont")







        sql1.AppendLine("'")
        sql1.AppendLine("exec (@sql)")



        sql2.AppendLine("declare @day_diff int")
        sql2.AppendLine("set @day_diff = 60")
        sql2.AppendLine("")
        sql2.AppendLine("")
        sql2.AppendLine("select last_month.row_name")
        sql2.AppendLine("	,monthly_sum_1")
        sql2.AppendLine("	,monthly_sum_2")
        sql2.AppendLine("from (")
        sql2.AppendLine("	select")
        sql2.AppendLine("	'From SPB port to Vorsino by train' as row_name")
        sql2.AppendLine("	,count(isnull(t1.container,0) ) as monthly_sum_1")
        sql2.AppendLine("	from [UserModules].[user].[MAIL_PREPARE_TRAIN_BY_MONTH] t1")
        sql2.AppendLine("	where cast(entry_date as date) <= DATEADD(month, ((YEAR(cast(getdate() as date)) - 1900) * 12) + MONTH(cast(getdate() as date))-1, -1)")
        sql2.AppendLine("	and cast(entry_date as date) >= dateadd(day,-@day_diff , cast(getdate() as date))")
        sql2.AppendLine(") last_month")
        sql2.AppendLine("left join")
        sql2.AppendLine("(")
        sql2.AppendLine("	select")
        sql2.AppendLine("	'From SPB port to Vorsino by train' as row_name")
        sql2.AppendLine("	,count(isnull(container,0) ) as monthly_sum_2")
        sql2.AppendLine("	from [UserModules].[user].[MAIL_PREPARE_TRAIN_BY_MONTH]")
        sql2.AppendLine("	where datepart(month,cast(entry_date as date)) = datepart(month, cast(getdate() as date))")
        sql2.AppendLine("	and datepart(year,cast(entry_date as date)) = datepart(year, cast(getdate() as date))")
        sql2.AppendLine(") current_month")
        sql2.AppendLine("on last_month.row_name = current_month.row_name")
        sql2.AppendLine("")
        sql2.AppendLine("")
        sql2.AppendLine("union all")
        sql2.AppendLine("")
        sql2.AppendLine("select last_month.row_name")
        sql2.AppendLine("	,monthly_sum_1")
        sql2.AppendLine("	,monthly_sum_2")
        sql2.AppendLine("from (")
        sql2.AppendLine("	select")
        sql2.AppendLine("	'Total calculated (RUB)' as row_name")
        sql2.AppendLine("	,sum(cast(isnull(t1.total_calculated,0) as numeric(20,0))) as monthly_sum_1")
        sql2.AppendLine("	from [UserModules].[user].[mail_prepare] t1")
        sql2.AppendLine("	where cast(entry_date as date) <= DATEADD(month, ((YEAR(cast(getdate() as date)) - 1900) * 12) + MONTH(cast(getdate() as date))-1, -1)")
        sql2.AppendLine("	and cast(entry_date as date) >= dateadd(day,-@day_diff , cast(getdate() as date))")
        sql2.AppendLine(") last_month")
        sql2.AppendLine("left join")
        sql2.AppendLine("(")
        sql2.AppendLine("	select")
        sql2.AppendLine("	'Total calculated (RUB)' as row_name")
        sql2.AppendLine("	,sum(cast(isnull(total_calculated,0) as numeric(20,0))) as monthly_sum_2")
        sql2.AppendLine("	from [UserModules].[user].[mail_prepare]")
        sql2.AppendLine("	where datepart(month,cast(entry_date as date)) = datepart(month, cast(getdate() as date))")
        sql2.AppendLine("	and datepart(year,cast(entry_date as date)) = datepart(year, cast(getdate() as date))")
        sql2.AppendLine(") current_month")
        sql2.AppendLine("on last_month.row_name = current_month.row_name")
        sql2.AppendLine("")
        sql2.AppendLine("")
        sql2.AppendLine("union all")
        sql2.AppendLine("")
        sql2.AppendLine("")
        sql2.AppendLine("select last_month.row_name")
        sql2.AppendLine("	,monthly_sum_1")
        sql2.AppendLine("	,monthly_sum_2")
        sql2.AppendLine("from (")
        sql2.AppendLine("	select")
        sql2.AppendLine("	'STP->ODCY(q-ty)' as row_name")
        sql2.AppendLine("	,sum(cast(isnull(t1.stp_qty_,0) as numeric(20,0))) as monthly_sum_1")
        sql2.AppendLine("	from [UserModules].[user].[mail_prepare] t1")
        sql2.AppendLine("	where cast(entry_date as date) <= DATEADD(month, ((YEAR(cast(getdate() as date)) - 1900) * 12) + MONTH(cast(getdate() as date))-1, -1)")
        sql2.AppendLine("	and cast(entry_date as date) >= dateadd(day,-@day_diff, cast(getdate() as date))")
        sql2.AppendLine(") last_month")
        sql2.AppendLine("")
        sql2.AppendLine("left join")
        sql2.AppendLine("(")
        sql2.AppendLine("	select")
        sql2.AppendLine("	'STP->ODCY(q-ty)' as row_name")
        sql2.AppendLine("	,sum(cast(isnull(stp_qty_,0) as numeric(20,0))) as monthly_sum_2")
        sql2.AppendLine("	from [UserModules].[user].[mail_prepare]")
        sql2.AppendLine("	where datepart(month,cast(entry_date as date)) = datepart(month, cast(getdate() as date))")
        sql2.AppendLine("	and datepart(year,cast(entry_date as date)) = datepart(year, cast(getdate() as date))")
        sql2.AppendLine(") current_month")
        sql2.AppendLine("on last_month.row_name = current_month.row_name")
        sql2.AppendLine("")
        sql2.AppendLine("")
        sql2.AppendLine("union all")
        sql2.AppendLine("")
        sql2.AppendLine("")
        sql2.AppendLine("select last_month.row_name")
        sql2.AppendLine("	,monthly_sum_1")
        sql2.AppendLine("	,monthly_sum_2")
        sql2.AppendLine("from (")
        sql2.AppendLine("")
        sql2.AppendLine("	select")
        sql2.AppendLine("	'STP->ODCY' as row_name")
        sql2.AppendLine("	,sum(cast(isnull(t1.stp_money_,0) as numeric(20,0))) as monthly_sum_1")
        sql2.AppendLine("	from [UserModules].[user].[mail_prepare] t1")
        sql2.AppendLine("	where cast(entry_date as date) <= DATEADD(month, ((YEAR(cast(getdate() as date)) - 1900) * 12) + MONTH(cast(getdate() as date))-1, -1)")
        sql2.AppendLine("	and cast(entry_date as date) >= dateadd(day,-@day_diff, cast(getdate() as date))")
        sql2.AppendLine(") last_month")
        sql2.AppendLine("")
        sql2.AppendLine("")
        sql2.AppendLine("left join")
        sql2.AppendLine("(")
        sql2.AppendLine("	select")
        sql2.AppendLine("	'STP->ODCY' as row_name")
        sql2.AppendLine("	,sum(cast(isnull(stp_money_,0) as numeric(20,0))) as monthly_sum_2")
        sql2.AppendLine("	from [UserModules].[user].[mail_prepare]")
        sql2.AppendLine("	where datepart(month,cast(entry_date as date)) = datepart(month, cast(getdate() as date))")
        sql2.AppendLine("	and datepart(year,cast(entry_date as date)) = datepart(year, cast(getdate() as date))")
        sql2.AppendLine(") current_month")
        sql2.AppendLine("on last_month.row_name = current_month.row_name")
        sql2.AppendLine("")
        sql2.AppendLine("")


        sql3.AppendLine("--Demurrage - расчёт стоимости для каждого контейнера, который сейчас находится в простое")
        sql3.AppendLine("declare @cont_tab as table (carrier_id int, container varchar(max), keep_time int)")
        sql3.AppendLine("declare @result_tab as table (carrier_id int, container varchar(max), keep_time int, demurrage_fee_rub int, demurrage_fee_usd numeric(20,3))")
        sql3.AppendLine("declare @carrier int")
        sql3.AppendLine("declare @container varchar(max)")
        sql3.AppendLine("declare @keep_time int")
        sql3.AppendLine("declare @i int")
        sql3.AppendLine("declare @current_amt int")
        sql3.AppendLine("")
        sql3.AppendLine("")
        sql3.AppendLine("DECLARE cont_CURSOR CURSOR FOR")
        sql3.AppendLine("(")
        sql3.AppendLine("--Общий результат")
        sql3.AppendLine("select")
        sql3.AppendLine("--count(container)")
        sql3.AppendLine("CARRIER_ID")
        sql3.AppendLine(",container")
        sql3.AppendLine("--,arrival_date")
        sql3.AppendLine("--,total_free_days")
        sql3.AppendLine(",keep_time")
        sql3.AppendLine("from")
        sql3.AppendLine("(")
        sql3.AppendLine("")
        sql3.AppendLine("--Выборка контейнеров и необходимой информации для них, которые НЕ НАХОДЯТСЯ на ODCY")
        sql3.AppendLine("		select")
        sql3.AppendLine("		info.CARRIER_ID")
        sql3.AppendLine("		,info.container")
        sql3.AppendLine("		,info.ARRIVAL_DATE")
        sql3.AppendLine("		,case")
        sql3.AppendLine("				when patindex('%/%', info.CONDITION) <> 0 then")
        sql3.AppendLine("				substring(info.CONDITION, 1, patindex('%/%', info.CONDITION)-1)")
        sql3.AppendLine("				when patindex('%/%', info.CONDITION) = 0 then")
        sql3.AppendLine("				info.CONDITION")
        sql3.AppendLine("		end as total_free_days")
        sql3.AppendLine("		,datediff(day, cast(arrival_date as date), cast(getdate() as date)) + 1 as keep_time")
        sql3.AppendLine("")
        sql3.AppendLine("")
        sql3.AppendLine("		from [UserModules].[user].[ITEM_INFORMATION] info")
        sql3.AppendLine("")
        sql3.AppendLine("		left join [UserModules].[user].[DESTINATION_MASTER] [master]")
        sql3.AppendLine("		on info.CARRIER_ID = [master].id")
        sql3.AppendLine("		where")
        sql3.AppendLine("		info.[status] is not null")
        sql3.AppendLine("		and info.[status] <> ''")
        sql3.AppendLine("		and info.[status] <> 'N/A'")
        sql3.AppendLine("		and info.[status] <> 'In transit'")
        sql3.AppendLine("		and ([master].IS_ODCY <> 'Y' or [master].IS_ODCY is null)")
        sql3.AppendLine("")
        sql3.AppendLine("		UNION ALL")
        sql3.AppendLine("		--Выборка контейнеров и необходимой информации для них, которые НАХОДЯТСЯ на ODCY")
        sql3.AppendLine("		select")
        sql3.AppendLine("		CARRIER_ID")
        sql3.AppendLine("		,CONTAINER")
        sql3.AppendLine("		,ARRIVAL_DATE")
        sql3.AppendLine("		,total_free_days")
        sql3.AppendLine("		,datediff(day, cast(arrival_date as date), cast(getdate() as date)) + 1 as keep_time")
        sql3.AppendLine("		from")
        sql3.AppendLine("		(")
        sql3.AppendLine("")
        sql3.AppendLine("				--Парсинг условий из ранее сформированной таблицы и преобразование их в int. Вычисление общей суммы бесплатного срока хранения")
        sql3.AppendLine("				select")
        sql3.AppendLine("				CARRIER_ID")
        sql3.AppendLine("				,container")
        sql3.AppendLine("				,arrival_date")
        sql3.AppendLine("				,condition")
        sql3.AppendLine("				,parrent_id")
        sql3.AppendLine("				,parrent_condition")
        sql3.AppendLine("				--Условия хранения")
        sql3.AppendLine("				,case when isnumeric --Если это число")
        sql3.AppendLine("				(")
        sql3.AppendLine("					condition_parc")
        sql3.AppendLine("				) = 1")
        sql3.AppendLine("")
        sql3.AppendLine("				and isnumeric --И это число")
        sql3.AppendLine("				(")
        sql3.AppendLine("					parrent_condition_parc")
        sql3.AppendLine("				) = 1")
        sql3.AppendLine("				then --Тогда считаем общую сумму бесплатного хранения")
        sql3.AppendLine("					cast(condition_parc as int)")
        sql3.AppendLine("					+")
        sql3.AppendLine("					cast(parrent_condition_parc as int)")
        sql3.AppendLine("				end as total_free_days")
        sql3.AppendLine("")
        sql3.AppendLine("				from")
        sql3.AppendLine("				--Выборка контейнеров ТОЛЬКО на ODCY, определение их родительских портов и условий хранение в родительском порту + ODCY")
        sql3.AppendLine("				(")
        sql3.AppendLine("						select")
        sql3.AppendLine("						info.CARRIER_ID")
        sql3.AppendLine("						,info.CONTAINER")
        sql3.AppendLine("						,info.ARRIVAL_DATE")
        sql3.AppendLine("						,info.CONDITION")
        sql3.AppendLine("						,[master].parrent_port_id as parrent_id")
        sql3.AppendLine("						--Следующая строка повторяется несколько раз в case. При изменении её нужно подставить в case")
        sql3.AppendLine("						,(select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id) as parrent_condition")
        sql3.AppendLine("						,case")
        sql3.AppendLine("							when patindex('%/%', info.CONDITION) <> 0 then")
        sql3.AppendLine("							substring(info.CONDITION, 1, patindex('%/%', info.CONDITION)-1)")
        sql3.AppendLine("							when patindex('%/%', info.CONDITION) = 0 then")
        sql3.AppendLine("							info.CONDITION")
        sql3.AppendLine("						end as condition_parc")
        sql3.AppendLine("						,case")
        sql3.AppendLine("							when patindex('%/%', (select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id)) <> 0 then")
        sql3.AppendLine("							substring((select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id), 1, patindex('%/%', (select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id))-1)")
        sql3.AppendLine("							when patindex('%/%', (select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id)) = 0 then")
        sql3.AppendLine("							(select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id)")
        sql3.AppendLine("						end as parrent_condition_parc")
        sql3.AppendLine("")
        sql3.AppendLine("						from [UserModules].[user].[ITEM_INFORMATION] info")
        sql3.AppendLine("")
        sql3.AppendLine("						left join [UserModules].[user].[DESTINATION_MASTER] [master]")
        sql3.AppendLine("						on [master].id = info.CARRIER_ID")
        sql3.AppendLine("")
        sql3.AppendLine("						--left join [UserModules].[user].[DESTINATION_MASTER] slave")
        sql3.AppendLine("						--on [master].id = [slave].ODCY_ID")
        sql3.AppendLine("")
        sql3.AppendLine("					where [master].IS_ODCY = 'Y'")
        sql3.AppendLine("				) t")
        sql3.AppendLine("		)tt")
        sql3.AppendLine(")ttt")
        sql3.AppendLine("where keep_time > 0")
        sql3.AppendLine("and keep_time > total_free_days")
        sql3.AppendLine("and LEN(container) = 11")
        sql3.AppendLine("--and CARRIER_ID = 33")
        sql3.AppendLine("")
        sql3.AppendLine(")")
        sql3.AppendLine("")
        sql3.AppendLine("")
        sql3.AppendLine("OPEN cont_CURSOR")
        sql3.AppendLine("FETCH NEXT FROM cont_cursor INTO @carrier, @container, @keep_time")
        sql3.AppendLine("")
        sql3.AppendLine("WHILE @@FETCH_STATUS = 0")
        sql3.AppendLine("BEGIN")
        sql3.AppendLine("	set @i = 1")
        sql3.AppendLine("	set @current_amt = 0")
        sql3.AppendLine("	--print @carrier")
        sql3.AppendLine("	--print @container")
        sql3.AppendLine("	--print @keep_time")
        sql3.AppendLine("	--print ' '")
        sql3.AppendLine("	while @i <= @keep_time")
        sql3.AppendLine("		begin")
        sql3.AppendLine("			set @current_amt = @current_amt + (")
        sql3.AppendLine("			select")
        sql3.AppendLine("			isnull(max(price), 0)")
        sql3.AppendLine("			from [UserModules].[user].[DEMURRAGE_PRICE]")
        sql3.AppendLine("			where carrier_id = @carrier")
        sql3.AppendLine("			and [days] < @i")
        sql3.AppendLine("			)")
        sql3.AppendLine("			set @i = @i + 1")
        sql3.AppendLine("		end")
        sql3.AppendLine("		insert into @result_tab (carrier_id, container, keep_time, demurrage_fee_rub, demurrage_fee_usd) values (@carrier, @container, @keep_time, @current_amt, @current_amt / (select cast(EXCH_RATE as numeric(20,3)) from [UserModules].[user].[CURRENCY] where [from] = 'USD' and [to] = 'RUB'))")
        sql3.AppendLine("	FETCH NEXT FROM cont_cursor INTO @carrier, @container, @keep_time")
        sql3.AppendLine("END")
        sql3.AppendLine("")
        sql3.AppendLine("CLOSE cont_CURSOR")
        sql3.AppendLine("DEALLOCATE cont_CURSOR")
        sql3.AppendLine("")
        sql3.AppendLine("----------Вот здесь можно посмотреть, что попадает в demurrage, и сколько это стоит")
        sql3.AppendLine("--select *")
        sql3.AppendLine("--from @result_tab")
        sql3.AppendLine("")
        sql3.AppendLine("")
        sql3.AppendLine("select")
        sql3.AppendLine("'Ust-Luga+St.Pet(Port)' as Devision")
        sql3.AppendLine("--,info.CARRIER_ID as carrier")
        sql3.AppendLine("--,info.container as container")
        sql3.AppendLine("--,result.demurrage_fee_rub")
        sql3.AppendLine("--,result.demurrage_fee_usd")
        sql3.AppendLine(",count (info.container) as Container")
        sql3.AppendLine(",count (result.container) as Demurrage")
        sql3.AppendLine(",isnull(sum(demurrage_fee_rub),0) as [Cost(rub)]")
        sql3.AppendLine(",isnull(sum(demurrage_fee_usd),0) as [Cost($)]")
        sql3.AppendLine("")
        sql3.AppendLine("from [UserModules].[user].[ITEM_INFORMATION] info")
        sql3.AppendLine("")
        sql3.AppendLine("left join @result_tab result")
        sql3.AppendLine("on info.CONTAINER collate Cyrillic_General_CI_AS = result.container collate Cyrillic_General_CI_AS")
        sql3.AppendLine("")
        sql3.AppendLine("where")
        sql3.AppendLine("(")
        sql3.AppendLine("[STATUS] = 'CC finished'")
        sql3.AppendLine("or [status] = 'not CC'")
        sql3.AppendLine("or ([status] = 'In transit' and cast(arrival_date as date) = cast(getdate()as date))")
        sql3.AppendLine(")")
        sql3.AppendLine("and LEN(info.container) = 11")
        sql3.AppendLine("and cast(do_date as date) <> cast(getdate() as date)")
        sql3.AppendLine("and info.CARRIER_ID in (60,62,64)")
        sql3.AppendLine("")
        sql3.AppendLine("union all")
        sql3.AppendLine("")
        sql3.AppendLine("select")
        sql3.AppendLine("'ODCY STP + DRY dock' as Devision")
        sql3.AppendLine("--,info.CARRIER_ID as carrier")
        sql3.AppendLine("--,info.container as container")
        sql3.AppendLine("--,result.demurrage_fee_rub")
        sql3.AppendLine("--,result.demurrage_fee_usd")
        sql3.AppendLine(",count (info.container) as Container")
        sql3.AppendLine(",count (result.container) as Demurrage")
        sql3.AppendLine(",isnull(sum(demurrage_fee_rub),0) as [Cost(rub)]")
        sql3.AppendLine(",isnull(sum(demurrage_fee_usd),0) as [Cost($)]")
        sql3.AppendLine("")
        sql3.AppendLine("from [UserModules].[user].[ITEM_INFORMATION] info")
        sql3.AppendLine("")
        sql3.AppendLine("left join @result_tab result")
        sql3.AppendLine("on info.CONTAINER collate Cyrillic_General_CI_AS = result.container collate Cyrillic_General_CI_AS")
        sql3.AppendLine("")
        sql3.AppendLine("where")
        sql3.AppendLine("(")
        sql3.AppendLine("[STATUS] = 'CC finished'")
        sql3.AppendLine("or [status] = 'not CC'")
        sql3.AppendLine("or ([status] = 'In transit' and cast(arrival_date as date) = cast(getdate()as date))")
        sql3.AppendLine(")")
        sql3.AppendLine("and LEN(info.container) = 11")
        sql3.AppendLine("and cast(do_date as date) <> cast(getdate() as date)")
        sql3.AppendLine("and info.CARRIER_ID in (61, 63)")
        sql3.AppendLine("")
        sql3.AppendLine("union all")
        sql3.AppendLine("")
        sql3.AppendLine("select")
        sql3.AppendLine("'Novorossiysk(Port)' as Devision")
        sql3.AppendLine("--,info.CARRIER_ID as carrier")
        sql3.AppendLine("--,info.container as container")
        sql3.AppendLine("--,result.demurrage_fee_rub")
        sql3.AppendLine("--,result.demurrage_fee_usd")
        sql3.AppendLine(",count (info.container) as Container")
        sql3.AppendLine(",count (result.container) as Demurrage")
        sql3.AppendLine(",isnull(sum(demurrage_fee_rub),0) as [Cost(rub)]")
        sql3.AppendLine(",isnull(sum(demurrage_fee_usd),0) as [Cost($)]")
        sql3.AppendLine("")
        sql3.AppendLine("from [UserModules].[user].[ITEM_INFORMATION] info")
        sql3.AppendLine("")
        sql3.AppendLine("left join @result_tab result")
        sql3.AppendLine("on info.CONTAINER collate Cyrillic_General_CI_AS = result.container collate Cyrillic_General_CI_AS")
        sql3.AppendLine("")
        sql3.AppendLine("where")
        sql3.AppendLine("(")
        sql3.AppendLine("[STATUS] = 'CC finished'")
        sql3.AppendLine("or [status] = 'not CC'")
        sql3.AppendLine("or ([status] = 'In transit' and cast(arrival_date as date) = cast(getdate()as date))")
        sql3.AppendLine(")")
        sql3.AppendLine("and LEN(info.container) = 11")
        sql3.AppendLine("and cast(do_date as date) <> cast(getdate() as date)")
        sql3.AppendLine("and info.CARRIER_ID in (65)")
        sql3.AppendLine("")
        sql3.AppendLine("union all")
        sql3.AppendLine("")
        sql3.AppendLine("select")
        sql3.AppendLine("'Moscow(Khovr+Pavelet)' as Devision")
        sql3.AppendLine("--,info.CARRIER_ID as carrier")
        sql3.AppendLine("--,info.container as container")
        sql3.AppendLine("--,result.demurrage_fee_rub")
        sql3.AppendLine("--,result.demurrage_fee_usd")
        sql3.AppendLine(",count (info.container) as Container")
        sql3.AppendLine(",count (result.container) as Demurrage")
        sql3.AppendLine(",isnull(sum(demurrage_fee_rub),0) as [Cost(rub)]")
        sql3.AppendLine(",isnull(sum(demurrage_fee_usd),0) as [Cost($)]")
        sql3.AppendLine("")
        sql3.AppendLine("from [UserModules].[user].[ITEM_INFORMATION] info")
        sql3.AppendLine("")
        sql3.AppendLine("left join @result_tab result")
        sql3.AppendLine("on info.CONTAINER collate Cyrillic_General_CI_AS = result.container collate Cyrillic_General_CI_AS")
        sql3.AppendLine("")
        sql3.AppendLine("where")
        sql3.AppendLine("(")
        sql3.AppendLine("[STATUS] = 'CC finished'")
        sql3.AppendLine("or [status] = 'not CC'")
        sql3.AppendLine("or ([status] = 'In transit' and cast(arrival_date as date) = cast(getdate()as date))")
        sql3.AppendLine(")")
        sql3.AppendLine("and LEN(info.container) = 11")
        sql3.AppendLine("and cast(do_date as date) <> cast(getdate() as date)")
        sql3.AppendLine("and info.CARRIER_ID in (67, 70)")
        sql3.AppendLine("")
        sql3.AppendLine("union all")
        sql3.AppendLine("")
        sql3.AppendLine("select")
        sql3.AppendLine("'Kupavna(TMGR+TSR)' as Devision")
        sql3.AppendLine("--,info.CARRIER_ID as carrier")
        sql3.AppendLine("--,info.container as container")
        sql3.AppendLine("--,result.demurrage_fee_rub")
        sql3.AppendLine("--,result.demurrage_fee_usd")
        sql3.AppendLine(",count (info.container) as Container")
        sql3.AppendLine(",count (result.container) as Demurrage")
        sql3.AppendLine(",isnull(sum(demurrage_fee_rub),0) as [Cost(rub)]")
        sql3.AppendLine(",isnull(sum(demurrage_fee_usd),0) as [Cost($)]")
        sql3.AppendLine("")
        sql3.AppendLine("from [UserModules].[user].[ITEM_INFORMATION] info")
        sql3.AppendLine("")
        sql3.AppendLine("left join @result_tab result")
        sql3.AppendLine("on info.CONTAINER collate Cyrillic_General_CI_AS = result.container collate Cyrillic_General_CI_AS")
        sql3.AppendLine("")
        sql3.AppendLine("where")
        sql3.AppendLine("(")
        sql3.AppendLine("[STATUS] = 'CC finished'")
        sql3.AppendLine("or [status] = 'not CC'")
        sql3.AppendLine("or ([status] = 'In transit' and cast(arrival_date as date) = cast(getdate()as date))")
        sql3.AppendLine(")")
        sql3.AppendLine("and LEN(info.container) = 11")
        sql3.AppendLine("and cast(do_date as date) <> cast(getdate() as date)")
        sql3.AppendLine("and info.CARRIER_ID in (0)")
        sql3.AppendLine("")
        sql3.AppendLine("union all")
        sql3.AppendLine("")
        sql3.AppendLine("select")
        sql3.AppendLine("'Silikatnaya(TSR)' as Devision")
        sql3.AppendLine("--,info.CARRIER_ID as carrier")
        sql3.AppendLine("--,info.container as container")
        sql3.AppendLine("--,result.demurrage_fee_rub")
        sql3.AppendLine("--,result.demurrage_fee_usd")
        sql3.AppendLine(",count (info.container) as Container")
        sql3.AppendLine(",count (result.container) as Demurrage")
        sql3.AppendLine(",isnull(sum(demurrage_fee_rub),0) as [Cost(rub)]")
        sql3.AppendLine(",isnull(sum(demurrage_fee_usd),0) as [Cost($)]")
        sql3.AppendLine("")
        sql3.AppendLine("from [UserModules].[user].[ITEM_INFORMATION] info")
        sql3.AppendLine("")
        sql3.AppendLine("left join @result_tab result")
        sql3.AppendLine("on info.CONTAINER collate Cyrillic_General_CI_AS = result.container collate Cyrillic_General_CI_AS")
        sql3.AppendLine("")
        sql3.AppendLine("where")
        sql3.AppendLine("(")
        sql3.AppendLine("[STATUS] = 'CC finished'")
        sql3.AppendLine("or [status] = 'not CC'")
        sql3.AppendLine("or ([status] = 'In transit' and cast(arrival_date as date) = cast(getdate()as date))")
        sql3.AppendLine(")")
        sql3.AppendLine("and LEN(info.container) = 11")
        sql3.AppendLine("and cast(do_date as date) <> cast(getdate() as date)")
        sql3.AppendLine("and info.CARRIER_ID in (71)")
        sql3.AppendLine("")
        sql3.AppendLine("union all")
        sql3.AppendLine("")
        sql3.AppendLine("select")
        sql3.AppendLine("'Tuchkovo(TSR)' as Devision")
        sql3.AppendLine("--,info.CARRIER_ID as carrier")
        sql3.AppendLine("--,info.container as container")
        sql3.AppendLine("--,result.demurrage_fee_rub")
        sql3.AppendLine("--,result.demurrage_fee_usd")
        sql3.AppendLine(",count (info.container) as Container")
        sql3.AppendLine(",count (result.container) as Demurrage")
        sql3.AppendLine(",isnull(sum(demurrage_fee_rub),0) as [Cost(rub)]")
        sql3.AppendLine(",isnull(sum(demurrage_fee_usd),0) as [Cost($)]")
        sql3.AppendLine("")
        sql3.AppendLine("from [UserModules].[user].[ITEM_INFORMATION] info")
        sql3.AppendLine("")
        sql3.AppendLine("left join @result_tab result")
        sql3.AppendLine("on info.CONTAINER collate Cyrillic_General_CI_AS = result.container collate Cyrillic_General_CI_AS")
        sql3.AppendLine("")
        sql3.AppendLine("where")
        sql3.AppendLine("(")
        sql3.AppendLine("[STATUS] = 'CC finished'")
        sql3.AppendLine("or [status] = 'not CC'")
        sql3.AppendLine("or ([status] = 'In transit' and cast(arrival_date as date) = cast(getdate()as date))")
        sql3.AppendLine(")")
        sql3.AppendLine("and LEN(info.container) = 11")
        sql3.AppendLine("and cast(do_date as date) <> cast(getdate() as date)")
        sql3.AppendLine("and info.CARRIER_ID in (66, 69)")
        sql3.AppendLine("")
        sql3.AppendLine("union all")
        sql3.AppendLine("")
        sql3.AppendLine("select")
        sql3.AppendLine("'Vorsino(TMR+TSR)' as Devision")
        sql3.AppendLine("--,info.CARRIER_ID as carrier")
        sql3.AppendLine("--,info.container as container")
        sql3.AppendLine("--,result.demurrage_fee_rub")
        sql3.AppendLine("--,result.demurrage_fee_usd")
        sql3.AppendLine(",count (info.container) as Container")
        sql3.AppendLine(",count (result.container) as Demurrage")
        sql3.AppendLine(",isnull(sum(demurrage_fee_rub),0) as [Cost(rub)]")
        sql3.AppendLine(",isnull(sum(demurrage_fee_usd),0) as [Cost($)]")
        sql3.AppendLine("")
        sql3.AppendLine("from [UserModules].[user].[ITEM_INFORMATION] info")
        sql3.AppendLine("")
        sql3.AppendLine("left join @result_tab result")
        sql3.AppendLine("on info.CONTAINER collate Cyrillic_General_CI_AS = result.container collate Cyrillic_General_CI_AS")
        sql3.AppendLine("")
        sql3.AppendLine("where")
        sql3.AppendLine("(")
        sql3.AppendLine("[STATUS] = 'CC finished'")
        sql3.AppendLine("or [status] = 'not CC'")
        sql3.AppendLine("or ([status] = 'In transit' and cast(arrival_date as date) = cast(getdate()as date))")
        sql3.AppendLine(")")
        sql3.AppendLine("and LEN(info.container) = 11")
        sql3.AppendLine("and cast(do_date as date) <> cast(getdate() as date)")
        sql3.AppendLine("and info.CARRIER_ID in (68)")



        sql4.AppendLine("--Demmurage")
        sql4.AppendLine("	--Общий результат")
        sql4.AppendLine("	select")
        sql4.AppendLine("	--count(container)")
        sql4.AppendLine("	provider as [Provider]")
        sql4.AppendLine("	,container as [Truck/Cont/Air invoice]")
        sql4.AppendLine("	,CONDITION as [ST(MAX)]")
        sql4.AppendLine("	,cargo as [Cargo]")
        sql4.AppendLine("	,arrival_date as [Arrival date (Post/Station)]")
        sql4.AppendLine("	,[STATUS] as [Status]")
        sql4.AppendLine("	--,total_free_days")
        sql4.AppendLine("	,keep_time as [CY storage, days]")
        sql4.AppendLine("	,total_free_days - keep_time as [Days left]")
        sql4.AppendLine("	from")
        sql4.AppendLine("	(")
        sql4.AppendLine("")
        sql4.AppendLine("	--Выборка контейнеров и необходимой информации для них, которые НЕ НАХОДЯТСЯ на ODCY")
        sql4.AppendLine("			select")
        sql4.AppendLine("			[master].PROVIDER")
        sql4.AppendLine("			,info.container")
        sql4.AppendLine("			,info.CONDITION")
        sql4.AppendLine("			,info.CARGO")
        sql4.AppendLine("			,info.ARRIVAL_DATE")
        sql4.AppendLine("			,info.[STATUS]")
        sql4.AppendLine("			,case")
        sql4.AppendLine("					when patindex('%/%', info.CONDITION) <> 0 then")
        sql4.AppendLine("					substring(info.CONDITION, 1, patindex('%/%', info.CONDITION)-1)")
        sql4.AppendLine("					when patindex('%/%', info.CONDITION) = 0 then")
        sql4.AppendLine("					info.CONDITION")
        sql4.AppendLine("			end as total_free_days")
        sql4.AppendLine("			,datediff(day, cast(arrival_date as date), cast(getdate() as date)) + 1 as keep_time")
        sql4.AppendLine("")
        sql4.AppendLine("")
        sql4.AppendLine("			from [UserModules].[user].[ITEM_INFORMATION] info")
        sql4.AppendLine("")
        sql4.AppendLine("			left join [UserModules].[user].[DESTINATION_MASTER] [master]")
        sql4.AppendLine("			on info.CARRIER_ID = [master].id")
        sql4.AppendLine("			where")
        sql4.AppendLine("			info.[status] is not null")
        sql4.AppendLine("			and info.[status] <> ''")
        sql4.AppendLine("			and info.[status] <> 'N/A'")
        sql4.AppendLine("			and info.[status] <> 'In transit'")
        sql4.AppendLine("			and ([master].IS_ODCY <> 'Y' or [master].IS_ODCY is null)")
        sql4.AppendLine("")
        sql4.AppendLine("			UNION ALL")
        sql4.AppendLine("			--Выборка контейнеров и необходимой информации для них, которые НАХОДЯТСЯ на ODCY")
        sql4.AppendLine("			select")
        sql4.AppendLine("			provider")
        sql4.AppendLine("			,CONTAINER")
        sql4.AppendLine("			,CONDITION")
        sql4.AppendLine("			,CARGO")
        sql4.AppendLine("			,ARRIVAL_DATE")
        sql4.AppendLine("			,[STATUS]")
        sql4.AppendLine("			,total_free_days")
        sql4.AppendLine("			,datediff(day, cast(arrival_date as date), cast(getdate() as date)) + 1 as keep_time")
        sql4.AppendLine("			from")
        sql4.AppendLine("			(")
        sql4.AppendLine("")
        sql4.AppendLine("					--Парсинг условий из ранее сформированной таблицы и преобразование их в int. Вычисление общей суммы бесплатного срока хранения")
        sql4.AppendLine("					select")
        sql4.AppendLine("					PROVIDER")
        sql4.AppendLine("					,container")
        sql4.AppendLine("					,CONDITION")
        sql4.AppendLine("					,CARGO")
        sql4.AppendLine("					,ARRIVAL_DATE")
        sql4.AppendLine("					,[STATUS]")
        sql4.AppendLine("					,parrent_id")
        sql4.AppendLine("					,parrent_condition")
        sql4.AppendLine("					--Условия хранения")
        sql4.AppendLine("					,case when isnumeric --Если это число")
        sql4.AppendLine("					(")
        sql4.AppendLine("						condition_parc")
        sql4.AppendLine("					) = 1")
        sql4.AppendLine("")
        sql4.AppendLine("					and isnumeric --И это число")
        sql4.AppendLine("					(")
        sql4.AppendLine("						parrent_condition_parc")
        sql4.AppendLine("					) = 1")
        sql4.AppendLine("					then --Тогда считаем общую сумму бесплатного хранения")
        sql4.AppendLine("						cast(condition_parc as int)")
        sql4.AppendLine("						+")
        sql4.AppendLine("						cast(parrent_condition_parc as int)")
        sql4.AppendLine("					end as total_free_days")
        sql4.AppendLine("")
        sql4.AppendLine("					from")
        sql4.AppendLine("					--Выборка контейнеров ТОЛЬКО на ODCY, определение их родительских портов и условий хранение в родительском порту + ODCY")
        sql4.AppendLine("					(")
        sql4.AppendLine("							select")
        sql4.AppendLine("							[master].PROVIDER")
        sql4.AppendLine("							,info.CONTAINER")
        sql4.AppendLine("							,info.CONDITION")
        sql4.AppendLine("							,info.CARGO")
        sql4.AppendLine("							,info.ARRIVAL_DATE")
        sql4.AppendLine("							,info.[STATUS]")
        sql4.AppendLine("							,[master].parrent_port_id as parrent_id")
        sql4.AppendLine("							--Следующая строка повторяется несколько раз в case. При изменении её нужно подставить в case")
        sql4.AppendLine("							,(select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id) as parrent_condition")
        sql4.AppendLine("							,case")
        sql4.AppendLine("								when patindex('%/%', info.CONDITION) <> 0 then")
        sql4.AppendLine("								substring(info.CONDITION, 1, patindex('%/%', info.CONDITION)-1)")
        sql4.AppendLine("								when patindex('%/%', info.CONDITION) = 0 then")
        sql4.AppendLine("								info.CONDITION")
        sql4.AppendLine("							end as condition_parc")
        sql4.AppendLine("							,case")
        sql4.AppendLine("								when patindex('%/%', (select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id)) <> 0 then")
        sql4.AppendLine("								substring((select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id), 1, patindex('%/%', (select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id))-1)")
        sql4.AppendLine("								when patindex('%/%', (select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id)) = 0 then")
        sql4.AppendLine("								(select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id)")
        sql4.AppendLine("							end as parrent_condition_parc")
        sql4.AppendLine("")
        sql4.AppendLine("							from [UserModules].[user].[ITEM_INFORMATION] info")
        sql4.AppendLine("")
        sql4.AppendLine("							left join [UserModules].[user].[DESTINATION_MASTER] [master]")
        sql4.AppendLine("							on [master].id = info.CARRIER_ID")
        sql4.AppendLine("")
        sql4.AppendLine("							--left join [UserModules].[user].[DESTINATION_MASTER] slave")
        sql4.AppendLine("							--on [master].id = [slave].ODCY_ID")
        sql4.AppendLine("")
        sql4.AppendLine("						where [master].IS_ODCY = 'Y'")
        sql4.AppendLine("					) t")
        sql4.AppendLine("			)tt")
        sql4.AppendLine("	)ttt")
        sql4.AppendLine("	where LEN(container) = 11")
        sql4.AppendLine("	and (total_free_days - keep_time) <=5")
        sql4.AppendLine("	and keep_time > 0")
        sql4.AppendLine("	and keep_time <= total_free_days")
        sql4.AppendLine("	order by (total_free_days - keep_time)")





        sql5.AppendLine("--Demurrage - расчёт стоимости для каждого контейнера, который сейчас находится в простое")
        sql5.AppendLine("declare @cont_tab as table (carrier_id int, container varchar(max), keep_time int)")
        sql5.AppendLine("declare @result_tab as table (carrier_id int, container varchar(max), keep_time int, demurrage_fee_rub int, demurrage_fee_usd numeric(20,3))")
        sql5.AppendLine("declare @carrier int")
        sql5.AppendLine("declare @container varchar(max)")
        sql5.AppendLine("declare @keep_time int")
        sql5.AppendLine("declare @i int")
        sql5.AppendLine("declare @current_amt int")
        sql5.AppendLine("")
        sql5.AppendLine("")
        sql5.AppendLine("DECLARE cont_CURSOR CURSOR FOR")
        sql5.AppendLine("(")
        sql5.AppendLine("	--Общий результат")
        sql5.AppendLine("	select")
        sql5.AppendLine("	--count(container)")
        sql5.AppendLine("	CARRIER_ID")
        sql5.AppendLine("	,container")
        sql5.AppendLine("	--,arrival_date")
        sql5.AppendLine("	--,total_free_days")
        sql5.AppendLine("	,keep_time")
        sql5.AppendLine("	from")
        sql5.AppendLine("	(")
        sql5.AppendLine("")
        sql5.AppendLine("	--Выборка контейнеров и необходимой информации для них, которые НЕ НАХОДЯТСЯ на ODCY")
        sql5.AppendLine("			select")
        sql5.AppendLine("			info.CARRIER_ID")
        sql5.AppendLine("			,info.container")
        sql5.AppendLine("			,info.ARRIVAL_DATE")
        sql5.AppendLine("			,case")
        sql5.AppendLine("					when patindex('%/%', info.CONDITION) <> 0 then")
        sql5.AppendLine("					substring(info.CONDITION, 1, patindex('%/%', info.CONDITION)-1)")
        sql5.AppendLine("					when patindex('%/%', info.CONDITION) = 0 then")
        sql5.AppendLine("					info.CONDITION")
        sql5.AppendLine("			end as total_free_days")
        sql5.AppendLine("			,datediff(day, cast(arrival_date as date), cast(getdate() as date)) + 1 as keep_time")
        sql5.AppendLine("")
        sql5.AppendLine("")
        sql5.AppendLine("			from [UserModules].[user].[ITEM_INFORMATION] info")
        sql5.AppendLine("")
        sql5.AppendLine("			left join [UserModules].[user].[DESTINATION_MASTER] [master]")
        sql5.AppendLine("			on info.CARRIER_ID = [master].id")
        sql5.AppendLine("			where")
        sql5.AppendLine("			info.[status] is not null")
        sql5.AppendLine("			and info.[status] <> ''")
        sql5.AppendLine("			and info.[status] <> 'N/A'")
        sql5.AppendLine("			and info.[status] <> 'In transit'")
        sql5.AppendLine("			and ([master].IS_ODCY <> 'Y' or [master].IS_ODCY is null)")
        sql5.AppendLine("")
        sql5.AppendLine("			UNION ALL")
        sql5.AppendLine("			--Выборка контейнеров и необходимой информации для них, которые НАХОДЯТСЯ на ODCY")
        sql5.AppendLine("			select")
        sql5.AppendLine("			CARRIER_ID")
        sql5.AppendLine("			,CONTAINER")
        sql5.AppendLine("			,ARRIVAL_DATE")
        sql5.AppendLine("			,total_free_days")
        sql5.AppendLine("			,datediff(day, cast(arrival_date as date), cast(getdate() as date)) + 1 as keep_time")
        sql5.AppendLine("			from")
        sql5.AppendLine("			(")
        sql5.AppendLine("")
        sql5.AppendLine("					--Парсинг условий из ранее сформированной таблицы и преобразование их в int. Вычисление общей суммы бесплатного срока хранения")
        sql5.AppendLine("					select")
        sql5.AppendLine("					CARRIER_ID")
        sql5.AppendLine("					,container")
        sql5.AppendLine("					,arrival_date")
        sql5.AppendLine("					,condition")
        sql5.AppendLine("					,parrent_id")
        sql5.AppendLine("					,parrent_condition")
        sql5.AppendLine("					--Условия хранения")
        sql5.AppendLine("					,case when isnumeric --Если это число")
        sql5.AppendLine("					(")
        sql5.AppendLine("						condition_parc")
        sql5.AppendLine("					) = 1")
        sql5.AppendLine("")
        sql5.AppendLine("					and isnumeric --И это число")
        sql5.AppendLine("					(")
        sql5.AppendLine("						parrent_condition_parc")
        sql5.AppendLine("					) = 1")
        sql5.AppendLine("					then --Тогда считаем общую сумму бесплатного хранения")
        sql5.AppendLine("						cast(condition_parc as int)")
        sql5.AppendLine("						+")
        sql5.AppendLine("						cast(parrent_condition_parc as int)")
        sql5.AppendLine("					end as total_free_days")
        sql5.AppendLine("")
        sql5.AppendLine("					from")
        sql5.AppendLine("					--Выборка контейнеров ТОЛЬКО на ODCY, определение их родительских портов и условий хранение в родительском порту + ODCY")
        sql5.AppendLine("					(")
        sql5.AppendLine("							select")
        sql5.AppendLine("							info.CARRIER_ID")
        sql5.AppendLine("							,info.CONTAINER")
        sql5.AppendLine("							,info.ARRIVAL_DATE")
        sql5.AppendLine("							,info.CONDITION")
        sql5.AppendLine("							,[master].parrent_port_id as parrent_id")
        sql5.AppendLine("							--Следующая строка повторяется несколько раз в case. При изменении её нужно подставить в case")
        sql5.AppendLine("							,(select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id) as parrent_condition")
        sql5.AppendLine("							,case")
        sql5.AppendLine("								when patindex('%/%', info.CONDITION) <> 0 then")
        sql5.AppendLine("								substring(info.CONDITION, 1, patindex('%/%', info.CONDITION)-1)")
        sql5.AppendLine("								when patindex('%/%', info.CONDITION) = 0 then")
        sql5.AppendLine("								info.CONDITION")
        sql5.AppendLine("							end as condition_parc")
        sql5.AppendLine("							,case")
        sql5.AppendLine("								when patindex('%/%', (select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id)) <> 0 then")
        sql5.AppendLine("								substring((select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id), 1, patindex('%/%', (select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id))-1)")
        sql5.AppendLine("								when patindex('%/%', (select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id)) = 0 then")
        sql5.AppendLine("								(select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id)")
        sql5.AppendLine("							end as parrent_condition_parc")
        sql5.AppendLine("")
        sql5.AppendLine("							from [UserModules].[user].[ITEM_INFORMATION] info")
        sql5.AppendLine("")
        sql5.AppendLine("							left join [UserModules].[user].[DESTINATION_MASTER] [master]")
        sql5.AppendLine("							on [master].id = info.CARRIER_ID")
        sql5.AppendLine("")
        sql5.AppendLine("							--left join [UserModules].[user].[DESTINATION_MASTER] slave")
        sql5.AppendLine("							--on [master].id = [slave].ODCY_ID")
        sql5.AppendLine("")
        sql5.AppendLine("						where [master].IS_ODCY = 'Y'")
        sql5.AppendLine("					) t")
        sql5.AppendLine("			)tt")
        sql5.AppendLine("	)ttt")
        sql5.AppendLine("	where keep_time > 0")
        sql5.AppendLine("	and keep_time > total_free_days")
        sql5.AppendLine("	and LEN(container) = 11")
        sql5.AppendLine("	--and CARRIER_ID = 33")
        sql5.AppendLine("")
        sql5.AppendLine(")")
        sql5.AppendLine("")
        sql5.AppendLine("")
        sql5.AppendLine("OPEN cont_CURSOR")
        sql5.AppendLine("	FETCH NEXT FROM cont_cursor INTO @carrier, @container, @keep_time")
        sql5.AppendLine("")
        sql5.AppendLine("	WHILE @@FETCH_STATUS = 0")
        sql5.AppendLine("	BEGIN")
        sql5.AppendLine("		set @i = 1")
        sql5.AppendLine("		set @current_amt = 0")
        sql5.AppendLine("		--print @carrier")
        sql5.AppendLine("		--print @container")
        sql5.AppendLine("		--print @keep_time")
        sql5.AppendLine("		--print ' '")
        sql5.AppendLine("		while @i <= @keep_time")
        sql5.AppendLine("			begin")
        sql5.AppendLine("				set @current_amt = @current_amt + (")
        sql5.AppendLine("				select")
        sql5.AppendLine("				isnull(max(price), 0)")
        sql5.AppendLine("				from [UserModules].[user].[DEMURRAGE_PRICE]")
        sql5.AppendLine("				where carrier_id = @carrier")
        sql5.AppendLine("				and [days] < @i")
        sql5.AppendLine("				)")
        sql5.AppendLine("				set @i = @i + 1")
        sql5.AppendLine("			end")
        sql5.AppendLine("			insert into @result_tab (carrier_id, container, keep_time, demurrage_fee_rub, demurrage_fee_usd) values (@carrier, @container, @keep_time, @current_amt, @current_amt / (select cast(EXCH_RATE as numeric(20,3)) from [UserModules].[user].[CURRENCY] where [from] = 'USD' and [to] = 'RUB'))")
        sql5.AppendLine("		FETCH NEXT FROM cont_cursor INTO @carrier, @container, @keep_time")
        sql5.AppendLine("	END")
        sql5.AppendLine("")
        sql5.AppendLine("	CLOSE cont_CURSOR")
        sql5.AppendLine("	DEALLOCATE cont_CURSOR")
        sql5.AppendLine("")
        sql5.AppendLine("----------Вот здесь можно посмотреть, что попадает в demurrage, и сколько это стоит")
        sql5.AppendLine("	--select *")
        sql5.AppendLine("	--from @result_tab")
        sql5.AppendLine("")
        sql5.AppendLine("")
        sql5.AppendLine("	select")
        sql5.AppendLine("	info.CARGO as [Type]")
        sql5.AppendLine("	,count (info.container) as Container")
        sql5.AppendLine("	,count (result.container) as Demurrage")
        sql5.AppendLine("")
        sql5.AppendLine("	from [UserModules].[user].[ITEM_INFORMATION] info")
        sql5.AppendLine("")
        sql5.AppendLine("	left join @result_tab result")
        sql5.AppendLine("	on info.CONTAINER collate Cyrillic_General_CI_AS = result.container collate Cyrillic_General_CI_AS")
        sql5.AppendLine("")
        sql5.AppendLine("	where")
        sql5.AppendLine("	(")
        sql5.AppendLine("	[STATUS] = 'CC finished'")
        sql5.AppendLine("	or [status] = 'not CC'")
        sql5.AppendLine("	or ([status] = 'In transit' and cast(arrival_date as date) = cast(getdate()as date))")
        sql5.AppendLine("	)")
        sql5.AppendLine("	and LEN(info.container) = 11")
        sql5.AppendLine("	and cast(do_date as date) <> cast(getdate() as date)")
        sql5.AppendLine("	")
        sql5.AppendLine("	group by info.CARGO")
        sql5.AppendLine("")
        sql5.AppendLine("")
        sql5.AppendLine("	")



        sql_mail_title_warning.AppendLine("	--Общий результат")
        sql_mail_title_warning.AppendLine("	select")
        sql_mail_title_warning.AppendLine("	count(container) as total")
        sql_mail_title_warning.AppendLine("	from")
        sql_mail_title_warning.AppendLine("	(")
        sql_mail_title_warning.AppendLine("")
        sql_mail_title_warning.AppendLine("	--Выборка контейнеров и необходимой информации для них, которые НЕ НАХОДЯТСЯ на ODCY")
        sql_mail_title_warning.AppendLine("			select")
        sql_mail_title_warning.AppendLine("			[master].PROVIDER")
        sql_mail_title_warning.AppendLine("			,info.container")
        sql_mail_title_warning.AppendLine("			,info.CONDITION")
        sql_mail_title_warning.AppendLine("			,info.CARGO")
        sql_mail_title_warning.AppendLine("			,info.ARRIVAL_DATE")
        sql_mail_title_warning.AppendLine("			,info.[STATUS]")
        sql_mail_title_warning.AppendLine("			,case")
        sql_mail_title_warning.AppendLine("					when patindex('%/%', info.CONDITION) <> 0 then")
        sql_mail_title_warning.AppendLine("					substring(info.CONDITION, 1, patindex('%/%', info.CONDITION)-1)")
        sql_mail_title_warning.AppendLine("					when patindex('%/%', info.CONDITION) = 0 then")
        sql_mail_title_warning.AppendLine("					info.CONDITION")
        sql_mail_title_warning.AppendLine("			end as total_free_days")
        sql_mail_title_warning.AppendLine("			,datediff(day, cast(arrival_date as date), cast(getdate() as date)) + 1 as keep_time")
        sql_mail_title_warning.AppendLine("")
        sql_mail_title_warning.AppendLine("")
        sql_mail_title_warning.AppendLine("			from [UserModules].[user].[ITEM_INFORMATION] info")
        sql_mail_title_warning.AppendLine("")
        sql_mail_title_warning.AppendLine("			left join [UserModules].[user].[DESTINATION_MASTER] [master]")
        sql_mail_title_warning.AppendLine("			on info.CARRIER_ID = [master].id")
        sql_mail_title_warning.AppendLine("			where")
        sql_mail_title_warning.AppendLine("			info.[status] is not null")
        sql_mail_title_warning.AppendLine("			and info.[status] <> ''")
        sql_mail_title_warning.AppendLine("			and info.[status] <> 'N/A'")
        sql_mail_title_warning.AppendLine("			and info.[status] <> 'In transit'")
        sql_mail_title_warning.AppendLine("			and ([master].IS_ODCY <> 'Y' or [master].IS_ODCY is null)")
        sql_mail_title_warning.AppendLine("")
        sql_mail_title_warning.AppendLine("			UNION ALL")
        sql_mail_title_warning.AppendLine("			--Выборка контейнеров и необходимой информации для них, которые НАХОДЯТСЯ на ODCY")
        sql_mail_title_warning.AppendLine("			select")
        sql_mail_title_warning.AppendLine("			provider")
        sql_mail_title_warning.AppendLine("			,CONTAINER")
        sql_mail_title_warning.AppendLine("			,CONDITION")
        sql_mail_title_warning.AppendLine("			,CARGO")
        sql_mail_title_warning.AppendLine("			,ARRIVAL_DATE")
        sql_mail_title_warning.AppendLine("			,[STATUS]")
        sql_mail_title_warning.AppendLine("			,total_free_days")
        sql_mail_title_warning.AppendLine("			,datediff(day, cast(arrival_date as date), cast(getdate() as date)) + 1 as keep_time")
        sql_mail_title_warning.AppendLine("			from")
        sql_mail_title_warning.AppendLine("			(")
        sql_mail_title_warning.AppendLine("")
        sql_mail_title_warning.AppendLine("					--Парсинг условий из ранее сформированной таблицы и преобразование их в int. Вычисление общей суммы бесплатного срока хранения")
        sql_mail_title_warning.AppendLine("					select")
        sql_mail_title_warning.AppendLine("					PROVIDER")
        sql_mail_title_warning.AppendLine("					,container")
        sql_mail_title_warning.AppendLine("					,CONDITION")
        sql_mail_title_warning.AppendLine("					,CARGO")
        sql_mail_title_warning.AppendLine("					,ARRIVAL_DATE")
        sql_mail_title_warning.AppendLine("					,[STATUS]")
        sql_mail_title_warning.AppendLine("					,parrent_id")
        sql_mail_title_warning.AppendLine("					,parrent_condition")
        sql_mail_title_warning.AppendLine("					--Условия хранения")
        sql_mail_title_warning.AppendLine("					,case when isnumeric --Если это число")
        sql_mail_title_warning.AppendLine("					(")
        sql_mail_title_warning.AppendLine("						condition_parc")
        sql_mail_title_warning.AppendLine("					) = 1")
        sql_mail_title_warning.AppendLine("")
        sql_mail_title_warning.AppendLine("					and isnumeric --И это число")
        sql_mail_title_warning.AppendLine("					(")
        sql_mail_title_warning.AppendLine("						parrent_condition_parc")
        sql_mail_title_warning.AppendLine("					) = 1")
        sql_mail_title_warning.AppendLine("					then --Тогда считаем общую сумму бесплатного хранения")
        sql_mail_title_warning.AppendLine("						cast(condition_parc as int)")
        sql_mail_title_warning.AppendLine("						+")
        sql_mail_title_warning.AppendLine("						cast(parrent_condition_parc as int)")
        sql_mail_title_warning.AppendLine("					end as total_free_days")
        sql_mail_title_warning.AppendLine("")
        sql_mail_title_warning.AppendLine("					from")
        sql_mail_title_warning.AppendLine("					--Выборка контейнеров ТОЛЬКО на ODCY, определение их родительских портов и условий хранение в родительском порту + ODCY")
        sql_mail_title_warning.AppendLine("					(")
        sql_mail_title_warning.AppendLine("							select")
        sql_mail_title_warning.AppendLine("							[master].PROVIDER")
        sql_mail_title_warning.AppendLine("							,info.CONTAINER")
        sql_mail_title_warning.AppendLine("							,info.CONDITION")
        sql_mail_title_warning.AppendLine("							,info.CARGO")
        sql_mail_title_warning.AppendLine("							,info.ARRIVAL_DATE")
        sql_mail_title_warning.AppendLine("							,info.[STATUS]")
        sql_mail_title_warning.AppendLine("							,[master].parrent_port_id as parrent_id")
        sql_mail_title_warning.AppendLine("							--Следующая строка повторяется несколько раз в case. При изменении её нужно подставить в case")
        sql_mail_title_warning.AppendLine("							,(select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id) as parrent_condition")
        sql_mail_title_warning.AppendLine("							,case")
        sql_mail_title_warning.AppendLine("								when patindex('%/%', info.CONDITION) <> 0 then")
        sql_mail_title_warning.AppendLine("								substring(info.CONDITION, 1, patindex('%/%', info.CONDITION)-1)")
        sql_mail_title_warning.AppendLine("								when patindex('%/%', info.CONDITION) = 0 then")
        sql_mail_title_warning.AppendLine("								info.CONDITION")
        sql_mail_title_warning.AppendLine("							end as condition_parc")
        sql_mail_title_warning.AppendLine("							,case")
        sql_mail_title_warning.AppendLine("								when patindex('%/%', (select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id)) <> 0 then")
        sql_mail_title_warning.AppendLine("								substring((select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id), 1, patindex('%/%', (select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id))-1)")
        sql_mail_title_warning.AppendLine("								when patindex('%/%', (select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id)) = 0 then")
        sql_mail_title_warning.AppendLine("								(select top 1 condition from [UserModules].[user].[ITEM_INFORMATION] info1 left join [UserModules].[user].[DESTINATION_MASTER] [master1] on [master1].id = info1.CARRIER_ID where master1.id = [master].parrent_port_id)")
        sql_mail_title_warning.AppendLine("							end as parrent_condition_parc")
        sql_mail_title_warning.AppendLine("")
        sql_mail_title_warning.AppendLine("							from [UserModules].[user].[ITEM_INFORMATION] info")
        sql_mail_title_warning.AppendLine("")
        sql_mail_title_warning.AppendLine("							left join [UserModules].[user].[DESTINATION_MASTER] [master]")
        sql_mail_title_warning.AppendLine("							on [master].id = info.CARRIER_ID")
        sql_mail_title_warning.AppendLine("")
        sql_mail_title_warning.AppendLine("							--left join [UserModules].[user].[DESTINATION_MASTER] slave")
        sql_mail_title_warning.AppendLine("							--on [master].id = [slave].ODCY_ID")
        sql_mail_title_warning.AppendLine("")
        sql_mail_title_warning.AppendLine("						where [master].IS_ODCY = 'Y'")
        sql_mail_title_warning.AppendLine("					) t")
        sql_mail_title_warning.AppendLine("			)tt")
        sql_mail_title_warning.AppendLine("	)ttt")
        sql_mail_title_warning.AppendLine("	where LEN(container) = 11")
        sql_mail_title_warning.AppendLine("	and (total_free_days - keep_time) <= 3")
        sql_mail_title_warning.AppendLine("	and keep_time > 0")
        sql_mail_title_warning.AppendLine("	and keep_time <= total_free_days")
        sql_mail_title_warning.AppendLine("")



        mail_title_warning = db.GetRow(sql_mail_title_warning.ToString).Item("total")



        sql_mail_title_demurrage.AppendLine("select demurrage from [UserModules].[user].[mail_prepare] where cast(entry_date as date) = cast(getdate() as date)")
        mail_title_demurrage = db.GetRow(sql_mail_title_demurrage.ToString).Item("demurrage")


        Using ccn As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("SERKIntranetConnectionString").ConnectionString)
            ccn.Open()
            Using dad As New SqlDataAdapter(sql2.ToString, ccn)
                dad.Fill(monthly_table)
            End Using
            ccn.Close()
        End Using



        db.NonQuery(prepare_sql.ToString)
        db.NonQuery(prepare_sql_1.ToString)




        Sql_trend.SelectCommand = sql.ToString

        SqlDataSource_summary_table.SelectCommand = sql1.ToString
        SqlDataSource_summary_table.Select(DataSourceSelectArguments.Empty)

        SqlDataSource_second_table.SelectCommand = sql3.ToString
        SqlDataSource_second_table.Select(DataSourceSelectArguments.Empty)

        SqlDataSource_third_table.SelectCommand = sql4.ToString
        SqlDataSource_third_table.Select(DataSourceSelectArguments.Empty)

        SqlDataSource_Forth_table.SelectCommand = sql5.ToString
        SqlDataSource_Forth_table.Select(DataSourceSelectArguments.Empty)


        'Распихиваем данные по таблицам
        dv_container_trend = Sql_trend.Select(DataSourceSelectArguments.Empty)

        'Распихиваем данные по GridView
        Summary_table.DataSource = SqlDataSource_summary_table
        Summary_table.DataBind()

        Second_table.DataSource = SqlDataSource_second_table
        Second_table.DataBind()

        Third_table.DataSource = SqlDataSource_third_table
        Third_table.DataBind()

        Forth_table.DataSource = SqlDataSource_Forth_table
        Forth_table.DataBind()



        'Формируем возростающий, линейный график
        Container_trend_graph.Series.FindByName("container").Points.DataBind(dv_container_trend, "date", "container", "")
        Container_trend_graph.Series.FindByName("demurrage").Points.DataBind(dv_container_trend, "date", "demurrage", "")



        Dim i As Integer
        Dim label As String
        ''Делаем подписи по оси X


        'Container_trend_graph.Series.FindByName("container").SmartLabelStyle.Enabled = True
        'Container_trend_graph.Series.FindByName("container").SmartLabelStyle.IsMarkerOverlappingAllowed = False
        ''Container_trend_graph.Series.FindByName("container").SmartLabelStyle.AllowOutsidePlotArea = False
        ''Container_trend_graph.Series.FindByName("container").SmartLabelStyle.MovingDirection = LabelAlignmentStyles.Top
        'Container_trend_graph.Series.FindByName("container").SmartLabelStyle.MaxMovingDistance = 1
        ''Container_trend_graph.Series.FindByName("container").SmartLabelStyle


        'Container_trend_graph.Series.FindByName("demurrage").SmartLabelStyle.Enabled = True
        'Container_trend_graph.Series.FindByName("demurrage").SmartLabelStyle.IsMarkerOverlappingAllowed = False
        'Container_trend_graph.Series.FindByName("demurrage").SmartLabelStyle.AllowOutsidePlotArea = False
        'Container_trend_graph.Series.FindByName("demurrage").SmartLabelStyle.MovingDirection = LabelAlignmentStyles.Bottom
        'Container_trend_graph.Series.FindByName("demurrage").SmartLabelStyle.MaxMovingDistance = 1




        i = 0
        For Each row As DataRowView In dv_container_trend
            Container_trend_graph.ChartAreas("ChartArea1").AxisX.CustomLabels.Add(Container_trend_graph.Series.FindByName("container").Points(i).XValue - 0.017, Container_trend_graph.Series.FindByName("container").Points(i).XValue + 0.017, "", 0, LabelMarkStyle.LineSideMark)
            label = row.Item(0).ToString
            'Container_trend_graph.ChartAreas("ChartArea1").AxisX.CustomLabels.Add(Container_trend_graph.Series.FindByName("container").Points(i).XValue - 1, Container_trend_graph.Series.FindByName("container").Points(i).XValue + 1, vbCrLf + vbCrLf + vbCrLf, 1, LabelMarkStyle.None)
            Container_trend_graph.ChartAreas("ChartArea1").AxisX.CustomLabels.Add(Container_trend_graph.Series.FindByName("container").Points(i).XValue - 1, Container_trend_graph.Series.FindByName("container").Points(i).XValue + 1, label, 1, LabelMarkStyle.None)
            'Container_trend_graph.ChartAreas("ChartArea1").AxisX.CustomLabels(i + i + 1).MarkColor = Drawing.Color.FromArgb(339933)
            i = i + 1
        Next
        'If i <= 8 Then
        '    Container_trend_graph.ChartAreas("ChartArea1").AxisX.Maximum = 0.7
        'Else
        '    Container_trend_graph.ChartAreas("ChartArea1").AxisX.Maximum = Double.NaN
        'End If




    End Sub



    Protected Sub Page_SaveStateComplete(sender As Object, e As EventArgs) Handles Me.SaveStateComplete



    End Sub


    Private Sub Summary_table_RowDataBound(sender As Object, e As GridViewRowEventArgs) Handles Summary_table.RowDataBound
        Dim i As Integer = 0
        If e.Row.RowType = DataControlRowType.DataRow Then
        ElseIf e.Row.RowType = DataControlRowType.Header Then
            i = 0
            For Each cell As TableCell In e.Row.Cells
                If i > 0 Then
                    cell.Text = Left(cell.Text, 2)
                End If
                i = i + 1
            Next
        End If
    End Sub

    Private Sub r_PreRenderComplete(sender As Object, e As EventArgs) Handles Me.PreRenderComplete

    End Sub



    Public Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)

    End Sub

    Private Sub r_PreRender(sender As Object, e As EventArgs) Handles Me.PreRender
        Dim row = New GridViewRow(-1, -1, DataControlRowType.Header, DataControlRowState.Normal)
        Dim c0 As New TableCell With {.Text = "Month"}
        row.Cells.Add(c0)
        row.Cells(0).HorizontalAlign = HorizontalAlign.Center
        Summary_table.Controls(0).Controls.AddAt(0, row)

        Dim last_month_days As Integer = day_diff - Date.Today.Day


        If last_month_days >= 0 Then
            Dim c1 As New TableCell With {.Text = DateAdd(DateInterval.Month, -1, Date.Today).ToString("MMMM", CultureInfo.CreateSpecificCulture("en"))}
            row.Cells.Add(c1)
            c1.HorizontalAlign = HorizontalAlign.Center
            c1.ColumnSpan = last_month_days + 1
        End If


        Dim c2 As New TableCell With {.Text = Date.Today.ToString("MMMM", CultureInfo.CreateSpecificCulture("en"))}
        row.Cells.Add(c2)
        c2.HorizontalAlign = HorizontalAlign.Center
        If last_month_days >= 0 Then
            c2.ColumnSpan = Summary_table.Rows(0).Cells.Count - last_month_days - 2
        Else
            c2.ColumnSpan = Summary_table.Rows(0).Cells.Count - 1
        End If





        'Dim total_calculated_row = New GridViewRow(-1, -1, DataControlRowType.DataRow, DataControlRowState.Normal)
        'Dim STP_qty_row = New GridViewRow(-1, -1, DataControlRowType.DataRow, DataControlRowState.Normal)
        'Dim STP_row = New GridViewRow(-1, -1, DataControlRowType.DataRow, DataControlRowState.Normal)



        Dim i As Integer = 0
        For Each r As DataRow In monthly_table.Rows
            i = i + 1
            Dim row1 = New GridViewRow(-1, -1, DataControlRowType.DataRow, DataControlRowState.Normal)

            Dim cell_0 As New TableCell With {.Text = r.Item(0).ToString}
            row1.Cells.Add(cell_0)
            row1.Cells(0).HorizontalAlign = HorizontalAlign.Center
            row1.Cells(0).Style.Add("color", "Red")


            If last_month_days >= 0 Then
                Dim cell_1 As New TableCell With {.Text = r.Item(1).ToString}
                cell_1.HorizontalAlign = HorizontalAlign.Center
                cell_1.ColumnSpan = last_month_days + 1
                row1.Cells.Add(cell_1)
            End If

            Dim cell_2 As New TableCell With {.Text = r.Item(2).ToString}
            cell_2.HorizontalAlign = HorizontalAlign.Center
            If last_month_days >= 0 Then
                cell_2.ColumnSpan = Summary_table.Rows(0).Cells.Count - last_month_days - 2
            Else
                cell_2.ColumnSpan = Summary_table.Rows(0).Cells.Count - 1
            End If
            row1.Cells.Add(cell_2)

            Summary_table.Controls(0).Controls.AddAt(Summary_table.Rows.Count + i + 1, row1)
        Next

        Summary_table.Rows(1).Style.Add("background", "yellow")
        Summary_table.Rows(2).Style.Add("background", "yellow")


        Dim sw As StringWriter = New StringWriter
        Dim hw As HtmlTextWriter = New HtmlTextWriter(sw)
        panel.RenderControl(hw)

        Dim mail_title As String
        mail_title = "Container Delivery Trend & Cost - Today's Demurrage : " + mail_title_demurrage + ":, Warning (D+3) : " + mail_title_warning




        CommonFunctions.SendEmail2("naumenko.mv@samsung.com", mail_title, sw.ToString.Replace("/ModuleMaster/TempImages", Request.Url.GetLeftPart(UriPartial.Authority) & "/ModuleMaster/TempImages"))
        CommonFunctions.SendEmail2("t.tseneva@samsung.com", mail_title, sw.ToString.Replace("/ModuleMaster/TempImages", Request.Url.GetLeftPart(UriPartial.Authority) & "/ModuleMaster/TempImages"))
        CommonFunctions.SendEmail2("k.zuev@samsung.com", mail_title, sw.ToString.Replace("/ModuleMaster/TempImages", Request.Url.GetLeftPart(UriPartial.Authority) & "/ModuleMaster/TempImages"))
        CommonFunctions.SendEmail2("a.kopytkin@samsung.com", mail_title, sw.ToString.Replace("/ModuleMaster/TempImages", Request.Url.GetLeftPart(UriPartial.Authority) & "/ModuleMaster/TempImages"))
        CommonFunctions.SendEmail2("a.markova@samsung.com", mail_title, sw.ToString.Replace("/ModuleMaster/TempImages", Request.Url.GetLeftPart(UriPartial.Authority) & "/ModuleMaster/TempImages"))
        CommonFunctions.SendEmail2("i.krivokon@samsung.com", mail_title, sw.ToString.Replace("/ModuleMaster/TempImages", Request.Url.GetLeftPart(UriPartial.Authority) & "/ModuleMaster/TempImages"))
    End Sub

End Class