Exemplo n.º 1
0
 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);
                         }
                     }
                 }
             }
         }
     }
 }
Exemplo n.º 2
0
        // Получение информации по заказу
        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();
        }
Exemplo n.º 4
0
        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();
        }
Exemplo n.º 5
0
        // Получения заказов которые были изменены за 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);
        }
Exemplo n.º 6
0
        //Получение значения з БД
        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);
        }
Exemplo n.º 7
0
        //Получение продуктов в заказе
        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>());
        }
Exemplo n.º 8
0
        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);
        }
Exemplo n.º 9
0
        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);
        }
Exemplo n.º 10
0
        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);
        }