Coverage for mindsdb / integrations / handlers / quickbooks_handler / quickbooks_table.py: 0%

1import pandas as pd 

2from mindsdb.integrations.libs.api_handler import APITable 

3from mindsdb_sql_parser import ast 

4 

5 

6class AccountsTable(APITable): 

7 

8 def flatten_dict(self, data: dict, prefix: str = ""): 

9 flat_data = {} 

10 for key, value in data.items(): 

11 if isinstance(value, dict): 

12 flattened_sub_dict = self.flatten_dict(value, f"{key}_") 

13 flat_data.update(flattened_sub_dict) 

14 else: 

15 flat_data[f"{prefix}{key}"] = value 

16 return flat_data 

17 

18 def select(self, query: ast.Select) -> pd.DataFrame: 

19 qbo = self.handler.connect() 

20 accounts_data = qbo.accounts.get() 

21 flattened_accounts_data = [self.flatten_dict(account) for account in accounts_data] 

22 result = pd.DataFrame(flattened_accounts_data) 

23 self.filter_columns(result, query) 

24 return result 

25 

26 def get_columns(self): 

27 return [ 

28 'Name', 

29 'SubAccount', 

30 'FullyQualifiedName', 

31 'Active', 

32 'Classification', 

33 'AccountType', 

34 'AccountSubType', 

35 'CurrentBalance', 

36 'CurrentBalanceWithSubAccounts', 

37 'CurrencyRef_value', 

38 'CurrencyRef_name', 

39 'domain', 

40 'sparse', 

41 'Id', 

42 'SyncToken', 

43 'MetaData_CreateTime', 

44 'MetaData_LastUpdatedTime', 

45 ] 

46 

47 def filter_columns(self, result: pd.DataFrame, query: ast.Select = None): 

48 columns = [] 

49 if query is not None: 

50 for target in query.targets: 

51 if isinstance(target, ast.Star): 

52 columns = self.get_columns() 

53 break 

54 elif isinstance(target, ast.Identifier): 

55 columns.append(target.value) 

56 if len(columns) > 0: 

57 result = result[columns] 

58 

59 

60class PurchasesTable(APITable): 

61 

62 def flatten_dict(self, data: dict, prefix: str = ""): 

63 flat_data = {} 

64 for key, value in data.items(): 

65 if isinstance(value, dict): 

66 flattened_sub_dict = self.flatten_dict(value, f"{key}_") 

67 flat_data.update(flattened_sub_dict) 

68 elif isinstance(value, list): 

69 for i, item in enumerate(value): 

70 if isinstance(item, dict): 

71 flattened_sub_dict = self.flatten_dict(item, f"{key}_{i}_") 

72 flat_data.update(flattened_sub_dict) 

73 else: 

74 flat_data[f"{prefix}{key}_{i}"] = item 

75 else: 

76 flat_data[f"{prefix}{key}"] = value 

77 return flat_data 

78 

79 def select(self, query: ast.Select) -> pd.DataFrame: 

80 qbo = self.handler.connect() 

81 purchases_data = qbo.purchases.get() 

82 flattened_purchases_data = [self.flatten_dict(purchase) for purchase in purchases_data] 

83 result = pd.DataFrame(flattened_purchases_data) 

84 self.filter_columns(result, query) 

85 return result 

86 

87 def get_columns(self): 

88 return [ 

89 'AccountRef_value', 

90 'AccountRef_name', 

91 'PaymentType', 

92 'Credit', 

93 'TotalAmt', 

94 'domain', 

95 'sparse', 

96 'Id', 

97 'SyncToken', 

98 'MetaData_CreateTime', 

99 'MetaData_LastUpdatedTime', 

100 'TxnDate', 

101 'CurrencyRef_value', 

102 'CurrencyRef_name', 

103 'EntityRef_value', 

104 'EntityRef_name', 

105 'EntityRef_type', 

106 'Line_0_Id', 

107 'Line_0_Amount', 

108 'Line_0_DetailType' 

109 # Add more columns for additional line items if needed 

110 ] 

111 

112 def filter_columns(self, result: pd.DataFrame, query: ast.Select = None): 

113 columns = [] 

114 if query is not None: 

115 for target in query.targets: 

116 if isinstance(target, ast.Star): 

117 columns = self.get_columns() 

118 break 

119 elif isinstance(target, ast.Identifier): 

120 columns.append(target.value) 

121 if len(columns) > 0: 

122 result = result[columns] 

123 

124 

125class BillPaymentsTable(APITable): 

126 def flatten_dict(self, data: dict, prefix: str = ""): 

127 flat_data = {} 

128 for key, value in data.items(): 

129 if isinstance(value, dict): 

