private void bindData1(int traitID, int codeID)
{
DataTable dt = null;
using (SecureData sd = new SecureData(false, UserManager.GetLoginToken(true)))
{
using (DataManager dm = sd.BeginProcessing(true, true))
{
string traitName = dm.ReadValue(@"select coded_name from crop_trait where crop_trait_id = :ctid", new DataParameters(":ctid", traitID, DbType.Int32)).ToString();
string cropName = dm.ReadValue(@"select name from crop where crop_id = (select crop_id from crop_trait where crop_trait_id = :ctid)", new DataParameters(":ctid", traitID, DbType.Int32)).ToString();
string codeValue = dm.ReadValue(@"select code from crop_trait_code where crop_trait_code_id = :ctcid", new DataParameters(":ctcid", codeID, DbType.Int32)).ToString();
string codeDesc = dm.ReadValue(@"select description from crop_trait_code_lang where crop_trait_code_id = :ctcid and sys_lang_id = :langid", new DataParameters(":ctcid", codeID, DbType.Int32, ":langid", sd.LanguageID, DbType.Int32)).ToString();
if (traitName != "")
{
lblDesc.Text = cropName + " accessions with code " + codeValue + " (" + codeDesc + ") for descriptor " + traitName;
dt = sd.GetData("web_descriptor_value_accession1", ":traitid=" + traitID + ";:codeid=" + codeID, 0, 0).Tables["web_descriptor_value_accession1"];
gvAccession.DataSource = dt;
gvAccession.DataBind();
}
}
}
}
public static bool HasWebAddress()
{
using (SecureData sd = GetSecureData(true))
{
// using (DataManager dm = sd.BeginProcessing(true))
// {
// DataTable dt = dm.Read(@"
// select *
// from web_user_shipping_address
// where web_user_id = :wuserid",
// new DataParameters
// (":wuserid", sd.WebUserID, DbType.Int32
// ));
DataTable dt = sd.GetData("web_user_shipping_address", ":wuserid=" + sd.WebUserID, 0, 0).Tables["web_user_shipping_address"];
if (dt.Rows.Count > 1)
{
return(true);
}
else
{
return(false);
}
//}
}
}
public static string GetMaterialDescription(string formcode)
{
using (SecureData sd = new SecureData(false, UserManager.GetLoginToken(true)))
{
// using (DataManager dm = sd.BeginProcessing(true))
// {
// string text = (dm.ReadValue(@"
// select
// coalesce(cvl.description, cv.value) as description
// from
// code_value cv
// left join code_value_lang cvl
// on cv.code_value_id = cvl.code_value_id
// and cvl.sys_lang_id = :langid
// where
// cv.group_name = 'GERMPLASM_FORM'
// and cv.value = :formcode
// ", new DataParameters(":langid", sd.LanguageID, DbType.Int32
// , ":formcode", formcode, DbType.String))).ToString();
// return text;
// }
var dt = sd.GetData("web_lookup_material_description", ":langid=" + sd.LanguageID + ";:formcode=" + formcode, 0, 0).Tables["web_lookup_material_description"];
return(dt.ToScalarValue());
}
}
private void bindDropDowns()
{
using (var sd = new SecureData(false, UserManager.GetLoginToken()))
{
DataTable dt = sd.GetData("web_lookup_query_dataviewlist", ":langid=" + sd.LanguageID + ";:dvName=" + "web_qry_%", 0, 0).Tables["web_lookup_query_dataviewlist"];
//ddlDataView.DataSource = dt;
//ddlDataView.DataBind();
string name = "";
string title = "";
string config = "";
bool isInternal = (Page.User.IsInRole("ALLUSERS")) || Toolkit.GetSetting("MakeReportsExternal", false); // only show internal reports to internal users, show external report to all
foreach (DataRow dr in dt.Rows)
{
name = dr["dataview_name"].ToString();
title = dr["title"].ToString();
config = dr["configuration_options"].ToString().ToLower();
if (isInternal || (config == "external"))
{
ddlDataView.Items.Add(new ListItem(title, name));
}
}
ddlDataView.Items.Insert(0, new ListItem("-- Select One --", ""));
ddlDataView.SelectedIndex = 0;
showMiscControl(true);
}
}
/// <summary>
/// Get user's preference from the database.
/// </summary>
/// <param name="coopid"></param>
/// <param name="prefKey"></param>
/// <returns></returns>
public static string GetUserPref(string prefKey)
{
string ret = null;
using (SecureData sd = GetSecureData(true))
{
// using (DataManager dm = sd.BeginProcessing(true))
// {
// ret = (dm.ReadValue(@"
// select
// preference_value
// from
// web_user_preference
// where
// web_user_id = :userid
// and preference_name = :prefname
// ", new DataParameters(
// ":userid", sd.WebUserID , DbType.Int32,
// ":prefname", prefKey, DbType.String))).ToString();
// }
var dt = sd.GetData("web_user_preference", ":userid=" + sd.WebUserID + ";:prefname=" + prefKey, 0, 0).Tables["web_user_preference"];
ret = dt.ToScalarValue();
}
return(ret);
}
public static string GetWebAppResource(string appName, string formName)
{
using (SecureData sd = GetSecureData(true))
{
string key = "WebPvAppResource" + sd.LanguageID;
var cm = CacheManager.Get(key);
if (cm.Keys.Count == 0)
{
var dt = sd.GetData("web_lookup_app_resource", ":appname=" + appName + ";:formname=" + formName + ";:langid=" + sd.LanguageID, 0, 0).Tables["web_lookup_app_resource"];
StringBuilder sb = new StringBuilder();
if (dt != null)
{
foreach (DataRow dr in dt.Rows)
{
sb.Append(";").Append(dr["app_resource_name"].ToString()).Append("=").Append(dr["display_member"].ToString());
}
}
string sText = sb.ToString();
if (sText.Length > 0)
{
sText = sText.Substring(1);
}
cm[key] = sText;
return(sText);
}
else
{
return(cm[key].ToString());
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
using (var sd = new SecureData(false, UserManager.GetLoginToken()))
{
DataTable dt = sd.GetData("web_summary_1", "", 0, 0).Tables["web_summary_1"];
foreach (DataRow dr in dt.Rows)
{
if (dr[0].ToString() != "0")
{
cntFamily = dr[0].ToString();
}
if (dr[1].ToString() != "0")
{
cntGenus = dr[1].ToString();
}
if (dr[2].ToString() != "0")
{
cntSpecies = dr[2].ToString();
}
if (dr[3].ToString() != "0")
{
cntAccession = dr[3].ToString();
}
}
}
}
private void bindDropDowns()
{
pnlSearchDown.Visible = false;
pnlSearch.Visible = true;
using (SecureData sd = new SecureData(false, UserManager.GetLoginToken(true)))
{
var dt = sd.GetData("web_search_dataviewlist", ":langid=" + sd.LanguageID, 0, 0).Tables["web_search_dataviewlist"];
// using (DataManager dm = sd.BeginProcessing(true)) {
// DataTable dt = dm.Read(@"
//select
// dv.dataview_name,
// coalesce(dvl.title, dv.dataview_name, '') as title
//from
// sys_dataview dv left join sys_dataview_lang dvl
// on dv.sys_dataview_id = dvl.sys_dataview_id
// and dvl.sys_lang_id = :langid
//where
// dv.category_name = 'web_search'
//order by
// dv.category_sort_order, 2
//", new DataParameters(":langid", sd.LanguageID, DbType.Int32));
view.DataSource = dt;
view.DataBind();
searchItem1.LoadData = true;
searchItemLL.LoadData = true;
// }
}
}
private void bindData3(int traitID, string value)
{
DataTable dt = null;
using (SecureData sd = new SecureData(false, UserManager.GetLoginToken(true)))
{
using (DataManager dm = sd.BeginProcessing(true, true))
{
string traitName = dm.ReadValue(@"select coded_name from crop_trait where crop_trait_id = :ctid", new DataParameters(":ctid", traitID, DbType.Int32)).ToString();
string cropName = dm.ReadValue(@"select name from crop where crop_id = (select crop_id from crop_trait where crop_trait_id = :ctid)", new DataParameters(":ctid", traitID, DbType.Int32)).ToString();
if (traitName != "")
{
var low = value.Split(';')[0];
var up = value.Split(';')[1];
lblDesc.Text = cropName + " accessions with values between " + low + " and " + up + " for descriptor " + traitName;
dt = sd.GetData("web_descriptor_value_accession3", ":traitid=" + traitID + ";:low=" + low + ";:up=" + up, 0, 0).Tables["web_descriptor_value_accession3"];
gvAccession.DataSource = dt;
gvAccession.DataBind();
}
}
}
}
protected void ddlClass_SelectedIndexChanged(object sender, EventArgs e)
{
string code = Utils.Sanitize(ddlClass.SelectedValue);
string value = ddlClass.SelectedItem.Text;
if (code != "allclass")
{
using (SecureData sd = new SecureData(false, UserManager.GetLoginToken(true)))
{
var dt = sd.GetData("web_lookup_taxon_use_subclass", ":usagecode=" + code, 0, 0).Tables["web_lookup_taxon_use_subclass"];
lstSubclass.DataSource = dt;
lstSubclass.DataBind();
lstSubclass.Items.Insert(0, new ListItem("all uses", "all subclasses"));
lstSubclass.SelectedIndex = 0;
lstSubclass.Rows = 4;
}
}
else
{
lstSubclass.Items.Clear();
lstSubclass.Items.Insert(0, new ListItem("all uses", "all subclasses"));
lstSubclass.Rows = 1;
}
}
private void bindAttachments(int resultID, int?resultAttachID, SecureData sd)
{
var dtAttach = sd.GetData("web_feedback_result_attachments", ":feedbackresultid=" + resultID + ";:feedbackresultattachid=" + resultAttachID, 0, 0).Tables["web_feedback_result_attachments"];
gvAttachments.DataSource = dtAttach;
gvAttachments.DataBind();
}
private void populateShippingAddressList(int wuserid)
{
using (SecureData sd = new SecureData(false, UserManager.GetLoginToken()))
{
// using (DataManager dm = sd.BeginProcessing(true))
// {
// DataTable dt = dm.Read(@"
// select 0 as wusaid, '' as address_name
// union select web_user_shipping_address_id as wusaid, address_name as address_name
// from web_user_shipping_address
// where web_user_id = :wuserid
// order by address_name",
// new DataParameters(":wuserid", wuserid
// ));
DataTable dt = sd.GetData("web_user_shipping_address", ":wuserid=" + wuserid, 0, 0).Tables["web_user_shipping_address"];
if (dt.Rows.Count > 0)
{
ddlShipping.DataValueField = "wusaid";
ddlShipping.DataTextField = "address_name";
ddlShipping.DataSource = dt;
ddlShipping.DataBind();
}
//}
}
}
public Stream Getdata(string dataview, string parameters, string limit)
{
var request = WebOperationContext.Current.IncomingRequest;
var headers = request.Headers;
var JSONString = string.Empty;
try
{
var httpToken = headers["Authorization"].Trim().Replace("Bearer ", "");
using (SecureData sd = new SecureData(false, httpToken))
{
if (string.IsNullOrEmpty(limit))
{
limit = "0";
}
var dsResponse = sd.GetData(dataview, parameters, 0, int.Parse(limit), "");
if (dsResponse != null && dsResponse.Tables.Contains(dataview))
{
JSONString = JsonConvert.SerializeObject(dsResponse.Tables[dataview]);
}
}
}
catch (Exception e)
{
WebOperationContext.Current.OutgoingResponse.StatusCode = System.Net.HttpStatusCode.InternalServerError; //500
JSONString = JsonConvert.SerializeObject(e.Message);
}
WebOperationContext.Current.OutgoingResponse.ContentType = "application/json; charset=utf-8";
return(new MemoryStream(Encoding.UTF8.GetBytes(JSONString)));
}
private DataTable getDataViewDataForCooperator(string dvName, int orderID, int limit)
{
using (var sd = new SecureData(false, UserManager.GetLoginToken(true)))
{
return(sd.GetData(dvName, ":cooperatorid=" + sd.CooperatorID + ";:order_request_id=" + orderID, 0, limit).Tables[dvName]);
}
}
private void bindData(int iprID)
{
using (SecureData sd = new SecureData(false, UserManager.GetLoginToken()))
{
DataTable dt = sd.GetData("web_accessiondetail_ipr_disclaimer", ":accessioniprid=" + iprID, 0, 0).Tables["web_accessiondetail_ipr_disclaimer"];
if (dt.Rows.Count > 0)
{
string description = dt.Rows[0]["description"].ToString();
Regex regx = new Regex("http://([\\w+?\\.\\w+])+([a-zA-Z0-9\\~\\!\\@\\#\\$\\%\\^\\&\\*\\(\\)_\\-\\=\\+\\\\\\/\\?\\.\\:\\;\\'\\,]*)?", RegexOptions.IgnoreCase);
MatchCollection mactches = regx.Matches(description);
foreach (Match match in mactches)
{
string matchURL = match.Value;
if (matchURL.Substring(matchURL.Length - 1, 1) == ")")
{
matchURL = matchURL.Substring(0, matchURL.Length - 1);
}
if (matchURL.Substring(matchURL.Length - 2, 2) == ").")
{
matchURL = matchURL.Substring(0, matchURL.Length - 2);
}
description = description.Replace(match.Value, "<a href='" + matchURL + "'>" + match.Value + "</a>");
}
lblDescription.Text = description;
}
}
}
public DataTable ListDistributionTypes(int accessionID)
{
using (SecureData sd = UserManager.GetSecureData(true)) {
return(sd.GetData("web_lookup_distribution_type", ":accessionid=" + accessionID + ";:langid=" + sd.LanguageID, 0, 0).Tables["web_lookup_distribution_type"]);
// using (var dm = sd.BeginProcessing(true)) {
// var dt = dm.Read(@"
//select
// distinct
// cv.value as value,
// coalesce(cvl.description, cv.value) as display_text
//from
// inventory i
// left join code_value cv
// on cv.group_name = 'GERMPLASM_FORM'
// and cv.value = i.form_type_code
// left join code_value_lang cvl
// on cv.code_value_id = cvl.code_value_id
// and cvl.sys_lang_id = :langid
//where
// i.accession_id = :accessionid
// and i.is_distributable = 'Y'
// and i.is_available = 'Y'
//", new DataParameters(":accessionid", accessionID, DbType.Int32, ":langid", sd.LanguageID, DbType.Int32));
// return dt;
}
}
protected void Page_Load(object sender, EventArgs e)
{
DataTable dt = null;
var accessionID = Toolkit.ToInt32(Request.QueryString["accessionid"], -1); // 390399
if (accessionID < 0)
{
// HACK: just for testing, remove later!
dt = new DataTable();
dt.Columns.Add("thumbnail_virtual_path", typeof(string));
dt.Columns.Add("title", typeof(string));
dt.Columns.Add("virtual_path", typeof(string));
dt.Rows.Add(new object[] { "~/uploads/images/PI_501110_93ncei01_SD_pk.jpg", "some title here", "~/uploads/images/PI_501110_93ncei01_SD_pk_thumbnail.jpg" });
dt.Rows.Add(new object[] { @"~\uploads\images\PI_501111_93ncei01_SD_pk.jpg", "title 2 here", @"~\uploads\images/PI_501111_93ncei01_SD_pk_thumbnail.jpg" });
}
else
{
using (var sd = new SecureData(false, UserManager.GetLoginToken())) {
dt = sd.GetData("web_accessiondetail_inventory_image", ":accessionid=" + accessionID, 0, 0).Tables["web_accessiondetail_inventory_image"];
}
}
if (dt.Rows.Count > 0)
{
imagePreviewer.DataSource = dt;
imagePreviewer.DataBind();
}
else
{
imagePreviewer.Visible = false;
}
}
private DataTable getDataViewData(string dvName, int id)
{
using (var sd = new SecureData(false, UserManager.GetLoginToken()))
{
return(sd.GetData(dvName, ":accessionid=" + id, 0, 0).Tables[dvName]);
}
}
private void bindCitation(int id)
{
pnlLiterature.Visible = false;
using (SecureData sd = UserManager.GetSecureData(true))
{
using (DataManager dm = sd.BeginProcessing(true, true))
{
DataTable dt = dm.Read(@"
select c.*, l.abbreviation from citation c left join literature l on c.literature_id = l.literature_id where citation_id = :cid",
new DataParameters(":cid", id));
if (dt.Rows.Count > 0)
{
pnlCitation.Visible = true;
DataRow dr = dt.Rows[0];
lblCitation.Text = dr["author_name"] + "<br />" + dr["citation_title"] + "<br />" + dr["abbreviation"] + " " + dr["reference"] + " " + dr["citation_year"] + "<br />";
}
rptCitation.DataSource = sd.GetData("web_crop_citation_accession", ":citationid=" + id, 0, 0).Tables["web_crop_citation_accession"];
rptCitation.DataBind();
}
}
}
public void bindData()
{
using (SecureData sd = new SecureData(false, UserManager.GetLoginToken(true)))
{
var dt = sd.GetData("web_searchcriteria_item_list", "", 0, 0).Tables["web_searchcriteria_item_list"];
var drNone = dt.NewRow();
drNone["group_name"] = " -- Select One -- ";
drNone["name"] = "none";
dt.Rows.InsertAt(drNone, 0);
//foreach (DataRow dr in dt.Rows)
//{
// dr["group_name"] = dr["group_name"].ToString().ToLower().Replace("_", " ");
//}
bool isInternal = (Page.User.IsInRole("ALLUSERS"));
if (isInternal)
{
var drOrder = dt.NewRow();
drOrder["group_name"] = "order request id";
drOrder["name"] = "@order_request.order_request_id";
dt.Rows.Add(drOrder);
}
ddlItem.DataSource = dt;
ddlItem.DataBind();
}
//lblChoose.Text = "Choose Query " + sequence + ":";
//btnClear.Text = "Clear Criteria " + sequence;
lblChoose.Text = Site1.DisplayText("lblChooseN", "Choose Criterion") + " " + sequence + ":";
btnClear1.Text = Site1.DisplayText("btnClearN", "Clear Criterion") + " " + sequence;
}
// for other queries (accession_source)
private DataTable getDataViewData(string dvName, string dataParams, int limit)
{
using (var sd = new SecureData(false, UserManager.GetLoginToken(true)))
{
return(sd.GetData(dvName, dataParams, 0, limit).Tables[dvName]);
}
}
private void bindLists()
{
using (SecureData sd = new SecureData(false, UserManager.GetLoginToken(true)))
{
// CWR crop list
var dt = sd.GetData("web_lookup_taxon_crop", "", 0, 0).Tables["web_lookup_taxon_crop"];
lstCrop.DataSource = dt;
lstCrop.DataBind();
lstCrop.Items.Insert(0, new ListItem("ALL", "ALL"));
lstCrop.SelectedIndex = 0;
// Taxon family list
dt = sd.GetData("web_lookup_taxon_family_cwr", "", 0, 0).Tables["web_lookup_taxon_family_cwr"];
lstFamily.DataSource = dt;
lstFamily.DataBind();
lstFamily.Items.Insert(0, new ListItem("ALL FAMILIES", "0"));
lstFamily.Items.Insert(1, new ListItem("all pteridophytes", "ferns"));
lstFamily.Items.Insert(2, new ListItem("all gymnosperms", "gymno"));
lstFamily.Items.Insert(3, new ListItem("all angiosperms", "angio"));
lstFamily.SelectedIndex = 0;
// Continent list
dt = sd.GetData("web_lookup_continent", "", 0, 0).Tables["web_lookup_continent"];
ddlContinent.DataSource = dt;
ddlContinent.DataBind();
// Country list
dt = sd.GetData("web_lookup_country_taxon", "", 0, 0).Tables["web_lookup_country_taxon"];
lstCountry.DataSource = dt;
lstCountry.DataBind();
lstCountry.Items.Insert(0, new ListItem("ALL COUNTRIES", "0:0"));
lstCountry.SelectedIndex = 0;
// Repository list
dt = sd.GetData("web_lookup_site_list", "", 0, 0).Tables["web_lookup_site_list"];
lstRepository.DataSource = dt;
lstRepository.DataBind();
lstRepository.Items.Insert(0, new ListItem("ALL", "0"));
lstRepository.SelectedIndex = 0;
}
}
private void bindActionNote(int id)
{
using (var sd = new SecureData(false, UserManager.GetLoginToken(true)))
{
DataTable dt = sd.GetData("web_accessiondetail_action_note", ":actionName=" + "select distinct action_name_code from accession_action where is_web_visible = 'Y' and action_name_code != 'AVAIL_CMT'" + ";:accessionid=" + id, 0, 0).Tables["web_accessiondetail_action_note"];
showAndBind(plActionNote, rptActionNote, dt);
}
}
public DataSet GetData(bool suppressExceptions, string userName, string password, string dataviewName, string delimitedParameterList, int offset, int limit, string options)
{
using (SecureData sd = new SecureData(suppressExceptions, Login(userName, password))) {
DataSet ds = sd.GetData(dataviewName, delimitedParameterList, offset, limit, options);
//string ua = HttpContext.Current.Request.UserAgent;
//ds.RemotingFormat = SerializationFormat.Binary;
return(ds);
}
}
private void bindImages(SecureData sd, int taxonomyfamilyID) //temp query now
{
var dt = sd.GetData("web_taxonomyfamily_images", ":taxonomyfamilyid=" + taxonomyfamilyID, 0, 0).Tables["web_taxonomyfamily_images"];
if (dt.Rows.Count > 0)
{
pnlImages.Visible = true;
rptImages.DataSource = dt;
rptImages.DataBind();
}
}
private void bindCheckOther(SecureData sd, int taxonomyfamilyID)//temp query now
{
var dt = sd.GetData("web_taxonomyfamily_checkother", ":taxonomyfamilyid=" + taxonomyfamilyID, 0, 0).Tables["web_taxonomyfamily_checkother"];
if (dt.Rows.Count > 0)
{
pnlCheckOther.Visible = true;
rptCheckOther.DataSource = dt;
rptCheckOther.DataBind();
}
}
private void bindSynonyms(SecureData sd, int taxonomyfamilyID)
{
var dt = sd.GetData("web_taxonomyfamily_synonyms", ":taxonomyfamilyid=" + taxonomyfamilyID, 0, 0).Tables["web_taxonomyfamily_synonyms"];
if (dt.Rows.Count > 0)
{
pnlSynonyms.Visible = true;
rptSynonyms.DataSource = dt;
rptSynonyms.DataBind();
}
}
public static void FillWebLookupDropDown(this MasterPage mp, DropDownList ddl, SecureData sd)
{
ddl.DataSource = null;
ddl.DataTextField = "title";
ddl.DataValueField = "dataview_name";
var dt = sd.GetData("web_lookup_list", null, 0, 0).Tables["web_lookup_list"];
ddl.DataSource = dt;
ddl.DataBind();
}
/// <summary>
/// Lists all code groups
/// </summary>
/// <param name="mp"></param>
/// <param name="ddl"></param>
/// <param name="sd"></param>
public static void FillCodeGroupDropDown(this MasterPage mp, DropDownList ddl, SecureData sd)
{
ddl.DataSource = null;
ddl.DataTextField = "display_member";
ddl.DataValueField = "value_member";
var dt = sd.GetData("group_name_lookup", null, 0, 0).Tables["group_name_lookup"];
ddl.DataSource = dt;
ddl.DataBind();
}
/// <summary>
/// Lists all values within the given code group
/// </summary>
/// <param name="mp"></param>
/// <param name="ddl"></param>
/// <param name="groupName"></param>
/// <param name="sd"></param>
public static void FillCodeGroupDropDown(this MasterPage mp, DropDownList ddl, string groupName, SecureData sd)
{
ddl.DataSource = null;
ddl.DataTextField = "display_member";
ddl.DataValueField = "value_member";
var dt = sd.GetData("importlist_code_value", ":groupname=" + groupName + ";:langid=" + sd.LanguageID, 0, 0).Tables["importlist_code_value"];
ddl.DataSource = dt;
ddl.DataBind();
}