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); }