136 lines
6.3 KiB
C#
136 lines
6.3 KiB
C#
using Microsoft.Extensions.Configuration;
|
|
using MySqlConnector;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using System.Threading.Tasks;
|
|
using uWine.Core.Domain.Entities;
|
|
using uWine.Core.Domain.Repositories;
|
|
|
|
namespace uWine.Core.Infrastructure.Repository
|
|
{
|
|
public class MariadbOwnerRepository : MariaDbServerRepository, IOwnerRepository
|
|
{
|
|
public MariadbOwnerRepository(IConfiguration configuration) : base(configuration)
|
|
{
|
|
|
|
}
|
|
|
|
public void Insert(Owner owner)
|
|
{
|
|
using (var connection = Connection())
|
|
{
|
|
connection.Open();
|
|
var cmd = new MySqlCommand($@"INSERT INTO t_owners (
|
|
first_name,
|
|
last_name,
|
|
company_name,
|
|
note
|
|
) VALUES (
|
|
@firstName,
|
|
@lastName,
|
|
@companyName,
|
|
@note )", connection);
|
|
cmd.Parameters.AddWithValue("@firstName", owner.Name);
|
|
cmd.Parameters.AddWithValue("@lastName", owner.Surname);
|
|
cmd.Parameters.AddWithValue("@companyName", owner.CompanyName);
|
|
cmd.Parameters.AddWithValue("@note", owner.Note);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
}
|
|
|
|
public void AddAddress(int ownerId, Address address)
|
|
{
|
|
using (var connection = Connection())
|
|
{
|
|
connection.Open();
|
|
using var cmd = new MySqlCommand($@"INSERT INTO t_addresses (
|
|
owner_id,
|
|
country,
|
|
postal_code,
|
|
county,
|
|
province,
|
|
city,
|
|
street,
|
|
house_number,
|
|
latitude,
|
|
longitude,
|
|
active
|
|
) VALUES (
|
|
@ownerId,
|
|
@country,
|
|
@postal_code,
|
|
@county,
|
|
@province,
|
|
@city,
|
|
@street,
|
|
@house_number,
|
|
@latitude,
|
|
@longitude,
|
|
@active ); LAST_INSERT_ID();", connection);
|
|
cmd.Parameters.AddWithValue("@ownerId", ownerId);
|
|
cmd.Parameters.AddWithValue("@country", address.Country);
|
|
cmd.Parameters.AddWithValue("@postal_code", address.PostalCode);
|
|
cmd.Parameters.AddWithValue("@county", address.County);
|
|
cmd.Parameters.AddWithValue("@province", address.Province);
|
|
cmd.Parameters.AddWithValue("@city", address.City);
|
|
cmd.Parameters.AddWithValue("@street", address.Street);
|
|
cmd.Parameters.AddWithValue("@house_number", address.HouseNumber);
|
|
cmd.Parameters.AddWithValue("@latitude", address.Latitude);
|
|
cmd.Parameters.AddWithValue("@longitude", address.Longitude);
|
|
cmd.Parameters.AddWithValue("@active", address.Active ? 1 : 0);
|
|
using (var reader = cmd.ExecuteReader())
|
|
{
|
|
if (reader.Read())
|
|
{
|
|
address.Id = reader.GetInt32(0);
|
|
}
|
|
}
|
|
|
|
using var cmd2 = new MySqlCommand("INSERT INTO t_owners_addresses (owner_id, address_id) VALUES (@ownerId, @addressId)", connection);
|
|
cmd2.Parameters.AddWithValue("@ownerId", ownerId);
|
|
cmd2.Parameters.AddWithValue("@addressId", address.Id);
|
|
cmd2.ExecuteNonQuery();
|
|
}
|
|
}
|
|
|
|
public Owner Simple(int ownerId)
|
|
{
|
|
using (var connection = Connection())
|
|
{
|
|
connection.Open();
|
|
using var cmd = new MySqlCommand("SELECT * FROM t_owners WHERE id = @id", connection);
|
|
cmd.Parameters.AddWithValue("@id", ownerId);
|
|
using (var reader = cmd.ExecuteReader())
|
|
{
|
|
if (reader.Read())
|
|
{
|
|
var owner = Owner.FromRecord(reader);
|
|
}
|
|
}
|
|
|
|
}
|
|
return new Owner();
|
|
}
|
|
|
|
public List<Owner> FromUser(int userId)
|
|
{
|
|
var list = new List<Owner>();
|
|
using (var connection = Connection()) {
|
|
connection.Open();
|
|
var cmd = new MySqlCommand("SELECT owner_id as id FROM t_user_owner WHERE user_id = @userId",connection);
|
|
cmd.Parameters.AddWithValue("userId", userId);
|
|
using (var reader = cmd.ExecuteReader())
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
list.Add(Simple(reader.GetInt32("id")));
|
|
}
|
|
}
|
|
}
|
|
return list;
|
|
}
|
|
}
|
|
}
|