unit main;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Db, StdCtrls, Buttons, Grids, DBGrids, ADODB, ComCtrls, ExtCtrls,inifiles;

type
  TForm1 = class(TForm)
    Panel1: TPanel;
    BitBtn1: TBitBtn;
    Label1: TLabel;
    CheckBox1: TCheckBox;
    dtp2: TDateTimePicker;
    btn1: TBitBtn;
    GroupBox1: TGroupBox;
    lbl2: TLabel;
    lbl1: TLabel;
    lbl3: TLabel;
    edt2: TEdit;
    edt1: TEdit;
    dtp1: TDateTimePicker;
    GroupBox2: TGroupBox;
    dbgrd1: TDBGrid;
    cm1: TADOCommand;
    qry11: TADOQuery;
    ds1: TDataSource;
    qry1: TADOQuery;
    GroupBox3: TGroupBox;
    dbgrd2: TDBGrid;
    qry22: TADOQuery;
    ds2: TDataSource;
    qry2: TADOQuery;
    GroupBox4: TGroupBox;
    DBGrid1: TDBGrid;
    qry33: TADOQuery;
    ds3: TDataSource;
    qry3: TADOQuery;
    connmis: TADOConnection;
    Button1: TButton;
    connyd: TADOConnection;
    procedure btn1Click(Sender: TObject);
    procedure BitBtn1Click(Sender: TObject);
    procedure CheckBox1Click(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormCreate(Sender: TObject);
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.DFM}
var
  month,id,nam,namepy,cid,addr,addrpy,man,num,lname,sname,tname,pos,bname,anum,
  only,user,added1,added2,added3,added4,added5,flag,iflag,//------------user_info
  Charge_type,Write_back_flag,LockBank,term,//-------------user_dldf
  Last_read,Cur_read,Gage_change,Change_last_read,Change_cur_read,gage_a,//:string;--user_gage_reading
  cap,lass,//------------------------user_info
  sum_a,dra,power_a,power_lose,power_sum,useless_a,useless_lose,useless_sum,
  capacity_a,tran_lose_a,life_a,nlife_a,ind_a,farm_a,trade_a,hind_a,peak_a,norm_a,vale_a,
  other1_a,Other2_a,Other3_a,Other4_a,Other5_a,Other6_a,Other7_a,Other8_a,Other9_a,Other10_a,//--user_dldf
  Gage_scale,Gage_Type,Change_a,//integer;//--------user_gage_raeding
  tran_lose_f,repair_f,capacity_p,base_fee,base_price,cos_fee,life_p,life_f,nlife_p,nlife_f,ind_p,ind_f,
  farm_p,farm_f,trade_p,trade_f,hind_p,hind_f,peak_p,peak_f,norm_p,norm_f,vale_p,
  vale_f,other1_p,other1_f,Other2_p,Other2_f,Other3_p,Other3_f,Other4_p,Other4_f,
  Other5_p,Other5_f,Other6_p,Other6_f,Other7_p,Other7_f,Other8_p,Other8_f,Other9_p,
  Other9_f,Other10_p,Other10_f,Sum_f,Remain_last,Remain_cur,Should_charge,Fact_charge,
  Fact_latefee,derate_latefee,OddmentLast,OddmentCur,//------------user_dldf
  norm_add,cmis,cyd:string;//double;//----------------user_gage_reading
  lid,lrate,llrd,lcrd,lamt,lrpa,lgaa:Tstringlist;

function caldate(str:string;m:integer):string;
var
  tr:string;
  re,yy,mm:integer;
begin
  tr := copy(str,5,2);
  re := strtoint(tr)+m;
  mm := re;
  if (re mod 12)=0 then
  begin
    mm := 12;
    yy := strtoint(copy(str,1,4))+(re div 12)-1;
  end
  else
  begin
    if re>0 then
    begin
      mm := re mod 12;
      yy := strtoint(copy(str,1,4))+(re div 12);
    end
    else
    begin
      mm := 12+(re mod 12);
      yy := strtoint(copy(str,1,4))+(re div 12)-1;
    end;
  end;
  if length(inttostr(mm))=1 then
    result := inttostr(yy)+'0'+inttostr(mm)
  else
  result := inttostr(yy)+inttostr(mm);
