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;       //关联合同
    { 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=''入库'' ');
    sql.Add(' where X.CRType=''应付款登记'' and X.FYType=''坯布费'' 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=''出库'' and A.FactoryName=AA.FactoryName and A.CKName=''坯布'' ');
    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=''入库'' ');
    sql.Add(' left join JYOrder_Sub U on U.Subid=isnull(C.OrdSubidRK,X.ZSDSubid)');
    sql.Add(' where X.CRType=''应付款登记'' and X.FYType=''加工费'' 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=''出库'' and A.FactoryName=AA.FactoryName and A.CKName=''外加工'' ');
    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<>''￥'' 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<>''￥'' and A.BZType<>''RMB'' and C.GSType=A.GSType),1) as decimal(18,2))');
    sql.Add(',Sum(case when (A.BZType<>''￥'') 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=''出库'' ');
    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=''应收款登记'' ');
    sql.add(' and A.CRFlag=''应收收'' 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'); //待观察
    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<>''￥'' 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<>''￥'' and A.BZType<>''RMB'' and C.GSType=A.GSType),1)) as decimal(18,2))');
    sql.add(',USDMoney=(case when (A.BZType<>''￥'') 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=''应收款登记'' ');
    sql.Add('inner join JYOrder_Main C on C.OrderNO=B.OrderNO ');
    sql.add('where C.Mainid='''+trim(FConNo)+''' and A.CRType=''收款登记'' ');
    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<>''￥'' 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 (''应付款登记'',''付款杂费'') and isnull(A.FYType,'''')<>''加工费'' and isnull(A.FYType,'''')<>''坯布费'' ');
    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('坯布1',Tv1,'订单收付款明细');
  ReadCxBandedGrid('染费1',Tv2,'订单收付款明细');
  ReadCxBandedGrid('收款1',Tv3,'订单收付款明细');
  ReadCxBandedGrid('费用支出1',Tv5,'订单收付款明细');
  ReadCxBandedGrid('销售出库1',Tv4,'订单收付款明细');
  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('坯布1',Tv1,'订单收付款明细');
  WriteCxBandedGrid('染费1',Tv2,'订单收付款明细');
  WriteCxBandedGrid('收款1',Tv3,'订单收付款明细');
  WriteCxBandedGrid('费用支出1',Tv5,'订单收付款明细');
  WriteCxBandedGrid('销售出库1',Tv4,'订单收付款明细');
  Close;
end;

end.