public IEnumerable <DTOShipmentsViewModel> GetShipments(DTOGroupingShipsmentsViewModel dtoGroupingShipsmentsViewModel = null)
{
string query;
if (dtoGroupingShipsmentsViewModel == null)
{
query = "SELECT S.Id, S.ShipmentDate,S.Company,S.City,S.Country, A.Surname+' '+A.Name as SurnameName, A.UserName as Login, S.Quantity,S.Sum FROM Shipments S INNER JOIN AspNetUsers A ON S.Manager_Id = A.Id";
}
else
{
StringBuilder queryBegin = new StringBuilder("SELECT ");
StringBuilder queryEnd = new StringBuilder(" FROM Shipments S INNER JOIN AspNetUsers A ON S.Manager_Id=A.Id GROUP BY ");
if (dtoGroupingShipsmentsViewModel.Date)
{
queryBegin.Append("CAST(S.ShipmentDate AS date) as ShipmentDate, ");
queryEnd.Append("CAST(S.ShipmentDate AS date),");
}
if (dtoGroupingShipsmentsViewModel.Company)
{
queryBegin.Append("S.Company, ");
queryEnd.Append("S.Company,");
}
if (dtoGroupingShipsmentsViewModel.City)
{
queryBegin.Append("S.City, ");
queryEnd.Append("S.City,");
}
if (dtoGroupingShipsmentsViewModel.Country)
{
queryBegin.Append("S.Country, ");
queryEnd.Append("S.Country,");
}
if (dtoGroupingShipsmentsViewModel.SurnameName)
{
queryBegin.Append("A.Surname+' '+A.Name as SurnameName, ");
queryEnd.Append("A.Surname+' '+A.Name,");
}
query = queryBegin.Append("Sum(S.Quantity) as Quantity,Sum(S.Sum) as Sum").ToString() + queryEnd.Remove(queryEnd.Length - 1, 1).ToString();
}
var shipments = Database.Shipments.GetShipments(query);
List <DTOShipmentsViewModel> dtoShipmentsViewModel = new List <DTOShipmentsViewModel>();
foreach (var item in shipments)
{
dtoShipmentsViewModel.Add(new DTOShipmentsViewModel()
{
Id = item.Id,
ShipmentDate = item.ShipmentDate,
Company = item.Company,
City = item.City,
Country = item.Country,
SurnameName = item.SurnameName,
Login = item.Login,
Quantity = item.Quantity,
Sum = item.Sum
});
}
return(dtoShipmentsViewModel);
}
private void GroupButton_Click(object sender, EventArgs e)
{
GroupButton.Visible = false;
DTOGroupingShipsmentsViewModel dtoGroupingShipsmentsViewModel = new DTOGroupingShipsmentsViewModel
{
Date = DateCheckBox.Checked,
Company = CompanyCheckBox.Checked,
City = CityCheckBox.Checked,
Country = CountryCheckBox.Checked,
SurnameName = SurnameCheckBox.Checked
};
ShipmentsGrid.DataSource = null;
var responce = ShipmentService.GetShipments(dtoGroupingShipsmentsViewModel);
ShipmentsGrid.DataSource = responce;
if (responce.First().ShipmentDate.ToString() == "01.01.0001 0:00:00")
{
ShipmentsGrid.Columns["ShipmentDate"].Visible = false;
}
if (responce.First().Company == null)
{
ShipmentsGrid.Columns["Company"].Visible = false;
}
if (responce.First().City == null)
{
ShipmentsGrid.Columns["City"].Visible = false;
}
if (responce.First().Country == null)
{
ShipmentsGrid.Columns["Country"].Visible = false;
}
if (responce.First().SurnameName == null)
{
ShipmentsGrid.Columns["SurnameName"].Visible = false;
}
SetupGrid();
}
