?? slsaleclothstatistic.pas

?? 詳細的ERP設計資料
?? PAS
?? 第 1 頁 / 共 3 頁
字號:
    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('Y-查看業務')
        +' and a.RoleID= '+ inttostr(Guarder.UserID)+' ) ';
    ADOQuery.ExecSQL;
  end;

  ADOQuery.Close;
  ADOQuery.SQL.Text :=' if exists (select * from dbo.sysobjects where '
      +' id = object_id '
      +' (' +Quotedstr('[dbo].[TempExpenseList]')+') and OBJECTPROPERTY(id,'
      +Quotedstr('IsUserTable')+' ) = 1) drop table [dbo].[TempExpenseList] ';
  ADOQuery.ExecSQL;
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' select top 1 * into TempExpenseList from #ExpenseList' ;
  ADOQuery.ExecSQL;
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' select top 1 * from TempExpenseList' ;
  ADOQuery.open;
  ExpSttcCheckListBox.Columns :=4;
  ExpSttcCheckListBox.Items.Clear;
  ExpSttcCheckListBox.Hint :='';
  WNADOCQuery1.Field.Clear;
  for I := 0 to ADOQuery.Fields.Count - 1 do
  begin
    if not (ADOQuery.Fields[i] is TNumericField)  then
       ExpSttcCheckListBox.Items.Add(Trim(ADOQuery.Fields[i].FieldName))
     else
       ExpSttcCheckListBox.Hint :=ExpSttcCheckListBox.Hint
         +', sum(isnull('+Trim(ADOQuery.Fields[i].FieldName)+',0)) as ['
         +Trim(ADOQuery.Fields[i].FieldName)+'] ';
     WNADOCQuery1.Field.Add(Trim(ADOQuery.Fields[i].FieldName));
  end;
  WhereStr :=' where  1=1 ';
  Memo1.Text :='不限制條件!';
  SelectStr :='';



end;

procedure TSLSaleClothStatisticForm.Button1Click(Sender: TObject);
begin
  Panel6.Visible :=True;
  Panel6.Repaint;
  Button1.Tag:=1 ;
  adsMaster.Close;
  adsMaster.CommandText:=' select * from #ExpenseList'+WhereStr
      +' order by [日期], [編號]' ;
//  showmessage(adsMaster.CommandText);
  adsMaster.open;
  Panel6.Visible :=False;
  UpdateDBGrid;
  DBGrid.hint :='';
end;

procedure TSLSaleClothStatisticForm.FormActivate(Sender: TObject);
begin
  inherited;
  ADOQuery.Close;
  ADOQuery.SQL.Text :='select max(日期) MDate  from #ExpenseList ';
  ADOQuery.Open;
  if ADOQuery.FieldByName('MDate').IsNull then WhereStr :=Datetostr(date)
    else WhereStr :=Trim(ADOQuery.fieldbyname('MDate').AsString);
  Memo1.ReadOnly :=False;
  Memo1.Clear;
  Memo1.Text :=' 日期 等于'+ Quotedstr(WhereStr);
  Memo1.ReadOnly :=True;
  WhereStr :=' where [日期]='+Quotedstr(WhereStr);
  Button1Click(sender);
end;

procedure TSLSaleClothStatisticForm.Button2Click(Sender: TObject);
var I :integer;
    SelectStr1:String;
