protected function dtgDepreciationClass_Bind() { $this->dtgDepreciationClass->TotalItemCount = DepreciationClass::CountAll(); if ($this->dtgDepreciationClass->TotalItemCount == 0) { $this->dtgDepreciationClass->ShowHeader = false; } else { $this->dtgDepreciationClass->DataSource = DepreciationClass::LoadAll(); $this->dtgDepreciationClass->ShowHeader = true; } }
protected function btnNext_Click() { $blnError = false; if ($this->intStep == 1) { if ($this->chkHeaderRow->Checked) { $this->blnHeaderRow = true; } else { $this->blnHeaderRow = false; } // Check errors if ($this->lstFieldSeparator->SelectedValue == 'other' && !$this->txtFieldSeparator->Text) { $this->flcFileCsv->Warning = "Please enter the field separator."; $blnError = true; } elseif ($this->lstTextDelimiter->SelectedValue == 'other' && !$this->txtTextDelimiter->Text) { $this->flcFileCsv->Warning = "Please enter the text delimiter."; $blnError = true; } else { // Step 1 complete // File Not Uploaded if (!file_exists($this->flcFileCsv->File) || !$this->flcFileCsv->Size) { //throw new QCallerException('FileAssetType must be a valid QFileAssetType constant value'); $this->flcFileCsv->Warning = 'The file could not be uploaded. Please provide a valid file.'; $blnError = true; // File Has Incorrect MIME Type (only if an acceptiblemimearray is setup) } elseif (is_array($this->strAcceptibleMimeArray) && !array_key_exists($this->flcFileCsv->Type, $this->strAcceptibleMimeArray)) { $this->flcFileCsv->Warning = "Extension must be 'csv' or 'txt'"; $blnError = true; // File Successfully Uploaded } else { $this->flcFileCsv->Warning = ""; // Setup Filename, Base Filename and Extension $strFilename = $this->flcFileCsv->FileName; $intPosition = strrpos($strFilename, '.'); } if (!$blnError) { $this->FileCsvData = new File_CSV_DataSource(); // Setup the settings which have got on step 1 $this->FileCsvData->settings($this->GetCsvSettings()); $file = fopen($this->flcFileCsv->File, "r"); // Counter of files $i = 1; // Counter of rows $j = 1; $this->strFilePathArray = array(); // The uploaded file splits up in order to avoid out of memory while ($row = fgets($file, 1000)) { if ($j == 1) { $strFilePath = sprintf('%s/%s_mod_%s.csv', __TRACMOR_TMP__, $_SESSION['intUserAccountId'], $i); $this->strFilePathArray[] = $strFilePath; $file_part = fopen($strFilePath, "w+"); if ($i == 1) { $strHeaderRow = $row; } else { fwrite($file_part, $strHeaderRow); } } fwrite($file_part, $row); $j++; if ($j > 200) { $j = 1; $i++; fclose($file_part); } } $this->intTotalCount = ($i - 1) * 200 + $j - 1; if (false) { $blnError = true; $this->btnNext->Warning = $i . " " . $j . "Sorry that is too many assets. Your asset limit is = " . QApplication::$TracmorSettings->AssetLimit . ", this import has " . $this->intTotalCount . " assets, and you already have " . Asset::CountAll() . " assets in the database."; } else { $this->arrMapFields = array(); $this->arrTracmorField = array(); // Load first file $this->FileCsvData->load($this->strFilePathArray[0]); $file_skipped = fopen($this->strFilePath = sprintf('%s/%s_skipped.csv', __TRACMOR_TMP__, $_SESSION['intUserAccountId']), "w+"); // Get Headers if ($this->blnHeaderRow) { $this->arrCsvHeader = $this->FileCsvData->getHeaders(); // Create the header row in the skipped error file $this->PutSkippedRecordInFile($file_skipped, $this->arrCsvHeader); } /*else { // If it is not first file $this->FileCsvData->appendRow($this->FileCsvData->getHeaders()); }*/ $strFirstRowArray = $this->FileCsvData->getRow(0); for ($i = 0; $i < count($strFirstRowArray); $i++) { $this->arrMapFields[$i] = array(); if ($this->blnHeaderRow && array_key_exists($i, $this->arrCsvHeader)) { if ($this->arrCsvHeader[$i] == '') { $this->arrCsvHeader[$i] = ' '; } $this->lstMapHeader_Create($this, $i, $this->arrCsvHeader[$i]); $this->arrMapFields[$i]['header'] = $this->arrCsvHeader[$i]; } else { $this->lstMapHeader_Create($this, $i); } // Create Default Value TextBox, ListBox and DateTimePicker if ($this->blnHeaderRow && array_key_exists($i, $this->arrCsvHeader) && $this->arrCsvHeader[$i] || !$this->blnHeaderRow) { $txtDefaultValue = new QTextBox($this); $txtDefaultValue->Width = 200; $this->txtMapDefaultValueArray[] = $txtDefaultValue; $lstDefaultValue = new QListBox($this); $lstDefaultValue->Width = 200; $lstDefaultValue->Display = false; $this->lstMapDefaultValueArray[] = $lstDefaultValue; $dtpDate = new QDateTimePicker($this); $dtpDate->DateTimePickerType = QDateTimePickerType::Date; $dtpDate->DateTimePickerFormat = QDateTimePickerFormat::MonthDayYear; $dtpDate->Display = false; $this->dtpDateArray[] = $dtpDate; if (array_key_exists($i, $this->lstMapHeaderArray)) { $this->lstTramorField_Change(null, $this->lstMapHeaderArray[$i]->ControlId, null); } } $this->arrMapFields[$i]['row1'] = $strFirstRowArray[$i]; } $this->btnNext->Text = "Import Now"; fclose($file_skipped); // Create Add Field button $btnAddField = new QButton($this); $btnAddField->Text = "Add Field"; $btnAddField->AddAction(new QClickEvent(), new QServerAction('btnAddField_Click')); $btnAddField->AddAction(new QEnterKeyEvent(), new QServerAction('btnAddField_Click')); $btnAddField->AddAction(new QEnterKeyEvent(), new QTerminateAction()); $this->lstMapHeaderArray[] = $btnAddField; } } } } elseif ($this->intStep == 2) { // Step 2 complete $blnError = false; $blnAssetModelCode = false; $blnAssetModelShortDescription = false; $blnCategory = false; $blnManufacturer = false; $blnModelId = false; // Checking errors (Model Short Description, Model Code, Category and Manufacturer must be selected) for ($i = 0; $i < count($this->lstMapHeaderArray) - 1; $i++) { $lstMapHeader = $this->lstMapHeaderArray[$i]; $strSelectedValue = strtolower($lstMapHeader->SelectedValue); if ($strSelectedValue == "short description") { $blnAssetModelShortDescription = true; } elseif ($strSelectedValue == "model number") { $blnAssetModelCode = true; } elseif ($strSelectedValue == "category") { $blnCategory = true; } elseif ($strSelectedValue == "manufacturer") { $blnManufacturer = true; } elseif ($strSelectedValue == "id") { $blnModelId = true; } } if ($this->lstMapDefaultValueArray) { // Checking errors for required Default Value text fields foreach ($this->lstMapDefaultValueArray as $lstDefault) { if ($lstDefault->Display && $lstDefault->Required && !$lstDefault->SelectedValue) { $lstDefault->Warning = "You must select one default value."; $blnError = true; break; } else { $blnError = false; $lstDefault->Warning = ""; } } } if ($this->txtMapDefaultValueArray) { // Checking errors for required Default Value lst fields foreach ($this->txtMapDefaultValueArray as $txtDefault) { if ($txtDefault->Display && $txtDefault->Required && !$txtDefault->Text) { $txtDefault->Warning = "You must enter default value."; $blnError = true; break; } else { $blnError = false; $txtDefault->Warning = ""; } } } // If all required fields have no errors if (!$blnError && $blnAssetModelCode && $blnAssetModelShortDescription && $blnCategory && $blnManufacturer && ($this->lstImportAction->SelectedValue != 2 || $blnModelId)) { $this->btnNext->Warning = ""; // Setup keys for main required fields foreach ($this->arrTracmorField as $key => $value) { if ($value == 'category') { $this->intCategoryKey = $key; } elseif ($value == 'manufacturer') { $this->intManufacturerKey = $key; } elseif (QApplication::$TracmorSettings->DepreciationFlag == '1' && $value == 'depreciation class') { $this->intDepreciationKey = $key; } elseif ($this->lstImportAction->SelectedValue == 2 && $value == 'id') { $this->intItemIdKey = $key; } /*elseif ($value == 'created by') { $this->intCreatedByKey = $key; } elseif ($value == 'created date') { $this->intCreatedDateKey = $key; } elseif ($value == 'modified by') { $this->intModifiedByKey = $key; } elseif ($value == 'modified date') { $this->intModifiedDateKey = $key; }*/ } $this->objNewAssetModelArray = array(); $this->strModelValuesArray = array(); $this->blnImportEnd = false; $j = 1; $this->btnNext->RemoveAllActions('onclick'); // Add new ajax actions for button $this->btnNext->AddAction(new QClickEvent(), new QAjaxAction('btnNext_Click')); $this->btnNext->AddAction(new QClickEvent(), new QToggleEnableAction($this->btnNext)); $this->btnNext->AddAction(new QEnterKeyEvent(), new QAjaxAction('btnNext_Click')); $this->btnNext->AddAction(new QEnterKeyEvent(), new QToggleEnableAction($this->btnNext)); $this->btnNext->AddAction(new QEnterKeyEvent(), new QTerminateAction()); $this->btnNext->Warning = "Please wait..."; $this->intImportStep = 2; $this->intCurrentFile = 0; $this->strSelectedValueArray = array(); // New asset models $this->dtgAssetModel = new QDataGrid($this); $this->dtgAssetModel->Name = 'asset_model_list'; $this->dtgAssetModel->CellPadding = 5; $this->dtgAssetModel->CellSpacing = 0; $this->dtgAssetModel->CssClass = "datagrid"; $this->dtgAssetModel->UseAjax = true; $this->dtgAssetModel->ShowColumnToggle = false; $this->dtgAssetModel->ShowExportCsv = false; $this->dtgAssetModel->ShowHeader = false; $this->dtgAssetModel->AddColumn(new QDataGridColumnExt('Model', '<?= $_ITEM ?>', 'CssClass="dtg_column"', 'HtmlEntities="false"')); // Updated assets $this->dtgUpdatedAsset = new QDataGrid($this); $this->dtgUpdatedAsset->Name = 'updated_asset_list'; $this->dtgUpdatedAsset->CellPadding = 5; $this->dtgUpdatedAsset->CellSpacing = 0; $this->dtgUpdatedAsset->CssClass = "datagrid"; $this->dtgUpdatedAsset->UseAjax = true; $this->dtgUpdatedAsset->ShowColumnToggle = false; $this->dtgUpdatedAsset->ShowExportCsv = false; $this->dtgUpdatedAsset->ShowHeader = false; $this->dtgUpdatedAsset->AddColumn(new QDataGridColumnExt('Asset Tag', '<?= $_ITEM ?>', 'CssClass="dtg_column"', 'HtmlEntities="false"')); // Create the label for successful import $this->lblImportSuccess = new QLabel($this); $this->lblImportSuccess->HtmlEntities = false; $this->lblImportSuccess->Display = false; // Undo Last Import button $this->btnUndoLastImport = new QButton($this); $this->btnUndoLastImport->Text = "Undo Last Import"; $this->btnUndoLastImport->Display = false; $this->btnUndoLastImport->AddAction(new QClickEvent(), new QServerAction('btnCancel_Click')); $this->btnUndoLastImport->AddAction(new QEnterKeyEvent(), new QServerAction('btnCancel_Click')); $this->btnUndoLastImport->AddAction(new QEnterKeyEvent(), new QTerminateAction()); // Import More button $this->btnImportMore = new QButton($this); $this->btnImportMore->Text = "Import More"; $this->btnImportMore->Display = false; $this->btnImportMore->AddAction(new QClickEvent(), new QServerAction('btnImportMore_Click')); $this->btnImportMore->AddAction(new QEnterKeyEvent(), new QServerAction('btnImportMore_Click')); $this->btnImportMore->AddAction(new QEnterKeyEvent(), new QTerminateAction()); // Return to Assets button $this->btnReturnToAssets = new QButton($this); $this->btnReturnToAssets->Text = "Return to Models"; $this->btnReturnToAssets->Display = false; $this->btnReturnToAssets->AddAction(new QClickEvent(), new QServerAction('btnReturnToAssets_Click')); $this->btnReturnToAssets->AddAction(new QEnterKeyEvent(), new QServerAction('btnReturnToAssets_Click')); $this->btnReturnToAssets->AddAction(new QEnterKeyEvent(), new QTerminateAction()); } else { $this->btnNext->Warning = "You must select all required fields (Model Number, Model Short Description, Category and Manufacturer)."; $blnError = true; } } else { // Step 3 complete set_time_limit(0); $file_skipped = fopen($strFilePath = sprintf('%s/%s_skipped.csv', __TRACMOR_TMP__, $_SESSION['intUserAccountId']), "a"); if (!$this->blnImportEnd) { // Asset Model if ($this->intImportStep == 2) { $intCategoryArray = array(); // Load all categories with key=category_id foreach (Category::LoadAllWithFlags(true, false) as $objCategory) { $intCategoryArray[$objCategory->CategoryId] = strtolower($objCategory->ShortDescription); } $intManufacturerArray = array(); // Load all manufacturers with key=manufacturer_id foreach (Manufacturer::LoadAll() as $objManufacturer) { $intManufacturerArray[$objManufacturer->ManufacturerId] = strtolower($objManufacturer->ShortDescription); } if (QApplication::$TracmorSettings->DepreciationFlag == '1') { $intDepreciationClassArray = array(); foreach (DepreciationClass::LoadAll() as $objDepreciationClass) { $intDepreciationClassArray[$objDepreciationClass->DepreciationClassId] = strtolower($objDepreciationClass->ShortDescription); } } $intModelCustomFieldKeyArray = array(); $arrModelCustomField = array(); // Setup keys foreach ($this->arrTracmorField as $key => $value) { if ($value == 'short description') { $intModelShortDescriptionKey = $key; } elseif ($value == 'long description') { $intModelLongDescriptionKey = $key; } elseif ($value == 'model number') { $intModelCodeKey = $key; } elseif (substr($value, 0, 6) == 'model_') { $intModelCustomFieldKeyArray[substr($value, 6)] = $key; if (array_key_exists(substr($value, 6), $this->arrModelCustomField)) { $arrModelCustomField[substr($value, 6)] = $this->arrModelCustomField[substr($value, 6)]; } } } $strAssetModelArray = array(); $strItemCFVArray = array(); $strUpdatedItemCFVArray = array(); $strUpdatedValuesArray = array(); $this->arrOldItemArray = array(); $this->objUpdatedItemArray = array(); // Load all asset models foreach (AssetModel::LoadAllIntoExtendedArray() as $arrAssetModel) { $strAssetModelArray[] = strtolower(sprintf("%s_%s_%s_%s", addslashes($arrAssetModel['model_code']), addslashes($arrAssetModel['short_description']), $arrAssetModel['category_id'], $arrAssetModel['manufacturer_id'])); } $this->btnNext->Warning = sprintf("Please wait... Model import complete: %s%s", ceil(($this->intCurrentFile + 1) * 200 / $this->intTotalCount * 100), "%"); } // Asset /*elseif ($this->intImportStep == 5) { $intCategoryArray = array(); // Load all categories with keys=category_id foreach (Category::LoadAllWithFlags(true, false) as $objCategory) { //$intCategoryArray["'" . strtolower($objCategory->ShortDescription) . "'"] = $objCategory->CategoryId; $intCategoryArray[$objCategory->CategoryId] = strtolower($objCategory->ShortDescription); } $intManufacturerArray = array(); // Load all manufacturers with keys=manufacturer_id foreach (Manufacturer::LoadAll() as $objManufacturer) { //$intManufacturerArray["'" . strtolower($objManufacturer->ShortDescription) . "'"] = $objManufacturer->ManufacturerId; $intManufacturerArray[$objManufacturer->ManufacturerId] = strtolower($objManufacturer->ShortDescription); } if ($this->intCurrentFile == 0) { $this->intAssetModelArray = array(); // Load all asset models with keys=asset_model_id foreach (AssetModel::LoadAll() as $objAssetModel) { //$intAssetModelArray["'" . strtolower($objAssetModel->ShortDescription) . "'"] = $objAssetModel->AssetModelId; $this->intAssetModelArray[$objAssetModel->AssetModelId] = strtolower(sprintf("%s_%s_%s_%s", $objAssetModel->AssetModelCode, $objAssetModel->ShortDescription, $objAssetModel->CategoryId, $objAssetModel->ManufacturerId)); } } $intAssetCustomFieldKeyArray = array(); $arrAssetCustomField = array(); // Setup keys foreach ($this->arrTracmorField as $key => $value) { if ($value == 'asset model short description') { $intModelShortDescriptionKey = $key; } elseif ($value == 'asset model code') { $intModelCodeKey = $key; } elseif ($value == 'asset code') { $intAssetCode = $key; } elseif (substr($value, 0, 6) == 'asset_') { $intAssetCustomFieldKeyArray[substr($value, 6)] = $key; if (array_key_exists(substr($value, 6), $this->arrAssetCustomField)) { $arrAssetCustomField[substr($value, 6)] = $this->arrAssetCustomField[substr($value, 6)]; } } } $intLocationArray = array(); // Load all locations with keys=location_id foreach (Location::LoadAll() as $objLocation) { //$intLocationArray["'" . strtolower($objLocation->ShortDescription) . "'"] = $objLocation->LocationId; $intLocationArray[$objLocation->LocationId] = strtolower($objLocation->ShortDescription); } $strAssetArray = array(); $strUpdatedAssetArray = array(); // Load all assets foreach (Asset::LoadAll() as $objAsset) { $strAssetArray[] = strtolower($objAsset->AssetCode); } $this->btnNext->Warning = sprintf("Please wait... Asset import complete: %s%s", ceil(($this->intCurrentFile+1)*200/$this->intTotalCount*100), "%"); }*/ // Loads array of AssetModelId $arrAssetModelArray = AssetModel::LoadAllIntoArray(); $arrAssetModelId = array(); if (count($arrAssetModelArray)) { foreach ($arrAssetModelArray as $arrAssetModel) { $arrAssetModelId[$arrAssetModel['asset_model_id']] = true; } } for ($j = $this->intCurrentFile; $j < count($this->strFilePathArray); $j++) { $this->FileCsvData->load($this->strFilePathArray[$j]); if (!$j) { //$this->FileCsvData->appendRow($this->FileCsvData->getHeaders()); } if ($this->intImportStep == 2) { $objNewAssetModelArray = array(); for ($i = 0; $i < $this->FileCsvData->countRows(); $i++) { $strRowArray = $this->FileCsvData->getRow($i); if (isset($strRowArray[$intModelShortDescriptionKey])) { $strShortDescription = trim($strRowArray[$intModelShortDescriptionKey]) ? addslashes(trim($strRowArray[$intModelShortDescriptionKey])) : false; } elseif (isset($this->txtMapDefaultValueArray[$intModelShortDescriptionKey])) { $strShortDescription = $this->txtMapDefaultValueArray[$intModelShortDescriptionKey]->Text; } else { $strShortDescription = false; } //$strShortDescription = (trim($strRowArray[$intModelShortDescriptionKey])) ? trim($strRowArray[$intModelShortDescriptionKey]) : false; $strAssetModelCode = trim($strRowArray[$intModelCodeKey]) ? addslashes(trim($strRowArray[$intModelCodeKey])) : addslashes(trim($this->txtMapDefaultValueArray[$intModelCodeKey]->Text)); //$strAssetModelCode = trim($strRowArray[$intModelCodeKey]) ? trim($strRowArray[$intModelCodeKey]) : trim($this->txtMapDefaultValueArray[$intModelCodeKey]->Text); $strKeyArray = array_keys($intCategoryArray, isset($strRowArray[$this->intCategoryKey]) ? strtolower(trim($strRowArray[$this->intCategoryKey])) : array()); if (count($strKeyArray)) { $intCategoryId = $strKeyArray[0]; } else { $strKeyArray = array_keys($intCategoryArray, strtolower(trim($this->txtMapDefaultValueArray[$this->intCategoryKey]->Text))); if (count($strKeyArray)) { $intCategoryId = $strKeyArray[0]; } else { $intCategoryId = false; } } $strKeyArray = array_keys($intManufacturerArray, isset($strRowArray[$this->intManufacturerKey]) ? strtolower(trim($strRowArray[$this->intManufacturerKey])) : array()); if (count($strKeyArray)) { $intManufacturerId = $strKeyArray[0]; } else { $strKeyArray = array_keys($intManufacturerArray, strtolower(trim($this->txtMapDefaultValueArray[$this->intManufacturerKey]->Text))); if (count($strKeyArray)) { $intManufacturerId = $strKeyArray[0]; } else { $intManufacturerId = false; } } // depreciation if (QApplication::$TracmorSettings->DepreciationFlag == '1' && $this->intDepreciationKey != null) { $strKeyArray = array_keys($intDepreciationClassArray, strtolower(trim($strRowArray[$this->intDepreciationKey]))); if (count($strKeyArray)) { $intDepreciationId = $strKeyArray[0]; } else { $strKeyArray = array_keys($intDepreciationClassArray, strtolower(trim($this->txtMapDefaultValueArray[$this->intDepreciationKey]->Text))); if (count($strKeyArray)) { $intDepreciationId = $strKeyArray[0]; } else { if (trim($strRowArray[$this->intDepreciationKey]) == '') { // Depreciation class is blank, so null it out $intDepreciationId = 'NULL'; } else { // Depreciation class is invalid, so skip this record $intDepreciationId = false; } } } } else { $intDepreciationId = null; } // $objAssetModel = false; if (!$strShortDescription || $intCategoryId === false || $intManufacturerId === false || $intDepreciationId === false) { //$blnError = true; //echo sprintf("Desc: %s AssetCode: %s Cat: %s Man: %s<br/>", $strShortDescription, $strAssetModelCode, $intCategoryId, $intManufacturerId); //break; $strAssetModel = null; $this->intSkippedRecordCount++; $this->PutSkippedRecordInFile($file_skipped, $strRowArray); continue; } else { //$blnError = false; $strAssetModel = strtolower(sprintf("%s_%s_%s_%s", $strAssetModelCode, $strShortDescription, $intCategoryId, $intManufacturerId)); if ($this->lstImportAction->SelectedValue == 2) { $intItemId = intval(trim($strRowArray[$this->intItemIdKey])); if ($intItemId > 0 && array_key_exists($intItemId, $arrAssetModelId)) { $objAssetModelArray = AssetModel::LoadArrayBySearchHelper(null, null, null, null, null, null, null, null, null, null, null, null, $intItemId); if ($objAssetModelArray) { $objAssetModel = $objAssetModelArray[0]; } } } else { $intItemId = 0; } } if ($strAssetModel && !$intItemId && !$this->in_array_nocase($strAssetModel, $strAssetModelArray)) { // Custom Fields Section $strCFVArray = array(); $objDatabase = CustomField::GetDatabase(); $blnCheckCFVError = false; // Asset Model Custom Field import foreach ($arrModelCustomField as $objCustomField) { if ($objCustomField->CustomFieldQtypeId != 2) { $strCSDescription = trim($strRowArray[$intModelCustomFieldKeyArray[$objCustomField->CustomFieldId]]); $strCSDescription = strlen($strCSDescription) > 0 ? addslashes($strCSDescription) : addslashes($this->txtMapDefaultValueArray[$intModelCustomFieldKeyArray[$objCustomField->CustomFieldId]]->Text); $strCFVArray[$objCustomField->CustomFieldId] = strlen($strCSDescription) > 0 ? sprintf("'%s'", $strCSDescription) : "NULL"; } else { $objDatabase = AssetModel::GetDatabase(); $strCSDescription = addslashes(trim($strRowArray[$intModelCustomFieldKeyArray[$objCustomField->CustomFieldId]])); $blnInList = false; foreach (CustomFieldValue::LoadArrayByCustomFieldId($objCustomField->CustomFieldId) as $objCustomFieldValue) { if (strtolower($objCustomFieldValue->ShortDescription) == strtolower($strCSDescription)) { //$intCustomFieldValueId = $objCustomFieldValue->CustomFieldValueId; $blnInList = true; break; } } // Add the CustomFieldValue // Removed adding new 'select' values /*if (!$blnInList && !in_array($strCSDescription, $strAddedCFVArray)) { $strQuery = sprintf("INSERT INTO custom_field_value (custom_field_id, short_description, created_by, creation_date) VALUES (%s, '%s', %s, NOW());", $objCustomField->CustomFieldId, $strCSDescription, $_SESSION['intUserAccountId']); $objDatabase->NonQuery($strQuery); $strAddedCFVArray[] = $strCSDescription; } else*/ if (!$blnInList && $this->lstMapDefaultValueArray[$intModelCustomFieldKeyArray[$objCustomField->CustomFieldId]]->SelectedValue != null) { $strCSDescription = $this->lstMapDefaultValueArray[$intModelCustomFieldKeyArray[$objCustomField->CustomFieldId]]->SelectedName; } elseif (!$blnInList) { $blnCheckCFVError = true; break; } if (!$blnCheckCFVError) { if ($strCSDescription) { $strCFVArray[$objCustomField->CustomFieldId] = sprintf("'%s'", $strCSDescription); } else { $strCFVArray[$objCustomField->CustomFieldId] = "NULL"; } } } } if (!$blnCheckCFVError) { $strAssetModelArray[] = $strAssetModel; // $this->strModelValuesArray[] = sprintf("('%s', '%s', '%s', '%s', '%s', '%s', NOW())", $strShortDescription, (isset($intModelLongDescriptionKey)) ? addslashes(trim($strRowArray[$intModelLongDescriptionKey])) : null, $strAssetModelCode, $intCategoryId, $intManufacturerId, $_SESSION['intUserAccountId']); $this->strModelValuesArray[] = sprintf("('%s', '%s', '%s', '%s', '%s', '%s', NOW(), %s)", $strShortDescription, isset($intModelLongDescriptionKey) ? addslashes(trim($strRowArray[$intModelLongDescriptionKey])) : null, $strAssetModelCode, $intCategoryId, $intManufacturerId, $_SESSION['intUserAccountId'], $intDepreciationId ? $intDepreciationId : 'NULL'); $objNewAssetModelArray[] = $strShortDescription; if (isset($strCFVArray) && count($strCFVArray)) { $strModelCFVArray[] = implode(', ', $strCFVArray); } else { $strModelCFVArray[] = ""; } } else { $this->intSkippedRecordCount++; $this->PutSkippedRecordInFile($file_skipped, $strRowArray); $strAssetModel = null; } } elseif ($strAssetModel && $this->lstImportAction->SelectedValue == 2 && $objAssetModel) { $strUpdateFieldArray = array(); $strUpdateFieldArray[] = sprintf("`manufacturer_id`='%s'", $intManufacturerId); $strUpdateFieldArray[] = sprintf("`category_id`='%s'", $intCategoryId); $strUpdateFieldArray[] = sprintf("`short_description`='%s'", $strShortDescription); $strUpdateFieldArray[] = sprintf("`asset_model_code`='%s'", $strAssetModelCode); if (QApplication::$TracmorSettings->DepreciationFlag == '1' && $this->intDepreciationKey != null && $intDepreciationId != null) { $strUpdateFieldArray[] = sprintf("`depreciation_class_id` = %s", $intDepreciationId); } $strModelLongDescription = ""; if (isset($intModelLongDescription)) { if (trim($strRowArray[$intModelLongDescriptionKey])) { $strModelLongDescription = trim($strRowArray[$intModelLongDescriptionKey]); } else { $strModelLongDescription = isset($txtMapDefaultValueArray[$intModelLongDescriptionKey]) ? trim($txtMapDefaultValueArray[$intModelLongDescriptionKey]->Text) : ''; } $strUpdateFieldArray[] = sprintf("`asset_model_long_description`='%s'", $strModelLongDescription); } $strUpdateFieldArray[] = sprintf("modified_by='%s'", $_SESSION['intUserAccountId']); $blnCheckCFVError = false; foreach ($arrModelCustomField as $objCustomField) { if ($objCustomField->CustomFieldQtypeId != 2) { $strCSDescription = isset($strRowArray[$intModelCustomFieldKeyArray[$objCustomField->CustomFieldId]]) ? trim($strRowArray[$intModelCustomFieldKeyArray[$objCustomField->CustomFieldId]]) : ""; $strCSDescription = strlen($strCSDescription) > 0 ? addslashes($strCSDescription) : addslashes($this->txtMapDefaultValueArray[$intModelCustomFieldKeyArray[$objCustomField->CustomFieldId]]->Text); $strCFVArray[$objCustomField->CustomFieldId] = strlen($strCSDescription) > 0 ? sprintf("'%s'", $strCSDescription) : "NULL"; } else { $objDatabase = CustomField::GetDatabase(); $strCSDescription = isset($strRowArray[$intModelCustomFieldKeyArray[$objCustomField->CustomFieldId]]) ? addslashes(trim($strRowArray[$intModelCustomFieldKeyArray[$objCustomField->CustomFieldId]])) : ""; $strCFVArray[$objCustomField->CustomFieldId] = $strCSDescription ? sprintf("'%s'", $strCSDescription) : "NULL"; $blnInList = false; foreach (CustomFieldValue::LoadArrayByCustomFieldId($objCustomField->CustomFieldId) as $objCustomFieldValue) { if (strtolower($objCustomFieldValue->ShortDescription) == strtolower($strCSDescription)) { //$intItemKeyntCustomFieldValueId = $objCustomFieldValue->CustomFieldValueId; $blnInList = true; break; } } if (!$blnInList && $this->lstMapDefaultValueArray[$intModelCustomFieldKeyArray[$objCustomField->CustomFieldId]]->SelectedValue != null) { $strCSDescription = $this->lstMapDefaultValueArray[$intModelCustomFieldKeyArray[$objCustomField->CustomFieldId]]->SelectedName; } elseif (!$blnInList) { $blnCheckCFVError = true; break; } if (!$blnCheckCFVError) { if ($strCSDescription) { $strCFVArray[$objCustomField->CustomFieldId] = sprintf("'%s'", $strCSDescription); } else { $strCFVArray[$objCustomField->CustomFieldId] = "NULL"; } } } } if (!$blnCheckCFVError) { $strUpdatedValuesArray[] = sprintf("UPDATE `asset_model` SET %s WHERE `asset_model_id`='%s'", implode(", ", $strUpdateFieldArray), $objAssetModel->AssetModelId); if (isset($strCFVArray) && count($strCFVArray)) { $strUpdatedItemCFVArray[$objAssetModel->AssetModelId] = $strCFVArray; } else { $strUpdatedItemCFVArray[$objAssetModel->AssetModelId] = ""; } $this->objUpdatedItemArray[$objAssetModel->AssetModelId] = sprintf("%s", $objAssetModel->ShortDescription); //$this->arrOldItemArray[$objAssetModel->AssetModelId] = $objAssetModel; // $strItemQuery = sprintf("UPDATE `asset_model` SET `short_description`='%s', `long_description`='%s', `manufacturer_id`='%s', `category_id`='%s', `asset_model_code`='%s', `modified_by`=%s, `modified_date`=%s WHERE `asset_model_id`='%s'", $objAssetModel->ShortDescription, $objAssetModel->LongDescription, $objAssetModel->ManufacturerId, $objAssetModel->CategoryId, $objAssetModel->AssetModelCode, (!$objAssetModel->ModifiedBy) ? "NULL" : $objAssetModel->ModifiedBy, (!$objAssetModel->ModifiedBy) ? "NULL" : sprintf("'%s'", $objAssetModel->ModifiedDate), $objAssetModel->AssetModelId); $strItemQuery = sprintf("UPDATE `asset_model` SET `short_description`='%s', `long_description`='%s', `manufacturer_id`='%s', `category_id`='%s', `asset_model_code`='%s', `modified_by`=%s, `modified_date`=%s, `depreciation_class_id`=%s WHERE `asset_model_id`='%s'", $objAssetModel->ShortDescription, $objAssetModel->LongDescription, $objAssetModel->ManufacturerId, $objAssetModel->CategoryId, $objAssetModel->AssetModelCode, !$objAssetModel->ModifiedBy ? "NULL" : $objAssetModel->ModifiedBy, !$objAssetModel->ModifiedBy ? "NULL" : sprintf("'%s'", $objAssetModel->ModifiedDate), $objAssetModel->DepreciationClassId ? sprintf("'%s'", $objAssetModel->DepreciationClassId) : "NULL", $objAssetModel->AssetModelId); $strCFVArray = array(); foreach ($this->arrModelCustomField as $objCustomField) { $strCFV = $objAssetModel->GetVirtualAttribute($objCustomField->CustomFieldId); $strCFVArray[] = sprintf("`cfv_%s`='%s'", $objCustomField->CustomFieldId, $strCFV); } if (isset($strCFVArray) && count($strCFVArray)) { $strCFVQuery = sprintf("UPDATE `asset_model_custom_field_helper` SET %s WHERE `asset_model_id`='%s'", implode(", ", $strCFVArray), $intItemId); } else { $strCFVQuery = false; } $this->arrOldItemArray[$objAssetModel->AssetModelId] = array("ItemSql" => $strItemQuery, "CFVSql" => $strCFVQuery); } } else { $this->intSkippedRecordCount++; $this->PutSkippedRecordInFile($file_skipped, $strRowArray); } } //if ($this->intCurrentFile == count($this->strFilePathArray)) { // Inserts if (count($this->strModelValuesArray)) { $objDatabase = AssetModel::GetDatabase(); // print sprintf("INSERT INTO `asset_model` (`short_description`, `long_description`, `asset_model_code`, `category_id`, `manufacturer_id`, `created_by`, `creation_date`, `depreciation_class_id`) VALUES %s;", str_replace('""','"', implode(", ", $this->strModelValuesArray))); // exit(); // $objDatabase->NonQuery(sprintf("INSERT INTO `asset_model` (`short_description`, `long_description`, `asset_model_code`, `category_id`, `manufacturer_id`, `created_by`, `creation_date`) VALUES %s;", str_replace('""','"', implode(", ", $this->strModelValuesArray)))); $objDatabase->NonQuery(sprintf("INSERT INTO `asset_model` (`short_description`, `long_description`, `asset_model_code`, `category_id`, `manufacturer_id`, `created_by`, `creation_date`, `depreciation_class_id`) VALUES %s;", str_replace('""', '"', implode(", ", $this->strModelValuesArray)))); $intInsertId = $objDatabase->InsertId(); if ($intInsertId) { // Update for asset custom fields with allAssetModesF Flag $arrAllAssetModelsFlaggedObjects = EntityQtypeCustomField::LoadArrayByEntityQtypeId(QApplication::Translate(EntityQtype::Asset)); foreach ($arrAllAssetModelsFlaggedObjects as $arrAllAssetModelsFlaggedObject) { if ($arrAllAssetModelsFlaggedObject->CustomField->AllAssetModelsFlag) { $newAssetCustomField = new AssetCustomFieldAssetModel(); $newAssetCustomField->CustomFieldId = $arrAllAssetModelsFlaggedObject->CustomField->CustomFieldId; $newAssetCustomField->AssetModelId = $intInsertId; $newAssetCustomField->Save(); } } $strModelIdArray = array(); $strCFVArray = array(); for ($i = 0; $i < count($objNewAssetModelArray); $i++) { $this->objNewAssetModelArray[$intInsertId + $i] = $objNewAssetModelArray[$i]; $strCFVArray[$i] = sprintf("('%s', %s)", $intInsertId + $i, $strModelCFVArray[$i]); $strModelIdArray[$i] = sprintf("(%s)", $intInsertId + $i); } $strCFVNameArray = array(); foreach ($arrModelCustomField as $objCustomField) { $strCFVNameArray[] = sprintf("`cfv_%s`", $objCustomField->CustomFieldId); } if (count($strModelCFVArray) > 0 && count($strCFVNameArray) > 0) { $strQuery = sprintf("INSERT INTO `asset_model_custom_field_helper` (`asset_model_id`, %s) VALUES %s", implode(", ", $strCFVNameArray), implode(", ", $strCFVArray)); } else { $strQuery = sprintf("INSERT INTO `asset_model_custom_field_helper` (`asset_model_id`) VALUES %s", implode(", ", $strModelIdArray)); } $objDatabase->NonQuery($strQuery); } $this->strModelValuesArray = array(); } // Updates if (count($strUpdatedValuesArray)) { $objDatabase = AssetModel::GetDatabase(); foreach ($strUpdatedValuesArray as $query) { $objDatabase->NonQuery($query); } foreach ($this->objUpdatedItemArray as $intItemKey => $objUpdatedItem) { if (isset($strUpdatedItemCFVArray[$intItemKey]) && count($strUpdatedItemCFVArray[$intItemKey])) { $strCFVArray = array(); foreach ($arrModelCustomField as $objCustomField) { $strCFVArray[] = sprintf("`cfv_%s`=%s", $objCustomField->CustomFieldId, $strUpdatedItemCFVArray[$intItemKey][$objCustomField->CustomFieldId]); } if (isset($strCFVArray) && count($strCFVArray)) { $strQuery = sprintf("UPDATE `asset_model_custom_field_helper` SET %s WHERE `asset_model_id`='%s'", implode(", ", $strCFVArray), $intItemKey); $objDatabase->NonQuery($strQuery); } } } } //} //$this->intCurrentFile++; //break; } //$j++; } if ($this->intImportStep == 3) { /*if (count($this->strSelectedValueArray)) { $objDatabase = CustomField::GetDatabase(); $strQuery = sprintf("INSERT INTO `custom_field_selection` " . "(`entity_id`,`entity_qtype_id`, `custom_field_value_id`) " . "VALUES %s;", implode(", ", $this->strSelectedValueArray)); $objDatabase->NonQuery($strQuery); }*/ // Insert Values into helper tables $objDatabase = Asset::GetDatabase(); $objDatabase->NonQuery("SET FOREIGN_KEY_CHECKS=0;"); // Insert into asset_model_custom_field_helper $objDatabase->NonQuery(sprintf("INSERT INTO `asset_model_custom_field_helper` (`asset_model_id`) (SELECT `asset_model_id` FROM `asset_model` WHERE `asset_model_id` NOT IN (SELECT `asset_model_id` FROM `asset_model_custom_field_helper`));")); // Inserts end $objDatabase->NonQuery("SET FOREIGN_KEY_CHECKS=1;"); $this->blnImportEnd = true; $this->btnNext->Warning = ""; $this->lblImportResults->Display = true; if (count($this->objNewAssetArray)) { $this->lblImportAssets->Display = true; $this->dtgAsset->Paginator = new QPaginator($this->dtgAsset); $this->dtgAsset->ItemsPerPage = 20; } if (count($this->objUpdatedAssetArray)) { $this->lblImportUpdatedAssets->Display = true; $this->dtgUpdatedAsset->Paginator = new QPaginator($this->dtgUpdatedAsset); $this->dtgUpdatedAsset->ItemsPerPage = 20; } if (count($this->objNewAssetModelArray)) { $this->lblImportModels->Display = true; $this->dtgAssetModel->Paginator = new QPaginator($this->dtgAssetModel); $this->dtgAssetModel->ItemsPerPage = 20; } $this->btnNext->Display = false; $this->btnCancel->Display = false; $this->btnUndoLastImport->Display = true; $this->btnImportMore->Display = true; $this->btnReturnToAssets->Display = true; $this->lblImportSuccess->Display = true; $this->lblImportSuccess->Text = sprintf("Success:<br/>" . "<b>%s</b> Records imported successfully<br/>" . "<b>%s</b> Records skipped due to error<br/>", count($this->objNewAssetModelArray) + count($this->objUpdatedItemArray), $this->intSkippedRecordCount); if ($this->intSkippedRecordCount) { $this->lblImportSuccess->Text .= sprintf("<a href='./asset_model_import.php?intDownloadCsv=1'>Click here to download records that could not be imported</a>"); } $this->lblImportSuccess->Text .= "<br/><br/>"; $this->intImportStep = -1; } // Enable Next button $this->btnNext->Enabled = true; if (!$this->blnImportEnd && !$this->intCurrentFile) { $this->intImportStep++; } } fclose($file_skipped); } if (!$blnError) { if (($this->blnImportEnd || $this->intImportStep == 2) && $this->intImportStep != -1) { $this->intStep++; $this->DisplayStepForm($this->intStep); } if (!$this->blnImportEnd) { QApplication::ExecuteJavaScript("document.getElementById('" . $this->btnNext->ControlId . "').click();"); } if (!($this->intCurrentFile < count($this->strFilePathArray))) { $this->intCurrentFile = 0; $this->intImportStep++; } } }
protected function btnNext_Click() { $blnError = false; $this->btnNext->Warning = ''; if ($this->intStep == 1) { if ($this->chkHeaderRow->Checked) { $this->blnHeaderRow = true; } else { $this->blnHeaderRow = false; } // Check errors if ($this->lstFieldSeparator->SelectedValue == 'other' && !$this->txtFieldSeparator->Text) { $this->flcFileCsv->Warning = "Please enter the field separator."; $blnError = true; } elseif ($this->lstTextDelimiter->SelectedValue == 'other' && !$this->txtTextDelimiter->Text) { $this->flcFileCsv->Warning = "Please enter the text delimiter."; $blnError = true; } else { // Step 1 complete // File Not Uploaded if (!file_exists($this->flcFileCsv->File) || !$this->flcFileCsv->Size) { //throw new QCallerException('FileAssetType must be a valid QFileAssetType constant value'); $this->flcFileCsv->Warning = 'The file could not be uploaded. Please provide a valid file.'; $blnError = true; // File Has Incorrect MIME Type (only if an acceptiblemimearray is setup) } elseif (is_array($this->strAcceptibleMimeArray) && !array_key_exists($this->flcFileCsv->Type, $this->strAcceptibleMimeArray)) { $this->flcFileCsv->Warning = "Extension must be 'csv' or 'txt'"; $blnError = true; // File Successfully Uploaded } else { $this->flcFileCsv->Warning = ""; // Setup Filename, Base Filename and Extension $strFilename = $this->flcFileCsv->FileName; $intPosition = strrpos($strFilename, '.'); } if (!$blnError) { $this->FileCsvData = new File_CSV_DataSource(); // Setup the settings which have got on step 1 $this->FileCsvData->settings($this->GetCsvSettings()); $file = fopen($this->flcFileCsv->File, "r"); // Counter of files $i = 1; // Counter of rows $j = 1; $this->strFilePathArray = array(); // The uploaded file splits up in order to avoid out of memory while ($row = fgets($file, 1000)) { if ($j == 1) { $strFilePath = sprintf('%s/%s_%s.csv', __DOCROOT__ . __SUBDIRECTORY__ . __TRACMOR_TMP__, $_SESSION['intUserAccountId'], $i); $this->strFilePathArray[] = $strFilePath; $file_part = fopen($strFilePath, "w+"); if ($i == 1) { $strHeaderRow = $row; } else { fwrite($file_part, $strHeaderRow); } } fwrite($file_part, $row); $j++; if ($j > 200) { $j = 1; $i++; fclose($file_part); } } $this->intTotalCount = ($i - 1) * 200 + $j - 1; $this->intTotalCount -= $this->chkHeaderRow->Checked ? 1 : 0; if ($this->lstImportAction->SelectedValue == 1 && $this->intAssetLimit != null && $this->intAssetLimit < $this->intTotalCount + $this->intAssetCount) { $blnError = true; $this->btnNext->Warning = sprintf('This import of %s assets would exceed your limit of %s assets.', $this->intTotalCount, $this->intAssetLimit); } else { $this->arrMapFields = array(); $this->arrTracmorField = array(); // Load first file $this->FileCsvData->load($this->strFilePathArray[0]); $file_skipped = fopen($this->strFilePath = sprintf('%s/%s_skipped.csv', __DOCROOT__ . __SUBDIRECTORY__ . __TRACMOR_TMP__, $_SESSION['intUserAccountId']), "w+"); // Get Headers if ($this->blnHeaderRow) { $this->arrCsvHeader = $this->FileCsvData->getHeaders(); // Create the header row in the skipped error file $this->PutSkippedRecordInFile($file_skipped, $this->arrCsvHeader); } /*else { // If it is not first file $this->FileCsvData->appendRow($this->FileCsvData->getHeaders()); }*/ $strFirstRowArray = $this->FileCsvData->getRow(0); for ($i = 0; $i < count($strFirstRowArray); $i++) { $this->arrMapFields[$i] = array(); if ($this->blnHeaderRow && array_key_exists($i, $this->arrCsvHeader)) { if ($this->arrCsvHeader[$i] == '') { $this->arrCsvHeader[$i] = ' '; } $this->lstMapHeader_Create($this, $i, $this->arrCsvHeader[$i]); $this->arrMapFields[$i]['header'] = $this->arrCsvHeader[$i]; } else { $this->lstMapHeader_Create($this, $i); } // Create Default Value TextBox, ListBox and DateTimePicker if ($this->blnHeaderRow && array_key_exists($i, $this->arrCsvHeader) && $this->arrCsvHeader[$i] || !$this->blnHeaderRow) { $txtDefaultValue = new QTextBox($this); $txtDefaultValue->Width = 200; $this->txtMapDefaultValueArray[] = $txtDefaultValue; $lstDefaultValue = new QListBox($this); $lstDefaultValue->Width = 200; $lstDefaultValue->Display = false; $this->lstMapDefaultValueArray[] = $lstDefaultValue; $dtpDate = new QDateTimePicker($this); $dtpDate->DateTimePickerType = QDateTimePickerType::Date; $dtpDate->DateTimePickerFormat = QDateTimePickerFormat::MonthDayYear; $dtpDate->Display = false; $this->dtpDateArray[] = $dtpDate; if (array_key_exists($i, $this->lstMapHeaderArray)) { $this->lstTramorField_Change(null, $this->lstMapHeaderArray[$i]->ControlId, null); } } $this->arrMapFields[$i]['row1'] = $strFirstRowArray[$i]; } $this->btnNext->Text = "Import Now"; fclose($file_skipped); // Create Add Field button $btnAddField = new QButton($this); $btnAddField->Text = "Add Field"; $btnAddField->AddAction(new QClickEvent(), new QServerAction('btnAddField_Click')); $btnAddField->AddAction(new QEnterKeyEvent(), new QServerAction('btnAddField_Click')); $btnAddField->AddAction(new QEnterKeyEvent(), new QTerminateAction()); $this->lstMapHeaderArray[] = $btnAddField; } } } } elseif ($this->intStep == 2) { // Step 2 complete $blnRequiredAllAssetCustomFields = true; $blnError = false; $blnAssetCode = false; $blnAssetModelShortDescription = false; $blnLocation = false; $blnAssetId = false; // $array Required Custom Fields for All Asset Models $arrAssetCustomFieldOptions = EntityQtypeCustomField::LoadArrayByEntityQtypeId(QApplication::Translate(EntityQtype::Asset)); // generate error flag and field names which are required for export; $arrRequiredAllAssetCustomFields = array(); foreach ($arrAssetCustomFieldOptions as $arrAssetCustomFieldOption) { if ($arrAssetCustomFieldOption->CustomField->RequiredFlag && $arrAssetCustomFieldOption->CustomField->ActiveFlag && $arrAssetCustomFieldOption->CustomField->AllAssetModelsFlag) { $arrRequiredAllAssetCustomFields[] = $arrAssetCustomFieldOption->CustomField->ShortDescription; if (!in_array($arrAssetCustomFieldOption->CustomField->ShortDescription, $this->getLstKeys())) { $blnRequiredAllAssetCustomFields = false; } } } // Checking errors (Model Short Description, Model Code, Category and Manufacturer must be selected) for ($i = 0; $i < count($this->lstMapHeaderArray) - 1; $i++) { $lstMapHeader = $this->lstMapHeaderArray[$i]; $strSelectedValue = strtolower($lstMapHeader->SelectedValue); if ($strSelectedValue == "model") { $blnAssetModelShortDescription = true; } elseif ($strSelectedValue == "asset tag") { $blnAssetCode = true; } elseif ($strSelectedValue == "location") { $blnLocation = true; } elseif ($strSelectedValue == "id") { $blnAssetId = true; } } if ($this->lstMapDefaultValueArray) { // Checking errors for required Default Value text fields foreach ($this->lstMapDefaultValueArray as $lstDefault) { if ($lstDefault->Display && $lstDefault->Required && !$lstDefault->SelectedValue) { $lstDefault->Warning = "You must select one default value."; $blnError = true; break; } else { $blnError = false; $lstDefault->Warning = ""; } } } if ($this->txtMapDefaultValueArray) { // Checking errors for required Default Value lst fields foreach ($this->txtMapDefaultValueArray as $txtDefault) { if ($txtDefault->Display && $txtDefault->Required && !$txtDefault->Text) { $txtDefault->Warning = "You must enter default value."; break; } else { $blnError = false; $txtDefault->Warning = ""; } } } // If all required fields have no errors if (!$blnError && $blnRequiredAllAssetCustomFields && $blnAssetCode && $blnAssetModelShortDescription && $blnLocation && ($this->lstImportAction->SelectedValue != 2 || $blnAssetId)) { $this->btnNext->Warning = ""; // Setup keys for main required fields foreach ($this->arrTracmorField as $key => $value) { /*if ($value == 'category') { $this->intCategoryKey = $key; } elseif ($value == 'manufacturer') { $this->intManufacturerKey = $key; } else*/ if ($this->lstImportAction->SelectedValue == 2 && $value == 'id') { $this->intItemIdKey = $key; } /*elseif ($value == 'created by') { $this->intCreatedByKey = $key; } elseif ($value == 'created date') { $this->intCreatedDateKey = $key; } elseif ($value == 'modified by') { $this->intModifiedByKey = $key; } elseif ($value == 'modified date') { $this->intModifiedDateKey = $key; }*/ } $this->objNewAssetArray = array(); $this->strAssetValuesArray = array(); $this->blnImportEnd = false; $j = 1; $this->btnNext->RemoveAllActions('onclick'); // Add new ajax actions for button $this->btnNext->AddAction(new QClickEvent(), new QAjaxAction('btnNext_Click')); $this->btnNext->AddAction(new QClickEvent(), new QToggleEnableAction($this->btnNext)); $this->btnNext->AddAction(new QEnterKeyEvent(), new QAjaxAction('btnNext_Click')); $this->btnNext->AddAction(new QEnterKeyEvent(), new QToggleEnableAction($this->btnNext)); $this->btnNext->AddAction(new QEnterKeyEvent(), new QTerminateAction()); $this->btnNext->Warning = "Please wait..."; $this->intImportStep = 2; $this->intCurrentFile = 0; $this->strSelectedValueArray = array(); // New asset models $this->dtgAsset = new QDataGrid($this); $this->dtgAsset->Name = 'asset_list'; $this->dtgAsset->CellPadding = 5; $this->dtgAsset->CellSpacing = 0; $this->dtgAsset->CssClass = "datagrid"; $this->dtgAsset->UseAjax = true; $this->dtgAsset->ShowColumnToggle = false; $this->dtgAsset->ShowExportCsv = false; $this->dtgAsset->ShowHeader = false; $this->dtgAsset->AddColumn(new QDataGridColumnExt('Model', '<?= $_ITEM ?>', 'CssClass="dtg_column"', 'HtmlEntities="false"')); // Updated assets $this->dtgUpdatedAsset = new QDataGrid($this); $this->dtgUpdatedAsset->Name = 'updated_asset_list'; $this->dtgUpdatedAsset->CellPadding = 5; $this->dtgUpdatedAsset->CellSpacing = 0; $this->dtgUpdatedAsset->CssClass = "datagrid"; $this->dtgUpdatedAsset->UseAjax = true; $this->dtgUpdatedAsset->ShowColumnToggle = false; $this->dtgUpdatedAsset->ShowExportCsv = false; $this->dtgUpdatedAsset->ShowHeader = false; $this->dtgUpdatedAsset->AddColumn(new QDataGridColumnExt('Asset Tag', '<?= $_ITEM ?>', 'CssClass="dtg_column"', 'HtmlEntities="false"')); // Create the label for successful import $this->lblImportSuccess = new QLabel($this); $this->lblImportSuccess->HtmlEntities = false; $this->lblImportSuccess->Display = false; // Undo Last Import button $this->btnUndoLastImport = new QButton($this); $this->btnUndoLastImport->Text = "Undo Last Import"; $this->btnUndoLastImport->Display = false; $this->btnUndoLastImport->AddAction(new QClickEvent(), new QServerAction('btnCancel_Click')); $this->btnUndoLastImport->AddAction(new QEnterKeyEvent(), new QServerAction('btnCancel_Click')); $this->btnUndoLastImport->AddAction(new QEnterKeyEvent(), new QTerminateAction()); // Import More button $this->btnImportMore = new QButton($this); $this->btnImportMore->Text = "Import More"; $this->btnImportMore->Display = false; $this->btnImportMore->AddAction(new QClickEvent(), new QServerAction('btnImportMore_Click')); $this->btnImportMore->AddAction(new QEnterKeyEvent(), new QServerAction('btnImportMore_Click')); $this->btnImportMore->AddAction(new QEnterKeyEvent(), new QTerminateAction()); // Return to Assets button $this->btnReturnToAssets = new QButton($this); $this->btnReturnToAssets->Text = "Return to Assets"; $this->btnReturnToAssets->Display = false; $this->btnReturnToAssets->AddAction(new QClickEvent(), new QServerAction('btnReturnToAssets_Click')); $this->btnReturnToAssets->AddAction(new QEnterKeyEvent(), new QServerAction('btnReturnToAssets_Click')); $this->btnReturnToAssets->AddAction(new QEnterKeyEvent(), new QTerminateAction()); } else { $strRequiredAllAssetCustomFields = ''; if (count($arrRequiredAllAssetCustomFields) > 0) { $strRequiredAllAssetCustomFields = implode(", ", $arrRequiredAllAssetCustomFields) . ", "; } $this->btnNext->Warning = "You must select all required fields (" . $strRequiredAllAssetCustomFields . "Asset Tag, Model Short Description and Location)."; $blnError = true; } } else { // Step 3 complete set_time_limit(0); $file_skipped = fopen($strFilePath = sprintf('%s/%s_skipped.csv', __DOCROOT__ . __SUBDIRECTORY__ . __TRACMOR_TMP__, $_SESSION['intUserAccountId']), "a"); if (!$this->blnImportEnd) { // Asset if ($this->intImportStep == 2) { $intCategoryArray = array(); // Load all categories with key=category_id /*foreach (Category::LoadAllWithFlags(true, false) as $objCategory) { $intCategoryArray[$objCategory->CategoryId] = strtolower($objCategory->ShortDescription); } $intManufacturerArray = array(); // Load all manufacturers with key=manufacturer_id foreach (Manufacturer::LoadAll() as $objManufacturer) { $intManufacturerArray[$objManufacturer->ManufacturerId] = strtolower($objManufacturer->ShortDescription); }*/ $intAssetCustomFieldKeyArray = array(); $arrAssetCustomField = array(); // Setup keys foreach ($this->arrTracmorField as $key => $value) { if ($value == 'asset tag') { $intAssetCodeKey = $key; } elseif ($value == 'model') { $intAssetModelDescriptionKey = $key; } elseif ($value == 'location') { $intLocationKey = $key; } elseif ($value == 'parent asset') { $intParentAssetKey = $key; } elseif ($value == 'locked to parent') { $intLinkedKey = $key; } elseif (substr($value, 0, 6) == 'asset_') { $intAssetCustomFieldKeyArray[substr($value, 6)] = $key; if (array_key_exists(substr($value, 6), $this->arrAssetCustomField)) { $arrAssetCustomField[substr($value, 6)] = $this->arrAssetCustomField[substr($value, 6)]; } } elseif (QApplication::$TracmorSettings->DepreciationFlag == '1' && $value == "depreciate asset") { $this->intDepreciationClassKey = $key; } elseif (QApplication::$TracmorSettings->DepreciationFlag == '1' && $value == "purchase cost") { $this->intPurchaseCostKey = $key; } elseif (QApplication::$TracmorSettings->DepreciationFlag == '1' && $value == "purchase date") { $this->intPurchaseDateKey = $key; } } $intAssetModelArray = array(); $strItemCFVArray = array(); $strUpdatedItemCFVArray = array(); $strUpdatedValuesArray = array(); $this->arrOldItemArray = array(); $this->objUpdatedItemArray = array(); // Load all asset models foreach (AssetModel::LoadAllIntoArray() as $arrAssetModel) { //$strAssetModelArray[] = strtolower(sprintf("%s_%s_%s_%s", addslashes($arrAssetModel['model_code']), addslashes($arrAssetModel['short_description']), $arrAssetModel['category_id'], $arrAssetModel['manufacturer_id'])); $intAssetModelArray[$arrAssetModel['asset_model_id']] = strtolower($arrAssetModel['short_description']); } $intLocationArray = array(); // Load all locations with keys=location_id foreach (Location::LoadAll() as $objLocation) { $intLocationArray[$objLocation->LocationId] = strtolower($objLocation->ShortDescription); } // Depreciation if (QApplication::$TracmorSettings->DepreciationFlag == '1') { foreach (DepreciationClass::LoadAll() as $objDepreciationClass) { $this->intDepreciationClassArray[$objDepreciationClass->DepreciationClassId] = strtolower($objDepreciationClass->ShortDescription); } } $strAssetArray = array(); // Load all assets // Loads array of AssetModelId $arrAssetArray = Asset::LoadAllIntoArray(); $arrAssetId = array(); if (count($arrAssetArray)) { foreach ($arrAssetArray as $arrAsset) { $arrAssetId[$arrAsset['asset_id']] = addslashes(strtolower($arrAsset['asset_code'])); $strAssetArray[] = addslashes(strtolower($arrAsset['asset_code'])); } } } for ($j = $this->intCurrentFile; $j < count($this->strFilePathArray); $j++) { $this->FileCsvData->load($this->strFilePathArray[$j]); if (!$j) { //$this->FileCsvData->appendRow($this->FileCsvData->getHeaders()); } if ($this->intImportStep == 2) { $strAssetCFVArray = array(); $objNewAssetArray = array(); for ($i = 0; $i < $this->FileCsvData->countRows(); $i++) { $strRowArray = $this->FileCsvData->getRow($i); $strAssetCode = trim($strRowArray[$intAssetCodeKey]) ? addslashes(trim($strRowArray[$intAssetCodeKey])) : false; //$strAssetCode = (trim($strRowArray[$intAssetCodeKey])) ? trim($strRowArray[$intAssetCodeKey]) : false; $strKeyArray = array_keys($intLocationArray, isset($strRowArray[$intLocationKey]) ? strtolower(trim($strRowArray[$intLocationKey])) : array()); if (count($strKeyArray)) { $intLocationId = $strKeyArray[0]; } else { $strKeyArray = array_keys($intLocationArray, strtolower(trim($this->txtMapDefaultValueArray[$intLocationKey]->Text))); if (count($strKeyArray)) { $intLocationId = $strKeyArray[0]; } else { $intLocationId = false; } } $strKeyArray = array_keys($intAssetModelArray, strtolower(trim($strRowArray[$intAssetModelDescriptionKey]))); if (count($strKeyArray)) { $intAssetModelId = $strKeyArray[0]; } else { $strKeyArray = array_keys($intAssetModelArray, strtolower(trim($this->txtMapDefaultValueArray[$intAssetModelDescriptionKey]->Text))); if (count($strKeyArray)) { $intAssetModelId = $strKeyArray[0]; } else { $intAssetModelId = false; } } $blnError = false; // Skip records with not filled required custom fields $log = ''; if ($intAssetModelId) { foreach ($intAssetCustomFieldKeyArray as $k => $v) { $log .= 'k' . $k . '=' . $v; } // Load Specific asset model custom field $arrRequiredSpecificAssetCustomFields = AssetCustomFieldAssetModel::LoadArrayByAssetModelId($intAssetModelId); foreach ($arrRequiredSpecificAssetCustomFields as $objRequiredCustomField) { if ($objRequiredCustomField->CustomField->RequiredFlag) { $log .= $objRequiredCustomField->CustomField->CustomFieldId; if (!array_key_exists($objRequiredCustomField->CustomField->CustomFieldId, $intAssetCustomFieldKeyArray) || empty($strRowArray[array_search($objRequiredCustomField->CustomField->CustomFieldId, $intAssetCustomFieldKeyArray)])) { $blnError = true; $this->intImportStep = 3; // $log .= "<h1>".$objRequiredCustomField->CustomField->ShortDescription.$objRequiredCustomField->CustomField->CustomFieldId." is empty</h1>"; } } } } //print $log; exit; if (!$blnError) { if (isset($intParentAssetKey) && isset($strRowArray[$intParentAssetKey])) { $strKeyArray = array_keys($arrAssetId, strtolower(trim($strRowArray[$intParentAssetKey]))); if (count($strKeyArray)) { $intParentAssetId = $strKeyArray[0]; } else { $strKeyArray = array_keys($arrAssetId, strtolower(trim($this->txtMapDefaultValueArray[$intParentAssetKey]->Text))); if (count($strKeyArray)) { $intParentAssetId = $strKeyArray[0]; } else { $intParentAssetId = null; $blnError = true; } } if (!$intParentAssetId || $strRowArray[$intParentAssetKey] == $strAssetCode) { $blnError = true; } $blnLinked = $intParentAssetId && isset($intLinkedKey) && strlen(trim($strRowArray[$intLinkedKey])) > 0 ? 1 : 0; } else { $intParentAssetId = null; $blnLinked = 0; } } /*$strKeyArray = array_keys($intCategoryArray, strtolower(trim($strRowArray[$this->intCategoryKey]))); if (count($strKeyArray)) { $intCategoryId = $strKeyArray[0]; } else { $strKeyArray = array_keys($intCategoryArray, strtolower(trim($this->txtMapDefaultValueArray[$this->intCategoryKey]->Text))); if (count($strKeyArray)) { $intCategoryId = $strKeyArray[0]; } else { $intCategoryId = false; } } $strKeyArray = array_keys($intManufacturerArray, strtolower(trim($strRowArray[$this->intManufacturerKey]))); if (count($strKeyArray)) { $intManufacturerId = $strKeyArray[0]; } else { $strKeyArray = array_keys($intManufacturerArray, strtolower(trim($this->txtMapDefaultValueArray[$this->intManufacturerKey]->Text))); if (count($strKeyArray)) { $intManufacturerId = $strKeyArray[0]; } else { $intManufacturerId = false; } }*/ // If depreciation is enabled within Application // Any non-empty value sets to 1 // If this is checked and no depreciation class short description is not corresponding asset model default depreciation class, this will skip due to error $blnDepreciationError = false; $blnDepreciationFlag = null; $intDepreciationClassId = null; $intPurchaseCost = null; $dttPurchaseDate = null; if (QApplication::$TracmorSettings->DepreciationFlag == '1' && $this->intDepreciationClassKey) { // print($this->intDepreciationClassKey)."__".$this->intPurchaseCostKey."__".$this->intPurchaseDateKey."__".strtolower(trim($strRowArray[$this->intDepreciationClassKey]))."<br />" ; if(!empty($strRowArray[$this->intDepreciationClassKey])){exit;} $strKeyArray = array_keys($this->intDepreciationClassArray, strtolower(trim($strRowArray[$this->intDepreciationClassKey]))); if (count($strKeyArray) > 0) { $intDepreciationClassId = $strKeyArray[0]; } else { $strKeyArray = array_keys($this->intDepreciationClassArray, strtolower(trim($this->txtMapDefaultValueArray[$this->intDepreciationClassKey]->Text))); if (count($strKeyArray)) { $intDepreciationClassId = $strKeyArray[0]; } else { $intDepreciationClassId = false; } } if ($intDepreciationClassId > 0 && $intAssetModelId > 0) { if ($intDepreciationClassId != AssetModel::Load($intAssetModelId)->DepreciationClassId) { $blnDepreciationError = true; } elseif (isset($this->intPurchaseCostKey) && isset($this->intPurchaseCostKey)) { // Check intVal for Purchase cost $intPurchaseCost = trim($strRowArray[$this->intPurchaseCostKey]) ? addslashes(trim($strRowArray[$this->intPurchaseCostKey])) : false; if (!is_numeric($intPurchaseCost)) { $blnDepreciationError = true; } $strPurchaseDate = trim($strRowArray[$this->intPurchaseDateKey]) ? trim($strRowArray[$this->intPurchaseDateKey]) : false; // Check isDate for Purchase date if (!($dttPurchaseDate = DateTime::createFromFormat('M d Y g:i A', $strPurchaseDate))) { $blnDepreciationError = true; } else { $dttPurchaseDate = $dttPurchaseDate->format('Y-m-d h:i:s'); // print $intDepreciationClassId."__".$intPurchaseCost."__".$dttPurchaseDate; exit; } $blnDepreciationFlag = 1; } else { $blnDepreciationError = true; } } } $objAsset = false; if (!$strAssetCode || $blnError || $intAssetModelId === false || $intLocationId === false || $blnDepreciationError) { //$blnError = true; //echo sprintf("Desc: %s AssetCode: %s Cat: %s Man: %s<br/>", $strAssetCode, $strLocation, $intCategoryId, $intManufacturerId); $strAssetCode = null; $this->intSkippedRecordCount++; $this->PutSkippedRecordInFile($file_skipped, $strRowArray); } else { if ($this->lstImportAction->SelectedValue == 2) { $intItemId = intval(trim($strRowArray[$this->intItemIdKey])); if ($intItemId > 0 && array_key_exists($intItemId, $arrAssetId)) { //QApplication::$Database[1]->EnableProfiling(); $objAssetArray = Asset::LoadArrayBySearchHelper(null, null, null, null, null, false, null, null, null, null, null, null, null, null, null, false, null, null, null, false, false, false, null, null, false, $intItemId); //QApplication::$Database[1]->OutputProfiling(); if ($objAssetArray) { $objAsset = $objAssetArray[0]; } } } else { $intItemId = 0; } } if ($strAssetCode && !$intItemId && !$this->in_array_nocase($strAssetCode, $strAssetArray)) { // Custom Fields Section $strCFVArray = array(); $objDatabase = CustomField::GetDatabase(); $blnCheckCFVError = false; // Asset Model Custom Field import foreach ($arrAssetCustomField as $objCustomField) { if ($objCustomField->CustomFieldQtypeId != 2) { $strCSDescription = isset($strRowArray[$intAssetCustomFieldKeyArray[$objCustomField->CustomFieldId]]) ? trim($strRowArray[$intAssetCustomFieldKeyArray[$objCustomField->CustomFieldId]]) : ""; $strCSDescription = strlen($strCSDescription) > 0 ? addslashes($strCSDescription) : addslashes($this->txtMapDefaultValueArray[$intAssetCustomFieldKeyArray[$objCustomField->CustomFieldId]]->Text); $strCFVArray[$objCustomField->CustomFieldId] = strlen($strCSDescription) > 0 ? sprintf("'%s'", $strCSDescription) : "NULL"; } else { $objDatabase = Asset::GetDatabase(); $strCSDescription = isset($strRowArray[$intAssetCustomFieldKeyArray[$objCustomField->CustomFieldId]]) ? addslashes(trim($strRowArray[$intAssetCustomFieldKeyArray[$objCustomField->CustomFieldId]])) : ""; $blnInList = false; foreach (CustomFieldValue::LoadArrayByCustomFieldId($objCustomField->CustomFieldId) as $objCustomFieldValue) { if (strtolower($objCustomFieldValue->ShortDescription) == strtolower($strCSDescription)) { //$intCustomFieldValueId = $objCustomFieldValue->CustomFieldValueId; $blnInList = true; break; } } // Add the CustomFieldValue // Removed adding new 'select' values /*if (!$blnInList && !in_array($strCSDescription, $strAddedCFVArray)) { $strQuery = sprintf("INSERT INTO custom_field_value (custom_field_id, short_description, created_by, creation_date) VALUES (%s, '%s', %s, NOW());", $objCustomField->CustomFieldId, $strCSDescription, $_SESSION['intUserAccountId']); $objDatabase->NonQuery($strQuery); $strAddedCFVArray[] = $strCSDescription; } else*/ if (!$blnInList && $this->lstMapDefaultValueArray[$intAssetCustomFieldKeyArray[$objCustomField->CustomFieldId]]->SelectedValue != null) { $strCSDescription = $this->lstMapDefaultValueArray[$intAssetCustomFieldKeyArray[$objCustomField->CustomFieldId]]->SelectedName; } elseif (!$blnInList) { $blnCheckCFVError = true; break; } if (!$blnCheckCFVError) { if ($strCSDescription) { $strCFVArray[$objCustomField->CustomFieldId] = sprintf("'%s'", $strCSDescription); } else { $strCFVArray[$objCustomField->CustomFieldId] = "NULL"; } } } } // Check if asset limit has been reached $blnAssetLimitError = $this->intAssetLimit != null && $this->intAssetCount + count($objNewAssetArray) >= $this->intAssetLimit; if (!$blnCheckCFVError && !$blnAssetLimitError) { $strAssetArray[] = stripslashes($strAssetCode); $this->strAssetValuesArray[] = sprintf("('%s', '%s', '%s', %s, %s, '%s', NOW(), %s, %s, '%s')", $strAssetCode, $intLocationId, $intAssetModelId, $intParentAssetId ? $intParentAssetId : "NULL", $blnLinked ? "1" : "0", $_SESSION['intUserAccountId'], $blnDepreciationFlag ? $blnDepreciationFlag : "NULL", $intPurchaseCost ? $intPurchaseCost : "NULL", $dttPurchaseDate ? '"' . $dttPurchaseDate . '"' : "NULL"); $objNewAssetArray[] = stripslashes($strAssetCode); if (isset($strCFVArray) && count($strCFVArray)) { $strAssetCFVArray[] = str_replace('""', '"', implode(', ', $strCFVArray)); /*if ($j == 2) { echo "strAssetCFVArray: "; print_r($strAssetCFVArray); echo "strCFVArray: "; print_r($strCFVArray); exit; }*/ } else { $strAssetCFVArray[] = ""; } } else { $this->intSkippedRecordCount++; if ($blnAssetLimitError) { $strRowArray[] = sprintf('Asset limit of %s reached', $this->intAssetLimit); } $this->PutSkippedRecordInFile($file_skipped, $strRowArray); $strAssetCode = null; } } elseif ($strAssetCode && $this->lstImportAction->SelectedValue == 2 && $objAsset) { // Import Action is "Create and Update Records" $strUpdateFieldArray = array(); $strUpdateFieldArray[] = sprintf("`asset_code`='%s'", $strAssetCode); $strUpdateFieldArray[] = sprintf("`asset_model_id`='%s'", $intAssetModelId); $strUpdateFieldArray[] = sprintf("`parent_asset_id`=%s", QApplication::$Database[1]->SqlVariable($intParentAssetId)); $strUpdateFieldArray[] = sprintf("`linked_flag`='%s'", $blnLinked ? 1 : 0); $strUpdateFieldArray[] = sprintf("`modified_by`='%s'", $_SESSION['intUserAccountId']); // Depreciation Fields $strUpdateFieldArray[] = sprintf("`depreciation_flag`=%s", $blnDepreciationFlag ? $blnDepreciationFlag : "NULL"); $strUpdateFieldArray[] = sprintf("`purchase_cost`=%s", $intPurchaseCost ? $intPurchaseCost : "NULL"); $strUpdateFieldArray[] = sprintf("`purchase_date`=%s", $dttPurchaseDate ? $dttPurchaseDate : "NULL"); $blnCheckCFVError = false; foreach ($arrAssetCustomField as $objCustomField) { if ($objCustomField->CustomFieldQtypeId != 2) { $strCSDescription = isset($strRowArray[$intAssetCustomFieldKeyArray[$objCustomField->CustomFieldId]]) ? trim($strRowArray[$intAssetCustomFieldKeyArray[$objCustomField->CustomFieldId]]) : ""; $strCSDescription = strlen($strCSDescription) > 0 ? addslashes($strCSDescription) : addslashes($this->txtMapDefaultValueArray[$intAssetCustomFieldKeyArray[$objCustomField->CustomFieldId]]->Text); $strCFVArray[$objCustomField->CustomFieldId] = strlen($strCSDescription) > 0 ? sprintf("'%s'", $strCSDescription) : "NULL"; } else { $objDatabase = CustomField::GetDatabase(); $strCSDescription = isset($strRowArray[$intAssetCustomFieldKeyArray[$objCustomField->CustomFieldId]]) ? addslashes(trim($strRowArray[$intAssetCustomFieldKeyArray[$objCustomField->CustomFieldId]])) : ""; $strCFVArray[$objCustomField->CustomFieldId] = $strCSDescription ? sprintf("'%s'", $strCSDescription) : "NULL"; $blnInList = false; foreach (CustomFieldValue::LoadArrayByCustomFieldId($objCustomField->CustomFieldId) as $objCustomFieldValue) { if (strtolower($objCustomFieldValue->ShortDescription) == strtolower($strCSDescription)) { //$intItemKeyntCustomFieldValueId = $objCustomFieldValue->CustomFieldValueId; $blnInList = true; break; } } if (!$blnInList && $this->lstMapDefaultValueArray[$intAssetCustomFieldKeyArray[$objCustomField->CustomFieldId]]->SelectedValue != null) { $strCSDescription = $this->lstMapDefaultValueArray[$intAssetCustomFieldKeyArray[$objCustomField->CustomFieldId]]->SelectedName; } elseif (!$blnInList) { $blnCheckCFVError = true; break; } if (!$blnCheckCFVError) { if ($strCSDescription) { $strCFVArray[$objCustomField->CustomFieldId] = sprintf("'%s'", $strCSDescription); } else { $strCFVArray[$objCustomField->CustomFieldId] = "NULL"; } } } } // The user can not change location if ($intLocationId != $objAsset->LocationId) { $blnCheckCFVError = true; } if (!$blnCheckCFVError) { $strUpdatedValuesArray[] = sprintf("UPDATE `asset` SET %s WHERE `asset_id`='%s'", str_replace('""', '"', implode(", ", $strUpdateFieldArray)), $objAsset->AssetId); if (isset($strCFVArray) && count($strCFVArray)) { $strUpdatedItemCFVArray[$objAsset->AssetId] = $strCFVArray; } else { $strUpdatedItemCFVArray[$objAsset->AssetId] = ""; } $this->objUpdatedItemArray[$objAsset->AssetId] = sprintf("%s", $objAsset->AssetCode); //$this->arrOldItemArray[$objAsset->AssetId] = $objAsset; //$strItemQuery = sprintf("UPDATE `asset` SET `asset_code`='%s', `asset_model_id`='%s', `parent_asset_id`=%s, `linked_flag`='%s', `modified_by`=%s, `modified_date`=%s WHERE `asset_id`='%s'", $objAsset->AssetCode, $objAsset->AssetModelId, (!$objAsset->ParentAssetId) ? "NULL" : $objAsset->ParentAssetId, $objAsset->LinkedFlag, (!$objAsset->ModifiedBy) ? "NULL" : $objAsset->ModifiedBy, (!$objAsset->ModifiedBy) ? "NULL" : sprintf("'%s'", $objAsset->ModifiedDate), $objAsset->AssetId); $strItemQuery = sprintf("UPDATE `asset` SET `asset_code`='%s', `asset_model_id`='%s', `parent_asset_id`=%s, `linked_flag`='%s', `modified_by`=%s, `modified_date`=%s, `depreciation_flag`=%s, `purchase_cost`=%s, `purchase_date`=%s WHERE `asset_id`='%s'", $objAsset->AssetCode, $objAsset->AssetModelId, !$objAsset->ParentAssetId ? "NULL" : $objAsset->ParentAssetId, $objAsset->LinkedFlag, !$objAsset->ModifiedBy ? "NULL" : $objAsset->ModifiedBy, !$objAsset->ModifiedBy ? "NULL" : sprintf("'%s'", $objAsset->ModifiedDate), $blnDepreciationFlag ? $blnDepreciationFlag : "NULL", $intPurchaseCost ? $intPurchaseCost : "NULL", $dttPurchaseDate ? '"' . $dttPurchaseDate . '"' : "NULL", $objAsset->AssetId); $strCFVArray = array(); foreach ($this->arrAssetCustomField as $objCustomField) { $strCFV = $objAsset->GetVirtualAttribute($objCustomField->CustomFieldId); $strCFVArray[] = sprintf("`cfv_%s`='%s'", $objCustomField->CustomFieldId, $strCFV); } // Update values for custom field set not allowed to model $arrToClear = $this->substactNotAllowedFields($objAsset->AssetModelId, null); foreach ($arrToClear as $idToBeNull) { $strCFVArray[] = sprintf("`cfv_%s`= NULL", $idToBeNull); } if (isset($strCFVArray) && count($strCFVArray)) { $strCFVQuery = sprintf("UPDATE `asset_custom_field_helper` SET %s WHERE `asset_id`='%s'", str_replace('""', '"', implode(", ", $strCFVArray)), $intItemId); } else { $strCFVQuery = false; } $this->arrOldItemArray[$objAsset->AssetId] = array("ItemSql" => $strItemQuery, "CFVSql" => $strCFVQuery); } } else { // If Import Action is "Create Records" and this Asset has already in the database /*elseif ($this->lstImportAction->SelectedValue == 1 && $this->in_array_nocase($strAssetCode, $strAssetArray)) { // Skipped and flagged as duplicates $this->intSkippedRecordCount++; $this->PutSkippedRecordInFile($file_skipped, $strRowArray); }*/ $this->intSkippedRecordCount++; $this->PutSkippedRecordInFile($file_skipped, $strRowArray); } } //if ($this->intCurrentFile == count($this->strFilePathArray)) { // Inserts if (count($this->strAssetValuesArray)) { $objDatabase = Asset::GetDatabase(); //var_dump($this->strAssetValuesArray); //exit(); // $objDatabase->NonQuery(sprintf("INSERT INTO `asset` (`asset_code`, `location_id`, `asset_model_id`, `parent_asset_id`, `linked_flag`, `created_by`, `creation_date`) VALUES %s;", str_replace('""','"',implode(", ", $this->strAssetValuesArray)))); $objDatabase->NonQuery(sprintf("INSERT INTO `asset` (`asset_code`, `location_id`, `asset_model_id`, `parent_asset_id`, `linked_flag`, `created_by`, `creation_date`, `depreciation_flag`, `purchase_cost`, `purchase_date` ) VALUES %s;", str_replace('""', '"', implode(", ", $this->strAssetValuesArray)))); $intInsertId = $objDatabase->InsertId(); if ($intInsertId) { $strAssetIdArray = array(); $strCFVArray = array(); for ($i = 0; $i < count($objNewAssetArray); $i++) { $this->objNewAssetArray[$intInsertId + $i] = $objNewAssetArray[$i]; $strCFVArray[$i] = sprintf("('%s', %s)", $intInsertId + $i, $strAssetCFVArray[$i]); $strAssetIdArray[$i] = sprintf("(%s)", $intInsertId + $i); } /*if ($j == 1) { echo "strCFVArray: "; print_r($strCFVArray); echo "strAssetCFVArray: "; print_r($strAssetCFVArray); exit; }*/ $strCFVNameArray = array(); foreach ($arrAssetCustomField as $objCustomField) { $strCFVNameArray[] = sprintf("`cfv_%s`", $objCustomField->CustomFieldId); } if (count($strAssetCFVArray) > 0 && count($strCFVNameArray) > 0) { $strQuery = sprintf("INSERT INTO `asset_custom_field_helper` (`asset_id`, %s) VALUES %s", str_replace('""', '"', implode(", ", $strCFVNameArray)), str_replace('""', '"', implode(", ", $strCFVArray))); } else { $strQuery = sprintf("INSERT INTO `asset_custom_field_helper` (`asset_id`) VALUES %s", str_replace('""', '"', implode(", ", $strAssetIdArray))); } $objDatabase->NonQuery($strQuery); $theAsset = Asset::Load($intInsertId); $strQuery = $this->substactNotAllowedFields($theAsset->AssetModelId, $theAsset->AssetId); if (!empty($strQuery)) { //$objDatabase->NonQuery($this->substactNotAllowedFields($theAsset->AssetModelId,$theAsset->AssetId)); $objDatabase->NonQuery($strQuery); } } $this->strAssetValuesArray = array(); } // Updates if (count($strUpdatedValuesArray)) { $objDatabase = Asset::GetDatabase(); foreach ($strUpdatedValuesArray as $query) { $objDatabase->NonQuery($query); } foreach ($this->objUpdatedItemArray as $intItemKey => $objUpdatedItem) { if (isset($strUpdatedItemCFVArray[$intItemKey]) && count($strUpdatedItemCFVArray[$intItemKey])) { $strCFVArray = array(); foreach ($arrAssetCustomField as $objCustomField) { $strCFVArray[] = sprintf("`cfv_%s`=%s", $objCustomField->CustomFieldId, $strUpdatedItemCFVArray[$intItemKey][$objCustomField->CustomFieldId]); } if (isset($strCFVArray) && count($strCFVArray)) { $strQuery = sprintf("UPDATE `asset_custom_field_helper` SET %s WHERE `asset_id`='%s'", str_replace('""', '"', implode(", ", $strCFVArray)), $intItemKey); $objDatabase->NonQuery($strQuery); // insert nulls where not allowed $theAsset = Asset::Load($intItemKey); $strQuery = $this->substactNotAllowedFields($theAsset->AssetModelId, $theAsset->AssetId); if ($strQuery) { $objDatabase->NonQuery($strQuery); } } } } } //} //$this->intCurrentFile++; //break; } //$j++; } if ($this->intImportStep == 3) { /*if (count($this->strSelectedValueArray)) { $objDatabase = CustomField::GetDatabase(); $strQuery = sprintf("INSERT INTO `custom_field_selection` " . "(`entity_id`,`entity_qtype_id`, `custom_field_value_id`) " . "VALUES %s;", implode(", ", $this->strSelectedValueArray)); $objDatabase->NonQuery($strQuery); }*/ // Insert Values into helper tables $objDatabase = Asset::GetDatabase(); $objDatabase->NonQuery("SET FOREIGN_KEY_CHECKS=0;"); // Insert into asset_custom_field_helper $objDatabase->NonQuery(sprintf("INSERT INTO `asset_custom_field_helper` (`asset_id`) (SELECT `asset_id` FROM `asset` WHERE `asset_id` NOT IN (SELECT `asset_id` FROM `asset_custom_field_helper`));")); // Inserts end $objDatabase->NonQuery("SET FOREIGN_KEY_CHECKS=1;"); $this->blnImportEnd = true; $this->btnNext->Warning = ""; $this->lblImportResults->Display = true; if (count($this->objNewAssetArray)) { //$this->lblImportAssets->Display = true; $this->dtgAsset->Paginator = new QPaginator($this->dtgAsset); $this->dtgAsset->ItemsPerPage = 20; } if (count($this->objUpdatedAssetArray)) { $this->lblImportUpdatedAssets->Display = true; $this->dtgUpdatedAsset->Paginator = new QPaginator($this->dtgUpdatedAsset); $this->dtgUpdatedAsset->ItemsPerPage = 20; } if (count($this->objNewAssetArray)) { $this->lblImportAsset->Display = true; $this->dtgAsset->Paginator = new QPaginator($this->dtgAsset); $this->dtgAsset->ItemsPerPage = 20; } $this->btnNext->Display = false; $this->btnCancel->Display = false; $this->btnUndoLastImport->Display = true; $this->btnImportMore->Display = true; $this->btnReturnToAssets->Display = true; $this->lblImportSuccess->Display = true; $this->lblImportSuccess->Text = sprintf("Success:<br/>" . "<b>%s</b> Records imported successfully<br/>" . "<b>%s</b> Records skipped due to error<br/>", count($this->objNewAssetArray) + count($this->objUpdatedItemArray), $this->intSkippedRecordCount); if ($this->intSkippedRecordCount) { $this->lblImportSuccess->Text .= sprintf("<a href='./asset_import.php?intDownloadCsv=1'>Click here to download records that could not be imported</a>"); } $this->lblImportSuccess->Text .= "<br/><br/>"; $this->intImportStep = -1; } // Enable Next button $this->btnNext->Enabled = true; if (!$this->blnImportEnd && !$this->intCurrentFile) { $this->intImportStep++; } } fclose($file_skipped); } if (!$blnError) { if (($this->blnImportEnd || $this->intImportStep == 2) && $this->intImportStep != -1) { $this->intStep++; $this->DisplayStepForm($this->intStep); } if (!$this->blnImportEnd) { $this->btnNext->Warning = "Please wait..."; QApplication::ExecuteJavaScript("document.getElementById('" . $this->btnNext->ControlId . "').click();"); } if (!($this->intCurrentFile < count($this->strFilePathArray))) { $this->intCurrentFile = 0; $this->intImportStep++; } } }
protected function lstDepreciationClass_Create() { $this->lstDepreciationClass = new QListBox($this); $this->lstDepreciationClass->Name = QApplication::Translate('Depreciation Class'); //if (!$this->blnEditMode) $this->lstDepreciationClass->AddItem('- Select One -', null); $objDepreciationClassArray = DepreciationClass::LoadAll(); if (is_array($objDepreciationClassArray)) { foreach ($objDepreciationClassArray as $objDepreciationClass) { $objListItem = new QListItem($objDepreciationClass->ShortDescription, $objDepreciationClass->DepreciationClassId); if ($this->objAssetModel->DepreciationClass && $this->objAssetModel->DepreciationClass->DepreciationClassId == $objDepreciationClass->DepreciationClassId) { $objListItem->Selected = true; } $this->lstDepreciationClass->AddItem($objListItem); } } }
/** * Refresh this MetaControl with Data from the local AssetModel object. * @param boolean $blnReload reload AssetModel from the database * @return void */ public function Refresh($blnReload = false) { if ($blnReload) { $this->objAssetModel->Reload(); } if ($this->lblAssetModelId) { if ($this->blnEditMode) { $this->lblAssetModelId->Text = $this->objAssetModel->AssetModelId; } } if ($this->lstCategory) { $this->lstCategory->RemoveAllItems(); $this->lstCategory->AddItem(QApplication::Translate('- Select One -'), null); $objCategoryArray = Category::LoadAll(); if ($objCategoryArray) { foreach ($objCategoryArray as $objCategory) { $objListItem = new QListItem($objCategory->__toString(), $objCategory->CategoryId); if ($this->objAssetModel->Category && $this->objAssetModel->Category->CategoryId == $objCategory->CategoryId) { $objListItem->Selected = true; } $this->lstCategory->AddItem($objListItem); } } } if ($this->lblCategoryId) { $this->lblCategoryId->Text = $this->objAssetModel->Category ? $this->objAssetModel->Category->__toString() : null; } if ($this->lstManufacturer) { $this->lstManufacturer->RemoveAllItems(); $this->lstManufacturer->AddItem(QApplication::Translate('- Select One -'), null); $objManufacturerArray = Manufacturer::LoadAll(); if ($objManufacturerArray) { foreach ($objManufacturerArray as $objManufacturer) { $objListItem = new QListItem($objManufacturer->__toString(), $objManufacturer->ManufacturerId); if ($this->objAssetModel->Manufacturer && $this->objAssetModel->Manufacturer->ManufacturerId == $objManufacturer->ManufacturerId) { $objListItem->Selected = true; } $this->lstManufacturer->AddItem($objListItem); } } } if ($this->lblManufacturerId) { $this->lblManufacturerId->Text = $this->objAssetModel->Manufacturer ? $this->objAssetModel->Manufacturer->__toString() : null; } if ($this->txtAssetModelCode) { $this->txtAssetModelCode->Text = $this->objAssetModel->AssetModelCode; } if ($this->lblAssetModelCode) { $this->lblAssetModelCode->Text = $this->objAssetModel->AssetModelCode; } if ($this->txtShortDescription) { $this->txtShortDescription->Text = $this->objAssetModel->ShortDescription; } if ($this->lblShortDescription) { $this->lblShortDescription->Text = $this->objAssetModel->ShortDescription; } if ($this->txtLongDescription) { $this->txtLongDescription->Text = $this->objAssetModel->LongDescription; } if ($this->lblLongDescription) { $this->lblLongDescription->Text = $this->objAssetModel->LongDescription; } if ($this->txtImagePath) { $this->txtImagePath->Text = $this->objAssetModel->ImagePath; } if ($this->lblImagePath) { $this->lblImagePath->Text = $this->objAssetModel->ImagePath; } if ($this->lstCreatedByObject) { $this->lstCreatedByObject->RemoveAllItems(); $this->lstCreatedByObject->AddItem(QApplication::Translate('- Select One -'), null); $objCreatedByObjectArray = UserAccount::LoadAll(); if ($objCreatedByObjectArray) { foreach ($objCreatedByObjectArray as $objCreatedByObject) { $objListItem = new QListItem($objCreatedByObject->__toString(), $objCreatedByObject->UserAccountId); if ($this->objAssetModel->CreatedByObject && $this->objAssetModel->CreatedByObject->UserAccountId == $objCreatedByObject->UserAccountId) { $objListItem->Selected = true; } $this->lstCreatedByObject->AddItem($objListItem); } } } if ($this->lblCreatedBy) { $this->lblCreatedBy->Text = $this->objAssetModel->CreatedByObject ? $this->objAssetModel->CreatedByObject->__toString() : null; } if ($this->calCreationDate) { $this->calCreationDate->DateTime = $this->objAssetModel->CreationDate; } if ($this->lblCreationDate) { $this->lblCreationDate->Text = sprintf($this->objAssetModel->CreationDate) ? $this->objAssetModel->__toString($this->strCreationDateDateTimeFormat) : null; } if ($this->lstModifiedByObject) { $this->lstModifiedByObject->RemoveAllItems(); $this->lstModifiedByObject->AddItem(QApplication::Translate('- Select One -'), null); $objModifiedByObjectArray = UserAccount::LoadAll(); if ($objModifiedByObjectArray) { foreach ($objModifiedByObjectArray as $objModifiedByObject) { $objListItem = new QListItem($objModifiedByObject->__toString(), $objModifiedByObject->UserAccountId); if ($this->objAssetModel->ModifiedByObject && $this->objAssetModel->ModifiedByObject->UserAccountId == $objModifiedByObject->UserAccountId) { $objListItem->Selected = true; } $this->lstModifiedByObject->AddItem($objListItem); } } } if ($this->lblModifiedBy) { $this->lblModifiedBy->Text = $this->objAssetModel->ModifiedByObject ? $this->objAssetModel->ModifiedByObject->__toString() : null; } if ($this->lblModifiedDate) { if ($this->blnEditMode) { $this->lblModifiedDate->Text = $this->objAssetModel->ModifiedDate; } } if ($this->lstDepreciationClass) { $this->lstDepreciationClass->RemoveAllItems(); $this->lstDepreciationClass->AddItem(QApplication::Translate('- Select One -'), null); $objDepreciationClassArray = DepreciationClass::LoadAll(); if ($objDepreciationClassArray) { foreach ($objDepreciationClassArray as $objDepreciationClass) { $objListItem = new QListItem($objDepreciationClass->__toString(), $objDepreciationClass->DepreciationClassId); if ($this->objAssetModel->DepreciationClass && $this->objAssetModel->DepreciationClass->DepreciationClassId == $objDepreciationClass->DepreciationClassId) { $objListItem->Selected = true; } $this->lstDepreciationClass->AddItem($objListItem); } } } if ($this->lblDepreciationClassId) { $this->lblDepreciationClassId->Text = $this->objAssetModel->DepreciationClass ? $this->objAssetModel->DepreciationClass->__toString() : null; } if ($this->lstAssetModelCustomFieldHelper) { $this->lstAssetModelCustomFieldHelper->RemoveAllItems(); $this->lstAssetModelCustomFieldHelper->AddItem(QApplication::Translate('- Select One -'), null); $objAssetModelCustomFieldHelperArray = AssetModelCustomFieldHelper::LoadAll(); if ($objAssetModelCustomFieldHelperArray) { foreach ($objAssetModelCustomFieldHelperArray as $objAssetModelCustomFieldHelper) { $objListItem = new QListItem($objAssetModelCustomFieldHelper->__toString(), $objAssetModelCustomFieldHelper->AssetModelId); if ($objAssetModelCustomFieldHelper->AssetModelId == $this->objAssetModel->AssetModelId) { $objListItem->Selected = true; } $this->lstAssetModelCustomFieldHelper->AddItem($objListItem); } } // Because AssetModelCustomFieldHelper's AssetModelCustomFieldHelper is not null, if a value is already selected, it cannot be changed. if ($this->lstAssetModelCustomFieldHelper->SelectedValue) { $this->lstAssetModelCustomFieldHelper->Enabled = false; } else { $this->lstAssetModelCustomFieldHelper->Enabled = true; } } if ($this->lblAssetModelCustomFieldHelper) { $this->lblAssetModelCustomFieldHelper->Text = $this->objAssetModel->AssetModelCustomFieldHelper ? $this->objAssetModel->AssetModelCustomFieldHelper->__toString() : null; } }