$jxtbsql="select * from jx_tbprofile where id=".$li;
$jxtblist =$db-> query($jxtbsql);
foreach($jxtblist as $key=>$li1){ if($li1[execlevel]==0){ //0阶段 $list; $success=0;//完成笔数
//先判断目的字段配置是否有误 $strtab="select * from jx_tbprofile_dtl where tableno='".$li1[tableno]."'"; //检索字段名称 $listtab=$db->query($strtab); $strcol="show columns from ".$li1[desttable]; //获取目的表字段名称 $listcol=$db->query($strcol);
在这3张表之间转换数据的jx_zy_detail_charge_belong,jx_zy_detail_charge_force,jx_zy_detail_charge这是跑的第一条查询sql:jx_zy_detail_charge_belong->jx_zy_detail_charge_force SELECT jx_zy_detail_charge_belong.data_month, jx_zy_detail_charge_belong.inpatient_no, jx_zy_detail_charge_belong.charge_date, jx_zy_detail_charge_belong.op_id_code, jx_zy_detail_charge_belong.charge_code, jx_zy_detail_charge_belong.charge_fee, jx_zy_detail_charge_belong.percentage, jx_zy_detail_charge_belong.ward_code, jx_zy_detail_charge_belong.dept_code, jx_zy_detail_charge_belong.order_no, jx_zy_detail_charge_belong.exec_unit, jx_zy_detail_charge_belong.retprice, jx_zy_detail_charge_belong.charge_amount, jx_zy_detail_charge_belong.serial, jx_zy_detail_charge_belong.group_no, jx_zy_detail_charge_belong.bill_code, jx_zy_detail_charge_belong.key_col, jx_zy_detail_charge_belong.pat_name FROM jx_zy_detail_charge_belong ,jx_mz_charge_item WHERE jx_zy_detail_charge_belong.data_month = '|datamonth|' and jx_zy_detail_charge_belong.charge_code =jx_mz_charge_item.fee_code and (jx_mz_charge_item.exec_unit is null or jx_mz_charge_item.exec_unit =' ') union all SELECT jx_zy_detail_charge_belong.data_month, jx_zy_detail_charge_belong.inpatient_no, jx_zy_detail_charge_belong.charge_date, jx_zy_detail_charge_belong.op_id_code, jx_zy_detail_charge_belong.charge_code, jx_zy_detail_charge_belong.charge_fee, jx_zy_detail_charge_belong.percentage, jx_zy_detail_charge_belong.ward_code, jx_zy_detail_charge_belong.dept_code, jx_zy_detail_charge_belong.order_no, jx_mz_charge_item.exec_unit, jx_zy_detail_charge_belong.retprice, jx_zy_detail_charge_belong.charge_amount, jx_zy_detail_charge_belong.serial, jx_zy_detail_charge_belong.group_no, jx_zy_detail_charge_belong.bill_code, jx_zy_detail_charge_belong.key_col, jx_zy_detail_charge_belong.pat_name
FROM jx_zy_detail_charge_belong ,jx_mz_charge_item WHERE jx_zy_detail_charge_belong.data_month = '|datamonth|' and jx_zy_detail_charge_belong.charge_code= jx_mz_charge_item.fee_code and jx_mz_charge_item.exec_unit is not null and jx_mz_charge_item.exec_unit <>' ' 就是从一个配置表,jx_mz_charge_item 中找到本表charge_code和fee_code相同,找到配置表的exec_unit符合条件就用这个值替换原表的科室值第二条转换查询sql:jx_zy_detail_charge_force-》jx_zy_detail_charge select data_month,inpatient_no,charge_date,op_id_code,charge_code,charge_fee,dept_code,exec_unit,retprice,charge_amount,serial,bill_code,pat_name from jx_zy_detail_charge_force where jx_zy_detail_charge_force.exec_unit not in (select description from jx_code_ctl where item_no like 'ordept%') and data_month='|datamonth|' union all select data_month,inpatient_no,charge_date,op_id_code,charge_code,charge_fee,dept_code,exec_unit,retprice,charge_amount,serial,bill_code,pat_name from jx_zy_detail_charge_force where jx_zy_detail_charge_force.exec_unit in (select description from jx_code_ctl where item_no like 'ordept%') and not ( jx_zy_detail_charge_force.bill_code in (select description from jx_code_ctl where item_no like 'orbill%') or jx_zy_detail_charge_force.bill_code in (select description from jx_code_ctl where item_no like 'zlbill%') ) and data_month='|datamonth|' union all select data_month,inpatient_no,charge_date,op_id_code,charge_code,charge_fee,dept_code,dept_code,retprice,charge_amount,serial,bill_code,pat_name from jx_zy_detail_charge_force where jx_zy_detail_charge_force.exec_unit in (select description from jx_code_ctl where item_no like 'ordept%') and ( jx_zy_detail_charge_force.bill_code in (select description from jx_code_ctl where item_no like 'orbill%') or jx_zy_detail_charge_force.bill_code in (select description from jx_code_ctl where item_no like 'zlbill%') ) and data_month='|datamonth|' union all select data_month,inpatient_no,charge_date,op_id_code,charge_code,charge_fee,dept_code,dept_code,retprice,charge_amount,serial,bill_code,pat_name from jx_zy_detail_charge_force where jx_zy_detail_charge_force.exec_unit is null and data_month='|datamonth|'将执行科室exec_unit字段在表jx_code_ctl中配置的科室,并且收费大类bill_code字段也在表jx_code_ctl中配置的大类中的数据的执行科室exec_unit改为开单科室dept_code
function foreverselect(){
$db = Db::getInstance();
//根据编码查询数据
$id=$_REQUEST['id']; //id
$datamonth=$_REQUEST['datamonth']; //日期
$ids=explode(',',$id); //截取
$jxtblist="";
$isok="yes"; //控制进度条标识
$datalist;//返回提示信息
$datalist1;
if(!empty($id)){
foreach($ids as $key=>$li){
$jxtbsql="select * from jx_tbprofile where id=".$li;
$jxtblist =$db-> query($jxtbsql);
foreach($jxtblist as $key=>$li1){
if($li1[execlevel]==0){ //0阶段
$list;
$success=0;//完成笔数
//先判断目的字段配置是否有误
$strtab="select * from jx_tbprofile_dtl where tableno='".$li1[tableno]."'"; //检索字段名称
$listtab=$db->query($strtab);
$strcol="show columns from ".$li1[desttable]; //获取目的表字段名称
$listcol=$db->query($strcol);
$ziduan;
$boole=false;
foreach ($listtab as $keytab=>$litab) {//配置目的表字段
foreach ($listcol as $keycol=>$licol)//目的表字段
{
if($litab[dest_column]==$licol['Field']){
$boole=true; //两个名称相互比较,如果遇到相同的给$boole赋值true。
//echo "相等";
break;
}else{
//echo $litab[dest_column];
//echo $licol['Field'];
$boole=false;
}
}
if(!$boole){//配置表中一个字段对应完之后获取$boole的值如果为false,就将改字段存入$ziduan函数中加入提示信息
$ziduan=$ziduan.$litab[dest_column];
$boole=false;
break;
}
}
if(!$boole){
$datalist=$datalist."未找到目的字段".$ziduan.",配置错误!";
}else{
//清除旧数据
$sql=str_replace("|datamonth|",$datamonth,$li1[sqldeldest]);//配置的删除语句
// echo $sql;
//mysql_query($sql) or die(mysql_error());
if (mysql_query($sql)){
//echo mysql_query($sql);
$datalist=$datalist."原数据表已清除!<br>";
$sql1=$li1[res];//配置的查询语句
$re=str_replace("|datamonth|",$datamonth,$sql1); //替换字符
$list1 =$db-> query($re);
//mysql_query($re) or die(mysql_error());
$jx = M ($li1[desttable]);
foreach($list1 as $key=>$li2)
{
//$User = A($li1[tableno]); // 实例化action控制器对象
foreach ($listtab as $key=>$litab){
if(!empty($litab[])){//目的表中预设字段名称。
//echo $litab[];
$_POST[$litab[dest_column]]=$litab[];
}else{
$_POST[$litab[dest_column]]=$li2[$litab[source_column]];
}
}
//$User->fuValues1($li2);
$tru=$jx->create();
$bo=$jx->add();
$success=$success+1; //成功笔数
}
//echo date("Y-m-d h:i:s")."<br>".$li1[tableprofile]."结算完毕!成功".$success."笔数据。<br>";
$datalist=$datalist.date("Y-m-d h:i:s")."<br>".$datamonth.$li1[tableprofile]."结算完毕!成功".$success."笔数据。<br>";
}else{
$datalist=$datalist."删除语句出问题!<br>";
}
}
$this->assign('isok',$isok);
}
}
}
}
}
$list1 =$db-> query($re);
//mysql_query($re) or die(mysql_error());
$jx = M ($li1[desttable]);
foreach($list1 as $key=>$li2)
{
//$User = A($li1[tableno]); // 实例化action控制器对象
foreach ($listtab as $key=>$litab){
if(!empty($litab[])){//目的表中预设字段名称。
//echo $litab[];
$_POST[$litab[dest_column]]=$litab[];
}else{
$_POST[$litab[dest_column]]=$li2[$litab[source_column]];
}
}
//$User->fuValues1($li2);
$tru=$jx->create();
$bo=$jx->add();
$success=$success+1; //成功笔数
}
SELECT jx_zy_detail_charge_belong.data_month,
jx_zy_detail_charge_belong.inpatient_no,
jx_zy_detail_charge_belong.charge_date,
jx_zy_detail_charge_belong.op_id_code,
jx_zy_detail_charge_belong.charge_code,
jx_zy_detail_charge_belong.charge_fee,
jx_zy_detail_charge_belong.percentage,
jx_zy_detail_charge_belong.ward_code,
jx_zy_detail_charge_belong.dept_code,
jx_zy_detail_charge_belong.order_no,
jx_zy_detail_charge_belong.exec_unit,
jx_zy_detail_charge_belong.retprice,
jx_zy_detail_charge_belong.charge_amount,
jx_zy_detail_charge_belong.serial,
jx_zy_detail_charge_belong.group_no,
jx_zy_detail_charge_belong.bill_code,
jx_zy_detail_charge_belong.key_col,
jx_zy_detail_charge_belong.pat_name
FROM jx_zy_detail_charge_belong ,jx_mz_charge_item
WHERE jx_zy_detail_charge_belong.data_month = '|datamonth|' and
jx_zy_detail_charge_belong.charge_code =jx_mz_charge_item.fee_code and
(jx_mz_charge_item.exec_unit is null or
jx_mz_charge_item.exec_unit =' ')
union all
SELECT jx_zy_detail_charge_belong.data_month,
jx_zy_detail_charge_belong.inpatient_no,
jx_zy_detail_charge_belong.charge_date,
jx_zy_detail_charge_belong.op_id_code,
jx_zy_detail_charge_belong.charge_code,
jx_zy_detail_charge_belong.charge_fee,
jx_zy_detail_charge_belong.percentage,
jx_zy_detail_charge_belong.ward_code,
jx_zy_detail_charge_belong.dept_code,
jx_zy_detail_charge_belong.order_no,
jx_mz_charge_item.exec_unit,
jx_zy_detail_charge_belong.retprice,
jx_zy_detail_charge_belong.charge_amount,
jx_zy_detail_charge_belong.serial,
jx_zy_detail_charge_belong.group_no,
jx_zy_detail_charge_belong.bill_code,
jx_zy_detail_charge_belong.key_col,
jx_zy_detail_charge_belong.pat_name
FROM jx_zy_detail_charge_belong ,jx_mz_charge_item
WHERE jx_zy_detail_charge_belong.data_month = '|datamonth|' and
jx_zy_detail_charge_belong.charge_code= jx_mz_charge_item.fee_code and
jx_mz_charge_item.exec_unit is not null and
jx_mz_charge_item.exec_unit <>' '
就是从一个配置表,jx_mz_charge_item 中找到本表charge_code和fee_code相同,找到配置表的exec_unit符合条件就用这个值替换原表的科室值第二条转换查询sql:jx_zy_detail_charge_force-》jx_zy_detail_charge
select data_month,inpatient_no,charge_date,op_id_code,charge_code,charge_fee,dept_code,exec_unit,retprice,charge_amount,serial,bill_code,pat_name
from jx_zy_detail_charge_force
where jx_zy_detail_charge_force.exec_unit not in (select description from jx_code_ctl where item_no like 'ordept%') and data_month='|datamonth|'
union all
select data_month,inpatient_no,charge_date,op_id_code,charge_code,charge_fee,dept_code,exec_unit,retprice,charge_amount,serial,bill_code,pat_name
from jx_zy_detail_charge_force
where jx_zy_detail_charge_force.exec_unit in (select description from jx_code_ctl where item_no like 'ordept%') and not
( jx_zy_detail_charge_force.bill_code in (select description from jx_code_ctl where item_no like 'orbill%') or
jx_zy_detail_charge_force.bill_code in (select description from jx_code_ctl where item_no like 'zlbill%') ) and data_month='|datamonth|'
union all
select data_month,inpatient_no,charge_date,op_id_code,charge_code,charge_fee,dept_code,dept_code,retprice,charge_amount,serial,bill_code,pat_name
from jx_zy_detail_charge_force
where jx_zy_detail_charge_force.exec_unit in (select description from jx_code_ctl where item_no like 'ordept%') and
( jx_zy_detail_charge_force.bill_code in (select description from jx_code_ctl where item_no like 'orbill%') or
jx_zy_detail_charge_force.bill_code in (select description from jx_code_ctl where item_no like 'zlbill%') ) and data_month='|datamonth|'
union all
select data_month,inpatient_no,charge_date,op_id_code,charge_code,charge_fee,dept_code,dept_code,retprice,charge_amount,serial,bill_code,pat_name
from jx_zy_detail_charge_force
where jx_zy_detail_charge_force.exec_unit is null and data_month='|datamonth|'将执行科室exec_unit字段在表jx_code_ctl中配置的科室,并且收费大类bill_code字段也在表jx_code_ctl中配置的大类中的数据的执行科室exec_unit改为开单科室dept_code