end;

function GetPYStr( stri:string):string;
var
  str,tr:string;
  i:integer;
  function GetPYChar( hzchar:string):char;
  begin //的到一個(gè)漢字的拼音頭個(gè)字母
    case WORD(hzchar[1]) shl 8 + WORD(hzchar[2]) of
    $B0A1..$B0C4 : result := 'a';
    $B0C5..$B2C0 : result := 'b';
    $B2C1..$B4ED : result := 'c';
    $B4EE..$B6E9 : result := 'd';
    $B6EA..$B7A1 : result := 'e';
    $B7A2..$B8C0 : result := 'f';
    $B8C1..$B9FD : result := 'g';
    $B9FE..$BBF6 : result := 'h';
    $BBF7..$BFA5 : result := 'j';
    $BFA6..$C0AB : result := 'k';
    $C0AC..$C2E7 : result := 'l';
    $C2E8..$C4C2 : result := 'm';
    $C4C3..$C5B5 : result := 'n';
    $C5B6..$C5BD : result := 'o';
    $C5BE..$C6D9 : result := 'p';
    $C6DA..$C8BA : result := 'q';
    $C8BB..$C8F5 : result := 'r';
    $C8F6..$CBF9 : result := 's';
    $CBFA..$CDD9 : result := 't';
    $CDDA..$CEF3 : result := 'w';
    $CEF4..$D188 : result := 'x';
    $D1B9..$D4D0 : result := 'y';
    $D4D1..$D7F9 : result := 'z';
    else
    result := char(0);
    end;
  end;
begin
  str := '';
  i := 1;
  while i<length(stri) do
  begin
    tr := copy(stri,i,2);
    str := str+GetPYChar(tr);
    i := i+2;
  end;
  result := str;
end;

procedure TForm1.btn1Click(Sender: TObject);
var
  str,date:string;
