コード例 #1
0
        public static void LoadExcelReportsFromZipFile(string zipFile)
        {
            string executionFolder = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
            string salesReportsFolder = Path.Combine(executionFolder, "SalesReports");

            ZipFileManager.Extract(zipFile, salesReportsFolder);

            DirectoryInfo salesReportsDirectory = new DirectoryInfo(salesReportsFolder);

            DirectoryInfo[] reportDirectories = salesReportsDirectory.GetDirectories();

            using (var msSQLServerContext = new SupermarketEntities())
            {
                foreach (DirectoryInfo reportDirectory in reportDirectories)
                {
                    DateTime reportDate = DateTime.ParseExact(
                        reportDirectory.Name,
                        "dd-MMM-yyyy",
                        CultureInfo.InvariantCulture);

                    FileInfo[] reportFiles = reportDirectory.GetFiles("*.xls");

                    foreach (FileInfo reportFile in reportFiles)
                    {
                        System.Data.DataTable contentsTable = ExcelManager.GetContents(reportFile.FullName);

                        InsertExcelFileDataInMSSQLServerDB(reportDate, contentsTable, msSQLServerContext);
                    }
                }
            }
        }
コード例 #2
0
        public static void CreateSalesReport()
        {
            SupermarketEntities supermarkets = new SupermarketEntities();

            var queryResult =
                from vendor in supermarkets.Vendors
                select new
                {
                    Vendor = vendor.VendorName,

                    dayToDayReprots =
                        from sale in supermarkets.Sales
                        join product in supermarkets.Products
                        on sale.ProductId equals product.ProductId
                        join report in supermarkets.Reports
                        on sale.ReportId equals report.ReportId
                        where product.VendorId == vendor.VendorId
                        group sale by report.ReportDate
                            into reportForDate
                            select new
                            {
                                Date = reportForDate.Key,
                                TotalSum = reportForDate.Sum(p => p.ProductTotalSum)
                            }
                };

            string fileName = "../../report.xml";
            Encoding encoding = Encoding.GetEncoding("windows-1251");
            using (XmlTextWriter writer = new XmlTextWriter(fileName, encoding))
            {
                writer.Formatting = Formatting.Indented;
                writer.IndentChar = '\t';
                writer.Indentation = 1;

                writer.WriteStartDocument();
                writer.WriteStartElement("sales");

                foreach (var vendor in queryResult)
                {
                    Console.WriteLine("Writing report for " + vendor.Vendor);

                    writer.WriteStartElement("sale");
                    writer.WriteAttributeString("vendor", vendor.Vendor);

                    foreach (var dayReport in vendor.dayToDayReprots)
                    {
                        writer.WriteStartElement("summary");
                        string dateString = string.Format("{0:dd-MMM-yyyy}", dayReport.Date);
                        writer.WriteAttributeString("date", dateString);
                        writer.WriteAttributeString("total-sum", dayReport.TotalSum.ToString("N2"));
                        writer.WriteEndElement();
                    }

                    writer.WriteEndElement();
                     
                }

                writer.WriteEndDocument();
            }
        }
コード例 #3
0
    private static void GeneratePDFAggregatedSalesReport()
    {
        using (var msSQLServerContext = new SupermarketEntities())
        {
            var sales =
                from product in msSQLServerContext.Products
                join unit in msSQLServerContext.Units
                on product.UnitId equals unit.UnitId
                join sale in msSQLServerContext.Sales
                on product.ProductId equals sale.ProductId
                join report in msSQLServerContext.Reports
                on sale.ReportId equals report.ReportId
                join localShop in msSQLServerContext.LocalShops
                on report.LocalShopId equals localShop.LocalShopId
                select new
                {
                    ProductName = product.ProductName,
                    ProductQuantity = sale.ProductQuantity,
                    Units = unit.UnitName,
                    ProductUnitPrice = sale.ProductUnitPrice,
                    LocalShopName = localShop.LocalShopName,
                    SaleDate = report.ReportDate,
                    TotalSum = sale.ProductTotalSum
                };

            var reports =
                from sale in sales
                group sale by sale.SaleDate into salesByDate
                select new
                {
                    SalesTotalSum = salesByDate.Sum(s => s.TotalSum),
                    Sales = salesByDate
                };

            foreach (var report in reports)
            {
                foreach (var sale in report.Sales)
                {
                    Console.WriteLine(
                        "{0} {1:N0} {2} {3} {4} {5} {6}",
                        sale.ProductName,
                        sale.ProductQuantity,
                        sale.Units,
                        sale.ProductUnitPrice,
                        sale.LocalShopName,
                        sale.SaleDate,
                        sale.TotalSum);
                }

                Console.WriteLine("--------------------------------" + report.SalesTotalSum);
            }
        }
    }
