get child category from flat data

本文介绍了一种解决Magento中因使用不同资源模型而导致的Fatal error问题的方法。通过修改_getChildrenCategoriesBase方法中的代码,用addUrlRewriteToResult替换原有的joinUrlRewrite方法,可以有效避免错误的发生。
Fatal error: Call to undefined method Mage_Catalog_Model_Resource_Category_Flat_Collection::joinUrlRewrite() 

This happens because the category model (catalog/category) uses different resource models for different settings of Use Flat Catalog Category and you are always using

 

 

try changing joinUrlRewrite to addUrlRewriteToResult

 

    protected function _getChildrenCategoriesBase($category)
    {
    	$collection = $category->getCollection();
    
    	$collection->addAttributeToSelect('url_key')
    	->addAttributeToSelect('name')
    	->addAttributeToSelect('all_children')
    	->addAttributeToSelect('is_anchor')
    	->addAttributeToSelect('category_code')
    	->setOrder('position', Varien_Db_Select::SQL_ASC)
    	->addUrlRewriteToResult();
    
    	return $collection;
    }
    
    public function getChildrenCategories($category)
    {
    	 
    	$collection = $this->_getChildrenCategoriesBase($category);
    	$collection->addAttributeToFilter('is_active', 1)
    	->addIdFilter($category->getChildren())
    	->load();
    	return $collection;
    }

 

 

 

 

 

