public List <SelectListItem> GetStandardValueDDLCustomFilter(string StandardName, string ValueFieldName, string TextFieldName, bool UseFilter, string SQLFilter)
{
StandardModel model = new StandardModel().SelectSingleStandardByName(StandardName);
List <SelectListItem> Result = GetStandardValueDDLCustomFilter(model.StandardID, ValueFieldName, TextFieldName, UseFilter, SQLFilter);
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 bool UpdateStandardValue(FormCollection 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);
//if (model.NotifiyOwner)
//{
// NotifyStandardOwner("UPDATE", fc, Configs, model, UserID);
//}
SetStandardVersionValue(model.StandardID);
string SQL = "UPDATE " + model.DBTableName + " SET ";
SQL += " ModifiedOn = '" + DateTime.Now + "', ";
SQL += " ModifiedBy = '" + UserID + "', ";
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 value;
if (string.IsNullOrEmpty(fc[config.FieldName]))
{
value = "";
}
else
{
if (config.AllowMultiSelect)
{
value = ";" + fc[config.FieldName]; //.Replace(",", ";") + ";";
value = value.Replace(",", ";") + ";";
}
else
{
value = fc[config.FieldName];
}
}
SQL += " " + config.FieldName + " = CAST('" + value + "' AS " + config.SQLDataType + ") , ";
}
}
SQL += " Enabled = CAST('" + fc["Enabled"] + "' AS BIT) , ";
SQL += " SortOrder = " + fc["SortOrder"] + " ";
SQL += " WHERE ID = " + RecordID;
bool Result = ExecuteSql(SQL);
return(Result);
}
public bool InsertStandardValue(FormCollection 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);
//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<SelectListItem> GetStandardValuesDDL(int StandardID, string ValueFieldName, string TextFieldName)
//{
// List<SelectListItem> Result = new List<SelectListItem>();
// StandardModel model = new StandardModel().SelectSingleStandard(StandardID);
// DataTable sValues = new DataTable(model.DBTableName);
// #region Execute SQL
// string SQL = "SELECT * FROM " + model.DBTableName + " WHERE enabled = 1 ORDER BY SortOrder";
// AutomationStandardsContext db = new AutomationStandardsContext();
// using (SqlConnection sqlConn = new SqlConnection(AutomationStandardsContext.ConnectionString))
// using (SqlCommand cmd = new SqlCommand(SQL, sqlConn))
// {
// sqlConn.Open();
// sValues.Load(cmd.ExecuteReader());
// sqlConn.Close();
// }
// #endregion
// foreach (DataRow dr in sValues.Rows)
// {
// Result.Add(new SelectListItem() { Text = dr[TextFieldName].ToString(), Value = dr[ValueFieldName].ToString() });
// }
// return Result;
//}
public List <SelectListItem> GetStandardValuesDDL(int StandardID, string ValueFieldName, string TextFieldName, bool filterResults, string filterColumn, string filterValue)
{
List <SelectListItem> Result = new List <SelectListItem>();
StandardModel model = new StandardModel().SelectSingleStandard(StandardID);
DataTable sValues = GetStandardValues(model.DBTableName);
foreach (DataRow dr in sValues.Rows)
{
if (dr["Enabled"].ToString().ToLower() == "true")
{
if (filterResults)
{
if (dr[filterColumn].ToString().ToLower().Contains(";"))
{
if (dr[filterColumn].ToString().ToLower().Contains(";" + filterValue.ToLower() + ";"))
{
Result.Add(new SelectListItem()
{
Text = dr[TextFieldName].ToString(), Value = dr[ValueFieldName].ToString()
});
}
}
else
{
if (dr[filterColumn].ToString().ToLower().Contains(filterValue.ToLower()))
{
Result.Add(new SelectListItem()
{
Text = dr[TextFieldName].ToString(), Value = dr[ValueFieldName].ToString()
});
}
}
}
else
{
Result.Add(new SelectListItem()
{
Text = dr[TextFieldName].ToString(), Value = dr[ValueFieldName].ToString()
});
}
}
}
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 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 List <SelectListItem> GetStandardValueDDLFiltered(int StandardID, string ValueFieldName, string TextFieldName, bool UseFilter, string SQLFilter, string FilterFieldName, string FilterValue)
{
List <SelectListItem> Result = new List <SelectListItem>();
StandardModel model = new StandardModel().SelectSingleStandard(StandardID);
DataTable sValues = GetStandardValuesCustomFilter(model.DBTableName, UseFilter, SQLFilter);
foreach (DataRow dr in sValues.Rows)
{
if (dr["Enabled"].ToString().ToLower() == "true")
{
if (dr[FilterFieldName].ToString().ToLower().Contains(";"))
{
if (dr[FilterFieldName].ToString().ToLower().Contains(";" + FilterValue.ToLower() + ";"))
{
Result.Add(new SelectListItem()
{
Text = dr[TextFieldName].ToString(), Value = dr[ValueFieldName].ToString()
});
}
}
else
{
if (dr[FilterFieldName].ToString().ToLower().Contains(FilterValue.ToLower()))
{
Result.Add(new SelectListItem()
{
Text = dr[TextFieldName].ToString(), Value = dr[ValueFieldName].ToString()
});
}
}
}
}
return(Result);
}
public bool InsertStandard(StandardModel model)
{
bool OkToCreate = false;
AutomationStandardsContext db = new AutomationStandardsContext();
var CurrentStds = (from s in db.Standard where s.DBTableName.ToLower() == model.DBTableName.ToLower() select s).ToList();
if (CurrentStds.Count == 0)
{
OkToCreate = true;
}
if (OkToCreate)
{
Standard Result = new Standard();
Result.StandardName = model.StandardName;
Result.DBTableName = model.DBTableName;
Result.StandardDefinition = model.StandardDefinition;
Result.StandardGroupID = model.StandardGroupID;
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;
Result.UsageCount = 0;
db.Standard.Add(Result);
try
{
db.SaveChanges();
model.StandardID = Result.StandardID;
//Create Table in DB with MODEL Values
StandardSQLManagement sql = new StandardSQLManagement();
bool SQLOK = sql.CreateStandardTable(model.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);
}