public async Task <List <RechargeCollection> > GetCollectionsAsync(string whereClause, List <OracleParameter> parameters)
{
string strSql = $"select * from V_COLLECTION {whereClause} order by cl_id";
DataTable masterDataTable;
masterDataTable = await db.GetDataAsync(strSql, parameters);
if (masterDataTable == null)
{
return(null);
}
if (masterDataTable.Rows.Count == 0)
{
return(null);
}
var results = new List <RechargeCollection>();
foreach (DataRow row in masterDataTable.Rows)
{
var obj = new RechargeCollection();
obj.Id = row["cl_id"] == DBNull.Value ? 0 : int.Parse(row["cl_id"].ToString());
obj.CreatedOn = row["createdon"] == DBNull.Value ? DateTime.MinValue : DateTime.Parse(row["createdon"].ToString());
obj.SubscriberNo = row["subs_no"] == DBNull.Value ? string.Empty : row["subs_no"].ToString();
obj.Amount = row["amount"] == DBNull.Value ? 0 : double.Parse(row["amount"].ToString());
obj.PointOfSale.Id = row["pos_id"] == DBNull.Value ? string.Empty : row["pos_id"].ToString();
obj.PointOfSale.Account = row["pos_acc"] == DBNull.Value ? 0 :int.Parse(row["pos_acc"].ToString());
obj.PointOfSale.Balance = row["pos_bal"] == DBNull.Value ? 0 : double.Parse(row["pos_bal"].ToString());
obj.PointOfSale.Name = row["pos_name"] == DBNull.Value ? string.Empty : row["pos_name"].ToString();
obj.PointOfSale.Role.Id = row["pos_role_id"] == DBNull.Value ? 0 : int.Parse(row["pos_role_id"].ToString());
obj.PointOfSale.Role.Name = row["pos_role_name"] == DBNull.Value ? string.Empty : row["pos_role_name"].ToString();
obj.AccessChannel.Id = row["access_channel"] == DBNull.Value ? string.Empty : row["access_channel"].ToString();
obj.AccessChannel.Name = row["access_channel_name"] == DBNull.Value ? string.Empty : row["access_channel_name"].ToString();
obj.Status.Id = row["status"] == DBNull.Value ? 0 : int.Parse(row["status"].ToString());
obj.Status.Name = row["status_name"] == DBNull.Value ? string.Empty : row["status_name"].ToString();
obj.StatusTime = row["status_time"] == DBNull.Value ? DateTime.MinValue : DateTime.Parse(row["status_time"].ToString());
obj.QueueNo = row["queue_no"] == DBNull.Value ? 0 : int.Parse(row["queue_no"].ToString());
obj.RefNo = row["ref_no"] == DBNull.Value ? string.Empty : row["ref_no"].ToString();
obj.RefMessage = row["ref_message"] == DBNull.Value ? string.Empty : row["ref_message"].ToString();
obj.RefTime = row["ref_time"] == DBNull.Value ? DateTime.MinValue : DateTime.Parse(row["ref_time"].ToString());
obj.RefTransNo = row["ref_trans_no"] == DBNull.Value ? string.Empty : row["ref_trans_no"].ToString();
obj.DebugInfo = row["debug_info"] == DBNull.Value ? string.Empty : row["debug_info"].ToString();
obj.ApiTransaction = row["api_trans"] == DBNull.Value ? 0 : int.Parse(row["api_trans"].ToString());
results.Add(obj);
}
return(results);
}
public async Task <List <CollectionRpt> > GetStatReportAsync(CollectionRptQueryParam param)
{
#region Parameters
param.Title = "تقرير احصائي يوضح اجمالي التحصيلات";
var parameters = new List <OracleParameter>();
var whereCluase = new StringBuilder();
if (param != null)
{
if (!string.IsNullOrEmpty(param.PosId))
{
whereCluase.Append(" WHERE pos_id = :PosId");
var p = new OracleParameter {
ParameterName = "PosId", OracleDbType = OracleDbType.Varchar2, Value = param.PosId
};
parameters.Add(p);
param.Title += $"للرقم {param.PosId} ";
}
if (!string.IsNullOrEmpty(param.SubsNo))
{
whereCluase.Append(whereCluase.Length > 0 ? " AND subs_no = :SubsNo" : " WHERE subs_no = :SubsNo");
var p = new OracleParameter {
ParameterName = "SubsNo", OracleDbType = OracleDbType.Varchar2, Value = param.SubsNo
};
parameters.Add(p);
param.Title += $"للمشترك {param.SubsNo} ";
}
if (!string.IsNullOrEmpty(param.ChannelId) && param.ChannelId != "-1")
{
whereCluase.Append(whereCluase.Length > 0 ? " AND access_channel = :ChannelId" : " WHERE access_channel = :ChannelId");
var p = new OracleParameter {
ParameterName = "ChannelId", OracleDbType = OracleDbType.Varchar2, Value = param.ChannelId
};
parameters.Add(p);
}
if (!string.IsNullOrEmpty(param.StatusId) && param.StatusId != "-1")
{
whereCluase.Append(whereCluase.Length > 0 ? " AND status = :StatusId" : " WHERE status = :StatusId");
var p = new OracleParameter {
ParameterName = "StatusId", OracleDbType = OracleDbType.Varchar2, Value = param.StatusId
};
parameters.Add(p);
}
if (param.StartDate > DateTime.MinValue && param.StartDate != null)
{
whereCluase.Append(whereCluase.Length > 0 ? " AND createdon >= :StartDate" : " WHERE createdon >= :StartDate");
var p = new OracleParameter {
ParameterName = "StartDate", OracleDbType = OracleDbType.Date, Value = param.StartDate
};
parameters.Add(p);
}
if (param.EndDate > DateTime.MinValue && param.EndDate != null)
{
whereCluase.Append(whereCluase.Length > 0 ? " AND createdon <= :EndDate" : " WHERE createdon <= :EndDate");
var p = new OracleParameter {
ParameterName = "EndDate", OracleDbType = OracleDbType.Date, Value = param.EndDate.AddDays(1)
};
parameters.Add(p);
}
}
#endregion
string strSql = string.Empty;
if (param.LevelId == "pos")
{
param.Title += $"\n للفترة من {param.StartDate.ToShortDateString()} الى {param.EndDate.ToShortDateString()} " + "على مستوى نقطة البيع";
strSql = $"select t.access_channel,t.access_channel_name, t.pos_id, t.pos_name,status, count(*) cnt, " +
$"sum(t.amount) amt,count(distinct t.pos_id) dist_cnt from V_COLLECTION t {whereCluase}" +
$" group by t.access_channel, t.pos_id, t.pos_name,t.access_channel_name,status ";
}
else
{
param.Title += $"\n للفترة من {param.StartDate.ToShortDateString()} الى {param.EndDate.ToShortDateString()} " + "على مستوى اليوم";
strSql = $"select t.access_channel,t.access_channel_name, to_char(createdon,'yyyy/mm/dd') d, count(*) cnt, sum(t.amount) amt,count(distinct t.pos_id) dist_cnt " +
$" ,status from V_COLLECTION t {whereCluase}" +
$" group by t.access_channel,to_char(createdon,'yyyy/mm/dd'),t.access_channel_name,status ";
}
DataTable masterDataTable;
masterDataTable = await db.GetDataAsync(strSql, parameters);
if (masterDataTable == null)
{
return(null);
}
if (masterDataTable.Rows.Count == 0)
{
return(null);
}
var results = new List <CollectionRpt>();
foreach (DataRow row in masterDataTable.Rows)
{
var obj = new CollectionRpt();
obj.Amount = row["amt"] == DBNull.Value ? 0 : double.Parse(row["amt"].ToString());
obj.Count = row["cnt"] == DBNull.Value ? 0 : int.Parse(row["cnt"].ToString());
obj.DistinctCount = row["dist_cnt"] == DBNull.Value ? 0 : int.Parse(row["dist_cnt"].ToString());
obj.Channel = row["access_channel_name"] == DBNull.Value ? string.Empty : row["access_channel_name"].ToString();
var statusId = row["status"] == DBNull.Value ? string.Empty : row["status"].ToString();
obj.Status = new CommonCodeRepo(db).GetCodesById(statusId, "Collection.Status");
if (param.LevelId == "pos")
{
obj.Partner.Id = row["pos_id"] == DBNull.Value ? string.Empty : row["pos_id"].ToString();
obj.Partner.Name = row["pos_name"] == DBNull.Value ? string.Empty : row["pos_name"].ToString();
}
else if (param.LevelId == "day")
{
obj.CollDay = row["d"] == DBNull.Value ? string.Empty : row["d"].ToString();
}
results.Add(obj);
}
return(results);
}
public async Task <List <MoneyTransferRpt> > GetStatReportAsync(MoneyTransferRptQueryParam param)
{
#region Parameters
param.Title = "تقرير احصائي يوضح اجمالي نقل ارصدة";
var parameters = new List <OracleParameter>();
var whereCluase = new StringBuilder();
if (param != null)
{
if (!string.IsNullOrEmpty(param.PosId))
{
whereCluase.Append(param.TransTypeId == "debit" ? " WHERE part_id = :PosId" : " WHERE createdby = :PosId");
var p = new OracleParameter {
ParameterName = "PosId", OracleDbType = OracleDbType.Varchar2, Value = param.PosId
};
parameters.Add(p);
param.Title += $"{Environment.NewLine} للرقم {param.PosId} ";
}
if (!string.IsNullOrEmpty(param.ChannelId) && param.ChannelId != "-1")
{
whereCluase.Append(whereCluase.Length > 0 ? " AND access_channel = :ChannelId" : " WHERE access_channel = :ChannelId");
var p = new OracleParameter {
ParameterName = "ChannelId", OracleDbType = OracleDbType.Varchar2, Value = param.ChannelId
};
parameters.Add(p);
}
if (param.StartDate > DateTime.MinValue && param.StartDate != null)
{
whereCluase.Append(whereCluase.Length > 0 ? " AND createdon >= :StartDate" : " WHERE createdon >= :StartDate");
var p = new OracleParameter {
ParameterName = "StartDate", OracleDbType = OracleDbType.Date, Value = param.StartDate
};
parameters.Add(p);
}
if (param.EndDate > DateTime.MinValue && param.EndDate != null)
{
whereCluase.Append(whereCluase.Length > 0 ? " AND createdon <= :EndDate" : " WHERE createdon <= :EndDate");
var p = new OracleParameter {
ParameterName = "EndDate", OracleDbType = OracleDbType.Date, Value = param.EndDate.AddDays(1)
};
parameters.Add(p);
}
}
#endregion
string strSql = string.Empty;
if (param.LevelId == "pos")
{
param.Title += $"{Environment.NewLine} للفترة من {param.StartDate.ToShortDateString()} الى {param.EndDate.ToShortDateString()} " + "على مستوى نقطة البيع";
strSql = $"select t.access_channel,t.access_channel_name, " + (param.TransTypeId == "debit" ? "t.part_id, t.part_name," : "t.createdby, t.creator_name,")
+ " count(*) cnt, sum(t.amount) amt from v_money_transfer t " +
$" {whereCluase}" +
$" group by t.access_channel, " + (param.TransTypeId == "debit" ? "t.part_id, t.part_name," : "t.createdby, t.creator_name,") + " t.access_channel_name ";
}
else
{
param.Title += $"{Environment.NewLine} للفترة من {param.StartDate.ToShortDateString()} الى {param.EndDate.ToShortDateString()} " + "على مستوى اليوم";
strSql = $"select t.access_channel,t.access_channel_name,to_char(createdon,'yyyy/mm/dd') d, count(*) cnt, sum(t.amount) amt from v_money_transfer t " +
$" {whereCluase}" +
$" group by t.access_channel,t.access_channel_name, to_char(createdon,'yyyy/mm/dd') ";
}
DataTable masterDataTable;
masterDataTable = await db.GetDataAsync(strSql, parameters);
if (masterDataTable == null)
{
return(null);
}
if (masterDataTable.Rows.Count == 0)
{
return(null);
}
var results = new List <MoneyTransferRpt>();
foreach (DataRow row in masterDataTable.Rows)
{
var obj = new MoneyTransferRpt();
obj.Amount = row["amt"] == DBNull.Value ? 0 : double.Parse(row["amt"].ToString());
obj.Count = row["cnt"] == DBNull.Value ? 0 : int.Parse(row["cnt"].ToString());
obj.Channel = row["access_channel_name"] == DBNull.Value ? string.Empty : row["access_channel_name"].ToString();
if (param.LevelId == "pos")
{
obj.Partner.Id = row[(param.TransTypeId == "debit" ? "part_id" : "createdby")] == DBNull.Value ? string.Empty : row[(param.TransTypeId == "debit" ? "part_id" : "createdby")].ToString();
obj.Partner.Name = row[(param.TransTypeId == "debit" ? "part_name" : "creator_name")] == DBNull.Value ? string.Empty : row[(param.TransTypeId == "debit" ? "part_name" : "creator_name")].ToString();
}
else if (param.LevelId == "day")
{
obj.CollDay = row["d"] == DBNull.Value ? string.Empty : row["d"].ToString();
}
results.Add(obj);
}
return(results);
}
public async Task <List <SMSIn> > GetSMSMessagesAsync(string mobileNo, string Msg, bool IncludeDates, DateTime?startDate, DateTime?endDate)
{
#region Parameters
var parameters = new List <OracleParameter>();
var whereCluase = new StringBuilder();
if (!string.IsNullOrEmpty(mobileNo))
{
whereCluase.Append(" WHERE sender = :mobileNo");
var p = new OracleParameter {
ParameterName = "mobileNo", OracleDbType = OracleDbType.Varchar2, Value = mobileNo
};
parameters.Add(p);
}
if (!string.IsNullOrEmpty(Msg))
{
whereCluase.Append(whereCluase.Length > 0 ? " AND message LIKE '%' || :Msg || '%' " : " WHERE message LIKE '%' || :Msg || '%' ");
var p = new OracleParameter {
ParameterName = "Msg", OracleDbType = OracleDbType.Varchar2, Value = Msg
};
parameters.Add(p);
}
//if (IncludeDates)
{
if (startDate > DateTime.MinValue && startDate != null)
{
whereCluase.Append(whereCluase.Length > 0 ? " AND createdon >= :StartDate" : " WHERE createdon >= :StartDate");
var p = new OracleParameter {
ParameterName = "StartDate", OracleDbType = OracleDbType.Date, Value = startDate
};
parameters.Add(p);
}
if (endDate > DateTime.MinValue && endDate != null)
{
whereCluase.Append(whereCluase.Length > 0 ? " AND createdon <= :EndDate" : " WHERE createdon <= :EndDate");
var p = new OracleParameter {
ParameterName = "EndDate", OracleDbType = OracleDbType.Date, Value = endDate
};
parameters.Add(p);
}
}
#endregion
string strSql = $"select * from sms_in {whereCluase} order by createdon";
DataTable masterDataTable;
masterDataTable = await db.GetDataAsync(strSql, parameters);
if (masterDataTable == null)
{
return(null);
}
if (masterDataTable.Rows.Count == 0)
{
return(null);
}
var results = new List <SMSIn>();
foreach (DataRow row in masterDataTable.Rows)
{
var obj = new SMSIn();
obj.Id = row["in_no"] == DBNull.Value ? 0 : int.Parse(row["in_no"].ToString());
obj.CreatedOn = row["createdon"] == DBNull.Value ? DateTime.MinValue : DateTime.Parse(row["createdon"].ToString());
obj.Message = row["message"] == DBNull.Value ? string.Empty : row["message"].ToString();
obj.Receiver = row["reciever"] == DBNull.Value ? string.Empty : row["reciever"].ToString();
obj.Sender = row["sender"] == DBNull.Value ? string.Empty : row["sender"].ToString();
obj.Lang = row["lang"] == DBNull.Value ? string.Empty : row["lang"].ToString();
obj.RefNo = row["ref_no"] == DBNull.Value ? 0 : int.Parse(row["ref_no"].ToString());
results.Add(obj);
}
return(results);
}
public async Task <List <AppBackgroundService> > GetBackgroundServicesAsync(BackgroundServiceListParam param, PartnerActivity permission,
string currentPartnerId, int currentPartnerAccount)
{
#region Parameters
var parameters = new List <OracleParameter>();
var whereCluase = new StringBuilder();
if (param != null)
{
if (!string.IsNullOrEmpty(param.Source) && param.Source != "-1")
{
whereCluase.Append(" WHERE bg_source = :Source");
var p = new OracleParameter {
ParameterName = "Source", OracleDbType = OracleDbType.Varchar2, Value = param.Source
};
parameters.Add(p);
}
if (param.Id > 0)
{
whereCluase.Append(whereCluase.Length > 0 ? " AND bg_id = :bgId" : " WHERE bg_id = :bgId");
var p = new OracleParameter {
ParameterName = "bgId", OracleDbType = OracleDbType.Int32, Value = param.Id
};
parameters.Add(p);
}
if (!string.IsNullOrEmpty(param.CreatedById))
{
whereCluase.Append(whereCluase.Length > 0 ? " AND createdby = :CreatedById" : " WHERE createdby = :CreatedById");
var p = new OracleParameter {
ParameterName = "CreatedById", OracleDbType = OracleDbType.Varchar2, Value = param.CreatedById
};
parameters.Add(p);
}
if (param.ActionPartnerAccount > 0)
{
whereCluase.Append(whereCluase.Length > 0 ? " AND action_partner_acc = :ActionPartnerAcc " : " WHERE action_partner_acc = :ActionPartnerAcc ");
var p = new OracleParameter {
ParameterName = "ActionPartnerAcc", OracleDbType = OracleDbType.Int32, Value = param.ActionPartnerAccount
};
parameters.Add(p);
}
if (permission.Scope.Id == "CurOpOnly")
{
whereCluase.Append(whereCluase.Length > 0 ? $" AND (partner_id = :PartnerId OR action_partner_acc = {currentPartnerAccount})" : $" WHERE (partner_id = :PartnerId OR action_partner_acc = {currentPartnerAccount})");
var p = new OracleParameter {
ParameterName = "PartnerId", OracleDbType = OracleDbType.Varchar2, Value = currentPartnerId
};
parameters.Add(p);
}
else if (!string.IsNullOrEmpty(param.PartnerId))
{
whereCluase.Append(whereCluase.Length > 0 ? " AND partner_id = :PartnerId" : " WHERE partner_id = :PartnerId");
var p = new OracleParameter {
ParameterName = "PartnerId", OracleDbType = OracleDbType.Varchar2, Value = param.PartnerId
};
parameters.Add(p);
}
if (permission.Scope.Id == "Exclusive")
{
var parm = new OracleParameter {
ParameterName = "RefId", OracleDbType = OracleDbType.Varchar2, Value = currentPartnerId
};
whereCluase.Append(whereCluase.Length > 0 ? $" AND (ref_partner=:RefId OR action_partner_acc = {currentPartnerAccount})"
: $" WHERE (ref_partner=:RefId OR action_partner_acc = {currentPartnerAccount})");
parameters.Add(parm);
}
if (!string.IsNullOrEmpty(param.Status) && param.Status != "-1")
{
whereCluase.Append(whereCluase.Length > 0 ? " AND status = :StatusId" : " WHERE status = :StatusId");
var p = new OracleParameter {
ParameterName = "StatusId", OracleDbType = OracleDbType.Varchar2, Value = param.Status
};
parameters.Add(p);
}
if (!string.IsNullOrEmpty(param.Name))
{
whereCluase.Append(whereCluase.Length > 0 ? " AND service_name LIKE '%' || :Name || '%' " : " WHERE service_name LIKE '%' || :Name || '%' ");
var p = new OracleParameter {
ParameterName = "Name", OracleDbType = OracleDbType.Varchar2, Value = param.Name
};
parameters.Add(p);
}
//if (param.IncludeDates)
{
if (param.StartDate > DateTime.MinValue && param.StartDate != null)
{
whereCluase.Append(whereCluase.Length > 0 ? " AND createdon >= :StartDate" : " WHERE createdon >= :StartDate");
var p = new OracleParameter {
ParameterName = "StartDate", OracleDbType = OracleDbType.Date, Value = param.StartDate
};
parameters.Add(p);
}
if (param.EndDate > DateTime.MinValue && param.EndDate != null)
{
whereCluase.Append(whereCluase.Length > 0 ? " AND createdon <= :EndDate" : " WHERE createdon <= :EndDate");
var p = new OracleParameter {
ParameterName = "EndDate", OracleDbType = OracleDbType.Date, Value = param.EndDate.AddDays(1)
};
parameters.Add(p);
}
}
if (permission.Details == null || permission.Details.Count == 0)
{
whereCluase.Append(string.IsNullOrEmpty(whereCluase.ToString()) ? " WHERE roleid=-1 " : " AND roleid=-1 ");
}
else
{
var allowedRoles = string.Join(",", permission.Details.Select(x => x.ToRole.Id).ToList());
whereCluase.Append(string.IsNullOrEmpty(whereCluase.ToString())
? $" WHERE (roleid in ( {allowedRoles} ) OR action_partner_acc = {currentPartnerAccount})"
: $" AND ( roleid in ( {allowedRoles} ) OR action_partner_acc = {currentPartnerAccount})");
}
}
#endregion
string strSql = $"select * from v_bg_service {whereCluase} order by bg_id";
DataTable masterDataTable;
masterDataTable = await db.GetDataAsync(strSql, parameters);
if (masterDataTable == null)
{
return(null);
}
if (masterDataTable.Rows.Count == 0)
{
return(null);
}
var results = new List <AppBackgroundService>();
foreach (DataRow row in masterDataTable.Rows)
{
var obj = ConvertDataRowToAppBackgroundService(row);
results.Add(obj);
}
return(results);
}