时间戳

首页 > 解决方案 > 如何按小时范围对从数据库获取的项目进行分组？

c# - 如何按小时范围对从数据库获取的项目进行分组？

问题描述

我必须从我的 API 返回一个时间范围列表以及在该范围内销售的所有商品。

我做了一个查询，它返回所有商品的销售小时数，查询返回的数据如下：

DESCRIPTION|QUANTITY|PRICE|HOURS
TEST        1        1.12  0:00-01:00
TOST        5        1.12  0:00-01:00
TIST        8        1.12  8:00-09:00
TAST        2        1.12  8:00-09:00
TAST        2        1.12  8:00-09:00
TAST        2        1.12  9:00-10:00
TAST        2        1.12  10:00-11:00

我会从我的 API 返回一个数据，比如

 [{ "range": "0:00-01:00", "items": [{"desc": "TEST", "qta": 8, "price": 1.12}, {"desc": "TOST", "qta": 8, "price": 1.12}, ...]}, {"range": "08:00-09:00", "items": [{"desc": "TIST", "qta": 8, "price": 1.12}]} ...]

所以我做了我的模型（它有更多的数据，因为上面的数据只是一个例子）

Public Class FasceOrarie

    Public Property fascia As String
    Public Property articoli As IEnumerable(Of ArticoliFascia)

    Public Sub New()
    End Sub

    Public Sub New(fascia As String, articoli As IEnumerable(Of ArticoliFascia))

        Me.fascia = fascia
        Me.articoli = articoli

    End Sub
    Public Class ArticoliFascia

        Public Property codart As String
        Public Property desc As String
        Public Property qta As String
        Public Property importo As String

        Public Sub New()
        End Sub

        Public Sub New(codart As String, desc As String, qta As Double, importo As Double)

            Me.codart = codart
            Me.desc = desc
            Me.qta = qta
            Me.importo = importo

        End Sub
    End Class

End Class

在这里，我有我的功能，我可以在其中进行选择，并且我有所有数据的阅读器：

Public Function ArticoliFasciaOraria(db As String) As IEnumerable(Of FasceOrarie)
    Dim SQLConnect As String = "Server=127.0.0.1;Port=3306;Database=" & db & ";Uid=visuali;Pwd=visualintelligence2k20;"
    Dim queryFasce As String = "MYQUERY"
    Dim listArticoli As List(Of FasceOrarie)

    Using cn As New MySqlConnection(SQLConnect)
        cn.Open()
        Using cmd As New MySqlCommand(queryFasce, cn)
            Try
                Using reader As MySqlDataReader = cmd.ExecuteReader()
                    // how to add data properly in the list of FasceOrarie?
                End Using
            Catch ex As Exception
                Return Nothing
            End Try
        End Using
    End Using
End Function

但是我如何格式化我的数据以在我的模型中正确设置它？如果我只是将它添加到列表中，我将在数组中多次设置相同的范围，而我必须有一个范围，其中包含属于该范围的所有项目...

我什至会接受 c# 解决方案，因为我稍后会将该项目迁移到 c#

标签： c#jsonvb.netapi

解决方案

发帖是用 c# 标记的，所以这是我的建议：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data;



namespace ConsoleApplication186
{
    class Program
    {
        static void Main(string[] args)
        {

            DataTable dt = new DataTable();
            dt.Columns.Add("DESCRIPTION",typeof(string)); 
            dt.Columns.Add("QUANTITY",typeof(int)); 
            dt.Columns.Add("PRICE",typeof(decimal)); 
            dt.Columns.Add("HOURS",typeof(string)); 

            dt.Rows.Add(new object[] {"TEST", 1, 1.12, "0:00-01:00"});
            dt.Rows.Add(new object[] {"TOST", 5, 1.12, "0:00-01:00"});
            dt.Rows.Add(new object[] {"TIST", 8, 1.12, "8:00-09:00"});
            dt.Rows.Add(new object[] {"TAST", 2, 1.12, "8:00-09:00"});
            dt.Rows.Add(new object[] {"TAST", 2, 1.12, "8:00-09:00"});
            dt.Rows.Add(new object[] {"TAST", 2, 1.12, "9:00-10:00"});
            dt.Rows.Add(new object[] {"TAST", 2, 1.12, "10:00-11:00"});

            List<ArticoliFascia> articoliFascia = dt.AsEnumerable().Select(x => new ArticoliFascia(x.Field<string>(3), x.Field<string>(0), x.Field<int>(1), x.Field<decimal>(2))).ToList();

            var groups = articoliFascia.GroupBy(x => new { start = x.startTime, end = x.endTime }).ToList();


 
        }
    }
    public class ArticoliFascia
    {
        public string codart { get; set; }
        public string desc { get; set; }
        public int qta { get; set; }
        public decimal importo { get; set; }
        public TimeSpan startTime { get; set; }
        public TimeSpan endTime { get; set; }

        public ArticoliFascia(string codart, string desc, int qta, decimal importo)
        {

            this.codart = codart;
            this.desc = desc;
            this.qta = qta;
            this.importo = importo;

            string[] splitArray = codart.Split(new char[] { '-' }).ToArray();
            startTime = TimeSpan.Parse(splitArray[0]);
            endTime = TimeSpan.Parse(splitArray[1]);
        }
    }
}

推荐阅读