Ta fram det lägsta priset

25 svar · 1 092 visningar · startad av devotion 31 maj 2006 · sida 2 av 2

Frågan, av devotion 31 maj 2006

:) Hmm, har en fråga! Jag har följande kod, Tack emission... :birp Set objRS = Server.CreateObject("ADODB.Recordset") objRS.Open sSQL, objConn, 3, 1 set sSuppliers=CreateObject("Scripting.Dictionary") set sArticles=CreateObject("Scripting.Dictionary") set sSupplierArticles=CreateObject("Scripting.Dictionary") Do while not objRS.EOF sSuppliers(cstr(objRS("SupplierId"))) = cstr(objRS("

Läs frågan i sin helhet →

devotionMedlem sedan jan. 20013 582 inlägg

4 juni 2006 19:45#21

Såååå, då har jag fixat och trixat lite med koden...

Detta är resultatet:

http://80.73.182.130/articles/cart.asp

Och här kommer koden:

<%
'Deklarera variabler
'*******************
Dim objConn, objRS, objDict, sSQL, sSuppliers, sSupplierArticles, sArticles, sKey, iSupplierId, sArticle, sSupplierarticle

iResultCount = 0

' Öppna databasen
' ***************
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = Application("sConn")
objConn.Open

sSQL = 	"SELECT tblArticle.articleName, tblArticle.articleUnit, tblPrice.articlePrice, tblSupplier.SupplierName, " &_
		"tblCart.articleAmount, tblSupplier.SupplierId, tblCategory.CategoryName, tblPrice.articleNumber, tblDefaultArticle.defaultArticle " &_
		"FROM tblArticle " &_
		"INNER JOIN tblPrice ON tblArticle.articlenumber = tblPrice.articleNumber " &_
		"INNER JOIN tblSupplier ON tblPrice.articleSupplierId = tblSupplier.SupplierId " &_
		"INNER JOIN tblCart ON tblArticle.articleName = tblCart.articleName " &_
		"INNER JOIN tblCategory ON tblPrice.articleSupplierId = tblCategory.supplierId AND tblPrice.articleCategoryNumber = tblCategory.categoryNumber " &_
		"LEFT OUTER JOIN tblDefaultArticle ON tblPrice.articleSupplierId = tblDefaultArticle.supplierId AND tblPrice.articleNumber = tblDefaultArticle.defaultArticleNumber "&_
		"ORDER BY tblPrice.articlePrice"

Class Article
	Public Name
	Public Unit
End Class

Class SupplierProduct
	Public Product
	Public CheapestArticle
	Public Supplier
	Public Articles
	Private Sub Class_Initialize
		Set Articles=CreateObject("Scripting.Dictionary")
		Set CheapestArticle=Nothing
	End Sub
End Class

Class SupplierArticle
	Public SupplierProduct
	Public ArticleNumber
	Public CategoryName
	Public Price
End Class

Class Supplier
	Public Name
	Public Id
End Class

Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open sSQL, objConn, 3, 1

set sSuppliers=CreateObject("Scripting.Dictionary")
set sProducts = CreateObject("Scripting.Dictionary")
set sSupplierProducts=CreateObject("Scripting.Dictionary")

Do while not objRS.EOF
	supplierId=cstr(objRS("SupplierId"))
	supplierName=cstr(objRS("SupplierName"))
	articleName  =cstr(objRS("articleName"))
	articleUnit=cstr(objRS("articleUnit"))
	articlePrice=cdbl(objRS("articlePrice"))
	categoryName=cstr(objRS("CategoryName"))
	articleNumber=cstr(objRS("articleNumber"))
	
	if not sSuppliers.Exists(supplierId) then
		set oSupplier = new Supplier
		oSupplier.Name = supplierName
		oSupplier.Id = supplierId
		set sSuppliers(supplierId)=oSupplier
	else
		set oSupplier = sSuppliers(supplierId)
	end if
	
	if not sProducts.Exists(articleName) then
		set oProduct = new Article
		oProduct.Name=articleName
		oProduct.Unit=articleUnit
		set sProducts(articleName) = oProduct
	else
		set oProduct=sProducts(articleName)
	end if
	
	sKey=supplierId & vbTab & articleName
	
	if not sSupplierProducts.Exists(sKey) then
		set oSupplierProduct=new SupplierProduct
		set oSupplierProduct.Product=oProduct
		set oSupplierProduct.Supplier=oSupplier
		set sSupplierProducts(sKey)=oSupplierProduct
	else
		set oSupplierProduct=sSupplierProducts(sKey)
	end if
	
	set oSupplierArticle=new SupplierArticle
	set oSupplierArticle.SupplierProduct=oSupplierProduct
	oSupplierArticle.ArticleNumber=articleNumber
	oSupplierArticle.CategoryName=categoryName
	oSupplierArticle.Price=articlePrice

	if oSupplierProduct.CheapestArticle is nothing then
		set oSupplierProduct.CheapestArticle=oSupplierArticle
	elseif oSupplierProduct.CheapestArticle.Price>oSupplierArticle.Price then
		set oSupplierProduct.CheapestArticle=oSupplierArticle
	end if

	 set oSupplierProduct.Articles(articleNumber)=oSupplierArticle

	objRs.MoveNext
	
Loop	

response.write vbTab & "<form><table>" & vbCrLf
response.write vbTab & vbTab & "<thead>" & vbCrLf 
response.write vbTab & vbTab & vbTab & "<tr>" & vbCrLf
response.write vbTab & vbTab & vbTab & vbTab & "<th>Artikelbenämning</th>" & vbCrLf 
response.write vbTab & vbTab & vbTab & vbTab & "<th>Enhet</th>" & vbCrLf

	for each iSupplierId in sSuppliers
		set oSupplier=sSuppliers(iSupplierId)
		response.write vbTab & vbTab & vbTab & vbTab & "<th>" & oSupplier.Name & "</th>" & vbCrLf
	next

	response.write vbTab & vbTab & vbTab & "</tr>" & vbCrLf 
	response.write vbTab & vbTab & "</thead>" & vbCrLf
	response.write vbTab & vbTab & "<tbody>" & vbCrLf

	for each sArticleName in sProducts
		set oProduct=sProducts(sArticleName)
		response.write vbTab & vbTab & vbTab & "<tr>" & vbCrLf 
		response.write vbTab & vbTab & vbTab & vbTab & "<td>" & sArticleName & "</td>" & vbCrLf 
		response.write vbTab & vbTab & vbTab & vbTab & "<td>" & oProduct.Unit & "</td>" & vbCrLf
		for each iSupplierId in sSuppliers
			set oSupplier=sSuppliers(iSupplierId)
			response.write vbTab & vbTab & vbTab & vbTab & "<td>" & vbCrLf
			sKey=iSupplierId & vbTab & sArticleName
			if not sSupplierProducts.Exists(sKey) then
				response.write "&nbsp;"
			else
				set oSupplierProduct=sSupplierProducts(sKey)
				'Här ska priserna loopas ut
				'*******************************
				iResultCount = iResultCount + 1
			
				response.write vbTab & vbTab & vbTab & vbTab & vbTab & "<span id=""result" & iResultCount & """ style=""visibility:hidden;position:absolute;z-index:20;background-color:#cccccc;border:1px solid black;padding:3px;"">" & vbCrLf
				response.write vbTab & vbTab & vbTab & vbTab & vbTab & vbTab & "<ul>" & vbCrLf
				for each sSupplierarticle in oSupplierProduct.Articles
					set oSupplierarticle=oSupplierProduct.Articles(sSupplierarticle)
					
					Response.Write vbTab & vbTab & vbTab & vbTab & vbTab & vbTab & vbTab & "<li>"
					Response.write "<span onclick=""changeValue('" & Trim(oSupplierArticle.ArticleNumber) & "','" & oSupplierArticle.Price & "','" & oSupplierArticle.CategoryName & "','defaultArticle', " & iResultCount & ")"" style=""cursor:pointer;color:blue"">" & oSupplierArticle.ArticleNumber & " - " & oSupplierArticle.Price & " - " & oSupplierArticle.CategoryName & "</span></li>" & vbCrLf
				next
				Response.write vbTab & vbTab & vbTab & vbTab & vbTab & vbTab & "</ul>" & vbCrLf
				response.write vbTab & vbTab & vbTab & vbTab & vbTab & "</span>" & vbCrLf
				'******************************************
				
				'Skriv ut länk
				'**********************
				response.write vbTab & vbTab & vbTab & vbTab & vbTab & vbTab & "<a id=""link" & iResultCount & """ href=""#"" onclick=""chooseResultContainer('result" & iResultCount & "'); return showResults(event,this)"" style=""color:blue;text-decoration:none"" title=""" & oSupplierArticle.CategoryName & """>" & Trim(oSupplierProduct.CheapestArticle.ArticleNumber) & " - " & oSupplierProduct.CheapestArticle.Price & " kr</a>" & vbCrLf
				response.write vbTab & vbTab & vbTab & vbTab & vbTab & vbTab & "<input type=""hidden"" name=""defaultArticle"" value=""" & oSupplierProduct.CheapestArticle.ArticleNumber & "|" & oSupplier.Id & """>" & vbCrLf
			'**********************************
			end if
			response.write vbTab & vbTab & vbTab & vbTab & "</td>" & vbCrLf
		next
		response.write vbTab & vbTab & vbTab & "</tr>" & vbCrLf
	next

response.write vbTab & vbTab & "</tbody>" & vbCrLf
response.write vbTab & "</table></form>" & vbCrLf

objRS.Close
Set objRS = Nothing

objConn.Close
Set objConn = Nothing

set sSuppliers = Nothing
set sArticles = Nothing
set sSupplierArticles = Nothing
%>

Nu tror jag det börjar närma sig.. :D

Mvh
Henrik

devotionMedlem sedan jan. 20013 582 inlägg

4 juni 2006 21:47#22

Nu är det bara att få till default article kvar... Emission???

Mvh
Henrik

emissionMedlem sedan dec. 19996 721 inlägg

4 juni 2006 22:02#23

Det var DM-konsert på MTV....

<%
'Deklarera variabler
'*******************
Dim objConn, objRS, objDict, sSQL, sSuppliers, sSupplierArticles, sArticles, sKey, iSupplierId, sArticle, sSupplierarticle

iResultCount = 0

' Öppna databasen
' ***************
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = Application("sConn")
objConn.Open

sSQL = 	"SELECT tblArticle.articleName, tblArticle.articleUnit, tblPrice.articlePrice, tblSupplier.SupplierName, " &_
		"tblCart.articleAmount, tblSupplier.SupplierId, tblCategory.CategoryName, tblPrice.articleNumber, tblDefaultArticle.defaultArticle " &_
		"FROM tblArticle " &_
		"INNER JOIN tblPrice ON tblArticle.articlenumber = tblPrice.articleNumber " &_
		"INNER JOIN tblSupplier ON tblPrice.articleSupplierId = tblSupplier.SupplierId " &_
		"INNER JOIN tblCart ON tblArticle.articleName = tblCart.articleName " &_
		"INNER JOIN tblCategory ON tblPrice.articleSupplierId = tblCategory.supplierId AND tblPrice.articleCategoryNumber = tblCategory.categoryNumber " &_
		"LEFT OUTER JOIN tblDefaultArticle ON tblPrice.articleSupplierId = tblDefaultArticle.supplierId AND tblPrice.articleNumber = tblDefaultArticle.defaultArticleNumber "&_
		"ORDER BY tblPrice.articlePrice"

Class Article
	Public Name
	Public Unit
End Class

Class SupplierProduct
	Public Product
	Public CheapestArticle
	Public DefaultArticle
	Public Supplier
	Public Articles
	Public Function GetDefaultOrCheapest
		If DefaultArticle Is Nothing then
			Set GetDefaultOrCheapest=CheapestArticle
		Else
			Set GetDefaultOrCheapest=DefaultArticle
		End If
	End Function
	Private Sub Class_Initialize
		Set Articles=CreateObject("Scripting.Dictionary")
		Set CheapestArticle=Nothing
		Set DefaultArticle=Nothing
	End Sub
End Class

Class SupplierArticle
	Public SupplierProduct
	Public ArticleNumber
	Public CategoryName
	Public Price
End Class

Class Supplier
	Public Name
	Public Id
End Class

Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open sSQL, objConn, 3, 1

set sSuppliers=CreateObject("Scripting.Dictionary")
set sProducts = CreateObject("Scripting.Dictionary")
set sSupplierProducts=CreateObject("Scripting.Dictionary")

Do while not objRS.EOF
	supplierId=cstr(objRS("SupplierId"))
	supplierName=cstr(objRS("SupplierName"))
	articleName  =cstr(objRS("articleName"))
	articleUnit=cstr(objRS("articleUnit"))
	articlePrice=cdbl(objRS("articlePrice"))
	categoryName=cstr(objRS("CategoryName"))
	articleNumber=cstr(objRS("articleNumber"))
	defaultArticle=cstr(objRS("defaultArticle") & "")
	
	if not sSuppliers.Exists(supplierId) then
		set oSupplier = new Supplier
		oSupplier.Name = supplierName
		oSupplier.Id = supplierId
		set sSuppliers(supplierId)=oSupplier
	else
		set oSupplier = sSuppliers(supplierId)
	end if
	
	if not sProducts.Exists(articleName) then
		set oProduct = new Article
		oProduct.Name=articleName
		oProduct.Unit=articleUnit
		set sProducts(articleName) = oProduct
	else
		set oProduct=sProducts(articleName)
	end if
	
	sKey=supplierId & vbTab & articleName
	
	if not sSupplierProducts.Exists(sKey) then
		set oSupplierProduct=new SupplierProduct
		set oSupplierProduct.Product=oProduct
		set oSupplierProduct.Supplier=oSupplier
		set sSupplierProducts(sKey)=oSupplierProduct
	else
		set oSupplierProduct=sSupplierProducts(sKey)
	end if
	
	set oSupplierArticle=new SupplierArticle
	set oSupplierArticle.SupplierProduct=oSupplierProduct
	oSupplierArticle.ArticleNumber=articleNumber
	oSupplierArticle.CategoryName=categoryName
	oSupplierArticle.Price=articlePrice

	if oSupplierProduct.CheapestArticle is nothing then
		set oSupplierProduct.CheapestArticle=oSupplierArticle
	elseif oSupplierProduct.CheapestArticle.Price>oSupplierArticle.Price then
		set oSupplierProduct.CheapestArticle=oSupplierArticle
	end if

    
	

	if defaultArticle<>"" then
		set oSupplierProduct.DefaultArticle=oSupplierArticle
	end if

	set oSupplierProduct.Articles(articleNumber)=oSupplierArticle

	objRs.MoveNext
	
Loop	

response.write vbTab & "<form><table>" & vbCrLf
response.write vbTab & vbTab & "<thead>" & vbCrLf 
response.write vbTab & vbTab & vbTab & "<tr>" & vbCrLf
response.write vbTab & vbTab & vbTab & vbTab & "<th>Artikelbenämning</th>" & vbCrLf 
response.write vbTab & vbTab & vbTab & vbTab & "<th>Enhet</th>" & vbCrLf

	for each iSupplierId in sSuppliers
		set oSupplier=sSuppliers(iSupplierId)
		response.write vbTab & vbTab & vbTab & vbTab & "<th>" & oSupplier.Name & "</th>" & vbCrLf
	next

	response.write vbTab & vbTab & vbTab & "</tr>" & vbCrLf 
	response.write vbTab & vbTab & "</thead>" & vbCrLf
	response.write vbTab & vbTab & "<tbody>" & vbCrLf

	for each sArticleName in sProducts
		set oProduct=sProducts(sArticleName)
		response.write vbTab & vbTab & vbTab & "<tr>" & vbCrLf 
		response.write vbTab & vbTab & vbTab & vbTab & "<td>" & sArticleName & "</td>" & vbCrLf 
		response.write vbTab & vbTab & vbTab & vbTab & "<td>" & oProduct.Unit & "</td>" & vbCrLf
		for each iSupplierId in sSuppliers
			set oSupplier=sSuppliers(iSupplierId)
			response.write vbTab & vbTab & vbTab & vbTab & "<td>" & vbCrLf
			sKey=iSupplierId & vbTab & sArticleName
			if not sSupplierProducts.Exists(sKey) then
				response.write "&nbsp;"
			else
				set oSupplierProduct=sSupplierProducts(sKey)
				'Här ska priserna loopas ut
				'*******************************
				iResultCount = iResultCount + 1
			
				response.write vbTab & vbTab & vbTab & vbTab & vbTab & "<span id=""result" & iResultCount & """ style=""visibility:hidden;position:absolute;z-index:20;background-color:#cccccc;border:1px solid black;padding:3px;"">" & vbCrLf
				response.write vbTab & vbTab & vbTab & vbTab & vbTab & vbTab & "<ul>" & vbCrLf
				for each sSupplierarticle in oSupplierProduct.Articles
					set oSupplierarticle=oSupplierProduct.Articles(sSupplierarticle)  
					
					Response.Write vbTab & vbTab & vbTab & vbTab & vbTab & vbTab & vbTab & "<li>"
					Response.write "<span onclick=""changeValue('" & Trim(oSupplierArticle.ArticleNumber) & "','" & oSupplierArticle.Price & "','" & oSupplierArticle.CategoryName & "','defaultArticle', " & iResultCount & ")"" style=""cursor:pointer;color:blue"">" & oSupplierArticle.ArticleNumber & " - " & oSupplierArticle.Price & " - " & oSupplierArticle.CategoryName & "</span></li>" & vbCrLf
				next
				Response.write vbTab & vbTab & vbTab & vbTab & vbTab & vbTab & "</ul>" & vbCrLf
				response.write vbTab & vbTab & vbTab & vbTab & vbTab & "</span>" & vbCrLf
				'******************************************
				
				'Skriv ut länk
				'**********************
				set ComparisonArticle=oSupplierProduct.GetDefaultOrCheapest
				response.write vbTab & vbTab & vbTab & vbTab & vbTab & vbTab & "<a id=""link" & iResultCount & """ href=""#"" onclick=""chooseResultContainer('result" & iResultCount & "'); return showResults(event,this)"" style=""color:blue;text-decoration:none"" title=""" & oSupplierArticle.CategoryName & """>" & Trim(ComparisonArticle.ArticleNumber) & " - " & ComparisonArticle.Price & " kr</a>" & vbCrLf
				response.write vbTab & vbTab & vbTab & vbTab & vbTab & vbTab & "<input type=""hidden"" name=""defaultArticle"" value=""" & ComparisonArticle.ArticleNumber & "|" & oSupplier.Id & """>" & vbCrLf
			'**********************************
			end if
			response.write vbTab & vbTab & vbTab & vbTab & "</td>" & vbCrLf
		next
		response.write vbTab & vbTab & vbTab & "</tr>" & vbCrLf
	next

response.write vbTab & vbTab & "</tbody>" & vbCrLf
response.write vbTab & "</table></form>" & vbCrLf

objRS.Close
Set objRS = Nothing

objConn.Close
Set objConn = Nothing

set sSuppliers = Nothing
set sArticles = Nothing
set sSupplierArticles = Nothing
%>

devotionMedlem sedan jan. 20013 582 inlägg

4 juni 2006 22:04#24

Nämen vafan.....

Det var ju det jag skulle se.. sändes det på "svensk" mtv?

//Henrik

BrimbaMedlem sedan dec. 19995 874 inlägg

4 juni 2006 22:05#25

Prova att lägg in en if-sats som kontrollerar om defaultArticle är null eller inte.

Sedan kan du istället för att skriva ut:
oSupplierProduct.CheapestArticle.Price
istället skriva ut:
oSupplierarticle.Price

devotionMedlem sedan jan. 20013 582 inlägg

4 juni 2006 22:47#26

Nu verkar det som att det funkar som det ska! :)

Tack för all hjälp emission!

see you next time! ;)

Mvh
Henrik