Exemplo n.º 1
0
        public static int InsertInSupermarketTable(string supermarketName)
        {
            SupermarketReportsEntities db = new SupermarketReportsEntities();
            using (db)
            {
                var supermarket = new SupermarketMSSql.Model.Supermarket()
                {
                    Name = supermarketName
                };

                bool doesExist = db.Supermarkets.Any(s => s.Name == supermarket.Name);

                //bool doesExist = db.Supermarkets.Select(s => s.Name == supermarket.Name).ToList().Count > 0;
                if (!doesExist)
                {
                    db.Supermarkets.Add(supermarket);
                    db.SaveChanges();
                }

                return db.Supermarkets
                    .Where(s => s.Name == supermarket.Name)
                    .First()
                    .SupermarketId;
            }
        }
Exemplo n.º 2
0
        public static void PrintToXML()
        {
            using (SupermarketReportsEntities msSqlDb = new SupermarketReportsEntities())
            {
                var doc = new XDocument();
                var sales = new XElement("sales");

                var salesReports = msSqlDb.SalesReports
                                          .Include("Product")
                                          .Include("Supermarket")
                                          .Include("Vendor")
                                          .GroupBy(x => x.Product.Vendor.VendorName)
                                          .ToList();

                foreach (var report in salesReports)
                {
                    var sale = new XElement("sale", new XAttribute("vendor", report.First().Product.Vendor.VendorName));

                    foreach (var item in report)
                    {
                        sale.Add(new XElement("summary",
                                            new XAttribute("date", item.ReportDate.ToString("dd-MMM-yyyy", CultureInfo.InvariantCulture)),
                                            new XAttribute("total-sum", item.Sum)));
                    }
                    sales.Add(sale);
                }

                doc.Add(sales);

                doc.Save("../../SalesReports/Sales-by-Vendors-report.xml");
            }
        }
Exemplo n.º 3
0
        public static void CreateSalesReportPdfFile(string location)
        {
            using (SupermarketReportsEntities msSqlDb = new SupermarketReportsEntities())
            {
                ICollection<SupermarketMSSql.Model.SalesReport> result = msSqlDb.SalesReports.Include("Product").Include("Supermarket").ToList();

                FillPdfReportFile(result, location);
            }
        }
Exemplo n.º 4
0
        public static void MigrateDataFromMySqlToMSSql()
        {
            using (SupermarketMySqlEntities mySqlDb = new SupermarketMySqlEntities())
            {
                using (SupermarketReportsEntities msSqlDb = new SupermarketReportsEntities())
                {
                    var vendors = mySqlDb.Vendors;
                    foreach (var vendor in vendors)
                    {
                        SupermarketMSSql.Model.Vendor newVendor = new SupermarketMSSql.Model.Vendor
                        {
                            VendorName = vendor.VendorName
                        };

                        msSqlDb.Vendors.Add(newVendor);
                    }

                    msSqlDb.SaveChanges();
                    var measures = mySqlDb.Measures;
                    foreach (var measure in measures)
                    {
                        SupermarketMSSql.Model.Measure newMeasure = new SupermarketMSSql.Model.Measure
                        {
                            MeasureName = measure.MeasureName
                        };

                        msSqlDb.Measures.Add(newMeasure);
                    }

                    msSqlDb.SaveChanges();

                    var products = mySqlDb.Products;
                    foreach (var product in products)
                    {
                        var vendor = msSqlDb.Vendors.Where(v => v.VendorName == product.Vendor.VendorName).First();
                        var measure = msSqlDb.Measures.Where(m => m.MeasureName == product.Measure.MeasureName).First();
                        SupermarketMSSql.Model.Product newProduct = new SupermarketMSSql.Model.Product
                        {
                            ProductName = product.ProductName,
                            BasePrice = product.BasePrice,
                            Measures_MeasureId = measure.MeasureId,
                            Vendors_VendorId = vendor.VendorId
                        };

                        msSqlDb.Products.Add(newProduct);
                    }

                    msSqlDb.SaveChanges();
                }
            }
        }
Exemplo n.º 5
0
        public static void InsertSalesReport(int productID, int quantity, decimal unitPrice,
            decimal sum, DateTime reportDate, int supermarketID)
        {
            SupermarketReportsEntities db = new SupermarketReportsEntities();
            using (db)
            {
                SalesReport report = new SalesReport()
                {
                    ProductId = productID,
                    Quantity = quantity,
                    ReportDate = reportDate,
                    SupermarketId = supermarketID,
                    UnitPrice = unitPrice,
                    Sum = sum
                };

                db.SalesReports.Add(report);
                db.SaveChanges();
            }
        }
