Jump to content
Software FX Community

database

User (Legacy)

By User (Legacy)
in Server

Recommended Posts

User (Legacy) Newbie

User (Legacy)

Posted
Posted 

<!-- #include virtual="/Include/CfxIE.inc" --><!-- #include

virtual="/analytics_include/common.inc" --><!-- #include

virtual="/analytics_include/database_connection.inc" --><%

Dim input_client_site_id, input_context, input_breakdown, input_section_id,

input_dated, input_end_dated, input_time_rule_set

Dim custom_x_chart_size, custom_y_chart_size, input_visitor_segment_id,

input_locale

input_client_site_id = Request("CSID")

input_context = Request("CTX")

input_time_rule_set = Request("TM")

input_breakdown = Request("BRK")

input_visitor_segment_id = Request("VSID")

input_dated = Request("DT")

input_end_dated = Request("EDT")

input_section_id = Request("SPID")

input_locale = Request("LCE")

custom_x_chart_size = Request("GX")

custom_y_chart_size = Request("GY")

If input_context = Empty Then input_context = 1 End If

If input_time_rule_set = Empty Then input_time_rule_set = 1 End If

If input_visitor_segment_id = Empty Then input_visitor_segment_id = 0 End If

If custom_x_chart_size = Empty Then custom_x_chart_size = 600 End If

If custom_y_chart_size = Empty Then custom_y_chart_size = 500 End If

%><!-- #include virtual="/analytics_include/locale_string.inc" --><%

Dim x_axis_title, y_axis_title, y2_axis_title

y_axis_title = lang_sessions_and_visitors

y2_axis_title = lang_page_views

Dim command, result_set, Error_Text

Set command = Server.CreateObject("ADODB.Command")

command.ActiveConnection = connection

Dim sqlOuterSelect, sqlOuterOrder, sqlSelect, sqlFrom, sqlSection, sqlDate,

sqlOrder, report_dated, report_edated, sql_date, sqlOuterJoinPart1,

sqlOuterJoinPart2,input_date_format

report_dated = input_dated

report_end_dated = input_end_dated

If input_context = 2 Then

 sqlSection = " AND a.section_category = 0 " _

     & " AND a.section_id = ? "

End If

sqlOuterSelect = " SELECT DATED, SESSIONS " & chr(34) & lang_sessions &

chr(34) & ", VISITORS " & chr(34) & lang_visitors & chr(34) & ", VIEWS " &

chr(34) & lang_page_views & chr(34) & " FROM ( "

sqlOuterOrder = " ORDER BY DATED "

input_date_format = lang_date_format

Select Case input_time_rule_set

 Case 1

  report_end_dated = input_dated

  report_dated = input_dated

  sqlSelect = " SELECT TO_CHAR(a.day, 'DD-MON-YY') DATED,

to_char(to_date(a.hour,'HH24' ),'HH24') HOUR "

  sqlDate = " AND (a.day) = TO_DATE(?) " _

          & " AND (a.day) = TO_DATE(?) "

  sqlOrder = " ORDER BY a.hour "

  x_axis_title = lang_hour

  If input_context = 1 Then

   sqlFrom = " FROM segment_hour a "

  Else

   sqlFrom = " FROM section_hour a "

  End If

  input_date_format = AF_NONE

  sqlOuterOrder = " ORDER BY HOUR "

  sqlOuterJoinPart1 = " SELECT TO_CHAR(TRUNC(SYSDATE) + (c.hour/24), '" &

lang_hour_format & "') DATED, NVL(b.SESSIONS, 0) SESSIONS, NVL(b.VISITORS,

0) VISITORS, NVL(b.VIEWS, 0) VIEWS, TO_NUMBER(c.HOUR) HOUR  FROM ( "

        sqlOuterJoinPart2 = " ) b, hours c, client_site d WHERE b.HOUR (" &

chr(43) & ") = c.hour " _

             & " AND b.client_site_id (" & chr(43) & ") = c.client_site_id "

_

             & " AND c.client_site_id = d.client_site_id " _

             & " AND c.client_site_id  = ? " _

                         & " AND TO_DATE(?) >= NVL(d.begin_date,SYSDATE -

366) " _

                         & " AND TO_DATE(?) >= NVL(d.begin_date,SYSDATE -

