""" #NEW Damages script to be used until efinity supplier communications are automated. #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() #Import Efinity bad stock pw = 'Spr33Pops101' PODetail = MyFunx.sql_import("vw_PurchaseOrderItems","PurchaseOrderDate", pw) PODetail = PODetail[['PurchaseOrderNumber','SimpleSKU','SimpleName','SupplierName','Email','AlternateEmail']] Stock = MyFunx.sql_import("vw_Inventory","Date", pw) QCed = Stock[['Date','BrandName','SimpleSKU','QualityControlBadQuantity']] QCed.rename(columns={'Date': 'Date QCed','QualityControlBadQuantity':'QC_Damaged'}, inplace=True) #Reading from Oversupply google doc c = gdocs.authenticate_gdocs() sheet = c.open('Oversupply') ws = sheet.worksheet('Sheet1') if ws.cell(2,1).value == "": print "No oversupply scanned" OS = DataFrame(columns = ['SKU','Oversupply']) l = 0 else:
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
from pandas import ExcelWriter import MyFunx team = raw_input('What is your Team Letter (A, B, C, S (S = SAMPLES))? ') shift = datetime.today().strftime('%Y-%m-%d %H:%M') #Import Brightpearl Detail Report columns = ["Order ID", "Ref", "Status", "Contact", "SKU", "Name", "Quantity"] BP = pd.read_csv('BPdetail.csv', header = 0, usecols = columns) #Lulu1 = pd.ExcelFile('Z:\\Python Scripts\\00_UPDATE\\Lulu1.xlsx') #Planned = Lulu1.parse('Sheet4', skiprows = 0, index = None, parse_cols = (36,46)) #Connect to MSSQL to get Lulu data pw = "Spr33Pops101" Lulu = MyFunx.sql_import("vw_ProcurementPipeline","ActualGoLiveDate",pw) Planned = Lulu[['SimpleSKU','ProcurementProductCategoryL3']] Planned.drop_duplicates(inplace = True, take_last = True) #Reading from StockCount google docs c = gspread.Client(auth=('*****@*****.**', 'spreeapp')) c.login() #============================================================================== # Details for SAMPLES processing #============================================================================== if team == 'S': sht = c.open('SamplesCount') worksheet = sht.worksheet('Team' + team) #Parameters for total samples count doc
#This script keeps an audit trail of samples sent from WH to Sample Room, Studio and back to WH import pandas as pd from pandas import DataFrame from datetime import date, datetime, timedelta from pandas import ExcelWriter import MyFunx, gdocs shift = datetime.today().strftime('%Y-%m-%d %H:%M') today = date.today() #============================================================================== # Read Samples Plan master data #============================================================================== pw = 'Spr33Pops101' Lulu = MyFunx.sql_import("vw_ProcurementPipeline","ActualGoLiveDate", pw) Planned = Lulu[['PlannedGoLiveDayOfWeek','PlannedGoLiveMonth','PlannedGoLiveYear','BuyerPlanName','BuyerPlanStatus','EmployeeFirstName','PlannedUnitCostExclTax','PlannedTotalQuantity','PlannedTotalCostExclTax','SimpleSKU','SimpleName','ConfigName','ConfigSKU','ProcurementStatus','ProcurementProductCategoryL3','ActualGoLiveDate','Supplier','Designer','EANNumber','BarCode']] Planned.rename(columns = {'PlannedGoLiveDayOfWeek':'GLDay','PlannedGoLiveMonth':'GLMonth','PlannedGoLiveYear':'GLYear','EmployeeFirstName':'Buyer','ProcurementProductCategoryL3':'Category'}, inplace = True) Planned.drop_duplicates(subset = ['SimpleSKU','GLMonth'], inplace = True, take_last = True) Planned = Planned[Planned['PlannedTotalCostExclTax'] > 0] Stock = MyFunx.sql_import("vw_Inventory","Date", pw) QCed = Stock[['Date','SimpleSKU','QualityControlGoodQuantity']] QCed.rename(columns={'Date': 'Date QCed','QualityControlGoodQuantity':'QC_Count'}, inplace=True) SamplesPlan = pd.merge(Planned, QCed, on = 'SimpleSKU', how = 'left', sort = False) SamplesPlan.drop_duplicates(subset = ['ConfigSKU'],take_last = True, inplace = True) SamplesPlan = SamplesPlan[(SamplesPlan.GLMonth >= today.month - 2) & (SamplesPlan.GLMonth <= today.month + 2) & (SamplesPlan.GLYear == today.year)] #============================================================================== # Read data from Google docs