需求：多组织（一个数据库实例里面，存在一个总公司数据库，多个分公司数据库）环境中，以总公司的会计科目为主（分公司可能不存在总公司存在的科目），以树形结构的方式展示各个科目下每个公司的该科目对应的余额数据。每类科目有一个余额汇总小计，最后再做一个全部余额合计，本例中使用一个总公司，一个分公司做的演示
效果如下所示：
准备工作：
1、在数据库中创建两个数据库，一个 MyWorkTest （表示总公司） 一个 MyWorkTestBackUp（表示分公司）
2、分别在两个数据库中执行表结构sql语句（没用注释的部分两个表都需要执行，注释的部分，只需要执行对应数据库的sql部分）
3、准备treetable插件
4、本例用的是MVC 需要将MVC的模板 _ViewStart 中的Layout设置为null
代码实现：
1、表结构SQL
--公司信息 Create Table MyWorkTest_CompanyInfo( id int IDENTITY(1,1) NOT NULL, fullname nvarchar(10) NOT NULL, isHeader bit not null, dbname nvarchar(100) not null ) GO delete from MyWorkTest_CompanyInfo insert into MyWorkTest_CompanyInfo(fullname,isHeader,dbname) select '总公司',1,'MyWorkTest' UNION ALL select '成都分公司',0,'MyWorkTestBackUp' GO --科目信息 Create Table MyWorkTest_Atype( typeid nvarchar(50) not null, parid nvarchar(50) not null, leveal smallint not null, sonnum int not null, fullname nvarchar(66) not null, usercode nvarchar(50) not null, balance numeric(18,2) default(0) ) GO delete from MyWorkTest_Atype /* --MyWorkTest 数据库数据 --资产类 insert into MyWorkTest_Atype(typeid,parid,leveal,sonnum,fullname,usercode,balance) select '00000','root','0','5','全部科目','00',0 UNION ALL select '00001','00000','1','3','资产类','10',0 UNION ALL --17640 select '0000100001','00001','2','0','库存现金','1001',10000 UNION ALL select '0000100002','00001','2','2','银行存款','1002',0 UNION ALL --7520 select '000010000200001','0000100002','3','0','建设银行','100201',1020 UNION ALL select '000010000200002','0000100002','3','2','工商银行','100202',0 UNION ALL --6500 select '00001000020000200001','000010000200002','4','0','成都工商银行','10020201',4000 UNION ALL select '00001000020000200002','000010000200002','4','0','绵阳工商银行','10020202',2500 UNION ALL select '0000100003','00001','2','2','其他货币资金','1003',0 UNION ALL --120 select '000010000300001','0000100003','3','0','外埠存款','100301',20 UNION ALL select '000010000300002','0000100003','3','0','信用卡','100302',100 --负债类 insert into MyWorkTest_Atype(typeid,parid,leveal,sonnum,fullname,usercode,balance) select '00002','00000','1','4','负债类','20',0 UNION ALL --1073 select '0000200001','00002','2','0','短期借款','2001',10 UNION ALL select '0000200002','00002','2','2','应付账款','2002',0 UNION ALL --33 select '000020000200001','0000200002','3','0','应付货款','200201',11 UNION ALL select '000020000200002','0000200002','3','0','应付加工费','200202',22 UNION ALL select '0000200003','00002','2','1','应交税费','2003',0 UNION ALL --30 select '000020000300001','0000200003','3','2','应交增值税','200301',0 UNION ALL --30 select '00002000030000100001','000020000300001','4','0','进项税额','20030101',10 UNION ALL select '00002000030000100002','000020000300001','4','0','己交税金','20030102',20 UNION ALL select '0000200004','00002','2','','应付职工薪酬','2004',1000 --共同类 insert into MyWorkTest_Atype(typeid,parid,leveal,sonnum,fullname,usercode,balance) select '00003','00000','1','1','共同类','30',0 UNION ALL --30 select '0000300001','00003','2','0','货币兑换','3001',10 UNION ALL select '0000300002','00003','2','0','总公司有子公司没有的项目','3002',20 --所有者权益 insert into MyWorkTest_Atype(typeid,parid,leveal,sonnum,fullname,usercode,balance) select '00004','00000','1','2','所有者权益','40',0 UNION ALL --350 select '0000400001','00004','2','0','实收资本','4001',150 UNION ALL select '0000400002','00004','2','0','盈余公积','4002',200 --成本类 insert into MyWorkTest_Atype(typeid,parid,leveal,sonnum,fullname,usercode,balance) select '00005','00000','1','1','成本类','50',0 UNION ALL --30 select '0000500001','00005','2','2','生产成本','5001',0 UNION ALL --30 select '000050000100001','0000500001','3','0','基本生产成本','500101',10 UNION ALL select '000050000100002','0000500001','3','0','辅助生产成本','500102',20 */ /* --MyWorkTestBackUp 数据库数据 insert into MyWorkTest_Atype(typeid,parid,leveal,sonnum,fullname,usercode,balance) select '00000','root','0','5','全部科目','00',0 UNION ALL select '00001','00000','1','3','资产类','10',176.2 UNION ALL select '0000100001','00001','2','0','库存现金','1001',100 UNION ALL select '0000100002','00001','2','2','银行存款','1002',75 UNION ALL select '000010000200001','0000100002','3','0','建设银行','100201',10 UNION ALL select '000010000200002','0000100002','3','2','工商银行','100202',65 UNION ALL select '00001000020000200001','000010000200002','4','0','成都工商银行','10020201',40 UNION ALL select '00001000020000200002','000010000200002','4','0','绵阳工商银行','10020202',25 UNION ALL select '0000100003','00001','2','2','其他货币资金','1003',1.20 UNION ALL select '000010000300001','0000100003','3','0','外埠存款','100301',0.20 UNION ALL select '000010000300002','0000100003','3','0','信用卡','100302',1 --负债类 insert into MyWorkTest_Atype(typeid,parid,leveal,sonnum,fullname,usercode,balance) select '00002','00000','1','4','负债类','20',1.73 UNION ALL select '0000200001','00002','2','0','短期借款','2001',0.1 UNION ALL select '0000200002','00002','2','2','应付账款','2002',0.33 UNION ALL select '000020000200001','0000200002','3','0','应付货款','200201',0.11 UNION ALL select '000020000200002','0000200002','3','0','应付加工费','200202',0.22 UNION ALL select '0000200003','00002','2','1','应交税费','2003',0.3 UNION ALL select '000020000300001','0000200003','3','2','应交增值税','200301',0.3 UNION ALL select '00002000030000100001','000020000300001','4','0','进项税额','20030101',0.1 UNION ALL select '00002000030000100002','000020000300001','4','0','己交税金','20030102',0.2 UNION ALL select '0000200004','00002','2','','应付职工薪酬','2004',1 --共同类 insert into MyWorkTest_Atype(typeid,parid,leveal,sonnum,fullname,usercode,balance) select '00003','00000','1','1','共同类','30',0.1 UNION ALL select '0000300001','00003','2','0','货币兑换','3001',0.1 --所有者权益 insert into MyWorkTest_Atype(typeid,parid,leveal,sonnum,fullname,usercode,balance) select '00004','00000','1','2','所有者权益','40',0.35 UNION ALL select '0000400001','00004','2','0','实收资本','4001',0.15 UNION ALL select '0000400002','00004','2','0','盈余公积','4002',0.2 --成本类 insert into MyWorkTest_Atype(typeid,parid,leveal,sonnum,fullname,usercode,balance) select '00005','00000','1','1','成本类','50',0.3 UNION ALL select '0000500001','00005','2','2','生产成本','5001',0.3 UNION ALL select '000050000100001','0000500001','3','0','基本生产成本','500101',0.1 UNION ALL select '000050000100002','0000500001','3','0','辅助生产成本','500102',0.2 */
2、存储过程
IF EXISTS ( SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[P_Inf_GetAtypeAssistantBalance]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1 ) DROP PROCEDURE [dbo].[P_Inf_GetAtypeAssistantBalance] GO /* 需求：多组织环境，以总公司的科目为参考（存在总公司有的科目在子公司没有的情况），统计每个公司的每个科目的余额情况 以树形的方式显示科目，在末级显示各个公司该科目的余额情况， 每个末级科目显示各个公司该科目余额的汇总数据， 不同科目类一个科目余额小计，最后汇总一个全部余额合计 */ CREATE PROCEDURE P_Inf_GetAtypeAssistantBalance AS BEGIN SET NOCOUNT ON --变量申明 DECLARE @ComName nvarchar(200),@DbName nvarchar(200), @IsHead bit, @Sql nvarchar(max) DECLARE @MaxLevel INT,@CIndex int DECLARE @CIndexStr nvarchar(200) -- 获取末级科目 SELECT typeid,leveal INTO #AtypeCw FROM MyWorkTest_Atype WHERE sonnum = 0 --末级数据表 CREATE TABLE #FinalData( CompanyID nvarchar(50), TypeID nvarchar(200), ParId nvarchar(200), BalanceTotal numeric(18,4), leveal int DEFAULT 0, CIndex nvarchar(200) ) -- 获取末级数据封装 set @CIndex =1 Declare my_cursor Cursor For Select dbname, fullname,isHeader From MyWorkTest_CompanyInfo Where dbname <> '' Order By isHeader Desc Open my_cursor Fetch Next From my_cursor Into @DbName, @ComName,@IsHead While @@Fetch_Status = 0 BEGIN SET @CIndexStr = right(cast(right('0000'+rtrim(@CIndex),4) as varchar(4)),4) SET @SQL ='select '''+@ComName+''', a.typeid,a.parid,a.balance,a.leveal,'''+@CIndexStr+''' AS cindex from ['+@DbName+']..MyWorkTest_Atype a INNER join #AtypeCw b on a.typeid = b.typeid' INSERT INTO #FinalData(CompanyID,TypeID,ParId,BalanceTotal,leveal,CIndex) exec (@Sql) INSERT INTO #FinalData(CompanyID,TypeID,ParId,BalanceTotal,leveal,CIndex) SELECT @ComName AS CompanyID,A.typeid,left(A.typeid,LEN(A.typeid)- 5) AS ParId,0 as BalanceTotal,0 AS leveal,@CIndexStr AS CIndex FROM #AtypeCw A LEFT JOIN #FinalData B ON A.typeid = B.TypeID AND b.CompanyID = @ComName WHERE B.TypeID IS NULL GROUP BY A.typeid SET @CIndex = @CIndex+1 Fetch Next From my_cursor Into @DbName, @ComName,@IsHead End Close my_cursor Deallocate my_cursor --在末级增加公司数据 select LEFT(typeid,5) as ATypeIndex, tag, usercode, fullname,typeid,parId,balancetotal ,leveal, ROW_NUMBER() OVER(PARTITION By LEFT(typeid,5) Order By typeid,parId,tag ) as AtypeInnerIndex into #LastLevealData from ( select 10 tag,a.usercode, a.fullname, f.TypeID,f.ParId, sum(BalanceTotal)AS BalanceTotal,a.leveal from #FinalData f left join MyWorkTest_Atype a on f.TypeID = a.typeid GROUP BY f.TypeID,a.fullname,f.ParId,a.leveal,a.usercode UNION ALL select 20 tag,'' AS usercode,CompanyID,TypeID+CIndex,TypeID, SUM(BalanceTotal) AS BalanceTotal,fc.leveal from #FinalData fc group by CompanyID,TypeID,ParId,fc.leveal,fc.CIndex ) t order by TypeID,tag --增加父级数据 SELECT @MaxLevel = max(leveal) FROM #LastLevealData WHILE @MaxLevel > 1 BEGIN INSERT INTO #LastLevealData(ATypeIndex,tag, usercode,fullname,typeid,parId,balancetotal,leveal,AtypeInnerIndex) SELECT LEFT(b.typeId,5) AS ATypeIndex ,10,b.usercode,b.FullName,b.typeId,b.parid,BalanceTotal,b.leveal,'' AS AtypeInnerIndex FROM ( SELECT DISTINCT LEFT(typeid, LEN(typeid)-5) as typeid, SUM(BalanceTotal) AS BalanceTotal FROM #LastLevealData WHERE tag = 10 AND leveal = @MaxLevel GROUP BY LEFT(typeid, LEN(typeid)-5) ) a INNER JOIN MyWorkTest_Atype b ON a.typeid = b.typeid SET @MaxLevel = @MaxLevel - 1 END update #LastLevealData set parid = '00000' where leveal = 2 and tag = 10 update #LastLevealData set leveal = 9 where leveal = 1 and tag = 10 -- 获取数据源 select ATypeIndex,tag,usercode, fullname,typeid,parId,balancetotal,leveal, ROW_NUMBER() OVER(PARTITION By LEFT(typeid,5) Order By ATypeIndex ,typeid,tag) as AtypeInnerIndex into #DataSource from #LastLevealData --小类合计放在每一类的最后面处理开始 select ATypeIndex,tag,usercode, fullname,typeid,parId,balancetotal,leveal, ROW_NUMBER() OVER(Order By ATypeIndex,leveal,AtypeInnerIndex) as AtypeInnerIndex into #DataSourceRst from #DataSource delete from #DataSource insert into #DataSource select * from #DataSourceRst --小类合计放在每一类的最后面处理结束 --所有合计 insert into #DataSource(ATypeIndex,tag,usercode, fullname,typeid,parId,balancetotal,leveal,AtypeInnerIndex) select '99999' AS ATypeIndex,40 AS tag, '合计' AS usercode, '合计' AS fullname,'99999' AS typeid, '00000' AS parId,sum(balancetotal), 0 as leveal, 0 AS AtypeInnerIndex from #DataSource where leveal = 9 update #DataSource set usercode = fullname+'小计' where leveal = 9 select * from #DataSource order by ATypeIndex --删除临时表 DROP TABLE #AtypeCw DROP TABLE #FinalData DROP TABLE #LastLevealData DROP TABLE #DataSource DROP TABLE #DataSourceRst END GO
3、页面代码
<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>tree-table</title> <link rel="stylesheet" href="/Content/TreeTable/assets/layui/css/layui.css"> <link rel="stylesheet" href="/Content/TreeTable/assets/common.css" /> </head> <body> <link rel="stylesheet" href="/Content/TreeTable/assets/layui/css/layui.css"> <link rel="stylesheet" href="/Content/TreeTable/assets/common.css" /> <p></p> <div class="layui-container"> <div class="layui-btn-group"> <button class="layui-btn" id="btn-expand">全部展开</button> <button class="layui-btn" id="btn-fold">全部折叠</button> </div> <table id="auth-table" class="layui-table" lay-filter="auth-table"></table> </div> <script src="~/Scripts/jquery-1.10.2.min.js"></script> <script src="/Content/TreeTable/assets/layui/layui.js"></script> <script> $.ajax({ url: '/MyTest/GetDataSource', type: 'post', dataType: 'json', success: function (res) { layui.config({ base: '/Content/TreeTable/module/' }).extend({ treetable: 'treetable-lay/treetable' }).use(['table', 'treetable'], function () { var $ = layui.jquery; var table = layui.table; var treetable = layui.treetable; // 渲染表格 layer.load(2); treetable.render({ data: res.data, treeColIndex: 1, treeSpid: '00000', treeIdName: 'TypeId', treePidName: 'ParId', elem: '#auth-table', page: false, cols: [[ { type: 'numbers' }, { field: 'UserCode', minWidth: 200, title: '科目编码' }, { field: 'FullName', minWidth: 200, title: '科目名称' }, { field: 'BalanceTotal', title: '余额' }, { field: 'AtypeInnerIndex', 80, align: 'center', title: '排序号' }, { field: 'TypeId', 80, align: 'center', title: 'ID' }, { field: 'ParId', 80, align: 'center', title: '父级ID' }, ]], done: function () { layer.closeAll('loading'); } }); $('#btn-expand').click(function () { treetable.expandAll('#auth-table'); }); $('#btn-fold').click(function () { treetable.foldAll('#auth-table'); }); }); } }) </script> </body> </html>
4、控制器代码
using MyWebSiteDemo.Models; using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Web; using System.Web.Mvc; using System.Web.Script.Serialization; namespace MyWebSiteDemo.Controllers { public class MyTestController : Controller { public ActionResult Index() { return View(); } public string GetDataSource() { ItemDbContext itemContext = new ItemDbContext(); List<Item> list = itemContext.Database.SqlQuery<Item>("exec P_Inf_GetAtypeAssistantBalance").ToList(); JsonData jsonData = new JsonData(); jsonData.data = list; jsonData.code = "0"; jsonData.msg = "OK"; jsonData.count = "67"; StringBuilder sb = new StringBuilder(); JavaScriptSerializer json = new JavaScriptSerializer(); json.Serialize(jsonData, sb); return sb.ToString(); } } public class JsonData { public List<Item> data { get; set; } public string code { get; set; } public string msg { get; set; } public string count { get; set; } } }
5、实体代码
using System; using System.Collections.Generic; using System.Linq; using System.Web; namespace MyWebSiteDemo.Models { public class Item { /// <summary> /// 大类编号 /// </summary> public string AtypeIndex { get; set; } //标识 public int Tag { get; set; } /// <summary> /// 编码 /// </summary> public string UserCode { get; set; } /// <summary> /// 全名 /// </summary> public string FullName { get; set; } /// <summary> /// 编号 /// </summary> public string TypeId { get; set; } /// <summary> /// 父级编号 /// </summary> public string ParId { get; set; } /// <summary> /// 余额 /// </summary> public decimal BalanceTotal { get; set; } /// <summary> /// 级别 /// </summary> public int Leveal { get; set; } /// <summary> /// 排序 /// </summary> public long AtypeInnerIndex { get; set; } } }
6、数据上下文
using System; using System.Collections.Generic; using System.Data.Entity; using System.Linq; using System.Web; namespace MyWebSiteDemo.Models { public class ItemDbContext:DbContext { public ItemDbContext() : base("数据库Key") {} } }
7、资源下载
treetable插件 提取码: 6pjw
写写博客，方便自己也方便有需要的人~~