/// <summary>
/// Populates the grid with all the forms and their properties
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void ldsForm_Selecting(object sender, LinqDataSourceSelectEventArgs e)
{
GRASPEntities db = new GRASPEntities();
var forms = from f in db.Form
where f.finalised == 1
select new
{
Name = f.name,
CreateDate = f.FormCreateDate,
Owner = f.owner,
Group = f.permittedGroup_path,
Id = f.id
//Actions = "<a style=\"color:#0058B1\" href=\"/Admin/Data_Entry/DataEntry.aspx?formID=" + SqlFunctions.StringConvert(f.id).TrimStart() + "\"><i class=\"fa fa-pencil-square-o\"></i> New Data</a>"
};
e.Result = forms.AsEnumerable().Select(x => new
{
Name = x.Name,
CreateDate = x.CreateDate,
Owner = x.Owner,
Group = x.Group,
Actions = "<a style=\"color:#0058B1\" href=\"DataEntryWebForm.aspx?formID=" + x.Id.ToString() + "\"><i class=\"fa fa-pencil-square-o\"></i> New Data</a>"
});
}
public static Survey createSurvey(ImportingElement element, int id, string flag)
{
GRASPEntities db = new GRASPEntities();
Survey survey = new Survey();
survey.name = element.refListName;
survey.owner_id = id;
db.Survey.Add(survey);
db.SaveChanges();
if (flag == "select1")
{
foreach (string label in element.select1Labels)
{
int size = FormFieldExport.getSurveyList(survey.id);
SurveyElement se = createSurveyElement(label, size);
createSurveyAssociation(survey.id, se.id);
}
}
if (flag == "survey")
{
foreach (string label in element.surveyValues)
{
int size = FormFieldExport.getSurveyList(survey.id);
SurveyElement se = createSurveyElement(label, size);
createSurveyAssociation(survey.id, se.id);
}
}
return survey;
}
/// <summary>
/// Checks whether the logged user has the givin permission.
/// </summary>
/// <param name="permission"></param>
/// <returns></returns>
/// <author>Saad Mansour</author>
public static bool IsLoggedUserHasPermission(GeneralEnums.Permissions permission)
{
bool isLoggedUserInRole = false;
try
{
string loggedUserName = HttpContext.Current.User.Identity.Name.ToUpper();
int userRoleID = User_Credential.GetRoleIdByUserName(loggedUserName);
using (GRASPEntities db = new GRASPEntities())
{
var permissionName = from p in db.Permissions
join rp in db.RolePermissions on p.id equals rp.PermissionID
where rp.RoleID == userRoleID
select p.Name;
if (permissionName.Contains(permission.ToString()))
{
isLoggedUserInRole = true;
}
}
}
catch (Exception ex)
{
LogUtils.WriteErrorLog(ex.ToString());
}
return isLoggedUserInRole;
}
/// <summary>
/// Insert a ResponseValue record using an external DB entity without committing the DB changes.
/// </summary>
/// <param name="db">Database Entity</param>
/// <param name="value">String containing the answer</param>
/// <param name="formResponseID">Int the responseID</param>
/// <param name="formFieldID"></param>
/// <param name="rCount">If the field is inside a repeatable, specify the current numeber of repetition</param>
/// <returns>Return the ResponseValue record inserted in the database</returns>
public static ResponseValue createResponseValue(GRASPEntities db, string value, int formResponseID, int formFieldID,
int positionIndex, int rCount, string fieldType)
{
if(db != null)
{
var response = new ResponseValue();
response.value = value;
response.FormResponseID = formResponseID;
response.formFieldId = formFieldID;
response.RVCreateDate = DateTime.Now;
response.RVRepeatCount = rCount;
response.positionIndex = positionIndex;
UpdateNumericAndDate(response, fieldType);
db.ResponseValue.Add(response);
//ONLY FOR DEBUG
//try
//{
// //db.SaveChanges();
//}
//catch(Exception ex)
//{
// string err = ex.StackTrace;
//}
return response;
}
else
{
return null;
}
}
public static int DeleteHASHes(int indexID)
{
using(GRASPEntities db = new GRASPEntities())
{
return db.Database.ExecuteSqlCommand("DELETE FROM IndexHASHes WHERE IndexID = @indexID", new SqlParameter("@indexID", indexID));
}
}
protected void grdIndexFields_ItemDataBound(object sender, GridItemEventArgs e)
{
if(e.Item is GridEditFormItem && e.Item.IsInEditMode)
{
GridEditableItem item = e.Item as GridEditableItem;
// access/modify the edit item template settings here
RadComboBox list = item.FindControl("ddlFormFields") as RadComboBox;
//decimal ffID = (item.DataItem as IndexField).FormFieldID;
int formID = Convert.ToInt32(ddlForms.SelectedValue);
GRASPEntities db = new GRASPEntities();
List<FormField> ffs = (from f in db.FormField
where f.form_id == formID && f.type != "SEPARATOR" && f.type != "TRUNCATED_TEXT" &&
f.type != "WRAPPED_TEXT" && f.type != "REPEATABLES_BASIC" && f.type != "REPEATABLES"
orderby f.positionIndex
select f).ToList();
list.DataSource = ffs;
list.DataBind();
//if(Session["updatedValue"] != null)
//{
// list.SelectedValue = Session["updatedValue"].ToString();
//}
}
else if(e.Item is GridDataItem && !e.Item.IsInEditMode && Page.IsPostBack)
{
//GridDataItem item = e.Item as GridDataItem;
//Label label = item.FindControl("lblFormFieldName") as Label;
//// update the label value
//label.Text = Session["updatedValue"].ToString();
}
}
/// <summary>
/// When the page loads it queries the DB to obtain all the formResponse with geolocalization.
/// All the coordinates are stored in a string that will be used by Google Maps APIs
/// to create the markers on the map
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Page_Load(object sender, EventArgs e)
{
GRASPEntities db = new GRASPEntities();
foreach (decimal ffID in FormField.getFormFieldFromType("GEOLOCATION"))
{
var coordinates = (from rv in db.ResponseValue
where rv.formFieldId == ffID
orderby rv.FormResponseID descending
select rv).Take(100);
foreach(var coord in coordinates)
{
if (!string.IsNullOrEmpty(coord.value))
{
string[] ltnlng = coord.value.Split(' ');
if(ltnlng[0] != "0" && ltnlng[1] != "0")
{
geoTmp += "[" + ltnlng[1] + ", " + ltnlng[0] + ", " + coord.FormResponseID + "],";
}
}
}
}
if(geoTmp.Length > 0)
geoCoordinates = geoTmp.Substring(0, geoTmp.Length - 1);
}
protected void grdIndexFields_InsertCommand(object sender, GridCommandEventArgs e)
{
var editableItem = ((GridEditableItem)e.Item);
//populate its properties
Hashtable values = new Hashtable();
editableItem.ExtractValues(values);
RadComboBox combo = editableItem.FindControl("ddlFormFields") as RadComboBox;
string ffID = combo.SelectedValue;
GRASPEntities db = new GRASPEntities();
//create new entity
IndexField idx = new IndexField();
idx.FormFieldID = Convert.ToDecimal(ffID);
idx.IndexID = Convert.ToInt32(grdIndex.SelectedValue.ToString());
db.IndexFields.Add(idx);
try
{
//submit chanages to Db
db.SaveChanges();
}
catch(System.Exception ex)
{
SetMessage(ex.Message);
}
}
public static bool InsertOrUpdateStatus(string fileName, bool isSaved)
{
bool isSuccess = false;
try
{
using (GRASPEntities db = new GRASPEntities())
{
var formResponseServerStatus = (from fr in db.FormResponseServerStatus
where fr.InstanceUniqueIdentifier == fileName
select fr).FirstOrDefault();
if (formResponseServerStatus != null)
{
formResponseServerStatus.IsSavedToServer = isSaved;
}
else
{
formResponseServerStatus = new FormResponseServerStatus();
formResponseServerStatus.InstanceUniqueIdentifier = fileName;
formResponseServerStatus.IsSavedToServer = isSaved;
db.FormResponseServerStatus.Add(formResponseServerStatus);
}
db.SaveChanges();
isSuccess = true;
}
}
catch (Exception)
{
}
return isSuccess;
}
/// <summary>
/// Fills the grid with all the forms
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void ldsForm_Selecting(object sender, LinqDataSourceSelectEventArgs e)
{
GRASPEntities db = new GRASPEntities();
var forms = (from f in db.Form
join fr in db.FormResponse on f.id equals fr.parentForm_id into j1
from j2 in j1.DefaultIfEmpty()
where f.finalised == 1
group j2 by new { f.id, f.name, f.FormCreateDate, f.owner, f.permittedGroup_path} into g
select new
{
Name = g.Key.name,
CreateDate = g.Key.FormCreateDate,
Owner = g.Key.owner,
Group = g.Key.permittedGroup_path,
Count = g.Count(t=>t.id !=null),
Id = g.Key.id
}).OrderByDescending(x => x.Id); //Order them by newest.
e.Result = forms.AsEnumerable().Select(x => new
{
Name = x.Name,
CreateDate = x.CreateDate.Value.ToString("dd MMM yyyy"),
Owner = x.Owner,
Group =x.Group,
Responses = x.Count,
Id = x.Id
//Actions = (x.Count>0) ?"<a style=\"color:#0058B1\" href=\"javascript:ImportForm('" + x.Id.ToString() + "','" + x.Name + "');void(0);\"><i class=\"fa fa-upload fa-2\"></i>Import</a>"+
//" <a style=\"margin-left: 5px;color:#0058B1\" href=\"javascript:ExportSettings('" + x.Id.ToString() + "','" + x.Name + "');void(0);\"><i class=\"fa fa-download fa-2\"></i>Export</a>"+
//" <a style=\"margin-left: 5px;color:#0058B1\" href=\"javascript:ViewForm('" + x.Id.ToString() + "','" + x.Name + "');void(0);\"><i class=\"fa fa-eye fa-2\"></i>View</a>" : "<a style=\"color:#0058B1\" href=\"javascript:ImportForm('" + x.Id.ToString() + "','" + x.Name + "');void(0);\"><i class=\"fa fa-upload fa-2\"></i>Import</a>"
});
}
protected void GrdCalcFields_NeedDataSource(object sender, Telerik.Web.UI.GridNeedDataSourceEventArgs e)
{
int formID = 0;
if(Request["FormID"] != null && Request["FormID"] != "")
{
formID = Convert.ToInt32(Request["FormID"]);
lnkCalcFieldList.Attributes["href"] = "CalculatedFieldInsert.aspx?FormID=" + formID.ToString();
using(GRASPEntities db = new GRASPEntities())
{
var formFieldExt = (from ffe in db.FormFieldExt
where ffe.FormID == formID
select ffe).OrderBy(o => o.PositionIndex);
if(Request["recalcall"] != null && Request["recalcall"].ToString() == "true")
{
foreach(var f in formFieldExt)
{
LitMessage.Text += "<br/>" + f.FormFieldExtName + ": " + ServerSideCalculatedField.Generate(f.FormFieldExtID);
}
}
GrdCalcFields.DataSource = formFieldExt.ToList();
}
}
}
public static void RemoveFilter(int formID, int userID)
{
using(GRASPEntities db = new GRASPEntities())
{
db.Database.ExecuteSqlCommand("DELETE UserFilters WHERE formID=@formID AND userID=@userID", new SqlParameter("@formID", formID), new SqlParameter("@userID", userID));
}
}
/// <summary>
/// Get the count of a given field.
/// </summary>
/// <param name="formID"></param>
/// <param name="formFieldID"></param>
/// <returns></returns>
public static int GetFieldCount(int formID, int formFieldID)
{
int count = 0;
try
{
using (GRASPEntities db = new GRASPEntities())
{
//Finds the occurrences of the date field.
count = (from ffr in db.FormFieldResponses
where ffr.parentForm_id == formID
&& ffr.formFieldId == formFieldID
select ffr.nvalue).Count();
}
}
catch (Exception ex)
{
LogUtils.WriteErrorLog(ex.ToString());
}
if (count == null)
{
count = 0;
}
return count;
}
public static void InsertHashString(GRASPEntities db, int formResponseID, int indexID, string stringToHASH)
{
IndexHASH h = new IndexHASH();
h.FormResponseID = formResponseID;
h.IndexID = indexID;
h.IndexHASHString = GetHashString(stringToHASH);
db.IndexHASHes.Add(h);
}
/// <summary>
/// </summary>
/// <param name="FormID">The id of the form</param>
/// <returns>A list representing all the formResponse for a form</returns>
protected IEnumerable<FormResponse> getCompiledForms(int FormID)
{
GRASPEntities db = new GRASPEntities();
var formResponse = from fr in db.FormResponse
where fr.parentForm_id == FormID
select fr;
return formResponse;
}
/// <summary>
/// </summary>
/// <param name="ffID">The id of a field</param>
/// <param name="FormResponseID">The id of a formResponse</param>
/// <returns>A list representing all the responsevalue for that field</returns>
protected ResponseValue getResponseValues(int ffID, int FormResponseID)
{
GRASPEntities db = new GRASPEntities();
var responseValue = (from rv in db.ResponseValue
where rv.FormResponseID == FormResponseID && rv.formFieldId == ffID
select rv).FirstOrDefault();
return responseValue;
}
/// <summary>
/// Fills the grid with the users with specified roles: SuperAdministrator, Supervisor, DataEntryOperator, Analyst
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void ldsRoles_Selecting(object sender, LinqDataSourceSelectEventArgs e)
{
GRASPEntities db = new GRASPEntities();
var roles = from r in db.Roles
where r.id == 3 || r.id == 4 || r.id == 7 || r.id >= 8
select r;
e.Result = roles;
}
public static ResponseValueExt Insert(GRASPEntities db, int formResponseID, int formFieldExtID, int formFieldID,int positionIndex, double nvalue)
{
ResponseValueExt rve = new ResponseValueExt();
rve.FormResponseID = formResponseID;
rve.FormFieldExtID = formFieldExtID;
rve.nvalue = nvalue;
rve.FormFieldID = formFieldID;
rve.PositionIndex = positionIndex;
db.ResponseValueExt.Add(rve);
return rve;
}
/// <summary>
/// Checks the Role table to obtain the role name
/// </summary>
/// <param name="roleID">the id of the role</param>
/// <returns>a string representing the role, empty string if the role not exists</returns>
public static string getRole(int roleID)
{
GRASPEntities db = new GRASPEntities();
string role = (from r in db.Roles
where r.id == roleID
select r.description).FirstOrDefault();
if (role != null)
return role;
else return "";
}
public static IEnumerable<SurveyElement> GetSurveyListElements(int p)
{
GRASPEntities db = new GRASPEntities();
IEnumerable<SurveyElement> list = from s in db.Survey
join sse in db.Survey_SurveyElement on s.id equals sse.Survey_id
join se in db.SurveyElement on sse.values_id equals se.id
where s.id == p
select se;
return list;
}
protected void BtnDelete_Command(object sender, CommandEventArgs e)
{
Telerik.Web.UI.ButtonCommandEventArgs arg = e as Telerik.Web.UI.ButtonCommandEventArgs;
int fieldExtID = Convert.ToInt32(arg.CommandArgument.ToString());
using(GRASPEntities db = new GRASPEntities())
{
db.Database.ExecuteSqlCommand("DELETE FROM FormFieldExt WHERE FormFieldExtID=" + fieldExtID);
db.Database.ExecuteSqlCommand("DELETE FROM ResponseValueExt WHERE FormFieldExtID=" + fieldExtID);
}
GrdCalcFields.Rebind();
LitMessage.Text = "The calculated field has been deleted.<br/>Now <strong>you must recalculate all the fields that was belonging to the field you have currently deleted, otherwise the results will not correct.</strong>";
}
/// <summary>
/// Create a single response for a formfield
/// </summary>
/// <param name="value">A string representing the response value</param>
/// <param name="formResponseID">The id of the formresponse</param>
/// <param name="formFieldID">The id of the formfield</param>
/// <param name="rCount">An int representing the repeatcount (-1 for roster/table, > 0 for roster children, 0 otherwise</param>
public static void createResponseValue(string value, int formResponseID, int formFieldID, int rCount)
{
GRASPEntities db = new GRASPEntities();
var response = new ResponseValue();
response.value = value;
response.FormResponseID = formResponseID;
response.formFieldId = formFieldID;
response.RVCreateDate = DateTime.Now;
response.RVRepeatCount = rCount;
db.ResponseValue.Add(response);
db.SaveChanges();
}
public static SurveyElement createSurveyElement(string value, int positionIndex)
{
GRASPEntities db = new GRASPEntities();
SurveyElement surveyel = new SurveyElement();
surveyel.value = value;
surveyel.positionIndex = positionIndex;
surveyel.defaultValue = 0;
db.SurveyElement.Add(surveyel);
db.SaveChanges();
return surveyel;
}
public static void createBinding(FormField ff, ImportingBindingContainer ibc)
{
GRASPEntities db = new GRASPEntities();
BindingContainer bc = new BindingContainer();
bc.pushed = 0;
bc.bType = ibc.bType.ToString();
bc.maxRange = ibc.maxRange;
bc.minRange = ibc.minRange;
bc.value = ibc.value;
bc.FormFieldAndBinding.Add(createFormFieldAndBinding(ff, bc.id));
db.BindingContainer.Add(bc);
db.SaveChanges();
}
public static void createConstraints(FormField ff, ImportConstraintContainer icc)
{
GRASPEntities db = new GRASPEntities();
ConstraintContainer cc = new ConstraintContainer();
cc.pushed = 0;
cc.cType = icc.cNumber.ToString();
cc.maxRange = icc.maxRange;
cc.minRange = icc.minRange;
cc.value = icc.value;
db.ConstraintContainer.Add(cc);
db.SaveChanges();
createconstraintAssociation(ff.id, cc.id);
}
public static UserFilters Insert(int formID, int userID, string filter, string filterDescription)
{
using(GRASPEntities db = new GRASPEntities())
{
UserFilters newUF = new UserFilters();
newUF.UserFilterCreateDate = DateTime.Now;
newUF.UserFilterIsEnabled = 1;
newUF.UserFilterString = filter;
newUF.UserFilterDescription = filterDescription;
newUF.formID = formID;
newUF.userID = userID;
db.UserFilters.Add(newUF);
db.SaveChanges();
return newUF;
}
}
/// <summary>
///
/// </summary>
/// <returns></returns>
/// <author>Saad Mansour</author>
public static List<Permissions> GetAllPermissionses()
{
List<Permissions> permissionses = null;
try
{
using (GRASPEntities db = new GRASPEntities())
{
permissionses = (from p in db.Permissions
select p).ToList();
}
}
catch (Exception ex)
{
LogUtils.WriteErrorLog(ex.ToString());
}
return permissionses;
}
public static Form createForm(string formID, string formName)
{
GRASPEntities db = new GRASPEntities();
Form form = new Form();
form.bindingsPolicy = "All";
form.designerVersion = "WebImport";
form.finalised = 0;
form.id_flsmsId = formID;
form.name = formName;
form.owner = "*****@*****.**";
form.FormCreateDate = DateTime.Now;
db.Form.Add(form);
db.SaveChanges();
return form;
}
/// <summary>
/// Parses the request headers to check the permission of the sender.
/// If it has a Basic Authentication then it makes all the checks on the user
/// </summary>
/// <param name="request">The HTTP request message to send</param>
/// <param name="cancellationToken">Token to send if the request will crash</param>
/// <returns>The task object representing the asynchronous operation</returns>
protected override Task<HttpResponseMessage> SendAsync(HttpRequestMessage request, CancellationToken cancellationToken)
{
var authHeader = request.Headers.Authorization;
if (authHeader == null)
{
return base.SendAsync(request, cancellationToken);
}
if (authHeader.Scheme != "Basic")
{
return base.SendAsync(request, cancellationToken);
}
var encodedUserPass = authHeader.Parameter.Trim();
var userPass = Encoding.ASCII.GetString(Convert.FromBase64String(encodedUserPass));
var parts = userPass.Split(":".ToCharArray());
var username = parts[0];
var password = parts[1];
GRASPEntities db = new GRASPEntities();
var item = (from u in db.User_Credential
join r in db.Roles on u.roles_id equals r.id
where u.username == username && u.password == password
select new { u, r }).FirstOrDefault();
if (item == null)
{
return base.SendAsync(request, cancellationToken);
}
var identity = new GenericIdentity(username, "Basic");
string[] roles = new string[1];
roles[0] = item.r.description;
var principal = new GenericPrincipal(identity, roles);
Thread.CurrentPrincipal = principal;
if (HttpContext.Current != null)
{
HttpContext.Current.User = principal;
}
return base.SendAsync(request, cancellationToken);
}
/// <summary>
/// Gets the max value of the given field.
/// </summary>
/// <param name="formID"></param>
/// <param name="formFieldID"></param>
/// <returns></returns>
/// <author>Saad Mansour</author>
public static string GetFieldMaxValue(int formID, int formFieldID, string fieldType)
{
//double? max = 0;
string max = string.Empty;
try
{
using (GRASPEntities db = new GRASPEntities())
{
switch (fieldType)
{
case "NUMERIC_TEXT_FIELD" :
case "SERVERSIDE_CALCULATED":
max = (from ffr in db.FormFieldResponses
where ffr.parentForm_id == formID
&& ffr.formFieldId == formFieldID
&& ffr.nvalue != null
select ffr.nvalue).Max().ToString();
break;
//case "DATE_FIELD":
// max = (from ffr in db.FormFieldResponses
// where ffr.parentForm_id == formID
// && ffr.formFieldId == formFieldID
// && ffr.dvalue != null
// select ffr.nvalue).Max().ToString();
// break;
default:
max = (from ffr in db.FormFieldResponses
where ffr.parentForm_id == formID
&& ffr.formFieldId == formFieldID
&& ffr.value != null
select ffr.value).Max();
break;
}
}
}
catch (Exception ex)
{
LogUtils.WriteErrorLog(ex.ToString());
}
return max;
}
