How do I solve this..problem ..

I made som updates to the form code to emphesize how to make selections with LINQ (this is NOT SQL, even if the syntax is similar).

I added some LINQ code to filter, I I put that code in the transfer of data fråm the "database"-objects to the "report"-objects.


I added the method

LoadDataFromDatabase()

that simulates the database request. But i real code you should really make the major selection by having a very tight SQL query. The information from the database that you dont need, should NEVER be fetched at all.

This is not optimized at all, but only a way of showing how some of the functionality works with LINQ and how to build a Pivot table out of live data, in a relatively simple way.

all data is also hard coded, but this is only to make this sample code simple to understand, for a novice developer. In real life you whould NEVER write code this way.

make sure you learn how to use Entity framework, for accessing the database, and retriving data from it. Entity Framework utilizes LINQ.

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Windows.Forms;

// I have a table with order details which contains a datetime column called
// orderdate.Now I want to display the count of specific materials ordered
// month wise as below.
// item nov 17  dec 17  jan 18  feb 18
// item1 5       3       6       3
// item2 6       2       7       2
// 
// but my condition is tht I have another table as below.count should be shown
// in the above datagriview for the groups in the second table as shown below.
// 
// Month_field from      to
// Nov 17      01-Nov-17 30-Nov-17
// Dec 17      01-Dec-17 31-Dec-17
// Jan 18      01-Jan-18 31-Jan-18
// Feb 18      01-Feb-18 28-Feb-18


namespace SalesReportApp
{
    public partial class Form1 : Form
    {
        // This is the original data from the database, that should be 
        // filtered with the SQL query before you load it in theis 
        // reporting application, otherwise you load of completly useless 
        // info, this affects the performance emencly...
        private List<Product> databaseProducts;
        private List<ReportPeriod> databaseReportPeriods;
        private List<SalesReport> databaseSalesReports;

        // This is the active filtered data the you gonna display in your report
        private List<Product> products;
        private List<ReportPeriod> reportPeriods;
        private List<SalesReport> salesReports;
        private List<ReportDataRow> reportdataSource;

        public Form1()
        {
            InitializeComponent();
        }

        private void DefaultInitializers()
        {
            // This is the original data from the database, that should be 
            // filtered with the SQL query before you load it in theis 
            // reporting application, otherwise you load of completly useless 
            // info, this affects the performance emencly...
            databaseProducts = new List<Product>();
            databaseReportPeriods = new List<ReportPeriod>();
            databaseSalesReports = new List<SalesReport>();

            // This is the active filtered data the you gonna display in your report
            products = new List<Product>();
            reportPeriods = new List<ReportPeriod>();
            salesReports = new List<SalesReport>();
            reportdataSource = new List<ReportDataRow>();
        }



