unit U_CKProductJYHZList;

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, MovePanel, cxButtonEdit, cxCalendar, RM_System, RM_Common,
  RM_Class, RM_GridReport, cxLookAndFeels, cxLookAndFeelPainters, cxNavigator,
  dxBarBuiltInMenu, cxPC;

type
  TfrmCKProductJYHZList = class(TForm)
    ToolBar1: TToolBar;
    TBRafresh: TToolButton;
    TBFind: TToolButton;
    TBExport: TToolButton;
    TBPrint: TToolButton;
    TBClose: TToolButton;
    Panel1: TPanel;
    ADOQueryCmd: TADOQuery;
    ADOQueryMain: TADOQuery;
    ADOQueryTemp: TADOQuery;
    DataSource1: TDataSource;
    cxGridPopupMenu1: TcxGridPopupMenu;
    Label3: TLabel;
    MPRTCodeName: TEdit;
    Label1: TLabel;
    Label2: TLabel;
    BegDate: TDateTimePicker;
    EndDate: TDateTimePicker;
    CDS_Main: TClientDataSet;
    Tv1: TcxGridDBTableView;
    cxGrid2Level1: TcxGridLevel;
    cxGrid2: TcxGrid;
    v2Column1: TcxGridDBColumn;
    v2Column5: TcxGridDBColumn;
    v1Column1: TcxGridDBColumn;
    Label5: TLabel;
    orderNo: TEdit;
    Label6: TLabel;
    PRTColor: TEdit;
    v1Column7: TcxGridDBColumn;
    v1Column6: TcxGridDBColumn;
    Label8: TLabel;
    MPRTKZ: TEdit;
    Label9: TLabel;
    MPRTMF: TEdit;
    Label7: TLabel;
    CPType: TComboBox;
    MovePanel2: TMovePanel;
    Label10: TLabel;
    Label11: TLabel;
    Label12: TLabel;
    YWY: TEdit;
    OrdPerson1: TEdit;
    v1Column11: TcxGridDBColumn;
    v1Column12: TcxGridDBColumn;
    v1Column3: TcxGridDBColumn;
    v1Column4: TcxGridDBColumn;
    v1Column5: TcxGridDBColumn;
    v1Column8: TcxGridDBColumn;
    MJstr4: TEdit;
    Label4: TLabel;
    Panel4: TPanel;
    Label13: TLabel;
    Panel10: TPanel;
    Image2: TImage;
    RichEdit1: TRichEdit;
    v1Column2: TcxGridDBColumn;
    v1Column9: TcxGridDBColumn;
    v1Column10: TcxGridDBColumn;
    conNo: TEdit;
    Label14: TLabel;
    KHorderNo: TEdit;
    Label15: TLabel;
    v1Column13: TcxGridDBColumn;
    RMGridReport1: TRMGridReport;
    ToolButton1: TToolButton;
    Tv1Column1: TcxGridDBColumn;
    Tv1Column2: TcxGridDBColumn;
    Tv1Column3: TcxGridDBColumn;
    Tv1Column4: TcxGridDBColumn;
    cxTabControl1: TcxTabControl;
    Tv1Column5: TcxGridDBColumn;
    Label16: TLabel;
    CustomerNoName: TEdit;
    Tv1Column6: TcxGridDBColumn;
    Tv1Column7: TcxGridDBColumn;
    procedure FormDestroy(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormCreate(Sender: TObject);
    procedure TBRafreshClick(Sender: TObject);
    procedure ConNoMChange(Sender: TObject);
    procedure TBCloseClick(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure TBExportClick(Sender: TObject);
    procedure TBFindClick(Sender: TObject);
    procedure MPRTCodeNameChange(Sender: TObject);
    procedure v1Column5PropertiesButtonClick(Sender: TObject; AButtonIndex: Integer);
    procedure PRTColorChange(Sender: TObject);
    procedure Image2Click(Sender: TObject);
    procedure Tv1CellDblClick(Sender: TcxCustomGridTableView; ACellViewInfo: TcxGridTableDataCellViewInfo; AButton: TMouseButton; AShift: TShiftState; var AHandled: Boolean);
    procedure Tv1MouseUp(Sender: TObject; Button: TMouseButton; Shift: TShiftState; X, Y: Integer);
    procedure TBPrintClick(Sender: TObject);
    procedure ToolButton1Click(Sender: TObject);
    procedure cxTabControl1Change(Sender: TObject);
  private
    FLeft, FTop: Integer;
    procedure InitGrid();
    { Private declarations }
  public
    { Public declarations }
  end;

var
  frmCKProductJYHZList: TfrmCKProductJYHZList;

implementation

uses
  U_DataLink, U_Fun, U_ZDYHelp, U_JYOrderCDOne;

{$R *.dfm}

procedure TfrmCKProductJYHZList.FormDestroy(Sender: TObject);
begin
  frmCKProductJYHZList := nil;
end;

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

procedure TfrmCKProductJYHZList.FormCreate(Sender: TObject);
begin
  //cxGrid1.Align:=alClient;
  BegDate.DateTime := SGetServerDateTime(ADOQueryTemp);
  EndDate.DateTime := SGetServerDateTime(ADOQueryTemp)
end;

procedure TfrmCKProductJYHZList.InitGrid();
var
  i: integer;
  j: Integer;
  strwhere: string;
begin
  if cxTabControl1.TabIndex < 2 then
  begin
    strwhere := strwhere + ' and  isnull(A.status,''0'')=''' + inttostr(cxTabControl1.TabIndex) + '''';
  end;

  try
    ADOQueryMain.DisableControls;
    with ADOQueryMain do
    begin
      Close;
      sql.Clear;
      Filtered := False;   //A.Mjstr4, D.PrtColor,D.PrtHX,D.SOrddefstr4,   X.SubId=A.SubId
      if cxTabControl1.TabIndex = 0 then
      begin
        sql.Add('select convert(char(10),A.fillTime,120) as CRTime,A.MJType as CPType,A.MainId,A.MJTypeother as QtyUnit,C.OrderNo,C.ConNO,C.CustomerNoName, ');
        sql.Add('count(A.MainId) as JQty,SUM(A.MJLen) as Qty,SUM(A.MJMaoZ) as KGQty,SUM(A.MJQty4) as MJQty4,');
        sql.Add('JQty=(select count(*) from WFB_MJJY X where X.MainId=A.MainId    ),');
        sql.Add('SCKROLL=(select sum(mjlen) from WFB_MJJY X where X.MainId=A.MainId and X.ckflag=''已出库''),');
        sql.Add('SkcROLL=(select sum(mjlen) from WFB_MJJY X where X.MainId=A.MainId and X.ckflag=''未出库''),');
        sql.Add('CKROLL=(select count(*) from WFB_MJJY X where X.MainId=A.MainId and X.ckflag=''已出库''),');
        sql.Add('KCROLL=(select count(*) from WFB_MJJY X where X.MainId=A.MainId and X.ckflag=''未出库''),');
        sql.Add('KHorderNo=(select top 1 KHorderNo from JYOrder_sub X where X.mainid=A.mainid),');
        sql.Add('khconNO=(select top 1 khconNo from JYOrderCon_Main X where X.conNO=C.conNO)');
        sql.Add('from WFB_MJJY A ');
        sql.Add('inner join JYOrder_Main C on C.MainId=A.MainId ');
        sql.Add('inner join JYOrder_sub D on D.subID=A.subID ');

        Sql.add('where A.fillTime>=''' + formatdateTime('yyyy-MM-dd', begdate.Date) + ''' ');
        Sql.add('and A.fillTime<''' + formatdateTime('yyyy-MM-dd', enddate.Date + 1) + ''' ');
        sql.add('and CKFlag=''未出库'' ');
        Sql.add('group by convert(char(10),A.fillTime,120),A.MJType,A.MainId,A.MJTypeother,C.OrderNo,C.ConNO,C.CustomerNoName ');
      end;
      if cxTabControl1.TabIndex = 1 then
      begin
        sql.Add('select A.MJType as CPType,A.MainId,A.MJTypeother as QtyUnit, A.cktime,C.OrderNo,C.ConNO,C.CustomerNoName,   ');
        sql.Add('count(A.MainId) as JQty,SUM(A.MJLen) as Qty,SUM(A.MJMaoZ) as KGQty,SUM(A.MJQty4) as MJQty4,');
        sql.Add('JQty=(select count(*) from WFB_MJJY X where X.MainId=A.MainId and X.cktime=A.cktime ),');
        sql.Add('SCKROLL=(select sum(mjlen) from WFB_MJJY X where X.MainId=A.MainId and X.ckflag=''已出库''  and X.cktime=A.cktime),');
        sql.Add('SkcROLL=(select sum(mjlen) from WFB_MJJY X where X.MainId=A.MainId and X.ckflag=''未出库'' and X.cktime=A.cktime),');
        sql.Add('CKROLL=(select count(*) from WFB_MJJY X where X.MainId=A.MainId and X.ckflag=''已出库'' and X.cktime=A.cktime),');
        sql.Add('KCROLL=(select count(*) from WFB_MJJY X where X.MainId=A.MainId and X.ckflag=''未出库'' and X.cktime=A.cktime),');
        sql.Add('KHorderNo=(select top 1 KHorderNo from JYOrder_sub X where X.mainid=A.mainid),');
        sql.Add('khconNO=(select top 1 khconNo from JYOrderCon_Main X where X.conNO=C.conNO)');
        sql.Add('from WFB_MJJY A ');
        sql.Add('inner join JYOrder_Main C on C.MainId=A.MainId ');
        sql.Add('inner join JYOrder_sub D on D.subID=A.subID ');
        Sql.add('where A.cktime>=''' + formatdateTime('yyyy-MM-dd', begdate.Date) + ''' ');
        Sql.add('and A.cktime<''' + formatdateTime('yyyy-MM-dd', enddate.Date + 1) + ''' ');
        sql.add('and CKFlag=''已出库'' ');
        Sql.add('group by  A.MJType,A.MainId,A.MJTypeother,A.cktime,C.OrderNo,C.ConNO,C.CustomerNoName  ');
      end;
      if cxTabControl1.TabIndex = 3 then
      begin
        sql.Add('select convert(char(10),A.fillTime,120) as CRTime,A.MJType as CPType,A.MainId,A.MJTypeother as QtyUnit,A.cktime,C.OrderNo,C.ConNO,C.CustomerNoName,');
        sql.Add('count(A.MainId) as JQty,SUM(A.MJLen) as Qty,SUM(A.MJMaoZ) as KGQty,SUM(A.MJQty4) as MJQty4,');
        sql.Add('JQty=(select count(*) from WFB_MJJY X where X.SubId=A.SubId),');
        sql.Add('SCKROLL=(select sum(mjlen) from WFB_MJJY X where X.MainId=A.MainId and X.ckflag=''已出库''),');
        sql.Add('SkcROLL=(select sum(mjlen) from WFB_MJJY X where X.MainId=A.MainId and X.ckflag=''未出库''),');
        sql.Add('CKROLL=(select count(*) from WFB_MJJY X where X.MainId=A.MainId and X.ckflag=''已出库''),');
        sql.Add('KCROLL=(select count(*) from WFB_MJJY X where X.MainId=A.MainId and X.ckflag=''未出库''),');
        sql.Add('KHorderNo=(select top 1 KHorderNo from JYOrder_sub X where X.mainid=A.mainid),');
        sql.Add('khconNO=(select top 1 khconNo from JYOrderCon_Main X where X.conNO=C.conNO)');
        sql.Add('from WFB_MJJY A ');
        sql.Add('inner join JYOrder_Main C on C.MainId=A.MainId ');
        sql.Add('inner join JYOrder_sub D on D.subID=A.subID ');

        Sql.add('where A.fillTime>=''' + formatdateTime('yyyy-MM-dd', begdate.Date) + ''' ');
        Sql.add('and A.fillTime<''' + formatdateTime('yyyy-MM-dd', enddate.Date + 1) + ''' ');
        sql.add('and CKFlag=''未出库'' ');
        Sql.add('group by convert(char(10),A.fillTime,120),A.MJType,A.MainId,A.MJTypeother,A.cktime,C.OrderNo,C.ConNO,C.CustomerNoName ');
      end;
//      Sql.add('group by convert(char(10),A.fillTime,120),A.SubId,A.MJType,A.MainId,A.MJTypeother,A.Mjstr4,A.cktime,C.OrderNo,C.ConNO,C.CustomerNoName,D.PRTCodeName,D.PrtColor,D.PrtHX,D.SOrddefstr4');
      Open;
//      ShowMessage(SQL.Text);                    
    end;
    SCreateCDS20(ADOQueryMain, CDS_Main);
    SInitCDSData20(ADOQueryMain, CDS_Main);
  finally
    ADOQueryMain.EnableControls;
  end;
end;

procedure TfrmCKProductJYHZList.TBRafreshClick(Sender: TObject);
begin
  //BegDate.SetFocus;
  MovePanel2.Visible := True;
  MovePanel2.Refresh;
  InitGrid();
  MovePanel2.Visible := False;
end;

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

procedure TfrmCKProductJYHZList.TBCloseClick(Sender: TObject);
begin
  WriteCxGrid(self.Caption + tv1.Name, Tv1, '成品仓库');
  Close;
end;

procedure TfrmCKProductJYHZList.FormShow(Sender: TObject);
begin

  ReadCxGrid(self.Caption + tv1.Name, Tv1, '成品仓库');
  if Trim(DParameters2) = '管理' then
  begin
    //v1Column5.Options.Focusing:=True;
  end
  else
  begin
    //v1Column5.Options.Focusing:=False;
  end;
  //InitGrid();
end;

procedure TfrmCKProductJYHZList.TBExportClick(Sender: TObject);
begin
  if ADOQueryMain.IsEmpty then
    exit;
  TcxGridToExcel('库存汇总列表', cxGrid2);
end;

procedure TfrmCKProductJYHZList.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 TfrmCKProductJYHZList.MPRTCodeNameChange(Sender: TObject);
begin
  TBFind.Click;
end;

procedure TfrmCKProductJYHZList.v1Column5PropertiesButtonClick(Sender: TObject; AButtonIndex: Integer);
begin
  try
    frmZDYHelp := TfrmZDYHelp.Create(Application);
    with frmZDYHelp do
    begin
      flag := 'SOrdDefStr10';
      flagname := '库存存放地点';
      if ShowModal = 1 then
      begin
        with CDS_Main do
        begin
          Edit;
          FieldByName('SOrdDefStr10').Value := Trim(ClientDataSet1.fieldbyname('ZdyName').AsString);
        end;
        with ADOQueryCmd do
        begin
          Close;
          sql.Clear;
          sql.Add('Update JYOrder_Sub Set SOrdDefStr10=''' + Trim(ClientDataSet1.fieldbyname('ZdyName').AsString) + '''');
          sql.Add(' where SubId=''' + Trim(Self.CDS_Main.fieldbyname('SubId').AsString) + '''');
          ExecSQL;
        end;
      end;
    end;
  finally
    frmZDYHelp.Free;
  end;
end;

procedure TfrmCKProductJYHZList.PRTColorChange(Sender: TObject);
begin
  TBFind.Click;
end;

procedure TfrmCKProductJYHZList.Image2Click(Sender: TObject);
begin
  Panel4.Visible := False;
end;

procedure TfrmCKProductJYHZList.Tv1CellDblClick(Sender: TcxCustomGridTableView; ACellViewInfo: TcxGridTableDataCellViewInfo; AButton: TMouseButton; AShift: TShiftState; var AHandled: Boolean);
begin
  Panel4.Left := FLeft;
  Panel4.Top := FTop + 110;
  Panel4.Visible := True;
  Panel10.Caption := Trim(TV1.Controller.FocusedColumn.Caption);
  RichEdit1.Text := CDS_Main.fieldbyname(TV1.Controller.FocusedColumn.DataBinding.FilterFieldName).AsString;
  application.ProcessMessages;
end;

procedure TfrmCKProductJYHZList.Tv1MouseUp(Sender: TObject; Button: TMouseButton; Shift: TShiftState; X, Y: Integer);
begin
  FLeft := X;
  FTop := Y;
end;

procedure TfrmCKProductJYHZList.TBPrintClick(Sender: TObject);
begin
  RMGridReport1.PreviewButtons := [pbZoom, pbLoad, pbSave, pbPrint, pbFind, pbPageSetup, pbExit, pbExport, pbNavigator];
end;

procedure TfrmCKProductJYHZList.ToolButton1Click(Sender: TObject);
begin
  if cds_main.IsEmpty then
    Exit;
  frmJYOrderCDOne := TfrmJYOrderCDOne.Create(Application);
  with frmJYOrderCDOne do
  begin
    orderno.Text := trim(self.CDS_Main.fieldbyname('orderno').asstring);
    gangno.Text := trim(self.CDS_Main.fieldbyname('MJStr4').asstring);
    PRTColor.Text := trim(self.CDS_Main.fieldbyname('PRTColor').asstring);
    if ShowModal = 1 then
    begin
    //     InitGrid();
    end;
    free;
  end;
end;

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

end.