/// <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; }