public static int GetMaxYearDossier(int dossierId)
{
int?max = 0;
using (PersonalFinancesDBEntities context = new PersonalFinancesDBEntities())
{
IEnumerable <int> res = context.Database.SqlQuery <int>(String.Format("SELECT [dbo].[GetMaxYearDossier]({0})", dossierId));
max = res.ElementAt(0);
}
/*if you want use executeScalar th SP should have select and not RETURN*/
//using (SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["PersonalFinancesDB"].ConnectionString))
//{
// con.Open();
// //SqlCommand cmd = new SqlCommand("GetMaxYearDossier", con);
// SqlCommand cmd = new SqlCommand("GetMaxYearDossier", con);
// cmd.Parameters.Add("@dossierId", SqlDbType.Int);
// cmd.Parameters["@dossierId"].Value = dossierId;
// cmd.CommandType = CommandType.StoredProcedure;
// var cesso = cmd.ExecuteScalar();
// max = (cesso == null) ? 0 : (int)cesso;
//}
return(max ?? 0);
}
public static void CreateRecordsFromImport(int dossierId, bool isExpense)
{
using (PersonalFinancesDBEntities context = new PersonalFinancesDBEntities())
{
context.CreateRecordsFromImport(dossierId, isExpense);
}
}
public static List <POCO.record> GetDoubles(int dossierId, int importRecordTmpId)
{
var list = new List <POCO.record>();
PersonalFinancesDBEntities db = new PersonalFinancesDBEntities();
importRecordTmp irt = db.importRecordTmps.Find(importRecordTmpId);
list = (from rec in db.sp_getListDuplicates(irt.dossierId,
irt.revenue,
irt.expense,
irt.date,
irt.description,
irt.comment)
select new POCO.record
{
dossierId = rec.dossierId,
description = rec.description,
comment = rec.comment,
date = rec.date,
revenue = rec.revenue,
expense = rec.expense,
CategoryDescription = rec.category,
SubcategoryDescription = rec.subcategory
}).ToList();
return(list);
}
public static AssetModel GetAssetModel(int dossierId, int assetId = 0)
{
PersonalFinancesDBEntities db = new PersonalFinancesDBEntities();
var asset = new AssetModel();
asset.assetId = assetId;
asset.subcategories = GetCategories(dossierId);
if (assetId != 0)
{
//existing record
asset assetDTO = db.assets.Find(assetId);
asset.dossierId = dossierId;
asset.receivable = decimal.Round(assetDTO.receivable, 2, MidpointRounding.AwayFromZero).ToString();;
asset.payable = decimal.Round(assetDTO.payable, 2, MidpointRounding.AwayFromZero).ToString();;
asset.isAsset = (assetDTO.receivable >= assetDTO.payable);
asset.description = assetDTO.description;
asset.assetSubcategoryId = assetDTO.assetSubcategoryId;
}
else
{
//new record
asset.dossierId = dossierId;
asset.receivable = "0,00";
asset.payable = "0,00";
asset.description = "";
asset.assetSubcategoryId = 0;
}
return(asset);
}
public static void DeleteDossier(int dossierId)
{
using (PersonalFinancesDBEntities context = new PersonalFinancesDBEntities())
{
context.DeleteDossier(dossierId);
}
}
public static void DeleteAsset(int dossierId, int assetId)
{
PersonalFinancesDBEntities db = new PersonalFinancesDBEntities();
var asset = db.assets.Where(a => a.dossierId == dossierId && a.assetId == assetId).Single();
db.assets.Remove(asset);
db.SaveChanges();
}
public RecordTmpController(PersonalFinancesDBEntities dbContext,
IImportProcessor importProcessor)
{
_dbContext = dbContext;
// _iImportProcessor = new ExcelImportProcessor();
_iImportProcessor = importProcessor;
}
public static void DeleteSelectedRecords(int dossierId,
string beginDate = "01/01/1900",
string endDate = "31/12/9999")
{
PersonalFinancesDBEntities context = new PersonalFinancesDBEntities();
context.sp_deleteRecordSelection(dossierId, beginDate, endDate);
}
public static void Delete(int dossierId, int importRecordTmpId)
{
PersonalFinancesDBEntities db = new PersonalFinancesDBEntities();
importRecordTmp irt = db.importRecordTmps.Where(i => i.dossierId == dossierId && i.importRecordTmpId == importRecordTmpId).Single();
db.importRecordTmps.Remove(irt);
db.SaveChanges();
}
public static void UpdateSubcategory(POCO.assetSubcategory assetSubcategory)
{
PersonalFinancesDBEntities db = new PersonalFinancesDBEntities();
var aC = db.assetSubcategories.Find(assetSubcategory.assetSubcategoryId);
aC.description = assetSubcategory.description;
aC.assetCategoryId = assetSubcategory.assetCategoryId;
db.Entry(aC).State = EntityState.Modified;
db.SaveChanges();
}
public static int GetMinYearDossier(int dossierId)
{
int?min = 0;
using (PersonalFinancesDBEntities context = new PersonalFinancesDBEntities())
{
IEnumerable <int> res = context.Database.SqlQuery <int>(String.Format("SELECT [dbo].[GetMinYearDossier]({0})", dossierId));
min = res.ElementAt(0);
}
return(min ?? 0);
}
public static void DeleteCategory(int dossierId, int assetCategoryId)
{
PersonalFinancesDBEntities db = new PersonalFinancesDBEntities();
var aC = (from c in db.assetCategories
where c.assetCategoryId == assetCategoryId &&
c.dossierId == dossierId
select c).Single();
db.assetCategories.Remove(aC);
db.SaveChanges();
}
public static void AddSubCategory(assetSubcategory subcat)
{
PersonalFinancesDBEntities db = new PersonalFinancesDBEntities();
var catEntity = new PersonalFinances.DATA.DataModel.assetSubcategory
{
assetCategoryId = subcat.assetCategoryId,
description = subcat.description
};
db.assetSubcategories.Add(catEntity);
db.SaveChanges();
}
public static assetCategory GetCategory(int assetCategoryId)
{
PersonalFinancesDBEntities db = new PersonalFinancesDBEntities();
var aC = db.assetCategories.Find(assetCategoryId);
return(new POCO.assetCategory
{
dossierId = aC.dossierId,
assetCategoryId = aC.assetCategoryId,
description = aC.description,
isAsset = aC.isAsset
});
}
public static void AddCategory(assetCategory cat)
{
PersonalFinancesDBEntities db = new PersonalFinancesDBEntities();
var catEntity = new PersonalFinances.DATA.DataModel.assetCategory {
dossierId = cat.dossierId,
isAsset = cat.isAsset,
description = cat.description
};
db.assetCategories.Add(catEntity);
db.SaveChanges();
}
public static void DeleteSubcategory(int dossierId, int assetSubcategoryId)
{
PersonalFinancesDBEntities db = new PersonalFinancesDBEntities();
// var aSC = db.assetSubcategories.Find(assetSubcategoryId);
var aSC = (from sc in db.assetSubcategories
join c in db.assetCategories on sc.assetCategoryId equals c.assetCategoryId
where sc.assetSubcategoryId == assetSubcategoryId &&
c.dossierId == dossierId
select sc).Single();
db.assetSubcategories.Remove(aSC);
db.SaveChanges();
}
public static void CreateDossier(DossierModel dossier, string UserName)
{
PersonalFinancesDBEntities db = new PersonalFinancesDBEntities();
dossier dos = new dossier();
string userId = (from u in db.AspNetUsers
where u.UserName == UserName
select u.Id).Single();
////TODO: give real value
dos.userId = userId;
dos.dossierName = dossier.dossierName;
dos.creationDate = DateTime.Now;
db.dossiers.Add(dos);
db.SaveChanges();
}
public static List <POCO.assetSubcategory> GetCategories(int dossierId)
{
PersonalFinancesDBEntities db = new PersonalFinancesDBEntities();
List <POCO.assetSubcategory> subcategories = (from sc in db.assetSubcategoryViews
where sc.dossierId == dossierId
select new POCO.assetSubcategory
{
assetSubcategoryId = sc.assetSubcategoryId,
description = sc.description,
dossierId = sc.dossierId,
isAsset = sc.isAsset,
categoryDescription = sc.categoryDescription,
assetCategoryId = sc.assetCategoryId
}).ToList();
//this.categories = FromListEntityToListPOCO<POCO.recordCategory, recordCategory>(categories);
return(subcategories.OrderBy(s => s.categoryDescription).ToList());
}
public static List <DossierDetailsModel> ListDossierUser(string userName)
{
List <DossierDetailsModel> list = new List <DossierDetailsModel>();
PersonalFinancesDBEntities _context = new PersonalFinancesDBEntities();
List <dossier> dossiers = (from d in _context.dossiers
join u in _context.AspNetUsers on d.userId equals u.Id
where u.UserName == userName
select d).ToList();
foreach (var dossier in dossiers)
{
DossierDetailsModel DDM = new DossierDetailsModel(dossier.dossierId);
list.Add(DDM);
}
return(list);
}
public RecordsImporter(PersonalFinancesDBEntities dbContext)
{
_dbContext = dbContext;
}
