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();
}
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);
}
}
// 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);
}
// 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;
}