130 flattened_sub_dict = self.flatten_dict(value, f"{prefix}{key}_") 

131 flat_data.update(flattened_sub_dict) 

132 elif isinstance(value, list): 

133 for i, item in enumerate(value): 

134 if isinstance(item, dict): 

135 flattened_sub_dict = self.flatten_dict(item, f"{prefix}{key}_{i}_") 

136 flat_data.update(flattened_sub_dict) 

137 else: 

138 flat_data[f"{prefix}{key}_{i}"] = item 

139 else: 

140 flat_data[prefix + key] = value 

141 return flat_data 

142 

143 def select(self, query: ast.Select) -> pd.DataFrame: 

144 qbo = self.handler.connect() 

145 billpayments_data = qbo.bill_payments.get() 

146 flattened_billpayments_data = [self.flatten_dict(bp) for bp in billpayments_data] 

147 result = pd.DataFrame(flattened_billpayments_data) 

148 self.filter_columns(result, query) 

149 return result 

150 

151 def get_columns(self): 

152 return [ 

153 'VendorRef_value', 

154 'VendorRef_name', 

155 'PayType', 

156 'CreditCardPayment_CCAccountRef_value', 

157 'CreditCardPayment_CCAccountRef_name', 

158 'CheckPayment_BankAccountRef_value', 

159 'CheckPayment_BankAccountRef_name', 

160 'TotalAmt', 

161 'Id', 

162 'SyncToken', 

163 'MetaData_CreateTime', 

164 'MetaData_LastUpdatedTime', 

165 'DocNumber', 

166 'TxnDate', 

167 'CurrencyRef_value', 

168 'CurrencyRef_name', 

169 'Line_0_Amount', 

170 'Line_0_LinkedTxn_0_TxnId', 

171 'Line_0_LinkedTxn_0_TxnType', 

172 'domain', 

173 'sparse', 

174 ] 

175 

176 def filter_columns(self, result: pd.DataFrame, query: ast.Select = None): 

177 columns = [] 

178 if query is not None: 

179 for target in query.targets: 

180 if isinstance(target, ast.Star): 

181 columns = self.get_columns() 

182 break 

183 elif isinstance(target, ast.Identifier): 

184 columns.append(target.value) 

185 if len(columns) > 0: 

186 result = result[columns] 

187 

188 

189class VendorsTable(APITable): 

190 

191 def flatten_dict(self, data: dict, prefix: str = ""): 

192 flat_data = {} 

193 for key, value in data.items(): 

194 if isinstance(value, dict): 

195 flattened_sub_dict = self.flatten_dict(value, f"{key}_") 

196 flat_data.update(flattened_sub_dict) 

197 else: 

198 flat_data[f"{prefix}{key}"] = value 

199 return flat_data 

200 

201 def select(self, query: ast.Select) -> pd.DataFrame: 

202 qbo = self.handler.connect() 

203 vendors_data = qbo.vendors.get() 

204 flattened_vendors_data = [self.flatten_dict(vendor) for vendor in vendors_data] 

205 result = pd.DataFrame(flattened_vendors_data) 

206 self.filter_columns(result, query) 

207 return result 

208 

209 def get_columns(self): 

210 return [ 

211 'Balance', 

212 'Vendor1099', 

213 'CurrencyRef_value', 

214 'CurrencyRef_name', 

215 'Id', 

216 'SyncToken', 

217 'MetaData_CreateTime', 

218 'MetaData_LastUpdatedTime', 

219 'DisplayName', 

220 'PrintOnCheckName', 

221 'Active', 

222 'domain', 

223 'sparse' 

224 ] 

225 

226 def filter_columns(self, result: pd.DataFrame, query: ast.Select = None): 

227 columns = [] 

228 if query is not None: 

229 for target in query.targets: 

230 if isinstance(target, ast.Star): 

231 columns = self.get_columns() 

232 break 

233 elif isinstance(target, ast.Identifier): 

234 columns.append(target.value) 

235 if len(columns) > 0: 

236 result = result[columns] 

237 

238 

239class BillsTable(APITable): 

240 

241 def flatten_dict(self, data: dict, prefix: str = ""): 

242 flat_data = {} 

243 for key, value in data.items(): 

244 if isinstance(value, dict): 

245 flattened_sub_dict = self.flatten_dict(value, f"{prefix}{key}_") 

246 flat_data.update(flattened_sub_dict) 

247 elif isinstance(value, list): 

248 for i, item in enumerate(value): 

249 if isinstance(item, dict): 

250 flattened_sub_dict = self.flatten_dict(item, f"{prefix}{key}_{i}_") 

251 flat_data.update(flattened_sub_dict) 

252 else: 

253 flat_data[f"{prefix}{key}_{i}"] = item 

