/// <summary>
/// Imports one or more categories.
/// </summary>
/// <param name="preview">if set to <c>true</c> [preview].</param>
/// <param name="dupeMode">if set to <c>true</c> [dupe mode].</param>
/// <param name="categories">The categories.</param>
/// <returns></returns>
public static List<object> ImportCategories( bool preview, bool dupeMode, List<object> categories )
{
Dictionary<string, object> j = new Dictionary<string, object>();
List<object> outputCategoryList = new List<object>();
using(SqlConnection cn = Site.CreateConnection(true, true)) {
cn.Open();
using( SqlTransaction trns = cn.BeginTransaction( "Import categories" ) ) {
foreach( Dictionary<string, object> k in categories ) {
outputCategoryList.Add( ImportCategory( ( Dictionary<string, object> )k, dupeMode, cn, trns ) );
}
if( preview ) {
trns.Rollback();
} else {
trns.Commit();
}
}
}
return outputCategoryList;
}
/// <summary>
/// Gets the best shipment.
/// </summary>
/// <param name="shipments">The shipments.</param>
/// <returns></returns>
public static Commerce.Shipment GetBestShipment(List<Commerce.Shipment> shipments)
{
/* check for voided numbers and add them to a list */
List<string> voidedTrackingNumbers = new List<string>();
List<Commerce.Shipment> unvoidedShipments = new List<Commerce.Shipment>();
foreach(Commerce.Shipment shipment in shipments) {
string voidStatus = shipment.VoidStatus.Trim().ToLower();
if(voidStatus == "y" || voidStatus == "true" || voidStatus == "voided" || voidStatus == "void" || voidStatus == "yes") {
voidedTrackingNumbers.Add(shipment.Tracking);
}
}
/* go through again and find all the shipments (hopefully just one) that are not voided */
foreach(Commerce.Shipment shipment in shipments) {
bool addShip = true;
foreach(string trackingNo in voidedTrackingNumbers) {
if(trackingNo == shipment.Tracking) {
addShip = false;
}
}
if(addShip) {
unvoidedShipments.Add(shipment);
}
}
/* there's more than one? Than try and find the lead tracking number */
if(unvoidedShipments.Count > 1) {
unvoidedShipments.Sort(delegate(Commerce.Shipment s1, Commerce.Shipment s2) {
int t1 = 0;
int t2 = 0;
try {
if(s1.Tracking.Length > 4) {
int.TryParse(s1.Tracking.Substring(1, 5), out t1);
}
if(s2.Tracking.Length > 4) {
int.TryParse(s2.Tracking.Substring(1, 5), out t2);
}
} catch(Exception e) {
(e.Message).Debug(8);
}
return t1.CompareTo(t2);
});
}
return unvoidedShipments[0];
}
/// <summary>
/// Initializes a new instance of the <see cref="SiteImagePlaceholders"/> class.
/// </summary>
/// <param name="site">The site.</param>
public SiteImagePlaceholders( Site site )
{
using(SqlConnection cn = Site.CreateConnection(true, true)) {
cn.Open();
using(SqlCommand cmd = new SqlCommand(@"select unique_siteId, m_imagingTemplate,c_imagingTemplate,f_imagingTemplate,t_imagingTemplate,
a_imagingTemplate,x_imagingTemplate,y_imagingTemplate,z_imagingTemplate,b_imagingTemplate,d_imagingTemplate, siteaddress
from site_configuration", cn)) {
using(SqlDataReader r = cmd.ExecuteReader()) {
List = new List<SiteImagePlaceholder>();
while(r.Read()) {
List.Add(new SiteImagePlaceholder(
r.GetGuid(0),
r.GetGuid(1),
r.GetGuid(2),
r.GetGuid(3),
r.GetGuid(4),
r.GetGuid(5),
r.GetGuid(6),
r.GetGuid(7),
r.GetGuid(8),
r.GetGuid(9),
r.GetGuid(10),
r.GetString(11)
));
}
}
}
}
}
/// <summary>
/// Gets the item images for use in a backend application.
/// </summary>
/// <param name="itemNumber">The item number.</param>
/// <returns></returns>
public static List<object> GetItemImages( string itemNumber )
{
List<object> l = new List<object>();
using( SqlConnection cn = Site.CreateConnection(true, true) ){
cn.Open();
using(SqlCommand cmd = new SqlCommand(@"select imagingId, fileSize, fileName, thumbnail,
thumbOrder, height, width from imaging with (nolock) where itemnumber = @itemnumber;", cn)) {
cmd.Parameters.Add("@itemnumber", SqlDbType.VarChar).Value = itemNumber;
using(SqlDataReader r = cmd.ExecuteReader()) {
while(r.Read()) {
Dictionary<string, object> j = new Dictionary<string, object>();
Guid imgId = r.GetGuid(0);
string fileName = r.GetString(2);
j.Add("imagingId", imgId.ToString());
j.Add("fileSize", r.GetInt32(1).ToString());
j.Add("fileName", fileName);
j.Add("thumbnail", r.GetBoolean(3));
j.Add("thumbOrder", r.GetInt32(4));
j.Add("height", r.GetInt32(5));
j.Add("width", r.GetInt32(6));
j.Add("src", imgId.ToFileName() + Path.GetExtension(fileName));
l.Add(j);
}
}
}
}
return l;
}
/// <summary>
/// Recalculates the order.
/// </summary>
/// <param name="args">The order arguments.</param>
/// <param name="fcn">The FCN.</param>
/// <param name="ftrans">The ftrans.</param>
/// <returns>{error:0,desc:""}</returns>
public static Dictionary<string, object> RecalculateOrder(Dictionary<string, object> args, SqlConnection fcn, SqlTransaction ftrans)
{
Dictionary<string, object> vt;
Dictionary<string, object> j;
Commerce.CreditCard card = null;
Commerce.Cash cash = null;
Commerce.Wire wire = null;
// never used -> Commerce.PayPal PayPal=null;
Commerce.Check check = null;
Commerce.PromiseToPay promiseToPay = null;
decimal discountAmount = 0;
Guid paymentMethodId = Guid.NewGuid();
bool backorder = false;
bool preview = false;
int errorId = -1;
("FUNCTION /w SP,CN,TRANS recalculateOrder").Debug(10);
string[] keys = { "userId", "orderSessionId", "cartSessionId", "preview", "purchaseOrder" };
bool transactionSucsessStatus = false;
int termId = 0;
int orderId = 0;
decimal difference = 0;
foreach(string keyName in keys) {
if(!args.ContainsKey(keyName)) {
Dictionary<string, object> o = new Dictionary<string, object>();
string _msg = "The key \"" + keyName + "\" is missing from the argument dictionary. All keys must be present even if they are blank.";
o.Add("error", -4010);
o.Add("description", _msg);
String.Format("recalculateOrder failed. {0}", _msg).Debug(1);
return o;
}
}
/* get the old order */
SqlConnection cn;
SqlTransaction trans;
Guid orderSessionId = new Guid((string)args["orderSessionId"]);
if(fcn == null) {
cn = Site.CreateConnection(true, true);
cn.Open();
trans = cn.BeginTransaction("Recalculate transaction");
} else {
cn = fcn;
trans = ftrans;
}
Commerce.Order originalOrder = Commerce.Order.GetOrderBySessionId(orderSessionId, cn, trans);
termId = originalOrder.TermId;
preview = Convert.ToBoolean(args["preview"].ToString());
if(!preview) {
/* preview the recalculation in preview to asertain the grand total for charging the card */
trans.Save("preChargePreview");
j = ExecPlaceOrder(
orderSessionId,
Convert.ToInt32(args["userId"].ToString()),
orderSessionId,
true,/*preview*/
new Guid(Main.Site.Defaults.SiteId),
new Guid((string)args["cartSessionId"]),
args["purchaseOrder"].ToString(),
DateTime.Now/* this value is ignored in the SP for recalculations */,
termId,
discountAmount,
cn,
trans
);
termId = (int)j["termId"];
orderId = (int)j["orderId"];
if(j["error"].ToString() != "0" && j["error"].ToString() != "5") {
Exception ex = new Exception(j["description"].ToString());
throw ex;
}
if(!decimal.TryParse(j["difference"].ToString(), out difference)) {
difference = 0;
}
difference = Math.Round(difference, 2, MidpointRounding.AwayFromZero);
if(difference != 0) {
if(backorder) {
/* no need to do anything */
} else if(termId == 0 && difference > 0) {/*this is a prepaid credit card transaction - termId 0 */
/* don't try and charge credit cards negitive amounts */
card = new Commerce.CreditCard(
args["cardType"].ToString().MaxLength(50, true),
args["cardNumber"].ToString().MaxLength(100, true),
args["nameOnCard"].ToString().MaxLength(100, true),
args["secNumber"].ToString().MaxLength(7, true),
args["expMonth"].ToString().MaxLength(4, true),
args["expYear"].ToString().MaxLength(4, true)
);
List<int> orderIds = new List<int>();
orderIds.Add(orderId);
card.Insert(paymentMethodId, orderSessionId, originalOrder.UserId, originalOrder.SessionId,
termId, "", difference, DateTime.Now, orderIds, "", cn, trans);
} else if(termId == 9 /* this is a COD Check transaction - termId 9 */ ) {
check = new Commerce.Check(
args["checkNumber"].ToString().MaxLength(50, true),
args["routingNumber"].ToString().MaxLength(50, true),
args["bankAccountNumber"].ToString().MaxLength(50, true),
args["checkNotes"].ToString().MaxLength(50, true)
);
List<int> orderIds = new List<int>();
orderIds.Add(orderId);
check.Insert(paymentMethodId, originalOrder.UserId, originalOrder.SessionId, termId, "", difference, DateTime.Now, orderIds, "", cn, trans);
} else if(termId == 20 /* this is a wire transfer - termId 20 */ ) {
wire = new Commerce.Wire(
args["swift"].ToString().MaxLength(50, true),
args["bankName"].ToString().MaxLength(50, true),
args["routingTransitNumber"].ToString().MaxLength(50, true)
);
List<int> orderIds = new List<int>();
orderIds.Add(orderId);
wire.Insert(paymentMethodId, originalOrder.UserId, originalOrder.SessionId, termId, "", difference, DateTime.Now, orderIds, "", cn, trans);
} else if(termId == 13 /* this order is prepaid in cash */) {
cash = new Commerce.Cash(); /*don't you wish it was really that easy?*/
List<int> orderIds = new List<int>();
orderIds.Add(orderId);
cash.Insert(paymentMethodId, originalOrder.UserId, originalOrder.SessionId, termId, "", difference, DateTime.Now, orderIds, "", cn, trans);
} else if(difference > 0) {
/* this order is an accrued order */
promiseToPay = new Commerce.PromiseToPay();
List<int> orderIds = new List<int>();
orderIds.Add(orderId);
promiseToPay.Insert(paymentMethodId, originalOrder.UserId, originalOrder.SessionId, termId, "", difference, DateTime.Now, orderIds, "", cn, trans);
}
}
trans.Rollback("preChargePreview");
}
/* do the recalculation */
j = ExecPlaceOrder(
new Guid((string)args["orderSessionId"]),
Convert.ToInt32(args["userId"].ToString()),
new Guid((string)args["orderSessionId"]),
preview,
new Guid(Main.Site.Defaults.SiteId),
new Guid((string)args["cartSessionId"]),
args["purchaseOrder"].ToString(),
DateTime.Now/* this value is ignored in the SP for recalculations */,
termId,
discountAmount,
cn,
trans
);
errorId = Convert.ToInt32(j["error"].ToString());
if(errorId != 0 && errorId != 5) {/* if there was an return the error without continuing */
if(fcn == null) {
trans.Rollback();
cn.Close();
}
return j;
};
if(termId != 0 || backorder == true) {
/* this order uses acrued payment method or has lost value, don't charge now */
("order with payment terms, backorder. No payment gateway now.").Debug(7);
transactionSucsessStatus = true;
} else if(difference > 0) {
("starting payment gateway...").Debug(5);
vt = Commerce.VirtualTerminal.ChargeCreditCard(
(Commerce.Address)j["billToAddress"], (Commerce.Address)j["shipToAddress"], card,
difference, new Guid((string)args["orderSessionId"]), originalOrder.OrderNumber,
originalOrder.PurchaseOrder, fcn, ftrans
);
if(vt == null) {
Dictionary<string, object> o = new Dictionary<string, object>();
trans.Rollback();
o.Add("error", -1754);
o.Add("description", "Internal virtual terminal error. Unable to create virtual terminal object.");
("Invalid credit card passed to local system").Debug(7);
("placeOrder Failed with error code -1754").Debug(7);
if(fcn == null) {
cn.Dispose();
}
return o;
}
transactionSucsessStatus = vt["error"].ToString() == "0";
if(!transactionSucsessStatus) {
j.Add("error", -3);
j.Add("description", vt["description"]);
j.Add("virtualTerminal", vt);
if(fcn == null) {
cn.Dispose();
}
return j;
}
}
if(errorId != 0 || transactionSucsessStatus == false || preview) {
if(fcn == null) {
trans.Rollback();
}
if(!preview) {
Exception ex = new Exception("The trasnaction failed.");
throw ex;
}
} else {
Commerce.Order order;
RecalculateOrderEventArgs e;
if(fcn == null) {
order = Commerce.Order.GetOrderByOrderId(orderId, cn, trans);
e = new RecalculateOrderEventArgs(order, cn, trans, args, Main.GetCurrentSession(), HttpContext.Current);
} else {
order = Commerce.Order.GetOrderByOrderId(orderId, fcn, ftrans);
e = new RecalculateOrderEventArgs(order, fcn, ftrans, args, Main.GetCurrentSession(), HttpContext.Current);
}
Main.Site.raiseOnrecalculateorder(e);
if(Site.AbortDefaultEvent == true) {
Site.AbortDefaultEvent = false;
}
if(fcn == null) {
trans.Commit();
}
}
if(fcn == null) {
cn.Dispose();
}
return j;
}
/// <summary>
/// Gets most of the order's by info using various methods. Formated for JSON.
/// </summary>
/// <param name="orderNumber">The order number. Pass null if not used.</param>
/// <param name="orderId">The order id. Pass a value less than 0 if not use.)</param>
/// <param name="order">The order. If you already have an order object loaded you can pass it preventing addtional database queries.</param>
/// <param name="cn">The sql connection.</param>
/// <param name="trans">The transaction.</param>
/// <returns></returns>
public static Dictionary<string, object> GetOrderJson(string orderNumber, int orderId, Commerce.Order order,
SqlConnection cn, SqlTransaction trans)
{
Dictionary<string, object> j = new Dictionary<string, object>();
cn = (SqlConnection)Utilities.Iif(cn == null, Site.SqlConnection, cn);
if(order == null) {
if(orderNumber != null) {
order = Commerce.Order.GetOrderByOrderNumber(orderNumber, cn, trans);
} else if(orderId > -1) {
order = Commerce.Order.GetOrderByOrderId(orderId, cn, trans);
}
}
if(order == null) {
j.Add("error", -1);
j.Add("description", String.Format("Order {0} not found.", orderNumber));
return j;
}
j.Add("error", 0);
j.Add("description", "");
j.Add("approvedBy", order.ApprovedBy);
j.Add("billToAddress", order.BillToAddress);
j.Add("canceled", order.Canceled);
j.Add("closed", order.Closed);
j.Add("comment", order.Comment);
j.Add("deliverBy", order.DeliverBy);
j.Add("discount", order.Discount);
j.Add("FOB", order.FOB);
j.Add("grandTotal", order.GrandTotal);
j.Add("manifest", order.Manifest);
j.Add("orderDate", order.OrderDate);
j.Add("orderId", order.OrderId);
j.Add("orderNumber", order.OrderNumber);
j.Add("paid", order.Paid);
j.Add("parentOrderId", order.ParentOrderId);
j.Add("paymentMethodId", order.PaymentMethodId);
j.Add("purchaseOrder", order.PurchaseOrder);
j.Add("readyForExport", order.ReadyForExport);
j.Add("recalculatedOn", order.RecalculatedOn);
j.Add("requisitionedBy", order.RequisitionedBy);
j.Add("scanned_order_image", order.ScannedOrderImage);
j.Add("service1", order.Service1);
j.Add("service2", order.Service2);
j.Add("sessionId", order.SessionId);
j.Add("shippingTotal", order.ShippingTotal);
j.Add("shipToAddress", order.ShipToAddress);
j.Add("soldBy", order.SoldBy);
j.Add("subTotal", order.SubTotal);
j.Add("taxTotal", order.TaxTotal);
j.Add("term", order.Term);
j.Add("user", order.User);
j.Add("userId", order.UserId);
j.Add("vendor_accountNo", order.VendorAccountNumber);
j.Add("lastStatusId", order.LastStatusId);
j.Add("lastStatus", order.LastStatus);
List<Dictionary<string, object>> lines = new List<Dictionary<string, object>>();
foreach(Commerce.Line line in order.Lines) {
Dictionary<string, object> l = new Dictionary<string, object>();
l.Add("addressId", line.AddressId);
l.Add("addTime", line.AddTime);
l.Add("backorderedQty", line.BackorderedQty);
l.Add("canceledQty", line.CanceledQty);
l.Add("cartId", line.CartId);
l.Add("customLineNumber", line.CustomLineNumber);
l.Add("epsmmcsAIFilename", line.EPSMMCSAIFileName);
l.Add("epsmmcsOutput", line.EPSMMCSOutput);
l.Add("estimatedFulfillmentDate", line.EstimatedFulfillmentDate);
l.Add("fulfillmentDate", line.FulfillmentDate);
l.Add("itemNumber", line.ItemNumber);
l.Add("kitAllocationCartId", line.KitAllocationCartId);
l.Add("kitAllocationId", line.KitAllocationId);
l.Add("kitQty", line.KitQty);
l.Add("lineDetail", line.LineDetail);
l.Add("lineNumber", line.LineNumber);
l.Add("noTaxValueCostTotal", line.NoTaxValueCostTotal);
l.Add("orderId", line.OrderId);
l.Add("orderNumber", line.OrderNumber);
l.Add("parentCartId", line.ParentCartId);
l.Add("price", line.Price);
l.Add("qty", line.Qty);
l.Add("serialId", line.SerialId);
l.Add("serialNumber", line.SerialNumber);
l.Add("shipmentId", line.ShipmentId);
l.Add("shipmentNumber", line.ShipmentNumber);
l.Add("showAsSeperateLineOnInvoice", line.ShowAsSeperateLineOnInvoice);
l.Add("valueCostTotal", line.ValueCostTotal);
l.Add("vendorItemKitAssignmentId", line.VendorItemKitAssignmentId);
l.Add("lastStatus", line.LastStatus);
l.Add("lastStatusId", line.LastStatusId);
Dictionary<string, object> form = new Dictionary<string, object>();
form.Add("HTML", line.Form.HtmlWithValues());
form.Add("name", line.Form.Name);
form.Add("inputs", line.Form.Inputs);
form.Add("ext", line.Form.Extention);
l.Add("form", form);
l.Add("item", Admin.GetItem(line.ItemNumber));
lines.Add(l);
}
j.Add("lines", lines);
return j;
}
/// <summary>
/// List the specified path.
/// </summary>
/// <param name="path">The path.</param>
/// <returns></returns>
public static Dictionary<string, object> Ls( string path )
{
( "FUNCTION /w (!PRIVATE ACCESS ONLY!),fileSystem ls" ).Debug( 10 );
Dictionary<string, object> j = new Dictionary<string, object>();
ArrayList dirs = new ArrayList();
ArrayList files = new ArrayList();
List<object> f = new List<object>();
Dictionary<string, object> up = new Dictionary<string, object>();
try {
using(Impersonation imp = new Impersonation()) {
if(path.Length == 0) {
string targetDirectory = Main.PhysicalApplicationPath;
if(Main.GetCurrentSession().UserId == -1) {
targetDirectory += "user\\public";
if(!Directory.Exists(targetDirectory)) {
Directory.CreateDirectory(targetDirectory);
}
} else {
targetDirectory += "user\\" + Convert.ToString(Main.GetCurrentSession().UserId);
if(!Directory.Exists(targetDirectory)) {
Directory.CreateDirectory(targetDirectory);
}
}
/*
* this seems like it woulbe be cool, but really it isn't
* so lets just put this here
* */
path = Main.PhysicalApplicationPath;
}
if(path.StartsWith("/")) {
path = path.TrimStart('/');
}
/* trim ending slash */
if(path.EndsWith("\\")) {
path = path.Substring(0, path.Length - 1);
}
if(!path.Contains(":")) {
path = Main.PhysicalApplicationPath + path.Replace("/", "\\");
}
if(Main.FileSystemAccess == FileSystemAccess.Site && (!path.Contains(Main.PhysicalApplicationPath.Substring(0, Main.PhysicalApplicationPath.Length - 1)))) {
Exception e = new Exception("Access outside of physical site path not allowed");
throw e;
}
files.AddRange(Directory.GetFiles(path));
dirs.AddRange(Directory.GetDirectories(path));
if(path.Length > 3) {
up.Add("name", path + "\\..");
up.Add("size", 0);
up.Add("creationTime", "");
up.Add("lastAccessTime", "");
up.Add("lastWriteTime", "");
up.Add("objectType", "directory");
f.Add(up);
}
foreach(string dir in dirs) {
Dictionary<string, object> fd = new Dictionary<string, object>();
fd.Add("name", dir);
fd.Add("size", 0);
fd.Add("creationTime", File.GetCreationTime(dir));
fd.Add("lastAccessTime", File.GetLastAccessTime(dir));
fd.Add("lastWriteTime", File.GetLastWriteTime(dir));
fd.Add("objectType", "directory");
f.Add(fd);
}
foreach(string file in files) {
/* for some reason some files are listed that are not actually there
* for instance C:\WebDev.WebServer20.EXE shows in the list
* but is not in the directory. */
try {
Dictionary<string, object> fd = new Dictionary<string, object>();
fd.Add("name", file);
FileInfo info = new FileInfo(file);
fd.Add("size", info.Length);
fd.Add("creationTime", info.CreationTime);
fd.Add("lastAccessTime", info.LastAccessTime);
fd.Add("lastWriteTime", info.LastWriteTime);
fd.Add("objectType", info.Extension);
f.Add(fd);
} catch{ }
}
j.Add("files", f);
j.Add("error", 0);
j.Add("description", "");
}
} catch( Exception e ) {
j.Add( "error", -1 );
j.Add( "source", e.Source );
j.Add( "description", e.Message );
}
return j;
}
/// <summary>
/// Updates the cart based on the dictionary provided.
/// Pass the quantity of the item as qty+jguid(cartId) or as the jguid(cartId)
/// All other form variables should be passed using their cartDetailId.
/// </summary>
/// <param name="args">The args.</param>
/// <returns>{error:0,desc:"error description",items:item Collection,subTotal:x,taxTotal:x,estShipTotal:x,discountTotal:x,grandTotal:x,addresses:addressCollection}.</returns>
public static Dictionary<string, object> UpdateCart(Dictionary<string, object> args)
{
("FUNCTION /w SP updateCart").Debug(10);
Dictionary<string, object> j = new Dictionary<string, object>();
Session session = null;
if(args.ContainsKey("sessionId")) {
session = new Session(Main.Site, new Guid((string)args["sessionId"]));
} else {
session = Main.GetCurrentSession();
}
if(session.Cart.Items.Count == 0) {
session.Cart.Refresh();
}
foreach(Commerce.CartItem i in session.Cart.Items) {
/* check for each QTY key, if the key exists then update this item. */
if(args.ContainsKey(i.CartId.EncodeXMLId())) {
string formId = i.CartId.EncodeXMLId();
int qty = 0;/* if a qty was passed, and it turns out not to be numeric, then you loose the item */
if(!int.TryParse(args[formId].ToString(), out qty)) {
qty = 0;
}
Guid addressId = Guid.Empty;
if(args.ContainsKey("addressId")) {
addressId = new Guid(args["addressId"].ToString());
}
SqlCommand cmd = new SqlCommand(Cart.UPDATE_CART_QUERY, Site.SqlConnection);
cmd.Parameters.Add("@qty", SqlDbType.Int).Value = args[i.CartId.EncodeXMLId()];
cmd.Parameters.Add("@price", SqlDbType.Money).Value = 0;
cmd.Parameters.Add("@cartId", SqlDbType.UniqueIdentifier).Value = new Guid(i.CartId.ToString());
cmd.Parameters.Add("@setPrice", SqlDbType.Bit).Value = false;
cmd.Parameters.Add("@addressId", SqlDbType.UniqueIdentifier).Value = addressId;
cmd.ExecuteNonQuery();
cmd.Dispose();
UpdateCartDetail(i, args);
}
}
session.Cart.Refresh();
List<object> items = new List<object>();
foreach(Commerce.CartItem i in session.Cart.Items) {
Dictionary<string, object> jt = new Dictionary<string, object>();
jt.Add("cartId", i.CartId);
jt.Add("price", i.Price);
jt.Add("qty", i.Qty);
jt.Add("addressId", i.AddressId);
jt.Add("inputs", i.Inputs);
items.Add(jt);
}
j.Add("items", items);
j.Add("subTotal", (float)session.Cart.SubTotal);
j.Add("taxTotal", (float)session.Cart.TaxTotal);
j.Add("estShipTotal", (float)session.Cart.EstShipTotal);
j.Add("discountTotal", (float)session.Cart.DiscountTotal);
j.Add("grandTotal", (float)session.Cart.GrandTotal);
j.Add("addresses", session.Cart.Addresses);
j.Add("error", 0);
j.Add("description", "");
return j;
}
/// <summary>
/// Imports a zip to zone table.
/// </summary>
/// <param name="preview">if set to <c>true</c> [preview].</param>
/// <param name="dupeMode">if set to <c>true</c> [dupe mode].</param>
/// <param name="zones">The zones.</param>
/// <param name="carrierId">The Carrier id.</param>
/// <param name="service">The service.</param>
/// <returns></returns>
public static List<object> ImportZipToZone( bool preview, bool dupeMode, List<object> zones, string carrierId, string service )
{
List<object> outputItemList = new List<object>();
using(SqlConnection cn = Site.CreateConnection(true, true)) {
cn.Open();
using( SqlTransaction trns = cn.BeginTransaction( "Import areaSurcharge" ) ) {
string zipRange = "";
string shipZone = "";
int fromZip = 0;
int toZip = 0;
using( SqlCommand cmd = new SqlCommand( @"delete from zipToZone where carrier = @carrierId and service = @service", cn, trns ) ) {
cmd.Parameters.Add( "@carrierId", SqlDbType.Int ).Value = carrierId;
cmd.Parameters.Add( "@service", SqlDbType.Int ).Value = service;
cmd.ExecuteNonQuery();/* get rid of existing rates */
}
foreach( Dictionary<string, object> keys in zones ) {
List<object> columns = new List<object>();
if( keys.ContainsKey( "zipRange" ) && keys.ContainsKey( "shipZone" ) ) {
zipRange = keys[ "zipRange" ].ToString();
shipZone = keys[ "shipZone" ].ToString();
if( Regex.IsMatch( zipRange, "^[0-9]{3,5}-?[0-9]{0,5}$" )
&& Regex.IsMatch( shipZone, "^[0-9]{1,3}$" )
&& Regex.IsMatch( service, "^[0-9]{1,3}$" ) ) {
if( zipRange.Contains( "-" ) ) {
/* rage of codes, not a single code */
string[] range = zipRange.Split( '-' );
switch( range[ 0 ].Length ) {
case 1: range[ 0 ] = range[ 0 ] + "0000"; break;
case 2: range[ 0 ] = range[ 0 ] + "000"; break;
case 3: range[ 0 ] = range[ 0 ] + "00"; break;
case 4: range[ 0 ] = range[ 0 ] + "0"; break;
}
switch( range[ 1 ].Length ) {
case 1: range[ 1 ] = range[ 1 ] + "9999"; break;
case 2: range[ 1 ] = range[ 1 ] + "999"; break;
case 3: range[ 1 ] = range[ 1 ] + "99"; break;
case 4: range[ 1 ] = range[ 1 ] + "9"; break;
}
int z = 0;
int s = 0;
if( ( int.TryParse( range[ 0 ], out fromZip )
&& int.TryParse( range[ 1 ], out toZip )
&& int.TryParse( shipZone, out z )
&& int.TryParse( service, out s ) ) ) {
using( SqlCommand cmd = new SqlCommand( @"insert into zipToZone
(zipToZoneId,carrier,sourceZip,service,fromzip,toZip,shipZone,VerCol)
values (newId(),@carrierId,@sourceZip,@service,@fromZip,@toZip,@shipZone,null)", cn, trns ) ) {
cmd.Parameters.Add( "@carrierId", SqlDbType.Int ).Value = carrierId;
cmd.Parameters.Add( "@sourceZip", SqlDbType.Int ).Value = Main.Site.company_zip.Substring( 0, 5 );
cmd.Parameters.Add( "@service", SqlDbType.Int ).Value = service;
cmd.Parameters.Add( "@fromZip", SqlDbType.Int ).Value = fromZip;
cmd.Parameters.Add( "@toZip", SqlDbType.Int ).Value = toZip;
cmd.Parameters.Add( "@shipZone", SqlDbType.Int ).Value = shipZone;
cmd.ExecuteNonQuery();
}
columns.Add( fromZip.ToString() + "-" + toZip.ToString() );
columns.Add( shipZone );
}
} else {
int zip = 0;
int z = 0;
int s = 0;
switch( zipRange.Length ) {
case 1: zipRange = zipRange + "0000"; break;
case 2: zipRange = zipRange + "000"; break;
case 3: zipRange = zipRange + "00"; break;
case 4: zipRange = zipRange + "0"; break;
}
if( ( int.TryParse( zipRange, out zip )
&& int.TryParse( shipZone, out z )
&& int.TryParse( service, out s ) ) ) {
/* just a single zip to bo in both */
using( SqlCommand cmd = new SqlCommand( @"insert into zipToZone
(zipToZoneId,carrier,sourceZip,service,fromzip,toZip,shipZone,VerCol)
values (newId(),@carrierId,@sourceZip,@service,@fromZip,@toZip,@shipZone,null)", cn, trns ) ) {
cmd.Parameters.Add( "@carrierId", SqlDbType.Int ).Value = carrierId;
cmd.Parameters.Add( "@sourceZip", SqlDbType.Int ).Value = Main.Site.company_zip.Substring( 0, 5 );
cmd.Parameters.Add( "@service", SqlDbType.Int ).Value = service;
cmd.Parameters.Add( "@fromZip", SqlDbType.Int ).Value = zip;
cmd.Parameters.Add( "@toZip", SqlDbType.Int ).Value = zip;
cmd.Parameters.Add( "@shipZone", SqlDbType.Int ).Value = shipZone;
cmd.ExecuteNonQuery();
}
columns.Add( zip.ToString() + "-" + zip.ToString() );
columns.Add( shipZone );
}
}
}
}
outputItemList.Add( columns );
}
if( preview ) {
trns.Rollback();
} else {
trns.Commit();
}
}
}
return outputItemList;
}
/// <summary>
/// Parses CSV and XLS files for import.
/// </summary>
/// <param name="args">{importFilePath:path,sheetNumber:XLS sheet number,ignoreBlanks:true/false}</param>
/// <returns></returns>
public static Dictionary<string, object> ImportWizard( Dictionary<string, object> args )
{
Dictionary<string, object> j = new Dictionary<string, object>();
List<object> l = new List<object>();
string importFilePath = "";
bool ignoreBlanks = false;
j.Add( "error", 0 );
j.Add( "description", "" );
if( !args.ContainsKey( "importFilePath" ) ) {
j[ "error" ] = -2;
j[ "description" ] = "Missing key: importFilePath";
return j;
} else {
importFilePath = ( string )args[ "importFilePath" ];
}
int sheetNumber;
if( !int.TryParse( args[ "sheetNumber" ].ToString(), out sheetNumber ) ) {
sheetNumber = 0;
}
if( args.ContainsKey( "ignoreBlanks" ) ) {
ignoreBlanks = ( bool )args[ "ignoreBlanks" ];
}
try {
if( !File.Exists( importFilePath ) ) {
Exception e = new Exception( "File not found:" + importFilePath );
throw e;
}
OleDbConnection cn;
OleDbCommand cmd;
OleDbDataAdapter ad = new OleDbDataAdapter();
DataSet ds = new DataSet();
if( importFilePath.ToLower().EndsWith( ".xls" ) ) {
string connection = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + importFilePath + ";" +
"Extended Properties='Excel 8.0;IMEX=1';";
cn = new OleDbConnection( connection );
cn.Open();
DataTable sheets = cn.GetOleDbSchemaTable( System.Data.OleDb.OleDbSchemaGuid.Tables, null );
if( sheetNumber > sheets.Rows.Count ) {
Exception e = new Exception( "Only " + sheets.Rows.Count + " were found in the XLS file, but you selected sheet " + sheetNumber );
throw e;
}
cmd = new OleDbCommand( "select * from [" + sheets.Rows[ sheetNumber ].ItemArray[ 2 ] + "]", cn );
} else if( importFilePath.ToLower().EndsWith( ".csv" ) ) {
string connection = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + Path.GetDirectoryName( importFilePath ) + ";" +
"Extended Properties='text;IMEX=1;HDR=Yes;FMT=Delimited(,)';";
cn = new OleDbConnection( connection );
cn.Open();
cmd = new OleDbCommand( "select * from [" + Path.GetFileName( importFilePath ) + "]", cn );
} else {
Exception e = new Exception( "Unsupported file format." );
throw e;
}
List<string[]> parse = new List<string[]>();
using( OleDbDataReader d = cmd.ExecuteReader() ) {
while( d.Read() ) {
int h = d.FieldCount;
List<string> lst = new List<string>();
for( int x = 0; h > x; x++ ) {
lst.Add( d.GetValue( x ).ToString() );
}
parse.Add( lst.ToArray() );
}
}
if( cn.State == ConnectionState.Open ) {
cn.Close();
}
j.Add( "parse", parse );
} catch( Exception e ) {
j[ "error" ] = -1;
j[ "description" ] = e.Message;
}
return j;
}
/// <summary>
/// Imports a surcharge table.
/// </summary>
/// <param name="preview">if set to <c>true</c> [preview].</param>
/// <param name="dupeMode">if set to <c>true</c> [dupe mode].</param>
/// <param name="rates">The rates.</param>
/// <param name="carrierId">The Carrier id.</param>
/// <returns></returns>
public static List<object> ImportSurcharge( bool preview, bool dupeMode, List<object> rates, string carrierId )
{
List<object> outputSurchargeList = new List<object>();
using(SqlConnection cn = Site.CreateConnection(true, true)) {
cn.Open();
using( SqlTransaction trns = cn.BeginTransaction( "Import areaSurcharge" ) ) {
using( SqlCommand cmd = new SqlCommand( @"delete from areaSurcharge where carrier = @carrierId", cn, trns ) ) {
cmd.Parameters.Add( "@carrierId", SqlDbType.Int ).Value = carrierId;
cmd.ExecuteNonQuery();/* get rid of existing rates */
}
foreach( Dictionary<string, object> keys in rates ) {
if( keys.Count > 0 ) {
if( keys.ContainsKey( "deliveryArea" ) ) {
if( keys[ "deliveryArea" ].ToString() != "" ) {
List<object> columns = new List<object>();
foreach( KeyValuePair<string, object> key in keys ) {
if( key.Key.ToString().StartsWith( "deliveryArea" ) ) {
string deliveryArea = key.Value.ToString().Trim();
if( Regex.IsMatch( deliveryArea, "^[0-9]{3,5}$" ) ) {
using( SqlCommand cmd = new SqlCommand( @"insert into areaSurcharge (areaSurchargeId,deliveryArea,carrier,VerCol)
values (newId(),@deliveryArea,@carrierId,null)", cn, trns ) ) {
cmd.Parameters.Add( "@deliveryArea", SqlDbType.Int ).Value = deliveryArea;
cmd.Parameters.Add( "@carrierId", SqlDbType.Int ).Value = carrierId;
cmd.ExecuteNonQuery();
}
columns.Add( deliveryArea );
} else {
columns.Add( "" );
}
}
}
outputSurchargeList.Add( columns );
}
}
}
}
if( preview ) {
trns.Rollback();
} else {
trns.Commit();
}
}
}
return outputSurchargeList;
}
/// <summary>
/// Imports a rates table.
/// </summary>
/// <param name="preview">if set to <c>true</c> [preview].</param>
/// <param name="dupeMode">if set to <c>true</c> [dupe mode].</param>
/// <param name="rates">The rates.</param>
/// <param name="rateId">The rate id.</param>
/// <returns></returns>
public static List<object> ImportRates( bool preview, bool dupeMode, List<object> rates, string rateId )
{
Dictionary<string, object> j = new Dictionary<string, object>();
List<object> outputRateList = new List<object>();
using(SqlConnection cn = Site.CreateConnection(true, true)) {
cn.Open();
using( SqlTransaction trns = cn.BeginTransaction( "Import rates" ) ) {
string weight = "0";
string shipZone = "0";
string cost = "0";
int lastWeight = -1;
using( SqlCommand cmd = new SqlCommand( @"delete from shipZone where rate = @rateId", cn, trns ) ) {
cmd.Parameters.Add( "@rateId", SqlDbType.Int ).Value = rateId;
cmd.ExecuteNonQuery();/* get rid of existing rates */
}
foreach( Dictionary<string, object> keys in rates ) {
List<object> columns = new List<object>();
foreach( KeyValuePair<string, object> key in keys ) {
if( key.Key == "weight" ) {
weight = key.Value.ToString();
weight = weight.Replace( "letter", "0" ).Replace( "envelope", "0" ).Replace( "Letter", "0" ).Replace( "Envelope", "0" )
.Replace( "LETTER", "0" ).Replace( "ENVELOPE", "0" ).Replace( "lbs.", "" ).Replace( "Lbs.", "" ).Trim();
columns.Add( weight );
} else if( key.Key.ToString().StartsWith( "zone" ) ) {
shipZone = key.Key.ToString().Replace( "zone", "" ).Trim();
cost = key.Value.ToString().Replace( "$", "" ).Trim();
if( Regex.IsMatch( weight, "^[0-9]+$" ) && Regex.IsMatch( cost, "^([0-9.])+$" ) && Regex.IsMatch( shipZone, "^[0-9]+$" ) ) {
Dictionary<string, object> sz = new Dictionary<string, object>();
sz.Add( "weight", weight );
sz.Add( "shipZone", shipZone );
sz.Add( "cost", cost );
using( SqlCommand cmd = new SqlCommand( @"insert into shipZone (zoneId,rate,weight,shipZone,cost,VerCol)
values (newId(),@rateId,@weight,@zone,@cost,null)", cn, trns ) ) {
cmd.Parameters.Add( "@rateId", SqlDbType.Int ).Value = rateId;
cmd.Parameters.Add( "@weight", SqlDbType.Int ).Value = weight;
cmd.Parameters.Add( "@zone", SqlDbType.Int ).Value = shipZone;
cmd.Parameters.Add( "@cost", SqlDbType.Money ).Value = cost;
cmd.ExecuteNonQuery();
}
columns.Add( cost );
}
}
if( lastWeight.ToString() != weight ) {
if( Regex.IsMatch( weight, "^[0-9]+$" ) && Regex.IsMatch( cost, "^([0-9.])+$" ) && Regex.IsMatch( shipZone, "^[0-9]+$" ) ) {
if( columns.Count > 0 ) {
if( columns[ 0 ].ToString().Length > 0 ) {
lastWeight = Convert.ToInt32( weight );
outputRateList.Add( columns );
}
}
}
}
}
}
if( preview ) {
trns.Rollback();
} else {
trns.Commit();
}
}
}
return outputRateList;
}
/// <summary>
/// Initializes a new instance of the <see cref="Payment"/> class.
/// </summary>
/// <param name="paymentMethodId">The payment method id.</param>
public Payment(Guid paymentMethodId)
{
PaymentRefrences = new List<PaymentReference>();
using(SqlConnection cn = Site.CreateConnection(true, true)) {
cn.Open();
using(SqlCommand cmd = new SqlCommand(@"select p.paymentMethodId, paymentType,
cardName, cardType, cardNumber, expMonth, expYear, secNumber, userId, sessionId,
addressId, routingNumber, checkNumber, bankAccountNumber, payPalEmailAddress, swift,
bankName, routingTransitNumber, cash, notes, p.amount totalPayment,
generalLedgerInsertId, promiseToPay, paymentRefId, pd.paymentMethodDetailId,
pd.orderId, pd.amount from paymentMethods p with (nolock)
inner join paymentMethodsDetail pd with (nolock) on p.paymentMethodId = pd.paymentMethodId
where p.paymentMethodId = @paymentMethodId and not generalLedgerInsertId = '00000000-0000-0000-0000-000000000000';", cn)) {
cmd.Parameters.Add("@paymentMethodId", SqlDbType.UniqueIdentifier).Value = paymentMethodId;
using(SqlDataReader r = cmd.ExecuteReader()) {
int i = 0;
while(r.Read()) {
/* populate the parent object on the first row */
if(i == 0) {
Id = r.GetGuid(0);
PaymentType = r.GetString(1);
CardName = r.GetString(2);
CardType = r.GetString(3);
CardNumber = r.GetString(4);
ExpMonth = r.GetString(5);
ExpYear = r.GetString(6);
SecNumber = r.GetString(7);
UserId = r.GetInt32(8);
SessionId = r.GetGuid(9);
AddressId = r.GetGuid(10);
RoutingNumber = r.GetString(11);
CheckNumber = r.GetString(12);
BankAccountNumber = r.GetString(13);
PayPalEmailAddress = r.GetString(14);
SWIFT = r.GetString(15);
BankName = r.GetString(16);
RoutingTransitNumber = r.GetString(17);
Cash = r.GetBoolean(18);
Notes = r.GetString(19);
Amount = r.GetDecimal(20);
GeneralLedgerInsertId = r.GetGuid(21);
PromiseToPay = r.GetBoolean(22);
PaymentRefId = r.GetInt32(23);
}
PaymentRefrences.Add(new Commerce.PaymentReference(r.GetGuid(24), this, r.GetInt32(25), r.GetDecimal(26)));
i++;
}
}
}
}
}
/// <summary>
/// Pays with existing payment.
/// </summary>
/// <param name="paymentMethodIds">The list of paymentMethodIds.</param>
/// <param name="amount">The amount.</param>
/// <param name="userId">The userId.</param>
/// <param name="postingDate">The posting date.</param>
/// <param name="orderIds">The order ids.</param>
/// <param name="cn">The sql connection (or null).</param>
/// <param name="trans">The sql transaction (or null).</param>
/// <returns>{error:0,desc:"error description"}.</returns>
public static Dictionary<string, object> PayWithExistingPaymentMethods( List<object> paymentMethodIds,
decimal amount, int userId, DateTime postingDate, List<object> orderIds,
SqlConnection cn, SqlTransaction trans )
{
int errorId = 0;
string desc = "";
List<int> intIds = orderIds.ConvertAll( delegate( object i ) {
return Convert.ToInt32( i );
} );
Dictionary<string, object> j = new Dictionary<string, object>();
/* before updating - check to ensure that there really is enouch left over on this paymentMethodId(s)
* to attach the desiered amount to the selected order
*/
using( SqlCommand cmd = new SqlCommand() ) {
List<SqlDataRecord> rec_paymentMethodIds = new List<SqlDataRecord>();
List<SqlDataRecord> rec_orderIds = new List<SqlDataRecord>();
SqlMetaData[] hashTable = {
new SqlMetaData("keyName",SqlDbType.VarChar,100),
new SqlMetaData("keyValue",SqlDbType.Variant),
new SqlMetaData("primary_key",SqlDbType.Bit),
new SqlMetaData("dataType",SqlDbType.VarChar,50),
new SqlMetaData("dataLength",SqlDbType.Int),
new SqlMetaData("varCharMaxValue",SqlDbType.VarChar,-1)
};
foreach( string id in paymentMethodIds ) {
SqlDataRecord rec = new SqlDataRecord( hashTable );
rec.SetValue( 0, "paymentMethodId" );
rec.SetValue( 1, id );
rec.SetBoolean( 2, false );
rec.SetString( 3, "uniqueidentifier" );
rec.SetValue( 4, 32 );
rec_paymentMethodIds.Add( rec );
}
foreach( int id in intIds ) {
SqlDataRecord rec = new SqlDataRecord( hashTable );
rec.SetValue( 0, "orderId" );
rec.SetValue( 1, id );
rec.SetBoolean( 2, false );
rec.SetString( 3, "int" );
rec.SetValue( 4, 8 );
rec_orderIds.Add( rec );
}
cmd.Connection = cn;
cmd.Transaction = trans;
cmd.CommandType = CommandType.StoredProcedure;
/* this SP will return a single row with error, desc saying if the procedure was successfull.
* the SP sums the remaning total value left on the selected paymentMethods and compares
* it to the amount trying to be paid. If the remaining amount is >= the amount trying to
* be paid the payments will be attached, if the remaining amount is < the amoun trying to
* be paid an error will be returned saying as much.
*/
cmd.CommandText = "dbo.attachPaymentMethods";
cmd.Parameters.Add( "@amountTryingToBePaid", SqlDbType.Money ).Value = amount;
cmd.Parameters.Add( "@paymentMethodIds", SqlDbType.Structured );
cmd.Parameters[ "@paymentMethodIds" ].Direction = ParameterDirection.Input;
if( rec_paymentMethodIds.Count == 0 ) {
string message = "You must select at least one payment method.";
message.Debug( 7 );
Exception ex = new Exception( message );
throw ex;
} else {
cmd.Parameters[ "@paymentMethodIds" ].Value = rec_paymentMethodIds;
}
cmd.Parameters.Add( "@orderIds", SqlDbType.Structured );
cmd.Parameters[ "@orderIds" ].Direction = ParameterDirection.Input;
if( rec_orderIds.Count == 0 ) {
string message = "You must select at least one payment method.";
message.Debug( 7 );
Exception ex = new Exception( message );
throw ex;
} else {
cmd.Parameters[ "@orderIds" ].Value = rec_orderIds;
}
using( SqlDataReader r = cmd.ExecuteReader() ) {
/* batch 1 is the status */
r.Read();
Dictionary<string, object> s = new Dictionary<string, object>();
errorId = r.GetInt32( 0 );
desc = r.GetString( 1 );
/* NOTE: Addtional callback information for attaching payments to orders
* I don't really care about this stuff so I'm not going to write anything to capture it
* but there is is for anyone who does want to capture it.
*/
/* batch 2 is the actual payment detail inserts (paymentMethodDetailId,paymentMethodId,refId,amount)*/
/* batch 3 is the actual upated orders (orderId, paid) */
}
}
if( errorId != 0 ) {
j.Add( "error", errorId );
j.Add( "description", desc );
} else {
j.Add( "error", 0 );
j.Add( "description", "" );
}
return j;
}
public static void Insert( string paymentType, Guid paymentMethodId, Guid addressId, int userId, Guid sessionId,
int termId, string reference, decimal amount, DateTime postingDate, List<int> orderIds,
string cardName, string cardType, string cardNumber, string expMonth, string expYear,
string secNumber, string routingNumber, string checkNumber, string bankAccountNumber,
string payPalEmailAddress, string swift, string bankName, string routingTransitNumber,
bool cash, string notes, bool _promiseToPay, SqlConnection cn, SqlTransaction trans )
{
String.Format( "Place Order > insertPaymentMethod for userId: {0}, type: {1}", userId, paymentType ).Debug( 7 );
try {
using(SqlCommand cmd=new SqlCommand()) {
List<SqlDataRecord> rowData=new List<SqlDataRecord>();
SqlMetaData[] hashTable= {
new SqlMetaData("keyName",SqlDbType.VarChar,100),
new SqlMetaData("keyValue",SqlDbType.Variant),
new SqlMetaData("primary_key",SqlDbType.Bit),
new SqlMetaData("dataType",SqlDbType.VarChar,50),
new SqlMetaData("dataLength",SqlDbType.Int),
new SqlMetaData("varCharMaxValue",SqlDbType.VarChar,-1)
};
StringBuilder s=new StringBuilder();
foreach(int id in orderIds) {
SqlDataRecord rec=new SqlDataRecord(hashTable);
rec.SetValue(0,"orderId");
rec.SetValue(1,id);
rec.SetBoolean(2,false);
rec.SetString(3,"int");
rec.SetValue(4,8);
rowData.Add(rec);
}
cmd.Connection=cn;
cmd.Transaction=trans;
cmd.CommandType=CommandType.StoredProcedure;
cmd.CommandText="dbo.insertPaymentMethod";
cmd.Parameters.Add("@paymentMethodId",SqlDbType.UniqueIdentifier).Value=paymentMethodId;
cmd.Parameters.Add("@paymentType",SqlDbType.VarChar).Value=paymentType;
cmd.Parameters.Add("@cardName",SqlDbType.VarChar).Value=cardName;
cmd.Parameters.Add("@cardType",SqlDbType.VarChar).Value=cardType.MaxLength(25,false);
cmd.Parameters.Add("@cardNumber",SqlDbType.VarChar).Value=cardNumber;
cmd.Parameters.Add("@expMonth",SqlDbType.VarChar).Value=expMonth;
cmd.Parameters.Add("@expYear",SqlDbType.VarChar).Value=expYear;
cmd.Parameters.Add("@secNumber",SqlDbType.VarChar).Value=secNumber;
cmd.Parameters.Add("@userId",SqlDbType.Int).Value=userId;
cmd.Parameters.Add("@sessionId",SqlDbType.UniqueIdentifier).Value=sessionId;
cmd.Parameters.Add("@addressId",SqlDbType.UniqueIdentifier).Value=addressId;
cmd.Parameters.Add("@routingNumber",SqlDbType.VarChar).Value="";
cmd.Parameters.Add("@checkNumber",SqlDbType.VarChar).Value="";
cmd.Parameters.Add("@bankAccountNumber",SqlDbType.VarChar).Value="";
cmd.Parameters.Add("@payPalEmailAddress",SqlDbType.VarChar).Value="";
cmd.Parameters.Add("@swift",SqlDbType.VarChar).Value="";
cmd.Parameters.Add("@bankName",SqlDbType.VarChar).Value="";
cmd.Parameters.Add("@routingTransitNumber",SqlDbType.VarChar).Value=routingTransitNumber;
cmd.Parameters.Add("@cash",SqlDbType.Bit).Value=cash;
cmd.Parameters.Add("@notes",SqlDbType.VarChar).Value=notes;
cmd.Parameters.Add("@termId",SqlDbType.Int).Value=termId;
cmd.Parameters.Add("@reference",SqlDbType.VarChar).Value=reference;
cmd.Parameters.Add("@amount",SqlDbType.Money).Value=amount;
cmd.Parameters.Add("@promiseToPay",SqlDbType.Bit).Value=_promiseToPay;
cmd.Parameters.Add("@orderIds",SqlDbType.Structured);
cmd.Parameters["@orderIds"].Direction=ParameterDirection.Input;
if(rowData.Count==0) {
cmd.Parameters["@orderIds"].Value=null;
} else {
cmd.Parameters["@orderIds"].Value=rowData;
}
cmd.ExecuteNonQuery();
cmd.Dispose();
}
} catch(Exception ex) {
String.Format("Place Order > insertPaymentMethod exception:{0}",ex.Message).Debug(0);
}
}
/// <summary>
/// Populates a google JSON row.
/// </summary>
/// <param name="r">The r.</param>
/// <param name="columnLength">Length of the column.</param>
/// <returns></returns>
private static Dictionary<string, object> populateGoogleRow(object[] r, int columnLength)
{
Dictionary<string, object> row = new Dictionary<string, object>();
List<Dictionary<string, object>> colData = new List<Dictionary<string, object>>();
for(int x = 0; x < columnLength; x++) {
Dictionary<string, object> col = new Dictionary<string, object>();
col.Add("v", r[x]);
col.Add("f", null);
colData.Add(col);
}
row.Add("c", colData);
return row;
}
/// <summary>
/// Initializes a new instance of the <see cref="ZipToZones"/> class.
/// </summary>
/// <param name="site">The site.</param>
public ZipToZones(Site site)
{
List = new List<ZipToZone>();
using(SqlConnection cn = Site.CreateConnection(true, true)) {
cn.Open();
using(SqlCommand cmd = new SqlCommand("dbo.getZipToZones", cn)) {
using(SqlDataReader zoneList = cmd.ExecuteReader()) {
while(zoneList.Read()) {
List.Add(new ZipToZone(
zoneList.GetGuid(0),
zoneList.GetInt32(1),
zoneList.GetInt32(2),
zoneList.GetInt32(3),
zoneList.GetInt32(4),
zoneList.GetInt32(5),
zoneList.GetInt32(6)
));
}
}
}
}
}
/// <summary>
/// Handles file uploads, supplies a progress meter using class ProgressInfo.
/// </summary>
public static void Iis6Upload()
{
( "FUNCTION /w SP,ADHOC,fileSystem,binaryStream upload" ).Debug( 10 );
Dictionary<string, object> j = new Dictionary<string, object>();
string uploadId = HttpContext.Current.Request.QueryString[ "uploadId" ];
string explicitTarget = HttpContext.Current.Request.QueryString[ "target" ];
string targetDirectory = Main.PhysicalApplicationPath;
string userRootDir = targetDirectory + "\\user\\";
string userDir = targetDirectory + "\\user\\" + Convert.ToString( Session.CurrentSession.UserId );
string publicDir = targetDirectory + "\\user\\public";
string tempDir = targetDirectory + "\\temp";
if( !Directory.Exists( userRootDir ) ) {
Directory.CreateDirectory( userRootDir );
}
if( !Directory.Exists( tempDir ) ) {
Directory.CreateDirectory( tempDir );
}
if(Session.CurrentSession.UserId == -1) {
targetDirectory = publicDir;
if( !Directory.Exists( publicDir ) ) {
Directory.CreateDirectory( publicDir );
}
} else {
targetDirectory = userDir;
if( !Directory.Exists( userDir ) ) {
Directory.CreateDirectory( userDir );
}
}
if( explicitTarget.Length > 0 ) {
if( Directory.Exists( explicitTarget ) ) {
targetDirectory = explicitTarget;
}
}
const int BUFFER_SIZE = 16384;
/* find the raw request */
HttpWorkerRequest request = ( HttpWorkerRequest )HttpContext.Current.GetType().GetProperty( "WorkerRequest", ( BindingFlags )36 ).GetValue( HttpContext.Current, null );
string contentType = HttpContext.Current.Request.ContentType;
if( contentType.Contains( "multipart/form-data" ) ) {
using(Impersonation imp = new Impersonation()) {
Guid id = new Guid(uploadId);
ProgressInfo u = new ProgressInfo(id);
if(!Main.ProgressInfos.ContainsKey(id)) {
Main.ProgressInfos.Add(id, u);
} else {
Main.ProgressInfos[id] = u;
}
string boundary = contentType.Substring(contentType.IndexOf("boundary=") + 9);
List<string> delList = new List<string>();
byte[] binBoundary = System.Text.Encoding.UTF8.GetBytes(boundary);
byte[] binFileNameProp = System.Text.Encoding.UTF8.GetBytes("filename=\"");
u.TotalItemSize = Convert.ToInt32(request.GetKnownRequestHeader(HttpWorkerRequest.HeaderContentLength));
u.CurrentItemName = "multipart/form-data stream";
if(u.TotalItemSize < 0) {
u.Complete = true;
return;
}; /* over size */
u.CurrentSizeComplete = 0;
int bytes = BUFFER_SIZE;
byte[] buffer = new byte[BUFFER_SIZE];
Guid tempFileId = System.Guid.NewGuid();
buffer.Equals(buffer);
byte[] body = request.GetPreloadedEntityBody();
u.CurrentSizeComplete += body.Length;
string tempFile = Main.PhysicalApplicationPath + "\\temp\\temp_" + tempFileId.ToString() + ".tmp";
delList.Add(tempFile);
try {
long tickCount = Environment.TickCount;
long tickTimeout = 1000;
u.Started = DateTime.Now;
using(FileStream fs = File.Create(tempFile)) {
fs.Write(body, 0, body.Length);
if(!request.IsEntireEntityBodyIsPreloaded()) {
while((u.TotalItemSize - u.CurrentSizeComplete) >= bytes) {
bytes = request.ReadEntityBody(buffer, buffer.Length);
u.CurrentSizeComplete += bytes;
u.Updated = DateTime.Now;
fs.Write(buffer, 0, bytes);
if(bytes > 0) {
tickCount = Environment.TickCount;
}
if(Environment.TickCount - tickCount > tickTimeout) {
("HTTP client tick timedout during upload.").Debug(4);
u.Complete = true;
File.Delete(tempFile);
return;
}
}
bytes = request.ReadEntityBody(buffer, (u.TotalItemSize - u.CurrentSizeComplete));
fs.Write(buffer, 0, bytes);
u.CurrentSizeComplete += bytes;
}
}
/* file(s) downloaded now parse the request file */
List<long> boundaries = new List<long>();
List<long> fileNames = new List<long>();
long boundaryLength = binBoundary.Length;
long bufferSize = 10240;
using(FileStream fs = File.OpenRead(tempFile)) {
boundaries = fs.Find(binBoundary, 0);/*get all the boundraies in the upload*/
List<string> tmpFilePaths = new List<string>();
u.TotalItemSize = Convert.ToInt32(fs.Length);
for(var x = 0; boundaries.Count - 1 > x; x++) {
byte[] cBuffer = new byte[bufferSize];
string fileName = "";
long endAt = 0;
long headerEndsAt = 0;
string destFilePath = Main.PhysicalApplicationPath + "\\temp\\temp_" + tempFileId.ToString() + "___" + Convert.ToString(x) + ".tmp";
if(boundaries.Count - 1 == x) {
endAt = fs.Length;
} else {
endAt = boundaries[x + 1];
}
using(FileStream ds = File.Create(destFilePath)) {
u.CurrentSizeComplete = Convert.ToInt32(boundaries[x]);
fs.Position = boundaries[x];
headerEndsAt = fs.FindFirst(new byte[4] { 13, 10, 13, 10 }, boundaries[x]) + 6;
fs.Position = boundaries[x];
byte[] headerBytes = new byte[headerEndsAt - boundaries[x]];
bytes = fs.Read(headerBytes, 0, headerBytes.Length);
string header = System.Text.Encoding.UTF8.GetString(headerBytes);
int filenameStart = header.IndexOf("filename=\"") + 10;
int filenameEnds = header.IndexOf("\"", filenameStart);
fileName = header.Substring(filenameStart, (filenameEnds - filenameStart));
u.CurrentItemName = fileName;
/* move to the end of the header */
fs.Position = headerEndsAt;
/*copy the data from the sumission temp file into the finished temp file*/
bytes = 1;
long dataLength = endAt - headerEndsAt;
long readIn = 0;
int remainder = Convert.ToInt32(dataLength % cBuffer.Length);
if(dataLength > cBuffer.Length) {
while((readIn + cBuffer.Length) < dataLength) {
bytes = fs.Read(cBuffer, 0, cBuffer.Length);
ds.Write(cBuffer, 0, cBuffer.Length);
readIn += cBuffer.Length;
}
} else {
remainder = Convert.ToInt32(dataLength);
}
if(remainder > 0) {
fs.Read(cBuffer, 0, remainder);
ds.Write(cBuffer, 0, remainder);
}
if(fileName.Length > 0 && filenameStart > 9) {
tmpFilePaths.Add(destFilePath + "|" + fileName);
}
delList.Add(destFilePath);
}
}
for(var y = 0; tmpFilePaths.Count > y; y++) {
string tempFileLocation = tmpFilePaths[y].Split('|')[0];
string orgFileNameWithExt = tmpFilePaths[y].Split('|')[1];
string orgFileExt = Path.GetExtension(orgFileNameWithExt);
string orgFileName = Path.GetFileNameWithoutExtension(orgFileNameWithExt);
string newFileName = orgFileName;
string newFileNameWithExt = orgFileName + orgFileExt;
int f = 0;
while(File.Exists(Path.Combine(targetDirectory, newFileNameWithExt))) {
newFileNameWithExt = newFileName + "(" + Convert.ToString(f + 1) + ")" + orgFileExt;
f++;
}
File.Move(tempFileLocation, Path.Combine(targetDirectory, newFileNameWithExt));
u.UploadedFiles.Add(Path.Combine(targetDirectory, newFileNameWithExt));
}
}
for(var y = 0; delList.Count > y; y++) {
File.Delete(delList[y]);
}
u.Complete = true;
} catch(Exception e) {
(e.Message).Debug(0);
u.Complete = true;
for(var y = 0; delList.Count > y; y++) {
File.Delete(delList[y]);
}
}
}
}
try {
HttpContext.Current.Response.Redirect( Main.AdminDirectory + "/js/blank.html" );
request.FlushResponse( true );
request.EndOfRequest();
request.CloseConnection();
} finally {
}
return;
}
/// <summary>
/// Gets the logical drives.
/// </summary>
/// <returns></returns>
public static List<object> GetLogicalDrives()
{
( "FUNCTION /w (!PRIVATE ACCESS ONLY!),fileSystem getLogicalDrives" ).Debug( 10 );
List<object> j = new List<object>();
string[] drives = Directory.GetLogicalDrives();
foreach( string drive in drives ) {
Dictionary<string, object> fd = new Dictionary<string, object>();
fd.Add( "name", drive );
j.Add( fd );
}
return j;
}
/// <summary>
/// Refreshes the price filters based on the list of items fed to it
/// used in the list class to create and then filter lists of items.
/// </summary>
/// <param name="item_list">The item_list.</param>
/// <param name="priceRanges">The price ranges.</param>
/// <returns></returns>
private static List<Dictionary<string, object>> GetPriceFilters(List<Commerce.Item> item_list,
List<decimal> priceRanges)
{
List<Dictionary<string, object>> priceFilters = new List<Dictionary<string, object>>();
int ptotal = priceRanges.Count;
priceRanges.Sort(delegate(decimal p1, decimal p2) {
return p1.CompareTo(p2);
});
for(int x = 0; ptotal > x; x++) {
/* figure out the range */
decimal low = 0;
decimal high = 0;
bool highest = false;
if(x == 0) {
low = 0;
high = priceRanges[x];
} else if(x == ptotal - 1) {
low = priceRanges[x];
high = decimal.MaxValue;
highest = true;
} else {
low = priceRanges[x];
high = priceRanges[x + 1];
}
List<Commerce.Item> currentPriceList = item_list.FindAll(delegate(Commerce.Item itm) {
/* figure out the effective price */
decimal ePrice = itm.GetEffectivePrice();
return ePrice > low && ePrice < high;
});
Dictionary<string, object> l = new Dictionary<string, object>();
/* find the highest value item */
decimal maxValue = 0;
foreach(Commerce.Item _i in currentPriceList) {
decimal p = _i.GetEffectivePrice();
maxValue = p > maxValue ? p : maxValue;
}
l.Add("id", priceRanges[x]);
l.Add("low", low);
l.Add("high", highest ? maxValue : high);
l.Add("list", currentPriceList);
l.Add("count", currentPriceList.Count);
priceFilters.Add(l);
}
return priceFilters;
}
/// <summary>
/// Exports the files.
/// </summary>
private void exportFiles()
{
/* create a connection and transaction in case this fails */
string transactionName = "quickbooksExport";
SqlConnection cn = Site.CreateConnection(true, true);
cn.Open();
SqlTransaction trans=cn.BeginTransaction(transactionName);
this.Message="Checking for orders to import.";
("Quickbooks 10 Export: " + this.Message).Debug(10);
/* generate file names */
exportDate=DateTime.Now;
string dateSuffix=exportDate.ToString("MM-dd-yyyy.hh-mm-ss");
StringBuilder billFileBuffer=new StringBuilder(@"");
StringBuilder invoiceFileBuffer=new StringBuilder(@"");
StringBuilder billPaymentBuffer=new StringBuilder(@"");
StringBuilder customerPaymentBuffer=new StringBuilder(@"");
/* there are four potential output files */
invoiceFileName="Invoice_"+dateSuffix+".iif";
customer_paymentFileName="Customer_Payment_"+dateSuffix+".iif";
billFileName="Bill_"+dateSuffix+".iif";
bill_paymentFileName="Invoice_"+dateSuffix+".iif";
("Quickbooks 10 Export: " + this.Message).Debug(10);
/* get the list of orders to export and close. This represents bills and invoices. */
List<int> orderIds=new List<int>();
using(SqlCommand cmd=new SqlCommand(@"/* Quickbooks IIF Export. Check for orders to export */
select orderId from orders o with (nolock)
where not readyForExport = '1/1/1900 00:00:00.000' and not orderId in
(
select convert(int,cid) from
(select controlId as cid from dbo.exportFileAudit a with (nolock) where controlIdType = '384D7FB0-164C-40FB-8FBB-B4CF0B77B1AF') cids
)
", cn,trans)) {
using(SqlDataReader r=cmd.ExecuteReader()) {
while(r.Read()) {
int orderId=r.GetInt32(0);
orderIds.Add(orderId);
}
}
}
/* WARNING: THIS DOES NOT WORK - IT WILL NEVER WORK. quickbooks does not support importing
* payments to be associated with existing order because it's a f*****g steaming pile of useless shit
*
* get the list of payments and close. This represents the bill and customer payment files */
List<Guid> paymentIds=new List<Guid>();
using(SqlCommand cmd=new SqlCommand(@"/* Quickbooks IIF Export. Check for payments to export */
declare @emptyGuid uniqueidentifier = '00000000-0000-0000-0000-000000000000';
declare @importPromiseToPay bit = 0;
select paymentMethodId from paymentMethods with (nolock)
where generalLedgerInsertId = @emptyGuid and (promiseToPay = 0 or promiseToPay = @importPromiseToPay) and paymentMethodId not in
(
select convert(uniqueidentifier,cid) from
(select controlId as cid from dbo.exportFileAudit a with (nolock) where controlIdType = '3511CB96-333C-466D-AD3E-347DD75BD315') cids
)
",cn,trans)) {
cmd.Parameters.Add("@controlIdType",SqlDbType.UniqueIdentifier).Value=new Guid(orderControlId);
using(SqlDataReader r=cmd.ExecuteReader()) {
if(r.HasRows) {
while(r.Read()) {
Guid paymentId=r.GetGuid(0);
paymentIds.Add(paymentId);
}
}
}
}
/*
* payments
*/
this.Message="Creating Payment Files.";
("Quickbooks 10 Export: " + this.Message).Debug(10);
foreach(Guid paymentId in paymentIds) {
try {
Commerce.Payment payment = new Commerce.Payment(paymentId);
/* create the export string and validate it before adding it to the exportFileAudit table */
bool customer=payment.User.AccountType==0;
string fileName;
if(customer) {
fileName=bill_paymentFileName;
} else {
fileName=customer_paymentFileName;
}
/* teamplates:
*
!TRNS TRNSID TRNSTYPE DATE ACCNT NAME AMOUNT DOCNUM
!SPL SPLID TRNSTYPE DATE ACCNT NAME AMOUNT DOCNUM
!ENDTRNS
*
*
* CUSTOMER
TRNS PAYMENT 7/16/98 Undeposited Funds Ecker Designs:Office Repairs 500 321
SPL PAYMENT 7/16/98 Accounts Receivable Ecker Designs:Office Repairs -500 321
ENDTRNS
* BILL
*
!TRNS TRNSID TRNSTYPE DATE ACCNT NAME AMOUNT DOCNUM MEMO CLEAR TOPRINT
!SPL SPLID TRNSTYPE DATE ACCNT NAME AMOUNT DOCNUM MEMO CLEAR QNTY
!ENDTRNS
*
*
TRNS BILLPMT 7/16/98 Checking Bayshore CalOil Service -35 Test Memo N Y
SPL BILLPMT 7/16/98 Accounts Payable Bayshore CalOil Service 35 N
ENDTRNS
*
*/
if(customer) {
/* payments from customers */
string strPaymentDate = payment.GetAddress().DateCreated.ToString("MM/dd/yyyy");
customerPaymentBuffer.Append("TRNS PAYMENT "+strPaymentDate+
" "+payment.User.UserId+" "+payment.User.Handle+" "+payment.Amount.ToString()+" "+payment.Id.ToBase64Hash()+
Environment.NewLine);
foreach(Commerce.PaymentReference reference in payment.PaymentRefrences){
customerPaymentBuffer.Append("SPL PAYMENT "+strPaymentDate+
" Accounts Receivable Order Number:"+reference.Order.OrderNumber+" "+
(reference.Amount*-1).ToString()+" "+reference.Order.OrderNumber+
Environment.NewLine);
}
customerPaymentBuffer.Append("ENDTRNS"+Environment.NewLine);
} else {
/* payments to vendors */
string strPaymentDate = payment.GetAddress().DateCreated.ToString("MM/dd/yyyy");
billPaymentBuffer.Append("TRNS BILLPMT "+strPaymentDate+
" Checking "+payment.User.Handle+" "+(payment.Amount*-1).ToString()+" "+payment.Notes+" N Y"+
Environment.NewLine);
foreach(Commerce.PaymentReference reference in payment.PaymentRefrences){
billPaymentBuffer.Append("SPL BILLPMT "+strPaymentDate+
" Accounts Payable Order Number:"+reference.Order.OrderNumber+" "+reference.Amount.ToString()+" N "+
Environment.NewLine);
}
billPaymentBuffer.Append("ENDTRNS"+Environment.NewLine);
}
using(SqlCommand cmd=new SqlCommand(@"/* insert exportFileAudit for Quickbooks IFF export plugin (payments) */
insert into exportFileAudit (exportFileAuditId, exportFileId, exportFileName, controlId, exportDate, controlIdType)
values (newId(), @exportFileId, @fileName, @paymentId, @exportDate, @controlIdType)",cn,trans)) {
cmd.Parameters.Add("@paymentId",SqlDbType.UniqueIdentifier).Value=new Guid(paymentId.ToString());
cmd.Parameters.Add("@controlIdType",SqlDbType.UniqueIdentifier).Value=new Guid(paymentControlId);
cmd.Parameters.Add("@exportFileId",SqlDbType.UniqueIdentifier).Value=new Guid(controlId);
cmd.Parameters.Add("@fileName",SqlDbType.VarChar).Value=fileName;
cmd.Parameters.Add("@exportDate",SqlDbType.DateTime).Value=exportDate;
cmd.ExecuteNonQuery();
}
} catch(Exception ex) {
/* somthing didn't work correctly*/
ex.Message.Debug(0);
}
}
/*
* orders
*/
this.Message="Creating Order Files.";
("Quickbooks 10 Export: " + this.Message).Debug(10);
foreach(int orderId in orderIds) {
try {
/* get the order */
Commerce.Order order = Commerce.Order.GetOrderByOrderId(orderId,cn,trans);
/* create the export string and validate it before adding it to the exportFileAudit table */
bool customer = order.User.AccountType==0;
string fileName;
if(customer) {
fileName=invoiceFileName;
} else {
fileName=billFileName;
}
if(customer) {
/*
* INVOICE
*
!TRNS TRNSID TRNSTYPE DATE ACCNT NAME CLASS AMOUNT DOCNUM MEMO CLEAR TOPRINT NAMEISTAXABLE DUEDATE TERMS PAYMETH SHIPVIA SHIPDATE REP FOB PONUM INVMEMO ADDR1 ADDR2 ADDR3 ADDR4 ADDR5 SADDR1 SADDR2 SADDR3 SADDR4 SADDR5 TOSEND ISAJE OTHER1
!SPL SPLID TRNSTYPE DATE ACCNT NAME CLASS AMOUNT DOCNUM MEMO CLEAR QNTY PRICE INVITEM PAYMETH TAXABLE EXTRA VATCODE VATRATE VATAMOUNT VALADJ SERVICEDATE TAXCODE TAXRATE TAXAMOUNT TAXITEM OTHER2 OTHER3 REIMBEXP
!ENDTRNS
!ACCNT NAME REFNUM ACCNTTYPE ACCNUM CURRENCY
!INVITEM NAME INVITEMTYPE DESC PURCHASEDESC ACCNT ASSETACCNT COGSACCNT PRICE COST TAXABLE PAYMETH TAXVEND TAXDIST PREFVEND REORDERPOINT EXTRA CUSTFLD1 CUSTFLD2 CUSTFLD3 CUSTFLD4 CUSTFLD5 DEP_TYPE ISPASSEDTHRU HIDDEN
!CUST NAME BADDR1 BADDR2 BADDR3 BADDR4 BADDR5 COMPANYNAME FIRSTNAME MIDINIT LASTNAME CONT1 EMAIL PHONE1 FAXNUM SALUTATION FIRSTNAME MIDINIT LASTNAME CONT2 PHONE2 CTYPE SADDR1 SADDR2 SADDR3 SADDR4 SADDR5
!VEND NAME ADDR1 ADDR2 ADDR3 ADDR4 ADDR5 COMPANYNAME CONT1 EMAIL PHONE1 FAXNUM PRINTAS SALUTATION FIRSTNAME MIDINIT LASTNAME CONT2 PHONE2 VTYPE
!OTHERNAME NAME BADDR1 BADDR2 BADDR3 BADDR4 BADDR5 COMPANYNAME CONT1 EMAIL PHONE1 FAXNUM SALUTATION FIRSTNAME MIDINIT LASTNAME CONT2 PHONE2
!EMP NAME ADDR1 ADDR2 ADDR3 ADDR4 ADDR5 FIRSTNAME MIDINIT LASTNAME CITY STATE ZIP
*
*
TRNS INVOICE 4/5/2011 Accounts Receivable Clay Design 59.66 48ZC N N 4/5/2011 Debbie Gizicki 25659 Kinyon Taylor, MI 48044 Carrah Wilczynski 21805 E. Sunset Dr. Macomb, MI 48044 Y
SPL INVOICE 4/5/2011 51100 -12.76 UPS Ground Tracking: Shipped On: -1 12.76 Shipping N 0.00 Non 0.00 0.00
SPL INVOICE 4/5/2011 25501 0 Tax -1 0 Tax N 0.00 Non 0.00 0.00
SPL INVOICE 4/5/2011 47900 -27.95 2 Quart Bowl -1 27.95 454.N N 0.00 Non 0.00 0.00
SPL INVOICE 4/5/2011 47900 -18.95 Ice Cream Bowl -1 18.95 395.STIC N 0.00 Non 0.00 0.00
ENDTRNS
*/
string strExpAcct;
if(order.User.PurchaseAccount.Length>0){
strExpAcct = order.User.PurchaseAccount;
}else{
strExpAcct= defaultExportToAccount;
}
string strShippingDetails = "";
/* if there was at least one shipment, show that shipment */
if(order.Shipments.Count>0){
StringBuilder tl = new StringBuilder();
foreach(Commerce.Shipment shipment in order.Shipments){
tl.Append(shipment.Tracking);
}
strShippingDetails=string.Format("{0} Tracking:{1} Shipped On:{2}",order.ShipToAddress.Rate.Name,
string.Join(tl.ToString(),","),order.Shipments[0].DateShipped);
}
/* invoice */
string strOrderDate = order.OrderDate.ToString("MM/dd/yyyy");
string strShippingTotal = (order.ShippingTotal).ToString();
string strShippingTotalNeg = (order.ShippingTotal * -1).ToString();
string strTaxTotal = (order.TaxTotal).ToString();
string strTaxTotalNeg = (order.TaxTotal*-1).ToString();
string acctName = order.User.Handle;
if(acctName.Length==0){
/* if the acct name is blank use the Id # */
acctName = order.User.UserId.ToString();
}
string transType = "CASH SALE";
string dpAcct = "Undeposited Funds";
if(order.Term.Accrued){
transType = "INVOICE";
dpAcct = "Accounts Receivable";
}
/* header */
invoiceFileBuffer.Append("TRNS "+transType+" "+strOrderDate+
" "+dpAcct+" " +
acctName + " " +
order.GrandTotal.ToString() + " " +
order.OrderNumber+" "+order.OrderNumber+" N N "+
strOrderDate + " " +
order.PurchaseOrder + " " +
order.BillToAddress.FirstName + " " +
order.BillToAddress.LastName + " " +
order.BillToAddress.Address1 + " " +
order.BillToAddress.Address2 + " " +
order.BillToAddress.City + ", " + order.BillToAddress.State + " " + order.BillToAddress.Zip + " " +
order.ShipToAddress.FirstName + " " + order.ShipToAddress.LastName + " " +
order.ShipToAddress.Address1 + " " + order.ShipToAddress.Address2 + " " +
order.ShipToAddress.City + ", " +
order.ShipToAddress.State + " " +
order.ShipToAddress.Zip + " Y " +Environment.NewLine);
/* line items */
foreach(Commerce.Line line in order.Lines){
invoiceFileBuffer.Append("SPL "+transType+" " + strOrderDate + " " + line.Item.RevenueAccount +
" " + ((line.Price*line.Qty)*-1).ToString() + " " + line.Item.ShortDescription +
" " + (line.Qty*-1).ToString() + " " + line.Price.ToString() + " " + line.ItemNumber +
" N 0.00 Non 0.00 0.00 "+Environment.NewLine);
}
/* items that go into the header in Rendition but go into a line item in quickbooks */
invoiceFileBuffer.Append("SPL "+transType+" "+strOrderDate+" "+shippingRevAcct+" "+
strShippingTotalNeg+" "+strShippingDetails+" -1 "+strShippingTotal+
" "+"Shipping"+" N 0.00 Non 0.00 0.00 "+
Environment.NewLine);
invoiceFileBuffer.Append("SPL "+transType+" "+strOrderDate+" "+taxRevAcct+" "+strTaxTotalNeg+" "+
"Tax"+" -1 "+strTaxTotal+" "+"Tax"+" N 0.00 Non 0.00 0.00 "+
Environment.NewLine);
invoiceFileBuffer.Append("SPL "+transType+" "+strOrderDate+" "+discRevAcct+
" "+order.Discount+" "+"Discount"+" -1 "+(order.Discount*-1)+
" "+"Discount"+" N 0.00 Non 0.00 0.00 "+
Environment.NewLine);
invoiceFileBuffer.Append("ENDTRNS"+Environment.NewLine);
} else {
/*
* PURCHASE ORDER
*
*
!TRNS TRNSID TRNSTYPE DATE ACCNT NAME CLASS AMOUNT DOCNUM MEMO CLEAR TOPRINT ADDR5 DUEDATE TERMS
!SPL SPLID TRNSTYPE DATE ACCNT NAME CLASS AMOUNT DOCNUM MEMO CLEAR QNTY REIMBEXP SERVICEDATE OTHER2
!ENDTRNS
*
*
TRNS BILL 7/16/98 Accounts Payable Bayshore Water -59.25 N N 8/15/98 Net 30
SPL BILL 7/16/98 Utilities:Water 59.25 N NOTHING 0/0/0
ENDTRNS
*
*/
string acctName = order.User.Handle;
if(acctName.Length==0){
/* if the acct name is blank use the Id # */
acctName = order.User.UserId.ToString();
}
string strOrderDate = order.OrderDate.ToString("MM/dd/yyyy");
string strDueDate = order.DeliverBy.ToString("MM/dd/yyyy");
billFileBuffer.Append("TRNS BILL "+strOrderDate+" Accounts Payable "+
order.UserId.ToString()+" "+(order.GrandTotal*-1)+" N N "+strDueDate+" "+order.Term.Name+
Environment.NewLine);
foreach(Commerce.Line line in order.Lines){
billFileBuffer.Append("SPL BILL "+strOrderDate+" "+line.Item.ItemNumber + ":" + line.Item.ShortDescription +
" "+(line.Price*line.Qty)+" N NOTHING 0/0/0 "+
Environment.NewLine);
}
billFileBuffer.Append("ENDTRNS"+Environment.NewLine);
}
using(SqlCommand cmd=new SqlCommand(@"/* insert exportFileAudit for Quickbooks IFF export plugin (orders) */
insert into exportFileAudit (exportFileAuditId, exportFileId, exportFileName, controlId, exportDate, controlIdType)
values (newId(), @exportFileId, @fileName, @orderId, @exportDate, @controlIdType)",cn,trans)) {
cmd.Parameters.Add("@orderId",System.Data.SqlDbType.Int).Value=orderId;
cmd.Parameters.Add("@controlIdType",SqlDbType.UniqueIdentifier).Value=new Guid(orderControlId);
cmd.Parameters.Add("@exportFileId",SqlDbType.UniqueIdentifier).Value=new Guid(controlId);
cmd.Parameters.Add("@fileName",SqlDbType.VarChar).Value=fileName;
cmd.Parameters.Add("@exportDate",SqlDbType.DateTime).Value=exportDate;
cmd.ExecuteNonQuery();
}
} catch(Exception ex) {
/* somthing didn't work correctly*/
Error = -1;
Message = string.Format("Error exporting to quickbooks:{0}",ex.Message);
Admin.AddFlag("0","order",orderId.ToString(),string.Format("Error exporting to Quickbooks -> <br>{0}",ex.Message));
ex.Message.Debug(0);
}
}
if(Error!=0) {
/* an error occured, roll back all transactions and prevent output of any files */
trans.Rollback(transactionName);
} else {
this.Message="Writing buffers to disk.";
("Quickbooks 10 Export: "+this.Message).Debug(10);
/* output files when there is data */
if(billFileBuffer.Length>0) {
File.WriteAllText(Path.GetFullPath(outputPath).ToString()+billFileName,
billFileHeader+billFileBuffer.ToString());
}
if(invoiceFileBuffer.Length>0) {
File.WriteAllText(Path.GetFullPath(outputPath).ToString()+invoiceFileName,
invoiceFileHeader+invoiceFileBuffer.ToString());
}
if(billPaymentBuffer.Length>0) {
File.WriteAllText(Path.GetFullPath(outputPath).ToString()+bill_paymentFileName,
billPaymentHeader+billPaymentBuffer.ToString());
}
if(customerPaymentBuffer.Length>0) {
File.WriteAllText(Path.GetFullPath(outputPath).ToString()+customer_paymentFileName,
customerPaymentHeader+customerPaymentBuffer.ToString());
}
this.Message = "Last export: " + DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss");
("Quickbooks 10 Export: "+this.Message).Debug(10);
try{
/* sometimes there's just not a trasaction, how can I check? more work thats how! */
if(paymentIds.Count>0||orderIds.Count>0){
trans.Commit();
}
}catch{}
cn.Close();
}
}
/// <summary>
/// List initalizer - after the request vars have been fetched via InitList.
/// </summary>
/// <param name="_options">The options.</param>
/// <param name="_category">The category.</param>
/// <param name="_recordsPerPage">The records per page.</param>
/// <param name="_orderBy">The order by.</param>
/// <param name="_filter">The filter.</param>
/// <param name="_listMode">The list mode.</param>
/// <param name="_page">The page.</param>
/// <param name="_price">The price.</param>
/// <param name="_search">The search.</param>
private void InitListWithValues(ListOptions _options, string _category, string _recordsPerPage, string _orderBy,
string _filter, string _listMode, string _page, string _price, string _search)
{
/* check if the caller
* has any new options */
if(_options != null) {
Options = _options;
PriceRanges = _options.PriceRanges;
} else {
PriceRanges = Options.PriceRanges;
}
/* get the context and session
* then update any listing
* vars the user may be sending */
Session session = Main.GetCurrentSession();
if(_recordsPerPage != null) {
Rendition.Merchant.UpdateRecordsPerPage(Convert.ToInt32(_recordsPerPage));
}
if(_orderBy != null) {
Rendition.Merchant.UpdateOrderBy(Convert.ToInt32(_orderBy));
}
if(_listMode != null) {
Rendition.Merchant.UpdateListMode(Convert.ToInt32(_listMode));
}
/* if a category list was selected
run the options.category_nameRegexReplace
on it to replace characters in the category
name to make their URLS more friendly. */
if(_category != null) {
if(Options.CategoryNameRegEx.Length > 0) {
CategoryName = Regex.Replace(_category,
Options.CategoryNameRegEx, Options.CategoryNameRegExReplace);
}
ListType = Options.RequestCategory;
} else {
CategoryName = "";
};
if(_search != null) {
Search = _search;
ListType = Options.RequestSearch;
} else {
Search = "";
};
if(CategoryName == "" && Search == "") {
return;
}
if(_page != null) {
Options.RequestPage = _page;
} else {
Options.RequestPage = "1";
};
ItemList = new List<Commerce.Item>();
if(_search == null) {
/* list a category */
ListTypeValue = CategoryName.UrlEncode();
Category = Main.Site.Categories.GetCategoryByName(CategoryName);
if(Category == null) {
/* category does not exist - redirect if there's a page to redirect to */
if(HttpContext.Current != null && Options.NoItemsFoundRedirect.Length > 0) {
HttpContext.Current.Response.Redirect(Options.NoItemsFoundRedirect);
}
NullReferenceException ex = new NullReferenceException("Category is not found.");
throw ex;
} else {
/* remove items that are set
* hidden from listings */
ItemList.AddRange(Category.Items.FindAll(delegate(Rendition.Commerce.Item item) {
return !item.BOMOnly;
}));
}
} else {
/* list a search */
char[] splitBy = { ' ' };
string[] searchSplit = Search.Split(splitBy);
ListTypeValue = Search.UrlEncode();
Category = new Commerce.Category(ListTypeValue, Guid.Empty, true);
/* generate a menu
* first try a keyword */
foreach(string sh in searchSplit) {
_menu = Main.Site.Menus.GetMenuByName("search:" + sh);
if(_menu != null) {
break;
}
}
/* if not use the 'search' menu */
if(_menu == null) {
_menu = Main.Site.Menus.GetMenuByName("Search");
}
/* finally use the 'default' menu */
if(_menu == null) {
_menu = Main.Site.Menus.GetMenuByName("Default");
}
foreach(Commerce.Item it in Main.Site.Items.List) {
foreach(string sh in searchSplit) {
/* prevents nulls from mucking everything up */
if(sh == null) { continue; }
if(it == null) { continue; }
string sa = sh.Trim().ToLower();
if(sa.Length > 0) {
if(
/* search critera */
(
it.ShortDescription.ToLower().Contains(sa) ||
it.Description.ToLower().Contains(sa) ||
it.Html.ToLower().Contains(sa) ||
it.ItemNumber.ToLower().Contains(sa) ||
it.Keywords.ToLower().Contains(sa) ||
it.ProductCopy.ToLower().Contains(sa) ||
it.HomeCategory.Name.ToLower().Contains(sa) ||
it.AltCategory.Name.ToLower().Contains(sa)
) && it.BOMOnly == false /* don't show hidden items */
) {
if(!ItemList.Contains(it)) {
ItemList.Add(it);
}
}
}
}
}
if(ItemList.Count == 0) {
string q = Options.NoItemsFoundRedirect.Contains("?") ? "&" : "?";
HttpContext.Current.Response.Redirect(Options.NoItemsFoundRedirect + q + "ref=" +
_search.UrlEncode());
}
}
/* get an inital list of price filters */
List<Dictionary<string, object>> priceFilters = GetPriceFilters(ItemList, PriceRanges);
Filter = "";
if(_filter != null) {
Filter = _filter;
}
/* apply property and price filters */
if(Filter.Length > 0) {
char[] delimiters = new char[] { '_' };
string[] filters = Filter.Split(delimiters, StringSplitOptions.RemoveEmptyEntries);
for(var x = 0; filters.Length > x; x++) {
char[] delimiters2 = new char[] { '|' };
string[] hashrow = filters[x].Split(delimiters2, StringSplitOptions.RemoveEmptyEntries);
/* apply price filters */
if(hashrow[0] == Options.RequestPrice) {
Dictionary<string, object> sfilter = priceFilters.Find(delegate(Dictionary<string, object> flt) {
return flt["id"].ToString() == hashrow[1];
});
if(sfilter != null) {
List<Commerce.Item> lst = (List<Commerce.Item>)sfilter["list"];
if(lst != null) {
if(lst.Count > 0) {
ItemList = ItemList.FindAll(delegate(Rendition.Commerce.Item item1) {
return lst.Find(delegate(Rendition.Commerce.Item item2) {
return item1 == item2;
}) != null;
});
}
}
}
} else {
/* apply property filters */
ItemList = ItemList.FindAll(delegate(Rendition.Commerce.Item item) {
return item.Properties.FindAll(delegate(Rendition.Commerce.Property prop) {
return (prop.Text == hashrow[1] && prop.Name == hashrow[0]);
}).Count > 0;
});
}
}
}
/* now check if there are items
* in the list and if so do
* the listing or fail */
if(ItemList.Count > 0) {
/* calculate how many
* pages there needs
* to be etc.. */
To = ItemList.Count;
From = 0;
Regex number = new Regex("[0-9]+");
if(!number.IsMatch(Options.RequestPage)) {
Page = 1;
} else {
try {
Page = Convert.ToInt32(Options.RequestPage);
} catch {
Page = 1;
}
};
int rpp = session.RecordsPerPage > 0 ? session.RecordsPerPage : Main.Site.Defaults.RecordsPerPage;
int addMod = ItemList.Count % rpp > 0 ? 1 : 0;
TotalPages = (ItemList.Count / rpp) + addMod;
if(Page > TotalPages) {
Page = TotalPages;
}
/* Users will pass in a 1 based
* set, so it is nessessary to
* convert it to a zero based set */
PrevPage = Page - 1;
NextPage = Page + 1;
Page--;
if(Page < 0) {
Page = 0;
}
From = rpp * Page;
To = (rpp * Page) + rpp;
if(To > ItemList.Count) {
To = ItemList.Count;
}
if(rpp < 1) {
session.RecordsPerPage = Main.Site.Defaults.RecordsPerPage;
if(session.RecordsPerPage < 1) {
session.RecordsPerPage = 100;
}
};
if(NextPage > TotalPages) {
NextPage = TotalPages;
};
if(PrevPage < 1) {
PrevPage = 1;
};
/* get the Properties for this selection of items */
Properties = Rendition.Main.Site.Properties.List.FindAll(delegate(Rendition.Commerce.Property p) {
return ItemList.Exists(delegate(Rendition.Commerce.Item itm) {
return p.ItemNumber == itm.ItemNumber;
});
});
/* now that the filters have been
* applied recreate the price list
* but only for pepole requesting it */
if(_filter != null) {
priceFilters = GetPriceFilters(ItemList, PriceRanges);
}
if(session.ListOrder == 0) {
OrderByName = Options.OrderMessageFeaturedItem;
/* get the order int respective
* to the category id (ix.id)
* but on searches order by
* item.searchPriority
* as there is no category order.
*/
if(Category.Id != Guid.Empty) {
ItemList.Sort(delegate(Rendition.Commerce.Item item1, Rendition.Commerce.Item item2) {
return item1.CategoryOrder[Category.Id].CompareTo(item2.CategoryOrder[Category.Id]);
});
} else {
ItemList.Sort(delegate(Rendition.Commerce.Item item1, Rendition.Commerce.Item item2) {
return item1.SearchPriority.CompareTo(item2.SearchPriority);
});
}
} else if(session.ListOrder == 1) {
OrderByName = Options.OrderMessagePriceLowToHigh;
ItemList.Sort(delegate(Rendition.Commerce.Item item1, Rendition.Commerce.Item item2) {
return item1.Price.CompareTo(item2.Price);
});
} else if(session.ListOrder == 2) {
OrderByName = Options.OrderMessagePriceHighToLow;
ItemList.Sort(delegate(Rendition.Commerce.Item item1, Rendition.Commerce.Item item2) {
return item2.Price.CompareTo(item1.Price);
});
} else if(session.ListOrder == 3) {
OrderByName = Options.OrderMessageShortDescAtoZ;
ItemList.Sort(delegate(Rendition.Commerce.Item item1, Rendition.Commerce.Item item2) {
return item1.ShortDescription.CompareTo(item2.ShortDescription);
});
} else if(session.ListOrder == 4) {
OrderByName = Options.OrderMessageShortDescZtoA;
ItemList.Sort(delegate(Rendition.Commerce.Item item1, Rendition.Commerce.Item item2) {
return item2.ShortDescription.CompareTo(item1.ShortDescription);
});
}
/* build price filter links table */
pfltBuilder.Append("<table class=\"priceFiltertable\">");
/* start counting how many times we loop */
int x = 0;
int highest = priceFilters.Count - 1;
foreach(Dictionary<string, object> priceFilter in priceFilters) {
if(((int)priceFilter["count"]) > 0) {
string low = ((decimal)priceFilter["low"]).ToString("c");
string high = ((decimal)priceFilter["high"]).ToString("c");
HasPriceFilters = true;
/* there are items that are this price, make a link to the items */
pfltBuilder.Append("<tr><td>");
string pFilterLink = "";
string _pflt = Options.PriceFilterFormat;
if(x == 0) {
_pflt = Options.PriceFilterLowestFormat;
} else if(x == highest) {
_pflt = Options.PriceFilterHighestFormat;
}
pFilterLink = "<a rel=\"nofollow\" href=\"/" + Options.ScriptName + "?" + ListType +
"=" + ListTypeValue + "&page=" + (Page + 1).ToString() +
"&filter=" + HttpUtility.UrlEncode(Filter.Replace(Options.RequestPrice + "|" + priceFilter["id"], "") +
"_" + Options.RequestPrice + "|" + priceFilter["id"]) + "\">" +
String.Format(_pflt, low, high, priceFilter["count"]) + "</a>";
PriceFilterLinks.Add(pFilterLink);
pfltBuilder.Append(pFilterLink);
pfltBuilder.Append("</td></tr>");
x++;
}
}
pfltBuilder.Append("</table>");
/* build remove filter links and table */
string currentValue = "";
char[] delimiters = new char[] { '_' };
string[] filters = Filter.Split(delimiters, StringSplitOptions.RemoveEmptyEntries);
if(filters.Length > 0) {
rfltBuilder.Append("<table class=\"removeFiltertable\">");
foreach(string flt in filters) {
HasRemoveFilters = true;
string fltName = flt.Split('|')[0];
string fltValue = flt.Split('|')[1];
if(currentValue != fltValue && fltName != Options.RequestPrice) {
rfltBuilder.Append("<tr><th>");
rfltBuilder.Append(String.Format(Options.PropertyFilterRemoveFormat, fltName));
rfltBuilder.Append("</th></tr>");
}
rfltBuilder.Append("<tr><td>");
string rFiltLink = "<a rel=\"nofollow\" href=\"/" + Options.ScriptName + "?" + ListType +
"=" + ListTypeValue + "&page=" + (Page + 1).ToString() +
"&filter=" + HttpUtility.UrlEncode(Filter.Replace(fltName + "|" + fltValue, "")) +
"\" title=\"" + String.Format(Options.PropertyFilterRemoveTitle, Options.RequestPrice) + "\">" +
String.Format(Options.PropertyFilterValueRemoveFormat, fltValue.HtmlEncode()) + "</a>";
if(fltName == Options.RequestPrice) {
rFiltLink = "<a rel=\"nofollow\" href=\"/" + Options.ScriptName + "?" + ListType +
"=" + ListTypeValue + "&page=" + (Page + 1).ToString() +
"&filter=" + HttpUtility.UrlEncode(Filter.Replace(fltName + "|" + fltValue, "")) +
"\" title=\"" + String.Format(Options.PropertyFilterRemoveTitle, Options.RequestPrice.HtmlEncode()) + "\">" +
Options.PropertyFilterRemovePrice + "</a>";
}
currentValue = fltValue;
rfltBuilder.Append(rFiltLink);
RemoveFilterLinks.Add(rFiltLink);
rfltBuilder.Append("</td></tr>");
}
rfltBuilder.Append("</table>");
}
/* build filter links and table */
string currentProperty = "";
string currentText = "";
string count = "";
fltBuilder.Append("<table class=\"filtertable\">");
for(var y = 0; Properties.Count > y; y++) {
if((currentProperty != Properties[y].Name ||
currentText != Properties[y].Text)
&& Properties[y].Text.Trim().Length > 0
&& Properties[y].ShowInFilter == true
&& !Filter.Contains(Properties[y].Name)) {
HasFilters = true;
if(currentProperty != Properties[y].Name) {
fltBuilder.Append("<tr><th>");
if(currentProperty != Properties[y].Name) {
fltBuilder.Append(Properties[y].Name);
currentProperty = Properties[y].Name;
}
fltBuilder.Append("</th></tr>");
}
fltBuilder.Append("<tr><td>");
if(currentText != Properties[y].Text) {
count = Properties.FindAll(delegate(Rendition.Commerce.Property p) {
return p.Text.Trim() == Properties[y].Text.Trim() &&
Properties[y].Name.Trim() == p.Name.Trim();
}).Count.ToString();
string f_link = "<a rel=\"nofollow\" href=\"/" + Options.ScriptName + "?" + ListType + "=" +
ListTypeValue + "&page=" + (Page + 1).ToString() +
"&filter=" + HttpUtility.UrlEncode(Filter + "_" + Properties[y].Name.Trim() + '|' + Properties[y].Text.Trim()) +
"\" title=\"" + String.Format(Options.PropertyFilterFormatTitle.HtmlEncode(), Properties[y].Text.Trim()) + "\">" +
String.Format(Options.PropertyFilterFormat, Properties[y].Text.Trim().HtmlEncode(), count) + "</a>";
fltBuilder.Append(f_link);
FilterLinks.Add(f_link);
currentText = Properties[y].Text;
}
fltBuilder.Append("</td></tr>");
}
}
fltBuilder.Append("</table>");
}
}
/// <summary>
/// Gets the orders by order ids.
/// </summary>
/// <param name="ids">The ids.</param>
/// <param name="fcn">SQL connection.</param>
/// <param name="ftrns">SQL transaction.</param>
/// <returns>The matching orders.</returns>
public static List<Order> GetOrdersByOrderIds(int[] ids, SqlConnection fcn, SqlTransaction ftrns)
{
List<Order> orders = new List<Order>();
if(ids.Length == 0) { return orders; };
List<SqlDataRecord> rowData = new List<SqlDataRecord>();
SqlMetaData[] hashTable = {
new SqlMetaData("keyName",SqlDbType.VarChar,100),
new SqlMetaData("keyValue",SqlDbType.Variant),
new SqlMetaData("primary_key",SqlDbType.Bit),
new SqlMetaData("dataType",SqlDbType.VarChar,50),
new SqlMetaData("dataLength",SqlDbType.Int),
new SqlMetaData("varCharMaxValue",SqlDbType.VarChar,-1)
};
StringBuilder s = new StringBuilder();
foreach(int id in ids) {
SqlDataRecord rec = new SqlDataRecord(hashTable);
rec.SetValue(0, "orderId");
rec.SetValue(1, id);
rec.SetBoolean(2, false);
rec.SetString(3, "int");
rec.SetValue(4, 8);
rowData.Add(rec);
}
SqlConnection cn;
if(fcn != null) {
cn = fcn;
} else {
cn = Site.SqlConnection;
}
using(SqlCommand cmd = cn.CreateCommand()) {
if(fcn != null) {
cmd.Transaction = ftrns;
}
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "dbo.getOrders";
cmd.Parameters.Add("@orderIds", SqlDbType.Structured);
cmd.Parameters["@orderIds"].Direction = ParameterDirection.Input;
cmd.Parameters["@orderIds"].Value = rowData;
using(SqlDataReader u = cmd.ExecuteReader()) {
int orderId = -1;
DateTime orderDate = DateTime.MinValue;
decimal grandTotal = 0;
decimal taxTotal = 0;
decimal subTotal = 0;
decimal shippingTotal = 0;
decimal service1 = 0;
decimal service2 = 0;
string manifest = "";
string purchaseOrder = "";
decimal discount = 0;
string comment = "";
decimal paid = 0;
Guid billToAddressId = Guid.Empty;
bool closed = false;
bool canceled = false;
Guid paymentMethodId = Guid.Empty;
int termId = -1;
int userId = -1;
string orderNumber = "";
bool creditMemo = false;
string scanned_order_image = "";
DateTime readyForExport = DateTime.MinValue;
DateTime recalculatedOn = DateTime.MinValue;
Guid sessionId = Guid.Empty;
int soldBy = -1;
int requisitionedBy = -1;
int approvedBy = -1;
DateTime deliverBy = DateTime.MinValue;
string vendor_accountNo = "";
string FOB = "";
int parentOrderId = -1;
int order_status = -1;
List<Line> lines = new List<Line>();
while(u.Read()) {
/* #44 is orderId */
if(u.GetInt32(44) != orderId && orderId != -1) {
/*the orderId has changed, add the previous order */
orders.Add(new Order(
orderId, orderDate, grandTotal, taxTotal, subTotal, shippingTotal, service1,
service2, manifest, purchaseOrder, discount, comment, paid, billToAddressId, closed,
canceled, paymentMethodId, termId, userId, orderNumber, creditMemo, scanned_order_image,
readyForExport, recalculatedOn, sessionId, soldBy, requisitionedBy, approvedBy, deliverBy,
vendor_accountNo, FOB, parentOrderId, lines, order_status, cn, ftrns
));
lines = new List<Line>();/* create a new list of lines for the next order */
}
orderId = u.GetInt32(44);
orderDate = u.GetDateTime(132);
grandTotal = u.GetDecimal(133);
taxTotal = u.GetDecimal(134);
subTotal = u.GetDecimal(135);
shippingTotal = u.GetDecimal(136);
service1 = u.GetDecimal(137);
service2 = u.GetDecimal(138);
manifest = u.GetString(139);
purchaseOrder = u.GetString(140);
discount = u.GetDecimal(164);
comment = u.GetString(141);
paid = u.GetDecimal(190);
billToAddressId = u.GetGuid(103);
closed = u.GetBoolean(191);
canceled = u.GetBoolean(191);
termId = u.GetInt32(84);
userId = u.GetInt32(55);
orderNumber = u.GetString(46);
creditMemo = u.GetBoolean(193);
scanned_order_image = u.GetString(53);
readyForExport = u.GetDateTime(7);
recalculatedOn = u.GetDateTime(194);
sessionId = u.GetGuid(38);
soldBy = u.GetInt32(195);
requisitionedBy = u.GetInt32(196);
approvedBy = u.GetInt32(197);
deliverBy = u.GetDateTime(198);
vendor_accountNo = u.GetString(199);
FOB = u.GetString(200);
parentOrderId = u.GetInt32(201);
order_status = u.GetInt32(5);
/* always add every line */
lines.Add(new Line(
u.GetGuid(37)/*cartId*/,
u.GetGuid(38)/*sessionId*/,
u.GetInt32(39)/*qty*/,
u.GetString(0)/*itemNumber*/,
u.GetDecimal(41)/*price*/,
u.GetDateTime(42)/*add time*/,
u.GetInt32(44)/*orderId*/,
u.GetInt32(45)/*serialId*/,
u.GetString(46)/*orderNumber*/,
u.GetString(47)/*serialNumber*/,
u.GetGuid(48)/*addressId*/,
u.GetInt32(49)/*shipmentId*/,
u.GetString(50)/*shipmentNumber*/,
u.GetInt32(51)/*lineNumber*/,
u.GetString(52)/*epsmmcsoutput*/,
u.GetString(54)/*epsmmcsfilename*/,
u.GetDecimal(170)/*valueCostTotal*/,
u.GetDecimal(171)/*noTaxValueCostTotal*/,
u.GetDateTime(203)/*fullfillmentDate*/,
u.GetDateTime(204)/*estimatedFulfillmentDate*/,
u.GetGuid(202)/*parentCartId*/,
u.GetInt32(12)/*backorderedqty*/,
u.GetInt32(13)/*canceledQty*/,
u.GetString(174)/*customLineNumber*/,
u.GetInt32(205)/*kitAllocationId*/,
u.GetInt32(206)/*kitQty*/,
u.GetBoolean(207)/*showAsSeperateLineOnInvoice*/,
u.GetGuid(208)/*vendorItemKitAssignmentId*/,
u.GetGuid(209)/*kitAllocationCartId*/,
u.GetInt32(1)/*line_status*/
));
}
/* add the last order */
orders.Add(new Order(
orderId, orderDate, grandTotal, taxTotal, subTotal, shippingTotal, service1,
service2, manifest, purchaseOrder, discount, comment, paid, billToAddressId, closed,
canceled, paymentMethodId, termId, userId, orderNumber, creditMemo, scanned_order_image,
readyForExport, recalculatedOn, sessionId, soldBy, requisitionedBy, approvedBy, deliverBy,
vendor_accountNo, FOB, parentOrderId, lines, order_status, cn, ftrns
));
/* now all the shipments that belong to the orders */
u.NextResult();
while(u.Read()) {
int shipmentOrderId = u.GetInt32(0);
/* find the order that goes to this shipment */
Commerce.Order sOrd = orders.Find(delegate(Commerce.Order ord) {
return ord.OrderId == shipmentOrderId;
});
if(sOrd == null) { continue; }
/*
cart.orderId,addressUpdateId,cart.shipmentNumber,tracking,
dateShipped,actualWeight,actualService,actualCost,
actualBilledWeight,packageLength,packageWidth,
packageHeight,thirdPartyAccount,voidStatus,
emailSent,addDate
*/
Shipment shp = new Shipment(sOrd.ShipToAddress, sOrd,
u.GetGuid(1), u.GetString(2), u.GetString(3),
u.GetString(4), u.GetString(5),
u.GetString(6), u.GetString(7),
u.GetString(8), u.GetString(9),
u.GetString(10), u.GetString(11),
u.GetString(12), u.GetString(13),
u.GetDateTime(14), u.GetDateTime(15));
sOrd.Shipments.Add(shp);
}
/* next batch... line detail
cartDetailId, cartDetail.cartId,
inputName, value, cartDetail.sessionId */
u.NextResult();
while(u.Read()) {
LineDetail lineDetail = new LineDetail(
u.GetGuid(0),
u.GetGuid(1),
u.GetGuid(4),
u.GetString(2),
u.GetString(3)
);
/* find the line to attach this line detail to */
Line line = lines.Find(delegate(Commerce.Line l) {
return l.CartId == lineDetail.CartId;
});
if(line != null) {
line.LineDetail.Add(lineDetail);
}
}
/* next batch... form source
* order_line_forms.cartId, sourceCode, formName */
u.NextResult();
while(u.Read()) {
Guid id = u.GetGuid(0);
Line line = lines.Find(delegate(Commerce.Line l) {
return l.CartId == id;
});
if(line != null) {
if(u.IsDBNull(1)) {
line.SourceCode = "";
line.FormName = "NO FORM";
} else {
line.SourceCode = u.GetString(1);
line.FormName = u.GetString(2);
}
}
}
}
}
return orders;
}
/// <summary>
/// Uses System.Reflection to turn request strings into methods in the merchant, Admin or setup classes.
/// </summary>
/// <param name="decodedRequestString">The decoded request string.</param>
/// <param name="adminMode">if set to <c>true</c> [Admin mode].</param>
/// <returns></returns>
public static Dictionary<string, object> JsonToMethod(string decodedRequestString, bool adminMode)
{
//("FUNCTION /w reflection JSONToMethod").debug();
Dictionary<string, object> o = new Dictionary<string, object>();
if(decodedRequestString.Length == 0) {
o.Add("error", -8);
o.Add("description", "missing arguments argument");
return o;
} else {
MethodInfo methodInfo;
List<Object> args = new List<Object>();
string proc = "";
int x = 0;
JArray req = null;
try {
req = JsonConvert.DeserializeObject<JArray>(decodedRequestString);
} catch(Exception e) {
o.Add("error", -7);
o.Add("description", "JSON parse error. " + e.Message);
return o;
}
List<object> argumentCollection = new List<object>();
proc = (string)req[0];
if(req[1].GetType() == typeof(JArray)) {
JArray a = (JArray)req[1];
foreach(object q in a as JArray) {
argumentCollection.Add(q.JTokenToGeneric());
}
} else {
o.Add("error", -6);
o.Add("description", "missing second argument (method arguemnts)");
return o;
}
ParameterInfo[] pramInfo = null;
if(proc.Length > 0) {
methodInfo = null;
if(adminMode) {
methodInfo = typeof(Admin).GetMethod(proc);
/* execute all plugins with an Admin class */
bool mexec = false;
foreach(Plugin plugin in Main.Plugins) {
Type[] cinfos = plugin.GetType().GetNestedTypes();
foreach(Type cinfo in cinfos) {
if(cinfo.BaseType == typeof(Admin)) {
MethodInfo innerMethodInfo = cinfo.GetMethod(proc);
if(innerMethodInfo != null) {
methodInfo = innerMethodInfo;
mexec = true;
break;
}
}
}
if(mexec) {
break;
}
}
} else {
methodInfo = typeof(Merchant).GetMethod(proc);
/* execute all plugins with an Merchant class */
bool mexec = false;
foreach(Plugin plugin in Main.Plugins) {
Type[] cinfos = plugin.GetType().GetNestedTypes();
foreach(Type cinfo in cinfos) {
if(cinfo.BaseType == typeof(Merchant)) {
MethodInfo innerMethodInfo = cinfo.GetMethod(proc);
if(innerMethodInfo != null) {
methodInfo = innerMethodInfo;
mexec = true;
break;
}
}
}
if(mexec) {
break;
}
}
}
if(methodInfo == null) {
o.Add("error", -5);
o.Add("description", "method not found");
return o;
} else {
pramInfo = methodInfo.GetParameters();
}
} else {
o.Add("error", -4);
o.Add("description", "method argument is missing");
return o;
}
if(argumentCollection != null) {
foreach(object argument in argumentCollection) {
if(argument != null) {
if(pramInfo != null) {
if(pramInfo.Length == x) {
o.Add("error", -3);
o.Add("description", "Too many arguments.");
return o;
}
if(pramInfo[x].ParameterType == argument.GetType()) {
args.Add(argument);
} else {
o.Add("error", -2);
o.Add("description", "Argument " + x + ":" + pramInfo[x].Name +
" expected type " + pramInfo[x].ParameterType.ToString() + ", " +
" but got type " + argument.GetType().ToString() + ".");
return o;
}
x++;
}
} else {
o.Add("error", -9);
o.Add("description", "Argument " + x + " was null.");
return o;
}
}
}
if(args.Count == x) {
try {
o.Add(methodInfo.Name, methodInfo.Invoke(null, args.ToArray()));
} catch(Exception e) {
e = Main.getInnermostException(e);
string msg = String.Format("Application Error {0} on Page:{1},Message:{2}, Stack Trace: {3}",
500,
"Responder=>GetMethod exception", e.Message, e.StackTrace);
o.Add("error", -500);
o.Add("description", "Internal server error: " + e.Source + ": " + e.Message);
}
} else {
o.Add("error", -1);
o.Add("description", "Expected " + args.Count + " arguments, but got " + x + " arguments");
}
return o;
}
}
/// <summary>
/// Gets the child orders by order id.
/// </summary>
/// <param name="orderId">The order id.</param>
/// <param name="cn">SQL connection.</param>
/// <param name="trans">The trans.</param>
/// <returns>
/// A list of orders matching the serial ids.
/// </returns>
public static List<Order> GetChildOrdersByOrderId(int orderId, SqlConnection cn, SqlTransaction trans)
{
using(SqlCommand cmd = new SqlCommand("select orderId from orders with (nolock) where parentOrderId = @orderId", cn, trans)) {
cmd.Parameters.Add("@orderId", SqlDbType.Int).Value = orderId;
using(SqlDataReader d = cmd.ExecuteReader()) {
List<int> orderIds = new List<int>();
while(d.Read()) {
orderIds.Add(d.GetInt32(0));
}
return GetOrdersByOrderIds(orderIds.ToArray(), cn, trans);
}
}
}
/// <summary>
/// Gets the google chart query.
/// Returns the format that google data table is expecting for direct interjection.
/// </summary>
/// <param name="chartType">Type of the chart.</param>
/// <param name="query">The query.</param>
/// <returns></returns>
public static Dictionary<string, object> GetGoogleChartQuery(string chartType, string query)
{
Dictionary<string, object> j = new Dictionary<string, object>();
Dictionary<string, object> dataObject = new Dictionary<string, object>();
List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
List<Dictionary<string, object>> columns = new List<Dictionary<string, object>>();
using(SqlConnection cn = Site.CreateConnection(true, true)) {
cn.Open();
try {
using(SqlCommand cmd = new SqlCommand(query, cn)) {
using(SqlDataReader r = cmd.ExecuteReader()) {
int colLength = r.FieldCount;
while(r.Read()) {
if(columns.Count==0){
object[] colNames = new object[colLength];
for(int x = 0; x < colLength; x++) {
string name = r.GetName(x);
Dictionary<string, object> col = new Dictionary<string, object>();
if(name == string.Empty) {
name = "Column" + (x+1);
}
col.Add("id",name);
col.Add("label",name);
col.Add("pattern","");
col.Add("type",getGoogleDataType(r.GetFieldType(x)));
colNames[x] = name;
columns.Add(col);
}
}
object[] values = new object[colLength];
r.GetValues(values);
rows.Add(populateGoogleRow(values, colLength));
}
}
}
} catch(Exception ex) {
j.Add("message", ex.Message);
j.Add("error", -1);
return j;
}
}
dataObject.Add("cols",columns);
dataObject.Add("rows",rows);
j.Add("message", "");
j.Add("error", 0);
j.Add("data", dataObject);
return j;
}
/// <summary>
/// Renders the template images. Updates an ProgressInfo matching progressInfoId parameter.
/// </summary>
/// <param name="templateId">The template id.</param>
/// <param name="progressInfoId">The progress info id.</param>
/// <returns></returns>
public static List<object> RenderTemplateImages( string templateId, string progressInfoId )
{
Guid id = new Guid( progressInfoId );
ProgressInfo u = new ProgressInfo( id );
if( !Main.ProgressInfos.ContainsKey( id ) ) {
Main.ProgressInfos.Add( id, u );
} else {
Main.ProgressInfos[ id ] = u;
}
u.CurrentItemName = "Starting";
u.TotalItemCount = 0;
u.Started = DateTime.Now;
( "FUNCTION /w SP renderTemplateImages" ).Debug( 10 );
List<object> l = new List<object>();
List<object> entries = new List<object>();
using(Impersonation imp = new Impersonation()) {
using(SqlConnection cn = Site.CreateConnection(true, true)) {
cn.Open();
using(SqlCommand cmd = new SqlCommand("getTemplateUsage @templateId", cn)) {
cmd.Parameters.Add("@templateId", SqlDbType.UniqueIdentifier).Value = new Guid(templateId.ToString());
using(SqlDataReader d = cmd.ExecuteReader()) {
if(d.HasRows) {
while(d.Read()) {
foreach(Commerce.Item i in Main.Site.Items.List) {
u.TotalItemCount++;
string[] ls = { d.GetString(1) };
List<object> f = new List<object>();
f.Add(i.ItemNumber);
f.Add(ls);
f.Add(d.GetGuid(0));
entries.Add(f);
}
}
}
}
}
}
foreach(List<object> entry in entries) {
l.Add(RefreshItemImages(((string)entry[0]), ((string[])entry[1]), ((Guid)(entry[2])), Guid.Empty));
u.CurrentItemCount++;
u.Updated = DateTime.Now;
u.CurrentItemName = ((string)entry[0]);
}
}
u.Complete = true;
return l;
}
/// <summary>
/// Creates a pie chart from a JSON request.
/// </summary>
/// <param name="query">Query batch.</param>
/// <param name="_options">GraphOptions.</param>
/// <param name="binaryOutput">if set to <c>true</c> the image will output in the response stream.</param>
/// <returns></returns>
public static System.Drawing.Bitmap PieChart( string query, Dictionary<string, object> _options, bool binaryOutput )
{
( "FUNCTION /w binaryStream pieChart" ).Debug( 10 );
/* query expects two columns
* 0 name
* 1 value
* 2 color1 (or null)
* 3 color2 (or null)
*/
JToken jtOpt = JToken.FromObject( _options );
JsonSerializer serializer = new JsonSerializer();
GraphOptions options = ( GraphOptions )serializer.Deserialize( new JTokenReader( jtOpt ), typeof( GraphOptions ) );
if( options.Width == 0 || options.Height == 0 ) {
/*bad image size defined */
return null;
}
System.Drawing.Bitmap image = null;
GraphPane myPane = null;
using(SqlConnection cn = Site.CreateConnection(true, true)) {
cn.Open();
try {
using(SqlCommand cmd = new SqlCommand(query, cn)) {
myPane = new GraphPane(new System.Drawing.Rectangle(0, 0, options.Width, options.Height), options.Title, "", "");
// Set the GraphPane title
myPane.Title.Text = options.Title;
myPane.Title.FontSpec.IsItalic = options.IsItalic;
myPane.Title.FontSpec.Size = options.TitleFontSize;
myPane.Title.FontSpec.Family = options.FontFamily;
System.Drawing.Color fill1 = System.Drawing.Color.FromName(options.Fill.StartColor);
System.Drawing.Color fill2 = System.Drawing.Color.FromName(options.Fill.EndColor);
// Fill the pane background with a color gradient
myPane.Fill = new Fill(fill1, fill2, options.Fill.Angle);
// No fill for the chart background
myPane.Chart.Fill.Type = FillType.None;
// Set the legend to an arbitrary location
myPane.Legend.IsVisible = options.ShowLegend;
myPane.Legend.Position = LegendPos.Float;
myPane.Legend.Location = new Location(0.95f, 0.15f, CoordType.PaneFraction, AlignH.Right, AlignV.Top);
myPane.Legend.FontSpec.Size = 10f;
myPane.Legend.IsHStack = false;
List<PieItem> segments = new List<PieItem>();
using(SqlDataReader r = cmd.ExecuteReader()) {
while(r.Read()) {
System.Drawing.Color color1 = System.Drawing.Color.FromName(r.GetString(2));
System.Drawing.Color color2 = System.Drawing.Color.FromName(r.GetString(3));
PieItem s = myPane.AddPieSlice(Convert.ToDouble(r.GetValue(1)), color1, color2, 45f, 0, r.GetString(0));
if(r.GetValue(1).GetType() == typeof(decimal)) {
s.Label.Text = r.GetString(0) + ' ' + r.GetDecimal(1).ToString(options.NodeLabelFormat);
} else {
s.Label.Text = s.Label.Text = r.GetString(0) + ' ' + Convert.ToString(r.GetValue(1));
}
segments.Add(s);
}
}
}
// Sum up the pie values
CurveList curves = myPane.CurveList;
double total = 0;
for(int x = 0; x < curves.Count; x++)
total += ((PieItem)curves[x]).Value;
// Calculate the Axis Scale Ranges
myPane.AxisChange();
image = myPane.GetImage(true);
} catch(Exception ex) {
if(image == null) {
image = new Bitmap(700, 700);
}
image = WriteImageError(image, ex.Message, "Arial", 8f);
}
if(binaryOutput) {
using(MemoryStream ms = new MemoryStream()) {
image.Save(ms, System.Drawing.Imaging.ImageFormat.Png);
if(HttpContext.Current != null) {
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ContentType = "image/png";
HttpContext.Current.Response.AddHeader("Expires", "0");/* RFC 2616 14.21 Content has already expired */
HttpContext.Current.Response.AddHeader("Cache-Control", "no-store");/* RFC 2616 14.9.2 Don't ever cache */
HttpContext.Current.Response.AddHeader("Pragma", "no-store");/* RFC 2616 14.32 Pragma - same as cache control */
ms.WriteTo(HttpContext.Current.Response.OutputStream);
}
}
image.Dispose();
if(HttpContext.Current != null) {
HttpContext.Current.Response.Flush();
HttpContext.Current.ApplicationInstance.CompleteRequest();
}
}
}
return image;
}
/// <summary>
/// Refreshes the user contacts.
/// </summary>
public void RefreshUserContacts()
{
_contacts = new List<Address>();
string sqlCommand = @"select
contactId,firstName,lastName,address1,address2,
city,state,zip,country,homePhone,workPhone,email,specialInstructions,
comments,sendshipmentupdates,emailads, rate,dateCreated,company
from contacts with (nolock) where userId = @userId";
using(SqlConnection cn = Site.CreateConnection(true, true)) {
cn.Open();
using(SqlCommand cmd = new SqlCommand(sqlCommand, cn)) {
cmd.Parameters.Add("@userId", SqlDbType.Int).Value = this.UserId;
using(SqlDataReader d = cmd.ExecuteReader()) {
while(d.Read()) {
Address addr = new Address(
d.GetGuid(0),
d.GetString(1),
d.GetString(2),
d.GetString(3),
d.GetString(4),
d.GetString(5),
d.GetString(6),
d.GetString(7),
d.GetString(8),
d.GetString(9),
d.GetString(10),
d.GetString(11),
d.GetString(12),
d.GetString(13),
d.GetBoolean(14),
d.GetBoolean(15),
Main.Site.Rates.List.Find(delegate(Commerce.Rate t) {
return t.Id == d.GetInt32(16);
}),
d.GetDateTime(17),
d.GetString(18));
_contacts.Add(addr);
}
}
}
}
}
private List<Input> GetFormInputs(string html)
{
List<Input> ip = new List<Input>();
HtmlDocument doc = new HtmlDocument();
if(html == null) {
return ip;
}
try {
doc.LoadHtml(html);
IEnumerable<HtmlNode> nodes = doc.DocumentNode.DescendantNodes();
foreach(HtmlNode node in nodes) {
string tagName = node.Name;
if(tagName == "select" || tagName == "input") {
Input i = new Input(node.GetAttributeValue("name", ""), "");
if(tagName == "select") {
List<InputOption> ips = new List<InputOption>();
foreach(HtmlNode opt in node.ChildNodes) {
InputOption o = new InputOption();
o.Value = opt.GetAttributeValue("value", "");
o.InnerText = opt.InnerText;
i.Options.Add(o);
}
} else {
string inputType = node.GetAttributeValue("type", "");
string maxlength = node.GetAttributeValue("maxlength", "");
i.Type = inputType;
i.MaxLength = maxlength;
}
/* add all the attributes */
foreach(HtmlAttribute atr in node.Attributes) {
i.Attributes.Add(atr.Name, atr.Value);
}
ip.Add(i);
}
}
} catch(Exception ex) {
String.Format("getFormInputs exception. Form:{0}, Item:{1} =>{2}", this.Name, this.Item.Number, ex.Message).Debug(3);
}
return ip;
}
