joinsoci.~pas

企业端数据申报系统：单位管理模块 单位查询. 业务申报模块 在线数据下载 在线数据上传 在线业务申核 申报业务查询 磁盘数据导出 磁盘数据导入 在线业务模块 在线业务

            begin
              close;
              sql.Clear;
              ls_sql:='select a.cgdate cgdate,b.rewage rewage,a.itcode itcode,a.notes notes from psupcase a,psarch b where a.manid=b.manid and a.manid='+Query1manid.AsString+' and a.procflg='+chr(39)+'1'+chr(39);
              sql.Add(ls_sql);
              prepare;
              open;
              ld_chantime:=fieldbyname('cgdate').Value;
              lr_rewage:=fieldbyname('rewage').AsFloat;
              li_itcode:=fieldbyname('itcode').AsInteger;
              ls_chaninfo:=fieldbyname('notes').AsString;
              Params.Clear;
              close;
            end;
            if (Query1chantime.Value=ld_chantime) and (Query1rewage.AsFloat=lr_rewage)
              and (Query1itcode.AsInteger=li_itcode) and (Query1chaninfo.AsString=ls_chaninfo) then
            begin
              Query1.Next;
              continue;
            end else
            begin
              with datashare_form.Query1 do
              begin
                close;
                sql.Clear;
                //if strtoint(copy(Query1chgreason.AsString,1,1)) in [1,2,3,4] then
                if (strtoint(copy(ls_upcode,1,1)) in [1,2,3]) or ((strtoint(copy(ls_upcode,1,1)) in [4]) and (Query1status.AsString='1')) then
                begin
                  ls_sql:='update psupcase set iscode='+chr(39)+Query1iscode.AsString+chr(39)+',psname='+chr(39)+Query1psname.AsString+chr(39)+',agcardno='+chr(39)+Query1agcardno.AsString+chr(39)+',cgdate='+chr(39)+Query1chantime.AsString+chr(39)+',itcode='+Query1itcode.AsString+',notes='+chr(39)+Query1chaninfo.AsString+chr(39)+',opcode='+inttostr(main_form.user.OPERID)+',opdate=today(),opname='+chr(39)+main_form.user.OPNAME+chr(39)+' where manid='+Query1manid.AsString+' and procflg='+chr(39)+'1'+chr(39);
                end else
                begin
                  ls_sql:='update psupcase set iscode='+chr(39)+Query1iscode.AsString+chr(39)+',psname='+chr(39)+Query1psname.AsString+chr(39)+',agcardno='+chr(39)+Query1agcardno.AsString+chr(39)+',cgdate='+chr(39)+Query1chantime.AsString+chr(39)+',notes='+chr(39)+Query1chaninfo.AsString+chr(39)+',opcode='+inttostr(main_form.user.OPERID)+',opdate=today(),opname='+chr(39)+main_form.user.OPNAME+chr(39)+' where manid='+Query1manid.AsString+' and procflg='+chr(39)+'1'+chr(39);
                end;
                sql.Add(ls_sql);
                Execsql;

                close;
                sql.Clear;
                //if strtoint(copy(Query1chgreason.AsString,1,1)) in [1,2,3,4] then
                if (strtoint(copy(ls_upcode,1,1)) in [1,2,3]) or ((strtoint(copy(ls_upcode,1,1)) in [4]) and (Query1status.AsString='1')) then
                begin
                  ls_sql:='update psarch set chantime='+chr(39)+Query1chantime.AsString+chr(39)+',itcode='+Query1itcode.AsString+',chaninfo='+chr(39)+Query1chaninfo.AsString+chr(39)+',rewage='+ Query1rewage.AsString+' where manid='+Query1manid.AsString;
                end else
                begin
                  ls_sql:='update psarch set chantime='+chr(39)+Query1chantime.AsString+chr(39)+',chaninfo='+chr(39)+Query1chaninfo.AsString+chr(39)+' where manid='+Query1manid.AsString;
                end;
                sql.Add(ls_sql);
                Execsql;
                close;
              end;
            end;
            //Query1.Next;
            //continue;
          end else
          begin
            //ls_upcode:=Query1chgreason.AsString;
            with datashare_form.Query1 do //取变动原因对应的变动类型和参保状态
            begin
              close;
              sql.Clear;
              ls_sql:='select uptype,status from upreason where upcode='+chr(39)+ls_upcode+chr(39);
              sql.Add(ls_sql);
              prepare;
              open;
              ls_uptype:=fieldbyname('uptype').AsString;
              ls_status:=fieldbyname('status').AsString;
              Params.Clear;
              close;
            end;

            with datashare_form.Query1 do
            begin
              close;    //取得最大的opid
              sql.clear;
              ls_sql:='select max(opid) opid from psupcase';
              sql.add(ls_sql);
              prepare;
              open;
              li_opid:=fieldbyname('opid').AsInteger+1;
              //在变动表中插入一条变动业务
              //if strtoint(copy(Query1chgreason.AsString,1,1)) in [1,2,3,4] then
              if (strtoint(copy(ls_upcode,1,1)) in [1,2,3]) or ((strtoint(copy(ls_upcode,1,1)) in [4]) and (Query1status.AsString='1')) then
              begin
                ls_sql:='insert into psupcase(iscode,psname,agcardno,uptype,opid,upreason,cgdate,oristatus,opcode,opdate,procflg,notes,manid,psseno,cpseno,itcode,opname) ';
                ls_sql:=ls_sql+'values('+chr(39)+Query1iscode.AsString+chr(39)+','+chr(39)+Query1psname.AsString+chr(39)+','+chr(39)+Query1agcardno.AsString+chr(39)+','+chr(39)+ls_uptype+chr(39)+','+inttostr(li_opid)+','+chr(39)+ls_upcode+chr(39)+','+chr(39)+Query1chantime.AsString+chr(39)+','+chr(39)+Query1status.AsString+chr(39)+','+chr(39)+inttostr(main_form.user.OPERID)+chr(39)+',today(),'+chr(39)+'1'+chr(39)+','+chr(39)+Query1chaninfo.AsString+chr(39)+','+Query1manid.AsString+','+Query1psseno.AsString+','+Query1cpseno.AsString+','+ifthen(Query1itcode.AsString='','null',Query1itcode.AsString)+','+chr(39)+main_form.user.OPNAME+chr(39)+')';
              end else
              begin
                ls_sql:='insert into psupcase(iscode,psname,agcardno,uptype,opid,upreason,cgdate,oristatus,opcode,opdate,procflg,notes,manid,psseno,cpseno,opname) ';
                ls_sql:=ls_sql+'values('+chr(39)+Query1iscode.AsString+chr(39)+','+chr(39)+Query1psname.AsString+chr(39)+','+chr(39)+Query1agcardno.AsString+chr(39)+','+chr(39)+ls_uptype+chr(39)+','+inttostr(li_opid)+','+chr(39)+ls_upcode+chr(39)+','+chr(39)+Query1chantime.AsString+chr(39)+','+chr(39)+Query1status.AsString+chr(39)+','+chr(39)+inttostr(main_form.user.OPERID)+chr(39)+',today(),'+chr(39)+'1'+chr(39)+','+chr(39)+Query1chaninfo.AsString+chr(39)+','+Query1manid.AsString+','+Query1psseno.AsString+','+Query1cpseno.AsString+','+chr(39)+main_form.user.OPNAME+chr(39)+')';
              end;
              Query1.Edit;
              Query1status.Value:=ls_status;
              Query1.Post;
              sql.Clear;
              sql.Add(ls_sql);
              Execsql;
              close;
              //更新人员表中相应的值：参保状态、变动原因，变动时间，变动备注
             // if (ls_uptype='01') or (ls_uptype='02') or (ls_uptype='03') then
              //begin   //如是新增或续保，需设置ifmodify＝1
              //  ls_sql:='update psarch set status='+chr(39)+ls_status+chr(39)+',chgreason='+chr(39)+ls_upcode+chr(39)+',chantime='+chr(39)+Query1chantime.AsString+chr(39)+',chaninfo='+chr(39)+Query1chaninfo.AsString+chr(39)+',rewage='+Query1rewage.AsString+',ifmodify='+chr(39)+'1'+chr(39)+',itcode='+ifthen(Query1itcode.AsString='','null',Query1itcode.AsString)+' where manid='+Query1manid.AsString;
             // end
             // else
              //if strtoint(copy(Query1chgreason.AsString,1,1)) in [1,2,3,4] then
              if (strtoint(copy(ls_upcode,1,1)) in [1,2,3]) or ((strtoint(copy(ls_upcode,1,1)) in [4]) and (Query1status.AsString='1')) then
              begin
                ls_sql:='update psarch set status='+chr(39)+ls_status+chr(39)+',chgreason='+chr(39)+ls_upcode+chr(39)+',chantime='+chr(39)+Query1chantime.AsString+chr(39)+',chaninfo='+chr(39)+Query1chaninfo.AsString+chr(39)+',rewage='+Query1rewage.AsString+',itcode='+ifthen(Query1itcode.AsString='','null',Query1itcode.AsString)+' where manid='+Query1manid.AsString;
              end else
              begin
                ls_sql:='update psarch set status='+chr(39)+ls_status+chr(39)+',chgreason='+chr(39)+ls_upcode+chr(39)+',chantime='+chr(39)+Query1chantime.AsString+chr(39)+',chaninfo='+chr(39)+Query1chaninfo.AsString+chr(39)+' where manid='+Query1manid.AsString;
              end;
              close;
              sql.Clear;
              sql.Add(ls_sql);
              Execsql;
              close;
            end;
          end;
          Query1.Next;
        end;
        datashare_form.Dbs.Commit;
        //Query1.Refresh;
        //Query1.Filtered:=true;
        Query1.EnableControls;
        Query1.Bookmark:=bookmark;
        ReOpen(Query1);
        //Query1.CancelUpdates;
        IsModified:=false;
        //dbgrid2.SetFocus;
        TB_save.Enabled:=false;
        TB_cancel.Enabled:=false;
        TB_curcancel.Enabled:=false;
        M_save.Enabled:=false;
        M_cancel.Enabled:=false;
        M_curcancel.Enabled:=false;
        //TB_bgcancel.Enabled:=false;
        InfoUpdate;
        result:=true;
      except
        on e:exception do
        begin
          datashare_form.Dbs.Rollback;
          //Query1.CancelUpdates;
          //Query1.Filtered:=true;
          Query1.EnableControls;
          application.MessageBox(pchar(e.Message),'恩普软件',MB_OK+MB_IconStop+MB_SystemModal);
          result:=false;
        end;
      end;
    //finally
     // Query1.Bookmark:=bookmark;
    //end;
