private static void dumpTable(TextWriter tw, Cinar.Database.Table tbl, DatabaseProvider dbProvider)
{
if (tbl == null)
{
throw new Exception("No such table.");
}
string delimitL = "[", delimitR = "]";
switch (dbProvider)
{
case DatabaseProvider.PostgreSQL:
delimitL = "\""; delimitR = "\"";
break;
case DatabaseProvider.MySQL:
delimitL = "`"; delimitR = "`";
break;
case DatabaseProvider.SQLServer:
delimitL = "["; delimitR = "]";
break;
default:
break;
}
string fields = delimitL + String.Join(delimitR + ", " + delimitL, tbl.Columns.ToStringArray()) + delimitR;
string sql = String.Format("insert into {2}{0}{3} ({1}) values ({{0}});", tbl.Name, fields, delimitL, delimitR);
DataTable dt = Provider.Database.GetDataTable("select * from [" + tbl.Name + "]");
foreach (DataRow dr in dt.Rows)
{
string[] values = new string[tbl.Columns.Count];
for (int i = 0; i < tbl.Columns.Count; i++)
{
string fieldName = tbl.Columns[i].Name;
if (dt.Columns[i].DataType == typeof(bool))
{
values[i] = dr.IsNull(fieldName) ? "null" : "'" + (dr[tbl.Columns[i].Name].Equals(true) ? 1 : 0) + "'";
}
else if (dt.Columns[i].DataType == typeof(DateTime))
{
values[i] = dr.IsNull(fieldName) ? "null" : "'" + ((DateTime)dr[tbl.Columns[i].Name]).ToString("yyyy-MM-dd HH:mm") + "'";
}
else
{
values[i] = dr.IsNull(fieldName) ? "null" : "'" + dr[tbl.Columns[i].Name].ToString().Replace("'", "''").Replace("\\", "\\\\").Replace("\n", "\\n").Replace("\r", "\\r") + "'";
}
}
tw.Write(sql, String.Join(", ", values));
tw.WriteLine();
}
}
internal override string show()
{
StringBuilder sb = new StringBuilder();
if (String.IsNullOrEmpty(EntityName))
{
return(Provider.GetResource("Select entity"));
}
Cinar.Database.Table tbl = Provider.Database.Tables[EntityName];
if (tbl == null)
{
return(Provider.GetResource("The table [entityName] coulnd't be found").Replace("[entityName]", EntityName));
}
string pageUrl = Provider.Request.Url.Scheme + "://" + Provider.Request.Url.Authority + Provider.Request.RawUrl;
CinarUriParser uriParser = new CinarUriParser(pageUrl);
if (!String.IsNullOrEmpty(Provider.Request["delete"]))
{
deleteEntity();
Provider.Response.Redirect(Provider.Request["returnUrl"], true);
return(String.Empty); //***
}
FilterParser filterParser = new FilterParser(this.Filter, EntityName);
string where = filterParser.GetWhere();
string sql = "";
if (string.IsNullOrWhiteSpace(this.SQL))
{
sql = String.Format(@"
select
{0}
from
{1}
where
{2} {3}
order by
{4} {5}",
"*",
this.EntityName,
defaultWhere,
String.IsNullOrEmpty(where) ? "" : ("and " + where),
this.OrderBy,
this.Ascending ? "asc" : "desc");
if (!ShowPaging)
{
sql = Provider.Database.AddLimitOffsetToSQL(sql, HowManyItems, Offset);
}
}
else
{
Interpreter engine = Provider.GetInterpreter(SQL, this);
engine.Parse();
engine.Execute();
sql = engine.Output;
if (ShowPaging && sql.ToLowerInvariant().Contains("limit"))
{
return("Do not use limit in SQL with paging. DataList does it.");
}
if (!sql.ToLowerInvariant().Contains("order by"))
{
sql += " order by " + this.OrderBy + " " + (this.Ascending ? "asc":"desc");
}
if (!ShowPaging && !sql.ToLowerInvariant().Contains("limit"))
{
sql = Provider.Database.AddLimitOffsetToSQL(sql, HowManyItems, Offset);
}
}
string countSQL = "";
if (ShowPaging)
{
countSQL = "SELECT count(*) " + sql.Substring(sql.IndexOf("from", StringComparison.InvariantCultureIgnoreCase));
if (countSQL.LastIndexOf("order by", StringComparison.InvariantCultureIgnoreCase) > -1)
{
countSQL = countSQL.Substring(0, countSQL.LastIndexOf("order by", StringComparison.InvariantCultureIgnoreCase));
}
if (countSQL.LastIndexOf("group by", StringComparison.InvariantCultureIgnoreCase) > -1)
{
countSQL = countSQL.Substring(0, countSQL.LastIndexOf("group by", StringComparison.InvariantCultureIgnoreCase));
}
if (countSQL.LastIndexOf("having", StringComparison.InvariantCultureIgnoreCase) > -1)
{
countSQL = countSQL.Substring(0, countSQL.LastIndexOf("having", StringComparison.InvariantCultureIgnoreCase));
}
sql = Provider.Database.AddPagingToSQL(sql,
HowManyItems,
pageNo);
}
data = Provider.Database.GetDataTable(sql, filterParser.GetParams());
Provider.Translate(EntityName, data);
if (data.Rows.Count == 0)
{
return("");
}
else
{
sb.Append(base.show());
}
// paging
if (this.ShowPaging)
{
/*
* <ul class="pagination mbn mtn">
* <li><a href="#"><i class="fa fa-angle-double-left"></i></a></li>
* <li><a href="#"><i class="fa fa-angle-left"></i></a></li>
* <li class="active"><a href="#">1</a></li>
* <li><a href="#">2</a></li>
* <li><a href="#">...</a></li>
* <li><a href="#">15</a></li>
* <li><a href="#"><i class="fa fa-angle-right"></i></a></li>
* <li><a href="#"><i class="fa fa-angle-double-right"></i></a></li>
* </ul>
*/
string prevPageLink = "", nextPageLink = "";
uriParser = new CinarUriParser(pageUrl);
if (AjaxPaging)
{
uriParser.Path = "/GetModuleHtml.ashx";
uriParser.QueryPart["name"] = "DataList";
uriParser.QueryPart["id"] = this.Id.ToString();
}
if (pageNo > 0)
{
uriParser.QueryPart["pageNo" + this.Id] = (pageNo - 1).ToString();
prevPageLink = String.Format("<li><a href=\"{0}\"{1}>{2}</a></li>",
AjaxPaging ? "javascript:void()" : uriParser.Uri.ToString(),
AjaxPaging ? " onclick=\"showDataListPage('" + uriParser.Uri + "', " + this.Id + ");\"" : "",
LabelPrevPage == "Previous Page" ? Provider.GetModuleResource("Previous Page") : LabelPrevPage);
}
int count = Provider.Database.GetInt(countSQL, filterParser.GetParams());
string pagingWithNumbers = ""; //"<div class='pagingWithNumbers'>";
for (int i = 0; i < Math.Ceiling((decimal)count / (decimal)HowManyItems); i++)
{
uriParser.QueryPart["pageNo" + this.Id] = i.ToString();
pagingWithNumbers += String.Format("<li class=\"{3}\"><a href=\"{0}\"{1}>{2}</a></li>",
AjaxPaging ? "javascript:void()" : uriParser.Uri.ToString(),
AjaxPaging ? " onclick=\"showDataListPage('" + uriParser.Uri + "', " + this.Id + ");\"" : "",
i + 1,
pageNo == i ? "active":"");
}
//pagingWithNumbers += "</div>";
if ((pageNo + 1) * HowManyItems < count)
{
uriParser.QueryPart["pageNo" + this.Id] = (pageNo + 1).ToString();
nextPageLink = String.Format("<li><a href=\"{0}\"{1}>{2}</a></li>",
AjaxPaging ? "javascript:void()" : uriParser.Uri.ToString(),
AjaxPaging ? " onclick=\"showDataListPage('" + uriParser.Uri + "', " + this.Id + ");\"" : "",
LabelNextPage == "Next Page" ? Provider.GetModuleResource("Next Page") : LabelNextPage);
}
if (!string.IsNullOrWhiteSpace(prevPageLink) || !string.IsNullOrWhiteSpace(nextPageLink))
{
sb.AppendFormat("<ul class=\"pagination mbn mtn\">{0} {1} {2}</ul>", prevPageLink, pagingWithNumbers, nextPageLink);
}
}
return(sb.ToString());
}
internal override string show()
{
StringBuilder sb = new StringBuilder();
if (String.IsNullOrEmpty(EntityName))
{
return(Provider.GetResource("Select entity"));
}
Cinar.Database.Table tbl = Provider.Database.Tables[EntityName];
if (tbl == null)
{
return(Provider.GetResource("The table [entityName] coulnd't be found").Replace("[entityName]", EntityName));
}
string pageUrl = Provider.Request.Url.Scheme + "://" + Provider.Request.Url.Authority + Provider.Request.RawUrl;
CinarUriParser uriParser = new CinarUriParser(pageUrl);
if (!String.IsNullOrEmpty(Provider.Request["delete"]))
{
deleteEntity();
uriParser.QueryPart.Remove("delete");
pageUrl = uriParser.ToString();
}
BaseEntity testEntity = Provider.CreateEntity(EntityName);
if (String.IsNullOrEmpty(ShowFields))
{
ShowFields = String.Format("{0},Visible", testEntity.GetNameColumn());
}
StringBuilder sbFrom = new StringBuilder();
sbFrom.AppendFormat("[{0}]\n", EntityName);
// generate SQL
string[] showFieldsArr = ShowFields.Split(',');
string[] showFieldsArrWithAs = new string[showFieldsArr.Length];
for (int i = 0; i < showFieldsArr.Length; i++)
{
string field = showFieldsArr[i];
PropertyInfo pi = testEntity.GetType().GetProperty(field);
EditFormFieldPropsAttribute attrib = (EditFormFieldPropsAttribute)CMSUtility.GetAttribute(pi, typeof(EditFormFieldPropsAttribute));
ColumnDetailAttribute fieldProps = (ColumnDetailAttribute)CMSUtility.GetAttribute(pi, typeof(ColumnDetailAttribute));
string caption = Provider.GetResource(pi.DeclaringType.Name + "." + pi.Name);
if (fieldProps.References != null)
{
BaseEntity testRefEntity = Provider.CreateEntity(fieldProps.References.Name);
showFieldsArrWithAs[i] = "T" + field + "." + testRefEntity.GetNameColumn() + " as [" + caption + "]";
sbFrom.AppendFormat("\tleft join [{0}] as {1} ON {1}.{2} = [{3}].{4}\n", fieldProps.References.Name, "T" + field, "Id", EntityName, field);
}
else
{
showFieldsArrWithAs[i] = EntityName + "." + field + " as [" + caption + "]";
}
}
ShowFields = String.Join(",", showFieldsArrWithAs);
ShowFields = EntityName + ".Id," + ShowFields;
FilterParser filterParser = new FilterParser(this.Filter, EntityName);
string where = filterParser.GetWhere();
int pageNo = 0;
Int32.TryParse(Provider.Request["pageNo"], out pageNo);
string sql = Provider.Database.AddPagingToSQL(String.Format(@"
select
{0}
from
{1}
where
1=1 {2}
order by
{3} {4}
",
ShowFields,
sbFrom.ToString(),
String.IsNullOrEmpty(where) ? "" : ("and " + where),
EntityName + "." + this.OrderBy,
this.Ascending ? "asc" : "desc"),
HowManyItems,
pageNo);
DataTable dt = Provider.Database.GetDataTable(sql, filterParser.GetParams());
sb.Append("<table cellpadding=\"0\" cellspacing=\"0\" border=\"0\">\n");
// header
sb.Append("<tr class=\"header\">\n");
for (int i = 0; i < dt.Columns.Count; i++)
{
if (i == 0)
{
continue;
}
string colName = dt.Columns[i].ColumnName;
string img = "";
uriParser.QueryPart["orderBy"] = showFieldsArr[i - 1];
if (this.OrderBy == showFieldsArr[i - 1])
{
uriParser.QueryPart["ascending"] = (!this.Ascending).ToString();
img = this.Ascending ? " (asc)" : " (desc)";
}
else
{
uriParser.QueryPart["ascending"] = "True";
}
sb.AppendFormat("<td><a href=\"{0}\">{1}</a>{2}</td>\n", uriParser.ToString(), colName, img);
}
if (this.Editable)
{
sb.AppendFormat("<td>{0}</td>\n", " ");
}
if (this.Deletable)
{
sb.AppendFormat("<td>{0}</td>\n", " ");
}
sb.Append("</tr>\n");
string nameField = testEntity.GetNameColumn();
// data
uriParser = new CinarUriParser(pageUrl);
foreach (DataRow dr in dt.Rows)
{
sb.Append("<tr class=\"data\">\n");
string editUrl = this.EditPage + "?item=" + dr[0] + "&returnUrl=" + Provider.Server.UrlEncode(Provider.Request.RawUrl);
for (int i = 0; i < dt.Columns.Count; i++)
{
if (i == 0)
{
continue;
}
object data = (dr.IsNull(i) || dr[i].Equals("")) ? " " : dr[i];
if (showFieldsArr[i - 1] == nameField && this.Editable)
{
data = "<a href=\"" + editUrl + "\">" + data + "</a>";
}
if (dr.Table.Columns[i].DataType == typeof(bool))
{
data = ((bool)data) ? Provider.GetResource("Yes") : Provider.GetResource("No");
}
sb.AppendFormat("<td>{0}</td>\n", data);
}
if (this.Deletable)
{
uriParser.QueryPart["delete"] = dr[0].ToString();
sb.AppendFormat("<td><span class=\"cbtn cdelete\" onclick=\"if(confirm('Kayýt silinecek!')) location.href='{0}'\"></span></td>\n", uriParser.ToString());
}
if (this.Editable)
{
sb.AppendFormat("<td><span class=\"cbtn cedit\" onclick=\"location.href='{0}'\"></span></td>\n", editUrl);
}
sb.Append("</tr>\n");
}
// paging
string prevPageLink = "", nextPageLink = "";
uriParser = new CinarUriParser(pageUrl);
if (pageNo > 0)
{
uriParser.QueryPart["pageNo"] = (pageNo - 1).ToString();
prevPageLink = String.Format("<a href=\"{0}\"><< {1}</a> ", uriParser.Uri.ToString(), Provider.GetModuleResource("Previous Page"));
}
if (dt.Rows.Count == HowManyItems)
{
uriParser.QueryPart["pageNo"] = (pageNo + 1).ToString();
nextPageLink = String.Format(" <a href=\"{0}\">{1} >></a>", uriParser.Uri.ToString(), Provider.GetModuleResource("Next Page"));
}
sb.AppendFormat("<tr class=\"footer\"><td colspan=\"100\">{0} {1}</td></tr>\n", prevPageLink, nextPageLink);
sb.Append("</table>\n");
if (!String.IsNullOrEmpty(NewRecordLink))
{
sb.AppendFormat("<p class=\"newRec\"><a href=\"{0}\">{1}</a></p>\n", this.EditPage + "?returnUrl=" + Provider.Server.UrlEncode(Provider.Request.RawUrl), NewRecordLink);
}
return(sb.ToString());
}
