@author: Wiebke.Toussaint
"""
#This script reads all damaged stock processed by Spree QC teams.
#Script must be scheduled to run on a daily basis at 23:45.
import pandas as pd
from pandas import DataFrame
from datetime import date, timedelta
from pandas import ExcelWriter
import MyFunx, gdocs
today = date.today()
#Reading from QCDamages google doc
c = gdocs.authenticate_gdocs()
sht = c.open('QCDamages')
worksht = sht.worksheet('Sheet1')
if worksht.cell(2,1).value == "":
print "No damages scanned"
Damaged = DataFrame(columns = ['ProductID', 'Reason for damage', 'QC Responsible','Date', 'Damaged'])
dmgs = DataFrame(columns = ['ProductID', 'Reason for damage', 'QC Responsible','Date'])
lgth = 0
else:
info = worksht.get_all_values()
headers = info.pop(0)
dmgs = DataFrame(data = info, columns = ['ProductID', 'Reason for damage', 'QC Responsible'])
lgth = len(dmgs)
dup = [d.upper() for d in dmgs['ProductID']]
dmgs['ProductID'] = dup
def InboundData(lastmonth, nextmonth, today):
import numpy as np
import pandas as pd
from pandas import DataFrame
import gspread
import MyFunx, gdocs
#==============================================================================
# Import from all required data sources
#==============================================================================
#Import Brightpearl Detail Report data
columns = [u"Order ID", u"Ref", u"SKU", u"Status", u"Quantity"]
BPdetail = pd.read_csv('BPdetail.csv', header = 0, usecols = columns, encoding = 'iso-8859-1')
BPdetail['Order ID'] = BPdetail['Order ID'].map(lambda x: unicode(x))
BPdetail.rename(columns={'Order ID': u'POs', 'Quantity': u'BP Qty'}, inplace=True)
BPdet = BPdetail[BPdetail['Status'].str.contains('Cancel PO')==False]
BPdet = pd.pivot_table(BPdet, values = [u'BP Qty'], index = [u'SKU',u'POs',u'Ref',u'Status'], aggfunc=np.sum)
BPdet.reset_index(inplace=True)
CancelledPOs = BPdetail[BPdetail['Status']=='Cancel PO']
CancelledPOs = CancelledPOs.groupby('POs').agg({'SKU':'count'})
#Import Brightpearl PO Report data
columns = ["Order ID", "Delivery due"]
BPreport = pd.read_csv('BPreport.csv', header = 0, usecols = columns, parse_dates = [1])
BPreport = BPreport.dropna(axis = 0,how = 'all') #removes empty rows
BPreport['Order ID'] = BPreport['Order ID'].map(lambda x: x.strip('PO#')) #removes text in front of PO number
BPreport.rename(columns={'Order ID': 'POs', 'Delivery due':'DeliveryDue'}, inplace=True)
BP = pd.merge(BPdet, BPreport, on = 'POs', how = 'left', sort = False)
#Import Epping Receiving Report data
c = gdocs.authenticate_gdocs()
sht = c.open('Epping Receiving Report')
worksheet = sht.worksheet('Booked')
info = worksheet.get_all_values()
headers = info.pop(0)
B_R = DataFrame(data = info, columns = headers)
Bookd = B_R[[u'POs',u'Date booked']]
Bookd = Bookd.replace('',np.nan)
Bookd = Bookd.dropna(subset = [u'Date booked'], thresh = 1)
Bookd = Bookd.drop_duplicates(subset = [u'POs'], take_last = False)
Bookd = Bookd.loc[Bookd.POs != ""]
Bookd[u'Date booked'] = pd.to_datetime(Bookd[u'Date booked'], infer_datetime_format = True)
Receivd = B_R[['POs', 'Partial delivery', 'Date received']]
Receivd = Receivd.replace('',np.nan)
Receivd = Receivd.dropna(subset = ['Date received'], thresh = 1)
Receivd = Receivd.drop_duplicates(subset = ['POs'], take_last = True)
Receivd = Receivd.loc[Receivd.POs != ""]
Receivd['Date received'] = pd.to_datetime(Receivd['Date received'], infer_datetime_format = True)
#Import Rolling Stock data
Stock = pd.ExcelFile('Z:\\SUPPLY CHAIN\\Python Scripts\\02_StockCount\\Rolling Stock.xlsx')
QCed = Stock.parse('Sheet1', skiprows = 0, index = None, parse_cols = (1,3,4,5), encoding = 'utf-8')
QCed.rename(columns={'Date': u'LastQCed', 'PO':u'POs','ProductID':u'SKU'}, inplace=True)
poqc = [unicode(p) for p in QCed['POs']]
QCed['POs'] = poqc
QCed = QCed.groupby(['POs','SKU']).agg({'Qty Counted':np.sum, 'LastQCed':np.max})
QCed.reset_index(inplace=True)
#Import Rolling Damages
Damages = pd.ExcelFile('03_Damages_OS\\Rolling Damages.xlsx')
Damages = Damages.parse('Sheet1', skiprows = 0, index = None, encoding = 'utf-8')
SKU = Damages['SKU'].value_counts()
Damagd = DataFrame(data = SKU)
Damagd.reset_index(level=0, inplace=True)
Damagd.columns = [u'SKU', u'Qty Damaged']
#Import Lulu Assortment Plans
## table = "vw_ProcurementPipeline"
## dateparse = "ActualGoLiveDate"
pw = raw_input("Enter SQL Server database password: "******"vw_ProcurementPipeline",["ActualGoLiveDate","PlannedGoLiveDate"], pw)
Planned = Lulu[['PlannedGoLiveDayOfWeek','PlannedGoLiveMonth','PlannedGoLiveYear','PlannedGoLiveDate','BuyerPlanName','BuyerPlanStatus','EmployeeFirstName','PlannedUnitCostExclTax','PlannedTotalQuantity','PlannedTotalCostExclTax','SimpleSKU','SimpleName','ConfigName','ConfigSKU','ProcurementStatus','ProcurementProductCategoryL3','ActualGoLiveDate','Supplier','Designer','EANNumber','BarCode']]
#Merge EAN, BarCode information with SKU
Planned.loc[Planned.EANNumber == "",'EANNumber'] = None
SKU = Planned['EANNumber'].combine_first(Planned['SimpleSKU'])
Planned.loc[:,'SKU'] = Planned['BarCode'].combine_first(SKU)
Planned.drop_duplicates(subset = ['SKU','PlannedGoLiveMonth'], inplace = True, take_last = True)
if lastmonth == 11 | 12:
Planned = Planned[((Planned.PlannedGoLiveMonth >= lastmonth) & (Planned.PlannedGoLiveYear == today.year)) | ((Planned.PlannedGoLiveMonth <= nextmonth) & (Planned.PlannedGoLiveYear == today.year))]
else:
Planned = Planned[((Planned.PlannedGoLiveMonth >= lastmonth) & (Planned.PlannedGoLiveYear == today.year)) & ((Planned.PlannedGoLiveMonth <= nextmonth) & (Planned.PlannedGoLiveYear == today.year))]
Planned.rename(columns={'PlannedGoLiveDayOfWeek':'GLDay','PlannedGoLiveMonth':'GLMonth','PlannedGoLiveYear':'GLYear','PlannedGoLiveDate':'GLDate', 'EmployeeFirstName':'Buyer','ProcurementProductCategoryL3':'Category', 'PlannedUnitCostExclTax':'UnitCost','PlannedTotalQuantity':'TotalUnits','PlannedTotalCostExclTax':'TotalCost'}, inplace=True)
Planned = Planned[Planned['TotalCost'] > 0]
Planned.loc[Planned.ProcurementStatus == 'Deleted', ['TotalUnits','TotalCost']] = ""
#Import Dynaman IBOI1003 Inbound Order Received Messages
## table = "vw_WarehouseInboundItemsReceived"
## dateparse = "Timestamp"
IBOI1003 = MyFunx.sql_import("vw_WarehouseInboundItemsReceived","Timestamp",pw)
IBOI1003 = IBOI1003[['MessageReference','ItemCode','QuantityReceived','Timestamp']]
TaknIn = IBOI1003.groupby(['MessageReference','ItemCode']).agg({'QuantityReceived':np.sum, 'Timestamp':np.max})
TaknIn.reset_index(inplace=True)
TaknIn.columns = ['POs','SKU','OTDLastReceived','Qty Received']
TaknIn['POs'] = TaknIn['POs'].apply(lambda x: x if len(x) < 7 else 0)
TaknIn = TaknIn[TaknIn['POs'] != 0]
#Import Dynaman ITMI1002
## table = "vw_WarehouseStockAvailability"
## dateparse = "Timestamp"
ITMI1002 = MyFunx.sql_import("vw_WarehouseStockAvailability","Timestamp",pw)
ITMI1002 = ITMI1002[['ITEM_CODE','QTY']]
PutAway = pd.pivot_table(ITMI1002, values = ['QTY'], index = ['ITEM_CODE'], aggfunc=np.sum)
PutAway.reset_index(inplace=True)
PutAway.columns = ['SKU','Qty PutAway']
Merge = pd.merge(Planned, BP, on = 'SKU', how = 'left', sort = False)
Merge1 = pd.merge(Merge, Bookd, on = 'POs', how = 'left', sort = False)
Merge2 = pd.merge(Merge1, Receivd, on = 'POs', how = 'left', sort = False)
Merge3 = pd.merge(Merge2, QCed, on = ['SKU','POs'], how = 'left', sort = False)
Merge4 = pd.merge(Merge3, Damagd, on = 'SKU', how = 'left', sort = False)
Merge5 = pd.merge(Merge4, TaknIn, on = ['SKU','POs'], how = 'left', sort = False)
Visibility = pd.merge(Merge5, PutAway, on = 'SKU', how = 'left')
Visibility.drop_duplicates(inplace = True)
#remove duplicates that are in Draft PO status,assuming that these POs are outdated
Visibility.loc[Visibility['Ref'].str.contains("sample|Sample|SAMPLE|samples|Samples|OS|Os|OVERSUPPLY|fraud")==True,['TotalUnits','TotalCost','Qty PutAway']] = ""
Visibility['Oversupply'] = ""
Visibility.loc[Visibility['Ref'].str.contains("OS|Os|OVERSUPPLY")==True, 'Oversupply'] = Visibility['BP Qty']
V0 = Visibility.loc[Visibility.TotalUnits == "",]
Vn0 = Visibility.loc[Visibility.TotalUnits != "",]
Va = Vn0[Vn0.duplicated(subset = ['SKU'], take_last = False)==False]
Vb = Vn0[Vn0.duplicated(subset = ['SKU'], take_last = False)==True]
Vb.loc[:, ['TotalUnits','TotalCost','Qty PutAway']] = ""
Vc = Vb[Vb['Status']!='Draft PO']
Visibility = Va.append([Vc,V0], ignore_index = True)
Visibility.replace("", np.nan, inplace = True)
return Visibility