unit U_PlanProfitDetails;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, cxGraphics, cxCustomData, cxStyles, cxTL, cxMaskEdit, DB, ADODB,
  cxInplaceContainer, cxDBTL, cxControls, cxTLData, ComCtrls, ToolWin, StdCtrls,
  cxFilter, cxData, cxDataStorage, cxEdit, cxDBData, DBClient,
  cxGridCustomTableView, cxGridTableView, cxGridDBTableView, ExtCtrls,
  cxSplitter, cxGridLevel, cxClasses, cxGridCustomView, cxGrid,
  cxGridCustomPopupMenu, cxGridPopupMenu, RM_Dataset, RM_System, RM_Common,
  RM_Class, RM_GridReport, IdBaseComponent, IdComponent, IdTCPConnection,
  IdTCPClient, IdFTP, ShellAPI, IniFiles, cxCheckBox, cxCalendar, cxButtonEdit,
  cxTextEdit, cxLookAndFeels, cxLookAndFeelPainters, cxNavigator,
  dxBarBuiltInMenu, cxPC, dxSkinsCore, dxSkinsDefaultPainters, dxDateRanges,
  U_BaseHelp, Vcl.Menus, Vcl.Clipbrd, DateUtils, dxSkinWXI,
  dxScrollbarAnnotations, U_BaseList, System.ImageList, Vcl.ImgList;

