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