using System.Data;
using MES.Service.DB;
using MES.Service.Dto.service;
using MES.Service.Modes;
using MES.Service.util;
using SqlSugar;
using DbType = System.Data.DbType;

namespace MES.Service.service.QC;

public class XJService
{
    private static readonly OracleSQLHelper SQLHelper = new();

    private readonly BaseService _baseService = new();


    private readonly string[] statusArray = ["生产", "待产"];

    //生成最新的检验单号
    public string getMaxReleaseNo()
    {
        var sql =
            string.Format(
                "select max(RELEASE_NO) from QS_QA_ITEM_XJ where RELEASE_NO like 'XJN%' and to_char(CREATE_DATE,'yyyy-mm-dd') = '{0}'",
                DateTime.Now.ToString("yyyy-MM-dd"));

        var executeScalar = SQLHelper.ExecuteScalar(sql);
        var date = DateTime.Now.ToString("yyMMdd");
        var number = "0001";
        var olReleaseNo = executeScalar.ToString();
        //判断今天是否生成了巡检单
        if (string.IsNullOrEmpty(olReleaseNo)) return "XJN" + date + number;
        //截取后四位的流水号累加
        var substring = Convert.ToInt32(olReleaseNo.Substring(10));
        substring += 1;
        number = substring.ToString("D4");

        //如果为空直接返回0001
        return "XJN" + date + number;
    }

    public List<Womdaa> getItem(string daa001)
    {
        var db = SqlSugarHelper.GetInstance();

        //in的写法 https://www.donet5.com/Home/Doc?typeId=1187

        return db.Queryable<Womdaa, MesItems>((a, c) =>
                new JoinQueryInfos(
                    JoinType.Left, a.Daa002 == c.Id.ToString()))
            .Where(a => a.Daa001 == daa001)
            .Select((a, c) => new Womdaa
            {
                Daa001 = a.Daa001,
                Daa002 = c.ItemNo,
                Daa003 = a.Daa003,
                Daa004 = a.Daa004,
                Daa008 = a.Daa008,
                SjXtNo = a.SjXtNo,
                salesOrderNo = a.salesOrderNo,
                saleOrderNo = a.saleOrderNo,
                // BoardStyle = c.BoardStyle,
                Id = a.Id
            })
            .ToList();
    }

    public List<MesLine> getLineAll()
    {
        var db = SqlSugarHelper.GetInstance();

        return db.Queryable<Womdaa, MesLine>((a, b) =>
                new JoinQueryInfos(JoinType.Left, a.Daa015 == b.LineNo))
            .GroupBy((a, b) => new
            {
                a.Daa015, b.LineName
            })
            .Select((a, b) => new MesLine
            {
                LineNo = a.Daa015,
                LineName = b.LineName
            }).OrderBy("a.Daa015").ToList();
    }

    public List<Womdaa> getDaa001(string daa020, string item)
    {
        var db = SqlSugarHelper.GetInstance();
        return db.Queryable<Womdaa>().Where(a =>
                a.Daa015 == daa020 &&
                a.Daa002 == item)
            .Select<Womdaa>(a => new Womdaa
            {
                Daa001 = a.Daa001
                // DetailMem = a.DetailMem
            }).ToList();
    }

    public List<Womdaa> getBoardItem(string lineNo)
    {
        var db = SqlSugarHelper.GetInstance();
        return db.Queryable<Womdaa>()
            .Where(a =>
                a.Daa015 == lineNo)
            .Select<Womdaa>(a =>
                new Womdaa
                {
                    Daa002 = a.Daa002,
                    Daa003 = a.Daa003
                    //DetailMem = a.DetailMem
                }).OrderBy(a => a.Daa002).ToList();
    }

