uyjtj.~pas

行业应急系统 包括: 应急系统（待遇核发） 发放系统 开发环境:Delphi5 (sp1)+ Oracle 8.05 MultiExport是一个可以将数据按指定字段倒出

      Sql.Add('SELECT   tryjbxx.xm AS "姓名",');
      Sql.Add('         tryjbxx.sfzh AS "身份证号",');
      Sql.Add('         ff199809 AS "199809",');
      Sql.Add('         ff199810 AS "199810",');
      Sql.Add('         ff199811 AS "199811",');
      Sql.Add('         ff199812 AS "199812",');
      Sql.Add('         ff199901 AS "199901",');
      Sql.Add('         ff199902 AS "199902",');
      Sql.Add('         ff199903 AS "199903",');
      Sql.Add('         ff199904 AS "199904",');
      Sql.Add('         ff199905 AS "199905",');
      Sql.Add('         ff199906 AS "199906",');
      Sql.Add('         ff199907 AS "199907",');
      Sql.Add('         ff199908 AS "199908",');
      Sql.Add('         ff199909 AS "199909",');
      Sql.Add('         ff199910 AS "199910",');
      Sql.Add('         ff199911 AS "199911",');
      Sql.Add('         ff199912 AS "199912",');
      Sql.Add('         ff200001 AS "200001",');
      Sql.Add('         ff200002 AS "200002",');
      Sql.Add('         ff200003 AS "200003",');
      Sql.Add('         ff200004 AS "200004",');
      Sql.Add('         ff200005 AS "200005",');
      Sql.Add('         ff200006 AS "200006",');
      Sql.Add('         ff200007 AS "200007",');
      Sql.Add('         ff200008 AS "200008",');
      Sql.Add('         ff200009 AS "200009",');
      Sql.Add('         ff200010 AS "200010",');
      Sql.Add('         ff200011 AS "200011",');
      Sql.Add('         ff200012 AS "200012",');
      Sql.Add('         ff200101 AS "200101",');
      Sql.Add('         ff200102 AS "200102",');
      Sql.Add('         ff200103 AS "200103",');
      Sql.Add('         ff200104 AS "200104",');
      Sql.Add('         ff200105 AS "200105",');
      Sql.Add('         ff200106 AS "200106",');
      Sql.Add('         ff200107 AS "200107",');
      Sql.Add('         ff200108 AS "200108",');
      Sql.Add('         ff200109 AS "200109",');
      Sql.Add('         ff200110 AS "200110",');
      Sql.Add('         ff200111 AS "200111",');
      Sql.Add('         ff200112 AS "200112",');
      Sql.Add('         total AS "合计"');
      Sql.Add('    FROM tryjbxx, tyfjl');
      Sql.Add('   WHERE tryjbxx.ryid = tyfjl.ryid and tryjbxx.dwid=''' +
        strDWID + '''');
      Sql.Add('ORDER BY tryjbxx.ryid');
    end;

    qryOut.Open;

    QExport.Dataset := qryOut;

    QExport.ColumnsWidth.Clear;
    QExport.UserFormats.Clear;

    QExport.Execute;

  finally
    qryOut.Free;
  end;

end;

procedure TfrmYJTJ.Button3Click(Sender: TObject);
begin
  //批量处理
  if trim(edtDWDM.Text) <> '' then
    with TQuery.Create(Self) do
    try
      DataBaseName := dfmMain.dbsMain.DatabaseName;
      SQL.Add('SELECT DWID FROM tDWXX where dwdm like ''' + Trim(edtDWDM.Text) +
        '''');
      Open;
      while not EOF do
      begin
        DataOut('d:\data', FieldByName('dwid').AsString);
        Next;
      end;
    finally
      Free;
    end
  else
    DataOut('d:\data', StrDWID);

  gfShowOK('数据已经倒出');

end;

procedure TfrmYJTJ.Button4Click(Sender: TObject);
begin
  inherited;

  //批量处理
  if trim(edtDWDM.Text) <> '' then
    with TQuery.Create(Self) do
    try
      DataBaseName := dfmMain.dbsMain.DatabaseName;
      SQL.Add('SELECT DWID,DWMC FROM tDWXX where dwdm like ''' +
        Trim(edtDWDM.Text) +
        '''');
      Open;
      while not EOF do
      begin
        //月结

        //单位月结统计
        dfmMain.dbsMain.StartTransaction;
        spYJTJ.UnPrepare;
        spYJTJ.ParamByName('p_DWID').AsString := FieldByName('DWID').AsString;
        spYJTJ.Prepare;
        spYJTJ.ExecProc;
        if spYJTJ.ParamByName('P_ErrorCode').AsInteger = 0 then
        begin
          dfmMain.dbsMain.Commit;
          pShowPanelHint(FieldByName('DWMC').AsString + '待遇已经统计完毕');
          Beep;
        end
        else
        begin
          dfmMain.dbsMain.Rollback;
          gfShowError(PChar(FieldByName('DWID').AsString + '月结出错'));
          Beep;
        end;
        repaint;

        Next;
      end;
    finally
      Free;
    end
  else
  begin
    //月结
    if strDWID = '' then
    begin
      gfShowOK('请选择单位');
      Abort;
    end;

    if gfShowQuestion(PChar('是否进行' + strDWMC +
      '的月结处理?' + #13 + '注意：所有的报表必须重新统计')) <> IDOK then
      Abort;

    //单位月结统计
    dfmMain.dbsMain.StartTransaction;

    spYJTJ.UnPrepare;
    spYJTJ.ParamByName('p_DWID').AsString := strDWID;
    spYJTJ.Prepare;
    spYJTJ.ExecProc;
    if spYJTJ.ParamByName('P_ErrorCode').AsInteger = 0 then
    begin
      dfmMain.dbsMain.Commit;
      pShowPanelHint('待遇已经统计完毕');
      Beep;
    end
    else
    begin
      dfmMain.dbsMain.Rollback;
      gfShowError('月结出错');
      Beep;
    end;
  end;
  repaint;


  gfShowOK('月结完成');
end;

procedure TfrmYJTJ.FormShow(Sender: TObject);
var
  i: integer;
begin
  inherited;
  for i := 0 to pagTemp.PageCount - 1 do
  begin
    Self.pagTemp.Pages[i].TabVisible := true;
    cmbInterface.Items.Add(pagTemp.Pages[i].Caption);
  end;

  PagTemp.ActivePageIndex := 0;
  PageControl1.ActivePageIndex := 0;
  PageControl3.ActivePageIndex := 0;
  DBGrid1.Visible := False;
end;

procedure TfrmYJTJ.Show2;
  function translateMonthName(oldStyle: string): string;
    //e.g.
    //      200101 should be changed to '2001年1月'
  begin
    result := Copy(oldStyle, 1, 4) + '年' + Copy(oldStyle, 5, 2) + '月';
  end;
begin
  if cmbMonth.ItemIndex = -1 then
  begin
    gfShowOK('请选择月份');
    Abort;
  end;

{
  if trim(edtXM.Text) = '' then
  begin
    gfShowOK('请选择单位');
    Abort;
  end;
}
  if not chkAll.Checked then
    case Self.PageControl3.ActivePageIndex of
      0: //显示人员增减信息
        begin
          Label4.Caption := '中央行业' +
            translateMonthName(cmbMonth.Items[cmbMonth.ItemIndex]) +
            '离退休人员增减变动汇总表';
          qryTemp2.Close;
          qryTemp2.SQL.Clear;

          with qryTemp2 do
          begin
            Sql.Add('SELECT   tdwxx.dwdm AS "单位代码", tdwxx.dwmc AS "单位名称",');
            Sql.Add('         trep_dwzjbd.lx_qc AS "期初|离休", trep_dwzjbd.lgr_qc AS "期初|老工人",');
            Sql.Add('         trep_dwzjbd.tx_qc AS "期初|退休", trep_dwzjbd.tz_qc AS "期初|退职",');
            Sql.Add('         trep_dwzjbd.hj_qc AS "期初|合计", trep_dwzjbd.lx_zj AS "增加|离休",');
            Sql.Add('         trep_dwzjbd.lgr_zj AS "增加|老工人", trep_dwzjbd.tx_zj AS "增加|退休",');
            Sql.Add('         trep_dwzjbd.tz_zj AS "增加|退职", trep_dwzjbd.hj_zj AS "增加|合计",');
            Sql.Add('         trep_dwzjbd.lx_js AS "减少|离休", trep_dwzjbd.lgr_js AS "减少|老工人",');
            Sql.Add('         trep_dwzjbd.tx_js AS "减少|退休", trep_dwzjbd.tz_js AS "减少|退职",');
            Sql.Add('         trep_dwzjbd.hj_js AS "减少|合计", trep_dwzjbd.lx_qm AS "期末|离休",');
            Sql.Add('         trep_dwzjbd.lgr_qm AS "期末|老工人", trep_dwzjbd.tx_qm AS "期末|退休",');
            Sql.Add('         trep_dwzjbd.tz_qm AS "期末|退职", trep_dwzjbd.hj_qm AS "期末|合计"');
            Sql.Add('    FROM trep_dwzjbd, tdwxx,tff_dwdy,tff_dwxx');
            Sql.Add('   WHERE trep_dwzjbd.dwid = tdwxx.dwid');
            Sql.Add('     AND trep_dwzjbd.yjrq = (SELECT TO_DATE (''' +
              cmbMonth.Items[cmbMonth.ItemIndex] + ''', ''yyyymm'')');
            Sql.Add('                               FROM DUAL)');
            sql.add('     AND tff_dwxx.dwid_ff = ''' + strDWID + '''');
            Sql.Add('     and tff_dwdy.yjdwid=tdwxx.dwid');
            Sql.Add('	 and tff_dwdy.ffdwid=tff_dwxx.dwid_ff');
            Sql.Add('UNION');
            Sql.Add('SELECT   ''合计'' AS "单位代码", '' '' AS "单位名称",');
            Sql.Add('         SUM (trep_dwzjbd.lx_qc) AS "期初|离休",');
            Sql.Add('         SUM (trep_dwzjbd.lgr_qc) AS "期初|老工人",');
            Sql.Add('         SUM (trep_dwzjbd.tx_qc) AS "期初|退休",');
            Sql.Add('         SUM (trep_dwzjbd.tz_qc) AS "期初|退职",');
            Sql.Add('         SUM (trep_dwzjbd.hj_qc) AS "期初|合计",');
            Sql.Add('         SUM (trep_dwzjbd.lx_zj) AS "增加|离休",');
            Sql.Add('         SUM (trep_dwzjbd.lgr_zj) AS "增加|老工人",');
            Sql.Add('         SUM (trep_dwzjbd.tx_zj) AS "增加|退休",');
            Sql.Add('         SUM (trep_dwzjbd.tz_zj) AS "增加|退职",');
            Sql.Add('         SUM (trep_dwzjbd.hj_zj) AS "增加|合计",');
            Sql.Add('         SUM (trep_dwzjbd.lx_js) AS "减少|离休",');
            Sql.Add('         SUM (trep_dwzjbd.lgr_js) AS "减少|老工人",');
            Sql.Add('         SUM (trep_dwzjbd.tx_js) AS "减少|退休",');
            Sql.Add('         SUM (trep_dwzjbd.tz_js) AS "减少|退职",');
            Sql.Add('         SUM (trep_dwzjbd.hj_js) AS "减少|合计",');
            Sql.Add('         SUM (trep_dwzjbd.lx_qm) AS "期末|离休",');
            Sql.Add('         SUM (trep_dwzjbd.lgr_qm) AS "期末|老工人",');
            Sql.Add('         SUM (trep_dwzjbd.tx_qm) AS "期末|退休",');
            Sql.Add('         SUM (trep_dwzjbd.tz_qm) AS "期末|退职",');
            Sql.Add('         SUM (trep_dwzjbd.hj_qm) AS "期末|合计"');
            Sql.Add('    FROM trep_dwzjbd,tdwxx,tff_dwxx,tff_dwdy');
            Sql.Add('   WHERE trep_dwzjbd.dwid = tdwxx.dwid');
            Sql.Add('     AND trep_dwzjbd.yjrq = (SELECT TO_DATE (''' +
              cmbMonth.Items[cmbMonth.ItemIndex] + ''', ''yyyymm'')');
            Sql.Add('                               FROM DUAL)');
            sql.add('     AND tff_dwxx.dwid_ff = ''' + strDWID + '''');
            Sql.Add('     and tff_dwdy.yjdwid=tdwxx.dwid');
            Sql.Add('	 and tff_dwdy.ffdwid=tff_dwxx.dwid_ff');
            Sql.Add('ORDER BY "单位代码"'); end;
        end; // end of with

      1: //一次性待遇明细表
        begin
          Label4.Caption := '中央行业' +
            translateMonthName(cmbMonth.Items[cmbMonth.ItemIndex]) +
            '一次性待遇明细表';

          qryTemp2.Close;
          qryTemp2.SQL.Clear;


          qryTemp2.Sql.Add('SELECT   tdwxx.dwdm AS "单位代码", tdwxx.dwmc AS "单位名称",');
          qryTemp2.Sql.Add('         trep_dwycxdymx.ltxswrs AS "人数|离退休死亡  ",');
          qryTemp2.Sql.Add('         trep_dwycxdymx.zzswrs AS "人数|在职死亡    ",');
          qryTemp2.Sql.Add('         trep_dwycxdymx.zcrs AS "人数|个人帐户转出",');
          qryTemp2.Sql.Add('         trep_dwycxdymx.tzhrs AS "人数|退个人帐户  ",');
          qryTemp2.Sql.Add('         trep_dwycxdymx.hjrs AS "人数|合计        ",');
          qryTemp2.Sql.Add('         trep_dwycxdymx.szf AS "死亡待遇|丧葬费      ",');
          qryTemp2.Sql.Add('         trep_dwycxdymx.fxj AS "死亡待遇|一次性抚恤金",');
          qryTemp2.Sql.Add('         trep_dwycxdymx.jjf AS "死亡待遇|救济金      ",');
          qryTemp2.Sql.Add('         trep_dwycxdymx.grzh_sw AS "死亡待遇|个人帐户    ",');
          qryTemp2.Sql.Add('         trep_dwycxdymx.tbje AS "死亡待遇|退补金额    ",');
          qryTemp2.Sql.Add('         trep_dwycxdymx.hj_sw AS "死亡待遇|合计        ",');
          qryTemp2.Sql.Add('         trep_dwycxdymx.grzh_zc AS "转出帐户|个人帐户    ",');
          qryTemp2.Sql.Add('         trep_dwycxdymx.lx_zc AS "转出帐户|利息        ",');
          qryTemp2.Sql.Add('         trep_dwycxdymx.hj_zc AS "转出帐户|合计        ",');
          qryTemp2.Sql.Add('         trep_dwycxdymx.grzh_t AS "退个人帐户|个人帐户    ",');
          qryTemp2.Sql.Add('         trep_dwycxdymx.lx_t AS "退个人帐户|利息        ",');
          qryTemp2.Sql.Add('         trep_dwycxdymx.hj_t AS "退个人帐户|合计        ",');
          qryTemp2.Sql.Add('         trep_dwycxdymx.zj AS "总计"');
          qryTemp2.Sql.Add('    FROM trep_dwycxdymx, tdwxx,tff_dwxx,tff_dwdy');
          qryTemp2.Sql.Add('   WHERE trep_dwycxdymx.dwid = tdwxx.dwid');
          qryTemp2.Sql.Add('     AND trep_dwycxdymx.yjrq = (SELECT TO_DATE ('''
            +
            cmbMonth.Items[cmbMonth.ItemIndex] + ''', ''yyyymm'')');
          qryTemp2.Sql.Add('                                  FROM DUAL)');
          qryTemp2.sql.add('     AND tff_dwxx.dwid_ff = ''' + strDWID + '''');
          qryTemp2.Sql.Add('     and tff_dwdy.yjdwid=tdwxx.dwid');
          qryTemp2.Sql.Add('	 and tff_dwdy.ffdwid=tff_dwxx.dwid_ff');
          qryTemp2.Sql.Add('UNION');
          qryTemp2.Sql.Add('SELECT   ''合计'' AS "单位代码", '' '' AS "单位名称",');
          qryTemp2.Sql.Add('         SUM (trep_dwycxdymx.ltxswrs) AS "人数|离退休死亡  ",');
          qryTemp2.Sql.Add('         SUM (trep_dwycxdymx.zzswrs) AS "人数|在职死亡    ",');
          qryTemp2.Sql.Add('         SUM (trep_dwycxdymx.zcrs) AS "人数|个人帐户转出",');
          qryTemp2.Sql.Add('         SUM (trep_dwycxdymx.tzhrs) AS "人数|退个人帐户  ",');
          qryTemp2.Sql.Add('         SUM (trep_dwycxdymx.hjrs) AS "人数|合计        ",');
          qryTemp2.Sql.Add('         SUM (trep_dwycxdymx.szf) AS "死亡待遇|丧葬费      ",');
          qryTemp2.Sql.Add('         SUM (trep_dwycxdymx.fxj) AS "死亡待遇|一次性抚恤金",');
          qryTemp2.Sql.Add('         SUM (trep_dwycxdymx.jjf) AS "死亡待遇|救济金      ",');
          qryTemp2.Sql.Add('         SUM (trep_dwycxdymx.grzh_sw) AS "死亡待遇|个人帐户    ",');
          qryTemp2.Sql.Add('         SUM (trep_dwycxdymx.tbje) AS "死亡待遇|退补金额    ",');
          qryTemp2.Sql.Add('         SUM (trep_dwycxdymx.hj_sw) AS "死亡待遇|合计        ",');
          qryTemp2.Sql.Add('         SUM (trep_dwycxdymx.grzh_zc) AS "转出帐户|个人帐户    ",');
          qryTemp2.Sql.Add('         SUM (trep_dwycxdymx.lx_zc) AS "转出帐户|利息        ",');
          qryTemp2.Sql.Add('         SUM (trep_dwycxdy