public bool Equals(FullPersonInfo p)
{
bool ret = base.Equals(p);
if (!ret)
return ret;
if (gender != p.gender)
return false;
if (genome != p.genome)
return false;
if (!properties.Equals(p.properties))
return false;
return true;
}
public void updatePerson(UInt64 id, FullPersonInfo fpInfo)
{
if (!connect())
return;
try
{
begin();
String query;
if (id == 0)
query = "insert into person (id, name, gender, race) values (:newid, :name, :g, :r)";
else
query = "update person set id = :newid, name = :name, gender = :g, race = :r where id = :id";
NpgsqlCommand command = new NpgsqlCommand(query, connection);
command.Parameters.Add(new NpgsqlParameter("newid", NpgsqlTypes.NpgsqlDbType.Numeric));
command.Parameters["newid"].Value = fpInfo.getId();
command.Parameters.Add(new NpgsqlParameter("id", NpgsqlTypes.NpgsqlDbType.Numeric));
command.Parameters["id"].Value = id;
command.Parameters.Add(new NpgsqlParameter("name", NpgsqlTypes.NpgsqlDbType.Varchar));
command.Parameters["name"].Value = fpInfo.name;
command.Parameters.Add(new NpgsqlParameter("g", NpgsqlTypes.NpgsqlDbType.Char));
String g = "?";
switch (fpInfo.gender)
{
case FullPersonInfo.Gender.Unknown:
g = "?";
break;
case FullPersonInfo.Gender.Male:
g = "M";
break;
case FullPersonInfo.Gender.Female:
g = "F";
break;
}
command.Parameters["g"].Value = g;
command.Parameters.Add(new NpgsqlParameter("r", NpgsqlTypes.NpgsqlDbType.Varchar));
String r = null;
switch (fpInfo.genome)
{
case FullPersonInfo.Genome.Android:
r = "A";
break;
case FullPersonInfo.Genome.Human:
r = "H";
break;
}
command.Parameters["r"].Value = r;
command.ExecuteNonQuery();
command = new NpgsqlCommand("delete from person_prop where pers_id = :persId", connection);
command.Parameters.Add(new NpgsqlParameter("persId", NpgsqlTypes.NpgsqlDbType.Numeric));
command.Parameters["persId"].Value = fpInfo.getId();
command.ExecuteNonQuery();
foreach (DataRow row in fpInfo.properties.Rows)
{
command = new NpgsqlCommand("insert into person_prop (prop_id, pers_id, value) values (:propId, :persId, :value)", connection);
command.Parameters.Add(new NpgsqlParameter("persId", NpgsqlTypes.NpgsqlDbType.Numeric));
command.Parameters["persId"].Value = fpInfo.getId();
command.Parameters.Add(new NpgsqlParameter("propId", NpgsqlTypes.NpgsqlDbType.Numeric));
command.Parameters["propId"].Value = Convert.ToUInt64(row[0]);
command.Parameters.Add(new NpgsqlParameter("value", NpgsqlTypes.NpgsqlDbType.Varchar));
command.Parameters["value"].Value = Convert.ToString(row[2]);
command.ExecuteNonQuery();
}
commit();
}
catch (Exception ex)
{
HandleException(ex);
}
finally
{
disconnect();
}
}
public void insertPerson(FullPersonInfo fpInfo)
{
updatePerson(0, fpInfo);
}
public FullPersonInfo getPersonInfo(UInt64 id)
{
Logging.log("getPersonInfo\n");
if (!connect())
return null;
FullPersonInfo ret = new FullPersonInfo();
NpgsqlCommand command = new NpgsqlCommand("select NAME, GENDER, RACE from PERSON where ID = :value1", connection);
try
{
command.Parameters.Add(new NpgsqlParameter("value1", NpgsqlTypes.NpgsqlDbType.Numeric));
command.Parameters[0].Value = id;
NpgsqlDataReader rd = command.ExecuteReader();
while (rd.Read())
{
ret.id = id;
ret.name = Convert.ToString(rd["NAME"]);
String sGender = Convert.ToString(rd["GENDER"]);
String sGenome = Convert.ToString(rd["RACE"]);
switch (sGender)
{
case "M":
ret.gender = FullPersonInfo.Gender.Male;
break;
case "F":
ret.gender = FullPersonInfo.Gender.Female;
break;
default:
ret.gender = FullPersonInfo.Gender.Unknown;
break;
}
switch (sGenome)
{
case "H":
ret.genome = FullPersonInfo.Genome.Human;
break;
case "A":
ret.genome = FullPersonInfo.Genome.Android;
break;
}
}
command = new NpgsqlCommand("select PROPERTY.ID, PROPERTY.NAME, PERSON_PROP.VALUE from PERSON_PROP, PROPERTY where PERSON_PROP.PERS_ID = :value1 and PERSON_PROP.PROP_ID = PROPERTY.ID ORDER BY PROPERTY.NAME ASC", connection);
command.Parameters.Add(new NpgsqlParameter("value1", NpgsqlTypes.NpgsqlDbType.Numeric));
command.Parameters[0].Value = id;
rd = command.ExecuteReader();
while (rd.Read())
{
UInt64 propId = Convert.ToUInt64(rd[0]);
String key = Convert.ToString(rd[1]);
String value = Convert.ToString(rd[2]);
ret.properties.Rows.Add(propId, key, value);
}
}
catch (Exception ex)
{
HandleException(ex);
}
finally
{
disconnect();
}
Logging.log("!getPersonInfo\n");
return ret;
}
public void fillWithQuestionsForGender(DataTable table, FullPersonInfo.Gender gender, UInt64 sessionId)
{
table.Clear();
table.Columns.Add("ID", Type.GetType("System.UInt64"));
table.Columns.Add("TEXT", Type.GetType("System.String"));
String g = "('A'";
switch (gender)
{
case FullPersonInfo.Gender.Female:
g += ", 'F'";
break;
case FullPersonInfo.Gender.Male:
g += ", 'M'";
break;
}
g += ")";
if (!connect())
return;
String query = "SELECT ID, TEXT FROM VK_QUESTION WHERE ID NOT IN " +
"(SELECT QUESTION_ID FROM VK_SESSION_QUESTION WHERE SESSION_ID = :sid) AND " +
"GENDER IN " + g;
try
{
NpgsqlCommand command = new NpgsqlCommand(query, connection);
command.Parameters.Add(new NpgsqlParameter("sid", NpgsqlTypes.NpgsqlDbType.Numeric));
command.Parameters["sid"].Value = sessionId;
NpgsqlDataReader rd = command.ExecuteReader();
while (rd.Read())
{
table.Rows.Add(rd["ID"], rd["TEXT"]);
}
}
catch (Exception ex)
{
HandleException(ex);
}
finally
{
disconnect();
}
}