public function softwareAssetsReports()
{
if (Session::has('username') && (Session::get('user_type') == "Root" || Session::get('user_type') == "Admin" || Session::get("user_type") == "User")) {
$view = View::make("Reports.software_assets_reports");
$view->nav = "system";
$view->tab = "software";
$totalSoftwareAssets = Software::all()->count();
$softwareTypes = SoftwareType::orderBy("software_type")->get();
$available = Software::where("status", "=", "Available")->count();
$pwu = Software::where("status", "=", "PWU")->count();
$retired = Software::where("status", "=", "Retired")->count();
$test_case = Software::where("status", "=", "Test Case")->count();
$lost = Software::where("status", "=", "Lost")->count();
$view->softwareTypes = $softwareTypes;
$view->available = $available;
$view->pwu = $pwu;
$view->retired = $retired;
$view->test_case = $test_case;
$view->lost = $lost;
$view->totalSoftwareAssets = $totalSoftwareAssets;
return $view;
} else {
return Redirect::to("/");
}
}
private function processImport($file)
{
if (Session::has('username') && (Session::get('user_type') == "Root" || Session::get('user_type') == "Admin")) {
if (!in_array($file->getClientOriginalExtension(), array("xls", "xlsx", "csv"))) {
Input::flash();
return Redirect::to('assets/software/import')->with('message', "Invalid file selected.");
} else {
$filename = "upload-" . str_random(9) . "." . $file->getClientOriginalExtension();
$file->move("uploads", $filename);
$readFile = "uploads/" . $filename;
$reader = Excel::selectSheetsByIndex(0)->load($readFile, function ($reader) {
})->get();
/*
* Before validating each rows of the file uploaded, the file itself is checked if it has the valid attributes (columns)
* using the algorithm found below.
*
* 1. File is read.
* 2. Boolean variable $excelIsValid to check if the file is valid. Set to false by default.
*
*/
$excelChecker = Excel::selectSheetsByIndex(0)->load($readFile, function ($reader) {
})->get()->toArray();
$excelIsValid = false;
/*
* 3. Loop through the excel file and check if at least once all the columns have been present.
* 4. If it does, $excelIsValid is set to true.
* 5. If $excelIsValid is still false by end of the loop, it is automatically assumed that the file is:
* A.) Empty
* B.) Does not have the right attributes.
* C.) Has valid columns, but does not have any valid entry.
*
*/
foreach ($excelChecker as $ex) {
if (isset($ex["productkey"]) && isset($ex["softwaretype"]) && isset($ex["status"])) {
$excelIsValid = true;
}
}
/* 6. If file is invalid, redirect to import form and return an error. */
if (!$excelIsValid) {
Input::flash();
File::delete($readFile);
return Redirect::to('assets/software/import')->with('message', "Excel file has invalid attributes. Please download the form.");
}
/*
* CHECKING EXCEL FILE FOR ERRORS WHILE READING THE ROWS
*
* 1. $hasError is a Boolean variable that is simply used to tell if any error has been found.
* This variable is, by default, set to false. If any error has been detected, it is set to true,
* regardless of how many errors has been detected.
*
* 2. $rowIndex indexes which row the reader is currently reading. Default value set to 1 because
* the first row of excel files is automatically set as the attribute row. When the reader reads each row,
* $rowIndex is incremented. For example, reader is currently reading row 2, $rowIndex will then be incremented,
* setting its value to 2, thus the row number.
*
* 3. $rowsWithErrors is the array of the rows with errors. To explain further, let's say excel file has 10 readable (non-attrib)
* rows. No errors were found from rows number 2-8, but errors were found in rows 9, 10, and 11. These 9, 10, and 11
* will then be in the $rowsWithError.
*
* 4. $error array is the variable that will be used to collect all errors found from the excel file.
* This is a two-dimensional array.
*
*
*/
$hasError = false;
//Detects if there are any errors.
$hasCorrectRows = false;
$rowIndex = 1;
//Indexes which row the reader is reading.
$rowsWithErrors = array();
//This is used to contain in an array the row numbers of the rows with error.
$error = array();
//Error details collector.
foreach ($reader as $r) {
/*
* 5. Here, we immediately increment the value of $rowIndex, since the variable will be used in the core logic of this method.
*
* 6. $errorCount variable is a variable used in every loop. Set to 0 when the loop begins, so it always comes back to 0 for every loop.
* $errorCount is used to track the number of errors for the current row. This variable goes hand in hand with the
* $rowsWithError array when publishing the rows with errors, and the error details for each row with error.
*
* This is how $rowsWithError and $rowCount will be used:
*
* for each $rowWithErrors:
* Row $rowWithErrors Index:
* Errors Found :
* $rowCount 1. Foo bar
* $rowCount 2. Jane Doe etc..
*
*
*/
$rowIndex += 1;
$errorCount = 0;
//Counts the number of errors for the currect row.
$rowHasError = false;
//Check if this row has error. I will use this before the reading of the row ends.
// If $rowHasError is still false by end of the reading, then I will write it in the database.
$warranty_start = !empty(trim($r->warrantystart)) ? trim($r->warrantystart) : "1994-04-16";
$validator = Validator::make(array("software asset tag" => trim($r->assettag), "product key" => trim($r->productkey), "employee number" => trim($r->employeenumber), "laptop serial number" => trim($r->laptopsn), "software type" => trim($r->softwaretype), "warranty start date" => trim($r->warrantystart), "warranty end date" => trim($r->warrantyend), "status" => trim($r->status)), array("software asset tag" => "required|unique:tbl_software_assets,asset_tag|unique:tbl_assets,asset_tag", "product key" => "required", "employee number" => "exists:tbl_employees,employee_number", "laptop serial number" => "exists:tbl_assets,serial_number", "software type" => "required|exists:tbl_software_types,software_type", "warranty start date" => "required_with:warranty end date|date:Y-m-d", "warranty end date" => "date:Y-m-d|after:" . $warranty_start, "status" => "required|in:Available,PWU,Retired,Test Case,Lost"), array("after" => "The :attribute must be after the warranty start date."));
if ($validator->fails()) {
/* 7. When error has been found, $rowsWithError is immediately updated. Also, $hasError and $rowHasError are set to true.*/
$hasError = true;
$rowHasError = true;
$rowsWithErrors[$rowIndex] = $rowIndex;
/* 8. Then I will check which fields have errors.
*
* 9. If an error has been found in a certain field,
* I will loop through the errors found on that field, increment the $errorCount (which, again, tracks
* how many errors has been found on a certain row.), update the two-dimensional $error array.
* Please note that the first array of $error contains the row number which the errors found belong to.
*
*/
if ($validator->messages()->get("software asset tag")) {
foreach ($validator->messages()->get("software asset tag") as $e) {
$errorCount += 1;
$error[$rowIndex][$errorCount] = $errorCount . ". " . $e . "<br/>";
}
}
if ($validator->messages()->get("product key")) {
foreach ($validator->messages()->get("product key") as $e) {
$errorCount += 1;
$error[$rowIndex][$errorCount] = $errorCount . ". " . $e . "<br/>";
}
}
if ($validator->messages()->get("employee number")) {
foreach ($validator->messages()->get("employee number") as $e) {
$errorCount += 1;
$error[$rowIndex][$errorCount] = $errorCount . ". " . $e . "<br/>";
}
}
if ($validator->messages()->get("laptop serial number")) {
foreach ($validator->messages()->get("laptop serial number") as $e) {
$errorCount += 1;
$error[$rowIndex][$errorCount] = $errorCount . ". " . $e . "<br/>";
}
}
if ($validator->messages()->get("software type")) {
foreach ($validator->messages()->get("software type") as $e) {
$errorCount += 1;
$error[$rowIndex][$errorCount] = $errorCount . ". " . $e . "<br/>";
}
}
if ($validator->messages()->get("warranty start date")) {
foreach ($validator->messages()->get("warranty start date") as $e) {
$errorCount += 1;
$error[$rowIndex][$errorCount] = $errorCount . ". " . $e . "<br/>";
}
}
if ($validator->messages()->get("warranty end date")) {
foreach ($validator->messages()->get("warranty end date") as $e) {
$errorCount += 1;
$error[$rowIndex][$errorCount] = $errorCount . ". " . $e . "<br/>";
}
}
if ($validator->messages()->get("status")) {
foreach ($validator->messages()->get("status") as $e) {
$errorCount += 1;
$error[$rowIndex][$errorCount] = $errorCount . ". " . $e . "<br/>";
}
}
}
if ($r->status != "Lost" && Employee::where("employee_number", "=", $r->employeenumber)->whereIn("status", array("OJT Graduate", "Graduate", "Resigned", "Obsolete"))->first()) {
$hasError = true;
//This will only matter if no errors has been found above.
$rowHasError = true;
//This will only matter if no errors has been found above.
$rowsWithErrors[$rowIndex] = $rowIndex;
//This will only matter if no errors has been found above.
$errorCount += 1;
$error[$rowIndex][$errorCount] = $errorCount . ". " . "Cannot assign an asset to employees no longer working in the company." . "<br/>";
}
if (trim($r->laptopsn) != null && !Asset::where("serial_number", "=", $r->laptopsn)->whereHas("classification", function ($q) {
$q->where("name", "=", "Laptops");
})->first()) {
$hasError = true;
//This will only matter if no errors has been found above.
$rowHasError = true;
//This will only matter if no errors has been found above.
$rowsWithErrors[$rowIndex] = $rowIndex;
//This will only matter if no errors has been found above.
$errorCount += 1;
$error[$rowIndex][$errorCount] = $errorCount . ". " . "Cannot assign software asset to non-laptop assets. Please check the serial number and try again." . "<br/>";
}
if (!$rowHasError) {
$hasCorrectRows = true;
$software_type_id = SoftwareType::where("software_type", "=", $r->softwaretype)->get(array("id"))->first();
//Create the asset
$software = new Software();
$software->asset_tag = trim($r->assettag) != null ? trim($r->assettag) : null;
$software->product_key = trim($r->productkey);
$software->employee_number = trim($r->employeenumber) != null ? trim($r->employeenumber) : null;
$software->assigned_to_serial_number = trim($r->laptopsn) != null ? trim($r->laptopsn) : null;
$software->location = trim($r->location) != null ? trim($r->location) : null;
$software->software_type_id = $software_type_id->id;
$software->warranty_start = trim($r->warrantystart);
$software->warranty_end = trim($r->warrantyend);
$software->status = trim($r->status);
$software->notes = trim($r->notes) != null ? trim($r->notes) : null;
$software->date_added = date("Y-m-d H:i:s");
$software->save();
//Log the new asset to asset logs
if (!empty(trim($r->employeenumber))) {
$employee = Employee::where("employee_number", "=", trim($r->employeenumber))->first();
$desc = "Software Asset <strong>" . $software->asset_tag . "</strong> added to the database and assigned to employee <strong>" . $employee->first_name . " " . $employee->last_name . "</strong> with asset status <strong>" . $software->status . "</strong>.";
} else {
$desc = "Software Asset <strong>" . $software->asset_tag . "</strong> added to the database with status <strong>" . $software->status . "</strong>.";
}
$softwareLog = new SoftwareLog();
$softwareLog->user_id = Session::get("user_id");
$softwareLog->software_id = $software->id;
$softwareLog->employee_id = !empty($software->employee->id) ? $software->employee->id : null;
$softwareLog->description = $desc;
$softwareLog->transaction = "History";
$softwareLog->save();
//Parallel logging to system logs
$desc = "(" . Session::get('user_type') . ") " . "<strong>" . Session::get('username') . "</strong> added software asset <strong>" . $software->asset_tag . "</strong>.";
$newLog = new UserLog();
$newLog->description = $desc;
$newLog->user_id = Session::get('user_id');
$newLog->type = "System";
$newLog->save();
}
}
File::delete($readFile);
if ($hasCorrectRows) {
//Log the changes made
$desc = "(" . Session::get("user_type") . ") <b>" . Session::get("username") . "</b> has imported data to software assets database. ";
$newLog = new UserLog();
$newLog->description = $desc;
$newLog->user_id = Session::get('user_id');
$newLog->type = "System";
$newLog->save();
}
return $this->importResult($hasError, $hasCorrectRows, $rowsWithErrors, $error);
}
} else {
return Redirect::to('/');
}
}
public function deleteSoftwareType($id)
{
if (Session::has('username') && Session::get('user_type') == "Root") {
if (!is_numeric($id) || !SoftwareType::find($id)) {
return Redirect::to("settings/assets/softwaretypes");
} else {
if (count(SoftwareType::find($id)->softwareassets) > 0) {
return Redirect::to("settings/assets/softwaretypes");
}
}
$softwareType = SoftwareType::find($id);
$desc = "(" . Session::get('user_type') . ") " . "<strong>" . Session::get('username') . "</strong> has deleted software type <strong>" . $softwareType->software_type . "</strong>.";
//Log the changes made
$newLog = new UserLog();
$newLog->description = $desc;
$newLog->user_id = Session::get('user_id');
$newLog->type = "System";
$newLog->save();
$softwareType->delete();
return Redirect::to("settings/assets/softwaretypes");
} else {
return Redirect::to("/");
}
}
public function exportSoftwareBegin()
{
if (Session::has('username') && (Session::get('user_type') == "Root" || Session::get('user_type') == "Admin")) {
if (isset($_GET["search"])) {
$view = View::make("Export.export_software");
$view->nav = "system";
$view->tab = "software";
$input = Input::all();
$asset_tag = trim($input["asset_tag"]) != null ? str_replace(' ', '%', trim($input["asset_tag"])) : "";
$employee = trim($input["employee"]) != null ? trim($input["employee"]) : "";
$serial_number = trim($input["serial_number"]) != null ? str_replace(' ', '%', trim($input["serial_number"])) : "";
$software_type = trim($input["software_type"]) != null ? str_replace(' ', '%', trim($input["software_type"])) : "";
$asset_status = !empty($input["asset_status"]) ? $input["asset_status"] : "";
$warranty_start = trim($input["warranty_start"]) != null ? str_replace(' ', '%', trim($input["warranty_start"])) : "";
$warranty_end = trim($input["warranty_end"]) != null ? str_replace(' ', '%', trim($input["warranty_end"])) : "";
$software = Software::where("asset_tag", "LIKE", "%{$asset_tag}%")->where(function ($query) use($employee) {
$emp = "%" . str_replace(' ', '%', $employee) . "%";
$emp = DB::connection()->getPdo()->quote($emp);
if (!empty($employee)) {
$query->where("employee_number", "LIKE", $employee)->orWhereHas("employee", function ($q) use($employee, $emp) {
$q->whereRaw("concat(first_name,' ',last_name) LIKE {$emp}");
})->orWhereHas("employee", function ($q) use($employee, $emp) {
$q->whereRaw("concat(last_name,' ',first_name) LIKE {$emp}");
});
}
})->where(function ($query) use($serial_number) {
if (!empty($serial_number)) {
$query->where("assigned_to_serial_number", "=", $serial_number);
}
})->where(function ($query) use($software_type) {
if (!empty(${$software_type})) {
$query->where("software_type_id", "=", $software_type);
}
})->where(function ($query) use($asset_status) {
if (!empty($asset_status)) {
$query->where("status", "=", $asset_status);
}
})->where(function ($query) use($warranty_start) {
if (!empty($warranty_start)) {
$query->where("warranty_start", "LIKE", "{$warranty_start}%");
}
})->where(function ($query) use($warranty_end) {
if (!empty($warranty_end)) {
$query->where("warranty_end", "LIKE", "{$warranty_end}%");
}
})->orderBy("asset_tag")->paginate(25);
$result = Software::where("asset_tag", "LIKE", "%{$asset_tag}%")->where(function ($query) use($employee) {
$emp = "%" . str_replace(' ', '%', $employee) . "%";
$emp = DB::connection()->getPdo()->quote($emp);
if (!empty($employee)) {
$query->where("employee_number", "LIKE", $employee)->orWhereHas("employee", function ($q) use($employee, $emp) {
$q->whereRaw("concat(first_name,' ',last_name) LIKE {$emp}");
})->orWhereHas("employee", function ($q) use($employee, $emp) {
$q->whereRaw("concat(last_name,' ',first_name) LIKE {$emp}");
});
}
})->where(function ($query) use($serial_number) {
if (!empty($serial_number)) {
$query->where("assigned_to_serial_number", "=", $serial_number);
}
})->where(function ($query) use($software_type) {
if (!empty(${$software_type})) {
$query->where("software_type_id", "=", $software_type);
}
})->where(function ($query) use($asset_status) {
if (!empty($asset_status)) {
$query->where("status", "=", $asset_status);
}
})->where(function ($query) use($warranty_start) {
if (!empty($warranty_start)) {
$query->where("warranty_start", "LIKE", "{$warranty_start}%");
}
})->where(function ($query) use($warranty_end) {
if (!empty($warranty_end)) {
$query->where("warranty_end", "LIKE", "{$warranty_end}%");
}
})->count();
$getSoftwareTypes = SoftwareType::all();
$softwareTypes = array("" => "--Select One--");
foreach ($getSoftwareTypes as $gst) {
$softwareTypes[$gst->id] = $gst->software_type;
}
$view->softwareTypes = $softwareTypes;
$view->software = $software;
$view->results = $result;
Input::flash();
return $view;
} else {
if (isset($_GET["export"])) {
$input = Input::all();
$asset_tag = trim($input["asset_tag"]) != null ? str_replace(' ', '%', trim($input["asset_tag"])) : "";
$employee = trim($input["employee"]) != null ? trim($input["employee"]) : "";
$serial_number = trim($input["serial_number"]) != null ? str_replace(' ', '%', trim($input["serial_number"])) : "";
$software_type = trim($input["software_type"]) != null ? str_replace(' ', '%', trim($input["software_type"])) : "";
$asset_status = !empty($input["asset_status"]) ? $input["asset_status"] : "";
$warranty_start = trim($input["warranty_start"]) != null ? str_replace(' ', '%', trim($input["warranty_start"])) : "";
$warranty_end = trim($input["warranty_end"]) != null ? str_replace(' ', '%', trim($input["warranty_end"])) : "";
if (Session::has("secure") && Session::get("user_type") == "Root" && isset($_GET["pk"])) {
$software = Software::where("asset_tag", "LIKE", "%{$asset_tag}%")->where(function ($query) use($employee) {
$emp = "%" . str_replace(' ', '%', $employee) . "%";
$emp = DB::connection()->getPdo()->quote($emp);
if (!empty($employee)) {
$query->where("employee_number", "LIKE", $employee)->orWhereHas("employee", function ($q) use($employee, $emp) {
$q->whereRaw("concat(first_name,' ',last_name) LIKE {$emp}");
})->orWhereHas("employee", function ($q) use($employee, $emp) {
$q->whereRaw("concat(last_name,' ',first_name) LIKE {$emp}");
});
}
})->where(function ($query) use($serial_number) {
if (!empty($serial_number)) {
$query->where("assigned_to_serial_number", "=", $serial_number);
}
})->where(function ($query) use($software_type) {
if (!empty(${$software_type})) {
$query->where("software_type_id", "=", $software_type);
}
})->where(function ($query) use($asset_status) {
if (!empty($asset_status)) {
$query->where("status", "=", $asset_status);
}
})->where(function ($query) use($warranty_start) {
if (!empty($warranty_start)) {
$query->where("warranty_start", "LIKE", "{$warranty_start}%");
}
})->where(function ($query) use($warranty_end) {
if (!empty($warranty_end)) {
$query->where("warranty_end", "LIKE", "{$warranty_end}%");
}
})->orderBy("asset_tag")->get();
} else {
$software = Software::where("asset_tag", "LIKE", "%{$asset_tag}%")->where(function ($query) use($employee) {
$emp = "%" . str_replace(' ', '%', $employee) . "%";
$emp = DB::connection()->getPdo()->quote($emp);
if (!empty($employee)) {
$query->where("employee_number", "LIKE", $employee)->orWhereHas("employee", function ($q) use($employee, $emp) {
$q->whereRaw("concat(first_name,' ',last_name) LIKE {$emp}");
})->orWhereHas("employee", function ($q) use($employee, $emp) {
$q->whereRaw("concat(last_name,' ',first_name) LIKE {$emp}");
});
}
})->where(function ($query) use($serial_number) {
if (!empty($serial_number)) {
$query->where("assigned_to_serial_number", "=", $serial_number);
}
})->where(function ($query) use($software_type) {
if (!empty(${$software_type})) {
$query->where("software_type_id", "=", $software_type);
}
})->where(function ($query) use($asset_status) {
if (!empty($asset_status)) {
$query->where("status", "=", $asset_status);
}
})->where(function ($query) use($warranty_start) {
if (!empty($warranty_start)) {
$query->where("warranty_start", "LIKE", "{$warranty_start}%");
}
})->where(function ($query) use($warranty_end) {
if (!empty($warranty_end)) {
$query->where("warranty_end", "LIKE", "{$warranty_end}%");
}
})->orderBy("asset_tag")->get(array("id", "asset_tag", "location", "warranty_start", "warranty_end", "notes", "status", "employee_number", "assigned_to_serial_number", "software_type_id", "date_added"));
}
if ($software->count() > 0) {
if (isset($_GET["format"])) {
$softwareArray = array();
if (Session::has("secure") && Session::get("user_type") == "Root" && isset($_GET["pk"])) {
foreach ($software as $s) {
$softwareArray[$s->id] = array("Asset Tag" => $s->asset_tag, "Product Key" => $s->product_key, "Employee" => !empty($s->employee->first_name) ? $s->employee->first_name . " " . $s->employee->last_name : "Unassigned.", "Employee Number" => !empty($s->employee->employee_number) ? $s->employee->employee_number : null, "Location" => !empty($s->location) ? $s->location : "No Information.", "Assigned to Laptop" => !empty($s->assigned_to_serial_number) ? $s->assigned_to_serial_number : "No Information.", "Notes" => !empty($s->notes) ? $s->notes : null, "Warranty Start Date" => !empty($s->warranty_start) ? DateTime::createFromFormat("Y-m-d", $s->warranty_start)->format("F d, Y") : "No Information", "Warranty End Date" => !empty($s->warranty_end) ? DateTime::createFromFormat("Y-m-d", $s->warranty_end)->format("F d, Y") : "No Information", "Date Added to System" => DateTime::createFromFormat("Y-m-d H:i:s", $s->date_added)->format("F d, Y g:iA"), "Software Type" => $s->type->software_type, "Status" => $s->status);
}
} else {
foreach ($software as $s) {
$softwareArray[$s->id] = array("Asset Tag" => $s->asset_tag, "Employee" => !empty($s->employee->first_name) ? $s->employee->first_name . " " . $s->employee->last_name : "Unassigned.", "Employee Number" => !empty($s->employee->employee_number) ? $s->employee->employee_number : null, "Location" => !empty($s->location) ? $s->location : "No Information.", "Assigned to Laptop" => !empty($s->assigned_to_serial_number) ? $s->assigned_to_serial_number : "No Information.", "Notes" => !empty($s->notes) ? $s->notes : null, "Warranty Start Date" => !empty($s->warranty_start) ? DateTime::createFromFormat("Y-m-d", $s->warranty_start)->format("F d, Y") : "No Information", "Warranty End Date" => !empty($s->warranty_end) ? DateTime::createFromFormat("Y-m-d", $s->warranty_end)->format("F d, Y") : "No Information", "Date Added to System" => DateTime::createFromFormat("Y-m-d H:i:s", $s->date_added)->format("F d, Y g:iA"), "Software Type" => $s->type->software_type, "Status" => $s->status);
}
}
} else {
$softwareArray = $software->toArray();
}
Excel::create('software_assets_export_' . str_random(6), function ($excel) use($softwareArray) {
// Set the title
$excel->setTitle('Software Assets Export');
// Chain the setters
$excel->setCreator('Vault')->setCompany('Vault');
// Call them separately
$excel->setDescription('Software Assets Data Export from IT Vault.');
// Our first sheet
$excel->sheet('Software Assets', function ($sheet) use($softwareArray) {
$sheet->fromArray($softwareArray);
});
})->download('xlsx');
} else {
Input::flash();
return Redirect::to('export/software')->withInput()->with('info', "No records have been retrieved. Data export cancelled.");
}
}
}
} else {
return Redirect::to("/");
}
}
