using MES.Service.DB;
using MES.Service.Modes;

namespace MES.Service.service.QC;

public class BaseService
{
    public string[]? getUserLineNo(string userNo)
    {
        //MesLineUser
        var db = SqlSugarHelper.GetInstance();
        var mesLineUsers = db.Queryable<MesLineUser>()
            .Where(a => a.Fcode == userNo)
            .ToList();
        return mesLineUsers.Select(s => s.LineNo).ToArray();
    }

    public int saveImage(List<MesQsImage> entity)
    {
        var mesQsImages = entity.Where(item => item.Id == null).ToList();

        mesQsImages.ForEach(s =>
        {
            if (!string.IsNullOrEmpty(s.base64Date))
                //将base64转换为byte数组
                s.ImageData = Convert.FromBase64String(s.base64Date);
        });

        return SqlSugarHelper.UseTransactionWithOracle(db =>
            db.Insertable(mesQsImages).ExecuteCommand());
    }

    public int removeImage(decimal? id)
    {
        return SqlSugarHelper.UseTransactionWithOracle(db => db
            .Deleteable(new MesQsImage { Id = id })
            .ExecuteCommand());
    }

    public List<MesQsImage> getByFid(decimal id, string qsType)
    {
        var db = SqlSugarHelper.GetInstance();
        var mesQsImages = db.Queryable<MesQsImage>()
            .Where(s => s.Fid == id && s.QsType == qsType).ToList();
        mesQsImages.ForEach(s =>
        {
            if (s.ImageData is { Length: > 0 })
                s.base64Date = Convert.ToBase64String(s.ImageData);
        });
        return mesQsImages;
    }

    /// <summary>
    /// 获取QC车间列表（根据视图 V_MES_QC_CJ）
    /// </summary>
    /// <returns>车间列表</returns>
    public List<dynamic> GetQCDepartments()
    {
        var db = SqlSugarHelper.GetInstance();
        var sql = @"
            SELECT
                T.DEPARTMENTNAME as departmentname,
                T.DEPARTMENTID as departmentid
            FROM SYS_DEPARTMENT T
            WHERE T.DEPARTMENTID IN (
                2951904,2951874,2973564,3131720,3252567,3306857,3335722,2306534,2670262,
                2365021,2365022,2749927,2306535,2558979,2558980,2558981,2558982,1606194,
                1608020,1608021,1606193,1608022,1638037,1682634,1802281,1802278,1802279,
                1802280,1999190,1999191,2272979,2513368,2513369,2513370,2513371,2558971,
                2558972,2558973,2558974,2558975,2558976,2558977,2749970,2749946,2749947,
                2749948,2670253,2670258,2670260,2513372,2513373,2513374,2513375,2513376,
                2670261,139517,139518,139519,209814,211639,221640,378794,440720,441918,
                441919,502188,507542,507545,507546,139524
            )
            ORDER BY T.DEPARTMENTNAME";
        
        return db.Ado.SqlQuery<dynamic>(sql);
    }

    /// <summary>
    /// 获取QC线体列表（根据车间ID筛选，基于视图 V_MES_QC_XT）
    /// </summary>
    /// <param name="departmentId">车间ID，如果为空则返回所有QC线体</param>
    /// <returns>线体列表</returns>
    public List<MesLine> GetQCLines(string? departmentId)
    {
        var db = SqlSugarHelper.GetInstance();
        
        if (string.IsNullOrEmpty(departmentId))
        {
            // 如果没有指定车间，返回所有QC线体
            var sql = @"
                SELECT E.LINE_NO as LineNo, E.LINE_NAME as LineName, E.DEPOT_ID as DepotId
                FROM MES_LINE E
                LEFT JOIN (
                    SELECT T.DEPARTMENTID
                    FROM SYS_DEPARTMENT T
                    WHERE T.DEPARTMENTID IN (
                        2951904,2951874,2973564,3131720,3252567,3306857,3335722,2306534,2670262,
                        2365021,2365022,2749927,2306535,2558979,2558980,2558981,2558982,1606194,
                        1608020,1608021,1606193,1608022,1638037,1682634,1802281,1802278,1802279,
                        1802280,1999190,1999191,2272979,2513368,2513369,2513370,2513371,2558971,
                        2558972,2558973,2558974,2558975,2558976,2558977,2749970,2749946,2749947,
                        2749948,2670253,2670258,2670260,2513372,2513373,2513374,2513375,2513376,
                        2670261,139517,139518,139519,209814,211639,221640,378794,440720,441918,
                        441919,502188,507542,507545,507546,139524
                    )
                ) C ON C.DEPARTMENTID = E.DEPOT_ID
                WHERE C.DEPARTMENTID IS NOT NULL
                ORDER BY E.LINE_NO DESC";
            
            return db.Ado.SqlQuery<MesLine>(sql);
        }
        else
        {
            // 如果指定了车间，只返回该车间下的线体
            return db.Queryable<MesLine>()
                .Where(e => e.DepotId.ToString() == departmentId)
                .OrderBy(e => e.LineNo, SqlSugar.OrderByType.Desc)
                .Select(e => new MesLine
                {
                    LineNo = e.LineNo,
                    LineName = e.LineName,
                    DepotId = e.DepotId
                })
                .ToList();
        }
    }
}