sql.Append(" select *"); sql.Append(" from ("); sql.Append(" select '店面零售' as SellFlag, a.InputDate, a.InputDate as InputDateYM,"); sql.Append(" e.CompanyNo,e.CompanyFullName,(case e.Active when 1 then '是' else '否'end ) 公司可用, d.ShopNo, d.ShopFullName,(case d.Active when 1 then '是' else '否' end)店面可用,"); sql.Append(" f.BigRegionName, f.ProvinceName, f.CityName, f.CountyName, f.TownName,"); sql.Append(" '' as DestCompanyId, '' as DestCompanyName,"); sql.Append(" '' as DestBigRegionName, '' as DestProvinceName, '' as DestCityName, '' as DestCountyName, "); sql.Append(" '' as DestTownName,"); if (rpSubProductLineId != "-1") { sql.Append(" rp.rpSubProductLine as ProductLine,"); } else { sql.Append(" c.ProductLine,"); } sql.Append(" p.ProductGroupName,b.ProductModel,b.ProductNum,b.ProductType,ps.ProductSeries,(case b.Active when 1 then '是' else '否' end)产品可用,a.SelloutQty as SelloutQty"); sql.Append(" from FactShopSellout a "); sql.Append(" inner join DimProduct b on a.ProductId = b.ProductId "); sql.Append(" inner join DimProductLine c on b.ProductLineId = c.ProductLineId "); sql.Append(" inner join DimRPSubProductLine rp on b.RPSubProductLineId = rp.RPSubProductLineId "); sql.Append(" inner join DimShop d on a.ShopId = d.ShopId "); sql.Append(" inner join DimCompany e on d.CompanyId = e.CompanyId and isKP=0 "); sql.Append(" inner join RelCompanyWith3T r on e.CompanyId = r.CompanyId and rp.RPSubProductLineId=r.RPSubProductLineId and r.Active=1"); sql.Append(" left join DimProductGroup p on b.ProductGroupId=p.ProductGroupId "); sql.Append(" left join DimProductSeries ps on b.ProductSeriesId = ps.ProductSeriesId "); sql.Append(" inner join vGeography f on e.TownId = f.TownId "); sql.Append(" where 1=1 "); sql.Append(" and a.InputDate BETWEEN '" + beginInputDate + "' and '" + endInputDate + "' "); sql.Append(" and c.ProductLineId = "); sql.Append(" (" + productLineId + ") "); sql.Append(" and b.ProductType IN "); sql.Append(" (" + ProductType + ") "); sql.Append(" and b.Active IN "); sql.Append(" (" + productActive + ") "); sql.Append(" and b.ProductGroupId IN "); sql.Append(" (" + productGroup + ") "); sql.Append(" and isnull(b.ProductSeriesId,'') IN "); sql.Append(" (" + productSeriesId + ") "); if (rpSubProductLineId != "-1") { sql.Append(" and b.rpSubProductLineId=" + rpSubProductLineId); } if (bigRegionId != -1) { sql.Append(" and f.BigRegionId=" + bigRegionId); } if (regionId != -1) { sql.Append(" and f.RegionId=" + regionId); } if (provinceId != -1) { sql.Append(" and f.ProvinceId=" + provinceId); } if (rtCityId != -1) { sql.Append(" and f.CityId=" + rtCityId); } if (companyId != -1) { sql.Append(" and e.companyId=" + companyId); } sql.Append(" union all"); sql.Append(" select '公司批发' as SellFlag, a.InputDate, a.InputDate as InputDateYM, "); sql.Append(" e.CompanyNo,e.CompanyFullName,(case e.Active when 1 then '是' else '否'end ) 公司可用, '' as ShopNo, '' as ShopFullName,'' as 店面可用,"); sql.Append(" f.BigRegionName, f.ProvinceName, f.CityName, f.CountyName,f.TownName, "); sql.Append(" i.ResellerNo as DestCompanyId, i.FullName as DestCompanyName,"); sql.Append(" j.BigRegionName as DestBigRegionName, j.ProvinceName as DestProvinceName, j.CityName as DestCityName, "); sql.Append(" i.CountyName as DestCountyName,i.TownName as DestTownName,"); if (rpSubProductLineId != "-1") { sql.Append(" rp.rpSubProductLine as ProductLine,"); } else { sql.Append(" c.ProductLine,"); } sql.Append(" p.ProductGroupName,b.ProductModel,b.ProductNum,b.ProductType,ps.ProductSeries,(case b.Active when 1 then '是' else '否' end)产品可用, "); sql.Append(" a.SellthruQty as SelloutQty "); sql.Append(" from Fact3TCompanySellthru a "); sql.Append(" inner join DimProduct b on a.ProductId = b.ProductId "); sql.Append(" inner join DimProductLine c on b.ProductLineId = c.ProductLineId "); sql.Append(" inner join DimRPSubProductLine rp on b.RPSubProductLineId = rp.RPSubProductLineId "); sql.Append(" inner join DimCompany e on a.CompanyId = e.CompanyId"); sql.Append(" inner join vGeography f on e.TownId = f.TownId "); sql.Append(" left join vMixResellerWithDimReseller i on a.DestMixResellerId = i.MixResellerId "); sql.Append(" left join vCountyGeography j on i.CountyId = j.CountyId "); sql.Append(" left join RelResellerWith3Ttype k on i.ResellerId = k.ResellerId and b.ProductLineId = k.ProductLineId "); sql.Append(" left join DimProductGroup p on b.ProductGroupId=p.ProductGroupId "); sql.Append(" left join DimProductSeries ps on b.ProductSeriesId = ps.ProductSeriesId ");下面还有一些就是条件了。
sql.Append(" from (");
sql.Append(" select '店面零售' as SellFlag, a.InputDate, a.InputDate as InputDateYM,");
sql.Append(" e.CompanyNo,e.CompanyFullName,(case e.Active when 1 then '是' else '否'end ) 公司可用, d.ShopNo, d.ShopFullName,(case d.Active when 1 then '是' else '否' end)店面可用,");
sql.Append(" f.BigRegionName, f.ProvinceName, f.CityName, f.CountyName, f.TownName,");
sql.Append(" '' as DestCompanyId, '' as DestCompanyName,");
sql.Append(" '' as DestBigRegionName, '' as DestProvinceName, '' as DestCityName, '' as DestCountyName, ");
sql.Append(" '' as DestTownName,");
if (rpSubProductLineId != "-1")
{
sql.Append(" rp.rpSubProductLine as ProductLine,");
}
else
{
sql.Append(" c.ProductLine,");
}
sql.Append(" p.ProductGroupName,b.ProductModel,b.ProductNum,b.ProductType,ps.ProductSeries,(case b.Active when 1 then '是' else '否' end)产品可用,a.SelloutQty as SelloutQty");
sql.Append(" from FactShopSellout a ");
sql.Append(" inner join DimProduct b on a.ProductId = b.ProductId ");
sql.Append(" inner join DimProductLine c on b.ProductLineId = c.ProductLineId ");
sql.Append(" inner join DimRPSubProductLine rp on b.RPSubProductLineId = rp.RPSubProductLineId ");
sql.Append(" inner join DimShop d on a.ShopId = d.ShopId ");
sql.Append(" inner join DimCompany e on d.CompanyId = e.CompanyId and isKP=0 ");
sql.Append(" inner join RelCompanyWith3T r on e.CompanyId = r.CompanyId and rp.RPSubProductLineId=r.RPSubProductLineId and r.Active=1");
sql.Append(" left join DimProductGroup p on b.ProductGroupId=p.ProductGroupId ");
sql.Append(" left join DimProductSeries ps on b.ProductSeriesId = ps.ProductSeriesId ");
sql.Append(" inner join vGeography f on e.TownId = f.TownId ");
sql.Append(" where 1=1 ");
sql.Append(" and a.InputDate BETWEEN '" + beginInputDate + "' and '" + endInputDate + "' ");
sql.Append(" and c.ProductLineId = ");
sql.Append(" (" + productLineId + ") ");
sql.Append(" and b.ProductType IN ");
sql.Append(" (" + ProductType + ") ");
sql.Append(" and b.Active IN ");
sql.Append(" (" + productActive + ") ");
sql.Append(" and b.ProductGroupId IN ");
sql.Append(" (" + productGroup + ") ");
sql.Append(" and isnull(b.ProductSeriesId,'') IN ");
sql.Append(" (" + productSeriesId + ") ");
if (rpSubProductLineId != "-1")
{
sql.Append(" and b.rpSubProductLineId=" + rpSubProductLineId);
}
if (bigRegionId != -1)
{
sql.Append(" and f.BigRegionId=" + bigRegionId);
}
if (regionId != -1)
{
sql.Append(" and f.RegionId=" + regionId);
}
if (provinceId != -1)
{
sql.Append(" and f.ProvinceId=" + provinceId);
}
if (rtCityId != -1)
{
sql.Append(" and f.CityId=" + rtCityId);
}
if (companyId != -1)
{
sql.Append(" and e.companyId=" + companyId);
} sql.Append(" union all");
sql.Append(" select '公司批发' as SellFlag, a.InputDate, a.InputDate as InputDateYM, ");
sql.Append(" e.CompanyNo,e.CompanyFullName,(case e.Active when 1 then '是' else '否'end ) 公司可用, '' as ShopNo, '' as ShopFullName,'' as 店面可用,");
sql.Append(" f.BigRegionName, f.ProvinceName, f.CityName, f.CountyName,f.TownName, ");
sql.Append(" i.ResellerNo as DestCompanyId, i.FullName as DestCompanyName,");
sql.Append(" j.BigRegionName as DestBigRegionName, j.ProvinceName as DestProvinceName, j.CityName as DestCityName, ");
sql.Append(" i.CountyName as DestCountyName,i.TownName as DestTownName,");
if (rpSubProductLineId != "-1")
{
sql.Append(" rp.rpSubProductLine as ProductLine,");
}
else
{
sql.Append(" c.ProductLine,");
}
sql.Append(" p.ProductGroupName,b.ProductModel,b.ProductNum,b.ProductType,ps.ProductSeries,(case b.Active when 1 then '是' else '否' end)产品可用, ");
sql.Append(" a.SellthruQty as SelloutQty ");
sql.Append(" from Fact3TCompanySellthru a ");
sql.Append(" inner join DimProduct b on a.ProductId = b.ProductId ");
sql.Append(" inner join DimProductLine c on b.ProductLineId = c.ProductLineId ");
sql.Append(" inner join DimRPSubProductLine rp on b.RPSubProductLineId = rp.RPSubProductLineId ");
sql.Append(" inner join DimCompany e on a.CompanyId = e.CompanyId");
sql.Append(" inner join vGeography f on e.TownId = f.TownId ");
sql.Append(" left join vMixResellerWithDimReseller i on a.DestMixResellerId = i.MixResellerId ");
sql.Append(" left join vCountyGeography j on i.CountyId = j.CountyId ");
sql.Append(" left join RelResellerWith3Ttype k on i.ResellerId = k.ResellerId and b.ProductLineId = k.ProductLineId ");
sql.Append(" left join DimProductGroup p on b.ProductGroupId=p.ProductGroupId ");
sql.Append(" left join DimProductSeries ps on b.ProductSeriesId = ps.ProductSeriesId ");下面还有一些就是条件了。