KwaixiaodianJstPurchaseItemsRange_append.py 3.6 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103
  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,a.po_date ,poi_id,a.po_id
  40. from ruixuan.kwaixiaodian_jst_purchase_items a
  41. left join ruixuan.kwaixiaodian_jst_purchase b
  42. on a.po_id=b.po_id
  43. where sku_id='{sku_id}' and b.status in('Finished','Confirmed' )
  44. and b.po_id is not null
  45. order by po_date
  46. """.format(sku_id=sku_id)
  47. data_list = self.conn.QueryAll(sql=sku_sql)
  48. sumqty = 0
  49. end_qty = 0
  50. for i, data in enumerate(data_list):
  51. if i == 0:
  52. start_qty = 0
  53. end_qty = data[1]-1
  54. qty = data[1]
  55. price = data[2]
  56. if data[3] is None:
  57. po_date=None
  58. else:
  59. po_date = data[3].decode("utf-8")
  60. sumqty = data[1]
  61. poi_id = data[4]
  62. po_id = data[5]
  63. sku_num = i + 1
  64. else:
  65. sku_id = data[0].decode("utf-8")
  66. qty = data[1]
  67. price = data[2]
  68. sumqty += data[1]
  69. if data[3] is None:
  70. po_date = None
  71. else:
  72. po_date = data[3].decode("utf-8")
  73. poi_id = data[4]
  74. po_id = data[5]
  75. start_qty = end_qty + 1
  76. end_qty = sumqty-1
  77. sku_num = i + 1
  78. # sku_item = {
  79. # "sku_id": sku_id,
  80. # "qty": qty,
  81. # "price": price,
  82. # "po_date": po_date,
  83. # "poi_id": poi_id,
  84. # "po_id": po_id,
  85. # "start_qty": start_qty,
  86. # "end_qty": end_pty,
  87. # "sku_num": i + 1
  88. # }
  89. sku_item=(
  90. poi_id, po_id, sku_id, qty, price, po_date, start_qty, end_qty, sku_num
  91. )
  92. sku_item_list.append(sku_item)
  93. batch_insertPro_jst(sku_item_list, 'kwaixiaodian_jst_purchase_items_range')
  94. if __name__ == '__main__':
  95. KwaixiaodianJstPurchaseItemsRange().getSkuId()