public Dictionary <string, object> GetPieChart(string ProjectID)
{
var sql = @"select Sum(FactReceiptValue) as SumRecepitValue, Sum(FactInvoiceValue) as SumInvoiceValue,ProjectInfo as ProjectInfoID,
ProjectInfoName from S_C_ManageContract_ReceiptObj
where ProjectInfo='{0}'
group by ProjectInfo,ProjectInfoName";
var dataSource = new DataTable();
dataSource.Columns.Add("nameField");
dataSource.Columns.Add("valueField", typeof(decimal));
var receiptValue = 0m;
var invoiceValue = 0m;
var remainInvoiceValue = 0m;
var remaintContractValue = 0m;
var db = SQLHelper.CreateSqlHelper(ConnEnum.Market);
var dt = db.ExecuteDataTable(String.Format(sql, ProjectID));
if (dt.Rows.Count > 0)
{
receiptValue = dt.Rows[0]["SumRecepitValue"] == null || dt.Rows[0]["SumRecepitValue"] == DBNull.Value ? 0m : Convert.ToDecimal(dt.Rows[0]["SumRecepitValue"]);
invoiceValue = dt.Rows[0]["SumInvoiceValue"] == null || dt.Rows[0]["SumInvoiceValue"] == DBNull.Value ? 0m : Convert.ToDecimal(dt.Rows[0]["SumInvoiceValue"]);
remainInvoiceValue = (invoiceValue - receiptValue) < 0 ? 0m : invoiceValue - receiptValue;
}
sql = @"select Sum(ProjectValue) as ContractValue,ProjectID from S_C_ManageContract_ProjectRelation
where ProjectID='{0}' group by ProjectID";
dt = db.ExecuteDataTable(String.Format(sql, ProjectID));
if (dt.Rows.Count > 0)
{
var contractValue = dt.Rows[0]["ContractValue"] == null || dt.Rows[0]["ContractValue"] == DBNull.Value ? 0m : Convert.ToDecimal(dt.Rows[0]["ContractValue"]);
remaintContractValue = (contractValue - receiptValue - remainInvoiceValue) < 0 ? receiptValue : contractValue - receiptValue - remainInvoiceValue;
}
var contractRemainRow = dataSource.NewRow();
contractRemainRow["nameField"] = "剩余合同额";
contractRemainRow["valueField"] = remaintContractValue;
dataSource.Rows.Add(contractRemainRow);
var invoiceRemainRow = dataSource.NewRow();
invoiceRemainRow["nameField"] = "应收款";
invoiceRemainRow["valueField"] = remainInvoiceValue;
dataSource.Rows.Add(invoiceRemainRow);
var receiptRow = dataSource.NewRow();
receiptRow["nameField"] = "已收款";
receiptRow["valueField"] = receiptValue;
dataSource.Rows.Add(receiptRow);
var chart = HighChartHelper.CreatePieChart("项目总体情况分析", "金额", dataSource);
var result = chart.Render();
var credits = new Dictionary <string, object>();
credits.SetValue("enabled", false);
result.SetValue("credits", credits);
return(result);
}
/// <summary>
/// 月度费用分布
/// </summary>
/// <returns></returns>
private Dictionary <string, object> GetContractReimbursementChartData(string costUnitArr)
{
var sourceDt = this.SQLDB.ExecuteDataTable(string.Format(@"select sum(ApplyValue) ApplyValue,BelongMonth from
(select S_EP_ReimbursementApply.*,detail.ApplyValue,month(ApplyDate) BelongMonth from S_EP_ReimbursementApply
left join
(select sum(isnull(ApplyValue,0)) ApplyValue,S_EP_ReimbursementApplyID
from S_EP_ReimbursementApply_Details group by S_EP_ReimbursementApplyID) detail
on detail.S_EP_ReimbursementApplyID = S_EP_ReimbursementApply.ID
where S_EP_ReimbursementApply.FlowPhase = 'End' and year(getdate()) = year(ApplyDate)
) tmp where ProjectInfo in ('{0}') group by BelongMonth", costUnitArr));
DataTable newDt = new DataTable();
newDt.Columns.Add(new DataColumn("BelongMonth", typeof(string)));
newDt.Columns.Add(new DataColumn("ApplyValue", typeof(decimal)));
for (int i = 1; i <= 12; i++)
{
var resRow = newDt.NewRow();
resRow["BelongMonth"] = i.ToString() + "月";
resRow["ApplyValue"] = 0;
var row = sourceDt.Select("BelongMonth=" + i).FirstOrDefault();
if (row != null && row["ApplyValue"] != null && row["ApplyValue"] != DBNull.Value)
{
resRow["ApplyValue"] = (decimal)row["ApplyValue"];
}
newDt.Rows.Add(resRow);
}
var columnChart = HighChartHelper.CreateColumnChart("", newDt, "BelongMonth", new string[] { "报销费用" }, new string[] { "ApplyValue" });
return(columnChart.Render());
}
public JsonResult GetAllList()
{
string projectInfoID = this.Request["ProjectInfoID"];
var projectInfo = this.GetEntityByID <S_I_ProjectInfo>(projectInfoID);
if (projectInfo == null)
{
throw new Formula.Exceptions.BusinessException("没有找到指定的项目对象");
}
var marketDBContext = Formula.FormulaHelper.GetEntities <Market.Logic.Domain.MarketEntities>();
var project = marketDBContext.S_I_Project.FirstOrDefault(d => d.ID == projectInfo.MarketProjectInfoID);
if (project == null)
{
throw new Formula.Exceptions.BusinessException("未能找到指定的项目对象");
}
string sql = @"select SubjectName,SubjectCode,Sum(CostValue) as CostValue,ProjectID
from dbo.S_FC_CostInfo where ProjectID='{0}'
group by SubjectName,SubjectCode,ProjectID";
var db = SQLHelper.CreateSqlHelper(ConnEnum.Market);
var mainDt = db.ExecuteDataTable(String.Format(sql, project.ID));
var result = new Dictionary <string, object>();
result["data"] = mainDt;
var pieChart = HighChartHelper.CreatePieChart("项目成本分析", "成本金额（元）", mainDt, "SubjectName", "CostValue");
var chartData = pieChart.Render();
var credits = new Dictionary <string, object>();
credits.SetValue("enabled", false);
chartData.SetValue("credits", credits);
result["pieChartData"] = chartData;
return(Json(result));
}
public Dictionary <string, object> GetPieChartData(string belongYear, string projectID)
{
string sql = @"select * from (select SubjectName,SubjectCode,Sum(CostValue) as CostValue,
BelongYear,ProjectID from dbo.S_FC_CostInfo
group by SubjectName,SubjectCode,BelongYear,ProjectID) tableInfo where 1=1";
if (!String.IsNullOrEmpty(belongYear))
{
sql += " and BelongYear='" + belongYear + "'";
}
if (!String.IsNullOrEmpty(projectID))
{
sql += " and ProjectID='" + projectID + "'";
}
var db = SQLHelper.CreateSqlHelper(ConnEnum.Market);
var dt = db.ExecuteDataTable(sql);
var pieChart = HighChartHelper.CreatePieChart(belongYear + "成本科目分布", "成本金额（元）", dt, "SubjectName", "CostValue");
var chartOption = pieChart.Render();
var credits = new Dictionary <string, object>();
credits.SetValue("enabled", false);
chartOption.SetValue("credits", credits);
return(chartOption);
}
public Dictionary <string, object> GetYearChartData(string belongYear)
{
var depts = EnumBaseHelper.GetEnumDef("System.ManDept").EnumItem;
string sql = @"select Sum(ToA1) as ToA1,
BelongYear,BelongMonth from S_EP_PublishInfo
left join S_I_ProjectInfo on S_EP_PublishInfo.ProjectInfoID=S_I_ProjectInfo.ID
where ChargeDeptID in ('" + string.Join("','", depts.Select(a => a.Code).ToArray()) + @"') and PublishTime is not null and BelongYear='{0}' group by BelongYear,BelongMonth";
var db = SQLHelper.CreateSqlHelper(ConnEnum.Project);
var dt = db.ExecuteDataTable(String.Format(sql, belongYear));
var dataSource = new DataTable();
dataSource.Columns.Add("BelongYear", typeof(string));
dataSource.Columns.Add("BelongMonth", typeof(string));
dataSource.Columns.Add("ToA1", typeof(decimal));
dataSource.Columns.Add("SumToA1", typeof(decimal));
var sumValue = 0m;
for (int i = 1; i <= 12; i++)
{
var row = dataSource.NewRow();
row["BelongMonth"] = i + "月";
row["BelongYear"] = belongYear;
var value = 0m;
var monthRows = dt.Select("BelongMonth = '" + i.ToString() + "'");
if (monthRows.Length > 0)
{
value = monthRows[0]["ToA1"] == null || monthRows[0]["ToA1"] == DBNull.Value ? 0m : Convert.ToDecimal(monthRows[0]["ToA1"]);
}
sumValue += value;
row["SumToA1"] = sumValue;
row["ToA1"] = value;
dataSource.Rows.Add(row);
}
#region 生成图表
var yAxies = new List <yAxis>();
var y1 = new yAxis {
MiniValue = 0, TitleInfo = new Dictionary <string, object>(), Lable = new Dictionary <string, object>()
};
y1.TitleInfo.SetValue("text", "折合A1数");
y1.Lable.SetValue("format", "{value}张");
yAxies.Add(y1);
var serDefines = new List <Series>();
var costSer = new Series {
Name = "当月出图", Field = "ToA1", Type = "column", yAxis = 0, Tooltip = new Dictionary <string, object>()
};
var sumCostSer = new Series {
Name = "累计出图", Field = "SumToA1", Type = "spline", yAxis = 0, Tooltip = new Dictionary <string, object>()
};
serDefines.Add(costSer);
serDefines.Add(sumCostSer);
var chart = HighChartHelper.CreateColumnXYChart(belongYear + "年度出图分析", "", dataSource, "BelongMonth", yAxies, serDefines, null);
return(chart);
#endregion
}
private Dictionary <string, object> GetPieChartOption()
{
var result = this.createPieSql();
var dt = this.SqlHelper.ExecuteDataTable(result[0]);
var pieChart = HighChartHelper.CreatePieChart("", result[1], dt);
return(pieChart.Render());
}
private Dictionary <string, object> GetChartOption(string customerID, int startYear, int endYear)
{
var dt = this.GetChartData(customerID, startYear, endYear);
string seriesNames = "计划收款,到款金额,开票金额";
string seriesFields = "PlanReceiptValue,ReceiptValue,InvoiceValue";
var columChart = HighChartHelper.CreateColumnChart("", dt, "Title", seriesNames.Split(','), seriesFields.Split(','));
return(columChart.Render());
}
public Dictionary <string, object> GetYearChartData(string belongYear, string projectID)
{
string sql = @"select Sum(CostValue) as CostValue,
BelongYear,BelongMonth,ProjectID from S_FC_CostInfo where ProjectID='{0}'
and BelongYear='{1}' group by BelongYear,BelongMonth,ProjectID";
var db = SQLHelper.CreateSqlHelper(ConnEnum.Market);
var dt = db.ExecuteDataTable(String.Format(sql, projectID, belongYear));
var dataSource = new DataTable();
dataSource.Columns.Add("BelongYear", typeof(string));
dataSource.Columns.Add("BelongMonth", typeof(string));
dataSource.Columns.Add("CostValue", typeof(decimal));
dataSource.Columns.Add("SumCostValue", typeof(decimal));
var sumValue = 0m;
for (int i = 1; i <= 12; i++)
{
var row = dataSource.NewRow();
row["BelongMonth"] = i + "月";
row["BelongYear"] = belongYear;
var value = 0m;
var monthRows = dt.Select("BelongMonth = '" + i.ToString() + "'");
if (monthRows.Length > 0)
{
value = monthRows[0]["CostValue"] == null || monthRows[0]["CostValue"] == DBNull.Value ? 0m : Convert.ToDecimal(monthRows[0]["CostValue"]);
}
sumValue += value;
row["SumCostValue"] = sumValue;
row["CostValue"] = value;
dataSource.Rows.Add(row);
}
#region 生成图表
var yAxies = new List <yAxis>();
var y1 = new yAxis {
MiniValue = 0, TitleInfo = new Dictionary <string, object>(), Lable = new Dictionary <string, object>()
};
y1.TitleInfo.SetValue("text", "成本金额");
y1.Lable.SetValue("format", "{value}元");
yAxies.Add(y1);
var serDefines = new List <Series>();
var costSer = new Series {
Name = "实际成本", Field = "CostValue", Type = "column", yAxis = 0, Tooltip = new Dictionary <string, object>()
};
var sumCostSer = new Series {
Name = "累计成本", Field = "SumCostValue", Type = "spline", yAxis = 0, Tooltip = new Dictionary <string, object>()
};
serDefines.Add(costSer);
serDefines.Add(sumCostSer);
var chart = HighChartHelper.CreateColumnXYChart(belongYear + "年度成本分析", "", dataSource, "BelongMonth", yAxies, serDefines, null);
return(chart);
#endregion
}
public override JsonResult GetList(QueryBuilder qb)
{
string sql = @"select * from (select ID as ID,Name as DeptName,SortIndex,{1},0 as Summary,0 as Avg
from S_A_Org where Type='{0}' ) TableInfo ";
string fields = String.Empty;
string seriesNames = String.Empty;
string seriesFields = String.Empty;
qb.PageSize = 0;
var orgService = FormulaHelper.GetService <IOrgService>();
var mistakeLevelEnum = EnumBaseHelper.GetEnumDef("Project.MistakeLevel").EnumItem.ToList();
foreach (var item in mistakeLevelEnum)
{
fields += " 0 as " + item.Code + ",";
seriesFields += item.Code + ",";
seriesNames += item.Name + ",";
}
var db = SQLHelper.CreateSqlHelper(ConnEnum.Base);
var dt = db.ExecuteDataTable(String.Format(sql, OrgType.ManufactureDept.ToString(), fields.TrimEnd(',')));
var mistakeList = this.entities.Set <S_AE_Mistake>().Where(qb).ToList();
foreach (DataRow deptRow in dt.Rows)
{
double mistakeSum = 0;
var deptID = deptRow["ID"].ToString();
foreach (var item in mistakeLevelEnum)
{
var count = mistakeList.Count(d => d.DeptID == deptID && d.MistakeLevel == item.Code);
mistakeSum += count;
deptRow[item.Code] = count;
}
deptRow["Summary"] = mistakeSum;
double userCount = orgService.GetOrgUsers(deptRow["ID"].ToString()).Count;
double avg = 0;
if (userCount > 0)
{
avg = Math.Round(Convert.ToDouble(mistakeSum / userCount), 2);
}
deptRow["Avg"] = avg;
}
var result = new List <Dictionary <string, object> >();
var data = new Dictionary <string, object>();
data.SetValue("data", dt);
var pieChart = HighChartHelper.CreatePieChart("部门质量问题分析", "错误", dt, "DeptName", "Summary");
data.SetValue("PieChart", pieChart.Render());
var columnChart = HighChartHelper.CreateColumnChart("部门质量问题分类分析", dt, "DeptName", seriesNames.TrimEnd(',').Split(',')
, seriesFields.TrimEnd(',').Split(','));
data.SetValue("ColumnChart", columnChart.Render());
return(Json(data));
}
public Dictionary <string, object> GetColumnChart(S_I_ProjectInfo projectInfo)
{
var majorList = projectInfo.GetMajors();
var picFileType = EnumBaseHelper.GetEnumDef("Project.PublicationType").EnumItem.ToList();
var dataSource = new DataTable();
dataSource.Columns.Add("MajorName");
var seriesNames = "出图费用(元)";
var seriesFields = "CostValue";
//foreach (var item in picFileType)
//{
// dataSource.Columns.Add(item.Code, typeof(decimal));
// seriesNames += item.Name + "折A1,";
// seriesFields += item.Code + ",";
//}
dataSource.Columns.Add("CostValue", typeof(decimal));
string sql = @"select SUM(CostValue) CostValue,Extend5 MajorValue from S_FC_CostInfo
where ProjectID = '" + projectInfo.MarketProjectInfoID + "' and SubjectCode = 'PublishFee' group by Extend5";
var marketSqlHepler = SQLHelper.CreateSqlHelper(ConnEnum.Market);
var dt = marketSqlHepler.ExecuteDataTable(sql);
foreach (var major in majorList)
{
var row = dataSource.NewRow();
row["MajorName"] = major.GetValue("Name");
//foreach (var item in picFileType)
//{
// var value = 0m;
// var list = dt.Select("MajorValue='" + major.GetValue("Value") + "' and PublishType='" + item.Code + "'");
// if (list.Length > 0)
// {
// value = list[0]["ToA1"] == null || list[0]["ToA1"] == DBNull.Value ? 0m : Convert.ToDecimal(list[0]["ToA1"]);
// }
// row[item.Code] = value;
//}
var value = 0m;
var list = dt.Select("MajorValue='" + major.GetValue("Value") + "'");
if (list.Length > 0)
{
value = list[0]["CostValue"] == null || list[0]["CostValue"] == DBNull.Value ? 0m : Convert.ToDecimal(list[0]["CostValue"]);
}
row["CostValue"] = value;
dataSource.Rows.Add(row);
}
var chart = HighChartHelper.CreateColumnChart("各专业出图费用统计", dataSource, "MajorName", seriesNames.TrimEnd(',').Split(','), seriesFields.TrimEnd(',').Split(','));
var result = chart.Render();
var credits = new Dictionary <string, object>();
credits.SetValue("enabled", false);
result.SetValue("credits", credits);
return(result);
}
private Dictionary <string, object> GetColumnChartOption()
{
var sql = @"select ID,Name from dbo.S_A_Org where Type='ManufactureDept' order by SortIndex";
var baseSqlDB = SQLHelper.CreateSqlHelper(ConnEnum.Base);
var mainDt = baseSqlDB.ExecuteDataTable(sql);
mainDt.Columns.Add("CurrentYearReceiptValue", typeof(decimal)); //当年实际收款金额
mainDt.Columns.Add("CurrentYearContractValue", typeof(decimal)); //当年已签合同金额
mainDt.Columns.Add("ContractValue", typeof(decimal)); //待签合同金额
mainDt.Columns.Add("RemainContractValue", typeof(decimal)); //合同余额
mainDt.Columns.Add("ReceivableValue", typeof(decimal)); //应收款
//当年实际到款
sql = @"select Sum(Amount) as ReceiptValue,ReceiptMasterUnitID from S_C_Receipt
where BelongYear='" + DateTime.Now.Year + "' group by ReceiptMasterUnitID";
var currentReceiptDt = this.SqlHelper.ExecuteDataTable(sql);
//当年已签合同
sql = @"select Sum(ContractRMBAmount) as ContractValue,ProductionUnitID from dbo.S_C_ManageContract
where BelongYear='" + DateTime.Now.Year + "' and IsSigned = '" + ContractIsSigned.Signed + "' group by ProductionUnitID";
var currentContractDt = this.SqlHelper.ExecuteDataTable(sql);
//当前合同余额
sql = @"select Sum(isnull(ContractRMBAmount,0) - isnull(SummaryReceiptValue,0)- isnull(SummaryBadDebtValue,0)) as RemainContractValue,ProductionUnitID from S_C_ManageContract
where BelongYear='" + DateTime.Now.Year + "' and IsSigned = '" + ContractIsSigned.Signed + "' group by ProductionUnitID";
var currentRemainContractDt = this.SqlHelper.ExecuteDataTable(sql);
//当前待签合同金额
sql = @"select Sum(ContractRMBAmount) as ContractValue,ProductionUnitID from dbo.S_C_ManageContract
where (IsSigned != '" + ContractIsSigned.Signed + "' or IsSigned is null) group by ProductionUnitID";
var contractDt = this.SqlHelper.ExecuteDataTable(sql);
//当前应收款
sql = @"select Sum(isnull(SummaryInvoiceValue,0) - isnull(SummaryReceiptValue,0)) as ReceivableValue,ProductionUnitID from S_C_ManageContract
where BelongYear='" + DateTime.Now.Year + "' and IsSigned = '" + ContractIsSigned.Signed + "' group by ProductionUnitID";
var receivableDt = this.SqlHelper.ExecuteDataTable(sql);
foreach (DataRow item in mainDt.Rows)
{
item["CurrentYearReceiptValue"] = GetDataSourceValue(currentReceiptDt, "ReceiptValue", "ReceiptMasterUnitID", item["ID"].ToString());
item["CurrentYearContractValue"] = GetDataSourceValue(currentContractDt, "ContractValue", "ProductionUnitID", item["ID"].ToString());
item["RemainContractValue"] = GetDataSourceValue(currentRemainContractDt, "RemainContractValue", "ProductionUnitID", item["ID"].ToString());
item["ContractValue"] = GetDataSourceValue(contractDt, "ContractValue", "ProductionUnitID", item["ID"].ToString());
item["ReceivableValue"] = GetDataSourceValue(receivableDt, "ReceivableValue", "ProductionUnitID", item["ID"].ToString());
}
string series = "当年实际收款金额,当年已签合同金额,待签合同金额,当前合同余额,当前应收款";
string serieFields = "CurrentYearReceiptValue,CurrentYearContractValue,ContractValue,RemainContractValue,ReceivableValue";
var columChart = HighChartHelper.CreateColumnChart("", mainDt, "Name", series.Split(','), serieFields.Split(','));
columChart.Is3D = false;
columChart.TitleInfo.Text = "";
return(columChart.Render());
}
private Dictionary <string, object> GetColumnChartOption(string projectInfoID, string series, string serieFields, string majorValue = "")
{
DataTable resDT = new DataTable();
//var startDate = DateTime.Now.AddMonths(-5);
for (var i = 0; i < 6; i++)
{
//var curYear = startDate.Year;
//var curMonth = startDate.Month;
var sql = @"select ProjectInfoID,(case when DateDiff(ww,CreateDate,getDate())=0 then '本周'
when DateDiff(ww,CreateDate,getDate())=1 then '上周'
else '前'+cast(DateDiff(ww,CreateDate,getDate())+1 as nvarchar(20))+'周' end) as BelongWeek,
count(0) as ProductNum,sum(isnull(PrintCount,0)) as PrintCount
from S_E_Product
where DateDiff(ww,CreateDate,getDate())={1}
and ProjectInfoID='{0}' {2}
Group by DateDiff(ww,CreateDate,getDate()),ProjectInfoID";
var majorValueSQL = "";
if (!string.IsNullOrEmpty(majorValue))
{
majorValueSQL = " and MajorValue='" + majorValue + "'";
}
sql = string.Format(sql, projectInfoID, i, majorValueSQL);
var dt = this.SqlHelper.ExecuteDataTable(sql);
if (dt.Rows.Count == 0)
{
var curWeek = "";
switch (i)
{
case 0:
curWeek = "本周";
break;
case 1:
curWeek = "上周";
break;
default:
curWeek = "前" + (i + 1).ToString() + "周";
break;
}
dt.Rows.Add(projectInfoID, curWeek, 0, 0);
}
resDT.Merge(dt);
//startDate = startDate.AddMonths(1);
}
var columChart = HighChartHelper.CreateColumnChart("", resDT, "BelongWeek", series.Split(','), serieFields.Split(','));
columChart.Is3D = false;
columChart.TitleInfo.Text = "";
return(columChart.Render());
}
public Dictionary <string, object> GetPieChart(string projectInfoID)
{
string sql = @"select Sum(ToA1) as ToA1,MajorName from S_EP_PublishInfo
where ProjectInfoID='" + projectInfoID + "' and PublishTime is not null group by MajorName";
var dt = this.SqlHelper.ExecuteDataTable(sql);
var chart = HighChartHelper.CreatePieChart("各专业出图情况", "折A1张数", dt, "MajorName", "ToA1");
var result = chart.Render();
var credits = new Dictionary <string, object>();
credits.SetValue("enabled", false);
result.SetValue("credits", credits);
return(result);
}
public Dictionary <string, object> GetChartData(string Year, DataTable planReceiptDt, DataTable receiptDt, DataTable planPaymentDt, DataTable paymentDt)
{
var dt = new DataTable();
var series = "PlanReceipt,Receipt,ReceiptDeviation,PlanPayment,Payment,PayDeviation";
var serieNames = "计划收款,实际收款,收款偏差,计划付款,实际付款,付款偏差";
dt.Columns.Add("Month");
dt.Columns.Add("PlanReceipt", typeof(decimal));
dt.Columns.Add("Receipt", typeof(decimal));
dt.Columns.Add("PlanPayment", typeof(decimal));
dt.Columns.Add("Payment", typeof(decimal));
dt.Columns.Add("PayDeviation", typeof(decimal));
dt.Columns.Add("ReceiptDeviation", typeof(decimal));
var miniYValue = 0;
for (int i = 1; i <= 12; i++)
{
var dataRow = dt.NewRow();
dataRow["Month"] = i + "月";
var planReceiptRows = planReceiptDt.Select("BelongMonth=" + i.ToString());
dataRow["PlanReceipt"] = planReceiptRows.Length == 0 ? 0m : Convert.ToDecimal(planReceiptRows[0]["Value"]);
var receiptRows = receiptDt.Select("BelongMonth=" + i.ToString());
dataRow["Receipt"] = receiptRows.Length == 0 ? 0m : Convert.ToDecimal(receiptRows[0]["Value"]);
var planPaymentRows = planPaymentDt.Select("BelongMonth=" + i.ToString());
dataRow["PlanPayment"] = planPaymentRows.Length == 0 ? 0m : Convert.ToDecimal(planPaymentRows[0]["Value"]);
var paymentRows = paymentDt.Select("BelongMonth=" + i.ToString());
dataRow["Payment"] = paymentRows.Length == 0 ? 0m : Convert.ToDecimal(paymentRows[0]["Value"]);
dataRow["PayDeviation"] = Convert.ToDecimal(dataRow["PlanPayment"]) - Convert.ToDecimal(dataRow["Payment"]);
dataRow["ReceiptDeviation"] = Convert.ToDecimal(dataRow["PlanReceipt"]) - Convert.ToDecimal(dataRow["Receipt"]);
dt.Rows.Add(dataRow);
if (miniYValue > Convert.ToInt32(dataRow["PayDeviation"]))
{
miniYValue = Convert.ToInt32(dataRow["PayDeviation"]);
}
if (miniYValue > Convert.ToInt32(dataRow["ReceiptDeviation"]))
{
miniYValue = Convert.ToInt32(dataRow["ReceiptDeviation"]);
}
}
var columChart = HighChartHelper.CreateColumnChart(Year + "年计划收支分析", dt, "Month", serieNames.Split(','), series.Split(','));
columChart.yAxisInfo.MiniValue = miniYValue;
return(columChart.Render());
}
/// <summary>
/// 科目费用分布
/// </summary>
/// <returns></returns>
private Dictionary <string, object> GetContractSubjectPieData(string costUnitArr)
{
var sourceDt = this.SQLDB.ExecuteDataTable(string.Format(@"select sum(isnull(ApplyValue,0)) valueField, SubjectName nameField from S_EP_ReimbursementApply_Details detail
left join S_EP_ReimbursementApply apply on apply.ID = detail.S_EP_ReimbursementApplyID
where apply.FlowPhase = 'End' and ProjectInfo in ('{0}') group by SubjectName", costUnitArr));
var chart = HighChartHelper.CreatePieChart("", "", sourceDt);
var result = chart.Render();
var credits = new Dictionary <string, object>();
credits.SetValue("enabled", false);
result.SetValue("credits", credits);
return(result);
}
public JsonResult GetPieChartData(string ProjectInfoID, string ID, string Type = null)
{
if (String.IsNullOrEmpty(Type))
{
}
var sql = @"
select '已结算' ItemName,ISnull(SummaryCostQuantity,0) ItemValue from {2} where
ProjectInfoID='{0}' {1}
union
select '未结算' ItemName,Quantity - ISnull(SummaryCostQuantity,0) ItemValue from {2} where
ProjectInfoID='{0}' {1}
";
var whereStr = "";
if (!String.IsNullOrEmpty(ID))
{
whereStr = String.Format("and ID='{0}'", ID);
}
else
{
whereStr = "and NodeType = 'Root'";
}
if (String.IsNullOrEmpty(Type))
{
sql = String.Format(sql, ProjectInfoID, whereStr, "S_C_CBS");
}
else
{
sql = String.Format(sql, ProjectInfoID, whereStr, "S_C_CBS_Budget");
}
var data = this.SqlHelper.ExecuteDataTable(sql);
var result = new Dictionary <string, object>();
var chat = HighChartHelper.CreatePieChart("项目定额工时结算情况分析", "项目定额工时结算情况分析", data, "ItemName", "ItemValue").Render();
var plot = (Dictionary <string, object>)chat["plotOptions"];
var pie = (Dictionary <string, object>)plot["pie"];
var dataLabels = (Dictionary <string, object>)pie["dataLabels"];
dataLabels.SetValue("format", "<b>{point.name}</b>: {point.percentage:.1f} %");
pie.SetValue("dataLabels", dataLabels);
plot.SetValue("pie", pie);
chat.SetValue("plotOptions", plot);
result["chartData"] = chat;
return(Json(result));
}
public Dictionary <string, object> GetLineChartData(string Year, DataTable planReceiptDt, DataTable receiptDt, DataTable planPaymentDt, DataTable paymentDt)
{
var series = "PlanCashFlow,CashFlow";
var serieNames = "计划现金流,实际现金流";
var dt = new DataTable();
dt.Columns.Add("Month");
dt.Columns.Add("PlanCashFlow", typeof(decimal));
dt.Columns.Add("CashFlow", typeof(decimal));
var miniYValue = 0;
for (int i = 1; i <= 12; i++)
{
var dataRow = dt.NewRow();
dataRow["Month"] = i + "月";
var planReceiptRows = planReceiptDt.Select("BelongMonth=" + i.ToString());
var planReceipt = planReceiptRows.Length == 0 ? 0m : Convert.ToDecimal(planReceiptRows[0]["Value"]);
var planPaymentRows = planPaymentDt.Select("BelongMonth=" + i.ToString());
var planPayment = planPaymentRows.Length == 0 ? 0m : Convert.ToDecimal(planPaymentRows[0]["Value"]);
dataRow["PlanCashFlow"] = planReceipt - planPayment;
var receiptRows = receiptDt.Select("BelongMonth=" + i.ToString());
var receiptValue = receiptRows.Length == 0 ? 0m : Convert.ToDecimal(receiptRows[0]["Value"]);
var paymentRows = paymentDt.Select("BelongMonth=" + i.ToString());
var paymentvalue = paymentRows.Length == 0 ? 0m : Convert.ToDecimal(paymentRows[0]["Value"]);
dataRow["CashFlow"] = receiptValue - paymentvalue;
dt.Rows.Add(dataRow);
if (miniYValue > Convert.ToInt32(dataRow["CashFlow"]))
{
miniYValue = Convert.ToInt32(dataRow["CashFlow"]);
}
if (miniYValue > Convert.ToInt32(dataRow["PlanCashFlow"]))
{
miniYValue = Convert.ToInt32(dataRow["PlanCashFlow"]);
}
}
var columChart = HighChartHelper.CreateColumnChart(Year + "年现金流分析", dt, "Month", serieNames.Split(','), series.Split(','));
columChart.Chart.Type = "line";
columChart.yAxisInfo.MiniValue = miniYValue;
return(columChart.Render());
}
public Dictionary <string, object> GetPieChartData(string belongYear)
{
var depts = EnumBaseHelper.GetEnumDef("System.ManDept").EnumItem;
string sql = @"select * from (select Sum(ToA1) SumToA1,ChargeDeptID,ChargeDeptName,BelongYear from S_EP_PublishInfo
left join S_I_ProjectInfo on S_EP_PublishInfo.ProjectInfoID=S_I_ProjectInfo.ID
where PublishTime is not null
group by ChargeDeptID,ChargeDeptName,BelongYear ) tableInfo where ChargeDeptID in ('" + string.Join("','", depts.Select(a => a.Code).ToArray()) + @"') ";
if (!String.IsNullOrEmpty(belongYear))
{
sql += " and BelongYear='" + belongYear + "'";
}
var db = SQLHelper.CreateSqlHelper(ConnEnum.Project);
var dt = db.ExecuteDataTable(sql);
var pieChart = HighChartHelper.CreatePieChart(belongYear + "各部门出图", "出图量", dt, "ChargeDeptName", "SumToA1");
var chartOption = pieChart.Render();
var credits = new Dictionary <string, object>();
credits.SetValue("enabled", false);
chartOption.SetValue("credits", credits);
return(chartOption);
}
public Dictionary <string, object> GetPieChartData(string belongYear, string projectID)
{
string sql = @"select * from (select Sum(WorkHourValue) as WorkHourValue,ProjectID,BelongYear,MajorCode,MajorName from S_W_UserWorkHour
group by BelongYear,MajorCode,MajorName,ProjectID ) tableInfo where 1=1";
if (!String.IsNullOrEmpty(belongYear))
{
sql += " and BelongYear='" + belongYear + "'";
}
if (!String.IsNullOrEmpty(projectID))
{
sql += " and ProjectID='" + projectID + "'";
}
var db = SQLHelper.CreateSqlHelper(ConnEnum.HR);
var dt = db.ExecuteDataTable(sql);
var pieChart = HighChartHelper.CreatePieChart(belongYear + "专业工时分布", "工时", dt, "MajorName", "WorkHourValue");
var chartOption = pieChart.Render();
var credits = new Dictionary <string, object>();
credits.SetValue("enabled", false);
chartOption.SetValue("credits", credits);
return(chartOption);
}
private Dictionary <string, object> GetPieChartOption(int year, int month)
{
var startDate = new DateTime(year, month, 1);
var endDate = startDate.AddMonths(1).AddDays(-1);
var day = new WorkHourFO().GetWorkValue(startDate, endDate);
var title = year.ToString() + "年" + month + "月 应填：{0} 已填：{1} 未填：{2}";
string sql = @"select isnull(Sum(" + (workHourType != WorkHourSaveType.HD.ToString() ? "NormalValue" : "Round(isnull(NormalValue,0)/" + NormalHoursMax.ToString() + ",2)") + @"),0) as valueField,ProjectName as nameField from dbo.S_W_UserWorkHour
where UserID='{0}' and WorkHourDate>='{1}' and WorkHourDate<='{2}' group by ProjectName";
var dt = this.SqlHelper.ExecuteDataTable(String.Format(sql, this.CurrentUserInfo.UserID, startDate, endDate));
if (dt.Rows.Count == 0)
{
var row = dt.NewRow();
row["valueField"] = day;
row["nameField"] = "未填工时";
dt.Rows.Add(row);
title = String.Format(title, day, 0, Convert.ToInt16(day));
}
else
{
var obj = Convert.ToDecimal(dt.Compute("Sum(valueField)", ""));
if ((day - obj) > 0)
{
var row = dt.NewRow();
row["valueField"] = day - obj;
row["nameField"] = "未填工时";
dt.Rows.Add(row);
}
title = String.Format(title, day, Convert.ToInt16(obj), Convert.ToInt16(day - obj));
}
var pieChart = HighChartHelper.CreatePieChart(title, "工时", dt);
return(pieChart.Render());
}
public override JsonResult GetList(QueryBuilder qb)
{
string projectInfoID = this.Request["ProjectInfoID"];
var list = this.entities.Set <S_AE_Mistake>().Where(d => d.ProjectInfoID == projectInfoID).ToList();
var result = new List <Dictionary <string, object> >();
var mistakeLevels = EnumBaseHelper.GetEnumDef("Project.MistakeLevel").EnumItem;
string fields = String.Empty;
string seriesNames = String.Empty;
string seriesFields = String.Empty;
foreach (var item in mistakeLevels)
{
fields += ", 0 as " + item.Code;
seriesFields += item.Code + ",";
seriesNames += item.Name + ",";
}
//柱状图sql模型
var sql = @"select distinct Name as MajorName,WBSValue,SortIndex {1},0 as Summary
from S_W_WBS wbs
where ProjectInfoID='{0}'
and WBSType = 'Major'
order by SortIndex";
var dt = this.SqlHelper.ExecuteDataTable(String.Format(sql, projectInfoID, fields));
foreach (DataRow row in dt.Rows)
{
var wbsValue = row["WBSValue"].ToString();
var majorName = row["MajorName"].ToString();
var dic = new Dictionary <string, object>();
dic.SetValue("MajorValue", wbsValue);
dic.SetValue("MajorName", majorName);
double mistakeSum = 0;
foreach (var level in mistakeLevels)
{
var mistakeCount = list.Count(d => d.MajorCode == wbsValue && d.MistakeLevel == level.Code);
dic.SetValue(level.Code, mistakeCount);
mistakeSum += mistakeCount;
row[level.Code] = mistakeCount;
}
dic.SetValue("Summary", mistakeSum);
result.Add(dic);
}
var data = new Dictionary <string, object>();
data.SetValue("data", result);
#region 创建柱状图
var columnChart = HighChartHelper.CreateColumnChart("专业质量问题分析", dt, "MajorName", seriesNames.TrimEnd(',').Split(',')
, seriesFields.TrimEnd(',').Split(','));
data.SetValue("ColumnChart", columnChart.Render());
#endregion
#region 创建饼图
sql = @"select item.Code,item.Name,item.SortIndex,
MisNum=(select count(0) from S_AE_Mistake mis
where mis.ProjectInfoID='{0}' and mis.MistakeLevel=item.Code)
from {1}.dbo.S_M_EnumDef def
left join {1}.dbo.S_M_EnumItem item
on def.ID=item.EnumDefID
where def.Code='Project.MistakeLevel'
order by item.SortIndex";
dt = this.SqlHelper.ExecuteDataTable(String.Format(sql, projectInfoID, SQLHelper.CreateSqlHelper(ConnEnum.Base).DbName));
var pieChart = HighChartHelper.CreatePieChart("质量问题分类分析", "错误", dt, "Name", "MisNum");
var chartOption = pieChart.Render();
var credits = new Dictionary <string, object>();
credits.SetValue("enabled", false);
chartOption.SetValue("credits", credits);
#endregion
data.SetValue("PieChart", chartOption);
return(Json(data));
}
public JsonResult GetList(string StartDate, string EndDate)
{
var baseDB = SQLHelper.CreateSqlHelper(ConnEnum.Base);
var HrDB = SQLHelper.CreateSqlHelper(ConnEnum.HR);
var resultDt = new DataTable();
resultDt.Columns.Add("DeptName");
resultDt.Columns.Add("DeptID");
resultDt.Columns.Add("UserCount", typeof(int));
resultDt.Columns.Add("BaseWorkHour", typeof(decimal));
resultDt.Columns.Add("Production", typeof(decimal));
resultDt.Columns.Add("Other", typeof(decimal));
resultDt.Columns.Add("SumWorkHour", typeof(decimal));
resultDt.Columns.Add("AvgSumWorkHour", typeof(decimal));
resultDt.Columns.Add("AvgProduction", typeof(decimal));
resultDt.Columns.Add("ProductionScale", typeof(decimal));
var deptSQL = @"select ID,Name,UserCount,SortIndex from S_A_Org
left join (select Count(0) as UserCount,DeptID from dbo.S_A_User
where IsDeleted='0' group by DeptID) UserDeptInfo on S_A_Org.ID=UserDeptInfo.DeptID
where Type in ('ManufactureDept') order by SortIndex ";
var mainDeptDt = baseDB.ExecuteDataTable(deptSQL);
string sql = @"select * from (select UserDeptID,UserDeptName,BelongYear,BelongQuarter,BelongMonth,
isnull(Max(Production),0) as ProductionWorkHour,
isnull(Max(Other),0) as OtherWorkHour
from (select Sum(WorkHourValue) as WorkHourValue,DeptWorkHourType,
UserDeptID,UserDeptName,BelongYear,BelongMonth,BelongQuarter
from (select case when WorkHourType='Production' then 'Production' else 'Other' end as DeptWorkHourType,*
from S_W_UserWorkHour where 1=1 {0} ) S_W_UserWorkHour
group by UserDeptID,UserDeptName,DeptWorkHourType,BelongYear,BelongMonth,BelongQuarter
) as DeptWorkHourInfo
pivot(avg(WorkHourValue) for DeptWorkHourType in (Production,Other)) tableInfo
group by UserDeptID,UserDeptName,BelongYear,BelongQuarter,BelongMonth) TableInfo ";
if (String.IsNullOrEmpty(EndDate))
{
EndDate = DateTime.Now.ToShortDateString();
}
if (String.IsNullOrEmpty(StartDate))
{
StartDate = DateTime.Now.AddMonths(-1).ToShortDateString();
}
string whereStr = " and WorkHourDate>='" + StartDate + "' and WorkHourDate <= '" + EndDate + "'";
var workHourDt = HrDB.ExecuteDataTable(String.Format(sql, whereStr));
var standardWork = this.getStandardWorkHour(Convert.ToDateTime(StartDate), Convert.ToDateTime(EndDate));
var result = new Dictionary <string, object>();
foreach (DataRow dept in mainDeptDt.Rows)
{
var row = resultDt.NewRow();
row["DeptName"] = dept["Name"];
row["DeptID"] = dept["ID"];
var userCount = dept["UserCount"] == null || dept["UserCount"] == DBNull.Value ? 0 : Convert.ToInt32(dept["UserCount"]);
row["UserCount"] = userCount;
var standardWorkHour = dept["UserCount"] == null || dept["UserCount"] == DBNull.Value ? 0 : Convert.ToInt32(dept["UserCount"]) * standardWork;
row["BaseWorkHour"] = standardWorkHour;
var obj = workHourDt.Compute("Sum(ProductionWorkHour)", " UserDeptID='" + dept["ID"] + "'");
var productionValue = obj == null || obj == DBNull.Value ? 0m : Convert.ToDecimal(obj);
obj = workHourDt.Compute("Sum(OtherWorkHour)", " UserDeptID='" + dept["ID"] + "'");
var otherValue = obj == null || obj == DBNull.Value ? 0m : Convert.ToDecimal(obj);
var sumWorkHour = productionValue + otherValue;
row["Production"] = productionValue;
row["Other"] = otherValue;
row["SumWorkHour"] = productionValue + otherValue;
row["AvgSumWorkHour"] = userCount == 0 ? 0m : Math.Round((productionValue + otherValue) / userCount, 2);
row["AvgProduction"] = userCount == 0 ? 0m : Math.Round(productionValue / userCount, 2);
row["ProductionScale"] = standardWorkHour == 0 ? 0 : Math.Round(productionValue * 100 / standardWorkHour, 2);
resultDt.Rows.Add(row);
}
result.SetValue("data", resultDt);
var yAxies = new List <yAxis>();
var y1 = new yAxis {
MiniValue = 0, TitleInfo = new Dictionary <string, object>(), Lable = new Dictionary <string, object>()
};
y1.TitleInfo.SetValue("text", "工时");
y1.Lable.SetValue("format", "{value}");
var y2 = new yAxis {
MiniValue = 0, TitleInfo = new Dictionary <string, object>(), Lable = new Dictionary <string, object>()
};
y2.TitleInfo.SetValue("text", "项目负荷率");
y2.Lable.SetValue("format", "{value}%"); y2.opposite = true;
yAxies.Add(y1);
yAxies.Add(y2);
var serDefines = new List <Series>();
var productionSer = new Series {
Name = "项目工时", Field = "Production", Type = "column", yAxis = 0, Tooltip = new Dictionary <string, object>()
};
var otherer = new Series {
Name = "非项目工时", Field = "Other", Type = "column", yAxis = 0, Tooltip = new Dictionary <string, object>()
};
var productionScaleSer = new Series {
Name = "项目负荷率", Field = "ProductionScale", Type = "spline", yAxis = 1, Tooltip = new Dictionary <string, object>()
};
productionScaleSer.Tooltip.SetValue("valueSuffix", "%");
serDefines.Add(productionSer);
serDefines.Add(otherer);
serDefines.Add(productionScaleSer);
var chart = HighChartHelper.CreateColumnXYChart("", "", resultDt, "DeptName", yAxies, serDefines, null);
result.SetValue("chart", chart);
return(Json(result));
}
public JsonResult GetAnalysisList()
{
string queryData = this.Request["QueryData"];
var startDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
var endDate = startDate.AddMonths(1);
if (!String.IsNullOrEmpty(queryData))
{
var query = JsonHelper.ToObject(queryData);
if (!String.IsNullOrEmpty(query.GetValue("StartDate")))
{
startDate = Convert.ToDateTime(query.GetValue("StartDate"));
}
if (!String.IsNullOrEmpty(query.GetValue("EndDate")))
{
endDate = Convert.ToDateTime(query.GetValue("EndDate"));
}
}
var dt = CreateTable();
var sql = @"select Count(0) as WorkCount,UserInfo,ContactType from dbo.S_M_BusinessTrace
where RegisterDate>='{0}' and RegisterDate<='{1}'
group by UserInfo,ContactType";
var dataDt = this.SqlHelper.ExecuteDataTable(String.Format(sql, startDate.ToString(), endDate.ToString()));
foreach (DataRow row in dt.Rows)
{
var totalValue = 0m;
var rows = dataDt.Select("UserInfo='" + row["UserID"].ToString() + "'");
foreach (DataRow item in rows)
{
if (item["ContactType"] == null || item["ContactType"] == DBNull.Value || String.IsNullOrEmpty(item["ContactType"].ToString()))
{
continue;
}
var field = item["ContactType"].ToString() + "_Count";
var value = item["WorkCount"] == null || item["WorkCount"] == DBNull.Value ? 0m : Convert.ToDecimal(item["WorkCount"]);
row[field] = value;
totalValue += value;
}
row["TotalValue"] = totalValue;
}
var result = new Dictionary <string, object>();
result.SetValue("data", dt);
#region 生成图表
var series = string.Empty; var serieFields = string.Empty;
var def = EnumBaseHelper.GetEnumDef("EPC.SaleWorkContent");
var items = def.EnumItem.ToList();
foreach (var item in items)
{
series += item.Name + ",";
serieFields += item.Code + "_Count,";
}
series = series.TrimEnd(',');
serieFields = serieFields.TrimEnd(',');
var columChart = HighChartHelper.CreateColumnChart("销售人员工作分析", dt, "UserName", series.Split(','), serieFields.Split(','));
result.SetValue("chartData", columChart.Render());
#endregion
return(Json(result));
}
public JsonResult GetDeptContractList()
{
var dt = EnumBaseHelper.GetEnumTable("System.ManDept");
dt.Columns.Add("ContractKPI", typeof(decimal));
dt.Columns.Add("ContractValue", typeof(decimal));
dt.Columns.Add("ContractComplateRate", typeof(decimal));
dt.Columns.Add("UnContractValue", typeof(decimal));
dt.Columns.Add("ContractKPIRemain", typeof(decimal));
dt.Columns.Add("TimeRate", typeof(decimal));
var belongYear = String.IsNullOrEmpty(GetQueryString("BelongYear")) ? DateTime.Now.Year : Convert.ToInt32(GetQueryString("BelongYear"));
var sql = "select * from S_KPI_IndicatorOrg where IndicatorType = 'YearIndicator' and BelongYear= '{0}'";
var indicatorDt = this.SqlHelper.ExecuteDataTable(String.Format(sql, belongYear));
sql = @"select isnull(Sum(ContractRMBValue),0) as DataValue,ChargeDeptID,ChargeDeptName,BelongYear,BelongMonth,BelongQuarter
FROM(
SELECT ContractRMBValue,ProductionDept as ChargeDeptID,ProductionDeptName as ChargeDeptName,
YEAR(SignDate) AS BelongYear,MONTH(SignDate) AS BelongMonth,DATEPART(QUARTER,SignDate) AS BelongQuarter
FROM dbo.S_M_ContractInfo
WHERE ContractState='Sign' AND YEAR(SignDate)='{0}'
)resulttable
GROUP BY ChargeDeptID,ChargeDeptName,BelongYear,BelongMonth,BelongQuarter ";
var contractDt = this.SqlHelper.ExecuteDataTable(String.Format(sql, belongYear));
sql = @"select isnull(Sum(ContractRMBValue),0) as DataValue,ChargeDeptID,ChargeDeptName,BelongYear,BelongMonth,BelongQuarter
FROM(
SELECT ContractRMBValue,ProductionDept as ChargeDeptID,ProductionDeptName as ChargeDeptName,
YEAR(SignDate) AS BelongYear,MONTH(SignDate) AS BelongMonth,DATEPART(QUARTER,SignDate) AS BelongQuarter
FROM dbo.S_M_ContractInfo
WHERE ContractState!='Sign' OR ContractState IS NULL
)resulttable
GROUP BY ChargeDeptID,ChargeDeptName,BelongYear,BelongMonth,BelongQuarter";
var UncCntractDt = this.SqlHelper.ExecuteDataTable(sql);
var sumKpi = 0m; var sumValue = 0m;
foreach (DataRow row in dt.Rows)
{
var obj = indicatorDt.Compute("Sum(ContractValue)", "OrgID='" + row["value"] + "'");
var kpi = obj == null || obj == DBNull.Value ? 0 : Convert.ToDecimal(obj);
row["ContractKPI"] = kpi;
sumKpi += kpi;
obj = contractDt.Compute("Sum(DataValue)", "ChargeDeptID='" + row["value"] + "'");
var value = obj == null || obj == DBNull.Value ? 0 : Convert.ToDecimal(obj);
row["ContractValue"] = value;
sumValue += value;
row["ContractComplateRate"] = kpi == 0 ? 100 : Math.Round(value / kpi * 100, 2);
obj = UncCntractDt.Compute("Sum(DataValue)", "ChargeDeptID='" + row["value"] + "'");
row["UnContractValue"] = obj == null || obj == DBNull.Value ? 0 : Convert.ToDecimal(obj);
row["ContractKPIRemain"] = kpi - value;
row["TimeRate"] = Math.Round(Convert.ToDecimal(DateTime.Now.DayOfYear) / 365 * 100);
}
var result = new Dictionary <string, object>();
result.SetValue("data", dt);
var sumData = new Dictionary <string, object>();
sumData.SetValue("ContractComplateRate", sumKpi == 0 ? 100 : Math.Round(sumValue / sumKpi * 100, 2));
result.SetValue("sumData", sumData);
var yAxies = new List <yAxis>();
var y1 = new yAxis {
MiniValue = 0, TitleInfo = new Dictionary <string, object>(), Lable = new Dictionary <string, object>()
};
y1.TitleInfo.SetValue("text", "合同金额");
y1.Lable.SetValue("format", "{value}元");
var y2 = new yAxis {
MiniValue = 0, TitleInfo = new Dictionary <string, object>(), Lable = new Dictionary <string, object>()
};
y2.TitleInfo.SetValue("text", "完成率");
y2.Lable.SetValue("format", "{value}%"); y2.opposite = true;
yAxies.Add(y1);
yAxies.Add(y2);
var serDefines = new List <Series>();
var ReceiptKPISer = new Series {
Name = "合同目标", Field = "ContractKPI", Type = "column", yAxis = 0, Tooltip = new Dictionary <string, object>()
};
ReceiptKPISer.Tooltip.SetValue("valueSuffix", "元");
serDefines.Add(ReceiptKPISer);
var ReceiptValueSer = new Series {
Name = "已签订金额", Field = "ContractValue", Type = "column", yAxis = 0, Tooltip = new Dictionary <string, object>()
};
ReceiptValueSer.Tooltip.SetValue("valueSuffix", "元");
serDefines.Add(ReceiptValueSer);
var CanReceiptValueSer = new Series {
Name = "待签约金额", Field = "UnContractValue", Type = "column", yAxis = 0, Tooltip = new Dictionary <string, object>()
};
CanReceiptValueSer.Tooltip.SetValue("valueSuffix", "元");
serDefines.Add(CanReceiptValueSer);
var RecepitComplateRateSer = new Series {
Name = "完成率", Field = "ContractComplateRate", Type = "spline", yAxis = 1, Tooltip = new Dictionary <string, object>()
};
RecepitComplateRateSer.Tooltip.SetValue("valueSuffix", "%");
serDefines.Add(RecepitComplateRateSer);
//var TimeRateSer = new Series { Name = "时间", Field = "TimeRate", Type = "spline", yAxis = 1, Tooltip = new Dictionary<string, object>() };
//TimeRateSer.Tooltip.SetValue("valueSuffix", "%");
//serDefines.Add(TimeRateSer);
var chart = HighChartHelper.CreateColumnXYChart(belongYear + "年各部门合同情况", "", dt, "text", yAxies, serDefines, null);
result.SetValue("chart", chart);
return(Json(result));
}
public JsonResult GetContractList(QueryBuilder qb, string Year)
{
var receiptSQL = @"SELECT ISNULL(SUM(ReceiptInfo.ReceiptValue), 0) AS Value ,
ProductionDept
FROM ( SELECT S_M_Receipt.* ,
YEAR(ReceiptDate) AS BelongYear,
ProductionDept ,
ProductionDeptName
FROM S_M_Receipt
LEFT JOIN S_M_ContractInfo ON ContractInfo = S_M_ContractInfo.ID
) ReceiptInfo
WHERE 1 = 1 {0}
GROUP BY ProductionDept;";
var invoiceSQL = @"SELECT ISNULL(SUM(ReceiptInfo.InvoiceValue), 0) AS Value ,
ProductionDept
FROM ( SELECT S_M_Invoice.* ,
YEAR(InvoiceDate) AS BelongYear,
ProductionDept ,
ProductionDeptName
FROM S_M_Invoice
LEFT JOIN S_M_ContractInfo ON ContractInfo = S_M_ContractInfo.ID
) ReceiptInfo
WHERE State = 'Normal' {0}
GROUP BY ProductionDept;";
var contractSQL = @"SELECT ISNULL(SUM(ContractRMBValue), 0) AS Value ,
ISNULL(SUM(SumBadDebtValue), 0) AS BadValue ,
YEAR(SignDate) AS BelongYear,
ProductionDept
FROM S_M_ContractInfo
WHERE ContractState='Sign' {0}
GROUP BY ProductionDept;";
string innerWhere = string.Empty;
if (!String.IsNullOrEmpty(Year) && Year.ToLowerInvariant() != "all")
{
innerWhere += " and BelongYear in ('" + Year.Replace(",", "','") + "')";
}
receiptSQL = String.Format(receiptSQL, innerWhere);
invoiceSQL = String.Format(invoiceSQL, innerWhere);
contractSQL = String.Format(contractSQL, innerWhere);
var receiptDt = this.SqlHelper.ExecuteDataTable(receiptSQL);
var invoiceDt = this.SqlHelper.ExecuteDataTable(invoiceSQL);
var contractDt = this.SqlHelper.ExecuteDataTable(contractSQL);
var deptField = "ProductionDept";
var deptNameField = "ProductionDeptName";
var resultDt = CreateDefaultTable(qb, deptField, deptNameField);
foreach (DataRow item in resultDt.Rows)
{
var receiptRow = receiptDt.Select("ProductionDept='" + item[deptField] + "'").FirstOrDefault();
if (receiptRow != null)
{
item["ReceiptValue"] = receiptRow["Value"];
}
var invoiceRow = invoiceDt.Select("ProductionDept='" + item[deptField] + "'").FirstOrDefault();
if (invoiceRow != null)
{
item["InvoiceValue"] = invoiceRow["Value"];
}
var contractRow = contractDt.Select("ProductionDept='" + item[deptField] + "'").FirstOrDefault();
if (contractRow != null)
{
item["ContractValue"] = contractRow["Value"];
item["BadDebtValue"] = contractRow["BadValue"];
}
item["RemainValue"] = Convert.ToDecimal(item["ContractValue"]) -
Convert.ToDecimal(item["ReceiptValue"]) - Convert.ToDecimal(item["BadDebtValue"]);
item["ReciveableValue"] = Convert.ToDecimal(item["InvoiceValue"]) -
Convert.ToDecimal(item["ReceiptValue"]) - Convert.ToDecimal(item["BadDebtValue"]);
}
var result = new Dictionary <string, object>();
result.SetValue("data", resultDt);
var series = "合同总额,收款总额,开票总额,坏账总额,应收款总额,剩余合同额";
var serieFields = "ContractValue,ReceiptValue,InvoiceValue,BadDebtValue,ReciveableValue,RemainValue";
var chartData = HighChartHelper.CreateColumnChart("", resultDt, deptNameField, series.Split(','), serieFields.Split(','));
result.SetValue("chartData", chartData.Render());
return(Json(result));
}
public JsonResult GetDeptReceiptList()
{
var dt = EnumBaseHelper.GetEnumTable("System.ManDept");
dt.Columns.Add("ReceiptKPI", typeof(decimal));
dt.Columns.Add("ReceiptValue", typeof(decimal));
dt.Columns.Add("RecepitComplateRate", typeof(decimal));
dt.Columns.Add("CanReceiptValue", typeof(decimal));
dt.Columns.Add("RemaintContractValue", typeof(decimal));
dt.Columns.Add("ReceiptKPIRemain", typeof(decimal));
dt.Columns.Add("TimeRate", typeof(decimal));
var belongYear = String.IsNullOrEmpty(GetQueryString("BelongYear")) ? DateTime.Now.Year : Convert.ToInt32(GetQueryString("BelongYear"));
var sql = "select * from S_KPI_IndicatorOrg where IndicatorType = 'YearIndicator' and BelongYear= '{0}'";
var indicatorDt = this.SqlHelper.ExecuteDataTable(String.Format(sql, belongYear));
sql = @"SELECT ISNULL(SUM(ReceiptInfo.ReceiptValue), 0) AS Value ,
ProductionDept,ProductionDeptName
FROM ( SELECT S_M_Receipt.* ,
YEAR(ReceiptDate) AS BelongYear,
ProductionDept ,
ProductionDeptName
FROM S_M_Receipt
LEFT JOIN S_M_ContractInfo ON ContractInfo = S_M_ContractInfo.ID
WHERE YEAR(ReceiptDate)='{0}'
) ReceiptInfo
WHERE 1 = 1
GROUP BY ProductionDept,ProductionDeptName;";
var receiptDt = this.SqlHelper.ExecuteDataTable(String.Format(sql, belongYear));
sql = @" select isnull(Sum(isnull(ReceiptValue,0)-isnull(FactReceiptValue,0)),0) as DataValue,
ProductionDept as ChargeDeptID,ProductionDeptName as ChargeDeptName
from dbo.S_M_ContractInfo_ReceiptObj left join dbo.S_M_ContractInfo
on S_M_ContractInfo_ReceiptObj.S_M_ContractInfoID=S_M_ContractInfo.ID
group by ProductionDept,ProductionDeptName";
var canReceiptDt = this.SqlHelper.ExecuteDataTable(sql);
sql = @"select Sum(isnull(ContractRMBValue,0)-isnull(SumReceiptValue,0)) as DataValue,ProductionDept as ChargeDeptID,ProductionDeptName as ChargeDeptName
from S_M_ContractInfo where ContractState='Sign' group by ProductionDept,ProductionDeptName";
var remainContractDt = this.SqlHelper.ExecuteDataTable(sql);
var sumKpi = 0m; var sumReceiptValue = 0m;
foreach (DataRow row in dt.Rows)
{
var obj = indicatorDt.Compute("Sum(ReceiptValue)", "OrgID='" + row["value"] + "'");
var kpi = obj == null || obj == DBNull.Value ? 0 : Convert.ToDecimal(obj);
row["ReceiptKPI"] = kpi;
sumKpi += kpi;
obj = receiptDt.Compute("Sum(DataValue)", "ChargeDeptID='" + row["value"] + "'");
var recepitValue = obj == null || obj == DBNull.Value ? 0 : Convert.ToDecimal(obj);
row["ReceiptValue"] = recepitValue;
sumReceiptValue += recepitValue;
row["RecepitComplateRate"] = kpi == 0 ? 100 : Math.Round(recepitValue / kpi * 100, 2);
obj = canReceiptDt.Compute("Sum(DataValue)", "ChargeDeptID='" + row["value"] + "'");
row["CanReceiptValue"] = obj == null || obj == DBNull.Value ? 0 : Convert.ToDecimal(obj);
row["RemaintContractValue"] = remainContractDt.Compute("Sum(DataValue)", "ChargeDeptID='" + row["value"] + "'");
row["ReceiptKPIRemain"] = kpi - recepitValue;
row["TimeRate"] = Math.Round(Convert.ToDecimal(DateTime.Now.DayOfYear) / 365 * 100);
}
var result = new Dictionary <string, object>();
result.SetValue("data", dt);
var sumData = new Dictionary <string, object>();
sumData.SetValue("RecepitComplateRate", sumKpi == 0 ? 100 : Math.Round(sumReceiptValue / sumKpi * 100, 2));
result.SetValue("sumData", sumData);
var yAxies = new List <yAxis>();
var y1 = new yAxis {
MiniValue = 0, TitleInfo = new Dictionary <string, object>(), Lable = new Dictionary <string, object>()
};
y1.TitleInfo.SetValue("text", "收款金额");
y1.Lable.SetValue("format", "{value}元");
var y2 = new yAxis {
MiniValue = 0, TitleInfo = new Dictionary <string, object>(), Lable = new Dictionary <string, object>()
};
y2.TitleInfo.SetValue("text", "完成率");
y2.Lable.SetValue("format", "{value}%"); y2.opposite = true;
yAxies.Add(y1);
yAxies.Add(y2);
var serDefines = new List <Series>();
var ReceiptKPISer = new Series {
Name = "收款目标", Field = "ReceiptKPI", Type = "column", yAxis = 0, Tooltip = new Dictionary <string, object>()
};
ReceiptKPISer.Tooltip.SetValue("valueSuffix", "元");
serDefines.Add(ReceiptKPISer);
var ReceiptValueSer = new Series {
Name = "已收款", Field = "ReceiptValue", Type = "column", yAxis = 0, Tooltip = new Dictionary <string, object>()
};
ReceiptValueSer.Tooltip.SetValue("valueSuffix", "元");
serDefines.Add(ReceiptValueSer);
var CanReceiptValueSer = new Series {
Name = "经营应收款", Field = "CanReceiptValue", Type = "column", yAxis = 0, Tooltip = new Dictionary <string, object>()
};
CanReceiptValueSer.Tooltip.SetValue("valueSuffix", "元");
serDefines.Add(CanReceiptValueSer);
var RecepitComplateRateSer = new Series {
Name = "完成率", Field = "RecepitComplateRate", Type = "spline", yAxis = 1, Tooltip = new Dictionary <string, object>()
};
RecepitComplateRateSer.Tooltip.SetValue("valueSuffix", "%");
serDefines.Add(RecepitComplateRateSer);
var chart = HighChartHelper.CreateColumnXYChart(belongYear + "年各部门收款情况", "", dt, "text", yAxies, serDefines, null);
result.SetValue("chart", chart);
return(Json(result));
}
public JsonResult GetDeptIndicatorList(string anlysisValue = "ReceiptValue")
{
var dt = EnumBaseHelper.GetEnumTable("System.ManDept");
dt.Columns.Add("ContractKPI", typeof(decimal));
dt.Columns.Add("ContractValue", typeof(decimal));
dt.Columns.Add("ContractComplateRate", typeof(decimal));
dt.Columns.Add("UnContractValue", typeof(decimal));
dt.Columns.Add("ContractKPIRemain", typeof(decimal));
dt.Columns.Add("ReceiptKPI", typeof(decimal));
dt.Columns.Add("ReceiptValue", typeof(decimal));
dt.Columns.Add("RecepitComplateRate", typeof(decimal));
dt.Columns.Add("CanReceiptValue", typeof(decimal));
dt.Columns.Add("RemaintContractValue", typeof(decimal));
dt.Columns.Add("ReceiptKPIRemain", typeof(decimal));
dt.Columns.Add("TimeRate", typeof(decimal));
var belongYear = String.IsNullOrEmpty(GetQueryString("BelongYear")) ? DateTime.Now.Year : Convert.ToInt32(GetQueryString("BelongYear"));
var sql = "select * from S_KPI_IndicatorOrg where IndicatorType = 'YearIndicator' and BelongYear= '{0}'";
var indicatorDt = this.SqlHelper.ExecuteDataTable(String.Format(sql, belongYear));
sql = @"SELECT ISNULL(SUM(ReceiptInfo.ReceiptValue), 0) AS Value ,
ProductionDept,ProductionDeptName
FROM ( SELECT ReceiptValue,
ProductionDept,ProductionDeptName
FROM S_M_Receipt
LEFT JOIN S_M_ContractInfo ON ContractInfo = S_M_ContractInfo.ID
WHERE YEAR(ReceiptDate)='{0}'
) ReceiptInfo
WHERE 1 = 1
GROUP BY ProductionDept,ProductionDeptName; ";
var receiptDt = this.SqlHelper.ExecuteDataTable(String.Format(sql, belongYear));
sql = @" select isnull(Sum(isnull(ReceiptValue,0)-isnull(FactReceiptValue,0)),0) as DataValue,ProductionDept as ChargeDeptID,ProductionDeptName as ChargeDeptName
FROM S_M_ContractInfo_ReceiptObj
left join S_M_ContractInfo on S_M_ContractInfo_ReceiptObj.S_M_ContractInfoID=S_M_ContractInfo.ID
group by ProductionDept,ProductionDeptName";
var canReceiptDt = this.SqlHelper.ExecuteDataTable(sql);
sql = @" select Sum(isnull(ContractRMBValue,0)-isnull(SumReceiptValue,0)) as DataValue,ProductionDept as ChargeDeptID,
ProductionDeptName as ChargeDeptName
from S_M_ContractInfo where ContractState='Sign' group by ProductionDept,ProductionDeptName";
var remainContractDt = this.SqlHelper.ExecuteDataTable(sql);
sql = @" SELECT isnull(Sum(ContractRMBValue),0) as DataValue,ChargeDeptID,ChargeDeptName,BelongYear,BelongMonth,BelongQuarter
FROM
(select ContractRMBValue,ProductionDept as ChargeDeptID,ProductionDeptName as ChargeDeptName,
YEAR(SignDate) AS BelongYear,MONTH(SignDate) AS BelongMonth,DATEPART(QUARTER,SignDate) AS BelongQuarter
from S_M_ContractInfo
WHERE ContractState='Sign' and YEAR(SignDate)='{0}')result
GROUP by BelongYear,BelongQuarter,BelongMonth,ChargeDeptID,ChargeDeptName ";
var contractDt = this.SqlHelper.ExecuteDataTable(String.Format(sql, belongYear));
sql = @"select isnull(Sum(ContractRMBValue),0) as DataValue,ChargeDeptID,ChargeDeptName,BelongYear,BelongMonth,BelongQuarter
FROM(
SELECT ContractRMBValue,ProductionDept as ChargeDeptID,ProductionDeptName as ChargeDeptName,
YEAR(SignDate) AS BelongYear,MONTH(SignDate) AS BelongMonth,DATEPART(QUARTER,SignDate) AS BelongQuarter
FROM dbo.S_M_ContractInfo
WHERE ContractState!='Sign' OR ContractState IS NULL
)resulttable
GROUP BY ChargeDeptID,ChargeDeptName,BelongYear,BelongMonth,BelongQuarter";
var UncCntractDt = this.SqlHelper.ExecuteDataTable(sql);
var sumReceptKpi = 0m; var sumReceiptValue = 0m; var sumContractKpi = 0m; var sumContractValue = 0m;
foreach (DataRow row in dt.Rows)
{
var obj = indicatorDt.Compute("Sum(ReceiptValue)", "OrgID='" + row["value"] + "'");
var receptkpi = obj == null || obj == DBNull.Value ? 0 : Convert.ToDecimal(obj);
row["ReceiptKPI"] = receptkpi;
sumReceptKpi += receptkpi;
obj = receiptDt.Compute("Sum(DataValue)", "ChargeDeptID='" + row["value"] + "'");
var recepitValue = obj == null || obj == DBNull.Value ? 0 : Convert.ToDecimal(obj);
row["ReceiptValue"] = recepitValue;
sumReceiptValue += recepitValue;
row["RecepitComplateRate"] = receptkpi == 0 ? 100 : Math.Round(recepitValue / receptkpi * 100, 2);
obj = canReceiptDt.Compute("Sum(DataValue)", "ChargeDeptID='" + row["value"] + "'");
row["CanReceiptValue"] = obj == null || obj == DBNull.Value ? 0 : Convert.ToDecimal(obj);
row["RemaintContractValue"] = remainContractDt.Compute("Sum(DataValue)", "ChargeDeptID='" + row["value"] + "'");
row["ReceiptKPIRemain"] = receptkpi - recepitValue;
//row["TimeRate"] = Math.Round(Convert.ToDecimal(DateTime.Now.DayOfYear) / 365 * 100);
obj = indicatorDt.Compute("Sum(ContractValue)", "OrgID='" + row["value"] + "'");
var contractKpi = obj == null || obj == DBNull.Value ? 0 : Convert.ToDecimal(obj);
row["ContractKPI"] = contractKpi;
sumContractKpi += contractKpi;
obj = contractDt.Compute("Sum(DataValue)", "ChargeDeptID='" + row["value"] + "'");
var value = obj == null || obj == DBNull.Value ? 0 : Convert.ToDecimal(obj);
row["ContractValue"] = value;
sumContractValue += value;
row["ContractComplateRate"] = contractKpi == 0 ? 100 : Math.Round(value / contractKpi * 100, 2);
obj = UncCntractDt.Compute("Sum(DataValue)", "ChargeDeptID='" + row["value"] + "'");
row["UnContractValue"] = obj == null || obj == DBNull.Value ? 0 : Convert.ToDecimal(obj);
row["ContractKPIRemain"] = contractKpi - value;
}
var result = new Dictionary <string, object>();
result.SetValue("data", dt);
var sumData = new Dictionary <string, object>();
sumData.SetValue("RecepitComplateRate", sumReceptKpi == 0 ? 100 : Math.Round(sumReceiptValue / sumReceptKpi * 100, 2));
sumData.SetValue("ContractComplateRate", sumContractKpi == 0 ? 100 : Math.Round(sumReceiptValue / sumContractKpi * 100, 2));
result.SetValue("sumData", sumData);
#region 生成图表
var yAxies = new List <yAxis>();
var y1 = new yAxis {
MiniValue = 0, TitleInfo = new Dictionary <string, object>(), Lable = new Dictionary <string, object>()
};
y1.TitleInfo.SetValue("text", "金额");
y1.Lable.SetValue("format", "{value}元");
var y2 = new yAxis {
MiniValue = 0, TitleInfo = new Dictionary <string, object>(), Lable = new Dictionary <string, object>()
};
y2.TitleInfo.SetValue("text", "完成率");
y2.Lable.SetValue("format", "{value}%"); y2.opposite = true;
yAxies.Add(y1);
yAxies.Add(y2);
var serDefines = new List <Series>();
if (anlysisValue == AnlysisValue.ReceiptValue.ToString())
{
var ReceiptKPISer = new Series {
Name = "收款目标", Field = "ReceiptKPI", Type = "column", yAxis = 0, Tooltip = new Dictionary <string, object>()
};
ReceiptKPISer.Tooltip.SetValue("valueSuffix", "元");
serDefines.Add(ReceiptKPISer);
var ReceiptValueSer = new Series {
Name = "已收款", Field = "ReceiptValue", Type = "column", yAxis = 0, Tooltip = new Dictionary <string, object>()
};
ReceiptValueSer.Tooltip.SetValue("valueSuffix", "元");
serDefines.Add(ReceiptValueSer);
var CanReceiptValueSer = new Series {
Name = "经营应收款", Field = "CanReceiptValue", Type = "column", yAxis = 0, Tooltip = new Dictionary <string, object>()
};
CanReceiptValueSer.Tooltip.SetValue("valueSuffix", "元");
serDefines.Add(CanReceiptValueSer);
var RecepitComplateRateSer = new Series {
Name = "收款完成率", Field = "RecepitComplateRate", Type = "spline", yAxis = 1, Tooltip = new Dictionary <string, object>()
};
RecepitComplateRateSer.Tooltip.SetValue("valueSuffix", "%");
serDefines.Add(RecepitComplateRateSer);
}
else
{
var contractKPISer = new Series {
Name = "合同目标", Field = "ContractKPI", Type = "column", yAxis = 0, Tooltip = new Dictionary <string, object>()
};
contractKPISer.Tooltip.SetValue("valueSuffix", "元");
serDefines.Add(contractKPISer);
var contractValueSer = new Series {
Name = "已签订金额", Field = "ContractValue", Type = "column", yAxis = 0, Tooltip = new Dictionary <string, object>()
};
contractValueSer.Tooltip.SetValue("valueSuffix", "元");
serDefines.Add(contractValueSer);
var unContractValueSer = new Series {
Name = "待签约金额", Field = "UnContractValue", Type = "column", yAxis = 0, Tooltip = new Dictionary <string, object>()
};
unContractValueSer.Tooltip.SetValue("valueSuffix", "元");
serDefines.Add(unContractValueSer);
var contractComplateRateSer = new Series {
Name = "合同完成率", Field = "ContractComplateRate", Type = "spline", yAxis = 1, Tooltip = new Dictionary <string, object>()
};
contractComplateRateSer.Tooltip.SetValue("valueSuffix", "%");
serDefines.Add(contractComplateRateSer);
}
//var TimeRateSer = new Series { Name = "时间", Field = "TimeRate", Type = "spline", yAxis = 1, Tooltip = new Dictionary<string, object>() };
//TimeRateSer.Tooltip.SetValue("valueSuffix", "%");
//serDefines.Add(TimeRateSer);
string title = belongYear + "年各部门收款完成情况";
if (anlysisValue == AnlysisValue.ContractValue.ToString())
{
title = belongYear + "年各部门合同完成情况";
}
var chart = HighChartHelper.CreateColumnXYChart(title, "", dt, "text", yAxies, serDefines, null);
result.SetValue("chart", chart);
#endregion
return(Json(result));
}
public JsonResult GetProjectList(QueryBuilder qb, string Year)
{
var receiptSQL = @"SELECT isnull(Sum(RelationValue),0) as Value,ChargerDept FROM (
select * FROM(
select RelationValue,ChargerDept,YEAR(PlanDate) AS BelongYear
from S_M_Receipt_PlanRelation left join S_M_Receipt on S_M_ReceiptID = S_M_Receipt.ID
left join S_M_ContractInfo_ReceiptObj on ReceiptObjID = S_M_ContractInfo_ReceiptObj.ID
left join S_I_Engineering on S_M_ContractInfo_ReceiptObj.ProjectInfo= S_I_Engineering.ID
)midTable
WHERE 1=1 {0})result
group by ChargerDept";
var invoiceSQL = @"select isnull(Sum(RelationValue),0) as Value,ChargerDept FROM(
SELECT * FROM (SELECT RelationValue,ChargerDept,YEAR(InvoiceDate) AS BelongYear,S_M_Invoice.State
from S_M_Invoice_ReceiptObjRelation left join S_M_Invoice on S_M_InvoiceID = S_M_Invoice.ID
left join S_M_ContractInfo_ReceiptObj on S_M_ReceiptObjID = S_M_ContractInfo_ReceiptObj.ID
left join S_I_Engineering on ProjectInfo = S_I_Engineering.ID
)midtable
where State = 'Normal' {0}
)result
group by ChargerDept";
var contractSQL = @"select isnull(Sum(ReceiptValue),0) as Value,isnull(Sum(FactBadValue),0) as BadValue,ChargerDept FROM
(
SELECT * FROM (
SELECT ReceiptValue,FactBadValue,S_I_Engineering.ChargerDept,YEAR(SignDate) AS BelongYear,ContractState
FROM S_M_ContractInfo_ReceiptObj
left join S_I_Engineering on ProjectInfo = S_I_Engineering.ID
left join S_M_ContractInfo on S_M_ContractInfo_ReceiptObj.S_M_ContractInfoID = S_M_ContractInfo.ID
)midTable
where ContractState='Sign' {0}
)result
group by ChargerDept";
string innerWhere = string.Empty;
if (!String.IsNullOrEmpty(Year) && Year.ToLowerInvariant() != "all")
{
innerWhere += " and BelongYear in ('" + Year.Replace(",", "','") + "')";
}
receiptSQL = String.Format(receiptSQL, innerWhere);
invoiceSQL = String.Format(invoiceSQL, innerWhere);
contractSQL = String.Format(contractSQL, innerWhere);
var receiptDt = this.SqlHelper.ExecuteDataTable(receiptSQL);
var invoiceDt = this.SqlHelper.ExecuteDataTable(invoiceSQL);
var contractDt = this.SqlHelper.ExecuteDataTable(contractSQL);
var resultDt = CreateDefaultTable(qb);
foreach (DataRow item in resultDt.Rows)
{
var receiptRow = receiptDt.Select("ChargerDept='" + item["ChargerDept"] + "'").FirstOrDefault();
if (receiptRow != null)
{
item["ReceiptValue"] = receiptRow["Value"];
}
var invoiceRow = invoiceDt.Select("ChargerDept='" + item["ChargerDept"] + "'").FirstOrDefault();
if (invoiceRow != null)
{
item["InvoiceValue"] = invoiceRow["Value"];
}
var contractRow = contractDt.Select("ChargerDept='" + item["ChargerDept"] + "'").FirstOrDefault();
if (contractRow != null)
{
item["ContractValue"] = contractRow["Value"];
item["BadDebtValue"] = contractRow["BadValue"];
}
item["RemainValue"] = Convert.ToDecimal(item["ContractValue"]) -
Convert.ToDecimal(item["ReceiptValue"]) - Convert.ToDecimal(item["BadDebtValue"]);
item["ReciveableValue"] = Convert.ToDecimal(item["InvoiceValue"]) -
Convert.ToDecimal(item["ReceiptValue"]) - Convert.ToDecimal(item["BadDebtValue"]);
}
var result = new Dictionary <string, object>();
result.SetValue("data", resultDt);
var series = "合同总额,收款总额,开票总额,坏账总额,应收款总额,剩余合同额";
var serieFields = "ContractValue,ReceiptValue,InvoiceValue,BadDebtValue,ReciveableValue,RemainValue";
var chartData = HighChartHelper.CreateColumnChart("", resultDt, "ChargerDeptName", series.Split(','), serieFields.Split(','));
result.SetValue("chartData", chartData.Render());
return(Json(result));
}
private Dictionary <string, object> GetChartData(string xAxisType, string anlysisValue, string analysisType, int startYear, int endYear)
{
string series = string.Empty;
string serieFields = string.Empty;
string sql = @"select Sum(DataValue) as Value,BelongYear {0} from
(select isnull(ThisContractRMBAmount,0) as DataValue,BelongYear,BelongQuarter,BelongMonth
from S_C_ManageContract where IsSigned='" + ContractIsSigned.Signed + @"'
union all
select isnull(SupplementaryRMBAmount,0) as DataValue,ad.BelongYear,ad.BelongQuarter,ad.BelongMonth
from S_C_ManageContract_Supplementary ad
inner join S_C_ManageContract con on ad.ContractInfoID=con.ID where IsSigned='" + ContractIsSigned.Signed + @"'
)tb where BelongYear>='" + startYear + "' and BelongYear<='" + endYear + "' group by BelongYear {0}";
if (anlysisValue == AnlysisValue.ReceiptValue.ToString())
{
sql = @"select Sum(Amount) as Value,BelongYear {0} from S_C_Receipt
where BelongYear>='" + startYear + "' and BelongYear<='" + endYear + "' group by BelongYear {0}";
}
#region 月为X轴统计图表
if (xAxisType == XAxisType.Month.ToString())
{
sql = String.Format(sql, ",BelongMonth");
var dt = this.SqlHelper.ExecuteDataTable(sql);
var dataSource = new DataTable();
dataSource.Columns.Add("Month", typeof(string));
for (int i = startYear; i <= endYear; i++)
{
series += i.ToString() + "年,";
serieFields += i.ToString() + ",";
dataSource.Columns.Add(i.ToString(), typeof(decimal));
}
for (int i = 1; i <= 12; i++)
{
var row = dataSource.NewRow();
row["Month"] = i + "月";
for (int j = startYear; j <= endYear; j++)
{
var data = dt.Select("BelongMonth='" + i + "' and BelongYear='" + j + "'").FirstOrDefault();
if (analysisType == AnalysisType.Month.ToString())
{
if (data == null || data["Value"] == null || data["Value"] == DBNull.Value)
{
row[j.ToString()] = 0;
}
else
{
row[j.ToString()] = data["Value"];
}
}
else
{
var value = 0M;
if (j == DateTime.Now.Year && i > DateTime.Now.Month)
{
row[j.ToString()] = value;
}
else
{
if (data != null && data["Value"] != null && data["Value"] != DBNull.Value)
{
value = Convert.ToDecimal(data["Value"]);
}
if (i == 1)
{
row[j.ToString()] = value;
}
else
{
var preDataRow = dataSource.Select("Month='" + (i - 1) + "月'").FirstOrDefault();
var preValue = 0M;
if (preDataRow != null)
{
preValue = Convert.ToDecimal(preDataRow[j.ToString()]);
value += preValue;
}
}
}
row[j.ToString()] = value;
}
}
dataSource.Rows.Add(row);
}
series = series.TrimEnd(',');
serieFields = serieFields.TrimEnd(',');
var columChart = HighChartHelper.CreateColumnChart("", dataSource, "Month", series.Split(','), serieFields.Split(','));
return(columChart.Render());
}
#endregion
#region 年统计图表
else
{
sql = String.Format(sql, "");
var dt = this.SqlHelper.ExecuteDataTable(sql);
var dataSource = new DataTable();
dataSource.Columns.Add("Value", typeof(decimal));
dataSource.Columns.Add("Year", typeof(string));
for (int i = startYear; i <= endYear; i++)
{
var row = dataSource.NewRow();
row["Year"] = i;
var data = dt.Select(" BelongYear='" + i + "' ").FirstOrDefault();
//单月统计
if (analysisType == AnalysisType.Month.ToString())
{
if (data == null || data["Value"] == null || data["Value"] == DBNull.Value)
{
row["Value"] = 0;
}
else
{
row["Value"] = data["Value"];
}
dataSource.Rows.Add(row);
}
else //累计统计
{
var value = 0M;
if (data == null || data["Value"] == null || data["Value"] == DBNull.Value)
{
value = 0;
}
else
{
value = Convert.ToDecimal(data["Value"]);
}
if (i == startYear)
{
row["Value"] = value;
}
else
{
var preRow = dataSource.Select("Year='" + (i - 1) + "'").FirstOrDefault();
var preValue = 0M;
if (preRow != null && preRow["Value"] != null && preRow["Value"] != DBNull.Value)
{
preValue = Convert.ToDecimal(preRow["Value"]);
}
row["Value"] = preValue + value;
}
dataSource.Rows.Add(row);
}
}
series = EnumBaseHelper.GetEnumDescription(typeof(AnlysisValue), anlysisValue);
serieFields = "Value";
var columChart = HighChartHelper.CreateColumnChart("", dataSource, "Year", series.Split(','), serieFields.Split(','));
return(columChart.Render());
}
#endregion
}
/// <summary>
/// 获取列表数据
/// </summary>
/// <returns></returns>
public JsonResult GetCostList(QueryBuilder qb)
{
//费用科目查询
var cbsType = "";
var whereCBSType = " and isnull(ParentID,'')=''";
foreach (var item in qb.Items)
{
if (item.Field == "CBSType")
{
cbsType = item.Value.ToString();
}
}
if (!string.IsNullOrEmpty(cbsType))
{
whereCBSType = " and isnull(ParentID,'')!='' and CBSType='" + cbsType + "'";
}
//年份查询
var belongYear = "";
var whereBelong = " ";
foreach (var item in qb.Items)
{
if (item.Field == "BelongYear")
{
belongYear = item.Value.ToString();
}
}
if (!string.IsNullOrEmpty(belongYear))
{
whereBelong = " and BelongYear='" + belongYear + "'";
}
//月份查询
var belongMonth = "";
foreach (var item in qb.Items)
{
if (item.Field == "BelongMonth")
{
belongMonth = item.Value.ToString();
}
}
if (!string.IsNullOrEmpty(belongMonth))
{
whereBelong += " and BelongMonth='" + belongMonth + "'";
}
//季度查询
var belongQuarter = "";
foreach (var item in qb.Items)
{
if (item.Field == "BelongQuarter")
{
belongQuarter = item.Value.ToString();
}
}
if (!string.IsNullOrEmpty(belongQuarter))
{
whereBelong += " and BelongQuarter='" + belongQuarter + "'";
}
//获取列表数据
var projectInfoID = GetQueryString("ProjectInfoID");
var sqlHelper = SQLHelper.CreateSqlHelper(ConnEnum.Project);
var sql = @"select CBSCode,CBSName,sum(CBSValue) as CBSValue,sum(CostValue) as CostValue from
(
select CBSCode,CBSName,CBSValue,
(select sum(CostValue) from S_C_CBSCost as c
inner join S_C_CBSNode as n on c.CBSNodeID=n.ID
where n.FullID like '%'+node.ID+'%' {0}) as CostValue
from S_C_CBSNode as node
where CBSNodeType in ('CBS','Major') and ProjectInfoID='" + projectInfoID + @"' {1}
) as t
group by CBSCode,CBSName";
var resultDt = sqlHelper.ExecuteDataTable(string.Format(sql, whereBelong, whereCBSType));
//图表数据
var result = new Dictionary <string, object>();
result.SetValue("data", resultDt);
var series = "";
var serieFields = "";
var chartDt = new DataTable();
chartDt.Columns.Add("CBSType");
//预算金额
var cbsRow = chartDt.NewRow();
cbsRow["CBSType"] = "预算金额";
chartDt.Rows.Add(cbsRow);
//实耗成本
var costRow = chartDt.NewRow();
costRow["CBSType"] = "实耗成本";
chartDt.Rows.Add(costRow);
if (resultDt.Rows.Count > 0)
{
foreach (DataRow resultDr in resultDt.Rows)
{
series += resultDr["CBSName"] + ",";
serieFields += resultDr["CBSCode"] + ",";
chartDt.Columns.Add(resultDr["CBSCode"].ToString(), typeof(decimal));
cbsRow[resultDr["CBSCode"].ToString()] = resultDr["CBSValue"];
costRow[resultDr["CBSCode"].ToString()] = resultDr["CostValue"];
}
var chartData = HighChartHelper.CreateColumnChart("", chartDt, "CBSType", series.Trim(',').Split(','), serieFields.Trim(',').Split(','));
result.SetValue("chartData", chartData.Render());
return(Json(result));
}
else
{
return(Json(""));
}
}
