protected void btnDownloadTemplate_Click(object sender, EventArgs e)
{
string path = g_Config["WordTemplatePath"];
string filenamewithoutextension = null;
string datafilename = null;
string originalfilename = null;
if ((myReport.WordFile != null) && File.Exists(path + myReport.WordFile.WordFileName))
{
filenamewithoutextension = Path.GetFileNameWithoutExtension(myReport.WordFile.WordFileName);
if (filenamewithoutextension.LastIndexOf('.') > 0)
{
filenamewithoutextension = filenamewithoutextension.Substring(0, filenamewithoutextension.LastIndexOf('.'));
}
datafilename = filenamewithoutextension + ".xlsx";
originalfilename = myReport.WordFile.OrigFileName;
}
else
{
int svid = Convert.ToInt32(this.ddlQueryName.SelectedValue);
CUSTOMRP.Model.WORDTEMPLATE wordtemplate = WebHelper.bllWORDTEMPLATE.GetModelBySVID(me.ID, svid, me.ID);
if (wordtemplate != null)
{
filenamewithoutextension = Path.GetFileNameWithoutExtension(wordtemplate.TemplateFileName);
datafilename = wordtemplate.DataFileName;
originalfilename = wordtemplate.TemplateFileName;
}
}
if (filenamewithoutextension == null)
{
this.lblJavascript.Text = String.Format("<script type=\"text/javascript\">alert(\"{0}\");</script>", AppNum.ErrorMsg.filenotfounderror);
return;
}
MemoryStream filestream = MailMerge.ChangeDataFilePath(path + myReport.WordFile.WordFileName, datafilename);
Context.Response.ContentType = "application/octet-stream";
//Encode filename according to RFC5987
//Response.AddHeader("Content-Disposition", "attachment; filename=\"" + originalfilename + "\"");
Context.Response.AddHeader("Content-Disposition", String.Format("attachment; filename=\"{0}\"; filename*=utf-8''{1}", originalfilename, HttpUtility.UrlPathEncode(originalfilename)));
Context.Response.AddHeader("Content-Length", filestream.Length.ToString());
byte[] fileBuffer = new byte[filestream.Length];
filestream.Read(fileBuffer, 0, (int)filestream.Length);
filestream.Close();
Context.Response.BinaryWrite(fileBuffer);
p_fSuppressRender = true;
HttpContext.Current.ApplicationInstance.CompleteRequest();
}
public int Add(CUSTOMRP.Model.WORDTEMPLATE model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("INSERT INTO [WORDTEMPLATE](");
strSql.Append("[WORDTEMPLATEName],[Description],[VIEWID],[TemplateFileName],[DataFileName],[ModifyDate],[ModifyUser],[CreateDate],[CreateUser]");
strSql.Append(") VALUES (");
strSql.Append("@WORDTEMPLATEName,@Description,@VIEWID,@TemplateFileName,@DataFileName,@ModifyDate,@ModifyUser,@CreateDate,@CreateUser");
strSql.Append(");select @@IDENTITY");
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@WORDTEMPLATEName", SqlDbType.NVarChar, 50),
new SqlParameter("@Description", SqlDbType.NVarChar, 50),
new SqlParameter("@VIEWID", SqlDbType.Int, 4),
new SqlParameter("@TemplateFileName", SqlDbType.NVarChar, 50),
new SqlParameter("@DataFileName", SqlDbType.NVarChar, 200),
new SqlParameter("@ModifyDate", SqlDbType.DateTime),
new SqlParameter("@ModifyUser", SqlDbType.Int, 4),
new SqlParameter("@CreateDate", SqlDbType.DateTime),
new SqlParameter("@CreateUser", SqlDbType.Int, 4),
};
parameters[0].Value = model.WordTemplateName;
parameters[1].Value = model.Description;
parameters[2].Value = model.ViewID;
parameters[3].Value = model.TemplateFileName;
parameters[4].Value = model.DataFileName;
parameters[5].Value = model.ModifyDate;
parameters[6].Value = model.ModifyUser;
parameters[7].Value = model.CreateDate;
parameters[8].Value = model.CreateUser;
object obj = DbHelperSQL.GetSingle(model.ModifyUser, strSql.ToString(), parameters);
if (obj == null)
{
return(0);
}
else
{
return(Convert.ToInt32(obj));
}
}
public bool Update(CUSTOMRP.Model.WORDTEMPLATE model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("UPDATE [WORDTEMPLATE] SET ");
strSql.Append("[WORDTEMPLATEName] = @WORDTEMPLATEName,");
strSql.Append("[Description] = @Description,");
strSql.Append("[TemplateFileName] = @TemplateFileName,");
strSql.Append("[DataFileName] = @DataFileName,");
strSql.Append("[ModifyDate] = @ModifyDate,");
strSql.Append("[ModifyUser] = @ModifyUser");
strSql.Append(" WHERE [VIEWID] = @VIEWID");
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@WORDTEMPLATEName", SqlDbType.NVarChar, 50),
new SqlParameter("@Description", SqlDbType.NVarChar, 50),
new SqlParameter("@TemplateFileName", SqlDbType.NVarChar, 100),
new SqlParameter("@DataFileName", SqlDbType.NVarChar, 100),
new SqlParameter("@ModifyDate", SqlDbType.DateTime),
new SqlParameter("@ModifyUser", SqlDbType.Int, 4),
new SqlParameter("@VIEWID", SqlDbType.Int, 4),
};
parameters[0].Value = model.WordTemplateName;
parameters[1].Value = model.Description;
parameters[2].Value = model.TemplateFileName;
parameters[3].Value = model.DataFileName;
parameters[4].Value = model.ModifyDate;
parameters[5].Value = model.ModifyUser;
parameters[6].Value = model.ViewID;
int rows = DbHelperSQL.ExecuteSql(model.ModifyUser, strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// Convert DataRow to POCO object of WordTemplate
/// </summary>
/// <param name="row">DataRow to be converted</param>
/// <returns></returns>
private CUSTOMRP.Model.WORDTEMPLATE DataRowToModel(DataRow row)
{
CUSTOMRP.Model.WORDTEMPLATE model = new CUSTOMRP.Model.WORDTEMPLATE();
if (row != null)
{
if (row["WORDTEMPLATEID"] != null)
{
model.WordTemplateID = Convert.ToInt32(row["WORDTEMPLATEID"]);
}
if (row["WORDTEMPLATEName"] != null)
{
model.WordTemplateName = Convert.ToString(row["WORDTEMPLATEName"]);
}
if (row["Description"] != null)
{
model.Description = Convert.ToString(row["Description"]);
}
if (row["VIEWID"] != null)
{
model.ViewID = Convert.ToInt32(row["VIEWID"]);
}
if (row["TemplateFileName"] != null)
{
model.TemplateFileName = Convert.ToString(row["TemplateFileName"]);
}
if (row["DataFileName"] != null)
{
model.DataFileName = Convert.ToString(row["DataFileName"]);
}
if (row["ModifyDate"] != null)
{
model.ModifyDate = Convert.ToDateTime(row["ModifyDate"]);
}
if (row["ModifyUser"] != null)
{
model.ModifyUser = Convert.ToInt32(row["ModifyUser"]);
}
if (row["CreateDate"] != null)
{
model.CreateDate = Convert.ToDateTime(row["CreateDate"]);
}
if (row["CreateUser"] != null)
{
model.CreateUser = Convert.ToInt32(row["CreateUser"]);
}
if (row["FileCount"] != null)
{
model.FileCount = Convert.ToInt32(row["FileCount"]);
}
if (row["SOURCEVIEWNAME"] != null)
{
model.SOURCEVIEWNAME = Convert.ToString(row["SOURCEVIEWNAME"]);
}
if (row["DATABASEID"] != null)
{
model.DATABASEID = Convert.ToInt32(row["DATABASEID"]);
}
if (row["SVDESC"] != null)
{
model.SVDESC = Convert.ToString(row["SVDESC"]);
}
if (row["SOURCETYPE"] != null)
{
model.SOURCETYPE = Convert.ToInt32(row["SOURCETYPE"]);
}
if (row["VIEWLEVEL"] != null)
{
model.VIEWLEVEL = Convert.ToString(row["VIEWLEVEL"]);
}
}
return(model);
}
protected void btnDownloadDatafile_Click(object sender, EventArgs e)
{
if (String.IsNullOrWhiteSpace(this.txtReportName.Text))
{
this.lblJavascript.Text = String.Format("<script type=\"text/javascript\">alert(\"{0}\");</script>",
String.Format(AppNum.ErrorMsg.fieldcannotbeempty, "Report Name"));
return;
}
#region Get ColumnInfo
CUSTOMRP.Model.SOURCEVIEW sv = WebHelper.bllSOURCEVIEW.GetModel(me.ID, Int32.Parse(this.ddlQueryName.SelectedValue));
List <CUSTOMRP.Model.SOURCEVIEWCOLUMN> svclist = WebHelper.bllSOURCEVIEWCOLUMN.GetModelsForSourceView(me.ID, sv.ID).OrderBy(x => x.DisplayName).ToList();
string[] colnames = svclist.Select(x => x.DisplayName).ToArray();
switch (sv.SourceType)
{
case CUSTOMRP.Model.SOURCEVIEW.SourceViewType.View:
case CUSTOMRP.Model.SOURCEVIEW.SourceViewType.Table:
{
try
{
columninfos = CUSTOMRP.BLL.AppHelper.GetColumnInfoForTblView(me.ID, me.DatabaseNAME, sv.TBLVIEWNAME);
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.ToString());
columninfos = new List <CUSTOMRP.Model.ColumnInfo>();
this.lblJavascript.Text = String.Format("<script type=\"text/javascript\">alert('Error in retrieving columns for [{0}]. Please check view defination.');</script>", sv.TBLVIEWNAME);
}
if (colnames == null)
{
colnames = CUSTOMRP.BLL.AppHelper.GetColumnNamesForTblView(me.ID, me.DatabaseNAME, sv.TBLVIEWNAME);
}
}
break;
case CUSTOMRP.Model.SOURCEVIEW.SourceViewType.StoredProc:
{
try
{
columninfos = CUSTOMRP.BLL.AppHelper.GetColumnInfoForStoredProc(me.ID, me.DatabaseNAME, sv.ID);
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.ToString());
columninfos = new List <CUSTOMRP.Model.ColumnInfo>();
this.lblJavascript.Text = String.Format("<script type=\"text/javascript\">alert('Error in retrieving columns for [{0}]. Please check stored proc defination.');</script>", sv.TBLVIEWNAME);
}
if (colnames == null)
{
colnames = CUSTOMRP.BLL.AppHelper.GetColumnNamesForStoredProc(me.ID, me.DatabaseNAME, sv.TBLVIEWNAME);
}
}
break;
}
// Filter result to only columns that is requested
columninfos = columninfos.Where(x => colnames.Contains(x.ColName)).ToList();
#endregion Get ColumnInfo
StringBuilder selectclause = new StringBuilder("TOP 5 ");
//v1.2.0 Fai 2017.01.11 - Avoid to throw Exception if columninfos have no any items - Begin
//selectclause.Append(String.Join(",", columninfos.Select(x => "[" + x.DisplayName + "]").ToArray()));
if (columninfos.Select(x => "[" + x.DisplayName + "]").ToArray().Length > 0)
{
selectclause.Append(String.Join(",", columninfos.Select(x => "[" + x.DisplayName + "]").ToArray()));
}
else
{
selectclause.Append("*");
}
//v1.2.0 Fai 2017.01.11 - Avoid to throw Exception if columninfos have no any items - End
//v1.2.0 - Cheong - 2016/07/04 - Add option to hide duplicate items
//DataTable dt = CUSTOMRP.BLL.AppHelper.getDataForReport(sv.ID, me.DatabaseNAME, selectclause.ToString(), CUSTOMRP.BLL.AppHelper.sql_plus(sv.ID, me), "", "", true);
DataTable dt = CUSTOMRP.BLL.AppHelper.getDataForReport(me.ID, sv.ID, me.DatabaseNAME, selectclause.ToString(), CUSTOMRP.BLL.AppHelper.sql_plus(sv.ID, me), "", "", myReport.fHideDuplicate);
NPOI.XSSF.UserModel.XSSFWorkbook XSSFworkbook = NPOIHelper.GetWorkbookAsMailMergeDataSource(dt);
string path = g_Config["WordTemplatePath"];
string filenamewithoutextension = null;
string datafilename = null;
if ((myReport.WordFile != null) && File.Exists(path + myReport.WordFile.WordFileName))
{
filenamewithoutextension = Path.GetFileNameWithoutExtension(myReport.WordFile.WordFileName);
if (filenamewithoutextension.LastIndexOf('.') > 0)
{
filenamewithoutextension = filenamewithoutextension.Substring(0, filenamewithoutextension.LastIndexOf('.'));
}
datafilename = filenamewithoutextension + ".xlsx";
}
else
{
CUSTOMRP.Model.WORDTEMPLATE wordtemplate = WebHelper.bllWORDTEMPLATE.GetModelBySVID(me.ID, sv.ID, me.ID);
if (wordtemplate != null)
{
filenamewithoutextension = Path.GetFileNameWithoutExtension(wordtemplate.TemplateFileName);
datafilename = wordtemplate.DataFileName;
}
}
if (datafilename == null)
{
this.lblJavascript.Text = String.Format("<script type=\"text/javascript\">alert(\"{0}\");</script>", AppNum.ErrorMsg.parameter_error);
return;
}
string folder = PathHelper.getTempFolderName();
if (!Directory.Exists(folder))
{
Directory.CreateDirectory(folder);
}
string filePath = folder + PathHelper.getSafePath(datafilename);
try
{
using (FileStream outFs = new FileStream(filePath, FileMode.Create))
{
XSSFworkbook.Write(outFs);
//CA2202
//outFs.Close();
}
FileInfo file = new System.IO.FileInfo(filePath);
Response.ContentType = "application/octet-stream";
//Encode filename according to RFC5987
//Response.AddHeader("Content-Disposition", "attachment; filename=\"" + datafilename + "\"");
Context.Response.AddHeader("Content-Disposition", String.Format("attachment; filename=\"{0}\"; filename*=utf-8''{1}", datafilename, HttpUtility.UrlPathEncode(datafilename)));
Response.AddHeader("Content-Length", file.Length.ToString());
Response.WriteFile(file.FullName);
Response.Flush();
p_fSuppressRender = true;
HttpContext.Current.ApplicationInstance.CompleteRequest();
}
finally
{
if (File.Exists(filePath))
{
File.Delete(filePath);
}
}
}
