public static CustomValue GetValue(LoginUser loginUser, int customFieldID, int refID, bool createValue) { CustomValues values = new CustomValues(loginUser); values.LoadByFieldID(customFieldID, refID); if (values.IsEmpty) { CustomField field = CustomFields.GetCustomField(loginUser, customFieldID); values = new CustomValues(loginUser); CustomValue value = values.AddNewCustomValue(); value.CustomFieldID = customFieldID; value.Value = ""; if (field.FieldType == CustomFieldType.PickList) { string[] items = field.ListValues.Split('|'); if (items.Length > 0) { value.Value = items[0]; } } value.RefID = refID; if (createValue == false) { return(value); } value.Collection.Save(); values.LoadByFieldID(customFieldID, refID); return(values[0]); } else { return(values[0]); } }
partial void BeforeRowEdit(CustomValue newValue) { CustomValue oldValue = CustomValues.GetCustomValue(LoginUser, newValue.CustomValueID); newValue.OrganizationID = LoginUser.OrganizationID; if (oldValue.Value == newValue.Value) { return; } CustomField customField = CustomFields.GetCustomField(LoginUser, newValue.CustomFieldID); string format = "Changed {0} from '{1}' to '{2}'."; ActionLogs.AddActionLog(LoginUser, ActionLogType.Update, customField.RefType, newValue.RefID, string.Format(format, customField.Name, oldValue.Value, newValue.Value)); }
public string GetSQL() { StringBuilder builder = new StringBuilder(); ReportTables tables = new ReportTables(_loginUser); tables.LoadAll(); string con = _matchAll ? " AND " : " OR "; foreach (ReportCondition condition in _items) { string value = ""; if (condition.Value1 is string && condition.Value1.ToString().ToLower() == "self") { User user = Users.GetUser(_loginUser, _loginUser.UserID); condition.Value1 = user.FirstLastName; } if (condition.Value2 is string && condition.Value2.ToString().ToLower() == "self") { User user = Users.GetUser(_loginUser, _loginUser.UserID); condition.Value2 = user.FirstLastName; } if (!condition.IsCustomField) { ReportTableField field = (ReportTableField)ReportTableFields.GetReportTableField(_loginUser, condition.FieldID); if (condition.Value1.ToString().Trim() == "" && (condition.ConditionOperator == ConditionOperator.IsEqualTo || condition.ConditionOperator == ConditionOperator.IsNotEqualTo)) { //ticketsview.customers will not be null if (field.ReportTableFieldID == 251) { if (condition.ConditionOperator == ConditionOperator.IsEqualTo) { value = " = ''"; } else if (condition.ConditionOperator == ConditionOperator.IsNotEqualTo) { value = " <> ''"; } } else { if (condition.ConditionOperator == ConditionOperator.IsEqualTo) { value = " IS NULL"; } else if (condition.ConditionOperator == ConditionOperator.IsNotEqualTo) { value = " IS NOT NULL"; } } } else { switch (field.DataType) { case "bit": if (condition.ConditionOperator == ConditionOperator.IsEqualTo) { value = " = "; } else { value = " <> "; } if ((bool)condition.Value1) { value = value + "1"; } else { value = value + "0"; } break; case "datetime": if (condition.ConditionOperator == ConditionOperator.IsGreaterThan || condition.ConditionOperator == ConditionOperator.IsLessThan) { switch (condition.ConditionOperator) { case ConditionOperator.IsGreaterThan: value = " > "; break; case ConditionOperator.IsLessThan: value = " < "; break; default: break; } DateTime date = (DateTime)condition.Value1; value = value + "'" + DataUtils.DateToUtc(_loginUser, date).ToString("g", CultureInfo.GetCultureInfo("en-US")) + "'"; } else { DateTime date1 = (DateTime)condition.Value1; DateTime date2; if (condition.ConditionOperator == ConditionOperator.IsEqualTo || condition.ConditionOperator == ConditionOperator.IsNotEqualTo) { date2 = date1.AddDays(1); } else { date2 = (DateTime)condition.Value2; } value = " BETWEEN '" + DataUtils.DateToUtc(_loginUser, date1).ToString("g", CultureInfo.GetCultureInfo("en-US")) + "' AND '" + DataUtils.DateToUtc(_loginUser, date2).ToString("g", CultureInfo.GetCultureInfo("en-US")) + "'"; if (condition.ConditionOperator == ConditionOperator.IsNotInBetween || condition.ConditionOperator == ConditionOperator.IsNotEqualTo) { value = " NOT" + value; } } break; case "int": if (condition.ConditionOperator != ConditionOperator.IsInBetween && condition.ConditionOperator != ConditionOperator.IsNotInBetween) { switch (condition.ConditionOperator) { case ConditionOperator.IsEqualTo: value = " = "; break; case ConditionOperator.IsNotEqualTo: value = " <> "; break; case ConditionOperator.IsGreaterThan: value = " > "; break; case ConditionOperator.IsLessThan: value = " < "; break; default: break; } int i = (int)condition.Value1; value = value + i.ToString(); } else { int i1 = (int)condition.Value1; int i2 = (int)condition.Value2; value = " BETWEEN " + i1.ToString() + " AND " + i2.ToString(); if (condition.ConditionOperator == ConditionOperator.IsNotInBetween) { value = " NOT" + value; } } break; case "float": if (condition.ConditionOperator != ConditionOperator.IsInBetween && condition.ConditionOperator != ConditionOperator.IsNotInBetween) { switch (condition.ConditionOperator) { case ConditionOperator.IsEqualTo: value = " = "; break; case ConditionOperator.IsNotEqualTo: value = " <> "; break; case ConditionOperator.IsGreaterThan: value = " > "; break; case ConditionOperator.IsLessThan: value = " < "; break; default: break; } double d = (double)condition.Value1; value = value + d.ToString(); } else { int i1 = (int)condition.Value1; int i2 = (int)condition.Value2; value = " BETWEEN " + i1.ToString() + " AND " + i2.ToString(); if (condition.ConditionOperator == ConditionOperator.IsNotInBetween) { value = " NOT" + value; } } break; default: switch (condition.ConditionOperator) { case ConditionOperator.IsEqualTo: value = " = '" + condition.Value1.ToString() + "'"; break; case ConditionOperator.IsNotEqualTo: value = " <> '" + condition.Value1.ToString() + "'"; break; case ConditionOperator.StartsWith: value = " LIKE '" + condition.Value1.ToString() + "%'"; break; case ConditionOperator.EndsWith: value = " LIKE '%" + condition.Value1.ToString() + "'"; break; case ConditionOperator.Contains: value = " LIKE '%" + condition.Value1.ToString() + "%'"; break; default: break; } break; } } value = tables.FindByReportTableID(field.ReportTableID).TableName + ".[" + field.FieldName + "]" + value; } else { CustomField field = (CustomField)CustomFields.GetCustomField(_loginUser, condition.FieldID); if (condition.Value1.ToString().Trim() == "") { if (condition.ConditionOperator == ConditionOperator.IsEqualTo) { value = " IS NULL"; } else if (condition.ConditionOperator == ConditionOperator.IsNotEqualTo) { value = " IS NOT NULL"; } } else { switch (field.FieldType) { case CustomFieldType.Text: case CustomFieldType.PickList: switch (condition.ConditionOperator) { case ConditionOperator.IsEqualTo: value = " = '" + condition.Value1.ToString() + "'"; break; case ConditionOperator.IsNotEqualTo: value = " <> '" + condition.Value1.ToString() + "'"; break; case ConditionOperator.StartsWith: value = " LIKE '" + condition.Value1.ToString() + "%'"; break; case ConditionOperator.EndsWith: value = " LIKE '%" + condition.Value1.ToString() + "'"; break; case ConditionOperator.Contains: value = " LIKE '%" + condition.Value1.ToString() + "%'"; break; default: break; } break; case CustomFieldType.Date: case CustomFieldType.Time: case CustomFieldType.DateTime: if (condition.ConditionOperator != ConditionOperator.IsInBetween && condition.ConditionOperator != ConditionOperator.IsNotInBetween) { switch (condition.ConditionOperator) { case ConditionOperator.IsEqualTo: value = " = "; break; case ConditionOperator.IsNotEqualTo: value = " <> "; break; case ConditionOperator.IsGreaterThan: value = " > "; break; case ConditionOperator.IsLessThan: value = " < "; break; default: break; } DateTime date = (DateTime)condition.Value1; value = value + "'" + DataUtils.DateToUtc(_loginUser, date).ToString("g", _loginUser.CultureInfo) + "'"; } else { DateTime date1 = (DateTime)condition.Value1; DateTime date2 = (DateTime)condition.Value2; value = " BETWEEN '" + DataUtils.DateToUtc(_loginUser, date1).ToString("g", _loginUser.CultureInfo) + "' AND '" + DataUtils.DateToUtc(_loginUser, date2).ToString("g", _loginUser.CultureInfo) + "'"; if (condition.ConditionOperator == ConditionOperator.IsNotInBetween) { value = " NOT" + value; } } break; case CustomFieldType.Boolean: if (condition.ConditionOperator == ConditionOperator.IsEqualTo) { value = " = "; } else { value = " <> "; } if ((bool)condition.Value1) { value = value + "'True'"; } else { value = value + "'False'"; } break; case CustomFieldType.Number: if (condition.ConditionOperator != ConditionOperator.IsInBetween && condition.ConditionOperator != ConditionOperator.IsNotInBetween) { switch (condition.ConditionOperator) { case ConditionOperator.IsEqualTo: value = " = "; break; case ConditionOperator.IsNotEqualTo: value = " <> "; break; case ConditionOperator.IsGreaterThan: value = " > "; break; case ConditionOperator.IsLessThan: value = " < "; break; default: break; } int i = (int)condition.Value1; value = value + i.ToString(); } else { int i1 = (int)condition.Value1; int i2 = (int)condition.Value2; value = " BETWEEN " + i1.ToString() + " AND " + i2.ToString(); if (condition.ConditionOperator == ConditionOperator.IsNotInBetween) { value = " NOT" + value; } } break; default: break; } } value = DataUtils.GetCustomFieldColumn(_loginUser, field, DataUtils.GetReportPrimaryKeyFieldName(field.RefType), true, false) + value; } if (builder.Length < 1) { builder.Append("((" + value + ")"); } else { builder.Append(con + "(" + value + ")"); } } if (builder.Length > 0) { builder.Append(")"); } return(builder.ToString().Trim()); }