📄 unit_analyzecustfancy.pas
字号:
unit Unit_AnalyzeCustFancy;
interface
uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
Unit_AnalyzeTemplet, Db, DBTables, XLGrids, Buttons, StdCtrls,
TFlatComboBoxUnit, TFlatButtonUnit, ExtCtrls, Unit_Public, ThreadP,
TFlatEditUnit, TFlatSpinEditUnit;
type
TFrm_AnalyzeCustFancy = class(TFrm_AnalyzeTemplet)
Label7: TLabel;
FlatComboBoxIndiTop: TFlatComboBox;
Label8: TLabel;
FlatComboBoxIndiServer: TFlatComboBox;
SBIndi: TSpeedButton;
XLSheet1: TXLSheet;
Label10: TLabel;
FlatComboBox_Month: TFlatComboBox;
FlatSpinEditInteger_Year: TFlatSpinEditInteger;
Query1: TQuery;
procedure FormShow(Sender: TObject);
procedure FlatComboBoxIndiTopChange(Sender: TObject);
Procedure ThreadDone(Sender: TObject);
procedure FlatButton1Click(Sender: TObject);
procedure FlatButton4Click(Sender: TObject);
procedure SBIndiClick(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_AnalyzeCustFancy: TFrm_AnalyzeCustFancy;
implementation
Function Fun_ExportData(pFileName:String;pReportTitle:String;pObjectSource:TObject):Boolean;stdcall;external'winfun.dll';
{$R *.DFM}
Procedure TFrm_AnalyzeCustFancy.ThreadDone(Sender: TObject);
Begin //进程结束
Try
draw := Nil;
Finally
End;
End;
Function TFrm_AnalyzeCustFancy.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;
//读取大类喜好代码
ValueString:=Fun_GetIndiTop(FlatComboboxIndiTop,'',1);
If ValueString<>'' Then
WhereString:=WhereString+' And IndiSEQ='+ValueString;
//读取小类喜好代码
ValueString:=Fun_GetIndiTitle(FlatComboBoxIndiServer,'',FlatComboboxIndiTop.Text,1);
If ValueString<>'' Then
WhereString:=WhereString+' And IndiCode='+ValueString;
//读取时间
WhereString:=WhereString+' And CalcMonth='''+IntToStr(FlatSpinEditInteger_Year.Value)+FlatCombobox_Month.Text+'''';
WhereSQL:=WhereString;
End;
Function TFrm_AnalyzeCustFancy.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 SBGrade.Down Then
GroupString:=GroupString+'GradeNo,';
GroupSQL:=GroupString;
End;
Function TFrm_AnalyzeCustFancy.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';
//判断是否选择客户等级
If SBGrade.Down Then
WhereString:=WhereString+' And GradeNo=:GradeNo';
RelaWhereSQL:=WhereString;
End;
Function TFrm_AnalyzeCustFancy.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_CUSTINTEREST Where '+WhereSQL+') Favoritesdist Group By '+Copy(GroupSQL,1,Length(GroupSQL)-1)
Else
Begin
v_GroupSQL:=GroupSQL(1);
SQL:='Select * From (Select INDISEQ,IndiCode,'+v_GroupSQL+' Sum(Amount) SL From (Select * From T_A_CUSTINTEREST Where '+WhereSQL+') Favoritesdist Group By '+Copy(v_GroupSQL,1,Length(v_GroupSQL)-1)+',INDISEQ,IndiCode'+') Sub_Favoritesdist Where '+RelaWhereSQL;
End;
End;
procedure TFrm_AnalyzeCustFancy.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));
Fun_GetIndiTop(FlatComboboxIndiTop,'所有',0);
FlatComboBoxIndiTopChange(Nil);
SBIndi.Down :=true;
end;
procedure TFrm_AnalyzeCustFancy.FlatComboBoxIndiTopChange(Sender: TObject);
begin
Fun_GetIndiTitle(FlatComboboxIndiServer,'所有',FlatComboboxIndiTop.Text);
end;
procedure TFrm_AnalyzeCustFancy.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 Or SBGrade.Down ) and SBIndi.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
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 SBIndi.Down Then
Begin
StrSQL:=SQL(1);
QueryDetail.DataSource := DataSourceTotal;
QueryDetail.SQL.Text := StrSQL;
QueryDetail.Open;
End;
QueryRow.Close;
QueryRow.SQL.Clear;
StrSQL :='select a.INDISEQ as INDISEQ,b.INDICAPTION,a.INDICODE,a.INDITITLE '+
' from t_d_indititle a,t_d_inditop b where a.INDISEQ=b.INDISEQ(+) ';
lstr :=Fun_GetIndiTop(FlatComboboxIndiTop,'',1);
//大类
If lstr<>'' Then
StrSQL:=StrSQL+' And a.IndiSEQ='''+lstr+'''';
lstr :=Fun_GetIndiTop(FlatComboBoxIndiServer,'',1);
//小类
If lstr<>'' Then
StrSQL:=StrSQL+' And a.IndiCode='''+lstr+'''';
StrSQL :=StrSQL+' order by b.indiseq,a.indicode';
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_AnalyzeCustFancy.FlatButton4Click(Sender: TObject);
begin
If Draw <> Nil Then
Draw.Terminate;
inherited;
end;
procedure TFrm_AnalyzeCustFancy.SBIndiClick(Sender: TObject);
begin
inherited;
SBIndi.Down :=true;
end;
procedure TFrm_AnalyzeCustFancy.FlatButton2Click(Sender: TObject);
begin
inherited;
Fun_ExportData('个人大客户喜好构成统计','个人大客户喜好构成统计',XLSheet1);
end;
end.
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -