public Boolean ApproveStockHoldingHeader(stockholdingheader shh) { Boolean status = true; string utString = ""; try { string updateSQL = "update StockHoldingHeader set DocumentStatus=99, status=1 " + ", ApproveUser='******'" + ", commentStatus='" + shh.CommentStatus + "'" + ", DocumentNo=" + shh.DocumentNo + ", DocumentDate=convert(date, getdate())" + " where DocumentID='" + shh.DocumentID + "'" + " and TemporaryNo=" + shh.TemporaryNo + " and TemporaryDate='" + shh.TemporaryDate.ToString("yyyy-MM-dd") + "'"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("update", "StockHoldingHeader", "", updateSQL) + Main.QueryDelimiter; if (!UpdateTable.UT(utString)) { status = false; } } catch (Exception) { status = false; } return(status); }
public Boolean reverseStockHoldingHeader(stockholdingheader shh) { Boolean status = true; string utString = ""; try { string updateSQL = "update StockHoldingHeader set DocumentStatus=" + shh.DocumentStatus + // ",QCStatus=" + mrnh.QCStatus + ", forwardUser='******'" + ", commentStatus='" + shh.CommentStatus + "'" + ", ForwarderList='" + shh.ForwarderList + "'" + " where DocumentID='" + shh.DocumentID + "'" + " and TemporaryNo=" + shh.TemporaryNo + " and TemporaryDate='" + shh.TemporaryDate.ToString("yyyy-MM-dd") + "'"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("update", "StockHoldingHeader", "", updateSQL) + Main.QueryDelimiter; if (!UpdateTable.UT(utString)) { status = false; } } catch (Exception) { status = false; } return(status); }
public static List <stockholdingdetail> getStockHoldingHeaderDetail(stockholdingheader shh) { stockholdingdetail shd; List <stockholdingdetail> StockHoldingHeaderDetailList = new List <stockholdingdetail>(); try { string query = ""; SqlConnection conn = new SqlConnection(Login.connString); query = "select RowID,DocumentID,DocumentName,TemporaryNo,TemporaryDate,StockItemID,StockItemName,ModelNo,ModelName,Quantity, " + "InwardDocumentID,InwardDocumentNo,InwardDocumentDate,StockReferenceNo " + "from ViewStockHoldingDetail " + " where DocumentID='" + shh.DocumentID + "'" + " and TemporaryNo=" + shh.TemporaryNo + " and TemporaryDate='" + shh.TemporaryDate.ToString("yyyy-MM-dd") + "'" + " order by StockItemID"; SqlCommand cmd = new SqlCommand(query, conn); conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { shd = new stockholdingdetail(); shd.RowID = reader.GetInt32(0); shd.DocumentID = reader.GetString(1); shd.DocumentName = reader.GetString(2); shd.TemporaryNo = reader.GetInt32(3); shd.TemporaryDate = reader.GetDateTime(4).Date; shd.StockItemID = reader.GetString(5); shd.StockItemName = reader.GetString(6); shd.ModelNo = reader.IsDBNull(7)?"NA":reader.GetString(7); shd.ModelName = reader.IsDBNull(8) ? "NA" : reader.GetString(8); shd.Quantity = reader.GetDouble(9); shd.InwardDocumentID = reader.GetString(10); shd.InwardDocumentNo = reader.GetString(11); shd.InwardDocumentDate = reader.GetDateTime(12); shd.StockReferenceNo = reader.GetInt32(13); StockHoldingHeaderDetailList.Add(shd); } conn.Close(); } catch (Exception) { MessageBox.Show("Error querying Stock Header Details"); } return(StockHoldingHeaderDetailList); }
public Boolean validateStockHoldingHeader(stockholdingheader shh) { Boolean status = true; try { if (shh.StoreLocationID.Trim().Length == 0 || shh.StoreLocationID == null) { return(false); } if (shh.Remarks.Trim().Length == 0 || shh.Remarks == null) { return(false); } } catch (Exception ex) { } return(status); }
public Boolean InsertSHHeaderAndDetail(stockholdingheader shh, List <stockholdingdetail> StockHoldingHeaderDetails) { Boolean status = true; string utString = ""; string updateSQL = ""; try { shh.TemporaryNo = DocumentNumberDB.getNumber(shh.DocumentID, 1); if (shh.TemporaryNo <= 0) { MessageBox.Show("Error in Creating New Number"); return(false); } updateSQL = "update DocumentNumber set TempNo =" + shh.TemporaryNo + " where FYID='" + Main.currentFY + "' and DocumentID='" + shh.DocumentID + "'"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("update", "DocumentNumber", "", updateSQL) + Main.QueryDelimiter; updateSQL = "insert into StockHoldingHeader " + "(DocumentID,TemporaryNo,TemporaryDate,DocumentNo,DocumentDate," + "StoreLocationID,Remarks,Comments,CommentStatus,CreateUser," + "CreateTime,ForwarderList,Status,DocumentStatus)" + " values (" + "'" + shh.DocumentID + "'," + shh.TemporaryNo + "," + "'" + shh.TemporaryDate.ToString("yyyy-MM-dd") + "'," + shh.DocumentNo + "," + "'" + shh.DocumentDate.ToString("yyyy-MM-dd") + "'," + "'" + shh.StoreLocationID + "'," + "'" + shh.Remarks + "'," + "'" + shh.Comments + "'," + "'" + shh.CommentStatus + "'," + "'" + Login.userLoggedIn + "'," + "GETDATE()" + "," + "'" + shh.ForwarderList + "'," + +shh.Status + "," + +shh.DocumentStatus + ")"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("insert", "StockHoldingHeader", "", updateSQL) + Main.QueryDelimiter; updateSQL = "Delete from StockHoldingDetail where DocumentID='" + shh.DocumentID + "'" + " and TemporaryNo=" + shh.TemporaryNo + " and TemporaryDate='" + shh.TemporaryDate.ToString("yyyy-MM-dd") + "'"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("delete", "StockHoldingDetail", "", updateSQL) + Main.QueryDelimiter; foreach (stockholdingdetail shd in StockHoldingHeaderDetails) { updateSQL = "insert into StockHoldingDetail " + "(DocumentID,TemporaryNo,TemporaryDate,StockItemID,ModelNo,Quantity,InwardDocumentID,InwardDocumentNo,InwardDocumentdate,StockReferenceNo) " + "values ('" + shd.DocumentID + "'," + shh.TemporaryNo + "," + "'" + shd.TemporaryDate.ToString("yyyy-MM-dd") + "'," + "'" + shd.StockItemID + "'," + "'" + shd.ModelNo + "'," + shd.Quantity + "," + "'" + shd.InwardDocumentID + "'," + shd.InwardDocumentNo + "," + "'" + shd.InwardDocumentDate.ToString("yyyy-MM-dd") + "'," + shd.StockReferenceNo + ")"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("insert", "StockHoldingDetail", "", updateSQL) + Main.QueryDelimiter; } if (!UpdateTable.UT(utString)) { status = false; } } catch (Exception ex) { status = false; MessageBox.Show("Transaction Exception Occured"); } return(status); }
public Boolean updateSHHeaderAndDetail(stockholdingheader shh, stockholdingheader prevshh, List <stockholdingdetail> StockHoldingHeaderDetails) { Boolean status = true; string utString = ""; try { string updateSQL = "update StockHoldingHeader set TemporaryNo='" + shh.TemporaryNo + "',TemporaryDate='" + shh.TemporaryDate.ToString("yyyy-MM-dd") + "',DocumentNo='" + shh.DocumentNo + "',DocumentDate='" + shh.DocumentDate.ToString("yyyy-MM-dd") + "', StoreLocationID='" + shh.StoreLocationID + "', Remarks='" + shh.Remarks + "', Comments='" + shh.Comments + "', CommentStatus='" + shh.CommentStatus + "', ForwarderList='" + shh.ForwarderList + "'" + " where DocumentID='" + prevshh.DocumentID + "'" + " and TemporaryNo=" + prevshh.TemporaryNo + " and TemporaryDate='" + prevshh.TemporaryDate.ToString("yyyy-MM-dd") + "'"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("update", "StockHoldingHeader", "", updateSQL) + Main.QueryDelimiter; updateSQL = "Delete from StockHoldingDetail where DocumentID='" + prevshh.DocumentID + "'" + " and TemporaryNo=" + prevshh.TemporaryNo + " and TemporaryDate='" + prevshh.TemporaryDate.ToString("yyyy-MM-dd") + "'"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("delete", "StockHoldingDetail", "", updateSQL) + Main.QueryDelimiter; foreach (stockholdingdetail shd in StockHoldingHeaderDetails) { updateSQL = "insert into StockHoldingDetail " + "(DocumentID,TemporaryNo,TemporaryDate,StockItemID,ModelNo,Quantity,InwardDocumentID,InwardDocumentNo,InwardDocumentdate,StockReferenceNo) " + "values ('" + shd.DocumentID + "'," + shd.TemporaryNo + "," + "'" + shd.TemporaryDate.ToString("yyyy-MM-dd") + "'," + "'" + shd.StockItemID + "'," + "'" + shd.ModelNo + "'," + shd.Quantity + "," + "'" + shd.InwardDocumentID + "'," + shd.InwardDocumentNo + "," + "'" + shd.InwardDocumentDate.ToString("yyyy-MM-dd") + "'," + shd.StockReferenceNo + ")"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("insert", "StockHoldingDetail", "", updateSQL) + Main.QueryDelimiter; } if (!UpdateTable.UT(utString)) { status = false; MessageBox.Show("Transaction Exception Occured"); } } catch (Exception ex) { status = false; } return(status); }
public List <stockholdingheader> getFilteredStockHoldingHeader(string userList, int opt, string userCommentStatusString) { stockholdingheader shh; List <stockholdingheader> StockHoldingHeaderList = new List <stockholdingheader>(); try { //approved user comment status string string acStr = ""; try { acStr = userCommentStatusString.Substring(0, userCommentStatusString.Length - 2) + "1" + Main.delimiter2; } catch (Exception ex) { acStr = ""; } //----- string query1 = "select RowID, DocumentID, DocumentName,TemporaryNo,TemporaryDate,DocumentNo,DocumentDate," + " StoreLocationID,StoreLocationName,Remarks,Comments," + " CommentStatus,CreateUser,ForwardUser,ApproveUser,CreatorName,CreateTime,ForwarderName,ApproverName,ForwarderList,Status,DocumentStatus " + " from ViewStockHoldingHeader" + " where ((ForwardUser='******' and DocumentStatus between 2 and 98) " + " or (CreateUser='******' and DocumentStatus=1)" + " or (CommentStatus like '%" + userCommentStatusString + "%' and DocumentStatus between 1 and 98)) order by DocumentDate desc,DocumentID asc,DocumentNo desc"; string query2 = "select RowID, DocumentID, DocumentName,TemporaryNo,TemporaryDate,DocumentNo,DocumentDate," + " StoreLocationID,StoreLocationName,Remarks,Comments," + " CommentStatus,CreateUser,ForwardUser,ApproveUser,CreatorName,CreateTime,ForwarderName,ApproverName,ForwarderList,Status,DocumentStatus " + " from ViewStockHoldingHeader" + " where ((createuser='******' and DocumentStatus between 2 and 98 ) " + " or (ForwarderList like '%" + userList + "%' and DocumentStatus between 2 and 98 and ForwardUser <> '" + Login.userLoggedIn + "')" + " or (commentStatus like '%" + acStr + "%' and DocumentStatus between 1 and 98)) order by TemporaryDate desc,DocumentID asc,TemporaryNo desc"; string query3 = "select RowID, DocumentID, DocumentName,TemporaryNo,TemporaryDate,DocumentNo,DocumentDate," + " StoreLocationID,StoreLocationName,Remarks,Comments," + " CommentStatus,CreateUser,ForwardUser,ApproveUser,CreatorName,CreateTime,ForwarderName,ApproverName,ForwarderList,Status,DocumentStatus " + " from ViewStockHoldingHeader" + " where ((createuser='******'" + " or ForwarderList like '%" + userList + "%'" + " or commentStatus like '%" + acStr + "%'" + " or approveUser='******')" + " and DocumentStatus = 99) order by DocumentDate desc,DocumentID asc,DocumentNo desc"; string query6 = "select RowID, DocumentID, DocumentName,TemporaryNo,TemporaryDate,DocumentNo,DocumentDate," + " StoreLocationID,StoreLocationName,Remarks,Comments," + " CommentStatus,CreateUser,ForwardUser,ApproveUser,CreatorName,CreateTime,ForwarderName,ApproverName,ForwarderList,Status,DocumentStatus " + " from ViewStockHoldingHeader" + " where DocumentStatus = 99 order by DocumentDate desc,DocumentID asc,DocumentNo desc"; SqlConnection conn = new SqlConnection(Login.connString); string query = ""; switch (opt) { case 1: query = query1; break; case 2: query = query2; break; case 3: query = query3; break; case 6: query = query6; break; default: query = ""; break; } SqlCommand cmd = new SqlCommand(query, conn); conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { try { shh = new stockholdingheader(); shh.RowID = reader.GetInt32(0); shh.DocumentID = reader.GetString(1); shh.DocumentName = reader.GetString(2); shh.TemporaryNo = reader.GetInt32(3); shh.TemporaryDate = reader.GetDateTime(4); shh.DocumentNo = reader.GetInt32(5); shh.DocumentDate = reader.GetDateTime(6); shh.StoreLocationID = reader.GetString(7); shh.StoreLocationName = reader.GetString(8); shh.Remarks = reader.GetString(9); shh.Comments = reader.GetString(10); if (!reader.IsDBNull(11)) { shh.CommentStatus = reader.GetString(11); } else { shh.CommentStatus = ""; } shh.CreateUser = reader.GetString(12); shh.ForwardUser = reader.GetString(13); shh.ApproveUser = reader.GetString(14); shh.CreatorName = reader.GetString(15); shh.CreateTime = reader.GetDateTime(16); shh.ForwarderName = reader.GetString(17); shh.ApproverName = reader.GetString(18); if (!reader.IsDBNull(19)) { shh.ForwarderList = reader.GetString(19); } else { shh.ForwarderList = ""; } shh.Status = reader.GetInt32(20); shh.DocumentStatus = reader.GetInt32(21); StockHoldingHeaderList.Add(shh); } catch (Exception ex) { } } conn.Close(); } catch (Exception ex) { MessageBox.Show("Error querying Stock Header Details"); } return(StockHoldingHeaderList); }