private void populateStateList(string type)
{
string countryCode;
if (type == "org")
{
countryCode = ddlCountry.SelectedValue;
}
else
{
countryCode = ddlShipCountry.SelectedValue;
}
DataTable dt = Utils.GetStateList(countryCode);
if (dt.Rows.Count > 0)
{
if (type == "org")
{
ddlState.DataValueField = "gid";
ddlState.DataTextField = "stateName";
ddlState.DataSource = dt;
ddlState.DataBind();
ddlState.Visible = true;
}
if (type == "ship")
{
ddlShipState.DataValueField = "gid";
ddlShipState.DataTextField = "stateName";
ddlShipState.DataSource = dt;
ddlShipState.DataBind();
ddlShipState.Visible = true;
}
}
if (dt.Rows.Count == 1)
{
if (type == "org")
{
ddlState.Visible = false;
}
if (type == "ship")
{
ddlShipState.Visible = false;
}
}
}
private void populateStateList()
{
string countryCode = ddlCountry.SelectedValue;
DataTable dt = Utils.GetStateList(countryCode);
if (dt.Rows.Count > 1)
{
{
ddlState.Visible = true;
ddlState.DataValueField = "gid";
ddlState.DataTextField = "stateName";
ddlState.DataSource = dt;
ddlState.DataBind();
}
}
else
{
ddlState.Visible = false;
}
}
protected void ddlCountry_SelectedIndexChanged(object sender, EventArgs e)
{
string selected = ddlCountry.SelectedValue;
if (selected == "")
{
ddlState.Items.Clear();
}
else
{
selected = selected.Split(':')[1];
DataTable dt = Utils.GetStateList(selected);
if (dt.Rows.Count > 0)
{
ddlState.DataValueField = "gid";
ddlState.DataTextField = "stateName";
ddlState.DataSource = dt;
ddlState.DataBind();
}
}
divAdvToggleOn = true;
}
public string SearchCriteria(string op)
{
if (ddlItem.SelectedValue == "@accession.id")
{
string search = " @accession.accession_number_part1='" + ddlIdentifier.SelectedValue + "'";
int from = Toolkit.ToInt32(txtIDFrom.Text.Trim(), 0);
int to = Toolkit.ToInt32(txtIDTo.Text.Trim(), 0);
if (!string.IsNullOrEmpty(txtIDFrom.Text))
{
if (!string.IsNullOrEmpty(txtIDTo.Text))
{
search += " and @accession.accession_number_part2 >= " + from;
}
else
{
search += " and @accession.accession_number_part2 = " + from;
}
}
if (!string.IsNullOrEmpty(txtIDTo.Text))
{
if (!string.IsNullOrEmpty(txtIDFrom.Text))
{
search += " and @accession.accession_number_part2 <= " + to;
}
else
{
search += " and @accession.accession_number_part2 = " + to;
}
}
return(search);
}
else if (ddlItem.SelectedValue == "@accession.location")
{
string search = "";
string geoid = "";
string sql = "";
if (ddlState.SelectedIndex <= 0)
//geoid = ddlCountry.SelectedValue.Split(':')[0];
{
string selected = ddlCountry.SelectedValue;
string inState = "";
if (selected != "")
{
DataTable dt = Utils.GetStateList(selected.Split(':')[1]);
foreach (DataRow dr in dt.Rows)
{
inState += dr["gid"] + ",";
}
inState += ddlCountry.SelectedValue.Split(':')[0];
//search = " @accession_source.geography_id in (" + inState + ")";
search = " (@accession_source.geography_id in (" + inState + ") and @accession_source.is_origin='Y') " + op; //6/27/2013 SE works OK for this query
//sql = "select accession_id from accession_source where is_origin = 'Y' and geography_id in (" + inState + ")";
}
}
else
{
geoid = ddlState.SelectedValue;
if (!string.IsNullOrEmpty(geoid))
{
//search = " @accession_source.geography_id=" + geoid;
search = " (@accession_source.geography_id=" + geoid + " and @accession_source.is_origin='Y') " + op; //6/27/2013 SE works OK for this query
}
//sql = "select accession_id from accession_source where is_origin = 'Y' and geography_id = " + geoid;
}
/*
* if (sql != "")
* {
* using (var sd = UserManager.GetSecureData(true))
* {
* using (var dm = DataManager.Create(sd.DataConnectionSpec))
* {
* DataTable dtID = dm.Read(sql);
* List<string> IDs = new List<string>();
* if (dtID != null)
* {
* foreach (DataRow dr in dtID.Rows)
* {
* IDs.Add(dr[0].ToString());
* }
* if (IDs.Count > 0)
* search = " @accession.accession_id in (" + String.Join(",", IDs.ToArray()) + ")";
* else
* search = "";
* }
* else
* search = "";
* }
* }
* }
* else
* search = "";
*/
return(search);
}
else if (ddlOperator.SelectedIndex == 0 || ddlItem.Items.Count == 0 || (ddlItem.Items.Count > 0 && ddlItem.SelectedIndex == 0))
{
return("");
}
else if (ddlItem.SelectedValue == "@inventory.availability_status_code")
{
string condition1 = "";
string condition2 = "";
string search = "";
foreach (ListItem li in lstValues.Items)
{
string value = li.Value;
if (li.Selected)
{
if (li.Value == "AVAIL")
{
condition1 = " @inventory.availability_status_code = 'AVAIL'";
}
if (li.Value == "NOT AVAIL")
{
condition2 = " @inventory.availability_status_code not in ('AVAIL')";
}
}
}
if (condition1 != "" && condition2 != "") // have everything equals no constraint
{
search = "";
}
else
{
search = condition1 + condition2;
}
return(search);
}
//else if (txtValue.Visible)
//{
// if (txtValue.Text.Trim() != "")
// {
// return " " + ddlItem.SelectedValue + " " + txtValue.Text.Trim();
// }
// else
// return "";
//}
//else
//{
// string field = ddlItem.SelectedValue;
// StringBuilder sb = new StringBuilder();
// foreach (ListItem li in lstValues.Items)
// {
// if (li.Selected)
// {
// if (sb.Length > 0)
// sb.Append(" OR @").Append(field).Append(" ").Append(li.Value);
// else
// sb.Append(" @").Append(field).Append(" ").Append(li.Value);
// }
// }
else if (txtValue.Visible)
{
if (txtValue.Text.Trim() != "")
{
//if (ddlOperator.SelectedValue == "like")
// return " " + ddlItem.SelectedValue + " like '%" + txtValue.Text.Trim() + "%'";
//else if (ddlOperator.SelectedValue == "=")
// return " " + ddlItem.SelectedValue + " = '" + txtValue.Text.Trim() + "'";
//else
// return " " + ddlItem.SelectedValue + " in (" + txtValue.Text.Trim() + ")";
string search = "";
string userTxt = txtValue.Text.Trim();
if (userTxt.IndexOf(",") < 0)
{
userTxt = userTxt.Replace("'", "''"); //leave the case of example genus_name in ('vitis', 'Parthenocissus','Ampelocissus', 'Ampelopsis')
}
switch (ddlOperator.SelectedValue)
{
case "=":
search = " " + ddlItem.SelectedValue + " = '" + userTxt + "'";
break;
case "like":
search = " " + ddlItem.SelectedValue + " like '" + userTxt.Replace('*', '%') + "'";
break;
case "in":
search = " " + ddlItem.SelectedValue + " in (" + userTxt + ")";
break;
case "contain":
search = " " + ddlItem.SelectedValue + " like '%" + userTxt.Replace('*', '%') + "%'";
break;
case "!=":
search = " " + ddlItem.SelectedValue + " != '" + userTxt + "'";
break;
default:
search = " " + ddlItem.SelectedValue + " = '" + userTxt + "'";
break;
}
return(search);
}
else
{
return("");
}
}
else
{
string field = " ";
string fieldNull = " ";
if (ddlItem.SelectedValue.Substring(0, 1) != "@")
{
field += " @";
fieldNull += " @";
}
field += ddlItem.SelectedValue + " in (";
fieldNull += ddlItem.SelectedValue;
StringBuilder sb = new StringBuilder();
bool hasNull = false;
foreach (ListItem li in lstValues.Items)
{
if (li.Selected)
{
if (li.Value.ToUpper() == "NULL")
{
hasNull = true;
}
else
{
if (sb.Length > 0)
{
sb.Append(", '").Append(li.Value).Append("'");
}
else
{
sb.Append("'").Append(li.Value).Append("'");
}
}
}
}
if (hasNull)
{
if (sb.Length > 0)
{
return(" (" + field + sb.Append(")").Append(" or ").Append(fieldNull).Append(" is null) ").Append(op).ToString());
}
//return field + sb.Append(")").Append(" or ").Append(fieldNull).Append(" is null").ToString();
else
{
//return " (" + sb.Append(fieldNull).Append(" is null)").ToString();
return(" (" + sb.Append(fieldNull).Append(" is null) A").Append(op).ToString());
}
}
else
{
if (sb.Length > 0)
{
return(field + sb.ToString() + ")");
}
else
{
return("");
}
}
}
}
