D7myChuangpin/创品码单管理(FMMD.dll)/U_CKProductBCPOutHZList.pas

unit U_CKProductBCPOutHZList;

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, RM_Common, RM_Class,
  RM_e_Xls, RM_Dataset, RM_System, RM_GridReport, cxCheckBox, Menus,
  MovePanel, cxSplitter, BtnEdit, cxTextEdit, cxPC, cxCalendar, TeeProcs,
  TeEngine, Chart, DbChart, Series;

type
  TfrmCKProductBCPOutHZList = class(TForm)
    ToolBar1: TToolBar;
    TBRafresh: TToolButton;
    TBFind: TToolButton;
    TBExport: TToolButton;
    TBClose: TToolButton;
    Panel1: TPanel;
    ADOQueryCmd: TADOQuery;
    ADOQueryMain: TADOQuery;
    ADOQueryTemp: TADOQuery;
    DataSource1: TDataSource;
    cxGridPopupMenu1: TcxGridPopupMenu;
    Label1: TLabel;
    CDS_Main: TClientDataSet;
    CDS_Print: TClientDataSet;
    RM1: TRMGridReport;
    RMDBMain: TRMDBDataSet;
    RMXLSExport1: TRMXLSExport;
    RMDBHZ: TRMDBDataSet;
    CDS_HZ: TClientDataSet;
    PopupMenu1: TPopupMenu;
    N1: TMenuItem;
    N2: TMenuItem;
    MovePanel2: TMovePanel;
    CDS_CX: TClientDataSet;
    CDS_DH: TClientDataSet;
    ADOQueryDH: TADOQuery;
    ADOQueryPrint: TADOQuery;
    cxGrid1: TcxGrid;
    Tv0: TcxGridDBTableView;
    cxGridDBColumn13: TcxGridDBColumn;
    cxXSPS: TcxGridDBColumn;
    cxGridLevel1: TcxGridLevel;
    ClientDataSet0: TClientDataSet;
    DataSource0: TDataSource;
    v0XSQty: TcxGridDBColumn;
    v0JGPS: TcxGridDBColumn;
    v0JGQty: TcxGridDBColumn;
    Label2: TLabel;
    Begdate: TDateTimePicker;
    EndDate: TDateTimePicker;
    DBChart1: TDBChart;
    RadioGroup1: TRadioGroup;
    Series1: TBarSeries;
    Series2: TFastLineSeries;
    Series4: TBarSeries;
    Series3: TFastLineSeries;
    cxSplitter1: TcxSplitter;
    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 N1Click(Sender: TObject);
    procedure N2Click(Sender: TObject);
    procedure TBCKCXClick(Sender: TObject);
    procedure C_CodeNameChange(Sender: TObject);
    procedure RadioGroup1Click(Sender: TObject);
    procedure Tv0CellDblClick(Sender: TcxCustomGridTableView;
      ACellViewInfo: TcxGridTableDataCellViewInfo; AButton: TMouseButton;
      AShift: TShiftState; var AHandled: Boolean);
  private
    procedure InitGrid();
    procedure TXCSH();
    function  YSData(Order_Main10:TClientDataSet):Boolean;
    { Private declarations }
  public
    { Public declarations }
  end;

var
  frmCKProductBCPOutHZList: TfrmCKProductBCPOutHZList;

implementation
uses
  U_DataLink,U_Fun, U_ZdyAttachGYS,U_CKProductBCPOutHZList_Month;

{$R *.dfm}

procedure TfrmCKProductBCPOutHZList.FormDestroy(Sender: TObject);
begin
  frmCKProductBCPOutHZList:=nil;
end;

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

procedure TfrmCKProductBCPOutHZList.FormCreate(Sender: TObject);
begin
  BegDate.Date:=SGetServerDateTime(ADOQueryTemp);
  EndDate.Date:=SGetServerDateTime(ADOQueryTemp);
end;

