unit U_CKProductBCPKCHZList;

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, cxLookAndFeels, cxLookAndFeelPainters,
  cxNavigator, dxSkinsCore, dxSkinBlack, dxSkinBlue, dxSkinBlueprint,
  dxSkinCaramel, dxSkinCoffee, dxSkinDarkRoom, dxSkinDarkSide,
  dxSkinDevExpressDarkStyle, dxSkinDevExpressStyle, dxSkinFoggy,
  dxSkinGlassOceans, dxSkinHighContrast, dxSkiniMaginary, dxSkinLilian,
  dxSkinLiquidSky, dxSkinLondonLiquidSky, dxSkinMcSkin, dxSkinMetropolis,
  dxSkinMetropolisDark, dxSkinMoneyTwins, dxSkinOffice2007Black,
  dxSkinOffice2007Blue, dxSkinOffice2007Green, dxSkinOffice2007Pink,
  dxSkinOffice2007Silver, dxSkinOffice2010Black, dxSkinOffice2010Blue,
  dxSkinOffice2010Silver, dxSkinOffice2013DarkGray,
  dxSkinOffice2013LightGray, dxSkinOffice2013White, dxSkinPumpkin,
  dxSkinSeven, dxSkinSevenClassic, dxSkinSharp, dxSkinSharpPlus,
  dxSkinSilver, dxSkinSpringTime, dxSkinStardust, dxSkinSummer2008,
  dxSkinTheAsphaltWorld, dxSkinsDefaultPainters, dxSkinValentine,
  dxSkinVS2010, dxSkinWhiteprint, dxSkinXmas2008Blue, dxSkinscxPCPainter;

type
  TfrmCKProductBCPKCHZList = class(TForm)
    ToolBar1: TToolBar;
    TBRafresh: TToolButton;
    TBFind: TToolButton;
    TBExport: TToolButton;
    TBClose: TToolButton;
    Panel1: TPanel;
    ADOQueryCmd: TADOQuery;
    ADOQueryMain: TADOQuery;
    ADOQueryTemp: TADOQuery;
    DataSource1: TDataSource;
    cxGridPopupMenu1: TcxGridPopupMenu;
    Label3: TLabel;
    Label4: TLabel;
    MPRTCode: TEdit;
    CDS_Main: TClientDataSet;
    Tv1: TcxGridDBTableView;
    cxGrid2Level1: TcxGridLevel;
    cxGrid2: TcxGrid;
    v2Column1: TcxGridDBColumn;
    v2Column3: TcxGridDBColumn;
    v2Column5: TcxGridDBColumn;
    v2Column6: TcxGridDBColumn;
    Label6: TLabel;
    MJID: TEdit;
    v1Column7: TcxGridDBColumn;
    v1Column6: TcxGridDBColumn;
    v1Column8: TcxGridDBColumn;
    v1Column9: TcxGridDBColumn;
    Label9: TLabel;
    MPRTMF: TEdit;
    Label7: TLabel;
    CPType: TComboBox;
    v1Column3: TcxGridDBColumn;
    MovePanel2: TMovePanel;
    c_kuwei: TEdit;
    Label13: TLabel;
    v1Column13: TcxGridDBColumn;
    v1Column5: TcxGridDBColumn;
    Label12: TLabel;
    c_ywy: TEdit;
    v1Column10: TcxGridDBColumn;
    v1Column11: TcxGridDBColumn;
    Label14: TLabel;
    ganghao: TEdit;
    Label15: TLabel;
    KuanHao: TEdit;
    Label2: TLabel;
    BegDate: TDateTimePicker;
    EndDate: TDateTimePicker;
    CheckBox1: TCheckBox;
    Tv1Column1: TcxGridDBColumn;
    Tv1Column2: TcxGridDBColumn;
    prtcolor: TEdit;
    ToolButton1: TToolButton;
    Panel5: TPanel;
    Label28: TLabel;
    Button5: TButton;
    Button6: TButton;
    BZ: TEdit;
    Tv1Column3: TcxGridDBColumn;
    Label1: TLabel;
    QTYUNIT: TComboBox;
    Tv1Column4: TcxGridDBColumn;
    Tv1Column5: TcxGridDBColumn;
    Label5: TLabel;
    ISSD: TComboBox;
    Label8: TLabel;
    JLNOTE: TEdit;
    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 MPRTCodeChange(Sender: TObject);
    procedure v1Column5PropertiesButtonClick(Sender: TObject; AButtonIndex: Integer);
    procedure PRTColorChange(Sender: TObject);
    procedure CheckBox1Click(Sender: TObject);
    procedure CPTypeChange(Sender: TObject);
    procedure ToolButton1Click(Sender: TObject);
    procedure Button5Click(Sender: TObject);
    procedure Button6Click(Sender: TObject);
    procedure orderNoKeyPress(Sender: TObject; var Key: Char);
  private
    procedure InitGrid();
    { Private declarations }
  public
    { Public declarations }
    CANSHU1: string;
  end;

