public IEnumerable<DPSViewEditResult> GetDPSViewEditRecordsExport(DateTime? StartDate, DateTime? EndDate, string PortfolioOwner, string Responsibility, string Account, string GUID)
{
DBFactory db;
SqlDataReader rdr;
List<DPSViewEditResult> data = null;
try
{
db = new DBFactory();
rdr = db.ExecuteReader("MSI_spGetDPSViewEditRecordsExport", new SqlParameter("@StartDate", StartDate),
new SqlParameter("@EndDate", EndDate), new SqlParameter("@PortfolioOwner", PortfolioOwner),
new SqlParameter("@Responsibility", Responsibility), new SqlParameter("@Account", Account), new SqlParameter("@GUID", GUID));
data = new List<DPSViewEditResult>();
DPSViewEditResult record;
while (rdr.Read())
{
#region Data field Assignment
record = new DPSViewEditResult();
record.AcctName = rdr["AcctName"].ToString();
record.PIMSAcct = rdr["PIMSAcct"].ToString();
record.OriginalAcct = rdr["OriginalAcct"].ToString();
record.TransDate = Convert.ToDateTime(rdr["TransDate"]);
record.DateRec = Convert.ToDateTime(rdr["DateRec"]);
record.GUID = rdr["GUID"].ToString();
record.CheckNumber = rdr["CheckNumber"].ToString();
record.PmtType = rdr["PmtType"].ToString();
record.TransSource = rdr["TransSource"].ToString();
record.CurrentResp = rdr["CurrentResp"].ToString();
record.OurCheckNumber = rdr["OurCheckNumber"].ToString();
record.Uploaded = rdr["Uploaded"].ToString();
record.Portfolio = rdr["Portfolio"].ToString();
record.ID = Convert.ToInt32(rdr["ID"].ToString());
record.Amount = rdr["Amount"] == DBNull.Value ? Convert.ToDecimal(0.0) : Convert.ToDecimal(rdr["Amount"]);
record.NetPayment = rdr["NetPayment"] == DBNull.Value ? Convert.ToDecimal(0.0) : Convert.ToDecimal(rdr["NetPayment"]);
#endregion
#region Data Modification
if (record.Amount == Convert.ToDecimal(0.0))
{
record.Amount = null;
}
if (record.TransDate.ToString() == "1/1/1900 12:00:00 AM")
{
record.TransDate = null;
}
if (record.DateRec.ToString() == "1/1/1900 12:00:00 AM")
{
record.DateRec = null;
}
if (record.NetPayment == Convert.ToDecimal(0.0))
{
record.NetPayment = null;
}
if (record.Uploaded.ToUpper() == "TRUE")
{
record.Uploaded = "Yes";
}
else
{
record.Uploaded = "No";
}
#endregion
//Add record
data.Add(record);
}
//Close the datareader
rdr.Close();
}
catch (Exception ex)
{
throw new Exception("Exception in DataQueries.GetDPSViewEditRecordsExport:" + ex.Message);
}
return data.AsEnumerable<DPSViewEditResult>();
}
public IEnumerable<MSI_Port_InvestmentsTrans> GetPortfolioInvestmentsSummary(string productCode, string userId, bool isOriginal)
{
string thisMethod = string.Format("{0}.{1}", thisClass, System.Reflection.MethodBase.GetCurrentMethod().Name);
string logMessage = string.Format("{0}|Method incoming parameters productCode={1}, userId={2}", thisMethod, productCode, userId);
LogHelper.Info(logMessage);
MSI_Port_InvestmentsTrans investmentTransaction = null;
DBFactory db;
List<MSI_Port_InvestmentsTrans> investmentTransactions = null;
System.Data.DataSet ds;
try
{
db = new DBFactory();
ds = db.ExecuteDataset("MSI_sp_GetPortfolioInvestmentsSummary", "PurchaseInvestmentsSummary", new SqlParameter("@productCode", productCode), new SqlParameter("@userId", userId), new SqlParameter("@isOriginal", isOriginal));
if (ds.Tables["PurchaseInvestmentsSummary"].Rows.Count > 0)
{
investmentTransactions = new List<MSI_Port_InvestmentsTrans>();
foreach (System.Data.DataRow dr in ds.Tables["PurchaseInvestmentsSummary"].Rows)
{
investmentTransaction = new MSI_Port_InvestmentsTrans();
investmentTransaction.ID = int.Parse(dr["ID"].ToString());
investmentTransaction.Portfolio_ = dr["Portfolio#"].ToString();
if (dr["ProfitShare_before"] != DBNull.Value)
investmentTransaction.ProfitShare_before = Convert.ToDouble(dr["ProfitShare_before"].ToString());
if (dr["ProfitShare_after"] != DBNull.Value)
investmentTransaction.ProfitShare_after = Convert.ToDouble(dr["ProfitShare_after"].ToString());
if (dr["SalesPrice"] != DBNull.Value)
investmentTransaction.SalesPrice = Convert.ToDecimal(dr["SalesPrice"].ToString());
if (dr["Notes"] != DBNull.Value)
investmentTransaction.Notes = dr["Notes"].ToString();
if (dr["Interest"] != DBNull.Value)
investmentTransaction.Interest = Convert.ToDouble( dr["Interest"].ToString());
investmentTransaction.Inv_AgencyName = dr["Inv_AgencyName"].ToString();
DateTime notNullDt;
if (DateTime.TryParse(dr["CreatedDate"].ToString(), out notNullDt))
investmentTransaction.CreatedDate = notNullDt;
if (DateTime.TryParse(dr["UpdatedDate"].ToString(), out notNullDt))
investmentTransaction.UpdatedDate = notNullDt;
investmentTransaction.CreatedBy = dr["CreatedBy"].ToString();
investmentTransaction.UpdatedBy = dr["UpdatedBy"].ToString();
investmentTransaction.TransType = dr["TransType"].ToString();
investmentTransactions.Add(investmentTransaction);
}
}
}
catch (Exception ex)
{
ErrorLogHelper.Error(logMessage, ex);
}
return investmentTransactions.AsEnumerable<MSI_Port_InvestmentsTrans>();
}
public IEnumerable<MSI_Debtor> GetDebtors(string accountNumber)
{
MSI_Debtor debtor = null;
DBFactory db;
List<MSI_Debtor> debtors = null;
System.Data.DataSet ds;
try
{
db = new DBFactory();
ds = db.ExecuteDataset("MSI_spGetDebtors", "Debtors", new SqlParameter("@pimsAccountNumber", accountNumber));
if (ds.Tables["Debtors"].Rows.Count > 0)
{
debtors = new List<MSI_Debtor>();
foreach (System.Data.DataRow dr in ds.Tables["Debtors"].Rows)
{
debtor = new MSI_Debtor();
debtor.Account = dr["ACCOUNT"].ToString();
debtor.FirstName = dr["FirstName"].ToString();
debtor.LastName = dr["LastName"].ToString();
debtor.Address1 = dr["ADDRESS1"].ToString();
debtor.Address2 = dr["ADDRESS2"].ToString();
debtor.City = dr["CITY"].ToString();
debtor.State = dr["STATE"].ToString();
debtor.Zip = dr["ZIP_CODE"].ToString();
string ssn = dr["SSN"].ToString().Trim();
debtor.LastFourSSN = ssn.Substring(7, 4);
debtor.MobilePhone = dr["PHONE_CELL"].ToString();
debtor.HomePhone = dr["PHONE_HOME"].ToString();
debtor.WorkPhone = dr["PHONE_WORK"].ToString();
debtor.DOB = dr["DOB"].ToString();
debtor.DebtCurrentBalance = dr["DebtCurrentBalance"].ToString();
debtor.DebtPurchaseBalance = dr["DebtorPurchaseBalance"].ToString();
debtor.CreditorName = dr["CreditorName"].ToString();
debtors.Add(debtor);
}
}
}
catch (Exception ex)
{
}
return debtors.AsEnumerable<MSI_Debtor>();
}
public IEnumerable<LookUp> GetDistinctResponsibility()
{
DBFactory db;
SqlDataReader rdr;
List<LookUp> data = null;
try
{
db = new DBFactory();
rdr = db.ExecuteReader("MSI_sp_GetDistinctResponsibility");
data = new List<LookUp>();
LookUp record;
while (rdr.Read())
{
record = new LookUp(rdr["RESPONSIBILITY"].ToString(), rdr["RESPONSIBILITY"].ToString());
data.Add(record);
}
rdr.Close();
}
catch (Exception ex)
{
throw new Exception("Exception in DataQueries.GetDistinctResponsibility:" + ex.Message);
}
return data.AsQueryable<LookUp>();
}
public IEnumerable<RecallViewEditResult> GetRecallViewEditRecordsExport(DateTime? StartDate, DateTime? EndDate, string PortfolioOwner, string Responsibility, string Account, string GUID)
{
DBFactory db;
SqlDataReader rdr;
List<RecallViewEditResult> data = null;
try
{
db = new DBFactory();
rdr = db.ExecuteReader("MSI_spGetRecallViewEditRecordsExport", new SqlParameter("@StartDate", StartDate),
new SqlParameter("@EndDate", EndDate), new SqlParameter("@PortfolioOwner", PortfolioOwner),
new SqlParameter("@Responsibility", Responsibility), new SqlParameter("@Account", Account), new SqlParameter("@GUID", GUID));
data = new List<RecallViewEditResult>();
RecallViewEditResult record;
while (rdr.Read())
{
record = new RecallViewEditResult();
record.AcctName = rdr["AcctName"].ToString();
record.PIMSAcct = rdr["PIMSAcct"].ToString();
record.OrigAcct = rdr["OrigAcct"].ToString();
record.DateAcctClosed = Convert.ToDateTime(rdr["DateAcctClosed"]);
record.DateNoteSent = Convert.ToDateTime(rdr["DateNoteSent"]);
record.Date = Convert.ToDateTime(rdr["Date"]);
record.UploadedDate = Convert.ToDateTime(rdr["UploadedDate"]);
record.CurrentResp = rdr["CurrentResp"].ToString();
record.RecallReason = rdr["RecallReason"].ToString();
record.NewStatus = rdr["NewStatus"].ToString();
record.NewResp = rdr["NewResp"].ToString();
record.CheckNumber = rdr["CheckNumber"].ToString();
record.Explanation = rdr["Explanation"].ToString();
record.Seller = rdr["Seller"].ToString();
record.SellerCheck = rdr["SellerCheck"].ToString();
record.AcctName = rdr["AcctName"].ToString();
record.Putback = rdr["Putback"].ToString();
record.CostBasis = rdr["CostBasis"] == DBNull.Value ? Convert.ToDecimal(0.0) : Convert.ToDecimal(rdr["CostBasis"]);
record.SalesBasis = rdr["SalesBasis"] == DBNull.Value ? Convert.ToDecimal(0.0) : Convert.ToDecimal(rdr["SalesBasis"]);
record.AmtReceivable = rdr["AmtReceivable"] == DBNull.Value ? Convert.ToDecimal(0.0) : Convert.ToDecimal(rdr["AmtReceivable"]);
record.AmtPayable = rdr["AmtPayable"] == DBNull.Value ? Convert.ToDecimal(0.0) : Convert.ToDecimal(rdr["AmtPayable"]);
record.GUID = rdr["GUID"].ToString();
record.Portfolio = rdr["Portfolio"].ToString();
record.ID = Convert.ToInt32(rdr["ID"].ToString());
record.FaceValueofAcct = rdr["FaceValueofAcct"] == DBNull.Value ? Convert.ToDecimal(0.0) : Convert.ToDecimal(rdr["FaceValueofAcct"]);
if (record.FaceValueofAcct == Convert.ToDecimal(0.0))
{
record.FaceValueofAcct = null;
}
if (record.Putback.ToUpper() == "TRUE")
{
record.Putback = "Yes";
}
else
{
record.Putback = "No";
}
if (record.CostBasis == Convert.ToDecimal(0.0))
{
record.CostBasis = null;
}
if (record.SalesBasis == Convert.ToDecimal(0.0))
{
record.SalesBasis = null;
}
if (record.AmtReceivable == Convert.ToDecimal(0.0))
{
record.AmtReceivable = null;
}
if (record.AmtPayable == Convert.ToDecimal(0.0))
{
record.AmtPayable = null;
}
if (record.DateAcctClosed.ToString() == "1/1/1900 12:00:00 AM")
{
record.DateAcctClosed = null;
}
if (record.DateNoteSent.ToString() == "1/1/1900 12:00:00 AM")
{
record.DateNoteSent = null;
}
if (record.Date.ToString() == "1/1/1900 12:00:00 AM")
{
record.Date = null;
}
if (record.UploadedDate.ToString() == "1/1/1900 12:00:00 AM")
{
record.UploadedDate = null;
}
data.Add(record);
}
//Close the datareader
rdr.Close();
}
catch (Exception ex)
{
throw new Exception("Exception in DataQueries.GetRecallViewEditRecordsExport:" + ex.Message);
}
return data.AsEnumerable<RecallViewEditResult>();
}
public IQueryable<SearchResult> GetSearchResults(string name)
{
DBFactory db;
SqlDataReader rdr;
List<SearchResult> data = null;
try
{
db = new DBFactory();
rdr = db.ExecuteReader("MSI_spBasicSearch", new SqlParameter("@name", name));
data = new List<SearchResult>();
SearchResult record;
while (rdr.Read())
{
record = new SearchResult();
CreateSearchResult(rdr, record);
data.Add(record);
}
}
catch (Exception ex)
{
throw new Exception("Exception in DataQueries.GetSearchResults:" + ex.Message);
}
return data.AsQueryable<SearchResult>();
}
public IEnumerable<DPSViewEditResult> GetDPSReportRecords(string Export, string reportName)
{
DBFactory db;
SqlDataReader rdr;
List<DPSViewEditResult> data = null;
try
{
db = new DBFactory();
string reportProcedureName = "";
switch (reportName)
{
case "DPSCheckDetail": reportProcedureName = "MSI_spGetDPSCheckDetailReportData";
break;
case "DPSPayable": reportProcedureName = "MSI_spGetDPSPayableReportData";
break;
case "DPSPaidByOurCheck": reportProcedureName = "MSI_spGetDPSPaidByOurCheckReportData";
break;
}
rdr = db.ExecuteReader(reportProcedureName, new SqlParameter("@Export", Export));
data = new List<DPSViewEditResult>();
DPSViewEditResult record;
while (rdr.Read())
{
record = new DPSViewEditResult();
//record.OriginalAcct = rdr["OriginalAcct"].ToString();
//record.TransDate = Convert.ToDateTime(rdr["TransDate"]);
//record.AcctName = rdr["AcctName"].ToString();
//record.Portfolio = rdr["Portfolio"].ToString();
//record.PIMSAcct = rdr["PIMSAcct"].ToString();
//record.Amount = rdr["Amount"] == DBNull.Value ? Convert.ToDecimal(0.0) : Convert.ToDecimal(rdr["Amount"]);
//record.NetPayment = rdr["NetPayment"] == DBNull.Value ? Convert.ToDecimal(0.0) : Convert.ToDecimal(rdr["NetPayment"]);
//record.PmtType = rdr["PmtType"].ToString();
//record.CurrentResp = rdr["CurrentResp"].ToString();
//record.GUID = rdr["GUID"].ToString();
//record.ID = Convert.ToInt32(rdr["ID"].ToString());
//if (Export != null)
//{
record.AcctName = rdr["AcctName"].ToString();
record.PIMSAcct = rdr["PIMSAcct"].ToString();
record.OriginalAcct = rdr["OriginalAcct"].ToString();
record.TransDate = Convert.ToDateTime(rdr["TransDate"]);
record.DateRec = Convert.ToDateTime(rdr["DateRec"]);
record.TransCode = rdr["TransCode"].ToString();
record.GUID = rdr["GUID"].ToString();
record.CheckNumber = rdr["CheckNumber"].ToString();
record.PmtType = rdr["PmtType"].ToString();
record.TransSource = rdr["TransSource"].ToString();
record.CurrentResp = rdr["CurrentResp"].ToString();
record.OurCheckNumber = rdr["OurCheckNumber"].ToString();
record.Uploaded = rdr["Uploaded"].ToString();
record.Portfolio = rdr["Portfolio"].ToString();
record.ID = Convert.ToInt32(rdr["ID"].ToString());
record.Amount = rdr["Amount"] == DBNull.Value ? Convert.ToDecimal(0.0) : Convert.ToDecimal(rdr["Amount"]);
record.NetPayment = rdr["NetPayment"] == DBNull.Value ? Convert.ToDecimal(0.0) : Convert.ToDecimal(rdr["NetPayment"]);
record.TotalAmount = rdr["TotalAmount"] == DBNull.Value ? Convert.ToDecimal(0.0) : Convert.ToDecimal(rdr["TotalAmount"]);
if (record.TotalAmount == Convert.ToDecimal(0.0))
{
record.TotalAmount = null;
}
//}
if (record.Amount == Convert.ToDecimal(0.0))
{
record.Amount = null;
}
if (record.NetPayment == Convert.ToDecimal(0.0))
{
record.NetPayment = null;
}
if (record.TransDate.ToString() == "1/1/1900 12:00:00 AM")
{
record.TransDate = null;
}
if (record.DateRec.ToString() == "1/1/1900 12:00:00 AM")
{
record.DateRec = null;
}
data.Add(record);
}
//Close the datareader
rdr.Close();
}
catch (Exception ex)
{
throw new Exception("Exception in DataQueries.GetDPSReportRecords:" + ex.Message);
}
return data.AsEnumerable<DPSViewEditResult>();
}
public IEnumerable<Purchases> GetPurchases(DateTime? startDate, DateTime? endDate, string productCode)
{
DBFactory db;
SqlDataReader rdr;
List<Purchases> data = null;
try
{
db = new DBFactory();
rdr = db.ExecuteReader("MSI_spPurchasesReport", new SqlParameter("@startDate", startDate), new SqlParameter("@endDate", endDate), new SqlParameter("@prodCode", productCode));
data = new List<Purchases>();
Purchases record;
while (rdr.Read())
{
record = new Purchases();
record.ACCOUNT = rdr["ACCOUNT"].ToString();
record.PRODUCT_CODE = rdr["PRODUCT_CODE"].ToString();
record.PurchaseDate = Convert.ToDateTime(rdr["PurchaseDate"]);
record.purchaseprice = rdr["purchaseprice"] == DBNull.Value ? Convert.ToDecimal(0.0) : Convert.ToDecimal(rdr["purchaseprice"]);
record.OriginalBalance = rdr["OriginalBalance"] == DBNull.Value ? Convert.ToDecimal(0.0) : Convert.ToDecimal(rdr["OriginalBalance"]);
record.Seller = rdr["Seller"].ToString();
if (record.purchaseprice == Convert.ToDecimal(0.0))
{
record.purchaseprice = null;
}
if (record.OriginalBalance == Convert.ToDecimal(0.0))
{
record.OriginalBalance = null;
}
data.Add(record);
}
//Close the datareader
rdr.Close();
}
catch (Exception ex)
{
throw new Exception("Exception in DataQueries.GetPurchases:" + ex.Message);
}
return data.AsEnumerable<Purchases>();
}
public IEnumerable<MSI_Debtor> GetDebtors(string accountNumber)
{
string thisMethod = string.Format("{0}.{1}", thisClass, System.Reflection.MethodBase.GetCurrentMethod().Name);
string logMessage = string.Format("{0}|Method incoming parameters accountNumber={1}", thisMethod, accountNumber);
LogHelper.Info(logMessage);
MSI_Debtor debtor = null;
DBFactory db;
List<MSI_Debtor> debtors = null;
System.Data.DataSet ds;
try
{
db = new DBFactory();
ds = db.ExecuteDataset("MSI_spGetDebtors", "Debtors", new SqlParameter("@pimsAccountNumber", accountNumber));
if (ds.Tables["Debtors"].Rows.Count > 0)
{
debtors = new List<MSI_Debtor>();
foreach (System.Data.DataRow dr in ds.Tables["Debtors"].Rows)
{
debtor = new MSI_Debtor();
debtor.Account = dr["ACCOUNT"].ToString();
debtor.FirstName = dr["FirstName"].ToString();
debtor.LastName = dr["LastName"].ToString();
debtor.Address1 = dr["ADDRESS1"].ToString();
debtor.Address2 = dr["ADDRESS2"].ToString();
debtor.City = dr["CITY"].ToString();
debtor.State = dr["STATE"].ToString();
debtor.Zip = dr["ZIP_CODE"].ToString();
string ssn = dr["SSN"].ToString().Trim();
debtor.LastFourSSN = ssn.Substring(7, 4);
debtor.MobilePhone = dr["PHONE_CELL"].ToString();
debtor.HomePhone = dr["PHONE_HOME"].ToString();
debtor.WorkPhone = dr["PHONE_WORK"].ToString();
debtor.DOB = dr["DOB"].ToString();
debtor.DebtCurrentBalance = dr["DebtCurrentBalance"].ToString();
debtor.DebtPurchaseBalance = dr["DebtorPurchaseBalance"].ToString();
debtor.CreditorName = dr["CreditorName"].ToString();
debtors.Add(debtor);
}
}
}
catch (Exception ex)
{
ErrorLogHelper.Error(logMessage, ex);
}
return debtors.AsEnumerable<MSI_Debtor>();
}
public IEnumerable<LookUp> GetDistinctResponsibility()
{
string thisMethod = string.Format("{0}.{1}", thisClass, System.Reflection.MethodBase.GetCurrentMethod().Name);
string logMessage = string.Format("{0}", thisMethod);
LogHelper.Info(logMessage);
DBFactory db;
SqlDataReader rdr;
List<LookUp> data = null;
try
{
db = new DBFactory();
rdr = db.ExecuteReader("MSI_sp_GetDistinctResponsibility");
data = new List<LookUp>();
LookUp record;
while (rdr.Read())
{
record = new LookUp(rdr["RESPONSIBILITY"].ToString(), rdr["RESPONSIBILITY"].ToString());
data.Add(record);
}
rdr.Close();
}
catch (Exception ex)
{
ErrorLogHelper.Error(logMessage, ex);
}
return data.AsQueryable<LookUp>();
}
public IEnumerable<RecallViewEditResult> GetRecallsReceivableReportRecords(string Export, string reportName)
{
DBFactory db;
SqlDataReader rdr;
List<RecallViewEditResult> data = null;
try
{
db = new DBFactory();
string reportProcedureName = "";
switch (reportName)
{
case "RecallsReceivable": reportProcedureName = "MSI_spGetRecallsReceivableReportData";
break;
case "RecallsInvoiceLookup": reportProcedureName = "MSI_spGetRecallsInvoiceLookupReportData";
break;
case "RecallsSellerCheckLookup": reportProcedureName = "MSI_spGetRecallsSellerCheckLookupReportData";
break;
case "RecallsPayable": reportProcedureName = "MSI_spGetRecallsPayableReportData";
break;
case "RecallsPaidByOurCheck": reportProcedureName = "MSI_spGetRecallsPaidByOurCheckReportData";
break;
}
rdr = db.ExecuteReader(reportProcedureName, new SqlParameter("@Export", Export));
data = new List<RecallViewEditResult>();
RecallViewEditResult record;
while (rdr.Read())
{
record = new RecallViewEditResult();
record.TotalAmount = rdr["TotalAmount"] == DBNull.Value ? Convert.ToDecimal(0.0) : Convert.ToDecimal(rdr["TotalAmount"]);
if (record.TotalAmount == Convert.ToDecimal(0.0))
{
record.TotalAmount = null;
}
record.OrigAcct = rdr["OrigAcct"].ToString();
record.Date = Convert.ToDateTime(rdr["Date"]);
record.AcctName = rdr["AcctName"].ToString();
record.Portfolio = rdr["Portfolio"].ToString();
record.PIMSAcct = rdr["PIMSAcct"].ToString();
record.FaceValueofAcct = rdr["FaceValueofAcct"] == DBNull.Value ? Convert.ToDecimal(0.0) : Convert.ToDecimal(rdr["FaceValueofAcct"]);
if (record.FaceValueofAcct == Convert.ToDecimal(0.0))
{
record.FaceValueofAcct = null;
}
record.CostBasis = rdr["CostBasis"] == DBNull.Value ? Convert.ToDecimal(0.0) : Convert.ToDecimal(rdr["CostBasis"]);
record.AmtReceivable = rdr["AmtReceivable"] == DBNull.Value ? Convert.ToDecimal(0.0) : Convert.ToDecimal(rdr["AmtReceivable"]);
record.Invoice = rdr["Invoice"].ToString();
record.ID = Convert.ToInt32(rdr["ID"].ToString());
record.SellerCheck = rdr["SellerCheck"].ToString();
record.SalesBasis = rdr["SalesBasis"] == DBNull.Value ? Convert.ToDecimal(0.0) : Convert.ToDecimal(rdr["SalesBasis"]);
record.AmtPayable = rdr["AmtPayable"] == DBNull.Value ? Convert.ToDecimal(0.0) : Convert.ToDecimal(rdr["AmtPayable"]);
record.CheckNumber = rdr["CheckNumber"].ToString();
if (Export != null)
{
record.OrigAcct = rdr["OrigAcct"].ToString();
record.Date = Convert.ToDateTime(rdr["Date"]);
record.AcctName = rdr["AcctName"].ToString();
record.Portfolio = rdr["Portfolio"].ToString();
record.PIMSAcct = rdr["PIMSAcct"].ToString();
record.FaceValueofAcct = rdr["FaceValueofAcct"] == DBNull.Value ? Convert.ToDecimal(0.0) : Convert.ToDecimal(rdr["FaceValueofAcct"]);
if (record.FaceValueofAcct == Convert.ToDecimal(0.0))
{
record.FaceValueofAcct = null;
}
record.CostBasis = rdr["CostBasis"] == DBNull.Value ? Convert.ToDecimal(0.0) : Convert.ToDecimal(rdr["CostBasis"]);
record.AmtReceivable = rdr["AmtReceivable"] == DBNull.Value ? Convert.ToDecimal(0.0) : Convert.ToDecimal(rdr["AmtReceivable"]);
record.Invoice = rdr["Invoice"].ToString();
record.CurrentResp = rdr["CurrentResp"].ToString();
record.RecallReason = rdr["RecallReason"].ToString();
record.DateNoteSent = Convert.ToDateTime(rdr["DateNoteSent"]);
record.NewStatus = rdr["NewStatus"].ToString();
record.NewResp = rdr["NewResp"].ToString();
record.UploadedDate = Convert.ToDateTime(rdr["UploadedDate"]);
record.CheckNumber = rdr["CheckNumber"].ToString();
record.Explanation = rdr["Explanation"].ToString();
record.SalesBasis = rdr["SalesBasis"] == DBNull.Value ? Convert.ToDecimal(0.0) : Convert.ToDecimal(rdr["SalesBasis"]);
record.Seller = rdr["Seller"].ToString();
record.SellerCheck = rdr["SellerCheck"].ToString();
record.Putback = rdr["Putback"].ToString();
record.DateAcctClosed = Convert.ToDateTime(rdr["DateAcctClosed"]);
record.AmtReceivable = rdr["AmtReceivable"] == DBNull.Value ? Convert.ToDecimal(0.0) : Convert.ToDecimal(rdr["AmtReceivable"]);
record.AmtPayable = rdr["AmtPayable"] == DBNull.Value ? Convert.ToDecimal(0.0) : Convert.ToDecimal(rdr["AmtPayable"]);
record.GUID = rdr["GUID"].ToString();
if (record.Putback.ToUpper() == "TRUE")
{
record.Putback = "Yes";
}
else
{
record.Putback = "No";
}
if (record.CostBasis == Convert.ToDecimal(0.0))
{
record.CostBasis = null;
}
if (record.SalesBasis == Convert.ToDecimal(0.0))
{
record.SalesBasis = null;
}
if (record.AmtReceivable == Convert.ToDecimal(0.0))
{
record.AmtReceivable = null;
}
if (record.AmtPayable == Convert.ToDecimal(0.0))
{
record.AmtPayable = null;
}
if (record.DateAcctClosed.ToString() == "1/1/1900 12:00:00 AM")
{
record.DateAcctClosed = null;
}
if (record.DateNoteSent.ToString() == "1/1/1900 12:00:00 AM")
{
record.DateNoteSent = null;
}
if (record.Date.ToString() == "1/1/1900 12:00:00 AM")
{
record.Date = null;
}
if (record.UploadedDate.ToString() == "1/1/1900 12:00:00 AM")
{
record.UploadedDate = null;
}
}
data.Add(record);
}
//Close the datareader
rdr.Close();
}
catch (Exception ex)
{
throw new Exception("Exception in DataQueries.GetRecallsReceivableReportRecords:" + ex.Message);
}
return data.AsEnumerable<RecallViewEditResult>();
}
public IEnumerable<MSI_Port_SalesTrans_Original> GetPortfolioSalesSummary(string productCode, string userId)
{
string thisMethod = string.Format("{0}.{1}", thisClass, System.Reflection.MethodBase.GetCurrentMethod().Name);
string logMessage = string.Format("{0}|Method incoming parameters productCode={1}, userId={2}", thisMethod, productCode, userId);
LogHelper.Info(logMessage);
MSI_Port_SalesTrans_Original salesTransaction = null;
DBFactory db;
List<MSI_Port_SalesTrans_Original> salesTransactions = null;
System.Data.DataSet ds;
try
{
db = new DBFactory();
ds = db.ExecuteDataset("MSI_sp_GetPortfolioSalesSummary", "PurchaseSalesSummary", new SqlParameter("@productCode", productCode), new SqlParameter("@userId", userId));
if (ds.Tables["PurchaseSalesSummary"].Rows.Count > 0)
{
salesTransactions = new List<MSI_Port_SalesTrans_Original>();
foreach (System.Data.DataRow dr in ds.Tables["PurchaseSalesSummary"].Rows)
{
salesTransaction = new MSI_Port_SalesTrans_Original();
salesTransaction.ID = int.Parse(dr["ID"].ToString());
salesTransaction.Portfolio_ = dr["Portfolio#"].ToString();
salesTransaction.Buyer = dr["Buyer"].ToString();
if (dr["SalesBasis"] != DBNull.Value)
salesTransaction.SalesBasis = Convert.ToDouble(dr["SalesBasis"].ToString());
if (dr["FaceValue"] != DBNull.Value)
salesTransaction.FaceValue = Convert.ToDecimal(dr["FaceValue"].ToString());
if (dr["Cut-OffDate"] != DBNull.Value)
salesTransaction.Cut_OffDate = DateTime.Parse(dr["Cut-OffDate"].ToString());
if (dr["#ofAccts"] != DBNull.Value)
salesTransaction.C_ofAccts = Convert.ToDouble(dr["#ofAccts"].ToString());
if (dr["SalesPrice"] != DBNull.Value)
salesTransaction.SalesPrice = Convert.ToDecimal(dr["SalesPrice"].ToString());
DateTime closingDate;
if (DateTime.TryParse(dr["ClosingDate"].ToString(), out closingDate))
salesTransaction.ClosingDate = closingDate;
salesTransaction.Lender = dr["Lender"].ToString();
int putbackTermDays;
if (int.TryParse(dr["PutbackTerm"].ToString(), out putbackTermDays))
salesTransaction.PutbackTerm_days_ = putbackTermDays;
DateTime putbackDeadLine;
if (DateTime.TryParse(dr["PutbackDeadLine"].ToString(), out putbackDeadLine))
salesTransaction.PutbackDeadline = putbackDeadLine;
salesTransaction.Notes = dr["Notes"].ToString();
DateTime notNullDt;
if (DateTime.TryParse(dr["CreatedDate"].ToString(), out notNullDt))
salesTransaction.CreatedDate = notNullDt;
if (DateTime.TryParse(dr["UpdatedDate"].ToString(), out notNullDt))
salesTransaction.UpdatedDate = notNullDt;
salesTransaction.CreatedBy = dr["CreatedBy"].ToString();
salesTransaction.UpdatedBy = dr["UpdatedBy"].ToString();
salesTransactions.Add(salesTransaction);
}
}
}
catch (Exception ex)
{
ErrorLogHelper.Error(logMessage, ex);
}
return salesTransactions.AsEnumerable<MSI_Port_SalesTrans_Original>();
}
public MSI_Port_Acq_Original GetPortfolioPurchaseSummary(string productCode)
{
string thisMethod = string.Format("{0}.{1}", thisClass, System.Reflection.MethodBase.GetCurrentMethod().Name);
string logMessage = string.Format("{0}|Method incoming parameters productCode={1}", thisMethod, productCode);
LogHelper.Info(logMessage);
MSI_Port_Acq_Original portfolio = null;
DBFactory db;
System.Data.DataSet ds;
System.Data.DataRow dr;
try
{
portfolio = new MSI_Port_Acq_Original();
db = new DBFactory();
ds = db.ExecuteDataset("MSI_sp_GetPortfolioPurchaseSummary", "PurchaseSummary", new SqlParameter("@productCode", productCode));
dr = ds.Tables["PurchaseSummary"].Rows[0];
portfolio.Portfolio_ = dr["Portfolio#"].ToString();
portfolio.Company = dr["Company"].ToString();
portfolio.Seller = dr["Seller"].ToString();
portfolio.CostBasis = Convert.ToDouble(dr["CostBasis"].ToString());
portfolio.Face = Convert.ToDecimal(dr["Face"].ToString());
portfolio.Cut_OffDate = DateTime.Parse(dr["Cut-OffDate"].ToString());
portfolio.C_ofAccts = Convert.ToDouble(dr["#ofAccts"].ToString());
portfolio.PurchasePrice = Convert.ToDecimal(dr["PurchasePrice"].ToString());
DateTime closingDate;
if (DateTime.TryParse(dr["ClosingDate"].ToString(), out closingDate))
portfolio.ClosingDate = closingDate;
portfolio.Lender_FileDescription = dr["Lender/FileDescription"].ToString();
int putbackTermDays;
if (int.TryParse(dr["PutBackTerm"].ToString(), out putbackTermDays))
portfolio.PutbackTerm__days_ = putbackTermDays;
DateTime putbackDeadLine;
if (DateTime.TryParse(dr["PutbackDeadLine"].ToString(), out putbackDeadLine))
portfolio.PutbackDeadline = putbackDeadLine;
portfolio.Notes = dr["Notes"].ToString();
int resaleId;
if (int.TryParse(dr["ResaleRestrictionId"].ToString(), out resaleId))
portfolio.ResaleRestrictionId = resaleId;
portfolio.CreatedBy = dr["CreatedBy"].ToString();
portfolio.CreatedDate = DateTime.Parse(dr["CreatedDate"].ToString());
portfolio.UpdatedBy = dr["UpdatedBy"].ToString();
portfolio.UpdatedDate = DateTime.Parse(dr["UpdatedDate"].ToString());
}
catch (Exception ex)
{
ErrorLogHelper.Error(logMessage, ex);
}
return portfolio;
}
public IEnumerable<PortfolioTransactions> GetPortfolioTransactionsReports(string productCode)
{
DBFactory db;
SqlDataReader rdr;
List<PortfolioTransactions> data = null;
try
{
db = new DBFactory();
rdr = db.ExecuteReader("MSI_spPortfolioTransactionsReport", new SqlParameter("@prodCode", productCode));
data = new List<PortfolioTransactions>();
PortfolioTransactions record;
while (rdr.Read())
{
record = new PortfolioTransactions();
record.ACCOUNT = rdr["ACCOUNT"].ToString();
record.PRODUCT_CODE = rdr["PRODUCT_CODE"].ToString();
record.Tran_Date = Convert.ToDateTime(rdr["Tran_Date"]);
record.Amount = rdr["Amount"] == DBNull.Value ? Convert.ToDecimal(0.0) : Convert.ToDecimal(rdr["Amount"]);
record.TRAN_CODE = rdr["TRAN_CODE"].ToString();
record.TRAN_SOURCE = rdr["TRAN_SOURCE"].ToString();
record.Responsibility = rdr["Responsibility"].ToString();
if (record.Amount == Convert.ToDecimal(0.0))
{
record.Amount = null;
}
data.Add(record);
}
//Close the datareader
rdr.Close();
}
catch (Exception ex)
{
throw new Exception("Exception in DataQueries.GetPortfolioTransactionsReports:" + ex.Message);
}
return data.AsEnumerable<PortfolioTransactions>();
}
public IEnumerable<MSI_MediaTracker> GetExistingUploadMediaDetails(string accountNumber)
{
DBFactory db;
SqlDataReader rdr;
List<MSI_MediaTracker> data = null;
try
{
db = new DBFactory();
rdr = db.ExecuteReader("MSI_spGetMediaTrackerByAccountNumber", new SqlParameter("@account", accountNumber));
data = new List<MSI_MediaTracker>();
MSI_MediaTracker record;
while (rdr.Read())
{
record = new MSI_MediaTracker();
record.Id = Convert.ToInt32(rdr["Id"].ToString());
record.Account = rdr["Account"].ToString();
record.OriginalAccount = rdr["OriginalAccount"].ToString();
record.MediaTypeId = Convert.ToInt32(rdr["MediaTypeId"].ToString());
record.MediaDocuments = rdr["MediaDocuments"].ToString();
record.TypeConstraints = rdr["TypeConstraints"].ToString();
data.Add(record);
}
//Close the datareader
rdr.Close();
}
catch (Exception ex)
{
throw new Exception("Exception in DataQueries.GetExistingUploadMediaDetails:" + ex.Message);
}
return data.AsEnumerable<MSI_MediaTracker>();
}
public IEnumerable<PortfolioPieRpt> GetPortfolioWorkStationDescription()
{
DBFactory db;
SqlDataReader rdr;
List<PortfolioPieRpt> data = null;
try
{
db = new DBFactory();
rdr = db.ExecuteReader("sp_PortfolioWorkStationDescriptionReport");
data = new List<PortfolioPieRpt>();
while (rdr.Read())
{
data.Add(new PortfolioPieRpt { Count = rdr["Count"].ToString(), KeyText = rdr["WorkStatusDescription"].ToString() });
}
}
catch (Exception ex)
{
throw new Exception("Exception in DataQueries.GetPortfolioWorkStationDescription:" + ex.Message);
}
return data.AsEnumerable<PortfolioPieRpt>();
}
public IEnumerable<ComplianceViewResult> GetComplianceReportRecords(string AgencyId, string reportType)
{
DBFactory db;
SqlDataReader rdr = null;
string reportName = "";
List<ComplianceViewResult> data = null;
try
{
db = new DBFactory();
switch (reportType)
{
case "NCRA": reportName = "MSI_spGetComplianceNCRAReportData";
break;
case "ORP": reportName = "MSI_spGetComplianceORPReportData";
break;
case "RC": reportName = "MSI_spGetComplianceRCReportData";
break;
case "SOA": reportName = "MSI_spGetComplianceSOAReportData";
break;
case "AAI": reportName = "MSI_spGetComplianceAAIReportData";
break;
case "NCP": reportName = "MSI_spGetComplianceNCPReportData";
break;
}
rdr = db.ExecuteReader(reportName, new SqlParameter("@AgencyId", AgencyId));
data = new List<ComplianceViewResult>();
ComplianceViewResult record;
while (rdr.Read())
{
record = new ComplianceViewResult();
record.AgencyId = rdr["AgencyId"].ToString();
record.ComplaintID = rdr["ComPlaintId"].ToString();
record.Account = rdr["PIMSAccount"].ToString();
record.LastName = rdr["LastName"].ToString();
record.FirstName = rdr["FirstName"].ToString();
record.LastFourSSN = rdr["LastFourSSN"].ToString();
if (rdr["ComplaintDate"] != DBNull.Value)
record.ComplaintDate = Convert.ToDateTime(rdr["ComplaintDate"]);
if (rdr["MoreInfoRequestedDate"] != DBNull.Value)
record.MoreInfoRequestedDate = Convert.ToDateTime(rdr["MoreInfoRequestedDate"]);
if (rdr["ComplaintSubmittedDate"] != DBNull.Value)
record.ComplaintSubmittedDate = Convert.ToDateTime(rdr["ComplaintSubmittedDate"]);
if (rdr["AgencyResponseToDebtorDate"] != DBNull.Value)
record.AgencyResponseToDebtorDate = Convert.ToDateTime(rdr["AgencyResponseToDebtorDate"]);
if (rdr["TotalResponseTimeDays"] != DBNull.Value)
record.TotalResponseTimeDays = rdr["TotalResponseTimeDays"].ToString();
if (record.ComplaintDate.ToString() == "1/1/1900 12:00:00 AM")
{
record.ComplaintDate = null;
}
if (record.MoreInfoRequestedDate.ToString() == "1/1/1900 12:00:00 AM")
{
record.MoreInfoRequestedDate = null;
}
if (record.ComplaintSubmittedDate.ToString() == "1/1/1900 12:00:00 AM")
{
record.ComplaintSubmittedDate = null;
}
if (record.AgencyResponseToDebtorDate.ToString() == "1/1/1900 12:00:00 AM")
{
record.AgencyResponseToDebtorDate = null;
}
data.Add(record);
}
//Close the datareader
rdr.Close();
}
catch (Exception ex)
{
throw new Exception("Exception in DataQueries.ReportType:" + reportType + "GetComplianceReportRecords:" + ex.Message);
}
return data.AsEnumerable<ComplianceViewResult>();
}
public IEnumerable<ComplianceViewResult> GetComplianceReportRecordsExport(string AgencyId, string reportType)
{
DBFactory db;
SqlDataReader rdr;
List<ComplianceViewResult> data = null;
string reportName = "";
try
{
db = new DBFactory();
switch (reportType)
{
case "NCRA": reportName = "MSI_spGetComplianceNCRAReportDataExport";
break;
case "ORP": reportName = "MSI_spGetComplianceORPReportDataExport";
break;
case "RC": reportName = "MSI_spGetComplianceRCReportDataExport";
break;
case "SOA": reportName = "MSI_spGetComplianceSOAReportDataExport";
break;
case "AAI": reportName = "MSI_spGetComplianceAAIReportDataExport";
break;
case "NCP": reportName = "MSI_spGetComplianceNCPReportDataExport";
break;
}
//rdr = db.ExecuteReader("MSI_spGetComplianceNCRAReportDataExport", new SqlParameter("@AgencyId", AgencyId));
rdr = db.ExecuteReader(reportName, new SqlParameter("@AgencyId", AgencyId));
data = new List<ComplianceViewResult>();
ComplianceViewResult record;
while (rdr.Read())
{
record = new ComplianceViewResult();
record.AgencyId = rdr["AgencyId"].ToString();
record.AgencyName = rdr["AgencyName"].ToString();
record.LastName = rdr["LastName"].ToString();
record.FirstName = rdr["FirstName"].ToString();
record.Account = rdr["PIMSAccount"].ToString();
record.ComplaintID = rdr["ComPlaintId"].ToString();
record.HomePhone = rdr["HomePhone"].ToString();
record.WorkPhone = rdr["WorkPhone"].ToString();
record.LastFourSSN = rdr["LastFourSSN"].ToString();
record.MobilePhone = rdr["MobilePhone"].ToString();
record.Address = rdr["Address"].ToString();
record.City = rdr["City"].ToString();
record.State = rdr["State"].ToString();
record.Zip = rdr["Zip"].ToString();
record.CreditorName = rdr["CreditorName"].ToString();
record.DebtorIdentityVerified = rdr["DebtorIdentityVerifiedText"].ToString();
record.ContactMethod = rdr["ContactMethod"].ToString();
record.ContactTime = rdr["ContactTime"].ToString();
record.DebtProduct = rdr["DebtProduct"].ToString();
if (rdr["DebtPurchaseBalance"].ToString() != "")
record.DebtPurchaseBalance = rdr["DebtPurchaseBalance"] == DBNull.Value ? Convert.ToDecimal(0.0) : Convert.ToDecimal(rdr["DebtPurchaseBalance"]);
if (rdr["DebtCurrentBalance"].ToString() != "")
record.DebtCurrentBalance = rdr["DebtCurrentBalance"] == DBNull.Value ? Convert.ToDecimal(0.0) : Convert.ToDecimal(rdr["DebtCurrentBalance"]);
record.DisputeDebt = rdr["DisputeDebtText"].ToString();
record.DisputeDebtAmount = rdr["DisputeDebtAmountText"].ToString();
record.DisputeDebtDueDate = rdr["DisputeDebtDueDateText"].ToString();
record.ComplaintID = rdr["ComplaintId"].ToString();
if (rdr["ComplaintDate"] != DBNull.Value)
record.ComplaintDate = Convert.ToDateTime(rdr["ComplaintDate"]);
record.ComplaintReceivedByMethod = rdr["ComplaintReceivedByMethod"].ToString();
record.ComplaintIssue = rdr["ComplaintIssue"].ToString();
record.ComplaintNotes = rdr["ComplaintNotes"].ToString();
record.ComplaintSubmitedToAgency = rdr["ComplaintSubmitedToAgencyText"].ToString();
if (rdr["ComplaintSubmitedToAgencyDate"] != DBNull.Value)
record.ComplaintSubmitedToAgencyDate = Convert.ToDateTime(rdr["ComplaintSubmitedToAgencyDate"]);
record.MoreInfoReqdFromDebtor = rdr["MoreInfoReqdFromDebtorText"].ToString();
if (rdr["MoreInfoRequestedDate"] != DBNull.Value)
record.MoreInfoRequestedDate = Convert.ToDateTime(rdr["MoreInfoRequestedDate"]);
record.MoreInfoRequested = rdr["MoreInfoRequested"].ToString();
record.MoreInfoReceivedFromDebtor = rdr["MoreInfoReceivedFromDebtorText"].ToString();
if (rdr["MoreInfoReceivedDate"] != DBNull.Value)
record.MoreInfoReceivedDate = Convert.ToDateTime(rdr["MoreInfoReceivedDate"]);
record.MoreInfoReceived = rdr["MoreInfoReceived"].ToString();
record.ComplaintSubmittedToOwner = rdr["ComplaintSubmittedToOwnerText"].ToString();
if (rdr["ComplaintSubmittedDate"] != DBNull.Value)
record.ComplaintSubmittedDate = Convert.ToDateTime(rdr["ComplaintSubmittedDate"]);
record.TimeToSubmitDays = rdr["TimeToSubmitDays"].ToString();
record.MoreInfoFromAgency = rdr["MoreInfoFromAgencyText"].ToString();
if (rdr["MoreInfoFromAgencyRequestedDate"] != DBNull.Value)
record.MoreInfoFromAgencyRequestedDate = Convert.ToDateTime(rdr["MoreInfoFromAgencyRequestedDate"]);
record.MoreInfoFromAgencyRequested = rdr["MoreInfoFromAgencyRequested"].ToString();
record.MoreInfoFromAgencyReceived = rdr["MoreInfoFromAgencyReceived"].ToString();
if (rdr["MoreInfoFromAgencyReceivedDate"] != DBNull.Value)
record.MoreInfoFromAgencyReceivedDate = Convert.ToDateTime(rdr["MoreInfoFromAgencyReceivedDate"]);
record.OwnerResponse = rdr["OwnerResponse"].ToString();
if (rdr["OwnerResponseDate"] != DBNull.Value)
record.OwnerResponseDate = Convert.ToDateTime(rdr["OwnerResponseDate"]);
record.OwnerResponseDays = rdr["OwnerResponseDays"].ToString();
if (rdr["AgencyResponseToDebtorDate"] != DBNull.Value)
record.AgencyResponseToDebtorDate = Convert.ToDateTime(rdr["AgencyResponseToDebtorDate"]);
record.TotalResponseTimeDays = rdr["TotalResponseTimeDays"].ToString();
record.DebtorAgree = rdr["DebtorAgreeText"].ToString();
record.NeedFurtherAction = rdr["NeedFurtherActionText"].ToString();
record.FinalActionStep = rdr["FinalActionStep"].ToString();
record.IsViewedByOwner = rdr["IsViewedByOwnerText"].ToString();
record.IsViewedByAgency = rdr["IsViewedByAgencyText"].ToString();
record.CreatedBy = rdr["CreatedBy"].ToString();
if (record.DebtPurchaseBalance == Convert.ToDecimal(0.0))
{
record.DebtPurchaseBalance = null;
}
if (record.DebtCurrentBalance == Convert.ToDecimal(0.0))
{
record.DebtCurrentBalance = null;
}
if (record.ComplaintDate.ToString() == "1/1/1900 12:00:00 AM")
{
record.ComplaintDate = null;
}
if (record.ComplaintSubmitedToAgencyDate.ToString() == "1/1/1900 12:00:00 AM")
{
record.ComplaintSubmitedToAgencyDate = null;
}
if (record.MoreInfoRequestedDate.ToString() == "1/1/1900 12:00:00 AM")
{
record.MoreInfoRequestedDate = null;
}
if (record.MoreInfoReceivedDate.ToString() == "1/1/1900 12:00:00 AM")
{
record.MoreInfoReceivedDate = null;
}
if (record.ComplaintSubmittedDate.ToString() == "1/1/1900 12:00:00 AM")
{
record.ComplaintSubmittedDate = null;
}
if (record.MoreInfoFromAgencyRequestedDate.ToString() == "1/1/1900 12:00:00 AM")
{
record.MoreInfoFromAgencyRequestedDate = null;
}
if (record.MoreInfoFromAgencyReceivedDate.ToString() == "1/1/1900 12:00:00 AM")
{
record.MoreInfoFromAgencyReceivedDate = null;
}
if (record.OwnerResponseDate.ToString() == "1/1/1900 12:00:00 AM")
{
record.OwnerResponseDate = null;
}
if (record.AgencyResponseToDebtorDate.ToString() == "1/1/1900 12:00:00 AM")
{
record.AgencyResponseToDebtorDate = null;
}
data.Add(record);
}
//Close the datareader
rdr.Close();
}
catch (Exception ex)
{
throw new Exception("Exception in DataQueries.GetComplianceNCRAReportRecords:" + ex.Message);
}
return data.AsEnumerable<ComplianceViewResult>();
}
public IEnumerable<MoneyViewEditResult> GetMoneyViewEditRecords(Decimal? Amount, int Source)
{
DBFactory db;
SqlDataReader rdr;
List<MoneyViewEditResult> data = null;
try
{
db = new DBFactory();
rdr = db.ExecuteReader("MSI_spGetMoneyViewEditRecords", new SqlParameter("@Amount", Amount), new SqlParameter("@Source", Source.ToString()));
data = new List<MoneyViewEditResult>();
MoneyViewEditResult record;
while (rdr.Read())
{
record = new MoneyViewEditResult();
record.MID = Convert.ToInt32(rdr["MID"].ToString());
record.FName = rdr["FName"].ToString();
record.LName = rdr["LName"].ToString();
record.SText = rdr["SText"].ToString();
record.Amount = rdr["Amount"] == DBNull.Value ? Convert.ToDecimal(0.0) : Convert.ToDecimal(rdr["Amount"]);
if (record.Amount == Convert.ToDecimal(0.0))
{
record.Amount = null;
}
data.Add(record);
}
//Close the datareader
rdr.Close();
}
catch (Exception ex)
{
throw new Exception("Exception in DataQueries.GetMoneyViewEditRecords:" + ex.Message);
}
return data.AsEnumerable<MoneyViewEditResult>();
}
public IEnumerable<Sales> GetSales(DateTime? startDate, DateTime? endDate, string productCode)
{
DBFactory db;
SqlDataReader rdr;
List<Sales> data = null;
try
{
db = new DBFactory();
rdr = db.ExecuteReader("MSI_spSalesReport", new SqlParameter("@startDate", startDate), new SqlParameter("@endDate", endDate), new SqlParameter("@prodCode", productCode));
data = new List<Sales>();
Sales record;
while (rdr.Read())
{
record = new Sales();
record.ACCOUNT = rdr["ACCOUNT"].ToString();
record.PRODUCT_CODE = rdr["PRODUCT_CODE"].ToString();
record.SoldDate = Convert.ToDateTime(rdr["SoldDate"]);
record.FaceValue = rdr["FaceValue"] == DBNull.Value ? Convert.ToDecimal(0.0) : Convert.ToDecimal(rdr["FaceValue"]);
record.SalesPrice = rdr["SalesPrice"] == DBNull.Value ? Convert.ToDecimal(0.0) : Convert.ToDecimal(rdr["SalesPrice"]);
record.Responsibility = rdr["Responsibility"].ToString();
if (record.FaceValue == Convert.ToDecimal(0.0))
{
record.FaceValue = null;
}
if (record.SalesPrice == Convert.ToDecimal(0.0))
{
record.SalesPrice = null;
}
data.Add(record);
}
//Close the datareader
rdr.Close();
}
catch (Exception ex)
{
throw new Exception("Exception in DataQueries.GetSales:" + ex.Message);
}
return data.AsEnumerable<Sales>();
}
public IEnumerable<PeopleViewEditResult> GetPeopleViewEditRecords(string FirstName, string LastName)
{
DBFactory db;
SqlDataReader rdr;
List<PeopleViewEditResult> data = null;
try
{
db = new DBFactory();
rdr = db.ExecuteReader("MSI_spGetPeopleViewEditRecords", new SqlParameter("@FirstName", FirstName), new SqlParameter("@LastName", LastName));
data = new List<PeopleViewEditResult>();
PeopleViewEditResult record;
while (rdr.Read())
{
record = new PeopleViewEditResult();
record.PID = Convert.ToInt32(rdr["PID"].ToString());
record.FName = rdr["FName"].ToString();
record.LName = rdr["LName"].ToString();
record.Address = rdr["Address"].ToString();
record.CreatedAt = Convert.ToDateTime(rdr["CreatedAt"]);
data.Add(record);
}
//Close the datareader
rdr.Close();
}
catch (Exception ex)
{
throw new Exception("Exception in DataQueries.GetPeopleViewEditRecords:" + ex.Message);
}
return data.AsEnumerable<PeopleViewEditResult>();
}
public IQueryable<SearchResult> GetSearchResults(string account, string originator, string seller, string investor)
{
DBFactory db;
SqlDataReader rdr;
List<SearchResult> data = null;
object _account, _originator, _seller, _investor = null;
if (string.IsNullOrEmpty(account))
_account = DBNull.Value;
else
_account = account;
if (string.IsNullOrEmpty(originator))
_originator = DBNull.Value;
else
_originator = originator;
if (string.IsNullOrEmpty(seller))
_seller = DBNull.Value;
else
_seller = seller;
if (string.IsNullOrEmpty(investor))
_investor = DBNull.Value;
else
_investor = investor;
try
{
db = new DBFactory();
rdr = db.ExecuteReader("MSI_spAdvanceSearch",
new SqlParameter("@accountNumber", _account),
new SqlParameter("@originator", _originator),
new SqlParameter("@seller", _seller),
new SqlParameter("@ssnFourDigits", _investor));
data = new List<SearchResult>();
SearchResult record;
while (rdr.Read())
{
record = new SearchResult();
CreateSearchResult(rdr, record);
data.Add(record);
}
}
catch (Exception ex)
{
throw new Exception("Exception in DataQueries.GetSearchResults:" + ex.Message);
}
return data.AsQueryable<SearchResult>();
}
public MSI_Port_Acq_Original GetPortfolioPurchaseSummary(string productCode)
{
MSI_Port_Acq_Original portfolio = null;
DBFactory db;
System.Data.DataSet ds;
System.Data.DataRow dr;
try
{
portfolio = new MSI_Port_Acq_Original();
db = new DBFactory();
ds = db.ExecuteDataset("MSI_sp_GetPortfolioPurchaseSummary", "PurchaseSummary", new SqlParameter("@productCode", productCode));
dr = ds.Tables["PurchaseSummary"].Rows[0];
portfolio.Portfolio_ = dr["Portfolio#"].ToString();
portfolio.Company = dr["Company"].ToString();
portfolio.Seller = dr["Seller"].ToString();
portfolio.CostBasis = Convert.ToDouble(dr["CostBasis"].ToString());
portfolio.Face = Convert.ToDecimal(dr["Face"].ToString());
portfolio.Cut_OffDate = DateTime.Parse(dr["Cut-OffDate"].ToString());
portfolio.C_ofAccts = Convert.ToDouble(dr["#ofAccts"].ToString());
portfolio.PurchasePrice = Convert.ToDecimal(dr["PurchasePrice"].ToString());
DateTime closingDate;
if (DateTime.TryParse(dr["ClosingDate"].ToString(), out closingDate))
portfolio.ClosingDate = closingDate;
portfolio.Lender_FileDescription = dr["Lender/FileDescription"].ToString();
int putbackTermDays;
if (int.TryParse(dr["PutBackTerm"].ToString(), out putbackTermDays))
portfolio.PutbackTerm__days_ = putbackTermDays;
DateTime putbackDeadLine;
if (DateTime.TryParse(dr["PutbackDeadLine"].ToString(), out putbackDeadLine))
portfolio.PutbackDeadline = putbackDeadLine;
portfolio.Notes = dr["Notes"].ToString();
int resaleId;
if (int.TryParse(dr["ResaleRestrictionId"].ToString(), out resaleId))
portfolio.ResaleRestrictionId = resaleId;
}
catch (Exception ex)
{
}
return portfolio;
}
public IEnumerable<LookUp> GetDistinctProductCodes()
{
DBFactory db;
SqlDataReader rdr;
List<LookUp> data = null;
try
{
db = new DBFactory();
rdr = db.ExecuteReader("MSI_sp_GetDistinctProductCode");
data = new List<LookUp>();
LookUp record;
while (rdr.Read())
{
record = new LookUp(rdr["Product_Code"].ToString(), rdr["Product_Code"].ToString());
data.Add(record);
}
rdr.Close();
}
catch (Exception ex)
{
throw new Exception("Exception in DataQueries.GetSearchResults:" + ex.Message);
}
return data.AsQueryable<LookUp>();
}
public IEnumerable<MSI_Port_SalesTrans_Original> GetPortfolioSalesSummary(string productCode)
{
MSI_Port_SalesTrans_Original salesTransaction = null;
DBFactory db;
List<MSI_Port_SalesTrans_Original> salesTransactions = null;
System.Data.DataSet ds;
try
{
db = new DBFactory();
ds = db.ExecuteDataset("MSI_sp_GetPortfolioSalesSummary", "PurchaseSalesSummary", new SqlParameter("@productCode", productCode));
if (ds.Tables["PurchaseSalesSummary"].Rows.Count > 0)
{
salesTransactions = new List<MSI_Port_SalesTrans_Original>();
foreach (System.Data.DataRow dr in ds.Tables["PurchaseSalesSummary"].Rows)
{
salesTransaction = new MSI_Port_SalesTrans_Original();
salesTransaction.ID = int.Parse(dr["ID"].ToString());
salesTransaction.Portfolio_ = dr["Portfolio#"].ToString();
salesTransaction.Buyer = dr["Buyer"].ToString();
if (dr["SalesBasis"] != DBNull.Value)
salesTransaction.SalesBasis = Convert.ToDouble(dr["SalesBasis"].ToString());
if (dr["FaceValue"] != DBNull.Value)
salesTransaction.FaceValue = Convert.ToDecimal(dr["FaceValue"].ToString());
if (dr["Cut-OffDate"] != DBNull.Value)
salesTransaction.Cut_OffDate = DateTime.Parse(dr["Cut-OffDate"].ToString());
if (dr["#ofAccts"] != DBNull.Value)
salesTransaction.C_ofAccts = Convert.ToDouble(dr["#ofAccts"].ToString());
if (dr["SalesPrice"] != DBNull.Value)
salesTransaction.SalesPrice = Convert.ToDecimal(dr["SalesPrice"].ToString());
DateTime closingDate;
if (DateTime.TryParse(dr["ClosingDate"].ToString(), out closingDate))
salesTransaction.ClosingDate = closingDate;
salesTransaction.Lender = dr["Lender"].ToString();
int putbackTermDays;
if (int.TryParse(dr["PutbackTerm"].ToString(), out putbackTermDays))
salesTransaction.PutbackTerm_days_ = putbackTermDays;
DateTime putbackDeadLine;
if (DateTime.TryParse(dr["PutbackDeadLine"].ToString(), out putbackDeadLine))
salesTransaction.PutbackDeadline = putbackDeadLine;
salesTransaction.Notes = dr["Notes"].ToString();
salesTransactions.Add(salesTransaction);
}
}
}
catch (Exception ex)
{
}
return salesTransactions.AsEnumerable<MSI_Port_SalesTrans_Original>();
}
public IEnumerable<DPSViewEditResult> GetDPSViewEditRecords(DateTime? StartDate, DateTime? EndDate, string PortfolioOwner, string Responsibility, string Account, string GUID)
{
DBFactory db;
SqlDataReader rdr;
List<DPSViewEditResult> data = null;
try
{
db = new DBFactory();
rdr = db.ExecuteReader("MSI_spGetDPSViewEditRecords", new SqlParameter("@StartDate", StartDate),
new SqlParameter("@EndDate", EndDate), new SqlParameter("@PortfolioOwner", PortfolioOwner),
new SqlParameter("@Responsibility", Responsibility), new SqlParameter("@Account", Account), new SqlParameter("@GUID", GUID));
data = new List<DPSViewEditResult>();
DPSViewEditResult record;
while (rdr.Read())
{
record = new DPSViewEditResult();
record.AcctName = rdr["AcctName"].ToString();
record.PIMSAcct = rdr["PIMSAcct"].ToString();
record.OriginalAcct = rdr["OriginalAcct"].ToString();
record.TransDate = Convert.ToDateTime(rdr["TransDate"]);
record.DateRec = Convert.ToDateTime(rdr["DateRec"]);
record.GUID = rdr["GUID"].ToString();
record.Portfolio = rdr["Portfolio"].ToString();
record.ID = Convert.ToInt32(rdr["ID"].ToString());
record.Amount = rdr["Amount"] == DBNull.Value ? Convert.ToDecimal(0.0) : Convert.ToDecimal(rdr["Amount"]);
if (record.Amount == Convert.ToDecimal(0.0))
{
record.Amount = null;
}
if (record.TransDate.ToString() == "1/1/1900 12:00:00 AM")
{
record.TransDate = null;
}
if (record.DateRec.ToString() == "1/1/1900 12:00:00 AM")
{
record.DateRec = null;
}
data.Add(record);
}
//Close the datareader
rdr.Close();
}
catch (Exception ex)
{
throw new Exception("Exception in DataQueries.GetDPSViewEditRecords:" + ex.Message);
}
return data.AsEnumerable<DPSViewEditResult>();
}
public IEnumerable<PortfolioSummary> GetPortfolioSummaryReports(string description)
{
DBFactory db;
SqlDataReader rdr;
List<PortfolioSummary> data = null;
try
{
db = new DBFactory();
rdr = db.ExecuteReader("MSI_spPortfolioSummary", new SqlParameter("@description", description));
data = new List<PortfolioSummary>();
PortfolioSummary record;
while (rdr.Read())
{
record = new PortfolioSummary();
record.Description = rdr["Description"].ToString();
record.Type = rdr["Type"].ToString();
record.Accounts = Convert.ToInt32(rdr["Accounts"]);
record.Balance = rdr["Balance"] == DBNull.Value ? Convert.ToDecimal(0.0) : Convert.ToDecimal(rdr["Balance"]);
record.ShortDescription = rdr["ShortDescription"].ToString();
record.Seller = rdr["Seller"].ToString();
record.Status = rdr["Status"].ToString();
if (record.Balance == Convert.ToDecimal(0.0))
{
record.Balance = null;
}
data.Add(record);
}
//Close the datareader
rdr.Close();
}
catch (Exception ex)
{
throw new Exception("Exception in DataQueries.GetPortfolioSummaryReports:" + ex.Message);
}
return data.AsEnumerable<PortfolioSummary>();
}
public IEnumerable<MediaViewEditResult> GetMediaNotReceivedReportRecords(string Export)
{
DBFactory db;
SqlDataReader rdr;
List<MediaViewEditResult> data = null;
try
{
db = new DBFactory();
rdr = db.ExecuteReader("MSI_spGetMediaNotReceivedReportData", new SqlParameter("@Export", Export));
data = new List<MediaViewEditResult>();
MediaViewEditResult record;
while (rdr.Read())
{
record = new MediaViewEditResult();
record.OrigAcct = rdr["OrigAcct"].ToString();
record.PIMSAcct = rdr["PIMSAcct"].ToString();
record.AcctName = rdr["AcctName"].ToString();
record.Portfolio = rdr["Portfolio"].ToString();
record.OriginalLender = rdr["OriginalLender"].ToString();
record.ID = Convert.ToInt32(rdr["ID"].ToString());
if (Export != null)
{
record.OpenDate = Convert.ToDateTime(rdr["OpenDate"]);
record.CODate = Convert.ToDateTime(rdr["CODate"]);
record.OrderDate = Convert.ToDateTime(rdr["OrderDate"]);
record.GUID = rdr["GUID"].ToString();
record.Portfolio = rdr["Portfolio"].ToString();
record.CompanyRequesting = rdr["CompanyRequesting"].ToString();
record.SSN = rdr["SSN"].ToString();
record.IsApplication = rdr["IsApplication"].ToString();
record.IsAffidavitIssuer = rdr["IsAffidavitIssuer"].ToString();
record.IsAffidavitSeller = rdr["IsAffidavitSeller"].ToString();
record.IsUnavailable = rdr["IsUnavailable"].ToString();
record.IsClosed = rdr["IsClosed"].ToString();
record.Explanation = rdr["Explanation"].ToString();
record.MediaTypeReceived = rdr["MediaTypeReceived"].ToString();
record.Notes = rdr["Notes"].ToString();
record.SellerInvoice = rdr["SellerInvoice"].ToString();
record.BuyerCheck = rdr["BuyerCheck"].ToString();
record.OurCheck = rdr["OurCheck"].ToString();
record.Seller = rdr["Seller"].ToString();
record.OrderNumber = rdr["OrderNumber"].ToString();
record.OurInvoice = rdr["OurInvoice"].ToString();
record.StmtsFrom = Convert.ToDateTime(rdr["StmtsFrom"]);
record.StmtsTo = Convert.ToDateTime(rdr["StmtsTo"]);
record.DateSubmitted = Convert.ToDateTime(rdr["DateSubmitted"]);
record.DateConfirmed = Convert.ToDateTime(rdr["DateConfirmed"]);
record.DateSellerPaid = Convert.ToDateTime(rdr["DateSellerPaid"]);
record.DateReceived = Convert.ToDateTime(rdr["DateReceived"]);
record.UnavailableDate = Convert.ToDateTime(rdr["UnavailableDate"]);
record.DatePayRec = Convert.ToDateTime(rdr["DatePayRec"]);
record.ID = Convert.ToInt32(rdr["ID"].ToString());
record.SellerFee = rdr["SellerFee"] == DBNull.Value ? Convert.ToDecimal(0.0) : Convert.ToDecimal(rdr["SellerFee"]);
record.OurFee = rdr["OurFee"] == DBNull.Value ? Convert.ToDecimal(0.0) : Convert.ToDecimal(rdr["OurFee"]);
if (record.SellerFee == Convert.ToDecimal(0.0))
{
record.SellerFee = null;
}
if (record.OurFee == Convert.ToDecimal(0.0))
{
record.OurFee = null;
}
if (record.OpenDate.ToString() == "1/1/1900 12:00:00 AM")
{
record.OpenDate = null;
}
if (record.CODate.ToString() == "1/1/1900 12:00:00 AM")
{
record.CODate = null;
}
if (record.OrderDate.ToString() == "1/1/1900 12:00:00 AM")
{
record.OrderDate = null;
}
if (record.StmtsFrom.ToString() == "1/1/1900 12:00:00 AM")
{
record.StmtsFrom = null;
}
if (record.StmtsTo.ToString() == "1/1/1900 12:00:00 AM")
{
record.StmtsTo = null;
}
if (record.DateSubmitted.ToString() == "1/1/1900 12:00:00 AM")
{
record.DateSubmitted = null;
}
if (record.DateConfirmed.ToString() == "1/1/1900 12:00:00 AM")
{
record.DateConfirmed = null;
}
if (record.DateSellerPaid.ToString() == "1/1/1900 12:00:00 AM")
{
record.DateSellerPaid = null;
}
if (record.DateReceived.ToString() == "1/1/1900 12:00:00 AM")
{
record.DateReceived = null;
}
if (record.UnavailableDate.ToString() == "1/1/1900 12:00:00 AM")
{
record.UnavailableDate = null;
}
if (record.DatePayRec.ToString() == "1/1/1900 12:00:00 AM")
{
record.DatePayRec = null;
}
if (record.IsApplication.ToUpper() == "TRUE")
{
record.IsApplication = "Yes";
}
else
{
record.IsApplication = "No";
}
if (record.IsAffidavitIssuer.ToUpper() == "TRUE")
{
record.IsAffidavitIssuer = "Yes";
}
else
{
record.IsAffidavitIssuer = "No";
}
if (record.IsAffidavitSeller.ToUpper() == "TRUE")
{
record.IsAffidavitSeller = "Yes";
}
else
{
record.IsAffidavitSeller = "No";
}
if (record.IsUnavailable.ToUpper() == "TRUE")
{
record.IsUnavailable = "Yes";
}
else
{
record.IsUnavailable = "No";
}
if (record.IsClosed.ToUpper() == "TRUE")
{
record.IsClosed = "Yes";
}
else
{
record.IsClosed = "No";
}
}
data.Add(record);
}
//Close the datareader
rdr.Close();
}
catch (Exception ex)
{
throw new Exception("Exception in DataQueries.GetMediaNotReceivedReportRecords:" + ex.Message);
}
return data.AsEnumerable<MediaViewEditResult>();
}
public IEnumerable<vwAccount> GetAccounts(string nameLike)
{
DBFactory db;
SqlDataReader rdr;
List<vwAccount> data = null;
try
{
db = new DBFactory();
rdr = db.ExecuteReader("MSI_spGetVWAccountNameSearch", new SqlParameter("@nameSearch", nameLike));
data = new List<vwAccount>();
vwAccount record;
while (rdr.Read())
{
record = new vwAccount();
record.FirstName = rdr["FirstName"].ToString();
record.LastName = rdr["LastName"].ToString();
record.NAME = rdr["NAME"].ToString();
record.ACCOUNT = rdr["ACCOUNT"].ToString();
record.OriginalAccount = rdr["OriginalAccount"].ToString();
if (!string.IsNullOrEmpty(rdr["OpenDate"].ToString()))
record.OpenDate = Convert.ToDateTime(rdr["OpenDate"].ToString());
if (!string.IsNullOrEmpty(rdr["ChargeOffDate"].ToString()))
record.ChargeOffDate = Convert.ToDateTime(rdr["ChargeOffDate"].ToString());
record.SSN = rdr["SSN"].ToString();
data.Add(record);
}
}
catch (Exception ex)
{
throw new Exception("Exception in DataQueries.GetSearchResults:" + ex.Message);
}
return data.AsQueryable<vwAccount>();
}
