public CUSTOMRP.Model.SOURCEVIEWCOLUMN DataRowToModel(DataRow row)
{
CUSTOMRP.Model.SOURCEVIEWCOLUMN model = new CUSTOMRP.Model.SOURCEVIEWCOLUMN();
int tempint;
if (row != null)
{
if (Int32.TryParse(Convert.ToString(row["ID"]), out tempint))
{
model.ID = tempint;
}
if (Int32.TryParse(Convert.ToString(row["SVID"]), out tempint))
{
model.SVID = tempint;
}
model.COLUMNNAME = row["COLUMNNAME"] as string;
model.DISPLAYNAME = row["DISPLAYNAME"] as string;
if (Int32.TryParse(Convert.ToString(row["COLUMNTYPE"]), out tempint))
{
model.COLUMNTYPE = tempint;
}
model.COLUMNCOMMENT = row["COLUMNCOMMENT"] as string;
model.FORMULAFIELDID = Convert.IsDBNull(row["FORMULAFIELDID"]) ? (int?)null : Convert.ToInt32(row["FORMULAFIELDID"]);
model.HIDDEN = Convert.ToBoolean(row["HIDDEN"]);
model.DEFAULTDISPLAYNAME = row["DEFAULTDISPLAYNAME"] as string;
}
return(model);
}
private void AjaxSaveData(SVData data)
{
CUSTOMRP.Model.SOURCEVIEWCOLUMN svc = null;
foreach (SVCData r in data.items)
{
//v1.7.0 Ben 2017.08.21 - set the latest
//svc = myColumns.Where(x => x.COLUMNNAME == r.COLUMNNAME).FirstOrDefault();
svc = myColumns.Where(x => x.COLUMNNAME == r.COLUMNNAME).OrderByDescending(x => x.ID).FirstOrDefault();
if (svc != null)
{
svc.DISPLAYNAME = r.DISPLAYNAME;
svc.HIDDEN = Convert.ToBoolean(r.HIDDEN);
}
else
{
svc = new CUSTOMRP.Model.SOURCEVIEWCOLUMN();
svc.COLUMNNAME = r.COLUMNNAME;
svc.DISPLAYNAME = r.DISPLAYNAME;
svc.COLUMNTYPE = 1;
svc.COLUMNCOMMENT = String.Empty;
svc.HIDDEN = Convert.ToBoolean(r.HIDDEN);
svc.DEFAULTDISPLAYNAME = String.Empty;
myColumns.Add(svc);
}
}
Session[strSessionViewNew_myColumns] = myColumns;
}
public int Add(int UserID, CUSTOMRP.Model.SOURCEVIEWCOLUMN model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into SOURCEVIEWCOLUMN(");
strSql.Append("SVID,COLUMNNAME,DISPLAYNAME,COLUMNTYPE,COLUMNCOMMENT,FORMULAFIELDID,HIDDEN,DEFAULTDISPLAYNAME,DATA_TYPE)");
strSql.Append(" values (@SVID,@COLUMNNAME,@DISPLAYNAME,@COLUMNTYPE,@COLUMNCOMMENT,@FORMULAFIELDID,@HIDDEN,@DEFAULTDISPLAYNAME,@DATA_TYPE");
strSql.Append(")");
strSql.Append(";select @@IDENTITY");
SqlParameter[] parameters =
{
new SqlParameter("@SVID", model.SVID),
new SqlParameter("@COLUMNNAME", model.COLUMNNAME),
new SqlParameter("@DISPLAYNAME", model.DISPLAYNAME),
new SqlParameter("@COLUMNTYPE", model.COLUMNTYPE),
new SqlParameter("@COLUMNCOMMENT", model.COLUMNCOMMENT),
new SqlParameter("@FORMULAFIELDID", model.FORMULAFIELDID),
new SqlParameter("@HIDDEN", model.HIDDEN),
new SqlParameter("@DEFAULTDISPLAYNAME", model.DEFAULTDISPLAYNAME),
new SqlParameter("@DATA_TYPE", model.DATA_TYPE),
};
object obj = DbHelperSQL.GetSingle(UserID, strSql.ToString(), parameters);
if (obj == null)
{
return(0);
}
else
{
return(Convert.ToInt32(obj));
}
}
public bool Update(int UserID, CUSTOMRP.Model.SOURCEVIEWCOLUMN model)
{
int rows = DbHelperSQL.ExecuteSql(UserID, "UPDATE SOURCEVIEWCOLUMN SET"
+ " SVID = @SVID,"
+ " COLUMNNAME = @COLUMNNAME,"
+ " DISPLAYNAME = @DISPLAYNAME,"
+ " COLUMNTYPE = @COLUMNTYPE,"
+ " COLUMNCOMMENT = @COLUMNCOMMENT,"
+ " FORMULAFIELDID = @FORMULAFIELDID,"
+ " HIDDEN = @HIDDEN,"
+ " DEFAULTDISPLAYNAME = @DEFAULTDISPLAYNAME,"
+ " DATA_TYPE = @DATA_TYPE"
+ " WHERE ID = @ID",
new SqlParameter("@SVID", model.SVID),
new SqlParameter("@COLUMNNAME", model.COLUMNNAME),
new SqlParameter("@DISPLAYNAME", model.DISPLAYNAME),
new SqlParameter("@COLUMNTYPE", model.COLUMNTYPE),
new SqlParameter("@COLUMNCOMMENT", model.COLUMNCOMMENT),
new SqlParameter("@FORMULAFIELDID", model.FORMULAFIELDID),
new SqlParameter("@HIDDEN", model.HIDDEN),
new SqlParameter("@DEFAULTDISPLAYNAME", model.DEFAULTDISPLAYNAME),
new SqlParameter("@DATA_TYPE", model.DATA_TYPE),
new SqlParameter("@ID", model.ID)
);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
public CUSTOMRP.Model.SOURCEVIEWCOLUMN GetModel(int UserID, int ID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ID,SVID,COLUMNNAME,DISPLAYNAME,COLUMNTYPE,COLUMNCOMMENT,FORMULAFIELDID,HIDDEN,DEFAULTDISPLAYNAME from SOURCEVIEWCOLUMN");
strSql.Append(" where ID=@ID");
SqlParameter[] parameters =
{
new SqlParameter("@ID", SqlDbType.Int, 4)
};
parameters[0].Value = ID;
CUSTOMRP.Model.SOURCEVIEWCOLUMN model = new CUSTOMRP.Model.SOURCEVIEWCOLUMN();
DataSet ds = DbHelperSQL.Query(UserID, strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
private void FillgvFields()
{
try
{
CUSTOMRP.Model.SOURCEVIEWCOLUMN svc = null;
string tblviewname = this.ddlTblViewName.SelectedValue;
int sourcetype = dbViewList.Where(x => x.TblViewName == tblviewname).First().SourceType;
string[] strAllColumns = sourcetype == 2 ?
CUSTOMRP.BLL.AppHelper.GetColumnNamesForStoredProc(me.ID, me.DatabaseNAME, tblviewname).OrderBy(x => x).ToArray() :
CUSTOMRP.BLL.AppHelper.GetColumnNamesForTblView(me.ID, me.DatabaseNAME, tblviewname).OrderBy(x => x).ToArray();
//v1.8.2 Ben 2018.02.22 - Add to store actual columns name from DB
Session[strSessionViewNew_strAllColumns] = strAllColumns;
TableRow tr = null;
TableHeaderRow thead = null;
TableFooterRow tfoot = null;
TableHeaderCell th = null;
TableCell td = null;
HtmlGenericControl span = null;
HtmlInputText txt = null;
HtmlInputCheckBox cb = null;
string safeColName = null;
// refresh column data
if ((myView != null) && (myView.TBLVIEWNAME == tblviewname))
{
if (!IsPostBack)
{
//v1.7.0 Ben 2017.08.21 - Does not show hidden column in default and hence checkbox must be checked
//myColumns = WebHelper.bllSOURCEVIEWCOLUMN.GetModelsForSourceView(me.ID, myView.ID).OrderBy(x => x.COLUMNNAME).ToList();
myColumns = WebHelper.bllSOURCEVIEWCOLUMN.GetModelsForSourceView(me.ID, myView.ID, true).OrderBy(x => x.COLUMNNAME).ToList();
Session[strSessionViewNew_myColumns] = myColumns;
}
}
gvFields.Rows.Clear();
#region Header
thead = new TableHeaderRow();
thead.TableSection = TableRowSection.TableHeader;
th = new TableHeaderCell();
th.Text = "Field Name";
thead.Cells.Add(th);
th = new TableHeaderCell();
th.Text = "Display Name";
thead.Cells.Add(th);
th = new TableHeaderCell();
th.Text = "Visible";
thead.Cells.Add(th);
this.gvFields.Rows.Add(thead);
#endregion
#region Body
foreach (string colname in strAllColumns)
{
if (myColumns != null)
{
svc = myColumns.Where(x => x.COLUMNNAME == colname).OrderByDescending(x => x.ID).FirstOrDefault();
}
safeColName = HttpUtility.HtmlEncode(colname.Replace(' ', '_'));
tr = new TableRow();
tr.TableSection = TableRowSection.TableBody;
tr.Attributes.Add("data-colname", colname);
td = new TableCell();
span = new HtmlGenericControl("SPAN");
span.InnerHtml = colname;
td.Controls.Add(span);
tr.Cells.Add(td);
td = new TableCell();
td.Attributes.Add("data-fieldname", "DISPLAYNAME");
td.Attributes.Add("data-fieldvalue", (svc != null) ? svc.DISPLAYNAME : String.Empty);
txt = new HtmlInputText();
txt.ID = "txtDisplayName_" + safeColName;
txt.Attributes.Add("class", "form-inline input-sm form-control");
txt.Attributes.Add("placeholder", "Enter Display Name here...");
txt.Value = (svc != null) ? svc.DISPLAYNAME : String.Empty;
td.Controls.Add(txt);
//span = new HtmlGenericControl("SPAN");
//span.InnerHtml = (svc != null) ? svc.DISPLAYNAME : String.Empty;
//td.Controls.Add(span);
tr.Cells.Add(td);
td = new TableCell();
td.Style.Add("text-align", "center");
td.Attributes.Add("data-fieldname", "HIDDEN");
td.Attributes.Add("data-fieldvalue", ((svc == null) || (!svc.HIDDEN)) ? "true" : "false");
cb = new HtmlInputCheckBox();
cb.ID = "cbHidden_" + safeColName;
cb.Attributes.Add("class", "form-inline input-sm");
cb.Checked = ((svc == null) || (!svc.HIDDEN));
td.Controls.Add(cb);
//span = new HtmlGenericControl("SPAN");
//span.InnerHtml = ((svc == null) || (!svc.HIDDEN)) ? "True" : "False";
//td.Controls.Add(span);
tr.Cells.Add(td);
this.gvFields.Rows.Add(tr);
}
#endregion
#region Footer
tfoot = new TableFooterRow();
tfoot.TableSection = TableRowSection.TableFooter;
th = new TableHeaderCell();
th.Text = "Field Name";
tfoot.Cells.Add(th);
th = new TableHeaderCell();
th.Text = "Display Name";
tfoot.Cells.Add(th);
td = new TableCell();
tfoot.Cells.Add(td);
this.gvFields.Rows.Add(tfoot);
#endregion
}
catch (Exception e)
{
throw e;
// don't throw error here
}
}
protected void btnNext_Click(object sender, EventArgs e)
{
if (!this.ValidateParameters())
{
return;
}
CUSTOMRP.Model.REPORT myReport = null;
List <CUSTOMRP.Model.ColumnInfo> columninfos = null;
string path = g_Config["WordTemplatePath"];
string savedFileName = String.Empty;
try
{
savedFileName = this.SaveTemplateFile();
}
catch
{
this.lblJavascript.Text = String.Format("<script type=\"text/javascript\">alert(\"{0}\");</script>", AppNum.ErrorMsg.uploadFailed);
return;
}
string strReportName = this.txtReportName.Text.Trim();
int svID = Convert.ToInt32(this.ddlQueryName.SelectedValue);
#region Read CustomProps from file
System.Collections.Specialized.NameValueCollection nvc = QueryReport.Code.MailMerge.ReadCustomPropsFromFile(path + savedFileName);
if (String.IsNullOrWhiteSpace(strReportName))
{
strReportName = nvc["RptName"];
if (strReportName != null)
{
strReportName = strReportName.Trim();
}
}
string strQueryName = nvc["QueryName"];
if (!String.IsNullOrWhiteSpace(strQueryName))
{
CUSTOMRP.Model.SOURCEVIEW svFromFile = WebHelper.bllSOURCEVIEW.GetModelByQueryName(me.ID, strQueryName.Trim(), me.DatabaseID);
svID = (svFromFile != null) ? svFromFile.ID : -1;
}
#endregion
if (String.IsNullOrWhiteSpace(strReportName))
{
this.lblJavascript.Text = String.Format("<script type=\"text/javascript\">alert(\"{0}\");</script>",
String.Format(AppNum.ErrorMsg.fieldcannotbeempty, "Report Name"));
return;
}
if (svID == -1)
{
this.lblJavascript.Text = String.Format("<script type=\"text/javascript\">alert(\"{0}\");</script>",
String.Format(AppNum.ErrorMsg.pleaseselectvalidvaluefrom, "Query Name"));
return;
}
CUSTOMRP.Model.RPCATEGORY rpcat = WebHelper.bllcategory.GetModelList(me.ID, "DATABASEID='" + me.DatabaseID + "'").FirstOrDefault();
CUSTOMRP.Model.REPORTGROUP rpgrp = WebHelper.bllrpGroup.GetModelList(me.ID, "DATABASEID='" + me.DatabaseID + "'").FirstOrDefault();
#region Save columninfos
CUSTOMRP.Model.SOURCEVIEW sv = WebHelper.bllSOURCEVIEW.GetModel(me.ID, svID);
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 Save columninfos
#region myReport
DateTime currTime = DateTime.Now;
int UserID = me.ID;
myReport = new CUSTOMRP.Model.REPORT();
myReport.REPORTNAME = strReportName;
myReport.RPTITLE = myReport.REPORTNAME;
myReport.DATABASEID = me.DatabaseID;
myReport.SVID = svID;
myReport.CATEGORY = rpcat != null ? rpcat.ID : -1;
myReport.REPORTGROUPLIST = rpgrp != null ? rpgrp.ID : -1;
myReport.DEFAULTFORMAT = 0;
myReport.ADDUSER = UserID;
myReport.AUDODATE = currTime;
myReport.ADDUSER = UserID;
myReport.ReportColumns = new List <CUSTOMRP.Model.REPORTCOLUMN>();
foreach (CUSTOMRP.Model.ColumnInfo c in columninfos)
{
CUSTOMRP.Model.SOURCEVIEWCOLUMN svc = svclist.Where(x => x.COLUMNNAME == c.ColName).First();
myReport.ReportColumns.Add(new CUSTOMRP.Model.REPORTCOLUMN()
{
COLUMNNAME = c.ColName,
COLUMNFUNC = (int)CUSTOMRP.Model.REPORTCOLUMN.ColumnFuncs.Content,
CRITERIA1 = String.Empty,
CRITERIA2 = String.Empty,
CRITERIA3 = String.Empty,
CRITERIA4 = String.Empty,
AUDODATE = currTime,
SOURCEVIEWCOLUMNID = svc.ID,
DISPLAYNAME = svc.DISPLAYNAME,
COLUMNTYPE = (int)CUSTOMRP.Model.REPORTCOLUMN.ColumnTypes.Normal,
COLUMNCOMMENT = String.Empty,
HIDDEN = false,
});
}
myReport.WordFile = new CUSTOMRP.Model.WORDFILE();
myReport.WordFile.Description = String.Format("Word file for {0}", myReport.RPTITLE);
myReport.WordFile.OrigFileName = Path.GetFileName(fUploadTemplate.PostedFile.FileName).Replace(' ', '_');
myReport.WordFile.WordFileName = savedFileName;
myReport.WordFile.CreateDate = currTime;
myReport.WordFile.CreateUser = UserID;
myReport.WordFile.ModifyDate = currTime;
myReport.WordFile.ModifyUser = UserID;
#endregion myReport
Session[rpexcel.strSessionKeyMyReport] = myReport;
Session[rpexcel.strSessionKeyColumnInfo] = columninfos;
Response.Redirect("rpworddetail.aspx");
}