unit Unit_AnalyzeIntegral;

interface

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

type
  TFrm_AnalyzeIntegral = class(TFrm_AnalyzeTemplet)
    Label10: TLabel;
    FlatSpinEditInteger_Year: TFlatSpinEditInteger;
    FlatComboBox_Month: TFlatComboBox;
    Label7: TLabel;
    FEMinIntegral: TFlatSpinEditInteger;
    Label8: TLabel;
    FEMaxIntegral: TFlatSpinEditInteger;
    FlatCheckBox1: TFlatCheckBox;
    XLSheet1: TXLSheet;
    Label9: TLabel;
    Query1: TQuery;
    procedure FormShow(Sender: TObject);
    Procedure ThreadDone(Sender: TObject);
    procedure FlatButton1Click(Sender: TObject);
    procedure FlatButton4Click(Sender: TObject);
    procedure SBGradeClick(Sender: TObject);
    procedure FlatButton2Click(Sender: TObject);

  private
    { Private declarations }
    Draw: Threadp.TDrawThread;

    Function SQL(pStyle:Integer=0):String;
    Function GroupSQL(pStyle:Integer=0):String;
    Function WhereSQL:String;
    Function RelaWhereSQL:String;

  public
    { Public declarations }
  end;

var
  Frm_AnalyzeIntegral: TFrm_AnalyzeIntegral;

implementation
Uses
   Unit_Public;
   Function Fun_ExportData(pFileName:String;pReportTitle:String;pObjectSource:TObject):Boolean;stdcall;external'winfun.dll';

{$R *.DFM}
Procedure TFrm_AnalyzeIntegral.ThreadDone(Sender: TObject);
Begin //進程結束
  Try
    draw := Nil;
  Finally
  End;
End;

Function TFrm_AnalyzeIntegral.WhereSQL:String;
Var
   WhereString:String;
   ValueString:String;
