private void Click_Save(object sender, EventArgs e)
{
if (!IsValidToSave())
{
return;
}
string stringOriginalCode = String.Empty;
string stringCode = String.Empty;
foreach (DataGridViewRow row in DataGridCodes.Rows)
{
string stringTemp = Util.ConvertDynamicToString(row.Cells[0].Value);
stringOriginalCode += stringTemp + " ";
stringCode += Util.NormalizeString(stringTemp) + " ";
}
stringOriginalCode = stringOriginalCode.TrimEnd(' ');
MaterialStuck materialStuck = new MaterialStuck()
{
OriginalCode = stringOriginalCode,
Name = Util.NormalizeString(NameInput.Text),
Code = stringCode,
Ktl = Util.NormalizeString(KLTInput.Text),
Description = DescriptionInput.Text,
Unit = Util.ConvertDynamicToString(UnitComboBox.SelectedItem),
Location = Util.NormalizeString(LocationInput.Text),
ProviderName = Util.ConvertDynamicToString(SupplierComboBox.SelectedItem),
Total = Util.ConvertDynamicToDouble(AmountInput.Value)
};
DataDB.UpdateMaterialListForDummies(materialStuck);
Util.ShowMessage(AlarmType.SUCCESS, $"Se han insertado el registro");
FillDataGrid();
}
private void DataGrid_UserDeletingRow(object sender, DataGridViewRowCancelEventArgs e)
{
DataGridViewRow row = e.Row;
try
{
MaterialStuck material = new MaterialStuck
{
Id = (Guid)row.Cells["Id"].Value,
OriginalCode = Util.ConvertDynamicToString(row.Cells["OriginalCode"].Value),
//Name = Util.ConvertDynamicToString(row.Cells["Name"].Value),
Description = Util.ConvertDynamicToString(row.Cells["Description"].Value),
Provider = Util.ConvertDynamicToString(row.Cells["ProviderName"].Value),
Total = Util.ConvertDynamicToDouble(row.Cells["StockTotal"].Value),
Unit = Util.ConvertDynamicToString(row.Cells["Unit"].Value),
Location = Util.ConvertDynamicToString(row.Cells["Location"].Value),
Ktl = Util.ConvertDynamicToString(row.Cells["KLT"].Value),
IsActive = false
};
MaterialStuck tempMaterial = DeletedMaterialList.Find(item => item.Id == material.Id);
//MaterialStuck tempMaterial = DeletedMaterialList.Find(item => (item.Id == material.Id && (Util.IsEmptyString(material.OriginalCode) ? true : item.OriginalCode == material.OriginalCode)));
if (!DeletedMaterialList.Contains(tempMaterial))
{
DeletedMaterialList.Add(material);
}
}
catch (Exception ex)
{
//
}
}
private bool IsAlreadyInsideList(List <MaterialStuck> list, MaterialStuck material)
{
MaterialStuck tempMaterial = list.Find(item => (item.Id == material.Id && !Util.IsEmptyGuid(material.Id)));
if (!list.Contains(tempMaterial))
{
return(false);
}
return(true);
}
public static void UpdateMaterialListForDummies(MaterialStuck material)
{
OpenConnection();
try
{
using (IDbConnection dbConnection = connection)
{
string originalCode = material.OriginalCode;
string code = material.Code;
string name = material.Name ?? "";
double total = material.Total;
string ktl = material.Ktl ?? "";
string description = material.Description ?? "";
string provider = material.ProviderName ?? "";
string location = material.Location ?? "";
string unit = material.Unit ?? "";
IDbCommand dbCommand = dbConnection.CreateCommand();
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = @"dbo.sproc_UpdateWarehouseMaterialForDummies";
dbCommand.Parameters.Add(new SqlParameter("@Code", code));
dbCommand.Parameters.Add(new SqlParameter("@OriginalCode", originalCode));
dbCommand.Parameters.Add(new SqlParameter("@Description", description));
dbCommand.Parameters.Add(new SqlParameter("@ProviderName", provider));
dbCommand.Parameters.Add(new SqlParameter("@Total", total));
dbCommand.Parameters.Add(new SqlParameter("@Unit", unit));
dbCommand.Parameters.Add(new SqlParameter("@Location", location));
dbCommand.Parameters.Add(new SqlParameter("@KLT", ktl));
dbCommand.Parameters.Add(new SqlParameter("@MaterialName", name));
dbCommand.ExecuteNonQuery();
}
connection.Close();
}
catch (Exception exc)
{
MessageBox.Show($"Error SQL: {exc.Message}");
connection.Close();
throw new Exception("Get (EnabledProducts) error: " + exc.Message, exc);
}
}
private void Click_Save(object sender, EventArgs e)
{
List <MaterialStuck> materialList = new List <MaterialStuck>();
DataTable dataTable = (DataTable)DataGrid.DataSource;
DataTable dataTableChanges = dataTable.GetChanges(DataRowState.Modified);
DataTable dataTableInserted = dataTable.GetChanges(DataRowState.Added);
if (dataTableChanges == null && DeletedMaterialList.Count == 0 && dataTableInserted == null)
{
MessageBox.Show("No hay ningún cambio hecho");
return;
}
materialList.AddRange(DeletedMaterialList);
if (dataTableChanges != null)
{
foreach (System.Data.DataRow row in dataTableChanges.Rows)
{
MaterialStuck material = null;
try
{
material = new MaterialStuck
{
Id = new Guid(row["Id"].ToString()),
OriginalCode = Util.ConvertDynamicToString(row["OriginalCode"]),
//Name = Util.ConvertDynamicToString(row["Name"]),
Description = Util.ConvertDynamicToString(row["Description"]),
Provider = Util.ConvertDynamicToString(row["ProviderName"]),
Total = Util.ConvertDynamicToDouble(row["StockTotal"]),
Unit = Util.ConvertDynamicToString(row["Unit"]),
Location = Util.ConvertDynamicToString(row["Location"]),
Ktl = Util.ConvertDynamicToString(row["KLT"]),
IsActive = Util.StringToBool(row["IsActive"].ToString())
};
}
catch
{
Util.ShowMessage(AlarmType.WARNING, "No puede cambiar una fila recién insertada, salga de la ventana e intentelo de nuevo por favor");
continue;
}
if (material.OriginalCode != String.Empty)
{
if (!IsAlreadyInsideList(materialList, material))
{
materialList.Add(material);
}
}
else
{
Util.ShowMessage(AlarmType.WARNING, "No puede dejar la clave (Code) de un producto vacio");
return;
}
materialList.Add(material);
}
}
if (dataTableInserted != null)
{
foreach (System.Data.DataRow row in dataTableInserted.Rows)
{
MaterialStuck material = new MaterialStuck
{
OriginalCode = Util.ConvertDynamicToString(row["OriginalCode"]),
//Name = Util.ConvertDynamicToString(row["Name"]),
Description = Util.ConvertDynamicToString(row["Description"]),
Provider = Util.ConvertDynamicToString(row["ProviderName"]),
Total = Util.ConvertDynamicToDouble(row["StockTotal"]),
Unit = Util.ConvertDynamicToString(row["Unit"]),
Location = Util.ConvertDynamicToString(row["Location"]),
Ktl = Util.ConvertDynamicToString(row["KLT"]),
IsActive = true
};
if (material.OriginalCode != String.Empty)
{
if (!IsAlreadyInsideList(materialList, material))
{
materialList.Add(material);
}
}
else
{
Util.ShowMessage(AlarmType.WARNING, "No puede dejar la clave (Code) de un producto vacio");
return;
}
}
}
DataDB.UpdateMaterialList(materialList);
Util.ShowMessage(AlarmType.SUCCESS, $"Se han cambiado los registro(s)");
dataTable.AcceptChanges();
//if (dataTableInserted != null)
//{
// FillDataGrid();
// DataGrid.FirstDisplayedScrollingRowIndex = DataGrid.RowCount - 1;
//}
}
public static List <MaterialStuck> ConvertExcelToMaterialListFromLetfOvers(string filePath)
{
List <MaterialStuck> materialList = new List <MaterialStuck>();
Excel.Application app = new Excel.Application();
Excel.Workbook workbook = app.Workbooks.Open(filePath);
foreach (Excel._Worksheet sheet in workbook.Sheets)
{
Excel.Range range = sheet.UsedRange;
int rowCount = range.Rows.Count;
int colCount = range.Columns.Count;
if (sheet.Name.ToUpper() == Defs.EXCLUDED_SHEET_COSTOS || sheet.Name.ToUpper() == Defs.EXCLUDED_SHEET_COLORES)
{
continue;
}
for (int rowIndex = 2; rowIndex <= rowCount; rowIndex++)
{
MaterialStuck material = new MaterialStuck();
for (int colIndex = 1; colIndex <= 2; colIndex++)
{
int colorNumber = System.Convert.ToInt32(((Excel.Range)sheet.Cells[rowIndex, colIndex]).Interior.Color);
Color color = System.Drawing.ColorTranslator.FromOle(colorNumber);
if (!Util.IsLike(color.Name, Defs.COLOR_GREEN))
{
var value = range.Cells[rowIndex, colIndex].Value2;
string code = (string)(value + "");
break;
}
if (range.Cells[rowIndex, colIndex] != null && range.Cells[rowIndex, colIndex].Value2 != null)
{
var value = range.Cells[rowIndex, colIndex].Value2;
if (colIndex == Defs.NUM_COL_MATERIAL_CODE)
{
string code = (string)(value + "");
material.Code = Util.NormalizeString(code);
material.OriginalCode = code;
}
else if (colIndex == Defs.NUM_COL_AMOUNT)
{
double amount;
try
{
amount = (double)value;
}
catch (Exception e)
{
amount = 0;
}
material.Total = amount;
}
if (rowIndex != 1 && material.Code != null && material.Total != 0)
{
material.Location = sheet.Name;
materialList.Add(material);
break;
}
}
}
}
Marshal.ReleaseComObject(range);
Marshal.ReleaseComObject(sheet);
}
//cleanup
GC.Collect();
GC.WaitForPendingFinalizers();
//close and release
workbook.Close(0);
Marshal.ReleaseComObject(workbook);
//quit and release
app.Quit();
Marshal.ReleaseComObject(app);
return(materialList);
}
public static List <MaterialStuck> ConvertExcelToMaterialListFromWarehouseExcel(string filePath)
{
List <MaterialStuck> materialList = new List <MaterialStuck>();
List <string> errorList = new List <string>();
Excel.Application app = new Excel.Application();
Excel.Workbook workbook = app.Workbooks.Open(filePath);
foreach (Excel._Worksheet sheet in workbook.Sheets)
{
Excel.Range range = sheet.UsedRange;
int rowCount = range.Rows.Count;
int colCount = range.Columns.Count;
List <Column> headercolumns = new List <Column>(colCount);
string sheetName = sheet.Name.ToUpper();
if (sheetName == Defs.EXCLUDED_SHEET_COSTOS || sheetName == Defs.EXCLUDED_SHEET_LEYENDA)
{
continue;
}
for (int rowIndex = 1; rowIndex <= rowCount; rowIndex++)
{
MaterialStuck material = new MaterialStuck();
if (headercolumns.Count < MIN_COLUMNS_BOM_AMOUNT) //Get Columns names
{
for (int colIndex = 1; colIndex <= colCount; colIndex++)
{
if (range.Cells[rowIndex, colIndex] != null && range.Cells[rowIndex, colIndex].Value2 != null)
{
var colNameTemp = range.Cells[rowIndex, colIndex].Value2.ToString();
string columnName = Util.ConvertDynamicToString(colNameTemp);
Column column = new Column(columnName, colIndex);
headercolumns.Add(column);
}
}
if (rowIndex == HEADER_COLUMN_TOLERANCE)
{
break;
}
}
else
{
Column colWaterhouseId = headercolumns.Find(col => Util.IsLike(col.Name, Defs.COL_WATERHOUSE_IDS));
Column colAmount = headercolumns.Find(col => Util.IsLike(col.Name, Defs.COL_TOTAL));
Column colUnit = headercolumns.Find(col => Util.IsLike(col.Name, Defs.COL_UNIT));
Column colLocation = headercolumns.Find(col => Util.IsLike(col.Name, Defs.COL_LOCATION));
Column colProvider = headercolumns.Find(col => Util.IsLike(col.Name, Defs.COL_PROVIDER));
Column colKlt = headercolumns.Find(col => Util.IsLike(col.Name, Defs.COL_KLT));
if (colWaterhouseId == null)
{
MessageBox.Show($"Formato no reconocido en {sheetName}");
break;
}
var dynamicCode = range.Cells[rowIndex, colWaterhouseId.Index].Value;
var dynamicAmount = range.Cells[rowIndex, colAmount.Index].Value;
var dynamicUnit = range.Cells[rowIndex, colUnit.Index].Value;
var dynamicLocation = range.Cells[rowIndex, colLocation.Index].Value;
var dynamicProvider = range.Cells[rowIndex, colProvider.Index].Value;
var dynamicKlt = range.Cells[rowIndex, colKlt.Index].Value;
string materialCode = Util.ConvertDynamicToString(dynamicCode);
material.Code = Util.NormalizeString(materialCode);
material.OriginalCode = materialCode;
material.Total = Util.ConvertDynamicToDouble(dynamicAmount);
material.Unit = Util.ConvertDynamicToString(dynamicUnit);
material.Location = Util.ConvertDynamicToString(dynamicLocation);
material.Provider = Util.ConvertDynamicToString(dynamicProvider);
material.Ktl = Util.ConvertDynamicToString(dynamicKlt);
//Add Valid material to list
if (!Util.IsEmptyString(material.Code))
{
materialList.Add(material);
}
}
}
}
//cleanup
GC.Collect();
GC.WaitForPendingFinalizers();
//close and release
workbook.Close(0);
Marshal.ReleaseComObject(workbook);
//quit and release
app.Quit();
Marshal.ReleaseComObject(app);
return(materialList);
}
public static List <MaterialStuck> ConvertExcelToMaterialListFromMemo(string filePath)
{
List <MaterialStuck> materialList = new List <MaterialStuck>();
List <string> errorList = new List <string>();
Excel.Application app = new Excel.Application();
Excel.Workbook workbook;
try
{
workbook = app.Workbooks.Open(filePath, UpdateLinks: 2);
}
catch (Exception e)
{
Util.ShowMessage(AlarmType.ERROR, e.Message);
return(null);
}
foreach (Excel._Worksheet sheet in workbook.Sheets)
{
if (sheet.Visible == Excel.XlSheetVisibility.xlSheetHidden)
{
continue;
}
Excel.Range range = sheet.UsedRange;
int rowCount = range.Rows.Count;
int colCount = range.Columns.Count;
List <Column> headercolumns = new List <Column>(colCount);
string sheetName = sheet.Name.ToUpper();
int rowToleranceIndex = 0;
for (int rowIndex = 1; rowIndex <= rowCount; rowIndex++)
{
MaterialStuck material = new MaterialStuck();
if (headercolumns.Count < 4) //Get Columns names
{
colCount = 4;
for (int colIndex = 1; colIndex <= colCount; colIndex++)
{
try
{
if (range.Cells[rowIndex, colIndex] != null && range.Cells[rowIndex, colIndex].Value2 != null)
{
string columnName = (string)range.Cells[rowIndex, colIndex].Value2.ToString();
Column column = new Column(columnName, colIndex);
headercolumns.Add(column);
}
}
catch (Exception e)
{
break;
}
}
if (rowIndex == HEADER_COLUMN_TOLERANCE)
{
break;
}
}
else
{
string errorItem = String.Empty;
string materialMessageItem = String.Empty;
Column colMaterialCode = headercolumns.Find(col => Util.IsLike(col.Name, Defs.COL_MATERIAL_ORDERN_MEMO));
Column colAmount = headercolumns.Find(col => Util.IsLike(col.Name, Defs.COL_PZAS_MEMO));
Column colMarca = headercolumns.Find(col => Util.IsLike(col.Name, Defs.COL_MARCA_MEMO));
Column colDescription = headercolumns.Find(col => Util.IsLike(col.Name, Defs.COL_DESCRIPCION_MEMO));
if (colMaterialCode == null)
{
errorItem = $"\n*ERROR: Formato no reconocido en la hoja {sheet.Name.ToString()}, la columna del número de material, debe de decir: \"TEXTO BREVE Clave de material del proveedor\"";
errorList.Add(errorItem);
break;
}
var dynamicCode = range.Cells[rowIndex, colMaterialCode.Index].Value;
var dynamicAmount = range.Cells[rowIndex, colAmount.Index].Value;
var dynamicMarca = range.Cells[rowIndex, colMarca.Index].Value;
var dynamicDescription = range.Cells[rowIndex, colDescription.Index].Value;
string materialCode = Util.ConvertDynamicToString(dynamicCode) == "?" ? String.Empty : Util.ConvertDynamicToString(dynamicCode);
if (Util.IsEmptyString(materialCode))
{
rowToleranceIndex++;
if (EMPTINESS_ROW_TOLERANCE < rowToleranceIndex)
{
break;
}
}
material.Code = Util.NormalizeString(materialCode);
material.OriginalCode = materialCode;
material.Description = Util.ConvertDynamicToString(dynamicDescription);
material.Total = Util.ConvertDynamicToDouble(dynamicAmount);
material.Provider = Util.ConvertDynamicToString(dynamicMarca) == "?" ? "" : Util.ConvertDynamicToString(dynamicMarca);
material.Ktl = sheet.Name;
//Add Valid material to list
if (!Util.IsEmptyString(material.Code))
{
if (Util.IsEmptyString(material.Unit))
{
material.Unit = "Unidad";
}
materialList.Add(material);
materialMessageItem = $"\nPROCESANDO: Hoja: {sheet.Name} Num Parte: {material.Code} Cantidad: {material.Total} Unidad {material.Unit}";
}
else if (!Util.IsEmptyString(material.Code))
{
errorItem = $"No hay información en la columna de {Defs.COL_AMOUNT} en la fila {rowIndex} de la hoja {sheet.Name.ToString()}";
errorList.Add(errorItem);
}
}
}
Marshal.ReleaseComObject(range);
Marshal.ReleaseComObject(sheet);
}
//cleanup
GC.Collect();
GC.WaitForPendingFinalizers();
//close and release
workbook.Close(0);
Marshal.ReleaseComObject(workbook);
//quit and release
app.Quit();
Marshal.ReleaseComObject(app);
if (errorList.Count > 0)
{
materialList.Clear();
Util.ShowMessage(AlarmType.ERROR, errorList);
}
return(materialList);
}