Exemplo n.º 6
0
        public static int InsertExpenses(string vendorName, DateTime date, decimal expenses)
        {
            int vendorID;
            SupermarketReportsEntities db = new SupermarketReportsEntities();

            using (db)
            {
                vendorID = db.Vendors
                    .Where(v => v.VendorName == vendorName)
                    .First()
                    .VendorId;

                Expens newExpense = new Expens
                {
                    VendorId = vendorID,
                    Date = date,
                    Expenses = expenses
                };

                db.Expenses.Add(newExpense);
                db.SaveChanges();
            }

            return vendorID;
        }
Exemplo n.º 7
0
 public static int GetProductIdByName(string mysqlProductName)
 {
     SupermarketReportsEntities db = new SupermarketReportsEntities();
     using (db)
     {
         var product = db.Products.Where(p => p.ProductName == mysqlProductName).First();
         return product.Productid;
     }
 }
Exemplo n.º 8
0
        public static void WriteDataFromSQLite()
        {
            string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;" +
                                        @" Data Source=../../vendors.xls; Persist Security Info=false; Extended Properties=Excel 8.0;";


            var conn1 = new OleDbConnection(connectionString);
            conn1.Open();
            using (conn1)
            {
                string sql = "CREATE TABLE [Product Reports] (Vendor string, Income decimal, Expenses decimal, Taxes decimal, [Financial Result] decimal)";
                var cmd = new OleDbCommand(sql, conn1);
                cmd.ExecuteNonQuery();
            }

            using (ProductReportsEntities sqLiteContext = new ProductReportsEntities())
            {
                using (SupermarketReportsEntities db = new SupermarketReportsEntities())
                {
                    DateTime currentDate = DateTime.Now;
                    var reports = db.SalesReports
                        .Where(d => d.ReportDate.Year == currentDate.Year && d.ReportDate.Month == currentDate.Month)
                        .GroupBy(v => v.Product.Vendors_VendorId).ToList();

                    foreach (var item in reports)
                    {
                        string vendorName = item.First().Product.Vendor.VendorName;
                        decimal income = sqLiteContext.ProductReports.Where(v => v.VendorName == vendorName).Sum(s => s.TotalIncomes);

                        int vendorID = item.First().Product.Vendor.VendorId;
                        var expencesList = db.Expenses
                            .Where(v => v.VendorId == vendorID && v.Date.Year == currentDate.Year && v.Date.Month == currentDate.Month)
                            .GroupBy(vi => vi.VendorId).FirstOrDefault();

                        if (expencesList == null)
                        {
                            continue;
                        }

                        decimal expences = expencesList.Sum(e => e.Expenses);

                        var taxes = sqLiteContext.Taxes;

                        decimal totalTaxes = 0;

                        foreach (var tax in taxes)
                        {
                            //string taxName = tax.Product_Name.Replace("”", "\"").Replace("“", "\"").Replace("’", "'");

                            var vId = sqLiteContext.ProductReports.Where(p => p.VendorName.Replace("”", "\"")
                                .Replace("“", "\"").Replace("’", "'") == vendorName).FirstOrDefault();
                            if (vId == null)
                            {
                                continue;
                            }

                            totalTaxes += vId.TotalIncomes;
                        }
                        var conn = new OleDbConnection(connectionString);
                        conn.Open();
                        using (conn)
                        {
                            string sql = "INSERT INTO [Product_Reports$](Vendor, Income, Expenses, Taxes, [Financial Result]) VALUES (@VendorName, @Income, @Expences, @Taxes, @FinancialResult)";
                            var cmd = new OleDbCommand(sql, conn);

                            cmd.Parameters.AddWithValue("@VendorName", vendorName);
                            cmd.Parameters.AddWithValue("@Income", income);
                            cmd.Parameters.AddWithValue("@Expences", expences);
                            cmd.Parameters.AddWithValue("@Taxes", totalTaxes);
                            cmd.Parameters.AddWithValue("@FinancialResult", income - expences - totalTaxes);

                            cmd.ExecuteNonQuery();
                        }

                        //var taxes = sqLiteContext.Taxes;

                        //foreach (var tax in taxes)
                        //{
                        //    string taxName = tax.Product_Name;

                        //    var vId = sqLiteContext.ProductReports.Where(p => p.ProductName.Replace("\\", "") == taxName).FirstOrDefault();
                        //    if (vId == null)
                        //    {
                        //        continue;
                        //    }
                        //    Console.WriteLine("{0} {1}", taxName);

                        //}
                    }
                }
            }
        }