366) "

 Case 2, 3

  If input_end_dated = empty Then

   If input_time_rule_set = 2 Then

    sql_date = "SELECT TO_CHAR(NEXT_DAY(TO_DATE(?) - 7,

DECODE(client_week,1,'sunday',2,'monday',3,'tuesday',4,'wednesday',5,'thursd

ay',6,'friday',7,'saturday','sunday')) + 6, 'DD-MON-YY') DATED FROM

client_site WHERE client_site_id = ? "

    command.CommandText = sql_date

    set result_set = command.Execute

(,Array(input_dated,input_client_site_id))

    If err.Number <> 0 Then

     Error_Text = "Error:" & Err.Number & ":" & Err.Description

     Response.AppendToLog Error_Text

    End If

    report_end_dated = result_set("DATED")

    result_set.close

    set result_set = nothing

    sql_date = "SELECT TO_CHAR(NEXT_DAY(TO_DATE( ? ) - 7,

DECODE(client_week,1,'sunday',2,'monday',3,'tuesday',4,'wednesday',5,'thursd

ay',6,'friday',7,'saturday','sunday')), 'DD-MON-YY') DATED FROM client_site

WHERE client_site_id = ? "

    command.CommandText = sql_date

    set result_set = command.Execute

(,Array(input_dated,input_client_site_id))

    If err.Number <> 0 Then

     Error_Text = "Error:" & Err.Number & ":" & Err.Description

     Response.AppendToLog Error_Text

    End If

    report_dated = result_set("DATED")

    result_set.close

    set result_set = nothing

   Else

    sql_date = "SELECT TO_CHAR(ADD_MONTHS(TRUNC(TO_DATE(?) - client_month +

1, 'MONTH') - 1 + client_month, 1) - 1, 'DD-MON-YY') DATED FROM client_site

WHERE client_site_id = ? "

    command.CommandText = sql_date

    set result_set = command.Execute

(,Array(input_dated,input_client_site_id))

    If err.Number <> 0 Then

     Error_Text = "Error:" & Err.Number & ":" & Err.Description

     Response.AppendToLog Error_Text

    End If

    report_end_dated = result_set("DATED")

    result_set.close

    set result_set = nothing

    sql_date = "SELECT TO_CHAR(TRUNC(TO_DATE(?) - client_month + 1,

'MONTH') - 1 + client_month, 'DD-MON-YY') DATED FROM client_site WHERE

client_site_id = ? "

    command.CommandText = sql_date

    set result_set = command.Execute

(,Array(input_dated,input_client_site_id))

    If err.Number <> 0 Then

     Error_Text = "Error:" & Err.Number & ":" & Err.Description

     Response.AppendToLog Error_Text

    End If

    report_dated = result_set("DATED")

    result_set.close

    set result_set = nothing

   End If

  End If

  x_axis_title = lang_day

  sqlSelect = " SELECT TO_CHAR(a.day, 'DD-MON-YY') DATED "

  sqlDate = " AND (a.day) <= TO_DATE(?) " _

          & " AND (a.day) >= TO_DATE(?) "

  sqlOrder = " ORDER BY a.day "

  If input_context = 1 Then

   sqlFrom = " FROM segment_day a "

  Else

   sqlFrom = " FROM section_day a "

  End If

  sqlOuterJoinPart1 = " SELECT c.day DATED, NVL(b.SESSIONS, 0) SESSIONS,