254 else: 

255 flat_data[prefix + key] = value 

256 return flat_data 

257 

258 def select(self, query: ast.Select) -> pd.DataFrame: 

259 qbo = self.handler.connect() 

260 bills_data = qbo.bills.get() 

261 flattened_bills_data = [self.flatten_dict(bill) for bill in bills_data] 

262 result = pd.DataFrame(flattened_bills_data) 

263 self.filter_columns(result, query) 

264 return result 

265 

266 def get_columns(self): 

267 return [ 

268 'DueDate', 

269 'Balance', 

270 'Id', 

271 'SyncToken', 

272 'MetaData_CreateTime', 

273 'MetaData_LastUpdatedTime', 

274 'TxnDate', 

275 'CurrencyRef_value', 

276 'CurrencyRef_name', 

277 'VendorRef_value', 

278 'VendorRef_name', 

279 'APAccountRef_value', 

280 'APAccountRef_name', 

281 'TotalAmt', 

282 'Line_0_Id', 

283 'Line_0_LineNum', 

284 'Line_0_Description', 

285 'Line_0_Amount', 

286 'Line_0_DetailType', 

287 'Line_0_ItemBasedExpenseLineDetail_BillableStatus', 

288 'Line_0_ItemBasedExpenseLineDetail_ItemRef_value', 

289 'Line_0_ItemBasedExpenseLineDetail_ItemRef_name', 

290 'Line_0_ItemBasedExpenseLineDetail_UnitPrice', 

291 'Line_0_ItemBasedExpenseLineDetail_Qty', 

292 'Line_1_Id', 

293 'Line_1_LineNum', 

294 'Line_1_Description', 

295 'Line_1_Amount', 

296 'Line_1_DetailType', 

297 'Line_1_ItemBasedExpenseLineDetail_BillableStatus', 

298 'Line_1_ItemBasedExpenseLineDetail_ItemRef_value', 

299 'Line_1_ItemBasedExpenseLineDetail_ItemRef_name', 

300 'Line_1_ItemBasedExpenseLineDetail_UnitPrice', 

301 'Line_1_ItemBasedExpenseLineDetail_Qty', 

302 'domain', 

303 'sparse' 

304 ] 

305 

306 def filter_columns(self, result: pd.DataFrame, query: ast.Select = None): 

307 columns = [] 

308 if query is not None: 

309 for target in query.targets: 

310 if isinstance(target, ast.Star): 

311 columns = self.get_columns() 

312 break 

313 elif isinstance(target, ast.Identifier): 

314 columns.append(target.value) 

315 if len(columns) > 0: 

316 result = result[columns] 

317 

318 

319class EmployeesTable(APITable): 

320 

321 def flatten_dict(self, data: dict, prefix: str = ""): 

322 flat_data = {} 

323 for key, value in data.items(): 

324 if isinstance(value, dict): 

325 flattened_sub_dict = self.flatten_dict(value, f"{prefix}{key}_") 

326 flat_data.update(flattened_sub_dict) 

327 elif isinstance(value, list): 

328 for i, item in enumerate(value): 

329 if isinstance(item, dict): 

330 flattened_sub_dict = self.flatten_dict(item, f"{prefix}{key}_{i}_") 

331 flat_data.update(flattened_sub_dict) 

332 else: 

333 flat_data[f"{prefix}{key}_{i}"] = item 

334 else: 

335 flat_data[prefix + key] = value 

336 return flat_data 

337 

338 def select(self, query: ast.Select) -> pd.DataFrame: 

339 qbo = self.handler.connect() 

340 employees_data = qbo.employees.get() 

341 flattened_employees_data = [self.flatten_dict(employee) for employee in employees_data] 

342 result = pd.DataFrame(flattened_employees_data) 

343 self.filter_columns(result, query) 

344 return result 

345 

346 def get_columns(self): 

347 return [ 

348 'BillableTime', 

349 'Id', 

350 'SyncToken', 

351 'MetaData_CreateTime', 

352 'MetaData_LastUpdatedTime', 

353 'GivenName', 

354 'FamilyName', 

355 'DisplayName', 

356 'PrintOnCheckName', 

357 'Active', 

358 'PrimaryPhone_FreeFormNumber', 

359 'HiredDate', 

360 'domain', 

361 'sparse' 

362 ] 

363 

364 def filter_columns(self, result: pd.DataFrame, query: ast.Select = None): 

365 columns = [] 

366 if query is not None: 

367 for target in query.targets: 

368 if isinstance(target, ast.Star): 

369 columns = self.get_columns() 

370 break 

371 elif isinstance(target, ast.Identifier): 

372 columns.append(target.value) 

373 if len(columns) > 0: 

374 result = result[columns]