begin
  month := '';
  id := '';
  nam := '';
  namepy := '';
  cid := '';
  addr := '';
  addrpy := '';
  man := '';
  num := '';
  lname := '';
  sname := '';
  tname := '';
  pos := '';
  bname := '';
  anum := '';
  only := '';
  user := '';
  added1 := '';
  added2 := '';
  added3 := '';
  added4 := '';
  added5 := '';
  flag := '';
  iflag := '';
  cap := '54007';
  lass := '54007';

  month := formatdatetime('yyyymm',dtp1.date);
  str := '';
  if trim(edt1.text)<>'' then
    str := ' and a.客戶編號(hào)='''+edt1.text+'''';
  if trim(edt2.Text)<>'' then
    str := str+' and a.客戶名稱='''+edt2.Text+'''';
  if trim(str)='' then
  begin
    showmessage('用戶編號(hào)和用戶姓名不能都為空');
    exit;
  end;
  with qry1 do
  try
    close;
    sql.Clear;
    SQL.Add('select '''+formatdatetime('yyyymm',dtp1.date)+''''+' as 月份,a.客戶編號(hào),a.客戶名稱,a.拼音字頭,a.順序號(hào),c.臺(tái)區(qū)名稱,c.所屬線路,b.名稱,c.臺(tái)區(qū)名稱,d.抄表本名稱,case 特征碼一 ');
    sql.add('when '''+inttostr(10000000)+''''+' then '''+inttostr(1)+''''+' else '''+inttostr(0)+''''+' end as 是否居光,a.特征碼一,a.用戶性質(zhì),a.用戶性質(zhì)1,c.變壓器性質(zhì),備注,0 ');
    sql.add('from df_dfsj'+formatdatetime('yyyymm',dtp1.date)+' a,bs_gdsda b,bs_tqda c,df_zcda d where a.所編號(hào)=b.所編號(hào) and a.所編號(hào)=c.所編號(hào) and a.臺(tái)區(qū)編號(hào)=c.臺(tái)區(qū)編號(hào) ');
    sql.add('and a.抄表本號(hào)=d.抄表本號(hào) and d.年月='''+formatdatetime('yyyymm',dtp1.date)+''''+' and a.所編號(hào) in ('''+inttostr(22)+''''+','''+inttostr(23)+''')');
    SQL.Add(str);
    Open;


    if recordcount<=0 then
    begin
      Close;
      SQL.Clear;
      SQL.Add('select '''+formatdatetime('yyyymm',dtp1.date)+''' as 月份,a.客戶編號(hào),a.客戶名稱,a.拼音字頭,a.順序號(hào),c.臺(tái)區(qū)名稱,c.所屬線路,b.名稱,c.臺(tái)區(qū)名稱,d.抄表本名稱,case 特征碼一 when ''');
      sql.add(inttostr(10000000)+''' then '''+inttostr(1)+''' else '''+inttostr(0)+''' end as 是否居光,a.特征碼一,a.用戶性質(zhì),a.用戶性質(zhì)1,c.變壓器性質(zhì),備注,0 from df_dfsj'+formatdatetime('yyyymm',dtp1.date));
      sql.add(' a,bs_gdsda b,bs_tqda c,df_zcda d where a.所編號(hào)=b.所編號(hào) and a.所編號(hào)=c.所編號(hào) and a.臺(tái)區(qū)編號(hào)=c.臺(tái)區(qū)編號(hào) and a.抄表本號(hào)=d.抄表本號(hào) and d.年月='''+formatdatetime('yyyymm',dtp1.date)+''' ');
      sql.add('and a.所編號(hào)='''+inttostr(21)+''' and a.抄表本號(hào)='''+inttostr(60)+'''');
      SQL.Add(str);
      Open;
    end;

    if recordcount>0 then
    begin
      pos := fieldbyname('抄表本名稱').asstring;
    end;

    if recordcount<=0 then
    begin
      close;
      sql.clear;
      sql.Text := 'select '''+formatdatetime('yyyymm',dtp1.date)+''' as 月份,a.客戶編號(hào),a.客戶名稱,a.拼音字頭,a.順序號(hào),c.臺(tái)區(qū)名稱,c.所屬線路,b.名稱,c.臺(tái)區(qū)名稱,c.臺(tái)區(qū)名稱,case 特征碼一 when '''+inttostr(10000000)+''' then ''';
      sql.Text := sql.Text+inttostr(1)+''' else '''+inttostr(0)+''' end as 是否居光,a.特征碼一,a.用戶性質(zhì),a.用戶性質(zhì)1,c.變壓器性質(zhì),備注,0 from gdf_dfsj'+formatdatetime('yyyymm',dtp1.date)+' a,bs_gdsda b,bs_tqda c where a.所編號(hào)=b.所編號(hào) ';
      sql.Text := sql.Text+'and a.所編號(hào)=c.所編號(hào) and a.臺(tái)區(qū)編號(hào)=c.臺(tái)區(qū)編號(hào) and a.所編號(hào) in ('''+inttostr(22)+''','''+inttostr(23)+''','''+inttostr(24)+''')';
      sql.Text := sql.Text+str;
      open;
    end;

    if (recordcount>0) and (pos='') then
       pos := fieldbyname('臺(tái)區(qū)名稱').asstring;

    if recordcount>0 then
    begin
      addr := fieldbyname('臺(tái)區(qū)名稱').asstring;
      lname := fieldbyname('所屬線路').asstring;
      tname := fieldbyname('臺(tái)區(qū)名稱').asstring;
      added3 := fieldbyname('變壓器性質(zhì)').asstring;
    end;

    if recordcount<=0 then