umain.pas

用于移动公司的派单

unit Umain;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, IdBaseComponent, IdComponent, IdTCPConnection, IdTCPClient,
  IdTelnet, ComCtrls, DB, ADODB, ExtCtrls, Grids, DBGrids, StdCtrls,
  Buttons, Gauges,ComObj, Menus,Inifiles;

type
  Tamain = class(TForm)
    IdTelnet1: TIdTelnet;
    StatusBar1: TStatusBar;
    PageControl1: TPageControl;
    TabSheet1: TTabSheet;
    TabSheet2: TTabSheet;
    HMQU: TADOQuery;
    conn: TADOConnection;
    HMDS: TDataSource;
    HMQUid: TAutoIncField;
    HMQUmsisdn: TWideStringField;
    HMQUins_date: TDateTimeField;
    HMQUfinshed: TBooleanField;
    HMQUfinsh_time: TDateTimeField;
    DBGrid1: TDBGrid;
    GroupBox1: TGroupBox;
    Label1: TLabel;
    Edit1: TEdit;
    BitBtn1: TBitBtn;
    OpenDialog1: TOpenDialog;
    Label2: TLabel;
    BitBtn2: TBitBtn;
    BitBtn3: TBitBtn;
    PopupMenu1: TPopupMenu;
    N1: TMenuItem;
    BitBtn4: TBitBtn;
    Memo1: TMemo;
    HMQUhd: TWideStringField;
    HMQUhlr: TWideStringField;
    HMQUIMSI: TWideStringField;
    N2: TMenuItem;
    BitBtn5: TBitBtn;
    GroupBox2: TGroupBox;
    Gauge1: TGauge;
    Label3: TLabel;
    GroupBox3: TGroupBox;
    Label4: TLabel;
    Gauge2: TGauge;
    HMQUnode: TWideStringField;
    procedure BitBtn1Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure BitBtn2Click(Sender: TObject);
    procedure N1Click(Sender: TObject);
    procedure BitBtn3Click(Sender: TObject);
    procedure BitBtn4Click(Sender: TObject);
    procedure Memo1KeyPress(Sender: TObject; var Key: Char);
    procedure IdTelnet1DataAvailable(Sender: TIdTelnet;
      const Buffer: String);
    procedure IdTelnet1Connect(Sender: TObject);
    procedure IdTelnet1Connected(Sender: TObject);
    procedure IdTelnet1Disconnected(Sender: TObject);
    procedure loginHLR62;
    procedure loginHLR61;
    procedure findlist(msisdn:string);
    procedure FormCloseQuery(Sender: TObject; var CanClose: Boolean);
    procedure cancelT22(imsi:string);
    procedure N2Click(Sender: TObject);
    procedure BitBtn5Click(Sender: TObject);
    procedure extractfile(title:string;dataset:Tdataset);
    function fieltostr(col_cou:integer):string;
    procedure execcanel;
    procedure execimsi;
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  amain: Tamain;
  ipaddr,username,pwd,LOGINPWD,KEYCHR,BufStr:string;
  MyQUery:TADOQuery;
  CZLX:INTEGER;
implementation

{$R *.dfm}
function Tamain.fieltostr(col_cou:integer):string;
var fiena:string;
begin
   case col_cou of
     1:fiena:='A';
     2:fiena:='B';
     3:fiena:='C';
     4:fiena:='D';
     5:fiena:='E';
     6:fiena:='F';
     7:fiena:='G';
     8:fiena:='H';
     9:fiena:='I';
     10:fiena:='J';
     11:fiena:='K';
     12:fiena:='L';
     13:fiena:='M';
     14:fiena:='N';
     15:fiena:='O';
     16:fiena:='P';
     17:fiena:='Q';
     18:fiena:='R';
     19:fiena:='S';
     20:fiena:='T';
     21:fiena:='U';
     22:fiena:='V';
     23:fiena:='W';
     24:fiena:='X';
     25:fiena:='Y';
     26:fiena:='Z';
   end;
  result:=fiena;
end;

