<% '======================================================================================================================= ' IMPORT SERVICE ' Inserts parsed spreadsheet rows into businesses inside one transaction. ' Duplicates (same phone digits OR same email) are skipped, both against existing rows and within the file itself. '======================================================================================================================= Function ImportService_TextParam(cmd, size) cmd.Parameters.Append cmd.CreateParameter(, 202, 1, size, Null) ' adVarWChar, adParamInput End Function Function ImportService_NullIfEmpty(s) If Len(s) = 0 Then ImportService_NullIfEmpty = Null Else ImportService_NullIfEmpty = s End Function ' tbl : result of ImportFile_Read(path, 0) ' colOf : Dictionary field key -> column index (only mapped fields) ' defaultIndustry : industry name used when a row has none ("" = leave blank) ' markCustomers : True to flag every imported row as a previous customer ' Returns a Dictionary: read, added, dup_db, dup_file, no_name, new_industries, failed, error, batch_id, dups (Dictionary of samples) Function ImportService_Run(tbl, colOf, defaultIndustry, markCustomers, fileName) Dim res : Set res = CreateObject("Scripting.Dictionary") res("read") = 0 : res("added") = 0 : res("dup_db") = 0 : res("dup_file") = 0 res("no_name") = 0 : res("new_industries") = 0 : res("failed") = False : res("error") = "" res("batch_id") = 0 Dim dups : Set dups = CreateObject("Scripting.Dictionary") Set res("dups") = dups Dim conn : Set conn = DAL().Connection Dim rs, phones, emails, industries Set phones = CreateObject("Scripting.Dictionary") : phones.CompareMode = 1 Set emails = CreateObject("Scripting.Dictionary") : emails.CompareMode = 1 Set industries = CreateObject("Scripting.Dictionary") : industries.CompareMode = 1 Set rs = conn.Execute("SELECT phone_digits, email FROM businesses") Do While Not rs.EOF If Len(rs(0) & "") > 0 Then phones(rs(0) & "") = 1 If Len(rs(1) & "") > 0 Then emails(LCase(rs(1) & "")) = 1 rs.MoveNext Loop rs.Close Set rs = conn.Execute("SELECT id, name FROM industries") Do While Not rs.EOF industries(rs(1) & "") = CLng(rs(0)) rs.MoveNext Loop rs.Close ' Prepared insert for businesses Dim cmd : Set cmd = Server.CreateObject("ADODB.Command") Set cmd.ActiveConnection = conn cmd.CommandType = 1 cmd.CommandText = "INSERT INTO businesses (name, contact_name, industry_id, phone, phone_digits, email, website, " & _ "address1, city, state, zip, source, status, is_customer, notes, import_batch_id, created_at) " & _ "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,Now())" ImportService_TextParam cmd, 255 ' name ImportService_TextParam cmd, 150 ' contact_name cmd.Parameters.Append cmd.CreateParameter(, 3, 1, , Null) ' industry_id ImportService_TextParam cmd, 40 ' phone ImportService_TextParam cmd, 20 ' phone_digits ImportService_TextParam cmd, 255 ' email ImportService_TextParam cmd, 255 ' website ImportService_TextParam cmd, 255 ' address1 ImportService_TextParam cmd, 100 ' city ImportService_TextParam cmd, 50 ' state ImportService_TextParam cmd, 20 ' zip ImportService_TextParam cmd, 255 ' source ImportService_TextParam cmd, 20 ' status cmd.Parameters.Append cmd.CreateParameter(, 3, 1, , 0) ' is_customer cmd.Parameters.Append cmd.CreateParameter(, 203, 1, 4000, Null) ' notes (adLongVarWChar) cmd.Parameters.Append cmd.CreateParameter(, 3, 1, , 0) ' import_batch_id Dim indCmd : Set indCmd = Server.CreateObject("ADODB.Command") Set indCmd.ActiveConnection = conn indCmd.CommandType = 1 indCmd.CommandText = "INSERT INTO industries (name) VALUES (?)" ImportService_TextParam indCmd, 100 Dim rows : Set rows = tbl("rows") Dim i, rec, nm, contact, ind, phone, digits, email, website, addr, city, st, zip, notes Dim indId, reason, srcLabel, cust, k srcLabel = Left(fileName, 255) If markCustomers Then cust = 1 Else cust = 0 On Error Resume Next conn.BeginTrans ' Batch row (counts are updated at the end) conn.Execute "INSERT INTO import_batches (file_name, imported_at, rows_read, rows_added, rows_skipped) VALUES ('" & _ Replace(Left(fileName, 255), "'", "''") & "', Now(), 0, 0, 0)", , 128 If Err.Number = 0 Then Set rs = conn.Execute("SELECT @@IDENTITY") res("batch_id") = CLng(rs(0)) rs.Close End If If Err.Number = 0 Then For i = 0 To rows.Count - 1 If Err.Number <> 0 Then Exit For rec = rows(i) res("read") = res("read") + 1 nm = Left(ImportFile_Cell(rec, ImportService_Col(colOf, "name")), 255) If Len(nm) = 0 Then res("no_name") = res("no_name") + 1 Else contact = Left(ImportFile_Cell(rec, ImportService_Col(colOf, "contact_name")), 150) ind = Left(ImportFile_Cell(rec, ImportService_Col(colOf, "industry")), 100) phone = Left(ImportFile_Cell(rec, ImportService_Col(colOf, "phone")), 40) email = Left(ImportFile_Cell(rec, ImportService_Col(colOf, "email")), 255) website = Left(ImportFile_Cell(rec, ImportService_Col(colOf, "website")), 255) addr = Left(ImportFile_Cell(rec, ImportService_Col(colOf, "address1")), 255) city = Left(ImportFile_Cell(rec, ImportService_Col(colOf, "city")), 100) st = Left(ImportFile_Cell(rec, ImportService_Col(colOf, "state")), 50) zip = Left(ImportFile_NormalizeZip(ImportFile_Cell(rec, ImportService_Col(colOf, "zip"))), 20) notes = Left(ImportFile_Cell(rec, ImportService_Col(colOf, "notes")), 4000) If InStr(email, "@") = 0 Then email = "" email = LCase(email) digits = ImportFile_PhoneDigits(phone) reason = "" If Len(digits) > 0 Then If phones.Exists(digits) Then reason = "phone" End If If Len(reason) = 0 And Len(email) > 0 Then If emails.Exists(email) Then reason = "email" End If If Len(reason) > 0 Then If (reason = "phone" And phones(digits) = 2) Or (reason = "email" And emails(email) = 2) Then res("dup_file") = res("dup_file") + 1 Else res("dup_db") = res("dup_db") + 1 End If If dups.Count < 25 Then dups.Add dups.Count, Array(i + 2, nm, reason) Else ' Resolve industry (create when new) indId = Null If Len(ind) = 0 Then ind = defaultIndustry If Len(ind) > 0 Then If industries.Exists(ind) Then indId = industries(ind) Else indCmd.Parameters(0).Value = ind indCmd.Execute , , 128 If Err.Number <> 0 Then Exit For Set rs = conn.Execute("SELECT @@IDENTITY") indId = CLng(rs(0)) rs.Close industries(ind) = indId res("new_industries") = res("new_industries") + 1 End If End If cmd.Parameters(0).Value = nm cmd.Parameters(1).Value = ImportService_NullIfEmpty(contact) cmd.Parameters(2).Value = indId cmd.Parameters(3).Value = ImportService_NullIfEmpty(phone) cmd.Parameters(4).Value = ImportService_NullIfEmpty(digits) cmd.Parameters(5).Value = ImportService_NullIfEmpty(email) cmd.Parameters(6).Value = ImportService_NullIfEmpty(website) cmd.Parameters(7).Value = ImportService_NullIfEmpty(addr) cmd.Parameters(8).Value = ImportService_NullIfEmpty(city) cmd.Parameters(9).Value = ImportService_NullIfEmpty(st) cmd.Parameters(10).Value = ImportService_NullIfEmpty(zip) cmd.Parameters(11).Value = ImportService_NullIfEmpty(srcLabel) cmd.Parameters(12).Value = "new" cmd.Parameters(13).Value = cust cmd.Parameters(14).Value = ImportService_NullIfEmpty(notes) cmd.Parameters(15).Value = res("batch_id") cmd.Execute , , 128 If Err.Number <> 0 Then Exit For res("added") = res("added") + 1 If Len(digits) > 0 Then phones(digits) = 2 If Len(email) > 0 Then emails(email) = 2 End If End If Next End If If Err.Number <> 0 Then res("failed") = True res("error") = "Row " & (res("read") + 1) & ": " & Err.Description Err.Clear conn.RollbackTrans Else conn.Execute "UPDATE import_batches SET rows_read = " & CLng(res("read")) & ", rows_added = " & CLng(res("added")) & _ ", rows_skipped = " & CLng(res("read") - res("added")) & " WHERE id = " & CLng(res("batch_id")), , 128 If Err.Number <> 0 Then res("failed") = True res("error") = Err.Description Err.Clear conn.RollbackTrans Else conn.CommitTrans End If End If On Error GoTo 0 Set ImportService_Run = res End Function Function ImportService_Col(colOf, key) If colOf.Exists(key) Then ImportService_Col = colOf(key) Else ImportService_Col = -1 End Function %>