A merchant creates an advertising campaign with (let's say) a total budget of 100. He then adds products to his advertising campaign with the option to set his own Cost-Per-Click (default 0.01); the higher the CPC he sets, the higher ranking his product(s) will be.Each time a user of the site clicks on his advert it adds a record to the database with timestamp of click, merchant_id, product_id, ip_address and CPC price (at the time of click).Now I add up all the CPCs (after the merchant's last top-up) and deduct it from his most recent budget. When his budget has run out the adverts are removed from the listings until he tops up again.The code I have works as expected but I'm just wondering if anyone could help simplify the script or knows a better way to achieve my desired results.<%Sub Sponsors()dim sql_string : sql_string = dim rsMerchantsdim rsBudgetdim cpc_costdim rsProducts'#########################################################################################################################################################################'#########################################################################################################################################################################Set rsMerchants = iconn.Execute(SELECT accounts.account_id, AffiliateWindow.account_id, AffiliateWindow.product_id from accounts INNER JOIN AffiliateWindow ON accounts.account_id = AffiliateWindow.account_id GROUP BY AffiliateWindow.account_id)If Not rsMerchants.EOF thenDo while not rsMerchants.eof '##################################################################Set rsBudget = iconn.Execute(SELECT aclk.account_id, aclk.action, SUM(aclk.cpc) AS totalCPC, aclk.timestamp FROM aclk WHERE aclk.action = 'Clicked' AND aclk.timestamp >= ' & topUpTimeStamp(rsMerchants(account_id)) & ' AND aclk.account_id = ' & rsMerchants(account_id) & ' GROUP BY aclk.account_id)If Not rsBudget.EOF thencpc_cost = FormatNumber(rsBudget(totalCPC),2) Else cpc_cost = FormatNumber(0,2) End IfrsBudget.Closeset rsBudget = nothing'##################################################################If topUpAmount(rsMerchants(account_id)) >= cpc_cost thensql_string = sql_string & AffiliateWindow.account_id = ' & rsMerchants(account_id) & ' OR end if      rsMerchants.MoveNextloopend ifrsMerchants.Closeset rsMerchants = nothing'#########################################################################################################################################################################'#########################################################################################################################################################################IF sql_string <>  THENsql_string = ( & LEFT(sql_string, (LEN(sql_string)-3)) & ) ANDELSEsql_string = AffiliateWindow.account_id = '0' ANDEND IFset rsProducts = iconn.Execute(SELECT AffiliateWindow.account_id, AffiliateWindow.product_id, AffiliateWindow.product_name, AffiliateWindow.display, AffiliateWindow.merchant_name, AffiliateWindow.search_price, AffiliateWindow.merchant_image_url, AffiliateWindow.cpc, accounts.account_id, accounts.merchant_budget FROM AffiliateWindow INNER JOIN accounts ON AffiliateWindow.account_id = accounts.account_id WHERE ( & LEFT(sql_string, (LEN(sql_string)-3)) & ) AND AffiliateWindow.display = '1' ORDER BY AffiliateWindow.cpc DESC, RAND() LIMIT 6)If Not rsProducts.EOF thenResponse.Write<ul>Do while not rsProducts.eof'###############################'   DISPLAY ADS HERE'###############################rsProducts.MoveNextloopResponse.Write</ul>End If  rsProducts.Closeset rsProducts = nothing    End SubFunction topUpAmount(intAccount_id)dim rsMerchantBudgetSet rsMerchantBudget = iconn.Execute(SELECT cpc, account_id, action, timestamp FROM aclk WHERE account_id = ' & intAccount_id & ' AND action = 'topup' ORDER BY timestamp DESC)If NOT rsMerchantBudget.EOF ThentopUpAmount = FormatNumber(rsMerchantBudget(cpc),2)   End IfrsMerchantBudget.Closeset rsMerchantBudget = nothingEnd FunctionFunction topUpTimeStamp(intAccount_id)dim rsLastTopUpSet rsLastTopUp = iconn.Execute(SELECT cpc, account_id, action, timestamp FROM aclk WHERE account_id = ' & intAccount_id & ' AND action = 'topup' ORDER BY timestamp DESC LIMIT 1)If NOT rsLastTopUp.EOF ThentopUpTimeStamp = rsLastTopUp(timestamp)   End IfrsLastTopUp.Closeset rsLastTopUp = nothingEnd Function%>
Advertising campaign script
database;vbscript;asp classic