/// <summary> /// Gets file model by file name. /// </summary> /// <param name="fileName">Specified file name.</param> /// <returns>File model.</returns> public static File GetFile(string fileName) { File file = null; using (ExcelFileDBContext context = new ExcelFileDBContext()) { file = context.File.Where(f => f.Name == fileName).FirstOrDefault(); } return(file); }
/// <summary> /// Gets sums of all columns from all classes. /// </summary> /// <param name="fileId">Specified file id</param> /// <returns>All sums by classes.</returns> public static IEnumerable <BalanceNumber> GetSumByClass(int fileId) { Queue <BalanceNumber> result = new Queue <BalanceNumber>(); using (ExcelFileDBContext context = new ExcelFileDBContext()) { var classes = context.Class.Where(c => c.FileId == fileId).Select(c => new { c.ClassId, }); foreach (var temp in classes) { result.Enqueue(GetTotalSum("[dbo].[SumByClass]", temp.ClassId)); } } return(result); }
/// <summary> /// Gets list of classes that belongs to specified file. /// </summary> /// <param name="fileId">Id of the specified file.</param> /// <returns>List of classes.</returns> public static IEnumerable <Class> GetClasses(int fileId) { IEnumerable <Class> classes = null; using (ExcelFileDBContext context = new ExcelFileDBContext()) { classes = context.Class.Where(c => c.FileId == fileId); foreach (Class @class in classes) { context.Entry(@class).Collection("Balances").Load(); } classes = classes.ToList(); } return(classes); }
/// <summary> /// Executes stored in data base procedure. /// </summary> /// <param name="procedureName">Procedure name.</param> /// <param name="classId">Optional input parameter.</param> /// <returns>Result of the executed procedure.</returns> private static BalanceNumber GetTotalSum(string procedureName, int classId = -1) { BalanceNumber balance = new BalanceNumber(); using (ExcelFileDBContext context = new ExcelFileDBContext()) { try { SqlConnection connection = new SqlConnection(context.Database.Connection.ConnectionString); SqlCommand command = new SqlCommand(procedureName, connection); command.CommandType = System.Data.CommandType.StoredProcedure; var AB = new SqlParameter { ParameterName = "@ABSum", Direction = System.Data.ParameterDirection.Output, SqlDbType = System.Data.SqlDbType.Decimal, Scale = 2 }; var PB = new SqlParameter { ParameterName = "@PBSum", Direction = System.Data.ParameterDirection.Output, SqlDbType = System.Data.SqlDbType.Decimal, Scale = 2 }; var TD = new SqlParameter { ParameterName = "@TDSum", Direction = System.Data.ParameterDirection.Output, SqlDbType = System.Data.SqlDbType.Decimal, Scale = 2 }; var TL = new SqlParameter { ParameterName = "@TLSum", Direction = System.Data.ParameterDirection.Output, SqlDbType = System.Data.SqlDbType.Decimal, Scale = 2 }; var AOB = new SqlParameter { ParameterName = "@AOBSum", Direction = System.Data.ParameterDirection.Output, SqlDbType = System.Data.SqlDbType.Decimal, Scale = 2 }; var POB = new SqlParameter { ParameterName = "@POBSum", Direction = System.Data.ParameterDirection.Output, SqlDbType = System.Data.SqlDbType.Decimal, Scale = 2 }; if (classId > 0) { balance.ClassId = classId; var input = new SqlParameter { ParameterName = "@classId", Direction = System.Data.ParameterDirection.Input, SqlDbType = System.Data.SqlDbType.Int, SqlValue = classId, }; command.Parameters.Add(input); } command.Parameters.Add(AB); command.Parameters.Add(PB); command.Parameters.Add(TD); command.Parameters.Add(TL); command.Parameters.Add(AOB); command.Parameters.Add(POB); connection.Open(); command.ExecuteNonQuery(); balance.AssetBalance = Convert.ToDecimal(command.Parameters["@ABSum"].Value); balance.PassiveBalance = Convert.ToDecimal(command.Parameters["@PBSum"].Value); balance.TurnoverDebit = Convert.ToDecimal(command.Parameters["@TDSum"].Value); balance.TurnoverLoan = Convert.ToDecimal(command.Parameters["@TLSum"].Value); balance.AssetOutgoingBalance = Convert.ToDecimal(command.Parameters["@AOBSum"].Value); balance.PassiveOutgoingBalance = Convert.ToDecimal(command.Parameters["@POBSum"].Value); connection.Close(); } catch (Exception ex) { throw new Exception(ex.Message); } } return(balance); }