    public List<QsQaItemXj> getPage(XJPageResult queryObj)
    {
        var db = SqlSugarHelper.GetInstance();

        var id = Convert.ToDecimal(queryObj.id);

        string[]? lineNo = null;

        if (StringUtil.IsNotNullOrEmpty(queryObj.createUser))
            lineNo = _baseService.getUserLineNo(queryObj.createUser);

        return db
            .Queryable<QsQaItemXj, Womdaa>((s, a) =>
                new JoinQueryInfos(
                    JoinType.Inner, s.BillNo == a.Daa001
                )
            )
            .WhereIF(lineNo != null && lineNo.Length > 0,
                (s, a) => lineNo.Contains(a.Daa015))
            .WhereIF(
                StringUtil.IsNotNullOrEmpty(queryObj.result) &&
                "未完成".Equals(queryObj.result),
                (s, a) => s.FcheckResu == null || s.Fsubmit == null ||
                          s.Fsubmit == 0)
            .WhereIF(
                StringUtil.IsNotNullOrEmpty(queryObj.result) &&
                !"未完成".Equals(queryObj.result),
                (s, a) => s.FcheckResu != null && s.Fsubmit != null &&
                          s.Fsubmit != 0)
            .WhereIF(id > 0, (s, a) => s.Id == id)
            .Select((s, a) => new QsQaItemXj
            {
                CatQty = a.Daa008,
                MoidNum = s.MoidNum,
                Daa020 = a.Daa015,
                // DetailMem = a.DetailMem,
                TaskNo = a.Daa001,
                Id = s.Id,
                CreateBy = s.CreateBy,
                CreateDate = s.CreateDate,
                ReleaseNo = s.ReleaseNo,
                ItemNo = s.ItemNo,
                ItemUnit = s.ItemUnit,
                BillNo = s.BillNo,
                FcheckBy = s.FcheckBy,
                FcheckResu = s.FcheckResu,
                Remarks = s.Remarks,
                PlanQty = a.Daa008, // 工单数量
                ItemModel = a.Daa004, // 规格
                ItemName = a.Daa003, // 产品名称
                ItemId = a.Daa002 // 产品名称
                // Fsubmit = s.Fsubmit // 若需要返回 Fsubmit 字段
            })
            .OrderBy(s => s.CreateDate, OrderByType.Desc)
            .ToPageList(queryObj.PageIndex, queryObj.Limit);
    }
    /*
    public List<QsQaItemXj> getPage(XJPageResult queryObj)
    {
        var db = SqlSugarHelper.GetInstance();

        var id = Convert.ToDecimal(queryObj.id);

        string[]? lineNo = null;

        if (StringUtil.IsNotNullOrEmpty(queryObj.createUser))
            lineNo = _baseService.getUserLineNo(queryObj.createUser);

        return db
            .Queryable<QsQaItemXj, Womdaa >((s, a) =>
                new JoinQueryInfos(
                    JoinType.Inner, s.BillNo == a.Daa001
                )
            )
            .WhereIF(lineNo != null && lineNo.Length > 0,
                (s, a) => lineNo.Contains(a.Daa015))
            .WhereIF(
                StringUtil.IsNotNullOrEmpty(queryObj.result) &&
                "未完成".Equals(queryObj.result),
                (s, a) => s.FcheckResu == null)
            .WhereIF(
                StringUtil.IsNotNullOrEmpty(queryObj.result) &&
                !"未完成".Equals(queryObj.result),
                (s, a) => s.FcheckResu != null)
            .WhereIF(id > 0, (s, a) => s.Id == id)
            .Select((s, a) => new QsQaItemXj
            {
                CatQty = a.Daa008,
                MoidNum = s.MoidNum,
                Daa020 = a.Daa015,
                // DetailMem = a.DetailMem,
                TaskNo = a.Daa001,
                Id = s.Id,
                CreateBy = s.CreateBy,
                CreateDate = s.CreateDate,
                ReleaseNo = s.ReleaseNo,
                ItemNo = s.ItemNo,
                ItemUnit = s.ItemUnit,
                BillNo = s.BillNo,
                FcheckBy = s.FcheckBy,
                FcheckResu = s.FcheckResu,
                Remarks = s.Remarks,

                PlanQty = a.Daa008,//工单数量
                ItemModel = a.Daa004,//规格
                ItemName = a.Daa003,//产品名称
                ItemId =  a.Daa002//产品名称
            }).OrderBy(s => s.CreateDate, OrderByType.Desc)
            .ToPageList(queryObj.PageIndex, queryObj.Limit);
    }
    */


    public List<QsQaItemXj01> setJYItem(decimal itemId)
    {
        var db = SqlSugarHelper.GetInstance();

        var count = db.Queryable<MesQa>().Where(s => s.QsType == "2"
            && s.ItemId == itemId && s.Fsubmit == 1).Count();

        if (count <= 0) return new List<QsQaItemXj01>();

        return db
            .Queryable<MesQualityStandard>()
            .Where(b => b.QsType == "2"
                        && b.ItemId == itemId).Select(b => new QsQaItemXj01
            {
                ProjName = b.ProjName,
                ItemMod = b.ItemMod,
                InspectionMethod = b.InspectionMethod,
                UsingInstruments = b.UsingInstruments,
                LevelNum = SqlFunc.IsNull(
                    SqlFunc.IsNull(
                        Convert.ToDecimal(b.LevelNum) *
                        Convert.ToDecimal(b.InspectionLevel), 1),
                    Convert.ToDecimal(b.InspectionLevel)),
                MaxValue = Convert.ToDecimal(b.MaxValue),
                StandardValue = Convert.ToDecimal(b.StandardValue),
                MinValue = Convert.ToDecimal(b.MinValue),
                Notes = b.Notes,
                FcheckLevel = b.FcheckLevel,
                FacLevel = b.FacLevel,
                QsCode = b.QsCode,
                QsName = b.QsName,
                result = "未检测",
                isCheck = 0,
                Picture = b.Picture,
                Picturename = b.Picturename
            }).ToList();
    }

