LTSI-feedback-builder/main.py at master · CameronLooney/LTSI-feedback-builder · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
# Need options to upload one to 3 files so i can do if none options
# merge datasets on sch line item doing whiochever merge need it to probabily be efficent
# highlight yellow if status is under review with CSAM or action sdm is Not order created on tool Not processed on LTSI tool
import pandas as pd
import streamlit as st
import smtplib
from functools import reduce


# ideas
# join the all the columns then merge ?
st.set_page_config(page_title='LTSI Feedback Form')

st.write("""

# LTSI Feedback
### Instructions: \n
- If feedback is received from SDM in separate files this tool can be used.
- If the feedback files contain all open orders within them, Open Order file is not needed
- If the feedback files are reduced with just rows with new feedback then upload Open Orders File
- Once at least two files have been uploaded click create
### Contact me if issues arise:
Slack: @Cameron Looney \n
email: cameron_j_looney@apple.com""")
st.write("## Upload 1 to 3 Feedback Files")
feedback1 = st.file_uploader("Upload Feedback File 1", type="xlsx")
feedback2 = st.file_uploader("Upload Feedback File 2", type="xlsx")
feedback3 = st.file_uploader("Upload Feedback File 3", type="xlsx")
st.write("## Upload Open Orders File")
open_orders = st.file_uploader("Upload Open Order File if feedback does not contain all open order rows", type="xlsx")
if st.button("Create Feedback"):
    def download_file(file):
        import io
        # Writing df to Excel Sheet
        buffer = io.BytesIO()
        with pd.ExcelWriter(buffer, engine='xlsxwriter') as writer:
            file.to_excel(writer, sheet_name='Sheet1', index=False)
            workbook = writer.book
            worksheet = writer.sheets['Sheet1']
            formatdict = {'num_format': 'dd/mm/yyyy'}
            fmt = workbook.add_format(formatdict)
            worksheet.set_column('K:K', None, fmt)
            worksheet.set_column('L:L', None, fmt)
            for column in file:
                column_width = max(file[column].astype(str).map(len).max(), len(column))
                col_idx = file.columns.get_loc(column)
                writer.sheets['Sheet1'].set_column(col_idx, col_idx, column_width)
                worksheet.autofilter(0, 0, file.shape[0], file.shape[1])

            writer.save()
            from datetime import date

            today = date.today()
            d1 = today.strftime("%d/%m/%Y")
            st.write("Download Completed File:")
            st.download_button(
                label="Download Excel worksheets",
                data=buffer,
                file_name="LTSI_feedback_" + d1 + ".xlsx",
                mime="application/vnd.ms-excel"
            )


    def old_feedback_getter(df):
        cols = [8]
        col_count = 37
        if df.shape[1] >= 39:
            while col_count < df.shape[1]:
                cols.append(col_count)
                col_count += 1

        return df.iloc[:, cols]

    def new_feedback_getter(df):
        return df.iloc[:, [8, 34, 35, 36]]

    def open_new_feedback_merge(open,new_feedback):
        return open.merge(new_feedback, how="left", on="Sales Order and Line Item")

    def case2(feedback,open_orders):
        feed1 = pd.read_excel(feedback, sheet_name=0, engine="openpyxl")
        openOrders = pd.read_excel(open_orders, sheet_name=0, engine="openpyxl")
        old_feedback = old_feedback_getter(feed1)
        new_feedback = new_feedback_getter(feed1)
        open = openOrders.iloc[:, :33]
        combined_feedback = open.merge(new_feedback, how="left", on="Sales Order and Line Item")
        final = combined_feedback.merge(old_feedback, how="left", on="Sales Order and Line Item")
        download_file(final)
    def case3(feedback1,feedback2,open_orders):
        feed1 = pd.read_excel(feedback1, sheet_name=0, engine="openpyxl")
        feed2 = pd.read_excel(feedback2, sheet_name=0, engine="openpyxl")
        openOrders = pd.read_excel(open_orders, sheet_name=0, engine="openpyxl")
        old_feedback1 = old_feedback_getter(feed1)
        new_feedback1 = new_feedback_getter(feed1)
        old_feedback2 = old_feedback_getter(feed2)
        new_feedback2 = new_feedback_getter(feed2)
        open = openOrders.iloc[:, :33]
        joined_new_feedback = pd.concat([new_feedback1, new_feedback2], ignore_index=True)
        joined_old_feedback = pd.concat([old_feedback1, old_feedback2], ignore_index=True)
        combined_feedback = open.merge(joined_new_feedback, how="left", on="Sales Order and Line Item")
        final = combined_feedback.merge(joined_old_feedback, how="left", on="Sales Order and Line Item")
        download_file(final)
    def case4(feedback1,feedback2,feedback3, open_orders):
        feed1 = pd.read_excel(feedback1, sheet_name=0, engine="openpyxl")
        feed2 = pd.read_excel(feedback2, sheet_name=0, engine="openpyxl")
        feed3 = pd.read_excel(feedback3, sheet_name=0, engine="openpyxl")
        openOrders = pd.read_excel(open_orders, sheet_name=0, engine="openpyxl")
        old_feedback1 = old_feedback_getter(feed1)
        new_feedback1 = new_feedback_getter(feed1)
        old_feedback2 = old_feedback_getter(feed2)
        new_feedback2 = new_feedback_getter(feed2)
        old_feedback3 = old_feedback_getter(feed3)
        new_feedback3 = new_feedback_getter(feed3)
        open = openOrders.iloc[:, :33]
        joined_new_feedback = pd.concat([new_feedback1, new_feedback2, new_feedback3], ignore_index=True)
        joined_old_feedback = pd.concat([old_feedback1, old_feedback2, old_feedback3], ignore_index=True)
        combined_feedback = open.merge(joined_new_feedback, how="left", on="Sales Order and Line Item")
        final = combined_feedback.merge(joined_old_feedback, how="left", on="Sales Order and Line Item")
        download_file(final)
    def case5(feedback1,feedback2):
        feed1 = pd.read_excel(feedback1, sheet_name=0, engine="openpyxl")
        feed2 = pd.read_excel(feedback2, sheet_name=0, engine="openpyxl")
        open = feed1.iloc[:, :33]
        old_feedback = old_feedback_getter(feed1)
        # drop na
        new_feedback1 = new_feedback_getter(feed1)
        new_feedback2 = new_feedback_getter(feed2)
        new_feedback1 = new_feedback1[new_feedback1.iloc[:, 1].notna()]
        new_feedback2 = new_feedback2[new_feedback2.iloc[:, 1].notna()]
        joined_new_feedback = pd.concat([new_feedback1, new_feedback2], ignore_index=True)
        combined_feedback = open.merge(joined_new_feedback, how="left", on="Sales Order and Line Item")
        final = combined_feedback.merge(old_feedback, how="left", on="Sales Order and Line Item")
        download_file(final)
    def case6(feedback1,feedback2,feedback3):
        feed1 = pd.read_excel(feedback1, sheet_name=0, engine="openpyxl")
        feed2 = pd.read_excel(feedback2, sheet_name=0, engine="openpyxl")
        feed3 = pd.read_excel(feedback3, sheet_name=0, engine="openpyxl")
        open = feed1.iloc[:, :33]
        old_feedback = old_feedback_getter(feed1)
        # drop na
        new_feedback1 = new_feedback_getter(feed1)
        new_feedback1 = new_feedback1[new_feedback1.iloc[:, 1].notna()]
        new_feedback2 = new_feedback_getter(feed2)
        new_feedback2 = new_feedback2[new_feedback2.iloc[:, 1].notna()]
        new_feedback3 = new_feedback_getter(feed3)
        new_feedback3 = new_feedback3[new_feedback3.iloc[:, 1].notna()]
        joined_new_feedback = pd.concat([new_feedback1, new_feedback2,new_feedback3], ignore_index=True)
        combined_feedback = open.merge(joined_new_feedback, how="left", on="Sales Order and Line Item")
        final = combined_feedback.merge(old_feedback, how="left", on="Sales Order and Line Item")
        download_file(final)

    # Case 1 feedback + no open (has all rows)
    if feedback1 is not None and feedback2 is None and feedback3 is None and  open_orders is None:
        st.error("File already complete, no need to upload")
    if feedback1 is None and feedback2 is not  None and feedback3 is None and  open_orders is None:
        st.error("File already complete, no need to upload")
    if feedback1 is None and feedback2 is None and feedback3 is not  None and  open_orders is None:
        st.error("File already complete, no need to upload")

    # Case 2 one feedback + open -> combine
    if feedback1 is not None and feedback2 is None and feedback3 is None and open_orders is not None:
        case2(feedback1,open_orders)
    if feedback1 is  None and feedback2 is not None and feedback3 is None and open_orders is not None:
        case2(feedback2,open_orders)
    if feedback1 is  None and feedback2 is None and feedback3 is not  None and open_orders is not None:
        case2(feedback3,open_orders)

    # Case 3 two feedback + open -> combine
    if feedback1 is not None and feedback2 is not None and feedback3 is None and open_orders is not None:
        case3(feedback1,feedback2,open_orders)
    if feedback1 is not None and feedback2 is  None and feedback3 is not None and open_orders is not None:
        case3(feedback1,feedback3,open_orders)
    if feedback1 is None and feedback2 is not None and feedback3 is not None and open_orders is not None:
        case3(feedback2,feedback3,open_orders)

    # Case 4 3 feedback + open orders
    if feedback1 is not None and feedback2 is not None and feedback3 is not None and open_orders is not None:
        case4(feedback1,feedback2,feedback3, open_orders)

    # Case 5 2 feedbacks and no open orders
    if feedback1 is not None and feedback2 is not None and feedback3 is  None and open_orders is None:
        case5(feedback1,feedback2)
    if feedback1 is not None and feedback2 is  None and feedback3 is not None and open_orders is None:
        case5(feedback1,feedback3)
    if feedback1 is  None and feedback2 is not None and feedback3 is not None and open_orders is None:
        case5(feedback2,feedback3)

    # Case 6 3 feedbacks and no Open
    if feedback1 is not None and feedback2 is not None and feedback3 is not None and open_orders is  None:
        case6(feedback1,feedback2,feedback3)

    # Case 7 0 feedback and Open
    if feedback1 is None and feedback2 is None and feedback3 is None and open_orders is not None:
        st.error("Error: No Feedback uploaded. \n\n"
                 "Open Order file up to date")

    # Case 8 no files uploaded
    if feedback1 is None and feedback2 is None and feedback3 is None and open_orders is None:
        st.error("Error: No Feedback/Files uploaded.")