type
  TfrmPlanProfitDetails = class(TfrmBasehelp)
    ToolBar1: TToolBar;
    TBRafresh: TToolButton;
    TBClose: TToolButton;
    ADOQueryTemp: TADOQuery;
    ADOQueryCmd: TADOQuery;
    Panel1: TPanel;
    ToolButton2: TToolButton;
    ADOQueryMain: TADOQuery;
    RM1: TRMGridReport;
    RMDB_Main: TRMDBDataSet;
    Label3: TLabel;
    OrderNo: TEdit;
    Label2: TLabel;
    Label6: TLabel;
    begdate: TDateTimePicker;
    Enddate: TDateTimePicker;
    cxGridPopupMenu2: TcxGridPopupMenu;
    DS_HZ: TDataSource;
    CDS_HZ: TClientDataSet;
    Label1: TLabel;
    ComTaiTou: TEdit;
    ADOQueryPrint: TADOQuery;
    ToolButton1: TToolButton;
    PM_1: TPopupMenu;
    N1: TMenuItem;
    cxGrid1: TcxGrid;
    Tv1: TcxGridDBTableView;
    v2CRTime: TcxGridDBColumn;
    Tv1Column27: TcxGridDBColumn;
    Tv1Column23: TcxGridDBColumn;
    v2Column2: TcxGridDBColumn;
    v2FactoryName: TcxGridDBColumn;
    Tv1Column2: TcxGridDBColumn;
    v2OrderNo: TcxGridDBColumn;
    Tv1Column3: TcxGridDBColumn;
    Tv1Column24: TcxGridDBColumn;
    Tv1Column20: TcxGridDBColumn;
    Tv1Column21: TcxGridDBColumn;
    Tv1Column4: TcxGridDBColumn;
    Tv1Column5: TcxGridDBColumn;
    Tv1Column6: TcxGridDBColumn;
    Tv1Column7: TcxGridDBColumn;
    Tv1Column8: TcxGridDBColumn;
    Tv1Column9: TcxGridDBColumn;
    Tv1Column10: TcxGridDBColumn;
    Tv1Column30: TcxGridDBColumn;
    Tv1Column11: TcxGridDBColumn;
    Tv1Column12: TcxGridDBColumn;
    Tv1Column13: TcxGridDBColumn;
    Tv1Column14: TcxGridDBColumn;
    Tv1Column15: TcxGridDBColumn;
    Tv1Column16: TcxGridDBColumn;
    Tv1Column26: TcxGridDBColumn;
    Tv1Column17: TcxGridDBColumn;
    Tv1Column18: TcxGridDBColumn;
    Tv1Column25: TcxGridDBColumn;
    Tv1Column1: TcxGridDBColumn;
    Tv1Column28: TcxGridDBColumn;
    Tv1Column29: TcxGridDBColumn;
    cxGridLevel1: TcxGridLevel;
    Panel2: TPanel;
    cxGrid2: TcxGrid;
    TV2: TcxGridDBTableView;
    cxGridDBColumn10: TcxGridDBColumn;
    cxGridDBColumn12: TcxGridDBColumn;
    cxGridDBColumn13: TcxGridDBColumn;
    cxGridDBColumn14: TcxGridDBColumn;
    cxGridDBColumn15: TcxGridDBColumn;
    cxGridLevel2: TcxGridLevel;
    cxGrid3: TcxGrid;
    TV3: TcxGridDBTableView;
    cxGridDBColumn42: TcxGridDBColumn;
    cxGridDBColumn46: TcxGridDBColumn;
    cxGridDBColumn47: TcxGridDBColumn;
    cxGridDBColumn50: TcxGridDBColumn;
    cxGridDBColumn52: TcxGridDBColumn;
    cxGridDBColumn53: TcxGridDBColumn;
    cxGridDBColumn55: TcxGridDBColumn;
    cxGridDBColumn56: TcxGridDBColumn;
    cxGridDBColumn57: TcxGridDBColumn;
    cxGridLevel3: TcxGridLevel;
    ADOQuery3: TADOQuery;
    ADOQuery2: TADOQuery;
    CDS_2: TClientDataSet;
    DS_2: TDataSource;
    DS_3: TDataSource;
    CDS_3: TClientDataSet;
    TV2Column1: TcxGridDBColumn;
    TV2Column2: TcxGridDBColumn;
    TV2Column3: TcxGridDBColumn;
    TV2Column4: TcxGridDBColumn;
    Tv1Column19: TcxGridDBColumn;
    TV2Column5: TcxGridDBColumn;
    TV2Column6: TcxGridDBColumn;
    TV3Column1: TcxGridDBColumn;
    TV3Column2: TcxGridDBColumn;
    cxSplitter1: TcxSplitter;
    TV2Column7: TcxGridDBColumn;
    TV3Column3: TcxGridDBColumn;
    cxGrid4: TcxGrid;
    TV4: TcxGridDBTableView;
    cxGridDBColumn1: TcxGridDBColumn;
    cxGridDBColumn2: TcxGridDBColumn;
    cxGridDBColumn3: TcxGridDBColumn;
    cxGridDBColumn4: TcxGridDBColumn;
    cxGridDBColumn5: TcxGridDBColumn;
    cxGridDBColumn6: TcxGridDBColumn;
    cxGridDBColumn7: TcxGridDBColumn;
    cxGridDBColumn8: TcxGridDBColumn;
    cxGridDBColumn9: TcxGridDBColumn;
    cxGridDBColumn11: TcxGridDBColumn;
    cxGridDBColumn16: TcxGridDBColumn;
    cxGridDBColumn17: TcxGridDBColumn;
    cxGridDBColumn18: TcxGridDBColumn;
    cxGridDBColumn19: TcxGridDBColumn;
    cxGridDBColumn20: TcxGridDBColumn;
    cxGridDBColumn21: TcxGridDBColumn;
    cxGridDBColumn22: TcxGridDBColumn;
    cxGridDBColumn23: TcxGridDBColumn;
    cxGridDBColumn24: TcxGridDBColumn;
    cxGridDBColumn25: TcxGridDBColumn;
    cxGridDBColumn26: TcxGridDBColumn;
    cxGridDBColumn27: TcxGridDBColumn;
    cxGridDBColumn28: TcxGridDBColumn;
    cxGridDBColumn29: TcxGridDBColumn;
    cxGridDBColumn30: TcxGridDBColumn;
    cxGridDBColumn31: TcxGridDBColumn;
    cxGridDBColumn32: TcxGridDBColumn;
    cxGridDBColumn33: TcxGridDBColumn;
    cxGridDBColumn34: TcxGridDBColumn;
    cxGridDBColumn35: TcxGridDBColumn;
    cxGridDBColumn36: TcxGridDBColumn;
    cxGridDBColumn37: TcxGridDBColumn;
    cxGridDBColumn38: TcxGridDBColumn;
    cxGridLevel4: TcxGridLevel;
    cxSplitter2: TcxSplitter;
    DataSource1: TDataSource;
    TV4Column1: TcxGridDBColumn;
    Tv1Column22: TcxGridDBColumn;
    ClientDataSet1: TClientDataSet;
    DataSource2: TDataSource;
    ADOQuery5: TADOQuery;
    TV4Column2: TcxGridDBColumn;
    TV4Column3: TcxGridDBColumn;
    TV4Column4: TcxGridDBColumn;
    Tv1Column31: TcxGridDBColumn;
    Tv1Column32: TcxGridDBColumn;
    CDS_31: TClientDataSet;
    CDS_21: TClientDataSet;
    TV2Column8: TcxGridDBColumn;
    TV3Column4: TcxGridDBColumn;
    TV2Column9: TcxGridDBColumn;
    TV3Column5: TcxGridDBColumn;
    TV2Column10: TcxGridDBColumn;
    TV3Column6: TcxGridDBColumn;
    ToolButton3: TToolButton;
    CDS_32: TClientDataSet;
    CDS_22: TClientDataSet;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure TBCloseClick(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure TBRafreshClick(Sender: TObject);
    procedure ToolButton2Click(Sender: TObject);
    procedure OrderNoChange(Sender: TObject);
    procedure ToolButton4Click(Sender: TObject);
    procedure cxTabControl1Change(Sender: TObject);
    procedure ToolButton1Click(Sender: TObject);
    procedure N1Click(Sender: TObject);
    procedure FormResize(Sender: TObject);
    procedure ToolButton3Click(Sender: TObject);
  private
    { Private declarations }
    procedure InitGrid();
    procedure InitYFFGrid();
    procedure InitZD();
  public
    fmanage, FZKType, FOrderNo, FMainID: string;
    { Public declarations }
    RKFlag, FCYID, FUnit: string;
  end;

var
  frmPlanProfitDetails: TfrmPlanProfitDetails;

implementation

uses
  U_DataLink, U_RtFun, U_ZDYHelp, U_LabelPrint;


{$R *.dfm}

procedure TfrmPlanProfitDetails.InitGrid();
begin
  try
    ADOQueryMain.DisableControls;
    with ADOQueryMain do
    begin
      Filtered := False;
      Close;
      SQL.Clear;
      sql.Add('Select Amount=(Case when ISNULL(A.RegType,'''')=''收款'' then -1*Amount else Amount end)');
//      sql.Add(',SSAmount=(Case when ISNULL(A.RegType,'''')=''收款'' then -1*Amount else Amount end)-ISNULL(A.SXFAmount,0)');
//      sql.Add(',SSCNYAmount=((Case when ISNULL(A.RegType,'''')=''收款'' then -1*Amount else Amount end) ');
//      sql.Add('-ISNULL(A.SXFAmount,0))*ISNULL((Case when Currency=''CNY'' then 1 else A.JHExchangeRate end),1)');

      sql.Add(',WBSJRMB = ISNULL((select sum(cast(CWBSY as decimal(18,2))) from Costing_Table where COrderNo = A.OrderNo), 0),');
      sql.Add('RMBSHJE = ISNULL((select sum(cast(CRMBSSJE as decimal(18,2))) from Costing_Table where COrderNo = A.OrderNo), 0),');
      sql.Add('TSSJ = ISNULL((select sum(cast(CTSSY as decimal(18,2))) from Costing_Table where COrderNo = A.OrderNo), 0)');
      sql.Add(',CPKPJE = ISNULL((select sum(cast(CCPKPJE as decimal(18,2))) from Costing_Table where COrderNo = A.OrderNo), 0)');
      SQL.Add(',BuyConNO=ISNULL(A.BuyConNo,(select TOP 1 BuyConNo from Trade_Plan_Sub X where X.MainID=A.OrdMainId ))');
      SQL.Add(',A.*,Saleser=(select Top 1 Saleser from BS_Company X where X.CoAbbrName=A.OppCoName)');
      sql.Add(' from Finance_Flow A where A.OrderNo= ' + QuotedStr(Trim(FOrderNo)));
      sql.Add(' and FFType =  ''应收款登记'' and isnull(status, ''0'') = ''9'' ');
//      sql.Add(' and ISNULL(A.FFTime,A.SQDate)<''' + FormatDateTime('yyyy-MM-dd', Enddate.DateTime + 1) + ''' ');
      //ShowMessage(sql.text);
      Open;
    end;
    SCreateCDS(ADOQueryMain, CDS_HZ);
    SInitCDSData(ADOQueryMain, CDS_HZ);
    if not CDS_HZ.IsEmpty then
    begin
      with CDS_HZ do
      begin
        for var i := 0 to 3 do
        begin
          append;
          if i = 0 then
          begin
            CDS_HZ.FieldByName('FFAbstract').Value := '外币收益RMB';
            CDS_HZ.FieldByName('Amount').Value := ADOQueryMain.FieldByName('WBSJRMB').Value;
          end
          else if i = 1 then
          begin
            CDS_HZ.FieldByName('FFAbstract').Value := '人民币实收金额';
            CDS_HZ.FieldByName('Amount').Value := ADOQueryMain.FieldByName('RMBSHJE').Value;
          end
          else if i = 2 then
          begin
            CDS_HZ.FieldByName('FFAbstract').Value := '退税收益';
            CDS_HZ.FieldByName('Amount').Value := ADOQueryMain.FieldByName('TSSJ').Value;
          end
          else if i = 3 then
          begin
            CDS_HZ.FieldByName('FFAbstract').Value := '成品开票金额';
            CDS_HZ.FieldByName('Amount').Value := ADOQueryMain.FieldByName('CPKPJE').Value;
          end;
          post;
        end;
      end;
    end;

  finally
    ADOQueryMain.EnableControls;
    TV4.DataController.Filter.Clear;
  end;
//  ToolButton2.Click;
end;

procedure TfrmPlanProfitDetails.InitYFFGrid();
begin
  try
    ADOQuery5.DisableControls;
    with ADOQuery5 do
    begin
      Filtered := False;
      Close;
      SQL.Clear;
      sql.Add('Select Amount=(Case when ISNULL(A.RegType,'''')=''付款'' then -1*Amount else Amount end)');
//      sql.Add(',SSAmount=(Case when ISNULL(A.RegType,'''')=''收款'' then -1*Amount else Amount end)-ISNULL(A.SXFAmount,0)');
//      sql.Add(',SSCNYAmount=((Case when ISNULL(A.RegType,'''')=''收款'' then -1*Amount else Amount end) ');
//      sql.Add('-ISNULL(A.SXFAmount,0))*ISNULL((Case when Currency=''CNY'' then 1 else A.JHExchangeRate end),1)');
      sql.Add(',GDSs = ISNULL((select sum(cast(GDS as decimal(18, 2))) from Costing_Table where COrderNo = A.OrderNo), 0),');
      sql.Add('QTSF = ISNULL((select sum(cast(COrderSF as decimal(18, 2))) from Costing_Table where COrderNo = A.OrderNo), 0),');

      SQL.Add('BuyConNO=ISNULL(A.BuyConNo,(select TOP 1 BuyConNo from Trade_Plan_Sub X where X.MainID=A.OrdMainId ))');
      SQL.Add(',A.*,Saleser=(select Top 1 Saleser from BS_Company X where X.CoAbbrName=A.OppCoName)');
      sql.Add(' from Finance_Flow A where A.OrderNo= ' + QuotedStr(Trim(FOrderNo)));
      sql.Add(' and (FFType =  ''应付款登记'' or FFType = ''其他应付款登记'' or FFType = ''手工费用登记'' ) and isnull(status, ''0'') = ''9''');
//      sql.Add(' and ISNULL(A.FFTime,A.SQDate)>=''' + FormatDateTime('yyyy-MM-dd', begdate.DateTime) + ''' ');
//      sql.Add(' and ISNULL(A.FFTime,A.SQDate)<''' + FormatDateTime('yyyy-MM-dd', Enddate.DateTime + 1) + ''' ');
      //ShowMessage(sql.text);
      Open;
    end;
    SCreateCDS(ADOQuery5, ClientDataSet1);
    SInitCDSData(ADOQuery5, ClientDataSet1);
    if not ClientDataSet1.IsEmpty then
    begin
      with ClientDataSet1 do
      begin
        for var i := 0 to 1 do
        begin
          append;
          if i = 0 then
          begin
            FieldByName('FFAbstract').Value := '国地税';
            FieldByName('Amount').Value := ADOQuery5.FieldByName('GDSs').Value;
          end
          else if i = 1 then
          begin
            FieldByName('FFAbstract').Value := '其他税费';
            FieldByName('Amount').Value := ADOQuery5.FieldByName('QTSF').Value;
          end;
          post;
        end;
      end;
    end;

  finally
    ADOQuery5.EnableControls;
    TV1.DataController.Filter.Clear;
  end;
//  ToolButton2.Click;
end;

procedure TfrmPlanProfitDetails.InitZD();
begin

  with ADOQuery2 do
  begin
    Filtered := False;
    Close;
    SQL.Clear;
    sql.Add('select A.OrderNo,C.OrderNo TOOrdNO,A.C_Pattern,A.C_Color,A.C_ColorNo, ISNULL(A.ZDPrice,0) ZDPrice, U.QtyUnit OrdUnit, A.ZDTime ');
//    if FUnit = 'M' then
//    begin
//      sql.Add(',ZDAmount=(ISNULL(A.ZDPrice,0)*SUM(StkMeter))')
//    end
//    else if FUnit = 'Y' then
//    begin
//      sql.Add(',ZDAmount=(ISNULL(A.ZDPrice,0)*SUM(StkYardage))')
//    end
//    else if FUnit = 'Kg' then
//    begin
//      sql.Add(',ZDAmount=(ISNULL(A.ZDPrice,0)*SUM(StkNetWeight))')
//    end;
    SQL.Add(' ,ZDAmount = ISNULL(SUM(ISNULL(A.ZDPrice,0) *');
    SQL.Add(' CASE U.QtyUnit');
    sql.Add(' WHEN ''M'' THEN A.StkMeter');
    SQL.Add(' WHEN ''Y'' THEN A.StkYardage');
    SQL.Add(' ELSE A.StkNetWeight');
    sql.Add(' END), 0) ');
    SQL.Add(',COUNT(CIID) Piece,SUM(StkGrossWeight) GrossWeight');
    sql.Add(',SUM(StkNetWeight) NetWeight,SUM(StkMeter) Meter,SUM(StkYardage) Yardage');
    SQL.Add('from Trade_Cloth_Stock_ZD  A left join Trade_Plan_Sub B on A.ZDSubId=B.SubID');
    sql.Add('left join Trade_Plan_Main C on C.MainId=B.MainId');
    sql.add(' OUTER APPLY (SELECT TOP 1 QtyUnit ');
    sql.Add(' FROM Trade_Cloth_DR_Sub X');
    sql.add(' WHERE X.DRSID = A.DRSID) AS U');
    sql.Add(' where A.MainID= ' + QuotedStr(Trim(FMainID)));
    sql.Add(' and C.OrderNo <> '''' ');
    Sql.Add('GROUP BY A.OrderNo,C.OrderNo,A.C_Pattern,A.C_Color,A.C_ColorNo,A.ZDPrice,U.QtyUnit, A.ZDTime');
//      ShowMessage(sql.text);
    Open;
  end;
  SCreateCDS(ADOQuery2, CDS_2);
  SInitCDSData(ADOQuery2, CDS_2);
  with CDS_2 do
  begin
    first;
    while not eof do
    begin
      Edit;
      FieldByName('SDefNote').AsString := '成品转单';
      post;
      Next;
    end;
  end;

  with ADOQuery2 do
  begin
    Filtered := False;
    Close;
    SQL.Clear;
    SQL.Add(' select FromOrdNo, stkName, C_Pattern, C_Color, C_ColorNo, sum(qty) as qty, price, sum(piece) as piece, qtyUnit, ZDAmount = (isnull(price, 0) * sum(qty)), qty = sum(qty), iotime, ');
    SQL.Add(' (select OrderNo from Trade_Plan_Main where MainID in(select MainID from Pur_ClothPlan_Main X where X.PurNo = A.ToOrdNo)) ToOrdNo ');
    SQL.Add(' from Bs_Cloth_IO A where STKName in (''贸易坯布'', ''贸易待检布'')');
    SQL.Add(' and FromOrdNo <> (select OrderNo from Trade_Plan_Main where MainID in(select MainID from Pur_ClothPlan_Main X where X.PurNo = A.ToOrdNo)) ');
    sql.Add(' and FromOrdNo = ' + quotedstr(FOrderNo));
    SQL.Add(' group by FromOrdNo, ToOrdNo, C_Pattern, C_Color, C_ColorNo, Price, stkName, qtyUnit, iotime ');
//    ShowMessage(sql.text);
    Open;
  end;
  SCreateCDS(ADOQuery2, CDS_21);
  SInitCDSData(ADOQuery2, CDS_21);
  if not CDS_21.IsEmpty then
  begin
    CDS_21.First;
    with CDS_2 do
    begin
      for var i := 0 to CDS_21.RecordCount - 1 do
      begin
        append;
        FieldByName('OrderNo').AsString := CDS_21.FieldByName('FromOrdNo').AsString;
        FieldByName('ToOrdNo').AsString := CDS_21.FieldByName('ToOrdNo').AsString;
        FieldByName('C_Pattern').AsString := CDS_21.FieldByName('C_Pattern').AsString;
        FieldByName('C_Color').AsString := CDS_21.FieldByName('C_Color').AsString;
        FieldByName('ZDAmount').AsString := CDS_21.FieldByName('ZDAmount').AsString;
        FieldByName('C_ColorNo').AsString := CDS_21.FieldByName('C_ColorNo').AsString;
        FieldByName('ZDPrice').AsString := CDS_21.FieldByName('Price').AsString;
        FieldByName('OrdUnit').AsString := CDS_21.FieldByName('qtyUnit').AsString;
        FieldByName('Piece').AsString := CDS_21.FieldByName('Piece').AsString;
        FieldByName('ZDTime').AsString := CDS_21.FieldByName('iotime').AsString;
        if CDS_21.FieldByName('stkName').AsString = '贸易坯布' then
          FieldByName('SDefNote').AsString := '坯布转单'
        else if CDS_21.FieldByName('stkName').AsString = '贸易待检布' then
          FieldByName('SDefNote').AsString := '待检布转单';
        if LowerCase(CDS_21.FieldByName('QtyUnit').AsString) = 'kg' then
          FieldByName('NetWeight').AsString := CDS_21.FieldByName('qty').AsString
        else if LowerCase(CDS_21.FieldByName('QtyUnit').AsString) = 'm' then
          FieldByName('Meter').AsString := CDS_21.FieldByName('qty').AsString;
        CDS_21.Next;
      end;
      post;
    end;
  end;

  with ADOQuery2 do
  begin
    Filtered := False;
    Close;
    SQL.Clear;
    SQL.Add('SELECT ');
    SQL.Add('  B.OrderNo AS FromOrdNo, B.C_Pattern, A.IOType, B.C_Color, B.C_ColorNo, ISNULL(A.price, sub.price) price, sub.QtyUnit, A.IOTime,COUNT(*) Piece, A.IOType,');
    SQL.Add(' Sum(A.Meter) Meter, Sum(A.NetWeight) NetWeight, Sum(A.GrossWeight) GrossWeight, Sum(A.Yardage) Yardage,');
    SQL.Add('  (SELECT top 1 OrderNo FROM Trade_Cloth_DR_Sub X WHERE X.DRMID = A.IONO)  AS TOOrdNo,');
    SQL.Add('  iif(sum(A.Amount) is null,CAST(ISNULL(Sum(');
    SQL.Add('  ISNULL(A.price, sub.price) * ');
    SQL.Add('  ISNULL( CASE sub.QtyUnit');
    SQL.Add(' WHEN ''M'' THEN  A.Meter');
    SQL.Add(' 	WHEN ''Kg'' THEN A.NetWeight  ');
    SQL.Add(' 				WHEN ''Y'' THEN A.Yardage');
    SQL.Add(' 	END, 0)),0) AS DECIMAL(18,2)), sum(A.Amount)) AS zdamount');
    SQL.Add('FROM Trade_Cloth_IO A ');
    SQL.Add('JOIN Trade_Cloth_Stock B ON A.StkID = B.StkID ');
    SQL.Add('CROSS APPLY (');
    SQL.Add('  SELECT TOP 1 QtyUnit, price ');
    SQL.Add('  FROM Trade_Cloth_DR_Sub X ');
    SQL.Add('  WHERE X.DRMID = A.IONO');
    SQL.Add(') sub');
    SQL.Add('WHERE B.OrderNo <> (SELECT top 1 OrderNo FROM Trade_Cloth_DR_Sub X WHERE X.DRMID = A.IONO) ');
    SQL.Add('  AND A.IOType IN (''加工出库'', ''回修出库'')');
    SQL.Add(' AND B.OrderNo = ' + QuotedStr(FOrderNo));
    SQL.Add('GROUP BY B.OrderNo, A.IONO, B.C_Pattern, A.IOType, B.C_Color, B.C_ColorNo, sub.price, sub.QtyUnit, A.IOTime, A.price');
    Open;
  end;
  SCreateCDS(ADOQuery2, CDS_22);
  SInitCDSData(ADOQuery2, CDS_22);
  if not CDS_22.IsEmpty then
  begin
    CDS_22.First;
    with CDS_2 do
    begin
      for var i := 0 to CDS_22.RecordCount - 1 do
      begin
        append;
        FieldByName('OrderNo').AsString := CDS_22.FieldByName('FromOrdNo').AsString;
        FieldByName('ToOrdNo').AsString := CDS_22.FieldByName('ToOrdNo').AsString;
        FieldByName('C_Pattern').AsString := CDS_22.FieldByName('C_Pattern').AsString;
        FieldByName('C_Color').AsString := CDS_22.FieldByName('C_Color').AsString;
        FieldByName('ZDAmount').AsString := CDS_22.FieldByName('ZDAmount').AsString;
        FieldByName('C_ColorNo').AsString := CDS_22.FieldByName('C_ColorNo').AsString;
        FieldByName('ZDPrice').AsString := CDS_22.FieldByName('Price').AsString;
        FieldByName('OrdUnit').AsString := CDS_22.FieldByName('qtyUnit').AsString;
        FieldByName('Piece').AsString := CDS_22.FieldByName('Piece').AsString;
        FieldByName('ZDTime').AsString := CDS_22.FieldByName('iotime').AsString;
        FieldByName('Meter').AsString := CDS_22.FieldByName('Meter').AsString;
        FieldByName('GrossWeight').AsString := CDS_22.FieldByName('GrossWeight').AsString;
        FieldByName('Yardage').AsString := CDS_22.FieldByName('Yardage').AsString;
        FieldByName('NetWeight').AsString := CDS_22.FieldByName('NetWeight').AsString;
        if CDS_22.FieldByName('IOType').AsString = '加工出库' then
          FieldByName('SDefNote').AsString := ' 加工转单'
        else if CDS_22.FieldByName('IOType').AsString = '回修出库' then
          FieldByName('SDefNote').AsString := ' 回修转单';
        CDS_22.Next;
      end;
      post;
    end;
  end;

  with ADOQuery3 do
  begin
    Filtered := False;
    Close;
    SQL.Clear;
    sql.Add('select A.OrderNo FromOrdNo,C.OrderNo,B.C_Pattern,B.C_Color,B.C_ColorNo,COUNT(CIID) Piece,ISNULL(A.ZDPrice,0) ZDPrice, D.qtyunit OrdUnit, A.ZDTime ');
    sql.Add(', ZDAmount = case  D.qtyunit when ''M'' then (ISNULL(A.ZDPrice,0)*SUM(StkMeter))');
    sql.Add(' when ''Y'' then (ISNULL(A.ZDPrice,0)*SUM(StkYardage))');
    sql.Add(' when ''Kg'' then (ISNULL(A.ZDPrice,0)*SUM(StkNetWeight)) end');
    SQL.Add(',SUM(StkGrossWeight) GrossWeight,SUM(StkNetWeight) NetWeight,SUM(StkMeter) Meter,SUM(StkYardage) Yardage');
    SQL.Add('from Trade_Cloth_Stock_ZD  A left join Trade_Plan_Sub B on A.ZDSubId=B.SubID');
    sql.Add('left join Trade_Plan_Main C on C.MainId=B.MainId');
    sql.Add('left join (select DISTINCT QtyUnit,DRSID from Trade_Cloth_DR_Sub ) D on D.DRSID = A.DRSID');
    sql.Add(' where A.ZDMainID= ' + QuotedStr(Trim(FMainID)));
    Sql.Add('GROUP BY A.OrderNO,C.OrderNo,B.C_Pattern,B.C_Color,B.C_ColorNo,A.ZDPrice, D.qtyunit, A.ZDTime');
//      ShowMessage(sql.text);
    Open;
  end;
  SCreateCDS(ADOQuery3, CDS_3);
  SInitCDSData(ADOQuery3, CDS_3);
  with CDS_3 do
  begin
    first;
    while not eof do
    begin
      Edit;
      FieldByName('SDefNote').AsString := '成品转单';
      post;
      Next;
    end;
  end;
  with ADOQuery3 do
  begin
    Filtered := False;
    Close;
    SQL.Clear;
    SQL.Add('select stkName, FromOrdNo, C_Pattern, C_Color, C_ColorNo, Price, qtyUnit, ZDAmount = (isnull(price, 0) * sum(qty)), qty = sum(qty), sum(piece) as piece, iotime, ');
    SQL.Add(' (select OrderNo from Trade_Plan_Main where MainID in(select MainID from Pur_ClothPlan_Main X where X.PurNo = A.ToOrdNo)) ToOrdNo ');
    SQL.Add(' from Bs_Cloth_IO A where STKName in (''贸易坯布'', ''贸易待检布'')');
    SQL.Add(' and FromOrdNo <> (select OrderNo from Trade_Plan_Main where MainID in(select MainID from Pur_ClothPlan_Main X where X.PurNo = A.ToOrdNo)) ');
    sql.Add(' and (select OrderNo from Trade_Plan_Main where MainID in(select MainID from Pur_ClothPlan_Main X where X.PurNo = A.ToOrdNo)) = ' + quotedstr(FOrderNo));
    SQL.Add(' group by FromOrdNo, ToOrdNo, C_Pattern, C_Color, C_ColorNo, Price, stkName, qtyUnit, iotime ');
//      ShowMessage(sql.text);
    Open;
  end;
  SCreateCDS(ADOQuery3, CDS_31);
  SInitCDSData(ADOQuery3, CDS_31);
  if not CDS_31.IsEmpty then
  begin
    with CDS_3 do
    begin
      CDS_31.First;
      for var i := 0 to CDS_31.RecordCount - 1 do
      begin
        append;
        FieldByName('OrderNo').AsString := OrderNo.Text;
        FieldByName('FromOrdNo').AsString := CDS_31.FieldByName('FromOrdNo').AsString;
        FieldByName('C_Pattern').AsString := CDS_31.FieldByName('C_Pattern').AsString;
        FieldByName('C_Color').AsString := CDS_31.FieldByName('C_Color').AsString;
        FieldByName('ZDAmount').AsString := CDS_31.FieldByName('ZDAmount').AsString;
        FieldByName('C_ColorNo').AsString := CDS_31.FieldByName('C_ColorNo').AsString;
        FieldByName('ZDPrice').AsString := CDS_31.FieldByName('Price').AsString;
        FieldByName('OrdUnit').AsString := CDS_31.FieldByName('qtyUnit').AsString;
        FieldByName('ZDTime').AsString := CDS_31.FieldByName('iotime').AsString;
        FieldByName('Piece').AsString := CDS_31.FieldByName('Piece').AsString;
        if CDS_31.FieldByName('stkName').AsString = '贸易坯布' then
          FieldByName('SDefNote').AsString := '坯布转单'
        else if CDS_31.FieldByName('stkName').AsString = '贸易待检布' then
          FieldByName('SDefNote').AsString := '待检布转单';
        if LowerCase(CDS_31.FieldByName('QtyUnit').AsString) = 'kg' then
          FieldByName('NetWeight').AsString := CDS_31.FieldByName('qty').AsString
        else if LowerCase(CDS_31.FieldByName('QtyUnit').AsString) = 'm' then
          FieldByName('Meter').AsString := CDS_31.FieldByName('qty').AsString;

        CDS_31.Next;
      end;
      post;
    end;
  end;

  with ADOQuery3 do
  begin
    Filtered := False;
    Close;
    SQL.Clear;
    SQL.Add('SELECT ');
    SQL.Add('  B.OrderNo AS FromOrdNo, B.C_Pattern, A.IOType, B.C_Color, B.C_ColorNo, ISNULL(A.price, sub.price) price, sub.QtyUnit, A.IOTime,COUNT(*) Piece, A.IOType,');
    SQL.Add(' Sum(A.Meter) Meter, Sum(A.NetWeight) NetWeight, Sum(A.GrossWeight) GrossWeight, Sum(A.Yardage) Yardage,');
    SQL.Add('  (SELECT top 1 OrderNo FROM Trade_Cloth_DR_Sub X WHERE X.DRMID = A.IONO)  AS ToOrdNo,');
    SQL.Add('  iif(sum(A.Amount) is null, CAST(ISNULL(Sum(');
    SQL.Add('  ISNULL(A.price, sub.price) * ');
    SQL.Add('  ISNULL( CASE sub.QtyUnit');
    SQL.Add(' WHEN ''M'' THEN  A.Meter');
    SQL.Add(' 	WHEN ''Kg'' THEN  A.NetWeight  ');
    SQL.Add(' 				WHEN ''Y'' THEN A.Yardage');
    SQL.Add(' 	END, 0)),0) AS DECIMAL(18,2)), sum(A.Amount)) AS zdamount');
    SQL.Add('FROM Trade_Cloth_IO A ');
    SQL.Add('JOIN Trade_Cloth_Stock B ON A.StkID = B.StkID ');
    SQL.Add('CROSS APPLY (');
    SQL.Add('  SELECT TOP 1 QtyUnit, price ');
    SQL.Add('  FROM Trade_Cloth_DR_Sub X ');
    SQL.Add('  WHERE X.DRMID = A.IONO');
    SQL.Add(') sub');
    SQL.Add('WHERE B.OrderNo <> (SELECT top 1 OrderNo FROM Trade_Cloth_DR_Sub X WHERE X.DRMID = A.IONO) ');
    SQL.Add('  AND A.IOType IN (''加工出库'', ''回修出库'')');
    SQL.Add(' AND (SELECT top 1 OrderNo FROM Trade_Cloth_DR_Sub X WHERE X.DRMID = A.IONO) = ' + QuotedStr(FOrderNo));
    SQL.Add('GROUP BY B.OrderNo, A.IONO, B.C_Pattern, A.IOType, B.C_Color, B.C_ColorNo, sub.price, sub.QtyUnit, A.IOTime, A.price');
    Open;
  end;
  SCreateCDS(ADOQuery3, CDS_32);
  SInitCDSData(ADOQuery3, CDS_32);
  if not CDS_32.IsEmpty then
  begin
    with CDS_3 do
    begin
      CDS_32.First;
      for var i := 0 to CDS_32.RecordCount - 1 do
      begin
        append;
        FieldByName('OrderNo').AsString := OrderNo.Text;
        FieldByName('FromOrdNo').AsString := CDS_32.FieldByName('FromOrdNo').AsString;
        FieldByName('C_Pattern').AsString := CDS_32.FieldByName('C_Pattern').AsString;
        FieldByName('C_Color').AsString := CDS_32.FieldByName('C_Color').AsString;
        FieldByName('ZDAmount').AsString := CDS_32.FieldByName('ZDAmount').AsString;
        FieldByName('C_ColorNo').AsString := CDS_32.FieldByName('C_ColorNo').AsString;
        FieldByName('ZDPrice').AsString := CDS_32.FieldByName('Price').AsString;
        FieldByName('OrdUnit').AsString := CDS_32.FieldByName('qtyUnit').AsString;
        FieldByName('ZDTime').AsString := CDS_32.FieldByName('iotime').AsString;
        FieldByName('Piece').AsString := CDS_32.FieldByName('Piece').AsString;
        FieldByName('Meter').AsString := CDS_32.FieldByName('Meter').AsString;
        FieldByName('GrossWeight').AsString := CDS_32.FieldByName('GrossWeight').AsString;
        FieldByName('Yardage').AsString := CDS_32.FieldByName('Yardage').AsString;
        FieldByName('NetWeight').AsString := CDS_32.FieldByName('NetWeight').AsString;
        if CDS_32.FieldByName('IOType').AsString = '加工出库' then
          FieldByName('SDefNote').AsString := ' 加工转单'
        else if CDS_32.FieldByName('IOType').AsString = '回修出库' then
          FieldByName('SDefNote').AsString := ' 回修转单';

        CDS_32.Next;
      end;
      post;
    end;
  end;

//  ToolButton2.Click;
end;

procedure TfrmPlanProfitDetails.N1Click(Sender: TObject);
begin

  Clipboard.SetTextBuf(PChar(Trim(CDS_HZ.fieldbyname(TV1.Controller.FocusedColumn.DataBinding.FilterFieldName).AsString)));

end;

procedure TfrmPlanProfitDetails.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  inherited;
  Action := caFree;
end;

procedure TfrmPlanProfitDetails.FormResize(Sender: TObject);
begin
  inherited;
  cxGrid4.Width := ClientWidth div 2;
  cxGrid2.Width := ClientWidth div 2;
end;

procedure TfrmPlanProfitDetails.TBCloseClick(Sender: TObject);
begin

  Close;
end;

procedure TfrmPlanProfitDetails.FormShow(Sender: TObject);
begin
  inherited;
//  cxGrid4.Width := 981;

//  CxSplitter2.Left := cxGrid4.Width;
  ReadCxGrid(trim(self.Caption), Tv1, '利润明细');
//  Enddate.DateTime := SGetServerDate(ADOQueryTemp);
////  begdate.DateTime := StartOfTheMonth(Enddate.DateTime);
//  begdate.DateTime := EndDate.DateTime - 365;
  InitGrid();
  InitYFFGrid();
  InItZD();
end;

procedure TfrmPlanProfitDetails.TBRafreshClick(Sender: TObject);
begin
  InitGrid();
  InitYFFGrid();
  InitZD();
end;

procedure TfrmPlanProfitDetails.ToolButton1Click(Sender: TObject);
begin
  WriteCxGrid(trim(self.Caption), Tv1, '利润明细');
end;

procedure TfrmPlanProfitDetails.ToolButton2Click(Sender: TObject);
var
  fsj: string;
begin
  if ADOQueryMain.Active = False then
    Exit;

  SDofilter(ADOQueryMain, Trim(SGetFilters(Panel1, 1, 2)));
  SCreateCDS(ADOQueryMain, CDS_HZ);
  SInitCDSData(ADOQueryMain, CDS_HZ);
end;

procedure TfrmPlanProfitDetails.ToolButton3Click(Sender: TObject);
begin
  inherited;
  try
    frmLabelPrint := TfrmLabelPrint.Create(Application);
    with frmLabelPrint do
    begin
      FLMType := 'TradePlanProfitPrint';
      FFiltration1 := FOrderNo + ',' + FMainID + ',' + FUnit;
      if ShowModal = 1 then
      begin
//        Self.InitGrid();
      end;
    end;
  finally
    frmLabelPrint.Free;
  end;
end;

procedure TfrmPlanProfitDetails.OrderNoChange(Sender: TObject);
begin
  ToolButton2.Click;
end;

procedure TfrmPlanProfitDetails.ToolButton4Click(Sender: TObject);
var
  filepath: string;
  mBegdate, mEnddate: string;
begin
  mBegdate := FormatDateTime('yyyy-MM-dd', begdate.Date);
  mEnddate := FormatDateTime('yyyy-MM-dd', enddate.Date);
  if CDS_HZ.IsEmpty then
    exit;

  with ADOQueryPrint do
  begin
    Close;
    SQL.Clear;
    with ADOQueryPrint do
    begin
      Close;
      SQL.Clear;
      Filtered := False;
      sql.Add(' exec P_YSSK_List @begdate=' + quotedstr(Trim(FormatDateTime('yyyy-MM-dd', begdate.DateTime))) + ',@Enddate=' + quotedstr(Trim(FormatDateTime('yyyy-MM-dd', Enddate.DateTime + 1))) + ',@flag=''已审核''');
      Open;
    end;
    Open;
  end;

  try
    filepath := ExtractFilePath(Application.ExeName) + 'report\应收账款.rmf';
    if not FileExists(Pchar(filepath)) then
    begin
      application.MessageBox(pchar('文件[' + filepath + ']不存在！'), '提示信息', MB_IConError);
      exit;
    end;

    RmVariables['begDate'] := FormatDateTime('yyyy-mm-dd', begDate.Date);
    RmVariables['enddate'] := FormatDateTime('yyyy-mm-dd', enddate.Date);
    RMVariables['ZDR'] := trim(DName);
    RM1.LoadFromFile(filepath);
    RM1.ShowReport;
  finally

  end;

end;

procedure TfrmPlanProfitDetails.cxTabControl1Change(Sender: TObject);
begin
  InitGrid();
  InitYFFGrid();
end;

end.

