public static void Create(FormField m_FormField)
{
SqlConnection conn = DB.DbConnect();
conn.Open();
string queryString = "INSERT INTO CMS_FormFields(label, fieldType, parentId, validationType, fieldText) VALUES(@label, @fieldType, @parentId, @validationType, @fieldText)";
SqlCommand insertFormField = new SqlCommand(queryString, conn);
insertFormField.Parameters.AddWithValue("label", m_FormField.Label ?? "");
insertFormField.Parameters.AddWithValue("fieldType", m_FormField.FieldType);
insertFormField.Parameters.AddWithValue("parentId", m_FormField.ParentId);
insertFormField.Parameters.AddWithValue("validationType", m_FormField.ValidationType);
insertFormField.Parameters.AddWithValue("fieldText", m_FormField.LabelText ?? string.Empty);
insertFormField.ExecuteNonQuery();
queryString = "SELECT IDENT_CURRENT('CMS_FormFields')";
SqlCommand getFormFieldId = new SqlCommand(queryString, conn);
int m_FormFieldId = (int)(decimal)getFormFieldId.ExecuteScalar();
foreach (FormField temp in m_FormField.Children)
{
queryString = "INSERT INTO CMS_FormFields(label, fieldType, parentId) VALUES(@label, @fieldType, @parentId)";
SqlCommand insertTemp = new SqlCommand(queryString, conn);
insertTemp.Parameters.AddWithValue("label", temp.Label ?? "");
insertTemp.Parameters.AddWithValue("fieldType", temp.FieldType);
insertTemp.Parameters.AddWithValue("parentId", m_FormFieldId);
insertTemp.ExecuteNonQuery();
}
conn.Close();
}
public ActionResult AddFormField()
{
FormField m_FormField = new FormField();
ViewBag.FieldTypes = FormFieldRepository.getFieldTypes();
ViewBag.ValidationTypes = FormFieldRepository.getValidationTypes();
return View("AddFormField", m_FormField);
}
public ActionResult AddFormField(FormField m_FormField, string[] childrenTitle, string[] childrenValue)
{
ViewBag.DisplayInfo = 0;
if (childrenTitle == null)
{
ViewBag.childrenTitle = new string[0];
}
else
{
ViewBag.childrenTitle = childrenTitle;
}
if (childrenValue == null)
{
ViewBag.childrenTitle = new string[0];
}
else
{
ViewBag.childrenValue = childrenValue;
}
ViewBag.FieldTypes = FormFieldRepository.getFieldTypes();
ViewBag.ValidationTypes = FormFieldRepository.getValidationTypes();
if (ModelState.IsValid)
{
FormFieldRepository.Create(m_FormField, childrenTitle, childrenValue);
return RedirectToAction("Index", "FormField");
}
else
{
ViewBag.DisplayInfo = 1;
if (m_FormField.FieldType == 3)
{
ViewBag.Type = "Checkbox";
}
else if (m_FormField.FieldType == 4)
{
ViewBag.Type = "Radio";
}
else if (m_FormField.FieldType == 5)
{
ViewBag.Type = "Option";
}
return View("AddFormField", m_FormField);
}
}
public void Create(FormField m_FormField, string[] childrenTitle)
{
if (childrenTitle != null)
{
foreach (string c_Title in childrenTitle)
{
if (c_Title.Length > 0)
{
FormField temp = new FormField();
temp.Label = c_Title;
temp.FieldType = m_FormField.FieldType;
m_FormField.Children.Add(temp);
}
}
}
DBFormField.Create(m_FormField);
}
public void Update(FormField m_FormField, string[] childrenTitle)
{
DBFormField.DeleteChildren(m_FormField.Id);
if (childrenTitle != null)
{
foreach (string label in childrenTitle)
{
if (label.Length > 0)
{
FormField temp = new FormField();
temp.Label = label;
temp.FieldType = m_FormField.FieldType;
temp.ParentId = m_FormField.Id;
DBFormField.Create(temp);
}
}
}
DBFormField.Update(m_FormField);
}
public void Create(FormField m_FormField, string[] childrenTitle, string[] childrenValue)
{
if (childrenTitle != null)
{
int count = 0;
foreach (string c_Title in childrenTitle)
{
if (m_FormField.FieldType == 10)
{
if (c_Title.Length > 0 && childrenValue[count].Length > 0)
{
FormField temp = new FormField();
temp.Label = c_Title + ":" + childrenValue[count];
temp.FieldType = m_FormField.FieldType;
m_FormField.Children.Add(temp);
}
}
else
{
if (c_Title.Length > 0)
{
FormField temp = new FormField();
temp.Label = c_Title;
temp.FieldType = m_FormField.FieldType;
m_FormField.Children.Add(temp);
}
}
count++;
}
}
DBFormField.Create(m_FormField);
}
public static List<FormField> getFormFields(int id)
{
SqlConnection conn = DB.DbConnect();
conn.Open();
string queryString = "SELECT * FROM CMS_FormFields as ff, CMS_FormToFormFields as fff WHERE ff.id = fff.formFieldId and fff.formId = @id ORDER BY sortOrder ASC";
SqlCommand getFields = new SqlCommand(queryString, conn);
getFields.Parameters.AddWithValue("id", id);
SqlDataReader formFieldsReader = getFields.ExecuteReader();
List<FormField> m_FormFields = new List<FormField>();
while (formFieldsReader.Read())
{
FormField temp = new FormField();
temp.Id = formFieldsReader.GetInt32(0);
temp.Label = formFieldsReader.GetString(1);
temp.FieldType = formFieldsReader.GetInt32(2);
temp.ParentId = formFieldsReader.GetInt32(3);
temp.IsRequired = formFieldsReader.GetInt32(10);
m_FormFields.Add(temp);
}
conn.Close();
return m_FormFields;
}
public static Form RetrieveOne(int id)
{
SqlConnection conn = DB.DbConnect();
conn.Open();
string queryString = "SELECT * FROM CMS_Forms WHERE id = @id AND pageWorkFlowState != 4";
SqlCommand getForm = new SqlCommand(queryString, conn);
getForm.Parameters.AddWithValue("id", id);
SqlDataReader formReader = getForm.ExecuteReader();
Form m_Form = new Form();
if (formReader.Read())
{
SqlConnection conn2 = DB.DbConnect();
conn2.Open();
m_Form.Id = formReader.GetInt32(0);
m_Form.FormName = formReader.GetString(1);
m_Form.SubmissionEmail = formReader.GetString(2);
m_Form.MyFormFields = new List<int>();
queryString = "SELECT * FROM CMS_FormFields as ff, CMS_FormToFormFields as fff WHERE ff.id = fff.formFieldId AND fff.formId = @formId ORDER BY sortOrder ASC";
SqlCommand getFormFields = new SqlCommand(queryString, conn2);
getFormFields.Parameters.AddWithValue("formId", id);
SqlDataReader formFieldsReader = getFormFields.ExecuteReader();
while (formFieldsReader.Read())
{
FormField temp = new FormField();
temp.Id = formFieldsReader.GetInt32(0);
temp.Label = formFieldsReader.GetString(1);
temp.FieldType = formFieldsReader.GetInt32(2);
temp.ParentId = formFieldsReader.GetInt32(3);
temp.IsRequired = formFieldsReader.GetInt32(10);
temp.Children = DBFormField.RetrieveChildren(temp.Id);
m_Form.MyFormFields.Add(temp.Id);
m_Form.FormFields.Add(temp);
}
conn2.Close();
}
conn.Close();
return m_Form;
}
public static List<Form> RetrieveAll()
{
SqlConnection conn = DB.DbConnect();
conn.Open();
string queryString = "SELECT * FROM CMS_Forms WHERE pageWorkFlowState != 4";
SqlCommand getForm = new SqlCommand(queryString, conn);
SqlDataReader formReader = getForm.ExecuteReader();
List<Form> m_Forms = new List<Form>();
while (formReader.Read())
{
SqlConnection conn2 = DB.DbConnect();
conn2.Open();
Form tempForm = new Form();
tempForm.Id = formReader.GetInt32(0);
tempForm.FormName = formReader.GetString(1);
tempForm.SubmissionEmail = formReader.GetString(2);
queryString = "SELECT * FROM CMS_FormFields as ff, CMS_FormToFormFields as fff WHERE ff.id = fff.formFieldId AND fff.formId = @formId";
SqlCommand getFormFields = new SqlCommand(queryString, conn2);
getFormFields.Parameters.AddWithValue("formId", tempForm.Id);
SqlDataReader formFieldsReader = getFormFields.ExecuteReader();
while (formFieldsReader.Read())
{
FormField temp = new FormField();
temp.Id = formFieldsReader.GetInt32(0);
temp.Label = formFieldsReader.GetString(1);
temp.FieldType = formFieldsReader.GetInt32(2);
temp.ParentId = formFieldsReader.GetInt32(3);
temp.IsRequired = formFieldsReader.GetInt32(10);
tempForm.FormFields.Add(temp);
}
m_Forms.Add(tempForm);
conn2.Close();
}
conn.Close();
return m_Forms;
}
public static void Update(FormField m_FormField)
{
SqlConnection conn = DB.DbConnect();
conn.Open();
string queryString = "UPDATE CMS_FormFields SET label = @label, fieldType = @fieldType, parentId = @parentId, validationType = @validationType, fieldText = @fieldText WHERE id = @id";
SqlCommand updateFormField = new SqlCommand(queryString, conn);
updateFormField.Parameters.AddWithValue("label", m_FormField.Label ?? "");
updateFormField.Parameters.AddWithValue("fieldType", m_FormField.FieldType);
updateFormField.Parameters.AddWithValue("parentId", m_FormField.ParentId);
updateFormField.Parameters.AddWithValue("id", m_FormField.Id);
updateFormField.Parameters.AddWithValue("validationType", m_FormField.ValidationType);
updateFormField.Parameters.AddWithValue("fieldText", m_FormField.LabelText);
updateFormField.ExecuteNonQuery();
conn.Close();
}
public static FormField RetrieveOne(int id)
{
SqlConnection conn = DB.DbConnect();
conn.Open();
string queryString = "SELECT * FROM CMS_FormFields WHERE id = @id AND pageWorkFlowState != 4";
SqlCommand getFormField = new SqlCommand(queryString, conn);
getFormField.Parameters.AddWithValue("id", id);
SqlDataReader formFieldReader = getFormField.ExecuteReader();
FormField m_FormField = new FormField();
if (formFieldReader.Read())
{
m_FormField.Id = formFieldReader.GetInt32(0);
m_FormField.Label = formFieldReader.GetString(1);
m_FormField.FieldType = formFieldReader.GetInt32(2);
m_FormField.FieldTypeText = getFieldTypeText(formFieldReader.GetInt32(2));
m_FormField.ParentId = formFieldReader.GetInt32(3);
m_FormField.ValidationType = formFieldReader.GetInt32(5);
if (!DBNull.Value.Equals(formFieldReader[6]))
{
m_FormField.LabelText = formFieldReader.GetString(6);
}
if (m_FormField.FieldType == 3 || m_FormField.FieldType == 4 || m_FormField.FieldType == 5 || m_FormField.FieldType == 10)
{
m_FormField.Children = RetrieveChildren(m_FormField.Id);
}
}
conn.Close();
return m_FormField;
}
public static List<FormField> RetrieveChildren(int parentId)
{
SqlConnection conn = DB.DbConnect();
conn.Open();
string queryString = "SELECT * FROM CMS_FormFields WHERE parentId = @parentId ORDER BY id";
SqlCommand getChildren = new SqlCommand(queryString, conn);
getChildren.Parameters.AddWithValue("parentId", parentId);
SqlDataReader childrenReader = getChildren.ExecuteReader();
List<FormField> m_Children = new List<FormField>();
while (childrenReader.Read())
{
FormField temp = new FormField();
temp.Id = childrenReader.GetInt32(0);
temp.Label = childrenReader.GetString(1);
temp.FieldType = childrenReader.GetInt32(2);
temp.ParentId = childrenReader.GetInt32(3);
m_Children.Add(temp);
}
conn.Close();
return m_Children;
}
public static List<FormField> RetrieveAll()
{
SqlConnection conn = DB.DbConnect();
conn.Open();
string queryString = "SELECT * FROM CMS_FormFields WHERE parentId = 0 AND pageWorkFlowState != 4 ORDER BY label";
SqlCommand getFormFields = new SqlCommand(queryString, conn);
SqlDataReader formFieldsReader = getFormFields.ExecuteReader();
List<FormField> m_FormFields = new List<FormField>();
while (formFieldsReader.Read())
{
FormField temp = new FormField();
temp.Id = formFieldsReader.GetInt32(0);
temp.Label = formFieldsReader.GetString(1);
temp.FieldType = formFieldsReader.GetInt32(2);
temp.FieldTypeText = getFieldTypeText(formFieldsReader.GetInt32(2));
temp.ParentId = formFieldsReader.GetInt32(3);
temp.Children = RetrieveChildren(temp.ParentId);
temp.ValidationType = formFieldsReader.GetInt32(5);
if (!DBNull.Value.Equals(formFieldsReader[6]))
{
temp.LabelText = formFieldsReader.GetString(6);
}
m_FormFields.Add(temp);
}
conn.Close();
return m_FormFields;
}
public void Update(FormField m_FormField, string[] childrenTitle, string[] childrenValue)
{
DBFormField.DeleteChildren(m_FormField.Id);
if (childrenTitle != null)
{
int count = 0;
foreach (string label in childrenTitle)
{
if (m_FormField.FieldType == 10)
{
if (label.Length > 0 && childrenValue[count].Length > 0)
{
FormField temp = new FormField();
temp.Label = label + ":" + childrenValue[count];
temp.FieldType = m_FormField.FieldType;
temp.ParentId = m_FormField.Id;
DBFormField.Create(temp);
}
}
else
{
if (label.Length > 0)
{
FormField temp = new FormField();
temp.Label = label;
temp.FieldType = m_FormField.FieldType;
temp.ParentId = m_FormField.Id;
DBFormField.Create(temp);
}
}
count++;
}
}
DBFormField.Update(m_FormField);
}