begin
  inherited;
  Panel6.Visible :=True;
  Panel6.Repaint;
  Button1.Tag:=0;

  if Trim(SelectStr) ='' then
    begin
      ExpSttcCheckListBox.Checked[1] :=true;
      ExpSttcCheckListBox.ItemIndex := 1;
      ExpSttcCheckListBox.OnClickCheck(ExpSttcCheckListBox);
    end;
  SelectStr1 :=Trim(SelectStr);
  while Pos(',', SelectStr1)=1 do  SelectStr1[Pos(',', SelectStr1)] :=' ';

  ADOQuery.Close;
  ADOQuery.SQL.Text :=' IF EXISTS(  SELECT * FROM tempdb..sysobjects '
        +' WHERE ID = OBJECT_ID('+Quotedstr('tempdb..#ExpenseListTtl')
        +' )) DROP TABLE #ExpenseListTtl ' ;
  ADOQuery.ExecSQL;
  if  (pos('客戶名稱',SelectStr1)>0) or (pos('經手人',SelectStr1)>0) or (pos('部門名稱',SelectStr1)>0)
  then  begin
    ADOQuery.Close;
    ADOQuery.SQL.Text :=' IF EXISTS(  SELECT * FROM tempdb..sysobjects '
          +' WHERE ID = OBJECT_ID('+Quotedstr('tempdb..#ExpenseList0')
          +' )) DROP TABLE #ExpenseList0 ' ;
    ADOQuery.ExecSQL;
    //加入明細流水數據------
    ADOQuery.Close;
    if ShowExpenseCkBx.Checked then
        ADOQuery.SQL.Text :=' select *,  '
        +' [結算金額] [銷售毛利] ,'
        +' [商品名稱] [費用名稱] ,'
        +' [結算金額] [費用金額], '
        +' [結算金額] [客戶信用金額], '
        +' [結算金額] [信用預警金額] '
        +' into #ExpenseList0  from #ExpenseList  '
      else
        ADOQuery.SQL.Text :=' select *,  '
        +' [結算金額] [銷售毛利] ,'
        +' [結算金額] [客戶信用金額], '
        +' [結算金額] [信用預警金額] '
        +' into #ExpenseList0  from #ExpenseList ' ;
    ADOQuery.ExecSQL;

    ADOQuery.Close;
    if ShowExpenseCkBx.Checked then
      ADOQuery.SQL.Text :=' Update #ExpenseList0 set [客戶信用金額]=0,'
      +' [信用預警金額]=0 ,[費用名稱]=null ,[費用金額]=0 ,'
      +' [銷售毛利]=Isnull([成本單價],0)*Isnull([標準數量],0) '      
    else
      ADOQuery.SQL.Text :=' Update #ExpenseList0 set [客戶信用金額]=0,'
      +' [信用預警金額]=0 ,[銷售毛利]=Isnull([成本單價],0)*Isnull([標準數量],0) ';
    ADOQuery.ExecSQL;

    //插入預警數量 ==========
    ADOQuery.Close;
    ADOQuery.SQL.Text :=' insert into  #ExpenseList0 ( '
      +' [客戶名稱],[客戶信用金額] ) '
      +' select b.name [客戶名稱], a.QuotaAmount [客戶信用金額] '
      +' from PCCredit a                  '
      +' left outer join DAClient b on b.ID=a.ClientID '
      +' where a.RecordState<>'+ Quotedstr('刪除')
      +' and Isnull(a.QuotaAmount,0)<>0 ';
    ADOQuery.ExecSQL;

    if ShowExpenseCkBx.Checked then
    begin
      ADOQuery.Close;

      if EndStockCkBx.Checked then
        ADOQuery.SQL.Text :=' insert into  #ExpenseList0 ( '
          +' [日期],[編號],[業務類別],[客戶名稱],[經手人], '
          +' [部門名稱], [費用名稱],[費用金額] ) '
          +' select b.date [日期],b.Code [編號],b.BillMode [業務類別],'
          +' c.name [客戶名稱],d.name [經手人], e.name  [部門名稱] ,'
          +' f.name [費用名稱],Isnull(Amount,0)  [費用金額]  '
          +' from FNExpenseDetail  a                  '
          +' left outer join FNExpenseMaster b on b.ID=a.MasterID '
          +' left outer join DAClient        c on c.ID=a.ClientID '
          +' left outer join MSEmployee      d on d.ID=b.EmployeeID  '
          +' left outer join MSDepartment    e on e.ID=d.DepartmentID  '
          +' left outer join DAExpenseClass    f on f.ID=a.ExpenseID  '
          +' where b.RecordState<>'+ Quotedstr('刪除')
          +' and Isnull(a.Amount,0)<>0  and Isnull(a.ClientID,0)<>0  '
      else
        ADOQuery.SQL.Text :=' insert into  #ExpenseList0 ( '
          +' [日期],[編號],[業務類別],[客戶名稱],[經手人], '
          +' [部門名稱], [費用名稱],[費用金額] ) '
          +' select b.date [日期],b.Code [編號],b.BillMode [業務類別],'
          +' c.name [客戶名稱],d.name [經手人], e.name  [部門名稱] ,'
          +' f.name [費用名稱],Isnull(Amount,0)  [費用金額]  '
          +' from FNExpenseDetail  a                  '
          +' left outer join FNExpenseMaster b on b.ID=a.MasterID '
          +' left outer join DAClient        c on c.ID=a.ClientID '
          +' left outer join MSEmployee      d on d.ID=b.EmployeeID  '
          +' left outer join MSDepartment    e on e.ID=d.DepartmentID  '
          +' left outer join DAExpenseClass    f on f.ID=a.ExpenseID  '
          +' where b.RecordState<>'+ Quotedstr('刪除')
          +' and Isnull(a.Amount,0)<>0 ';
      ADOQuery.ExecSQL;
    end;

    ADOQuery.Close;
    if ShowExpenseCkBx.Checked then
      begin
        if EndStockCkBx.Checked  then
          ADOQuery.SQL.Text:=' select ' + SelectStr1+ ExpSttcCheckListBox.hint
          +' , Sum(Isnull([銷售毛利],0)) [銷售毛利]  '
          +' , Sum(Isnull([費用金額],0)) [客戶成本]  '
          +' , Sum(Isnull([客戶信用金額],0)) [應收帳款余額]  '
          +' , Sum(Isnull([客戶信用金額],0)) [客戶信用金額]  '
          +' , Sum(Isnull([客戶信用金額],0)) [信用預警金額]  '
          +' into #ExpenseListTtl from #ExpenseList0  '
          +WhereStr +' group by '+SelectStr1
        else
          ADOQuery.SQL.Text:=' select ' + SelectStr1+ ExpSttcCheckListBox.hint
  //        +' , [費用名稱]  '
          +' , Sum(Isnull([銷售毛利],0)) [銷售毛利]  '
          +' , Sum(Isnull([費用金額],0)) [費用金額]  '
          +' , Sum(Isnull([客戶信用金額],0)) [應收帳款余額]  '
          +' , Sum(Isnull([客戶信用金額],0)) [客戶信用金額]  '
          +' , Sum(Isnull([客戶信用金額],0)) [信用預警金額]  '
          +' into #ExpenseListTtl from #ExpenseList0  '
  //        +WhereStr +' group by '+SelectStr1+',[費用名稱] '
          +WhereStr +' group by '+SelectStr1 ;
      end
    else
        ADOQuery.SQL.Text:=' select ' + SelectStr1+ ExpSttcCheckListBox.hint
        +' , Sum(Isnull([銷售毛利],0)) [銷售毛利]  '
        +' , Sum(Isnull([客戶信用金額],0)) [應收帳款余額]  '
        +' , Sum(Isnull([客戶信用金額],0)) [客戶信用金額]  '
        +' , Sum(Isnull([客戶信用金額],0)) [信用預警金額]  '
        +' into #ExpenseListTtl from #ExpenseList0  '
        +WhereStr +' group by '+SelectStr1    ;
    ADOQuery.ExecSQL;

    ADOQuery.Close;
    ADOQuery.SQL.Text:=' update #ExpenseListTtl set [標準單價]=null, '
       +'  [成本單價]=null ';
    ADOQuery.ExecSQL;


    ADOQuery.Close;
    ADOQuery.SQL.Text:=' update #ExpenseListTtl set [標準單價]= '
       +' [銷售金額]/[標準數量] where Isnull([標準數量],0)<>0 ';
    ADOQuery.ExecSQL;

    ADOQuery.Close;
    ADOQuery.SQL.Text:=' update #ExpenseListTtl set [成本單價]= '
       +' [銷售毛利]/[標準數量] where Isnull([標準數量],0) <>0 ';
    ADOQuery.ExecSQL;

    ADOQuery.Close;
    ADOQuery.SQL.Text:=' update #ExpenseListTtl set [銷售毛利]= '
       +' [銷售金額]-[銷售毛利]';
    ADOQuery.ExecSQL;

