Solved

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

Posted on 2007-11-26
15
706 Views
Last Modified: 2010-04-24
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
Comment
Question by:swordfishsoup
  • 8
  • 5
  • 2
15 Comments
 
LVL 29

Expert Comment

by:Badotz
ID: 20349466
>>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
 

Author Comment

by:swordfishsoup
ID: 20350341
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
 
LVL 29

Expert Comment

by:Badotz
ID: 20351286
Can you not code the solution from your example of the "to-do" list?

What, exactly, do you need help with?
0
 

Author Comment

by:swordfishsoup
ID: 20355927
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
 
LVL 25

Expert Comment

by:Rouchie
ID: 20356730
>> 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
 

Author Comment

by:swordfishsoup
ID: 20356821
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
 
LVL 25

Expert Comment

by:Rouchie
ID: 20357034
>> 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:swordfishsoup
ID: 20357094
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
 
LVL 25

Expert Comment

by:Rouchie
ID: 20357157
>> 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
 

Author Comment

by:swordfishsoup
ID: 20357289
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
 
LVL 25

Expert Comment

by:Rouchie
ID: 20357635
>> 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
 

Author Comment

by:swordfishsoup
ID: 20357730
>>>>or am I being thick?

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

Cool thanks
0
 
LVL 25

Accepted Solution

by:
Rouchie earned 500 total points
ID: 20357796
>>>>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
 

Author Comment

by:swordfishsoup
ID: 20433493
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
 

Author Closing Comment

by:swordfishsoup
ID: 31410949
His name is Fido :)
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now