unit U_CKProductBCcbPKCHZList;

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, cxTextEdit, cxCheckBox;

type
  TfrmCKProductBCcbPKCHZList = 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;
    MPRTCodeName: TEdit;
    Label1: TLabel;
    Label2: TLabel;
    BegDate: TDateTimePicker;
    EndDate: TDateTimePicker;
    CDS_Main: TClientDataSet;
    Tv1: TcxGridDBTableView;
    cxGrid2Level1: TcxGridLevel;
    cxGrid2: TcxGrid;
    v2Column1: TcxGridDBColumn;
    v2Column3: TcxGridDBColumn;
    v2Column5: TcxGridDBColumn;
    v2Column6: TcxGridDBColumn;
    v1Column1: TcxGridDBColumn;
    Label5: TLabel;
    orderNo: TEdit;
    Label6: TLabel;
    MJID: TEdit;
    v1Column7: TcxGridDBColumn;
    v1Column6: TcxGridDBColumn;
    v1Column8: TcxGridDBColumn;
    v1Column9: TcxGridDBColumn;
    Label8: TLabel;
    MPRTKZ: TEdit;
    Label9: TLabel;
    MPRTMF: TEdit;
    Label7: TLabel;
    CPType: TComboBox;
    v1Column3: TcxGridDBColumn;
    MovePanel2: TMovePanel;
    PRTColor: TComboBox;
    RKplace: TEdit;
    Label13: TLabel;
    v1Column13: TcxGridDBColumn;
    Label10: TLabel;
    ConNo: TEdit;
    v1Column2: TcxGridDBColumn;
    v1Column4: TcxGridDBColumn;
    v1Column5: TcxGridDBColumn;
    Label11: TLabel;
    Label12: TLabel;
    PRTHX: TEdit;
    ywy: TEdit;
    v1Column11: TcxGridDBColumn;
    Label14: TLabel;
    PRTEColor: TEdit;
    Label15: TLabel;
    KuanHao: TEdit;
    v1Column12: TcxGridDBColumn;
    Label16: TLabel;
    MPRTCode: TEdit;
    v1Column14: TcxGridDBColumn;
    Tv1Column1: TcxGridDBColumn;
    Tv1Column2: TcxGridDBColumn;
    Label17: TLabel;
    lidan: TEdit;
    Tv1Column3: TcxGridDBColumn;
    Tv1Column4: TcxGridDBColumn;
    Tv1Column5: TcxGridDBColumn;
    Tv1Column6: TcxGridDBColumn;
    Tv1Column7: TcxGridDBColumn;
    ToolButton1: TToolButton;
    Tv1Column8: 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 Tv1Column3PropertiesEditValueChanged(Sender: TObject);
    procedure Tv1Column4PropertiesButtonClick(Sender: TObject; AButtonIndex: Integer);
    procedure Tv1CustomDrawCell(Sender: TcxCustomGridTableView; ACanvas: TcxCanvas; AViewInfo: TcxGridTableDataCellViewInfo; var ADone: Boolean);
    procedure v1Column3PropertiesChange(Sender: TObject);
    procedure Tv1Column7PropertiesEditValueChanged(Sender: TObject);
    procedure ToolButton1Click(Sender: TObject);
  private
    procedure InitGrid();
    { Private declarations }
  public
    canshu1: string;
    { Public declarations }
  end;

var
  frmCKProductBCcbPKCHZList: TfrmCKProductBCcbPKCHZList;

implementation

uses
  U_DataLink, U_Fun, U_ZDYHelp, U_FileUp;

{$R *.dfm}

procedure TfrmCKProductBCcbPKCHZList.FormDestroy(Sender: TObject);
begin
  frmCKProductBCCBPKCHZList := nil;
end;

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

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