procedure Tamain.extractfile(title:string;dataset:Tdataset);
var
    ExcelApp: Variant;
    i,j,yz,sumcnt,colcount,rowcount:integer;
    sumje:real;
    s,jz,lastfiena,filename,fiename:string;
    MyQUery:TADOQuery;
    s1,s2:TstringList;
begin
   colcount:=dataset.FieldCount;
   ExcelApp := CreateOleObject('Excel.Application' );
   ExcelApp.Visible := true;
   ExcelApp.Caption := title;
   ExcelApp.WorkBooks.Add;
   //表格标题
   ExcelApp.cells.item[1,1]:=title;
//   ExcelApp.cells.item[1,1].HorizontalAlignment:=xlCenter;
   ExcelApp.ActiveSheet.Rows[1].RowHeight := 1.4/0.035; // 1厘米
   ExcelApp.ActiveSheet.Range['A1:'+fieltostr(colcount)+'1'].merge;//合并单元格
   ExcelApp.ActiveSheet.Rows[1].Font.size:=18;
   ExcelApp.ActiveSheet.Rows[1].Font.Name := '隶书';
   ExcelApp.ActiveSheet.Rows[1].Font.Color := clBlue;
   ExcelApp.ActiveSheet.Rows[1].Font.Bold := True;
   //打印字段名
{
   for i:=1 to colcount do
    begin
      fiename:=trim(dataset.Fields[i-1].FieldName);
//      yz:=pos('->',fiename);
//      fiename:=copy(fiename,1,yz-1);
      ExcelApp.cells.item[2,i]:=fiename;
    end;
}
   ExcelApp.cells.item[2,1]:='ID号';
   ExcelApp.cells.item[2,2]:='号码';
   ExcelApp.cells.item[2,3]:='导入时间';
   ExcelApp.cells.item[2,4]:='完成否';
   ExcelApp.cells.item[2,5]:='完成时间';
   ExcelApp.cells.item[2,6]:='号段';
   ExcelApp.cells.item[2,7]:='归属HLR';
   ExcelApp.cells.item[2,8]:='IMSI号';

   //打印内容
   dataset.first;
   j:=1;
   while not dataset.eof do
    begin
      for I:=1 to colcount do
       begin
        ExcelApp.cells.item[j+2,i]:=trim(dataset.fields[i-1].text);
       end;
      dataset.next;
      j:=j+1;
    end;
   //打印脚注
   j:=j+1;
   ExcelApp.cells.item[j+1,1]:='制表日期：'+formatdatetime('yyyy-mm-dd hh:mm:ss',now)+'  制表人：'+trim(username);
//   ExcelApp.cells.Item[j+1,1].HorizontalAlignment :=xlright;
   ExcelApp.ActiveSheet.Range['A'+trim(inttostr(j+1))+':'+fieltostr(colcount)+inttostr(j+1)].merge;//合并单元格
   ExcelApp.ActiveSheet.Rows[j+1].Font.size:=12;
   ExcelApp.ActiveSheet.Rows[j+1].Font.Name := '宋体';
   ExcelApp.ActiveSheet.Rows[j+1].Font.Color := clRed;
   //   ExcelApp.ActiveSheet.Rows[j+1].Font.Bold := True; //粗体字
   //加边框
   j:=j+1;
   ExcelApp.ActiveSheet.Range[ 'A1:'+fieltostr(colcount)+inttostr(j)].Borders[1].Weight := 2;
   ExcelApp.ActiveSheet.Range[ 'A1:'+fieltostr(colcount)+inttostr(j)].Borders[2].Weight := 2;
   ExcelApp.ActiveSheet.Range[ 'A1:'+fieltostr(colcount)+inttostr(j)].Borders[3].Weight := 2;
   ExcelApp.ActiveSheet.Range[ 'A1:'+fieltostr(colcount)+inttostr(j)].Borders[4].Weight := 2;
//   ExcelApp.WorkBooks.Close;
//   ExcelApp.Quit;
end;
procedure Tamain.BitBtn1Click(Sender: TObject);
begin
  if OpenDialog1.Execute then
   begin
     Edit1.text:=OpenDialog1.FileName;

   end;
end;

