readquery.pas

胜天财务进销存2003源代码,SQLSERVER版,目前最完整的财务进销存系统.

        sGroupField := 'Name';
        sWidth := '30,80,100,90,90,90,110,90,90';
        sPrintChart := '5,Billdate,Total'; //默认图形字段
        sBandCaption := '　,销售数据'; //双表头
        sBandIndex := '6,3';
        sPubMask := '1,2,3,4,5,6,7,8,$';
        sColSum := 'SumNumber,Total';
      end;
    CLIENT_SALE_COLLECT:
      begin
        Caption := '客户销售汇总表';
        sField := 'ID,UserCode,Name,AreaName ,SumNumber,Total';
        sCaption := 'ID,客户编号,客户名称,地区,销售数量,总金额';
        sWidth := '30,90,150,90,90,90';
        sPrintChart := '5,Name,Total'; //默认图形字段
        sBandCaption := '　,销售数据'; //双表头
        sBandIndex := '4,2';
        sGroupField := 'AreaName';
        sPubMask := '1,2,3,4,5,$';
        sColSum := 'SumNumber,Total';
      end;
    CLIENT_SALE_DETAILED:
      begin
        Caption := '客户销售明细表';
        sField := 'ID,UserCode,Name,AreaName,iUserCode,BillType,Billdate,WareName,SumNumber,Total';
        sCaption := 'ID,客户编号,客户名称,地区,单据编号,单据类型,销售日期,商品名称,销售数量,总金额';
        sGroupField := 'Name';
        sWidth := '30,90,150,90,90,90,90,110,90,90';
        sPrintChart := '5,Billdate,Total'; //默认图形字段
        sBandCaption := '　,销售数据'; //双表头
        sBandIndex := '7,3';
        sPubMask := '1,2,3,4,5,6,7,8,9,$';
        sColSum := 'SumNumber,Total';
      end;
    CLIENT_SALE_PROFIT:
      begin
        Caption := '客户销售利润表';
        sField := 'ID,UserCode,Name ,AreaName,SumNumber,Total,CBTotal,LRTotal';
        sCaption := 'ID,客户编号,客户名称,地区,销售数量,销售金额,销售成本,销售利润';
        sWidth := '30,90,150,90,90,90,90,90';
        sPrintChart := '5,Name,LRTotal'; //默认图形字段
        sBandCaption := '　,销售数据'; //双表头
        sBandIndex := '4,4';
        sGroupField := 'AreaName';
        sPubMask := '1,2,3,4,5,$,$,$';
        sColSum := 'SumNumber,Total,CBTotal,LRTotal';
      end;
    EMPLOYE_SALE_PROFIT:
      begin
        Caption := '业务员销售利润表';
        sField := 'ID,UserCode,Name ,SumNumber,Total,CBTotal,LRTotal';
        sCaption := 'ID,业务员编号,业务员名称,销售数量,销售金额,销售成本,销售利润';
        sWidth := '30,80,100,90,90,90,90';
        sPrintChart := '5,Name,LRTotal'; //默认图形字段
        sBandCaption := '　,销售数据'; //双表头
        sBandIndex := '3,4';
        sPubMask := '1,2,3,4,$,$,$';
        sColSum := 'SumNumber,Total,CBTotal,LRTotal';
      end;
    WARE_SALE_PROFIT:
      begin
        Caption := '商品销售利润表';
        sBandIndex := '5,4';
        if bUseTwoUnit then
        begin
          sPubCalcField := 'TwoNumber';
          cPubFieldMode := 'S';
          sTwoField := ',TwoNumber';
          sTwoCaption := ',辅助数量';
          sTwoWidth := ',90';
          sTwoMask := ',7';
          sBandIndex := '5,5';
        end;
        sField := 'ID,UserCode,Name  ,Shortname ,Unit ,SumNumber' + sTwoField + ',Total,CBTotal,LRTotal';
        sCaption := 'ID,商品编号,商品名称,商品分类,计量单位,销售数量' + sTwoCaption +
          ',销售金额,销售成本,销售利润';
        sWidth := '30,80,150,90,90,90,90' + sTwoWidth + ',90,90';
        sPrintChart := '5,Name,LRTotal'; //默认图形字段
        sBandCaption := '　,销售数据'; //双表头
        sPubMask := '1,2,3,4,5,6' + sTwoMask + ',$,$,$';
        sColSum := 'SumNumber,Total,CBTotal,LRTotal';
      end;
    CLIENT_RECEIPT_QUERY:
      begin
        Caption := '客户应收查询';
        sField := 'ID,UserCode,Name,AreaName,Receive,ARTotal,DoARTotal,OverARTotal';
        sCaption := 'ID,客户编号,客户名称,地区,期初应收款,本期应收款,本期已收款,期末应收款';
        sWidth := '30,90,150,90,90,90,90,90';
        sPrintChart := '5,Name,ARTotal'; //默认图形字段
        sBandCaption := '　,应收款数据'; //双表头
        sBandIndex := '4,4';
        sGroupField := 'AreaName';
        sPubMask := '1,2,3,4,5,$,$,$';
        sColSum := 'Receive,ARTotal,DoARTotal,OverARTotal';
      end;
    PROVIDE_PAYABLE_QUERY:
      begin
        Caption := '供应商应付查询';
        sField := 'ID,UserCode,Name,AreaName,Payable,ARTotal,DoARTotal,OverARTotal';
        sCaption := 'ID,供应商编号,供应商名称,地区,期初应付款,本期应付款,本期已付款,期末应付款';
        sWidth := '30,90,150,90,90,90,90,90';
        sPrintChart := '5,Name,ARTotal'; //默认图形字段
        sBandCaption := '　,应付款数据'; //双表头
        sBandIndex := '4,4';
        sPubMask := '1,2,3,4,5,$,$,$';
        sColSum := 'Payable,ARTotal,DoARTotal,OverARTotal';
      end;
    CLIENT_RECEIPT_DETAILED:
      begin
        Caption := '客户应收款明细';
        sField := 'ID,UserCode,Name,AreaName,iUserCode,BillDate,SumTotal,DoTotal';
        sCaption := 'ID,客户编号,客户名称,地区,单据编号,开单日期,本期应收款,本期已收款';
        sWidth := '30,90,150,90,90,90,90,90';
        sPrintChart := '5,Name,SumTotal'; //默认图形字段
        sBandCaption := '　,应收款数据'; //双表头
        sBandIndex := '6,2';
        sGroupField := 'AreaName';
        sPubMask := '1,2,3,4,5,6,$,$';
        sColSum := 'SumTotal,DoTotal';
      end;
    PROVIDE_PAYABLE_DETAILED:
      begin
        Caption := '供应商应付款明细';
        sField := 'ID,UserCode,Name,AreaName,iUserCode,BillDate,SumTotal,DoTotal';
        sCaption := 'ID,供应商编号,供应商名称,地区,单据编号,开单日期,本期应付款,本期已付款';
        sWidth := '30,90,150,90,90,90,90,90';
        sPrintChart := '5,Name,SumTotal'; //默认图形字段
        sBandCaption := '　,应付款数据'; //双表头
        sBandIndex := '6,2';
        sPubMask := '1,2,3,4,5,6,$,$';
        sColSum := 'SumTotal,DoTotal';
      end;
    OPERATE_LOG:
      begin
        Caption := '操作日志查询';
        sField := 'ID,Date,PCName,Employee ,Operate';
        sCaption := 'ID,日期,电脑名称,职员,事件';
        sWidth := '30,120,120,120,200';
        bbChart.Visible := ivNever;
        bbAlldel.Visible := ivAlways;
        bbDetaled.Visible := ivNever;
        if not StrToBool2(GetIniValue(frmData.ADOConnet, 'OperateLog')) then
          ShowMsg('你没有启用操作日志记录功能，请在账套信息的账套设置里启用！');
      end;
  else
    begin
      ShowMsg('内部参数出错,请与开发商联系！');
      exit;
    end;
  end;
  if lTree = 2 then
  begin
    TreeMain.Visible := True;
    MainGrid.Visible := False;
    bbSaveAs.Enabled := False;
    StrToTreeField(TreeMain, sField, sCaption, sWidth, sPubMask);
    TreeMain.ColumnByFieldName('ID').Visible := false;
    TreeMain.Columns[1].SummaryFooterType := cstCount; //合计
    TreeMain.Columns[1].SummaryFooterFormat := '记录条数: 0';
    SetColSum(TreeMain, sColSum); //设置列合计
    SetCol(Caption, TdxDbGrid(TreeMain), 1);
  end
  else
  begin
    TreeMain.Visible := False;
    MainGrid.Visible := True;
    bbSaveAs.Enabled := True;
    StrToGridField(MainGrid, sField, sCaption, sWidth, sPubMask);
    MainGrid.ColumnByFieldName('ID').Visible := false;
    MainGrid.Columns[1].SummaryFooterType := cstCount;
    MainGrid.Columns[1].SummaryFooterFormat := '记录条数: 0';
    SetCanWrite(MainGrid, sWrite); //设置可写
    SetColSum(MainGrid, sColSum); //设置列合计
    if bReportGroup and (sGroupField <> '') then
      TdxDBGridColumn(MainGrid.ColumnByFieldName(sGroupField)).GroupIndex := 0;
    //分组
    SetCol(Caption, MainGrid, 1);
    if sBandCaption <> '' then
      bVisibleBand := StrToGridBand(MainGrid, sBandCaption, sBandIndex);
    if bVisibleBand then
      bbBands.Visible := ivAlways; //双表头
  end;
  sReadChart := ReadChartSetup(lMode);
  if Trim(sReadChart) <> '' then
    sPrintChart := sReadChart;
