everlight.eap/backend/DllEapDal/MachineToPmDal.cs

using Cksoft.Data;
using DllEapEntity.PM;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace DllEapDal
{
    public class MachineToPmDal
    {
        private IDatabase CurrDb;

        public MachineToPmDal(IDatabase db)
        {
            CurrDb = db;
        }

        public IEnumerable<MachineToPM> Get()
        {
            var sql = @" select a.id,a.fCode macCode, a.FName macName, b.id modelId, b.FCode modelCode, b.FName modelName,
                                d.FCode pmcode, d.FName pmName, d.Maintenance maintenance, d.TimeSpan timespan, d.id pmId,
                                     c.startTime starttime, f.pmTime lastpmtime,h.FCode as pCode,h.FName as pName 
                          from machine a
                          left outer join macmodel b  on a.mmodeId = b.id
                          left outer join pmmachine c on a.id = c.macid
                          left outer join pmschema d on c.pmid = d.id
                          left outer join pmhistory f on a.id = f.macid and f.id in (select max(id) from pmhistory h where h.macid = a.id and h.pmid = c.pmid)
                          left outer join MacTProcess g on a.id=g.macid 
                          left outer join TProcess h on g.pCode=h.FCode";
            var models = CurrDb.FindList<MachineToPM>(sql);
            return models;
        }

        public IEnumerable<MachineToPM> Get(int start, int length, string order, string sort, string filter, string states, string errorinfo, out int total)
        {
            string sql = @"select a.id,a.fCode macCode,a.FName macName,b.id modelId,b.FCode modelCode,b.FName modelName,
                          d.FCode pmcode,d.FName pmName,d.Maintenance maintenance,d.TimeSpan timespan,d.id pmId,
                           c.startTime starttime,f.pmTime lastpmtime,h.FCode as PCode,h.FName as PName 
                           from machine a 
                          left outer join macmodel b  on a.mmodeId = b.id
                          left outer join pmmachine c on a.id=c.macid
                          left outer join pmschema d on c.pmid=d.id
                          left outer join pmhistory f on a.id=f.macid and f.id in (select max(id) from pmhistory h where h.macid=a.id and h.pmid=c.pmid) 
                          left outer join MacTProcess g on a.id=g.macid 
                          left outer join TProcess h on g.pCode=h.FCode "
            + $" where 1=1 {filter} order by {sort} {order}";
            var pms = CurrDb.FindList<MachineToPM>(sql).Where(c => !string.IsNullOrEmpty(c.PmCode)).ToList();
            DateTime dateNow = DateTime.Now;
            var results = new List<MachineToPM>();
            for (var i = 0; i < pms.Count(); i++)
            {
                var item = pms[i];
                if (item.LastPmTime == null)
                {
                    item.NextPmTime = item.StartTime.Value.AddDays(item.TimeSpan);
                    if (item.NextPmTime >= dateNow.AddDays(10))
                    {
                        item.State = 0;   // 正常未到保养提醒
                        results.Add(item);
                    }
                    else if (dateNow < item.NextPmTime && item.NextPmTime < dateNow.AddDays(10))
                    {
                        item.State = 1;   // 提醒未过保
                        results.Add(item);
                    }
                    else
                    {
                        item.State = 2;    // 过保
                        results.Add(item);
                    }
                }
                else
                {
                    item.NextPmTime = item.LastPmTime.Value.AddDays(item.TimeSpan);
                    if (item.NextPmTime >= dateNow.AddDays(10))
                    {
                        item.State = 0;   // 正常未到保养提醒
                        results.Add(item);
                    }
                    else if (dateNow < item.NextPmTime && item.NextPmTime < dateNow.AddDays(10))
                    {
                        item.State = 1;   // 提醒未过保
                        results.Add(item);
                    }
                    else
                    {
                        item.State = 2;    // 过保
                        results.Add(item);
                    }
                }
            }
            total = results.Count; 
            if (!string.IsNullOrEmpty(states))
            {
                var arr = states.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).Select(c => Convert.ToInt32(c)).ToList();
                if (arr != null && arr.Count > 0)
                {
                    if (arr.Count == 1)
                    {
                        results = results.Where(c => c.State == arr[0]).ToList();
                    }
                    else if (arr.Count == 2)
                    {
                        results = results.Where(c => c.State == arr[0] || c.State == arr[1]).ToList();
                    }
                }
            }
            results = results.Skip(start - 1).Take(length).ToList();
            return results;
        }

        public int GetCount(string filter)
        {
            string errorinfo = string.Empty;
            //string sql = $"select count(1) from ProgramRule a where 1=1 {filter}";
            var entities = CurrDb.FindListForCondition<MachineToPM>(filter, ref errorinfo);
            if (entities != null)
            {
                return entities.Count();
            }
            return 0;
        }

        public MachineToPM Get(int id)
        {
            var pro = CurrDb.FindEntityFor<MachineToPM>(id);
            return pro;
        }
    }
}