public static List <reportpendingsupplyqty> GetInTransitQtyDetail(string stockid) { reportpendingsupplyqty qty; List <reportpendingsupplyqty> QtyList = new List <reportpendingsupplyqty>(); try { string query = ""; SqlConnection conn = new SqlConnection(Login.connString); query = "select a.pono,a.podate,c.DeliveryPeriod,c.CustomerID,d.Name customername, a.stockitemid,a.qtyordered,isnull(b.qtyReceived, 0) qtyreceived," + "(a.qtyordered - isnull(b.qtyReceived, 0)) pendingsupply " + "from ViewPOQtyOrdered as a left join viewpoqtyreceived as b " + "on (a.pono = b.pono and a.podate = b.podate and a.stockitemid = b.stockitemid) " + "left join POHeader as c on (a.PONo = c.pono and a.podate = c.podate) " + "left join customer as d on c.CustomerID = d.CustomerID where a.stockitemid = '" + stockid + "'"; SqlCommand cmd = new SqlCommand(query, conn); conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { qty = new reportpendingsupplyqty(); qty.TrackingNo = reader.GetInt32(0); //For PONO qty.TrackingDate = reader.GetDateTime(1); // For PODate qty.CustomerPODate = reader.GetDateTime(1).AddDays(reader.GetInt32(2)); //Target Date qty.CustomerID = reader.GetString(3); qty.CustomerName = reader.GetString(4); qty.StockItemID = reader.GetString(5); qty.POQty = reader.IsDBNull(6) ? 0 : reader.GetDouble(6); //Ordered Quantity qty.BilledQty = reader.IsDBNull(7) ? 0 : reader.GetDouble(7); //Received Quantity qty.BalanceQty = reader.IsDBNull(8) ? 0 : reader.GetDouble(8); // Supply Pendding Quantity QtyList.Add(qty); } conn.Close(); } catch (Exception ex) { MessageBox.Show("Error querying Quantity Details"); } return(QtyList); }
public static List <reportpendingsupplyqty> GetQtyDetail(string stockid) { reportpendingsupplyqty qty; List <reportpendingsupplyqty> QtyList = new List <reportpendingsupplyqty>(); try { string query = ""; SqlConnection conn = new SqlConnection(Login.connString); query = "select DocumentID, trackingno, trackingdate, ReferenceNo, customerpono, customerpodate, " + "isnull(quantity,0),isnull(invqty, 0),CustomerName " + "from ViewPOvsInvoiceQtyTotal where stockitemid='" + stockid + "'" + " and documentid='POPRODUCTINWARD' order by trackingno"; SqlCommand cmd = new SqlCommand(query, conn); conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { qty = new reportpendingsupplyqty(); qty.DocumentID = reader.GetString(0); qty.TrackingNo = reader.GetInt32(1); qty.TrackingDate = reader.GetDateTime(2); qty.ReferenceNo = reader.GetString(3); qty.CustomerPoNo = reader.GetString(4); qty.CustomerPODate = reader.GetDateTime(5); qty.POQty = reader.IsDBNull(6) ? 0 : reader.GetDouble(6); qty.BilledQty = reader.IsDBNull(7) ? 0 : reader.GetDouble(7); qty.CustomerName = reader.GetString(8); QtyList.Add(qty); } conn.Close(); } catch (Exception ex) { MessageBox.Show("Error querying Quantity Details"); } return(QtyList); }
public static List <reportpendingsupplyqty> QtyList() { reportpendingsupplyqty povsinv; List <reportpendingsupplyqty> QtyList = new List <reportpendingsupplyqty>(); try { string query = ""; SqlConnection conn = new SqlConnection(Login.connString); ////////query = "select a.STOCKITEMID,b.Name,a.QUANTITYRECEIPT,a.QUANTITYISSUE,a.PRESENTSTOCK " + //////// " from ViewPresentTotalStock a,StockItem b where a.STOCKITEMID=b.StockItemID " + //////// " order by a.STOCKITEMID"; query = "select documentid, stockitemid, StockItemName, " + "sum(isnull(quantity, 0)) POqty, sum(isnull(invqty, 0)) InvQty," + "sum(isnull(quantity, 0)) - sum(isnull(invqty, 0)) InvQty " + "from ViewPOvsInvoiceQtyTotal " + "group by documentid, stockitemid, StockItemName " + "order by documentid, stockitemid, StockItemName"; SqlCommand cmd = new SqlCommand(query, conn); conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { povsinv = new reportpendingsupplyqty(); povsinv.DocumentID = reader.GetString(0); povsinv.StockItemID = reader.GetString(1); povsinv.StockItemName = reader.GetString(2); povsinv.POQty = reader.IsDBNull(3) ? 0 : reader.GetDouble(3); povsinv.BilledQty = reader.IsDBNull(4) ? 0 : reader.GetDouble(4); povsinv.BalanceQty = reader.IsDBNull(5) ? 0 : reader.GetDouble(5); QtyList.Add(povsinv); } conn.Close(); } catch (Exception ex) { MessageBox.Show("Error querying Stock Reconcolitation Main"); } return(QtyList); }
public static List <reportpendingsupplyqty> getPendingSupplyList() { reportpendingsupplyqty pendingSupQty; List <reportpendingsupplyqty> pendingSupQtyList = new List <reportpendingsupplyqty>(); try { string query = ""; SqlConnection conn = new SqlConnection(Login.connString); query = "select a.POno,a.POdate,a.stockitemid,a.qtyordered,isnull(b.qtyreceived, 0) qtyreceived, " + "(isnull(qtyOrdered, 0) - isnull(qtyReceived, 0)) pendingsupply " + "from viewpoqtyordered as a left join ViewPOQtyReceived as b " + "on(a.pono = b.pono and a.podate = b.podate and a.stockitemid = b.stockitemid)"; SqlCommand cmd = new SqlCommand(query, conn); conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { pendingSupQty = new reportpendingsupplyqty(); pendingSupQty.TrackingNo = reader.GetInt32(0); //For PONO pendingSupQty.TrackingDate = reader.GetDateTime(1); //For PODate pendingSupQty.StockItemID = reader.IsDBNull(2) ? "" : reader.GetString(2); //For StockITemID pendingSupQty.POQty = reader.IsDBNull(3) ? 0 : reader.GetDouble(3); //Quanity Ordered pendingSupQty.BilledQty = reader.IsDBNull(4) ? 0 : reader.GetDouble(4); //Quantity Received pendingSupQty.BalanceQty = reader.IsDBNull(5) ? 0 : reader.GetDouble(5); //Pending Supply pendingSupQtyList.Add(pendingSupQty); } conn.Close(); } catch (Exception ex) { MessageBox.Show("Error querying Stock Reconcolitation"); } return(pendingSupQtyList); }