everlight.eap/backend/DllEapDal/OFILM/OutPutDataDal.cs

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

namespace DllEapDal.OFILM
{
    public class OutPutDataDal
    {
        private IDatabase db;

        public OutPutDataDal(IDatabase db)
        {
            this.db = db;
        }

        public LayuiModel<OutPutData> Get(string fcode, DateTime start, DateTime end, string recipe, int pageIndex, int pageSize)
        {
            string sql= $@"SELECT b.FName Factory,a.Plant Plant,a.Floor Floor,a.FCode FCode,a.ProcessCode ProcessCode,a.Recipe Recipe,a.FCount FCount,a.SDate SDate,a.EDate EDate FROM `v_maccountdetail` a LEFT JOIN factoryregion b on a.FactoryId=b.iD where 1=1 ";
            string ss = $@" and a.SDate>='{start.ToString("yyyy-MM-dd HH:mm:dd")}' and a.SDate<= '{end.ToString("yyyy-MM-dd HH:mm:dd")}' ";
            if (!string.IsNullOrEmpty(fcode))
            {
                ss += $@" and a.fcode like '%{fcode}%' ";
            }
            if (!string.IsNullOrEmpty(recipe))
            {
                ss += $@" and a.Recipe like '%{recipe}%' ";
            }

            string str = $@" limit {(pageIndex - 1) * pageSize},{pageSize}";

            return new LayuiModel<OutPutData>()
            {
                code = 1,
                count = Convert.ToInt32(db.FindObject("select count(1) from v_maccountdetail a where 1=1 " + ss)),
                data = db.FindList<OutPutData>(sql + ss + str)
            };
        }

        public LayuiModel<OutPutData> GetCount(string fcode, DateTime start, DateTime end, string recipe, int pageIndex, int pageSize)
        {

            string str = $@"select `a`.`SDate` AS `SDate`,`a`.`EDate` AS `EDate`,`b`.`FCode` AS `FCode`,`c`.`FCount` AS `FCount`,substring_index(replace(replace(replace(`a`.`PName`,' ','-'),'_','-'),'.','-'),'-',1) AS `Recipe`,`h`.`FName` AS `Factory`,`d`.`PCode` AS `ProcessCode`,`f`.`FName` AS `Floor`,`g`.`FName` AS `Plant` from ((((((`maccountmst` `a` join `machine` `b` on((`a`.`MacID` = `b`.`ID`))) join `maccountdetail` `c` on((`c`.`MstID` = `a`.`ID`))) join `mactprocess` `d` on((`a`.`MacID` = `d`.`MacID`))) join `factoryregion` `e` on((`b`.`RegionId` = `e`.`Id`))) join `factoryregion` `f` on((`e`.`ParentId` = `f`.`Id`))) join `factoryregion` `g` on((`f`.`ParentId` = `g`.`Id`)) join `factoryregion` h on ((`b`.`FactoryId` = `h`.`Id`)))  where ((`c`.`TypeID` = 0)  and (`g`.`Id` <> 207) and (`f`.`Id` <> 281) and (`f`.`Id` <> 213) and (`f`.`Id` <> 210) and (`f`.`Id` <> 45) and (`f`.`Id` <> 13)) ";
            string sql = $@"select * from ({str}) t where 1=1 ";
            string ss = $@" and t.SDate>='{start.ToString("yyyy-MM-dd HH:mm:dd")}' and t.SDate<= '{end.ToString("yyyy-MM-dd HH:mm:dd")}' ";
            if (!string.IsNullOrEmpty(fcode))
            {
                ss += $@" and t.fcode like '%{fcode}%' ";
            }
            if (!string.IsNullOrEmpty(recipe))
            {
                ss += $@" and t.Recipe like '%{recipe}%' ";
            }

            string strt = $@" limit {(pageIndex - 1) * pageSize},{pageSize}";
            var count = Convert.ToInt32(db.FindObject($@"select count(1) from ({str}) t where 1=1 " + ss));
            var data = db.FindList<OutPutData>(sql + ss + strt);

            return new LayuiModel<OutPutData>()
            {
                code = 1,
                count=count,
                data=data
            };
        }
    }
}