procedure TfrmCKProductBCcbPKCHZList.InitGrid();
begin
  try
    ADOQueryMain.DisableControls;
    with ADOQueryMain do
    begin
      Filtered := False;
      Close;
      sql.Clear;
      sql.Add('select SUM(A.KGQty) KGQty,SUM(A.Qty) Qty,SUM(A.MJYLen) MJYLen,SUM(A.NetKGQty) NetKGQty,count(A.MJID) JQty,A.QtyUnit,A.CPType  ');
      sql.Add(' ,A.KuanHao,B.ConNo,B.OrderNo,B.lidan,B.ywy,B.MPRTCode,B.MPRTCodeName,B.MPRTMF,B.MPRTKZ  ');
      SQL.Add(',COUNT(DISTINCT BAOID)BS,KCREASON');
      sql.Add(' ,C.PRTColor,C.PRTEColor,C.PRTHX,C.PrtKuanNo,D.CPKCNoet,D.CPKCStatus,A.MainId,A.SubId,CPYY,clfs ');
      SQL.Add(',CPYYy=isnull(( CPYY+'';处理方式为:''+clfs  ),'''')');
      SQL.Add(',ISSC=cast((case when (select COUNT(T.CYID) from XD_File T where T.FileType = ''异常成品'' and T.CYID=A.SUBID)=0 then 0 else 1 end) as bit )');
      sql.Add('from  CK_BanCP_CR A   inner join CK_BanCP_KC D on A.MJID=D.MJID inner join JYOrder_Main B on A.MainId=B.MainId  ');
      sql.Add('inner join JYOrder_Sub C on A.SubId=C.SubId  inner join ck_sxpb_cr k on k.spid=a.apid where A.CRFlag=''入库'' and D.KCValid=''Y''AND ISNULL(D.CPKCStatus,'''')<>''''  ');
      if canshu1 = '处理' then
      begin
        sql.Add('and (B.ywy =' + QuotedStr(Trim(DName)));
        sql.Add('or B.lidan =' + QuotedStr(Trim(DName)));
        sql.Add(')');
      end;
      sql.Add(' group by  A.QtyUnit,A.MainId,A.SubId,A.CPType,D.CPKCNoet,D.CPKCStatus ,KCREASON,A.KuanHao,B.ConNo,B.OrderNo,B.lidan,B.ywy,B.MPRTCode,B.MPRTCodeName,B.MPRTMF,B.MPRTKZ ,isnull(( CPYY+'';处理方式为:''+clfs  ),''''),C.PRTColor');
      SQL.Add(',C.PRTEColor,C.PRTHX,C.PrtKuanNo,CPYY,clfs,KCREASON');
//      ShowMessage(SQL.Text);
      Open;                                            //,C.KCNote,C.KCZTStatus
    end;
    SCreateCDS20(ADOQueryMain, CDS_Main);
    SInitCDSData20(ADOQueryMain, CDS_Main);
  finally
    ADOQueryMain.EnableControls;
  end;
  TBFind.Click;
end;

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

  MovePanel2.Visible := False;
end;

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

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

procedure TfrmCKProductBCcbPKCHZList.FormShow(Sender: TObject);
begin
  ReadCxGrid(trim(self.Caption), Tv1, '成品仓库');
end;

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

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

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

procedure TfrmCKProductBCcbPKCHZList.Tv1Column3PropertiesEditValueChanged(Sender: TObject);
var
  mvalue, FFieldName: string;
