我该如何解决这个问题..问题.. [英] How do I solve this..problem ..
本文介绍了我该如何解决这个问题..问题..的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个包含订单详细信息的表，其中包含一个名为orderdate的日期时间列。现在我想显示按月订购的特定材料的数量如下。
item nov 17 dec 17 jan 18 feb 18
item1 5 3 6 3
item2 6 &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; 2&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; 7&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; 2 <无线电通信/>
但我的条件是我还有另一张桌子如下。对于第二个表中的组，上面的数据视图中应显示
，如下所示。
month_field ;来自 至
11月17日 01- 11月17日至11月30日
12月17日 01-Dec-17 31 -Dec-17
1月18日 01-Jan-18 31-Jan-18
2月18日 01-Feb-18 28-Feb-18
我尝试过：
试过找不到任何解决方案请帮助
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
What I have tried:
Tried couldn't find any solution. pls help
推荐答案
我想我有一个解决方案，即使它是一个丑陋的解决方案。
首先我创建了你可能拥有的som类，但我需要它们来使我的解决方案正常工作。
I think I have a solution for you, even if it is an ugly one.
First I created som classes that you probably have, but I needed them to get my solution to work properly.
namespace SalesReportApp
{
internal class Product
{
public string productName { get; set; }
public Product(string productName)
{
this.productName = productName;
}
}
}
我还需要为ReportPeriod设一个课程：
I also needed to have a class for ReportPeriod:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace SalesReportApp
{
public class ReportPeriod
{
public string month_field { get; set; }
public DateTime from { get; set; }
public DateTime to { get; set; }
public ReportPeriod()
{
}
public ReportPeriod(string month_field, DateTime from, DateTime to)
{
this.month_field = month_field;
this.from = from;
this.to = to;
}
public ReportPeriod(string month_field, string from, string to)
{
this.month_field = month_field;
this.from = DateTime.Parse(from);
this.to = DateTime.Parse(to);
}
}
}
销售报告包含我们需要的重要信息。 />
The sales report holds the vital information we need.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace SalesReportApp
{
public class SalesReport
{
public string Item { get; set; }
public string Month_field { get; set; }
public int SalesCount { get; set; }
public SalesReport()
{
}
public SalesReport(string Item, string Month_Field, int SalesCount)
{
this.Item = Item;
this.Month_field = Month_field;
this.SalesCount = SalesCount;
}
public override string ToString()
{
return
Item：{Item}，Month：{ Month_field}，Sales：{SalesCount};
}
}
}
"Item:{Item}, Month:{Month_field}, Sales:{SalesCount}" ; } } }
所有销售额我们现在拥有的数据应该在最终报告中一起烹饪：
And all the sales data we now have should bee cooked together in the final report:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace SalesReportApp
{
public class ReportDataRow
{
public string Product { get; set; }
public int Month1 { get; set; }
public int Month2 { get; set; }
public int Month3 { get; set; }
public int Month4 { get; set; }
public ReportDataRow()
{
}
public ReportDataRow(string product, int month1, int month2, int month3, int month4)
{
this.Product = product;
this.Month1 = month1;
this.Month2 = month2;
this.Month3 = month3;
this.Month4 = month4;
}
}
}
我使用了一些我通常从不使用的讨厌伎俩只是重命名列namnes以使他们正确...淘气的技巧.....而这堂课我不是我生命中最美好的时刻之一...但我累了，现在睡觉的路上...... 。
这是Windows窗体中的代码...
I use a little nasty trick the I normally never uses myself to just rename the column namnes to get them right... Naughty trick..... And this class i NOT one of my finest moments in life... but I'm tired and on my way to sleep now....
This is the code in the Windows Form ...
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
{
List<Product> products = new List<Product>();
List<ReportPeriod> reportPeriods = new List<ReportPeriod>();
List<SalesReport> salesReports = new List<SalesReport>();
List<ReportDataRow> reportdataSource = new List<ReportDataRow>();
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
// **************************
// ***** ADING PRODUCTS *****
// **************************
products.Add(new Product(productName: "Item1"));
products.Add(new Product(productName: "Item2"));
// *********************************
// ***** ADDING REPORT PERIODS *****
// *********************************
reportPeriods.Add(new ReportPeriod(month_field: "Nov 17", from: "2017-11-01", to: "2017-11-30"));
reportPeriods.Add(new ReportPeriod(month_field: "Dec 17", from: "2017-12-01", to: "2017-12-31"));
reportPeriods.Add(new ReportPeriod(month_field: "Jan 18", from: "2018-01-01", to: "2018-01-31"));
reportPeriods.Add(new ReportPeriod(month_field: "Feb 18", from: "2018-02-01", to: "2018-02-28"));
// ********************************
// ***** ADDING SALES REPORTS *****
// ********************************
// Sales for Nov 17
salesReports.Add(new SalesReport(Item: "Item1", Month_Field: "Nov 17", SalesCount: 5));
salesReports.Add(new SalesReport(Item: "Item2", Month_Field: "Nov 17", SalesCount: 6));
// Sales for Dec 17
salesReports.Add(new SalesReport(Item: "Item1", Month_Field: "Dec 17", SalesCount: 3));
salesReports.Add(new SalesReport(Item: "Item2", Month_Field: "Dec 17", SalesCount: 2));
// Sales for Jan 18
salesReports.Add(new SalesReport(Item: "Item1", Month_Field: "Jan 18", SalesCount: 6));
salesReports.Add(new SalesReport(Item: "Item2", Month_Field: "Jan 18", SalesCount: 7));
// Sales for Feb 18
salesReports.Add(new SalesReport(Item: "Item1", Month_Field: "Feb 18", SalesCount: 3));
salesReports.Add(new SalesReport(Item: "Item2", Month_Field: "Feb 18", SalesCount: 2));
// 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++)
{
// Get sales info for product
int sales = salesReportSubList[columnIndex].SalesCount;
if (columnIndex == 0) reportDataRow.Month1 = sales;
if (columnIndex == 1) reportDataRow.Month2 = sales;
if (columnIndex == 2) reportDataRow.Month3 = sales;
if (columnIndex == 3) reportDataRow.Month4 = sales;
}
// Add row to datasource
reportDataRow.Product = prod.productName;
reportdataSource.Add(reportDataRow);
}
// Data binding populates DataGridView, with columns and data rows (Tuples).
dataGridView1.DataSource = reportdataSource;
// Change Column headers after databinding is done
for(int columnIndex=0; columnIndex< reportPeriods.Count; columnIndex++)
{
dataGridView1.Columns[columnIndex+1].HeaderText = reportPeriods[columnIndex].month_field;
}
}
}
}
我做了som更新到表单代码，以强调如何使用LINQ进行选择（这不是SQL，即使语法类似）。
我添加了一些LINQ代码进行过滤，我把那些代码放在从数据库 - 对象到报告 - 对象的数据传输中。
我添加了模拟数据库请求的方法
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()。但我真正的代码你应该通过非常紧凑的SQL查询来做出主要选择。根本不应该提供你不需要的数据库中的信息。
这根本没有优化，只是一种显示如何一些的方式功能与LINQ一起使用以及如何以相对简单的方式从实时数据构建数据透视表。
所有数据也都是硬编码的，但这只是为了对于新手开发人员来说，使这个示例代码易于理解。在现实生活中，你永远不会以这种方式编写代码。
确保你学习如何使用Entity框架，访问数据库，以及从中恢复数据。实体框架使用LINQ。
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;
}
}
}
}
这篇关于我该如何解决这个问题..问题..的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
