mainunit.pas

用友二次开发程序 颜色尺码二维定义工具

unit MainUnit;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, cxGraphics, cxCheckComboBox, cxControls, cxContainer, cxEdit,
  cxTextEdit, cxMaskEdit, cxDropDownEdit, cxLookupEdit, cxDBLookupEdit,
  cxDBLookupComboBox, DB, ADODB, StdCtrls, Buttons, Menus,
  cxLookAndFeelPainters, ExtCtrls, cxButtonEdit, cxButtons, dxExEdtr, dxTL,
  dxDBCtrl, dxDBGrid, dxCntner, cxPC, Grids, DBGrids;

type
  TFrmColorSize = class(TForm)
    UF_CONN: TADOConnection;
    Color: TADOQuery;
    Size: TADOQuery;
    DS_Size: TDataSource;
    DS_Color: TDataSource;
    CMD: TADOCommand;
    sInvCode: TcxButtonEdit;
    Bevel1: TBevel;
    Label1: TLabel;
    Size_PC: TcxPageControl;
    cxTabSheet1: TcxTabSheet;
    Color_PC: TcxTabSheet;
    strColor: TdxDBGrid;
    strColorColumn1: TdxDBGridColumn;
    DS_InvSize: TDataSource;
    InvColor: TADOQuery;
    InvSize: TADOQuery;
    DS_InvColor: TDataSource;
    ProdColor: TdxDBGrid;
    dxDBGridColumn1: TdxDBGridColumn;
    Temp: TADOQuery;
    ProdSize: TdxDBGrid;
    strSize: TdxDBGrid;
    strSizeColumn1: TdxDBGridColumn;
    ProdSizeColumn1: TdxDBGridColumn;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    BitBtn1: TBitBtn;
    Label2: TLabel;
    procedure sInvCodePropertiesChange(Sender: TObject);
    procedure strColorDblClick(Sender: TObject);
    procedure ProdColorDblClick(Sender: TObject);
    procedure strSizeDblClick(Sender: TObject);
    procedure ProdSizeDblClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure sInvCodePropertiesButtonClick(Sender: TObject;
      AButtonIndex: Integer);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  FrmColorSize: TFrmColorSize;

implementation

{$R *.dfm}

