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