unit U_JGPBCKKCList_MRT;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, cxStyles, cxCustomData, cxGraphics, cxFilter, cxData,
  cxDataStorage, cxEdit, DB, cxDBData, cxGridCustomTableView,
  cxGridTableView, cxGridBandedTableView, cxGridDBBandedTableView,
  cxGridLevel, cxClasses, cxControls, cxGridCustomView, cxGridDBTableView,
  cxGrid, StdCtrls, ComCtrls, ExtCtrls, ToolWin, cxGridCustomPopupMenu,
  cxGridPopupMenu, ADODB, DBClient, cxDropDownEdit, cxCheckBox, RM_Common,
  RM_Class, RM_e_Xls, RM_Dataset, RM_System, RM_GridReport, Menus,
  cxCalendar, cxButtonEdit, cxTextEdit, cxLookAndFeels,
  cxLookAndFeelPainters, cxNavigator;

type
  TfrmJGPBCKKCList_MRT = class(TForm)
    ToolBar1: TToolBar;
    TBRafresh: TToolButton;
    TBFind: TToolButton;
    TBClose: TToolButton;
    Panel1: TPanel;
    ADOQueryCmd: TADOQuery;
    ADOQueryMain: TADOQuery;
    ADOQueryTemp: TADOQuery;
    DataSource1: TDataSource;
    cxGridPopupMenu1: TcxGridPopupMenu;
    CDS_Main: TClientDataSet;
    RM1: TRMGridReport;
    RMDBMain: TRMDBDataSet;
    RMXLSExport1: TRMXLSExport;
    RMDBHZ: TRMDBDataSet;
    CDS_HZ: TClientDataSet;
    CDS_PRT: TClientDataSet;
    PopupMenu1: TPopupMenu;
    N1: TMenuItem;
    N2: TMenuItem;
    cxGrid2: TcxGrid;
    Tv1: TcxGridDBTableView;
    v2YLName: TcxGridDBColumn;
    v1ZKFQty: TcxGridDBColumn;
    cxGrid2Level1: TcxGridLevel;
    Label3: TLabel;
    BNo: TEdit;
    ToolButton2: TToolButton;
    Label2: TLabel;
    v1PJMFQty: TcxGridDBColumn;
    cxStyleRepository1: TcxStyleRepository;
    cxStyle1: TcxStyle;
    v1ZPZJQty: TcxGridDBColumn;
    v1FLQty: TcxGridDBColumn;
    v1ZSLQty: TcxGridDBColumn;
    cxStyle2: TcxStyle;
    Label1: TLabel;
    BegDate: TDateTimePicker;
    v1ZMHQty: TcxGridDBColumn;
    ADOQuery1: TADOQuery;
    v1ZWJJQty: TcxGridDBColumn;
    v1ZSXQty: TcxGridDBColumn;
    v1ZXSQty: TcxGridDBColumn;
    v1ZSZQty: TcxGridDBColumn;
    v1ZGZPQty: TcxGridDBColumn;
    v1Column6: TcxGridDBColumn;
    v1PJPZJQty: TcxGridDBColumn;
    v1PJKFQty: TcxGridDBColumn;
    v1PJMHQty: TcxGridDBColumn;
    v1PJWJJQty: TcxGridDBColumn;
    v1PJSXQty: TcxGridDBColumn;
    v1PJXSQty: TcxGridDBColumn;
    v1PJSZQty: TcxGridDBColumn;
    v1PJGZPQty: TcxGridDBColumn;
    cxStyle3: TcxStyle;
    v1CRTime: TcxGridDBColumn;
    EndDate: TDateTimePicker;
    RadioGroup1: TRadioGroup;
    v1QSFLQty: TcxGridDBColumn;
    v1ZXCRTime: TcxGridDBColumn;
    procedure FormDestroy(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure TBRafreshClick(Sender: TObject);
    procedure ConNoMChange(Sender: TObject);
    procedure TBCloseClick(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure TBFindClick(Sender: TObject);
    procedure MPRTCodeNameChange(Sender: TObject);
    procedure N1Click(Sender: TObject);
    procedure N2Click(Sender: TObject);
    procedure SXBatchNOChange(Sender: TObject);
    procedure FactoryNameChange(Sender: TObject);
    procedure Tv1CellDblClick(Sender: TcxCustomGridTableView;
      ACellViewInfo: TcxGridTableDataCellViewInfo; AButton: TMouseButton;
      AShift: TShiftState; var AHandled: Boolean);
    procedure ToolButton2Click(Sender: TObject);
    procedure RadioGroup1Click(Sender: TObject);
  private
    procedure InitGrid();
    { Private declarations }
  public
    { Public declarations }
  end;

var
  frmJGPBCKKCList_MRT: TfrmJGPBCKKCList_MRT;

implementation
uses
  U_DataLink,U_RTFun;

{$R *.dfm}

procedure TfrmJGPBCKKCList_MRT.FormDestroy(Sender: TObject);
begin
  frmJGPBCKKCList_MRT:=nil;
end;

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

procedure TfrmJGPBCKKCList_MRT.InitGrid();
var i:Integer;
begin
  ToolBar1.SetFocus;
  try
    ADOQueryMain.DisableControls;
    with ADOQueryMain do
    begin
      Filtered:=False;
      Close;
      sql.Clear;
      sql.Add('select AA.*,PJSNQty=(case when FLQty=0 then 0 else ZSLQty/FLQty*1000 end),PJPZJQty=(case when FLQty=0 then 0 else ZPZJQty/FLQty*1000 end)');
      sql.Add(',PJKFQty=(case when FLQty=0 then 0 else ZKFQty/FLQty*1000 end),PJMHQty=(case when FLQty=0 then 0 else ZMHQty/FLQty*1000 end),PJWJJQty=(case when FLQty=0 then 0 else ZWJJQty/FLQty*1000 end)');
      SQL.Add(',PJSXQty=(case when FLQty=0 then 0 else ZSXQty/FLQty*1000 end),PJXSQty=(case when FLQty=0 then 0 else ZXSQty/FLQty*1000 end),PJSZQty=(case when FLQty=0 then 0 else ZSZQty/FLQty*1000 end)');
      sql.Add(',PJGZPQty=(case when FLQty=0 then 0 else ZGZPQty/FLQty*1000 end)');
      sql.Add(',PJMFQty=(case when FLQty=0 then 0 else (isnull(ZSLQty,0)+isnull(ZPZJQty,0)+isnull(ZKFQty,0)+isnull(ZMHQty,0)+isnull(ZWJJQty,0)+isnull(ZSXQty,0)+isnull(ZXSQty,0)+isnull(ZSZQty,0)+isnull(ZGZPQty,0))/FLQty*1000 end)');
      sql.Add('from (select ');
      if RadioGroup1.ItemIndex=2 then
      sql.Add(' convert(varchar(10),B.CRTime,120) CRTime');
      if RadioGroup1.ItemIndex=1 then
      sql.Add(' convert(varchar(7),B.CRTime,120) CRTime');
      if RadioGroup1.ItemIndex=0 then
      sql.Add(' convert(varchar(4),B.CRTime,120) CRTime');
      sql.Add(',BNo,Max(CRTime) ZXCRTime,SUM(isnull(FLQty,0)) FLQty,ZSLQty=isnull(Sum(Qty1),0)+isnull(Sum(Qty2),0)+isnull(Sum(Qty4),0)+isnull(Sum(Qty5),0)+isnull(Sum(Qty6),0) ');
      sql.Add(',ZPZJQty=isnull(Sum(Qty3),0),ZKFQty=isnull(Sum(Qty7),0)+isnull(Sum(Qty8),0),ZMHQty=isnull(Sum(Qty9),0)+isnull(Sum(Qty10),0) ');
      sql.Add(',ZWJJQty=isnull(Sum(ZXJQty),0)+isnull(Sum(NTQty),0)+isnull(Sum(WLQty),0)+isnull(Sum(WJJQty1),0)++isnull(Sum(WJJQty2),0)++isnull(Sum(WJJQty3),0) ');
      sql.Add(',ZSXQty=isnull(Sum(SXQty),0),ZXSQty=isnull(Sum(HSQty2),0),ZSZQty=isnull(Sum(SZQty),0),ZGZPQty=isnull(Sum(XSQty),0) ');
      sql.add(',QSFLQty=(select isnull(Sum(C.CPFLQty),0) from CK_CP_CR C where isnull(C.BNo,'''')=isnull(B.BNo,'''') and C.CRType=''销售出库''');
      if RadioGroup1.ItemIndex=2 then
      begin
        sql.Add(' and convert(varchar(10),C.CRTime,120)=convert(varchar(10),B.CRTime,120))');
      end;
      if RadioGroup1.ItemIndex=1 then
      begin
        sql.Add(' and convert(varchar(7),C.CRTime,120)=convert(varchar(7),B.CRTime,120))');
      end;
      if RadioGroup1.ItemIndex=0 then
      begin
        sql.Add(' and convert(varchar(4),C.CRTime,120)=convert(varchar(4),B.CRTime,120))');
      end;
      sql.Add(' from CK_SXPB_CK B ');
      sql.Add(' where B.CRFlag=''出库'' and isnull(B.CKName,'''')=''原料'' ');
      if RadioGroup1.ItemIndex=2 then
      begin
        sql.Add(' and convert(varchar(10),B.CRTime,120)>='''+trim(FormatDateTime('yyyy-MM-dd',BegDate.Date))+''' and convert(varchar(10),B.CRTime,120)<'''+trim(FormatDateTime('yyyy-MM-dd',EndDate.Date+1))+'''');
        sql.Add(' group by convert(varchar(10),B.CRTime,120),BNo ');
      end;
      if RadioGroup1.ItemIndex=1 then
      begin
        sql.Add(' and convert(varchar(7),B.CRTime,120)>='''+trim(FormatDateTime('yyyy-MM',BegDate.Date))+''' and convert(varchar(7),B.CRTime,120)<='''+trim(FormatDateTime('yyyy-MM',EndDate.Date))+'''');
        sql.Add(' group by convert(varchar(7),B.CRTime,120),BNo ');
      end;
      if RadioGroup1.ItemIndex=0 then
      begin
        sql.Add(' and convert(varchar(4),B.CRTime,120)>='''+trim(FormatDateTime('yyyy',BegDate.Date))+''' and convert(varchar(4),B.CRTime,120)<='''+trim(FormatDateTime('yyyy',EndDate.Date))+'''');
        sql.Add(' group by convert(varchar(4),B.CRTime,120),BNo ');
      end;
      sql.Add(') AA');
      sql.Add(' where isnull(AA.FLQty,0)<>0');
      sql.Add(' order by CRTime,BNo');
      Open;
    end;
    SCreateCDS20(ADOQueryMain,CDS_Main);
    SInitCDSData20(ADOQueryMain,CDS_Main);
    if CDS_Main.IsEmpty=False then
    begin
      for i:=1 to 34-CDS_Main.RecordCount do
      begin
        with CDS_Main do
        begin
          Append;
          post;
        end;
      end;
    end;
  finally;
    ADOQueryMain.EnableControls;
  end;
end;

procedure TfrmJGPBCKKCList_MRT.TBRafreshClick(Sender: TObject);
begin
  ToolBar1.SetFocus;
  InitGrid();
end;

procedure TfrmJGPBCKKCList_MRT.ConNoMChange(Sender: TObject);
begin
  if ADOQueryMain.Active then
  begin
    SDofilter(ADOQueryMain,SGetFilters(Panel1,1,2));
  end;
end;

procedure TfrmJGPBCKKCList_MRT.TBCloseClick(Sender: TObject);
begin
  WriteCxGrid('原料库存列表4321',Tv1,'原料仓库');
  Close;
end;

procedure TfrmJGPBCKKCList_MRT.FormShow(Sender: TObject);
begin
  ReadCxGrid('原料库存列表4321',Tv1,'原料仓库');
  BegDate.Date:=SGetServerDate(ADOQueryTemp);
  endDate.Date:=SGetServerDate(ADOQueryTemp);
  //InitGrid();
end;

procedure TfrmJGPBCKKCList_MRT.TBFindClick(Sender: TObject);
begin
  if ADOQueryMain.Active then
  begin
    SDofilter(ADOQueryMain,SGetFilters(Panel1,1,2));
    SCreateCDS20(ADOQueryMain,CDS_Main);
    SInitCDSData20(ADOQueryMain,CDS_Main);
  end;
end;

procedure TfrmJGPBCKKCList_MRT.MPRTCodeNameChange(Sender: TObject);
begin
  TBFind.Click;
end;

procedure TfrmJGPBCKKCList_MRT.N1Click(Sender: TObject);
begin
  SelOKNo(CDS_Main,True);
end;

procedure TfrmJGPBCKKCList_MRT.N2Click(Sender: TObject);
begin
  SelOKNo(CDS_Main,False);
end;

procedure TfrmJGPBCKKCList_MRT.SXBatchNOChange(Sender: TObject);
begin
  TBFind.Click;
end;

procedure TfrmJGPBCKKCList_MRT.FactoryNameChange(Sender: TObject);
begin
  TBFind.Click;
end;

procedure TfrmJGPBCKKCList_MRT.Tv1CellDblClick(Sender: TcxCustomGridTableView;
  ACellViewInfo: TcxGridTableDataCellViewInfo; AButton: TMouseButton;
  AShift: TShiftState; var AHandled: Boolean);
begin
  ModalResult:=1;
end;

procedure TfrmJGPBCKKCList_MRT.ToolButton2Click(Sender: TObject);
begin
  ToolBar1.SetFocus;
  if CDS_Main.IsEmpty then exit;
  TcxGridToExcel('砼报表年月日',cxGrid2);
end;

procedure TfrmJGPBCKKCList_MRT.RadioGroup1Click(Sender: TObject);
begin
  if RadioGroup1.ItemIndex=2 then
  begin
    v1CRTime.Caption:='日期';
    BegDate.Format:='yyyy-MM-dd';
    EndDate.Format:='yyyy-MM-dd';
  end;
  if RadioGroup1.ItemIndex=1 then
  begin
    v1CRTime.Caption:='月份';
    BegDate.Format:='yyyy-MM';
    EndDate.Format:='yyyy-MM';
  end;
  if RadioGroup1.ItemIndex=0 then
  begin
    v1CRTime.Caption:='年份';
    BegDate.Format:='yyyy';
    EndDate.Format:='yyyy';
  end;
  InitGrid();
end;

end.