begin

  mvalue := TcxTextEdit(Sender).EditingText;
  FFieldName := Trim(Tv1.Controller.FocusedColumn.DataBinding.FilterFieldName);
  with CDS_Main do
  begin
    Edit;
    FieldByName(FFieldName).Value := mvalue;
    Post;
  end;

  with ADOQueryCmd do
  begin
    Close;
    SQL.Clear;
    sql.Add('update CK_BanCP_KC set  ' + FFieldName + '=' + quotedstr(Trim(mvalue)));
    sql.Add(' where   KCValid=''Y'' and MJID in (select X.MJID from CK_BanCP_CR X where X.CRFlag=''入库'' ');
    sql.Add(' and isnull(X.Subid,'''')=' + quotedstr(Trim(CDS_Main.fieldbyname('Subid').AsString)));
    sql.Add(' and isnull(X.QtyUnit,'''')=' + quotedstr(Trim(CDS_Main.fieldbyname('QtyUnit').AsString)));
    sql.Add(' and isnull(X.CPType,'''')=' + quotedstr(Trim(CDS_Main.fieldbyname('CPType').AsString)));

    sql.Add(' and isnull(X.KuanHao,'''')=' + quotedstr(Trim(CDS_Main.fieldbyname('KuanHao').AsString)) + ')');

    SQL.Add('AND isnull(CPKCStatus,'''')=' + QuotedStr(Trim(CDS_Main.fieldbyname('CPKCStatus').AsString)));

    SQL.Add('and (select isnull(CPYY,'''') from ck_sxpb_cr where spid=(select apid from wfb_mjjy  f where f.mjid=CK_BanCP_KC.mjid ))=' + QuotedStr(Trim(CDS_Main.fieldbyname('cpyy').AsString)));
    SQL.Add('and (select isnull(clfs,'''') from ck_sxpb_cr where spid=(select apid from wfb_mjjy  f where f.mjid=CK_BanCP_KC.mjid ))=' + QuotedStr(Trim(CDS_Main.fieldbyname('clfs').AsString)));
//    ShowMessage(sql.Text);
    ExecSQL;
  end;
  Tv1.Controller.EditingController.ShowEdit();
end;

procedure TfrmCKProductBCcbPKCHZList.Tv1Column4PropertiesButtonClick(Sender: TObject; AButtonIndex: Integer);
var
  mvalue, FFieldName: string;
begin
  try
    frmZDYHelp := TfrmZDYHelp.Create(Application);
    with frmZDYHelp do
    begin
      flag := 'KCZTStatus';
      flagname := '库存状态';
      if ShowModal = 1 then
      begin
        mvalue := Trim(ClientDataSet1.fieldbyname('ZDYName').AsString);
      end;
    end;
  finally
    frmZDYHelp.Free;
  end;
  with CDS_Main do
  begin
    Edit;
    FieldByName('CPKCStatus').Value := mvalue;
    Post;
  end;

  with ADOQueryCmd do
  begin
    Close;
    SQL.Clear;
    sql.Add('update CK_BanCP_KC set  CPKCStatus=' + quotedstr(Trim(mvalue)));
    sql.Add(' where   KCValid=''Y'' and MJID in (select X.MJID from CK_BanCP_CR X  where X.CRFlag=''入库'' ');
    sql.Add(' and isnull(X.Subid,'''')=' + quotedstr(Trim(CDS_Main.fieldbyname('Subid').AsString)));
    sql.Add(' and isnull(X.QtyUnit,'''')=' + quotedstr(Trim(CDS_Main.fieldbyname('QtyUnit').AsString)));
    sql.Add(' and isnull(X.CPType,'''')=' + quotedstr(Trim(CDS_Main.fieldbyname('CPType').AsString)));

    sql.Add(' and isnull(X.KuanHao,'''')=' + quotedstr(Trim(CDS_Main.fieldbyname('KuanHao').AsString)) + ')');
    SQL.Add('AND isnull(CPKCNoet,'''')=' + QuotedStr(Trim(CDS_Main.fieldbyname('CPKCNoet').AsString)));
//    SQL.Add('AND isnull(CPKCStatus,'''')=' + QuotedStr(Trim(CDS_Main.fieldbyname('CPKCStatus').AsString)));
//    ShowMessage(sql.Text);
    ExecSQL;
  end;
  tv1.Controller.EditingController.ShowEdit();
end;

