protected int UpdateCitation(PubIdTitle pc)
{
//string connectionStr = ConfigurationManager.AppSettings.Get("ConnectionString");
string connectionStr = ConfigurationManager.ConnectionStrings["UcccPubMedDB"].ConnectionString;
SqlConnection conn = new SqlConnection(connectionStr);
string sqlStatement;
int citation = Helper.ReadCitation(pc.articleTitle);
if (citation == -1)
{
return(-1);
}
DateTime today = DateTime.Now;
sqlStatement = "update publication_processing" +
" set citation = @citation" +
", citation_date = @citation_date" +
" where publication_id = @publication_id";
SqlCommand command = new SqlCommand(sqlStatement, conn);
SqlParameter publication_idParameter = new SqlParameter();
publication_idParameter.ParameterName = "@publication_id";
publication_idParameter.SqlDbType = SqlDbType.Int;
publication_idParameter.Value = pc.publcationId;
command.Parameters.Add(publication_idParameter);
SqlParameter citationParameter = new SqlParameter();
citationParameter.ParameterName = "@citation";
citationParameter.SqlDbType = SqlDbType.Int;
citationParameter.Value = citation;
command.Parameters.Add(citationParameter);
SqlParameter citation_dateParameter = new SqlParameter();
citation_dateParameter.ParameterName = "@citation_date";
citation_dateParameter.SqlDbType = SqlDbType.DateTime;
citation_dateParameter.Value = today;
command.Parameters.Add(citation_dateParameter);
conn.Open();
command.ExecuteNonQuery();
conn.Close();
return(citation);
}
protected void btnCitation_Click(object sender, EventArgs e)
{
ErrorMessage.Text = "";
//string connectionStr = ConfigurationManager.AppSettings.Get("ConnectionString");
string connectionStr = ConfigurationManager.ConnectionStrings["UcccPubMedDB"].ConnectionString;
SqlConnection conn = new SqlConnection(connectionStr);
string sqlStatement;
string startDate = "";
string endDate = "";
if (txtStartDate.Text != "")
{
startDate = txtStartDate.Text;
}
else
{
ErrorMessage.Text = "Please give start date.";
return;
}
if (txtEndDate.Text != "")
{
endDate = txtEndDate.Text;
}
else
{
ErrorMessage.Text = "Please give end date.";
return;
}
string clientIdStr = "";
if (ddlMember.SelectedIndex != 0 && ddlMember.SelectedIndex != -1)
{
clientIdStr = ddlMember.SelectedValue.ToString();
}
else
{
ErrorMessage.Text = "Please select member.";
return;
}
sqlStatement =
"select pd.publication_id," +
" p.article_title" +
" from publication_processing pd" +
" inner join publication p" +
" on pd.publication_id = p.publication_id" +
" inner join publication_author pa" +
" on pd.publication_id = pa.publication_id" +
" inner join author a" +
" on pa.author_id = a.author_id" +
" and a.client_id = " +
clientIdStr +
" where ((pd.publication_date >= '" +
startDate +
"' and pd.publication_date <= '" +
endDate +
"')) order by p.article_title";
/*
* sqlStatement =
* "select pd.publication_id," +
* " p.article_title" +
* " from publication_processing pd" +
* " inner join publication p" +
* " on pd.publication_id = p.publication_id" +
* " inner join publication_author pa" +
* " on pd.publication_id = pa.publication_id" +
* " inner join author a" +
* " on pa.author_id = a.author_id" +
* " and a.client_id = " +
* clientIdStr +
* " where pd.publication_date >= '" +
* startDate +
* "' and pd.publication_date <= '" +
* endDate +
* "'";
*/
SqlCommand myCommand = new SqlCommand(sqlStatement, conn);
conn.Open();
SqlDataReader myReader;
myReader = myCommand.ExecuteReader();
List <PubIdTitle> idTitleList = new List <PubIdTitle>();
try
{
while (myReader.Read())
{
PubIdTitle pc = new PubIdTitle();
pc.publcationId = Convert.ToInt32(myReader["publication_id"]);
pc.articleTitle = myReader["article_title"].ToString();
idTitleList.Add(pc);
}
}
finally
{
myReader.Close();
conn.Close();
}
int citationGot = 0;
foreach (PubIdTitle pc in idTitleList)
{
int citation = UpdateCitation(pc);
if (citation != -1)
{
citationGot++;
}
}
int totalPub = idTitleList.Count;
//string msg = "Among " + totalPub.ToString() + ", successfully got " + citationGot + " citation counts.";
//ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('" + msg + "');", true);
HttpCookie _dateCookies = new HttpCookie("dates");
_dateCookies["startDate"] = txtStartDate.Text;
_dateCookies["endDate"] = txtEndDate.Text;
_dateCookies.Expires = DateTime.Now.AddDays(5);
Response.Cookies.Add(_dateCookies);
GetCitationStat();
}
