BOM列表二开过滤方案 获取非最高BOM版本

先在系统中设置对应的BOM列表过滤方案 参考最高BOM版本的设置 对应的过滤条件关键字自己设置一个
本文设置为了“UnHBom”
具体二开列表插件代码如下：
 
using Kingdee.BOS.Core.List.PlugIn;
using Kingdee.BOS.Core.List.PlugIn.Args;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Kingdee.BOS.Util;
using Kingdee.BOS.ServiceHelper;
using Kingdee.BOS.Core.Permission;
using Kingdee.K3.Core.MFG;
using Kingdee.BOS;
using Kingdee.BOS.Core.Metadata;
using Kingdee.K3.Core.MFG.EnumConst;
using Kingdee.BOS.Core.Enums;
using System.Data;
using Kingdee.BOS.Core.SqlBuilder;
using Kingdee.K3.MFG.ServiceHelper;
namespace Kingdee.K3.MFG.ENG.Business.PlugIn
{
    public class BOMFilterEK : AbstractListPlugIn
    {
        public override void PrepareFilterParameter(FilterArgs e)
        {
            #region 非最高版本BOM UnHBom自己添加的过滤方案条件关键字
            //如果过滤条件含有最新结果关键字，则在此基础上处理，以支持最新结果内置方案另存增加条件
            if (!e.FilterString.IsNullOrEmptyOrWhiteSpace() && e.FilterString.IndexOf("UnHBom") >= 0)
            {
                List<long> orgIds = PermissionServiceHelper.GetPermissionOrg(this.Context, new BusinessObject() { Id = MFGFormIdConst.SubSys_ENG.BomBill }, PermissionConst.View);
                if (orgIds.IsEmpty()) return;

                List<long> unhighestBomIds = GetDefaultBomIdsByOrgIds(this.Context, orgIds, true);

                if (!unhighestBomIds.IsEmpty())
                {
                    SqlParam sqlParam = new SqlParam("@PKValue", KDDbType.udt_inttable, unhighestBomIds.Distinct().ToArray());

                    e.SqlParams = new List<SqlParam>();
                    e.SqlParams.Add(sqlParam);

                    var cardSql = StringUtils.GetSqlWithCardinality(unhighestBomIds.Distinct().Count(), "@PKValue", 1);
                    ExtJoinTableDescription joinTable = new ExtJoinTableDescription();
                    joinTable.TableName = cardSql;
                    joinTable.ScourceKey = "FID";
                    joinTable.FieldName = "FID";
                    joinTable.TableNameAs = "Tmp";
                    e.ExtJoinTables = new List<ExtJoinTableDescription>();
                    e.ExtJoinTables.Add(joinTable);

                    e.FilterString = e.FilterString.Replace("UnHBom", "");
                }
            }
            #endregion
        }
		/// <summary>
        /// 根据使用组织获取，全部物料的非最高版本BOM
        /// </summary>
        /// <param name="ctx"></param>
        /// <param name="useOrgIds">组织</param>
        /// <param name="onlyAudit">是否已审核</param>
        /// <returns></returns>
        public List<long> GetDefaultBomIdsByOrgIds(Context ctx, List<long> useOrgIds, bool onlyAudit)
        {
            FormMetadata bomMetadata = (FormMetadata)MetaDataServiceHelper.Load(ctx, MFGFormIdConst.SubSys_ENG.BomBill) as FormMetadata;
            string policyType = Convert.ToString(MFGServiceHelper.GetBaseDataPolicyType(ctx, MFGFormIdConst.SubSys_ENG.BomBill));
            long useOrgId = useOrgIds.FirstOrDefault();
            if ("1".Equals(policyType)) useOrgId = -1;

            List<SqlParam> sqlParam = new List<SqlParam>();
            string documentStatusStr = string.Empty;
            if (onlyAudit)
            {
                documentStatusStr = string.Format(" and v1.fdocumentstatus = '{0}' ", (char)(int)Enums.Enu_BillStatus.KdApproved);
            }

            List<int> lstType = new List<int>() { 99, 0, (int)Enums.Enu_BOMUse.ZZBOM, (int)Enums.Enu_BOMUse.WWBOM, (int)Enums.Enu_BOMUse.ZUZBOM };
            //标准BOM
            StringBuilder sbSql = new StringBuilder();

            sbSql.AppendLine(" SELECT DISTINCT * FROM ( ");
			 sbSql.AppendLine(" select t0.FMaterialId, t0.FMasterId, v1.FId as FBomId, v1.FNUMBER as FBomNumber, v1.FUseOrgId as FUseOrgId, v1.FIsDefault as FIsDefault, v1.FBomUse ");
            sbSql.AppendFormat(", case t1.FErpClsId when '{0}' then {1} when '{2}' then {3} when '{4}' then {5} when '{6}' then {7} else 0 end as FFilter, t1.FErpClsId ",
            (int)BOSEnums.Enu_MaterialType.KdZZ, (int)Enums.Enu_BOMUse.ZZBOM,
               (int)BOSEnums.Enu_MaterialType.KdXN, (int)Enums.Enu_BOMUse.ZZBOM,
               (int)BOSEnums.Enu_MaterialType.KdWW, (int)Enums.Enu_BOMUse.WWBOM,
               (int)BOSEnums.Enu_MaterialType.KdPZ, (int)Enums.Enu_BOMUse.ZZBOM
               ).AppendLine();
            sbSql.AppendLine(" from T_BD_MATERIAL t0 ");
            sbSql.AppendLine(" inner join T_BD_MATERIALBASE t1 on t0.FMATERIALID=t1.FMATERIALID ");
            sbSql.AppendLine(" inner join T_ENG_BOM v1 on t0.FMaterialId=v1.FMaterialId ");

            if (useOrgId != -1)
            {
                var cardSqlForMtrl = StringUtils.GetSqlWithCardinality(useOrgIds.Distinct().Count(), "@FID1", 1);
                sbSql.AppendFormat(" INNER JOIN {0} T ON T.FID = v1.FUSEORGID ", cardSqlForMtrl).AppendLine();
                sqlParam.Add(new SqlParam("@FID1", KDDbType.udt_inttable, useOrgIds.Distinct().ToArray()));
            }
            //v1.fforbidstatus BOM禁用状态
            sbSql.AppendFormat(" WHERE v1.fbomcategory = '{0}'", (int)Enums.Enu_BOMCateGory.StandardBOM);
            sbSql.AppendFormat(" {0} and v1.fforbidstatus='{1}' ", documentStatusStr, (char)(int)Enums.Enu_ForbidStatus.KdNormal);
            sbSql.AppendFormat(" and v1.FBomUse in ({0})", string.Join(",", lstType)).AppendLine();

            //以下UNION ALL配置BOM数据
            sbSql.AppendLine(" UNION ALL ");
			   sbSql.AppendFormat(@" select t0.FMaterialId, t0.FMasterId, v1.FId as FBomId, v1.FNUMBER as FBomNumber, v1.FUseOrgId as FUseOrgId, v1.FIsDefault as FIsDefault 
 , v1.FBomUse, 88 AS FFilter, t1.FErpClsId 
 from T_BD_MATERIAL t0 
 inner join T_BD_MATERIALBASE t1 on t0.FMATERIALID=t1.FMATERIALID 
 inner join T_ENG_BOM v1 on t0.FMaterialId=v1.FMaterialId ").AppendLine();

            if (useOrgId != -1)
            {
                var cardSqlForMtrl = StringUtils.GetSqlWithCardinality(useOrgIds.Distinct().Count(), "@FID2", 1);
                sbSql.AppendFormat(" INNER JOIN {0} T ON T.FID = v1.FUSEORGID ", cardSqlForMtrl).AppendLine();
                sqlParam.Add(new SqlParam("@FID2", KDDbType.udt_inttable, useOrgIds.Distinct().ToArray()));
            }
            //v1.fforbidstatus BOM禁用状态
            sbSql.AppendFormat(@" where T1.FERPCLSID = '{0}' and v1.fbomcategory = '{1}' and v1.fforbidstatus='A' {2} ",
                (int)Enums.Enu_MaterialType.KdPZ, (int)Enums.Enu_BOMCateGory.ConfigBOM, documentStatusStr).AppendLine();

            sbSql.AppendLine(" ) TT ORDER BY FBomNumber DESC ");


            //分别是（物料内码，BOM内码，物料MasterId，使用组织内码）
            List<Tuple<long, long, long, long>> lstDefDataEntities = new List<Tuple<long, long, long, long>>();
            using (IDataReader dataEntities = DBServiceHelper.ExecuteReader(ctx, sbSql.ToString(), sqlParam))
            {
				 while (dataEntities.Read())
                {
                    long bomUse = Convert.ToInt64(dataEntities[6]);
                    long filter = Convert.ToInt64(dataEntities[7]);

                    Tuple<long, long, long, long> bomCahce = new Tuple<long, long, long, long>(Convert.ToInt64(dataEntities[0]), Convert.ToInt64(dataEntities[2]), Convert.ToInt64(dataEntities[1]), Convert.ToInt64(dataEntities[4]));
                    if (filter != 88)
                    {
                        int erpClsId = Convert.ToInt32(dataEntities[8]);
                        if (erpClsId != (int)Enums.Enu_MaterialType.KdPZ && (bomUse == 99 || bomUse == filter))
                        {
                            lstDefDataEntities.Add(bomCahce);
                        }
                    }
                    else
                    {
                        lstDefDataEntities.Add(bomCahce);
                    }
                }
            }

            List<long> result = new List<long>();
            Dictionary<string, long> dctDefultBomByOrg = new Dictionary<string, long>();

            List<long> hiBomIds = new List<long>();
            foreach (var dataEntity in lstDefDataEntities)
            {
                long lBomId = dataEntity.Item2;
                if (lBomId <= 0) continue;
				 long lMaterialMasterId = dataEntity.Item3;
                long lUseOrgId = "1".Equals(policyType) ? -1 : dataEntity.Item4;
                string mapKey = string.Format("{0}_{1}", lMaterialMasterId, lUseOrgId);

                long lsDefBomByOrgItems = 0;
                //根据物料masterid_orgId，取第一个为最高版本BOM内码
                if (!dctDefultBomByOrg.TryGetValue(mapKey, out lsDefBomByOrgItems))
                {
                    dctDefultBomByOrg[mapKey] = lBomId;
                }
                if (dctDefultBomByOrg.TryGetValue(mapKey, out lsDefBomByOrgItems))
                {
                    if (lsDefBomByOrgItems != lBomId && !result.Contains(lBomId))
                    {
                        result.Add(lBomId);
                    }
                }

            }

            lstDefDataEntities = null;
            return result;
        }
    }
}