public static string GetDeleteString(string guid)
{
string indexTablename = "[dbo].[" + GWDataDB.GetTableName(interfaceName, GWDataDBTable.Index) + "]";
string patientTableName = "[dbo].[" + GWDataDB.GetTableName(interfaceName, GWDataDBTable.Patient) + "]";
string orderTableName = "[dbo].[" + GWDataDB.GetTableName(interfaceName, GWDataDBTable.Order) + "]";
string reportTableName = "[dbo].[" + GWDataDB.GetTableName(interfaceName, GWDataDBTable.Report) + "]";
StringBuilder sb = new StringBuilder();
//sb.AppendLine("USE " + GWDataDB.DataBaseName);
//sb.AppendLine("IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + GWDataDB.GetTableName(interfaceName, GWDataDBTable.Report) + "]') AND type in (N'U'))");
sb.AppendLine("DELETE" + " FROM " + reportTableName);
sb.AppendLine("WHERE " + GWDBControl.GetFullFieldName(interfaceName, GWDataDBField.r_DATA_ID) + " = " + "\'" + guid + "\'");
sb.AppendLine("");
//sb.AppendLine("IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + GWDataDB.GetTableName(interfaceName, GWDataDBTable.Order) + "]') AND type in (N'U'))");
sb.AppendLine("DELETE" + " FROM " + orderTableName);
sb.AppendLine("WHERE " + GWDBControl.GetFullFieldName(interfaceName, GWDataDBField.o_DATA_ID) + " = " + "\'" + guid + "\'");
sb.AppendLine("");
//sb.AppendLine("IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + GWDataDB.GetTableName(interfaceName, GWDataDBTable.Patient) + "]') AND type in (N'U'))");
sb.AppendLine("DELETE" + " FROM " + patientTableName);
sb.AppendLine("WHERE " + GWDBControl.GetFullFieldName(interfaceName, GWDataDBField.p_DATA_ID) + " = " + "\'" + guid + "\'");
sb.AppendLine("");
//sb.AppendLine("IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + GWDataDB.GetTableName(interfaceName, GWDataDBTable.Index) + "]') AND type in (N'U'))");
sb.AppendLine("DELETE" + " FROM " + indexTablename);
sb.AppendLine("WHERE " + GWDBControl.GetFullFieldName(interfaceName, GWDataDBField.i_IndexGuid) + " = " + "\'" + guid + "\'");
return(sb.ToString());
}
//All matched tables
public static string GetTablesString(string guid)
{
string patientTableName = "[dbo].[" + GWDataDB.GetTableName(interfaceName, GWDataDBTable.Patient) + "]";
string orderTableName = "[dbo].[" + GWDataDB.GetTableName(interfaceName, GWDataDBTable.Order) + "]";
string reportTableName = "[dbo].[" + GWDataDB.GetTableName(interfaceName, GWDataDBTable.Report) + "]";
StringBuilder sb = new StringBuilder();
//sb.AppendLine("USE " + GWDataDB.DataBaseName);
//sb.AppendLine("");
//sb.AppendLine("SET ANSI_NULLS ON");
//sb.AppendLine("SET QUOTED_IDENTIFIER ON");
//sb.AppendLine("");
//sb.AppendLine("IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + GWDataDB.GetTableName(interfaceName, GWDataDBTable.Patient) + "]') AND type in (N'U'))");
sb.AppendLine("SELECT " + patientTableName + ".* FROM " + patientTableName);
sb.AppendLine("WHERE " + GWDBControl.GetFullFieldName(interfaceName, GWDataDBField.p_DATA_ID) + " = " + "\'" + guid + "\'");
sb.AppendLine("");
sb.AppendLine("IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + GWDataDB.GetTableName(interfaceName, GWDataDBTable.Patient) + "]') AND type in (N'U'))");
sb.AppendLine("SELECT " + orderTableName + ".* FROM " + orderTableName);
sb.AppendLine("WHERE " + GWDBControl.GetFullFieldName(interfaceName, GWDataDBField.o_DATA_ID) + " = " + "\'" + guid + "\'");
sb.AppendLine("");
sb.AppendLine("IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + GWDataDB.GetTableName(interfaceName, GWDataDBTable.Patient) + "]') AND type in (N'U'))");
sb.AppendLine("SELECT " + reportTableName + ".* FROM " + reportTableName);
sb.AppendLine("WHERE " + GWDBControl.GetFullFieldName(interfaceName, GWDataDBField.r_DATA_ID) + " = " + "\'" + guid + "\'");
sb.AppendLine("");
return(sb.ToString());
}
public static string GetFilterString(SimpleQuery queryInfo, XCollection <QueryCriteriaItem> filterItemList)
{
//Get interface name from configuration file
string indexTablename = "[dbo].[" + GWDataDB.GetTableName(interfaceName, GWDataDBTable.Index) + "]";
string patientTableName = "[dbo].[" + GWDataDB.GetTableName(interfaceName, GWDataDBTable.Patient) + "]";
string orderTableName = "[dbo].[" + GWDataDB.GetTableName(interfaceName, GWDataDBTable.Order) + "]";
string reportTableName = "[dbo].[" + GWDataDB.GetTableName(interfaceName, GWDataDBTable.Report) + "]";
#region Query Head
StringBuilder sb = new StringBuilder();
//sb.AppendLine("USE " + GWDataDB.DataBaseName);
//sb.AppendLine("");
//sb.AppendLine("SET ANSI_NULLS ON");
//sb.AppendLine("SET QUOTED_IDENTIFIER ON");
//sb.AppendLine("");
//sb.AppendLine("IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + GWDataDB.GetTableName(interfaceName, GWDataDBTable.Index) + "]') AND type in (N'U'))");
sb.AppendLine("SELECT");
sb.AppendLine("\t" + indexTablename + ".* FROM " + indexTablename);
sb.AppendLine("\tLEFT JOIN " + patientTableName + " ON " + GWDBControl.GetFullFieldName(interfaceName, GWDataDBField.p_DATA_ID) + " = " + GWDBControl.GetFullFieldName(interfaceName, GWDataDBField.i_IndexGuid));
sb.AppendLine("\tLEFT JOIN " + orderTableName + " ON " + GWDBControl.GetFullFieldName(interfaceName, GWDataDBField.o_DATA_ID) + " = " + GWDBControl.GetFullFieldName(interfaceName, GWDataDBField.i_IndexGuid));
sb.AppendLine("\tLEFT JOIN " + reportTableName + " ON " + GWDBControl.GetFullFieldName(interfaceName, GWDataDBField.r_DATA_ID) + " = " + GWDBControl.GetFullFieldName(interfaceName, GWDataDBField.i_IndexGuid));
#endregion
#region Query Condition
if (queryInfo != null || filterItemList != null)
{
StringBuilder sbWhere = new StringBuilder();
if (queryInfo != null)
{
#region Simple Query
if (queryInfo.EventType.Enable)
{
sbWhere.AppendLine("\tAND " + GWDBControl.GetFullFieldName(interfaceName, queryInfo.EventType.Fieldname) + " like " + "\'%" + queryInfo.EventType.FieldValue.Replace("'", "''") + "%\'");
}
if (queryInfo.StartTime.Enable)
{
sbWhere.AppendLine("\tAND " + GWDBControl.GetFullFieldName(interfaceName, queryInfo.StartTime.Fieldname) + " >= " + "\'" + queryInfo.StartTime.FieldValue.Replace("'", "''") + "\'");
}
if (queryInfo.EndTime.Enable)
{
sbWhere.AppendLine("\tAND " + GWDBControl.GetFullFieldName(interfaceName, queryInfo.EndTime.Fieldname) + " <= " + "\'" + queryInfo.EndTime.FieldValue.Replace("'", "''") + "\'");
}
if (queryInfo.PatientID.Enable)
{
sbWhere.AppendLine("\tAND " + GWDBControl.GetFullFieldName(interfaceName, queryInfo.PatientID.Fieldname) + " like " + "\'%" + queryInfo.PatientID.FieldValue.Replace("'", "''") + "%\'");
}
if (queryInfo.PatientName.Enable)
{
sbWhere.AppendLine("\tAND " + GWDBControl.GetFullFieldName(interfaceName, queryInfo.PatientName.Fieldname) + " like " + "\'%" + queryInfo.PatientName.FieldValue.Replace("'", "''") + "%\'");
}
if (queryInfo.OrderNo.Enable)
{
sbWhere.AppendLine("\tAND " + GWDBControl.GetFullFieldName(interfaceName, queryInfo.OrderNo.Fieldname) + " like " + "\'%" + queryInfo.OrderNo.FieldValue.Replace("'", "''") + "%\'");
}
if (queryInfo.AccessionNo.Enable)
{
sbWhere.AppendLine("\tAND " + GWDBControl.GetFullFieldName(interfaceName, queryInfo.AccessionNo.Fieldname) + " like " + "\'%" + queryInfo.AccessionNo.FieldValue.Replace("'", "''") + "%\'");
}
if (queryInfo.StudyInstanceUID.Enable)
{
sbWhere.AppendLine("\tAND " + GWDBControl.GetFullFieldName(interfaceName, queryInfo.StudyInstanceUID.Fieldname) + " like " + "\'%" + queryInfo.StudyInstanceUID.FieldValue.Replace("'", "''") + "%\'");
}
#endregion
}
else
{
#region Advanced Query
foreach (QueryCriteriaItem filterItem in filterItemList)
{
if (filterItem.Type == QueryCriteriaType.Or)
{
sbWhere.Append("\t OR ");
}
else
{
sbWhere.Append("\tAND ");
}
string fname = GWDBControl.GetFullFieldName(interfaceName, filterItem.GWDataDBField);
switch (filterItem.Operator)
{
case QueryCriteriaOperator.Like:
{
sbWhere.AppendLine(fname + " LIKE " + "\'%" + filterItem.Translating.ConstValue + "%\'");
break;
}
case QueryCriteriaOperator.Equal:
{
sbWhere.AppendLine(fname + " = " + "\'" + filterItem.Translating.ConstValue + "\'");
break;
}
case QueryCriteriaOperator.NotEqual:
{
sbWhere.AppendLine("(" + fname + " <> " + "\'" + filterItem.Translating.ConstValue + "\'" + " OR " + fname + " IS NULL)");
break;
}
case QueryCriteriaOperator.LargerThan:
{
sbWhere.AppendLine(fname + " > " + "\'" + filterItem.Translating.ConstValue + "\'");
break;
}
case QueryCriteriaOperator.SmallerThan:
{
sbWhere.AppendLine(fname + " < " + "\'" + filterItem.Translating.ConstValue + "\'");
break;
}
case QueryCriteriaOperator.EqualLargerThan:
{
sbWhere.AppendLine(fname + " >= " + "\'" + filterItem.Translating.ConstValue + "\'");
break;
}
case QueryCriteriaOperator.EqualSmallerThan:
{
sbWhere.AppendLine(fname + " <= " + "\'" + filterItem.Translating.ConstValue + "\'");
break;
}
}
}
#endregion
}
if (sbWhere.Length > 4)
{
sbWhere.Remove(1, 4);
sb.AppendLine("WHERE");
sb.AppendLine(sbWhere.ToString());
}
}
#endregion
sb.AppendLine(" ORDER BY " + GWDataDBField.i_DataDateTime.GetFullFieldName(interfaceName));
return(sb.ToString());
}
