// 3+4+5+6+7 DBに接続してパスワードチェック
private bool CheckPassword()
{
MySQLUtil.DBConnection();
string selectSql = $"SELECT Password FROM {database}.{userTable} WHERE Name=@Name ;";
string[] parameters = new string[] { "Name", _localData.TeamName };
DataTable tbl = MySQLUtil.DBSelect(selectSql, parameters);
// 3.exist data?
if (tbl.Rows.Count == 1)
{
// yes
foreach (DataRow row in tbl.Rows)
{
if (_localData.Password == row[0].ToString())
{
// 7.check ok
return(true);
}
else
{
// 5. local data delete
MySQLUtil.DBClose();
_localData.Password = "";
LocalDataExport();
EditorUtility.DisplayDialog("パスワードチェックエラー", "パスワードが合いません\r\n再入力をお願いします", "OK");
// Debug.Log("password error");
return(false);
}
}
}
else
{
// no 6. db insert
string insertSql = $"insert into {database}.{userTable} (Name ,Password) " +
" value ( @Name , @Password );";
parameters = new string[]
{
"Name", _localData.TeamName,
"Password", _localData.Password
};
MySQLUtil.DBInsert(insertSql, parameters);
return(true);
}
return(false);
}
private void MakePublicData()
{
_publicData = ScriptableObject.CreateInstance <PublicData>();
MySQLUtil.DBConnection();
string selectPublicData = $"SELECT Name,UpdateTime,Progress " +
$"FROM {database}.{table} " +
$"ORDER BY Name ASC,UpdateTime ASC,Progress ASC;";
string[] parameters = { };
DataTable tbl = MySQLUtil.DBSelect(selectPublicData, parameters);
PublicDataStruct[] publicDataStructs = new PublicDataStruct[] { };
foreach (DataRow row in tbl.Rows)
{
PublicDataStruct publicDataStruct = new PublicDataStruct();
publicDataStruct.TeamName = row["Name"].ToString();
publicDataStruct.Progress = (int)row["Progress"];
publicDataStruct.DateTime = (DateTime)row["UpdateTime"];
if (_publicData.publicDatas.ContainsKey(row["Name"].ToString()))
{
Array.Resize(ref publicDataStructs, publicDataStructs.Length + 1);
publicDataStructs[publicDataStructs.Length - 1] = publicDataStruct;
}
else
{
//length1
publicDataStructs = new PublicDataStruct[] { publicDataStruct };
}
if (_publicData.publicDatas.ContainsKey(row["Name"].ToString()))
{
_publicData.publicDatas.Remove(row["Name"].ToString());
}
_publicData.publicDatas.Add(row["Name"].ToString(), publicDataStructs);
}
if (_localData != null)
{
MakeUserPoint();
}
MySQLUtil.DBClose();
}
private void MakeUserPoint()
{
string selectUserProgress = $"SELECT UpdateTime,Progress " +
$"FROM {database}.{table} " +
$"WHERE Name=@Name " +
$"ORDER BY UpdateTime ASC;";
string[] parameters = new string[] { "Name", _localData.TeamName };
DataTable tbl = MySQLUtil.DBSelect(selectUserProgress, parameters);
_userPoint = new Vector2[] { new Vector2(0f, 0f) };
foreach (DataRow row in tbl.Rows)
{
Array.Resize(ref _userPoint, _userPoint.Length + 1);
float x = 48f - ((float)(_endDt - ((DateTime)row[0])).TotalMinutes / 60f);
float y = float.Parse(row[1].ToString());
_userPoint[_userPoint.Length - 1] = new Vector2(x, y);
// Debug.Log(((_endDt-((DateTime)row[0])).TotalMinutes)/60);
}
}
void Awake()
{
try
{
if (_localData == null)
{
_localData = ScriptableObject.CreateInstance <LocalData>();
}
LocalDataImport();
if (_rankingData == null)
{
_rankingData = ScriptableObject.CreateInstance <RankingData>();
}
RankingDataImport();
MySQLUtil.DBConnection();
string _selectUserProgress =
"select tbl1.Name ,tbl1.UpdateTime,tbl1.Progress,tbl1.URL,tbl1.Comment,tbl1.Title " +
"FROM Cluster.GameJam2020inWinter as tbl1 " +
"INNER JOIN ( "+
" SELECT tbl2.Name,UpdateTime ,MAX(Progress) as MAXProgress " +
"FROM Cluster.GameJam2020inWinter " +
" INNER JOIN ( " +
"SELECT Name, MAX(UpdateTime) as LastTime " +
"FROM Cluster.GameJam2020inWinter " +
"GROUP BY Name " +
")as tbl2 " +
"Where UpdateTime = tbl2.LastTime " +
"GROUP BY Name "+
") as tbl3 " +
"where tbl1.UpdateTime = tbl3.UpdateTime and Progress =tbl3.MAXProgress AND tbl1.Name =tbl3.Name " +
"ORDER BY Progress DESC,tbl1.UpdateTime ASC " +
"; ";
DataTable tbl = MySQLUtil.DBSelect(_selectUserProgress);
foreach (DataRow row in tbl.Rows)
{
if (_rankingData.rankingData.ContainsKey(row["Name"].ToString()))
{
_rankingData.rankingData[row["Name"].ToString()]._progress = (int)row["Progress"];
_rankingData.rankingData[row["Name"].ToString()]._comment = row["Comment"].ToString();
_rankingData.rankingData[row["Name"].ToString()]._url = row["URL"].ToString();
_rankingData.rankingData[row["Name"].ToString()]._title = row["Title"].ToString();
_rankingData.rankingData[row["Name"].ToString()]._dateTime = (DateTime)row["UPdateTime"];
}
else
{
RankingDataStruct rankingDataStruct = new RankingDataStruct();
rankingDataStruct._progress = (int)row["Progress"];
rankingDataStruct._comment = row["Comment"].ToString();
rankingDataStruct._url = row["URL"].ToString();
rankingDataStruct._title = row["Title"].ToString();
rankingDataStruct._dateTime = (DateTime)row["UPdateTime"];
_rankingData.rankingData.Add(row["Name"].ToString(), rankingDataStruct);
}
}
MySQLUtil.DBClose();
}
catch (Exception e)
{
Debug.Log("error");
Debug.Log(e);
MySQLUtil.DBClose();
}
}