Process Orders

<% ' Get Form Variables showOrders = TRIM( Request( "showOrders" ) ) searchName = TRIM( Request( "searchName" ) ) searchEmail = TRIM( Request( "searchEmail" ) ) searchShipID = TRIM( Request( "searchShipID" ) ) showPage = TRIM( Request( "showPage" ) ) allPages = TRIM( Request( "allpages" ) ) FormCount = Request.Form.Count for i=1 to Request.Form.Count if Request.Form.Key(i) = "FirstCheck" then FirstCheck = i + 1 CheckStart = FirstCheck - 1 end if next ' Assign Default Values IF showOrders = "" THEN showOrders = 1 END IF IF showPage = "" THEN showPage = 1 END IF If FirstCheck = "" then FirstCheck = 6 CheckStart = FirstCheck-1 end if ' Open Database Connection Server.MapPath(".") dbPath = "E:/hshome/webcaddy/database/storedbexchange.mdb" Set Con = Server.CreateObject("ADODB.Connection") Con.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & dbPath ' Open Database Connection Server.MapPath(".") dbPath = "E:/hshome/webcaddy/database/storedbexchange.mdb" Set Con1 = Server.CreateObject("ADODB.Connection") Con1.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & dbPath %> Process Orders <%IF session( "iDebugLevel" ) > 0 THEN %> Session Debug Level Set to:<%=session( "iDebugLevel" )%>
<%for i=1 to Request.Form.Count%> Form Key #<%=i%> = <%=Request.Form.Key(i)%>
<%next%> orderstatus : <%=orderStatus%>
showPage : <%=showPage%>
searchName : <%=searchName%>
searchEmail : <%=searchEmail%>
searchShipID: <%=searchShipID%>
allPages : <%=allPages%>
showOrders : <%=showOrders%>
FormCount : <%=FormCount%>
CheckStart : <%=CheckStart%>
FirstCheck : <%=FirstCheck%>
<%END IF%> <% ' From the form the following data is Post to this page ' and can be retrieved via the Request.Form command ' Key 1 is the sho button. If count is < CheckStart then nothing to update ' Key 2 If count = FirstCheck then this is an action button and no records are selected. ' Key 2 If Count is > FirstCheck will be the first selected IDShip number ' The last Key (FormCommand) will be the action number 0-6 is a status change. 90-99 is a command ' Command 90 is to make a payment ' Command 91 is a join command ' Command 92 is a BulkEmail command ' Command 93 is a New Order command ' Command 94 is a New Payment command ' Command 95 is a New Order & Payment command ' Now process the form actions %> <% IF FormCount > 0 THEN FormCommand = Request.Form.Key(FormCount) END IF IF FormCount > CheckStart THEN IF FormCount = FirstCheck THEN %>
No Records Selected - Please select a records before selecting an action button
<%ELSE IF FormCommand < 90 THEN FOR i = FirstCheck to (FormCount - 1) sqlString = "UPDATE ship SET ship_status = " & FormCommand & " " SELECT CASE FormCommand CASE 4 sqlString = sqlString 'Decrement product_stock sqldecString = "SELECT order_productID, order_quantity " &_ "FROM orders " &_ "WHERE order_shipID = " & Request.Form.Key(i)-100 IF session( "iDebugLevel" ) > 0 THEN %> sqldecString: <%=sqldecstring%>
<%END IF SET RS = Server.CreateObject( "ADODB.Recordset" ) RS.CursorType = adOpenStatic RS.ActiveConnection = Con RS.Open sqldecString WHILE NOT RS.EOF sqldecString = "UPDATE products SET " &_ "product_stock = product_stock - " & RS("order_quantity") & " "&_ "WHERE product_id = " & RS("order_productID") IF session( "iDebugLevel" ) > 0 THEN %> sqldecString: <%=sqldecstring%>
<%END IF Con1.Execute sqldecString RS.MoveNext WEND CASE 5 sqlString = sqlString CASE 6 sqlString = sqlString CASE ELSE sqlString = sqlString & " , ship_shipped=NULL " END SELECT sqlString = sqlString & "WHERE ship_shipid = " & Request.Form.Key(i)-100 IF session( "iDebugLevel" ) > 0 THEN %> sqlString: <%=sqlstring%>
<%END IF Con.Execute sqlString NEXT ELSE 'Make Payment for selected users IF FormCommand = 90 THEN FOR i = FirstCheck to (FormCount - 1) sqlString = "SELECT SUM(order_price * order_quantity) AS sumprice " &_ "FROM orders WHERE order_shipID = " & Request.Form.Key(i)-100 IF session( "iDebugLevel" ) > 0 THEN %> sqlString: <%=sqlstring%>
<%END IF SET RS = Server.CreateObject( "ADODB.Recordset" ) RS.CursorType = adOpenStatic RS.ActiveConnection = Con RS.Open sqlString sumorderprice = RS( "sumprice" ) sqlString = "SELECT * " &_ "FROM ship WHERE ship_shipID = " & Request.Form.Key(i)-100 IF session( "iDebugLevel" ) > 0 THEN %> sqlString: <%=sqlstring%>
sumorder : <%=sumorderprice%>
<%END IF SET RS = Server.CreateObject( "ADODB.Recordset" ) RS.CursorType = adOpenStatic RS.ActiveConnection = Con RS.Open sqlString IF session( "iDebugLevel" ) > 0 THEN %> Ship Charge: <%=RS( "ship_charge" )%>
Ship GST : <%=RS( "ship_GST" )%>
<%END IF sumorderprice = sumorderprice + RS( "ship_charge" ) sqlString = "UPDATE ship SET " &_ "ship_payment = " & sumorderprice & ", " &_ "ship_paydate = NOW() " &_ "WHERE ship_shipid = " & Request.Form.Key(i)-100 IF session( "iDebugLevel" ) > 0 THEN %> sqlString: <%=sqlstring%>
<%END IF Con.Execute sqlString NEXT END IF 'Join two orders witht he same User IF FormCommand = 91 THEN IF FormCount = (CheckStart + 3) THEN 'Check that the UserID's are the same for both selected orders sqlString = "SELECT ship_userID AS User_ID " &_ "FROM ship WHERE ship_shipID = " & Request.Form.Key(FirstCheck)-100 &_ "OR ship_shipID = " & Request.Form.Key(FirstCheck + 1)-100 IF session( "iDebugLevel" ) > 0 THEN %> sqlString: <%=sqlstring%>
<%END IF SET RS = Server.CreateObject( "ADODB.Recordset" ) RS.CursorType = adOpenStatic RS.ActiveConnection = Con RS.Open sqlString userid1 = RS( "User_ID" ) IF NOT RS.EOF THEN RS.MoveNext userid2 = RS( "User_ID" ) ELSE userid2 = -1 END IF IF userid1 = userid2 THEN sqlString = "UPDATE orders SET " &_ "order_shipid = " & Request.Form.Key(FirstCheck)-100 &_ " WHERE order_shipid = " & Request.Form.Key(FirstCheck + 1)-100 IF session( "iDebugLevel" ) > 0 THEN %> sqlString: <%=sqlstring%>
<%END IF Con.Execute sqlString sqlString = "DELETE FROM ship WHERE " &_ "ship_shipid = " & Request.Form.Key(FirstCheck + 1)-100 IF session( "iDebugLevel" ) > 0 THEN %> sqlString: <%=sqlstring%>
<%END IF Con.Execute sqlString ELSE%> Error - Cannot join orders from different users <%END IF IF session( "iDebugLevel" ) > 0 THEN %> FormCommand : <%=FormCommand%>
FormCount : <%=FormCount%>
<%FOR i = FirstCheck to (FormCount - 1) %> FormKey : <%=Request.Form.Key(i)-100%>
<%NEXT%> <%END IF%> <%ELSE%> Error - Only Select two Orders to be join. <%END IF%> <%END IF 'Bulk Email to selected User IF FormCommand = 92 THEN Server.Transfer "composeMsg.asp" END IF IF FormCommand = 93 THEN Server.Transfer "NewOrderMsg.asp" END IF IF FormCommand = 94 THEN Server.Transfer "NewPaymentMsg.asp" END IF IF FormCommand = 95 THEN Server.Transfer "NewOrderPaymentMsg.asp" END IF END IF END IF END IF%> <% ' Retrieve Orders sqlString = "SELECT * " &_ " FROM (orders INNER JOIN (users INNER JOIN ship ON users.user_id = ship.ship_userID) " &_ " ON orders.order_shipID = ship.ship_shipID) " &_ " INNER JOIN Products ON orders.order_productID = Products.product_id " &_ " WHERE " IF showOrders < 99 THEN if showOrders = 9 THEN sqlString = sqlString & " user_email LIKE '%" & searchEmail & "%'" sqlString = sqlString & " AND user_username LIKE '%" & searchName & "%'" sqlString = sqlString & " AND ship_shipID LIKE '%" & searchShipID & "%'" ELSE sqlString = sqlString & " ship_status=" & showOrders END IF ELSE sqlString = sqlString & " ship_status < 6 " END IF IF (showOrders < 5) OR (showOrders = 9) THEN sqlString = sqlString & " ORDER BY ship_created DESC , orders.order_productID DESC" ELSE sqlString = sqlString & " ORDER BY ship_shipped DESC , orders.order_productID DESC" END IF IF session( "iDebugLevel" ) > 0 THEN %> Session Debug Level Set to:<%=session( "iDebugLevel" )%>
sqlString: <%=sqlstring%>
<%END IF%> <% SET RS = Server.CreateObject( "ADODB.Recordset" ) RS.CursorType = adOpenStatic IF allPages = "" THEN RS.PageSize = 300 ELSE RS.PageSize = 99999 END IF RS.ActiveConnection = Con RS.Open sqlString IF NOT RS.EOF THEN RS.AbsolutePage = cINT( showPage ) END IF FUNCTION SELECTED( val1, val2 ) IF cSTR( val1 ) = cSTR( val2 ) THEN SELECTED = " selected " END IF END FUNCTION %>