procedure TfrmCKProductBCcbPKCHZList.Tv1CustomDrawCell(Sender: TcxCustomGridTableView; ACanvas: TcxCanvas; AViewInfo: TcxGridTableDataCellViewInfo; var ADone: Boolean);
begin
  if AViewInfo.GridRecord.Values[tv1.GetColumnByFieldName('CPKCStatus').Index] = null then
    Exit;
  if AViewInfo.GridRecord.Values[tv1.GetColumnByFieldName('CPKCStatus').Index] <> '' then
    ACanvas.Brush.Color := $008080FF;
end;

procedure TfrmCKProductBCcbPKCHZList.v1Column3PropertiesChange(Sender: TObject);
var
  mvalue, FFieldName: string;
  msubid, mcptype, mqtyunit: string;
begin
  msubid := Trim(CDS_Main.fieldbyname('subid').AsString);
  mcptype := Trim(CDS_Main.fieldbyname('cptype').AsString);
  mqtyunit := Trim(CDS_Main.fieldbyname('qtyunit').AsString);

  mvalue := TcxTextEdit(Sender).EditingText;
  FFieldName := Trim(Tv1.Controller.FocusedColumn.DataBinding.FilterFieldName);
  if Application.MessageBox(PChar('确定要将' + Trim(CDS_Main.fieldbyname('MPRTCode').AsString) + ' ' + Trim(CDS_Main.fieldbyname('PRTColor').AsString) + ' 调整为' + mvalue + '吗？'), '提示', 32 + 4) <> IDYES then
    Exit;
  with ADOQueryTemp do
  begin
    Close;
    sql.Clear;
    sql.Add('select SUM(A.KGQty) KGQty,SUM(A.Qty) Qty,SUM(A.MJYLen) MJYLen,SUM(A.NetKGQty) NetKGQty,count(A.MJID) JQty,A.QtyUnit,A.CPType  ');
    sql.Add(' ,A.KuanHao,B.ConNo,B.OrderNo,B.lidan,B.ywy,B.MPRTCode,B.MPRTCodeName,B.MPRTMF,B.MPRTKZ  ');
    sql.Add(' ,C.PRTColor,C.PRTEColor,C.PRTHX,C.PrtKuanNo,C.KCNote,C.KCZTStatus,A.MainId,A.SubId ');
    sql.Add('from  CK_BanCP_CR A   inner join CK_BanCP_KC D on A.MJID=D.MJID inner join JYOrder_Main B on A.MainId=B.MainId  ');
    sql.Add('inner join JYOrder_Sub C on A.SubId=C.SubId    ');
    sql.Add(' where A.CRFlag=''入库'' and D.KCValid=''Y'' ');
    sql.Add('and A.subid=' + quotedstr(msubid));
    sql.Add('and A.CPtype=' + quotedstr(mcptype));
    sql.Add('and A.qtyunit=' + quotedstr(mqtyunit));
    sql.Add(' group by  A.QtyUnit,A.MainId,A.SubId,A.CPType,C.KCNote,C.KCZTStatus ,A.KuanHao,B.ConNo,B.OrderNo,B.lidan,B.ywy,B.MPRTCode,B.MPRTCodeName,B.MPRTMF,B.MPRTKZ ,C.PRTColor,C.PRTEColor,C.PRTHX,C.PrtKuanNo');
    Open;
  end;

  if ADOQueryTemp.RecordCount > 1 then
  begin
    Application.MessageBox('更新数据超出指定范围,更新失败!', '提示', 0);
    Exit;
  end;

  with ADOQueryCmd do
  begin
    Close;
    SQL.Clear;
    sql.Add('update WFB_MJJY set  mjtype=' + quotedstr(Trim(mvalue)));
    sql.Add('where exists(select mjid from CK_BanCP_kc D WHERE D.MJID=wfb_mjjy.MJID AND D.KCVALID=''Y'')');
    sql.Add('and subid=' + quotedstr(msubid));
    sql.Add('and mjtype=' + quotedstr(mcptype));
    sql.Add('and MJTypeOther=' + quotedstr(mqtyunit));

    sql.Add('update CK_BanCP_CR set  CPType=' + quotedstr(Trim(mvalue)));
    sql.Add('where exists(select mjid from CK_BanCP_kc D WHERE D.MJID=CK_BanCP_CR.MJID AND D.KCVALID=''Y'')');
    sql.Add('and subid=' + quotedstr(msubid));
    sql.Add('and CPtype=' + quotedstr(mcptype));
    sql.Add('and qtyunit=' + quotedstr(mqtyunit));

    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(msubid + ':' + mcptype + '>>>>>' + mvalue)));
    sql.Add(',' + quotedstr(trim('成功')));
    sql.Add(')');

