public int Save(ICustomer customer)
{
String query = @"INSERT INTO [dbo].[Customer]
([Name]
,[Description]
,[Active]
,[DefaultTransactionTypeId])
OUTPUT INSERTED.Id
VALUES
( @Name
, @Description
, @Active
, @DefaultTransactionTypeId)";
if (customer.Id != 0)
{
query = @"UPDATE [dbo].[Customer]
SET [Name] = @Name
,[Description] = @Description
,[Active] = @Active
,[DefaultTransactionTypeId] = @DefaultTransactionTypeId
WHERE Id = @Id";
}
IList <SqlParameter> sqlParameterCollection = new List <SqlParameter>();
sqlParameterCollection.Add(new SqlParameter("@Name", customer.Name));
sqlParameterCollection.Add(new SqlParameter("@Description", customer.Description));
sqlParameterCollection.Add(new SqlParameter("@Active", customer.Active));
if (customer.DefaultTransactionTypeId.HasValue)
{
sqlParameterCollection.Add(new SqlParameter("@DefaultTransactionTypeId", customer.DefaultTransactionTypeId.Value));
}
else
{
sqlParameterCollection.Add(new SqlParameter("@DefaultTransactionTypeId", DBNull.Value));
}
if (customer.Id != 0)
{
sqlParameterCollection.Add(new SqlParameter("@Id", customer.Id));
SqlService.ExecuteNonQuery(query, sqlParameterCollection.ToArray());
return(customer.Id);
}
else
{
return(SqlService.ExecuteScalar(query, sqlParameterCollection.ToArray()));
}
}
public IList <ITransactionType> GetTransactionTypes(ITransactionTypeFilter filter)
{
SqlQueryBuilder sqlQueryBuilder = new SqlQueryBuilder();
sqlQueryBuilder.Select = " SELECT * ";
sqlQueryBuilder.From = " FROM [dbo].[TransactionType] ";
if (filter.Id.HasValue)
{
sqlQueryBuilder.Where.Add(new SqlWhere()
{
Where = "Id = @Id", Param = new SqlParameter("@Id", filter.Id.Value)
});
}
if (filter.Income.HasValue)
{
sqlQueryBuilder.Where.Add(new SqlWhere()
{
Where = "Income = @Income", Param = new SqlParameter("@Income", filter.Income.Value)
});
}
if (!string.IsNullOrWhiteSpace(filter.Color))
{
sqlQueryBuilder.Where.Add(new SqlWhere()
{
Where = "Color = @Color", Param = new SqlParameter("@Color", filter.Color)
});
}
if (!string.IsNullOrWhiteSpace(filter.Name))
{
sqlQueryBuilder.Where.Add(new SqlWhere()
{
Where = "Name = @Name", Param = new SqlParameter("@Name", filter.Name)
});
}
var table = SqlService.GetDataTable(sqlQueryBuilder);
var myEnumerable = table.AsEnumerable();
return((from item in myEnumerable select new TransactionType {
Income = item.Field <bool>("Income"),
Color = item.Field <string>("Color"),
Name = item.Field <string>("Name"),
Description = item.Field <string>("Description"),
Id = item.Field <int>("Id")
}).ToList <ITransactionType>());
}
public IList <ITransaction> GetTransactions(ITransactionFilter filter)
{
SqlQueryBuilder sqlQueryBuilder = new SqlQueryBuilder();
sqlQueryBuilder.Select = " SELECT * ";
sqlQueryBuilder.From = " FROM [dbo].[Transaction] ";
if (filter.Id.HasValue)
{
sqlQueryBuilder.Where.Add(new SqlWhere()
{
Where = "Id = @Id", Param = new SqlParameter("@Id", filter.Id.Value)
});
}
if (filter.CustomerId.HasValue)
{
sqlQueryBuilder.Where.Add(new SqlWhere()
{
Where = "CustomerId = @CustomerId", Param = new SqlParameter("@CustomerId", filter.CustomerId.Value)
});
}
if (filter.TransactionTypeId.HasValue)
{
sqlQueryBuilder.Where.Add(new SqlWhere()
{
Where = "TransactionTypeId = @TransactionTypeId", Param = new SqlParameter("@TransactionTypeId", filter.TransactionTypeId.Value)
});
}
if (!string.IsNullOrWhiteSpace(filter.Name))
{
sqlQueryBuilder.Where.Add(new SqlWhere()
{
Where = "Name = @Name", Param = new SqlParameter("@Name", filter.Name)
});
}
if (filter.Value.HasValue)
{
sqlQueryBuilder.Where.Add(new SqlWhere()
{
Where = "Value = @Value", Param = new SqlParameter("@Value", filter.Value.Value)
});
}
if (filter.ValueMin.HasValue)
{
sqlQueryBuilder.Where.Add(new SqlWhere()
{
Where = "Value >= @ValueMin", Param = new SqlParameter("@ValueMin", filter.ValueMin.Value)
});
}
if (filter.ValueMax.HasValue)
{
sqlQueryBuilder.Where.Add(new SqlWhere()
{
Where = "Value <= @ValueMax", Param = new SqlParameter("@ValueMax", filter.ValueMax.Value)
});
}
if (filter.Date.HasValue)
{
sqlQueryBuilder.Where.Add(new SqlWhere()
{
Where = "Date = @Date", Param = new SqlParameter("@Date", filter.Date.Value)
});
}
if (filter.DateMin.HasValue)
{
sqlQueryBuilder.Where.Add(new SqlWhere()
{
Where = "Date >= @DateMin", Param = new SqlParameter("@DateMin", filter.DateMin.Value)
});
}
if (filter.DateMax.HasValue)
{
sqlQueryBuilder.Where.Add(new SqlWhere()
{
Where = "Date <= @DateMax", Param = new SqlParameter("@DateMax", filter.DateMax.Value)
});
}
var table = SqlService.GetDataTable(sqlQueryBuilder);
var myEnumerable = table.AsEnumerable();
var result = (from item in myEnumerable select new Transaction {
Id = item.Field <int>("Id"),
Name = item.Field <string>("Name"),
Description = item.Field <string>("Description"),
Value = item.Field <decimal>("Value"),
TransactionTypeId = item.Field <int>("TransactionTypeId"),
CustomerId = item.Field <int?>("CustomerId"),
Date = item.Field <DateTime>("Date")
}).ToList <ITransaction>();
var ttService = new TransactionTypeService();
var tts = ttService.GetTransactionTypes();
var cService = new CustomerService();
var cs = cService.GetCustomers();
foreach (var trans in result)
{
trans.TransactionType = tts.Where(tt => tt.Id == trans.TransactionTypeId).FirstOrDefault();
if (trans.CustomerId.HasValue)
{
trans.Customer = cs.Where(c => c.Id == trans.CustomerId.Value).FirstOrDefault();
}
}
return(result);
}
public int Save(ITransaction transaction)
{
string query = @"INSERT INTO [dbo].[Transaction]
([Name]
,[Description]
,[Value]
,[TransactionTypeId]
,[CustomerId]
,[Date])
OUTPUT INSERTED.Id
VALUES
( @Name
, @Description
, @Value
, @TransactionTypeId
, @CustomerId
, @Date )";
if (transaction.Id != 0)
{
query = @"UPDATE [dbo].[Transaction]
SET [Name] = @Name
,[Description] = @Description
,[Value] = @Value
,[TransactionTypeId] = @TransactionTypeId
,[CustomerId] = @CustomerId
,[Date] = @Date
WHERE Id = @Id";
}
IList <SqlParameter> sqlParameterCollection = new List <SqlParameter>();
if (string.IsNullOrWhiteSpace(transaction.Name))
{
sqlParameterCollection.Add(new SqlParameter("@Name", DBNull.Value));
}
else
{
sqlParameterCollection.Add(new SqlParameter("@Name", transaction.Name));
}
if (string.IsNullOrWhiteSpace(transaction.Description))
{
sqlParameterCollection.Add(new SqlParameter("@Description", DBNull.Value));
}
else
{
sqlParameterCollection.Add(new SqlParameter("@Description", transaction.Description));
}
sqlParameterCollection.Add(new SqlParameter("@Value", transaction.Value));
sqlParameterCollection.Add(new SqlParameter("@TransactionTypeId", transaction.TransactionTypeId));
sqlParameterCollection.Add(new SqlParameter("@Date", transaction.Date));
if (transaction.CustomerId.HasValue)
{
sqlParameterCollection.Add(new SqlParameter("@CustomerId", transaction.CustomerId));
}
else
{
sqlParameterCollection.Add(new SqlParameter("@CustomerId", DBNull.Value));
}
if (transaction.Id != 0)
{
sqlParameterCollection.Add(new SqlParameter("@Id", transaction.Id));
SqlService.ExecuteNonQuery(query, sqlParameterCollection.ToArray());
return(transaction.Id);
}
else
{
return(SqlService.ExecuteScalar(query, sqlParameterCollection.ToArray()));
}
}
public int Save(ITransactionType transactionType)
{
string query = @"INSERT INTO [dbo].[TransactionType]
([Name]
,[Description]
,[Color]
,[Income])
OUTPUT INSERTED.Id
VALUES
( @Name
, @Description
, @Color
, @Income)";
if (transactionType.Id != 0)
{
query = @"UPDATE [dbo].[TransactionType]
SET [Name] = @Name
,[Description] = @Description
,[Color] = @Color
,[Income] = @Income
WHERE Id = @Id";
}
IList <SqlParameter> sqlParameterCollection = new List <SqlParameter>();
sqlParameterCollection.Add(new SqlParameter("@Income", transactionType.Income));
if (string.IsNullOrWhiteSpace(transactionType.Description))
{
sqlParameterCollection.Add(new SqlParameter("@Description", DBNull.Value));
}
else
{
sqlParameterCollection.Add(new SqlParameter("@Description", transactionType.Description));
}
if (string.IsNullOrWhiteSpace(transactionType.Name))
{
sqlParameterCollection.Add(new SqlParameter("@Name", DBNull.Value));
}
else
{
sqlParameterCollection.Add(new SqlParameter("@Name", transactionType.Name));
}
if (string.IsNullOrWhiteSpace(transactionType.Color))
{
sqlParameterCollection.Add(new SqlParameter("@Color", DBNull.Value));
}
else
{
sqlParameterCollection.Add(new SqlParameter("@Color", transactionType.Color));
}
if (transactionType.Id != 0)
{
sqlParameterCollection.Add(new SqlParameter("@Id", transactionType.Id));
SqlService.ExecuteNonQuery(query, sqlParameterCollection.ToArray());
return(transactionType.Id);
}
else
{
return(SqlService.ExecuteScalar(query, sqlParameterCollection.ToArray()));
}
}