procedure Tamain.FormCreate(Sender: TObject);
var MyIniFile:TiniFile;
begin
//
  MyIniFile:=TIniFile.Create(extractfilepath(application.exename)+'conn.ini');
  Ipaddr:=MyIniFile.ReadString('HLR','IPADDR','10.47.0.173');
  username:=MyIniFile.ReadString('HLR','USERNAME','SCEYDX');
  pwd:=MyIniFile.ReadString('HLR','PWD','ZZEYDX');
  LOGINPWD:=MyIniFile.ReadString('HLR','LOGINPWD','cisco321');
  KEYCHR:=MyIniFile.ReadString('HLR','KEYCHR','R3745A.NK.NMC');
  MyInifile.Free;
  IdTelnet1.Host:=Ipaddr;


  if conn.Connected then conn.Connected:=false;
  conn.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source='+extractfilepath(application.exename)+'data.mdb;Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=5;';
  conn.Connected:=true;
  HMQU.open;
  MyQuery:=TADOQuery.create(nil);
  MyQUery.Connection:=conn;
end;

procedure Tamain.BitBtn2Click(Sender: TObject);
var ExcelApp: Variant;
    i,m,j:integer;
    msisdn,hd:string;
begin
   MyQUery.close;
   MyQUery.sql.clear;
   MyQUery.sql.add('delete from t_msisdn');
   MyQuery.ExecSQL;
   HMQu.close;
   HMQu.Open;
   GroupBox2.Visible:=true;
   application.ProcessMessages;
// 导入
   try
     m:=0;
//     StatusBar1.SimpleText:='正在导入源文件！';
     Label3.Caption:='正在创建OLE ....';
     application.ProcessMessages;
     ExcelApp := CreateOleObject('Excel.Application');
     ExcelApp.WorkBooks.Open(Trim(Edit1.text) );
     if not HMQU.Active then begin HMQU.open; HMQU.Last;end;
     Label3.Caption:='正在计算总记录数 ....';
     application.ProcessMessages;
     for i:=1 to 2000 do
      if trim(ExcelApp.Cells[i,1].value)<>'' then m:=m+1;
     Gauge1.MaxValue:=m;
     Gauge1.Progress:=0;
     Label3.Caption:='开始导入数据 ....';
     application.ProcessMessages;
     for j:=1 to m do
      begin
       if trim(ExcelApp.Cells[j,1].value)<>'' then
        begin
         HMQU.Append;
         msisdn:=trim(ExcelApp.Cells[j,1].value);
         HMQUmsisdn.AsString:=msisdn;
         HMQUfinshed.AsBoolean:=false;
         HMQUins_date.AsDateTime:=now;
         hd:=copy(trim(msisdn),1,3);
         HMQUhd.AsString:=hd;
         if (hd='135') or (hd='137') or (hd='159') or (hd='134') then
          HMQUhlr.AsString:='HLR62'
         else HMQUhlr.AsString:='HLR61';
         Gauge1.Progress:=Gauge1.Progress+1;
        end;
      end;
     messagedlg('数据导入成功，号码总数：'+inttostr(m),mtinformation,[mbOk],0);
     ExcelApp.WorkBooks.Close;
     ExcelApp.Quit;
   except
    ExcelApp.WorkBooks.Close;//出现异常情况时关闭
    ExcelApp.Quit;
    showmessage('请选择EXCEL电子表格!');
    exit;
   end;
  GroupBox2.Visible:=false;
end;

procedure Tamain.N1Click(Sender: TObject);
begin
//记录删除
  try
    HMQU.Delete;
  except
    messagedlg('暂时无法删除！',mtinformation,[mbOk],0);
  end;

end;
procedure Tamain.execcanel;
var i_imsi:string;
begin
  czlx:=2;
  label4.Caption:='正在关闭短信功能 -HLR61';
  application.ProcessMessages;
  if IdTelnet1.Connected then
   IdTelnet1.Disconnect;
  IdTelnet1.Connect;
  sleep(600);
  IdTelnet1.Write(LOGINPWD+#13);
  LOGINHLR61;
//获取IMSI号
//Hlr61
  MyQuery.close;
  MyQUery.sql.clear;
  MyQUery.SQL.add('select * from T_msisdn where HLR=''HLR61'' and (not finshed)');
  MyQUery.open;