ModuleMaster/mi/kpi_summary.aspx.vb - main - ivan/ModMas

ModuleMaster/mi/kpi_summary.aspx.vb
1 035 строк56 KB
i.krivokon
Initial commit
13 мар 2026, 13:49
13 мар 2026, 13:4900d9fb6

Imports System.Data
Imports System.IO
Imports System.Web.UI.DataVisualization.Charting

Partial Class r
    Inherits System.Web.UI.Page
    Private sqlf As New sharedClasses.dbFunctions.SqlFunctions
    Public color As String = "black"
    Public dr As Data.DataRow
    Public trend As Char
    Public division As String = "W/M"


    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        Dim DV_linear As DataView


        'Dim Day_Flag As Boolean = True
        'Dim Line_Flag As Boolean = False
        Dim sql, sql_caption As New StringBuilder ' sql - для паутинки, sql_caption - для заголовка


        'line = Request("line")
        'If Not String.IsNullOrEmpty(line) And line <> "Total VD" Then
        '    Line_Flag = True
        'End If

        'If Not IsPostBack Then
        '    graph_date.Value = Request("date")
        'End If


        'If graph_date.Value <> "" Then
        '        Dim tmp_date As Date = graph_date.Value
        '        If DateTime.Now.ToString("yyyy/MM/dd") <> Date.Parse(graph_date.Value).ToString("yyyy/MM/dd") Then
        '            Day_Flag = False
        '        End If
        '    End If

        If IsPostBack Then
            Weaknesses.Columns(4).Visible = True
        End If
        division = Plant.SelectedValue

        'Строка запроса к базе для линейного графика
        sql.AppendLine("declare @date date ")
        sql.AppendLine("set @date = getdate() ")
        sql.AppendLine("declare @division nvarchar(max) ")
        sql.AppendLine("set @division = '" + division + "' ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("--TTL Productivity Employee ")
        sql.AppendLine("select * from ( ")
        sql.AppendLine("select ")
        sql.AppendLine("top 1 ")
        sql.AppendLine("TTL_Productivity_Employee_D1.[date] as [D-1] ")
        sql.AppendLine(",cast(TTL_Productivity_Employee_D1.[target] as decimal(20,1)) as [T-1] ")
        sql.AppendLine(",cast(TTL_Productivity_Employee_D1.[result] as decimal(20,1)) as [R-1] ")
        sql.AppendLine(",cast(TTL_Productivity_Employee_D1.[achieve_rate] as decimal(20,1)) as [AR-1] ")
        sql.AppendLine(",TTL_Productivity_Employee_D1.KPI as [KPI-1] ")
        sql.AppendLine(",TTL_Productivity_Employee_D2.[date] as [D-2] ")
        sql.AppendLine(",TTL_Productivity_Employee_D2.[target] as [T-2] ")
        sql.AppendLine(",TTL_Productivity_Employee_D2.[result] as [R-2] ")
        sql.AppendLine(",cast(TTL_Productivity_Employee_D2.[achieve_rate] as decimal(20,1)) as [AR-2] ")
        sql.AppendLine(",TTL_Productivity_Employee_D2.KPI as [KPI-2] ")
        sql.AppendLine("from ")
        sql.AppendLine("( ")
        sql.AppendLine("	select top 2 ")
        sql.AppendLine("	[date] as [date] ")
        sql.AppendLine("	,TARGET____ as [target] ")
        sql.AppendLine("	,RESULT as [result] ")
        sql.AppendLine("	,ACHIEVEMENT_RAYE____ as [achieve_rate] ")
        sql.AppendLine("	,'TTL Productivity Employee' as 'KPI' ")
        sql.AppendLine("	,ROW_NUMBER() OVER(ORDER BY [date] desc) AS Row# ")
        sql.AppendLine("	from [UserModules].[user].[DAILY_KPI_REPORT_2017] ")
        sql.AppendLine("	where cast([date] as date) < cast(@date as date) ")
        sql.AppendLine("	and division = @division ")
        sql.AppendLine("	order by [date] desc ")
        sql.AppendLine(") as TTL_Productivity_Employee_D1 ")
        sql.AppendLine("left join ")
        sql.AppendLine("( ")
        sql.AppendLine("	select top 2 ")
        sql.AppendLine("	[date] as [date] ")
        sql.AppendLine("	,TARGET____ as [target] ")
        sql.AppendLine("	,RESULT as [result] ")
        sql.AppendLine("	,ACHIEVEMENT_RAYE____ as [achieve_rate] ")
        sql.AppendLine("	,'TTL Productivity Employee' as 'KPI' ")
        sql.AppendLine("	,ROW_NUMBER() OVER(ORDER BY [date] desc) AS Row# ")
        sql.AppendLine("	from [UserModules].[user].[DAILY_KPI_REPORT_2017] ")
        sql.AppendLine("	where cast([date] as date) < cast(@date as date) ")
        sql.AppendLine("	and division = @division ")
        sql.AppendLine("	order by [date] desc ")
        sql.AppendLine(") as TTL_Productivity_Employee_D2 ")
        sql.AppendLine("on TTL_Productivity_Employee_D1.[kpi]=TTL_Productivity_Employee_D2.[kpi] and TTL_Productivity_Employee_D1.[date]<>TTL_Productivity_Employee_D2.[date] ")
        sql.AppendLine("order by TTL_Productivity_Employee_D1.[date] desc ")
        sql.AppendLine(") as K1 ")
        sql.AppendLine(" ")
        sql.AppendLine("union all ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("--Manpower Efficiency ")
        sql.AppendLine("select * from ( ")
        sql.AppendLine("select ")
        sql.AppendLine("top 1 ")
        sql.AppendLine("Manpower_Efficiency_D1.[date] as [D-1] ")
        sql.AppendLine(",Manpower_Efficiency_D1.[target] as [T-1] ")
        sql.AppendLine(",Manpower_Efficiency_D1.[result] as [R-1] ")
        sql.AppendLine(",Manpower_Efficiency_D1.[achieve_rate] as [AR-1] ")
        sql.AppendLine(",Manpower_Efficiency_D1.KPI as [KPI-1] ")
        sql.AppendLine(",Manpower_Efficiency_D2.[date] as [D-2] ")
        sql.AppendLine(",Manpower_Efficiency_D2.[target] as [T-2] ")
        sql.AppendLine(",Manpower_Efficiency_D2.[result] as [R-2] ")
        sql.AppendLine(",Manpower_Efficiency_D2.[achieve_rate] as [AR-2] ")
        sql.AppendLine(",Manpower_Efficiency_D2.KPI as [KPI-2] ")
        sql.AppendLine("from ")
        sql.AppendLine("( ")
        sql.AppendLine("	select top 2 ")
        sql.AppendLine("	[date] as [date] ")
        sql.AppendLine("	,TARGET_____920 as [target] ")
        sql.AppendLine("	,RESULT_500 as [result] ")
        sql.AppendLine("	,ACHIEVEMENT_RATE____ as [achieve_rate] ")
        sql.AppendLine("	,'Manpower Efficiency' as 'KPI' ")
        sql.AppendLine("	,ROW_NUMBER() OVER(ORDER BY [date] desc) AS Row# ")
        sql.AppendLine("	from [UserModules].[user].[DAILY_KPI_REPORT_2017] ")
        sql.AppendLine("	where cast([date] as date) < cast(@date as date) ")
        sql.AppendLine("	and division = @division ")
        sql.AppendLine("	order by [date] desc ")
        sql.AppendLine(") as Manpower_Efficiency_D1 ")
        sql.AppendLine("left join ")
        sql.AppendLine("( ")
        sql.AppendLine("	select top 2 ")
        sql.AppendLine("	[date] as [date] ")
        sql.AppendLine("	,TARGET_____920 as [target] ")
        sql.AppendLine("	,RESULT_500 as [result] ")
        sql.AppendLine("	,ACHIEVEMENT_RATE____ as [achieve_rate] ")
        sql.AppendLine("	,'Manpower Efficiency' as 'KPI' ")
        sql.AppendLine("	,ROW_NUMBER() OVER(ORDER BY [date] desc) AS Row# ")
        sql.AppendLine("	from [UserModules].[user].[DAILY_KPI_REPORT_2017] ")
        sql.AppendLine("	where cast([date] as date) < cast(@date as date) ")
        sql.AppendLine("	and division = @division ")
        sql.AppendLine("	order by [date] desc ")
        sql.AppendLine(") as Manpower_Efficiency_D2 ")
        sql.AppendLine("on Manpower_Efficiency_D1.[kpi]=Manpower_Efficiency_D2.[kpi] and Manpower_Efficiency_D1.[date]<>Manpower_Efficiency_D2.[date] ")
        sql.AppendLine("order by Manpower_Efficiency_D1.[date] desc ")
        sql.AppendLine(") as K2 ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("union all ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("--Loss Rate ")
        sql.AppendLine("select * from ( ")
        sql.AppendLine("select ")
        sql.AppendLine("top 1 ")
        sql.AppendLine("Loss_Rate_D1.[date] as [D-1] ")
        sql.AppendLine(",Loss_Rate_D1.[target] as [T-1] ")
        sql.AppendLine(",Loss_Rate_D1.[result] as [R-1] ")
        sql.AppendLine(",Loss_Rate_D1.[achieve_rate] as [AR-1] ")
        sql.AppendLine(",Loss_Rate_D1.KPI as [KPI-1] ")
        sql.AppendLine(",Loss_Rate_D2.[date] as [D-2] ")
        sql.AppendLine(",Loss_Rate_D2.[target] as [T-2] ")
        sql.AppendLine(",Loss_Rate_D2.[result] as [R-2] ")
        sql.AppendLine(",Loss_Rate_D2.[achieve_rate] as [AR-2] ")
        sql.AppendLine(",Loss_Rate_D2.KPI as [KPI-2] ")
        sql.AppendLine("from ")
        sql.AppendLine("( ")
        sql.AppendLine("	select top 2 ")
        sql.AppendLine("	[date] as [date] ")
        sql.AppendLine("	,TARGET_____232 as [target] ")
        sql.AppendLine("	,RESULT_921 as [result] ")
        sql.AppendLine("	,ACHIEVEMENT_RATE_____290 as [achieve_rate] ")
        sql.AppendLine("	,'Loss Rate' as 'KPI' ")
        sql.AppendLine("	,ROW_NUMBER() OVER(ORDER BY [date] desc) AS Row# ")
        sql.AppendLine("	from [UserModules].[user].[DAILY_KPI_REPORT_2017] ")
        sql.AppendLine("	where cast([date] as date) < cast(@date as date) ")
        sql.AppendLine("	and division = @division ")
        sql.AppendLine("	order by [date] desc ")
        sql.AppendLine(") as Loss_Rate_D1 ")
        sql.AppendLine("left join ")
        sql.AppendLine("( ")
        sql.AppendLine("	select top 2 ")
        sql.AppendLine("	[date] as [date] ")
        sql.AppendLine("	,TARGET_____232 as [target] ")
        sql.AppendLine("	,RESULT_921 as [result] ")
        sql.AppendLine("	,ACHIEVEMENT_RATE_____290 as [achieve_rate] ")
        sql.AppendLine("	,'Loss Rate' as 'KPI' ")
        sql.AppendLine("	,ROW_NUMBER() OVER(ORDER BY [date] desc) AS Row# ")
        sql.AppendLine("	from [UserModules].[user].[DAILY_KPI_REPORT_2017] ")
        sql.AppendLine("	where cast([date] as date) < cast(@date as date) ")
        sql.AppendLine("	and division = @division ")
        sql.AppendLine("	order by [date] desc ")
        sql.AppendLine(") as Loss_Rate_D2 ")
        sql.AppendLine("on Loss_Rate_D1.[kpi]=Loss_Rate_D2.[kpi] and Loss_Rate_D1.[date]<>Loss_Rate_D2.[date] ")
        sql.AppendLine("order by Loss_Rate_D1.[date] desc ")
        sql.AppendLine(") as K3 ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("union all ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("--Small Loss Rate ")
        sql.AppendLine("select * from ( ")
        sql.AppendLine("select ")
        sql.AppendLine("top 1 ")
        sql.AppendLine("Small_Loss_Rate_D1.[date] as [D-1] ")
        sql.AppendLine(",Small_Loss_Rate_D1.[target] as [T-1] ")
        sql.AppendLine(",Small_Loss_Rate_D1.[result] as [R-1] ")
        sql.AppendLine(",Small_Loss_Rate_D1.[achieve_rate] as [AR-1] ")
        sql.AppendLine(",Small_Loss_Rate_D1.KPI as [KPI-1] ")
        sql.AppendLine(",Small_Loss_Rate_D2.[date] as [D-2] ")
        sql.AppendLine(",Small_Loss_Rate_D2.[target] as [T-2] ")
        sql.AppendLine(",Small_Loss_Rate_D2.[result] as [R-2] ")
        sql.AppendLine(",Small_Loss_Rate_D2.[achieve_rate] as [AR-2] ")
        sql.AppendLine(",Small_Loss_Rate_D2.KPI as [KPI-2] ")
        sql.AppendLine("from ")
        sql.AppendLine("( ")
        sql.AppendLine("	select top 2 ")
        sql.AppendLine("	[date] as [date] ")
        sql.AppendLine("	,SMALL_LOSS_RATE as [target] ")
        sql.AppendLine("	,RESULT_15 as [result] ")
        sql.AppendLine("	,ACHIEVEMENT_RATE_____964 as [achieve_rate] ")
        sql.AppendLine("	,'Small Loss Rate' as 'KPI' ")
        sql.AppendLine("	,ROW_NUMBER() OVER(ORDER BY [date] desc) AS Row# ")
        sql.AppendLine("	from [UserModules].[user].[DAILY_KPI_REPORT_2017] ")
        sql.AppendLine("	where cast([date] as date) < cast(@date as date) ")
        sql.AppendLine("	and division = @division ")
        sql.AppendLine("	order by [date] desc ")
        sql.AppendLine(") as Small_Loss_Rate_D1 ")
        sql.AppendLine("left join ")
        sql.AppendLine("( ")
        sql.AppendLine("	select top 2 ")
        sql.AppendLine("	[date] as [date] ")
        sql.AppendLine("	,SMALL_LOSS_RATE as [target] ")
        sql.AppendLine("	,RESULT_15 as [result] ")
        sql.AppendLine("	,ACHIEVEMENT_RATE_____964 as [achieve_rate] ")
        sql.AppendLine("	,'Small Loss Rate' as 'KPI' ")
        sql.AppendLine("	,ROW_NUMBER() OVER(ORDER BY [date] desc) AS Row# ")
        sql.AppendLine("	from [UserModules].[user].[DAILY_KPI_REPORT_2017] ")
        sql.AppendLine("	where cast([date] as date) < cast(@date as date) ")
        sql.AppendLine("	and division = @division ")
        sql.AppendLine("	order by [date] desc ")
        sql.AppendLine(") as Small_Loss_Rate_D2 ")
        sql.AppendLine("on Small_Loss_Rate_D1.[kpi]=Small_Loss_Rate_D2.[kpi] and Small_Loss_Rate_D1.[date]<>Small_Loss_Rate_D2.[date] ")
        sql.AppendLine("order by Small_Loss_Rate_D1.[date] desc ")
        sql.AppendLine(") as K4 ")
        sql.AppendLine(" ")
        sql.AppendLine("union all ")
        sql.AppendLine(" ")
        sql.AppendLine("--Fixed Qty ")
        sql.AppendLine("select * from ( ")
        sql.AppendLine("select ")
        sql.AppendLine("top 1 ")
        sql.AppendLine("Fixed_Qty_D1.[date] as [D-1] ")
        sql.AppendLine(",Fixed_Qty_D1.[target] as [T-1] ")
        sql.AppendLine(",Fixed_Qty_D1.[result] as [R-1] ")
        sql.AppendLine(",Fixed_Qty_D1.[achieve_rate] as [AR-1] ")
        sql.AppendLine(",Fixed_Qty_D1.KPI as [KPI-1] ")
        sql.AppendLine(",Fixed_Qty_D2.[date] as [D-2] ")
        sql.AppendLine(",Fixed_Qty_D2.[target] as [T-2] ")
        sql.AppendLine(",Fixed_Qty_D2.[result] as [R-2] ")
        sql.AppendLine(",Fixed_Qty_D2.[achieve_rate] as [AR-2] ")
        sql.AppendLine(",Fixed_Qty_D2.KPI as [KPI-2] ")
        sql.AppendLine("from ")
        sql.AppendLine("( ")
        sql.AppendLine("	select top 2 ")
        sql.AppendLine("	[date] as [date] ")
        sql.AppendLine("	,TARGET_____636 as [target] ")
        sql.AppendLine("	,RESULT_650 as [result] ")
        sql.AppendLine("	,ACHIEVEMENT_RATE_____359 as [achieve_rate] ")
        sql.AppendLine("	,'Fixed Qty' as 'KPI' ")
        sql.AppendLine("	,ROW_NUMBER() OVER(ORDER BY [date] desc) AS Row# ")
        sql.AppendLine("	from [UserModules].[user].[DAILY_KPI_REPORT_2017] ")
        sql.AppendLine("	where cast([date] as date) < cast(@date as date) ")
        sql.AppendLine("	and division = @division ")
        sql.AppendLine("	order by [date] desc ")
        sql.AppendLine(") as Fixed_Qty_D1 ")
        sql.AppendLine("left join ")
        sql.AppendLine("( ")
        sql.AppendLine("	select top 2 ")
        sql.AppendLine("	[date] as [date] ")
        sql.AppendLine("	,TARGET_____636 as [target] ")
        sql.AppendLine("	,RESULT_650 as [result] ")
        sql.AppendLine("	,ACHIEVEMENT_RATE_____359 as [achieve_rate] ")
        sql.AppendLine("	,'Fixed Qty' as 'KPI' ")
        sql.AppendLine("	,ROW_NUMBER() OVER(ORDER BY [date] desc) AS Row# ")
        sql.AppendLine("	from [UserModules].[user].[DAILY_KPI_REPORT_2017] ")
        sql.AppendLine("	where cast([date] as date) < cast(@date as date) ")
        sql.AppendLine("	and division = @division ")
        sql.AppendLine("	order by [date] desc ")
        sql.AppendLine(") as Fixed_Qty_D2 ")
        sql.AppendLine("on Fixed_Qty_D1.[kpi]=Fixed_Qty_D2.[kpi] and Fixed_Qty_D1.[date]<>Fixed_Qty_D2.[date] ")
        sql.AppendLine("order by Fixed_Qty_D1.[date] desc ")
        sql.AppendLine(") as K5 ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("union all ")
        sql.AppendLine(" ")
        sql.AppendLine("--OTP ")
        sql.AppendLine("select * from ( ")
        sql.AppendLine("select ")
        sql.AppendLine("top 1 ")
        sql.AppendLine("OTP_D1.[date] as [D-1] ")
        sql.AppendLine(",OTP_D1.[target] as [T-1] ")
        sql.AppendLine(",OTP_D1.[result] as [R-1] ")
        sql.AppendLine(",OTP_D1.[achieve_rate] as [AR-1] ")
        sql.AppendLine(",OTP_D1.KPI as [KPI-1] ")
        sql.AppendLine(",OTP_D2.[date] as [D-2] ")
        sql.AppendLine(",OTP_D2.[target] as [T-2] ")
        sql.AppendLine(",OTP_D2.[result] as [R-2] ")
        sql.AppendLine(",OTP_D2.[achieve_rate] as [AR-2] ")
        sql.AppendLine(",OTP_D2.KPI as [KPI-2] ")
        sql.AppendLine("from ")
        sql.AppendLine("( ")
        sql.AppendLine("	select top 2 ")
        sql.AppendLine("	[date] as [date] ")
        sql.AppendLine("	,TARGET_____415 as [target] ")
        sql.AppendLine("	,RESULT_508 as [result] ")
        sql.AppendLine("	,ACHIEVEMENT_RATE_____32 as [achieve_rate] ")
        sql.AppendLine("	,'OTP' as 'KPI' ")
        sql.AppendLine("	,ROW_NUMBER() OVER(ORDER BY [date] desc) AS Row# ")
        sql.AppendLine("	from [UserModules].[user].[DAILY_KPI_REPORT_2017] ")
        sql.AppendLine("	where cast([date] as date) < cast(@date as date) ")
        sql.AppendLine("	and division = @division ")
        sql.AppendLine("	order by [date] desc ")
        sql.AppendLine(") as OTP_D1 ")
        sql.AppendLine("left join ")
        sql.AppendLine("( ")
        sql.AppendLine("	select top 2 ")
        sql.AppendLine("	[date] as [date] ")
        sql.AppendLine("	,TARGET_____415 as [target] ")
        sql.AppendLine("	,RESULT_508 as [result] ")
        sql.AppendLine("	,ACHIEVEMENT_RATE_____32 as [achieve_rate] ")
        sql.AppendLine("	,'OTP' as 'KPI' ")
        sql.AppendLine("	,ROW_NUMBER() OVER(ORDER BY [date] desc) AS Row# ")
        sql.AppendLine("	from [UserModules].[user].[DAILY_KPI_REPORT_2017] ")
        sql.AppendLine("	where cast([date] as date) < cast(@date as date) ")
        sql.AppendLine("	and division = @division ")
        sql.AppendLine("	order by [date] desc ")
        sql.AppendLine(") as OTP_D2 ")
        sql.AppendLine("on OTP_D1.[kpi]=OTP_D2.[kpi] and OTP_D1.[date]<>OTP_D2.[date] ")
        sql.AppendLine("order by OTP_D1.[date] desc ")
        sql.AppendLine(") as K6 ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("union all ")
        sql.AppendLine(" ")
        sql.AppendLine("--OQC Lot Reject ")
        sql.AppendLine("select * from ( ")
        sql.AppendLine("select ")
        sql.AppendLine("top 1 ")
        sql.AppendLine("OQC_Lot_Reject_D1.[date] as [D-1] ")
        sql.AppendLine(",OQC_Lot_Reject_D1.[target] as [T-1] ")
        sql.AppendLine(",OQC_Lot_Reject_D1.[result] as [R-1] ")
        sql.AppendLine(",OQC_Lot_Reject_D1.[achieve_rate] as [AR-1] ")
        sql.AppendLine(",OQC_Lot_Reject_D1.KPI as [KPI-1] ")
        sql.AppendLine(",OQC_Lot_Reject_D2.[date] as [D-2] ")
        sql.AppendLine(",OQC_Lot_Reject_D2.[target] as [T-2] ")
        sql.AppendLine(",OQC_Lot_Reject_D2.[result] as [R-2] ")
        sql.AppendLine(",OQC_Lot_Reject_D2.[achieve_rate] as [AR-2] ")
        sql.AppendLine(",OQC_Lot_Reject_D2.KPI as [KPI-2] ")
        sql.AppendLine("from ")
        sql.AppendLine("( ")
        sql.AppendLine("	select top 2 ")
        sql.AppendLine("	[date] as [date] ")
        sql.AppendLine("	,TARGET_____809 as [target] ")
        sql.AppendLine("	,RESULT_81 as [result] ")
        sql.AppendLine("	,ACHIEVEMENT_RATE_____375 as [achieve_rate] ")
        sql.AppendLine("	,'OQC Lot Reject' as 'KPI' ")
        sql.AppendLine("	,ROW_NUMBER() OVER(ORDER BY [date] desc) AS Row# ")
        sql.AppendLine("	from [UserModules].[user].[DAILY_KPI_REPORT_2017] ")
        sql.AppendLine("	where cast([date] as date) < cast(@date as date) ")
        sql.AppendLine("	and division = @division ")
        sql.AppendLine("	order by [date] desc ")
        sql.AppendLine(") as OQC_Lot_Reject_D1 ")
        sql.AppendLine("left join ")
        sql.AppendLine("( ")
        sql.AppendLine("	select top 2 ")
        sql.AppendLine("	[date] as [date] ")
        sql.AppendLine("	,TARGET_____809 as [target] ")
        sql.AppendLine("	,RESULT_81 as [result] ")
        sql.AppendLine("	,ACHIEVEMENT_RATE_____375 as [achieve_rate] ")
        sql.AppendLine("	,'OQC Lot Reject' as 'KPI' ")
        sql.AppendLine("	,ROW_NUMBER() OVER(ORDER BY [date] desc) AS Row# ")
        sql.AppendLine("	from [UserModules].[user].[DAILY_KPI_REPORT_2017] ")
        sql.AppendLine("	where cast([date] as date) < cast(@date as date) ")
        sql.AppendLine("	and division = @division ")
        sql.AppendLine("	order by [date] desc ")
        sql.AppendLine(") as OQC_Lot_Reject_D2 ")
        sql.AppendLine("on OQC_Lot_Reject_D1.[kpi]=OQC_Lot_Reject_D2.[kpi] and OQC_Lot_Reject_D1.[date]<>OQC_Lot_Reject_D2.[date] ")
        sql.AppendLine("order by OQC_Lot_Reject_D1.[date] desc ")
        sql.AppendLine(") as K7 ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("union all ")
        sql.AppendLine(" ")
        sql.AppendLine("--Defect rate ")
        sql.AppendLine("select * from ( ")
        sql.AppendLine("select ")
        sql.AppendLine("top 1 ")
        sql.AppendLine("Defect_rate_D1.[date] as [D-1] ")
        sql.AppendLine(",Defect_rate_D1.[target] as [T-1] ")
        sql.AppendLine(",Defect_rate_D1.[result] as [R-1] ")
        sql.AppendLine(",Defect_rate_D1.[achieve_rate] as [AR-1] ")
        sql.AppendLine(",Defect_rate_D1.KPI as [KPI-1] ")
        sql.AppendLine(",Defect_rate_D2.[date] as [D-2] ")
        sql.AppendLine(",Defect_rate_D2.[target] as [T-2] ")
        sql.AppendLine(",Defect_rate_D2.[result] as [R-2] ")
        sql.AppendLine(",Defect_rate_D2.[achieve_rate] as [AR-2] ")
        sql.AppendLine(",Defect_rate_D2.KPI as [KPI-2] ")
        sql.AppendLine("from ")
        sql.AppendLine("( ")
        sql.AppendLine("	select top 2 ")
        sql.AppendLine("	[date] as [date] ")
        sql.AppendLine("	,TARGET_____710 as [target] ")
        sql.AppendLine("	,RESULT_85 as [result] ")
        sql.AppendLine("	,ACHIEVEMENT_RATE_____790 as [achieve_rate] ")
        sql.AppendLine("	,'Defect rate' as 'KPI' ")
        sql.AppendLine("	,ROW_NUMBER() OVER(ORDER BY [date] desc) AS Row# ")
        sql.AppendLine("	from [UserModules].[user].[DAILY_KPI_REPORT_2017] ")
        sql.AppendLine("	where cast([date] as date) < cast(@date as date) ")
        sql.AppendLine("	and division = @division ")
        sql.AppendLine("	order by [date] desc ")
        sql.AppendLine(") as Defect_rate_D1 ")
        sql.AppendLine("left join ")
        sql.AppendLine("( ")
        sql.AppendLine("	select top 2 ")
        sql.AppendLine("	[date] as [date] ")
        sql.AppendLine("	,TARGET_____710 as [target] ")
        sql.AppendLine("	,RESULT_85 as [result] ")
        sql.AppendLine("	,ACHIEVEMENT_RATE_____790 as [achieve_rate] ")
        sql.AppendLine("	,'Defect rate' as 'KPI' ")
        sql.AppendLine("	,ROW_NUMBER() OVER(ORDER BY [date] desc) AS Row# ")
        sql.AppendLine("	from [UserModules].[user].[DAILY_KPI_REPORT_2017] ")
        sql.AppendLine("	where cast([date] as date) < cast(@date as date) ")
        sql.AppendLine("	and division = @division ")
        sql.AppendLine("	order by [date] desc ")
        sql.AppendLine(") as Defect_rate_D2 ")
        sql.AppendLine("on Defect_rate_D1.[kpi]=Defect_rate_D2.[kpi] and Defect_rate_D1.[date]<>Defect_rate_D2.[date] ")
        sql.AppendLine("order by Defect_rate_D1.[date] desc ")
        sql.AppendLine(") as K8 ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("union all ")
        sql.AppendLine(" ")
        sql.AppendLine("--E-pass Scan rate ")
        sql.AppendLine("select * from ( ")
        sql.AppendLine("select ")
        sql.AppendLine("top 1 ")
        sql.AppendLine("E_pass_Scan_rate_D1.[date] as [D-1] ")
        sql.AppendLine(",E_pass_Scan_rate_D1.[target] as [T-1] ")
        sql.AppendLine(",E_pass_Scan_rate_D1.[result] as [R-1] ")
        sql.AppendLine(",E_pass_Scan_rate_D1.[achieve_rate] as [AR-1] ")
        sql.AppendLine(",E_pass_Scan_rate_D1.KPI as [KPI-1] ")
        sql.AppendLine(",E_pass_Scan_rate_D2.[date] as [D-2] ")
        sql.AppendLine(",E_pass_Scan_rate_D2.[target] as [T-2] ")
        sql.AppendLine(",E_pass_Scan_rate_D2.[result] as [R-2] ")
        sql.AppendLine(",E_pass_Scan_rate_D2.[achieve_rate] as [AR-2] ")
        sql.AppendLine(",E_pass_Scan_rate_D2.KPI as [KPI-2] ")
        sql.AppendLine("from ")
        sql.AppendLine("( ")
        sql.AppendLine("	select top 2 ")
        sql.AppendLine("	[date] as [date] ")
        sql.AppendLine("	,TARGET_____560 as [target] ")
        sql.AppendLine("	,RESULT_897 as [result] ")
        sql.AppendLine("	,ACHIEVEMENT_RATE_____745 as [achieve_rate] ")
        sql.AppendLine("	,'E-pass Scan rate' as 'KPI' ")
        sql.AppendLine("	,ROW_NUMBER() OVER(ORDER BY [date] desc) AS Row# ")
        sql.AppendLine("	from [UserModules].[user].[DAILY_KPI_REPORT_2017] ")
        sql.AppendLine("	where cast([date] as date) < cast(@date as date) ")
        sql.AppendLine("	and division = @division ")
        sql.AppendLine("	order by [date] desc ")
        sql.AppendLine(") as E_pass_Scan_rate_D1 ")
        sql.AppendLine("left join ")
        sql.AppendLine("( ")
        sql.AppendLine("	select top 2 ")
        sql.AppendLine("	[date] as [date] ")
        sql.AppendLine("	,TARGET_____560 as [target] ")
        sql.AppendLine("	,RESULT_897 as [result] ")
        sql.AppendLine("	,ACHIEVEMENT_RATE_____745 as [achieve_rate] ")
        sql.AppendLine("	,'E-pass Scan rate' as 'KPI' ")
        sql.AppendLine("	,ROW_NUMBER() OVER(ORDER BY [date] desc) AS Row# ")
        sql.AppendLine("	from [UserModules].[user].[DAILY_KPI_REPORT_2017] ")
        sql.AppendLine("	where cast([date] as date) < cast(@date as date) ")
        sql.AppendLine("	and division = @division ")
        sql.AppendLine("	order by [date] desc ")
        sql.AppendLine(") as E_pass_Scan_rate_D2 ")
        sql.AppendLine("on E_pass_Scan_rate_D1.[kpi]=E_pass_Scan_rate_D2.[kpi] and E_pass_Scan_rate_D1.[date]<>E_pass_Scan_rate_D2.[date] ")
        sql.AppendLine("order by E_pass_Scan_rate_D1.[date] desc ")
        sql.AppendLine(") as K9 ")

        Sql_linear.SelectCommand = sql.ToString


        'Средние значения, даты для заголовка и легенды
        sql_caption.AppendLine("declare @date date ")
        sql_caption.AppendLine("set @date = getdate() ")
        sql_caption.AppendLine("declare @division nvarchar(max) ")
        sql_caption.AppendLine("set @division = '" + division + "' ")
        sql_caption.AppendLine(" ")
        sql_caption.AppendLine("select ")
        sql_caption.AppendLine("[D-1] ")
        sql_caption.AppendLine(",[D-2] ")
        sql_caption.AppendLine(",cast(avg(cast([AR-1] as numeric)) as decimal(20,1)) as [avg_D-1] ")
        sql_caption.AppendLine(",cast(avg(cast([AR-2] as numeric)) as decimal(20,1)) as [avg_D-2] ")
        sql_caption.AppendLine("from ")
        sql_caption.AppendLine("( ")
        sql_caption.AppendLine("--TTL Productivity Employee ")
        sql_caption.AppendLine("select * from ( ")
        sql_caption.AppendLine("select ")
        sql_caption.AppendLine("top 1 ")
        sql_caption.AppendLine("TTL_Productivity_Employee_D1.[date] as [D-1] ")
        sql_caption.AppendLine(",TTL_Productivity_Employee_D1.[target] as [T-1] ")
        sql_caption.AppendLine(",TTL_Productivity_Employee_D1.[result] as [R-1] ")
        sql_caption.AppendLine(",TTL_Productivity_Employee_D1.[achieve_rate] as [AR-1] ")
        sql_caption.AppendLine(",TTL_Productivity_Employee_D1.KPI as [KPI-1] ")
        sql_caption.AppendLine(",TTL_Productivity_Employee_D2.[date] as [D-2] ")
        sql_caption.AppendLine(",TTL_Productivity_Employee_D2.[target] as [T-2] ")
        sql_caption.AppendLine(",TTL_Productivity_Employee_D2.[result] as [R-2] ")
        sql_caption.AppendLine(",TTL_Productivity_Employee_D2.[achieve_rate] as [AR-2] ")
        sql_caption.AppendLine(",TTL_Productivity_Employee_D2.KPI as [KPI-2] ")
        sql_caption.AppendLine("from ")
        sql_caption.AppendLine("( ")
        sql_caption.AppendLine("	select top 2 ")
        sql_caption.AppendLine("	[date] as [date] ")
        sql_caption.AppendLine("	,TARGET____ as [target] ")
        sql_caption.AppendLine("	,RESULT as [result] ")
        sql_caption.AppendLine("	,ACHIEVEMENT_RAYE____ as [achieve_rate] ")
        sql_caption.AppendLine("	,'TTL Productivity Employee' as 'KPI' ")
        sql_caption.AppendLine("	,ROW_NUMBER() OVER(ORDER BY [date] desc) AS Row# ")
        sql_caption.AppendLine("	from [UserModules].[user].[DAILY_KPI_REPORT_2017] ")
        sql_caption.AppendLine("	where cast([date] as date) < cast(@date as date) ")
        sql_caption.AppendLine("	and division = @division ")
        sql_caption.AppendLine("	order by [date] desc ")
        sql_caption.AppendLine(") as TTL_Productivity_Employee_D1 ")
        sql_caption.AppendLine("left join ")
        sql_caption.AppendLine("( ")
        sql_caption.AppendLine("	select top 2 ")
        sql_caption.AppendLine("	[date] as [date] ")
        sql_caption.AppendLine("	,TARGET____ as [target] ")
        sql_caption.AppendLine("	,RESULT as [result] ")
        sql_caption.AppendLine("	,ACHIEVEMENT_RAYE____ as [achieve_rate] ")
        sql_caption.AppendLine("	,'TTL Productivity Employee' as 'KPI' ")
        sql_caption.AppendLine("	,ROW_NUMBER() OVER(ORDER BY [date] desc) AS Row# ")
        sql_caption.AppendLine("	from [UserModules].[user].[DAILY_KPI_REPORT_2017] ")
        sql_caption.AppendLine("	where cast([date] as date) < cast(@date as date) ")
        sql_caption.AppendLine("	and division = @division ")
        sql_caption.AppendLine("	order by [date] desc ")
        sql_caption.AppendLine(") as TTL_Productivity_Employee_D2 ")
        sql_caption.AppendLine("on TTL_Productivity_Employee_D1.[kpi]=TTL_Productivity_Employee_D2.[kpi] and TTL_Productivity_Employee_D1.[date]<>TTL_Productivity_Employee_D2.[date] ")
        sql_caption.AppendLine("order by TTL_Productivity_Employee_D1.[date] desc ")
        sql_caption.AppendLine(") as K1 ")
        sql_caption.AppendLine(" ")
        sql_caption.AppendLine("union all ")
        sql_caption.AppendLine(" ")
        sql_caption.AppendLine(" ")
        sql_caption.AppendLine("--Manpower Efficiency ")
        sql_caption.AppendLine("select * from ( ")
        sql_caption.AppendLine("select ")
        sql_caption.AppendLine("top 1 ")
        sql_caption.AppendLine("Manpower_Efficiency_D1.[date] as [D-1] ")
        sql_caption.AppendLine(",Manpower_Efficiency_D1.[target] as [T-1] ")
        sql_caption.AppendLine(",Manpower_Efficiency_D1.[result] as [R-1] ")
        sql_caption.AppendLine(",Manpower_Efficiency_D1.[achieve_rate] as [AR-1] ")
        sql_caption.AppendLine(",Manpower_Efficiency_D1.KPI as [KPI-1] ")
        sql_caption.AppendLine(",Manpower_Efficiency_D2.[date] as [D-2] ")
        sql_caption.AppendLine(",Manpower_Efficiency_D2.[target] as [T-2] ")
        sql_caption.AppendLine(",Manpower_Efficiency_D2.[result] as [R-2] ")
        sql_caption.AppendLine(",Manpower_Efficiency_D2.[achieve_rate] as [AR-2] ")
        sql_caption.AppendLine(",Manpower_Efficiency_D2.KPI as [KPI-2] ")
        sql_caption.AppendLine("from ")
        sql_caption.AppendLine("( ")
        sql_caption.AppendLine("	select top 2 ")
        sql_caption.AppendLine("	[date] as [date] ")
        sql_caption.AppendLine("	,TARGET_____920 as [target] ")
        sql_caption.AppendLine("	,RESULT_500 as [result] ")
        sql_caption.AppendLine("	,ACHIEVEMENT_RATE____ as [achieve_rate] ")
        sql_caption.AppendLine("	,'Manpower Efficiency' as 'KPI' ")
        sql_caption.AppendLine("	,ROW_NUMBER() OVER(ORDER BY [date] desc) AS Row# ")
        sql_caption.AppendLine("	from [UserModules].[user].[DAILY_KPI_REPORT_2017] ")
        sql_caption.AppendLine("	where cast([date] as date) < cast(@date as date) ")
        sql_caption.AppendLine("	and division = @division ")
        sql_caption.AppendLine("	order by [date] desc ")
        sql_caption.AppendLine(") as Manpower_Efficiency_D1 ")
        sql_caption.AppendLine("left join ")
        sql_caption.AppendLine("( ")
        sql_caption.AppendLine("	select top 2 ")
        sql_caption.AppendLine("	[date] as [date] ")
        sql_caption.AppendLine("	,TARGET_____920 as [target] ")
        sql_caption.AppendLine("	,RESULT_500 as [result] ")
        sql_caption.AppendLine("	,ACHIEVEMENT_RATE____ as [achieve_rate] ")
        sql_caption.AppendLine("	,'Manpower Efficiency' as 'KPI' ")
        sql_caption.AppendLine("	,ROW_NUMBER() OVER(ORDER BY [date] desc) AS Row# ")
        sql_caption.AppendLine("	from [UserModules].[user].[DAILY_KPI_REPORT_2017] ")
        sql_caption.AppendLine("	where cast([date] as date) < cast(@date as date) ")
        sql_caption.AppendLine("	and division = @division ")
        sql_caption.AppendLine("	order by [date] desc ")
        sql_caption.AppendLine(") as Manpower_Efficiency_D2 ")
        sql_caption.AppendLine("on Manpower_Efficiency_D1.[kpi]=Manpower_Efficiency_D2.[kpi] and Manpower_Efficiency_D1.[date]<>Manpower_Efficiency_D2.[date] ")
        sql_caption.AppendLine("order by Manpower_Efficiency_D1.[date] desc ")
        sql_caption.AppendLine(") as K2 ")
        sql_caption.AppendLine(" ")
        sql_caption.AppendLine(" ")
        sql_caption.AppendLine("union all ")
        sql_caption.AppendLine(" ")
        sql_caption.AppendLine(" ")
        sql_caption.AppendLine("--Loss Rate ")
        sql_caption.AppendLine("select * from ( ")
        sql_caption.AppendLine("select ")
        sql_caption.AppendLine("top 1 ")
        sql_caption.AppendLine("Loss_Rate_D1.[date] as [D-1] ")
        sql_caption.AppendLine(",Loss_Rate_D1.[target] as [T-1] ")
        sql_caption.AppendLine(",Loss_Rate_D1.[result] as [R-1] ")
        sql_caption.AppendLine(",Loss_Rate_D1.[achieve_rate] as [AR-1] ")
        sql_caption.AppendLine(",Loss_Rate_D1.KPI as [KPI-1] ")
        sql_caption.AppendLine(",Loss_Rate_D2.[date] as [D-2] ")
        sql_caption.AppendLine(",Loss_Rate_D2.[target] as [T-2] ")
        sql_caption.AppendLine(",Loss_Rate_D2.[result] as [R-2] ")
        sql_caption.AppendLine(",Loss_Rate_D2.[achieve_rate] as [AR-2] ")
        sql_caption.AppendLine(",Loss_Rate_D2.KPI as [KPI-2] ")
        sql_caption.AppendLine("from ")
        sql_caption.AppendLine("( ")
        sql_caption.AppendLine("	select top 2 ")
        sql_caption.AppendLine("	[date] as [date] ")
        sql_caption.AppendLine("	,TARGET_____232 as [target] ")
        sql_caption.AppendLine("	,RESULT_921 as [result] ")
        sql_caption.AppendLine("	,ACHIEVEMENT_RATE_____290 as [achieve_rate] ")
        sql_caption.AppendLine("	,'Loss Rate' as 'KPI' ")
        sql_caption.AppendLine("	,ROW_NUMBER() OVER(ORDER BY [date] desc) AS Row# ")
        sql_caption.AppendLine("	from [UserModules].[user].[DAILY_KPI_REPORT_2017] ")
        sql_caption.AppendLine("	where cast([date] as date) < cast(@date as date) ")
        sql_caption.AppendLine("	and division = @division ")
        sql_caption.AppendLine("	order by [date] desc ")
        sql_caption.AppendLine(") as Loss_Rate_D1 ")
        sql_caption.AppendLine("left join ")
        sql_caption.AppendLine("( ")
        sql_caption.AppendLine("	select top 2 ")
        sql_caption.AppendLine("	[date] as [date] ")
        sql_caption.AppendLine("	,TARGET_____232 as [target] ")
        sql_caption.AppendLine("	,RESULT_921 as [result] ")
        sql_caption.AppendLine("	,ACHIEVEMENT_RATE_____290 as [achieve_rate] ")
        sql_caption.AppendLine("	,'Loss Rate' as 'KPI' ")
        sql_caption.AppendLine("	,ROW_NUMBER() OVER(ORDER BY [date] desc) AS Row# ")
        sql_caption.AppendLine("	from [UserModules].[user].[DAILY_KPI_REPORT_2017] ")
        sql_caption.AppendLine("	where cast([date] as date) < cast(@date as date) ")
        sql_caption.AppendLine("	and division = @division ")
        sql_caption.AppendLine("	order by [date] desc ")
        sql_caption.AppendLine(") as Loss_Rate_D2 ")
        sql_caption.AppendLine("on Loss_Rate_D1.[kpi]=Loss_Rate_D2.[kpi] and Loss_Rate_D1.[date]<>Loss_Rate_D2.[date] ")
        sql_caption.AppendLine("order by Loss_Rate_D1.[date] desc ")
        sql_caption.AppendLine(") as K3 ")
        sql_caption.AppendLine(" ")
        sql_caption.AppendLine(" ")
        sql_caption.AppendLine(" ")
        sql_caption.AppendLine("union all ")
        sql_caption.AppendLine(" ")
        sql_caption.AppendLine(" ")
        sql_caption.AppendLine("--Small Loss Rate ")
        sql_caption.AppendLine("select * from ( ")
        sql_caption.AppendLine("select ")
        sql_caption.AppendLine("top 1 ")
        sql_caption.AppendLine("Small_Loss_Rate_D1.[date] as [D-1] ")
        sql_caption.AppendLine(",Small_Loss_Rate_D1.[target] as [T-1] ")
        sql_caption.AppendLine(",Small_Loss_Rate_D1.[result] as [R-1] ")
        sql_caption.AppendLine(",Small_Loss_Rate_D1.[achieve_rate] as [AR-1] ")
        sql_caption.AppendLine(",Small_Loss_Rate_D1.KPI as [KPI-1] ")
        sql_caption.AppendLine(",Small_Loss_Rate_D2.[date] as [D-2] ")
        sql_caption.AppendLine(",Small_Loss_Rate_D2.[target] as [T-2] ")
        sql_caption.AppendLine(",Small_Loss_Rate_D2.[result] as [R-2] ")
        sql_caption.AppendLine(",Small_Loss_Rate_D2.[achieve_rate] as [AR-2] ")
        sql_caption.AppendLine(",Small_Loss_Rate_D2.KPI as [KPI-2] ")
        sql_caption.AppendLine("from ")
        sql_caption.AppendLine("( ")
        sql_caption.AppendLine("	select top 2 ")
        sql_caption.AppendLine("	[date] as [date] ")
        sql_caption.AppendLine("	,SMALL_LOSS_RATE as [target] ")
        sql_caption.AppendLine("	,RESULT_15 as [result] ")
        sql_caption.AppendLine("	,ACHIEVEMENT_RATE_____964 as [achieve_rate] ")
        sql_caption.AppendLine("	,'Small Loss Rate' as 'KPI' ")
        sql_caption.AppendLine("	,ROW_NUMBER() OVER(ORDER BY [date] desc) AS Row# ")
        sql_caption.AppendLine("	from [UserModules].[user].[DAILY_KPI_REPORT_2017] ")
        sql_caption.AppendLine("	where cast([date] as date) < cast(@date as date) ")
        sql_caption.AppendLine("	and division = @division ")
        sql_caption.AppendLine("	order by [date] desc ")
        sql_caption.AppendLine(") as Small_Loss_Rate_D1 ")
        sql_caption.AppendLine("left join ")
        sql_caption.AppendLine("( ")
        sql_caption.AppendLine("	select top 2 ")
        sql_caption.AppendLine("	[date] as [date] ")
        sql_caption.AppendLine("	,SMALL_LOSS_RATE as [target] ")
        sql_caption.AppendLine("	,RESULT_15 as [result] ")
        sql_caption.AppendLine("	,ACHIEVEMENT_RATE_____964 as [achieve_rate] ")
        sql_caption.AppendLine("	,'Small Loss Rate' as 'KPI' ")
        sql_caption.AppendLine("	,ROW_NUMBER() OVER(ORDER BY [date] desc) AS Row# ")
        sql_caption.AppendLine("	from [UserModules].[user].[DAILY_KPI_REPORT_2017] ")
        sql_caption.AppendLine("	where cast([date] as date) < cast(@date as date) ")
        sql_caption.AppendLine("	and division = @division ")
        sql_caption.AppendLine("	order by [date] desc ")
        sql_caption.AppendLine(") as Small_Loss_Rate_D2 ")
        sql_caption.AppendLine("on Small_Loss_Rate_D1.[kpi]=Small_Loss_Rate_D2.[kpi] and Small_Loss_Rate_D1.[date]<>Small_Loss_Rate_D2.[date] ")
        sql_caption.AppendLine("order by Small_Loss_Rate_D1.[date] desc ")
        sql_caption.AppendLine(") as K4 ")
        sql_caption.AppendLine(" ")
        sql_caption.AppendLine("union all ")
        sql_caption.AppendLine(" ")
        sql_caption.AppendLine("--Fixed Qty ")
        sql_caption.AppendLine("select * from ( ")
        sql_caption.AppendLine("select ")
        sql_caption.AppendLine("top 1 ")
        sql_caption.AppendLine("Fixed_Qty_D1.[date] as [D-1] ")
        sql_caption.AppendLine(",Fixed_Qty_D1.[target] as [T-1] ")
        sql_caption.AppendLine(",Fixed_Qty_D1.[result] as [R-1] ")
        sql_caption.AppendLine(",Fixed_Qty_D1.[achieve_rate] as [AR-1] ")
        sql_caption.AppendLine(",Fixed_Qty_D1.KPI as [KPI-1] ")
        sql_caption.AppendLine(",Fixed_Qty_D2.[date] as [D-2] ")
        sql_caption.AppendLine(",Fixed_Qty_D2.[target] as [T-2] ")
        sql_caption.AppendLine(",Fixed_Qty_D2.[result] as [R-2] ")
        sql_caption.AppendLine(",Fixed_Qty_D2.[achieve_rate] as [AR-2] ")
        sql_caption.AppendLine(",Fixed_Qty_D2.KPI as [KPI-2] ")
        sql_caption.AppendLine("from ")
        sql_caption.AppendLine("( ")
        sql_caption.AppendLine("	select top 2 ")
        sql_caption.AppendLine("	[date] as [date] ")
        sql_caption.AppendLine("	,TARGET_____636 as [target] ")
        sql_caption.AppendLine("	,RESULT_650 as [result] ")
        sql_caption.AppendLine("	,ACHIEVEMENT_RATE_____359 as [achieve_rate] ")
        sql_caption.AppendLine("	,'Fixed Qty' as 'KPI' ")
        sql_caption.AppendLine("	,ROW_NUMBER() OVER(ORDER BY [date] desc) AS Row# ")
        sql_caption.AppendLine("	from [UserModules].[user].[DAILY_KPI_REPORT_2017] ")
        sql_caption.AppendLine("	where cast([date] as date) < cast(@date as date) ")
        sql_caption.AppendLine("	and division = @division ")
        sql_caption.AppendLine("	order by [date] desc ")
        sql_caption.AppendLine(") as Fixed_Qty_D1 ")
        sql_caption.AppendLine("left join ")
        sql_caption.AppendLine("( ")
        sql_caption.AppendLine("	select top 2 ")
        sql_caption.AppendLine("	[date] as [date] ")
        sql_caption.AppendLine("	,TARGET_____636 as [target] ")
        sql_caption.AppendLine("	,RESULT_650 as [result] ")
        sql_caption.AppendLine("	,ACHIEVEMENT_RATE_____359 as [achieve_rate] ")
        sql_caption.AppendLine("	,'Fixed Qty' as 'KPI' ")
        sql_caption.AppendLine("	,ROW_NUMBER() OVER(ORDER BY [date] desc) AS Row# ")
        sql_caption.AppendLine("	from [UserModules].[user].[DAILY_KPI_REPORT_2017] ")
        sql_caption.AppendLine("	where cast([date] as date) < cast(@date as date) ")
        sql_caption.AppendLine("	and division = @division ")
        sql_caption.AppendLine("	order by [date] desc ")
        sql_caption.AppendLine(") as Fixed_Qty_D2 ")
        sql_caption.AppendLine("on Fixed_Qty_D1.[kpi]=Fixed_Qty_D2.[kpi] and Fixed_Qty_D1.[date]<>Fixed_Qty_D2.[date] ")
        sql_caption.AppendLine("order by Fixed_Qty_D1.[date] desc ")
        sql_caption.AppendLine(") as K5 ")
        sql_caption.AppendLine(" ")
        sql_caption.AppendLine(" ")
        sql_caption.AppendLine("union all ")
        sql_caption.AppendLine(" ")
        sql_caption.AppendLine("--OTP ")
        sql_caption.AppendLine("select * from ( ")
        sql_caption.AppendLine("select ")
        sql_caption.AppendLine("top 1 ")
        sql_caption.AppendLine("OTP_D1.[date] as [D-1] ")
        sql_caption.AppendLine(",OTP_D1.[target] as [T-1] ")
        sql_caption.AppendLine(",OTP_D1.[result] as [R-1] ")
        sql_caption.AppendLine(",OTP_D1.[achieve_rate] as [AR-1] ")
        sql_caption.AppendLine(",OTP_D1.KPI as [KPI-1] ")
        sql_caption.AppendLine(",OTP_D2.[date] as [D-2] ")
        sql_caption.AppendLine(",OTP_D2.[target] as [T-2] ")
        sql_caption.AppendLine(",OTP_D2.[result] as [R-2] ")
        sql_caption.AppendLine(",OTP_D2.[achieve_rate] as [AR-2] ")
        sql_caption.AppendLine(",OTP_D2.KPI as [KPI-2] ")
        sql_caption.AppendLine("from ")
        sql_caption.AppendLine("( ")
        sql_caption.AppendLine("	select top 2 ")
        sql_caption.AppendLine("	[date] as [date] ")
        sql_caption.AppendLine("	,TARGET_____415 as [target] ")
        sql_caption.AppendLine("	,RESULT_508 as [result] ")
        sql_caption.AppendLine("	,ACHIEVEMENT_RATE_____32 as [achieve_rate] ")
        sql_caption.AppendLine("	,'OTP' as 'KPI' ")
        sql_caption.AppendLine("	,ROW_NUMBER() OVER(ORDER BY [date] desc) AS Row# ")
        sql_caption.AppendLine("	from [UserModules].[user].[DAILY_KPI_REPORT_2017] ")
        sql_caption.AppendLine("	where cast([date] as date) < cast(@date as date) ")
        sql_caption.AppendLine("	and division = @division ")
        sql_caption.AppendLine("	order by [date] desc ")
        sql_caption.AppendLine(") as OTP_D1 ")
        sql_caption.AppendLine("left join ")
        sql_caption.AppendLine("( ")
        sql_caption.AppendLine("	select top 2 ")
        sql_caption.AppendLine("	[date] as [date] ")
        sql_caption.AppendLine("	,TARGET_____415 as [target] ")
        sql_caption.AppendLine("	,RESULT_508 as [result] ")
        sql_caption.AppendLine("	,ACHIEVEMENT_RATE_____32 as [achieve_rate] ")
        sql_caption.AppendLine("	,'OTP' as 'KPI' ")
        sql_caption.AppendLine("	,ROW_NUMBER() OVER(ORDER BY [date] desc) AS Row# ")
        sql_caption.AppendLine("	from [UserModules].[user].[DAILY_KPI_REPORT_2017] ")
        sql_caption.AppendLine("	where cast([date] as date) < cast(@date as date) ")
        sql_caption.AppendLine("	and division = @division ")
        sql_caption.AppendLine("	order by [date] desc ")
        sql_caption.AppendLine(") as OTP_D2 ")
        sql_caption.AppendLine("on OTP_D1.[kpi]=OTP_D2.[kpi] and OTP_D1.[date]<>OTP_D2.[date] ")
        sql_caption.AppendLine("order by OTP_D1.[date] desc ")
        sql_caption.AppendLine(") as K6 ")
        sql_caption.AppendLine(" ")
        sql_caption.AppendLine(" ")
        sql_caption.AppendLine("union all ")
        sql_caption.AppendLine(" ")
        sql_caption.AppendLine("--OQC Lot Reject ")
        sql_caption.AppendLine("select * from ( ")
        sql_caption.AppendLine("select ")
        sql_caption.AppendLine("top 1 ")
        sql_caption.AppendLine("OQC_Lot_Reject_D1.[date] as [D-1] ")
        sql_caption.AppendLine(",OQC_Lot_Reject_D1.[target] as [T-1] ")
        sql_caption.AppendLine(",OQC_Lot_Reject_D1.[result] as [R-1] ")
        sql_caption.AppendLine(",OQC_Lot_Reject_D1.[achieve_rate] as [AR-1] ")
        sql_caption.AppendLine(",OQC_Lot_Reject_D1.KPI as [KPI-1] ")
        sql_caption.AppendLine(",OQC_Lot_Reject_D2.[date] as [D-2] ")
        sql_caption.AppendLine(",OQC_Lot_Reject_D2.[target] as [T-2] ")
        sql_caption.AppendLine(",OQC_Lot_Reject_D2.[result] as [R-2] ")
        sql_caption.AppendLine(",OQC_Lot_Reject_D2.[achieve_rate] as [AR-2] ")
        sql_caption.AppendLine(",OQC_Lot_Reject_D2.KPI as [KPI-2] ")
        sql_caption.AppendLine("from ")
        sql_caption.AppendLine("( ")
        sql_caption.AppendLine("	select top 2 ")
        sql_caption.AppendLine("	[date] as [date] ")
        sql_caption.AppendLine("	,TARGET_____809 as [target] ")
        sql_caption.AppendLine("	,RESULT_81 as [result] ")
        sql_caption.AppendLine("	,ACHIEVEMENT_RATE_____375 as [achieve_rate] ")
        sql_caption.AppendLine("	,'OQC Lot Reject' as 'KPI' ")
        sql_caption.AppendLine("	,ROW_NUMBER() OVER(ORDER BY [date] desc) AS Row# ")
        sql_caption.AppendLine("	from [UserModules].[user].[DAILY_KPI_REPORT_2017] ")
        sql_caption.AppendLine("	where cast([date] as date) < cast(@date as date) ")
        sql_caption.AppendLine("	and division = @division ")
        sql_caption.AppendLine("	order by [date] desc ")
        sql_caption.AppendLine(") as OQC_Lot_Reject_D1 ")
        sql_caption.AppendLine("left join ")
        sql_caption.AppendLine("( ")
        sql_caption.AppendLine("	select top 2 ")
        sql_caption.AppendLine("	[date] as [date] ")
        sql_caption.AppendLine("	,TARGET_____809 as [target] ")
        sql_caption.AppendLine("	,RESULT_81 as [result] ")
        sql_caption.AppendLine("	,ACHIEVEMENT_RATE_____375 as [achieve_rate] ")
        sql_caption.AppendLine("	,'OQC Lot Reject' as 'KPI' ")
        sql_caption.AppendLine("	,ROW_NUMBER() OVER(ORDER BY [date] desc) AS Row# ")
        sql_caption.AppendLine("	from [UserModules].[user].[DAILY_KPI_REPORT_2017] ")
        sql_caption.AppendLine("	where cast([date] as date) < cast(@date as date) ")
        sql_caption.AppendLine("	and division = @division ")
        sql_caption.AppendLine("	order by [date] desc ")
        sql_caption.AppendLine(") as OQC_Lot_Reject_D2 ")
        sql_caption.AppendLine("on OQC_Lot_Reject_D1.[kpi]=OQC_Lot_Reject_D2.[kpi] and OQC_Lot_Reject_D1.[date]<>OQC_Lot_Reject_D2.[date] ")
        sql_caption.AppendLine("order by OQC_Lot_Reject_D1.[date] desc ")
        sql_caption.AppendLine(") as K7 ")
        sql_caption.AppendLine(" ")
        sql_caption.AppendLine(" ")
        sql_caption.AppendLine("union all ")
        sql_caption.AppendLine(" ")
        sql_caption.AppendLine("--Defect rate ")
        sql_caption.AppendLine("select * from ( ")
        sql_caption.AppendLine("select ")
        sql_caption.AppendLine("top 1 ")
        sql_caption.AppendLine("Defect_rate_D1.[date] as [D-1] ")
        sql_caption.AppendLine(",Defect_rate_D1.[target] as [T-1] ")
        sql_caption.AppendLine(",Defect_rate_D1.[result] as [R-1] ")
        sql_caption.AppendLine(",Defect_rate_D1.[achieve_rate] as [AR-1] ")
        sql_caption.AppendLine(",Defect_rate_D1.KPI as [KPI-1] ")
        sql_caption.AppendLine(",Defect_rate_D2.[date] as [D-2] ")
        sql_caption.AppendLine(",Defect_rate_D2.[target] as [T-2] ")
        sql_caption.AppendLine(",Defect_rate_D2.[result] as [R-2] ")
        sql_caption.AppendLine(",Defect_rate_D2.[achieve_rate] as [AR-2] ")
        sql_caption.AppendLine(",Defect_rate_D2.KPI as [KPI-2] ")
        sql_caption.AppendLine("from ")
        sql_caption.AppendLine("( ")
        sql_caption.AppendLine("	select top 2 ")
        sql_caption.AppendLine("	[date] as [date] ")
        sql_caption.AppendLine("	,TARGET_____710 as [target] ")
        sql_caption.AppendLine("	,RESULT_85 as [result] ")
        sql_caption.AppendLine("	,ACHIEVEMENT_RATE_____790 as [achieve_rate] ")
        sql_caption.AppendLine("	,'Defect rate' as 'KPI' ")
        sql_caption.AppendLine("	,ROW_NUMBER() OVER(ORDER BY [date] desc) AS Row# ")
        sql_caption.AppendLine("	from [UserModules].[user].[DAILY_KPI_REPORT_2017] ")
        sql_caption.AppendLine("	where cast([date] as date) < cast(@date as date) ")
        sql_caption.AppendLine("	and division = @division ")
        sql_caption.AppendLine("	order by [date] desc ")
        sql_caption.AppendLine(") as Defect_rate_D1 ")
        sql_caption.AppendLine("left join ")
        sql_caption.AppendLine("( ")
        sql_caption.AppendLine("	select top 2 ")
        sql_caption.AppendLine("	[date] as [date] ")
        sql_caption.AppendLine("	,TARGET_____710 as [target] ")
        sql_caption.AppendLine("	,RESULT_85 as [result] ")
        sql_caption.AppendLine("	,ACHIEVEMENT_RATE_____790 as [achieve_rate] ")
        sql_caption.AppendLine("	,'Defect rate' as 'KPI' ")
        sql_caption.AppendLine("	,ROW_NUMBER() OVER(ORDER BY [date] desc) AS Row# ")
        sql_caption.AppendLine("	from [UserModules].[user].[DAILY_KPI_REPORT_2017] ")
        sql_caption.AppendLine("	where cast([date] as date) < cast(@date as date) ")
        sql_caption.AppendLine("	and division = @division ")
        sql_caption.AppendLine("	order by [date] desc ")
        sql_caption.AppendLine(") as Defect_rate_D2 ")
        sql_caption.AppendLine("on Defect_rate_D1.[kpi]=Defect_rate_D2.[kpi] and Defect_rate_D1.[date]<>Defect_rate_D2.[date] ")
        sql_caption.AppendLine("order by Defect_rate_D1.[date] desc ")
        sql_caption.AppendLine(") as K8 ")
        sql_caption.AppendLine(" ")
        sql_caption.AppendLine(" ")
        sql_caption.AppendLine("union all ")
        sql_caption.AppendLine(" ")
        sql_caption.AppendLine("--E-pass Scan rate ")
        sql_caption.AppendLine("select * from ( ")
        sql_caption.AppendLine("select ")
        sql_caption.AppendLine("top 1 ")
        sql_caption.AppendLine("E_pass_Scan_rate_D1.[date] as [D-1] ")
        sql_caption.AppendLine(",E_pass_Scan_rate_D1.[target] as [T-1] ")
        sql_caption.AppendLine(",E_pass_Scan_rate_D1.[result] as [R-1] ")
        sql_caption.AppendLine(",E_pass_Scan_rate_D1.[achieve_rate] as [AR-1] ")
        sql_caption.AppendLine(",E_pass_Scan_rate_D1.KPI as [KPI-1] ")
        sql_caption.AppendLine(",E_pass_Scan_rate_D2.[date] as [D-2] ")
        sql_caption.AppendLine(",E_pass_Scan_rate_D2.[target] as [T-2] ")
        sql_caption.AppendLine(",E_pass_Scan_rate_D2.[result] as [R-2] ")
        sql_caption.AppendLine(",E_pass_Scan_rate_D2.[achieve_rate] as [AR-2] ")
        sql_caption.AppendLine(",E_pass_Scan_rate_D2.KPI as [KPI-2] ")
        sql_caption.AppendLine("from ")
        sql_caption.AppendLine("( ")
        sql_caption.AppendLine("	select top 2 ")
        sql_caption.AppendLine("	[date] as [date] ")
        sql_caption.AppendLine("	,TARGET_____560 as [target] ")
        sql_caption.AppendLine("	,RESULT_897 as [result] ")
        sql_caption.AppendLine("	,ACHIEVEMENT_RATE_____745 as [achieve_rate] ")
        sql_caption.AppendLine("	,'E-pass Scan rate' as 'KPI' ")
        sql_caption.AppendLine("	,ROW_NUMBER() OVER(ORDER BY [date] desc) AS Row# ")
        sql_caption.AppendLine("	from [UserModules].[user].[DAILY_KPI_REPORT_2017] ")
        sql_caption.AppendLine("	where cast([date] as date) < cast(@date as date) ")
        sql_caption.AppendLine("	and division = @division ")
        sql_caption.AppendLine("	order by [date] desc ")
        sql_caption.AppendLine(") as E_pass_Scan_rate_D1 ")
        sql_caption.AppendLine("left join ")
        sql_caption.AppendLine("( ")
        sql_caption.AppendLine("	select top 2 ")
        sql_caption.AppendLine("	[date] as [date] ")
        sql_caption.AppendLine("	,TARGET_____560 as [target] ")
        sql_caption.AppendLine("	,RESULT_897 as [result] ")
        sql_caption.AppendLine("	,ACHIEVEMENT_RATE_____745 as [achieve_rate] ")
        sql_caption.AppendLine("	,'E-pass Scan rate' as 'KPI' ")
        sql_caption.AppendLine("	,ROW_NUMBER() OVER(ORDER BY [date] desc) AS Row# ")
        sql_caption.AppendLine("	from [UserModules].[user].[DAILY_KPI_REPORT_2017] ")
        sql_caption.AppendLine("	where cast([date] as date) < cast(@date as date) ")
        sql_caption.AppendLine("	and division = @division ")
        sql_caption.AppendLine("	order by [date] desc ")
        sql_caption.AppendLine(") as E_pass_Scan_rate_D2 ")
        sql_caption.AppendLine("on E_pass_Scan_rate_D1.[kpi]=E_pass_Scan_rate_D2.[kpi] and E_pass_Scan_rate_D1.[date]<>E_pass_Scan_rate_D2.[date] ")
        sql_caption.AppendLine("order by E_pass_Scan_rate_D1.[date] desc ")
        sql_caption.AppendLine(") as K9 ")
        sql_caption.AppendLine(") as Average ")
        sql_caption.AppendLine("group by [D-1], [D-2] ")
        sql_caption.AppendLine(" ")


        dr = sqlf.GetRow(sql_caption.ToString)







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










        'Формируем график
        Summary.Series.FindByName("KPI_D-1").Points.DataBind(DV_linear, "KPI-1", "AR-1", "")
        Summary.Series.FindByName("KPI_D-2").Points.DataBind(DV_linear, "KPI-2", "AR-2", "")

        'Вид и стиль радара
        Summary.Series.FindByName("KPI_D-1")("RadarDrawingStyle") = "Line"
        Summary.Series.FindByName("KPI_D-1")("AreaDrawingStyle") = "Polygon"

        Summary.Series.FindByName("KPI_D-2")("RadarDrawingStyle") = "Line"
        Summary.Series.FindByName("KPI_D-2")("AreaDrawingStyle") = "Polygon"

        'Легенда
        Summary.Series.FindByName("KPI_D-1").LegendText = dr("D-1")
        Summary.Series.FindByName("KPI_D-2").LegendText = dr("D-2")


        'Разукрашивание точек
        Dim i As Integer = 0
        For i = 0 To Summary.Series.FindByName("KPI_D-1").Points.Count - 1
            If (Decimal.Parse(Summary.Series.FindByName("KPI_D-1").Points.Item(i).YValues(0).ToString) < 95) Then
                Summary.Series.FindByName("KPI_D-1").Points.Item(i).LabelForeColor = Drawing.Color.Red
            ElseIf Decimal.Parse(Summary.Series.FindByName("KPI_D-1").Points.Item(i).YValues(0).ToString) >= 95 And Decimal.Parse(Summary.Series.FindByName("KPI_D-2").Points.Item(i).YValues(0).ToString) < 95 Then
                Summary.Series.FindByName("KPI_D-1").Points.Item(i).LabelForeColor = Drawing.Color.Green
            End If
        Next


        'Заголовок графика
        'Summary.Titles.Item(0).Text = "SERK-" + division + " KPI Achievement rate " + dr("avg_d-1").ToString + "%" + ChrW(8595) + " (" + dr("D-2").ToString + " " + dr("avg_D-2").ToString + "%)"
        If Decimal.Parse(dr("avg_d-1")) < Decimal.Parse(dr("avg_d-2")) Then
            color = "red"
            trend = ChrW(8595)
        ElseIf Decimal.Parse(dr("avg_d-1")) > Decimal.Parse(dr("avg_d-2")) Then
            color = "green"
            trend = ChrW(8593)
        End If



        DV_linear.RowFilter = "[AR-1] < 95"
        Weaknesses.DataSource = DV_linear
        Weaknesses.DataBind()


    End Sub

    Private Sub Weaknesses_RowDataBound(sender As Object, e As GridViewRowEventArgs) Handles Weaknesses.RowDataBound
        If e.Row.RowType = DataControlRowType.DataRow Then
            If Decimal.Parse(e.Row.Cells(3).Text) > Decimal.Parse(e.Row.Cells(4).Text) Then
                e.Row.Cells(3).Text = e.Row.Cells(3).Text + "%" + ChrW(8593)
                e.Row.Cells(3).ForeColor = Drawing.Color.Blue
            ElseIf Decimal.Parse(e.Row.Cells(3).Text) < Decimal.Parse(e.Row.Cells(4).Text) Then
                e.Row.Cells(3).Text = e.Row.Cells(3).Text + "%" + ChrW(8595)
                e.Row.Cells(3).ForeColor = Drawing.Color.Red
            End If
        ElseIf e.Row.RowType = DataControlRowType.Header Then
            e.Row.Cells(3).BackColor = Drawing.Color.FromArgb(255, 204, 0)
        End If
    End Sub

    Private Sub Weaknesses_PreRender(sender As Object, e As EventArgs) Handles Weaknesses.PreRender
        Weaknesses.Columns(4).Visible = False
    End Sub
End Class