public ActionResult Detail(string userName) { UserBusinessOperation businessOperation = new UserBusinessOperation(); UserAccountingDTO itemOut = null; BaseOutput dbitem = businessOperation.GetUserAccountingInfo(userName, out itemOut); return(View(itemOut)); }
private UserAccountingDTO GetUserIncome(string userName) { UserAccountingDTO pricePointAndPaymentDTO = null; try { var query = @" select UserName, sum(IncomeAbility) as Income from ( select UserID, YEAR, UserName, Type, TypeDesc, avg( IncomeAbility) as IncomeAbility from ( SELECT UserID, year, month,usr.UserName, Type, CASE WHEN Type=1 THEN 'SMS' WHEN Type=2 THEN 'CALL' WHEN Type=3 THEN 'NetConsume' WHEN Type=4 THEN 'AppConsume' WHEN Type=5 THEN 'AzeriCard' WHEN Type=6 THEN 'Bakcell' END as TypeDesc ,sum(IncomeAbility) As IncomeAbility from [tbl_UserIncomeAbility] uia join tbl_User usr on uia.UserID=usr.ID where uia.Status=1 and usr.Status=1 group by UserID,year,month, usr.UserName, uia.type) temp group by UserID,year,UserName, type,TypeDesc ) temp2 where UserName=@P_UserName group by UserName " ; using (var connection = new SqlConnection(ConnectionStrings.ConnectionString)) { connection.Open(); using (var command = new SqlCommand(query, connection)) { command.Parameters.AddWithValue("@P_UserName", userName.GetStringOrEmptyData()); var reader = command.ExecuteReader(); while (reader.Read()) { pricePointAndPaymentDTO = new UserAccountingDTO() { TotalIncome = reader.GetDecimalOrDefaultValue(1) }; } } connection.Close(); return(pricePointAndPaymentDTO); } } catch (Exception ex) { throw; } }
public IHttpActionResult GetUserAccountingInfo(string userName) { UserBusinessOperation businessOperation = new UserBusinessOperation(); UserAccountingDTO itemOut = null; BaseOutput dbitem = businessOperation.GetUserAccountingInfo(userName, out itemOut); if (dbitem.ResultCode == 1) { return(Ok(itemOut)); } else { return(Content(HttpStatusCode.BadRequest, dbitem)); } }
public BaseOutput GetUserAccountingInfo(string username, out UserAccountingDTO itemOut) { BaseOutput baseOutput; itemOut = null; try { TotalPointAndPriceRepository pointAndPriceRepository = new TotalPointAndPriceRepository(); var userAccountingInfo = pointAndPriceRepository.GetUserAccountingInfo(username); itemOut = userAccountingInfo; return(baseOutput = new BaseOutput(true, BOResultTypes.Success.GetHashCode(), BOBaseOutputResponse.SuccessResponse, "")); } catch (Exception ex) { return(baseOutput = new BaseOutput(false, BOResultTypes.Danger.GetHashCode(), BOBaseOutputResponse.DangerResponse, ex.Message)); } }
public UserAccountingDTO GetUserAccountingInfo(string userName) { UserAccountingDTO userAccountingDTO = new UserAccountingDTO(); UserAccountingDTO priceAndPointInfo = GetUserPriceAndPoint(userName); if (priceAndPointInfo != null) { userAccountingDTO.TotalPoint = priceAndPointInfo.TotalPoint; userAccountingDTO.TotalPrice = priceAndPointInfo.TotalPrice; } UserAccountingDTO incomeInfo = GetUserIncome(userName); if (incomeInfo != null) { userAccountingDTO.TotalIncome = incomeInfo.TotalIncome; } userAccountingDTO.ExpenseAbility = userAccountingDTO.TotalIncome - userAccountingDTO.TotalPrice; return(userAccountingDTO); }
private UserAccountingDTO GetUserPriceAndPoint(string userName) { UserAccountingDTO pricePointAndPaymentDTO = null; try { var query = @"select avg(TotalPoint) as avg_point, avg(TotalPrice) as avg_price from ( select month, sum(point) TotalPoint,Sum (price) as TotalPrice from [dbo].[tbl_UserPointAndPrice] upp,tbl_User usr where usr.ID=upp.UserID and usr.UserName=@P_UserName group by month) as temp; "; using (var connection = new SqlConnection(ConnectionStrings.ConnectionString)) { connection.Open(); using (var command = new SqlCommand(query, connection)) { command.Parameters.AddWithValue("@P_UserName", userName.GetStringOrEmptyData()); var reader = command.ExecuteReader(); while (reader.Read()) { pricePointAndPaymentDTO = new UserAccountingDTO() { TotalPoint = reader.GetDecimalOrDefaultValue(0), TotalPrice = reader.GetDecimalOrDefaultValue(1) }; } } connection.Close(); return(pricePointAndPaymentDTO); } } catch (Exception ex) { throw; } }