package kingdee

// 库存查询

type Inventory struct {
	FNumber    string  `gorm:"column:FNumber" json:"FNumber"`       // 物料代码
	FName      string  `gorm:"column:FName" json:"FName"`           // 物料名称
	FModel     string  `gorm:"column:FModel" json:"FModel"`         // 规格型号
	FSource    string  `gorm:"column:FSource" json:"FSource"`       // 生产车间编号
	FErpCls    string  `gorm:"column:FErpCls" json:"FErpCls"`       // 物料属性
	FBatchNo   string  `gorm:"column:FBatchNo" json:"FBatchNo"`     // 批号
	FStockNo   string  `gorm:"column:FStockNo" json:"FStockNo"`     // 仓库代码
	FStockName string  `gorm:"column:FStockName" json:"FStockName"` // 仓库名称
	FUnit      string  `gorm:"column:FUnit" json:"FUnit"`           // 基本计量单位
	FUnitQty   float64 `gorm:"column:FUnitQty" json:"FUnitQty"`     // 基本计量单位数量
	FSec       string  `gorm:"column:FSec" json:"FSec"`             // 常用计量单位
	FQty       float64 `gorm:"column:FQty" json:"FQty"`             // 常用计量单位数量
}

func ICInventory() []Inventory {
	sql := `
	SELECT
		TOP (100) PERCENT item.FNumber AS FNumber,
		item.FName AS FName,
		item.FModel AS FModel,
		(SELECT FName FROM t_SubMessage WHERE item.FErpClsID=FInterID) as FErpCls,
		ti1.FNumber AS FSource,
		i.FBatchNo AS FBatchNo,
		s.FNumber AS FStockNo,
		s.FName AS FStockName,
		ti.FName AS FUnit,
		CONVERT (FLOAT, i.FQty) AS FUnitQty,
		ti2.FName AS FSec,
		i.FQty AS FQty
	FROM
		ICInventory AS i
	LEFT OUTER JOIN t_ICItem AS item ON i.FItemID = item.FItemID
	LEFT OUTER JOIN t_Stock AS s ON i.FStockID = s.FItemID
	LEFT OUTER JOIN t_Item AS ti ON item.FUnitID = ti.FItemID
	LEFT OUTER JOIN t_Item AS ti1 ON item.FSource = ti1.FItemID
	LEFT OUTER JOIN t_Item AS ti2 ON item.FStoreUnitID = ti2.FItemID
	LEFT OUTER JOIN t_Item AS ti3 ON LEFT (item.FNumber, 5) = ti3.FNumber
	AND ti3.FItemClassID = 4
	--WHERE
	--	  (i.FQty <> 0)
	ORDER BY
		item.FNumber
	`
	var result []Inventory

	db.Raw(sql).Scan(&result)
	//db.Raw(sql).Debug().Scan(&result)

	// 打印列名称测试
	//rows, _ := db.Raw(sql).Debug().Rows()
	//fmt.Println(rows.Columns())

	return result
}