public void insertPilotDepData(DepRecord insertRec)
{
string _cmdstr = "INSERT INTO Dependant (dpnd_empno,dpnd_relationship,dpnd_ssn,dpnd_fname,dpnd_minit,dpnd_lname,dpnd_sex,dpnd_dob,dpnd_order,dpnd_row_eff_dt,dpnd_add_diff)"
+ " VALUES (@dempno,@drel,@dssn,@dfname,@dmint,@dlname,@dsex,@dob,@dorder,@roweffdt,@diff)";
string _cmdstr1 = "INSERT INTO Address (addr_empno,addr_dpnd_ssn,addr_type,addr_addr1,addr_addr2,addr_city,addr_state,addr_zip,addr_row_eff_dt)"
+ " VALUES(@empno,@dssn,@atype,@addr1,@addr2,@city,@state,@zip,@roweffdt)";
SqlCommand cmd = null;
SqlTransaction ts;
conn.Open();
ts = conn.BeginTransaction();
try
{
cmd = new SqlCommand(_cmdstr, conn, ts);
cmd.Parameters.AddWithValue("@dempno", insertRec.DEmpNum);
cmd.Parameters.AddWithValue("@drel", insertRec.Relation);
cmd.Parameters.AddWithValue("@dssn", insertRec.DSSN);
cmd.Parameters.AddWithValue("@dfname", insertRec.DFirstName);
if (!(insertRec.DAddress1.Equals("") && insertRec.DCity.Equals("") && insertRec.DState.Equals("") && insertRec.DZip.Equals("")))
{
cmd.Parameters.AddWithValue("@diff", 1);
}
else
{
cmd.Parameters.AddWithValue("@diff", 0);
}
if (insertRec.DMiddleInitial.ToString().Equals(""))
{
cmd.Parameters.AddWithValue("@dmint", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@dmint", insertRec.DMiddleInitial);
}
cmd.Parameters.AddWithValue("@dlname", insertRec.DLastName);
cmd.Parameters.AddWithValue("@dsex", insertRec.DSexCode);
cmd.Parameters.AddWithValue("@dob", insertRec.DDateBirth);
cmd.Parameters.AddWithValue("@dorder", insertRec.Order);
cmd.Parameters.AddWithValue("@roweffdt", DateTime.Now);
cmd.ExecuteNonQuery();
if (!(insertRec.DAddress1.Equals("") && insertRec.DCity.Equals("") && insertRec.DState.Equals("") && insertRec.DZip.Equals("")))
{
cmd = new SqlCommand(_cmdstr1, conn, ts);
cmd.Parameters.AddWithValue("@empno", insertRec.DEmpNum);
cmd.Parameters.AddWithValue("@dssn", insertRec.DSSN);
cmd.Parameters.AddWithValue("@atype", "004");
cmd.Parameters.AddWithValue("@addr1", insertRec.DAddress1);
if (insertRec.DAddress2.Equals(""))
{
cmd.Parameters.AddWithValue("@addr2", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@addr2", insertRec.DAddress2);
}
cmd.Parameters.AddWithValue("@city", insertRec.DCity);
cmd.Parameters.AddWithValue("@state", insertRec.DState);
cmd.Parameters.AddWithValue("@zip", insertRec.DZip);
cmd.Parameters.AddWithValue("@roweffdt", DateTime.Now);
cmd.ExecuteNonQuery();
}
ts.Commit();
}
catch (Exception ex)
{
ts.Rollback();
throw (new Exception("Error in inserting new Beneficiary Records!"));
}
finally
{
conn.Close();
cmd.Dispose();
}
}
public void updateDependant(string atype, DepRecord duRec, string _prevssn)
{
string _cmdstr = "UPDATE [Dependant] "
+ " SET [dpnd_relationship] = @rel "
+ " ,[dpnd_ssn] = @dssn "
+ " ,[dpnd_fname] = @fname "
+ " ,[dpnd_minit] = @mi "
+ " ,[dpnd_lname] = @lname "
+ " ,[dpnd_sex] = @dsexcd "
+ " ,[dpnd_dob] = @dob "
+ " ,[dpnd_order] = @order "
+ " ,[dpnd_owner] = @owner " //bit
+ " ,[dpnd_ownernotElegb] = @oElegb " //bit
+ " ,[dpnd_validated] = @valid " //bit
+ " ,[dpnd_validated_dt] = @validateddt "
+ " ,[dpnd_owner_eff_dt] = @effdt "
+ " ,[dpnd_owner_stop_dt] = @stopdt "
+ " ,[dpnd_row_eff_dt] = @roweffdt "
+ " ,[dpnd_notElegbnotes] = @enotes "
+ " ,[dpnd_add_diff] = @diff "
+ " WHERE [dpnd_empno] = @empno AND [dpnd_ssn] = @dssn1 ";
string _cmdstr1 = "UPDATE Address "
+ " SET [addr_dpnd_ssn] = @dssn,[addr_type] = @atype,[addr_addr1] = @addr1,[addr_addr2] = @addr2"
+ ",[addr_city] = @city,[addr_state] = @state,[addr_zip] = @zip,[addr_row_eff_dt] = @roweffdt"
+ " WHERE [addr_empno] = @empno AND [addr_dpnd_ssn] = @dssn1 AND [addr_type] = '004'";
string _cmdstr2 = "SELECT COUNT(*) "
+ " FROM Address "
+ " WHERE [addr_empno] = @empno AND [addr_dpnd_ssn] = @dssn1 AND [addr_type] = @atype";
string _cmdstr3 = "INSERT INTO [Address] "
+ " ([addr_empno],[addr_dpnd_ssn],[addr_type],[addr_addr1], "
+ " [addr_addr2],[addr_city],[addr_state],[addr_zip],[addr_row_eff_dt]) "
+ " VALUES (@empno,@dssn,@atype,@addr1,@addr2,@city,@state,@zip,@roweffdt) ";
SqlCommand cmd = null;
SqlTransaction ts;
SqlDateTime sqldbnull;
conn.Open();
ts = conn.BeginTransaction();
try
{
cmd = new SqlCommand(_cmdstr, conn, ts);
cmd.Parameters.AddWithValue("@empno", duRec.DEmpNum);
cmd.Parameters.AddWithValue("@dssn1", _prevssn);
cmd.Parameters.AddWithValue("@dssn", duRec.DSSN);
cmd.Parameters.AddWithValue("@fname", duRec.DFirstName);
cmd.Parameters.AddWithValue("@lname", duRec.DLastName);
//check if address is different
if (atype.Equals("004"))
{
cmd.Parameters.AddWithValue("@diff", 1);
}
else
{
cmd.Parameters.AddWithValue("@diff", 0);
}
cmd.Parameters.AddWithValue("@rel", duRec.Relation);
if (!duRec.DMiddleInitial.Equals(""))
{
cmd.Parameters.AddWithValue("@mi", duRec.DMiddleInitial);
}
else
{
cmd.Parameters.AddWithValue("@mi", DBNull.Value);
}
cmd.Parameters.AddWithValue("@dsexcd", duRec.DSexCode);
if (!duRec.DDateBirth.Equals(""))
{
cmd.Parameters.AddWithValue("@dob", duRec.DDateBirth);
}
else
{
cmd.Parameters.AddWithValue("@dob", DBNull.Value);
}
cmd.Parameters.AddWithValue("@order", duRec.Order);
if (duRec.Owner)
{
cmd.Parameters.AddWithValue("@owner", 1);
}
else
{
cmd.Parameters.AddWithValue("@owner", 0);
}
if (duRec.EligibilityStatus)
{
cmd.Parameters.AddWithValue("@oElegb", 1);
}
else
{
cmd.Parameters.AddWithValue("@oElegb", 0);
}
if (duRec.OwnershipValidated)
{
cmd.Parameters.AddWithValue("@valid", 1);
}
else
{
cmd.Parameters.AddWithValue("@valid", 0);
}
if (duRec.OwnershipValidDate.Equals("") || duRec.OwnershipValidDate.Equals(DateTime.MinValue.ToShortDateString()))
{
cmd.Parameters.AddWithValue("@validateddt", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@validateddt", duRec.OwnershipValidDate);
}
if (duRec.OwnershipStartDate.Equals("") || duRec.OwnershipStartDate.Equals(DateTime.MinValue.ToShortDateString()))
{
cmd.Parameters.AddWithValue("@effdt", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@effdt", duRec.OwnershipStartDate);
}
if (duRec.OwnershipEndDate.Equals("") || duRec.OwnershipEndDate.Equals(DateTime.MinValue.ToShortDateString()))
{
cmd.Parameters.AddWithValue("@stopdt", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@stopdt", duRec.OwnershipEndDate);
}
if (!duRec.ElegibilityNotes.Equals(""))
{
cmd.Parameters.AddWithValue("@enotes", duRec.ElegibilityNotes);
}
else
{
cmd.Parameters.AddWithValue("@enotes", DBNull.Value);
}
cmd.Parameters.AddWithValue("@roweffdt", DateTime.Now);
cmd.ExecuteNonQuery();
if (atype.Equals("004"))
{
cmd = new SqlCommand(_cmdstr2, conn, ts);
cmd.Parameters.AddWithValue("@empno", duRec.DEmpNum);
cmd.Parameters.AddWithValue("@dssn1", _prevssn);
cmd.Parameters.AddWithValue("@atype", atype);
int _count = Convert.ToInt32(cmd.ExecuteScalar());
if (_count == 0)
{
cmd = new SqlCommand(_cmdstr3, conn, ts);
}
else
{
cmd = new SqlCommand(_cmdstr1, conn, ts);
cmd.Parameters.AddWithValue("@dssn1", _prevssn);
}
cmd.Parameters.AddWithValue("@empno", duRec.DEmpNum);
cmd.Parameters.AddWithValue("@dssn", duRec.DSSN);
cmd.Parameters.AddWithValue("@atype", atype);
cmd.Parameters.AddWithValue("@addr1", duRec.DAddress1);
cmd.Parameters.AddWithValue("@addr2", duRec.DAddress2);
cmd.Parameters.AddWithValue("@city", duRec.DCity);
cmd.Parameters.AddWithValue("@state", duRec.DState);
cmd.Parameters.AddWithValue("@zip", duRec.DZip);
cmd.Parameters.AddWithValue("@roweffdt", DateTime.Now);
cmd.ExecuteNonQuery();
}
ts.Commit();
}
catch (Exception ex)
{
ts.Rollback();
throw (new Exception("Error in Updating Records"));
}
finally
{
conn.Close();
}
}
/// <summary>
/// Dependants Record for the given Employee Number
/// </summary>
/// <param name="empno">Employee Number</param>
/// <returns>Depenedent Record</returns>
public DepRecord getPilotDepData(int empno, string dssn)
{
string _cmdstr = "SELECT [dpnd_empno],[dpnd_relationship],[dpnd_fname],[dpnd_minit]"
+ ",[dpnd_lname],[dpnd_ssn],[dpnd_sex],[dpnd_dob],[dpnd_order]"
+ ",[dpnd_owner],[dpnd_ownernotElegb],[dpnd_validated]"
+ ",[dpnd_validated_dt],[dpnd_owner_eff_dt]"
+ ",[dpnd_owner_stop_dt],[dpnd_row_eff_dt],[dpnd_notElegbnotes]"
+ " FROM [Dependant] WHERE [dpnd_empno] = @empno AND [dpnd_ssn] = @dssn";
string _cmdstr1 = "SELECT [addr_empno],[addr_dpnd_ssn],[addr_type],[addr_addr1],[addr_addr2],[addr_city],[addr_state]"
+ ",[addr_zip],[addr_phone],[addr_phone2],[addr_email],[addr_email2],[addr_row_eff_dt]"
+ " FROM [Address] WHERE [addr_empno] = @empno AND [addr_type] = '004' AND [addr_dpnd_ssn] = @dssn";
DepRecord dRec = new DepRecord();
SqlCommand command = new SqlCommand(_cmdstr, conn);
command.Parameters.AddWithValue("@empno", empno);
command.Parameters.AddWithValue("@dssn", dssn);
SqlDataReader reader, reader1;
conn.Open();
using (conn)
{
reader = command.ExecuteReader();
if (reader.Read())
{
dRec.DEmpNum = Convert.ToInt32(reader["dpnd_empno"]);
dRec.DSSN = reader["dpnd_ssn"].ToString();
dRec.DFirstName = reader["dpnd_fname"].ToString();
dRec.DLastName = reader["dpnd_lname"].ToString();
dRec.DMiddleInitial = reader["dpnd_minit"].ToString();
if (reader["dpnd_order"] != DBNull.Value)
{
dRec.Order = Convert.ToInt32(reader["dpnd_order"]);
}
if (reader["dpnd_relationship"] != DBNull.Value)
{
dRec.Relation = reader["dpnd_relationship"].ToString();
}
if (reader["dpnd_sex"] != DBNull.Value)
{
dRec.DSexCode = reader["dpnd_sex"].ToString();
}
if (reader["dpnd_dob"] != DBNull.Value)
{
dRec.DDateBirth = Convert.ToDateTime(reader["dpnd_dob"]).ToString("MM/dd/yyyy");
}
if (reader["dpnd_owner"] != DBNull.Value)
{
dRec.Owner = Convert.ToBoolean(reader["dpnd_owner"]);
}
if (reader["dpnd_validated"] != DBNull.Value)
{
dRec.OwnershipValidated = Convert.ToBoolean(reader["dpnd_validated"]); //ownership validation status
}
if (reader["dpnd_ownernotElegb"] != DBNull.Value)
{
dRec.EligibilityStatus = Convert.ToBoolean(reader["dpnd_ownernotElegb"]); //ownership elegibility status
}
if (reader["dpnd_notElegbnotes"] != DBNull.Value)
{
dRec.ElegibilityNotes = reader["dpnd_notElegbnotes"].ToString(); //ownership elegibility status notes
}
if (reader["dpnd_owner_eff_dt"] != DBNull.Value)
{
dRec.OwnershipStartDate = Convert.ToDateTime(reader["dpnd_owner_eff_dt"]).ToString("MM/dd/yyyy");
}
if (reader["dpnd_owner_stop_dt"] != DBNull.Value)
{
dRec.OwnershipEndDate = Convert.ToDateTime(reader["dpnd_owner_stop_dt"]).ToString("MM/dd/yyyy");
}
if (reader["dpnd_validated_dt"] != DBNull.Value)
{
dRec.OwnershipValidDate = Convert.ToDateTime(reader["dpnd_validated_dt"]).ToString("MM/dd/yyyy");
}
}
reader.Close();
SqlCommand command1 = new SqlCommand(_cmdstr1, conn);
command1.Parameters.AddWithValue("@empno", empno);
command1.Parameters.AddWithValue("@dssn", dssn);
reader1 = command1.ExecuteReader();
if (reader1.Read())
{
dRec.DAddress1 = reader1["addr_addr1"].ToString();
dRec.DAddress2 = reader1["addr_addr2"].ToString();
dRec.DState = reader1["addr_state"].ToString();
dRec.DCity = reader1["addr_city"].ToString();
dRec.DZip = reader1["addr_zip"].ToString();
}
reader1.Close();
}
conn.Close();
return(dRec);
}
