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) { } } }