private static string getOrderBy(DataToQuerySelect queryData)
{
string orderBy = "";
try
{
foreach (FieldsToOrder field in queryData.orderBy)
{
if (String.IsNullOrEmpty(orderBy))
{
orderBy = field.filedName + " " + field.orderType;
}
else
{
orderBy = orderBy + ", " + field.filedName + " " + field.orderType;;
}
}
if (!String.IsNullOrEmpty(orderBy))
{
orderBy = " Order By " + orderBy;
}
}
catch
{
orderBy = "";
}
return(orderBy);
}
public static DataTable dataBaseSelect(DataToQuerySelect queryData)
{
DataTable table = new DataTable();
string fields = getFieldsForSelect(queryData);
string limit = getLimit(queryData);
string orderBy = getOrderBy(queryData);
string conditions = getConditionsForSelect(queryData);
string query = "Select " + fields + " From " + queryData.tableName + conditions + orderBy + limit + ";";
try
{
if (connected == true)
{
NpgsqlCommand command = new NpgsqlCommand(query, dataBaseConnection);
NpgsqlDataAdapter data = new NpgsqlDataAdapter(command);
data.Fill(table);
}
else
{
table = null;
}
}
catch (Exception)
{
table = null;
}
return(table);
}
public static int getWinnigNumber(int roulette_bet_id)
{
DataTable table = new DataTable();
DataToQuerySelect queryData = new DataToQuerySelect();
int winningNumber = 0;
try
{
queryData.tableName = "roulette_bets";
List <string> fields = new List <string>();
fields.Add("winner_number");
queryData.fields = fields;
Conditions condition = new Conditions();
List <Conditions> conditionsList = new List <Conditions>();
condition.filedName = "id";
condition.condition = "=";
condition.value = roulette_bet_id.ToString();
conditionsList.Add(condition);
queryData.conditions = conditionsList;
dataBaseConnect();
table = dataBaseSelect(queryData);
dataBaseDesconnect();
winningNumber = Int32.Parse(findAFieldValueInTable(table: table, fieldName: "winner_number", lineNum: 0));
}
catch
{
winningNumber = 0;
}
return(winningNumber);
}
public static int getRouletteBetID(int roullete_id)
{
int id = 0;
DataToQuerySelect queryData = new DataToQuerySelect();
try
{
queryData.tableName = "roulette_bets";
List <string> fields = new List <string>();
fields.Add("*");
queryData.fields = fields;
Conditions condition = new Conditions();
List <Conditions> conditionsList = new List <Conditions>();
condition.filedName = "roullete_id";
condition.condition = "=";
condition.value = roullete_id.ToString();
conditionsList.Add(condition);
condition.filedName = "state_id";
condition.condition = "=";
condition.value = State.getID("open");
conditionsList.Add(condition);
queryData.conditions = conditionsList;
dataBaseConnect();
DataTable table = dataBaseSelect(queryData);
dataBaseDesconnect();
id = Int32.Parse(findAFieldValueInTable(table: table, fieldName: "id", lineNum: 0));
}
catch
{
id = 0;
}
return(id);
}
public static DataTable getWinnersByNumber(int roulette_bet_id, int number)
{
DataTable table = new DataTable();
DataToQuerySelect queryData = new DataToQuerySelect();
try
{
queryData.tableName = "bet_by_number";
List <string> fields = new List <string>();
fields.Add("user_id");
fields.Add("amount");
fields.Add("'number' \"betType\"");
queryData.fields = fields;
Conditions condition = new Conditions();
List <Conditions> conditionsList = new List <Conditions>();
condition.filedName = "roulette_bets_id";
condition.condition = "=";
condition.value = roulette_bet_id.ToString();
conditionsList.Add(condition);
condition.filedName = "number";
condition.condition = "=";
condition.value = number.ToString();
conditionsList.Add(condition);
queryData.conditions = conditionsList;
dataBaseConnect();
table = dataBaseSelect(queryData);
dataBaseDesconnect();
}
catch
{
table = null;
}
return(table);
}
private int getCreatedId()
{
int createdID = 0;
DataToQuerySelect queryData = new DataToQuerySelect();
try
{
queryData.tableName = "roulettes";
queryData.limit = "1";
List <string> fields = new List <string>();
fields.Add("id");
queryData.fields = fields;
FieldsToOrder field = new FieldsToOrder();
List <FieldsToOrder> orderBy = new List <FieldsToOrder>();
field.filedName = "id";
field.orderType = "desc";
orderBy.Add(field);
queryData.orderBy = orderBy;
dataBaseConnect();
DataTable table = dataBaseSelect(queryData);
dataBaseDesconnect();
if (table != null)
{
createdID = Int32.Parse(findAFieldValueInTable(table, "id", 0));
}
}
catch
{
errorInConsult = true;
}
return(createdID);
}
public static string getID(string state)
{
string stateResult = "";
DataToQuerySelect queryData = new DataToQuerySelect();
try
{
queryData.tableName = "states";
List <string> fields = new List <string>();
fields.Add("id");
queryData.fields = fields;
Conditions condition = new Conditions();
List <Conditions> conditionsList = new List <Conditions>();
condition.filedName = "state";
condition.condition = "=";
condition.value = state;
conditionsList.Add(condition);
queryData.conditions = conditionsList;
dataBaseConnect();
DataTable table = dataBaseSelect(queryData);
dataBaseDesconnect();
stateResult = findAFieldValueInTable(table: table, fieldName: "id", lineNum: 0);
}
catch
{
stateResult = "NotFound";
}
return(stateResult);
}
public List <Dictionary <string, string> > ToList()
{
DataToQuerySelect queryData = new DataToQuerySelect();
List <Dictionary <string, string> > response = new List <Dictionary <string, string> >();
try
{
queryData.tableName = "roulettes";
List <string> fields = new List <string>();
fields.Add("id");
fields.Add("state_id");
queryData.fields = fields;
FieldsToOrder field = new FieldsToOrder();
List <FieldsToOrder> orderBy = new List <FieldsToOrder>();
field.filedName = "id";
field.orderType = "asc";
orderBy.Add(field);
queryData.orderBy = orderBy;
dataBaseConnect();
DataTable table = dataBaseSelect(queryData);
dataBaseDesconnect();
response = getRoulettes(table);
}
catch
{
response = null;
}
return(response);
}
private static string getLimit(DataToQuerySelect queryData)
{
string top = "";
if (!String.IsNullOrEmpty(queryData.limit))
{
top = " limit " + queryData.limit + " ";
}
return(top);
}
private static string getFieldsForSelect(DataToQuerySelect queryData)
{
string fields = "";
foreach (string field in queryData.fields)
{
if (String.IsNullOrEmpty(fields))
{
fields = field;
}
else
{
fields = fields + ", " + field;
}
}
return(fields);
}
private static string getConditionsForSelect(DataToQuerySelect queryData)
{
string condition = "";
string resultConditions = "";
try
{
foreach (Conditions conditions in queryData.conditions)
{
switch (conditions.condition)
{
case "=":
condition = conditions.filedName + " " + conditions.condition + " '" + conditions.value + "'";
break;
default:
condition = conditions.filedName + " " + conditions.condition + " " + conditions.value;
break;
}
if (String.IsNullOrEmpty(resultConditions))
{
resultConditions = condition;
}
else
{
resultConditions = resultConditions + " and " + condition;
}
condition = "";
}
if (!String.IsNullOrEmpty(resultConditions))
{
resultConditions = " Where " + resultConditions;
}
}
catch
{
resultConditions = "";
}
return(resultConditions);
}
public bool rouletteExists()
{
bool exists;
DataToQuerySelect queryData = new DataToQuerySelect();
try
{
queryData.tableName = "roulettes";
List <string> fields = new List <string>();
fields.Add("*");
queryData.fields = fields;
Conditions condition = new Conditions();
List <Conditions> conditionsList = new List <Conditions>();
condition.filedName = "id";
condition.condition = "=";
condition.value = id.ToString();
conditionsList.Add(condition);
queryData.conditions = conditionsList;
dataBaseConnect();
DataTable table = dataBaseSelect(queryData);
dataBaseDesconnect();
if (table != null && table.Rows.Count > 0)
{
exists = true;
}
else
{
exists = false;
}
}
catch
{
errorInConsult = true;
exists = false;
}
return(exists);
}
public bool loadRoulette(int _id)
{
DataToQuerySelect queryData = new DataToQuerySelect();
id = _id;
bool loaded = false;
bool exists = rouletteExists();
if (exists)
{
queryData.tableName = "roulettes";
List <string> fields = new List <string>();
fields.Add("state_id");
fields.Add("last_number");
queryData.fields = fields;
Conditions condition = new Conditions();
List <Conditions> conditionsList = new List <Conditions>();
condition.filedName = "id";
condition.condition = "=";
condition.value = id.ToString();
conditionsList.Add(condition);
queryData.conditions = conditionsList;
dataBaseConnect();
DataTable table = dataBaseSelect(queryData);
dataBaseDesconnect();
last_number = Int32.Parse(findAFieldValueInTable(table: table, fieldName: "last_number", lineNum: 0));
state = findAFieldValueInTable(table: table, fieldName: "state_id", lineNum: 0);
state = State.getState(state);
loaded = true;
}
else
{
id = 0;
}
return(loaded);
}
