public List <SMSOne> GetList(GetListParam param)
{
#region Parameters
var parameters = new List <OracleParameter>();
var whereCluase = new StringBuilder();
if (param != null)
{
if (!string.IsNullOrEmpty(param.Receiver))
{
whereCluase.Append(" WHERE receiver = :Receiver");
var p = new OracleParameter {
ParameterName = "Receiver", OracleDbType = OracleDbType.Varchar2, Value = param.Receiver
};
parameters.Add(p);
}
if (!string.IsNullOrEmpty(param.CreatorId))
{
whereCluase.Append(whereCluase.Length > 0 ? " AND createdby = :CreatorId" : " WHERE createdby = :CreatorId");
var p = new OracleParameter {
ParameterName = "CreatorId", OracleDbType = OracleDbType.Varchar2, Value = param.CreatorId
};
parameters.Add(p);
}
if (param.CreatorAccount > 0)
{
whereCluase.Append(whereCluase.Length > 0 ? " AND createdbyacc = :createdbyacc" : " WHERE createdbyacc = :CreatorAccount");
var p = new OracleParameter {
ParameterName = "CreatorAccount", OracleDbType = OracleDbType.Int32, Value = param.CreatorAccount
};
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
};
parameters.Add(p);
}
}
if (!string.IsNullOrEmpty(param.Message))
{
whereCluase.Append(whereCluase.Length > 0 ? " AND (msg LIKE '%' || :Message || '%') " : " WHERE (msg LIKE '%' || :Message || '%') ");
var p = new OracleParameter {
ParameterName = "Message", OracleDbType = OracleDbType.Varchar2, Value = param.Message
};
parameters.Add(p);
}
}
#endregion
string strSql = $"select * from SEND_SMSONE_HIS {whereCluase} order by sms_id";
DataTable masterDataTable;
masterDataTable = db.GetData(strSql, parameters);
if (masterDataTable == null)
{
return(null);
}
if (masterDataTable.Rows.Count == 0)
{
return(null);
}
var results = new List <SMSOne>();
foreach (DataRow row in masterDataTable.Rows)
{
var obj = new SMSOne();
obj.Id = row["sms_id"] == DBNull.Value ? -1 : int.Parse(row["sms_id"].ToString());
obj.Note = row["note"] == DBNull.Value ? string.Empty : row["note"].ToString();
obj.Shortcode = row["shortcode"] == DBNull.Value ? string.Empty : row["shortcode"].ToString();
obj.Receiver = row["receiver"] == DBNull.Value ? string.Empty : row["receiver"].ToString();
obj.Message = row["msg"] == DBNull.Value ? string.Empty : row["msg"].ToString();
obj.CreatedOn = row["createdon"] == DBNull.Value ? DateTime.MinValue : DateTime.Parse(row["createdon"].ToString());
var createdAccount = row["createdbyacc"] == DBNull.Value ? -1 : int.Parse(row["createdbyacc"].ToString());
var createdBy = partnerManager.GetPartnerByAccount(createdAccount);
obj.CreatedBy.Id = createdBy.Id;
obj.CreatedBy.Name = createdBy.Name;
obj.CreatedBy.Account = createdBy.Account;
results.Add(obj);
}
return(results);
}
public List <Confiscation> GetList(GetListParam param)
{
#region Parameters
var parameters = new List <OracleParameter>();
var whereCluase = new StringBuilder();
if (param != null)
{
if (!string.IsNullOrEmpty(param.PartnerId))
{
whereCluase.Append(" WHERE partner_id = :PartnerId");
var p = new OracleParameter {
ParameterName = "PartnerId", OracleDbType = OracleDbType.Varchar2, Value = param.PartnerId
};
parameters.Add(p);
}
if (!string.IsNullOrEmpty(param.CreatorId))
{
whereCluase.Append(whereCluase.Length > 0 ? " AND createdby = :CreatorId" : " WHERE createdby = :CreatorId");
var p = new OracleParameter {
ParameterName = "CreatorId", OracleDbType = OracleDbType.Varchar2, Value = param.CreatorId
};
parameters.Add(p);
}
if (param.PartnerAccount > 0)
{
whereCluase.Append(whereCluase.Length > 0 ? " AND partner_acc = :PartnerAccount" : " WHERE partner_acc = :PartnerAccount");
var p = new OracleParameter {
ParameterName = "PartnerAccount", OracleDbType = OracleDbType.Int32, Value = param.PartnerAccount
};
parameters.Add(p);
}
if (param.CreatorAccount > 0)
{
whereCluase.Append(whereCluase.Length > 0 ? " AND partner_acc = :createdbyacc" : " WHERE createdbyacc = :CreatorAccount");
var p = new OracleParameter {
ParameterName = "CreatorAccount", OracleDbType = OracleDbType.Int32, Value = param.CreatorAccount
};
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
};
parameters.Add(p);
}
}
}
#endregion
string strSql = $"select * from confiscation {whereCluase} order by con_id";
DataTable masterDataTable;
masterDataTable = db.GetData(strSql, parameters);
if (masterDataTable == null)
{
return(null);
}
if (masterDataTable.Rows.Count == 0)
{
return(null);
}
var results = new List <Confiscation>();
foreach (DataRow row in masterDataTable.Rows)
{
var obj = new Confiscation();
obj.Id = row["con_id"] == DBNull.Value ? -1 : int.Parse(row["con_id"].ToString());
obj.Amount = row["balance"] == DBNull.Value ? -1 : double.Parse(row["balance"].ToString());
obj.Note = row["note"] == DBNull.Value ? string.Empty : row["note"].ToString();
obj.CreatedOn = row["createdon"] == DBNull.Value ? DateTime.MinValue : DateTime.Parse(row["createdon"].ToString());
var createdAccount = row["createdbyacc"] == DBNull.Value ? -1 : int.Parse(row["createdbyacc"].ToString());
var createdBy = partnerManager.GetPartnerByAccount(createdAccount);
obj.CreatedBy.Id = createdBy.Id;
obj.CreatedBy.Name = createdBy.Name;
obj.CreatedBy.Account = createdBy.Account;
var partnerAccount = row["partner_acc"] == DBNull.Value ? -1 : int.Parse(row["partner_acc"].ToString());
var partner = partnerManager.GetPartnerByAccount(partnerAccount);
obj.Partner = partner;
results.Add(obj);
}
return(results);
}
public List <SMSOutBack> GetList(GetListParam param)
{
#region Parameters
var parameters = new List <OracleParameter>();
var whereCluase = new StringBuilder();
if (param != null)
{
if (!string.IsNullOrEmpty(param.Receiver))
{
whereCluase.Append(" WHERE receiver = :Receiver");
var p = new OracleParameter {
ParameterName = "Receiver", OracleDbType = OracleDbType.Varchar2, Value = param.Receiver
};
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 (!string.IsNullOrEmpty(param.Message))
{
whereCluase.Append(whereCluase.Length > 0 ? " AND (message LIKE '%' || :Message || '%') " : " WHERE (message LIKE '%' || :Message || '%') ");
var p = new OracleParameter {
ParameterName = "Message", OracleDbType = OracleDbType.Varchar2, Value = param.Message
};
parameters.Add(p);
}
}
#endregion
whereCluase.Append(string.IsNullOrEmpty(whereCluase.ToString()) ? " WHERE ROWNUM <= 200 " : " AND ROWNUM <= 200 ");
string strSql = $"select * from v_smsout {whereCluase} order by row_id desc";
DataTable masterDataTable;
masterDataTable = db.GetData(strSql, parameters);
if (masterDataTable == null)
{
return(null);
}
if (masterDataTable.Rows.Count == 0)
{
return(null);
}
var results = new List <SMSOutBack>();
foreach (DataRow row in masterDataTable.Rows)
{
var obj = new SMSOutBack();
obj.Id = row["row_id"] == DBNull.Value ? -1 : int.Parse(row["row_id"].ToString());
obj.Receiver = row["receiver"] == DBNull.Value ? string.Empty : row["receiver"].ToString();
obj.Sender = row["sender"] == DBNull.Value ? string.Empty : row["sender"].ToString();
obj.Message = row["message"] == DBNull.Value ? string.Empty : row["message"].ToString();
obj.CreatedOn = row["createdon"] == DBNull.Value ? DateTime.MinValue : DateTime.Parse(row["createdon"].ToString());
obj.BackedOn = row["backedon"] == DBNull.Value ? DateTime.MinValue : DateTime.Parse(row["backedon"].ToString());
results.Add(obj);
}
return(results);
}
public List <PartnerStatusLog> GetList(GetListParam param)
{
#region Parameters
var parameters = new List <OracleParameter>();
var whereCluase = new StringBuilder();
if (param != null)
{
if (!string.IsNullOrEmpty(param.PartnerId))
{
whereCluase.Append(" WHERE partner_id = :PartnerId");
var p = new OracleParameter {
ParameterName = "PartnerId", OracleDbType = OracleDbType.Varchar2, Value = param.PartnerId
};
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.CreatedByAccount > 0)
{
whereCluase.Append(whereCluase.Length > 0 ? " AND createdbyacc = :CreatedByAccount" : " WHERE createdbyacc = :CreatedByAccount");
var p = new OracleParameter {
ParameterName = "CreatedByAccount", OracleDbType = OracleDbType.Int32, Value = param.CreatedByAccount
};
parameters.Add(p);
}
if (param.PartnerAccount > 0)
{
whereCluase.Append(whereCluase.Length > 0 ? " AND partner_acc = :PartnerAccount" : " WHERE partner_acc = :PartnerAccount");
var p = new OracleParameter {
ParameterName = "PartnerAccount", OracleDbType = OracleDbType.Int32, Value = param.PartnerAccount
};
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);
}
}
}
#endregion
string strSql = $"select * from PARTNER_STATUS_LOG {whereCluase} order by log_id DESC";
DataTable masterDataTable;
masterDataTable = db.GetData(strSql, parameters);
if (masterDataTable == null)
{
return(null);
}
if (masterDataTable.Rows.Count == 0)
{
return(null);
}
var results = new List <PartnerStatusLog>();
foreach (DataRow row in masterDataTable.Rows)
{
var obj = ConvertDataRowToPartnerStatusLog(row);
results.Add(obj);
}
return(results);
}
