public void GetConsumption()
{
string sql = @"SELECT comp.zfinIndex as compIndex, comp.zfinName as compName, comp.basicUom as compUoM, pc.actualConsumption, pc.bomScrap, pc.actualScrap,pc.targetConsumption,pc.targetScrap, pc.createdOn
FROM tbOrders o LEFT JOIN tbProductionConsumption pc ON o.orderId=pc.orderId LEFT JOIN tbZfin comp ON comp.zfinId=pc.componentId
WHERE o.sapId=@order";
SqlConnection conn = new SqlConnection(Variables.npdConnectionString);
SqlCommand sqlComand = new SqlCommand(sql, conn);
sqlComand.Parameters.Add("@order", SqlDbType.Int);
sqlComand.Parameters["@order"].Value = Number;
try
{
conn.Open();
using (SqlDataReader reader = sqlComand.ExecuteReader())
{
while (reader.Read())
{
ProductionUsage pu = new ProductionUsage
{
Component = new Product {
Index = int.Parse(reader[0].ToString()), Name = reader[1].ToString(), UoM = reader[2].ToString()
},
ActualConsumption = float.Parse(reader[3].ToString()),
BomScrap = float.Parse(reader[4].ToString()),
ActualScrap = float.Parse(reader[5].ToString()),
TargetConsumption = float.Parse(reader[6].ToString()),
TargetScrap = float.Parse(reader[7].ToString())
};
ProductUsages.Add(pu);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public async Task GetProductUsage(int materialTypeId)
{
string str = "";
foreach (Order o in Items)
{
str += o.Number + ",";
}
str = str.Length > 0 ? str.Substring(0, str.Length - 1) : str;
string sql = @"SELECT o.sapId, comp.zfinIndex as component, comp.zfinName as comp_name, comp.basicUom, pc.actualConsumption,pc.actualScrap,pc.targetScrap, z.zfinIndex, z.zfinName, o.executedSap, u.pcPerBox, u.pcPerPallet
FROM tbProductionConsumption pc LEFT JOIN tbZfin comp ON comp.zfinId=pc.componentId LEFT JOIN tbOrders o ON o.orderId=pc.orderId
LEFT JOIN tbZfin z ON z.zfinId=o.zfinId LEFT JOIN tbUom u ON u.zfinId=z.zfinId
WHERE pc.actualConsumption IS NOT NULL AND pc.actualConsumption > 0 AND o.sapId IN (" + str + ") AND comp.materialType=" + materialTypeId;
if (str.Length > 0)
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
SqlCommand command = new SqlCommand(sql, conn);
try
{
using (SqlDataReader nReader = command.ExecuteReader())
{
while (nReader.Read())
{
try
{
ProductionUsage Pu = new ProductionUsage
{
Component = new Product
{
Index = Int32.Parse(nReader[1].ToString()),
Name = nReader[2].ToString(),
UoM = nReader[3].ToString(),
CategoryId = materialTypeId,
},
Product = new Product
{
Index = Int32.Parse(nReader[7].ToString()),
Name = nReader[8].ToString(),
boxCount = int.Parse(nReader[10].ToString()),
palletCount = int.Parse(nReader[11].ToString())
},
Order = new Order(Int32.Parse(nReader[0].ToString()), float.Parse(nReader[9].ToString())),
ActualConsumption = float.Parse(nReader[4].ToString(), System.Globalization.NumberStyles.Number),
ActualScrap = float.Parse(nReader[5].ToString(), System.Globalization.NumberStyles.Number),
TargetScrap = float.Parse(nReader[6].ToString(), System.Globalization.NumberStyles.Number)
};
Items.Where(i => i.Number == Int32.Parse(nReader[0].ToString())).FirstOrDefault().ProductUsages.Add(Pu);
}
catch (Exception ex)
{
}
}
}
}catch (Exception ex)
{
}
}
}