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 }
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 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 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 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 Dictionary <string, object> GetYearChartData(string projectID) { string sql = @"select Sum(S_C_ReceiptPlanRelation.RelationValue) as ReceiptValue, S_C_ManageContract_ReceiptObj.ProjectInfo as ProjectInfoID, S_C_Receipt.BelongMonth,S_C_Receipt.BelongQuarter ,S_C_Receipt.BelongYear from S_C_ReceiptPlanRelation left join S_C_Receipt on S_C_ReceiptPlanRelation.ReceiptID=S_C_Receipt.ID left join S_C_ManageContract_ReceiptObj on S_C_ReceiptPlanRelation.ReceiptObjectID=S_C_ManageContract_ReceiptObj.ID where ProjectInfo='{0}' group by S_C_ManageContract_ReceiptObj.ProjectInfo, S_C_Receipt.BelongMonth,S_C_Receipt.BelongQuarter ,S_C_Receipt.BelongYear "; var db = SQLHelper.CreateSqlHelper(ConnEnum.Market); var dt = db.ExecuteDataTable(String.Format(sql, projectID)); var dataSource = new DataTable(); dataSource.Columns.Add("YearMonth", typeof(string)); dataSource.Columns.Add("ReceiptValue", typeof(decimal)); dataSource.Columns.Add("SumReceiptValue", typeof(decimal)); var sumValue = 0m; foreach (DataRow item in dt.Rows) { var row = dataSource.NewRow(); var belongYear = Convert.ToInt32(item["BelongYear"]); var belongMonth = Convert.ToInt32(item["BelongMonth"]); if (belongMonth < 10) { row["YearMonth"] = belongYear + "-0" + belongMonth + "月"; } else { row["YearMonth"] = belongYear + "-" + belongMonth + "月"; } var value = item["ReceiptValue"] == null || item["ReceiptValue"] == DBNull.Value ? 0m : Convert.ToDecimal(item["ReceiptValue"]); sumValue += value; row["ReceiptValue"] = value; row["SumReceiptValue"] = sumValue; 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 = "ReceiptValue", Type = "column", yAxis = 0, Tooltip = new Dictionary <string, object>() }; var sumCostSer = new Series { Name = "累计收款", Field = "SumReceiptValue", Type = "spline", yAxis = 0, Tooltip = new Dictionary <string, object>() }; serDefines.Add(costSer); serDefines.Add(sumCostSer); var chart = HighChartHelper.CreateColumnXYChart("收款分析", "", dataSource, "YearMonth", yAxies, serDefines, null); return(chart); #endregion }
public JsonResult GetAnalysisList() { string queryData = this.Request["QueryData"]; var lastYear = 5; if (!String.IsNullOrEmpty(queryData)) { var query = JsonHelper.ToObject(queryData); if (!String.IsNullOrEmpty(query.GetValue("LastYear"))) { lastYear = Convert.ToInt32(query.GetValue("LastYear")); } } var startYear = DateTime.Now.Year - 5 + 1; var startDate = new DateTime(startYear, 1, 1); var endDate = new DateTime(DateTime.Now.Year, 12, 31); var dt = CreateTable(); var sql = @"select count(0) as BidCount,BusinessType from S_M_BidResult where MakingTime>='{0}' and MakingTime<='{1}' group by Year(MakingTime),BusinessType"; var bidInfoDt = this.SqlHelper.ExecuteDataTable(String.Format(sql, startDate.ToString(), endDate.ToString())); sql = @"select count(0) as BidCount,BusinessType from S_M_BidResult where IsBid='{2}' and MakingTime>='{0}' and MakingTime<='{1}' group by Year(MakingTime),BusinessType"; var inbidInfoDt = this.SqlHelper.ExecuteDataTable(String.Format(sql, startDate.ToString(), endDate.ToString(), "是")); foreach (DataRow row in dt.Rows) { var bidCount = 0m; var inbidCount = 0m; var scale = 0m; var bidRows = bidInfoDt.Select("BusinessType='" + row["Value"].ToString() + "'"); if (bidRows.Length > 0) { bidCount = bidRows[0]["BidCount"] == null || bidRows[0]["BidCount"] == DBNull.Value ? 0m : Convert.ToDecimal(bidRows[0]["BidCount"]); } var inbidRows = inbidInfoDt.Select("BusinessType='" + row["Value"].ToString() + "'"); if (inbidRows.Length > 0) { inbidCount = inbidRows[0]["BidCount"] == null || inbidRows[0]["BidCount"] == DBNull.Value ? 0m : Convert.ToDecimal(inbidRows[0]["BidCount"]); } if (bidCount > 0) { scale = Math.Round(inbidCount / bidCount * 100, 2); } row["BidCount"] = bidCount; row["InBidCount"] = inbidCount; row["Scale"] = scale; } var result = new Dictionary <string, object>(); result.SetValue("data", dt); #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>(); var contractValueSer = new Series { Name = "投标数", Field = "BidCount", Type = "column", yAxis = 0, Tooltip = new Dictionary <string, object>() }; contractValueSer.Tooltip.SetValue("valueSuffix", "个"); serDefines.Add(contractValueSer); var unContractValueSer = new Series { Name = "中标数", Field = "InBidCount", Type = "column", yAxis = 0, Tooltip = new Dictionary <string, object>() }; unContractValueSer.Tooltip.SetValue("valueSuffix", "个"); serDefines.Add(unContractValueSer); var contractComplateRateSer = new Series { Name = "中标率", Field = "Scale", Type = "spline", yAxis = 1, Tooltip = new Dictionary <string, object>() }; contractComplateRateSer.Tooltip.SetValue("valueSuffix", "%"); serDefines.Add(contractComplateRateSer); string title = "投标结果分析"; var chart = HighChartHelper.CreateColumnXYChart(title, "", dt, "text", yAxies, serDefines, null); result.SetValue("chartData", chart); #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 SUM(DataValue) DataValue,BelongYear,BelongQuarter,BelongMonth,ChargeDeptID,ChargeDeptName from ( select isnull(DeptValue,0) as DataValue, Dept as ChargeDeptID,DeptName as ChargeDeptName, BelongYear,BelongQuarter,BelongMonth from S_C_ManageContract_DeptRelation dept left join S_C_ManageContract con on dept.S_C_ManageContractID = con.ID where IsSigned='{1}' and BelongYear='{0}' union select isnull(DeptValue,0) as DataValue, Dept as ChargeDeptID,DeptName as ChargeDeptName, ad.BelongYear,ad.BelongQuarter,ad.BelongMonth from S_C_ManageContract_Supplementary_DeptRelation dept left join S_C_ManageContract_Supplementary ad on ad.ID=dept.S_C_ManageContract_SupplementaryID where ad.SignDate is not null and ad.SignDate !='' and ad.BelongYear='{0}' ) tb group by BelongYear,BelongQuarter,BelongMonth,ChargeDeptID,ChargeDeptName "; var contractDt = this.SqlHelper.ExecuteDataTable(String.Format(sql, belongYear, ContractIsSigned.Signed.ToString())); sql = @"select SUM(DataValue) DataValue,BelongYear,BelongQuarter,BelongMonth,ChargeDeptID,ChargeDeptName from ( select isnull(DeptValue,0) as DataValue, Dept as ChargeDeptID,DeptName as ChargeDeptName, BelongYear,BelongQuarter,BelongMonth from S_C_ManageContract_DeptRelation dept left join S_C_ManageContract con on dept.S_C_ManageContractID = con.ID where IsSigned='{0}' or IsSigned is null union select isnull(DeptValue,0) as DataValue, Dept as ChargeDeptID,DeptName as ChargeDeptName, ad.BelongYear,ad.BelongQuarter,ad.BelongMonth from S_C_ManageContract_Supplementary_DeptRelation dept left join S_C_ManageContract_Supplementary ad on ad.ID=dept.S_C_ManageContract_SupplementaryID where ad.SignDate is null or ad.SignDate ='' ) tb group by BelongYear,BelongQuarter,BelongMonth,ChargeDeptID,ChargeDeptName "; var UncCntractDt = this.SqlHelper.ExecuteDataTable(String.Format(sql, ContractIsSigned.UnSigned.ToString())); 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 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 Sum(dept.RelationValue) as DataValue,Dept as ChargeDeptID,DeptName as ChargeDeptName from S_C_Receipt_DeptRelation dept left join S_C_Receipt receipt on dept.S_C_Receipt_ID = receipt.ID where BelongYear='{0}' group by Dept,DeptName"; var receiptDt = this.SqlHelper.ExecuteDataTable(String.Format(sql, belongYear)); sql = @"select isnull(Sum(isnull(ReceiptValue,0)-isnull(FactReceiptValue,0)),0) as DataValue, ChargerDept as ChargeDeptID,ChargerDeptName as ChargeDeptName from S_C_ManageContract_ReceiptObj left join S_I_Project on S_C_ManageContract_ReceiptObj.ProjectInfo=S_I_Project.ID where MileStoneState='{0}' group by ChargerDept,ChargerDeptName"; var canReceiptDt = this.SqlHelper.ExecuteDataTable(String.Format(sql, true.ToString())); sql = @"select Sum(isnull(DeptValue,0)-isnull(SumDeptReceiptValue,0)) as DataValue, Dept as ChargeDeptID,DeptName as ChargeDeptName from S_C_ManageContract_DeptRelation dept left join S_C_ManageContract con on dept.S_C_ManageContractID = con.ID where IsSigned='{0}' group by Dept,DeptName"; var remainContractDt = this.SqlHelper.ExecuteDataTable(String.Format(sql, ContractIsSigned.Signed.ToString())); sql = @"select isnull(sum(DataValue),0) as DataValue,Dept as ChargeDeptID,DeptName as ChargeDeptName,BelongYear,BelongQuarter,BelongMonth from (select isnull(DeptValue,0) as DataValue,Dept,DeptName,BelongYear,BelongQuarter,BelongMonth from S_C_ManageContract_DeptRelation dept left join S_C_ManageContract con on dept.S_C_ManageContractID = con.ID where IsSigned='{1}' and BelongYear='{0}' union all select isnull(DeptValue,0) as DataValue,Dept,DeptName,ad.BelongYear,ad.BelongQuarter,ad.BelongMonth from S_C_ManageContract_Supplementary_DeptRelation dept left join S_C_ManageContract_Supplementary ad on ad.ID=dept.S_C_ManageContract_SupplementaryID inner join S_C_ManageContract con on ad.ContractInfoID=con.ID where IsSigned='{1}' and ad.BelongYear='{0}' )tb group by BelongYear,BelongQuarter,BelongMonth,Dept,DeptName"; var contractDt = this.SqlHelper.ExecuteDataTable(String.Format(sql, belongYear, ContractIsSigned.Signed.ToString())); sql = @"select isnull(sum(DataValue),0) as DataValue,Dept as ChargeDeptID,DeptName as ChargeDeptName,BelongYear,BelongQuarter,BelongMonth from (select isnull(DeptValue,0) as DataValue,Dept,DeptName,BelongYear,BelongQuarter,BelongMonth from S_C_ManageContract_DeptRelation dept left join S_C_ManageContract con on dept.S_C_ManageContractID = con.ID where (IsSigned='{0}' or IsSigned is null) union all select isnull(DeptValue,0) as DataValue,Dept,DeptName,ad.BelongYear,ad.BelongQuarter,ad.BelongMonth from S_C_ManageContract_Supplementary_DeptRelation dept left join S_C_ManageContract_Supplementary ad on ad.ID=dept.S_C_ManageContract_SupplementaryID inner join S_C_ManageContract con on ad.ContractInfoID=con.ID where (IsSigned='{0}' or IsSigned is null) )tb group by BelongYear,BelongQuarter,BelongMonth,Dept,DeptName"; var UncCntractDt = this.SqlHelper.ExecuteDataTable(String.Format(sql, ContractIsSigned.UnSigned.ToString())); 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)); }