<% IF session( "iDebugLevel" ) > 1 THEN %> Session Debug Level Set to:<%=session( "iDebugLevel" )%>
showpage: <%=showPage%>
shipID: <%=shipID%>
OrderStatus: <%=orderStatus%>
amount: <%=amount%>
paymentType: <%=paymentType%>
<%END IF%>

Order List Processing	GOTO:MANAGE Showing Orders for:
View the Orders in the following STATE: CANCELLED - NEW - PENDING - PRODUCTION - SHIPPING - SHIPPED - DELIVERED

Sel Name Email Items Total Payment Date Edit

<%'Set the first check point number which counts the number of fields before the first check box%> <% rowCount = 0 WHILE NOT RS.EOF AND rowCount < RS.PageSize rowCount = rowCount + 1 currentUser=RS( "user_username" ) userID =RS( "user_id" ) currentShip=RS( "ship_shipid" ) statusShip = RS( "ship_status") shipID = RS( "ship_shipid" ) payment = RS( "ship_payment" ) paymentMethod = RS( "ship_paymethod" ) useremail = RS( "user_email" ) username = RS( "user_username" ) createddate = RS( "ship_created" ) paymentdate = RS( "ship_paydate" ) shipdate = RS( "ship_shipped" ) shipcurrency = RS( "ship_currency" ) shipGST= RS( "ship_GST" ) discount = RS( "user_discount" ) orderTotal = 0 nextuser = "FALSE" %> <% discountAmount = orderTotal * discount / 100 if shipGST > 0 then orderTotal = ordertotal - discountAmount + shipping else orderTotal = ordertotal - discountAmount + shipping shipGST = 0 end if balance = orderTotal - payment %>

