public SearchConditionInstance GetConditionInstance()
{
var instance = new SearchConditionInstance();
var objConditionInfo = (ModuleFieldInfo)m_Condition.EditValue;
instance.ConditionID = objConditionInfo.FieldID;
instance.Operator = m_Operator.EditValue.ToString();
instance.Value = m_EditValue.EditValue.Encode(objConditionInfo);
if (string.IsNullOrEmpty(objConditionInfo.FunctionSCDValue) != true)
{
using (var ctrlSA = new SAController())
{
List <string> values;
DataContainer container = null;
DataSet dsResult;
values = new List <string>();
values.Add(objConditionInfo.FunctionSCDValue + "('" + instance.Value + "')");
ctrlSA.ExecuteProcedureFillDataset(out container, "sp_get_condition_value", values);
dsResult = container.DataSet;
if (dsResult.Tables.Count == 1)
{
instance.Value = dsResult.Tables[0].Rows[0][0].ToString();
}
}
}
return(instance);
}
public SearchConditionInstance GetConditionInstance()
{
var instance = new SearchConditionInstance {
SQLLogic = m_SQLLogic.EditValue.ToString()
};
var subInstance = new List <SearchConditionInstance>();
foreach (var group in Groups)
{
if (group.InUse)
{
subInstance.Add(group.GetConditionInstance());
}
}
foreach (var condition in Conditions)
{
if (condition.InUse)
{
subInstance.Add(condition.GetConditionInstance());
}
}
instance.SubCondition = subInstance.ToArray();
return(instance);
}
private string BuildSearchExtension(SearchModuleInfo searchInfo, SearchConditionInstance conditionIntance)
{
var fields = FieldUtils.GetModuleFields(searchInfo.ModuleID);
using (var conn = new NpgsqlConnection(ConnectionString))
{
using (var comm = new NpgsqlCommand(searchInfo.WhereExtension, conn))
{
conn.Open();
comm.CommandType = CommandType.StoredProcedure;
PostgresqlHelper.DiscoveryParameters(comm);
foreach (var field in
fields.Where(field => field.WhereExtension == WebCore.CODES.DEFMODFLD.WHEREEXTENSION.YES))
{
comm.Parameters[field.FieldName].Value = DBNull.Value;
foreach (var condition in conditionIntance.SubCondition)
{
if (condition.ConditionID == field.FieldID && string.IsNullOrEmpty(condition.SQLLogic))
{
comm.Parameters[field.FieldName].Value = condition.Operator;
}
}
}
comm.ExecuteNonQuery();
return(comm.Parameters["RETURN_VALUE"].Value.ToString());
}
}
}
public LogicConditionModel(List <WebCore.Entities.CodeInfo> conditions)
{
SearchConditionInstance = new SearchConditionInstance();
Conditions = conditions.ToList();
}
public LogicConditionModel()
{
SearchConditionInstance = new SearchConditionInstance();
Conditions = new List <WebCore.Entities.CodeInfo>();
}
//End TrungTT
public static string BuildSearchCondition(ModuleInfo moduleInfo, ref string whereExtension, OracleCommand comm, SearchConditionInstance conditionIntance)
{
// Parse And/Or Group Condition
if (conditionIntance.SQLLogic != null)
{
var conditions = new List <string>();
foreach (var pCondition in conditionIntance.SubCondition)
{
var strCondition = BuildSearchCondition(moduleInfo, ref whereExtension, comm, pCondition);
if (!string.IsNullOrEmpty(strCondition))
{
conditions.Add(strCondition);
}
}
if (conditions.Count == 0)
{
return(string.Empty);
}
switch (conditionIntance.SQLLogic)
{
case CODES.SQL_EXPRESSION.SQL_LOGIC.OR:
return("(" + string.Join(" OR ", conditions.ToArray()) + ")");
default:
return("(" + string.Join(" AND ", conditions.ToArray()) + ")");
}
}
var condition = FieldUtils.GetModuleFieldByID(moduleInfo.ModuleID, conditionIntance.ConditionID);
string conditionFormat;
switch (conditionIntance.Operator)
{
case CODES.DEFMODFLD.CONDITION_OPERATOR.LIKE:
conditionFormat = "{0} LIKE '%' || {1} || '%'";
break;
case CODES.DEFMODFLD.CONDITION_OPERATOR.NOTLIKE:
conditionFormat = "{0} NOT LIKE '%' || {1} || '%'";
break;
case CODES.DEFMODFLD.CONDITION_OPERATOR.NOTEQUAL:
conditionFormat = "{0} <> {1}";
break;
case CODES.DEFMODFLD.CONDITION_OPERATOR.BEGINWITH:
conditionFormat = "{0} LIKE {1} || '%'";
break;
case CODES.DEFMODFLD.CONDITION_OPERATOR.ENDWITH:
conditionFormat = "{0} LIKE '%' || {1}";
break;
case CODES.DEFMODFLD.CONDITION_OPERATOR.LARGER:
conditionFormat = "{0} > {1}";
break;
case CODES.DEFMODFLD.CONDITION_OPERATOR.LARGEROREQUAL:
conditionFormat = "{0} >= {1}";
break;
case CODES.DEFMODFLD.CONDITION_OPERATOR.SMALLER:
conditionFormat = "{0} < {1}";
break;
case CODES.DEFMODFLD.CONDITION_OPERATOR.SMALLEROREQUAL:
conditionFormat = "{0} <= {1}";
break;
case CODES.DEFMODFLD.CONDITION_OPERATOR.INARRAY:
conditionFormat = "{0} IN ({1})";
break;
case CODES.DEFMODFLD.CONDITION_OPERATOR.NOTINARRAY:
conditionFormat = "{0} NOT IN ({1})";
break;
default:
conditionFormat = "{0} = {1}";
break;
}
// Build Left Operator
var leftOperator = condition.ParameterName;
switch (condition.TextCase)
{
case CODES.DEFMODFLD.TEXTCASE.UPPER:
leftOperator = string.Format("UPPER({0})", condition.ParameterName);
break;
case CODES.DEFMODFLD.TEXTCASE.LOWER:
leftOperator = string.Format("LOWER({0})", condition.ParameterName);
break;
}
string rightOperator;
if (string.IsNullOrEmpty(conditionIntance.Value))
{
var parameterName = ":" + (comm.Parameters.Count + 1);
comm.Parameters.Add(parameterName, DBNull.Value);
rightOperator = parameterName;
}
else if (
conditionIntance.Operator == CODES.DEFMODFLD.CONDITION_OPERATOR.INARRAY ||
conditionIntance.Operator == CODES.DEFMODFLD.CONDITION_OPERATOR.NOTINARRAY)
{
var values = conditionIntance.Value.Split(new[] { "," }, StringSplitOptions.None);
var parameterNames = new string[values.Length];
for (var i = 0; i < values.Length; i++)
{
var paramName = ":" + (comm.Parameters.Count + 1);
parameterNames[i] = paramName;
comm.Parameters.Add(paramName, values[i].Decode(condition));
}
rightOperator = string.Join(",", parameterNames);
}
else
{
var paramName = ":" + (comm.Parameters.Count + 1);
switch (condition.TextCase)
{
case CODES.DEFMODFLD.TEXTCASE.UPPER:
comm.Parameters.Add(paramName, conditionIntance.Value.ToUpper());
break;
case CODES.DEFMODFLD.TEXTCASE.LOWER:
comm.Parameters.Add(paramName, conditionIntance.Value.ToLower());
break;
default:
comm.Parameters.Add(paramName, conditionIntance.Value.Decode(condition));
break;
}
rightOperator = paramName;
}
if (!string.IsNullOrEmpty(condition.WhereExtension))
{
whereExtension = whereExtension.Replace("{" + condition.ParameterName + ":RIGHT}", string.Format(conditionFormat, "", rightOperator));
whereExtension = whereExtension.Replace("{" + condition.ParameterName + ":VALUE}", rightOperator);
}
if (!string.IsNullOrEmpty(condition.CustomSearchCondition))
{
var customSearchInstance = condition.CustomSearchCondition;
customSearchInstance = customSearchInstance.Replace("{" + condition.ParameterName + ":RIGHT}", string.Format(conditionFormat, "", rightOperator));
customSearchInstance = customSearchInstance.Replace("{" + condition.ParameterName + ":VALUE}", rightOperator);
return(customSearchInstance);
}
return(string.Format(conditionFormat, leftOperator, rightOperator));
}
public static string BuildSearchConditionKey(ModuleInfo moduleInfo, SearchConditionInstance conditionIntance)
{
if (conditionIntance.SQLLogic != null)
{
var conditions = new List <string>();
foreach (var pCondition in conditionIntance.SubCondition)
{
var strCondition = BuildSearchConditionKey(moduleInfo, pCondition);
if (!string.IsNullOrEmpty(strCondition))
{
conditions.Add(strCondition);
}
}
if (conditions.Count == 0)
{
return(string.Empty);
}
switch (conditionIntance.SQLLogic)
{
case CODES.SQL_EXPRESSION.SQL_LOGIC.OR:
return("(" + string.Join(" OR ", conditions.ToArray()) + ")");
default:
return("(" + string.Join(" AND ", conditions.ToArray()) + ")");
}
}
var condition = FieldUtils.GetModuleFieldByID(moduleInfo.ModuleID, conditionIntance.ConditionID);
string conditionFormat;
switch (conditionIntance.Operator)
{
case CODES.DEFMODFLD.CONDITION_OPERATOR.LIKE:
conditionFormat = "{0} LIKE '%{1}%'";
break;
case CODES.DEFMODFLD.CONDITION_OPERATOR.NOTLIKE:
conditionFormat = "{0} NOT LIKE '%{1}%'";
break;
case CODES.DEFMODFLD.CONDITION_OPERATOR.NOTEQUAL:
conditionFormat = "{0} <> {1}";
break;
case CODES.DEFMODFLD.CONDITION_OPERATOR.BEGINWITH:
conditionFormat = "{0} LIKE '{1}%'";
break;
case CODES.DEFMODFLD.CONDITION_OPERATOR.ENDWITH:
conditionFormat = "{0} LIKE '%{1}'";
break;
case CODES.DEFMODFLD.CONDITION_OPERATOR.LARGER:
conditionFormat = "{0} > {1}";
break;
case CODES.DEFMODFLD.CONDITION_OPERATOR.LARGEROREQUAL:
conditionFormat = "{0} >= {1}";
break;
case CODES.DEFMODFLD.CONDITION_OPERATOR.SMALLER:
conditionFormat = "{0} < {1}";
break;
case CODES.DEFMODFLD.CONDITION_OPERATOR.SMALLEROREQUAL:
conditionFormat = "{0} <= {1}";
break;
case CODES.DEFMODFLD.CONDITION_OPERATOR.INARRAY:
conditionFormat = "{0} IN ({1})";
break;
case CODES.DEFMODFLD.CONDITION_OPERATOR.NOTINARRAY:
conditionFormat = "{0} NOT IN ({1})";
break;
default:
conditionFormat = "{0}={1}";
break;
}
string fieldName;
//if (App.Environment.EnvironmentType == EnvironmentType.CLIENT_APPLICATION)
// fieldName = string.Format("[{0}]",
// LangUtils.Translate(
// LangType.LABEL_FIELD,
// moduleInfo.ModuleName,
// condition.FieldName));
//else
fieldName = condition.ParameterName;
if (conditionIntance.Value == null)
{
return(string.Format(conditionFormat, fieldName, null));
}
return(string.Format(conditionFormat, fieldName, conditionIntance.Value));
}
private void DiscoveryParametersForSearch(NpgsqlCommand command, SearchModuleInfo searchInfo, string queryFormat, SearchConditionInstance conditionIntance, List <SearchConditionInstance> staticConditionInstances)
{
//command.BindByName = true;
var whereExtension = "1 = 1";
if (!string.IsNullOrEmpty(searchInfo.WhereExtension))
{
whereExtension = BuildSearchExtension(searchInfo, conditionIntance);
}
BuildStaticConditions(searchInfo, command, staticConditionInstances);
var whereCondition = "";// ModuleUtils.BuildSearchCondition(searchInfo, ref whereExtension, command, conditionIntance);
if (string.IsNullOrEmpty(whereCondition))
{
whereCondition = "1 = 1";
}
command.CommandText = string.Format(queryFormat, whereCondition, whereExtension);
if (searchInfo.ModuleID == STATICMODULE.UPFILE_MODID)
{
//command.CommandText = queryFormat + " and sessionskey = '" + Session.SessionKey + "'";
}
}
public void GetSearchStatistic(out DataContainer searchStatistic, string moduleID, string subModule, SearchConditionInstance conditionIntance, List <SearchConditionInstance> staticConditionInstances)
{
searchStatistic = m_Client.GetSearchStatistic(moduleID, subModule, conditionIntance, staticConditionInstances);
}
public void ExecuteSearch(out string searchResultKey, out DateTime searchTime, string moduleID, string subModule, SearchConditionInstance conditionIntance, List <SearchConditionInstance> staticConditionInstances)
{
searchResultKey = m_Client.ExecuteSearch(out searchTime, moduleID, subModule, conditionIntance, staticConditionInstances);
}
