private void gridviewImportData_ValidatingEditor(object sender, DevExpress.XtraEditors.Controls.BaseContainerValidateEditorEventArgs e)
{
if (gridviewImportData.FocusedColumn == colApprovalNo)
{
TAApprovalImportViewModel Row = (TAApprovalImportViewModel)gridviewImportData.GetFocusedRow();
if (Row != null)
{
if (Row.ApprovalNoPrefixID != null && e.Value != null)
{
int No = Model.CommonFunctions.ParseInt(e.Value.ToString());
if (DALObj.IsDuplicateRecord(Row.ApprovalNoPrefixID.Value, No, 0) || dsImportedData.Any(r => r.ApprovalNoPrefixID == Row.ApprovalNoPrefixID && r.ApprovalNo == No && r.RowNo != Row.RowNo))
{
Row.ApprovalNoError = e.Value.ToString() + ": Duplicate number not accepted.";
e.Value = null;
}
}
}
}
}
private void gridviewImportData_CellValueChanged(object sender, DevExpress.XtraGrid.Views.Base.CellValueChangedEventArgs e)
{
TAApprovalImportViewModel Row = (TAApprovalImportViewModel)gridviewImportData.GetRow(e.RowHandle);
if (Row == null)
{
return;
}
gridviewImportData.PostEditor();
if (e.Column == colApprovalNoPrefixName)
{
if (Row.ApprovalNoPrefixID == null || Row.ApprovalNoPrefixID == 0)
{
Row.ApprovalNoPrefixNameError = "Approval No. Prefix not selected.";
}
else
{
Row.ApprovalNoPrefixNameError = null;
// if duplicate found in current records, then generate new
if (Row.ApprovalNo == null || Row.ApprovalNo == 0 || dsImportedData.Where(r => r.RowNo != Row.RowNo).Any(r => r.ApprovalNoPrefixID == Row.ApprovalNoPrefixID && r.ApprovalNo == Row.ApprovalNo))
{
int No = DALObj.GenerateTAApprovalNo(Row.ApprovalNoPrefixID.Value);
No = Math.Max(No, (dsImportedData.Where(r => r.RowNo != Row.RowNo && Row.ApprovalNoPrefixID == Row.ApprovalNoPrefixID).Max(r => r.ApprovalNo) ?? 0) + 1);
Row.ApprovalNo = No;
}
}
}
else if (e.Column == colApprovalNo)
{
}
else if (e.Column == colEmployeeName)
{
var employee = dsEmployee.FirstOrDefault(r => r.EmployeeID == Row.EmployeeID);
if (employee != null)
{
Row.EmployeeNo = employee.EmployeeNo;
Row.EmployeeNoPrefixName = employee.EmployeeNoPrefix;
}
}
}
void ImportData(string ImportFileName)
{
if (string.IsNullOrWhiteSpace(ImportFileName))
{
Alit.WinformControls.MessageBox.Show("Please select excel file.", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (!System.IO.File.Exists(ImportFileName))
{
Alit.WinformControls.MessageBox.Show("Selected file doesn't exists.", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
string extension = System.IO.Path.GetExtension(ImportFileName).ToUpper();
if (extension != ".XLS" && extension != ".XLSX")
{
if (Alit.WinformControls.MessageBox.Show(this, "Selected file is not seems to be valid excel file. Do you want to continue ?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.No)
{
return;
}
}
//--
tAApprovalImportViewModelBindingSource.Clear();
if (extension.ToUpper() != ".XLS" && extension.ToUpper() != ".XLSX")
{
if (Alit.WinformControls.MessageBox.Show(this, "Selected file is seems to be Not valid excel file. Do you want to continue ?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.No)
{
return;
}
}
string conStr = string.Empty;
if (extension == ".xlsx")
{
conStr = string.Format(Excel07ConString, ImportFileName, "Yes");
}
else
{
conStr = string.Format(Excel03ConString, ImportFileName, "Yes");
}
ShowWaitForm();
try
{
DevExpress.DataAccess.Excel.ExcelDataSource myExcelSource = new DevExpress.DataAccess.Excel.ExcelDataSource();
myExcelSource.FileName = ImportFileName;
DevExpress.DataAccess.Excel.ExcelWorksheetSettings worksheetSettings = new DevExpress.DataAccess.Excel.ExcelWorksheetSettings(GetWorkSheetNameByIndex(ImportFileName, 0));
myExcelSource.SourceOptions = new DevExpress.DataAccess.Excel.ExcelSourceOptions(worksheetSettings);
myExcelSource.SourceOptions.SkipEmptyRows = true;
myExcelSource.SourceOptions.UseFirstRowAsHeader = true;
myExcelSource.Fill();
System.Collections.IList list = ((IListSource)myExcelSource).GetList();
DevExpress.DataAccess.Native.Excel.DataView dataView = (DevExpress.DataAccess.Native.Excel.DataView)list;
try
{
#region Decoding Data
for (int ri = 0; ri < dataView.Count; ri++)
{
DevExpress.DataAccess.Native.Excel.ViewRow dsRow = (DevExpress.DataAccess.Native.Excel.ViewRow)dataView[ri];
TAApprovalImportViewModel NewRecord = new TAApprovalImportViewModel()
{
RowNo = ri + 1
};
//--
object importvalue = null;
// Transaction Date (Approval Date)
importvalue = dataView.Columns[ImportFileColumnIndex_TransactionDate].GetValue(dsRow);
if (importvalue != null)
{
DateTime dtvalue;
if (DateTime.TryParse(importvalue.ToString(), out dtvalue))
{
NewRecord.ApprovalDate = dtvalue;
}
else
{
NewRecord.ApprovalDateError = importvalue.ToString() + " : invalid value";
}
}
else
{
NewRecord.ApprovalDateError = "No value";
}
// TransactionNoPrefix
importvalue = dataView.Columns[ImportFileColumnIndex_TransactionNoPrefix].GetValue(dsRow);
if (importvalue != null)
{
NewRecord.ApprovalNoPrefixName = importvalue.ToString();
string prefixToCheck = NewRecord.ApprovalNoPrefixName.Trim().ToUpper();
var Prefix = dsApprovalNoPrefix.FirstOrDefault(r => r.TAApprovalNoPrefixName.ToUpper() == prefixToCheck);
if (Prefix != null)
{
NewRecord.ApprovalNoPrefixID = Prefix.TAApprovalNoPrefixID;
}
else
{
NewRecord.ApprovalNoPrefixNameError = importvalue.ToString() + " : not found.";
}
}
else
{
NewRecord.ApprovalNoPrefixNameError = "No value.";
}
// TransactionNo
importvalue = dataView.Columns[ImportFileColumnIndex_TransactionNo].GetValue(dsRow);
if (importvalue != null)
{
int value = 0;
if (int.TryParse(importvalue.ToString(), out value))
{
NewRecord.ApprovalNo = value;
//--
if (NewRecord.ApprovalNoPrefixID != null)
{
if (DALObj.IsDuplicateRecord(NewRecord.ApprovalNoPrefixID.Value, NewRecord.ApprovalNo.Value, 0))
{
NewRecord.ApprovalNo = null;
NewRecord.ApprovalNoError = importvalue.ToString() + " : duplicate value can not accepted.";
}
}
}
else
{
NewRecord.ApprovalNoError = importvalue.ToString() + " : invalid value";
}
}
else
{
NewRecord.ApprovalNoError = "No value";
}
// EmployeeNo
importvalue = dataView.Columns[ImportFileColumnIndex_EmployeeNo].GetValue(dsRow);
if (importvalue != null)
{
int value = 0;
if (int.TryParse(importvalue.ToString(), out value))
{
NewRecord.EmployeeNo = value;
var Employee = dsEmployee.FirstOrDefault(r => r.EmployeeNo == NewRecord.EmployeeNo);
if (Employee != null)
{
NewRecord.EmployeeID = Employee.EmployeeID;
NewRecord.EmployeeNoPrefixName = Employee.EmployeeNoPrefix;
NewRecord.EmployeeName = Employee.EmployeeName;
}
else
{
NewRecord.EmployeeNameError = importvalue.ToString() + " : not found";
}
}
else
{
NewRecord.EmployeeNameError = importvalue.ToString() + " : invalid value";
}
}
else
{
NewRecord.EmployeeNameError = "No value";
}
// Approved Date
importvalue = dataView.Columns[ImportFileColumnIndex_ApprovedDate].GetValue(dsRow);
if (importvalue != null)
{
DateTime value;
if (DateTime.TryParse(importvalue.ToString(), out value))
{
NewRecord.ApprovedDate = value;
}
else
{
NewRecord.ApprovedDateError = importvalue.ToString() + " : invalid value";
}
}
else
{
NewRecord.ApprovedDateError = "No value";
}
// ApprovedHours
importvalue = dataView.Columns[ImportFileColumnIndex_ApprovedHours].GetValue(dsRow);
if (importvalue != null)
{
decimal value;
if (decimal.TryParse(importvalue.ToString(), out value))
{
NewRecord.ApprovedHours = value;
}
else
{
NewRecord.ApprovedHoursError = importvalue.ToString() + " : invalid value";
}
}
else
{
NewRecord.ApprovedHoursError = "No value";
}
// ApprovedFor
importvalue = dataView.Columns[ImportFileColumnIndex_ApprovedFor].GetValue(dsRow);
if (importvalue != null)
{
int value;
if (int.TryParse(importvalue.ToString(), out value) && value >= 1 && value <= 4)
{
NewRecord.ApprovalTypeID = (eTAApprovalType)(value - 1);
}
else
{
NewRecord.ApprovalTypeIDError = importvalue.ToString() + " : invalid value";
}
}
else
{
NewRecord.ApprovalTypeIDError = "No value";
}
// Remark
importvalue = dataView.Columns[ImportFileColumnIndex_Remark].GetValue(dsRow);
if (importvalue != null)
{
NewRecord.Remark = importvalue.ToString();
}
//// DocumandLocation
//importvalue = dataView.Columns[ImportFileColumnIndex_DocumandLocation].GetValue(dsRow);
//if (importvalue != null)
//{
// NewRecord.DocumentLocation = importvalue.ToString();
//}
//// Check - Valid for Import
//if (NewRecord.ApprovalDate != null && NewRecord.ApprovalNoPrefixID != null && NewRecord.ApprovalNo != null &&
// NewRecord.ApprovalTypeID != null && NewRecord.EmployeeID != null &&
// NewRecord.ApprovedDate != null && NewRecord.ApprovedHours != null)
//{
// NewRecord.ValidForImport = true;
//}
//else
//{
// NewRecord.ValidForImport = false;
//}
//--
tAApprovalImportViewModelBindingSource.Add(NewRecord);
}
#endregion
}
catch (Exception ex)
{
ex = DAL.CommonFunctions.GetFinalError(ex);
CloseWaitForm();
Alit.WinformControls.MessageBox.Show("Error while trying to decode excel file.\r\n" + (ex != null ? ex.Message : ""), MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
catch (Exception ex)
{
ex = DAL.CommonFunctions.GetFinalError(ex);
CloseWaitForm();
Alit.WinformControls.MessageBox.Show("Error while trying to connect excel file.\r\n" + (ex != null ? ex.Message : ""), MessageBoxButtons.OK, MessageBoxIcon.Error);
}
CloseWaitForm();
}
