Extract sql script out of asp page

• shas-435744

  SSC Enthusiast

  Points: 179

  More actions

  May 30, 2009 at 6:07 am

  #132387

  Hi,

  I have been struggling to extract an sp out of this.

  Please help me in Making a store proc out of this asp code.

  <% strIncludeJavaScript = "../../vors.js"<%
  Server.ScriptTimeout = 5000
  if len(request("ConnName"))> 0 then

  strConnName = request("ConnName")

  else

  strConnName = session("CnnStrName")

  end if

  if len(request("pid"))> 0 then

  intPracticeID = request("pid")

  else

  intPracticeID = session("practiceID")

  end if

  'strConnName = session("CnnStrName")

  'intPracticeID = session("PracticeId")

  strDate = request("reportDate")

  if not isdate(strDate) then

  strDate = date()

  end if

  if not request("p") = "print" then

  strHTMLBodyOnload = "reportTypeClick();"

  printForm()

  else

  if cbool(Request("include_zero_balance")) then

  strZeroBalance = ""

  strZeroBalanceFilter = " and " & _

  "(" & vbnewline & _

  "round(isnull(t1.amount_current,0), 2)!=0 or " & vbnewline & _

  "round(isnull(t2.amount_31_60,0), 2) !=0 or " & vbnewline & _

  "round(isnull(t3.amount_61_90,0), 2) !=0 or " & vbnewline & _

  "round(isnull(t4.amount_91_120,0), 2) !=0 or " & vbnewline & _

  "round(isnull(t5.amount_121,0), 2) !=0 or " & vbnewline & _

  "round(isnull(t6.unapplied,0), 2) !=0 " & vbnewline & _

  ") "

  else

  strZeroBalance1 = " and isnull(isBalanced, 0) = 0 "

  strZeroBalanceFilter = " and " & _

  "(" & vbnewline & _

  "round(isnull(t1.amount_current,0), 2) + " & vbnewline & _

  "round(isnull(t2.amount_31_60,0), 2) + " & vbnewline & _

  "round(isnull(t3.amount_61_90,0), 2) + " & vbnewline & _

  "round(isnull(t4.amount_91_120,0), 2) + " & vbnewline & _

  "round(isnull(t5.amount_121,0), 2) + " & vbnewline & _

  "round(isnull(t6.unapplied,0), 2) !=0 " & vbnewline & _

  ") "

  end if

  strGrouping = ""

  if not request("group") = "none" then

  strGrouping = request("group")

  strGroupingLabel = replace(lcase(strGrouping), "id", "")

  else

  strGroupingLabel = "Billing Category"

  end if

  strFilter = ""

  arG = array("providerID", "officeID", "billingCategoryID")

  for each item in arG

  if not request(item) = "-1" then

  if len(strFilter) > 0 then

  strFilter = strFilter & "|"

  end if

  strFilter = strFilter & item & "," & request(item)

  strFilterLabel = addOptDisplayValue(strFilterLabel, getFilterValue(item, request(item), strConnName), ", ")

  end if

  next

  if len(trim(strFilterLabel))>0 then

  strFilterLabel = "
  Filters " & strFilterLabel

  end if

  strAddFieldsLabel = ""

  if request("reportType") = "detail" then

  strAddFields = "PatientName"

  if request("GuarantorName") = 1 then

  strAddFields = strAddFields & "|GuarantorName"

  strAddFieldsLabel = addOptDisplayValue(strAddFieldsLabel, "Guarantor Name", ", ")

  end if

  if request("phone") = 1 then

  strAddFields = strAddFields & "|HomePhone|WorkPhone"

  strAddFieldsLabel = addOptDisplayValue(strAddFieldsLabel, "Phone Numbers", ", ")

  end if

  if request("LastPayment") = 1 then

  strAddFields = strAddFields & "|LastPaymentType|LastPaymentDate"

  strAddFieldsLabel = addOptDisplayValue(strAddFieldsLabel, "Last Payment", ", ")

  end if

  if request("SendStatement") = 1 then

  strAddFields = strAddFields & "|SendStmt"

  strAddFieldsLabel = addOptDisplayValue(strAddFieldsLabel, "Send Statement", ", ")

  end if

  if request("InColl") = 1 then

  strAddFields = strAddFields & "|InColl"

  strAddFieldsLabel = addOptDisplayValue(strAddFieldsLabel, "In Collections", ", ")

  end if

  if request("DunMsg") = 1 then

  strAddFields = strAddFields & "|DunMsg"

  strAddFieldsLabel = addOptDisplayValue(strAddFieldsLabel, "Dunning Message", ", ")

  end if

  end if

  bln00= cbool(request("aging_current") = "1")

  bln30= cbool(request("aging_30") = "1")

  bln60= cbool(request("aging_60") = "1")

  bln90= cbool(request("aging_90") = "1")

  bln120= cbool(request("aging_120") = "1")

  blnUnApp = cbool(request("unapplied") = "1")

  '==========================================

  'if session("uid") = 156 then

  'Response.Write "

  125"

  'Response.end

  'end if

  '===========================================

  if not request("reportType") = "summary" then

  strBucketsLabel = ""

  if bln00 then

  strBucketsLabel = addOptDisplayValue(strBucketsLabel, "Current", ", " )

  end if

  if bln30 then

  strBucketsLabel = addOptDisplayValue(strBucketsLabel, "31 - 60", ", " )

  end if

  if bln60 then

  strBucketsLabel = addOptDisplayValue(strBucketsLabel, "61 -90", ", " )

  end if

  if bln90 then

  strBucketsLabel = addOptDisplayValue(strBucketsLabel, "91 - 120", ", " )

  end if

  if bln120 then

  strBucketsLabel = addOptDisplayValue(strBucketsLabel, "120 and up", ", " )

  end if

  if blnUnApp then

  strBucketsLabel = addOptDisplayValue(strBucketsLabel, "UnApplied", ", " )

  end if

  strBucketsLabel = " Aging Buckets: " & strBucketsLabel

  strBanner = "

  " & getPracticeName(intPracticeID, strConnName) & "
  ble - " & ucFirst(request("reportType")) & "

  Group by " & ucfirst(strGroupingLabel) & ": " & strFilterLabel & "
  " & strBucketsLabel

  if len(trim(strAddFieldsLabel))>0 then

  strBanner = strBanner & "
  Additional Fields: " & strAddFieldsLabel

  end if

  strSqual = getARSQL(request("reportType") = "detail", strGrouping, strFilter, strAddFields, bln00, bln30, bln60, bln90, bln120, blnUnApp, strZeroBalance)

  arSqual = split(strSqual, "|||")

  if false and (session("strLIId") = "programmer") then

  print "

  " & strSqual & "

  "

  die

  end if

  'call getStaticRecordSet(arSqual(3), strConnName)

  call getStaticRecordSet(arSqual(0), strConnName)

  call getStaticRecordSet(arSqual(1), strConnName)

  set rstReport = getStaticRecordSet(arSqual(2), strConnName)

  if rstReport.eof then

  call getStaticRecordSet(arSqual(3), strConnName)

  print openHTML()

  print strBanner

  print "

  "

  response.write "Report criteria resulted in no results."

  print closeHTML()

  response.end

  end if

  select case lcase(strGrouping)

  case "providerid"

  strG1 = "providerID"

  strG2 = "providerName"

  case "officeid"

  strG1 = "officeID"

  strG2 = "officeName"

  case else

  if request("reportType") = "detail" then

  strG1 = "Code"

  strG2 = "Description"

  else

  strG1 = ""

  strG2 = ""

  end if

  end select

  printARReport rstReport, strG1, strG2, strAddFields, strBanner

  call getStaticRecordSet(arSqual(3), strConnName)

  else

  'strSqual = getARSQL(request("reportType") = "detail", "", "", "")

  call printARSummaryReport(intPracticeID, request("reportDate"), strConnName, strZeroBalance)

  end if

  %>   <%
  end if

  function getARSQL(blnShowPatient, strGroupBy, strWhere, strAddFields, bln00, bln30, bln60, bln90, bln120, blnUnApp, strZeroBalance)
  strSQLEnd = vbnewline & vbnewline & "drop table #table1" & vbnewline & "drop table #table2" & vbnewline & "drop table #table3" & vbnewline & "drop table #table4" & vbnewline & "drop table #table5" & vbnewline & "drop table #table6" & vbnewline & "drop table #table7"

  strSQLTemp1 = ""
  strSQLTemp2 = ""
  strSQLTemp3 = ""
  strSQLTemp4 = ""
  strSQLTemp5 = ""
  strSQLTemp6 = ""
  strSQLTemp7 = ""

  if len(strGroupBy)>0 then

  strGroupBy = trim(lcase(strGroupBy))

  strSQLOption_1 = ", " & strGroupBy

  strSQLOption_2 = ", g." & strGroupBy

  strSQLOption_3 = strGroupBy

  strSQLOption_4 = "g." & strGroupBy

  if strGroupBy = "providerid" then

  strSQLOption_5 = ", p.providerName"

  strSQLOption_6 = " left outer join " & strConnName & "..vwProviders p on p.providerID= g.providerID "

  else

  strSQLOption_5 = ", o.officeName"

  strSQLOption_6 = " left outer join " & strConnName & "..tblOffice o on o.officeID = g.officeID "

  end if

  end if

  if blnShowPatient then

  strPatient1 = ", g.patientID, pp.PatientName "

  strPatient2 = ", patientID"

  strPatient3 = "patientID"

  strPatient4 = " left outer join vwPatientPerson pp on pp.patientID = g.patientID "

  if cbool(instr(strAddFields, "GuarantorName")) then

  strPatient1 = strPatient1 & ", gp.GuarantorName"

  strPatient4 = strPatient4 &" left outer join " & strConnName & "..vwGuarantorPerson gp on gp.patientID = g.patientID "

  end if

  if cbool(instr(strAddFields, "Phone")) then

  strPatient1 = strPatient1 & ", pr.HomePhone, pr.WorkPhone"

  strPatient4 = strPatient4 &" left outer join " & strConnName & "..tblPersons pr on pr.personID = pp.personID "

  end if

  if cbool(instr(strAddFields, "SendStmt")) then

  strPatient1 = strPatient1 & ", case when pbc.SendStatement = 0 then 'No' else 'Yes' end SendStmt"

  strPatient4 = strPatient4 &" left outer join " & strConnName & "..tblPatientBillingCategories pbc on pbc.patientID = g.patientID "

  end if

  if cbool(instr(strAddFields, "InColl")) then

  strPatient1 = strPatient1 & ", case when pbc.InCollections = 0 then 'No' else 'Yes' end InColl"

  if not cbool(instr(strAddFields, "SendStmt")) then

  strPatient4 = strPatient4 &" left outer join " & strConnName & "..tblPatientBillingCategories pbc on pbc.patientID = g.patientID "

  end if

  end if

  if cbool(instr(strAddFields, "DunMsg")) then

  strPatient1 = strPatient1 & ", case when pbc.PrintDunningMessage = 0 then 'No' else 'Yes' end DunMsg"

  if not (cbool(instr(strAddFields, "SendStmt")) or cbool(instr(strAddFields, "InColl")) )then

  strPatient4 = strPatient4 &" left outer join " & strConnName & "..tblPatientBillingCategories pbc on pbc.patientID = g.patientID "

  end if

  end if

  if cbool(instr(strAddFields, "LastPayment")) then

  strPatient1 = strPatient1 & ", case when source is null then null else case when source = 'P' then 'Pat'else'Ins'end end LastPaymentType, case when source is null then null else convert(varchar(2), datepart(m, paymentDate)) + '/' + convert(varchar(2), datepart(d, paymentDate)) + '/' + convert(varchar(4), datepart(yyyy, paymentDate)) end LastPaymentDate"

  strPatient4 = strPatient4 & " left outer join " & strConnName & "..vwPatientLastPayment plp on plp.patientID = g.patientID "

  end if

  end if

  strWhere1 = ""

  if len(strWhere)>0 then

  arWhere = split(strWhere, "|")

  for each item in arWhere

  arWhat = split(item, ",")

  if arWhat(0) = "billingCategoryID" then

  strArg = "billingCategoryID"

  elseif arWhat(0) = "providerID" then

  strArg = "providerID"

  elseif arWhat(0) = "officeID" then

  strArg = "officeID"

  end if

  if len(strWhere1)>0 then

  strWhere1 = strWhere1 & " and "

  end if

  strWhere1 = strWhere1 & strArg & " = " & arWhat(1)

  next

  end if

  strOrderBy = ""

  strOrderBy = strOrderBy & strSQLOption_4

  strBC1 = ", isnull(bc.Code, 0) Code, bc.description"

  strBC2 = " left outer join " & strConnName & "..tblBillingCategories bc on bc.billingCategoryID = g.billingCategoryID "

  strSqual = _

  "select " & vbnewline & _

  "g.billingCategoryID" & strBC1 & strSQLOption_2 & strSQLOption_5 & strPatient1 & ", " & vbnewline & _

  "round(isnull(t1.amount_current,0), 2) amount_current," & vbnewline & _

  "round(isnull(t2.amount_31_60,0), 2) amount_31_60," & vbnewline & _

  "round(isnull(t3.amount_61_90,0), 2) amount_61_90," & vbnewline & _

  "round(isnull(t4.amount_91_120,0), 2) amount_91_120, " & vbnewline & _

  "round(isnull(t5.amount_121,0), 2) amount_121, " & vbnewline & _

  "round(isnull(t6.unapplied,0), 2) unapplied" & vbnewline & _

  "from " & vbnewline & _

  "(" & vbnewline & _

  "select * from #table1" & ")g" & vbnewline

  strSQLTemp1 = " insert into #table1 select billingCategoryID " & replace(strSQLOption_1, "providerid", " isnull(providerid, 0) providerid") & strPatient2 & " from " & strConnName & "_report..arChargesPayments " & vbnewline & _

  "where practiceID = " & intPracticeID & " " & vbnewline

  if len(strWhere1)>0 then

  strSQLTemp1 = strSQLTemp1 & _

  " and " & strWhere1

  end if

  strSQLTemp1 = strSQLTemp1 & _

  "group by billingCategoryID" & strSQLOption_1 & strPatient2 & vbnewline

  strSqual = strSqual & _

  "left outer join " & vbnewline & _

  "(" & vbnewline & _

  "select * from #table2 " & ")t1 on t1.billingCategoryID = g.billingCategoryID" & vbnewline

  strSQLTemp2 = " insert into #table2select " & vbnewline & _

  "billingCategoryID" & replace(strSQLOption_1, "providerid", " isnull(providerid, 0) providerid") & strPatient2 & ", " & vbnewline & _

  "sum(chargeAmount) amount_current" & vbnewline & _

  "from " & vbnewline & _

  "" & strConnName & "_report..arChargesPayments " & vbnewline & _

  "where procDate >= dateadd(d,-30, '" & strDate & "') " & strZeroBalance & " and rawDate<='" & strDate & "' " & vbnewline
  if len(strWhere1)>0 then

  strSQLTemp2 = strSQLTemp2 & _

  " and " & strWhere1

  end if

  strSQLTemp2 = strSQLTemp2 & _

  "and practiceID = " & intPracticeID & "" & vbnewline & _

  "and type != 'unapppay' " & vbnewline & _

  "group by billingCategoryID" & strSQLOption_1 & strPatient2 & vbnewline

  if len(strSQLOption_3)>0 then

  strSqual = strSqual & " and t1." & strSQLOption_3 & " = g." & strSQLOption_3 & " " & vbnewline

  end if

  if len(strPatient3)>0 then

  strSqual = strSqual & " and t1." & strPatient3 & " = g." & strPatient3 & " " & vbnewline

  end if

  strSqual = strSqual & _

  "left outer join " & vbnewline & _

  "(" & vbnewline & _

  "select * from #table3 " & ")t2 on t2.billingCategoryID = g.billingCategoryID" & vbnewline

  strSQLTemp3 = " insert into #table3 select " & vbnewline & _

  "billingCategoryID" & replace(strSQLOption_1, "providerid", " isnull(providerid, 0) providerid") & strPatient2 & ", " & vbnewline & _

  "sum(chargeAmount) amount_31_60" & vbnewline & _

  " from " & vbnewline & _

  "" & strConnName & "_report..arChargesPayments " & vbnewline & _

  "where procDate < dateadd(d,-30, '" & strDate & "') " & strZeroBalance & " and procDate >= dateadd(d, -60, '" & strDate & "') and rawDate<='" & strDate & "' " & vbnewline & _
  "and practiceID = " & intPracticeID & "" & vbnewline & _
  "and type != 'unapppay' " & vbnewline
  if len(strWhere1)>0 then

  strSQLTemp3 = strSQLTemp3 & _

  " and " & strWhere1

  end if

  strSQLTemp3 = strSQLTemp3 & _

  "group by billingCategoryID" & strSQLOption_1 & strPatient2 & vbnewline

  if len(strSQLOption_3)>0 then

  strSqual = strSqual & " and t2." & strSQLOption_3 & " = g." & strSQLOption_3 & " " & vbnewline

  end if

  if len(strPatient3)>0 then

  strSqual = strSqual & " and t2." & strPatient3 & " = g." & strPatient3 & " " & vbnewline

  end if

  strSqual = strSqual & _

  "left outer join " & vbnewline & _

  "(" & vbnewline & _

  "select * from #table4" & ")t3 on t3.billingCategoryID = g.billingCategoryID" & vbnewline

  strSQLTemp4 = " insert into #table4 select " & vbnewline & _

  "billingCategoryID" & replace(strSQLOption_1, "providerid", " isnull(providerid, 0) providerid") & strPatient2 & ", " & vbnewline & _

  "sum(chargeAmount) amount_61_90" & vbnewline & _

  " from " & vbnewline & _

  "" & strConnName & "_report..arChargesPayments " & vbnewline & _

  "where procDate < dateadd(d,-60, '" & strDate & "') " & strZeroBalance & " and procDate >= dateadd(d, -90, '" & strDate & "') and rawDate<='" & strDate & "' " & vbnewline & _
  "and practiceID = " & intPracticeID & "" & vbnewline & _
  "and type != 'unapppay' " & vbnewline
  if len(strWhere1)>0 then

  strSQLTemp4 = strSQLTemp4 & _

  " and " & strWhere1

  end if

  strSQLTemp4 = strSQLTemp4 & _

  "group by billingCategoryID" & strSQLOption_1 & strPatient2 & vbnewline

  if len(strSQLOption_3)>0 then

  strSqual = strSqual & " and t3." & strSQLOption_3 & " = g." & strSQLOption_3 & " " & vbnewline

  end if

  if len(strPatient3)>0 then

  strSqual = strSqual & " and t3." & strPatient3 & " = g." & strPatient3 & " " & vbnewline

  end if

  strSqual = strSqual & _

  "left outer join " & vbnewline & _

  "(" & vbnewline & _

  "select * from #table5 " & ")t4 on t4.billingCategoryID = g.billingCategoryID" & vbnewline

  strSQLTemp5 = " insert into #table5 select " & vbnewline & _

  "billingCategoryID" & replace(strSQLOption_1, "providerid", " isnull(providerid, 0) providerid") & strPatient2 & ", " & vbnewline & _

  "sum(chargeAmount) amount_91_120" & vbnewline & _

  "from " & vbnewline & _

  "" & strConnName & "_report..arChargesPayments " & vbnewline & _

  "where procDate < dateadd(d,-90, '" & strDate & "') " & strZeroBalance & " and procDate >= dateadd(d, -120, '" & strDate & "') and rawDate<='" & strDate & "' " & vbnewline & _
  "and practiceID = " & intPracticeID & "" & vbnewline & _
  "and type != 'unapppay' " & vbnewline
  if len(strWhere1)>0 then

  strSQLTemp5 = strSQLTemp5 & _

  " and " & strWhere1

  end if

  strSQLTemp5 = strSQLTemp5 & _

  "group by billingCategoryID" & strSQLOption_1 & strPatient2 & vbnewline

  if len(strSQLOption_3)>0 then

  strSqual = strSqual & " and t4." & strSQLOption_3 & " = g." & strSQLOption_3 & " " & vbnewline

  end if

  if len(strPatient3)>0 then

  strSqual = strSqual & " and t4." & strPatient3 & " = g." & strPatient3 & " " & vbnewline

  end if

  strSqual = strSqual & _

  "left outer join " & vbnewline & _

  "(" & vbnewline & _

  "select * from #table6 " & ")t5 on t5.billingCategoryID = g.billingCategoryID" & vbnewline

  strSQLTemp6 = " insert into #table6 select " & vbnewline & _

  "billingCategoryID" & replace(strSQLOption_1, "providerid", " isnull(providerid, 0) providerid") & strPatient2 & ", " & vbnewline & _

  "sum(chargeAmount) amount_121" & vbnewline & _

  "from " & vbnewline & _

  "" & strConnName & "_report..arChargesPayments " & vbnewline & _

  "where procDate < dateadd(d, -120, '" & strDate & "') " & strZeroBalance & " and rawDate<='" & strDate & "' " & vbnewline & _
  "and practiceID = " & intPracticeID & "" & vbnewline & _
  "and type != 'unapppay' " & vbnewline
  if len(strWhere1)>0 then

  strSQLTemp6 = strSQLTemp6 & _

  " and " & strWhere1

  end if

  strSQLTemp6 = strSQLTemp6 & _

  "group by billingCategoryID" & strSQLOption_1 & strPatient2 & vbnewline

  if len(strSQLOption_3)>0 then

  strSqual = strSqual & " and t5." & strSQLOption_3 & " = g." & strSQLOption_3 & " " & vbnewline

  end if

  if len(strPatient3)>0 then

  strSqual = strSqual & " and t5." & strPatient3 & " = g." & strPatient3 & " " & vbnewline

  end if

  strSqual = strSqual & _

  "left outer join " & vbnewline & _

  "(" & vbnewline & _

  "select * from #table7 " & ")t6 on t6.billingCategoryID = g.billingCategoryID" & vbnewline

  strSQLTemp7 = " insert into #table7 select " & vbnewline & _

  "billingCategoryID" & replace(strSQLOption_1, "providerid", " isnull(providerid, 0) providerid") & strPatient2 & ", " & vbnewline & _

  "sum(chargeAmount) unapplied" & vbnewline & _

  "from " & vbnewline & _

  "" & strConnName & "_report..arChargesPayments " & vbnewline & _

  "where practiceID = " & intPracticeID & "" & vbnewline & _

  "and type = 'unapppay' " & vbnewline

  if len(strWhere1)>0 then

  strSQLTemp7 = strSQLTemp7 & _

  " and " & strWhere1

  end if

  strSQLTemp7 = strSQLTemp7 & _

  "group by billingCategoryID" & strSQLOption_1 & strPatient2 & vbnewline

  if len(strSQLOption_3)>0 then

  strSqual = strSqual & " and t6." & strSQLOption_3 & " = g." & strSQLOption_3 & " " & vbnewline

  end if

  if len(strPatient3)>0 then

  strSqual = strSqual & " and t6." & strPatient3 & " = g." & strPatient3 & " " & vbnewline

  end if

  strBucketsOrder= ""

  if bln00 then

  strBucketsOrder = addOptDisplayValue(strBucketsOrder, "round(isnull(t1.amount_current,0), 2) != 0 ", " or " )

  end if

  if bln30 then

  strBucketsOrder = addOptDisplayValue(strBucketsOrder, "round(isnull(t2.amount_31_60,0), 2) != 0 " , " or ")

  end if

  if bln60 then

  strBucketsOrder = addOptDisplayValue(strBucketsOrder, "round(isnull(t3.amount_61_90,0), 2) != 0 " , " or ")

  end if

  if bln90 then

  strBucketsOrder = addOptDisplayValue(strBucketsOrder, "round(isnull(t4.amount_91_120,0), 2) != 0 " , " or ")

  end if

  if bln120 then

  strBucketsOrder = addOptDisplayValue(strBucketsOrder, "round(isnull(t5.amount_121,0), 2) !=0 " , " or ")

  end if

  if blnUnApp then

  strBucketsOrder = addOptDisplayValue(strBucketsOrder, "round(isnull(t6.unapplied,0), 2) !=0 " , " or ")

  end if

  if len(trim(strBucketsOrder))>0 then

  strBucketsOrder = " and (" & strBucketsOrder & ") "

  end if

  strSqual = strSqual & _

  strBC2 & strSQLOption_6 & strPatient4 & _

  "where 1=1 " & strZeroBalanceFilter & strBucketsOrder

  if strGroupBy = "" and blnShowPatient then

  if not len(strOrderBy) > 0 then

  strOrderBy = "bc.Code"

  else

  strOrderBy = "bc.Code, " & strOrderBy

  end if

  end if

  strFinalSort = _

  "round(isnull(t1.amount_current,0), 2) + " & vbnewline & _

  "round(isnull(t2.amount_31_60,0), 2) + " & vbnewline & _

  "round(isnull(t3.amount_61_90,0), 2) + " & vbnewline & _

  "round(isnull(t4.amount_91_120,0), 2) + " & vbnewline & _

  "round(isnull(t6.unapplied,0), 2) + " & vbnewline & _

  "round(isnull(t5.amount_121,0), 2) desc"

  if not len(strOrderBy) > 0 then

  strOrderBy = strFinalSort

  else

  strOrderBy = strOrderBy & ", " & strFinalSort

  end if

  if len(strOrderBy) > 0 then

  strSqual = strSqual & _

  "order by " & strOrderBy

  end if

  if len(trim(strPatient2))> 0 then

  strPatientSQLCreate = strPatient2 & " int"

  else

  strPatientSQLCreate = ""

  end if

  if len(trim(strSQLOption_1))> 0 then

  strSQLOption_1_SQL = strSQLOption_1 & " int"

  else

  strSQLOption_1_SQL = ""

  end if

  strSQLTempCreate = _

  "create table #table1 (billingCategoryID int" & strSQLOption_1_SQL & " " & strPatientSQLCreate & ")" & vbnewline & _

  "create table #table2 (billingCategoryID int" & strSQLOption_1_SQL & " " & strPatientSQLCreate & ", amount_current money)" & vbnewline & _

  "create table #table3 (billingCategoryID int" & strSQLOption_1_SQL & " " & strPatientSQLCreate & ", amount_31_60 money)" & vbnewline & _

  "create table #table4 (billingCategoryID int" & strSQLOption_1_SQL & " " & strPatientSQLCreate & ", amount_61_90 money)" & vbnewline & _

  "create table #table5 (billingCategoryID int" & strSQLOption_1_SQL & " " & strPatientSQLCreate & ", amount_91_120 money)" & vbnewline & _

  "create table #table6 (billingCategoryID int" & strSQLOption_1_SQL & " " & strPatientSQLCreate & ", amount_121 money)" & vbnewline & _

  "create table #table7 (billingCategoryID int" & strSQLOption_1_SQL & " " & strPatientSQLCreate & ", unapplied money)" & vbnewline

  getARSQL = strSQLTempCreate & "|||" & strSQLTemp1 & strSQLTemp2 & strSQLTemp3 & strSQLTemp4 & strSQLTemp5 & strSQLTemp6 & strSQLTemp7 & "|||" & strSqual & "|||" & strSQLEnd

  end function

  sub printForm()

  strSqual = "select billingCategoryID, Code, Description from tblBillingCategories where practiceID = " & session("practiceID") & " order by code"

  set rstBC = getStaticRecordSet(strSqual, strConnName)

  strSqual = "select officeID, officeName from tblOffice where practiceID = " & session("practiceID") & " order by officeName"

  set rstO = getStaticRecordSet(strSqual, strConnName)

  strSqual = "Select StaffProviderID providerID, FirstName + ' ' + LastName as ProviderName from vwStaffProvider where AmIProvider = 1 and PracticeID = " & session("practiceID") & " order by LastName"

  set rstP = getStaticRecordSet(strSqual, strConnName)

  print openHTML()

  call includeCalendarJavaScript()

  print noFormPageHeader("Reports", "ble", "Reports")

  %>

  <% sub printARReport(rstReport, strGroupID, strGroupLabel, strAddFields, strBanner)
  print openHTML()
  print strBanner & "

  "
  total_current = 0
  total_31_60 = 0
  total_61_90 = 0
  total_91_120 = 0
  total_121 = 0
  total_unapplied = 0

  grand_total_current = 0
  grand_total_31_60 = 0
  grand_total_61_90 = 0
  grand_total_91_120 = 0
  grand_total_121 = 0
  grand_total_unapplied = 0
  blnFirstOpenGroup = true
  blnFirstCloseGroup = false
  blnGroups = cbool(len(strGroupID))
  intNumCols = 9
  blnAddFields = false

  if len(strAddFields) >0 then

  blnAddFields = true

  arAddFields = split(strAddFields, "|")

  intNumCols = intNumCols + ubound(arAddFields)+1

  end if

  if strGroupID = "Code" then

  intNumCols = intNumCols - 1

  end if

  intRowCount = 0

  %>

  <% if blnGroups then
  %>

  <%
  end if
  openGroup blnAddFields, arAddFields, strGroupID
  intTempGroupID = -1
  strBG = "#ffffff"
  while not rstReport.eof
  intRowCount = intRowCount + 1
  if blnGroups then
  if not intTempGroupID = rstReport(strGroupID) then
  strBG = "#ffffff"
  intTempGroupID = rstReport(strGroupID)
  if blnFirstOpenGroup then
  blnFirstOpenGroup = false
  else
  blnFirstCloseGroup = true
  closeGroup false, intNumCols, total_current, total_31_60, total_61_90, total_91_120, total_121, total_unapplied
  %>

  <%
  openGroup blnAddFields,arAddFields, strGroupID
  end if
  intRowCount = 1
  total_current = 0
  total_31_60 = 0
  total_61_90 = 0
  total_91_120 = 0
  total_121 = 0
  total_unapplied = 0
  end if
  end if
  total_current = total_current + rstReport("amount_current")
  total_31_60 = total_31_60 + rstReport("amount_31_60")
  total_61_90 = total_61_90 + rstReport("amount_61_90")
  total_91_120 = total_91_120 + rstReport("amount_91_120")
  total_121 = total_121 + rstReport("amount_121")
  total_unapplied = total_unapplied + rstReport("unapplied")

  grand_total_current = grand_total_current + rstReport("amount_current")
  grand_total_31_60 = grand_total_31_60 + rstReport("amount_31_60")
  grand_total_61_90 = grand_total_61_90 + rstReport("amount_61_90")
  grand_total_91_120 = grand_total_91_120 + rstReport("amount_91_120")
  grand_total_121 = grand_total_121 + rstReport("amount_121")
  grand_total_unapplied = grand_total_unapplied + rstReport("unapplied")
  if strBG = "#e5e5e5" then
  strBG = "#ffffff"
  else
  strBG = "#e5e5e5"
  end if
  %>

  <%
  if blnAddFields then
  for each item in arAddFields
  %>

  <%
  next
  end if
  if not strGroupID = "Code" then
  %>

  <%
  function splitName(strItem)
  blnFirst = false
  strReturn = ""
  for intX = 1 to len(strItem)
  strChar = mid(strItem, intX, 1)
  if ucase(strChar) = strChar then
  if blnFirst then
  strReturn = strReturn & " "
  else
  blnFirst = true
  end if
  end if
  strReturn = strReturn & strChar
  next
  splitName = strReturn
  end function

  sub printARSummaryReport (intPracticeID, strDate, strConnName, strZeroBalance)
  strSqual = getSummaryTRSQL(intPracticeID, strDate, strConnName, strZeroBalance)
  set rstReport=getStaticRecordSet(strSqual, strConnName)

  strSqual = "select sum(chargeAmount) startingBalance from " & strConnName & "_report..arChargesPayments where practiceID = " & intPracticeID & " and rawDate < '" & datepart("m", strDate) & "/1/" & datepart("yyyy", strDate) & "'"
  set rstReport2 = getStaticRecordSet(strSqual, strConnName)

  strSqual = getARSummarySQL(strDate, intPracticeID, strZeroBalance)
  set rstReport3 = getStaticRecordSet(strSqual, strConnName)

  printSummaryReport intPracticeID, rstReport, rstReport2, rstReport3, strDate
  end sub

  function getSummaryTRSQL(intPracticeID, strDate, strConnName, strZeroBalance)
  intYear = datepart("yyyy", strDate)
  intMonth = datepart("m", strDate)
  getSummaryTRSQL = _
  "select " & vbnewline & _
  "arg, month, sum(chargeAmount) amount" & vbnewline & _
  " from " & vbnewline & _
  "(" & vbnewline & _
  "select " & vbnewline & _
  "case when cp.type = 'charge' then " & vbnewline & _
  "'ch'" & vbnewline & _
  "else" & vbnewline & _
  " case when pc.isRefund = 1 then " & vbnewline & _
  "case when pc.sourceType = 1 then " & vbnewline & _
  "'rp'" & vbnewline & _
  "else" & vbnewline & _
  "'ri'" & vbnewline & _
  "end" & vbnewline & _
  "else" & vbnewline & _
  "case when pc.paymentCodeType = 1 then" & vbnewline & _
  "case when pc.sourceType = 1 then " & vbnewline & _
  "'pp'" & vbnewline & _
  "else" & vbnewline & _
  "'pi'" & vbnewline & _
  "end" & vbnewline & _
  "else" & vbnewline & _
  "case when pc.creditDebit = 1 then " & vbnewline & _
  "'ad'" & vbnewline & _
  "else" & vbnewline & _
  "'ac'" & vbnewline & _
  "end" & vbnewline & _
  "end" & vbnewline & _
  "end" & vbnewline & _
  "end" & vbnewline & _
  "arg, datepart(m, cp.rawDate) month, chargeamount" & vbnewline & _
  "from " & strConnName & "_report..arChargesPayments cp" & vbnewline & _
  "left outer join " & strConnName & "..tblPayments p on cp.paymentID = p.paymentID" & vbnewline & _
  "left outer join " & strConnName & "..tblPaymentCodes pc on p.PaymentCodeId = pc.PaymentCodeId" & vbnewline & _
  "where cp.practiceID = " & intPracticeID & vbnewline & _
  "and datepart(yyyy, cp.rawDate) = " & intYear & vbnewline & _
  "and datepart(m, cp.rawDate) = " & intMonth & vbnewline & _
  "and cp.rawDate < dateadd(d, 1, '" & strDate & "') " & vbnewline & _
  ") theTable" & vbnewline & _
  "group by arg, month"
  end function

  sub printSummaryReport(intPracticeID, rstReport, rstReport2, rstReport3, strDate)
  print openHTML()
  sngStartingBalance = rstReport2("startingBalance")
  sngCurrentCharges = getTRValue(rstReport, "ch", null)
  sngInsurancePayments = getTRValue(rstReport, "pi", null)
  sngInsuranceRefunds = getTRValue(rstReport, "ri", null)
  sngTotalInsurancePayments = getTRValue(rstReport, "ri,pi", null)
  sngPatientPayments = getTRValue(rstReport, "pp", null)
  sngPatientRefunds = getTRValue(rstReport, "rp", null)
  sngTotalPatientPayments = getTRValue(rstReport, "rp,pp", null)
  sngTotalPayments = getTRValue(rstReport, "pp,rp,pi,ri", null)
  sngDebits = getTRValue(rstReport, "ad", null)
  sngCredits = getTRValue(rstReport, "ac", null)
  sngDebitsCredits = getTRValue(rstReport, "ad,ac", null)
  sngBalance = getTRValue(rstReport, "", null) + sngStartingBalance
  sngAmount_current = rstReport3("amount_current")
  sngAmount_31_60 = rstReport3("amount_31_60")
  sngAmount_61_90 = rstReport3("amount_61_90")
  sngAmount_91_120 = rstReport3("amount_91_120")
  sngAmount_121 = rstReport3("amount_121")
  sngAmount_total = sngAmount_current + sngAmount_31_60 + sngAmount_61_90 + sngAmount_91_120 + sngAmount_121
  %>

  <%
  print closeHTML
  end sub

  function getTRValue(rstReport, strRowCode, intMonthID)

  arRowCode = split(strRowCode, ",")
  dim arFilter()
  blnArray = false
  intX = 0
  for each item in arRowCode

  redim preserve arFilter(intX)
  blnArray = true
  arFilter(intX)= arFilter(intX)& "arg='" & item & "'"
  if intMonthID >= 1 and intMonthID <= 12 then
  arFilter(intX) = "(" & arFilter(intX) & " and month=" & intMonthID & ")"
  end if
  intX = intX + 1
  next
  if blnArray then
  strFilter = join(arFilter, " or ")
  else
  if intMonthID >= 1 and intMonthID <= 12 then
  strFilter = "month=" & intMonthID
  else
  strFilter = ""
  end if
  end if

  rstReport.filter = strFilter
  sngNumber = 0
  while not rstReport.eof
  sngNumber = sngNumber + rstReport("amount")
  rstReport.moveNext
  wend
  getTRValue = sngNumber
  end function

  function getARSummarySQL(strDate, intPracticeID, strZeroBalance)
  strSqual = _
  "select " & vbnewline & _
  "isnull(t1.amount_current,0) amount_current," & vbnewline & _
  "isnull(t2.amount_31_60,0) amount_31_60," & vbnewline & _
  "isnull(t3.amount_61_90,0) amount_61_90," & vbnewline & _
  "isnull(t4.amount_91_120,0) amount_91_120, " & vbnewline & _
  "isnull(t5.amount_121,0) amount_121" & vbnewline & _
  "from " & vbnewline & _
  "(" & vbnewline & _
  "select 1 billingCategoryID" & vbnewline & _
  ")g" & vbnewline & _
  "left outer join " & vbnewline & _
  "(" & vbnewline & _
  "select 1" & vbnewline & _
  "billingCategoryID, " & vbnewline & _
  "sum(chargeAmount) amount_current" & vbnewline & _
  "from " & vbnewline & _
  "" & strConnName & "_report..arChargesPayments " & vbnewline & _
  "where procDate >= dateadd(d,-30, '" & strDate & "')" & vbnewline & _

  "and rawDate < dateadd(d, 1, '" & strDate & "')" & vbnewline & _
  "" & strZeroBalance & " and practiceID = " & intPracticeID & "" & vbnewline & _
  ")t1 on t1.billingCategoryID = g.billingCategoryID" & vbnewline & _
  "left outer join " & vbnewline & _
  "(" & vbnewline & _
  "select 1" & vbnewline & _
  "billingCategoryID, " & vbnewline & _
  "sum(chargeAmount) amount_31_60" & vbnewline & _
  "from " & vbnewline & _
  "" & strConnName & "_report..arChargesPayments " & vbnewline & _
  "where procDate < dateadd(d,-30, '" & strDate & "') and procDate >= dateadd(d, -60, '" & strDate & "')" & vbnewline & _

  "and rawDate < dateadd(d, 1, '" & strDate & "')" & vbnewline & _
  "" & strZeroBalance & " and practiceID = " & intPracticeID & "" & vbnewline & _
  "" & vbnewline & _
  ")t2 on t2.billingCategoryID = g.billingCategoryID" & vbnewline & _
  "left outer join " & vbnewline & _
  "(" & vbnewline & _
  "select 1" & vbnewline & _
  "billingCategoryID, " & vbnewline & _
  "sum(chargeAmount) amount_61_90" & vbnewline & _
  "from " & vbnewline & _
  "" & strConnName & "_report..arChargesPayments " & vbnewline & _
  "where procDate < dateadd(d,-60, '" & strDate & "') and procDate >= dateadd(d, -90, '" & strDate & "')" & vbnewline & _

  "and rawDate < dateadd(d, 1, '" & strDate & "')" & vbnewline & _
  "" & strZeroBalance & " and practiceID = " & intPracticeID & "" & vbnewline & _
  ")t3 on t3.billingCategoryID = g.billingCategoryID" & vbnewline & _
  "left outer join " & vbnewline & _
  "(" & vbnewline & _
  "select 1" & vbnewline & _
  "billingCategoryID, " & vbnewline & _
  "sum(chargeAmount) amount_91_120" & vbnewline & _
  "from " & vbnewline & _
  "" & strConnName & "_report..arChargesPayments " & vbnewline & _
  "where procDate < dateadd(d,-90, '" & strDate & "') and procDate >= dateadd(d, -120, '" & strDate & "')" & vbnewline & _

  "and rawDate < dateadd(d, 1, '" & strDate & "')" & vbnewline & _
  "" & strZeroBalance & " and practiceID = " & intPracticeID & "" & vbnewline & _
  ")t4 on t4.billingCategoryID = g.billingCategoryID" & vbnewline & _
  "left outer join " & vbnewline & _
  "(" & vbnewline & _
  "select 1" & vbnewline & _
  "billingCategoryID, " & vbnewline & _
  "sum(chargeAmount) amount_121" & vbnewline & _
  "from " & vbnewline & _
  "" & strConnName & "_report..arChargesPayments " & vbnewline & _
  "where procDate < dateadd(d, -120, '" & strDate & "')" & vbnewline & _
  "and rawDate < dateadd(d, 1, '" & strDate & "')" & vbnewline & _
  "" & strZeroBalance & " and practiceID = " & intPracticeID & "" & vbnewline & _
  ")t5 on t5.billingCategoryID = g.billingCategoryID" & vbnewline
  getARSummarySQL = strSqual
  end function

  function getMonthName(intMonthID)
  select case intMonthID
  case 1
  getMonthName = "January"
  case 2
  getMonthName = "Febfuary"
  case 3
  getMonthName = "March"
  case 4
  getMonthName = "April"
  case 5
  getMonthName = "May"
  case 6
  getMonthName = "June"
  case 7
  getMonthName = "July"
  case 8
  getMonthName = "August"
  case 9
  getMonthName = "September"
  case 10
  getMonthName = "October"
  case 11
  getMonthName = "November"
  case 12
  getMonthName = "December"

  end select
  end function

  function getFilterValue(strSource, strValue, strConnName)
  getFilterValue = strSource
  select case lcase(trim(strSource))
  case "providerid"
  strWhat = "providerName"
  strLocation = "vwProviders"
  strWhere = "providerID = " & strValue
  strLabel = "Provider"
  case "officeid"
  strWhat = "officeName"
  strLocation = "tblOffice"
  strWhere = "officeID = " & strValue
  strLabel = "Office"
  case "billingcategoryid"
  strWhat = "description + '(' + code + ')'"
  strLocation = "tblbillingcategories"
  strWhere = "billingCategoryID = " & strValue
  strLabel = "Billing Category"
  case else
  print "--" & strSource & "--"
  die
  end select
  strSqual = "select " & strWhat & " retVal from " & strLocation & " where " & strWhere
  set rstO = getStaticRecordSet(strSqual, strConnName)
  if not rstO.eof then
  getFilterValue = strLabel & ": " & rstO("retVal")
  else
  getFilterValue = ""
  end if
  end function
  function addOptDisplayValue(strF, strV, strD)
  if len(trim(strF))> 0 then

  addOptDisplayValue = strF & strD & strV

  else

  addOptDisplayValue = strV

  end if

  end function

  sub dropTables()

  on error resume next

  session("CnnStr")

  set objC = server.CreateObject("ADODB.Connection")

  With objC

  .ConnectionString = session("CnnStr")

  .CursorLocation = 3

  .CommandTimeout = 1800

  .Open

  End With

  strSQL = vbnewline & vbnewline & "drop table #table1" & vbnewline & "drop table #table2" & vbnewline & "drop table #table3" & vbnewline & "drop table #table4" & vbnewline & "drop table #table5" & vbnewline & "drop table #table6" & vbnewline & "drop table #table7"

  Set objR = server.CreateObject("ADODB.Recordset")

  With objR

  .ActiveConnection = objC

  .Source = strSQL

  .CursorType = 3

  .LockType = 4

  .Open

  End With

  set objR = nothing

  set objC = nothing

  end sub

  %>

  Thanks in Advance.

  <%=rstReport(strGroupLabel)%> (<%=rstReport(strGroupID)%>)
  <%=rstReport(strGroupLabel)%> (<%=rstReport(strGroupID)%>)
  <%=intRowCount%>	<% if lcase(item) = "patientname" then response.write rstReport(item) & " (" & rstReport("patientID") & ")" else response.write rstReport(item) end if %>

• John H Marsh

  SSChampion

  Points: 11671

  More actions

  May 30, 2009 at 8:46 pm

  #1002444

  Hello,

  Your ASP code is not visible. It may be best to include it as an attachment.

  Possibly a good starting point would be to set up a Profiler on a Dev/Test SQL Server and use it to capture the SQL code being sent by the Web Application. You would still need to parameterise the SP though and ensure you that don’t miss and conditionally generated SQL.

  Regards,

  John Marsh

  www.sql.lu
  SQL Server Luxembourg User Group