//v1.8.8 2019.03.08 Alex - Don't run the view/sp to refresh
public List <CUSTOMRP.Model.SOURCEVIEWCOLUMN> RefreshModelsForSourceView(int UserID, int SVID, bool showHidden = false)
{
List <CUSTOMRP.Model.SOURCEVIEWCOLUMN> result = dal.GetModelsForSourceView(UserID, SVID, showHidden);
#region Check if there's new columns to add, and whether there are columns to hide
if (showHidden)
{
string[] svColumns = result.Select(x => x.COLUMNNAME).ToArray();
CUSTOMRP.Model.SOURCEVIEW sv = dalSV.GetModel(UserID, SVID);
//CUSTOMRP.Model.V_DATABASE db = (new V_DATABASE()).GetModel(sv.DATABASEID);
CUSTOMRP.Model.DATABASE db = dalDB.GetModel(UserID, sv.DATABASEID);
string[] colnames = new string[0];
switch (sv.SourceType)
{
case CUSTOMRP.Model.SOURCEVIEW.SourceViewType.View:
case CUSTOMRP.Model.SOURCEVIEW.SourceViewType.Table:
{
colnames = CUSTOMRP.BLL.AppHelper.GetColumnNamesForTblView(UserID, db.NAME, sv.TBLVIEWNAME);
}
break;
case CUSTOMRP.Model.SOURCEVIEW.SourceViewType.StoredProc:
{
colnames = CUSTOMRP.BLL.AppHelper.GetColumnNamesForStoredProc(UserID, db.NAME, sv.TBLVIEWNAME);
}
break;
}
for (int i = 0; i < colnames.Length; i++)
{
//if (!svColumns.Contains(colnames[i]))
if (!svColumns.Contains(colnames[i], StringComparer.OrdinalIgnoreCase))
{
// new column
result.Add(new CUSTOMRP.Model.SOURCEVIEWCOLUMN()
{
ID = 0,
SVID = SVID,
COLUMNNAME = colnames[i],
DISPLAYNAME = "",
COLUMNTYPE = 1,
COLUMNCOMMENT = "",
HIDDEN = false,
DEFAULTDISPLAYNAME = "",
});
}
else
{
result.Where(x => x.COLUMNNAME.ToUpper() == colnames[i].ToUpper()).ToList().ForEach(x => x.COLUMNNAME = colnames[i]);
}
}
}
#endregion
#region Remove hidden records from list if hidden record is not to be included in result
if (!showHidden)
{
result = result.Where(x => !x.HIDDEN).ToList();
}
#endregion
return(result);
}
protected void Word(int rpid, ReportCriteria[] rpParam = null, CUSTOMRP.BLL.AppHelper.QueryParamsObject[] qpParam = null)
{
CUSTOMRP.Model.REPORT myReport = WebHelper.bllReport.GetModel(me.ID, rpid);
string WordFilePath = null;
if ((myReport.WordFile != null) && (File.Exists(g_Config["WordTemplatePath"] + myReport.WordFile.WordFileName)))
{
WordFilePath = myReport.WordFile.WordFileName;
}
else
{
CUSTOMRP.Model.WORDTEMPLATE template = WebHelper.bllWORDTEMPLATE.GetModelByReportID(me.ID, myReport.ID, me.ID);
if (File.Exists(g_Config["WordTemplatePath"] + template.TemplateFileName))
{
WordFilePath = template.TemplateFileName;
}
else
{
Context.Response.Write(String.Format("<script type=\"text/javascript\">alert(\"{0}\");</script>",
AppNum.ErrorMsg.filenotfounderror));
Context.Response.End();
return;
}
}
CUSTOMRP.Model.SOURCEVIEW mySV = WebHelper.bllSOURCEVIEW.GetModel(me.ID, myReport.SVID);
string[] colnames;
switch (mySV.SourceType)
{
case CUSTOMRP.Model.SOURCEVIEW.SourceViewType.StoredProc:
{
colnames = CUSTOMRP.BLL.AppHelper.GetColumnNamesForStoredProc(me.ID, me.DatabaseNAME, mySV.TBLVIEWNAME);
}
break;
default:
{
colnames = CUSTOMRP.BLL.AppHelper.GetColumnNamesForTblView(me.ID, me.DatabaseNAME, mySV.TBLVIEWNAME);
}
break;
}
//just print .no need save to database.
List <string> rpcr = new List <string>();
List <string> comments = new List <string>();
List <string> avgs = new List <string>();
List <string> sums = new List <string>();
List <string> groups = new List <string>();
List <string> subtotal = new List <string>();
List <string> subavg = new List <string>();
List <string> subcount = new List <string>();
List <string> count = new List <string>();
//v1.2.0 - Cheong - 2016/07/04 - Add option to hide duplicate items
//DataTable dt = CUSTOMRP.BLL.AppHelper.getDataForReport(ref comments, ref avgs, ref sums, ref groups, ref subtotal, ref subavg, ref subcount, ref count, me, rpid, true, rpParam);
DataTable dt = CUSTOMRP.BLL.AppHelper.getDataForReport(me.ID, ref comments, ref avgs, ref sums, ref groups, ref subtotal, ref subavg, ref subcount, ref count, me,
rpid, myReport.fHideDuplicate, rpParam, QueryParamsToSqlParams(qpParam));
this.p_fSuppressRender = true;
string path = g_Config["WordTemplatePath"] + WordFilePath;
string downloadFilename = myReport.REPORTNAME.Replace(' ', '_') + ".docx";
// Not calling downloadfile because this function need not write on disk
using (MemoryStream filestream = MailMerge.PerformMailMergeFromTemplate(path, dt, colnames))
{
Context.Response.ContentType = "application/octet-stream";
//Context.Response.AddHeader("Content-Disposition", "attachment; filename=\"" + downloadFilename + "\"");
Context.Response.AddHeader("Content-Disposition", String.Format("attachment; filename=\"{0}\"; filename*=utf-8''{1}", downloadFilename, HttpUtility.UrlPathEncode(downloadFilename)));
Context.Response.AddHeader("Content-Length", filestream.Length.ToString());
byte[] fileBuffer = new byte[filestream.Length];
filestream.Read(fileBuffer, 0, (int)filestream.Length);
//CA2202
//filestream.Close();
Context.Response.BinaryWrite(fileBuffer);
Context.Response.End();
}
}
private void LoadCriteriaUIFromSession()
{
CUSTOMRP.Model.SOURCEVIEW mySV = WebHelper.bllSOURCEVIEW.GetModel(me.ID, container.SVID);
foreach (Fields cn in container.criteriaColumn)
{
ColumnInfo columnInfo = columninfos.Where(x => x.ColName == cn.ColumnName).FirstOrDefault();
if (columnInfo != null) // skip column if it no longer exists.
{
CUSTOMRP.Model.REPORTCOLUMN col = myReport.ReportColumns.Where(x => x.ColumnFunc == REPORTCOLUMN.ColumnFuncs.Criteria && x.COLUMNNAME == cn.ColumnName).FirstOrDefault();
if ("String" == columnInfo.DataType)
{
Controls.CriteriaString control = (Controls.CriteriaString)Page.LoadControl("~/controls/CriteriaString.ascx");
control.ColumnName = columnInfo.ColName;
//v1.7.0 Ben 2017.12.20 - columnInfo.DisplayName is ColumnName if directly from clicking run (generated by GetColumnInfoForStoredProc). While cn.DisplayName is generated from 'container.criteriaColumn =...' in rpexcel.aspx which must be the actual
//control.DisplayName = columnInfo.DisplayName;
control.DisplayName = cn.DisplayName;
control.DBName = me.DatabaseNAME;
control.SourceView = mySV.TBLVIEWNAME;
control.SourceType = mySV.SOURCETYPE;
//if ((!IsPostBack) && (col != null))
if (col != null)
{
control.op1 = col.CRITERIA2;
control.range1 = col.CRITERIA3;
control.range2 = col.CRITERIA4;
}
this.Panel1.Controls.Add(control);
}
if ("DateTime" == columnInfo.DataType)
{
Controls.CriteriaString control = (Controls.CriteriaString)Page.LoadControl("~/controls/CriteriaString.ascx");
control.ColumnName = columnInfo.ColName;
//v1.7.0 Ben 2017.12.20 - columnInfo.DisplayName is ColumnName if directly from clicking run (generated by GetColumnInfoForStoredProc). While cn.DisplayName is generated from 'container.criteriaColumn =...' in rpexcel.aspx which must be the actual
//control.DisplayName = columnInfo.DisplayName;
control.DisplayName = cn.DisplayName;
control.ControlType = "datetime";
control.DBName = me.DatabaseNAME;
control.SourceView = mySV.TBLVIEWNAME;
control.SourceType = mySV.SOURCETYPE;
//if ((!IsPostBack) && (col != null))
if (col != null)
{
control.op1 = col.CRITERIA2;
control.range1 = col.CRITERIA3;
control.range2 = col.CRITERIA4;
}
this.Panel1.Controls.Add(control);
}
else if (("Int" == columnInfo.DataType) || ("Decimal" == columnInfo.DataType))
{
Controls.CriteriaNumber control = (Controls.CriteriaNumber)Page.LoadControl("~/Controls/CriteriaNumber.ascx");
control.ColumnName = columnInfo.ColName;
//v1.7.0 Ben 2017.12.20 - columnInfo.DisplayName is ColumnName if directly from clicking run (generated by GetColumnInfoForStoredProc). While cn.DisplayName is generated from 'container.criteriaColumn =...' in rpexcel.aspx which must be the actual
//control.DisplayName = columnInfo.DisplayName;
control.DisplayName = cn.DisplayName;
control.DBName = me.DatabaseNAME;
control.SourceView = mySV.TBLVIEWNAME;
control.SourceType = mySV.SOURCETYPE;
//if ((!IsPostBack) && (col != null))
if (col != null)
{
control.op1 = col.CRITERIA2;
control.range1 = col.CRITERIA3;
control.range2 = col.CRITERIA4;
}
this.Panel1.Controls.Add(control);
}
else if ("Enum" == columnInfo.DataType)
{
Controls.CriteriaInt control = (Controls.CriteriaInt)Page.LoadControl("~/controls/CriteriaInt.ascx");
control.ColumnName = columnInfo.ColName;
//v1.7.0 Ben 2017.12.20 - columnInfo.DisplayName is ColumnName if directly from clicking run (generated by GetColumnInfoForStoredProc). While cn.DisplayName is generated from 'container.criteriaColumn =...' in rpexcel.aspx which must be the actual
//control.DisplayName = columnInfo.DisplayName;
control.DisplayName = cn.DisplayName;
CUSTOMRP.Model.RpEnum rp = new RpEnum();
Type a = rp.GetType().GetNestedType((mySV.SOURCEVIEWNAME + "_" + cn).ToUpper());
control.dt = Common.Utils.GetTableFEnum(a, "text", "value");
this.Panel1.Controls.Add(control);
}
}
//else
//{
// // 2018.10.02 Alex - Name absent in model - Formulas
// Controls.CriteriaNumber control = (Controls.CriteriaNumber)Page.LoadControl("~/Controls/CriteriaNumber.ascx");
// control.ColumnName = cn.ColumnName;
// control.DisplayName = cn.DisplayName;
// HiddenField hf = new HiddenField();
// hf.Value = cn.Formula;
// control.Controls.Add(hf);
// this.Panel1.Controls.Add(control);
//}
}
// Alex 2018.09.20 - Begin
//
//
//
var prefilterParams = new List <CUSTOMRP.BLL.AppHelper.QueryParamsObject>();
var QueryParams = CUSTOMRP.BLL.AppHelper.GetQueryParams(me.ID, me.DatabaseNAME, "qreport." + mySV.TBLVIEWNAME);
// mySV.TBLVIEWNAME may be a view or an sp
//Controls.CriteriaQueryParamWrapper wrapper = (Controls.CriteriaQueryParamWrapper) Page.LoadControl("~/controls/CriteriaQueryParamWrapper.ascx");
var dt = WebHelper.bllQUERYPARAMS.GetList(me.ID, myReport.ID).Tables[0].AsEnumerable().ToDictionary <DataRow, string, string>(rw => Convert.ToString(rw["NAME"]),
rw => Convert.ToString(rw["VALUE"]));
foreach (var x in QueryParams)
{
//Label label2 = new Label();
//label2.Text = me.DatabaseNAME + " qreport." + mySV.TBLVIEWNAME + QueryParams.Count.ToString();
//this.Panel1.Controls.Add(label2);
Controls.CriteriaQueryParam qpControl = (Controls.CriteriaQueryParam)Page.LoadControl("~/controls/CriteriaQueryParam.ascx");
qpControl.ID = x.ParamName.Substring(12);
qpControl.ColumnName = x.ParamName.Substring(12); // Real
qpControl.DisplayName = qpControl.ColumnName.Replace("_", " "); // Display
switch (x.SqlType)
{
case "bit": // radio buttons
qpControl.ControlType = "bool";
break;
case "date": // calendar
case "datetime":
qpControl.ControlType = "datetime";
break;
case "int": // <%=this.ControlType=="int" ? "type='number'" : "" %>
qpControl.ControlType = "int";
if (x.ParamName.Substring(12).Contains('$'))
{
qpControl.ControlType = "enum";
int separator = x.ParamName.IndexOf('$'); // Separator is the $ sign
qpControl.ColumnName = x.ParamName.Substring(12, separator - 12); // Real
qpControl.DisplayName = qpControl.ColumnName.Replace("_", " "); // Display
String[] chopped = x.ParamName.Substring(12).Split('$');
qpControl.EnumArr = CUSTOMRP.BLL.AppHelper.QueryParamGetEnumValues(me.ID, me.DatabaseNAME, chopped[1], chopped[2].Replace("#", " "));
}
break;
case "varchar": // normal
case "nvarchar":
qpControl.ControlType = "string";
if (x.ParamName.Substring(12).Contains('$'))
{
qpControl.ControlType = "enum";
int separator = x.ParamName.IndexOf('$'); // Separator is the $ sign
qpControl.ColumnName = x.ParamName.Substring(12, separator - 12); // Real
qpControl.DisplayName = qpControl.ColumnName.Replace("_", " "); // Display
String[] chopped = x.ParamName.Substring(12).Split('$');
qpControl.EnumArr = CUSTOMRP.BLL.AppHelper.QueryParamGetEnumValues(me.ID, me.DatabaseNAME, chopped[1], chopped[2].Replace("#", " "));
}
break;
}
x.Type = qpControl.ControlType;
if (x.Type == "enum")
{
x.ParamName = x.ParamName.Substring(12);
}
else
{
x.ParamName = qpControl.ColumnName;
}
prefilterParams.Add(x);
this.PlaceHolder_QueryParamsWrapper.Controls.Add(qpControl);
if (dt.Keys.Contains(qpControl.ColumnName))
{
qpControl.Value = dt[qpControl.ColumnName];
}
//wrapper.Panel_QueryParams.Controls.Add(qpControl);
}
//if (QueryParams.Count > 0)
//{
// this.PlaceHolder_QueryParamsWrapper.Controls.Add(wrapper);
//}
Session[strSessionKeyQueryParams] = prefilterParams;
//
//
//
// Alex 2018.09.20 - End
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(int UserID, CUSTOMRP.Model.SOURCEVIEW model)
{
return(dal.Update(UserID, model));
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(int UserID, CUSTOMRP.Model.SOURCEVIEW model)
{
return(dal.Add(UserID, model));
}
private void LoadCriteriaUIFromSession()
{
CUSTOMRP.Model.SOURCEVIEW mySV = WebHelper.bllSOURCEVIEW.GetModel(me.ID, container.SVID);
// Alex 2018.09.20 - Begin
//
//
//
var prefilterParams = new List <CUSTOMRP.BLL.AppHelper.QueryParamsObject>();
var QueryParams = CUSTOMRP.BLL.AppHelper.GetQueryParams(me.ID, me.DatabaseNAME, "qreport." + mySV.TBLVIEWNAME);
// mySV.TBLVIEWNAME may be a view or an sp
foreach (var x in QueryParams)
{
//Label label2 = new Label();
//label2.Text = me.DatabaseNAME + " qreport." + mySV.TBLVIEWNAME + QueryParams.Count.ToString();
//this.Panel1.Controls.Add(label2);
Controls.CriteriaQueryParam qpControl = (Controls.CriteriaQueryParam)Page.LoadControl("~/controls/CriteriaQueryParam.ascx");
qpControl.ID = x.ParamName.Substring(12);
qpControl.ColumnName = x.ParamName.Substring(12); // Real
qpControl.DisplayName = qpControl.ColumnName.Replace("_", " "); // Display
switch (x.SqlType)
{
case "bit": // radio buttons
qpControl.ControlType = "bool";
break;
case "date": // calendar
case "datetime":
qpControl.ControlType = "datetime";
break;
case "int": // <%=this.ControlType=="int" ? "type='number'" : "" %>
qpControl.ControlType = "int";
if (x.ParamName.Substring(12).Contains('$'))
{
qpControl.ControlType = "enum";
int separator = x.ParamName.IndexOf('$'); // Separator is the $ sign
qpControl.ColumnName = x.ParamName.Substring(12, separator - 12); // Real
qpControl.DisplayName = qpControl.ColumnName.Replace("_", " "); // Display
String[] chopped = x.ParamName.Substring(12).Split('$');
qpControl.EnumArr = CUSTOMRP.BLL.AppHelper.QueryParamGetEnumValues(me.ID, me.DatabaseNAME, chopped[1], chopped[2].Replace("#", " "));
}
break;
case "varchar": // normal
case "nvarchar":
qpControl.ControlType = "string";
if (x.ParamName.Substring(12).Contains('$'))
{
qpControl.ControlType = "enum";
int separator = x.ParamName.IndexOf('$'); // Separator is the $ sign
qpControl.ColumnName = x.ParamName.Substring(12, separator - 12); // Real
qpControl.DisplayName = qpControl.ColumnName.Replace("_", " "); // Display
String[] chopped = x.ParamName.Substring(12).Split('$');
qpControl.EnumArr = CUSTOMRP.BLL.AppHelper.QueryParamGetEnumValues(me.ID, me.DatabaseNAME, chopped[1], chopped[2].Replace("#", " "));
}
break;
}
x.Type = qpControl.ControlType;
if (x.Type == "enum")
{
x.ParamName = x.ParamName.Substring(12);
}
else
{
x.ParamName = qpControl.ColumnName;
}
//if (!prefilterParams.Contains(tmpModel))
prefilterParams.Add(x);
this.PlaceHolder_QueryParamsWrapper.Controls.Add(qpControl);
}
Session[strSessionKeyQueryParams] = prefilterParams;
//
//
//
// Alex 2018.09.20 - End
foreach (Fields cn in container.criteriaColumn)
{
var theColumm = columninfos.Where(x => x.ColName == cn.ColumnName);
if (theColumm.Count() > 0)
{
ColumnInfo columnInfo = theColumm.First();
CUSTOMRP.Model.REPORTCOLUMN col = myReport.ReportColumns.Where(x => x.ColumnFunc == REPORTCOLUMN.ColumnFuncs.Criteria && x.COLUMNNAME == cn.ColumnName).FirstOrDefault();
if ("String" == columnInfo.DataType)
{
Controls.CriteriaString control = (Controls.CriteriaString)Page.LoadControl("~/controls/CriteriaString.ascx");
control.ColumnName = columnInfo.ColName;
control.DisplayName = columnInfo.DisplayName;
if ((!IsPostBack) && (col != null))
{
control.op1 = col.CRITERIA2;
control.range1 = col.CRITERIA3;
control.range2 = col.CRITERIA4;
}
this.Panel1.Controls.Add(control);
}
if ("DateTime" == columnInfo.DataType)
{
Controls.CriteriaString control = (Controls.CriteriaString)Page.LoadControl("~/controls/CriteriaString.ascx");
control.ColumnName = columnInfo.ColName;
control.DisplayName = columnInfo.DisplayName;
control.ControlType = "datetime";
if ((!IsPostBack) && (col != null))
{
control.op1 = col.CRITERIA2;
control.range1 = col.CRITERIA3;
control.range2 = col.CRITERIA4;
}
this.Panel1.Controls.Add(control);
}
else if (("Int" == columnInfo.DataType) || ("Decimal" == columnInfo.DataType))
{
Controls.CriteriaNumber control = (Controls.CriteriaNumber)Page.LoadControl("~/Controls/CriteriaNumber.ascx");
control.ColumnName = columnInfo.ColName;
control.DisplayName = columnInfo.DisplayName;
if ((!IsPostBack) && (col != null))
{
control.op1 = col.CRITERIA2;
control.range1 = col.CRITERIA3;
control.range2 = col.CRITERIA4;
}
this.Panel1.Controls.Add(control);
}
else if ("Enum" == columnInfo.DataType)
{
Controls.CriteriaInt control = (Controls.CriteriaInt)Page.LoadControl("~/controls/CriteriaInt.ascx");
control.ColumnName = columnInfo.ColName;
control.DisplayName = columnInfo.DisplayName;
CUSTOMRP.Model.RpEnum rp = new RpEnum();
Type a = rp.GetType().GetNestedType((mySV.SOURCEVIEWNAME + "_" + cn).ToUpper());
control.dt = Common.Utils.GetTableFEnum(a, "text", "value");
this.Panel1.Controls.Add(control);
}
}
else
{
throw new Exception("no citerias column :" + cn.ColumnName);
}
}
}
//run
protected void btnPrint_Click(object sender, EventArgs e)
{
string WordFilePath = null;
string downloadFilename = null;
if ((myReport != null) && (myReport.WordFile != null) && (File.Exists(g_Config["WordTemplatePath"] + myReport.WordFile.WordFileName)))
{
WordFilePath = myReport.WordFile.WordFileName;
downloadFilename = myReport.WordFile.OrigFileName;
}
else
{
CUSTOMRP.Model.WORDTEMPLATE template = WebHelper.bllWORDTEMPLATE.GetModelByReportID(me.ID, myReport.ID, me.ID);
if (template != null)
{
if (File.Exists(g_Config["WordTemplatePath"] + template.TemplateFileName))
{
WordFilePath = template.TemplateFileName;
downloadFilename = myReport.RPTITLE + ".docx";
}
}
}
if (WordFilePath == null)
{
Response.Write(String.Format("<script type=\"text/javascript\">alert(\"{0}\")</script>",
AppNum.ErrorMsg.filenotfounderror));
return;
}
string strSqlColumn, strSqlPlus, strSqlCriteria, strSqlSortOn;
getSql(ref rpcr, out strSqlColumn, out strSqlCriteria, out strSqlPlus, out strSqlSortOn);
if (String.IsNullOrWhiteSpace(strSqlColumn))
{
#region Get Column Names
string[] colnames = null;
CUSTOMRP.Model.SOURCEVIEW sv = WebHelper.bllSOURCEVIEW.GetModel(me.ID, myReport.SVID);
List <CUSTOMRP.Model.SOURCEVIEWCOLUMN> svc = WebHelper.bllSOURCEVIEWCOLUMN.GetModelsForSourceView(me.ID, sv.ID, true).OrderBy(x => x.DisplayName).ToList();
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 (svc == null)
{
colnames = CUSTOMRP.BLL.AppHelper.GetColumnNamesForTblView(me.ID, me.DatabaseNAME, sv.TBLVIEWNAME);
// Filter result to only columns that is requested
columninfos = columninfos.Where(x => colnames.Contains(x.ColName)).ToList();
}
}
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 (svc == null)
{
colnames = CUSTOMRP.BLL.AppHelper.GetColumnNamesForStoredProc(me.ID, me.DatabaseNAME, sv.TBLVIEWNAME);
// Filter result to only columns that is requested
columninfos = columninfos.Where(x => colnames.Contains(x.ColName)).ToList();
}
}
break;
}
if (svc.Count == 0)
{
// do sorting
columninfos = columninfos.OrderBy(p => p.ColName).ToList();
}
else
{
columninfos = (from c in columninfos
join s in svc.Where(x => x.ColumnType == CUSTOMRP.Model.SOURCEVIEWCOLUMN.ColumnTypes.Normal) on c.ColName equals s.COLUMNNAME into lefts
from s in lefts.DefaultIfEmpty()
where (s == null || !s.HIDDEN)
orderby s.DisplayName, c.ColName
select new CUSTOMRP.Model.ColumnInfo()
{
ColName = c.ColName,
DisplayName = (s == null || String.IsNullOrEmpty(s.DISPLAYNAME)) ? c.ColName : s.DISPLAYNAME, // no need to show actual column name here if DisplayName is supplied
DataType = c.DataType,
}).ToList();
}
strSqlColumn = String.Join(",", columninfos.Select(x => "[" + x.ColName + "]").ToArray());
#endregion
}
if (strSqlColumn != "")
{
var sqlParams_queryParams = new List <SqlParameter>();
foreach (var x in container.queryParams)
{
switch (x.SqlType)
{
case "bit":
sqlParams_queryParams.Add(new SqlParameter()
{
ParameterName = "@QueryParam_" + x.ParamName, SqlDbType = SqlDbType.Bit, Value = int.Parse(x.Value)
});
break;
case "int":
sqlParams_queryParams.Add(new SqlParameter()
{
ParameterName = "@QueryParam_" + x.ParamName, SqlDbType = SqlDbType.Int, Value = int.Parse(x.Value)
});
break;
case "date":
sqlParams_queryParams.Add(new SqlParameter()
{
ParameterName = "@QueryParam_" + x.ParamName, SqlDbType = SqlDbType.Date, Value = x.Value
});
break;
case "datetime":
sqlParams_queryParams.Add(new SqlParameter()
{
ParameterName = "@QueryParam_" + x.ParamName, SqlDbType = SqlDbType.DateTime, Value = x.Value
});
break;
case "varchar":
sqlParams_queryParams.Add(new SqlParameter()
{
ParameterName = "@QueryParam_" + x.ParamName, Size = -1, SqlDbType = SqlDbType.VarChar, Value = x.Value
});
break;
case "nvarchar":
sqlParams_queryParams.Add(new SqlParameter()
{
ParameterName = "@QueryParam_" + x.ParamName, Size = -1, SqlDbType = SqlDbType.NVarChar, Value = x.Value
});
break;
}
}
this.rpdt = CUSTOMRP.BLL.AppHelper.getDataForReport(me.ID, myReport.SVID, me.DatabaseNAME, strSqlColumn, strSqlPlus, strSqlCriteria, strSqlSortOn, myReport.fHideDuplicate, sqlParams_queryParams);
if (rpdt != null)
{
if (container.Format == 1)
{
Server.Transfer("htmlexport.aspx?active=html", true);
}
else if (container.Format == 0)
{
string path = g_Config["WordTemplatePath"] + WordFilePath;
string fileName = container.ReportName + ".docx";
using (MemoryStream filestream = MailMerge.PerformMailMergeFromTemplate(path, rpdt, columninfos.Select(x => x.DisplayName).ToArray()))
{
Context.Response.ContentType = "application/octet-stream";
//Context.Response.AddHeader("Content-Disposition", "attachment; filename=\"" + downloadFilename + "\"");
Context.Response.AddHeader("Content-Disposition", String.Format("attachment; filename=\"{0}\"; filename*=utf-8''{1}", downloadFilename, HttpUtility.UrlPathEncode(downloadFilename)));
Context.Response.AddHeader("Content-Length", filestream.Length.ToString());
byte[] fileBuffer = new byte[filestream.Length];
filestream.Read(fileBuffer, 0, (int)filestream.Length);
//CA2202
//filestream.Close();
Context.Response.BinaryWrite(fileBuffer);
Context.Response.End();
}
}
}
else
{
this.lblJavascript.Text = String.Format("<script type=\"text/javascript\">alert(\"{0}\");</script>", "Empty data!");
}
}
else
{
this.lblJavascript.Text = String.Format("<script type=\"text/javascript\">alert(\"{0}\");</script>", "Zero Column,please check!");
}
}