BOM列表二开过滤方案 获取非最高BOM版本原创
4人赞赏了该文章
1,008次浏览
编辑于2022年02月11日 09:48:51
先在系统中设置对应的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; } } }