int 提取当前包括负数存量(string edt_id_gather)
{
//参数：仓库集合编号,空白就全部
string temp_sql;
temp_sql="";
if(edt_id_gather!="")
temp_sql=" and edt_id in "+edt_id_gather+" ";
string sql
sql="select res_id,sum(num) as num from edt_res where 1=1 "+temp_sql+" group by res_id";
db_run(sql);
return 1;
};
//产品最近单据日期 明细 参数：单据类型集合，状态集合
int res_end_date_item(string vou_type_father,string vou_state_father)
{
string temp_sql;
string sql
sql="select a.voucher_id,a.voucher_date,c.res_id,c.std_num from ebs_v a,ebs_vr b,ebs_vr_item c,";
sql+="(select max(a.voucher_date)as voucher_date,c.res_id "
sql+="from ebs_v a,ebs_vr b,ebs_vr_item c "
sql+="where a.voucher_id=b.voucher_id and a.voucher_id=c.voucher_id "
if(vou_type_father!="")
sql+="and a.voucher_type in "+str_lst_to_sql_lst(vou_type_father)+" ";
if(vou_state_father!="")
sql+="and a.state in "+str_lst_to_sql_lst(vou_state_father)+" ";
sql+="group by c.res_id) as h where a.voucher_id=b.voucher_id and a.voucher_id=c.voucher_id "
sql+="and a.voucher_date=h.voucher_date and c.res_id=h.res_id "
if(vou_type_father!="")
sql+="and a.voucher_type in "+str_lst_to_sql_lst(vou_type_father)+" ";
if(vou_state_father!="")
sql+="and a.state in "+str_lst_to_sql_lst(vou_state_father)+" ";
sql+=" order by c.res_id,a.voucher_id desc "
db_run(sql);
//dbg(sql);
return 1;
};
int 产品BOM表()
{
string sql;
sql="select res_id from res_composing ";
sql+="group by res_id ";
//dbg(sql)
db_run(sql);
return 1;
};
int 标记BOM产品(string 产品编号列,string BOM编号列,string 标记列)
{
int n,id;
n = db_row_count(); // SQL检索出报表 提取报表行数
for(id=1;id<=n;id++)
//循环次数 id的初始值为1.一般表格有合计可以少循环一次如果一定要的id<=n
{
string ext_res_id
ext_res_id=db_res_ext(产品编号列);
//数据区 产品编号存，查找bom表是否有本物料
db_select_area(1);
if(db_seek(BOM编号列, ext_res_id))
{
db_select_area(0);
db_set_value_ext(标记列,'是');
db_set_value_ext("single_deploy","+");
db_set_value_ext("multi_deploy","++");
//bom列 单个展开 和 全部展开 设置标识
};
db_select_area(0);
db_next();
//数据区 下行
};
return 1;
};
int 标记BOM明细(string 产品编号列,string BOM编号列,string 标记列,int 标记数据区编号)
{
int n,id;
n = db_row_count(); // SQL检索出报表 提取报表行数
for(id=1;id<=n;id++)
//循环次数 id的初始值为1.一般表格有合计可以少循环一次如果一定要的id<=n
{
string ext_res_id
ext_res_id=db_res_ext(产品编号列);
//数据区 产品编号存，查找bom表是否有本物料
db_select_area(1);
if(db_seek(BOM编号列,ext_res_id))
{
db_select_area(标记数据区编号);
db_set_value_ext(标记列,'是');
//db_show();
};
db_select_area(标记数据区编号);
db_next();
//数据区 下行
};
return 1;
};
int 产品资料标准bom组成()
{
string sql;
sql="select a.order_id,a.res_id,sub_res_id,num,"
sql+="res_cat_id,res_cat_id as res_cat_id_name,res_name,parent_res_id,auto_copy_parent_info,batch_flag,"
sql+="res_unit_type,res_unit_type as res_unit_type_name,bar_code,quality_days,res_spec,ceil_num,"
sql+="floor_num,manufacturer,stop_flag,in_ceil_price,in_ref_price,"
sql+="out_floor_price,out_ref_price,res_kind,res_kind as res_kind_name,cost_price,";
sql+="price_dot_num,easy_code,default_edt_id,res_model,res_rank,res_place,"
sql+="ass_unit_type,num_dot_num,res_desc,edt_io_flag,cost_cal_method,edt_ceil_num,";
sql+="edt_floor_num,b.ext_1,b.ext_2,b.ext_3,b.ext_4,b.ext_5,b.ext_6,b.ext_7,b.ext_8,"
sql+="create_date,create_user_id,last_modi_date,last_modi_user_id,emf_route_id,"
sql+="subject_storage_amount,subject_sell_income,subject_sell_cost,'' as y_bom "
sql+="from res_composing a ,res b where a.sub_res_id=b.res_id order by a.res_id,a.order_id,sub_res_id desc";
//dbg(sql)
db_run_query_thr(sql);
db_map("res_catalog","res_cat_id_name","res_cat_id_name");
db_map("res_unit_type","res_unit_type_name","res_unit_type_name");
db_map("res_kind","res_kind_name","res_kind_name");
db_add_row();
//编号转名字
//db_show();
return 1;
};
int 单个产品标准bom组成(string ext_res_id)
{
string sql;
sql="select a.order_id,a.res_id,sub_res_id,num,"
sql+="res_cat_id,res_cat_id as res_cat_id_name,res_name,parent_res_id,auto_copy_parent_info,batch_flag,"
sql+="res_unit_type,res_unit_type as res_unit_type_name,bar_code,quality_days,res_spec,ceil_num,"
sql+="floor_num,manufacturer,stop_flag,in_ceil_price,in_ref_price,"
sql+="out_floor_price,out_ref_price,res_kind,res_kind as res_kind_name,cost_price,";
sql+="price_dot_num,easy_code,default_edt_id,res_model,res_rank,res_place,"
sql+="ass_unit_type,num_dot_num,res_desc,edt_io_flag,cost_cal_method,edt_ceil_num,";
sql+="edt_floor_num,b.ext_1,b.ext_2,b.ext_3,b.ext_4,b.ext_5,b.ext_6,b.ext_7,b.ext_8,"
sql+="create_date,create_user_id,last_modi_date,last_modi_user_id,emf_route_id,"
sql+="subject_storage_amount,subject_sell_income,subject_sell_cost,'' as y_bom "
sql+="from res_composing a ,res b where a.sub_res_id=b.res_id and a.res_id='"+ext_res_id+"' ";
sql+="order by a.res_id,a.order_id";
//dbg(sql)
db_run_query_thr(sql);
db_map("res_catalog","res_cat_id_name","res_cat_id_name");
db_map("res_unit_type","res_unit_type_name","res_unit_type_name");
db_map("res_kind","res_kind_name","res_kind_name");
db_map("yes_no","stop_flag","stop_flag");
//编号转名字
//db_show();
return 1;
};
int bom组装写入表格(int mg_row_id)
{
grid_set_v("MG","y_bom",mg_row_id,db_res_ext("y_bom"));
grid_set_v("MG","res_id",mg_row_id,db_res_ext("sub_res_id"));
grid_set_v("MG","res_name",mg_row_id,db_res_ext("res_name"));
grid_set_v("MG","res_spec",mg_row_id,db_res_ext("res_spec"));
grid_set_v("MG","res_model",mg_row_id,db_res_ext("res_model"));
grid_set_v("MG","res_desc",mg_row_id,db_res_ext("res_desc"));
grid_set_v("MG","res_unit_type",mg_row_id,db_res_ext("res_unit_type"));
grid_set_v("MG","res_unit_type_name",mg_row_id,db_res_ext("res_unit_type_name"));
grid_set_v("MG","res_kind_name",mg_row_id,db_res_ext("res_kind_name"));
grid_set_v("MG","stop_flag_name",mg_row_id,db_res_ext("stop_flag"));
grid_set_v("MG","bom_num",mg_row_id,db_res_ext("num"));
grid_set_v("MG","res_cat_name",mg_row_id,db_res_ext("res_cat_id_name"));
return 1;
};
int 单次展开bom报表(int ext_cur_row)
{
int mg_row_id
mg_row_id=ext_cur_row;
int ext_num_plies
ext_num_plies=grid_get_v("MG","ext_num_plies",mg_row_id);
string ext_num_of_plies
ext_num_of_plies=grid_get_v("MG","ext_num_of_plies",mg_row_id);
if(strlen(ext_num_of_plies)>=80)
{
grid_set_row_bgcolor("MG",mg_row_id,13823999);
return 0;
};
//层级超出40层级关系，该行颜色标识。退出执行
db_select_area(1);
产品BOM表();
db_select_area(3);
单个产品标准bom组成(grid_get_v("MG","res_id",ext_cur_row));
db_go_head();
标记BOM明细("sub_res_id","res_id","y_bom",3)
db_select_area(3);
db_go_head();
//db_show();
int bom_item_yn
bom_item_yn=0;
//组装明细是否为全部非bom物资
int bom_item_yn
bom_item_yn=0;
//组装明细是否为全部非bom物资
int n,id;
n = db_row_count(); // SQL检索出报表 提取报表行数
for(id=1;id<=n;id++)
//循环次数 id的初始值为1.一般表格有合计可以少循环一次如果一定要的id<=n
{
grid_insert_before("MG",mg_row_id+id);
grid_set_v("MG","ext_num_plies",mg_row_id+id,ext_num_plies+1);
grid_set_v("MG","ext_num_of_plies",mg_row_id+id,ext_num_of_plies+"."+(ext_num_plies+1));
if(db_res_ext("y_bom")=="是")
{
grid_set_v("MG","single_deploy",mg_row_id+id,"+");
bom_item_yn++;
};
bom组装写入表格(mg_row_id+id );
db_next();
};
grid_set_v("MG","single_deploy",mg_row_id,"-");
return 1;
};
int 单次合拢bom报表(int ext_cur_row)
{
int mg_row_id
mg_row_id=ext_cur_row;
//当前行号
int ext_num_plies
ext_num_plies=grid_get_v("MG","ext_num_plies",mg_row_id);
//当前层级
string ext_num_of_plies
ext_num_of_plies=grid_get_v("MG","ext_num_of_plies",mg_row_id);
//当前层级关系
grid_set_v("MG","single_deploy",mg_row_id,"+");
//把点击行单个展开列更新为+;
int id;
id=0;
while(id==0)
{
if(ext_num_plies<to_int(grid_get_v("MG","ext_num_plies",mg_row_id+1)))
{grid_del_row("MG",mg_row_id+1);}
else
{id++};
};
return 1;
};
int 单次执行bom报表(int ext_cur_row)
{
int mg_row_id
mg_row_id=ext_cur_row;
string y_bom
y_bom=grid_get_v("MG","y_bom",mg_row_id);
string single_deploy;
single_deploy=grid_get_v("MG","single_deploy",mg_row_id);
string multi_deploy;
multi_deploy=grid_get_v("MG","multi_deploy",mg_row_id);
//是否BOM,单个展开,全部展开 存放三个变量值
if(y_bom=="是"&&single_deploy=="+")
{
单次展开bom报表(mg_row_id);
//dbg(y_bom+" "+single_deploy+" "+multi_deploy);
}
else if(y_bom=="是"&&single_deploy=="-")
{
单次合拢bom报表(mg_row_id);
};
return 1;
};
int 单次展开标准bom报表(int ext_cur_row)
{
int mg_row_id
mg_row_id=ext_cur_row;
int ext_num_plies
ext_num_plies=grid_get_v("MG","ext_num_plies",mg_row_id);
string ext_num_of_plies
ext_num_of_plies=grid_get_v("MG","ext_num_of_plies",mg_row_id);
if(strlen(ext_num_of_plies)>=80)
{
grid_set_row_bgcolor("MG",mg_row_id,13823999);
return 0;
};
//层级超出9层级关系，该行颜色标识。退出执行
/*
********
db_select_area(1);
产品BOM表();
db_select_area(3);
产品资料标准bom组成();
*/
db_select_area(3);
db_go_head();
标记BOM明细("sub_res_id","res_id","y_bom",3)
db_select_area(3);
db_go_head();
string ext_res_id;
ext_res_id=grid_get_v("MG","res_id",mg_row_id);
//db_show();
//******************************************************
int bom_item_yn
bom_item_yn=0;
//组装明细是否为全部非bom物资
int id;
id=1;
if(db_seek("res_id",ext_res_id))
{
do
{
grid_insert_before("MG",mg_row_id+id);
grid_set_v("MG","ext_num_plies",mg_row_id+id,ext_num_plies+1);
grid_set_v("MG","ext_num_of_plies",mg_row_id+id,ext_num_of_plies+"."+(ext_num_plies+1));
if(db_res_ext("y_bom")=="是")
{
grid_set_v("MG","single_deploy",mg_row_id+id,"+");
bom_item_yn++;
};
bom组装写入表格(mg_row_id+id );
//dbg(mg_row_id+id+" "+db_res_ext("sub_res_id"));
db_next();
id++;
}while(ext_res_id==db_res_ext("res_id")&&db_res_ext("res_id")!="");
};
grid_set_v("MG","single_deploy",mg_row_id,"-");
return 1;
};
int 单次限制执行bom层级报表(int ext_cur_row,int ext_num_plies)
{
int mg_row_id
mg_row_id=ext_cur_row;
int num_plies
num_plies=grid_get_v("MG","ext_num_plies",mg_row_id);
string y_bom
y_bom=grid_get_v("MG","y_bom",mg_row_id);
string single_deploy;
single_deploy=grid_get_v("MG","single_deploy",mg_row_id);
string multi_deploy;
multi_deploy=grid_get_v("MG","multi_deploy",mg_row_id);
//是否BOM,单个展开,全部展开 存放三个变量值
if(y_bom=="是"&&single_deploy=="+"&&num_plies<=ext_num_plies)
{
单次展开标准bom报表(mg_row_id);
//dbg(y_bom+" "+single_deploy+" "+multi_deploy);
}
return 1;
};
int 单次全部展开执行bom报表(int ext_cur_row)
{
int mg_row_id
mg_row_id=ext_cur_row;
string y_bom
y_bom=grid_get_v("MG","y_bom",mg_row_id);
string single_deploy;
single_deploy=grid_get_v("MG","single_deploy",mg_row_id);
string multi_deploy;
multi_deploy=grid_get_v("MG","multi_deploy",mg_row_id);
//是否BOM,单个展开,全部展开 存放三个变量值
int ext_num_plies
ext_num_plies=grid_get_v("MG","ext_num_plies",mg_row_id);
//当前层级
string ext_num_of_plies
ext_num_of_plies=grid_get_v("MG","ext_num_of_plies",mg_row_id);
//当前层级关系变量
if(y_bom=="是"&&multi_deploy=="++")
{
grid_set_v("MG","multi_deploy",mg_row_id,"--");
单次执行bom报表(mg_row_id)
int id;
id=1;
while(id>0)
{
if(ext_num_plies<to_int(grid_get_v("MG","ext_num_plies",mg_row_id+id)))
{
if(grid_get_v("MG","y_bom",mg_row_id+id)=="是"&&grid_get_v("MG","single_deploy",mg_row_id+id)=="+")
{
单次展开bom报表(mg_row_id+id)
};
id++;
}
else
{
id=0;
};
// 当前层级数》比较层级数 执行开展。否则就退出
};
}
else if(y_bom=="是"&&multi_deploy=="--")
{
grid_set_v("MG","multi_deploy",mg_row_id,"++");
单次合拢bom报表(mg_row_id);
};
return 1;
};
int get_last_time_price(string ext_vou_type) //取得物资上次价格合计金额大于0 参数：单据类型
{
string sql;
sql="select c.res_id,c.std_num,c.total_amount from ebs_v a,ebs_vr_item c ";
sql+="where a.voucher_id=c.voucher_id ";
sql+="and a.voucher_type in "+str_lst_to_sql_lst(ext_vou_type)+" and a.state in('B','C') and c.total_amount>0 and c.std_num>0 ";
sql+="order by c.res_id,a.voucher_date desc"
//dbg(sql);
db_run(sql);
return 1;
};
int func_can_run()
{
//if(!have_any_priv('eba_rep,eba_rep.voucher,eba_rep.item')) return 0;
return 1;
};
int func_def_lmt()
{
def_lmt_dict('bom_num_plies','BOM层级','ext_bom_deep');
lmt_set_hide('bom_num_plies');
lmt_set_must_checked('bom_num_plies');
//def_lmt_str('res_qry','产品查询'); //def_lmt_dict_multi_set('res','产品',"res_main");
def_lmt_dict('res','产品',"res_main");
lmt_set_must_checked('res');
def_lmt_dict_multi('res_kind','产品性质','res_kind');
def_lmt_dict_multi('res_catalog','产品目录','res_catalog');
//def_lmt_dict('top_catalog_id','父目录','res_catalog');
def_lmt_dict('res_stop_flag','停用物资','bool');
def_lmt_dict('res_bom','BOM显示','bool');
return 1;
}
int func_init_lmt_val()
{
def_lmt_set_val('bom_num_plies','0');
lmt_set_checked('res_stop_flag');
def_lmt_set_val('res_stop_flag','N');
return 1;
}
int func_def_field()
{
db_set_field_count(77);
db_set_field_info(0,'res_id','产品编号',82)
db_set_field_info(1,'res_cat_id','目录编号',82)
db_set_field_info(2,'res_cat_name','目录名称',82)
db_set_field_info(3,'res_name','产品名称',82)
db_set_field_info(4,'parent_res_id','上级产品',82)
db_set_field_info(5,'auto_copy_parent_info','自动同步',82)
db_set_field_info(6,'batch_flag','库存是否分批次',82)
db_set_field_info(7,'res_unit_type','计件单位',82)
db_set_field_info(8,'res_unit_type_name','单位名称',82)
db_set_field_info(9,'bar_code','条形码',82)
db_set_field_info(10,'quality_days','保质天数',82)
db_set_field_info(11,'res_spec','规格',82)
db_set_field_info(12,'ceil_num','库存上限',82)
db_set_field_info(13,'floor_num','库存下限',82)
db_set_field_info(14,'manufacturer','厂商',82)
db_set_field_info(15,'stop_flag','停用编号',82)
db_set_field_info(16,'stop_flag_name','停用标志',82)
db_set_field_info(17,'in_ceil_price','最高进价',82)
db_set_field_info(18,'in_ref_price','参考进价',82)
db_set_field_info(19,'out_floor_price','最低售价',82)
db_set_field_info(20,'out_ref_price','参考售价',82)
db_set_field_info(21,'res_kind','性质编号',82)
db_set_field_info(22,'res_kind_name','性质名称',82)
db_set_field_info(23,'order_id','顺序码',82)
db_set_field_info(24,'cost_price','核算价格',82)
db_set_field_info(25,'price_dot_num','价格保留小数位数',82)
db_set_field_info(26,'easy_code','助记码',82)
db_set_field_info(27,'default_edt_id','默认仓库',82)
db_set_field_info(28,'res_model','产品型号',82)
db_set_field_info(29,'res_rank','产品等级',82)
db_set_field_info(30,'res_place','存放位置',82)
db_set_field_info(31,'ass_unit_type','辅助数量单位',82)
db_set_field_info(32,'ass_unit_type_name','辅助数量单位名称',82)
db_set_field_info(33,'num_dot_num','数量小数位数',82)
db_set_field_info(34,'res_desc','其他描述',82)
db_set_field_info(35,'edt_io_flag','是否进行存货编号',82)
db_set_field_info(36,'edt_io_flag_name','是否进行存货管理',82)
db_set_field_info(37,'cost_cal_method','成本计算编号',82)
db_set_field_info(38,'cost_cal_method_name','成本计算方式',82)
db_set_field_info(39,'edt_ceil_num','分仓库存上限',82)
db_set_field_info(40,'edt_floor_num','分仓库存下限',82)
db_set_field_info(41,'ext_1','扩展一',82)
db_set_field_info(42,'ext_2','扩展二',82)
db_set_field_info(43,'ext_3','扩展三',82)
db_set_field_info(44,'ext_4','扩展四',82)
db_set_field_info(45,'ext_5','扩展五',82)
db_set_field_info(46,'ext_6','扩展六',82)
db_set_field_info(47,'ext_7','扩展七',82)
db_set_field_info(48,'ext_8','扩展八',82)
db_set_field_info(49,'create_date','创建日期',82)
db_set_field_info(50,'create_user_id','创建工号',82)
db_set_field_info(51,'create_user_id_name','创建名称',82)
db_set_field_info(52,'last_modi_date','上次修改日期',82)
db_set_field_info(53,'last_modi_user_id','上次修改工号',82)
db_set_field_info(54,'last_modi_user_id_name','上次修改名称',82)
db_set_field_info(55,'emf_route_id','工艺路线编号',82)
db_set_field_info(56,'emf_route_id_name','工艺路线名称',82)
db_set_field_info(57,'subject_storage_amount','存货科目代码',82)
db_set_field_info(58,'subject_sell_income','销售收入科目代码',82)
db_set_field_info(59,'subject_sell_cost','销售成本科目代码',82)
db_set_field_info(60,'ext_num_plies','层级',60);
db_set_field_info(61,'ext_num_of_plies','层级关系',60);
db_set_field_info(62,'y_bom','是否BOM',60);
db_set_field_info(63,'single_deploy','单个展开',60);
db_set_field_info(64,'multi_deploy','全部展开',60);
db_set_field_info(65,'main_res_id','主物料编号',60);
db_set_field_info(66,'main_res_name','主物料名',60);
db_set_field_info(67,'main_res_model','主物料型号',60);
db_set_field_info(68,'main_res_spec','主物料规格',60);
db_set_field_info(69,'bom_num','BOM用量',60);
db_set_field_type_s('bom_num','I');
db_set_field_info(70,'std_price','历史订单进价',60);
db_set_field_type_s('std_price','I');
db_set_field_info(71,'sum_std_price','合计进价',60);
db_set_field_type_s('sum_std_price','I');
db_set_field_info(72,'std_amount','历史订单金额',60);
db_set_field_type_s('std_amount','I');
db_set_field_sum_flag('std_amount');
db_set_field_info(73,'sum_std_amount','合计金额',60);
db_set_field_type_s('sum_std_amount','I');
db_set_field_sum_flag('sum_std_amount');
db_set_field_info(74,'ext_edt_num','当前存量',60);
db_set_field_type_s('ext_edt_num','I');
db_set_field_info(75,'end_vou_date','最近单据日期',60);
db_set_field_info(76,'end_vou_num','最近单据数量',60);
db_set_field_type_s('end_vou_num','I');
return 1;
}
int func_query()
{
db_select_area(0);
string sql_res;
sql_res="select res_id,res_cat_id,res_cat_id as res_cat_name,res_name,parent_res_id,";
sql_res+="auto_copy_parent_info,batch_flag,res_unit_type,res_unit_type as res_unit_type_name,";
sql_res+="bar_code,quality_days,res_spec,ceil_num,floor_num,manufacturer,stop_flag,stop_flag as stop_flag_name,";
sql_res+="in_ceil_price,in_ref_price,out_floor_price,out_ref_price,res_kind,res_kind as res_kind_name,";
sql_res+="order_id,cost_price,price_dot_num,easy_code,default_edt_id,res_model,res_rank,res_place,ass_unit_type,ass_unit_type as ass_unit_type_name,";
sql_res+="num_dot_num,res_desc,edt_io_flag,edt_io_flag as edt_io_flag_name,cost_cal_method,cost_cal_method as cost_cal_method_name,";
sql_res+="edt_ceil_num,edt_floor_num,ext_1,ext_2,ext_3,ext_4,ext_5,ext_6,ext_7,ext_8,create_date,create_user_id,create_user_id as create_user_id_name,";
sql_res+="last_modi_date,last_modi_user_id,last_modi_user_id as last_modi_user_id_name,emf_route_id,";
sql_res+="emf_route_id as emf_route_id_name,subject_storage_amount,subject_sell_income,subject_sell_cost,";
sql_res+="'1' as ext_num_plies,'1' as ext_num_of_plies,'' as y_bom,'' as single_deploy,'' as multi_deploy,";
sql_res+="res_id as main_res_id,res_name as main_res_name,res_model as main_res_model,res_spec as main_res_spec,'' as bom_num,";
sql_res+="'' as std_price,'' as sum_std_price,'' as std_amount,'' as sum_std_amount,'' as ext_edt_num,'' as end_vou_date,'' as end_vou_num ";
sql_res+="from res where 1=1 ";
if(lmt_checked('res_bom')&&lmt_val('res_bom')=="Y")
sql_res+="and res_id in (select res_id from res_composing group by res_id ) ";
if(lmt_checked('res_bom')&&lmt_val('res_bom')=="N")
sql_res+="and res_id not in (select res_id from res_composing group by res_id ) ";
if(lmt_checked('res'))
sql_res+="and res_id='"+lmt_val('res')+"' ";
//sql_res+="and res_id in (select key_id from app_tmp_key_set where set_id ="+lmt_val("res")+") ";
if(lmt_checked('res_kind')&&lmt_val('res_kind')!="")
sql_res+="and res_kind in "+str_lst_to_sql_lst(lmt_val("res_kind"))+" ";
if(lmt_checked('res_catalog')&&lmt_val('res_catalog')!="")
sql_res+="and res_cat_id in "+str_lst_to_sql_lst(lmt_val("res_catalog"))+" ";
if(lmt_checked('res_stop_flag')&&lmt_val('res_stop_flag')!="")
sql_res+="and stop_flag='"+(lmt_val("res_stop_flag"))+"' ";
db_run(sql_res);
//dbg(sql_res);
db_map("res_catalog","res_cat_name","res_cat_name");
db_map("res_unit_type","res_unit_type_name","res_unit_type_name");
db_map("yes_no","stop_flag_name","stop_flag_name");
db_map("res_kind","res_kind_name","res_kind_name");
db_map("res_unit_type","ass_unit_type_name","ass_unit_type_name");
db_map("yes_no","edt_io_flag_name","edt_io_flag_name");
db_map("res_cost_cal_method","cost_cal_method_name","cost_cal_method_name");
db_map("user","create_user_id_name","create_user_id_name");
db_map("user","last_modi_user_id_name","last_modi_user_id_name");
db_map("emf_route","emf_route_id_name","emf_route_id_name");
db_sort_ext('res_id');
db_add_row();
db_go_head();
db_select_area(1);
产品BOM表();
//作用：所有bom物料暂存表格
db_select_area(0);
db_go_head();
标记BOM产品("res_id","res_id","y_bom")
db_select_area(2);
产品资料标准bom组成();
db_add_row();
//产品资料标准bom组成,插入一行空白行表示最后一行
db_go_head();
标记BOM明细("sub_res_id","res_id","y_bom",2)
//db_show();
//提取当前库存
db_select_area(7);
string edt_val;
edt_val=str_lst_to_sql_lst(pub_para_get('mrp_edt_val'));//仓库编号集合
提取当前包括负数存量(edt_val);
//产品最近单据日期 明细
db_select_area(8);
//产品最近单据日期 明细 参数：单据类型集合，状态集合
res_end_date_item("AA","");
//写入当前存量
db_select_area(0);
string ext_res_id,ext_edt_num,ext_vou_date,ext_vou_num;
db_go_head();
int n,id;
n=db_row_count(); // SQL检索出报表 提取报表行数
for(id=1;id<=n;id++)
{
ext_edt_num="";
ext_vou_date="";
ext_vou_num="";
ext_res_id=db_res_ext("res_id");
db_select_area(7);
if(db_seek("res_id",ext_res_id))
{
ext_edt_num=db_res_ext("num");
};
db_select_area(8);
if(db_seek("res_id",ext_res_id))
{
ext_vou_date=db_res_ext("voucher_date");
ext_vou_num=db_res_ext("std_num");
};
db_select_area(0);
db_set_value_ext("ext_edt_num",ext_edt_num);
db_set_value_ext("end_vou_date", ext_vou_date);
db_set_value_ext("end_vou_num",ext_vou_num);
db_next();
};
db_select_area(0);
/*
数据区-0；
bom产品列表-1；
产品资料标准bom组成-2
当前存量-7
最近销售订单数据区-8
*/
/*
int plies_n,id;
plies_n =lmt_val("bom_num_plies");
for(id=1;id<=plies_n;id++)
//循环次数 id的初始值为1.一般表格有合计可以少循环一次如果一定要的id<=n
{
db_select_area(0);
db_go_head();
产品BOM展开(id)
db_select_area(0)
db_go_head();
};
db_select_area(5);
明细产品集合条件();
db_select_area(0);
db_go_head();
删除数据区行();
*/
//dbg(get_cur_time())
return 1;
}
int get_grid_price(int select_area_id,string grid_name) //参数：数据区编号，表格名
{
//进入价格数据区
db_select_area(select_area_id);
//db_show();
int n,id; //定义整数 循环条件初始化数
n=grid_row_num(grid_name)-1; //报表中数据表格： MG
//dbg("n: "+n)
for(id=1;id<=n;id++)
//循环次数 id的初始值为1.一般表格有合计可以少循环一次如果一定要的id<=n
{
string temp_res_id;
temp_res_id=grid_get_v(grid_name,"res_id",id);
//dbg("temp_res_id: "+temp_res_id)
num temp_std_num,temp_total_amount,temp_price;
temp_price="";
if(db_seek("res_id",temp_res_id))
{
temp_std_num=db_res_ext("std_num");
temp_total_amount=db_res_ext("total_amount")/100;
if(temp_std_num!=0)
{temp_price=num_trim(temp_total_amount/temp_std_num)};
};
grid_set_v(grid_name,"std_price",id,temp_price);
grid_set_v(grid_name,"std_amount",id,num_dec_cut(temp_price*grid_get_v(grid_name,"bom_num",id),2));
//dbg(temp_std_num+" ** "+temp_total_amount+" ** "+temp_price)
};
return 1;
}
int get_grid_sum_price(string grid_name) //提取合计价格 参数：数据区编号，表格名
{
int n,id; //定义整数 循环条件初始化数
n=grid_row_num(grid_name)-1; //报表中数据表格： MG
//dbg("n: "+n)
for(id=1;id<=n;id++)
//循环次数 id的初始值为1.一般表格有合计可以少循环一次如果一定要的id<=n
{
string temp_single_deploy,temp_ext_num_of_plies;
temp_single_deploy=grid_get_v(grid_name,"single_deploy",id);
temp_ext_num_of_plies=grid_get_v(grid_name,"ext_num_of_plies",id);
num temp_sum_std_amount;
temp_sum_std_amount=0;
//dbg("temp_res_id: "+temp_res_id)
if(temp_single_deploy!="")
{
int rn,rid; //定义整数 循环条件初始化数
rn=n; //报表中数据表格： MG
for(rid=id+1;rid<=rn;rid++)
//循环次数 id的初始值为1.一般表格有合计可以少循环一次如果一定要的id<=n
{
string temp_val;
temp_val=grid_get_v(grid_name,"ext_num_of_plies",rid);
string temp_single_deploy;
temp_single_deploy=grid_get_v(grid_name,"single_deploy",rid);
num temp_num;
temp_num=grid_get_v(grid_name,"std_amount",rid);
if(temp_ext_num_of_plies!=temp_val&&str_pos(temp_val,temp_ext_num_of_plies)>-1)
{
if(temp_single_deploy=="")
temp_sum_std_amount+=temp_num;
//dbg("sum_std_amount: "+temp_num)
}
else
{
grid_set_v(grid_name,"sum_std_amount",id,temp_sum_std_amount);
break
};
};
}
};
return 1;
}
int func_run_over()
{
//dbg(get_cur_time())
grid_disable_sort("MG");
int n; //定义整数 报表行数
int plies_n;
plies_n =lmt_val("bom_num_plies");
db_select_area(1);
产品BOM表();
db_select_area(3);
产品资料标准bom组成();
//提取当前库存
db_select_area(7);
string edt_val;
edt_val=str_lst_to_sql_lst(pub_para_get('mrp_edt_val'));//仓库编号集合
提取当前包括负数存量(edt_val);
db_select_area(8);
//产品最近单据日期 明细 参数：单据类型集合，状态集合
res_end_date_item("AA","");
db_select_area(0);
//dbg(get_cur_time())
int id; //定义整数 循环条件初始化数
n = grid_row_num("MG"); //报表中数据表格： MG
for(id=1;id<=n&&plies_n>0;id++)
//循环次数 id的初始值为1.一般表格有合计可以少循环一次如果一定要的id<=n
{
if(grid_get_v("MG","single_deploy",id)=="+")
{
单次限制执行bom层级报表(id,plies_n);
n = grid_row_num("MG");
};
};
db_select_area(5);
get_last_time_price("AA,AB") //取得物资上次价格合计金额大于0 参数：单据类型
//db_show();
//提取历史订单价格
get_grid_price(5,"MG") //参数：数据区编号，表格名
get_grid_sum_price("MG");//提取合计金额
db_select_area(0); //产品明细价格控制
if(!have_exec_priv("u_CP_001_grid_def")&&!is_super_user())
{
grid_hide_col("MG","inp_price");
grid_hide_col("MG","inp_amount");
grid_hide_col("MG","discount");
grid_hide_col("MG","discount_amount");
grid_hide_col("MG","tax_price");
grid_hide_col("MG","tax_rate");
grid_hide_col("MG","tax_amount");
grid_hide_col("MG","item_fee_1");
grid_hide_col("MG","item_fee_2");
grid_hide_col("MG","total_amount");
grid_hide_col("MG","std_price");
grid_hide_col("MG","sum_std_price");
grid_hide_col("MG","std_amount");
grid_hide_col("MG","sum_std_amount");
menu_hide_item("N_Oper_Def_Fmt");
};
return 1;
}
//把当前存量和最近单据日期 数量写入表格
int set_ext_val()
{
string ext_res_id,ext_edt_num,ext_vou_date,ext_vou_num;
int n; //定义整数 报表行数
int id; //定义整数 循环条件初始化数
n= grid_row_num("MG")-1; //报表中数据表格： MG
//dbg(n);
for(id=1;id<=n;id++)
//循环次数 id的初始值为1.一般表格有合计可以少循环一次如果一定要的id<=n
{
ext_res_id=grid_get_v('MG','res_id',id);
//dbg(ext_res_id);
if(ext_res_id!="")//如果没有这条判定则所列都有内容，影响美观，审核是一样的 。
{
ext_edt_num="";
ext_vou_date="";
ext_vou_num="";
db_select_area(7);
if(db_seek("res_id",ext_res_id))
{
ext_edt_num=db_res_ext("num");
};
db_select_area(8);
if(db_seek("res_id",ext_res_id))
{
ext_vou_date=db_res_ext("voucher_date");
ext_vou_num=db_res_ext("std_num");
};
grid_set_v('MG','ext_edt_num',id,ext_edt_num);
grid_set_v('MG','end_vou_date',id,ext_vou_date);
grid_set_v('MG','end_vou_num',id,ext_vou_num);
}
};
};
int func_dbl_click()
{
if(grid_get_col_name("MG",grid_cur_col("MG"))=="single_deploy")
{
单次执行bom报表(grid_cur_row("MG"));
//提取历史订单价格
get_grid_price(5,"MG") //参数：数据区编号，表格名
get_grid_sum_price("MG");//提取合计金额
//把当前存量和最近单据日期 数量写入表格
set_ext_val()
};
if(grid_get_col_name("MG",grid_cur_col("MG"))=="multi_deploy")
{
单次全部展开执行bom报表(grid_cur_row("MG"));
//提取历史订单价格
get_grid_price(5,"MG") //参数：数据区编号，表格名
get_grid_sum_price("MG");//提取合计金额
//把当前存量和最近单据日期 数量写入表格
set_ext_val()
};
if(grid_get_col_name("MG",grid_cur_col("MG"))=="res_id"||grid_get_col_name("MG",grid_cur_col("MG"))=="res_name")
{
//dbg(grid_get_v("MG","res_id",grid_cur_row("MG")))
view_app_obj("res",grid_get_v("MG","res_id",grid_cur_row("MG")));
};
//view_voucher(grid_get_v("MG","voucher_id",grid_cur_row("MG")));
//根据单据流水打开相关单据
//view_voucher_by_no(grid_get_v("MG","voucher_no",grid_cur_row("MG")));
//根据单据编号打开相关单据
grid_mark_sum("MG","std_amount");
grid_mark_sum("MG","sum_std_amount");
return 1;
}; |
