DTS中文件导出

'********************************************************************** '  Visual Basic ActiveX Script '************************************************************************ 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="" OR  isnull(fld) then   MakeString = "00000000"  else   if Cdate(fld) = #12/31/2020# then    MakeString = "00000000"   else    'Makestring = formatDateTime(fld,1)    makestring = fld    makeSTring = trim(cstr(makestring))    makeString = trim(replace(makestring,"/",""))    makeString = left(makestring,2) & mid(makestring,3,2) & right(makestring,4)   end if  end if   case "shortdate"  if fld="" OR  isnull(fld) then   MakeString = "000000"  else   if Cdate(fld) = #12/31/2020# then    MakeString = "000000"   else    'Makestring = formatDateTime(fld,1)    makestring = fld    makeSTring = trim(cstr(makestring))    makeString = trim(replace(makestring,"/",""))    makeString = left(makestring,2) & mid(makestring,3,2) & right(makestring,2)   end if  end if  case "integer"  if isnull(fld) then   MakeString = space(length - 1) & "0"  else   if fld = "" then    makeString = space(length)   else    fld = trim(cstr(fld))    fldlength = len(fld)    if fldLength < length then     MakeString = space(length - fldLength) & fld    else     MakeString = left(fld,length)    end if   end if  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 =  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)))   fld = replace(fld, "$", "")   fld = replace(fld, "(", "-")   fld = replace(fld, ")", "")   fld = replace(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 select end function

Function Main()

set conn = createObject("adodb.connection") conn.Open "driver={sql server};server=bert; uid=internetguest;pwd=guest;database=checkandclaim" set conn2 = createObject("adodb.connection") conn2.Open "driver={sql server};server=bert; uid=internetguest;pwd=guest;database=checkandclaim"

'''sqlStr = "select a.*, b.claim_type  from tbl_checks_printed as a inner join tbl_claim_History as b on a.claim_number = b.claim_number where gr_Number between 1600 and 1601" sqlStr = "select *  from tbl_checks_printed_MSOTemp where gr_Number between 1600 and 1699" ''''sqlStr2 = "select * from Tbl_checks_printedClaimLines as a inner join tbl_checks_printed as b on a.claim_number = b.claim_number where b.gr_number between 1600 and 1601 order by a.claim_number"

set rs = conn.execute(sqlStr)

dim fso set fso = createobject("scripting.FileSystemObject") set file = fso.createTextFile("//bert/vol1/sqldata/MSOExports/ESI_CheckExport.dat")

