K3Cloud 结果临时表 新写法 及插入数据,删除临时表

/*
                      结果临时表
                        string inpurTableName = AppServiceContext.DBService.CreateTemporaryTableName(this.Context);
                        string strsql = "/*dialect*/create table " + inpurTableName + " as select FENTRYID from T_CN_PAYAPPLYENTRY where 1<>1";
                        DBServiceHelper.Execute(this.Context, strsql);
 if (DBServiceHelper.IsExistTable(this.Context, inpurTableName)) DBServiceHelper.Execute(this.Context, "/*dialect*/drop table " + inpurTableName + "");
*/

using Kingdee.BOS.App;
using Kingdee.BOS.Contracts;
using Kingdee.BOS.Core.DynamicForm.PlugIn;
using Kingdee.BOS.Core.DynamicForm.PlugIn.Args;
using Kingdee.BOS.Core.Metadata.FieldElement;
using Kingdee.BOS.Orm.DataEntity;
using Kingdee.BOS.ServiceHelper;
using Kingdee.BOS.ServiceHelper.ManagementCenter;
using Kingdee.BOS.Util;
using Kingdee.K3.FIN.App.Core;
using Kingdee.K3.FIN.App.Core.FINServiceForCN;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Text;

namespace HANS.AllOperations 
{
    [Description("付款申请单保存获取审批人和审批金额")]
    [HotUpdate]
    public class MPayApplySaveOprate : AbstractOperationServicePlugIn
    {
        IEnumerable<DynamicObject> selectedRows = null;
        public override void OnPreparePropertys(PreparePropertysEventArgs e)
        {
            //源单类型
            e.FieldKeys.Add("FSOURCETYPE");
            //源单行内码
            e.FieldKeys.Add("FSRCROWID");
            //审核人
            e.FieldKeys.Add("FAPPROVERID");
            //审批人
            e.FieldKeys.Add("F_HANS_AUDITID");
            //审批金额
            e.FieldKeys.Add("F_HANS_AUDITAMOUNT");

        }
        public override void BeforeExecuteOperationTransaction(BeforeExecuteOperationTransaction e)
        {
            base.BeforeExecuteOperationTransaction(e);
            selectedRows = e.SelectedRows.Select(s => s.DataEntity);
        }
        public override void BeginOperationTransaction(BeginOperationTransactionArgs e)
        {
            base.BeginOperationTransaction(e);
            if (selectedRows != null && selectedRows.Count() != 0)
            {
                foreach (DynamicObject bill in selectedRows)
                {
                    DynamicObjectCollection entryCollect = bill["FPAYAPPLYENTRY"] as DynamicObjectCollection;
                    Dictionary<string, decimal> dic = new Dictionary<string, decimal>();
                    //源单是采购订单
                    List<string> srcpurId=entryCollect.Where(p => Convert.ToString(p["FSOURCETYPE"]).EqualsIgnoreCase("PUR_PurchaseOrder")).Where(a => Convert.ToInt64(a["FSRCROWID"]) > 0).Select(o => Convert.ToString(o["FSRCROWID"])).Distinct().ToList();
                    if(srcpurId!=null && srcpurId.Count>0)
                    {
                        //结果临时表
                        string inpurTableName = AppServiceContext.DBService.CreateTemporaryTableName(this.Context);
                        string strsql = "/*dialect*/create table " + inpurTableName + " as select FENTRYID from T_CN_PAYAPPLYENTRY where 1<>1";
                        DBServiceHelper.Execute(this.Context, strsql);
                        Dictionary<string, DbType> columnInfos = new Dictionary<string, DbType>();
                        columnInfos.Add("FENTRYID", DbType.Int64);

                        BulkInsertAdapter bulkInsertAdapter = new BulkInsertAdapter(base.Context, columnInfos, inpurTableName, srcpurId.Count);
                        foreach (string dc in srcpurId)
                        {
                            DataRow newRow = bulkInsertAdapter.NewRow;
                            newRow["FENTRYID"] = dc;
                            bulkInsertAdapter.Insert(newRow);
                        }
                        bulkInsertAdapter.Finish();
                        strsql = "/*dialect*/select FYFAMOUNT,F_HANS_LASTAUDIT  from T_PUR_POORDERINSTALLMENT   WHERE F_HANS_WETHERPUSH>0 and FENTRYID in (select FENTRYID from "+ inpurTableName + ")";
                        DataSet da = DBServiceHelper.ExecuteDataSet(this.Context, strsql);
                        if(da!=null && da.Tables.Count>0 && da.Tables[0].Rows.Count>0)
                        {
                            foreach(DataRow row in da.Tables[0].Rows)
                            {
                                string auditId = Convert.ToString(row[1]);
                                decimal qty = Convert.ToDecimal(row[0]);
                                if(!auditId.IsNullOrEmptyOrWhiteSpace() && Convert.ToDecimal(auditId)>0)
                                {
                                    if (dic.Count > 0)
                                    {
                                        if (dic.ContainsKey(auditId)) dic[auditId] = Convert.ToDecimal(dic[auditId]) + qty;
                                        else dic.Add(auditId, qty);
                                    }
                                    else
                                    {
                                        dic.Add(auditId, qty);
                                    }
                                }                               
                            }                           
                        }
                        if (DBServiceHelper.IsExistTable(this.Context, inpurTableName)) DBServiceHelper.Execute(this.Context, "/*dialect*/drop table "+ inpurTableName + "");
                    }
                    //源单是应付单
                    List<string> srcappId = entryCollect.Where(p => Convert.ToString(p["FSOURCETYPE"]).EqualsIgnoreCase("AP_Payable")).Where(a => Convert.ToInt64(a["FSRCROWID"]) > 0).Select(o => Convert.ToString(o["FSRCROWID"])).Distinct().ToList();
                    if (srcappId != null && srcappId.Count > 0)
                    {
                        //结果临时表
                        string inpurTableName = AppServiceContext.DBService.CreateTemporaryTableName(this.Context);
                        string strsql = "/*dialect*/create table " + inpurTableName + " as select FENTRYID from T_CN_PAYAPPLYENTRY where 1<>1";
                        DBServiceHelper.Execute(this.Context, strsql);
                        Dictionary<string, DbType> columnInfos = new Dictionary<string, DbType>();
                        columnInfos.Add("FENTRYID", DbType.Int64);

                        BulkInsertAdapter bulkInsertAdapter = new BulkInsertAdapter(base.Context, columnInfos, inpurTableName, srcappId.Count);
                        foreach (string dc in srcappId)
                        {
                            DataRow newRow = bulkInsertAdapter.NewRow;
                            newRow["FENTRYID"] = dc;
                            bulkInsertAdapter.Insert(newRow);
                        }
                        bulkInsertAdapter.Finish();
                        strsql = "/*dialect*/select FPAYAMOUNTFOR,F_HANS_LASTAUDIT  from T_AP_PAYABLEPLAN   WHERE F_HANS_WETHERPUSH>0 and FENTRYID in (select FENTRYID from " + inpurTableName + ")";
                        DataSet da = DBServiceHelper.ExecuteDataSet(this.Context, strsql);
                        if (da != null && da.Tables.Count > 0 && da.Tables[0].Rows.Count > 0)
                        {
                            foreach (DataRow row in da.Tables[0].Rows)
                            {
                                string auditId = Convert.ToString(row[1]);
                                decimal qty = Convert.ToDecimal(row[0]);
                                if (!auditId.IsNullOrEmptyOrWhiteSpace() && Convert.ToDecimal(auditId) > 0)
                                {
                                    if (dic.Count > 0)
                                    {
                                        if (dic.ContainsKey(auditId)) dic[auditId] = Convert.ToDecimal(dic[auditId]) + qty;
                                        else dic.Add(auditId, qty);
                                    }
                                    else
                                    {
                                        dic.Add(auditId, qty);
                                    }
                                }
                            }
                        }
                        if (DBServiceHelper.IsExistTable(this.Context, inpurTableName)) DBServiceHelper.Execute(this.Context, "/*dialect*/drop table " + inpurTableName + "");
                    }
                    DynamicObjectCollection auditCollect = bill["F_HANS_AuditAmountEntity"] as DynamicObjectCollection;
                    if (dic.Count()>0)
                    {
                        auditCollect.Clear();
                        foreach(var dc in dic)
                        {
                            DynamicObject newRow = new DynamicObject(auditCollect.DynamicCollectionItemPropertyType);
                            DynamicObject Materials = null;
                            IViewService Service = ServiceHelper.GetService<IViewService>();
                            BaseDataField auditField = this.BusinessInfo.GetField("F_HANS_AUDITID") as BaseDataField;//审批人
                            Utils.SetBaseDataValue(Service, newRow, auditField, Convert.ToInt64(dc.Key), ref Materials,this.Context);
                            Materials = null;
                            newRow["F_HANS_AUDITAMOUNT"] = dc.Value;//审批金额
                            auditCollect.Add(newRow);
                        }
                    }
                    else
                    {
                        auditCollect.Clear();
                    }
                }
            }
        }
    }
}