public string GetMultiSelect(int pageTemplateId, int columnDefId)
{
try
{
var pageTemplate = SessionService.PageTemplate(pageTemplateId);
var columnDef = SessionService.ColumnDef(columnDefId);
var dbEntity = SessionService.DbEntity(pageTemplate.DbEntityId);
using (TargetEntities Db = new TargetEntities())
{
Db.Database.Connection.ConnectionString = dbEntity.ConnectionString;
var columnValue = columnDef.ColumnName;
var columnText = columnDef.ColumnName;
var tableName = pageTemplate.TableName;
var sql = "SELECT DISTINCT ISNULL(CAST(" + columnValue + " AS varchar(500)),'') AS ValueField, ISNULL(CAST(" + columnText + " AS varchar(500)),'') AS TextField FROM " + tableName + " ORDER BY TextField";
if (columnDef.ElementType == "DropdownCustomOption")
{
sql = "SELECT OptionValue AS ValueField, OptionText AS TextField FROM CustomOption WHERE ColumnDefId = " + columnDefId + " ORDER BY TextField";
}
else if (columnDef.ElementType == "DropdownSimple")
{
sql = "SELECT ISNULL(CAST(" + columnDef.ValueField + " AS varchar(250)),'') AS ValueField, ISNULL(CAST(" + columnDef.TextField.Replace(",", " + ' - ' + ") + " AS varchar(500)),'') AS TextField FROM " + columnDef.LookupTable + " ORDER BY TextField";
}
var recs = Db.Database.SqlQuery <ValueText>(sql).ToList();
var json = JsonConvert.SerializeObject(recs);
return(json);
}
}
catch (Exception ex)
{
return(ex.Message);
}
}
public string GetColumnDefById(int columnDefId)
{
if (columnDefId == 0)
{
ColumnDef columnDef = new ColumnDef();
columnDef.ColumnName = "";
columnDef.DisplayName = "";
columnDef.IsRequired = false;
columnDef.ShowInGrid = true;
columnDef.ElementType = "Textbox";
columnDef.DataLength = 50;
columnDef.DataType = "TEXT";
columnDef.LookupTable = "";
string obj = JsonConvert.SerializeObject(columnDef);
return(obj);
}
else
{
var columnDef = SessionService.ColumnDef(columnDefId);
Session["sec.ColumnDefId" + columnDefId] = null;
Session["sec.ColumnDefs" + columnDef.PageTemplateId] = null;
columnDef = SessionService.ColumnDef(columnDefId);
string obj = JsonConvert.SerializeObject(columnDef);
return(obj);
}
}
public string GetAttachedFiles(int pageTemplateId, int columnDefId, int recordId)
{
using (SourceControlEntities Db = new SourceControlEntities())
{
StringBuilder sb = new StringBuilder();
ColumnDef columnDef = SessionService.ColumnDef(columnDefId);
sb.AppendLine("");
var files = Db.FileAttachments.Where(w => w.ColumnDefId == columnDefId && w.RecordId == recordId).OrderBy(o => o.UploadDate);
if (files != null)
{
sb.AppendLine("<select class='site-select-element' id='download" + columnDefId + "_" + recordId + "' onchange='DownloadFile(" + columnDefId + "," + recordId + ")' style='width:" + columnDef.ElementWidth + "px;'>");
sb.AppendLine("<option value=''>Select file to download (" + files.Count() + " files)</option>");
foreach (var file in files)
{
sb.AppendLine("<option value='" + file.FileAttachmentId + "'>" + file.FileName + "</option>");
//sb.AppendLine("<a href='javascript:;' onclick='DownloadFile(" + file.FileAttachmentId + ")'>" + file.FileName + "</a><br>");
}
sb.AppendLine("</select>");
}
return(sb.ToString());
}
}
public string EncryptRecords(int columnDefId)
{
var numOfRecord = 0;
var columnName = "";
try
{
StringBuilder sb = new StringBuilder();
var i = 0;
var columnDef = SessionService.ColumnDef(columnDefId);
var pageTemplate = SessionService.PageTemplate(columnDef.PageTemplateId);
var dbEntity = SessionService.DbEntity(pageTemplate.DbEntityId);
using (TargetEntities targetDb = new TargetEntities())
{
targetDb.Database.Connection.ConnectionString = dbEntity.ConnectionString;
var encryptedValue = "";
columnName = columnDef.ColumnName;
var tableName = pageTemplate.TableName;
var primaryKey = pageTemplate.PrimaryKey;
var primaryKeyType = pageTemplate.PrimaryKeyType;
var sql = "SELECT CAST(" + primaryKey + " AS nvarchar(36)) AS RecordId, CAST(ISNULL(" + columnName + ", '') AS varchar(max)) AS RecordValue FROM " + tableName;
var exe = "UPDATE " + tableName + " SET " + columnName + " = '[RecordValue]' WHERE " + primaryKey + " = '[RecordId]';";
var items = targetDb.Database.SqlQuery <EncrypedRecord>(sql).ToList();
foreach (var item in items.Where(w => w.RecordValue.Length > 0))
{
encryptedValue = Crypto.Encrypt(item.RecordValue).Replace("'", "''");
sb.Append(exe.Replace("[RecordId]", item.RecordId).Replace("[RecordValue]", encryptedValue));
numOfRecord++;
i++;
if (i > 50)
{
i = 0;
targetDb.Database.ExecuteSqlCommand(sb.ToString());
sb.Clear();
}
}
if (i > 0 && sb.Length > 0)
{
targetDb.Database.ExecuteSqlCommand(sb.ToString());
}
}
return("Successful. " + numOfRecord + " records were encrypted for column " + columnName + ".");
}
catch (Exception ex)
{
return(ex.Message);
}
}
public ActionResult LookupWindow(int pageTemplateId, int columnDefId)
{
using (SourceControlEntities Db = new SourceControlEntities())
{
var pageTemplate = SessionService.PageTemplate(pageTemplateId);
var gridColumns = "";
var selectColumn = "";
var columnDef = SessionService.ColumnDef(columnDefId);
// add primary key
string selectColumns = columnDef.ValueField;
string displayColumns = columnDef.ValueField;
// loop thru each column and see if there are lookups. use display name
if (columnDef.TextField.Contains(","))
{
string[] words = columnDef.TextField.Split(new char[] { ',' });
foreach (var word in words)
{
var colDef = SessionService.ColumnDef(columnDef.PageTemplateId, word);
if (colDef != null)
{
if (colDef.LookupTable.Length > 0 && colDef.ValueField.Length > 0 && (colDef.ElementType == "DropdownSimple"))
{
var lookupDef = SessionService.ColumnDef(columnDef.PageTemplateId, columnDef.LookupTable);
selectColumns += "," + colDef.ValueField + "|(SELECT " + colDef.TextField + " FROM " + colDef.LookupTable + " WHERE " + colDef.ValueField + " = " + columnDef.LookupTable + "." + colDef.ValueField + ")[BYPASS_SELECT_FIELD]";
displayColumns += "," + colDef.DisplayName;
}
else
{
selectColumns += "," + word;
displayColumns += "," + colDef.DisplayName;
}
}
else
{
selectColumns += "," + word;
displayColumns += "," + word;
}
}
}
else
{
selectColumns += "," + columnDef.TextField;
displayColumns += "," + columnDef.DisplayName;
}
// get the field after the key to display
if (selectColumns.Contains(","))
{
string[] words = selectColumns.Split(new char[] { ',' });
selectColumn = words[1];
var i = 0;
foreach (var word in words)
{
i++;
if (i == 1)
{
continue;
}
if (word.Contains("[BYPASS_SELECT_FIELD]"))
{
string[] splits = word.Split(new char[] { '|' });
gridColumns += "{ field: \"" + splits[0] + "\", title: \"" + splits[0] + "\" },";
continue;
}
gridColumns += "{ field: \"" + word + "\", title: \"" + word + "\" },";
}
}
else
{
selectColumn = selectColumns;
}
ViewBag.gridColumns = gridColumns;
ViewBag.pageTemplateId = pageTemplateId;
ViewBag.columnDefId = columnDefId;
ViewBag.targetColumn = pageTemplate.TableName + "_" + columnDef.ColumnName;
ViewBag.tableNameOveride = columnDef.LookupTable;
ViewBag.selectColumns = selectColumns;
ViewBag.sortOveride = columnDef.OrderField;
ViewBag.selectColumn = selectColumn;
return(View());
}
}