public List<UserSales> GetUserSalesStatistic(DateTime startDate, DateTime endDate, int companyId, int
userId)
{
List<UserSales> result = new List<UserSales>();
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputDateTimeParameter("@start_date", startDate),
SqlUtilities.GenerateInputDateTimeParameter("@end_date", endDate),
SqlUtilities.GenerateInputIntParameter("@company_id", companyId)
};
string sqlParam = "";
DateTime minTime = new DateTime(1999, 1, 1);
if (startDate > minTime && endDate > minTime)
{
sqlParam += " AND create_time BETWEEN @start_date AND @end_date";
}
else if (startDate > minTime && endDate <= minTime)
{
sqlParam += " AND create_time >= @start_date ";
}
else if (startDate <= minTime && endDate > minTime)
{
sqlParam += " AND create_time <= @end_date";
}
if (companyId > 0)
{
sqlParam += " AND company_id = @company_id";
}
if (userId > 0)
{
sqlParam += " AND user_id = @user_id";
}
string sql = "SELECT user_id, SUM(costs) AS money, SUM(costs-self_costs) AS profit FROM orders WHERE is_delete = 0 AND status IN(4,5) " + sqlParam + " GROUP BY user_id ORDER BY money DESC";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
UserSales us = new UserSales();
User user = new UserDAL().GetUserById(dr.GetInt32(0));
us.User = user;
us.Money = dr.GetDecimal(1);
us.Profit = dr.GetDecimal(2);
result.Add(us);
}
}
return result;
}
public List<UserSales> GetUserAssessStatistic(DateTime startDate, DateTime endDate, int companyId, int clientId, string carrierEncode, int userId)
{
List<UserSales> result = new List<UserSales>();
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputDateTimeParameter("@start_date", startDate),
SqlUtilities.GenerateInputDateTimeParameter("@end_date", endDate),
SqlUtilities.GenerateInputIntParameter("@client_id", clientId),
SqlUtilities.GenerateInputIntParameter("@company_id", companyId),
SqlUtilities.GenerateInputNVarcharParameter("@carrier_encode", 50, carrierEncode),
SqlUtilities.GenerateInputIntParameter("@user_id", userId)
};
string sqlParam = "";
DateTime minTime = new DateTime(1999, 1, 1);
if (startDate > minTime && endDate > minTime)
{
sqlParam += " AND O.create_time BETWEEN @start_date AND @end_date";
}
else if (startDate > minTime && endDate <= minTime)
{
sqlParam += " AND O.create_time >= @start_date ";
}
else if (startDate <= minTime && endDate > minTime)
{
sqlParam += " AND O.create_time <= @end_date";
}
if (companyId > 0)
{
sqlParam += " AND O.company_id = @company_id";
}
if (clientId >= 0)
{
sqlParam += " AND O.client_id = @client_id";
}
if (userId > 0)
{
sqlParam += " AND O.user_id = @user_id";
}
if (!string.IsNullOrEmpty(carrierEncode))
{
sqlParam += " AND OD.carrier_encode = @carrier_encode";
}
string sql = "SELECT O.user_id, SUM(total_costs) AS costs FROM orders AS O INNER JOIN order_details AS OD ON O.id = OD.order_id WHERE O.is_delete = 0 AND OD.is_delete = 0 AND O.status IN(4,5) " + sqlParam + " GROUP BY O.user_id";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
UserSales us = new UserSales();
User user = new UserDAL().GetUserById(dr.GetInt32(0));
us.User = user;
us.Money = dr.GetDecimal(1);
result.Add(us);
}
}
return result;
}