asked on

Need to write this in asp.net vb..net

Function Main
      Dim strTemp, strSQL, strFilterList, varTemp, rstTemp, lngTemp,varParameters
      Dim strURLReport
      Dim strFileName, strTimeFlag
      Dim FS, i, strUniqueID
      dim blnNoResult
      dim strTemplate, strReportName, strParameters, strReportFormat

      Dim rstMain

      strReportFormat = ".PDF"

      'Log file name
      mstrReportFilePath = GetCDPath
      strLogFIle = mstrReportFilePath & "\" & TEMP_FOLDER & LOG_FILE & ".log"
      '-----------

      if mblnAutoRun then
            strStringtoAppend = "----------------------------------------------------------------------------------------" & vbCrLf
            lngErr = AppendStringToFile(strStringToAppend, strLogFile)
            strStringtoAppend = PAGENAME & "-" & METHODNAME & " | " & strActiveDivisionName & " | PROCESS STARTED"
            lngErr = AppendStringToLog(strStringToAppend, strLogFile)
      end if

      strParamConnection = Replace(CONN_STRING, REPLACE_SERVER_NAME, APP_SERVER_NAME)
      strParamConnection = Replace(strParamConnection, REPLACE_DATABASE_NAME, DIVISION_DB_NAME)
      strParamConnection = Replace(strParamConnection, REPLACE_USERNAME, IIS_USERNAME)
      strParamConnection = Replace(strParamConnection, REPLACE_PASSWORD, IIS_PASSWORD)

      strActiveDBConnection = Replace(CONN_STRING, REPLACE_SERVER_NAME, APP_SERVER_NAME)
      strActiveDBConnection = Replace(strActiveDBConnection, REPLACE_DATABASE_NAME, APP_DB_NAME)
      strActiveDBConnection = Replace(strActiveDBConnection, REPLACE_USERNAME, IIS_USERNAME)
      strActiveDBConnection = Replace(strActiveDBConnection, REPLACE_PASSWORD, IIS_PASSWORD)

      strActiveIHConnection = Replace(CONN_STRING, REPLACE_SERVER_NAME, strActiveIHServerName)
      strActiveIHConnection = Replace(strActiveIHConnection, REPLACE_DATABASE_NAME, strActiveIHDBName)
      strActiveIHConnection = Replace(strActiveIHConnection, REPLACE_USERNAME, IIS_USERNAME)
      strActiveIHConnection = Replace(strActiveIHConnection, REPLACE_PASSWORD, IIS_PASSWORD)

      strActiveMWConnection = Replace(CONN_STRING, REPLACE_SERVER_NAME, strActiveMWServerName)
      strActiveMWConnection = Replace(strActiveMWConnection, REPLACE_DATABASE_NAME, strActiveMWDBName)
      strActiveMWConnection = Replace(strActiveMWConnection, REPLACE_USERNAME, IIS_USERNAME)
      strActiveMWConnection = Replace(strActiveMWConnection, REPLACE_PASSWORD, IIS_PASSWORD)

      strActiveMAConnection = Replace(CONN_STRING, REPLACE_SERVER_NAME, strActiveMAServerName)
      strActiveMAConnection = Replace(strActiveMAConnection, REPLACE_DATABASE_NAME, strActiveMADBName)
      strActiveMAConnection = Replace(strActiveMAConnection, REPLACE_USERNAME, IIS_USERNAME)
      strActiveMAConnection = Replace(strActiveMAConnection, REPLACE_PASSWORD, IIS_PASSWORD)

      strActiveAWConnection = Replace(CONN_STRING, REPLACE_SERVER_NAME, strActiveAWServerName)
      strActiveAWConnection = Replace(strActiveAWConnection, REPLACE_DATABASE_NAME, strActiveAWDBName)
      strActiveAWConnection = Replace(strActiveAWConnection, REPLACE_USERNAME, IIS_USERNAME)
      strActiveAWConnection = Replace(strActiveAWConnection, REPLACE_PASSWORD, IIS_PASSWORD)

      '-----------------------------------------------------------------
      'Get last completed week ending date
      if mblnAutoRun then
            'strWeekDate = FormatDateTime(DateAdd("ww", -1, DateAdd("d", 7 - DatePart("w", Now), Now)), vbShortDate)
            strWeekDate = FormatDateTime(DateAdd("ww", 0, DateAdd("d", 7 - DatePart("w", Now-1), Now-1)), vbShortDate)

            strSQL = SQL_GET_SELECTED_WEEK

            strSQL = replace(strSQL,REPLACE_WEEK_DATE,strWeekDate)

            Set rstTemp = ExecSQL(strActiveMWConnection, strSQL, 900, true)

            strCurrYear = rstTemp("merch_year").value
            strCurrPeriod = rstTemp("merch_period").value

            mstrYearPeriod = strCurrYear & RIGHT("00" & strCurrPeriod,2)
      else
            strSQL = SQL_GET_CURRENT_YEAR_PERIOD

            strSQL = replace(strSQL,REPLACE_YEAR_PERIOD,mstrYearPeriod)

            Set rstTemp = ExecSQL(strActiveMWConnection, strSQL, 900, true)

            strWeekDate = rstTemp("WEEK_DATE").value

            strCurrYear = rstTemp("merch_year").value
            strCurrPeriod = rstTemp("merch_period").value
      end if

      if mblnAutoRun then
            strStringtoAppend = PAGENAME & "-" & METHODNAME & " | RUN WEEK: " & strWeekDate
            AppendStringToLog strStringToAppend, strLogFile
      end if

      '-----------------------------------------------------------------

      strTemp = GetCDPath() & "\" & SQL_FILE_SELECT_PERIOD

      strSQL = ReadTextFile(strTemp)

      strSQL = replace(strSQL,REPLACE_WEEK_DATE,strWeekDate)

      Set rstTemp = ExecSQL(strActiveMWConnection, strSQL, 900, true)

      rstTemp.filter = "calendar_year_code = " & strCurrYear & " and period_code = " & strCurrPeriod

      if not rstTemp.eof then
            mstrFromDate = rstTemp("period_start_date").value
            mstrToDate = rstTemp("period_end_date").value
      end if
      '----------GLOBAL_APPS-----
      Set cnnGlobalApps = CreateObject("ADODB.Connection")
      cnnGlobalApps.Open strActiveDBConnection

      if mblnAutoRun then
            strStringtoAppend = PAGENAME & "-" & METHODNAME & " | DROP TEMP TABLES - GLOBAL_APPS"
            lngErr = AppendStringToLog(strStringToAppend, strLogFile)
      end if

      'Drop temporary tables
      call DropTmpTable(cnnGlobalApps,1)

      if mblnAutoRun then
            strStringtoAppend = PAGENAME & "-" & METHODNAME & " | CREATE TEMP TABLES - GLOBAL_APPS"
            lngErr = AppendStringToLog(strStringToAppend, strLogFile)
      end if

      '-----CREATE TEMPORARY TABLE-----
      strTemp = GetCDPath() & "\" & SQL_FILE_CREATE_TEMP_TABLE
      strSQL = ReadTextFile(strTemp)

      cnnGlobalApps.Execute strSQL


      '----------MW-----
      Set cnnMW = CreateObject("ADODB.Connection")
      cnnMW.Open strActiveMWConnection

      if mblnAutoRun then
            strStringtoAppend = PAGENAME & "-" & METHODNAME & " | DROP TEMP TABLES - MW"
            lngErr = AppendStringToLog(strStringToAppend, strLogFile)
      end if

      'Drop temporary tables
      call DropTmpTable(cnnMW,2)

      if mblnAutoRun then
            strStringtoAppend = PAGENAME & "-" & METHODNAME & " | CREATE TEMP TABLES - MW"
            lngErr = AppendStringToLog(strStringToAppend, strLogFile)
      end if

      '-----CREATE TEMPORARY TABLE-----
      strTemp = GetCDPath() & "\" & SQL_FILE_CREATE_TEMP_TABLE_MW
      strSQL = ReadTextFile(strTemp)

      cnnMW.Execute strSQL

      '-------Main SQL
      if mblnAutoRun then
            strStringtoAppend = PAGENAME & "-" & METHODNAME & " | EXTRACT DATA"
            lngErr = AppendStringToLog(strStringToAppend, strLogFile)
      end if

      '-----Extract SL Components from IH to MW
      strTemp = GetCDPath() & "\" & SQL_FILE_MW_SL_COMPONENT
      strSQL = ReadTextFile(strTemp)

      Set rstTemp = ExecSQL(strActiveIHConnection, strSQL, 900, true)

      if not rstTemp.eof then
            'Insert into GlobalApps DB
            lngTemp = TransferDataBetweenServer("", cnnMW, "", TMP_TBL_MW_SL_COMPONENT, "", rstTemp, , False, 120)
            'lngTemp = TransferDataBetweenServer("", cnnMW, "", "TMP_MW_SL_COMPONENT" , "", rstTemp, , False, 120)
      end if
      '----------------------------------------------------
      strTemp = GetCDPath() & "\" & SQL_FILE_LOCATION_AW
      strSQL = ReadTextFile(strTemp)

      strSQL = replace(strSQL,REPLACE_BEGIN_DATE,mstrFromDate)
      strSQL = replace(strSQL,REPLACE_END_DATE,mstrToDate)

      if mblnAutoRun then      'Add by Ivan Guentchev #0001610
            strStringtoAppend = PAGENAME & "-" & METHODNAME & " | SQL_FILE_LOCATION_AW"
            lngErr = AppendStringToLog(strStringToAppend, strLogFile)
      end if

      Set rstTemp = ExecSQL(strActiveAWConnection, strSQL, 1800, true)

      if not rstTemp.eof then
            'Insert into GlobalApps DB
            lngTemp = TransferDataBetweenServer("", cnnGlobalApps, "", TMP_TBL_AW_DATA, "", rstTemp, , False, 120)
            'lngTemp = TransferDataBetweenServer("", cnnGlobalApps, "", "TMP_AW_DATA" , "", rstTemp, , False, 120)
      end if

      '-------Main SQL
      strTemp = GetCDPath() & "\" & SQL_FILE_LOCATION_MA
      strSQL = ReadTextFile(strTemp)

      strSQL = replace(strSQL,REPLACE_BEGIN_DATE,mstrFromDate)
      strSQL = replace(strSQL,REPLACE_END_DATE,mstrToDate)
      strSQL = replace(strSQL,REPLACE_YEAR_PERIOD,mstrYearPeriod)


      if mblnAutoRun then      'Add by Ivan Guentchev #0001610
            strStringtoAppend = PAGENAME & "-" & METHODNAME & " | SQL_FILE_LOCATION_MA"
            lngErr = AppendStringToLog(strStringToAppend, strLogFile)
      end if

      Set rstTemp = ExecSQL(strActiveMAConnection, strSQL, 1800, true)

      if not rstTemp.eof then
            'Insert into GlobalApps DB
            lngTemp = TransferDataBetweenServer("", cnnGlobalApps, "", TMP_TBL_MA_DATA, "", rstTemp, , False, 120)
            'lngTemp = TransferDataBetweenServer("", cnnGlobalApps, "", "TMP_MA_DATA" , "", rstTemp, , False, 120)
      end if
      '------------------------------------------
      '-------Main SQL
      strTemp = GetCDPath() & "\" & SQL_FILE_LOCATION_MW
      strSQL = ReadTextFile(strTemp)

      strSQL = replace(strSQL,REPLACE_DIVISION_LEVEL_ID,DivisionLevel)
      strSQL = replace(strSQL,REPLACE_BEGIN_DATE,mstrFromDate)
      strSQL = replace(strSQL,REPLACE_END_DATE,mstrToDate)
      strSQL = replace(strSQL,REPLACE_YEAR_PERIOD,mstrYearPeriod)

      if mblnAutoRun then      'Add by Ivan Guentchev #0001610
            strStringtoAppend = PAGENAME & "-" & METHODNAME & " | SQL_FILE_LOCATION_MW"
            lngErr = AppendStringToLog(strStringToAppend, strLogFile)
      end if

      Set rstTemp = ExecSQL(cnnMW, strSQL, 1800, true)


      if not rstTemp.eof then
            'Insert into GlobalApps DB
            lngTemp = TransferDataBetweenServer("", cnnGlobalApps, "", TMP_TBL_MW_DATA, "", rstTemp, , False, 120)
            'lngTemp = TransferDataBetweenServer("", cnnGlobalApps, "", "TMP_MW_DATA" , "", rstTemp, , False, 120)
      end if

      '-------------------------------------
      '-------Main SQL
      strTemp = GetCDPath() & "\" & SQL_FILE_MAIN
      strSQL = ReadTextFile(strTemp)

      if mblnAutoRun then      'Add by Ivan Guentchev #0001610
            strStringtoAppend = PAGENAME & "-" & METHODNAME & " | FINAL QUERY"
            lngErr = AppendStringToLog(strStringToAppend, strLogFile)
      end if

      Set rstMain = ExecSQL(cnnGlobalApps, strSQL, 1800, true)


      If Not (rstMain Is Nothing) Then
            if rstMain.recordcount = 0 then
                  if strLanguage = "F" then
                        strMessage = "Aucun résultat trouvé pour les critères sélectionné."
                  else
                        strMessage = "No data found for the selected criteria."
                  end if
                  if mblnAutoRun then
                        strStringtoAppend = PAGENAME & "-" & METHODNAME & " | "      & strMessage
                        lngErr = AppendStringToLog(strStringToAppend, strLogFile)
                        strStringtoAppend = PAGENAME & "-" & METHODNAME & " | PROCESS ENDED"
                        AppendStringToLog strStringToAppend, strLogFile
                        strStringtoAppend = "----------------------------------------------------------------------------------------" & vbCrLf
                        AppendStringToFile strStringToAppend, strLogFile
                  end if
            else

                  Call GenerateReport(rstMain, strReportFormat)
                  Server.ScriptTimeout = iScriptTimeout

            end if
      end if

      'Drop temporary tables
      call DropTmpTable(cnnGlobalApps,1)

      'Close connection
      if isobject(cnnGlobalApps) then
            if not cnnGlobalApps is nothing then
                  if cnnGlobalApps.State <> adStateClosed then
                        cnnGlobalApps.Close
                  end if
                  Set cnnGlobalApps = Nothing
            end if
      end if

      'Drop temporary tables
      call DropTmpTable(cnnMW,2)

      'Close connection
      if isobject(cnnMW) then
            if not cnnMW is nothing then
                  if cnnMW.State <> adStateClosed then
                        cnnMW.Close
                  end if
                  Set cnnMW = Nothing
            end if
      end if

      Server.ScriptTimeout = iScriptTimeout      'reset to default timeout

      if mblnAutoRun then
            Response.Write "<script LANGUAGE='javascript'>"
            Response.Write "window.history.go(-2);" 'must put this because if not the report is processed another time!!
            Response.Write "window.opener = '';"
            Response.Write "window.close();"
            Response.Write "</script>"
      else
            Response.Write "<script LANGUAGE='javascript'>"
            Response.Write "alert('" & strMessage & "');"
            Response.Write "history.back();"
            Response.Write "</script>"
      end if
