<%
%>
<%
asp_header "Expires: Thu, 01 Jan 1970 00:00:01 GMT"
Server.ScriptTimeOut=600
asp_include "include/customer_variables.asp",false
asp_include "include/import_functions.asp",false
strOriginalTableName = "`customer`"
setArrElement keys,asp_count(keys),AddFieldWrappers("ID")
keys_present = 1
total_records = 0
goodlines = 0
Function getFieldNamesByHeaders(ByVal fields)
Dim fieldsNotFoundArr,fNamesArr,fNamesFromQuery,fieldLabelError,labelFieldsNotFoundArr,j,labelNotFound,i,label,fieldsListError,queryFieldsNotFoundArr,fieldFromListNotFound,fieldNotFound,fieldsDbError,dbFieldsNotFoundArr,strSQL,rs,dbFieldNum,fieldFromDBNotFound,fNameFromDB
Set fieldsNotFoundArr = (CreateDictionary())
Set fNamesArr = (CreateDictionary())
doAssignmentByRef fNamesFromQuery,GetFieldsList(strTableName)
fieldLabelError = false
Set labelFieldsNotFoundArr = (CreateDictionary())
j = 0
do while IsLess(j,asp_count(fields))
labelNotFound = true
i = 0
do while IsLess(i,asp_count(fNamesFromQuery))
doAssignmentByRef label,GetFieldLabel(GoodFieldName(strTableName),GoodFieldName(ArrayElement(fNamesFromQuery,i)))
if IsEqual(ArrayElement(fields,j),label) then
setArrElement fNamesArr,j,ArrayElement(fNamesFromQuery,i)
labelNotFound = false
exit do
end if
i = CSmartDbl(i)+1
loop
if bValue(labelNotFound) then
fieldLabelError = true
setArrElement labelFieldsNotFoundArr,asp_count(labelFieldsNotFoundArr),ArrayElement(fields,j)
end if
j = CSmartDbl(j)+1
loop
fieldsListError = false
Set queryFieldsNotFoundArr = (CreateDictionary())
if bValue(fieldLabelError) then
fieldFromListNotFound = true
Set fNamesArr = (CreateDictionary())
j = 0
do while IsLess(j,asp_count(fields))
fieldNotFound = true
i = 0
do while IsLess(i,asp_count(fNamesFromQuery))
if IsEqual(ArrayElement(fields,j),ArrayElement(fNamesFromQuery,i)) then
setArrElement fNamesArr,j,ArrayElement(fNamesFromQuery,i)
fieldNotFound = false
fieldFromListNotFound = false
exit do
end if
i = CSmartDbl(i)+1
loop
if bValue(fieldNotFound) then
fieldsListError = true
setArrElement queryFieldsNotFoundArr,asp_count(queryFieldsNotFoundArr),ArrayElement(fields,j)
end if
j = CSmartDbl(j)+1
loop
end if
fieldsDbError = false
Set dbFieldsNotFoundArr = (CreateDictionary())
if bValue(fieldLabelError) and bValue(fieldsListError) then
Set fNamesArr = (CreateDictionary())
strSQL = "select * from " & CSmartStr(AddTableWrappers(strOriginalTableName))
doAssignmentByRef rs,db_query(strSQL,conn)
doAssignmentByRef dbFieldNum,db_numfields(rs)
j = 0
do while IsLess(j,asp_count(fields))
fieldFromDBNotFound = true
i = 0
do while IsLess(i,dbFieldNum)
doAssignmentByRef fNameFromDB,db_fieldname(rs,i)
if IsEqual(ArrayElement(fields,j),fNameFromDB) then
setArrElement fNamesArr,j,fNameFromDB
fieldFromDBNotFound = false
exit do
end if
i = CSmartDbl(i)+1
loop
if bValue(fieldFromDBNotFound) then
fieldsDbError = true
setArrElement dbFieldsNotFoundArr,asp_count(dbFieldsNotFoundArr),ArrayElement(fields,j)
end if
j = CSmartDbl(j)+1
loop
end if
if (bValue(fieldLabelError) and bValue(fieldsListError)) and bValue(fieldsDbError) then
if IsLess(asp_count(labelFieldsNotFoundArr),asp_count(dbFieldsNotFoundArr)) and IsLess(asp_count(labelFieldsNotFoundArr),asp_count(queryFieldsNotFoundArr)) then
doAssignment fieldsNotFoundArr,labelFieldsNotFoundArr
else
if IsLess(asp_count(dbFieldsNotFoundArr),asp_count(labelFieldsNotFoundArr)) and IsLess(asp_count(dbFieldsNotFoundArr),asp_count(queryFieldsNotFoundArr)) then
doAssignment fieldsNotFoundArr,dbFieldsNotFoundArr
else
if IsLess(asp_count(queryFieldsNotFoundArr),asp_count(labelFieldsNotFoundArr)) and IsLess(asp_count(queryFieldsNotFoundArr),asp_count(dbFieldsNotFoundArr)) then
doAssignment fieldsNotFoundArr,queryFieldsNotFoundArr
else
if IsEqual(asp_count(queryFieldsNotFoundArr),asp_count(labelFieldsNotFoundArr)) and IsEqual(asp_count(queryFieldsNotFoundArr),asp_count(dbFieldsNotFoundArr)) then
doAssignment fieldsNotFoundArr,dbFieldsNotFoundArr
end if
end if
end if
end if
ResponseWrite "Import didn't succed, couldn't find followind fields: " & CSmartStr(asp_implode(", ",fieldsNotFoundArr))
Response.End
else
doAssignmentByRef getFieldNamesByHeaders,fNamesArr
Exit Function
end if
End Function
Function ImportFromExcel(ByVal uploadfile)
Dim autoinc,ret,data,fields,k,sql
autoinc = false
ret = 1
doAssignmentByRef data,openImportExcelFile(uploadfile)
Set fields = (CreateDictionary())
doAssignmentByRef fields,getImportExcelFields(data)
doAssignmentByRef fields,getFieldNamesByHeaders(fields)
keys_present = 1
k = 0
do while IsLess(k,asp_count(keys))
if not bValue(asp_in_array(RemoveFieldWrappers(ArrayElement(keys,k)),fields,false)) then
keys_present = 0
exit do
end if
k = CSmartDbl(k)+1
loop
autoinc = true
doAssignmentByRef ret,getImportExelData(data,fields)
doAssignmentByRef ImportFromExcel,ret
Exit Function
End Function
Function ImportFromCSV(ByVal uploadfile)
Dim ret,fields,j,k,autoinc,sql,line,row,handle,data,arr,key,value
ret = 1
Set fields = (CreateDictionary())
doAssignmentByRef fields,getImportCVSFields(uploadfile)
j = 0
do while IsLess(j,asp_count(fields))
setArrElement fields,j,trim(asp_str_replace("""","",ArrayElement(fields,j)))
j = CSmartDbl(j)+1
loop
doAssignmentByRef fields,getFieldNamesByHeaders(fields)
keys_present = 1
k = 0
do while IsLess(k,asp_count(keys))
if not bValue(asp_in_array(RemoveFieldWrappers(ArrayElement(keys,k)),fields,false)) then
keys_present = 0
exit do
end if
k = CSmartDbl(k)+1
loop
autoinc = false
autoinc = true
total_records = 0
line = ""
row = 0
if not IsFalse(doAssignmentByRef(handle,asp_fopen(uploadfile,"r"))) then
import_exitLoop11=false
do while not IsFalse(doAssignmentByRef(data,fgetcsv(handle,1000000,",")))
import_exitLoop11=false
do
if IsIdentical(row,0) then
row = CSmartDbl(row)+1
exit do
end if
Set arr = (CreateDictionary())
GetCollectionBounds data,import_loopIdx12,import_loopMax12
do while import_loopIdx12<=import_loopMax12
key = GetCollectionKey(data,import_loopIdx12)
doAssignment value,ArrayElement(data,key)
setArrElement arr,ArrayElement(fields,key),value
import_loopIdx12=import_loopIdx12+1
loop
doAssignmentByRef ret,InsertRecord(arr,row)
row = CSmartDbl(row)+1
loop while false
if import_exitLoop11 then _
exit do
loop
asp_fclose handle
end if
total_records = CSmartDbl(row)-1
doAssignmentByRef ImportFromCSV,ret
Exit Function
End Function
Function import_error_handler(ByVal errno,ByVal errstr,ByVal errfile,ByVal errline)
error_happened = 1
End Function
Function ParseCSVLine(ByVal line)
Dim arr,inword,hasquotes,start,i,c
Set arr = (CreateDictionary())
inword = 0
hasquotes = 0
start = 0
i = 0
do while IsLess(i,asp_strlen(line))
doAssignment c,ArrayElement(line,i)
do
If IsEqual(c,"""") then
if not bValue(inword) then
inword = 1
hasquotes = 1
start = CSmartDbl(i)+1
else
if IsEqual(ArrayElement(line,CSmartDbl(i)+1),"""") then
i = CSmartDbl(i)+1
exit do
else
inword = 0
hasquotes = 0
setArrElement arr,asp_count(arr),asp_substr(line,start,CSmartDbl(i)-CSmartDbl(start))
start = CSmartDbl(i)+1
end if
end if
exit do
End If
If IsEqual(c,"""") or IsEqual(c,",") then
if not bValue(inword) then
if IsEqual(ArrayElement(line,CSmartDbl(i)+1),",") then
inword = 1
end if
hasquotes = 0
start = CSmartDbl(i)+1
else
if not bValue(hasquotes) then
inword = 0
if IsEqual(ArrayElement(line,CSmartDbl(i)+1),",") then
inword = 1
end if
hasquotes = 0
setArrElement arr,asp_count(arr),asp_substr(line,start,CSmartDbl(i)-CSmartDbl(start))
start = CSmartDbl(i)+1
end if
end if
exit do
End If
If IsEqual(c,"""") or IsEqual(c,",") or IsEqual(c," ") then
exit do
End If
inword = 1
exit do
Loop While false
i = CSmartDbl(i)+1
loop
if IsLess(start,asp_strlen(line)) then
setArrElement arr,asp_count(arr),asp_substr(line,start,empty)
end if
doAssignmentByRef ParseCSVLine,arr
Exit Function
End Function
Function InsertRecord(ByVal arr,ByVal recInd)
Dim ret,rawvalues,key,val,var_type,value,retval,fields,fields_list,values_list,sql,temp_error_message,sqlset,where,k,rstmp,data
ret = 1
Set rawvalues = (CreateDictionary())
GetCollectionBounds arr,import_loopIdx14,import_loopMax14
do while import_loopIdx14<=import_loopMax14
key = GetCollectionKey(arr,import_loopIdx14)
doAssignment val,ArrayElement(arr,key)
setArrElement rawvalues,key,val
doAssignmentByRef var_type,GetFieldType(key,"")
if bValue(NeedQuotes(var_type)) then
if bValue(IsDateFieldType(var_type)) then
doAssignmentByRef value,localdate2db(val)
if (not IsIdentical(value,-1) and not IsFalse(value)) and bValue(asp_strlen(value)) then
setArrElement arr,key,value
else
setArrElement arr,key,null
end if
else
if bValue(IsTimeType(var_type)) then
doAssignmentByRef value,localtime2db(val)
if (((not IsIdentical(value,-1) and not IsFalse(value)) and bValue(asp_strlen(value))) and bValue(asp_strlen(val))) and not bValue(isnull(val)) then
setArrElement arr,key,value
else
setArrElement arr,key,null
end if
end if
end if
else
value = CSmartStr(val)
doAssignmentByRef value,asp_str_replace(",",".",value)
if IsLess(0,asp_strlen(value)) then
setArrElement arr,key,0+CSmartDbl(value)
else
setArrElement arr,key,null
end if
end if
import_loopIdx14=import_loopIdx14+1
loop
retval = true
if bValue(eventObj.exists_p1("BeforeInsert")) then
doAssignmentByRef retval,eventObj.BeforeInsert_p2(rawvalues,arr)
end if
if bValue(retval) then
doAssignmentByRef fields,asp_array_keys(arr,empty)
GetCollectionBounds fields,import_loopIdx15,import_loopMax15
do while import_loopIdx15<=import_loopMax15
key = GetCollectionKey(fields,import_loopIdx15)
doAssignment val,ArrayElement(fields,key)
setArrElement fields_list,key,AddFieldWrappers(val)
import_loopIdx15=import_loopIdx15+1
loop
values_list = ""
GetCollectionBounds arr,import_loopIdx16,import_loopMax16
do while import_loopIdx16<=import_loopMax16
key = GetCollectionKey(arr,import_loopIdx16)
doAssignment val,ArrayElement(arr,key)
if not bValue(isnull(ArrayElement(arr,key))) then
values_list = CSmartStr(values_list) & (CSmartStr(add_db_quotes(key,val,"")) & ", ")
else
values_list = CSmartStr(values_list) & "NULL, "
end if
import_loopIdx16=import_loopIdx16+1
loop
if IsLess(0,asp_strlen(values_list)) then
doAssignmentByRef values_list,asp_substr(values_list,0,CSmartDbl(asp_strlen(values_list))-2)
end if
sql = ((((("insert into " & CSmartStr(AddTableWrappers(strOriginalTableName))) & " (") & CSmartStr(asp_implode(",",fields_list))) & ") values (") & CSmartStr(values_list)) & ")"
if bValue(db_exec_import(sql,conn)) then
goodlines = CSmartDbl(goodlines)+1
else
temp_error_message = ((((("Error: in the line: " & CSmartStr(asp_implode(",",arr))) & " More info "
temp_error_message = CSmartStr(temp_error_message) & (((((("
"
if bValue(keys_present) then
sql = ("update " & CSmartStr(AddTableWrappers(strOriginalTableName))) & " set "
sqlset = ""
where = " where "
GetCollectionBounds fields,import_loopIdx17,import_loopMax17
do while import_loopIdx17<=import_loopMax17
k = GetCollectionKey(fields,import_loopIdx17)
doAssignment val,ArrayElement(fields,k)
if not bValue(asp_in_array(AddFieldWrappers(ArrayElement(fields,k)),keys,false)) then
if not bValue(isnull(ArrayElement(arr,val))) then
sqlset = CSmartStr(sqlset) & (((CSmartStr(ArrayElement(fields_list,k)) & "=") & CSmartStr(add_db_quotes(val,ArrayElement(arr,val),""))) & ", ")
else
sqlset = CSmartStr(sqlset) & (CSmartStr(ArrayElement(fields_list,k)) & "=NULL, ")
end if
else
where = CSmartStr(where) & (((CSmartStr(ArrayElement(fields_list,k)) & "=") & CSmartStr(add_db_quotes(val,ArrayElement(arr,val),""))) & " and ")
end if
import_loopIdx17=import_loopIdx17+1
loop
if IsLess(0,asp_strlen(sqlset)) then
sql = CSmartStr(sql) & CSmartStr(asp_substr(sqlset,0,CSmartDbl(asp_strlen(sqlset))-2))
end if
doAssignmentByRef where,asp_substr(where,0,CSmartDbl(asp_strlen(where))-5)
sql = CSmartStr(sql) & (" " & CSmartStr(where))
doAssignmentByRef rstmp,db_query((("select * from " & CSmartStr(AddTableWrappers(strOriginalTableName))) & " ") & CSmartStr(where),conn)
doAssignmentByRef data,db_fetch_array(rstmp)
if bValue(data) then
if bValue(db_exec_import(sql,conn)) then
goodlines = CSmartDbl(goodlines)+1
else
ResponseWrite "not updated"
error_message = CSmartStr(error_message) & CSmartStr(temp_error_message)
ret = 0
end if
else
error_message = CSmartStr(error_message) & CSmartStr(temp_error_message)
ret = 0
end if
else
error_message = CSmartStr(error_message) & CSmartStr(temp_error_message)
end if
end if
doAssignmentByRef InsertRecord,ret
Exit Function
end if
End Function
doAssignment id,IIF(not IsEqual(postvalue("id"),""),postvalue("id"),1)
error_message = ""
if IsEqual(GetRequestValue(RequestForm(),"a"),"added") then
doAssignmentByRef value,postvalue("value_ImportFileName" & CSmartStr(id))
doAssignmentByRef var_type,postvalue("type_ImportFileName" & CSmartStr(id))
doAssignmentByRef importfile,getImportTableName("file_ImportFileName" & CSmartStr(id))
doAssignmentByRef ext,asp_substr(value,CSmartDbl(asp_strlen(value))-4,empty)
doAssignmentByRef ext,asp_strtoupper(ext)
if bValue(eventObj.exists_p1("BeforeImport")) then
if IsFalse(eventObj.BeforeImport()) then
Response.End
end if
end if
if IsEqual(ext,".XLS") then
ImportFromExcel importfile
else
ImportFromCSV importfile
end if
if bValue(eventObj.exists_p1("AfterImport")) then
eventObj.AfterImport_p2 goodlines,CSmartDbl(total_records)-CSmartDbl(goodlines)
end if
if IsEqual(goodlines,total_records) then
error_message = ("" & CSmartStr(goodlines)) & " records were imported "
error_message = CSmartStr(error_message) & "To back to your list click on the Back to list button."
else
error_message = CSmartStr(error_message) & (("Number of records: " & CSmartStr(total_records)) & " ")
error_message = CSmartStr(error_message) & (("Imported: " & CSmartStr(goodlines)) & " ")
error_message = CSmartStr(error_message) & "Not imported: "
error_message = CSmartStr(error_message) & (CSmartStr(CSmartDbl(total_records)-CSmartDbl(goodlines)) & " ")
end if
end if
asp_include "include/xtempl.asp",false
asp_include "classes/runnerpage.asp",false
Set xt = (CreateClass("Xtempl",0,Empty,Empty,Empty,Empty,Empty,Empty,Empty))
Set params = (CreateDictionary3("pageType",PAGE_IMPORT,"id",id,"tName",strTableName))
setArrElementByRef params,"xt",xt
setArrElement params,"needSearchClauseObj",false
Set pageObject = (CreateClass("RunnerPage",1,params,Empty,Empty,Empty,Empty,Empty,Empty))
pageObject.addButtonHandlers
setArrElement pageObject.body,"begin",CSmartStr(ArrayElement(pageObject.body,"begin")) & "" & vbcrlf
setArrElement pageObject.body,"begin",CSmartStr(ArrayElement(pageObject.body,"begin")) & "" & vbcrlf
setArrElement pageObject.body,"begin",CSmartStr(ArrayElement(pageObject.body,"begin")) & "" & vbcrlf
if IsIdentical(pageObject.debugJSMode,true) then
setArrElement pageObject.body,"begin",CSmartStr(ArrayElement(pageObject.body,"begin")) & "" & vbcrlf
else
setArrElement pageObject.body,"begin",CSmartStr(ArrayElement(pageObject.body,"begin")) & "" & vbcrlf
end if
pageObject.fillSetCntrlMaps
setArrElement pageObject.body,"end",CSmartStr(ArrayElement(pageObject.body,"end")) & ""
pageObject.addCommonJs
setArrElement pageObject.body,"end",CSmartStr(ArrayElement(pageObject.body,"end")) & (("")
xt.assignbyref_p2 "body",pageObject.body
xt.assign_p2 "importfile_attrs",((("id=""file_ImportFileName" & CSmartStr(pageObject.id)) & """ name=""file_ImportFileName") & CSmartStr(pageObject.id)) & """"
xt.assign_p2 "backtolist_attrs",("id=""backButton" & CSmartStr(pageObject.id)) & """"
xt.assign_p2 "importlink_attrs",("id=""saveButton" & CSmartStr(pageObject.id)) & """"
xt.assign_p2 "error_message",error_message
xt.display_p1 "customer_import.htm"
ResponseWrite vbcrlf & vbcrlf
%>