?? slsaleclothstatistic.pas
字號:
unit SLSaleClothStatistic;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, WSVoucherBrowse, DB, ActnList, Grids,WSEdit, DBGrids, QLDBGrid,
ComCtrls, ExtCtrls, ToolWin,DateUtils, ADODB, StdCtrls, Buttons, GEdit,
DBCtrls, Menus, WNADOCQuery,TypInfo, CheckLst;
type
TSLSaleClothStatisticForm = class(TWSVoucherBrowseForm)
ToolButton4: TToolButton;
ToolButton5: TToolButton;
ToolButton6: TToolButton;
ToolButton7: TToolButton;
ToolButton8: TToolButton;
ToolButton9: TToolButton;
ToolButton11: TToolButton;
ToolButton12: TToolButton;
ToolButton13: TToolButton;
adsMaster: TADODataSet;
Panel2: TPanel;
ADOQuery: TADOQuery;
ADOQuery2: TADOQuery;
Panel3: TPanel;
Panel5: TPanel;
Button3: TButton;
TempAds: TADODataSet;
WNADOCQuery1: TWNADOCQuery;
Button1: TButton;
Panel4: TPanel;
GroupBox3: TGroupBox;
Memo1: TMemo;
Button2: TButton;
Panel6: TPanel;
Button4: TButton;
ExpSttcGroupBox: TGroupBox;
ExpSttcCheckListBox: TCheckListBox;
ExpSttcCkBxPopMenu: TPopupMenu;
N1: TMenuItem;
N2: TMenuItem;
N3: TMenuItem;
N4: TMenuItem;
VipsetPanel: TPanel;
Label1: TLabel;
DateTimePicker1: TDateTimePicker;
DateTimePicker2: TDateTimePicker;
Button5: TButton;
Button6: TButton;
GroupBox1: TGroupBox;
CheckBox1: TCheckBox;
CheckBox2: TCheckBox;
CheckBox3: TCheckBox;
ClientAccountLiast: TToolButton;
DataSource1: TDataSource;
DBGrid1: TDBGrid;
AccountListQry: TADOQuery;
AccountListQryDSDesigner: TDateTimeField;
AccountListQryDSDesigner2: TStringField;
AccountListQryDSDesigner3: TStringField;
AccountListQryDSDesigner4: TBCDField;
AccountListQryDSDesigner5: TBCDField;
AccountListQryDSDesigner6: TBCDField;
AccountListQryDSDesigner7: TStringField;
ListClientSaleAct: TAction;
ListClientSale: TMenuItem;
ListGoodsSaleAct: TAction;
ListGoodsSale: TMenuItem;
ShowExpenseCkBx: TCheckBox;
EndStockCkBx: TCheckBox;
EmployeeCostCkBx: TCheckBox;
GoodsStockCkBx: TCheckBox;
ToolButton1: TToolButton;
procedure UpdateDBGrid;
procedure DBGridTitleClick(Column: TColumn);
procedure DBGridDblClick(Sender: TObject);
procedure Button3Click(Sender: TObject);
procedure FormShow(Sender: TObject);
procedure Button1Click(Sender: TObject);
procedure FormActivate(Sender: TObject);
procedure Button2Click(Sender: TObject);
procedure N1Click(Sender: TObject);
procedure N2Click(Sender: TObject);
procedure N4Click(Sender: TObject);
procedure N3Click(Sender: TObject);
procedure adsMasterBeforeOpen(DataSet: TDataSet);
procedure DateTimePicker2Exit(Sender: TObject);
procedure DateTimePicker1Exit(Sender: TObject);
procedure Button4Click(Sender: TObject);
procedure Button6Click(Sender: TObject);
procedure Button5Click(Sender: TObject);
procedure ExpSttcCheckListBoxClickCheck(Sender: TObject);
procedure ClientAccountLiastClick(Sender: TObject);
procedure ListClientSaleActExecute(Sender: TObject);
procedure ListGoodsSaleActExecute(Sender: TObject);
procedure ShowExpenseCkBxClick(Sender: TObject);
procedure adsMasterAfterOpen(DataSet: TDataSet);
private
{ Private declarations }
WhereStr,SelectStr :string;
public
{ Public declarations }
protected
function CreateEditForm: TWSEditForm; override;
end;
var
SLSaleClothStatisticForm: TSLSaleClothStatisticForm;
implementation
uses CommonDM,QLDBFlt,WSSecurity,QLRptBld,SLRpCheckReckoning;
//CommonDM, QLDBFlt, QLRptBld, SLRpCheckReckoning;
{$R *.dfm}
function TSLSaleClothStatisticForm.CreateEditForm: TWSEditForm;
begin
// Result := TSLEdClearBillAForm.Create(Self);
end;
procedure TSLSaleClothStatisticForm.UpdateDBGrid;
var I: Integer;
begin
with DBGrid do
begin
FooterRowCount := 0;
Columns[0].Footer.ValueType := fvtStaticText;
Columns[0].Footer.Value := '合計:';
Columns[0].Footer.Alignment := taCenter;
Columns[0].Title.Alignment:= taCenter;
for I := 0 to Columns.Count - 1 do
begin
Columns[i].Width :=90;
if Pos('日',Columns[I].FieldName)>0 then Columns[i].Width :=70;
if Pos('編',Columns[I].FieldName)>0 then Columns[i].Width :=70;
Columns[i].Title.Alignment:= taCenter;
if Columns[I].Field is TNumericField then
begin
if (not CheckBox1.Checked) and (Pos('數量',Columns[I].FieldName)>0 )
then Columns[I].Visible :=False;
if (not CheckBox2.Checked) and (Pos('單價',Columns[I].FieldName)>0 )
then Columns[I].Visible :=False;
if (not CheckBox3.Checked) and (Pos('金額',Columns[I].FieldName)>0 )
then Columns[I].Visible :=False;
SetStrProp(Fields[I], 'DisplayFormat','#,#.00') ;
if Pos('Price',Columns[I].FieldName)<=0 then
Columns[I].Footer.ValueType := fvtSum;
end;
end;
FooterRowCount := 1;
end;
end;
procedure TSLSaleClothStatisticForm.DBGridTitleClick(Column: TColumn);
begin
inherited;
UpdateDBGrid;
end;
procedure TSLSaleClothStatisticForm.DBGridDblClick(Sender: TObject);
begin
// inherited;
end;
procedure TSLSaleClothStatisticForm.Button3Click(Sender: TObject);
begin
inherited;
WNADOCQuery1.TabName :='TempExpenseList';
WNADOCQuery1.ConnectionString :=CommonData.acnConnection.ConnectionString;
WNADOCQuery1.Execute(False);
if trim(WNADOCQuery1.QueryTerm)<>'' then
WhereStr :=' where ' + trim(WNADOCQuery1.QueryTerm)
else WhereStr :=' Where 1=1 ' ;
Memo1.ReadOnly :=False;
Memo1.Clear;
if ( copy(trim(WNADOCQuery1.QueryTerm),1,3) ='not' ) or
( copy(trim(WNADOCQuery1.QueryTerm),1,3) ='NOT' )then
Memo1.Text :='全部不滿足條件: '
+Copy(trim(WNADOCQuery1.ShowTerm.Text),5,length(trim(WNADOCQuery1.ShowTerm.Text))-4 )
else Memo1.Text :=Copy(trim(WNADOCQuery1.ShowTerm.Text),5,length(trim(WNADOCQuery1.ShowTerm.Text))-4);
if WhereStr =' Where 1=1 ' then
begin
Memo1.Clear;
Memo1.Text :='不限制條件!';
end;
Memo1.ReadOnly :=True;
ToolBar.Hint :=' 臺帳條件: '+Memo1.Text;
if Button1.Tag=1 then Button1Click(sender)
else Button2Click(sender);
end;
procedure TSLSaleClothStatisticForm.FormShow(Sender: TObject);
var I :integer;
begin
inherited;
ADOQuery.Close;
ADOQuery.SQL.Text :=' IF EXISTS( SELECT * FROM tempdb..sysobjects '
+' WHERE ID = OBJECT_ID('+Quotedstr('tempdb..#ExpenseList')
+' )) DROP TABLE #ExpenseList ' ;
ADOQuery.ExecSQL;
ADOQuery.Close;
ADOQuery.SQL.Text :=' select b.Date [日期], '
+' b.Code [編號], '
+' b.BillMode [業務類別], '
+' b.Brief [摘要], '
+' c.name [客戶名稱], '
+' d.name [經手人] , '
+' b.ClearDate [結算日期] , '
+' b.memo [備注], '
+' e.CODE [商品編號], '
+' e.name [商品名稱], '
+' g.name [標準單位], '
+' a.GoodsSpec [顏色], '
+' a.CapStyle [杯型], '
// +' a.Quantity*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [包裝數量], '
// +' f.name [包裝單位], '
// +' a.PriceBase [銷售單價], '
+' Isnull(a.SizeA,0)*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [Ｓ], ' //
+' Isnull(a.SizeB,0)*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [Ｍ], '
+' Isnull(a.SizeC,0)*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [Ｌ], '
+' Isnull(a.SizeD,0)*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [XL], '
+' Isnull(a.SizeE,0)*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [XXL], '
+' Isnull(a.SizeF,0)*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [XXXL], '
+' Isnull(a.QuantityPcs,0)*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [均碼], '
+' Isnull(a.Amount,0)*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [銷售金額], '
+' Isnull(a.GoalQuantity,0)*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [標準數量], '
+' a.PriceGoal [標準單價], '
+' a.PriceCost [成本單價], '
+' a.PriceCost [附加費用], '
+' a.PriceCost [結算金額], '
+' a.PriceCost [折扣返利金額], '
+' h.name [商品類別], '
+' i.name [地區名稱], '
+' L.name [部門名稱], '
+' a.memo [附注] '
+' into #ExpenseList '
+' from SLSaleDetail a '
+' left outer join SLSaleMaster b on b.ID=a.MasterID '
+' left outer join DAClient c on c.ID=b.ClientID '
+' left outer join MSEmployee d on d.ID=b.EmployeeID '
+' left outer join DAGoods e on e.ID=a.GoodsID '
+' left outer join MSUnit f on f.ID=a.PackUnitID '
+' left outer join MSUnit g on g.ID=e.UnitID '
+' left outer join DAGoodsClass h on h.ID=e.GoodsClassID '
+' left outer join DAArea i on i.ID=c.AreaID '
+' left outer join MSDepartment l on l.ID=d.DepartmentID '
+' where b.RecordState<>'+ Quotedstr('刪除')
+' and isnull(a.GoodsID,0)<>0 '
+' order by b.date ,b.code,a.ID ';
ADOQuery.ExecSQL;
ADOQuery.Close;
ADOQuery.SQL.Text :=' Update #ExpenseList set '
+' [結算金額]=null,[折扣返利金額]=null,[附加費用]=null ';
ADOQuery.ExecSQL;
{ADOQuery.Close;
ADOQuery.SQL.Text :=' Update #ExpenseList set '
+' [標準單價]=[銷售金額]/[標準數量]'
+' where [標準數量]<>0 and [標準數量] is not null ';
ADOQuery.ExecSQL; }
ADOQuery.Close;
ADOQuery.SQL.Text :=' insert into #ExpenseList ( [日期], '
+' [編號], [業務類別],[摘要],[客戶名稱],[經手人], '
+' [備注],[附加費用]) '
+' select a.Date [日期],a.Code [編號], '
+' a.BillMode [業務類別],a.Brief [摘要], '
+' b.name [客戶名稱], c.name [經手人] , '
+' a.memo [備注],a.SundryFee*Isnull(a.ModeDC,1)*Isnull(a.ModeC,1) [附加費用] '
+' from SLSaleMaster a '
+' left outer join DAClient b on b.ID=a.ClientID '
+' left outer join MSEmployee c on c.ID=a.EmployeeID '
+' where a.RecordState<>'+ Quotedstr('刪除')
+' and Isnull(a.SundryFee,0)<>0 '
+' and isnull(a.ClientID,0)<>0 ' ; //插入附加費用記錄
ADOQuery.ExecSQL;
ADOQuery.Close;
ADOQuery.SQL.Text :=' insert into #ExpenseList ( [日期], '
+' [編號], [業務類別],[摘要],[客戶名稱],[經手人], '
+' [備注],[結算金額],[折扣返利金額] ) '
+' select a.Date [日期],a.Code [編號], '
+' a.BillMode [業務類別],a.Brief [摘要], '
+' b.name [客戶名稱],c.name [經手人] , '
+' a.memo [備注],a.AmountD [結算金額], '
+' a.AmountRed [折扣返利金額] from FNClearSLMaster a '
+' left outer join DAClient b on b.ID=a.ClientID '
+' left outer join MSEmployee c on c.ID=a.EmployeeID '
+' where a.RecordState<>'+ Quotedstr('刪除')
+' and isnull(a.ClientID,0)<>0 ' ; //插入結算記錄
ADOQuery.ExecSQL;
if Guarder.UserName<>'系統管理員' then
begin
ADOQuery.Close;
ADOQuery.SQL.Text :=' delete from #ExpenseList '
+' where [地區名稱] not in ( select b.name '
+' from MSRolePermissions a '
+' left outer join MSPermission b on b.ID=a.PermissionID '
+' where b.PermissionClass='+Quotedstr('X-查看地區')
+' and a.RoleID= '+ inttostr(Guarder.UserID)+' ) ';
ADOQuery.ExecSQL;
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -