otherhycompareunit.pas

公交行业的管理系统

unit OtherHYCompareUnit;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DBCtrls, Grids, DBGrids, StdCtrls, Buttons, ExtCtrls, DB, ADODB,
  ComCtrls;

type
  TOtherHYMonthFrm = class(TForm)
    ADOQuery1: TADOQuery;
    ADOQuery6: TADOQuery;
    Panel1: TPanel;
    Panel11: TPanel;
    Label7: TLabel;
    Label11: TLabel;
    Edit3: TEdit;
    ComboBox4: TComboBox;
    Panel2: TPanel;
    SpeedButton2: TSpeedButton;
    Label8: TLabel;
    Label12: TLabel;
    Edit4: TEdit;
    ComboBox5: TComboBox;
    Panel3: TPanel;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    Label10: TLabel;
    ComboBox1: TComboBox;
    ComboBox2: TComboBox;
    ComboBox3: TComboBox;
    Panel12: TPanel;
    BitBtn1: TBitBtn;
    SpeedButton1: TSpeedButton;
    PageControl1: TPageControl;
    TabSheet1: TTabSheet;
    ListView1: TListView;
    TabSheet2: TTabSheet;
    ListView2: TListView;
    TabSheet3: TTabSheet;
    ListView3: TListView;
    TabSheet4: TTabSheet;
    ListView4: TListView;
    Panel4: TPanel;
    Panel5: TPanel;
    BBPrint1: TBitBtn;
    BitBtn2: TBitBtn;
    procedure BitBtn1Click(Sender: TObject);
    procedure BBPrint1Click(Sender: TObject);
    procedure BitBtn2Click(Sender: TObject);
    procedure SpeedButton2Click(Sender: TObject);
    procedure SpeedButton1Click(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure Edit3Change(Sender: TObject);
    procedure ComboBox4Change(Sender: TObject);
    procedure Edit4Change(Sender: TObject);
    procedure ComboBox5Change(Sender: TObject);
    procedure ComboBox2Change(Sender: TObject);
    procedure ComboBox1Change(Sender: TObject);
    procedure ComboBox3Change(Sender: TObject);
  private
    { Private declarations }
  public
    procedure IninLView(LView:TListView;sRow,eRow,sCol,eCol:Integer);
    procedure ClearLView(LView:TListView;sRow,eRow,sCol,eCol:Integer);
    procedure WriteListView(LView:TListView;Row:Integer;Source:TADOQuery);
    procedure WritePercent(LView:TListView;Row:Integer);
  end;

var
  OtherHYMonthFrm: TOtherHYMonthFrm;

implementation

uses {Code,} PrintInfoUnit,
  BusTradeDataModuleUnit, CitySelectUnit, PrintProc2;

{$R *.dfm}

procedure TOtherHYMonthFrm.WriteListView(LView:TListView;Row:Integer;Source:TADOQuery);
var
i:integer;
begin
        for i:=0 to Row-1 do
        begin
                if LView.Items[i].SubItems.Count<3 then
                begin
                        if (Source.fieldbyname('比较城市实际').AsString<>'') then
                                LView.Items[i].SubItems.Add(Converter(Source.fieldbyname('比较城市实际').AsFloat))
                        else
                                LView.Items[i].SubItems.Add('0');
                        if (Source.fieldbyname('被比较城市实际').AsString<>'') then
                                LView.Items[i].SubItems.Add(Converter(Source.fieldbyname('被比较城市实际').AsFloat))
                        else
                                LView.Items[i].SubItems.Add('0');
                        if (Source.fieldbyname('比较量').AsString<>'') then
                                LView.Items[i].SubItems.Add(Converter(Source.fieldbyname('比较量').AsFloat))
                        else
                                LView.Items[i].SubItems.Add('0');
                        LView.Items[i].SubItems.Add('0');
                        Source.Next;
                end
                else
                begin
                        if (Source.fieldbyname('比较城市实际').AsString<>'') then
                                LView.Items[i].SubItems[2]:=Converter(Source.fieldbyname('比较城市实际').AsFloat)
                        else
                                LView.Items[i].SubItems[2]:='0';
                        if (Source.fieldbyname('被比较城市实际').AsString<>'') then
                                LView.Items[i].SubItems[3]:=Converter(Source.fieldbyname('被比较城市实际').AsFloat)
                        else
                                LView.Items[i].SubItems[3]:='0';
                        if (Source.fieldbyname('比较量').AsString<>'') then
                                LView.Items[i].SubItems[4]:=Converter(Source.fieldbyname('比较量').AsFloat)
                        else
                                LView.Items[i].SubItems[4]:='0';
                        LView.Items[i].SubItems[5]:='0';
                        Source.Next;
                end;
        end;
end;

procedure TOtherHYMonthFrm.IninLView(LView:TListView;sRow,eRow,sCol,eCol:Integer);
var
        i,j:Integer;
begin
        for i:=sRow to eRow do
                for j:=sCol to eCol do
                        LView.Items[i].SubItems.Add('0');
end;

procedure TOtherHYMonthFrm.ClearLView(LView:TListView;sRow,eRow,sCol,eCol:Integer);
var
        i,j:Integer;
begin
        for i:=sRow to eRow do
                for j:=sCol to eCol do
                        LView.Items[i].SubItems[j]:='0';
end;

procedure TOtherHYMonthFrm.WritePercent(LView:TListView;Row:Integer);
VAR
        IDIV,IDIVD:single;
        i:integer;
begin
        for i:=0 to Row-1 do
        begin
                if LView.Items[i].SubItems[3]<>'0' then
                begin
                        iDiv:=StrToFloat(LView.Items[i].SubItems[4]);
                        iDivD:=StrToFloat(LView.Items[i].SubItems[3]);
                        LView.Items[i].SubItems[5]:=ConverterPercent(FloatToStr(iDiv/iDivD*100));
                end else LView.Items[i].SubItems[5]:='0';
        end;
end;

procedure TOtherHYMonthFrm.BitBtn1Click(Sender: TObject);
var
        iComCpy:String;
        iComDCpy:String;
        ComTime,BaseTime:string;
        iYear:String;
        sqlString:String;
        Result:single;
        IDiv,IDivD:single;
        j:integer;
        scmode:String;
begin
        ClearLView(ListView1,0,19,2,5);
        ClearLView(ListView2,0,19,2,5);
        ClearLView(ListView3,0,20,2,5);
        ClearLView(ListView4,0,19,2,5);
        BBPrint1.Enabled:=false;

        if Edit4.Text='' then
        begin
            showmessage('请输入被比较城市的代码！');
            exit;
        end;
        if Edit3.Text=Edit4.Text then
        begin
            showmessage('对不起！比较城市不能与被比较城市相同！请重新选择！');
            exit;
        end;
        if (ComboBox2.Text='') or (ComboBox1.Text='') or (ComboBox3.Text='') then
        begin
            showmessage('请准确输入比较基期的时间！');
            exit;
        end;
        if (length(ComboBox2.Text)<>4) or (ComboBox2.Text>'3000') or (ComboBox2.Text<'1900') then
        begin
                showmessage('对不起！基期年份非法！请重新选择！');
                exit;
        end;
        if (StrToInt(ComboBox1.Text)>StrToInt(ComboBox3.Text)) then
        begin
                showmessage('对不起！比较基期的起始月份不能大于终止月份！请重新选择！');
                exit;
        end;
        if (ComboBox4.Text='') then
        begin
                showmessage('请选择是对生成值，还是对测算值进行比较！');
                exit;
        end;
//判断比较城市
        ADOQuery6.Close;
        ADOQuery6.SQL.Clear;
        ADOQuery6.SQL.Add('select * from 公交行业统计月报_z where 城市代码='+''''+Edit3.Text+''''+
                          ' and 统计年份='+ComboBox2.Text+' and 统计月份='+ComboBox3.Text);
        ADOQuery6.Open;
        if (ADOQuery6.RecordCount=0) then
        begin
                showmessage('比较城市在'+ComboBox2.Text+'年'+ComboBox3.Text+'月的数据为空！请重新选择比较基期！');
                exit;
        end;
        ADOQuery6.Close;
        ADOQuery6.SQL.Clear;
        ADOQuery6.SQL.Add('select * from 公交行业统计月报_z where 城市代码='+''''+Edit3.Text+''''+
                          ' and 统计年份='+ComboBox2.Text+' and 统计月份>='+ComboBox1.Text+
                          ' and 统计月份<='+ComboBox3.Text);
        ADOQuery6.Open;
        if ADOQuery6.RecordCount=0 then
        begin
                showmessage('比较城市在'+ComboBox2.Text+'年'+ComboBox1.Text+'月至'+ComboBox3.Text+'月的数据为空！请重新选择比较基期！');
                exit;
        end;
        ADOQuery6.Close;
        ADOQuery6.SQL.Clear;
        ADOQuery6.SQL.Add('select distinct 统计月份 from 公交行业统计月报_z where 城市代码='+''''+Edit3.Text+''''+
                          ' and 统计年份='+ComboBox2.Text+' and 统计月份>='+ComboBox1.Text+
                          ' and 统计月份<='+ComboBox3.Text);
        ADOQuery6.Open;
        ComTime:=IntToStr(ADOQuery6.RecordCount);
//判断被比较城市
        ADOQuery6.Close;
        ADOQuery6.SQL.Clear;
        ADOQuery6.SQL.Add('select * from 公交行业统计月报_z where 城市代码='+''''+Edit4.Text+''''+
                          ' and 统计年份='+ComboBox2.Text+' and 统计月份='+ComboBox3.Text);
        ADOQuery6.Open;
        if (ADOQuery6.RecordCount=0) then
        begin
                showmessage('被比较城市在'+ComboBox2.Text+'年'+ComboBox3.Text+'月的数据为空！请重新选择比较基期！');
                exit;
        end;
        ADOQuery6.Close;
        ADOQuery6.SQL.Clear;
        ADOQuery6.SQL.Add('select * from 公交行业统计月报_z where 城市代码='+''''+Edit4.Text+''''+
                          ' and 统计年份='+ComboBox2.Text+' and 统计月份>='+ComboBox1.Text+
                          ' and 统计月份<='+ComboBox3.Text);
        ADOQuery6.Open;
        if ADOQuery6.RecordCount=0 then
        begin
            showmessage('被比较城市在'+ComboBox2.Text+'年'+ComboBox1.Text+'月至'+ComboBox3.Text+'月的数据为空！请重新选择比较基期！');
            exit;
        end;

        ADOQuery6.Close;
        ADOQuery6.SQL.Clear;
        ADOQuery6.SQL.Add('select distinct 统计月份 from 公交行业统计月报_z where 城市代码='+''''+Edit4.Text+''''+
                          ' and 统计年份='+ComboBox2.Text+' and 统计月份>='+ComboBox1.Text+
                          ' and 统计月份<='+ComboBox3.Text);
        ADOQuery6.Open;
        BaseTime:=IntToStr(ADOQuery6.RecordCount);
//选出数据
        iComCpy:=''''+Edit3.Text+'''';
        iComDCpy:=''''+Edit4.Text+'''';
        iYear:=ComboBox2.Text;
        scmode:=ComboBox4.Text;
        if scmode='生成值' then
                sqlString:='SELECT a.指标代码,'+ ' sum(a.本月实际)/'+BaseTime+' AS 比较城市实际,'
                          +' sum(b.测算值)/'+ComTime+' AS 被比较城市实际,'
                          +' (比较城市实际-被比较城市实际) AS 比较量'
                          +' FROM 公交行业统计月报_z AS a, 公交行业统计月报_z AS b'
                          +' WHERE a.城市代码= '+iComCpy+' and b.城市代码='+iComDCpy
                          +' and a.统计月份>='+ComboBox1.Text+' And a.统计月份<='+ComboBox3.Text
                          +' and b.统计月份>='+ComboBox1.Text+' And b.统计月份<='+ComboBox3.Text
                          +' And a.统计年份='+iYear+ ' And b.统计年份=a.统计年份'
                          +' And a.指标代码=b.指标代码'
                          +' GROUP BY a.指标代码, a.城市代码, b.城市代码'
                          +' ORDER BY a.指标代码'
        else
                sqlString:='SELECT a.指标代码,'+ ' sum(a.测算值)/'+BaseTime+' AS 比较城市实际,'
                          +' sum(b.测算值)/'+ComTime+' AS 被比较城市实际,'
                          +' (比较城市实际-被比较城市实际) AS 比较量'
                          +' FROM 公交行业统计月报_z AS a, 公交行业统计月报_z AS b'
                          +' WHERE a.城市代码= '+iComCpy+' and b.城市代码='+iComDCpy
                          +' and a.统计月份>='+ComboBox1.Text+' And a.统计月份<='+ComboBox3.Text
                          +' and b.统计月份>='+ComboBox1.Text+' And b.统计月份<='+ComboBox3.Text
                          +' And a.统计年份='+iYear+ ' And b.统计年份=a.统计年份'
                          +' And a.指标代码=b.指标代码'
                          +' GROUP BY a.指标代码, a.城市代码, b.城市代码'
                          +' ORDER BY a.指标代码';
        ADOQuery1.Close;
        ADOQuery1.SQL.Clear;
        ADOQuery1.SQL.Add(sqlString);
        ADOQuery1.Open;

        if ADOQuery1.RecordCount<>0 then
        begin
                WriteListView(ListView1,20,ADOQuery1);
                WriteListView(ListView2,20,ADOQuery1);
                WriteListView(ListView3,21,ADOQuery1);
                WriteListView(ListView4,20,ADOQuery1);
        end;
//25个时点数
        if scmode='生成值' then
                sqlString:='SELECT a.指标代码, a.本月实际 AS 比较城市实际, b.测算值 AS 被比较城市实际,'
                          +' (比较城市实际-被比较城市实际) AS 比较量'
                          +' FROM 公交行业统计月报_z AS a, 公交行业统计月报_z AS b'
                          +' WHERE a.城市代码= '+iComCpy+' and b.城市代码='+iComDCpy
                          +' and a.统计月份='+ComboBox3.Text+' and b.统计月份='+ComboBox3.Text
                          +' And a.统计年份='+iYear+ ' And b.统计年份=a.统计年份'
                          +' And a.指标代码=b.指标代码 ORDER BY a.指标代码'
        else
                sqlString:='SELECT a.指标代码, a.测算值 AS 比较城市实际, b.测算值 AS 被比较城市实际,'
                          +' (比较城市实际-被比较城市实际) AS 比较量'
                          +' FROM 公交行业统计月报_z AS a, 公交行业统计月报_z AS b'
                          +' WHERE a.城市代码= '+iComCpy+' and b.城市代码='+iComDCpy
                          +' and a.统计月份='+ComboBox3.Text+' and b.统计月份='+ComboBox3.Text
                          +' And a.统计年份='+iYear+ ' And b.统计年份=a.统计年份'