public bool UpdateStandardGroup(StandardGroupModel model)
{
AutomationStandardsContext db = new AutomationStandardsContext();
StandardGroup Result = db.StandardGroup.First(d => d.StandardGroupID == model.StandardGroupID);
Result.StandardGroupName = model.StandardGroupName;
try
{
db.SaveChanges();
return(true);
}
catch (DbEntityValidationException ex)
{
string body = "";
foreach (var eve in ex.EntityValidationErrors)
{
body += string.Format("Entity of type \"{0}\" in state \"{1}\" has the following validation errors:", eve.Entry.Entity.GetType().Name, eve.Entry.State);
body += "<br>";
foreach (var ve in eve.ValidationErrors)
{
body += string.Format("- Property: \"{0}\", Error: \"{1}\"", ve.PropertyName, ve.ErrorMessage);
}
}
//GlobalFunctions gf = new //GlobalFunctions();
//gf.SendErrorModelEmail(this.GetType().Name, body);
return(false);
}
}
public StandardConfigModel SelectSingleStandardConfig(int StandardConfigID)
{
AutomationStandardsContext db = new AutomationStandardsContext();
StandardConfigModel Result = (from d in db.StandardConfig
where d.StandardConfigID == StandardConfigID
select new StandardConfigModel
{
StandardConfigID = d.StandardConfigID,
StandardID = d.StandardID,
StandardName = d.Standard.StandardName,
FieldName = d.FieldName,
DisplayName = d.DisplayName,
DataTypeID = d.DataTypeID,
DataTypeName = d.DataType.DataTypeName, // StandardDataType.DataTypeName,
SQLDataType = d.DataType.SQLDataType, // StandardDataType.SQLDataType,
SortOrder = d.SortOrder,
VersionNumber = d.VersionNumber,
UseToolTip = d.UseToolTip,
ToolTip = d.ToolTip,
UseStandardData = d.UseStandardData,
AllowMultiSelect = d.AllowMultiSelect,
StandardLUID = d.StandardLUID,
StandardLUValue = d.StandardLUValue,
StandardUseFilter = d.StandardUseFilter,
StandardFilterSQL = d.StandardFilterSQL
}).First();
return(Result);
}
public StandardModel SelectSingleStandardByDBTableName(string DBTableName)
{
AutomationStandardsContext db = new AutomationStandardsContext();
StandardModel Result = new StandardModel();
try
{
Result = (from d in db.Standard
where d.DBTableName == DBTableName
select new StandardModel
{
StandardID = d.StandardID,
StandardName = d.StandardName,
StandardDefinition = d.StandardDefinition,
DBTableName = d.DBTableName,
StandardGroupID = d.StandardGroupID,
StandardGroupName = d.StandardGroup.StandardGroupName,
ManageRoles = d.ManageRoles,
ViewerRoles = d.ViewerRoles,
VersionConfig = d.VersionConfig,
VersionValue = d.VersionValue,
Tags = d.Tags,
NotifiyOwner = d.NotifiyOwner,
OwnerEmail = d.OwnerEmail,
UsageCount = d.UsageCount
}).First();
}
catch
{
return(new StandardModel());
}
return(Result);
}
public int GetStandardRowsCount(string TableName)
{
string SQL = "SELECT COUNT(ID) FROM " + TableName;
AutomationStandardsContext db = new AutomationStandardsContext();
#region Execute SQL
int count = 0;
using (SqlConnection thisConnection = new SqlConnection(AutomationStandardsContext.ConnectionString))
{
using (SqlCommand cmdCount = new SqlCommand(SQL, thisConnection))
{
thisConnection.Open();
count = (int)cmdCount.ExecuteScalar();
}
}
#endregion
return(count);
}
public bool DeleteStandardValue(string StandardID, string RecordID, string UserID)
{
//List<StandardConfigModel> Configs = new StandardConfigModel().SelectStandardConfigs(Convert.ToInt32(StandardID));
//StandardModel model = new StandardModel().SelectSingleStandard(Convert.ToInt32(StandardID));
AutomationStandardsContext db = new AutomationStandardsContext();
Standard model = db.Standard.Find(Convert.ToInt32(StandardID));
if (string.IsNullOrEmpty(UserID))
{
UserID = "WebUser";
}
//if (model.NotifiyOwner)
//{
// FormCollection newfc = new FormCollection();
// newfc.Add("StandardID", StandardID);
// newfc.Add("ID", RecordID);
// NotifyStandardOwner("DELETE", newfc, Configs, model, UserID);
//}
SetStandardVersionValue(model.StandardID);
string SQL = "DELETE FROM " + model.DBTableName + " WHERE ID = " + RecordID;
bool Result = ExecuteSql(SQL);
return(Result);
}
public List <StandardModel> SelectAllViewerStandardsByRoles(List <string> CurrentRoles)
{
AutomationStandardsContext db = new AutomationStandardsContext();
List <StandardModel> Result = new List <StandardModel>();
List <string> QueryRoles = new List <string>();
foreach (string Role in CurrentRoles)
{
QueryRoles.Add(Role.ToLower());
}
var AllStandards = SelectAllStandards();
foreach (var Standard in AllStandards)
{
List <string> StandardRoles = Standard.ViewerRoles.ToLower().Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList();
List <string> ViewerRoles = new List <string>();
foreach (string Role in StandardRoles)
{
ViewerRoles.Add(Role.Trim().ToLower());
}
if (ViewerRoles.Intersect(QueryRoles).Any())
{
Result.Add(Standard);
}
}
return(Result);
}
public DataTable GetStandardValuesCustomFilter(string TableName, bool UseFilter, string FilterSQL)
{
DataTable Result = new DataTable(TableName);
string SQL = "SELECT * FROM " + TableName;
if (UseFilter)
{
SQL += " WHERE Enabled = 1 AND " + FilterSQL;
}
SQL += " ORDER BY SortOrder";
AutomationStandardsContext db = new AutomationStandardsContext();
#region Execute SQL
using (SqlConnection sqlConn = new SqlConnection(AutomationStandardsContext.ConnectionString))
using (SqlCommand cmd = new SqlCommand(SQL.Replace("'", "'"), sqlConn))
{
sqlConn.Open();
Result.Load(cmd.ExecuteReader());
sqlConn.Close();
}
#endregion
return(Result);
}
public List <StandardModel> SelectAllStandards()
{
AutomationStandardsContext db = new AutomationStandardsContext();
List <StandardModel> Result = (from d in db.Standard
select new StandardModel
{
StandardID = d.StandardID,
StandardName = d.StandardName,
StandardDefinition = d.StandardDefinition,
DBTableName = d.DBTableName,
StandardGroupID = d.StandardGroupID,
StandardGroupName = d.StandardGroup.StandardGroupName,
ManageRoles = d.ManageRoles,
ViewerRoles = d.ViewerRoles,
VersionConfig = d.VersionConfig,
VersionValue = d.VersionValue,
Tags = d.Tags,
NotifiyOwner = d.NotifiyOwner,
OwnerEmail = d.OwnerEmail,
UsageCount = d.UsageCount
}).ToList();
StandardSQLManagement sql = new StandardSQLManagement();
foreach (StandardModel item in Result)
{
item.StandardCount = sql.GetStandardRowsCount(item.DBTableName);
}
return(Result);
}
public bool InsertStandardDataType(StandardDataTypeModel model)
{
AutomationStandardsContext db = new AutomationStandardsContext();
StandardDataType Result = new StandardDataType();
Result.DataTypeName = model.StandardDataTypeName;
Result.SQLDataType = model.SQLDataType;
db.StandardDataType.Add(Result);
try
{
db.SaveChanges();
model.StandardDataTypeID = Result.DataTypeID;
return(true);
}
catch (DbEntityValidationException ex)
{
string body = "";
foreach (var eve in ex.EntityValidationErrors)
{
body += string.Format("Entity of type \"{0}\" in state \"{1}\" has the following validation errors:", eve.Entry.Entity.GetType().Name, eve.Entry.State);
body += "<br>";
foreach (var ve in eve.ValidationErrors)
{
body += string.Format("- Property: \"{0}\", Error: \"{1}\"", ve.PropertyName, ve.ErrorMessage);
}
}
//GlobalFunctions gf = new //GlobalFunctions();
//gf.SendErrorModelEmail(this.GetType().Name, body);
return(false);
}
}
public bool DeleteStandardDataType(int DataTypeID)
{
AutomationStandardsContext db = new AutomationStandardsContext();
var DataTypes = db.StandardDataType.Where(d => d.DataTypeID == DataTypeID).ToList();
if (DataTypes.Count > 0)
{
db.StandardDataType.Remove(DataTypes.First());
try
{
db.SaveChanges();
return(true);
}
catch (DbEntityValidationException ex)
{
string body = "";
foreach (var eve in ex.EntityValidationErrors)
{
body += string.Format("Entity of type \"{0}\" in state \"{1}\" has the following validation errors:", eve.Entry.Entity.GetType().Name, eve.Entry.State);
body += "<br>";
foreach (var ve in eve.ValidationErrors)
{
body += string.Format("- Property: \"{0}\", Error: \"{1}\"", ve.PropertyName, ve.ErrorMessage);
}
}
//GlobalFunctions gf = new //GlobalFunctions();
//gf.SendErrorModelEmail(this.GetType().Name, body);
return(false);
}
}
return(true);
}
public ActionResult Viewer(string id)
{
//Standard StandardInfo = new StandardModel().SelectSingleStandardAsDbStandardByName(id);
AutomationStandardsContext db = new AutomationStandardsContext();
Standard standardInfo = db.Standard.Where(x => x.DBTableName == id).FirstOrDefault();
return(View(standardInfo.StandardID));
}
public bool InsertStandardValueAsIFormCollecction(IFormCollection fc, string UserID)
{
int StandardID = Convert.ToInt32(fc["StandardID"]);
string RecordID = fc["ID"];
List <StandardConfigModel> Configs = new StandardConfigModel().SelectStandardConfigs(StandardID);
//StandardModel model = new StandardModel().SelectSingleStandard(StandardID);
AutomationStandardsContext db = new AutomationStandardsContext();
Standard model = db.Standard.Where(x => x.StandardID == StandardID).FirstOrDefault();
//if (model.NotifiyOwner)
//{
// NotifyStandardOwner("INSERT", fc, Configs, model, UserID);
//}
SetStandardVersionValue(model.StandardID);
string InsertFields = "INSERT INTO " + model.DBTableName + " ( ";
string ValuesFields = " VALUES (";
InsertFields += "CreatedOn ,";
ValuesFields += "'" + DateTime.Now + "' , ";
InsertFields += "CreatedBy ,";
ValuesFields += "'" + UserID + "' , ";
foreach (var formKey in fc.Keys) // fc.AllKeys)
{
List <StandardConfigModel> foundConfig = Configs.Where(c => c.FieldName == formKey).ToList();
if (foundConfig.Count > 0)
{
StandardConfigModel config = foundConfig.First();
string value;
if (config.AllowMultiSelect)
{
value = ";" + fc[config.FieldName]; //.Replace(",", ";") + ";";
value = value.Replace(",", ";") + ";";
}
else
{
value = fc[config.FieldName];
}
InsertFields += " " + config.FieldName + " ,";
ValuesFields += "CAST('" + value + "' AS " + config.SQLDataType + " ) , ";
}
}
InsertFields += "Enabled ,";
ValuesFields += "CAST('" + fc["Enabled"] + "' AS BIT) , ";
InsertFields += "SortOrder )";
ValuesFields += " " + fc["SortOrder"] + " )";
string SQL = InsertFields + " " + ValuesFields;
bool Result = ExecuteSql(SQL);
return(Result);
}
public List <StandardGroupModel> SelectAllStandardGroup()
{
AutomationStandardsContext db = new AutomationStandardsContext();
List <StandardGroupModel> Result = (from d in db.StandardGroup
select new StandardGroupModel
{
StandardGroupID = d.StandardGroupID,
StandardGroupName = d.StandardGroupName
}).ToList();
return(Result);
}
public bool ExecuteSql(string sql)
{
AutomationStandardsContext db = new AutomationStandardsContext();
SqlConnection conn = new SqlConnection(AutomationStandardsContext.ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.CommandText = sql;
cmd.Connection = conn;
conn.Open();
int output = cmd.ExecuteNonQuery();
bool Result = false;
if (output >= 0)
{
Result = true;
}
conn.Close();
return(Result);
//var entityConnection = (System.Data.EntityClient.EntityConnection)db.Database.Connection;
//DbConnection conn = entityConnection.StoreConnection;
//ConnectionState initialState = conn.State;
//try
//{
// if (initialState != ConnectionState.Open)
// conn.Open(); // open connection if not already open
// using (DbCommand cmd = conn.CreateCommand())
// {
// cmd.CommandText = sql;
// cmd.ExecuteNonQuery();
// }
//}
//catch
//{
// return false;
//}
//finally
//{
// if (initialState != ConnectionState.Open)
// {
// conn.Close(); // only close connection if not initially open
// }
//}
//return true;
}
public List <StandardDataTypeModel> SelectStandardDataTypes()
{
AutomationStandardsContext db = new AutomationStandardsContext();
List <StandardDataTypeModel> Result = (from d in db.StandardDataType
select new StandardDataTypeModel
{
StandardDataTypeID = d.DataTypeID,
StandardDataTypeName = d.DataTypeName,
SQLDataType = d.SQLDataType
}).ToList();
return(Result);
}
public StandardGroupModel SelectSingleStandardGroup(int GroupID)
{
AutomationStandardsContext db = new AutomationStandardsContext();
StandardGroupModel Result = (from d in db.StandardGroup
where d.StandardGroupID == GroupID
select new StandardGroupModel
{
StandardGroupID = d.StandardGroupID,
StandardGroupName = d.StandardGroupName
}).First();
return(Result);
}
//public int VersionConfig { get; set; }
//public int VersionValue { get; set; }
//public string Tags { get; set; }
//public bool NotifiyOwner { get; set; }
//public string OwnerEmail { get; set; }
//public int UsageCount { get; set; }
public string SelectStanardname(int StandardID)
{
AutomationStandardsContext db = new AutomationStandardsContext();
string Name = "";
try
{
Name = db.Standard.First(s => s.StandardID == StandardID).StandardName;
}
catch
{ }
return(Name);
}
public StandardDataTypeModel SelectSingleStandardDataType(int DataTypeID)
{
AutomationStandardsContext db = new AutomationStandardsContext();
StandardDataTypeModel Result = (from d in db.StandardDataType
where d.DataTypeID == DataTypeID
select new StandardDataTypeModel
{
StandardDataTypeID = d.DataTypeID,
StandardDataTypeName = d.DataTypeName,
SQLDataType = d.SQLDataType
}).First();
return(Result);
}
public bool DeleteStandard(int StandardID)
{
AutomationStandardsContext db = new AutomationStandardsContext();
var Standards = db.Standard.Where(d => d.StandardID == StandardID).ToList();
if (Standards.Count > 0)
{
var Configs = db.StandardConfig.Where(c => c.StandardID == StandardID).ToList();
foreach (var Config in Configs)
{
db.StandardConfig.Remove(Config);
db.SaveChanges();
}
db.Standard.Remove(Standards.First());
try
{
db.SaveChanges();
//DELETE TABLE
StandardSQLManagement sql = new StandardSQLManagement();
bool SQLOK = sql.DeleteStandardTable(Standards.First().DBTableName);
return(SQLOK);
}
catch (DbEntityValidationException ex)
{
string body = "";
foreach (var eve in ex.EntityValidationErrors)
{
body += string.Format("Entity of type \"{0}\" in state \"{1}\" has the following validation errors:", eve.Entry.Entity.GetType().Name, eve.Entry.State);
body += "<br>";
foreach (var ve in eve.ValidationErrors)
{
body += string.Format("- Property: \"{0}\", Error: \"{1}\"", ve.PropertyName, ve.ErrorMessage);
}
}
//GlobalFunctions gf = new //GlobalFunctions();
//gf.SendErrorModelEmail(this.GetType().Name, body);
return(false);
}
}
return(true);
}
public Standard SelectSingleStandardAsDbStandardByName(string StandardName)
{
AutomationStandardsContext db = new AutomationStandardsContext();
Standard Result = new Standard();
try
{
Result = db.Standard.Where(x => x.StandardName == StandardName).FirstOrDefault();
}
catch
{
return(new Standard());
}
return(Result);
}
public Standard SelectSingleStandardAsDbStandard(int StandardID)
{
AutomationStandardsContext db = new AutomationStandardsContext();
StandardModel Result = new StandardModel();
//if (StandardID != 0)
//{
// Result = (from d in db.Standard
// where d.StandardID == StandardID
// select new StandardModel
// {
// StandardID = d.StandardID,
// StandardName = d.StandardName,
// StandardDefinition = d.StandardDefinition,
// DBTableName = d.DBTableName,
// StandardGroupID = d.StandardGroupID,
// StandardGroupName = d.StandardGroup.StandardGroupName,
// ManageRoles = d.ManageRoles,
// ViewerRoles = d.ViewerRoles,
// VersionConfig = d.VersionConfig,
// VersionValue = d.VersionValue,
// Tags = d.Tags,
// NotifiyOwner = d.NotifiyOwner,
// OwnerEmail = d.OwnerEmail,
// UsageCount = d.UsageCount
// }).First();
//}
Standard CurrentStandard = db.Standard.First(s => s.StandardID == StandardID);
CurrentStandard.UsageCount = CurrentStandard.UsageCount + 1;
try
{
db.SaveChanges();
}
catch
{
string body = "";
body += string.Format("Failed to increment usage counter for Standard: \"{0}\" ID: \"{1}\" ", CurrentStandard.DBTableName, StandardID);
//GlobalFunctions gf = new //GlobalFunctions();
//gf.SendErrorModelEmail(this.GetType().Name, body);
return(CurrentStandard);
}
return(Result);
}
//public bool NotifyStandardOwner(string EntryType, FormCollection fc, List<StandardConfigModel> Configs, StandardModel StandardInfo, string CurrentUser)
//{
// int StandardID = Convert.ToInt32(fc["StandardID"]);
// string RecordID = fc["ID"];
// string To, Subject, Body, Type;
// ActiveDirectoryUserInfo UserInfo = new ActiveDirectoryUserInfo(CurrentUser);
// To = StandardInfo.OwnerEmail;
// Subject = "OneClick Standard - " + StandardInfo.StandardName;
// Body = "";
// Type = "";
// #region Build Body
// switch (EntryType)
// {
// case "INSERT":
// Subject = Subject + " - New Standard Added";
// Type = "Inserted";
// Body = "A new standard has been added to <b>" + StandardInfo.StandardName + "</b>. Below is the new standard added.<br><br><br>";
// Body += @"<table class=""details-table"" width=""80%""><tr><td class=""titles"" style=""font-family:Calibri,Arial;"">Field</td><td class=""titles"" style=""font-family:Calibri,Arial;"">New Value</td></tr>";
// foreach (var formKey in fc.Keys) //AllKeys)
// {
// List<StandardConfigModel> foundConfig = Configs.Where(c => c.FieldName == formKey).ToList();
// if (foundConfig.Count > 0)
// {
// StandardConfigModel config = foundConfig.First();
// string newValue;
// if (config.AllowMultiSelect)
// {
// newValue = ";" + fc[config.FieldName].Replace(",", ";") + ";";
// }
// else
// {
// newValue = fc[config.FieldName];
// }
// Body += @"<tr><td class=""values"" style=""font-family:Calibri,Arial;"">" + config.DisplayName + @"</td><td class=""values"" style=""font-family:Calibri,Arial;"">" + newValue + "</td></tr>";
// }
// }
// Body += "</table>";
// break;
// case "UPDATE":
// DataRow OldData = GetStandardRow(StandardInfo.DBTableName, RecordID);
// Subject = Subject + " - Existing Standard Updated";
// Type = "Updated";
// Body = "An existing standard has been updated to <b>" + StandardInfo.StandardName + "</b>. Below is the old and new standard values.<br><br><br>";
// Body += @"<table class=""details-table"" width=""80%""><tr><td class=""titles"" style=""font-family:Calibri,Arial;"">Field</td><td class=""titles"" style=""font-family:Calibri,Arial;"">Old Value</td><td class=""titles"" style=""font-family:Calibri,Arial;"">New Value</td></tr>";
// foreach (var formKey in fc.Keys) //AllKeys)
// {
// List<StandardConfigModel> foundConfig = Configs.Where(c => c.FieldName == formKey).ToList();
// if (foundConfig.Count > 0)
// {
// StandardConfigModel config = foundConfig.First();
// string newValue;
// if (config.AllowMultiSelect)
// {
// newValue = ";" + fc[config.FieldName].Replace(",", ";") + ";";
// }
// else
// {
// newValue = fc[config.FieldName];
// }
// string OldValue = OldData[config.FieldName].ToString();
// Body += @"<tr><td class=""values"" style=""font-family:Calibri,Arial;"">" + config.DisplayName + @"</td><td class=""values"" style=""font-family:Calibri,Arial;"">" + OldValue + @"</td><td class=""values"" style=""font-family:Calibri,Arial;"">" + newValue + "</td></tr>";
// }
// }
// Body += "</table>";
// break;
// case "DELETE":
// DataRow DeletedData = GetStandardRow(StandardInfo.DBTableName, RecordID);
// Subject = Subject + " - Standard Deleted";
// Type = "Deleted";
// Body = "A standard has been deleted from <b>" + StandardInfo.StandardName + "</b>. Below is the standard that was deleted.<br><br><br>";
// Body += @"<table class=""details-table"" width=""80%""><tr><td class=""titles"" style=""font-family:Calibri,Arial;"">Field</td><td class=""titles"" style=""font-family:Calibri,Arial;"">Deleted Value</td></tr>";
// foreach (var config in Configs.OrderBy(c => c.SortOrder))
// {
// string DeletedValue = DeletedData[config.FieldName].ToString();
// Body += @"<tr><td class=""values"" style=""font-family:Calibri,Arial;"">" + config.DisplayName + @"</td><td class=""values"" style=""font-family:Calibri,Arial;"">" + DeletedValue + "</td></tr>";
// }
// Body += "</table>";
// break;
// }
// #endregion
// NotificationTemplateModel tempinfo = new NotificationTemplateModel().SelectSingleNotificationTemplateByName("Standard");
// Body = tempinfo.NotificationBody.Replace("{#NotificationBody#}", Body)
// .Replace("{#Type#}", Type)
// .Replace("{#ChangedBy#}", UserInfo.FirstName + " " + UserInfo.LastName)
// .Replace("{#StandardName#}", StandardInfo.StandardName)
// .Replace("{#RequestUrl#}", "http://util.obs.org/OneClick/Standard/Viewer/" + StandardInfo.StandardName);
// wsESEUtil.ESEUtilitySoapClient ws = new wsESEUtil.ESEUtilitySoapClient();
// bool result = ws.sendEmail(Subject, Body, To, true, "eseautomation", "", "", "", "");
// return result;
//}
public bool SetStandardVersionValue(int StandardID)
{
AutomationStandardsContext db = new AutomationStandardsContext();
var Standards = db.Standard.Where(d => d.StandardID == StandardID).ToList();
if (Standards.Count > 0)
{
Standard CurrentStandard = Standards.First();
int NewVersion = CurrentStandard.VersionValue + 1;
CurrentStandard.VersionValue = NewVersion;
db.SaveChanges();
}
return(true);
}
public List <SelectListItem> SelectAllStandardsDDL()
{
AutomationStandardsContext db = new AutomationStandardsContext();
List <SelectListItem> Result = (from d in db.Standard
select new SelectListItem
{
Value = SqlFunctions.StringConvert((double)d.StandardID).Trim(),
Text = d.StandardName
}).OrderBy(t => t.Text).ToList();
SelectListItem n = new SelectListItem()
{
Value = "0", Text = "Please Select One"
};
Result.Insert(0, n);
return(Result);
}
public PartialViewResult StandardEditor(string id, string ParentId)
{
AutomationStandardsContext db = new AutomationStandardsContext();
StandardSQLManagement sql = new StandardSQLManagement();
StandardValueSingle model = new StandardValueSingle();
//StandardModel StandardInfo = new StandardModel().SelectSingleStandard(Convert.ToInt32(StandardID));
Standard StandardInfo = db.Standard.Where(x => x.StandardID == Convert.ToInt32(ParentId)).FirstOrDefault();
model.dtRow = sql.GetStandardRow(StandardInfo.DBTableName, id);
model.Config = new StandardConfigModel().SelectStandardConfigs(StandardInfo.StandardID);
model.StandardID = StandardInfo.StandardID;
model.TableName = StandardInfo.DBTableName;
model.StandardName = StandardInfo.StandardName;
return(PartialView(model));
}
//public int SortOrder { get; set; }
//public int VersionNumber { get; set; }
//public bool UseToolTip { get; set; }
//public string ToolTip { get; set; }
//public bool UseStandardData { get; set; }
//public bool AllowMultiSelect { get; set; }
//public int StandardLUID { get; set; }
//public string StandardLUValue { get; set; }
//public bool StandardUseFilter { get; set; }
//public string StandardFilterSQL { get; set; }
public List <StandardConfigModel> SelectStandardConfigs(int StandardID)
{
AutomationStandardsContext db = new AutomationStandardsContext();
List <StandardDataType> dtp = new List <StandardDataType>();
dtp = db.StandardDataType.ToList();
Standard st = db.Standard.Where(x => x.StandardID == StandardID).FirstOrDefault();
List <StandardConfigModel> Result = new List <StandardConfigModel>(); //(from d in db.StandardConfig
// where d.StandardID == StandardID
List <StandardConfig> scmResult = db.StandardConfig.Where(d => d.StandardID == StandardID).ToList();
foreach (var d in scmResult)
{
StandardConfigModel sc = new StandardConfigModel();
sc.StandardConfigID = d.StandardConfigID;
sc.StandardID = d.StandardID;
sc.StandardName = st.StandardName; //d.Standard.StandardName;
sc.FieldName = d.FieldName;
sc.DisplayName = d.DisplayName;
sc.DataTypeID = d.DataTypeID;
sc.DataTypeName = dtp.Where(x => x.DataTypeID == d.DataTypeID).First().DataTypeName; //d.DataType.DataTypeName; // StandardDataType.DataTypeName;
sc.SQLDataType = dtp.Where(x => x.DataTypeID == d.DataTypeID).First().SQLDataType; //d.DataType.SQLDataType; //StandardDataType.SQLDataType;
sc.SortOrder = d.SortOrder;
sc.VersionNumber = d.VersionNumber;
sc.UseToolTip = d.UseToolTip;
sc.ToolTip = d.ToolTip;
sc.UseStandardData = d.UseStandardData;
sc.AllowMultiSelect = d.AllowMultiSelect;
sc.StandardLUID = d.StandardLUID;
sc.StandardLUValue = d.StandardLUValue;
sc.StandardUseFilter = d.StandardUseFilter;
sc.StandardFilterSQL = d.StandardFilterSQL;
Result.Add(sc);
}
return(Result);
}
public bool SetStandardConfigSortOrder(int ConfigID, int SortOrder)
{
AutomationStandardsContext db = new AutomationStandardsContext();
var ConfigInfo = (from f in db.StandardConfig
where f.StandardConfigID == ConfigID
select f).ToList();
if (ConfigInfo.Count == 0)
{
return(false);
}
var Config = ConfigInfo.First();
Config.SortOrder = SortOrder;
try
{
db.SaveChanges();
}
catch (DbEntityValidationException ex)
{
string body = "";
foreach (var eve in ex.EntityValidationErrors)
{
body += string.Format("Entity of type \"{0}\" in state \"{1}\" has the following validation errors:", eve.Entry.Entity.GetType().Name, eve.Entry.State);
body += "<br>";
foreach (var ve in eve.ValidationErrors)
{
body += string.Format("- Property: \"{0}\", Error: \"{1}\"", ve.PropertyName, ve.ErrorMessage);
}
}
////GlobalFunctions gf = new //GlobalFunctions();
////gf.SendErrorModelEmail(this.GetType().Name, body);
return(false);
}
return(true);
}
public bool UpdateStandard(StandardModel model)
{
AutomationStandardsContext db = new AutomationStandardsContext();
Standard Result = db.Standard.First(d => d.StandardID == model.StandardID);
Result.StandardName = model.StandardName;
Result.StandardGroupID = model.StandardGroupID;
Result.StandardDefinition = model.StandardDefinition;
Result.ManageRoles = model.ManageRoles;
Result.ViewerRoles = model.ViewerRoles;
Result.VersionConfig = model.VersionConfig;
Result.VersionValue = model.VersionValue;
Result.Tags = model.Tags;
Result.NotifiyOwner = model.NotifiyOwner;
Result.OwnerEmail = model.OwnerEmail;
try
{
db.SaveChanges();
return(true);
}
catch (DbEntityValidationException ex)
{
string body = "";
foreach (var eve in ex.EntityValidationErrors)
{
body += string.Format("Entity of type \"{0}\" in state \"{1}\" has the following validation errors:", eve.Entry.Entity.GetType().Name, eve.Entry.State);
body += "<br>";
foreach (var ve in eve.ValidationErrors)
{
body += string.Format("- Property: \"{0}\", Error: \"{1}\"", ve.PropertyName, ve.ErrorMessage);
}
}
//GlobalFunctions gf = new //GlobalFunctions();
//gf.SendErrorModelEmail(this.GetType().Name, body);
return(false);
}
}
public DataTable ExecuteSelectSql(string SQL)
{
DataTable Result = new DataTable();
AutomationStandardsContext db = new AutomationStandardsContext();
#region Execute SQL
using (SqlConnection sqlConn = new SqlConnection(AutomationStandardsContext.ConnectionString))
using (SqlCommand cmd = new SqlCommand(SQL, sqlConn))
{
sqlConn.Open();
Result.Load(cmd.ExecuteReader());
sqlConn.Close();
}
#endregion
return(Result);
}
public IEnumerable <SelectListItem> DDLStandardGroup()
{
AutomationStandardsContext db = new AutomationStandardsContext();
List <SelectListItem> Result = new List <SelectListItem>();
var items = (from d in db.StandardGroup
select new
{
Value = d.StandardGroupID,
Text = d.StandardGroupName
}
).OrderBy(s => s.Text).ToList();
foreach (var i in items)
{
SelectListItem l = new SelectListItem();
l.Text = i.Text;
l.Value = i.Value.ToString();
Result.Add(l);
}
return(Result);
}
public List <SelectListItem> GetStandardValueDDLCustomFilter(int StandardID, string ValueFieldName, string TextFieldName, bool UseFilter, string SQLFilter)
{
List <SelectListItem> Result = new List <SelectListItem>();
//StandardModel model = new StandardModel().SelectSingleStandard(StandardID);
AutomationStandardsContext db = new AutomationStandardsContext();
Standard model = db.Standard.Find(StandardID);
DataTable sValues = GetStandardValuesCustomFilter(model.DBTableName, UseFilter, SQLFilter);
foreach (DataRow dr in sValues.Rows)
{
if (dr["Enabled"].ToString().ToLower() == "true")
{
Result.Add(new SelectListItem()
{
Text = dr[TextFieldName].ToString(), Value = dr[ValueFieldName].ToString()
});
}
}
return(Result);
}
