public void UpdateCurrentMonth(DateTime currentDate, int clientId)
{
var items = Dal.GetAll <Item>("select i.* from Item i where i.DefaultIncluded = 1 and i.ClientId = " + clientId + " and (i.ItemTypeId = 1 or " +
"(i.ItemTypeId = 3 and DATEDIFF(MONTH, '" + currentDate + "', (select TOP 1 RelateToMonth from Expense e where e.ItemId = i.ItemId order by RelateToMonth desc)) = MonthsPeriod) or " +
"(i.ItemTypeId = 2 and DATEDIFF(MONTH, '" + currentDate + "', (select TOP 1 RelateToMonth from Expense e where e.ItemId = i.ItemId order by RelateToMonth desc)) = 12))");
List <Expense> expenses = items.Select(i => new Expense
{
ClientId = clientId,
ItemId = i.ItemId,
Name = i.Name,
Payed = false,
RelateToMonth = currentDate,
Price = i.Price
}).ToList();
expenses.ForEach(a => AddExpense(a));
Dal.Save(string.Format("UPDATE Client SET CurrentMonth = '{0}' WHERE ClientId = {1}", currentDate, clientId));
}
public void UpdateUser(User user)
{
Dal.Save(string.Format("UPDATE [User] SET Name = N'{0}', Email = N'{1}', Password = N'{2}', ClientId = {3} WHERE UserId = {4}",
user.Name, user.Email, user.Password, user.ClientId, user.UserId));
}
public void AddUser(User user)
{
Dal.Save(string.Format("INSERT INTO [User] (Name, Email, Password, ClientId) VALUES (N'{0}', N'{1}', N'{2}', {3})",
user.Name, user.Email, user.Password, user.ClientId));
}
public void UpdateItem(Item item)
{
Dal.Save(string.Format("UPDATE ITEM SET Name = N'{0}', Price = {1}, ItemTypeId = {2}, ClientId = {3}, DefaultIncluded = N'{4}', MonthsPeriod = {5} WHERE ItemId = {6}",
item.Name, item.Price, item.ItemTypeId, item.ClientId, item.DefaultIncluded, item.MonthsPeriod, item.ItemId));
}
public void AddItem(Item item)
{
Dal.Save(string.Format("INSERT INTO ITEM (Name, Price, ItemTypeId, ClientId, DefaultIncluded, MonthsPeriod) VALUES (N'{0}', {1}, {2}, {3}, N'{4}', {5})",
item.Name, item.Price, item.ItemTypeId, item.ClientId, item.DefaultIncluded, item.MonthsPeriod));
}
public void UpdateExpense(Expense expense)
{
Dal.Save(string.Format("UPDATE Expense SET Name = N'{0}', ItemId = {1}, UpdatedAt = N'{2}', Price = {3}, RelateToMonth = N'{4}', ClientId = {5}, Description = N'{6}', Payed = N'{7}' WHERE ExpenseId = {8}",
expense.Name, expense.ItemId, DateTime.Now, expense.Price, expense.RelateToMonth, expense.ClientId, expense.Description, expense.Payed, expense.ExpenseId));
}
public void AddExpense(Expense expense)
{
Dal.Save(string.Format("INSERT INTO Expense (Name, ItemId, CreatedAt, UpdatedAt, Price, RelateToMonth, ClientId, Description, Payed) VALUES (N'{0}', {1}, N'{2}', N'{3}', {4}, N'{5}', {6}, N'{7}', N'{8}')",
expense.Name, expense.ItemId, DateTime.Now, DateTime.Now, expense.Price, expense.RelateToMonth, expense.ClientId, expense.Description, expense.Payed));
}