    public List<QsQaItemXj01> getJYItem(decimal? pid, decimal? id)
    {
        var db = SqlSugarHelper.GetInstance();
        var qsQaItemXj01s = db.Queryable<QsQaItemXj01>()
            .WhereIF(pid > 0, a => a.Pid == pid)
            .WhereIF(id > 0, a => a.Id == id)
            .ToList();

        var array = qsQaItemXj01s.Select(s => s.Id).ToArray();
        var qsQaItemDetails = db.Queryable<QsQaItemXj02>()
            .Where(s => array.Contains(s.Pid))
            .GroupBy(s => s.Pid)
            .Select(s => new
            {
                s.Pid,
                count = SqlFunc.AggregateCount(s.Id)
            }).ToList();

        qsQaItemXj01s.ForEach(s =>
        {
            var find = qsQaItemDetails.Find(a => s.Id == a.Pid);
            if (find == null)
            {
                s.isCheck = 0;
                s.result = "未完成";
            }
            else
            {
                s.isCheck = find.count;
                if (find.count == s.LevelNum && s.IsPass == 1)
                    s.result = "合格";
                else if (find.count == s.LevelNum && s.IsPass == 0)
                    s.result = "不合格";
                else
                    s.result = "未完成";
            }

            //过滤出图片id不为空的数据转为base64
            if (s.Picture is { Length: > 0 })
                s.imageData = Convert.ToBase64String(s.Picture);
        });

        //排序，未完成的排在前面
        qsQaItemXj01s = qsQaItemXj01s.OrderBy(s => s.isCheck).ToList();

        return qsQaItemXj01s;
    }


    public int save(XJDto xjDto)
    {
        var xj = xjDto.from;
        var items = xjDto.items;
        var userNo = xjDto.userNo;

        SqlSugarHelper.UseTransactionWithOracle(db =>
        {
            var commit = 0;

            xj.CreateBy = userNo;
            xj.CreateDate = DateTime.Now;

            var pid = db.Insertable(xj).ExecuteReturnIdentity();

            xj.Id = pid;

            xjDto.gid = pid;

            foreach (var item in items) item.Pid = pid;

            commit += db.Insertable(items).ExecuteCommand();

            return commit;
        });

        xjDto.items = getJYItem(xj.Id, null);

        xjDto.items.ForEach(s =>
        {
            if (s.MaxValue != null || s.StandardValue != null ||
                s.MinValue != null) return;
            var detail = new QsQaItemXj02();
            detail.Pid = s.Id;
            detail.Gid = xjDto.gid;
            detail.Fstand = "√";
            detail.FcheckResu = "1";
            detail.UpdateBy = xjDto.userNo;
            detail.count = (int?)s.LevelNum;
            SetQSItemDetail(detail);
        });

        return Convert.ToInt32(xj.Id);
    }

    public int saveItem(XJDto xjDto)
    {
        var items = xjDto.items;
        var userNo = xjDto.userNo;

        SqlSugarHelper.UseTransactionWithOracle(db =>
        {
            foreach (var item in items) item.Pid = xjDto.gid;

            return db.Insertable(items).ExecuteCommand();
        });

        xjDto.items = getJYItem(xjDto.gid, null);

        xjDto.items.ForEach(s =>
        {
            if (s.MaxValue != null || s.StandardValue != null ||
                s.MinValue != null) return;
            var detail = new QsQaItemXj02();
            detail.Pid = s.Id;
            detail.Gid = xjDto.gid;
            detail.Fstand = "√";
            detail.FcheckResu = "1";
            detail.UpdateBy = xjDto.userNo;
            detail.count = (int?)s.LevelNum;
            SetQSItemDetail(detail);
        });

        return Convert.ToInt32(xjDto.gid);
    }


