def edit_gift_bag_info(cls, data):
now = get_now()
try:
cursor = cls.conn_ss.cursor()
# 更新gifts表中的数据
update_sql = "update gifts set name = '%s', limit_goods_num = '%d', update_by = '%s', update_time = '%s', image_url = '%s' where id = '%s'" % (
data['name'], data['limitGoodsNum'], data['staffNo'], now,
data['image_url'], data['id'])
cursor.execute(update_sql)
# 根据gifts.id删除gifts_goods_relation表中的关系
update_relation_sql = "update gift_goods_relation set status = 2, update_by = '%s', update_time = '%s' where gift_id = '%s' and status = 1" % (
data['staffNo'], now, data['id'])
cursor.execute(update_relation_sql)
# 根据goods插入新的数据
goods_list = data['goods']
gift_id = data['id']
create_by = data['staffNo']
cls.insert_records_into_gift_goods_relation(
cursor, goods_list, gift_id, create_by)
cls.conn_ss.commit()
cursor.close()
return True
except Exception as e:
print(e)
cls.conn_ss.rollback()
return False
def send_message(cls, staff_no):
access_token = cls.get_access_token()
user_id = cls.get_user_id_by_staff_no(staff_no)
print(user_id)
now = get_now()
url = cls.url + '/topapi/message/corpconversation/asyncsend_v2?access_token=' + access_token
HEADERS = {'Content-Type': 'application/json'}
target_url = urllib.parse.quote('http://222.186.81.36:8081/gift/#/orderList')
print(target_url)
data = {
"agent_id": cls.agent_id,
"userid_list": user_id,
"msg": {
"msgtype": "oa",
"oa": {
"message_url": "eapp://pages/index/index",
"pc_message_url": "dingtalk://dingtalkclient/action/openapp?corpid="+cls.CorpId+"&container_type=work_platform&app_id=0_"+cls.agent_id+"&redirect_type=jump&redirect_url="+target_url,
"head": {
"bgcolor": "FFBBBBBB",
"text": "福利管理系统"
},
"body": {
"title": "您有一个生日礼包可以前往领取啦",
"content": now + ",管理员已通过您提交的领取生日礼包申请，请于周五下午3-5点前往集团大楼4楼409联系福利专员领取",
}
}
}
}
res = requests.post(url=url, headers=HEADERS, json=data)
print(res.text)
return res.text
def update_stock_detail(cls, change_type, num, desc, stock_id, create_by,
price, goods_id):
now = get_now()
try:
# 判断修改后入库数是否大于等于出库数，若入库数小于出库数，则不允许修改
res = cls.get_goods_stock_in_and_out(goods_id, stock_id)
total_in = res['total_in']
total_out = res['total_out']
if change_type == 1:
total_in = int(total_in) + int(num)
if change_type == 2:
total_out = int(total_out) + int(num)
if total_in < total_out:
return 1
cursor = cls.conn_ss.cursor()
update_sql = "update goods_stock_change_detail set change_type = '%s', num = '%s', change_des = '%s', update_by = '%s', update_time = '%s', price = '%s' where id = '%s'" % \
(change_type, num, desc, create_by, now, price, stock_id)
cursor.execute(update_sql)
cls.conn_ss.commit()
cursor.close()
if change_type == 1:
goods_price = cls.get_goods_avg_price(goods_id)
cls.update_goods_price(goods_price, goods_id, create_by)
cls.correct_stock_out_record(goods_id, create_by)
return 2
except Exception as e:
print(e)
cls.conn_ss.rollback()
return 3
def correct_stock_out_record(cls, goods_id, create_by):
# 将该商品的出库记录保存
stock_out_list = []
sql = "SELECT *, convert(varchar(19),gscd.create_time,120) FROM goods_stock_change_detail gscd WHERE gscd.status = 1 and gscd.change_type = 2 AND gscd.goods_id = '%s'" % (
goods_id)
df_sql = pd.read_sql(sql, con=cls.conn_ss)
for recordIndex, record in df_sql.iterrows():
record_item = {}
record_item['goods_id'] = record[1]
record_item['change_type'] = record[2]
record_item['num'] = record[3]
record_item['change_des'] = record[4]
record_item['create_time'] = str(record[11])
record_item['create_by'] = record[7]
record_item['update_time'] = str(record[8])
record_item['update_by'] = record[9]
stock_out_list.append(record_item)
# 将该商品的出库记录全部删除
now = get_now()
try:
cursor = cls.conn_ss.cursor()
update_sql = "update goods_stock_change_detail set status = 2, update_by = '%s', update_time = '%s' where status = 1 and change_type = 2 and goods_id = '%s'" % \
(create_by, now, goods_id)
cursor.execute(update_sql)
cls.conn_ss.commit()
cursor.close()
# 根据保存的出库记录，逐条判断商品的出库价格，并新增出库记录
for recordIndex, record in enumerate(stock_out_list):
cls.stock_out_with_price(record)
except Exception as e:
print(e)
cls.conn_ss.rollback()
return False
def delete_stock_detail(cls, stock_id, create_by):
now = get_now()
now = f'\'{now}\''
try:
sql = "select gscd.goods_id from goods_stock_change_detail gscd where gscd.id = '%s'" % (
stock_id)
df_sql = pd.read_sql(sql, con=cls.conn_ss)
goods_id = df_sql['goods_id'][0]
# 判断删除后入库是否大于等于出库
res = cls.get_goods_stock_in_and_out(goods_id, stock_id)
total_in = res['total_in']
total_out = res['total_out']
if int(total_in) < int(total_out):
return 1
cursor = cls.conn_ss.cursor()
update_sql = f'update goods_stock_change_detail set status = 2, update_by = {create_by}, update_time = {now} where id = {stock_id}'
cursor.execute(update_sql)
cls.conn_ss.commit()
cursor.close()
goods_price = cls.get_goods_avg_price(goods_id)
cls.update_goods_price(goods_price, goods_id, create_by)
cls.correct_stock_out_record(goods_id, create_by)
return 2
except Exception as e:
print(e)
cls.conn_ss.rollback()
return 3
def edit_goods_status(cls, goods_id, status, staff_no):
now = get_now()
now = f'\'{now}\''
try:
cursor = cls.conn_ss.cursor()
# 商品下架之前先判断一下该商品是否属于某个礼包，若属于，不允许下架，提示用户先取消和礼包的关联关系，而后，再进行下架
if status != 1:
belong_gift = cls.belong_gift(goods_id)
if belong_gift:
return 1
else:
update_sql = f'update goods set status = {status}, update_by = {staff_no}, update_time = {now} where id = {goods_id}'
cursor.execute(update_sql)
cls.conn_ss.commit()
cursor.close()
return 2
else:
update_sql = f'update goods set status = {status}, update_by = {staff_no}, update_time = {now} where id = {goods_id}'
cursor.execute(update_sql)
cls.conn_ss.commit()
cursor.close()
return 2
except Exception as e:
print(e)
cls.conn_ss.rollback()
return 3
def update_goods_price(cls, price, goods_id, staff_no):
now = get_now()
try:
cursor = cls.conn_ss.cursor()
update_sql = "update goods set price = '%s', update_by = '%s', update_time = '%s' where id = '%d'" % (
price, staff_no, now, goods_id)
cursor.execute(update_sql)
cls.conn_ss.commit()
cursor.close()
return True
except Exception as e:
print(e)
cls.conn_ss.rollback()
return False
def edit_goods_info(cls, goods_id, name, unit, price, image_url,
update_by):
now = get_now()
try:
cursor = cls.conn_ss.cursor()
update_sql = "update goods set name = '%s', unit = '%s', price = '%s', image_url = '%s', update_by = '%s', update_time = '%s' where id = '%d'" % (
name, unit, price, image_url, update_by, now, goods_id)
cursor.execute(update_sql)
cls.conn_ss.commit()
cursor.close()
return True
except Exception as e:
print(e)
cls.conn_ss.rollback()
return False
def edit_gift_bag_status(cls, gift_bag_id, status, staff_no):
now = get_now()
now = f'\'{now}\''
try:
cursor = cls.conn_ss.cursor()
update_sql = f'update gifts set status = {status}, update_by = {staff_no}, update_time = {now} where id = {gift_bag_id}'
cursor.execute(update_sql)
cls.conn_ss.commit()
cursor.close()
return True
except Exception as e:
cls.conn_ss.rollback()
err_msg = e
return False, err_msg
def edit_order_status(cls, order_id, status, staff_no, staff_name):
try:
now = get_now()
cursor = cls.conn_ss.cursor()
update_sql = ''
if status == 3:
update_sql = "update [order] set status = '%d', confirm_by = '%s', confirm_by_name = '%s', confirm_time = '%s' where id = '%s'" % (status, staff_no, staff_name, now, order_id)
if status == 4 or status == 2:
update_sql = "update [order] set status = '%d', finished_by = '%s', finish_by_name = '%s', finish_time = '%s' where id = '%s'" % (status, staff_no, staff_name, now, order_id)
# 如果是取消订单，要将goods_stock_change_detial表中的相应记录状态置为2
if status == 2:
update_stock_sql = "update [goods_stock_change_detail] set status = 2, update_by = '%s', update_time = '%s' where order_id = '%s'" % (staff_no, now, order_id)
cursor.execute(update_stock_sql)
cursor.execute(update_sql)
cls.conn_ss.commit()
cursor.close()
return True
except Exception as e:
print(e)
cls.conn_ss.rollback()
return False
def batch_import_goods(cls, file_data_frame, staff_no):
now = get_now()
try:
cursor = cls.conn_ss.cursor()
for index, line in file_data_frame.iterrows():
goods_id = line[0]
goods_name = line[1]
unit = line[2]
num = line[3]
price = line[4]
# 根据id判断是否存在，若存在，更新
flag = cls.is_exist_goods(goods_id)
if flag:
update_sql = "update goods set name = '%s', unit = '%s', price = '%s', image_url = '%s', update_by = '%s', update_time where id = '%d'" % (
goods_name, unit, price, '', staff_no, now, goods_id)
cursor.execute(update_sql)
info = {
'goods_id': goods_id,
'change_type': 1,
'num': num,
'change_des': '导入',
'create_by': staff_no,
'price': price
}
base_sql = "insert into dbo.goods_stock_change_detail ({}) values ({})"
sql_item = []
sql_values = []
for key in info:
sql_item.append(key)
if isinstance(info[key], str): # 如果是字符串 加上引号
sql_values.append("\'" + info[key] + "\'")
else:
sql_values.append(info[key])
sql = base_sql.format(','.join(sql_item),
','.join(list(map(str, sql_values))))
print('库存插入')
print(sql)
cursor.execute(sql)
else:
# 若不存在， 新增
goods_info = {
'name': goods_name,
'unit': unit,
'image_url': '',
'price': price
}
# 新增商品
base_sql_goods = "insert into dbo.goods ({}) values ({})"
sql_item_goods = ['create_by']
sql_values_goods = [staff_no]
for key in goods_info:
sql_item_goods.append(key)
if isinstance(goods_info[key], str): # 如果是字符串 加上引号
sql_values_goods.append("\'" + goods_info[key] +
"\'")
else:
sql_values_goods.append(goods_info[key])
sql_goods = base_sql_goods.format(
','.join(sql_item_goods),
','.join(list(map(str, sql_values_goods))))
cursor.execute(sql_goods)
# 新增商品之后获取该商品id
goods_id = cursor.lastrowid
# 新增库存变化明细记录
base_sql_stock = "insert into dbo.goods_stock_change_detail ({}) values ({})"
sql_item_stock = []
sql_values_stock = []
stock_info = {
'price': price,
'goods_id': goods_id,
'num': num,
'change_des': '导入',
'create_by': staff_no
}
for key in stock_info:
sql_item_stock.append(key)
if isinstance(stock_info[key], str): # 如果是字符串 加上引号
sql_values_stock.append("\'" + stock_info[key] +
"\'")
else:
sql_values_stock.append(stock_info[key])
sql_stock = base_sql_stock.format(
','.join(sql_item_stock),
','.join(list(map(str, sql_values_stock))))
print('新增库存插入')
print(sql_stock)
cursor.execute(sql_stock)
cls.conn_ss.commit()
cursor.close()
return True
except Exception as e:
print(e)
cls.conn_ss.rollback()
return False