public static IEnumerable<SqlDataRecord> TSqlDataRecord(List<Menu> ListaMenu)
{
List<SqlDataRecord> listaSqlDataRecord = new List<SqlDataRecord>();
foreach (Menu oMenu in ListaMenu)
{
SqlDataRecord oSqlDataRecord = new SqlDataRecord(
new SqlMetaData[]{ new SqlMetaData("nMenuId", SqlDbType.Int),
new SqlMetaData("nValor", SqlDbType.Bit)
});
oSqlDataRecord.SetInt32(0, oMenu.nMenuId);
oSqlDataRecord.SetBoolean(1, oMenu.bEstado);
listaSqlDataRecord.Add(oSqlDataRecord);
}
return listaSqlDataRecord;
}
public static IEnumerable<SqlDataRecord> TSqlDataRecord(List<Permiso> ListaPermiso)
{
List<SqlDataRecord> listaSqlDataRecord = new List<SqlDataRecord>();
foreach (Permiso oPermiso in ListaPermiso)
{
SqlDataRecord oSqlDataRecord = new SqlDataRecord(
new SqlMetaData[]{ new SqlMetaData("nModId", SqlDbType.Int),
new SqlMetaData("nPermId", SqlDbType.Int),
new SqlMetaData("nValor", SqlDbType.Bit)
});
oSqlDataRecord.SetInt32(0, oPermiso.nModId);
oSqlDataRecord.SetInt32(1, oPermiso.nPermId);
oSqlDataRecord.SetBoolean(2, oPermiso.bEstado);
listaSqlDataRecord.Add(oSqlDataRecord);
}
return listaSqlDataRecord;
}
private static SqlDataRecord CreateBooleanRecord(bool? value)
{
var record = new SqlDataRecord(new SqlMetaData("Value", SqlDbType.Bit));
if (value.HasValue)
record.SetBoolean(0, value.Value);
else
record.SetDBNull(0);
return record;
}
/// <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;
}
public void SetValue(ref SqlDataRecord sqlDataRecord, SqlDescriptionAttribute sqlDescription, object value,
int ordinal)
{
if (!sqlDescription.HasDbType)
{
throw new InvalidDataException("SqlDbType can not be null");
}
if (value == null)
{
sqlDataRecord.SetDBNull(ordinal);
return;
}
switch (sqlDescription.SqlDbType)
{
case SqlDbType.BigInt:
var ll = value as long?;
if (!ll.HasValue)
{
throw new Exception("Value is not BigInt");
}
sqlDataRecord.SetInt64(ordinal, ll.Value);
break;
case SqlDbType.Binary:
var bb = value as byte?;
if (!bb.HasValue)
{
throw new Exception("Value is not BigInt");
}
sqlDataRecord.SetSqlByte(ordinal, bb.Value);
break;
case SqlDbType.Bit:
var bit = value as bool?;
if (!bit.HasValue)
{
throw new Exception("Value is not Bit");
}
sqlDataRecord.SetBoolean(ordinal, bit.Value);
break;
case SqlDbType.NChar:
case SqlDbType.Char:
var chr = value as char?;
if (!chr.HasValue)
{
throw new Exception("Value is not Char");
}
sqlDataRecord.SetChar(ordinal, chr.Value);
break;
case SqlDbType.DateTime:
case SqlDbType.SmallDateTime:
case SqlDbType.Date:
case SqlDbType.DateTime2:
var dt = value as DateTime?;
if (!dt.HasValue)
{
throw new Exception("Value is not DateTime");
}
sqlDataRecord.SetDateTime(ordinal, dt.Value);
break;
case SqlDbType.Decimal:
case SqlDbType.Money:
case SqlDbType.SmallMoney:
var dc = value as decimal?;
if (!dc.HasValue)
{
throw new Exception("Value is not Decimal");
}
sqlDataRecord.SetDecimal(ordinal, dc.Value);
break;
case SqlDbType.Float:
var d = value as double?;
if (!d.HasValue)
{
throw new Exception("Value is not Double");
}
sqlDataRecord.SetDouble(ordinal, d.Value);
break;
case SqlDbType.Image:
case SqlDbType.VarBinary:
var bytes = value as byte[];
if (bytes == null)
{
throw new Exception("Value is not byte array");
}
sqlDataRecord.SetBytes(ordinal, 0, bytes, 0, bytes.Length);
break;
case SqlDbType.Int:
var integer = value as int?;
if (integer == null)
{
var ushortValue = (value as ushort?);
if (ushortValue == null)
{
throw new Exception("Value is not int or ushort");
}
integer = ushortValue.Value;
}
sqlDataRecord.SetInt32(ordinal, integer.Value);
break;
case SqlDbType.NText:
case SqlDbType.NVarChar:
case SqlDbType.VarChar:
case SqlDbType.Text:
case SqlDbType.Xml:
var str = value as string;
if (str == null)
{
var chars = value as char[];
if (chars == null)
{
throw new Exception("Value is not string or char array");
}
str = new string(chars);
}
sqlDataRecord.SetString(ordinal, str);
break;
case SqlDbType.Real:
var f = value as float?;
if (f == null)
{
throw new Exception("Value is not float");
}
sqlDataRecord.SetFloat(ordinal, f.Value);
break;
case SqlDbType.UniqueIdentifier:
var guid = value as Guid?;
if (guid == null)
{
throw new Exception("Value is not Guid");
}
sqlDataRecord.SetGuid(ordinal, guid.Value);
break;
case SqlDbType.SmallInt:
var sh = value as short?;
if (sh == null)
{
var uByte = value as sbyte?;
if (uByte == null)
{
throw new Exception("Value is not short or sbyte");
}
sh = uByte.Value;
}
sqlDataRecord.SetInt16(ordinal, sh.Value);
break;
case SqlDbType.TinyInt:
var b = value as byte?;
if (b == null)
{
throw new Exception("Value is not byte");
}
sqlDataRecord.SetByte(ordinal, b.Value);
break;
case SqlDbType.Time:
var timeSpan = value as TimeSpan?;
if (timeSpan == null)
{
throw new Exception("Value is not TimeSpan");
}
sqlDataRecord.SetTimeSpan(ordinal, timeSpan.Value);
break;
case SqlDbType.DateTimeOffset:
var dateTimeOffset = value as DateTimeOffset?;
if (dateTimeOffset == null)
{
throw new Exception("Value is not DateTimeOffset");
}
sqlDataRecord.SetDateTimeOffset(ordinal, dateTimeOffset.Value);
break;
case SqlDbType.Structured:
case SqlDbType.Udt:
case SqlDbType.Timestamp:
case SqlDbType.Variant:
throw new NotImplementedException();
default:
throw new ArgumentOutOfRangeException();
}
}
/// <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>
/// Posts payments to general ledger.
/// </summary>
/// <param name="ids">The payment ids.</param>
/// <param name="postingDate">The posting date.</param>
/// <param name="postingNotes">The posting notes.</param>
/// <param name="preview">if set to <c>true</c> [preview].</param>
/// <returns>{error:0,desc:"error description",preview:false,
/// generalLedgerEntries:{
/// drDate,
/// drDetails,
/// drReference,
/// drAmount,
/// crDate,
/// crDetails,
/// crReference,
/// crAmount
/// },
/// rawGL:{
/// generalLedgerId,
/// creditRecord,
/// debitRecord,
/// amount,
/// userId,
/// termId,
/// addDate,
/// reference,
/// orderId,
/// generalLedgerId
/// },
/// rawGLDetail:{
/// generalLedgerDetailId,
/// generalLedgerId,
/// refId,
/// refType
/// }
/// }.</returns>
public static Dictionary<string, object> PostPaymentsToGeneralLedger(List<object> ids, string postingDate, string postingNotes, bool preview)
{
Dictionary<string, object> j = new Dictionary<string, object>();
List<object> accountantReadable = new List<object>();
List<object> rawGL = new List<object>();
List<object> rawGLDetail = new List<object>();
using(SqlConnection cn = Site.CreateConnection(true, true)) {
cn.Open();
using(SqlTransaction trans = cn.BeginTransaction("paymentPosting")) {
List<Order> orders = new List<Order>();
int error = 0;
string desc = "";
decimal crTotal = 0;
decimal drTotal = 0;
DateTime _postingDate;
if(!DateTime.TryParse(postingDate, out _postingDate)) {
j.Add("preview", preview);
j.Add("error", -2);
j.Add("description", "Posting date is not in the correct format.");
}
if(ids.Count == 0) {
j.Add("preview", preview);
j.Add("error", -1);
j.Add("description", "No orders selected.");
};
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(object id in ids) {
SqlDataRecord rec = new SqlDataRecord(hashTable);
rec.SetValue(0, "paymentId");
rec.SetValue(1, (new Guid((string)id)).ToString());
rec.SetBoolean(2, false);
rec.SetString(3, "uniqueidentifier");
rec.SetValue(4, 0);
rowData.Add(rec);
}
using(SqlCommand cmd = cn.CreateCommand()) {
cmd.Transaction = trans;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "dbo.postPaymentsToGeneralLedger";
cmd.Parameters.Add("@paymentMethodIds", SqlDbType.Structured);
cmd.Parameters["@paymentMethodIds"].Direction = ParameterDirection.Input;
cmd.Parameters["@paymentMethodIds"].Value = rowData;
cmd.Parameters.Add("@unique_site_id", SqlDbType.UniqueIdentifier);
cmd.Parameters["@unique_site_id"].Direction = ParameterDirection.Input;
cmd.Parameters["@unique_site_id"].Value = new Guid(Site.Id.ToString());
cmd.Parameters.Add("@postingDate", SqlDbType.DateTime);
cmd.Parameters["@postingDate"].Direction = ParameterDirection.Input;
cmd.Parameters["@postingDate"].Value = _postingDate;
cmd.Parameters.Add("@referenceNotes", SqlDbType.VarChar);
cmd.Parameters["@referenceNotes"].Direction = ParameterDirection.Input;
cmd.Parameters["@referenceNotes"].Value = postingNotes;
using(SqlDataReader u = cmd.ExecuteReader()) {
u.Read();
error = u.GetInt32(0);
desc = u.GetString(1);
drTotal = u.GetDecimal(2);
crTotal = u.GetDecimal(3);
if(error != 0) {
j.Add("error", error);
j.Add("description", desc);
} else {
j.Add("error", 0);
j.Add("description", "");
}
j.Add("drTotal", drTotal);
j.Add("crTotal", crTotal);
u.NextResult();
/* second batch is an accountant readable GL table - the result of the table vars in the SP
* but not the literal values in the GL, the literal values are next
*/
while(u.Read()) {
/* looks like: userId, handle, addDate, debit, credit, notes */
Dictionary<string, object> row = new Dictionary<string, object>();
row.Add("userId", u.GetInt32(0));
row.Add("handle", u.GetString(1));
row.Add("addDate", u.GetDateTime(2));
row.Add("debit", u.GetDecimal(3));
row.Add("credit", u.GetDecimal(4));
row.Add("notes", u.GetString(5));
accountantReadable.Add(row);
}
/* third batch is the GL entries in the format of the generalLedger table */
u.NextResult();
while(u.Read()) {
Dictionary<string, object> row = new Dictionary<string, object>();
row.Add("generalLedgerId", u.GetGuid(0));
row.Add("refId", u.GetString(1));
row.Add("refType", u.GetInt32(2));
row.Add("creditRecord", u.GetBoolean(3));
row.Add("debitRecord", u.GetBoolean(4));
row.Add("amount", u.GetDecimal(5));
row.Add("userId", u.GetInt32(6));
row.Add("addDate", u.GetDateTime(7));
row.Add("reference", u.GetString(8));
row.Add("generalLedgerInsertId", u.GetGuid(9));
rawGL.Add(row);
}
j.Add("generalLedgerEntries", accountantReadable);
j.Add("rawGL", rawGL);
j.Add("preview", preview);
}
}
if(preview) {
trans.Rollback("paymentPosting");
} else {
trans.Commit();
}
}
}
return j;
}
/// <summary>
/// Creates the SQL data records.
/// </summary>
/// <param name="updatedProcesses">The updated processes.</param>
/// <returns>List of Data Records.</returns>
private static IEnumerable<SqlDataRecord> CreateSqlDataRecords(IDictionary<int, bool> updatedProcesses)
{
var metaData = new SqlMetaData[2];
metaData[0] = new SqlMetaData("Id", SqlDbType.Int);
metaData[1] = new SqlMetaData("Value", SqlDbType.Bit);
var record = new SqlDataRecord(metaData);
foreach (var id in updatedProcesses)
{
record.SetInt32(0, id.Key);
record.SetBoolean(1, !id.Value);
yield return record;
}
}
public void AddParameters(IDbCommand command, SqlMapper.Identity identity)
{
var sqlCommand = (SqlCommand)command;
sqlCommand.CommandType = CommandType.StoredProcedure;
var groups = new List<SqlDataRecord>();
var rules = new List<SqlDataRecord>();
SqlMetaData[] groupSqlType =
{
new SqlMetaData("RuleGroupID", SqlDbType.Int),
new SqlMetaData("GroupGuid", SqlDbType.VarChar, 32),
new SqlMetaData("IsSystem", SqlDbType.Bit),
new SqlMetaData("DisplayOrder", SqlDbType.Int),
new SqlMetaData("[Enabled]", SqlDbType.Bit)
};
SqlMetaData[] ruleSqlType =
{
new SqlMetaData("RuleDetailID", SqlDbType.Int),
new SqlMetaData("RuleTypeID", SqlDbType.Int),
new SqlMetaData("GroupGuid", SqlDbType.Char, 32),
new SqlMetaData("RuleConfiguration", SqlDbType.Xml),
new SqlMetaData("[Enabled]", SqlDbType.Bit)
};
foreach (RuleGroup group in this._groups)
{
var groupRecord = new SqlDataRecord(groupSqlType);
string groupGuid = Guid.NewGuid().ToString().Replace("-", string.Empty);
groupRecord.SetInt32(0, group.ID);
groupRecord.SetString(1, groupGuid);
groupRecord.SetBoolean(2, group.IsSystem);
groupRecord.SetInt32(3, group.DisplayOrder);
groupRecord.SetBoolean(4, group.IsEnabled);
groups.Add(groupRecord);
foreach (var rule in group.Rules)
{
var ruleRecord = new SqlDataRecord(ruleSqlType);
var ruleType = (int)rule.RuleTypeID;
ruleRecord.SetInt32(0, rule.ID);
ruleRecord.SetInt32(1, ruleType);
ruleRecord.SetString(2, groupGuid);
ruleRecord.SetString(3, this.RuleWriter(rule));
ruleRecord.SetSqlBoolean(4, rule.IsEnabled);
rules.Add(ruleRecord);
}
}
var userIDParam = sqlCommand.Parameters.Add("UserID", SqlDbType.Int);
userIDParam.Value = this._userID;
var profileIDParam = sqlCommand.Parameters.Add("ProFileID", SqlDbType.Int);
profileIDParam.Value = this._profileID;
var profileNameParam = sqlCommand.Parameters.Add("ProfileName", SqlDbType.VarChar, 50);
profileNameParam.Value = this._profileName;
var groupsPAram = sqlCommand.Parameters.Add("Groups", SqlDbType.Structured);
groupsPAram.TypeName = "RulesGroupTableType";
groupsPAram.Value = groups;
var rulesParam = sqlCommand.Parameters.Add("Rules", SqlDbType.Structured);
rulesParam.TypeName = "RulesTableType";
rulesParam.Value = rules;
}
public static Dictionary<string, object> CreateUpdateOrDelete( string objectName, List<object> data, bool overwrite, Int64 commandType )
{
var j = new Dictionary<string, object>();
var rowData = new List<SqlDataRecord>();
SqlMetaData[] rowUpdateTable = {
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( var field in data ) {
var inner = ( Dictionary<string, object> )field;
var rec = new SqlDataRecord(rowUpdateTable);
var varCharMaxValue = "";
foreach(var innerField in inner ) {
switch (innerField.Key)
{
case "dataType":
rec.SetString( 3, innerField.Value.ToString() );
break;
case "primaryKey":
rec.SetBoolean( 2, Convert.ToBoolean( innerField.Value ) );
break;
case "name":
rec.SetString( 0, innerField.Value.ToString() );
break;
case "value":
varCharMaxValue = innerField.Value.ToString();
rec.SetValue( 1, innerField.Value );
break;
case "dataLength":
rec.SetValue( 4, Convert.ToInt32( innerField.Value ) );
break;
}
}
rec.SetValue( 5, Convert.ToString( varCharMaxValue ) );
rowData.Add( rec );
}
using(var cn = CreateConnection()) {
cn.Open();
using(var cmd = cn.CreateCommand()) {
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = _createOrUpdateQuery;
cmd.Parameters.Add("@objectName", SqlDbType.VarChar, 50);
cmd.Parameters["@objectName"].Direction = ParameterDirection.Input;
cmd.Parameters["@objectName"].Value = objectName;
cmd.Parameters.Add("@row", SqlDbType.Structured);
cmd.Parameters["@row"].Direction = ParameterDirection.Input;
cmd.Parameters["@row"].Value = rowData;
cmd.Parameters.Add("@overwrite", SqlDbType.Bit);
cmd.Parameters["@overwrite"].Direction = ParameterDirection.Input;
cmd.Parameters["@overwrite"].Value = overwrite;
cmd.Parameters.Add("@commandType", SqlDbType.Int);
cmd.Parameters["@commandType"].Direction = ParameterDirection.Input;
cmd.Parameters["@commandType"].Value = Convert.ToInt32(commandType);/* 0 = Update, 1 = insert, 2 = delete*/
using(var u = cmd.ExecuteReader()) {
u.Read();
j.Add("error", u.GetInt32(0));
j.Add("description", u.GetString(1));
j.Add("primaryKey", u.GetValue(2).ToString());
if(commandType == 0) {
if(u.GetInt32(0) == 0) {
j.Add("RowVersion", u.GetValue(3).ToString());
}
} else {
j.Add("RowVersion", -1);
}
j.Add("commandType", commandType.ToString(CultureInfo.InvariantCulture));
}
}
}
return j;
}