u_rklyjc.pas

相关的销售服务管理行业的一个软件

unit U_RKLYJC;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  BaseCell, Db, DBTables, ImgList, OleCtrls, CELLLib_TLB, TB97Ctls, TB97,
  TB97Tlbr, StdCtrls, ADODB;

type
  TfrmRKLYJC = class(TBaseCellForm)
    TempQuery: TADOQuery;
    ADOQuery1: TADOQuery;
    S_GoodQuery: TADOQuery;
    ADOStoredProc1: TADOStoredProc;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure ToolbarButton975Click(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure SpeedButton1Click(Sender: TObject);
  private
    EXITFLAG:BOOLEAN;
    CK,fromDate,toDate : String;
    procedure WriteDate;
    { Private declarations }
  public
    { Public declarations }
  end;

var
  frmRKLYJC: TfrmRKLYJC;

implementation
uses U_DM,ProcessBar,U_RKLYJCDlg;
{$R *.DFM}

procedure TfrmRKLYJC.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  inherited;
  if  not Assigned(frmRKLYJC) then
  frmRKLYJC:=nil;
end;
procedure TfrmRKLYJC.ToolbarButton975Click(Sender: TObject);
begin
 inherited;
 EXITFLAG:=FALSE;
 WITH TfrmRKLYJCDlg.CREATE(SELF) DO
 IF SHOWMODAL=MROK THEN
 begin
 CK:=Edit1.Text;
 fromDate :=FormatDateTime('yyyy-mm-dd',DateTimePicker1.DateTime);
 toDate :=FormatDateTime('yyyy-mm-dd',DateTimePicker2.DateTime);
 end
 ELSE
 begin
 EXITFLAG:=TRUE;
 Release;
 end;
 IF NOT EXITFLAG THEN
 WriteDate;
 Cell1.DoRedrawAll;
end;

PROCEDURE TfrmRKLYJC.WriteDate;
function  GetComputer:string;
var
  Name  :Array [0..Max_ComputerName_Length] of Char;
  dwSize          :DWord;
begin
  dwSize:=Max_ComputerName_Length+1;
  GetComputer:='';
 if   GetComputerName(Name,dwSize) then
     GetComputer:=strpas(Name) ;
end;
var
A:array of array of string;
i,j,Mcol,x:integer;
ss,sHsl,ComputerName : String;
iHSL:Real;
ExitFlag : BooLean;
BEGIN
ComputerName:=GetComputer;
ProcessBarForm:=TProcessBarForm.Create(Self);
processbarform.show;
ProcessBarForm.Panel1.Caption:='正在填充数据...';
ProcessBarForm.Update;

ADOStoredProc1.Parameters.ParamByName('@CK').Value:= CK;
ADOStoredProc1.Parameters.ParamByName('@fromDate').Value:= fromDate;
ADOStoredProc1.Parameters.ParamByName('@ToDate').Value:= ToDate;
ADOStoredProc1.Parameters.ParamByName('@ComputerName').Value:=computername;
ADOStoredProc1.ExecProc;

S_GoodQuery.Close;
S_GoodQuery.SQL.Clear;
S_GoodQuery.SQL.Add('select T1.nGdCode,T1.nGdName,T1.nGdGg,T2.nUName  from s_Goods T1,s_Unit T2');
S_GoodQuery.SQL.Add('where T1.nGdUnit=T2.nUTCode');
S_GoodQuery.Open;

TempQuery.Close;
TempQuery.SQL.Clear;
TempQuery.SQL.Add('select 货品编码,上期结存数量,上期结存金额,本期购入数量,本期购入金额,公共材料数量,公共材料金额,出售数量,出售金额, 本期结存数量,本期结存金额  from RP_RuLingJie where 计算机名='''+ComputerName+'''');
TempQuery.Open;

//Fillchar(JRL,sizeof(JRL),0);
ADOQuery1.Close;
ADOQuery1.SQL.CLEAR;
ADOQuery1.SQL.ADD('select TA.货品编码 nGoodCode,TB.Order_Form,TB.nFsCount from');
ADOQuery1.SQL.ADD('(select 货品编码 from RP_RuLingJie where 计算机名='''+ComputerName+''') TA');
ADOQuery1.SQL.ADD('left join');
ADOQuery1.SQL.ADD('(select A.nGoodCode,B.Order_Form,sum(A.nFsCount) nFsCount from');
ADOQuery1.SQL.ADD('(select T2.nGoodCode,T1.nDdID,sum(T2.nFsCount*T1.nRedBlue*T3.iSFTag) nFsCount');
ADOQuery1.SQL.ADD('from kc_Goodmaster T1,kc_Gooddetail T2,s_SFtype T3,Js_Mainlist T4');
ADOQuery1.SQL.ADD('where  T1.nBillid=T2.nBillid and  T1.nSfTType=T3.nSFTCode and  T1.nDdID=T4.huifenhno and T3.nSFTCode=''00033331''');
if  CK<>'' then
ADOQuery1.SQL.ADD('and T1.nCkCode='''+CK+'''');
if  fromDate<>'' then
ADOQuery1.SQL.ADD('and T1.nDateRk>='''+fromDate+'''');
if  toDate<>'' then
ADOQuery1.SQL.ADD('and T1.nDateRk>='''+toDate+'''');
ADOQuery1.SQL.ADD('group by T2.nGoodCode,T1.nDdID)A,');
ADOQuery1.SQL.ADD('(select huifenhno,Order_Form from Js_Mainlist)B');
ADOQuery1.SQL.ADD('where A.nDdID=B.huifenhno');
ADOQuery1.SQL.ADD('group by A.nGoodCode,B.Order_Form)TB');
ADOQuery1.SQL.ADD('on TA.货品编码=TB.nGoodCode');
ADOQuery1.SQL.ADD('order by nGoodCode,Order_Form');
ADOQuery1.Open;

ProcessBarForm.Gauge1.MaxValue:=ADOQuery1.RecordCount;;
ProcessBarForm.Gauge1.Progress:=0;

ClearCell;
Cell1.Rows :=6;
Cell1.Cols :=7;
//画表头              
ADOQuery1.First;
SetLength(A, 3,Cell1.Cols+10);
with ADOQuery1 do
while not Eof do
begin
   for i:=7 to Cell1.Cols do
   begin
   ExitFlag :=False;
      if A[0][i]=Fieldbyname('Order_Form').Asstring then
      begin
      ExitFlag := True ;
      break;
      end;
   end;
   if not ExitFlag then
   begin
   Cell1.Cols := Cell1.Cols + 2;
   SetLength(A,3, Cell1.Cols+10);
   A[0][Cell1.Cols-2]:=Fieldbyname('Order_Form').Asstring;
   Cell1.DoJoinCells(Cell1.Cols-2,4,Cell1.Cols-1,4);
   Cell1.DoSetCellString(Cell1.Cols-2,4,A[0][Cell1.Cols-2]);
   Cell1.DoSetCellString(Cell1.Cols-2,5,'数量');
   Cell1.DoSetColWidth(Cell1.Cols-2,40);
   Cell1.DoSetCellString(Cell1.Cols-1,5,'金额');
   Cell1.DoSetColWidth(Cell1.Cols-1,40);
   end;
Next;
end;
Cell1.DoJoinCells(7,3,Cell1.Cols-1,3);
Cell1.DoSetCellString(7,3,'本   期   领   用');

if CK='' then CK:='所有仓库';
Cell1.DoSetCellString(0,2,'材料库名:'+CK);
Cell1.DoJoinCells(0,3,0,5);
Cell1.DoSetCellString(0,3,'材料名称');
Cell1.DoJoinCells(1,3,1,5);
Cell1.DoSetCellString(1,3,'计量单位');
Cell1.DoSetColWidth(1,20);
Cell1.DoJoinCells(2,3,2,5);
Cell1.DoSetCellString(2,3,'规格');
Cell1.DoSetColWidth(2,30);
Cell1.DoJoinCells(3,3,4,3);
Cell1.DoSetCellString(3,3,'上期结存');
Cell1.DoSetColWidth(3,40);
Cell1.DoJoinCells(5,3,6,3);
Cell1.DoSetCellString(5,3,'本期购入');
Cell1.DoSetColWidth(5,40);
Cell1.DoJoinCells(3,4,3,5);
Cell1.DoSetCellString(3,4,'数量');
Cell1.DoJoinCells(4,4,4,5);
Cell1.DoSetCellString(4,4,'金额');
Cell1.DoJoinCells(5,4,5,5);
Cell1.DoSetCellString(5,4,'数量');
Cell1.DoJoinCells(6,4,6,5);
Cell1.DoSetCellString(6,4,'金额');

Cell1.Cols :=Cell1.Cols+2;
Cell1.DoJoinCells(Cell1.Cols-2,3,Cell1.Cols-1,3);
Cell1.DoSetCellString(Cell1.Cols-2,3,'公用材料');
Cell1.DoJoinCells(Cell1.Cols-2,4,Cell1.Cols-2,5);
Cell1.DoSetCellString(Cell1.Cols-2,4,'数量');
Cell1.DoSetColWidth(Cell1.Cols-2,40);
Cell1.DoJoinCells(Cell1.Cols-1,4,Cell1.Cols-1,5);
Cell1.DoSetCellString(Cell1.Cols-1,4,'金额');
Cell1.DoSetColWidth(Cell1.Cols-1,40);
Cell1.DoSetColWidth(Cell1.Cols-2,40);

Cell1.Cols :=Cell1.Cols+2;
Cell1.DoJoinCells(Cell1.Cols-2,3,Cell1.Cols-1,3);
Cell1.DoSetCellString(Cell1.Cols-2,3,'出售');
Cell1.DoJoinCells(Cell1.Cols-2,4,Cell1.Cols-2,5);
Cell1.DoSetCellString(Cell1.Cols-2,4,'数量');
Cell1.DoJoinCells(Cell1.Cols-1,4,Cell1.Cols-1,5);
Cell1.DoSetCellString(Cell1.Cols-1,4,'金额');
Cell1.DoSetColWidth(Cell1.Cols-1,40);
Cell1.DoSetColWidth(Cell1.Cols-2,40);

Cell1.Cols :=Cell1.Cols+2;
Cell1.DoJoinCells(Cell1.Cols-2,3,Cell1.Cols-1,3);
Cell1.DoSetCellString(Cell1.Cols-2,3,'本期结存');
Cell1.DoJoinCells(Cell1.Cols-2,4,Cell1.Cols-2,5);
Cell1.DoSetCellString(Cell1.Cols-2,4,'数量');
Cell1.DoJoinCells(Cell1.Cols-1,4,Cell1.Cols-1,5);
Cell1.DoSetCellString(Cell1.Cols-1,4,'金额');
Cell1.DoSetColWidth(Cell1.Cols-1,40);
Cell1.DoSetColWidth(Cell1.Cols-2,40);

Cell1.DoJoinCells(0,0,Cell1.Cols-1,0);
Cell1.DoSetCellString(0,0,'江阴汇丰鞋业有限公司材料入库领用结存月报表');
Cell1.DoSetCellFont(0,0,14,5,'宋体');
Cell1.DoSetCellReadOnly(0,0,true);
Cell1.DoSetCellAlignment(0,0,100);
Cell1.DoSetRowHeight(0,50);
Cell1.DoJoinCells(0,2,3,2);

Cell1.DoJoinCells(6,2,Cell1.Cols-1,2);
Cell1.DoSetCellString(6,2,'开始日期:'+fromDate+'  结束日期:'+toDate);

Cell1.DoDrawLine(0,3,Cell1.Cols-1,Cell1.Rows-1,0,1,1);
//画表头完毕

ADOQuery1.First;
for i:=0 to ADOQuery1.RecordCount -1 do
    begin
      if (i<>0) and (ss<>ADOQuery1.Fieldbyname('nGoodCode').Asstring) and (ADOQuery1.Fieldbyname('nGoodCode').Asstring<>'')  then
       begin
       Cell1.Rows:=Cell1.Rows+1;
          if S_GoodQuery.Locate('nGdCode',A[1][0],[]) then begin
          A[1][0]:=S_GoodQuery.Fieldbyname('nGdName').Asstring;
          A[1][1]:=S_GoodQuery.Fieldbyname('nUName').Asstring;
          A[1][2]:=S_GoodQuery.Fieldbyname('nGdGg').Asstring;
          end;
          if TempQuery.Locate('货品编码',A[1][0],[]) then begin
          A[1][3]:=TempQuery.Fieldbyname('上期结存数量').Asstring;
          A[1][4]:=TempQuery.Fieldbyname('上期结存金额').Asstring;
          A[1][5]:=TempQuery.Fieldbyname('本期购入数量').Asstring;
          A[1][6]:=TempQuery.Fieldbyname('本期购入金额').Asstring;
          A[1][Cell1.Cols-6]:=TempQuery.Fieldbyname('公共材料数量').Asstring;
          A[1][Cell1.Cols-5]:=TempQuery.Fieldbyname('公共材料金额').Asstring;
          A[1][Cell1.Cols-4]:=TempQuery.Fieldbyname('出售数量').Asstring;
          A[1][Cell1.Cols-3]:=TempQuery.Fieldbyname('出售金额').Asstring;
          A[1][Cell1.Cols-2]:=TempQuery.Fieldbyname('本期结存数量').Asstring;
          A[1][Cell1.Cols-1]:=TempQuery.Fieldbyname('本期结存金额').Asstring;
          end;
       for j:=0 to Cell1.Cols-1 do begin
          Cell1.DoSetCellString(j,Cell1.Rows-1,A[1][j]);
          if  (A[1][j]<>'') and (j>2) then
            if A[2][j]<>'' then
             A[2][j]:=FloatToStr(StrToFloat(A[2][j])+ StrToFloat(A[1][j]))
            else
             A[2][j]:=A[1][j];
          A[1][j]:='';
          end;
       ss:=ADOQuery1.Fieldbyname('nGoodCode').Asstring;
       end;
  // 填零列编码
  A[1][0]:=ADOQuery1.Fieldbyname('nGoodCode').Asstring;
  for j:=7 to Cell1.Cols-6 do
  if   (A[0][j]=ADOQuery1.Fieldbyname('Order_Form').Asstring) and (ADOQuery1.Fieldbyname('Order_Form').Asstring<>'') then
     if A[1][j]<>'' then
      A[1][j]:=FloatToStr(StrToFloat(A[1][j])+ADOQuery1.Fieldbyname('nFsCount').AsFloat)
    else
      A[1][j]:=ADOQuery1.Fieldbyname('nFsCount').AsString;

Cell1.DoDrawLine(0,Cell1.Rows-1,Cell1.Cols-1,Cell1.Rows-1,0,1,1);
ADOQuery1.next;
ProcessBarForm.Gauge1.Progress:=i;
ProcessBarForm.Update;
end;
ProcessBarForm.Free;
Cell1.Rows :=Cell1.Rows+1;
Cell1.DoDrawLine(0,Cell1.Rows-1,Cell1.Cols-1,Cell1.Rows-1,0,1,1);
Cell1.DoJoinCells(0,Cell1.Rows-1,2,Cell1.Rows-1);
Cell1.DoSetCellString(0,Cell1.Rows-1,'合计：');


   { FOR I:=5 TO  9 do //query2.recordcount+3 DO
    if A[I]<>0 then
         begin
         iHsl:= A[I]/Mcol;
         sHsl := FloattoStr(Round(iHsl*100));
         if Pos('.',sHSL) > 0 then
         Delete(sHSL,Pos('.',sHSL),Length(sHSL));
         sHsl := sHSL+'%';
         Cell1.DoSetCellSTRING(I,cell1.rows-1,SHsl);
         end ;
         }

//设置只读 设置字体
    for i := 0 to Cell1.Cols  do
       for j :=1 to cell1.rows-1 do
       begin
       Cell1.DoSetCellReadOnly(i,j,true);
       Cell1. DoSetCellTextStyle(i,j,1);
       Cell1.DoSetCellAlignment(i,j,100);
       Cell1.DoSetCellFont(i,j,8,0,'宋体');
       end;
       Cell1.DoSetCellAlignment(6,2,1);
       Cell1.DoSetCellAlignment(0,2,1);

    for i := 3 to Cell1.Cols  do
       for j :=5 to cell1.rows-1 do
       begin
       Cell1.DoSetCellAlignment(i,j,2);
       Cell1.DoSetCellAlignment(0,j,1);
       end;
       Cell1.DoSetCellAlignment(0,cell1.rows-1,100);
       for i:=0 to Cell1.cols - 1 do
       Cell1.DoSetCellColor(i,cell1.rows-1,0,rgb(255,255,128));
       Cell1.DoSetSkipReadOnlyCell(0);
       Cell1.DoRedrawAll;
end;
procedure TfrmRKLYJC.FormShow(Sender: TObject);
begin
  inherited;
ToolbarButton975Click(NIL);
 IF EXITFLAG THEN
  CLOSE;
 SELF.ONSHOW:=NIL;
end;

procedure TfrmRKLYJC.FormCreate(Sender: TObject);
begin
  inherited;
  aPrintCol:=60;
  aPrintRow:=43;
  aStartCol:=1;
  aStartRow:=5;
//  STARTROW:=7;
//  STARTCOL:=1;
end;

procedure TfrmRKLYJC.SpeedButton1Click(Sender: TObject);
begin
  inherited;
	PrintCell(False,False);
end;

end.