public decimal GetPutnamHRARecordRate(string _qy) { HRA hobj = new HRA(); decimal rate = 0; string yrmo = hobj.GetMaxQuarterYRMO(_qy); string cmdstr = "SELECT rate FROM hra_rates " + "WHERE yrmo = " + "( " + "SELECT MAX(yrmo) FROM hra_rates " + "WHERE yrmo <= @yrmo AND type = 'Putnam' " + ") " + "AND type = 'Putnam'"; try { if (connect == null || connect.State == ConnectionState.Closed) { connect.Open(); } command = new SqlCommand(cmdstr, connect); command.Parameters.AddWithValue("@yrmo", yrmo); object result = command.ExecuteScalar(); if ((result == null) || (result == DBNull.Value)) { throw new Exception("Cannot find Putnam HRA Record Rate for YRMO - " + yrmo + " or less"); } rate = Decimal.Parse(result.ToString()); return(rate); } finally { connect.Close(); } }
public static Boolean hasPartData_AuditR_Discp(string _qy) { Boolean _result; HRA hobj = new HRA(); int count = 0; string yrmo = hobj.GetMaxQuarterYRMO(_qy); DateTime date = HRA.GetLastDayofYRMO(yrmo); string cmdstr = "SELECT COUNT(*) " + "FROM hra_PartDataInvoice " + "WHERE source = 'ptnm_partdata' " + "AND period = @qy " + "AND termdt <= @date " + "AND balance <> 0 " + "AND (LOWER(RTRIM(LTRIM(partStatDesc))) <> 'terminated, paid out') " + "AND CONVERT(INT, LTRIM(RTRIM(REPLACE(ssn, '-', '')))) NOT IN " + "( " + "SELECT CONVERT(INT, LTRIM(RTRIM(REPLACE(ssn, '-', '')))) " + "FROM hra_AUDITR " + "WHERE modifydt <= @date " + "AND period= @qy " + ") " + "AND CONVERT(INT, LTRIM(RTRIM(REPLACE(ssn, '-', '')))) NOT IN " + "( " + "SELECT CONVERT(INT, LTRIM(RTRIM(REPLACE(dpnd_ssn, '-', '')))) " + "FROM Dependant " + "WHERE dpnd_owner = 1 AND dpnd_validated = 1 " + ") "; try { if (connect != null && connect.State == ConnectionState.Closed) { connect.Open(); } command = new SqlCommand(cmdstr, connect); command.Parameters.AddWithValue("@qy", _qy); command.Parameters.AddWithValue("@date", date); count = Convert.ToInt32(command.ExecuteScalar()); if (count != 0) { _result = true; } else { _result = false; } command.Dispose(); return(_result); } finally { connect.Close(); } }
public static Boolean hasWgwkInv_PartData_noBal(string _qy) { Boolean _result; HRA hobj = new HRA(); int count = 0; string yrmo = hobj.GetMaxQuarterYRMO(_qy); string prevyrmo = hobj.getPrevYRMO(yrmo); string priyrmo = hobj.getPrevYRMO(prevyrmo); DateTime date = HRA.GetLastDayofYRMO(yrmo); string cmdstr = "SELECT COUNT(DISTINCT ssn) " + "FROM hra_PartDataInvoice " + "WHERE source = 'wgwk_invoice' " + "AND period IN (@yrmo, @prevyrmo, @priyrmo) " + "AND CONVERT(INT, LTRIM(RTRIM(REPLACE(ssn, '-', '')))) IN " + "( " + "SELECT CONVERT(INT, LTRIM(RTRIM(REPLACE(ssn, '-', '')))) " + "FROM hra_PartDataInvoice " + "WHERE source = 'ptnm_partdata' " + "AND period = @qy " + "AND termdt <= @date " + "AND (balance = 0 OR (LOWER(RTRIM(LTRIM(partStatDesc))) = 'terminated, paid out')) " + ") "; try { if (connect != null && connect.State == ConnectionState.Closed) { connect.Open(); } command = new SqlCommand(cmdstr, connect); command.Parameters.AddWithValue("@qy", _qy); command.Parameters.AddWithValue("@date", date); command.Parameters.AddWithValue("@yrmo", yrmo); command.Parameters.AddWithValue("@prevyrmo", prevyrmo); command.Parameters.AddWithValue("@priyrmo", priyrmo); count = Convert.ToInt32(command.ExecuteScalar()); if (count != 0) { _result = true; } else { _result = false; } command.Dispose(); return(_result); } finally { connect.Close(); } }
public static Boolean AUDITRInserted(string _qy) { HRA hobj = new HRA(); int count = 0; string cmdstr = "SELECT COUNT(*) FROM [hra_AUDITR] WHERE [period] = @qy"; try { if (connect == null || connect.State == ConnectionState.Closed) { connect.Open(); } command = new SqlCommand(cmdstr, connect); command.Parameters.AddWithValue("@qy", _qy); count = Convert.ToInt32(command.ExecuteScalar()); if (count == 0) { if (Convert.ToInt32(hobj.GetMaxQuarterYRMO(_qy)) < 200804) { return(true); } else { return(false); } } else { return(true); } } finally { connect.Close(); } }