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");
}
}
}