end;

procedure TfrmReadQuery.LoadData;
var
  sSql, sWhere, sJoin: string;
begin
  treeMain.DataSource := nil;
  MainGrid.DataSource := nil;
  if lTree = 2 then
    treeMain.DataSource := daBase2Info
  else
    MainGrid.DataSource := daBase2Info;

  case lMode of
    BASE_CLIENT:
      sSql := ' SELECT * FROM Unit WHERE  Mode = ' + IntTostr(BASE_CLIENT);
    BASE_PROVIDE:
      sSql := ' SELECT * FROM Unit WHERE  Mode = ' + IntTostr(BASE_PROVIDE);
    BASE_EMPLOYE:
      sSql := ' SELECT * FROM Employe WHERE Admin<>1';
    BASE_WARE:
      sSql := ' SELECT W.ID, W.TreeParent, W.UserCode,w.Unit,w.Unit2,w.Scale, W.Name, W.Sort,' +
        ' Sum(WS.Number) AS SumNumber, Sum(WS.Total) AS SumTotal' +
        ' FROM Ware AS W LEFT JOIN WareStock AS WS ON W.ID = WS.WareID ' +
        ' GROUP BY W.ID, W.TreeParent, W.UserCode, w.Unit,w.Unit2,w.Scale,W.Name, W.Sort';
    BASE_DEPOT:
      sSql := ' SELECT * FROM Depot';
    BASE_FIXED_ADD:
      sSql := ' SELECT * FROM FixedAssets';
    BASE_FIXED_WORK:
      begin
        sSql := 'SELECT f.ID, f.UserCode, f.Name, f.UseMonth, f.CountMonth,w.ID as WID,f.ID as FixID, w.Work'
          + ' FROM FixedAssets AS f LEFT JOIN FixedWork AS w ON f.ID = w.FixID';
      end;
    WAGE_DATA_INPUT, QUERY_WAGE_PRINT_BAR, QUERY_WAGE_PRINT_TAB:
      begin
        sSql := 'SELECT E.ID, E.TreeParent, E.UserCode, E.Name, E.Wage, E.Dept,' +
          ' W.ID AS WID, E.ID AS EmployeID, W.Signature, W.Memo, W.Period, ' +
          ' W.A0, W.A1, W.A2, W.A3, W.A4, W.A5, W.A6, W.A7, W.A8, W.A9, W.A10, W.A11, W.A12,' +
          ' W.A13, W.A14, W.A15, W.A16, W.A17, W.A18, W.A19, W.A20, W.A21, W.A22, W.A23,' +
          ' W.A24, W.A25, W.A26, W.A27, W.A28, W.A29, W.A30 ' +
          ' FROM Employe AS E LEFT JOIN WageTable AS W ON E.ID = W.EmployeID' +
          ' WHERE (((E.Admin)<>1))'; // AND W.Period=' + IntToStr(GetPeriod);
      end;
    QUERY_WAGE_PRINT_SUM:
      sSql := ' SELECT 0 as ID, E.Dept,Sum(E.Wage) as Wage,Sum(A0) as A0,' +
        ' Sum(A1) as A1,Sum(A2) as A2,Sum(A3) as A3,Sum(A4) as A4,Sum(A5) as A5,Sum(A6) as A6,Sum(A7) as A7,Sum(A8) as A8,Sum(A9) as A9,Sum(A10) as A10,' +
        ' Sum(A11) as A11,Sum(A12) as A12,Sum(A13) as A13,Sum(A14) as A14,Sum(A15) as A15,Sum(A16) as A16,Sum(A17) as A17,Sum(A18) as A18,Sum(A19) as A19,Sum(A20) as A20,' +
        ' Sum(A21) as A21,Sum(A22) as A22,Sum(A23) as A23,Sum(A24) as A24,Sum(A25) as A25,Sum(A26) as A26,Sum(A27) as A27,Sum(A28) as A28,Sum(A29) as A29,Sum(A30) as A30' +
        ' FROM Employe AS E LEFT JOIN WageTable AS W ON E.ID = W.EmployeID' +
        ' WHERE E.Admin<>1' +
        ' GROUP BY E.Dept';
    QUERY_FIXED_BILL:
      sSql := ' SELECT * FROM FixedAssets where' +
        ' (indate >= #' + sStartDate + '# AND inDate <=#' + sEndDate + '#)';
    QUERY_FIXED_DEPRECIATE:
      sSql := ' SELECT F.ID, F.TreeParent, F.UserCode, F.Name, F.UseMonth, F.BornValue,' +
        ' F.AllAbate,(F.AllAbate+iif(isnull(Sum(D1.MAbateValue)),0,Sum(D1.MAbateValue))) AS SumDep,' +
        ' iif(isnull(Sum(D2.MAbateMod)),0,Sum(D2.MAbateMod)) AS NowSumMod,' +
        ' iif(isnull(Sum(D2.MAbateValue)),0,Sum(D2.MAbateValue)) AS NowSumValue' +
        ' FROM (FixedAssets AS F LEFT JOIN (SELECT * FROM FixedDepreciate WHERE Period<' + IntToStr(GetPeriod)
        + ')' +
        ' AS D1 ON F.ID = D1.FixID)' +
        ' LEFT JOIN (SELECT * FROM FixedDepreciate WHERE Period=' + IntToStr(GetPeriod) + ')' +
        ' AS D2 ON F.ID = D2.FixID' +
        ' WHERE (F.Delete=False)' +
        ' GROUP BY F.ID, F.TreeParent, F.UserCode, F.Name, F.UseMonth, F.BornValue, F.AllAbate';
    QUERY_WAGE_LEARNING:
      sSql := ' SELECT 0 as ID,E.Learning, Count(E.ID) AS [count], [Count]/AC.AllCount*100 as [Percent]'
        + ' FROM Employe E ,[Select Count(ID)as AllCount,admin From Employe GROUP BY admin]. AS AC' +
        ' WHERE E.Admin<>1 and ac.Admin<>1 GROUP BY E.Learning, AC.AllCount';
    WARE_STOCK_QUERY:
      begin
        if lDepotID = 0 then sWhere := 'WareStock'
        else sWhere := '(SELECT * FROM WareStock WHERE DepotID=' + IntToStr(lDepotID) + ')';
        if lOutOrder = 0 then
        begin
          if lTree = 2 then //显示所有商品不管是否有库存
            sSql := ' SELECT W.ID, W.TreeParent, W.UserCode,w.Unit,w.Unit2,w.Scale, W.Name, W.Sort,' +
              ' Sum(WS.Number) AS SumNumber,Sum(WS.Total) AS SumTotal,iif(SumNumber=0,0,SumTotal/SumNumber) as Price' +
              ' FROM Ware AS W LEFT JOIN ' + sWhere + ' AS WS ON W.ID = WS.WareID ' +
              ' GROUP BY W.ID, W.TreeParent, W.UserCode, w.Unit,w.Unit2,w.Scale,W.Name, W.Sort'
          else
            sSql := ' SELECT W.ID, W.TreeParent, W.UserCode,w.Unit,w.Unit2,w.Scale, W.Name, W.Sort,' +
              ' Sum(WS.Number) AS SumNumber,Sum(WS.Total) AS SumTotal, iif(SumNumber=0,0,SumTotal/SumNumber) as Price' +