Example #1
0
        private void UpdateProductData(Excel.Application xlApp, DataRow[] drSellers, DateTime SummaryCreationDate)
        {
            try
            {
                ProductMaster ObjProductMaster = CommonFunctions.ObjProductMaster;
                lblStatus.Text = "Loading Product Inventory file";
                DataTable dtProductInventory  = CommonFunctions.ReturnDataTableFromExcelWorksheet("Inventory", ProductInventoryFilePath, "*");
                DataRow[] drProductsInventory = dtProductInventory.DefaultView.ToTable().Select("", "[StockName] asc");
                ObjProductMaster.LoadProductInventoryFile(drProductsInventory);

                lblStatus.Text = "Processing Product Inventory file";
                for (int i = 0; i < drSellers.Length; i++)
                {
                    lblStatus.Text = "Updating Product Inventory file for Seller " + (i + 1) + " of " + drSellers.Length;
                    String SheetName = drSellers[i]["Seller Name"].ToString().Trim().Replace(":", "").
                                       Replace("\\", "").Replace("/", "").
                                       Replace("?", "").Replace("*", "").
                                       Replace("[", "").Replace("]", "");
                    //DataTable dtSellerInvoice = CommonFunctions.ReturnDataTableFromExcelWorksheet(SheetName, SellerSummaryFilePath, "*", "A6:F100000");
                    ReportType EnumReportType  = (CommonFunctions.ObjGeneralSettings.SummaryLocation == 0 ? ReportType.INVOICE : ReportType.QUOTATION);
                    Invoice    ObjInvoice      = CommonFunctions.GetInvoiceTemplate(EnumReportType);
                    DataTable  dtSellerInvoice = ObjInvoice.LoadInvoice(SheetName, SellerSummaryFilePath);
                    if (dtSellerInvoice == null)
                    {
                        continue;
                    }
                    dtSellerInvoice.DefaultView.RowFilter = "IsNull([Sl No], 0) > 0";
                    DataRow[] drProducts = dtSellerInvoice.DefaultView.ToTable().Select("", "[Sl No] asc");

                    ObjProductMaster.UpdateProductInventoryDataFromInvoice(drProducts);
                }
                ObjProductMaster.ComputeStockNetData("Sale");

                lblStatus.Text = "Updating Product Inventory file";
                ObjProductMaster.UpdateProductInventoryFile(xlApp, SummaryCreationDate, ProductInventoryFilePath);
                lblStatus.Text = "Completed updating Product Inventory file";

                lblStatus.Text = "Updating Product Stock History file";
                ObjProductMaster.UpdateProductStockHistoryFile(xlApp, SummaryCreationDate, "Sale", ProductStockHistoryFilePath);
                lblStatus.Text = "Completed updating Product Stock History file";

                CommonFunctions.ObjProductMaster.ResetStockProducts();
            }
            catch (Exception ex)
            {
                CommonFunctions.ShowErrorDialog("UpdateOrderMasterForm.UpdateProductData()", ex);
                throw;
            }
        }