Begin
   //初始化WhereString變量
   WhereString:='(1=1)';
   //讀取地市代碼
   ValueString:=Fun_GetCity(FlatCombobox_City,'',1);
   If ValueString<>'' Then
      WhereString:=WhereString+' And CityNo='''+ValueString+'''';
   //讀取局向代碼
   ValueString:=Fun_GetCounty(FlatCombobox_County,'','',1);
   If ValueString<>'' Then
      WhereString:=WhereString+' And OFF_NO_HOME='+ValueString;
   //讀取工號代碼
   If FlatComboBox_ManagerNo.ItemIndex>0 Then
      WhereString:=WhereString+' And WK_NO='''+Copy(FlatComboBox_ManagerNo.Text,1,Pos(':',FlatComboBox_ManagerNo.Text)-1)+'''';
   //讀取等級代碼
   ValueString:=Fun_GetCodeName(FlatCombobox_Grade);
   If ValueString<>'' Then
      WhereString:=WhereString+' And GradeNo='''+ValueString+'''';
   //讀取品牌代碼
   ValueString:=Fun_GetCodeName(FlatCombobox_Brand);
   If ValueString<>'' Then
      WhereString:=WhereString+' And ProductNo='''+ValueString+'''';
   //讀取客戶屬性代碼
   ValueString:=Fun_GetCodeName(FlatCombobox_Att);
   If ValueString<>'' Then
      WhereString:=WhereString+' And CustAtt='+ValueString;
   //讀取時間
   WhereString:=WhereString+' And CalcMonth='''+IntToStr(FlatSpinEditInteger_Year.Value)+FlatCombobox_Month.Text+'''';
   //讀取最小積分點
   If FEMinIntegral.Value<>0 Then
      WhereString:=WhereString+' And Integral>='+IntToStr(FEMinIntegral.Value);
   //讀取最大積分點
   If FEMinIntegral.Value<>0 Then
      WhereString:=WhereString+' And Integral<='+IntToStr(FEMaxIntegral.Value);
   //判斷是否統(tǒng)計積分用戶或非積分用戶
   If FlatCheckBox1.Checked Then
      WhereString:=WhereString+' And AppIntegr=1'       //統(tǒng)計已積分客戶
   Else
      WhereString:=WhereString+' And AppIntegr=0';       //統(tǒng)計非積分客戶

   WhereSQL:=WhereString;
End;

Function TFrm_AnalyzeIntegral.GroupSQL(pStyle:Integer=0):String;
Var
   GroupString:String;
Begin
   //判斷是否選擇地市
   If SBCity.Down Then
      GroupString:='CityNo,';
   //判斷是否選擇局向
   If SBCounty.Down Then
      GroupString:=GroupString+'OFF_NO_HOME,';
   //判斷是否選擇品牌
   If SBBrand.Down Then
      GroupString:=GroupString+'ProductNO,';
   //判斷是否選擇客戶屬性
   If SBAtt.Down Then
      GroupString:=GroupString+'CustAtt,';

   If pStyle=1 Then
      //判斷是否選擇客戶屬性
      If SBGrade.Down Then
         GroupString:=GroupString+'GradeNo,';

   GroupSQL:=GroupString;
End;

Function TFrm_AnalyzeIntegral.RelaWhereSQL:String;
Var
   WhereString:String;
Begin
   WhereString:='(1=1)';
   //判斷是否選擇地市
   If SBCity.Down Then
      WhereString:=WhereString+' And CityNo=:CityNo';
   //判斷是否選擇局向
   If SBCounty.Down Then
      WhereString:=WhereString+' And Off_no_home=:Off_no_home';
   //判斷是否選擇品牌
   If SBBrand.Down Then
      WhereString:=WhereString+' And ProductNO=:ProductNo';
   //判斷是否選擇客戶屬性
   If SBAtt.Down Then
      WhereString:=WhereString+' And CustAtt=:CustAtt';

   RelaWhereSQL:=WhereString;
End;

Function TFrm_AnalyzeIntegral.SQL(pStyle:Integer=0):String;
Var
   v_GroupSQL:String;
Begin
   If pStyle=0 Then
      SQL:='Select '+GroupSQL+'Sum(AMOUNT) SL From (Select * From T_A_Integral Where '+WhereSQL+') Integral Group By '+Copy(GroupSQL,1,Length(GroupSQL)-1)
   Else
      Begin
         v_GroupSQL:=GroupSQL(1);
         SQL:='Select * From (Select '+v_GroupSQL+'Sum(Amount) SL From (Select * From T_A_Integral Where '+WhereSQL+') Integral Group By '+Copy(v_GroupSQL,1,Length(v_GroupSQL)-1)+') Sub_Integral Where '+RelaWhereSQL;
      End;
End;

procedure TFrm_AnalyzeIntegral.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));
   SBGrade.Down :=true;
end;

procedure TFrm_AnalyzeIntegral.FlatButton1Click(Sender: TObject);
Var
   StrSQL:String;
   CityNo:String;
   lstr :string;
   querytjcount,querytj3Count :integer;
begin
   inherited;

   If Not (SBCity.Down Or SBCounty.Down Or SBBrand.Down Or SBAtt.Down ) and SBGrade.Down Then
      Begin
         Application.MessageBox('請選擇列條件!', '系統(tǒng)提示', 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
      StrSQL:=SQL;
      QueryDetail.DataSource := Nil;
      QueryTotal.Close;
      QueryTotal.SQL.Text := StrSQL;
      QueryTotal.Open;
      //querytjcount :=QueryTotal.RecordCount;
      //*********得到總的記錄個數****************
      query1.close;
      lStr :='select count(*) from (';
      lStr :=lstr+StrSQL+')';
      query1.sql.text :=lstr;
      query1.open;
      querytjCount :=query1.fields[0].asinteger;
      //*****************************************

      QueryDetail.Close;
      If SBGrade.Down Then
         Begin
            StrSQL:=SQL(1);
            QueryDetail.DataSource := DataSourceTotal;
            QueryDetail.SQL.Text := StrSQL;
            QueryDetail.Open;
        End;

      QueryRow.Close;
      StrSQL :='Select * From t_d_Grade where GRADENO is not null ';
      lstr :=Fun_GetCodeName(FlatCombobox_Grade);
      If lstr<>'' Then
      StrSQL:=StrSQL+' And GRADENO='''+lstr+'''';      
      QueryRow.sql.text :=StrSQL;
      QueryRow.Open;
      //***************得到總的記錄個數*********************
      query1.close;
      lstr :='select count(*) ';
      lstr :=lstr+copy(StrSQL,pos('From',StrSQL),length(StrSQL)-pos('From',StrSQL)+1);
      //讀取等級代碼
      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('系統(tǒng)在執(zhí)行過程中發(fā)生錯誤，請檢查輸入項!', '提示', MB_OK + MB_ICONINFORMATION);
    FlatButton1.Enabled:=True;
    screen.Cursor :=crDefault;
    Exit;
  End;
  screen.Cursor :=crDefault;
  FlatButton1.Enabled:=True;
end;




procedure TFrm_AnalyzeIntegral.FlatButton4Click(Sender: TObject);
begin
   If Draw <> Nil Then
      Begin //退出進程
         Draw.Terminate;
         Sleep(500); //等待0.5秒,進程清理數據
      End;

   inherited;
   
end;

procedure TFrm_AnalyzeIntegral.SBGradeClick(Sender: TObject);
begin
  inherited;
  SBGrade.Down :=true;
end;

procedure TFrm_AnalyzeIntegral.FlatButton2Click(Sender: TObject);
begin
  inherited;
  Fun_ExportData('大客戶積分推廣統(tǒng)計','大客戶積分推廣統(tǒng)計',XLSheet1);
end;

end.