ModuleMaster/pc/sfrep.aspx.vb - main - ivan/ModMas

ModuleMaster/pc/sfrep.aspx.vb
1 172 строки69 KB
i.krivokon
Initial commit
13 мар 2026, 13:49
13 мар 2026, 13:4900d9fb6
Imports sharedClasses.dbFunctions
Imports sharedClasses
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Imports System.Collections
Imports System.Collections.Generic
Imports sharedClasses.Common
Imports System.Globalization

Partial Class PC_sfrep
    Inherits System.Web.UI.Page

    Dim sqlf As New SqlFunctions

    Private monthColumns As New Collection
    Private blankColumns As New Collection

    Private forExport As Boolean = False
    Dim prevModel As String = ""
    Dim f As Boolean = False

    Private models1 As New SortedSet(Of String)
    Private models2 As New SortedSet(Of String)
    Private models3 As New SortedSet(Of String)
    Private models4 As New SortedSet(Of String)
    Private models5 As New SortedSet(Of String)

    Private numberOfColumnsToShow As Integer = 8

    Private myCItrad As System.Globalization.CultureInfo = New System.Globalization.CultureInfo("en-US", True)

#Region "Page events"
    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        buildTypeList()
        buildDivisionList()
    End Sub

    Protected Sub Page_PreRender(sender As Object, e As EventArgs) Handles Me.PreRender
        PrepareReport(type.SelectedValue, division.SelectedValue)
        report.DataSource = prepareData(inchFilter.Text, modelFilter.Text, type.SelectedValue)

        total.DataSource = prepareTTLData(type.SelectedValue)
        total.DataBind()

        report.DataBind()
        'Mail sending
        If Not IsNothing(Request.QueryString("mode")) Then
            If Request.QueryString("mode") = "mail" Then
                SendEmail()
                Response.End()
            End If
        End If
        monthColumns = Nothing
        blankColumns = Nothing
    End Sub

    Protected Sub Page_SaveStateComplete(sender As Object, e As EventArgs) Handles Me.SaveStateComplete
        MakeTotalTable()
    End Sub
#End Region

