jf_tj_un.pas

布林电话收费管理系统

unit jf_tj_un;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, stand_un, BusinessSkinForm, bsSkinCtrls, bsMessages, bsSkinGrids,
  StdCtrls, Mask, bsSkinBoxCtrls, bsDBGrids,db,DateUtils;

type
  TJF_TJ = class(Tstand)
    bsSkinPanel1: TbsSkinPanel;
    bsSkinXFormButton3: TbsSkinXFormButton;
    bsSkinXFormButton2: TbsSkinXFormButton;
    bsSkinXFormButton4: TbsSkinXFormButton;
    bsSkinGroupBox2: TbsSkinGroupBox;
    bsSkinGroupBox3: TbsSkinGroupBox;
    bsSkinGroupBox4: TbsSkinGroupBox;
    bsSkinMessage1: TbsSkinMessage;
    bsSkinPanel2: TbsSkinPanel;
    bsSkinStringGrid1: TbsSkinStringGrid;
    bsSkinScrollBar1: TbsSkinScrollBar;
    bsSkinGroupBox5: TbsSkinGroupBox;
    bsSkinLabel4: TbsSkinLabel;
    bsSkinEdit10: TbsSkinEdit;
    bsSkinLabel1: TbsSkinLabel;
    bsSkinEdit1: TbsSkinEdit;
    bsSkinEdit3: TbsSkinEdit;
    bsSkinLabel3: TbsSkinLabel;
    bsSkinPanel4: TbsSkinPanel;
    bsSkinPanel5: TbsSkinPanel;
    bsSkinDBGrid1: TbsSkinDBGrid;
    bsSkinScrollBar3: TbsSkinScrollBar;
    bsSkinScrollBar2: TbsSkinScrollBar;
    bsSkinPanel3: TbsSkinPanel;
    bsSkinLabel2: TbsSkinLabel;
    bsSkinEdit2: TbsSkinEdit;
    bsSkinEdit4: TbsSkinEdit;
    bsSkinLabel5: TbsSkinLabel;
    bsSkinLabel6: TbsSkinLabel;
    bsSkinEdit5: TbsSkinEdit;
    bsSkinLabel7: TbsSkinLabel;
    bsSkinEdit6: TbsSkinEdit;
    bsSkinLabel8: TbsSkinLabel;
    bsSkinEdit7: TbsSkinEdit;
    bsSkinLabel9: TbsSkinLabel;
    bsSkinDateEdit3: TbsSkinDateEdit;
    bsSkinScrollBar4: TbsSkinScrollBar;
    bsSkinGauge1: TbsSkinGauge;
    bsSkinXFormButton5: TbsSkinXFormButton;
    procedure bsSkinXFormButton3Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure bsSkinXFormButton4Click(Sender: TObject);
    procedure bsSkinXFormButton2Click(Sender: TObject);
    procedure bsSkinEdit7KeyPress(Sender: TObject; var Key: Char);
    procedure sj(tel_num,tel_sfzh,jcrq:string);
    procedure bsSkinStringGrid1SelectCell(Sender: TObject; ACol,
      ARow: Integer; var CanSelect: Boolean);
    procedure bsSkinXFormButton5Click(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  JF_TJ: TJF_TJ;
  yhye,yhhf,yhfwf:real;
  khrq,jzrq:tdatetime;
  tel_fw:string;
implementation
 uses globe,dm_un,main_un,qfprn_un;
{$R *.dfm}

procedure TJF_TJ.sj(tel_num,tel_sfzh,jcrq:string);
var
 dm1:tdm;
begin
 dm1:=tdm.Create(nil);
 yhhf:=0;
 yhfwf:=0;
 yhye:=0;
 main.stu.Caption:='系统正在进相关的统计，请稍候运行其它功能。。。。。当前电话为：'+tel_num;
 screen.Cursor:=-1;
 with dm1 do
  begin
   q_a.Close;
   q_a.SQL.Clear;
   q_a.SQL.Add('select * from tel_user where tel_num=:tel_num');
   q_a.Parameters.ParamByName('tel_num').Value:=tel_num;
   q_a.Prepared;
   q_a.Open;
   if VarIsNull(q_a.FieldValues ['TEL_YCFS']) then  yhye:=0
   else   yhye:=q_a.FieldValues ['TEL_YCFS'];
   khrq:=q_a.FieldValues ['TEL_TIME'];
   tel_fw:=q_a.FieldValues ['TEL_FW'];
   q_b.close;
   q_b.SQL.Clear;
   q_b.SQL.Add('select max(UP_DATE) as maxup from TEL_UP_MX where up_tel=:up_tel and up_sfzh=:up_sfzh');
   q_b.Parameters.ParamByName('up_tel').Value:=tel_num;
   q_b.Parameters.ParamByName('up_sfzh').Value:=tel_sfzh;
   q_b.Prepared;
   q_b.Open;
   if q_b.Recordset.RecordCount >0 then
     if not varisnull(q_b.FieldValues ['maxup']) then
      jzrq:=q_b.FieldValues ['maxup']
     else
       jzrq:=khrq
    else
    jzrq:=khrq;
   if khrq>jzrq then jzrq:=khrq;
   //进行日期间的比较，当传入参数的日期小于当前的结帐日期时不产生服务费，取其中月值差.
   yhfwf:=0;
   if ((yearof(strtodatetime(jcrq))-yearof(jzrq))*12+(monthof(strtodatetime(jcrq))-monthof(jzrq)))>0 then
     begin
      //如果产生服务费，则对用户的服务费进行计算。
      q_c.Close;
      q_c.SQL.Clear;
      q_c.SQL.Add('select * from tel_cgyw');
      q_c.Prepared;
      q_c.Open;
      while not q_c.Eof do
       begin
        if pos(q_c.FieldValues ['yw_id'],tel_fw)<>0 then
          yhfwf:=yhfwf+q_c.FieldValues ['yw_money'];
        q_c.Next;
       end;
        yhfwf:=yhfwf*((yearof(strtodatetime(jcrq))-yearof(jzrq))*12+(monthof(strtodatetime(jcrq))-monthof(jzrq)));
     end;
     //对用户的话费进统计，
    q_d.Close;
    q_d.SQL.Clear;
    q_d.SQL.Add('select sum(total_money) as sum1 from tel_no where zj_tel=:zj_tel and tel_sfzh=:tel_sfzh');
    q_d.SQL.Add('and b_time<=:b_time');
    q_d.Parameters.ParamByName('zj_tel').Value:=tel_num;
    q_d.Parameters.ParamByName('tel_sfzh').Value:=tel_sfzh;
    q_d.Parameters.ParamByName('b_time').Value:=strtodatetime(jcrq);
    q_d.Prepared;
    q_d.Open;

    if q_d.Recordset.RecordCount >0 then
     if VarIsNull(q_d.FieldValues ['sum1']) then
        yhhf:=0
     else
         yhhf:=q_d.FieldValues ['sum1']
    else
       yhhf:=0;
    //用户的各项费用收集完毕：
  end;
  dm1.Free;
 main.stu.Caption:='当前电话：'+tel_num+'的相关数据统计完毕！！！';
// screen.Cursor:=0;
end;

procedure TJF_TJ.bsSkinXFormButton3Click(Sender: TObject);
begin
  inherited;
  close;
end;

procedure TJF_TJ.FormCreate(Sender: TObject);
begin
  inherited;
   bsskindateedit3.Date:=strtodatetime(sys_end);
   bsskinstringgrid1.ColWidths[0]:=1;
   bsskinstringgrid1.ColWidths[1]:=90;
   bsskinstringgrid1.Cells[1,0]:='用户电话号码';
   bsskinstringgrid1.ColWidths[2]:=120;
   bsskinstringgrid1.Cells[2,0]:='用户姓名';
   bsskinstringgrid1.ColWidths[3]:=140;
   bsskinstringgrid1.Cells[3,0]:='用户身份证号';
   bsskinstringgrid1.RowCount :=1;
end;

procedure TJF_TJ.bsSkinXFormButton4Click(Sender: TObject);
begin
  inherited;
//   bsskindateedit3.Text:='';
   bsskindateedit3.Date:=strtodatetime(sys_end);
   bsskinstringgrid1.ColWidths[0]:=1;
   bsskinstringgrid1.ColWidths[1]:=90;
   bsskinstringgrid1.Cells[1,0]:='用户电话号码';
   bsskinstringgrid1.ColWidths[2]:=120;
   bsskinstringgrid1.Cells[2,0]:='用户姓名';
   bsskinstringgrid1.ColWidths[3]:=140;
   bsskinstringgrid1.Cells[3,0]:='用户身份证号';
   bsskinstringgrid1.RowCount :=1;
   bsskinedit7.Clear;
   bsskinedit1.Clear;
   bsskinedit2.Clear;
   bsskinedit3.Clear;
   bsskinedit4.Clear;
   bsskinedit5.Clear;
   bsskinedit6.Clear;
   bsskinedit10.Clear;
end;

procedure TJF_TJ.bsSkinXFormButton2Click(Sender: TObject);
var
 da1:tdatasource;
 a1,a2,a3,a4:string;
 i:integer;
begin
  inherited;
 screen.Cursor:=-1;
 dm:=tdm.Create(nil);
 da1:=tdatasource.Create(nil);
 if bsskinedit7.Text <>'' then
  begin
      if telisno(bsskinedit7.Text) then
       begin
        with dm do
         begin
          q_a.Close;
          q_a.SQL.Clear;
          q_a.SQL.Add('select * from tel_user where tel_num=:tel_num');
          q_a.Parameters.ParamByName('tel_num').Value:=bsskinedit7.Text;
          q_a.Prepared;
          q_a.Open;
         end;
         a1:=bsskinedit7.Text;
         a2:=dm.q_a.FieldValues ['tel_sfzh'];
         a3:=bsskindateedit3.Text +' 23:59:59';
         if qfjc(a1,a2,a3) then
          begin
            bsskinmessage1.MessageDlg('用户没有欠费',mtinformation,[mbok],0);
             yhhf:=0;
             yhfwf:=0;
             yhye:=0;
             sj(a1,a2,a3);
             bsskinedit2.Text:=floattostr(yhhf);
             bsskinedit4.Text:=floattostr(yhfwf);
             bsskinedit5.Text:=floattostr(yhye);
             bsskinedit6.Text:=floattostr(yhhf+yhfwf-yhye);
            with dm do
              begin
              q_b.Close;
              q_b.SQL.Clear;
              q_b.SQL.Add('select TEL_SFZH as 身份证号,TEL_NAME as 用户姓名,ZJ_TEL as 主叫号码,BJ_TEL as 被叫号码,');
              q_b.SQL.add('B_DATE as 开始日期,B_TIME as 开始时间,E_TIME as 结束时间,');
              q_b.SQL.Add('TEL_UNIT as 单元,FL_NAME as 费类名称,TOTAL_MONEY as 总费用,');
              q_b.SQL.Add('JBFL as 基本费率,USER_ID as 操作员工号 from tel_no');
              q_b.SQL.Add('where zj_tel=:zj_tel and b_time<=:b_time');
              q_b.Parameters.ParamByName('zj_tel').Value:=bsskinedit7.Text;
              q_b.Parameters.ParamByName('b_time').Value:=bsskindateedit3.Text+' 23:59:59';
              q_b.Prepared;
              q_b.Open;
             end;
           da1.DataSet:=dm.q_b;
            bsskindbgrid1.DataSource:=da1;
          end
          else
           begin
  //          bsskinmessage1.MessageDlg('用户有欠费',mtinformation,[mbok],0);
             yhhf:=0;
             yhfwf:=0;
             yhye:=0;
             sj(a1,a2,a3);
             bsskinedit2.Text:=floattostr(yhhf);
             bsskinedit4.Text:=floattostr(yhfwf);
             bsskinedit5.Text:=floattostr(yhye);
             bsskinedit6.Text:=floattostr(yhhf+yhfwf-yhye);
            with dm do
              begin
              q_b.Close;
              q_b.SQL.Clear;
              q_b.SQL.Add('select TEL_SFZH as 身份证号,TEL_NAME as 用户姓名,ZJ_TEL as 主叫号码,BJ_TEL as 被叫号码,');
              q_b.SQL.add('B_DATE as 开始日期,B_TIME as 开始时间,E_TIME as 结束时间,');
              q_b.SQL.Add('TEL_UNIT as 单元,FL_NAME as 费类名称,TOTAL_MONEY as 总费用,');
              q_b.SQL.Add('JBFL as 基本费率,USER_ID as 操作员工号 from tel_no');
              q_b.SQL.Add('where zj_tel=:zj_tel and b_time<=:b_time');
              q_b.Parameters.ParamByName('zj_tel').Value:=bsskinedit7.Text;
              q_b.Parameters.ParamByName('b_time').Value:=bsskindateedit3.Text+' 23:59:59';
              q_b.Prepared;
              q_b.Open;
             end;
           da1.DataSet:=dm.q_b;
            bsskindbgrid1.DataSource:=da1;             
           end;
       end
      else
        begin
         bsskinmessage1.MessageDlg('用户输入的号码有误，请重新输入！',mterror,[mbok],0);
         bsskinedit7.Clear;
         bsskinedit7.SetFocus;
        end;
  end
 else
  begin
   //如果为空则查询全部的数据
   //具体的方式为对用户表中的数据进行全面的计算。从而得到结果,所以要用到一个进度条。
   with dm do
    begin
     q_1.Close;
     q_1.SQL.Clear;
     q_1.SQL.Add('select * from tel_user');
     q_1.Prepared;
     q_1.Open;
    end;
    bsskingauge1.Value:=0;
    bsskingauge1.MaxValue :=dm.q_1.Recordset.RecordCount;
    bsskinstringgrid1.RowCount :=1;
    bsskinedit10.Text:='0';
    bsskinedit1.Text:='0.00';
    bsskinedit3.Text:='0.00';
        with dm do
            begin
              q_g.Close;
              q_g.SQL.Clear;
              q_g.SQL.Add('delete from qf_user');
              q_g.Prepared;
              q_g.ExecSQL;
            end;
   for i:=1 to dm.q_1.Recordset.RecordCount do
     begin
         a1:=dm.q_1.FieldValues ['tel_num'];
         a2:=dm.q_1.FieldValues ['tel_sfzh'];
         a3:=bsskindateedit3.Text +' 23:59:59';
         a4:=dm.q_1.FieldValues ['tel_name'];
       if not qfjc(a1,a2,a3) then
        begin
           bsskinstringgrid1.Cells[1,bsskinstringgrid1.RowCount]:=a1;
           bsskinstringgrid1.Cells[2,bsskinstringgrid1.RowCount]:=a4;
           bsskinstringgrid1.Cells[3,bsskinstringgrid1.RowCount]:=a2;
           bsskinstringgrid1.RowCount :=1+bsskinstringgrid1.RowCount;
           bsskinedit10.Text:=inttostr(strtoint(bsskinedit10.Text)+1);
             yhhf:=0;
             yhfwf:=0;
             yhye:=0;
             sj(a1,a2,a3);
             bsskinedit1.Text:=floattostr(yhhf+strtofloat(bsskinedit1.Text));
             bsskinedit3.Text:=floattostr(yhfwf++strtofloat(bsskinedit3.Text));
           with dm do
            begin
              q_g.Close;
              q_g.SQL.Clear;
              q_g.SQL.Add('insert into qf_user (TEL_NUM,TEL_SFZH,TEL_NAME,');
              q_g.SQL.Add('TEL_HF,TEL_FWF,TEL_TJRQ,USER_TIME,TEL_DATE,TEL_STUT,hfhj)');
              q_g.SQL.Add('values(:TEL_NUM,:TEL_SFZH,:TEL_NAME,');
              q_g.SQL.Add(':TEL_HF,:TEL_FWF,:TEL_TJRQ,:USER_TIME,:TEL_DATE,:TEL_STUT,:hfhj)');
              q_g.Parameters.ParamByName('tel_num').Value:=a1;
              q_g.Parameters.ParamByName('tel_sfzh').Value:=a2;
              q_g.Parameters.ParamByName('tel_name').Value:=a4;
              q_g.Parameters.ParamByName('tel_hf').Value:=yhhf;
              q_g.Parameters.ParamByName('tel_fwf').Value:=yhfwf;
              q_g.Parameters.ParamByName('hfhj').Value:=yhfwf+yhhf;
              q_g.Parameters.ParamByName('tel_tjrq').Value:=strtodatetime(a3);
              q_g.Parameters.ParamByName('user_time').Value:=now;
              q_g.Parameters.ParamByName('tel_date').Value:=q_1.FieldValues ['tel_time'];
              if q_1.FieldValues ['YW_STUT']='1' then
                q_g.Parameters.ParamByName('tel_stut').Value:='开机'
              else
                q_g.Parameters.ParamByName('tel_stut').Value:='停机';
              q_g.Prepared;
              q_g.ExecSQL;
            end;
        end;
      bsskingauge1.Value:=bsskingauge1.Value+1;
      dm.q_1.Next;
     end;
  end;
 screen.Cursor:=0;
  main.stu.Caption:='系统数据统计完毕，可以正常使用....';
end;

procedure TJF_TJ.bsSkinEdit7KeyPress(Sender: TObject; var Key: Char);
begin
  inherited;
 if key=#13 then
    bsskinxformbutton2click(nil);
end;

procedure TJF_TJ.bsSkinStringGrid1SelectCell(Sender: TObject; ACol,
  ARow: Integer; var CanSelect: Boolean);
begin
  inherited;
     if arow>0 then
         if  bsskinstringgrid1.Cells[1,arow]<>'' then
          begin
           bsskinedit7.Text:= bsskinstringgrid1.Cells[1,arow];
           bsskinxformbutton2click(nil);
          end;
end;

procedure TJF_TJ.bsSkinXFormButton5Click(Sender: TObject);
var
 qfprn:tqfprn;
begin
  inherited;
 dm:=tdm.Create(nil);
  with dm do
   begin
    q_f.close;
    q_f.sql.clear;
    q_f.sql.add('select * from qf_user');
    q_f.open;
   end;
    if dm.q_f.Recordset.RecordCount >0 then
      begin
         qfprn:=tqfprn.Create(jf_tj);
         qfprn.qfprntj(bsskindateedit3.Text +' 23:59:59');
         qfprn.QuickRep1.Prepare;
         qfprn.QuickRep1.Preview;
         qfprn.Free;
      end
    else
     begin
      bsskinmessage1.MessageDlg('当前没有可以打印的数据！！！请再次进行查询！！',mterror,[mbok],0);
      bsskinedit7.Clear;
     end;
end;

procedure TJF_TJ.FormDestroy(Sender: TObject);
begin
  inherited;
  dm:=tdm.Create(nil); 
        with dm do
            begin
              q_g.Close;
              q_g.SQL.Clear;
              q_g.SQL.Add('delete from qf_user');
              q_g.Prepared;
              q_g.ExecSQL;
            end;
end;

end.