unit_analyzegradestru.pas

中国移动大客户管理系统

unit Unit_AnalyzeGradeStru;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Unit_AnalyzeTemplet, Db, DBTables, XLGrids, Buttons, StdCtrls,
  TFlatComboBoxUnit, TFlatButtonUnit, ExtCtrls, TFlatEditUnit,
  TFlatSpinEditUnit, Threadp;

type
  TFrm_AnalyzeGradeStru = class(TFrm_AnalyzeTemplet)
    SpeedButton1: TSpeedButton;
    Label15: TLabel;
    Label7: TLabel;
    XLSheet1: TXLSheet;
    FlatSpinEditInteger_Year: TFlatSpinEditInteger;
    FlatComboBox_Month: TFlatComboBox;
    Query1: TQuery;
    procedure FormShow(Sender: TObject);
    procedure FlatButton1Click(Sender: TObject);
    Procedure ThreadDone(Sender: TObject);
    procedure FlatButton4Click(Sender: TObject);
    procedure SpeedButton1Click(Sender: TObject);
    procedure FlatButton2Click(Sender: TObject);

  private
    { Private declarations }
   Draw: Threadp.TDrawThread;

  public
    { Public declarations }
  end;

var
  Frm_AnalyzeGradeStru: TFrm_AnalyzeGradeStru;

implementation
Uses
   Unit_Public, dmmain;
   Function Fun_ExportData(pFileName:String;pReportTitle:String;pObjectSource:TObject):Boolean;stdcall;external'winfun.dll';
{$R *.DFM}
Procedure TFrm_AnalyzeGradeStru.ThreadDone(Sender: TObject);
Begin //进程结束
  Try
    draw := Nil;
  Finally
  End;
End;

procedure TFrm_AnalyzeGradeStru.FormShow(Sender: TObject);
Var
   V_Date:String;
begin
  inherited;
   v_Date:=Fun_GetDate(-1);
   FlatSpinEditInteger_Year.Value:=StrToInt(Copy(v_Date,1,4));
   FlatComboBox_Month.ItemIndex:=FlatComboBox_Month.Items.IndexOf(Copy(v_Date,6,2));
   SpeedButton1.Down :=true;
end;

procedure TFrm_AnalyzeGradeStru.FlatButton1Click(Sender: TObject);
Var
   SQL,S6,StrSQL :String;
   ValueString:String;
   WhereString:String;
   GroupString:String;
   CityNo:String;
   lstr :string;
   querytjcount,querytj3Count :integer;
Begin
   inherited;

   If Not (SBCity.Down Or SBCounty.Down Or SBBrand.Down Or SBAtt.Down ) and  SpeedButton1.Down Then
      Begin
         Application.MessageBox('请选择列条件!', '系统提示', MB_OK + MB_ICONINFORMATION);
         Exit;
      End;

   If (FlatCombobox_City.ItemIndex=0) And SBCounty.Down And Not SBCity.Down Then
      SBCity.Down:=True;

   screen.Cursor :=crSQLWait;
   FlatButton1.Enabled:=False;
   try
     SQL := 'select CityNo,Off_No_Home,CalcMonth,CustAtt,RowGrade GradeNo,ColGrade,Amount,ProductNo from T_A_CustGradeChange where 1=1';

     //地市
     ValueString:=Fun_GetCity(FlatCombobox_City,'',1);
     If ValueString<>'' Then
        SQL:=SQL+' And CityNo='''+ValueString+'''';
     ValueString:=Fun_GetCounty(FlatCombobox_County,'','',1);
     //局向
     If ValueString<>'' Then
        SQL:=SQL+' And Off_No_Home='''+ValueString+'''';
     //等级
     ValueString:=Fun_GetCodeName(FlatCombobox_Grade);
     If ValueString<>'' Then
        SQL:=SQL+' And RowGrade = '''+ValueString+'''';
     //客户属性
     ValueString:=Fun_GetCodeName(FlatComboBox_Att);
     If ValueString<>'' Then
        SQL:=SQL+' And CustAtt = '''+ValueString+'''';
     //品牌
     ValueString:=Fun_GetCodeName(FlatComboBox_Brand);
     If ValueString<>'' Then
        SQL:=SQL+' And ProductNo = '''+ValueString+'''';

     //时间
     SQL:=SQL+' And CalcMonth='''+IntToStr(FlatSpinEditInteger_Year.Value)+FlatCombobox_Month.Text+'''';

     GroupString:='';
     WhereString:=' where 1=1 ';

     If SBCity.Down = true Then
        Begin
           GroupString:=GroupString+' CityNo,';
           WhereString:=WhereString+' And CityNo=:CityNo';
        End;
     If SBCounty.Down = true Then
        Begin
           GroupString:=GroupString+'Off_No_Home,';
           WhereString:=WhereString+' And Off_No_Home=:Off_No_Home';
        End;
     If SBAtt.Down = true Then
        Begin
           GroupString:=GroupString+'CustAtt,';
           WhereString:=WhereString+' And CustAtt=:CustAtt';
        End;
     If SBBrand.Down = true Then
        Begin
           GroupString:=GroupString+'ProductNo,';
           WhereString:=WhereString+' And ProductNo=:ProductNo';
        End;
     If SBGrade.Down = true Then
        Begin
           GroupString:=GroupString+'GradeNo,';
           WhereString:=WhereString+' And GradeNo=:GradeNo';
        End;

     GroupString:=Copy(GroupString,1,Length(GroupString)-1);


     S6:='Select '+GroupString+',Sum(Amount) sl From ('+SQL+') Group By'+GroupString+' Order By '+GroupString;

     QueryDetail.Close;
     QueryDetail.DataSource := Nil;
     QueryTotal.Close;
     QueryTotal.SQL.Text := s6;
     QueryTotal.Open;

      //*********得到总的记录个数****************
      query1.close;
      lStr :='select count(*) from (';
      lStr :=lstr+s6+')';
      query1.sql.text :=lStr;
      query1.open;
      querytjCount :=query1.fields[0].asinteger;
      //*****************************************
      
     GroupString:=GroupString+',ColGrade';

     QueryDetail.DataSource := DataSourceTotal;
     QueryDetail.Close;
     QueryDetail.SQL.Text :=
     'select * from (Select ' + GroupString + ',Sum(Amount) SL from ('
     + SQL + ') Group By ' + GroupString + ') ' + WhereString;
     QueryDetail.Open;

     QueryRow.Close;
     StrSQL :=
     'select ''00'' GradeNo,''上月'' Description from dual '+
     'union select ''01'' GradeNo,''本月'' Description from dual '+
     'union select to_char(GradeNo) GradeNo,''到''||Description Description '
     +' from T_D_GRADE';
     QueryRow.sql.text :=StrSQL;
     QueryRow.Open;
     querytj3count :=QueryRow.RecordCount;
      //***************得到总的记录个数*********************
      query1.close;
      lStr :='select count(*) from (';
      lStr :=lstr+StrSQL+')';
      query1.sql.text := lstr;
      query1.open;
      querytj3Count :=query1.fields[0].asinteger;
      //*****************************************************
     
     //开始显示数据
      CityNo:=Copy(Fun_GetCity(FlatCombobox_City,'',1),3,1);
      If CityNo='' Then
         CityNo:='0';
      //要异常保护
      //if RunThead(xlsheet1, queryTotal, queryDetail,
      //  queryRow,querytjCount,querytj3Count,QueryDetail.DatabaseName,
      //  StrToInt(CityNo)) = false then
      begin
        screen.Cursor :=crDefault;
        FlatButton1.Enabled:=True;
        exit;
      end;


 //   SqlText.RowSql := '';
 //   SqlText.SumSql := '';
    //重画图形
//    ReDrawMap();

  Except
    Application.MessageBox('系统在执行过程中发生错误，请检查输入项!', '提示', MB_OK + MB_ICONINFORMATION);
    FlatButton1.Enabled:=True;
    screen.Cursor :=crDefault;
    Exit;
  End;
  screen.Cursor :=crDefault;
  FlatButton1.Enabled:=True;
end;

procedure TFrm_AnalyzeGradeStru.FlatButton4Click(Sender: TObject);
begin
   If draw <> Nil Then
      Begin
         Draw.Terminate;
         Sleep(500);
      End;

   inherited;

end;

procedure TFrm_AnalyzeGradeStru.SpeedButton1Click(Sender: TObject);
begin
  inherited;
  SpeedButton1.Down :=true;
end;

procedure TFrm_AnalyzeGradeStru.FlatButton2Click(Sender: TObject);
begin
  inherited;
  Fun_ExportData('星级变动结构统计','星级变动结构统计',XLSheet1);
end;

end.