public static void GetExcelData()
        {
            using (var db = new CatalogContext())
            {
                HashSet<string> addedShops = new HashSet<string>();

                //this is used for avoiding duplicates and fast search
                foreach (var item in db.Shops)
                {
                    addedShops.Add(item.Name);
                }

                foreach (var path in paths)
                {
                    foreach (var fileName in path.Value)
                    {
                        AddShop(fileName, addedShops, db);
                    }
                }

                db.SaveChanges();
              AddSale(db, paths);
                db.SaveChanges();
            }
        }
        public static void AddShop(string fileName, HashSet<string> addedShops, CatalogContext db)
        {
            var myShop = new Shop();
            string shopName = fileName.Split((new string[] { "-Sales" }), StringSplitOptions.None).First();
            myShop.Name = shopName;

            if (!addedShops.Contains(shopName))
            {
                db.Shops.Add(myShop);
            }
        }
Пример #3
0
        public static void CreateXMLFile(string path)
        {
            XmlWriterSettings settings = new XmlWriterSettings();
            XmlWriter document = XmlWriter.Create(path);

            document.WriteStartDocument();
            document.WriteStartElement("Sales");

            using (var db = new CatalogContext())
            {
                var vendorReport = db.Sales
                    .Include("Product")
                    .Include("Product.Vendor")
                    .GroupBy(x => new { x.Date, x.Product.Vendor.Name })
                    .OrderBy(x => x.Key.Name);

                string vendor = "";
                foreach (var item in vendorReport)
                {
                    if (vendor != item.Key.Name)
                    {
                        if (vendor != string.Empty)
                        {
                            document.WriteEndElement();
                        }
                        vendor = item.Key.Name;
                        document.WriteStartElement("sale");
                        document.WriteAttributeString("vendor", vendor);
                    }

                    document.WriteStartElement("summary");
                    document.WriteAttributeString("date", item.Key.Date.ToString("dd-MMM-yyyy"));
                    document.WriteAttributeString("total-sum", item.Sum(x => x.Sum).ToString());
                    document.WriteEndElement();
                }

                document.WriteEndDocument();
            }

            document.Close();
        }
Пример #4
0
        private static void CreateJsonObject(CatalogContext db)
        {
            using (db)
            {
                var productReport = db.Sales
                    .Include("Product")
                    .Include("Product.Vendor")
                    .Select(x => new
                    {
                        x.Sum,
                        x.Quantity,
                        x.ProductID,
                        x.Product.ProductName,
                        x.Product.Vendor.Name
                    }
                            )
                    .GroupBy(x => x.ProductID);

                int count = 0;
                foreach (var item in productReport)
                {
                    Console.WriteLine("ID: {0} Name: {1} Vendor: {2} Quantity: {3} Sum: {4}",
                        item.Key,
                        item.First().ProductName,
                        item.First().Name,
                        item.Sum(x => x.Quantity),
                        item.Sum(x => x.Sum)
                        );

                    ProductReport currentReport = new ProductReport(item.Key, item.First().ProductName, item.First().Name,
                                                                    Convert.ToDouble(item.Sum(x => x.Quantity)), item.Sum(x => x.Sum));

                    InsertIntoMongoProductReport(currentReport, count);
                    count++;
                }
            }
        }
Пример #5
0
 public IndexModel(Catalog.Data.CatalogContext context)
 {
     _context = context;
 }
Пример #6
0
 public EditModel(Catalog.Data.CatalogContext context)
 {
     _context = context;
 }
        public static void CreatePDFDocument()
        {
            var document = new Document();
            //string path = Server.MapPath("PDFs");
            PdfWriter.GetInstance(document, new FileStream("../../document.pdf", FileMode.Create));
            document.Open();
            PdfPTable table = new PdfPTable(5);


            //leave a gap before and after the table
            table.SpacingBefore = 20f;
            table.SpacingAfter = 30f;

            using (var db = new CatalogContext())
            {

                //one query to the database
                var sales = db.Sales
                    .Include("Product")
                    .Include("Shop")
                    .OrderBy(x => new { x.Date, x.ProductID, x.ShopID })
                    .ToList();
                DateTime reportDate = new DateTime();
                decimal totalSum = 0;
                foreach (var sale in sales)
                {
                    if (reportDate != sale.Date)
                    {
                        if (totalSum != 0)
                        {
                            table.AddCell("");
                            table.AddCell("");
                            table.AddCell("");
                            table.AddCell(string.Format("Total sum for: {0}", reportDate.ToString("dd-MMM-yyyy")));
                            //PdfPCell total = new PdfPCell(new Phrase(string.Format("Total: {0}", totalSum)));
                            //total.HorizontalAlignment = 1;
                            //total.Colspan = 4;

                            //table.AddCell(total);
                            table.AddCell(string.Format("Total: {0}", totalSum));
                            totalSum = 0;
                        }
                        table.AddCell(string.Format("Date: {0}", sale.Date.ToString("dd-MMM-yyyy")));
                        table.CompleteRow();
                        table.AddCell("Product");
                        table.AddCell("Quantity");
                        table.AddCell("UnitPrice");
                        table.AddCell("Location");
                        table.AddCell("Sum");
                        reportDate = sale.Date;
                    }
                    totalSum += sale.Sum;
                    table.AddCell(sale.Product.ProductName);
                    table.AddCell(sale.Quantity.ToString());
                    table.AddCell(sale.UnitPrice.ToString());
                    table.AddCell(sale.Shop.Name);
                    table.AddCell(sale.Sum.ToString());
                }

                document.Add(table);
                document.Close();
            }
        }
