public int GetCount(MoneyTransferQueryDto param)
{
string WhereClause = string.Empty;
var parameters = BuildCriteria(param, ref WhereClause);
var strSqlStatment = new StringBuilder();
strSqlStatment.Append($"Select count(*) val from v_money_transfer t { WhereClause }");
var count = this.db.GetIntScalarValue(strSqlStatment.ToString(), parameters);
return(count);
}
public IActionResult MoneyTranferQuery()
{
var currentRoleId = _partnerManager.GetCurrentUserRole(this.HttpContext);
var permission = _partnerActivity.GetPartAct("MoneyTransfer.Query", currentRoleId);
if (permission == null)
{
return(Redirect(Request.Headers["Referer"].ToString()));
}
var model = new MoneyTransferQueryDto();
model.Paging.PageNo = 1;
model.Paging.PageSize = 50;
model.Paging.Count = 0;
model.QFromDate = DateTime.Today.AddMonths(-1);
model.QToDate = DateTime.Today;
model.QPartnerId = User.Claims.FirstOrDefault(c => c.Type == ClaimTypes.MobilePhone).Value;
return(View(model));
}
public MoneyTransferQueryDto MTQueryWithPaging(MoneyTransferQueryDto param)
{
string WhereClause = string.Empty;
var parameters = BuildCriteria(param, ref WhereClause);
var ps = param.Paging.PageSize > 0 ? param.Paging.PageSize : 50;
var strSqlStatment = new StringBuilder();
strSqlStatment.Append("Select * from ( ");
strSqlStatment.Append("select rownum as seq , main_data.* from ( ");
strSqlStatment.Append("Select * from v_money_transfer t " + WhereClause + " order by createdon desc ");
strSqlStatment.Append(") main_data ) ");
strSqlStatment.Append($"WHERE seq > ({param.Paging.PageNo - 1}) * {param.Paging.PageSize} AND ROWNUM <= {ps}");
var masterDataTable = this.db.GetData(strSqlStatment.ToString(), parameters);
if (masterDataTable == null)
{
param.Results = null;
return(param);
}
if (masterDataTable.Rows.Count == 0)
{
param.Results = null;
return(param);
}
var moneyTransfer = new List <MoneyTransferDetailQueryDto>();
foreach (DataRow row in masterDataTable.Rows)
{
var obj = ConvertDataRowToDataModel(row, true);
moneyTransfer.Add(obj);
}
param.Results = moneyTransfer;
return(param);
}
private List <OracleParameter> BuildCriteria(MoneyTransferQueryDto param, ref string criteria)
{
var WhereClause = new StringBuilder();
var parameters = new List <OracleParameter>();
if (param.QId > 0)
{
var parm = new OracleParameter {
ParameterName = "TransId", OracleDbType = OracleDbType.Int32, Value = param.QId
};
WhereClause.Append(" WHERE trans_id=:TransId ");
parameters.Add(parm);
}
if (!string.IsNullOrEmpty(param.QPartnerId))
{
if (param.QListTypeId == "all")
{
if (param.QScope == "CurOpOnly")
{
WhereClause.Append(string.IsNullOrEmpty(WhereClause.ToString()) ? " WHERE (part_id=:PartId OR createdby=:PartId) "
: " AND (part_id=:PartId OR createdby=:PartId2) ");
var parm = new OracleParameter {
ParameterName = "PartId", OracleDbType = OracleDbType.Varchar2, Value = param.QueryUser
};
var parm2 = new OracleParameter {
ParameterName = "PartId2", OracleDbType = OracleDbType.Varchar2, Value = param.QueryUser
};
parameters.Add(parm);
parameters.Add(parm2);
}
else if (param.QScope == "Exclusive")
{
WhereClause.Append(string.IsNullOrEmpty(WhereClause.ToString())
? " WHERE (part_id=:PartId OR createdby=:PartId2) AND (exists (select 1 from partner where (partner.partner_id = t.part_id or partner.partner_id = t.createdby) and partner.ref_partner = '" + param.QueryUser + "'))"
: " AND (part_id=:PartId OR createdby=:PartId2) AND (exists (select 1 from partner where (partner.partner_id = t.part_id or partner.partner_id = t.createdby) and partner.ref_partner = '" + param.QueryUser + "'))");
var parm = new OracleParameter {
ParameterName = "PartId", OracleDbType = OracleDbType.Varchar2, Value = param.QueryUser
};
var parm2 = new OracleParameter {
ParameterName = "PartId2", OracleDbType = OracleDbType.Varchar2, Value = param.QueryUser
};
parameters.Add(parm);
parameters.Add(parm2);
}
else
{
WhereClause.Append(string.IsNullOrEmpty(WhereClause.ToString()) ? " WHERE (part_id=:PartId OR createdby=:PartId2) "
: " AND (part_id=:PartId OR createdby=:PartId2) ");
var parm = new OracleParameter {
ParameterName = "PartId", OracleDbType = OracleDbType.Varchar2, Value = param.QPartnerId
};
var parm2 = new OracleParameter {
ParameterName = "PartId2", OracleDbType = OracleDbType.Varchar2, Value = param.QPartnerId
};
parameters.Add(parm);
parameters.Add(parm2);
}
}
else if (param.QListTypeId == "debit")
{
if (param.QScope == "CurOpOnly")
{
WhereClause.Append(string.IsNullOrEmpty(WhereClause.ToString()) ? " WHERE part_id=:PartId "
: " AND part_id=:PartId ");
var parm = new OracleParameter {
ParameterName = "PartId", OracleDbType = OracleDbType.Varchar2, Value = param.QueryUser
};
parameters.Add(parm);
}
else if (param.QScope == "Exclusive")
{
WhereClause.Append(string.IsNullOrEmpty(WhereClause.ToString())
? " WHERE (part_id=:PartId) AND (exists (select 1 from partner where (partner.partner_id = t.part_id) and partner.ref_partner = '" + param.QueryUser + "'))"
: " AND (part_id=:PartId) AND (exists (select 1 from partner where (partner.partner_id = t.part_id) and partner.ref_partner = '" + param.QueryUser + "'))");
var parm = new OracleParameter {
ParameterName = "PartId", OracleDbType = OracleDbType.Varchar2, Value = param.QueryUser
};
parameters.Add(parm);
}
else
{
WhereClause.Append(string.IsNullOrEmpty(WhereClause.ToString()) ? " WHERE part_id=:PartId " : " AND part_id=:PartId ");
var parm = new OracleParameter {
ParameterName = "PartId", OracleDbType = OracleDbType.Varchar2, Value = param.QPartnerId
};
parameters.Add(parm);
}
}
else if (param.QListTypeId == "credit")
{
if (param.QScope == "CurOpOnly")
{
WhereClause.Append(string.IsNullOrEmpty(WhereClause.ToString()) ? " WHERE createdby=:PartId "
: " AND createdby=:PartId ");
var parm = new OracleParameter {
ParameterName = "PartId", OracleDbType = OracleDbType.Varchar2, Value = param.QueryUser
};
parameters.Add(parm);
}
else if (param.QScope == "Exclusive")
{
WhereClause.Append(string.IsNullOrEmpty(WhereClause.ToString())
? " WHERE (part_id=:PartId) AND (exists (select 1 from partner where (partner.partner_id = t.createdby) and partner.ref_partner = '" + param.QueryUser + "'))"
: " AND (part_id=:PartId) AND (exists (select 1 from partner where (partner.partner_id = t.createdby) and partner.ref_partner = '" + param.QueryUser + "'))");
var parm = new OracleParameter {
ParameterName = "PartId", OracleDbType = OracleDbType.Varchar2, Value = param.QueryUser
};
parameters.Add(parm);
}
else
{
WhereClause.Append(string.IsNullOrEmpty(WhereClause.ToString()) ? " WHERE createdby=:PartId " : " AND createdby=:PartId ");
var parm = new OracleParameter {
ParameterName = "PartId", OracleDbType = OracleDbType.Varchar2, Value = param.QPartnerId
};
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);
}
criteria = WhereClause.ToString();
return(parameters);
}
public async Task <MoneyTransferQueryDto> MTQuery(MoneyTransferQueryDto param, int count = 200)
{
var WhereClause = new StringBuilder();
var parameters = new List <OracleParameter>();
if (param.QId > 0)
{
var parm = new OracleParameter {
ParameterName = "TransId", OracleDbType = OracleDbType.Int32, Value = param.QId
};
WhereClause.Append(" WHERE trans_id=:TransId ");
parameters.Add(parm);
}
if (!string.IsNullOrEmpty(param.QPartnerId))
{
if (param.QListTypeId == "all")
{
if (param.QScope == "CurOpOnly")
{
WhereClause.Append(string.IsNullOrEmpty(WhereClause.ToString()) ? " WHERE part_id=:PartId OR createdby=:PartId "
: " AND part_id=:PartId OR createdby=:PartId ");
var parm = new OracleParameter {
ParameterName = "PartId", OracleDbType = OracleDbType.Varchar2, Value = param.QueryUser
};
parameters.Add(parm);
}
else if (param.QScope == "Exclusive")
{
WhereClause.Append(string.IsNullOrEmpty(WhereClause.ToString())
? " WHERE (part_id=:PartId OR createdby=:PartId) AND (exists (select 1 from partner where (partner.partner_id = t.part_id or partner.partner_id = t.createdby) and partner.ref_partner = '" + param.QueryUser + "'))"
: " AND (part_id=:PartId OR createdby=:PartId) AND (exists (select 1 from partner where (partner.partner_id = t.part_id or partner.partner_id = t.createdby) and partner.ref_partner = '" + param.QueryUser + "'))");
var parm = new OracleParameter {
ParameterName = "PartId", OracleDbType = OracleDbType.Varchar2, Value = param.QueryUser
};
parameters.Add(parm);
}
else
{
WhereClause.Append(string.IsNullOrEmpty(WhereClause.ToString()) ? " WHERE part_id=:PartId OR createdby=:PartId "
: " AND part_id=:PartId OR createdby=:PartId ");
var parm = new OracleParameter {
ParameterName = "PartId", OracleDbType = OracleDbType.Varchar2, Value = param.QPartnerId
};
parameters.Add(parm);
}
}
else if (param.QListTypeId == "debit")
{
if (param.QScope == "CurOpOnly")
{
WhereClause.Append(string.IsNullOrEmpty(WhereClause.ToString()) ? " WHERE part_id=:PartId "
: " AND part_id=:PartId ");
var parm = new OracleParameter {
ParameterName = "PartId", OracleDbType = OracleDbType.Varchar2, Value = param.QueryUser
};
parameters.Add(parm);
}
else if (param.QScope == "Exclusive")
{
WhereClause.Append(string.IsNullOrEmpty(WhereClause.ToString())
? " WHERE (part_id=:PartId) AND (exists (select 1 from partner where (partner.partner_id = t.part_id) and partner.ref_partner = '" + param.QueryUser + "'))"
: " AND (part_id=:PartId) AND (exists (select 1 from partner where (partner.partner_id = t.part_id) and partner.ref_partner = '" + param.QueryUser + "'))");
var parm = new OracleParameter {
ParameterName = "PartId", OracleDbType = OracleDbType.Varchar2, Value = param.QueryUser
};
parameters.Add(parm);
}
else
{
WhereClause.Append(string.IsNullOrEmpty(WhereClause.ToString()) ? " WHERE part_id=:PartId " : " AND part_id=:PartId ");
var parm = new OracleParameter {
ParameterName = "PartId", OracleDbType = OracleDbType.Varchar2, Value = param.QPartnerId
};
parameters.Add(parm);
}
}
else if (param.QListTypeId == "credit")
{
if (param.QScope == "CurOpOnly")
{
WhereClause.Append(string.IsNullOrEmpty(WhereClause.ToString()) ? " WHERE createdby=:PartId "
: " AND createdby=:PartId ");
var parm = new OracleParameter {
ParameterName = "PartId", OracleDbType = OracleDbType.Varchar2, Value = param.QueryUser
};
parameters.Add(parm);
}
else if (param.QScope == "Exclusive")
{
WhereClause.Append(string.IsNullOrEmpty(WhereClause.ToString())
? " WHERE (part_id=:PartId) AND (exists (select 1 from partner where (partner.partner_id = t.createdby) and partner.ref_partner = '" + param.QueryUser + "'))"
: " AND (part_id=:PartId) AND (exists (select 1 from partner where (partner.partner_id = t.createdby) and partner.ref_partner = '" + param.QueryUser + "'))");
var parm = new OracleParameter {
ParameterName = "PartId", OracleDbType = OracleDbType.Varchar2, Value = param.QueryUser
};
parameters.Add(parm);
}
else
{
WhereClause.Append(string.IsNullOrEmpty(WhereClause.ToString()) ? " WHERE createdby=:PartId " : " AND createdby=:PartId ");
var parm = new OracleParameter {
ParameterName = "PartId", OracleDbType = OracleDbType.Varchar2, Value = param.QPartnerId
};
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 createdon<=:EndDate " : " AND createdon<=:EndDate ");
var parm = new OracleParameter {
ParameterName = "EndDate", OracleDbType = OracleDbType.Date, Value = param.QToDate
};
parameters.Add(parm);
}
WhereClause.Append(string.IsNullOrEmpty(WhereClause.ToString()) ? " WHERE ROWNUM <= " + count : " AND ROWNUM <= " + count);
var masterDataTable = await this.db.GetDataAsync("Select * from v_money_transfer " + WhereClause + " order by createdon desc", parameters);
if (masterDataTable == null)
{
return(null);
}
if (masterDataTable.Rows.Count == 0)
{
return(null);
}
var moneyTransfer = new List <MoneyTransferDetailQueryDto>();
foreach (DataRow row in masterDataTable.Rows)
{
var obj = ConvertDataRowToDataModel(row);
moneyTransfer.Add(obj);
}
param.Results = moneyTransfer;
return(param);
}
public IActionResult MoneyTranferQuery(MoneyTransferQueryDto model, [FromQuery
(Name = "direction")] string direction)
{
#region Prepare Query
model.Error = string.Empty;
Partner targetPartner = null;
var currUserId = _partnerManager.GetCurrentUserId(this.HttpContext);
var currRoleId = _partnerManager.GetCurrentUserRole(this.HttpContext);
var currAccountId = _partnerManager.GetCurrentUserAccount(this.HttpContext);
var permission = _partnerActivity.GetPartAct("MoneyTransfer.Query", currRoleId);
if (permission == null)
{
model.Error = "ليس لديك الصلاحيات الكافية";
return(View(model));
}
if (!string.IsNullOrEmpty(model.QPartnerId) && model.QPartnerId != currUserId)
{
var validateTargetPartnerResult = _partnerManager.Validate(model.QPartnerId);
targetPartner = validateTargetPartnerResult.Success ? validateTargetPartnerResult.Partner : null;
if (targetPartner == null)
{
model.Error = "يرجى التأكد من الرقم المراد الاستعلام عنه";
return(View(model));
}
}
if (permission.Scope.Id == "CurOpOnly" && model.QPartnerId != currUserId)
{
model.Error = "ليس لديك الصلاحيات الكافية للاستعلام عن هذا الرقم";
return(View(model));
}
else if (permission.Scope.Id == "Exclusive" && targetPartner != null && targetPartner.RefPartner.Id != currUserId)
{
model.Error = "ليس لديك الصلاحيات الكافية للاستعلام عن هذا الرقم";
return(View(model));
}
#endregion
ModelState.Clear();
if (direction == "pre" && model.Paging.PageNo > 1)
{
model.Paging.PageNo -= 1;
}
if (direction == "next")
{
model.Paging.PageNo += 1;
}
if (model.QFromDate == DateTime.MinValue)
{
model.QFromDate = DateTime.Today.AddMonths(-1);
}
if (model.QToDate == DateTime.MinValue)
{
model.QFromDate = DateTime.Today;
}
model.QueryUser = _partnerManager.GetCurrentUserId(this.HttpContext);
model.QScope = permission.Scope.Id;
var result = new MoneyTransferRepo(_db, _partnerManager, _partnerActivity).MTQueryWithPaging(model);
if (result != null && result.Results != null)
{
model.Paging.Count = new MoneyTransferRepo(_db, _partnerManager, _partnerActivity).GetCount(model);
}
else
{
model.Paging.Count = 0;
}
return(View(result));
}
