private void PopulateOrderReportItem(IDataReader reader, OrderReportItem orderReportItem)
{
orderReportItem.OrderId = reader["orderId"].ToString();
orderReportItem.ClientName = reader["clientName"].ToString();
orderReportItem.ProductName = reader["productName"].ToString();
orderReportItem.OrderDate = DateTime.Parse(reader["orderDate"].ToString());
orderReportItem.Quantity = int.Parse(reader["quantity"].ToString());
orderReportItem.Total = double.Parse(reader["total"].ToString());
}
public bool CreateCsv(string clientId)
{
ClientRepository clientRepository = new ClientRepository(_context);
Client client = clientRepository.FindById(clientId);
string docPath = ConfigurationManager.AppSettings["CsvPath"];
docPath = string.Concat(docPath, client.Name.Trim(), ".csv");
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT o.orderId");
sql.AppendLine(",c.name AS clientName");
sql.AppendLine(",p.name AS productName");
sql.AppendLine(",p.value AS productValue");
sql.AppendLine(",o.orderDate");
sql.AppendLine(",o.quantity");
sql.AppendLine(",(o.quantity * p.value) AS total");
sql.AppendLine("FROM orders o");
sql.AppendLine("LEFT JOIN products p");
sql.AppendLine("ON p.id=o.productId");
sql.AppendLine("LEFT JOIN clients c");
sql.AppendLine("ON c.id=o.clientId");
sql.AppendLine("WHERE o.clientId=@clientId");
MySqlCommand command = new MySqlCommand(sql.ToString(), _context);
command.Parameters.Add("@clientId", MySqlDbType.VarChar).Value = clientId;
List <OrderReportItem> reportItems = new List <OrderReportItem>();
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
OrderReportItem item = new OrderReportItem();
PopulateOrderReportItem(reader, item);
reportItems.Add(item);
}
}
StringBuilder csvBuilder = new StringBuilder();
csvBuilder.AppendLine("Código Pedido;Nome Cliente; NomeProduto; Data Pedido; Quantidade Pedido; Valor Produto; Valor Total");
foreach (OrderReportItem item in reportItems)
{
string formatedDate = item.OrderDate.ToString("dd/MM/yyyy");
string formatedProductValue = item.ProductValue.ToString("N3");
string formatedTotal = item.Total.ToString("N3");
csvBuilder.AppendFormat("{0};{1};{2};{3};{4};{5};{6}", item.OrderId, item.ClientName, item.ProductName, formatedDate, item.Quantity, formatedProductValue, formatedTotal).AppendLine();
}
try
{
File.WriteAllText(docPath, csvBuilder.ToString());
}
catch (InvalidCastException err)
{
return(false);
}
return(true);
}
