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 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;
}
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);
}
public void UpdateInvoiceLog(QuickBooksInvoiceLog log)
{
SpCall spCall = new SpCall("DAT.QB_INVOICE_LOG_UPDATE");
spCall.SetBigInt("@ID", log.Id);
spCall.SetVarchar("@INTEGRATION_STATUS", log.IntegrationStatus);
spCall.SetDateTime("@INTEGRATION_DATE", log.IntegrationDate);
spCall.SetBigInt("@BATCH_ID", log.BatchId);
spCall.SetVarchar("@QB_INVOICE_NO", log.QuickBooksInvoiceId);
spCall.SetVarchar("@QB_TXN_ID", log.QuickBooksTxnId);
spCall.SetVarchar("@ERROR_LOG", log.ErrorLog);
spCall.SetDateTime("@UPDATE_DATE", log.CreateDate);
spCall.SetVarchar("@UPDATE_USER", context.UserName);
spCall.SetVarchar("@RECORD_STATUS", log.RecordStatus);
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 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 List <QuickBooksInvoiceLog> List(DateTime begin, DateTime end, long customerId, string status, long batchId, bool notIntegrated)
{
List <QuickBooksInvoiceLog> result = new List <QuickBooksInvoiceLog>();
SpCall spCall = new SpCall("DAT.QB_INVOICE_LOG_LIST");
spCall.SetVarchar("@INTEGRATION_STATUS", status);
spCall.SetDateTime("@INTEGRATION_DATE_BEGIN", begin);
spCall.SetDateTime("@INTEGRATION_DATE_END", end);
spCall.SetBigInt("@BATCH_ID", batchId);
spCall.SetBigInt("@CUSTOMER_ID", customerId);
spCall.SetBit("@NOT_INTEGRATED", notIntegrated);
DataSet ds = db.ExecuteDataSet(spCall);
ds.Tables[0].AsEnumerable().ToList().ForEach(logRow => { result.Add(InitLog(logRow)); });
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);
}
void UpdateOrderInternal(OrderMaster orderMaster, bool cleanItems)
{
orderMaster.UpdateDate = DateTime.Now;
SpCall spCall = new SpCall("DAT.ORDER_MASTER_UPDATE");
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("@UPDATE_DATE", orderMaster.UpdateDate);
spCall.SetVarchar("@UPDATE_USER", orderMaster.UpdatedUser);
spCall.SetBit("@CLEAN_ORDERITEMS", cleanItems);
spCall.SetBigInt("@SHIP_ADDRESS", orderMaster.ShippingAddressId);
db.ExecuteNonQuery(spCall);
}
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 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 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 override void LoadData()
{
MaestroReportDefinition reportDef = (MaestroReportDefinition)context.Bag["REPORT_DEF"];
DateTime beginDate = (DateTime)context.Bag[MessageDataExtensionKeys.BEGIN_DATE];
DateTime endDate = (DateTime)context.Bag[MessageDataExtensionKeys.END_DATE];
SpCall call = new SpCall(reportDef.ProcedureName);
call.SetDateTime("@BEGIN_DATE", beginDate);
call.SetDateTime("@END_DATE", endDate);
reportData = context.Database.ExecuteDataSet(call);
if (reportData.Tables[0].Rows.Count == 0)
{
throw new Exception(string.Format("No data can be found between {0} and {1}", beginDate, endDate));
}
baseData = reportData.Tables[0].AsEnumerable().ToList();
}
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 void InsertIntegrationLog(QuickBooksInvoiceLog log)
{
SpCall spCall = new SpCall("DAT.QB_INVOICE_LOG_INSERT");
spCall.SetBigInt("@ORDER_ID", log.OrderId);
spCall.SetBigInt("@MAESTRO_CUSTOMER_ID", log.Customer.Id);
spCall.SetVarchar("@QB_CUSTOMER_ID", log.Customer.QuickBooksId);
spCall.SetVarchar("@INTEGRATION_STATUS", log.IntegrationStatus);
spCall.SetDateTime("@INTEGRATION_DATE", log.IntegrationDate);
spCall.SetBigInt("@BATCH_ID", log.BatchId);
spCall.SetVarchar("@QB_INVOICE_NO", log.QuickBooksInvoiceId);
spCall.SetVarchar("@QB_TXN_ID", log.QuickBooksTxnId);
spCall.SetVarchar("@ERROR_LOG", log.ErrorLog);
spCall.SetDateTime("@CREATE_DATE", log.CreateDate);
spCall.SetVarchar("@CREATE_USER", context.UserName);
long result = db.ExecuteScalar <long>(spCall);
log.Id = result;
}
public List <TransactionLog> List(TransactionLogQuery logQuery)
{
List <TransactionLog> result = new List <TransactionLog>();
SpCall call = new SpCall("COR.TRANSACTION_LOG_LIST");
call.SetBigInt("@ID", logQuery.Id);
call.SetVarchar("@TRAN_CODE", logQuery.TransactionCode);
call.SetVarchar("@TRANSACTION_STATUS", logQuery.Status);
call.SetDateTime("@BEGIN_DATE", logQuery.BeginDate);
call.SetDateTime("@END_DATE", logQuery.EndDate);
call.SetVarchar("@CREATE_USER", logQuery.User);
using (SqlReader reader = db.ExecuteReader(call))
{
while (reader.Read())
{
TransactionLog log = new TransactionLog()
{
Id = reader.GetInt64("ID"),
CreateDate = reader.GetDateTime("CREATE_DATE"),
CreatedUser = reader.GetString("CREATE_USER"),
Log = reader.GetValue <byte[]>("TRANSACTION_LOG"),
LogJson = reader.GetString("TRANSACTION_JSON"),
RecordStatus = reader.GetString("RECORD_STATUS"),
Status = reader.GetString("TRANSACTION_STATUS"),
TransactionCode = reader.GetString("TRAN_CODE"),
LogObjectId = reader.GetInt64("TRANSACTION_OBJECT_ID"),
ActionType = reader.GetString("ACTION_TYPE"),
RequestType = reader.GetString("REQUEST_TYPE"),
UpdateDate = reader.GetDateTime("UPDATE_DATE"),
UpdatedUser = reader.GetString("UPDATE_USER"),
Duration = reader.GetInt32("DURATION")
};
SetDisplayProperties(log);
result.Add(log);
}
}
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);
}
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 = 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);
}
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);
}
void InsertExecutionLog(DateTime startDate)
{
DateTime endDate = DateTime.Now;
double duration = endDate.Subtract(startDate).TotalMilliseconds;
OnLogEventHandler(string.Format("Inserting execution log for Task `{0}`, duration:{1} miliseconds", taskName, duration), null);
using (Database db = new Database())
{
SpCall call = new SpCall("COR.TASK_SCHEDULER_LOG_INSERT");
call.SetVarchar("@TASK_NAME", taskName);
call.SetDateTime("@START_DATE", startDate);
call.SetDateTime("@END_DATE", endDate);
call.SetInt("@INTERVALL", intervall);
call.SetVarchar("@INTERVALL_UNIT", intervallUnit.ToString());
call.SetDecimal("@DURATION", Convert.ToDecimal(duration));
call.SetDateTime("@CREATE_DATE", DateTime.Now);
db.ExecuteNonQuery(call);
}
this.lastExecution = endDate;
}
protected override void Delete()
{
string userName = request.MessageHeader.UserName;
long id = ValidateEntityIdFromDataExtension();
Context.TransactionObject = UnitCache.Instance[id];
SpCall spCall = new SpCall("DAT.UNIT_DELETE");
spCall.SetBigInt("@ID", id);
spCall.SetDateTime("@UPDATE_DATE", DateTime.Now);
spCall.SetVarchar("@UPDATE_USER", userName);
db.ExecuteNonQuery(spCall);
}
protected override void Update()
{
MaestroUnitType item = (MaestroUnitType)request.TransactionEntityList[0];
Context.TransactionObject = item;
SpCall call = new SpCall("DAT.UNIT_TYPE_UPDATE");
call.SetBigInt("@ID", item.Id);
call.SetVarchar("@UNIT_TYPE_NAME", item.Name);
call.SetVarchar("@UNIT_TYPE_DESCRIPTION", item.Description);
call.SetBit("@CAN_HAVE_UNITS", item.CanHaveUnits);
call.SetDateTime("@UPDATE_DATE", DateTime.Now);
call.SetVarchar("@UPDATE_USER", Context.UserName);
db.ExecuteNonQuery(call);
}
protected override void Update()
{
MaestroUnit item = (MaestroUnit)request.TransactionEntityList[0];
Context.TransactionObject = item;
SpCall call = new SpCall("DAT.UNIT_UPDATE");
call.SetBigInt("@ID", item.Id);
call.SetBigInt("@UNIT_TYPE_ID", item.UnitType.Id);
call.SetVarchar("@UNIT_NAME", item.Name);
call.SetVarchar("@QB_UNIT", item.QuickBooksUnit);
call.SetDateTime("@UPDATE_DATE", DateTime.Now);
call.SetVarchar("@UPDATE_USER", Context.UserName);
db.ExecuteNonQuery(call);
}
protected override void New()
{
MaestroRegion region = (MaestroRegion)request.TransactionEntityList[0];
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", DateTime.Now);
call.SetVarchar("@CREATE_USER", Context.UserName);
region.Id = db.ExecuteScalar <long>(call);
response.TransactionResult = region;
}
public static void InsertCacheRegistryInfo(CacheRegistryInfo cacheRegistryInfo)
{
SpCall sp = new SpCall("CACHE.CACHE_REGISTRY_INSERT");
sp.SetVarchar("@GLOBAL_CACHE_NAME", cacheRegistryInfo.CacheId);
sp.SetVarchar("@CACHE_ID", cacheRegistryInfo.CacheId);
sp.SetVarchar("@APPLICATION_CODE", "MAESTRO");
sp.SetVarchar("@CACHE_HOST_NAME", cacheRegistryInfo.HostName);
sp.SetDateTime("@UPDATE_DATE", DateTime.Now);
sp.SetVarchar("@RECORD_STATUS", "A");
using (Database db = new Database())
{
db.ExecuteNonQuery(sp);
}
}