end;


procedure TJoinSoci_form.N1Click(Sender: TObject);//业务办理
begin
  inherited;
  M_yw.Checked:=true;
  N1.Checked:=true;
  DBGrid2.Visible:=True;
  DBGrid3.Visible:=False;
  ToolButton2.Caption:=' 业务';
  ToolButton2.ImageIndex:=6;
  //flatcombobox1.Visible:=true;
  //TB_itcode.Visible:=true;
  toolbar3.Visible:=true;
  toolbar6.Visible:=false;
  TB_bgcancel.Enabled:=true;
  M_bgcancel.Enabled:=true;
  panel6.Visible:=true;
  panel7.Visible:=true;
  //TB_curcancel.Enabled:=true;
  //M_curcancel.Enabled:=true;
  //setfilter;
  if treeviewcp1.Selected<>nil then
    TreeViewCP1Change(self,treeviewcp1.Selected);
end;

procedure TJoinSoci_form.N2Click(Sender: TObject); //业务查询
var
  ret:integer;
begin
  inherited;
  if IsModified then
  begin
    ret:=Application.MessageBox('职工参保变动信息已经被修改，是否要保存？','保存确认',MB_YESNOCancel+MB_DEFBUTTON1+MB_IconQuestion+MB_SystemModal);
    if ret=IDYES then
    begin
      if not save then
      begin
        N1Click(nil);
        exit;
      end;
    end
    else
      if ret=IDNO then
      begin
        cancel_butExecute(nil);
        //exit;
      end
      else
      begin
        N1Click(nil);
        exit;
      end;
  end;

  M_search.Checked:=true;
  N2.Checked:=true;
  DBGrid2.Visible:=False;
  DBGrid3.Visible:=True;
  ToolButton2.Caption:=' 查询';
  ToolButton2.ImageIndex:=5;
  //flatcombobox1.Visible:=false;
  //TB_itcode.Visible:=false;
  toolbar3.Visible:=false;
  toolbar6.Visible:=true;
  toolbar6.Top:=toolbar3.Top;
  toolbar6.Left:=toolbar3.Left;
  toolbar6.Width:=302;
  TB_bgcancel.Enabled:=false;
  M_bgcancel.Enabled:=false;
  TB_curcancel.Enabled:=false;
  m_curcancel.Enabled:=false;
  panel6.Visible:=false;
  panel7.Visible:=false;
  if treeviewcp1.Selected<>nil then
    TreeViewCP1Change(self,treeviewcp1.Selected);