Пример #8
0
 public DeleteModel(Catalog.Data.CatalogContext context)
 {
     _context = context;
 }
        static void Main(string[] args)
        {
            Database.SetInitializer(new Configuration());

            using (var db = new CatalogContext())
            {
                var sale = db.Vendors.First();
                //var vendorReport = db.Sales
                //    .Include("Product")
                //    .Include("Product.Vendor")
                //    .GroupBy(x => new {  x.Date, x.Product.Vendor.Name })
                //    .OrderBy(x=> x.Key.Name);

                //foreach (var vendor in vendorReport)
                //{
                //    Console.WriteLine("Vendor: {0}", vendor.Key.Name);
                //    //Console.WriteLine("ProductId: {0}", vendor.Key.ProductID);
                //    Console.WriteLine("Sum: {0}", vendor.Sum(x=> x.Sum));
                //}

                //Console.WriteLine("test");
                //var productReport = db.Sales
                //    //.Include("Product")
                //    //.Include("Product.Vendor")
                //    .Select(x => new {
                //                        x.Sum,
                //                        x.Quantity,
                //                        x.ProductID,
                //                        x.Product.ProductName,
                //                        x.Product.Vendor.Name
                //                     }
                //            )
                //    .GroupBy(x => x.ProductID);

                //foreach (var item in productReport)
                //{
                //    Console.WriteLine("ID: {0} Name: {1} Vendor: {2} Quantity: {3} Sum: {4}",
                //        item.Key,
                //        item.First().ProductName,
                //        item.First().Name,
                //        item.Sum(x => x.Quantity),
                //        item.Sum(x => x.Sum)
                //        );
                //}

                //Console.WriteLine("test");

                //Console.WriteLine("{0,15} {1,15} {2,15} {3,15} {4,15}", "Product", "Quantity", "UnitPrice", "Location", "Sum");

                //var sales = db.Sales
                //    .Include("Product")
                //    .Include("Shop")
                //    .OrderBy(x => new { x.Date, x.ProductID, x.ShopID})
                //    .ToList();
                //DateTime reportDate = new DateTime();

                //foreach (var sale in sales)
                //{
                //    if (reportDate != sale.Date)
                //    {
                //        Console.WriteLine("Date: {0}", sale.Date);
                //        reportDate = sale.Date;
                //    }

                //    Console.WriteLine("{0,15} {1,15} {2,15} {3,15} {4,10:F2}",
                //       sale.Product.ProductName,
                //       sale.Quantity,
                //       sale.UnitPrice,
                //       sale.Shop.Name,
                //       sale.Sum
                //       );
                //}
            }
        }
