public List <OptionModel> SearchOptionByProposal(string contracEthereumProposal)
{
try
{
string query = "SELECT IdOption, " +
"ContracEthereumProposal, " +
"Description " +
"FROM `Option` o " +
"WHERE o.ContracEthereumProposal='" + contracEthereumProposal + "'";
List <OptionModel> options = new List <OptionModel>();
MySqlDataReader reader = ExecuteQuery(query);
while (reader.Read())
{
OptionModel option = new OptionModel();
option.Description = reader["Description"].ToString();
option.IdOption = Convert.ToInt32(reader["IdOption"]);
options.Add(option);
}
return(options);
}
catch (Exception err)
{
throw err;
}
}
public List <ProposalModel> SearchProposal(string ethereumContractUser, string ProposalName)
{
try
{
string query = "SELECT Proposal.ContracEthereumProposal," +
"Proposal.ProposalName," +
"Proposal.SecurityType," +
"Proposal.QuestionType," +
"Proposal.ProposalName," +
"Proposal.InitialDate, " +
"Proposal.FinalDate," +
"Proposal.AdvancedSearch, " +
"Proposal.Description, " +
"Proposal.UserCreator," +
"Proposal.MinimumQuantitySelected, " +
"Proposal.MaximumQuantitySelected " +
"FROM dbEthereumDemo.Proposal ";
StringBuilder criteria = new StringBuilder();
if (ethereumContractUser != null)
{
if (criteria.ToString() == string.Empty)
{
query += " Where ";
}
else
{
criteria.Append(" AND ");
}
query += ("ContracEthereumProposal = '" + ethereumContractUser + "'");
}
if (ProposalName != null)
{
if (criteria.ToString() == string.Empty)
{
criteria.Append(" Where ");
}
else
{
criteria.Append(" AND ");
}
criteria.Append("ProposalName='" + ProposalName + "'");
}
List <ProposalModel> proposals = new List <ProposalModel>();
using (MySqlDataReader reader = ExecuteQuery(query))
{
while (reader.Read())
{
ProposalModel proposal = new ProposalModel();
proposal.ContracEthereumProposal = reader["ContracEthereumProposal"].ToString();
proposal.ProposalName = reader["ProposalName"].ToString();
proposal.SecurityType = (EnumSecurityType)Convert.ToInt32(reader["SecurityType"]);
proposal.QuestionType = (EnumQuestionType)Convert.ToInt32(reader["QuestionType"]);
proposal.InitialDate = Convert.ToDateTime(reader["InitialDate"]);
proposal.FinalDate = Convert.ToDateTime(reader["FinalDate"]);
proposal.AdvancedSearch = Convert.ToInt32(reader["AdvancedSearch"]);
proposal.Description = reader["Description"].ToString();
proposal.MinimumQuantitySelected = Convert.ToInt32(reader["MinimumQuantitySelected"]);
proposal.MaximumQuantitySelected = Convert.ToInt32(reader["MaximumQuantitySelected"]);
proposal.UserCreator = reader["UserCreator"].ToString();
proposal.Options = new List <OptionModel>();
using (DataAccess con = new DataAccess())
{
string quer = "SELECT IdOption, " +
"ContracEthereumProposal, " +
"Description " +
"FROM `Option` o " +
"WHERE o.ContracEthereumProposal='" + proposal.ContracEthereumProposal + "'";
using (MySqlDataReader r = con.ExecuteQuery(quer))
{
while (r.Read())
{
OptionModel option = new OptionModel()
{
Description = r["Description"].ToString(),
IdOption = Convert.ToInt32(r["IdOption"])
};
proposal.Options.Add(option);
}
r.Close();
}
proposals.Add(proposal);
}
proposals.Add(proposal);
}
}
return(proposals);
}
catch (Exception err)
{
throw err;
}
}
public List <ProposalModel> SearchProposalByUser(string email, EnumQuestionType?questionType)
{
try
{
string query = "SELECT p.ContracEthereumProposal," +
"p.ProposalName," +
"p.SecurityType," +
"p.QuestionType," +
"p.InitialDate," +
"p.FinalDate," +
"p.AdvancedSearch," +
"p.ContracEthereumProposal," +
"p.Description, " +
"p.MinimumQuantitySelected," +
"p.MaximumQuantitySelected, " +
"p.UserCreator, " +
"CASE WHEN p.UserCreator=pu.Email THEN 1 else 0 END 'IsMine', " +
"pu.IsVoted " +
"FROM Proposal p INNER JOIN ProposalUser pu " +
"ON p.ContracEthereumProposal=pu.ContracEthereumProposal " +
" INNER JOIN User u ON pu.Email=u.Email " +
"WHERE u.Email = '" + email + "' ";
// "AND NOW() BETWEEN p.InitialDate AND p.FinalDate;";
if (questionType != null)
{
query += " AND QuestionType=" + questionType.ToString();
}
List <ProposalModel> proposals = new List <ProposalModel>();
using (MySqlDataReader reader = ExecuteQuery(query))
{
while (reader.Read())
{
ProposalModel proposal = new ProposalModel();
proposal.ContracEthereumProposal = reader["ContracEthereumProposal"].ToString();
proposal.ProposalName = reader["ProposalName"].ToString();
proposal.SecurityType = (EnumSecurityType)Convert.ToInt32(reader["SecurityType"]);
proposal.QuestionType = (EnumQuestionType)Convert.ToInt32(reader["QuestionType"]);
proposal.InitialDate = Convert.ToDateTime(reader["InitialDate"]);
proposal.FinalDate = Convert.ToDateTime(reader["FinalDate"]);
proposal.AdvancedSearch = Convert.ToInt32(reader["AdvancedSearch"]);
proposal.Description = reader["Description"].ToString();
proposal.IsVoted = Convert.ToBoolean(reader["IsVoted"]);
proposal.MinimumQuantitySelected = Convert.ToInt32(reader["MinimumQuantitySelected"]);
proposal.MaximumQuantitySelected = Convert.ToInt32(reader["MaximumQuantitySelected"]);
proposal.UserCreator = reader["UserCreator"].ToString();
proposal.IsMine = Convert.ToBoolean(reader["IsMine"]);
proposal.Options = new List <OptionModel>();
using (DataAccess con = new DataAccess())
{
string quer = "SELECT IdOption, " +
"ContracEthereumProposal, " +
"Description " +
"FROM `Option` o " +
"WHERE o.ContracEthereumProposal='" + proposal.ContracEthereumProposal + "'";
using (MySqlDataReader r = con.ExecuteQuery(quer))
{
while (r.Read())
{
OptionModel option = new OptionModel()
{
Description = r["Description"].ToString(),
IdOption = Convert.ToInt32(r["IdOption"])
};
proposal.Options.Add(option);
}
r.Close();
}
proposals.Add(proposal);
}
}
reader.Close();
}
return(proposals);
}
catch (Exception err)
{
throw err;
}
}
