[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 729
  • Last Modified:

perform select records - calculation and update records. Stock update feature

Hi

I'm at the last linkin the chain off an online store. Everythingis functioning but I need to add a feature and can't translate what's in my head to asp vb....

basically, the cart has an array of details of the items in the visitors cart, including a STOCK ID number shown as : <%=WA_eCart_DisplayInfo(magecart, "StockID")%>  Which relates the stock quantity of a given product (couldn't be done with a ProdID as the same product can come in different sizes and colours).

What I ('THINK') I need to do is to-
Create a loop of the stockID and Quantities in cart
Select from existing stock Table where stock ID = cart stock ID and get existing quantity.
subtract quantiy in cart from quantity in table and give it a variabe(? or add 'i' after to increment?)
Then update able Stock with the new quantity

(I will add a If quantity is 0 the email admin with an item b is out of stock visitor won't/doesn't need to see this)

Doeas that sound right? If so help please.....If not is here a simpler way to do this.

Thanks in advance for your help. (I'll post the page itself here, let me know if it would help to see the cart code too).




<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="WA_eCart/WA_eCart_Definition_VB.asp" -->
<!--#include file="WA_eCart/WA_eCart_Database_VB.asp" -->
<!--#include file="WA_eCart/magecart_VB.asp" -->
<!--#include file="Connections/MagicalRooms.asp" -->
 
<%
Dim RsGetStockIDs__ParamSession
RsGetStockIDs__ParamSession = "699633794"
If ((Session("OrderNumber")) <> "") Then 
  RsGetStockIDs__ParamSession = (Session("OrderNumber"))
End If
%>
 
<%
if (cStr(Request.Form("Submit order.x")) <> "") then
  Session("OrderNumber") = ""
end if
%>
<%
if (cStr(cStr(Request.Form("frmshipping"))) <> "") then
  Session("ShipOption") = "" & cStr(cStr(Request.Form("frmshipping"))) & ""
end if
%>
 
 
 
<%' WA eCart Store Cart Summary in Db
if ("" = "")  then
  WA_connection = MM_MagicalRooms_STRING
  WA_table = "tblOrders"
  WA_redirectURL = ""
  WA_indexField = "OrderID"
  WA_fieldNamesStr = "OrderReferenceID|OrderUserEmail|OrderShipping|OrderTotal|OrderWrappingInfo|OrderStatus"
  WA_fieldValuesStr = "" & cStr(Session.SessionID) & "" & "|" & "" & cStr(cStr(Request.Form("email"))) & "" & "|" & "" & cStr(WA_eCart_GetShipping(magecart)) & "" & "|" & "" & cStr(WA_eCart_GrandTotal(magecart)) & "" & "|" & "" & cStr(cStr(Request.Form("frmwrappinginfo"))) & "" & "|" & "0"
  WA_columnTypesStr = "',none,''|',none,''|none,none,NULL|none,none,NULL|',none,''|none,none,NULL"
  WA_sessionName = "OrderNumber"
  indexFieldIndex = -1
  WA_fieldValues = Split(WA_fieldValuesStr,"|")
  WA_fieldNames = Split(WA_fieldNamesStr,"|")
  WA_columns = Split(WA_columnTypesStr,"|")
  set WA_editCmd = Server.CreateObject("ADODB.Command")
  updateFieldValue = ""
  WA_editCmd.ActiveConnection = WA_connection
  if (WA_redirectURL <> "" AND Request.QueryString <> "" AND Request.QueryString.Count > 0) then
    if (inStr(WA_redirectURL,"?") > 0)  then
	  WA_redirectURL = WA_redirectURL & "&"
	else
	  WA_redirectURL = WA_redirectURL & "?"
	end if
    WA_redirectURL = WA_redirectURL & Request.QueryString
  end if
  for i = 0 to Ubound(WA_fieldNames)
    if (WA_indexField  = WA_fieldNames(i)) then
      indexFieldIndex = i
      exit for
    end if
  next
  if (indexFieldIndex >= 0) then  updateFieldValue = WA_fieldValues(indexFieldIndex)
  if (updateFieldValue = "") then updateFieldValue = cStr(Session(WA_sessionName))
  updateColType = "none,none,NULL"
  if (indexFieldIndex >= 0)  then
     updateColType = WA_columns(indexFieldIndex)
  end if
  if (updateFieldValue <> "" AND updateFieldValue <> "undefined")  then
      valueForWhere = WA_generateInsertParams(Array(WA_indexField), Array(updateColType), Array(updateFieldValue), -1)
      sqlstr = "select " & WA_indexField & " from " & WA_table & " where " & WA_indexField & " = " & valueForWhere(2) & " order by " & WA_indexField & " DESC"
      set WA_eCartRecordset = Server.CreateObject("ADODB.Recordset")
      WA_eCartRecordset.ActiveConnection = WA_connection
      WA_eCartRecordset.Source = sqlstr
      WA_eCartRecordset.CursorType = 0
      WA_eCartRecordset.CursorLocation = 2
      WA_eCartRecordset.LockType = 1
      WA_eCartRecordset.Open()
	  if (NOT WA_eCartRecordset.EOF)  then  
	    updateFieldValue = cStr(WA_eCartRecordset.Fields.Item(WA_indexField).Value)
	  else
	    updateFieldValue = ""
	  end if
	  WA_eCartRecordset.Close()
  end if
  if (updateFieldValue <> "" AND updateFieldValue <> "undefined")  then
    updateParamsObj = WA_generateInsertParams(WA_fieldNames, WA_columns, WA_fieldValues, indexFieldIndex)
	  valueForWhere = WA_generateInsertParams(Array(WA_indexField), Array(updateColType), Array(updateFieldValue), -1)
	  WA_editCmd.CommandText = "update " & WA_table & " SET " & updateParamsObj(3) & " where " & WA_indexField & " = " & valueForWhere(2)
    WA_editCmd.Execute()
	  WA_editCmd.ActiveConnection.Close()
  else
    updateFieldValue = ""
    insertParamsObj = WA_generateInsertParams(WA_fieldNames, WA_columns, WA_fieldValues, -1)
    WA_editCmd.CommandText = "insert into " & WA_table & " (" & insertParamsObj(1) & ") values (" & insertParamsObj(2) & ")"
    WA_editCmd.Execute()
    WA_editCmd.ActiveConnection.Close()
  end if
  if (indexFieldIndex < 0)  then
	if (updateFieldValue = "")  then
      obj = WA_generateWhereClause(WA_fieldNames, WA_columns, WA_fieldValues, indexFieldIndex)
      sqlstr = "select " & WA_indexField & " from " & WA_table & " where " &  obj & " order by " & WA_indexField & " DESC"
      set WA_eCartRecordset = Server.CreateObject("ADODB.Recordset")
      WA_eCartRecordset.ActiveConnection = WA_connection
      WA_eCartRecordset.Source = sqlstr
      WA_eCartRecordset.CursorType = 0
      WA_eCartRecordset.CursorLocation = 2
      WA_eCartRecordset.LockType = 1
      WA_eCartRecordset.Open()
	  if (NOT WA_eCartRecordset.EOF)  then  Session(WA_sessionName) = WA_eCartRecordset.Fields.Item(WA_indexField).Value
	  WA_eCartRecordset.Close()
	else
	  Session(WA_sessionName) = updateFieldValue
	end if
  else
    Session(WA_sessionName) = WA_fieldValues(indexFieldIndex)
  end if
  if (WA_redirectURL <> "")  then
    Response.Redirect(WA_redirectURL)
  end if
end if
%>
<%' WA eCart Store Cart Details in Db
if ("" = "") then
  WA_connection = MM_MagicalRooms_STRING
  WA_table = "tblOrderDetails"
  WA_redirectURL = ""
  WA_indexField = "DetailOrderID"
  WA_fieldNamesStr = "DetailOrderID|DetailStockID|DetailProductID|DetailProductName|DetailProductDesc|DetailQuantity|DetailPrice|DetailOrderSize|DetailOrderColour"
  WA_fieldValuesStr = "" & cStr( Session("OrderNumber") ) & "" & "|" & "" & cStr(WA_eCart_DisplayInfo(magecart, "StockID")) & "" & "|" & "" & cStr(WA_eCart_DisplayInfo(magecart, "ID")) & "" & "|" & "" & cStr(WA_eCart_DisplayInfo(magecart, "Name")) & "" & "|" & "" & cStr(WA_eCart_DisplayInfo(magecart, "Description")) & "" & "|" & "" & cStr(WA_eCart_DisplayInfo(magecart, "Quantity")) & "" & "|" & "" & cStr(WA_eCart_DisplayInfo(magecart, "Price")) & "" & "|" & "" & cStr(WA_eCart_DisplayInfo(magecart, "Size")) & "" & "|" & "" & cStr(WA_eCart_DisplayInfo(magecart, "Colour")) & ""
  WA_columnTypesStr = "none,none,NULL|none,none,NULL|none,none,NULL|',none,''|',none,''|none,none,NULL|none,none,NULL|',none,''|',none,''"
  indexFieldIndex = -1
  WA_fieldValues = Split(WA_fieldValuesStr,"|")
  if (WA_redirectURL <> "" AND Request.QueryString <> "" AND Request.QueryString.Count > 0) then
    if (inStr(WA_redirectURL,"?") > 0)  then
	  WA_redirectURL = WA_redirectURL & "&"
	else
	  WA_redirectURL = WA_redirectURL & "?"
	end if
    WA_redirectURL = WA_redirectURL & Request.QueryString
  end if
  WA_cmdObjString = "ADODB.Command"
  set WA_editCmd = Server.CreateObject(WA_cmdObjString)
  WA_editCmd.ActiveConnection = WA_connection
  WA_fieldNames = Split(WA_fieldNamesStr,"|")
  WA_columns = Split(WA_columnTypesStr,"|")
  for i = 0 to Ubound(WA_fieldNames)
    if (WA_indexField  = WA_fieldNames(i)) then
      indexFieldIndex = i
      exit for
    end if
  next
  deleteFieldValue = cStr(WA_fieldValues(indexFieldIndex))
  if (deleteFieldValue <> "" AND deleteFieldValue <> "undefined") then
    deleteParamsObj = WA_generateInsertParams(Array(WA_indexField), Array(WA_columns(indexFieldIndex)), Array(deleteFieldValue), -1)
    WA_editCmd.CommandText = "Delete from " & WA_table & " where " & WA_indexField & " = " & deleteParamsObj(2)
    WA_editCmd.Execute()
  end if
  set magecart = WA_eCart_MoveFirst(magecart)
  while (NOT WA_eCart_EOF(magecart))
    WA_fieldValuesStr = "" & cStr( Session("OrderNumber") ) & "" & "|" & "" & cStr(WA_eCart_DisplayInfo(magecart, "StockID")) & "" & "|" & "" & cStr(WA_eCart_DisplayInfo(magecart, "ID")) & "" & "|" & "" & cStr(WA_eCart_DisplayInfo(magecart, "Name")) & "" & "|" & "" & cStr(WA_eCart_DisplayInfo(magecart, "Description")) & "" & "|" & "" & cStr(WA_eCart_DisplayInfo(magecart, "Quantity")) & "" & "|" & "" & cStr(WA_eCart_DisplayInfo(magecart, "Price")) & "" & "|" & "" & cStr(WA_eCart_DisplayInfo(magecart, "Size")) & "" & "|" & "" & cStr(WA_eCart_DisplayInfo(magecart, "Colour")) & ""
    WA_fieldValues = Split(WA_fieldValuesStr,"|")
    insertParamsObj = WA_generateInsertParams(WA_fieldNames, WA_columns, WA_fieldValues, -1)
    WA_editCmd.CommandText = "insert into " & WA_table & " (" & insertParamsObj(1) & ") values (" & insertParamsObj(2) & ")"
    WA_editCmd.Execute()
    set magecart = WA_eCart_MoveNext(magecart)
  wend
  set magecart = WA_eCart_MoveFirst(magecart)
  WA_editCmd.ActiveConnection.Close()
  if (WA_redirectURL <> "")  then
    Response.Redirect(WA_redirectURL)
  end if
end if
%>
<%
%>
<%
Dim RsGetStockIDs
Dim RsGetStockIDs_cmd
Dim RsGetStockIDs_numRows
 
Set RsGetStockIDs_cmd = Server.CreateObject ("ADODB.Command")
RsGetStockIDs_cmd.ActiveConnection = MM_MagicalRooms_STRING
RsGetStockIDs_cmd.CommandText = "SELECT tblOrderDetails.DetailStockID, tblOrderDetails.DetailQuantity FROM tblOrders LEFT JOIN tblOrderDetails ON tblOrders.OrderID = tblOrderDetails.DetailOrderID WHERE tblOrders.OrderReferenceID= ? GROUP BY tblOrderDetails.DetailStockID, tblOrderDetails.DetailQuantity" 
RsGetStockIDs_cmd.Prepared = true
RsGetStockIDs_cmd.Parameters.Append RsGetStockIDs_cmd.CreateParameter("param1", 200, 1, 255, RsGetStockIDs__ParamSession) ' adVarChar
 
Set RsGetStockIDs = RsGetStockIDs_cmd.Execute
RsGetStockIDs_numRows = 0
%>
<%
Dim RsStock
Dim RsStock_cmd
Dim RsStock_numRows
 
Set RsStock_cmd = Server.CreateObject ("ADODB.Command")
RsStock_cmd.ActiveConnection = MM_MagicalRooms_STRING
RsStock_cmd.CommandText = "SELECT * FROM tblStock" 
RsStock_cmd.Prepared = true
 
Set RsStock = RsStock_cmd.Execute
RsStock_numRows = 0
%>
<%
Dim MM_editAction
MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
If (Request.QueryString <> "") Then
  MM_editAction = MM_editAction & "?" & Server.HTMLEncode(Request.QueryString)
End If
 
' boolean to abort record edit
Dim MM_abortEdit
MM_abortEdit = false
%>
<%
' IIf implementation
Function MM_IIf(condition, ifTrue, ifFalse)
  If condition = "" Then
    MM_IIf = ifFalse
  Else
    MM_IIf = ifTrue
  End If
End Function
%>
 
<%
if (WA_eCart_IsEmpty(magecart))     then
  magecart_redirStr = "bag.asp"'_redirStr
  magecart_cartAction = "RedirectIfEmpty"
end if
%>
<%
' WA eCart Redirect
if (magecart_redirStr <> "")     then
  Response.Redirect(magecart_redirStr)
end if
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml"><!-- InstanceBegin template="/MR07 OffLine/templates/master1.dwt.asp" codeOutsideHTMLIsLocked="false" -->
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<!-- InstanceBeginEditable name="doctitle" -->
<title>Untitled Document</title>
<!-- InstanceEndEditable --><!-- InstanceBeginEditable name="head" -->
<link href="WA_eCart/CSS/eC_Refreshing_GranitePine_Tahoma.css" rel="stylesheet" type="text/css" />
<!-- InstanceEndEditable -->
<link href="magicalrooms.css" rel="stylesheet" type="text/css" />
<script type="text/javascript">
<!--
function MM_preloadImages() { //v3.0
  var d=document; if(d.images){ if(!d.MM_p) d.MM_p=new Array();
    var i,j=d.MM_p.length,a=MM_preloadImages.arguments; for(i=0; i<a.length; i++)
    if (a[i].indexOf("#")!=0){ d.MM_p[j]=new Image; d.MM_p[j++].src=a[i];}}
}
 
function MM_swapImgRestore() { //v3.0
  var i,x,a=document.MM_sr; for(i=0;a&&i<a.length&&(x=a[i])&&x.oSrc;i++) x.src=x.oSrc;
}
 
function MM_findObj(n, d) { //v4.01
  var p,i,x;  if(!d) d=document; if((p=n.indexOf("?"))>0&&parent.frames.length) {
    d=parent.frames[n.substring(p+1)].document; n=n.substring(0,p);}
  if(!(x=d[n])&&d.all) x=d.all[n]; for (i=0;!x&&i<d.forms.length;i++) x=d.forms[i][n];
  for(i=0;!x&&d.layers&&i<d.layers.length;i++) x=MM_findObj(n,d.layers[i].document);
  if(!x && d.getElementById) x=d.getElementById(n); return x;
}
 
function MM_swapImage() { //v3.0
  var i,j=0,x,a=MM_swapImage.arguments; document.MM_sr=new Array; for(i=0;i<(a.length-2);i+=3)
   if ((x=MM_findObj(a[i]))!=null){document.MM_sr[j++]=x; if(!x.oSrc) x.oSrc=x.src; x.src=a[i+2];}
}
//-->
</script>
<!-- InstanceParam name="bag" type="boolean" value="true" --><!-- InstanceParam name="leftNav" type="boolean" value="true" --><!-- InstanceParam name="admin nav" type="boolean" value="false" --><!-- InstanceParam name="site nav" type="boolean" value="true" -->
</head>
<body onload="MM_preloadImages('img/images/wish_list_selected_03.gif','img/images/log_in_selected_03.gif','img/images/register_selected_03.gif','img/images/delivery_selected_03.gif','img/images/locator_selected_03.gif','img/images/help_selected_03.gif','img/images/voucher_selected_03.gif')">
<div id="container">
   
  <div id="banner">
    <div id="logo"></div>
	<!--#include file="keyword_search.asp" -->
	<div id="tabs"><img src="img/images/full_tabs_03.gif" name="fulltabs" width="467" height="23" border="0" usemap="#tab" id="fulltabs" />
        <map name="tab" id="tab">
          <area shape="rect" coords="2,2,56,21" href="wish_list.asp" onmouseover="MM_swapImage('fulltabs','','img/images/wish_list_selected_03.gif',1)" onmouseout="MM_swapImgRestore()" />
          <area shape="rect" coords="58,3,97,21" href="login.asp" onmouseover="MM_swapImage('fulltabs','','img/images/log_in_selected_03.gif',1)" onmouseout="MM_swapImgRestore()" />
          <area shape="rect" coords="99,3,146,21" href="register.asp" onmouseover="MM_swapImage('fulltabs','','img/images/register_selected_03.gif',1)" onmouseout="MM_swapImgRestore()" />
          <area shape="rect" coords="149,3,196,23" href="#" onmouseover="MM_swapImage('fulltabs','','img/images/delivery_selected_03.gif',1)" onmouseout="MM_swapImgRestore()" />
          <area shape="rect" coords="200,3,316,22" href="#" onmouseover="MM_swapImage('fulltabs','','img/images/locator_selected_03.gif',1)" onmouseout="MM_swapImgRestore()" />
          <area shape="rect" coords="319,5,350,22" href="#" onmouseover="MM_swapImage('fulltabs','','img/images/help_selected_03.gif',1)" onmouseout="MM_swapImgRestore()" />
          <area shape="rect" coords="355,5,455,21" href="#" onmouseover="MM_swapImage('fulltabs','','img/images/voucher_selected_03.gif',1)" onmouseout="MM_swapImgRestore()" />
        </map>
    </div>
  </div>
 
  <div id="mainpage">
    <div id="leftnav">
      <script type="text/javascript" language="JavaScript" src="Scripts/drop.js">
	</script>
      
      <!--#include file="nav.asp" -->
      
    <!--#include file="bag_display.asp"-->
    </div>
  <!-- InstanceBeginEditable name="contentarea" -->
<div id="content">    
<div class="eC_Refreshing_GranitePine_Tahoma">
	<%
'WA eCart Show If Start
if (NOT WA_eCart_IsEmpty(magecart))     then
%>
	<h2>Order Confirmation</h2>
	<table class="eC_ShoppingCart" border="0" cellspacing="0" cellpadding="0">
			<tr><%=WA_eCart_DisplayInfo(magecart, "StockID")%>
				
					<th>Name</th>
				
 
					<th>Description</th>
				
 
					<th>Price</th>
				
 
					<th>Quantity</th>
				
 
					<th>Size</th>
				
 
					<th>Colour</th>
				
 
					<th>Total</th>
				
			</tr>
		<%
  while (NOT WA_eCart_EOF(magecart))
%>
			<tr>
				
					<td><%=WA_eCart_DisplayInfo(magecart, "Name")%></td>
				
 
					<td><%=WA_eCart_DisplayInfo(magecart, "Description")%></td>
				
 
					<td  class="eC_PriceItem" ><%=WA_eCart_DisplayMoney(magecart, WA_eCart_DisplayInfo(magecart, "Price"))%></td>
				
 
					<td  class="eC_FormItem" ><%=WA_eCart_DisplayInfo(magecart, "Quantity")%></td>
				
 
					<td><%=WA_eCart_DisplayInfo(magecart, "Size")%></td>
				
 
					<td><%=WA_eCart_DisplayInfo(magecart, "Colour")%></td>
				
 
					<td  class="eC_PriceItem" ><%=WA_eCart_DisplayMoney(magecart, WA_eCart_DisplayInfo(magecart, "TotalPrice"))%></td>
				
			</tr>
		<%
    set magecart = WA_eCart_MoveNext(magecart)
  wend
  set magecart = WA_eCart_MoveFirst(magecart)
%>
	</table>
	<h3>Order Summary</h3>
	<div class="eC_OrderSummary">
		<table border="0" cellpadding="0" cellspacing="0" class="eC_CartSummary">
			<%
'WA eCart Merchandizing Show Start
'ecart="magecart"
if (WA_eCart_GetDiscounts(magecart) > 0 OR WA_eCart_GetCharges(magecart) > 0 OR WA_eCart_GetShipping(magecart) > 0 OR WA_eCart_GetTax(magecart) > 0)     then
%>
				<tr>
					<td class="eC_SummaryLabel">Sub-total</td>
					<td><%=WA_eCart_DisplayMoney(magecart, WA_eCart_TotalColumn(magecart, "TotalPrice"))%></td>
				</tr>
			<%
'WA eCart Merchandizing Show End
'ecart="magecart"
end if
%>
			<%
'WA eCart Merchandizing Show Start
'ecart="magecart"
if (WA_eCart_GetDiscounts(magecart) > 0)     then
%><%
'WA eCart Merchandizing Show End
'ecart="magecart"
end if
%><%
'WA eCart Merchandizing Show Start
'ecart="magecart"
if (WA_eCart_GetCharges(magecart) > 0)     then
%><%
'WA eCart Merchandizing Show End
'ecart="magecart"
end if
%><%
'WA eCart Merchandizing Show Start
'ecart="magecart"
if (WA_eCart_GetShipping(magecart) > 0)     then
%>
					<tr>
					  <td class="eC_SummaryLabel">Additional Charges </td>
					  <td>&nbsp;</td>
			  </tr>
				<%
if (NOT WA_eCart_Rules_EOF(magecart, "Charges")) then
	while (NOT WA_eCart_Rules_EOF(magecart, "Charges"))
		if (WA_eCart_RuleLooperValue(magecart, "Charges") > 0) then
%>
					<tr>
						<td class="eC_IndividualLabel"><%=WA_eCart_RuleLooperName(magecart, "Charges")%></td>
						<td class="eC_IndividualCharge"><%=WA_eCart_DisplayMoney(magecart, WA_eCart_RuleLooperValue(magecart, "Charges"))%></td>
					</tr>
	  <%
		end if
		set magecart = WA_eCart_Rules_MoveNext(magecart, "Charges")
	wend
	set magecart = WA_eCart_Rules_MoveFirst(magecart, "Charges")
end if
%>	<tr>
						<td class="eC_SummaryLabel">Shipping</td>
						<td>&nbsp;</td>
					</tr>
				<%
if (NOT WA_eCart_Rules_EOF(magecart, "Shipping")) then
	while (NOT WA_eCart_Rules_EOF(magecart, "Shipping"))
		if (WA_eCart_RuleLooperValue(magecart, "Shipping") > 0) then
%>
					<tr>
						<td class="eC_IndividualLabel"><%=WA_eCart_RuleLooperName(magecart, "Shipping")%></td>
						<td class="eC_IndividualCharge"><%=WA_eCart_DisplayMoney(magecart, WA_eCart_RuleLooperValue(magecart, "Shipping"))%></td>
					</tr>
				<%
		end if
		set magecart = WA_eCart_Rules_MoveNext(magecart, "Shipping")
	wend
	set magecart = WA_eCart_Rules_MoveFirst(magecart, "Shipping")
end if
%><%
'WA eCart Merchandizing Show End
'ecart="magecart"
end if
%><%
'WA eCart Merchandizing Show Start
'ecart="magecart"
if (WA_eCart_GetTax(magecart) > 0)     then
%><%
'WA eCart Merchandizing Show End
'ecart="magecart"
end if
%>
			
				<tr class="eC_SummaryFooter">
					<td class="eC_SummaryLabel">Total:</td>
					<td><%=WA_eCart_DisplayMoney(magecart, WA_eCart_GrandTotal(magecart))%></td>
				</tr>
		</table>
	</div>
	<%
'WA eCart Show If Middle
else
%><table><tr><td>The cart is empty</td></tr></table><%
'WA eCart Show If End
end if
%>
	
	<div class="eC_PurchaserInfoWrapper">
		<h3 class="eC_InfoHeader">Billing Information</h3>
		<div class="eC_InfoContainer">
			<p class="eC_OrderInfo"><%=Request.Form("firstname")%>&nbsp;<%=Request.Form("lastname")%><br />
				<%=Request.Form("street1")%><br />
				<%=Request.Form("city")%>,&nbsp;<%=Request.Form("state_province")%>&nbsp;<%=Request.Form("postcode")%><br />
				<%=Request.Form("country")%></p>
		</div>
		<h3 class="eC_InfoHeader">Gift Wrapping Information</h3>
		<div class="eC_InfoContainer">
			<p class="eC_OrderInfo"><%=cStr(Request.Form("frmwrappinginfo"))%></p>
		</div>
		<h3 class="eC_InfoHeader">Shipping Information</h3>
		<div class="eC_InfoContainer">
			<p class="eC_OrderInfo"><%=Request.Form("shipping_firstname")%>&nbsp;<%=Request.Form("shipping_lastname")%><br />
				<%=Request.Form("shipping_street1")%><br />
				<%=Request.Form("shipping_city")%>,&nbsp;<%=Request.Form("shipping_state_province")%>&nbsp;<%=Request.Form("shipping_postcode")%><br />
				<%=Request.Form("shipping_country")%><br />
				<%=Request.Form("shipping_phone")%>
			</p>
		</div>
	</div>
	
	
 
	
	<div class="eC_CheckoutFormWrapper">
		<form name="checkout_PP_LI_Hid" action="https://www.sandbox.paypal.com/uk/cgi-bin/webscr" method="post">
			
			<table class="eC_ButtonWrapper" border="0" cellpadding="0" cellspacing="0">
				<tr>
					<td>
					<input type="hidden" name="business" id="business" value="" />
<%magecart_Index = magecart.DisplayIndex - 2%><%
while (NOT WA_eCart_EOF(magecart))
%>
<%magecart_Index = magecart_Index + 1%>
 
<% 'WA eCart Validation 
if ((WA_eCart_FormatNumber(WA_eCart_DisplayInfo(magecart, "Price") - ((WA_eCart_DisplayInfo(magecart, "Price")/WA_eCart_TotalColumn(magecart, "TotalPrice")) * WA_eCart_GetDiscounts(magecart)), false, 2) > 0)) then
%>
<input type="hidden" name="amount_<%=magecart_Index+1%>" id="amount_<%=magecart_Index+1%>" value="<%= WA_eCart_FormatNumber(WA_eCart_DisplayInfo(magecart, "Price") - ((WA_eCart_DisplayInfo(magecart, "Price")/WA_eCart_TotalColumn(magecart, "TotalPrice")) * WA_eCart_GetDiscounts(magecart)), false, 2) %>" />
<input type="hidden" name="item_name_<%=magecart_Index+1%>" id="item_name_<%=magecart_Index+1%>" value="<%=WA_eCart_DisplayInfo(magecart, "Name")%>" />
 
<input type="hidden" name="quantity_<%=magecart_Index+1%>" id="quantity_<%=magecart_Index+1%>" value="<%=WA_eCart_DisplayInfo(magecart, "Quantity")%>" />
<input type="hidden" name="item_number<%=magecart_Index+1%>" id="item_number<%=magecart_Index+1%>" value="<%=WA_eCart_DisplayInfo(magecart, "StockID")%>" />
<input type="hidden" name="on0_<%=magecart_Index+1%>" id="on0_<%=magecart_Index+1%>" value="" />
<input type="hidden" name="os0_<%=magecart_Index+1%>" id="os0_<%=magecart_Index+1%>" value="" />
<input type="hidden" name="on1_<%=magecart_Index+1%>" id="on1_<%=magecart_Index+1%>" value="" />
<input type="hidden" name="os1_<%=magecart_Index+1%>" id="os1_<%=magecart_Index+1%>" value="" />
<% 'WA eCart Validation 
else
magecart_Index = magecart_Index - 1
end if
%>
<%
  set magecart = WA_eCart_MoveNext(magecart)
wend
set magecart = WA_eCart_MoveFirst(magecart)
%>
<input type="hidden" name="handling_cart" id="handling_cart" value="<%= (WA_eCart_GetShipping(magecart) + WA_eCart_GetCharges(magecart))%>" />
<input type="hidden" name="tax_cart" id="tax_cart" value="<%=WA_eCart_GetTax(magecart)%>" />
<input type="hidden" name="image_url" id="image_url" value="" />
<input type="hidden" name="no_note" id="no_note" value="0" />
<input type="hidden" name="no_shipping" id="no_shipping" value="0" />
<input type="hidden" name="cancel_return" id="cancel_return" value="" />
<input type="hidden" name="return" id="return" value="" />
<input type="hidden" name="cmd" id="cmd" value="_cart" />
<input type="hidden" name="upload" id="upload" value="1" />
<input type="hidden" name="currency_code" id="currency_code" value="GBP" />
<input type="hidden" name="address_override" id="address_override" value="1" />
<input type="hidden" name="first_name" id="first_name" value="<%=Request.Form("shipping_firstname")%>" />
<input type="hidden" name="last_name" id="last_name" value="<%=Request.Form("shipping_lastname")%>" />
<input type="hidden" name="address1" id="address1" value="<%=Request.Form("shipping_street1")%>" />
<input type="hidden" name="address2" id="address2" value="<%=Request.Form("shipping_street2")%>" />
<input type="hidden" name="city" id="city" value="<%=Request.Form("shipping_city")%>" />
<input type="hidden" name="state" id="state" value="<%=Request.Form("shipping_state_province")%>" />
<input type="hidden" name="zip" id="zip" value="<%=Request.Form("shipping_postcode")%>" />
<input type="hidden" name="email" id="email" value="<%=Request.Form("email")%>" />
<input type="hidden" name="night_phone_b" id="night_phone_b" value="<%=Request.Form("phone")%>" />
					<input type="image" alt="Modify information" value="Modify information" src="WA_eCart/Images/Granite Pine/Btn2_EN_modifyinformation.gif" onclick="history.back();return false;"/><input type="image" name="Submit order" id="Submit order" value="Checkout" alt="Submit order" src="WA_eCart/Images/Granite Pine/Btn2_EN_submitorder.gif"  />
					</td>
				</tr>
			</table>
		
      
</form>
	
</div>
 
	
	<p class="eC_SmallPrint">Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet.</p>
 
</div></div>
  <!-- InstanceEndEditable --></div>
<div class="footer" id="footer"><a href="page.html">How To Find Us</a>  <a href="page.html">Terms  &amp; Conditions</a>  - <a href="page.html">Privacy
  Policy</a> - Returns - Links - Customer Feedback<br />
  &copy; Copyright Magical Rooms 2007</div>
</div></body>
<!-- InstanceEnd --></html>
<%
RsGetStockIDs.Close()
Set RsGetStockIDs = Nothing
%>
<%
RsStock.Close()
Set RsStock = Nothing
%>

Open in new window

0
swordfishsoup
Asked:
swordfishsoup
  • 8
  • 5
  • 2
1 Solution
 
BadotzCommented:
>>couldn't be done with a ProdID as the same product can come in different sizes and colours

Well, there's your problem...

Attributes of products indicate different products, and should therefore have unique PRODUCT_ID fields. A red widget is not the same as a blue widget.

Either that, or the widget is the master, and the size and the color are each in a separate table:

table: widget
id: number
description: string
...
...

table: widget_size
id: number
widget_id: number (foreign key)
size: string
on-hand: number
...
...

table: widget_color
id: number
widget_id: number (foreign key)
color: string
on-hand: number
...
...

0
 
swordfishsoupAuthor Commented:
The Stock ID is the unique number. The Prod ID refers to the item generally - fine for most functions - but each product has a unique ID based on size and colour prod id and stock

So large green teddy bear is Stock ID 109 and small red teddy bear is Stock ID 110 -

they both share Prod ID 177.

But that's not the issue or the problem

>>>>Create a loop of the stockID and Quantities in cart
Select from existing stock Table where stock ID = cart stock ID and get existing quantity.
subtract quantiy in cart from quantity in table and give it a variabe(? or add 'i' after to increment?)
Then update able Stock with the new quantity

This is what I need to do somehow. I have set the products stock ID and Quantity in the add product section of the site. Now I just need to update the quantity on a sale going through.
0
 
BadotzCommented:
Can you not code the solution from your example of the "to-do" list?

What, exactly, do you need help with?
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
swordfishsoupAuthor Commented:
Hi Badotz.

This is a case of being able to talk the talk but having an issue with the walk! No I can't code it from that, well probably could but will take me a week of frustration and I don't have a week left on this.

If you're saying that this would be the way to achieve this - then that's a step in the right direction.

Maybe you could just give me a pointer as how to get the loop going that would be helpful, otherwise if you reckon that my method is appropriate for this then I guess you've answered the question!

Have a good day

Dave
0
 
RouchieCommented:
>> So large green teddy bear...

Yeeaahhh!  I wondered when that little dude would make another appearance!!

You've submitted a LOT of code here!  In fact, there's a little too much to work through, so what I can offer is the code to do what you need, but you'll have to tell me how get at certain info if I can't work it out.

Can you just confirm that what you need to do is reduce the stock of a product in the database when somebody purchases that product?  If so, there will be an easy way to do this in the database rather than ASP having to work it out.
0
 
swordfishsoupAuthor Commented:
Well! Me and Teddy are glad to see you :)

Hope you're well.

Yup you're right you may remember setting up the stock table that gets updated as new products are loaded (splitting into sizes colours etc) So Big Green Teddy has a unique stock ID and quantity and Big Red (I'm so sick of Teddy's I can't tell you!) has a unique stock ID etc (both have the same product number but that's irrelevant).

Background:

Bear in mind I have built into the shopping pages a stock count, so if more are 'added to cart' than there are in stock a redirect offers ' we've only got ' please reduce quntity' or add to wish list bla bla -

So, there should never be neagtive numbers to deal with (famous last words).
What could make this easier is that I already have set up a 'store the carts contents in the database' on departure to PayPal -  and have included stock ID in this  - so that information is already gathered. WHich while writing makes your suggestion of doing this in Access seem a great one?

Otherwise I suppose I just need a souped up rowcounter kind of thingy that will subtract the ordered quantity from the stock table (on stockID) from the Orders table on Stock ID.

There is a lot going on on the checkout page so I was half planning (I'm good at half planning) to have this triggered on the IPN feedback from PayPal but that was getting too complicated?

If the order is cancelled during remote checkout I could have the IPN's cancelled order page trigger putting the quantities back but that's another mission. They can do that manually for now. If this thing doesn't go up soon no one is going to get teddies for Christmas, and it'll all be my fault and I'll have to live in the hills.........

I guess the most relevant info you need is this:
http://www.discburner.co.uk/stock.html

Thanks

Dave

(i've got some other questions up...if you're ever bored! :)
0
 
RouchieCommented:
>> So, there should never be neagtive numbers to deal with (famous last words).

How and when you trigger the stock-adjusting code is completely up to you.  I don't know how the 'big boys' (i.e. Amazon) adjust this.  It makes sense to do it when the product is added to the cart, but then again you have to have a 100% reliable way of adding it back to the shelf if somebody doesn't proceed past the payment stage.  Also what if someone adds 1000 teddies to their basket then leaves their PC on all week because they don't believe in Christmas, then empties their basket??  The website would report being out of stock, and poor teddy gets sent to landfill after the January sales!

As I think about this it gets more serious...  If somebody adds 1000 teddies, then closes down their browser, no trigger will ever be fired to put the teddies back on the shelf.  Therefore the most reliable way is to adjust stock after payment.  The only catch is that you would have to do a repeat stock check immediately prior to payment in case 2 people both add 10 teddies simulteanously and there's only 15 in stock.

>> I was half planning to have this triggered on the IPN feedback from PayPal but that was getting too complicated?

Not if you've already stored the quantities of the sale in the database.  All you have to do then is execute a SQL statement that subtracts one amount from the other, using the order ID as the point of reference.

How does the IPN feedback get returned?  Does it fire a certain ASP page?  If so, presuming it fires "thankyou.asp?orderID=xxxxxx" then you could do perform the logic using the orderID.

Are you using SQL Server, because this would be dead easy within a stored procedure?
0
 
swordfishsoupAuthor Commented:
Nahh....I don't do dead easy :) Access mbd

I see what you mean about the 1000 teddies in the landfill scenario (and can't say I didn't get a certain amount of pleasure from the notion).

How I had anticipated this is:

Also in the Orders table is a status. Which is set at 0 - open when the checkout happens - stock (eventually) deducted and off they go to pay. Then if the IPN comes back (yeah - i set a confim - asp and a cancel.asp and it will return whatever info I set in the form - i e order no, stock id quantity customer email etc. Although as the order has been stored I only really need the customer email or order number. Ideally When it has been confirmed as sold (triggered by the ipn) the order table 'status column' updates from 0 to 1 = pending packaging and shipping off to lapland.

The shop owners have an admin page for order management (or they will have by 2moro) which can be searched by date or status. If there is an open order from yesterday (or 10 mins ago) - but no trace of a 'you've been paid for a 1000 teddies email from PayPal - They would change the status to 2 = cancelled or phone or email the customer.........

Does that sound more secure?

If I could survive without earning any money it would be good to develop this so that if the the time between a checkout and confirm > 1 hour or whatever then the store would be mailed to investigate....but I'm not getting paid enough to be so creative with this...

0
 
RouchieCommented:
>> Does that sound more secure?

It does, but it relies on staff vigilence which is not always reliable either!  Either way you choose, the SQL statement in Access will be something to this effect (please excuse incorrect column names because I'm not familiar with the database content)...

Your admin page can contain the values of yyyy and xxxx because this can be seen by the staff monitoring the page (i.e. put the values into a hyperlink so the update page can work from the querystring)

Try testing this in Access directly first to make sure that it does work!
UPDATE 
	tblStock 
SET
	Quantity = Quantity - 	(SELECT 
					DetailQuantity 
				FROM 
					tblOrderDetails 
				WHERE 
					(DetailProductID = yyyyyyyyyyyy) AND
					(DetailsID = xxxxxxxxxxxxxxx)
				)
WHERE
	StockProdID = yyyyyyyyyyyy

Open in new window

0
 
swordfishsoupAuthor Commented:
That's great Thanks.
Will get stuck in now. Will I need it to loop through each stock ID somehow though - or am I being thick?
0
 
RouchieCommented:
>> Will I need it to loop through each stock ID somehow though

Yes.  Something like:

For each orderID
  For each item in orderID
    <a href="reduceStock.asp?detailsID=xxxxx&productID=yyyyyy">Amend Stock Level</a>
  Nextr
Next
0
 
swordfishsoupAuthor Commented:
>>>>or am I being thick?

>>>>>>>>>Yes. :)

Cool thanks
0
 
RouchieCommented:
>>>>or am I being thick?

When I stare at something for too long my head turns to absolute mush and no matter how hard I try I just can't get it to work, or see how it can be done.  Recently I've resorted to taking my dogs out really early in the morning and letting my mind wonder.  Sounds really bizarre but I've solved some really complex problems using simple methods this way.

So my advice, burn the teddies and get a dog....  :-\
0
 
swordfishsoupAuthor Commented:
I'm still on this Rouchie - getting approval on rest of the site before the final admin stuff, so taking a bit of a breather before my head exploded....
0
 
swordfishsoupAuthor Commented:
His name is Fido :)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 8
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now