protected void btnSave_Click(object sender, EventArgs e)
{
if (!this.ownerPage.CheckUserActionPermission(ETEMEnums.SecuritySettings.SAPDataQuantitySave, false))
{
return;
}
if (string.IsNullOrEmpty(this.hdnRowMasterKey.Value) || this.hdnRowMasterKey.Value == Constants.INVALID_ID_STRING)
{
this.currentEntity = new SAPDataQuantity();
}
else
{
this.currentEntity = this.ownerPage.CostCalculationRef.GetSAPDataQuantityById(this.hdnRowMasterKey.Value);
if (this.currentEntity == null)
{
this.ownerPage.CallContext.ResultCode = ETEMEnums.ResultEnum.Error;
base.AddMessage(this.lbResultContext, string.Format("Entity `SAPDataQuantity` not found by ID ({0})!", this.hdnRowMasterKey.Value));
return;
}
}
this.currentEntity.idCostCenter = this.ddlCostCenter.SelectedValueINT;
this.currentEntity.idQuantityType = this.ddlQuantityType.SelectedValueINT;
this.currentEntity.ValueData = BaseHelper.ConvertToDecimalOrMinValue(this.tbxValueData.Text.Trim());
this.ownerPage.CallContext = this.ownerPage.CostCalculationRef.SAPDataQuantitySave(new List <SAPDataQuantity>()
{
this.currentEntity
}, this.ownerPage.CallContext);
if (this.ownerPage.CallContext.ResultCode == ETEMEnums.ResultEnum.Success)
{
this.hdnRowMasterKey.Value = this.ownerPage.CallContext.EntityID;
base.AddMessage(this.lbResultContext, this.ownerPage.CallContext.Message);
}
else
{
if (!ShowErrors(new List <CallContext>()
{
this.ownerPage.CallContext
}))
{
return;
}
}
if (this.ownerPage is SAPDataQuantitiesList)
{
((SAPDataQuantitiesList)this.ownerPage).LoadFilteredList();
}
}
public override void UserControlLoad()
{
SetEmptyValues();
base.ClearResultContext(this.lbResultContext);
if (this.ownerPage == null)
{
throw new UMSException("Current Page is null or is not inheritor of BasicPage.");
}
if (!string.IsNullOrEmpty(this.hdnRowMasterKey.Value) && this.hdnRowMasterKey.Value != Constants.INVALID_ID_ZERO_STRING)
{
this.CurrentEntityMasterID = this.hdnRowMasterKey.Value;
}
InitLoadControls();
this.currentEntity = this.ownerPage.CostCalculationRef.GetSAPDataQuantityById(this.CurrentEntityMasterID);
if (this.currentEntity != null)
{
this.SetHdnField(this.currentEntity.idSAPDataQuantity.ToString());
this.tbxDateFrom.SetTxbDateTimeValue(this.currentEntity.SAPData.DateFrom);
this.tbxDateTo.SetTxbDateTimeValue(this.currentEntity.SAPData.DateTo);
this.tbxStatus.Text = this.currentEntity.SAPData.Status;
BaseHelper.CheckAndSetSelectedValue(this.ddlCostCenter.DropDownListCTRL, this.currentEntity.idCostCenter.ToString(), false);
BaseHelper.CheckAndSetSelectedValue(this.ddlQuantityType.DropDownListCTRL, this.currentEntity.idQuantityType.ToString(), false);
this.tbxValueData.Text = this.currentEntity.ValueDataRoundString;
base.ClearResultContext(this.lbResultContext);
}
else
{
SetEmptyValues();
}
this.pnlFormData.Visible = true;
this.pnlFormData.Focus();
}
public List <Expense> LoadExpenseGroupByIdSAPData(int idSAPData)
{
List <Expense> listGroupExpense = new List <Expense>();
SAPData sapData = new SAPDataBL().GetEntityById(idSAPData);
List <SAPDataExpense> listSAPDataExpenses = sapData.SAPDataExpenses.ToList();
List <SAPDataQuantity> listSAPDataQuantity = sapData.SAPDataQuantities.ToList();
List <KeyValue> listKeyValueExpense = new KeyValueBL().GetAllKeyValueByKeyTypeIntCode(ETEMEnums.KeyTypeEnum.ExpensesType.ToString());
List <KeyValue> listKeyValueQuantity = new KeyValueBL().GetAllKeyValueByKeyTypeIntCode(ETEMEnums.KeyTypeEnum.QuantityType.ToString());
KeyValue kvMachineHours = listKeyValueQuantity.Where(w => w.KeyValueIntCode == ETEMEnums.QuantityTypeEnum.MachineHours.ToString()).FirstOrDefault();
KeyValue kvProductionQuantity = listKeyValueQuantity.Where(w => w.KeyValueIntCode == ETEMEnums.QuantityTypeEnum.ProductionQuantity.ToString()).FirstOrDefault();
List <KeyValue> listKeyValueCostCenter = new KeyValueBL().GetAllKeyValueByKeyTypeIntCode(ETEMEnums.KeyTypeEnum.CostCenter.ToString());
List <string> listIntCodesCostCenterForProductionQuantity = new List <string>()
{
ETEMEnums.CostCenterEnum.DIESDepartment.ToString()
};
Dictionary <int, int> dictKvCostCenterIdAndQuantityTypeId = new Dictionary <int, int>();
foreach (KeyValue kvCostCenter in listKeyValueCostCenter)
{
if (listIntCodesCostCenterForProductionQuantity.Contains(kvCostCenter.KeyValueIntCode))
{
if (!dictKvCostCenterIdAndQuantityTypeId.ContainsKey(kvCostCenter.idKeyValue))
{
dictKvCostCenterIdAndQuantityTypeId.Add(kvCostCenter.idKeyValue, kvProductionQuantity.idKeyValue);
}
}
else
{
if (!dictKvCostCenterIdAndQuantityTypeId.ContainsKey(kvCostCenter.idKeyValue))
{
dictKvCostCenterIdAndQuantityTypeId.Add(kvCostCenter.idKeyValue, kvMachineHours.idKeyValue);
}
}
}
List <KeyValue> listKeyValueExpensesTypeGroup = new KeyValueBL().GetAllKeyValueByKeyTypeIntCode(ETEMEnums.KeyTypeEnum.ExpensesTypeGroup.ToString());
//this.expenseGroup = new ExpenseGroup();
int quantityTypeID = Constants.INVALID_ID;
foreach (KeyValue costCenter in listKeyValueCostCenter)
{
foreach (KeyValue expensesTypeGroup in listKeyValueExpensesTypeGroup)
{
Expense expense = new Expense();
expense.IdSAPData = sapData.idSAPData;
expense.ExpenseGroup = expensesTypeGroup;
expense.CostCenter = costCenter;
expense.ExpenseValue = listSAPDataExpenses
.Where(k =>
k.idCostCenter == costCenter.idKeyValue &&
listKeyValueExpense
.Where(f => f.DefaultValue2 == expensesTypeGroup.KeyValueIntCode)
.Select(f => f.idKeyValue).Contains(k.idExpensesType))
.Sum(k => k.ValueData);
if (dictKvCostCenterIdAndQuantityTypeId.ContainsKey(costCenter.idKeyValue))
{
quantityTypeID = dictKvCostCenterIdAndQuantityTypeId[costCenter.idKeyValue];
}
SAPDataQuantity quantity = listSAPDataQuantity.FirstOrDefault(q => q.idCostCenter == costCenter.idKeyValue && q.idQuantityType == quantityTypeID);
if (quantity != null && quantity.ValueData != Decimal.Zero)
{
expense.ExpenseValue_MH = expense.ExpenseValue / quantity.ValueData;
}
listGroupExpense.Add(expense);
}
}
return(listGroupExpense);
}
public CallContext ImportSAPDataExpensesAndQuantities(string fileFullName, int idEntity, CallContext resultContext)
{
try
{
resultContext.ResultCode = ETEMEnums.ResultEnum.Error;
FileInfo excelFile = new FileInfo(fileFullName);
using (ExcelPackage package = new ExcelPackage(excelFile))
{
int currRow = 1;
int currCol = 0;
bool res;
decimal resultParseDecimal;
int resultParseInt;
ExcelWorksheet workSheetExpenses = package.Workbook.Worksheets[1];
ExcelWorksheet workSheetQuantity = package.Workbook.Worksheets[2];
if (workSheetExpenses == null && workSheetQuantity == null)
{
resultContext.Message = "Error! No Excel work sheet `Expenses` and `Quantity`!";
return(resultContext);
}
else if (workSheetExpenses == null)
{
resultContext.Message = "Error! No Excel work sheet `Expenses`!";
return(resultContext);
}
else if (workSheetQuantity == null)
{
resultContext.Message = "Error! No Excel work sheet `Quantity`!";
return(resultContext);
}
SAPData _SAPData = this.GetEntityById(idEntity);
if (_SAPData == null)
{
resultContext.Message = "Entity `SAPData` not found by ID (`" + idEntity + "`)!";
return(resultContext);
}
List <string> listKeyTypeIntCodes = new List <string>()
{
ETEMEnums.KeyTypeEnum.CostCenter.ToString(),
ETEMEnums.KeyTypeEnum.ExpensesType.ToString(),
ETEMEnums.KeyTypeEnum.QuantityType.ToString()
};
List <KeyValueDataView> listKeyValuesToSAPDataExpensesAndQuantity = new List <KeyValueDataView>();
List <KeyValueDataView> listKeyValueCostCenter = new List <KeyValueDataView>();
List <KeyValueDataView> listKeyValueExpensesType = new List <KeyValueDataView>();
List <KeyValueDataView> listKeyValueQuantityType = new List <KeyValueDataView>();
listKeyValuesToSAPDataExpensesAndQuantity = (from kv in this.dbContext.KeyValues
join kt in this.dbContext.KeyTypes on kv.idKeyType equals kt.idKeyType
where listKeyTypeIntCodes.Contains(kt.KeyTypeIntCode)
select new KeyValueDataView
{
idKeyValue = kv.idKeyValue,
Name = kv.Name,
NameEN = kv.NameEN,
DefaultValue1 = kv.DefaultValue1,
KeyValueIntCode = kv.KeyValueIntCode,
KeyTypeIntCode = kt.KeyTypeIntCode
}
).ToList <KeyValueDataView>();
listKeyValueCostCenter = listKeyValuesToSAPDataExpensesAndQuantity.Where(w => w.KeyTypeIntCode == ETEMEnums.KeyTypeEnum.CostCenter.ToString()).ToList();
listKeyValueExpensesType = listKeyValuesToSAPDataExpensesAndQuantity.Where(w => w.KeyTypeIntCode == ETEMEnums.KeyTypeEnum.ExpensesType.ToString()).ToList();
listKeyValueQuantityType = listKeyValuesToSAPDataExpensesAndQuantity.Where(w => w.KeyTypeIntCode == ETEMEnums.KeyTypeEnum.QuantityType.ToString()).ToList();
List <SAPDataExpense> listSAPDataExpenseOld = new List <SAPDataExpense>();
List <SAPDataQuantity> listSAPDataQuantityOld = new List <SAPDataQuantity>();
List <SAPDataExpense> listSAPDataExpenseNew = new List <SAPDataExpense>();
List <SAPDataQuantity> listSAPDataQuantityNew = new List <SAPDataQuantity>();
listSAPDataExpenseOld = (from sde in this.dbContext.SAPDataExpenses
where sde.idSAPData == _SAPData.idSAPData
select sde).ToList <SAPDataExpense>();
listSAPDataQuantityOld = (from sdq in this.dbContext.SAPDataQuantities
where sdq.idSAPData == _SAPData.idSAPData
select sdq).ToList <SAPDataQuantity>();
Dictionary <string, string> dictErrorsExpenses = new Dictionary <string, string>();
Dictionary <string, string> dictErrorsQuantities = new Dictionary <string, string>();
SAPDataExpense newSAPDataExpense = new SAPDataExpense();
SAPDataQuantity newSAPDataQuantity = new SAPDataQuantity();
bool hasNotErrorInRow = true;
string rangeValueStr = string.Empty;
ExcelRange range;
for (int i = 4; i < listKeyValueCostCenter.Count + 4; i++)
{
for (int j = 2; j < listKeyValueExpensesType.Count + 2; j++)
{
hasNotErrorInRow = true;
currRow = 1;
range = workSheetExpenses.Cells[currRow, i];
rangeValueStr = (range.Value != null ? range.Value.ToString() : string.Empty);
var kvCostCenter = listKeyValueCostCenter.Where(w => w.Name.Trim().ToUpper() == rangeValueStr.Trim().ToUpper()).FirstOrDefault();
newSAPDataExpense = new SAPDataExpense();
newSAPDataExpense.idSAPData = idEntity;
if (kvCostCenter != null)
{
newSAPDataExpense.idCostCenter = kvCostCenter.idKeyValue;
}
else
{
hasNotErrorInRow = false;
if (dictErrorsExpenses.ContainsKey("CostCenterExpenses"))
{
dictErrorsExpenses["CostCenterExpenses"] += "," + i;
}
else
{
dictErrorsExpenses.Add("CostCenterExpenses", i.ToString());
}
}
currCol = 2;
range = workSheetExpenses.Cells[j, currCol];
rangeValueStr = (range.Value != null ? range.Value.ToString() : string.Empty);
var kvExpensesType = listKeyValueExpensesType.Where(w => w.KeyValueIntCode.Trim().ToUpper() == rangeValueStr.Trim().ToUpper()).FirstOrDefault();
if (kvExpensesType != null)
{
newSAPDataExpense.idExpensesType = kvExpensesType.idKeyValue;
}
else
{
hasNotErrorInRow = false;
if (dictErrorsExpenses.ContainsKey("ExpensesType"))
{
dictErrorsExpenses["ExpensesType"] += "," + j;
}
else
{
dictErrorsExpenses.Add("ExpensesType", j.ToString());
}
}
range = workSheetExpenses.Cells[j, i];
rangeValueStr = (range.Value != null ? range.Value.ToString() : string.Empty);
res = Decimal.TryParse(rangeValueStr, NumberStyles.Any, BaseHelper.GetNumberFormatInfo("", ".", 4), out resultParseDecimal);
if (res)
{
newSAPDataExpense.ValueData = resultParseDecimal;
}
else
{
newSAPDataExpense.ValueData = decimal.Zero;
}
var checkSAPDataExpenses = listSAPDataExpenseOld.Where(w => w.idCostCenter == newSAPDataExpense.idCostCenter &&
w.idExpensesType == newSAPDataExpense.idExpensesType).ToList();
if (checkSAPDataExpenses.Count > 0)
{
hasNotErrorInRow = false;
if (dictErrorsExpenses.ContainsKey("DuplicateOldExpenses"))
{
dictErrorsExpenses["DuplicateOldExpenses"] += "," + j + "-" + i;
}
else
{
dictErrorsExpenses.Add("DuplicateOldExpenses", j + "-" + i);
}
}
if (hasNotErrorInRow)
{
listSAPDataExpenseNew.Add(newSAPDataExpense);
// this.dbContext.SAPDataExpenses.AddObject(newSAPDataExpense);
}
}
for (int j = 2; j < listKeyValueQuantityType.Count + 2; j++)
{
hasNotErrorInRow = true;
currRow = 1;
range = workSheetQuantity.Cells[currRow, i];
rangeValueStr = (range.Value != null ? range.Value.ToString() : string.Empty);
var kvCostCenter = listKeyValueCostCenter.Where(w => w.Name.Trim().ToUpper() == rangeValueStr.Trim().ToUpper()).FirstOrDefault();
newSAPDataQuantity = new SAPDataQuantity();
newSAPDataQuantity.idSAPData = idEntity;
if (kvCostCenter != null)
{
newSAPDataQuantity.idCostCenter = kvCostCenter.idKeyValue;
}
else
{
hasNotErrorInRow = false;
if (dictErrorsQuantities.ContainsKey("CostCenterQuantity"))
{
dictErrorsQuantities["CostCenterQuantity"] += "," + i;
}
else
{
dictErrorsQuantities.Add("CostCenterQuantity", i.ToString());
}
}
currCol = 2;
range = workSheetQuantity.Cells[j, currCol];
rangeValueStr = (range.Value != null ? range.Value.ToString() : string.Empty);
var kvQuantityType = listKeyValueQuantityType.Where(w => w.KeyValueIntCode.Trim().ToUpper() == rangeValueStr.Trim().ToUpper()).FirstOrDefault();
if (kvQuantityType != null)
{
newSAPDataQuantity.idQuantityType = kvQuantityType.idKeyValue;
}
else
{
hasNotErrorInRow = false;
if (dictErrorsQuantities.ContainsKey("QuantityType"))
{
dictErrorsQuantities["QuantityType"] += "," + j;
}
else
{
dictErrorsQuantities.Add("QuantityType", j.ToString());
}
}
range = workSheetQuantity.Cells[j, i];
rangeValueStr = (range.Value != null ? range.Value.ToString() : string.Empty);
res = Decimal.TryParse(rangeValueStr, NumberStyles.Any, BaseHelper.GetNumberFormatInfo("", ".", 4), out resultParseDecimal);
if (res)
{
newSAPDataQuantity.ValueData = resultParseDecimal;
}
else
{
newSAPDataQuantity.ValueData = decimal.Zero;
}
var checkSAPDataQuantity = listSAPDataQuantityOld.Where(w => w.idCostCenter == newSAPDataQuantity.idCostCenter &&
w.idQuantityType == newSAPDataQuantity.idQuantityType).ToList();
if (checkSAPDataQuantity.Count > 0)
{
hasNotErrorInRow = false;
if (dictErrorsQuantities.ContainsKey("DuplicateOldQuantity"))
{
dictErrorsQuantities["DuplicateOldQuantity"] += "," + j + "-" + i;
}
else
{
dictErrorsQuantities.Add("DuplicateOldQuantity", j + "-" + i);
}
}
if (hasNotErrorInRow)
{
listSAPDataQuantityNew.Add(newSAPDataQuantity);
// this.dbContext.SAPDataQuantities.AddObject(newSAPDataQuantity);
}
}
}
if (dictErrorsExpenses.Count == 0 && dictErrorsQuantities.Count == 0)
{
resultContext = new SAPDataExpensesBL().EntitySave <SAPDataExpense>(listSAPDataExpenseNew, resultContext);
if (resultContext.ResultCode == ETEMEnums.ResultEnum.Success)
{
resultContext = new SAPDataQuantityBL().EntitySave <SAPDataQuantity>(listSAPDataQuantityNew, resultContext);
if (resultContext.ResultCode == ETEMEnums.ResultEnum.Success)
{
resultContext.ResultCode = ETEMEnums.ResultEnum.Success;
resultContext.Message = "The SAP data expenses and quantities by cost centers have been imported successfully!";
}
else
{
resultContext.ResultCode = ETEMEnums.ResultEnum.Error;
resultContext.Message = "Error import the SAP data expenses and quantities by cost centers!";
}
}
else
{
resultContext.ResultCode = ETEMEnums.ResultEnum.Error;
resultContext.Message = "Error import the SAP data expenses and quantities by cost centers!";
}
}
else
{
List <string> listErrors = new List <string>();
if (dictErrorsExpenses.ContainsKey("CostCenterExpenses"))
{
listErrors.Add("Error! The field `Cost Center` for expenses is missing or in wrong format, Cols (" + dictErrorsExpenses["CostCenterExpenses"] + ")!");
}
if (dictErrorsExpenses.ContainsKey("ExpensesType"))
{
listErrors.Add("Error! The field `Expenses Type` is missing or in wrong format, Rows (" + dictErrorsExpenses["ExpensesType"] + ")!");
}
if (dictErrorsExpenses.ContainsKey("DuplicateOldExpenses"))
{
listErrors.Add("Error! The selected file includes expenses with duplicate data in the database, Rows-Cols (" + dictErrorsExpenses["DuplicateOldExpenses"] + ")!");
}
if (dictErrorsQuantities.ContainsKey("CostCenterQuantity"))
{
listErrors.Add("Error! The field `Cost Center` for quantities is missing or in wrong format, Cols (" + dictErrorsQuantities["CostCenterQuantity"] + ")!");
}
if (dictErrorsQuantities.ContainsKey("QuantityType"))
{
listErrors.Add("Error! The field `Quantity Type` is missing or in wrong format, Rows (" + dictErrorsQuantities["QuantityType"] + ")!");
}
if (dictErrorsQuantities.ContainsKey("DuplicateOldQuantity"))
{
listErrors.Add("Error! The selected file includes quantities with duplicate data in the database, Rows-Cols (" + dictErrorsQuantities["DuplicateOldQuantity"] + ")!");
}
resultContext.Message = string.Join(Constants.ERROR_MESSAGES_SEPARATOR, listErrors);
}
}
}
catch (Exception ex)
{
resultContext.Message = "Error import the SAP data expenses and quantities by cost centers!";
BaseHelper.Log("Error import entities `SAPDataExpenses`, `SAPDataQuantity`!");
BaseHelper.Log(ex.Message);
BaseHelper.Log(ex.StackTrace);
}
return(resultContext);
}
