//获取购房星座分布 public string GetConstellations() { var sql = string.Format(@" select name,COUNT(1) as value from ( select case when (b.BirthDate >= '12-22' and b.BirthDate<='12-31') or b.BirthDate<='01-19' then '魔羯座' when b.BirthDate between '01-20' and '02-18' then '水瓶座' when b.BirthDate between '02-19' and '03-20' then '双鱼座' when b.BirthDate between '03-21' and '04-20' then '白羊座' when b.BirthDate between '04-21' and '05-20' then '金牛座' when b.BirthDate between '05-21' and '06-21' then '双子座' when b.BirthDate between '06-22' and '07-22' then '巨蟹座' when b.BirthDate between '07-23' and '08-22' then '狮子座' when b.BirthDate between '08-23' and '09-22' then '处女座' when b.BirthDate between '09-23' and '10-22' then '天秤座' when b.BirthDate between '10-23' and '11-21' then '天蝎座' when b.BirthDate between '11-22' and '12-21' then '射手座' end as name from s_Trade a inner join ( select TradeGUID,right(convert(varchar(10),BirthDate,120),5) as BirthDate from dbo.s_Trade2Cst where BirthDate is not null ) b on a.TradeGUID=b.TradeGUID where a.TradeStatus='激活' ) a group by name "); DataTable dt = SqlDataBaseManager.GetDataBase().ExecuteDataTable(sql); return(Dt2Json(dt)); }
//统计房间价格分布 public string GetRoomPrice() { var sql = string.Format(@" SELECT * FROM ( SELECT '≥20000 元' AS name,CASE WHEN sumcnt=0 THEN 0 ELSE CAST(CAST(cnt1 AS money)/CAST(sumcnt AS money) as decimal(5,2)) END AS value FROM ( SELECT COUNT(1) AS sumcnt FROM dbo.p_Room WHERE Price IS NOT NULL ) a LEFT JOIN ( SELECT COUNT(1) AS cnt1 FROM dbo.p_Room WHERE Price>=20000 AND Price IS NOT NULL ) b ON 1=1 UNION ALL SELECT '≥15000 元' AS name,CASE WHEN sumcnt=0 THEN 0 ELSE CAST(CAST(cnt1 AS money)/CAST(sumcnt AS money) as decimal(5,2)) END AS value FROM ( SELECT COUNT(1) AS sumcnt FROM dbo.p_Room WHERE Price IS NOT NULL ) a LEFT JOIN ( SELECT COUNT(1) AS cnt1 FROM dbo.p_Room WHERE Price<20000 AND Price>=15000 AND Price IS NOT NULL ) b ON 1=1 UNION ALL SELECT '≥10000 元' AS name,CASE WHEN sumcnt=0 THEN 0 ELSE CAST(CAST(cnt1 AS money)/CAST(sumcnt AS money) as decimal(5,2)) END AS value FROM ( SELECT COUNT(1) AS sumcnt FROM dbo.p_Room WHERE Price IS NOT NULL ) a LEFT JOIN ( SELECT COUNT(1) AS cnt1 FROM dbo.p_Room WHERE Price<15000 AND Price>=10000 AND Price IS NOT NULL ) b ON 1=1 UNION ALL SELECT '10000元' AS name,1.00-SUM(value) AS value FROM ( SELECT '≥20000 元' AS name,CASE WHEN sumcnt=0 THEN 0 ELSE CAST(CAST(cnt1 AS money)/CAST(sumcnt AS money) as decimal(5,2)) END AS value FROM ( SELECT COUNT(1) AS sumcnt FROM dbo.p_Room WHERE Price IS NOT NULL ) a LEFT JOIN ( SELECT COUNT(1) AS cnt1 FROM dbo.p_Room WHERE Price>=20000 AND Price IS NOT NULL ) b ON 1=1 UNION ALL SELECT '≥15000 元' AS name,CASE WHEN sumcnt=0 THEN 0 ELSE CAST(CAST(cnt1 AS money)/CAST(sumcnt AS money) as decimal(5,2)) END AS value FROM ( SELECT COUNT(1) AS sumcnt FROM dbo.p_Room WHERE Price IS NOT NULL ) a LEFT JOIN ( SELECT COUNT(1) AS cnt1 FROM dbo.p_Room WHERE Price<20000 AND Price>=15000 AND Price IS NOT NULL ) b ON 1=1 UNION ALL SELECT '≥10000 元' AS name,CASE WHEN sumcnt=0 THEN 0 ELSE CAST(CAST(cnt1 AS money)/CAST(sumcnt AS money) as decimal(5,2)) END AS value FROM ( SELECT COUNT(1) AS sumcnt FROM dbo.p_Room WHERE Price IS NOT NULL ) a LEFT JOIN ( SELECT COUNT(1) AS cnt1 FROM dbo.p_Room WHERE Price<15000 AND Price>=10000 AND Price IS NOT NULL ) b ON 1=1 ) a ) a ORDER BY a.value "); DataTable dt = SqlDataBaseManager.GetDataBase().ExecuteDataTable(sql); return(Dt2Json(dt)); }
//获取各月份成交量 public string GetDiffMonthDealCnt() { var sql = string.Format(@" SELECT CONVERT(VARCHAR(200),MONTH(QSDate))+'月' AS name,COUNT(1) AS value FROM dbo.s_Contract WHERE Status='激活' GROUP BY MONTH(QSDate) ORDER BY MONTH(QSDate) "); DataTable dt = SqlDataBaseManager.GetDataBase().ExecuteDataTable(sql); return(Dt2Json(dt)); }
//获取项目落地的城市 public string GetAchievedProjectCount() { var sql = string.Format(@" SELECT c.city AS name ,COUNT(c.city) AS value FROM dbo.p_Project AS p INNER JOIN p_City AS c ON p.ProjAddress LIKE '%'+ REPLACE(c.city,'市','') +'%' or p.ProjName like '%'+ REPLACE(c.city,'市','') +'%' or p.ProjInfo like '%'+ REPLACE(c.city,'市','') +'%' or p.TargetEffigy like '%'+ REPLACE(c.city,'市','') +'%' GROUP BY c.city "); DataTable dt = SqlDataBaseManager.GetDataBase().ExecuteDataTable(sql); return(Dt2Json(dt)); }
//获取各产品类型的成交量 public string GetDiffProductCnt() { var sql = string.Format(@" SELECT c.BProductTypeName AS name,COUNT(1) AS value FROM dbo.s_Contract a LEFT JOIN dbo.p_Room b ON a.RoomGUID=b.RoomGUID LEFT JOIN dbo.p_BuildProductType c ON b.BProductTypeCode = c.BProductTypeCode WHERE a.Status='激活' GROUP BY c.BProductTypeName "); DataTable dt = SqlDataBaseManager.GetDataBase().ExecuteDataTable(sql); return(Dt2Json(dt)); }
//获取各媒体带来的接电接访 public string GetMediaOppCnt() { var sql = string.Format(@" SELECT TOP 5 name,value FROM ( SELECT MainMediaName AS name,COUNT(1) AS value FROM dbo.s_Opportunity WHERE ISNULL(MainMediaName,'')<>'' GROUP BY MainMediaName ) a ORDER BY value DESC "); DataTable dt = SqlDataBaseManager.GetDataBase().ExecuteDataTable(sql); return(Dt2Json(dt)); }
//获取销量排名柱状图 public string GetSaleRanking() { var sql = string.Format(@" SELECT TOP 3 ProjName as name,cnt as value FROM ( SELECT b.ProjName,COUNT(a.ContractGUID) AS cnt FROM dbo.s_Contract a LEFT JOIN dbo.p_Project b ON a.ProjGUID=b.ProjGUID WHERE a.Status='激活' GROUP BY b.ProjName ) a ORDER BY cnt DESC "); DataTable dt = SqlDataBaseManager.GetDataBase().ExecuteDataTable(sql); return(Dt2Json(dt)); }
//统计整个公司平均去化率 public string GetRemovalRate() { var sql = string.Format(@" SELECT '' as name,CASE WHEN SUM(a.cnt)=0 THEN 0 ELSE CAST(CAST(SUM(b.cnt) as money) *100/CAST(SUM(a.cnt) as money) as decimal(5,2) ) END AS value FROM ( SELECT COUNT(1) AS cnt FROM dbo.p_Room a ) a LEFT JOIN ( SELECT COUNT(1) AS cnt FROM dbo.p_Room a INNER JOIN dbo.s_Contract b ON a.RoomGUID=b.RoomGUID WHERE b.Status='激活' ) b ON 1=1 "); DataTable dt = SqlDataBaseManager.GetDataBase().ExecuteDataTable(sql); return(Dt2Json(dt)); }
//获取不同年龄段的购买力 public string GetDiffAgeBuy() { var sql = string.Format(@" SELECT age AS name,COUNT(1) AS value FROM ( SELECT CASE WHEN (YEAR(a.BirthDate)-1920)<80 THEN CONVERT(VARCHAR(10),(YEAR(a.BirthDate)-1900)/10)+'0后' ELSE CONVERT(VARCHAR(10),(YEAR(a.BirthDate)-2000)/10)+'0后' END AS age, CASE WHEN YEAR(a.BirthDate)%100>=20 THEN YEAR(a.BirthDate)%100/10 ELSE 100 END AS ordernum FROM dbo.s_Trade2Cst a LEFT JOIN dbo.s_Contract b ON a.TradeGUID = b.TradeGUID WHERE b.Status='激活' AND a.BirthDate IS NOT NULL ) a GROUP BY age,ordernum ORDER BY ordernum "); DataTable dt = SqlDataBaseManager.GetDataBase().ExecuteDataTable(sql); return(Dt2Json(dt)); }
//获取跟进次数排名最前的5个客户及所属公司 public string GetMostCstGjjl() { var sql = string.Format(@" SELECT TOP 5 BUName AS name,cnt AS value FROM ( SELECT b.cstguid,d.BUName,a.OppGUID,COUNT(a.GjjlGUID) AS cnt FROM dbo.s_Opp2Gjjl a LEFT JOIN dbo.[s_Opp2Cst ] b ON a.OppGUID=b.OppGUID LEFT JOIN dbo.p_CstAttach c ON b.CstGUID = c.CstGUID LEFT JOIN dbo.myBusinessUnit d ON d.BUGUID=c.BUGUID WHERE GjDate BETWEEN '2013-01-01' AND '2013-12-31' GROUP BY b.cstguid,d.BUName,a.OppGUID ) a ORDER BY cnt DESC "); DataTable dt = SqlDataBaseManager.GetDataBase().ExecuteDataTable(sql); return(Dt2Json(dt)); }
//获取最大购买年龄和最小购买年龄 public string GetTwoDiffAge() { var sql = string.Format(@" SELECT YEAR(GETDATE())- YEAR(MAX(BirthDate)) AS eldest,YEAR(GETDATE())-YEAR(MIN(BirthDate)) AS oldest FROM dbo.s_Trade2Cst a LEFT JOIN dbo.s_Contract b ON a.TradeGUID = b.TradeGUID WHERE b.Status='激活' AND a.BirthDate IS NOT NULL AND YEAR(BirthDate)<>1900 "); DataTable dt = SqlDataBaseManager.GetDataBase().ExecuteDataTable(sql); if (dt != null) { if (dt.Rows.Count > 0) { return("[{name:'eldest',value:" + dt.Rows[0][0] + "},{name:'oldest',value:" + dt.Rows[0][1] + "}]"); } } return(""); }