providerprofitinfo.pas

是一款针对啤酒行业的销售进销存管理系统,包括基本的进货,销售,库存

unit ProviderProfitInfo;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, ComCtrls, Grids, DB, ADODB, PicButton, ExtCtrls,COMObj,Excel97,Excel2000;

type
  TProviderProfit = class(TForm)
    Image1: TImage;
    Label1: TLabel;
    Image2: TImage;
    Bevel1: TBevel;
    Bevel2: TBevel;
    PicButton4: TPicButton;
    Query1: TADOQuery;
    DBgrid: TStringGrid;
    Label2: TLabel;
    BeginDate: TDateTimePicker;
    Label3: TLabel;
    EndDate: TDateTimePicker;
    Label4: TLabel;
    ComboBox1: TComboBox;
    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
  ProviderProfit: TProviderProfit;
  Product_Count:integer; ///存储产品数量
  Trader_count:integer; ////存储二级商人数/////
implementation

{$R *.dfm}
uses common,printerRpt;
procedure TProviderProfit.Image2Click(Sender: TObject);
begin
  close;
end;

procedure TProviderProfit.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.ColWidths[2]:=80; 
       dbgrid.Cells[3,0]:='进货价';
       Dbgrid.Cells[4,0]:='销售价';
       dbgrid.Cells[5,0]:='销售数量';
       Dbgrid.Cells[6,0]:='销售总金额';
       DBgrid.ColWidths[6]:=80;
       Dbgrid.Cells[7,0]:='差价';
       dbgrid.Cells[8,0]:='所属市';

end;

procedure TProviderProfit.PicButton4Click(Sender: TObject);
var
  query2,Query3:TAdoquery;
  strSearch,strSearch1:string;
  FieldList:TStringList;
  i,j:integer;
  strWhere,strSql:string;
  tmpTotal:array[0..7] of double;
  strdelete,strInsert: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]:='';
             Dbgrid.Cells[7,i]:='';
             Dbgrid.Cells[8,i]:='';
             Dbgrid.Cells[9,i]:='';
             Dbgrid.Cells[10,i]:='';
         end;
         DBgrid.RowCount:=2;
         ///得到产品的数量/////
         //Product_count:=GetRsCount(query1,'ProductTable');

          /////得到指定时间范围内的所有销售单号,并插入到临时表中////
         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;
         //Showmessage(strWhere);
         ////得到产品数量/////   
         strSearch:='SELECT count(ProductName) as c2 FROM  (SELECT  DISTINCT ProductName from  TmpOutTable)';
         query3:=RunSqlOfAdo(query1,strSearch);
         Product_count:=query3.FieldByName('c2').AsInteger;
         //////得到二级商的人数////
         strSearch:='SELECT  count(traderName) as c1 FROM  (SELECT  DISTINCT TraderName from  TmpOutTable)';
         query2:=RunSqlOfAdo(query1,strSearch);
         if query2.RecordCount<>0 then
         begin
            Trader_count:=query2.fieldbyname('c1').AsInteger; 
         end;
         //Showmessage('t'+inttostr(Trader_count));
         //Showmessage('p'+inttostr(Product_count));
         Dbgrid.RowCount:=Trader_count*Product_count+Trader_count+2;
         /////显示品种名称/////
         strSearch:='SELECT * FROM  (SELECT  DISTINCT ProductName from  TmpOutTable)';
         query3:=RunSqlOfAdo(query1,strSearch);
         for j:=1 to  Trader_count do
         begin
            i:=1;
            while not query3.Eof do
            begin
                DBgrid.Cells[2,i+(Product_count+1)*(j-1)]:=query3.FieldValues['ProductName'];

                query3.next;
                inc(i);
            end;
            query3.First; 
         end;
         /////显示品种名称以及二级商名称/////
         strSearch:='SELECT  traderName as t1 FROM  (SELECT  DISTINCT TraderName from  TmpOutTable)';
         query3:=RunSqlOfAdo(query1,strSearch);

         i:=1;
         while not query3.Eof do
         begin
                for j:=1 to  Product_count do
                begin
                   DBgrid.Cells[1,j+(Product_count+1)*(i-1)]:=query3.FieldValues['t1'];
                end;
                query3.next;
                inc(i);

         end;
         /////统计查询///////
         ////////得到进货价////


         for j:=1 to  Trader_count do
         begin
            for i:=1 to Product_count do
            begin
                strSearch:='SELECT  *  FROM  ProductTable  WHERE  productName="'+dbgrid.Cells[2,i+(Product_count+1)*(j-1)]+'"';
                query3:=RunSqlOfAdo(query1,strSearch);
                if  query3.FieldByName('Price_stock').isnull then
                begin
                    DBgrid.Cells[3,i+(Product_count+1)*(j-1)]:='0';
                end
                else
                   DBgrid.Cells[3,i+(Product_count+1)*(j-1)]:=query3.FieldValues['Price_stock'];
            end;
         end;
         ////得到销售价/////
         for j:=1 to  Trader_count do
         begin
            for i:=1 to Product_count do
            begin
                strSearch:='SELECT  *  FROM  tmpOutTable  WHERE  productName="'+dbgrid.Cells[2,i+(Product_count+1)*(j-1)]+'"  and  TraderName="'+dbgrid.Cells[1,i+(Product_count+1)*(j-1)]+'"';
                query3:=RunSqlOfAdo(query1,strSearch);
                if not query3.FieldByName('Price').IsNull then
                begin
                   DBgrid.Cells[4,i+(Product_count+1)*(j-1)]:=query3.FieldValues['Price'];
                end
                else
                   DBgrid.Cells[4,i+(Product_count+1)*(j-1)]:='0';
            end;
         end;
         ////得到销售数量/////
         for j:=1 to  Trader_count do
         begin
            for i:=1 to Product_count do
            begin
                strSearch:='SELECT  SUM(Amount)as Amount  FROM  tmpOutTable  WHERE  productName="'+dbgrid.Cells[2,i+(Product_count+1)*(j-1)]+'"  and  TraderName="'+dbgrid.Cells[1,i+(Product_count+1)*(j-1)]+'"';