Пример #10
0
 public CreateModel(Catalog.Data.CatalogContext context)
 {
     _context = context;
 }
        private static void AddSale(CatalogContext db, Dictionary<string, List<string>> paths)
        {
            foreach (var path in paths)
            {
                foreach (var fileName in path.Value)
                {
                    // used for gathering and combing the sells of the same products and same shop
                    Dictionary<int, Sale> currentSales = new Dictionary<int, Sale>();

                    OleDbConnection excelConnection =
                        new OleDbConnection(
                           "Provider=Microsoft.ACE.OLEDB.12.0;" +
                          @"Data Source=..\..\Extracted\" + path.Key + "\\" + fileName + ";" +
                          @"Extended Properties=""Excel 8.0;HDR=YES""");

                    string shopName = fileName.Split((new string[] { "-Sales" }), StringSplitOptions.None).First();

                    excelConnection.Open();
                    using (excelConnection)
                    {

                        using   (
                                OleDbCommand command =
                                new OleDbCommand(
                                String.Format("SELECT * FROM [{0}${1}]", "Sales", "B3:E"), excelConnection)
                                )
                        {

                            command.Connection = excelConnection;

                            OleDbDataReader dataExcel = command.ExecuteReader();
                            using (dataExcel)
                            {
                                while (dataExcel.Read())
                                {
                                    var productId = dataExcel["ProductID"];
                                    int productIdFull = 0;

                                    if (productId != DBNull.Value)
                                    {
                                        productIdFull = Convert.ToInt32(productId);
                                    }
                                    else
                                    {
                                        continue;
                                    }

                                    //Console.WriteLine("{0}", productIdFull);
                                    DateTime date = DateTime.Parse(path.Key);
                                    //Console.WriteLine("{0}", date);
                                    decimal sum = Convert.ToDecimal(dataExcel["Sum"]);
                                    //Console.WriteLine("{0}", sum);
                                    decimal quantity = Convert.ToDecimal(dataExcel["Quantity"]);
                                    //Console.WriteLine("{0}", quantity);
                                    decimal unitPrice = Convert.ToDecimal(dataExcel["Unit Price"]);
                                    //Console.WriteLine("{0}", unitPrice);

                                    int shopID = db.Shops.Where(x => x.Name.CompareTo(shopName) == 0).First().ShopID;

                                    if (currentSales.ContainsKey(productIdFull))
                                    {
                                        currentSales[productIdFull].Quantity += quantity;
                                        currentSales[productIdFull].Sum += sum;
                                    }
                                    else
                                    {
                                        Sale newSale = new Sale
                                        {
                                            Date = date,
                                            ProductID = productIdFull,
                                            Quantity = quantity,
                                            UnitPrice = unitPrice,
                                            Sum = sum,
                                            ShopID = shopID
                                        };

                                        currentSales[productIdFull] = newSale;
                                    }
                                }
                            }
                        }
                    }

                    foreach (var item in currentSales)
                    {
                        db.Sales.Add(item.Value);
                    }
                }
            }
        }
Пример #12
0
        static void Main(string[] args)
        {
            string path = @"../../file.xml";
            Dictionary<string, Dictionary<string, decimal>> data = new Dictionary<string, Dictionary<string, decimal>>();


            XmlDocument doc = new XmlDocument();
            doc.Load(@"../../file.xml");
            XmlNode rootNode = doc.DocumentElement;
            //Console.WriteLine("Root node: {0}", rootNode.Name);

            decimal expense = 0m;
            string txt = "";
            int count = 0;
            foreach (XmlNode node in rootNode.ChildNodes)
            {
                string vendor = node.Attributes["vendor"].Value.ToString();
                string month = "";
                //Console.WriteLine(vendor);
                foreach (XmlNode child in node)
                {
                    month = child.Attributes["month"].Value.ToString();
                    txt = child.InnerText.ToString();
                    expense = decimal.Parse(txt, CultureInfo.InvariantCulture);

                    if (!data.ContainsKey(vendor))
                    {
                        data[vendor] = new Dictionary<string, decimal>();
                    }
                    data[vendor].Add(month, expense);

                    VendorReport currentReport = new VendorReport(vendor, month, expense);
                    JsonClient.InsertIntoMongoVendorReport(currentReport, count);
                    count++;
                }
            }

            using (var db = new CatalogContext())
            {
                //one query to database
                var currentVendors = db.Vendors.ToList();
                var currentExpenses = db.VendorExpenses.ToList();

                foreach (var vendor in data)
                {

                    int vendorID = currentVendors
                        .Where(x => x.Name.CompareTo(vendor.Key) == 0)
                        .Select(x => x.VendorId).First();

                    foreach (var vendorExpense in vendor.Value)
                    {
                        VendorExpense newExpense = new VendorExpense
                            {
                                VendorID = vendorID,
                                Month = DateTime.Parse(vendorExpense.Key),
                                Expense = vendorExpense.Value
                            };
                        var currentExpense = currentExpenses.Where(x => x.VendorID == vendorID && x.Month == DateTime.Parse(vendorExpense.Key)).FirstOrDefault();
                        if (currentExpense != null)
                        {
                            currentExpense.Expense = vendorExpense.Value;
                        }
                        else
                        {
                            db.VendorExpenses.Add(newExpense);
                        }
                    }
                }
                db.SaveChanges();
            }
        }
Пример #13
0
 public DetailsModel(Catalog.Data.CatalogContext context)
 {
     _context = context;
 }
Пример #14
0
        static void Main(string[] args)
        {
            var db = new Catalog.Data.CatalogContext();

            CreateJsonObject(db);
        }