public static List <ClaimBordx> GetFees(DateTime from, DateTime to) { List <ClaimBordx> reportData = null; if (DAL.OpenConnection()) { string query = "SELECT [Checks Generated].[Payment Date], [Checks Generated].[Pay to], [Checks Generated].Amount, [Checks Generated].[Check Nro], " + " [Checks Generated].[Month Coverage], [Checks Generated].Memo FROM [Checks Generated] " + " WHERE [Checks Generated].[Payment Date] Between #" + from.ToString("MM/dd/yyyy") + "# And #" + to.ToString("MM/dd/yyyy") + "# " + " AND Year([Checks Generated].[Payment Date]) = " + to.ToString("yyyy") + " ORDER BY [Checks Generated].[Payment Date] "; reportData = new List <ClaimBordx>(); DataSet ds = new DataSet(); DataTable dt = new DataTable(); OleDbDataAdapter adapter = new OleDbDataAdapter(query, conn); adapter.Fill(ds); dt = ds.Tables[0]; foreach (DataRow row in dt.Rows) { ClaimBordx claim = new ClaimBordx(); claim.Address = new Address(); claim.DateFeesPaid = DateTime.Parse(row["Payment Date"].ToString()); claim.FeesPaid = double.Parse(row["Amount"].ToString()); claim.LossDescription = row["Pay to"].ToString() + " (Check #: " + row["Check Nro"].ToString() + ")"; reportData.Add(claim); } DAL.CloseConnection(); } return(reportData); }
public static List <ClaimBordx> GetReportData(DateTime from, DateTime to) { List <ClaimBordx> reportData = null; if (DAL.OpenConnection()) { string query = "SELECT Asegurados.[Asegurado ID], Asegurados.Apellidos, Asegurados.Nombres, Asegurados_1.[Asegurado ID] as ClaimantId,Asegurados_1.Apellidos as ClaimantLastName, Asegurados_1.Nombres as ClaimantName, " + "Status_1.[Numero Poliza], Status_1.[Fecha Desde], Status_1.[Fecha Hasta], Reclamos.[Numero del Reclamo], Worksheets.[Numero del Worksheet], " + "Diagnosticos.[ICD 9 Code], Diagnosticos.Diagnostico, Worksheets.[Fecha del Worksheet], [Pagos de Reclamos].[Fecha de Pago], " + "Min([Detalle de los Worksheets].[Fecha del Servicio]) AS [MinOfFecha del Servicio], " + "Max([Detalle de los Worksheets].[Fecha del Servicio]) AS [MaxOfFecha del Servicio], [Monedas Paises].Pais, Monedas.Simbolo, Worksheets.[Monto Cliente], " + "Worksheets.[Monto Cubierto] FROM([Monedas Paises] INNER JOIN ((Diagnosticos INNER JOIN(((Reclamos INNER JOIN Worksheets " + "ON Reclamos.[Reclamo ID] = Worksheets.[Reclamo ID]) INNER JOIN(((Asegurados INNER JOIN Status ON Asegurados.[Asegurado ID] = Status.[Asegurado ID]) " + "INNER JOIN Status AS Status_1 ON Status.[Status ID Principal] = Status_1.[Status ID]) INNER JOIN Asegurados AS Asegurados_1 " + "ON Status_1.[Asegurado ID] = Asegurados_1.[Asegurado ID]) ON Worksheets.[Status ID] = Status.[Status ID]) " + "INNER JOIN[Pagos de Reclamos] ON Worksheets.[Pago de Reclamo ID] = [Pagos de Reclamos].[Pago de Reclamo ID]) " + "ON Diagnosticos.[Diagnostico ID] = Reclamos.[Diagnostico ID]) INNER JOIN[Detalle de los Worksheets] " + "ON Worksheets.[Worksheet ID] = [Detalle de los Worksheets].[Worksheet ID]) ON[Monedas Paises].[Pais ID] = [Detalle de los Worksheets].[Pais Moneda ID]) " + "INNER JOIN Monedas ON[Detalle de los Worksheets].[Moneda ID] = Monedas.[Moneda ID] " + "WHERE ((([Pagos de Reclamos].[Fecha de Pago]) Between #" + from.ToString("MM/dd/yyyy") + "# And #" + to.ToString("MM/dd/yyyy") + "#)) " + "GROUP BY Asegurados.[Asegurado ID], Asegurados.Apellidos, Asegurados.Nombres, " + "Asegurados_1.[Asegurado ID], Asegurados_1.Apellidos, Asegurados_1.Nombres, Status_1.[Numero Poliza], Status_1.[Fecha Desde], Status_1.[Fecha Hasta], Reclamos.[Numero del Reclamo], " + "Worksheets.[Numero del Worksheet], Diagnosticos.[ICD 9 Code], Diagnosticos.Diagnostico, Worksheets.[Fecha del Worksheet], " + "[Pagos de Reclamos].[Fecha de Pago], [Monedas Paises].Pais, Monedas.Simbolo, [Pagos de Reclamos].[Fecha de Pago], Worksheets.[Monto Cliente], " + "Worksheets.[Monto Cubierto] " + //"HAVING ((([Pagos de Reclamos].[Fecha de Pago]) Between #" + from.ToString("MM/dd/yyyy") + "# And #" + to.ToString("MM/dd/yyyy") + "#)) "+ "ORDER BY[Pagos de Reclamos].[Fecha de Pago];"; //query = "select * from asegurados;"; reportData = new List <ClaimBordx>(); DataSet ds = new DataSet(); DataTable dt = new DataTable(); OleDbDataAdapter adapter = new OleDbDataAdapter(query, conn); adapter.Fill(ds); dt = ds.Tables[0]; foreach (DataRow row in dt.Rows) { try { ClaimBordx claim = new ClaimBordx(); claim.Address = new Address(); claim.FLAddress = new Address(); claim.InsuredId = row["Asegurado ID"].ToString(); claim.Insured = row["Apellidos"].ToString() + " " + row["Nombres"].ToString(); claim.ClaimantId = row["ClaimantId"].ToString(); claim.Claimant = row["ClaimantLastName"].ToString() + " " + row["ClaimantName"].ToString(); claim.PolicyNumber = row["Numero Poliza"].ToString(); claim.EffectiveDate = DateTime.Parse(row["Fecha Desde"].ToString()); claim.ExpirationDate = DateTime.Parse(row["Fecha Hasta"].ToString()); //string claimnumber = row["Numero del Reclamo"].ToString(); int claimnro = int.Parse(row["Numero del Reclamo"].ToString()); claim.ClaimNumber = "L" + claimnro.ToString("0000000") + "/" + row["Numero del Worksheet"].ToString(); claim.Year = claim.ExpirationDate.Year; claim.LossDateFrom = DateTime.Parse(row["MinOfFecha del Servicio"].ToString()); claim.LossDateTo = DateTime.Parse(row["MaxOfFecha del Servicio"].ToString()); string icd9 = row["ICD 9 Code"].ToString(); claim.LossDescription = string.IsNullOrEmpty(icd9) ? row["Diagnostico"].ToString() : icd9 + "-" + row["Diagnostico"].ToString(); claim.LossLocation = row["Pais"].ToString(); claim.OriginalCurrency = row["Simbolo"].ToString(); claim.AmountClaimed = double.Parse(row["Monto Cliente"].ToString()); claim.AmountPaid = double.Parse(row["Monto Cubierto"].ToString()); claim.DateClaimMade = DateTime.Parse(row["Fecha del Worksheet"].ToString()); claim.DateClaimPaid = DateTime.Parse(row["Fecha de Pago"].ToString()); claim.DateClaimNotified = DateTime.Parse(row["Fecha del Worksheet"].ToString()); reportData.Add(claim); } catch (Exception es) { } } foreach (ClaimBordx claim in reportData) { claim.Address = GetAddress(claim.ClaimantId); claim.FLAddress = GetAddress(claim.ClaimantId, "2"); } DAL.CloseConnection(); } return(reportData); }