/// <summary>
/// 获取配置。
/// </summary>
/// <typeparam name="T">配置类型。</typeparam>
/// <param name="getParameter">获取配置参数。</param>
/// <param name="restartAppDomainOnChange">当配置改变时重新启动应用程序域。</param>
/// <returns>配置。</returns>
public override T GetMergedConfig <T>(IConfigParameter getParameter, bool restartAppDomainOnChange)
{
Check.Argument.IsNotNull("getParameter", getParameter);
Check.Argument.IsAssignableFrom("getParameter", getParameter, typeof(XmlConfigGetParameter));
T config = GetConfigFromCache <T>(getParameter);
if (config == null)
{
lock (this.SyncLock)
{
config = GetConfigFromCache <T>(getParameter);
if (config == null)
{
config = new T();
XmlConfigGetParameter cp = getParameter as XmlConfigGetParameter;
if (cp.HasFiles)
{
FileMergeResult mergeResult = new FileMergeResult(new List <string>(cp.Files));
foreach (string file in cp.Files)
{
try
{
T splitConfig = Serializer.XmlSerializer.FromFile <T>(file);
config.Merge(splitConfig);
mergeResult.FilesMerged.Add(file);
}
catch (Exception ex)
{
FileMergeFailReason mergeFailReasion = new FileMergeFailReason();
mergeFailReasion.FileName = file;
mergeFailReasion.MergeFailReason = ExceptionHelper.GetMessage(ex);
mergeResult.FileMergeFailReasons.Add(mergeFailReasion);
}
}
if (!mergeResult.HasFileMerged)
{
// Logger.Error
}
else if (!mergeResult.AllFilesMerged)
{
//Logger.Warning("Framework.Configuration", "XMLConfigProvider", fileMergeResult.ToString());
}
AddConfigToCache(getParameter, config);
SetupWacher(new XmlConfigChangeWatcher(cp));
}
}
}
}
return(config);
}
public FileMergeResult MergeCMCorporateFinance(SqlConnection con, long cmId, string foldertemplate, string temporaryFolderLocation)
{
DBHelper db = new DBHelper();
List <CMData> dataResult = db.ExecToModel <CMData>(con, "dbo.Generate_Document_CM_Data_SP", CommandType.StoredProcedure, new List <SqlParameter> {
this.NewSqlParameter("@Id", SqlDbType.BigInt, cmId)
});
System.Data.DataTable listBorrower = db.ExecToDataTable(con, "Generate_Document_CM_Borrower_SP", CommandType.StoredProcedure, new List <SqlParameter> {
this.NewSqlParameter("@Id", SqlDbType.BigInt, cmId)
});
System.Data.DataTable listFacility = db.ExecToDataTable(con, "Generate_Document_CM_ProposalFacility_SP", CommandType.StoredProcedure, new List <SqlParameter> {
this.NewSqlParameter("@Id", SqlDbType.BigInt, cmId)
});
System.Data.DataTable listDealTeam = db.ExecToDataTable(con, "Generate_Document_CM_DealTeam_SP", CommandType.StoredProcedure, new List <SqlParameter> {
this.NewSqlParameter("@Id", SqlDbType.BigInt, cmId)
});
string fileName = "CM-" + dataResult[0].ProductType + "-" + dataResult[0].CompanyName + "-" + dataResult[0].ProjectCode + ".docx";
string fileNamePDF = "CM-" + dataResult[0].ProductType + "-" + dataResult[0].CompanyName + "-" + dataResult[0].ProjectCode + ".pdf";
string fileTemplateName = "CM Template - Corporate Finance.docx";
string fileTemplateFullName = foldertemplate.AppendPath("\\", fileTemplateName);
string getfileName = Path.GetFileName(fileTemplateFullName);
string destFile = Path.Combine(temporaryFolderLocation, fileName);
File.Copy(fileTemplateFullName, destFile, true);
object missing = System.Reflection.Missing.Value;
object readOnly = (object)false;
Application app = new Application();
string currFontFamily = "";
float currFontSize = 0;
try
{
Document doc = app.Documents.Open(destFile, ref missing, ref readOnly);
app.Visible = false;
try
{
#region Cover
Range reviewMemo = app.ActiveDocument.Bookmarks["bmReview"].Range;
reviewMemo.Text = dataResult[0].ReviewMemo;
reviewMemo.Font.Name = "Roboto Light";
Range cmNumber = app.ActiveDocument.Bookmarks["bmCMnumber"].Range;
//cmNumber.Text = dataResult[0].c;
cmNumber.Font.Name = "Roboto Light";
Range projCompanyName = app.ActiveDocument.Bookmarks["bmCompanyName"].Range;
projCompanyName.Text = dataResult[0].CompanyName;
projCompanyName.Font.Name = "Roboto Light";
Range projName = app.ActiveDocument.Bookmarks["bmProjectName"].Range;
projName.Text = dataResult[0].ProjectName;
projName.Font.Name = "Roboto Light";
Range projCode = app.ActiveDocument.Bookmarks["bmProjectCode"].Range;
projCode.Text = dataResult[0].ProjectCode;
projCode.Font.Name = "Roboto Light";
Range projDate = app.ActiveDocument.Bookmarks["bmProjectDate"].Range;
projDate.Text = dataResult[0].CMDate.ToString("dd-MMMM-yyyy");
projDate.Font.Name = "Roboto Light";
#endregion
#region PROJECT
Range project = app.ActiveDocument.Bookmarks["bmProject"].Range;
Table tblproject = app.ActiveDocument.Tables.Add(project, 4, 2, WdDefaultTableBehavior.wdWord9TableBehavior);
tblproject.Range.Font.Name = currFontFamily = "Roboto Light";
tblproject.Range.Font.Size = currFontSize = 10;
tblproject.set_Style("Table Grid");
tblproject.Columns[1].SetWidth(130, WdRulerStyle.wdAdjustFirstColumn);
tblproject.Cell(1, 1).Range.Text = "Project Name";
//tblproject.Cell(1, 2).Range.Text = dataResult[0].ProjectDescription.ToString();
tblproject.Cell(1, 2).Range.Text = "-";
tblproject.Cell(2, 1).Range.Text = "Sector – Sub sector";
tblproject.Cell(2, 2).Range.Text = dataResult[0].SubSector + " " + dataResult[0].SubSector;
tblproject.Cell(3, 1).Range.Text = "Funding Needs";
tblproject.Cell(3, 2).Range.InsertFile(ConvertHtmlAndFile.SaveToHtmlNew(dataResult[0].FundingNeeds, currFontFamily, currFontSize));
tblproject.Cell(4, 1).Range.Text = "Deal Strategy";
tblproject.Cell(4, 2).Range.InsertFile(ConvertHtmlAndFile.SaveToHtmlNew(dataResult[0].DealStrategy, currFontFamily, currFontSize));
#endregion
#region BORROWER
Range borrower = app.ActiveDocument.Bookmarks["bmBorrower"].Range;
Table tblborrower = app.ActiveDocument.Tables.Add(borrower, 3, 5, WdDefaultTableBehavior.wdWord9TableBehavior);
int rowCount = 3;
tblborrower.Range.Font.Name = currFontFamily = "Roboto Light";
tblborrower.Range.Font.Size = currFontSize = 10;
tblborrower.set_Style("Table Grid");
tblborrower.AutoFitBehavior(WdAutoFitBehavior.wdAutoFitWindow);
tblborrower.Columns[1].SetWidth(app.InchesToPoints(1.6f / 2.54f), WdRulerStyle.wdAdjustNone);
tblborrower.Cell(1, 1).Range.Text = "Project Company";
tblborrower.Cell(1, 2).Merge(tblborrower.Cell(1, 5));
tblborrower.Cell(2, 1).Range.Text = "Project Sponsors";
tblborrower.Cell(2, 1).Range.Borders[WdBorderType.wdBorderBottom].LineStyle = WdLineStyle.wdLineStyleNone;
tblborrower.Cell(2, 2).Range.Text = "Project Company";
tblborrower.Cell(2, 2).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphCenter;
tblborrower.Cell(2, 2).Range.Shading.BackgroundPatternColor = WdColor.wdColorGray10;
tblborrower.Cell(2, 3).Merge(tblborrower.Cell(2, 4));
tblborrower.Cell(2, 3).Range.Text = "Shareholders";
tblborrower.Cell(2, 3).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphCenter;
tblborrower.Cell(2, 3).Range.Shading.BackgroundPatternColor = WdColor.wdColorGray10;
tblborrower.Cell(2, 4).Range.Text = "% ownership";
tblborrower.Cell(2, 4).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphCenter;
tblborrower.Cell(2, 4).Range.Shading.BackgroundPatternColor = WdColor.wdColorGray10;
int rowtemp = 0;
string prevkey = "";
string cellText = "";
foreach (DataRow item in listBorrower.Rows)
{
prevkey = item[0].ToString() + "\r\a";
if (cellText != prevkey)
{
tblborrower.Rows[rowCount].Cells[2].Range.Text = item[0].ToString();
cellText = tblborrower.Rows[rowCount].Cells[2].Range.Text;
tblborrower.Cell(1, 2).Range.Text = item[0].ToString() + " ";
}
tblborrower.Rows.Add(ref missing);
tblborrower.Rows[rowCount].Cells[1].Borders[WdBorderType.wdBorderBottom].LineStyle = WdLineStyle.wdLineStyleNone;
tblborrower.Rows[rowCount].Cells[3].Range.Text = item[1].ToString();
tblborrower.Rows[rowCount].Cells[5].Range.Text = item[2].ToString();
rowCount++;
}
rowCount++;
tblborrower.Rows.Add(ref missing);
tblborrower.Rows[rowCount].Cells[1].Range.Text = "Ultimate Beneficial Owner";
tblborrower.Rows[rowCount].Cells[1].Borders[WdBorderType.wdBorderBottom].LineStyle = WdLineStyle.wdLineStyleSingle;
tblborrower.Rows[rowCount].Cells[1].Borders[WdBorderType.wdBorderTop].LineStyle = WdLineStyle.wdLineStyleSingle;
tblborrower.Rows[rowCount].Cells[2].Range.Text = dataResult[0].UltimateBeneficialOwner;
rowtemp = rowCount;
rowCount++;
tblborrower.Rows.Add(ref missing);
tblborrower.Rows[rowCount].Cells[1].Range.Text = "Rating";
tblborrower.Rows[rowCount].Cells[1].Borders[WdBorderType.wdBorderBottom].LineStyle = WdLineStyle.wdLineStyleSingle;
tblborrower.Rows[rowCount].Cells[1].Borders[WdBorderType.wdBorderTop].LineStyle = WdLineStyle.wdLineStyleSingle;
tblborrower.Rows[rowCount].Cells[2].Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphCenter;
tblborrower.Rows[rowCount].Cells[2].Range.Shading.BackgroundPatternColor = WdColor.wdColorGray10;
tblborrower.Rows[rowCount].Cells[2].Range.Text = "IIF Rating";
tblborrower.Rows[rowCount].Cells[3].Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphCenter;
tblborrower.Rows[rowCount].Cells[3].Range.Shading.BackgroundPatternColor = WdColor.wdColorGray10;
tblborrower.Rows[rowCount].Cells[3].Range.Text = "External Rating";
tblborrower.Rows[rowCount].Cells[4].Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphCenter;
tblborrower.Rows[rowCount].Cells[4].Range.Shading.BackgroundPatternColor = WdColor.wdColorGray10;
tblborrower.Rows[rowCount].Cells[4].Range.Text = "S&E Category";
tblborrower.Rows[rowCount].Cells[5].Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphCenter;
tblborrower.Rows[rowCount].Cells[5].Range.Shading.BackgroundPatternColor = WdColor.wdColorGray10;
tblborrower.Rows[rowCount].Cells[5].Range.Text = "LQC/BI Checking";
//merge column Ultimate Beneficial Owner
tblborrower.Rows[rowtemp].Cells[2].Merge(tblborrower.Rows[rowtemp].Cells[5]);
rowCount++;
tblborrower.Rows.Add(ref missing);
tblborrower.Rows[rowCount].Cells[2].Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft;
tblborrower.Rows[rowCount].Cells[2].Range.Shading.BackgroundPatternColor = WdColor.wdColorWhite;
tblborrower.Rows[rowCount].Cells[3].Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft;
tblborrower.Rows[rowCount].Cells[3].Range.Shading.BackgroundPatternColor = WdColor.wdColorWhite;
tblborrower.Rows[rowCount].Cells[4].Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft;
tblborrower.Rows[rowCount].Cells[4].Range.Shading.BackgroundPatternColor = WdColor.wdColorWhite;
tblborrower.Rows[rowCount].Cells[5].Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft;
tblborrower.Rows[rowCount].Cells[5].Range.Shading.BackgroundPatternColor = WdColor.wdColorWhite;
tblborrower.Rows[rowCount].Cells[2].Range.Text = "";
tblborrower.Rows[rowCount].Cells[3].Range.Text = "S&P: ";
tblborrower.Rows[rowCount].Cells[4].Range.Text = "";
tblborrower.Rows[rowCount].Cells[5].Range.Text = "";
tblborrower.Rows[rowCount].Cells[1].Borders[WdBorderType.wdBorderBottom].LineStyle = WdLineStyle.wdLineStyleNone;
tblborrower.Rows[rowCount].Cells[2].Borders[WdBorderType.wdBorderBottom].LineStyle = WdLineStyle.wdLineStyleNone;
tblborrower.Rows[rowCount].Cells[3].Borders[WdBorderType.wdBorderBottom].LineStyle = WdLineStyle.wdLineStyleNone;
tblborrower.Rows[rowCount].Cells[4].Borders[WdBorderType.wdBorderBottom].LineStyle = WdLineStyle.wdLineStyleNone;
tblborrower.Rows[rowCount].Cells[5].Borders[WdBorderType.wdBorderBottom].LineStyle = WdLineStyle.wdLineStyleNone;
rowCount++;
tblborrower.Rows.Add(ref missing);
tblborrower.Rows[rowCount].Cells[2].Range.Text = "Rating Date: " + Convert.ToDateTime(dataResult[0].IIFRatingDate).ToString("dd MMM yyyy");
tblborrower.Rows[rowCount].Cells[3].Range.Text = "Moodys: " + dataResult[0].MoodysRate;
tblborrower.Rows[rowCount].Cells[1].Borders[WdBorderType.wdBorderBottom].LineStyle = WdLineStyle.wdLineStyleNone;
tblborrower.Rows[rowCount].Cells[2].Borders[WdBorderType.wdBorderBottom].LineStyle = WdLineStyle.wdLineStyleNone;
tblborrower.Rows[rowCount].Cells[3].Borders[WdBorderType.wdBorderBottom].LineStyle = WdLineStyle.wdLineStyleNone;
tblborrower.Rows[rowCount].Cells[4].Borders[WdBorderType.wdBorderBottom].LineStyle = WdLineStyle.wdLineStyleNone;
tblborrower.Rows[rowCount].Cells[5].Borders[WdBorderType.wdBorderBottom].LineStyle = WdLineStyle.wdLineStyleNone;
rowCount++;
tblborrower.Rows.Add(ref missing);
tblborrower.Rows[rowCount].Cells[3].Range.Text = "Fitch: " + dataResult[0].FitchRate;
tblborrower.Rows[rowCount].Cells[1].Borders[WdBorderType.wdBorderBottom].LineStyle = WdLineStyle.wdLineStyleNone;
tblborrower.Rows[rowCount].Cells[2].Borders[WdBorderType.wdBorderBottom].LineStyle = WdLineStyle.wdLineStyleNone;
tblborrower.Rows[rowCount].Cells[3].Borders[WdBorderType.wdBorderBottom].LineStyle = WdLineStyle.wdLineStyleNone;
tblborrower.Rows[rowCount].Cells[4].Borders[WdBorderType.wdBorderBottom].LineStyle = WdLineStyle.wdLineStyleNone;
tblborrower.Rows[rowCount].Cells[5].Borders[WdBorderType.wdBorderBottom].LineStyle = WdLineStyle.wdLineStyleNone;
rowCount++;
tblborrower.Rows.Add(ref missing);
tblborrower.Rows[rowCount].Cells[3].Range.Text = "Pefindo: " + dataResult[0].PefindoRate;
rowCount++;
tblborrower.Rows.Add(ref missing);
tblborrower.Rows[rowCount].Cells[1].Range.Text = "Other information";
tblborrower.Rows[rowCount].Cells[2].Merge(tblborrower.Rows[rowCount].Cells[5]);
//tblborrower.Rows[rowCount].Cells[2].Range.Text = dataResult[0].OtherInformation;
tblborrower.Rows[rowCount].Cells[2].Range.InsertFile(ConvertHtmlAndFile.SaveToHtmlNew(dataResult[0].OtherInformation, currFontFamily, currFontSize));
tblborrower.Rows[rowCount].Borders[WdBorderType.wdBorderBottom].LineStyle = WdLineStyle.wdLineStyleSingle;
#endregion
#region FACILITY
Range proposal = app.ActiveDocument.Bookmarks["bmFacility"].Range;
Table tblProposal = app.ActiveDocument.Tables.Add(proposal, 3, 5, WdDefaultTableBehavior.wdWord9TableBehavior);
tblProposal.AutoFitBehavior(WdAutoFitBehavior.wdAutoFitWindow);
tblProposal.Range.Font.Name = "Roboto Light";
tblProposal.Range.Font.Size = 10;
tblProposal.set_Style("Table Grid");
tblProposal.Columns[1].SetWidth(app.InchesToPoints(1.8f / 2.54f), WdRulerStyle.wdAdjustNone);
int rowcount = 2;
tblProposal.Cell(1, 1).Range.Text = "Purpose";
tblProposal.Cell(1, 2).Merge(tblProposal.Cell(1, 3));
tblProposal.Cell(1, 2).Range.InsertFile(ConvertHtmlAndFile.SaveToHtml(dataResult[0].Purpose));
tblProposal.Cell(2, 1).Range.Text = "Approval Authority";
tblProposal.Cell(2, 2).Merge(tblProposal.Cell(2, 3));
tblProposal.Cell(2, 2).Range.Text = dataResult[0].ApprovalAuhority;
tblProposal.Cell(3, 1).Range.Text = "Facility";
tblProposal.Cell(3, 2).Shading.BackgroundPatternColor = WdColor.wdColorGray10;
tblProposal.Cell(3, 2).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphCenter;
tblProposal.Cell(3, 2).Range.Text = "Type";
tblProposal.Cell(3, 3).Shading.BackgroundPatternColor = WdColor.wdColorGray10;
tblProposal.Cell(3, 3).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphCenter;
tblProposal.Cell(3, 3).Range.Text = "Approved";
tblProposal.Cell(3, 4).Shading.BackgroundPatternColor = WdColor.wdColorGray10;
tblProposal.Cell(3, 4).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphCenter;
tblProposal.Cell(3, 4).Range.Text = "Proposed";
tblProposal.Cell(3, 5).Shading.BackgroundPatternColor = WdColor.wdColorGray10;
tblProposal.Cell(3, 5).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphCenter;
tblProposal.Cell(3, 5).Range.Text = "Outstanding";
foreach (DataRow item in listFacility.Rows)
{
tblProposal.Rows.Add(ref missing);
rowcount++;
tblProposal.Rows[rowcount].Cells[2].Range.Text = item[0].ToString();
tblProposal.Rows[rowcount].Cells[3].Range.Text = item[1].ToString() + " " + item[2].ToString();
tblProposal.Rows[rowcount].Cells[4].Range.Text = item[3].ToString() + " " + item[4].ToString();
tblProposal.Rows[rowcount].Cells[5].Range.Text = item[5].ToString() + " " + item[6].ToString();
}
rowcount++;
tblProposal.Rows.Add(ref missing);
tblProposal.Rows[rowcount].Cells[1].Range.Text = "Group Exposure";
tblProposal.Rows[rowcount].Cells[2].Range.Text = dataResult[0].GroupExposureCurr + dataResult[0].GroupExposureAmount;
rowcount++;
tblProposal.Rows.Add(ref missing);
tblProposal.Rows[rowcount].Cells[1].Range.Text = "Remarks";
tblProposal.Rows[rowcount].Cells[2].Range.InsertFile(ConvertHtmlAndFile.SaveToHtml(dataResult[0].Remarks));
rowcount++;
tblProposal.Rows.Add(ref missing);
tblProposal.Rows[rowcount].Cells[1].Range.Text = "Tenor";
tblProposal.Rows[rowcount].Cells[2].Range.Text = dataResult[0].TenorYear + " year(s) " + dataResult[0].TenorMonth + " month(s)";
rowcount++;
tblProposal.Rows.Add(ref missing);
tblProposal.Rows[rowcount].Cells[1].Range.Text = "Average Loan Life";
tblProposal.Rows[rowcount].Cells[2].Range.Text = dataResult[0].AverageLoanLifeYear + " year(s) " + dataResult[0].AverageLoanLifeMonth + " month(s)";
rowcount++;
tblProposal.Rows.Add(ref missing);
tblProposal.Rows[rowcount].Cells[1].Range.Text = "Pricing";
tblProposal.Rows[rowcount].Cells[2].Range.Text = "Interest rate";
tblProposal.Rows[rowcount].Cells[3].Range.InsertFile(ConvertHtmlAndFile.SaveToHtml(dataResult[0].PricingInterestRate));
rowcount++;
tblProposal.Rows.Add(ref missing);
tblProposal.Rows[rowcount].Cells[2].Range.Text = "Commitment Fee";
tblProposal.Rows[rowcount].Cells[3].Range.Text = dataResult[0].PricingCommitmentFee;
rowcount++;
tblProposal.Rows.Add(ref missing);
tblProposal.Rows[rowcount].Cells[2].Range.Text = "Upfront Fee";
tblProposal.Rows[rowcount].Cells[3].Range.Text = dataResult[0].PricingUpfrontFacilityFee;
rowcount++;
tblProposal.Rows.Add(ref missing);
tblProposal.Rows[rowcount].Cells[2].Range.Text = "Structuring Fee";
tblProposal.Rows[rowcount].Cells[3].Range.Text = dataResult[0].PricingStructuringFee;
rowcount++;
tblProposal.Rows.Add(ref missing);
tblProposal.Rows[rowcount].Cells[2].Range.Text = "Arranger Fee";
tblProposal.Rows[rowcount].Cells[3].Range.Text = dataResult[0].PricingArrangerFee;
rowcount++;
tblProposal.Rows.Add(ref missing);
tblProposal.Rows[rowcount].Cells[1].Range.Text = "Collateral";
tblProposal.Rows[rowcount].Cells[2].Range.InsertFile(ConvertHtmlAndFile.SaveToHtml(dataResult[0].PricingCollateral));
rowcount++;
tblProposal.Rows.Add(ref missing);
tblProposal.Rows[rowcount].Cells[1].Range.Text = "Other Conditions";
tblProposal.Rows[rowcount].Cells[2].Range.InsertFile(ConvertHtmlAndFile.SaveToHtml(dataResult[0].PricingOtherConditions));
rowcount++;
tblProposal.Rows.Add(ref missing);
tblProposal.Rows[rowcount].Cells[1].Range.Text = "Limit Compliance";
tblProposal.Cell(rowcount, 2).Shading.BackgroundPatternColor = WdColor.wdColorGray10;
tblProposal.Cell(rowcount, 2).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphCenter;
tblProposal.Rows[rowcount].Cells[2].Range.Text = "IDR million, as of xx1] ";
tblProposal.Cell(rowcount, 3).Shading.BackgroundPatternColor = WdColor.wdColorGray10;
tblProposal.Cell(rowcount, 3).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphCenter;
tblProposal.Rows[rowcount].Cells[3].Range.Text = "Max Limit";
tblProposal.Cell(rowcount, 4).Shading.BackgroundPatternColor = WdColor.wdColorGray10;
tblProposal.Cell(rowcount, 4).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphCenter;
tblProposal.Rows[rowcount].Cells[4].Range.Text = "Proposed";
tblProposal.Cell(rowcount, 5).Shading.BackgroundPatternColor = WdColor.wdColorGray10;
tblProposal.Cell(rowcount, 5).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphCenter;
tblProposal.Rows[rowcount].Cells[5].Range.Text = "Remarks";
rowcount++;
tblProposal.Rows.Add(ref missing);
tblProposal.Rows[rowcount].Cells[2].Range.Text = "Single Project Exposure limit";
//tblProposal.Columns[rowcount].Shading.BackgroundPatternColor = WdColor.wdColorWhite;
tblProposal.Cell(rowcount, 2).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft;
tblProposal.Rows[rowcount].Cells[3].Range.Text = Convert.ToString(dataResult[0].FacilityLimitComplianceSingleProjectExposureMaxLimit);
tblProposal.Cell(rowcount, 3).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft;
tblProposal.Rows[rowcount].Cells[4].Range.Text = Convert.ToString(dataResult[0].FacilityLimitComplianceSingleProjectExposureProposed);
tblProposal.Cell(rowcount, 4).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft;
tblProposal.Rows[rowcount].Cells[5].Range.Text = dataResult[0].SingleProjectExposureRemarks;
tblProposal.Cell(rowcount, 5).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft;
rowcount++;
tblProposal.Rows.Add(ref missing);
tblProposal.Rows[rowcount].Cells[2].Range.Text = "Product";
tblProposal.Cell(rowcount, 2).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft;
tblProposal.Rows[rowcount].Cells[3].Range.Text = Convert.ToString(dataResult[0].FacilityLimitComplianceProductMaxLimit);
tblProposal.Cell(rowcount, 3).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft;
tblProposal.Rows[rowcount].Cells[4].Range.Text = Convert.ToString(dataResult[0].FacilityLimitComplianceProductProposed);
tblProposal.Cell(rowcount, 4).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft;
tblProposal.Rows[rowcount].Cells[5].Range.Text = dataResult[0].ProductRemarks;
tblProposal.Cell(rowcount, 5).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft;
rowcount++;
tblProposal.Rows.Add(ref missing);
tblProposal.Rows[rowcount].Cells[2].Range.Text = "Risk Rating";
tblProposal.Cell(rowcount, 2).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft;
tblProposal.Rows[rowcount].Cells[3].Range.Text = Convert.ToString(dataResult[0].FacilityLimitComplianceRiskRatingMaxLimit);
tblProposal.Cell(rowcount, 3).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft;
tblProposal.Rows[rowcount].Cells[4].Range.Text = Convert.ToString(dataResult[0].FacilityLimitComplianceRiskRatingProposed);
tblProposal.Cell(rowcount, 4).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft;
tblProposal.Rows[rowcount].Cells[5].Range.Text = dataResult[0].RiskRatingRemarks;
tblProposal.Cell(rowcount, 5).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft;
rowcount++;
tblProposal.Rows.Add(ref missing);
tblProposal.Rows[rowcount].Cells[2].Range.Text = "Group Exposure Limit";
tblProposal.Cell(rowcount, 2).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft;
tblProposal.Rows[rowcount].Cells[3].Range.Text = Convert.ToString(dataResult[0].FacilityLimitComplianceGrupExposureMaxLimit);
tblProposal.Cell(rowcount, 3).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft;
tblProposal.Rows[rowcount].Cells[4].Range.Text = Convert.ToString(dataResult[0].FacilityLimitComplianceGrupExposureProposed);
tblProposal.Cell(rowcount, 4).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft;
tblProposal.Rows[rowcount].Cells[5].Range.Text = dataResult[0].GrupExposureRemarks;
tblProposal.Cell(rowcount, 5).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft;
rowcount++;
tblProposal.Rows.Add(ref missing);
tblProposal.Rows[rowcount].Cells[2].Range.Text = "Sector exposure";
tblProposal.Cell(rowcount, 2).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft;
tblProposal.Rows[rowcount].Cells[3].Range.Text = Convert.ToString(dataResult[0].FacilityLimitComplianceSectorExposureMaxLimit);
tblProposal.Cell(rowcount, 3).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft;
tblProposal.Rows[rowcount].Cells[4].Range.Text = Convert.ToString(dataResult[0].FacilityLimitComplianceSectorExposureProposed);
tblProposal.Cell(rowcount, 4).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft;
tblProposal.Rows[rowcount].Cells[5].Range.Text = dataResult[0].SectorExposureRemarks;
tblProposal.Cell(rowcount, 5).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft;
rowcount++;
tblProposal.Rows.Add(ref missing);
tblProposal.Rows[rowcount].Cells[1].Range.Text = "Exception to IIF Policy";
tblProposal.Rows[rowcount].Cells[2].Range.InsertFile(ConvertHtmlAndFile.SaveToHtml(dataResult[0].PricingExceptionToIIFPolicy));
rowcount++;
tblProposal.Rows.Add(ref missing);
tblProposal.Rows[rowcount].Cells[1].Range.Text = "Review Period";
tblProposal.Rows[rowcount].Cells[2].Range.Text = dataResult[0].ProposalReviewPeriod;
#endregion
#region D.Recommendation
Range keyInvestment = app.ActiveDocument.Bookmarks["bmRecommendation"].Range;
Paragraph paragraph = doc.Content.Paragraphs.Add(keyInvestment);
paragraph.Range.InsertFile(ConvertHtmlAndFile.SaveToHtml(dataResult[0].KeyInvestmentRecommendation));
Range recommendation = app.ActiveDocument.Bookmarks["bmRecommendation"].Range;
paragraph.Range.InsertFile(ConvertHtmlAndFile.SaveToHtml(dataResult[0].Recommendation));
Range accountResponsible = app.ActiveDocument.Bookmarks["bmRecommendation"].Range;
Table tblAccountResponsible = app.ActiveDocument.Tables.Add(accountResponsible, 1, 3, WdDefaultTableBehavior.wdWord9TableBehavior);
tblAccountResponsible.Range.Font.Name = "Roboto Light";
tblAccountResponsible.Range.Font.Size = 10;
tblAccountResponsible.set_Style("Table Grid");
tblAccountResponsible.Columns[1].SetWidth(130, WdRulerStyle.wdAdjustFirstColumn);
tblAccountResponsible.AutoFitBehavior(WdAutoFitBehavior.wdAutoFitWindow);
tblAccountResponsible.Cell(1, 1).Range.Text = "Account Responsible";
tblAccountResponsible.Cell(1, 2).Range.Text = "Deal Team";
tblAccountResponsible.Cell(1, 2).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphCenter;
tblAccountResponsible.Cell(1, 2).Range.Shading.BackgroundPatternColor = WdColor.wdColorGray10;
tblAccountResponsible.Cell(1, 3).Range.Text = "CIO";
tblAccountResponsible.Cell(1, 3).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphCenter;
tblAccountResponsible.Cell(1, 3).Range.Shading.BackgroundPatternColor = WdColor.wdColorGray10;
int rowCountDealTeam = 1;
foreach (DataRow item in listDealTeam.Rows)
{
rowCountDealTeam++;
tblAccountResponsible.Rows.Add(ref missing);
tblAccountResponsible.Rows[rowCountDealTeam].Cells[2].Shading.BackgroundPatternColor = WdColor.wdColorWhite;
tblAccountResponsible.Rows[rowCountDealTeam].Cells[2].Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft;
tblAccountResponsible.Rows[rowCountDealTeam].Cells[3].Shading.BackgroundPatternColor = WdColor.wdColorWhite;
tblAccountResponsible.Rows[rowCountDealTeam].Cells[3].Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft;
tblAccountResponsible.Rows[rowCountDealTeam].Cells[2].Range.Text = item[0].ToString();
if (rowCountDealTeam <= 2)
{
tblAccountResponsible.Rows[rowCountDealTeam].Cells[3].Range.Text = dataResult[0].AccountResponsibleCIOName;
}
}
#endregion
#region Attachment
this.FillBookmarkWithCMAttachmentType1(app, con, "bmPeriodicReview", AppConstants.TableName.CM_PeriodicReview, cmId);
this.FillBookmarkWithCMAttachmentType1(app, con, "bmPreviousApproval", AppConstants.TableName.CM_PreviousApprovals, cmId);
this.FillBookmarkWithCMAttachmentType1(app, con, "bmRiskRating", AppConstants.TableName.CM_RiskRating, cmId);
this.FillBookmarkWithCMAttachmentType1(app, con, "bmKYCchecklist", AppConstants.TableName.CM_KYCChecklists, cmId);
this.FillBookmarkWithCMAttachmentType1(app, con, "bmSAndEReview", AppConstants.TableName.CM_SAndEReview, cmId);
this.FillBookmarkWithCMAttachmentType1(app, con, "bmOtherbankFacilities", AppConstants.TableName.CM_OtherBanksFacilities, cmId);
this.FillBookmarkWithCMAttachmentType1(app, con, "bmOtherAttachment", AppConstants.TableName.CM_OtherAttachment, cmId);
#endregion
doc.PageSetup.PaperSize = WdPaperSize.wdPaperA4;
//doc.SaveAs2(Path.Combine(temporaryFolderLocation, fileNamePDF), WdExportFormat.wdExportFormatPDF);
doc.SaveAs2(Path.Combine(temporaryFolderLocation, fileName));
}
finally
{
doc.Close(WdSaveOptions.wdDoNotSaveChanges);
}
}
finally
{
app.Quit();
}
//File.Delete(destFile);
//string destFilePDF = Path.Combine(temporaryFolderLocation, fileNamePDF);
//byte[] fileContent = File.ReadAllBytes(destFilePDF);
FileMergeResult result = new FileMergeResult();
//result.FileContent = fileContent;
//result.FileName = fileNamePDF;
return(result);
}
public FileMergeResult MergePAMEquityFinance(SqlConnection con, long pamId, string foldertemplate, string temporaryFolderLocation)
{
DBHelper db = new DBHelper();
//foldertemplate = ConfigurationManager.AppSettings["PAM_TEMPLATE_FOLDER_LOCATION"];
//temporaryFolderLocation = ConfigurationManager.AppSettings["PAM_MERGE_FOLDER_LOCATION"];
List <PAMData> dataResult = db.ExecToModel <PAMData>(con, "dbo.Generate_Document_PAM_Data_SP", CommandType.StoredProcedure, new List <SqlParameter> {
this.NewSqlParameter("@Id", SqlDbType.BigInt, pamId)
});
System.Data.DataTable listBorrower = db.ExecToDataTable(con, "Generate_Document_PAM_Borrower_SP", CommandType.StoredProcedure, new List <SqlParameter> {
this.NewSqlParameter("@Id", SqlDbType.BigInt, pamId)
});
System.Data.DataTable listBorrowerCover = db.ExecToDataTable(con, "Generate_Document_PAM_Borrower_Cover_SP", CommandType.StoredProcedure, new List <SqlParameter> {
this.NewSqlParameter("@Id", SqlDbType.BigInt, pamId)
});
System.Data.DataTable listFacility = db.ExecToDataTable(con, "Generate_Document_PAM_ProposalFacility_SP", CommandType.StoredProcedure, new List <SqlParameter> {
this.NewSqlParameter("@Id", SqlDbType.BigInt, pamId)
});
System.Data.DataTable listDealTeam = db.ExecToDataTable(con, "Generate_Document_PAM_DealTeam_SP", CommandType.StoredProcedure, new List <SqlParameter> {
this.NewSqlParameter("@Id", SqlDbType.BigInt, pamId)
});
System.Data.DataTable listDocVersion = db.ExecToDataTable(con, "Generate_Document_PAM_Version_SP", CommandType.StoredProcedure, new List <SqlParameter> {
this.NewSqlParameter("@Id", SqlDbType.BigInt, pamId)
});
string fileName = "PAM-" + dataResult[0].ProductType + "-" + dataResult[0].ProjectCompanyName + "-" + dataResult[0].ProjectCode + ".docx";
string fileNamePDF = "PAM-" + dataResult[0].ProductType + "-" + dataResult[0].ProjectCompanyName + "-" + dataResult[0].ProjectCode + ".pdf";
string fileTemplateName = "PAM Equity Investment Template.docx";
string fileTemplateFullName = foldertemplate.AppendPath("\\", fileTemplateName);
string getfileName = Path.GetFileName(fileTemplateFullName);
string destFile = Path.Combine(temporaryFolderLocation, fileName);
IIFCommon.copyFromNetwork(fileTemplateFullName, destFile, foldertemplate, temporaryFolderLocation);
object missing = System.Reflection.Missing.Value;
object readOnly = (object)false;
Application app = new Application();
string currFontFamily = "Roboto Light";
float currFontSize = 10;
try
{
Document doc = app.Documents.Open(destFile, ref missing, ref readOnly);
app.Visible = false;
try
{
#region Cover
//app.ActiveDocument.Bookmarks["CompanyName"].Range.Text = dataResult[0].ProjectCompanyName;
int countBorrower = 0;
int countSetBorrower = 0;
string prevBorrower = "";
string currentBorrower = "";
List <String> lsBorrower = new List <string>();
foreach (DataRow item in listBorrowerCover.Rows)
{
countBorrower++;
prevBorrower = item[0].ToString().Trim().ToLower();
if (!lsBorrower.Contains(prevBorrower))
{
if (countBorrower > 5)
{
continue;
}
countSetBorrower++;
app.ActiveDocument.Bookmarks["CompanyName" + (countSetBorrower)].Range.Text = item[0].ToString();
currentBorrower = item[0].ToString().Trim().ToLower();
lsBorrower.Add(currentBorrower);
}
}
app.ActiveDocument.Bookmarks["ProjectName"].Range.Text = dataResult[0].ProjectName;
app.ActiveDocument.Bookmarks["ProjectCode"].Range.Text = dataResult[0].ProjectCode;
System.Globalization.CultureInfo cult = new System.Globalization.CultureInfo("en-us");
string dateToShow = string.Format(cult, "{0:dd-MMMM-yyyy}", dataResult[0].PAMDate);
app.ActiveDocument.Bookmarks["ProjectDate"].Range.Text = dateToShow;
//app.ActiveDocument.Bookmarks["FooterProjectCode"].Range.Text = dataResult[0].ProjectCode;
#endregion
#region PROJECT
app.ActiveDocument.Bookmarks["AxPROJECTxProjectName"].Range.Text = dataResult[0].ProjectName;
app.ActiveDocument.Bookmarks["AxPROJECTxSectorSubsector"].Range.Text = dataResult[0].SectorDesc + " - " + dataResult[0].SubSectorDesc;
this.FillBookmarkWithPAMAttachmentABNormal(app, con, "AxPROJECTxFundingNeeds", AppConstants.TableName.PAM_ProjectData, pamId, "FundingNeeds", "Id");
this.FillBookmarkWithPAMAttachmentABNormal(app, con, "AxPROJECTxDealStrategy", AppConstants.TableName.PAM_ProjectData, pamId, "DealStrategy", "Id");
#endregion
#region BORROWER
app.ActiveDocument.Bookmarks["BxBORROWERxInvesteeCompany"].Range.Text = dataResult[0].ProjectCompanyName;
Table tblShareholders = IIFCommon.createTable(app, "BxBORROWERxShareholders", 3, true);
//header
tblShareholders.Cell(1, 1).Range.Text = "Target Company";
tblShareholders.Cell(1, 2).Range.Text = "Shareholders";
tblShareholders.Cell(1, 3).Range.Text = "% ownership";
string prevkey = "";
string cellText = "";
int rowCounter = 1;
int rowTemp = 0;
foreach (DataRow item in listBorrower.Rows)
{
tblShareholders.Rows.Add(ref missing);
rowCounter++;
prevkey = item[0].ToString().Trim().ToLower();
if (cellText.Trim().ToLower() != prevkey.Trim().ToLower())
{
//merge kolom kalo value nya beda, mulai row ke 3
if (rowCounter > 2 && (rowTemp != (rowCounter - 1)))
{
tblShareholders.Cell(rowTemp, 1).Merge(tblShareholders.Cell(rowCounter - 1, 1));
}
rowTemp = rowCounter;
tblShareholders.Cell(rowCounter, 1).Shading.BackgroundPatternColor = WdColor.wdColorWhite;
tblShareholders.Cell(rowCounter, 1).Range.Text = item[0].ToString().Trim();
cellText = item[0].ToString().Trim().ToLower();
tblShareholders.Cell(rowCounter, 1).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft;
}
tblShareholders.Cell(rowCounter, 2).Shading.BackgroundPatternColor = WdColor.wdColorWhite;
tblShareholders.Cell(rowCounter, 2).Range.Text = item[1].ToString().Trim();
tblShareholders.Cell(rowCounter, 2).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft;
tblShareholders.Cell(rowCounter, 3).Shading.BackgroundPatternColor = WdColor.wdColorWhite;
tblShareholders.Cell(rowCounter, 3).Range.Text = item[2].ToString().Trim();
tblShareholders.Cell(rowCounter, 3).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphRight;
}
//merge kolom untuk value trakhir
if (rowCounter > 2 && (rowTemp != rowCounter))
{
tblShareholders.Cell(rowTemp, 1).Merge(tblShareholders.Cell(rowCounter, 1));
}
app.ActiveDocument.Bookmarks["BxBORROWERxUltimateBeneficialOwner"].Range.Text = dataResult[0].UltimateBeneficialOwner;
app.ActiveDocument.Bookmarks["BxBORROWERxRatingxSP"].Range.Text = dataResult[0].SAndPRate;
app.ActiveDocument.Bookmarks["BxBORROWERxRatingxMoodys"].Range.Text = dataResult[0].MoodysRate;
app.ActiveDocument.Bookmarks["BxBORROWERxRatingxFitch"].Range.Text = dataResult[0].FitchRate;
app.ActiveDocument.Bookmarks["BxBORROWERxRatingxPefindo"].Range.Text = dataResult[0].PefindoRate;
app.ActiveDocument.Bookmarks["BxBORROWERxRatingxSAndECategory"].Range.Text = dataResult[0].SAndECategoryRate + "-" + dataResult[0].SAndECategoryType;
app.ActiveDocument.Bookmarks["BxBORROWERxRatingxLQCBIChecking"].Range.Text = dataResult[0].LQCOrBICheckingRate;
this.FillBookmarkWithPAMAttachmentABNormal(app, con, "BxBORROWERxBusinessActivities", AppConstants.TableName.PAM_BorrowerOrTargetCompanyData, pamId, "BusinessActivities", "Id");
this.FillBookmarkWithPAMAttachmentABNormal(app, con, "BxBORROWERxOtherInformation", AppConstants.TableName.PAM_BorrowerOrTargetCompanyData, pamId, "OtherInformation", "Id");
#endregion
#region PROPOSAL
this.FillBookmarkWithPAMAttachmentABNormal(app, con, "CxPROPOSALxPurpose", AppConstants.TableName.PAM_ProposalData, pamId, "Purpose", "Id");
app.ActiveDocument.Bookmarks["CxPROPOSALxApprovalAuthority"].Range.Text = dataResult[0].ApprovalAuthority;
Table tblFacility = IIFCommon.createTable(app, "CxPROPOSALxInvestment", 2, true);
//header
tblFacility.Cell(1, 1).Range.Text = "Type";
tblFacility.Cell(1, 2).Range.Text = "Amount";
rowCounter = 1;
foreach (DataRow item in listFacility.Rows)
{
tblFacility.Rows.Add(ref missing);
rowCounter++;
tblFacility.Cell(rowCounter, 1).Shading.BackgroundPatternColor = WdColor.wdColorWhite;
tblFacility.Cell(rowCounter, 1).Range.Text = item[0].ToString();
tblFacility.Cell(rowCounter, 1).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft;
tblFacility.Cell(rowCounter, 2).Shading.BackgroundPatternColor = WdColor.wdColorWhite;
tblFacility.Cell(rowCounter, 2).Range.Text = item[1].ToString() + " " + item[2].ToString();
tblFacility.Cell(rowCounter, 2).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphRight;
}
tblFacility.Rows.Add(ref missing);
tblFacility.Cell(rowCounter + 1, 1).Range.Text = "Remarks : " + dataResult[0].FacilityOrInvestmentRemarks;
tblFacility.Cell(rowCounter + 1, 1).Merge(tblFacility.Cell(rowCounter + 1, 2));
tblFacility.Cell(rowCounter + 1, 1).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft;
app.ActiveDocument.Bookmarks["CxPROPOSALxGroupExposure"].Range.Text = dataResult[0].GroupExposureCurr + " " + dataResult[0].GroupExposureAmount;
this.FillBookmarkWithPAMAttachmentABNormal(app, con, "CxPROPOSALxRemarks", AppConstants.TableName.PAM_ProposalData, pamId, "Remarks", "Id");
app.ActiveDocument.Bookmarks["CxPROPOSALxExpectedHoldingPeriod"].Range.Text = dataResult[0].ExpectedHoldingPeriodYear + " Year(s) " + dataResult[0].ExpectedHoldingPeriodMonth + " Month(s)";
this.FillBookmarkWithPAMAttachmentABNormal(app, con, "CxPROPOSALxExitStrategy", AppConstants.TableName.PAM_ProposalData, pamId, "ExitStrategy", "Id");
this.FillBookmarkWithPAMAttachmentABNormal(app, con, "CxPROPOSALxExpectedReturn", AppConstants.TableName.PAM_ProposalData, pamId, "ExpectedReturn", "Id");
this.FillBookmarkWithPAMAttachmentABNormal(app, con, "CxPROPOSALxOtherCondition", AppConstants.TableName.PAM_ProposalData, pamId, "PricingOtherConditions", "Id");
this.FillBookmarkWithPAMAttachmentABNormal(app, con, "CxPROPOSALxExceptionToIIFPolicy", AppConstants.TableName.PAM_ProposalData, pamId, "PricingExceptionToIIFPolicy", "Id");
app.ActiveDocument.Bookmarks["CxPROPOSALxReviewPeriod"].Range.Text = dataResult[0].reviewPeriod;
#endregion
#region RECOMMENDATION
this.FillBookmarkWithPAMAttachmentABNormal(app, con, "DxRECOMMENDATIONxKeyInvestment", AppConstants.TableName.PAM_RecommendationData, pamId, "KeyInvestmentRecommendation", "Id");
this.FillBookmarkWithPAMAttachmentABNormal(app, con, "DxRECOMMENDATION", AppConstants.TableName.PAM_RecommendationData, pamId, "Recommendation", "Id");
Table tblDealTeam = IIFCommon.createTable(app, "DxRECOMMENDATIONxDealTeam", 1, false);
tblDealTeam.Borders.Enable = 0;
rowCounter = 0;
foreach (DataRow item in listDealTeam.Rows)
{
tblDealTeam.Rows.Add(ref missing);
rowCounter++;
tblDealTeam.Cell(rowCounter, 1).Range.Text = item[0].ToString();
tblDealTeam.Cell(rowCounter, 1).Range.Shading.BackgroundPatternColor = WdColor.wdColorWhite;
tblDealTeam.Cell(rowCounter, 1).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft;
}
app.ActiveDocument.Bookmarks["DxRECOMMENDATIONxCIO"].Range.Text = dataResult[0].AccountResponsibleCIOName;
#endregion
#region Attachment
this.FillBookmarkWithPAMAttachmentNormal(app, con, "ProjectAnalysis", AppConstants.TableName.PAM_ProjectAnalysis, pamId);
this.FillBookmarkWithPAMAttachmentNormal(app, con, "HistoricalFinancialandFinancialProject", AppConstants.TableName.PAM_HistoricalFinancial, pamId);
this.FillBookmarkWithPAMAttachmentNormal(app, con, "Supplemental", AppConstants.TableName.PAM_Supplemental, pamId);
this.FillBookmarkWithPAMAttachmentNormal(app, con, "SocialEnvironmental", AppConstants.TableName.PAM_Social, pamId);
this.FillBookmarkWithPAMAttachmentNormal(app, con, "TermSheet", AppConstants.TableName.PAM_TermSheet, pamId);
this.FillBookmarkWithPAMAttachmentNormal(app, con, "RiskRating", AppConstants.TableName.PAM_RiskRating, pamId);
this.FillBookmarkWithPAMAttachmentNormal(app, con, "KYCChecklists", AppConstants.TableName.PAM_KYCChecklists, pamId);
this.FillBookmarkWithPAMAttachmentNormal(app, con, "OtherBanksfacilities", AppConstants.TableName.PAM_OtherBanksFacilities, pamId);
this.FillBookmarkWithPAMAttachmentNormal(app, con, "ShareValuationReport", AppConstants.TableName.PAM_ShareValuationReport, pamId);
System.Data.DataTable listLegalDue = db.ExecToDataTable(con, "Generate_Document_PAM_LegalDue_SP", CommandType.StoredProcedure, new List <SqlParameter> {
this.NewSqlParameter("@Id", SqlDbType.BigInt, pamId)
});
IIFCommon.createLegalSAndEDueOtherReportTable(app, listLegalDue, "LegalDuediligenceReportAttachment", "LegalDuediligenceReportDescription", currFontFamily, currFontSize);
System.Data.DataTable listSAndDue = db.ExecToDataTable(con, "Generate_Document_PAM_SAndDue_SP", CommandType.StoredProcedure, new List <SqlParameter> {
this.NewSqlParameter("@Id", SqlDbType.BigInt, pamId)
});
IIFCommon.createLegalSAndEDueOtherReportTable(app, listSAndDue, "SAndDuediligenceReportAttachment", "SAndDuediligenceReportDescription", currFontFamily, currFontSize);
System.Data.DataTable listOtherReport = db.ExecToDataTable(con, "Generate_Document_PAM_OtherReport_SP", CommandType.StoredProcedure, new List <SqlParameter> {
this.NewSqlParameter("@Id", SqlDbType.BigInt, pamId)
});
IIFCommon.createLegalSAndEDueOtherReportTable(app, listOtherReport, "OtherReportAttachment", "OtherReportDescription", currFontFamily, currFontSize);
#endregion
IIFCommon.finalizeDoc(doc);
IIFCommon.injectFooterPAM(doc, dataResult[0].ProjectCode);
bool isPreview = false;
try
{
if (dataResult[0].MWorkflowStatusId != null && dataResult[0].MWorkflowStatusId == 7)
{
isPreview = true;
}
}
catch { }
fileNamePDF = IIFCommon.fileNameFormat(listDocVersion, fileNamePDF, isPreview);
doc.SaveAs2(Path.Combine(temporaryFolderLocation, fileNamePDF), WdExportFormat.wdExportFormatPDF);
//doc.SaveAs2(Path.Combine(temporaryFolderLocation, fileName));
}
catch (Exception ex)
{
throw ex;
}
finally
{
doc.Close(WdSaveOptions.wdDoNotSaveChanges);
}
}
finally
{
app.Quit();
}
File.Delete(destFile);
string destFilePDF = Path.Combine(temporaryFolderLocation, fileNamePDF);
byte[] fileContent = File.ReadAllBytes(destFilePDF);
FileMergeResult result = new FileMergeResult();
result.FileContent = fileContent;
result.FileName = fileNamePDF;
return(result);
}
public FileMergeResult MergePAMCorporateFinance(SqlConnection con, long pamId, string foldertemplate, string temporaryFolderLocation)
{
DBHelper db = new DBHelper();
List <PAMData> dataResult = db.ExecToModel <PAMData>(con, "dbo.Generate_Document_PAM_Data_SP", CommandType.StoredProcedure, new List <SqlParameter> {
this.NewSqlParameter("@Id", SqlDbType.BigInt, pamId)
});
System.Data.DataTable listBorrower = db.ExecToDataTable(con, "Generate_Document_PAM_Borrower_SP", CommandType.StoredProcedure, new List <SqlParameter> {
this.NewSqlParameter("@Id", SqlDbType.BigInt, pamId)
});
System.Data.DataTable listFacility = db.ExecToDataTable(con, "Generate_Document_PAM_ProposalFacility_SP", CommandType.StoredProcedure, new List <SqlParameter> {
this.NewSqlParameter("@Id", SqlDbType.BigInt, pamId)
});
System.Data.DataTable listDealTeam = db.ExecToDataTable(con, "Generate_Document_PAM_DealTeam_SP", CommandType.StoredProcedure, new List <SqlParameter> {
this.NewSqlParameter("@Id", SqlDbType.BigInt, pamId)
});
string fileName = "PAM-" + dataResult[0].ProductType + "-" + dataResult[0].ProjectCompanyName + "-" + dataResult[0].ProjectCode + ".docx";
string fileNamePDF = "PAM-" + dataResult[0].ProductType + "-" + dataResult[0].ProjectCompanyName + "-" + dataResult[0].ProjectCode + ".pdf";
string fileTemplateName = "PAM Template - Corporate Finance.docx";
string fileTemplateFullName = foldertemplate.AppendPath("\\", fileTemplateName);
string getfileName = Path.GetFileName(fileTemplateFullName);
string destFile = Path.Combine(temporaryFolderLocation, fileName);
File.Copy(fileTemplateFullName, destFile, true);
object missing = System.Reflection.Missing.Value;
object readOnly = (object)false;
Application app = new Application();
try
{
Document doc = app.Documents.Open(destFile, ref missing, ref readOnly);
app.Visible = false;
try
{
#region header & Footer
//doc.ActiveWindow.ActivePane.View.SeekView = WdSeekView.wdSeekCurrentPageFooter;
//Object oMissing = System.Reflection.Missing.Value;
//doc.ActiveWindow.Selection.TypeText("Review Memorandum – Project Finance");
//Object TotalPages = WdFieldType.wdFieldNumPages;
//Object CurrentPage = WdFieldType.wdFieldPage;
//doc.ActiveWindow.Selection.TypeText("\t\t");
//doc.ActiveWindow.Selection.Fields.Add(doc.ActiveWindow.Selection.Range, ref CurrentPage, ref oMissing, ref oMissing);
#endregion
#region Cover
this.SetBookmarkText(app, "BorrowerName", dataResult[0].ProjectCompanyName);
this.SetBookmarkText(app, "ProjectName", dataResult[0].ProjectName);
this.SetBookmarkText(app, "ProjectCode", dataResult[0].ProjectCode);
this.SetBookmarkText(app, "ProjectDate", dataResult[0].PAMDate.ToString("dd-MMMM-yyyy"));
#endregion
#region A.Project
Range project = app.ActiveDocument.Bookmarks["ExecutiveSummary"].Range;
Table tblproject = app.ActiveDocument.Tables.Add(project, 4, 2, WdDefaultTableBehavior.wdWord9TableBehavior);
tblproject.Range.Font.Name = "Roboto Light";
tblproject.Range.Font.Size = 10;
tblproject.set_Style("Table Grid");
tblproject.Columns[1].SetWidth(120, WdRulerStyle.wdAdjustFirstColumn);
tblproject.Cell(1, 1).Range.Text = "Borrower Name";
tblproject.Cell(1, 2).Range.Text = dataResult[0].ProjectCompanyName.ToString();
tblproject.Cell(2, 1).Range.Text = "Sector – Sub sector";
tblproject.Cell(2, 2).Range.Text = dataResult[0].Sector + " - " + dataResult[0].SubSector;
tblproject.Cell(3, 1).Range.Text = "Funding Needs";
Logger.Error("yefta:" + dataResult[0].FundingNeeds);
tblproject.Cell(3, 2).Range.InsertFile(ConvertHtmlAndFile.SaveToHtml(dataResult[0].FundingNeeds));
tblproject.Cell(4, 1).Range.Text = "Deal Strategy";
tblproject.Cell(4, 2).Range.InsertFile(ConvertHtmlAndFile.SaveToHtml(dataResult[0].DealStrategy));
#endregion
#region B.Borrower
Range borrower = app.ActiveDocument.Bookmarks["Borrower"].Range;
Table tblborrower = app.ActiveDocument.Tables.Add(borrower, 2, 5, WdDefaultTableBehavior.wdWord9TableBehavior);
tblborrower.Range.Font.Name = "Roboto Light";
tblborrower.Range.Font.Size = 10;
tblborrower.set_Style("Table Grid");
tblborrower.Columns[1].SetWidth(100, WdRulerStyle.wdAdjustFirstColumn);
tblborrower.Cell(1, 1).Range.Text = "Borrower Company";
string prevkey = "";
string cellText = "";
foreach (DataRow items in listBorrower.Rows)
{
prevkey = items[0].ToString() + "\r\a";
if (cellText != prevkey)
{
tblborrower.Cell(1, 2).Range.Text = items[0].ToString();
cellText = tblborrower.Cell(1, 2).Range.Text;
}
}
tblborrower.Cell(2, 1).Range.Text = "Shareholders";
tblborrower.Cell(2, 1).Range.Borders[WdBorderType.wdBorderBottom].LineStyle = WdLineStyle.wdLineStyleNone;
tblborrower.Cell(2, 2).SetWidth(140, WdRulerStyle.wdAdjustFirstColumn);
tblborrower.Cell(2, 2).Range.Text = "Borrower(s)";
tblborrower.Cell(2, 2).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphCenter;
tblborrower.Cell(2, 2).Range.Shading.BackgroundPatternColor = WdColor.wdColorGray10;
tblborrower.Cell(2, 3).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphCenter;
tblborrower.Cell(2, 3).Range.Shading.BackgroundPatternColor = WdColor.wdColorGray10;
tblborrower.Cell(2, 3).Range.Text = "Shareholders";
tblborrower.Cell(2, 5).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphCenter;
tblborrower.Cell(2, 5).Range.Shading.BackgroundPatternColor = WdColor.wdColorGray10;
tblborrower.Cell(2, 5).Range.Text = "% ownership";
string prevkey1 = "";
string cellText1 = "";
int rowCount = 2;
foreach (DataRow item in listBorrower.Rows)
{
rowCount++;
tblborrower.Rows.Add(ref missing);
prevkey1 = item[0].ToString() + "\r\a";
if (cellText1 != prevkey1)
{
tblborrower.Rows[rowCount].Cells[2].Range.Text = item[0].ToString();
tblborrower.Rows[rowCount].Cells[2].Range.Shading.BackgroundPatternColor = WdColor.wdColorWhite;
tblborrower.Rows[rowCount].Cells[3].Range.Shading.BackgroundPatternColor = WdColor.wdColorWhite;
tblborrower.Rows[rowCount].Cells[5].Range.Shading.BackgroundPatternColor = WdColor.wdColorWhite;
cellText1 = tblborrower.Rows[rowCount].Cells[2].Range.Text;
tblborrower.Cell(1, 2).Range.Text = item[0].ToString() + " ";
tblborrower.Cell(1, 2).Range.Font.Name = "Roboto Light";
tblborrower.Cell(1, 2).Range.Font.Size = 10;
}
tblborrower.Rows[rowCount].Cells[1].Borders[WdBorderType.wdBorderTop].LineStyle = WdLineStyle.wdLineStyleNone;
tblborrower.Rows[rowCount].Cells[1].Borders[WdBorderType.wdBorderBottom].LineStyle = WdLineStyle.wdLineStyleNone;
tblborrower.Rows[rowCount].Cells[3].Range.Text = item[1].ToString();
tblborrower.Rows[rowCount].Cells[3].Range.Font.Name = "Roboto Light";
tblborrower.Rows[rowCount].Cells[3].Range.Font.Size = 10;
var Ownership = Convert.ToDecimal(item[2]);
tblborrower.Rows[rowCount].Cells[5].Range.Text = Ownership.ToString("#,#");
tblborrower.Rows[rowCount].Cells[5].Range.Font.Name = "Roboto Light";
tblborrower.Rows[rowCount].Cells[5].Range.Font.Size = 10;
}
rowCount++;
tblborrower.Rows.Add(ref missing);
tblborrower.Rows[rowCount].Cells[1].Range.Text = "Ultimate Beneficial Owner";
tblborrower.Rows[rowCount].Cells[1].Borders[WdBorderType.wdBorderBottom].LineStyle = WdLineStyle.wdLineStyleSingle;
tblborrower.Rows[rowCount].Cells[1].Borders[WdBorderType.wdBorderTop].LineStyle = WdLineStyle.wdLineStyleSingle;
tblborrower.Rows[rowCount].Cells[2].Range.Text = dataResult[0].UltimateBeneficialOwner;
tblborrower.Rows[rowCount].Cells[2].Range.Font.Name = "Roboto Light";
int rowtemp = rowCount;
rowCount++;
tblborrower.Rows.Add(ref missing);
tblborrower.Rows[rowCount].Range.Font.Name = "Roboto Light";
tblborrower.Rows[rowCount].Cells[1].Range.Text = "Rating";
tblborrower.Rows[rowCount].Cells[1].Borders[WdBorderType.wdBorderBottom].LineStyle = WdLineStyle.wdLineStyleNone;
tblborrower.Rows[rowCount].Cells[2].Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphCenter;
tblborrower.Rows[rowCount].Cells[2].Range.Shading.BackgroundPatternColor = WdColor.wdColorGray10;
tblborrower.Rows[rowCount].Cells[2].Range.Text = "IIF Rating";
tblborrower.Rows[rowCount].Cells[3].Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphCenter;
tblborrower.Rows[rowCount].Cells[3].Range.Shading.BackgroundPatternColor = WdColor.wdColorGray10;
tblborrower.Rows[rowCount].Cells[3].Range.Text = "External Rating";
tblborrower.Rows[rowCount].Cells[4].Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphCenter;
tblborrower.Rows[rowCount].Cells[4].Range.Shading.BackgroundPatternColor = WdColor.wdColorGray10;
tblborrower.Rows[rowCount].Cells[4].Range.Text = "S&E Category";
tblborrower.Rows[rowCount].Cells[5].Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphCenter;
tblborrower.Rows[rowCount].Cells[5].Range.Shading.BackgroundPatternColor = WdColor.wdColorGray10;
tblborrower.Rows[rowCount].Cells[5].Range.Text = "LQC/BI Checking";
rowCount++;
tblborrower.Rows.Add(ref missing);
tblborrower.Rows[rowCount].Cells[1].Borders[WdBorderType.wdBorderTop].LineStyle = WdLineStyle.wdLineStyleNone;
tblborrower.Rows[rowCount].Cells[2].Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft;
tblborrower.Rows[rowCount].Cells[2].Range.Shading.BackgroundPatternColor = WdColor.wdColorWhite;
tblborrower.Rows[rowCount].Cells[3].Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft;
tblborrower.Rows[rowCount].Cells[3].Range.Shading.BackgroundPatternColor = WdColor.wdColorWhite;
tblborrower.Rows[rowCount].Cells[4].Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft;
tblborrower.Rows[rowCount].Cells[4].Range.Shading.BackgroundPatternColor = WdColor.wdColorWhite;
tblborrower.Rows[rowCount].Cells[5].Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft;
tblborrower.Rows[rowCount].Cells[5].Range.Shading.BackgroundPatternColor = WdColor.wdColorWhite;
tblborrower.Rows[rowCount].Cells[2].Range.Text = dataResult[0].IIFRate;
tblborrower.Rows[rowCount].Cells[3].Range.Text = "S&P: " + dataResult[0].SAndPRate;
tblborrower.Rows[rowCount].Cells[3].Range.Font.Name = "Roboto Light";
tblborrower.Rows[rowCount].Cells[4].Range.Text = dataResult[0].SAndECategoryRate;
tblborrower.Rows[rowCount].Cells[5].Range.Text = dataResult[0].LQCOrBICheckingRate;;
tblborrower.Rows[rowCount].Cells[1].Borders[WdBorderType.wdBorderBottom].LineStyle = WdLineStyle.wdLineStyleNone;
tblborrower.Rows[rowCount].Cells[2].Borders[WdBorderType.wdBorderBottom].LineStyle = WdLineStyle.wdLineStyleNone;
tblborrower.Rows[rowCount].Cells[3].Borders[WdBorderType.wdBorderBottom].LineStyle = WdLineStyle.wdLineStyleNone;
tblborrower.Rows[rowCount].Cells[4].Borders[WdBorderType.wdBorderBottom].LineStyle = WdLineStyle.wdLineStyleNone;
tblborrower.Rows[rowCount].Cells[5].Borders[WdBorderType.wdBorderBottom].LineStyle = WdLineStyle.wdLineStyleNone;
rowCount++;
tblborrower.Rows.Add(ref missing);
tblborrower.Rows[rowCount].Cells[1].Borders[WdBorderType.wdBorderTop].LineStyle = WdLineStyle.wdLineStyleNone;
tblborrower.Rows[rowCount].Cells[2].Borders[WdBorderType.wdBorderTop].LineStyle = WdLineStyle.wdLineStyleNone;
tblborrower.Rows[rowCount].Cells[3].Borders[WdBorderType.wdBorderTop].LineStyle = WdLineStyle.wdLineStyleNone;
tblborrower.Rows[rowCount].Cells[4].Borders[WdBorderType.wdBorderTop].LineStyle = WdLineStyle.wdLineStyleNone;
tblborrower.Rows[rowCount].Cells[5].Borders[WdBorderType.wdBorderTop].LineStyle = WdLineStyle.wdLineStyleNone;
tblborrower.Rows[rowCount].Cells[2].Range.Text = "Rating Date: " + dataResult[0].IIFRatingDate.Value.ToString("dd-MMM-yyyy");
tblborrower.Rows[rowCount].Cells[2].Range.Font.Name = "Roboto Light";
tblborrower.Rows[rowCount].Cells[3].Range.Text = "Moodys: " + dataResult[0].MoodysRate;
tblborrower.Rows[rowCount].Cells[3].Range.Font.Name = "Roboto Light";
rowCount++;
tblborrower.Rows.Add(ref missing);
tblborrower.Rows[rowCount].Cells[3].Range.Text = "Fitch: " + dataResult[0].FitchRate;
tblborrower.Rows[rowCount].Cells[3].Range.Font.Name = "Roboto Light";
rowCount++;
tblborrower.Rows.Add(ref missing);
tblborrower.Rows[rowCount].Cells[3].Range.Text = "Pefindo: " + dataResult[0].PefindoRate;
tblborrower.Rows[rowCount].Cells[3].Range.Font.Name = "Roboto Light";
rowCount++;
int countBusinessActivities = rowCount;
tblborrower.Rows.Add(ref missing);
tblborrower.Rows[rowCount].Cells[1].Range.Text = "Business Activities";
tblborrower.Rows[rowCount].Cells[2].Range.InsertFile(ConvertHtmlAndFile.SaveToHtml(dataResult[0].BusinessActivities));
tblborrower.Rows[rowCount].Borders[WdBorderType.wdBorderBottom].LineStyle = WdLineStyle.wdLineStyleSingle;
tblborrower.Rows[rowCount].Borders[WdBorderType.wdBorderTop].LineStyle = WdLineStyle.wdLineStyleSingle;
tblborrower.Rows[rowCount].Range.Font.Name = "Roboto Light";
rowCount++;
tblborrower.Rows.Add(ref missing);
int countOtherinfo = rowCount;
tblborrower.Rows[rowCount].Cells[1].Range.Text = "Other information";
tblborrower.Rows[rowCount].Cells[2].Range.InsertFile(ConvertHtmlAndFile.SaveToHtml(dataResult[0].OtherInformation));
tblborrower.Rows[rowCount].Borders[WdBorderType.wdBorderBottom].LineStyle = WdLineStyle.wdLineStyleSingle;
tblborrower.Rows[rowCount].Range.Font.Name = "Roboto Light";
tblborrower.Cell(1, 2).Merge(tblborrower.Cell(1, 5));
tblborrower.Cell(2, 3).Merge(tblborrower.Cell(2, 4));
tblborrower.Cell(2, 3).SetWidth(130, WdRulerStyle.wdAdjustSameWidth);
for (int a = 1; a <= listBorrower.Rows.Count; a++)
{
tblborrower.Cell(2 + a, 3).Merge(tblborrower.Cell(2 + a, 4));
tblborrower.Cell(2 + a, 3).SetWidth(130, WdRulerStyle.wdAdjustSameWidth);
}
tblborrower.Rows[countOtherinfo].Cells[2].Merge(tblborrower.Rows[countOtherinfo].Cells[5]);
tblborrower.Rows[countBusinessActivities].Cells[2].Merge(tblborrower.Rows[countBusinessActivities].Cells[5]);
tblborrower.Rows[rowtemp].Cells[2].Merge(tblborrower.Rows[rowtemp].Cells[5]);
#endregion
#region C.Proposal
Range proposal = app.ActiveDocument.Bookmarks["Proposal"].Range;
Table tblProposal = app.ActiveDocument.Tables.Add(proposal, 3, 3, WdDefaultTableBehavior.wdWord9TableBehavior);
tblProposal.Range.Font.Name = "Roboto Light";
tblProposal.Range.Font.Size = 10;
tblProposal.set_Style("Table Grid");
tblProposal.Columns[1].SetWidth(130, WdRulerStyle.wdAdjustFirstColumn);
tblProposal.AutoFitBehavior(WdAutoFitBehavior.wdAutoFitWindow);
tblProposal.Cell(1, 1).Range.Text = "Purpose";
tblProposal.Cell(1, 2).Range.InsertFile(ConvertHtmlAndFile.SaveToHtml(dataResult[0].Purpose));
tblProposal.Cell(1, 2).Merge(tblProposal.Cell(1, 3));
tblProposal.Cell(1, 2).Range.Font.Name = "Roboto Light";
tblProposal.Cell(1, 2).Range.Font.Size = 10;
tblProposal.Cell(2, 1).Range.Text = "Approval Authority";
tblProposal.Cell(2, 2).Merge(tblProposal.Cell(2, 3));
tblProposal.Cell(2, 2).Range.Text = dataResult[0].ApprovalAuthority;
tblProposal.Cell(2, 2).Range.Font.Name = "Roboto Light";
tblProposal.Cell(3, 1).Range.Text = "Facility";
tblProposal.Cell(3, 1).Borders[WdBorderType.wdBorderBottom].LineStyle = WdLineStyle.wdLineStyleNone;
tblProposal.Cell(3, 2).Shading.BackgroundPatternColor = WdColor.wdColorGray10;
tblProposal.Cell(3, 2).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphCenter;
tblProposal.Cell(3, 2).Range.Text = "Type";
tblProposal.Cell(3, 3).Shading.BackgroundPatternColor = WdColor.wdColorGray10;
tblProposal.Cell(3, 3).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphCenter;
tblProposal.Cell(3, 3).Range.Text = "Amount";
int rowcounttblProposal = 3;
foreach (DataRow item in listFacility.Rows)
{
rowcounttblProposal++;
tblProposal.Rows.Add(ref missing);
tblProposal.Rows[rowcounttblProposal].Cells[1].Borders[WdBorderType.wdBorderTop].LineStyle = WdLineStyle.wdLineStyleNone;
tblProposal.Rows[rowcounttblProposal].Cells[2].Shading.BackgroundPatternColor = WdColor.wdColorWhite;
tblProposal.Rows[rowcounttblProposal].Cells[2].Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft;
tblProposal.Rows[rowcounttblProposal].Cells[3].Shading.BackgroundPatternColor = WdColor.wdColorWhite;
tblProposal.Rows[rowcounttblProposal].Cells[3].Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft;
tblProposal.Rows[rowcounttblProposal].Cells[2].Range.Text = item[0].ToString();
tblProposal.Rows[rowcounttblProposal].Cells[2].Range.Font.Name = "Roboto Light";
tblProposal.Rows[rowcounttblProposal].Cells[2].Range.Font.Size = 10;
var amout = Convert.ToDecimal(item[2]);
tblProposal.Rows[rowcounttblProposal].Cells[3].Range.Text = item[1].ToString() + " " + amout.ToString("#,#");
tblProposal.Rows[rowcounttblProposal].Cells[3].Range.Font.Name = "Roboto Light";
tblProposal.Rows[rowcounttblProposal].Cells[3].Range.Font.Size = 10;
}
rowcounttblProposal++;
tblProposal.Rows.Add(ref missing);
tblProposal.Rows[rowcounttblProposal].Cells[2].Shading.BackgroundPatternColor = WdColor.wdColorWhite;
tblProposal.Rows[rowcounttblProposal].Cells[2].Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft;
tblProposal.Rows[rowcounttblProposal].Cells[3].Shading.BackgroundPatternColor = WdColor.wdColorWhite;
tblProposal.Rows[rowcounttblProposal].Cells[3].Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft;
tblProposal.Rows[rowcounttblProposal].Cells[1].Borders[WdBorderType.wdBorderTop].LineStyle = WdLineStyle.wdLineStyleSingle;
tblProposal.Rows[rowcounttblProposal].Cells[1].Range.Text = "Group Exposure";
tblProposal.Rows[rowcounttblProposal].Cells[2].Range.Text = dataResult[0].GroupExposureCurr + " " + dataResult[0].GroupExposureAmount;
tblProposal.Rows[rowcounttblProposal].Cells[2].Range.Font.Name = "Roboto Light";
int GroupExpoCount = rowcounttblProposal;
rowcounttblProposal++;
tblProposal.Rows.Add(ref missing);
tblProposal.Rows[rowcounttblProposal].Cells[1].Range.Text = "Remarks";
tblProposal.Rows[rowcounttblProposal].Cells[2].Range.InsertFile(ConvertHtmlAndFile.SaveToHtml(dataResult[0].Remarks));
tblProposal.Rows[rowcounttblProposal].Cells[2].Range.Font.Name = "Roboto Light";
tblProposal.Rows[rowcounttblProposal].Cells[2].Range.Font.Size = 10;
int RemarksCount = rowcounttblProposal;
rowcounttblProposal++;
tblProposal.Rows.Add(ref missing);
tblProposal.Rows[rowcounttblProposal].Cells[1].Range.Text = "Tenor";
tblProposal.Rows[rowcounttblProposal].Cells[2].Range.Text = dataResult[0].tenorYear + " year(s) " + dataResult[0].tenorMonth + " month(s)";
tblProposal.Rows[rowcounttblProposal].Cells[2].Range.Font.Name = "Roboto Light";
int tenorCount = rowcounttblProposal;
rowcounttblProposal++;
tblProposal.Rows.Add(ref missing);
tblProposal.Rows[rowcounttblProposal].Cells[1].Range.Text = "Average Loan Life";
tblProposal.Rows[rowcounttblProposal].Cells[2].Range.Text = dataResult[0].averageLoanLifeYear + " year(s) " + dataResult[0].averageLoanLifeMonth + " month(s)";
tblProposal.Rows[rowcounttblProposal].Cells[2].Range.Font.Name = "Roboto Light";
int AverageCount = rowcounttblProposal;
rowcounttblProposal++;
tblProposal.Rows.Add(ref missing);
tblProposal.Rows[rowcounttblProposal].Cells[1].Range.Text = "Pricing";
tblProposal.Rows[rowcounttblProposal].Cells[1].Borders[WdBorderType.wdBorderBottom].LineStyle = WdLineStyle.wdLineStyleNone;
tblProposal.Rows[rowcounttblProposal].Cells[2].Range.Text = "Interest rate";
tblProposal.Rows[rowcounttblProposal].Cells[3].Range.InsertFile(ConvertHtmlAndFile.SaveToHtml(dataResult[0].pricingInterestRate));
tblProposal.Rows[rowcounttblProposal].Cells[3].Range.Font.Name = "Roboto Light";
tblProposal.Rows[rowcounttblProposal].Cells[3].Range.Font.Size = 10;
rowcounttblProposal++;
tblProposal.Rows.Add(ref missing);
tblProposal.Rows[rowcounttblProposal].Cells[1].Borders[WdBorderType.wdBorderTop].LineStyle = WdLineStyle.wdLineStyleNone;
tblProposal.Rows[rowcounttblProposal].Cells[2].Range.Text = "Commitment Fee";
tblProposal.Rows[rowcounttblProposal].Cells[3].Range.Text = dataResult[0].pricingCommitmentFee;
tblProposal.Rows[rowcounttblProposal].Cells[3].Range.Font.Name = "Roboto Light";
rowcounttblProposal++;
tblProposal.Rows.Add(ref missing);
tblProposal.Rows[rowcounttblProposal].Cells[2].Range.Text = "Upfront Fee";
tblProposal.Rows[rowcounttblProposal].Cells[3].Range.Text = dataResult[0].pricingUpfrontFacilityFee;
tblProposal.Rows[rowcounttblProposal].Cells[3].Range.Font.Name = "Roboto Light";
rowcounttblProposal++;
tblProposal.Rows.Add(ref missing);
tblProposal.Rows[rowcounttblProposal].Cells[2].Range.Text = "Structuring Fee";
tblProposal.Rows[rowcounttblProposal].Cells[3].Range.Text = dataResult[0].pricingStructuringFee;
tblProposal.Rows[rowcounttblProposal].Cells[3].Range.Font.Name = "Roboto Light";
rowcounttblProposal++;
tblProposal.Rows.Add(ref missing);
tblProposal.Rows[rowcounttblProposal].Cells[2].Range.Text = "Arranger Fee";
tblProposal.Rows[rowcounttblProposal].Cells[3].Range.Text = dataResult[0].pricingArrangerFee;
tblProposal.Rows[rowcounttblProposal].Cells[3].Range.Font.Name = "Roboto Light";
rowcounttblProposal++;
tblProposal.Rows.Add(ref missing);
tblProposal.Rows[rowcounttblProposal].Cells[1].Borders[WdBorderType.wdBorderTop].LineStyle = WdLineStyle.wdLineStyleSingle;
tblProposal.Rows[rowcounttblProposal].Cells[1].Range.Text = "Collateral";
tblProposal.Rows[rowcounttblProposal].Cells[2].Range.InsertFile(ConvertHtmlAndFile.SaveToHtml(dataResult[0].pricingCollateral));
tblProposal.Cell(rowcounttblProposal, 2).Merge(tblProposal.Cell(rowcounttblProposal, 3));
tblProposal.Rows[rowcounttblProposal].Cells[2].Range.Font.Name = "Roboto Light";
tblProposal.Rows[rowcounttblProposal].Cells[2].Range.Font.Size = 10;
rowcounttblProposal++;
tblProposal.Rows.Add(ref missing);
tblProposal.Rows[rowcounttblProposal].Cells[1].Range.Text = "Other Conditions";
tblProposal.Rows[rowcounttblProposal].Cells[2].Range.InsertFile(ConvertHtmlAndFile.SaveToHtml(dataResult[0].pricingOtherConditions));
tblProposal.Rows[rowcounttblProposal].Cells[2].Range.Font.Name = "Roboto Light";
tblProposal.Rows[rowcounttblProposal].Cells[2].Range.Font.Size = 10;
rowcounttblProposal++;
tblProposal.Rows.Add(ref missing);
tblProposal.Rows[rowcounttblProposal].Cells[1].Range.Text = "Exception to IIF Policy";
tblProposal.Rows[rowcounttblProposal].Cells[2].Range.InsertFile(ConvertHtmlAndFile.SaveToHtml(dataResult[0].pricingExceptionToIIFPolicy));
tblProposal.Rows[rowcounttblProposal].Cells[2].Range.Font.Name = "Roboto Light";
tblProposal.Rows[rowcounttblProposal].Cells[2].Range.Font.Size = 10;
rowcounttblProposal++;
tblProposal.Rows.Add(ref missing);
tblProposal.Rows[rowcounttblProposal].Cells[1].Range.Text = "Review Period";
tblProposal.Rows[rowcounttblProposal].Cells[2].Range.Text = dataResult[0].reviewPeriod;
tblProposal.Rows[rowcounttblProposal].Cells[2].Range.Font.Name = "Roboto Light";
tblProposal.Cell(GroupExpoCount, 2).Merge(tblProposal.Cell(GroupExpoCount, 3));
tblProposal.Cell(RemarksCount, 2).Merge(tblProposal.Cell(RemarksCount, 3));
tblProposal.Cell(tenorCount, 2).Merge(tblProposal.Cell(tenorCount, 3));
tblProposal.Cell(AverageCount, 2).Merge(tblProposal.Cell(AverageCount, 3));
#endregion
#region D.Recommendation
Range keyInvestment = app.ActiveDocument.Bookmarks["KeyInvestment"].Range;
Paragraph paragkeyInvestment = doc.Content.Paragraphs.Add(keyInvestment);
paragkeyInvestment.Range.InsertFile(ConvertHtmlAndFile.SaveToHtml(dataResult[0].KeyInvestmentRecommendation));
paragkeyInvestment.Range.Font.Name = "Roboto Light";
paragkeyInvestment.Range.Font.Size = 10;
Range recommendation = app.ActiveDocument.Bookmarks["Recommendation"].Range;
Paragraph paragRecommendation = doc.Content.Paragraphs.Add(recommendation);
paragRecommendation.Range.InsertFile(ConvertHtmlAndFile.SaveToHtml(dataResult[0].Recommendation));
paragRecommendation.Range.Font.Name = "Roboto Light";
paragRecommendation.Range.Font.Size = 10;
Range accountResponsible = app.ActiveDocument.Bookmarks["AccountRespon"].Range;
Table tblAccountResponsible = app.ActiveDocument.Tables.Add(accountResponsible, 1, 3, WdDefaultTableBehavior.wdWord9TableBehavior);
tblAccountResponsible.Range.Font.Name = "Roboto Light";
tblAccountResponsible.Range.Font.Size = 10;
tblAccountResponsible.set_Style("Table Grid");
tblAccountResponsible.Columns[1].SetWidth(130, WdRulerStyle.wdAdjustFirstColumn);
tblAccountResponsible.AutoFitBehavior(WdAutoFitBehavior.wdAutoFitWindow);
tblAccountResponsible.Cell(1, 1).Range.Text = "Account Responsible";
tblAccountResponsible.Cell(1, 2).Range.Text = "Deal Team";
tblAccountResponsible.Cell(1, 2).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphCenter;
tblAccountResponsible.Cell(1, 2).Range.Shading.BackgroundPatternColor = WdColor.wdColorGray10;
tblAccountResponsible.Cell(1, 3).Range.Text = "CIO";
tblAccountResponsible.Cell(1, 3).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphCenter;
tblAccountResponsible.Cell(1, 3).Range.Shading.BackgroundPatternColor = WdColor.wdColorGray10;
int rowCountDealTeam = 1;
foreach (DataRow item in listDealTeam.Rows)
{
rowCountDealTeam++;
tblAccountResponsible.Rows.Add(ref missing);
tblAccountResponsible.Rows[rowCountDealTeam].Range.Font.Name = "Roboto Light";
tblAccountResponsible.Cell(rowCountDealTeam, 1).Borders[WdBorderType.wdBorderTop].LineStyle = WdLineStyle.wdLineStyleNone;
tblAccountResponsible.Cell(rowCountDealTeam, 1).Borders[WdBorderType.wdBorderBottom].LineStyle = WdLineStyle.wdLineStyleNone;
tblAccountResponsible.Rows[rowCountDealTeam].Cells[2].Shading.BackgroundPatternColor = WdColor.wdColorWhite;
tblAccountResponsible.Rows[rowCountDealTeam].Cells[2].Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft;
tblAccountResponsible.Rows[rowCountDealTeam].Cells[3].Shading.BackgroundPatternColor = WdColor.wdColorWhite;
tblAccountResponsible.Rows[rowCountDealTeam].Cells[3].Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft;
tblAccountResponsible.Rows[rowCountDealTeam].Cells[2].Range.Text = item[0].ToString();
if (rowCountDealTeam <= 2)
{
tblAccountResponsible.Rows[rowCountDealTeam].Cells[3].Range.Text = dataResult[0].AccountResponsibleCIOName;
tblAccountResponsible.Rows[rowCountDealTeam].Cells[3].Range.Font.Name = "Roboto Light";
}
}
tblAccountResponsible.Cell(rowCountDealTeam, 1).Borders[WdBorderType.wdBorderBottom].LineStyle = WdLineStyle.wdLineStyleSingle;
#endregion
#region Attachment
this.FillBookmarkWithPAMAttachmentType1(app, con, "ProjectAnalysis", AppConstants.TableName.PAM_ProjectAnalysis, pamId);
this.FillBookmarkWithPAMAttachmentType1(app, con, "HistoricalFinancial", AppConstants.TableName.PAM_HistoricalFinancial, pamId);
this.FillBookmarkWithPAMAttachmentType1(app, con, "SupplementalProcurement", AppConstants.TableName.PAM_Supplemental, pamId);
this.FillBookmarkWithPAMAttachmentType1(app, con, "SocialEnvironmental", AppConstants.TableName.PAM_Social, pamId);
this.FillBookmarkWithPAMAttachmentType1(app, con, "TermSheet", AppConstants.TableName.PAM_TermSheet, pamId);
this.FillBookmarkWithPAMAttachmentType1(app, con, "RiskRating", AppConstants.TableName.PAM_RiskRating, pamId);
this.FillBookmarkWithPAMAttachmentType1(app, con, "KYCchecklists", AppConstants.TableName.PAM_KYCChecklists, pamId);
this.FillBookmarkWithPAMAttachmentType1(app, con, "OtherBanks", AppConstants.TableName.PAM_OtherBanksFacilities, pamId);
this.FillBookmarkWithPAMAttachmentType2(app, con, "LegalDueDiligence", AppConstants.TableName.PAM_LegalDueDiligenceReport, pamId);
this.FillBookmarkWithPAMAttachmentType2(app, con, "SAndEDueDiligence", AppConstants.TableName.PAM_SAndEDueDiligence, pamId);
this.FillBookmarkWithPAMAttachmentType2(app, con, "OtherReports", AppConstants.TableName.PAM_OtherReports, pamId);
#endregion
doc.PageSetup.PaperSize = WdPaperSize.wdPaperA4;
doc.SaveAs2(Path.Combine(temporaryFolderLocation, fileNamePDF), WdExportFormat.wdExportFormatPDF);
}
finally
{
doc.Close(WdSaveOptions.wdDoNotSaveChanges);
}
}
finally
{
app.Quit();
}
File.Delete(destFile);
string destFilePDF = Path.Combine(temporaryFolderLocation, fileNamePDF);
byte[] fileContent = File.ReadAllBytes(destFilePDF);
FileMergeResult result = new FileMergeResult();
result.FileContent = fileContent;
result.FileName = fileNamePDF;
return(result);
}
public FileMergeResult MergeCMCorporateFinance(SqlConnection con, long cmId, string foldertemplate, string temporaryFolderLocation)
{
DBHelper db = new DBHelper();
//foldertemplate = ConfigurationManager.AppSettings["CM_TEMPLATE_FOLDER_LOCATION"];
//temporaryFolderLocation = ConfigurationManager.AppSettings["CM_MERGE_FOLDER_LOCATION"];
List <CMData> dataResult = db.ExecToModel <CMData>(con, "dbo.Generate_Document_CM_Data_SP", CommandType.StoredProcedure, new List <SqlParameter> {
this.NewSqlParameter("@Id", SqlDbType.BigInt, cmId)
});
System.Data.DataTable listBorrower = db.ExecToDataTable(con, "Generate_Document_CM_Borrower_SP", CommandType.StoredProcedure, new List <SqlParameter> {
this.NewSqlParameter("@Id", SqlDbType.BigInt, cmId)
});
System.Data.DataTable listBorrowerCover = db.ExecToDataTable(con, "Generate_Document_CM_Borrower_Cover_SP", CommandType.StoredProcedure, new List <SqlParameter> {
this.NewSqlParameter("@Id", SqlDbType.BigInt, cmId)
});
System.Data.DataTable listFacility = db.ExecToDataTable(con, "Generate_Document_CM_ProposalFacility_SP", CommandType.StoredProcedure, new List <SqlParameter> {
this.NewSqlParameter("@Id", SqlDbType.BigInt, cmId)
});
System.Data.DataTable listDealTeam = db.ExecToDataTable(con, "Generate_Document_CM_DealTeam_SP", CommandType.StoredProcedure, new List <SqlParameter> {
this.NewSqlParameter("@Id", SqlDbType.BigInt, cmId)
});
System.Data.DataTable listDocVersion = db.ExecToDataTable(con, "Generate_Document_CM_Version_SP", CommandType.StoredProcedure, new List <SqlParameter> {
this.NewSqlParameter("@Id", SqlDbType.BigInt, cmId)
});
string fileName = "CM-" + dataResult[0].ProductType + "-" + dataResult[0].CompanyName + "-" + dataResult[0].ProjectCode + ".docx";
string fileNamePDF = "CM-" + dataResult[0].ProductType + "-" + dataResult[0].CompanyName + "-" + dataResult[0].ProjectCode + ".pdf";
string fileTemplateName = "CM Corporate Finance Template.docx";
string fileTemplateFullName = foldertemplate.AppendPath("\\", fileTemplateName);
string getfileName = Path.GetFileName(fileTemplateFullName);
string destFile = Path.Combine(temporaryFolderLocation, fileName);
IIFCommon.copyFromNetwork(fileTemplateFullName, destFile, foldertemplate, temporaryFolderLocation);
object missing = System.Reflection.Missing.Value;
object readOnly = (object)false;
Application app = new Application();
string currFontFamily = "Roboto Light";
float currFontSize = 10;
try
{
Document doc = app.Documents.Open(destFile, ref missing, ref readOnly);
app.Visible = false;
try
{
#region Cover
app.ActiveDocument.Bookmarks["Review"].Range.Text = dataResult[0].ReviewMemo;
//app.ActiveDocument.Bookmarks["CompanyName"].Range.Text = dataResult[0].CompanyName;
int countBorrower = 0;
int countSetBorrower = 0;
string prevBorrower = "";
string currentBorrower = "";
List <String> lsBorrower = new List <string>();
foreach (DataRow item in listBorrowerCover.Rows)
{
countBorrower++;
prevBorrower = item[0].ToString().Trim().ToLower();
if (!lsBorrower.Contains(prevBorrower))
{
if (countBorrower > 5)
{
continue;
}
countSetBorrower++;
app.ActiveDocument.Bookmarks["CompanyName" + (countSetBorrower)].Range.Text = item[0].ToString();
currentBorrower = item[0].ToString().Trim().ToLower();
lsBorrower.Add(currentBorrower);
}
}
app.ActiveDocument.Bookmarks["ProjectName"].Range.Text = dataResult[0].ProjectName;
app.ActiveDocument.Bookmarks["CMNumber"].Range.Text = IIFCommon.generateCMNumber(
dataResult[0].ProjectCode
, Convert.ToInt32(dataResult[0].CMNumber).ToString("00")
, dataResult[0].ApprovalAuhority
, dataResult[0].CMDate.ToString("MMM")
, dataResult[0].CMDate.ToString("yyyy")
);
app.ActiveDocument.Bookmarks["ProjectCode"].Range.Text = dataResult[0].ProjectCode;
System.Globalization.CultureInfo cult = new System.Globalization.CultureInfo("en-us");
string dateToShow = string.Format(cult, "{0:dd-MMMM-yyyy}", dataResult[0].CMDate);
app.ActiveDocument.Bookmarks["ProjectDate"].Range.Text = dateToShow;
string footerDateToShow = string.Format(cult, "{0:MMM yyyy}", dataResult[0].CMDate);
//app.ActiveDocument.Bookmarks["FooterDate"].Range.Text = footerDateToShow;
#endregion
#region PROJECT
app.ActiveDocument.Bookmarks["AxPROJECTxProjectName"].Range.Text = dataResult[0].ProjectName;
app.ActiveDocument.Bookmarks["AxPROJECTxSectorSubsector"].Range.Text = dataResult[0].SectorDesc + " - " + dataResult[0].SubSectorDesc;
this.FillBookmarkWithCMAttachmentABNormal(app, con, "AxPROJECTxFundingNeeds", AppConstants.TableName.CM_ProjectData, cmId, "FundingNeeds", "Id");
this.FillBookmarkWithCMAttachmentABNormal(app, con, "AxPROJECTxDealStrategy", AppConstants.TableName.CM_ProjectData, cmId, "DealStrategy", "Id");
#endregion
#region BORROWER
app.ActiveDocument.Bookmarks["BxBORROWERxBorrower"].Range.Text = dataResult[0].CompanyName;
Table tblShareholders = IIFCommon.createTable(app, "BxBORROWERxShareholders", 3, true);
//header
tblShareholders.Cell(1, 1).Range.Text = "Project Company";
tblShareholders.Cell(1, 2).Range.Text = "Shareholders";
tblShareholders.Cell(1, 3).Range.Text = "% ownership";
string prevkey = "";
string cellText = "";
int rowCounter = 1;
int rowTemp = 0;
foreach (DataRow item in listBorrower.Rows)
{
tblShareholders.Rows.Add(ref missing);
rowCounter++;
prevkey = item[0].ToString().Trim().ToLower();
if (cellText.Trim().ToLower() != prevkey.Trim().ToLower())
{
//merge kolom kalo value nya beda, mulai row ke 3
if (rowCounter > 2 && (rowTemp != (rowCounter - 1)))
{
tblShareholders.Cell(rowTemp, 1).Merge(tblShareholders.Cell(rowCounter - 1, 1));
}
rowTemp = rowCounter;
tblShareholders.Cell(rowCounter, 1).Shading.BackgroundPatternColor = WdColor.wdColorWhite;
tblShareholders.Cell(rowCounter, 1).Range.Text = item[0].ToString().Trim();
cellText = item[0].ToString().Trim().ToLower();
tblShareholders.Cell(rowCounter, 1).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft;
}
tblShareholders.Cell(rowCounter, 2).Shading.BackgroundPatternColor = WdColor.wdColorWhite;
tblShareholders.Cell(rowCounter, 2).Range.Text = item[1].ToString().Trim();
tblShareholders.Cell(rowCounter, 2).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft;
tblShareholders.Cell(rowCounter, 3).Shading.BackgroundPatternColor = WdColor.wdColorWhite;
tblShareholders.Cell(rowCounter, 3).Range.Text = item[2].ToString().Trim();
tblShareholders.Cell(rowCounter, 3).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphRight;
}
//merge kolom untuk value trakhir
if (rowCounter > 2 && (rowTemp != rowCounter))
{
tblShareholders.Cell(rowTemp, 1).Merge(tblShareholders.Cell(rowCounter, 1));
}
app.ActiveDocument.Bookmarks["BxBORROWERxUltimateBeneficialOwner"].Range.Text = dataResult[0].UltimateBeneficialOwner;
app.ActiveDocument.Bookmarks["BxBORROWERxRatingxRating"].Range.Text = dataResult[0].IIFRate;
string ratingDateToShow = string.Format(cult, "{0:dd MMM yyyy}", dataResult[0].IIFRatingDate);
app.ActiveDocument.Bookmarks["BxBORROWERxRatingxRatingDate"].Range.Text = ratingDateToShow;
app.ActiveDocument.Bookmarks["BxBORROWERxRatingxSP"].Range.Text = dataResult[0].SAndPRate;
app.ActiveDocument.Bookmarks["BxBORROWERxRatingxMoodys"].Range.Text = dataResult[0].MoodysRate;
app.ActiveDocument.Bookmarks["BxBORROWERxRatingxFitch"].Range.Text = dataResult[0].FitchRate;
app.ActiveDocument.Bookmarks["BxBORROWERxRatingxPefindo"].Range.Text = dataResult[0].PefindoRate;
app.ActiveDocument.Bookmarks["BxBORROWERxRatingxSAndECategory"].Range.Text = dataResult[0].SAndECategoryRate + "-" + dataResult[0].SAndECategoryType;
app.ActiveDocument.Bookmarks["BxBORROWERxRatingxLQCBIChecking"].Range.Text = dataResult[0].LQCOrBICheckingRate;
this.FillBookmarkWithCMAttachmentABNormal(app, con, "BxBORROWERxBusinessActivities", AppConstants.TableName.CM_BorrowerOrInvesteeCompanyData, cmId, "BusinessActivities", "Id");
this.FillBookmarkWithCMAttachmentABNormal(app, con, "BxBORROWERxOtherInformation", AppConstants.TableName.CM_BorrowerOrInvesteeCompanyData, cmId, "OtherInformation", "Id");
#endregion
#region PROPOSAL
this.FillBookmarkWithCMAttachmentABNormal(app, con, "CxPROPOSALxPurpose", AppConstants.TableName.CM_ProposalOrFacilityData, cmId, "Purpose", "Id");
app.ActiveDocument.Bookmarks["CxPROPOSALxApprovalAuthority"].Range.Text = dataResult[0].ApprovalAuhority;
Table tblFacility = IIFCommon.createTable(app, "CxPROPOSALxFacility", 4, true);
//header
tblFacility.Cell(1, 1).Range.Text = "Type";
tblFacility.Cell(1, 2).Range.Text = "Approved";
tblFacility.Cell(1, 3).Range.Text = "Proposed";
tblFacility.Cell(1, 4).Range.Text = "Outstanding";
rowCounter = 1;
foreach (DataRow item in listFacility.Rows)
{
tblFacility.Rows.Add(ref missing);
rowCounter++;
tblFacility.Cell(rowCounter, 1).Shading.BackgroundPatternColor = WdColor.wdColorWhite;
tblFacility.Cell(rowCounter, 1).Range.Text = item[0].ToString();
tblFacility.Cell(rowCounter, 1).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft;
tblFacility.Cell(rowCounter, 2).Shading.BackgroundPatternColor = WdColor.wdColorWhite;
tblFacility.Cell(rowCounter, 2).Range.Text = item[1].ToString() + " " + item[2].ToString();
tblFacility.Cell(rowCounter, 2).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphRight;
tblFacility.Cell(rowCounter, 3).Shading.BackgroundPatternColor = WdColor.wdColorWhite;
tblFacility.Cell(rowCounter, 3).Range.Text = item[3].ToString() + " " + item[4].ToString();
tblFacility.Cell(rowCounter, 3).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphRight;
tblFacility.Cell(rowCounter, 4).Shading.BackgroundPatternColor = WdColor.wdColorWhite;
tblFacility.Cell(rowCounter, 4).Range.Text = item[5].ToString() + " " + item[6].ToString();
tblFacility.Cell(rowCounter, 4).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphRight;
}
tblFacility.Rows.Add(ref missing);
tblFacility.Cell(rowCounter + 1, 1).Range.Text = "Remarks : " + dataResult[0].FacilityOrInvestmentRemarks;
tblFacility.Cell(rowCounter + 1, 1).Merge(tblFacility.Cell(rowCounter + 1, 4));
tblFacility.Cell(rowCounter + 1, 1).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft;
app.ActiveDocument.Bookmarks["CxPROPOSALxGroupExposure"].Range.Text = dataResult[0].GroupExposureCurr + " " + dataResult[0].GroupExposureAmount;
this.FillBookmarkWithCMAttachmentABNormal(app, con, "CxPROPOSALxRemarks", AppConstants.TableName.CM_ProposalOrFacilityData, cmId, "Remarks", "Id");
app.ActiveDocument.Bookmarks["CxPROPOSALxTenor"].Range.Text = dataResult[0].TenorYear + " year(s) " + dataResult[0].TenorMonth + " month(s)";
app.ActiveDocument.Bookmarks["CxPROPOSALxAverageLoanLife"].Range.Text = dataResult[0].AverageLoanLifeYear + " year(s) " + dataResult[0].AverageLoanLifeMonth + " month(s)";
this.FillBookmarkWithCMAttachmentABNormal(app, con, "CxPROPOSALxPricingxInterestRate", AppConstants.TableName.CM_ProposalOrFacilityData, cmId, "PricingInterestRate", "Id");
app.ActiveDocument.Bookmarks["CxPROPOSALxPricingxCommitmentFee"].Range.Text = dataResult[0].PricingCommitmentFee;
app.ActiveDocument.Bookmarks["CxPROPOSALxPricingxFacility"].Range.Text = dataResult[0].PricingUpfrontFacilityFee;
app.ActiveDocument.Bookmarks["CxPROPOSALxPricingxStructuringFee"].Range.Text = dataResult[0].PricingStructuringFee;
app.ActiveDocument.Bookmarks["CxPROPOSALxPricingxArrangerFee"].Range.Text = dataResult[0].PricingArrangerFee;
this.FillBookmarkWithCMAttachmentABNormal(app, con, "CxPROPOSALxCollateral", AppConstants.TableName.CM_ProposalOrFacilityData, cmId, "PricingCollateral", "Id");
this.FillBookmarkWithCMAttachmentABNormal(app, con, "CxPROPOSALxOtherCondition", AppConstants.TableName.CM_ProposalOrFacilityData, cmId, "PricingOtherConditions", "Id");
app.ActiveDocument.Bookmarks["CxPROPOSALxLimitCompliancexCurrency"].Range.Text = dataResult[0].LimitComplianceCurrency;
string asForDateToShow = string.Format(cult, "{0:MMM}", Convert.ToDateTime("1985-" + dataResult[0].FacilityLimitComplianceMonth.ToString() + "-01"));
app.ActiveDocument.Bookmarks["CxPROPOSALxLimitCompliancexAsFor"].Range.Text = asForDateToShow + " " + dataResult[0].FacilityLimitComplianceYear.ToString();
app.ActiveDocument.Bookmarks["CxPROPOSALxLimitCompliancexRiskRating"].Range.Text = dataResult[0].FacilityLimitComplianceIIFRate;
app.ActiveDocument.Bookmarks["CxPROPOSALxLimitCompliancexSecExposure"].Range.Text = dataResult[0].SectorDesc;
app.ActiveDocument.Bookmarks["CxPROPOSALxLimitCompliancexSPELxML"].Range.Text = dataResult[0].FacilityLimitComplianceSingleProjectExposureMaxLimit;
app.ActiveDocument.Bookmarks["CxPROPOSALxLimitCompliancexSPELxML"].Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphRight;
app.ActiveDocument.Bookmarks["CxPROPOSALxLimitCompliancexSPELxP"].Range.Text = dataResult[0].FacilityLimitComplianceSingleProjectExposureProposed;
app.ActiveDocument.Bookmarks["CxPROPOSALxLimitCompliancexSPELxP"].Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphRight;
app.ActiveDocument.Bookmarks["CxPROPOSALxLimitCompliancexSPELxR"].Range.Text = dataResult[0].SingleProjectExposureRemarks;
app.ActiveDocument.Bookmarks["CxPROPOSALxLimitCompliancexPxML"].Range.Text = dataResult[0].FacilityLimitComplianceProductMaxLimit;
app.ActiveDocument.Bookmarks["CxPROPOSALxLimitCompliancexPxML"].Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphRight;
app.ActiveDocument.Bookmarks["CxPROPOSALxLimitCompliancexPxP"].Range.Text = dataResult[0].FacilityLimitComplianceProductProposed;
app.ActiveDocument.Bookmarks["CxPROPOSALxLimitCompliancexPxP"].Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphRight;
app.ActiveDocument.Bookmarks["CxPROPOSALxLimitCompliancexPxR"].Range.Text = dataResult[0].ProductRemarks;
app.ActiveDocument.Bookmarks["CxPROPOSALxLimitCompliancexRRxML"].Range.Text = dataResult[0].FacilityLimitComplianceRiskRatingMaxLimit;
app.ActiveDocument.Bookmarks["CxPROPOSALxLimitCompliancexRRxML"].Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphRight;
app.ActiveDocument.Bookmarks["CxPROPOSALxLimitCompliancexRRxP"].Range.Text = dataResult[0].FacilityLimitComplianceRiskRatingProposed;
app.ActiveDocument.Bookmarks["CxPROPOSALxLimitCompliancexRRxP"].Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphRight;
app.ActiveDocument.Bookmarks["CxPROPOSALxLimitCompliancexRRxR"].Range.Text = dataResult[0].RiskRatingRemarks;
app.ActiveDocument.Bookmarks["CxPROPOSALxLimitCompliancexGELxML"].Range.Text = dataResult[0].FacilityLimitComplianceGrupExposureMaxLimit;
app.ActiveDocument.Bookmarks["CxPROPOSALxLimitCompliancexGELxML"].Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphRight;
app.ActiveDocument.Bookmarks["CxPROPOSALxLimitCompliancexGELxP"].Range.Text = dataResult[0].FacilityLimitComplianceGrupExposureProposed;
app.ActiveDocument.Bookmarks["CxPROPOSALxLimitCompliancexGELxP"].Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphRight;
app.ActiveDocument.Bookmarks["CxPROPOSALxLimitCompliancexGELxR"].Range.Text = dataResult[0].GrupExposureRemarks;
app.ActiveDocument.Bookmarks["CxPROPOSALxLimitCompliancexSExML"].Range.Text = dataResult[0].FacilityLimitComplianceSectorExposureMaxLimit;
app.ActiveDocument.Bookmarks["CxPROPOSALxLimitCompliancexSExML"].Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphRight;
app.ActiveDocument.Bookmarks["CxPROPOSALxLimitCompliancexSExP"].Range.Text = dataResult[0].FacilityLimitComplianceSectorExposureProposed;
app.ActiveDocument.Bookmarks["CxPROPOSALxLimitCompliancexSExP"].Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphRight;
app.ActiveDocument.Bookmarks["CxPROPOSALxLimitCompliancexSExR"].Range.Text = dataResult[0].SectorExposureRemarks;
app.ActiveDocument.Bookmarks["CxPROPOSALxLimitCompliancexNotes"].Range.Text = dataResult[0].notes;
this.FillBookmarkWithCMAttachmentABNormal(app, con, "CxPROPOSALxExceptionToIIFPolicy", AppConstants.TableName.CM_ProposalOrFacilityData, cmId, "PricingExceptionToIIFPolicy", "Id");
app.ActiveDocument.Bookmarks["CxPROPOSALxReviewPeriod"].Range.Text = dataResult[0].ProposalReviewPeriod;
#endregion
#region RECOMMENDATION
this.FillBookmarkWithCMAttachmentABNormal(app, con, "DxRECOMMENDATIONxKeyInvestment", AppConstants.TableName.CM_RecommendationData, cmId, "KeyInvestmentRecommendation", "Id");
this.FillBookmarkWithCMAttachmentABNormal(app, con, "DxRECOMMENDATION", AppConstants.TableName.CM_RecommendationData, cmId, "Recommendation", "Id");
Table tblDealTeam = IIFCommon.createTable(app, "DxRECOMMENDATIONxDealTeam", 1, false);
tblDealTeam.Borders.Enable = 0;
rowCounter = 0;
foreach (DataRow item in listDealTeam.Rows)
{
tblDealTeam.Rows.Add(ref missing);
rowCounter++;
tblDealTeam.Cell(rowCounter, 1).Range.Text = item[0].ToString();
tblDealTeam.Cell(rowCounter, 1).Range.Shading.BackgroundPatternColor = WdColor.wdColorWhite;
tblDealTeam.Cell(rowCounter, 1).Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft;
}
app.ActiveDocument.Bookmarks["DxRECOMMENDATIONxCIO"].Range.Text = dataResult[0].AccountResponsibleCIOName;
#endregion
#region Attachment
this.FillBookmarkWithCMAttachmentNormal(app, con, "PeriodicReview", AppConstants.TableName.CM_PeriodicReview, cmId);
System.Data.DataTable listPreviousApproval = db.ExecToDataTable(con, "Generate_Document_CM_PreviousApproval_SP", CommandType.StoredProcedure, new List <SqlParameter> {
this.NewSqlParameter("@ProjectCode", SqlDbType.VarChar, dataResult[0].ProjectCode)
});
IIFCommon.createPreviousApproval(app, listPreviousApproval, "PreviousApprovals", dataResult, currFontFamily, currFontSize);
this.FillBookmarkWithCMAttachmentNormal(app, con, "RiskRating", AppConstants.TableName.CM_RiskRating, cmId);
this.FillBookmarkWithCMAttachmentNormal(app, con, "KYCChecklists", AppConstants.TableName.CM_KYCChecklists, cmId);
this.FillBookmarkWithCMAttachmentNormal(app, con, "SandEReview", AppConstants.TableName.CM_SAndEReview, cmId);
this.FillBookmarkWithCMAttachmentNormal(app, con, "OtherBanksfacilities", AppConstants.TableName.CM_OtherBanksFacilities, cmId);
this.FillBookmarkWithCMAttachmentNormal(app, con, "OtherAttachment", AppConstants.TableName.CM_OtherAttachment, cmId);
#endregion
IIFCommon.finalizeDoc(doc);
IIFCommon.injectFooterCM(doc, footerDateToShow, "Corporate");
bool isPreview = false;
try
{
if (dataResult[0].MWorkflowStatusId != null && dataResult[0].MWorkflowStatusId == 7)
{
isPreview = true;
}
}
catch { }
fileNamePDF = IIFCommon.fileNameFormat(listDocVersion, fileNamePDF, isPreview);
doc.SaveAs2(Path.Combine(temporaryFolderLocation, fileNamePDF), WdExportFormat.wdExportFormatPDF);
//doc.SaveAs2(Path.Combine(temporaryFolderLocation, fileName));
}
finally
{
doc.Close(WdSaveOptions.wdDoNotSaveChanges);
}
}
finally
{
app.Quit();
}
File.Delete(destFile);
string destFilePDF = Path.Combine(temporaryFolderLocation, fileNamePDF);
byte[] fileContent = File.ReadAllBytes(destFilePDF);
FileMergeResult result = new FileMergeResult();
result.FileContent = fileContent;
result.FileName = fileNamePDF;
return(result);
}
/// <summary>
///
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="getParameter"></param>
/// <param name="restartAppDomainOnChange"></param>
/// <returns></returns>
public override T GetConfig <T>(IConfigParameter getParameter, bool restartAppDomainOnChange)
{
Check.Argument.IsNotNull("getParameter", getParameter);
Check.Argument.IsAssignableFrom("getParameter", getParameter, typeof(XmlConfigGetParameter));
T config = GetConfigFromCache <T>(getParameter);
if (config == null)
{
lock (this.SyncLock)
{
config = GetConfigFromCache <T>(getParameter);
if (config == null)
{
XmlConfigGetParameter cp = getParameter as XmlConfigGetParameter;
if (cp.Files.Length > 0)
{
if (cp.Files.Length == 1)
{
try
{
config = Serializer.XmlSerializer.FromFile <T>(cp.Files[0]);
}
catch (Exception ex)
{
ConfigThrowHelper.ThrowConfigException(ex, R.ConfigFileNotResolved, cp.Files[0], typeof(T).FullName);
}
}
else
{
try
{
FileMergeResult fileMergeResult = XmlUtils.MergeFiles(new List <string>(cp.Files));
if (!fileMergeResult.AllFilesMerged)
{
//Logger.Warning("Framework.Configuration", "XMLConfigProvider", fileMergeResult.ToString());
}
if (fileMergeResult.HasFileMerged)
{
config = Serializer.XmlSerializer.FromSerializedString <T>(fileMergeResult.FileContentMerged);
}
else
{
//Logger.Error("Framework.Configuration", "XMLConfigProvider", fileMergeResult.ToString());
}
}
catch (Exception ex)
{
ConfigThrowHelper.ThrowConfigException(ex, R.ConfigFileNotResolved, cp.FilePaths, typeof(T).FullName);
}
}
AddConfigToCache(getParameter, config);
SetupWacher(new XmlConfigChangeWatcher(cp));
}
}
}
}
return(config);
}
public void MergePAMDocument(long id, string connectionString, string folderTemplateLocation, string temporaryFolderLocation, string mergeByFQN, string mergeBy)
{
log4net.Config.XmlConfigurator.Configure();
this.Logger.Info("MergePAMDocument_" + version);
using (SqlConnection con = new SqlConnection(connectionString))
{
con.Open();
string query = string.Empty;
query = query + "SELECT";
query = query + " [MProductTypeId]";
query = query + " FROM [dbo].[PAM]";
query = query + " WHERE [Id] = @Id";
int?productTypeId = null;
using (SqlCommand cmd = con.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
cmd.Parameters.Add(this.NewSqlParameter("Id", SqlDbType.BigInt, id));
using (SqlDataReader dr = cmd.ExecuteReader())
{
int indexOf_MProductTypeId = dr.GetOrdinal("MProductTypeId");
while (dr.Read())
{
productTypeId = dr.GetInt32(indexOf_MProductTypeId);
}
}
}
if (productTypeId.HasValue)
{
FileMergeResult fileMergeResult = null;
switch (productTypeId.Value)
{
case 1:
PAM_ProjectFinanceNEW svcPAMProjectFinance = new PAM_ProjectFinanceNEW();
fileMergeResult = svcPAMProjectFinance.MergePAMProjectFinance(con, id, folderTemplateLocation, temporaryFolderLocation);
break;
case 2:
PAM_CorporateFinanceNEW svcPAMCorporateFinance = new PAM_CorporateFinanceNEW();
fileMergeResult = svcPAMCorporateFinance.MergePAMCorporateFinance(con, id, folderTemplateLocation, temporaryFolderLocation);
break;
case 3:
PAM_EquityFinanceNEW svcEquityFinance = new PAM_EquityFinanceNEW();
fileMergeResult = svcEquityFinance.MergePAMEquityFinance(con, id, folderTemplateLocation, temporaryFolderLocation);
break;
default:
break;
}
string query1 = string.Empty;
query1 = query1 + "SELECT";
query1 = query1 + " [MWorkflowStatusId]";
query1 = query1 + " FROM [dbo].[PAM]";
query1 = query1 + " WHERE [Id] = @Id";
int?mWorkflowStatusId = null;
using (SqlCommand cmd = con.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = query1;
cmd.Parameters.Add(this.NewSqlParameter("Id", SqlDbType.BigInt, id));
using (SqlDataReader dr = cmd.ExecuteReader())
{
int indexOf_mWorkflowStatusId = dr.GetOrdinal("MWorkflowStatusId");
while (dr.Read())
{
mWorkflowStatusId = dr.GetInt32(indexOf_mWorkflowStatusId);
}
}
}
bool isPreview = false;
try
{
if (mWorkflowStatusId != null && mWorkflowStatusId == 7)
{
isPreview = true;
}
}
catch { }
SaveMergeResultToDatabase svcSave = new SaveMergeResultToDatabase();
svcSave.SavePAMToDatabase(con, id, fileMergeResult.FileContent, mergeByFQN, mergeBy, fileMergeResult.FileName, isPreview);
}
con.Close();
}
}
