public static Order UpdateAssignOrder(ulong orderId, List <MaterialOrder> changedList)
{
OpenConnection();
List <MaterialOrder> list = new List <MaterialOrder>();
Order order = new Order();
Dictionary <string, string> concatDictionary = Util.ConcatMaterialList(SplitOn, changedList);
string WarehouseIds = concatDictionary[Defs.COL_WATERHOUSE_IDS];
string MaterialAmounts = concatDictionary[Defs.COL_TOTAL];
try
{
using (IDbConnection dbConnection = connection)
{
IDbCommand dbCommand = dbConnection.CreateCommand();
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = @"dbo.sproc_AssignOrder";
dbCommand.Parameters.Add(new SqlParameter("@WarehouseIds", WarehouseIds));
dbCommand.Parameters.Add(new SqlParameter("@MaterialAmounts", MaterialAmounts));
dbCommand.Parameters.Add(new SqlParameter("@OrderId", orderId.ToString()));
using (IDataReader reader = dbCommand.ExecuteReader())
{
while (reader.Read())
{
order.ExcelPath = reader["ExcelPath"].ToString();
order.Id = ulong.Parse(reader["Id"].ToString());
MaterialOrder material = new MaterialOrder
{
OriginalCode = reader["OriginalCode"].ToString(),
Code = reader["Code"].ToString(),
Description = reader["Description"].ToString(),
ChosenAmount = double.Parse(reader["ChosenAmount"].ToString()),
Unit = reader["Unit"].ToString()
};
list.Add(material);
}
}
}
order.MaterialList = list;
connection.Close();
return(order);
}
catch (Exception exc)
{
MessageBox.Show($"Error SQL: {exc.Message}");
throw new Exception("Get (EnabledProducts) error: " + exc.Message, exc);
}
}
public static Order GetOrderById(ulong orderId)
{
OpenConnection();
List <MaterialOrder> list = new List <MaterialOrder>();
Order order = new Order();
try
{
using (IDbConnection dbConnection = connection)
{
IDbCommand dbCommand = dbConnection.CreateCommand();
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = @"dbo.sproc_GetOrderById";
dbCommand.Parameters.Add(new SqlParameter("@OrderId", orderId.ToString()));
using (IDataReader reader = dbCommand.ExecuteReader())
{
while (reader.Read())
{
order.ExcelPath = reader["ExcelPath"].ToString();
order.Id = ulong.Parse(reader["Id"].ToString());
MaterialOrder material = new MaterialOrder
{
OriginalCode = reader["OriginalCode"].ToString(),
Code = reader["Code"].ToString(),
Description = reader["Description"].ToString(),
ChosenAmount = double.Parse(reader["ChosenAmount"].ToString()),
Unit = reader["Unit"].ToString(),
OriginName = reader["OriginName"].ToString(),
Ktl = reader["KLT"].ToString(),
Location = reader["Location"].ToString(),
ProviderName = reader["ProviderName"].ToString()
};
list.Add(material);
}
}
}
order.MaterialList = list;
connection.Close();
return(order);
}
catch (Exception exc)
{
MessageBox.Show($"Error SQL: {exc.Message}");
throw new Exception("Get (EnabledProducts) error: " + exc.Message, exc);
}
}
public static Order GetPreOrderById(Order order)
{
OpenConnection();
try
{
using (IDbConnection dbConnection = connection)
{
IDbCommand dbCommand = dbConnection.CreateCommand();
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = @"dbo.sproc_GetPreOrderById";
dbCommand.Parameters.Add(new SqlParameter("@OrderId", Util.ConvertDynamicToString(order.Id)));
using (IDataReader reader = dbCommand.ExecuteReader())
{
List <MaterialOrder> tempList = new List <MaterialOrder>();
while (reader.Read())
{
order.ExcelPath = reader["ExcelPath"].ToString();
MaterialOrder material = new MaterialOrder()
{
OriginalCode = reader["OriginalCode"].ToString(),
AvailableAmount = double.Parse(reader["AvailableAmount"].ToString()),
WantedAmount = double.Parse(reader["WantedAmount"].ToString()),
Description = reader["Description"].ToString(),
Location = reader["Location"].ToString(),
Ktl = reader["KLT"].ToString(),
BOMUnit = reader["BOMUnit"].ToString(),
WarehouseUnit = reader["WarehouseUnit"].ToString(),
OriginName = reader["OriginName"].ToString(),
StockTotal = Util.ConvertDynamicToDouble(reader["StockTotal"]),
ProviderName = Util.ConvertDynamicToString(reader["ProviderName"])
};
tempList.Add(material);
}
order.MaterialList = tempList;
}
}
connection.Close();
return(order);
}
catch (Exception exc)
{
MessageBox.Show($"Error SQL: {exc.Message}");
throw new Exception("Get (EnabledProducts) error: " + exc.Message, exc);
}
}
public static List <MaterialOrder> GetMaterialByOrderId(ulong orderId)
{
OpenConnection();
List <MaterialOrder> list = new List <MaterialOrder>();
try
{
using (IDbConnection dbConnection = connection)
{
IDbCommand dbCommand = dbConnection.CreateCommand();
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = @"dbo.sproc_GetMaterialByOrderId";
dbCommand.Parameters.Add(new SqlParameter("@OrderId", orderId.ToString()));
using (IDataReader reader = dbCommand.ExecuteReader())
{
while (reader.Read())
{
MaterialOrder material = new MaterialOrder
{
Code = reader["Code"].ToString(),
OriginalCode = reader["OriginalCode"].ToString(),
AvailableAmount = double.Parse(reader["AvailableAmount"].ToString()),
StockTotal = double.Parse(reader["StockTotal"].ToString()),
WantedAmount = double.Parse(reader["WantedAmount"].ToString()),
WarehouseId = new Guid(reader["Warehouse_FK"].ToString()),
Location = reader["Location"].ToString(),
Ktl = reader["KLT"].ToString(),
OriginName = reader["OriginName"].ToString(),
BOMUnit = reader["BOMUnit"].ToString(),
WarehouseUnit = reader["WarehouseUnit"].ToString()
};
list.Add(material);
}
}
}
connection.Close();
return(list);
}
catch (Exception exc)
{
MessageBox.Show($"Error SQL: {exc.Message}");
throw new Exception("Get (EnabledProducts) error: " + exc.Message, exc);
}
}
public MyTextBox(MaterialOrder material)
{
this.material = material;
}
public MyCheckBox(MaterialOrder material)
{
this.TextAlign = ContentAlignment.MiddleRight;
this.material = material;
this.Checked = true;
}
public static void UpdateBOMExcelFile(string filePath, List <MaterialOrder> materialList)
{
string copyFilePath = filePath.ToLower().Replace(".xlsx", Defs.EXCEL_FILE_POSTFIX);
bool retry = true;
while (retry)
{
try
{
Util.DeleteFileIfExist(copyFilePath);
File.Copy(filePath, copyFilePath, true);
retry = false;
}
catch (Exception exc)
{
if (!(MessageBox.Show($"Verifique que el archivo no este siendo usado por otro usuario ({copyFilePath})", "Archivo siento utilizado",
MessageBoxButtons.YesNo, MessageBoxIcon.Question,
MessageBoxDefaultButton.Button1) == System.Windows.Forms.DialogResult.Yes))
{
retry = false;
}
}
}
CurrentExcelOpenPath = copyFilePath;
Excel.Application app = new Excel.Application();
app.WorkbookBeforeClose += CloseUpdatedExcel;
Excel.Workbook workbook = app.Workbooks.Open(copyFilePath, UpdateLinks: 3);
List <string> errorList = new List <string>();
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();
if (sheetName == Defs.EXCLUDED_SHEET_COSTOS || sheetName == Defs.EXCLUDED_SHEET_LEYENDA)
{
continue;
}
Excel.Range rows = range.Rows;
int rowToleranceIndex = 0;
for (int rowIndex = 1; rowIndex <= rowCount; rowIndex++)
{
if (headercolumns.Count < MIN_COLUMNS_BOM_AMOUNT) //Get Columns names
{
colCount = colCount > NORMAL_COLUMN_AMOUNT ? NORMAL_COLUMN_AMOUNT : colCount;
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 colMaterialCode = headercolumns.Find(col => Util.IsLike(col.Name, Defs.COL_MATERIAL_CODE));
Column colAmount = headercolumns.Find(col => Util.IsLike(col.Name, Defs.COL_AMOUNT));
if (colMaterialCode == null)
{
MessageBox.Show($"Formato no reconocido en {sheetName} en la columna de: Clave del material");
break;
}
var dynamicCode = range.Cells[rowIndex, colMaterialCode.Index].Value;
var dynamicAmount = range.Cells[rowIndex, colAmount.Index].Value;
string materialCode = Util.ConvertDynamicToString(dynamicCode);
materialCode = Util.NormalizeString(materialCode);
if (Util.IsEmptyString(materialCode))
{
rowToleranceIndex++;
if (EMPTINESS_ROW_TOLERANCE < rowToleranceIndex)
{
break;
}
}
MaterialOrder material = materialList.Find(item => item.Code == materialCode);
if (material == null)
{
continue;
}
double desiredAmount = Util.ConvertDynamicToDouble(dynamicAmount);;
//Add Valid material to list
if (!Util.IsEmptyString(materialCode) && desiredAmount != 0)
{
Column colStatus = headercolumns.Find(col => Util.IsLike(col.Name, Defs.COL_STATUS));
Column colUnit = headercolumns.Find(col => Util.IsLike(col.Name, Defs.COL_UNIT));
DeleteOverRowFromExcel(rows);
double leftAmount = Math.Abs(desiredAmount - material.ChosenAmount);
int currentRowIndex = rowIndex;
int previousRowIndex = currentRowIndex - 1;
int nextRowIndex = currentRowIndex + 1;
//Add row upon
try
{
rows[currentRowIndex].Insert(Excel.XlInsertShiftDirection.xlShiftDown, rows[currentRowIndex]);
}
catch (Exception e)
{
errorList.Add($"En la hoja: {sheetName} hubo un problema, posiblemente quedó incompleta, por favor revisarla");
workbook.Save();
continue;
}
Excel.Range currentLine = (Excel.Range)rows[nextRowIndex];
Excel.Range newLine = (Excel.Range)rows[currentRowIndex];
currentLine.Copy(newLine);
range.Cells[currentRowIndex, colStatus.Index].Value = Defs.STATE_ORIGINAL;
currentRowIndex++;
previousRowIndex = currentRowIndex - 1;
nextRowIndex = currentRowIndex + 1;
if (desiredAmount > material.ChosenAmount)
{
range.Cells[currentRowIndex, colAmount.Index].Value = material.ChosenAmount;
Excel.Range currentLineTemp = (Excel.Range)rows[currentRowIndex];
//Add row below
try
{
rows[nextRowIndex].Insert(Excel.XlInsertShiftDirection.xlShiftDown, rows[currentRowIndex]);
}
catch (Exception e)
{
errorList.Add($"En la hoja: {sheetName} hubo un problema, posiblemente quedó incompleta, por favor revisarla");
workbook.Save();
continue;
}
Excel.Range newLineTemp = (Excel.Range)rows[nextRowIndex];
currentLineTemp.Copy(newLineTemp);
range.Cells[nextRowIndex, colAmount.Index].Value = leftAmount;
range.Cells[nextRowIndex, colStatus.Index].Value = Defs.STATE_PENDING;
rowIndex++;
}
else
{
range.Cells[currentRowIndex, colAmount.Index].Value = material.ChosenAmount;
}
rows[currentRowIndex].Interior.Color = Color.Green;
range.Cells[currentRowIndex, colStatus.Index].Value = Defs.STATE_RESERVED;
range.Cells[currentRowIndex, colUnit.Index].Interior.Color = Color.Red;
rows[previousRowIndex].Interior.Color = Color.White;
range.Cells[currentRowIndex, colUnit.Index].Value = material.Unit;
rowIndex++;
materialList.Remove(material);
}
}
}
Marshal.ReleaseComObject(range);
Marshal.ReleaseComObject(sheet);
}
if (errorList.Count != 0)
{
Util.ShowMessage(AlarmType.WARNING, errorList);
}
//cleanup
GC.Collect();
GC.WaitForPendingFinalizers();
//close and release
workbook.Save();
app.Visible = true;
string excelName = workbook.Name;
bool condition = !("Sheet1" == excelName);
if (condition)
{
EmailConfirmation windows = new EmailConfirmation(CurrentExcelOpenPath);
windows.Show();
}
Marshal.ReleaseComObject(workbook);
//quit and release
//app.Quit();
Marshal.ReleaseComObject(app);
}
public static Order InsertBOMMaterialList(List <MaterialBOM> materialList, string excelPath)
{
OpenConnection();
Dictionary <string, string> concatDictionary = Util.ConcatBOMMaterialList(SplitOn, materialList);
string MaterialCodes = concatDictionary[Defs.COL_MATERIAL_CODE];
string MaterialAmounts = concatDictionary[Defs.COL_AMOUNT];
string MaterialUnits = concatDictionary[Defs.COL_UNIT];
string MaterialExcelRow = concatDictionary[Defs.EXCEL_ROW];
string MaterialExcelSheet = concatDictionary[Defs.SHEET_NAME];
Order order = new Order();
try
{
using (IDbConnection dbConnection = connection)
{
IDbCommand dbCommand = dbConnection.CreateCommand();
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = @"dbo.sproc_InsertPreOrder";
dbCommand.Parameters.Add(new SqlParameter("@MaterialCodes", MaterialCodes));
dbCommand.Parameters.Add(new SqlParameter("@MaterialAmounts", MaterialAmounts));
dbCommand.Parameters.Add(new SqlParameter("@MaterialUnits", MaterialUnits));
dbCommand.Parameters.Add(new SqlParameter("@ExcelSheets", MaterialExcelSheet));
dbCommand.Parameters.Add(new SqlParameter("@ExcelRows", MaterialExcelRow));
dbCommand.Parameters.Add(new SqlParameter("@ExcelPath", excelPath));
using (IDataReader reader = dbCommand.ExecuteReader())
{
List <MaterialOrder> tempList = new List <MaterialOrder>();
while (reader.Read())
{
ulong OrderId = ulong.Parse(reader["Id"].ToString());
if (OrderId != 0)
{
order.Id = OrderId;
MaterialOrder material = new MaterialOrder()
{
OriginalCode = reader["OriginalCode"].ToString(),
AvailableAmount = double.Parse(reader["AvailableAmount"].ToString()),
WantedAmount = double.Parse(reader["WantedAmount"].ToString()),
Description = reader["Description"].ToString(),
Location = reader["Location"].ToString(),
Ktl = reader["KLT"].ToString(),
BOMUnit = reader["BOMUnit"].ToString(),
WarehouseUnit = reader["WarehouseUnit"].ToString(),
OriginName = reader["OriginName"].ToString(),
StockTotal = Util.ConvertDynamicToDouble(reader["StockTotal"]),
ProviderName = Util.ConvertDynamicToString(reader["ProviderName"])
};
tempList.Add(material);
}
}
order.MaterialList = tempList;
order.ExcelPath = excelPath;
}
}
connection.Close();
return(order);
}
catch (Exception exc)
{
MessageBox.Show($"Error SQL: {exc.Message}");
throw new Exception("Get (EnabledProducts) error: " + exc.Message, exc);
}
}