NVL(b.VISITORS, 0) VISITORS, NVL(b.VIEWS, 0) VIEWS FROM ( "

  sqlOuterJoinPart2 = " ) b, days c WHERE b.DATED (" & chr(43) & ") = c.day

" _

     & " AND b.client_site_id (" & chr(43) & ") = c.client_site_id " _

     & " AND c.client_site_id  = ? " _

     & " AND TO_DATE(c.day) <= TO_DATE(?) " _

     & " AND TO_DATE(c.day) >= TO_DATE(?) "

 Case 4

  If input_end_dated = empty Then

   sql_date = "SELECT TO_CHAR(TRUNC(TO_DATE(?), 'day'), 'DD-MON-YY') DATED

FROM dual "

   command.CommandText = sql_date

   set result_set = command.Execute (,Array(input_dated))

   If err.Number <> 0 Then

    Error_Text = "Error:" & Err.Number & ":" & Err.Description

    Response.AppendToLog Error_Text

   End If

   report_end_dated = result_set("DATED")

   result_set.close

   set result_set = nothing

   sql_date = "SELECT TO_CHAR(TRUNC(TO_DATE(?) - 84, 'day'), 'DD-MON-YY')

DATED FROM dual"

   command.CommandText = sql_date

   set result_set = command.Execute (,Array(input_dated))

    If err.Number <> 0 Then

    Error_Text = "Error:" & Err.Number & ":" & Err.Description

    Response.AppendToLog Error_Text

   End If

   report_dated = result_set("DATED")

   result_set.close

   set result_set = nothing

  End If

  sqlSelect = " SELECT TO_CHAR(a.week, 'DD-MON-YY') DATED "

  sqlDate = " AND (a.week) <= TO_DATE(?) " _

          & " AND (a.week) >= TO_DATE(?) "

  sqlOrder = " ORDER BY a.week "

  x_axis_title = lang_week

  If input_context = 1 Then

   sqlFrom = " FROM segment_week a "

  Else

   sqlFrom = " FROM section_week a "

  End If

  sqlOuterJoinPart1 = " SELECT c.week DATED, NVL(b.SESSIONS, 0) SESSIONS,

NVL(b.VISITORS, 0) VISITORS, NVL(b.VIEWS, 0) VIEWS FROM ( "

  sqlOuterJoinPart2 = " ) b, weeks c WHERE b.DATED (" & chr(43) & ") =

c.week " _

        & " AND b.client_site_id (" & chr(43) & ") = c.client_site_id " _

        & " AND c.client_site_id  = ? " _

                                & " AND TO_DATE(c.week) <= TO_DATE(?) " _

                                & " AND TO_DATE(c.week) >= TO_DATE(?) "

 Case 5

  If input_end_dated = empty Then

   sql_date = "SELECT TO_CHAR(TRUNC(TO_DATE(?), 'month'), 'DD-MON-YY') DATED

FROM dual "

   command.CommandText = sql_date

   set result_set = command.Execute (,Array(input_dated))

   If err.Number <> 0 Then

    Error_Text = "Error:" & Err.Number & ":" & Err.Description

    Response.AppendToLog Error_Text

   End If

   report_end_dated = result_set("DATED")

   result_set.close

   set result_set = nothing

   sql_date = "SELECT TO_CHAR(ADD_MONTHS(TRUNC(TO_DATE(?), 'month'), -12),

'DD-MON-YY') DATED FROM dual "

   command.CommandText = sql_date

   set result_set = command.Execute (,Array(input_dated))

   If err.Number <> 0 Then

    Error_Text = "Error:" & Err.Number & ":" & Err.Description

    Response.AppendToLog Error_Text

   End If

   report_dated = result_set("DATED")

   result_set.close

   set result_set = nothing

  End If

  x_axis_title = lang_month

  sqlSelect = " SELECT TO_CHAR(a.month, 'DD-MON-YY') DATED "

  sqlDate = " AND (a.month) <= TO_DATE(?) " _

          & " AND (a.month) >= TO_DATE(?) "

  sqlOrder = " ORDER BY a.month "

  If input_context = 1 Then

   sqlFrom = " FROM segment_month a "

  Else

   sqlFrom = " FROM section_month a "

  End If

  sqlOuterJoinPart1 = " SELECT c.month DATED, NVL(b.SESSIONS, 0) SESSIONS,

NVL(b.VISITORS, 0) VISITORS, NVL(b.VIEWS, 0) VIEWS FROM ( "

  sqlOuterJoinPart2 = " ) b, months c where b.DATED (" & chr(43) & ") =

c.month " _

        & " AND b.client_site_id (" & chr(43) & ") = c.client_site_id " _

        & " AND c.client_site_id  = ? " _

                                & " AND TO_DATE(c.month) <= TO_DATE(?) " _

                                & " AND TO_DATE(c.month) >= TO_DATE(?) "

End Select

set command = nothing

Set command = Server.CreateObject("ADODB.Command")

command.ActiveConnection = connection

sql_final = sqlOuterSelect _

  & sqlOuterJoinPart1 _

  & sqlSelect _

     & " , a.sessions SESSIONS, a.unique_visitors VISITORS, a.page_views

VIEWS, a.client_site_id client_site_id " _

     & sqlFrom _

     & " WHERE " _

     & " a.client_site_id = ? " _

  & " AND a.visitor_segment_id = ? " _

     & sqlSection _

     & sqlDate _

     & sqlOrder _

     & sqlOuterJoinPart2 _

     & " ) " _

     & sqlOuterOrder

command.CommandText = sql_final

If input_context = 1 Then

 set result_set = command.Execute

(,Array(input_client_site_id,input_visitor_segment_id,report_end_dated,repor

t_dated,input_client_site_id,report_end_dated,report_dated))

Else

 set result_set = command.Execute

(,Array(input_client_site_id,input_visitor_segment_id,input_section_id,repor

t_end_dated,report_dated,input_client_site_id,report_end_dated,report_dated)

)

End If

If Err.Number <> 0 Then

 Error_Text = "SQL:" & sql_final & "Error:" & Err.Number & ":" &

Err.Description

 Response.AppendToLog Error_Text

End If

Set chart = Server.CreateObject("ChartFX.WebServer")

chart.RgbBk = custom_bg_color

chart.Chart3D = custom_view_3d

chart.Axis(AXIS_X).Title = x_axis_title

chart.Axis(AXIS_Y).Title = y_axis_title

chart.Axis(AXIS_Y2).Title = y2_axis_title

chart.Axis(AXIS_Y).TitleFont.Bold=TRUE

chart.Axis(AXIS_X).TitleFont.Bold=TRUE

chart.Axis(AXIS_Y2).TitleFont.Bold=TRUE

chart.OpenDataEx COD_VALUES,3,COD_UNKNOWN

chart.CloseData COD_VALUES

chart.Series(0).YAxis = AXIS_Y

chart.Series(1).YAxis = AXIS_Y

chart.Series(2).YAxis = AXIS_Y2

chart.Series(2).Gallery = LINES

chart.Axis(AXIS_X).Format = input_date_format

chart.AdoResultset result_set

chart.Axis(AXIS_X).LabelAngle = 90

chart.Axis(AXIS_Y).Locale = lang_format

chart.Axis(AXIS_Y).Format = AF_DATE

chart.Axis(AXIS_Y).Locale = lang_format

chart.Axis(AXIS_Y).Format = AF_NUMBER

chart.Axis(AXIS_Y).Decimals = custom_num_decimal_places

chart.Axis(AXIS_Y2).Locale = lang_format

chart.Axis(AXIS_Y2).Format = AF_NUMBER

chart.Axis(AXIS_Y2).Decimals = custom_num_decimal_places

chart.Axis(AXIS_Y).Min = custom_min_value

chart.Axis(AXIS_Y2).Min = custom_min_value

chart.Axis(AXIS_X).TickMark = TS_NONE

chart.Axis(AXIS_Y2).TickMark = TS_CROSS

chart.Grid = CHART_HORZGRID

chart.Border = custom_border

chart.Axis(AXIS_Y).GridColor = custom_grid_color

chart.Axis(AXIS_X).GridColor = custom_grid_color

chart.Series(0).BorderColor = custom_border_color

chart.Series(1).BorderColor = custom_border_color

chart.Series(2).BorderColor = black

chart.TopGap = custom_top_gap

chart.SerLegBox = custom_legend_box

Set SerLegBox = chart.SerLegBoxObj

SerLegBox.BkColor = custom_legend_box_color

chart.SerLegBoxObj.Docked = custom_legend_align

chart.SerLegBoxObj.BorderStyle = custom_legend_box_style

result_set.close

set result_set = nothing

set command = nothing

connection.close

Set connection = Nothing

chart.GetHtmlData custom_x_chart_size,custom_y_chart_size,"Image"

%>
Link to comment
Share on other sites
Software FX Newbie

Software FX

Posted
Posted 

Have you tried GetHtmlTag with your code ?

Have you tried commenting out the block of code that customizes the chart ?

--

Regards,

JC

Software FX Support

"Peter" <plee@doubleclick.net> wrote in message

news:D4rXqZdVCHA.1940@webserver1.softwarefx.com...

> <!-- #include virtual="/Include/CfxIE.inc" --><!-- #include

> virtual="/analytics_include/common.inc" --><!-- #include

> virtual="/analytics_include/database_connection.inc" --><%

> Dim input_client_site_id, input_context, input_breakdown,

input_section_id,

> input_dated, input_end_dated, input_time_rule_set

> Dim custom_x_chart_size, custom_y_chart_size, input_visitor_segment_id,

> input_locale

> input_client_site_id = Request("CSID")

> input_context = Request("CTX")

> input_time_rule_set = Request("TM")

> input_breakdown = Request("BRK")

> input_visitor_segment_id = Request("VSID")

> input_dated = Request("DT")

> input_end_dated = Request("EDT")

> input_section_id = Request("SPID")

> input_locale = Request("LCE")

> custom_x_chart_size = Request("GX")

> custom_y_chart_size = Request("GY")

>

> If input_context = Empty Then input_context = 1 End If

> If input_time_rule_set = Empty Then input_time_rule_set = 1 End If

> If input_visitor_segment_id = Empty Then input_visitor_segment_id = 0 End

If

> If custom_x_chart_size = Empty Then custom_x_chart_size = 600 End If

> If custom_y_chart_size = Empty Then custom_y_chart_size = 500 End If

>

> %><!-- #include virtual="/analytics_include/locale_string.inc" --><%

>

> Dim x_axis_title, y_axis_title, y2_axis_title

> y_axis_title = lang_sessions_and_visitors

> y2_axis_title = lang_page_views

>

> Dim command, result_set, Error_Text

> Set command = Server.CreateObject("ADODB.Command")

> command.ActiveConnection = connection

>

> Dim sqlOuterSelect, sqlOuterOrder, sqlSelect, sqlFrom, sqlSection,

sqlDate,

> sqlOrder, report_dated, report_edated, sql_date, sqlOuterJoinPart1,

> sqlOuterJoinPart2,input_date_format

> report_dated = input_dated

> report_end_dated = input_end_dated

>

> If input_context = 2 Then

>  sqlSection = " AND a.section_category = 0 " _

>      & " AND a.section_id = ? "

> End If

> sqlOuterSelect = " SELECT DATED, SESSIONS " & chr(34) & lang_sessions &

> chr(34) & ", VISITORS " & chr(34) & lang_visitors & chr(34) & ", VIEWS " &

> chr(34) & lang_page_views & chr(34) & " FROM ( "

> sqlOuterOrder = " ORDER BY DATED "

> input_date_format = lang_date_format

>

> Select Case input_time_rule_set

>  Case 1

>   report_end_dated = input_dated

>   report_dated = input_dated

>

>   sqlSelect = " SELECT TO_CHAR(a.day, 'DD-MON-YY') DATED,

> to_char(to_date(a.hour,'HH24' ),'HH24') HOUR "

>   sqlDate = " AND (a.day) = TO_DATE(?) " _

>           & " AND (a.day) = TO_DATE(?) "

>   sqlOrder = " ORDER BY a.hour "

>   x_axis_title = lang_hour

>   If input_context = 1 Then

>    sqlFrom = " FROM segment_hour a "

>   Else

>    sqlFrom = " FROM section_hour a "

>   End If

>

>   input_date_format = AF_NONE

>   sqlOuterOrder = " ORDER BY HOUR "

>   sqlOuterJoinPart1 = " SELECT TO_CHAR(TRUNC(SYSDATE) + (c.hour/24), '" &

> lang_hour_format & "') DATED, NVL(b.SESSIONS, 0) SESSIONS, NVL(b.VISITORS,

> 0) VISITORS, NVL(b.VIEWS, 0) VIEWS, TO_NUMBER(c.HOUR) HOUR  FROM ( "

>         sqlOuterJoinPart2 = " ) b, hours c, client_site d WHERE b.HOUR ("

&

> chr(43) & ") = c.hour " _

>              & " AND b.client_site_id (" & chr(43) & ") = c.client_site_id

"

> _

>              & " AND c.client_site_id = d.client_site_id " _

>              & " AND c.client_site_id  = ? " _

>                          & " AND TO_DATE(?) >= NVL(d.begin_date,SYSDATE -

> 366) " _

>                          & " AND TO_DATE(?) >= NVL(d.begin_date,SYSDATE -

> 366) "

