其实这个是有瑕疵的，只要勾选部门汇总或科目汇总即可查询，完全可以不要后面的部门选择框框和科目选择框框。这点需要跟meng总
体建议增加一个纯粹的逻辑勾选控件。
自定义报表分为两个部分：
第一部分是在配制管理->数据脚本里面建立一个扩展查询：
查询脚本如下：
int func_def_lmt()
{
dt_define('cycle','select cycle_id,cycle_id from asset_cycle');
def_lmt_dict('lmt_dict_id','折旧月份','cycle');
dt_define('dept','select max(a.dept_id) as deptid,b.dept_name from asset_dept_apart a left join app_dept b on a.dept_id = b.dept_id group by dept_name');
def_lmt_dict('lmt_dept_yesno','部门汇总','dept');
dt_define('subid','select max(a.subject_id) as subid,b.name from asset_dept_apart a left join evm_subject b on a.subject_id = b.subject_id group by name');
def_lmt_dict('lmt_subid_yesno','科目汇总','subid');
def_lmt_set_val('lmt_yes_no',"yes");
return 1;
};
int func_init_lmt_val()
{
return 1;
};
int func_def_field()
{
db_set_field_count(6);
db_set_field_info(0,'dict_id',"折旧月份",100);
db_set_field_info(1,'code',"折旧金额",80);
db_set_field_info(2,'dept_id',"所属部门编号",80);
db_set_field_info(3,'name',"所属部门",100);
db_set_field_info(4,'sub_id',"科目ID",100);
db_set_field_info(5,'sub_name',"科目名称",100);
return 1;
};
int func_def_imp()
{
return 1;
};
int func_init()
{
return 1;
};
int func_query()
{
string sql,sql_bydept,sql_normal,sql3,sql4,sql5,sql_special,logic;
sql_bydept="select a.cycle_id,sum(a.val_depre) as val,c.dept_id,d.dept_name,c.subject_id,e.name ";
sql_normal="select a.cycle_id,a.val_depre,c.dept_id,d.dept_name,c.subject_id,e.name ";
sql3="from ((asset_acc a left join asset_dept_apart c on c.asset_id=a.asset_id) ";
sql4="left join app_dept d on d.dept_id=c.dept_id) ";
sql5="left join evm_subject e on c.subject_id=e.subject_id ";
logic=lmt_checked("lmt_dict_id") +""+ lmt_checked("lmt_dept_yesno") +""+ lmt_checked("lmt_subid_yesno");
switch(logic)
{
case "000":
sql=sql_normal+sql3+sql4+sql5;
//msg("啥都没被勾选");
break;
case "100":
sql=sql_normal+sql3+sql4+sql5+" where a.cycle_id='" + lmt_val("lmt_dict_id") + "'";
//msg("折旧月份被勾选");
break;
case "010":
sql=sql_bydept+sql3+sql4+sql5+" group by a.cycle_id,c.dept_id,d.dept_name,c.subject_id,e.name";
//msg("部门汇总被勾选");
break;
case "001":
sql="select a.cycle_id,sum(a.val_depre) as val,'NULL','NULL',c.subject_id,e.name ";
sql=sql+"from ((asset_acc a left join asset_dept_apart c on c.asset_id=a.asset_id) ";
sql=sql+"left join app_dept d on d.dept_id=c.dept_id) ";
sql=sql+"left join evm_subject e on c.subject_id=e.subject_id ";
sql=sql+" group by a.cycle_id,c.dept_id,c.subject_id,e.name";
//msg("科目汇总被勾选");
break;
case "110":
sql=sql_bydept+sql3+sql4+sql5+" where a.cycle_id='" + lmt_val("lmt_dict_id") + "' group by a.cycle_id,c.dept_id,d.dept_name,c.subject_id,e.name";
//msg("折旧月份、部门汇总被勾选");
break;
case "011":
warn("请检查！部门汇总、科目汇总不能同时被勾选。");
break;
case "101":
sql="select a.cycle_id,sum(a.val_depre) as val,'NULL','NULL',c.subject_id,e.name ";
sql=sql+"from ((asset_acc a left join asset_dept_apart c on c.asset_id=a.asset_id) ";
sql=sql+"left join app_dept d on d.dept_id=c.dept_id) ";
sql=sql+"left join evm_subject e on c.subject_id=e.subject_id ";
sql=sql+" where a.cycle_id='" + lmt_val("lmt_dict_id") + "' group by a.cycle_id,c.dept_id,c.subject_id,e.name";
//msg("折旧月份、科目汇总被勾选");
break;
case "111":
warn("折旧月份、部门汇总、科目汇总被勾选。"+nl()+"请检查！部门汇总、科目汇总不能同时被勾选。");
break;
};
db_run(sql);
return 1;
};
第二部分是在固定资产报表里面的功能菜单-》报表分组脚本定义里面添加如下脚本：
int init()
{
rep_add_group("固定资产折旧");
// rep_add_rep("hello","Eba_007","客户报表1");
rep_add_csp_rep("固定资产折旧","ds_001","固定资产折旧报表");
return 1;
};
最终结果如图脚本还有很多优化的地方，还请高手们不吝赐教。 |
