//
// This code is part of Document Solutions for Word demos.
// Copyright (c) MESCIUS inc. All rights reserved.
//
using System;
using System.IO;
using System.Drawing;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Linq;
using System.Globalization;
using GrapeCity.Documents.Word;
namespace DsWordWeb.Demos
{
// This data template sample prints an invoice containing the list
// of products in the purchase order.
// The invoice data is generated for a randomly selected
// order from the sample DsNWind database.
public class DataTplOrderInvoice
{
public GcWordDocument CreateDocx(int _ = 0)
{
using var ds = new DataSet();
// Load the sample database, fetch a random supplier
// and a random order from that supplier:
ds.ReadXml(Path.Combine("Resources", "data", "DsNWind.xml"));
// Database tables used by the invoice:
var dtSuppliers = ds.Tables["Suppliers"];
var dtOrders = ds.Tables["OrdersCustomersEmployees"];
var dtOrdersDetails = ds.Tables["EmployeesProductsOrders"];
// Collect order data:
var random = Util.NewRandom();
var fetchedIndex = random.Next(dtSuppliers.Select().Count());
var supplier =
dtSuppliers.Select()
.Skip(fetchedIndex).Take(1)
.Select(it => new
{
SupplierID = Convert.ToInt32(it["SupplierID"]),
CompanyName = it["CompanyName"].ToString(),
ContactName = it["ContactName"].ToString(),
ContactTitle = it["ContactTitle"].ToString(),
Address = it["Address"].ToString(),
City = it["City"].ToString(),
Region = it["Region"].ToString(),
PostalCode = it["PostalCode"].ToString(),
Country = it["Country"].ToString(),
Phone = it["Phone"].ToString(),
Fax = it["Fax"].ToString(),
HomePage = it["HomePage"].ToString()
}).FirstOrDefault();
fetchedIndex = random.Next(dtOrders.Select().Count());
var order = dtOrders.Select()
.Skip(fetchedIndex).Take(1)
.Select(it => new
{
OrderDate = it["OrderDate"],
OrderID = Convert.ToInt32(it["OrderID"]),
CompanyName = it["CompanyName"].ToString(),
Name = $"{it["FirstName"]} {it["LastName"]}",
Address = $"{it["ShipAddress"]},\n{it["ShipCity"]} {it["ShipRegion"]} {it["ShipPostalCode"]} {it["ShipCountry"]}",
Email = GetEmail(it["FirstName"].ToString(), it["LastName"].ToString(), it["CompanyName"].ToString()),
}).FirstOrDefault();
var orderDetails = dtOrdersDetails.Select()
.Select(it => new
{
OrderID = Convert.ToInt32(it["OrderID"]),
ProductName = it["ProductName"].ToString(),
UnitPrice = Convert.ToDecimal(it["UnitPrice"]),
Quantity = Convert.ToDecimal(it["Quantity"]),
Total = Convert.ToDecimal(it["UnitPrice"]) * Convert.ToDecimal(it["Quantity"]),
})
.Where(it => it.OrderID == order.OrderID)
.OrderBy(it => it.ProductName).ToList();
// Finally, prep the integrated data source for the template:
var data = new
{
o = order,
ps = orderDetails,
total = orderDetails.Sum(od_ => od_.Total),
};
// Load the template DOCX, add the data source and process the template:
var doc = new GcWordDocument();
doc.Load(Path.Combine("Resources", "WordDocs", "InvoiceTemplate.docx"));
doc.DataTemplate.DataSources.Add("ds", data);
doc.DataTemplate.Process(CultureInfo.GetCultureInfo("en-US"));
// Done:
return doc;
}
// Generate a sample email address from other data:
private string GetEmail(string firstName, string lastName, string companyName)
{
var x = new string(companyName.ToLower().Where(c_ => char.IsLetterOrDigit(c_)).ToArray());
return $"{firstName.ToLower()}.{char.ToLower(lastName[0])}@{x}.com";
}
public static List<string[]> GetSampleParamsList()
{
return new List<string[]>()
{
new string[] { "@data-templates/Order Invoice", "Generate the invoice for a random order fetched from a database", null },
new string[] { "@use-data-tpl/Order Invoice", "Generate the invoice for a random order fetched from a database", null },
};
}
}
}