public List <OrderMaster> List(DateTime begin, DateTime end, long customerId, string status, string dateField)
{
List <OrderMaster> result = new List <OrderMaster>();
SpCall spCall = new SpCall("DAT.ORDER_MASTER_LIST");
spCall.SetDateTime("@BEGIN_DATE", begin);
spCall.SetDateTime("@END_DATE", end);
spCall.SetBigInt("@CUSTOMER_ID", customerId);
spCall.SetVarchar("@STATUS", status);
spCall.SetVarchar("@DATE_FIELD", dateField);
DataSet ds = db.ExecuteDataSet(spCall);
if (ds.Tables[0].Rows.Count > 0)
{
ds.Tables[0].AsEnumerable().ToList().ForEach(omRow =>
{
OrderMaster om = ReadOrderMaster(omRow);
om.OrderItems = new List <OrderItem>();
ds.Tables[1].AsEnumerable().Where(itemRow =>
itemRow.Field <long>("ORDER_ID") == om.Id).ToList().ForEach(itemRow =>
om.OrderItems.Add(InitOrderItem(itemRow)));
ds.Tables[2].AsEnumerable().Where(logRow => logRow.Field <long>("ORDER_ID") == om.Id).ToList().ForEach(
logRow => om.InvoiceLog = qm.InitLog(logRow));
result.Add(om);
});
}
return(result);
}
public List <OrderMaster> ListMergeOrders(string invoiceGroup)
{
List <OrderMaster> result = new List <OrderMaster>();
string status = "CR";
SpCall spCall = new SpCall("DAT.ORDER_MASTER_MERGE_LIST");
spCall.SetVarchar("@STATUS", status);
spCall.SetVarchar("@INVOICE_GROUP", invoiceGroup);
DataSet ds = db.ExecuteDataSet(spCall);
if (ds.Tables[0].Rows.Count > 0)
{
ds.Tables[0].AsEnumerable().ToList().ForEach(omRow =>
{
OrderMaster om = ReadOrderMaster(omRow);
om.OrderItems = new List <OrderItem>();
ds.Tables[1].AsEnumerable().Where(itemRow =>
itemRow.Field <long>("ORDER_ID") == om.Id).ToList().ForEach(itemRow =>
om.OrderItems.Add(InitOrderItem(itemRow)));
ds.Tables[2].AsEnumerable().Where(logRow => logRow.Field <long>("ORDER_ID") == om.Id).ToList().ForEach(
logRow => om.InvoiceLog = qm.InitLog(logRow));
result.Add(om);
});
}
return(result);
}
protected override DataTable PrepareTable(List <ITransactionEntity> itemList)
{
SpCall spCall = new SpCall("DAT.GET_QB_PRODUCT_MAP_SCHEMA");
DataTable dt = db.ExecuteDataTable(spCall);
MaestroProduct unknownProduct = new ProductManager(context).GetUnknownItem();
MaestroUnit unknownUnit = new UnitManager(context).GetUnknownItem();
itemList.Cast <QuickBooksProductMapDef>().ToList().ForEach(m =>
{
DataRow row = dt.NewRow();
row["PRODUCT_ID"] = m.Product == null ? unknownProduct.Id : m.Product.Id;
row["QB_CODE"] = m.QuickBooksCode;
row["QB_LIST_ID"] = m.QuickBooksListId;
row["QB_PARENT_CODE"] = m.QuickBooksParentCode;
row["QB_PARENT_LIST_ID"] = m.QuickBooksParentListId;
row["QB_DESCRIPTION"] = m.QuickBooksDescription;
row["PRICE"] = m.Price;
row["UNIT_ID"] = m.Unit == null ? unknownUnit.Id : m.Unit.Id;
row["CREATE_DATE"] = m.CreateDate;
row["CREATE_USER"] = m.CreatedUser;
row["UPDATE_DATE"] = m.UpdateDate;
row["UPDATE_USER"] = m.UpdatedUser;
row["RECORD_STATUS"] = m.RecordStatus;
row["REPORT_LABEL"] = m.Label;
dt.Rows.Add(row);
});
dt.TableName = "DAT.QB_PRODUCT_MAP";
return(dt);
}
public void InsertOrder(OrderMaster orderMaster)
{
orderMaster.CreateDate = DateTime.Now;
SpCall spCall = new SpCall("DAT.ORDER_MASTER_INSERT");
spCall.SetBigInt("@ID", orderMaster.Id);
spCall.SetBigInt("@CUSTOMER_ID", orderMaster.Customer.Id);
spCall.SetDateTime("@ORDER_DATE", orderMaster.OrderDate);
spCall.SetDateTime("@DELIVERY_DATE", orderMaster.DeliveryDate);
spCall.SetVarchar("@PAYMENT_TYPE", orderMaster.PaymentType);
spCall.SetVarchar("@NOTES", orderMaster.Notes);
spCall.SetVarchar("@ORDER_STATUS", orderMaster.OrderStatus);
spCall.SetDateTime("@CREATE_DATE", orderMaster.CreateDate);
spCall.SetVarchar("@CREATE_USER", orderMaster.CreatedUser);
spCall.SetBigInt("@SHIP_ADDRESS", orderMaster.ShippingAddressId);
orderMaster.UpdatedUser = orderMaster.CreatedUser;
orderMaster.UpdateDate = orderMaster.CreateDate;
db.ExecuteNonQuery(spCall);
DataTable dt = PrepareOrderItemsTable(orderMaster.OrderItems);
db.ExecuteBulkInsert(dt);
}
protected override void New()
{
TransactionDefinition td = (TransactionDefinition)request.TransactionEntityList[0];
td.CreateDate = DateTime.Now;
td.CreatedUser = Context.UserName;
if (TransactionCache.Instance.Get(td.TranCode, false) != null)
{
throw new Exception(string.Format("Trancode `{0}` exists", td.TranCode));
}
SpCall spCall = new SpCall("COR.TRANSACTION_DEFINITION_INSERT");
spCall.SetVarchar("@TRAN_CODE", td.TranCode);
spCall.SetVarchar("@TRAN_DESCRIPTION", td.TranDescription);
spCall.SetBit("@IS_CANCELLABLE", td.IsCancellable);
spCall.SetInt("@MENU_ID", td.MenuId);
spCall.SetVarchar("@GROUP_CODE", td.GroupCode);
spCall.SetBit("@QB_RELATED", td.IsQbRelated);
spCall.SetVarchar("@CODE_BASE", td.CodeBase);
spCall.SetDateTime("@CREATE_DATE", td.CreateDate);
spCall.SetVarchar("@CREATE_USER", td.CreatedUser);
long id = db.ExecuteScalar <long>(spCall);
td.Id = id;
Context.TransactionObject = td;
}
DataTable PrepareOrderItemsTable(List <OrderItem> orderItems)
{
//buraya update/insert/delete karasilastirmasi konabilir.
SpCall spCall = new SpCall("DAT.GET_ORDER_ITEM_SCHEMA");
DataTable dt = db.ExecuteDataTable(spCall);
DateTime now = DateTime.Now;
orderItems.ForEach(o =>
{
DataRow dr = dt.NewRow();
dr["ORDER_ID"] = o.OrderId;
dr["PRODUCT_ID"] = o.Product.Id;
dr["QUANTITY"] = o.Quantity;
dr["UNIT_ID"] = o.Unit.Id;
dr["QB_PRODUCT_MAP_ID"] = o.QbProductMap == null ? -1 : o.QbProductMap.Id;
dr["PRICE"] = o.Price;
dr["CREATE_DATE"] = now;
dr["CREATE_USER"] = context.UserName;
dr["UPDATE_DATE"] = now;
dr["UPDATE_USER"] = context.UserName;
dr["RECORD_STATUS"] = "A";
dr["AMOUNT"] = o.Amount;
dt.Rows.Add(dr);
});
dt.TableName = "DAT.ORDER_ITEM";
return(dt);
}
public void Erase(long id)
{
SpCall spCall = new SpCall("DAT.CUSTOMER_ERASE");
spCall.SetBigInt("@ID", id);
db.ExecuteNonQuery(spCall);
}
public MaestroRegion GetUnknownItem()
{
MaestroRegion region = RegionCache.Instance.GetByName(MaestroApplication.Instance.UNKNOWN_ITEM_NAME);
if (region == null)
{
region = new MaestroRegion()
{
PostalCode = "",
GreaterRegion = string.Empty,
Name = MaestroApplication.Instance.UNKNOWN_ITEM_NAME,
CreateDate = DateTime.Now,
UpdateDate = DateTime.Now,
CreatedUser = "******",
UpdatedUser = "******",
Description = ""
};
SpCall call = new SpCall("DAT.REGION_INSERT");
call.SetVarchar("@POSTAL_CODE", region.PostalCode);
call.SetVarchar("@REGION_NAME", region.Name);
call.SetVarchar("@REGION_DESCRIPTION", region.Description);
call.SetVarchar("@GREATER_REGION", region.GreaterRegion);
call.SetDateTime("@CREATE_DATE", region.CreateDate);
call.SetVarchar("@CREATE_USER", "MAESTRO");
region.Id = db.ExecuteScalar <long>(call);
RegionCache.Instance.Reload(true);
}
return(region);
}
protected override DataTable PrepareTable(List <ITransactionEntity> itemList)
{
SpCall spCall = new SpCall("DAT.GET_PRODUCT_SCHEMA");
DataTable dt = db.ExecuteDataTable(spCall);
itemList.Cast <MaestroProduct>().ToList().ForEach(p =>
{
DataRow row = dt.NewRow();
row["PRODUCT_NAME"] = p.Name;
row["PRODUCT_DESCRIPTION"] = p.Description;
row["QB_PRODUCT_ID"] = p.QuickBooksProductId;
row["PRICE"] = p.Price;
row["MINIMUM_ORDER_QUANTITY"] = p.MinimumOrderQuantity;
row["UNIT_TYPE_ID"] = p.UnitType.Id;
row["PRODUCT_GROUP_ID"] = p.GroupId;
row["COST_BASE"] = p.CostBase;
row["CREATE_DATE"] = p.CreateDate;
row["CREATE_USER"] = p.CreatedUser;
row["UPDATE_DATE"] = p.UpdateDate;
row["UPDATE_USER"] = p.UpdatedUser;
row["RECORD_STATUS"] = p.RecordStatus;
dt.Rows.Add(row);
});
dt.TableName = "DAT.PRODUCT";
return(dt);
}
public Dictionary <string, List <Dictionary <string, string> > > ListDashboardSummary()
{
SpCall spCall = new SpCall("RPT.DASHBOARD_SUMMARY");
DataSet ds = db.ExecuteDataSet(spCall);
//first table contains summary codes
List <string> summaryCodes = ds.Tables[0].AsEnumerable().ToList().Select(r => r.Field <string>("TBL_CODE")).ToList();
Dictionary <string, List <Dictionary <string, string> > > result = new Dictionary <string, List <Dictionary <string, string> > >();
for (int i = 1; i < ds.Tables.Count; i++)
{
List <Dictionary <string, string> > tblDict = new List <Dictionary <string, string> >();
DataTable tb = ds.Tables[i];
List <string> columns = tb.Columns.Cast <DataColumn>().Select(c => c.ColumnName).ToList();
tb.AsEnumerable().ToList().ForEach(delegate(DataRow r)
{
Dictionary <string, string> obj = new Dictionary <string, string>();
columns.ForEach(c => obj.Add(c, r[c].ToString()));
tblDict.Add(obj);
});
result.Add(summaryCodes[i - 1], tblDict);
}
return(result);
}
public List <OrderMaster> GetOrders(List <long> ids)
{
SpCall spCall = new SpCall("DAT.ORDER_MASTER_SELECT_MULTI_BY_ID");
spCall.SetStructured <long>("@ID_LIST", "COR.ID_LIST", "ID", ids);
DataSet ds = db.ExecuteDataSet(spCall);
if (ds.Tables[0].Rows.Count == 0)
{
throw new Exception("No orders could be found not found");
}
List <OrderMaster> result = new List <OrderMaster>();
ds.Tables[0].AsEnumerable().ToList().ForEach(row =>
{
long orderId = row.Field <long>("ID");
OrderMaster orderMaster = ReadOrderMaster(row);
orderMaster.OrderItems = ReadOrderItems(ds.Tables[1].AsEnumerable().Where(r => r.Field <long>("ORDER_ID") == orderId).ToList());
orderMaster.InvoiceLog = ReadInvoice(ds.Tables[2].AsEnumerable().First(r => r.Field <long>("ORDER_ID") == orderId));
result.Add(orderMaster);
});
return(result);
}
public void UpdateOrderStatus(List <long> ids, string status)
{
SpCall spCall = new SpCall("DAT.ORDER_MASTER_UPDATE_STATUS");
spCall.SetStructured <long>("@ID_LIST", "COR.ID_LIST", "ID", ids);
spCall.SetVarchar("@ORDER_STATUS", status);
db.ExecuteNonQuery(spCall);
}
public void BackUp(Guid guid)
{
SpCall call = new SpCall("BCK.BACK_UP_PRODUCT");
call.SetVarchar("@BATCH_ID", guid.ToString());
call.SetDateTime("@BATCH_DATE", DateTime.Now);
db.ExecuteNonQuery(call);
}
public long GetNewOrderId()
{
long result = 0;
SpCall spCall = new SpCall("DAT.GET_ORDER_ID");
result = db.ExecuteScalar <long>(spCall);
return(result);
}
public void Delete(long id)
{
SpCall spCall = new SpCall("DAT.PRODUCT_DELETE");
spCall.SetBigInt("@ID", id);
spCall.SetDateTime("@UPDATE_DATE", DateTime.Now);
spCall.SetVarchar("@UPDATE_USER", context.UserName);
db.ExecuteNonQuery(spCall);
}
public long GetNewOrderId(DateTime date)
{
long result = 0;
SpCall spCall = new SpCall("DAT.GET_ORDER_ID_WITH_DATE");
spCall.SetDateTime("@ORDER_DATE", date);
result = db.ExecuteScalar <long>(spCall);
return(result);
}
public void Delete(long id)
{
//MaestroCustomer td = (MaestroCustomer)request.TransactionEntityList[0];
SpCall spCall = new SpCall("DAT.CUSTOMER_DELETE");
spCall.SetBigInt("@ID", id);
spCall.SetDateTime("@UPDATE_DATE", DateTime.Now);
spCall.SetVarchar("@UPDATE_USER", context.UserName);
db.ExecuteNonQuery(spCall);
}
public void InsertNewItem(MaestroProductGroup productGroup)
{
SpCall call = new SpCall("DAT.PRODUCT_GROUP_INSERT");
call.SetVarchar("@PRODUCT_GROUP_NAME", productGroup.Name);
call.SetVarchar("@GROUP_DESCRIPTION", productGroup.GroupDescription);
call.SetDateTime("@CREATE_DATE", productGroup.CreateDate);
call.SetVarchar("@CREATE_USER", productGroup.CreatedUser);
productGroup.Id = db.ExecuteScalar <long>(call);
}
public void UpdateBatch(long batchId, string batchStatus)
{
SpCall spCall = new SpCall("DAT.QB_BATCH_INTEGRATION_UPDATE");
spCall.SetBigInt("@ID", batchId);
spCall.SetVarchar("@STATUS", batchStatus);
spCall.SetDateTime("@UPDATE_DATE", DateTime.Now);
spCall.SetVarchar("@UPDATE_USER", context.UserName);
db.ExecuteNonQuery(spCall);
}
public async Task TestCaptchaRequired()
{
var spCall = new SpCall
{
Method = "CaptchaRequiredMethod"
};
var spInvokeParams = new InvokeOptions();
try
{
await _directSpInvoker.Invoke(spCall, spInvokeParams);
Assert.Fail("SpInvalidCaptchaException was expected!");
}
catch (InvalidCaptchaException ex)
{
var captchaRequest = (CaptchaRequest)ex.SpCallError.ErrorData;
//try with captcha
try
{
spInvokeParams = new InvokeOptions {
ApiInvokeOptions = new ApiInvokeOptions {
CaptchaId = captchaRequest.Id, CaptchaCode = "1234"
}
};
await _directSpInvoker.Invoke(spCall, spInvokeParams);
Assert.Fail("SpInvalidCaptchaException was expected!");
}
catch (InvalidCaptchaException ex2)
{
captchaRequest = (CaptchaRequest)ex2.SpCallError.ErrorData;
spInvokeParams = new InvokeOptions {
ApiInvokeOptions = new ApiInvokeOptions {
CaptchaId = captchaRequest.Id, CaptchaCode = "123"
}
};
await _directSpInvoker.Invoke(spCall, spInvokeParams);
//second call with the same captcha id must fail
try
{
await _directSpInvoker.Invoke(spCall, spInvokeParams);
Assert.Fail("SpInvalidCaptchaException was expected for second retry!");
}
catch (InvalidCaptchaException)
{
//expected exception
}
}
}
}
public void InsertNewItem(MaestroUnit item)
{
SpCall call = new SpCall("DAT.UNIT_INSERT");
call.SetBigInt("@UNIT_TYPE_ID", item.UnitType.Id);
call.SetVarchar("@UNIT_NAME", item.Name);
call.SetVarchar("@QB_UNIT", item.QuickBooksUnit);
call.SetDateTime("@CREATE_DATE", DateTime.Now);
call.SetVarchar("@CREATE_USER", context.UserName);
item.Id = db.ExecuteScalar <long>(call);
}
public void InsertNewItem(MaestroUnitType item)
{
SpCall call = new SpCall("DAT.UNIT_TYPE_INSERT");
call.SetVarchar("@UNIT_TYPE_NAME", item.Name);
call.SetVarchar("@UNIT_TYPE_DESCRIPTION", item.Description);
call.SetBit("@CAN_HAVE_UNITS", item.CanHaveUnits);
call.SetDateTime("@CREATE_DATE", DateTime.Now);
call.SetVarchar("@CREATE_USER", context.UserName);
item.Id = db.ExecuteScalar <long>(call);
}
protected override void BackUp()
{
string guid = Guid.NewGuid().ToString();
SpCall call = new SpCall("BCK.BACK_UP_CUSTOMER");
call.SetVarchar("@BATCH_ID", guid);
call.SetDateTime("@BATCH_DATE", DateTime.Now);
db.ExecuteNonQuery(call);
response.TransactionResult = guid;
response.ResultMessage = string.Format("Backup created with batch id `{0}`", guid) + Environment.NewLine;
}
public long CreateIntegrationBatch(List <long> orderIds)
{
SpCall spCall = new SpCall("DAT.QB_BATCH_INTEGRATION_INSERT");
spCall.SetStructured <long>("@ID_LIST", "COR.ID_LIST", "ID", orderIds);
spCall.SetDateTime("@CREATE_DATE", DateTime.Now);
spCall.SetVarchar("@CREATE_USER", context.UserName);
long result = db.ExecuteScalar <long>(spCall);
return(result);
}
protected override void Delete()
{
long id = ValidateEntityIdFromDataExtension();
Context.TransactionObject = CustomerProductUnitCache.Instance[id];
SpCall spCall = new SpCall("DAT.CUSTOMER_PRODUCT_UNIT_DELETE");
spCall.SetBigInt("@ID", id);
spCall.SetDateTime("@UPDATE_DATE", DateTime.Now);
spCall.SetVarchar("@UPDATE_USER", Context.UserName);
db.ExecuteNonQuery(spCall);
}
protected override void Delete()
{
long id = ValidateEntityIdFromDataExtension();
Context.TransactionObject = UnitTypeCache.Instance[id];
SpCall spCall = new SpCall("DAT.UNIT_TYPE_DELETE");
spCall.SetBigInt("@ID", id);
spCall.SetDateTime("@UPDATE_DATE", DateTime.Now);
spCall.SetVarchar("@UPDATE_USER", Context.UserName);
Context.Database.ExecuteNonQuery(spCall);
}
public async Task TestAsyncInt()
{
var spCall = new SpCall
{
Method = "AsyncIntMethod"
};
var spInvokeParams = new InvokeOptions();
var result = await _directSpInvoker.Invoke(spCall, spInvokeParams);
Assert.AreEqual(result.ReturnValue, 1);
}
void DeleteOrderItem(OrderItem item)
{
item.UpdateDate = DateTime.Now;
item.UpdatedUser = context.UserName;
SpCall spCall = new SpCall("DAT.ORDER_ITEM_DELETE");
spCall.SetBigInt("@ID", item.Id);
spCall.SetDateTime("@UPDATE_DATE", item.UpdateDate);
spCall.SetVarchar("@UPDATE_USER", context.UserName);
db.ExecuteNonQuery(spCall);
}
public void Update(MaestroProductGroup productGroup)
{
SpCall call = new SpCall("DAT.PRODUCT_GROUP_UPDATE");
call.SetBigInt("@ID", productGroup.Id);
call.SetVarchar("@PRODUCT_GROUP_NAME", productGroup.Name);
call.SetVarchar("@GROUP_DESCRIPTION", productGroup.GroupDescription);
call.SetDateTime("@UPDATE_DATE", DateTime.Now);
call.SetVarchar("@UPDATE_USER", context.UserName);
db.ExecuteNonQuery(call);
}
public List <QuickBooksInvoiceLog> List(List <long> idList)
{
List <QuickBooksInvoiceLog> result = new List <QuickBooksInvoiceLog>();
SpCall spCall = new SpCall("DAT.QB_INVOICE_LOG_SELECT_MULTI_BY_ID");
spCall.SetStructured <long>("@ID_LIST", "COR.ID_LIST", "ID", idList);
DataSet ds = db.ExecuteDataSet(spCall);
ds.Tables[0].AsEnumerable().ToList().ForEach(logRow => { result.Add(InitLog(logRow)); });
return(result);
}
