private function saveHeaders($file, $fileID)
{
//create the path
$fileName = base_path() . "/public/files/" . $file->getClientOriginalName();
//create the empty arrays
$columnTitles = [];
$taxonomyArray = [];
$fieldArray = [];
//load the file
$rows = Excel::load($fileName, function ($reader) {
})->get();
//set the values of the columns as the header
foreach ($rows as $title => $value) {
//set the row data
foreach ($value as $key => $column) {
$columnTitles[$key][] = $column;
}
}
$files = File::find($fileID);
foreach ($columnTitles as $key => $value) {
$field_tax = Field_Taxonomy::where('title', '=', $key);
$fieldTaxID = 0;
$count = 1;
//if the title doesn't exist as a record in the field_tax table
if ($field_tax->count() === 0) {
//grab the id of the newly inserted taxonomy record
$fieldTaxID = Field_Taxonomy::insertGetId(['title' => $key]);
foreach ($value as $data) {
Field::insert(['value' => $data, 'field_taxonomy_id' => $fieldTaxID, 'row_in_file' => $count]);
$count++;
}
File_Field_Taxonomy::insert(['file_id' => $fileID, 'field_taxonomy_id' => $fieldTaxID]);
} else {
$fileArr = explode(".", $file->getClientOriginalName());
$fileTitleArr = explode("_", $fileArr[0]);
$fieldTax = Field_Taxonomy::take(1)->select('field_taxonomy_table.id')->join('file_field_taxonomy_table', 'file_field_taxonomy_table.field_taxonomy_id', '=', 'field_taxonomy_table.id')->join('files', 'files.id', '=', 'file_field_taxonomy_table.file_id')->where('title', '=', $key)->where('parsed_title', '=', $fileTitleArr[0])->get();
if (count($fieldTax) > 0) {
$fieldTaxID = $fieldTax[0]->id;
} else {
$fieldTaxID = Field_Taxonomy::insertGetId(['title' => $key]);
}
foreach ($value as $data) {
Field::insert(['value' => $data, 'field_taxonomy_id' => $fieldTaxID, 'row_in_file' => $count]);
$count++;
}
File_Field_Taxonomy::insert(['file_id' => $fileID, 'field_taxonomy_id' => $fieldTaxID]);
}
}
}
/**
* Display a listing of the resource.
*
* @return \Illuminate\Http\Response
*/
public function index($date, $file)
{
/*$fields = Field::select('fields.value', 'field_taxonomy_table.title')
->join('field_taxonomy_table', 'fields.field_taxonomy_id', '=', 'field_taxonomy_table.id')
->join('files', 'field_taxonomy_table.file_id', '=', 'files.id')
->where('files.parsed_title', '=', $file)
->get();
dd($fields);*/
$fields = File::join('file_field_taxonomy_table as fftt', 'fftt.file_id', '=', 'files.id')->join('field_taxonomy_table as ftt', 'fftt.field_taxonomy_id', '=', 'ftt.id')->where('files.parsed_title', '=', $file)->select('ftt.title', 'ftt.id')->groupBy('ftt.title');
if ($file === "claimsbybatch") {
$fields = $fields->where('title', '=', 'claim_status_ud')->orWhere('title', '=', 'claim_procedure_status_ud')->orWhere('title', '=', 'from_service_date')->orWhere('title', '=', 'clean_claim_date')->orWhere('title', '=', 'received_date')->orWhere('title', '=', 'benefitplan_ud');
}
$titleArr = [];
//loop through all of the results
foreach ($fields->get() as $field) {
//split the title at the underscore
$title = explode('_', $field->title);
$count = 0;
$isDate = false;
$type = "string";
$arrValues = [];
//loop through the title arr
foreach ($title as $word) {
if ($word === "date") {
$isDate = true;
}
//remove the "ud" from the title arr
if ($word === "ud") {
unset($title[$count]);
} else {
//if it's benefit plan, then split it up
if ($title[$count] === "benefitplan") {
$title[$count] = "benefit plan";
}
//make each word proper case
$title[$count] = ucfirst($title[$count]);
}
$count++;
}
if ($isDate) {
$titleArr['date'][] = ['title' => implode(' ', $title), 'db_title' => $field->title, 'value' => $arrValues];
} else {
$arrValues = Field_Taxonomy::select('value')->distinct()->join('fields', 'fields.field_taxonomy_id', '=', 'field_taxonomy_table.id')->where('title', '=', $field->title)->get();
$titleArr['other'][] = ['title' => implode(' ', $title), 'db_title' => $field->title, 'value' => $arrValues];
}
}
return view("reports.main")->with('data', $titleArr);
}