コード例 #4
0
    /// <summary>
    /// Should be executed only once - when we need  to populate the
    /// MS SQL Server database.
    /// </summary>
    private static void CopyMySQLDataToMSSQLServer()
    {
        using (SupermarketModel mySQLContext = new SupermarketModel())
        {
            using (SupermarketEntities msSQLServerContext = new SupermarketEntities())
            {
                foreach (var mySQLVendor in mySQLContext.Vendors)
                {
                    var msSQLServerVendor = new EntityFrameworkModels.Vendor
                    {
                        VendorName = mySQLVendor.VendorName
                    };

                    msSQLServerContext.Vendors.Add(msSQLServerVendor);
                    msSQLServerContext.SaveChanges();
                }

                foreach (var mySQLUnit in mySQLContext.Units)
                {
                    var msSQLServerUnit = new EntityFrameworkModels.Unit
                    {
                        UnitName = mySQLUnit.UnitName
                    };

                    msSQLServerContext.Units.Add(msSQLServerUnit);
                    msSQLServerContext.SaveChanges();
                }

                foreach (var mySQLProduct in mySQLContext.Products)
                {
                    var msSQLServerProduct = new EntityFrameworkModels.Product
                    {
                        VendorId = mySQLProduct.VendorID,
                        UnitId = mySQLProduct.UnitID,
                        ProductName = mySQLProduct.ProductName,
                        BasePrice = mySQLProduct.BasePrice
                    };

                    msSQLServerContext.Products.Add(msSQLServerProduct);
                    msSQLServerContext.SaveChanges();
                }
            }
        }
    }
