项目预算及经营报表(二维动态表)
项目预算及经营报表是一张二维的动态统计表，在水平方向是月份、季度、年度的动态加载；垂直方向是预算科目的动态加载并且还要合并一级预算科目单元格，是非常典型和复杂的统计报表。
所在项目名称：深度搜索项目管理系统
原始excel报表原型：下载
（一）、具体的界面效果如下：
完整的界面效果如下：
（二）、界面布局模板代码
<style type="text/css">
.panel .WorkFlowCSSTableHeader { font-weight: bold; text-align: center; background-color: #999 !important; color: #fff !important; background-image: none; }
.value { text-align: right; }
#统计结果层 { width: 1450px; font-family: 'PingFang SC'; }
.WorkFlowCSSTable { background-color: #333 !important; }
.YellowBg td, .YellowBg, .WorkFlowCSSTable td.YellowBg { background-color: yellow !important; }
.BlueBg td, .BlueBg, .WorkFlowCSSTable td.BlueBg { background-color: #BCD6EE !important; }
.LighterYellowBg td, .LighterYellowBg, .WorkFlowCSSTable td.LighterYellowBg { background-color: #FFFFCC !important; }
.DarkRedBg td, .DarkRedBg, .WorkFlowCSSTable td.DarkRedBg { background-color: #F8CBAC !important; }
</style>
<div class="panel panel-bordered CenterDIV Relative" style="margin-top: 10px; display: table; min-width: 100%;">
<div class="panel-heading Relative">
<h3 class="panel-title">年度预算报表</h3>
<div id="YearDIV" style="position: absolute; left: 200px; top: 15px;"></div>
<div style="position: absolute; left: 300px; top: 15px;">
<button type="button" class="btn btn-primary" onclick="统计()">统计</button>
</div>
</div>
<div class="panel-body" style="padding: 15px;">
<div id="统计结果层"></div>
</div>
</div>
<script>
DomReady(function ()
{
Element.SetHtml($("#YearDIV"), GetCOMMONYEARSelectObjectHTML("Year", "", true, ""));
统计();
});
function 统计()
{
var Year = Element.GetValue($("#Year"));
LoadServiceClass.LoadServiceInterfaceByDLLSetting("深度搜索插件.dll|标准项目插件.年度预算报表|统计", "年度预算统计", true, "统计结果层", GetLoadingMsg("正在加载..."), { Year: Year }, function (返回值)
{
//成功后的回调
}, null, { ActionName: "年度预算统计" });
}
</script>
（三）、插件源代码
//-----------------------------------------------------------------------
// <copyright file="年度预算报表.cs" company="企管宝">
// CustomizedWCFUI
// </copyright>
// <author></author>
// <createdate></createdate>
// <revisionhistory>
// </revisionhistory>
//-----------------------------------------------------------------------
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Text;
using System.Data;
using PublicFunctions;
using PublicFunctions.Service;
using PublicFunctions.Button;
using PublicFunctions.Form;
using PublicFunctions.Grid;
using PublicFunctions.PageTemplate;
namespace 标准项目插件
{
/// <summary>
/// 年度预算报表
/// </summary>
public class 年度预算报表 : ServiceFactoryBase
{
#region 函数说明：构造函数
/// <summary>
/// 构造函数，必须保留
/// </summary>
/// <param name="ServiceData"></param>
public 年度预算报表(ServiceDataModel ServiceData)
: base(ServiceData)
{ }
#endregion
#region 函数说明：统计
/// <summary>
/// 统计
/// </summary>
/// <returns></returns>
public AjaxResultModel 统计()
{
AjaxResultModel AjaxResultObject = new AjaxResultModel();
int Year = ConvertData.ConvertInt(PublicFunctions.PublicFunctions.GetDataFromDictionary("Year", ServiceData.RequestFormDictionary));
using (PublicFunctions.DBHelper.RawDBHelper.SqlDataFactory context = new PublicFunctions.DBHelper.RawDBHelper.SqlDataFactory(PublicSetting.RawDBHelper_CRM_ConnectionStringKey))
{
StringBuilder sb = new StringBuilder();
//定义月列表
List<int> MonthList = new List<int>() { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 };
//定义季度列表
List<int> QuarterList = new List<int>() { 1, 2, 3, 4 };
//定义季度与月份的字典
Dictionary<int, List<int>> QuarterMonthListDictionary = new Dictionary<int, List<int>>() { { 1, new List<int>() { 1, 2, 3 } }, { 2, new List<int>() { 4, 5, 6 } }, { 3, new List<int>() { 7, 8, 9 } }, { 4, new List<int>() { 10, 11, 12 } } };
//定义BaseSQL字典
Dictionary<string, string> BaseSQLDictionary = new Dictionary<string, string>();
//定义统计结果模型列表(用于存储中间的值，便于后面不用再次计算)
List<SummaryResultModel> SummaryResultList = new List<SummaryResultModel>();
#region 代码块说明：生成表头
sb.Append("<table class=\"WorkFlowCSSTable FullWidth\">");
sb.Append("<tbody>");
sb.Append("<tr>");
sb.Append("<td colspan=\"3\" class=\"WorkFlowCSSTableHeader\"> 类别 </td>");
foreach (int i in MonthList)
{
sb.Append("<td class=\"WorkFlowCSSTableHeader\">" + Year + "." + i + "</td>");
}
foreach (int i in QuarterList)
{
sb.Append("<td class=\"WorkFlowCSSTableHeader\">" + i + "季度</td>");
}
sb.Append("<td class=\"WorkFlowCSSTableHeader\">年度</td>");
sb.Append("</tr>");
#endregion
#region 代码块说明：一、签约统计（实际发生额）
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["签约统计"] = "select value=sum(ISNULL(ContractMoney,0)) from 合同会审主表 where IsFinished='true' and ContractCategory in('SaaS平台','数据标注') and ContractDate between '<$MinDate$>' and '<$MaxDate$>'";
sb.Append("<tr class=\"YellowBg\">");
sb.Append("<td colspan=\"3\"> 一、签约统计（实际发生额） </td>");
foreach (int i in MonthList)
{
string MinDate = Year + "-" + i + "-1";
string MaxDate = PublicFunctions.DateHelper.MonthHelper.GetLastDayOfMonth(Year, i).ToString("yyyy-MM-dd");
decimal value = Summary(context, BaseSQLDictionary["签约统计"], MinDate, MaxDate);
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "收入总计预算", SummaryType = "月", MonthORQuarter = i, Value = value });
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
string MinDate = Year + "-" + QuarterMonthListDictionary[i].First() + "-1";
string MaxDate = PublicFunctions.DateHelper.MonthHelper.GetLastDayOfMonth(Year, QuarterMonthListDictionary[i].Last()).ToString("yyyy-MM-dd");
decimal value = Summary(context, BaseSQLDictionary["签约统计"], MinDate, MaxDate);
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "收入总计预算", SummaryType = "季", MonthORQuarter = i, Value = value });
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
string MinDate = Year + "-1-1";
string MaxDate = PublicFunctions.DateHelper.MonthHelper.GetLastDayOfMonth(Year, 12).ToString("yyyy-MM-dd");
decimal value = Summary(context, BaseSQLDictionary["签约统计"], MinDate, MaxDate);
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "收入总计预算", SummaryType = "年", MonthORQuarter = 0, Value = value });
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：签约额
//销售合同金额
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["销售合同金额"] = "select value=sum(ISNULL(ContractMoney,0)) from 合同会审主表 where IsFinished='true' and ContractCategory in('SaaS平台') and ContractDate between '<$MinDate$>' and '<$MaxDate$>'";
sb.Append("<tr>");
sb.Append("<td rowspan=\"2\"> 签约额 </td>");
sb.Append("<td>S01</td>");
sb.Append("<td> 销售合同金额（SAAS平台） </td>");
foreach (int i in MonthList)
{
string MinDate = Year + "-" + i + "-1";
string MaxDate = PublicFunctions.DateHelper.MonthHelper.GetLastDayOfMonth(Year, i).ToString("yyyy-MM-dd");
decimal value = Summary(context, BaseSQLDictionary["销售合同金额"], MinDate, MaxDate);
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
string MinDate = Year + "-" + QuarterMonthListDictionary[i].First() + "-1";
string MaxDate = PublicFunctions.DateHelper.MonthHelper.GetLastDayOfMonth(Year, QuarterMonthListDictionary[i].Last()).ToString("yyyy-MM-dd");
decimal value = Summary(context, BaseSQLDictionary["销售合同金额"], MinDate, MaxDate);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
string MinDate = Year + "-1-1";
string MaxDate = PublicFunctions.DateHelper.MonthHelper.GetLastDayOfMonth(Year, 12).ToString("yyyy-MM-dd");
decimal value = Summary(context, BaseSQLDictionary["销售合同金额"], MinDate, MaxDate);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
//销售订单实际完工量
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["销售订单实际完工量"] = "select value=sum(ISNULL(ContractMoney,0)) from 合同会审主表 where IsFinished='true' and ContractCategory in('数据标注') and ContractDate between '<$MinDate$>' and '<$MaxDate$>'";
sb.Append("<tr>");
sb.Append("<td>S02</td>");
sb.Append("<td> 销售订单实际完工量（数据标注） </td>");
foreach (int i in MonthList)
{
string MinDate = Year + "-" + i + "-1";
string MaxDate = PublicFunctions.DateHelper.MonthHelper.GetLastDayOfMonth(Year, i).ToString("yyyy-MM-dd");
decimal value = Summary(context, BaseSQLDictionary["销售订单实际完工量"], MinDate, MaxDate);
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
string MinDate = Year + "-" + QuarterMonthListDictionary[i].First() + "-1";
string MaxDate = PublicFunctions.DateHelper.MonthHelper.GetLastDayOfMonth(Year, QuarterMonthListDictionary[i].Last()).ToString("yyyy-MM-dd");
decimal value = Summary(context, BaseSQLDictionary["销售订单实际完工量"], MinDate, MaxDate);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
string MinDate = Year + "-1-1";
string MaxDate = PublicFunctions.DateHelper.MonthHelper.GetLastDayOfMonth(Year, 12).ToString("yyyy-MM-dd");
decimal value = Summary(context, BaseSQLDictionary["销售订单实际完工量"], MinDate, MaxDate);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：二、收入总计（实际发生额）
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["收入总计"] = "select value=sum(ISNULL(Money,0)) from 客户回款记录,合同会审主表 where 合同会审主表.ContractNO=客户回款记录.ContractNO and 客户回款记录.[Date] between '<$MinDate$>' and '<$MaxDate$>' and 合同会审主表.ContractCategory in('SaaS平台','数据标注','其他')";
sb.Append("<tr class=\"YellowBg\">");
sb.Append("<td colspan=\"3\"> 二、收入总计（实际发生额） </td>");
foreach (int i in MonthList)
{
string MinDate = Year + "-" + i + "-1";
string MaxDate = PublicFunctions.DateHelper.MonthHelper.GetLastDayOfMonth(Year, i).ToString("yyyy-MM-dd");
decimal value = Summary(context, BaseSQLDictionary["收入总计"], MinDate, MaxDate);
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "收入总计实际", SummaryType = "月", MonthORQuarter = i, Value = value });
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
string MinDate = Year + "-" + QuarterMonthListDictionary[i].First() + "-1";
string MaxDate = PublicFunctions.DateHelper.MonthHelper.GetLastDayOfMonth(Year, QuarterMonthListDictionary[i].Last()).ToString("yyyy-MM-dd");
decimal value = Summary(context, BaseSQLDictionary["收入总计"], MinDate, MaxDate);
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "收入总计实际", SummaryType = "季", MonthORQuarter = i, Value = value });
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
string MinDate = Year + "-1-1";
string MaxDate = PublicFunctions.DateHelper.MonthHelper.GetLastDayOfMonth(Year, 12).ToString("yyyy-MM-dd");
decimal value = Summary(context, BaseSQLDictionary["收入总计"], MinDate, MaxDate);
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "收入总计实际", SummaryType = "年", MonthORQuarter = 0, Value = value });
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：外部实收
//SAAS平台收入
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["SAAS平台收入"] = "select value=sum(ISNULL(Money,0)) from 客户回款记录,合同会审主表 where 合同会审主表.ContractNO=客户回款记录.ContractNO and 客户回款记录.[Date] between '<$MinDate$>' and '<$MaxDate$>' and 合同会审主表.ContractCategory in('SaaS平台')";
sb.Append("<tr>");
sb.Append("<td rowspan=\"3\">外部实收</td>");
sb.Append("<td>R01</td>");
sb.Append("<td> SAAS平台收入 </td>");
foreach (int i in MonthList)
{
string MinDate = Year + "-" + i + "-1";
string MaxDate = PublicFunctions.DateHelper.MonthHelper.GetLastDayOfMonth(Year, i).ToString("yyyy-MM-dd");
decimal value = Summary(context, BaseSQLDictionary["SAAS平台收入"], MinDate, MaxDate);
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
string MinDate = Year + "-" + QuarterMonthListDictionary[i].First() + "-1";
string MaxDate = PublicFunctions.DateHelper.MonthHelper.GetLastDayOfMonth(Year, QuarterMonthListDictionary[i].Last()).ToString("yyyy-MM-dd");
decimal value = Summary(context, BaseSQLDictionary["SAAS平台收入"], MinDate, MaxDate);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
string MinDate = Year + "-1-1";
string MaxDate = PublicFunctions.DateHelper.MonthHelper.GetLastDayOfMonth(Year, 12).ToString("yyyy-MM-dd");
decimal value = Summary(context, BaseSQLDictionary["SAAS平台收入"], MinDate, MaxDate);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
//数据标注收入
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["数据标注收入"] = "select value=sum(ISNULL(Money,0)) from 客户回款记录,合同会审主表 where 合同会审主表.ContractNO=客户回款记录.ContractNO and 客户回款记录.[Date] between '<$MinDate$>' and '<$MaxDate$>' and 合同会审主表.ContractCategory in('数据标注')";
sb.Append("<tr>");
sb.Append("<td>R02</td>");
sb.Append("<td> 数据标注收入 </td>");
foreach (int i in MonthList)
{
string MinDate = Year + "-" + i + "-1";
string MaxDate = PublicFunctions.DateHelper.MonthHelper.GetLastDayOfMonth(Year, i).ToString("yyyy-MM-dd");
decimal value = Summary(context, BaseSQLDictionary["数据标注收入"], MinDate, MaxDate);
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
string MinDate = Year + "-" + QuarterMonthListDictionary[i].First() + "-1";
string MaxDate = PublicFunctions.DateHelper.MonthHelper.GetLastDayOfMonth(Year, QuarterMonthListDictionary[i].Last()).ToString("yyyy-MM-dd");
decimal value = Summary(context, BaseSQLDictionary["数据标注收入"], MinDate, MaxDate);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
string MinDate = Year + "-1-1";
string MaxDate = PublicFunctions.DateHelper.MonthHelper.GetLastDayOfMonth(Year, 12).ToString("yyyy-MM-dd");
decimal value = Summary(context, BaseSQLDictionary["数据标注收入"], MinDate, MaxDate);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
//其他收入
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["其他收入"] = "select value=sum(ISNULL(Money,0)) from 客户回款记录,合同会审主表 where 合同会审主表.ContractNO=客户回款记录.ContractNO and 客户回款记录.[Date] between '<$MinDate$>' and '<$MaxDate$>' and 合同会审主表.ContractCategory in('其他')";
sb.Append("<tr>");
sb.Append("<td>R99</td>");
sb.Append("<td> 其他 </td>");
foreach (int i in MonthList)
{
string MinDate = Year + "-" + i + "-1";
string MaxDate = PublicFunctions.DateHelper.MonthHelper.GetLastDayOfMonth(Year, i).ToString("yyyy-MM-dd");
decimal value = Summary(context, BaseSQLDictionary["其他收入"], MinDate, MaxDate);
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
string MinDate = Year + "-" + QuarterMonthListDictionary[i].First() + "-1";
string MaxDate = PublicFunctions.DateHelper.MonthHelper.GetLastDayOfMonth(Year, QuarterMonthListDictionary[i].Last()).ToString("yyyy-MM-dd");
decimal value = Summary(context, BaseSQLDictionary["其他收入"], MinDate, MaxDate);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
string MinDate = Year + "-1-1";
string MaxDate = PublicFunctions.DateHelper.MonthHelper.GetLastDayOfMonth(Year, 12).ToString("yyyy-MM-dd");
decimal value = Summary(context, BaseSQLDictionary["其他收入"], MinDate, MaxDate);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：三、成本总计（预算额）
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["成本总计"] = "select value=sum(ISNULL(预算金额,0)) from 项目费用预算月度表 where 年='<$Year$>' and 月 in(<$Month$>) and 一级科目 in('员工成本','其他成本')";
sb.Append("<tr>");
sb.Append("<td colspan=\"3\" class=\"DarkRedBg\"> 三、成本总计（预算额） </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["成本总计"], Year, new List<int>() { i });
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "成本总计预算", SummaryType = "月", MonthORQuarter = i, Value = value });
sb.Append("<td class='value DarkRedBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["成本总计"], Year, QuarterMonthListDictionary[i]);
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "成本总计预算", SummaryType = "季", MonthORQuarter = i, Value = value });
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["成本总计"], Year, MonthList);
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "成本总计预算", SummaryType = "年", MonthORQuarter = 0, Value = value });
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：三、成本总计（实际发生额）
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["成本总计"] = "select value=sum(ISNULL(已发生,0)) from 项目费用预算月度表 where 年='<$Year$>' and 月 in(<$Month$>) and 一级科目 in('员工成本','其他成本')";
sb.Append("<tr class=\"YellowBg\">");
sb.Append("<td colspan=\"3\"> 三、成本总计（实际发生额） </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["成本总计"], Year, new List<int>() { i });
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "成本总计实际", SummaryType = "月", MonthORQuarter = i, Value = value });
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["成本总计"], Year, QuarterMonthListDictionary[i]);
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "成本总计实际", SummaryType = "季", MonthORQuarter = i, Value = value });
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["成本总计"], Year, MonthList);
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "成本总计实际", SummaryType = "年", MonthORQuarter = 0, Value = value });
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：员工成本小计（预算额）
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["员工成本小计"] = "select value=sum(ISNULL(预算金额,0)) from 项目费用预算月度表,项目立项表 where 项目费用预算月度表.项目编码=项目立项表.项目编码 and 项目立项表.项目类型='研发部门' and 项目费用预算月度表.一级科目 in('员工成本') and 项目费用预算月度表.年='<$Year$>' and 项目费用预算月度表.月 in(<$Month$>)";
sb.Append("<tr>");
sb.Append("<td colspan=\"3\" class=\"LighterYellowBg\"> 员工成本小计（预算额） </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["员工成本小计"], Year, new List<int>() { i });
sb.Append("<td class='value LighterYellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["员工成本小计"], Year, QuarterMonthListDictionary[i]);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["员工成本小计"], Year, MonthList);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：员工成本小计（实际发生额）
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["员工成本小计"] = "select value=sum(ISNULL(已发生,0)) from 项目费用预算月度表,项目立项表 where 项目费用预算月度表.项目编码=项目立项表.项目编码 and 项目立项表.项目类型='研发部门' and 项目费用预算月度表.一级科目 in('员工成本') and 项目费用预算月度表.年='<$Year$>' and 项目费用预算月度表.月 in(<$Month$>)";
sb.Append("<tr>");
sb.Append("<td colspan=\"3\" class=\"LighterYellowBg\"> 员工成本小计（实际发生额） </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["员工成本小计"], Year, new List<int>() { i });
sb.Append("<td class='value LighterYellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["员工成本小计"], Year, QuarterMonthListDictionary[i]);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["员工成本小计"], Year, MonthList);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：员工成本明细
Dictionary<string, string> BudgetDictionary = GetBudgetDictionary(context, "研发部门", "员工成本");
int x = 0;
if (BudgetDictionary.Count > 0)
{
foreach (var pair in BudgetDictionary)
{
//预算额
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["员工成本小计"] = "select value=sum(ISNULL(预算金额,0)) from 项目费用预算月度表,项目立项表 where 项目费用预算月度表.项目编码=项目立项表.项目编码 and 项目立项表.项目类型='研发部门' and 项目费用预算月度表.一级科目 in('员工成本') and 项目费用预算月度表.二级科目='" + pair.Key + "' and 项目费用预算月度表.年='<$Year$>' and 项目费用预算月度表.月 in(<$Month$>)";
x++;
sb.Append("<tr>");
if (x == 1)
{
sb.Append("<td rowspan=\"" + (BudgetDictionary.Count * 2) + "\">员工成本<br />(主要统计研发部门）</td>");
}
sb.Append("<td class=\"BlueBg\">" + pair.Value + "</td>");
sb.Append("<td class=\"BlueBg\"> " + pair.Key + " </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["员工成本小计"], Year, new List<int>() { i });
sb.Append("<td class='value BlueBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["员工成本小计"], Year, QuarterMonthListDictionary[i]);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["员工成本小计"], Year, MonthList);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
//实际发生额
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["员工成本小计"] = "select value=sum(ISNULL(已发生,0)) from 项目费用预算月度表,项目立项表 where 项目费用预算月度表.项目编码=项目立项表.项目编码 and 项目立项表.项目类型='研发部门' and 项目费用预算月度表.一级科目 in('员工成本') and 项目费用预算月度表.二级科目='" + pair.Key + "' and 项目费用预算月度表.年='<$Year$>' and 项目费用预算月度表.月 in(<$Month$>)";
sb.Append("<tr>");
sb.Append("<td>" + pair.Value + "</td>");
sb.Append("<td> " + pair.Key + " </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["员工成本小计"], Year, new List<int>() { i });
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["员工成本小计"], Year, QuarterMonthListDictionary[i]);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["员工成本小计"], Year, MonthList);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
}
}
#endregion
#region 代码块说明：其他成本小计（预算额）
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["其他成本小计"] = "select value=sum(ISNULL(预算金额,0)) from 项目费用预算月度表 where 一级科目 in('其他成本') and 年='<$Year$>' and 月 in(<$Month$>)";
sb.Append("<tr>");
sb.Append("<td colspan=\"3\" class=\"LighterYellowBg\"> 其他成本小计（预算额） </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["其他成本小计"], Year, new List<int>() { i });
sb.Append("<td class='value LighterYellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["其他成本小计"], Year, QuarterMonthListDictionary[i]);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["其他成本小计"], Year, MonthList);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：其他成本小计（实际发生额）
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["其他成本小计"] = "select value=sum(ISNULL(已发生,0)) from 项目费用预算月度表 where 一级科目 in('其他成本') and 年='<$Year$>' and 月 in(<$Month$>)";
sb.Append("<tr>");
sb.Append("<td colspan=\"3\" class=\"LighterYellowBg\"> 其他成本小计（实际发生额） </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["其他成本小计"], Year, new List<int>() { i });
sb.Append("<td class='value LighterYellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["其他成本小计"], Year, QuarterMonthListDictionary[i]);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["其他成本小计"], Year, MonthList);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：其他成本明细
//注意：虽然统计所有部门的（每类项目都会添加相同的本一级科目），但是避免重复，只读研发部门的科目
BudgetDictionary = GetBudgetDictionary(context, "研发部门", "其他成本");
x = 0;
if (BudgetDictionary.Count > 0)
{
foreach (var pair in BudgetDictionary)
{
//预算额
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["其他成本小计"] = "select value=sum(ISNULL(预算金额,0)) from 项目费用预算月度表 where 一级科目 in('其他成本') and 二级科目='" + pair.Key + "' and 年='<$Year$>' and 月 in(<$Month$>)";
x++;
sb.Append("<tr>");
if (x == 1)
{
sb.Append("<td rowspan=\"" + (BudgetDictionary.Count * 2) + "\">其他成本</td>");
}
sb.Append("<td class=\"BlueBg\">" + pair.Value + "</td>");
sb.Append("<td class=\"BlueBg\"> " + pair.Key + " </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["其他成本小计"], Year, new List<int>() { i });
sb.Append("<td class='value BlueBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["其他成本小计"], Year, QuarterMonthListDictionary[i]);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["其他成本小计"], Year, MonthList);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
//实际发生额
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["其他成本小计"] = "select value=sum(ISNULL(已发生,0)) from 项目费用预算月度表 where 一级科目 in('其他成本') and 二级科目='" + pair.Key + "' and 年='<$Year$>' and 月 in(<$Month$>)";
sb.Append("<tr>");
sb.Append("<td>" + pair.Value + "</td>");
sb.Append("<td> " + pair.Key + " </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["其他成本小计"], Year, new List<int>() { i });
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["其他成本小计"], Year, QuarterMonthListDictionary[i]);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["其他成本小计"], Year, MonthList);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
}
}
#endregion
#region 代码块说明：四、税金成本（预算额）
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["税金成本"] = "select value=sum(ISNULL(预算金额,0)) from 项目费用预算月度表 where 年='<$Year$>' and 月 in(<$Month$>) and 一级科目 in('税金')";
sb.Append("<tr>");
sb.Append("<td colspan=\"3\" class=\"DarkRedBg\"> 四、税金成本（预算额） </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["税金成本"], Year, new List<int>() { i });
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "税金成本预算", SummaryType = "月", MonthORQuarter = i, Value = value });
sb.Append("<td class='value DarkRedBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["税金成本"], Year, QuarterMonthListDictionary[i]);
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "税金成本预算", SummaryType = "季", MonthORQuarter = i, Value = value });
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["税金成本"], Year, MonthList);
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "税金成本预算", SummaryType = "年", MonthORQuarter = 0, Value = value });
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：四、税金成本（实际发生额）
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["税金成本"] = "select value=sum(ISNULL(已发生,0)) from 项目费用预算月度表 where 年='<$Year$>' and 月 in(<$Month$>) and 一级科目 in('税金')";
sb.Append("<tr class=\"YellowBg\">");
sb.Append("<td colspan=\"3\"> 四、税金成本（实际发生额） </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["税金成本"], Year, new List<int>() { i });
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "税金成本实际", SummaryType = "月", MonthORQuarter = i, Value = value });
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["税金成本"], Year, QuarterMonthListDictionary[i]);
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "税金成本实际", SummaryType = "季", MonthORQuarter = i, Value = value });
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["税金成本"], Year, MonthList);
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "税金成本实际", SummaryType = "年", MonthORQuarter = 0, Value = value });
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：其他成本明细
//注意：虽然统计所有部门的（每类项目都会添加相同的本一级科目），但是避免重复，只读研发部门的科目
BudgetDictionary = GetBudgetDictionary(context, "研发部门", "税金");
x = 0;
if (BudgetDictionary.Count > 0)
{
foreach (var pair in BudgetDictionary)
{
//预算额
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["税金成本"] = "select value=sum(ISNULL(预算金额,0)) from 项目费用预算月度表 where 年='<$Year$>' and 月 in(<$Month$>) and 一级科目 in('税金') and 二级科目='" + pair.Key + "'";
x++;
sb.Append("<tr>");
if (x == 1)
{
sb.Append("<td rowspan=\"" + (BudgetDictionary.Count * 2) + "\">税金</td>");
}
sb.Append("<td class=\"BlueBg\">" + pair.Value + "</td>");
sb.Append("<td class=\"BlueBg\"> " + pair.Key + " </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["税金成本"], Year, new List<int>() { i });
sb.Append("<td class='value BlueBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["税金成本"], Year, QuarterMonthListDictionary[i]);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["税金成本"], Year, MonthList);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
//实际发生额
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["税金成本"] = "select value=sum(ISNULL(已发生,0)) from 项目费用预算月度表 where 年='<$Year$>' and 月 in(<$Month$>) and 一级科目 in('税金') and 二级科目='" + pair.Key + "'";
sb.Append("<tr>");
sb.Append("<td>" + pair.Value + "</td>");
sb.Append("<td> " + pair.Key + " </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["税金成本"], Year, new List<int>() { i });
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["税金成本"], Year, QuarterMonthListDictionary[i]);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["税金成本"], Year, MonthList);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
}
}
#endregion
#region 代码块说明：五、营业费用总计（预算额）
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["营业费用总计"] = "select value=sum(ISNULL(预算金额,0)) from 项目费用预算月度表,项目立项表 where 项目费用预算月度表.项目编码=项目立项表.项目编码 and 项目费用预算月度表.年='<$Year$>' and 项目费用预算月度表.月 in(<$Month$>) and ( (项目立项表.项目类型='销售部' and 项目费用预算月度表.一级科目='员工费用') OR (项目立项表.项目类型='市场部' and 项目费用预算月度表.一级科目='市场推广费用') OR 项目费用预算月度表.一级科目='其他营业费用' )";
sb.Append("<tr>");
sb.Append("<td colspan=\"3\" class=\"DarkRedBg\"> 五、营业费用总计（预算额） </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["营业费用总计"], Year, new List<int>() { i });
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "营业费用预算", SummaryType = "月", MonthORQuarter = i, Value = value });
sb.Append("<td class='value DarkRedBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["营业费用总计"], Year, QuarterMonthListDictionary[i]);
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "营业费用预算", SummaryType = "季", MonthORQuarter = i, Value = value });
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["营业费用总计"], Year, MonthList);
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "营业费用预算", SummaryType = "年", MonthORQuarter = 0, Value = value });
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：五、营业费用总计（实际发生额）
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["营业费用总计"] = "select value=sum(ISNULL(已发生,0)) from 项目费用预算月度表,项目立项表 where 项目费用预算月度表.项目编码=项目立项表.项目编码 and 项目费用预算月度表.年='<$Year$>' and 项目费用预算月度表.月 in(<$Month$>) and ( (项目立项表.项目类型='销售部' and 项目费用预算月度表.一级科目='员工费用') OR (项目立项表.项目类型='市场部' and 项目费用预算月度表.一级科目='市场推广费用') OR 项目费用预算月度表.一级科目='其他营业费用' )";
sb.Append("<tr class=\"YellowBg\">");
sb.Append("<td colspan=\"3\"> 五、营业费用总计（实际发生额） </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["营业费用总计"], Year, new List<int>() { i });
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "营业费用实际", SummaryType = "月", MonthORQuarter = i, Value = value });
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["营业费用总计"], Year, QuarterMonthListDictionary[i]);
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "营业费用实际", SummaryType = "季", MonthORQuarter = i, Value = value });
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["营业费用总计"], Year, MonthList);
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "营业费用实际", SummaryType = "年", MonthORQuarter = 0, Value = value });
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：员工费用小计（预算额）
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["员工费用小计"] = "select value=sum(ISNULL(预算金额,0)) from 项目费用预算月度表,项目立项表 where 项目费用预算月度表.项目编码=项目立项表.项目编码 and 项目费用预算月度表.年='<$Year$>' and 项目费用预算月度表.月 in(<$Month$>) and ( (项目立项表.项目类型='销售部' and 项目费用预算月度表.一级科目='员工费用') )";
sb.Append("<tr>");
sb.Append("<td colspan=\"3\" class=\"LighterYellowBg\"> 员工费用小计（预算额） </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["员工费用小计"], Year, new List<int>() { i });
sb.Append("<td class='value LighterYellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["员工费用小计"], Year, QuarterMonthListDictionary[i]);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["员工费用小计"], Year, MonthList);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：员工费用小计（实际发生额）
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["员工费用小计"] = "select value=sum(ISNULL(已发生,0)) from 项目费用预算月度表,项目立项表 where 项目费用预算月度表.项目编码=项目立项表.项目编码 and 项目费用预算月度表.年='<$Year$>' and 项目费用预算月度表.月 in(<$Month$>) and ( (项目立项表.项目类型='销售部' and 项目费用预算月度表.一级科目='员工费用') )";
sb.Append("<tr>");
sb.Append("<td colspan=\"3\" class=\"LighterYellowBg\"> 员工费用小计（实际发生额） </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["员工费用小计"], Year, new List<int>() { i });
sb.Append("<td class='value LighterYellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["员工费用小计"], Year, QuarterMonthListDictionary[i]);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["员工费用小计"], Year, MonthList);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：员工费用明细
BudgetDictionary = GetBudgetDictionary(context, "销售部", "员工费用");
x = 0;
if (BudgetDictionary.Count > 0)
{
foreach (var pair in BudgetDictionary)
{
//预算额
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["员工费用小计"] = "select value=sum(ISNULL(预算金额,0)) from 项目费用预算月度表,项目立项表 where 项目费用预算月度表.项目编码=项目立项表.项目编码 and 项目费用预算月度表.年='<$Year$>' and 项目费用预算月度表.月 in(<$Month$>) and ( (项目立项表.项目类型='销售部' and 项目费用预算月度表.一级科目='员工费用') ) and 项目费用预算月度表.二级科目='" + pair.Key + "'";
x++;
sb.Append("<tr>");
if (x == 1)
{
sb.Append("<td rowspan=\"" + (BudgetDictionary.Count * 2) + "\">员工费用<br>（销售部)</td>");
}
sb.Append("<td class=\"BlueBg\">" + pair.Value + "</td>");
sb.Append("<td class=\"BlueBg\"> " + pair.Key + " </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["员工费用小计"], Year, new List<int>() { i });
sb.Append("<td class='value BlueBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["员工费用小计"], Year, QuarterMonthListDictionary[i]);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["员工费用小计"], Year, MonthList);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
//实际发生额
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["员工费用小计"] = "select value=sum(ISNULL(已发生,0)) from 项目费用预算月度表,项目立项表 where 项目费用预算月度表.项目编码=项目立项表.项目编码 and 项目费用预算月度表.年='<$Year$>' and 项目费用预算月度表.月 in(<$Month$>) and ( (项目立项表.项目类型='销售部' and 项目费用预算月度表.一级科目='员工费用') ) and 项目费用预算月度表.二级科目='" + pair.Key + "'";
sb.Append("<tr>");
sb.Append("<td>" + pair.Value + "</td>");
sb.Append("<td> " + pair.Key + " </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["员工费用小计"], Year, new List<int>() { i });
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["员工费用小计"], Year, QuarterMonthListDictionary[i]);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["员工费用小计"], Year, MonthList);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
}
}
#endregion
#region 代码块说明：市场推广费用小计（预算额）
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["市场推广费用小计"] = "select value=sum(ISNULL(预算金额,0)) from 项目费用预算月度表,项目立项表 where 项目费用预算月度表.项目编码=项目立项表.项目编码 and 项目费用预算月度表.年='<$Year$>' and 项目费用预算月度表.月 in(<$Month$>) and ( (项目立项表.项目类型='市场部' and 项目费用预算月度表.一级科目='市场推广费用') )";
sb.Append("<tr>");
sb.Append("<td colspan=\"3\" class=\"LighterYellowBg\"> 市场推广费用小计（预算额） </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["市场推广费用小计"], Year, new List<int>() { i });
sb.Append("<td class='value LighterYellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["市场推广费用小计"], Year, QuarterMonthListDictionary[i]);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["市场推广费用小计"], Year, MonthList);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：市场推广费用小计（实际发生额）
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["市场推广费用小计"] = "select value=sum(ISNULL(已发生,0)) from 项目费用预算月度表,项目立项表 where 项目费用预算月度表.项目编码=项目立项表.项目编码 and 项目费用预算月度表.年='<$Year$>' and 项目费用预算月度表.月 in(<$Month$>) and ( (项目立项表.项目类型='市场部' and 项目费用预算月度表.一级科目='市场推广费用') )";
sb.Append("<tr>");
sb.Append("<td colspan=\"3\" class=\"LighterYellowBg\"> 市场推广费用小计（实际发生额） </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["市场推广费用小计"], Year, new List<int>() { i });
sb.Append("<td class='value LighterYellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["市场推广费用小计"], Year, QuarterMonthListDictionary[i]);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["市场推广费用小计"], Year, MonthList);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：市场推广费用明细
BudgetDictionary = GetBudgetDictionary(context, "市场部", "市场推广费用");
x = 0;
if (BudgetDictionary.Count > 0)
{
foreach (var pair in BudgetDictionary)
{
//预算额
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["市场推广费用小计"] = "select value=sum(ISNULL(预算金额,0)) from 项目费用预算月度表,项目立项表 where 项目费用预算月度表.项目编码=项目立项表.项目编码 and 项目费用预算月度表.年='<$Year$>' and 项目费用预算月度表.月 in(<$Month$>) and ( (项目立项表.项目类型='市场部' and 项目费用预算月度表.一级科目='市场推广费用') ) and 项目费用预算月度表.二级科目='" + pair.Key + "'";
x++;
sb.Append("<tr>");
if (x == 1)
{
sb.Append("<td rowspan=\"" + (BudgetDictionary.Count * 2) + "\">市场推广费用</td>");
}
sb.Append("<td class=\"BlueBg\">" + pair.Value + "</td>");
sb.Append("<td class=\"BlueBg\"> " + pair.Key + " </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["市场推广费用小计"], Year, new List<int>() { i });
sb.Append("<td class='value BlueBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["市场推广费用小计"], Year, QuarterMonthListDictionary[i]);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["市场推广费用小计"], Year, MonthList);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
//实际发生额
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["市场推广费用小计"] = "select value=sum(ISNULL(已发生,0)) from 项目费用预算月度表,项目立项表 where 项目费用预算月度表.项目编码=项目立项表.项目编码 and 项目费用预算月度表.年='<$Year$>' and 项目费用预算月度表.月 in(<$Month$>) and ( (项目立项表.项目类型='市场部' and 项目费用预算月度表.一级科目='市场推广费用') ) and 项目费用预算月度表.二级科目='" + pair.Key + "'";
sb.Append("<tr>");
sb.Append("<td>" + pair.Value + "</td>");
sb.Append("<td> " + pair.Key + " </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["市场推广费用小计"], Year, new List<int>() { i });
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["市场推广费用小计"], Year, QuarterMonthListDictionary[i]);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["市场推广费用小计"], Year, MonthList);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
}
}
#endregion
#region 代码块说明：其他营业费用小计（预算额）
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["其他营业费用小计"] = "select value=sum(ISNULL(预算金额,0)) from 项目费用预算月度表 where 年='<$Year$>' and 月 in(<$Month$>) and 一级科目='其他营业费用'";
sb.Append("<tr>");
sb.Append("<td colspan=\"3\" class=\"LighterYellowBg\"> 其他市场费用小计（预算额） </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["其他营业费用小计"], Year, new List<int>() { i });
sb.Append("<td class='value LighterYellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["其他营业费用小计"], Year, QuarterMonthListDictionary[i]);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["其他营业费用小计"], Year, MonthList);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：其他营业费用小计（实际发生额）
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["其他营业费用小计"] = "select value=sum(ISNULL(已发生,0)) from 项目费用预算月度表 where 年='<$Year$>' and 月 in(<$Month$>) and 一级科目='其他营业费用'";
sb.Append("<tr>");
sb.Append("<td colspan=\"3\" class=\"LighterYellowBg\"> 其他市场费用小计（实际发生额） </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["其他营业费用小计"], Year, new List<int>() { i });
sb.Append("<td class='value LighterYellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["其他营业费用小计"], Year, QuarterMonthListDictionary[i]);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["其他营业费用小计"], Year, MonthList);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：其他营业费用明细
//注意：虽然统计所有部门的（每类项目都会添加相同的本一级科目），但是避免重复，只读研发部门的科目
BudgetDictionary = GetBudgetDictionary(context, "研发部门", "其他营业费用");
x = 0;
if (BudgetDictionary.Count > 0)
{
foreach (var pair in BudgetDictionary)
{
//预算额
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["其他营业费用小计"] = "select value=sum(ISNULL(预算金额,0)) from 项目费用预算月度表 where 年='<$Year$>' and 月 in(<$Month$>) and 一级科目='其他营业费用' and 二级科目='" + pair.Key + "'";
x++;
sb.Append("<tr>");
if (x == 1)
{
sb.Append("<td rowspan=\"" + (BudgetDictionary.Count * 2) + "\">其他营业费用</td>");
}
sb.Append("<td class=\"BlueBg\">" + pair.Value + "</td>");
sb.Append("<td class=\"BlueBg\"> " + pair.Key + " </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["其他营业费用小计"], Year, new List<int>() { i });
sb.Append("<td class='value BlueBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["其他营业费用小计"], Year, QuarterMonthListDictionary[i]);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["其他营业费用小计"], Year, MonthList);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
//实际发生额
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["其他营业费用小计"] = "select value=sum(ISNULL(已发生,0)) from 项目费用预算月度表 where 年='<$Year$>' and 月 in(<$Month$>) and 一级科目='其他营业费用' and 二级科目='" + pair.Key + "'";
sb.Append("<tr>");
sb.Append("<td>" + pair.Value + "</td>");
sb.Append("<td> " + pair.Key + " </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["其他营业费用小计"], Year, new List<int>() { i });
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["其他营业费用小计"], Year, QuarterMonthListDictionary[i]);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["其他营业费用小计"], Year, MonthList);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
}
}
#endregion
#region 代码块说明：六、管理费用和财务费用总计（预算额）
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["管理费用和财务费用总计"] = "select value=sum(ISNULL(预算金额,0)) from 项目费用预算月度表,项目立项表 where 项目费用预算月度表.项目编码=项目立项表.项目编码 and 项目费用预算月度表.年='<$Year$>' and 项目费用预算月度表.月 in(<$Month$>) and 项目立项表.项目类型='职能部'";
sb.Append("<tr>");
sb.Append("<td colspan=\"3\" class=\"DarkRedBg\"> 六、管理费用&财务费用总计（预算额） </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["管理费用和财务费用总计"], Year, new List<int>() { i });
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "管理费用和财务费用预算", SummaryType = "月", MonthORQuarter = i, Value = value });
sb.Append("<td class='value DarkRedBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["管理费用和财务费用总计"], Year, QuarterMonthListDictionary[i]);
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "管理费用和财务费用预算", SummaryType = "季", MonthORQuarter = i, Value = value });
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["管理费用和财务费用总计"], Year, MonthList);
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "管理费用和财务费用预算", SummaryType = "年", MonthORQuarter = 0, Value = value });
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：六、管理费用和财务费用总计（实际发生额）
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["管理费用和财务费用总计"] = "select value=sum(ISNULL(已发生,0)) from 项目费用预算月度表,项目立项表 where 项目费用预算月度表.项目编码=项目立项表.项目编码 and 项目费用预算月度表.年='<$Year$>' and 项目费用预算月度表.月 in(<$Month$>) and 项目立项表.项目类型='职能部'";
sb.Append("<tr class=\"YellowBg\">");
sb.Append("<td colspan=\"3\"> 六、管理费用&财务费用总计（实际发生额） </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["管理费用和财务费用总计"], Year, new List<int>() { i });
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "管理费用和财务费用实际", SummaryType = "月", MonthORQuarter = i, Value = value });
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["管理费用和财务费用总计"], Year, QuarterMonthListDictionary[i]);
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "管理费用和财务费用实际", SummaryType = "月", MonthORQuarter = i, Value = value });
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["管理费用和财务费用总计"], Year, MonthList);
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "管理费用和财务费用实际", SummaryType = "月", MonthORQuarter = 0, Value = value });
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：员工费用小计（预算额）
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["员工费用小计"] = "select value=sum(ISNULL(预算金额,0)) from 项目费用预算月度表,项目立项表 where 项目费用预算月度表.项目编码=项目立项表.项目编码 and 项目费用预算月度表.年='<$Year$>' and 项目费用预算月度表.月 in(<$Month$>) and 项目立项表.项目类型='职能部' and 项目费用预算月度表.一级科目='员工费用'";
sb.Append("<tr>");
sb.Append("<td colspan=\"3\" class=\"LighterYellowBg\"> 员工费用小计（预算额） </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["员工费用小计"], Year, new List<int>() { i });
sb.Append("<td class='value LighterYellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["员工费用小计"], Year, QuarterMonthListDictionary[i]);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["员工费用小计"], Year, MonthList);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：员工费用小计（实际发生额）
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["员工费用小计"] = "select value=sum(ISNULL(已发生,0)) from 项目费用预算月度表,项目立项表 where 项目费用预算月度表.项目编码=项目立项表.项目编码 and 项目费用预算月度表.年='<$Year$>' and 项目费用预算月度表.月 in(<$Month$>) and 项目立项表.项目类型='职能部' and 项目费用预算月度表.一级科目='员工费用'";
sb.Append("<tr>");
sb.Append("<td colspan=\"3\" class=\"LighterYellowBg\"> 员工费用小计（实际发生额） </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["员工费用小计"], Year, new List<int>() { i });
sb.Append("<td class='value LighterYellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["员工费用小计"], Year, QuarterMonthListDictionary[i]);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["员工费用小计"], Year, MonthList);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：员工费用明细
BudgetDictionary = GetBudgetDictionary(context, "职能部", "员工费用");
x = 0;
if (BudgetDictionary.Count > 0)
{
foreach (var pair in BudgetDictionary)
{
//预算额
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["员工费用小计"] = "select value=sum(ISNULL(预算金额,0)) from 项目费用预算月度表,项目立项表 where 项目费用预算月度表.项目编码=项目立项表.项目编码 and 项目费用预算月度表.年='<$Year$>' and 项目费用预算月度表.月 in(<$Month$>) and 项目立项表.项目类型='职能部' and 项目费用预算月度表.一级科目='员工费用' and 项目费用预算月度表.二级科目='" + pair.Key + "'";
x++;
sb.Append("<tr>");
if (x == 1)
{
sb.Append("<td rowspan=\"" + (BudgetDictionary.Count * 2) + "\">员工费用</td>");
}
sb.Append("<td class=\"BlueBg\">" + pair.Value + "</td>");
sb.Append("<td class=\"BlueBg\"> " + pair.Key + " </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["员工费用小计"], Year, new List<int>() { i });
sb.Append("<td class='value BlueBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["员工费用小计"], Year, QuarterMonthListDictionary[i]);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["员工费用小计"], Year, MonthList);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
//实际发生额
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["员工费用小计"] = "select value=sum(ISNULL(已发生,0)) from 项目费用预算月度表,项目立项表 where 项目费用预算月度表.项目编码=项目立项表.项目编码 and 项目费用预算月度表.年='<$Year$>' and 项目费用预算月度表.月 in(<$Month$>) and 项目立项表.项目类型='职能部' and 项目费用预算月度表.一级科目='员工费用' and 项目费用预算月度表.二级科目='" + pair.Key + "'";
sb.Append("<tr>");
sb.Append("<td>" + pair.Value + "</td>");
sb.Append("<td> " + pair.Key + " </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["员工费用小计"], Year, new List<int>() { i });
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["员工费用小计"], Year, QuarterMonthListDictionary[i]);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["员工费用小计"], Year, MonthList);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
}
}
#endregion
#region 代码块说明：基础设施费用小计（预算额）
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["基础设施费用小计"] = "select value=sum(ISNULL(预算金额,0)) from 项目费用预算月度表,项目立项表 where 项目费用预算月度表.项目编码=项目立项表.项目编码 and 项目费用预算月度表.年='<$Year$>' and 项目费用预算月度表.月 in(<$Month$>) and 项目立项表.项目类型='职能部' and 项目费用预算月度表.一级科目='基础设施费用'";
sb.Append("<tr>");
sb.Append("<td colspan=\"3\" class=\"LighterYellowBg\"> 基础设施费用小计（预算额） </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["基础设施费用小计"], Year, new List<int>() { i });
sb.Append("<td class='value LighterYellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["基础设施费用小计"], Year, QuarterMonthListDictionary[i]);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["基础设施费用小计"], Year, MonthList);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：基础设施费用小计（实际发生额）
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["基础设施费用小计"] = "select value=sum(ISNULL(已发生,0)) from 项目费用预算月度表,项目立项表 where 项目费用预算月度表.项目编码=项目立项表.项目编码 and 项目费用预算月度表.年='<$Year$>' and 项目费用预算月度表.月 in(<$Month$>) and 项目立项表.项目类型='职能部' and 项目费用预算月度表.一级科目='基础设施费用'";
sb.Append("<tr>");
sb.Append("<td colspan=\"3\" class=\"LighterYellowBg\"> 基础设施费用小计（实际发生额） </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["基础设施费用小计"], Year, new List<int>() { i });
sb.Append("<td class='value LighterYellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["基础设施费用小计"], Year, QuarterMonthListDictionary[i]);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["基础设施费用小计"], Year, MonthList);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：基础设施费用明细
BudgetDictionary = GetBudgetDictionary(context, "职能部", "基础设施费用");
x = 0;
if (BudgetDictionary.Count > 0)
{
foreach (var pair in BudgetDictionary)
{
//预算额
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["基础设施费用小计"] = "select value=sum(ISNULL(预算金额,0)) from 项目费用预算月度表,项目立项表 where 项目费用预算月度表.项目编码=项目立项表.项目编码 and 项目费用预算月度表.年='<$Year$>' and 项目费用预算月度表.月 in(<$Month$>) and 项目立项表.项目类型='职能部' and 项目费用预算月度表.一级科目='基础设施费用' and 项目费用预算月度表.二级科目='" + pair.Key + "'";
x++;
sb.Append("<tr>");
if (x == 1)
{
sb.Append("<td rowspan=\"" + (BudgetDictionary.Count * 2) + "\">基础设施费用</td>");
}
sb.Append("<td class=\"BlueBg\">" + pair.Value + "</td>");
sb.Append("<td class=\"BlueBg\"> " + pair.Key + " </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["基础设施费用小计"], Year, new List<int>() { i });
sb.Append("<td class='value BlueBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["基础设施费用小计"], Year, QuarterMonthListDictionary[i]);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["基础设施费用小计"], Year, MonthList);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
//实际发生额
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["基础设施费用小计"] = "select value=sum(ISNULL(已发生,0)) from 项目费用预算月度表,项目立项表 where 项目费用预算月度表.项目编码=项目立项表.项目编码 and 项目费用预算月度表.年='<$Year$>' and 项目费用预算月度表.月 in(<$Month$>) and 项目立项表.项目类型='职能部' and 项目费用预算月度表.一级科目='基础设施费用' and 项目费用预算月度表.二级科目='" + pair.Key + "'";
sb.Append("<tr>");
sb.Append("<td>" + pair.Value + "</td>");
sb.Append("<td> " + pair.Key + " </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["基础设施费用小计"], Year, new List<int>() { i });
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["基础设施费用小计"], Year, QuarterMonthListDictionary[i]);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["基础设施费用小计"], Year, MonthList);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
}
}
#endregion
#region 代码块说明：其他费用小计（预算额）
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["其他费用小计"] = "select value=sum(ISNULL(预算金额,0)) from 项目费用预算月度表,项目立项表 where 项目费用预算月度表.项目编码=项目立项表.项目编码 and 项目费用预算月度表.年='<$Year$>' and 项目费用预算月度表.月 in(<$Month$>) and 项目立项表.项目类型='职能部' and 项目费用预算月度表.一级科目='其他费用'";
sb.Append("<tr>");
sb.Append("<td colspan=\"3\" class=\"LighterYellowBg\"> 其他费用小计（预算额） </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["其他费用小计"], Year, new List<int>() { i });
sb.Append("<td class='value LighterYellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["其他费用小计"], Year, QuarterMonthListDictionary[i]);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["其他费用小计"], Year, MonthList);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：其他费用小计（实际发生额）
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["其他费用小计"] = "select value=sum(ISNULL(已发生,0)) from 项目费用预算月度表,项目立项表 where 项目费用预算月度表.项目编码=项目立项表.项目编码 and 项目费用预算月度表.年='<$Year$>' and 项目费用预算月度表.月 in(<$Month$>) and 项目立项表.项目类型='职能部' and 项目费用预算月度表.一级科目='其他费用'";
sb.Append("<tr>");
sb.Append("<td colspan=\"3\" class=\"LighterYellowBg\"> 其他费用小计（实际发生额） </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["其他费用小计"], Year, new List<int>() { i });
sb.Append("<td class='value LighterYellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["其他费用小计"], Year, QuarterMonthListDictionary[i]);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["其他费用小计"], Year, MonthList);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：其他费用明细
BudgetDictionary = GetBudgetDictionary(context, "职能部", "其他费用");
x = 0;
if (BudgetDictionary.Count > 0)
{
foreach (var pair in BudgetDictionary)
{
//预算额
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["其他费用小计"] = "select value=sum(ISNULL(预算金额,0)) from 项目费用预算月度表,项目立项表 where 项目费用预算月度表.项目编码=项目立项表.项目编码 and 项目费用预算月度表.年='<$Year$>' and 项目费用预算月度表.月 in(<$Month$>) and 项目立项表.项目类型='职能部' and 项目费用预算月度表.一级科目='其他费用' and 项目费用预算月度表.二级科目='" + pair.Key + "'";
x++;
sb.Append("<tr>");
if (x == 1)
{
sb.Append("<td rowspan=\"" + (BudgetDictionary.Count * 2) + "\">其他费用</td>");
}
sb.Append("<td class=\"BlueBg\">" + pair.Value + "</td>");
sb.Append("<td class=\"BlueBg\"> " + pair.Key + " </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["其他费用小计"], Year, new List<int>() { i });
sb.Append("<td class='value BlueBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["其他费用小计"], Year, QuarterMonthListDictionary[i]);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["其他费用小计"], Year, MonthList);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
//实际发生额
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["其他费用小计"] = "select value=sum(ISNULL(已发生,0)) from 项目费用预算月度表,项目立项表 where 项目费用预算月度表.项目编码=项目立项表.项目编码 and 项目费用预算月度表.年='<$Year$>' and 项目费用预算月度表.月 in(<$Month$>) and 项目立项表.项目类型='职能部' and 项目费用预算月度表.一级科目='其他费用' and 项目费用预算月度表.二级科目='" + pair.Key + "'";
sb.Append("<tr>");
sb.Append("<td>" + pair.Value + "</td>");
sb.Append("<td> " + pair.Key + " </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["其他费用小计"], Year, new List<int>() { i });
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["其他费用小计"], Year, QuarterMonthListDictionary[i]);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["其他费用小计"], Year, MonthList);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
}
}
#endregion
#region 代码块说明：财务费用小计（预算额）
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["财务费用小计"] = "select value=sum(ISNULL(预算金额,0)) from 项目费用预算月度表,项目立项表 where 项目费用预算月度表.项目编码=项目立项表.项目编码 and 项目费用预算月度表.年='<$Year$>' and 项目费用预算月度表.月 in(<$Month$>) and 项目立项表.项目类型='职能部' and 项目费用预算月度表.一级科目='财务费用'";
sb.Append("<tr>");
sb.Append("<td colspan=\"3\" class=\"LighterYellowBg\"> 财务费用小计（预算额） </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["财务费用小计"], Year, new List<int>() { i });
sb.Append("<td class='value LighterYellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["财务费用小计"], Year, QuarterMonthListDictionary[i]);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["财务费用小计"], Year, MonthList);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：财务费用小计（实际发生额）
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["财务费用小计"] = "select value=sum(ISNULL(已发生,0)) from 项目费用预算月度表,项目立项表 where 项目费用预算月度表.项目编码=项目立项表.项目编码 and 项目费用预算月度表.年='<$Year$>' and 项目费用预算月度表.月 in(<$Month$>) and 项目立项表.项目类型='职能部' and 项目费用预算月度表.一级科目='财务费用'";
sb.Append("<tr>");
sb.Append("<td colspan=\"3\" class=\"LighterYellowBg\"> 财务费用小计（实际发生额） </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["财务费用小计"], Year, new List<int>() { i });
sb.Append("<td class='value LighterYellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["财务费用小计"], Year, QuarterMonthListDictionary[i]);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["财务费用小计"], Year, MonthList);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：财务费用明细
BudgetDictionary = GetBudgetDictionary(context, "职能部", "财务费用");
x = 0;
if (BudgetDictionary.Count > 0)
{
foreach (var pair in BudgetDictionary)
{
//预算额
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["财务费用小计"] = "select value=sum(ISNULL(预算金额,0)) from 项目费用预算月度表,项目立项表 where 项目费用预算月度表.项目编码=项目立项表.项目编码 and 项目费用预算月度表.年='<$Year$>' and 项目费用预算月度表.月 in(<$Month$>) and 项目立项表.项目类型='职能部' and 项目费用预算月度表.一级科目='财务费用' and 项目费用预算月度表.二级科目='" + pair.Key + "'";
x++;
sb.Append("<tr>");
if (x == 1)
{
sb.Append("<td rowspan=\"" + (BudgetDictionary.Count * 2) + "\">财务费用</td>");
}
sb.Append("<td class=\"BlueBg\">" + pair.Value + "</td>");
sb.Append("<td class=\"BlueBg\"> " + pair.Key + " </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["财务费用小计"], Year, new List<int>() { i });
sb.Append("<td class='value BlueBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["财务费用小计"], Year, QuarterMonthListDictionary[i]);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["财务费用小计"], Year, MonthList);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
//实际发生额
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["财务费用小计"] = "select value=sum(ISNULL(已发生,0)) from 项目费用预算月度表,项目立项表 where 项目费用预算月度表.项目编码=项目立项表.项目编码 and 项目费用预算月度表.年='<$Year$>' and 项目费用预算月度表.月 in(<$Month$>) and 项目立项表.项目类型='职能部' and 项目费用预算月度表.一级科目='财务费用' and 项目费用预算月度表.二级科目='" + pair.Key + "'";
sb.Append("<tr>");
sb.Append("<td>" + pair.Value + "</td>");
sb.Append("<td> " + pair.Key + " </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["财务费用小计"], Year, new List<int>() { i });
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["财务费用小计"], Year, QuarterMonthListDictionary[i]);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["财务费用小计"], Year, MonthList);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
}
}
#endregion
#region 代码块说明：员工人数
sb.Append("<tr class=\"YellowBg\">");
sb.Append("<td colspan=\"3\"> 七、员工人数（实际当月数） </td>");
Dictionary<int, decimal> MonthTotalEmployeeDictionary = new Dictionary<int, decimal>();
foreach (int i in MonthList)
{
decimal value = CountTotalEmployee(context, Year + "-" + i + "-1", PublicFunctions.DateHelper.MonthHelper.GetLastDayOfMonth(Year, i).ToString("yyyy-MM-dd"));
MonthTotalEmployeeDictionary[i] = value;
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "员工人数", SummaryType = "月", MonthORQuarter = i, Value = value });
sb.Append("<td class='value'>" + value + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = 0;
foreach (int month in QuarterMonthListDictionary[i])
{
value += MonthTotalEmployeeDictionary[month];
}
value = ConvertData.ConvertDecimal(value) / 3;
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "员工人数", SummaryType = "季", MonthORQuarter = i, Value = value });
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
var value = MonthTotalEmployeeDictionary.Values.Sum();
value = ConvertData.ConvertDecimal(value) / MonthTotalEmployeeDictionary.Count;
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "员工人数", SummaryType = "年", Value = value });
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：八、费用调整（预算额）
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["费用调整"] = "select value=sum(ISNULL(预算金额,0)) from 项目费用预算月度表 where 年='<$Year$>' and 月 in(<$Month$>) and 一级科目 in('费用调整')";
sb.Append("<tr>");
sb.Append("<td colspan=\"3\" class=\"DarkRedBg\"> 八、费用调整总计（预算额） </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["费用调整"], Year, new List<int>() { i });
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "费用调整预算", SummaryType = "月", MonthORQuarter = i, Value = value });
sb.Append("<td class='value DarkRedBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["费用调整"], Year, QuarterMonthListDictionary[i]);
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "费用调整预算", SummaryType = "季", MonthORQuarter = i, Value = value });
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["费用调整"], Year, MonthList);
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "费用调整预算", SummaryType = "年", MonthORQuarter = 0, Value = value });
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：八、费用调整（实际发生额）
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["费用调整"] = "select value=sum(ISNULL(已发生,0)) from 项目费用预算月度表 where 年='<$Year$>' and 月 in(<$Month$>) and 一级科目 in('费用调整')";
sb.Append("<tr class=\"YellowBg\">");
sb.Append("<td colspan=\"3\"> 八、费用调整（实际发生额） </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["费用调整"], Year, new List<int>() { i });
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "费用调整实际", SummaryType = "月", MonthORQuarter = i, Value = value });
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["费用调整"], Year, QuarterMonthListDictionary[i]);
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "费用调整实际", SummaryType = "季", MonthORQuarter = i, Value = value });
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["费用调整"], Year, MonthList);
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "费用调整实际", SummaryType = "年", MonthORQuarter = 0, Value = value });
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：费用调整明细
//注意：虽然统计所有部门的（每类项目都会添加相同的本一级科目），但是避免重复，只读研发部门的科目
BudgetDictionary = GetBudgetDictionary(context, "研发部门", "费用调整");
x = 0;
if (BudgetDictionary.Count > 0)
{
foreach (var pair in BudgetDictionary)
{
//预算额
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["费用调整"] = "select value=sum(ISNULL(预算金额,0)) from 项目费用预算月度表 where 年='<$Year$>' and 月 in(<$Month$>) and 一级科目 in('费用调整') and 二级科目='" + pair.Key + "'";
x++;
sb.Append("<tr>");
if (x == 1)
{
sb.Append("<td rowspan=\"" + (BudgetDictionary.Count * 2) + "\">费用调整</td>");
}
sb.Append("<td class=\"BlueBg\">" + pair.Value + "</td>");
sb.Append("<td class=\"BlueBg\"> " + pair.Key + " </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["费用调整"], Year, new List<int>() { i });
sb.Append("<td class='value BlueBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["费用调整"], Year, QuarterMonthListDictionary[i]);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["费用调整"], Year, MonthList);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
//实际发生额
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["费用调整"] = "select value=sum(ISNULL(已发生,0)) from 项目费用预算月度表 where 年='<$Year$>' and 月 in(<$Month$>) and 一级科目 in('费用调整') and 二级科目='" + pair.Key + "'";
sb.Append("<tr>");
sb.Append("<td>" + pair.Value + "</td>");
sb.Append("<td> " + pair.Key + " </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["费用调整"], Year, new List<int>() { i });
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["费用调整"], Year, QuarterMonthListDictionary[i]);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["费用调整"], Year, MonthList);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
}
}
#endregion
#region 代码块说明：九、部门最终利润（预算）
//公式：收入总计-成本总计-税金成本-营业费用总计-管理费、财务费总计-费用调整总计（预算）
sb.Append("<tr>");
sb.Append("<td colspan=\"3\" class=\"DarkRedBg\"> 九、部门最终利润（预算发生额） </td>");
foreach (int i in MonthList)
{
//收入总计
decimal 收入总计 = (from item in SummaryResultList where item.SummaryName == "收入总计预算" && item.SummaryType == "月" && item.MonthORQuarter == i select item.Value).Sum();
//成本总计
decimal 成本总计 = (from item in SummaryResultList where item.SummaryName == "成本总计预算" && item.SummaryType == "月" && item.MonthORQuarter == i select item.Value).Sum();
//税金成本
decimal 税金成本 = (from item in SummaryResultList where item.SummaryName == "税金成本预算" && item.SummaryType == "月" && item.MonthORQuarter == i select item.Value).Sum();
//营业费用总计
decimal 营业费用 = (from item in SummaryResultList where item.SummaryName == "营业费用预算" && item.SummaryType == "月" && item.MonthORQuarter == i select item.Value).Sum();
//管理费、财务费总计
decimal 管理费和财务费 = (from item in SummaryResultList where item.SummaryName == "管理费用和财务费用预算" && item.SummaryType == "月" && item.MonthORQuarter == i select item.Value).Sum();
//费用调整总计
decimal 费用调整 = (from item in SummaryResultList where item.SummaryName == "费用调整预算" && item.SummaryType == "月" && item.MonthORQuarter == i select item.Value).Sum();
//计算最终利润
decimal value = 收入总计 - 成本总计 - 税金成本 - 营业费用 - 管理费和财务费 - 费用调整;
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "部门最终利润预算", SummaryType = "月", MonthORQuarter = i, Value = value });
sb.Append("<td class='value DarkRedBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
//收入总计
decimal 收入总计 = (from item in SummaryResultList where item.SummaryName == "收入总计预算" && item.SummaryType == "季" && item.MonthORQuarter == i select item.Value).Sum();
//成本总计
decimal 成本总计 = (from item in SummaryResultList where item.SummaryName == "成本总计预算" && item.SummaryType == "季" && item.MonthORQuarter == i select item.Value).Sum();
//税金成本
decimal 税金成本 = (from item in SummaryResultList where item.SummaryName == "税金成本预算" && item.SummaryType == "季" && item.MonthORQuarter == i select item.Value).Sum();
//营业费用总计
decimal 营业费用 = (from item in SummaryResultList where item.SummaryName == "营业费用预算" && item.SummaryType == "季" && item.MonthORQuarter == i select item.Value).Sum();
//管理费、财务费总计
decimal 管理费和财务费 = (from item in SummaryResultList where item.SummaryName == "管理费用和财务费用预算" && item.SummaryType == "季" && item.MonthORQuarter == i select item.Value).Sum();
//费用调整总计
decimal 费用调整 = (from item in SummaryResultList where item.SummaryName == "费用调整预算" && item.SummaryType == "季" && item.MonthORQuarter == i select item.Value).Sum();
//计算最终利润
decimal value = 收入总计 - 成本总计 - 税金成本 - 营业费用 - 管理费和财务费 - 费用调整;
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "部门最终利润预算", SummaryType = "季", MonthORQuarter = i, Value = value });
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
//收入总计
decimal 收入总计 = (from item in SummaryResultList where item.SummaryName == "收入总计预算" && item.SummaryType == "年" select item.Value).Sum();
//成本总计
decimal 成本总计 = (from item in SummaryResultList where item.SummaryName == "成本总计预算" && item.SummaryType == "年" select item.Value).Sum();
//税金成本
decimal 税金成本 = (from item in SummaryResultList where item.SummaryName == "税金成本预算" && item.SummaryType == "年" select item.Value).Sum();
//营业费用总计
decimal 营业费用 = (from item in SummaryResultList where item.SummaryName == "营业费用预算" && item.SummaryType == "年" select item.Value).Sum();
//管理费、财务费总计
decimal 管理费和财务费 = (from item in SummaryResultList where item.SummaryName == "管理费用和财务费用预算" && item.SummaryType == "年" select item.Value).Sum();
//费用调整总计
decimal 费用调整 = (from item in SummaryResultList where item.SummaryName == "费用调整预算" && item.SummaryType == "年" select item.Value).Sum();
//计算最终利润
decimal value = 收入总计 - 成本总计 - 税金成本 - 营业费用 - 管理费和财务费 - 费用调整;
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "部门最终利润预算", SummaryType = "年", MonthORQuarter = 0, Value = value });
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：九、部门最终利润（实际发生额）
//公式：收入总计-成本总计-税金成本-营业费用总计-管理费、财务费总计-费用调整总计（实际）
sb.Append("<tr class=\"YellowBg\">");
sb.Append("<td colspan=\"3\"> 九、部门最终利润（实际发生额） </td>");
foreach (int i in MonthList)
{
//收入总计
decimal 收入总计 = (from item in SummaryResultList where item.SummaryName == "收入总计实际" && item.SummaryType == "月" && item.MonthORQuarter == i select item.Value).Sum();
//成本总计
decimal 成本总计 = (from item in SummaryResultList where item.SummaryName == "成本总计实际" && item.SummaryType == "月" && item.MonthORQuarter == i select item.Value).Sum();
//税金成本
decimal 税金成本 = (from item in SummaryResultList where item.SummaryName == "税金成本实际" && item.SummaryType == "月" && item.MonthORQuarter == i select item.Value).Sum();
//营业费用总计
decimal 营业费用 = (from item in SummaryResultList where item.SummaryName == "营业费用实际" && item.SummaryType == "月" && item.MonthORQuarter == i select item.Value).Sum();
//管理费、财务费总计
decimal 管理费和财务费 = (from item in SummaryResultList where item.SummaryName == "管理费用和财务费用实际" && item.SummaryType == "月" && item.MonthORQuarter == i select item.Value).Sum();
//费用调整总计
decimal 费用调整 = (from item in SummaryResultList where item.SummaryName == "费用调整实际" && item.SummaryType == "月" && item.MonthORQuarter == i select item.Value).Sum();
//计算最终利润
decimal value = 收入总计 - 成本总计 - 税金成本 - 营业费用 - 管理费和财务费 - 费用调整;
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "部门最终利润实际", SummaryType = "月", MonthORQuarter = i, Value = value });
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
//收入总计
decimal 收入总计 = (from item in SummaryResultList where item.SummaryName == "收入总计实际" && item.SummaryType == "季" && item.MonthORQuarter == i select item.Value).Sum();
//成本总计
decimal 成本总计 = (from item in SummaryResultList where item.SummaryName == "成本总计实际" && item.SummaryType == "季" && item.MonthORQuarter == i select item.Value).Sum();
//税金成本
decimal 税金成本 = (from item in SummaryResultList where item.SummaryName == "税金成本实际" && item.SummaryType == "季" && item.MonthORQuarter == i select item.Value).Sum();
//营业费用总计
decimal 营业费用 = (from item in SummaryResultList where item.SummaryName == "营业费用实际" && item.SummaryType == "季" && item.MonthORQuarter == i select item.Value).Sum();
//管理费、财务费总计
decimal 管理费和财务费 = (from item in SummaryResultList where item.SummaryName == "管理费用和财务费用实际" && item.SummaryType == "季" && item.MonthORQuarter == i select item.Value).Sum();
//费用调整总计
decimal 费用调整 = (from item in SummaryResultList where item.SummaryName == "费用调整实际" && item.SummaryType == "季" && item.MonthORQuarter == i select item.Value).Sum();
//计算最终利润
decimal value = 收入总计 - 成本总计 - 税金成本 - 营业费用 - 管理费和财务费 - 费用调整;
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "部门最终利润实际", SummaryType = "季", MonthORQuarter = i, Value = value });
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
//收入总计
decimal 收入总计 = (from item in SummaryResultList where item.SummaryName == "收入总计实际" && item.SummaryType == "年" select item.Value).Sum();
//成本总计
decimal 成本总计 = (from item in SummaryResultList where item.SummaryName == "成本总计实际" && item.SummaryType == "年" select item.Value).Sum();
//税金成本
decimal 税金成本 = (from item in SummaryResultList where item.SummaryName == "税金成本实际" && item.SummaryType == "年" select item.Value).Sum();
//营业费用总计
decimal 营业费用 = (from item in SummaryResultList where item.SummaryName == "营业费用实际" && item.SummaryType == "年" select item.Value).Sum();
//管理费、财务费总计
decimal 管理费和财务费 = (from item in SummaryResultList where item.SummaryName == "管理费用和财务费用实际" && item.SummaryType == "年" select item.Value).Sum();
//费用调整总计
decimal 费用调整 = (from item in SummaryResultList where item.SummaryName == "费用调整实际" && item.SummaryType == "年" select item.Value).Sum();
//计算最终利润
decimal value = 收入总计 - 成本总计 - 税金成本 - 营业费用 - 管理费和财务费 - 费用调整;
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "部门最终利润实际", SummaryType = "年", MonthORQuarter = 0, Value = value });
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：十、部门实际利润率（预算）
//部门最终利润/收入总计（预算）
sb.Append("<tr>");
sb.Append("<td colspan=\"3\" class=\"DarkRedBg\"> 十、部门实际利润率（预算额） </td>");
foreach (int i in MonthList)
{
//收入总计
decimal 收入总计 = (from item in SummaryResultList where item.SummaryName == "收入总计预算" && item.SummaryType == "月" && item.MonthORQuarter == i select item.Value).Sum();
//部门最终利润
decimal 部门最终利润 = (from item in SummaryResultList where item.SummaryName == "部门最终利润预算" && item.SummaryType == "月" && item.MonthORQuarter == i select item.Value).Sum();
//计算最终利润
decimal value = ConvertData.ConvertToDisplayMoney((收入总计 > 0 ? 部门最终利润 / 收入总计 : 0) * 100);
sb.Append("<td class='value DarkRedBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "%</td>");
}
foreach (int i in QuarterList)
{
//收入总计
decimal 收入总计 = (from item in SummaryResultList where item.SummaryName == "收入总计预算" && item.SummaryType == "季" && item.MonthORQuarter == i select item.Value).Sum();
//部门最终利润
decimal 部门最终利润 = (from item in SummaryResultList where item.SummaryName == "部门最终利润预算" && item.SummaryType == "季" && item.MonthORQuarter == i select item.Value).Sum();
//计算最终利润
decimal value = ConvertData.ConvertToDisplayMoney((收入总计 > 0 ? 部门最终利润 / 收入总计 : 0) * 100);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "%</td>");
}
if (true)
{
//收入总计
decimal 收入总计 = (from item in SummaryResultList where item.SummaryName == "收入总计预算" && item.SummaryType == "年" select item.Value).Sum();
//部门最终利润
decimal 部门最终利润 = (from item in SummaryResultList where item.SummaryName == "部门最终利润预算" && item.SummaryType == "年" select item.Value).Sum();
//计算最终利润
decimal value = ConvertData.ConvertToDisplayMoney((收入总计 > 0 ? 部门最终利润 / 收入总计 : 0) * 100);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "%</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：十、部门实际利润率（实际）
//部门最终利润/收入总计（实际）
sb.Append("<tr class=\"YellowBg\">");
sb.Append("<td colspan=\"3\"> 十、部门实际利润率（实际额） </td>");
foreach (int i in MonthList)
{
//收入总计
decimal 收入总计 = (from item in SummaryResultList where item.SummaryName == "收入总计实际" && item.SummaryType == "月" && item.MonthORQuarter == i select item.Value).Sum();
//部门最终利润
decimal 部门最终利润 = (from item in SummaryResultList where item.SummaryName == "部门最终利润实际" && item.SummaryType == "月" && item.MonthORQuarter == i select item.Value).Sum();
//计算最终利润
decimal value = ConvertData.ConvertToDisplayMoney((收入总计 > 0 ? 部门最终利润 / 收入总计 : 0) * 100);
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "%</td>");
}
foreach (int i in QuarterList)
{
//收入总计
decimal 收入总计 = (from item in SummaryResultList where item.SummaryName == "收入总计实际" && item.SummaryType == "季" && item.MonthORQuarter == i select item.Value).Sum();
//部门最终利润
decimal 部门最终利润 = (from item in SummaryResultList where item.SummaryName == "部门最终利润实际" && item.SummaryType == "季" && item.MonthORQuarter == i select item.Value).Sum();
//计算最终利润
decimal value = ConvertData.ConvertToDisplayMoney((收入总计 > 0 ? 部门最终利润 / 收入总计 : 0) * 100);
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "%</td>");
}
if (true)
{
//收入总计
decimal 收入总计 = (from item in SummaryResultList where item.SummaryName == "收入总计实际" && item.SummaryType == "年" select item.Value).Sum();
//部门最终利润
decimal 部门最终利润 = (from item in SummaryResultList where item.SummaryName == "部门最终利润实际" && item.SummaryType == "年" select item.Value).Sum();
//计算最终利润
decimal value = ConvertData.ConvertToDisplayMoney((收入总计 > 0 ? 部门最终利润 / 收入总计 : 0) * 100);
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "%</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：十一、固定资产&无形资产采购小计（预算额）
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["固定资产"] = "select value=sum(ISNULL(预算金额,0)) from 项目费用预算月度表 where 年='<$Year$>' and 月 in(<$Month$>) and 一级科目 in('固定资产')";
sb.Append("<tr>");
sb.Append("<td colspan=\"3\" class=\"DarkRedBg\"> 十一、固定资产&无形资产采购小计（预算额） </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["固定资产"], Year, new List<int>() { i });
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "固定资产预算", SummaryType = "月", MonthORQuarter = i, Value = value });
sb.Append("<td class='value DarkRedBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["固定资产"], Year, QuarterMonthListDictionary[i]);
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "固定资产预算", SummaryType = "季", MonthORQuarter = i, Value = value });
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["固定资产"], Year, MonthList);
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "固定资产预算", SummaryType = "年", MonthORQuarter = 0, Value = value });
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：十一、固定资产&无形资产采购小计（实际发生额）
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["固定资产"] = "select value=sum(ISNULL(已发生,0)) from 项目费用预算月度表 where 年='<$Year$>' and 月 in(<$Month$>) and 一级科目 in('固定资产')";
sb.Append("<tr class=\"YellowBg\">");
sb.Append("<td colspan=\"3\"> 十一、固定资产&无形资产采购小计（实际发生额） </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["固定资产"], Year, new List<int>() { i });
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "固定资产实际", SummaryType = "月", MonthORQuarter = i, Value = value });
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["固定资产"], Year, QuarterMonthListDictionary[i]);
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "固定资产实际", SummaryType = "季", MonthORQuarter = i, Value = value });
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["固定资产"], Year, MonthList);
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "固定资产实际", SummaryType = "年", MonthORQuarter = 0, Value = value });
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：固定资产明细
//注意：虽然统计所有部门的（每类项目都会添加相同的本一级科目），但是避免重复，只读研发部门的科目
BudgetDictionary = GetBudgetDictionary(context, "研发部门", "固定资产");
x = 0;
if (BudgetDictionary.Count > 0)
{
foreach (var pair in BudgetDictionary)
{
//预算额
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["固定资产"] = "select value=sum(ISNULL(预算金额,0)) from 项目费用预算月度表 where 年='<$Year$>' and 月 in(<$Month$>) and 一级科目 in('固定资产') and 二级科目='" + pair.Key + "'";
x++;
sb.Append("<tr>");
if (x == 1)
{
sb.Append("<td rowspan=\"" + (BudgetDictionary.Count * 2) + "\">固定资产</td>");
}
sb.Append("<td class=\"BlueBg\">" + pair.Value + "</td>");
sb.Append("<td class=\"BlueBg\"> " + pair.Key + " </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["固定资产"], Year, new List<int>() { i });
sb.Append("<td class='value BlueBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["固定资产"], Year, QuarterMonthListDictionary[i]);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["固定资产"], Year, MonthList);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
//实际发生额
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["固定资产"] = "select value=sum(ISNULL(已发生,0)) from 项目费用预算月度表 where 年='<$Year$>' and 月 in(<$Month$>) and 一级科目 in('固定资产') and 二级科目='" + pair.Key + "'";
sb.Append("<tr>");
sb.Append("<td>" + pair.Value + "</td>");
sb.Append("<td> " + pair.Key + " </td>");
foreach (int i in MonthList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["固定资产"], Year, new List<int>() { i });
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["固定资产"], Year, QuarterMonthListDictionary[i]);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
decimal value = SummaryBudget(context, BaseSQLDictionary["固定资产"], Year, MonthList);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
}
}
#endregion
#region 代码块说明：十四、利润分配（预算）
sb.Append("<tr>");
sb.Append("<td colspan=\"3\" class=\"DarkRedBg\"> 十四、利润分配（预算额） </td>");
foreach (int i in MonthList)
{
//部门最终利润
decimal 部门最终利润 = (from item in SummaryResultList where item.SummaryName == "部门最终利润预算" && item.SummaryType == "月" && item.MonthORQuarter == i select item.Value).Sum();
//利润分配
decimal value = 部门最终利润 > 0 ? 部门最终利润 * ConvertData.ConvertDecimal(0.25) : 0;
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "利润分配预算", SummaryType = "月", MonthORQuarter = i, Value = value });
sb.Append("<td class='value DarkRedBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
//部门最终利润
decimal 部门最终利润 = (from item in SummaryResultList where item.SummaryName == "部门最终利润预算" && item.SummaryType == "季" && item.MonthORQuarter == i select item.Value).Sum();
//利润分配
decimal value = 部门最终利润 > 0 ? 部门最终利润 * ConvertData.ConvertDecimal(0.25) : 0;
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "利润分配预算", SummaryType = "季", MonthORQuarter = i, Value = value });
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
//部门最终利润
decimal 部门最终利润 = (from item in SummaryResultList where item.SummaryName == "部门最终利润预算" && item.SummaryType == "年" select item.Value).Sum();
//利润分配
decimal value = 部门最终利润 > 0 ? 部门最终利润 * ConvertData.ConvertDecimal(0.25) : 0;
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "利润分配预算", SummaryType = "年", Value = value });
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：十四、利润分配（实际）
sb.Append("<tr class=\"YellowBg\">");
sb.Append("<td colspan=\"3\"> 十四、利润分配（实际发生额） </td>");
foreach (int i in MonthList)
{
//部门最终利润
decimal 部门最终利润 = (from item in SummaryResultList where item.SummaryName == "部门最终利润实际" && item.SummaryType == "月" && item.MonthORQuarter == i select item.Value).Sum();
//利润分配
decimal value = 部门最终利润 > 0 ? 部门最终利润 * ConvertData.ConvertDecimal(0.25) : 0;
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "利润分配实际", SummaryType = "月", MonthORQuarter = i, Value = value });
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
//部门最终利润
decimal 部门最终利润 = (from item in SummaryResultList where item.SummaryName == "部门最终利润实际" && item.SummaryType == "季" && item.MonthORQuarter == i select item.Value).Sum();
//利润分配
decimal value = 部门最终利润 > 0 ? 部门最终利润 * ConvertData.ConvertDecimal(0.25) : 0;
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "利润分配实际", SummaryType = "季", MonthORQuarter = i, Value = value });
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
//部门最终利润
decimal 部门最终利润 = (from item in SummaryResultList where item.SummaryName == "部门最终利润实际" && item.SummaryType == "年" select item.Value).Sum();
//利润分配
decimal value = 部门最终利润 > 0 ? 部门最终利润 * ConvertData.ConvertDecimal(0.25) : 0;
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "利润分配实际", SummaryType = "年", Value = value });
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：十五、内报税前利润（预算）
sb.Append("<tr>");
sb.Append("<td colspan=\"3\" class=\"DarkRedBg\"> 十五、内报税前利润（预算额） </td>");
foreach (int i in MonthList)
{
//部门最终利润
decimal 部门最终利润 = (from item in SummaryResultList where item.SummaryName == "部门最终利润预算" && item.SummaryType == "月" && item.MonthORQuarter == i select item.Value).Sum();
//利润分配
decimal 利润分配 = (from item in SummaryResultList where item.SummaryName == "利润分配预算" && item.SummaryType == "月" && item.MonthORQuarter == i select item.Value).Sum();
//内报税前利润
decimal value = 部门最终利润 - 利润分配;
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "内报税前利润预算", SummaryType = "月", MonthORQuarter = i, Value = value });
sb.Append("<td class='value DarkRedBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
//部门最终利润
decimal 部门最终利润 = (from item in SummaryResultList where item.SummaryName == "部门最终利润预算" && item.SummaryType == "季" && item.MonthORQuarter == i select item.Value).Sum();
//利润分配
decimal 利润分配 = (from item in SummaryResultList where item.SummaryName == "利润分配预算" && item.SummaryType == "季" && item.MonthORQuarter == i select item.Value).Sum();
//内报税前利润
decimal value = 部门最终利润 - 利润分配;
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "内报税前利润预算", SummaryType = "季", MonthORQuarter = i, Value = value });
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
//部门最终利润
decimal 部门最终利润 = (from item in SummaryResultList where item.SummaryName == "部门最终利润预算" && item.SummaryType == "年" select item.Value).Sum();
//利润分配
decimal 利润分配 = (from item in SummaryResultList where item.SummaryName == "利润分配预算" && item.SummaryType == "年" select item.Value).Sum();
//内报税前利润
decimal value = 部门最终利润 - 利润分配;
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "内报税前利润预算", SummaryType = "年", Value = value });
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：十五、内报税前利润（实际）
sb.Append("<tr class=\"YellowBg\">");
sb.Append("<td colspan=\"3\"> 十五、内报税前利润（实际发生额） </td>");
foreach (int i in MonthList)
{
//部门最终利润
decimal 部门最终利润 = (from item in SummaryResultList where item.SummaryName == "部门最终利润实际" && item.SummaryType == "月" && item.MonthORQuarter == i select item.Value).Sum();
//利润分配
decimal 利润分配 = (from item in SummaryResultList where item.SummaryName == "利润分配实际" && item.SummaryType == "月" && item.MonthORQuarter == i select item.Value).Sum();
//内报税前利润
decimal value = 部门最终利润 - 利润分配;
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "内报税前利润实际", SummaryType = "月", MonthORQuarter = i, Value = value });
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
//部门最终利润
decimal 部门最终利润 = (from item in SummaryResultList where item.SummaryName == "部门最终利润实际" && item.SummaryType == "季" && item.MonthORQuarter == i select item.Value).Sum();
//利润分配
decimal 利润分配 = (from item in SummaryResultList where item.SummaryName == "利润分配实际" && item.SummaryType == "季" && item.MonthORQuarter == i select item.Value).Sum();
//内报税前利润
decimal value = 部门最终利润 - 利润分配;
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "内报税前利润实际", SummaryType = "季", MonthORQuarter = i, Value = value });
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
//部门最终利润
decimal 部门最终利润 = (from item in SummaryResultList where item.SummaryName == "部门最终利润实际" && item.SummaryType == "年" select item.Value).Sum();
//利润分配
decimal 利润分配 = (from item in SummaryResultList where item.SummaryName == "利润分配实际" && item.SummaryType == "年" select item.Value).Sum();
//内报税前利润
decimal value = 部门最终利润 - 利润分配;
SummaryResultList.Add(new SummaryResultModel() { SummaryName = "内报税前利润实际", SummaryType = "年", Value = value });
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：十六、内报税前利润率（预算）
//内报税前利润/收入总计（预算）
sb.Append("<tr>");
sb.Append("<td colspan=\"3\" class=\"DarkRedBg\"> 十六、内报税前利润率（预算额） </td>");
foreach (int i in MonthList)
{
//收入总计
decimal 收入总计 = (from item in SummaryResultList where item.SummaryName == "收入总计预算" && item.SummaryType == "月" && item.MonthORQuarter == i select item.Value).Sum();
//内报税前利润
decimal 内报税前利润 = (from item in SummaryResultList where item.SummaryName == "内报税前利润预算" && item.SummaryType == "月" && item.MonthORQuarter == i select item.Value).Sum();
//计算最终利润
decimal value = ConvertData.ConvertToDisplayMoney((收入总计 > 0 ? 内报税前利润 / 收入总计 : 0) * 100);
sb.Append("<td class='value DarkRedBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "%</td>");
}
foreach (int i in QuarterList)
{
//收入总计
decimal 收入总计 = (from item in SummaryResultList where item.SummaryName == "收入总计预算" && item.SummaryType == "季" && item.MonthORQuarter == i select item.Value).Sum();
//内报税前利润
decimal 内报税前利润 = (from item in SummaryResultList where item.SummaryName == "内报税前利润预算" && item.SummaryType == "季" && item.MonthORQuarter == i select item.Value).Sum();
//计算最终利润
decimal value = ConvertData.ConvertToDisplayMoney((收入总计 > 0 ? 内报税前利润 / 收入总计 : 0) * 100);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "%</td>");
}
if (true)
{
//收入总计
decimal 收入总计 = (from item in SummaryResultList where item.SummaryName == "收入总计预算" && item.SummaryType == "年" select item.Value).Sum();
//内报税前利润
decimal 内报税前利润 = (from item in SummaryResultList where item.SummaryName == "内报税前利润预算" && item.SummaryType == "年" select item.Value).Sum();
//计算最终利润
decimal value = ConvertData.ConvertToDisplayMoney((收入总计 > 0 ? 内报税前利润 / 收入总计 : 0) * 100);
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "%</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：十六、内报税前利润率（实际）
//内报税前利润/收入总计（实际）
sb.Append("<tr class=\"YellowBg\">");
sb.Append("<td colspan=\"3\"> 十六、内报税前利润率（实际发生额） </td>");
foreach (int i in MonthList)
{
//收入总计
decimal 收入总计 = (from item in SummaryResultList where item.SummaryName == "收入总计实际" && item.SummaryType == "月" && item.MonthORQuarter == i select item.Value).Sum();
//内报税前利润
decimal 内报税前利润 = (from item in SummaryResultList where item.SummaryName == "内报税前利润实际" && item.SummaryType == "月" && item.MonthORQuarter == i select item.Value).Sum();
//计算最终利润
decimal value = ConvertData.ConvertToDisplayMoney((收入总计 > 0 ? 内报税前利润 / 收入总计 : 0) * 100);
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "%</td>");
}
foreach (int i in QuarterList)
{
//收入总计
decimal 收入总计 = (from item in SummaryResultList where item.SummaryName == "收入总计实际" && item.SummaryType == "季" && item.MonthORQuarter == i select item.Value).Sum();
//内报税前利润
decimal 内报税前利润 = (from item in SummaryResultList where item.SummaryName == "内报税前利润实际" && item.SummaryType == "季" && item.MonthORQuarter == i select item.Value).Sum();
//计算最终利润
decimal value = ConvertData.ConvertToDisplayMoney((收入总计 > 0 ? 内报税前利润 / 收入总计 : 0) * 100);
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "%</td>");
}
if (true)
{
//收入总计
decimal 收入总计 = (from item in SummaryResultList where item.SummaryName == "收入总计实际" && item.SummaryType == "年" select item.Value).Sum();
//内报税前利润
decimal 内报税前利润 = (from item in SummaryResultList where item.SummaryName == "内报税前利润实际" && item.SummaryType == "年" select item.Value).Sum();
//计算最终利润
decimal value = ConvertData.ConvertToDisplayMoney((收入总计 > 0 ? 内报税前利润 / 收入总计 : 0) * 100);
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "%</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：部门效率评估
#region 代码块说明：人均产值（预算额）
sb.Append("<tr>");
sb.Append("<td rowspan=\"4\" class=\"LighterYellowBg\">部门效率评估</td>");
sb.Append("<td colspan=\"2\" class=\"LighterYellowBg\">人均产值（预算额）</td>");
foreach (int i in MonthList)
{
//收入总计
decimal 收入总计 = (from item in SummaryResultList where item.SummaryName == "收入总计预算" && item.SummaryType == "月" && item.MonthORQuarter == i select item.Value).Sum();
//员工人数
decimal 员工人数 = (from item in SummaryResultList where item.SummaryName == "员工人数" && item.SummaryType == "月" && item.MonthORQuarter == i select item.Value).Sum();
//人均产值
decimal value = ConvertData.ConvertToDisplayMoney((员工人数 > 0 ? 收入总计 / 员工人数 : 0));
sb.Append("<td class='value LighterYellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
//收入总计
decimal 收入总计 = (from item in SummaryResultList where item.SummaryName == "收入总计预算" && item.SummaryType == "季" && item.MonthORQuarter == i select item.Value).Sum();
//员工人数
decimal 员工人数 = (from item in SummaryResultList where item.SummaryName == "员工人数" && item.SummaryType == "月" && item.MonthORQuarter == i select item.Value).Sum();
//人均产值
decimal value = ConvertData.ConvertToDisplayMoney((员工人数 > 0 ? 收入总计 / 员工人数 : 0));
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
//收入总计
decimal 收入总计 = (from item in SummaryResultList where item.SummaryName == "收入总计预算" && item.SummaryType == "年" select item.Value).Sum();
//员工人数
decimal 员工人数 = (from item in SummaryResultList where item.SummaryName == "员工人数" && item.SummaryType == "年" select item.Value).Sum();
//人均产值
decimal value = ConvertData.ConvertToDisplayMoney((员工人数 > 0 ? 收入总计 / 员工人数 : 0));
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：人均产值（实际额）
sb.Append("<tr>");
sb.Append("<td colspan=\"2\" class=\"LighterYellowBg\">人均产值（实际发生额）</td>");
foreach (int i in MonthList)
{
//收入总计
decimal 收入总计 = (from item in SummaryResultList where item.SummaryName == "收入总计实际" && item.SummaryType == "月" && item.MonthORQuarter == i select item.Value).Sum();
//员工人数
decimal 员工人数 = (from item in SummaryResultList where item.SummaryName == "员工人数" && item.SummaryType == "月" && item.MonthORQuarter == i select item.Value).Sum();
//人均产值
decimal value = ConvertData.ConvertToDisplayMoney((员工人数 > 0 ? 收入总计 / 员工人数 : 0));
sb.Append("<td class='value LighterYellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
//收入总计
decimal 收入总计 = (from item in SummaryResultList where item.SummaryName == "收入总计实际" && item.SummaryType == "季" && item.MonthORQuarter == i select item.Value).Sum();
//员工人数
decimal 员工人数 = (from item in SummaryResultList where item.SummaryName == "员工人数" && item.SummaryType == "月" && item.MonthORQuarter == i select item.Value).Sum();
//人均产值
decimal value = ConvertData.ConvertToDisplayMoney((员工人数 > 0 ? 收入总计 / 员工人数 : 0));
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
//收入总计
decimal 收入总计 = (from item in SummaryResultList where item.SummaryName == "收入总计实际" && item.SummaryType == "年" select item.Value).Sum();
//员工人数
decimal 员工人数 = (from item in SummaryResultList where item.SummaryName == "员工人数" && item.SummaryType == "年" select item.Value).Sum();
//人均产值
decimal value = ConvertData.ConvertToDisplayMoney((员工人数 > 0 ? 收入总计 / 员工人数 : 0));
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：人均利润（预算额）
sb.Append("<tr>");
sb.Append("<td colspan=\"2\" class=\"LighterYellowBg\">人均利润（预算额）</td>");
foreach (int i in MonthList)
{
//部门最终利润
decimal 部门最终利润 = (from item in SummaryResultList where item.SummaryName == "部门最终利润预算" && item.SummaryType == "月" && item.MonthORQuarter == i select item.Value).Sum();
//员工人数
decimal 员工人数 = (from item in SummaryResultList where item.SummaryName == "员工人数" && item.SummaryType == "月" && item.MonthORQuarter == i select item.Value).Sum();
//人均利润
decimal value = ConvertData.ConvertToDisplayMoney((员工人数 > 0 ? 部门最终利润 / 员工人数 : 0));
sb.Append("<td class='value LighterYellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
//部门最终利润
decimal 部门最终利润 = (from item in SummaryResultList where item.SummaryName == "部门最终利润预算" && item.SummaryType == "季" && item.MonthORQuarter == i select item.Value).Sum();
//员工人数
decimal 员工人数 = (from item in SummaryResultList where item.SummaryName == "员工人数" && item.SummaryType == "月" && item.MonthORQuarter == i select item.Value).Sum();
//人均利润
decimal value = ConvertData.ConvertToDisplayMoney((员工人数 > 0 ? 部门最终利润 / 员工人数 : 0));
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
//部门最终利润
decimal 部门最终利润 = (from item in SummaryResultList where item.SummaryName == "部门最终利润预算" && item.SummaryType == "年" select item.Value).Sum();
//员工人数
decimal 员工人数 = (from item in SummaryResultList where item.SummaryName == "员工人数" && item.SummaryType == "年" select item.Value).Sum();
//人均利润
decimal value = ConvertData.ConvertToDisplayMoney((员工人数 > 0 ? 部门最终利润 / 员工人数 : 0));
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：人均利润（实际额）
sb.Append("<tr>");
sb.Append("<td colspan=\"2\" class=\"LighterYellowBg\">人均利润（实际额）</td>");
foreach (int i in MonthList)
{
//部门最终利润
decimal 部门最终利润 = (from item in SummaryResultList where item.SummaryName == "部门最终利润实际" && item.SummaryType == "月" && item.MonthORQuarter == i select item.Value).Sum();
//员工人数
decimal 员工人数 = (from item in SummaryResultList where item.SummaryName == "员工人数" && item.SummaryType == "月" && item.MonthORQuarter == i select item.Value).Sum();
//人均利润
decimal value = ConvertData.ConvertToDisplayMoney((员工人数 > 0 ? 部门最终利润 / 员工人数 : 0));
sb.Append("<td class='value LighterYellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
//部门最终利润
decimal 部门最终利润 = (from item in SummaryResultList where item.SummaryName == "部门最终利润实际" && item.SummaryType == "季" && item.MonthORQuarter == i select item.Value).Sum();
//员工人数
decimal 员工人数 = (from item in SummaryResultList where item.SummaryName == "员工人数" && item.SummaryType == "月" && item.MonthORQuarter == i select item.Value).Sum();
//人均利润
decimal value = ConvertData.ConvertToDisplayMoney((员工人数 > 0 ? 部门最终利润 / 员工人数 : 0));
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
//部门最终利润
decimal 部门最终利润 = (from item in SummaryResultList where item.SummaryName == "部门最终利润实际" && item.SummaryType == "年" select item.Value).Sum();
//员工人数
decimal 员工人数 = (from item in SummaryResultList where item.SummaryName == "员工人数" && item.SummaryType == "年" select item.Value).Sum();
//人均利润
decimal value = ConvertData.ConvertToDisplayMoney((员工人数 > 0 ? 部门最终利润 / 员工人数 : 0));
sb.Append("<td class='value YellowBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
#endregion
#endregion
#region 代码块说明：每月资金需求（预算）
//成本总计+税金成本+营业费用总计+管理费、财务费总计+固定资产&无形资产采购小计-固定资产折旧-无形资产摊销
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["固定资产折旧"] = "select value=sum(ISNULL(预算金额,0)) from 项目费用预算月度表 where 年='<$Year$>' and 月 in(<$Month$>) and 二级科目='固定资产折旧'";
BaseSQLDictionary["无形资产摊销"] = "select value=sum(ISNULL(预算金额,0)) from 项目费用预算月度表 where 年='<$Year$>' and 月 in(<$Month$>) and 二级科目='无形资产摊销'";
sb.Append("<tr>");
sb.Append("<td colspan=\"3\"> 每月资金需求（预算额） </td>");
foreach (int i in MonthList)
{
//成本总计
decimal 成本总计 = (from item in SummaryResultList where item.SummaryName == "成本总计预算" && item.SummaryType == "月" && item.MonthORQuarter == i select item.Value).Sum();
//税金成本
decimal 税金成本 = (from item in SummaryResultList where item.SummaryName == "税金成本预算" && item.SummaryType == "月" && item.MonthORQuarter == i select item.Value).Sum();
//营业费用总计
decimal 营业费用 = (from item in SummaryResultList where item.SummaryName == "营业费用预算" && item.SummaryType == "月" && item.MonthORQuarter == i select item.Value).Sum();
//管理费、财务费总计
decimal 管理费和财务费 = (from item in SummaryResultList where item.SummaryName == "管理费用和财务费用预算" && item.SummaryType == "月" && item.MonthORQuarter == i select item.Value).Sum();
//固定资产
decimal 固定资产 = (from item in SummaryResultList where item.SummaryName == "固定资产预算" && item.SummaryType == "月" && item.MonthORQuarter == i select item.Value).Sum();
//固定资产折旧
decimal 固定资产折旧 = SummaryBudget(context, BaseSQLDictionary["固定资产折旧"], Year, new List<int>() { i });
//无形资产摊销
decimal 无形资产摊销 = SummaryBudget(context, BaseSQLDictionary["无形资产摊销"], Year, new List<int>() { i });
//计算最终利润
decimal value = 成本总计 + 税金成本 + 营业费用 + 管理费和财务费 + 固定资产 - 固定资产折旧 - 无形资产摊销;
sb.Append("<td class='value DarkRedBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
//成本总计
decimal 成本总计 = (from item in SummaryResultList where item.SummaryName == "成本总计预算" && item.SummaryType == "季" && item.MonthORQuarter == i select item.Value).Sum();
//税金成本
decimal 税金成本 = (from item in SummaryResultList where item.SummaryName == "税金成本预算" && item.SummaryType == "季" && item.MonthORQuarter == i select item.Value).Sum();
//营业费用总计
decimal 营业费用 = (from item in SummaryResultList where item.SummaryName == "营业费用预算" && item.SummaryType == "季" && item.MonthORQuarter == i select item.Value).Sum();
//管理费、财务费总计
decimal 管理费和财务费 = (from item in SummaryResultList where item.SummaryName == "管理费用和财务费用预算" && item.SummaryType == "季" && item.MonthORQuarter == i select item.Value).Sum();
//固定资产
decimal 固定资产 = (from item in SummaryResultList where item.SummaryName == "固定资产预算" && item.SummaryType == "季" && item.MonthORQuarter == i select item.Value).Sum();
//固定资产折旧
decimal 固定资产折旧 = SummaryBudget(context, BaseSQLDictionary["固定资产折旧"], Year, QuarterMonthListDictionary[i]);
//无形资产摊销
decimal 无形资产摊销 = SummaryBudget(context, BaseSQLDictionary["无形资产摊销"], Year, QuarterMonthListDictionary[i]);
//计算最终利润
decimal value = 成本总计 + 税金成本 + 营业费用 + 管理费和财务费 + 固定资产 - 固定资产折旧 - 无形资产摊销;
sb.Append("<td class='value DarkRedBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
//成本总计
decimal 成本总计 = (from item in SummaryResultList where item.SummaryName == "成本总计预算" && item.SummaryType == "年" select item.Value).Sum();
//税金成本
decimal 税金成本 = (from item in SummaryResultList where item.SummaryName == "税金成本预算" && item.SummaryType == "年" select item.Value).Sum();
//营业费用总计
decimal 营业费用 = (from item in SummaryResultList where item.SummaryName == "营业费用预算" && item.SummaryType == "年" select item.Value).Sum();
//管理费、财务费总计
decimal 管理费和财务费 = (from item in SummaryResultList where item.SummaryName == "管理费用和财务费用预算" && item.SummaryType == "年" select item.Value).Sum();
//固定资产
decimal 固定资产 = (from item in SummaryResultList where item.SummaryName == "固定资产预算" && item.SummaryType == "年" select item.Value).Sum();
//固定资产折旧
decimal 固定资产折旧 = SummaryBudget(context, BaseSQLDictionary["固定资产折旧"], Year, MonthList);
//无形资产摊销
decimal 无形资产摊销 = SummaryBudget(context, BaseSQLDictionary["无形资产摊销"], Year, MonthList);
//计算最终利润
decimal value = 成本总计 + 税金成本 + 营业费用 + 管理费和财务费 + 固定资产 - 固定资产折旧 - 无形资产摊销;
sb.Append("<td class='value DarkRedBg'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：每月资金需求（实际）
//成本总计+税金成本+营业费用总计+管理费、财务费总计+固定资产&无形资产采购小计-固定资产折旧-无形资产摊销
BaseSQLDictionary = new Dictionary<string, string>();
BaseSQLDictionary["固定资产折旧"] = "select value=sum(ISNULL(已发生,0)) from 项目费用实际月度表 where 年='<$Year$>' and 月 in(<$Month$>) and 二级科目='固定资产折旧'";
BaseSQLDictionary["无形资产摊销"] = "select value=sum(ISNULL(已发生,0)) from 项目费用实际月度表 where 年='<$Year$>' and 月 in(<$Month$>) and 二级科目='无形资产摊销'";
sb.Append("<tr class=\"YellowBg\">");
sb.Append("<td colspan=\"3\"> 每月资金需求（实际额） </td>");
foreach (int i in MonthList)
{
//成本总计
decimal 成本总计 = (from item in SummaryResultList where item.SummaryName == "成本总计实际" && item.SummaryType == "月" && item.MonthORQuarter == i select item.Value).Sum();
//税金成本
decimal 税金成本 = (from item in SummaryResultList where item.SummaryName == "税金成本实际" && item.SummaryType == "月" && item.MonthORQuarter == i select item.Value).Sum();
//营业费用总计
decimal 营业费用 = (from item in SummaryResultList where item.SummaryName == "营业费用实际" && item.SummaryType == "月" && item.MonthORQuarter == i select item.Value).Sum();
//管理费、财务费总计
decimal 管理费和财务费 = (from item in SummaryResultList where item.SummaryName == "管理费用和财务费用实际" && item.SummaryType == "月" && item.MonthORQuarter == i select item.Value).Sum();
//固定资产
decimal 固定资产 = (from item in SummaryResultList where item.SummaryName == "固定资产实际" && item.SummaryType == "月" && item.MonthORQuarter == i select item.Value).Sum();
//固定资产折旧
decimal 固定资产折旧 = SummaryBudget(context, BaseSQLDictionary["固定资产折旧"], Year, new List<int>() { i });
//无形资产摊销
decimal 无形资产摊销 = SummaryBudget(context, BaseSQLDictionary["无形资产摊销"], Year, new List<int>() { i });
//计算最终利润
decimal value = 成本总计 + 税金成本 + 营业费用 + 管理费和财务费 + 固定资产 - 固定资产折旧 - 无形资产摊销;
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
foreach (int i in QuarterList)
{
//成本总计
decimal 成本总计 = (from item in SummaryResultList where item.SummaryName == "成本总计实际" && item.SummaryType == "季" && item.MonthORQuarter == i select item.Value).Sum();
//税金成本
decimal 税金成本 = (from item in SummaryResultList where item.SummaryName == "税金成本实际" && item.SummaryType == "季" && item.MonthORQuarter == i select item.Value).Sum();
//营业费用总计
decimal 营业费用 = (from item in SummaryResultList where item.SummaryName == "营业费用实际" && item.SummaryType == "季" && item.MonthORQuarter == i select item.Value).Sum();
//管理费、财务费总计
decimal 管理费和财务费 = (from item in SummaryResultList where item.SummaryName == "管理费用和财务费用实际" && item.SummaryType == "季" && item.MonthORQuarter == i select item.Value).Sum();
//固定资产
decimal 固定资产 = (from item in SummaryResultList where item.SummaryName == "固定资产实际" && item.SummaryType == "季" && item.MonthORQuarter == i select item.Value).Sum();
//固定资产折旧
decimal 固定资产折旧 = SummaryBudget(context, BaseSQLDictionary["固定资产折旧"], Year, QuarterMonthListDictionary[i]);
//无形资产摊销
decimal 无形资产摊销 = SummaryBudget(context, BaseSQLDictionary["无形资产摊销"], Year, QuarterMonthListDictionary[i]);
//计算最终利润
decimal value = 成本总计 + 税金成本 + 营业费用 + 管理费和财务费 + 固定资产 - 固定资产折旧 - 无形资产摊销;
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
if (true)
{
//成本总计
decimal 成本总计 = (from item in SummaryResultList where item.SummaryName == "成本总计实际" && item.SummaryType == "年" select item.Value).Sum();
//税金成本
decimal 税金成本 = (from item in SummaryResultList where item.SummaryName == "税金成本实际" && item.SummaryType == "年" select item.Value).Sum();
//营业费用总计
decimal 营业费用 = (from item in SummaryResultList where item.SummaryName == "营业费用实际" && item.SummaryType == "年" select item.Value).Sum();
//管理费、财务费总计
decimal 管理费和财务费 = (from item in SummaryResultList where item.SummaryName == "管理费用和财务费用实际" && item.SummaryType == "年" select item.Value).Sum();
//固定资产
decimal 固定资产 = (from item in SummaryResultList where item.SummaryName == "固定资产实际" && item.SummaryType == "年" select item.Value).Sum();
//固定资产折旧
decimal 固定资产折旧 = SummaryBudget(context, BaseSQLDictionary["固定资产折旧"], Year, MonthList);
//无形资产摊销
decimal 无形资产摊销 = SummaryBudget(context, BaseSQLDictionary["无形资产摊销"], Year, MonthList);
//计算最终利润
decimal value = 成本总计 + 税金成本 + 营业费用 + 管理费和财务费 + 固定资产 - 固定资产折旧 - 无形资产摊销;
sb.Append("<td class='value'>" + PublicFunctions.ConvertData.FormatQFZNumber(value) + "</td>");
}
sb.Append("</tr>");
#endregion
#region 代码块说明：生在表尾
sb.Append("</tbody>");
sb.Append("</table>");
#endregion
AjaxResultObject.Result = sb.ToString();
AjaxResultObject.IsSuccess = true;
}
AjaxResultObject.IsSuccess = true;
return AjaxResultObject;
}
#endregion
#region 函数说明：Summary统计
/// <summary>
/// Summary
/// </summary>
/// <param name="context"></param>
/// <param name="sql"></param>
/// <param name="MinDate"></param>
/// <param name="MaxDate"></param>
/// <returns></returns>
public decimal Summary(PublicFunctions.DBHelper.RawDBHelper.SqlDataFactory context, string sql, string MinDate, string MaxDate)
{
sql = sql.Replace("<$MinDate$>", MinDate).Replace("<$MaxDate$>", MaxDate);
return context.GetCellFieldValue<decimal>("value", sql);
}
#endregion
#region 函数说明：SummaryBudget预算成本统计
/// <summary>
/// SummaryBudget预算成本统计
/// </summary>
/// <param name="context"></param>
/// <param name="sql"></param>
/// <param name="Year"></param>
/// <param name="MonthList"></param>
/// <returns></returns>
public decimal SummaryBudget(PublicFunctions.DBHelper.RawDBHelper.SqlDataFactory context, string sql, int Year, List<int> MonthList)
{
sql = sql.Replace("<$Year$>", Year.ToString()).Replace("<$Month$>", string.Join(",", MonthList));
return context.GetCellFieldValue<decimal>("value", sql);
}
#endregion
#region 函数说明：GetBudgetDictionary获得预算科目字典
///<summary>
/// GetBudgetDictionary获得预算科目字典
/// </summary>
/// <param name="context"></param>
/// <param name="项目类型"></param>
/// <param name="一级科目"></param>
/// <returns></returns>
public Dictionary<string, string> GetBudgetDictionary(PublicFunctions.DBHelper.RawDBHelper.SqlDataFactory context, string 项目类型, string 一级科目)
{
return context.GetDictionary("二级科目", "科目编码", "select 二级科目,科目编码 from 项目费用科目模板 where 项目类型='" + 项目类型 + "' and 一级科目='" + 一级科目 + "'");
}
#endregion
#region 函数说明：CountTotalEmployee统计在职人数
/// <summary>
/// CountTotalEmployee统计在职人数
/// </summary>
/// <param name="context"></param>
/// <param name="MinDate"></param>
/// <param name="MaxDate"></param>
/// <returns></returns>
public int CountTotalEmployee(PublicFunctions.DBHelper.RawDBHelper.SqlDataFactory context, string MinDate, string MaxDate)
{
string sql = "select count(1) from EmployeeTable where (InCompanyTime IS NOT NULL and InCompanyTime<='" + MinDate + "') and (QuitTime IS NULL OR QuitTime='1900-01-01' OR QuitTime>='" + MaxDate + "')";
return context.CountSQL(sql);
}
#endregion
#region 类说明：SummaryResultModel统计结果模型类
/// <summary>
/// SummaryResultModel
/// </summary>
public class SummaryResultModel
{
/// <summary>
/// 统计结果名称
/// </summary>
public string SummaryName { get; set; }
/// <summary>
/// 统计类型（月、季、年）
/// </summary>
public string SummaryType { get; set; }
/// <summary>
/// 具体的月份或季度
/// </summary>
public int MonthORQuarter { get; set; }
/// <summary>
/// 统计值
/// </summary>
public decimal Value { get; set; }
}
#endregion
}
}