直接SQL账表联查单据原创
weiweibrother
53人赞赏了该文章
434次浏览
编辑于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;
}
}
}推荐阅读