脚本部分:
int func_def_lmt()
{
def_lmt_date('beg_date','起始日期');// 定义日期型提取条件def_lmt_date
lmt_set_must_checked('beg_date');// 设置参数默认必须选择lmt_set_must_checked
def_lmt_date('end_date','结束日期'); // 定义日期型提取条件def_lmt_date
lmt_set_must_checked('end_date'); // 设置参数默认必须选择lmt_set_must_checked
def_lmt_dict('sup','供应商','sup'); //定义字典型提取条件def_lmt_dict
return 1;
};
int func_init_lmt_val()
{
def_lmt_set_val('beg_date',get_cur_date_std());//定义参数的默认值def_lmt_set_val 起始日期'beg_date' 取当天日期标准格式get_cur_date_std()
def_lmt_set_val('end_date',get_cur_date_std());//定义参数的默认值def_lmt_set_val结束日期'end_date' 取当天日期标准格式get_cur_date_std()
//def_lmt_set_val('voucher_type','HC'); //定义参数的默认值def_lmt_set_val单据类型voucher_type
//def_lmt_set_val('edt','73'); //定义参数的默认值def_lmt_set_val 产品仓库'edt'
return 1;
};
int func_def_field()
{
db_set_field_count(16);// 设置字段数db_set_field_count
db_set_field_info(0,'供应商编号','供应商编号',60); //设置字段名称、标题、宽度db_set_field_info
db_set_field_info(1,'供应商名称','供应商名称',180); //设置字段名称、标题、宽度db_set_field_info
db_set_field_info(2,'期初_付款单','期初_付款单',80); //设置字段名称、标题、宽度db_set_field_info
db_set_field_info(3,'期初_结算单','期初_结算单',80); //设置字段名称、标题、宽度db_set_field_info
db_set_field_info(4,'期初_收到发票','期初_收到发票',80); //设置字段名称、标题、宽度db_set_field_info
db_set_field_info(5,'期初_货款','期初_货款',80); //设置字段名称、标题、宽度db_set_field_info
db_set_field_info(6,'期初_票款','期初_票款',80); //设置字段名称、标题、宽度db_set_field_info
db_set_field_info(7,'本期_采购订单','本期_采购订单',80); //设置字段名称、标题、宽度db_set_field_info
db_set_field_info(8,'本期_外协采购单','本期_外协采购单',80); //设置字段名称、标题、宽度db_set_field_info
db_set_field_info(9,'本期_外协加工单','本期_外协加工单',80); //设置字段名称、标题、宽度db_set_field_info
db_set_field_info(10,'订单合计','订单合计',80); //设置字段名称、标题、宽度db_set_field_info
db_set_field_info(11,'本期_付款单','本期_付款单',80); //设置字段名称、标题、宽度db_set_field_info
db_set_field_info(12,'本期_结算单','本期_结算单',80); //设置字段名称、标题、宽度db_set_field_info
db_set_field_info(13,'本期_收到发票','本期_收到发票',80); //设置字段名称、标题、宽度db_set_field_info
db_set_field_info(14,'期末_货款','期末_货款',80); //设置字段名称、标题、宽度db_set_field_info
db_set_field_info(15,'期末_票款','期末_票款',80); //设置字段名称、标题、宽度db_set_field_info
db_set_field_type(2,'I'); //设置字段类型db_set_field_type
db_set_field_type(3,'I'); //设置字段类型db_set_field_type
db_set_field_type(4,'I'); //设置字段类型db_set_field_type
db_set_field_type(5,'I'); //设置字段类型db_set_field_type
db_set_field_type(6,'I'); //设置字段类型db_set_field_type
db_set_field_type(7,'I'); //设置字段类型db_set_field_type
db_set_field_type(8,'I'); //设置字段类型db_set_field_type
db_set_field_type(9,'I'); //设置字段类型db_set_field_type
db_set_field_type(10,'I'); //设置字段类型db_set_field_type
db_set_field_type(11,'I'); //设置字段类型db_set_field_type
db_set_field_type(12,'I'); //设置字段类型db_set_field_type
db_set_field_type(13,'I'); //设置字段类型db_set_field_type
db_set_field_type(14,'I'); //设置字段类型db_set_field_type
db_set_field_type(15,'I'); //设置字段类型db_set_field_type
// db_set_field_sum_flag('num_beg'); 设置字段显示合计标志db_set_field_sum_flag
return 1;
};
int func_def_imp()
{
return 1;
};
int func_init()
{
return 1;
};
int func_query()
{
string sql,开始日期,结束日期,供应商;
供应商 = lmt_val("sup")
开始日期 = lmt_val("beg_date");
结束日期 = lmt_val("end_date");
sql=" SELECT 供应商编号, 供应商名称, 期初_付款单, 期初_结算单, 期初_收到发票, [期初_结算单]-[期初_付款单] AS 期初_货款,"
sql=sql+" 期初_收到发票-期初_付款单 AS 期初_票款,本期_采购订单, 本期_外协采购单, 本期_外协加工单, 本期_采购订单+本期_外协采购单+本期_外协加工单 AS 订单合计,"
sql=sql+" 本期_付款单, 本期_结算单, 本期_收到发票, [期初_货款]+[本期_结算单]-[本期_付款单] AS 期末_货款, [期初_票款]+[本期_收到发票]-[本期_付款单] AS 期末_票款"
sql=sql+" FROM ("
sql=sql+" SELECT [0_合并].供应商编号, sup.sup_name AS 供应商名称, Sum([0_合并].期初_付款单)/100 AS 期初_付款单, Sum([0_合并].期初_结算单)/100 AS 期初_结算单,"
sql=sql+" Sum([0_合并].期初_收到发票)/100 AS 期初_收到发票, Sum([0_合并].本期_采购订单)/100 AS 本期_采购订单,"
sql=sql+" Sum([0_合并].本期_外协采购单)/100 AS 本期_外协采购单, Sum([0_合并].本期_外协加工单)/100 AS 本期_外协加工单,"
sql=sql+" Sum([0_合并].本期_付款单)/100 AS 本期_付款单, Sum([0_合并].本期_结算单)/100 AS 本期_结算单, Sum([0_合并].本期_收到发票)/100 AS 本期_收到发票"
sql=sql+" FROM ("
sql=sql+" SELECT [0_合并].供应商编号,isnull('') AS 期初_付款单,isnull('') AS 期初_结算单,isnull('') AS 期初_收到发票,"
sql=sql+" Sum([0_合并].采购订单) AS 本期_采购订单, Sum([0_合并].外协采购单) AS 本期_外协采购单, Sum([0_合并].外协加工单) AS 本期_外协加工单,"
sql=sql+" Sum([0_合并].付款单) AS 本期_付款单, Sum([0_合并].结算单) AS 本期_结算单, Sum([0_合并].收到发票) AS 本期_收到发票"
sql=sql+" FROM ("
sql=sql+" SELECT ebs_v.voucher_date AS 单据日期, ebs_v.voucher_type AS 类型, app_voucher_type.voucher_name AS 单据类型, ebs_v.state AS 单据状态,"
sql=sql+" ebs_vr.eba_id AS 供应商编号, ebs_vr.discount_amount AS 采购订单,isnull('') AS 外协采购单 ,isnull('') AS 外协加工单,isnull('') AS 付款单,"
sql=sql+" isnull('') AS 结算单 ,isnull('') AS 收到发票"
sql=sql+" FROM ((ebs_v LEFT JOIN ebs_vr ON ebs_v.voucher_id = ebs_vr.voucher_id) LEFT JOIN app_voucher_type"
sql=sql+" ON ebs_v.voucher_type = app_voucher_type.voucher_type) LEFT JOIN ebm_mio ON ebs_v.voucher_id = ebm_mio.voucher_id GROUP BY ebs_v.voucher_date,"
sql=sql+" ebs_v.voucher_type, app_voucher_type.voucher_name, ebs_v.state, ebs_vr.eba_id, ebs_vr.discount_amount, ebm_mio.eba_id, ebm_mio.amount,"
sql=sql+" ebs_v.voucher_id, ebs_v.voucher_no HAVING (((ebs_v.voucher_type)='AA') AND ((ebs_v.state)='B' Or (ebs_v.state)='C')) "
sql=sql+" UNION ALL "
sql=sql+" SELECT ebs_v.voucher_date AS 单据日期, ebs_v.voucher_type AS 类型, app_voucher_type.voucher_name AS 单据类型, ebs_v.state AS 单据状态,"
sql=sql+" ebs_vr.eba_id AS 供应商编号, isnull('') AS 采购订单, ebs_vr.discount_amount AS 外协采购单, isnull('') AS 外协加工单, isnull('') AS 付款单,"
sql=sql+" isnull('') AS 结算单,isnull('') AS 收到发票"
sql=sql+" FROM ((ebs_v LEFT JOIN ebs_vr ON ebs_v.voucher_id = ebs_vr.voucher_id)"
sql=sql+" LEFT JOIN app_voucher_type ON ebs_v.voucher_type = app_voucher_type.voucher_type)"
sql=sql+" LEFT JOIN ebm_mio ON ebs_v.voucher_id = ebm_mio.voucher_id"
sql=sql+" GROUP BY ebs_v.voucher_date, ebs_v.voucher_type, app_voucher_type.voucher_name, ebs_v.state, ebs_vr.eba_id, ebs_vr.discount_amount,"
sql=sql+" ebm_mio.eba_id, ebm_mio.amount, ebs_v.voucher_id, ebs_v.voucher_no"
sql=sql+" HAVING (((ebs_v.voucher_type)='HN') AND ((ebs_v.state)='B' Or (ebs_v.state)='C')) "
sql=sql+" UNION ALL "
sql=sql+" SELECT ebs_v.voucher_date AS 单据日期, ebs_v.voucher_type AS 类型, app_voucher_type.voucher_name AS 单据类型, ebs_v.state AS 单据状态,"
sql=sql+" ebs_vr.eba_id AS 供应商编号, isnull('') AS 采购订单, isnull('') AS 外协采购单, ebs_vr_item.total_amount AS 外协加工单 ,isnull('') AS 付款单,"
sql=sql+" isnull('') AS 结算单 ,isnull('') AS 收到发票"
sql=sql+" FROM (((ebs_v LEFT JOIN ebs_vr ON ebs_v.voucher_id = ebs_vr.voucher_id) LEFT JOIN ebs_vr_item ON ebs_v.voucher_id = ebs_vr_item.voucher_id)"
sql=sql+" LEFT JOIN app_voucher_type ON ebs_v.voucher_type = app_voucher_type.voucher_type)"
sql=sql+" LEFT JOIN res ON ebs_vr_item.res_id = res.res_id"
sql=sql+" WHERE (((ebs_v.voucher_type)='HO') AND ((ebs_v.state)='B' Or (ebs_v.state)='C') AND ((res.res_kind)='40')) "
sql=sql+" UNION ALL "
sql=sql+" SELECT ebs_v.voucher_date AS 单据日期, ebs_v.voucher_type AS 类型, app_voucher_type.voucher_name AS 单据类型, ebs_v.state AS 单据状态,"
sql=sql+" ebm_mio.eba_id AS 供应商编号, isnull('') AS 采购订单,isnull('') AS 外协采购单, isnull('') AS 外协加工单, ebm_mio.amount AS 付款单,"
sql=sql+" isnull('') AS 结算单 ,isnull('') AS 收到发票"
sql=sql+" FROM ((ebs_v LEFT JOIN ebs_vr ON ebs_v.voucher_id = ebs_vr.voucher_id)"
sql=sql+" LEFT JOIN app_voucher_type ON ebs_v.voucher_type = app_voucher_type.voucher_type)"
sql=sql+" LEFT JOIN ebm_mio ON ebs_v.voucher_id = ebm_mio.voucher_id GROUP BY ebs_v.voucher_date, ebs_v.voucher_type, app_voucher_type.voucher_name,"
sql=sql+" ebs_v.state, ebs_vr.eba_id, ebm_mio.eba_id, ebm_mio.amount, ebs_v.voucher_id, ebs_v.voucher_no"
sql=sql+" HAVING (((ebs_v.voucher_type)='DA') AND ((ebs_v.state)='B' Or (ebs_v.state)='C')) "
sql=sql+" UNION ALL "
sql=sql+" SELECT ebs_v.voucher_date AS 单据日期, ebs_v.voucher_type AS 类型, app_voucher_type.voucher_name AS 单据类型, ebs_v.state AS 单据状态,"
sql=sql+" ebs_vr.eba_id AS 供应商编号, isnull('') AS 采购订单,isnull('') AS 外协采购单, isnull('') AS 外协加工单, isnull('') AS 付款单,"
sql=sql+" ebs_vr.discount_amount AS 结算单 ,isnull('') AS 收到发票"
sql=sql+" FROM ((ebs_v LEFT JOIN ebs_vr ON ebs_v.voucher_id = ebs_vr.voucher_id)"
sql=sql+" LEFT JOIN app_voucher_type ON ebs_v.voucher_type = app_voucher_type.voucher_type)"
sql=sql+" LEFT JOIN ebm_mio ON ebs_v.voucher_id = ebm_mio.voucher_id"
sql=sql+" GROUP BY ebs_v.voucher_date, ebs_v.voucher_type, app_voucher_type.voucher_name, ebs_v.state, ebs_vr.eba_id, ebs_vr.discount_amount,"
sql=sql+" ebm_mio.eba_id, ebm_mio.amount, ebs_v.voucher_id, ebs_v.voucher_no"
sql=sql+" HAVING (((ebs_v.voucher_type)='HV') AND ((ebs_v.state)='B' Or (ebs_v.state)='C')) "
sql=sql+" UNION ALL"
sql=sql+" SELECT ebs_v.voucher_date AS 单据日期, ebs_v.voucher_type AS 类型, app_voucher_type.voucher_name AS 单据类型, ebs_v.state AS 单据状态,"
sql=sql+" ebs_vr.eba_id AS 供应商编号, isnull('') AS 采购订单,isnull('') AS外协采购单, isnull('') AS 外协加工单, isnull('') AS 付款单,"
sql=sql+" isnull('') AS 结算单 ,ebs_vr.discount_amount AS 收到发票"
sql=sql+" FROM ((ebs_v LEFT JOIN ebs_vr ON ebs_v.voucher_id = ebs_vr.voucher_id)"
sql=sql+" LEFT JOIN app_voucher_type ON ebs_v.voucher_type = app_voucher_type.voucher_type)"
sql=sql+" LEFT JOIN ebm_mio ON ebs_v.voucher_id = ebm_mio.voucher_id"
sql=sql+" GROUP BY ebs_v.voucher_date, ebs_v.voucher_type, app_voucher_type.voucher_name, ebs_v.state, ebs_vr.eba_id, ebs_vr.discount_amount,"
sql=sql+" ebm_mio.eba_id, ebm_mio.amount, ebs_v.voucher_id, ebs_v.voucher_no"
sql=sql+" HAVING (((ebs_v.voucher_type)='AC') AND ((ebs_v.state)='B' Or (ebs_v.state)='C'))"
sql=sql+" ) AS 0_合并"
sql=sql+" WHERE ((([0_合并].单据日期)>='"+开始日期+"' And ([0_合并].单据日期)<='"+结束日期+"'))"
sql=sql+" GROUP BY [0_合并].供应商编号"
sql=sql+" UNION ALL"
sql=sql+" SELECT [0_合并].供应商编号,Sum([0_合并].付款单) AS 期初_付款单,Sum([0_合并].结算单) AS 期初_结算单,Sum([0_合并].收到发票)AS 期初_收到发票,"
sql=sql+" isnull('') AS 本期_采购订单, isnull('') AS 本期_外协采购单, isnull('') AS 本期_外协加工单, isnull('') AS 本期_付款单,"
sql=sql+" isnull('') AS 本期_结算单, isnull('') AS 本期_收到发票"
sql=sql+" FROM ("
sql=sql+" SELECT ebs_v.voucher_date AS 单据日期, ebs_v.voucher_type AS 类型, app_voucher_type.voucher_name AS 单据类型, ebs_v.state AS 单据状态,"
sql=sql+" ebs_vr.eba_id AS 供应商编号, ebs_vr.discount_amount AS 采购订单,isnull('') AS 外协采购单 ,isnull('') AS 外协加工单,isnull('') AS 付款单,"
sql=sql+" isnull('') AS 结算单 ,isnull('') AS 收到发票"
sql=sql+" FROM ((ebs_v LEFT JOIN ebs_vr ON ebs_v.voucher_id = ebs_vr.voucher_id)"
sql=sql+" LEFT JOIN app_voucher_type ON ebs_v.voucher_type = app_voucher_type.voucher_type)"
sql=sql+" LEFT JOIN ebm_mio ON ebs_v.voucher_id = ebm_mio.voucher_id"
sql=sql+" GROUP BY ebs_v.voucher_date, ebs_v.voucher_type, app_voucher_type.voucher_name, ebs_v.state, ebs_vr.eba_id, ebs_vr.discount_amount,"
sql=sql+" ebm_mio.eba_id, ebm_mio.amount, ebs_v.voucher_id, ebs_v.voucher_no"
sql=sql+" HAVING (((ebs_v.voucher_type)='AA') AND ((ebs_v.state)='B' Or (ebs_v.state)='C')) "
sql=sql+" UNION ALL "
sql=sql+" SELECT ebs_v.voucher_date AS 单据日期, ebs_v.voucher_type AS 类型, app_voucher_type.voucher_name AS 单据类型, ebs_v.state AS 单据状态,"
sql=sql+" ebs_vr.eba_id AS 供应商编号, isnull('') AS 采购订单, ebs_vr.discount_amount AS 外协采购单, isnull('') AS 外协加工单, isnull('') AS 付款单,"
sql=sql+" isnull('') AS 结算单,isnull('') AS 收到发票"
sql=sql+" FROM ((ebs_v LEFT JOIN ebs_vr ON ebs_v.voucher_id = ebs_vr.voucher_id)"
sql=sql+" LEFT JOIN app_voucher_type ON ebs_v.voucher_type = app_voucher_type.voucher_type)"
sql=sql+" LEFT JOIN ebm_mio ON ebs_v.voucher_id = ebm_mio.voucher_id"
sql=sql+" GROUP BY ebs_v.voucher_date, ebs_v.voucher_type, app_voucher_type.voucher_name, ebs_v.state, ebs_vr.eba_id, ebs_vr.discount_amount,"
sql=sql+" ebm_mio.eba_id, ebm_mio.amount, ebs_v.voucher_id, ebs_v.voucher_no"
sql=sql+" HAVING (((ebs_v.voucher_type)='HN') AND ((ebs_v.state)='B' Or (ebs_v.state)='C')) "
sql=sql+" UNION ALL "
sql=sql+" SELECT ebs_v.voucher_date AS 单据日期, ebs_v.voucher_type AS 类型, app_voucher_type.voucher_name AS 单据类型, ebs_v.state AS 单据状态,"
sql=sql+" ebs_vr.eba_id AS 供应商编号, isnull('') AS 采购订单, isnull('') AS 外协采购单, ebs_vr_item.total_amount AS 外协加工单 ,"
sql=sql+" isnull('') AS 付款单, isnull('') AS 结算单 ,isnull('') AS 收到发票"
sql=sql+" FROM (((ebs_v LEFT JOIN ebs_vr ON ebs_v.voucher_id = ebs_vr.voucher_id) LEFT JOIN ebs_vr_item ON ebs_v.voucher_id = ebs_vr_item.voucher_id)"
sql=sql+" LEFT JOIN app_voucher_type ON ebs_v.voucher_type = app_voucher_type.voucher_type)"
sql=sql+" LEFT JOIN res ON ebs_vr_item.res_id = res.res_id"
sql=sql+" WHERE (((ebs_v.voucher_type)='HO') AND ((ebs_v.state)='B' Or (ebs_v.state)='C') AND ((res.res_kind)='40')) "
sql=sql+" UNION ALL "
sql=sql+" SELECT ebs_v.voucher_date AS 单据日期, ebs_v.voucher_type AS 类型, app_voucher_type.voucher_name AS 单据类型, ebs_v.state AS 单据状态,"
sql=sql+" ebm_mio.eba_id AS 供应商编号, isnull('') AS 采购订单,isnull('') AS 外协采购单, isnull('') AS 外协加工单, ebm_mio.amount AS 付款单,"
sql=sql+" isnull('') AS 结算单 ,isnull('') AS 收到发票"
sql=sql+" FROM ((ebs_v LEFT JOIN ebs_vr ON ebs_v.voucher_id = ebs_vr.voucher_id)"
sql=sql+" LEFT JOIN app_voucher_type ON ebs_v.voucher_type = app_voucher_type.voucher_type)"
sql=sql+" LEFT JOIN ebm_mio ON ebs_v.voucher_id = ebm_mio.voucher_id"
sql=sql+" GROUP BY ebs_v.voucher_date, ebs_v.voucher_type, app_voucher_type.voucher_name, ebs_v.state, ebs_vr.eba_id, ebm_mio.eba_id,"
sql=sql+" ebm_mio.amount, ebs_v.voucher_id, ebs_v.voucher_no HAVING (((ebs_v.voucher_type)='DA') AND ((ebs_v.state)='B' Or (ebs_v.state)='C')) "
sql=sql+" UNION ALL "
sql=sql+" SELECT ebs_v.voucher_date AS 单据日期, ebs_v.voucher_type AS 类型, app_voucher_type.voucher_name AS 单据类型, ebs_v.state AS 单据状态,"
sql=sql+" ebs_vr.eba_id AS 供应商编号, isnull('') AS 采购订单,isnull('') AS 外协采购单, isnull('') AS 外协加工单, isnull('') AS 付款单,"
sql=sql+" ebs_vr.discount_amount AS 结算单 ,isnull('') AS 收到发票"
sql=sql+" FROM ((ebs_v LEFT JOIN ebs_vr ON ebs_v.voucher_id = ebs_vr.voucher_id)"
sql=sql+" LEFT JOIN app_voucher_type ON ebs_v.voucher_type = app_voucher_type.voucher_type)"
sql=sql+" LEFT JOIN ebm_mio ON ebs_v.voucher_id = ebm_mio.voucher_id"
sql=sql+" GROUP BY ebs_v.voucher_date, ebs_v.voucher_type, app_voucher_type.voucher_name, ebs_v.state, ebs_vr.eba_id, ebs_vr.discount_amount,"
sql=sql+" ebm_mio.eba_id, ebm_mio.amount, ebs_v.voucher_id, ebs_v.voucher_no"
sql=sql+" HAVING (((ebs_v.voucher_type)='HV') AND ((ebs_v.state)='B' Or (ebs_v.state)='C')) "
sql=sql+" UNION ALL SELECT ebs_v.voucher_date AS 单据日期, ebs_v.voucher_type AS 类型, app_voucher_type.voucher_name AS 单据类型,"
sql=sql+" ebs_v.state AS 单据状态, ebs_vr.eba_id AS 供应商编号, isnull('') AS 采购订单,isnull('') AS 外协采购单, isnull('') AS 外协加工单,"
sql=sql+" isnull('') AS 付款单, isnull('') AS 结算单 ,ebs_vr.discount_amount AS 收到发票"
sql=sql+" FROM ((ebs_v LEFT JOIN ebs_vr ON ebs_v.voucher_id = ebs_vr.voucher_id)"
sql=sql+" LEFT JOIN app_voucher_type ON ebs_v.voucher_type = app_voucher_type.voucher_type)"
sql=sql+" LEFT JOIN ebm_mio ON ebs_v.voucher_id = ebm_mio.voucher_id GROUP BY ebs_v.voucher_date, ebs_v.voucher_type, app_voucher_type.voucher_name,"
sql=sql+" ebs_v.state, ebs_vr.eba_id, ebs_vr.discount_amount, ebm_mio.eba_id, ebm_mio.amount, ebs_v.voucher_id, ebs_v.voucher_no"
sql=sql+" HAVING (((ebs_v.voucher_type)='AC') AND ((ebs_v.state)='B' Or (ebs_v.state)='C'))"
sql=sql+" )as 0_合并"
sql=sql+" WHERE [0_合并].单据日期<'"+开始日期+"'"
sql=sql+" GROUP BY [0_合并].供应商编号"
sql=sql+" ) AS 0_合并 LEFT JOIN sup ON [0_合并].供应商编号 = sup.sup_id"
sql=sql+" GROUP BY [0_合并].供应商编号, sup.sup_name"
if( lmt_checked("sup"))
{
sql=sql+" HAVING 供应商编号='"+供应商+"'"
}
sql=sql+" ) AS [ls_Alias]"
db_run(sql)
return 1;
}; |