コード例 #5
0
        public static void GeneratePDFAggregatedSalesReport()
        {
            string executionFolder = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
            string salesReportFile = Path.Combine(executionFolder, "Sales-Report.pdf");

            var fs = new FileStream(salesReportFile, FileMode.Create);

            var document = new Document(PageSize.A4, 25, 25, 30, 30);

            PdfWriter writer = PdfWriter.GetInstance(document, fs);
            document.Open();

            var table = new PdfPTable(5);

            table.TotalWidth = 545f;
            table.LockedWidth = true;

            float[] widths = new float[] { 0.75f, 0.5f, 0.25f, 1.25f, 0.25f };
            table.SetWidths(widths);

            table.SpacingBefore = 20f;
            table.SpacingAfter = 30f;

            var cellTitle = new PdfPCell(new Phrase("Aggregated Sales Report", new Font(Font.HELVETICA, FontSize, Font.BOLD)))
            {
                Colspan = 5,
                HorizontalAlignment = 1
            };

            cellTitle.PaddingBottom = 10f;
            cellTitle.PaddingLeft = 10f;
            cellTitle.PaddingTop = 4f;
            table.AddCell(cellTitle);

            using (var msSQLServerContext = new SupermarketEntities())
            {
                var sales =
                    from product in msSQLServerContext.Products
                    join unit in msSQLServerContext.Units
                    on product.UnitId equals unit.UnitId
                    join sale in msSQLServerContext.Sales
                    on product.ProductId equals sale.ProductId
                    join report in msSQLServerContext.Reports
                    on sale.ReportId equals report.ReportId
                    join localShop in msSQLServerContext.LocalShops
                    on report.LocalShopId equals localShop.LocalShopId
                    select new
                    {
                        ProductName = product.ProductName,
                        ProductQuantity = sale.ProductQuantity,
                        Units = unit.UnitName,
                        ProductUnitPrice = sale.ProductUnitPrice,
                        LocalShopName = localShop.LocalShopName,
                        SaleDate = report.ReportDate,
                        TotalSum = sale.ProductTotalSum
                    };

                var reports =
                    from sale in sales
                    group sale by sale.SaleDate into salesByDate
                    select new
                    {
                        SalesTotalSum = salesByDate.Sum(s => s.TotalSum),
                        Sales = salesByDate
                    };

                foreach (var report in reports)
                {
                    DateTime reportDate = DateTime.Now;
                    bool first = true;

                    foreach (var sale in report.Sales)
                    {
                        if (first)
                        {
                            reportDate = sale.SaleDate;
                            WriteTableHeader(table, reportDate);
                            first = false;
                        }

                        var cellProduct = new PdfPCell(new Phrase(sale.ProductName, new Font(Font.HELVETICA, FontSize, Font.NORMAL)));
                        cellProduct.PaddingBottom = 10f;
                        cellProduct.PaddingLeft = 10f;
                        cellProduct.PaddingTop = 4f;
                        table.AddCell(cellProduct);

                        var cellQuantity = new PdfPCell(new Phrase(sale.ProductQuantity.ToString() + " " + sale.Units, new Font(Font.HELVETICA, FontSize, Font.NORMAL)));
                        cellQuantity.PaddingBottom = 10f;
                        cellQuantity.PaddingLeft = 10f;
                        cellQuantity.PaddingTop = 4f;
                        table.AddCell(cellQuantity);

                        var cellUnitPrice = new PdfPCell(new Phrase(sale.ProductUnitPrice.ToString("N2"), new Font(Font.HELVETICA, FontSize, Font.NORMAL)));
                        cellUnitPrice.PaddingBottom = 10f;
                        cellUnitPrice.PaddingLeft = 10f;
                        cellUnitPrice.PaddingTop = 4f;
                        table.AddCell(cellUnitPrice);

                        var cellLocation = new PdfPCell(new Phrase(sale.LocalShopName, new Font(Font.HELVETICA, FontSize, Font.NORMAL)));
                        cellLocation.PaddingBottom = 10f;
                        cellLocation.PaddingLeft = 10f;
                        cellLocation.PaddingTop = 4f;
                        table.AddCell(cellLocation);

                        var cellSum = new PdfPCell(new Phrase(sale.TotalSum.ToString("N2"), new Font(Font.HELVETICA, FontSize, Font.NORMAL)));
                        cellSum.PaddingBottom = 10f;
                        cellSum.PaddingLeft = 10f;
                        cellSum.PaddingTop = 4f;
                        table.AddCell(cellSum);

                    }

                    var cellReportTotalSum = new PdfPCell(new Phrase("Total sum for " + reportDate.ToString("dd-MMM-yyyy") + ":", new Font(Font.HELVETICA, FontSize, Font.NORMAL)))
                    {
                        Colspan = 4,
                        HorizontalAlignment = 2
                    };

                    cellReportTotalSum.PaddingBottom = 10f;
                    cellReportTotalSum.PaddingLeft = 10f;
                    cellReportTotalSum.PaddingTop = 4f;
                    table.AddCell(cellReportTotalSum);

                    var cellTotalSum = new PdfPCell(new Phrase(report.SalesTotalSum.ToString("N2"), new Font(Font.HELVETICA, FontSize, Font.BOLD)));
                    cellTotalSum.PaddingBottom = 10f;
                    cellTotalSum.PaddingLeft = 10f;
                    cellTotalSum.PaddingTop = 4f;
                    table.AddCell(cellTotalSum);
                }

                document.Add(table);

                // Close the document
                document.Close();

                // Close the writer instance
                writer.Close();

                // Always close open filehandles explicity
                fs.Close();
            }
        }
