public DataTable selectInvalidity(invaliditySearchData data) { DataTable searchResult = new DataTable(); SQLiteCommand selectData = new SQLiteCommand(); SQLiteDataAdapter adapter = new SQLiteDataAdapter(); adapter.SelectCommand = selectData; selectData.Connection = connection; selectData.CommandText = "SELECT id, fio, dateBirth, age, region, town, address, diagnose, groupe, invalidityDate, sex FROM invalidityView WHERE deleted=0"; if (data.fio.Trim() != null && data.fio.Trim() !="") { selectData.CommandText += " AND fio=:fio "; selectData.Parameters.Add(":fio", DbType.String); selectData.Parameters[":fio"].Value = data.fio; } if (data.ageStart != null) { selectData.CommandText += " AND age>=:startAge AND age <=:endAge "; selectData.Parameters.Add(":startAge", DbType.String); selectData.Parameters.Add(":endAge", DbType.String); selectData.Parameters[":startAge"].Value = data.ageStart; selectData.Parameters[":endAge"].Value = data.ageEnd; } if (data.region != null) { selectData.CommandText += " AND region=:region "; selectData.Parameters.Add(":region", DbType.String); selectData.Parameters[":region"].Value = data.region; } if (data.diagnose != null) { selectData.CommandText += " AND mkbCode=:mkbCode "; selectData.Parameters.Add(":mkbCode", DbType.String); selectData.Parameters[":mkbCode"].Value = getID(data.diagnose, typesData.mkbcode); } if (data.lpz != null) { selectData.CommandText += " AND LPZ=:lpz "; selectData.Parameters.Add(":lpz", DbType.String); selectData.Parameters[":lpz"].Value = data.lpz; } if (data.excludeTill18) { selectData.CommandText += " AND dateBirth >:birth"; selectData.Parameters.Add(":birth", DbType.DateTime); selectData.Parameters[":birth"].Value = DateTime.Now.AddYears(-18).Date; } if (data.orphanDiseases) { selectData.CommandText += " AND mkbCode IN (SELECT diagnosis.codeMKB FROM diagnosis WHERE diagnosis.isOrphan = 1) "; } if (data.working) { selectData.CommandText += " AND position =''"; } connect(); adapter.Fill(searchResult); disconnect(); return searchResult; }
public void start(invaliditySearchData dataToSearch) { invalidityData = dataToSearch; }