#Region "Data preparation"
    Private Sub buildTypeList()
        Dim curValue As String = type.SelectedValue
        type.DataSource = sqlf.GetData("SELECT DISTINCT [type] as soptype FROM [UserModules].[user].[SOP_FLUCTUATION] ORDER BY [type] ")
        type.DataTextField = "soptype"
        type.DataValueField = "soptype"
        type.DataBind()
        Dim itm As ListItem = type.Items.FindByValue(curValue)
        If Not IsNothing(itm) Then
            itm.Selected = True
        End If
    End Sub

    Private Sub buildDivisionList()
        Dim curValue As String = division.SelectedValue
        division.DataSource = sqlf.GetData("SELECT DISTINCT case [division] when 'A1' then 'CTV' else 'MON' end as text, [division] as division FROM [UserModules].[user].[SOP_FLUCTUATION] where [division] <> 'Division' ORDER BY [division] ")
        division.DataTextField = "text"
        division.DataValueField = "division"
        division.DataBind()
        Dim itm As ListItem = division.Items.FindByValue(curValue)
        If Not IsNothing(itm) Then
            itm.Selected = True
        End If
    End Sub

    Private Sub PrepareReport(sopType As String, division As String)
        Dim sql As New StringBuilder
        sql.AppendLine("select row_number() over( order by [week]) as id, row_number() over (partition by model order by model, [site] desc) as num, ")
        sql.AppendLine("tbl.* ")
        sql.AppendLine("from ( ")
        sql.AppendLine("select ")
        sql.AppendLine("'201438' as [Week], ")
        sql.AppendLine("site, inch, model, ")
        sql.AppendLine("SUM(Diff0) as Diff0, ")
        sql.AppendLine("SUM(Diff1) as Diff1, ")
        sql.AppendLine("SUM(Diff2) as Diff2, ")
        sql.AppendLine("SUM(Diff3) as Diff3, ")
        sql.AppendLine("SUM(Diff4) as Diff4, ")
        sql.AppendLine("SUM(Diff5) as Diff5, ")
        sql.AppendLine("SUM(Diff6) as Diff6, ")
        sql.AppendLine("SUM(Diff7) as Diff7, ")
        sql.AppendLine("SUM(Diff8) as Diff8, ")
        sql.AppendLine("SUM(Diff9) as Diff9, ")
        sql.AppendLine("SUM(Diff10) as Diff10, ")
        sql.AppendLine("SUM(Diff11) as Diff11, ")
        sql.AppendLine("SUM(Diff12) as Diff12, ")
        sql.AppendLine("SUM(Diff13) as Diff13, ")
        sql.AppendLine("SUM(Diff14) as Diff14, ")
        sql.AppendLine("SUM(Diff15) as Diff15, ")
        sql.AppendLine("SUM(Diff16) as Diff16, ")
        sql.AppendLine("SUM(Diff17) as Diff17, ")
        sql.AppendLine("SUM(Diff18) as Diff18, ")
        sql.AppendLine("SUM(Diff19) as Diff19, ")
        sql.AppendLine("SUM(Diff20) as Diff20, ")
        sql.AppendLine("SUM(Diff21) as Diff21, ")
        sql.AppendLine("SUM(Diff22) as Diff22, ")
        sql.AppendLine("SUM(Diff0)+SUM(Diff1)+SUM(Diff2)+SUM(Diff3)+SUM(Diff4)+SUM(Diff5)+ ")
        sql.AppendLine("SUM(Diff6)+SUM(Diff7)+SUM(Diff8)+SUM(Diff9)+SUM(Diff10)+SUM(Diff11)+ ")
        sql.AppendLine("SUM(Diff12)+SUM(Diff13)+SUM(Diff14)+SUM(Diff15)+SUM(Diff16)+SUM(Diff17)+ ")
        sql.AppendLine("SUM(Diff18)+SUM(Diff19)+SUM(Diff20)+SUM(Diff21)+SUM(Diff22) as Total, max([type]) as [Type] ")
        sql.AppendLine("from ( ")
        sql.AppendLine("select ")
        sql.AppendLine("case SUBSTRING (t1.material, len(t1.material)-1, 2) ")
        sql.AppendLine("	when 'RU' then 'SERC diff' ")
        sql.AppendLine("	when 'UA' then 'SEUC diff' ")
        sql.AppendLine("	when 'KZ' then 'SECE diff' ")
        sql.AppendLine("	when 'CI' then 'EXPR diff' ")
        sql.AppendLine("	ELSE 'AAAA diff' ")
        sql.AppendLine(" end as [Site] , ")
        sql.AppendLine("SUBSTRING(t1.MATERIAL, 3,2) as Inch, ")
        sql.AppendLine("SUBSTRING (t1.material, 1, len(t1.material)-2) as model, ")
        sql.AppendLine("cast(CASE t1.CurWeek WHEN '' THEN 0 ELSE t1.CurWeek END as decimal)-cast(CASE t2.CurWeek WHEN '' THEN 0 ELSE t2.CurWeek END as decimal) as Diff0, ")
        sql.AppendLine("cast(CASE t1.CurWeekNo1 WHEN '' THEN 0 ELSE t1.CurWeekNo1 END as decimal)-cast(CASE t2.CurWeekNo1 WHEN '' THEN 0 ELSE t2.CurWeekNo1 END as decimal) as Diff1, ")
        sql.AppendLine("cast(CASE t1.CurWeekNo2 WHEN '' THEN 0 ELSE t1.CurWeekNo2 END as decimal)-cast(CASE t2.CurWeekNo2 WHEN '' THEN 0 ELSE t2.CurWeekNo2 END as decimal) as Diff2, ")
        sql.AppendLine("cast(CASE t1.CurWeekNo3 WHEN '' THEN 0 ELSE t1.CurWeekNo3 END as decimal)-cast(CASE t2.CurWeekNo3 WHEN '' THEN 0 ELSE t2.CurWeekNo3 END as decimal) as Diff3, ")
        sql.AppendLine("cast(CASE t1.CurWeekNo4 WHEN '' THEN 0 ELSE t1.CurWeekNo4 END as decimal)-cast(CASE t2.CurWeekNo4 WHEN '' THEN 0 ELSE t2.CurWeekNo4 END as decimal) as Diff4, ")
        sql.AppendLine("cast(CASE t1.CurWeekNo5 WHEN '' THEN 0 ELSE t1.CurWeekNo5 END as decimal)-cast(CASE t2.CurWeekNo5 WHEN '' THEN 0 ELSE t2.CurWeekNo5 END as decimal) as Diff5, ")
        sql.AppendLine("cast(CASE t1.CurWeekNo6 WHEN '' THEN 0 ELSE t1.CurWeekNo6 END as decimal)-cast(CASE t2.CurWeekNo6 WHEN '' THEN 0 ELSE t2.CurWeekNo6 END as decimal) as Diff6, ")
        sql.AppendLine("cast(CASE t1.CurWeekNo7 WHEN '' THEN 0 ELSE t1.CurWeekNo7 END as decimal)-cast(CASE t2.CurWeekNo7 WHEN '' THEN 0 ELSE t2.CurWeekNo7 END as decimal) as Diff7, ")
        sql.AppendLine("cast(CASE t1.CurWeekNo8 WHEN '' THEN 0 ELSE t1.CurWeekNo8 END as decimal)-cast(CASE t2.CurWeekNo8 WHEN '' THEN 0 ELSE t2.CurWeekNo8 END as decimal) as Diff8, ")
        sql.AppendLine("cast(CASE t1.CurWeekNo9 WHEN '' THEN 0 ELSE t1.CurWeekNo9 END as decimal)-cast(CASE t2.CurWeekNo9 WHEN '' THEN 0 ELSE t2.CurWeekNo9 END as decimal) as Diff9, ")
        sql.AppendLine("cast(CASE t1.CurWeekNo10 WHEN '' THEN 0 ELSE t1.CurWeekNo10 END as decimal)-cast(CASE t2.CurWeekNo10 WHEN '' THEN 0 ELSE t2.CurWeekNo10 END as decimal) as Diff10, ")
        sql.AppendLine("cast(CASE t1.CurWeekNo11 WHEN '' THEN 0 ELSE t1.CurWeekNo11 END as decimal)-cast(CASE t2.CurWeekNo11 WHEN '' THEN 0 ELSE t2.CurWeekNo11 END as decimal) as Diff11, ")
        sql.AppendLine("cast(CASE t1.CurWeekNo12 WHEN '' THEN 0 ELSE t1.CurWeekNo12 END as decimal)-cast(CASE t2.CurWeekNo12 WHEN '' THEN 0 ELSE t2.CurWeekNo12 END as decimal) as Diff12, ")
        sql.AppendLine("cast(CASE t1.CurWeekNo13 WHEN '' THEN 0 ELSE t1.CurWeekNo13 END as decimal)-cast(CASE t2.CurWeekNo13 WHEN '' THEN 0 ELSE t2.CurWeekNo13 END as decimal) as Diff13, ")
        sql.AppendLine("cast(CASE t1.CurWeekNo14 WHEN '' THEN 0 ELSE t1.CurWeekNo14 END as decimal)-cast(CASE t2.CurWeekNo14 WHEN '' THEN 0 ELSE t2.CurWeekNo14 END as decimal) as Diff14, ")
        sql.AppendLine("cast(CASE t1.CurWeekNo15 WHEN '' THEN 0 ELSE t1.CurWeekNo15 END as decimal)-cast(CASE t2.CurWeekNo15 WHEN '' THEN 0 ELSE t2.CurWeekNo15 END as decimal) as Diff15, ")
        sql.AppendLine("cast(CASE t1.CurWeekNo16 WHEN '' THEN 0 ELSE t1.CurWeekNo16 END as decimal)-cast(CASE t2.CurWeekNo16 WHEN '' THEN 0 ELSE t2.CurWeekNo16 END as decimal) as Diff16, ")
        sql.AppendLine("cast(CASE t1.CurWeekNo17 WHEN '' THEN 0 ELSE t1.CurWeekNo17 END as decimal)-cast(CASE t2.CurWeekNo17 WHEN '' THEN 0 ELSE t2.CurWeekNo17 END as decimal) as Diff17, ")
        sql.AppendLine("cast(CASE t1.CurWeekNo18 WHEN '' THEN 0 ELSE t1.CurWeekNo18 END as decimal)-cast(CASE t2.CurWeekNo18 WHEN '' THEN 0 ELSE t2.CurWeekNo18 END as decimal) as Diff18, ")
        sql.AppendLine("cast(CASE t1.CurWeekNo19 WHEN '' THEN 0 ELSE t1.CurWeekNo19 END as decimal)-cast(CASE t2.CurWeekNo19 WHEN '' THEN 0 ELSE t2.CurWeekNo19 END as decimal) as Diff19, ")
        sql.AppendLine("cast(CASE t1.CurWeekNo20 WHEN '' THEN 0 ELSE t1.CurWeekNo20 END as decimal)-cast(CASE t2.CurWeekNo20 WHEN '' THEN 0 ELSE t2.CurWeekNo20 END as decimal) as Diff20, ")
        sql.AppendLine("cast(CASE t1.CurWeekNo21 WHEN '' THEN 0 ELSE t1.CurWeekNo21 END as decimal)-cast(CASE t2.CurWeekNo21 WHEN '' THEN 0 ELSE t2.CurWeekNo21 END as decimal) as Diff21, ")
        sql.AppendLine("cast(CASE t1.CurWeekNo22 WHEN '' THEN 0 ELSE t1.CurWeekNo22 END as decimal)-cast(CASE t2.CurWeekNo22 WHEN '' THEN 0 ELSE t2.CurWeekNo22 END as decimal) as Diff22, t1.[type] ")
        sql.AppendLine("from ")
        sql.AppendLine("usermodules.[user].sop_fluctuation t1 ")
        sql.AppendLine("left join usermodules.[user].sop_fluctuation t2 ")
        sql.AppendLine("on t1.id = t2.id+1 ")
        sql.AppendLine("and t1.DIVISION <> 'Division' and t2.DIVISION <> 'Division' ")
        sql.AppendLine("and SUBSTRING (t1.material, 1, len(t1.material)-2) = SUBSTRING (t2.material, 1, len(t2.material)-2) and t1.[type] = t2.[type] ")
        sql.AppendLine("where t2.id is not null and t1.[type] = '" & sopType & "' and t1.[division] = '" & division & "'")
        sql.AppendLine(") tt ")
        sql.AppendLine("group by site, inch, model ")
        sql.AppendLine("union all ")
        sql.AppendLine("select ")
        sql.AppendLine("'201438' as [Week], ")
        sql.AppendLine("plan_id as [site], ")
        sql.AppendLine("SUBSTRING(t1.MATERIAL, 3,2) as Inch, ")
        sql.AppendLine("SUBSTRING (t1.material, 1, len(t1.material)-2) as model, ")
        sql.AppendLine("SUM(cast(CASE t1.CurWeek WHEN '' THEN 0 ELSE t1.CurWeek END as decimal)), ")
        sql.AppendLine("SUM(cast(CASE t1.CurWeekNo1 WHEN '' THEN 0 ELSE t1.CurWeekNo1 END as decimal)), ")
        sql.AppendLine("SUM(cast(CASE t1.CurWeekNo2 WHEN '' THEN 0 ELSE t1.CurWeekNo2 END as decimal)), ")
        sql.AppendLine("SUM(cast(CASE t1.CurWeekNo3 WHEN '' THEN 0 ELSE t1.CurWeekNo3 END as decimal)), ")
        sql.AppendLine("SUM(cast(CASE t1.CurWeekNo4 WHEN '' THEN 0 ELSE t1.CurWeekNo4 END as decimal)), ")
        sql.AppendLine("SUM(cast(CASE t1.CurWeekNo5 WHEN '' THEN 0 ELSE t1.CurWeekNo5 END as decimal)), ")
        sql.AppendLine("SUM(cast(CASE t1.CurWeekNo6 WHEN '' THEN 0 ELSE t1.CurWeekNo6 END as decimal)), ")
        sql.AppendLine("SUM(cast(CASE t1.CurWeekNo7 WHEN '' THEN 0 ELSE t1.CurWeekNo7 END as decimal)), ")
        sql.AppendLine("SUM(cast(CASE t1.CurWeekNo8 WHEN '' THEN 0 ELSE t1.CurWeekNo8 END as decimal)), ")
        sql.AppendLine("SUM(cast(CASE t1.CurWeekNo9 WHEN '' THEN 0 ELSE t1.CurWeekNo9 END as decimal)), ")
        sql.AppendLine("SUM(cast(CASE t1.CurWeekNo10 WHEN '' THEN 0 ELSE t1.CurWeekNo10 END as decimal)), ")
        sql.AppendLine("SUM(cast(CASE t1.CurWeekNo11 WHEN '' THEN 0 ELSE t1.CurWeekNo11 END as decimal)), ")
        sql.AppendLine("SUM(cast(CASE t1.CurWeekNo12 WHEN '' THEN 0 ELSE t1.CurWeekNo12 END as decimal)), ")
        sql.AppendLine("SUM(cast(CASE t1.CurWeekNo13 WHEN '' THEN 0 ELSE t1.CurWeekNo13 END as decimal)), ")
        sql.AppendLine("SUM(cast(CASE t1.CurWeekNo14 WHEN '' THEN 0 ELSE t1.CurWeekNo14 END as decimal)), ")
        sql.AppendLine("SUM(cast(CASE t1.CurWeekNo15 WHEN '' THEN 0 ELSE t1.CurWeekNo15 END as decimal)), ")
        sql.AppendLine("SUM(cast(CASE t1.CurWeekNo16 WHEN '' THEN 0 ELSE t1.CurWeekNo16 END as decimal)), ")
        sql.AppendLine("SUM(cast(CASE t1.CurWeekNo17 WHEN '' THEN 0 ELSE t1.CurWeekNo17 END as decimal)), ")
        sql.AppendLine("SUM(cast(CASE t1.CurWeekNo18 WHEN '' THEN 0 ELSE t1.CurWeekNo18 END as decimal)), ")
        sql.AppendLine("SUM(cast(CASE t1.CurWeekNo19 WHEN '' THEN 0 ELSE t1.CurWeekNo19 END as decimal)), ")
        sql.AppendLine("SUM(cast(CASE t1.CurWeekNo20 WHEN '' THEN 0 ELSE t1.CurWeekNo20 END as decimal)), ")
        sql.AppendLine("SUM(cast(CASE t1.CurWeekNo21 WHEN '' THEN 0 ELSE t1.CurWeekNo21 END as decimal)), ")
        sql.AppendLine("SUM(cast(CASE t1.CurWeekNo22 WHEN '' THEN 0 ELSE t1.CurWeekNo22 END as decimal)), ")
        sql.AppendLine("SUM(cast(CASE t1.CurWeek WHEN '' THEN 0 ELSE t1.CurWeek END as decimal))+ ")
        sql.AppendLine("SUM(cast(CASE t1.CurWeekNo1 WHEN '' THEN 0 ELSE t1.CurWeekNo1 END as decimal))+ ")
        sql.AppendLine("SUM(cast(CASE t1.CurWeekNo2 WHEN '' THEN 0 ELSE t1.CurWeekNo2 END as decimal))+ ")
        sql.AppendLine("SUM(cast(CASE t1.CurWeekNo3 WHEN '' THEN 0 ELSE t1.CurWeekNo3 END as decimal))+ ")
        sql.AppendLine("SUM(cast(CASE t1.CurWeekNo4 WHEN '' THEN 0 ELSE t1.CurWeekNo4 END as decimal))+ ")
        sql.AppendLine("SUM(cast(CASE t1.CurWeekNo5 WHEN '' THEN 0 ELSE t1.CurWeekNo5 END as decimal))+ ")
        sql.AppendLine("SUM(cast(CASE t1.CurWeekNo6 WHEN '' THEN 0 ELSE t1.CurWeekNo6 END as decimal))+ ")
        sql.AppendLine("SUM(cast(CASE t1.CurWeekNo7 WHEN '' THEN 0 ELSE t1.CurWeekNo7 END as decimal))+ ")
        sql.AppendLine("SUM(cast(CASE t1.CurWeekNo8 WHEN '' THEN 0 ELSE t1.CurWeekNo8 END as decimal))+ ")
        sql.AppendLine("SUM(cast(CASE t1.CurWeekNo9 WHEN '' THEN 0 ELSE t1.CurWeekNo9 END as decimal))+ ")
        sql.AppendLine("SUM(cast(CASE t1.CurWeekNo10 WHEN '' THEN 0 ELSE t1.CurWeekNo10 END as decimal))+ ")
        sql.AppendLine("SUM(cast(CASE t1.CurWeekNo11 WHEN '' THEN 0 ELSE t1.CurWeekNo11 END as decimal))+ ")
        sql.AppendLine("SUM(cast(CASE t1.CurWeekNo12 WHEN '' THEN 0 ELSE t1.CurWeekNo12 END as decimal))+ ")
        sql.AppendLine("SUM(cast(CASE t1.CurWeekNo13 WHEN '' THEN 0 ELSE t1.CurWeekNo13 END as decimal))+ ")
        sql.AppendLine("SUM(cast(CASE t1.CurWeekNo14 WHEN '' THEN 0 ELSE t1.CurWeekNo14 END as decimal))+ ")
        sql.AppendLine("SUM(cast(CASE t1.CurWeekNo15 WHEN '' THEN 0 ELSE t1.CurWeekNo15 END as decimal))+ ")
        sql.AppendLine("SUM(cast(CASE t1.CurWeekNo16 WHEN '' THEN 0 ELSE t1.CurWeekNo16 END as decimal))+ ")
        sql.AppendLine("SUM(cast(CASE t1.CurWeekNo17 WHEN '' THEN 0 ELSE t1.CurWeekNo17 END as decimal))+ ")
        sql.AppendLine("SUM(cast(CASE t1.CurWeekNo18 WHEN '' THEN 0 ELSE t1.CurWeekNo18 END as decimal))+ ")
        sql.AppendLine("SUM(cast(CASE t1.CurWeekNo19 WHEN '' THEN 0 ELSE t1.CurWeekNo19 END as decimal))+ ")
        sql.AppendLine("SUM(cast(CASE t1.CurWeekNo20 WHEN '' THEN 0 ELSE t1.CurWeekNo20 END as decimal))+ ")
        sql.AppendLine("SUM(cast(CASE t1.CurWeekNo21 WHEN '' THEN 0 ELSE t1.CurWeekNo21 END as decimal))+ ")
        sql.AppendLine("SUM(cast(CASE t1.CurWeekNo22 WHEN '' THEN 0 ELSE t1.CurWeekNo22 END as decimal)), MAX([type]) ")
        sql.AppendLine("from ")
        sql.AppendLine("usermodules.[user].sop_fluctuation t1 WHERE t1.[type]='" & sopType & "' and t1.[division] = '" & division & "'")
        sql.AppendLine("group by ")
        sql.AppendLine("plan_id, ")
        sql.AppendLine("SUBSTRING(t1.MATERIAL, 3,2), ")
        sql.AppendLine("SUBSTRING (t1.material, 1, len(t1.material)-2) ")
        sql.AppendLine(") tbl ")
        sql.AppendLine("order by tbl.model, tbl.site desc ")

        Dim dt As DataTable = sqlf.GetData(sql.ToString)
        If Not IsNothing(dt) Then
            sqlf.NonQuery("TRUNCATE TABLE UserModules.[user].[SOP_FLUCTUATION_REPORT] ")
            'sql.NonQuery("DELETE FROM UserModules.[user].[SOP_FLUCTUATION_REPORT] WHERE [type] =" & sopType & "")
            sql.Clear()
            sql.Append("INSERT INTO UserModules.[user].[SOP_FLUCTUATION_REPORT] ")
            sql.Append(" ([NUM] ")
            sql.Append(" ,[REPORT_WEEK] ")
            sql.Append(" ,[SITE] ")
            sql.Append(" ,[INCH] ")
            sql.Append(" ,[MODEL] ")
            sql.Append(" ,[DIFF0] ")
            sql.Append(" ,[DIFF1] ")
            sql.Append(" ,[DIFF2] ")
            sql.Append(" ,[DIFF3] ")
            sql.Append(" ,[DIFF4] ")
            sql.Append(" ,[DIFF5] ")
            sql.Append(" ,[DIFF6] ")
            sql.Append(" ,[DIFF7] ")
            sql.Append(" ,[DIFF8] ")
            sql.Append(" ,[DIFF9] ")
            sql.Append(" ,[DIFF10] ")
            sql.Append(" ,[DIFF11] ")
            sql.Append(" ,[DIFF12] ")
            sql.Append(" ,[DIFF13] ")
            sql.Append(" ,[DIFF14] ")
            sql.Append(" ,[DIFF15] ")
            sql.Append(" ,[DIFF16] ")
            sql.Append(" ,[DIFF17] ")
            sql.Append(" ,[DIFF18] ")
            sql.Append(" ,[DIFF19] ")
            sql.Append(" ,[DIFF20] ")
            sql.Append(" ,[DIFF21] ")
            sql.Append(" ,[DIFF22], [Total]) ")
            sql.Append(" VALUES ")
            sql.Append(" (@NUM  ")
            sql.Append(" ,@REPORT_WEEK  ")
            sql.Append(" ,@SITE  ")
            sql.Append(" ,@INCH  ")
            sql.Append(" ,@MODEL  ")
            sql.Append(" ,@DIFF0  ")
            sql.Append(" ,@DIFF1  ")
            sql.Append(" ,@DIFF2  ")
            sql.Append(" ,@DIFF3  ")
            sql.Append(" ,@DIFF4  ")
            sql.Append(" ,@DIFF5  ")
            sql.Append(" ,@DIFF6  ")
            sql.Append(" ,@DIFF7  ")
            sql.Append(" ,@DIFF8  ")
            sql.Append(" ,@DIFF9  ")
            sql.Append(" ,@DIFF10  ")
            sql.Append(" ,@DIFF11  ")
            sql.Append(" ,@DIFF12  ")
            sql.Append(" ,@DIFF13  ")
            sql.Append(" ,@DIFF14  ")
            sql.Append(" ,@DIFF15  ")
            sql.Append(" ,@DIFF16  ")
            sql.Append(" ,@DIFF17  ")
            sql.Append(" ,@DIFF18  ")
            sql.Append(" ,@DIFF19  ")
            sql.Append(" ,@DIFF20  ")
            sql.Append(" ,@DIFF21  ")
            sql.Append(" ,@DIFF22, @Total ) ")
            Dim com As New SqlCommand
            com.CommandText = sql.ToString
            Dim prevNum As Integer = 1
            Dim prevModel As String = ""
            Dim prevRow As DataRow
            Dim prevRow1 As DataRow
            For Each dr As DataRow In dt.Rows
                If dr("num") < prevNum Then
                    prevRow = dt.Select("model='" & prevModel & "' AND num=" & prevNum)(0)
                    prevRow1 = dt.Select("model='" & prevModel & "' AND num=" & (prevNum - 1))(0)
                    'Making total difference row
                    With com.Parameters
                        .Clear()
                        .AddWithValue("num", prevNum + 1)
                        .AddWithValue("REPORT_WEEK", dr("week"))
                        .AddWithValue("site", "Total Diff")
                        .AddWithValue("inch", prevRow("inch"))
                        .AddWithValue("model", prevModel)
                        .AddWithValue("Diff0", prevRow1("Diff0") - prevRow("Diff0"))
                        .AddWithValue("Diff1", prevRow1("Diff1") - prevRow("Diff1"))
                        .AddWithValue("Diff2", prevRow1("Diff2") - prevRow("Diff2"))
                        .AddWithValue("Diff3", prevRow1("Diff3") - prevRow("Diff3"))
                        .AddWithValue("Diff4", prevRow1("Diff4") - prevRow("Diff4"))
                        .AddWithValue("Diff5", prevRow1("Diff5") - prevRow("Diff5"))
                        .AddWithValue("Diff6", prevRow1("Diff6") - prevRow("Diff6"))
                        .AddWithValue("Diff7", prevRow1("Diff7") - prevRow("Diff7"))
                        .AddWithValue("Diff8", prevRow1("Diff8") - prevRow("Diff8"))
                        .AddWithValue("Diff9", prevRow1("Diff9") - prevRow("Diff9"))
                        .AddWithValue("Diff10", prevRow1("Diff10") - prevRow("Diff10"))
                        .AddWithValue("Diff11", prevRow1("Diff11") - prevRow("Diff11"))
                        .AddWithValue("Diff12", prevRow1("Diff12") - prevRow("Diff12"))
                        .AddWithValue("Diff13", prevRow1("Diff13") - prevRow("Diff13"))
                        .AddWithValue("Diff14", prevRow1("Diff14") - prevRow("Diff14"))
                        .AddWithValue("Diff15", prevRow1("Diff15") - prevRow("Diff15"))
                        .AddWithValue("Diff16", prevRow1("Diff16") - prevRow("Diff16"))
                        .AddWithValue("Diff17", prevRow1("Diff17") - prevRow("Diff17"))
                        .AddWithValue("Diff18", prevRow1("Diff18") - prevRow("Diff18"))
                        .AddWithValue("Diff19", prevRow1("Diff19") - prevRow("Diff19"))
                        .AddWithValue("Diff20", prevRow1("Diff20") - prevRow("Diff20"))
                        .AddWithValue("Diff21", prevRow1("Diff21") - prevRow("Diff21"))
                        .AddWithValue("Diff22", prevRow1("Diff22") - prevRow("Diff22"))
                        .AddWithValue("Total", prevRow1("Total") - prevRow("Total"))
                    End With
                    sqlf.NonQuery(com)
                    'Making RATIO row
                    With com.Parameters
                        .Clear()
                        .AddWithValue("num", prevNum + 2)
                        .AddWithValue("REPORT_WEEK", dr("week"))
                        .AddWithValue("site", "Rate,%")
                        .AddWithValue("inch", prevRow("inch"))
                        .AddWithValue("model", prevModel)
                        .AddWithValue("Diff0", Math.Round(divide(prevRow1("Diff0") - prevRow("Diff0"), prevRow("Diff0"), -200) * 100))
                        .AddWithValue("Diff1", Math.Round(divide(prevRow1("Diff1") - prevRow("Diff1"), prevRow("Diff1"), -200) * 100))
                        .AddWithValue("Diff2", Math.Round(divide(prevRow1("Diff2") - prevRow("Diff2"), prevRow("Diff2"), -200) * 100))
                        .AddWithValue("Diff3", Math.Round(divide(prevRow1("Diff3") - prevRow("Diff3"), prevRow("Diff3"), -200) * 100))
                        .AddWithValue("Diff4", Math.Round(divide(prevRow1("Diff4") - prevRow("Diff4"), prevRow("Diff4"), -200) * 100))
                        .AddWithValue("Diff5", Math.Round(divide(prevRow1("Diff5") - prevRow("Diff5"), prevRow("Diff5"), -200) * 100))
                        .AddWithValue("Diff6", Math.Round(divide(prevRow1("Diff6") - prevRow("Diff6"), prevRow("Diff6"), -200) * 100))
                        .AddWithValue("Diff7", Math.Round(divide(prevRow1("Diff7") - prevRow("Diff7"), prevRow("Diff7"), -200) * 100))
                        .AddWithValue("Diff8", Math.Round(divide(prevRow1("Diff8") - prevRow("Diff8"), prevRow("Diff8"), -200) * 100))
                        .AddWithValue("Diff9", Math.Round(divide(prevRow1("Diff9") - prevRow("Diff9"), prevRow("Diff9"), -200) * 100))
                        .AddWithValue("Diff10", Math.Round(divide(prevRow1("Diff10") - prevRow("Diff10"), prevRow("Diff10"), -200) * 100))
                        .AddWithValue("Diff11", Math.Round(divide(prevRow1("Diff11") - prevRow("Diff11"), prevRow("Diff11"), -200) * 100))
                        .AddWithValue("Diff12", Math.Round(divide(prevRow1("Diff12") - prevRow("Diff12"), prevRow("Diff12"), -200) * 100))
                        .AddWithValue("Diff13", Math.Round(divide(prevRow1("Diff13") - prevRow("Diff13"), prevRow("Diff13"), -200) * 100))
                        .AddWithValue("Diff14", Math.Round(divide(prevRow1("Diff14") - prevRow("Diff14"), prevRow("Diff14"), -200) * 100))
                        .AddWithValue("Diff15", Math.Round(divide(prevRow1("Diff15") - prevRow("Diff15"), prevRow("Diff15"), -200) * 100))
                        .AddWithValue("Diff16", Math.Round(divide(prevRow1("Diff16") - prevRow("Diff16"), prevRow("Diff16"), -200) * 100))
                        .AddWithValue("Diff17", Math.Round(divide(prevRow1("Diff17") - prevRow("Diff17"), prevRow("Diff17"), -200) * 100))
                        .AddWithValue("Diff18", Math.Round(divide(prevRow1("Diff18") - prevRow("Diff18"), prevRow("Diff18"), -200) * 100))
                        .AddWithValue("Diff19", Math.Round(divide(prevRow1("Diff19") - prevRow("Diff19"), prevRow("Diff19"), -200) * 100))
                        .AddWithValue("Diff20", Math.Round(divide(prevRow1("Diff20") - prevRow("Diff20"), prevRow("Diff20"), -200) * 100))
                        .AddWithValue("Diff21", Math.Round(divide(prevRow1("Diff21") - prevRow("Diff21"), prevRow("Diff21"), -200) * 100))
                        .AddWithValue("Diff22", Math.Round(divide(prevRow1("Diff22") - prevRow("Diff22"), prevRow("Diff22"), -200) * 100))
                        .AddWithValue("Total", Math.Round(divide(prevRow1("Total") - prevRow("Total"), prevRow("Total"), -200) * 100))
                    End With
                    sqlf.NonQuery(com)
                End If
                With com.Parameters
                    .Clear()
                    .AddWithValue("num", dr("num"))
                    .AddWithValue("REPORT_WEEK", dr("week"))
                    .AddWithValue("site", dr("site"))
                    .AddWithValue("inch", dr("inch"))
                    .AddWithValue("model", dr("model"))
                    .AddWithValue("Diff0", dr("Diff0"))
                    .AddWithValue("Diff1", dr("Diff1"))
                    .AddWithValue("Diff2", dr("Diff2"))
                    .AddWithValue("Diff3", dr("Diff3"))
                    .AddWithValue("Diff4", dr("Diff4"))
                    .AddWithValue("Diff5", dr("Diff5"))
                    .AddWithValue("Diff6", dr("Diff6"))
                    .AddWithValue("Diff7", dr("Diff7"))
                    .AddWithValue("Diff8", dr("Diff8"))
                    .AddWithValue("Diff9", dr("Diff9"))
                    .AddWithValue("Diff10", dr("Diff10"))
                    .AddWithValue("Diff11", dr("Diff11"))
                    .AddWithValue("Diff12", dr("Diff12"))
                    .AddWithValue("Diff13", dr("Diff13"))
                    .AddWithValue("Diff14", dr("Diff14"))
                    .AddWithValue("Diff15", dr("Diff15"))
                    .AddWithValue("Diff16", dr("Diff16"))
                    .AddWithValue("Diff17", dr("Diff17"))
                    .AddWithValue("Diff18", dr("Diff18"))
                    .AddWithValue("Diff19", dr("Diff19"))
                    .AddWithValue("Diff20", dr("Diff20"))
                    .AddWithValue("Diff21", dr("Diff21"))
                    .AddWithValue("Diff22", dr("Diff22"))
                    .AddWithValue("Total", dr("Total"))
                End With
                sqlf.NonQuery(com)
                prevNum = dr("num")
                prevModel = dr("model")
            Next

            prevRow = dt.Select("model='" & prevModel & "' AND num=" & prevNum)(0)
            prevRow1 = dt.Select("model='" & prevModel & "' AND num=" & (prevNum - 1))(0)
            'Making total difference row
            With com.Parameters
                .Clear()
                .AddWithValue("num", prevNum + 1)
                .AddWithValue("REPORT_WEEK", prevRow("week"))
                .AddWithValue("site", "Total Diff")
                .AddWithValue("inch", prevRow("inch"))
                .AddWithValue("model", prevModel)
                .AddWithValue("Diff0", prevRow1("Diff0") - prevRow("Diff0"))
                .AddWithValue("Diff1", prevRow1("Diff1") - prevRow("Diff1"))
                .AddWithValue("Diff2", prevRow1("Diff2") - prevRow("Diff2"))
                .AddWithValue("Diff3", prevRow1("Diff3") - prevRow("Diff3"))
                .AddWithValue("Diff4", prevRow1("Diff4") - prevRow("Diff4"))
                .AddWithValue("Diff5", prevRow1("Diff5") - prevRow("Diff5"))
                .AddWithValue("Diff6", prevRow1("Diff6") - prevRow("Diff6"))
                .AddWithValue("Diff7", prevRow1("Diff7") - prevRow("Diff7"))
                .AddWithValue("Diff8", prevRow1("Diff8") - prevRow("Diff8"))
                .AddWithValue("Diff9", prevRow1("Diff9") - prevRow("Diff9"))
                .AddWithValue("Diff10", prevRow1("Diff10") - prevRow("Diff10"))
                .AddWithValue("Diff11", prevRow1("Diff11") - prevRow("Diff11"))
                .AddWithValue("Diff12", prevRow1("Diff12") - prevRow("Diff12"))
                .AddWithValue("Diff13", prevRow1("Diff13") - prevRow("Diff13"))
                .AddWithValue("Diff14", prevRow1("Diff14") - prevRow("Diff14"))
                .AddWithValue("Diff15", prevRow1("Diff15") - prevRow("Diff15"))
                .AddWithValue("Diff16", prevRow1("Diff16") - prevRow("Diff16"))
                .AddWithValue("Diff17", prevRow1("Diff17") - prevRow("Diff17"))
                .AddWithValue("Diff18", prevRow1("Diff18") - prevRow("Diff18"))
                .AddWithValue("Diff19", prevRow1("Diff19") - prevRow("Diff19"))
                .AddWithValue("Diff20", prevRow1("Diff20") - prevRow("Diff20"))
                .AddWithValue("Diff21", prevRow1("Diff21") - prevRow("Diff21"))
                .AddWithValue("Diff22", prevRow1("Diff22") - prevRow("Diff22"))
                .AddWithValue("Total", prevRow1("Total") - prevRow("Total"))
            End With
            sqlf.NonQuery(com)
            'Making RATIO row
            With com.Parameters
                .Clear()
                .AddWithValue("num", prevNum + 2)
                .AddWithValue("REPORT_WEEK", prevRow("week"))
                .AddWithValue("site", "Rate,%")
                .AddWithValue("inch", prevRow("inch"))
                .AddWithValue("model", prevModel)
                .AddWithValue("Diff0", Math.Round(divide(prevRow1("Diff0") - prevRow("Diff0"), prevRow("Diff0"), -200) * 100))
                .AddWithValue("Diff1", Math.Round(divide(prevRow1("Diff1") - prevRow("Diff1"), prevRow("Diff1"), -200) * 100))
                .AddWithValue("Diff2", Math.Round(divide(prevRow1("Diff2") - prevRow("Diff2"), prevRow("Diff2"), -200) * 100))
                .AddWithValue("Diff3", Math.Round(divide(prevRow1("Diff3") - prevRow("Diff3"), prevRow("Diff3"), -200) * 100))
                .AddWithValue("Diff4", Math.Round(divide(prevRow1("Diff4") - prevRow("Diff4"), prevRow("Diff4"), -200) * 100))
                .AddWithValue("Diff5", Math.Round(divide(prevRow1("Diff5") - prevRow("Diff5"), prevRow("Diff5"), -200) * 100))
                .AddWithValue("Diff6", Math.Round(divide(prevRow1("Diff6") - prevRow("Diff6"), prevRow("Diff6"), -200) * 100))
                .AddWithValue("Diff7", Math.Round(divide(prevRow1("Diff7") - prevRow("Diff7"), prevRow("Diff7"), -200) * 100))
                .AddWithValue("Diff8", Math.Round(divide(prevRow1("Diff8") - prevRow("Diff8"), prevRow("Diff8"), -200) * 100))
                .AddWithValue("Diff9", Math.Round(divide(prevRow1("Diff9") - prevRow("Diff9"), prevRow("Diff9"), -200) * 100))
                .AddWithValue("Diff10", Math.Round(divide(prevRow1("Diff10") - prevRow("Diff10"), prevRow("Diff10"), -200) * 100))
                .AddWithValue("Diff11", Math.Round(divide(prevRow1("Diff11") - prevRow("Diff11"), prevRow("Diff11"), -200) * 100))
                .AddWithValue("Diff12", Math.Round(divide(prevRow1("Diff12") - prevRow("Diff12"), prevRow("Diff12"), -200) * 100))
                .AddWithValue("Diff13", Math.Round(divide(prevRow1("Diff13") - prevRow("Diff13"), prevRow("Diff13"), -200) * 100))
                .AddWithValue("Diff14", Math.Round(divide(prevRow1("Diff14") - prevRow("Diff14"), prevRow("Diff14"), -200) * 100))
                .AddWithValue("Diff15", Math.Round(divide(prevRow1("Diff15") - prevRow("Diff15"), prevRow("Diff15"), -200) * 100))
                .AddWithValue("Diff16", Math.Round(divide(prevRow1("Diff16") - prevRow("Diff16"), prevRow("Diff16"), -200) * 100))
                .AddWithValue("Diff17", Math.Round(divide(prevRow1("Diff17") - prevRow("Diff17"), prevRow("Diff17"), -200) * 100))
                .AddWithValue("Diff18", Math.Round(divide(prevRow1("Diff18") - prevRow("Diff18"), prevRow("Diff18"), -200) * 100))
                .AddWithValue("Diff19", Math.Round(divide(prevRow1("Diff19") - prevRow("Diff19"), prevRow("Diff19"), -200) * 100))
                .AddWithValue("Diff20", Math.Round(divide(prevRow1("Diff20") - prevRow("Diff20"), prevRow("Diff20"), -200) * 100))
                .AddWithValue("Diff21", Math.Round(divide(prevRow1("Diff21") - prevRow("Diff21"), prevRow("Diff21"), -200) * 100))
                .AddWithValue("Diff22", Math.Round(divide(prevRow1("Diff22") - prevRow("Diff22"), prevRow("Diff22"), -200) * 100))
                .AddWithValue("Total", Math.Round(divide(prevRow1("Total") - prevRow("Total"), prevRow("Total"), -200) * 100))

            End With
            sqlf.NonQuery(com)
        End If
    End Sub

    Private Function prepareData(inch As String, model As String, type As String) As DataTable
        Dim sql As String = "SELECT [SITE], [INCH], [MODEL], [DIFF0], [DIFF1], [DIFF2], [DIFF3], "
        If numberOfColumnsToShow > 4 Then
            sql &= "[DIFF4], [DIFF5], [DIFF6], [DIFF7], "
        End If
        If numberOfColumnsToShow > 8 Then
            sql &= "[DIFF8], [DIFF9], [DIFF10], [DIFF11], "
        End If
        If numberOfColumnsToShow > 12 Then
            sql &= "[DIFF12], [DIFF13], [DIFF14], [DIFF15], "
        End If
        If numberOfColumnsToShow > 16 Then
            sql &= "[DIFF16], [DIFF17], [DIFF18], [DIFF19], "
        End If
        If numberOfColumnsToShow > 20 Then
            sql &= "[DIFF20], [DIFF21], [DIFF22], "
        End If
        sql &= "[Total] FROM [UserModules].[user].[SOP_FLUCTUATION_REPORT] WHERE INCH <> 'te'"

        Dim weekList As DataTable = sqlf.GetData("SELECT [CURWEEK] AS DIFF0, [CURWEEKNo1] AS DIFF1, [CURWEEKNo2] AS DIFF2, " & _
                                                 "[CURWEEKNo3] AS DIFF3, [CURWEEKNo4] AS DIFF4, [CURWEEKNo5] AS DIFF5, [CURWEEKNo6] AS DIFF6, " & _
                                                 "[CURWEEKNo7] AS DIFF7, [CURWEEKNo8] AS DIFF8, [CURWEEKNo9] AS DIFF9, [CURWEEKNo10] AS DIFF10, " & _
                                                 "[CURWEEKNo11] AS DIFF11, [CURWEEKNo12] AS DIFF12, [CURWEEKNo13] AS DIFF13, [CURWEEKNo14] AS DIFF14, " & _
                                                 "[CURWEEKNo15] AS DIFF15, [CURWEEKNo16] AS DIFF16, [CURWEEKNo17] AS DIFF17, [CURWEEKNo18] AS DIFF18, " & _
                                                 "[CURWEEKNo19] AS DIFF19, [CURWEEKNo20] AS DIFF20, [CURWEEKNo21] AS DIFF21, [CURWEEKNo22] AS DIFF22 " & _
                                                 "FROM [UserModules].[user].[SOP_FLUCTUATION] WHERE division = 'Division' and type='" & type & "'")
        monthColumns.Clear()
        blankColumns.Clear()

        Dim week As New weekOfYear
        Dim prevMonth As String = ""
        Dim prevMonthColNum As Integer = 0

        If Not IsNothing(weekList) Then
            If weekList.Rows.Count > 0 Then
                Dim blankCnt As Integer = 1
                For i As Integer = 0 To 22
                    If weekList.Rows(0)("DIFF" & i).ToString = "" Then
                        'Blank weeks
                        blankColumns.Add(i + monthColumns.Count + 3, i + monthColumns.Count + 3)
                        sql = sql.Replace("[DIFF" & i & "]", "[DIFF" & i & "] as [W" & "".PadLeft(blankCnt, "~") & "]")
                        blankCnt += 1
                    Else
                        week.yearAndWeek = weekList.Rows(0)("DIFF" & i).ToString.Replace("W", "")
                        If i = 0 Then
                            prevMonth = week.monthName
                        End If
                        If prevMonth <> week.monthName Then
                            'Adding MONTH  column
                            monthColumns.Add(i + monthColumns.Count + 3, i + monthColumns.Count + 3)
                            Dim monthTotal As String = ""
                            For j As Integer = i - 1 To prevMonthColNum Step -1
                                monthTotal &= "[DIFF" & j & "]+"
                            Next
                            sql = sql.Replace("[DIFF" & i & "]", " case when [site]='Rate,%' then 'MR' else cast(" & monthTotal.Trim("+") & " as varchar) end as [" & prevMonth & "], [DIFF" & i & "] as W" & weekList.Rows(0)("DIFF" & i).ToString)
                            prevMonth = week.monthName
                            prevMonthColNum = i
                        Else
                            sql = sql.Replace("[DIFF" & i & "]", "[DIFF" & i & "] as W" & weekList.Rows(0)("DIFF" & i).ToString)
                        End If

                    End If
                Next
            End If
        End If

        If inch <> "" Then
            sql &= " AND INCH = '" & inch.Replace("--", "") & "' "
        End If
        If model <> "" Then
            sql &= " AND model LIKE '%" & model.Replace("--", "") & "%' "
        End If

        'Ratio filter

        If ratioSelector.SelectedValue <> 0 Then
            Dim val As Integer = ratioSelector.SelectedValue
            Dim condition As New StringBuilder
            If val = -3 Then 'NoChange
                condition.AppendLine("AND model in ( ")
                condition.AppendLine("SELECT distinct model FROM [UserModules].[user].[SOP_FLUCTUATION_REPORT] ")
                condition.AppendLine("where ")
                condition.AppendLine("ABS(Diff0)=0 AND ")
                condition.AppendLine("ABS(Diff1)=0 AND ")
                condition.AppendLine("ABS(Diff2)=0 AND ")
                condition.AppendLine("ABS(Diff3)=0 AND ")
                condition.AppendLine("ABS(Diff4)=0 AND ")
                condition.AppendLine("ABS(Diff5)=0 AND ")
                condition.AppendLine("ABS(Diff6)=0 AND ")
                condition.AppendLine("ABS(Diff7)=0 AND ")
                condition.AppendLine("ABS(Diff8)=0 AND ")
                condition.AppendLine("ABS(Diff9)=0 AND ")
                condition.AppendLine("ABS(Diff10)=0 AND ")
                condition.AppendLine("ABS(Diff11)=0 AND ")
                condition.AppendLine("ABS(Diff12)=0 AND ")
                condition.AppendLine("ABS(Diff13)=0 AND ")
                condition.AppendLine("ABS(Diff14)=0 AND ")
                condition.AppendLine("ABS(Diff15)=0 AND ")
                condition.AppendLine("ABS(Diff16)=0 AND ")
                condition.AppendLine("ABS(Diff17)=0 AND ")
                condition.AppendLine("ABS(Diff18)=0 AND ")
                condition.AppendLine("ABS(Diff19)=0 AND ")
                condition.AppendLine("ABS(Diff20)=0 AND ")
                condition.AppendLine("ABS(Diff21)=0 AND ")
                condition.AppendLine("ABS(Diff22)=0 AND ")
                condition.AppendLine("[Site] = 'Rate,%') ")
            ElseIf val = -1 Then 'From Zero
                condition.AppendLine("AND model in ( ")
                condition.AppendLine("SELECT distinct model FROM [UserModules].[user].[SOP_FLUCTUATION_REPORT] ")
                condition.AppendLine("where ")
                condition.AppendLine("((Diff0)=-20000 OR ")
                condition.AppendLine("(Diff1)=-20000 OR ")
                condition.AppendLine("(Diff2)=-20000 OR ")
                condition.AppendLine("(Diff3)=-20000 OR ")
                condition.AppendLine("(Diff4)=-20000 OR ")
                condition.AppendLine("(Diff5)=-20000 OR ")
                condition.AppendLine("(Diff6)=-20000 OR ")
                condition.AppendLine("(Diff7)=-20000 OR ")
                condition.AppendLine("(Diff8)=-20000 OR ")
                condition.AppendLine("(Diff9)=-20000 OR ")
                condition.AppendLine("(Diff10)=-20000 OR ")
                condition.AppendLine("(Diff11)=-20000 OR ")
                condition.AppendLine("(Diff12)=-20000 OR ")
                condition.AppendLine("(Diff13)=-20000 OR ")
                condition.AppendLine("(Diff14)=-20000 OR ")
                condition.AppendLine("(Diff15)=-20000 OR ")
                condition.AppendLine("(Diff16)=-20000 OR ")
                condition.AppendLine("(Diff17)=-20000 OR ")
                condition.AppendLine("(Diff18)=-20000 OR ")
                condition.AppendLine("(Diff19)=-20000 OR ")
                condition.AppendLine("(Diff20)=-20000 OR ")
                condition.AppendLine("(Diff21)=-20000 OR ")
                condition.AppendLine("(Diff22)=-20000 ) AND ")
                condition.AppendLine("[Site] = 'Rate,%') ")
            ElseIf val = -2 Then 'To Zero
                condition.AppendLine("AND model in ( ")
                condition.AppendLine("SELECT distinct model FROM [UserModules].[user].[SOP_FLUCTUATION_REPORT] ")
                condition.AppendLine("where ")
                condition.AppendLine("((Diff0)=-100 OR ")
                condition.AppendLine("(Diff1)=-100 OR ")
                condition.AppendLine("(Diff2)=-100 OR ")
                condition.AppendLine("(Diff3)=-100 OR ")
                condition.AppendLine("(Diff4)=-100 OR ")
                condition.AppendLine("(Diff5)=-100 OR ")
                condition.AppendLine("(Diff6)=-100 OR ")
                condition.AppendLine("(Diff7)=-100 OR ")
                condition.AppendLine("(Diff8)=-100 OR ")
                condition.AppendLine("(Diff9)=-100 OR ")
                condition.AppendLine("(Diff10)=-100 OR ")
                condition.AppendLine("(Diff11)=-100 OR ")
                condition.AppendLine("(Diff12)=-100 OR ")
                condition.AppendLine("(Diff13)=-100 OR ")
                condition.AppendLine("(Diff14)=-100 OR ")
                condition.AppendLine("(Diff15)=-100 OR ")
                condition.AppendLine("(Diff16)=-100 OR ")
                condition.AppendLine("(Diff17)=-100 OR ")
                condition.AppendLine("(Diff18)=-100 OR ")
                condition.AppendLine("(Diff19)=-100 OR ")
                condition.AppendLine("(Diff20)=-100 OR ")
                condition.AppendLine("(Diff21)=-100 OR ")
                condition.AppendLine("(Diff22)=-100 ) AND ")
                condition.AppendLine("[Site] = 'Rate,%') ")
            Else
                condition.AppendLine("AND model in ( ")
                condition.AppendLine("SELECT distinct model FROM [UserModules].[user].[SOP_FLUCTUATION_REPORT] ")
                condition.AppendLine("where ( ")
                condition.AppendLine("ABS(Diff0)>=" & ratioSelector.SelectedValue & " OR ")
                condition.AppendLine("ABS(Diff1)>=" & ratioSelector.SelectedValue & " OR ")
                condition.AppendLine("ABS(Diff2)>=" & ratioSelector.SelectedValue & " OR ")
                condition.AppendLine("ABS(Diff3)>=" & ratioSelector.SelectedValue & " OR ")
                condition.AppendLine("ABS(Diff4)>=" & ratioSelector.SelectedValue & " OR ")
                condition.AppendLine("ABS(Diff5)>=" & ratioSelector.SelectedValue & " OR ")
                condition.AppendLine("ABS(Diff6)>=" & ratioSelector.SelectedValue & " OR ")
                condition.AppendLine("ABS(Diff7)>=" & ratioSelector.SelectedValue & " OR ")
                condition.AppendLine("ABS(Diff8)>=" & ratioSelector.SelectedValue & " OR ")
                condition.AppendLine("ABS(Diff9)>=" & ratioSelector.SelectedValue & " OR ")
                condition.AppendLine("ABS(Diff10)>=" & ratioSelector.SelectedValue & " OR ")
                condition.AppendLine("ABS(Diff11)>=" & ratioSelector.SelectedValue & " OR ")
                condition.AppendLine("ABS(Diff12)>=" & ratioSelector.SelectedValue & " OR ")
                condition.AppendLine("ABS(Diff13)>=" & ratioSelector.SelectedValue & " OR ")
                condition.AppendLine("ABS(Diff14)>=" & ratioSelector.SelectedValue & " OR ")
                condition.AppendLine("ABS(Diff15)>=" & ratioSelector.SelectedValue & " OR ")
                condition.AppendLine("ABS(Diff16)>=" & ratioSelector.SelectedValue & " OR ")
                condition.AppendLine("ABS(Diff17)>=" & ratioSelector.SelectedValue & " OR ")
                condition.AppendLine("ABS(Diff18)>=" & ratioSelector.SelectedValue & " OR ")
                condition.AppendLine("ABS(Diff19)>=" & ratioSelector.SelectedValue & " OR ")
                condition.AppendLine("ABS(Diff20)>=" & ratioSelector.SelectedValue & " OR ")
                condition.AppendLine("ABS(Diff21)>=" & ratioSelector.SelectedValue & " OR ")
                condition.AppendLine("ABS(Diff22)>=" & ratioSelector.SelectedValue & ") and [Site] = 'Rate,%') ")
            End If
            sql &= condition.ToString
        End If
        sql &= " ORDER BY INCH, Id"
        Return sqlf.GetData(sql)
    End Function

    Private Function prepareTTLData(type As String) As DataTable
        Dim ttl As String = "sum(cast([DIFF0] as decimal) + cast([DIFF1] as decimal) + cast([DIFF2] as decimal) + cast([DIFF3] as decimal) "
        Dim sql As String = "SELECT [SITE], sum(cast([DIFF0] as decimal)), sum(cast([DIFF1] as decimal)), sum(cast([DIFF2] as decimal)), sum(cast([DIFF3] as decimal)), "
        If numberOfColumnsToShow > 4 Then
            sql &= "sum(cast([DIFF4] as decimal)), sum(cast([DIFF5] as decimal)), sum(cast([DIFF6] as decimal)), sum(cast([DIFF7] as decimal)), "
            ttl &= " + cast([DIFF4] as decimal) + cast([DIFF5] as decimal) + cast([DIFF6] as decimal) + cast([DIFF7] as decimal) "
        End If
        If numberOfColumnsToShow > 8 Then
            sql &= "sum(cast([DIFF8] as decimal)), sum(cast([DIFF9] as decimal)), sum(cast([DIFF10] as decimal)), sum(cast([DIFF11] as decimal)), "
            ttl &= " + cast([DIFF8] as decimal) + cast([DIFF9] as decimal) + cast([DIFF10] as decimal) + cast([DIFF11] as decimal) "
        End If
        If numberOfColumnsToShow > 12 Then
            sql &= "sum(cast([DIFF12] as decimal)), sum(cast([DIFF13] as decimal)), sum(cast([DIFF14] as decimal)), sum(cast([DIFF15] as decimal)), "
            ttl &= " + cast([DIFF12] as decimal) + cast([DIFF13] as decimal) + cast([DIFF14] as decimal) + cast([DIFF15] as decimal) "
        End If
        If numberOfColumnsToShow > 16 Then
            sql &= "sum(cast([DIFF16] as decimal)), sum(cast([DIFF17] as decimal)), sum(cast([DIFF18] as decimal)), sum(cast([DIFF19] as decimal)), "
            ttl &= " + cast([DIFF16] as decimal) + cast([DIFF17] as decimal) + cast([DIFF18] as decimal) + cast([DIFF19] as decimal) "
        End If
        If numberOfColumnsToShow > 20 Then
            sql &= "sum(cast([DIFF20] as decimal)), sum(cast([DIFF21] as decimal)), sum(cast([DIFF22] as decimal)), "
            ttl &= " + cast([DIFF20] as decimal) + cast([DIFF21] as decimal) + cast([DIFF22] as decimal) "
        End If
        'sql &= "sum(cast([Total] as decimal)) as [Total] FROM [UserModules].[user].[SOP_FLUCTUATION_REPORT] " & _
        '    "WHERE INCH <> 'te' and (site not like '% diff' or site like 'Total%') and site not like 'Rate%' group by [site]"

        sql &= ttl & ") as [Total] FROM [UserModules].[user].[SOP_FLUCTUATION_REPORT] " & _
            "WHERE INCH <> 'te' and site not like '% diff' and site not like 'Rate%' group by [site]"

        Dim weekList As DataTable = sqlf.GetData("SELECT [CURWEEK] AS DIFF0, [CURWEEKNo1] AS DIFF1, [CURWEEKNo2] AS DIFF2, " & _
                                                 "[CURWEEKNo3] AS DIFF3, [CURWEEKNo4] AS DIFF4, [CURWEEKNo5] AS DIFF5, [CURWEEKNo6] AS DIFF6, " & _
                                                 "[CURWEEKNo7] AS DIFF7, [CURWEEKNo8] AS DIFF8, [CURWEEKNo9] AS DIFF9, [CURWEEKNo10] AS DIFF10, " & _
                                                 "[CURWEEKNo11] AS DIFF11, [CURWEEKNo12] AS DIFF12, [CURWEEKNo13] AS DIFF13, [CURWEEKNo14] AS DIFF14, " & _
                                                 "[CURWEEKNo15] AS DIFF15, [CURWEEKNo16] AS DIFF16, [CURWEEKNo17] AS DIFF17, [CURWEEKNo18] AS DIFF18, " & _
                                                 "[CURWEEKNo19] AS DIFF19, [CURWEEKNo20] AS DIFF20, [CURWEEKNo21] AS DIFF21, [CURWEEKNo22] AS DIFF22 " & _
                                                 "FROM [UserModules].[user].[SOP_FLUCTUATION] WHERE division = 'Division' and type='" & type & "'")
        monthColumns.Clear()
        blankColumns.Clear()

        Dim week As New weekOfYear
        Dim prevMonth As String = ""
        Dim prevMonthColNum As Integer = 0

        If Not IsNothing(weekList) Then
            If weekList.Rows.Count > 0 Then
                Dim blankCnt As Integer = 1
                For i As Integer = 0 To 22
                    If weekList.Rows(0)("DIFF" & i).ToString = "" Then
                        'Blank weeks
                        blankColumns.Add(i + monthColumns.Count + 3, i + monthColumns.Count + 3)
                        sql = sql.Replace("[DIFF" & i & "] as decimal))", "[DIFF" & i & "] as decimal)) as [W" & "".PadLeft(blankCnt, "~") & "]")
                        blankCnt += 1
                    Else
                        week.yearAndWeek = weekList.Rows(0)("DIFF" & i).ToString.Replace("W", "")
                        If i = 0 Then
                            prevMonth = week.monthName
                        End If
                        If prevMonth <> week.monthName Then
                            'Adding MONTH  column
                            monthColumns.Add(i + monthColumns.Count + 3, i + monthColumns.Count + 3)
                            Dim monthTotal As String = ""
                            For j As Integer = i - 1 To prevMonthColNum Step -1
                                monthTotal &= "[DIFF" & j & "]+"
                            Next
                            sql = sql.Replace("[DIFF" & i & "] as decimal))",
                                              " case when [site]='Rate,%' then 'MR' else cast(" & monthTotal.Trim("+") & " as varchar) end as decimal)) as [" & prevMonth & "], " & _
                                              "sum(cast([DIFF" & i & "] as decimal)) as W" & weekList.Rows(0)("DIFF" & i).ToString)
                            prevMonth = week.monthName
                            prevMonthColNum = i
                        Else
                            sql = sql.Replace("[DIFF" & i & "] as decimal))", "[DIFF" & i & "] as decimal)) as W" & weekList.Rows(0)("DIFF" & i).ToString)
                        End If

                    End If
                Next
            End If
        End If

        Return sqlf.GetData(sql)
    End Function

