public Boolean ApproveCreditNoteHeader(CreditNoteHeader cnh) { Boolean status = true; string utString = ""; try { string updateSQL = "update CreditNoteHeader set DocumentStatus=99, status=1 " + ", ApproveUser='******'" + ", commentStatus='" + cnh.CommentStatus + "'" + ", CreditNoteNo =" + cnh.CreditNoteNo + ", CreditNoteDate=convert(date, getdate())" + " where DocumentID='" + cnh.DocumentID + "'" + " and TemporaryNo=" + cnh.TemporaryNo + " and TemporaryDate='" + cnh.TemporaryDate.ToString("yyyy-MM-dd") + "'"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("update", "CreditNoteHeader", "", updateSQL) + Main.QueryDelimiter; if (!UpdateTable.UT(utString)) { status = false; } } catch (Exception ex) { MessageBox.Show(this.ToString() + "-" + System.Reflection.MethodBase.GetCurrentMethod().Name + "() : Error"); status = false; } return(status); }
public Boolean validateCreditNoteHeader(CreditNoteHeader cnh) { Boolean status = true; try { if (cnh.DocumentID.Trim().Length == 0 || cnh.DocumentID == null) { return(false); } if (cnh.AccountCredit.Trim().Length == 0 || cnh.AccountCredit == null) { return(false); } if (cnh.SLCode.Trim().Length == 0 || cnh.SLCode == null) { return(false); } if (cnh.AmountCredit == 0) { return(false); } if (cnh.Narration.Trim().Length == 0 || cnh.Narration == null) { return(false); } } catch (Exception ex) { MessageBox.Show(this.ToString() + "-" + System.Reflection.MethodBase.GetCurrentMethod().Name + "() : Error"); return(false); } return(status); }
public Boolean updateCreditHeaderAndDetail(CreditNoteHeader cnh, CreditNoteHeader prevcnh, List <CreditNoteDetail> CNDetails) { Boolean status = true; string utString = ""; try { string updateSQL = "update CreditNoteHeader set AccountCredit='" + cnh.AccountCredit + "', SLCode='" + cnh.SLCode + "', SLType='" + cnh.SLType + "', RefDocumentID='" + cnh.ReferenceDocID + "', RefNo='" + cnh.ReferenceNo + "', RefDate='" + cnh.ReferenceDate.ToString("yyyy-MM-dd") + "', AmountCredit=" + cnh.AmountCredit + ", Narration='" + cnh.Narration + "', Comments='" + cnh.Comments + "', CommentStatus='" + cnh.CommentStatus + "', ForwarderList='" + cnh.ForwarderList + "'" + " where DocumentID='" + prevcnh.DocumentID + "'" + " and TemporaryNo=" + prevcnh.TemporaryNo + " and TemporaryDate='" + prevcnh.TemporaryDate.ToString("yyyy-MM-dd") + "'"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("update", "CreditNoteHeader", "", updateSQL) + Main.QueryDelimiter; updateSQL = "Delete from CreditNoteDetail where DocumentID='" + cnh.DocumentID + "'" + " and TemporaryNo=" + cnh.TemporaryNo + " and TemporaryDate='" + cnh.TemporaryDate.ToString("yyyy-MM-dd") + "'"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("delete", "CreditNoteDetail", "", updateSQL) + Main.QueryDelimiter; foreach (CreditNoteDetail cnd in CNDetails) { updateSQL = "insert into CreditNoteDetail " + "(DocumentID,TemporaryNo,TemporaryDate,AccountDebit,AmountDebit) " + "values ('" + cnd.DocumentID + "'," + cnd.TemporaryNo + "," + "'" + cnd.TemporaryDate.ToString("yyyy-MM-dd") + "'," + "'" + cnd.AccountDebit + "'," + cnd.AmountDebit + ")"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("insert", "CreditNoteDetail", "", updateSQL) + Main.QueryDelimiter; } if (!UpdateTable.UT(utString)) { status = false; MessageBox.Show("Transaction Exception Occured"); } } catch (Exception ex) { status = false; } return(status); }
public static List <CreditNoteDetail> getCreditNoteDetail(CreditNoteHeader cnh) { CreditNoteDetail cnd; List <CreditNoteDetail> CNDetail = new List <CreditNoteDetail>(); try { string query = ""; SqlConnection conn = new SqlConnection(Login.connString); query = "select RowID,DocumentID,TemporaryNo, TemporaryDate,AccountDebit,AccountDebitName,AmountDebit " + " from ViewCreditNote " + "where DocumentID='" + cnh.DocumentID + "'" + " and TemporaryNo=" + cnh.TemporaryNo + " and TemporaryDate='" + cnh.TemporaryDate.ToString("yyyy-MM-dd") + "'"; SqlCommand cmd = new SqlCommand(query, conn); conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { cnd = new CreditNoteDetail(); cnd.RowID = reader.GetInt32(0); cnd.DocumentID = reader.GetString(1); cnd.TemporaryNo = reader.GetInt32(2); cnd.TemporaryDate = reader.GetDateTime(3).Date; cnd.AccountDebit = reader.GetString(4); cnd.AccountDebitName = reader.GetString(5); cnd.AmountDebit = reader.GetDecimal(6); CNDetail.Add(cnd); } conn.Close(); } catch (Exception ex) { MessageBox.Show(System.Reflection.MethodBase.GetCurrentMethod().Name + "() : Error"); } return(CNDetail); }
public Boolean InsertCreditHeaderAndDetail(CreditNoteHeader cnh, List <CreditNoteDetail> CNDetails) { Boolean status = true; string utString = ""; string updateSQL = ""; try { cnh.TemporaryNo = DocumentNumberDB.getNumber(cnh.DocumentID, 1); if (cnh.TemporaryNo <= 0) { MessageBox.Show("Error in Creating New Number"); return(false); } updateSQL = "update DocumentNumber set TempNo =" + cnh.TemporaryNo + " where FYID='" + Main.currentFY + "' and DocumentID='" + cnh.DocumentID + "'"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("update", "DocumentNumber", "", updateSQL) + Main.QueryDelimiter; updateSQL = "insert into CreditNoteHeader " + "(DocumentID,TemporaryNo,TemporaryDate,CreditNoteNo,CreditNoteDate,AccountCredit,AmountCredit,SLCode,SLType, RefNo, RefDate,Narration," + "Comments,CommentStatus,CreateUser,CreateTime,ForwarderList,DocumentStatus,Status)" + " values (" + "'" + cnh.DocumentID + "'," + cnh.TemporaryNo + "," + "'" + cnh.TemporaryDate.ToString("yyyy-MM-dd") + "'," + cnh.CreditNoteNo + "," + "'" + cnh.CreditNoteDate.ToString("yyyy-MM-dd") + "'," + "'" + cnh.AccountCredit + "'," + cnh.AmountCredit + "," + "'" + cnh.SLCode + "'," + "'" + cnh.SLType + "'," + "'" + cnh.ReferenceNo + "'," + "'" + cnh.ReferenceDate + "'," + "'" + cnh.Narration + "'," + "'" + cnh.Comments + "'," + "'" + cnh.CommentStatus + "'," + "'" + Login.userLoggedIn + "'," + "GETDATE()" + "," + "'" + cnh.ForwarderList + "'," + cnh.DocumentStatus + "," + cnh.status + ")"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("insert", "CreditNoteHeader", "", updateSQL) + Main.QueryDelimiter; updateSQL = "Delete from CreditNoteDetail where DocumentID='" + cnh.DocumentID + "'" + " and TemporaryNo=" + cnh.TemporaryNo + " and TemporaryDate='" + cnh.TemporaryDate.ToString("yyyy-MM-dd") + "'"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("delete", "CreditNoteDetail", "", updateSQL) + Main.QueryDelimiter; foreach (CreditNoteDetail cnd in CNDetails) { updateSQL = "insert into CreditNoteDetail " + "(DocumentID,TemporaryNo,TemporaryDate,AccountDebit,AmountDebit) " + "values ('" + cnd.DocumentID + "'," + cnh.TemporaryNo + "," + "'" + cnd.TemporaryDate.ToString("yyyy-MM-dd") + "'," + "'" + cnd.AccountDebit + "'," + cnd.AmountDebit + ")"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("insert", "CreditNoteDetail", "", updateSQL) + Main.QueryDelimiter; } if (!UpdateTable.UT(utString)) { status = false; } } catch (Exception ex) { status = false; MessageBox.Show("Transaction Exception Occured"); } return(status); }
public List <CreditNoteHeader> getFilteredCreditNoteHeader(string userList, int opt, string userCommentStatusString) { CreditNoteHeader cnh; List <CreditNoteHeader> CNHeaders = new List <CreditNoteHeader>(); 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 distinct DocumentID,TemporaryNo,TemporaryDate,CreditNoteNo,CreditNoteDate," + " AccountCredit,AccountCreditName, AmountCredit,SLCode,SLName, RefNo, RefDate,Narration,CreateUser,ForwardUser," + "ApproveUser,CreatorName,CreateTime,ForwarderName,ApproverName,ForwarderList,status,DocumentStatus,CommentStatus,SLType " + " from ViewCreditNote" + " where ((ForwardUser='******' and DocumentStatus between 2 and 98) " + " or (CreateUser='******' and DocumentStatus=1)" + " or (CommentStatus like '%" + userCommentStatusString + "%' and DocumentStatus between 1 and 98))"; string query2 = "select distinct DocumentID,TemporaryNo,TemporaryDate,CreditNoteNo,CreditNoteDate," + " AccountCredit,AccountCreditName, AmountCredit,SLCode,SLName, RefNo, RefDate,Narration,CreateUser,ForwardUser," + "ApproveUser,CreatorName,CreateTime,ForwarderName,ApproverName,ForwarderList,status,DocumentStatus,CommentStatus,SLType " + " from ViewCreditNote" + " 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))"; string query3 = "select distinct DocumentID,TemporaryNo,TemporaryDate,CreditNoteNo,CreditNoteDate," + " AccountCredit,AccountCreditName, AmountCredit,SLCode,SLName, RefNo, RefDate,Narration,CreateUser,ForwardUser," + "ApproveUser,CreatorName,CreateTime,ForwarderName,ApproverName,ForwarderList,status,DocumentStatus,CommentStatus,SLType " + " from ViewCreditNote" + " where ((createuser='******'" + " or ForwarderList like '%" + userList + "%'" + " or commentStatus like '%" + acStr + "%'" + " or approveUser='******')" + " and DocumentStatus = 99) order by CreditNoteDate desc,DocumentID asc,CreditNoteNo desc"; string query6 = "select distinct DocumentID,TemporaryNo,TemporaryDate,CreditNoteNo,CreditNoteDate," + " AccountCredit,AccountCreditName, AmountCredit,SLCode,SLName, RefNo, RefDate,Narration,CreateUser,ForwardUser," + "ApproveUser,CreatorName,CreateTime,ForwarderName,ApproverName,ForwarderList,status,DocumentStatus,CommentStatus,SLType " + " from ViewCreditNote" + " where DocumentStatus = 99 order by CreditNoteDate desc,DocumentID asc,CreditNoteNo 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 { cnh = new CreditNoteHeader(); cnh.DocumentID = reader.GetString(0); cnh.TemporaryNo = reader.GetInt32(1); cnh.TemporaryDate = reader.GetDateTime(2); cnh.CreditNoteNo = reader.GetInt32(3); cnh.CreditNoteDate = reader.GetDateTime(4); cnh.AccountCredit = reader.GetString(5); cnh.AccountCreditName = reader.GetString(6); cnh.AmountCredit = reader.GetDecimal(7); cnh.SLCode = reader.GetString(8); cnh.SLName = reader.GetString(9); cnh.ReferenceNo = reader.GetString(10); cnh.ReferenceDate = reader.GetDateTime(11); cnh.Narration = reader.GetString(12); cnh.CreateUser = reader.GetString(13); cnh.ForwardUser = reader.GetString(14); cnh.ApproveUser = reader.GetString(15); cnh.CreatorName = reader.GetString(16); cnh.CreateTime = reader.GetDateTime(17); cnh.ForwarderName = reader.GetString(18); cnh.ApproverName = reader.GetString(19); cnh.ForwarderList = reader.IsDBNull(20) ? "" : reader.GetString(20); cnh.status = reader.GetInt32(21); cnh.DocumentStatus = reader.GetInt32(22); cnh.CommentStatus = reader.IsDBNull(23)?"":reader.GetString(23); cnh.SLType = reader.IsDBNull(24) ? "" : reader.GetString(24); CNHeaders.Add(cnh); } catch (Exception ex) { } } conn.Close(); } catch (Exception ex) { MessageBox.Show(this.ToString() + "-" + System.Reflection.MethodBase.GetCurrentMethod().Name + "() : Error"); } return(CNHeaders); }