GarbageCrane/一车两车整合版/Code/RecordRepository.cs

using Dapper;
using GCAS.Dto;
using GCAS.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;

namespace GCAS.Code
{
    public class RecordRepository
    {
        public async Task<List<SourceChartDto>> GetChartSource(DateTime? startTime, DateTime? endTime)
        {
            string selectSql = "SELECT id,netweight,time,entrance,team,device from record where 1=1 ";
            if (startTime != null && endTime != null)
            {
                selectSql += " and time>@StartTime and time<@EndTime ";
            }

            using (IDbConnection conn = DataBaseConfig.GetSqlConnection())
            {
                return await Task.Run(() => conn.Query<SourceChartDto>(selectSql, new { StartTime = startTime, EndTime = endTime }).ToList());
            }
        }

        public async Task<List<RecordDto>> GetList(string keyWord, int device, int entrance, int team, DateTime? startTime, DateTime? endTime)
        {
            string selectSql = @"SELECT record.id,record.number,record.netweight/1000 AS netweight,record.device, device.name as devicename,record.team, team.name as teamname,record.entrance, entrance.name as entrancename,record.time FROM  record 
                                        LEFT JOIN device ON record.device = device.id
                                        LEFT JOIN team ON record.team = team.id
                                        LEFT JOIN entrance ON record.entrance = entrance.id
                                        WHERE 1=1 ";
            if (!string.IsNullOrEmpty(keyWord)) 
            {
                selectSql += " and record.number like @KeyWord ";
            }
            if (startTime != null && endTime != null)
            {
                selectSql += " and record.time>@StartTime and record.time<@EndTime ";
            }
            if (device != 0)
            {
                selectSql += " and record.device=@Device ";
            }
            if (entrance != 0)
            {
                selectSql += " and record.entrance=@Entrance ";
            }
            if (team != 0)
            {
                selectSql += " and record.team=@Team ";
            }
            selectSql += "  order by record.id desc";
            using (IDbConnection conn = DataBaseConfig.GetSqlConnection())
            {
                var list = await Task.Run(() => conn.Query<RecordDto>(selectSql, new { KeyWord = "%" + keyWord + "%", StartTime = startTime, Device = device, Entrance = entrance, Team = team, EndTime = endTime }).ToList());
                return list;
            }
        }



        public async Task<int> Delete(int id)
        {
            string deleteSql = "DELETE FROM record WHERE id=@Id";
            using (IDbConnection conn = DataBaseConfig.GetSqlConnection())
            {
                return await Task.Run(() => conn.ExecuteAsync(deleteSql, new { Id = id }));
            }
        }



