BOS批量插入数据然后关联表实战

栏目:云星空知识作者:金蝶来源:金蝶云社区发布:2024-09-16浏览:1

BOS批量插入数据然后关联表实战

引用的dll:
Kingdee.K3.FIN.App.Core.dll
Kingdee.BOS.App.dll
Kingdee.BOS.ServiceHelper.dll

方法:SaveFPJSZZGLRW

    [Description("发票反写应收"), HotUpdate]
    public class INVOPRT : AbstractOperationServicePlugIn
    {
        public override void OnPreparePropertys(PreparePropertysEventArgs e)
        {
            //e.FieldKeys.Add("");将需要应用的字段Key加入
            e.FieldKeys.Add("FBillNo");
            //结算组织
            e.FieldKeys.Add("FSETTLEORGID");
        }
  public override void AfterExecuteOperationTransaction(AfterExecuteOperationTransaction e)
        {
 if (strOP.EqualsIgnoreCase("Save"))
            {
                //销售普通发票,销售增值税专票 等 要更新(开票人、收款人、复核人)字段;
                List<Dto> dtos = e.DataEntitys.Select(P => new Dto()
                {
                    FId = Convert.ToInt64(P["Id"])
                   ,FSettle_ParentOrg_Id = Convert.ToInt64((P["SETTLEORGID"] as DynamicObject)["ParentOrg_Id"])
                }).Distinct().ToList();
                this.SaveFPJSZZGLRW(this.Context, dtos);
            }
        }

#region 销售普通发票,销售增值税专票 等 要更新(开票人、收款人、复核人)字段

        private class Dto
        {
            public Int64 FId { get; set; }
            public Int64 FSettle_ParentOrg_Id { get; set; }
        }

        private void SaveFPJSZZGLRW(Context context, List<Dto> list)
        {
            if (context == null || list.IsEmpty()) return;

            //创建表结构
            string tmpTableName = CommonFunction.GetTempTableName(context);
            StringBuilder sbSQL = new StringBuilder();
            sbSQL.AppendFormat(" Create table {0}  ( ", tmpTableName);
            sbSQL.Append("FId int not null ");
            sbSQL.Append(",FSettle_ParentOrg_Id int not null");
            sbSQL.Append(" ) ");
            DBUtils.Execute(context, sbSQL.ToString());

            //插入数据
            Dictionary<string, DbType> columnInfos = new Dictionary<string, DbType>();
            columnInfos.Add("FId", DbType.Int64);
            columnInfos.Add("FSettle_ParentOrg_Id", DbType.Int64);
            BulkInsertAdapter bulkInsertAdapter = new BulkInsertAdapter(context, columnInfos, tmpTableName, list.Count);
            foreach (Dto dto in list)
            {
                DataRow newRow = bulkInsertAdapter.NewRow;
                newRow["FId"] = dto.FId;
                newRow["FSettle_ParentOrg_Id"] = dto.FSettle_ParentOrg_Id;
                bulkInsertAdapter.Insert(newRow);
            }
            bulkInsertAdapter.Finish();

            //创建索引
            string indexPrefix12 = tmpTableName.Substring(6, 12);
            List<SqlObject> listSqlObject = new List<SqlObject>();
            string text = string.Format(" IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_INDNAME = 'idx_{0}_1') CREATE INDEX idx_{0}_1 ON {1} (FId)", indexPrefix12, tmpTableName);
            listSqlObject.Add(new SqlObject(text, new List<SqlParam>()));
            text = string.Format(" IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_INDNAME = 'idx_{0}_2') CREATE INDEX idx_{0}_2 ON {1} (FSettle_ParentOrg_Id)", indexPrefix12, tmpTableName);
            listSqlObject.Add(new SqlObject(text, new List<SqlParam>()));
            DBUtils.ExecuteBatchWithTime(context, listSqlObject, 300);
            listSqlObject.Clear();

            //更新(开票人、收款人、复核人)字段
            string updateSql = string.Format(@"/*dialect*/Merge into T_IV_SALESIC_O a Using 
                                            (
                                                select 
                                                x.FID 
                                                ,z.F_HANS_DRAWER as FDRAWER
                                                ,z.F_HANS_PAYEE as FPAYEE
                                                ,z.F_HANS_REVIEWER as FREVIEWER
                                                from T_IV_SALESIC x 
                                                inner join {0} y on x.FID=y.FID
                                                inner join HANS_t_FPJSZZGLRW z on z.FSETTLEORGID=y.FSettle_ParentOrg_Id AND z.FDocumentStatus='C'
                                             ) b ON (a.FID = b.FID)    
                                             When Matched Then Update Set a.FDRAWER = b.FDRAWER,a.FPAYEE=b.FPAYEE,a.FREVIEWER=b.FREVIEWER
                                            ", tmpTableName);
            DBServiceHelper.Execute(context, updateSql);

            //删除临时表
            if (DBServiceHelper.IsExistTable(context, tmpTableName))
                DBServiceHelper.Execute(context, string.Format(@"/*dialect*/DROP TABLE {0}", tmpTableName));
        }
        #endregion
    }
}


INVOPRT.rar

BOS批量插入数据然后关联表实战

引用的dll:Kingdee.K3.FIN.App.Core.dllKingdee.BOS.App.dllKingdee.BOS.ServiceHelper.dll方法:SaveFPJSZZGLRW [Description("发票反写...
点击下载文档
确认删除?
回到顶部
客服QQ
  • 客服QQ点击这里给我发消息