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