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; } }