static void Main(string[] args) { XmlDocument xd = new XmlDocument(); xd.LoadXml(Properties.Resources.Data); List <TrainTable> tt = Parser.Controllers.Parser.ParseToList(xd); using (ContextTrain db = new ContextTrain()) { if (db.Database.Exists() == false) { AddingToDatabase.AddToDB(tt); } } int TrainNumber = 2236; string LastStation = "ЧЕРНОРЕЧЕНСКАЯ"; DateTime WhenLastOperation = Convert.ToDateTime("30.06.2019 14:07:00"); Console.WriteLine("Задать значения для отчета? Y/N"); string answer = Console.ReadLine(); if (answer == "Y") { Console.WriteLine("Номер поезда:"); TrainNumber = Convert.ToInt32(Console.ReadLine()); Console.WriteLine("Станция дислокации:"); LastStation = Console.ReadLine().ToUpper(); Console.WriteLine("Дата и время:"); WhenLastOperation = Convert.ToDateTime(Console.ReadLine()); } ExcelReport.ToExcel(TrainNumber, LastStation, WhenLastOperation); Console.WriteLine("Готово!"); Console.ReadKey(); }
/// <summary> /// Метод, декомпозирущий общую спарсенную таблицу и загружающий их в базу данных /// </summary> /// <param name="tt"></param> public static void AddToDB(List <TrainTable> tt) { int IdCount = 0; //Создание таблицы Грузы var Cargoes = new List <Cargo>(); foreach (TrainTable t in tt) { Cargoes.Add(new Cargo { FreightEtsngName = t.FreightEtsngName, }); } Cargoes = Cargoes.Distinct(new CargoComparer()).ToList(); foreach (Cargo c in Cargoes) { c.CargoId = ++IdCount; } IdCount = 0; //Создание таблицы Станции var Stations = new List <Station>(); foreach (TrainTable t in tt) { Stations.Add(new Station { StationName = t.FromStationName }); Stations.Add(new Station { StationName = t.ToStationName }); Stations.Add(new Station { StationName = t.LastStationName }); } Stations = Stations.Distinct(new StationComparer()).ToList(); foreach (Station s in Stations) { s.StationId = ++IdCount; } IdCount = 0; //Создание таблицы Операции var Operations = new List <Operation>(); foreach (TrainTable t in tt) { foreach (Station s in Stations) { if (t.LastStationName == s.StationName) { Operations.Add(new Operation { WhenLastOperation = t.WhenLastOperation, LastOperationName = t.LastOperationName, LastStationId = s.StationId }); } } } Operations = Operations.Distinct(new OperationComparer()).ToList(); foreach (var o in Operations) { o.OperationId = ++IdCount; } IdCount = 0; //Занесение таблиц Грузы, Операции и Станции в общую таблицу вместе с Id TrainTableController.AddStationCargoOperationId(tt, Stations, Cargoes, Operations); //Создание таблицы Вагоны var Carriges = new List <Carrige>(); foreach (TrainTable t in tt) { Carriges.Add(new Carrige { OperationId = t.OperationId, PositionInTrain = t.PositionInTrain, InvoiceNum = t.InvoiceNum, CarNumber = t.CarNumber, CargoId = t.CargoId, FreightTotalWeightKg = t.FreightTotalWeightKg }); } Carriges = Carriges.Distinct(new CarrigeComparer()).ToList(); foreach (var cr in Carriges) { cr.CarrigeId = ++IdCount; } IdCount = 0; // Добавление Id Вагонов в общую таблицу TrainTableController.AddCarrigeId(tt, Carriges); //Создание таблицы Поезда var Trains = new List <Train>(); foreach (TrainTable t in tt) { Trains.Add(new Train { TrainNumber = t.TrainNumber, TrainIndex = t.TrainIndex, TrainIndexCombined = t.TrainIndexCombined, FromStationId = t.FromStationNameId, ToStationId = t.ToStationNameId, CarrigeId = t.CarrigeId }); } Trains = Trains.Distinct(new TrainComparer()).ToList(); foreach (var t in Trains) { t.TrainId = ++IdCount; } //Заполнение таблиц в базе данных using (ContextTrain db = new ContextTrain()) { foreach (var c in Cargoes) { db.Cargos.Add(new Cargo { CargoId = c.CargoId, FreightEtsngName = c.FreightEtsngName }); } foreach (var s in Stations) { db.Stations.Add(new Station { StationId = s.StationId, StationName = s.StationName }); } foreach (var c in Carriges) { db.Carriges.Add(new Carrige { CarrigeId = c.CarrigeId, CarNumber = c.CarNumber, PositionInTrain = c.PositionInTrain, InvoiceNum = c.InvoiceNum, OperationId = c.OperationId, CargoId = c.CargoId, FreightTotalWeightKg = c.FreightTotalWeightKg }); } foreach (var o in Operations) { db.Operations.Add(new Operation { OperationId = o.OperationId, LastOperationName = o.LastOperationName, LastStationId = o.LastStationId, WhenLastOperation = o.WhenLastOperation }); } foreach (var t in Trains) { db.Trains.Add(new Train { TrainId = t.TrainId, TrainIndex = t.TrainIndex, TrainIndexCombined = t.TrainIndexCombined, TrainNumber = t.TrainNumber, FromStationId = t.FromStationId, ToStationId = t.ToStationId, CarrigeId = t.CarrigeId, }); } db.SaveChanges(); } }
/// <summary> /// Метод, реализующий SQL-выборку и последующее заполнение Excel-документа /// </summary> /// <param name="TrainNumber"></param> /// <param name="LastStation"></param> /// <param name="WhenLastOperation"></param> static public void ToExcel(int TrainNumber, string LastStation, DateTime WhenLastOperation) { using (var db = new ContextTrain()) { List <TrainTable> Train_res = (from t in db.Trains join c in db.Carriges on t.CarrigeId equals c.CarrigeId join o in db.Operations on c.OperationId equals o.OperationId join s in db.Stations on o.LastStationId equals s.StationId join cg in db.Cargos on c.CargoId equals cg.CargoId where s.StationName == LastStation && t.TrainNumber == TrainNumber && o.WhenLastOperation == WhenLastOperation orderby c.PositionInTrain ascending select new TrainTable { TrainIndex = t.TrainIndex, CarNumber = c.CarNumber, InvoiceNum = c.InvoiceNum, WhenLastOperation = o.WhenLastOperation, LastOperationName = o.LastOperationName, LastStationName = s.StationName, PositionInTrain = c.PositionInTrain, CargoId = cg.CargoId, FreightEtsngName = cg.FreightEtsngName, FreightTotalWeightKg = c.FreightTotalWeightKg }).ToList(); string excelTemplatePath = Directory.GetCurrentDirectory() + "/NL_Template.xlsx"; FileInfo fileTemplate = new FileInfo(excelTemplatePath); string excelFilePath = Directory.GetCurrentDirectory() + $"/NL-{DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-fff")}.xlsx"; FileInfo file = new FileInfo(excelFilePath); ExcelPackage.LicenseContext = LicenseContext.NonCommercial; using (ExcelPackage excelPackage = new ExcelPackage(file, fileTemplate)) { ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets[0]; //Заполенение номера поезда, номера станции, станции дислокации и даты worksheet.Cells[3, 3].Value = TrainNumber; worksheet.Cells[4, 3].Value = Train_res[0].TrainIndex; worksheet.Cells[3, 5].Value = LastStation; worksheet.Cells[4, 5].Value = WhenLastOperation.ToString("dd.MM.yyyy"); //переменная для нумерации вагонов int count = 0; //переменная, равная началу вывода выборки int index = 7; foreach (TrainTable t in Train_res) { worksheet.Cells[index, 1].Value = ++count; worksheet.Cells[index, 2].Value = t.CarNumber; worksheet.Cells[index, 3].Value = t.InvoiceNum; worksheet.Cells[index, 4].Value = WhenLastOperation.ToString("dd.MM.yyyy"); worksheet.Cells[index, 5].Value = t.FreightEtsngName; worksheet.Cells[index, 6].Value = Convert.ToDouble(t.FreightTotalWeightKg) / 1000; worksheet.Cells[index, 7].Value = t.LastOperationName; index++; } //Список всех типов груза в выборке List <Cargo> CargoType = new List <Cargo>(); foreach (TrainTable t in Train_res) { CargoType.Add(new Cargo { CargoId = t.CargoId, FreightEtsngName = t.FreightEtsngName }); } //Удаление дубликатов CargoType = CargoType.Distinct(new CargoComparer()).ToList(); //переменная для подсчета вагонов по грузам int carrigeCount = 0; //Переменные для общего количества груза и по вагонам соответственно double kgSum = 0; double kg = 0; //Вывод итоговых подсчетов for (int i = 0; i < CargoType.Count(); i++) { worksheet.Cells[index + i, 5].Value = CargoType[i].FreightEtsngName; foreach (TrainTable t in Train_res) { if (t.FreightEtsngName == CargoType[i].FreightEtsngName) { kg += Convert.ToDouble(t.FreightTotalWeightKg) / 1000; worksheet.Cells[index + i, 2].Value = ++carrigeCount; } } worksheet.Cells[index + i, 6].Value = kg; kgSum += kg; kg = 0; carrigeCount = 0; } worksheet.Cells[index + CargoType.Count(), 6].Value = kgSum; worksheet.Cells[index + CargoType.Count(), 1].Value = "Всего:"; worksheet.Cells[index + CargoType.Count(), 2].Value = Train_res.Count(); worksheet.Cells[index + CargoType.Count(), 5].Value = CargoType.Count(); worksheet.Cells["A" + index + ":G" + (index + CargoType.Count())].Style.Font.Bold = true; worksheet.Cells["A7:G" + (index + CargoType.Count())].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; worksheet.Cells["A7:G" + (index + CargoType.Count())].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; worksheet.Cells["A7:G" + (index + CargoType.Count())].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; worksheet.Cells["A7:G" + (index + CargoType.Count())].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; excelPackage.SaveAs(file); } } }