private List <HRM.Infrastructure.Utilities.FilterAttribute> ExtractFilterAttributes(DataSourceRequest request)
{
List <HRM.Infrastructure.Utilities.FilterAttribute> list = new List <HRM.Infrastructure.Utilities.FilterAttribute>();
foreach (Kendo.Mvc.FilterDescriptor filter in request.Filters)
{
HRM.Infrastructure.Utilities.FilterAttribute attribute = new HRM.Infrastructure.Utilities.FilterAttribute()
{
Member = filter.Member,
MemberType = filter.MemberType,
};
switch (filter.Operator)
{
case Kendo.Mvc.FilterOperator.Contains:
attribute.Operator = FILTEROPERATOR.Contains;
break;
case Kendo.Mvc.FilterOperator.StartsWith:
attribute.Operator = FILTEROPERATOR.StartWith;
break;
case Kendo.Mvc.FilterOperator.EndsWith:
attribute.Operator = FILTEROPERATOR.EndWith;
break;
}
list.Add(attribute);
}
return(list);
}
/// <summary>
/// Danh sách các điều kiện do người dùng truyền vào
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public List <HRM.Infrastructure.Utilities.FilterAttribute> ExtractAdvanceFilterAttributes(object model)
{
List <HRM.Infrastructure.Utilities.FilterAttribute> list = new List <HRM.Infrastructure.Utilities.FilterAttribute>();
if (model == null)
{
return(list);
}
PropertyInfo[] propertyInfos = model.GetType().GetProperties();
List <PropertyInfo> lstPropertyInfo = propertyInfos.ToList();
foreach (PropertyInfo _profertyInfo in lstPropertyInfo)
{
HRM.Infrastructure.Utilities.FilterAttribute attribute = new HRM.Infrastructure.Utilities.FilterAttribute()
{
Member = _profertyInfo.Name,
MemberType = _profertyInfo.PropertyType,
Value2 = model.GetPropertyValue(_profertyInfo.Name)
};
if (_profertyInfo.PropertyType.Name == "List`1")
{
attribute.MemberType = typeof(object);
var lstObj = (model.GetPropertyValue(_profertyInfo.Name) as IList);
object result = null;
if (lstObj != null)
{
result = string.Join(",", lstObj.OfType <object>().Select(x => x.ToString()).ToArray());
}
attribute.Value2 = result;
}
else if (_profertyInfo.PropertyType == typeof(DateTime))
{
attribute.MemberType = typeof(DateTime);
if (attribute.Value2 != null && attribute.Value2.ToString() == DateTime.MinValue.ToString())
{
attribute.Value2 = null;
}
}
//#region [Hien.Nguyen] xử lý tự động convert Guid orcl
//Guid _tmp = new Guid();
//if (attribute.Value2 != null && Guid.TryParse(attribute.Value2.ToString(), out _tmp) == true)
//{
// attribute.Value2 = Common.DotNetToOracle(attribute.Value2.ToString());
//}
//#endregion
list.Add(attribute);
}
return(list);
}
/// <summary>
/// Danh sách các điều kiện do người dùng truyền vào
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public List <HRM.Infrastructure.Utilities.FilterAttribute> ExtractAdvanceFilterAttributes(object model)
{
List <HRM.Infrastructure.Utilities.FilterAttribute> list = new List <HRM.Infrastructure.Utilities.FilterAttribute>();
if (model == null)
{
return(list);
}
PropertyInfo[] propertyInfos = model.GetType().GetProperties();
List <PropertyInfo> lstPropertyInfo = propertyInfos.ToList();
foreach (PropertyInfo _profertyInfo in lstPropertyInfo)
{
HRM.Infrastructure.Utilities.FilterAttribute attribute = new HRM.Infrastructure.Utilities.FilterAttribute()
{
Member = _profertyInfo.Name,
MemberType = _profertyInfo.PropertyType,
Value2 = model.GetPropertyValue(_profertyInfo.Name)
};
if (_profertyInfo.PropertyType.Name == "List`1")
{
attribute.MemberType = typeof(object);
var lstObj = (model.GetPropertyValue(_profertyInfo.Name) as IList);
object result = null;
if (lstObj != null)
{
result = string.Join(",", lstObj.OfType <object>().Select(x => x.ToString()).ToArray());
}
attribute.Value2 = result;
}
else if (_profertyInfo.PropertyType == typeof(DateTime))
{
attribute.MemberType = typeof(DateTime);
if (attribute.Value2 != null && attribute.Value2.ToString() == DateTime.MinValue.ToString())
{
attribute.Value2 = null;
}
}
list.Add(attribute);
}
return(list);
}
private List<HRM.Infrastructure.Utilities.FilterAttribute> ExtractFilterAttributes(DataSourceRequest request)
{
List<HRM.Infrastructure.Utilities.FilterAttribute> list = new List<HRM.Infrastructure.Utilities.FilterAttribute>();
if (request.Filters == null)
return list;
foreach (Kendo.Mvc.FilterDescriptor filter in request.Filters)
{
HRM.Infrastructure.Utilities.FilterAttribute attribute = new HRM.Infrastructure.Utilities.FilterAttribute()
{
Member = filter.Member,
MemberType = filter.MemberType,
};
switch (filter.Operator)
{
case Kendo.Mvc.FilterOperator.IsEqualTo:
attribute.Operator = FILTEROPERATOR.Equals;
break;
case Kendo.Mvc.FilterOperator.Contains:
attribute.Operator = FILTEROPERATOR.Contains;
break;
case Kendo.Mvc.FilterOperator.StartsWith:
attribute.Operator = FILTEROPERATOR.StartWith;
break;
case Kendo.Mvc.FilterOperator.EndsWith:
attribute.Operator = FILTEROPERATOR.EndWith;
break;
}
list.Add(attribute);
}
return list;
}
/// <summary>
/// [Hien.Pham] - Hàm mở rộng chuyển các tham số điều kiện tìm kiếm thành đối tượng param
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
private List<HRM.Infrastructure.Utilities.FilterAttribute> ExtractAdvanceFilterAttributes(object model)
{
List<HRM.Infrastructure.Utilities.FilterAttribute> list = new List<HRM.Infrastructure.Utilities.FilterAttribute>();
if (model == null)
return list;
PropertyInfo[] propertyInfos = model.GetType().GetProperties();
List<PropertyInfo> lstPropertyInfo = propertyInfos.ToList();
foreach (PropertyInfo _profertyInfo in lstPropertyInfo)
{
HRM.Infrastructure.Utilities.FilterAttribute attribute = new HRM.Infrastructure.Utilities.FilterAttribute()
{
Member = _profertyInfo.Name,
MemberType = _profertyInfo.PropertyType,
Value2 = model.GetPropertyValue(_profertyInfo.Name)
};
if (_profertyInfo.PropertyType.Name == "List`1")
{
attribute.MemberType = typeof(object);
var lstObj = (model.GetPropertyValue(_profertyInfo.Name) as IList);
object result = null;
if (lstObj != null)
result = string.Join(",", lstObj.OfType<object>().Select(x => x.ToString()).ToArray());
attribute.Value2 = result;
}
else if (_profertyInfo.PropertyType == typeof(DateTime))
{
attribute.MemberType = typeof(DateTime);
if (attribute.Value2 != null && attribute.Value2.ToString() == DateTime.MinValue.ToString())
{
attribute.Value2 = null;
}
}
list.Add(attribute);
}
return list;
}
/// <summary>
/// Tạo dữ liệu lọc
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public List <HRM.Infrastructure.Utilities.FilterAttribute> ExtractFilterAttributes(DataSourceRequest request)
{
List <HRM.Infrastructure.Utilities.FilterAttribute> list = new List <HRM.Infrastructure.Utilities.FilterAttribute>();
if (request.Filters == null || request.Filters.Count == 0)
{
return(list);
}
if (request.Filters.FirstOrDefault().GetType() == typeof(Kendo.Mvc.FilterDescriptor))
{
foreach (Kendo.Mvc.FilterDescriptor filter in request.Filters)
{
HRM.Infrastructure.Utilities.FilterAttribute attribute = new HRM.Infrastructure.Utilities.FilterAttribute()
{
Member = filter.Member,
MemberType = filter.MemberType,
Value = filter.Value.ToString(),
};
switch (filter.Operator)
{
case Kendo.Mvc.FilterOperator.IsEqualTo:
attribute.Operator = FILTEROPERATOR.Equals;
break;
case Kendo.Mvc.FilterOperator.Contains:
attribute.Operator = FILTEROPERATOR.Contains;
break;
case Kendo.Mvc.FilterOperator.StartsWith:
attribute.Operator = FILTEROPERATOR.StartWith;
break;
case Kendo.Mvc.FilterOperator.EndsWith:
attribute.Operator = FILTEROPERATOR.EndWith;
break;
}
list.Add(attribute);
}
}
else
{
foreach (Kendo.Mvc.CompositeFilterDescriptor item in request.Filters)
{
foreach (Kendo.Mvc.FilterDescriptor filter in item.FilterDescriptors)
{
HRM.Infrastructure.Utilities.FilterAttribute attribute = new HRM.Infrastructure.Utilities.FilterAttribute()
{
Member = filter.Member,
MemberType = filter.MemberType,
};
switch (filter.Operator)
{
case Kendo.Mvc.FilterOperator.IsEqualTo:
attribute.Operator = FILTEROPERATOR.Equals;
break;
case Kendo.Mvc.FilterOperator.Contains:
attribute.Operator = FILTEROPERATOR.Contains;
break;
case Kendo.Mvc.FilterOperator.StartsWith:
attribute.Operator = FILTEROPERATOR.StartWith;
break;
case Kendo.Mvc.FilterOperator.EndsWith:
attribute.Operator = FILTEROPERATOR.EndWith;
break;
}
list.Add(attribute);
}
}
}
return(list);
}
public static ParamaterModle ParseParamOracle(ListQueryModel model, string storeName)
{
string strQuery = "begin " + storeName + "(";
List <OracleParameter> lstParas = new List <OracleParameter>();
OracleParameter para = null;
if (model.AdvanceFilters != null)
{
var selectExport = model.AdvanceFilters.Where(w => w.Member == "IsExport").FirstOrDefault();
if (selectExport != null)
{
if (selectExport.Value2 != null)
{
bool isExport = (bool)selectExport.Value2;
if (isExport)
{
model.PageSize = int.MaxValue;
}
}
}
foreach (var item in model.AdvanceFilters)
{
if (item.Member != null && (item.Member.ToLower() == "IsExport".ToLower() ||
item.Member == "ValueFields" || item.Member == "LoginUserID"))
{
continue;
}
Type paramType = item.MemberType;
strQuery += ":" + item.Member + ", ";
para = new OracleParameter();
para.ParameterName = item.Member;
FilterAttribute filterAtt = model.Filters.Where(m => m.Member == item.Member).FirstOrDefault();
object paramValue = item.Value2;
if (paramValue == null && filterAtt != null && filterAtt.Value != null)
{
paramValue = filterAtt.Value;
}
para.Value = paramValue;
if (paramType == typeof(Int32?) || paramType == typeof(Int32))
{
para.DbType = System.Data.DbType.Int32;
para.OracleDbType = OracleDbType.Number;
}
//Tìm kiếm dạng Unicode, có dấu thay cho ký tự N trong Sql
if ((item.MemberType == typeof(string) && item.Member != "OrgStructureID") && paramValue != null)
{
//System.Text.UnicodeEncoding unicode = new System.Text.UnicodeEncoding();
para.DbType = System.Data.DbType.String;
para.OracleDbType = OracleDbType.NVarChar;
para.Value = paramValue;
}
//else
//{
// if (item.MemberType == typeof(Int32?))
// {
// para.DbType = System.Data.DbType.Int32;
// }
// else if (item.MemberType == typeof(Double?))
// {
// para.DbType = System.Data.DbType.Double;
// }
//}
if (paramValue == null || paramValue.ToString() == Guid.Empty.ToString())
{
para.Value = DBNull.Value;
}
lstParas.Add(para);
}
strQuery += ":PageIndex";
para = new OracleParameter();
para.ParameterName = "PageIndex";
para.Value = model.PageIndex == 0 ? 1 : model.PageIndex;
para.DbType = System.Data.DbType.Int32;
para.OracleDbType = OracleDbType.Number;
lstParas.Add(para);
strQuery += ",:PageSize";
para = new OracleParameter();
para.ParameterName = "PageSize";
para.Value = model.PageSize == 0 ? 50 : model.PageSize;
para.DbType = System.Data.DbType.Int32;
lstParas.Add(para);
if (CheckStoreNamePermisson(storeName))
{
strQuery += ",:UserName";
para = new OracleParameter();
para.ParameterName = "UserName";
para.Value = model.UserLogin;
para.DbType = System.Data.DbType.String;
para.OracleDbType = OracleDbType.NVarChar;
lstParas.Add(para);
}
}
para = new OracleParameter();
para.ParameterName = "R_Output";
para.OracleDbType = OracleDbType.Cursor;
para.Direction = System.Data.ParameterDirection.Output;
lstParas.Add(para);
ParamaterModle param = new ParamaterModle();
param.Params = lstParas.ToArray();
param.SqlQuery = strQuery + ",:R_Output); end;";
return(param);
}
public static ParamaterModle ParseParamSQL(ListQueryModel model, string storeName)
{
string strQuery = storeName + " ";
List <SqlParameter> lstParas = new List <SqlParameter>();
SqlParameter para = null;
var selectExport = model.AdvanceFilters.Where(w => w.Member != null && w.Member.ToLower() == "IsExport".ToLower()).FirstOrDefault();
if (selectExport != null)
{
if (selectExport.Value2 != null)
{
bool isExport = (bool)selectExport.Value2;
if (isExport)
{
model.PageSize = 100000;
}
}
}
foreach (var item in model.AdvanceFilters)
{
if (item.Member != null && (item.Member.ToLower() == "IsExport".ToLower() ||
item.Member == "ValueFields" || item.Member == "LoginUserID"))
{
continue;
}
Type paramType = item.MemberType;
strQuery += "@" + item.Member + ", ";
para = new SqlParameter();
para.ParameterName = item.Member;
FilterAttribute filterAtt = model.Filters.Where(m => m.Member == item.Member).FirstOrDefault();
object paramValue = item.Value2;
if (paramValue == null && filterAtt != null && filterAtt.Value != null)
{
paramValue = filterAtt.Value;
}
paramValue = para.Value = item.Value2;
if (paramType == typeof(Int32?) || paramType == typeof(Int32))
{
para.SqlDbType = System.Data.SqlDbType.Int;
}
//Tìm kiếm dạng Unicode, có dấu thay cho ký tự N trong Sql
System.Text.RegularExpressions.Regex isGuid = new System.Text.RegularExpressions.Regex(@"^(\{){0,1}[0-9a-fA-F]{8}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{12}(\}){0,1}$", System.Text.RegularExpressions.RegexOptions.Compiled);
bool _isMatch = isGuid.IsMatch(item.Value2 == null ? string.Empty : item.Value2.ToString());
if ((item.MemberType == typeof(string) && !_isMatch && item.Member != "OrgStructureID") && paramValue != null)
{
System.Text.UnicodeEncoding unicode = new System.Text.UnicodeEncoding();
para.Value = unicode.GetBytes(item.Value2.ToString());
}
else
if (item.MemberType == typeof(Int32?))
{
para.DbType = System.Data.DbType.Int32;
}
else if (item.MemberType == typeof(Double?))
{
para.DbType = System.Data.DbType.Double;
}
else if (item.MemberType == typeof(DateTime?) || item.MemberType == typeof(DateTime))
{
para.DbType = System.Data.DbType.DateTime;
}
if (paramValue == null)
{
para.Value = DBNull.Value;
}
lstParas.Add(para);
}
strQuery += "@PageIndex";
para = new SqlParameter();
para.ParameterName = "PageIndex";
para.Value = model.PageIndex == 0 ? 1 : model.PageIndex;
para.DbType = System.Data.DbType.Int32;
lstParas.Add(para);
if (model.PageSize > 0)
{
strQuery += ",@PageSize";
para = new SqlParameter();
para.ParameterName = "PageSize";
para.Value = model.PageSize;
para.DbType = System.Data.DbType.Int32;
lstParas.Add(para);
}
else
{
//if (strQuery.LastIndexOf(',') > 0)
//{
// strQuery = strQuery.Substring(0, strQuery.Length - 1);
//}
}
ParamaterModle param = new ParamaterModle();
param.Params = lstParas.ToArray();
param.SqlQuery = strQuery;
return(param);
}