private void RibbonEllipse_Load(object sender, RibbonUIEventArgs e)
{
_excelApp = Globals.ThisAddIn.Application;
if (_cells == null)
_cells = new ExcelStyleCells(_excelApp);
_eFunctions.DebugQueries = false;
_eFunctions.DebugErrors = false;
_eFunctions.DebugWarnings = false;
var enviroments = EnviromentConstants.GetEnviromentList();
foreach (var env in enviroments)
{
var item = Factory.CreateRibbonDropDownItem();
item.Label = env;
drpEnviroment.Items.Add(item);
}
}
private void FormatProfile()
{
try
{
_excelApp = Globals.ThisAddIn.Application;
_excelApp.Workbooks.Add();
_excelApp.ActiveWorkbook.ActiveSheet.Name = _sheetName01;
var optionList = new List<string>();
if (_cells == null)
_cells = new ExcelStyleCells(_excelApp);
_cells.GetCell("A1").Value = "CERREJÓN";
_cells.GetCell("A1").Style = _cells.GetStyle(StyleConstants.HeaderDefault);
_cells.MergeCells("A1", "A2");
_cells.GetCell("B1").Value = "STATISTICS PROFILES - ELLIPSE 8";
_cells.GetCell("B1").Style = _cells.GetStyle(StyleConstants.HeaderDefault);
_cells.MergeCells("B1", "J2");
_cells.GetCell("A4:AT4").Style = _cells.GetStyle(StyleConstants.Option);
_cells.GetCell("AU4").Style = _cells.GetStyle(StyleConstants.TitleInformation);
_cells.GetCell("C4").Value = "Equipment";
_cells.GetCell("D4").Value = "Fuel Type";
_cells.GetCell("E4").Value = "Fuel Capacity";
_cells.GetCell("F4").Value = "Primary Statistic";
_cells.GetCell("AU4").Value = "Result";
_cells.GetCell("A4").Value = "Options";
optionList.Add("3. Maintain Operating Statistics Profile");
optionList.Add("4. Maintain General Equipment Profile");
optionList.Add("Delete Profile");
_cells.SetValidationList(_cells.GetCell("A5:A100"), optionList);
_cells.GetCell("B4").Value = "Profile Type";
optionList = new List<string> {"EGI", "Equipment"};
_cells.SetValidationList(_cells.GetCell("B5:B100"), optionList);
const int startColumn = 7;
for (var i = 0; i < 20; i++)
{
_cells.GetRange(i*2 + startColumn, 3, i*2 + startColumn + 1, 3).Style =
_cells.GetStyle(StyleConstants.Option);
_cells.MergeCells(i*2 + startColumn, 3, i*2 + startColumn + 1, 3);
_cells.GetCell(i*2 + startColumn, 3).Value = i + 1;
_cells.GetCell(i*2 + startColumn, 4).Value = "Stat " + (i + 1);
_cells.GetCell(i*2 + startColumn + 1, 4).Value = "D/I";
}
}
catch (Exception error)
{
_cells.GetCell("AU5").Value = error.Message;
}
}
private void RibbonEllipse_Load(object sender, RibbonUIEventArgs e)
{
excelApp = Globals.ThisAddIn.Application;
if (cells == null)
cells = new ExcelStyleCells(excelApp);
var enviromentList = EnviromentConstants.GetEnviromentList();
foreach (var item in enviromentList)
{
var drpItem = Factory.CreateRibbonDropDownItem();
drpItem.Label = item;
drpRequisitionService.Items.Add(drpItem);
}
drpRequisitionService.SelectedItem.Label = Resources.RibbonEllipse_RibbonEllipse_Load_Productivo;
}
private void ValidateStats()
{
if (_cells == null)
_cells = new ExcelStyleCells(_excelApp);
var excelBook = _excelApp.ActiveWorkbook;
Worksheet excelSheet = excelBook.ActiveSheet;
if (excelSheet.Name != SheetName01) return;
if (drpBulkMaterialEnv.Label == null || drpBulkMaterialEnv.Label.Equals("")) return;
var currentRow = TittleRow + 1;
while ((_cells.GetNullIfTrimmedEmpty(_cells.GetCell(3, currentRow).Value)) != null)
{
var statType = _cells.GetNullIfTrimmedEmpty(_cells.GetCell(16, currentRow).Value);
var equipNo = _cells.GetNullIfTrimmedEmpty(_cells.GetCell(8, currentRow).Value);
var stat = _cells.GetNullIfTrimmedEmpty(_cells.GetCell(17, currentRow).Value);
var statDate = _cells.GetNullIfTrimmedEmpty(_cells.GetCell(14, currentRow).Value);
if (equipNo != null & statType != null & stat != null)
{
var lastStat = GetLastStatistic(equipNo, statType, statDate);
_cells.GetCell(17, currentRow).AddComment(Convert.ToString(lastStat.StatDate + " - " + lastStat.MeterValue, CultureInfo.InvariantCulture));
_cells.GetCell(17, currentRow).Style = _cells.GetStyle(Convert.ToDecimal(stat) < lastStat.MeterValue ? StyleConstants.Error : StyleConstants.Success);
}
currentRow++;
}
}
private void btnUnApplyDelete_Click(object sender, RibbonControlEventArgs e)
{
if (_cells == null)
_cells = new ExcelStyleCells(_excelApp);
var excelBook = _excelApp.ActiveWorkbook;
Worksheet excelSheet = excelBook.ActiveSheet;
if (excelSheet.Name != SheetName01) return;
var proxySheet = new BMUSheet.BulkMaterialUsageSheetService();
var opSheet = new BMUSheet.OperationContext();
if (drpBulkMaterialEnv.Label == null || drpBulkMaterialEnv.Label.Equals("")) return;
proxySheet.Url = _eFunctions.GetServicesUrl(drpBulkMaterialEnv.SelectedItem.Label) + "/BulkMaterialUsageSheet";
_frmAuth.SelectedEnviroment = drpBulkMaterialEnv.SelectedItem.Label;
_frmAuth.StartPosition = FormStartPosition.CenterScreen;
if (_frmAuth.ShowDialog() != DialogResult.OK) return;
opSheet.district = _frmAuth.EllipseDsct;
opSheet.maxInstances = 100;
opSheet.position = _frmAuth.EllipsePost;
opSheet.returnWarnings = false;
ClientConversation.authenticate(_frmAuth.EllipseUser, _frmAuth.EllipsePswd);
var currentRow = TittleRow + 1;
while ((_cells.GetNullIfTrimmedEmpty(_cells.GetCell(1, currentRow).Value)) != null)
{
var requestSheet = new BMUSheet.BulkMaterialUsageSheetDTO();
_cells.GetCell(1, currentRow).Select();
try
{
requestSheet.bulkMaterialUsageSheetId = _cells.GetNullIfTrimmedEmpty(_cells.GetCell(1, currentRow).Value);
var replySheet = proxySheet.unapply(opSheet, requestSheet);
if (replySheet.errors.Length > 0)
{
foreach (var t in replySheet.errors) { _cells.GetCell(ResultColumn, currentRow).Value += " - " + t.messageText; }
_cells.GetRange(1, currentRow, 6, currentRow).Style = _cells.GetStyle(StyleConstants.Error);
_cells.GetRange(1, currentRow, 6, currentRow).Select();
}
else
{
_cells.GetRange(1, currentRow, 6, currentRow).Style = _cells.GetStyle(StyleConstants.Success);
_cells.GetRange(1, currentRow, 6, currentRow).Select();
DeleteHeader(proxySheet, opSheet, requestSheet, currentRow, currentRow);
}
}
catch (Exception error)
{
_cells.GetRange(1, currentRow, 6, currentRow).Style = _cells.GetStyle(StyleConstants.Error);
_cells.GetCell(ResultColumn, currentRow).Value = error.Message;
_cells.GetCell(ResultColumn, currentRow).Select();
}
finally { currentRow++; }
}
}
private void OrderAndSort(Worksheet excelSheet)
{
if (_cells == null)
_cells = new ExcelStyleCells(_excelApp);
excelSheet.Cells.Columns.AutoFit();
excelSheet.Cells.Rows.AutoFit();
_excelSheetItems.Sort.SortFields.Clear();
_excelSheetItems.Sort.SortFields.Add(_cells.GetCell(2, TittleRow), XlSortOn.xlSortOnValues, XlOrder.xlDownThenOver, Type.Missing, Type.Missing);
_excelSheetItems.Sort.SortFields.Add(_cells.GetCell(3, TittleRow), XlSortOn.xlSortOnValues, XlOrder.xlDownThenOver, Type.Missing, Type.Missing);
_excelSheetItems.Sort.SortFields.Add(_cells.GetCell(4, TittleRow), XlSortOn.xlSortOnValues, XlOrder.xlDownThenOver, Type.Missing, Type.Missing);
_excelSheetItems.Sort.SortFields.Add(_cells.GetCell(6, TittleRow), XlSortOn.xlSortOnValues, XlOrder.xlDownThenOver, Type.Missing, Type.Missing);
_excelSheetItems.Sort.SortFields.Add(_cells.GetCell(9, TittleRow), XlSortOn.xlSortOnValues, XlOrder.xlDownThenOver, Type.Missing, Type.Missing);
_excelSheetItems.Sort.SortFields.Add(_cells.GetCell(9, TittleRow), XlSortOn.xlSortOnValues, XlOrder.xlDownThenOver, Type.Missing, Type.Missing);
_excelSheetItems.Sort.SortFields.Add(_cells.GetCell(10, TittleRow), XlSortOn.xlSortOnValues, XlOrder.xlDownThenOver, Type.Missing, Type.Missing);
_excelSheetItems.Sort.SortFields.Add(_cells.GetCell(11, TittleRow), XlSortOn.xlSortOnValues, XlOrder.xlDownThenOver, Type.Missing, Type.Missing);
_excelSheetItems.Sort.Apply();
}
private void ImportFile()
{
if (_cells == null)
_cells = new ExcelStyleCells(_excelApp);
var excelBook = _excelApp.ActiveWorkbook;
Worksheet excelSheet = excelBook.ActiveSheet;
if (excelSheet.Name != SheetName01) return;
_cells.GetRange(1, TittleRow + 1, ResultColumn, MaxRows).ClearFormats();
_cells.GetRange(1, TittleRow + 1, ResultColumn, MaxRows).ClearComments();
_cells.GetRange(1, TittleRow + 1, ResultColumn, MaxRows).ClearContents();
_cells.GetRange(1, TittleRow + 1, ResultColumn, MaxRows).NumberFormat = "@";
var openFileDialog1 = new OpenFileDialog
{
Filter = @"Archivos CSV|*.csv",
FileName = @"Seleccione un archivo de Texto",
Title = @"Programa de Lectura",
InitialDirectory = @"C:\\"
};
if (openFileDialog1.ShowDialog() != DialogResult.OK) return;
var filePath = openFileDialog1.FileName;
var inputFileDescription = new CsvFileDescription
{
SeparatorChar = ',',
FirstLineHasColumnNames = false,
EnforceCsvColumnAttribute = true
};
var cc = new CsvContext();
var bulkMaterials = cc.Read<BulkMaterial>(filePath, inputFileDescription);
var currentRow = TittleRow + 1;
foreach (var bulkMaterial in bulkMaterials)
{
try
{
_cells.GetCell(3, currentRow).Value = bulkMaterial.WarehouseId;
_cells.GetCell(4, currentRow).Value = DateTime.ParseExact(bulkMaterial.DefaultUsageDate, @"MM/dd/yy", CultureInfo.CurrentCulture).ToString("yyyyMMdd");
_cells.GetCell(8, currentRow).Value = bulkMaterial.EquipmentReference;
_cells.GetCell(11, currentRow).Value = bulkMaterial.BulkMaterialTypeId;
_cells.GetCell(13, currentRow).Value = bulkMaterial.Quantity;
}
catch (Exception error)
{
_cells.GetCell(ResultColumn, currentRow).Value = "Error: " + error.Message;
}
finally { currentRow++; }
}
OrderAndSort(excelSheet);
}
private void BulkMaterialFormatMultiple()
{
try
{
if (_cells == null)
_cells = new ExcelStyleCells(_excelApp);
var excelBook = _excelApp.Workbooks.Add();
Worksheet excelSheet = excelBook.ActiveSheet;
excelSheet.Name = SheetName01;
_cells = new ExcelStyleCells(_excelApp);
_cells.GetRange(1, TittleRow + 1, ResultColumn, MaxRows).Style = _cells.GetStyle(StyleConstants.Normal);
_cells.GetRange(1, TittleRow + 1, ResultColumn, MaxRows).ClearFormats();
_cells.GetRange(1, TittleRow + 1, ResultColumn, MaxRows).ClearComments();
_cells.GetRange(1, TittleRow + 1, ResultColumn, MaxRows).Clear();
_cells.GetRange(1, TittleRow + 1, ResultColumn, TittleRow + 1).NumberFormat = "@";
_cells.GetCell("A1").Value = "CERREJÓN";
_cells.GetCell("B1").Value = "Bulk Material Usage Sheet";
_cells.GetRange("A1", "B1").Style = _cells.GetStyle(StyleConstants.HeaderDefault);
_cells.GetRange("B1", "D1").Merge();
_cells.GetCell(1, TittleRow).Value = "Usage Sheet Id";
_cells.GetCell(2, TittleRow).Value = "District";
_cells.GetCell(3, TittleRow).Value = "Warehouse";
_cells.GetCell(4, TittleRow).Value = "Usage Date";
_cells.GetCell(5, TittleRow).Value = "Usage Time";
_cells.GetCell(6, TittleRow).Value = "General Account Code";
_cells.GetCell(7, TittleRow).Value = "Usage Item Id";
_cells.GetCell(8, TittleRow).Value = "Equipment Reference";
_cells.GetCell(9, TittleRow).Value = "Component Code";
_cells.GetCell(10, TittleRow).Value = "Modifier Code";
_cells.GetCell(11, TittleRow).Value = "Bulk Material Type";
_cells.GetCell(12, TittleRow).Value = "Condition Monitoring Action";
_cells.GetCell(13, TittleRow).Value = "Quantity";
_cells.GetCell(14, TittleRow).Value = "Transaction Date";
_cells.GetCell(15, TittleRow).Value = "Statistic Time";
_cells.GetCell(16, TittleRow).Value = "Statistic Type";
_cells.GetCell(17, TittleRow).Value = "Statistic Meter";
_cells.GetCell(ResultColumn, TittleRow).Value = "Result";
#region Styles
_cells.GetCell(1, TittleRow).Style = _cells.GetStyle(StyleConstants.TitleInformation);
_cells.GetCell(2, TittleRow).Style = _cells.GetStyle(StyleConstants.TitleOptional);
_cells.GetCell(3, TittleRow).Style = _cells.GetStyle(StyleConstants.TitleRequired);
_cells.GetCell(4, TittleRow).Style = _cells.GetStyle(StyleConstants.TitleRequired);
_cells.GetCell(5, TittleRow).Style = _cells.GetStyle(StyleConstants.TitleOptional);
_cells.GetCell(6, TittleRow).Style = _cells.GetStyle(StyleConstants.TitleRequired);
_cells.GetCell(7, TittleRow).Style = _cells.GetStyle(StyleConstants.TitleInformation);
_cells.GetCell(8, TittleRow).Style = _cells.GetStyle(StyleConstants.TitleRequired);
_cells.GetCell(9, TittleRow).Style = _cells.GetStyle(StyleConstants.TitleOptional);
_cells.GetCell(10, TittleRow).Style = _cells.GetStyle(StyleConstants.TitleOptional);
_cells.GetCell(11, TittleRow).Style = _cells.GetStyle(StyleConstants.TitleRequired);
_cells.GetCell(12, TittleRow).Style = _cells.GetStyle(StyleConstants.TitleRequired);
_cells.GetCell(13, TittleRow).Style = _cells.GetStyle(StyleConstants.TitleRequired);
_cells.GetCell(14, TittleRow).Style = _cells.GetStyle(StyleConstants.TitleRequired);
_cells.GetCell(15, TittleRow).Style = _cells.GetStyle(StyleConstants.TitleOptional);
_cells.GetCell(16, TittleRow).Style = _cells.GetStyle(StyleConstants.TitleOptional);
_cells.GetCell(17, TittleRow).Style = _cells.GetStyle(StyleConstants.TitleOptional);
_cells.GetCell(ResultColumn, TittleRow).Style = _cells.GetStyle(StyleConstants.TitleInformation);
#endregion
#region Instructions
_cells.GetCell("E1").Value = "OBLIGATORIO";
_cells.GetCell("E1").Style = _cells.GetStyle(StyleConstants.TitleRequired);
_cells.GetCell("E2").Value = "OPCIONAL";
_cells.GetCell("E2").Style = _cells.GetStyle(StyleConstants.TitleOptional);
_cells.GetCell("E3").Value = "INFORMATIVO";
_cells.GetCell("E3").Style = _cells.GetStyle(StyleConstants.TitleInformation);
_cells.GetCell("E4").Value = "ACCIÓN A REALIZAR";
_cells.GetCell("E4").Style = _cells.GetStyle(StyleConstants.TitleAction);
_cells.GetCell("E5").Value = "REQUERIDO ADICIONAL";
_cells.GetCell("E5").Style = _cells.GetStyle(StyleConstants.TitleAdditional);
#endregion
_optionList = new List<string>
{
" Fuel/Diesel",
"B - Condition Monitoring Fitment",
"L - Condition Monitoring Rebuild in Situ",
"O - Oil Changed",
"C - Condition Monitoring Defitment",
"A - Oil Added",
"F - Filter Changed"
};
_cells.SetValidationList(_cells.GetCell(12, TittleRow + 1), _optionList);
_excelSheetItems = excelSheet.ListObjects.AddEx(XlListObjectSourceType.xlSrcRange,
_cells.GetRange(1, TittleRow, ResultColumn, MaxRows), XlListObjectHasHeaders: XlYesNoGuess.xlYes);
_excelSheetItems.Name = "ExcelSheetItems";
_cells.GetRange(1, TittleRow + 1, ResultColumn, MaxRows).NumberFormat = "@";
OrderAndSort(excelSheet);
}
catch (Exception error)
{
MessageBox.Show(error.Message);
}
}
/// <summary>
/// Crea las instancias a los servicios BulkMaterialUsageSheetService y BulkMaterialUsageSheetItemService
/// </summary>
private void BulkMaterialExcecute()
{
try
{
_cells.GetRange(1, TittleRow + 1, ResultColumn, MaxRows).ClearFormats();
_cells.GetRange(1, TittleRow + 1, ResultColumn, MaxRows).ClearComments();
if (_cells == null)
_cells = new ExcelStyleCells(_excelApp);
var excelBook = _excelApp.ActiveWorkbook;
Worksheet excelSheet = excelBook.ActiveSheet;
if (excelSheet.Name != SheetName01) return;
var proxySheet = new BMUSheet.BulkMaterialUsageSheetService();
var opSheet = new BMUSheet.OperationContext();
var proxyItem = new BMUSheetItem.BulkMaterialUsageSheetItemService();
var opItem = new BMUSheetItem.OperationContext();
if (drpBulkMaterialEnv.Label == null || drpBulkMaterialEnv.Label.Equals("")) return;
proxySheet.Url = _eFunctions.GetServicesUrl(drpBulkMaterialEnv.SelectedItem.Label) + "/BulkMaterialUsageSheet";
proxyItem.Url = _eFunctions.GetServicesUrl(drpBulkMaterialEnv.SelectedItem.Label) + "/BulkMaterialUsageSheetItem";
_frmAuth.SelectedEnviroment = drpBulkMaterialEnv.SelectedItem.Label;
_frmAuth.StartPosition = FormStartPosition.CenterScreen;
if (_frmAuth.ShowDialog() != DialogResult.OK) return;
opSheet.district = _frmAuth.EllipseDsct;
opSheet.maxInstances = 100;
opSheet.position = _frmAuth.EllipsePost;
opSheet.returnWarnings = false;
opItem.district = _frmAuth.EllipseDsct;
opItem.maxInstances = 100;
opItem.position = _frmAuth.EllipsePost;
opItem.returnWarnings = false;
ClientConversation.authenticate(_frmAuth.EllipseUser, _frmAuth.EllipsePswd);
try
{
_excelSheetItems.Sort.SortFields.Clear();
_excelSheetItems.Sort.SortFields.Add(_cells.GetCell(2, TittleRow), XlSortOn.xlSortOnValues, XlOrder.xlDownThenOver, Type.Missing, Type.Missing);
_excelSheetItems.Sort.SortFields.Add(_cells.GetCell(3, TittleRow), XlSortOn.xlSortOnValues, XlOrder.xlDownThenOver, Type.Missing, Type.Missing);
_excelSheetItems.Sort.SortFields.Add(_cells.GetCell(4, TittleRow), XlSortOn.xlSortOnValues, XlOrder.xlDownThenOver, Type.Missing, Type.Missing);
_excelSheetItems.Sort.SortFields.Add(_cells.GetCell(6, TittleRow), XlSortOn.xlSortOnValues, XlOrder.xlDownThenOver, Type.Missing, Type.Missing);
_excelSheetItems.Sort.SortFields.Add(_cells.GetCell(9, TittleRow), XlSortOn.xlSortOnValues, XlOrder.xlDownThenOver, Type.Missing, Type.Missing);
_excelSheetItems.Sort.SortFields.Add(_cells.GetCell(9, TittleRow), XlSortOn.xlSortOnValues, XlOrder.xlDownThenOver, Type.Missing, Type.Missing);
_excelSheetItems.Sort.SortFields.Add(_cells.GetCell(10, TittleRow), XlSortOn.xlSortOnValues, XlOrder.xlDownThenOver, Type.Missing, Type.Missing);
_excelSheetItems.Sort.SortFields.Add(_cells.GetCell(11, TittleRow), XlSortOn.xlSortOnValues, XlOrder.xlDownThenOver, Type.Missing, Type.Missing);
_excelSheetItems.Sort.Apply();
var currentRow = TittleRow + 1;
while ((_cells.GetNullIfTrimmedEmpty(_cells.GetCell(3, currentRow).Value)) != null)
{
var currentHeader = currentRow;
var requestSheet = new BMUSheet.BulkMaterialUsageSheetDTO();
var requestItemList = new List<BMUSheetItem.BulkMaterialUsageSheetItemDTO>();
var allRequestItemList = new List<BMUSheetItem.BulkMaterialUsageSheetItemDTO>();
_cells.GetCell(1, currentRow).Select();
//llenado de variables del encabezado de la hoja
requestSheet.bulkMaterialUsageSheetId = _cells.GetNullIfTrimmedEmpty(_cells.GetCell(1, currentRow).Value) != null ? _cells.GetEmptyIfNull(_cells.GetCell(1, currentRow).Value) : null;
requestSheet.districtCode = _cells.GetNullIfTrimmedEmpty(_cells.GetCell(2, currentRow).Value) ?? "ICOR";
requestSheet.warehouseId = _cells.GetEmptyIfNull(_cells.GetCell(3, currentRow).Value);
requestSheet.defaultUsageDate = _cells.GetEmptyIfNull(_cells.GetCell(4, currentRow).Value);
requestSheet.defaultAccountCode = _cells.GetNullIfTrimmedEmpty(_cells.GetCell(6, currentRow).Value) != null ? _cells.GetEmptyIfNull(_cells.GetCell(6, currentRow).Value) : null;
requestSheet.defaultAccountCode = requestSheet.defaultAccountCode ?? GetBulkAccountCode(_cells.GetNullIfTrimmedEmpty(_cells.GetCell(8, currentRow).Value));
//Crea el encabezado
var replySheet = proxySheet.create(opSheet, requestSheet);
//valida si el encabezado tiene errores
if (replySheet.errors.Length > 0)
{
foreach (var t in replySheet.errors)
_cells.GetCell(ResultColumn, currentRow).Value += " - " + t.messageText;
_cells.GetRange(1, currentHeader, 6, currentRow).Style = _cells.GetStyle(StyleConstants.Error);
_cells.GetRange(1, currentHeader, 6, currentRow).Select();
currentRow++;
}
else
{
//si el encabezado no tiene errores empueza a agregar los items a la coleccion.
requestSheet.bulkMaterialUsageSheetId = replySheet.bulkMaterialUsageSheetDTO.bulkMaterialUsageSheetId;
_cells.GetCell(1, currentRow).Value = replySheet.bulkMaterialUsageSheetDTO.bulkMaterialUsageSheetId;
//mientras que el encabezado sea el mismo, llene la lista de items
var sheetId = _cells.GetNullIfTrimmedEmpty(_cells.GetCell(1, currentRow).Value);
var warehouseId = _cells.GetEmptyIfNull(_cells.GetCell(3, currentRow).Value);
var defaultUsageDate = _cells.GetEmptyIfNull(_cells.GetCell(4, currentRow).Value);
var defaultAccountCode = (_cells.GetNullIfTrimmedEmpty(_cells.GetCell(6, currentRow).Value) != null ? _cells.GetEmptyIfNull(_cells.GetCell(6, currentRow).Value) : null);
defaultAccountCode = defaultAccountCode ?? GetBulkAccountCode(_cells.GetNullIfTrimmedEmpty(_cells.GetCell(8, currentRow).Value));
while (
(
requestSheet.bulkMaterialUsageSheetId == sheetId ||
(
sheetId == null &&
requestSheet.warehouseId == warehouseId &&
requestSheet.defaultUsageDate == defaultUsageDate &&
requestSheet.defaultAccountCode == defaultAccountCode
)
)
)
{
ItemListAdd(currentRow, requestSheet, requestItemList, allRequestItemList, excelSheet);
currentRow++;
sheetId = _cells.GetNullIfTrimmedEmpty(_cells.GetCell(1, currentRow).Value);
warehouseId = _cells.GetEmptyIfNull(_cells.GetCell(3, currentRow).Value);
defaultUsageDate = _cells.GetEmptyIfNull(_cells.GetCell(4, currentRow).Value);
defaultAccountCode = (_cells.GetNullIfTrimmedEmpty(_cells.GetCell(6, currentRow).Value) != null ? _cells.GetEmptyIfNull(_cells.GetCell(6, currentRow).Value) : null);
defaultAccountCode = defaultAccountCode ?? GetBulkAccountCode(_cells.GetNullIfTrimmedEmpty(_cells.GetCell(8, currentRow).Value));
}
try
{
if (requestItemList.Count > 0)
{
//esta operacion agrega la lista de items al encabezado
var replyItem = proxyItem.multipleCreate(opItem, requestItemList.ToArray());
//recorre el resultado de la ejecucion de la operacion multipleCreate donde hubo errores.
var errorCounter = 0;
foreach (var rItem in replyItem.Where(rItem => rItem.errors.Length > 0))
{
errorCounter++;
var errorMessage = rItem.errors.Aggregate("", (current, error) => current + (error.messageText + ", "));
var currentItem = 0;
foreach (var item in allRequestItemList)
{
if (_cells.GetEmptyIfNull(item.bulkMaterialUsageSheetId).ToUpper() == _cells.GetEmptyIfNull(rItem.bulkMaterialUsageSheetItemDTO.bulkMaterialUsageSheetId) &
_cells.GetEmptyIfNull(item.bulkMaterialUsageSheetItemId).ToUpper() == _cells.GetEmptyIfNull(rItem.bulkMaterialUsageSheetItemDTO.bulkMaterialUsageSheetItemId)
)
{
requestItemList.Remove(item);
_cells.GetRange(8, currentHeader + currentItem, 13, currentHeader + currentItem).Style = _cells.GetStyle(StyleConstants.Error);
_cells.GetCell(ResultColumn, currentHeader + currentItem).Value += errorMessage;
_cells.GetCell(ResultColumn, currentHeader + currentItem).Select();
}
currentItem++;
}
}
if (errorCounter > 0 & requestItemList.Count > 0)
{
try
{
var deleteHeader = false;
replyItem = proxyItem.multipleCreate(opItem, requestItemList.ToArray());
foreach (var rItem in replyItem.Where(item => item.errors.Length > 0)) { deleteHeader = true; }
if (deleteHeader)
{
DeleteHeader(proxySheet, opSheet, requestSheet, currentHeader, currentRow - 1);
}
else
{
ApplyHeader(proxySheet, opSheet, requestSheet, currentRow - 1, currentHeader);
}
}
catch (Exception error)
{
MessageBox.Show(error.Message);
DeleteHeader(proxySheet, opSheet, requestSheet, currentHeader, currentRow - 1);
}
}
else if (errorCounter == 0 & requestItemList.Count > 0)
{
ApplyHeader(proxySheet, opSheet, requestSheet, currentRow - 1, currentHeader);
}
else
{
DeleteHeader(proxySheet, opSheet, requestSheet, currentHeader, currentRow - 1);
}
}
else
{
_cells.GetCell(ResultColumn, currentRow - 1).Value += "No hay Items para Aplicar en esta hoja!";
DeleteHeader(proxySheet, opSheet, requestSheet, currentHeader, currentRow - 1);
}
}
catch (Exception error)
{
MessageBox.Show(error.Message);
}
}
}
}
catch (Exception error)
{
MessageBox.Show(error.Message);
}
}
catch (Exception error)
{
MessageBox.Show(error.Message);
}
}
/// <summary>
/// Dat formato a la hoja para cargar los items contractuales y los StockCodes asociados a estas.
/// </summary>
private void FormatSheet()
{
_excelApp = Globals.ThisAddIn.Application;
var excelBook = _excelApp.Workbooks.Add();
Microsoft.Office.Interop.Excel.Worksheet excelSheet = excelBook.ActiveSheet;
excelSheet.Name = SheetName01;
_worksheet = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet);
_eFunctions.SetDBSettings(drpEnviroment.SelectedItem.Label);
if (_cells == null)
_cells = new ExcelStyleCells(_excelApp);
_cells.GetCell("A1").Value = "CERREJÓN";
_cells.GetCell("B1").Value = "CREATE STOCKCODES INST";
_cells.GetCell("A3").Value = "Contract No";
_cells.GetCell("A4").Value = "Contract Prefix";
_cells.GetCell("A5").Value = "Stock Code";
_cells.GetCell("B5").Value = "Stock Description";
_cells.GetCell("C5").Value = "Unit Of Issue";
_cells.GetCell("D5").Value = "Part No";
_cells.GetCell("E5").Value = "Result";
//AA y MI fueron establecidos en la estrategia de Instalaciones como los prefijos para identificar los contratos
var optionList = new List<string> {"AA", "MI"};
_cells.SetValidationList(_cells.GetCell("B4"), optionList);
_cells.GetCell("A1").Style = _cells.GetStyle(StyleConstants.HeaderDefault);
_cells.GetCell("A3").Style = _cells.GetStyle(StyleConstants.Option);
_cells.GetCell("A4").Style = _cells.GetStyle(StyleConstants.Option);
_cells.GetCell("B1").Style = _cells.GetStyle(StyleConstants.HeaderDefault);
_cells.GetCell("B3").Style = _cells.GetStyle(StyleConstants.Select);
_cells.GetCell("B4").Style = _cells.GetStyle(StyleConstants.Select);
_cells.GetRange("A5", "D5").Style = _cells.GetStyle(StyleConstants.TitleRequired);
_cells.GetCell("E5").Style = _cells.GetStyle(StyleConstants.TitleInformation);
_cells.MergeCells("A1", "A2");
var contractRange = _worksheet.Controls.AddNamedRange(_worksheet.Range["B3:B4"], "ContractRange");
contractRange.Change += changesCotractPrefixRange_Change;
excelSheet.Cells.Columns.AutoFit();
excelSheet.Cells.Rows.AutoFit();
}