public static FormDTO Get(int productId, int step, int userId)
{
using (ProtectorUsFormDb db = new ProtectorUsFormDb())
{
var query = db.Forms
.Where(f => f.ProductId == productId && f.Active)
.Select(f => new FormDTO()
{
FormId = f.FormId,
Name = f.Name,
Inputs = f.FormInputOptions.Where(fio => (int)fio.StepOrder == step).OrderBy(fio=>fio.StepOrder).GroupBy(fio=>fio.FormInputId).Select(fio=>fio.FirstOrDefault()).Select(fio => new InputDTO()
{
FormInputOptionId = fio.FormInputOptionId,
Id = fio.FormInput.FormInputId,
Name = fio.FormInput.Name,
Type = fio.FormInput.Type,
TextBefore = fio.FormInput.TextBefore,
TextAfter = fio.FormInput.TextAfter,
CssClass = fio.FormInput.CssClass,
Options = f.FormInputOptions.Where(fio2 => fio2.FormInputId == fio.FormInputId).Select(fio2 => new FormOptionDTO()
{
Id = fio2.FormInputOptionId,
Text = fio2.FormOption.Text
})
})
});
FormDTO form = query.FirstOrDefault();
var formValueQuery = from fv in db.FormValues
join fio in db.FormInputOptions on fv.InputId equals fio.FormInputOptionId
where fio.FormId == form.FormId && fv.UserId == userId && (int)fio.StepOrder == step
select fv;
form.Values = formValueQuery.ToList();
return form;
}
}
public static IEnumerable<FormAnsweredDTO> GetAll(int formId, List<FormFilter> filters)
{
using (ProtectorUsFormDb db = new ProtectorUsFormDb())
{
var inputNames = (from fio in db.FormInputOptions
join fi in db.FormInputs on fio.FormInputId equals fi.FormInputId
where fio.FormId == formId
select fi.Name
).Distinct().ToArray();
var inputNamesString = string.Join(",", inputNames);
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append("select * from (select ");
sb.Append(" fi.Name as inputName,");
sb.Append(" case");
sb.Append(" when value is null then cast(fv.ValueOptionId as varchar) + '=>'+fo.Text");
sb.Append(" else fv.Value");
sb.Append(" end as Value,");
sb.Append(" u.Name as userName");
sb.Append(" from FormValue fv");
sb.Append(" inner join FormInputOption fioi on fioi.FormInputOptionId = fv.InputId");
sb.Append(" inner join FormInput fi on fi.FormInputId = fioi.FormInputId");
sb.Append(" left join FormInputOption fiov on fiov.FormInputOptionId = fv.ValueOptionId");
sb.Append(" left join FormOption fo on fo.FormOptionId = fiov.FormOptionId");
sb.Append(" inner join [User] u on u.UserId = fv.UserId");
sb.AppendFormat(" where fioi.FormId = {0}) as t1", formId);
sb.Append(" pivot");
sb.Append("(");
sb.Append(" max(value)");
sb.AppendFormat(" for inputName in ({0})", inputNamesString);
sb.Append(") as t2 ");
filters.RemoveAll(f => f.Value == null);
if (filters != null && filters.Count > 0)
{
sb.Append(" where ");
string field;
string value;
string condition;
foreach (var filter in filters)
{
field = filter.Field;
value = filter.Value;
condition = "=";
switch (filter.Type)
{
case FormInputType.Text:
condition = " like ";
value = $"'%{filter.Value}%'";
break;
case FormInputType.Select:
field = $" SUBSTRING({filter.Field},0,PATINDEX('%=>%',{filter.Field}))";
value = filter.Value;
break;
case FormInputType.Number:
switch (filter.Condition)
{
case FormFilterCondition.Equal:
condition = " = ";
break;
case FormFilterCondition.Greater:
condition = " < ";
break;
case FormFilterCondition.Smaller:
condition = " > ";
break;
case FormFilterCondition.GreaterOrEqual:
condition = " <= ";
break;
case FormFilterCondition.SmallerOrEqual:
condition = " >= ";
break;
default:
condition = " like ";
break;
}
field = $"try_parse({filter.Field} as decimal)";
value = filter.Value;
break;
case FormInputType.Date:
field = $"try_parse({filter.Field} as date using 'pt-BR')";
value = $"try_parse('{filter.Value}' as date)";
if (filter.Condition == FormFilterCondition.Between)
{
value += $" and try_parse('{filter.Value2}' as date)";
condition = "between";
}
break;
}
var andOr = filter.And ? "and" : "or";
sb.AppendFormat($" ({field} {condition} {value}) {andOr}");
}
sb.Length = sb.Length - 4;
}
if (db.Database.Connection.State != System.Data.ConnectionState.Open)
{
db.Database.Connection.Open();
}
var dbCmd = db.Database.Connection.CreateCommand();
dbCmd.CommandText = sb.ToString();
List<FormAnsweredDTO> formAnswereds = new List<FormAnsweredDTO>();
using (var dr = dbCmd.ExecuteReader())
{
var inputs = new List<InputDTO>();
foreach (var name in inputNames)
{
inputs.Add(new InputDTO()
{
Name = name
});
}
while (dr.Read())
{
var formAnswered = new FormAnsweredDTO()
{
Form = new FormDTO()
{
Inputs = inputs
},
User = new UserDTO() { Name = dr.GetString(0) }
};
var values = new List<object>();
foreach (var name in inputNames)
{
values.Add(Convert.ToString(dr[name]));
}
formAnswered.Values = values;
formAnswereds.Add(formAnswered);
}
}
db.Database.Connection.Close();
return formAnswereds;
}
}
public static void Reply(IEnumerable<FormValue> formValues)
{
using (ProtectorUsFormDb db = new ProtectorUsFormDb())
{
foreach (var value in formValues)
{
db.FormValues.AddOrUpdate(value);
}
db.SaveChanges();
}
}
