📄 salelistinfo.pas
字号:
unit saleListInfo;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, DB, ADODB, StdCtrls, ComCtrls, Grids, PicButton, ExtCtrls,COMOBJ,excel97,excel2000;
type
TSaleList = class(TForm)
Image1: TImage;
Label1: TLabel;
Image2: TImage;
Bevel1: TBevel;
Bevel2: TBevel;
PicButton4: TPicButton;
Label2: TLabel;
Label3: TLabel;
Label4: TLabel;
DBgrid: TStringGrid;
BeginDate: TDateTimePicker;
EndDate: TDateTimePicker;
ComboBox1: TComboBox;
Query1: TADOQuery;
PicButton5: TPicButton;
ADOTable1: TADOTable;
PicButton1: TPicButton;
procedure Image2Click(Sender: TObject);
procedure FormCreate(Sender: TObject);
procedure PicButton4Click(Sender: TObject);
procedure DBgridDrawCell(Sender: TObject; ACol, ARow: Integer;
Rect: TRect; State: TGridDrawState);
procedure PicButton5Click(Sender: TObject);
procedure PicButton1Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
SaleList: TSaleList;
implementation
{$R *.dfm}
uses common,PrinterRpt;
procedure TSaleList.Image2Click(Sender: TObject);
begin
close;
end;
procedure TSaleList.FormCreate(Sender: TObject);
begin
ShowPloyForm(image1.Picture.Bitmap,handle);
combobox1.Items.Add('辽阳');
combobox1.Items.Add('沈阳');
combobox1.Items.Add('本溪');
combobox1.Items.Add('大连');
combobox1.Items.Add('鞍山');
combobox1.Items.Add('抚顺');
combobox1.Items.Add('丹东');
combobox1.Items.Add('北京');
combobox1.text:='辽阳';
////设置Dbgrid列标题////
Dbgrid.Cells[0,0]:='编号';
Dbgrid.ColWidths[0]:=50;
dbgrid.Cells[1,0]:='二级商名称';
Dbgrid.ColWidths[1]:=100;
Dbgrid.Cells[2,0]:='所属区域';
Dbgrid.Cells[3,0]:='销售数量';
dbgrid.ColWidths[2]:=80;
dbgrid.Cells[4,0]:='销售金额';
dbgrid.Cells[5,0]:='所属市';
dbgrid.Cells[6,0]:='业务员名称';
end;
procedure TSaleList.PicButton4Click(Sender: TObject);
var
query2,Query3:TAdoquery;
strSearch,strSearch1:string;
FieldList:TStringList;
i:integer;
strWhere,strSql:string;
tmpTotal:array[0..1] of double;
strDelete:string;
Save_cursor:Tcursor; ///设置光标
begin
///得到所属市的所有区域名称
Save_cursor:=Screen.Cursor; ///存储屏幕光标///
Screen.Cursor:=CrHourGlass; ///设置屏幕光标
for i:=1 to dbgrid.rowcount-1 do
begin
Dbgrid.Cells[0,i]:='';
Dbgrid.Cells[1,i]:='';
Dbgrid.Cells[2,i]:='';
Dbgrid.Cells[3,i]:='';
Dbgrid.Cells[4,i]:='';
Dbgrid.Cells[5,i]:='';
Dbgrid.Cells[6,i]:='';
end;
DBgrid.RowCount:=2;
/////得到指定时间范围内的所有销售单号,并插入到临时表中////
strSearch1:='SELECT * FROM saleTable WHERE saleDate>="'+DateToStr(BeginDate.Date)+'" and saleDate<="'+ DateToStr(EndDate.Date)+'" and city="'+trim(combobox1.Text)+'"';
//showmessage(strSearch1);
FieldList:=TStringList.Create;
//showmessage(strSearch);
strWhere:='DELETE * FROM TmpOutTable';
RunNonSql(query1,strWhere);
query3:=RunSqlOfAdo(query1,strSearch1);
if isRunSql(query1,strSearch1) then
begin
//Showmessage('bbb'+'aaaa');
while not query3.Eof do
begin
FieldList.Add(query3.FieldValues['saleCode']);
query3.next;
end;
FieldList.Sort;
strWhere:='INSERT INTO TmpOutTable SELECT * FROM OutTable WHERE saleCode BETWEEN "'+FieldList[0]+'" and "'+FieldList[FieldList.count-1]+'"';
//showmessage(strWhere);
RunNonSql(query1,strWhere);
end
else
begin
ShowMessage('无记录存在...');
Screen.Cursor:=Save_cursor;
exit;
end;
/////得到二级商的姓名///////
strSql:='SELECT * FROM (SELECT traderName,areaName, SUM(Amount) as a1,SUM(saleSum) as s1 FROM tmpOutTable GROUP BY TraderName,areaName) ORDER BY a1 DESC';
query3:=RunSqlOfAdo(query1,strSql);
if isRunSql(query1,strSql) then
begin
i:=1;
dbGRID.RowCount:=QUERY3.RecordCount+2;
while not query3.Eof do
begin
DBgrid.Cells[1,i]:=query3.fieldByName('tradername').AsString;
DBgrid.Cells[2,i]:=query3.fieldByName('areaName').AsString;
DBgrid.Cells[3,i]:=query3.fieldByName('A1').AsString;
DBgrid.Cells[4,i]:=query3.fieldByName('S1').AsString;
query3.Next;
inc(i);
end;
end
else
begin
ShowMessage('无记录存在...');
Screen.Cursor:=Save_cursor;
exit;
end;
//Showmessage(strWhere);
/////添加区域对应的业务员名称/////
for i:=1 to DBgrid.RowCount-2 do
begin
strSQL:='SELECT * FROM sysTable WHERE param0="'+DBgrid.Cells[2,i]+'"';
DBgrid.Cells[6,i]:=GetFieldValue(query1,strSql,'param2');
end;
////添加序号/////
for i:=1 to DBgrid.rowcount-1 do
begin
DBgrid.Cells[0,i]:=IntToStr(i);
DBgrid.Cells[5,i]:=trim(combobox1.text);
end;
////计算总合计////
tmpTotal[0]:=0;
tmpTotal[1]:=0;
for i:=1 to DBgrid.RowCount-2 do
begin
tmpTotal[0]:=tmpTotal[0]+strToFloat(DBgrid.cells[3,i]);
tmpTotal[1]:=tmpTotal[1]+strToFloat(DBgrid.cells[4,i]);
end;
DBgrid.Cells[1,DBgrid.RowCount-1]:='合计:';
DBgrid.Cells[3,DBgrid.RowCount-1]:=FloatToStr(tmpTotal[0]);
DBgrid.Cells[4,DBgrid.RowCount-1]:=FloatToStr(tmpTotal[1]);
/////添加报表数据///
strDelete:='DELETE * FROM ReportSaleList';
RunNonSql(query1,strDelete);
for i:=1 to DBgrid.rowcount-1 do
begin
strSql:='Insert into ReportSaleList(code,traderName,areaName,saleNum,saleSum,city,clerkName) VALUES('+DBgrid.cells[0,i]+',"'+DBgrid.cells[1,i]+'","'+DBgrid.cells[2,i]+'","'+DBgrid.cells[3,i]+'","'+DBgrid.cells[4,i]+'","'+DBgrid.cells[5,i]+'","'+DBgrid.cells[6,i]+'")';
RunNonSql(query1,strSql);
end;
Screen.Cursor:=SAVE_CURSOR;///恢复屏幕光标
end;
procedure TSaleList.DBgridDrawCell(Sender: TObject; ACol, ARow: Integer;
Rect: TRect; State: TGridDrawState);
begin
if (Arow=(Dbgrid.rowcount-1)) then
begin
DBgrid.Canvas.Brush.color:=rgb(255,255,206);
dbgrid.Canvas.Brush.Style:=bssolid;
dbgrid.Canvas.Font.Size:=11;
dbgrid.canvas.Font.Style:=[fsbold];
//dbgrid.Canvas.font.Color:=rgb(255,255,255);
dbgrid.Canvas.FillRect(Rect);
DBgrid.Canvas.TextOut(rect.left,rect.Top,dbgrid.Cells[Acol,Arow]);
end;
end;
procedure TSaleList.PicButton5Click(Sender: TObject);
var
TitleName:TStringList;
ColWidth,ColumnWidth:Array of Integer;
i:integer;
LineWidth:integer;
begin
TitleName:=TStringList.Create;
//ColWidth:=TStringList.Create;
TitleName.Add('编号');
TitleName.Add('二级商名称');
TitleName.Add('所属区域');
TitleName.Add('销售数量');
TitleName.Add('销售金额');
TitleName.Add('业务员名称');
TitleName.Add('所属市');
//TitleName.Add('搭赠金额');
SetReportTitle('销售排行榜情况报表');
SetTableName('ReportSaleList'); ////设置报表的表名
SetColumnTitle(TitleName);////设置报表列的标题
/////设置动态数组的长度////
setlength(colwidth,GetFieldCount(AdoTable1));
setlength(columnwidth,GetFieldCount(AdoTable1)+1);
for i:=0 to GetFieldCount(AdoTable1)-1 do
begin
Colwidth[i]:=10;
//ColumnWidth[i]:=60;
end;
////设置输出内容的列宽度
ColumnWidth[1]:=40;
ColumnWidth[2]:=90;
ColumnWidth[3]:=70;
ColumnWidth[4]:=70;
ColumnWidth[5]:=70;
ColumnWidth[6]:=80;
ColumnWidth[7]:=80;
//ColumnWidth[14]:=80;
////计算表格水平线的宽度
lineWidth:=0;
for i:=1 to GetFieldCount(AdoTable1) do
begin
LineWidth:=LineWidth+ColumnWidth[i];
end;
SetHorizontalLineWidth(LineWidth); ///设置表格水平线的宽度
SetIntegerCol(true);
CreateList(sender,AdoTable1,picbutton5,colwidth,columnWidth);
end;
procedure TSaleList.PicButton1Click(Sender: TObject);
var
v,sheet,range:variant;
i,j:integer;
TitleName:string;
Query2:TAdoquery;
strSql:string;
FileName:string;
begin
v:=createoleobject('excel.application');
v.visible:=true;
v.workbooks.add(-4167);
v.workbooks[1].sheets[1].name:='saleOrder';
sheet:=v.workbooks[1].sheets['saleorder'];
range:=Sheet.range[sheet.cells[1,1],sheet.cells[1,7]];
//sheet.caption:='sdfgsdfsd';
range.select;
range.merge;
range.HorizontalAlignment:=3;
range.font.size:=18;
range.font.bold:=2;
TitleName:=DateTostr(beginDate.Date)+'---'+DateTostr(EndDate.Date)+'销售排行榜情况表';
Sheet.range[sheet.cells[1,1],sheet.cells[1,7]]:=TitleName;
strSql:='SELECT * FROM ReportSaleList';
Query2:=RunSqlOfAdo(query1,strsql);
for i:=1 to DBgrid.RowCount do
begin
for j:=1 to 7 do
begin
v.workbooks[1].sheets[1].cells[i+1,j]:=Dbgrid.Cells[j-1,i-1];
end;
end;
//range.select;
v.workbooks[1].sheets[1].cells[DBgrid.RowCount+2,1]:='制表人:';
v.workbooks[1].sheets[1].cells[DBgrid.RowCount+2,3]:='审核人:';
v.workbooks[1].sheets[1].cells[DBgrid.RowCount+2,5]:='制表日期:';
range:=Sheet.range[sheet.cells[2,1],sheet.cells[DBgrid.RowCount+1,7]];
range.select;
range.borders.linestyle:=xlcontinuous;
//range.borders.linewidth:=3;
range.font.size:=14;
//sheet.charttype:=
Filename:=extractFilePath(paramStr(0))+'\saleTotal.xls';
{if FileExists(FileName) then
begin
DeleteFile(FileName);
v.workbooks[1].saveas(FileName);
end
else
v.workbooks[1].saveas(FileName);
}
end;
end.
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -