APP查询用户购买套餐使用量和套餐总数

查询用户购买套餐使用量和套餐总数，根据查询用户一共购买了套餐发布职位数量的总数，查询用户使用多少发布职位次数

查询用户购买套餐下载简历总数，查询用户使用了多少次下载简历，查询用户一共购买了套餐短信数量的总数，查询用户使

用了多少次发送短信，查询用户购买招聘会套餐次数的总数，查询用户使用了多少次招聘会

后台查询用户手机套餐次数、招聘会次数、发布职位次数、下载简历次数

 /// <summary>
        /// 查询用户手机套餐次数、招聘会次数、发布职位次数、下载简历次数
        /// </summary>
        /// <returns></returns>
        public ActionResult View_SelectUserPurchaseSetmeal(int UserID)
        {
            DateTime NowTime = DateTime.Now;

            //执行新增存储过程，得到返回结果
            List<Dictionary<string, object>> ListReturn = new List<Dictionary<string, object>>();
            try
            {
                ListReturn = DALMethod.ListChaXunShuJu("LiJin_API_Manage", new Dictionary<string, object> {
                { "@Type","SelectUserPurchaseSetmeal" },{"@UserID",UserID }, {"@NowTime",NowTime } });

                RS.code = 200;
                RS.data = ListReturn;
                RS.message = "成功！！";
            }
            catch (Exception e)
            {

                RS.code = 500;
                RS.data = ListReturn;
                RS.message = "失败！！";
            }

            return Json(RS, JsonRequestBehavior.AllowGet);


        }

这是sql的查询数据