End function

ASKER CERTIFIED SOLUTION

Elvio Lujan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mathieu_cupryk

ASKER

Just this part

Function Main
Dim strTemp, strSQL, strFilterList, varTemp, rstTemp, lngTemp,varParameters
Dim strURLReport
Dim strFileName, strTimeFlag
Dim FS, i, strUniqueID
dim blnNoResult
dim strTemplate, strReportName, strParameters, strReportFormat

Dim rstMain

strReportFormat = ".PDF"

'Log file name
mstrReportFilePath = GetCDPath
strLogFIle = mstrReportFilePath & "\" & TEMP_FOLDER & LOG_FILE & ".log"
'-----------

if mblnAutoRun then
strStringtoAppend = "----------------------------------------------------------------------------------------" & vbCrLf
lngErr = AppendStringToFile(strStringToAppend, strLogFile)
strStringtoAppend = PAGENAME & "-" & METHODNAME & " | " & strActiveDivisionName & " | PROCESS STARTED"
lngErr = AppendStringToLog(strStringToAppend, strLogFile)
end if

strParamConnection = Replace(CONN_STRING, REPLACE_SERVER_NAME, APP_SERVER_NAME)
strParamConnection = Replace(strParamConnection, REPLACE_DATABASE_NAME, DIVISION_DB_NAME)
strParamConnection = Replace(strParamConnection, REPLACE_USERNAME, IIS_USERNAME)
strParamConnection = Replace(strParamConnection, REPLACE_PASSWORD, IIS_PASSWORD)