コード例 #6
0
        public static void GenerateProductReports(string folderName)
        {
            DirectoryInfo directory = Directory.CreateDirectory(folderName);

            List<MongoProductReport> mongoProductsReports = new List<MongoProductReport>();
            MongoDBManager<MongoProductReport> mongo = new MongoDBManager<MongoProductReport>();

            using (var msSQLServerContext = new SupermarketEntities())
            {
                var sales =
                    from product in msSQLServerContext.Products
                    join vendor in msSQLServerContext.Vendors
                    on product.VendorId equals vendor.VendorId
                    join sale in msSQLServerContext.Sales
                    on product.ProductId equals sale.ProductId
                    group sale by sale.ProductId into productsById
                    select new
                    {
                        ProductId = productsById.Key,
                        SalesTotalQuantity = productsById.Sum(s => s.ProductQuantity),
                        SalesTotalSum = productsById.Sum(s => s.ProductTotalSum)
                    };

                var productReports =
                    from sale in sales
                    join product in msSQLServerContext.Products
                    on sale.ProductId equals product.ProductId
                    join vendor in msSQLServerContext.Vendors
                    on product.VendorId equals vendor.VendorId
                    select new
                    {
                        ProductId = sale.ProductId,
                        ProductName = product.ProductName,
                        VendorName = vendor.VendorName,
                        SalesTotalQuantity = sale.SalesTotalQuantity,
                        SalesTotalSum = sale.SalesTotalSum
                    };

                foreach (var productReport in productReports)
                {
                    JObject jsonObject = new JObject(
                        new JProperty("product-id", productReport.ProductId),
                        new JProperty("product-name", productReport.ProductName),
                        new JProperty("vendor-name", productReport.VendorName),
                        new JProperty("total-quantity-sold", productReport.SalesTotalQuantity),
                        new JProperty("total-incomes", productReport.SalesTotalSum));

                    string filePath = Path.Combine(folderName, string.Format("{0}.json", productReport.ProductId));

                    using (FileStream file = File.Create(filePath))
                    {
                        using (StreamWriter writer = new StreamWriter(file))
                        {
                            writer.Write(jsonObject.ToString());
                        }
                    }

                    MongoProductReport mongoProductReport = new MongoProductReport
                    {
                        ProductId = productReport.ProductId,
                        ProductName = productReport.ProductName,
                        VendorName = productReport.VendorName,
                        TotalQuantitySold = productReport.SalesTotalQuantity,
                        TotalIncomes = productReport.SalesTotalSum
                    };

                    mongoProductsReports.Add(mongoProductReport);
                }
            }

            mongo.InsertInMongoDB(mongoProductsReports, "ProductsReports");
        }
コード例 #7
0
        /// <summary>
        /// Method for extracting expenses reports in XML format
        /// </summary>
        /// <param name="filePath">Filepath for the .xml file</param>
        public static void ReadVendorMonthlyExpenses(string filePath)
        {
            XmlReader reader = XmlReader.Create(filePath);

            List<MongoVendorExpense> mongoVendorExpensesList = new List<MongoVendorExpense>();

            using (var msSQLServerContext = new SupermarketEntities())
            {
                using (reader)
                {
                    int vendorId = 0;

                    while (reader.Read())
                    {
                        //Insert a new vendor
                        if (reader.NodeType == XmlNodeType.Element && reader.Name == "sale")
                        {
                            string vendorName = reader.GetAttribute("vendor");
                            var vendor = msSQLServerContext.Vendors.FirstOrDefault(v => v.VendorName == vendorName);

                            if (vendor == null)
                            {
                                // the vendor doesn't exist
                                vendor = new Vendor
                                {
                                    VendorName = vendorName
                                };

                                msSQLServerContext.Vendors.Add(vendor);
                                msSQLServerContext.SaveChanges();
                            }

                            vendorId = vendor.VendorId;
                        }

                        //Insert Expenses
                        if (reader.NodeType == XmlNodeType.Element && reader.Name == "expenses")
                        {
                            string month = reader.GetAttribute("month");
                            DateTime monthAsDate = DateTime.ParseExact(month, "MMM-yyyy", CultureInfo.InvariantCulture);

                            decimal amount = reader.ReadElementContentAsDecimal();

                            var vendorMonthlyExpense = new VendorExpense
                            {
                                VendorId = vendorId,
                                Month = monthAsDate.Month,
                                Year = monthAsDate.Year,
                                Amount = amount
                            };

                            var mongoVendorExpense = new MongoVendorExpense
                            {
                                VendorId = vendorId,
                                Month = monthAsDate.Month,
                                Year = monthAsDate.Year,
                                Amount = amount
                            };

                            mongoVendorExpensesList.Add(mongoVendorExpense);
                            msSQLServerContext.VendorExpenses.Add(vendorMonthlyExpense);
                        }
                    }
                }

                MongoDBManager<MongoVendorExpense> mongoDBInserter = new MongoDBManager<MongoVendorExpense>();
                mongoDBInserter.InsertInMongoDB(mongoVendorExpensesList, "VendorExpenses");

                msSQLServerContext.SaveChanges();
            }
        }
