public FormListStructure GenerateLookupViewStructure(ExcelConfiguration conf, string viewName, string directoryPath)
{
FormListStructure frm = new FormListStructure();
ExcelUIViewLookup view = GetLookupView(conf, viewName);
if (view != null)
{
string[] tables = view.TableName.Split(',');
ExcelTableSpec tableSpec = GetTableSpec(conf, tables[0],0);
string gridStr = "";
string buttonStr = "";
string select = "";
string from = tables[0];
string where = " 1=1 ";
string sql = "";
string dataSourceStr = "";
string dsSelectParam = "";
#region Gen Grid
gridStr = @"
<dx:ASPxGridView ID=""view" + view.ViewName + @""" runat=""server"" Width=""100%""
Styles-AlternatingRow-BackColor=""White"" Styles-Row-BackColor=""#D9EDF7""
AutoGenerateColumns=""False"" ClientInstanceName=""view" + view.ViewName + @"""
DataSourceID=""ds" + view.ViewName + @""" KeyFieldName=""" + GetPrimaryKeyName(tableSpec) + @""" Theme=""Metropolis"">
<Columns>
<dx:GridViewCommandColumn
ShowSelectButton =""True""
SelectAllCheckboxMode=""Page"" ShowClearFilterButton=""True""
ShowDeleteButton=""False"" ShowSelectCheckbox=""False"" ButtonType=""Image"" VisibleIndex=""30"">
</dx:GridViewCommandColumn>
<dx:GridViewDataTextColumn FieldName=""" + GetPrimaryKeyName(tableSpec) + @""" ReadOnly=""True"" Visible=""False"" VisibleIndex=""2"">
<EditFormSettings Visible=""False"" />
</dx:GridViewDataTextColumn>
";
int colNum = 2;
foreach (var fd in view.Items)
{
colNum++;
gridStr += @" <dx:GridViewDataTextColumn Caption=""" + fd.Caption + @""" FieldName=""" + GetOnlyFieldName(fd) + @""" VisibleIndex=""" + colNum + @"""></dx:GridViewDataTextColumn>
";
}
gridStr += @"</Columns>
<Settings ShowFilterRow=""True"" ShowGroupPanel=""False"" />
<SettingsBehavior ConfirmDelete=""true"" AllowSelectSingleRowOnly=""true"" />
<SettingsCommandButton>
<SelectButton>
<Image ToolTip=""เลือก"" Url=""Images/select.png"" />
</SelectButton>
</SettingsCommandButton>
<ClientSideEvents SelectionChanged=""grid_SelectionChanged"" />
</dx:ASPxGridView>
";
#endregion
#region Gen sql dataSourceStr
if (view.SQLSelect == null || view.SQLSelect == "")
{
foreach (var fd in tableSpec.Fields)
{
if (fd.FieldPrimaryKey || fd.FieldForeignKey)
{
select += tableSpec.TableName + "." + fd.FieldName + "\r\n,";
}
}
if (tables.Length > 1)
{
for (int i = 1; i < tables.Length; ++i)
{
from += GetJoinStr(conf, tables[0], tables[i]);
}
}
foreach (var item in view.Items)
{
if (item.DataBinding != null && item.DataBinding != "")
{
select += item.DataBinding + "\r\n,";
}
}
select = select.Substring(0, select.Length - 1);
if (view.InputParameters != null && view.InputParameters != "")
{
string[] fds = view.InputParameters.Split(',');
foreach (var f in fds)
{
where += " AND " + f + "=@" + f.Substring(f.IndexOf('.') + 1);
dsSelectParam += "<asp:Parameter Name=\"" + f.Substring(f.IndexOf('.') + 1) + "\" Type=\"Int32\" />";
}
}
if (view.FilterParameters != null && view.FilterParameters != "")
{
string[] fds = view.FilterParameters.Split(',');
foreach (var f in fds)
{
where += " AND " + f + "=@" + f.Substring(f.IndexOf('.') + 1);
dsSelectParam += "<asp:Parameter Name=\"" + f.Substring(f.IndexOf('.') + 1) + "\" Type=\"Int32\" />";
}
}
sql = "select " + select + " from " + from + " where " + where;
}
else
{
sql = view.SQLSelect;
if (view.SQLSelectParams != null && view.SQLSelectParams != "")
{
string[] fds = view.SQLSelectParams.Split(',');
foreach (var f in fds)
{
dsSelectParam += "<asp:Parameter Name=\"" + f.Substring(f.IndexOf('.') + 1) + "\" Type=\"Int32\" />";
}
}
}
//DefaultConnection
/*
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DefaultConnection %>"
DeleteCommand="DELETE FROM [COMMON_MASTER_ORGANIZE] WHERE [ORGANIZE_ID] = @PK_ID"
SelectCommand="SELECT * FROM [COMMON_MASTER_ORGANIZE]"
<DeleteParameters>
<asp:Parameter Name="ORGANIZE_ID" Type="Int32" />
</DeleteParameters>
</asp:SqlDataSource>
*/
dataSourceStr = @"<asp:SqlDataSource ID=""ds" + view.ViewName + @""" runat=""server"" ConnectionString=""<%$ ConnectionStrings:DefaultConnection %>""
DeleteCommand=""DELETE FROM " + tables[0] + @" WHERE [" + GetPrimaryKeyName(tableSpec) + @"] = @" + GetPrimaryKeyName(tableSpec) + @"""
SelectCommand=""" + sql + @""" >
<DeleteParameters>
<asp:Parameter Name=""" + GetPrimaryKeyName(tableSpec) + @""" Type=""Int32"" />
</DeleteParameters>
<SelectParameters>
" + dsSelectParam + @"
</SelectParameters>
</asp:SqlDataSource>";
#endregion
#region Gen PopulateData
//frm.PopulateCS += "//Grid \r\n";
//frm.PopulateCS += "ds" + view.ViewName + ".SelectParameters.Clear();\r\n";
frm.InitCS += "//Grid " + view.ViewName + " \r\n";
frm.InitCS += "ds" + view.ViewName + ".SelectParameters.Clear();\r\n";
if (view.InputParameters != null && view.InputParameters != "")
{
string[] fds = view.InputParameters.Split(',');
foreach (var f in fds)
{
frm.VariableDeclareCS += "string input" + f.Substring(f.IndexOf('.') + 1) + "=\"-1\";\r\n";
//frm.PopulateCS += "ds" + view.ViewName + ".SelectParameters.Add(\"" + f.Substring(f.IndexOf('.') + 1) + "\", System.Data.DbType.Int32, input" + f.Substring(f.IndexOf('.') + 1) + ");\r\n";
frm.InitCS += @" if (Request.QueryString[""" + f.Substring(f.IndexOf('.') + 1) + @"""] != null && Request.QueryString[""" + f.Substring(f.IndexOf('.') + 1) + @"""] != ""-1"")
{
input" + f.Substring(f.IndexOf('.') + 1) + @" = Request.QueryString[""" + f.Substring(f.IndexOf('.') + 1) + @"""];
}
";
frm.InitCS += "ds" + view.ViewName + ".SelectParameters.Add(\"" + f.Substring(f.IndexOf('.') + 1) + "\", System.Data.DbType.Int32, input" + f.Substring(f.IndexOf('.') + 1) + ");\r\n";
}
}
if (view.FilterParameters != null && view.FilterParameters != "")
{
string[] fds = view.FilterParameters.Split(',');
foreach (var f in fds)
{
frm.VariableDeclareCS += "string param" + f.Substring(f.IndexOf('.') + 1) + "=\"-1\";\r\n";
//frm.PopulateCS += "ds" + view.ViewName + ".SelectParameters.Add(\"" + f.Substring(f.IndexOf('.') + 1) + "\", System.Data.DbType.Int32, param" + f.Substring(f.IndexOf('.') + 1) + ");\r\n";
frm.InitCS += "ds" + view.ViewName + ".SelectParameters.Add(\"" + f.Substring(f.IndexOf('.') + 1) + "\", System.Data.DbType.Int32, param" + f.Substring(f.IndexOf('.') + 1) + ");\r\n";
}
}
if (view.SQLSelectParams != null && view.SQLSelectParams != "")
{
string[] fds = view.SQLSelectParams.Split(',');
foreach (var f in fds)
{
frm.VariableDeclareCS += "string param" + f.Substring(f.IndexOf('.') + 1) + "=\"-1\";\r\n";
frm.InitCS += "ds" + view.ViewName + ".SelectParameters.Add(\"" + f.Substring(f.IndexOf('.') + 1) + "\", System.Data.DbType.Int32, param" + f.Substring(f.IndexOf('.') + 1) + ");\r\n";
}
}
#endregion
#region Gen button
//buttonStr += @"<input type=""button"" id=""btn" + view.ViewName + @"_new"" class='btn btn-primary' value=' Add ' onclick="""" />";
#endregion
frm.HtmlTag = @"<div>
" + gridStr + @"
</div>";
frm.DataSourceTag = dataSourceStr;
frm.TopButtonTag = buttonStr;
return frm;
}
else
{
return frm;
}
}
public FormListStructure GenerateListViewStructure(ExcelConfiguration conf, string viewName, string directoryPath, bool isInListView)
{
FormListStructure frm = new FormListStructure();
ExcelUIViewList view = GetListView(conf, viewName);
if (view != null)
{
string[] tables = view.TableName.Split(',');
ExcelTableSpec tableSpec = GetTableSpec(conf, tables[0],0);
string gridStr = "";
string buttonStr = "";
string select = "";
string from = tables[0];
string where = " 1=1 ";
string sql = "";
string dataSourceStr = "";
string dsSelectParam = "";
#region Gen Grid
var editView = GetDetailView(conf, view.EditView);
try
{
string xx = editView.ViewName;
}
catch (Exception ex)
{
throw new ApplicationException("View " + view.ViewName + " ต้องการ View " + view.EditView + " ** แต่ไม่มี", ex);
}
var linkDetail = @"<a target=""_blank"" onclick=""OpenChildDetail('fancyBox" + view.ViewName + @"','" + view.EditView + @".aspx?" + editView.GetHyperlinkRequest() + @"', view" + view.ViewName + @");""><img src=""images/edit.png"" style=""border:none"" alt=""edit"" /></a>";
if (isInListView)
{
linkDetail = @"<a target=""_self"" href=""" + view.EditView + @".aspx?" + editView.GetHyperlinkRequest() + @"""><img src=""images/edit.png"" style=""border:none"" alt=""edit"" /></a>";
}
gridStr = @"
<dx:ASPxGridView ID=""view" + view.ViewName + @""" runat=""server"" Width=""100%""
AutoGenerateColumns=""False"" Styles-AlternatingRow-BackColor=""White"" Styles-Row-BackColor=""#D9EDF7""
DataSourceID=""ds" + view.ViewName + @""" KeyFieldName=""" + GetPrimaryKeyName(tableSpec) + @""" ClientInstanceName=""view" + view.ViewName + @""" Theme=""Metropolis"">
<Columns>
<dx:GridViewCommandColumn SelectAllCheckboxMode=""Page"" ShowClearFilterButton=""True""
ShowDeleteButton=""True"" ShowSelectCheckbox=""False"" ButtonType=""Image"" VisibleIndex=""0"">
</dx:GridViewCommandColumn>
<dx:GridViewDataTextColumn FieldName=""" + GetPrimaryKeyName(tableSpec) + @""" ReadOnly=""True"" Visible=""False"" VisibleIndex=""2"">
<EditFormSettings Visible=""False"" />
</dx:GridViewDataTextColumn>
<dx:GridViewDataTextColumn VisibleIndex=""1"" Caption="""">
<DataItemTemplate>
" + linkDetail + @"
</DataItemTemplate>
</dx:GridViewDataTextColumn>
";
int colNum = 2;
foreach (var fd in view.Items)
{
colNum++;
var f = GetFieldSpec(conf, tables[0], fd.DataBinding);
//frm.ValidateCS += GetValidateString(conf, f.FieldName);
frm.FomattingCS += GetFormattingListString(view, fd);
try
{
if (f==null || f.FieldType == ExcelEntityFieldType.String)
{
gridStr += @"
<dx:GridViewDataTextColumn Caption=""" + fd.Caption + @""" FieldName=""" + GetOnlyFieldName(fd) + @""" VisibleIndex=""" + colNum + @""">
</dx:GridViewDataTextColumn>
";
}
else if (f.FieldType == ExcelEntityFieldType.Number)
{
gridStr += @"
<dx:GridViewDataSpinEditColumn Caption=""" + fd.Caption + @""" FieldName=""" + GetOnlyFieldName(fd) + @""" VisibleIndex=""" + colNum + @""">
</dx:GridViewDataSpinEditColumn>
";
}
else if (f.FieldType == ExcelEntityFieldType.DateTime)
{
gridStr += @"
<dx:GridViewDataDateColumn Caption=""" + fd.Caption + @""" FieldName=""" + GetOnlyFieldName(fd) + @""" VisibleIndex=""" + colNum + @""">
</dx:GridViewDataDateColumn>
";
}
else if (f.FieldType == ExcelEntityFieldType.Boolean)
{
gridStr += @"
<dx:GridViewDataCheckColumn Caption=""" + fd.Caption + @""" FieldName=""" + GetOnlyFieldName(fd) + @""" VisibleIndex=""" + colNum + @""">
</dx:GridViewDataCheckColumn>
";
}
}
catch (Exception ex)
{
throw new ApplicationException("View " + view.ViewName + " ต้องการใช้ field [" + fd.DataBinding + "] แต่ไม่มีในตาราง [" + GetOnlyTableName(fd)+"]", ex);
}
}
gridStr += @"</Columns>
<Settings ShowFilterRow=""True"" ShowFilterRowMenu=""true"" ShowHeaderFilterButton=""false"" ShowGroupPanel=""False"" />
<SettingsBehavior ConfirmDelete=""true"" AllowSelectSingleRowOnly=""true"" />
<SettingsText ConfirmDelete=""ท่านต้องการลบรายการนี้หรือไม่?"" />
<SettingsCommandButton>
<DeleteButton>
<Image ToolTip=""ลบ"" Url=""Images/del.png"" />
</DeleteButton>
</SettingsCommandButton>
</dx:ASPxGridView>
<a id=""fancyBox" + view.ViewName + @""" class='fancyBox' style=""display:none""></a>
";
#endregion
#region Gen sql dataSourceStr
if (view.SQLSelect == null || view.SQLSelect == "")
{
ExcelTableSpec tb = GetTableSpec(conf, tables[0], 0);
foreach (var fd in tb.Fields)
{
if (fd.FieldPrimaryKey || fd.FieldForeignKey)
{
select += tb.TableName + "." + fd.FieldName + "\r\n,";
}
}
if (tables.Length > 1)
{
for (int i = 1; i < tables.Length; ++i)
{
from += GetJoinStr(conf, tables[0], tables[i]);
}
}
foreach (var item in view.Items)
{
if (item.DataBinding != null && item.DataBinding != "")
{
select += item.DataBinding + "\r\n,";
}
}
select = select.Substring(0, select.Length - 1);
if (view.InputParameters != null && view.InputParameters != "")
{
string[] fds = view.InputParameters.Split(',');
foreach (var f in fds)
{
where += " AND " + f + "=@" + f.Substring(f.IndexOf('.') + 1);
dsSelectParam += "<asp:Parameter Name=\"" + f.Substring(f.IndexOf('.') + 1) + "\" Type=\"Int32\" />";
}
}
if (view.FilterParameters != null && view.FilterParameters != "")
{
string[] fds = view.FilterParameters.Split(',');
foreach (var f in fds)
{
where += " AND " + f + "=@" + f.Substring(f.IndexOf('.') + 1);
dsSelectParam += "<asp:Parameter Name=\"" + f.Substring(f.IndexOf('.') + 1) + "\" Type=\"Int32\" />";
}
}
sql = "select " + select + " from " + from + " where " + where;
}
else
{
sql = view.SQLSelect;
}
//DefaultConnection
/*
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DefaultConnection %>"
DeleteCommand="DELETE FROM [COMMON_MASTER_ORGANIZE] WHERE [ORGANIZE_ID] = @PK_ID"
SelectCommand="SELECT * FROM [COMMON_MASTER_ORGANIZE]"
<DeleteParameters>
<asp:Parameter Name="ORGANIZE_ID" Type="Int32" />
</DeleteParameters>
</asp:SqlDataSource>
*/
dataSourceStr = @"
<asp:SqlDataSource ID=""ds" + view.ViewName + @""" runat=""server"" ConnectionString=""<%$ ConnectionStrings:DefaultConnection %>""
DeleteCommand=""DELETE FROM " + tables[0] + @" WHERE [" + GetPrimaryKeyName(tableSpec) + @"] = @" + GetPrimaryKeyName(tableSpec) + @"""
SelectCommand=""" + sql + @""" >
<DeleteParameters>
<asp:Parameter Name=""" + GetPrimaryKeyName(tableSpec) + @""" Type=""Int32"" />
</DeleteParameters>
<SelectParameters>
" + dsSelectParam + @"
</SelectParameters>
</asp:SqlDataSource>";
#endregion
#region Gen PopulateData
//frm.PopulateCS += "//Grid \r\n";
//frm.PopulateCS += "ds" + view.ViewName + ".SelectParameters.Clear();\r\n";
frm.InitCS += "//Grid "+ view.ViewName+" \r\n";
frm.InitCS += "ds" + view.ViewName + ".SelectParameters.Clear();\r\n";
if (view.InputParameters != null && view.InputParameters != "")
{
string[] fds = view.InputParameters.Split(',');
foreach (var f in fds)
{
frm.VariableDeclareCS += "string input" + f.Substring(f.IndexOf('.') + 1) + "=\"-1\";\r\n";
//frm.PopulateCS += "ds" + view.ViewName + ".SelectParameters.Add(\"" + f.Substring(f.IndexOf('.') + 1) + "\", System.Data.DbType.Int32, input" + f.Substring(f.IndexOf('.') + 1) + ");\r\n";
frm.InitCS += @" if (Request.QueryString[""" + f.Substring(f.IndexOf('.') + 1) + @"""] != null && Request.QueryString[""" + f.Substring(f.IndexOf('.') + 1) + @"""] != ""-1"")
{
input" + f.Substring(f.IndexOf('.') + 1) + @" = Request.QueryString[""" + f.Substring(f.IndexOf('.') + 1) + @"""];
}
";
frm.InitCS += "ds" + view.ViewName + ".SelectParameters.Add(\"" + f.Substring(f.IndexOf('.') + 1) + "\", System.Data.DbType.Int32, input" + f.Substring(f.IndexOf('.') + 1) + ");\r\n";
}
}
if (view.FilterParameters != null && view.FilterParameters != "")
{
string[] fds = view.FilterParameters.Split(',');
foreach (var f in fds)
{
frm.VariableDeclareCS += "string param" + f.Substring(f.IndexOf('.') + 1) + "=\"-1\";\r\n";
//frm.PopulateCS += "ds" + view.ViewName + ".SelectParameters.Add(\"" + f.Substring(f.IndexOf('.') + 1) + "\", System.Data.DbType.Int32, param" + f.Substring(f.IndexOf('.') + 1) + ");\r\n";
frm.InitCS += "ds" + view.ViewName + ".SelectParameters.Add(\"" + f.Substring(f.IndexOf('.') + 1) + "\", System.Data.DbType.Int32, param" + f.Substring(f.IndexOf('.') + 1) + ");\r\n";
}
}
if (view.SQLSelectParams != null && view.SQLSelectParams != "")
{
string[] fds = view.SQLSelectParams.Split(',');
foreach (var f in fds)
{
frm.VariableDeclareCS += "string param" + f.Substring(f.IndexOf('.') + 1) + "=\"-1\";\r\n";
frm.InitCS += "ds" + view.ViewName + ".SelectParameters.Add(\"" + f.Substring(f.IndexOf('.') + 1) + "\", System.Data.DbType.Int32, param" + f.Substring(f.IndexOf('.') + 1) + ");\r\n";
}
}
#endregion
#region Gen button
buttonStr += "";// @"<input type=""button"" id=""btn" + view.ViewName + @"_new"" class='btn btn-primary' value=' Add ' onclick=""location='" + view.EditView + ".aspx?id=-1';\" />";
#endregion
frm.HtmlTag = @"<div>
" + gridStr + @"
</div>";
frm.DataSourceTag = dataSourceStr;
frm.TopButtonTag = "";// buttonStr;
return frm;
}
else
{
return frm;
}
}