end;

procedure TJoinSoci_form.SetFilter(); //根据TreeViewCP中选择的节点和参保状态过滤记录
var
  i:integer;
  ls_filter:string;
  idlist:TStringList;
  p_organ:^TOrganization;
begin
  if treeviewcp1.Selected=nil then
  begin
    application.MessageBox('请先选择一个单位或部门！','恩普软件',MB_OK+MB_ICONINFORMATION+MB_SystemModal);
    exit;
  end;
  p_organ:=treeviewcp1.Selected.Data;
  if not p_organ^.canaccess then
    exit;

  //ls_filter:='ifdel=0 ';
  if treeviewcp1.Selected<>nil then
  begin
    idlist:=TStringList.Create;
    idlist:=treeviewcp1.GenerateOrganIdsList(treeviewcp1.Selected,main_form.user.operid);//将当前节点及其所有下属节点
    ls_filter:=ls_filter+' and ((organid=-10)';
    for i:=0 to idlist.Count-1 do
    begin
      ls_filter:=ls_filter+' or (organid='+idlist[i]+')';
    end;
    ls_filter:=ls_filter+')';
  end;

  if flatcombobox1.ItemIndex<>0 then
    ls_filter:=ls_filter+' and (status='+chr(39)+statuslist[flatcombobox1.ItemIndex]+chr(39)+')';
  ls_filter:=copy(ls_filter,6,length(ls_filter)-5);
  //if Query1.Eof then
  //   Query1.Cancel;
  //Query1.Filter:=ls_filter;
  idlist.Free;

  if priorColumn_yw<>nil then
  begin
    if IsAsc_yw then
    begin
      ls_filter:=ls_filter+' order by '+priorcolumn_yw.FieldName+' asc';
    end else
    begin
      ls_filter:=ls_filter+' order by '+priorcolumn_yw.FieldName+' desc';
    end;
  end else
  begin
    ls_filter:=ls_filter+' order by agcardno ';
  end;

  query1.Close;
  query1.SQL.Clear;
  query1.SQL.Add('select * from psarch where '+ls_filter);
  query1.Prepare;
  query1.Open;

