readquery.pas

中小企业管理系统------ ERP系统原代码

        end
        else
        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, Ws.Price, Ws.Price*SumNumber AS SumTotal' +
              ' 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,Ws.Price'
          else
            sSql := ' SELECT W.ID, W.TreeParent, W.UserCode,w.Unit,w.Unit2,w.Scale, W.Name, W.Sort,' +
              ' Sum(WS.Number) AS SumNumber, Ws.Price, Ws.Price*SumNumber AS SumTotal' +
              ' FROM Ware AS W INNER 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,Ws.Price ';
        end;
      end;
    WARE_STOCK_DISTRIBUTE:
      begin
        {SQL2000:WITH ROLLUP, WITH CUBE on SELECT statements}
        sSql := ' TRANSFORM Sum(K.Number)' +
          ' SELECT W.ID,W.UserCode,W.Name,W.Sort,W.Unit' +
          ' FROM WareStock AS K,Ware AS W,Depot AS D' +
          ' WHERE K.WareID=W.ID and K.DepotID=D.ID' +
          ' GROUP BY W.ID,W.UserCode,W.Name,W.Sort,W.Unit' +
          ' PIVOT D.Name';
      end;
    WARE_STOCK_EDIT:
      begin
        bbSave.Enabled := False;
        if bStartAccount then
          sJoin := ' INNER JOIN '
        else
          sJoin := ' LEFT JOIN ';
        if lDepotID = 0 then
        begin
          sSql := 'SELECT w.ID,W.UserCode, W.Name,w.Unit,w.Unit2,w.Scale, W.Sort,' +
            ' 1 as Initial,0 as WSID,Sum(WS.Number)as [Number],WS.Price,Sum(ws.Number*ws.Price) as Total' +
            ' FROM Ware AS W ' + sJoin + 'WareStock0 AS WS  ON W.ID = WS.WareID ' +
            ' WHERE W.SeedCount=0 ' +
            ' GROUP BY w.ID, w.TreeParent, W.UserCode, W.Name,w.Unit,w.Unit2,w.Scale, W.Sort,' +
            ' WS.Price';
          SetCanWrite(MainGrid, ''); //设置可写
        end
        else
        begin
          sSql := 'SELECT w.ID,W.UserCode, W.Name,w.Unit,w.Unit2,w.Scale, W.Sort,' +
            ' w.ID as WareID,1 as Initial,WS.ID as WSID,WS.Number as [Number],WS.Price,Sum(ws.Number*ws.Price) as Total,' +
            IntToStr(lDepotID) + ' as DepotID ' +
            ' FROM Ware AS W ' + sJoin + '(WareStock0 WHERE DepotID=' +
            IntToStr(lDepotID) + ') AS WS  ON W.ID = WS.WareID' +
            ' WHERE W.SeedCount=0 ' +
            ' GROUP BY w.ID, w.TreeParent, W.UserCode, W.Name,w.Unit,w.Unit2,w.Scale, W.Sort,' +
            ' ws.ID,WS.ID,WS.Number,WS.Price';
          if not bStartAccount then
          begin
            SetCanWrite(MainGrid, sWrite); //设置可写
            bbSave.Enabled := True;
          end
          else
            SetCanWrite(MainGrid, ''); //设置可写
        end;
      end;
    WARE_STOCK_PRICE_EDIT:
      sSql := ' SELECT W.ID, W.TreeParent, W.UserCode, W.Name, W.Sort, WS.Number, ' +
        ' WS.Price,W.ConstPrice, W.Pos_Price, W.Price1, W.Price2, W.Price3, W.Price4' +
        ' FROM Ware AS W LEFT JOIN WareStock AS WS ON W.ID = WS.WareID';
    WARE_STOCK_ORDER_COLLECT:
      sSql := ' SELECT w.ID,w.TreeParent,w.UserCode,w.Name,w.Shortname,' +
        ' w.Unit,w.Unit2,w.Scale,i.Billdate,Sum(s.Number)As SumNumber,s.Price,Sum(s.Number*s.Price) as Total' +
        ' FROM Ware w,BillIndex i,BillStock s WHERE i.Type=1 and s.Type=1 and i.ID=s.IndexID and s.WareID=w.ID and ' +
        ' i.Mode=' + IntToStr(STOCK_ORDER_EDIT) + ' and ' +
        ' (i.Billdate >= #' + sStartDate + '# AND i.Billdate <=#' + sEndDate + '#)' +
        ' GROUP BY w.ID,w.TreeParent,w.UserCode,w.Name,w.Shortname,w.Unit,w.Unit2,w.Scale,i.BillDate,s.Price';
    EMPLOYE_STOCK_ORDER_COLLECT:
      sSql := ' SELECT E.ID,E.TreeParent,E.UserCode,E.Name,i.BillDate,' +
        ' Sum(s.Number)As SumNumber,Sum(s.Number*s.Price) as Total' +
        ' FROM Employe e,BillIndex i,BillStock s ' +
        ' WHERE i.Type=1 and s.Type=1 and i.ID=s.IndexID AND i.EmployeID=e.ID and ' +
        ' i.Mode=' + IntToStr(STOCK_ORDER_EDIT) + ' and ' +
        ' (i.Billdate >= #' + sStartDate + '# AND i.Billdate <=#' + sEndDate + '#)' +
        ' GROUP BY E.ID,E.TreeParent,E.UserCode,E.Name,i.BillDate';
    CLIENT_STOCK_ORDER_COLLECT:
      sSql := ' SELECT u.ID,u.TreeParent,u.UserCode,u.Name,u.AreaName,i.BillDate,' +
        ' Sum(s.Number)As SumNumber,Sum(s.Number*s.Price) as Total' +
        ' FROM Unit u,BillIndex i,BillStock s' +
        ' WHERE i.Type=1 and s.Type=1 and i.ID=s.IndexID AND i.UnitID=u.ID and ' +
        ' i.Mode=' + IntToStr(STOCK_ORDER_EDIT) + ' and ' +
        ' (i.Billdate >= #' + sStartDate + '# AND i.Billdate <=#' + sEndDate + '#)' +
        ' GROUP BY u.ID,u.TreeParent,u.UserCode,u.Name,u.AreaName,i.BillDate';
    WARE_SALE_ORDER_COLLECT:
      sSql := ' SELECT w.ID,w.TreeParent,w.UserCode,w.Name,w.Shortname,' +
        ' w.Unit,w.Unit2,w.Scale,i.BillDate,Sum(s.Number)As SumNumber,s.Price,Sum(s.Number*s.Price) as Total' +
        ' FROM Ware w,BillIndex i,BillSale s ' +
        ' WHERE i.Type=1 and s.Type=1 and i.ID=s.IndexID and s.WareID=w.ID and ' +
        ' i.Mode=' + IntToStr(SALE_ORDER_EDIT) + ' and ' +
        ' (i.BillDate >= #' + sStartDate + '# AND i.Billdate <=#' + sEndDate + '#)' +
        ' GROUP BY w.ID,w.TreeParent,w.UserCode,w.Name,w.Shortname,w.Unit,w.Unit2,w.Scale,i.BillDate,s.Price';
    EMPLOYE_SALE_ORDER_COLLECT:
      sSql := ' SELECT E.ID,E.TreeParent,E.UserCode,E.Name,' +
        ' Sum(s.Number)As SumNumber,Sum(s.Number*s.Price) as Total' +
        ' FROM Employe e,BillIndex i,BillSale s' +
        ' WHERE i.Type=1 and s.Type=1 and i.ID=s.IndexID AND i.EmployeID=e.ID and ' +
        ' i.Mode=' + IntToStr(SALE_ORDER_EDIT) + ' and ' +
        ' (i.Billdate >= #' + sStartDate + '# AND i.Billdate <=#' + sEndDate + '#)' +
        ' GROUP BY E.ID,E.TreeParent,E.UserCode,E.Name';
    CLIENT_SALE_ORDER_COLLECT:
      sSql := ' SELECT u.ID,u.TreeParent,u.UserCode,u.Name,u.AreaName,' +
        ' Sum(s.Number)As SumNumber,Sum(s.Number*s.Price) as Total' +
        ' FROM Unit u,BillIndex i,BillSale s' +
        ' WHERE i.Type=1 and s.Type=1 and i.ID=s.IndexID AND i.UnitID=u.ID and ' +
        ' i.Mode=' + IntToStr(SALE_ORDER_EDIT) + ' and ' +
        ' (i.Billdate >= #' + sStartDate + '# AND i.Billdate <=#' + sEndDate + '#)' +
        ' GROUP BY u.ID,u.TreeParent,u.UserCode,u.Name,u.AreaName';
    WARE_STOCK_COLLECT:
      sSql := ' SELECT w.ID,w.TreeParent,w.UserCode,w.Name,w.Shortname,w.Unit,w.Unit2,w.Scale,' +
        ' Sum(iif(i.Mode=' + IntToStr(STOCK_BACK_EDIT) + ',-s.Number,s.Number))As SumNumber,' +
        ' Sum(iif(i.Mode=' + IntToStr(STOCK_BACK_EDIT) + ',-s.Number*s.Price,s.Number*s.Price)) as Total' +
        ' FROM Ware w,BillIndex i,BillStock s WHERE i.Type=2 and s.Type=2 and i.ID=s.IndexID and s.WareID=w.ID ' +
        ' and i.MODE in(' + IntToStr(STOCK_FORMAL_EDIT) + ',' + IntToStr(STOCK_BACK_EDIT) + ')' +
        ' and (i.Billdate >= #' + sStartDate + '# AND i.Billdate <=#' + sEndDate + '#)' +
        ' GROUP BY w.ID,w.TreeParent,w.UserCode,w.Name,w.Shortname,w.Unit,w.Unit2,w.Scale';
    WARE_STOCK_DETAILED:
      sSql := ' SELECT w.ID,w.TreeParent,w.UserCode,w.Name,w.Shortname,' +
        ' w.Unit,w.Unit2,w.Scale,u.Name as UnitName,i.UserCode as iUserCode,i.ID as BillID,i.Name as BillType,i.Mode as BillMode,i.Billdate,' +
        ' iif(i.Mode=' + IntToStr(STOCK_BACK_EDIT) + ',-s.Number,s.Number)As SumNumber,' +
        ' iif(i.Mode=' + IntToStr(STOCK_BACK_EDIT) + ',-s.Number*s.Price,s.Number*s.Price) as Total' +
        ' FROM Ware w,BillIndex i,BillStock s,Unit u' +
        ' WHERE i.Type=2 and s.Type=2 and i.ID=s.IndexID and s.WareID=w.ID and i.UnitID=u.ID ' +
        ' and i.MODE in(' + IntToStr(STOCK_FORMAL_EDIT) + ',' + IntToStr(STOCK_BACK_EDIT) + ')' +
        ' and (i.Billdate >= #' + sStartDate + '# AND i.Billdate <=#' + sEndDate + '#)';
    EMPLOYE_STOCK_COLLECT:
      sSql := ' SELECT E.ID,E.TreeParent,E.UserCode,E.Name,' +
        ' Sum(iif(i.Mode=' + IntToStr(STOCK_BACK_EDIT) + ',-s.Number,s.Number))As SumNumber,' +
        ' Sum(iif(i.Mode=' + IntToStr(STOCK_BACK_EDIT) + ',-s.Number*s.Price,s.Number*s.Price)) as Total' +
        ' FROM Employe e,BillIndex i,BillStock s ' +
        ' WHERE i.Type=2 and s.Type=2 and i.ID=s.IndexID AND i.EmployeID=e.ID ' +
        ' and i.MODE in(' + IntToStr(STOCK_FORMAL_EDIT) + ',' + IntToStr(STOCK_BACK_EDIT) + ')' +
        ' and (i.Billdate >= #' + sStartDate + '# AND i.Billdate <=#' + sEndDate + '#)' +
        ' GROUP BY E.ID,E.TreeParent,E.UserCode,E.Name';
    EMPLOYE_STOCK_DETAILED:
      sSql := ' SELECT E.ID,E.TreeParent,E.UserCode,E.Name,w.Name as WareName,i.ID as BillID,i.Name as BillType,i.Mode as BillMode,i.UserCode as iUserCode,i.BillDate,'
        +
        ' iif(i.Mode=' + IntToStr(STOCK_BACK_EDIT) + ',-s.Number,s.Number)As SumNumber,' +
        ' iif(i.Mode=' + IntToStr(STOCK_BACK_EDIT) + ',-s.Number*s.Price,s.Number*s.Price) as Total' +
        ' FROM Employe e,BillIndex i,BillStock s,Ware w' +
        ' WHERE i.Type=2 and s.Type=2 and i.ID=s.IndexID AND i.EmployeID=e.ID and s.WareID=w.ID ' +
        ' and i.MODE in(' + IntToStr(STOCK_FORMAL_EDIT) + ',' + IntToStr(STOCK_BACK_EDIT) + ')' +
        ' and (i.Billdate >= #' + sStartDate + '# AND i.Billdate <=#' + sEndDate + '#)';
    PROVIDE_STOCK_COLLECT:
      sSql := ' SELECT u.ID,u.TreeParent,u.UserCode,u.Name,u.AreaName,' +
        ' Sum(iif(i.Mode=' + IntToStr(STOCK_BACK_EDIT) + ',-s.Number,s.Number))As SumNumber,' +
        ' Sum(iif(i.Mode=' + IntToStr(STOCK_BACK_EDIT) + ',-s.Number*s.Price,s.Number*s.Price)) as Total' +
        ' FROM Unit u,BillIndex i,BillStock s' +
        ' WHERE i.Type=2 and s.Type=2 and i.ID=s.IndexID AND i.UnitID=u.ID ' +
        ' and i.MODE in(' + IntToStr(STOCK_FORMAL_EDIT) + ',' + IntToStr(STOCK_BACK_EDIT) + ')' +
        ' and (i.Billdate >= #' + sStartDate + '# AND i.Billdate <=#' + sEndDate + '#)' +
        ' GROUP BY u.ID,u.TreeParent,u.UserCode,u.Name,u.AreaName';
    PROVIDE_STOCK_DETAILED:
      sSql := ' SELECT u.ID,u.TreeParent,u.UserCode,u.Name,w.Name as WareName,u.AreaName,i.Name as BillType,i.UserCode as iUserCode,i.ID as BillID,i.Mode as BillMode,i.BillDate,'
        +
        ' iif(i.Mode=' + IntToStr(STOCK_BACK_EDIT) + ',-s.Number,s.Number)As SumNumber,' +
        ' iif(i.Mode=' + IntToStr(STOCK_BACK_EDIT) + ',-s.Number*s.Price,s.Number*s.Price) as Total' +
        ' FROM Unit u,BillIndex i,BillStock s,Ware w' +
        ' WHERE i.Type=2 and s.Type=2 and i.ID=s.IndexID AND i.UnitID=u.ID and s.WareID=w.ID ' +
        ' and i.MODE in(' + IntToStr(STOCK_FORMAL_EDIT) + ',' + IntToStr(STOCK_BACK_EDIT) + ')' +
        ' and (i.Billdate >= #' + sStartDate + '# AND i.Billdate <=#' + sEndDate + '#)';
    WARE_SALE_COLLECT:
      sSql := ' SELECT w.ID,w.TreeParent,w.UserCode,w.Name,w.Shortname,w.Unit,w.Unit2,w.Scale,' +
        ' Sum(iif(i.Mode=' + IntToStr(SALE_BACK_EDIT) + ',-s.Number,s.Number))As SumNumber,' +
        ' Sum(iif(i.Mode=' + IntToStr(SALE_BACK_EDIT) + ',-s.Number*s.Price,s.Number*s.Price)) as Total' +
        ' FROM Ware w,BillIndex i,BillSale s ' +
        ' WHERE i.Type=2 and s.Type=2 and i.ID=s.IndexID and s.WareID=w.ID ' +
        ' and i.MODE in(' + IntToStr(SALE_FORMAL_EDIT) + ',' + IntToStr(SALE_READY_EDIT) + ',' +
        IntToStr(SALE_BACK_EDIT) + ',' + IntToStr(SALE_POS_EDIT) + ')' +
        ' and (i.BillDate >= #' + sStartDate + '# AND i.Billdate <=#' + sEndDate + '#)' +
        ' GROUP BY w.ID,w.TreeParent,w.UserCode,w.Name,w.Shortname,w.Unit,w.Unit2,w.Scale';
    WARE_SALE_DETAILED:
      sSql := ' SELECT w.ID,w.TreeParent,w.UserCode,w.Name,w.Name as WareName,w.Shortname,i.Name as BillType,i.UserCode as iUserCode,i.ID as BillID,i.Mode as BillMode,'
        +
        ' w.Unit,w.Unit2,w.Scale,u.Name as UnitName,i.BillDate,s.Price,' +
        ' iif(i.Mode=' + IntToStr(SALE_BACK_EDIT) + ',-s.Number,s.Number)As SumNumber,' +
        ' iif(i.Mode=' + IntToStr(SALE_BACK_EDIT) + ',-s.Number*s.Price,s.Number*s.Price) as Total' +
        ' FROM Ware w,BillIndex i,BillSale s,Unit U ' +
        ' WHERE i.Type=2 and s.Type=2 and i.ID=s.IndexID and s.WareID=w.ID and i.UnitID=U.ID ' +
        ' and i.MODE in(' + IntToStr(SALE_FORMAL_EDIT) + ',' + IntToStr(SALE_READY_EDIT) + ',' +
        IntToStr(SALE_BACK_EDIT) + ',' + IntToStr(SALE_POS_EDIT) + ')' +
        ' and (i.BillDate >= #' + sStartDate + '# AND i.Billdate <=#' + sEndDate + '#)';
    EMPLOYE_SALE_COLLECT:
      sSql := ' SELECT E.ID,E.TreeParent,E.UserCode,E.Name,' +
        ' Sum(iif(i.Mode=' + IntToStr(SALE_BACK_EDIT) + ',-s.Number,s.Number))As SumNumber,' +
        ' Sum(iif(i.Mode=' + IntToStr(SALE_BACK_EDIT) + ',-s.Number*s.Price,s.Number*s.Price)) as Total' +
        ' FROM Employe e,BillIndex i,BillSale s' +
        ' WHERE i.Type=2 and s.Type=2 and i.ID=s.IndexID AND i.EmployeID=e.ID ' +
        ' and i.MODE in(' + IntToStr(SALE_FORMAL_EDIT) + ',' + IntToStr(SALE_READY_EDIT) + ',' +
        IntToStr(SALE_BACK_EDIT) + ',' + IntToStr(SALE_POS_EDIT) + ')' +
        ' and(i.Billdate >= #' + sStartDate + '# AND i.Billdate <=#' + sEndDate + '#)' +
        ' GROUP BY E.ID,E.TreeParent,E.UserCode,E.Name';
    EMPLOYE_SALE_DETAILED:
      sSql := ' SELECT E.ID,E.TreeParent,E.UserCode,E.Name,w.Name as WareName,i.BillDate,i.Name as BillType,i.UserCode as iUserCode,i.ID as BillID,i.Mode as BillMode,'
        +
        ' iif(i.Mode=' + IntToStr(SALE_BACK_EDIT) + ',-s.Number,s.Number)As SumNumber,' +
        ' iif(i.Mode=' + IntToStr(SALE_BACK_EDIT) + ',-s.Number*s.Price,s.Number*s.Price) as Total' +
        ' FROM Employe e,BillIndex i,BillSale s,Ware w' +
        ' WHERE i.Type=2 and s.Type=2 and i.ID=s.IndexID AND i.EmployeID=e.ID and s.WareID=w.ID ' +
        ' and i.MODE in(' + IntToStr(SALE_FORMAL_EDIT) + ',' + IntToStr(SALE_READY_EDIT) + ',' +
        IntToStr(SALE_BACK_EDIT) + ',' + IntToStr(SALE_POS_EDIT) + ')' +
        ' and (i.Billdate >= #' + sStartDate + '# AND i.Billdate <=#' + sEndDate + '#)';
    CLIENT_SALE_COLLECT:
      sSql := ' SELECT u.ID,u.TreeParent,u.UserCode,u.Name,u.AreaName,' +
        ' Sum(iif(i.Mode=' + IntToStr(SALE_BACK_EDIT) + ',-s.Number,s.Number))As SumNumber,' +
        ' Sum(iif(i.Mode=' + IntToStr(SALE_BACK_EDIT) + ',-s.Number*s.Price,s.Number*s.Price)) as Total' +
        ' FROM Unit u,BillIndex i,BillSale s' +
        ' WHERE i.Type=2 and s.Type=2 and i.ID=s.IndexID AND i.UnitID=u.ID  ' +
        ' and i.MODE in(' + IntToStr(SALE_FORMAL_EDIT) + ',' + IntToStr(SALE_READY_EDIT) + ',' +
        IntToStr(SALE_BACK_EDIT) + ',' + IntToStr(SALE_POS_EDIT) + ')' +
        ' and(i.Billdate >= #' + sStartDate + '# AND i.Billdate <=#' + sEndDate + '#)' +
        ' GROUP BY u.ID,u.TreeParent,u.UserCode,u.Name,u.AreaName';
    CLIENT_SALE_DETAILED:
      sSql := ' SELECT u.ID,u.TreeParent,u.UserCode,u.Name,w.Name as WareName,u.AreaName,i.BillDate,i.Name as BillType,i.ID as BillID,i.Mode as BillMode,i.UserCode as iUserCode,'
        +
        ' iif(i.Mode=' + IntToStr(SALE_BACK_EDIT) + ',-s.Number,s.Number)As SumNumber,' +
        ' iif(i.Mode=' + IntToStr(SALE_BACK_EDIT) + ',-s.Number*s.Price,s.Number*s.Price) as Total' +
        ' FROM Unit u,BillIndex i,BillSale s,Ware w' +
        ' WHERE i.Type=2 and s.Type=2 and i.ID=s.IndexID AND i.UnitID=u.ID and s.WareID=w.ID ' +
        ' and i.MODE in(' + IntToStr(SALE_FORMAL_EDIT) + ',' + IntToStr(SALE_READY_EDIT) + ',' +
        IntToStr(SALE_BACK_EDIT) + ',' + IntToStr(SALE_POS_EDIT) + ')' +
        ' and (i.Billdate >= #' + sStartDate + '# AND i.Billdate <=#' + sEndDate + '#)';
    WARE_SALE_PROFIT:
      sSql := ' SELECT w.ID,w.TreeParent,w.UserCode,w.Name,w.Shortname, w.Unit,w.Unit2,w.Scale,' +
        ' Sum(iif(i.Mode=' + IntToStr(SALE_BACK_EDIT) + ',-s.Number,s.Number))As SumNumber,' +
        ' Sum(iif(i.Mode=' + IntToStr(SALE_BACK_EDIT) + ',-s.Number*s.Price,s.Number*s.Price)) as Total,' +
        ' Sum(iif(i.Mode=' + IntToStr(SALE_BACK_EDIT) +
        ',-s.Number*s.ConstPrice,s.Number*s.ConstPrice)) as CBTotal,' +
        ' Sum(iif(i.Mode=' + IntToStr(SALE_BACK_EDIT) + ',-s.Number*s.Price,s.Number*s.Price)) -' +
        ' Sum(iif(i.Mode=' + IntToStr(SALE_BACK_EDIT) +
        ',-s.Number*s.ConstPrice,s.Number*s.ConstPrice)) as LRTotal' +
        ' FROM Ware w,BillIndex i,BillSale s ' +
        ' WHERE i.Type=2 and s.Type=2 and i.ID=s.IndexID and s.WareID=w.ID ' +
        ' and i.MODE in(' + IntToStr(SALE_FORMAL_EDIT) + ',' + IntToStr(SALE_READY_EDIT) + ',' +
        IntToStr(SALE_BACK_EDIT) + ',' + IntToStr(SALE_POS_EDIT) + ')' +
        ' and (i.Billdate >= #' + sStartDate + '# AND i.Billdate <=#' + sEndDate + '#)' +
        ' GROUP BY w.ID,w.TreeParent,w.UserCode,w.Name,w.Shortname,w.Unit,w.Unit2,w.Scale';
    EMPLOYE_SALE_PROFIT:
      sSql := ' SELECT E.ID,E.TreeParent,E.UserCode,E.Name,' +
        ' Sum(iif(i.Mode=' + IntToStr(SALE_BACK_EDIT) + ',-s.Number,s.Number))As SumNumber,' +
        ' Sum(iif(i.Mode=' + IntToStr(SALE_BACK_EDIT) + ',-s.Number*s.Price,s.Number*s.Price)) as Total,' +
        ' Sum(iif(i.Mode=' + IntToStr(SALE_BACK_EDIT) +
        ',-s.Number*s.ConstPrice,s.Number*s.ConstPrice)) as CBTotal,' +
        ' Sum(iif(i.Mode=' + IntToStr(SALE_BACK_EDIT) + ',-s.Number*s.Price,s.Number*s.Price)) -' +
        ' Sum(iif(i.Mode=' + IntToStr(SALE_BACK_EDIT) +
        ',-s.Number*s.ConstPrice,s.Number*s.ConstPrice)) as LRTotal' +
        ' FROM Employe e,BillIndex i,BillSale s' +
        ' WHERE i.Type=2 and s.Type=2 and i.ID=s.IndexID AND i.EmployeID=e.ID ' +
        ' and i.MODE in(' + IntToStr(SALE_FORMAL_EDIT) + ',' + IntToStr(SALE_READY_EDIT) + ',' +
        IntToStr(SALE_BACK_EDIT) + ',' + IntToStr(SALE_POS_EDIT) + ')' +
        ' and (i.Billdate >= #' + sStartDate + '# AND i.Billdate <=#' + sEndDate + '#)' +
        ' GROUP BY E.ID,E.TreeParent,E.UserCode,E.Name';
    CLIENT_SALE_PROFIT:
      sSql := ' SELECT u.ID,u.TreeParent,u.UserCode,u.Name,u.AreaName,' +
        ' Sum(iif(i.Mode=' + IntToStr(SALE_BACK_EDIT) + ',-s.Number,s.Number))As SumNumber,' +
        ' Sum(iif(i.Mode=' + IntToStr(SALE_BACK_EDIT) + ',-s.Number*s.Price,s.Number*s.Price)) as Total,' +
        ' Sum(iif(i.Mode=' + IntToStr(SALE_BACK_EDIT) +
        ',-s.Number*s.ConstPrice,s.Number*s.ConstPrice)) as CBTotal,' +
        ' Sum(iif(i.Mode=' + IntToStr(SALE_BACK_EDIT) + ',-s.Number*s.Price,s.Number*s.Price)) -' +
        ' Sum(iif(i.Mode=' + IntToStr(SALE_BACK_EDIT) +
        ',-s.Number*s.ConstPrice,s.Number*s.ConstPrice)) as LRTotal' +
        ' FROM Unit u,BillIndex i,BillSale s' +
        ' WHERE i.Type=2 and s.Type=2 and i.ID=s.IndexID AND i.UnitID=u.ID ' +
        ' and i.MODE in(' + IntToStr(SALE_FORMAL_EDIT) + ',' + IntToStr(SALE_READY_EDIT) + ',' +
        IntToStr(SALE_BACK_EDIT) + ',' + IntToStr(SALE_POS_EDIT) + ')' +
        ' and (i.Billdate >= #' + sStartDate + '# AND i.Billdate <=#' + sEndDate + '#