D10szGerun/A02基础产品管理/U_CraftSync.pas

unit U_CraftSync;

interface

uses
  SysUtils, Classes, Variants, DB, ADODB;

//<2F><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ݾ<EFBFBD>Ĭͬ<C4AC><CDAC><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>а󶨸ù<F3B6A8B8><C3B9>յIJ<D5B5>Ʒ:
//  <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֶ<EFBFBD>(<28><><EFBFBD><EFBFBD><EFBFBD>ձ<EFBFBD><D5B1><EFBFBD>/<2F><><EFBFBD><EFBFBD>) + <20><>γɴ<CEB3><C9B4><EFBFBD><EFBFBD><EFBFBD>ӱ<EFBFBD> + <20>ư<EFBFBD>ͼ<EFBFBD>ӱ<EFBFBD>
//ע<><D7A2>:<3A><><EFBFBD><EFBFBD><EFBFBD>ڵ<EFBFBD><DAB5>÷<EFBFBD><C3B7><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ִ<EFBFBD><D6B4>,<2C><><EFBFBD><EFBFBD><EFBFBD>̲<EFBFBD><CCB2>Լ<EFBFBD><D4BC><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
procedure SyncCraftToProduct(const ACRID: string);

implementation

uses
  U_DataLink, U_RTFun;

function StrSQL(const AValue: Variant): string;
begin
  if VarIsNull(AValue) then
    Result := 'null'
  else
    Result := QuotedStr(Trim(VarToStr(AValue)));
end;

function NumSQL(const AValue: Variant): string;
begin
  if VarIsNull(AValue) then
    Result := 'null'
  else
    Result := FormatFloat('0.##########', VarAsType(AValue, varDouble));
end;

function WBColList(const APrefix: string): string;
var
  i: Integer;
begin
  Result := '';
  for i := 1 to 72 do
  begin
    if Result <> '' then
      Result := Result + ',';
    Result := Result + APrefix + 'WB_' + IntToStr(i);
  end;
end;

procedure SyncCraftToProduct(const ACRID: string);
var
  Qry, QryYarn: TADOQuery;
  Products: TStringList;
  i: Integer;
  NewNo: string;
