function to finalize invoices - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

function to finalize invoices

Author

Topic

firejackmusic
Starting Member

15 Posts

Posted - 2009-10-22 : 05:33:18

I have this function to finalise when auction finishes, it goes through each product, and list the product hidden if time is up, I then need to get the winning bidders and make a invoice, the invoice is just a total a one line entry, i get the detailed information another way, the the problem is going through everything that has finished for each sale, bit baffled any help welcome

Function updateAuctionProcedure()
	
 	sqlUpdate = "update products set listhidden = 1 where idproduct = "&pidProduct
 	connTemp.Execute(sqlUpdate)
				
 	'See if user has any bids			
 	bInvoiceComplete = False
 	sqlBid = "select top 1 count(bidAmount) as bids, idCustomerBid from bidhistory where idproduct = "&pidProduct&" group by bidAmount, idCustomerBid"
 	set rsBid = connTemp.Execute(sqlBid)
				
 	if rsBid("bids") > 0 and bInvoiceComplete = False then
 '		'if different user they need invoice
 		bidderId = rsBid("idCustomerBid")						
 		 		
 		if Session("lastuser") <> bidderId then
	' 		'get total sum of products bought by bidder
	 		sqlSum = "SELECT     SUM(products.Price) AS TotalNet FROM products INNER JOIN "
	 		sqlSum = sqlSum &"          bidhistory ON products.idproduct = bidhistory.idProduct "
	 		sqlSum = sqlSum &"	GROUP BY bidhistory.idCustomerBid HAVING (bidhistory.idCustomerBid = "&bidderId&") "
			set rsSum = connTemp.Execute(sqlSum)
								
	 		'Insert a final invoice
	 		totalNet = rsSum("TotalNet")
	 		totalCommission = 0
	 		totalCommission = pPrice * pCommission / 100
	 		totalVat = pPrice * pVatAmount / 100
	 		totalSum = TotalNet  + totalCommission + pVatAmount
								
	 		pSupplierTotalCommission = totalNet * pSupplierCommission / 100
								
	 		sqlUpdateInvoice = "insert into invoices ([idauction],[idcustomer],[paid],[total],[paymentSource],[datePaid],[net],[vat],[auctioneersCommission],[comPercentToAuctioneer] "
			sqlUpdateInvoice = sqlUpdateInvoice & "   ,[websiteCommission]  ,[dateInvoiceCreated]) values ("&pAuctionId&","&bidderId&",0,"&totalSum&",NULL,NULL,"&totalNet&","&pVatAmount&","&pSupplierTotalCommission&","&pSupplierCommission&",2,CURRENT_TIMESTAMP)"
	 		connTemp.Execute(sqlUpdateInvoice)
	 		bInvoiceComplete = True
	 	end if
	 	Session("lastuser") = bidderId
	end if
	bCloseAuction = false
end function

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-22 : 13:05:11

no use posting code, post some data and expected o/p to get solution

firejackmusic
Starting Member

15 Posts

Posted - 2009-10-23 : 04:11:39

The data passed is the idproduct, this can then dig out all the bids from the bidhistory table, the problem is, on finding a user with bids, to check on each product if he was the high bidder and if so, calculate the next bid below the max proxy bid entered... ie

if closing bid was 25 and bidder had bidder 75 to make sure thwy won the item, the calculation should be the bid lower 25 that is second highest, hope this is clear

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-23 : 12:36:50

what's preventing you from posting data in requested format?

firejackmusic
Starting Member

15 Posts

Posted - 2009-10-26 : 05:25:20

What data do you need?

firejackmusic
Starting Member

15 Posts

Posted - 2009-10-26 : 06:12:32

Like this


id	idCustomerBid	idProduct	bidAmount	bidDate
144	1	268	45.00	2009-10-22 10:49:57.460
145	1	268	65.00	2009-10-22 10:50:00.803
146	1	271	25.00	2009-10-22 11:10:25.150
147	4	268	60.00	2009-10-22 12:13:13.320
148	4	268	80.00	2009-10-22 12:13:19.053
149	4	268	100.00	2009-10-22 12:13:22.267
137	1	268	25.00	2009-10-22 09:25:15.530
138	1	269	25.00	2009-10-22 09:26:08.463
139	1	272	25.00	2009-10-22 09:26:28.590
140	1	273	25.00	2009-10-22 09:30:15.520
141	4	268	25.00	2009-10-22 09:30:39.337
142	4	268	45.00	2009-10-22 09:30:44.393
143	4	276	75.00	2009-10-22 09:40:45.807

products table only has one column it updates which is PRICE

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-27 : 13:53:36

see how to post a question
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

firejackmusic
Starting Member

15 Posts

Posted - 2009-10-29 : 08:25:13

Ok here goes ..

1. I am looking through the above data when an item in a auction closes.

2. The item that ends using this code


sqlUpdate = "update products set listhidden = 1 where idproduct = "&pidProduct

3. Using that id idproduct I can then scan the data below by idproduct


id	idCustomerBid	idProduct	bidAmount	bidDate
144	1	             268	45.00	2009-10-22 10:49:57.460
145	1	             268	65.00	2009-10-22 10:50:00.803
146	1	             271	25.00	2009-10-22 11:10:25.150
147	4	             268	60.00	2009-10-22 12:13:13.320
148	4	             268	80.00	2009-10-22 12:13:19.053
149	4	             268	100.00	2009-10-22 12:13:22.267
137	1	             268	25.00	2009-10-22 09:25:15.530
138	1	             269	25.00	2009-10-22 09:26:08.463
139	1	             272	25.00	2009-10-22 09:26:28.590
140	1	             273	25.00	2009-10-22 09:30:15.520
141	4	             268	25.00	2009-10-22 09:30:39.337
142	4	             268	45.00	2009-10-22 09:30:44.393
143	4	             276	75.00	2009-10-22 09:40:45.807

4. I then need to get the highbid of that item and return the highest bid but if there are multiple bids by other users I need the proxy bid (definition-

.... in which the winning bidder pays the price of the second-highest bid plus a defined increment.....) and the customers id

5. I then need to calculate all the bids by that user from the bidhistory table and repeat the search to create an invoice -

I hope this helps, Its a tall order I know but i need the automation applying, if there are other examples I would love to hear from anyone

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-31 : 02:08:57

are you using sql 2005?

firejackmusic
Starting Member

15 Posts

Posted - 2009-10-31 : 05:11:32

Sql 2008

firejackmusic
Starting Member

15 Posts

Posted - 2009-10-31 : 05:13:12

I'm wondering what solution to use, the server checks if enddate is less than now date then what really should happen? it would i assume always been creating invoices and checking who has won and what price they won it, not their high proxy bid but next one that is one increment higher than another bidder

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources