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