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