>  Case 2, 3

>   If input_end_dated = empty Then

>    If input_time_rule_set = 2 Then

>     sql_date = "SELECT TO_CHAR(NEXT_DAY(TO_DATE(?) - 7,

>

DECODE(client_week,1,'sunday',2,'monday',3,'tuesday',4,'wednesday',5,'thursd

> ay',6,'friday',7,'saturday','sunday')) + 6, 'DD-MON-YY') DATED FROM

> client_site WHERE client_site_id = ? "

>     command.CommandText = sql_date

>     set result_set = command.Execute

> (,Array(input_dated,input_client_site_id))

>     If err.Number <> 0 Then

>      Error_Text = "Error:" & Err.Number & ":" & Err.Description

>      Response.AppendToLog Error_Text

>     End If

>     report_end_dated = result_set("DATED")

>     result_set.close

>     set result_set = nothing

>

>     sql_date = "SELECT TO_CHAR(NEXT_DAY(TO_DATE( ? ) - 7,

>

DECODE(client_week,1,'sunday',2,'monday',3,'tuesday',4,'wednesday',5,'thursd

> ay',6,'friday',7,'saturday','sunday')), 'DD-MON-YY') DATED FROM

client_site

> WHERE client_site_id = ? "

>     command.CommandText = sql_date

>     set result_set = command.Execute

> (,Array(input_dated,input_client_site_id))

>     If err.Number <> 0 Then

>      Error_Text = "Error:" & Err.Number & ":" & Err.Description

>      Response.AppendToLog Error_Text

>     End If

>     report_dated = result_set("DATED")

>     result_set.close

>     set result_set = nothing

>    Else

>     sql_date = "SELECT TO_CHAR(ADD_MONTHS(TRUNC(TO_DATE(?) - client_month

+

> 1, 'MONTH') - 1 + client_month, 1) - 1, 'DD-MON-YY') DATED FROM

