unit U_KuLinHZList;

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, BtnEdit,
  cxPC, cxLookAndFeels, cxLookAndFeelPainters, cxNavigator,
  dxBarBuiltInMenu;

type
  TfrmKuLinHZList = class(TForm)
    ToolBar1: TToolBar;
    TBRafresh: TToolButton;
    TBFind: TToolButton;
    TBExport: TToolButton;
    TBClose: TToolButton;
    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;
    ADOQueryPrint: TADOQuery;
    tbJS: TToolButton;
    cxGrid2: TcxGrid;
    Tv1: TcxGridDBTableView;
    v1Column21: TcxGridDBColumn;
    v1Column13: TcxGridDBColumn;
    v2Column1: TcxGridDBColumn;
    v2Column6: TcxGridDBColumn;
    v1Column15: TcxGridDBColumn;
    v1Column17: TcxGridDBColumn;
    v1Column18: TcxGridDBColumn;
    v1Column5: TcxGridDBColumn;
    v1Column24: TcxGridDBColumn;
    cxGrid2Level1: TcxGridLevel;
    Panel1: TPanel;
    Label2: TLabel;
    Label3: TLabel;
    Label7: TLabel;
    Label1: TLabel;
    Label10: TLabel;
    BegDate: TDateTimePicker;
    EndDate: TDateTimePicker;
    MPRTCodeName: TEdit;
    ConNo: TEdit;
    chkdate: TCheckBox;
    lidan: TEdit;
    YWY: TEdit;
    cxTabControl1: TcxTabControl;
    tbJSCX: TToolButton;
    ToolButton1: TToolButton;
    v1Column1: TcxGridDBColumn;
    Panel2: TPanel;
    Panel3: TPanel;
    Panel4: TPanel;
    Panel5: TPanel;
    tbSQJS: TToolButton;
    tbSQJSCX: TToolButton;
    Panel6: TPanel;
    lblorderNO: TLabel;
    lblmainID: TLabel;
    Button1: TButton;
    Button2: TButton;
    Memo1: TMemo;
    v1Column2: TcxGridDBColumn;
    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 N1Click(Sender: TObject);
    procedure N2Click(Sender: TObject);
    procedure SPIDKeyPress(Sender: TObject; var Key: Char);
    procedure SPIDChange(Sender: TObject);
    procedure SPSpecChange(Sender: TObject);
    procedure FactoryNameChange(Sender: TObject);
    procedure Tv1CustomDrawCell(Sender: TcxCustomGridTableView; ACanvas: TcxCanvas; AViewInfo: TcxGridTableDataCellViewInfo; var ADone: Boolean);
    procedure tbJSClick(Sender: TObject);
    procedure tbJSCXClick(Sender: TObject);
    procedure cxTabControl1Change(Sender: TObject);
    procedure ToolButton1Click(Sender: TObject);
    procedure Panel2Click(Sender: TObject);
    procedure Panel4Click(Sender: TObject);
    procedure Panel3Click(Sender: TObject);
    procedure Panel5Click(Sender: TObject);
    procedure tbSQJSClick(Sender: TObject);
    procedure tbSQJSCXClick(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
  private
    procedure SetStatus();
    procedure InitGrid(KLScope: string);
    { Private declarations }
  public
    canshu1: string;
    { Public declarations }
  end;

implementation

uses
  U_DataLink, U_RTFun, U_ZDYHelp;

{$R *.dfm}

procedure TfrmKuLinHZList.SetStatus();
begin
  tbJS.Visible := False;
  tbJSCX.Visible := False;
  tbSQJS.Visible := False;
  tbSQJSCX.Visible := False;
  if Trim(canshu1) = '管理' then
  begin
    case cxTabControl1.TabIndex of
      1:
        begin
          tbJS.Visible := true;

        end;
      2:
        begin

          tbJSCX.Visible := true;
        end;

    end;
  end;
  if Trim(canshu1) = '提交' then
  begin
    case cxTabControl1.TabIndex of
      0:
        begin
          tbSQJS.Visible := true;

        end;
      1:
        begin

          tbSQJSCX.Visible := true;
        end;

    end;
  end;
end;

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

procedure TfrmKuLinHZList.FormCreate(Sender: TObject);
begin
  EndDate.DateTime := SGetServerDate10(ADOQueryTemp);
  BegDate.DateTime := EndDate.DateTime;

end;

procedure TfrmKuLinHZList.InitGrid(KLScope: string);
var
  WSql: string;
begin
  try
    ADOQueryMain.DisableControls;
    with ADOQueryMain do
    begin
      Close;
      sql.Clear;

      {sql.Add(' update CK_BanCP_KC set CKDate=DATEDIFF(DD,0,getdate()) where');
      sql.Add('	not exists (select DATEDIFF(DD,0,CRTime) from CK_BanCP_CR X where X.MJID=CK_BanCP_KC.MJID and X.CRFlag=''出库'')');
      sql.Add(' and  RKDate>0 ');

      sql.Add(' select PJKuLin=AA.KuLin/AA.RKPS,AA.KCPS,AA.RKPS,CKPS=AA.RKPS-AA.KCPS,AA.RKDate,AA.CKDate,AA.KCMainId');
      sql.Add(' ,B.ConNo,B.lidan,B.YWY,B.MPRTCodeName,B.MPRTCode,B.MPRTMF,B.MPRTKZ,B.ISHZKuLin');
      sql.Add(',zt=(case when ISHZKuLin=1 then ''未结束'' else ''已结束'' end)  ');
      sql.Add('  from (');
      sql.Add('	select A.KCMainId,SUM(A.RKDate) RKDate,SUM(A.CKDate) CKDate');
      sql.Add(' ,SUM(A.CKDate-A.RKDate) KuLin,COUNT(A.MJID) RKPS,KCPS=SUM(case when KCValid=''Y'' then 1 else 0 end) ');
      sql.Add('	from CK_BanCP_KC A where  A.RKDate > 0 ');
      sql.Add('	and exists (select MJID from CK_BanCP_CR X where X.MJID=A.MJID and X.CRFlag=''入库'')');
      sql.Add('	group by A.KCMainId');
      sql.Add('	) AA');
      sql.Add('inner join JYOrder_Main B on AA.KCMainId=B.MainId');   }

      case cxTabControl1.TabIndex of
        0:
          begin
            WSql := ' where	B.ISHZKuLin=1 and B.ISSQJieShu=0 ';
          end;
        1:
          begin
            WSql := ' where	B.ISHZKuLin=1 and B.ISSQJieShu=1  ';
          end;
        2:
          begin
            WSql := ' where	B.ISHZKuLin=0 ';
          end;
        3:
          begin
            WSql := ' where	1=1 ';
          end;
      end;

      if KLScope = '15到30天' then
      begin
        WSql := WSql + ' and AA.PJKuLin>=15 ';
        WSql := WSql + ' and AA.PJKuLin<30 ';
      end;

      if KLScope = '30到45天' then
      begin
        WSql := WSql + ' and AA.PJKuLin>=30 ';
        WSql := WSql + ' and AA.PJKuLin<45 ';
      end;

      if KLScope = '45到60天' then
      begin
        WSql := WSql + ' and AA.PJKuLin>=45 ';
        WSql := WSql + ' and AA.PJKuLin<60 ';
      end;

      if KLScope = '60天以上' then
      begin
        WSql := WSql + ' and AA.PJKuLin>=60 ';
      end;

      sql.Add('exec P_View_KuLinHZ ');
      sql.Add(' @criteria=' + QuotedStr(WSql));
//      ShowMessage(SQL.Text);
      Open;
    end;
    SCreateCDS20(ADOQueryMain, CDS_Main);
    SInitCDSData20(ADOQueryMain, CDS_Main);
    CDS_Main.First;
  finally
    ADOQueryMain.EnableControls;
  end;

end;

procedure TfrmKuLinHZList.TBRafreshClick(Sender: TObject);
begin
  ConNo.SetFocus;

  InitGrid('');

end;

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

procedure TfrmKuLinHZList.TBCloseClick(Sender: TObject);
begin
  WriteCxGrid(trim(self.Caption), Tv1, '库龄分析');
  Close;
end;

procedure TfrmKuLinHZList.FormShow(Sender: TObject);
begin
  SetStatus();

  ReadCxGrid(trim(self.Caption), Tv1, '库龄分析');

  InitGrid('');

end;

procedure TfrmKuLinHZList.TBExportClick(Sender: TObject);
begin
  if ADOQueryMain.IsEmpty then
    exit;
  TcxGridToExcel('待检布入库列表', cxGrid2);
end;

procedure TfrmKuLinHZList.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 TfrmKuLinHZList.N1Click(Sender: TObject);
begin
  SelOKNo(CDS_Main, True);
end;

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

procedure TfrmKuLinHZList.SPIDKeyPress(Sender: TObject; var Key: Char);
begin
  if Key = #13 then
  begin
    {if Length(Trim(SPID.Text))<4 then Exit;
    try
      ADOQueryMain.DisableControls;
      with ADOQueryMain do
      begin
        Filtered:=False;
        Close;
        sql.Clear;
        sql.Add(' select A.* ');
        sql.Add(',ConNo=(select ConNo from Contract_Main CM where CM.MainId=A.CGMainId)');
        //sql.Add(' ,ConNo=(select ConNo from Contract_Main CM where CM.MainId=A.CGMainId )');
        sql.Add(' from CK_SXPB_CR A');
        sql.add(' where SPID like :SXID');
        Parameters.ParamByName('SXID').Value:='%'+Trim(SXID.Text)+'%';
        Open;
        //ShowMessage(SQL.Text);
      end;
      SCreateCDS20(ADOQueryMain,CDS_Main);
      SInitCDSData20(ADOQueryMain,CDS_Main);
    finally
      ADOQueryMain.EnableControls;
    end; }
  end;
end;

procedure TfrmKuLinHZList.SPIDChange(Sender: TObject);
begin

  TBFind.Click;
end;

procedure TfrmKuLinHZList.SPSpecChange(Sender: TObject);
begin
  TBFind.Click;
end;

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

procedure TfrmKuLinHZList.Tv1CustomDrawCell(Sender: TcxCustomGridTableView; ACanvas: TcxCanvas; AViewInfo: TcxGridTableDataCellViewInfo; var ADone: Boolean);
var
  FKuLin: double;
begin
     // 库龄 白15天 绿15到30天 蓝30到45天  黄45到60天  红60天以上  是

  if AViewInfo.GridRecord.Values[TcxGridDBTableView(Sender).GetColumnByFieldName('PJKuLin').Index] = null then
    Exit;
  FKuLin := AViewInfo.GridRecord.Values[TcxGridDBTableView(Sender).GetColumnByFieldName('PJKuLin').Index];

  if ((FKuLin < 30) and (FKuLin >= 15)) then
  begin
    ACanvas.Brush.Color := $00FF00;
  end;
  if ((FKuLin < 45) and (FKuLin >= 30)) then
  begin
    ACanvas.Brush.Color := $ED9564;
  end;
  if ((FKuLin < 60) and (FKuLin >= 45)) then
  begin
    ACanvas.Brush.Color := $00FFFF;
  end;
  if FKuLin >= 60 then
  begin
    ACanvas.Brush.Color := $4763FF;
  end;

end;

procedure TfrmKuLinHZList.tbJSClick(Sender: TObject);
begin
  if CDS_Main.IsEmpty then
    exit;
  if Application.MessageBox('是否核实好数据？', '提示', 32 + 4) <> IDYES then
    Exit;
  try
    with ADOQueryCmd do
    begin
      close;
      sql.Clear;
      sql.Add('update  JYOrder_Main SET ISHZKuLin=''0'' ');
      sql.Add('where mainid=' + quotedstr(trim(CDS_Main.fieldbyname('KCMainId').AsString)));
      sql.Add('insert into SY_SysLog(operor,opertime,Model,acction,opevent,result) values( ');
      sql.Add(' ' + quotedstr(trim(DName)));
      sql.Add(',getdate() ');
      sql.Add(',' + quotedstr(trim(self.Caption)));
      sql.Add(',' + quotedstr(trim('库龄结束')));
      sql.Add(',' + quotedstr(trim('订单号：' + trim(CDS_Main.FieldByName('ConNo').AsString) + ' mainid：' + trim(CDS_Main.FieldByName('KCMainId').AsString))));
      sql.Add(',' + quotedstr(trim('成功')));
      sql.Add(')');
      execsql;
    end;
    application.MessageBox('操作成功！', '提示信息');
    CDS_Main.Delete;
  except
    application.MessageBox('操作失败！', '提示信息', 0);
  end;
end;

procedure TfrmKuLinHZList.tbJSCXClick(Sender: TObject);
begin
  if CDS_Main.IsEmpty then
    exit;
  if Application.MessageBox('是否核实好数据？', '提示', 32 + 4) <> IDYES then
    Exit;
  try
    with ADOQueryCmd do
    begin
      close;
      sql.Clear;
      sql.Add('update  JYOrder_Main SET ISHZKuLin=''1'' ');
      sql.Add('where mainid=' + quotedstr(trim(CDS_Main.fieldbyname('KCMainId').AsString)));
      sql.Add('insert into SY_SysLog(operor,opertime,Model,acction,opevent,result) values( ');
      sql.Add(' ' + quotedstr(trim(DName)));
      sql.Add(',getdate() ');
      sql.Add(',' + quotedstr(trim(self.Caption)));
      sql.Add(',' + quotedstr(trim('库龄结束')));
      sql.Add(',' + quotedstr(trim('订单号：' + trim(CDS_Main.FieldByName('ConNo').AsString) + ' mainid：' + trim(CDS_Main.FieldByName('KCMainId').AsString))));
      sql.Add(',' + quotedstr(trim('成功')));
      sql.Add(')');
      execsql;
    end;
    application.MessageBox('操作成功！', '提示信息');
    CDS_Main.Delete;
  except
    application.MessageBox('操作失败！', '提示信息', 0);
  end;
end;

procedure TfrmKuLinHZList.cxTabControl1Change(Sender: TObject);
begin
  SetStatus();
  InitGrid('');
end;

procedure TfrmKuLinHZList.ToolButton1Click(Sender: TObject);
begin
  try
    with ADOQueryCmd do
    begin
      close;
//      sql.Add('update CK_BanCP_KC set RKDate=isnull((select DATEDIFF(DD,0,CRTime) from CK_SXPB_CR X where X.SPID=CK_BanCP_KC.APID ),0) ');
      sql.Add('update CK_BanCP_KC set CKDate=isnull((select DATEDIFF(DD,0,CRTime) from CK_BanCP_CR X where X.MJID=CK_BanCP_KC.MJID and X.CRFlag=''出库''),0)');
      sql.Add(' update CK_SXPB_CR set PBRKDate=(PiQty-ISNULL(CPPS,0))*(DATEDIFF(DD,0,CRTime))');
      execsql;
    end;
    application.MessageBox('校准成功！', '提示信息');
    CDS_Main.Delete;
  except
    application.MessageBox('校准失败！', '提示信息', 0);
  end;
end;

procedure TfrmKuLinHZList.Panel2Click(Sender: TObject);
begin
  InitGrid('15到30天');
end;

procedure TfrmKuLinHZList.Panel4Click(Sender: TObject);
begin
  InitGrid('30到45天');
end;

procedure TfrmKuLinHZList.Panel3Click(Sender: TObject);
begin
  InitGrid('45到60天');
end;

procedure TfrmKuLinHZList.Panel5Click(Sender: TObject);
begin
  InitGrid('60天以上');
end;

procedure TfrmKuLinHZList.tbSQJSClick(Sender: TObject);
var
  str: string;
begin
  if CDS_Main.IsEmpty then
    exit;

  Memo1.Text := trim(CDS_Main.FieldByName('KLJSNote').AsString);
  lblmainID.Caption := trim(CDS_Main.fieldbyname('kcmainID').AsString);
//    lblorderNO.Caption := trim(Order_Main.FieldByName('orderNO').AsString);
  Panel6.Visible := True;
  cxGrid2.Enabled := False;

end;

procedure TfrmKuLinHZList.tbSQJSCXClick(Sender: TObject);
begin
  if CDS_Main.IsEmpty then
    exit;
  if Application.MessageBox('是否核实好数据？', '提示', 32 + 4) <> IDYES then
    Exit;
  try
    with ADOQueryCmd do
    begin
      close;
      sql.Clear;
      sql.Add('update  JYOrder_Main SET ISSQJieShu=''0'' ');
      sql.Add('where mainid=' + quotedstr(trim(CDS_Main.fieldbyname('KCMainId').AsString)));
      sql.Add('insert into SY_SysLog(operor,opertime,Model,acction,opevent,result) values( ');
      sql.Add(' ' + quotedstr(trim(DName)));
      sql.Add(',getdate() ');
      sql.Add(',' + quotedstr(trim(self.Caption)));
      sql.Add(',' + quotedstr(trim('库龄结束')));
      sql.Add(',' + quotedstr(trim('订单号：' + trim(CDS_Main.FieldByName('ConNo').AsString) + ' mainid：' + trim(CDS_Main.FieldByName('KCMainId').AsString))));
      sql.Add(',' + quotedstr(trim('成功')));
      sql.Add(')');
      execsql;
    end;
    application.MessageBox('操作成功！', '提示信息');
    CDS_Main.Delete;
  except
    application.MessageBox('操作失败！', '提示信息', 0);
  end;

end;

procedure TfrmKuLinHZList.Button1Click(Sender: TObject);
begin
  if CDS_Main.IsEmpty then
    exit;

  if Application.MessageBox('是否核实好数据？', '提示', 32 + 4) <> IDYES then
    Exit;
  try
    with ADOQueryCmd do
    begin
      close;
      sql.Clear;
      sql.Add('update  JYOrder_Main SET ISSQJieShu=''1'' ');
      sql.Add(',KLJSNote= ' + quotedstr(Trim(Memo1.text)));
      sql.Add('where mainid=' + quotedstr(trim(CDS_Main.fieldbyname('KCMainId').AsString)));
      sql.Add('insert into SY_SysLog(operor,opertime,Model,acction,opevent,result) values( ');
      sql.Add(' ' + quotedstr(trim(DName)));
      sql.Add(',getdate() ');
      sql.Add(',' + quotedstr(trim(self.Caption)));
      sql.Add(',' + quotedstr(trim('库龄结束')));
      sql.Add(',' + quotedstr(trim('订单号：' + trim(CDS_Main.FieldByName('ConNo').AsString) + ' mainid：' + trim(CDS_Main.FieldByName('KCMainId').AsString))));
      sql.Add(',' + quotedstr(trim('成功')));
      sql.Add(')');
      execsql;
    end;
    application.MessageBox('操作成功！', '提示信息');
    CDS_Main.Delete;
  except
    application.MessageBox('操作失败！', '提示信息', 0);
  end;
  Panel6.Visible := false;
  cxGrid2.Enabled := True;
end;

procedure TfrmKuLinHZList.Button2Click(Sender: TObject);
begin
  Panel6.Visible := false;
  cxGrid2.Enabled := True;
end;

end.