コード例 #8
0
        private static void InsertExcelFileDataInMSSQLServerDB(
            DateTime reportDate,
            System.Data.DataTable contentsTable,
            SupermarketEntities msSQLServerContext)
        {
            using (TransactionScope scope = new TransactionScope())
            {
                string localShopName = contentsTable.Rows[0][0].ToString().Trim();

                var localShop = msSQLServerContext.LocalShops.FirstOrDefault(ls => ls.LocalShopName == localShopName);

                if (localShop == null)
                {
                    localShop = new LocalShop
                    {
                        LocalShopName = localShopName
                    };

                    msSQLServerContext.LocalShops.Add(localShop);
                    msSQLServerContext.SaveChanges();
                }

                int rowsCount = contentsTable.Rows.Count;
                int colsCount = contentsTable.Columns.Count;

                var report = new Report
                {
                    LocalShopId = localShop.LocalShopId,
                    ReportDate = reportDate,
                    ReportTotalSum = decimal.Parse(contentsTable.Rows[rowsCount - 1][3].ToString())
                };

                msSQLServerContext.Reports.Add(report);
                msSQLServerContext.SaveChanges();

                for (int row = 2; row < rowsCount - 1; row++)
                {
                    int productId = int.Parse(contentsTable.Rows[row][0].ToString());
                    double productQuantity = double.Parse(contentsTable.Rows[row][1].ToString());
                    decimal productUnitPrice = decimal.Parse(contentsTable.Rows[row][2].ToString());
                    decimal productTotalSum = decimal.Parse(contentsTable.Rows[row][3].ToString());

                    var sale = new Sale
                    {
                        ReportId = report.ReportId,
                        ProductId = productId,
                        ProductQuantity = productQuantity,
                        ProductUnitPrice = productUnitPrice,
                        ProductTotalSum = productTotalSum
                    };

                    msSQLServerContext.Sales.Add(sale);
                }

                msSQLServerContext.SaveChanges();

                scope.Complete();
            }
        }
コード例 #9
0
        private static void FillVendorsTotalReportsExcel(string excelFilePath, string sheetName)
        {
            OleDbConnection connection = new OleDbConnection(
                string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes'",
                excelFilePath));

            connection.Open();

            using (connection)
            {
                using (var msSQLServerContext = new SupermarketEntities())
                {

                    var vendorFinalReports = VendorFinalReport.FinalReportUtilities.QueryIt();
                    
                    //var vendors =
                    //    from vendor in msSQLServerContext.Vendors
                    //    select vendor;

                    foreach (var vendor in vendorFinalReports)
                    {
                        OleDbCommand insertCommand = new OleDbCommand(
                            string.Format(
                            "INSERT INTO [NewSheet] ([Vendor], [Incomes], [Expenses], [Taxes], [Financial Result]) " +
                            "VALUES (@vendor, @incomes, @expenses, @taxes, @financialResult)"),
                            connection);

                        insertCommand.Parameters.AddWithValue("@vendor", vendor.VendorName);
                        insertCommand.Parameters.AddWithValue("@incomes", vendor.TotalIncome);
                        insertCommand.Parameters.AddWithValue("@expenses", vendor.Expenses);
                        insertCommand.Parameters.AddWithValue("@taxes", vendor.TotanTaxes);
                        insertCommand.Parameters.AddWithValue("@financialResult", vendor.Result);

                        insertCommand.ExecuteNonQuery();
                    }
                }
            }
        }