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