end;

procedure TJoinSoci_form.FlatComboBox1Change(Sender: TObject);
var
  ret:integer;
begin
  inherited;
  if IsModified then
  begin
    ret:=Application.MessageBox('职工参保变动信息已经被修改，是否要保存？','保存确认',MB_YESNOCANCEL+MB_DEFBUTTON1+MB_IconQuestion+MB_SystemModal);
    if ret=IDYES then
    begin
      if not save then
      begin
        flatcombobox1.ItemIndex:=PriorItemIndex;
        exit;
      end;
    end
    else
      if ret=IDNO then
      begin
        cancel_butExecute(nil);
        //exit;
      end
      else
      begin
        flatcombobox1.ItemIndex:=PriorItemIndex;
        exit;
      end;
  end;
  PriorItemIndex:=flatcombobox1.ItemIndex;
  //IsModified:=false;
  //Query1.Refresh;
  //Query1.CancelUpdates;
  //TB_save.Enabled:=false;
  //TB_cancel.Enabled:=false;
  //TB_bgcancel.Enabled:=false;

  setfilter;
end;

procedure TJoinSoci_form.TreeViewCP1Changing(Sender: TObject;
  Node: TTreeNode; var AllowChange: Boolean);
var
  ret:integer;
begin
  inherited;
  if IsModified then
  begin
    ret:=Application.MessageBox('职工参保变动信息已经被修改，是否要保存？','保存确认',MB_YESNOCANCEL+MB_DEFBUTTON1+MB_IconQuestion+MB_SystemModal);
    if ret=IDYES then
    begin
      if not save then
      begin
        AllowChange:=false;
        exit;
      end;
      AllowChange:=true;
    end
    else
      if ret=IDNO then
      begin
        cancel_butExecute(nil);
        AllowChange:=true;
        //IsModified:=false;
        //exit;
      end
      else
      begin
        AllowChange:=false;
        exit;
      end;
  end;
end;

procedure TJoinSoci_form.TreeViewCP1Change(Sender: TObject;
  Node: TTreeNode);
var
  str,ls_ids:string;
begin
  inherited;
  try
    case DBGrid2.visible of
    True:begin       //过滤人员
         setfilter;
         //InfoUpdate;
      end;
    False:begin   //显示变动业务
         BB_searchClick(self);
        {with Query2 do
        begin
          ls_ids:=treeviewcp1.GenerateOrganIds(treeviewcp1.Selected,main_form.user.operid);
          close;
          sql.Clear;
          str:='select b.iscode iscode,b.psname psname,b.agcardno agcardno,c.upname upreasonname';
          str:=str+',b.cgdate cgdate,e.opname opcode,b.opdate opdate,b.notes notes,d.name procflgname ';
          str:=str+'from psupcase b,upreason c,sbcode d,users e where b.upreason=c.upcode';
          str:=str+' and b.procflg=d.code and b.opcode=e.operid and d.type='+chr(39)+'procflg'+chr(39)+' and a.organid in ('+ls_ids+')';
          if priorColumn_search<>nil then
          begin
            if IsAsc_search then
            begin
              str:=str+' order by '+priorcolumn_search.FieldName+' asc';
            end else
            begin
              str:=str+' order by '+priorcolumn_search.FieldName+' desc';
            end;
          end else
          begin
            str:=str+' order by b.opdate desc';