public List <PartnerStatusLog> GetLogByPartnerId(string id)
{
#region Parameters
var parameters = new List <OracleParameter> {
new OracleParameter {
ParameterName = "PartnerId", OracleDbType = OracleDbType.Varchar2, Value = id
}
};
#endregion
DataTable masterDataTable;
masterDataTable = db.GetData("select * from partner_status_log where partner_id=:PartnerId order by log_id", 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);
}
public List <ActivityMessage> GetList(string activityId, int messageId)
{
string whereClause = string.Empty;
#region Parameters
var parameters = new List <OracleParameter>();
if (!string.IsNullOrEmpty(activityId))
{
var activityIdParamenter = new OracleParameter {
ParameterName = "activityId", OracleDbType = OracleDbType.Varchar2, Value = activityId
};
whereClause = " WHERE act_id=:activityId ";
parameters.Add(activityIdParamenter);
}
if (messageId > 0)
{
var messageIdParamenter = new OracleParameter {
ParameterName = "messageId", OracleDbType = OracleDbType.Varchar2, Value = messageId
};
whereClause += string.IsNullOrEmpty(whereClause) ? " WHERE msg_id=:messageId " : " AND msg_id=:messageId ";
parameters.Add(messageIdParamenter);
}
#endregion
var masterDataTable = db.GetData("Select * from activity_message t " + whereClause + "order by msg_order" +
"", parameters);
if (masterDataTable == null)
{
return(null);
}
if (masterDataTable.Rows.Count == 0)
{
return(null);
}
var list = new List <ActivityMessage>();
foreach (DataRow row in masterDataTable.Rows)
{
var obj = new ActivityMessage();
var actId = row["act_id"] == DBNull.Value ? string.Empty : row["act_id"].ToString();
obj.Activity = new ActivityRepo(db, partnerManager).GetActivity(actId);
var msgId = row["msg_id"] == DBNull.Value ? 0 : int.Parse(row["msg_id"].ToString());
obj.MessageOrder = row["msg_order"] == DBNull.Value ? 0 : int.Parse(row["msg_order"].ToString());
obj.Message = new MessageTemplateRepo(db, partnerManager).GetSingle(msgId);
var sendingTime = row["sending_time"] == DBNull.Value ? string.Empty : row["sending_time"].ToString();
obj.SendingTime = new CommonCodeRepo(db).GetCodesById(sendingTime, "ActivityMessage.SendingTime");
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 partner = partnerManager.GetPartnerByAccount(createdAccount);
obj.CreatedBy.Id = row["createdby"] == DBNull.Value ? string.Empty : row["createdby"].ToString();
obj.CreatedBy.Name = partner.Name;
obj.CreatedBy.Account = partner.Account;
list.Add(obj);
}
return(list);
}
public Queue <SMSOut> GetPendingSMSQueue(int count = 100)
{
var masterDataTable = db.GetData("Select * from SMS_OUT t WHERE ROWNUM < " + count + " order by row_id", null);
if (masterDataTable == null)
{
return(null);
}
if (masterDataTable.Rows.Count == 0)
{
return(null);
}
var results = new Queue <SMSOut>();
foreach (DataRow row in masterDataTable.Rows)
{
var obj = new SMSOut();
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();
results.Enqueue(obj);
}
return(results);
}
public List <PFR> GetList(int Account, bool IncludeDates, DateTime StartDate, DateTime EndDate)
{
if (Account <= 0)
{
return(null);
}
#region Parameters
//var openingPFR = new PFR();
var whereCluase = string.Empty;
//if (StartDate > DateTime.MinValue && StartDate != null)
//{
// openingPFR = GetPeriodOpeningBalance(Account, StartDate);
//}
var parameters = BuildParametersList(Account, IncludeDates, StartDate, EndDate, ref whereCluase);
#endregion
var strSqlStatment = new StringBuilder();
strSqlStatment.Append($"select * from pfr_online {whereCluase} order by createdon");
DataTable masterDataTable;
masterDataTable = db.GetData(strSqlStatment.ToString(), parameters);
var results = new List <PFR>();
//if (openingPFR != null)
//{
// results.Add(openingPFR);
//}
if (masterDataTable == null)
{
return(results);
}
if (masterDataTable.Rows.Count == 0)
{
return(results);
}
foreach (DataRow row in masterDataTable.Rows)
{
var obj = ConvertDataRowToPFR(row);
results.Add(obj);
}
return(results);
}
public List <Notification> GetList(string receiver)
{
var whereClause = new StringBuilder();
#region Parameters
var parameters = new List <OracleParameter>();
if (!string.IsNullOrEmpty(receiver))
{
var activityIdParamenter = new OracleParameter {
ParameterName = "receiver", OracleDbType = OracleDbType.Varchar2, Value = receiver
};
whereClause.Append(" WHERE partner_id = :receiver ");
parameters.Add(activityIdParamenter);
}
whereClause.Append(!string.IsNullOrEmpty(whereClause.ToString()) ? " AND ROWNUM < 200 " : " WHERE ROWNUM < 200 ");
#endregion
var masterDataTable = db.GetData("Select * from NOTIFICATION t " + whereClause + " order by nf_id desc", parameters);
if (masterDataTable == null)
{
return(null);
}
if (masterDataTable.Rows.Count == 0)
{
return(null);
}
var list = new List <Notification>();
foreach (DataRow row in masterDataTable.Rows)
{
var obj = new Notification();
obj.Id = row["nf_id"] == DBNull.Value ? 0 : int.Parse(row["nf_id"].ToString());
obj.CreatedOn = row["createdon"] == DBNull.Value ? DateTime.MinValue : DateTime.Parse(row["createdon"].ToString());
var createdAccount = row["partner_acc"] == DBNull.Value ? -1 : int.Parse(row["partner_acc"].ToString());
if (createdAccount > 0)
{
var partner = partnerManager.GetPartnerByAccount(createdAccount);
obj.Partner.Id = partner.Id;
obj.Partner.Account = partner.Account;
obj.Partner.Name = partner.Name;
}
else
{
obj.Partner.Id = row["partner_id"] == DBNull.Value ? string.Empty : row["partner_id"].ToString();;
obj.Partner.Account = createdAccount;
obj.Partner.Name = string.Empty;
}
obj.Message = row["notify_msg"] == DBNull.Value ? string.Empty : row["notify_msg"].ToString();
obj.RefNo = row["ref_no"] == DBNull.Value ? -1 : int.Parse(row["ref_no"].ToString());
var activityId = row["act_id"] == DBNull.Value ? string.Empty : row["act_id"].ToString();
obj.Activity = new ActivityRepo(db, partnerManager).GetActivity(activityId, false);
list.Add(obj);
}
return(list);
}
public ApiIPBlacklist GetSingleOrDefault(string ip)
{
#region Parameters
var parameters = new List <OracleParameter>();
var whereCluase = new StringBuilder();
if (string.IsNullOrEmpty(ip))
{
return(null);
}
whereCluase.Append(" WHERE ip_address = :ipAddress");
var p = new OracleParameter {
ParameterName = "ipAddress", OracleDbType = OracleDbType.Varchar2, Value = ip
};
parameters.Add(p);
#endregion
string strSql = $"select * from api_ip_blacklist {whereCluase} order by createdon desc";
DataTable masterDataTable;
masterDataTable = _db.GetData(strSql, parameters);
if (masterDataTable == null)
{
return(null);
}
if (masterDataTable.Rows.Count == 0)
{
return(null);
}
DataRow row = masterDataTable.Rows[0];
var obj = new ApiIPBlacklist();
obj.IPAddress = row["ip_address"] == DBNull.Value ? string.Empty : row["ip_address"].ToString();
obj.CreatedOn = row["createdon"] == DBNull.Value ? DateTime.MinValue : DateTime.Parse(row["createdon"].ToString());
return(obj);
}
public List <MessageTemplate> GetAll(string NotInActivity = "")
{
DataTable masterDataTable;
if (NotInActivity == "")
{
masterDataTable = db.GetData("Select * from message_template t order by createdon", null);
}
else
{
masterDataTable = db.GetData("Select * from message_template t where not exists (select 1 from activity_message a where a.act_id='" + NotInActivity + "'"
+ "and a.msg_id = t.msg_id) order by createdon", null);
}
if (masterDataTable == null)
{
return(null);
}
if (masterDataTable.Rows.Count == 0)
{
return(null);
}
var results = new List <MessageTemplate>();
foreach (DataRow row in masterDataTable.Rows)
{
var obj = new MessageTemplate();
obj.Id = row["msg_id"] == DBNull.Value ? -1 : int.Parse(row["msg_id"].ToString());
obj.ToWho = row["towho"] == DBNull.Value ? -1 : int.Parse(row["towho"].ToString());
obj.Title = row["msg_name"] == DBNull.Value ? string.Empty : row["msg_name"].ToString();
obj.Message = row["msg_text"] == DBNull.Value ? string.Empty : row["msg_text"].ToString();
obj.CreatedOn = row["createdon"] == DBNull.Value ? DateTime.MinValue :DateTime.Parse(row["createdon"].ToString());
obj.LastUpdatedOn = row["lastupdateon"] == DBNull.Value ? DateTime.MinValue :DateTime.Parse(row["lastupdateon"].ToString());
var createdAccount = row["createdbyacc"] == DBNull.Value ? -1 : int.Parse(row["createdbyacc"].ToString());
var partner = partnerManager.GetPartnerByAccount(createdAccount);
obj.CreatedBy.Id = row["createdby"] == DBNull.Value ? string.Empty : row["createdby"].ToString();
obj.CreatedBy.Name = partner.Name;
obj.CreatedBy.Account = partner.Account;
results.Add(obj);
}
return(results);
}
public Queue <GrappedRecharge> GetPendingRechargeQueue(int queueNo, int count = 100)
{
var masterDataTable = db.GetData("Select * from v_pending_recharge_req t WHERE queue_no=" + queueNo + " AND ROWNUM < " + count + " order by dcreatedon", null);
if (masterDataTable == null)
{
return(null);
}
if (masterDataTable.Rows.Count == 0)
{
return(null);
}
var results = new Queue <GrappedRecharge>();
foreach (DataRow row in masterDataTable.Rows)
{
var obj = new GrappedRecharge();
obj.Id = row["row_id"] == DBNull.Value ? -1 : int.Parse(row["row_id"].ToString());
obj.SubscriberNo = row["subs_no"] == DBNull.Value ? string.Empty : row["subs_no"].ToString();
obj.MasterId = row["cl_id"] == DBNull.Value ? int.MinValue : int.Parse(row["cl_id"].ToString());
obj.Amount = row["amount"] == DBNull.Value ? 0 : double.Parse(row["amount"].ToString());
obj.PointOfSaleId = row["pos_id"] == DBNull.Value ? string.Empty : row["pos_id"].ToString();
obj.PointOfSaleAccount = row["pos_acc"] == DBNull.Value ? 0 : int.Parse(row["pos_acc"].ToString());
obj.AccessChannelId = row["access_channel"] == DBNull.Value ? string.Empty : row["access_channel"].ToString();
obj.Status = row["status"] == DBNull.Value ? 0 :int.Parse(row["status"].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.RefTransNo = row["ref_trans_no"] == DBNull.Value ? string.Empty : row["ref_trans_no"].ToString();
obj.RefTime = row["ref_time"] == DBNull.Value ? DateTime.MinValue :DateTime.Parse(row["ref_time"].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.Enqueue(obj);
}
return(results);
}
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 <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);
}
private Partner ConvertDataRowToPartner(DataRow dataRow)
{
if (dataRow == null)
{
return(null);
}
#region Convert to Partner Object
var partner = new Partner();
partner.Id = dataRow["partner_id"] == DBNull.Value ? string.Empty : dataRow["partner_id"].ToString();
partner.Account = dataRow["partner_acc"] == DBNull.Value ? -1 : int.Parse(dataRow["partner_acc"].ToString());
partner.Name = dataRow["partner_name"] == DBNull.Value ? string.Empty : dataRow["partner_name"].ToString();
partner.BrandName = dataRow["brandname"] == DBNull.Value ? string.Empty : dataRow["brandname"].ToString();
partner.Balance = dataRow["balance"] == DBNull.Value ? double.MinValue : double.Parse(dataRow["balance"].ToString());
partner.Reserved = dataRow["reserved"] == DBNull.Value ? double.MinValue : double.Parse(dataRow["reserved"].ToString());
partner.VerificationCodeNext = dataRow["verificationcodenext"] == DBNull.Value ? false : dataRow["verificationcodenext"].ToString() == "1" ? true : false;
partner.LockTime = dataRow["locktime"] == DBNull.Value ? DateTime.MinValue : DateTime.Parse(dataRow["locktime"].ToString());
partner.Role.Id = dataRow["roleid"] == DBNull.Value ? int.MinValue : int.Parse(dataRow["roleid"].ToString());
partner.PersonalId.Id = dataRow["id_no"] == DBNull.Value ? string.Empty : dataRow["id_no"].ToString();
partner.PersonalId.IdType.Id = dataRow["id_type"] == DBNull.Value ? int.MinValue : int.Parse(dataRow["id_type"].ToString());
partner.PersonalId.Place = dataRow["id_place"] == DBNull.Value ? string.Empty : dataRow["id_place"].ToString();
partner.PersonalId.Issued = dataRow["id_issued"] == DBNull.Value ? DateTime.MinValue : DateTime.Parse(dataRow["id_issued"].ToString());
partner.Status.Id = dataRow["status"] == DBNull.Value ? int.MinValue : int.Parse(dataRow["status"].ToString());
partner.StatusOn = dataRow["statuson"] == DBNull.Value ? DateTime.MinValue : DateTime.Parse(dataRow["statuson"].ToString());
partner.StatusBy.Id = dataRow["statusby"] == DBNull.Value ? string.Empty : dataRow["statusby"].ToString();
partner.CreatedOn = dataRow["createdon"] == DBNull.Value ? DateTime.MinValue : DateTime.Parse(dataRow["createdon"].ToString());
partner.CreatedBy.Id = dataRow["createdby"] == DBNull.Value ? string.Empty : dataRow["createdby"].ToString();
partner.Address.City.Id = dataRow["cityid"] == DBNull.Value ? int.MinValue : int.Parse(dataRow["cityid"].ToString());
partner.Address.District.Id = dataRow["districtid"] == DBNull.Value ? int.MinValue : int.Parse(dataRow["districtid"].ToString());
partner.WrongPwdAttempts = dataRow["WRONG_PWD_ATTEMPTS"] == DBNull.Value ? int.MinValue : int.Parse(dataRow["WRONG_PWD_ATTEMPTS"].ToString());
partner.Address.Street = dataRow["street"] == DBNull.Value ? string.Empty : dataRow["street"].ToString();
partner.Address.Zone = dataRow["zone"] == DBNull.Value ? string.Empty : dataRow["zone"].ToString();
partner.Address.ExtraInfo = dataRow["extra_address"] == DBNull.Value ? string.Empty : dataRow["extra_address"].ToString();
partner.PairMobile = dataRow["pair_mobile"] == DBNull.Value ? string.Empty : dataRow["pair_mobile"].ToString();
partner.ContactInfo.Mobile = dataRow["mobile"] == DBNull.Value ? string.Empty : dataRow["mobile"].ToString();
partner.ContactInfo.Fixed = dataRow["fixed"] == DBNull.Value ? string.Empty : dataRow["fixed"].ToString();
partner.ContactInfo.Fax = dataRow["fax"] == DBNull.Value ? string.Empty : dataRow["fax"].ToString();
partner.ContactInfo.Email = dataRow["email"] == DBNull.Value ? string.Empty : dataRow["email"].ToString();
partner.Pwd = dataRow["pwd"] == DBNull.Value ? string.Empty : dataRow["pwd"].ToString();
partner.Extra = dataRow["extra"] == DBNull.Value ? string.Empty : dataRow["extra"].ToString();
partner.IPAddress = dataRow["ip_address"] == DBNull.Value ? string.Empty : dataRow["ip_address"].ToString();
partner.LastLoginOn = dataRow["last_login"] == DBNull.Value ? DateTime.MinValue : DateTime.Parse(dataRow["last_login"].ToString());
// get sub objects
// Role
partner.Role = new RoleRepo(db, partnerActivity).GetRole(partner.Role.Id);
partner.PersonalId.IdType = new IdTypeRepo(db).GetIdType(partner.PersonalId.IdType.Id);
partner.Status = new PartnerStatusRepo(db).GetStatus(partner.Status.Id);
partner.Address.City = new CityRepo(db).GetCity(partner.Address.City.Id);
partner.Address.District = new DistrictRepo(db).GetDistrict(partner.Address.District.Id);
var statusBy = dataRow["STATUSBY"] == DBNull.Value ? string.Empty : dataRow["STATUSBY"].ToString();
var statusUserparameter = new List <OracleParameter> {
new OracleParameter {
ParameterName = "statusBy", OracleDbType = OracleDbType.Varchar2, Value = statusBy
},
};
var statusByDataTable = db.GetData("select * from partner where partner_id=:statusBy", statusUserparameter);
if (statusByDataTable != null)
{
DataRow dr = statusByDataTable.Rows[0];
partner.StatusBy.Id = dr["partner_id"] == null ? string.Empty : dr["partner_id"].ToString();
partner.StatusBy.Name = dr["partner_name"] == null ? string.Empty : dr["partner_name"].ToString();
}
var createdBy = dataRow["CREATEDBY"] == DBNull.Value ? string.Empty : dataRow["CREATEDBY"].ToString();
var createUserparameter = new List <OracleParameter> {
new OracleParameter {
ParameterName = "createBy", OracleDbType = OracleDbType.Varchar2, Value = createdBy
},
};
var createByDataTable = db.GetData("select * from partner where partner_id=:createBy", createUserparameter);
if (createByDataTable != null)
{
DataRow dr = createByDataTable.Rows[0];
partner.CreatedBy.Id = dr["partner_id"] == null ? string.Empty : dr["partner_id"].ToString();
partner.CreatedBy.Name = dr["partner_name"] == null ? string.Empty : dr["partner_name"].ToString();
}
var refPartnerId = dataRow["REF_PARTNER"] == DBNull.Value ? string.Empty : dataRow["REF_PARTNER"].ToString();
var refPartparameter = new List <OracleParameter> {
new OracleParameter {
ParameterName = "RefPart", OracleDbType = OracleDbType.Varchar2, Value = refPartnerId
},
};
var refDataTable = db.GetData("select * from partner where partner_id=:RefPart", refPartparameter);
if (statusByDataTable != null)
{
DataRow dr = refDataTable.Rows[0];
partner.RefPartner.Id = dr["partner_id"] == null ? string.Empty : dr["partner_id"].ToString();
partner.RefPartner.Name = dr["partner_name"] == null ? string.Empty : dr["partner_name"].ToString();
}
/////////////////////////
#endregion
return(partner);
}
public List <CollectionRpt> GetStatReport(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 += $"{Environment.NewLine} للرقم {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 += $"{Environment.NewLine} للمشترك {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 += $"{Environment.NewLine} للفترة من {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 += $"{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,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 = db.GetData(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 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);
}