#####Time Flow Simulation######
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
from datetime import timedelta
import datetime
import csv

data=pd.read_excel('CF66-all.xlsx')
data.sort_values(by=['WBL_AUD_DT'],ascending=True,inplace=True)
or_data=pd.read_excel('CF66-ordinary.xlsx')
rule=pd.read_excel('6. Existing pricing strategy.xlsx')
or_name=or_data['WBL_NUM'].unique()

data['ordinary']=0
for i in range(len(data)):
	if data.iloc[i,2] in or_name:
		data.iloc[i,9]=1

data['volume']=data['CNTR_TYPE']
for i in range(len(data)):
	data.iloc[i,10]=int(data.iloc[i,10][0:2])


raw_data=data.groupby('SVVD')
data_to_list=list(raw_data)
raw_list=[]
for i in data_to_list:
	raw_list.append(i[1])




total_volume=raw_data['volume'].sum()*1.2

thisrule=rule.groupby(['装港','卸港']).get_group(('营口','海口'))
group_rule=thisrule.groupby(['开始天数','结束天数'])
rule_to_list=list(group_rule)
day_list=[]
rule_list=[]
for i in rule_to_list:
	day_list.append(i[0])
	rule_list.append(i[1])
	


m=datetime.timedelta(days=14)
newlist=[]

for i in raw_list:
	i['WBL_AUD_DT']=pd.to_datetime(i['WBL_AUD_DT'])
	m=datetime.timedelta(days=14)
	j=i[i['WBL_AUD_DT']>=i['WBL_AUD_DT'].max()-m]
	newlist.append(j)

del(raw_list)

for i in newlist:
	i['acc_volume']=i['volume'].cumsum()	
	i['total_volume']=i['volume'].sum()*1.2
	m=datetime.timedelta(days=14)
	i['day']=(i['WBL_AUD_DT']-i['WBL_AUD_DT'].max()+m).dt.days
	i['acc_rate']=i['acc_volume']/i['total_volume']*100
	i['new_AMT']=i['AMT']
	

for k in range(len(newlist)):
	acc_20gp=0
	acc_40gp=0
	acc_40hq=0
	print('k='+str(k))
	for i in range(len(day_list)):
		print('i='+str(i))
		first_day=day_list[i][0]
		last_day=day_list[i][1]
		flag=[0]*len(rule_list[i])
		for j in range(len(newlist[k])):
			if newlist[k].iloc[j]['day']>=first_day and newlist[k].iloc[j]['day']<last_day and newlist[k].iloc[j]['ordinary']==1:
				for z in range(len(rule_list[i])):
					print('z='+str(z))
					if newlist[k].iloc[j]['acc_rate']>rule_list[i].iloc[z]['舱位利用率阈值']and rule_list[i].iloc[z]['涨价/降价']=='涨价':
						if flag[z]==0:
							flag[z]=1
							acc_20gp+=rule_list[i].iloc[z]['20GP']
							acc_40gp+=rule_list[i].iloc[z]['40GP']
							acc_40hq+=rule_list[i].iloc[z]['40HQ']
								
					if newlist[k].iloc[j]['acc_rate']<rule_list[i].iloc[z]['舱位利用率阈值']and rule_list[i].iloc[z]['涨价/降价']=='降价':
						if flag[z]==0:
							flag[z]=1
							acc_20gp-=rule_list[i].iloc[z]['20GP']
							acc_40gp-=rule_list[i].iloc[z]['40GP']
							acc_40hq-=rule_list[i].iloc[z]['40HQ']
							
					print(flag)
					print(acc_20gp)
					print(acc_40gp)
					print(acc_40hq)
					
				if newlist[k].iloc[j]['CNTR_TYPE']=='20GP':
						newlist[k].iloc[j,15]+=acc_20gp
				if newlist[k].iloc[j]['CNTR_TYPE']=='40GP':
						newlist[k].iloc[j,15]+=acc_40gp
				if newlist[k].iloc[j]['CNTR_TYPE']=='40HQ':
						newlist[k].iloc[j,15]+=acc_40hq
					
			


			
for i in newlist:
	print('revenue:'+str(i['AMT'].sum()))
	print('newrevenue:'+str(i['new_AMT'].sum()))
newlist[0].to_csv('voyage1.csv')
newlist[1].to_csv('voyage2.csv')
newlist[2].to_csv('voyage3.csv')
		

[((9, 5, 9, 35), 'pandas.read_excel', 'pd.read_excel', ({(9, 19, 9, 34): '"""CF66-all.xlsx"""'}, {}), "('CF66-all.xlsx')", True, 'import pandas as pd\n'), ((11, 8, 11, 43), 'pandas.read_excel', 'pd.read_excel', ({(11, 22, 11, 42): '"""CF66-ordinary.xlsx"""'}, {}), "('CF66-ordinary.xlsx')", True, 'import pandas as pd\n'), ((12, 5, 12, 55), 'pandas.read_excel', 'pd.read_excel', ({(12, 19, 12, 54): '"""6. Existing pricing strategy.xlsx"""'}, {}), "('6. Existing pricing strategy.xlsx')", True, 'import pandas as pd\n'), ((47, 2, 47, 29), 'datetime.timedelta', 'datetime.timedelta', (), '', False, 'import datetime\n'), ((51, 17, 51, 48), 'pandas.to_datetime', 'pd.to_datetime', ({(51, 32, 51, 47): "i['WBL_AUD_DT']"}, {}), "(i['WBL_AUD_DT'])", True, 'import pandas as pd\n'), ((52, 3, 52, 30), 'datetime.timedelta', 'datetime.timedelta', (), '', False, 'import datetime\n'), ((61, 3, 61, 30), 'datetime.timedelta', 'datetime.timedelta', (), '', False, 'import datetime\n')]