public Boolean ApprovePR(stockissueheader sih) { Boolean status = true; string utString = ""; try { string updateSQL = "update StockIssueHeader set DocumentStatus=99, status=1 " + ", ApproveUser='******'" + ", DocumentNo=" + sih.DocumentNo + ", DocumentDate=convert(date, getdate())" + " where DocumentID='" + sih.DocumentID + "'" + " and TemporaryNo=" + sih.TemporaryNo + " and TemporaryDate='" + sih.TemporaryDate.ToString("yyyy-MM-dd") + "'"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("update", "StockIssueHeader", "", updateSQL) + Main.QueryDelimiter; if (!UpdateTable.UT(utString)) { status = false; } } catch (Exception) { status = false; } return(status); }
public Boolean reversePR(stockissueheader sih) { Boolean status = true; string utString = ""; try { string updateSQL = "update StockIssueHeader set DocumentStatus=" + sih.DocumentStatus + ", forwardUser='******'" + ", ForwarderList='" + sih.ForwarderList + "'" + " where DocumentID='" + sih.DocumentID + "'" + " and TemporaryNo=" + sih.TemporaryNo + " and TemporaryDate='" + sih.TemporaryDate.ToString("yyyy-MM-dd") + "'"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("update", "StockIssueHeader", "", updateSQL) + Main.QueryDelimiter; if (!UpdateTable.UT(utString)) { status = false; } } catch (Exception) { status = false; } return(status); }
public static List <stockissuedetail> getPRDetail(stockissueheader sih) { stockissuedetail sid; List <stockissuedetail> SIDetail = new List <stockissuedetail>(); try { string query = ""; SqlConnection conn = new SqlConnection(Login.connString); query = "select RowID,DocumentID,DocumentName,TemporaryNo, TemporaryDate,StockItemID,StockItemName,ModelNo,ModelName,IssueQuantity, " + "UsedQuantity,DamagedQuantity,ReturnedQuantity," + "MRNNo,MRNDate,BatchNo,SerialNo,ExpiryDate,PurchaseQuantity,PurchasePrice,PurchaseTax,SupplierID,SupplierName,StockReferenceNo " + "from ViewStockIssueDetail " + "where DocumentID='" + sih.DocumentID + "'" + " and TemporaryNo=" + sih.TemporaryNo + " and TemporaryDate='" + sih.TemporaryDate.ToString("yyyy-MM-dd") + "'" + " order by StockItemID"; SqlCommand cmd = new SqlCommand(query, conn); conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { sid = new stockissuedetail(); sid.RowID = reader.GetInt32(0); sid.DocumentID = reader.GetString(1); sid.DocumentName = reader.GetString(2); sid.TemporaryNo = reader.GetInt32(3); sid.TemporaryDate = reader.GetDateTime(4).Date; sid.StockItemID = reader.GetString(5); sid.StockItemName = reader.GetString(6); sid.ModelNo = reader.IsDBNull(7)?"NA":reader.GetString(7); sid.ModelName = reader.IsDBNull(8) ? "NA" : reader.GetString(8); sid.IssueQuantity = reader.GetDouble(9); sid.UsedQuantity = reader.GetDouble(10); sid.DamagedQuantity = reader.GetDouble(11); sid.ReturnedQuantity = reader.GetDouble(12); sid.MRNNo = reader.GetInt32(13); sid.MRNDate = reader.GetDateTime(14).Date; sid.BatchNo = reader.IsDBNull(15) ? "" : reader.GetString(15); sid.SerialNo = reader.IsDBNull(16) ? "" : reader.GetString(16); sid.ExpiryDate = reader.IsDBNull(17) ? DateTime.Parse("1900-01-01") : reader.GetDateTime(17); sid.PurchaseQuantity = reader.GetDouble(18); sid.PurchasePrice = reader.GetDouble(19); sid.PurchaseTax = reader.GetDouble(20); sid.SupplierID = reader.IsDBNull(21) ? "" : reader.GetString(21); sid.SupplierName = reader.IsDBNull(22) ? "" : reader.GetString(22); sid.StockReferenceNo = reader.GetInt32(23); SIDetail.Add(sid); } conn.Close(); } catch (Exception ex) { MessageBox.Show("Error querying MRN Details"); } return(SIDetail); }
public Boolean validateSIHeader(stockissueheader sih) { Boolean status = true; try { if (sih.DocumentID.Trim().Length == 0 || sih.DocumentID == null) { return(false); } if (sih.IssueType == 0) { return(false); } if (sih.ReferenceNo == 0) { return(false); } if (sih.ReferenceDate == null) { return(false); } if (sih.ToLocation.Trim().Length == 0 || sih.ToLocation == null) { return(false); } if (sih.Remarks.Trim().Length == 0 || sih.Remarks == null) { return(false); } } catch (Exception ex) { return(false); } return(status); }
public List <stockissueheader> getFilteredSIHeaderForRawMaterial(int prodPlanNo, DateTime planDate) { stockissueheader sih; List <stockissueheader> SIHeaders = new List <stockissueheader>(); try { string query = "select RowID, DocumentID, DocumentName,TemporaryNo,TemporaryDate,DocumentNo,DocumentDate," + " IssueType,ReferenceNo,ReferenceDate,ToLocation,ToLocationName," + " Remarks ,CreateUser,ForwardUser,ApproveUser,CreatorName,CreateTime,ForwarderName,ApproverName," + "ForwarderList,status,DocumentStatus " + " from ViewStockIssueHeader" + " where ReferenceNo = " + prodPlanNo + " and ReferenceDate = '" + planDate.ToString("yyyy-MM-dd") + "' and IssueType = 1 and" + " DocumentStatus = 99 and status = 1"; SqlConnection conn = new SqlConnection(Login.connString); SqlCommand cmd = new SqlCommand(query, conn); conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { try { sih = new stockissueheader(); sih.RowID = reader.GetInt32(0); sih.DocumentID = reader.GetString(1); sih.DocumentName = reader.GetString(2); sih.TemporaryNo = reader.GetInt32(3); sih.TemporaryDate = reader.GetDateTime(4); sih.DocumentNo = reader.GetInt32(5); sih.DocumentDate = reader.GetDateTime(6); sih.IssueType = reader.GetInt32(7); sih.ReferenceNo = reader.GetInt32(8); sih.ReferenceDate = reader.GetDateTime(9); sih.ToLocation = reader.GetString(10); sih.ToLocationName = reader.GetString(11); sih.Remarks = reader.GetString(12); sih.CreateUser = reader.GetString(13); sih.ForwardUser = reader.GetString(14); sih.ApproveUser = reader.GetString(15); sih.CreatorName = reader.GetString(16); sih.CreateTime = reader.GetDateTime(17); sih.ForwarderName = reader.GetString(18); sih.ApproverName = reader.GetString(19); if (!reader.IsDBNull(20)) { sih.ForwarderList = reader.GetString(20); } else { sih.ForwarderList = ""; } sih.status = reader.GetInt32(21); sih.DocumentStatus = reader.GetInt32(22); SIHeaders.Add(sih); } catch (Exception ex) { } } conn.Close(); } catch (Exception ex) { MessageBox.Show("Error querying StockIssue Header Details"); } return(SIHeaders); }
public Boolean InsertSIHeaderAndDetail(stockissueheader sih, List <stockissuedetail> SIDetails) { Boolean status = true; string utString = ""; string updateSQL = ""; try { sih.TemporaryNo = DocumentNumberDB.getNumber(sih.DocumentID, 1); if (sih.TemporaryNo <= 0) { MessageBox.Show("Error in Creating New Number"); return(false); } updateSQL = "update DocumentNumber set TempNo =" + sih.TemporaryNo + " where FYID='" + Main.currentFY + "' and DocumentID='" + sih.DocumentID + "'"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("update", "DocumentNumber", "", updateSQL) + Main.QueryDelimiter; updateSQL = "insert into StockIssueHeader " + "(DocumentID,TemporaryNo,TemporaryDate,DocumentNo,DocumentDate,IssueType,ReferenceNo," + "ReferenceDate,ToLocation,Remarks" + ",CreateUser,CreateTime,ForwarderList,DocumentStatus,Status)" + " values (" + "'" + sih.DocumentID + "'," + sih.TemporaryNo + "," + "'" + sih.TemporaryDate.ToString("yyyy-MM-dd") + "'," + sih.DocumentNo + "," + "'" + sih.DocumentDate.ToString("yyyy-MM-dd") + "'," + sih.IssueType + "," + sih.ReferenceNo + "," + "'" + sih.ReferenceDate.ToString("yyyy-MM-dd") + "'," + "'" + sih.ToLocation + "'," + "'" + sih.Remarks + "'," + "'" + Login.userLoggedIn + "'," + "GETDATE()" + "," + "'" + sih.ForwarderList + "'," + sih.DocumentStatus + "," + sih.status + ")"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("insert", "StockIssueHeader", "", updateSQL) + Main.QueryDelimiter; updateSQL = "Delete from StockIssueDetail where DocumentID='" + sih.DocumentID + "'" + " and TemporaryNo=" + sih.TemporaryNo + " and TemporaryDate='" + sih.TemporaryDate.ToString("yyyy-MM-dd") + "'"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("delete", "StockIssueDetail", "", updateSQL) + Main.QueryDelimiter; foreach (stockissuedetail sid in SIDetails) { updateSQL = "insert into StockIssueDetail " + "(DocumentID,TemporaryNo,TemporaryDate,StockItemID,ModelNo,IssueQuantity,MRNNo,MRNDate,BatchNo,ExpiryDate,SerialNo,PurchaseQuantity," + "PurchasePrice,PurchaseTax,SupplierID,StockReferenceNo) " + "values ('" + sih.DocumentID + "'," + sih.TemporaryNo + "," + "'" + sih.TemporaryDate.ToString("yyyy-MM-dd") + "'," + "'" + sid.StockItemID + "'," + "'" + sid.ModelNo + "'," + sid.IssueQuantity + "," + sid.MRNNo + "," + "'" + sid.MRNDate.ToString("yyyy-MM-dd") + "'," + "'" + sid.BatchNo + "'," + "'" + sid.ExpiryDate.ToString("yyyy-MM-dd") + "'," + "'" + sid.SerialNo + "'," + sid.PurchaseQuantity + "," + sid.PurchasePrice + "," + sid.PurchaseTax + "," + "'" + sid.SupplierID + "'," + +sid.StockReferenceNo + ")"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("insert", "StockIssueDetail", "", updateSQL) + Main.QueryDelimiter; } if (!UpdateTable.UT(utString)) { status = false; } } catch (Exception ex) { status = false; MessageBox.Show("Transaction Exception Occured"); } return(status); }
public Boolean updateSIHeaderAndDetail(stockissueheader sih, stockissueheader prevsih, List <stockissuedetail> SIDetails) { Boolean status = true; string utString = ""; try { string updateSQL = "update StockIssueHeader set IssueType='" + sih.IssueType + "',ReferenceNo='" + sih.ReferenceNo + "',ReferenceDate='" + sih.ReferenceDate.ToString("yyyy-MM-dd") + "', ToLocation='" + sih.ToLocation + "', Remarks='" + sih.Remarks + "', ForwarderList='" + sih.ForwarderList + "'" + " where DocumentID='" + prevsih.DocumentID + "'" + " and TemporaryNo=" + prevsih.TemporaryNo + " and TemporaryDate='" + prevsih.TemporaryDate.ToString("yyyy-MM-dd") + "'"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("update", "StockIssueHeader", "", updateSQL) + Main.QueryDelimiter; updateSQL = "Delete from StockIssueDetail where DocumentID='" + prevsih.DocumentID + "'" + " and TemporaryNo=" + prevsih.TemporaryNo + " and TemporaryDate='" + prevsih.TemporaryDate.ToString("yyyy-MM-dd") + "'"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("delete", "StockIssueDetail", "", updateSQL) + Main.QueryDelimiter; foreach (stockissuedetail sid in SIDetails) { updateSQL = "insert into StockIssueDetail " + "(DocumentID,TemporaryNo,TemporaryDate,StockItemID,ModelNo,IssueQuantity,MRNNo,MRNDate,BatchNo,ExpiryDate,SerialNo,PurchaseQuantity," + "PurchasePrice,PurchaseTax,SupplierID,StockReferenceNo) " + "values ('" + sih.DocumentID + "'," + sih.TemporaryNo + "," + "'" + sih.TemporaryDate.ToString("yyyy-MM-dd") + "'," + "'" + sid.StockItemID + "'," + "'" + sid.ModelNo + "'," + sid.IssueQuantity + "," + sid.MRNNo + "," + "'" + sid.MRNDate.ToString("yyyy-MM-dd") + "'," + "'" + sid.BatchNo + "'," + "'" + sid.ExpiryDate.ToString("yyyy-MM-dd") + "'," + "'" + sid.SerialNo + "'," + sid.PurchaseQuantity + "," + sid.PurchasePrice + "," + sid.PurchaseTax + "," + "'" + sid.SupplierID + "'," + +sid.StockReferenceNo + ")"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("insert", "StockIssueDetail", "", updateSQL) + Main.QueryDelimiter; } if (!UpdateTable.UT(utString)) { status = false; MessageBox.Show("Transaction Exception Occured"); } } catch (Exception ex) { status = false; } return(status); }
public List <stockissueheader> getFilteredSIHeader(string userList, int opt) { stockissueheader sih; List <stockissueheader> SIHeaders = new List <stockissueheader>(); 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," + " IssueType,ReferenceNo,ReferenceDate,ToLocation,ToLocationName," + " Remarks ,CreateUser,ForwardUser,ApproveUser,CreatorName,CreateTime,ForwarderName,ApproverName," + "ForwarderList,status,DocumentStatus " + " from ViewStockIssueHeader" + " where ((ForwardUser='******' and DocumentStatus between 2 and 98) " + " or (CreateUser='******' and DocumentStatus=1)) order by TemporaryDate desc,DocumentID asc,TemporaryNo desc "; string query2 = "select RowID, DocumentID, DocumentName,TemporaryNo,TemporaryDate,DocumentNo,DocumentDate," + " IssueType,ReferenceNo,ReferenceDate,ToLocation,ToLocationName," + " Remarks ,CreateUser,ForwardUser,ApproveUser,CreatorName,CreateTime,ForwarderName,ApproverName," + "ForwarderList,status,DocumentStatus " + " from ViewStockIssueHeader" + " where ((createuser='******' and DocumentStatus between 2 and 98 ) " + " or (ForwarderList like '%" + userList + "%' and DocumentStatus between 2 and 98 and ForwardUser <> '" + Login.userLoggedIn + "'))" + " order by TemporaryDate desc,DocumentID asc,TemporaryNo desc"; string query3 = "select RowID, DocumentID, DocumentName,TemporaryNo,TemporaryDate,DocumentNo,DocumentDate," + " IssueType,ReferenceNo,ReferenceDate,ToLocation,ToLocationName," + " Remarks ,CreateUser,ForwardUser,ApproveUser,CreatorName,CreateTime,ForwarderName,ApproverName," + "ForwarderList,status,DocumentStatus " + " from ViewStockIssueHeader" + " where ((createuser='******'" + " or ForwarderList like '%" + userList + "%'" + " or approveUser='******')" + " and DocumentStatus = 99 and status = 1) order by DocumentDate desc,DocumentID asc,DocumentNo desc"; string query6 = "select RowID, DocumentID, DocumentName,TemporaryNo,TemporaryDate,DocumentNo,DocumentDate," + " IssueType,ReferenceNo,ReferenceDate,ToLocation,ToLocationName," + " Remarks ,CreateUser,ForwardUser,ApproveUser,CreatorName,CreateTime,ForwarderName,ApproverName," + "ForwarderList,status,DocumentStatus " + " from ViewStockIssueHeader" + " where DocumentStatus = 99 and status = 1 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 { sih = new stockissueheader(); sih.RowID = reader.GetInt32(0); sih.DocumentID = reader.GetString(1); sih.DocumentName = reader.GetString(2); sih.TemporaryNo = reader.GetInt32(3); sih.TemporaryDate = reader.GetDateTime(4); sih.DocumentNo = reader.GetInt32(5); sih.DocumentDate = reader.GetDateTime(6); sih.IssueType = reader.GetInt32(7); sih.ReferenceNo = reader.GetInt32(8); sih.ReferenceDate = reader.GetDateTime(9); sih.ToLocation = reader.GetString(10); sih.ToLocationName = reader.GetString(11); sih.Remarks = reader.GetString(12); sih.CreateUser = reader.GetString(13); sih.ForwardUser = reader.GetString(14); sih.ApproveUser = reader.GetString(15); sih.CreatorName = reader.GetString(16); sih.CreateTime = reader.GetDateTime(17); sih.ForwarderName = reader.GetString(18); sih.ApproverName = reader.GetString(19); if (!reader.IsDBNull(20)) { sih.ForwarderList = reader.GetString(20); } else { sih.ForwarderList = ""; } sih.status = reader.GetInt32(21); sih.DocumentStatus = reader.GetInt32(22); SIHeaders.Add(sih); } catch (Exception ex) { } } conn.Close(); } catch (Exception ex) { MessageBox.Show("Error querying StockIssue Header Details"); } return(SIHeaders); }