public void InsertUnloadDate(DateTime unloadDate, List <Guid> ordersId)
{
foreach (var orderId in ordersId)
{
Select s = new Select(userConnection).
Column("UsrUnloadDate").From("Order").
Where("Id").IsEqual(Column.Parameter(orderId)) as Select;
using (var dbExecutor = userConnection.EnsureDBConnection())
{
using (var dataReader = s.ExecuteReader(dbExecutor))
{
if (dataReader.Read())
{
var date = dataReader.GetColumnValue <DateTime>("UsrUnloadDate");
if (date == DateTime.MinValue || date == null)
{
var orderEntity = userConnection.EntitySchemaManager.GetInstanceByName("Order").CreateEntity(userConnection);
if (orderEntity.FetchFromDB("Id", orderId))
{
orderEntity.SetColumnValue("UsrUnloadDate", unloadDate);
orderEntity.Save(false);
}
}
}
}
}
}
}
// Получение информации по заказу
public List <Order> GetOrderInfo(List <Guid> orders)
{
var Order = new List <Order>();
foreach (var orderId in orders)
{
Select selectOrder = new Select(userConnection)
.Column("Number")
.Column("StatusId")
.Column("ModifiedOn")
.From("Order")
.Where("Id").IsEqual(Column.Parameter(orderId)) as Select;
using (DBExecutor dbExecutor = userConnection.EnsureDBConnection())
{
using (IDataReader reader = selectOrder.ExecuteReader(dbExecutor))
{
while (reader.Read())
{
var orderStatusId = reader.GetColumnValue <Guid>("StatusId");
var orderStatus = GetLookupBPM("OrderStatus", "Id", orderStatusId);
Order.Add(new Order()
{
Number = reader.GetColumnValue <string>("Number"),
Status = orderStatus,
ModifiedOn = reader.GetColumnValue <DateTime>("ModifiedOn")
});
}
}
}
}
return(Order);
}
public void InsertErrorMessage(string logMessage)
{
Insert insert = new Insert(userConnection).Into("UsrIntegrationLogFtp")
.Set("UsrName", Column.Parameter("ServiceLog"))
.Set("UsrErrorDescription", Column.Parameter(logMessage));
insert.Execute();
}
public void InsertErrorMessage(string logMessage)
{
Insert insert = new Insert(userConnection).Into("UsrIntegrationLogFtp")
.Set("UsrName", Column.Parameter("Загрузка файлов c папки OUTProducts"))
.Set("UsrErrorDescription", Column.Parameter(logMessage));
insert.Execute();
}
// Получения заказов которые были изменены за 5 мин
public List <Guid> GetModifiedOrdersId()
{
var dateFormat = "yyyy-MM-dd HH:mm:ss";
var startDate = DateTime.Now.AddHours(-3).AddMinutes(-5).ToString(dateFormat);
var endDate = DateTime.Now.AddHours(-3).ToString(dateFormat);
var orderList = new List <Guid>();
Select select = new Select(userConnection)
.Column("Order", "Id")
.From("Order")
.Join(JoinType.LeftOuter, "OrderStatus")
.On("OrderStatus", "Id").IsEqual("Order", "StatusId")
.Where("Order", "ModifiedOn").IsBetween(Column.Parameter(startDate)).And(Column.Parameter(endDate))
//
.And("OrderStatus", "UsrCode").IsEqual(Column.Parameter(16)).And("Order", "UsrUnloadDate").IsNull()
//
/*.And("OrderStatus", "UsrCode").In(
* Column.Parameter(16),
* Column.Parameter(6),
* Column.Parameter(13))*/
as Select;
using (DBExecutor dbExecutor = userConnection.EnsureDBConnection())
{
using (IDataReader reader = select.ExecuteReader(dbExecutor))
{
while (reader.Read())
{
orderList.Add(reader.GetColumnValue <Guid>("Id"));
}
Select selectOrders = new Select(userConnection)
.Column("Order", "Id")
.From("Order")
.Join(JoinType.LeftOuter, "OrderStatus")
.On("OrderStatus", "Id").IsEqual("Order", "StatusId")
.Where("Order", "ModifiedOn").IsBetween(Column.Parameter(startDate)).And(Column.Parameter(endDate))
.And("OrderStatus", "UsrCode").In(
Column.Parameter(6),
Column.Parameter(13)).And("Order", "UsrUnloadDate").Not().IsNull()
as Select;
using (DBExecutor dbExecutor2 = userConnection.EnsureDBConnection())
{
using (IDataReader reader2 = selectOrders.ExecuteReader(dbExecutor2))
{
while (reader2.Read())
{
orderList.Add(reader2.GetColumnValue <Guid>("Id"));
}
}
}
}
}
return(orderList);
}
//Получение значения з БД
public string GetLookupBPM(string table, string column, Guid value)
{
if (value == null)
{
return(String.Empty);
}
if (table == "Order")
{
var orderName = (new Select(userConnection).Top(1)
.Column("Number")
.From(table)
.Where(column).IsEqual(Column.Parameter(value)) as Select).ExecuteScalar <string>();
return(orderName);
}
if (table == "OrderStatus")
{
var orderStatus = (new Select(userConnection).Top(1)
.Column("UsrCode")
.From(table)
.Where(column).IsEqual(Column.Parameter(value)) as Select).ExecuteScalar <string>();
return(orderStatus);
}
if (table == "UsrDeliveryService")
{
var deliveryService = (new Select(userConnection).Top(1)
.Column("UsrERPCode")
.From(table)
.Where(column).IsEqual(Column.Parameter(value)) as Select).ExecuteScalar <string>();
return(deliveryService);
}
if (table == "OrderPaymentStatus")
{
var paymentStatus = (new Select(userConnection).Top(1)
.Column("UsrPaymentStatusCode")
.From(table)
.Where(column).IsEqual(Column.Parameter(value)) as Select).ExecuteScalar <string>();
return(paymentStatus);
}
if (table == "DeliveryType")
{
var deliveryType = (new Select(userConnection).Top(1)
.Column("UsrErpCode")
.From(table)
.Where(column).IsEqual(Column.Parameter(value)) as Select).ExecuteScalar <string>();
return(deliveryType);
}
var data = (new Select(userConnection).Top(1)
.Column("Name")
.From(table)
.Where(column).IsEqual(Column.Parameter(value)) as Select).ExecuteScalar <string>();
return(data);
}
//Получение продуктов в заказе
public List <Order> GetOrderProductInfo(List <Guid> orders)
{
var OrderProduct = new List <Order>();
foreach (var orderId in orders)
{
Select selectOrder = new Select(userConnection)
.Column("OrderProduct", "OrderId")
.Column("OrderProduct", "UsrOrderItemNumber")
.Column("Order", "Number")
.Column("OrderProduct", "Name")
.Column("OrderProduct", "UsrSKU")
.Column("OrderProduct", "Quantity")
.Column("OrderProduct", "UsrInStock")
.Column("Order", "UsrWeight")
.Column("OrderProduct", "UsrOriginalPrice")
.Column("OrderProduct", "Price")
.Column("OrderProduct", "UsrDiscountedPrice")
.Column("OrderProduct", "DiscountAmount")
.Column("OrderProduct", "Amount")
.Column("OrderProduct", "UsrAmountPriceDiscount")
.From("Order")
.Join(JoinType.Inner, "OrderProduct")
.On("OrderProduct", "OrderId").IsEqual("Order", "Id")
.Where("Order", "Id").IsEqual(Column.Parameter(orderId)) as Select;
using (DBExecutor dbExecutor = userConnection.EnsureDBConnection())
{
using (IDataReader reader = selectOrder.ExecuteReader(dbExecutor))
{
while (reader.Read())
{
OrderProduct.Add(new Order
{
UsrOrderItemNumber = reader.GetColumnValue <int>("UsrOrderItemNumber"),
Number = reader.GetColumnValue <string>("Number"),
Name = reader.GetColumnValue <string>("Name"),
UsrSKU = reader.GetColumnValue <string>("UsrSKU"),
Quantity = reader.GetColumnValue <double>("Quantity"),
UsrInStock = reader.GetColumnValue <double>("UsrInStock"),
UsrWeightProduct = reader.GetColumnValue <double>("UsrWeight"),
UsrOriginalPrice = reader.GetColumnValue <double>("UsrOriginalPrice"),
Price = reader.GetColumnValue <double>("Price"),
UsrDiscountedPrice = reader.GetColumnValue <double>("UsrDiscountedPrice"),
DiscountAmount = reader.GetColumnValue <double>("DiscountAmount"),
Amount = reader.GetColumnValue <double>("Amount"),
UsrAmountPriceDiscount = reader.GetColumnValue <double>("UsrAmountPriceDiscount")
});
}
}
}
}
return(OrderProduct.OrderBy(x => x.OrderId).ToList <Order>());
}
public object GetLookupBPMIdByInt(string table, string column, long value)
{
if (value == 0)
{
return(Guid.Empty);
}
var lookupBPMId = (new Select(userConnection).Top(1)
.Column("Id")
.From(table)
.Where(column).IsEqual(Column.Parameter(value)) as Select).ExecuteScalar <object>();
return(lookupBPMId);
}
public List <Order> GetOrderDiscountsInfo(List <Guid> orders)
{
var OrderDiscounts = new List <Order>();
foreach (var orderId in orders)
{
Select selectOrder = new Select(userConnection)
.Column("UsrShares", "UsrOrderSharesId")
.Column("UsrShares", "UsrOrderItemNumber")
.Column("UsrShares", "UsrProductSharesId")
.Column("UsrShares", "UsrSKU")
.Column("UsrShares", "UsrQuantity")
.Column("UsrShares", "UsrRuleId")
.Column("UsrShares", "UsrRuleName")
.Column("UsrShares", "UsrPrice")
.Column("UsrShares", "UsrAmountPrice")
.Column("UsrShares", "UsrDiscountPercent")
.Column("UsrShares", "UsrDiscount")
.Column("UsrShares", "UsrDiscountAmount")
.Column("UsrShares", "UsrPriceDiscounted")
.Column("UsrShares", "UsrAmountPriceDiscounted")
.Column("UsrShares", "UsrSetId")
.From("UsrShares")
.Join(JoinType.Inner, "Order")
.On("Order", "Id").IsEqual("UsrShares", "UsrOrderSharesId")
.Join(JoinType.LeftOuter, "Product")
.On("Product", "Id").IsEqual("UsrShares", "UsrProductSharesId")
.Where("UsrShares", "UsrOrderSharesId").IsEqual(Column.Parameter(orderId)) as Select;
using (DBExecutor dbExecutor = userConnection.EnsureDBConnection())
{
using (IDataReader reader = selectOrder.ExecuteReader(dbExecutor))
{
while (reader.Read())
{
var orderSharesId = reader.GetColumnValue <Guid>("UsrOrderSharesId");
var order = GetLookupBPM("Order", "Id", orderSharesId);
var productSharesId = reader.GetColumnValue <Guid>("UsrProductSharesId");
var product = GetLookupBPM("Product", "Id", productSharesId);
OrderDiscounts.Add(new Order
{
Number = order,
UsrOrderItemNumber = reader.GetColumnValue <int>("UsrOrderItemNumber"),
ProductName = product,
UsrSKU = reader.GetColumnValue <string>("UsrSKU"),
UsrQuantity = reader.GetColumnValue <double>("UsrQuantity"),
UsrRuleId = reader.GetColumnValue <string>("UsrRuleId"),
UsrRuleName = reader.GetColumnValue <string>("UsrRuleName"),
UsrPrice = reader.GetColumnValue <double>("UsrPrice"),
UsrAmountPrice = reader.GetColumnValue <double>("UsrAmountPrice"),
UsrDiscountPercent = reader.GetColumnValue <double>("UsrDiscountPercent"),
UsrDiscount = reader.GetColumnValue <double>("UsrDiscount"),
UsrDiscountAmount = reader.GetColumnValue <double>("UsrDiscountAmount"),
UsrPriceDiscounted = reader.GetColumnValue <double>("UsrPriceDiscounted"),
UsrAmountPriceDiscounted = reader.GetColumnValue <double>("UsrAmountPriceDiscounted"),
UsrSetId = reader.GetColumnValue <double>("UsrSetId")
});
}
}
}
}
return(OrderDiscounts);
}
public List <Order> GetOrderHeadersInfo(List <Guid> orders)
{
var OrderHeaders = new List <Order>();
foreach (var orderId in orders)
{
Select selectOrder = new Select(userConnection)
.Column("Order", "Id")
.Column("Order", "Number")
.Column("Order", "UsrTypeId")
.Column("Order", "UsrId")
.Column("Order", "StatusId")
.Column("Order", "OwnerId")
.Column("Order", "PaymentStatusId")
.Column("Order", "UsrDeliveryDate")
.Column("Order", "PaymentAmount")
.Column("Order", "UsrCostDelivery")
.Column("Order", "UsrWeight")
.Column("Order", "UsrPaymentMethod")
.Column("Order", "ReceiverName")
.Column("Contact", "Email")
.Column("Order", "ContactNumber")
.Column("Contact", "UsrNumberActiveCard")
.Column("Order", "Comment")
.Column("Order", "UsrParentId")
.Column("Order", "UsrDeliveryServiceId")
.Column("Order", "UsrDeliveryDepartment")
.Column("Order", "DeliveryTypeId")
.Column("Order", "UsrCity")
.Column("Order", "UsrAddress")
.Column("Order", "UsrHouse")
.Column("Order", "UsrCorps")
.Column("Order", "UsrApartment")
.Column("Order", "UsrCityCode")
.Column("Order", "UsrStreetCode")
.From("Order")
.Join(JoinType.LeftOuter, "Contact")
.On("Contact", "Id").IsEqual("Order", "ContactId")
.Where("Order", "Id").IsEqual(Column.Parameter(orderId)) as Select;
using (DBExecutor dbExecutor = userConnection.EnsureDBConnection())
{
using (IDataReader reader = selectOrder.ExecuteReader(dbExecutor))
{
while (reader.Read())
{
var statusId = reader.GetColumnValue <Guid>("StatusId");
var status = GetLookupBPM("OrderStatus", "Id", statusId);
var ownerId = reader.GetColumnValue <Guid>("OwnerId");
var owner = GetLookupBPM("Contact", "Id", ownerId);
var parentOrderId = reader.GetColumnValue <Guid>("UsrParentId");
var parentOrder = GetLookupBPM("Order", "Id", parentOrderId);
var deliveryServiceId = reader.GetColumnValue <Guid>("UsrDeliveryServiceId");
var deliveryService = GetLookupBPM("UsrDeliveryService", "Id", deliveryServiceId);
var deliveryTypeId = reader.GetColumnValue <Guid>("DeliveryTypeId");
var deliveryType = GetLookupBPM("DeliveryType", "Id", deliveryTypeId);
var paymentStatusId = reader.GetColumnValue <Guid>("PaymentStatusId");
var paymentStatus = GetLookupBPM("OrderPaymentStatus", "Id", paymentStatusId);
var typeId = reader.GetColumnValue <Guid>("UsrTypeId");
var type = GetLookupBPM("UsrOrderType", "Id", typeId);
OrderHeaders.Add(new Order
{
Number = reader.GetColumnValue <string>("Number"),
UsrType = type,
UsrId = reader.GetColumnValue <string>("UsrId"),
Status = status,
Owner = owner,
PaymentStatus = paymentStatus,
PaymentAmount = reader.GetColumnValue <double>("PaymentAmount"),
UsrWeightOrder = reader.GetColumnValue <double>("UsrWeight"),
UsrPaymentMethod = reader.GetColumnValue <string>("UsrPaymentMethod"),
UsrDeliveryDate = reader.GetColumnValue <DateTime>("UsrDeliveryDate"),
Contact = reader.GetColumnValue <string>("ReceiverName"),
Email = reader.GetColumnValue <string>("Email"),
ContactNumber = reader.GetColumnValue <string>("ContactNumber"),
UsrNumberActiveCard = reader.GetColumnValue <string>("UsrNumberActiveCard"),
Comment = reader.GetColumnValue <string>("Comment"),
UsrParent = parentOrder,
UsrDeliveryService = deliveryService,
UsrDeliveryDepartment = reader.GetColumnValue <string>("UsrDeliveryDepartment"),
DeliveryType = deliveryType,
UsrCity = reader.GetColumnValue <string>("UsrCity"),
UsrAddress = reader.GetColumnValue <string>("UsrAddress"),
UsrHouse = reader.GetColumnValue <string>("UsrHouse"),
UsrCorps = reader.GetColumnValue <string>("UsrCorps"),
UsrApartment = reader.GetColumnValue <string>("UsrApartment"),
UsrCostDelivery = reader.GetColumnValue <double>("UsrCostDelivery"),
UsrCityCode = reader.GetColumnValue <string>("UsrCityCode"),
UsrStreetCode = reader.GetColumnValue <string>("UsrStreetCode")
});
}
}
}
}
return(OrderHeaders);
}