'**********************************************************************' Visual Basic ActiveX Script'************************************************************************
Function EligFlag(xDate) if isnull(xDate) or trim(xDate & " ")="" then EligFlag="N" else EligFlag="Y" end if End Function
Function MakeString(fld, typ, length) select case typ case "Sdate" if isnull(fld) or cstr(fld) = "12/31/2020" then MakeString = "000000" else fld = trim(cstr(fld)) MakeString = replace(left(fld,10),"/","") end if case "date" if fld="" then MakeString = "00000000" else if Cdate(fld) = #12/31/2020# then MakeString = "99991231" else MakeString = Year(CDate(fld)) & String(2 - Len(Month(fld)), "0") & Month(CDate(fld)) & String(2 - Len(Day(fld)), "0") & Day(CDate(fld)) MakeString = Trim(CStr(MakeString)) MakeString = Replace(MakeString, "/", "") end if end if case "integer" fld = trim(cstr(fld)) fldlength = len(fld) if fldLength < length then for i = 0 to length - (fldlength + 1) MakeString = makestring & "0" next makestring = makestring & cstr(fld) else MakeString = left(fld,length) end if case "string" if isnull(fld) or fld = "" then MakeString = space(length) else fld = trim(cstr(fld)) fldLength = length - len(fld) if right(fld,1) = "," then fldLength = fldLength + 1 fld = left(fld, len(fld) - 1) end if spaces = "" for i = 1 to fldLength spaces = spaces & " " next MakeString = ucase(left(fld,length) & spaces) end if case "money" if isnull(fld) or fld = "0.00" then MakeString = space(length - 1) & "0" else fld = trim(cstr(FormatCurrency(fld))) fldlength = len(fld) if fldLength < length then MakeString = space(length - fldLength) & fld else MakeString = left(fld,length) end if end if case "Time" if isnull(fld) or fld = "" then MakeString = space(length) else makeString = left(replace(fld,":",""),6) end if end selectend function
Function Main() set conn = createObject("adodb.connection") conn.Open "driver={sql server};server=bert; uid=internetguest;pwd=guest;database=membercalls" 'set rs = conn.execute("resultsempdep") set rs = conn.execute("sp_slcresultempdep_mon_temp") dim fsoif len(Month(date))=2 thenmymonth = Month(date) elsemymonth = "0" & Month(date) end if
if len(Day(date))=2 thenmyday = Day(date) elsemyday = "0" & Day(date) end if
set fso = createobject("scripting.FileSystemObject")myfilename = "D:/ACHMB" & mymonth & myday & ".txt"'myfilename = "//bert/VOL1/RXVender/NewPrx/ACHMB" & mymonth & myday & "A.txt"set file = fso.createTextFile(myfilename)
numOfRecords = 0
do while not rs.eof and not rs.bof if left(rs("memberid"), 5) <> "11111" then
clientid = makestring(rs("clientid"),"string",15) groupid = makestring(rs("groupid"),"integer",6) & "000 " memberid = makestring("M"&rs("groupid")&rs("empnumber"),"string",18) personcode = makestring(rs("personcode"),"integer",3) sex = rs("sex") if rs("relationshipcode")=0 then relationshipcode = makestring(1,"integer",1) else if rs("relationshipcode")=1 then relationshipcode = makestring(2,"integer",1) else relationshipcode = makestring(3,"integer",1) end if end if ' relationshipcode = makestring(rs("relationshipcode"),"integer",1)
effectivedate = makestring(rs("effectivedate"),"date",8) terminationdate = makestring(rs("terminationdate"),"date",8) memberlastname = makestring(rs("memberlastname"),"string",15) memberfirstname = makestring(rs("memberfirstname"),"string",12) addressline1 = makestring(rs("addressline1"),"string",30) addressline2 = makestring(rs("addressline2"),"string",25) city = makestring(rs("city"),"string",20) st = makestring(rs("state"),"string",2) zipcode = makestring(rs("zipcode"),"string",5) &" " birthday = makestring(rs("birthdate"),"date",8) memberphonenumber = " " anniversarydate = makestring(rs("anniversarydate"),"date",8) miscellaneousgrouping1 = makestring("","string",10) miscellaneousid = makestring("","string",12) miscellaneousdata = makestring(rs("memberid"),"integer",9)&makestring("","string",21) pponame = makestring(rs("pponame"),"string",30) coveragetype = " " filler = makestring("","string",100)
file.write clientid file.write groupid file.write memberid file.write personcode file.write sex file.write relationshipcode file.write effectivedate file.write terminationdate file.write memberlastname file.write memberfirstname file.write addressline1 file.write addressline2 file.write city file.write st file.write zipcode file.write birthday file.write memberphonenumber file.write anniversarydate file.write miscellaneousgrouping1 file.write miscellaneousid file.write miscellaneousdata file.write pponame file.write coveragetype file.write filler & vbcrlf end if if rs.eof then exit do rs.movenext loop
rs.close set conn = nothing
file.close set file = nothing set fso = nothing Main = DTSTaskExecResult_Success
End Function