<%
Response.expires = 0
Response.expiresabsolute = Now() - 1
Response.addHeader "pragma", "no-cache"
Response.addHeader "cache-control", "private"
Response.CacheControl = "no-cache"
response.Buffer=true
%>
<%
Group_qry=500
split_dat=3000 ' act times each action
server.scripttimeout = 100000
If Session("design-desk-shop-DAT_status") <> "login" Then
' Response.Redirect "login.asp"
end if
xPart_No=0
xDescription=1
xExtra_desc=2
xPreferred_Supplier=3
xCategory=4
xMakers_RRP=5
xRetail_Price=6
xRetail_Inc=7
xCost_Price=8
xBulk_Qty=9
xActual_Cost=10
xAverage_Cost=11
xVAT_Code=12
xQty_Held=13
xQty_on_Order=14
xUnits=15
xLast_Bought_Date=16
xLast_Sold_Date=17
xBin_No_1=18
xBin_No_2=19
xWeight=20
xBarcode=21
xKudos_Barcode=22
xLast_Stock_Take_Date=23
xStock_type=24
'global
DIM g_xpercentage,g_weight_price,g_vat
'Set objFSO = Server.CreateObject("Scripting.FileSystemObject")
on error resume next
conn_txt.close
Set conn_txt= nothing
on error goto 0
'Set conn_txt = Server.CreateObject("ADODB.Connection")
'strPathtoTextFile=server.MapPath(".") & "\dat_file\"
'conn_txt.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPathtoTextFile & ";Extended Properties=""text;HDR=YES;FMT=csvDelimited"""
' conn_txt.Open "DRIVER={Microsoft Text Driver (*.txt; *.csv)};" & _
' "DBQ=" & strPathtoTextFile & ";", "", ""
Set conn = Server.CreateObject("ADODB.Connection")
conn.CommandTimeout = 0
conn.open xDb_Conn_Str
'conn.Open xDb_Conn_Str
Function Check_dups
session("found_dups")=false
qry="SELECT Part_No, Count(Part_No) AS How_Many"
qry=qry & " FROM accessories_import_tmp "
qry=qry & " GROUP BY [part_no] "
qry=qry & " HAVING Count(accessories_import_tmp.Part_No) > 1 "
xc=DoList( qry,"nothing","nothing.asp?","xid","nothing","100%")
if xc<>"" then
session("found_dups")=true
response.write Xc
response.write "
Prceed without Duplicate Records"
end if
end function
function delete_dups
qry="DELETE accessories_import_tmp where Part_No in (SELECT Part_No "
qry=qry & " FROM accessories_import_tmp "
qry=qry & " GROUP BY [part_no] "
qry=qry & " HAVING Count(accessories_import_tmp.Part_No) > 1 )"
Set rs = conn.Execute(qry)
end function
Function Delete_tmp_table
Set rs_txt = Server.CreateObject("ADODB.Recordset")
' rs_txt.Open "SELECT * FROM [" & file_name &"]", conn_txt, 3, 3
'Now get the default values before deleting
Set rs_accessories_tmp = conn.Execute("SELECT TOP 1 * from accessories_import_tmp ")
IF not rs_accessories_tmp.eof then
g_xpercentage=rs_accessories_tmp("Percentage")
g_weight_price=rs_accessories_tmp("WeightPrice")
g_vat=rs_accessories_tmp("VAT")
else
g_xpercentage=200
g_weight_price=0.5
g_vat=0.175
end if
Set rs = conn.Execute("DELETE accessories_import_tmp")
end function
Function Do_insert_FS_read
session("insert")=""
session("data_dump")=""
session("count_err")=0
grouped_sql=""
call Delete_tmp_table
set f=objFSO.OpenTextFile(strPathtoTextFile&file_name, 1)
do while f.AtEndOfStream = false
' do while f.AtEndOfLine=false
' if zCount > 0 then ' ignore 1st line
rs_txt = Split( f.ReadLine, ",")
IF ubound(rs_txt) > 22 then
IF (rs_txt(xPart_No))<>"" THEN
'response.write rs(7) & "-"
zWeight="0.00"
'if not isnull(rs_txt(xWeight)) then
'on error resume next
yWeight=replace(rs_txt(xWeight),"£","")
if isnumeric(yWeight) then
zWeight=yWeight
end if
nzWeight=zWeight
' session("data_dump")= session("data_dump")& "
" & rs_txt(xWeight) & "-"& zWeight
' response.Redirect("msg.asp")
'------
zCost_Price="0.00"
yCost_Price=replace(rs_txt(xCost_Price),"£","")
If isnumeric(yCost_Price) then
zCost_Price=formatnumber(yCost_Price,2)
end if
'----
zExtra_Desc="NA"
yExtra_Desc=replace(rs_txt(xExtra_Desc),"'","")
If not isnull(yExtra_Desc) then
zExtra_Desc=yExtra_Desc
end if
IF LEN(zExtra_Desc) < 1 then zExtra_Desc="NA"
'---
zCategory=" "
If not isnull(rs_txt(xCategory)) then
zCategory=rs_txt(xCategory)
end if
'--
zPreferred_Supplier=" "
If not isnull(rs_txt(xPreferred_Supplier)) then
zPreferred_Supplier=rs_txt(xPreferred_Supplier)
end if
zPart_No=rs_txt(xPart_No)
'CALS
retail_price=c_retail_price(zCost_Price,g_xpercentage,zWeight,g_weight_price)
xxretail_inc=cretail_inc(retail_price,g_VAT)
'END cals
sSql=" INSERT INTO accessories_import_tmp (Weight,Category,[Product Name],[Preferred Supplier]"
sSql=sSql & ",[Retail Price],[Retail inc],[Cost Price],[Part_No]) VALUES ("
sSql=sSql & " '"& zWeight &"',"
sSql=sSql & " '"& zCategory &"',"
sSql=sSql & " '"&zExtra_Desc &"',"
sSql=sSql & " '"& zPreferred_Supplier&"',"
sSql=sSql & " '"& retail_price&"',"
sSql=sSql & " '"& xxretail_inc&"',"
sSql=sSql & zCost_Price
sSql=sSql & ",'"&zPart_No& "')"
'on error resume next
IF zCount > 0 then
' Set rs = conn.Execute(sSql) ' ignore 1st count , the header
' session("data_dump")= session("data_dump")& "
" & sSql & "-"& nzWeight
' response.Redirect("msg.asp")
'GROUPING
grouped_sql=grouped_sql& sSql &";"
IF zCount mod Group_qry= 0 then
conn.Execute(grouped_sql)
grouped_sql=""
end if
'
end if
zCount=zCount+1
' if err.number <>0 then
' session("data_dump")= session("data_dump") & " " & sSql
' err.number =0
' session("count_err")=session("count_err")+1
'response.redirect "msg.asp"
' end if
'IF instr(zPart_No,"4066") > 0 then session("data_dump")=session("data_dump") & " --" & sSql
'response.write sSql & "
"
end if ' p no
end if ' ubound
'end if ' zcount 0 line
session("txt_line_count")=session("txt_line_count")+1
'if (session("txt_line_count") mod 500)=0 then response.redirect
loop
' jus qry if any left
if grouped_sql<> "" then conn.Execute(grouped_sql) ' ignore 1st count , the header
' response.redirect "msg.asp"
' response.redirect "msg.asp"
zCount=zCount-1
session("insert_count")=zCount
call Check_dups
if session("found_dups")=false then ' go ahead to other function
session("status")= session("status") & " Total Rec in DAT File ( "& session("insert_count") & ")"
session("insert")="Complete"
response.redirect "import.asp?t=del&msg=Insert+Complete"
end if
end function
function Do_delete
if session("insert")="Complete" then
' count to be delete
sSql=" SELECT count([Part_No]) as c from Accessories where [Part_No] not in (SELECT [Part_No] from accessories_import_tmp)"
Set rs = conn.Execute(sSql)
session("deleted")=rs("c")
' do delete
sSql=" DELETE Accessories where [Part_No] not in (SELECT [Part_No] from accessories_import_tmp)"
Set rs = conn.Execute(sSql)
session("status")= session("status") & ">> Deleted ("& session("deleted") & ")"
response.redirect "import.asp?t=upd&msg=DELETED"
else
response.redirect "import.asp?msg=Delete+Failed"
end if
end function
'********UPD
Function Do_Update_new
grouped_sql=""
' Set rs_accessories_tmp = conn.Execute("SELECT * from accessories_import_tmp where [Part_No] in (select [Part_No] from accessories)")
xSQL="SELECT accessories.[Part_No],accessories.Weight, accessories.WeightPrice, accessories.VAT, accessories.Percentage,"
xSQL=xSQL & " accessories_import_tmp.[Cost Price] FROM accessories_import_tmp INNER JOIN"
xSQL=xSQL & " accessories ON accessories_import_tmp.Part_No = accessories.Part_No"
Set rs_accessories_tmp = conn.Execute(xSQL)
' nw split work
If session("split_count")<>0 then
rs_accessories_tmp.move session("split_count")
end if
do while not rs_accessories_tmp.eof
sSql="UPDATE Accessories set "
sSql=sSql & " Weight='"& rs_accessories_tmp("Weight") &"',"
'sSql=sSql & " Category ='"& rs_accessories_tmp("Category") &"',"
'sSql=sSql & " [Product Name] ='"&rs_accessories_tmp("Product Name") &"',"
'sSql=sSql & " [Preferred Supplier] ='"& rs_accessories_tmp("Preferred Supplier")&"',"
sSql=sSql & " [Cost Price] ="& rs_accessories_tmp("Cost Price") &","
'CALS
Cost_Price=rs_accessories_tmp("Cost Price")
xpercentage=rs_accessories_tmp("Percentage")
xxweight=rs_accessories_tmp("Weight")
weight_price=rs_accessories_tmp("WeightPrice")
retail_price=c_retail_price(Cost_Price,xpercentage,xxweight,weight_price)
xxretail_inc=cretail_inc(retail_price,rs_accessories_tmp("VAT"))
sSql=sSql & " [Retail Price] ='"&retail_price &"',"
sSql=sSql & " [Retail inc] ='"& xxretail_inc&"'"
'END cals
sSql=sSql & " WHERE [Part_No]= '"&rs_accessories_tmp("Part_No") & "'"
on error resume next
' Set rs_update = conn.Execute(sSql)
grouped_sql=grouped_sql& sSql &";"
IF zCount mod Group_qry= 0 then
conn.Execute(grouped_sql)
grouped_sql=""
end if
if err.number <>0 then
session("data_dump")=session("data_dump") & " " & grouped_sql & "--"& sSql
'&"--"& Err.Description &"-"& Err.Source&"-"& Err.Line
session("aborted_sql")=session("aborted_sql") & sSql &";"
' response.redirect "msg.asp"
' err.number =0
' session("count_err")=session("count_err")+1
end if
'response.write sSql & "
"
zCount=zCount+1
'New dat split
session("split_count")=session("split_count")+1
if zCount >= split_dat then
if session("split_count") < split_dat +100 then
part="B"
else
part="C"
end if
' response.redirect "import.asp?spl=t&msg=Part+Complete"
if grouped_sql<>"" then conn.Execute(grouped_sql)
response.redirect "nextpart.asp?c="&session("split_count")&"&p="&part
end if
rs_accessories_tmp.movenext
Loop
if grouped_sql<> "" then conn.Execute(grouped_sql) ' ignore 1st count , the header
' session("Updated")=zCount
session("Updated")=session("split_count")
session("status")= session("status") & " >> Updated (" & session("Updated")& ")"
session("split_count")=0
Set rs_txt = Nothing
' response.redirect "import.asp?t=ins&msg=UPDATED"
End Function
'************************
function do_insert_new
sql =sql & "select count([Part_No]) as c from accessories_import_tmp where [Part_No] not in (SELECT [Part_No] from accessories)"
Set rs = conn.Execute(sql)
session("insert_new")=rs("c")
session("status")= session("status") & " >> New Added (" & session("insert_new")& ")"
sql ="INSERT into Accessories SELECT * from accessories_import_tmp where "
sql =sql & "[Part_No] not in (SELECT [Part_No] from accessories)"
Set rs = conn.Execute(sql)
' session("data_dump")=sql
' response.redirect "msg.asp"
session("progress_stat")=session("progress_stat") & "Done]"
'** objFSO.DeleteFile strPathtoTextFile&file_name, true
response.redirect "import.asp?msg=All+Steps+Complete"
end function
'******************************************************************************
session("start_time")=now
'STEP 1 (Import to tmp)
if request.QueryString("import")="t" then
session("status")=""
session("progress_stat")= "[Importing To Temp Table........."
if check_file = false then response.redirect "import.asp?msg=Text+File+Not+Found"
'Do_insert_all_tmp(file_name)
Do_insert_FS_read
' session("insert")="Complete"
' response.redirect "import.asp?t=del&msg=Insert+Complete"
'
end if
'*****************
response.write "
--------------------------------------------
"
response.write session("progress_stat")
response.write "
--------------------------------------------
"
response.Write session("status")& "
"
response.write "--------------------------------------------
"
response.Write "
" & request.QueryString("msg") & "
"
'STEP 2 (Dete unwanted)
if request.QueryString("t")="del" then
session("progress_stat")=session("progress_stat") & "Done]
[Deleteing....."
call Do_Delete
end if
'STEP 3 (Update existing)
if request.QueryString("t")="upd" then
session("progress_stat")=session("progress_stat") & "Done]
[Updating....."
call Do_Update_new
response.redirect "import.asp?t=ins&msg=UPDATED"
end if
'STEP 4 (Add New)
if request.QueryString("t")="ins" then
session("progress_stat")=session("progress_stat") & "Done]
[Adding New records....."
call Do_insert_new
end if
'NEW step dups
if request.querystring("dups")<>"" then 'allow dups do STEP 2
call delete_dups
session("status")= session("status") & " Total Rec in DAT File ( "& session("insert_count") & ")"
session("insert")="Complete"
response.redirect "import.asp?t=del&msg=Insert+Complete"
session("progress_stat")=session("progress_stat") & "Done]
[Deleteing....."
call Do_Delete
end if
'*************NEW
'STEP 3 (Update existing)
if request.QueryString("spl")="t" then
session("progress_stat")=session("progress_stat") & "Done]
[Updating.....As Phases......"
call Do_Update_new
response.redirect "import.asp?t=ins&msg=UPDATED"
end if
response.write "
Logout
"
response.write "Back To Menu"
'response.write "
" & session("data_dump") & "---"& session("count_err")
if session("aborted_sql")<>"" THEN
conn.Execute(session("aborted_sql"))
session("aborted_sql")=""
'response.redirect "msg.asp?finala+fixed"
end if
session("aborted_sql")=""
on error resume next
conn.close
Set conn= nothing
on error goto 0
%>