/*查询用户购买套餐使用量和套餐总数*/
  	IF @Type = 'SelectUserPurchaseSetmeal'
	BEGIN
		/*查询用户一共购买了套餐发布职位数量的总数*/
		SET @ReleasePositionNum=(( SELECT  CASE  WHEN SUM( BS_SetMeal.ReleasePositionNum) IS NULL THEN 0 ELSE SUM( BS_SetMeal.ReleasePositionNum) END 
		FROM      BS_SetMealUser LEFT OUTER JOIN
		BS_SetMeal ON BS_SetMealUser.SetMealID = BS_SetMeal.SetMealID WHERE BS_SetMealUser.UserID=@UserID AND BS_SetMealUser.IsDelete=0 AND BS_SetMealUser.ExpireTime>=@NowTime)+(SELECT  CASE  WHEN SUM( BS_SetMeal.ReleasePositionNum) IS NULL THEN 0 ELSE SUM( BS_SetMeal.ReleasePositionNum) END 
		FROM      BS_SetMealUser INNER JOIN
		BS_ShareGratisMeal ON BS_SetMealUser.ShareGratisMealID = BS_ShareGratisMeal.ShareGratisMealID INNER JOIN
		BS_SetMeal ON BS_ShareGratisMeal.SetMealID = BS_SetMeal.SetMealID WHERE BS_SetMealUser.UserID=@UserID AND BS_SetMealUser.IsDelete=0 AND BS_SetMealUser.ExpireTime>=@NowTime) )

		/*查询用户使用多少发布职位次数*/
		SET @PositionUsageNum=(
		(SELECT CASE  WHEN SUM(  BS_SetMealUse.PositionUsageNum) IS NULL THEN 0 ELSE SUM( BS_SetMealUse.PositionUsageNum) END 
		FROM      BS_SetMealUser INNER JOIN
		BS_SetMealUse ON BS_SetMealUser.SetMealUserID = BS_SetMealUse.SetMealUserID
		WHERE BS_SetMealUser.UserID=@UserID AND BS_SetMealUser.ExpireTime>=@NowTime)) 
		/*查询用户购买套餐下载简历总数*/
		SET @DownloadResumeNum=((SELECT    CASE  WHEN SUM( BS_SetMeal.DownloadResumeNum) IS NULL THEN 0 ELSE SUM( BS_SetMeal.DownloadResumeNum) END 
		FROM      BS_SetMealUser INNER JOIN
		BS_SetMeal ON BS_SetMealUser.SetMealID = BS_SetMeal.SetMealID 
		WHERE BS_SetMealUser.UserID=@UserID AND BS_SetMealUser.IsDelete=0 AND BS_SetMealUser.ExpireTime>=@NowTime)+(SELECT  CASE  WHEN SUM( BS_SetMeal.DownloadResumeNum) IS NULL THEN 0 ELSE SUM( BS_SetMeal.DownloadResumeNum) END 
		FROM      BS_SetMealUser INNER JOIN
		BS_ShareGratisMeal ON BS_SetMealUser.ShareGratisMealID = BS_ShareGratisMeal.ShareGratisMealID INNER JOIN
		BS_SetMeal ON BS_ShareGratisMeal.SetMealID = BS_SetMeal.SetMealID WHERE BS_SetMealUser.UserID=@UserID AND BS_SetMealUser.IsDelete=0 AND BS_SetMealUser.ExpireTime>=@NowTime))
		/*查询用户使用了多少次下载简历*/
		SET @DownloadResumeUsageNum=(
		(SELECT CASE  WHEN SUM(  BS_SetMealUse.DownloadResumeUsageNum) IS NULL THEN 0 ELSE SUM( BS_SetMealUse.DownloadResumeUsageNum) END 
		FROM      BS_SetMealUser INNER JOIN
		BS_SetMealUse ON BS_SetMealUser.SetMealUserID = BS_SetMealUse.SetMealUserID
		WHERE BS_SetMealUser.UserID=@UserID AND BS_SetMealUser.ExpireTime>=@NowTime)) 

		/*查询用户一共购买了套餐短信数量的总数*/
		SET @NewsNum=(( SELECT  CASE  WHEN SUM( BS_SetMeal.PhoneNewsNum) IS NULL THEN 0 ELSE SUM( BS_SetMeal.PhoneNewsNum) END 
		FROM      BS_SetMealUser LEFT OUTER JOIN
		BS_SetMeal ON BS_SetMealUser.SetMealID = BS_SetMeal.SetMealID WHERE BS_SetMealUser.UserID=@UserID AND BS_SetMealUser.IsDelete=0 AND BS_SetMealUser.ExpireTime>=@NowTime)+(SELECT  CASE  WHEN SUM( BS_SetMeal.PhoneNewsNum) IS NULL THEN 0 ELSE SUM( BS_SetMeal.PhoneNewsNum) END 
		FROM      BS_SetMealUser INNER JOIN
		BS_ShareGratisMeal ON BS_SetMealUser.ShareGratisMealID = BS_ShareGratisMeal.ShareGratisMealID INNER JOIN
		BS_SetMeal ON BS_ShareGratisMeal.SetMealID = BS_SetMeal.SetMealID WHERE BS_SetMealUser.UserID=@UserID AND BS_SetMealUser.IsDelete=0 AND BS_SetMealUser.ExpireTime>=@NowTime) +(SELECT CASE  WHEN SUM(  MM_MobileNewsMeal.NewsNum)IS NULL THEN 0 ELSE SUM( MM_MobileNewsMeal.NewsNum) END 
		FROM      BS_SetMealUser INNER JOIN
		MM_MobileNewsMeal ON BS_SetMealUser.MobileNewsMealID = MM_MobileNewsMeal.MobileNewsMealID 
		WHERE BS_SetMealUser.UserID=@UserID AND BS_SetMealUser.IsDelete=0 AND BS_SetMealUser.ExpireTime>=@NowTime
		) )
		/*查询用户使用了多少次发送短信*/
		SET @NewsUseNum=(
		(SELECT CASE  WHEN SUM(  BS_SetMealUse.NewsUseNum) IS NULL THEN 0 ELSE SUM( BS_SetMealUse.NewsUseNum) END 
		FROM      BS_SetMealUser INNER JOIN
		BS_SetMealUse ON BS_SetMealUser.SetMealUserID = BS_SetMealUse.SetMealUserID
		WHERE BS_SetMealUser.UserID=@UserID AND BS_SetMealUser.ExpireTime>=@NowTime)) 

		/*查询用户购买招聘会套餐次数的总数*/
		SET @JobFairNumber=  (SELECT   CASE  WHEN SUM(  BS_JobFairMeal.Number) IS NULL THEN 0 ELSE SUM( BS_JobFairMeal.Number) END 
		FROM      BS_SetMealUser INNER JOIN
		BS_JobFairMeal ON BS_SetMealUser.JobFairMealID = BS_JobFairMeal.JobFairMealID  WHERE BS_SetMealUser.UserID=@UserID AND BS_SetMealUser.IsDelete=0 AND BS_SetMealUser.ExpireTime>=@NowTime ) +(SELECT  CASE  WHEN SUM( BS_JobFairMeal.Number)IS NULL THEN 0 ELSE SUM( BS_JobFairMeal.Number) END 
		FROM      BS_JobFairMeal RIGHT OUTER JOIN
		BS_ShareGratisMeal ON BS_JobFairMeal.JobFairMealID = BS_ShareGratisMeal.JobFairMealID RIGHT OUTER JOIN
		BS_SetMealUser ON BS_ShareGratisMeal.ShareGratisMealID = BS_SetMealUser.ShareGratisMealID WHERE BS_SetMealUser.UserID=@UserID AND BS_SetMealUser.IsDelete=0 AND BS_SetMealUser.ExpireTime>=@NowTime)


		/*查询用户使用了多少次招聘会*/
		SET @JobFairMealNum=(
		(SELECT CASE  WHEN SUM(  BS_SetMealUse.JobFairMealNum) IS NULL THEN 0 ELSE SUM( BS_SetMealUse.JobFairMealNum) END 
		FROM      BS_SetMealUser INNER JOIN
		BS_SetMealUse ON BS_SetMealUser.SetMealUserID = BS_SetMealUse.SetMealUserID
		WHERE BS_SetMealUser.UserID=@UserID AND BS_SetMealUser.IsDelete=0 AND BS_SetMealUser.ExpireTime>=@NowTime)) 


		SELECT PositionUsageNum=  @PositionUsageNum,

		ReleasePositionNum=     @ReleasePositionNum,
		( @ReleasePositionNum-@PositionUsageNum)as SurplusPositionUsageNum,
		DownloadResumeNum= @DownloadResumeNum,
		DownloadResumeUsageNum= @DownloadResumeUsageNum,
		(@DownloadResumeNum-@DownloadResumeUsageNum)AS SurplusDownloadResumeUsageNum,
		NewsNum=@NewsNum,
		NewsUseNum= @NewsUseNum,
		(@NewsNum-@NewsUseNum)AS SurplusNewsUseNum,
		JobFairNumber=@JobFairNumber,
		(@JobFairNumber-	@JobFairMealNum)AS SurplusJobFairMealNum           
					
	END  

用户手机套餐次数、招聘会次数、发布职位次数、下载简历次数根据数据库的循环查询数据，其实没什么可说的，看就懂了，只是省略了后台编写代码的时间，这样简单易懂不用这么麻烦去后台写循环判断，今天的分享就到这了