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(); }