        private void LoadDataFromDatabase()
        {
            // **************************
            // ***** ADING PRODUCTS *****
            // **************************
            databaseProducts.Clear();
            databaseProducts.Add(new Product(productName: "Item1"));
            databaseProducts.Add(new Product(productName: "Item2"));
            databaseProducts.Add(new Product(productName: "Item3"));
            databaseProducts.Add(new Product(productName: "Item4"));


            // *********************************
            // ***** ADDING REPORT PERIODS *****
            // *********************************
            databaseReportPeriods.Clear();
            databaseReportPeriods.Add(new ReportPeriod(Month_field: "Jul 17", from: "2017-07-01", to: "2017-07-31"));
            databaseReportPeriods.Add(new ReportPeriod(Month_field: "Aug 17", from: "2017-08-01", to: "2017-08-31"));
            databaseReportPeriods.Add(new ReportPeriod(Month_field: "Sep 17", from: "2017-09-01", to: "2017-09-30"));
            databaseReportPeriods.Add(new ReportPeriod(Month_field: "Oct 17", from: "2017-10-01", to: "2017-10-31"));
            databaseReportPeriods.Add(new ReportPeriod(Month_field: "Nov 17", from: "2017-11-01", to: "2017-11-30"));
            databaseReportPeriods.Add(new ReportPeriod(Month_field: "Dec 17", from: "2017-12-01", to: "2017-12-31"));
            databaseReportPeriods.Add(new ReportPeriod(Month_field: "Jan 18", from: "2018-01-01", to: "2018-01-31"));
            databaseReportPeriods.Add(new ReportPeriod(Month_field: "Feb 18", from: "2018-02-01", to: "2018-02-28"));


            // ********************************
            // ***** ADDING SALES REPORTS *****
            // ********************************
            databaseSalesReports.Clear();

            // Sales for Jul 17
            databaseSalesReports.Add(new SalesReport(Item: "Item1", Month_field: "Jul 17", SalesCount: 5));
            databaseSalesReports.Add(new SalesReport(Item: "Item2", Month_field: "Jul 17", SalesCount: 6));
            databaseSalesReports.Add(new SalesReport(Item: "Item3", Month_field: "Jul 17", SalesCount: 9));
            databaseSalesReports.Add(new SalesReport(Item: "Item4", Month_field: "Jul 17", SalesCount: 12));

            // Sales for Aug 17
            databaseSalesReports.Add(new SalesReport(Item: "Item1", Month_field: "Aug 17", SalesCount: 3));
            databaseSalesReports.Add(new SalesReport(Item: "Item2", Month_field: "Aug 17", SalesCount: 2));
            databaseSalesReports.Add(new SalesReport(Item: "Item3", Month_field: "Aug 17", SalesCount: 13));
            databaseSalesReports.Add(new SalesReport(Item: "Item4", Month_field: "Aug 17", SalesCount: 14));

            // Sales for Sep 17
            databaseSalesReports.Add(new SalesReport(Item: "Item1", Month_field: "Sep 17", SalesCount: 6));
            databaseSalesReports.Add(new SalesReport(Item: "Item2", Month_field: "Sep 17", SalesCount: 7));
            databaseSalesReports.Add(new SalesReport(Item: "Item3", Month_field: "Sep 17", SalesCount: 15));
            databaseSalesReports.Add(new SalesReport(Item: "Item4", Month_field: "Sep 17", SalesCount: 16));

            // Sales for Oct 17
            databaseSalesReports.Add(new SalesReport(Item: "Item1", Month_field: "Oct 17", SalesCount: 3));
            databaseSalesReports.Add(new SalesReport(Item: "Item2", Month_field: "Oct 17", SalesCount: 2));
            databaseSalesReports.Add(new SalesReport(Item: "Item3", Month_field: "Oct 17", SalesCount: 17));
            databaseSalesReports.Add(new SalesReport(Item: "Item4", Month_field: "Oct 17", SalesCount: 18));



            // Sales for Nov 17
            databaseSalesReports.Add(new SalesReport(Item: "Item1", Month_field: "Nov 17", SalesCount: 5));
            databaseSalesReports.Add(new SalesReport(Item: "Item2", Month_field: "Nov 17", SalesCount: 6));

            // Sales for Dec 17
            databaseSalesReports.Add(new SalesReport(Item: "Item1", Month_field: "Dec 17", SalesCount: 3));
            databaseSalesReports.Add(new SalesReport(Item: "Item2", Month_field: "Dec 17", SalesCount: 2));

            // Sales for Jan 18
            databaseSalesReports.Add(new SalesReport(Item: "Item1", Month_field: "Jan 18", SalesCount: 6));
            databaseSalesReports.Add(new SalesReport(Item: "Item2", Month_field: "Jan 18", SalesCount: 7));

            // Sales for Feb 18
            databaseSalesReports.Add(new SalesReport(Item: "Item1", Month_field: "Feb 18", SalesCount: 3));
            databaseSalesReports.Add(new SalesReport(Item: "Item2", Month_field: "Feb 18", SalesCount: 2));
        }


