252 lines
11 KiB
ObjectPascal
252 lines
11 KiB
ObjectPascal
|
unit U_JYOrderYS;
|
|||
|
|
|
|||
|
|
interface
|
|||
|
|
|
|||
|
|
uses
|
|||
|
|
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
|
|||
|
|
Dialogs, ComCtrls, ToolWin, ExtCtrls, StdCtrls, cxStyles, cxCustomData,
|
|||
|
|
cxGraphics, cxFilter, cxData, cxDataStorage, cxEdit, DB, cxDBData,
|
|||
|
|
cxCalendar, cxButtonEdit, cxTextEdit, cxGridLevel, cxGridCustomTableView,
|
|||
|
|
cxGridTableView, cxGridDBTableView, cxClasses, cxControls,
|
|||
|
|
cxGridCustomView, cxGrid, cxGridBandedTableView, cxGridDBBandedTableView,
|
|||
|
|
DBClient, ADODB, cxCurrencyEdit;
|
|||
|
|
|
|||
|
|
type
|
|||
|
|
TFrmJYOrderYS = class(TForm)
|
|||
|
|
Panel1: TPanel;
|
|||
|
|
Label2: TLabel;
|
|||
|
|
KHName: TEdit;
|
|||
|
|
Label3: TLabel;
|
|||
|
|
OrderNo: TEdit;
|
|||
|
|
Panel2: TPanel;
|
|||
|
|
cxGrid1: TcxGrid;
|
|||
|
|
cxGridLevel1: TcxGridLevel;
|
|||
|
|
TV1: TcxGridDBBandedTableView;
|
|||
|
|
ToolBar1: TToolBar;
|
|||
|
|
TBClose: TToolButton;
|
|||
|
|
V1CRTime: TcxGridDBBandedColumn;
|
|||
|
|
V1P_CodeName: TcxGridDBBandedColumn;
|
|||
|
|
V1TTPS: TcxGridDBBandedColumn;
|
|||
|
|
V1TTQty: TcxGridDBBandedColumn;
|
|||
|
|
V1Price: TcxGridDBBandedColumn;
|
|||
|
|
V1PBMoney: TcxGridDBBandedColumn;
|
|||
|
|
Panel3: TPanel;
|
|||
|
|
Panel4: TPanel;
|
|||
|
|
V1KCPBQty: TcxGridDBBandedColumn;
|
|||
|
|
V1KCPBPS: TcxGridDBBandedColumn;
|
|||
|
|
DataSource1: TDataSource;
|
|||
|
|
DataSource2: TDataSource;
|
|||
|
|
DataSource4: TDataSource;
|
|||
|
|
DataSource5: TDataSource;
|
|||
|
|
Order_PB: TClientDataSet;
|
|||
|
|
Order_RF: TClientDataSet;
|
|||
|
|
Order_XS: TClientDataSet;
|
|||
|
|
Order_FYZC: TClientDataSet;
|
|||
|
|
ADOQuery1: TADOQuery;
|
|||
|
|
ADOQuery2: TADOQuery;
|
|||
|
|
ADOQuery4: TADOQuery;
|
|||
|
|
cxGrid2: TcxGrid;
|
|||
|
|
TV2: TcxGridDBBandedTableView;
|
|||
|
|
cxGridDBBandedColumn1: TcxGridDBBandedColumn;
|
|||
|
|
cxGridDBBandedColumn2: TcxGridDBBandedColumn;
|
|||
|
|
V2Column1: TcxGridDBBandedColumn;
|
|||
|
|
cxGridDBBandedColumn3: TcxGridDBBandedColumn;
|
|||
|
|
cxGridDBBandedColumn4: TcxGridDBBandedColumn;
|
|||
|
|
cxGridDBBandedColumn5: TcxGridDBBandedColumn;
|
|||
|
|
cxGridDBBandedColumn6: TcxGridDBBandedColumn;
|
|||
|
|
V2FactoryName: TcxGridDBBandedColumn;
|
|||
|
|
cxGridLevel2: TcxGridLevel;
|
|||
|
|
Panel5: TPanel;
|
|||
|
|
cxGrid5: TcxGrid;
|
|||
|
|
TV5: TcxGridDBBandedTableView;
|
|||
|
|
V5CRTime: TcxGridDBBandedColumn;
|
|||
|
|
V5YFName: TcxGridDBBandedColumn;
|
|||
|
|
V5RMBMoney: TcxGridDBBandedColumn;
|
|||
|
|
cxGridLevel5: TcxGridLevel;
|
|||
|
|
Label1: TLabel;
|
|||
|
|
ConNo: TEdit;
|
|||
|
|
cxGrid4: TcxGrid;
|
|||
|
|
TV4: TcxGridDBBandedTableView;
|
|||
|
|
cxGridDBBandedColumn15: TcxGridDBBandedColumn;
|
|||
|
|
cxGridDBBandedColumn16: TcxGridDBBandedColumn;
|
|||
|
|
V4PRTColor: TcxGridDBBandedColumn;
|
|||
|
|
cxGridDBBandedColumn17: TcxGridDBBandedColumn;
|
|||
|
|
cxGridDBBandedColumn18: TcxGridDBBandedColumn;
|
|||
|
|
cxGridDBBandedColumn19: TcxGridDBBandedColumn;
|
|||
|
|
cxGridDBBandedColumn20: TcxGridDBBandedColumn;
|
|||
|
|
V4Column9: TcxGridDBBandedColumn;
|
|||
|
|
V4Column11: TcxGridDBBandedColumn;
|
|||
|
|
cxGridLevel4: TcxGridLevel;
|
|||
|
|
cxGrid3: TcxGrid;
|
|||
|
|
TV3: TcxGridDBBandedTableView;
|
|||
|
|
cxGridDBBandedColumn8: TcxGridDBBandedColumn;
|
|||
|
|
cxGridDBBandedColumn9: TcxGridDBBandedColumn;
|
|||
|
|
cxGridDBBandedColumn10: TcxGridDBBandedColumn;
|
|||
|
|
cxGridLevel3: TcxGridLevel;
|
|||
|
|
DataSource3: TDataSource;
|
|||
|
|
ADOQuery3: TADOQuery;
|
|||
|
|
Order_SK: TClientDataSet;
|
|||
|
|
V5BZType: TcxGridDBBandedColumn;
|
|||
|
|
V3BZType: TcxGridDBBandedColumn;
|
|||
|
|
V3Huilv: TcxGridDBBandedColumn;
|
|||
|
|
Label4: TLabel;
|
|||
|
|
MaoLMoney: TEdit;
|
|||
|
|
Label5: TLabel;
|
|||
|
|
YGMoney: TEdit;
|
|||
|
|
V1FactoryName: TcxGridDBBandedColumn;
|
|||
|
|
V5Note: TcxGridDBBandedColumn;
|
|||
|
|
V4Huilv: TcxGridDBBandedColumn;
|
|||
|
|
procedure FormClose(Sender: TObject; var Action: TCloseAction);
|
|||
|
|
procedure FormDestroy(Sender: TObject);
|
|||
|
|
procedure FormShow(Sender: TObject);
|
|||
|
|
procedure TBCloseClick(Sender: TObject);
|
|||
|
|
private
|
|||
|
|
procedure initgrid();
|
|||
|
|
{ Private declarations }
|
|||
|
|
public
|
|||
|
|
FMainid,FConnO,FYWY,FKHName,FMaoLMoney,FYGMoney: string; //<2F><><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ͬ
|
|||
|
|
{ Public declarations }
|
|||
|
|
end;
|
|||
|
|
|
|||
|
|
var
|
|||
|
|
FrmJYOrderYS: TFrmJYOrderYS;
|
|||
|
|
|
|||
|
|
implementation
|
|||
|
|
Uses
|
|||
|
|
U_DataLink,U_ZDYHelp,U_Fun;
|
|||
|
|
{$R *.dfm}
|
|||
|
|
|
|||
|
|
procedure TFrmJYOrderYS.initgrid();
|
|||
|
|
var FSKMoney,FPBMoney,FJGMoney,FZCMoney:double;
|
|||
|
|
begin
|
|||
|
|
with Adoquery1 do
|
|||
|
|
begin
|
|||
|
|
close;
|
|||
|
|
sql.clear;
|
|||
|
|
sql.add('select A.CRTime,AA.FactoryName,AA.SPName P_CodeName,Sum(A.PiQty) TTPS,Sum(A.Qty) TTQty,Max(AA.Price) Price,Sum(isnull(A.Qty,SJRKQty)*AA.Price) PBMoney ');
|
|||
|
|
sql.Add('from (select isnull(C.OrdMainidRK,Q.Mainid) OrdMainid,C.TOFactoryName,C.SPName,C.FactoryName,X.Price,Sum(X.Qty) SJRKQty,Sum(C.Qty) RKQty,SUM(X.Money) PBMoney from YF_Money_CR X ');
|
|||
|
|
sql.Add(' left join JYOrder_Main Q on Q.OrderNO=X.OrderNo ');
|
|||
|
|
sql.Add(' left join CK_SXPB_CR C on X.YFTypeId=C.SPID and C.CRFlag=''<27><><EFBFBD><EFBFBD>'' ');
|
|||
|
|
sql.Add(' where X.CRType=''Ӧ<><D3A6><EFBFBD><EFBFBD><EFBFBD>Ǽ<EFBFBD>'' and X.FYType=''<27><><EFBFBD><EFBFBD><EFBFBD><EFBFBD>'' and isnull(X.status,'''')<>''0''');
|
|||
|
|
sql.Add(' Group by isnull(C.OrdMainidRK,Q.Mainid),C.TOFactoryName,C.SPName,C.FactoryName,X.Price) AA ');
|
|||
|
|
sql.Add(' left join CK_SXPB_CR A on A.OrdMainidRK=AA.OrdMainid and A.FromFactoryName=AA.TOFactoryName and A.CRFlag=''<27><><EFBFBD><EFBFBD>'' and A.FactoryName=AA.FactoryName and A.CKName=''<27><><EFBFBD><EFBFBD>'' ');
|
|||
|
|
sql.Add(' where isnull(A.OrdMainidCK,AA.OrdMainid)='''+trim(FConNo)+'''');
|
|||
|
|
sql.Add(' group by A.CRTime,AA.FactoryName,AA.SPName');
|
|||
|
|
open;
|
|||
|
|
end;
|
|||
|
|
SCreateCDS20(Adoquery1,Order_PB);
|
|||
|
|
SInitCDSData20(Adoquery1,Order_PB);
|
|||
|
|
with Adoquery1 do
|
|||
|
|
begin
|
|||
|
|
close;
|
|||
|
|
sql.clear;
|
|||
|
|
sql.add('select A.CRTime,AA.FactoryName,AA.SPName P_CodeName,AA.PRTColor,Sum(A.PiQty) JGPS,Sum(A.Qty) JGQty,Max(AA.Price) Price,Sum(isnull(A.Qty,SJRKQty)*AA.Price) JGMoney ');
|
|||
|
|
sql.Add('from (select isnull(C.OrdMainidRK,Q.Mainid) OrdMainid,C.TOFactoryName,C.SPName,C.FactoryName,U.Subid,U.PRTColor,X.Price,C.FromGX,Sum(X.Qty) SJRKQty,Sum(C.Qty) RKQty,SUM(X.Money) PBMoney from YF_Money_CR X ');
|
|||
|
|
sql.Add(' left join JYOrder_Main Q on Q.OrderNO=X.OrderNo ');
|
|||
|
|
sql.Add(' left join CK_SXPB_CR C on X.YFTypeId=C.SPID and C.CRFlag=''<27><><EFBFBD><EFBFBD>'' ');
|
|||
|
|
sql.Add(' left join JYOrder_Sub U on U.Subid=isnull(C.OrdSubidRK,X.ZSDSubid)');
|
|||
|
|
sql.Add(' where X.CRType=''Ӧ<><D3A6><EFBFBD><EFBFBD><EFBFBD>Ǽ<EFBFBD>'' and X.FYType=''<27>ӹ<EFBFBD><D3B9><EFBFBD>'' and isnull(X.status,'''')<>''0''');
|
|||
|
|
sql.Add(' Group by isnull(C.OrdMainidRK,Q.Mainid),C.TOFactoryName,C.SPName,C.FactoryName,U.Subid,U.PRTColor,X.Price,C.FromGX) AA ');
|
|||
|
|
sql.Add(' left join CK_SXPB_CR A on A.OrdMainidRK=AA.OrdMainid and A.OrdSubidRK=AA.Subid and A.FromFactoryName=AA.TOFactoryName and A.FromGX=AA.FromGX and A.CRFlag=''<27><><EFBFBD><EFBFBD>'' and A.FactoryName=AA.FactoryName and A.CKName=''<27><><EFBFBD>ӹ<EFBFBD>'' ');
|
|||
|
|
sql.Add(' where isnull(A.OrdMainidCK,AA.OrdMainid)='''+trim(FConNo)+'''');
|
|||
|
|
sql.Add(' group by A.CRTime,AA.FactoryName,AA.SPName,AA.PRTColor');
|
|||
|
|
open;
|
|||
|
|
end;
|
|||
|
|
SCreateCDS20(Adoquery1,Order_RF);
|
|||
|
|
SInitCDSData20(Adoquery1,Order_RF);
|
|||
|
|
with AdoQuery1 do
|
|||
|
|
begin
|
|||
|
|
close;
|
|||
|
|
sql.clear;
|
|||
|
|
sql.add('select A.ConNo,A.CRTime,A.P_CodeName,isnull(E.PRTColor,E.SOrdDefStr4) PRTColor,A.BZType,A.Price');
|
|||
|
|
sql.add(',Sum(A.PS),Sum(A.Qty)');
|
|||
|
|
sql.add(',Huilv=isnull((select top 1 T.HuiLV from JYOrder_HuiLV T where A.CRTime>=T.BegDate and A.CRTime<isnull(T.EndDate,A.CRTime+1) and A.BZType<>''<27><>'' and A.BZType<>''RMB'' and T.GSType=A.GSType),1)');
|
|||
|
|
sql.add(',RMBMoney=cast(Sum(A.Money)*isnull((select TOP 1 C.HuiLV From JYOrder_HuiLV C where A.CRTime>=C.BegDate and A.CRTime<isnull(C.EndDate,A.CRTime+1) and A.BZType<>''<27><>'' and A.BZType<>''RMB'' and C.GSType=A.GSType),1) as decimal(18,2))');
|
|||
|
|
sql.Add(',Sum(case when (A.BZType<>''<27><>'') and (A.BZType<>''RMB'') then A.Money else 0 end) USDMoney');
|
|||
|
|
sql.add(' from YF_Money_CR A ');
|
|||
|
|
sql.Add(' left join JYOrder_Main P on P.OrderNo=A.OrderNo');
|
|||
|
|
sql.Add(' left join CK_SXPB_CR C on A.YFTypeId=C.SPID and C.CRFlag=''<27><><EFBFBD><EFBFBD>'' ');
|
|||
|
|
sql.Add(' left join JYOrder_Main B on B.Mainid=C.OrdMainidCK');
|
|||
|
|
sql.Add(' left join JYOrder_Sub E on E.Subid=isnull(C.OrdSubidCK,A.Subid)');
|
|||
|
|
sql.add(' where isnull(B.Mainid,P.Mainid)='''+trim(FConNo)+''' and A.CRType=''Ӧ<>տ<EFBFBD><D5BF>Ǽ<EFBFBD>'' ');
|
|||
|
|
sql.add(' and A.CRFlag=''Ӧ<><D3A6><EFBFBD><EFBFBD>'' and A.Status<>''0''');
|
|||
|
|
sql.add(' group by A.ConNo,A.CRTime,A.P_CodeName,E.PRTColor,E.SOrdDefStr4,A.BZType,A.Price,A.GSType'); //<2F><><EFBFBD>۲<EFBFBD>
|
|||
|
|
open;
|
|||
|
|
end;
|
|||
|
|
SCreateCDS20(Adoquery1,Order_XS);
|
|||
|
|
SInitCDSData20(Adoquery1,Order_XS);
|
|||
|
|
with AdoQuery1 do
|
|||
|
|
begin
|
|||
|
|
close;
|
|||
|
|
sql.clear;
|
|||
|
|
sql.add('select B.ConNo,A.CRTime,A.BZType');
|
|||
|
|
sql.add(',Huilv=isnull((select top 1 C.HuiLV from JYOrder_HuiLV C where A.CRTime>=C.BegDate and A.CRTime<isnull(C.EndDate,A.CRTime+1) and A.BZType<>''<27><>'' and A.BZType<>''RMB'' and C.GSType=A.GSType),1)');
|
|||
|
|
sql.add(',RMBMoney=cast((sum(E.Num)*isnull((select TOP 1 C.HuiLV From JYOrder_HuiLV C where A.CRTime>=C.BegDate and A.CRTime<isnull(C.EndDate,A.CRTime+1) and A.BZType<>''<27><>'' and A.BZType<>''RMB'' and C.GSType=A.GSType),1)) as decimal(18,2))');
|
|||
|
|
sql.add(',USDMoney=(case when (A.BZType<>''<27><>'') and (A.BZType<>''RMB'') then sum(E.Num) else 0 end)');
|
|||
|
|
sql.add('from YF_Money_CR A ');
|
|||
|
|
sql.add('inner join YF_log E on E.ListNo=A.YFID ');
|
|||
|
|
sql.Add('inner join YF_Money_CR B on B.YFID=E.ToNo and B.CRType=''Ӧ<>տ<EFBFBD><D5BF>Ǽ<EFBFBD>'' ');
|
|||
|
|
sql.Add('inner join JYOrder_Main C on C.OrderNO=B.OrderNO ');
|
|||
|
|
sql.add('where C.Mainid='''+trim(FConNo)+''' and A.CRType=''<27>տ<EFBFBD><D5BF>Ǽ<EFBFBD>'' ');
|
|||
|
|
sql.Add(' and A.status<>''0''');
|
|||
|
|
sql.add('group by B.ConNo,A.CRTime,A.BZType,A.GSType');
|
|||
|
|
open;
|
|||
|
|
end;
|
|||
|
|
SCreateCDS20(Adoquery1,Order_SK);
|
|||
|
|
SInitCDSData20(Adoquery1,Order_SK);
|
|||
|
|
with AdoQuery1 do
|
|||
|
|
begin
|
|||
|
|
close;
|
|||
|
|
sql.clear;
|
|||
|
|
sql.add('select A.CRTime,A.YFName,A.BZType,A.Note ');
|
|||
|
|
sql.add(',RMBMoney=(Sum(A.Money)*isnull((select top 1 C.HuiLV from JYOrder_HuiLV C where A.CRTime>=C.BegDate and A.CRTime<isnull(C.EndDate,A.CRTime+1)');
|
|||
|
|
sql.Add(' and A.BZType<>''<27><>'' and A.BZType<>''RMB'' and C.GSType=A.GSType),1))');
|
|||
|
|
sql.add(' from YF_Money_CR A ');
|
|||
|
|
sql.add(' left join JYOrder_Main D on D.OrderNo=A.OrderNo');
|
|||
|
|
sql.add(' where D.Mainid='''+trim(FConNo)+''' and A.CRType in (''Ӧ<><D3A6><EFBFBD><EFBFBD><EFBFBD>Ǽ<EFBFBD>'',''<27><><EFBFBD><EFBFBD><EFBFBD>ӷ<EFBFBD>'') and isnull(A.FYType,'''')<>''<27>ӹ<EFBFBD><D3B9><EFBFBD>'' and isnull(A.FYType,'''')<>''<27><><EFBFBD><EFBFBD><EFBFBD><EFBFBD>'' ');
|
|||
|
|
sql.Add(' and A.status<>''0''');
|
|||
|
|
sql.add(' group by A.CRTime,A.YFName,A.BZType,A.GSType,A.Note');
|
|||
|
|
open;
|
|||
|
|
end;
|
|||
|
|
SCreateCDS20(Adoquery1,Order_FYZC);
|
|||
|
|
SInitCDSData20(Adoquery1,Order_FYZC);
|
|||
|
|
end;
|
|||
|
|
|
|||
|
|
procedure TFrmJYOrderYS.FormClose(Sender: TObject;
|
|||
|
|
var Action: TCloseAction);
|
|||
|
|
begin
|
|||
|
|
Action:=caFree;
|
|||
|
|
end;
|
|||
|
|
|
|||
|
|
procedure TFrmJYOrderYS.FormDestroy(Sender: TObject);
|
|||
|
|
begin
|
|||
|
|
FrmJYOrderYS:=nil;
|
|||
|
|
end;
|
|||
|
|
|
|||
|
|
procedure TFrmJYOrderYS.FormShow(Sender: TObject);
|
|||
|
|
begin
|
|||
|
|
ReadCxBandedGrid('<27><><EFBFBD><EFBFBD>1',Tv1,'<27><><EFBFBD><EFBFBD><EFBFBD>ո<EFBFBD><D5B8><EFBFBD><EFBFBD><EFBFBD>ϸ');
|
|||
|
|
ReadCxBandedGrid('Ⱦ<><C8BE>1',Tv2,'<27><><EFBFBD><EFBFBD><EFBFBD>ո<EFBFBD><D5B8><EFBFBD><EFBFBD><EFBFBD>ϸ');
|
|||
|
|
ReadCxBandedGrid('<27>տ<EFBFBD>1',Tv3,'<27><><EFBFBD><EFBFBD><EFBFBD>ո<EFBFBD><D5B8><EFBFBD><EFBFBD><EFBFBD>ϸ');
|
|||
|
|
ReadCxBandedGrid('<27><><EFBFBD><EFBFBD>֧<EFBFBD><D6A7>1',Tv5,'<27><><EFBFBD><EFBFBD><EFBFBD>ո<EFBFBD><D5B8><EFBFBD><EFBFBD><EFBFBD>ϸ');
|
|||
|
|
ReadCxBandedGrid('<27><><EFBFBD>۳<EFBFBD><DBB3><EFBFBD>1',Tv4,'<27><><EFBFBD><EFBFBD><EFBFBD>ո<EFBFBD><D5B8><EFBFBD><EFBFBD><EFBFBD>ϸ');
|
|||
|
|
ConNo.Text:=trim(FConnO);
|
|||
|
|
OrderNo.Text:=trim(FYWY);
|
|||
|
|
KHName.Text:=trim(FKHName);
|
|||
|
|
MaoLMoney.Text:=trim(FMaoLMoney);
|
|||
|
|
YGMoney.Text:=trim(FYGMoney);
|
|||
|
|
initgrid();
|
|||
|
|
end;
|
|||
|
|
|
|||
|
|
procedure TFrmJYOrderYS.TBCloseClick(Sender: TObject);
|
|||
|
|
begin
|
|||
|
|
WriteCxBandedGrid('<27><><EFBFBD><EFBFBD>1',Tv1,'<27><><EFBFBD><EFBFBD><EFBFBD>ո<EFBFBD><D5B8><EFBFBD><EFBFBD><EFBFBD>ϸ');
|
|||
|
|
WriteCxBandedGrid('Ⱦ<><C8BE>1',Tv2,'<27><><EFBFBD><EFBFBD><EFBFBD>ո<EFBFBD><D5B8><EFBFBD><EFBFBD><EFBFBD>ϸ');
|
|||
|
|
WriteCxBandedGrid('<27>տ<EFBFBD>1',Tv3,'<27><><EFBFBD><EFBFBD><EFBFBD>ո<EFBFBD><D5B8><EFBFBD><EFBFBD><EFBFBD>ϸ');
|
|||
|
|
WriteCxBandedGrid('<27><><EFBFBD><EFBFBD>֧<EFBFBD><D6A7>1',Tv5,'<27><><EFBFBD><EFBFBD><EFBFBD>ո<EFBFBD><D5B8><EFBFBD><EFBFBD><EFBFBD>ϸ');
|
|||
|
|
WriteCxBandedGrid('<27><><EFBFBD>۳<EFBFBD><DBB3><EFBFBD>1',Tv4,'<27><><EFBFBD><EFBFBD><EFBFBD>ո<EFBFBD><D5B8><EFBFBD><EFBFBD><EFBFBD>ϸ');
|
|||
|
|
Close;
|
|||
|
|
end;
|
|||
|
|
|
|||
|
|
end.
|