Пример #1
0
 private static void IgnoreIfNull(IList nonKeyFields, DbCapacity dbCapacity)
 {
     RemoveIfNull(nonKeyFields, dbCapacity.Load, "load");
     RemoveIfNull(nonKeyFields, dbCapacity.Deliveries, "deliveries");
     RemoveIfNull(nonKeyFields, dbCapacity.Shifts, "shifts");
     RemoveIfNull(nonKeyFields, dbCapacity.Capacity, "capacity");
 }
Пример #2
0
        public void InsertCapacity(DbCapacity dbCapacity)
        {
            var special = new[] { $"(select id from {DepotTable} where name = @depot)", "depot_id" };
            var keyFieldsMinusSpecial = new List <string> {
                "day", "shift"
            };
            var nonKeyFields = new List <string> {
                "load", "deliveries", "shifts", "capacity"
            };
            var allFields = keyFieldsMinusSpecial;

            IgnoreIfNull(nonKeyFields, dbCapacity);
            allFields.AddRange(nonKeyFields);

            var sql = $"merge into {CapacityTable} as target " +
                      $"using (select {special[0]},{Utils.ToCsv(allFields, "@")}) as source({special[1]},{Utils.ToCsv(allFields)}) " +
                      "on target.depot_id = source.depot_id and target.day = source.day and target.shift = source.shift " +
                      (nonKeyFields.Any() ? $"when matched then update set {CreateUpdates(nonKeyFields, "target", "source")} " : string.Empty) +
                      $"when not matched then insert({special[1]},{Utils.ToCsv(allFields)}) values ({special[0]},{Utils.ToCsv(allFields, "source.")});";

            using var connection = new SqlConnection(_dbConnectionString);
            using var command    = new SqlCommand(sql, connection);
            command.Parameters.AddWithValue("@depot", dbCapacity.Depot);
            command.Parameters.AddWithValue("@day", dbCapacity.Day);
            command.Parameters.AddWithValue("@shift", dbCapacity.Shift);
            if (dbCapacity.Load.HasValue)
            {
                command.Parameters.AddWithValue("@load", dbCapacity.Load.Value);
            }
            if (dbCapacity.Deliveries.HasValue)
            {
                command.Parameters.AddWithValue("@deliveries", dbCapacity.Deliveries.Value);
            }
            if (dbCapacity.Shifts.HasValue)
            {
                command.Parameters.AddWithValue("@shifts", dbCapacity.Shifts.Value);
            }

            if (dbCapacity.Capacity.HasValue)
            {
                command.Parameters.AddWithValue("@capacity", dbCapacity.Capacity.Value);
            }
            connection.Open();
            command.ExecuteNonQuery();
        }
Пример #3
0
        public void InsertDateCapacity(DbCapacity dbCapacity)
        {
            var special = new[] { $"(select id from {DepotTable} where name = @depot)", "depot_id" };
            var keyFieldsMinusSpecial = new List <string> {
                "date", "shift"
            };
            var nonKeyFields = new List <string> {
                "load", "deliveries", "shifts", "capacity"
            };
            var allFields = keyFieldsMinusSpecial;

            IgnoreIfNull(nonKeyFields, dbCapacity);
            allFields.AddRange(nonKeyFields);

            var sql = $"insert into {CapacityDateTable} ({special[1]},{Utils.ToCsv(allFields)}) values ({special[0]},{Utils.ToCsv(allFields, "@")})";

            using var connection = new SqlConnection(_dbConnectionString);
            using var command    = new SqlCommand(sql, connection);
            command.Parameters.AddWithValue("@depot", dbCapacity.Depot);
            command.Parameters.AddWithValue("@shift", dbCapacity.Shift);
            command.Parameters.AddWithValue("@date", dbCapacity.Date);
            if (dbCapacity.Load.HasValue)
            {
                command.Parameters.AddWithValue("@load", dbCapacity.Load.Value);
            }
            if (dbCapacity.Deliveries.HasValue)
            {
                command.Parameters.AddWithValue("@deliveries", dbCapacity.Deliveries.Value);
            }
            if (dbCapacity.Shifts.HasValue)
            {
                command.Parameters.AddWithValue("@shifts", dbCapacity.Shifts.Value);
            }

            if (dbCapacity.Capacity.HasValue)
            {
                command.Parameters.AddWithValue("@capacity", dbCapacity.Capacity.Value);
            }
            connection.Open();
            command.ExecuteNonQuery();
        }