public lkkData selectLKK(string id)
{
lkkData result = new lkkData();
SQLiteCommand selectData = new SQLiteCommand();
SQLiteDataReader reader = null;
selectData.Connection = connection;
selectData.CommandText = "SELECT * FROM infedenceView WHERE id=:id";
selectData.Parameters.Add(":id", DbType.String);
selectData.Parameters[":id"].Value = id;
connect();
reader = selectData.ExecuteReader();
if (reader.Read())
{
result.date = Convert.ToDateTime(reader["data"]);
result.number = reader["number"].ToString();
result.department = reader["department"].ToString();
result.doctor = reader["doctor"].ToString();
result.fio = reader["fio"].ToString();
result.birth = reader["birth"].ToString();
result.age = reader["age"].ToString();
result.region = reader["region"].ToString();
result.town = reader["town"].ToString();
result.address = reader["address"].ToString();
result.addressWork = reader["addressWork"].ToString();
result.position = reader["position"].ToString();
result.mkbCode = reader["mkbCode"].ToString();
result.diagnose = reader["diagnose"].ToString();
result.lkk = reader["lkk"].ToString();
result.msek = reader["msek"].ToString();
result.addition = reader["addition"].ToString();
result.sex = reader["sex"].ToString();
result.haveInvalidity = Convert.ToBoolean(reader["haveInvalidity"].ToString());
result.InvalidityDate = Convert.ToDateTime(reader["invalidityDate"]);
result.LPZ = reader["LPZ"].ToString();
}
reader.Close();
reader = null;
return result;
}
public void updateData(lkkData data, string id)
{
updateSQLCommand.CommandText = "UPDATE lkk SET data=:data, number=:number, departmentID=:departmentID, headOfDepartment=:headOfDepartment, doctorID=:doctorID,FIO=:FIO, " +
"birth=:birth,age=:age,regionID=:regionID,townID=:townID, address=:address,addressWork=:addressWork,position=:position,mkbCode=:mkbCode, "+
"diagnose=:diagnose,lkk=:lkk,msek=:msek,addition=:addition,sex=:sex,status=:status,invalidityDate=:invalidityDate,LpzID=:LpzID, "+
"haveInvalidity=:haveInvalidity,comission=:comission WHERE id=:id";
updateSQLCommand.Parameters.Add(":id", DbType.String);
updateSQLCommand.Parameters[":id"].Value = id;
updateSQLCommand.Parameters.Add(":data", DbType.Date);
updateSQLCommand.Parameters[":data"].Value = data.date;
updateSQLCommand.Parameters.Add(":number", DbType.String);
updateSQLCommand.Parameters[":number"].Value = data.number;
updateSQLCommand.Parameters.Add(":departmentID", DbType.String);
updateSQLCommand.Parameters[":departmentID"].Value = getID(data.department, typesData.department);
updateSQLCommand.Parameters.Add(":headOfDepartment", DbType.String);
updateSQLCommand.Parameters[":headOfDepartment"].Value = getID(updateSQLCommand.Parameters[":departmentID"].Value.ToString(), typesData.headOfDepartment);
updateSQLCommand.Parameters.Add(":doctorID", DbType.String);
updateSQLCommand.Parameters[":doctorID"].Value = getID(data.doctor, typesData.doctor);
updateSQLCommand.Parameters.Add(":FIO", DbType.String);
updateSQLCommand.Parameters[":FIO"].Value = data.fio;
updateSQLCommand.Parameters.Add(":birth", DbType.String);
updateSQLCommand.Parameters[":birth"].Value = data.birth;
updateSQLCommand.Parameters.Add(":age", DbType.String);
updateSQLCommand.Parameters[":age"].Value = data.age;
updateSQLCommand.Parameters.Add(":regionID", DbType.String);
updateSQLCommand.Parameters[":regionID"].Value = getID(data.region, typesData.region);
updateSQLCommand.Parameters.Add(":townID", DbType.String);
updateSQLCommand.Parameters[":townID"].Value = getID(updateSQLCommand.Parameters[":regionID"].Value.ToString(), data.town);
updateSQLCommand.Parameters.Add(":address", DbType.String);
updateSQLCommand.Parameters[":address"].Value = data.address;
updateSQLCommand.Parameters.Add(":addressWork", DbType.String);
updateSQLCommand.Parameters[":addressWork"].Value = data.addressWork;
updateSQLCommand.Parameters.Add(":position", DbType.String);
updateSQLCommand.Parameters[":position"].Value = data.position;
updateSQLCommand.Parameters.Add(":mkbCode", DbType.String);
updateSQLCommand.Parameters[":mkbCode"].Value = data.mkbCode;
updateSQLCommand.Parameters.Add(":diagnose", DbType.String);
updateSQLCommand.Parameters[":diagnose"].Value = data.diagnose;
updateSQLCommand.Parameters.Add(":lkk", DbType.String);
updateSQLCommand.Parameters[":lkk"].Value = data.lkk;
updateSQLCommand.Parameters.Add(":msek", DbType.String);
updateSQLCommand.Parameters[":msek"].Value = data.msek;
updateSQLCommand.Parameters.Add(":addition", DbType.String);
updateSQLCommand.Parameters[":addition"].Value = data.addition;
updateSQLCommand.Parameters.Add(":sex", DbType.String);
updateSQLCommand.Parameters[":sex"].Value = data.sex;
updateSQLCommand.Parameters.Add(":status", DbType.String);
updateSQLCommand.Parameters[":status"].Value = data.status;
updateSQLCommand.Parameters.Add(":haveInvalidity", DbType.Boolean);
updateSQLCommand.Parameters[":haveInvalidity"].Value = data.haveInvalidity;
updateSQLCommand.Parameters.Add(":LpzID", DbType.String);
updateSQLCommand.Parameters[":LpzID"].Value = getID(data.LPZ, typesData.lpz);
if (data.haveInvalidity)
{
updateSQLCommand.Parameters.Add(":invalidityDate", DbType.Date);
updateSQLCommand.Parameters[":invalidityDate"].Value = data.InvalidityDate;
}
else
{
updateSQLCommand.Parameters.Add(":invalidityDate", DbType.Date);
updateSQLCommand.Parameters[":invalidityDate"].Value = "1900-01-01 00:00:00";
}
updateSQLCommand.Parameters.Add(":comission", DbType.String);
updateSQLCommand.Parameters[":comission"].Value = getMembersLkkToInsert();
connect();
updateSQLCommand.ExecuteNonQuery();
disconnect();
}
public string insertData(lkkData data)
{
insertSQLCommand.CommandText = "INSERT INTO LKK (data,number,departmentID, headOfDepartment, doctorID,FIO,birth,age,regionID,townID," +
"address,addressWork,position,mkbCode,diagnose,lkk,msek,addition,sex,status,invalidityDate,LpzID,haveInvalidity,comission) VALUES(:data,:number,:departmentID," +
":headOfDepartment, :doctorID,:FIO,:birth,:age,:regionID,:townID,:address,:addressWork,:position,:mkbCode,:diagnose,:lkk,:msek," +
":addition,:sex,:status,:invalidityDate,:LpzID,:haveInvalidity,:comission)";
insertSQLCommand.Parameters.Add(":data", DbType.Date);
insertSQLCommand.Parameters[":data"].Value = data.date;
insertSQLCommand.Parameters.Add(":number", DbType.String);
insertSQLCommand.Parameters[":number"].Value = data.number;
insertSQLCommand.Parameters.Add(":departmentID", DbType.String);
insertSQLCommand.Parameters[":departmentID"].Value = getID(data.department, typesData.department);
insertSQLCommand.Parameters.Add(":headOfDepartment", DbType.String);
insertSQLCommand.Parameters[":headOfDepartment"].Value = getID(insertSQLCommand.Parameters[":departmentID"].Value.ToString(), typesData.headOfDepartment);
insertSQLCommand.Parameters.Add(":doctorID", DbType.String);
insertSQLCommand.Parameters[":doctorID"].Value = getID(data.doctor, typesData.doctor);
insertSQLCommand.Parameters.Add(":FIO", DbType.String);
insertSQLCommand.Parameters[":FIO"].Value = data.fio;
insertSQLCommand.Parameters.Add(":birth", DbType.String);
insertSQLCommand.Parameters[":birth"].Value = data.birth;
insertSQLCommand.Parameters.Add(":age", DbType.String);
insertSQLCommand.Parameters[":age"].Value = data.age;
insertSQLCommand.Parameters.Add(":regionID", DbType.String);
insertSQLCommand.Parameters[":regionID"].Value = getID(data.region, typesData.region);
insertSQLCommand.Parameters.Add(":townID", DbType.String);
insertSQLCommand.Parameters[":townID"].Value = getID(insertSQLCommand.Parameters[":regionID"].Value.ToString(), data.town);
insertSQLCommand.Parameters.Add(":address", DbType.String);
insertSQLCommand.Parameters[":address"].Value = data.address;
insertSQLCommand.Parameters.Add(":addressWork", DbType.String);
insertSQLCommand.Parameters[":addressWork"].Value = data.addressWork;
insertSQLCommand.Parameters.Add(":position", DbType.String);
insertSQLCommand.Parameters[":position"].Value = data.position;
insertSQLCommand.Parameters.Add(":mkbCode", DbType.String);
insertSQLCommand.Parameters[":mkbCode"].Value = data.mkbCode;
insertSQLCommand.Parameters.Add(":diagnose", DbType.String);
insertSQLCommand.Parameters[":diagnose"].Value = data.diagnose;
insertSQLCommand.Parameters.Add(":lkk", DbType.String);
insertSQLCommand.Parameters[":lkk"].Value = data.lkk;
insertSQLCommand.Parameters.Add(":msek", DbType.String);
insertSQLCommand.Parameters[":msek"].Value = data.msek;
insertSQLCommand.Parameters.Add(":addition", DbType.String);
insertSQLCommand.Parameters[":addition"].Value = data.addition;
insertSQLCommand.Parameters.Add(":sex", DbType.String);
insertSQLCommand.Parameters[":sex"].Value = data.sex;
insertSQLCommand.Parameters.Add(":status", DbType.String);
insertSQLCommand.Parameters[":status"].Value = data.status;
insertSQLCommand.Parameters.Add(":haveInvalidity", DbType.Boolean);
insertSQLCommand.Parameters[":haveInvalidity"].Value = data.haveInvalidity;
insertSQLCommand.Parameters.Add(":LpzID", DbType.String);
insertSQLCommand.Parameters[":LpzID"].Value = getID(data.LPZ, typesData.lpz);
if (data.haveInvalidity)
{
insertSQLCommand.Parameters.Add(":invalidityDate", DbType.Date);
insertSQLCommand.Parameters[":invalidityDate"].Value = data.InvalidityDate;
}
else
{
insertSQLCommand.Parameters.Add(":invalidityDate", DbType.Date);
insertSQLCommand.Parameters[":invalidityDate"].Value = Convert.ToDateTime("01.01.1900");
}
insertSQLCommand.Parameters.Add(":comission", DbType.String);
insertSQLCommand.Parameters[":comission"].Value = getMembersLkkToInsert();
connect();
insertSQLCommand.ExecuteNonQuery();
disconnect();
string id = null;
SQLiteCommand selectData = new SQLiteCommand();
selectData.Connection = connection;
connect();
selectData.CommandText = "SELECT id FROM lkk WHERE data=:date AND number=:number AND fio=:fio";
selectData.Parameters.Add(":date", DbType.Date);
selectData.Parameters[":date"].Value = data.date;
selectData.Parameters.Add(":number", DbType.String);
selectData.Parameters[":number"].Value = data.number;
selectData.Parameters.Add(":fio", DbType.String);
selectData.Parameters[":fio"].Value = data.fio;
SQLiteDataReader getlkkID;
getlkkID = selectData.ExecuteReader();
if (getlkkID.Read())
id = getlkkID[0].ToString();
disconnect();
return id;
}