client_site

> WHERE client_site_id = ? "

>     command.CommandText = sql_date

>     set result_set = command.Execute

> (,Array(input_dated,input_client_site_id))

>     If err.Number <> 0 Then

>      Error_Text = "Error:" & Err.Number & ":" & Err.Description

>      Response.AppendToLog Error_Text

>     End If

>     report_end_dated = result_set("DATED")

>     result_set.close

>     set result_set = nothing

>

>     sql_date = "SELECT TO_CHAR(TRUNC(TO_DATE(?) - client_month + 1,

> 'MONTH') - 1 + client_month, 'DD-MON-YY') DATED FROM client_site WHERE

> client_site_id = ? "

>     command.CommandText = sql_date

>     set result_set = command.Execute

> (,Array(input_dated,input_client_site_id))

>     If err.Number <> 0 Then

>      Error_Text = "Error:" & Err.Number & ":" & Err.Description

>      Response.AppendToLog Error_Text

>     End If

>     report_dated = result_set("DATED")

>     result_set.close

>     set result_set = nothing

>    End If

>   End If

>

>   x_axis_title = lang_day

>   sqlSelect = " SELECT TO_CHAR(a.day, 'DD-MON-YY') DATED "

>   sqlDate = " AND (a.day) <= TO_DATE(?) " _