<%=username%> <%=useremail%> <%WHILE NOT RS.EOF AND nextuser = "FALSE" IF currentShip = RS( "ship_shipid" ) THEN %> <%orderTotal = orderTotal + ( RS( "order_quantity" ) * RS( "order_price" ) ) shipping = ( RS( "ship_charge" )) if RS("product_stock") < RS( "order_quantity" ) then%> <%else%> <%if RS("product_stock") < RS( "order_quantity" )+3 then%> <%else%> <%end if%> <%end if%> <%=RS( "order_quantity" )%>*<%=RS( "product_name" )%>
<%RS.MoveNext ELSE nextuser = "TRUE" END IF%> <%WEND%> <%=shipcurrency & FormatCurrency( orderTotal)%> <%=shipcurrency & FormatCurrency( payment)%> <% IF statusShip = 0 THEN %><%=createddate %><% END IF %> <% IF statusShip = 1 THEN %><%=createddate %><% END IF %> <% IF statusShip = 2 THEN %><%=createddate %><% END IF %> <% IF statusShip = 3 THEN %><%=paymentdate %><% END IF %> <% IF statusShip = 4 THEN %><%=paymentdate %><% END IF %> <% IF statusShip = 5 THEN %><%=shipdate %><% END IF %> <% IF statusShip = 6 THEN %><%=shipdate %><% END IF %> <%=currentShip%>

<% WEND %>

Send Selected Orders to the following STATE
name="0"> name="1"> name="2"> name="3"> name="4"> name="5"> name="6">

Total Orders in this State: <%=rowCount%>

Perform the following action on the orders above
<%IF statusShip > 0 THEN%> Summary List for this State
Country Ship List for this State <%END IF%>

<%IF statusShip > 3 THEN%>

Send Email <%END IF%>