public RechargeQuery QueryWithPaging(RechargeQuery param)
{
var WhereClause = string.Empty;
var parameters = BuildParameters(param, ref WhereClause);
var strSqlStatment = new StringBuilder();
strSqlStatment.Append("Select * from ( ");
strSqlStatment.Append("select rownum as seq , main_data.* from ( ");
strSqlStatment.Append("Select * from v_collection t " + WhereClause + " order by createdon desc ");
strSqlStatment.Append(") main_data ) ");
strSqlStatment.Append($"WHERE seq > ({param.Paging.PageNo - 1}) * {param.Paging.PageSize} AND ROWNUM <= {param.Paging.PageSize}");
var masterDataTable = this.db.GetData(strSqlStatment.ToString(), parameters);
if (masterDataTable == null)
{
return(param);
}
if (masterDataTable.Rows.Count == 0)
{
return(param);
}
var results = new List <RechargeQueryResult>();
foreach (DataRow row in masterDataTable.Rows)
{
var obj = ConvertDataRowToRechargeQueryResult(row);
results.Add(obj);
}
param.Results = results;
return(param);
}
public RechargeQuery Query(RechargeQuery param)
{
var WhereClause = string.Empty;
var parameters = BuildParameters(param, ref WhereClause);
var masterDataTable = this.db.GetData("Select * from v_collection t " + WhereClause + " order by createdon desc", parameters);
if (masterDataTable == null)
{
return(param);
}
if (masterDataTable.Rows.Count == 0)
{
return(param);
}
var results = new List <RechargeQueryResult>();
foreach (DataRow row in masterDataTable.Rows)
{
var obj = ConvertDataRowToRechargeQueryResult(row);
results.Add(obj);
}
param.Results = results;
return(param);
}
public int GetCount(RechargeQuery param)
{
string WhereClause = string.Empty;
var parameters = BuildParameters(param, ref WhereClause);
var strSqlStatment = new StringBuilder();
strSqlStatment.Append($"Select count(*) val from v_collection { WhereClause }");
var count = this.db.GetIntScalarValue(strSqlStatment.ToString(), parameters);
return(count);
}
public IActionResult Index()
{
var currentRoleId = partner.GetCurrentUserRole(this.HttpContext);
var permission = partnerActivity.GetPartAct("Recharge.Query", currentRoleId);
if (permission == null)
{
return(Redirect(Request.Headers["Referer"].ToString()));
}
var model = new RechargeQuery();
model.Statuses = new CommonCodeRepo(db).GetCodesByType("Collection.Status");
model.AccessChannel = new CommonCodeRepo(db).GetCodesByType("access.channel");
model.QFromDate = DateTime.Today.AddMonths(-1);
model.QToDate = DateTime.Today;
model.Paging.PageNo = 1;
model.Paging.PageSize = 50;
model.Paging.Count = 0;
return(View(model));
}
private List <OracleParameter> BuildParameters(RechargeQuery param, ref string criteria)
{
var WhereClause = new StringBuilder();
var parameters = new List <OracleParameter>();
if (param.QPosAccount > 0)
{
var parm = new OracleParameter {
ParameterName = "QPosAccount", OracleDbType = OracleDbType.Int32, Value = param.QPosAccount
};
WhereClause.Append(" WHERE pos_acc=:QPosAccount ");
parameters.Add(parm);
}
if (!string.IsNullOrEmpty(param.QPosId))
{
var parm = new OracleParameter {
ParameterName = "QPosId", OracleDbType = OracleDbType.Varchar2, Value = param.QPosId
};
WhereClause.Append(string.IsNullOrEmpty(WhereClause.ToString()) ? " WHERE pos_id=:QPosId " : " AND pos_id=:QPosId ");
parameters.Add(parm);
}
if (!string.IsNullOrEmpty(param.QSubsId))
{
var parm = new OracleParameter {
ParameterName = "QSubsId", OracleDbType = OracleDbType.Varchar2, Value = param.QSubsId
};
WhereClause.Append(string.IsNullOrEmpty(WhereClause.ToString()) ? " WHERE subs_no=:QSubsId " : " AND subs_no=:QSubsId ");
parameters.Add(parm);
}
if (param.StatusId >= 0)
{
var parm = new OracleParameter {
ParameterName = "StatusId", OracleDbType = OracleDbType.Int32, Value = param.StatusId
};
WhereClause.Append(string.IsNullOrEmpty(WhereClause.ToString()) ? " WHERE status=:StatusId " : " AND status=:StatusId ");
parameters.Add(parm);
}
if (!string.IsNullOrEmpty(param.AccessChannelId) && param.AccessChannelId != "-1")
{
var parm = new OracleParameter {
ParameterName = "AccessChannelId", OracleDbType = OracleDbType.Varchar2, Value = param.AccessChannelId
};
WhereClause.Append(string.IsNullOrEmpty(WhereClause.ToString()) ? " WHERE access_channel=:AccessChannelId " : " AND access_channel=:AccessChannelId ");
parameters.Add(parm);
}
if (param.QFromDate > DateTime.MinValue && param.QFromDate != null)
{
WhereClause.Append(string.IsNullOrEmpty(WhereClause.ToString()) ? " WHERE trunc(createdon)>=:StartDate " : " AND trunc(createdon)>=:StartDate ");
var parm = new OracleParameter {
ParameterName = "StartDate", OracleDbType = OracleDbType.Date, Value = param.QFromDate
};
parameters.Add(parm);
}
if (param.QToDate > DateTime.MinValue && param.QToDate != null)
{
WhereClause.Append(string.IsNullOrEmpty(WhereClause.ToString()) ? " WHERE trunc(createdon)<=:EndDate " : " AND trunc(createdon)<=:EndDate ");
var parm = new OracleParameter {
ParameterName = "EndDate", OracleDbType = OracleDbType.Date, Value = param.QToDate
};
parameters.Add(parm);
}
if (param.QueryScope == "CurOpOnly")
{
WhereClause.Append(string.IsNullOrEmpty(WhereClause.ToString()) ? " WHERE pos_id=:PartId " : " AND pos_id=:PartId ");
var parm = new OracleParameter {
ParameterName = "PartId", OracleDbType = OracleDbType.Varchar2, Value = param.CurrentUserId
};
parameters.Add(parm);
}
else if (param.QueryScope == "Exclusive")
{
WhereClause.Append(string.IsNullOrEmpty(WhereClause.ToString()) ?
" WHERE (exists (select 1 from partner where (partner.partner_id = t.pos_id) and partner.ref_partner = '" + param.CurrentUserId + "'))"
: " AND (exists (select 1 from partner where (partner.partner_id = t.pos_id) and partner.ref_partner = '" + param.CurrentUserId + "'))");
var parm = new OracleParameter {
ParameterName = "PartId", OracleDbType = OracleDbType.Varchar2, Value = param.CurrentUserId
};
parameters.Add(parm);
}
WhereClause.Append(string.IsNullOrEmpty(WhereClause.ToString()) ? " WHERE ROWNUM <= 200 " : " AND ROWNUM <= 200 ");
criteria = WhereClause.ToString();
return(parameters);
}
public IActionResult Index(RechargeQuery model, [FromQuery(Name = "direction")] string direction)
{
model.Error = string.Empty;
var currUserId = partner.GetCurrentUserId(this.HttpContext);
var currRoleId = partner.GetCurrentUserRole(this.HttpContext);
var currAccountId = partner.GetCurrentUserAccount(this.HttpContext);
var permission = partnerActivity.GetPartAct("Recharge.Query", currRoleId);
if (permission == null || permission.Details == null)
{
toastNotification.AddErrorToastMessage("ليس لديك الصلاحية الكافية", new ToastrOptions {
Title = ""
});
model.Statuses = new CommonCodeRepo(db).GetCodesByType("Collection.Status");
model.AccessChannel = new CommonCodeRepo(db).GetCodesByType("access.channel");
return(View(model));
}
if (permission.Scope.Id == "CurOpOnly")
{
if (!string.IsNullOrEmpty(model.QPosId) && model.QPosId != currUserId)
{
toastNotification.AddErrorToastMessage("ليس لديك الصلاحيات الكافية للاستعلام عن هذا الرقم", new ToastrOptions {
Title = ""
});
model.Statuses = new CommonCodeRepo(db).GetCodesByType("Collection.Status");
model.AccessChannel = new CommonCodeRepo(db).GetCodesByType("access.channel");
return(View(model));
}
else if (model.QPosAccount > 0 && model.QPosAccount != currAccountId)
{
toastNotification.AddErrorToastMessage("ليس لديك الصلاحيات الكافية للاستعلام عن هذا الحساب", new ToastrOptions {
Title = ""
});
model.Statuses = new CommonCodeRepo(db).GetCodesByType("Collection.Status");
model.AccessChannel = new CommonCodeRepo(db).GetCodesByType("access.channel");
return(View(model));
}
}
ModelState.Clear();
if (direction == "pre" && model.Paging.PageNo > 1)
{
model.Paging.PageNo -= 1;
}
if (direction == "next")
{
model.Paging.PageNo += 1;
}
model.QueryScope = permission.Scope.Id;
model.CurrentUserId = currUserId;
model.CurrentUserAccount = currAccountId;
var result = new RechargeQuery();
result = new RechargeRepo(db, partner).QueryWithPaging(model);
result.Statuses = new CommonCodeRepo(db).GetCodesByType("Collection.Status");
result.AccessChannel = new CommonCodeRepo(db).GetCodesByType("access.channel");
if (result.Results == null)
{
toastNotification.AddInfoToastMessage("عذرا لا توجد بيانات", new ToastrOptions {
Title = ""
});
}
if (result != null && result.Results != null)
{
model.Paging.Count = new RechargeRepo(db, partner).GetCount(model);
}
else
{
model.Paging.Count = 0;
}
return(View(result));
}