//    ShowMessage(SQL.Text);
    ExecSQL;
  end;
  with CDS_Main do
  begin
    Edit;
    FieldByName(FFieldName).Value := mvalue;
    Post;
  end;

  Tv1.Controller.EditingController.ShowEdit();
end;

procedure TfrmCKProductBCcbPKCHZList.Tv1Column7PropertiesEditValueChanged(Sender: TObject);
var
  mvalue, FFieldName: string;
begin

  mvalue := TcxTextEdit(Sender).EditingText;
  FFieldName := Trim(Tv1.Controller.FocusedColumn.DataBinding.FilterFieldName);
  with CDS_Main do
  begin
    Edit;
    FieldByName(FFieldName).Value := mvalue;
    Post;
  end;

  with ADOQueryCmd do
  begin
    Close;
    SQL.Clear;
    sql.Add('update CK_BanCP_KC set  ' + FFieldName + '=' + quotedstr(Trim(mvalue)));
    sql.Add(' where   KCValid=''Y'' and MJID in (select X.MJID from CK_BanCP_CR X where X.CRFlag=''入库'' ');
    sql.Add(' and isnull(X.Subid,'''')=' + quotedstr(Trim(CDS_Main.fieldbyname('Subid').AsString)));
    sql.Add(' and isnull(X.QtyUnit,'''')=' + quotedstr(Trim(CDS_Main.fieldbyname('QtyUnit').AsString)));
    sql.Add(' and isnull(X.CPType,'''')=' + quotedstr(Trim(CDS_Main.fieldbyname('CPType').AsString)));

    sql.Add(' and isnull(X.KuanHao,'''')=' + quotedstr(Trim(CDS_Main.fieldbyname('KuanHao').AsString)) + ')');

    SQL.Add('AND isnull(CPKCStatus,'''')=' + QuotedStr(Trim(CDS_Main.fieldbyname('CPKCStatus').AsString)));

    SQL.Add('and (select isnull(CPYY,'''') from ck_sxpb_cr where spid=(select apid from wfb_mjjy  f where f.mjid=CK_BanCP_KC.mjid ))=' + QuotedStr(Trim(CDS_Main.fieldbyname('cpyy').AsString)));
    SQL.Add('and (select isnull(clfs,'''') from ck_sxpb_cr where spid=(select apid from wfb_mjjy  f where f.mjid=CK_BanCP_KC.mjid ))=' + QuotedStr(Trim(CDS_Main.fieldbyname('clfs').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('Subid：' + trim(CDS_Main.fieldbyname('Subid').AsString) + '改为：' + trim(mvalue))));
    sql.Add(',' + quotedstr(trim('成功')));
    sql.Add(')');
//    ShowMessage(sql.Text);
    ExecSQL;
  end;
  Tv1.Controller.EditingController.ShowEdit();
end;

procedure TfrmCKProductBCcbPKCHZList.ToolButton1Click(Sender: TObject);
begin
  try
    frmFileUp := TfrmFileUp.Create(Application);
    with frmFileUp do
    begin
      Code.Text := Trim(Self.CDS_Main.fieldbyname('SUBID').AsString);
      CYID := Trim(Self.CDS_Main.fieldbyname('SUBID').AsString);
      if ShowModal = 1 then
      begin
        Self.InitGrid();
        Self.CDS_Main.Locate('SUBID', CYID, []);
      end;
    end;
  finally
    frmFileUp.Free;
  end;
end;

end.