procedure TfrmCKProductBCPOutHZList.InitGrid();
begin
  try
    ADOQueryMain.DisableControls;
    with ADOQueryMain do
    begin
      Close;
      sql.Clear;
      sql.add('exec PBCK_NYRHZ :Begdate,:EndDate,:Status ');
      Parameters.ParamByName('begdate').Value:=Trim(FormatDateTime('yyyy',BegDate.DateTime))+'-01-01';
      Parameters.ParamByName('enddate').Value:=datetostr(strtodate(Trim(FormatDateTime('yyyy',EndDate.DateTime))+'-12-31')+1);
      Parameters.ParamByName('Status').Value:='0';
      Open;
    end;
    SCreateCDS20(ADOQueryMain,ClientDataSet0);
    SInitCDSData20(ADOQueryMain,ClientDataSet0);
  finally
    ADOQueryMain.DisableControls;
  end;

end;

procedure TfrmCKProductBCPOutHZList.TXCSH();
var
  i:integer;
begin
  for i:=0 to DBChart1.SeriesCount-1 do
  begin
    DBChart1.Series[i].Active:=False;
  end;
  DBChart1.Series[RadioGroup1.ItemIndex].Active:=True;
  DBChart1.Series[RadioGroup1.ItemIndex].DataSource:=nil;
  DBChart1.Series[RadioGroup1.ItemIndex].DataSource:=ADOQueryMain;
  DBChart1.Series[RadioGroup1.ItemIndex].XLabelsSource:='FDate';
  DBChart1.Series[RadioGroup1.ItemIndex].YValues.ValueSource:='XSQty';
  DBChart1.Series[RadioGroup1.ItemIndex+2].Active:=True;
  DBChart1.Series[RadioGroup1.ItemIndex+2].DataSource:=nil;
  DBChart1.Series[RadioGroup1.ItemIndex+2].DataSource:=ADOQueryMain;
  DBChart1.Series[RadioGroup1.ItemIndex+2].XLabelsSource:='FDate';
  DBChart1.Series[RadioGroup1.ItemIndex+2].YValues.ValueSource:='JGQty';
end;

procedure TfrmCKProductBCPOutHZList.TBRafreshClick(Sender: TObject);
begin
  BegDate.SetFocus;
  InitGrid();
  TXCSH();
end;

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

procedure TfrmCKProductBCPOutHZList.TBCloseClick(Sender: TObject);
begin
  WriteCxGrid('<27>ֿ<EFBFBD><D6BF><EFBFBD><EFBFBD><EFBFBD><EFBFBD>б<EFBFBD>FY2',Tv0,'<27><>Ʒ<EFBFBD>ֿ<EFBFBD>');
  Close;
end;

procedure TfrmCKProductBCPOutHZList.FormShow(Sender: TObject);
begin
  ReadCxGrid('<27>ֿ<EFBFBD><D6BF><EFBFBD><EFBFBD><EFBFBD><EFBFBD>б<EFBFBD>FY2',Tv0,'<27><>Ʒ<EFBFBD>ֿ<EFBFBD>');
  with ADOQueryTemp do
  begin
    Close;
    sql.Clear;
    sql.Add('select Cast('''' as varchar(20)) MainId,Cast('''' as varchar(80)) KHName,Cast(0 as decimal(18,4)) PRTPrice ');
    Open;
  end;
  SCreateCDS20(ADOQueryTemp,CDS_CX);
  SInitCDSData20(ADOQueryTemp,CDS_CX);
  CDS_CX.Delete;
  //InitGrid();
  
end;

procedure TfrmCKProductBCPOutHZList.TBExportClick(Sender: TObject);
begin
  if ADOQueryMain.IsEmpty then exit;
  TcxGridToExcel('<27><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>б<EFBFBD>',cxGrid1);
  //SelExportData(Tv0,ADOQueryMain,'');
end;

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

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

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

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

