private string MergeDocumentWithDatabaseAndReturnFilePath(Customer customer, PRCDocument.PRCDocumentType type, Booking booking = null, BookingExtraSelection bes = null)
{
//BOOKING needs to have a propertyID, BES needs to have the ExtraID!!!!
var db = new PortugalVillasContext();
var bookPartTable = new DataTable();
var besPartTable = new DataTable();
var extraAttributes = new DataTable();
PRCDocument aDocument = new PRCDocument(type);
aDocument = InitialSetUpPRCDocument(type);
//tables for merge
List <DataTable> tablesForMerge_PreMerge = new List <DataTable>();
PRCDocumentData theDocumentDataInstance = new PRCDocumentData();
//PRC
tablesForMerge_PreMerge.Add(
theDocumentDataInstance.GetPRCDocumentData(
PRCDocumentData.PRCReturnDataTableWrapperTypes.StandardPRCInformation, 1));
//CUSTOMERS
tablesForMerge_PreMerge.Add(
theDocumentDataInstance.GetPRCDocumentData(PRCDocumentData.PRCReturnDataTableWrapperTypes.CustomerByCustomerID,
customer.CustomerID)); //works*/
//CUSTOMER BANK DETAILS
if (booking != null)
{
tablesForMerge_PreMerge.Add(
theDocumentDataInstance.GetPRCDocumentData(
PRCDocumentData.PRCReturnDataTableWrapperTypes.CustomerBankDetailByCustomerID, customer.CustomerID));
//COMMISSION FOR YEAR
var TotalCommisssion = new DataTable();
TotalCommisssion.Columns.Add("TotalCommissionThisYear");
TotalCommisssion.Columns.Add("CommissionDateTime");
DataRow _row = TotalCommisssion.NewRow();
_row["TotalCommissionThisYear"] = db.Bookings.Where(x => x.PropertyID == booking.PropertyID).Sum(x => x.CommissionAmount);
_row["CommissionDateTime"] = DateTime.Now.ToShortDateString();
TotalCommisssion.Rows.Add(_row);
tablesForMerge_PreMerge.Add(TotalCommisssion);
//BOOKING
tablesForMerge_PreMerge.Add(
theDocumentDataInstance.GetPRCDocumentData(PRCDocumentData.PRCReturnDataTableWrapperTypes.BookingByBookingID, booking.BookingID));
//PROPERTY
tablesForMerge_PreMerge.Add(
theDocumentDataInstance.GetPRCDocumentData(PRCDocumentData.PRCReturnDataTableWrapperTypes.PropertyByPropertyID,
(long)booking.PropertyID));
//TOWN
tablesForMerge_PreMerge.Add(
theDocumentDataInstance.GetPRCDocumentData(
PRCDocumentData.PRCReturnDataTableWrapperTypes.PropertyTownByPropertyTownID, Convert.ToInt64(db.Properties.Where(x => x.PropertyID == booking.PropertyID).FirstOrDefault().PropertyTownID)));
//REGION
tablesForMerge_PreMerge.Add(
theDocumentDataInstance.GetPRCDocumentData(
PRCDocumentData.PRCReturnDataTableWrapperTypes.PropertyRegionByPropertyRegionID, db.Properties.Where(x => x.PropertyID == booking.PropertyID).FirstOrDefault().PropertyTown.PropertyRegionID));
//BOOKINGPART
bookPartTable = theDocumentDataInstance.GetPRCDocumentData(
PRCDocumentData.PRCReturnDataTableWrapperTypes.BookingParticipantByBookingID, booking.BookingID);
bookPartTable.TableName = "BOOKINGPARTTABLE";
tablesForMerge_PreMerge.Add(bookPartTable);
if (booking.BookingParentContainerID != null)
{
//PARENT
tablesForMerge_PreMerge.Add(
theDocumentDataInstance.GetPRCDocumentData(
PRCDocumentData.PRCReturnDataTableWrapperTypes.StandardBookingParentContainer,
(long)booking.BookingParentContainerID));
}
BookingParentContainer bookingParentContainer;
//PROPERTY OWNER
tablesForMerge_PreMerge.Add(
theDocumentDataInstance.GetPRCDocumentData(
PRCDocumentData.PRCReturnDataTableWrapperTypes.StandardPropertyOwner, (long)booking.PropertyID));
}
if (bes != null)
{
//get top level item for same sub type to pull out attributes:
long?extraIDforAtts = null;
try
{
extraIDforAtts = db.BookingExtras.Where(x =>
x.TopLevelItem == true)
.Where(y => y.BookingExtraSubTypeID == bes.BookingExtra.BookingExtraSubTypeID)
.FirstOrDefault().BookingExtraID;
}
catch (Exception)
{
extraIDforAtts = db.BookingExtras.Where(x =>
x.MaxPersons > 10)
.Where(y => y.BookingExtraSubTypeID == bes.BookingExtra.BookingExtraSubTypeID)
.FirstOrDefault().BookingExtraID;
}
//BES
tablesForMerge_PreMerge.Add(
theDocumentDataInstance.GetPRCDocumentData(
PRCDocumentData.PRCReturnDataTableWrapperTypes.BookingExtraSelectionByBookingExtraSelectionID, bes.BookingExtraSelectionID));
//BEST PART (name etc)
besPartTable = theDocumentDataInstance.GetPRCDocumentData(
PRCDocumentData.PRCReturnDataTableWrapperTypes.BookingExtraParticipantByBookingExtraSelectionID,
bes.BookingExtraSelectionID);
besPartTable.TableName = "BESPARTTABLE";
tablesForMerge_PreMerge.Add(besPartTable);
//EXTRA
tablesForMerge_PreMerge.Add(
theDocumentDataInstance.GetPRCDocumentData(
PRCDocumentData.PRCReturnDataTableWrapperTypes.BookingExtraByBookingExtraID, bes.BookingExtraID));
//PARENT
if (bes.BookingParentContainerID != null)
{
tablesForMerge_PreMerge.Add(
theDocumentDataInstance.GetPRCDocumentData(
PRCDocumentData.PRCReturnDataTableWrapperTypes.StandardBookingParentContainer, (long)bes.BookingParentContainerID));
}
//ATTRIBUTES
if (extraIDforAtts != null)
{
extraAttributes = theDocumentDataInstance.GetPRCDocumentData(
PRCDocumentData.PRCReturnDataTableWrapperTypes.BookingExtraAttributesByBookingExtraID,
(long)extraIDforAtts);
extraAttributes.TableName = "ATTRIBUTETABLE";
}
//test if there is a related booking / property IF it's a solo BES event
if (booking != null)
{
if (bes.BookingParentContainerID != 0)
{
booking = db.Bookings.Where(x => x.BookingParentContainerID == bes.BookingParentContainerID).FirstOrDefault();
//PROPERTY
tablesForMerge_PreMerge.Add(
theDocumentDataInstance.GetPRCDocumentData(PRCDocumentData.PRCReturnDataTableWrapperTypes.PropertyByPropertyID,
(long)booking.PropertyID));
//TOWN
tablesForMerge_PreMerge.Add(
theDocumentDataInstance.GetPRCDocumentData(
PRCDocumentData.PRCReturnDataTableWrapperTypes.PropertyTownByPropertyTownID, Convert.ToInt64(db.Properties.Where(x => x.PropertyID == booking.PropertyID).FirstOrDefault().PropertyTownID)));
//REGION
tablesForMerge_PreMerge.Add(
theDocumentDataInstance.GetPRCDocumentData(
PRCDocumentData.PRCReturnDataTableWrapperTypes.PropertyRegionByPropertyRegionID, db.Properties.Where(x => x.PropertyID == booking.PropertyID).FirstOrDefault().PropertyTown.PropertyRegionID));
}
}
}
List <DataTable> tablesForMerge = new List <DataTable>();
foreach (var dataTable in tablesForMerge_PreMerge)
{
if (dataTable.Rows.Count > 0)
{
DataTable tableToModAndReturn = dataTable.Copy();
//clone every table
DataTable dtCloned = dataTable.Copy();
//change every datetime column to a string
//for every dateTime colum, load into new table
foreach (DataColumn col in dtCloned.Columns)
{
var theType = dtCloned.Columns[col.ColumnName].DataType.ToString();
if (dtCloned.Columns[col.ColumnName].DataType.ToString() == "DateTime")
{
//change the type
var name = col.ColumnName;
var value = "";
if (dtCloned.Rows[0][col].ToString().Count() >= 10)
{
value = /*DateTime.Parse(*/ Convert.ToDateTime(dtCloned.Rows[0][col].ToString()).ToString("dd/MM/yyyy");
//).ToString("dd/MM/yyyy");
}
tableToModAndReturn.Columns.Remove(name);
tableToModAndReturn.Columns.Add(new DataColumn
{
ColumnName = name,
DefaultValue = value
});
}
}
tablesForMerge.Add(tableToModAndReturn);
}
}
Aspose.Words.Document theDoc = new Aspose.Words.Document(HttpRuntime.AppDomainAppPath + aDocument.ServerDocumentURL);
//execute the merges
foreach (var dataTable in tablesForMerge)
{
for (int i = 0; i < dataTable.Rows.Count; i++)
{
theDoc.MailMerge.Execute(dataTable.Rows[i]);
}
}
//N.B The regions in the document need to correspond to the table name for the below merge to work
theDoc.MailMerge.ExecuteWithRegions(bookPartTable);
theDoc.MailMerge.ExecuteWithRegions(besPartTable);
theDoc.MailMerge.ExecuteWithRegions(extraAttributes);
string filepathAndName = aDocument.SavePath + aDocument.FileName + ".pdf";
theDoc.Save(aDocument.SavePath + aDocument.FileName + ".pdf");
db.Dispose();
return(filepathAndName);
}
public ActionResult TestingPage()
{
int customerID = 1;
int bookingID = 4;
/*AUTOMAILER CODE*/
// MaintainanceMailer letsMail = new MaintainanceMailer();
//letsMail.SendEmail();
//Merge with Regions
DataTable MyTable = new DataTable();
MyTable.TableName = "MyTable";
ConnectionStringSettings connection = ConfigurationManager.ConnectionStrings["PortugalVillasContext"];
using (SqlConnection conn = new SqlConnection(connection.ConnectionString))
{
using (SqlCommand cmd = new SqlCommand("GetDataForBookingExtraParticipantMailMerge", conn))
{
using (SqlDataAdapter da = new SqlDataAdapter("GetDataForBookingExtraParticipantMailMerge", conn))
{
// cmd.Parameters.AddWithValue("@BookingExtraSelectionID", 3);
//da.SelectCommand = cmd;
da.SelectCommand.CommandText = cmd.CommandText;
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.SelectCommand.Parameters.AddWithValue("@BookingExtraSelectionID", 3);
conn.Open();
da.Fill(MyTable);
}
}
}
////Main Merge
//DataTable MainMergeTable = new DataTable();
//MainMergeTable.TableName = "MainMergeTable";
//ConnectionStringSettings connection = ConfigurationManager.ConnectionStrings["PortugalVillasContext"];
//using (SqlConnection conn = new SqlConnection(connection.ConnectionString))
//{
// using (SqlCommand cmd = new SqlCommand("GetDataForBookingMailMerge", conn))
//{
// using (SqlDataAdapter da = new SqlDataAdapter("GetDataForBookingMailMerge", conn))
// {
// // cmd.Parameters.AddWithValue("@BookingExtraSelectionID", 3);
// //da.SelectCommand = cmd;
// da.SelectCommand.CommandText = cmd.CommandText;
// da.SelectCommand.CommandType = CommandType.StoredProcedure;
// da.SelectCommand.Parameters.AddWithValue("@CustomerID", 1);
// da.SelectCommand.Parameters.AddWithValue("@BookingID", 4);
// conn.Open();
// da.Fill(MyTable);
//}
//}
//}
/*MAIL MERGE CODE*/
PRCDocumentData theDocumentDataInstance = new PRCDocumentData();
//call the methods to bring back the necessary rows
DataTable PRCInformation = theDocumentDataInstance.GetPRCDocumentData(PRCDocumentData.PRCReturnDataTableWrapperTypes.StandardPRCInformation, 1); //NOT WORKING
DataTable Customers = theDocumentDataInstance.GetPRCDocumentData(PRCDocumentData.PRCReturnDataTableWrapperTypes.CustomerByCustomerID, 1); //works
DataTable Booking = theDocumentDataInstance.GetPRCDocumentData(PRCDocumentData.PRCReturnDataTableWrapperTypes.BookingByBookingID, 4); //works
DataTable BookingParticipant = theDocumentDataInstance.GetPRCDocumentData(PRCDocumentData.PRCReturnDataTableWrapperTypes.BookingParticipantByBookingID, 4);
DataTable BookingExtraSelectionByCustomerID = theDocumentDataInstance.GetPRCDocumentData(PRCDocumentData.PRCReturnDataTableWrapperTypes.BookingExtraSelectionByCustomerID, 1);
DataTable StandardBookingExtraSelection = theDocumentDataInstance.GetPRCDocumentData(PRCDocumentData.PRCReturnDataTableWrapperTypes.BookingExtraSelectionByBookingExtraSelectionID, 3);
DataTable StandardPropertyQuery = theDocumentDataInstance.GetPRCDocumentData(PRCDocumentData.PRCReturnDataTableWrapperTypes.PropertyByPropertyID, 221);
DataTable StandardPropertyRegionQuery = theDocumentDataInstance.GetPRCDocumentData(PRCDocumentData.PRCReturnDataTableWrapperTypes.PropertyRegionByPropertyRegionID, 1);
DataTable StandardBookingExtraQuery = theDocumentDataInstance.GetPRCDocumentData(PRCDocumentData.PRCReturnDataTableWrapperTypes.BookingExtraByBookingExtraID, 1);
DataTable BookingExtraParticipantByBookingExtraSelectionID = theDocumentDataInstance.GetPRCDocumentData(PRCDocumentData.PRCReturnDataTableWrapperTypes.BookingExtraParticipantByBookingExtraSelectionID, 3);
DataTable CustomAttributes = new DataTable();
CustomAttributes.Columns.Add("CurrentDate").DefaultValue = DateTime.Now.ToShortDateString();
DataRow newDateRow = CustomAttributes.NewRow();
newDateRow["CurrentDate"] = DateTime.Now.ToShortDateString();
CustomAttributes.Rows.Add(newDateRow);
//pass property ID
DataTable StandardPropertyOwnerQuery = theDocumentDataInstance.GetPRCDocumentData(PRCDocumentData.PRCReturnDataTableWrapperTypes.StandardPropertyOwner, 221);
//attempt a merge
string ApplicationPath = HttpContext.Server.MapPath("~/DocumentTemplates/BookingConfirmationVoucherTemplate.docx");
string SecndApplicationPath = HttpContext.Server.MapPath("~/DocumentTemplates/BCTTemplate.rtf");
string BookingRequestFormEU = HttpContext.Server.MapPath("~/DocumentTemplates/BOOKING_EU_EFTABookingRequestFormTemplate.doc");
string BookingRequestFormUK = HttpContext.Server.MapPath("~/DocumentTemplates/UKROWBookingRequestFormTemplate.doc");
string SecurityDepositTemplate = HttpContext.Server.MapPath("~/DocumentTemplates/SecurityDepositTemplate.docx");
string RentalRemittanceAdviceTemplate = HttpContext.Server.MapPath("~/DocumentTemplates/RemTemplate.doc");
string TestTemplate = HttpContext.Server.MapPath("~/DocumentTemplates/TESTMERGEREGIONS.doc");
string SavePath = HttpContext.Server.MapPath("~/DocumentGenerated/");
// string FullDocumentPath = ApplicationPath + "~/DocumentTemplates/BookingConfirmationVoucherTemplate.docx";
Aspose.Words.Document theDoc = new Aspose.Words.Document(TestTemplate);
//do all the table merges
// theDoc.MailMerge.Execute(MyMergeTable);
theDoc.MailMerge.ExecuteWithRegions(MyTable);
theDoc.Save(@"c:\test\testdoc1.doc");
/*
* PRCImageCollection ImageCollection = new PRCImageCollection(Server, "PRCV29");
*
* BookingCalendar TestBookingCalendar = new BookingCalendar(1);
* TestBookingCalendar.GetAllBookingDatesAndAddToThisCalendar();
* ViewBag.BookingCalDates = TestBookingCalendar;
*
*
* Session.Add("test", Property.GetPropertyByID(76));
*/
return(View());
}