public static int GetFieldMaxLength(XmlNode FieldNode)
{
if (FieldNode.Attributes[xAttribute.maxLength] == null)
{
return(0);
}
else
{
return(EvoTC.String2Int(FieldNode.Attributes[xAttribute.maxLength].Value));
}
}
static internal string GetDateDMY(string dateString)
{
bool ValidDate = false;
if (string.IsNullOrEmpty(dateString))
{
return(SQL_NULL);
}
char[] sepChars = { '/', '-' };
string[] dateParts = dateString.Split(sepChars);
if (dateParts.Length == 3)
{
if (EvoTC.String2Int(dateParts[2]) < 100)
{
dateParts[2] = (2000 + EvoTC.String2Int(dateParts[2])).ToString();
}
if (ServerUsesFrenchDates())
{
ValidDate = EvoTC.isDate(dateString);
}
else
{
ValidDate = EvoTC.isDate(string.Format("{1}/{0}/{2}", dateParts[0], dateParts[1], dateParts[2]));
}
if (ValidDate)
{
return(string.Format(SQL_DATEstr012, dateParts[0], dateParts[1], dateParts[2]));
}
else
{
return(SQL_NULL);
}
}
else
{
return(SQL_NULL);
}
}
private string FormMapDB_Objects()
{
//Strategies (proposal universes like BO)
StringBuilder myHTML = new StringBuilder();
StringBuilder html = new StringBuilder();
string sql, buffer, buffer2, pkName = "ID";
int j, f = 0;
DataSet ds = new DataSet();
string myTable, prevTable;
int myFormID = 0, myPanelID = 0;
StringBuilder sqlsb = new StringBuilder();
const string coma = ",";
sqlsb.Append("SELECT sc.id,so.name as dbtable,sc.name as dbcolumn,sc.xtype,sc.length,sc.isnullable ");
sqlsb.Append("FROM sysobjects as so, syscolumns as sc (nolock) ");
sqlsb.Append("WHERE sc.id=so.id AND ");
sqlsb.Append(whereMapDB);
if ((GetPageRequest("table2map") != null))
{
buffer = GetPageRequest("table2map").ToString();
sqlsb.Append(" AND so.id in (").Append(EvoDB.SQLescape(buffer)).Append(") ");
}
sqlsb.Append(" order by so.name,sc.colid");
ds = EvoDB.GetData(sqlsb.ToString(), _SqlConnection, ref ErrorMsg);
if (ds != null)
{
html.Append("<p>Your tables are roughly mapped. The Web UI is ready to use, refine, and tune.</p>");
prevTable = "";
//######### Form #########
int MaxLoop = ds.Tables[0].Rows.Count;
html.Append("<ul>");
for (int i = 0; i < MaxLoop; i++)
{
myTable = ds.Tables[0].Rows[i][xAttribute.dbTable].ToString();
myTable = myTable.Replace("'", "");
if (myTable != prevTable)
{
//get PK name
sqlsb = new StringBuilder();
sqlsb.Append("SELECT CU.Column_Name ");
sqlsb.Append("FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CU inner join ");
sqlsb.Append(" INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC on CU.Constraint_Name=TC.Constraint_Name ");
sqlsb.Append("WHERE CU.table_name = '").Append(myTable).Append("' AND TC.Constraint_Type='PRIMARY KEY' ");
sqlsb.Append("ORDER BY CU.Table_Name ");
pkName = EvoDB.GetDataScalar(sqlsb.ToString(), _SqlConnection, ref ErrorMessage).ToUpper();
if (string.IsNullOrEmpty(pkName))
{
pkName = "ID";
}
//+ dborder, spget, spdelete, entity, xmlfile) "
sqlsb = new StringBuilder();
sqlsb.AppendFormat("'{0}','{1}','{2}',", EvoTC.ToUpperLowers(myTable.Replace("_", " ")), myTable, pkName);
sqlsb.AppendFormat("'Definition obtained from scan table ''{0}'' with Evolutility wizard on {1}.'", myTable, EvoTC.TextNow());
sqlsb.Append(",'").Append(PagingSPCall);
sqlsb.Append("','EvoSP_Login @login, @password'");
sql = EvoDB.sqlINSERT("EvoDico_form", "title, dbtable, dbcolumnpk, description, sppaging, splogin", sqlsb.ToString());
sql += EvoDB.SQL_IDENTITY;
myFormID = EvoTC.String2Int(EvoDB.GetDataScalar(sql, _SqlConnectionDico, ref ErrorMessage));
if (myFormID > 0)
{
//######### Panel #########
myPanelID = EvoTC.String2Int(EvoDB.GetDataScalar("SELECT max(ID) FROM EvoDico_Panel WHERE FormID=" + myFormID.ToString(), _SqlConnectionDico, ref ErrorMessage));
if (myPanelID == 0)
{
sqlsb = new StringBuilder();
sqlsb.Append("INSERT INTO EvoDico_Panel (FormID, label, Width) VALUES(");
sqlsb.AppendFormat("{0},'{1}',100)", myFormID, myTable);
sqlsb.Append(EvoDB.SQL_IDENTITY);
myPanelID = EvoTC.String2Int(EvoDB.GetDataScalar(sqlsb.ToString(), _SqlConnectionDico, ref ErrorMessage));
}
}
else
{
ErrorMsg += "BAD SQL<br/>" + sql;
}
html.AppendFormat("<li>{0} : ", myTable);
html.Append(" <a href=\"evodicoTest.aspx?formID=").Append(myFormID).Append("\" target=\"r\">Run").Append(EvoUI.HTMLFlagPopup).Append("</a>");
html.Append(" - <a href=\"evodicoForm.aspx?ID=").Append(myFormID).Append("\" target=\"d\">Design").Append(EvoUI.HTMLFlagPopup).Append("</a></li>");
prevTable = myTable;
f = 0;
}
//######### Fields #########
//'buffer = CStr(ds.Tables[0].Rows[i][xAttribute.dbColumn))
buffer = ds.Tables[0].Rows[i][xAttribute.dbColumn].ToString();
if (buffer.ToUpper() != pkName)
{
f += 1;
sqlsb = new StringBuilder();
sqlsb.Append("INSERT INTO EvoDico_Field (FormID, fpos, label, dbcolumn, dbcolumnread, typeid, height, width, maxlength, required, panelid, search, searchlist, searchadv)");
sqlsb.AppendFormat(" VALUES ({0},", myFormID);
if (buffer == "title")
{
sqlsb.Append("5,");
}
else if (buffer == "name")
{
sqlsb.Append("1,");
}
else
{
sqlsb.AppendFormat("{0},", i * 10);
}
buffer2 = buffer.Replace("_", " ").Replace("'", "''");
j = buffer2.Length - 1;
if (j > 0)
{
buffer2 = EvoTC.ToUpperLowers(buffer2);
}
sqlsb.AppendFormat("'{0}','{1}','{1}',", buffer2, buffer);
j = -1;
int maxlength = EvoTC.String2Int(ds.Tables[0].Rows[i]["length"].ToString());
switch (EvoTC.String2Int(ds.Tables[0].Rows[i]["xtype"].ToString()))
{
case 127:
case 56:
case 52:
case 48:
case 36:
sqlsb.Append("10,1,20"); //"integer"
maxlength = 12;
break;
case 104:
sqlsb.Append("1,1,20"); //bool
maxlength = 0;
break;
case 61:
sqlsb.Append("17,1,30"); // t_datetime
maxlength = 25;
break;
case 106:
case 62:
case 60:
case 108:
case 59:
case 122:
sqlsb.Append("9,1,20"); //t_dec
maxlength = 12;
break;
case 58:
sqlsb.Append("2,1,30"); //t_date
maxlength = 20;
break;
case 189:
sqlsb.Append("18,1,30"); // t_time
maxlength = 12;
break;
case 99:
case 35:
sqlsb.Append("6,3,100"); // t_txtm
maxlength = maxlength / 2;
break;
default:
//175, 239, 231, 167
maxlength = maxlength / 2;
if (maxlength < 100)
{
sqlsb.Append("5,1,50"); //"text"
}
else if (maxlength < 200)
{
sqlsb.Append("5,1,100"); //"text"
}
else if (maxlength < 500)
{
sqlsb.Append("6,3,100"); //"textmul"
}
else
{
sqlsb.Append("6,5,100"); //"textmul"
}
break;
}
sqlsb.AppendFormat(",{0},", maxlength);
if (Convert.ToInt32(ds.Tables[0].Rows[i]["isnullable"]) > 0)
{
sqlsb.Append("0,");
}
else
{
sqlsb.Append("1,");
}
sqlsb.Append(myPanelID + coma);
if (f < 6)
{
sqlsb.Append("1,1"); //search, searchlist
}
else if (f < 20)
{
sqlsb.Append("1,0");
}
else
{
sqlsb.Append("0,0");
}
sqlsb.Append(",1)");
buffer = EvoDB.RunSQL(sqlsb.ToString(), _SqlConnectionDico, false);
if (buffer != string.Empty)
{
ErrorMsg += string.Format("BAD SQL<br/>{0}<br/>{1}", sqlsb.ToString(), buffer);
}
}
}
html.Append("</ul>");
}
else
{
html.Append(EvoUI.HTMLMessage("No database tables were found. There is nothing to map.", EvoUI.MsgType.Warn));
}
html.Append("<br/> ");
return(html.ToString());
}
private string JSEditDetails(XmlNodeList aNodeList, int gridID)
{
/// <summary>Generates JS for grid details editing.</summary>
/// <remarks>Can be used several times for several detail grids on the same form.</remarks>
StringBuilder js2 = new StringBuilder();
bool fieldReadOnly = false;
//already checked _DBAllowUpdateDetails and _DBAllowInsertDetails before calling function !
js2.AppendFormat("\n{0}:", gridID).Append("{flds:[ ");
nbFieldEditable = 0; //nbFieldEditable is a global variable
int nbFields = aNodeList.Count;
for (int i = 0; i < nbFields; i++)
{
XmlNode cn = aNodeList[i];
if (cn.NodeType == XmlNodeType.Element)
{
if (cn.Attributes[xAttribute.dbReadOnly] == null)
{
fieldReadOnly = false;
}
else
{
fieldReadOnly = EvoTC.String2Int(cn.Attributes[xAttribute.dbReadOnly].Value) > 0;
}
if (!fieldReadOnly)
{
nbFieldEditable += 1;
String fieldType = cn.Attributes[xAttribute.type].Value;
js2.Append("{").AppendFormat("i:{0},t:'{1}'", i + 1, fieldType);
if (cn.Attributes[xAttribute.required] != null)
{
js2.AppendFormat(",r:{0}", EvoTC.String2Int(cn.Attributes[xAttribute.required].Value));
}
switch (fieldType)
{
case EvoDB.t_lov:
js2.AppendFormat(",lov:{0}", HTMLlov(cn, String.Empty, s0, LOVFormat.JSON, 0));
break;
case EvoDB.t_bool:
String fieldFormat = EvoUI.PixCheck;
if (cn.Attributes[xAttribute.img] != null)
{
fieldFormat = cn.Attributes[xAttribute.img].Value.Replace("'", "\\'");
}
if (String.IsNullOrEmpty(fieldFormat))
{
fieldFormat = EvoUI.PixCheck;
}
js2.AppendFormat(",pix:'{0}'", fieldFormat);
break;
case EvoDB.t_text:
case EvoDB.t_txtm:
case EvoDB.t_email:
case EvoDB.t_html:
case EvoDB.t_url:
int ml = xAttribute.GetFieldMaxLength(cn);
if (ml > 0)
{
js2.AppendFormat(",ml:{0}", ml);
}
break;
}
js2.Append("},");
}
}
}
js2.Remove(js2.Length - 1, 1);
js2.Append("]},");
JSDetailsDone = (nbFieldEditable > 0);
return(js2.ToString());
}
private string InstallApp(string XMLDefFile)
{
StringBuilder myHTML = new StringBuilder();
bool xmlOK = true;
string sql, dbr = null, errorMsg = null;
string checkMark = EvoUI.HTMLImgCheckMark(IEbrowser, _PathPix);
myHTML.AppendFormat("<h2>{0}</h2>", XMLDefFile.Replace("_", ""));
if (!string.IsNullOrEmpty(XMLDefFile))
{
XmlDocument myDOM2 = new XmlDocument();
try
{
myDOM2.Load(FileNameWithMask(_PathXML + string.Format("Setup\\{0}.xml", XMLDefFile)));
}
catch //(Exception ex)
{
errorMsg = "XML not found or invalid";
xmlOK = false;
}
if (xmlOK)
{
string t = "";
XmlNode aNode = myDOM2.DocumentElement;
if (aNode.Name == "applicationcomponent")
{
sql = aNode["xml"].InnerText;
if (sql.StartsWith("<?xml"))
{
sql = xQuery.XMLHeader + sql;
}
aNode = aNode["sql"];
if (aNode.Attributes[xAttribute.dbTable] != null)
{
t = aNode.Attributes[xAttribute.dbTable].Value;
myHTML.AppendFormat("<p>DB Table: {0}</p>", t);
}
dicoXML2DB(sql, _SqlConnectionDico);
myHTML.Append("<div class=\"indent1\">");
if (string.IsNullOrEmpty(errorMsg))
{
myHTML.Append(checkMark).Append("Metadata<br>");
}
if (aNode.Attributes[xAttribute.dbTable] != null)
{
string tc = EvoDB.GetDataScalar(EvoDB.BuildSQL("count(*)", t), _SqlConnection, ref ErrorMsg);
if (EvoTC.isInteger(tc))
{
errorMsg = "Database table not created because it already exists.";
}
else
{
sql = aNode["create"].InnerText;
dbr = EvoDB.RunSQL(sql, _SqlConnection, false);
if (string.IsNullOrEmpty(dbr))
{
myHTML.Append(checkMark).Append("Database structure<br>");
}
else
{
errorMsg = dbr;
}
}
if (string.IsNullOrEmpty(dbr))
{
int i = EvoTC.String2Int(tc);
if (i == 0)
{
sql = aNode["seed"].InnerText;
dbr = EvoDB.RunSQL(sql, _SqlConnection, false);
if (string.IsNullOrEmpty(dbr))
{
myHTML.Append(checkMark).Append("Seed data<br>");
sql = aNode["sample"].InnerText;
dbr = EvoDB.RunSQL(sql, _SqlConnection, false);
if (string.IsNullOrEmpty(dbr))
{
myHTML.Append(checkMark).Append("Sample data<br>");
}
else
{
errorMsg = dbr;
}
}
}
}
else
{
errorMsg = dbr;
}
}
myHTML.Append("</div>");
}
}
if (!string.IsNullOrEmpty(errorMsg))
{
myHTML.Append(EvoUI.HTMLMessage(errorMsg, EvoUI.MsgType.Error));
}
}
myHTML.Append(HTMLToolsLink(FormID.ToString(), "")).Append("</p>");
return(myHTML.ToString());
}
private string dicoXML2DB(string XML, string sqlConnectionDico)
{
string sqlstart1, sqlstart2;
int FormID = 0, PanelID;
int maxLoopP = 0;
string sqllog = null;
Data def_Data = new Data();
StringBuilder allSQL = new StringBuilder();
try
{
myDOM.LoadXml(XML);
}
catch
{
allSQL = new StringBuilder();
allSQL.Append(EvoUI.HTMLMessage("<p>Invalid XML. Please validate your XML with Evolutility.xsd.</p>", EvoUI.MsgType.Error));
//allSQL.Append(HttpUtility.HtmlEncode(XML));
return(allSQL.ToString());
}
XmlNamespaceManager nsManager = new XmlNamespaceManager(new NameTable());
nsManager.AddNamespace("evo", xQuery.evoNameSpace);
XmlNode aNode = myDOM.DocumentElement;
if (aNode != null && aNode.Name == xElement.form)
{
if (aNode.Attributes[xAttribute.label] != null)
{
def_Data.title = aNode.Attributes[xAttribute.label].InnerXml;
}
try
{
def_Data = Data.Deserialize(aNode[xElement.data]);
}
catch
{
ErrorMsg = "Invalid XML file. The element 'data' must have attributes.";
}
}
StringBuilder sbSQL = new StringBuilder();
StringBuilder sqlFull = new StringBuilder();
if (string.IsNullOrEmpty(ErrorMsg))
{
//######### Form #########
sbSQL.Append("INSERT INTO EvoDico_Form(title,dbtable,dbcolumnpk,dbcolumnlead,dbwhere,dborder,sppaging,splogin,spget,spdelete,entity,entities,icon,description) ");
sbSQL.Append("VALUES('").Append(EvoDB.SQLescape(def_Data.title)).AppendFormat("','{0}','{1}','{2}','{3}','{4}','", def_Data.dbtable, def_Data.dbcolumnpk, def_Data.dbcolumnlead, def_Data.dbwhere, def_Data.dborder);
sbSQL.Append(EvoDB.SQLescape(def_Data.sppaging));
sbSQL.Append("','").Append(EvoDB.SQLescape(def_Data.splogin)).Append("','").Append(EvoDB.SQLescape(def_Data.spget)).Append("','");
sbSQL.Append(EvoDB.SQLescape(def_Data.spdelete)).AppendFormat("','{0}','{1}','{2}','", EvoDB.SQLescape(def_Data.entity), EvoDB.SQLescape(def_Data.entities), EvoDB.SQLescape(def_Data.icon));
sbSQL.AppendFormat("XML import on {0}')", EvoTC.TextNowTime());
sbSQL.Append(EvoDB.SQL_IDENTITY);
FormID = EvoTC.String2Int(EvoDB.GetDataScalar(sbSQL.ToString(), sqlConnectionDico, ref ErrorMessage));
if (FormID > 0 && string.IsNullOrEmpty(ErrorMsg))
{
//######### Panels #########
XmlNodeList aNodeListPanels = myDOM.DocumentElement.SelectNodes(xQuery.panel, nsManager);
maxLoopP = aNodeListPanels.Count - 1;
if (maxLoopP == -1)
{
aNodeListPanels = myDOM.DocumentElement.SelectNodes("//evo:tab/evo:panel", nsManager);
maxLoopP = aNodeListPanels.Count - 1;
}
sqlFull = new StringBuilder();
for (int p = 0; p <= maxLoopP; p++)
{
XmlNode pNode = aNodeListPanels[p];
sbSQL = new StringBuilder();
sbSQL.Append("INSERT INTO EvoDico_Panel (FormID, label, Width, cssclass, cssclasslabel) VALUES(");
sbSQL.AppendFormat("{0},", FormID);
sbSQL.AppendFormat("'{0}',", EvoDB.SQLescape(pNode.Attributes[xAttribute.label].Value));
sbSQL.AppendFormat("{0},", pNode.Attributes[xAttribute.width].Value);
if (pNode.Attributes[xAttribute.cssClass] != null)
{
sbSQL.AppendFormat("'{0}',", EvoDB.SQLescape(pNode.Attributes[xAttribute.cssClass].Value));
}
else
{
sbSQL.Append("'',");
}
if (pNode.Attributes[xAttribute.cssClassLabel] != null)
{
sbSQL.AppendFormat("'{0}')", EvoDB.SQLescape(pNode.Attributes[xAttribute.cssClassLabel].Value));
}
else
{
sbSQL.Append("'')");
}
sbSQL.Append(EvoDB.SQL_IDENTITY);
PanelID = EvoTC.String2Int(EvoDB.GetDataScalar(sbSQL.ToString(), sqlConnectionDico, ref ErrorMessage));
if (PanelID > 0 && string.IsNullOrEmpty(ErrorMsg))
{
//######### Fields #########
sqlstart1 = "INSERT INTO EvoDico_Field (FormID,";
sqlstart2 = string.Format(") VALUES ({0},", FormID);
foreach (XmlNode aNode2 in pNode.ChildNodes)
{
StringBuilder sbSQL1 = new StringBuilder();
StringBuilder sbSQL2 = new StringBuilder();
sbSQL1.Append(sqlstart1);
sbSQL2.Append(sqlstart2);
foreach (XmlAttribute aAttribute2 in aNode2.Attributes)
{
string buffer2 = aAttribute2.Name;
string buffer = aAttribute2.Value;
switch (buffer2)
{
case "panelid":
break;
default:
if (EvoTC.isInteger(buffer))
{
sbSQL1.AppendFormat("[{0}],", buffer2);
sbSQL2.AppendFormat("{0},", buffer);
}
else
{
if (buffer2 == xAttribute.type)
{
sbSQL1.Append("typeid,");
sbSQL2.AppendFormat("{0},", FieldTypeID(buffer));
}
else if (buffer != string.Empty)
{
sbSQL1.AppendFormat("[{0}],", buffer2);
sbSQL2.AppendFormat("'{0}',", EvoDB.SQLescape(buffer));
}
}
break;
}
}
sqlFull.Append(sbSQL1).Append("PanelID").Append(sbSQL2).Append(PanelID).Append(");\n");
}
}
}
string buff = EvoDB.RunSQL(sqlFull.ToString(), sqlConnectionDico, true);
if (!string.IsNullOrEmpty(buff))
{
ErrorMsg += buff;
}
//'######### Panels Details #########
//aNodeListPanels = myDOM.DocumentElement.SelectNodes("//panel-details", nsManager)
//maxLoopP = aNodeListPanels.Count - 1
//sqlstart1 = "INSERT INTO EvoDico_FieldDetails (FormID,"
//For p = 0 To maxLoopP
// With aNodeListPanels[p]
// Sql = "INSERT INTO EvoDico_Panel (TypeID, FormID, label, Width, dbtabledetails, dbcolumndetails) VALUES(2," & CInt(FormID) & ",'"
// Sql += .Attributes(xAttribute.label).Value & "'," & .Attributes(xAttribute.width).Value & ",'" & .Attributes(xAttribute.dbTableDetails).Value & "','" & .Attributes(xAttribute.dbColumnDetails).Value & "')"
// Buffer = EvoDB.RunSQL(Sql, sqlConnectionDico, True)
// If Buffer = "" Then
// PanelID = CInt(GetDataScalar(EvoDB.BuildSQL("max(ID)", "EvoDico_Panel", "TypeID=2 AND FormID=" & CStr(FormID)), sqlConnectionDico))
// '######### Fields Details #########
// sqlstart2 = ") VALUES (" & FormID & coma
// sqlFull = ""
// For Each aNode In .ChildNodes
// Sql = sqlstart1
// sql2 = sqlstart2
// For Each aAttribute In aNode.Attributes
// Buffer = aAttribute.Value
// dbcolumn = aAttribute.Name
// If InStr("-type-panelid-panelindex-label-dbcolumn-dbcolumnread-dbcolumnimg-dbtablelov-dborderlov-dbcolumnreadlov-dblovcolumn-dbwherelov-validationrule-maxlength-readonly-required-optional-format-fpos-link-linklabel-linktarget-searchlist-cssclass-width-", "-" & LCase(dbcolumn) & "-") > 0 Then
// If IsNumeric(Buffer) AndAlso Not dbcolumn = xAttribute.format Then
// Sql += dbcolumn & coma
// sql2 += Buffer & coma
// Else
// If aAttribute.Name = xAttribute.type Then
// Sql += "typeid,"
// sql2 += FieldTypeID(Buffer) & coma
// Else
// If Buffer <> String.Empty Then
// Sql += "[" & dbcolumn & "],"
// sql2 += "'" & EvoDB.SQLescape(Buffer) & "',"
// End If
// End If
// End If
// End If
// Next
// 'If InStr(sql, "panelid") = 0 Then
// ' sql += "PanelID,"
// ' sql2 += CStr(Val(PanelID))
// 'End If
// sqlFull += Sql & "userid" & sql2 & UserID & ");"
// Next
// End If
// End With
// Buffer = EvoDB.RunSQL(sqlFull, sqlConnectionDico, True)
// If Buffer <> String.Empty Then sqllog += Buffer & vbCrLf & Sql & vbCrLf2
//Next
//End If
if (!string.IsNullOrEmpty(sqllog))
{
ErrorMsg = sqllog.Replace("\n\n\n", "");
}
}
}
string retVal = String.Format("{0} {1}", def_Data.title, HTMLToolsLink(FormID.ToString(), ""));
def_Data = null;
nsManager = null;
myDOM = null;
return(retVal);
}
private string HTMLDataset(XmlNodeList aNodeList, string sql, string title, int ListMode, string LinkDetailNew, bool InsidePanel, int PanelDetailsIndex, int PanelDetailsID)
{
// Generates HTML for paging grid, details grid, and edit grid.
//ListMode: 0=list, 1=details, 2 details edit
string ctable, iconEntityDetails;
bool UseComments = false, YesNo = false;
string fieldType, fieldValue;
string endTD = " </td>";
int nbCommentsRow = 0, MaxLoopXML, MaxLoopSQL, MinLoop, TotalNbRecords = 0;
string htmlRecCount = String.Empty, buffer, buffer1, buffer2;
StringBuilder myHTML = new StringBuilder();
StringBuilder myHTML2 = new StringBuilder();
string[,] fieldFormats;
DataSet ds;
DataTable t = null;
string myLabel;
if (!_DBAllowUpdateDetails && ListMode == 2)
{
ListMode = 1;
}
//set record counts values
MaxLoopSQL = -1;
if (string.IsNullOrEmpty(sql))
{
// PanelDetailsID??? bug qqq
if (detailsLoaded && PanelDetailsIndex > -1 && ds2 != null)
{
t = ds2.Tables[PanelDetailsIndex];
MaxLoopSQL = t.Rows.Count - 1;
}
}
else
{
ds = EvoDB.GetData(sql, SqlConnection, ref ErrorMsg);
if (ds != null)
{
t = ds.Tables[0];
MaxLoopSQL = t.Rows.Count - 1;
}
}
if (sql.Length > 0)
{
if (string.IsNullOrEmpty(ErrorMsg))
{
if (ListMode == 0 && !string.IsNullOrEmpty(def_Data.sppaging))
{
int nbRows = 0;
if (MaxLoopSQL == _RowsPerPage - 1)
{
try
{
nbRows = Convert.ToInt32(t.Rows[0]["MoreRecords"]);
}
catch
{ }
}
if (MaxLoopSQL > -1)
{
TotalNbRecords = _RowsPerPage * (pageID - 1) + nbRows + MaxLoopSQL + 1;
}
else
{
TotalNbRecords = 0;
}
}
else
{
TotalNbRecords = MaxLoopSQL + 1;
}
htmlRecCount = HTMLrecordCount(TotalNbRecords, MaxLoopSQL);
}
else
{
MaxLoopSQL = -1;
TotalNbRecords = MaxLoopSQL + 1;
htmlRecCount = string.Empty;
}
}
if (MaxLoopSQL > -1 || ListMode == 2)
{
MaxLoopXML = aNodeList.Count - 1;
//display result
if (InsidePanel)
{
myHTML.Append("<table class=\"Holder\" cellpadding=\"4\"");
if (PanelDetailsID > -1)
{
myHTML.AppendFormat(" ID=\"{0}{1}P\">", UID, PanelDetailsID);
}
else
{
myHTML.Append(">");
}
}
if (ListMode == 0)
{
//If dbcolumnlead <> String.Empty Then myHTML.Append("<tr><td colspan=""2"">").Append(HTMLAlphabetLinks).Append("</td></tr>")
myHTML.Append("<tr valign=\"top\" class=\"RowInfo\"><td><p>").Append(title);
if (_ShowDesigner && _FormID > 0)
{
myHTML.Append(EvoUI.LinkDesigner(EvoUI.DesType.src, _FormID, title, _PathDesign));
}
myHTML.Append("</p></td><td><p class=\"Right\">");
if (TotalNbRecords > 0 && (_DBAllowExport || _DBAllowMassUpdate))
{
if (_DBAllowExport)
{
htmlRecCount += String.Format(" - {0}", EvoUI.HTMLLinkEventRef("70", EvoLang.Export));
}
#if !DB_MySQL
if (_DBAllowMassUpdate)
{
htmlRecCount += String.Format(" - {0}", EvoUI.HTMLLinkEventRef("80", EvoLang.MassUpdate));
}
#endif
myHTML.Append(htmlRecCount);
}
myHTML.Append("</p></td></tr><tr><td colspan=\"2\">");
}
else if (InsidePanel)
{
myHTML.Append("<tr><td>");
}
if (MaxLoopSQL > -1 || (ListMode == 2 && _DBAllowInsertDetails))
{
myHTML.AppendFormat("<span id=\"{0}{1}p\"><table id=\"EvoEditGrid{1}\"", UID, PanelDetailsID);
myHTML.Append(" class=\"EvoEditGrid\" rules=\"all");
if (ListMode == 0 && ColorTranslator.ToHtml(BackColorRowMouseOver) != string.Empty)
{
myHTML.Append("\" style=\"behavior:url(").Append(_PathPixToolbar).Append("tablehl.htc);\" slcolor=\"#FFFFCC\" hlcolor=\"").Append(ColorTranslator.ToHtml(BackColorRowMouseOver));
}
myHTML.Append("\">");
//1 field only
if (MaxLoopXML == 0)
{
if (aNodeList[0].Attributes[xAttribute.labelList] == null)
{
myLabel = aNodeList[0].Attributes[xAttribute.label].Value;
}
else
{
myLabel = aNodeList[0].Attributes[xAttribute.labelList].Value;
}
}
else
{
myLabel = Tilda;
}
//##### table header
#region "header"
MaxLoopXML = aNodeList.Count - 1;
fieldFormats = new string[MaxLoopXML + 1, 3];
myHTML2.Append("<THEAD><tr class=\"RowHeader\">");
//edit details
if (ListMode == 2)
{
myHTML2.Append("<td width=\"1%\">ID</td>");
endTD = "</td>";
}
for (int j = 0; j <= MaxLoopXML; j++)
{
XmlNode cn = aNodeList[j];
if (cn.NodeType == XmlNodeType.Element)
{
//cache field format
ctable = string.Format(".{0}", cn.Attributes[xAttribute.dbColumnRead].Value);
switch (cn.Attributes[xAttribute.type].Value)
{
case EvoDB.t_text:
case EvoDB.t_pix:
if (ListMode < 2)
{
if (cn.Attributes[xAttribute.link] == null)
{
fieldFormats[j, 0] = string.Empty;
}
else
{
fieldFormats[j, 0] = cn.Attributes[xAttribute.link].Value;
}
if (cn.Attributes[xAttribute.linkTarget] == null)
{
fieldFormats[j, 1] = string.Empty;
}
else
{
fieldFormats[j, 1] = cn.Attributes[xAttribute.linkTarget].Value;
}
}
else
{
fieldFormats[j, 0] = string.Empty;
}
break;
case EvoDB.t_lov:
if (ListMode < 2)
{
ctable = string.Format("@{0}", cn.Attributes[xAttribute.dbColumn].Value);
if (cn.Attributes[xAttribute.link] == null)
{
buffer = string.Empty;
}
else
{
buffer = cn.Attributes[xAttribute.link].Value;
if (buffer.Length > 0)
{
if (cn.Attributes[xAttribute.linkTarget] == null)
{
fieldFormats[j, 1] = string.Empty;
}
else
{
fieldFormats[j, 1] = cn.Attributes[xAttribute.linkTarget].Value;
}
if (cn.Attributes[xAttribute.linkLabel] == null)
{
fieldFormats[j, 2] = string.Empty;
}
else
{
fieldFormats[j, 2] = cn.Attributes[xAttribute.linkLabel].Value;
}
}
}
}
else
{
buffer = string.Empty;
}
fieldFormats[j, 0] = buffer;
break;
case EvoDB.t_int:
case EvoDB.t_dec:
if (ListMode == 2)
{
if (cn.Attributes[xAttribute.dbReadOnly] == null)
{
YesNo = false;
}
else
{
YesNo = cn.Attributes[xAttribute.dbReadOnly].Value.Equals(s1);
}
}
else
{
YesNo = true;
}
if (YesNo)
{
if (cn.Attributes[xAttribute.format] == null)
{
fieldFormats[j, 0] = string.Empty;
}
else
{
fieldFormats[j, 0] = cn.Attributes[xAttribute.format].Value;
}
}
else
{
fieldFormats[j, 0] = string.Empty;
}
break;
case EvoDB.t_date:
case EvoDB.t_time:
case EvoDB.t_datetime:
if (cn.Attributes[xAttribute.format] == null)
{
fieldFormats[j, 0] = EvoTC.DefaultDateFormat(cn.Attributes[xAttribute.type].Value);
}
else
{
fieldFormats[j, 0] = cn.Attributes[xAttribute.format].Value;
if (string.IsNullOrEmpty(fieldFormats[j, 0]))
{
fieldFormats[j, 0] = EvoTC.DefaultDateFormat(cn.Attributes[xAttribute.type].Value);
}
else if (fieldFormats[j, 0].IndexOf("{") < 0)
{
fieldFormats[j, 0] = "{0:" + fieldFormats[j, 0] + "}";
}
}
break;
case EvoDB.t_bool:
if (cn.Attributes[xAttribute.imgList] != null)
{
fieldFormats[j, 0] = cn.Attributes[xAttribute.imgList].Value;
}
if (string.IsNullOrEmpty(fieldFormats[j, 0]))
{
if (cn.Attributes[xAttribute.img] == null)
{
fieldFormats[j, 0] = EvoUI.PixCheck;
}
else
{
fieldFormats[j, 0] = cn.Attributes[xAttribute.img].Value;
if (fieldFormats[j, 0].Length == 0)
{
fieldFormats[j, 0] = EvoUI.PixCheck;
}
}
}
break;
case EvoDB.t_url:
buffer = string.Empty;
if (cn.Attributes[xAttribute.imgList] != null)
{
buffer = cn.Attributes[xAttribute.imgList].Value;
}
if (buffer.Length == 0 && (cn.Attributes[xAttribute.img] != null))
{
buffer = cn.Attributes[xAttribute.img].Value;
}
if (buffer.Length > 0)
{
buffer = _PathPixToolbar + buffer;
}
fieldFormats[j, 0] = buffer;
break;
case EvoDB.t_formula:
ctable = Tilda + cn.Attributes[xAttribute.dbColumnRead].Value;
if (cn.Attributes[xAttribute.format] == null)
{
fieldFormats[j, 0] = string.Empty;
}
else
{
fieldFormats[j, 0] = cn.Attributes[xAttribute.format].Value;
}
break;
default:
fieldFormats[j, 0] = string.Empty;
break;
}
// built header
if (cn.Attributes[xAttribute.labelList] == null)
{
myLabel = cn.Attributes[xAttribute.label].Value;
}
else
{
myLabel = cn.Attributes[xAttribute.labelList].Value;
}
myHTML2.Append("<td>");
if (j == 0 && string.IsNullOrEmpty(myLabel))
{
myHTML2.Append(HTMLlov(cn, cn.Attributes[xAttribute.dbColumnRead].Value, s0, LOVFormat.HTML, 0));
}
else
{
// Label + Flag & Actions (required, designer, sorting)
myHTML2.Append(myLabel);
if (ListMode == 2 && cn.Attributes[xAttribute.required] != null && cn.Attributes[xAttribute.required].Value == s1)
{
myHTML2.Append(EvoUI.HTMLFlagRequired);
}
if (_ShowDesigner)
{
myHTML2.Append(EvoUI.LinkDesigner(EvoUI.DesType.fld, Convert.ToInt32(cn.Attributes[xAttribute.id].Value), myLabel, _PathDesign));
}
if (_AllowSorting && ListMode == 0 && TotalNbRecords > 2)
{
myHTML2.Append(HTMLSortingArrows(ctable));
}
}
myHTML2.Append("</td>");
}
}
myHTML2.Append("</tr></THEAD>");
if (MaxLoopSQL == 0 || MaxLoopXML > 0 || !String.IsNullOrEmpty(myLabel))
{
myHTML.Append(myHTML2);
}
#endregion
//##### table body
myHTML.Append("<TBODY>");
if (ListMode == 2 && (_DBAllowInsertDetails || _DBAllowUpdateDetails) && !_DBReadOnly)
{
genJS.Append(JSEditDetails(aNodeList, PanelDetailsID)); //sets nbFieldEditable
}
if (ListMode == 2 && MaxLoopSQL < 0)
{
myHTML.Append(EvoUI.HTMLemptyRowEdit(MaxLoopXML + 2));
}
else
{
UseComments = EvoDB.ColumnExists(t, SQLColNbComments);
}
iconEntityDetails = string.IsNullOrEmpty(icon) ? string.Empty : EvoUI.HTMLIcon(_PathPixToolbar, icon);
for (int i = 0; i <= MaxLoopSQL; i++)
{
myHTML.Append(EvoUI.TRcssEvenOrOdd(YesNo));
YesNo = !YesNo;
DataRow ri = t.Rows[i];
buffer2 = (ri[dbPrimaryKey].ToString());
if (UseComments)
{
if (ri[SQLColNbComments] != null)
{
try
{
nbCommentsRow = Convert.ToInt32(ri[SQLColNbComments]);
}
catch
{
nbCommentsRow = -1;
}
}
else
{
nbCommentsRow = -1;
}
}
switch (ListMode)
{
case 2:
//details edit
myHTML.AppendFormat("<td>{0}</td>", buffer2);
MinLoop = 0;
break;
case 0:
//search result (master)
myHTML.AppendFormat("<td><a href=\"javascript:EvPost('-{0}')\">", buffer2);
//0=ID
fieldValue = Convert.ToString(ri[aNodeList[0].Attributes[xAttribute.dbColumnRead].Value]);
if (string.IsNullOrEmpty(icon))
{
if (!String.IsNullOrEmpty(dbcolumnicon))
{
buffer = Convert.ToString(ri[dbcolumnicon]);
if (!String.IsNullOrEmpty(buffer))
{
myHTML.Append(EvoUI.HTMLIcon(_PathPix, buffer));
}
}
}
else
{
myHTML.Append(icon);
}
if (String.IsNullOrEmpty(fieldValue))
{
myHTML.AppendFormat("({0})</a>", buffer2);
}
else
{
myHTML.Append(HttpUtility.HtmlEncode(fieldValue)).Append("</a>");
}
if (nbCommentsRow > 0)
{
myHTML.Append(EvoUI.HTMLCommentFlag(nbCommentsRow));
}
myHTML.Append(" </td>");
//If EditLink Then .Append(" <small>[<a href=""Javascript:").Append(Page.GetPostBackEventReference(Me, "e" & buffer2)).Append(""">Edit</a>]<small>")
MinLoop = 1;
break;
default:
//details list=1
MinLoop = 0;
if (string.IsNullOrEmpty(icon) && dbcolumnicon.Length > 0)
{
if (object.ReferenceEquals(ri[dbcolumnicon], DBNull.Value))
{
iconEntityDetails = string.Empty;
}
else
{
iconEntityDetails = EvoUI.HTMLIcon(_PathPix, Convert.ToString(ri[dbcolumnicon]));
}
}
break;
}
for (int j = MinLoop; j <= MaxLoopXML; j++)
{
XmlNode cn = aNodeList[j];
if (cn.NodeType == XmlNodeType.Element)
{
myHTML.Append("<td>");
string dbColumnReadName = cn.Attributes[xAttribute.dbColumnRead].Value;
try
{
if (ri[dbColumnReadName] != null)
{
fieldValue = ri[dbColumnReadName].ToString();
}
else
{
fieldValue = string.Empty;
}
}
catch
{
string newError = string.Format(EvoLang.err_NoDBColumn, dbColumnReadName);
AddError(newError);
fieldValue = string.Empty;
}
fieldType = cn.Attributes[xAttribute.type].Value;
if (!String.IsNullOrEmpty(fieldValue))
{
switch (fieldType)
{
case EvoDB.t_text:
fieldValue = HttpUtility.HtmlEncode(fieldValue);
if (j == MinLoop)
{
if (ListMode == 1 && fieldFormats[j, 0].Length > 0)
{
if (!String.IsNullOrEmpty(iconEntityDetails))
{
fieldValue = iconEntityDetails + fieldValue;
}
myHTML.Append(EvoUI.HTMLLink(EvoUI.Link4itemid(fieldFormats[j, 0], ri[dbPrimaryKey].ToString()), fieldValue));
}
else
{
myHTML.Append(fieldValue);
}
if (UseComments && ListMode > 0 && ri[dbColumnReadName] != null)
{
nbCommentsRow = Convert.ToInt32(ri[SQLColNbComments]);
if (nbCommentsRow > 0)
{
myHTML.Append(EvoUI.HTMLCommentFlag(nbCommentsRow));
}
}
}
else if (ListMode == 1 && fieldFormats[j, 0].Length > 0)
{
myHTML.Append(EvoUI.HTMLLink(EvoUI.Link4itemid(fieldFormats[j, 0], ri[dbPrimaryKey].ToString()), fieldValue, fieldFormats[j, 1], null));
}
else
{
myHTML.Append(fieldValue);
}
break;
case EvoDB.t_lov:
if (ListMode != 2)
{
if (fieldFormats[j, 0].Length > 0)
{
if (fieldFormats[j, 2].Length > 0)
{
fieldValue = fieldFormats[j, 2];
}
//linklabel
fieldValue = EvoUI.HTMLLink(EvoUI.Link4itemid(fieldFormats[j, 0], ri[cn.Attributes[xAttribute.dbColumn].Value].ToString()), fieldValue, fieldFormats[j, 1], null);
}
else if (cn.Attributes[xAttribute.dbColumnIcon] != null)
{
buffer1 = cn.Attributes[xAttribute.dbColumnIcon].Value;
if (buffer1.Length > 0)
{
buffer2 = Convert.ToString(ri[buffer1]);
if (!String.IsNullOrEmpty(buffer2))
{
fieldValue = EvoUI.HTMLImg(_PathPix + buffer2) + EvoUI.HTMLSpace + fieldValue;
}
}
}
}
myHTML.Append(fieldValue);
break;
case EvoDB.t_bool: // format is a picture name here
if (fieldValue.Equals(s1) || fieldValue.Equals("True"))
{
if (fieldFormats[j, 0].Length > 0)
{
myHTML.Append(EvoUI.HTMLSpace).Append(EvoUI.HTMLImg(_PathPixToolbar + fieldFormats[j, 0], EvoLang.Checked));
}
else
{
myHTML.Append(EvoUI.HTMLImgCheckMark(IEbrowser, _PathPixToolbar));
}
}
break;
case EvoDB.t_int:
if (fieldFormats[j, 0].Length > 0 && EvoTC.isInteger(fieldValue))
{
myHTML.Append(EvoUI.noBR(EvoTC.String2Int(fieldValue).ToString(fieldFormats[j, 0])));
}
else
{
myHTML.Append(fieldValue);
}
break;
case EvoDB.t_dec:
if (fieldFormats[j, 0].Length > 0 && EvoTC.isDecimal(fieldValue))
{
myHTML.Append(EvoUI.noBR(EvoTC.String2Dec(fieldValue).ToString(fieldFormats[j, 0])));
}
else
{
myHTML.Append(fieldValue);
}
break;
case EvoDB.t_date:
case EvoDB.t_time:
case EvoDB.t_datetime:
if (fieldFormats[j, 0].Length > 0 && EvoTC.isDate(fieldValue))
{
myHTML.Append(EvoUI.noBR(string.Format(fieldFormats[j, 0], EvoTC.String2DateTime(fieldValue))));
}
else
{
myHTML.Append(HttpUtility.HtmlEncode(fieldValue));
}
break;
case EvoDB.t_url:
fieldValue = HttpUtility.HtmlEncode(fieldValue);
if (fieldFormats[j, 0].Length > 0)
{
myHTML.Append(EvoUI.HTMLLink(fieldValue, string.Empty, inNewBrowser, fieldFormats[j, 0]));
}
else
{
myHTML.Append(EvoUI.HTMLLink(fieldValue, fieldValue, inNewBrowser, null));
}
break;
case EvoDB.t_txtm:
myHTML.Append(EvoTC.Text2HTMLwBR(fieldValue));
break;
case EvoDB.t_doc:
myHTML.Append(EvoUI.HTMLLink(_PathPix + fieldValue, fieldValue, inNewBrowser, null));
break;
case EvoDB.t_pix:
if (ListMode < 2 && fieldFormats[j, 0].Length > 0)
{
myHTML.Append(EvoUI.HTMLLink(EvoUI.Link4itemid(fieldFormats[j, 0], ri[dbPrimaryKey].ToString()), String.Empty, fieldFormats[j, 1], _PathPix + fieldValue));
}
else
{
myHTML.Append(EvoUI.HTMLImg(_PathPix + fieldValue));
}
break;
case EvoDB.t_formula:
if (fieldFormats[j, 0].Length > 0 && EvoTC.isDecimal(fieldValue))
{
myHTML.Append(EvoUI.noBR(EvoTC.String2Dec(fieldValue).ToString(fieldFormats[j, 0])));
}
else
{
myHTML.Append(HttpUtility.HtmlEncode(fieldValue));
}
break;
case EvoDB.t_html: // no escaping for html content
// warning: using html fields may expose to cross-site scripting
myHTML.Append(fieldValue);
break;
default:
myHTML.Append(HttpUtility.HtmlEncode(fieldValue));
break;
}
}
myHTML.Append(endTD);
}
}
myHTML.Append("</tr>");
}
myHTML.Append("</TBODY></table></span>");
//--- FOOTER : buttons + summary + paging navigation ---------------------------------
if (ListMode == 2 & nbFieldEditable > 0)
{
myHTML.Append(HTMLAddDeleteRows(PanelDetailsID));
}
if (ListMode == 0)
{
myHTML.Append("</td></tr><tr class=\"RowFoot\"><td colspan=\"2\">");
}
if (!string.IsNullOrEmpty(def_Data.sppaging))
{
if (ListMode == 0)
{
myHTML.Append(HTMLPagingFullNav(MaxLoopSQL, TotalNbRecords, htmlRecCount));
}
}
else if (TotalNbRecords > 0)
{
myHTML.AppendFormat("<div> {0}</div>", htmlRecCount);
}
if (ListMode == 1 && !(string.IsNullOrEmpty(LinkDetailNew) || _DBReadOnly))
{
myHTML.Append("<p>").Append(EvoUI.HTMLLink(LinkDetailNew.Replace(EvoDB.p_itemid, s0) + "&tdLOVE=1N", EvoLang.NewItem)).Append("</p>");
}
}
if (InsidePanel)
{
myHTML.Append(TdTrTableEnd);
}
}
else
{
myHTML.Append(HTMLNoGrid(ListMode, title, LinkDetailNew));
}
ds = null;
return(myHTML.ToString());
}