begin
  if Trim(ACRID) = '' then
    Exit;
  Qry := TADOQuery.Create(nil);
  QryYarn := TADOQuery.Create(nil);
  Products := TStringList.Create;
  try
    Qry.Connection := DataLink_ClothInfo.ADOLink;
    QryYarn.Connection := DataLink_ClothInfo.ADOLink;

    //1.<2E><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֶξ<D6B6><CEBE><EFBFBD>(ֻ<><D6BB><EFBFBD>¹<EFBFBD><C2B9>չ<EFBFBD>Ͻ<EFBFBD><CFBD>,<2C><><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ʒ<EFBFBD><C6B7><EFBFBD><EFBFBD><EFBFBD>ֶ<EFBFBD>)
    with Qry do
    begin
      Close;
      SQL.Clear;
      SQL.Add('update B set B.CRID=C.CRID,B.CR_Code=C.CR_Code,B.CR_Name=C.CR_Name');
      SQL.Add(',B.C_Width=C.C_Width,B.C_GramWeight=C.C_GramWeight,B.C_Color=C.C_Color');
      SQL.Add(',B.C_YarnQty=C.C_YarnQty,B.head_count=C.head_count,B.strip_width=C.strip_width');
      SQL.Add(',B.C_MachLatitudeDensity=C.C_MachLatitudeDensity,B.row_count_top=C.row_count_top');
      SQL.Add(',B.displace=C.displace,B.reed_width=C.reed_width,B.C_ReedNo=C.C_ReedNo');
      SQL.Add(',B.ends_per_dent=C.ends_per_dent,B.selvage_yarn=C.selvage_yarn,B.yardage=C.yardage');
      SQL.Add(',B.warp_arrange=C.warp_arrange,B.weft_arrange=C.weft_arrange,B.threading_method=C.threading_method');
      SQL.Add('from BS_Cloth_Info B join BS_Craft_Info C on C.CRID=B.CRID');
      SQL.Add('where B.CRID=' + QuotedStr(Trim(ACRID)));
      ExecSQL;
    end;

    //2.<2E>ư<EFBFBD>ͼ<EFBFBD><CDBC><EFBFBD><EFBFBD>(ÿ<><C3BF>Ʒһ<C6B7><D2BB>,<2C><><EFBFBD><EFBFBD><EFBFBD>滻)
    with Qry do
    begin
      Close;
      SQL.Clear;
      SQL.Add('delete W from Bs_Cloth_Info_WB W join BS_Cloth_Info B on B.BCIID=W.BCIID');
      SQL.Add('where B.CRID=' + QuotedStr(Trim(ACRID)));
      ExecSQL;

      Close;
      SQL.Clear;
      SQL.Add('insert into Bs_Cloth_Info_WB (BCIID,' + WBColList('') + ')');
      SQL.Add('select B.BCIID,' + WBColList('C.'));
      SQL.Add('from BS_Cloth_Info B join BS_Craft_Info_WB C on C.CRID=B.CRID');
      SQL.Add('where B.CRID=' + QuotedStr(Trim(ACRID)));
      ExecSQL;
    end;

    //3.<2E><>γɴ<CEB3><C9B4><EFBFBD>Ⱦ<EFBFBD><C8BE><EFBFBD>(<28><><EFBFBD><EFBFBD>Ʒ<EFBFBD><C6B7>ɾ<EFBFBD><C9BE><EFBFBD><EFBFBD>,YRId<49><64><EFBFBD><EFBFBD>Ʒ<EFBFBD><C6B7><EFBFBD>Լ<EFBFBD><D4BC><EFBFBD><EFBFBD><EFBFBD>ˮȡ<CBAE><C8A1>)
    with Qry do
    begin
      Close;
      SQL.Clear;
      SQL.Add('select BCIID from BS_Cloth_Info where CRID=' + QuotedStr(Trim(ACRID)));
      Open;
      while not Eof do
      begin
        Products.Add(Trim(FieldByName('BCIID').AsString));
        Next;
      end;
      Close;
    end;

    if Products.Count > 0 then
    begin
      with QryYarn do
      begin
        Close;
        SQL.Clear;
        SQL.Add('select * from BS_Craft_Yarn where CRID=' + QuotedStr(Trim(ACRID)));
        SQL.Add('order by YRType,seq_no,YRId');
        Open;
      end;
      try
        for i := 0 to Products.Count - 1 do
        begin
          with Qry do
          begin
            Close;
            SQL.Clear;
            SQL.Add('delete BS_Cloth_Yarn where BCIID=' + QuotedStr(Products[i]));
            ExecSQL;
          end;

          QryYarn.First;
          while not QryYarn.Eof do
          begin
            if GetLSNo(Qry, NewNo, 'RJ', 'BS_Cloth_Yarn', 4, 1) = False then
              raise Exception.Create('ȡ<><C8A1>Ʒ<EFBFBD><C6B7><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ˮ<EFBFBD><CBAE>ʧ<EFBFBD><CAA7>');
            with Qry do
            begin
              Close;
              SQL.Clear;
              SQL.Add('insert into BS_Cloth_Yarn (YRId,BCIID,YRType,seq_no,Y_Code,Y_Name,Brand,Y_Color,Denier,Y_Ratio,Y_Qty,Filler,FillTime)');
              SQL.Add('values (' + QuotedStr(Trim(NewNo)) + ',' + QuotedStr(Products[i]));
              SQL.Add(',' + StrSQL(QryYarn.FieldByName('YRType').Value));
              SQL.Add(',' + StrSQL(QryYarn.FieldByName('seq_no').Value));
              SQL.Add(',' + StrSQL(QryYarn.FieldByName('Y_Code').Value));
              SQL.Add(',' + StrSQL(QryYarn.FieldByName('Y_Name').Value));
              SQL.Add(',' + StrSQL(QryYarn.FieldByName('Brand').Value));
              SQL.Add(',' + StrSQL(QryYarn.FieldByName('Y_Color').Value));
              SQL.Add(',' + NumSQL(QryYarn.FieldByName('Denier').Value));
              SQL.Add(',' + NumSQL(QryYarn.FieldByName('Y_Ratio').Value));
              SQL.Add(',' + NumSQL(QryYarn.FieldByName('Y_Qty').Value));
              SQL.Add(',' + QuotedStr(Trim(DName)) + ',getdate())');
              ExecSQL;
            end;
            QryYarn.Next;
          end;
        end;
      finally
        QryYarn.Close;
      end;
    end;
  finally
    Products.Free;
    QryYarn.Free;
    Qry.Free;
  end;
end;

end.