public static void SeedMaterials(string excelFileName, TuxedoDb db)
{
var connectionString = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=..\\..\\..\\ExtractedExcelFiles\\tables\\{excelFileName}.xlsx; Extended Properties = \"Excel 12.0 Xml;HDR=YES\"";
OleDbConnection connection = new OleDbConnection(connectionString);
connection.Open();
OleDbCommand command = new OleDbCommand("SELECT * FROM [Sheet1$]", connection);
OleDbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
string Id = reader.GetDouble(0).ToString();
var Name = (string)reader["Name"];
var CountryId = reader.GetDouble(2).ToString();
Console.WriteLine($@"Material: {Name} with ID {Id} added to SQL db");
db.Materials.Add(new Material()
{
ID = int.Parse(Id),
Name = Name,
CountryID = int.Parse(CountryId)
});
}
}
public static void SeedSales(TuxedoDb db, IEnumerable <Sale> salesList)
{
foreach (var sale in salesList)
{
db.Sales.Add(new Sale()
{
Id = sale.Id,
StoreName = sale.StoreName,
TurnOver = sale.TurnOver
});
}
db.SaveChanges();
}
public static void ExportItems(TuxedoDb db)
{
var pdfFileDestination = "../../../Catalogue.pdf";
FileStream fs = new FileStream(pdfFileDestination, FileMode.Create, FileAccess.Write, FileShare.None);
Document doc = new Document();
PdfWriter writer = PdfWriter.GetInstance(doc, fs);
doc.Open();
doc.AddHeader("", "Catalogue of Suits");
var heading = new Phrase("Catalogue of Suits");
doc.Add(heading);
var table = new PdfPTable(3);
table.WidthPercentage = 80;
var cellModel = new PdfPCell(new Phrase("Suit Model"));
cellModel.BackgroundColor = BaseColor.CYAN;
var cellPrice = new PdfPCell(new Phrase("Suit Price"));
cellPrice.BackgroundColor = BaseColor.CYAN;
var cellBrand = new PdfPCell(new Phrase("Suit Brand"));
cellBrand.BackgroundColor = BaseColor.CYAN;
table.AddCell(cellBrand);
table.AddCell(cellModel);
table.AddCell(cellPrice);
foreach (var item in db.Items)
{
var brand = db.Brands
.Where(b => b.ID == item.BrandID)
.Select(br => br.Name)
.FirstOrDefault();
table.AddCell(brand);
table.AddCell(item.Model);
table.AddCell(item.Price.ToString("0.00"));
}
doc.Add(table);
doc.Close();
}
public static void SeedItems(string excelFileName, TuxedoDb db)
{
var connectionString = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=..\\..\\..\\ExtractedExcelFiles\\tables\\{excelFileName}.xlsx; Extended Properties = \"Excel 12.0 Xml;HDR=YES\"";
OleDbConnection connection = new OleDbConnection(connectionString);
connection.Open();
OleDbCommand command = new OleDbCommand("SELECT * FROM [Sheet1$]", connection);
OleDbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
string Id = reader.GetDouble(0).ToString();
var model = (string)reader["Model"];
var BrandId = reader.GetDouble(2).ToString();
var CountryId = reader.GetDouble(3).ToString();
var ColorId = reader.GetDouble(4).ToString();
var TypeId = reader.GetDouble(5).ToString();
var MaterialId = reader.GetDouble(6).ToString();
var Price = reader.GetDouble(7).ToString();
Console.WriteLine($@"Item: {model} with ID {Id} added to SQL db");
db.Items.Add(new Item()
{
ID = int.Parse(Id),
Model = model,
BrandID = int.Parse(BrandId),
CountryID = int.Parse(CountryId),
ColorID = int.Parse(ColorId),
TypeID = int.Parse(TypeId),
MaterialID = int.Parse(MaterialId),
Price = decimal.Parse(Price)
});
}
db.SaveChanges();
}
public static void ExportTypes(TuxedoDb db)
{
string fileName = "../../../Types.xml";
Encoding encoding = Encoding.GetEncoding("windows-1251");
using (XmlTextWriter writer = new XmlTextWriter(fileName, encoding))
{
writer.Formatting = Formatting.Indented;
writer.IndentChar = '\t';
writer.Indentation = 1;
writer.WriteStartDocument();
writer.WriteStartElement("types");
foreach (var type in db.Types)
{
writer.WriteStartElement("type");
writer.WriteElementString("name", type.Name);
writer.WriteEndElement();
}
writer.WriteEndDocument();
}
}
static ExportJSON()
{
db = new TuxedoDb();
}
public static void ExecuteScript()
{
var db = new TuxedoDb();
db.Database.CreateIfNotExists();
}