# -*- coding: utf-8 -*- import os import cx_Oracle as cx import pandas as pd import datetime as dt import schedule import time import requests from threading import Thread import dataframe_image as dfi import base64 import hashlib class Wechat: def __init__(self, secret): self.secret = secret def access_token(self): """获取企业微信access_token""" url = f'https://qyapi.weixin.qq.com/cgi-bin/gettoken?corpid=wwed5e7f3fd1a3a553&corpsecret={self.secret}' response = requests.get(url).json() return response['access_token'] def oracle_connect(sql): """Oracle数据库连接查询""" con = cx.connect("admin/xxb20140415@192.168.3.16:1521/zzjdata") cursor = con.cursor() cursor.execute(sql) column_s = [col[0] for col in cursor.description] data = cursor.fetchall() cursor.close() con.close() return data, column_s def generate_variety_sales_sql(): """生成地区各品种销售进度表SQL""" sql = """ with mdxs as (select a.subbh,b.lb,sum(case when nvl(b.bz,0)<>0 then a.sl*b.bz else a.jshj end) jshj from subfhd a left join c_ylgcjxsrw b on a.hh=b.hh where a.kdrq = TRUNC(SYSDATE) and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'¥' and nvl(a.bm,' ')<>'积分兑换' and b.hh is not null group by a.subbh,b.lb) select distinct c.c_dq 地市,a.c_mdfq1 地区,a.lb 类别,round(a.xsrw,0) 任务,nvl(b.jshj,0) "销量/额",case when nvl(a.xsrw,0)<>0 then round(nvl(b.jshj,0)*100/a.xsrw,4) else 0 end 进度 from c_ylrjxsrw a left join (select b.c_mdfq1,a.lb,sum(a.jshj) jshj from mdxs a left join gl_custom b on a.subbh=b.tjbh group by b.c_mdfq1,a.lb) b on a.c_mdfq1=b.c_mdfq1 and a.lb=b.lb left join (select distinct c_dq,c_mdfq1 from gl_custom where c_dq<>' ') c on a.c_mdfq1=c.c_mdfq1 """ return sql def transform_data_to_pivot(df): """将数据转换为透视表格式 - 修正版""" try: print(f"原始数据行数: {len(df)}") print(f"原始数据列: {df.columns.tolist()}") # 显示数据样本 print("\n原始数据样本:") print(df.head(10).to_string()) # 检查数据完整性 print(f"\n数据统计:") print(f"地市数量: {len(df['地市'].unique())}") print(f"地区数量: {len(df['地区'].unique())}") print(f"品类数量: {len(df['类别'].unique())}") print(f"总任务额: {df['任务'].sum():.0f}") print(f"总销售额: {df['销量/额'].sum():.0f}") # 固定品类顺序 fixed_categories = [ '智禾', '红曲', '黄精', '三鞭补酒', '达因D', '云南白药血膏剂', '云南白药小气', '杭州远大', '来益叶黄素', 'VC系列', '京润珍珠', '地喹氯铵含片', '扬子江' ] # 获取实际存在的品类 existing_categories = [cat for cat in fixed_categories if cat in df['类别'].unique()] other_categories = [cat for cat in sorted(df['类别'].unique()) if cat not in fixed_categories] all_categories = existing_categories + other_categories print(f"\n处理品类: {all_categories}") result_data = [] # === 第一部分:地区数据(直接从原始数据获取)=== print("\n=== 处理地区数据 ===") region_data = [] for region in sorted(df['地区'].unique()): region_df = df[df['地区'] == region] region_city = region_df['地市'].iloc[0] if len(region_df) > 0 else "未知" row = {'区域': region, '类型': '地区', '排序键': 1} region_total_sales = 0 region_total_task = 0 for category in all_categories: cat_data = region_df[region_df['类别'] == category] if len(cat_data) > 0: sales = float(cat_data['销量/额'].iloc[0]) task = float(cat_data['任务'].iloc[0]) progress = float(cat_data['进度'].iloc[0]) else: sales, task, progress = 0, 0, 0 row[f'{category}_销量'] = sales row[f'{category}_进度'] = progress region_total_sales += sales region_total_task += task row['总销售额'] = region_total_sales row['总任务'] = region_total_task region_data.append(row) print(f"地区 {region}: 销售={region_total_sales:.0f}, 任务={region_total_task:.0f}") # 按总销售额排序地区数据 region_data.sort(key=lambda x: x['总销售额']) result_data.extend(region_data) # === 第二部分:地市数据(重新聚合计算)=== print("\n=== 处理地市数据 ===") city_data = [] for city in sorted(df['地市'].unique()): city_df = df[df['地市'] == city] row = {'区域': city, '类型': '地市', '排序键': 2} city_total_sales = 0 city_total_task = 0 for category in all_categories: # 获取该地市该品类的所有地区数据 cat_data = city_df[city_df['类别'] == category] sales_sum = cat_data['销量/额'].sum() task_sum = cat_data['任务'].sum() # 重新计算进度:地市进度 = 地市销售合计 / 地市任务合计 progress = round(sales_sum * 100 / task_sum, 2) if task_sum > 0 else 0 row[f'{category}_销量'] = sales_sum row[f'{category}_进度'] = progress city_total_sales += sales_sum city_total_task += task_sum row['总销售额'] = city_total_sales row['总任务'] = city_total_task city_data.append(row) print( f"地市 {city}: 销售={city_total_sales:.0f}, 任务={city_total_task:.0f}, 进度={(city_total_sales / city_total_task * 100) if city_total_task > 0 else 0:.2f}%") # 按总销售额排序地市数据 city_data.sort(key=lambda x: x['总销售额']) result_data.extend(city_data) # === 第三部分:总计数据 === print("\n=== 处理总计数据 ===") total_row = {'区域': '总计', '类型': '总计', '排序键': 3} total_sales = 0 total_task = 0 for category in all_categories: cat_data = df[df['类别'] == category] sales_total = cat_data['销量/额'].sum() task_total = cat_data['任务'].sum() # 重新计算总进度:总计进度 = 总销售 / 总任务 progress_total = round(sales_total * 100 / task_total, 2) if task_total > 0 else 0 total_row[f'{category}_销量'] = sales_total total_row[f'{category}_进度'] = progress_total total_sales += sales_total total_task += task_total total_row['总销售额'] = total_sales total_row['总任务'] = total_task result_data.append(total_row) print( f"总计: 销售={total_sales:.0f}, 任务={total_task:.0f}, 进度={(total_sales / total_task * 100) if total_task > 0 else 0:.2f}%") # 构建最终DataFrame - 包含类型信息用于后续处理 final_columns = ['区域', '类型'] for cat in all_categories: final_columns.extend([f'{cat}_销量', f'{cat}_进度']) result_df = pd.DataFrame(result_data)[final_columns] print(f"\n✅ 转换完成,最终表格形状: {result_df.shape}") print("最终表格结构:") print(result_df[['区域', '类型'] + [f'{all_categories[0]}_销量', f'{all_categories[0]}_进度']].head().to_string()) return result_df, all_categories except Exception as e: print(f"❌ 数据转换失败: {str(e)}") import traceback traceback.print_exc() return None, None def create_multiindex_columns(categories): """创建多级表头""" columns = [('', '区域')] # 移除了类型列,但在数据中保留用于判断 for category in categories: columns.extend([(category, '销量/额'), (category, '进度')]) return pd.MultiIndex.from_tuples(columns) def img_create_variety_sales(data, title, format_dict, subset, path, file_mc, region_types=None): """ 生成品种销售进度表图片 - 调试版本 """ try: print(f"\n=== 开始生成图片 ===") print(f"数据形状: {data.shape}") print(f"区域类型信息长度: {len(region_types) if region_types is not None else 'None'}") # 获取品类列表 categories = [col[0] for col in data.columns if col[1] == '进度' and col[0] != ''] print(f"品类列表: {categories}") # 详细打印每行的区域和类型信息 print("\n=== 详细区域信息 ===") for idx in range(len(data)): region_name = data.iloc[idx][('', '区域')] region_type = region_types.iloc[idx] if region_types is not None and idx < len(region_types) else "未知" print(f"行 {idx}: 区域='{region_name}', 类型='{region_type}'") # 找出每个品类进度排名前三的地区 top3_styles = [] print("\n=== 计算各品类进度前三名 ===") # 构建地区索引映射 region_indices = {} for idx in range(len(data)): region_name = data.iloc[idx][('', '区域')] if region_types is not None and idx < len(region_types): region_type = region_types.iloc[idx] if region_type == '地区' and region_name != '总计': region_indices[region_name] = idx print(f"✅ 识别为地区: {region_name} (索引: {idx})") else: print(f"❌ 排除: {region_name} (类型: {region_type})") print(f"最终参与排名的地区: {list(region_indices.keys())}") # 为每个品类单独计算前三名 for category in categories: progress_col = (category, '进度') print(f"\n--- 处理品类: {category} ---") # 收集该品类所有地区的进度数据 region_progress = [] for region_name, row_idx in region_indices.items(): progress_value = data.iloc[row_idx][progress_col] if pd.notna(progress_value) and progress_value != 0: region_progress.append((region_name, float(progress_value), row_idx)) print(f" {region_name}: {progress_value}%") else: print(f" {region_name}: {progress_value}% (无效数据)") # 按进度值降序排列,取前三名 if region_progress: region_progress.sort(key=lambda x: x[1], reverse=True) top3_regions = region_progress[:3] print(f"{category} 品类进度前三名:") for i, (region_name, progress_value, row_idx) in enumerate(top3_regions, 1): print(f" 第{i}名: {region_name} - {progress_value}%") # 计算CSS选择器 col_idx = data.columns.get_loc(progress_col) row_css_idx = row_idx + 2 # +2是因为第一行是表头 col_css_idx = col_idx + 1 # +1是因为第一列是区域列 selector = f'tr:nth-child({row_css_idx}) td:nth-child({col_css_idx})' print(f" 应用样式: {selector}") top3_styles.append({ 'selector': selector, 'props': [ ('background-color', '#FF0000 !important'), ('color', '#FFFFFF !important'), ('font-weight', 'bold !important') ] }) else: print(f"{category} 品类没有有效的进度数据") print(f"\n总共生成了 {len(top3_styles)} 个前三名样式规则") # 设置基础样式 base_styles = [ {'selector': 'caption', # 标题 'props': [('font-weight', 'bold'), ('font-family', 'Microsoft Yahei'), ('color', '#000000'), ('background-color', '#F4B084'), ('font-size', '16px'), ('border-top', '2px solid #000000'), ('border-left', '2px solid #000000'), ('border-right', '2px solid #000000'), ('text-align', 'center'), ('padding', '6px')]}, {'selector': 'th.level0', # 一级表头 'props': [('font-family', 'Microsoft Yahei'), ('border', '1px solid #000000'), ('color', '#000000'), ('background-color', '#F4B084'), ('font-size', '10px'), ('vertical-align', 'center'), ('text-align', 'center'), ('white-space', 'nowrap'), ('padding', '4px')]}, {'selector': 'th.level1', # 二级表头 'props': [('font-family', 'Microsoft Yahei'), ('border', '1px solid #000000'), ('color', '#000000'), ('background-color', '#F4B084'), ('font-size', '9px'), ('vertical-align', 'center'), ('text-align', 'center'), ('white-space', 'nowrap'), ('padding', '3px')]}, {'selector': 'td.data', # 数据 'props': [('font-family', 'Microsoft Yahei'), ('border', '1px solid #000000'), ('font-size', '8px'), ('vertical-align', 'center'), ('text-align', 'center'), ('white-space', 'nowrap'), ('padding', '2px')]}, # 为总计行设置特殊样式 {'selector': 'tr:last-child td', 'props': [('background-color', '#FFFF00 !important'), ('font-weight', 'bold !important'), ('color', '#FF0000 !important')]} ] # 应用样式 styled_data = (data.style .hide(axis='index') .set_caption(title) .set_table_styles(base_styles) .format(format_dict, na_rep='-')) # 应用前三名样式 for style in top3_styles: styled_data = styled_data.set_table_styles([style], overwrite=False) # 导出图片 output_path = os.path.join(path, f'{file_mc}.png') dfi.export(styled_data, output_path) print(f"✅ 图片生成成功: {output_path}") return True except Exception as e: print(f"❌ 生成图片失败: {str(e)}") import traceback traceback.print_exc() return False def create_variety_sales_report(path, df, categories): """创建品种销售进度报表图片""" try: # 获取当前日期 now = dt.datetime.now() date_str = now.strftime("%Y%m%d") title_date = now.strftime("%Y年%m月%d日") # 保存类型信息到临时变量 - 确保索引对齐 region_types = df['类型'].reset_index(drop=True) # 创建多级表头(移除类型列) df_for_display = df.drop('类型', axis=1).reset_index(drop=True) multiindex_columns = create_multiindex_columns(categories) df_for_display.columns = multiindex_columns # 设置格式字典 format_dict = {} # 为销量列设置数值格式,为进度列设置百分比格式 for category in categories: format_dict[(category, '销量/额')] = '{:,.0f}' format_dict[(category, '进度')] = '{:.2f}%' # 需要渐变染色的列(只对进度列进行渐变染色) subset_cols = [(category, '进度') for category in categories] # 生成图片 - 修改标题为"截止X点X月X日地区各品种销售进度表" success = img_create_variety_sales( data=df_for_display, title=f"截止{now.hour}点{title_date}地区各品种销售进度表", format_dict=format_dict, subset=subset_cols, path=path, file_mc=f'品种销售进度表_{date_str}', region_types=region_types # 传递类型信息 ) if success: image_path = os.path.join(path, f'品种销售进度表_{date_str}.png') print(f"图片生成成功: {image_path}") return [image_path] else: return None except Exception as e: print(f"生成品种销售进度报表失败: {str(e)}") return None def send_robot_message(image_path, webhook_key): """通过企业微信群机器人发送图片消息""" try: # 读取图片内容 with open(image_path, 'rb') as f: image_data = f.read() # 将图片内容转换为base64 image_base64 = base64.b64encode(image_data).decode('utf-8') # 计算MD5 md5 = hashlib.md5(image_data).hexdigest() # 发送图片消息 send_url = f'https://qyapi.weixin.qq.com/cgi-bin/webhook/send?key={webhook_key}' payload = { "msgtype": "image", "image": { "base64": image_base64, "md5": md5 } } response = requests.post(send_url, json=payload).json() return response.get('errcode') == 0 except Exception as e: print(f"群机器人消息发送失败: {str(e)}") return False def application_push_image(access_token, image_path, user_id="037565", agent_id="1000077"): """推送图片到企业微信(个人)""" try: # 上传图片获取media_id with open(image_path, 'rb') as f: files = {'media': f} upload_url = f'https://qyapi.weixin.qq.com/cgi-bin/media/upload?access_token={access_token}&type=image' upload_response = requests.post(upload_url, files=files).json() if 'media_id' not in upload_response: print(f"图片上传失败: {upload_response}") return None media_id = upload_response['media_id'] # 发送图片消息 send_url = f'https://qyapi.weixin.qq.com/cgi-bin/message/send?access_token={access_token}' payload = { "touser": user_id, "msgtype": "image", "agentid": agent_id, "image": {"media_id": media_id}, "safe": 0 } response = requests.post(send_url, json=payload).json() return response except Exception as e: print(f"图片发送失败: {str(e)}") return None def send_variety_sales_report(path): """发送品种销售进度表""" try: # 初始化微信 wechat = Wechat('tXNMrgcTgeV3IAqJhWB7mOe_bcKe9EtdCDze_75mGeY') access_token = wechat.access_token() # 获取原始数据 sql = generate_variety_sales_sql() data, columns = oracle_connect(sql) # 转换为DataFrame df = pd.DataFrame(data, columns=columns) print(f"获取到 {len(df)} 条原始数据") # 数据验证 print("\n数据验证:") print(f"地市列表: {sorted(df['地市'].unique())}") print(f"地区列表: {sorted(df['地区'].unique())}") print(f"品类列表: {sorted(df['类别'].unique())}") # 显示样本数据 print("\n样本数据:") print(df.head(10).to_string()) # 转换数据为透视表格式 pivot_df, categories = transform_data_to_pivot(df) if pivot_df is None or len(pivot_df) == 0: raise Exception("数据转换后为空") print(f"转换后数据形状: {pivot_df.shape}") print("最终表格结构:") print(pivot_df[['区域']].to_string(index=False)) # 生成报表图片 image_paths = create_variety_sales_report(path, pivot_df, categories) if not image_paths: raise Exception("报表图片生成失败") # 群机器人webhook key webhook_key = "3cb06b7e-3658-4fbd-9676-54fc25fea470" # 推送报表图片 for image_path in image_paths: if os.path.exists(image_path): # 发送给群机器人 robot_result = send_robot_message(image_path, webhook_key) if robot_result: print(f"{dt.datetime.now()} - 品种销售进度表 群机器人推送成功") else: print(f"{dt.datetime.now()} - 品种销售进度表 群机器人推送失败") # 发送给个人 result = application_push_image(access_token, image_path, "037565") if result and result.get('errcode') == 0: print(f"{dt.datetime.now()} - 品种销售进度表 个人推送成功") else: print(f"{dt.datetime.now()} - 品种销售进度表 个人推送失败") # 删除临时文件 os.remove(image_path) return True except Exception as e: print(f"{dt.datetime.now()} - 品种销售进度表推送失败: {str(e)}") return False def run_variety_sales_scheduler(path): """运行品种销售进度表定时任务""" # 从早上10点到晚上22点,每2小时执行一次 schedule_times = [ "10:00","12:00", "14:00", "16:00", "18:00", "20:00", "22:00" ] for time_str in schedule_times: schedule.every().day.at(time_str).do(send_variety_sales_report, path=path) print(f"已设置定时任务: 每天 {time_str} 执行") # 立即测试一次 print(f"{dt.datetime.now()} - 正在执行品种销售进度表首次测试...") send_variety_sales_report(path) while True: schedule.run_pending() time.sleep(60) if __name__ == '__main__': print(f'品种销售进度表程序启动时间: {dt.datetime.now()}') output_path = r'D:\品种销售数据报表' if not os.path.exists(output_path): os.makedirs(output_path) # 启动定时任务线程 scheduler_thread = Thread(target=run_variety_sales_scheduler, args=(output_path,)) scheduler_thread.daemon = True scheduler_thread.start() try: while True: time.sleep(60) except KeyboardInterrupt: print("品种销售进度表程序已停止") 检查代码每个类别选出进度前三的地区填充红色背景白色字体,地市不参与填充
10-02
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值