public User Find(string domainName)
{
using (FondMcfoContext db = new FondMcfoContext())
{
return db.Users.FirstOrDefault(u => u.DomainName == domainName);
}
}
public int FondExists(string inventory, int idArm)
{
using (var db = new FondMcfoContext())
{
return db.Fonds.Count(f => f.IdArm == idArm && f.Inventory == inventory);
}
}
public List<RGroup> Get()
{
using (var db = new FondMcfoContext())
{
return db.RGroups.ToList();
}
}
public List<v_rStructure> Get()
{
using (var db = new FondMcfoContext())
{
return db.v_rStructure.ToList();
}
}
public List<VrTypeOperations> Get()
{
using (var db = new FondMcfoContext())
{
return db.VrTypeOperations.ToList();
}
}
public List<User> Get()
{
using (FondMcfoContext db = new FondMcfoContext())
{
return db.Users.ToList();
}
}
public void Add(User user)
{
using (FondMcfoContext db = new FondMcfoContext())
{
db.Users.Add(user);
db.SaveChanges();
}
}
public void Edit(User user)
{
using (FondMcfoContext db = new FondMcfoContext())
{
db.Entry(user).State = EntityState.Modified;
db.SaveChanges();
}
}
public void Add(UserSession userSession)
{
using (FondMcfoContext db = new FondMcfoContext())
{
db.UserSessions.Add(userSession);
db.SaveChanges();
}
}}
public List<RGroupBsby> GetForPrint()
{
using (var db = new FondMcfoContext())
{
var rGroupBsby = db.Database.SqlQuery<RGroupBsby>("spp_GroupBsby").ToList();
return rGroupBsby;
}
}
public ObjectContext GetObjectContext()
{
using (var db = new FondMcfoContext())
{
var adapter = (IObjectContextAdapter)db;
return adapter.ObjectContext;
}
}
public int GetIdTypeOperation(string nameTypeOperation)
{
using (var db = new FondMcfoContext())
{
if (db.RTypeOperations.Count(rt => rt.NameTypeOperation == nameTypeOperation) == 0)
return -1;
return db.RTypeOperations.First(rt => rt.NameTypeOperation == nameTypeOperation).IdTypeOperation;
}
}
public string GetDescriptionFondInflirovanie(int month, int year)
{
using (var db = new FondMcfoContext())
{
var fondInflirovanieDescriptionsSel = db.FondInflirovanieDescriptions
.FirstOrDefault(fid => fid.Month == month && fid.Year == year);
return fondInflirovanieDescriptionsSel == null ? "" : fondInflirovanieDescriptionsSel.Description;
}
}
public List<FondMoveAutoPosting> GetFondMoveAutoPosting(int idFondMoveDetail)
{
using (var db = new FondMcfoContext())
{
var fondMoveBsby = db.Database.SqlQuery<FondMoveAutoPosting>("spp_FondMovePostig @IdFondMoveDetail",
new SqlParameter { ParameterName = "@IdFondMoveDetail", SqlDbType = SqlDbType.Int, Value = idFondMoveDetail }
).ToList();
return fondMoveBsby;
}
}
public List<ROperation> GetOperationSelectedTypeOperation(int idTypeOperation)
{
using (var db = new FondMcfoContext())
{
var idOperationsNotSelect = new int[] {1,2,3,13,14,15};
return db.ROperations.Where(op => op.IdTypeOperation == idTypeOperation
&& !idOperationsNotSelect.Contains(op.IdOperation)).ToList();
}
}
public List<LogEvents> GetLog(int idArm,bool isRecvPosting)
{
using (var db = new FondMcfoContext())
{
var result = db.Database.SqlQuery<LogEvents>("spp_LogEvent @idArm, @isEventPosting",
new SqlParameter { ParameterName = "@idArm", SqlDbType = SqlDbType.Int, Value = idArm },
new SqlParameter { ParameterName = "@isEventPosting", SqlDbType = SqlDbType.Int, Value = isRecvPosting }
).ToList();
return result;
}
}
public string FondMoveHandPostingMoveAuto(int idFondMoveDetails)
{
using (var db = new FondMcfoContext())
{
var result = db.Database.SqlQuery<String>("spu_FondMoveHandPostingMoveAuto @IdFondMoveDetail",
new SqlParameter { ParameterName = "@IdFondMoveDetail", SqlDbType = SqlDbType.Int, Value = idFondMoveDetails }
).ToList().FirstOrDefault();
return result;
}
}
public List<FondMoveBsby> GetFondMoveBsby(int month, int year, int idArm)
{
using (var db = new FondMcfoContext())
{
var fondMoveBsby = db.Database.SqlQuery<FondMoveBsby>("spp_FondMoveBSBY @Year, @Month, @idArm",
new SqlParameter { ParameterName = "@Year", SqlDbType = SqlDbType.Int, Value = year },
new SqlParameter { ParameterName = "@Month", SqlDbType = SqlDbType.Int, Value = month },
new SqlParameter { ParameterName = "@idArm", SqlDbType = SqlDbType.Int, Value = idArm }
).ToList();
return fondMoveBsby;
}
}
public string RecvPostingInBalance(int year, int month, int idPostingFile)
{
using (var db = new FondMcfoContext())
{
var result = db.Database.SqlQuery<String>("spu_PostingsInBalance @year, @month, @idPostingsFile",
new SqlParameter { ParameterName = "@year", SqlDbType = SqlDbType.Int, Value = year },
new SqlParameter { ParameterName = "@month", SqlDbType = SqlDbType.Int, Value = month },
new SqlParameter { ParameterName = "@idPostingsFile", SqlDbType = SqlDbType.Int, Value = idPostingFile }
).ToList().FirstOrDefault();
return result;
}
}
public void AddSession(string domainName, UserSession sessions)
{
using (FondMcfoContext db = new FondMcfoContext())
{
User user = db.Users.FirstOrDefault(u => u.DomainName == domainName);
if (user != null)
{
user.UserSessionses.Add(sessions);
db.SaveChanges();
}
}
}
public List<CardIndexInfo> GetCardIndexInfo(int month, int year, int idFond)
{
using (var db = new FondMcfoContext())
{
var cardIndexAll = db.Database.SqlQuery<CardIndexInfo>("spp_CardIndexInfo @year, @month, @idFond",
new SqlParameter { ParameterName = "@year", SqlDbType = SqlDbType.Int, Value = year },
new SqlParameter { ParameterName = "@month", SqlDbType = SqlDbType.Int, Value = month },
new SqlParameter { ParameterName = "@idFond", SqlDbType = SqlDbType.Int, Value = idFond }
).ToList();
return cardIndexAll;
}
}
public HistoryAmortization GetHistoryAmortization(int idFond, int month, int year)
{
using (var db = new FondMcfoContext())
{
var fondsAmortizationInfo = db.Database.SqlQuery<HistoryAmortization>("spp_HistoryAmmortization @year, @month, @idFond",
new SqlParameter { ParameterName = "@year", SqlDbType = SqlDbType.Int, Value = year },
new SqlParameter { ParameterName = "@month", SqlDbType = SqlDbType.Int, Value = month },
new SqlParameter { ParameterName = "@idFond", SqlDbType = SqlDbType.Int, Value = idFond }
).ToList().FirstOrDefault();
return fondsAmortizationInfo;
}
}
public List<FondInflirovanieInfo> GetFondInflirovanieInfo(int month, int year, int idArm)
{
using (var db = new FondMcfoContext())
{
var fondsInflirovanieInfo = db.Database.SqlQuery<FondInflirovanieInfo>("spp_FondInflirovanie @year, @month, @idArm",
new SqlParameter { ParameterName = "@year", SqlDbType = SqlDbType.Int, Value = year },
new SqlParameter { ParameterName = "@month", SqlDbType = SqlDbType.Int, Value = month },
new SqlParameter { ParameterName = "@idArm", SqlDbType = SqlDbType.Int, Value = idArm }
).ToList();
return fondsInflirovanieInfo;
}
}
public List<CardIndexAll> GetCardIndexAll(int month, int year, int idArm, bool isInvestRealty)
{
using (var db = new FondMcfoContext())
{
var cardIndexAll = db.Database.SqlQuery<CardIndexAll>("spp_CardIndexAll @year, @month, @idArm, @isInvestRealty",
new SqlParameter { ParameterName = "@year", SqlDbType = SqlDbType.Int, Value = year},
new SqlParameter { ParameterName = "@month", SqlDbType = SqlDbType.Int, Value = month},
new SqlParameter { ParameterName = "@idArm", SqlDbType = SqlDbType.Int, Value = idArm},
new SqlParameter { ParameterName = "@isInvestRealty", SqlDbType = SqlDbType.Bit, Value = isInvestRealty}
).ToList();
return cardIndexAll;
}
}
public int LoadCalcFondMoveMsfo(int month, int year, int idArm)
{
using (var db = new FondMcfoContext())
{
var resultParam = new SqlParameter { Direction = ParameterDirection.Output, ParameterName = "@result", SqlDbType = SqlDbType.Int };
db.Database.ExecuteSqlCommand("exec @result = spu_FondMoveMSFO @Year, @Month, @idArm",
resultParam,
new SqlParameter {ParameterName = "@Year", SqlDbType = SqlDbType.Int, Value = year},
new SqlParameter {ParameterName = "@Month", SqlDbType = SqlDbType.Int, Value = month},
new SqlParameter {ParameterName = "@idArm", SqlDbType = SqlDbType.Int, Value = idArm}
);
return (int)resultParam.Value;
}
}
public bool IsTypeOperationInclOperation(int idTypeOperation)
{
using (var db = new FondMcfoContext())
{
var typeOperationSel = db.RTypeOperations.FirstOrDefault(it => it.IdTypeOperation == idTypeOperation);
if (typeOperationSel == null)
return false;
else
{
if (typeOperationSel.ROperations == null)
return false;
else
return true;
}
}
}
public List<RGroupMsfoBsbyIncl> Get()
{
using (var db = new FondMcfoContext())
{
var rGroupMsfoBsbyIncl = ( from groupBsbyIncl in db.GroupBsbyIncls
join rGroup in db.RGroups on groupBsbyIncl.IdGroup equals rGroup.IdGroup
join rGroupBsby in db.v_rGroups on groupBsbyIncl.IdGroupBsby equals rGroupBsby.idGroup
select new RGroupMsfoBsbyIncl
{
NameGroup = rGroup.NameGroup,
NameGroupBsby = rGroupBsby.nameGroup,
}
).OrderBy(t=>t.NameGroup).ToList();
return rGroupMsfoBsbyIncl;
}
}
public List<ROperationBsby> GetForPrint(int? idArm)
{
using (var db = new FondMcfoContext())
{
var rOperationsBsby = db.v_rOperations.Join(db.VrTypeOperations, // второй набор
p => p.idTypeOperation, // свойство-селектор объекта из первого набора
c => c.IdTypeOperation, // свойство-селектор объекта из второго набора
(p, c) => new ROperationBsby // результат
{
IdOperation = p.IdOperation,
NameTypeOperation = c.NameTypeOperation,
NameOperation = p.nameOperation,
IdArm = p.idArm
}
).Where(p => (idArm==null) || p.IdArm == idArm).OrderBy(p => p.NameTypeOperation).ThenBy(p => p.NameOperation).ToList();
return rOperationsBsby;
}
}
public string FondMoveHandPostings(int year, int month, int idArm,
object idFondMovePosting, object idFondMove, object debet, object credit, object cost, object isDelete)
{
using (var db = new FondMcfoContext())
{
var result = db.Database.SqlQuery<String>("spu_FondMoveHandPosting @year, @month, @idArm, @IdFondMovePosting, @IdFondMove, @Debet, @Kredit, @Cost, @IsDelete",
new SqlParameter { ParameterName = "@year", SqlDbType = SqlDbType.Int, Value = year },
new SqlParameter { ParameterName = "@month", SqlDbType = SqlDbType.Int, Value = month },
new SqlParameter { ParameterName = "@idArm", SqlDbType = SqlDbType.Int, Value = idArm },
new SqlParameter { ParameterName = "@IdFondMovePosting", SqlDbType = SqlDbType.Int, IsNullable = true, Value = idFondMovePosting ?? DBNull.Value },
new SqlParameter { ParameterName = "@IdFondMove", SqlDbType = SqlDbType.Int, IsNullable = true, Value = idFondMove ?? DBNull.Value },
new SqlParameter { ParameterName = "@Debet", SqlDbType = SqlDbType.VarChar, IsNullable = true, Value = debet ?? DBNull.Value },
new SqlParameter { ParameterName = "@Kredit", SqlDbType = SqlDbType.VarChar, IsNullable = true, Value = credit ?? DBNull.Value },
new SqlParameter { ParameterName = "@Cost", SqlDbType = SqlDbType.Decimal, IsNullable = true, Value = cost ?? DBNull.Value },
new SqlParameter { ParameterName = "@IsDelete", SqlDbType = SqlDbType.Int, IsNullable = true, Value = isDelete ?? DBNull.Value }
).ToList().FirstOrDefault();
return result;
}
}
public string FondMoveEditorInsert(FondMoveEditorParams fondMoveEditorParams)
{
using (var db = new FondMcfoContext())
{
var result = db.Database.SqlQuery<String>("spu_FondMoveMSFOEditor @IdTypeOperation, @IdOperation, @Month, @Year, " +
"@Inventory,@IdArm,@BalanceCost,@Iznos, @Comment," +
"@Inventory1,@IdArm1,@PartMove,@IdGroupInsert," +
"@YearInput,@MonthInput",
new SqlParameter { ParameterName = "@IdTypeOperation", SqlDbType = SqlDbType.Int,
Value = fondMoveEditorParams.IdTypeOperation },
new SqlParameter { ParameterName = "@IdOperation", SqlDbType = SqlDbType.Int,
Value = fondMoveEditorParams.IdOperation },
new SqlParameter { ParameterName = "@Month", SqlDbType = SqlDbType.Int,
Value = fondMoveEditorParams.Month },
new SqlParameter { ParameterName = "@Year", SqlDbType = SqlDbType.Int,
Value = fondMoveEditorParams.Year },
new SqlParameter { ParameterName = "@Inventory", SqlDbType = SqlDbType.VarChar,
Value = fondMoveEditorParams.Inventory },
new SqlParameter { ParameterName = "@IdArm", SqlDbType = SqlDbType.Int,
Value = fondMoveEditorParams.IdArm },
new SqlParameter { ParameterName = "@BalanceCost", SqlDbType = SqlDbType.Decimal,
Value = fondMoveEditorParams.BalanceCost },
new SqlParameter { ParameterName = "@Iznos", SqlDbType = SqlDbType.Decimal,
Value = fondMoveEditorParams.Iznos },
new SqlParameter { ParameterName = "@Comment", SqlDbType = SqlDbType.VarChar,
Value = fondMoveEditorParams.Comment },
new SqlParameter { ParameterName = "@Inventory1", SqlDbType = SqlDbType.VarChar,
Value = fondMoveEditorParams.Inventory1 },
new SqlParameter { ParameterName = "@IdArm1", SqlDbType = SqlDbType.Int,
Value = fondMoveEditorParams.IdArm1 },
new SqlParameter { ParameterName = "@PartMove", SqlDbType = SqlDbType.Decimal,
Value = fondMoveEditorParams.PartMove },
new SqlParameter { ParameterName = "@IdGroupInsert", SqlDbType = SqlDbType.Int,
Value = fondMoveEditorParams.IdGroupInsert },
new SqlParameter { ParameterName = "@YearInput", SqlDbType = SqlDbType.Int,
Value = fondMoveEditorParams.YearInput },
new SqlParameter { ParameterName = "@MonthInput", SqlDbType = SqlDbType.Int,
Value = fondMoveEditorParams.MonthInput }
).ToList().First();
return result;
}
}
