Sub DataImport(Caller As Object) As ResumableSub
' The following procedure loads the CSV data into "temp_import" table
wait for (ImportIntoTempTable) Complete (success As Boolean)
If success=False Then Return False
' Processing Categories
Dim SqlFind As String="SELECT distinct cat_code,cat_name FROM temp_import"
Dim SqlIns As String="INSERT OR IGNORE INTO categories (code,name) SELECT ?,? WHERE NOT EXISTS (SELECT * FROM categories WHERE code=?)"
Dim SqlUpd As String="UPDATE categories SET code=?,name=? WHERE code=?"
Dim RS As ResultSet
Dim Pars As List,NRows As Int=0
Dim Id_Prod As Int,Id_Cat As Int=0,Id_Sup As Int=0
Dim CodProd As String="",CodCat As String="",CodSup As String=""
Dim StatusText As String=""
Dim HasShowStatus As Boolean=SubExists(Caller,"ShowStatus")
StatusText="Importing categories: {0}"
If HasShowStatus Then CallSub3(Caller,"ShowStatus",StatusText,0)
Dim SenderFilter As Object = DBase.ExecQueryAsync("SQL", SqlFind, Null)
Wait For (SenderFilter) SQL_QueryComplete (success As Boolean, RS As ResultSet)
If success=False Then Return False
DBase.BeginTransaction
Do While RS.NextRow
CodCat=RS.GetString("cat_code")
Id_Cat=DBase.ExecquerySingleResult("SELECT id FROM categories WHERE code=?",Array As String(CodCat))
Pars.Initialize
For i=0 To RS.ColumnCount-1
Pars.Add(RS.GetString2(i))
Next
Pars.Add(CodCat)
If Id_Cat<>0 Then
DBase.ExecNonQuery2(SqlUpd,Pars)
Else
DBase.ExecNonQuery2(SqlIns,Pars)
Id_Cat=GetLastIdentity ' --> DBase.ExecQuerySingleResult("SELECT last_insert_rowid()")
End If
DBase.ExecNonQuery2("UPDATE temp_import SET id_category=? WHERE cat_code=? AND id_category IS NULL",Array As String(Id_Cat,CodCat))
NRows=NRows+1
If NRows Mod 10=0 Then
If HasShowStatus Then CallSub3(Caller,"ShowStatus",StatusText,NRows)
End If
Loop
RS.Close
DBase.TransactionSuccessful
DBase.EndTransaction
StatusText="Importing suppliers: {0}"
If HasShowStatus Then CallSub3(Caller,"ShowStatus",StatusText,0)
SqlFind ="SELECT distinct sup_code,sup_name FROM temp_import"
SqlIns ="INSERT OR IGNORE INTO suppliers (code,name,address,city,state) SELECT ?,?,?,?,? WHERE NOT EXISTS (SELECT * FROM suppliers WHERE code=?)"
SqlUpd ="UPDATE suppliers SET code=?,name=?,address=?,city=?,state=? WHERE code=?"
SenderFilter = DBase.ExecQueryAsync("SQL", SqlFind, Null)
Wait For (SenderFilter) SQL_QueryComplete (success As Boolean, RS As ResultSet)
If success=False Then Return False
DBase.BeginTransaction
NRows=0
Do While RS.NextRow
CodSup=RS.GetString("sup_code")
Id_Sup=DBase.ExecquerySingleResult("SELECT id FROM suppliers WHERE code=?",Array As String(CodSup))
Pars.Initialize
For i=0 To RS.ColumnCount-1
Pars.Add(RS.GetString2(i))
Next
Pars.Add(CodSup)
If Id_Sup<>0 Then
DBase.ExecNonQuery2(SqlUpd,Pars)
Else
DBase.ExecNonQuery2(SqlIns,Pars)
Id_Sup=GetLastIdentity
End If
DBase.ExecNonQuery2("UPDATE temp_import SET id_supplier=? WHERE sup_code=? AND id_supplier IS NULL",Array As String(Id_Sup,CodSup))
NRows=NRows+1
If NRows Mod 10=0 Then
If HasShowStatus Then CallSub3(Caller,"ShowStatus",StatusText,NRows)
End If
Loop
RS.close
DBase.TransactionSuccessful
DBase.EndTransaction
StatusText="Processing products: {0}"
If HasShowStatus Then CallSub3(Caller,"ShowStatus",StatusText,0)
SqlFind="SELECT id_category,id_supplier,name,flags,code FROM temp_import"
SqlIns ="INSERT OR IGNORE INTO products(id_category,id_supplier,name,flags,code) VALUES(?,?,?,'A',?)"
SqlUpd ="UPDATE products SET id_category=?,id_supplier=?,flags=?,code=? WHERE code=?"
DBase.ExecNonQuery("UPDATE products SET flags='X'") ' Mark all products as "Inactive"
SenderFilter = DBase.ExecQueryAsync("SQL", SqlFind, Null)
Wait For (SenderFilter) SQL_QueryComplete (success As Boolean, RS As ResultSet)
If success=False Then Return False
NRows=0
Do While RS.NextRow
CodProd=RS.GetString("code")
Id_Prod=DBase.ExecQuerySingleResult2("SELECT id FROM products WHERE code=?",Array As String(CodProd))
Pars.Initialize
For i=0 To RS.ColumnCount-1
Pars.Add(RS.GetString2(i))
Next
Try
If Id_Prod<>0 Then
DBase.ExecNonQuery2(SqlUpd,Pars)
Else
DBase.ExecNonQuery2(SqlIns,Pars)
End If
Catch
Log(LastException)
End Try
NRows=NRows+1
If NRows Mod 10=0 Then
If HasShowStatus Then CallSub3(Caller,"ShowStatus",StatusText,NRows)
End If
Loop
RS.close
DBase.TransactionSuccessful
DBase.EndTransaction
DBase.ExecNonQuery("DELETE FROM products WHERE flags='X'") ' Delete all old products
If HasShowStatus Then CallSub3(Caller,"ShowStatus","",0)
Return True
End Sub