/// <summary>
/// Gets the company employee count.
/// </summary>
/// <param name="customerId">The customer identifier.</param>
/// <param name="companyId">The company identifier.</param>
/// <returns></returns>
public Optional <CompanyEmployeeCount> GetCompanyEmployeeCount(int customerId, int companyId)
{
using (var connection = GetOpenedSqlConnection2()) {
var cmd = new SelectWhereGenerator <CompanyEmployeeCount>()
.WithOptionalConnection(connection.SqlConnection())
.WithTableName(Tables.CompanyEmployeeCount)
.WithWhere(o => o.CompanyId, companyId)
.WithWhere(o => o.CustomerId, customerId)
.Verify()
.GenerateCommand();
using (var sqlCommand = cmd) {
return(CreateModel <CompanyEmployeeCount>(sqlCommand.ExecuteReader()));
}
}
}
/// <summary>
/// Gets the customer addresses.
/// </summary>
/// <param name="customerId">The customer identifier.</param>
/// <returns></returns>
public IEnumerable <CustomerAddress> GetCustomerAddresses(int customerId)
{
using (var connection = GetOpenedSqlConnection2()) {
var cmd = new SelectWhereGenerator <CustomerAddress>()
.WithOptionalConnection(connection.SqlConnection())
.WithTableName(Tables.CustomerAddress)
.WithSelect()
.WithWhere(o => o.CustomerId, customerId)
.Verify()
.GenerateCommand();
using (var sqlCommand = cmd) {
return(CreateModels <CustomerAddress>(sqlCommand.ExecuteReader()));
}
}
}
/// <summary>
/// Gets the customer partially by identifier.
/// </summary>
/// <param name="customerId">The customer identifier.</param>
/// <param name="selectProperties">The selectProperties.</param>
/// <returns></returns>
public Customer GetCustomerPartiallyById(int customerId, params Expression <Func <Customer, object> >[] selectProperties)
{
using (var connection = GetOpenedSqlConnection2()) {
var cmd = new SelectWhereGenerator <Customer>()
.WithTableName(Tables.Customer)
.WithOptionalConnection(connection.SqlConnection())
.WithSelect(selectProperties)
.WithWhere(o => o.Id, customerId)
.Verify()
.GenerateCommand();
using (var sqlCommand = cmd) {
return(CreateModel <Customer>(sqlCommand.ExecuteReader()));
}
}
}
/// <summary>
/// Gets the company by identifier.
/// </summary>
/// <param name="companyId">The company identifier.</param>
/// <returns></returns>
public Optional <Company> GetCompanyById(int companyId)
{
using (var connection = GetOpenedSqlConnection2()) {
var cmd = new SelectWhereGenerator <Company>()
.WithOptionalConnection(connection.SqlConnection())
.WithTableName(Tables.Company)
.WithSelect() //select *
.WithWhere(o => o.Id, companyId)
.Verify()
.GenerateCommand();
using (var sqlCommand = cmd) {
return(CreateModel <Company>(sqlCommand.ExecuteReader()));
}
}
}
/// <summary>
/// Gets the customer's requested loan.
/// </summary>
/// <param name="customerId">The customer identifier.</param>
/// <returns></returns>
public Optional <CustomerRequestedLoan> GetCustomerRequestedLoan(int customerId)
{
using (var connection = GetOpenedSqlConnection2()) {
var cmd = new SelectWhereGenerator <CustomerRequestedLoan>()
.WithOptionalConnection(connection.SqlConnection())
.WithTableName(Tables.CustomerRequestedLoan)
.WithSelect() //select *
.WithWhere(o => o.CustomerId, customerId)
.Verify()
.GenerateCommand();
using (var sqlCommand = cmd) {
return(CreateModel <CustomerRequestedLoan>(sqlCommand.ExecuteReader()));
}
}
}
/// <summary>
/// Determines whether there is a broker with specified emailAddress
/// </summary>
/// <param name="emailAddress">The email address.</param>
/// <returns></returns>
public bool IsExistsBroker(string emailAddress)
{
using (var connection = GetOpenedSqlConnection2()) {
var cmd = new SelectWhereGenerator <Broker>()
.WithOptionalConnection(connection.SqlConnection())
.WithTableName(Tables.Broker)
.WithSelect(o => o.BrokerID)
.WithWhere(o => o.ContactEmail, emailAddress)
.Verify()
.GenerateCommand();
using (var sqlCommand = cmd) {
int id = (int)ExecuteScalarAndLog <int>(cmd);
return(id > 0);
}
}
}
/// <summary>
/// Determines whether the user exists.
/// </summary>
/// <param name="emailAddress">The email address.</param>
/// <param name="password">The password.</param>
/// <returns></returns>
public bool IsUserExists(string emailAddress, string password)
{
using (var connection = GetOpenedSqlConnection2()) {
var cmd = new SelectWhereGenerator <SecurityUser>()
.WithOptionalConnection(connection.SqlConnection())
.WithTableName(Tables.SecurityUser)
.WithSelect(o => o.UserId)
.WithWhere(o => o.EMail, emailAddress)
.WithWhere(o => o.EzPassword, password)
.Verify()
.GenerateCommand();
using (var sqlCommand = cmd) {
int id = (int)ExecuteScalarAndLog <int>(sqlCommand);
return(id > 0);
}
}
}
/// <summary>
/// Gets user identifier by user name.
/// </summary>
/// <param name="userName">Name of the user.</param>
/// <returns></returns>
public Optional <int> GetUserIdByUserName(string userName)
{
using (var connection = GetOpenedSqlConnection2()) {
var cmd = new SelectWhereGenerator <SecurityUser>()
.WithOptionalConnection(connection.SqlConnection())
.WithTableName(Tables.SecurityUser)
.WithSelect(o => o.UserId)
.WithWhere(o => o.UserName, userName)
.WithWhere(o => o.IsDeleted, false)
.Verify()
.GenerateCommand();
using (SqlCommand sqlCommand = cmd) {
return(ExecuteScalarAndLog <int>(sqlCommand));
}
}
}
/// <summary>
/// Gets the directors.
/// </summary>
/// <param name="customerId">The customer identifier.</param>
/// <param name="companyId">The company identifier.</param>
/// <returns></returns>
public Optional <IEnumerable <Director> > GetDirectors(int customerId, int companyId)
{
using (var connection = GetOpenedSqlConnection2()) {
var cmd = new SelectWhereGenerator <Director>()
.WithOptionalConnection(connection.SqlConnection())
.WithTableName(Tables.Director)
.WithSelect() //select *
.WithWhere(o => o.CustomerId, customerId)
.WithWhere(o => o.CompanyId, companyId)
.Verify()
.GenerateCommand();
using (var sqlCommand = cmd) {
return(CreateModels <Director>(sqlCommand.ExecuteReader())
.AsOptional());
}
}
}
/// <summary>
/// Gets the type of the company business.
/// </summary>
/// <param name="companyId">The company identifier.</param>
/// <returns></returns>
public Optional <TypeOfBusiness> GetCompanyBusinessType(int companyId)
{
using (var connection = GetOpenedSqlConnection2()) {
var cmd = new SelectWhereGenerator <Company>()
.WithOptionalConnection(connection.SqlConnection())
.WithTableName(Tables.Company)
.WithSelect(o => o.TypeOfBusiness)
.WithWhere(o => o.Id, companyId)
.Verify()
.GenerateCommand();
using (var sqlCommand = cmd) {
var res = ExecuteScalarAndLog <string>(sqlCommand);
if (res.HasValue)
{
return((TypeOfBusiness)Enum.Parse(typeof(TypeOfBusiness), res.Value));
}
return(Optional <TypeOfBusiness> .Empty());
}
}
}
