public string GetColumnDefInline(int pageTemplateId)
{
var columnDefs = SessionService.ColumnDefs(pageTemplateId).OrderBy(o => o.ColumnName);
string obj = JsonConvert.SerializeObject(columnDefs);
return(obj);
}
public string GetColumnDefs(int pageTemplateId)
{
var columnDefs = SessionService.ColumnDefs(pageTemplateId).OrderBy(o => o.ColumnOrder);
var json = JsonConvert.SerializeObject(columnDefs);
return(json);
}
public string GetColumnForLookup(int pageTemplateId)
{
var columnDefs = SessionService.ColumnDefs(pageTemplateId).OrderBy(o => o.DisplayName).Select(s => new { ValueField = s.ColumnName, TextField = s.ColumnName }).ToList();
columnDefs.Add(new { ValueField = "", TextField = "" });
var json = JsonConvert.SerializeObject(columnDefs);
return(json);
}
public string GetSortColumnOptions(int pageTemplateId)
{
var columnDefs1 = SessionService.ColumnDefs(pageTemplateId).Select(s => new { ValueField = s.ColumnDefId + " ASC", TextField = s.DisplayName + " Ascending" }).ToList();
var columnDefs2 = SessionService.ColumnDefs(pageTemplateId).Select(s => new { ValueField = s.ColumnDefId + " DESC", TextField = s.DisplayName + " Descending" }).ToList();
columnDefs1.AddRange(columnDefs2);
columnDefs1.Insert(0, new { ValueField = "", TextField = "" });
string json = JsonConvert.SerializeObject(columnDefs1.OrderBy(o => o.TextField));
return(json);
}
public ActionResult Index(int pageTemplateId, string json)
{
try
{
PageTemplate pageTemplate = SessionService.PageTemplate(pageTemplateId);
PageTemplate pageTemplate2 = new PageTemplate {
PageTemplateId = 0, PrimaryKey = "dummy", PrimaryKey2 = 0
};
ViewData["PageTemplate"] = pageTemplate;
ViewData["PageTemplate2"] = pageTemplate2;
string whereClause = "";
var formColumnName = "";
dynamic obj = Newtonsoft.Json.JsonConvert.DeserializeObject(json);
var columnDefs = SessionService.ColumnDefs(pageTemplateId);
foreach (var columnDef in columnDefs)
{
formColumnName = "Search_" + pageTemplate.TableName + "_" + columnDef.ColumnName;
GetWhereClause(columnDef, obj[formColumnName], ref whereClause);
// get lookup tables
if (columnDef.LookupTable.Length > 0 && columnDef.ValueField.Length > 0 && columnDef.TextField.Length > 0)
{
var lookupColumnDefs = SessionService.ColumnDefs(pageTemplate.DbEntityId, columnDef.LookupTable);
foreach (var lookupColumnDef in lookupColumnDefs)
{
formColumnName = "Search_" + columnDef.LookupTable + "_" + lookupColumnDef.ColumnName;
GetWhereClause(lookupColumnDef, obj[formColumnName], ref whereClause);
}
}
}
if (whereClause.Length > 3)
{
whereClause = whereClause.Substring(0, whereClause.Length - 4);
}
Session["WhereFilter" + pageTemplateId] = whereClause;
return(PartialView("~/Views/Home/_GridTemplate.cshtml"));
}
catch (Exception ex)
{
Helper.LogError(ex.Message + "\r\n" + ex.StackTrace);
}
return(null);
}
public string GetColumnOptions(int pageTemplateId)
{
if (pageTemplateId == 0)
{
return("");
}
try
{
var columnDefs = SessionService.ColumnDefs(pageTemplateId).OrderBy(o => o.DisplayName).Select(s => new { ValueField = s.ColumnDefId, TextField = s.DisplayName }).ToList();
string json = JsonConvert.SerializeObject(columnDefs);
return(json);
}
catch (Exception ex)
{
throw;
}
}
public ActionResult Index()
{
//string html = System.IO.File.ReadAllText(@"D:\Kraken\SourceControl\App_Data\SampleEIDMHtml.txt");
//var pdfBytes = Pdf.From(html).WithGlobalSetting("orientation", "Landscape").Content();
//System.IO.File.WriteAllBytes("C:\\Temp\\Test2.pdf", pdfBytes);
string username = User.Identity.Name.ToString();
//string username = "******";
//Helper.LogError("User tried to log in " + username + " at " + DateTime.Now);
using (SourceControlEntities Db = new SourceControlEntities())
{
var user = Db.AppUsers.Where(a => a.AdName == username).FirstOrDefault();
if (user == null)
{
//username = "******";
///New user. Add to AppUser, set to inactive
AppUser appUser = new AppUser {
AdName = username, FirstName = "", LastName = "", Email = "", PrimaryPhone = "", IsActive = true, ProfileIsComplete = false, LogonCount = 0, Password = "", AddDate = DateTime.Now
};
try
{
using (var context = new PrincipalContext(ContextType.Domain))
{
var principal = UserPrincipal.FindByIdentity(context, User.Identity.Name);
appUser.FirstName = Helper.ToSafeString(principal.GivenName);
appUser.LastName = Helper.ToSafeString(principal.Surname);
appUser.Email = Helper.ToSafeString(principal.EmailAddress);
appUser.PrimaryPhone = Helper.ToSafeString(principal.VoiceTelephoneNumber);
}
}
catch (Exception)
{
}
Db.AppUsers.Add(appUser);
Db.SaveChanges();
EmailService.SendEmail(SessionService.NetworkToolboxEmailAddress(), SessionService.NetworkToolboxEmailAddress(), "", "New user requesting access on Network Toolbox.", appUser.FirstName + " " + appUser.LastName + "<br>" + appUser.Email + "<br>" + username);
user = Db.AppUsers.Where(a => a.AdName == username).FirstOrDefault();
}
if (user == null)
{
return(RedirectToAction("Login", "Home"));
}
else
{
Session["sec.CurrentUser"] = user;
}
}
var columnDefs = SessionService.ColumnDefs(2126);
var pageTemplates = SessionService.PageTemplates(1);
return(View());
}
public string GetOptionsByColumnDefId(int pageTemplateId, int columnDefId)
{
var columnDef = SessionService.ColumnDefs(pageTemplateId).Where(w => w.ColumnDefId == columnDefId).FirstOrDefault();
string orderBy = "";
if (columnDef.OrderField.Length > 3)
{
orderBy = " ORDER BY " + columnDef.OrderField;
}
string textField = "";
if (columnDef.TextField.Contains(","))
{
var columns = columnDef.TextField.Split(new char[] { ',' });
foreach (var columnName in columns)
{
var dataType = SessionService.DataType(pageTemplateId, columnName);
if (dataType == "TEXT")
{
if (textField.Length == 0)
{
textField = columnName;
}
else
{
textField = textField + " + ' - ' + " + columnName;
}
}
else
{
if (textField.Length == 0)
{
textField = "CAST(" + columnName + " AS varchar)";
}
else
{
textField = textField + " + ' - ' + " + "CAST(" + columnName + " AS varchar)";
}
}
}
}
else
{
textField = columnDef.TextField;
}
string sql = "SELECT CAST(" + columnDef.ValueField + " AS varchar) AS ValueField, (" + textField + ") AS TextField FROM " + columnDef.LookupTable + " " + orderBy;
string json = "";
PageTemplate pageTemplate = SessionService.PageTemplate(pageTemplateId);
var dbEntity = SessionService.DbEntity(pageTemplate.DbEntityId);
using (TargetEntities targetDb = new TargetEntities())
{
targetDb.Database.Connection.ConnectionString = dbEntity.ConnectionString;
var recs = targetDb.Database.SqlQuery <ValueText>(sql);
json = Newtonsoft.Json.JsonConvert.SerializeObject(recs);
if (json.Length < 2)
{
json = "[];";
}
}
if (columnDef.AddBlankOption)
{
json = "[{ \"ValueField\":\"\", \"TextField\":\"\"}," + json.Substring(1, json.Length - 1);
}
return(json);
}
private void GetJsonDbFields(int pageTemplateId, ref StringBuilder sbJsonFields, ref StringBuilder sbDbFields)
{
var tableName = SessionService.TableName(pageTemplateId);
var columnDefs = SessionService.ColumnDefs(pageTemplateId);
foreach (var columnDef in columnDefs)
{
// lookup field DropdownSearchOption
if (columnDef.LookupTable != null && columnDef.LookupTable.Length > 0 && columnDef.ElementType == "DropdownSearchOption")
{
string lookUpFields = "";
if (columnDef.TextField.Contains(","))
{
string[] fields = columnDef.TextField.Split(new char[] { ',' });
lookUpFields = columnDef.LookupTable + "." + fields[0];
}
else
{
lookUpFields = columnDef.LookupTable + "." + columnDef.TextField;
}
sbJsonFields.Append("," + tableName + "_" + columnDef.ColumnName + "," + tableName + "_" + columnDef.ColumnName + "_");
sbDbFields.Append("," + tableName + "." + columnDef.ColumnName + ",(SELECT " + lookUpFields + " FROM " + columnDef.LookupTable + " WHERE " + columnDef.ValueField + " = " + tableName + "." + columnDef.ColumnName + ")");
}
else if (columnDef.LookupTable != null && columnDef.LookupTable.Length > 0 && columnDef.ElementType == "DropdownSimple")
{
string lookUpFields = "";
if (columnDef.TextField.Contains(","))
{
lookUpFields = "(";
string[] fields = columnDef.TextField.Split(new char[] { ',' });
foreach (var field in fields)
{
if (lookUpFields.Length < 3)
{
lookUpFields += columnDef.LookupTable + "." + field;
}
else
{
lookUpFields += " + ' - ' + " + columnDef.LookupTable + "." + field;
}
}
lookUpFields += ")";
}
else
{
lookUpFields = columnDef.LookupTable + "." + columnDef.TextField;
}
sbJsonFields.Append("," + tableName + "_" + columnDef.ColumnName);
sbDbFields.Append("," + tableName + "." + columnDef.ColumnName);
//dbFld += ",(SELECT " + lookUpFields + " FROM " + columnDef.LookupTable + " WHERE " + columnDef.ValueField + " = " + tableName + "." + columnDef.ColumnName + ")";
//sb.Append(", (SELECT " + lookUpFields + " FROM " + columnDef.LookupTable + " WHERE " + columnDef.ValueField + " = " + tableName + "." + columnDef.ColumnName + ") AS " + columnDef.ColumnName + "_ ");
}
else if (columnDef.ElementType == "DisplayOnly" || columnDef.ColumnName == "AddBy" || columnDef.ColumnName == "ChangeBy") // Lookup user names
{
if (columnDef.ColumnName == "AddBy" || columnDef.ColumnName == "ChangeBy")
{
sbJsonFields.Append("," + tableName + "_" + columnDef.ColumnName + "_");
sbDbFields.Append(",(SELECT FullName FROM AppUser WHERE UserId = " + tableName + "." + columnDef.ColumnName + ")");
//sb.Append(", (SELECT FullName FROM AppUser WHERE UserId = " + tableName + "." + columnDef.ColumnName + ") AS " + columnDef.ColumnName + "_ ");
}
else
{
sbJsonFields.Append("," + tableName + "_" + columnDef.ColumnName + "_");
sbDbFields.Append("," + tableName + "." + columnDef.ColumnName);
//sb.Append(", " + columnDef.ColumnName + " AS " + columnDef.ColumnName + "_ ");
}
}
else if (columnDef.ElementType == "Hidden")
{
if (columnDef.OverideValue.Length > 0)
{
sbJsonFields.Append("," + tableName + "_" + columnDef.ColumnName);
sbDbFields.Append(",'" + columnDef.OverideValue + "'");
}
else
{
sbJsonFields.Append("," + tableName + "_" + columnDef.ColumnName + "_");
sbDbFields.Append("," + tableName + "." + columnDef.ColumnName);
}
}
else
{
if (columnDef.IsPrimary == false)
{
sbJsonFields.Append("," + tableName + "_" + columnDef.ColumnName);
sbDbFields.Append("," + tableName + "." + columnDef.ColumnName);
}
}
}
}
public string GetFormData(int pageTemplateId = 0, string recordId = "", string layoutType = "")
{
try
{
string tableName = "";
string primaryKey = "";
string json = "";
StringBuilder sbJsonFields = new StringBuilder();
StringBuilder sbDbFields = new StringBuilder();
StringBuilder sb = new StringBuilder();
StringBuilder sbLinkNew = new StringBuilder();
var pageTemplate = SessionService.PageTemplate(pageTemplateId);
tableName = pageTemplate.TableName;
primaryKey = pageTemplate.PrimaryKey;
if (recordId.Length > 0 && recordId != "0")
{
var dbEntity = SessionService.DbEntity(pageTemplate.DbEntityId);
using (TargetEntities targetDb = new TargetEntities())
{
targetDb.Database.Connection.ConnectionString = dbEntity.ConnectionString;
if (pageTemplate.PrimaryKeyType == "TEXT")
{
recordId = "'" + recordId + "'";
}
StringBuilder sbSelect = new StringBuilder();
StringBuilder sbFrom = new StringBuilder();
sbSelect.Append("SELECT " + tableName + "." + primaryKey + " AS " + tableName + "_" + primaryKey);
sbFrom.Append(" FROM " + tableName);
var columnDefs = SessionService.ColumnDefs(pageTemplateId);
foreach (var columnDef in columnDefs.Where(w => !w.IsPrimary))
{
if (layoutType == "View" && columnDef.LookupTable.Length > 0 && columnDef.ValueField.Length > 0 && columnDef.TextField.Length > 0)
{
if (columnDef.IsEncrypted) // decrypt the value
{
var encryptedValue = targetDb.Database.SqlQuery <string>("SELECT " + columnDef.ColumnName + " FROM " + tableName + " WHERE " + tableName + "." + primaryKey + " = " + recordId).FirstOrDefault();
var decryptedValue = Crypto.Decrypt(encryptedValue);
sbSelect.Append(", '" + decryptedValue.Replace("'", "''") + "' AS " + tableName + "_" + columnDef.ColumnName);
}
else if (columnDef.ElementType == "MultiSelect")
{
// get items and inject into select statement
var lookupValue = targetDb.Database.SqlQuery <string>("SELECT " + columnDef.ColumnName + " FROM " + tableName + " WHERE " + primaryKey + " = " + recordId).FirstOrDefault();
if (lookupValue != null && lookupValue.Length > 0)
{
var injectSql = "SELECT STUFF((SELECT ', ' + " + columnDef.TextField + " FROM (SELECT " + columnDef.TextField + " FROM " + columnDef.LookupTable + " WHERE " + columnDef.ValueField + " IN (" + lookupValue + ")) AS T FoR XML PATH('')), 1, 1, '') AS InjectValue";
var injectValue = targetDb.Database.SqlQuery <string>(injectSql).FirstOrDefault();
if (injectValue != null)
{
sbSelect.Append(", '" + injectValue.Replace("'", "''") + "' AS " + tableName + "_" + columnDef.ColumnName);
}
}
}
else
{
sbSelect.Append(", " + columnDef.LookupTable + "." + columnDef.TextField + " AS " + tableName + "_" + columnDef.ColumnName);
// append fields from the lookup table
var lookupColumnDefs = SessionService.ColumnDefs(pageTemplate.DbEntityId, columnDef.LookupTable);
foreach (var lookupColumnDef in lookupColumnDefs)
{
sbSelect.Append(", " + columnDef.LookupTable + "." + lookupColumnDef.ColumnName + " AS " + columnDef.LookupTable + "_" + lookupColumnDef.ColumnName);
}
sbFrom.Append(" LEFT JOIN " + columnDef.LookupTable + " ON " + columnDef.LookupTable + "." + columnDef.ValueField + " = " + tableName + "." + columnDef.ColumnName + " ");
}
}
else
{
if (columnDef.IsEncrypted) // decrypt the value
{
var encryptedValue = targetDb.Database.SqlQuery <string>("SELECT " + columnDef.ColumnName + " FROM " + tableName + " WHERE " + tableName + "." + primaryKey + " = " + recordId).FirstOrDefault();
var decryptedValue = Crypto.Decrypt(encryptedValue);
sbSelect.Append(", '" + decryptedValue.Replace("'", "''") + "' AS " + tableName + "_" + columnDef.ColumnName);
}
else
{
sbSelect.Append(", " + tableName + "." + columnDef.ColumnName + " AS " + tableName + "_" + columnDef.ColumnName);
}
}
}
var recordSql = sbSelect.ToString() + sbFrom.ToString() + " WHERE " + tableName + "." + primaryKey + " = " + recordId + " FOR JSON PATH";
var recs = targetDb.Database.SqlQuery <string>(recordSql).ToList();
json = "";
foreach (var rec in recs)
{
json += rec;
}
if (json.Length > 2)
{
json = json.Substring(1, json.Length - 2);
}
return(json);
}
}
else // recordId = 0
{
// get defaults for new record
sb.Clear();
sb.Append("{\"" + tableName + "_" + primaryKey + "\":0");
List <ColumnDef> columnDefs = new List <ColumnDef>();
columnDefs = SessionService.ColumnDefs(pageTemplateId).Where(w => w.IsRequired == true || (w.DefaultValue != null)).ToList();
foreach (var columnDef in columnDefs)
{
if (columnDef.ElementType == "Note" || columnDef.ElementType == "FileAttachment")
{
sbLinkNew.Append("DELETE FROM " + columnDef.ElementType + " WHERE RecordId = -" + (columnDef.ColumnDefId + SessionService.UserId) + ";");
}
var defaultValue = columnDef.DefaultValue;
if (columnDef.OverideValue != null && columnDef.OverideValue.Length > 0)
{
defaultValue = columnDef.OverideValue;
}
if (columnDef.IsRequired || (defaultValue != null && defaultValue.Length > 0))
{
sb.Append(",\"" + tableName + "_" + columnDef.ColumnName + "\":");
if (defaultValue != null && defaultValue.Length > 0)
{
if (columnDef.DataType == "NUMBER")
{
sb.Append(Helper.ToInt32(defaultValue).ToString());
// add lookup display value
if (columnDef.LookupTable != null && columnDef.LookupTable.Length > 0 && Helper.ToInt32(defaultValue) > 0)
{
string lookUpFields = "";
if (columnDef.TextField.Contains(","))
{
lookUpFields = "(";
string[] fields = columnDef.TextField.Split(new char[] { ',' });
foreach (var field in fields)
{
if (lookUpFields.Length < 3)
{
lookUpFields += columnDef.LookupTable + "." + field;
}
else
{
lookUpFields += " + ' - ' + " + columnDef.LookupTable + "." + field;
}
}
lookUpFields += ")";
}
else
{
lookUpFields = "ISNULL(" + columnDef.LookupTable + "." + columnDef.TextField + ",'') ";
}
var displayValue = DataService.GetStringValue("SELECT " + lookUpFields + " FROM " + columnDef.LookupTable + " WHERE " + columnDef.ValueField + " = " + defaultValue);
sb.Append(",\"" + tableName + "_" + columnDef.ColumnName + "_\":\"" + displayValue.Replace("\"", "\"\"") + "\"");
}
}
else if (columnDef.DataType == "DECIMAL" || columnDef.DataType == "CURRENCY")
{
sb.Append(Helper.ToDecimal(defaultValue).ToString());
}
else if (columnDef.DataType == "TEXT" || columnDef.DataType == "MAXTEXT")
{
sb.Append("\"" + defaultValue.ToString() + "\"");
}
else if (columnDef.DataType == "BOOLEAN")
{
if (defaultValue.ToLower() == "true" || defaultValue == "1")
{
sb.Append("true");
}
else
{
sb.Append("false");
}
}
else if (columnDef.DataType == "DATE")
{
if (defaultValue == "getdate()")
{
sb.Append("\"" + DateTime.Now.ToLongDateString() + "\"");
}
else
{
sb.Append("\"" + Helper.ToDateTime(defaultValue).ToLongDateString() + "\"");
}
}
else
{
sb.Append("\"" + defaultValue.ToString() + "\"");
}
}
else if (columnDef.IsRequired == true)
{
if (columnDef.DataType == "NUMBER")
{
sb.Append("0");
}
else if (columnDef.DataType == "DECIMAL" || columnDef.DataType == "CURRENCY")
{
sb.Append("0");
}
else if (columnDef.DataType == "TEXT" || columnDef.DataType == "MAXTEXT")
{
sb.Append("\"\"");
}
else if (columnDef.DataType == "BOOLEAN")
{
sb.Append("false");
}
else if (columnDef.DataType == "DATE")
{
sb.Append("\"" + DateTime.Now.ToLongDateString() + "\"");
}
else
{
sb.Append("\"\"");
}
}
else
{
if (columnDef.DataType == "NUMBER")
{
sb.Append("0");
}
else if (columnDef.DataType == "DECIMAL" || columnDef.DataType == "CURRENCY")
{
sb.Append("0");
}
else if (columnDef.DataType == "TEXT" || columnDef.DataType == "MAXTEXT")
{
sb.Append("\"\"");
}
else if (columnDef.DataType == "BOOLEAN")
{
sb.Append("false");
}
else if (columnDef.DataType == "DATE")
{
sb.Append("\"" + DateTime.Now.ToLongDateString() + "\"");
}
else
{
sb.Append("\"\"");
}
}
}
}
sb.Append("}");
json = sb.ToString();
//if (sbLinkNew.Length > 0)
//{
// DataService.Execute(sbLinkNew.ToString());
//}
}
return(json);
}
catch (Exception ex)
{
Helper.LogError(ex);
}
return("");
}
public string GetLayoutFromColumnDef(int pageTemplateId, int numOfCol)
{
using (SourceControlEntities Db = new SourceControlEntities())
{
var pageTemplate = SessionService.PageTemplate(pageTemplateId);
StringBuilder sb = new StringBuilder();
sb.AppendLine("<table style='width:95%;'>");
var i = 0;
var columnDefs = SessionService.ColumnDefs(pageTemplateId).Where(w => !w.IsPrimary).OrderBy(o => o.ColumnOrder);
foreach (var columnDef in columnDefs)
{
i++;
if (numOfCol == 1)
{
sb.AppendLine("<tr>");
sb.AppendLine("<td style='width:20%' nowrap>");
sb.AppendLine(columnDef.DisplayName);
sb.AppendLine("</td>");
sb.AppendLine("<td style='width:80%'>");
sb.AppendLine("[" + pageTemplate.TableName + "_" + columnDef.ColumnName + "]");
sb.AppendLine("</td>");
sb.AppendLine("</tr>");
}
else if (numOfCol > 1)
{
if ((i == 1))
{
sb.AppendLine("<tr>");
}
if (numOfCol == 2)
{
sb.AppendLine("<td style='width:17%' nowrap>");
sb.AppendLine(columnDef.DisplayName);
sb.AppendLine("</td>");
sb.AppendLine("<td style='width:28%'>");
sb.AppendLine("[" + pageTemplate.TableName + "_" + columnDef.ColumnName + "]");
sb.AppendLine("</td>");
if ((i % 2) != 0)
{
sb.AppendLine("<td style='width:10%' nowrap> </td>");
}
}
else
{
sb.AppendLine("<td style='width:9%' nowrap>");
sb.AppendLine(columnDef.DisplayName);
sb.AppendLine("</td>");
sb.AppendLine("<td style='width:20%'>");
sb.AppendLine("[" + pageTemplate.TableName + "_" + columnDef.ColumnName + "]");
sb.AppendLine("</td>");
if ((i % 3) != 0)
{
sb.AppendLine("<td style='width:6%' nowrap> </td>");
}
}
if ((i % numOfCol) == 0)
{
if (i == columnDefs.Count())
{
sb.AppendLine("</tr>");
}
else
{
sb.AppendLine("</tr><tr>");
}
}
}
}
if ((numOfCol > 1 && i != numOfCol))
{
for (int x = i; x <= columnDefs.Count(); x++)
{
sb.AppendLine("<td nowrap> </td>");
}
sb.AppendLine("<tr>");
}
sb.AppendLine("</table>");
return(sb.ToString());
}
}