直接SQL账表联查单据原创
金蝶云·星空
金蝶云·星空-BOS平台
金蝶云·星空-协同开发
金蝶云社区-weiweibrother
weiweibrother
54人赞赏了该文章 729次浏览 未经作者许可，禁止转载编辑于2023年09月19日 16:29:43

①直接SQL账表不支持CellDbClick（账表可以使用）事件，需覆写EntityRowDoubleClick事件；当关联的单据号用逗号（英文格式）分隔时，需进行解析，使得其双击时，展开为单据列表（参考销售订单执行明细表，预收、收款单号列，双击跳转收款单列表）。

using Kingdee.BOS.Core.Bill;
using Kingdee.BOS.Core.DynamicForm;
using Kingdee.BOS.Core.DynamicForm.PlugIn;
using Kingdee.BOS.Core.DynamicForm.PlugIn.Args;
using Kingdee.BOS.Core.List;
using Kingdee.BOS.Core.Metadata;
using Kingdee.BOS.Core.Permission;
using Kingdee.BOS.Resource;
using Kingdee.BOS.ServiceHelper;
using Kingdee.BOS.Util;
using Kingdee.BOS.Web.Report.SQLReport;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Reflection;
using System.Linq;
using Kingdee.BOS.Contracts;
using Kingdee.BOS.Core.SqlBuilder;
using Kingdee.BOS.Orm.DataEntity;
using Kingdee.BOS.Core.CommonFilter;
using Kingdee.K3.SCM.Core.SAL;
using Kingdee.K3.SCM.ServiceHelper;
using Kingdee.BOS.Core.Metadata.FormElement;
namespace RPTMUTLTrackList
{
    public class ReportBillList : AbstractDynamicFormPlugIn
    {
        public override void EntityRowDoubleClick(EntityRowClickEventArgs e)
        {
            base.EntityRowDoubleClick(e);
            var view = this.View as SQLReportView;
            string text = e.ColKey.ToString().Trim();//获取列名
            string text2 = view.GetCurrentRowValue(text).ToString();//获取当前列对应行，即单元格
            string text3 = string.Empty;
            //string text4 = string.Empty;
            BillShowParameter billShowParameter = new BillShowParameter();
            billShowParameter.Status = OperationStatus.VIEW;
            //string formId = "SAL_OUTSTOCK";
            string key;
            if (view == null)
            {
                return;
            }
            switch (key = text.ToUpperInvariant())
            {
                case "销售订单号":
                    // var currentRowValue = GetCurrentRowValue(view, "单据编号");
                    // 版本PT-146850  [7.5.1.202008]后用此方法
                    {
                        String text4 = "SAL_SaleOrder";
                        var currentysRowValue = view.GetCurrentRowValue("销售订单号").ToString().Trim().ToString();
                        var ysbillNo = currentysRowValue.ToString();
                        string[] array3 = currentysRowValue.Split(new char[] { ',' });
                        List<string> list3 = new List<string>();
                        if (currentysRowValue == null)
                        {
                            return;
                        }
                        if (array3.Length == 1)
                        {
                            this.SetBillOpenParam(ysbillNo, text4, ref billShowParameter);
                            //this.SetBillOpenParam(text2, text4, ref billShowParameter);
                        }
                        else
                        {
                            for (int j = 0; j < array3.Length; j++)
                            {
                                list3.Add(string.Format(" '{0}'", array3[j]));
                            }
                            text3 = string.Join(",", this.GetPKValue(string.Join(",", list3.ToArray()), text4).ToArray());
                            if (text3.Trim() != "" && text3.Trim() != "0")
                            {
                                this.ViewShowList(text4, text3);
                            }
                        }
                        break;
                    }
                case "出库单号":
                    {
                        String text4 = "SAL_OUTSTOCK";
                        var currentysRowValue = view.GetCurrentRowValue("出库单号").ToString().Trim().ToString();
                        var ysbillNo = currentysRowValue.ToString();
                        string[] array3 = currentysRowValue.Split(new char[] { ',' });
                        List<string> list3 = new List<string>();
                        if (currentysRowValue == null)
                        {
                            return;
                        }
                        if (array3.Length == 1)
                        {
                            this.SetBillOpenParam(ysbillNo, text4, ref billShowParameter);
                            //this.SetBillOpenParam(text2, text4, ref billShowParameter);
                        }
                        else
                        {
                            for (int j = 0; j < array3.Length; j++)
                            {
                                list3.Add(string.Format(" '{0}'", array3[j]));
                            }
                            text3 = string.Join(",", this.GetPKValue(string.Join(",", list3.ToArray()), text4).ToArray());
                            if (text3.Trim() != "" && text3.Trim() != "0")
                            {
                                this.ViewShowList(text4, text3);
                            }
                        }
                        break;
                    }
                case "退料单号":
                    {
                        String text4 = "SAL_RETURNSTOCK";
                        var currentysRowValue = view.GetCurrentRowValue("退料单号").ToString().Trim().ToString();
                        var ysbillNo = currentysRowValue.ToString();
                        string[] array3 = currentysRowValue.Split(new char[] { ',' });
                        List<string> list3 = new List<string>();
                        if (currentysRowValue == null)
                        {
                            return;
                        }
                        if (array3.Length == 1)
                        {
                            this.SetBillOpenParam(ysbillNo, text4, ref billShowParameter);
                            //this.SetBillOpenParam(text2, text4, ref billShowParameter);
                        }
                        else
                        {
                            for (int j = 0; j < array3.Length; j++)
                            {
                                list3.Add(string.Format(" '{0}'", array3[j]));
                            }
                            text3 = string.Join(",", this.GetPKValue(string.Join(",", list3.ToArray()), text4).ToArray());
                            if (text3.Trim() != "" && text3.Trim() != "0")
                            {
                                this.ViewShowList(text4, text3);
                            }
                        }
                        break;
                    }
                   case "暂估应收单号":
                    {
                        String text4 = "AR_receivable";
                        var currentysRowValue = view.GetCurrentRowValue("暂估应收单号").ToString().Trim().ToString();
                        var ysbillNo = currentysRowValue.ToString();
                        string[] array3 = currentysRowValue.Split(new char[] { ',' });
                        List<string> list3 = new List<string>();
                        if (currentysRowValue == null)
                        {
                            return;
                        }
                        if (array3.Length == 1)
                        {
                            this.SetBillOpenParam(currentysRowValue, text4, ref billShowParameter);
                            //this.SetBillOpenParam(text2, text4, ref billShowParameter);
                        }
                        else
                        {
                            for (int j = 0; j < array3.Length; j++)
                            {
                                list3.Add(string.Format(" '{0}'", array3[j]));
                            }
                            text3 = string.Join(",", this.GetPKValue(string.Join(",", list3.ToArray()), text4).ToArray());
                            if (text3.Trim() != "" && text3.Trim() != "0")
                            {
                                this.ViewShowList(text4, text3);
                            }
                        }
                        // 使用单据视图打开
                        //ShowBillForm(billNo);
                        // 使用列表视图打开
                        //ShowbillListForm("AR_RECEIVEBILL", ysbillNo);
                        break;
                    }
                case "财务应收单号":
                    {
                        String text4 = "AR_receivable";
                        var currentysRowValue = view.GetCurrentRowValue("财务应收单号").ToString().Trim().ToString();
                        var ysbillNo = currentysRowValue.ToString();
                        string[] array3 = currentysRowValue.Split(new char[] { ',' });
                        List<string> list3 = new List<string>();
                        if (currentysRowValue == null)
                        {
                            return;
                        }
                        if (array3.Length == 1)
                        {
                            this.SetBillOpenParam(ysbillNo, text4, ref billShowParameter);
                            //this.SetBillOpenParam(text2, text4, ref billShowParameter);
                        }
                        else
                        {
                            for (int j = 0; j < array3.Length; j++)
                            {
                                list3.Add(string.Format(" '{0}'", array3[j]));
                            }
                            text3 = string.Join(",", this.GetPKValue(string.Join(",", list3.ToArray()), text4).ToArray());
                            if (text3.Trim() != "" && text3.Trim() != "0")
                            {
                                this.ViewShowList(text4, text3);
                            }
                        }
                        // 使用单据视图打开
                        //ShowBillForm(billNo);
                        // 使用列表视图打开
                        //ShowbillListForm("AR_RECEIVEBILL", ysbillNo);
                        break;
                    }
                case "预开单号":
                    {
                        String text4 = "AR_receivable";
                        var currentysRowValue = view.GetCurrentRowValue("预开单号").ToString().Trim().ToString();
                        var ysbillNo = currentysRowValue.ToString();
                        string[] array3 = currentysRowValue.Split(new char[] { ',' });
                        List<string> list3 = new List<string>();
                        if (currentysRowValue == null)
                        {
                            return;
                        }
                        if (array3.Length == 1)
                        {
                            this.SetBillOpenParam(ysbillNo, text4, ref billShowParameter);
                            //this.SetBillOpenParam(text2, text4, ref billShowParameter);
                        }
                        else
                        {
                            for (int j = 0; j < array3.Length; j++)
                            {
                                list3.Add(string.Format(" '{0}'", array3[j]));
                            }
                            text3 = string.Join(",", this.GetPKValue(string.Join(",", list3.ToArray()), text4).ToArray());
                            if (text3.Trim() != "" && text3.Trim() != "0")
                            {
                                this.ViewShowList(text4, text3);
                            }
                        }
                        // 使用单据视图打开
                        //ShowBillForm(billNo);
                        // 使用列表视图打开
                        //ShowbillListForm("AR_RECEIVEBILL", ysbillNo);
                        break;
                    }
            }
           if (billShowParameter.PKey != null && billShowParameter.PKey.Trim() != "" &&    billShowParameter.PKey.Trim() != "0")
			{
				this.View.ShowForm(billShowParameter);
				text2 = string.Empty;
				billShowParameter = new BillShowParameter();
			}
           base.EntityRowClick(e);
		
        }
                      // 只有在双击单据编号列时，才打开该编号对应的单据
                    // 版本PT-146850  [7.5.1.202008]前需自定义，用此方法
        private object GetCurrentRowValue(IDynamicFormView view, string columnName)
        {
            var currentPageData = GetValue(view, "currentPageData") as DataTable;
            if (currentPageData == null)
            {
                return null;
            }
            if (!currentPageData.Columns.Contains(columnName))
            {
                return null;
            }
            if (view.OpenParameter.GetCustomParameter("FLIST_selectedRows") == null)
            {
                return null;
            }
            var curRow = view.OpenParameter.GetCustomParameter("FLIST_selectedRows").ToString();
            // 反射获取当前账表数据包
            var rows = currentPageData.Select(string.Format("{0}={1}", "FIDENTITYID", curRow));
            if (rows.Length > 0)
            {
                return rows[0][columnName];
            }
            return null;
        }
        /// 反射获取当前视图的某个值
        private object GetValue(object obj, string propertyName)
        {
            var field = typeof(SQLReportView).GetField(propertyName, BindingFlags.NonPublic | BindingFlags.Instance);
            if (field != null)
            {
                return field.GetValue(obj);
            }
            return null;

        }
        
