//**********************************************************// // Copyright (c) 2010-2025 Martin RODOT // // // // The source code of Gestinux is distributed // // under the GNU General Public License (GPL) // // // // This software is distributed in the hope that it will be // // useful, but without any warranty; without even the // // implied warranty of merchantability or fitness for // // a particular purpose. // //**********************************************************// unit UnitProduct; {$mode objfpc}{$H+} interface uses Classes, SysUtils, DB, Fileutil, Forms, Controls, Graphics, Dialogs, ComCtrls, ExtCtrls, Grids, DBGrids, GCheckBox, GComboBox, GButton, GForm, GPagecontrol, GMemo, GLabel, GGrid, GPanel, GCommon, GEdit, GEditButton, GDbgrid, GDbconnection, GImage, GSpinEdit, GDateEdit, UnitCoeffStock; type { TFormProduct } TFormProduct = class(TGForm) BtnSalesDocsRefresh: TGButton; BtnEditStock: TGButton; BtnOk: TGButton; BtnCancel: TGButton; dsSalesDocs: TDataSource; DataSourceStocks: TDataSource; EditPeriod: TGPeriodEdit; EditCode: TGEdit; EditCoeffStockSales: TGEdit; EditGroup: TGEditButton; EditInternalNotes: TGMemo; EditName: TGEdit; EditPicture: TGimage; EditPublicDescription: TGMemo; EditSalesUnit: TGEditButton; EditStockManaged: TGCheckBox; EditStockUnit: TGEditButton; BtnCoeffStock: TGButton; GridSalesDocs: TGDbGrid; qrySalesDocs: TGQuery; pnlDates: TGPanel; pnlEditStock: TGPanel; GridStocks: TGDbGrid; qrySalesDocsCustomerName: TStringField; qrySalesDocsDeliveryCity: TStringField; qrySalesDocsdescription2: TStringField; qrySalesDocsDocCode: TStringField; qrySalesDocsDocDate: TDateField; qrySalesDocsDocType: TSmallintField; qrySalesDocsId: TLongintField; qrySalesDocsquantity: TLongintField; qrySalesDocsunitname: TStringField; qrySalesDocsunitprice: TLongintField; qrySalesDocsvalidated: TSmallintField; qrySalesDocsWarehouseName: TStringField; QueryStocks: TGQuery; PanelBtnCoeffStock: TGPanel; PanelCoeffStock: TGPanel; GridSalesCategories: TGGrid; GridTariffs: TGGrid; LabelCoeffStockSales: TGLabel; PageControl: TGPageControl; PanelStock: TPanel; PanelBottom: TGPanel; PanelDescription: TGPanel; PanelMemos: TGPanel; PanelSales: TGPanel; PanelStockManagement: TPanel; PanelTop: TGEntryPanel; QueryStocksdate_checked: TDateTimeField; QueryStocksFooter: TLargeintField; QueryStocksid: TLongintField; QueryStocksqty_checked: TLongintField; QueryStocksqty_delivered: TLongintField; QueryStocksqty_ordered: TLongintField; QueryStocksqty_quoted: TLongintField; QueryStocksqty_remaining: TLargeintField; QueryStocksupdatedate: TDateTimeField; QueryStocksWarehouseId: TLongintField; QueryStocksWarehouseName: TStringField; Splitter1: TSplitter; Splitter4: TSplitter; Splitter5: TSplitter; TabSheetSalesDocs: TTabSheet; TabSheetStock: TTabSheet; TabSheetDescription: TTabSheet; TabSheetSales: TTabSheet; procedure BtnSalesDocsRefreshClick(Sender: TObject); procedure BtnCoeffStockClick(Sender: TObject); procedure BtnEditStockClick(Sender: TObject); procedure BtnOkClick(Sender: TObject); procedure BtnSaveClick(Sender: TObject); procedure BtnSelectGroupClick(Sender: TObject); procedure DataSourceStocksDataChange(Sender: TObject; Field: TField); procedure EditChange(Sender: TObject); procedure EditCoeffStockSalesChange(Sender: TObject); procedure EditCoeffStockSalesDbToDisplay(const aQuery: TGQuery; const aFieldName: string); function EditCoeffStockSalesEntryToSql(const aText: string): string; procedure EditGroupChange(Sender: TObject); procedure EditSalesUnitButtonClick(Sender: TObject); procedure EditSalesUnitChange(Sender: TObject); procedure EnableControls(Sender: TObject); procedure EditStockUnitButtonClick(Sender: TObject); procedure EditStockUnitChange(Sender: TObject); procedure EditUnitChange(Sender: TObject); procedure FormClose(Sender: TObject; var CloseAction: TCloseAction); procedure FormCloseQuery(Sender: TObject; var Canclose: boolean); procedure FormCreate(Sender: TObject); procedure FormDestroy(Sender: TObject); procedure FormDropFiles(Sender: TObject; const FileNames: array of string); procedure FormShow(Sender: TObject); procedure GridSalesCategoriesEditButtonClick(Sender: TObject); procedure GridGetCellSelectable(Sender: TObject; ACol, ARow: integer; var Value: boolean); procedure GridSalesCategoriesGetCellEditable(Sender: TObject; ACol, ARow: integer; var Value: boolean); procedure GridSalesCategoriesPrepareCanvas(Sender: TObject; aCol, aRow: integer; aState: TGridDrawState); procedure GridStocksDblClick(Sender: TObject); procedure GridTariffsEditingDone(Sender: TObject); procedure GridTariffsValidateEntry(Sender: TObject; aCol, aRow: integer; const OldValue: string; var NewValue: string); procedure qrySalesDocsdoctypeGetText(Sender: TField; var aText: string; DisplayText: boolean); procedure qrySalesDocsunitpriceGetText(Sender: TField; var aText: string; DisplayText: boolean); procedure QueryStocksWarehouseNameGetText(Sender: TField; var aText: string; DisplayText: boolean); private procedure DisplaySalesCategories(const aProductId: TId); procedure DisplaySalesDocs(const aProductId: TId); procedure DisplayStock(const aProductId: TId); procedure DisplayTariffs(const aProductId: TId); procedure DisplayTaxes(const aRow: integer; const aProductId: TId); procedure Display(const aProductId: TId; const aWithLock: TWithLock); procedure SaveAccounts(const aProductId: TId); procedure SaveSalesCategoryTaxes(const aProductId: TId; aRow: integer); procedure SaveTariffs(const aProductId: TId); procedure SelectSalesCategoryAccount(const aRow: integer); procedure SelectSalesCategoryTax(const aTaxGroup: integer; const aRow: integer); procedure SetModified(const State: boolean); public ProductId: integer; Modified: boolean; ModifiedTariff: boolean; CoeffStock: TCoeffStock; class procedure Launch(const aProductId: integer); procedure Translate; override; end; var FormProduct: TFormProduct; implementation {$R *.lfm} uses GTranslationsBase, GDbconnectionBase, UnitDataModule, UnitProductGroups, UnitUnits, UnitAccounts, UnitAccountsBase, UnitTaxes, UnitProductStock, UnitSalesDocsBase; const Col_tariff_name = 0; Col_tariff_price = 1; Col_tariff_tariffId = Col_tariff_name; Col_tariff_producttariffId = Col_tariff_price; Col_SalesCategory_Name = 0; Col_SalesCategory_Account = 1; GRIDSTOCK_DETAIL = 0; GRIDSTOCK_FOOTER = 1; type // for each taxgroup column TTaxGroups = record Applicable: boolean; Required: boolean; Id: TId; DefaultSalesTaxId: TId; ProductTaxId: TId; end; // for each salescategory row TSalesCategoryIds = record SalesCategoryId: TId; ProductSalesCategoryId: TId; DefaultSalesAccountId: TId; SalesAccountId: TId; TaxSelected: boolean; // for each tax column TaxGroups: array of TTaxGroups; end; var SalesCategoryIds: array of TSalesCategoryIds; //---------------------------------------------------------------------------- // Create the form and show //---------------------------------------------------------------------------- { TFormProduct } class procedure TFormProduct.Launch(const aProductId: integer); begin if FormProduct = nil then Application.CreateForm(TFormProduct, FormProduct); FormProduct.Display(aProductId, WITH_LOCK); FormProduct.ActiveControl := FormProduct.EditName; FormProduct.ShowModal; end; procedure TFormProduct.SaveTariffs(const aProductId: TId); var ProductTariffId: TId; TariffId: TId; PriceStr: string; R: integer; begin for R := 1 to GridTariffs.RowCount-1 do begin TariffId := TObjId(GridTariffs.Objects[Col_tariff_name,R]).Id; ProductTariffId := TObjId(GridTariffs.Objects[Col_tariff_price,R]).Id; PriceStr := GridTariffs.Cells[Col_tariff_price, R]; if ProductTariffId = 0 then begin // no record read if PriceStr <> '' then begin // insert new price ProductTariffId := Datamodule1.Db.InsertSql('ProductTariffs', //- 'ProductId,TariffId,Price', IntToStr(aProductId) + ',' + IntToStr(TariffId) + ',' + IntToStr(DecodeAmount(PriceStr)), 'Id'); TObjId(GridTariffs.Objects[Col_tariff_price,R]).Id := ProductTariffId; end; end else begin // Tarif record read if PriceStr = '' then begin // delete Datamodule1.Db.ExecSql('DELETE FROM ProductTariffs ' + 'WHERE ProductId=' + IntToStr(aProductId) + ' ' + ' AND TariffId=' + IntToStr(TariffId)); TObjId(GridTariffs.Objects[Col_tariff_price,R]).Id := 0; end else begin // update PriceStr := IntToStr(DecodeAmount(PriceStr)); Datamodule1.Db.ExecSql( //- 'UPDATE ProductTariffs SET ' + //- 'Price = ' + PriceStr + ' ' + //- 'WHERE ProductId=' + IntToStr(aProductId) + ' ' + //- ' AND TariffId=' + IntToStr(TariffId) + ' ' + //- ' AND Price <> ' + PriceStr); end; end; end; end; procedure TFormProduct.SaveSalesCategoryTaxes(const aProductId: TId; aRow: integer); var TG: integer; begin for TG := low(TaxGroupsLibs) to high(TaxGroupsLibs) do begin // Check if SalesCategoryIds[aRow].TaxGroups[TG - 1].Required and (SalesCategoryIds[aRow].TaxGroups[TG - 1].DefaultSalesTaxId = 0) and (SalesCategoryIds[aRow].TaxGroups[TG - 1].ProductTaxId = 0) then AbortMessage('TaxIsRequired', [GridSalesCategories.Cells[Col_SalesCategory_Name, aRow], GridSalesCategories.Cells[TG + 1, 0]], ''); if SalesCategoryIds[aRow].TaxGroups[TG - 1].Id = 0 then begin // no record read if (SalesCategoryIds[aRow].TaxGroups[TG - 1].ProductTaxId <> 0) and (SalesCategoryIds[aRow].TaxGroups[TG - 1].ProductTaxId <> SalesCategoryIds[aRow].TaxGroups[TG - 1].DefaultSalesTaxId) then begin // insert new ProductAccount SalesCategoryIds[aRow].TaxGroups[TG - 1].Id := Datamodule1.Db.InsertSql('ProductTaxes', 'ProductId,SalesCategoryId,GroupId,TaxId', IntToStr(aProductId) + ',' + IntToStr(SalesCategoryIds[aRow].SalesCategoryId) + ',' + IntToStr(TG) + ',' + IntToStr( SalesCategoryIds[aRow].TaxGroups[TG - 1].ProductTaxId), 'Id'); end; end else begin // existing record if (SalesCategoryIds[aRow].TaxGroups[TG - 1].ProductTaxId <> 0) and (SalesCategoryIds[aRow].TaxGroups[TG - 1].ProductTaxId <> SalesCategoryIds[aRow].TaxGroups[TG - 1].DefaultSalesTaxId) then begin // update Datamodule1.Db.ExecSql('UPDATE ProductTaxes SET ' + ' TaxId = ' + IntToStr(SalesCategoryIds[aRow].TaxGroups[TG - 1].ProductTaxId) + ' WHERE Id=' + IntToStr(SalesCategoryIds[aRow].TaxGroups[TG - 1].Id)); end else begin // delete Datamodule1.Db.ExecSql('DELETE FROM ProductTaxes ' + 'WHERE Id=' + IntToStr(SalesCategoryIds[aRow].TaxGroups[TG - 1].Id)); SalesCategoryIds[aRow].TaxGroups[TG - 1].Id := 0; end; end; end; end; procedure TFormProduct.SaveAccounts(const aProductId: TId); var R: integer; begin for R := 1 to GridSalesCategories.RowCount - 1 do begin if SalesCategoryIds[R].ProductSalesCategoryId = 0 then begin if SalesCategoryIds[R].SalesAccountId <> 0 then begin // insert new ProductAccount SalesCategoryIds[R].ProductSalesCategoryId := Datamodule1.Db.InsertSql('ProductSalesAccounts', 'ProductId,SalesCategoryId,SalesAccountId', IntToStr(aProductId) + ',' + IntToStr(SalesCategoryIds[R].SalesCategoryId) + ',' + IntToStr(SalesCategoryIds[R].SalesAccountId), 'Id'); end; end else begin // existing record if SalesCategoryIds[R].SalesAccountId = 0 then begin // delete Datamodule1.Db.ExecSql('DELETE FROM ProductSalesAccounts ' + 'WHERE Id=' + IntToStr(SalesCategoryIds[R].ProductSalesCategoryId)); SalesCategoryIds[R].ProductSalesCategoryId := 0; end else begin // update Datamodule1.Db.ExecSql('UPDATE ProductSalesAccounts SET ' + ' SalesAccountId = ' + IntToStr(SalesCategoryIds[R].SalesAccountId) + ' WHERE Id=' + IntToStr(SalesCategoryIds[R].ProductSalesCategoryId)); end; end; SaveSalesCategoryTaxes(aProductId, R); end; end; //---------------------------------------------------------------------------- // Save Product //---------------------------------------------------------------------------- procedure TFormProduct.BtnSaveClick(Sender: TObject); begin // Check entries if Trim(EditName.Text) = EmptyString then AbortMessage('ProductNameRequired', []); if EditGroup.Id = 0 then AbortMessage('ProductGroupRequired', []); if EditStockManaged.Checked then begin if EditStockUnit.Id = 0 then AbortMessage('StockUnitRequired', []); if DecodeDecimal(EditCoeffStockSales.Text, 3) <= 0 then AbortMessage('CoeffStockSalesInvalid', []); end; try PanelTop.SaveToTable; SaveTariffs(PanelTop.Id); SaveAccounts(PanelTop.Id); Datamodule1.Db.Commit; SetModified(False); ProductId := PanelTop.Id; except Datamodule1.Db.Rollback; raise; end; end; procedure TFormProduct.BtnOkClick(Sender: TObject); begin ModalResult := mrNone; if Modified then BtnSaveClick(Self); ModalResult := mrOk; end; procedure TFormProduct.BtnCoeffStockClick(Sender: TObject); begin CoeffStock.InvertEditCoeffStockSales; CoeffStock.UpdateLabelCoeffStockSales; end; //------------------------------------------------------------------------------------------------- // Refresh sales doc //------------------------------------------------------------------------------------------------- procedure TFormProduct.BtnSalesDocsRefreshClick(Sender: TObject); begin DisplaySalesDocs(ProductId); end; procedure TFormProduct.BtnEditStockClick(Sender: TObject); var Id: TId; begin Id := QueryStocks.FieldByName('Id').AsInteger; TFormProductStock.Launch(QueryStocks.FieldByName('Id').AsInteger, ProductId, QueryStocks.FieldByName('WarehouseId').AsInteger); DisplayStock(ProductId); QueryStocks.Locate('Id', Id, []); end; //------------------------------------------------------------------------------------------------- // Select a product group //------------------------------------------------------------------------------------------------- procedure TFormProduct.BtnSelectGroupClick(Sender: TObject); begin TFormProductGroups.Select(EditGroup); end; procedure TFormProduct.DataSourceStocksDataChange(Sender: TObject; Field: TField); begin BtnEditStock.Enabled := QueryStocksFooter.AsInteger = GRIDSTOCK_DETAIL; end; procedure TFormProduct.EditChange(Sender: TObject); begin SetModified(True); end; procedure TFormProduct.EditCoeffStockSalesChange(Sender: TObject); begin CoeffStock.ChangeCoeff; EditChange(Sender); end; procedure TFormProduct.EditCoeffStockSalesDbToDisplay(const aQuery: TGQuery; const aFieldName: string); begin CoeffStock.DbToDisplay(aQuery, aFieldName); end; function TFormProduct.EditCoeffStockSalesEntryToSql(const aText: string): string; begin Result := CoeffStock.EntryToSql(aText); end; procedure TFormProduct.EditGroupChange(Sender: TObject); var Query: TGQuery; begin if (ProductId = 0) and (EditGroup.Id <> 0) then begin // Select default values Query := TGQuery.Create(Datamodule1.Db, 'SELECT StockManaged, SalesUnitId, StockUnitId, CoeffStockSales ' + //- 'FROM ProductGroups ' + //- 'WHERE Id = ' + IdSql(EditGroup.Id), WITHOUT_LOCK); if not Query.EOF then begin EditStockManaged.Checked := Query.FieldByName('StockManaged').AsInteger = 1; EditCoeffStockSales.Text := FormatDecimal(Query.FieldByName('CoeffStockSales').AsInteger, 3); EditSalesUnit.Id := Query.FieldByName('SalesUnitId').AsInteger; EditStockUnit.Id := Query.FieldByName('StockUnitId').AsInteger; CoeffStock.Init; CoeffStock.UpdateLabelCoeffStockSales; EnableControls(nil); end; end; EditChange(Sender); end; procedure TFormProduct.EditSalesUnitButtonClick(Sender: TObject); begin TFormUnits.Select(EditSalesUnit); end; procedure TFormProduct.EditSalesUnitChange(Sender: TObject); var Plural: string; begin Plural := DataModule1.Db.SelectSql( 'SELECT U.Plural ' + //- 'FROM Units U ' + //- 'WHERE U.Id = ' + IdSql(EditSalesUnit.Id)); CoeffStock.MajUnitSales(EditSalesUnit.Text, Plural); EditUnitChange(Sender); end; procedure TFormProduct.EnableControls(Sender: TObject); begin PanelStockManagement.Enabled := EditStockManaged.Checked; EditStockUnit.Enabled := EditStockManaged.Checked and (QueryStocks.RecordCount <= 1); GridStocks.Visible := EditStockManaged.Checked; pnlEditStock.Visible := EditStockManaged.Checked; if EditStockManaged.Checked then begin if EditStockUnit.Id = 0 then EditStockUnit.Id := EditSalesUnit.Id; if EditCoeffStockSales.Text = '' then EditCoeffStockSales.Text := FormatDecimal(1, 3); end; EditChange(Sender); end; procedure TFormProduct.EditStockUnitButtonClick(Sender: TObject); begin TFormUnits.Select(EditStockUnit); end; procedure TFormProduct.EditStockUnitChange(Sender: TObject); var Plural: string; begin Plural := DataModule1.Db.SelectSql( 'SELECT U.Plural ' + //- 'FROM Units U ' + //- 'WHERE U.Id = ' + IdSql(EditStockUnit.Id)); CoeffStock.MajUnitStock(EditStockUnit.Text, Plural); EditUnitChange(Sender); end; procedure TFormProduct.EditUnitChange(Sender: TObject); begin CoeffStock.UpdateLabelCoeffStockSales; EditChange(Sender); end; //-------------------------------------------------------------------------- // Save height of variable panels //-------------------------------------------------------------------------- procedure TFormProduct.FormClose(Sender: TObject; var CloseAction: TCloseAction); begin // workaround for bug 34930 GridSalesCategories.Options := GridSalesCategories.Options - [goAlwaysShowEditor]; GridTariffs.Options := GridTariffs.Options - [goAlwaysShowEditor]; IniFileParam.WriteInteger(Name, 'EditPublicDescription.Height', EditPublicDescription.Height); IniFileParam.WriteInteger(Name, 'PanelMemos.Height', PanelMemos.Height); end; //-------------------------------------------------------------------------- // Ask for confirmation before closing form //-------------------------------------------------------------------------- procedure TFormProduct.FormCloseQuery(Sender: TObject; var Canclose: boolean); begin CanClose := False; CancelEntriesMessage(Modified); Datamodule1.Db.Rollback; CanClose := True; end; //-------------------------------------------------------------------------- // Dynamic creation of one column for each tax group //-------------------------------------------------------------------------- procedure TFormProduct.FormCreate(Sender: TObject); var G: integer; begin CoeffStock := TCoeffStock.Create(ClassName, EditCoeffStockSales, EditSalesUnit, EditStockUnit, LabelCoeffStockSales); // Create columns for each Tax Group Id for G := low(TaxGroupsLibs) to high(TaxGroupsLibs) do begin while GridSalesCategories.Columns.Count < G + 2 do GridSalesCategories.AddColumn('TaxGroup_' + IntToStr(G)); GridSalesCategories.Columns[G + 1].ReadOnly := False; GridSalesCategories.Columns[G + 1].ButtonStyle := cbsButton; GridSalesCategories.Columns[G + 1].Title.Caption := TFormTaxes.LibTaxGroup(G); end; GridSalesCategories.AutoSizeColumns; EditPublicDescription.Height := IniFileParam.ReadInteger(Name, 'EditPublicDescription.Height', EditPublicDescription.Height); PanelMemos.Height := IniFileParam.ReadInteger(Name, 'PanelMemos.Height', PanelMemos.Height); end; procedure TFormProduct.FormDestroy(Sender: TObject); begin CoeffStock.Free; end; //------------------------------------------------------------------------ // Load an image file dropped on the form //------------------------------------------------------------------------ procedure TFormProduct.FormDropFiles(Sender: TObject; const FileNames: array of string); begin EditPicture.DropFiles(FileNames); end; //------------------------------------------------------------------------ // Show the form //------------------------------------------------------------------------ procedure TFormProduct.FormShow(Sender: TObject); begin // workaround for bug 34930 GridSalesCategories.Options := GridSalesCategories.Options + [goAlwaysShowEditor]; GridTariffs.Options := GridTariffs.Options + [goAlwaysShowEditor]; EditName.SetFocus; end; //------------------------------------------------------------------------ // Select a tax for a row and a taxgroup //------------------------------------------------------------------------ procedure TFormProduct.SelectSalesCategoryTax(const aTaxGroup: integer; const aRow: integer); var Id: TId; Code, TaxName: string; Rate: integer; AccountId: TId; begin Id := SalesCategoryIds[aRow].TaxGroups[aTaxGroup - 1].ProductTaxId; if Id = 0 then Id := SalesCategoryIds[aRow].TaxGroups[aTaxGroup - 1].DefaultSalesTaxId; if TFormTaxes.Select(Id, Code, TaxName, Rate, AccountId, 'GroupId = ' + IntToStr(aTaxGroup), not SalesCategoryIds[aRow].TaxGroups[aTaxGroup - 1].Required) then begin // memo Id SalesCategoryIds[aRow].TaxGroups[aTaxGroup - 1].ProductTaxId := Id; SalesCategoryIds[aRow].TaxSelected := Id <> 0; // display code GridSalesCategories.Cells[aTaxGroup + 1, GridSalesCategories.Row] := Code; EditChange(nil); end; end; //------------------------------------------------------------------------ // Select an account //------------------------------------------------------------------------ procedure TFormProduct.SelectSalesCategoryAccount(const aRow: integer); var Id: TId; begin Id := SalesCategoryIds[aRow].SalesAccountId; if Id = 0 then Id := SalesCategoryIds[aRow].DefaultSalesAccountId; if TFormAccounts.Select(Id) then begin if FormAccounts.Query.FieldByName('Header').AsInteger <> Header_regular then AbortMessage('CantSelectHeaderAccount', [FormAccounts.Query.FieldByName('Account').AsString]); // if same as default, reset to default = 0 if Id = SalesCategoryIds[aRow].DefaultSalesAccountId then SalesCategoryIds[aRow].SalesAccountId := 0 else SalesCategoryIds[aRow].SalesAccountId := Id; // if default, find account to display if SalesCategoryIds[aRow].SalesAccountId = 0 then FormAccounts.Query.Locate('Id', SalesCategoryIds[aRow].DefaultSalesAccountId, []); // display code and lib GridSalesCategories.Cells[Col_SalesCategory_Account, GridSalesCategories.Row] := FormAccounts.Query.FieldByName('Account').AsString + ' ' + FormAccounts.Query.FieldByName('Name').AsString; EditChange(nil); end; end; //------------------------------------------------------------------------ // Clicked button in SalesCategories Grid //------------------------------------------------------------------------ procedure TFormProduct.GridSalesCategoriesEditButtonClick(Sender: TObject); begin Assert(GridSalesCategories.Col > Col_SalesCategory_Name); if GridSalesCategories.Col = Col_SalesCategory_Account then SelectSalesCategoryAccount(GridSalesCategories.Row) else SelectSalesCategoryTax(GridSalesCategories.Col - 1, GridSalesCategories.Row); SetModified(True); end; //------------------------------------------------------------------------ // Can't select 1st column //------------------------------------------------------------------------ procedure TFormProduct.GridGetCellSelectable(Sender: TObject; ACol, ARow: integer; var Value: boolean); begin Value := aCol > 0; end; procedure TFormProduct.GridSalesCategoriesGetCellEditable(Sender: TObject; ACol, ARow: integer; var Value: boolean); begin Value := (Acol = Col_SalesCategory_Account) or ((Acol > Col_SalesCategory_Account) and SalesCategoryIds[aRow].TaxGroups[aCol - 2].Applicable); end; //------------------------------------------------------------------------ // Bold when sales account {or tax} are not default // Unable to do it for taxes ???? //------------------------------------------------------------------------ procedure TFormProduct.GridSalesCategoriesPrepareCanvas(Sender: TObject; aCol, aRow: integer; aState: TGridDrawState); begin if ((aCol = Col_SalesCategory_Account) and (aRow < Length(SalesCategoryIds)) and (SalesCategoryIds[aRow].SalesAccountId > 0)) then GridSalesCategories.Canvas.Font.Style := [fsBold] else GridSalesCategories.Canvas.Font.Style := []; end; //------------------------------------------------------------------------ // Dblclick in GridStocks to edit stock //------------------------------------------------------------------------ procedure TFormProduct.GridStocksDblClick(Sender: TObject); begin if BtnEditStock.Enabled then BtnEditStockClick(Sender); end; procedure TFormProduct.GridTariffsEditingDone(Sender: TObject); begin SetModified(True); end; //------------------------------------------------------------------------ // Validate a price in tariffs //------------------------------------------------------------------------ procedure TFormProduct.GridTariffsValidateEntry(Sender: TObject; aCol, aRow: integer; const OldValue: string; var NewValue: string); var Price: TAmount; begin Assert(aCol = Col_tariff_price); if ActiveControl = BtnCancel then begin GridTariffs.CancelRow(GridTariffs.Row); exit; end; if NewValue <> '' then begin try Price := DecodeAmount(NewValue); NewValue := FormatAmount(Price); except AbortMessage('InvalidPrice', [NewValue, aRow], HelpKeyword); end; if Price < 0 then AbortMessage('InvalidPrice', [NewValue, aRow], HelpKeyword); end; EditChange(Sender); end; procedure TFormProduct.qrySalesDocsdoctypeGetText(Sender: TField; var aText: string; DisplayText: boolean); begin aText := SalesDocTypeLabel(TSalesDocType(StrToIntDef(Sender.AsString, 0))); end; procedure TFormProduct.qrySalesDocsunitpriceGetText(Sender: TField; var aText: string; DisplayText: boolean); begin aText := FormatAmount(StrToIntDef(Sender.AsString, 0)); end; procedure TFormProduct.QueryStocksWarehouseNameGetText(Sender: TField; var aText: string; DisplayText: boolean); begin if QueryStocksWarehouseName.AsString = '*' then aText := TranslateText('FormProduct', 'TOTAL') else if QueryStocksWarehouseName.AsString = '?' then aText := TranslateText('FormProduct', 'UNSPECIFIED') else aText := QueryStocksWarehouseName.AsString; end; //------------------------------------------------------------------------ // Display Product tariffs or empty lines for each tariff //------------------------------------------------------------------------ procedure TFormProduct.DisplayTariffs(const aProductId: TId); var QueryTariffs: TGQuery; R : integer; begin GridTariffs.DeleteAllRows; R := 0; QueryTariffs := TGQuery.Create(Datamodule1.Db, 'SELECT PT.Id, T.Id as TariffId,T.Name as TariffName,PT.Price ' + //- 'FROM Tariffs T ' + //- 'LEFT OUTER JOIN ProductTariffs PT ' + //- ' ON PT.ProductId=' + IntToStr(aProductId) + ' ' + //- ' AND PT.TariffId=T.Id '); //- try while not QueryTariffs.EOF do begin inc(R); GridTariffs.AddRow; // Memo Ids GridTariffs.Objects[Col_tariff_producttariffId, R] := TObjId.Create(QueryTariffs.Fields[0].AsInteger); GridTariffs.Objects[Col_tariff_tariffId, R] := TObjId.Create(QueryTariffs.Fields[1].AsInteger); // Display GridTariffs.Cells[Col_tariff_name, R] := QueryTariffs.Fields[2].AsString; if QueryTariffs.Fields[3].IsNull then GridTariffs.Cells[Col_tariff_price, R] := '' else GridTariffs.Cells[Col_tariff_price, R] := FormatAmount(QueryTariffs.Fields[3].AsInteger); QueryTariffs.Next; end; finally QueryTariffs.Free; end; GridTariffs.SetColDefaultSize; end; //------------------------------------------------------------------------ // Display Taxes for a product and a sales category //------------------------------------------------------------------------ procedure TFormProduct.DisplayTaxes(const aRow: integer; const aProductId: TId); var QueryTaxes: TGQuery; TG: integer; begin QueryTaxes := TGQuery.Create(Self); QueryTaxes.Connection := Datamodule1.Db; // for each taxgroup for TG := low(TaxGroupsLibs) to high(TaxGroupsLibs) do begin SalesCategoryIds[aRow].TaxSelected := False; // Search if tax is applicable and Select Default Tax for the group QueryTaxes.Select( 'SELECT SCT.Applicable,SCT.Required,T.Id,T.Code ' + //- 'FROM SalesCategoriesTaxes SCT ' + //- 'LEFT OUTER JOIN Taxes T ON T.GroupId=' + IntToStr(TG) + ' ' + //- ' AND T.SalesDefault=1 ' + // - 'WHERE SCT.SalesCategoryId=' + IntToStr(SalesCategoryIds[aRow].SalesCategoryId) + ' AND SCT.GroupId=' + IntToStr(TG)); SalesCategoryIds[aRow].TaxGroups[TG - 1].Applicable := QueryTaxes.Fields[0].AsInteger = 1; if SalesCategoryIds[aRow].TaxGroups[TG - 1].Applicable then begin SalesCategoryIds[aRow].TaxGroups[TG - 1].Required := QueryTaxes.Fields[1].AsInteger = 1; SalesCategoryIds[aRow].TaxGroups[TG - 1].DefaultSalesTaxId := QueryTaxes.Fields[2].AsInteger; GridSalesCategories.Cells[TG + 1, aRow] := QueryTaxes.Fields[3].AsString; // search specific tax for product*salescategory*taxgroup QueryTaxes.Select( 'SELECT PT.Id, PT.TaxId, T.Code ' + //- 'FROM ProductTaxes PT ' + //- 'INNER JOIN Taxes T ON T.Id=PT.TaxId ' + //- 'WHERE PT.ProductId = ' + IdSql(aProductId) + '' + //- ' AND PT.SalesCategoryId = ' + IdSql(SalesCategoryIds[aRow].SalesCategoryId) + //- ' AND PT.GroupId=' + IntToStr(TG)); SalesCategoryIds[aRow].TaxGroups[TG - 1].Id := QueryTaxes.Fields[0].AsInteger; SalesCategoryIds[aRow].TaxGroups[TG - 1].ProductTaxId := QueryTaxes.Fields[1].AsInteger; if not QueryTaxes.EOF then begin // overload default tax GridSalesCategories.Cells[TG + 1, aRow] := QueryTaxes.Fields[2].AsString; end; end // Applicable else begin SalesCategoryIds[aRow].TaxGroups[TG - 1].Required := False; SalesCategoryIds[aRow].TaxGroups[TG - 1].DefaultSalesTaxId := 0; SalesCategoryIds[aRow].TaxGroups[TG - 1].ProductTaxId := 0; GridSalesCategories.Cells[TG + 1, aRow] := ''; end; // not Applicable end; // for each group QueryTaxes.Free; end; //------------------------------------------------------------------------ // Display Product Accounts and Taxes //------------------------------------------------------------------------ procedure TFormProduct.DisplaySalesCategories(const aProductId: TId); var QuerySalesCategories: TGQuery; R: integer; begin GridSalesCategories.DeleteAllRows; R := 0; QuerySalesCategories := TGQuery.Create(Datamodule1.Db, 'SELECT PSA.Id, SC.Id, SC.DefaultSalesAccountId, ' + ' PSA.SalesAccountId, SC.Name, AC1.Account, AC1.Name, ' + ' AC2.Account, AC2.Name ' + 'FROM SalesCategories SC ' + 'LEFT OUTER JOIN Accounts AC2 ' + ' ON AC2.Id=SC.DefaultSalesAccountId ' + 'LEFT OUTER JOIN ProductSalesAccounts PSA ' + ' ON PSA.ProductId=' + IdSql(aProductId) + ' ' + ' AND PSA.SalesCategoryId=SC.Id ' + 'LEFT OUTER JOIN Accounts AC1 ' + ' ON AC1.Id=PSA.SalesAccountId '); GridSalesCategories.OnColRowInserted := nil; try while not QuerySalesCategories.EOF do begin Inc(R); GridSalesCategories.AddRow; // Memo Ids SetLength(SalesCategoryIds, R + 1); SetLength(SalesCategoryIds[R].TaxGroups, Length(TaxGroupsLibs)); {0..n-1} SalesCategoryIds[R].ProductSalesCategoryId := QuerySalesCategories.Fields[0].AsInteger; SalesCategoryIds[R].SalesCategoryId := QuerySalesCategories.Fields[1].AsInteger; SalesCategoryIds[R].DefaultSalesAccountId := QuerySalesCategories.Fields[2].AsInteger; SalesCategoryIds[R].SalesAccountId := QuerySalesCategories.Fields[3].AsInteger; // Display account GridSalesCategories.Cells[Col_SalesCategory_Name, R] := QuerySalesCategories.Fields[4].AsString; if QuerySalesCategories.Fields[3].IsNull then begin GridSalesCategories.Cells[Col_SalesCategory_Account, R] := QuerySalesCategories.Fields[7].AsString + ' ' + QuerySalesCategories.Fields[8].AsString; end else begin GridSalesCategories.Cells[Col_SalesCategory_Account, R] := QuerySalesCategories.Fields[5].AsString + ' ' + QuerySalesCategories.Fields[6].AsString; end; // Display Taxes SalesCategoryIds[R].TaxSelected := False; DisplayTaxes(R, aProductId); QuerySalesCategories.Next; end; finally QuerySalesCategories.Free; end; GridSalesCategories.SetColDefaultSize; end; //------------------------------------------------------------------------ // Display stock //------------------------------------------------------------------------ procedure TFormProduct.DisplayStock(const aProductId: TId); begin QueryStocks.Active := False; QueryStocks.Sql.Text := // warehouse detail 'SELECT ' + //- ' '+inttostr(GRIDSTOCK_DETAIL)+' AS Footer, ' + //- ' ps.id, ' + //- ' wh.id AS WarehouseId, ' + //- ' wh.name AS WarehouseName, ' + //- ' ps.qty_checked, ' + //- ' ps.date_checked, ' + //- ' ps.qty_quoted, ' + //- ' ps.qty_ordered, ' + //- ' ps.qty_delivered, ' + //- ' qty_checked-COALESCE(ps.qty_delivered,0) ' + //- ' AS qty_remaining, ' + //- ' ps.updatedate ' + //- 'FROM Warehouses wh ' + //- 'LEFT JOIN ProductsStocks ps ' + //- ' ON ps.warehouseid=wh.id ' + //- ' AND ps.ProductId = :ProductId ' + //- 'JOIN Products psp ON psp.Id = ps.productid ' + //- 'UNION ' + //- // total 'SELECT ' + //- ' '+inttostr(GRIDSTOCK_FOOTER)+', ' + //- ' pst.id, ' + //- ' NULL AS WarehouseId, ' + //- ' ''*'' AS WarehouseName, ' + //- ' (SELECT SUM(qty_checked) FROM ProductsStocks WHERE productid = :ProductId) AS qty_checked, ' + //- ' NULL AS date_checked, ' + //- ' (SELECT SUM(COALESCE(qty_quoted,0)) FROM ProductsStocks WHERE productid = :ProductId) AS qty_quoted, ' + //- ' (SELECT SUM(COALESCE(qty_ordered,0)) FROM ProductsStocks WHERE productid = :ProductId) AS qty_ordered,' + //- ' (SELECT SUM(COALESCE(qty_delivered,0)) FROM ProductsStocks WHERE productid = :ProductId) AS qty_delivered, ' + //- ' (SELECT SUM(qty_checked)-SUM(COALESCE(qty_delivered,0)) FROM ProductsStocks WHERE productid = :ProductId) AS qty_remaining, ' + //- ' (SELECT MAX(updatedate) FROM ProductsStocks WHERE productid = :ProductId) AS updatedate ' + //- 'FROM ProductsStocks pst ' + //- 'WHERE pst.productid = :ProductId ' + //- 'ORDER BY 1,4 '; QueryStocks.Params[0].AsInteger := aProductId; QueryStocks.Open; end; //------------------------------------------------------------------------ // Display sales docs //------------------------------------------------------------------------ procedure TFormProduct.DisplaySalesDocs(const aProductId: TId); begin qrySalesDocs.active := False; qrySalesDocs.parambyname('ProductId').AsInteger := aProductId; if EditPeriod.StartDate = 0 then qrySalesDocs.parambyname('StartDate').AsDate := Now - 30 else qrySalesDocs.parambyname('StartDate').AsDate := EditPeriod.StartDate; if EditPeriod.EndDate = 0 then qrySalesDocs.parambyname('EndDate').AsDate := Now else qrySalesDocs.parambyname('EndDate').AsDate := EditPeriod.EndDate; qrySalesDocs.Open; end; //------------------------------------------------------------------------ // Display product //------------------------------------------------------------------------ procedure TFormProduct.Display(const aProductId: TId; const aWithLock: TWithLock); begin EditStockManaged.OnChange := nil; PanelTop.ReadFromQuery(aProductId, //- 'SELECT P.Name,P.Code,P.GroupId,P.PublicDescription, ' + //- ' PG.Name as GroupText, ' + //- ' P.PublicDescription,P.InternalNotes,P.Picture, ' + //- ' P.StockManaged, P.CoeffStockSales,P.SalesUnitId,' + //- ' USA.Plural AS SalesUnitText, ' + //- ' P.StockUnitId, UST.Plural AS StockUnitText ' + //- 'FROM Products P ' + //- 'LEFT JOIN ProductGroups PG ON PG.Id = P.GroupId ' + //- 'LEFT JOIN Units USA ON USA.Id = P.SalesUnitId ' + //- 'LEFT JOIN Units UST ON UST.Id = P.StockUnitId ' + //- 'WHERE P.Id = ' + IdSql(aProductId), WITH_LOCK); ProductId := aProductId; CoeffStock.Init; FormProduct.PageControl.ActivePage := FormProduct.TabSheetDescription; DisplayTariffs(aProductId); DisplaySalesCategories(aProductId); CoeffStock.UpdateLabelCoeffStockSales; DisplayStock(aProductId); DisplaySalesDocs(aProductId); EnableControls(nil); SetModified(False); EditStockManaged.OnChange := @EnableControls; end; //------------------------------------------------------------------------ // Activate buttons //------------------------------------------------------------------------ procedure TFormProduct.SetModified(const State: boolean); begin Modified := State; BtnOk.Enabled := Modified; BtnCancel.Enabled := Modified; end; procedure TFormProduct.Translate; var G: integer; begin inherited Translate; for G := 2 to GridSalesCategories.Columns.Count - 1 do GridSalesCategories.Columns[G].Title.Caption := TFormTaxes.LibTaxGroup(G - 1); GridSalesCategories.AutoSizeColumns; if not Assigned(CoeffStock) then CoeffStock := TCoeffStock.Create(ClassName, EditCoeffStockSales, EditSalesUnit, EditStockUnit, LabelCoeffStockSales); CoeffStock.UpdateLabelCoeffStockSales; end; end.