    public XJDto getXjDetail02ById(decimal? id)
    {
        var xjDto = new XJDto();

        var db = SqlSugarHelper.GetInstance();
        var qsQaItemXj01 =
            db.Queryable<QsQaItemXj01>().Single(s => s.Id == id);

        if (qsQaItemXj01.IsPass == 0)
            qsQaItemXj01.result = "不合格";
        else if (qsQaItemXj01.IsPass == 1)
            qsQaItemXj01.result = "合格";
        else
            qsQaItemXj01.result = "未完成";

        if (qsQaItemXj01.Picture is { Length: > 0 })
            qsQaItemXj01.imageData =
                Convert.ToBase64String(qsQaItemXj01.Picture);

        xjDto.ItemXj01 = qsQaItemXj01;


        xjDto.ItemXj02s = db.Queryable<QsQaItemXj02>().Where(s => s.Pid == id)
            .ToList();

        return xjDto;
    }

    public int SetQSItemDetail(QsQaItemXj02 detail)
    {
        var oracle = SqlSugarHelper.UseTransactionWithOracle(db =>
        {
            List<QsQaItemXj02> result = new();
            for (var i = 0; i < detail.count; i++)
            {
                var item = new QsQaItemXj02();
                item.Gid = detail.Gid;
                item.Pid = detail.Pid;
                item.Fstand = detail.Fstand;
                item.FcheckResu = detail.FcheckResu;
                item.CreateBy = detail.UpdateBy;
                item.CreateDate = DateTime.Now;
                result.Add(item);
            }

            return db.Insertable(result).ExecuteCommand();
        });

        detail.CreateBy = detail.UpdateBy;

        autoResult(detail);

        return oracle;
    }

    private int autoResult(QsQaItemXj02 detail)
    {
        var db = SqlSugarHelper.GetInstance();

        // Single 没有查询到结果时返回Null，如果结果大于1条会抛出错误
        var QsQaItemXj01 = db.Queryable<QsQaItemXj01>()
            .Single(s => s.Id == detail.Pid);

        if (QsQaItemXj01 == null) return 0;

        var count = db.Queryable<QsQaItemXj02>()
            .Where(s => s.Pid == detail.Pid).Count();


        var result = 0;

        if (QsQaItemXj01.LevelNum != count) return 0;

        var passCount = db.Queryable<QsQaItemXj02>()
            .Where(s => s.Pid == detail.Pid && s.Fstand == "√").Count();

        if (count == passCount) result = 1;

        var useTransactionWithOracle =
            SqlSugarHelper.UseTransactionWithOracle(db =>
            {
                var commit = 0;
                commit += db.Updateable<QsQaItemXj01>()
                    .SetColumns(s => s.IsPass == result)
                    .Where(s => s.Id == detail.Pid)
                    .ExecuteCommand();

                return commit;
            });

        //获取检验单的检验项目理论个数
        var sum = db.Queryable<QsQaItemXj01>()
            .Where(s => s.Pid == detail.Gid).Sum(it => it.LevelNum);

        if (sum == null || sum == 0) return 1;

        //获取检验单下的检验项目实际个数
        var icount = db.Queryable<QsQaItemXj02>()
            .Where(s => s.Gid == detail.Gid).Count();

        if (icount == 0) return 1;

        //实际个数等于理论个数时对检验单进行判定
        if (sum == icount)
        {
            var FcheckResu = "不合格";

            //获取这个检验单下的所有合格的检验结果
            passCount = db.Queryable<QsQaItemXj02>()
                .Where(s => s.Gid == detail.Gid && s.Fstand == "√").Count();

            //合格的检验结果等于总检验数视为合格
            if (icount == passCount) FcheckResu = "合格";

            SqlSugarHelper.UseTransactionWithOracle(db =>
            {
                return db.Updateable<QsQaItemXj>()
                    .SetColumns(s => s.FcheckResu == FcheckResu)
                    .SetColumns(s => s.FcheckDate == DateTime.Now)
                    .SetColumns(s => s.FcheckBy == detail.CreateBy)
                    .SetColumns(s => s.LastupdateBy == detail.CreateBy)
                    .SetColumns(s => s.LastupdateDate == DateTime.Now)
                    .Where(s => s.Id == detail.Gid)
                    .ExecuteCommand();
            });
        }

        return useTransactionWithOracle;
    }