#End Region

#Region "Controls Events"
    Protected Sub mail_Click(sender As Object, e As ImageClickEventArgs) Handles mail.Click
        'SendEmail()
    End Sub

    Protected Sub refresh_Click(sender As Object, e As ImageClickEventArgs) Handles refresh.Click

    End Sub

    Protected Sub type_SelectedIndexChanged(sender As Object, e As EventArgs) Handles type.SelectedIndexChanged
        PrepareReport(type.SelectedValue, division.SelectedValue)
    End Sub

    Protected Sub division_SelectedIndexChanged(sender As Object, e As EventArgs) Handles division.SelectedIndexChanged
        PrepareReport(type.SelectedValue, division.SelectedValue)
    End Sub

    Protected Sub toExcel_Click(sender As Object, e As ImageClickEventArgs) Handles toExcel.Click
        forExport = True
        excelDownload("SOP_FLUCT_REPORT_" & DateTime.Now.ToString("yyyyMMddHmmss"))
    End Sub

    Protected Sub report_RowDataBound(sender As Object, e As GridViewRowEventArgs) Handles report.RowDataBound
        With e.Row
            If .RowType = DataControlRowType.DataRow Then
                f = Not f
                If .Cells(2).Text <> prevModel Then
                    .CssClass = "border_top"
                End If
                Dim cnt As Integer = 0
                For Each cell As TableCell In .Cells

                    If cell.Text = "0" And _
                        .Cells(0).Text <> "Rate,%" And _
                        .Cells(0).Text <> "Total Diff" And _
                        Not monthColumns.Contains(cnt) Then
                        cell.Text = ""
                    End If

                    If .Cells(0).Text = "Rate,%" Then
                        cell.CssClass = "highlite2"
                        If cell.Text = "MR" Then
                            If (report.Rows(.RowIndex - 1).Cells(cnt).Text <> "") And _
                            (report.Rows(.RowIndex - 2).Cells(cnt).Text <> "") Then
                                cell.Text = Math.Round(report.Rows(.RowIndex - 1).Cells(cnt).Text / IIf(report.Rows(.RowIndex - 2).Cells(cnt).Text = "0", 1, report.Rows(.RowIndex - 2).Cells(cnt).Text) * 100)
                            Else
                                cell.Text = "0"
                            End If
                        End If

                        If cnt > 2 Then
                            If cell.Text <> "" And cell.Text <> "-" Then
                                If cell.Text = "-20000" Then
                                    cell.Text = "From ZERO"
                                    cell.CssClass = "red_yellow1"
                                Else
                                    If Convert.ToInt64(cell.Text) > 1000 Then
                                        cell.CssClass = "red_yellow1"
                                        models1.Add(.Cells(2).Text)
                                    ElseIf Convert.ToInt64(cell.Text) > 200 Then
                                        cell.CssClass = "red_yellow2"
                                        models2.Add(.Cells(2).Text)
                                    ElseIf Convert.ToInt64(cell.Text) > 100 Then
                                        cell.CssClass = "red_yellow3"
                                        models3.Add(.Cells(2).Text)
                                    ElseIf Convert.ToInt64(cell.Text) > 50 Then
                                        cell.CssClass = "red_yellow4"
                                        models4.Add(.Cells(2).Text)
                                    ElseIf Convert.ToInt64(cell.Text) > 10 Then
                                        cell.CssClass = "red_yellow5"
                                        models5.Add(.Cells(2).Text)
                                    ElseIf Convert.ToInt64(cell.Text) < 0 Then
                                        cell.CssClass = "green"
                                    End If
                                    cell.Text &= "%"
                                End If
                            End If
                        End If
                    End If

                    If .Cells(0).Text.StartsWith("2") Then
                        If f Then
                            cell.CssClass = "highlite"
                        Else
                            cell.CssClass = "highlite1"
                        End If
                    End If

                    If monthColumns.Contains(cnt) Then
                        cell.CssClass = "highlite2 border_column"
                    End If

                    If blankColumns.Contains(cnt) Then
                        cell.Visible = False
                    End If

                    If "Total Diff Rate,%".Contains(.Cells(0).Text) Then
                        cell.CssClass &= " bold"
                    End If

                    cnt += 1
                    If forExport Then
                        'For excel export
                        Dim classes() As String = cell.CssClass.Split(" ")
                        For Each cl As String In classes
                            Select Case cl
                                Case "border_top"
                                    cell.Style.Add("border-top", "2px solid black")
                                Case "border_column"
                                    cell.Style.Add("border-left", "2px solid black")
                                    cell.Style.Add("border-right", "2px solid black")
                                Case "bold"
                                    cell.Style.Add("font-weight", "bold")
                                Case "highlite"
                                    cell.Style.Add("background-color", "#dbefff")
                                Case "highlite1"
                                    cell.Style.Add("background-color", "#c1e3fe")
                                Case "highlite2"
                                    cell.Style.Add("background-color", "#507CD1")
                                    cell.Style.Add("color", "White")
                                    cell.Style.Add("font-weight", "bold")
                                Case "red"
                                    cell.Style.Add("background-color", "red")
                                Case "green"
                                    cell.Style.Add("background-color", "green")
                                Case "red_yellow1"
                                    cell.Style.Add("background-color", "#ff0000")
                                Case "red_yellow2"
                                    cell.Style.Add("background-color", "#ff3f00")
                                Case "red_yellow3"
                                    cell.Style.Add("background-color", "#ff7f00")
                                Case "red_yellow4"
                                    cell.Style.Add("background-color", "#ffbf00")
                                Case "red_yellow5"
                                    cell.Style.Add("background-color", "#ffff00")
                            End Select
                        Next
                    End If
                Next

                If .Cells(0).Text = "Rate,%" Then
                    .Cells(0).Text = "Rate"
                End If

                prevModel = .Cells(2).Text

            ElseIf e.Row.RowType = DataControlRowType.Header Then
                Dim cnt As Integer = 0
                For Each c As TableCell In e.Row.Cells
                    c.CssClass = "gridHeaderCell"
                    If blankColumns.Contains(cnt) Then
                        c.Visible = False
                    End If
                    cnt += 1
                Next
                e.Row.CssClass = "gridHeaderRow"
            End If
        End With
    End Sub
