/// <summary> /// 获取当前合同余额详情 /// </summary> /// <param name="type"></param> /// <param name="id"></param> /// <returns></returns> public static ResultDTO GetCurrentSurplusContract(string type, string id) { string sql, idName = ""; var db = new MarketContext(); EnumQueryType con = (EnumQueryType)System.Enum.Parse(typeof(EnumQueryType), type); switch (con) { case EnumQueryType.Customer: idName = "PartyA"; break; //case EnumQueryType.Department: // idName = "HeadOfSalesDeptID"; // break; default: return(WebApi.Error(EnumException.请求参数不合法)); } sql = @"select ID,Name,Convert(decimal(18,2),((isnull(ContractRMBAmount,0)-isnull(SumReceiptValue,0))/10000)) as ReceiptValue, SignDate from S_C_ManageContract where IsSigned='Signed' and {0}='{1}' order by ReceiptValue desc"; return(WebApi.Success(db.Database.SqlQuery <ContractBll>(string.Format(sql, idName, id)))); }
/// <summary> /// 获取当前应收款详情 /// </summary> /// <param name="type"></param> /// <param name="id"></param> /// <returns></returns> public static ResultDTO GetCurrentAccountReceivable(string type, string id) { string sql, idName = ""; var db = new MarketContext(); EnumQueryType con = (EnumQueryType)System.Enum.Parse(typeof(EnumQueryType), type); switch (con) { case EnumQueryType.Customer: idName = "con.PartyA"; break; case EnumQueryType.Department: idName = "con.ProductionDept"; break; default: return(WebApi.Error(EnumException.请求参数不合法)); } sql = @"SELECT Convert(decimal(18,2),isnull(SUM(SumInvoiceValue-ISNULL(SumReceiptValue,0)),0)/10000) AS ReceiptValue, con.ID as ID,con.Name AS Name, MAX(S_C_Invoice.InvoiceDate) InvoiceDate FROM S_C_ManageContract con left join S_C_Invoice on con.ID = S_C_Invoice.ContractInfoID WHERE {0} = '{1}' and con.IsSigned='Signed' GROUP BY con.ID,con.Name ORDER BY ReceiptValue DESC"; return(WebApi.Success(db.Database.SqlQuery <ContractBll>(string.Format(sql, idName, id)))); }
/// <summary> /// 获取实际收款(当季、当月、本周) /// </summary> /// <param name="type">查询条件</param> /// <param name="date"></param> /// <returns></returns> public static ResultDTO GetCurrentActualReceipt(string type, DateEnum date) { string sql, condition = ""; switch (date) { case DateEnum.Month: condition = "BelongYear = YEAR(getDate()) AND BeLongMonth = MONTH(getDate())"; break; case DateEnum.Quarter: condition = "BelongYear = YEAR(getDate()) AND BelongQuarter = datepart(quarter,getdate())"; break; case DateEnum.Week: condition = "(ArrivedDate between dateadd(wk, datediff(wk,0,getdate()), 0) and getdate())"; break; } var db = new MarketContext(); EnumQueryType con = (EnumQueryType)System.Enum.Parse(typeof(EnumQueryType), type); switch (con) { case EnumQueryType.Customer: sql = @"select CustomerID as ID,CustomerName AS Name ,Convert(decimal(18,2),isnull(SUM(Amount),0)/10000) AS TotalMoney ,count(distinct ContractInfoID) as ContractCount ,COUNT(DISTINCT ID) AS RecordNum from S_C_Receipt WHERE {0} GROUP BY CustomerName,CustomerID ORDER BY TotalMoney DESC"; break; case EnumQueryType.Department: sql = @"SELECT ReceiptMasterUnitID as ID,ReceiptMasterUnit as Name ,Convert(decimal(18,2),isnull(SUM(Amount),0)/10000) AS TotalMoney ,count(distinct ContractInfoID) as ContractCount ,COUNT(DISTINCT CustomerID) AS RecordNum FROM S_C_Receipt WHERE {0} GROUP BY ReceiptMasterUnit,ReceiptMasterUnitID ORDER BY TotalMoney DESC"; break; case EnumQueryType.NotGroup: sql = @"select ContractInfoID as ID,ContractName as Name, COUNT(ContractName) AS RecordNum, Convert(decimal(18,2),isnull(SUM(Amount)/10000,0)) AS ReceiptValue, MAX(ArrivedDate) AS LastTime from S_C_Receipt WHERE {0} GROUP BY ContractName,ContractInfoID ORDER BY ReceiptValue DESC"; return(WebApi.Success(db.Database.SqlQuery <ContractBll>(string.Format(sql, condition)))); default: return(WebApi.Error(EnumException.请求参数不合法)); } return(WebApi.Success(db.Database.SqlQuery <BusinessDetailBll>(string.Format(sql, condition)))); }
/// <summary> /// 获取实际收款详情(当季、当月、本周) /// </summary> /// <param name="type">查询条件</param> /// <param name="date"></param> /// <param name="id">客户ID或部门ID</param> /// <returns></returns> public static ResultDTO GetCurrentActualReceipt(string type, DateEnum date, string id) { string condition = "", idName = ""; switch (date) { case DateEnum.Month: condition = "BelongYear = YEAR(getDate()) AND BeLongMonth = MONTH(getDate())"; break; case DateEnum.Quarter: condition = "BelongYear = YEAR(getDate()) AND BelongQuarter = datepart(quarter,getdate())"; break; case DateEnum.Week: condition = "(ArrivedDate between dateadd(wk, datediff(wk,0,getdate()), 0) and getdate())"; break; } var db = new MarketContext(); EnumQueryType con = (EnumQueryType)System.Enum.Parse(typeof(EnumQueryType), type); switch (con) { case EnumQueryType.Customer: idName = "con.CustomerID"; break; case EnumQueryType.Department: idName = "ReceiptMasterUnitID"; break; default: return(WebApi.Error(EnumException.请求参数不合法)); } var sql = @"SELECT con.ContractID as ID,con.ContractName as Name,COUNT(ID) AS RecordNum, Convert(decimal(18,2),isnull(SUM(Amount),0)/10000) AS ReceiptValue, MAX(ArrivedDate) AS LastTime FROM S_C_Receipt left join ( SELECT ID as ContractID,Name as ContractName,PartyA as CustomerID FROM S_C_ManageContract WHERE IsSigned='Signed' )con on S_C_Receipt.CustomerID=con.CustomerID and S_C_Receipt.ContractInfoID=con.ContractID WHERE {0} = '{1}' AND {2} GROUP BY con.ContractName,con.ContractID ORDER BY ReceiptValue DESC"; return(WebApi.Success(db.Database.SqlQuery <ContractBll>(string.Format(sql, idName, id, condition)))); }
/// <summary> /// 获取已签合同详情(当季、当月、本周)和待签合同额(当年) /// </summary> /// <param name="type"></param> /// <param name="date"></param> /// <param name="id"></param> /// <returns></returns> public static ResultDTO GetCurrentContract(string type, DateEnum date, string id) { string sql, condition = "", idName = ""; switch (date) { case DateEnum.Quarter: condition = "IsSigned='Signed' and BelongYear = YEAR(getDate()) AND BelongQuarter=datepart(quarter,getdate())"; break; case DateEnum.Month: condition = "IsSigned='Signed' and BelongYear = YEAR(getDate()) AND BelongMonth=datepart(month,getdate())"; break; case DateEnum.Week: condition = "IsSigned='Signed' and SignDate between dateadd(wk, datediff(wk,0,getdate()), 0) and getdate()"; break; case DateEnum.Year: condition = "IsSigned!='Signed' --and BelongYear=datepart(year,getdate())"; break; } var db = new MarketContext(); EnumQueryType con = (EnumQueryType)System.Enum.Parse(typeof(EnumQueryType), type); switch (con) { case EnumQueryType.Customer: idName = "PartyA"; break; //case EnumQueryType.Department: // idName = "HeadOfSalesDeptID"; // break; case EnumQueryType.ManagerUser: idName = "BusinessManager"; break; default: return(WebApi.Error(EnumException.请求参数不合法)); } sql = @"select ID,Name,Convert(decimal(18,2),isnull(ContractRMBAmount,0)/10000) as ReceiptValue, SignDate,BusinessManagerName as UserName from S_C_ManageContract where {0} and {1}='{2}' order by ReceiptValue desc"; return(WebApi.Success(db.Database.SqlQuery <ContractBll>(string.Format(sql, condition, idName, id)))); }
/// <summary> /// 获取当前应收款 /// </summary> /// <param name="type"></param> /// <returns></returns> public static ResultDTO GetCurrentAccountReceivable(string type) { string sql; var db = new MarketContext(); EnumQueryType con = (EnumQueryType)System.Enum.Parse(typeof(EnumQueryType), type); switch (con) { case EnumQueryType.Customer: sql = @"SELECT Convert(decimal(18,2),isnull(SUM(SumInvoiceValue-ISNULL(SumReceiptValue,0)),0)/10000) as TotalMoney, PartyA as ID,PartyAName as Name ,count(distinct ID) as ContractCount FROM S_C_ManageContract WHERE IsSigned='Signed' GROUP BY PartyAName,PartyA ORDER BY TotalMoney DESC"; break; case EnumQueryType.Department: sql = @"SELECT Convert(decimal(18,2),isnull(SUM(SumInvoiceValue-ISNULL(SumReceiptValue,0)),0)/10000) AS TotalMoney, ProductionDeptName as Name,ProductionDept as ID, count(distinct ID) as ContractCount FROM S_C_ManageContract WHERE IsSigned='Signed' GROUP BY ProductionDept,ProductionDeptName ORDER BY TotalMoney DESC"; break; case EnumQueryType.NotGroup: sql = @"SELECT Convert(decimal(18,2),isnull(SUM(SumInvoiceValue-ISNULL(SumReceiptValue,0)),0)/10000) AS ReceiptValue, S_C_ManageContract.ID,S_C_ManageContract.Name,MAX(S_C_Invoice.InvoiceDate) InvoiceDate FROM S_C_ManageContract LEFT JOIN S_C_Invoice on S_C_ManageContract.ID = S_C_Invoice.ContractInfoID WHERE IsSigned='Signed' GROUP BY S_C_ManageContract.ID,S_C_ManageContract.Name ORDER BY ReceiptValue DESC"; return(WebApi.Success(db.Database.SqlQuery <ContractBll>(sql))); default: return(WebApi.Error(EnumException.请求参数不合法)); } return(WebApi.Success(db.Database.SqlQuery <BusinessDetailBll>(sql))); }
/// <summary> /// 获取当前合同余额 /// </summary> /// <param name="type"></param> /// <returns></returns> public static ResultDTO GetCurrentSurplusContract(string type) { string sql; var db = new MarketContext(); EnumQueryType con = (EnumQueryType)System.Enum.Parse(typeof(EnumQueryType), type); switch (con) { case EnumQueryType.Customer: sql = @"select PartyA as ID,PartyAName as Name, Convert(decimal(18,2),(isnull(Sum(isnull(ContractRMBAmount,0)-isnull(SumReceiptValue,0)),0))/10000) as TotalMoney, count(distinct ID) ContractCount from S_C_ManageContract where IsSigned='Signed' GROUP BY PartyAName,PartyA ORDER BY TotalMoney DESC"; break; // case EnumQueryType.Department: // sql = @"select HeadOfSalesDeptID as ID,HeadOfSalesDeptName as Name, // Convert(decimal(18,2),(isnull(Sum(isnull(ContractRMBAmount,0)-isnull(SummaryReceiptValue,0)-isnull(SummaryBadDebtValue,0)),0))/10000) as TotalMoney, // count(distinct ID) ContractCount // from S_C_ManageContract // where IsSigned='Signed' // group by HeadOfSalesDeptID,HeadOfSalesDeptName // ORDER BY TotalMoney DESC"; // break; case EnumQueryType.NotGroup: sql = @"select ID,Name,Convert(decimal(18,2),((isnull(ContractRMBAmount,0)-isnull(SumReceiptValue,0))/10000)) as ReceiptValue, SignDate from S_C_ManageContract where IsSigned='Signed' order by ReceiptValue desc"; return(WebApi.Success(db.Database.SqlQuery <ContractBll>(sql))); default: return(WebApi.Error(EnumException.请求参数不合法)); } return(WebApi.Success(db.Database.SqlQuery <BusinessDetailBll>(sql))); }
/// <summary> /// 修改计划收款日期 /// </summary> /// <param name="id">计划收款ID</param> /// <param name="date">修改时间</param> /// <returns></returns> public static ResultDTO UpdatePlanReceiptDate(string id, string date) { DateTime dt = DateTime.Parse(date); var db = new MarketContext(); var plan = db.S_C_PlanReceipt.Find(id); if (plan.State == "UnReceipt")//PlanReceiptState.UnReceipt.ToString() { if (plan.PlanReceiptDate.HasValue) { if (Convert.ToDateTime(plan.PlanReceiptDate) > dt) { return(WebApi.Error("延迟的新日期不能早于原计划完成日期")); } } var newplan = new S_C_PlanReceipt() { ID = Base.CreateGuid(), PlanReceiptDate = dt, BelongYear = dt.Year, BelongMonth = dt.Month, BelongQuarter = Base.GetQuarter(dt), PlanReceiptYearMonth = dt.Year.ToString() + dt.Month.ToString("00"), BadDebtValue = plan.BadDebtValue, ContractCode = plan.ContractCode, ContractInfoID = plan.ContractInfoID, ContractName = plan.ContractName, CustomerCode = plan.CustomerCode, CustomerName = plan.CustomerName, CustomerID = plan.CustomerID, CreateDate = plan.CreateDate, CreateUser = plan.CreateUser, CreateUserID = plan.CreateUserID, DutyType = plan.DutyType, FactReceiptValue = plan.FactReceiptValue, FirstDutyManID = plan.FirstDutyManID, FirstDutyManName = plan.FirstDutyManName, Importance = plan.Importance, IsBadDebt = plan.IsBadDebt, MasterID = plan.MasterID, MasterName = plan.MasterName, MasterUnit = plan.MasterUnit, MasterUnitID = plan.MasterUnitID, ModifyDate = plan.ModifyDate, ModifyUser = plan.ModifyUser, ModifyUserID = plan.ModifyUserID, Name = plan.Name, PlanReceiptValue = plan.PlanReceiptValue, ProduceMasterID = plan.ProduceMasterID, ProduceMasterName = plan.ProduceMasterName, ProductionUnitID = plan.ProductionUnitID, ProductionUnitName = plan.ProductionUnitName, ProjectCode = plan.ProjectCode, ProjectID = plan.ProjectID, ProjectName = plan.ProjectName, ReceiptDate = plan.ReceiptDate, ReceiptObjectID = plan.ReceiptObjectID, RelateParentID = plan.RelateParentID, RiskLevel = plan.RiskLevel, Remark = plan.Remark, S_C_ManageContract = plan.S_C_ManageContract, S_C_ManageContract_ReceiptObj = plan.S_C_ManageContract_ReceiptObj, State = plan.State }; db.S_C_PlanReceipt.Add(newplan); plan.State = "UnFinished";//PlanReceiptState.UnFinished.ToString(); return(WebApi.Success(db.SaveChanges() > 0)); } else { return(WebApi.Error(EnumException.操作异常)); } }
/// <summary> /// 获取计划收款详情(本年、本年后、本季、本季后、本月、本月后) /// </summary> /// <param name="type">查询条件</param> /// <param name="date"></param> /// <param name="id"></param> /// <returns></returns> public static ResultDTO GetCurrentPlanReceipt(string type, DateEnum date, string id) { string sql, condition = "", idName = ""; switch (date) { case DateEnum.Year: condition = "S_C_PlanReceipt.BelongYear <= YEAR(getdate())"; break; case DateEnum.AfterYear: condition = "S_C_PlanReceipt.BelongYear > datepart(year,getdate()) OR S_C_PlanReceipt.BelongYear = NULL"; break; case DateEnum.Quarter: condition = "S_C_PlanReceipt.BelongYear <= datepart(year,getdate()) AND S_C_PlanReceipt.BelongQuarter <= datepart(quarter,getdate())"; break; case DateEnum.AfterQuarter: condition = "(S_C_PlanReceipt.BelongYear = datepart(year,getdate()) AND S_C_PlanReceipt.BelongQuarter > datepart(quarter,getdate())) OR(S_C_PlanReceipt.BelongYear > datepart(year,getdate())) OR (S_C_PlanReceipt.BelongYear = NULL OR S_C_PlanReceipt.BelongQuarter = NULL)"; break; case DateEnum.Month: condition = "S_C_PlanReceipt.BelongYear <= datepart(year,getdate()) AND S_C_PlanReceipt.BelongMonth <= datepart(month,getdate())"; break; case DateEnum.AfterMonth: condition = "(S_C_PlanReceipt.BelongYear = datepart(year,getdate()) AND S_C_PlanReceipt.BelongMonth >datepart(month,getdate())) OR (S_C_PlanReceipt.BelongYear > datepart(year,getdate())) OR (S_C_PlanReceipt.BelongYear = NULL OR S_C_PlanReceipt.BelongQuarter = NULL OR S_C_PlanReceipt.BelongMonth = NULL)"; break; } var db = new MarketContext(); EnumQueryType con = (EnumQueryType)System.Enum.Parse(typeof(EnumQueryType), type); switch (con) { case EnumQueryType.Customer: idName = "S_C_PlanReceipt.CustomerId"; break; case EnumQueryType.Department: idName = "S_C_PlanReceipt.ProductionUnitID"; break; case EnumQueryType.ManagerUser: idName = "S_C_PlanReceipt.FirstDutyManID"; break; default: return(WebApi.Error(EnumException.请求参数不合法)); } sql = @"SELECT Convert(decimal(18,2),isnull(SUM(PlanReceiptValue-ISNULL(FactReceiptValue,0)),0)/10000) AS ReceiptValue, ContractInfoID as ID,mc.Name as Name FROM S_C_PlanReceipt LEFT JOIN S_C_ManageContract mc ON S_C_PlanReceipt.ContractInfoID=mc.ID WHERE {0} = '{1}' and S_C_PlanReceipt.State in ('UnReceipt') AND {2} GROUP BY ContractInfoID,mc.Name ORDER BY ReceiptValue DESC"; return(WebApi.Success(db.Database.SqlQuery <ContractBll>(string.Format(sql, idName, id, condition)))); }
/// <summary> /// <para name="Receipt">收款总览</para> /// <para name="CurrentSurplusContract">当前合同余额</para> /// <para name="CurrentAccountReceivable">当前应收款</para> /// <para name="CurrentQuarterActualReceipt">当季实际收款</para> /// <para name="CurrentMonthActualReceipt">当月实际收款</para> /// <para name="CurrentWeekActualReceipt">本周实际到款</para> /// <para name="CurrentYearPlanReceipt">本年计划收款</para> /// <para name="CurrentYearAfterPlanReceipt">本年后计划收款</para> /// <para name="CurrentQuarterPlanReceipt">当季计划收款</para> /// <para name="CurrentQuarterAfterPlanReceipt">本季后计划收款</para> /// <para name="CurrentMonthPlanReceipt">本月计划收款</para> /// <para name="CurrentMonthAfterPlanReceipt">本月后计划收款</para> /// <para name="Contract">合同总览</para> /// <para name="CurrentUnSignContract">当年待签合同额</para> /// <para name="CurrentQuarterSignContract">当季已签合同</para> /// <para name="CurrentMonthSignContract">当月已签合同</para> /// <para name="CurrentWeekSignContract">本周已签合同额</para> /// </summary> /// <returns></returns> public static ResultDTO GetData() { try { var sql = @"--当季实际收款 (select isnull(Count(ContractInfoID),0) as ContractCount,Convert(decimal(18,2), isnull(SUM([Amount]),0)/10000) as TotalMoney,'CurrentQuarterActualReceipt' as Condition, '当季实际收款' as Title from dbo.S_C_Receipt S_C_Receipt WHERE BelongYear = datepart(year,getdate()) AND BelongQuarter = datepart(quarter,getdate())) union --当月实际收款 (select isnull(Count(ContractInfoID),0) as ContractCount,Convert(decimal(18,2), isnull(SUM([Amount]),0)/10000) as TotalMoney, 'CurrentMonthActualReceipt' as Condition,'当月实际收款' as Title from dbo.S_C_Receipt S_C_Receipt WHERE BelongYear = datepart(year,getdate()) AND BelongMonth = datepart(month,getdate())) union --本周实际到款 (select isnull(Count(ContractInfoID),0) as ContractCount, Convert(decimal(18,2),isnull(SUM([Amount]),0)/10000) as TotalMoney, 'CurrentWeekActualReceipt' as Condition,'本周实际到款' as Title from dbo.S_C_Receipt S_C_Receipt WHERE ArrivedDate between dateadd(wk, datediff(wk,0,getdate()), 0) and getdate()) union --当前应收款 --当一个合同有多个未到款的发票时合同个数不对 select Count(ID) as ContractCount, Convert(decimal(18,2),SUM(ISNULL(SumInvoiceValue,0)-ISNULL(SumReceiptValue,0))/10000) as TotalMoney, 'CurrentAccountReceivable' as Condition,'当前应收款' as Title from dbo.S_C_ManageContract S_C_ManageContract where IsSigned='Signed' union --当前合同余额 (select isnull(Count(ID),0) as ContractCount, Convert(decimal(18,2),(isnull(Sum(isnull(ContractRMBAmount,0)-isnull(SumReceiptValue,0)),0))/10000) as TotalMoney, 'CurrentSurplusContract' as Condition,'当前合同余额' as Title from dbo.S_C_ManageContract S_C_ManageContract where IsSigned='Signed') union --本年计划收款 (SELECT count(distinct ContractInfoID) as ContractCount, Convert(decimal(18,2),ISNULL(SUM(PlanReceiptValue-ISNULL(FactReceiptValue,0)),0)/10000) as TotalMoney, 'CurrentYearPlanReceipt' as Condition,'本年计划收款' as Title FROM dbo.S_C_PlanReceipt WHERE BelongYear <= datepart(year,getdate()) and State ='UnReceipt') union --本年后计划收款 (SELECT count(distinct ContractInfoID) as ContractCount, Convert(decimal(18,2),ISNULL(SUM(PlanReceiptValue-ISNULL(FactReceiptValue,0)),0)/10000) as TotalMoney, 'CurrentYearAfterPlanReceipt' as Condition,'本年后计划收款' as Title FROM dbo.S_C_PlanReceipt WHERE (BelongYear > datepart(year,getdate()) OR BelongYear = NULL) and State in ('UnReceipt')) union --当季计划收款 (SELECT count(distinct ContractInfoID) as ContractCount, Convert(decimal(18,2),ISNULL(SUM(PlanReceiptValue-ISNULL(FactReceiptValue,0)),0)/10000) as TotalMoney, 'CurrentQuarterPlanReceipt' as Condition,'当季计划收款' as Title FROM dbo.S_C_PlanReceipt WHERE BelongYear <= datepart(year,getdate()) AND BelongQuarter <= datepart(quarter,getdate()) and State in ('UnReceipt')) union --本季后计划收款 (SELECT count(distinct ContractInfoID) as ContractCount, Convert(decimal(18,2),ISNULL(SUM(PlanReceiptValue-ISNULL(FactReceiptValue,0)),0)/10000) as TotalMoney, 'CurrentQuarterAfterPlanReceipt' as Condition,'本季后计划收款' as Title FROM dbo.S_C_PlanReceipt WHERE ((BelongYear = datepart(year,getdate()) AND BelongQuarter > datepart(quarter,getdate())) OR(BelongYear > datepart(year,getdate())) OR (BelongYear = NULL OR BelongQuarter = NULL)) and state = 'UnReceipt') union --本月计划收款 (SELECT count(distinct ContractInfoID) as ContractCount, Convert(decimal(18,2),ISNULL(SUM(PlanReceiptValue-ISNULL(FactReceiptValue,0)),0)/10000) as TotalMoney, 'CurrentMonthPlanReceipt' as Condition,'本月计划收款' as Title FROM dbo.S_C_PlanReceipt WHERE BelongYear <= datepart(year,getdate()) AND BelongMonth <= datepart(month,getdate()) and State in ('UnReceipt')) union --本月后计划收款 (SELECT count(distinct ContractInfoID) as ContractCount, Convert(decimal(18,2),ISNULL(SUM(PlanReceiptValue-ISNULL(FactReceiptValue,0)),0)/10000) as TotalMoney, 'CurrentMonthAfterPlanReceipt' as Condition,'本月后计划收款' as Title FROM dbo.S_C_PlanReceipt WHERE ((BelongYear = datepart(year,getdate()) AND BelongMonth >datepart(month,getdate())) OR (BelongYear > datepart(year,getdate())) OR (BelongYear = NULL OR BelongQuarter = NULL OR BelongMonth = NULL)) and State in ('UnReceipt')) union --当前待签合同额 (select count(ID)as ContractCount,Convert(decimal(18,2),isnull(Sum(ContractRMBAmount),0)/10000) as TotalMoney, 'CurrentUnSignContract' as Condition,'当前待签合同额' as Title from S_C_ManageContract where IsSigned!='Signed' --and BelongYear=datepart(year,getdate()) ) union (select count(ID)as ContractCount,Convert(decimal(18,2),isnull(Sum(ContractRMBAmount),0)/10000) as TotalMoney,'CurrentQuarterSignContract' as Condition,'当季已签合同' as Title from S_C_ManageContract where IsSigned='Signed' and BelongYear=datepart(year,getdate()) and BelongQuarter=datepart(quarter,getdate())) union (select count(ID)as ContractCount,Convert(decimal(18,2),isnull(Sum(ContractRMBAmount),0)/10000) as TotalMoney,'CurrentMonthSignContract' as Condition,'当月已签合同' as Title from S_C_ManageContract where IsSigned='Signed' and BelongYear=datepart(year,getdate()) and BelongMonth=datepart(month,getdate())) union (select count(ID)as ContractCount,Convert(decimal(18,2),isnull(SUM(ContractRMBAmount),0)/10000) as TotalMoney,'CurrentWeekSignContract' as Condition,'本周已签合同额' as Title FROM dbo.S_C_ManageContract WHERE IsSigned = 'Signed' AND SignDate between dateadd(wk, datediff(wk,0,getdate()), 0) and getdate())"; var db = new MarketContext(); var result = db.Database.SqlQuery <StatisticsDTO>(sql) .ToDictionary <StatisticsDTO, string, object>(item => item.Condition, item => new Dictionary <string, object> { { "ContractCount", item.ContractCount }, { "TotalMoney", item.TotalMoney }, { "Title", item.Title } }); sql = @"(select (select Convert(decimal(18,2),isnull(Sum(Amount),0)/10000) from dbo.S_C_Receipt where BelongYear=datepart(year,getdate())) as ActualValue, (SELECT Convert(decimal(18,2),isnull(SUM(Amount),0)/10000) FROM dbo.S_C_Receipt WHERE ArrivedDate between dateadd(year,-1,DATEADD(yy,DATEDIFF(yy,0,getdate()), 0)) and dateadd(year,-1,getdate())) as RelativeValue,'Receipt' as Condition) union (select (select Convert(decimal(18,2),isnull(Sum(ContractRMBAmount),0)/10000) from S_C_ManageContract where IsSigned='Signed' and BelongYear=datepart(year,getdate())) as ActualValue, (SELECT Convert(decimal(18,2),isnull(SUM(ContractRMBAmount),0)/10000) FROM dbo.S_C_ManageContract WHERE IsSigned = 'Signed' AND SignDate between dateadd(year,-1,DATEADD(yy,DATEDIFF(yy,0,getdate()), 0)) and dateadd(year,-1,getdate())) as RelativeValue,'Contract' as Condition)"; var list = db.Database.SqlQuery <Statistics1DTO>(sql); int belongYear = DateTime.Now.Year; var indicatorDt = db.S_KPI_IndicatorCompany.FirstOrDefault(d => d.IndicatorType == "YearIndicator" && d.BelongYear == belongYear); var receiptGoalValue = indicatorDt != null && indicatorDt.ReceiptValue.HasValue ? indicatorDt.ReceiptValue.Value : 0; var contractGoalValue = indicatorDt != null && indicatorDt.ContractValue.HasValue ? indicatorDt.ContractValue.Value : 0; foreach (var item in list) { decimal goalValue = contractGoalValue; if (item.Condition == "Receipt") { goalValue = receiptGoalValue; } decimal goal = goalValue == 0 ? 100 : Math.Round(item.ActualValue / goalValue * 100, 2); if (item.RelativeValue == 0) { result.Add(item.Condition, new Dictionary <string, object> { { "ActualValue", item.ActualValue }, { "GoalValue", goalValue }, { "FinishRate", goal }, { "GrowthRate", "--" } }); } else { result.Add(item.Condition, new Dictionary <string, object> { { "ActualValue", item.ActualValue }, { "GoalValue", goalValue }, { "FinishRate", 100 }, { "GrowthRate", Math.Round(((item.ActualValue - item.RelativeValue) / item.RelativeValue) * 100, 2) } }); } } return(WebApi.Success(result)); } catch (Exception e) { return(WebApi.Error(e.Message)); } }