public List <Partner> GetPartners(PartnerQuery param)
{
var WhereClause = string.Empty;
var parameters = BuildCriteria(param, ref WhereClause);
var dataTable = this.db.GetData("Select * from partner " + WhereClause + " order by partner_name ", parameters);
if (dataTable == null)
{
return(null);
}
if (dataTable.Rows.Count == 0)
{
return(null);
}
var partners = new List <Partner>();
foreach (DataRow row in dataTable.Rows)
{
var obj = ConvertDataRowToPartner(row);
partners.Add(obj);
}
return(partners);
}
public List <Partner> GetPartnersWithPaging(PartnerQuery param)
{
var WhereClause = string.Empty;
var parameters = BuildCriteria(param, ref WhereClause);
var strSqlStatment = new StringBuilder();
strSqlStatment.Append("Select * from ( ");
strSqlStatment.Append("select rownum as seq , main_data.* from ( ");
strSqlStatment.Append("Select * from partner " + WhereClause + " order by partner_name ");
strSqlStatment.Append(") main_data ) ");
strSqlStatment.Append($"WHERE seq >= ({param.Paging.PageNo - 1}) * {param.Paging.PageSize} AND ROWNUM <= {param.Paging.PageSize}");
var dataTable = this.db.GetData(strSqlStatment.ToString(), parameters);
if (dataTable == null)
{
return(null);
}
if (dataTable.Rows.Count == 0)
{
return(null);
}
var partners = new List <Partner>();
foreach (DataRow row in dataTable.Rows)
{
var obj = ConvertDataRowToPartner(row);
partners.Add(obj);
}
return(partners);
}
public int GetCount(PartnerQuery param)
{
string WhereClause = string.Empty;
var parameters = BuildCriteria(param, ref WhereClause);
var strSqlStatment = new StringBuilder();
strSqlStatment.Append($"Select count(*) val from partner { WhereClause }");
var count = this.db.GetIntScalarValue(strSqlStatment.ToString(), parameters);
return(count);
}
private List <OracleParameter> BuildCriteria(PartnerQuery param, ref string criteria)
{
var WhereClause = new StringBuilder();
var parameters = new List <OracleParameter>();
var permission = partnerActivity.GetPartAct("Partner.Query", param.QCreatorRoleId);
if (permission == null || permission.Scope.Id == "CurOpOnly")
{
var parm = new OracleParameter {
ParameterName = "QPartnerId", OracleDbType = OracleDbType.Varchar2, Value = param.QCreatorId
};
WhereClause.Append(" WHERE partner_id=:QPartnerId ");
parameters.Add(parm);
return(parameters);
}
if (!string.IsNullOrEmpty(param.QPartnerId))
{
var parm = new OracleParameter {
ParameterName = "QPartnerId", OracleDbType = OracleDbType.Varchar2, Value = param.QPartnerId
};
WhereClause.Append(" WHERE partner_id=:QPartnerId ");
parameters.Add(parm);
}
if (param.QAccount > 0)
{
WhereClause.Append(string.IsNullOrEmpty(WhereClause.ToString()) ? " WHERE partner_acc=:QAccount " : " AND partner_acc=:QAccount ");
var parm = new OracleParameter {
ParameterName = "QAccount", OracleDbType = OracleDbType.Int32, Value = param.QAccount
};
parameters.Add(parm);
}
if (!string.IsNullOrEmpty(param.QRefPartnerId))
{
WhereClause.Append(string.IsNullOrEmpty(WhereClause.ToString()) ? " WHERE ref_partner=:QRefPartnerId " : " AND ref_partner=:QRefPartnerId ");
var parm = new OracleParameter {
ParameterName = "QRefPartnerId", OracleDbType = OracleDbType.Varchar2, Value = param.QRefPartnerId
};
parameters.Add(parm);
}
if (param.QRoleId > 0)
{
WhereClause.Append(string.IsNullOrEmpty(WhereClause.ToString()) ? " WHERE roleid=:QRoleId " : " AND roleid=:QRoleId ");
var parm = new OracleParameter {
ParameterName = "QRoleId", OracleDbType = OracleDbType.Int32, Value = param.QRoleId
};
parameters.Add(parm);
}
if (param.QStatusId > 0)
{
WhereClause.Append(string.IsNullOrEmpty(WhereClause.ToString()) ? " WHERE status=:QStatusId " : " AND status=:QStatusId ");
var parm = new OracleParameter {
ParameterName = "QStatusId", OracleDbType = OracleDbType.Int32, Value = param.QStatusId
};
parameters.Add(parm);
}
if (!string.IsNullOrEmpty(param.QPartnerName))
{
//param.QPartnerName = Utility.RemoveSpecialChar(param.QPartnerName);
WhereClause.Append(string.IsNullOrEmpty(WhereClause.ToString()) ? " WHERE (partner_name LIKE '%' || :QPartnerName || '%') "
: " AND (partner_name LIKE '%' || :QPartnerName || '%') ");
var parm = new OracleParameter {
ParameterName = "QPartnerName", OracleDbType = OracleDbType.NVarchar2, Value = param.QPartnerName
};
parameters.Add(parm);
}
if (permission.Scope.Id == "Exclusive")
{
var parm = new OracleParameter {
ParameterName = "RefId", OracleDbType = OracleDbType.Varchar2, Value = param.QCreatorId
};
WhereClause.Append(" WHERE ref_partner=:RefId ");
parameters.Add(parm);
}
if (permission.Details == null || permission.Details.Count == 0)
{
WhereClause.Append(string.IsNullOrEmpty(WhereClause.ToString()) ? " WHERE roleid=-1 " : " AND roleid=-1 ");
}
else
{
var allowedRoles = string.Join(",", permission.Details.Select(x => x.ToRole.Id).ToList());
WhereClause.Append(string.IsNullOrEmpty(WhereClause.ToString())
? $" WHERE roleid in ( {allowedRoles} )"
: $" AND roleid in ( {allowedRoles} )");
}
criteria = WhereClause.ToString();
return(parameters);
}
/// <summary>
/// 获得列表数据
/// </summary>
private DataSet GetData()
{
DataSet ds = new PartnerQuery().GetPartnerInfo(this.Name, this.Status, viewpage1.CurrentPageIndex, viewpage1.PageSize);
return(ds);
}