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