# -*- coding: utf-8 -*- import os from collections import defaultdict import pandas as pd import numpy as np import openpyxl import time from pandas import Index from calendar import monthrange import datetime import calendar import logging import platform plat = platform.system().lower() # -*- 跑2023-01月脚本 02-06需求 -*- ''' 戴姆勒脚本变更代码 各word子账号合并到一个主账号，只要总数 ''' ############初始化目录地址 merge_file_dir = '/server/billing/daimler/merge_data/' alter_dbr_dir = '/server/billing/daimler/alter_data/' ############初始化变量参数 #####税前扣除 bs = float(-15000) # Refund # 增值税率 rate = float(1.06) # 增值税后金额 金额*rate(税率) bsAmountTax = (float(bs) * float(rate)) # 增值税得出的金额. bsTax = float(bsAmountTax) - bs # 增加金额汇总 taxSum = bsTax addSum = bs amountTaxSum = bsAmountTax # 扣除主账号 editPayerAccountId = '563347375094' # 扣除子账号 editLinkedAccountId = '444466323067' editLinkedAccountId2 = '10000100010001' daimler_map = { "DGRC": { '231703577464', '231732518549', '233198924213', '233281076386', '315154767884', '315166436895', '318222460809', '318251735617', '319093137346', '319095105578', '319131705619', '032861711361', '032968694919', '033176839117', '412333074862', '412592672283', '413185959261', '571509464092', '571914751254', '572500455038', '604214497778', '605281233780', '605492383956', '606333019050', '607073490803', '607215769011', '607251498050', '745178511853', '745186985354', '745404617525', '843064179036', '843146470903', '843403612003', '938026107066', '959875115713', '846363994602', '847121021275', '847349672033', '847535715113', '787753559055', '157425357330', '157571289045', '222510028891', '222726652783', '394747274994', '641166177863', '641228128543', '150264918770', '150265876719', '154308107197', '154344974850', '154396341553', '154377832036', # ===========2022-12 '000062495386', '000109320591', '016043598990', '187021522403', '206944717367', '207006135149', # ============2023-03 '155851258827' }, "BMBS": { '825095481624', '673531903747', '45016790339', '005952357992', '006618809156', '008855685915', '099609831577', '149729312152', '158107609377', '158515190244', '158636931693', '160478048914', '161725851224', '179818971150', '180063110126', '180280548979', '259625653064', '263249970047', '263902424712', '264554558165', '381109879703', '381965952467', '382093053518', '420190688017', '420235015001', '420347211434', '468973200212', '470975648111', '471722715986', '606431034865', '606635571449', '614449149933', '614450235268', '662090766598', '662661601741', '680011596858', '693339510868', '693619437440', '693700422527', '694079957533', '694096809975', '694098823346', '720819555472', '720826711875', '720959775198', '721033167150', '721152002098', '721305584708', '731145591192', '738568990634', '738993122676', '824698755435', '824850862427', '825095481624', '830723988525', '830765046968', '833723158764', '845876480170', '846152349690', '846800936473', '864364323687', '864518602649', '864710874555', '902795390617', '939002646100', '965407060405', '965453987633', '965517804909', '060167615401', '076513887860', '420626856655', '421314535486', '329076050796', '329159661224', '376544640131', '376560970070', '585581760571', '586490397677', '836190164454', '836212206490', '444387297159', '444466323067', '551526584239', '551543229458', '603217519688', '623235613882', '633869915042', '003518598515', '003606583618', '634001137982', '721172726690', '723527083861', '315650031369', '315666852707', '342121670255', '342146215747', '365617091351', '365678264806', '814940158343', '815152760759', '815217109270', '815218645700', '815470951043', '815623564417', '815665691798', '815773864716', '815919514877', '816040880523', "007826675569", "007933598034", "008072868499", "008177890141", "008228029835", "008283276620", "008287392585", "008473233401", "008516644951", "008528838699", "010265887827", "018669973095", "019723979408", "019784009857", "019866036447", "019962796551", "019997264032", "020047295228", "020339118543", "020498088730", "020596821809", "020868571044", "021034476631", "021164366546", "021202811639", "021577333626", "021667202555", "021821803133", "021908435203", "022176271466", "022248774214", "023812125476", "023901143212", "023945165201", "023962011534", "024002785254", "024107434926", "024208508347", "024236970373", "024253823497", "024313569899", "024359695628", "024412100834", "024483855859", "024563654013", "024633815385", "024667173878", "024830901242", "024838505922", "024853602879", "024874476753", "045025227496" }, "BMBS-Rollout": { '111035368608', '143293953769', '143419428863' }, "MBCL": { '115032441317', '115159631000' }, 'DGRC-Rollout': { '157701174715', '157708043020', '157784268223', '157844458947', '157876968059', '157972004829', '158027931531', '767680705323', '778776044487', '778911054371', '779093734419', '779265673788', '779378744728', '779435304058', '779482782114', '779517320026', '779563825200' }, 'DPTS': { '806730088729', '806835816217', '807016928789', '807180278334', '807196738502', '807321035642', } } # csv表头字段 dbr_index = Index(['InvoiceID', 'PayerAccountId', 'LinkedAccountId', 'RecordType', 'RecordId', 'ProductName', 'RateId', 'SubscriptionId', 'PricingPlanId', 'UsageType', 'Operation', 'AvailabilityZone', 'ReservedInstance', 'ItemDescription', 'UsageStartDate', 'UsageEndDate', 'UsageQuantity', 'BlendedRate', 'BlendedCost', 'UnBlendedRate', 'UnBlendedCost', 'ResourceId'], dtype='object') # 数据类型 data_type = { 'UsageStartDate': np.object, 'UsageEndDate': np.object, 'InvoiceID': np.object, 'ProductName': np.object, 'PayerAccountId': np.object, 'LinkedAccountId': np.object, 'UnBlendedCost': str, 'BlendedCost': str, 'ItemDescription': np.object, 'RecordType': np.object } ############方法 def check_path_creat(path): """ 检查路径是否存在，不存在就创建 :param path: 文件路径 :return: """ if not os.path.exists(path): os.mkdir(path) def del_file_form_path(filename: str, path: str): if filename in os.listdir(path): os.remove(os.path.join(merge_file_dir, filename)) def new_round(_float, _len=2) -> float: """ Parameters ---------- _float: _len: int, 指定四舍五入需要保留的小数点后几位数为_len Returns ------- type ==> float, 返回四舍五入后的值 """ try: if isinstance(_float, float): if 'e' in str(_float).lower(): return round(_float, _len) elif str(_float)[::-1].find('.') <= _len: return _float elif str(_float)[-1] == '5': return round(float(str(_float)[:-1] + '6'), _len) else: return round(_float, _len) elif isinstance(_float, str): _float = _float.replace(',', '') return new_round(float(_float), _len) else: return round(_float, _len) except Exception as e: print(_float, _len, e) def from_filename_get_datetime(filename: str): dt = filename[-7:] date = datetime.datetime.strptime(dt, "%Y-%m") days = calendar.monthrange(date.year, date.month) later1dt = date + datetime.timedelta(days[1] + 1) later1 = later1dt.strftime("%Y-%m-%d") n_days = calendar.monthrange(later1dt.year, later1dt.month) later2dt = later1dt + datetime.timedelta(n_days[1]) later2 = later2dt.strftime("%Y-%m-%d") return dt, later1, later2 class GenerateWord(object): def __init__(self, *args, **kwargs): self.args = args self.kwargs = kwargs @staticmethod def check_path_creat(path): if not os.path.exists(path): os.mkdir(path) class DaimlerGenerateWord(GenerateWord): def __init__(self, linkedid, result, filename, *args, **kwargs): super().__init__(*args, **kwargs) self.linkedid = linkedid self.result = result self.filename = filename self.link_dict = kwargs.get('link_dict', None) class CustomCsvByHour(object): def __init__(self, pathlist, *args, **kwargs): super().__init__(*args, **kwargs) self.costs_maps = defaultdict() self.path = [pathlist] self.finally_dict = defaultdict(float) self.sourcename = None self.es_iid = None self.es_total = 0 self.edp_iid = '' self.edp_total = 0 self.exclude_id = list() self.unblendedcost = 0 self.payer = None self.accountdt = pd.DataFrame() self.statementdt = pd.DataFrame() self.name = None self.filename = None self.partname = None self.filepath = None self.word_dict = defaultdict() self.init_edp_dict = defaultdict() self.es_data = None # self.dbr_index = None def create_new_csv(self): self.aws_feb={} self.costs_maps = {} for path in self.path: payer = path.split('-')[0] print(path.split('-')[0]) self.name = path self.edp_iid = '1356844351' if path.split('-')[0] == '010265887827': self.edp_iid = '1356844499' elif path.split('-')[0] == '565095721352': self.edp_iid = '1356843675' elif path.split('-')[0] == '565359735310': self.edp_iid = '1356844263' elif path.split('-')[0] == '563347375094': self.edp_iid = '1356844635' elif path.split('-')[0] == '563646727715': self.edp_iid = '1356844351' elif path.split('-')[0] == '011562250191': self.edp_iid = '1356843707' elif path.split('-')[0] == '011276597621': self.edp_iid = '1366731839' self.daimler_read_aws_feb_sinnetcostallocation(path.split('-')[0]) insert_data = defaultdict(list) leng = 2 startdate = '2023/12/1 0:00:00' enddate = '2023/12/31 23:59:59' #lineItem for k,v in self.aws_feb.items(): if k == 'total': continue insert_data['InvoiceID'].extend([self.edp_iid for _ in range(leng)]) insert_data['PayerAccountId'].extend([payer for _ in range(leng)]) insert_data['LinkedAccountId'].extend([k for _ in range(leng)]) insert_data['RecordType'].extend(['LineItem' for _ in range(leng)]) insert_data['ProductName'].extend(['Sinnet/L2C Support BJS', '']) # 改这俩 insert_data['UsageStartDate'].extend([startdate for _ in range(leng)]) insert_data['UsageEndDate'].extend([enddate for _ in range(leng)]) insert_data['ItemDescription'].extend(['Sinnet/L2C Support BJS', '税金 VAT 类型']) insert_data['BlendedCost'].extend(['{:.2f}'.format(float(v.get('unBlendedCost',0))), '{:.2f}'.format(float(v.get('unBlendedCost',0)) * 0.06)]) insert_data['UnBlendedCost'].extend(['{:.2f}'.format(float(v.get('unBlendedCost',0))), '{:.2f}'.format(float(v.get('unBlendedCost',0)) * 0.06)]) #新逻辑 awsCloudConner 金额 insert_data['InvoiceID'].extend([self.edp_iid for _ in range(leng)]) insert_data['PayerAccountId'].extend([payer for _ in range(leng)]) insert_data['LinkedAccountId'].extend([k for _ in range(leng)]) insert_data['RecordType'].extend(['LineItem' for _ in range(leng)]) insert_data['ProductName'].extend(['AWS Cloud Connet', '']) # 改这俩 insert_data['UsageStartDate'].extend([startdate for _ in range(leng)]) insert_data['UsageEndDate'].extend([enddate for _ in range(leng)]) insert_data['ItemDescription'].extend(['AWS Cloud Connet', '税金 VAT 类型']) insert_data['BlendedCost'].extend( ['{:.2f}'.format(float(v.get('awsCloudConnet',0))), '{:.2f}'.format(float(v.get('awsCloudConnet',0)) * 0.06)]) insert_data['UnBlendedCost'].extend( ['{:.2f}'.format(float(v.get('awsCloudConnet',0))), '{:.2f}'.format(float(v.get('awsCloudConnet',0)) * 0.06)]) a=int(editPayerAccountId); if a == int(payer) and int(editLinkedAccountId) == int(k): insert_data['InvoiceID'].extend([self.edp_iid for _ in range(leng)]) insert_data['PayerAccountId'].extend([payer for _ in range(leng)]) insert_data['LinkedAccountId'].extend([k for _ in range(leng)]) insert_data['RecordType'].extend(['LineItem' for _ in range(leng)]) insert_data['ProductName'].extend(['Sinnet/L2C Support BJS', '']) # 改这俩 insert_data['UsageStartDate'].extend([startdate for _ in range(leng)]) insert_data['UsageEndDate'].extend([enddate for _ in range(leng)]) insert_data['ItemDescription'].extend(['Sinnet/L2C Support BJS', '税金 VAT 类型']) insert_data['BlendedCost'].extend(['{:.2f}'.format(float(100000.00)), '{:.2f}'.format(float(100000.00) * 0.06)]) insert_data['UnBlendedCost'].extend(['{:.2f}'.format(float(100000.00)), '{:.2f}'.format(float(100000.00) * 0.06)]) #"", "010265887827", "007826675569", "AccountTotal", "", "", "", "", "", "", "", "", "", "关联账户# 007826675569 总额", "", "", "", "", "0.0700000000", "", "0.080095", "" #AccountTotal for k,v in self.aws_feb.items(): leng=1 if k == 'total': continue if a == int(payer) and int(editLinkedAccountId) == int(k): insert_data['InvoiceID'].extend(['' for _ in range(leng)]) insert_data['PayerAccountId'].extend([payer for _ in range(leng)]) insert_data['LinkedAccountId'].extend([k for _ in range(leng)]) insert_data['RecordType'].extend(['AccountTotal' for _ in range(leng)]) insert_data['ProductName'].extend(['']) # 改这俩 insert_data['UsageStartDate'].extend(['' for _ in range(leng)]) insert_data['UsageEndDate'].extend(['' for _ in range(leng)]) insert_data['ItemDescription'].extend(['关联账户# 019723979408 总额']) unBlendedCost = float(100000.00) * 1.06; awsCloudConnet = float(0) * 1.06; unBlendedCost = unBlendedCost + awsCloudConnet; insert_data['BlendedCost'].extend(['{:.2f}'.format(unBlendedCost)]) insert_data['UnBlendedCost'].extend(['{:.2f}'.format(unBlendedCost)]) else: insert_data['InvoiceID'].extend(['' for _ in range(leng)]) insert_data['PayerAccountId'].extend([payer for _ in range(leng)]) insert_data['LinkedAccountId'].extend([k for _ in range(leng)]) insert_data['RecordType'].extend(['AccountTotal' for _ in range(leng)]) insert_data['ProductName'].extend(['']) # 改这俩 insert_data['UsageStartDate'].extend(['' for _ in range(leng)]) insert_data['UsageEndDate'].extend(['' for _ in range(leng)]) insert_data['ItemDescription'].extend(['关联账户# 019723979408 总额']) unBlendedCost = float(v.get('unBlendedCost', 0)) * 1.06; awsCloudConnet = float(v.get('awsCloudConnet', 0)) * 1.06; unBlendedCost = unBlendedCost + awsCloudConnet; insert_data['BlendedCost'].extend(['{:.2f}'.format(unBlendedCost)]) insert_data['UnBlendedCost'].extend(['{:.2f}'.format(unBlendedCost)]) leng = 1 insert_data['InvoiceID'].extend(['' for _ in range(leng)]) insert_data['PayerAccountId'].extend([payer for _ in range(leng)]) insert_data['LinkedAccountId'].extend(['']) insert_data['RecordType'].extend(['StatementTotal' for _ in range(leng)]) insert_data['ProductName'].extend(['']) # 改这俩 insert_data['UsageStartDate'].extend(['' for _ in range(leng)]) insert_data['UsageEndDate'].extend(['' for _ in range(leng)]) insert_data['ItemDescription'].extend(['2023-12-01 00:00:00 - 2023-12-31 23:59:59 期间内的对账单总额是']) if self.aws_feb.get('total', '')=='': continue totalAmount=float(self.aws_feb.get('total','')) if a == int(payer) and int(editLinkedAccountId): totalAmount+=100000.00; insert_data['BlendedCost'].extend(['{:.2f}'.format(float(totalAmount) * 1.06)]) insert_data['UnBlendedCost'].extend(['{:.2f}'.format(float(totalAmount) * 1.06)]) #self.invoiceId='1355493111'Sinnet Support-011562250191-2023-08.csv self.partname = 'Sinnet Support-'+path.split('-')[0]+'-'+defaultMonth+'.csv' self.sourcename = path self.init_part_file() fill_data = insert_data # 获取导出LineItem的数据 self.save_data_to_dbr(fill_data) # 写入修改部分_文件 # partname保存 修改部分_文件,self.filename 改文件 def save_data_to_dbr(self, insert_data): partname = os.path.join(alter_dbr_dir,self.partname) with open(partname, 'a', encoding='utf-8') as pw: dbr_dict = {v: int(k) for k, v in enumerate(list(dbr_index))} for i in range(len(list(insert_data.values())[0])): empty_data = ["" for _ in range(len(dbr_index))] for k, v in insert_data.items(): empty_data[dbr_dict[k]] = v[i] # 修改部分写入 pw.write(','.join('"{}"'.format(x) for x in empty_data) + '\n') # 初始化：修改部分_ 文件标题行 def init_part_file(self): partname = os.path.join(alter_dbr_dir,self.partname) with open(partname, 'w', encoding='gb2312') as f: f.write(','.join('"{}"'.format(x) for x in list(dbr_index)) + '\n') def daimler_read_aws_feb_sinnetcostallocation(self, payerId): self.aws_feb = {} read_file = "/server/billing/daimler/config/aws_feb-202302.csv" if plat == 'windows': read_file = 'D:\\workspace\\zhangdanjiaoben\\billing-python\\daimler\\config\\aws_feb.csv' total = 0 with open(read_file, 'r', encoding='gb2312') as r: # open(alterpath, 'w', encoding='utf-8') as w: num = 0 while True: line = r.readline() if not line: break num += 1 data = line.split(',') payer = data[0].replace(' ', '') linkedAccountId = data[1].replace(' ', '') if len(linkedAccountId) == 10: linkedAccountId = "00" + linkedAccountId elif len(linkedAccountId) == 9: linkedAccountId = "000" + linkedAccountId elif len(linkedAccountId) == 8: linkedAccountId = "0000" + linkedAccountId elif len(linkedAccountId) == 7: linkedAccountId = "00000" + linkedAccountId elif len(linkedAccountId) == 6: linkedAccountId = "000000" + linkedAccountId elif len(linkedAccountId) == 11: linkedAccountId = "0" + linkedAccountId unBlendedCost = data[2].replace('\n', '') awsCloudConnet = data[3].replace('\n','') if not payer in payerId: continue if unBlendedCost=='' or unBlendedCost == None: continue cost={} cost['unBlendedCost'] = unBlendedCost cost['awsCloudConnet'] = awsCloudConnet self.aws_feb[linkedAccountId] = cost print(self.aws_feb[linkedAccountId]) total += float(unBlendedCost) total += float(awsCloudConnet) self.aws_feb["total"] = total print(self.aws_feb) defaultMonth = '2023-12' if __name__ == '__main__': logging.basicConfig(filename='logger.log', level=logging.INFO) if (plat == 'windows'): # 1==1 本地运行是目录 alter_dbr_dir = 'D:\\workspace\\zhangdanjiaoben\\billing-python\\daimler\\alter_data' merge_file_dir = 'D:\\workspace\\hangdanjiaoben\\billing-python\\daimler\\merge_data' dbr_typle = ( ('563347375094-aws-billing-detailed-line-items-with-resources-and-tags-ACTS-'+defaultMonth+'.csv', '563347375094-aws-billing-detailed-line-items-with-resources-and-tags-ACTS-Ningxia-'+defaultMonth+'.csv',), # ('563646727715-aws-billing-detailed-line-items-with-resources-and-tags-ACTS-'+defaultMonth+'.csv', # '563646727715-aws-billing-detailed-line-items-with-resources-and-tags-ACTS-Ningxia-'+defaultMonth+'.csv',), # ('565095721352-aws-billing-detailed-line-items-with-resources-and-tags-ACTS-2023-12.csv', # '565095721352-aws-billing-detailed-line-items-with-resources-and-tags-ACTS-Ningxia-2023-12.csv',), # ('565359735310-aws-billing-detailed-line-items-with-resources-and-tags-ACTS-2023-12.csv', # '565359735310-aws-billing-detailed-line-items-with-resources-and-tags-ACTS-Ningxia-2023-11.csv',), # ('010265887827-aws-billing-detailed-line-items-with-resources-and-tags-ACTS-2023-11.csv', # '010265887827-aws-billing-detailed-line-items-with-resources-and-tags-ACTS-Ningxia-2023-11.csv',), # ('011562250191-aws-billing-detailed-line-items-with-resources-and-tags-ACTS-2023-11.csv', # '011562250191-aws-billing-detailed-line-items-with-resources-and-tags-ACTS-Ningxia-2023-11.csv'), # ('011276597621-aws-billing-detailed-line-items-with-resources-and-tags-ACTS-2023-11.csv', # '011276597621-aws-billing-detailed-line-items-with-resources-and-tags-ACTS-Ningxia-2023-11.csv'), ) # 3.核心计算 for i in dbr_typle: CustomCsvByHour(i[0]).create_new_csv()