public IEnumerable <ServiceAction> GetActions(Int64 user_id, DateTime dateBegin, DateTime dateEnd)
{
var date1 = new DateTime(dateBegin.Year, dateBegin.Month, dateBegin.Day, 0, 0, 0);
var date2 = new DateTime(dateEnd.Year, dateEnd.Month, dateEnd.Day, 23, 59, 59);
var actions = new List <ServiceAction>();
SqlConnection connection = SqlProvider.CreateConnection();
SqlCommand cmd = null;
SqlDataReader reader = null;
try {
cmd = connection.CreateCommand();
cmd.CommandText = @"
select a.id, a.dt, a.customer, a.service_id, a.type_id, a.user_id, s.org_id, a.customer_type_id
from Actions a
join Services s on s.id = a.service_id
where a.dt between @date1 and @date2
and a.is_deleted = 0 ";
cmd.Parameters.AddWithValue("date1", date1);
cmd.Parameters.AddWithValue("date2", date2);
if (user_id >= 0)
{
cmd.CommandText += " and a.user_id = @user_id";
cmd.Parameters.AddWithValue("user_id", user_id);
}
cmd.CommandText += " order by a.dt desc, a.id desc";
reader = cmd.ExecuteReader();
while (reader.Read())
{
actions.Add(new ServiceAction {
Id = Convert.ToInt64(reader["id"]),
Service = ServiceService.GetServiceById(Convert.ToInt64(reader["service_id"])),
User = UserService.GetUserById(Convert.ToInt64(reader["user_id"])),
Date = Convert.ToDateTime(reader["dt"]),
Type = ActionTypeService.GetTypeById(Convert.ToInt64(reader["type_id"])),
Customer = reader["customer"] != DBNull.Value ? Convert.ToString(reader["customer"]) : string.Empty,
CustomerType = reader["customer_type_id"] != DBNull.Value ? CustomerTypeService.GetTypeById(Convert.ToInt64(reader["customer_type_id"])) : CustomerType.Empty
});
}
}
catch (Exception e) {
Logger.Error(e);
throw;
}
finally {
if (reader != null)
{
reader.Close();
reader.Dispose();
}
if (cmd != null)
{
cmd.Dispose();
}
connection.Close();
}
return(actions);
}