		private void ViewShowList(string formId, string PkValues)
		{
			ListShowParameter listShowParameter = new ListShowParameter();
			listShowParameter.ListFilterParameter = new ListRegularFilterParameter
			{
				Filter = string.Format(" FID IN({0}) ", PkValues)
			};
			listShowParameter.FormId = formId;
			listShowParameter.IsLookUp = false;
			listShowParameter.IsIsolationOrg = false;
			this.View.ShowForm(listShowParameter);
		}
        private List<string> GetPKValue(string billNo, string FormID)
		{
			List<string> list = new List<string>();
			if (billNo.Trim().Length > 0)
			{
				IQueryService service = ServiceFactory.GetService<IQueryService>(base.Context);
				QueryBuilderParemeter para = new QueryBuilderParemeter
				{
					FormId = FormID,
					SelectItems = SelectorItemInfo.CreateItems("FBILLNO,FID"),
					FilterClauseWihtKey = string.Format(" FBILLNO IN ({0}) ", billNo)
				};
				DynamicObjectCollection dynamicObjectCollection =            service.GetDynamicObjectCollection(base.Context, para, null);
				foreach (DynamicObject current in dynamicObjectCollection)
				{
					if (current != null)
					{
						list.Add(Convert.ToString(current["FID"]));
					}
				}
			}
			return list;
		}

