public void DeleteStatus(Int64 fileId, Int64 statusId)
{
var conn = SqlProvider.CreateConnection();
SqlCommand cmd = null;
try {
cmd = conn.CreateCommand();
cmd.CommandText = @"
delete from FileStatus
where file_id = @file_id and status_id = @status_id";
cmd.Parameters.Add(new SqlParameter("file_id", fileId));
cmd.Parameters.Add(new SqlParameter("status_id", statusId));
cmd.ExecuteNonQuery();
}
catch (Exception e) {
throw e;
}
finally {
conn.Dispose();
conn.Close();
if (cmd != null)
{
cmd.Dispose();
}
}
}
public FileStatusInfo GetLastStatuses(Int64 fileId)
{
FileStatusInfo status = null;
var conn = SqlProvider.CreateConnection();
SqlCommand cmd = null;
try {
cmd = conn.CreateCommand();
cmd.CommandText = @"
select FileStatus.file_id, FileStatus.dt, FileStatus.status_id, FileStatus.user_id, FileStatus.comments
from FileStatus
join Files on Files.id = FileStatus.file_id
where Files.is_deleted = 0
and FileStatus.file_id = @file_id
order by FileStatus.dt desc";
cmd.Parameters.Add(new SqlParameter("file_id", fileId));
var reader = cmd.ExecuteReader();
if (reader.Read())
{
status = new FileStatusInfo {
FileId = fileId,
Date = Convert.ToDateTime(reader["dt"]),
User = UserService.GetUserById(Convert.ToInt64(reader["user_id"])),
Status = GetStatusById(Convert.ToInt64(reader["status_id"])),
Comments = Convert.ToString(reader["comments"])
};
}
}
catch (Exception e) {
throw new DomainException(e);
}
finally {
conn.Dispose();
conn.Close();
if (cmd != null)
{
cmd.Dispose();
}
}
return(status);
}
public void SetStatus(long fileId, long statusId, DateTime date, string comments)
{
var conn = SqlProvider.CreateConnection();
SqlCommand cmd = null;
var user = UserService.GetCurrentUser();
if (user == null)
{
throw new DomainException("Для текущего контекста не определен пользователь. Метод IUserService.GetCurrentUser вернул пустое значение");
}
try {
cmd = conn.CreateCommand();
cmd.CommandText = @"
insert into FileStatus (file_id, dt, status_id, user_id, comments)
values (@file_id, @dt, @status_id, @user_id, @comments)";
cmd.Parameters.Add(new SqlParameter("file_id", fileId));
cmd.Parameters.Add(new SqlParameter("dt", date));
cmd.Parameters.Add(new SqlParameter("user_id", user.Id));
cmd.Parameters.Add(new SqlParameter("status_id", statusId));
cmd.Parameters.Add(new SqlParameter("comments", string.IsNullOrEmpty(comments) ? string.Empty : comments));
cmd.ExecuteNonQuery();
}
catch (Exception e) {
throw e;
}
finally {
conn.Dispose();
conn.Close();
if (cmd != null)
{
cmd.Dispose();
}
}
}
public void SetPassword(long userId, string password)
{
var conn = SqlProvider.CreateConnection();
var cmd = conn.CreateCommand();
try {
cmd.CommandText = @"
update Users set password = @password
where id = @id";
cmd.Parameters.Add(new SqlParameter("id", userId));
cmd.Parameters.Add(new SqlParameter("password", password));
int row_count = cmd.ExecuteNonQuery();
if (row_count == 0)
{
throw new DomainException("Подсистеме работы с данными не удалось обновить информацию в таблице Users");
}
}
finally {
cmd.Dispose();
conn.Close();
}
}
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);
}