everlight.eap/backend/DllEapDal/MacCheckMstDal.cs

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

namespace DllEapDal
{
    public class MacCheckMstDal
    {
        private IDatabase CurrDb = null;
        public MacCheckMstDal(IDatabase db)
        {
            CurrDb = db;
        }
        public MacCheckMst IU(MacCheckMst mst, string usercode, ref string errorinfo)
        {
            try
            {
                int result = 0;
                int id = mst.ID;
                if (mst.EntityStatusID == 1)
                {
                    result = CurrDb.InsertFor(mst, usercode);
                    if (result < 0)
                    {
                        return null;
                    }
                    object objid = CurrDb.FindObject("select @@IDENTITY");
                    if (objid.ToString() == "")
                    {
                        return null;
                    }
                    id = int.Parse(objid.ToString());
                }
                else
                {
                    result = CurrDb.UpdateFor(mst, usercode);
                    if (result < 0)
                    {
                        return null;
                    }
                }
                mst = CurrDb.FindEntityFor<MacCheckMst>(id);
                return mst;
            }
            catch (Exception e)
            {
                errorinfo = e.Message;
                return null;
            }

        }

        public int AddCheck(string maccode, List<MacCheckDetail> details, string usercode, ref string errorinfo, int checkMode = 1)
        {
            try
            {
                //var machine = CurrDb.FindListForCondition<Step>($" and a.FCode='{maccode}'", ref errorinfo).FirstOrDefault();
                //var smDal = new StaffMachineDal(CurrDb);
                //if (!smDal.IsPermitted(usercode, machine.ID, ref errorinfo))
                //{
                //    return -1;
                //}

                MacCheckMst mst = new MacCheckMst();
                mst.MacCode = maccode;
                mst.CheckMode = checkMode;
                mst = IU(mst, usercode, ref errorinfo);
                if (mst == null)
                    return -1;
                foreach (var item in details)
                    item.MacCheckMstID = mst.ID;
                int result = CurrDb.InsertFor<MacCheckDetail>(details, usercode);
                if (result <= 0)
                    return -1;
                return 1;
            }
            catch (Exception e)
            {
                errorinfo = e.Message;
                return -1;
            }

        }

        /// <summary>
        /// 获取点检记录列表
        /// </summary>
        /// <param name="start"></param>
        /// <param name="length"></param>
        /// <param name="order"></param>
        /// <param name="sort"></param>
        /// <param name="filter"></param>
        /// <param name="errorinfo"></param>
        /// <returns></returns>
        public IEnumerable<MacCheckMstDto> Get(int start, int length, string order, string sort, string filter, string errorinfo)
        {
            var sql = $@"select a.id,a.macCode,a.CheckMode,a.Rectime from maccheckmst a where 1=1 {filter} 
                         order by {sort} {order} limit {start - 1},{length} ";
            var msts = CurrDb.FindList<MacCheckMstDto>(sql);
            if (msts != null && msts.Count() > 0)
            {
                foreach (var item in msts)
                {
                    sql = $@"select a.paramCode,a.paramVal,b.fname paramName from maccheckdetail a
                           left join sec b on a.ParamCode = b.FCode 
                           where a.MacCheckMstID={item.Id}";
                    var details = CurrDb.FindList<MacCheckDetailDto>(sql);
                    item.MacCheckDetails = details;
                }
            }
            return msts;
        }