        public RecordModel Insert(RecordModel entity)
        {
            string numberSql = "SELECT CONCAT(DATE_FORMAT(NOW(), '%Y%m%d'),LPAD((COUNT(1) + 1), 5, 0))FROM record WHERE time>DATE(NOW())";

            string insertSql = "INSERT INTO record (number,time,grossweight,tareweight,netweight,device,entrance,team)" +
                "VALUES(@Number,NOW(),@GrossWeight,@TareWeight,@NetWeight,@Device,@Entrance,@Team)";
            using (IDbConnection conn = DataBaseConfig.GetSqlConnection())
            {
                var number = conn.QueryFirst<string>(numberSql);
                entity.Number = number;
                int count = conn.Execute(insertSql, entity);
                if (count > 0)
                {
                    return entity;
                }
             }
            return null;
        }
        public async Task<TotalDto> TotalStatistics()
        {
            TotalDto output = new TotalDto();

            string selectSql = @"SELECT IFNULL(sum(r.netweight),0)AS dailyTotal FROM record AS r WHERE	r.time > CURDATE();";

            if (Thread.CurrentThread.CurrentUICulture.Name == "zh-Hans")
                selectSql += @"SELECT IFNULL(sum(r.netweight),0)AS weeklyTotal FROM record AS r WHERE	r.time > DATE_FORMAT(SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-1), '%Y-%m-%d 00:00:00');";
            else
                selectSql += @"SELECT IFNULL(sum(r.netweight),0)AS weeklyTotal FROM record AS r WHERE	r.time > DATE_FORMAT(SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')), '%Y-%m-%d 00:00:00');";

            selectSql += @"SELECT IFNULL(sum(r.netweight),0)AS monthlyTotal FROM record AS r WHERE	r.time > DATE_FORMAT(CURDATE(), '%Y-%m-01 00:00:00'); ";

            selectSql += @"SELECT IFNULL(sum(r.netweight),0)AS annualTotal FROM record AS r WHERE	r.time > DATE_FORMAT(CURDATE(), '%Y-01-01 00:00:00'); ";

            using (IDbConnection conn = DataBaseConfig.GetSqlConnection())
            {
                using (var multi = await Task.Run(() => conn.QueryMultiple(selectSql)))
                {
                    output.Daily = multi.ReadFirst<int>();
                    output.Weekly = multi.ReadFirst<int>();
                    output.Monthly = multi.ReadFirst<int>();
                    output.AnnualTotal = multi.ReadFirst<int>();
                }

            }
            return output;

        }
        public TotalDto TotalStatisticsAB()
        {
            TotalDto output = new TotalDto();

            string selectSql = @"SELECT IFNULL(sum(r.netweight),0)AS dailyTotal FROM record AS r WHERE  entrance=1 AND	r.time > CURDATE();";
            selectSql += @"SELECT IFNULL(sum(r.netweight),0)AS dailyTotal FROM record AS r WHERE entrance=2 AND	r.time > CURDATE();";

            if (Thread.CurrentThread.CurrentUICulture.Name == "zh-Hans")
                selectSql += @"SELECT IFNULL(sum(r.netweight),0)AS weeklyTotal FROM record AS r WHERE	r.time > DATE_FORMAT(SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-1), '%Y-%m-%d 00:00:00');";
            else
                selectSql += @"SELECT IFNULL(sum(r.netweight),0)AS weeklyTotal FROM record AS r WHERE	r.time > DATE_FORMAT(SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')), '%Y-%m-%d 00:00:00');";

            selectSql += @"SELECT IFNULL(sum(r.netweight),0)AS monthlyTotal FROM record AS r WHERE	r.time > DATE_FORMAT(CURDATE(), '%Y-%m-01 00:00:00'); ";

            selectSql += @"SELECT IFNULL(sum(r.netweight),0)AS annualTotal FROM record AS r WHERE	r.time > DATE_FORMAT(CURDATE(), '%Y-01-01 00:00:00'); ";

            using (IDbConnection conn = DataBaseConfig.GetSqlConnection())
            {
                using (var multi = conn.QueryMultiple(selectSql))
                {
                    output.Daily = multi.ReadFirst<int>();
                    output.DailyB = multi.ReadFirst<int>();
                    output.Weekly = multi.ReadFirst<int>();
                    output.Monthly = multi.ReadFirst<int>();
                    output.AnnualTotal = multi.ReadFirst<int>();
                }

            }
            return output;

        }