		private List<string> GetInvPKValue(string billNos)
		{
			List<string> list = new List<string>();
			if (billNos.Trim().Length > 0)
			{
				DynamicObjectCollection invoiceFIDByNo = SaleServiceHelper.GetInvoiceFIDByNo(base.Context, billNos);
				foreach (DynamicObject current in invoiceFIDByNo)
				{
					if (current != null)
					{
						list.Add(Convert.ToString(current["FID"]));
					}
				}
			}
			return list;
		}

		private void SetBillOpenParam(string billNo, string formId, ref BillShowParameter billShowPara)
		{
			PermissionAuthResult permissionAuthResult = PermissionServiceHelper.FuncPermissionAuth(base.Context, new BusinessObject
			{
				Id = formId
			}, "6e44119a58cb4a8e86f6c385e14a17ad");
			if (!permissionAuthResult.Passed)
			{
				this.View.ShowMessage(ResManager.LoadKDString("你没有当前单据查看权限，请设置！", "004104000013505", SubSystemType.SCM, new object[0]), MessageBoxType.Notice);
				return;
			}
			List<string> list = new List<string>();
			List<string> list2 = new List<string>();
			string pKey = "";
			string defaultBillTypeId = "";
			if (billNo.Trim().Length > 0)
			{
				billNo = string.Format(" '{0}'", billNo);
				IQueryService service = ServiceFactory.GetService<IQueryService>(base.Context);
				QueryBuilderParemeter para = new QueryBuilderParemeter
				{
					FormId = formId,
					SelectItems = SelectorItemInfo.CreateItems("FBILLNO,FID,FBILLTYPEID"),
					FilterClauseWihtKey = string.Format(" FBILLNO IN ({0}) ", billNo)
				};
				DynamicObjectCollection dynamicObjectCollection = service.GetDynamicObjectCollection(base.Context, para, null);
				foreach (DynamicObject current in dynamicObjectCollection)
				{
					if (current != null)
					{
						list.Add(Convert.ToString(current["FID"]));
						list2.Add(Convert.ToString(current["FBILLTYPEID"]));
					}
				}
			}
			if (list.Count > 0)
			{
				pKey = string.Join(",", list.ToArray());
				defaultBillTypeId = list2[0];
			}
			billShowPara.FormId = formId;
			billShowPara.PKey = pKey;
			billShowPara.DefaultBillTypeId = defaultBillTypeId;
		}
	}
    }


图标赞 54
54人点赞
还没有人点赞，快来当第一个点赞的人吧！
图标打赏
0人打赏
还没有人打赏，快来当第一个打赏的人吧！

推荐阅读

直接SQL账表 单据联查

问一下直接SQL账表 是否可以实现单据联查，如果不可以请问一下 有没有其它方式最好不要用插件，方便教客户使用 直接sql账表联查功能，需使用插件做特殊处理。 , …

    文章

    1,826

=============有SQL账表联查多 …

请教 如题，首先SQL账表双击可以联查单据吗？是否有示例代码？另外，开发的SQL账表穿透了4、5张单据，是否可以根据不同的编号联查不同的单据？ 楼主说的是万能报 …

    文章

    1,037

直接SQL账表启用过滤行

直接SQL账表启用过滤行可以在报表表体属性里面勾选“显示过滤行”属性，如下图所示： [图片] 启用过滤行效果 [图片]

    知识

    2,688