        public IEnumerable<MacCheckMstDetailDto> GetMstDetail(int start, int length, string order, string sort, string filter, string errorinfo)
        {
            var sql = $@"SELECT
	                        a.id ID,
	                        i.FName FactoryName,
	                        k.FName FloorName,
	                        a.macCode MacCode,
	                        b.paramCode ParamCode,
	                        b.paramVal ParamVal,
	                        c.fname ParamName,
                            a.CheckMode,
	                        a.Rectime 
                        FROM
	                        maccheckmst a
	                        LEFT JOIN maccheckdetail b ON a.id = b.MacCheckMstID
	                        LEFT JOIN sec c ON b.ParamCode = c.FCode
	                        LEFT JOIN Machine d on d.FCode=a.macCode
	                        left outer join factoryregion i on d.factoryid =i.id
                            left outer join factoryregion j on d.regionid =j.id
                            left outer join factoryregion k on j.parentid =k.id  where 1=1 {filter} 
                         order by {sort} {order} limit {start - 1},{length} ";
            var msts = CurrDb.FindList<MacCheckMstDetailDto>(sql);
            var list = new List<MacCheckMstDetailDto>();
            var groupfactory = msts.GroupBy(c => c.FactoryName);
            var no = 0;
            if (groupfactory != null && groupfactory.Count() > 0)
            {
                foreach (var item in groupfactory)
                {
                    var renderFactory = true;
                    no++;
                    var dateGroups = item.Where(c => c.FactoryName == item.Key).GroupBy(c => c.FloorName);
                    if (dateGroups != null && dateGroups.Count() > 0)
                    {
                        foreach (var dateItem in dateGroups)
                        {
                            var renderFloor = true;
                            var dateIt = dateItem.Where(c => c.FloorName == dateItem.Key).GroupBy(c => c.MacCode);
                            foreach (var reportItem in dateIt)
                            {
                                var renderMac = true;
                                foreach (var it in reportItem)
                                {
                                    var temp = new MacCheckMstDetailDto
                                    {
                                        FactoryName = item.Key,
                                        FloorName = dateItem.Key,
                                        MacCode = reportItem.Key,
                                        ParamCode = it.ParamCode,
                                        ParamVal = it.ParamVal,
                                        ParamName = it.ParamName,
                                        CheckMode = it.CheckMode,
                                        RecTime = it.RecTime,
                                        FactoryRowSpan = item.Count(),
                                        FloorRowSpan = dateItem.Count(),
                                        MacRowSpan = reportItem.Count(),
                                        RenderFactor = renderFactory,
                                        RenderFloor = renderFloor,
                                        RenderMac = renderMac,
                                    };
                                    list.Add(temp);
                                    renderFactory = false;
                                    renderFloor = false;
                                    renderMac = false;
                                }

                            }
                        }
                    }
                }
            }
            return list;
        }

        public IEnumerable<MacCheckMstDetailDto> GetMstDetailExport(int start, int length, string order, string sort, string filter, string errorinfo)
        {
            var sql = $@"SELECT
	                        a.id ID,
	                        i.FName FactoryName,
	                        k.FName FloorName,
	                        a.macCode MacCode,
	                        b.paramCode ParamCode,
	                        b.paramVal ParamVal,
	                        c.fname ParamName,
                            a.CheckMode,
	                        a.Rectime 
                        FROM
	                        maccheckmst a
	                        LEFT JOIN maccheckdetail b ON a.id = b.MacCheckMstID
	                        LEFT JOIN sec c ON b.ParamCode = c.FCode
	                        LEFT JOIN Machine d on d.FCode=a.macCode
	                        left outer join factoryregion i on d.factoryid =i.id
                            left outer join factoryregion j on d.regionid =j.id
                            left outer join factoryregion k on j.parentid =k.id  where 1=1 {filter} 
                         order by {sort} {order} limit 10000 ";
            var msts = CurrDb.FindList<MacCheckMstDetailDto>(sql);
            return msts;
        }

        /// <summary>
        /// 获取点检记录的数量
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        public int GetCount(string filter)
        {
            var sql = new MacCheckMst().GetSelectSql();
            sql = $"select count(1) from ({sql} where 1=1 {filter}) tt";
            return Convert.ToInt32(CurrDb.FindList<string>(sql).FirstOrDefault() ?? "-1");
        }

        public IEnumerable<MacCheckQueryDto> GetCheckDtos(int start, int length, string order, string sort, string filter, string errorinfo, out int total)
        {
            var datas = CurrDb.FindListForCondition<MacCheckQueryDto>($"{filter} order by {sort} {order} limit {start - 1},{length}", ref errorinfo);
            var sql = $"select count(1) from maccheckmst a where 1=1 {filter}";
            total = Convert.ToInt32(CurrDb.FindList<string>(sql).FirstOrDefault() ?? "-1");
            return datas;
        }
    }
}