private void ReplicateProducts(SalesSystemContext sqlContext) { var oracleDb = new OracleEntities(); var products = oracleDb.PRODUCTS.Select(p => new { p.NAME, p.MESURE_ID, p.PRICE, p.VENDOR_ID, }); foreach (var product in products) { Measure measure = Measure.Pcs; switch ((int)product.MESURE_ID) { case 2: measure = Measure.L; break; case 3: measure = Measure.Ml; break; case 4: measure = Measure.Kg; break; case 5: measure = Measure.Gr; break; } sqlContext.Products.AddOrUpdate(p => p.Name, new Product() { Name = product.NAME, Price = product.PRICE, VendorId = (int)product.VENDOR_ID, Measure = measure }); } sqlContext.SaveChanges(); }
static void Main(string[] args) { var sqlContext = new SalesSystemContext(); ReplicateTowns(sqlContext); ReplicateVendors(sqlContext); ReplicateProducts(sqlContext); /* TESTING THE CONNECTIONS * var sqlContext = new SalesSystemContext(); var product = sqlContext.Towns.Select(p => p.Name); foreach (var p in product) { Console.WriteLine(p); } Console.WriteLine("end of sqldata print"); var orcleContext = new OracleEntities(); var mesurments = orcleContext.MEASURES.Select(m => m.NAME); foreach (var mes in mesurments) { Console.WriteLine(mes); } */ }
public void ExecuteReplication() { var context = new SalesSystemContext(); int count = context.Towns.Count(); ReplicateTowns(context); ReplicateVendors(context); ReplicateProducts(context); }
public static void PushXmlToDb(string filePath) { try { // LOAD XML XDocument xmlDocument = XDocument.Load(filePath); var vendors = xmlDocument.Descendants("vendor"); if (vendors.Any() == false) { throw new ArgumentException(@"No data found, ensure that the file contains any vendors reports."); } else { using (var db = new SalesSystemContext()) { foreach (var vendor in vendors) { // VENDRO NAME var vendorName = vendor.FirstAttribute.Value; var vendorExpenses = vendor.Descendants("expenses"); // VENDOR ID var vendorId = db.Vendors.FirstOrDefault(v => v.Name == vendorName).Id; foreach (var vendorExpense in vendorExpenses) { // EXPENSE PERIOD (MONTH - YEAR) var vendorExpensePeriod = vendorExpense.FirstAttribute.Value; // EXPENSE TOTAL (MONEY) var vendorExpenseTotal = decimal.Parse(vendorExpense.Value); // ADD EXPENSE db.Expenses.Add(new Expense() { VendorId = vendorId, Period = vendorExpensePeriod, Total = vendorExpenseTotal }); } } // ON SUCCESS db.SaveChanges(); MessageBox.Show(@"Xml data successiful imported in MSSQL database."); } } } catch (Exception e) { MessageBox.Show(e.StackTrace); } }
// GET SALES private static void GetSales(string directory, SalesSystemContext db, IList<Sale> sales) { foreach (var sale in GetSalesFromExcelFiles(directory, db)) { sales.Add(sale); } string[] subDirectories = Directory.GetDirectories(directory); if (subDirectories.Length > 0) { foreach (var subDirectory in subDirectories) { GetSales(subDirectory, db, sales); } } }
// MIGRATE DATA public static void MigrateData(string zipFilePath) { ExtractZipFile(zipFilePath); using (var db = new SalesSystemContext()) { IList<Sale> allSales = new List<Sale>(); GetSales(TempFolderForExtract, db, allSales); foreach (var sale in allSales) { db.Sales.Add(sale); } db.SaveChanges(); } Directory.Delete(TempFolderForExtract, true); }
private static string[] returnJSONSalesReports(DateTime startDate, DateTime endDate) { List<string> salesReportsJSON = new List<string>(); using (var ctx = new SalesSystemContext()) { var sales = ctx.Sales.Where(s => s.Date >= startDate && s.Date <= endDate).GroupBy(s => new { s.ProductId, s.Product.Name, VendorName = s.Product.Vendor.Name }) .Select(gr => new DB_TeamCapri.Resources.SalesByProductReport() { product_id = gr.Key.ProductId, product_name = gr.Key.Name, vendor_name = gr.Key.VendorName, total_quantity_sold = gr.Sum(s => s.Quantity), total_incomes = gr.Sum(s => s.Product.Price) }).ToList(); foreach (var sale in sales) { string jsonReport = JsonConvert.SerializeObject(sale).Replace('_', '-'); salesReportsJSON.Add(jsonReport); } return salesReportsJSON.ToArray(); } }
public MssqlToMysql(MySQLContext mysql, SalesSystemContext mssql) { this.mssql = mssql; this.mysql = mysql; }
// GET SALES FROM EXCEL FILES private static ICollection<Sale> GetSalesFromExcelFiles(string directory, SalesSystemContext db) { IList<Sale> sales = new List<Sale>(); string[] excelFilePaths = Directory.GetFiles(directory, @"*.xls"); foreach (var excelFilePath in excelFilePaths) { var excelConnectionString = String.Format(Settings.Default.ExcelReadConnectionString, excelFilePath); var excelConnection = new OleDbConnection(excelConnectionString); excelConnection.Open(); DataSet dataSet = new DataSet(); using (excelConnection) { var selectAllRowsCommandString = "SELECT * FROM [Sales$]"; var selectAllRowsCommand = new OleDbCommand(selectAllRowsCommandString, excelConnection); var excelAdapter = new OleDbDataAdapter(selectAllRowsCommand); excelAdapter.Fill(dataSet, @"Sales"); } var excelRows = dataSet.Tables[@"Sales"].Rows; var supermarketName = excelRows[0][0].ToString(); if (!db.Supermarkets.Any(s => s.Name == supermarketName)) { db.Supermarkets.Add(new Supermarket() { Name = supermarketName, Location = supermarketName.Replace("Supermarket", "").Trim() }); db.SaveChanges(); } var rowsCount = excelRows.Count; for (int i = 2; i < rowsCount - 1; i++) { var productName = excelRows[i][0].ToString(); var product = db.Products.FirstOrDefault(p => p.Name == productName); bool isAdded = false; string saleDateString = Path.GetFileName(Path.GetDirectoryName(excelFilePath)); int supermarketId = db.Supermarkets.First(s => s.Name == supermarketName).Id; if (db.Sales.Any()) { var addedsales = db.Sales.Select(s => new { Date = s.Date, Spp = s.SupermarketId, }); foreach (var addsale in addedsales) { if (addsale.Date == DateTime.Parse(saleDateString) && addsale.Spp == supermarketId) { isAdded = true; } } } // IF PRODUCT EXIST if (product != null && isAdded == false) { int productId = product.Id, quantity; int.TryParse(excelRows[i][1].ToString(), out quantity); decimal price; decimal.TryParse(excelRows[i][2].ToString(), out price); sales.Add(new Sale() { Date = DateTime.Parse(saleDateString), Product = db.Products.Find(productId), Quantity = quantity, Supermarket = db.Supermarkets.Find(supermarketId), Price = price, ItemSum = price*quantity }); } // ADD NEW PRODUCT else { // todo add new product db.Products.Add(new Product() { Name = productName, VendorId = 1, Measure = new Measure(), Price = 1 }); db.SaveChanges(); } } } // RETURN VALUE return sales; }
public void GeneratePDFReport(DateTime start, DateTime end) { System.IO.Directory.CreateDirectory(@"..\..\..\PDFReport"); using (FileStream fileStream = new FileStream(@"..\..\..\PDFReport\PDFReport.pdf", FileMode.Create, FileAccess.Write, FileShare.None)) using (Document pdfDocument = new Document()) using (var mssqlContext = new SalesSystemContext()) { PdfWriter pdfWriter = PdfWriter.GetInstance(pdfDocument, fileStream); pdfDocument.Open(); PdfPTable mainTable = new PdfPTable(5); PdfPCell mainHeaderCell = new PdfPCell(new Phrase("Aggregated Sales Report")); mainHeaderCell.Colspan = 5; mainHeaderCell.HorizontalAlignment = 1; mainTable.AddCell(mainHeaderCell); var saleReportsByDate = from Sale in mssqlContext.Sales join product in mssqlContext.Products on Sale.ProductId equals product.Id join supermarket in mssqlContext.Supermarkets on Sale.SupermarketId equals supermarket.Id where Sale.Date >= start && Sale.Date <= end group Sale by Sale.Date; decimal grandTotalSum = 0; foreach (var date in saleReportsByDate) { decimal totalSum = 0; PdfPCell dateHeaderCell = new PdfPCell(new Phrase("Date: " + String.Format("{0:d-MM-yyyy}", date.Key))); dateHeaderCell.BackgroundColor = BaseColor.LIGHT_GRAY; dateHeaderCell.Colspan = 5; dateHeaderCell.HorizontalAlignment = 0; mainTable.AddCell(dateHeaderCell); mainTable.AddCell(new PdfPCell(new Phrase("Product"))); mainTable.AddCell(new PdfPCell(new Phrase("Quantity"))); mainTable.AddCell(new PdfPCell(new Phrase("Unit Price"))); mainTable.AddCell(new PdfPCell(new Phrase("Location"))); mainTable.AddCell(new PdfPCell(new Phrase("Sum"))); foreach (var record in date) { mainTable.AddCell(new PdfPCell(new Phrase(record.Product.Name))); mainTable.AddCell(new PdfPCell(new Phrase(record.Quantity.ToString()))); mainTable.AddCell(new PdfPCell(new Phrase(record.Price.ToString()))); mainTable.AddCell(new PdfPCell(new Phrase(record.Supermarket.Name))); mainTable.AddCell(new PdfPCell(new Phrase((record.Quantity * record.Price).ToString()))); totalSum += record.Quantity * record.Price; } PdfPCell totalSumCellTitle = new PdfPCell(new Phrase("Total sum for " + String.Format("{0:d-MM-yyyy}", date.Key) + ":")); totalSumCellTitle.Colspan = 4; totalSumCellTitle.HorizontalAlignment = 1; PdfPCell totalSumCell = new PdfPCell(new Phrase(totalSum.ToString())); totalSumCell.HorizontalAlignment = 1; mainTable.AddCell(totalSumCellTitle); mainTable.AddCell(totalSumCell); grandTotalSum += totalSum; } PdfPCell grandTotalCellTitle = new PdfPCell(new Phrase("Grand total:")); grandTotalCellTitle.Colspan = 4; grandTotalCellTitle.HorizontalAlignment = 1; grandTotalCellTitle.BackgroundColor = BaseColor.CYAN; PdfPCell grandTotalCell = new PdfPCell(new Phrase(grandTotalSum.ToString())); grandTotalCell.HorizontalAlignment = 1; grandTotalCell.BackgroundColor = BaseColor.CYAN; mainTable.AddCell(grandTotalCellTitle); mainTable.AddCell(grandTotalCell); pdfDocument.Add(mainTable); pdfDocument.Close(); pdfWriter.Close(); } }
public static void GenerateXmlReport(string startDate, string endDate) { // INIT DB using (var db = new SalesSystemContext()) { // PARSE INPUT DATES TO DATETIME DateTime fromDate = DateTime.ParseExact(startDate, "dd-MM-yyyy", CultureInfo.InvariantCulture), toDate = DateTime.ParseExact(endDate, "dd-MM-yyyy", CultureInfo.InvariantCulture); // QUERY var sales = db.Sales .Where(s => s.Date >= fromDate && s.Date <= toDate) .Select(sale => new { VendroName = sale.Product.Vendor.Name, SaleDate = sale.Date, Price = sale.Product.Price, Quantity = sale.Quantity }).GroupBy(r => r.VendroName).OrderBy(r => r.Key); // IF QUERY CANT RETURN DATA if (sales.ToList().Count < 1) { MessageBox.Show(@"No data found."); } else { var reports = new Dictionary<string, Dictionary<string, decimal>>(); // GET DATA FROM RETURNED QUERY foreach (var report in sales) { // VENDOR NAME string vendorName = report.Key; // SALE INFO (DATE/PRICE) var saleInfo = new Dictionary<string, decimal>(); foreach (var sale in report) { // SALE DATE [MONTH-YEAR] string saleDate = String.Format("{0:MMM}-{0:yyyy}", sale.SaleDate); // TOTAL PRICE [PRICE * QUANTITY] decimal saleTotalPrice = sale.Price * sale.Quantity; // IF SELL INFO DOES NOT CONTAIN THE DATE - ADD DATA if (!saleInfo.ContainsKey(saleDate)) { saleInfo.Add(saleDate, saleTotalPrice); } // ELSE UPDATE DATA else { saleInfo[saleDate] += saleTotalPrice; } } // ADD DATA TO REPORTS reports.Add(vendorName, saleInfo); } // GENERATE XML // XML CONFIGS const string fileName = "../../sales-by-vendor.xml"; var encoding = Encoding.GetEncoding(@"Windows-1251"); using (var writer = new XmlTextWriter(fileName, encoding)) { // WRITER CONFIGS writer.Formatting = Formatting.Indented; writer.IndentChar = '\t'; writer.Indentation = 1; // DOCUMENT START writer.WriteStartDocument(); // WRITE DOCUMENT writer.WriteStartElement(@"expenses-by-month"); foreach (var report in reports) { // REPORT VENDOR string vendor = report.Key; writer.WriteStartElement(@"vendor"); writer.WriteAttributeString(@"name", vendor); foreach (var data in report.Value) { // REPORT [DATE] - [TOTAL] string reportDate = data.Key, reportTotal = data.Value.ToString("0.00"); writer.WriteStartElement(@"expenses"); writer.WriteAttributeString(@"month", reportDate); writer.WriteString(reportTotal); writer.WriteEndElement(); } writer.WriteEndElement(); } // DOCUMENT END writer.WriteEndDocument(); } // ON COMPLETE MessageBox.Show(@"Done generated reports in DB_TeamCapri/sales-by-vendor.xml"); } } }