<% Response.Buffer = True '========================================= 'This code is copyright (c) Internet Business Solutions SL, all rights reserved. 'The contents of this file are protect under law as the intellectual property 'of Internet Business Solutions SL. Any use, reproduction, disclosure or copying 'of any kind without the express and written permission of Internet Business 'Solutions SL is forbidden. 'Author: Vince Reid, vince@virtualred.net %> <% if storesessionvalue="" then storesessionvalue="virtualstore" if Session("loggedon") <> storesessionvalue OR disallowlogin=TRUE then response.redirect "login.asp" Response.ContentType = "unknown/exe" if request.form("act")="dumpinventory" OR request.form("act")="dump2COinventory" then Response.AddHeader "Content-Disposition","attachment;filename=dumpinventory.csv" elseif request.form("act")="dumpaffiliate" then Response.AddHeader "Content-Disposition","attachment;filename=affilreport.csv" else Response.AddHeader "Content-Disposition","attachment;filename=dumporders.csv" end if Dim sd, ed, rs, cnn, sSQL, sSQL2, hasdetails, sslok hasdetails = request.form("details")="true" sslok=true if request.servervariables("HTTPS")<>"on" AND (Request.ServerVariables("SERVER_PORT_SECURE") <> "1") AND nochecksslserver<>true then sslok=false Set rs = Server.CreateObject("ADODB.RecordSet") Set rs2 = Server.CreateObject("ADODB.RecordSet") Set cnn=Server.CreateObject("ADODB.Connection") cnn.open sDSN sSQL = "SELECT countryLCID, countryCurrency, adminStockManage FROM admin INNER JOIN countries ON admin.adminCountry=countries.countryID WHERE adminID=1" rs.Open sSQL,cnn,0,1 if orlocale<>"" then Session.LCID = orlocale elseif rs("countryLCID")<>0 then Session.LCID = rs("countryLCID") end if useEuro=(rs("countryCurrency")="EUR") stockManage=Int(rs("adminStockManage")) rs.Close if Request.Form("sd") = "" then sd=Date() else sd=Request.Form("sd") end if if Request.Form("ed") = "" then ed=Date() else ed=Request.Form("ed") end if if request.form("act")="dumpaffiliate" then tdt = DateValue(sd) tdt2 = DateValue(ed)+1 Response.write "Affiliate report for " & sd & " to " & ed & vbCrLf Response.write """ID"",""Name"",""Address"",""City"",""State"",""Zip"",""Country"",""Email"",""Total""" & vbCrLf if mysqlserver=true then sSQL = "SELECT affilID,affilName,affilAddress,affilCity,affilState,affilZip,affilCountry,affilEmail,SUM(ordTotal-ordDiscount) AS sumTot FROM affiliates LEFT JOIN orders ON affiliates.affilID=orders.ordAffiliate WHERE ordStatus>=3 AND ordDate BETWEEN " & datedelim & VSUSDate(tdt) & datedelim & " AND " & datedelim & VSUSDate(tdt2) & datedelim & " OR orders.ordAffiliate IS NULL GROUP BY affilID ORDER BY affilID" else sSQL = "SELECT affilID,affilName,affilAddress,affilCity,affilState,affilZip,affilCountry,affilEmail,(SELECT Sum(ordTotal-ordDiscount) FROM orders WHERE ordStatus>=3 AND ordAffiliate=affilID AND ordDate BETWEEN " & datedelim & VSUSDate(tdt) & datedelim & " AND " & datedelim & VSUSDate(tdt2) & datedelim & ") FROM affiliates ORDER BY affilID" end if rs.Open sSQL,cnn,0,1 do while NOT rs.EOF response.write """"&replace(rs("affilID")&"","""","""""")&"""," response.write """"&replace(rs("affilName")&"","""","""""")&"""," response.write """"&replace(rs("affilAddress")&"","""","""""")&"""," response.write """"&replace(rs("affilCity")&"","""","""""")&"""," response.write """"&replace(rs("affilState")&"","""","""""")&"""," response.write """"&replace(rs("affilZip")&"","""","""""")&"""," response.write """"&replace(rs("affilCountry")&"","""","""""")&"""," response.write """"&replace(rs("affilEmail")&"","""","""""")&"""," response.write """"&rs(8)&""""&vbCrLf rs.MoveNext loop rs.Close elseif request.form("act")="dumpinventory" then sSQL2 = "SELECT pID,pName,pPrice,pInStock,pSell FROM products" rs.Open sSQL2,cnn,0,1 response.write """ProductID"",""ProductName"",""Price"",""InStock"",""OptionGroup"",""Option""" & vbCrLf do while NOT rs.EOF if (rs("pSell") AND 2)=2 then rs2.Open "SELECT optGrpName,optName,optStock FROM optiongroup INNER JOIN (options INNER JOIN prodoptions ON options.optGroup=prodoptions.poOptionGroup) ON optiongroup.optGrpID=options.optGroup WHERE prodoptions.poProdID='"&replace(rs("pID"),"'","''")&"'",cnn,0,1 do while NOT rs2.EOF response.write """"&replace(rs("pID")&"","""","""""")&"""," response.write """"&replace(rs("pName")&"","""","""""")&"""," response.write """"&rs("pPrice")&"""," response.write rs2("optStock")&"," response.write """"&replace(rs2("optGrpName")&"","""","""""")&"""," response.write """"&replace(rs2("optName")&"","""","""""")&""""&vbCrLf rs2.MoveNext loop rs2.Close else response.write """"&replace(rs("pID")&"","""","""""")&"""," response.write """"&replace(rs("pName")&"","""","""""")&"""," response.write """"&rs("pPrice")&"""," response.write rs("pInStock")&vbCrLf end if rs.MoveNext loop rs.Close elseif request.form("act")="dump2COinventory" then sSQL2 = "SELECT payProvData1 FROM payprovider WHERE payProvID=2" rs.Open sSQL2,cnn,0,1 response.write rs("payProvData1") & vbCrLf rs.Close sSQL2 = "SELECT pID,pName,pPrice,"&IIfVr(digidownloads=TRUE,"pDownload,","")&"pDescription FROM products" rs.Open sSQL2,cnn,0,1 do while NOT rs.EOF response.write replace(rs("pID"),",","\,")&"," response.write replace(replace(rs("pName"),",","\,"),vbNewline," ")&"," response.write "," response.write rs("pPrice")&"," response.write ",," if digidownloads=TRUE then response.write IIfVr(trim(rs("pDownload")&"")<>"", "N", "Y")&"," else response.write "Y," end if response.write replace(replace(strip_tags2(rs("pDescription")&""),",","\,"),vbNewline,"\n")&vbCrLf rs.MoveNext loop rs.Close else if hasdetails then sSQL2 = "SELECT ordID,ordName,ordAddress,ordAddress2,ordCity,ordState,ordZip,ordCountry,ordEmail,ordPhone,ordExtra1,ordExtra2,ordShipName,ordShipAddress,ordShipAddress2,ordShipCity,ordShipState,ordShipZip,ordShipCountry,payProvName,ordAuthNumber,ordTotal,ordDate,ordStateTax,ordCountryTax,ordHSTTax,ordShipping,ordHandling,ordShipType,cartProdId,cartProdName,cartProdPrice,cartQuantity,cartID,ordDiscount,ordAddInfo FROM cart INNER JOIN (orders INNER JOIN payprovider ON payprovider.payProvID=orders.ordPayProvider) ON cart.cartOrderId=orders.ordID" else sSQL2 = "SELECT ordID,ordName,ordAddress,ordAddress2,ordCity,ordState,ordZip,ordCountry,ordEmail,ordPhone,ordExtra1,ordExtra2,ordShipName,ordShipAddress,ordShipAddress2,ordShipCity,ordShipState,ordShipZip,ordShipCountry,payProvName,ordAuthNumber,ordTotal,ordDate,ordStateTax,ordCountryTax,ordHSTTax,ordShipping,ordHandling,ordShipType,ordDiscount,ordAddInfo FROM orders INNER JOIN payprovider ON payprovider.payProvID=orders.ordPayProvider" end if if request.form("powersearch")="1" then fromdate = Trim(request.form("fromdate")) todate = Trim(request.form("todate")) ordid = Trim(Replace(Replace(request.form("ordid"),"'",""),"""","")) origsearchtext = Trim(Replace(request.form("searchtext"),"""",""")) searchtext = Trim(Replace(request.form("searchtext"),"'","''")) ordstatus = Trim(request.form("ordstatus")) sSQL2 = sSQL2 & " WHERE ordStatus>0" if ordid<>"" then if IsNumeric(ordid) then sSQL2 = sSQL2 & " AND ordID=" & ordid else success=false errmsg="The order id you specified seems to be invalid - " & ordid sSQL2 = sSQL2 & " AND ordID=0" end if else if fromdate<>"" then if IsNumeric(fromdate) then thefromdate = (Date()-fromdate) else err.number=0 on error resume next thefromdate = DateValue(fromdate) if err.number <> 0 then thefromdate = Date() success=false errmsg="One of your date values was invalid - " & fromdate end if on error goto 0 end if if todate="" then thetodate = thefromdate elseif IsNumeric(todate) then thetodate = (Date()-todate) else err.number=0 on error resume next thetodate = DateValue(todate) if err.number <> 0 then thetodate = Date() success=false errmsg="One of your date values was invalid - " & todate end if on error goto 0 end if if thefromdate > thetodate then tmpdate = thetodate thetodate = thefromdate thefromdate = tmpdate end if sd = thefromdate ed = thetodate sSQL2 = sSQL2 & " AND ordDate BETWEEN " & datedelim & VSUSDate(thefromdate) & datedelim & " AND " & datedelim & VSUSDate(thetodate+1) & datedelim end if if ordstatus<>"" AND NOT InStr(ordstatus,"9999")>0 then sSQL2 = sSQL2 & " AND ordStatus IN (" & ordstatus & ")" if searchtext<>"" then sSQL2 = sSQL2 & " AND (ordAuthNumber LIKE '%"&searchtext&"%' OR ordName LIKE '%"&searchtext&"%' OR ordEmail LIKE '%"&searchtext&"%' OR ordAddress LIKE '%"&searchtext&"%' OR ordCity LIKE '%"&searchtext&"%' OR ordState LIKE '%"&searchtext&"%' OR ordZip LIKE '%"&searchtext&"%' OR ordPhone LIKE '%"&searchtext&"%')" end if sSQL2 = sSQL2 & " ORDER BY ordID" else sSQL2 = sSQL2 & " WHERE ordDate BETWEEN "&datedelim & VSUSDate(sd) & datedelim & " AND " & datedelim & VSUSDate(DateValue(ed)+1) & datedelim & " ORDER BY ordID" end if rs.Open sSQL2,cnn,0,1 response.write """OrderID""," if extraorderfield1<>"" then response.write """" & replace(extraorderfield1,"""","""""") & """," response.write """CustomerName"",""Address""," if useaddressline2=TRUE then response.write """Address2""," response.write """City"",""State"",""Zip"",""Country"",""Email"",""Phone""," if extraorderfield2<>"" then response.write """" & replace(extraorderfield2,"""","""""") & """," response.write """ShipName"",""ShipAddress""," if useaddressline2=TRUE then response.write """ShipAddress2""," response.write """ShipCity"",""ShipState"",""ShipZip"",""ShipCountry"",""PaymentMethod"",""AuthNumber"",""Total"",""Date"",""StateTax"",""CountryTax""," if canadataxsystem=true then response.write """HST""," response.write """Shipping"",""Handling"",""Discounts"",""AddInfo"",""ShipingMethod""" if dumpccnumber then response.write ",""Card Number"",""Expiry Date"",""CVV Code"",""Issue Number""" if hasdetails then response.write ",""ProductID"",""ProductName"",""ProductPrice"",""Quantity"",""Options""" response.write vbCrLf do while NOT rs.EOF response.write rs("ordID")&"," if extraorderfield1<>"" then response.write """"&replace(rs("ordExtra1")&"","""","""""")&"""," response.write """"&replace(rs("ordName")&"","""","""""")&"""," response.write """"&replace(rs("ordAddress")&"","""","""""")&"""," if useaddressline2=TRUE then response.write """"&replace(rs("ordAddress2")&"","""","""""")&"""," response.write """"&replace(rs("ordCity")&"","""","""""")&"""," response.write """"&replace(rs("ordState")&"","""","""""")&"""," response.write """"&replace(rs("ordZip")&"","""","""""")&"""," response.write """"&replace(rs("ordCountry")&"","""","""""")&"""," response.write """"&replace(rs("ordEmail")&"","""","""""")&"""," response.write """"&replace(rs("ordPhone")&"","""","""""")&"""," if extraorderfield2<>"" then response.write """"&replace(rs("ordExtra2")&"","""","""""")&"""," response.write """"&replace(rs("ordShipName")&"","""","""""")&"""," response.write """"&replace(rs("ordShipAddress")&"","""","""""")&"""," if useaddressline2=TRUE then response.write """"&replace(rs("ordShipAddress2")&"","""","""""")&"""," response.write """"&replace(rs("ordShipCity")&"","""","""""")&"""," response.write """"&replace(rs("ordShipState")&"","""","""""")&"""," response.write """"&replace(rs("ordShipZip")&"","""","""""")&"""," response.write """"&replace(rs("ordShipCountry")&"","""","""""")&"""," response.write """"&replace(rs("payProvName")&"","""","""""")&"""," response.write """"&replace(rs("ordAuthNumber")&"","""","""""")&"""," response.write """"&rs("ordTotal")&"""," response.write """"&rs("ordDate")&"""," response.write """"&rs("ordStateTax")&"""," response.write """"&rs("ordCountryTax")&"""," if canadataxsystem=true then response.write """"&rs("ordHSTTax")&"""," response.write """"&rs("ordShipping")&"""," response.write """"&rs("ordHandling")&"""," response.write """"&rs("ordDiscount")&"""," response.write """"&replace(rs("ordAddInfo")&"","""","""""")&"""," response.write """"&replace(rs("ordShipType")&"","""","""""")&"""" if dumpccnumber then if sslok=false then response.write ",No SSL,No SSL,No SSL,No SSL" else rs2.Open "SELECT ordCNum FROM orders WHERE ordID=" & rs("ordID"),cnn,0,1 ordCNum = rs2("ordCNum") if encryptmethod="aspencrypt" OR encryptmethod="" then response.write """Encrypted"",""Encrypted"",""Encrypted"",""Encrypted""" elseif Trim(ordCNum)="" OR IsNull(ordCNum) then response.write ",""(no data)"","""","""",""""" elseif encryptmethod="none" then cnumarr = Split(ordCNum, "&") if IsArray(cnumarr) then response.write ","""""""&cnumarr(0)&"""""""" if UBOUND(cnumarr)>=1 then response.write ","""""""&cnumarr(1)&"""""""" else response.write ",""""" if UBOUND(cnumarr)>=2 then response.write ","""&cnumarr(2)&"""" else response.write ",""""" if UBOUND(cnumarr)>=3 then response.write ","""&cnumarr(3)&"""" else response.write ",""""" else response.write ",""(no data)"","""","""",""""" end if end if rs2.Close end if end if if hasdetails then theOptions = "" thePriceDiff = 0 rs2.Open "SELECT coPriceDiff,coOptGroup,coCartOption FROM cartoptions WHERE coCartID=" & rs("cartID"),cnn,0,1 do while NOT rs2.EOF theOptions = theOptions & "," & """" & replace(rs2("coOptGroup")&"","""","""""") & " - " & replace(rs2("coCartOption"),"""","""""") & """" thePriceDiff = thePriceDiff + rs2("coPriceDiff") rs2.MoveNext loop response.write ","""&replace(rs("cartProdId")&"","""","""""")&"""" response.write ","""&replace(rs("cartProdName")&"","""","""""")&"""" response.write ","&rs("cartProdPrice")+thePriceDiff response.write ","&rs("cartQuantity") response.write theOptions rs2.Close end if response.write vbCrLf rs.MoveNext loop rs.Close end if cnn.Close set rs = nothing set rs2 = nothing set cnn = nothing %>