procedure TFrmColorSize.sInvCodePropertiesChange(Sender: TObject);
begin
    InvSize.Close ;
    InvSize.SQL.Clear;
    InvSize.SQL.Add ('SELECT DISTINCT sSize AS 尺码 FROM ColorSize WHERE cInvCode = ''' + sInvCode.Text + '''');
    InvSize.Open ;
    InvColor.Close ;
    InvColor.SQL.Clear;
    InvColor.SQL.Add('SELECT DISTINCT sColor AS 颜色 FROM ColorSize WHERE cInvCode = ''' + sInvCode.Text + '''');
    InvColor.Open ;
end;

procedure TFrmColorSize.strColorDblClick(Sender: TObject);
begin
  Label2.Caption := '';
  IF InvColor.RecordCount = 0 Then
  begin
  CMD.CommandText := 'INSERT INTO ColorSize (cInvCode, sColor, sSize) '+
                     'VALUES (''' + sInvCode.Text + ''', ''指定色'' , ''指定码''' ;
  CMD.Execute;
  End;
  Temp.Close;
  Temp.SQL.Clear;
  Temp.SQL.Add('SELECT * FROM ColorSize WHERE (cInvCode = ''' + sInvCode.Text + ''' AND sColor = ''' + Color.Fields[0].AsString + ''')');
  Temp.Open ;
  if Temp.RecordCount = 0 Then
  Begin
  CMD.CommandText := 'INSERT INTO ColorSize (cInvCode, sColor, sSize) '+
                     'SELECT DISTINCT cInvCode, ''' + Color.Fields[0].AsString + ''' AS sColor, sSize ' +
                     'FROM ColorSize ' +
                     'WHERE (cInvCode = ''' + sInvCode.Text + ''')';
  CMD.Execute;
  Label2.Caption := '颜色 '+Color.Fields[0].AsString+' 增加成功!';
  InvColor.Close ;
  InvColor.SQL.Clear;
  InvColor.SQL.Add('SELECT DISTINCT sColor AS 颜色 FROM ColorSize WHERE cInvCode = ''' + sInvCode.Text + '''');
  InvColor.Open ;
  End;

end;

procedure TFrmColorSize.ProdColorDblClick(Sender: TObject);
begin
  Label2.Caption := '';
  CMD.CommandText := 'DELETE FROM ColorSize '+
                     'WHERE (cInvCode = ''' + sInvCode.Text + ''' AND sColor = ''' + InvColor.Fields[0].AsString + ''')';
  CMD.Execute;
  Label2.Caption := '颜色 '+InvColor.Fields[0].AsString+' 删除成功!';
  InvColor.Close ;
  InvColor.SQL.Clear;
  InvColor.SQL.Add('SELECT DISTINCT sColor AS 颜色 FROM ColorSize WHERE cInvCode = ''' + sInvCode.Text + '''');
  InvColor.Open ;
end;

procedure TFrmColorSize.strSizeDblClick(Sender: TObject);
begin
  Label2.Caption := '';
  IF InvSize.RecordCount = 0 Then
  begin
  CMD.CommandText := 'INSERT INTO ColorSize (cInvCode, sColor, sSize) '+
                     'VALUES (''' + sInvCode.Text + ''', ''指定色'' , ''指定码''' ;
  CMD.Execute;
  End ;
  Temp.Close;
  Temp.SQL.Clear;
  Temp.SQL.Add('SELECT * FROM ColorSize WHERE (cInvCode = ''' + sInvCode.Text + ''' AND sSize = ''' + Size.Fields[0].AsString + ''')');
  Temp.Open ;
  if Temp.RecordCount = 0 Then
  Begin
  CMD.CommandText := 'INSERT INTO ColorSize (cInvCode, sColor, sSize) '+
                     'SELECT DISTINCT cInvCode, sColor , ''' + Size.Fields[0].AsString + ''' AS sSize ' +
                     'FROM ColorSize ' +
                     'WHERE (cInvCode = ''' + sInvCode.Text + ''')';
  CMD.Execute;
  Label2.Caption := '尺码 '+Size.Fields[0].AsString+' 增加成功!';
  InvSize.Close ;
  InvSize.SQL.Clear;
  InvSize.SQL.Add ('SELECT DISTINCT sSize AS 尺码 FROM ColorSize WHERE cInvCode = ''' + sInvCode.Text + '''');
  InvSize.Open ;
  End;

end;

procedure TFrmColorSize.ProdSizeDblClick(Sender: TObject);
begin
  Label2.Caption := '';
  CMD.CommandText := 'DELETE FROM ColorSize '+
                     'WHERE (cInvCode = ''' + sInvCode.Text + ''' AND sSize = ''' + InvSize.Fields[0].AsString + ''')';
  CMD.Execute;
  Label2.Caption := '尺码 '+InvSize.Fields[0].AsString+' 删除成功!';
  InvSize.Close ;
  InvSize.SQL.Clear;
  InvSize.SQL.Add ('SELECT DISTINCT sSize AS 尺码 FROM ColorSize WHERE cInvCode = ''' + sInvCode.Text + '''');
  InvSize.Open ;

end;

procedure TFrmColorSize.FormCreate(Sender: TObject);
begin
  // 分配条码规则
  CMD.CommandText := 'INSERT INTO RuleAssign (assignId, assignType, invId, ruleId) '+
                     'SELECT Inventory.I_id, 1 AS assignType, Inventory.cInvCode, ''12'' AS cruleId '+
                     'FROM RuleAssign RIGHT OUTER JOIN Inventory ON RuleAssign.invId = Inventory.cInvCode '+
                     'WHERE (RuleAssign.invId IS NULL) AND (Inventory.cInvCCode LIKE N''10%'') '+
                     'ORDER BY Inventory.cInvCode ';
  CMD.Execute;
  CMD.CommandText := 'INSERT INTO RuleAssign (assignId, assignType, invId, ruleId) '+
                     'SELECT Inventory.I_id, 1 AS assignType, Inventory.cInvCode, ''13'' AS cruleId '+
                     'FROM RuleAssign RIGHT OUTER JOIN Inventory ON RuleAssign.invId = Inventory.cInvCode '+
                     'WHERE (RuleAssign.invId IS NULL) AND (Inventory.cInvCCode =''1086'') '+
                     'ORDER BY Inventory.cInvCode ';
  CMD.Execute;
  // 无色码资料建档
  CMD.CommandText := 'INSERT INTO ColorSize (cInvCode, sColor, sSize) '+
                     'SELECT Inventory.cInvCode, ''Z_指定色'' AS sColor, ''指定码'' AS sSize '+
                     'FROM Inventory LEFT OUTER JOIN ColorSize ON Inventory.cInvCode = ColorSize.cInvCode '+
                     'WHERE (ColorSize.cInvCode IS NULL) AND (Inventory.cInvCCode <>''1086'') AND (Inventory.cInvCCode LIKE N''10%'') ';
  CMD.Execute;
  // 非十位条码变更为十位
  CMD.CommandText := 'UPDATE Inventory SET cBarCode = CASE WHEN LEN(cBarCode) = 11 THEN LEFT(cInvCode, 10) ELSE '+
                     '(CASE WHEN LEN(cBarCode) = 9 THEN cInvCode + ''0'' ELSE  '+
                     '(CASE WHEN LEN(cBarCode) = 8 THEN cInvCode + ''00'' ELSE cInvCode END) END) END '+
                     'WHERE (cInvCCode LIKE N''10%'') AND (LEN(cBarCode) <> 10) AND (Inventory.cInvCCode <>''1086'') ';
  CMD.Execute;
  // 更新尺码颜色资料至条码库
  CMD.CommandText := 'INSERT INTO InventoryBarCodeSet '+
                     '(cInvCode, cBarCode, cRuleId, cAssignId, cInvName, cInvCCode, cInvCName, cFree1, cFree2) '+
                     'SELECT DISTINCT Inventory.cInvCode,  '+
                     'Inventory.cBarCode + UserDefine.cBarCode + UserDefine_1.cBarCode AS nBarCode,  '+
                     'RuleAssign.ruleId, RuleAssign.assignId, Inventory.cInvName, Inventory.cInvCCode,  '+
                     'InventoryClass.cInvCName, ColorSize.sColor, ColorSize.sSize '+
                     'FROM Inventory INNER JOIN '+
                     'ColorSize ON Inventory.cInvCode = ColorSize.cInvCode INNER JOIN '+
                     'UserDefine ON ColorSize.sColor = UserDefine.cValue INNER JOIN '+
                     'UserDefine UserDefine_1 ON ColorSize.sSize = UserDefine_1.cValue INNER JOIN '+
                     'RuleAssign ON Inventory.cInvCode = RuleAssign.invId INNER JOIN '+
                     'InventoryClass ON  '+
                     'Inventory.cInvCCode = InventoryClass.cInvCCode LEFT OUTER JOIN '+
                     'InventoryBarCodeSet ON Inventory.cInvCode = InventoryBarCodeSet.cInvCode AND  '+
                     'ColorSize.sColor = InventoryBarCodeSet.cFree1 AND  '+
                     'ColorSize.sSize = InventoryBarCodeSet.cFree2 '+
                     'WHERE (NOT (Inventory.cBarCode + UserDefine.cBarCode + UserDefine_1.cBarCode IS NULL)) '+
                     'AND (Inventory.cInvCCode LIKE N''10%'') AND (InventoryBarCodeSet.cBarCode IS NULL) AND (Inventory.cInvCCode <>''1086'') ';
  CMD.Execute;

end;

procedure TFrmColorSize.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  // 分配条码规则
  CMD.CommandText := 'INSERT INTO RuleAssign (assignId, assignType, invId, ruleId) '+
                     'SELECT Inventory.I_id, 1 AS assignType, Inventory.cInvCode, ''12'' AS cruleId '+
                     'FROM RuleAssign RIGHT OUTER JOIN Inventory ON RuleAssign.invId = Inventory.cInvCode '+
                     'WHERE (RuleAssign.invId IS NULL) AND (Inventory.cInvCCode LIKE N''10%'') AND (Inventory.cInvCCode <>''1086'')  '+
                     'ORDER BY Inventory.cInvCode ';
  CMD.Execute;
  CMD.CommandText := 'INSERT INTO RuleAssign (assignId, assignType, invId, ruleId) '+
                     'SELECT Inventory.I_id, 1 AS assignType, Inventory.cInvCode, ''13'' AS cruleId '+
                     'FROM RuleAssign RIGHT OUTER JOIN Inventory ON RuleAssign.invId = Inventory.cInvCode '+
                     'WHERE (RuleAssign.invId IS NULL) AND (Inventory.cInvCCode =''1086'') '+
                     'ORDER BY Inventory.cInvCode ';
  CMD.Execute;
  // 无色码资料建档
  CMD.CommandText := 'INSERT INTO ColorSize (cInvCode, sColor, sSize) '+
                     'SELECT Inventory.cInvCode, ''Z_指定色'' AS sColor, ''指定码'' AS sSize '+
                     'FROM Inventory LEFT OUTER JOIN ColorSize ON Inventory.cInvCode = ColorSize.cInvCode '+
                     'WHERE (ColorSize.cInvCode IS NULL) AND (Inventory.cInvCCode <>''1086'') AND (Inventory.cInvCCode LIKE N''10%'') ';
  CMD.Execute;
  // 非十位条码变更为十位
  CMD.CommandText := 'UPDATE Inventory SET cBarCode = CASE WHEN LEN(cBarCode) = 11 THEN LEFT(cInvCode, 10) ELSE '+
                     '(CASE WHEN LEN(cBarCode) = 9 THEN cInvCode + ''0'' ELSE  '+
                     '(CASE WHEN LEN(cBarCode) = 8 THEN cInvCode + ''00'' ELSE cInvCode END) END) END '+
                     'WHERE (cInvCCode LIKE N''10%'') AND (LEN(cBarCode) <> 10) AND (Inventory.cInvCCode <>''1086'') ';
  CMD.Execute;
  // 更新尺码颜色资料至条码库
  CMD.CommandText := 'INSERT INTO InventoryBarCodeSet '+
                     '(cInvCode, cBarCode, cRuleId, cAssignId, cInvName, cInvCCode, cInvCName, cFree1, cFree2) '+
                     'SELECT DISTINCT Inventory.cInvCode,  '+
                     'Inventory.cBarCode + UserDefine.cBarCode + UserDefine_1.cBarCode AS nBarCode,  '+
                     'RuleAssign.ruleId, RuleAssign.assignId, Inventory.cInvName, Inventory.cInvCCode,  '+
                     'InventoryClass.cInvCName, ColorSize.sColor, ColorSize.sSize '+
                     'FROM Inventory INNER JOIN '+
                     'ColorSize ON Inventory.cInvCode = ColorSize.cInvCode INNER JOIN '+
                     'UserDefine ON ColorSize.sColor = UserDefine.cValue INNER JOIN '+
                     'UserDefine UserDefine_1 ON ColorSize.sSize = UserDefine_1.cValue INNER JOIN '+
                     'RuleAssign ON Inventory.cInvCode = RuleAssign.invId INNER JOIN '+
                     'InventoryClass ON  '+
                     'Inventory.cInvCCode = InventoryClass.cInvCCode LEFT OUTER JOIN '+
                     'InventoryBarCodeSet ON Inventory.cInvCode = InventoryBarCodeSet.cInvCode AND  '+
                     'ColorSize.sColor = InventoryBarCodeSet.cFree1 AND  '+
                     'ColorSize.sSize = InventoryBarCodeSet.cFree2 '+
                     'WHERE (NOT (Inventory.cBarCode + UserDefine.cBarCode + UserDefine_1.cBarCode IS NULL)) '+
                     'AND (Inventory.cInvCCode LIKE N''10%'') AND (InventoryBarCodeSet.cBarCode IS NULL) AND (Inventory.cInvCCode <>''1086'') ';
  CMD.Execute;
end;

procedure TFrmColorSize.sInvCodePropertiesButtonClick(Sender: TObject;
  AButtonIndex: Integer);
begin
    if (sInvCode.Text <> '') and (length(sInvCode.Text) in [8,10]) then
    begin
        if MessageDlg('提示: '+#13+'建立该商品的指定色码档案吗? ',mtInformation,[mbYes,mbNo],0) = mrYes Then
        begin
            try
                CMD.CommandText := 'INSERT INTO ColorSize (cInvCode, sColor, sSize) '+
                     'VALUES (''' + sInvCode.Text + ''', ''Z_指定色'' , ''指定码'')' ;
                CMD.Execute;
                showmessage('建档成功!');
            except
                showmessage('建档失败!');
            end;
        end;
    end;
    InvSize.Close ;
    InvSize.SQL.Clear;
    InvSize.SQL.Add ('SELECT DISTINCT sSize AS 尺码 FROM ColorSize WHERE cInvCode = ''' + sInvCode.Text + '''');
    InvSize.Open ;
    InvColor.Close ;
    InvColor.SQL.Clear;
    InvColor.SQL.Add('SELECT DISTINCT sColor AS 颜色 FROM ColorSize WHERE cInvCode = ''' + sInvCode.Text + '''');
    InvColor.Open ;
end;

end.