        public async Task<List<DailyStatisticDto>> DailyStatistics(int teamId, int deviceId)
        {
            string selectSql = @"SELECT 
	                                entrance.id,
	                                entrance.name,
                               (SELECT 
                                        COUNT(1)
                                    FROM
                                        record AS r
                                    WHERE
                                        r.time > CURDATE() 
                                            AND r.entrance = record.entrance
                                            AND r.device = @DeviceId) AS dailycount,
                               SUM( IFNULL( record.netweight, 0 ) ) AS dailytotal,
                               (SELECT 
                                        Count(1)
                                    FROM
                                        record AS r
                                    WHERE
                                        r.time > CURDATE() AND r.team = @TeamId
                                            AND r.entrance = record.entrance
                                            AND r.device = @DeviceId) AS teamcount,
	                                (
                                SELECT 
	                                IFNULL( sum( r.netweight ), 0 ) 
                                FROM
	                                record AS r 
                                WHERE
	                                r.time > CURDATE() 
	                                AND r.team = @TeamId  
	                                AND r.entrance = record.entrance 
                                    AND r.device=@DeviceId 
	                                ) AS teamtotal 
                                FROM
	                                entrance 
	                                LEFT JOIN record ON entrance.id = record.entrance 
	                                AND record.time >  CURDATE() 
                                    AND device = @DeviceId 
                                GROUP BY
	                                entrance.id,
	                                entrance.name";
            using (IDbConnection conn = DataBaseConfig.GetSqlConnection())
            {
                var result = await Task.Run(() => conn.QueryAsync<DailyStatisticDto>(selectSql, new { TeamId = teamId, DeviceId = deviceId }));
                return result.ToList();
            }

        }


        public async Task<List<DailyStatisticDto>> DailyStatistics(int deviceId)
        {
            string selectSql = @"SELECT
	                                team.id,
	                                team.NAME,
	                                (
	                                SELECT
		                                COUNT( 1 ) 
	                                FROM
		                                record AS r 
	                                WHERE
		                                r.time > CURDATE() 
		                                AND r.entrance = record.entrance 
		                                AND r.device = @DeviceId 
	                                ) AS dailycount,
	                                SUM( IFNULL( record.netweight, 0 ) )/1000 AS dailytotal
                                FROM
	                                team
	                                LEFT JOIN record ON team.id = record.team 
	                                AND record.time > CURDATE() 
	                                AND device = @DeviceId 
                                GROUP BY
	                                team.id,
	                                team.NAME";
            using (IDbConnection conn = DataBaseConfig.GetSqlConnection())
            {
                var result = await Task.Run(() => conn.QueryAsync<DailyStatisticDto>(selectSql, new { DeviceId = deviceId }));
                return result.ToList();
            }

        }
        public async Task<List<DailyStatisticDto>> DailyTotalStatistics(int teamId)
        {
            string selectSql = @"SELECT 
	                                entrance.id,
	                                entrance.name,
                                    COUNT(record.id) AS dailycount,
	                                SUM( IFNULL( record.netweight, 0 ) ) AS dailytotal, 
                                    (
                                SELECT 
	                                count(1) 
                                FROM
	                                record AS r 
                                WHERE
	                                r.time > CURDATE() 
	                                AND r.team = @TeamId  
	                                AND r.entrance = record.entrance 
	                                ) AS teamcount,
	                                (
                                SELECT 
	                                IFNULL( sum( r.netweight ), 0 ) 
                                FROM
	                                record AS r 
                                WHERE
	                                r.time > CURDATE() 
	                                AND r.team = @TeamId  
	                                AND r.entrance = record.entrance 
	                                ) AS teamtotal 
                                FROM
	                                entrance 
	                                LEFT JOIN record ON entrance.id = record.entrance 
	                                AND record.time >  CURDATE() 
                                GROUP BY
	                                entrance.id,
	                                entrance.name";
            using (IDbConnection conn = DataBaseConfig.GetSqlConnection())
            {
                var result = await Task.Run(() => conn.QueryAsync<DailyStatisticDto>(selectSql, new { TeamId = teamId }));
                return result.ToList();
            }

        }
        public async Task<List<RealTimeBarDto>> GetDailyRealTimeData(int deviceId, int teamId)
        {
            string selectSql = @"SELECT time,FORMAT(netweight/1000,2) as weight  FROM record where device=@DeviceId and team=@TeamId and time>CURDATE()";
            using (IDbConnection conn = DataBaseConfig.GetSqlConnection())
            {
                var result = await Task.Run(() => conn.QueryAsync<RealTimeBarDto>(selectSql, new { DeviceId = deviceId, TeamId = teamId }));
                return result.ToList();
            }
        }
    }
}