id4382
Feb 4th 2008, 12:13 pm
Hello Everyone! I am working on a formula to try and automate some things with the Volusion software which is not yet capable in Version 5. What I want is the ability to give someone browsing my site, and after meeting the criteria I determine, have thier Discountlevel automatically changed from '0' or none to level 1.
Here is how I think it can work. The code I think will be placed in the template file, not sure, but, when the response code from the geoip matches the certain criteria I have set, I want the store to automatically give that person the discount level 1 access as they are creating their account and checking out. We need this cause we feel too many people are leaving cause they dont want to wait for the pricing to be given to them manually by me.
Using the following code from GeoIP that will be the foundation of determining who gets what:
<script language="JavaScript" src="http://j.maxmind.com/app/geoip.js"></script>
<script type="text/javascript" language="Javascript">
if (geoip_region() == "CA"){
ChangeDiscountLevel();
}
function ChangeDiscountLevel(){
"Whatever goes here to make this work"
}
//-->
</script>
The one line that interests me is the element name 'discountlevel'. According to Volusion, this is the Customers XSD for the API usage:
<?xml version="1.0" encoding="utf-8"?>
<xs:schema id="xmldata" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
xmlns:msprop="urn:schemas-microsoft-com:xml-msprop">
<xs:element name="xmldata" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="Customers">
<xs:complexType>
<xs:sequence>
<xs:element msprop:TableNameXsd="Customers" name="CustomerID" msprop:SqlDbType="Int" msprop:IsIdentity="true"
minOccurs="0" />
<xs:element name="AccessKey" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="Allow_Access_To_Private_Sections" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="BillingAddress1" msprop:maxLength="75" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="BillingAddress2" msprop:maxLength="75" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="CatalogSubscriber" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="Checkbox_For_New_Customers" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="City" msprop:maxLength="45" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="CompanyName" msprop:maxLength="100" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="Country" msprop:maxLength="30" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="Custom_Field_Custom1" msprop:maxLength="50" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="Custom_Field_Custom2" msprop:maxLength="50" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="Custom_Field_Custom3" msprop:maxLength="50" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="Custom_Field_Custom4" msprop:maxLength="50" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="Custom_Field_Custom5" msprop:maxLength="50" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="Customer_IsAnonymous" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="Customer_Notes" msprop:SqlDbType="Text" minOccurs="0" />
<xs:element name="CustomerType" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="DiscountLevel" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="EmailAddress" msprop:maxLength="75" msprop:SqlDbType="VarChar" minOccurs="1" />
<xs:element name="EmailSubscriber" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="FaxNumber" msprop:maxLength="30" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="FirstDateVisited" msprop:SqlDbType="SmallDateTime" minOccurs="0" />
<xs:element name="FirstName" msprop:maxLength="30" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="FirstOrderDate" msprop:SqlDbType="SmallDateTime" minOccurs="0" />
<xs:element name="ID_Customers_Groups" msprop:SqlDbType="Int" minOccurs="0" />
<xs:element name="IsSuperAdmin" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="LastLogin" msprop:SqlDbType="SmallDateTime" minOccurs="0" />
<xs:element name="LastLoginAttempt" msprop:SqlDbType="SmallDateTime" minOccurs="0" />
<xs:element name="LastModBy" msprop:SqlDbType="Int" minOccurs="0" />
<xs:element name="LastModified" msprop:SqlDbType="SmallDateTime" minOccurs="0" />
<xs:element name="LastName" msprop:maxLength="40" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="LoginAttempts" msprop:SqlDbType="Int" minOccurs="0" />
<xs:element name="news1" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="news10" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="news11" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="news12" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="news13" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="news14" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="news15" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="news16" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="news17" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="news18" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="news19" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="news2" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="news20" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="news3" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="news4" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="news5" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="news6" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="news7" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="news8" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="news9" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="Password" msprop:maxLength="64" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="PaysStateTax" msprop:maxLength="3" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="PercentDiscount" msprop:SqlDbType="Float" minOccurs="0" />
<xs:element name="PhoneNumber" msprop:maxLength="35" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="PostalCode" msprop:maxLength="15" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="SalesRep_CustomerID" msprop:SqlDbType="Int" minOccurs="0" />
<xs:element name="State" msprop:maxLength="30" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="TaxID" msprop:maxLength="64" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="WebsiteAddress" msprop:maxLength="150" msprop:SqlDbType="VarChar" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
Here is also a sample Javascript code from Volusion what I am guessing you would use to call this information or rewrite this code:
var api_url = "http://YourUrl/net/WebService.aspx?
Login=YourEmail@YourDomain.com&EncryptedPassword=YourPassword&
API_Name=Generic\\Orders&SELECT_Columns=o.OrderID,o.OrderStatus,od.TotalPrice&
WHERE_Column=o.OrderStatus&WHERE_Value=New";
var xmlhttp;
var api_response = "";
xmlhttp = new ActiveXObject("MSXML2.ServerXMLHTTP");
xmlhttp.open("POST", api_url, false);
xmlhttp.onreadystatechange = function() {
if (xmlhttp.readyState == 4) {
if (xmlhttp.status == 200) {
api_response = xmlhttp.responseText;
} else {
//unable to connect
}
} else {
//connecting...
}
}
xmlhttp.send();
Here is the ASP/VBScript Sample:
dim oXMLHttp
dim Xml_Returned
set oXMLHttp = Server.CreateObject("Msxml2.serverXmlHttp")
oXMLHttp.open "POST", "http://YourUrl/net/WebService.aspx?
Login=YourEmail@YourDomain.com&
EncryptedPassword=YourPassword&
API_Name=Generic\Orders&SELECT_Columns=o.OrderID,o.OrderStatus,od.TotalPrice&
WHERE_Column=o.OrderStatus&WHERE_Value=New", False
oXMLHttp.setRequestHeader "Content-Type", "text/xml; charset=utf-8"
oXMLHttp.setRequestHeader "Content-Action", "Volusion_API"
oXMLHttp.send
Xml_Returned = oXMLHttp.responseText
Now, unfortunately I do not have a lot of knowledge on how to query the information from the SQL server or write the code to change it automatically. I've read what i can on the API and SQL stuff, but it is a little over my head. BUT, this is one feature we have one wanted from day one when we went 'shopping-cart' shopping and landed with Volusion. If this is not possible, then we will most likely have to drop Volusion and move on since it is creating too many issues between making sales.
Even if no one has the definate answer, even ideas might be able to help as well, since we are willing to beta this on our site, if it works, then everyone who has a similar problem can benefit. Thanks!!
Here is how I think it can work. The code I think will be placed in the template file, not sure, but, when the response code from the geoip matches the certain criteria I have set, I want the store to automatically give that person the discount level 1 access as they are creating their account and checking out. We need this cause we feel too many people are leaving cause they dont want to wait for the pricing to be given to them manually by me.
Using the following code from GeoIP that will be the foundation of determining who gets what:
<script language="JavaScript" src="http://j.maxmind.com/app/geoip.js"></script>
<script type="text/javascript" language="Javascript">
if (geoip_region() == "CA"){
ChangeDiscountLevel();
}
function ChangeDiscountLevel(){
"Whatever goes here to make this work"
}
//-->
</script>
The one line that interests me is the element name 'discountlevel'. According to Volusion, this is the Customers XSD for the API usage:
<?xml version="1.0" encoding="utf-8"?>
<xs:schema id="xmldata" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
xmlns:msprop="urn:schemas-microsoft-com:xml-msprop">
<xs:element name="xmldata" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="Customers">
<xs:complexType>
<xs:sequence>
<xs:element msprop:TableNameXsd="Customers" name="CustomerID" msprop:SqlDbType="Int" msprop:IsIdentity="true"
minOccurs="0" />
<xs:element name="AccessKey" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="Allow_Access_To_Private_Sections" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="BillingAddress1" msprop:maxLength="75" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="BillingAddress2" msprop:maxLength="75" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="CatalogSubscriber" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="Checkbox_For_New_Customers" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="City" msprop:maxLength="45" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="CompanyName" msprop:maxLength="100" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="Country" msprop:maxLength="30" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="Custom_Field_Custom1" msprop:maxLength="50" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="Custom_Field_Custom2" msprop:maxLength="50" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="Custom_Field_Custom3" msprop:maxLength="50" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="Custom_Field_Custom4" msprop:maxLength="50" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="Custom_Field_Custom5" msprop:maxLength="50" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="Customer_IsAnonymous" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="Customer_Notes" msprop:SqlDbType="Text" minOccurs="0" />
<xs:element name="CustomerType" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="DiscountLevel" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="EmailAddress" msprop:maxLength="75" msprop:SqlDbType="VarChar" minOccurs="1" />
<xs:element name="EmailSubscriber" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="FaxNumber" msprop:maxLength="30" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="FirstDateVisited" msprop:SqlDbType="SmallDateTime" minOccurs="0" />
<xs:element name="FirstName" msprop:maxLength="30" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="FirstOrderDate" msprop:SqlDbType="SmallDateTime" minOccurs="0" />
<xs:element name="ID_Customers_Groups" msprop:SqlDbType="Int" minOccurs="0" />
<xs:element name="IsSuperAdmin" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="LastLogin" msprop:SqlDbType="SmallDateTime" minOccurs="0" />
<xs:element name="LastLoginAttempt" msprop:SqlDbType="SmallDateTime" minOccurs="0" />
<xs:element name="LastModBy" msprop:SqlDbType="Int" minOccurs="0" />
<xs:element name="LastModified" msprop:SqlDbType="SmallDateTime" minOccurs="0" />
<xs:element name="LastName" msprop:maxLength="40" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="LoginAttempts" msprop:SqlDbType="Int" minOccurs="0" />
<xs:element name="news1" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="news10" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="news11" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="news12" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="news13" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="news14" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="news15" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="news16" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="news17" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="news18" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="news19" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="news2" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="news20" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="news3" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="news4" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="news5" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="news6" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="news7" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="news8" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="news9" msprop:maxLength="1" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="Password" msprop:maxLength="64" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="PaysStateTax" msprop:maxLength="3" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="PercentDiscount" msprop:SqlDbType="Float" minOccurs="0" />
<xs:element name="PhoneNumber" msprop:maxLength="35" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="PostalCode" msprop:maxLength="15" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="SalesRep_CustomerID" msprop:SqlDbType="Int" minOccurs="0" />
<xs:element name="State" msprop:maxLength="30" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="TaxID" msprop:maxLength="64" msprop:SqlDbType="VarChar" minOccurs="0" />
<xs:element name="WebsiteAddress" msprop:maxLength="150" msprop:SqlDbType="VarChar" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
Here is also a sample Javascript code from Volusion what I am guessing you would use to call this information or rewrite this code:
var api_url = "http://YourUrl/net/WebService.aspx?
Login=YourEmail@YourDomain.com&EncryptedPassword=YourPassword&
API_Name=Generic\\Orders&SELECT_Columns=o.OrderID,o.OrderStatus,od.TotalPrice&
WHERE_Column=o.OrderStatus&WHERE_Value=New";
var xmlhttp;
var api_response = "";
xmlhttp = new ActiveXObject("MSXML2.ServerXMLHTTP");
xmlhttp.open("POST", api_url, false);
xmlhttp.onreadystatechange = function() {
if (xmlhttp.readyState == 4) {
if (xmlhttp.status == 200) {
api_response = xmlhttp.responseText;
} else {
//unable to connect
}
} else {
//connecting...
}
}
xmlhttp.send();
Here is the ASP/VBScript Sample:
dim oXMLHttp
dim Xml_Returned
set oXMLHttp = Server.CreateObject("Msxml2.serverXmlHttp")
oXMLHttp.open "POST", "http://YourUrl/net/WebService.aspx?
Login=YourEmail@YourDomain.com&
EncryptedPassword=YourPassword&
API_Name=Generic\Orders&SELECT_Columns=o.OrderID,o.OrderStatus,od.TotalPrice&
WHERE_Column=o.OrderStatus&WHERE_Value=New", False
oXMLHttp.setRequestHeader "Content-Type", "text/xml; charset=utf-8"
oXMLHttp.setRequestHeader "Content-Action", "Volusion_API"
oXMLHttp.send
Xml_Returned = oXMLHttp.responseText
Now, unfortunately I do not have a lot of knowledge on how to query the information from the SQL server or write the code to change it automatically. I've read what i can on the API and SQL stuff, but it is a little over my head. BUT, this is one feature we have one wanted from day one when we went 'shopping-cart' shopping and landed with Volusion. If this is not possible, then we will most likely have to drop Volusion and move on since it is creating too many issues between making sales.
Even if no one has the definate answer, even ideas might be able to help as well, since we are willing to beta this on our site, if it works, then everyone who has a similar problem can benefit. Thanks!!