procedure TfrmCKProductBCPOutHZList.TBCKCXClick(Sender: TObject);
var
  FFMainId,FPrice,FHZ:String;
begin
  if CDS_Main.IsEmpty then Exit;
  
  if CDS_Main.Locate('SSel',True,[])=False then
  begin
    Application.MessageBox('û<><C3BB>ѡ<EFBFBD><D1A1><EFBFBD><EFBFBD><EFBFBD><EFBFBD>','<27><>ʾ',0);
    Exit;
  end;
  if Application.MessageBox('ȷ<><C8B7>Ҫ<EFBFBD><D2AA><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>','<27><>ʾ',32+4)<>IDYES then Exit;
  with ADOQueryDH do
  begin
    Close;
    SQL.Clear;
    SQL.Add('select SubId=Cast('''' as varchar(20)) ,SHCompany=Cast('''' as varchar(40)),HZ=Cast('''' as varchar(100)) ');
    Open;
  end;
  SCreateCDS20(ADOQueryDH,CDS_DH);
  SInitCDSData20(ADOQueryDH,CDS_DH);
  if CDS_DH.IsEmpty=False then
  begin
    CDS_DH.Delete;
  end;
  MovePanel2.Visible:=True;
  MovePanel2.Refresh;
  try
    ADOQueryCmd.Connection.BeginTrans;
    CDS_Main.DisableControls;
    with CDS_Main do
    begin
      //First;
      while CDS_Main.Locate('SSel',True,[])=True do
      begin
        if Trim(FHZ)='' then
        begin
          with CDS_DH do
          begin
            Append;
            FieldByName('SubId').Value:=Trim(CDS_Main.fieldbyname('MainId').AsString);
            FieldByName('SHCompany').Value:=Trim(CDS_Main.fieldbyname('SHCompany').AsString);
            FieldByName('HZ').Value:=Trim(CDS_Main.fieldbyname('SubId').AsString)+Trim(CDS_Main.fieldbyname('SHCompany').AsString);
            Post;
          end;
          FHZ:=Trim(CDS_Main.fieldbyname('MainId').AsString)+Trim(CDS_Main.fieldbyname('SHCompany').AsString);
        end else
        begin
          if Trim(FHZ)<>Trim(CDS_Main.fieldbyname('MainId').AsString)+Trim(CDS_Main.fieldbyname('SHCompany').AsString) then
          begin
            with CDS_DH do
            begin
              Append;
              FieldByName('SubId').Value:=Trim(CDS_Main.fieldbyname('MainId').AsString);
              FieldByName('SHCompany').Value:=Trim(CDS_Main.fieldbyname('SHCompany').AsString);
              FieldByName('HZ').Value:=Trim(CDS_Main.fieldbyname('MainId').AsString)+Trim(CDS_Main.fieldbyname('SHCompany').AsString);
              Post;
            end;
            FHZ:=Trim(CDS_Main.fieldbyname('MainId').AsString)+Trim(CDS_Main.fieldbyname('SHCompany').AsString);
          end;
        end;
        begin
          with ADOQueryCmd do
          begin
            Close;
            sql.Clear;
            sql.Add('insert into CK_PBCP_CR_Log select * from CK_PBCP_CR where BCID='''+Trim(CDS_Main.fieldbyname('BCID').AsString)+'''');
            sql.Add(' update CK_PBCP_CR_Log Set Note='''+Trim(DName)+'''+convert(varchar(20),GETDATE(),120)');
            sql.Add(' where BCID='''+Trim(CDS_Main.fieldbyname('BCID').AsString)+'''');
            sql.Add('delete CK_PBCP_CR where BCID='''+Trim(CDS_Main.fieldbyname('BCID').AsString)+'''');
            sql.Add('UPdate CK_PBCP_KC Set KCKgQty=(select KgQty from CK_PBCP_CR A where  A.CRID=CK_PBCP_KC.CRID and A.CRType=''<27><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>'') ');
            sql.Add(',KCQty=(select Qty from CK_PBCP_CR A where  A.CRID=CK_PBCP_KC.CRID and A.CRType=''<27><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>'') ');
            SQL.Add(' where CRID='+CDS_Main.fieldbyname('CRID').AsString);
            ExecSQL;
          end;

          CDS_Main.Delete;
        end;
      end;
    end;
    CDS_Main.EnableControls;
    ADOQueryCmd.Connection.CommitTrans;
    MovePanel2.Visible:=False;
    Exit;
  except
    MovePanel2.Visible:=False;
    CDS_Main.EnableControls;
    ADOQueryCmd.Connection.RollbackTrans;
    Application.MessageBox('<27><><EFBFBD><EFBFBD><EFBFBD>쳣<EFBFBD><ECB3A3>','<27><>ʾ',0);
  end;
end;

function TfrmCKProductBCPOutHZList.YSData(Order_Main10:TClientDataSet):Boolean;
var
  CRID,YFID,Price,PriceUnit,OrderUnit:String;
begin
  Result:=False;
  with ADOQueryTemp do
  begin
    Close;
    SQL.Clear;
    sql.Add('select Top 1 * from JYOrder_Sub Where Mainid='''+Trim(Order_Main10.fieldbyname('MainId').AsString)+'''');
    Open;
  end;
  PriceUnit:=Trim(ADOQueryTemp.fieldbyname('PriceUnit').AsString);
  OrderUnit:=Trim(ADOQueryTemp.fieldbyname('OrderUnit').AsString);
  with ADOQueryTemp do
  begin
    Close;
    SQL.Clear;
    sql.Add('select * from YF_Money_KC where FactoryName='''+Trim(Order_Main10.fieldbyname('KHName').AsString)+'''');
    Open;
  end;
  if ADOQueryTemp.IsEmpty=False then
  begin
    CRID:=ADOQueryTemp.fieldbyname('CRID').AsString;
  end else
  begin
    with ADOQueryCmd do
    begin
      Close;
      sql.Clear;
      sql.Add('Update YF_Money_CRID set CRID=CRID+1');
      sql.Add('select * from YF_Money_CRID ');
      Open;
    end;
    CRID:=ADOQueryCmd.fieldbyname('CRID').AsString;
    with ADOQueryCmd do
    begin
      Close;
      SQL.Clear;
      sql.Add('select * from YF_Money_KC where 1<>1');
      Open;
    end;
    with ADOQueryCmd do
    begin
      Append;
      FieldByName('CRID').Value:=StrToInt(CRID);
      FieldByName('FactoryName').Value:=Trim(Order_Main10.fieldbyname('KHName').AsString);
      FieldByName('ZdyStr1').Value:='Ӧ<><D3A6><EFBFBD><EFBFBD>';
      Post;
    end;
  end;
  with ADOQueryTemp do
  begin
    Close;
    sql.Clear;
    sql.Add('select * from YF_Money_CR where FactoryName='''+Trim(Order_Main10.fieldbyname('KHName').AsString)+'''');
    sql.Add(' and YFTypeId='''+Trim(Order_Main10.fieldbyname('Mainid').AsString)+'''');
    sql.Add(' and Price='''+Trim(Order_Main10.fieldbyname('PRTPrice').AsString)+'''');
    Open;
  end;
  if ADOQueryTemp.IsEmpty then
  begin
    if GetLSNo(ADOQueryCmd,YFID,'CS','YF_Money_CR',3,1)=False then
    begin
      Application.MessageBox('ȡ<><C8A1><EFBFBD><EFBFBD>Ӧ<EFBFBD><D3A6><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ʧ<EFBFBD><CAA7>!','<27><>ʾ',0);
      Exit;
    end;
  end else
  begin
    YFID:=Trim(ADOQueryTemp.fieldbyname('YFID').AsString);
  end;
  with ADOQueryCmd do
  begin
    Close;
    sql.Clear;
    sql.Add('update YF_Money_CR Set Qty=(select isnull(Sum(Qty),0) from CK_BanCP_CR A  ');
    sql.Add(' inner join JYOrder_Sub B on A.SubId=B.SubId where');
    SQL.Add(' A.MainId=YF_Money_CR.YFTypeId and A.CRType=''<27><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>'' and CPType=''<27><>Ʒ''  ');
    SQL.Add(' and B.PRTPrice=YF_Money_CR.Price)');
    sql.Add(',PS=(select isnull(count(*),0) from CK_BanCP_CR A  ');
    sql.Add(' inner join JYOrder_Sub B on A.SubId=B.SubId where');
    SQL.Add(' A.MainId=YF_Money_CR.YFTypeId and A.CRType=''<27><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>'' and CPType=''<27><>Ʒ''  ');
    SQL.Add(' and B.PRTPrice=YF_Money_CR.Price )');
    sql.Add(' where YFTypeId='''+Trim(Order_Main10.fieldbyname('Mainid').AsString)+'''');
    sql.Add(' and Price='+Order_Main10.fieldbyname('PRTPrice').AsString);
    ExecSQL;
  end;
  with ADOQueryCmd do
  begin
    Close;
    sql.Clear;
    sql.Add('Update YF_Money_CR Set Money=Price*Qty,BBMoney=Price*Qty*HuiLv');
    sql.Add(' where YFID='''+Trim(YFID)+'''');
    ExecSQL;
  end;
  with ADOQueryCmd do
  begin
    Close;
    sql.Clear;
    sql.Add('Update YF_Money_KC Set KCMoney=(select isnull(Sum(Money*QtyFlag),0) from YF_Money_CR A where A.CRID=YF_Money_KC.CRID)');
    sql.Add(',KCBBMoney=(select isnull(Sum(BBMoney*QtyFlag),0) from YF_Money_CR A where A.CRID=YF_Money_KC.CRID)');
    sql.Add(' where CRID='+CRID);
    ExecSQL;
  end;
  with ADOQueryTemp do
  begin
    Close;
    sql.Clear;
    sql.Add('select * from YF_Money_CR where YFId='''+Trim(YFID)+'''');
    Open;
  end;
  if ADOQueryTemp.FieldByName('Qty').Value=0 then
  begin
    with ADOQueryCmd do
    begin
      Close;
      sql.Clear;
      sql.Add('delete from YF_Money_CR where YFID='''+Trim(YFID)+'''');
      ExecSQL;
    end;
  end;
  with ADOQueryTemp do
  begin
    Close;
    sql.Clear;
    sql.Add('select * from YF_Money_CR where CRId='+CRID);
    Open;
  end;
  if ADOQueryTemp.IsEmpty then
  begin
    with ADOQueryCmd do
    begin
      Close;
      sql.Clear;
      sql.Add('delete from YF_Money_KC where CRId='+CRID);
      ExecSQL;
    end;
  end;
  Result:=True;
end;

procedure TfrmCKProductBCPOutHZList.C_CodeNameChange(Sender: TObject);
begin
  TBFind.Click;
end;

procedure TfrmCKProductBCPOutHZList.RadioGroup1Click(Sender: TObject);
begin
  TXCSH();
end;

procedure TfrmCKProductBCPOutHZList.Tv0CellDblClick(
  Sender: TcxCustomGridTableView;
  ACellViewInfo: TcxGridTableDataCellViewInfo; AButton: TMouseButton;
  AShift: TShiftState; var AHandled: Boolean);
begin
  frmCKProductBCPOutHZList_Month:=TfrmCKProductBCPOutHZList_Month.Create(Self);
  with frmCKProductBCPOutHZList_Month do
  begin
    FYear:=trim(self.ClientDataSet0.fieldbyname('FDate').asstring);
    if showmodal=1 then
    begin

    end;
    free;
  end;
end;

end.