int row_number_id(int select_area_id,string sort_ext,string item_row_ext) //数据区分组标识行号：参数：目标数据编号,排序列集合,分组列集合(注意最3列数据分组)
{
//选择目标数据编号
db_select_area(select_area_id)
//对数据排序
db_sort_ext(sort_ext);
//把排序列列名提取出来,去了排序字段和空白内容
string field_ext;
field_ext=str_replace(item_row_ext,"desc","");
field_ext=str_replace(field_ext," ","");
//dbg(field_ext);
//解析列串,写入3个具体列名
string field_id_1,field_id_2,field_id_3;
if(str_pos(field_ext,",")==-1){
field_id_1=field_ext;
}else{
field_id_1=str_get_sub_n(field_ext,0,str_pos(field_ext,","));
field_ext=str_replace(field_ext,field_id_1+",","");
}
//dbg(field_id_1+" "+field_ext)
if(str_pos(field_ext,",")==-1){
field_id_2=field_ext;
}else{
field_id_2=str_get_sub_n(field_ext,0,str_pos(field_ext,","));
field_ext=str_replace(field_ext,field_id_2+",","");
}
//dbg(field_id_2+" "+field_ext)
if(str_pos(field_ext,",")==-1){
field_id_3=field_ext;
}else{
field_id_3=str_get_sub_n(field_ext,0,str_pos(field_ext,","));
field_ext=str_replace(field_ext,field_id_3+",","");
}
//dbg(field_id_3+" "+field_ext)
//增加一列 整数型行号
db_add_field("row_id");
db_set_field_type_s("row_id","I");
//开始根据分组列 标识行号
string field_id_val1,field_id_val2,field_id_val3; //列名值
int row_id_add;
row_id_add=1;
int n,id;
n = db_row_count(); // SQL检索出报表 提取报表行数
for(id=1;id<=n;id++) {
if(id==1){
field_id_val1=db_res_ext(field_id_1);
field_id_val2=db_res_ext(field_id_2);
field_id_val3=db_res_ext(field_id_3);
db_set_value_ext("row_id",row_id_add);
row_id_add++;
}else{
if(field_id_val1==db_res_ext(field_id_1)&&field_id_val2==db_res_ext(field_id_2)&&field_id_val3==db_res_ext(field_id_3)){
db_set_value_ext("row_id",row_id_add);
row_id_add++
}else{
//dbg(field_id_val1+"*"+db_res_ext(field_id_1)+" "+field_id_val2+"*"+db_res_ext(field_id_2)+" "+field_id_val3+"*"+db_res_ext(field_id_3))
row_id_add=1;
field_id_val1=db_res_ext(field_id_1);
field_id_val2=db_res_ext(field_id_2);
field_id_val3=db_res_ext(field_id_3);
db_set_value_ext("row_id",row_id_add);
row_id_add++;
};
};
db_next();
};
return 1;
};
//提取数据区分组标识行号：参数：目标数据编号,开始编号,结束编号
int extract_row_number_id(int select_area_id,int beg_id,int end_id)
{
//选择目标数据编号
db_select_area(select_area_id)
db_go_head();
int n,id;
n = db_row_count(); // SQL检索出报表 提取报表行数
for(id=1;id<=n;id++)
{
int row_id;
row_id=db_res_ext("row_id");
if(row_id>=beg_id&&row_id<=end_id){
db_next();
}else{
db_del_row();
};
};
return 1;
};
//按列汇总-汇总为字符串拼接 根据实际情况修改内容
int db_subtotal_one_str(string db_subname,string db_totalname)
{
//数据区分类汇总-单列分类,单列汇总
db_sort_ext(db_subname+" desc");
//db_show();
db_go_head();
int ext_db_row_id;
string ext_db_subname;
string ext_db_totalname,ext_total;
ext_db_subname=db_res_ext(db_subname);
ext_db_totalname="";
ext_total="";
ext_db_row_id=db_row_id();
db_next();
//当前行号,分类名字,汇总数量
int n; //定义整数 报表行数
int id; //定义整数 循环条件初始化数
n = db_row_count(); // SQL检索出报表 提取报表行数
for(id=1;id<n;id++)
{
if(ext_db_subname==db_res_ext(db_subname))
{
ext_db_totalname=db_res_ext(db_totalname);
db_del_row();
db_go_row(ext_db_row_id);
ext_total=db_res_ext(db_totalname);
db_set_value_ext(db_totalname,ext_db_totalname+nl()+ext_total);
}
else
{
ext_db_subname=db_res_ext(db_subname);
ext_db_totalname="";
ext_total="";
ext_db_row_id=db_row_id();
};
db_next();
};
//db_show();
return 1;
};
//发信息给操作员
int msg_user_tip()
{
db_select_area(0);
string eba_id; //客户编号
string msg_user; //操作员
string msg_info; //发信息备注
string sell_duration //间隔天数
string tip_val;
//把符合条件的记录 进入下步----发信息给哪个操作员--客户资料里业务员对应的操作员
//插入提醒列表
db_go_head();
int n,id;
n = db_row_count(); // SQL检索出报表 提取报表行数
for(id=1;id<=n;id++)
{
//dbg(id)
eba_id=db_res_ext("eba_id");
msg_user=db_res_ext("msg_user");
msg_info=db_res_ext("msg_info");
sell_duration=db_res_ext("sell_duration");
tip_val=db_res_ext("tip_val");
db_select_area(1);
//插入客户联系列表-------------------------由客户联系模块来提醒
int touch_id; //提取序号
//touch_id=db_get_sequence("seq_sys_id");
string sql;
sql="update app_sequence set seq_val=seq_val+1 where seq_name='seq_sys_id'";
db_run(sql);
db_run("commit");
sql="select seq_val from app_sequence where seq_name='seq_sys_id'";
db_run(sql);
touch_id=db_res_ext("seq_val");
db_select_area(3);
string sql;
sql="delete from crm_touch where touch_id="+touch_id;
//dbg(sql)
db_run(sql);
sql="insert into crm_touch"
sql+="(touch_id,eba_id,linkman_id,linkman,title,note_info,touch_purpose,touch_type,"
sql+="touch_date,touch_time,plan_touch_date,state,create_date,create_user_id,dept_id,emp_id,service_id,user_id) "
sql+="values("+touch_id+",'"+eba_id+"','','','客户超出销售间隔','"+tip_val+"','A','A',"
sql+="'"+get_next_date(get_cur_date_std())+"','080000','"+get_next_date(get_cur_date_std())+"','A','"+get_cur_date_std()+"','admin','','','','"+msg_user+"')"
//dbg(sql)
db_run(sql);
db_select_area(1);
int vou_id; //提取序号
//vou_id=db_get_sequence("seq_remind_obj");
string sql;
sql="update app_sequence set seq_val=seq_val+1 where seq_name='seq_remind_obj'";
db_run(sql);
db_run("commit");
sql="select seq_val from app_sequence where seq_name='seq_remind_obj'";
db_run(sql);
vou_id=db_res_ext("seq_val");
//dbg("vou_id: "+vou_id)
db_select_area(3);
//插入提醒列表
string sql;
sql="insert into ut_remind_mup_obj_list (obj_id,oper_type,key_id,ext_info,user_id,log_date,log_time,msg_obj_info,vou_id) "
sql+="values ('eba','A01','"+eba_id+"','"+dt_get_name("eba",eba_id)+"','"+msg_user+"','"
sql+=get_cur_date_std()+"','"+get_cur_time_std()+"','"+msg_info+"',"+vou_id+")"
//dbg(sql)
db_run(sql);
db_select_area(0);
db_next();
};
/*
//按操作员进行提示信息汇总-------------------------由系统内部提示模块
db_subtotal_one_str("msg_user","tip_val");
//db_show()
//发信息给操作员
db_select_area(0);
db_go_head();
int n,id;
n = db_row_count(); // SQL检索出报表 提取报表行数
for(id=1;id<=n;id++)
{
//发信息
tip_val=db_res_ext("tip_val");
send_msg_and_obj(db_res_ext("msg_user"),tip_val,"eba",db_res_ext("eba_id"));
db_next();
};
*/
return 1;
};
int main()
{
file_write(get_home_path()+'\客户销售超期提醒日志.txt',nl()+get_cur_date()+" "+get_cur_time()+" 客户销售超期提醒开始：--->");
/*
//app_sequence 表里增加 seq_remind_obj ,提醒列表里序号取这个字段
insert into app_sequence(seq_name,seq_val) values('seq_remind_obj',0)
select * from app_sequence
//把客户对应 营销区域定义默认业务员->操作员里对应业务员
string dt_sql;
dt_sql="select a.eba_id,a.emp_id as eba_emp_id,b.emp_id as service_emp_id,c.user_id "
dt_sql+="from eba a,eba_service b,mup_user c where a.service_id=b.service_id and b.emp_id=c.emp_id "
*/
//把客户对应 营销区域定义默认业务员生成字典
db_select_area(1);
string dt_sql;
dt_sql="select a.eba_id,b.emp_id,a.service_id,a.emp_id as eba_emp_id,b.emp_id as service_emp_id from eba a,eba_service b where a.service_id=b.service_id "
dt_define("my_eba_emp",dt_sql);
//把营销区域定义默认业务员生成字典
db_select_area(2);
dt_sql="select emp_id,user_id from mup_user where emp_id<>'' "
dt_define("my_user_emp",dt_sql);
//按客户分类：提醒和单据日期取最近一次记录
db_select_area(0);
string sql_max_date;
sql_max_date="select f.key_id,max(f.log_date) as log_date from ("
//提取当前日期之前的最近提醒记录 ：A01 销售订单间隔日期提醒
sql_max_date+="select a.key_id,max(a.log_date) as log_date from ut_remind_mup_obj_list a "
sql_max_date+="where a.obj_id='eba' and a.oper_type='A01' and a.log_date<=convert(varchar(8),getdate(),112) "
sql_max_date+="group by a.key_id "
sql_max_date+="UNION ALL "
//提取当前日期之前的最近单据日期
sql_max_date+="select b.eba_id,max(a.voucher_date) as voucher_date from (ebs_v a inner join ebs_vr b on a.voucher_id=b.voucher_id) "
sql_max_date+="where a.voucher_type='BA' and a.state in('B','C') and a.voucher_date<=convert(varchar(8),getdate(),112) "
sql_max_date+="group by b.eba_id "
sql_max_date+=") as f "
sql_max_date+="group by key_id "
//dbg(sql_max_date)
//客户间隔日期计算出 最早的提醒或单据日期 比如 当前为20221001 间隔天数为：10 最早日期为：20220901. 只要小于0901号的记录就要提醒了。
string sql;
sql="select eba_id,eba_grade,sell_duration,duration_day_date,"
sql+="key_id,isnull(log_date,'') as log_date,'' as msg_user,'' as msg_info,'' as tip_val from "
sql+="(select eba_id,eba_grade,sell_duration,"
sql+="convert(varchar(8),getdate(),112) as day_date,convert(varchar(8),dateadd(DAY,-1*sell_duration,getdate()),112) as duration_day_date "
sql+="from eba where sell_duration>0 and state in ('A','B','C')) as y " //客户状态限制一下sql+="left join "
sql+="("+sql_max_date+") as x "
sql+="on y.eba_id=x.key_id "
sql+="where y.duration_day_date> isnull(log_date,'') "
//dbg(sql)
db_run(sql);
file_write(get_home_path()+'\客户销售超期提醒日志.txt'," "+nl()+" "+sql+" "+nl()+" ");
//把符合条件的记录 进入下步----发信息给哪个操作员--客户资料里业务员对应的操作员
db_go_head();
int n,id;
n = db_row_count(); // SQL检索出报表 提取报表行数
file_write(get_home_path()+'\客户销售超期提醒日志.txt'," "+nl()+" "+n+" "+nl()+" ");
for(id=1;id<=n;id++)
{
string eba_id;
eba_id=db_res_ext("eba_id");
string eba_emp; //客户营销区域里业务员编号
eba_emp=dt_get_name("my_eba_emp",eba_id);
string user_emp; //操作员里对应业务员
user_emp=dt_get_name("my_user_emp",eba_emp);
//dbg(eba_emp+" * "+user_emp);
//有对应操作员写入编号,否则就删除当前记录
if(user_emp!="")
{
//客户级别如果是空白,就写入z最低级别
if(db_res_ext("eba_grade")=="")
db_set_value_ext("eba_grade","Z");
db_set_value_ext("msg_user",user_emp);
db_set_value_ext("msg_info",eba_emp+"->"+user_emp);
db_set_value_ext("tip_val",eba_id+" 客户:"+dt_get_name("eba",eba_id)+" 超出销售间隔 "+db_res_ext("sell_duration")+" 天!"); //通知信息内容
file_write(get_home_path()+'\客户销售超期提醒日志.txt'," "+nl()+" "+id+" "+nl()+" "+eba_id+" 客户:"+dt_get_name("eba",eba_id)+" 超出销售间隔 "+db_res_ext("sell_duration")+" 天!"+" "+eba_emp+"->"+user_emp);
db_next();
}
else
{
db_del_row();
};
};
db_sort_ext('msg_user,eba_grade,sell_duration,key_id'); //按操作员，客户级别，间隔天数，客户编号 排序
//db_show();
//一个操作员负责客户：一次只提醒20个记录太多了 也跟进不了。
//实例：把数据区表内按产品编号排序和按产品编号分组给序号
row_number_id(0,'msg_user,eba_grade,sell_duration,key_id','msg_user');
//数据区分组标识行号：参数：目标数据编号,开始编号,结束编号
extract_row_number_id(0,1,20)
//db_show();
//发信息给操作员-一行一个提醒信息
msg_user_tip();
file_write(get_home_path()+'\客户销售超期提醒日志.txt',get_cur_date()+" "+get_cur_time()+" 客户销售超期提醒结束");
return 1;
}; |
