def fixCustomerId(self):
if len(self.__customers) > 0:
max = 0
for customer in self.__customers:
if max <= customer.getId():
max = customer.getId() + 1
Customer.setCustomerId(max)
def btnAddActionPerformed(self):
customerId = self.__txtCustomerId.get()
itemId = self.__txtItemId.get()
number = self.__txtNumber.get()
employeeId = self.__txtEmployeeId.get()
if len(customerId) > 0 and len(itemId) > 0 and len(number) > 0 and len(
employeeId) > 0:
try:
customer = Customer(customerId, "", "", "")
item = Item(itemId, "", "", "")
employee = Employee(employeeId, "", "", "", "")
if self.__customers.__contains__(customer) \
and self.__items.__contains__(item) and\
self.__employees.__contains__(employee):
cIndex = self.__customers.index(customer)
iIndex = self.__items.index(item)
eIndex = self.__employees.index(employee)
transaction = Transaction("", self.__customers[cIndex],
self.__items[iIndex], number,
self.__employees[eIndex])
transaction.fixId()
self.__parent.addTransactionCallBack(transaction)
messagebox.showinfo("Success",
"A new transaction has been added")
self.showDefaultText()
else:
messagebox.showerror("Error",
"This information does not exist!")
except Exception:
messagebox.showerror("Error", "Invalid information format!")
else:
messagebox.showerror("Error", "Input fields cannot be left blank!")
def readCustomerTable(self):
self.__customers = []
sql = "SELECT * FROM customers"
self.__cursor.execute(sql)
records = self.__cursor.fetchall()
for row in records:
customer = Customer(str(row[0]), row[1], row[2], row[3])
self.__customers.append(customer)
return self.__customers
def searchCustomerByPhoneNumber(self, phoneNumber):
self.__customers = []
sql = "SELECT * FROM customers WHERE phoneNumber LIKE %s"
val = ("%" + phoneNumber + "%", )
self.__cursor.execute(sql, val)
records = self.__cursor.fetchall()
for row in records:
customer = Customer(str(row[0]), row[1], row[2], row[3])
self.__customers.append(customer)
return self.__customers
def searchCustomerByAddress(self, address):
self.__customers = []
sql = "SELECT * FROM customers WHERE address LIKE %s"
val = ("%" + address + "%", )
self.__cursor.execute(sql, val)
records = self.__cursor.fetchall()
for row in records:
customer = Customer(str(row[0]), row[1], row[2], row[3])
self.__customers.append(customer)
return self.__customers
def searchCustomerByFullName(self, fullName):
self.__customers = []
sql = "SELECT * FROM customers WHERE fullName LIKE %s"
val = ("%" + fullName + "%", )
self.__cursor.execute(sql, val)
records = self.__cursor.fetchall()
for row in records:
customer = Customer(str(row[0]), row[1], row[2], row[3])
self.__customers.append(customer)
return self.__customers
def searchCustomerById(self, customerId):
self.__customers = []
sql = "SELECT * FROM customers WHERE id = %s"
val = (customerId, )
self.__cursor.execute(sql, val)
records = self.__cursor.fetchall()
for row in records:
customer = Customer(str(row[0]), row[1], row[2], row[3])
self.__customers.append(customer)
return self.__customers
def upsert_order():
incoming = request.get_json()
print(incoming)
order = Orders()
customer = Customer()
orderid = incoming.get("OrderID", -1)
customerid = incoming.get("CustomerID", None)
if orderid != -1:
order = model.db.query(Orders).get(incoming["OrderID"])
model.db.query(OrderDetails).filter(OrderDetails.OrderID == incoming["OrderID"]).delete()
if customerid is not None:
customer = model.db.query(Customer).get(customerid)
if customer is None:
customer = Customer()
order.ShipAddress = incoming["ShipAddress"]
order.ShipName = incoming["ShipName"]
order.RequiredDate = dateutil.parser.parse(incoming["RequiredDate"])
order.CustomerID = incoming["CustomerID"]
order.ShipCity = incoming["ShipCity"]
order.ShipRegion = incoming["ShipRegion"]
order.ShipPostalCode = incoming["ShipPostalCode"]
order.OrdPaid = incoming["OrdPaid"]
order.CustomerID = customerid
model.db.add(order)
if incoming["customer"] is not None:
inbound = incoming["customer"]
customer.CustomerID = customerid
customer.CustomerFirstName = inbound["CustomerFirstName"]
customer.CompanyName = inbound["CompanyName"]
model.db.add(customer)
model.db.commit()
for tail in incoming["details"]:
print(tail["OrderID"])
if tail["OrderID"] is None:
tail["OrderID"] = order.OrderID
detail = OrderDetails()
detail.serialize_in(tail)
model.db.add(detail)
model.db.commit()
return json.dumps(incoming)
def btnEditActionPerformed(self):
fullName = self.__txtFullName.get()
address = self.__txtAddress.get()
phoneNumber = self.__txtPhoneNumber.get()
if len(fullName) > 0 and len(address) > 0 and len(phoneNumber) > 0:
try:
customer = Customer(str(self.__customer.getId()), fullName,
address, phoneNumber)
self.__parent.editCustomerCallBack(customer)
self.__editCustomerWindow.destroy()
messagebox.showinfo("Success",
"Customer information has been edited")
except Exception:
messagebox.showerror("Error", "Invalid information format!")
else:
messagebox.showerror("Error", "Input fields cannot be left blank!")
def load_customers():
with open("data/customers") as f:
content = f.readlines()
customers = []
for line in content:
customer_data = line.strip().split('|')
customer = Customer(
customer_data[0], # username
customer_data[1], # password
customer_data[2], # first name
customer_data[3], # last name
customer_data[4], # phone
customer_data[5], # email
customer_data[6], # passport number
customer_data[7], # citizenship
customer_data[8]) # gender
customers.append(customer)
return customers
def readTransactionTable(self):
self.__transactions = []
sql = "SELECT * FROM transactions"
self.__cursor.execute(sql)
records = self.__cursor.fetchall()
for row in records:
id = str(row[0])
customerId = str(row[1])
itemId = str(row[3])
number = str(row[5])
employeeId = str(row[7])
cIndex = self.__customers.index(Customer(customerId, "", "", ""))
iIndex = self.__items.index(Item(itemId, "", "", ""))
eIndex = self.__employees.\
index(Employee(employeeId, "", "", "", ""))
transaction = Transaction(id, self.__customers[cIndex],
self.__items[iIndex], number,
self.__employees[eIndex])
self.__transactions.append(transaction)
return self.__transactions
def btnAddActionPerformed(self):
fullName = self.__txtFullName.get()
address = self.__txtAddress.get()
phoneNumber = self.__txtPhoneNumber.get()
if len(fullName) > 0 and len(address) > 0 and len(phoneNumber) > 0:
try:
customer = Person(fullName, address, phoneNumber)
if self.__customers.__contains__(customer):
messagebox.showerror(
"Error", "Customer information already exists!")
else:
customer = Customer("", fullName, address, phoneNumber)
customer.fixId()
self.__parent.addCustomerCallBack(customer)
messagebox.showinfo("Success",
"A new customer has been added")
self.showDefaultText()
except Exception:
messagebox.showerror("Error", "Invalid information format!")
else:
messagebox.showerror("Error", "Input fields cannot be left blank!")
def get_data(file_name):
from model.Customer import Customer
import csv
from itertools import groupby
with open(("static/img/uploads/" + file_name), 'r', encoding='utf-8') as f:
reader = csv.reader(f, dialect='excel', delimiter='\t')
for row in reader:
if len(row) == 30:
row.extend([""])
row.extend([""])
if len(row) == 31:
row.extend([""])
if len(row) < 32 or len(row) > 32:
print(len(row))
print(row + "was not processed")
customer_list.append(
Customer(row[0], row[1], row[2], row[3], row[4], row[5],
row[6], row[7], row[8], row[9], row[10], row[11],
row[12], row[13], row[14], row[15], row[16], row[17],
row[18], row[19], row[20], row[21], row[22], row[23],
row[24], row[25], row[26], row[27], row[28], row[29],
row[30], row[31]))
# for name in customer_list:
# print(name.ship_city)
print(len(customer_list))
states = [
'AK', 'AL', 'AR', 'AZ', 'CA', 'CO', 'CT', 'DC', 'DE', 'FL', 'GA', 'HI',
'IA', 'ID', 'IL', 'IN', 'KS', 'KY', 'LA', 'MA', 'MD', 'ME', 'MI', 'MN',
'MO', 'MS', 'MT', 'NC', 'ND', 'NE', 'NH', 'NJ', 'NM', 'NV', 'NY', 'OH',
'OK', 'OR', 'PA', 'RI', 'SC', 'SD', 'TN', 'TX', 'UT', 'VA', 'VT', 'WA',
'WI', 'WV', 'WY'
]
us_state_abbrev = {
'ALABAMA': 'AL',
'ALASKA': 'AK',
'ARIZONA': 'AZ',
'ARKANSAS': 'AR',
'CALIFORNIA': 'CA',
'CA.': 'CA',
'COLORADO': 'CO',
'CONNECTICUT': 'CT',
'DELAWARE': 'DE',
'FLORIDA': 'FL',
'GEORGIA': 'GA',
'HAWAII': 'HI',
'IDAHO': 'ID',
'ILLINOIS': 'IL',
'INDIANA': 'IN',
'IOWA': 'IA',
'KANSAS': 'KS',
'KENTUCKY': 'KY',
'LOUISIANA': 'LA',
'MAINE': 'ME',
'MARYLAND': 'MD',
'MASSACHUSETTS': 'MA',
'MICHIGAN': 'MI',
'MINNESOTA': 'MN',
'MISSISSIPPI': 'MS',
'MISSOURI': 'MO',
'MONTANA': 'MT',
'NEBRASKA': 'NE',
'NEVADA': 'NV',
'NEW HAMPSHIRE': 'NH',
'NEW JERSEY': 'NJ',
'NEW MEXICO': 'NM',
'NEW YORK': 'NY',
'NORTH CAROLINA': 'NC',
'NORTH DAKOTA': 'ND',
'OHIO': 'OH',
'OKLAHOMA': 'OK',
'OREGON': 'OR',
'PENNSYLVANIA': 'PA',
'RHODE ISLAND': 'RI',
'SOUTH CAROLINA': 'SC',
'SOUTH DAKOTA': 'SD',
'TENNESSEE': 'TN',
'TEXAS': 'TX',
'UTAH': 'UT',
'VERMONT': 'VT',
'VIRGINIA': 'VA',
'WASHINGTON': 'WA',
'WEST VIRGINIA': 'WV',
'WISCONSIN': 'WI',
'WYOMING': 'WY',
}
all_states = []
for cus in customer_list:
if cus.item_price != "":
if not (cus.ship_state.upper() in states):
stater = cus.ship_state.upper()
name = (us_state_abbrev.get(stater, "skipped"))
if name == "skipped":
print(stater + " was skipped")
else:
all_states.append(name)
else:
all_states.append(cus.ship_state.upper())
all_states.sort()
print(all_states)
unique_states = all_states
unique_states = list(set(unique_states))
unique_states.sort()
print(unique_states)
state_count = [len(list(group)) for key, group in groupby(all_states)]
print(state_count)
moneylist = [0] * len(unique_states)
for cus in customer_list:
if cus.item_price != "":
for index in range(len(unique_states)):
if cus.ship_state.upper(
) == unique_states[index] or us_state_abbrev.get(
cus.ship_state.upper(),
"invalid") == unique_states[index]:
try:
moneylist[index] += float(cus.item_price)
except:
print("yikes")
with open('State_List.csv', 'w') as csvfile:
fieldnames = ['State', 'Count', 'Money']
writer = csv.DictWriter(csvfile, fieldnames=fieldnames)
writer.writeheader()
for i in range(len(unique_states)):
moneylist[i] = round(moneylist[i], 2)
writer.writerow({
'State': unique_states[i],
'Count': state_count[i],
'Money': moneylist[i]
})
useful_customer.append(
[unique_states[i], state_count[i], moneylist[i]])
return (unique_states, moneylist, state_count)
def buildESIndex(detailType, detail, s3confPath, s3filePath, spark):
sqlContext = SQLContext(spark)
# TODO.confinsteadof.json val
confJson = S3FilesDsl.readConfigJson(s3confPath)
rodTicketANTags = AdminNumberTags.antagsColumns(
S3FilesDsl.readFile(confJson.tags_admin_path, spark), spark)
parquetPath = confJson.fast_parquet_path
rodPostgreAdminNumber = sqlContext.read.parquet(parquetPath)
logging.info("FAST joins..")
networkFast = sqlContext.read.parquet(confJson.fast_network_parquet_path)
logging.info("common joins..")
# TODO: añadir import de utils.constantes
# TODO: comprobar parametros que se pasan a los metodos de Utils
common3 = joinMasterEntities(detail, spark)
common2 = common3.join(rodPostgreAdminNumber, ["admin_number"], "left")
common1 = Utils.fillEmptyFastColumns(common2)
common = common1.join(networkFast, ["admin_number"], "left"). \
withColumn("networkinfo", Utils.networkNestedObject("fast_customer", "fast_end_customer",
"router_interface_vendor_type_set")). \
drop("router_interface_vendor_type_set"). \
join(rodTicketANTags, ["admin_number"], "left"). \
withColumn("open", F.when(common1.status_desc.isin(Constants.openStatus), Constants.OPEN_YES).
otherwise(F.when(common1.status_desc.isin(Constants.notOpenStatus), Constants.OPEN_NO).
otherwise(Constants.EMPTY_STRING))). \
withColumn("ticket_max_value_partition", Utils.getIndexPartition("ticket_id")). \
withColumn("admin_number_escaped", Utils.urlWhitespaces("admin_number")). \
withColumn("fast_max_resolution_time", Utils.validateNumeric("fast_max_resolution_time")). \
withColumn("file", F.lit(s3filePath)). \
fillna(Constants.EMPTY_STRING, ["assigned_agent"])
if detailType == "helpdesk":
rodTicketReportedSource = getReportedSource(spark)
operationalManager = getOperationalManager(confJson.operational_path, spark)
opTags = OperatingTags.operatingTagsColumns(S3FilesDsl.readFile(confJson.tags_operating_path, spark))
customer = Customer.customerColumns(S3FilesDsl.readFile(confJson.customer_path, spark), spark)
endCustomer = EndCustomer.endCustomerColumns(S3FilesDsl.readFile(confJson.end_customer_path, spark), spark)
index1 = common \
.join(rodTicketReportedSource, ["reported_source_id"], "left") \
.drop("reported_source_id") \
.join(operationalManager, ["operating_company_name", "operating_le"], "left") \
.na.fill(Constants.EMPTY_STRING, ["operational_manager"]) \
.join(opTags, ["operating_company_name", "operating_le"], "left") \
.withColumn("tags", Utils.mergeArrays("tags", "operating_tags")) \
.drop("operating_tags") \
.join(customer, ["operating_company_name"], "left") \
.fillna(Constants.EMPTY_STRING, ["customer_correct"]) \
.join(endCustomer, ["operating_le"], "left") \
.fillna(Constants.EMPTY_STRING, ["end_customer_correct"]) \
.withColumn("end_customer_correct",
Utils.emptyEndCustomerCorrect("customer_correct", "end_customer_correct")) \
.withColumn("ci_country", Utils.kibanaCountry("ci_country")) \
.withColumn("end_user_country", Utils.kibanaCountry("end_user_country")) \
.withColumn("smc_cluster", Utils.smcClusterFromGroup("assigned_support_group")) \
.withColumn("ci_name_escaped", Utils.urlWhitespaces("ci_name")) \
.withColumn("product_categorization_all_tiers",
Utils.concat3Columns("product_categorization_tier_1", "product_categorization_tier_2",
"product_categorization_tier_3")) \
.withColumn("closure_categorization_all_tiers",
Utils.concat3Columns("closure_categorization_tier_1", "closure_categorization_tier_2",
"closure_categorization_tier_3")) \
.withColumn("operational_categorization_all_tiers",
Utils.concat3Columns("operational_categorization_tier_1",
"operational_categorization_tier_2",
"operational_categorization_tier_3")) \
.withColumnRenamed("reported_source_desc", "reported_source_id")
index = FastDsl.fastCircuitFields(index1, confJson, spark)
elif detailType == "problems":
index1 = common \
.withColumn("ci_country", Utils.kibanaCountry("ci_country")) \
.withColumn("ci_name_escaped", Utils.urlWhitespaces("ci_name"))
index = FastDsl.fastCircuitFields(index1, confJson, spark)
elif detailType == "changes":
rodTicketReportedSource = getReportedSource(spark)
index = common \
.join(rodTicketReportedSource, ["reported_source_id"], "left") \
.drop("reported_source_id") \
.withColumn("ci_country", Utils.kibanaCountry("ci_country")) \
.withColumn("company_country", Utils.kibanaCountry("company_country")) \
.withColumnRenamed("reported_source_desc", "reported_source_id")
# EL USUARIO SOLICITA QUE LAS DESCRIPCIONES DE LOS MAESTROS SE RENOMBREN COMO _id
indexRenamed = index \
.withColumnRenamed("status_desc", "status_id") \
.withColumnRenamed("substatus_desc", "substatus_id") \
.withColumnRenamed("urgency_desc", "urgency_id") \
.withColumnRenamed("priority_desc", "priority_id") \
.withColumnRenamed("impact_desc", "impact_id")
return indexRenamed