//    ADOQuery.Close;
//    ADOQuery.SQL.Text:=' update #ExpenseListTtl set [銷售單價]= '
//       +' [銷售金額]/[包裝數量] where isnull([包裝數量],0) <>0 ';
//    ADOQuery.ExecSQL;


    ADOQuery.Close;
    ADOQuery.SQL.Text:=' update #ExpenseListTtl set [應收帳款余額]= '
      +' ( Isnull([銷售金額],0)+Isnull([附加費用],0) -Isnull([結算金額],0) - '
      +' Isnull([折扣返利金額],0) ) ,'
      +' [信用預警金額]= (-Isnull([客戶信用金額],0) ) +'
      +' ( Isnull([銷售金額],0)+Isnull([附加費用],0) -Isnull([結算金額],0) - '
      +' Isnull([折扣返利金額],0) ) ';
    ADOQuery.ExecSQL;


    adsMaster.Close;
    adsMaster.CommandText:=' select * from #ExpenseListTtl ';
    adsMaster.open;
  end else
  begin
    ADOQuery.Close;
    ADOQuery.SQL.Text:=' select ' + SelectStr1+ ExpSttcCheckListBox.hint
      +' into #ExpenseListTtl from #ExpenseList  '
      +WhereStr +' group by '+SelectStr1;
    ADOQuery.ExecSQL;

    ADOQuery.Close;
    ADOQuery.SQL.Text:=' update #ExpenseListTtl set [標準單價]=null ';
    ADOQuery.ExecSQL;

    ADOQuery.Close;
    ADOQuery.SQL.Text:=' update #ExpenseListTtl set [標準單價]= '
       +' [銷售金額]/[標準數量] where [標準數量]<>0 ';
    ADOQuery.ExecSQL;
    adsMaster.Close;
    adsMaster.CommandText:=' select *,(Isnull([銷售金額],0)+Isnull([附加費用],0) '
      +' -Isnull([結算金額],0) - '
      +' Isnull([折扣返利金額],0) ) as [應收帳款余額] from #ExpenseListTtl ';
    adsMaster.open;
  end;


  Panel6.Visible :=False;
  UpdateDBGrid;
  DBGrid.hint :='匯總項目:'+SelectStr1;
