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));
}