>           & " AND (a.day) >= TO_DATE(?) "

>   sqlOrder = " ORDER BY a.day "

>

>   If input_context = 1 Then

>    sqlFrom = " FROM segment_day a "

>   Else

>    sqlFrom = " FROM section_day a "

>   End If

>

>   sqlOuterJoinPart1 = " SELECT c.day DATED, NVL(b.SESSIONS, 0) SESSIONS,

> NVL(b.VISITORS, 0) VISITORS, NVL(b.VIEWS, 0) VIEWS FROM ( "

>   sqlOuterJoinPart2 = " ) b, days c WHERE b.DATED (" & chr(43) & ") =

c.day

> " _

>      & " AND b.client_site_id (" & chr(43) & ") = c.client_site_id " _

>      & " AND c.client_site_id  = ? " _

>      & " AND TO_DATE(c.day) <= TO_DATE(?) " _

>      & " AND TO_DATE(c.day) >= TO_DATE(?) "

>

>  Case 4

>   If input_end_dated = empty Then

>    sql_date = "SELECT TO_CHAR(TRUNC(TO_DATE(?), 'day'), 'DD-MON-YY') DATED

> FROM dual "

>    command.CommandText = sql_date

>    set result_set = command.Execute (,Array(input_dated))

>    If err.Number <> 0 Then

>     Error_Text = "Error:" & Err.Number & ":" & Err.Description

>     Response.AppendToLog Error_Text

>    End If

>    report_end_dated = result_set("DATED")

>    result_set.close

>    set result_set = nothing

>

>    sql_date = "SELECT TO_CHAR(TRUNC(TO_DATE(?) - 84, 'day'), 'DD-MON-YY')

> DATED FROM dual"

>    command.CommandText = sql_date

>    set result_set = command.Execute (,Array(input_dated))

>     If err.Number <> 0 Then

>     Error_Text = "Error:" & Err.Number & ":" & Err.Description

>     Response.AppendToLog Error_Text

>    End If

>    report_dated = result_set("DATED")

>    result_set.close

>    set result_set = nothing

>   End If

>

>   sqlSelect = " SELECT TO_CHAR(a.week, 'DD-MON-YY') DATED "

>   sqlDate = " AND (a.week) <= TO_DATE(?) " _

>           & " AND (a.week) >= TO_DATE(?) "

>   sqlOrder = " ORDER BY a.week "

>

>   x_axis_title = lang_week

>   If input_context = 1 Then

>    sqlFrom = " FROM segment_week a "

>   Else

>    sqlFrom = " FROM section_week a "

>   End If

>

>   sqlOuterJoinPart1 = " SELECT c.week DATED, NVL(b.SESSIONS, 0) SESSIONS,

> NVL(b.VISITORS, 0) VISITORS, NVL(b.VIEWS, 0) VIEWS FROM ( "

>   sqlOuterJoinPart2 = " ) b, weeks c WHERE b.DATED (" & chr(43) & ") =

> c.week " _

>         & " AND b.client_site_id (" & chr(43) & ") = c.client_site_id " _

>         & " AND c.client_site_id  = ? " _

>                                 & " AND TO_DATE(c.week) <= TO_DATE(?) " _

>                                 & " AND TO_DATE(c.week) >= TO_DATE(?) "

>  Case 5

>   If input_end_dated = empty Then

>    sql_date = "SELECT TO_CHAR(TRUNC(TO_DATE(?), 'month'), 'DD-MON-YY')

DATED

> FROM dual "

>    command.CommandText = sql_date

>    set result_set = command.Execute (,Array(input_dated))

>    If err.Number <> 0 Then

>     Error_Text = "Error:" & Err.Number & ":" & Err.Description

>     Response.AppendToLog Error_Text

>    End If

>    report_end_dated = result_set("DATED")

>    result_set.close

>    set result_set = nothing

>

>    sql_date = "SELECT TO_CHAR(ADD_MONTHS(TRUNC(TO_DATE(?), 'month'), -12),

> 'DD-MON-YY') DATED FROM dual "

>    command.CommandText = sql_date

>    set result_set = command.Execute (,Array(input_dated))

>    If err.Number <> 0 Then

>     Error_Text = "Error:" & Err.Number & ":" & Err.Description

>     Response.AppendToLog Error_Text

>    End If

>    report_dated = result_set("DATED")

>    result_set.close

>    set result_set = nothing

>   End If

>

>   x_axis_title = lang_month

>   sqlSelect = " SELECT TO_CHAR(a.month, 'DD-MON-YY') DATED "

>   sqlDate = " AND (a.month) <= TO_DATE(?) " _

>           & " AND (a.month) >= TO_DATE(?) "

>   sqlOrder = " ORDER BY a.month "

>

>   If input_context = 1 Then

>    sqlFrom = " FROM segment_month a "

>   Else

>    sqlFrom = " FROM section_month a "

>   End If

>

>   sqlOuterJoinPart1 = " SELECT c.month DATED, NVL(b.SESSIONS, 0) SESSIONS,

> NVL(b.VISITORS, 0) VISITORS, NVL(b.VIEWS, 0) VIEWS FROM ( "

>   sqlOuterJoinPart2 = " ) b, months c where b.DATED (" & chr(43) & ") =

> c.month " _

>         & " AND b.client_site_id (" & chr(43) & ") = c.client_site_id " _

>         & " AND c.client_site_id  = ? " _

>                                 & " AND TO_DATE(c.month) <= TO_DATE(?) " _

>                                 & " AND TO_DATE(c.month) >= TO_DATE(?) "

> End Select

>

> set command = nothing

> Set command = Server.CreateObject("ADODB.Command")

> command.ActiveConnection = connection

>

> sql_final = sqlOuterSelect _

>   & sqlOuterJoinPart1 _

>   & sqlSelect _

>      & " , a.sessions SESSIONS, a.unique_visitors VISITORS, a.page_views

> VIEWS, a.client_site_id client_site_id " _

>      & sqlFrom _

>      & " WHERE " _

>      & " a.client_site_id = ? " _

>   & " AND a.visitor_segment_id = ? " _

>      & sqlSection _

>      & sqlDate _

>      & sqlOrder _

>      & sqlOuterJoinPart2 _

>      & " ) " _

>      & sqlOuterOrder

>

> command.CommandText = sql_final

> If input_context = 1 Then

>  set result_set = command.Execute

>

(,Array(input_client_site_id,input_visitor_segment_id,report_end_dated,repor

> t_dated,input_client_site_id,report_end_dated,report_dated))

> Else

>  set result_set = command.Execute

>

(,Array(input_client_site_id,input_visitor_segment_id,input_section_id,repor

>

t_end_dated,report_dated,input_client_site_id,report_end_dated,report_dated)

> )

> End If

> If Err.Number <> 0 Then

>  Error_Text = "SQL:" & sql_final & "Error:" & Err.Number & ":" &

> Err.Description

>  Response.AppendToLog Error_Text

> End If

>

> Set chart = Server.CreateObject("ChartFX.WebServer")

>

> chart.RgbBk = custom_bg_color

> chart.Chart3D = custom_view_3d

> chart.Axis(AXIS_X).Title = x_axis_title

> chart.Axis(AXIS_Y).Title = y_axis_title

> chart.Axis(AXIS_Y2).Title = y2_axis_title

> chart.Axis(AXIS_Y).TitleFont.Bold=TRUE

> chart.Axis(AXIS_X).TitleFont.Bold=TRUE

> chart.Axis(AXIS_Y2).TitleFont.Bold=TRUE

>

> chart.OpenDataEx COD_VALUES,3,COD_UNKNOWN

> chart.CloseData COD_VALUES

> chart.Series(0).YAxis = AXIS_Y

> chart.Series(1).YAxis = AXIS_Y

> chart.Series(2).YAxis = AXIS_Y2

> chart.Series(2).Gallery = LINES

>

> chart.Axis(AXIS_X).Format = input_date_format

>

> chart.AdoResultset result_set

>

> chart.Axis(AXIS_X).LabelAngle = 90

> chart.Axis(AXIS_Y).Locale = lang_format

> chart.Axis(AXIS_Y).Format = AF_DATE

> chart.Axis(AXIS_Y).Locale = lang_format

> chart.Axis(AXIS_Y).Format = AF_NUMBER

> chart.Axis(AXIS_Y).Decimals = custom_num_decimal_places

> chart.Axis(AXIS_Y2).Locale = lang_format

> chart.Axis(AXIS_Y2).Format = AF_NUMBER

> chart.Axis(AXIS_Y2).Decimals = custom_num_decimal_places

> chart.Axis(AXIS_Y).Min = custom_min_value

> chart.Axis(AXIS_Y2).Min = custom_min_value

> chart.Axis(AXIS_X).TickMark = TS_NONE

> chart.Axis(AXIS_Y2).TickMark = TS_CROSS

> chart.Grid = CHART_HORZGRID

>

> chart.Border = custom_border

> chart.Axis(AXIS_Y).GridColor = custom_grid_color

> chart.Axis(AXIS_X).GridColor = custom_grid_color

> chart.Series(0).BorderColor = custom_border_color

> chart.Series(1).BorderColor = custom_border_color

> chart.Series(2).BorderColor = black

>

> chart.TopGap = custom_top_gap

>

> chart.SerLegBox = custom_legend_box

> Set SerLegBox = chart.SerLegBoxObj

> SerLegBox.BkColor = custom_legend_box_color

> chart.SerLegBoxObj.Docked = custom_legend_align

> chart.SerLegBoxObj.BorderStyle = custom_legend_box_style

>

> result_set.close

> set result_set = nothing

> set command = nothing

> connection.close

> Set connection = Nothing

>

> chart.GetHtmlData custom_x_chart_size,custom_y_chart_size,"Image"

> %>

>

>

>
Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...