300 lines
11 KiB
ObjectPascal
300 lines
11 KiB
ObjectPascal
|
unit U_ContractListXF_LSCX;
|
|||
|
|
|
|||
|
|
interface
|
|||
|
|
|
|||
|
|
uses
|
|||
|
|
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
|
|||
|
|
Dialogs, StdCtrls, ExtCtrls, ComCtrls, ToolWin, cxStyles, cxCustomData,
|
|||
|
|
cxGraphics, cxFilter, cxData, cxDataStorage, cxEdit, DB, cxDBData, ADODB,
|
|||
|
|
cxGridCustomPopupMenu, cxGridPopupMenu, cxGridLevel, cxClasses,
|
|||
|
|
cxControls, cxGridCustomView, cxGridCustomTableView, cxGridTableView,
|
|||
|
|
cxGridDBTableView, cxGrid, DBClient, cxCheckBox, cxCalendar, cxSplitter,
|
|||
|
|
RM_Dataset, RM_System, RM_Common, RM_Class, RM_GridReport, RM_e_Xls,
|
|||
|
|
Menus, cxPC, cxButtonEdit, cxTextEdit, cxCurrencyEdit, RM_e_Graphic,
|
|||
|
|
RM_e_Jpeg, cxLookAndFeels, cxLookAndFeelPainters, cxNavigator;
|
|||
|
|
|
|||
|
|
type
|
|||
|
|
TfrmContractList_XF_LSCX = class(TForm)
|
|||
|
|
ToolBar1: TToolBar;
|
|||
|
|
TBRafresh: TToolButton;
|
|||
|
|
TBFind: TToolButton;
|
|||
|
|
TBClose: TToolButton;
|
|||
|
|
Tv1: TcxGridDBTableView;
|
|||
|
|
cxGrid1Level1: TcxGridLevel;
|
|||
|
|
cxGrid1: TcxGrid;
|
|||
|
|
cxGridPopupMenu1: TcxGridPopupMenu;
|
|||
|
|
ADOQueryCmd: TADOQuery;
|
|||
|
|
ADOQueryMain: TADOQuery;
|
|||
|
|
ADOQueryTemp: TADOQuery;
|
|||
|
|
DataSource1: TDataSource;
|
|||
|
|
TBExport: TToolButton;
|
|||
|
|
v1YWY: TcxGridDBColumn;
|
|||
|
|
Order_Main: TClientDataSet;
|
|||
|
|
v1YZFLQty: TcxGridDBColumn;
|
|||
|
|
Panel1: TPanel;
|
|||
|
|
Label9: TLabel;
|
|||
|
|
YWY: TEdit;
|
|||
|
|
Label7: TLabel;
|
|||
|
|
GCName: TEdit;
|
|||
|
|
v1GCName: TcxGridDBColumn;
|
|||
|
|
Label2: TLabel;
|
|||
|
|
CustomerNoName: TEdit;
|
|||
|
|
v1CustomerNoName: TcxGridDBColumn;
|
|||
|
|
vXSPrice: TcxGridDBColumn;
|
|||
|
|
vNote: TcxGridDBColumn;
|
|||
|
|
v1SKMoney: TcxGridDBColumn;
|
|||
|
|
Label1: TLabel;
|
|||
|
|
Label3: TLabel;
|
|||
|
|
v1SYuQty: TcxGridDBColumn;
|
|||
|
|
Note: TMemo;
|
|||
|
|
FKType: TMemo;
|
|||
|
|
v1QDDate: TcxGridDBColumn;
|
|||
|
|
v1KZFLQty: TcxGridDBColumn;
|
|||
|
|
v1FKType: TcxGridDBColumn;
|
|||
|
|
v1Column1: TcxGridDBColumn;
|
|||
|
|
v1Column2: TcxGridDBColumn;
|
|||
|
|
v1Column3: TcxGridDBColumn;
|
|||
|
|
v1Column4: TcxGridDBColumn;
|
|||
|
|
v1Column5: TcxGridDBColumn;
|
|||
|
|
Label4: TLabel;
|
|||
|
|
BegDate: TDateTimePicker;
|
|||
|
|
EndDate: TDateTimePicker;
|
|||
|
|
Label5: TLabel;
|
|||
|
|
CheckBox1: TCheckBox;
|
|||
|
|
procedure FormDestroy(Sender: TObject);
|
|||
|
|
procedure FormClose(Sender: TObject; var Action: TCloseAction);
|
|||
|
|
procedure FormCreate(Sender: TObject);
|
|||
|
|
procedure TBCloseClick(Sender: TObject);
|
|||
|
|
procedure TBFindClick(Sender: TObject);
|
|||
|
|
procedure TBExportClick(Sender: TObject);
|
|||
|
|
procedure TBRafreshClick(Sender: TObject);
|
|||
|
|
procedure FormShow(Sender: TObject);
|
|||
|
|
procedure CheckBox1Click(Sender: TObject);
|
|||
|
|
procedure CheckBox2Click(Sender: TObject);
|
|||
|
|
procedure CustomerNoChange(Sender: TObject);
|
|||
|
|
procedure Tv1FocusedRecordChanged(Sender: TcxCustomGridTableView;
|
|||
|
|
APrevFocusedRecord, AFocusedRecord: TcxCustomGridRecord;
|
|||
|
|
ANewItemRecordFocusingChanged: Boolean);
|
|||
|
|
procedure v1Column3CustomDrawCell(Sender: TcxCustomGridTableView;
|
|||
|
|
ACanvas: TcxCanvas; AViewInfo: TcxGridTableDataCellViewInfo;
|
|||
|
|
var ADone: Boolean);
|
|||
|
|
procedure GCNameKeyPress(Sender: TObject; var Key: Char);
|
|||
|
|
private
|
|||
|
|
procedure InitGrid();
|
|||
|
|
procedure InitForm();
|
|||
|
|
{ Private declarations }
|
|||
|
|
public
|
|||
|
|
{ Public declarations }
|
|||
|
|
end;
|
|||
|
|
|
|||
|
|
var
|
|||
|
|
frmContractList_XF_LSCX: TfrmContractList_XF_LSCX;
|
|||
|
|
|
|||
|
|
implementation
|
|||
|
|
uses
|
|||
|
|
U_DataLink,U_Fun;
|
|||
|
|
|
|||
|
|
{$R *.dfm}
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
procedure TfrmContractList_XF_LSCX.FormDestroy(Sender: TObject);
|
|||
|
|
begin
|
|||
|
|
frmContractList_XF_LSCX:=nil;
|
|||
|
|
end;
|
|||
|
|
|
|||
|
|
procedure TfrmContractList_XF_LSCX.FormClose(Sender: TObject;
|
|||
|
|
var Action: TCloseAction);
|
|||
|
|
begin
|
|||
|
|
Action:=caFree;
|
|||
|
|
end;
|
|||
|
|
|
|||
|
|
procedure TfrmContractList_XF_LSCX.FormCreate(Sender: TObject);
|
|||
|
|
begin
|
|||
|
|
cxgrid1.Align:=alClient;
|
|||
|
|
end;
|
|||
|
|
|
|||
|
|
procedure TfrmContractList_XF_LSCX.TBCloseClick(Sender: TObject);
|
|||
|
|
begin
|
|||
|
|
Close;
|
|||
|
|
// if FCloth<>1 then
|
|||
|
|
WriteCxGrid('С<><D0A1><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ѯSF',Tv1,'<27><><EFBFBD><EFBFBD>ָʾ<D6B8><CABE><EFBFBD><EFBFBD><EFBFBD><EFBFBD>');
|
|||
|
|
// else
|
|||
|
|
//WriteCxGrid('С<><D0A1><EFBFBD><EFBFBD><EFBFBD>б<EFBFBD>ѡ<EFBFBD><D1A1>1',Tv1,'<27><><EFBFBD><EFBFBD>ָʾ<D6B8><CABE><EFBFBD><EFBFBD><EFBFBD><EFBFBD>');
|
|||
|
|
end;
|
|||
|
|
|
|||
|
|
procedure TfrmContractList_XF_LSCX.InitGrid();
|
|||
|
|
var i:Integer;
|
|||
|
|
begin
|
|||
|
|
try
|
|||
|
|
ADOQueryMain.DisableControls;
|
|||
|
|
with ADOQueryMain do
|
|||
|
|
begin
|
|||
|
|
Filtered:=False;
|
|||
|
|
Close;
|
|||
|
|
sql.Clear;
|
|||
|
|
sql.Add('select AA.*,KZFLQty=Cast((case when isnull(YSMoney,0)=0 then 0 else SKMoney*HZConQty/YSMoney end) as decimal(18,2))');
|
|||
|
|
sql.Add(',SYMoney=isnull(SKMoney,0)-isnull(FHMoney,0)');
|
|||
|
|
sql.Add(',SYuMaxQty=Cast(Case when isnull(XSMinPrice,0)=0 then 0 else (isnull(SKMoney,0)-isnull(FHMoney,0))/XSMinPrice end as decimal(18,2))');
|
|||
|
|
sql.Add(',SYuMinQty=Cast(Case when isnull(XSMaxPrice,0)=0 then 0 else (isnull(SKMoney,0)-isnull(FHMoney,0))/XSMaxPrice end as decimal(18,2))');
|
|||
|
|
sql.add(' from (select A.* ');
|
|||
|
|
sql.Add(',XSMaxPrice=(select Max(ShiJiPrice) from JYOrderCon_Sub B where B.MainId=A.MainId)');
|
|||
|
|
sql.Add(',XSMinPrice=(select Min(ShiJiPrice) from JYOrderCon_Sub B where B.MainId=A.MainId)');
|
|||
|
|
sql.add(',SKMoney=(select Sum(Money) from YF_Money_CR B where B.FactoryName=A.CustomerNoName and B.GCName=A.GCName and B.CRType=''<27>տ<EFBFBD><D5BF>Ǽ<EFBFBD>'' and isnull(B.Status,''0'')>0)');
|
|||
|
|
sql.Add(',YZFLQty=(select Sum(B.OrdFLQty) from JYOrder_Main B where B.ConNo=A.ConNO )');
|
|||
|
|
sql.Add(',YSMoney=(select Sum(isnull(ShijiPrice,0)*isnull(FLQty,0)+isnull(BengSongFee,0)) from JYOrderCon_Sub B where B.MainId=A.MainId )');
|
|||
|
|
sql.Add(',HZConQty=(select Sum(isnull(FLQty,0)) from JYOrderCon_Sub B where B.MainId=A.MainId )');
|
|||
|
|
sql.add(',FHQty=(select Sum(Qty) from YF_Money_CR B where B.FactoryName=A.CustomerNoName and B.GCName=A.GCName and B.CRType=''Ӧ<>տ<EFBFBD><D5BF>Ǽ<EFBFBD>'')');
|
|||
|
|
sql.add(',FHMoney=(select Sum(Money) from YF_Money_CR B where B.FactoryName=A.CustomerNoName and B.GCName=A.GCName and B.CRType=''Ӧ<>տ<EFBFBD><D5BF>Ǽ<EFBFBD>'')');
|
|||
|
|
sql.Add(' from JYOrderCon_Main A ');
|
|||
|
|
SQL.Add(' where A.ConType in(''<27><>ʱ<EFBFBD><CAB1>ͬ'',''<27><>ʱ<EFBFBD><CAB1>ͬBKP'') and isnull(A.status,''0'')>''0'' and isnull(A.status,''0'')<''3'' ');
|
|||
|
|
if CheckBox1.Checked=True then
|
|||
|
|
begin
|
|||
|
|
sql.Add(' and A.QDDate>='''+Trim(FormatDateTime('yyyy-MM-dd',BegDate.Date))+'''');
|
|||
|
|
sql.Add(' and A.QDDate<'''+Trim(FormatDateTime('yyyy-MM-dd',EndDate.Date+1))+'''');
|
|||
|
|
end;
|
|||
|
|
sql.Add(' ) AA');
|
|||
|
|
sql.Add(' order by AA.QDDate');
|
|||
|
|
Open;
|
|||
|
|
end;
|
|||
|
|
SCreateCDS20(ADOQueryMain,Order_Main);
|
|||
|
|
SInitCDSData20(ADOQueryMain,Order_Main);
|
|||
|
|
finally;
|
|||
|
|
ADOQueryMain.EnableControls;
|
|||
|
|
end;
|
|||
|
|
//sql.Add(',YZFLQty=(select Sum(B.CPFLQty) from CK_CP_CR B where B.ConNo=A.ConNO and B.CRType=''<27><><EFBFBD>۳<EFBFBD><DBB3><EFBFBD>'')');
|
|||
|
|
//SQL.Add(',XSPrice=(select Max(JSPrice) from JYOrderCon_Sub B where B.MainId=A.MainId)');
|
|||
|
|
end;
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
procedure TfrmContractList_XF_LSCX.InitForm();
|
|||
|
|
begin
|
|||
|
|
ReadCxGrid('С<><D0A1><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ѯSF',Tv1,'<27><><EFBFBD><EFBFBD>ָʾ<D6B8><CABE><EFBFBD><EFBFBD><EFBFBD><EFBFBD>');
|
|||
|
|
EndDate.Date:=SGetServerDate(ADOQueryTemp);
|
|||
|
|
BegDate.Date:=EndDate.Date-7;
|
|||
|
|
InitGrid();
|
|||
|
|
end;
|
|||
|
|
|
|||
|
|
procedure TfrmContractList_XF_LSCX.TBFindClick(Sender: TObject);
|
|||
|
|
begin
|
|||
|
|
if ADOQueryMain.Active=False then Exit;
|
|||
|
|
SDofilter(ADOQueryMain,SGetFilters(Panel1,1,2));
|
|||
|
|
SCreateCDS20(ADOQueryMain,Order_Main);
|
|||
|
|
SInitCDSData20(ADOQueryMain,Order_Main);
|
|||
|
|
end;
|
|||
|
|
|
|||
|
|
|
|||
|
|
procedure TfrmContractList_XF_LSCX.TBExportClick(Sender: TObject);
|
|||
|
|
begin
|
|||
|
|
if ADOQueryMain.IsEmpty then Exit;
|
|||
|
|
SelExportData(Tv1,ADOQueryMain,'<27><>ʱС<CAB1><D0A1><EFBFBD><EFBFBD><EFBFBD>б<EFBFBD>');
|
|||
|
|
end;
|
|||
|
|
|
|||
|
|
procedure TfrmContractList_XF_LSCX.TBRafreshClick(Sender: TObject);
|
|||
|
|
begin
|
|||
|
|
InitGrid();
|
|||
|
|
end;
|
|||
|
|
|
|||
|
|
procedure TfrmContractList_XF_LSCX.FormShow(Sender: TObject);
|
|||
|
|
begin
|
|||
|
|
InitForm();
|
|||
|
|
end;
|
|||
|
|
|
|||
|
|
procedure TfrmContractList_XF_LSCX.CheckBox1Click(Sender: TObject);
|
|||
|
|
begin
|
|||
|
|
InitGrid();
|
|||
|
|
end;
|
|||
|
|
|
|||
|
|
procedure TfrmContractList_XF_LSCX.CheckBox2Click(Sender: TObject);
|
|||
|
|
begin
|
|||
|
|
TBRafresh.Click;
|
|||
|
|
end;
|
|||
|
|
|
|||
|
|
procedure TfrmContractList_XF_LSCX.CustomerNoChange(Sender: TObject);
|
|||
|
|
begin
|
|||
|
|
if ADOQueryMain.Active=False then Exit;
|
|||
|
|
SDofilter(ADOQueryMain,SGetFilters(Panel1,1,2));
|
|||
|
|
SCreateCDS20(ADOQueryMain,Order_Main);
|
|||
|
|
SInitCDSData20(ADOQueryMain,Order_Main);
|
|||
|
|
end;
|
|||
|
|
|
|||
|
|
procedure TfrmContractList_XF_LSCX.Tv1FocusedRecordChanged(
|
|||
|
|
Sender: TcxCustomGridTableView; APrevFocusedRecord,
|
|||
|
|
AFocusedRecord: TcxCustomGridRecord;
|
|||
|
|
ANewItemRecordFocusingChanged: Boolean);
|
|||
|
|
begin
|
|||
|
|
if Order_Main.IsEmpty then Exit;
|
|||
|
|
Note.Text:=Trim(Order_Main.fieldbyname('Note').AsString);
|
|||
|
|
FKType.Text:=Trim(Order_Main.fieldbyname('FKType').AsString);
|
|||
|
|
end;
|
|||
|
|
|
|||
|
|
procedure TfrmContractList_XF_LSCX.v1Column3CustomDrawCell(
|
|||
|
|
Sender: TcxCustomGridTableView; ACanvas: TcxCanvas;
|
|||
|
|
AViewInfo: TcxGridTableDataCellViewInfo; var ADone: Boolean);
|
|||
|
|
var
|
|||
|
|
id,id1,id2,id3:Integer;
|
|||
|
|
begin
|
|||
|
|
Id:=TV1.GetColumnByFieldName('SYMoney').Index;
|
|||
|
|
Id1:=TV1.GetColumnByFieldName('YZFLQty').Index;
|
|||
|
|
Id2:=TV1.GetColumnByFieldName('FHQty').Index;
|
|||
|
|
Id3:=TV1.GetColumnByFieldName('SKMoney').Index;
|
|||
|
|
if AViewInfo.GridRecord.Values[Id]<0 then
|
|||
|
|
begin
|
|||
|
|
ACanvas.Font.Color:=clRed;
|
|||
|
|
end;
|
|||
|
|
if (AViewInfo.GridRecord.Values[Id3]=Null) or (AViewInfo.GridRecord.Values[Id3]=0) then
|
|||
|
|
begin
|
|||
|
|
if (AViewInfo.GridRecord.Values[Id1]>0) or (AViewInfo.GridRecord.Values[Id2]>0) then
|
|||
|
|
begin
|
|||
|
|
ACanvas.Font.Color:=clRed;
|
|||
|
|
end;
|
|||
|
|
end;
|
|||
|
|
|
|||
|
|
|
|||
|
|
end;
|
|||
|
|
|
|||
|
|
procedure TfrmContractList_XF_LSCX.GCNameKeyPress(Sender: TObject;
|
|||
|
|
var Key: Char);
|
|||
|
|
var i:Integer;
|
|||
|
|
begin
|
|||
|
|
if Key=#13 then
|
|||
|
|
begin
|
|||
|
|
try
|
|||
|
|
ADOQueryMain.DisableControls;
|
|||
|
|
with ADOQueryMain do
|
|||
|
|
begin
|
|||
|
|
Filtered:=False;
|
|||
|
|
Close;
|
|||
|
|
sql.Clear;
|
|||
|
|
sql.Add('select AA.*,KZFLQty=Cast((case when isnull(YSMoney,0)=0 then 0 else SKMoney*HZConQty/YSMoney end) as decimal(18,2))');
|
|||
|
|
sql.Add(',SYMoney=isnull(SKMoney,0)-isnull(FHMoney,0)');
|
|||
|
|
sql.Add(',SYuMaxQty=Cast(Case when isnull(XSMinPrice,0)=0 then 0 else (isnull(SKMoney,0)-isnull(FHMoney,0))/XSMinPrice end as decimal(18,2))');
|
|||
|
|
sql.Add(',SYuMinQty=Cast(Case when isnull(XSMaxPrice,0)=0 then 0 else (isnull(SKMoney,0)-isnull(FHMoney,0))/XSMaxPrice end as decimal(18,2))');
|
|||
|
|
sql.add(' from (select A.* ');
|
|||
|
|
sql.Add(',XSMaxPrice=(select Max(ShiJiPrice) from JYOrderCon_Sub B where B.MainId=A.MainId)');
|
|||
|
|
sql.Add(',XSMinPrice=(select Min(ShiJiPrice) from JYOrderCon_Sub B where B.MainId=A.MainId)');
|
|||
|
|
sql.add(',SKMoney=(select Sum(Money) from YF_Money_CR B where B.FactoryName=A.CustomerNoName and B.GCName=A.GCName and B.CRType=''<27>տ<EFBFBD><D5BF>Ǽ<EFBFBD>'' and isnull(B.Status,''0'')>0)');
|
|||
|
|
sql.Add(',YZFLQty=(select Sum(B.OrdFLQty) from JYOrder_Main B where B.ConNo=A.ConNO )');
|
|||
|
|
sql.Add(',YSMoney=(select Sum(isnull(ShijiPrice,0)*isnull(FLQty,0)+isnull(BengSongFee,0)) from JYOrderCon_Sub B where B.MainId=A.MainId )');
|
|||
|
|
sql.Add(',HZConQty=(select Sum(isnull(FLQty,0)) from JYOrderCon_Sub B where B.MainId=A.MainId )');
|
|||
|
|
sql.add(',FHQty=(select Sum(Qty) from YF_Money_CR B where B.FactoryName=A.CustomerNoName and B.GCName=A.GCName and B.CRType=''Ӧ<>տ<EFBFBD><D5BF>Ǽ<EFBFBD>'')');
|
|||
|
|
sql.add(',FHMoney=(select Sum(Money) from YF_Money_CR B where B.FactoryName=A.CustomerNoName and B.GCName=A.GCName and B.CRType=''Ӧ<>տ<EFBFBD><D5BF>Ǽ<EFBFBD>'')');
|
|||
|
|
sql.Add(' from JYOrderCon_Main A ');
|
|||
|
|
SQL.Add(' where A.ConType in(''<27><>ʱ<EFBFBD><CAB1>ͬ'',''<27><>ʱ<EFBFBD><CAB1>ͬBKP'') and isnull(A.status,''0'')>''0'' and isnull(A.status,''0'')<''3'' ');
|
|||
|
|
sql.Add(' and A.GCName like '''+'%'+trim(GCName.Text)+'%'+'''');
|
|||
|
|
sql.Add(' ) AA');
|
|||
|
|
sql.Add(' order by AA.QDDate');
|
|||
|
|
Open;
|
|||
|
|
end;
|
|||
|
|
SCreateCDS20(ADOQueryMain,Order_Main);
|
|||
|
|
SInitCDSData20(ADOQueryMain,Order_Main);
|
|||
|
|
finally;
|
|||
|
|
ADOQueryMain.EnableControls;
|
|||
|
|
end;
|
|||
|
|
end;
|
|||
|
|
end;
|
|||
|
|
|
|||
|
|
end.
|