Example #2
0
        private void UpdateDetailsFromVendorPOFile()
        {
            xlApp = new Excel.Application();
            try
            {
                if (txtBoxVendorPOFile.Text.Trim().Length == 0)
                {
                    MessageBox.Show(this, "Vendor Purchase Orders file cannot be blank!!!\nPlease choose Vendor PO File.", "Error!!!", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }

                #region Check Vendor History file
                if (chkBoxUpdVendorHistory.Checked)
                {
                    if (!CommonFunctions.ValidateFile_Overwrite_TakeBackup(this, Path.GetDirectoryName(VendorPOFile), ref VendorHistoryFile, "VendorHistory.xlsx", "Vendor History"))
                    {
                        return;
                    }
                    txtBoxVendorHistoryFile.Text = VendorHistoryFile;
                }
                #endregion

                #region Check Product Inventory file
                if (chkBoxUpdProductInventory.Checked)
                {
                    if (ProductInventoryFile.Length == 0)
                    {
                        ProductInventoryFile            = Path.GetDirectoryName(VendorPOFile) + @"\ProductInventory.xlsx";
                        txtBoxProductInventoryFile.Text = ProductInventoryFile;
                    }

                    if (!File.Exists(ProductInventoryFile))
                    {
                        MessageBox.Show(this, "Cannot find \"" + ProductInventoryFile + "\" file.\nPlease Provide Valid Product Inventory file.",
                                        "Product Inventory", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return;
                    }
                }
                #endregion

                #region Check Product Stock History file
                if (chkBoxUpdStockHistory.Checked)
                {
                    if (!CommonFunctions.ValidateFile_Overwrite_TakeBackup(this, Path.GetDirectoryName(VendorPOFile), ref ProductStockHistoryFile, "ProductStockHistory.xlsx", "Stock History"))
                    {
                        return;
                    }
                    txtBoxProductStockHistoryFile.Text = ProductStockHistoryFile;
                }
                #endregion

                ReportProgressFunc(0);
                lblStatus.Text = "Reading Vendor Summary...";
                DataTable dtVendorSummary = CommonFunctions.ReturnDataTableFromExcelWorksheet("Vendor Summary", VendorPOFile, "*", "A2:K100000");
                if (dtVendorSummary == null)
                {
                    MessageBox.Show(this, "Provided Vendor PO file doesn't contain \"Vendor Summary\" Sheet.\nPlease provide correct file.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    lblStatus.Text = "Please provide file with \"Vendor Summary\" sheet";
                    return;
                }

                dtVendorSummary.DefaultView.RowFilter = "IsNull([Sl#], 0) > 0";
                DataRow[] drVendors = dtVendorSummary.DefaultView.ToTable().Select("", "[Sl#] asc");

                Excel.Workbook  xlVendorSummaryWorkbook  = xlApp.Workbooks.Open(VendorPOFile);
                Excel.Worksheet xlVendorSummaryWorksheet = CommonFunctions.GetWorksheet(xlVendorSummaryWorkbook, "Vendor Summary");
                DateTime        SummaryCreationDate      = DateTime.Parse(xlVendorSummaryWorksheet.Cells[1, 2].Value.ToString());
                xlVendorSummaryWorkbook.Close(false);

                String Message = "";
                if (chkBoxUpdVendorHistory.Checked)
                {
                    lblStatus.Text = "Updating Vendor History file";
                    UpdateVendorHistoryFile(xlApp, drVendors, SummaryCreationDate);
                    lblStatus.Text = "Completed updating Vendor History file";
                    Message       += "\nVendor History";
                }

                if (chkBoxUpdStockHistory.Checked || chkBoxUpdProductInventory.Checked)
                {
                    ProductMaster ObjProductMaster = CommonFunctions.ObjProductMaster;
                    lblStatus.Text = "Loading Product Inventory file";
                    DataTable dtProductInventory  = CommonFunctions.ReturnDataTableFromExcelWorksheet("Inventory", ProductInventoryFile, "*");
                    DataRow[] drProductsInventory = dtProductInventory.DefaultView.ToTable().Select("", "[StockName] asc");
                    ObjProductMaster.LoadProductInventoryFile(drProductsInventory);

                    lblStatus.Text = "Processing Product Inventory file";
                    for (int i = 0; i < drVendors.Length; i++)
                    {
                        lblStatus.Text = "Updating Product Inventory file for Vendor " + (i + 1) + " of " + drVendors.Length;
                        String SheetName = drVendors[i]["Vendor Name"].ToString().Replace(":", "").
                                           Replace("\\", "").Replace("/", "").
                                           Replace("?", "").Replace("*", "").
                                           Replace("[", "").Replace("]", "");
                        DataTable dtVendorPO = CommonFunctions.ReturnDataTableFromExcelWorksheet(SheetName, VendorPOFile, "*", "A6:F100000");
                        dtVendorPO.DefaultView.RowFilter = "IsNull([Sl#No#], 0) > 0";
                        DataRow[] drProducts = dtVendorPO.DefaultView.ToTable().Select("", "[Sl#No#] asc");

                        if (SheetName.Equals("Stock", StringComparison.InvariantCultureIgnoreCase))
                        {
                            ObjProductMaster.UpdateProductInventoryDataFromPO(drProducts, true);
                        }
                        else
                        {
                            ObjProductMaster.UpdateProductInventoryDataFromPO(drProducts, false);
                        }
                    }
                    ObjProductMaster.ComputeStockNetData("Purchase");

                    if (chkBoxUpdProductInventory.Checked)
                    {
                        lblStatus.Text = "Updating Product Inventory file";
                        ObjProductMaster.UpdateProductInventoryFile(xlApp, SummaryCreationDate, ProductInventoryFile);
                        lblStatus.Text = "Completed updating Product Inventory file";
                        Message       += "\nProduct Inventory";
                    }

                    if (chkBoxUpdStockHistory.Checked)
                    {
                        lblStatus.Text = "Updating Product Stock History file";
                        ObjProductMaster.UpdateProductStockHistoryFile(xlApp, SummaryCreationDate, "Purchase", ProductStockHistoryFile);
                        lblStatus.Text = "Completed updating Product Stock History file";
                        Message       += "\nProduct Stock History";
                    }

                    CommonFunctions.ObjProductMaster.ResetStockProducts();
                }
                MessageBox.Show(this, "Updated following details successfully:" + Message, "Update Purchases", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                CommonFunctions.ShowErrorDialog("UpdateProductPurchasesForm.UpdateDetailsFromVendorPOFile()", ex);
            }
            finally
            {
                xlApp.Quit();
                CommonFunctions.ReleaseCOMObject(xlApp);
            }
        }