9
3

RecipeDB.cpp 12 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296
  1. /*
  2. EQ2Emulator: Everquest II Server Emulator
  3. Copyright (C) 2007 EQ2EMulator Development Team (http://www.eq2emulator.net)
  4. This file is part of EQ2Emulator.
  5. EQ2Emulator is free software: you can redistribute it and/or modify
  6. it under the terms of the GNU General Public License as published by
  7. the Free Software Foundation, either version 3 of the License, or
  8. (at your option) any later version.
  9. EQ2Emulator is distributed in the hope that it will be useful,
  10. but WITHOUT ANY WARRANTY; without even the implied warranty of
  11. MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  12. GNU General Public License for more details.
  13. You should have received a copy of the GNU General Public License
  14. along with EQ2Emulator. If not, see <http://www.gnu.org/licenses/>.
  15. */
  16. #ifdef WIN32
  17. #include <WinSock2.h>
  18. #include <windows.h>
  19. #endif
  20. #include <mysql.h>
  21. #include <assert.h>
  22. #include "../../common/Log.h"
  23. #include "../WorldDatabase.h"
  24. #include "Recipe.h"
  25. extern MasterRecipeList master_recipe_list;
  26. extern MasterRecipeBookList master_recipebook_list;
  27. extern MasterItemList master_item_list;
  28. void WorldDatabase::LoadRecipes() {
  29. DatabaseResult res;
  30. bool status = database_new.Select(&res,
  31. "SELECT r.`id`,r.`soe_id`,r.`level`,r.`icon`,r.`skill_level`,r.`technique`,r.`knowledge`,r.`name`,r.`description`,i.`name` as `book`,r.`bench`,ipc.`adventure_classes`, "
  32. "r.`stage4_id`, r.`name`, r.`stage4_qty`, pcl.`name` as primary_comp_title,r.primary_comp_qty, fcl.name as `fuel_comp_title`, r.fuel_comp_qty, "
  33. "bc.`name` AS build_comp_title, bc.qty AS build_comp_qty, bc2.`name` AS build2_comp_title, bc2.qty AS build2_comp_qty, "
  34. "bc3.`name` AS build3_comp_title, bc3.qty AS build3_comp_qty, bc4.`name` AS build4_comp_title, bc4.qty AS build4_comp_qty,\n"
  35. "r.stage0_id, r.stage1_id, r.stage2_id, r.stage3_id, r.stage4_id, r.stage0_qty, r.stage1_qty, r.stage2_qty, r.stage3_qty, r.stage4_qty,\n"
  36. "r.stage0_byp_id, r.stage1_byp_id, r.stage2_byp_id, r.stage3_byp_id, r.stage4_byp_id, r.stage0_byp_qty, r.stage1_byp_qty, r.stage2_byp_qty, r.stage3_byp_qty, r.stage4_byp_qty\n"
  37. "FROM `recipe` r\n"
  38. "LEFT JOIN ((SELECT recipe_id, soe_recipe_crc FROM item_details_recipe_items GROUP BY soe_recipe_crc) as idri) ON idri.soe_recipe_crc = r.soe_id\n"
  39. "LEFT JOIN items i ON idri.recipe_id = i.id\n"
  40. "INNER JOIN items ipc ON r.stage4_id = ipc.id\n"
  41. "INNER JOIN recipe_comp_list pcl ON r.primary_comp_list = pcl.id\n"
  42. "INNER JOIN recipe_comp_list fcl ON r.fuel_comp_list = fcl.id\n"
  43. "LEFT JOIN (SELECT rsc.recipe_id, rsc.comp_list, rsc.`index`, rcl.`name`, rsc.qty FROM recipe_secondary_comp rsc INNER JOIN recipe_comp_list rcl ON rcl.id = rsc.comp_list WHERE `index` = 0) AS bc ON bc.recipe_id = r.id\n"
  44. "LEFT JOIN (SELECT rsc.recipe_id, rsc.comp_list, rsc.`index`, rcl.`name`, rsc.qty FROM recipe_secondary_comp rsc INNER JOIN recipe_comp_list rcl ON rcl.id = rsc.comp_list WHERE `index` = 1) AS bc2 ON bc2.recipe_id = r.id\n"
  45. "LEFT JOIN (SELECT rsc.recipe_id, rsc.comp_list, rsc.`index`, rcl.`name`, rsc.qty FROM recipe_secondary_comp rsc INNER JOIN recipe_comp_list rcl ON rcl.id = rsc.comp_list WHERE `index` = 2) AS bc3 ON bc3.recipe_id = r.id\n"
  46. "LEFT JOIN (SELECT rsc.recipe_id, rsc.comp_list, rsc.`index`, rcl.`name`, rsc.qty FROM recipe_secondary_comp rsc INNER JOIN recipe_comp_list rcl ON rcl.id = rsc.comp_list WHERE `index` = 3) AS bc4 ON bc4.recipe_id = r.id\n"
  47. "WHERE r.bHaveAllProducts");
  48. if (!status)
  49. return;
  50. while (res.Next()) {
  51. int32 i = 0;
  52. Recipe* recipe = new Recipe();
  53. recipe->SetID(res.GetInt32(i++));
  54. recipe->SetSoeID(res.GetInt32(i++));
  55. recipe->SetLevel(res.GetInt32(i++));
  56. recipe->SetTier(recipe->GetLevel() / 10 + 1);
  57. recipe->SetIcon(res.GetInt32(i++));
  58. recipe->SetSkill(res.GetInt32(i++));
  59. recipe->SetTechnique(res.GetInt32(i++));
  60. recipe->SetKnowledge(res.GetInt32(i++));
  61. recipe->SetName(res.GetString(i++));
  62. recipe->SetDescription(res.GetString(i++));
  63. recipe->SetBook(res.GetString(i++));
  64. //Convert the device string
  65. string device = res.GetString(i++);
  66. int32 deviceID = 0;
  67. int8 deviceSubType = 0;
  68. recipe->SetDevice(GetDeviceName(device).c_str());
  69. recipe->SetUnknown2(deviceID);
  70. recipe->SetDevice_Sub_Type(deviceSubType);
  71. recipe->SetClasses(res.GetInt64(i++));
  72. recipe->SetUnknown3(0);
  73. recipe->SetUnknown4(0);
  74. LogWrite(TRADESKILL__DEBUG, 5, "Recipes", "Loading recipe: %s (%u)", recipe->GetName(), recipe->GetID());
  75. recipe->SetProductID(res.GetInt32(i++));
  76. recipe->SetProductName(res.GetString(i++));
  77. recipe->SetProductQuantity(res.GetInt8(i++));
  78. recipe->SetPrimaryComponentTitle(res.GetString(i++));
  79. recipe->SetPrimaryComponentQuantity(res.GetInt16(i++));
  80. recipe->SetFuelComponentTitle(res.GetString(i++));
  81. recipe->SetFuelComponentQuantity(res.GetInt16(i++));
  82. recipe->SetBuild1ComponentTitle(res.GetString(i++));
  83. recipe->SetBuild1ComponentQuantity(res.GetInt16(i++));
  84. recipe->SetBuild2ComponentTitle(res.GetString(i++));
  85. recipe->SetBuild2ComponentQuantity(res.GetInt16(i++));
  86. recipe->SetBuild3ComponentTitle(res.GetString(i++));
  87. recipe->SetBuild3ComponentQuantity(res.GetInt16(i++));
  88. recipe->SetBuild4ComponentTitle(res.GetString(i++));
  89. recipe->SetBuild4ComponentQuantity(res.GetInt16(i++));
  90. LogWrite(TRADESKILL__DEBUG, 7, "Recipes", "Loading recipe: %s (%u)", recipe->GetName(), recipe->GetID());
  91. if (!master_recipe_list.AddRecipe(recipe)) {
  92. LogWrite(TRADESKILL__ERROR, 0, "Recipes", "Error adding recipe '%s' - duplicate ID: %u", recipe->GetName(), recipe->GetID());
  93. delete recipe;
  94. continue;
  95. }
  96. //Products/By-Products
  97. for (int8 stage = 0; stage < 5; stage++) {
  98. RecipeProducts* rp = new RecipeProducts;
  99. rp->product_id = res.GetInt32(i);
  100. rp->product_qty = res.GetInt8(i + 5);
  101. rp->byproduct_id = res.GetInt32(i + 10);
  102. rp->byproduct_qty = res.GetInt8(i + 15);
  103. recipe->products[stage] = rp;
  104. i++;
  105. }
  106. //Advance i past all the product info
  107. //i += 15;
  108. }
  109. LoadRecipeComponents();
  110. LogWrite(TRADESKILL__DEBUG, 0, "Recipes", "\tLoaded %u recipes", master_recipe_list.Size());
  111. }
  112. void WorldDatabase::LoadRecipeBooks(){
  113. Recipe *recipe;
  114. Query query;
  115. MYSQL_ROW row;
  116. MYSQL_RES *res;
  117. res = query.RunQuery2(Q_SELECT, "SELECT id, name, tradeskill_default_level FROM items WHERE item_type='Recipe'");
  118. if (res){
  119. while ((row = mysql_fetch_row(res))){
  120. recipe = new Recipe();
  121. recipe->SetBookID(atoul(row[0]));
  122. recipe->SetBookName(row[1]);
  123. recipe->SetLevel(atoi(row[2]));
  124. LogWrite(TRADESKILL__DEBUG, 5, "Recipes", "Loading Recipe Books: %s (%u)", recipe->GetBookName(), recipe->GetBookID());
  125. if (!master_recipebook_list.AddRecipeBook(recipe)){
  126. LogWrite(TRADESKILL__ERROR, 0, "Recipes", "Error adding Recipe Book '%s' - duplicate ID: %u", recipe->GetBookName(), recipe->GetBookID());
  127. safe_delete(recipe);
  128. continue;
  129. }
  130. }
  131. }
  132. LogWrite(TRADESKILL__DEBUG, 0, "Recipes", "\tLoaded %u Recipe Books ", master_recipebook_list.Size());
  133. }
  134. void WorldDatabase::LoadPlayerRecipes(Player *player){
  135. Recipe *recipe;
  136. Query query;
  137. MYSQL_ROW row;
  138. MYSQL_RES *res;
  139. int16 total = 0;
  140. assert(player);
  141. res = query.RunQuery2(Q_SELECT, "SELECT recipe_id, highest_stage FROM character_recipes WHERE char_id = %u", player->GetCharacterID());
  142. if (res) {
  143. while ((row = mysql_fetch_row(res))){
  144. int32 recipe_id = atoul(row[0]);
  145. Recipe* master_recipe = master_recipe_list.GetRecipe(recipe_id);
  146. if(!master_recipe) {
  147. LogWrite(TRADESKILL__ERROR, 0, "Recipes", "Error adding recipe %u to player '%s' - duplicate ID", atoul(row[0]), player->GetName());
  148. continue;
  149. }
  150. recipe = new Recipe(master_recipe);
  151. recipe->SetHighestStage(atoi(row[1]));
  152. LogWrite(TRADESKILL__DEBUG, 5, "Recipes", "Loading recipe: %s (%u) for player: %s (%u)", recipe->GetName(), recipe->GetID(), player->GetName(), player->GetCharacterID());
  153. if (!player->GetRecipeList()->AddRecipe(recipe)){
  154. LogWrite(TRADESKILL__ERROR, 0, "Recipes", "Error adding recipe %u to player '%s' - duplicate ID", recipe->GetID(), player->GetName());
  155. safe_delete(recipe);
  156. continue;
  157. }
  158. total++;
  159. }
  160. LogWrite(TRADESKILL__DEBUG, 0, "Recipes", "Loaded %u recipes for player: %s (%u)", total, player->GetName(), player->GetCharacterID());
  161. }
  162. }
  163. int32 WorldDatabase::LoadPlayerRecipeBooks(int32 char_id, Player *player) {
  164. assert(player);
  165. LogWrite(TRADESKILL__DEBUG, 0, "Recipes", "Loading Character Recipe Books for player '%s' ...", player->GetName());
  166. Query query;
  167. MYSQL_ROW row;
  168. MYSQL_RES *res;
  169. int32 count = 0;
  170. int32 old_id = 0;
  171. int32 new_id = 0;
  172. Recipe* recipe;
  173. res = query.RunQuery2(Q_SELECT, "SELECT recipebook_id FROM character_recipe_books WHERE char_id = %u", char_id);
  174. if (res && mysql_num_rows(res) > 0) {
  175. while (res && (row = mysql_fetch_row(res))){
  176. count++;
  177. new_id = atoul(row[0]);
  178. if(new_id == old_id)
  179. continue;
  180. Item* item = master_item_list.GetItem(new_id);
  181. if (!item)
  182. continue;
  183. recipe = new Recipe();
  184. recipe->SetBookID(new_id);
  185. recipe->SetBookName(item->name.c_str());
  186. LogWrite(TRADESKILL__DEBUG, 5, "Recipes", "Loading Recipe Books: %s (%u)", recipe->GetBookName(), recipe->GetBookID());
  187. if (!player->GetRecipeBookList()->AddRecipeBook(recipe)) {
  188. LogWrite(TRADESKILL__ERROR, 0, "Recipes", "Error adding player Recipe Book '%s' - duplicate ID: %u", recipe->GetBookName(), recipe->GetBookID());
  189. safe_delete(recipe);
  190. continue;
  191. }
  192. old_id = new_id;
  193. }
  194. }
  195. return count;
  196. }
  197. void WorldDatabase::SavePlayerRecipeBook(Player* player, int32 recipebook_id){
  198. Query query;
  199. query.AddQueryAsync(player->GetCharacterID(), this, Q_INSERT, "INSERT INTO character_recipe_books (char_id, recipebook_id) VALUES(%u, %u)", player->GetCharacterID(), recipebook_id);
  200. //if(query.GetErrorNumber() && query.GetError() && query.GetErrorNumber() < 0xFFFFFFFF)
  201. //LogWrite(TRADESKILL__ERROR, 0, "Recipes", "Error in SavePlayerRecipeBook query '%s' : %s", query.GetQuery(), query.GetError());
  202. }
  203. void WorldDatabase::SavePlayerRecipe(Player* player, int32 recipe_id) {
  204. Query query;
  205. query.AddQueryAsync(player->GetCharacterID(), this, Q_INSERT, "INSERT INTO character_recipes (char_id, recipe_id) VALUES (%u, %u)", player->GetCharacterID(), recipe_id);
  206. //if(query.GetErrorNumber() && query.GetError() && query.GetErrorNumber() < 0xFFFFFFFF)
  207. //LogWrite(TRADESKILL__ERROR, 0, "Recipes", "Error in SavePlayerRecipeBook query '%s' : %s", query.GetQuery(), query.GetError());
  208. }
  209. void WorldDatabase::LoadRecipeComponents() {
  210. DatabaseResult res;
  211. bool status = database_new.Select(&res,
  212. "SELECT r.id, pc.item_id AS primary_comp, fc.item_id AS fuel_comp, sc.item_id as secondary_comp, rsc.`index` + 1 AS slot\n"
  213. "FROM recipe r\n"
  214. "INNER JOIN (select comp_list, item_id FROM recipe_comp_list_item ) as pc ON r.primary_comp_list = pc.comp_list\n"
  215. "INNER JOIN (select comp_list, item_id FROM recipe_comp_list_item ) as fc ON r.fuel_comp_list = fc.comp_list\n"
  216. "LEFT JOIN recipe_secondary_comp rsc ON rsc.recipe_id = r.id\n"
  217. "LEFT JOIN (select comp_list, item_id FROM recipe_comp_list_item) as sc ON rsc.comp_list = sc.comp_list\n"
  218. "WHERE r.bHaveAllProducts\n"
  219. "ORDER BY r.id, rsc.`index` ASC");
  220. if (!status) {
  221. return;
  222. }
  223. Recipe* recipe = 0;
  224. int32 id = 0;
  225. while (res.Next()) {
  226. int32 tmp = res.GetInt32(0);
  227. if (id != tmp) {
  228. id = tmp;
  229. recipe = master_recipe_list.GetRecipe(id);
  230. if (!recipe) {
  231. continue;
  232. }
  233. }
  234. if (recipe && !res.IsNull(3)) {
  235. if (find(recipe->components[0].begin(), recipe->components[0].end(), res.GetInt32(1)) == recipe->components[0].end())
  236. recipe->AddBuildComp(res.GetInt32(1), 0);
  237. if (find(recipe->components[5].begin(), recipe->components[5].end(), res.GetInt32(2)) == recipe->components[5].end())
  238. recipe->AddBuildComp(res.GetInt32(2), 5);
  239. if (find(recipe->components[res.GetInt8(4)].begin(), recipe->components[res.GetInt8(4)].end(), res.GetInt32(3)) == recipe->components[res.GetInt8(4)].end())
  240. recipe->AddBuildComp(res.GetInt32(3), res.GetInt8(4));
  241. }
  242. //else
  243. //LogWrite(TRADESKILL__ERROR, 0, "Recipes", "Error loading `recipe_build_comps`, Recipe ID: %u", id);
  244. }
  245. }
  246. void WorldDatabase::UpdatePlayerRecipe(Player* player, int32 recipe_id, int8 highest_stage) {
  247. Query query;
  248. query.AddQueryAsync(player->GetCharacterID(), this, Q_UPDATE, "UPDATE `character_recipes` SET `highest_stage` = %i WHERE `char_id` = %u AND `recipe_id` = %u", highest_stage, player->GetCharacterID(), recipe_id);
  249. }
  250. /*
  251. ALTER TABLE `character_recipes`
  252. ADD COLUMN `highest_stage` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' AFTER `recipe_id`;
  253. */