#End Region

#Region "Misc"
    Private Function divide(operand1 As Integer, operand2 As Integer, ifZeroValue As Integer) As Decimal
        If operand1 = 0 And operand2 = 0 Then
            Return 0
        ElseIf operand2 = 0 Then
            Return ifZeroValue
        Else
            Return operand1 / operand2
        End If
    End Function

    Private Sub excelDownload(ByVal filename As String)
        Response.Clear()
        Response.Buffer = True
        Response.AddHeader("content-disposition", "attachment;filename=" & filename & ".xls")
        Response.ContentType = "application/vnd.xls"
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8")
        EnableViewState = False
        Response.Charset = "utf-8"

        Response.Write(gridToHtml(type.SelectedValue, division.SelectedValue))
        Response.End()
    End Sub

    Private Sub excelSave(fileName As String, data As String)
        Dim fileS As New FileStream(fileName, FileMode.Create)
        Dim sw As New StreamWriter(fileS)
        sw.Write(data)
        sw.Flush()
        sw.Close()
        fileS.Close()
    End Sub

    Private Sub SendEmail()
        forExport = True
        Dim mailRecepients As DataTable = sqlf.GetData("SELECT * from UserModules.dbo.Mail_Groups WHERE MAIL_GROUP_NAME = 'SOP Fluctuation' and enabled = 'Y' and single_id = 'i.krivokon'")
        If Not IsNothing(mailRecepients) Then
            Dim tmpFolderName As String = Server.MapPath("../TMP") & "\TF" & Guid.NewGuid.ToString.Replace("-", "")

            My.Computer.FileSystem.CreateDirectory(tmpFolderName)
            'TESTING
            Response.Clear()
            '-------------------------
            Dim cal As System.Globalization.Calendar = System.Globalization.CultureInfo.CurrentCulture.Calendar

            Dim curWeek As String = cal.GetWeekOfYear(Date.Today, System.Globalization.CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Monday).ToString

            Dim body As New StringBuilder
            body.Append("<div style='font-family: Gulim, Verdana'>")
            body.Append("<strong><p style='font-size: large'>Dear colleagues</p></strong>")
            body.Append("<strong><p style='font-size: medium'>Please pay attention to <u>W" & curweek & _
                        " SERK(TV/MON) SOP fluctuation report. </u></p></strong>")
            body.Append("<strong><p style='font-size: medium'>Below you can find Total data. Detail information is in the attachment </p></strong>")
            ''body.Append("<strong><p style='font-size: medium'>Report prepared basing on the G-ERP data from ZRPPB44050 </p></strong>")
            Dim attachments As New Collection
            For Each d As ListItem In division.Items
                division.SelectedItem.Selected = False
                d.Selected = True
                body.Append("<br /><hr /><strong><p style='font-size: large'>/// " & d.Text & " ///</p></strong>")
                For Each l As ListItem In type.Items
                    type.SelectedItem.Selected = False
                    l.Selected = True

                    'Creating report file for attachment
                    Dim reportFile As String = tmpFolderName & "\" & l.Value & "_" & d.Text & "_SOP_FLUCT_REPORT_" & DateTime.Now.ToString("yyyyMMddHmmss") & ".xls"
                    numberOfColumnsToShow = 100
                    Dim data As String = gridToHtml(l.Value, d.Value)
                    excelSave(reportFile, data)
                    attachments.Add(reportFile, reportFile)

                    'Creating total data for mail body
                    numberOfColumnsToShow = 10
                    Dim totalData As String = totalDataToHtml()
                    body.Append("<strong><p style='font-size: medium; color: blue;'>[ W vs " & l.Value.Substring(0, 1) & "-" & l.Value.Substring(1, l.Value.Length - 1) & " ] SOP Summary </p></strong>")
                    body.Append(totalData)
                Next
            Next

            body.Append("<hr />")
            body.Append("<p> This is automatic email. Please, do not reply to this address </p>")
            body.Append("<p> If you have any comments or proposal about this report contents - email to <a href='mailto:i.krivokon@samsung.com'>i.krivokon</a> or <a href='mailto:yoonjung.cha@samsung.com'>yoonjung.cha</a> </p>")
            body.Append("</div>")
            'TESTING
            Response.Write(body)
            '--------------------

            Dim m As New sharedClasses.Common.Mailer
            Dim aList As New StringBuilder
            For Each r As DataRow In mailRecepients.Rows
                If aList.Length > 0 Then
                    aList.Append(";")
                End If
                aList.Append(r("single_id") & "@samsung.com")
            Next
            Dim attList As New StringBuilder
            For Each att As String In attachments
                If attList.Length > 0 Then
                    attList.Append(";")
                End If
                attList.Append(att)
            Next

            m.send("serk.notify@samsung.com", _
                    aList.ToString, _
                    "SERK W" & curWeek & " TV/MON SOP fluctuation report.", _
                    body.ToString, _
                    attList.ToString)

            'm.send("serk.notify@samsung.com", _
            '        "i.krivokon@samsung.com;guevara.woo@samsung.com", _
            '        "SERK W" & curWeek & " TV/MON SOP fluctuation report.", _
            '        body.ToString, _
            '        attList.ToString)

            'm.send("serk.notify@samsung.com", _
            '        "i.krivokon@samsung.com", _
            '        "SERK W" & curWeek & " TV/MON SOP fluctuation report.", _
            '        body.ToString, _
            '        attList.ToString)

            ' My.Computer.FileSystem.DeleteDirectory(tmpFolderName, FileIO.DeleteDirectoryOption.DeleteAllContents)

        End If
    End Sub

    Private Function gridToHtml(sopType As String, division As String) As String

        PrepareReport(sopType, division)
        report.DataSource = prepareData(inchFilter.Text, modelFilter.Text, sopType)
        report.DataBind()
        For Each dr As GridViewRow In report.Rows
            If dr.RowType = DataControlRowType.DataRow Then
                If models1.Contains(dr.Cells(2).Text) Then
                    dr.Cells(2).Style.Add("background-color", "#ff0000")
                ElseIf models2.Contains(dr.Cells(2).Text) Then
                    dr.Cells(2).Style.Add("background-color", "#ff3f00")
                ElseIf models3.Contains(dr.Cells(2).Text) Then
                    dr.Cells(2).Style.Add("background-color", "#ff7f00")
                ElseIf models4.Contains(dr.Cells(2).Text) Then
                    dr.Cells(2).Style.Add("background-color", "#ffbf00")
                ElseIf models5.Contains(dr.Cells(2).Text) Then
                    dr.Cells(2).Style.Add("background-color", "#ffff00")
                End If
            End If
        Next

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

        Dim txt As New StringBuilder
        txt.AppendLine("<table><tr> ")
        txt.AppendLine("<td rowspan=""2"">Legend:</td> ")
        txt.AppendLine("<td>11~50</td> ")
        txt.AppendLine("<td>51~100</td> ")
        txt.AppendLine("<td>101~200</td> ")
        txt.AppendLine("<td>201~1000</td> ")
        txt.AppendLine("<td>1000~</td> ")
        txt.AppendLine("</tr><tr> ")
        txt.AppendLine("<td style=""background-color: #ffff00"">&nbsp;</td> ")
        txt.AppendLine("<td style=""background-color: #ffbf00"">&nbsp;</td> ")
        txt.AppendLine("<td style=""background-color: #ff7f00"">&nbsp;</td> ")
        txt.AppendLine("<td style=""background-color: #ff3f00"">&nbsp;</td> ")
        txt.AppendLine("<td style=""background-color: #ff0000"">&nbsp;</td> ")
        txt.AppendLine("</tr></table> ")

        Return txt.ToString & sw.ToString
    End Function

    Private Sub MakeTotalTable()
        If total.Rows.Count > 0 Then
            Dim i As Integer
            Dim drDiff As New GridViewRow(-1, -1, DataControlRowType.DataRow, DataControlRowState.Normal)
            Dim drRate As New GridViewRow(-1, -1, DataControlRowType.DataRow, DataControlRowState.Normal)
            For Each c As TableCell In total.HeaderRow.Cells

                Dim nc As New TableCell
                nc.Text = Convert.ToInt64(total.Rows(1).Cells(i).Text.Trim("M").Trim("P").Trim("_")) - Convert.ToInt64(total.Rows(0).Cells(i).Text.Trim("M").Trim("P").Trim("_"))
                drDiff.Cells.Add(nc)

                Dim nnc As New TableCell
                nnc.Text = (Math.Round(Convert.ToInt64(nc.Text) / Convert.ToInt64(total.Rows(1).Cells(i).Text.Trim("M").Trim("P").Trim("_")) * 100) & "%").Replace("NaN%", "-")
                drRate.Cells.Add(nnc)

                If Not c.Text.Contains("W") Then
                    c.Style.Add("background-color", "#507CD1")
                    c.Style.Add("color", "White")
                    c.Style.Add("font-weight", "bold")

                    nc.Style.Add("background-color", "#507CD1")
                    nc.Style.Add("color", "White")
                    nc.Style.Add("font-weight", "bold")

                    nnc.Style.Add("background-color", "#507CD1")
                    nnc.Style.Add("color", "White")
                    nnc.Style.Add("font-weight", "bold")

                    total.Rows(0).Cells(i).Style.Add("background-color", "#507CD1")
                    total.Rows(0).Cells(i).Style.Add("color", "White")
                    total.Rows(0).Cells(i).Style.Add("font-weight", "bold")

                    total.Rows(1).Cells(i).Style.Add("background-color", "#507CD1")
                    total.Rows(1).Cells(i).Style.Add("color", "White")
                    total.Rows(1).Cells(i).Style.Add("font-weight", "bold")
                Else
                    nc.CssClass = "highlite1"
                    nc.Style.Add("background-color", "#c1e3fe")
                    nnc.Style.Add("font-weight", "bold")

                    nnc.CssClass = "highlite2"
                    nnc.Style.Add("background-color", "#507CD1")
                    nnc.Style.Add("color", "White")
                    nnc.Style.Add("font-weight", "bold")
                End If

                If i > 0 Then
                    total.Rows(0).Cells(i).Text = Convert.ToInt64(total.Rows(0).Cells(i).Text).ToString("N0", CultureInfo.InvariantCulture)
                    total.Rows(1).Cells(i).Text = Convert.ToInt64(total.Rows(1).Cells(i).Text).ToString("N0", CultureInfo.InvariantCulture)
                    nc.Text = Convert.ToInt64(nc.Text).ToString("N0", CultureInfo.InvariantCulture)
                End If
                i += 1
            Next


            drDiff.Cells(0).Text = "Diff"
            drRate.Cells(0).Text = "Rate"

            CType(total.Controls(0), Table).Rows.Add(drDiff)
            CType(total.Controls(0), Table).Rows.Add(drRate)
        End If
    End Sub

    Private Function totalDataToHtml() As String
        PrepareReport(type.SelectedValue, division.SelectedValue)
        total.DataSource = prepareTTLData(type.SelectedValue)
        total.DataBind()
        MakeTotalTable()
        Dim sw As StringWriter = New StringWriter(myCItrad)
        Dim hw As HtmlTextWriter = New HtmlTextWriter(sw)
        total.RenderControl(hw)
        Return sw.ToString
    End Function

#End Region

    Public Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)

    End Sub



    
End Class