/// <summary>
/// Возвращает информацию о книгах
/// </summary>
/// <param name="filter">Объект фильтра</param>
/// <param name="address">Электронный адресс клиента</param>
/// <returns>Информация о книгах</returns>
public DataSet GetBook(FilterSDB filter, string address)
{
DataSet dataSetBook = new DataSet();
Command = new MySqlCommand();
Filter = filter;
Address = address;
string sqlWhere = "";
AddConditionBook(ref sqlWhere);
Command.CommandText = $"SELECT * FROM books{sqlWhere}";
try
{
ConnectionMySQL.Open();
Command.Connection = ConnectionMySQL;
MySqlDataAdapter adapter = new MySqlDataAdapter(Command);
adapter.Fill(dataSetBook);
}
catch (Exception e)
{
Log.Error(e.ToString);
throw new FaultException <MySqlException>(new MySqlException());
}
finally
{
ConnectionMySQL.Close();
}
return(dataSetBook);
}
/// <summary>
/// Возвращает статистику клиента
/// </summary>
/// <param name="filter">Объект фильтра</param>
/// <returns>Статистика клиента</returns>
public DataSet GetClientStatistics(FilterSDB filter)
{
DataSet dataSetBook = new DataSet();
Command = new MySqlCommand();
Filter = filter;
string sqlWhereBook = "";
AddConditionClientStatisticsByBook(ref sqlWhereBook);
string sqlWhereClient = "";
AddConditionClientStatistics(ref sqlWhereClient);
Command.CommandText = "SELECT Address, COUNT(b.Name) AS CountBook, SUM(Pages) AS CountPages FROM " +
"(SELECT DISTINCT Address FROM clients AS c " +
"JOIN levellanguages AS ll " +
"ON c.Address = ll.AddressClient" +
$"{sqlWhereClient}) AS Addr " +
"LEFT JOIN (SELECT * FROM books" +
$"{sqlWhereBook}) AS b " +
"ON Addr.Address = b.AddressClient " +
"GROUP BY Address";
try
{
ConnectionMySQL.Open();
Command.Connection = ConnectionMySQL;
MySqlDataAdapter adapter = new MySqlDataAdapter(Command);
adapter.Fill(dataSetBook);
}
catch (Exception e)
{
Log.Error(e.ToString);
throw new FaultException <MySqlException>(new MySqlException());
}
finally
{
ConnectionMySQL.Close();
}
return(dataSetBook);
}
/// <summary>
/// Возвращает информацию о клиенте в 2 таблицах
/// </summary>
/// <param name="address">Электронный адресс клиента</param>
/// <returns>Информация о клиенте</returns>
public DataSet GetClientInfo(string address)
{
string sql;
DataSet dataSetClient = new DataSet();
dataSetClient.Tables.Add();
dataSetClient.Tables.Add();
for (int i = 0; i <= 1; i++)
{
if (i == 0)
{
sql = "SELECT * FROM clients WHERE Address = @Address";
}
else
{
sql = "SELECT * FROM LevelLanguages WHERE AddressClient = @Address";
}
try
{
ConnectionMySQL.Open();
Command = new MySqlCommand(sql, ConnectionMySQL);
Command.Parameters.AddWithValue("@Address", address);
MySqlDataAdapter adapter = new MySqlDataAdapter(Command);
adapter.Fill(dataSetClient.Tables[i]);
}
catch (Exception e)
{
Log.Error(e.ToString);
throw new FaultException <MySqlException>(new MySqlException());
}
finally
{
ConnectionMySQL.Close();
}
}
return(dataSetClient);
}