KwaixiaodianJstPurchaseItemsRange_append.py 3.3 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293
  1. # Author renyupeng
  2. # coding=utf-8
  3. # @Time : 2024/11/27 13:24
  4. # @Site :
  5. # @File : KwaixiaodianJstPurchaseItemsRange.py
  6. # @Software: PyCharm
  7. # @contact: renyupeng@c-top.com.cn
  8. # @Tel 1501435553
  9. import threading
  10. from utils.mysql_helper import batch_insertPro, batch_insertPro_jst
  11. from utils.mysql_utils_pro import MysqlProUtils
  12. class KwaixiaodianJstPurchaseItemsRange:
  13. def __init__(self):
  14. self.conn = MysqlProUtils()
  15. def getSkuId(self):
  16. sql = """
  17. select sku_id from ruixuan.kwaixiaodian_jst_purchase_items
  18. group by sku_id
  19. """
  20. results = self.conn.QueryAll(sql=sql)
  21. items_per_thread = len(results)
  22. threads = []
  23. for i in range(1):
  24. start = i * items_per_thread
  25. end = start + items_per_thread
  26. thread = threading.Thread(target=self.handlerSkuRange, args=(results[start:end],))
  27. threads.append(thread)
  28. thread.start()
  29. for thread in threads:
  30. thread.join()
  31. def handlerSkuRange(self, sku_list):
  32. sku_item_list = []
  33. for sku_id in sku_list:
  34. sku_id = sku_id[0].decode("utf-8")
  35. sku_range_delete = """
  36. delete from ruixuan.kwaixiaodian_jst_purchase_items_range where sku_id='{sku_id}'
  37. """.format(sku_id=sku_id)
  38. self.conn.Operate(sku_range_delete)
  39. sku_sql = """select sku_id,qty,price,po_date ,poi_id,po_id
  40. from ruixuan.kwaixiaodian_jst_purchase_items
  41. where sku_id='{sku_id}'
  42. order by po_date """.format(sku_id=sku_id)
  43. data_list = self.conn.QueryAll(sql=sku_sql)
  44. sumqty = 0
  45. end_qty = 0
  46. for i, data in enumerate(data_list):
  47. if i == 0:
  48. start_qty = 0
  49. end_qty = data[1]
  50. qty = data[1]
  51. price = data[2]
  52. po_date = data[3].decode("utf-8")
  53. sumqty = data[1]
  54. poi_id = data[4]
  55. po_id = data[5]
  56. sku_num = i + 1
  57. else:
  58. sku_id = data[0].decode("utf-8")
  59. qty = data[1]
  60. price = data[2]
  61. sumqty += data[1]
  62. po_date = data[3].decode("utf-8")
  63. poi_id = data[4]
  64. po_id = data[5]
  65. start_qty = end_qty + 1
  66. end_qty = sumqty
  67. sku_num = i + 1
  68. # sku_item = {
  69. # "sku_id": sku_id,
  70. # "qty": qty,
  71. # "price": price,
  72. # "po_date": po_date,
  73. # "poi_id": poi_id,
  74. # "po_id": po_id,
  75. # "start_qty": start_qty,
  76. # "end_qty": end_pty,
  77. # "sku_num": i + 1
  78. # }
  79. sku_item=(
  80. poi_id, po_id, sku_id, qty, price, po_date, start_qty, end_qty, sku_num
  81. )
  82. sku_item_list.append(sku_item)
  83. batch_insertPro_jst(sku_item_list, 'kwaixiaodian_jst_purchase_items_range')
  84. if __name__ == '__main__':
  85. KwaixiaodianJstPurchaseItemsRange().getSkuId()