    public int UpdateQSItemDetail(QsQaItemXj02 detail)
    {
        var withOracle = SqlSugarHelper.UseTransactionWithOracle(db =>
        {
            return db.Updateable<QsQaItemXj02>()
                .SetColumns(s => s.UpdateBy == detail.UpdateBy)
                .SetColumns(s => s.UpdateDate == DateTime.Now)
                .SetColumnsIF(StringUtil.IsNotNullOrEmpty(detail.Fstand),
                    s => s.Fstand == detail.Fstand)
                .SetColumnsIF(StringUtil.IsNotNullOrEmpty(detail.FcheckResu),
                    s => s.FcheckResu == detail.FcheckResu)
                .Where(s => s.Id == detail.Id)
                .ExecuteCommand();
        });

        detail.CreateBy = detail.UpdateBy;

        withOracle += autoResult(detail);

        return withOracle;
    }

    //删除主表并且连级删除子表和孙表
    public int removeXJ(decimal? id)
    {
        var withOracle = SqlSugarHelper.UseTransactionWithOracle(db =>
        {
            var commit = 0;
            commit += db.Deleteable<QsQaItemXj>().Where(s => s.Id == id)
                .ExecuteCommand();
            commit += db.Deleteable<QsQaItemXj01>().Where(s => s.Pid == id)
                .ExecuteCommand();
            commit += db.Deleteable<QsQaItemXj02>().Where(s => s.Gid == id)
                .ExecuteCommand();
            return commit;
        });

        return withOracle;
    }

    //主表修改备注字段
    public int saveRemarksGid(XJDto dto)
    {
        return SqlSugarHelper.UseTransactionWithOracle(db =>
        {
            return db.Updateable<QsQaItemXj>()
                .SetColumns(it =>
                    it.Remarks == dto.Remarks) //SetColumns是可以叠加的 写2个就2个字段赋值
                .Where(it => it.Id == dto.gid)
                .ExecuteCommand();
        });
    }

    //子表修改备注字段
    public int saveRemarksPid(XJDto dto)
    {
        return SqlSugarHelper.UseTransactionWithOracle(db =>
        {
            return db.Updateable<QsQaItemXj01>()
                .SetColumns(it =>
                    it.Remarks == dto.Remarks) //SetColumns是可以叠加的 写2个就2个字段赋值
                .Where(it => it.Id == dto.pid)
                .ExecuteCommand();
        });
    }

    //孙表修改备注字段
    public int saveRemarksById(XJDto dto)
    {
        return SqlSugarHelper.UseTransactionWithOracle(db =>
        {
            return db.Updateable<QsQaItemXj02>()
                .SetColumns(it =>
                    it.Remarks == dto.Remarks) //SetColumns是可以叠加的 写2个就2个字段赋值
                .Where(it => it.Id == dto.id)
                .ExecuteCommand();
        });
    }

    public bool XJQaSubmit(QsItem item)
    {
        var (factory, company) = UserUtil.GetFactory(item.userNo);
        try
        {
            // 定义输出参数
            var outputResult = new SugarParameter("o_Result", null,
                DbType.Int32, ParameterDirection.Output,
                4000);

            var outputMessage = new SugarParameter("o_Msg", null, DbType.String,
                ParameterDirection.Output, 4000);

            // 定义输入参数
            var parameters = new List<SugarParameter>
            {
                new("p_Id", item.gid, DbType.Int32, ParameterDirection.Input),
                new("p_Flag", 1, DbType.Int32, ParameterDirection.Input),
                new("p_User", item.userNo, DbType.String,
                    ParameterDirection.Input),
                outputResult,
                outputMessage
            };

            var db = SqlSugarHelper.GetInstance();

            // 使用 SqlSugar 执行存储过程
            db.Ado.ExecuteCommand(
                "BEGIN PRC_WOMDAA_XJ_UPDATE_RES(:p_Id,:p_Flag,:p_User,:o_Result, :o_Msg); END;",
                parameters.ToArray());

            // 获取输出参数的值
            var resultValue = outputResult.Value?.ToString();
            var messageValue = outputMessage.Value?.ToString();

            if ("1".Equals(resultValue)) throw new Exception(messageValue);
            if ("0".Equals(resultValue)) throw new Exception(messageValue);

            return true;
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
    }

    public List<MesLine> getWorkshop()
    {
        var db = SqlSugarHelper.GetInstance();

        return db.Queryable<Womdaa, MesLine>((a, b) =>
                new JoinQueryInfos(JoinType.Left, a.Daa015 == b.LineNo))
            .GroupBy((a, b) => new
            {
                a.Daa015, b.LineName
            })
            .Select((a, b) => new MesLine
            {
                LineNo = a.Daa015,
                LineName = b.LineName
            }).OrderBy("a.Daa015").ToList();
    }
}