1、strSql.AppendLine("”)
Imports System.Text
Dim strSql As StringBuilder = New StringBuilder("")
strSql.AppendLine(" SELECT PartBin.PartNum 物料,T1.PartDescription 物料描述,PartBin.WarehouseCode 仓库,Warehse.Description 仓库描述,PartBin.BinNum 库位,WhseBin.Description 库位描述")
strSql.AppendLine(" ,PartBin.LotNum 批次,T1.TrackLots 追踪批次,PartBin.OnhandQty 库存数,T1.IUM 单位")
strSql.AppendLine(" ,((case when T1.CostMethod = 'T' then PartLot.LotLaborCost + PartLot.LotBurdenCost + PartLot.LotMaterialCost + PartLot.LotSubContCost + PartLot.LotMtlBurCost else PartCost.AvgLaborCost + PartCost.AvgBurdenCost + PartCost.AvgMaterialCost + PartCost.AvgSubContCost + PartCost.AvgMtlBurCost end)) 单位成本")
strSql.AppendLine(" ,CAST(0 AS DECIMAL(18,2)) 总金额,ProdGrup.Description 群组,T1.NetWeight 单位净重,T1.NetWeightUOM 净重单位,PartClass.Description 类别")
strSql.AppendLine(" ,((case when ((EntityGLC1.GLControlCode) is null) then GLCntrlAcct.GLAccount else GLCntrlAcct1.GLAccount end)) 总账科目")
strSql.AppendLine(" ,(PartBin.OnhANDQty * T1.NetWeight) 合计净重,CASE WHEN ISNULL(T1.CheckBox11,0)=1 THEN 0 ELSE CONVERT(DECIMAL(18,2),ISNULL(T10.boxPCs,0)) END 每箱数量,CASE WHEN ISNULL(T1.CheckBox11,0)=1 THEN 0 ELSE CONVERT(DECIMAL(18,2),CASE WHEN ISNULL(T10.boxPCs,0)=0 THEN 0 ELSE PartBin.OnHandQty/T10.boxPCs END,2) END 总箱数,CASE WHEN ISNULL(T1.CheckBox11,0)=1 THEN 0 ELSE (SELECT TOP 1 Number06 FROM ICE.UD30 WITH(NOLOCK) WHERE Key3='1' AND Company=T1.Company AND Key1=T1.PartNum)/(SELECT TOP 1 Number08 FROM ICE.UD30 WITH(NOLOCK) WHERE ShortChar01='Part' AND Company=T1.Company AND Key1=T1.PartNum)*PartBin.OnHandQty END 总体积,T1.CommodityCode HS商品代码,T1.Character01 合同名称,T1.Number01 合同重量,(PartBin.OnhANDQty * T1.Number01) 合计合同重量,T1.RunOut 用完")
strSql.AppendLine(" FROM Erp.PartBin INNER JOIN Part T1 ON PartBin.Company = T1.Company AND PartBin.PartNum = T1.PartNum")
strSql.AppendLine(" LEFT OUTER JOIN Erp.ProdGrup ON T1.Company = ProdGrup.Company AND T1.ProdCode = ProdGrup.ProdCode")
strSql.AppendLine(" LEFT OUTER JOIN Erp.PartCost ON PartBin.Company = PartCost.Company AND PartBin.PartNum = PartCost.PartNum")
strSql.AppendLine(" LEFT OUTER JOIN Erp.PartClass ON T1.Company = PartClass.Company AND T1.ClassID = PartClass.ClassID")
strSql.AppendLine(" LEFT OUTER JOIN Erp.PartLot ON PartBin.Company = PartLot.Company AND PartBin.PartNum = PartLot.PartNum AND PartBin.LotNum = PartLot.LotNum")
strSql.AppendLine(" LEFT OUTER JOIN Erp.EntityGLC ON PartClass.Company = EntityGLC.Company AND PartClass.ClassID = EntityGLC.Key1 AND EntityGLC.RelatedToFile = 'PartClass'")
strSql.AppendLine(" LEFT OUTER JOIN Erp.EntityGLC AS EntityGLC1 ON T1.Company = EntityGLC1.Company AND T1.PartNum = EntityGLC1.Key1")
strSql.AppendLine(" LEFT OUTER JOIN Erp.GLCntrlAcct ON EntityGLC.Company = GLCntrlAcct.Company AND EntityGLC.GLControlCode = GLCntrlAcct.GLControlCode AND GLCntrlAcct.GLAcctContext = 'Inventory/Expense'")
strSql.AppendLine(" LEFT OUTER JOIN Erp.GLCntrlAcct AS GLCntrlAcct1 ON EntityGLC1.Company = GLCntrlAcct1.Company AND EntityGLC1.GLControlCode = GLCntrlAcct1.GLControlCode AND GLCntrlAcct1.GLAcctContext = 'Inventory/Expense'")
strSql.AppendLine(" LEFT OUTER JOIN Erp.Warehse ON PartBin.Company = Warehse.Company AND PartBin.WarehouseCode = Warehse.WarehouseCode")
strSql.AppendLine(" LEFT OUTER JOIN Erp.WhseBin ON PartBin.Company = WhseBin.Company AND PartBin.WarehouseCode = WhseBin.WarehouseCode AND PartBin.BinNum = WhseBin.BinNum")
strSql.AppendLine(" LEFT OUTER JOIN (SELECT Company,PartNum,(MIN(XPartNum)) AS Calculated_xpart,(MIN(PartDescription)) AS Calculated_xpartdesc FROM Erp.CustXPrt GROUP BY Company,PartNum) AS SubQuery2 ON T1.Company = SubQuery2.Company AND T1.PartNum = SubQuery2.PartNum")
strSql.AppendLine(" LEFT OUTER JOIN Erp.WhseBin AS WhseBin1 ON PartBin.Company = WhseBin1.Company AND PartBin.WarehouseCode = WhseBin1.WarehouseCode AND PartBin.BinNum = WhseBin1.BinNum")
strSql.AppendLine(" LEFT OUTER JOIN (SELECT Company,Key1,CASE WHEN Number14=0 THEN 0 ELSE Number09/Number14 END boxPCs FROM Ice.UD30 WITH(NOLOCK) WHERE Key3='0') T10 ON T1.Company=T10.Company AND T1.PartNum=T10.Key1")
strSql.AppendLine(String.Format(" WHERE PartBin.Company='{0}' ", Otrans.Session.CompanyID))
2、+/& “”_
Dim mysql as string = "select d.Key1 [Shipment No.],a.Character02+ '/'+b.ShortChar06 as [品名及规格] "_
+ " ,cast(cast(CASE WHEN a.Number36<>0 OR a.CheckBox04=1 THEN a.Number36 ELSE c.NetWeight * a.Number04 END as int) as varchar) + 'KG' [数量/重量]"_
+" ,Convert(decimal(18,4),(CASE WHEN a.Number36<>0 THEN a.Number36*(CASE WHEN a.Number36<>0 THEN a.Number36 ELSE a.Number04 END*a.Number02) /(c.NetWeight *(CASE WHEN a.Number36<>0 THEN a.Number36 ELSE a.Number04 END)) ELSE a.Number04*a.Number02 END) / NULLIF( (CASE WHEN a.Number36<>0 OR a.CheckBox04=1 THEN a.Number36 ELSE c.NetWeight * a.Number04 END ) ,0)) [单价]"_
+ " , Convert(decimal(18,2),CASE WHEN a.Number36<>0 THEN a.Number36*(CASE WHEN a.Number36<>0 THEN a.Number36 ELSE a.Number04 END*a.Number02)/(c.NetWeight *(CASE WHEN a.Number36<>0 THEN a.Number36 ELSE a.Number04 END)) ELSE a.Number04*a.Number02 END) [申报总价]"_
+" ,b.ShortChar03 [合同/订单编号],a.ShortChar07 合同序号,d.Character05 装货港口 , h.ShortChar01 [卸货港口],d.Date12 [DATE],CONVERT(varchar,GETDATE(),112)+g.key1 [编号]"_
+ " from UD28 a "_
+ " inner join UD104a e on a.Key1 = e.Key1 and a.company=e.company AND A.KEY2=e.CHILDKEY1"_
+ " left join part c with(nolock) on a.shortchar02 = c.partnum and c.company=a.company "_
+ " left join erp.UOM f on c.company = f.company and c.ium =f. UOMCode"_
+ " left JOIN UD104 d WITH(NOLOCK) ON a.Key1=d.Key1 and d.company='NSZ' and d.key1 <> 'GNU000006' and d.key1 <> 'GNU000004'"_
+ " left join ud103a b with(nolock) on a.shortchar14=b.ChildKey1 and a.shortchar07=b.ChildKey3 and b.company = 'NSZ' "_
+ " left join ice.ud27 g on g.key1 <> ''"_
+ " left join country h on h.company ='NSZ'and h.CountryNum = d.ShortChar21 "_