public List <SMSReportDTO> SW_GetSMSReportShorParsetMsjs(int userID, string userName, int year)
{
var result = new List <SMSReportDTO>();
StringBuilder allQuery = new StringBuilder();
var query = @"select * from [GetMessageReportShortParseSMS](@P_USERID,@P_year) ";
allQuery.Append(query);
using (var connection = new SqlConnection(ConnectionStrings.ConnectionString))
{
connection.Open();
using (var command = new SqlCommand(allQuery.ToString(), connection))
{
SqlParameter puserID = new SqlParameter("@P_USERID", SqlDbType.Int);
puserID.Value = userID;
command.Parameters.Add(puserID);
SqlParameter pyear = new SqlParameter("@P_year", SqlDbType.Int);
pyear.Value = year;
command.Parameters.Add(pyear);
//command.Parameters.AddWithValue("@P_USERID", userID);
//command.Parameters.AddWithValue("@P_year", year);
var reader = command.ExecuteReader();
while (reader.Read())
{
SMSReportDTO sMSReportDTO = new SMSReportDTO()
{
INOUT_EVType = reader.GetInt32OrDefaultValue(0),
Name = reader.GetStringOrEmpty(1),
OperationType = reader.GetStringOrEmpty(2),
Year = reader.GetInt32OrDefaultValue(3),
January = reader.GetInt32OrNull(4),
February = reader.GetInt32OrNull(5),
March = reader.GetInt32OrNull(6),
April = reader.GetInt32OrNull(7),
May = reader.GetInt32OrNull(8),
June = reader.GetInt32OrNull(9),
July = reader.GetInt32OrNull(10),
August = reader.GetInt32OrNull(11),
September = reader.GetInt32OrNull(12),
October = reader.GetInt32OrNull(13),
November = reader.GetInt32OrNull(14),
December = reader.GetInt32OrNull(15),
};
sMSReportDTO.Average = GetAverageShort(sMSReportDTO);
sMSReportDTO.AveragePrice = sMSReportDTO.Average;
result.Add(sMSReportDTO);
}
}
connection.Close();
}
return(result);
}
public List <SMSReportDTO> SW_GetSMSReports(int userID, string userName, int year)
{
var result = new List <SMSReportDTO>();
StringBuilder allQuery = new StringBuilder();
var query = @"select * from [GetMessageReport](@P_USERID,@P_year) ";
allQuery.Append(query);
using (var connection = new SqlConnection(ConnectionStrings.ConnectionString))
{
connection.Open();
using (var command = new SqlCommand(allQuery.ToString(), connection))
{
SqlParameter puserID = new SqlParameter("@P_USERID", SqlDbType.Int);
puserID.Value = userID;
command.Parameters.Add(puserID);
SqlParameter pyear = new SqlParameter("@P_year", SqlDbType.Int);
pyear.Value = year;
command.Parameters.Add(pyear);
var reader = command.ExecuteReader();
while (reader.Read())
{
SMSReportDTO sMSReportDTO = new SMSReportDTO()
{
INOUT_EVType = reader.GetInt32OrDefaultValue(0),
Name = reader.GetStringOrEmpty(1),
OperationType = reader.GetStringOrEmpty(2),
Year = reader.GetInt32OrDefaultValue(3),
January = reader.GetDecimalOrDefaultValue2(4),
February = reader.GetDecimalOrDefaultValue2(5),
March = reader.GetDecimalOrDefaultValue2(6),
April = reader.GetDecimalOrDefaultValue2(7),
May = reader.GetDecimalOrDefaultValue2(8),
June = reader.GetDecimalOrDefaultValue2(9),
July = reader.GetDecimalOrDefaultValue2(10),
August = reader.GetDecimalOrDefaultValue2(11),
September = reader.GetDecimalOrDefaultValue2(12),
October = reader.GetDecimalOrDefaultValue2(13),
November = reader.GetDecimalOrDefaultValue2(14),
December = reader.GetDecimalOrDefaultValue2(15),
};
sMSReportDTO.Average = GetAverage(sMSReportDTO);
string prefix = GetNumberPrefix(userName);
tbl_OperatorInformation operatorInformation = GetAverageCost(prefix, sMSReportDTO.INOUT_EVType);
if (operatorInformation != null)
{
if (operatorInformation.Price == null)
{
sMSReportDTO.AveragePrice = null;
}
else
{
sMSReportDTO.AveragePrice = sMSReportDTO.Average * (decimal)operatorInformation.Price;;
}
if (operatorInformation.Point == null)
{
sMSReportDTO.AveragePoint = null;
}
else
{
sMSReportDTO.AveragePoint = sMSReportDTO.Average * (decimal)operatorInformation.Point;;
}
}
result.Add(sMSReportDTO);
}
}
connection.Close();
}
return(result);
}
public decimal GetAverageShort(SMSReportDTO item)
{
int k = 0;
decimal averageTotal = 0;
if (item.January > 0)
{
k++;
averageTotal = averageTotal + (int)item.January;
}
if (item.February > 0)
{
k++;
averageTotal = averageTotal + (int)item.February;
}
if (item.March > 0)
{
k++;
averageTotal = averageTotal + (int)item.March;
}
if (item.April > 0)
{
k++;
averageTotal = averageTotal + (int)item.April;
}
if (item.May > 0)
{
k++;
averageTotal = averageTotal + (int)item.May;
}
if (item.June > 0)
{
k++;
averageTotal = averageTotal + (int)item.June;
}
if (item.July > 0)
{
k++;
averageTotal = averageTotal + (int)item.July;
}
if (item.August > 0)
{
k++;
averageTotal = averageTotal + (int)item.August;
}
if (item.September > 0)
{
k++;
averageTotal = averageTotal + (int)item.September;
}
if (item.October > 0)
{
k++;
averageTotal = averageTotal + (int)item.October;
}
if (item.November > 0)
{
k++;
averageTotal = averageTotal + (int)item.November;
}
if (item.December > 0)
{
k++;
averageTotal = averageTotal + (int)item.December;
}
decimal average = averageTotal / (k == 0 ? 1 : k);
return(average);
}
public decimal GetAverage(SMSReportDTO item)
{
int k = 0;
decimal averageTotal = 0;
if (item.January.HasValue)
{
k++;
averageTotal = averageTotal + (int)item.January;
}
if (item.February.HasValue)
{
k++;
averageTotal = averageTotal + (int)item.February;
}
if (item.March.HasValue)
{
k++;
averageTotal = averageTotal + (int)item.March;
}
if (item.April.HasValue)
{
k++;
averageTotal = averageTotal + (int)item.April;
}
if (item.May.HasValue)
{
k++;
averageTotal = averageTotal + (int)item.May;
}
if (item.June.HasValue)
{
k++;
averageTotal = averageTotal + (int)item.June;
}
if (item.July.HasValue)
{
k++;
averageTotal = averageTotal + (int)item.July;
}
if (item.August.HasValue)
{
k++;
averageTotal = averageTotal + (int)item.August;
}
if (item.September.HasValue)
{
k++;
averageTotal = averageTotal + (int)item.September;
}
if (item.October.HasValue)
{
k++;
averageTotal = averageTotal + (int)item.October;
}
if (item.November.HasValue)
{
k++;
averageTotal = averageTotal + (int)item.November;
}
if (item.December.HasValue)
{
k++;
averageTotal = averageTotal + (int)item.December;
}
decimal average = averageTotal / (k == 0?1:k);
return(average);
}
