public async Task <IActionResult> Post([FromBody] UserPaymentType userPaymentType)
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"
INSERT INTO UserPaymentType (AcctNumber, Active, CustomerId, PaymentTypeId)
OUTPUT INSERTED.Id
VALUES (@AcctNumber, @Active, @CustomerId, @PaymentTypeId)";
cmd.Parameters.Add(new SqlParameter("@AcctNumber", userPaymentType.AcctNumber));
cmd.Parameters.Add(new SqlParameter("@Active", userPaymentType.Active));
cmd.Parameters.Add(new SqlParameter("@CustomerId", userPaymentType.CustomerId));
cmd.Parameters.Add(new SqlParameter("@PaymentTypeId", userPaymentType.PaymentTypeId));
int id = (int)cmd.ExecuteScalar();
userPaymentType.Id = id;
//return CreatedAtRoute("GetUserPaymentTypes", new { id = id }, userPaymentType);
return(new StatusCodeResult(StatusCodes.Status204NoContent));
}
}
}
public async Task <IActionResult> Get([FromQuery] Int32?customerId)
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"SELECT upt.Id, upt.CustomerId, upt.PaymentTypeId, upt.AcctNumber, upt.active
FROM UserPaymentType upt
WHERE upt.CustomerId = @customerId";
cmd.Parameters.Add(new SqlParameter("@customerId", customerId));
SqlDataReader reader = cmd.ExecuteReader();
UserPaymentType userPaymentType = null;
List <UserPaymentType> userPaymentTypes = new List <UserPaymentType>();
while (reader.Read())
{
userPaymentType = new UserPaymentType
{
Id = reader.GetInt32(reader.GetOrdinal("Id")),
CustomerId = reader.GetInt32(reader.GetOrdinal("CustomerId")),
PaymentTypeId = reader.GetInt32(reader.GetOrdinal("PaymentTypeId")),
AcctNumber = reader.GetString(reader.GetOrdinal("AcctNumber")),
Active = reader.GetBoolean(reader.GetOrdinal("Active")),
};
userPaymentTypes.Add(userPaymentType);
}
reader.Close();
return(Ok(userPaymentTypes));
}
}
}
public async Task <IActionResult> Get(
[FromQuery] string customerId
)
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
// We dont want to return a full list of all the users payment types so we only want to get one at a type
//based off of their customerId so we have to add the customerId param
if (customerId != null)
{
//because we only want active payment types we only get back where active = 1 (true)
cmd.CommandText = @"
SELECT u.Id, u.AcctNumber, u.Active, u.CustomerId, u.PaymentTypeid
FROM UserPaymentType u
WHERE u.Active = 1
AND u.CustomerId = @id";
cmd.Parameters.Add(new SqlParameter("@Id", customerId));
}
else
{
//if you try to get all the users payment types you will get a forbidden error
return(new StatusCodeResult(StatusCodes.Status403Forbidden));
};
SqlDataReader reader = cmd.ExecuteReader();
List <UserPaymentType> allUserPaymentTypes = new List <UserPaymentType>();
while (reader.Read())
{
var userPaymentType = new UserPaymentType()
{
Id = reader.GetInt32(reader.GetOrdinal("Id")),
AcctNumber = reader.GetString(reader.GetOrdinal("AcctNumber")),
Active = reader.GetBoolean(reader.GetOrdinal("Active")),
CustomerId = reader.GetInt32(reader.GetOrdinal("CustomerId")),
PaymentTypeId = reader.GetInt32(reader.GetOrdinal("PaymentTypeId")),
};
allUserPaymentTypes.Add(userPaymentType);
}
reader.Close();
return(Ok(allUserPaymentTypes));
}
}
}
public async Task <IActionResult> Get(
[FromQuery] int?customerId)
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"
SELECT upt.Id, upt.CustomerId, upt.PaymentTypeId, upt.AcctNumber,upt.Active, c.FirstName, c.LastName
FROM UserPaymentType upt
LEFT JOIN Customer c ON upt.CustomerId = c.Id
WHERE 1 = 1";
if (customerId != null)
{
cmd.CommandText += " AND CustomerId = @customerId";
cmd.Parameters.Add(new SqlParameter("@customerId", customerId));
}
SqlDataReader reader = cmd.ExecuteReader();
List <UserPaymentType> userpaymenttypes = new List <UserPaymentType>();
while (reader.Read())
{
int id = reader.GetInt32(reader.GetOrdinal("Id"));
string acctnumber = reader.GetString(reader.GetOrdinal("AcctNumber"));
bool active = reader.GetBoolean(reader.GetOrdinal("Active"));
int customeridid = reader.GetInt32(reader.GetOrdinal("CustomerId"));
int paymenttypeId = reader.GetInt32(reader.GetOrdinal("PaymentTypeId"));
UserPaymentType userpaymenttype = new UserPaymentType
{
Id = id,
Active = active,
AcctNumber = acctnumber,
CustomerId = customeridid,
PaymentTypeId = paymenttypeId
};
userpaymenttypes.Add(userpaymenttype);
}
reader.Close();
return(Ok(userpaymenttypes));
}
}
}
public async Task <IActionResult> Get([FromQuery] int?customerId)
{
if (!String.IsNullOrWhiteSpace(customerId.ToString()))
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @" SELECT Id, AcctNumber, Active, CustomerId, PaymentTypeId From UserPaymentType Where CustomerId = @customerId And Active = 1 ";
cmd.Parameters.Add(new SqlParameter("@customerId", customerId));
SqlDataReader reader = cmd.ExecuteReader();
UserPaymentType userPaymentType = null;
List <UserPaymentType> userpayments = new List <UserPaymentType>();
while (reader.Read())
{
int currentCustomerId = reader.GetInt32(reader.GetOrdinal("CustomerId"));
//UserPaymentType newUserPaymentType = userpayments.FirstOrDefau(i => i.Id == currentCustomerId);
if (isUserPaymentType(reader.GetInt32(reader.GetOrdinal("Id"))))
{
userPaymentType = new UserPaymentType
{
Id = reader.GetInt32(reader.GetOrdinal("Id")),
CustomerId = currentCustomerId,
PaymentTypeId = reader.GetInt32(reader.GetOrdinal("PaymentTypeId")),
AcctNumber = reader.GetString(reader.GetOrdinal("AcctNumber")),
Active = reader.GetBoolean(reader.GetOrdinal("Active")),
};
userpayments.Add(userPaymentType);
}
if (userPaymentType == null)
{
return(NotFound($"No user payment type found with id of {customerId} "));
}
;
}
reader.Close();
return(Ok(userpayments));
}
}
}
return(Ok());
}
public async Task <IActionResult> GET(
[FromQuery] int?customerId
)
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"
SELECT Id, AcctNumber, Active, CustomerId, PaymentTypeId
FROM UserPaymentType
WHERE 1 = 1
";
if (customerId != null)
{
cmd.CommandText += " AND CustomerId LIKE @customerId";
cmd.Parameters.Add(new SqlParameter("@customerId", customerId));
}
;
SqlDataReader reader = cmd.ExecuteReader();
var userPaymentTypes = new List <UserPaymentType>();
while (reader.Read())
{
if (reader.GetBoolean(reader.GetOrdinal("Active")) == true)
{
var userPaymentType = new UserPaymentType
{
Id = reader.GetInt32(reader.GetOrdinal("Id")),
AcctNumber = reader.GetString(reader.GetOrdinal("AcctNumber")),
Active = reader.GetBoolean(reader.GetOrdinal("Active")),
CustomerId = reader.GetInt32(reader.GetOrdinal("CustomerId")),
PaymentTypeId = reader.GetInt32(reader.GetOrdinal("PaymentTypeId"))
};
userPaymentTypes.Add(userPaymentType);
}
}
reader.Close();
return(Ok(userPaymentTypes));
}
}
}
public async Task <IActionResult> PUT([FromRoute] int id, [FromBody] UserPaymentType userPaymentType)
{
try
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"
UPDATE UserPaymentType
SET
AcctNumber = @acctNumber,
Active = @active,
CustomerId = @customerId,
PaymentTypeId = @paymentTypeId
WHERE Id = @id";
cmd.Parameters.Add(new SqlParameter("@acctNumber", userPaymentType.AcctNumber));
cmd.Parameters.Add(new SqlParameter("@active", userPaymentType.Active));
cmd.Parameters.Add(new SqlParameter("@customerId", userPaymentType.CustomerId));
cmd.Parameters.Add(new SqlParameter("@paymentTypeId", userPaymentType.PaymentTypeId));
cmd.Parameters.Add(new SqlParameter("@id", id));
int rowsAffected = cmd.ExecuteNonQuery();
if (rowsAffected > 0)
{
return(new StatusCodeResult(StatusCodes.Status204NoContent));
}
throw new Exception("No rows were effected");
}
}
}
catch (Exception)
{
if (!UserPaymentTypeExists(id))
{
return(NotFound());
}
else
{
throw;
}
}
}
public async Task <IActionResult> Get([FromRoute] int id)
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"
SELECT Id,
AcctNumber,
Active,
CustomerId,
PaymentTypeId
FROM UserPaymentType
WHERE Id = @id";
cmd.Parameters.Add(new SqlParameter("@id", id));
SqlDataReader reader = await cmd.ExecuteReaderAsync();
UserPaymentType userPaymentType = null;
if (reader.Read())
{
userPaymentType = new UserPaymentType
{
Id = reader.GetInt32(reader.GetOrdinal("Id")),
CustomerId = reader.GetInt32(reader.GetOrdinal("CustomerId")),
PaymentTypeId = reader.GetInt32(reader.GetOrdinal("PaymentTypeId")),
AcctNumber = reader.GetString(reader.GetOrdinal("AcctNumber")),
Active = reader.GetBoolean(reader.GetOrdinal("Active"))
};
}
reader.Close();
if (userPaymentType == null)
{
return(NotFound($"No user payment type found with ID of {id}"));
}
;
return(Ok(userPaymentType));
}
}
}
public async Task <IActionResult> Post([FromBody] UserPaymentType userPaymentType)
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"INSERT INTO UserPaymentType (CustomerId, PaymentTypeId, AcctNumber, Active)
OUTPUT INSERTED.Id
VALUES (@customerId, @paymentTypeId, @acctNumber, @active)";
cmd.Parameters.Add(new SqlParameter("@customerId", userPaymentType.CustomerId));
cmd.Parameters.Add(new SqlParameter("@paymentTypeId", userPaymentType.PaymentTypeId));
cmd.Parameters.Add(new SqlParameter("@acctNumber", userPaymentType.AcctNumber));
cmd.Parameters.Add(new SqlParameter("@active", userPaymentType.Active));
int newId = (int)cmd.ExecuteScalar();
userPaymentType.Id = newId;
return(CreatedAtRoute("GetUserPaymentType", new { id = newId }, userPaymentType));
}
}
}
public async Task <IActionResult> Get([FromQuery] int?customerId)
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"
SELECT
Id, AcctNumber, Active, CustomerId, PaymentTypeId
FROM UserPaymentType
WHERE CustomerId = @CustomerId";
//dylan recording minute 2:45 1/7/20
cmd.Parameters.Add(new SqlParameter("@CustomerId", customerId));
SqlDataReader reader = await cmd.ExecuteReaderAsync();
List <UserPaymentType> userPaymentTypes = new List <UserPaymentType>();
while (reader.Read())
{
UserPaymentType userPaymentType = new UserPaymentType()
{
Id = reader.GetInt32(reader.GetOrdinal("Id")),
CustomerId = reader.GetInt32(reader.GetOrdinal("CustomerId")),
PaymentTypeId = reader.GetInt32(reader.GetOrdinal("PaymentTypeId")),
AcctNumber = reader.GetString(reader.GetOrdinal("AcctNumber")),
Active = reader.GetBoolean(reader.GetOrdinal("Active"))
};
userPaymentTypes.Add(userPaymentType);
}
reader.Close();
return(Ok(userPaymentTypes));
}
}
}
public async Task <IActionResult> Edit([FromRoute] int id, [FromBody] UserPaymentType userPaymenType)
{
try
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"UPDATE UserPaymentType
SET AcctNumber = @acctNumber,
Active = @active WHERE id = @id";
cmd.Parameters.Add(new SqlParameter("@acctNumber", userPaymenType.AcctNumber));
cmd.Parameters.Add(new SqlParameter("@active", userPaymenType.Active));
cmd.Parameters.Add(new SqlParameter("@id", id));
int numberOfRowsEffected = cmd.ExecuteNonQuery();
if (numberOfRowsEffected > 0)
{
return(new StatusCodeResult(StatusCodes.Status204NoContent));
}
throw new Exception("No rows affected");
}
}
}
catch (Exception)
{
if (!isUserPaymentType(id))
{
return(NotFound());
}
else
{
throw;
}
}
}