while not rs.eof

 file.write makeString(rs("ch_number"),"string", 8)  file.write makeString(rs("ch_date"),"date", 8)  file.write makeString(rs("gr_number"),"string", 9)  file.write makeString(rs("gr_name"),"string", 30)  file.write makeString(rs("loc_num"),"string", 4)  file.write makeString(rs("loc_name"),"string", 25)    file.write space (84)       ''''for admin address 1 thru 4 not being used ( 21spaces each)    file.write makeString(rs("patient"),"string", 17)  file.write makeString(rs("Dependent_Relationship"),"string",10)   file.write makeString(rs("patacct_number"),"string", 10)  file.write makeString(rs("claim_number"),"string", 10)  file.write makeString(rs("employee_ssn"),"string", 11)  file.write makeString(rs("Employee_Number"),"string", 5)  file.write makeString(rs("ins_name"),"string", 30)  file.write makeString(rs("ins1address_line1"),"string", 30)  file.write makeString(rs("ins2address_line2"),"string", 30)  file.write makeString(rs("ins3address_line3"),"string", 30)  file.write makeString(rs("prov1num1"),"string", 42)  file.write makeString(rs("prov1name1"),"string", 42)  file.write makeString(rs("Provider_Address_1"),"string", 42)  file.write makeString(rs("Provider_Address_2"),"string", 42)  file.write makeString(rs("Provider_Address_3"),"string", 42)  file.write makeString(rs("PPO_#_and_name"),"string", 42)

 file.write space(84)      ''''''''''''''''''''filler

 file.write makeString(rs("total_elig2_Elig_Char_Amt_"),"money", 9)         '?????????  file.write makeString(rs("cob_amount"),"money", 9)  file.write makeString(rs("check_amt"),"money", 9)  file.write makeString(rs("total_emprespons"),"money", 9)  file.write makeString(rs("Discount_Description"),"string", 14)  file.write makeString(rs("disc_amt"),"money", 13)  file.write makeString(rs("Med_Reimbursement_Desc"),"string",14)  file.write makeString(rs("Med_Reimbursement_Amt"),"money", 13)  file.write makeString(rs("Dependant_Care_Desc"),"string", 14)  file.write makeString(rs("Dependant_Care_Amount"),"money", 13)  file.write makeString(rs("Ded_Remaining_Desc"),"string", 30)  file.write makeString(rs("Deductible_Remaining_Amt"),"money", 13)  file.write makeString(rs("Ded_Remaining_Desc_NETWK"),"string",30)   file.write makeString(rs("Ded_Remaining_Amt_NETWK"),"money", 13)  file.write makeString(rs("Out_of_Pocket_Desc"),"string", 30)  file.write makeString(rs("out_of_pocket_remaining"),"money", 13)  file.write makeString(rs("Out_of_Pocket_Desc_NETWK"),"string", 30)  file.write makeString(rs("Out_of_Pocket_Amt_NETWK"),"money", 13)  file.write makeString(rs("Annual_Accumulator_Desc"),"string", 30)  file.write makeString(rs("annual_accumulators"),"money", 13)

 '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''Charge Line 1 - 8  here''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''  set rs2 = conn2.execute("sp_slcChargeLineByClaimNumber " & rs("claim_number") )    if not (rs2.bof and rs2.eof) then   rs2.movefirst  end if

 while not (rs2.eof)   if not (chargeLines < 8) then  'move to eof of rs2 if chargelines > 8    'rs2.movelast    rs2.movenext   else    chargeLines = chargeLines + 1 'increments chargeline count           file.write MakeString( rs2("detdescrservice"), "string", 16)    file.write MakeString( rs2("incdate_from"), "shortdate", 6)    file.write MakeString( rs2("incdate_through"), "shortdate", 6)    file.write MakeString( rs2("charge_amt"), "money", 9)    file.write MakeString( rs2("not_covamt"), "money", 9)    file.write MakeString( rs2("Discount_Amount"), "money", 9)    file.write MakeString( rs2("Eligible_Amount"), "money", 9)    file.write MakeString( rs2("Remark_Code"), "string", 2)    file.write MakeString( rs2("Deductible_Applied"), "money", 7)    file.write MakeString( rs2("Paid_At_Percentage"), "string", 3)    file.write MakeString( rs2("plan_pay"), "money", 9)        'remark code 10 description contains characters after position 29 that crashes file write    if rs2("Remark_Code") = 10 then     remark = left(rs2("remark"), 29)    else     remark = rs2("remark")    end if    file.write MakeString( remark, "string", 35)      '''''''''''''''''''''Remark Description

   rs2.movenext   end if  wend     For i = chargeLines +1 to 8   file.write space(120)  next

 chargeLines = 0       '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

 file.write makeString(rs("total_amtcharge"),"money",9)   file.write makeString(rs("total_notcov"),"money", 9)  file.write makeString(rs("disc_amt2_Total_Disc_Amt_"),"money", 9)  file.write makeString(rs("total_elig"),"money", 9)  file.write makeString(rs("ded_taken"),"money",7)  file.write makeString(rs("check_amt2_Total_Plan_Paid_"),"money", 9)  file.write makeString(rs("Free_Form_Message_Line_1"),"string", 40)   file.write makeString(rs("Free_Form_Message_Line_2"),"string", 40)

 ''''''''''''''''''''''''''''''''''''??? CheckAddress Lines 1 - 5 ???'''''''''''''''''''''''''''''''''''''''''''  file.write makeString(rs("free1form_mess_line1"),"string", 30)  file.write makeString(rs("free2form_mess_line2"),"string", 30)  file.write makeString(rs("free3form_mess_line3"),"string", 30)  file.write makeString(rs("free4form_mess_line4"),"string", 30)  file.write makeString(rs("free5form_mess_line5"),"string", 30)  ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

 file.write makeString(rs("Alphabetic_Check_Amount"),"string", 70)  file.write makeString(rs("Numeric_Check_Amount"),"money", 13)    file.write space(31)      '''Profess_Courtesy_Desc  file.write space(14)    '''no_check_mess  file.write space(18)    ''''???No check required  file.write makeString(rs("pay_name"),"string", 65)  file.write makeString(rs("pay_add1"),"string", 30)  file.write makeString(rs("pay_add2"),"string", 30)  file.write makeString(rs("pay_add3"),"string", 30)

 'file.write makeString(rs("claim_type"), "string", 1)   ''''''''''''''Claim Type from claim history table''''''''''''''''''  file.write space (1)

 file.write space (9)      ''''''''''''''Surcharge - Not Here'''''''''''''''''''

 file.write space (126)      '''''''''''''filler

 ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''fields from Access DB not being used'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''  ''''''''file.write makeString(rs("Check_Printed"),"string"  ''''''''file.write makeString(rs("No_Pay"),"string"  ''''''''file.write makeString(rs("adjuster_ID"),"string"  ''''''''file.write makeString(rs("incurred_date"),"date"  ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

 file.write vbcr  rs.movenext wend

Main = DTSTaskExecResult_Success End Function