public async Task <ActionResult> Create(ComputerIndexViewModel viewModel)
{
try
{
string sql = @"INSERT INTO Computer
(Make, Manufacturer, PurchaseDate)
VALUES
(@Make, @Manufacturer, @PurchaseDate)";
if (viewModel.EmployeeId != null && viewModel.EmployeeId != 0)
{
sql = $@"{sql}
DECLARE @newId INT
SELECT @newId = @@IDENTITY
UPDATE ComputerEmployee
SET UnassignDate = @AssignDate
WHERE EmployeeID = @EmployeeId AND UnassignDate IS NULL
INSERT INTO ComputerEmployee
(ComputerId, EmployeeId, AssignDate)
VALUES
(@newId, @EmployeeId, @AssignDate)";
}
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.Add(new SqlParameter("@Make", viewModel.Computer.Make));
cmd.Parameters.Add(new SqlParameter("@Manufacturer", viewModel.Computer.Manufacturer));
cmd.Parameters.Add(new SqlParameter("@PurchaseDate", viewModel.Computer.PurchaseDate));
if (viewModel.EmployeeId != null && viewModel.EmployeeId != 0)
{
cmd.Parameters.Add(new SqlParameter("@EmployeeId", viewModel.EmployeeId));
cmd.Parameters.Add(new SqlParameter("@AssignDate", DateTime.Now));
}
await cmd.ExecuteNonQueryAsync();
return(RedirectToAction(nameof(Index)));
}
}
}
catch
{
return(View());
}
}
// GET: Computers
public ActionResult Index(string SearchString, String city, string status, double from = 0.0, double to = 0.0, int page = 1)
{
var computers = db.Computers.OfType <Computer>()
.Where(w => w.Ad.User.Confirmed)
.Include(l => l.Ad);
ViewBag.Views = db.Views;
ViewBag.Wishlists = db.Wishlists;
if (!String.IsNullOrEmpty(SearchString))
{
computers = computers.Where(a => a.Ad.City.Name.ToUpper().Contains(SearchString.ToUpper()) ||
a.Ad.AdDescribtion.ToUpper().Contains(SearchString.ToUpper()) ||
a.ComputerCompany.ToUpper().Contains(SearchString.ToUpper()) ||
a.ComputerOS.ToUpper().Contains(SearchString.ToUpper()) ||
a.ComputerColor.ToUpper().Contains(SearchString.ToUpper()));
}
if (!String.IsNullOrEmpty(city))
{
computers = computers.Where(a => a.Ad.City.Name.ToUpper().Equals(city));
}
if (to >= from && to > 1 && from >= 0.0)
{
computers = computers.Where(a => a.Ad.AdPrice >= from && a.Ad.AdPrice <= to);
}
var cities = computers.Select(c => c.Ad.City.Name).Distinct();
ViewBag.city = new SelectList(cities);
//pagination steps
int pageSize = 6;
var pager = new Pager(computers.Count(), page, pageSize);
ComputerIndexViewModel vModel = new ComputerIndexViewModel()
{
Computers = computers.OrderBy(a => a.Ad.AdPrice).Skip((pager.CurrentPage - 1) * pager.PageSize).Take(pager.PageSize),
Pager = pager,
SearchString = SearchString,
City = city,
Status = status,
From = from,
To = to
};
return(View(vModel));
}
// GET: Computers
// computers data
public ActionResult Index(string depCode, int?personId, string searchSoft = "", string searchString = "", int page = 1, bool modified = false, bool notebooks = false)
{
List <Computer> dbComputers;
Pager pager = null;
if (personId == null)
{
dbComputers = depCode == null
? _db.Computers.OrderBy(c => c.ComputerName).ToList()
: _db.Computers.AsEnumerable().Where(c => c.ComputerName.StartsWith(depCode + '-')).OrderBy(c => c.ComputerName).ToList();
}
else
{
dbComputers = _db.Computers.OrderBy(c => c.ComputerName).Where(c => c.Owner.Id == personId).ToList();
}
if (searchSoft != "")
{
dbComputers = dbComputers.Where(c => c.Software.IndexOf(searchSoft, StringComparison.OrdinalIgnoreCase) >= 0).ToList();
}
if (searchString != "")
{
dbComputers.RemoveAll(c => !StaticData.HasStringInData(searchString, c));
}
if (modified)
{
dbComputers = dbComputers.Where(c => c.IsConfigChanged()).ToList();
}
if (notebooks)
{
dbComputers = dbComputers.Where(c => c.IsNotebook).ToList();
}
if (!modified)
{
pager = new Pager(dbComputers.Count, page, 8);
}
var computers = dbComputers.Select(comp => new ComputerViewModel
{
Id = comp.Id,
IsNotebook = comp.IsNotebook,
IsConfigChanged = comp.IsConfigChanged(),
ComputerName = comp.ComputerName,
Cpu = comp.Cpu,
Ram = comp.Ram,
RamFixed = comp.Ram != comp.RamFixed ? comp.RamFixed : 0,
Hdd = comp.Hdd,
HddFixed = comp.Hdd != comp.HddFixed ? comp.HddFixed : string.Empty,
MotherBoard = comp.MotherBoard,
MotherBoardFixed = comp.MotherBoard != comp.MotherBoardFixed ? comp.MotherBoardFixed : string.Empty,
VideoAdapter = comp.VideoAdapter,
VideoAdapterFixed = comp.VideoAdapter != comp.VideoAdapterFixed ? comp.VideoAdapterFixed : string.Empty,
Monitor = string.IsNullOrEmpty(comp.Monitor) ? "(нет)" : comp.Monitor,
MonitorFixed = comp.Monitor != comp.MonitorFixed ? comp.MonitorFixed : string.Empty,
Owner = comp.Owner == null ? string.Empty : comp.Owner.ShortName,
OwnerId = comp.Owner?.Id ?? 0,
HasRequests = comp.SupportRequests.Count > 0,
HasModifications = comp.HasModifications,
LastReportDate = comp.UpdateDate?.ToString("g") ?? string.Empty
}).ToList();
var computersViewModel = new ComputerIndexViewModel
{
Computers = pager == null ? computers : computers.Skip((pager.CurrentPage - 1) * pager.PageSize).Take(pager.PageSize),
Pager = pager,
DepCodes = _db.Computers.AsEnumerable().Select(c => c.ComputerName.Split('-').First()).Distinct().OrderBy(c => c).ToArray(),
DepCode = depCode,
SearchSoft = searchSoft == "" ? null : searchSoft,
SearchData = searchString == "" ? null : searchString,
PersonSearch = personId != null,
HasModifiedComputers = _db.Computers.FirstOrDefaultAsync(c => c.IsConfigChanged()) != null,
ModifiedComputers = modified,
Notebooks = notebooks,
TotalCount = computers.Count
};
return(View(computersViewModel));
}
// GET: Computers
public ActionResult Index(string searchString)
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"
SELECT c.Id, c.Make, c.Manufacturer, c.PurchaseDate,
ce.AssignDate, ce.UnassignDate, e.Id as EmployeeId,
e.FirstName, e.LastName, e.DepartmentId, e.IsSuperVisor
FROM Computer c
LEFT JOIN ComputerEmployee ce ON c.id = ce.ComputerId
AND UnassignDate IS NULL
LEFT JOIN Employee e ON e.Id = ce.EmployeeId";
SqlDataReader reader = cmd.ExecuteReader();
Dictionary <int, ComputerIndexViewModel> viewModelHash = new Dictionary <int, ComputerIndexViewModel>();
while (reader.Read())
{
int computerId = reader.GetInt32(reader.GetOrdinal("Id"));
if (!viewModelHash.ContainsKey(computerId))
{
viewModelHash[computerId] = new ComputerIndexViewModel()
{
Computer = new Computer
{
Id = reader.GetInt32(reader.GetOrdinal("Id")),
Manufacturer = reader.GetString(reader.GetOrdinal("Manufacturer")),
Make = reader.GetString(reader.GetOrdinal("Make")),
PurchaseDate = reader.GetDateTime(reader.GetOrdinal("PurchaseDate"))
}
};
if (!reader.IsDBNull(reader.GetOrdinal("AssignDate")) && reader.IsDBNull(reader.GetOrdinal("UnassignDate")))
{
viewModelHash[computerId].Employee = new Employee
{
Id = reader.GetInt32(reader.GetOrdinal("EmployeeId")),
FirstName = reader.GetString(reader.GetOrdinal("FirstName")),
LastName = reader.GetString(reader.GetOrdinal("LastName")),
DepartmentId = reader.GetInt32(reader.GetOrdinal("DepartmentId")),
IsSupervisor = reader.GetBoolean(reader.GetOrdinal("IsSuperVisor"))
};
}
}
}
reader.Close();
List <ComputerIndexViewModel> viewModels = viewModelHash.Values.ToList();
if (!String.IsNullOrEmpty(searchString))
{
viewModels = viewModels.Where(s => s.Computer.Make.Contains(searchString) || s.Computer.Manufacturer.Contains(searchString)).ToList();
}
return(View(viewModels));
}
}
}
// GET: Computers/Create
public ActionResult Create()
{
ComputerIndexViewModel computerIndexViewModel = new ComputerIndexViewModel(_config.GetConnectionString("DefaultConnection"));
return(View(computerIndexViewModel));
}
