public static void SaveData()
{
var mongoClient = new MongoClient(@"mongodb://localhost/");
var mongoServer = mongoClient.GetServer();
var productsDb = mongoServer.GetDatabase("ProductsDb");
var products = productsDb.GetCollection <FullIProductInformation>("products");
SqlServerEntities db = new SqlServerEntities();
using (db)
{
var salesReports = db.Sales
.Include("Product")
.Include("Supermarket")
.Include("Product.Vendor")
.ToList()
.GroupBy(x => x.Product.ProductName);
foreach (var item in salesReports)
{
FullIProductInformation info = new FullIProductInformation();
info.TotalIncomes = item.Sum(x => x.Sum);
info.ProductName = item.Key;
info.TotalQuantitySold = item.Sum(x => x.Quantity);
info.VendorName = item.First().Product.Vendor.VendorName;
info.ProductId = item.First().ProductId;
SaveToFile(info);
products.Insert(info);
}
}
}
public static List <XMLReportInfo> XMLPersiter()
{
List <XMLReportInfo> info = new List <XMLReportInfo>();
Database.SetInitializer(new MigrateDatabaseToLatestVersion <SqlServerEntities, Configuration>());
using (var db = new SqlServerEntities())
{
var salesReports = (from s in db.Sales
group s by s.Product.Vendor.VendorName).ToList();
foreach (var report in salesReports)
{
XMLReportInfo curReport = new XMLReportInfo();
curReport.Key = report.Key;
List <decimal> sums = new List <decimal>();
List <DateTime> dates = new List <DateTime>();
foreach (var item in report)
{
dates.Add(item.Date);
sums.Add(item.Sum);
}
curReport.Sums = sums;
curReport.Dates = dates;
info.Add(curReport);
}
}
return(info);
}
public string updateTareas(Models.clsTareas oClsTareas)
{
try
{
using (var dbContext = new SqlServerEntities())
{
Entidades.tbTareas oTarea = (from q in dbContext.tbTareas
where q.tareCodigo == oClsTareas.inCodigo
select q).FirstOrDefault();
oTarea.tareTitular = oClsTareas.stTitular;
oTarea.tareAsunto = oClsTareas.stAsunto;
oTarea.tareFechaVencimiento = oClsTareas.stFechaVencimiento;
oTarea.tareContacto = oClsTareas.stContacto;
oTarea.tareCuenta = oClsTareas.stCuenta;
oTarea.estaCodigo = oClsTareas.oClsEstado.inEstaCodigo;
oTarea.prioCodigo = oClsTareas.oClsPrioridad.inCodigo;
oTarea.tareEnviarMensaje = oClsTareas.stEnviarMensaje;
oTarea.tareRepetir = oClsTareas.stRepetir;
oTarea.tareDescripcion = oClsTareas.stDescripcion;
dbContext.SaveChanges();
return("Grabación correcta");
}
//using ()
}
catch (Exception ex)
{
throw ex;
}
}
public static void SetDataFromXls(List <List <SaleInfo> > reports)
{
Database.SetInitializer(new MigrateDatabaseToLatestVersion <SqlServerEntities, Configuration>());
using (var db = new SqlServerEntities())
{
foreach (var file in reports)
{
foreach (var rep in file)
{
var supermarket = db.Supermarkets.FirstOrDefault(x => x.SupermarketName == rep.Location);
if (supermarket == null)
{
supermarket = new Supermarket {
SupermarketName = rep.Location
};
db.Supermarkets.Add(supermarket);
}
var sale = new Sale
{
ProductId = rep.ProductId,
Supermarket = supermarket,
Date = rep.SaleDate,
Quantity = rep.Quantity,
UnitPrice = rep.UnitPrice,
Sum = rep.Sum
};
db.Sales.Add(sale);
db.SaveChanges();
}
}
}
}
public List <Models.clsTareas> getAllTareas()
{
try
{
using (SqlServerEntities dbContext = new SqlServerEntities())
{
return((from q in dbContext.tbTareas
join tbEs in dbContext.tbEstadoTareas on q.estaCodigo equals tbEs.estaCodigo
join tbPr in dbContext.tbPrioridad on q.prioCodigo equals tbPr.prioCodigo
select new Models.clsTareas
{
inCodigo = q.tareCodigo,
stTitular = q.tareTitular,
stAsunto = q.tareAsunto,
stFechaVencimiento = q.tareFechaVencimiento,
stContacto = q.tareContacto,
stCuenta = q.tareCuenta,
oClsEstado = new Models.clsEstado {
inEstaCodigo = (int)q.estaCodigo, stDescripcion = tbEs.estaDescripcion
},
oClsPrioridad = new Models.clsPrioridad {
inCodigo = (int)q.prioCodigo, stDescripcion = tbPr.prioDescripcion
},
stEnviarMensaje = q.tareEnviarMensaje,
stRepetir = q.tareRepetir,
stDescripcion = q.tareDescripcion
}
).ToList());
}
}
catch (Exception ex)
{
throw ex;
}
}
protected List <DTOProdotto> GetByContition(Func <tabProdotti, bool> expression)
{
using (var ctx = new SqlServerEntities())
{
if (!ctx.tabProdotti.Any())
{
var tab = new tabProdotti();
tab.ID = Guid.NewGuid();
tab.Descrizione = "Generico";
tab.DescrizioneBreve = "Generico";
ctx.Entry(tab).State = EntityState.Added;
ctx.SaveChanges();
}
//TODO: linq Kit è più efficente
return(ctx.tabProdotti.Where(x => !x.isDeleted).Where(expression).Select(MapTable).ToList());
}
}
public static void SetData(
List <VendorInformation> vendors,
List <MeasureInformation> measures,
List <ProductInformation> products)
{
Database.SetInitializer(new MigrateDatabaseToLatestVersion <SqlServerEntities, Configuration>());
using (var db = new SqlServerEntities())
{
foreach (var vend in vendors)
{
db.Vendors.Add(new Vendor
{
VendorId = vend.VendorId,
VendorName = vend.VendorName
});
}
foreach (var meas in measures)
{
db.Measures.Add(new Measure
{
MeasureId = meas.MeasureId,
MeasureName = meas.MeasureName
});
}
foreach (var prod in products)
{
db.Products.Add(new Product
{
ProductName = prod.ProductName,
BasePrice = (decimal)prod.BasePrice,
MeasureId = prod.MeasureId,
VendorId = prod.VendorId
});
}
db.SaveChanges();
}
}
public string deleteTareas(Models.clsTareas oClsTareas)
{
try
{
using (var dbContext = new SqlServerEntities())
{
Entidades.tbTareas oTarea = (from q in dbContext.tbTareas
where q.tareCodigo == oClsTareas.inCodigo
select q).FirstOrDefault();
dbContext.tbTareas.Remove(oTarea);
dbContext.SaveChanges();
return("Grabación correcta");
}
//using ()
}
catch (Exception ex)
{
throw ex;
}
}
public static void GenerateTaxes()
{
using (SqlServerEntities db = new SqlServerEntities())
{
var names = from p in db.Products
select new
{
Product = p.ProductName,
Id = p.ProductId
};
using (VendorsDB dbLite = new VendorsDB())
{
Random rnd = new Random();
foreach (var item in names)
{
dbLite.Taxes.Add(new Tax()
{
Id = item.Id, ProductName = item.Product, Tax1 = rnd.Next(1, 40)
});
}
dbLite.SaveChanges();
}
}
}
public CategoriaService(Guid identifier, SqlServerEntities context) : base(identifier, context)
{
}
public static void CreatePDF()
{
Document myDocument = new Document(PageSize.A4.Rotate());
try
{
PdfWriter.GetInstance(myDocument, new FileStream("..\\..\\salesReport.pdf", FileMode.Create));
PdfPTable table = new PdfPTable(5);
float[] widths = new float[] { 25f, 10f, 10f, 45f, 10f };
table.SetWidths(widths);
table.DefaultCell.FixedHeight = 27f;
table.DefaultCell.VerticalAlignment = Element.ALIGN_MIDDLE;
myDocument.Open();
SqlServerEntities context = new SqlServerEntities();
var sales = (context.Sales.Include("Product").Include("Supermarket").Include("Product.Measure")).ToList().GroupBy(sale => sale.Date);
decimal totalSum = 0;
myDocument.Open();
var headerFont = FontFactory.GetFont("Arial", 14, Font.BOLD);
PdfPCell header = new PdfPCell(new Phrase("Aggregate Sales Report", headerFont));
header.Colspan = 5;
header.FixedHeight = 27f;
header.HorizontalAlignment = Element.ALIGN_CENTER;
header.VerticalAlignment = Element.ALIGN_MIDDLE;
table.AddCell(header);
foreach (var sale in sales)
{
decimal currentSum = 0;
var font = FontFactory.GetFont("Arial", 14, Font.BOLD);
PdfPCell date = new PdfPCell(new Phrase("Date: " + (sale.Key.Date).ToString("dd-MM-yyyy"), font));
date.Colspan = 5;
date.FixedHeight = 27f;
date.BackgroundColor = new BaseColor(210, 210, 210);
date.HorizontalAlignment = Element.ALIGN_LEFT;
date.VerticalAlignment = Element.ALIGN_MIDDLE;
table.AddCell(date);
PdfPCell product = new PdfPCell(new Phrase("Product", font));
product.FixedHeight = 27f;
product.BackgroundColor = new BaseColor(210, 210, 210);
product.HorizontalAlignment = Element.ALIGN_LEFT;
product.VerticalAlignment = Element.ALIGN_MIDDLE;
table.AddCell(product);
PdfPCell quantity = new PdfPCell(new Phrase("Quantity", font));
quantity.FixedHeight = 27f;
quantity.BackgroundColor = new BaseColor(210, 210, 210);
quantity.HorizontalAlignment = Element.ALIGN_LEFT;
quantity.VerticalAlignment = Element.ALIGN_MIDDLE;
table.AddCell(quantity);
PdfPCell price = new PdfPCell(new Phrase("Unit Price", font));
price.FixedHeight = 27f;
price.BackgroundColor = new BaseColor(210, 210, 210);
price.HorizontalAlignment = Element.ALIGN_LEFT;
price.VerticalAlignment = Element.ALIGN_MIDDLE;
table.AddCell(price);
PdfPCell location = new PdfPCell(new Phrase("Location", font));
location.FixedHeight = 27f;
location.BackgroundColor = new BaseColor(210, 210, 210);
location.HorizontalAlignment = Element.ALIGN_LEFT;
location.VerticalAlignment = Element.ALIGN_MIDDLE;
table.AddCell(location);
PdfPCell sumCol = new PdfPCell(new Phrase("Sum", font));
sumCol.FixedHeight = 27f;
sumCol.BackgroundColor = new BaseColor(210, 210, 210);
sumCol.HorizontalAlignment = Element.ALIGN_LEFT;
sumCol.VerticalAlignment = Element.ALIGN_MIDDLE;
table.AddCell(sumCol);
foreach (var item in sale)
{
table.AddCell((item.Product.ProductName).ToString());
table.AddCell((item.Quantity).ToString() + " " + item.Product.Measure.MeasureName);
table.AddCell((item.UnitPrice).ToString());
table.AddCell(String.Format("Supermarket \"{0}\"", item.Supermarket.SupermarketName));
PdfPCell sum = new PdfPCell(new Phrase((item.Sum).ToString()));
sum.HorizontalAlignment = Element.ALIGN_RIGHT;
table.AddCell(sum);
currentSum += item.Sum;
totalSum += item.Sum;
}
PdfPCell footerDate = new PdfPCell(new Phrase("Total sum for " + (sale.Key.Date).ToString("dd-MM-yyyy") + ": "));
footerDate.Colspan = 4;
footerDate.FixedHeight = 27f;
footerDate.HorizontalAlignment = Element.ALIGN_RIGHT;
footerDate.VerticalAlignment = Element.ALIGN_MIDDLE;
table.AddCell(footerDate);
PdfPCell current = new PdfPCell(new Phrase(currentSum.ToString()));
current.FixedHeight = 27f;
current.HorizontalAlignment = Element.ALIGN_RIGHT;
current.VerticalAlignment = Element.ALIGN_MIDDLE;
table.AddCell(current);
}
PdfPCell grandTotal = new PdfPCell(new Phrase("Grand total: "));
grandTotal.Colspan = 4;
grandTotal.FixedHeight = 27f;
grandTotal.BackgroundColor = new BaseColor(210, 210, 210);
grandTotal.HorizontalAlignment = Element.ALIGN_RIGHT;
grandTotal.VerticalAlignment = Element.ALIGN_MIDDLE;
table.AddCell(grandTotal);
PdfPCell total = new PdfPCell(new Phrase(totalSum.ToString()));
total.BackgroundColor = new BaseColor(210, 210, 210);
total.HorizontalAlignment = Element.ALIGN_RIGHT;
total.VerticalAlignment = Element.ALIGN_MIDDLE;
table.AddCell(total);
myDocument.Add(table);
}
catch (DocumentException de)
{
Console.WriteLine(de.Message);
}
catch (IOException ioe)
{
Console.WriteLine(ioe.Message);
}
myDocument.Close();
}
public ProdottoService(Guid identifier, SqlServerEntities context) : base(identifier, context)
{
}