        private void Form1_Load(object sender, EventArgs e)
        {
            // Initialize objects
            DefaultInitializers();

            // Load data from database....
            // Filter as much as possible at the SQL-level to maximize efficency
            LoadDataFromDatabase();

            // ******************************************************
            // ***** ADING UNFILTERED PRODUCTS TO ACTUAL REPORT *****
            // ******************************************************
            products.Clear();

            // Here we dont do any selection at all, we just swallow the entire list of products
            products.AddRange(databaseProducts);

            // ***********************************************************
            // ***** ADDING FILTERED REPORT PERIODS TO ACTUAL REPORT *****
            // ***********************************************************
            reportPeriods.Clear();

            // here we use LINQ to make a selection of input data
            reportPeriods.AddRange(databaseReportPeriods.Where(p => p.from>=DateTime.Parse("2017-11-01") && p.to <= DateTime.Parse("2018-02-28") ));

            // **********************************************************
            // ***** ADDING FILTERED SALES REPORTS TO ACTUAL REPORT *****
            // **********************************************************
            salesReports.Clear();

            // Here we filter manually, by looping throug the incoming data and select the individual records we want
            // This is just one possible method to make your selection of toples(rows) from the database
            // LINQ is very powerful, if you spend some time learning how it works.
            // If you use Entity Framework, you have the possibility to use the LINQ, syntax directly to the database.
            //salesReports.Add(databaseSalesReports.Where(r => r.Month_field == period.Month_field));
            foreach (var period in reportPeriods)
            {
                //var sr2 = databaseReportPeriods.Where(r => r.Month_field == period.Month_field);

                var sr3 = databaseSalesReports.Where(r => r.Month_field == period.Month_field);

                foreach (SalesReport sr in sr3)
                {
                    // SalesReport salesReport = new SalesReport(Item:sr.Item, Month_field:sr.Month_field, SalesCount:sr.SalesCount);
                    salesReports.Add(sr);
                }
            }


            // Add rows to dataGridView
            reportdataSource.Clear();
            foreach (Product prod in products)
            {
                // Work on 1 Item at a time....
                List<SalesReport> salesReportSubList = new List<SalesReport>();
                foreach (var salesItem in salesReports.Where(i => i.Item == prod.productName))
                {
                    salesReportSubList.Add((SalesReport)salesItem);
                }

                // Fill cell values with fresh data....
                ReportDataRow reportDataRow = new ReportDataRow();

                // This is a very pad solution but it probably work anyway
                for (int columnIndex = 0; columnIndex < reportPeriods.Count; columnIndex++)
                {
                    var sr = salesReportSubList.SingleOrDefault(r => r.Item == prod.productName && r.Month_field == reportPeriods[columnIndex].Month_field);
                    if (sr != null)
                    {
                        // Get sales info for product
                        // int sales = salesReportSubList[columnIndex].SalesCount;
                        if (columnIndex == 0) reportDataRow.Month1 = sr.SalesCount;
                        if (columnIndex == 1) reportDataRow.Month2 = sr.SalesCount;
                        if (columnIndex == 2) reportDataRow.Month3 = sr.SalesCount;
                        if (columnIndex == 3) reportDataRow.Month4 = sr.SalesCount;
                    }
                }
                // Add row to datasource
                reportDataRow.Product = prod.productName;
                reportdataSource.Add(reportDataRow);
            }

            // Data binding populates DataGridView, with columns and data rows (Tuples).

            // Without filtering
            dataGridView1.DataSource = reportdataSource;
            
            // With filtering through LINQ expression
            //dataGridView1.DataSource = reportdataSource.Where(r => r.Product == "Item1");

            // Change Column headers after databinding is done
            for (int columnIndex=0; columnIndex< reportPeriods.Count; columnIndex++)
            {
                dataGridView1.Columns[columnIndex+1].HeaderText = reportPeriods[columnIndex].Month_field;
            }


        }
    }
}