public EquipWeapons ReadCharacterEquipWeapons(string characterId)
{
EquipWeapons result = new EquipWeapons();
// Right hand weapon
SQLiteRowsReader reader = ExecuteReader("SELECT * FROM characteritem WHERE characterId=@characterId AND inventoryType=@inventoryType LIMIT 1",
new SqliteParameter("@characterId", characterId),
new SqliteParameter("@inventoryType", (byte)InventoryType.EquipWeaponRight));
CharacterItem rightWeapon;
if (ReadCharacterItem(reader, out rightWeapon))
{
result.rightHand = rightWeapon;
}
// Left hand weapon
reader = ExecuteReader("SELECT * FROM characteritem WHERE characterId=@characterId AND inventoryType=@inventoryType LIMIT 1",
new SqliteParameter("@characterId", characterId),
new SqliteParameter("@inventoryType", (byte)InventoryType.EquipWeaponLeft));
CharacterItem leftWeapon;
if (ReadCharacterItem(reader, out leftWeapon))
{
result.leftHand = leftWeapon;
}
return(result);
}
public override bool IncreaseGuildExp(int id, int increaseExp, int[] expTree, out short resultLevel, out int resultExp, out short resultSkillPoint)
{
resultLevel = 1;
resultExp = 0;
resultSkillPoint = 0;
SQLiteRowsReader reader = ExecuteReader("UPDATE guild SET exp=exp+@increaseExp WHERE id=@id;" +
"SELECT level, exp, skillPoint FROM guild WHERE id=@id LIMIT 1;",
new SqliteParameter("@increaseExp", increaseExp),
new SqliteParameter("@id", id));
if (reader.Read())
{
resultLevel = (short)reader.GetInt32("level");
resultExp = reader.GetInt32("exp");
resultSkillPoint = (short)reader.GetInt32("skillPoint");
// Update when guild level is increase
if (SocialSystemSetting.CalculateIncreasedGuildExp(expTree, resultLevel, resultExp, resultSkillPoint, out resultLevel, out resultExp, out resultSkillPoint))
{
ExecuteNonQuery("UPDATE guild SET level=@level, exp=@exp, skillPoint=@skillPoint WHERE id=@id",
new SqliteParameter("@level", resultLevel),
new SqliteParameter("@exp", resultExp),
new SqliteParameter("@skillPoint", resultSkillPoint),
new SqliteParameter("@id", id));
}
// Return true if success
return(true);
}
return(false);
}
public override PartyData ReadParty(int id)
{
PartyData result = null;
SQLiteRowsReader reader = ExecuteReader("SELECT * FROM party WHERE id=@id LIMIT 1",
new SqliteParameter("@id", id));
if (reader.Read())
{
result = new PartyData(id, reader.GetBoolean("shareExp"), reader.GetBoolean("shareItem"), reader.GetString("leaderId"));
reader = ExecuteReader("SELECT id, dataId, characterName, level FROM characters WHERE partyId=@id",
new SqliteParameter("@id", id));
SocialCharacterData partyMemberData;
while (reader.Read())
{
// Get some required data, other data will be set at server side
partyMemberData = new SocialCharacterData();
partyMemberData.id = reader.GetString("id");
partyMemberData.characterName = reader.GetString("characterName");
partyMemberData.dataId = reader.GetInt32("dataId");
partyMemberData.level = (short)reader.GetInt32("level");
result.AddMember(partyMemberData);
}
}
return(result);
}
private bool ReadCharacterItem(SQLiteRowsReader reader, out CharacterItem result, bool resetReader = true)
{
if (resetReader)
{
reader.ResetReader();
}
if (reader.Read())
{
result = new CharacterItem();
result.id = reader.GetString("id");
result.dataId = reader.GetInt32("dataId");
result.level = reader.GetInt16("level");
result.amount = reader.GetInt16("amount");
result.equipSlotIndex = reader.GetByte("equipSlotIndex");
result.durability = reader.GetFloat("durability");
result.exp = reader.GetInt32("exp");
result.lockRemainsDuration = reader.GetFloat("lockRemainsDuration");
result.ammo = reader.GetInt16("ammo");
result.sockets = ReadSockets(reader.GetString("sockets"));
return(true);
}
result = CharacterItem.Empty;
return(false);
}
public override string GooglePlayLogin(string gId, string email)
{
string id = string.Empty;
SQLiteRowsReader reader = ExecuteReader("SELECT id FROM userlogin WHERE username=@username AND password=@password AND authType=@authType LIMIT 1",
new SqliteParameter("@username", "g_" + gId),
new SqliteParameter("@password", GenericUtils.GetMD5(gId)),
new SqliteParameter("@authType", AUTH_TYPE_GOOGLE_PLAY));
if (reader.Read())
{
id = reader.GetString("id");
}
else
{
ExecuteNonQuery("INSERT INTO userlogin (id, username, password, email, authType) VALUES (@id, @username, @password, @email, @authType)",
new SqliteParameter("@id", GenericUtils.GetUniqueId()),
new SqliteParameter("@username", "g_" + gId),
new SqliteParameter("@password", GenericUtils.GetMD5(gId)),
new SqliteParameter("@email", email),
new SqliteParameter("@authType", AUTH_TYPE_GOOGLE_PLAY));
// Read last entry
reader = ExecuteReader("SELECT id FROM userlogin WHERE username=@username AND password=@password AND authType=@authType LIMIT 1",
new SqliteParameter("@username", "g_" + gId),
new SqliteParameter("@password", GenericUtils.GetMD5(gId)),
new SqliteParameter("@authType", AUTH_TYPE_GOOGLE_PLAY));
if (reader.Read())
{
id = reader.GetString("id");
}
}
return(id);
}
private bool ReadBuilding(SQLiteRowsReader reader, out BuildingSaveData result, bool resetReader = true)
{
if (resetReader)
{
reader.ResetReader();
}
if (reader.Read())
{
result = new BuildingSaveData();
result.Id = reader.GetString("id");
result.ParentId = reader.GetString("parentId");
result.EntityId = reader.GetInt32("entityId");
result.CurrentHp = reader.GetInt32("currentHp");
result.Position = new Vector3(reader.GetFloat("positionX"), reader.GetFloat("positionY"), reader.GetFloat("positionZ"));
result.Rotation = Quaternion.Euler(reader.GetFloat("rotationX"), reader.GetFloat("rotationY"), reader.GetFloat("rotationZ"));
result.IsLocked = reader.GetBoolean("isLocked");
result.LockPassword = reader.GetString("lockPassword");
result.CreatorId = reader.GetString("creatorId");
result.CreatorName = reader.GetString("creatorName");
return(true);
}
result = new BuildingSaveData();
return(false);
}
public override List <SocialCharacterData> ReadFriends(string id1)
{
List <SocialCharacterData> result = new List <SocialCharacterData>();
SQLiteRowsReader reader = ExecuteReader("SELECT characterId2 FROM friend WHERE characterId1=@id1",
new SqliteParameter("@id1", id1));
string characterId;
SocialCharacterData socialCharacterData;
SQLiteRowsReader reader2;
while (reader.Read())
{
characterId = reader.GetString("characterId2");
reader2 = ExecuteReader("SELECT id, dataId, characterName, level FROM characters WHERE id LIKE @id",
new SqliteParameter("@id", characterId));
while (reader2.Read())
{
// Get some required data, other data will be set at server side
socialCharacterData = new SocialCharacterData();
socialCharacterData.id = reader2.GetString("id");
socialCharacterData.characterName = reader2.GetString("characterName");
socialCharacterData.dataId = reader2.GetInt32("dataId");
socialCharacterData.level = reader2.GetInt16("level");
result.Add(socialCharacterData);
}
}
return(result);
}
public List <EquipWeapons> ReadCharacterEquipWeapons(string characterId)
{
List <EquipWeapons> result = new List <EquipWeapons>();
SQLiteRowsReader reader = ExecuteReader("SELECT * FROM characteritem WHERE characterId=@characterId AND (inventoryType=@inventoryType1 OR inventoryType=@inventoryType2) ORDER BY idx ASC",
new SqliteParameter("@characterId", characterId),
new SqliteParameter("@inventoryType1", (byte)InventoryType.EquipWeaponRight),
new SqliteParameter("@inventoryType2", (byte)InventoryType.EquipWeaponLeft));
CharacterItem tempInventory;
byte equipWeaponSet;
InventoryType inventoryType;
while (ReadCharacterItem(reader, out tempInventory, false))
{
equipWeaponSet = reader.GetByte("idx");
inventoryType = (InventoryType)reader.GetSByte("inventoryType");
// Fill weapon sets if needed
while (result.Count <= equipWeaponSet)
{
result.Add(new EquipWeapons());
}
// Get equip weapon set
if (inventoryType == InventoryType.EquipWeaponRight)
{
result[equipWeaponSet].rightHand = tempInventory;
}
if (inventoryType == InventoryType.EquipWeaponLeft)
{
result[equipWeaponSet].leftHand = tempInventory;
}
}
return(result);
}
public void ReadCharacter_UMA(
PlayerCharacterData characterData,
bool withEquipWeapons,
bool withAttributes,
bool withSkills,
bool withSkillUsages,
bool withBuffs,
bool withEquipItems,
bool withNonEquipItems,
bool withSummons,
bool withHotkeys,
bool withQuests)
{
// Read uma data
SQLiteRowsReader reader = ExecuteReader("SELECT data FROM characterumasaves WHERE id=@id",
new SqliteParameter("@id", characterData.Id));
if (reader.Read())
{
string data = reader.GetString("data");
string[] splitedData = data.Split(',');
List <byte> bytes = new List <byte>();
foreach (string entry in splitedData)
{
bytes.Add(byte.Parse(entry));
}
UmaAvatarData umaAvatarData = new UmaAvatarData();
umaAvatarData.SetBytes(bytes);
characterData.UmaAvatarData = umaAvatarData;
}
}
public override int GetCash(string userId)
{
int cash = 0;
SQLiteRowsReader reader = ExecuteReader("SELECT cash FROM userlogin WHERE id=@id LIMIT 1",
new SqliteParameter("@id", userId));
if (reader.Read())
{
cash = reader.GetInt32("cash");
}
return(cash);
}
public override GuildData ReadGuild(int id, GuildRoleData[] defaultGuildRoles)
{
GuildData result = null;
SQLiteRowsReader reader = ExecuteReader("SELECT * FROM guild WHERE id=@id LIMIT 1",
new SqliteParameter("@id", id));
if (reader.Read())
{
result = new GuildData(id, reader.GetString("guildName"), reader.GetString("leaderId"), defaultGuildRoles);
result.level = (short)reader.GetInt32("level");
result.exp = reader.GetInt32("exp");
result.skillPoint = (short)reader.GetInt32("skillPoint");
result.guildMessage = reader.GetString("guildMessage");
result.gold = reader.GetInt32("gold");
reader = ExecuteReader("SELECT * FROM guildrole WHERE guildId=@id",
new SqliteParameter("@id", id));
byte guildRole;
GuildRoleData guildRoleData;
while (reader.Read())
{
guildRole = (byte)reader.GetInt32("guildRole");
guildRoleData = new GuildRoleData();
guildRoleData.roleName = reader.GetString("name");
guildRoleData.canInvite = reader.GetBoolean("canInvite");
guildRoleData.canKick = reader.GetBoolean("canKick");
guildRoleData.shareExpPercentage = (byte)reader.GetInt32("shareExpPercentage");
result.SetRole(guildRole, guildRoleData);
}
reader = ExecuteReader("SELECT id, dataId, characterName, level, guildRole FROM characters WHERE guildId=@id",
new SqliteParameter("@id", id));
SocialCharacterData guildMemberData;
while (reader.Read())
{
// Get some required data, other data will be set at server side
guildMemberData = new SocialCharacterData();
guildMemberData.id = reader.GetString("id");
guildMemberData.characterName = reader.GetString("characterName");
guildMemberData.dataId = reader.GetInt32("dataId");
guildMemberData.level = (short)reader.GetInt32("level");
result.AddMember(guildMemberData, (byte)reader.GetInt32("guildRole"));
}
reader = ExecuteReader("SELECT dataId, level FROM guildskill WHERE guildId=@id",
new SqliteParameter("@id", id));
while (reader.Read())
{
result.SetSkillLevel(reader.GetInt32("dataId"), (short)reader.GetInt32("level"));
}
}
return(result);
}
public override int GetGuildGold(int guildId)
{
int gold = 0;
SQLiteRowsReader reader = ExecuteReader("SELECT gold FROM guild WHERE id=@id LIMIT 1",
new SqliteParameter("@id", guildId));
if (reader.Read())
{
gold = reader.GetInt32("gold");
}
return(gold);
}
public override byte GetUserLevel(string userId)
{
byte userLevel = 0;
SQLiteRowsReader reader = ExecuteReader("SELECT userLevel FROM userlogin WHERE id=@id LIMIT 1",
new SqliteParameter("@id", userId));
if (reader.Read())
{
userLevel = (byte)reader.GetSByte("userLevel");
}
return(userLevel);
}
public override int GetGold(string userId)
{
int gold = 0;
SQLiteRowsReader reader = ExecuteReader("SELECT gold FROM userlogin WHERE id=@id LIMIT 1",
new SqliteParameter("@id", userId));
if (reader.Read())
{
gold = reader.GetInt32("gold");
}
return(gold);
}
public override List <PlayerCharacterData> ReadCharacters(string userId)
{
List <PlayerCharacterData> result = new List <PlayerCharacterData>();
SQLiteRowsReader reader = ExecuteReader("SELECT id FROM characters WHERE userId=@userId ORDER BY updateAt DESC", new SqliteParameter("@userId", userId));
while (reader.Read())
{
string characterId = reader.GetString("id");
result.Add(ReadCharacter(userId, characterId, true, true, true, false, false, true, false, false, false, false));
}
return(result);
}
public override List <BuildingSaveData> ReadBuildings(string mapName)
{
List <BuildingSaveData> result = new List <BuildingSaveData>();
SQLiteRowsReader reader = ExecuteReader("SELECT * FROM buildings WHERE mapName=@mapName", new SqliteParameter("@mapName", mapName));
BuildingSaveData tempBuilding;
while (ReadBuilding(reader, out tempBuilding, false))
{
result.Add(tempBuilding);
}
return(result);
}
public List <CharacterQuest> ReadCharacterQuests(string characterId)
{
List <CharacterQuest> result = new List <CharacterQuest>();
SQLiteRowsReader reader = ExecuteReader("SELECT * FROM characterquest WHERE characterId=@characterId ORDER BY idx ASC",
new SqliteParameter("@characterId", characterId));
CharacterQuest tempQuest;
while (ReadCharacterQuest(reader, out tempQuest, false))
{
result.Add(tempQuest);
}
return(result);
}
public List <CharacterSkillUsage> ReadCharacterSkillUsages(string characterId)
{
List <CharacterSkillUsage> result = new List <CharacterSkillUsage>();
SQLiteRowsReader reader = ExecuteReader("SELECT * FROM characterskillusage WHERE characterId=@characterId ORDER BY coolDownRemainsDuration ASC",
new SqliteParameter("@characterId", characterId));
CharacterSkillUsage tempSkillUsage;
while (ReadCharacterSkillUsage(reader, out tempSkillUsage, false))
{
result.Add(tempSkillUsage);
}
return(result);
}
public List <CharacterBuff> ReadCharacterBuffs(string characterId)
{
List <CharacterBuff> result = new List <CharacterBuff>();
SQLiteRowsReader reader = ExecuteReader("SELECT * FROM characterbuff WHERE characterId=@characterId ORDER BY buffRemainsDuration ASC",
new SqliteParameter("@characterId", characterId));
CharacterBuff tempBuff;
while (ReadCharacterBuff(reader, out tempBuff, false))
{
result.Add(tempBuff);
}
return(result);
}
public List <CharacterHotkey> ReadCharacterHotkeys(string characterId)
{
List <CharacterHotkey> result = new List <CharacterHotkey>();
SQLiteRowsReader reader = ExecuteReader("SELECT * FROM characterhotkey WHERE characterId=@characterId",
new SqliteParameter("@characterId", characterId));
CharacterHotkey tempHotkey;
while (ReadCharacterHotkey(reader, out tempHotkey, false))
{
result.Add(tempHotkey);
}
return(result);
}
public List <CharacterSkill> ReadCharacterSkills(string characterId)
{
List <CharacterSkill> result = new List <CharacterSkill>();
SQLiteRowsReader reader = ExecuteReader("SELECT * FROM characterskill WHERE characterId=@characterId ORDER BY idx ASC",
new SqliteParameter("@characterId", characterId));
CharacterSkill tempSkill;
while (ReadCharacterSkill(reader, out tempSkill, false))
{
result.Add(tempSkill);
}
return(result);
}
public List <CharacterAttribute> ReadCharacterAttributes(string characterId)
{
List <CharacterAttribute> result = new List <CharacterAttribute>();
SQLiteRowsReader reader = ExecuteReader("SELECT * FROM characterattribute WHERE characterId=@characterId ORDER BY idx ASC",
new SqliteParameter("@characterId", characterId));
CharacterAttribute tempAttribute;
while (ReadCharacterAttribute(reader, out tempAttribute, false))
{
result.Add(tempAttribute);
}
return(result);
}
public List <CharacterSummon> ReadCharacterSummons(string characterId)
{
List <CharacterSummon> result = new List <CharacterSummon>();
SQLiteRowsReader reader = ExecuteReader("SELECT * FROM charactersummon WHERE characterId=@characterId ORDER BY type DESC",
new SqliteParameter("@characterId", characterId));
CharacterSummon tempSummon;
while (ReadCharacterSummon(reader, out tempSummon, false))
{
result.Add(tempSummon);
}
return(result);
}
public override List <CharacterItem> ReadStorageItems(StorageType storageType, string storageOwnerId)
{
List <CharacterItem> result = new List <CharacterItem>();
SQLiteRowsReader reader = ExecuteReader("SELECT * FROM storageitem WHERE storageType=@storageType AND storageOwnerId=@storageOwnerId ORDER BY idx ASC",
new SqliteParameter("@storageType", (byte)storageType),
new SqliteParameter("@storageOwnerId", storageOwnerId));
CharacterItem tempInventory;
while (ReadStorageItem(reader, out tempInventory, false))
{
result.Add(tempInventory);
}
return(result);
}
private List <CharacterItem> ReadCharacterItems(string characterId, InventoryType inventoryType)
{
List <CharacterItem> result = new List <CharacterItem>();
SQLiteRowsReader reader = ExecuteReader("SELECT * FROM characteritem WHERE characterId=@characterId AND inventoryType=@inventoryType ORDER BY idx ASC",
new SqliteParameter("@characterId", characterId),
new SqliteParameter("@inventoryType", (byte)inventoryType));
CharacterItem tempInventory;
while (ReadCharacterItem(reader, out tempInventory, false))
{
result.Add(tempInventory);
}
return(result);
}
public override string ValidateUserLogin(string username, string password)
{
string id = string.Empty;
SQLiteRowsReader reader = ExecuteReader("SELECT id FROM userlogin WHERE username=@username AND password=@password AND authType=@authType LIMIT 1",
new SqliteParameter("@username", username),
new SqliteParameter("@password", GenericUtils.GetMD5(password)),
new SqliteParameter("@authType", AUTH_TYPE_NORMAL));
if (reader.Read())
{
id = reader.GetString("id");
}
return(id);
}
public SQLiteRowsReader ExecuteReader(string sql, params SqliteParameter[] args)
{
SQLiteRowsReader result = new SQLiteRowsReader();
using (SqliteCommand cmd = new SqliteCommand(sql, connection))
{
foreach (SqliteParameter arg in args)
{
cmd.Parameters.Add(arg);
}
SqliteDataReader dataReader = cmd.ExecuteReader();
result.Init(dataReader);
dataReader.Close();
}
return(result);
}
private bool ReadCharacterAttribute(SQLiteRowsReader reader, out CharacterAttribute result, bool resetReader = true)
{
if (resetReader)
{
reader.ResetReader();
}
if (reader.Read())
{
result = new CharacterAttribute();
result.dataId = reader.GetInt32("dataId");
result.amount = (short)reader.GetInt32("amount");
return(true);
}
result = CharacterAttribute.Empty;
return(false);
}
private bool ReadCharacterSkill(SQLiteRowsReader reader, out CharacterSkill result, bool resetReader = true)
{
if (resetReader)
{
reader.ResetReader();
}
if (reader.Read())
{
result = new CharacterSkill();
result.dataId = reader.GetInt32("dataId");
result.level = (short)reader.GetInt32("level");
return(true);
}
result = CharacterSkill.Empty;
return(false);
}
public override string FacebookLogin(string fbId, string accessToken)
{
string url = "https://graph.facebook.com/" + fbId + "?access_token=" + accessToken + "&fields=id,name,email";
WebClient webClient = new WebClient();
string json = webClient.DownloadString(url);
json = json.Replace(@"\u0040", "@");
string id = string.Empty;
Dictionary <string, object> dict = Json.Deserialize(json) as Dictionary <string, object>;
if (dict.ContainsKey("id") && dict.ContainsKey("email"))
{
string email = (string)dict["email"];
SQLiteRowsReader reader = ExecuteReader("SELECT id FROM userlogin WHERE username=@username AND password=@password AND authType=@authType LIMIT 1",
new SqliteParameter("@username", "fb_" + fbId),
new SqliteParameter("@password", GenericUtils.GetMD5(fbId)),
new SqliteParameter("@authType", AUTH_TYPE_FACEBOOK));
if (reader.Read())
{
id = reader.GetString("id");
}
else
{
ExecuteNonQuery("INSERT INTO userlogin (id, username, password, email, authType) VALUES (@id, @username, @password, @email, @authType)",
new SqliteParameter("@id", GenericUtils.GetUniqueId()),
new SqliteParameter("@username", "fb_" + fbId),
new SqliteParameter("@password", GenericUtils.GetMD5(fbId)),
new SqliteParameter("@email", email),
new SqliteParameter("@authType", AUTH_TYPE_FACEBOOK));
// Read last entry
reader = ExecuteReader("SELECT id FROM userlogin WHERE username=@username AND password=@password AND authType=@authType LIMIT 1",
new SqliteParameter("@username", "fb_" + fbId),
new SqliteParameter("@password", GenericUtils.GetMD5(fbId)),
new SqliteParameter("@authType", AUTH_TYPE_FACEBOOK));
if (reader.Read())
{
id = reader.GetString("id");
}
}
}
return(id);
}