var
  frmCKProductBCPKCHZList: TfrmCKProductBCPKCHZList;

implementation

uses
  U_DataLink, U_Fun, U_ZDYHelp;

{$R *.dfm}

procedure TfrmCKProductBCPKCHZList.FormDestroy(Sender: TObject);
begin
  frmCKProductBCPKCHZList := nil;
end;

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

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

procedure TfrmCKProductBCPKCHZList.InitGrid();
var
  fwhere, Pwhere: string;
begin
  Pwhere := SGetFilters(Panel1, 1, 2);

  if SGetFilters(Panel1, 1, 2) <> '' then
    Pwhere := ' and ' + SGetFilters(Panel1, 1, 2);
  try
    ADOQueryMain.DisableControls;
    with ADOQueryMain do
    begin
      Filtered := False;
      Close;
      sql.Clear;
//      SQL.Add('select * from (');
//      sql.Add('select SUM(A.KGQty) KGQty,SUM(A.Qty) Qty,SUM(A.NetKGQty) NetKGQty,count(A.MJID) JQty,A.QtyUnit,C_DJ AS CPType,C_GANGNO AS ganghao ');
//      sql.Add(' ,A.KuanHao,d.c_ywy,c_code AS MPRTCode,c_mf AS MPRTMF,C_KZ AS MPRTKZ ,G.DDBZ,c_kuwei,G.JLNOTE ');
//      sql.Add(' ,C_COLOR AS PRTColor,C.PrtKuanNo,convert(varchar(10),CRTIME,120)CRTIME');
//      SQL.Add(',ISSD=(CASE WHEN ((SELECT COUNT (MJID ) FROM FHSQ_Sub g inner join FHSQ_Main f on f.fsid=g.fsid where g.mjid=d.mjid and f.sqtype<>''调样'' AND F.Status<>''已取消''  )>=(select Cycle from CK_BanCP_KC k where k.mjid=d.mjid)) ');
//      SQL.Add('OR ((SELECT COUNT(MJID) FROM CK_SXPB_CR_mx K  inner join CK_SXPB_CR J ON K.FSID=J.SPID WHERE K.MJID=D.MJID AND ISNULL(K.YCJ,'''')<>''1'' AND CRType=''客户检验'')>0) then ''已锁定'' else ''未锁定'' end)');
//      sql.Add('from  CK_BanCP_CR A   inner join CK_BanCP_KC D on A.MJID=D.MJID inner join wfb_mjjy G ON A.MJID=G.MJID LEFT join JYOrder_Main B on A.MainId=B.MainId  ');
//      sql.Add('LEFT join JYOrder_Sub C on A.SubId=C.SubId   where A.CRFlag=''入库'' and D.KCValid=''Y''  ');
//      if CheckBox1.Checked = True then
//      begin
//        sql.Add(' and   A.CRTime>' + quotedstr(FormatDateTime('yyyy-MM-dd', BegDate.DateTime)));
//        sql.Add(' and A.CRTime<=' + quotedstr(FormatDateTime('yyyy-MM-dd', enddate.DateTime + 1)));
//      end;
//      if CPType.Text = '正品' then
//      begin
//        sql.Add('and c_dj<>''次品'' and c_dj<>''次布''')
//      end
//      else if CPType.text = '次品' then
//      begin
//        sql.Add('and c_dj IN (''次品'',''次布'')');
//      end;
//
//      if (QTYUNIT.Text = '米') then
//      begin
//        sql.Add('and (QtyUnit=''米'' or qtyunit=''M'')')
//      end
//      else if QTYUNIT.text = '码' then
//      begin
//        sql.Add('and(QtyUnit=''码'' or qtyunit=''Y'')');
//      end
//      else if QTYUNIT.text = '公斤' then
//      begin
//        sql.Add('and(QtyUnit=''公斤'' or qtyunit=''KG'')');
//      end;
//
////      SQL.Add(')');
//      sql.Add(' group by  convert(varchar(10),CRTIME,120),A.QtyUnit,C_DJ,C_GANGNO,DDBZ,A.KuanHao,c_ywy,c_code,c_mf,C_KZ ,C_COLOR,C.PrtKuanNo,c_kuwei,JLNOTE');
//      sql.Add(')aa where 1=1');

      SQL.Add('select SUM(KGQty) KGQty,SUM(Qty) Qty,SUM(NetKGQty) NetKGQty,count(MJID) JQty,convert(varchar(10),CRTIME,120)CRTIME,QtyUnit,CPType,ganghao,DDBZ,KuanHao,c_ywy,MPRTCode,MPRTMF,');
      SQL.Add('MPRTKZ ,PRTColor,PrtKuanNo,c_kuwei,JLNOTE ,ISSD,SUM(KGQty) KGQty,SUM(Qty) Qty,SUM(NetKGQty) NetKGQty,count(MJID) JQty from (');
      SQL.Add('select A.KGQty,A.Qty,A.NetKGQty,A.MJID,A.QtyUnit,C_DJ AS CPType,C_GANGNO AS ganghao ');
      SQL.Add(' ,A.KuanHao,d.c_ywy,c_code AS MPRTCode,c_mf AS MPRTMF,C_KZ AS MPRTKZ ,ISNULL(G.DDBZ,'''')DDBZ,c_kuwei,G.JLNOTE');
      SQL.Add('  ,C_COLOR AS PRTColor,C.PrtKuanNo,convert(varchar(10),CRTIME,120)CRTIME');
      SQL.Add(',ISSD=(CASE WHEN ((SELECT COUNT (MJID ) FROM FHSQ_Sub g inner join FHSQ_Main f on f.fsid=g.fsid where g.mjid=d.mjid and f.sqtype<>''调样'' AND F.Status<>''已取消''  )>=(select Cycle');
      SQL.Add(' from CK_BanCP_KC k where k.mjid=d.mjid)) ');
      //OR ((SELECT COUNT(MJID) FROM CK_SXPB_CR_mx K  inner join CK_SXPB_CR J ON K.FSID=J.SPID WHERE K.MJID=D.MJID AND
      //ISNULL(K.YCJ,'''')<>''1'' AND CRType=''客户检验'')>0)
      SQL.Add(' then ''已锁定'' else  ''未锁定'' end)');
      SQL.Add('from  CK_BanCP_CR A   inner join CK_BanCP_KC D on A.MJID=D.MJID inner join wfb_mjjy G ON A.MJID=G.MJID LEFT join JYOrder_Main B on A.MainId=B.MainId ');
      SQL.Add('LEFT join JYOrder_Sub C on A.SubId=C.SubId   where A.CRFlag=''入库'' and D.KCValid=''Y'' AND A.ISNowCycle=1 and (D.KCKGQty>0 OR D.KCQTY>0) ');
      if CheckBox1.Checked = True then
      begin
        sql.Add(' and   A.CRTime>' + quotedstr(FormatDateTime('yyyy-MM-dd', BegDate.DateTime)));
        sql.Add(' and A.CRTime<=' + quotedstr(FormatDateTime('yyyy-MM-dd', enddate.DateTime + 1)));
      end;
      if CPType.Text = '正品' then
      begin
        sql.Add('and c_dj<>''次品'' and c_dj<>''次布'' and c_dj<>''二级品''  ')
      end
      else if CPType.text = '次品' then
      begin
        sql.Add('and c_dj IN (''次品'',''次布'',''二级品'' )');
      end;

      if (QTYUNIT.Text = '米') then
      begin
        sql.Add('and (QtyUnit=''米'' or qtyunit=''M'')')
      end
      else if QTYUNIT.text = '码' then
      begin
        sql.Add('and(QtyUnit=''码'' or qtyunit=''Y'')');
      end
      else if QTYUNIT.text = '公斤' then
      begin
        sql.Add('and(QtyUnit=''公斤'' or qtyunit=''KG'')');
      end;
      sql.Add(')aa where 1=1 ');
      sql.Add(Pwhere);
      SQL.Add('group by  convert(varchar(10),CRTIME,120),QtyUnit,CPType,ganghao,DDBZ,KuanHao,c_ywy,MPRTCode,MPRTMF,MPRTKZ ,PRTColor,PrtKuanNo,c_kuwei,JLNOTE ,ISSD');

      Open;
    end;
    SCreateCDS20(ADOQueryMain, CDS_Main);
    SInitCDSData20(ADOQueryMain, CDS_Main);
  finally
    ADOQueryMain.EnableControls;
  end;
end;

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

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

procedure TfrmCKProductBCPKCHZList.TBCloseClick(Sender: TObject);
begin
  WriteCxGrid(trim(self.Caption), Tv1, '成品仓库');
  Close;
end;

procedure TfrmCKProductBCPKCHZList.FormShow(Sender: TObject);
begin
  ReadCxGrid(trim(self.Caption), Tv1, '成品仓库');
  if CANSHU1 = '查询' then
  begin
    ToolButton1.Visible := false;

  end
  else
  begin
    ToolButton1.Visible := True;
  end;
end;

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

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

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

procedure TfrmCKProductBCPKCHZList.CheckBox1Click(Sender: TObject);
begin
  InitGrid();
end;

procedure TfrmCKProductBCPKCHZList.CPTypeChange(Sender: TObject);
begin
  InitGrid();
end;

procedure TfrmCKProductBCPKCHZList.ToolButton1Click(Sender: TObject);
begin
  BZ.Text := CDS_Main.fieldbyname('DDBZ').AsString;
  Panel5.Visible := True;
end;

procedure TfrmCKProductBCPKCHZList.Button5Click(Sender: TObject);
begin
  if CDS_Main.IsEmpty then
    Exit;

//  if trim(bz.Text) = '' then
//  begin
//    Application.MessageBox('备注不能为空!', '提示', 0);
//    Exit;
//  end;
  try
    CDS_Main.DisableControls;
    ADOQueryCmd.Connection.BeginTrans;
    with CDS_Main do
    begin

      with ADOQueryCmd do
      begin
        Close;
        sql.Clear;
        sql.Add('Update WFB_MJJY  Set DDBZ=''' + trim(BZ.Text) + ''' ');
        SQL.Add('WHERE MJID IN(SELECT A.MJID FROM CK_BanCP_KC A INNER JOIN CK_BANCP_CR B ON A.MJID=B.MJID AND B.CRFLAG=''入库'' WHERE ');
        SQL.Add('ISNULL(C_CODE,'''')=' + QuotedStr(CDS_Main.fieldbyname('MPRTCode').AsString));
        SQL.Add('AND ISNULL(C_GANGNO,'''')=' + QuotedStr(CDS_Main.fieldbyname('ganghao').AsString));
        SQL.Add('AND ISNULL(C_COLOR,'''')=' + QuotedStr(CDS_Main.fieldbyname('PRTColor').AsString));
        SQL.Add('AND ISNULL(C_DJ,'''')=' + QuotedStr(CDS_Main.fieldbyname('CPType').AsString));
        SQL.Add('AND ISNULL(C_MF,'''')=' + QuotedStr(CDS_Main.fieldbyname('MPRTMF').AsString));
        SQL.Add('AND ISNULL(C_KZ,'''')=' + QuotedStr(CDS_Main.fieldbyname('MPRTKZ').AsString));
        SQL.Add('AND ISNULL(C_YWY,'''')=' + QuotedStr(CDS_Main.fieldbyname('c_ywy').AsString));
        SQL.Add('AND convert(varchar(10),CRTIME,120)=' + QuotedStr(CDS_Main.fieldbyname('CRTIME').AsString));
//        SQL.Add('ISNULL(C_CODE,'''')=' + QuotedStr(CDS_Main.fieldbyname('MPRTCode').AsString));
//        SQL.Add('ISNULL(C_CODE,'''')=' + QuotedStr(CDS_Main.fieldbyname('MPRTCode').AsString));


        SQL.Add(')');
//        ShowMessage(sql.Text);

        ExecSQL;
      end;

    end;
    with CDS_Main do
    begin
      Edit;
      FieldbyName('DDBZ').AsString := Trim(BZ.Text);
      Post;
    end;
    tv1.Controller.EditingController.ShowEdit();
    ADOQueryCmd.Connection.CommitTrans;
    CDS_Main.EnableControls;
    application.MessageBox('数据保存成功！', '提示信息');
    Panel5.Visible := false;

    exit;
  except
    ADOQueryCmd.Connection.RollbackTrans;
    CDS_Main.EnableControls;
    application.MessageBox('数据保存失败！', '提示信息', 0);
  end;
end;

procedure TfrmCKProductBCPKCHZList.Button6Click(Sender: TObject);
begin
  Panel5.Visible := False;
end;

procedure TfrmCKProductBCPKCHZList.orderNoKeyPress(Sender: TObject; var Key: Char);
begin
  if Key = #13 then
  begin
    InitGrid();
  end;
end;

end.