קאוד: וועל אויס אלע
<%@ Page Language="VB" %>
<% Response.Buffer = True %>
<% Response.Expires = -5764 %>
<% Server.ScriptTimeout = 900 %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<!--#INCLUDE FILE="shopping_dao.aspx"-->
<!--#INCLUDE FILE="shopping_inc.aspx"-->
<script runat="server">
const WEBSITE as String = "http://www.*******/"
const IMAGES_URL as String = WEBSITE & "images/product_images/"
CONST FEED_FILENAME as String = "*****.txt"
sub page_Load(obj as object, e as eventargs)
GenerateShoppingFeed
end sub
Sub GenerateShoppingFeed
' const WEBSITE_SSL as String = "http://www.*******.net/"
Dim rootDir As String
Dim connectstr As String
Dim db_name As String
Dim db_username As String
Dim db_userpassword As String
Dim db_server As String
Dim Feed As ShoppingDAO = New ShoppingDAO
Response.Write("Feed Creation Begins.<BR>")
rootDir = Server.MapPath("/")
db_server = "********"
db_name = "*******"
db_username = "******"
db_userpassword = "********"
Feed.Filename = rootDir & "\feeds\shopping\" & FEED_FILENAME
connectstr = "SERVER=" & db_server & ";DATABASE=" & db_name & ";UID=" & db_username & ";PWD=" & db_userpassword
' connectstr = "Initial Catalog=" & db_name & ";Data Source=*********;UID=" & db_username & ";PWD=" & db_userpassword
Dim conn AS SqlConnection = New SQLConnection()
Dim cmd AS SQLCommand = New SQLCommand()
Dim rdr AS SQLDataReader
conn.ConnectionString = connectstr
' cmd.CommandText = _
' "SELECT CMRC_Products.ProductID, CMRC_Products.ModelNumber, CMRC_Products.ModelName, CMRC_Products.ProductImage, CMRC_Products.UnitCost, " & _
' "CMRC_Products.Description, CMRC_Products.ProductDetail, CMRC_Products.BulkQuantity, CMRC_Products.weight, CMRC_Products.stockquantity, " & _
' "CMRC_Products.status, CMRC_Products.active, CMRC_Products.longdesc, CMRC_Products.customerprice, CMRC_Products.listprice, " & _
' "CMRC_Products.callforpricing, CMRC_Products.freeshipping, CMRC_Categories.CategoryID, CMRC_Categories.CategoryName, " & _
' "CMRC_Categories.Description AS Expr1, CMRC_Attributes_Products.price, CMRC_Attributes_Products.prefix, " & _
' "CMRC_Attributes_Products.Status AS Expr2, CMRC_Attributes_Products.imagename, CMRC_Attribute_Sub.subattrid, " & _
' "CMRC_Attribute_Sub.subattributename, CMRC_Attribute_Sub.price AS Expr3, CMRC_Attribute_Sub.status AS Expr4, " & _
' "CMRC_Attribute_Main.attributename, CMRC_Attribute_Main.status AS Expr5 " & _
' "FROM CMRC_Categories_Products INNER JOIN " & _
' "CMRC_Categories ON CMRC_Categories_Products.CategoryID = CMRC_Categories.CategoryID INNER JOIN " & _
' "CMRC_Products ON CMRC_Categories_Products.ProductID = CMRC_Products.ProductID INNER JOIN " & _
' "CMRC_Attribute_Main INNER JOIN " & _
' "CMRC_Attribute_Sub ON CMRC_Attribute_Main.attrid = CMRC_Attribute_Sub.attrid INNER JOIN " & _
' "CMRC_Attributes_Products ON CMRC_Attribute_Main.attrid = CMRC_Attributes_Products.attrid AND " & _
' "CMRC_Attribute_Sub.subattrid = CMRC_Attributes_Products.subattrid ON CMRC_Products.ProductID = CMRC_Attributes_Products.productid " & _
' "WHERE CMRC_Products.status = 'Y' AND CMRC_Products.active = 'Y' "
cmd.CommandText = _
"SELECT CMRC_Products.ProductID, CMRC_Products.ModelNumber, CMRC_Products.ModelName, CMRC_Products.ProductImage, CMRC_Products.UnitCost, " & _
"CMRC_Products.Description, CMRC_Products.ProductDetail, CMRC_Products.BulkQuantity, CMRC_Products.weight, CMRC_Products.stockquantity, " & _
"CMRC_Products.status, CMRC_Products.active, CMRC_Products.longdesc, CMRC_Products.customerprice, CMRC_Products.listprice, " & _
"CMRC_Products.callforpricing, CMRC_Products.freeshipping, CMRC_Categories.CategoryID, CMRC_Categories.CategoryName, " & _
"CMRC_Categories.Description AS Expr1, CMRC_Attributes_Products.price, CMRC_Attributes_Products.prefix, " & _
"CMRC_Attributes_Products.Status AS Expr2, CMRC_Attributes_Products.imagename, CMRC_Attribute_Sub.subattrid, " & _
"CMRC_Attribute_Sub.subattributename, CMRC_Attribute_Sub.price AS Expr3, CMRC_Attribute_Sub.status AS Expr4, " & _
"CMRC_Attribute_Main.attributename, CMRC_Attribute_Main.status AS Expr5 " & _
"FROM CMRC_Categories_Products LEFT JOIN " & _
"CMRC_Categories ON CMRC_Categories_Products.CategoryID = CMRC_Categories.CategoryID LEFT JOIN " & _
"CMRC_Products ON CMRC_Categories_Products.ProductID = CMRC_Products.ProductID LEFT JOIN " & _
"CMRC_Attribute_Main LEFT JOIN " & _
"CMRC_Attribute_Sub ON CMRC_Attribute_Main.attrid = CMRC_Attribute_Sub.attrid LEFT JOIN " & _
"CMRC_Attributes_Products ON CMRC_Attribute_Main.attrid = CMRC_Attributes_Products.attrid AND " & _
"CMRC_Attribute_Sub.subattrid = CMRC_Attributes_Products.subattrid ON CMRC_Products.ProductID = CMRC_Attributes_Products.productid " & _
"WHERE CMRC_Products.status = 'Y' AND CMRC_Products.active = 'Y' "
cmd.CommandType = CommandType.Text
cmd.Connection = conn
cmd.Connection.Open()
rdr = cmd.ExecuteReader()
' Do While Not rsItems.EOF
Dim FeedItem As Shopping
While rdr.Read()
FeedItem = New Shopping
' Response.Write("Dumping " & trim(rdr("ModelName").ToString) & "<BR>")
FeedItem.MPN = trim(rdr.Item("ModelNumber"))
FeedItem.Brand = ""
FeedItem.UPC = ""
FeedItem.[Product Name] = RemoveDelimiter(trim(IfDbNullReturnString(rdr.Item("ModelName"))) & " - " & trim(IfDbNullReturnString(rdr.Item("attributename"))) & " - " & trim(IfDbNullReturnString(rdr.Item("subattributename"))))
FeedItem.Product Description = RemoveDelimiter(trim(IfDbNullReturnString(rdr.Item("description")))) & " " & RemoveDelimiter(IfDbNullReturnString(trim(rdr.Item("longdesc"))))
' FeedItem.Product Price = String.Format("{0:###,###.00}", rdr.Item("CustomerPrice") + rdr.Item("price"))
FeedItem.Product Price = String.Format("{0:###,###.00}", rdr.Item("UnitCost"))
FeedItem.Product URL = MakeURL(trim(IfDbNullReturnString(rdr.Item("CategoryName"))), trim(IfDbNullReturnString(rdr.Item("AttributeName"))), trim(IfDbNullReturnString(rdr.Item("SubAttributeName"))), trim(IfDbNullReturnString(rdr.Item("ModelName"))), IfDbNullReturnString(trim(rdr.Item("ProductId"))))
FeedItem.Image URL = IMAGES_URL & Encode(trim(rdr.Item("ProductImage")))
FeedItem.Shopping.com Categorization = "Home & Garden"
FeedItem.Stock Availability = "Y"
FeedItem.Stock Description = ""
FeedItem.Ground Shipping = ""
FeedItem.Weight = trim(rdr.Item("weight"))
FeedItem.Zip Code = "11211"
Feed.AddShoppingItem(FeedItem)
End While
' Loop
cmd.Dispose()
conn.Dispose()
Feed.DumpFeed(Response)
Response.Write("Feed created.<BR><BR>")
Response.Write("Right mouse click on the link below and save the file to your local drive then go to <a href='ftp://ftp.shopping.com/'>Shopping.com</a> to upload the feed file.<BR>")
Response.Write("<a href='" & FEED_FILENAME & "'>" & FEED_FILENAME & "</a><BR><BR>")
end sub
Private Function Encode(st AS String) As String
Dim S As String
S = st
S = Server.UrlEncode(S)
S = S.Replace("+", "%20")
Encode = S
End Function
Private Function MakeURL(Category As String, AttributeName As String, SubAttributeName As String, Name As String, ProductId As String) As String
MakeURL = WEBSITE & FixURL(Category & "_" & AttributeName & "_" & SubAttributeName & "_" & Name) & "_-P" & ProductID & ".aspx"
End Function
Private Function IfDbNullReturnString(fld) As String
If ( fld IS System.DBNull.Value ) Then
IfDbNullReturnString = ""
Else
IfDbNullReturnString = fld
End If
End Function
Private Function FixURL(URL As String)
Dim I As Integer
Dim Fixed As String = ""
Dim C As String
For I = 0 TO Len(URL) - 1
C = URL.SubString(I, 1)
IF ( ( C >= "a" AND C <= "z" ) OR ( C >= "A" AND C <= "Z" ) OR ( C >= "0" AND C <= "9" ) ) THEN
Fixed = Fixed & C
ELSE
Fixed = Fixed & "_"
END IF
Next I
FixURL = Fixed
End Function
Private Function RemoveDelimiter(ST As String) As String
Dim FixedStr AS String
FixedStr = ST
FixedStr = FixedStr.Replace("|", " ")
FixedStr = FixedStr.Replace("\n", " ")
FixedStr = FixedStr.Replace("\r", " ")
FixedStr = FixedStr.Replace(Chr(13), " ")
FixedStr = FixedStr.Replace(Chr(10), " ")
FixedStr = FixedStr.Replace(Chr(9), " ")
FixedStr = FixedStr.Replace(" ", " ")
FixedStr = FixedStr.Replace("&NBSP;", " ")
FixedStr = FixedStr.Replace("(Click to view)", " ")
FixedStr = FixedStr.Replace("Click here for more specs.", " ")
FixedStr = FixedStr.Replace("""", """)
FixedStr = FixedStr.Replace("™", " ")
FixedStr = FixedStr.Replace("”", """)
FixedStr = Regex.Replace(FixedStr, "(<[^>]+>)", "")
RemoveDelimiter = FixedStr
End Function
Private Function FixQuote(ST As String) As String
Dim FixedStr As String
FixedStr = ST
FixedStr = FixedStr.Replace("""", """""")
FixQuote = FixedStr
End Function
</script>
