Esempio n. 1
0
        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);
            }
        }
Esempio n. 2
0
        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);
            }
        }
Esempio n. 3
0
 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);
     }
 }
Esempio n. 4
0
        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);
            }
        }
Esempio n. 5
0
 public MyTextBox(MaterialOrder material)
 {
     this.material = material;
 }
Esempio n. 6
0
 public MyCheckBox(MaterialOrder material)
 {
     this.TextAlign = ContentAlignment.MiddleRight;
     this.material  = material;
     this.Checked   = true;
 }
Esempio n. 7
0
        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);
        }
Esempio n. 8
0
        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);
            }
        }