end;

procedure TSLSaleClothStatisticForm.N1Click(Sender: TObject);
begin
  ExpSttcCheckListBox.Sorted :=not ExpSttcCheckListBox.Sorted;
end;

procedure TSLSaleClothStatisticForm.N2Click(Sender: TObject);
var I:integer;
begin
  for I := 0 to ExpSttcCheckListBox.Items.Count - 1 do
  begin
    ExpSttcCheckListBox.Checked[I] :=not ExpSttcCheckListBox.Checked[I];
    ExpSttcCheckListBox.ItemIndex := I;
    ExpSttcCheckListBox.OnClickCheck(ExpSttcCheckListBox);
  end;
end;

procedure TSLSaleClothStatisticForm.N3Click(Sender: TObject);
var I:integer;
begin
  for I := 0 to ExpSttcCheckListBox.Items.Count - 1 do
  begin
    ExpSttcCheckListBox.Checked[I] :=True;
    ExpSttcCheckListBox.ItemIndex := I;
    ExpSttcCheckListBox.OnClickCheck(ExpSttcCheckListBox);
  end;
end;
?? 文件大小 12471 K
?? 上傳用戶 tswccyt
?? 所屬分類企業管理
??? 相關標簽

#ERP #設計資料
?? 快捷鍵說明

復制代碼 Ctrl + C
搜索代碼 Ctrl + F
全屏模式 F11
切換主題 Ctrl + Shift + D
顯示快捷鍵 ?
增大字號 Ctrl + =
減小字號 Ctrl + -
亚洲欧美第一页_禁久久精品乱码_粉嫩av一区二区三区免费野_久草精品视频

?? slsaleclothstatistic.pas

?? 快捷鍵說明
