public Boolean ApproveJournalVoucherHeader(JournalVoucherHeader jvh)
{
Boolean status = true;
string utString = "";
try
{
string updateSQL = "update JournalVoucherHeader set DocumentStatus=99, status=1 " +
", ApproveUser='******'" +
", commentStatus='" + jvh.CommentStatus + "'" +
", JournalNo =" + jvh.JournalNo +
//", JournalDate=convert(date, getdate())" +
" where DocumentID='" + jvh.DocumentID + "'" +
" and TemporaryNo=" + jvh.TemporaryNo +
" and TemporaryDate='" + jvh.TemporaryDate.ToString("yyyy-MM-dd") + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("update", "JournalVoucherHeader", "", updateSQL) +
Main.QueryDelimiter;
if (!UpdateTable.UT(utString))
{
status = false;
}
}
catch (Exception)
{
status = false;
}
return(status);
}
public Boolean reverseJournalVoucherHeader(JournalVoucherHeader jvh)
{
Boolean status = true;
string utString = "";
try
{
string updateSQL = "update JournalVoucherHeader set DocumentStatus=" + jvh.DocumentStatus +
", forwardUser='******'" +
", commentStatus='" + jvh.CommentStatus + "'" +
", ForwarderList='" + jvh.ForwarderList + "'" +
" where DocumentID='" + jvh.DocumentID + "'" +
" and TemporaryNo=" + jvh.TemporaryNo +
" and TemporaryDate='" + jvh.TemporaryDate.ToString("yyyy-MM-dd") + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("update", "JournalVoucherHeader", "", updateSQL) +
Main.QueryDelimiter;
if (!UpdateTable.UT(utString))
{
status = false;
}
}
catch (Exception)
{
status = false;
}
return(status);
}
public Boolean validateVoucherHeader(JournalVoucherHeader jvh)
{
Boolean status = true;
try
{
if (jvh.DocumentID.Trim().Length == 0 || jvh.DocumentID == null)
{
return(false);
}
if (jvh.Narration.Trim().Length == 0 || jvh.Narration == null)
{
return(false);
}
if (jvh.JournalDate == null || jvh.JournalDate > DateTime.Today || jvh.JournalDate == DateTime.Parse("1900-01-01"))
{
return(false);
}
}
catch (Exception ex)
{
return(false);
}
return(status);
}
public static List <JournalVoucherDetail> getJournalVoucherDetail(JournalVoucherHeader jvh)
{
JournalVoucherDetail jvd;
string tName = "";
if (jvh.DocumentID == "JOURNALVOUCHER")
{
tName = "ViewJournalVoucher";
}
else
if (jvh.DocumentID == "PJV")
{
tName = "ViewAutoJV";
}
else
if (jvh.DocumentID == "SJV")
{
tName = "ViewAutoJV";
}
List <JournalVoucherDetail> VDetail = new List <JournalVoucherDetail>();
try
{
string query = "";
SqlConnection conn = new SqlConnection(Login.connString);
query = "select RowID,DocumentID,TemporaryNo, TemporaryDate,AccountCode,AccountName,AmountDebit,AmountCredit,SLCode, SLName, SLType " +
" from " + tName +
" where DocumentID='" + jvh.DocumentID + "'" +
" and TemporaryNo=" + jvh.TemporaryNo +
" and TemporaryDate='" + jvh.TemporaryDate.ToString("yyyy-MM-dd") + "'";
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
jvd = new JournalVoucherDetail();
jvd.RowID = reader.GetInt32(0);
jvd.DocumentID = reader.GetString(1);
jvd.TemporaryNo = reader.GetInt32(2);
jvd.TemporaryDate = reader.GetDateTime(3).Date;
jvd.AccountCode = reader.GetString(4);
jvd.AccountName = reader.GetString(5);
jvd.AmountDebit = reader.GetDecimal(6);
jvd.AmountCredit = reader.GetDecimal(7);
jvd.SLCode = reader.IsDBNull(8)?"":reader.GetString(8);
jvd.SLName = reader.IsDBNull(9) ? "" : reader.GetString(9);
jvd.SLType = reader.IsDBNull(10) ? "" : reader.GetString(10);
VDetail.Add(jvd);
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error querying Journal Details");
}
return(VDetail);
}
public static JournalVoucherHeader getJournalHeaderForTrialBalance(JournalVoucherHeader jvhTemp)
{
JournalVoucherHeader jvh = new JournalVoucherHeader();
try
{
string tName = "";
if (jvhTemp.DocumentID == "JOURNALVOUCHER")
{
tName = "JournalVoucherHeader";
}
else
if (jvhTemp.DocumentID == "PJV")
{
tName = "PJVHeader";
}
else
if (jvhTemp.DocumentID == "SJV")
{
tName = "SJVHeader";
}
string query = "select distinct DocumentID,TemporaryNo,TemporaryDate,JournalNo,JournalDate," +
" Narration,status,DocumentStatus " +
" from " + tName +
" where DocumentID = '" + jvhTemp.DocumentID + "' and JournalNo = " + jvhTemp.JournalNo +
" and JournalDate = '" + jvhTemp.JournalDate.ToString("yyyy-MM-dd") + "'";
SqlConnection conn = new SqlConnection(Login.connString);
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
try
{
jvh.DocumentID = reader.GetString(0);
jvh.TemporaryNo = reader.GetInt32(1);
jvh.TemporaryDate = reader.GetDateTime(2);
jvh.JournalNo = reader.GetInt32(3);
jvh.JournalDate = reader.GetDateTime(4);
jvh.Narration = reader.GetString(5);
jvh.status = reader.GetInt32(6);
jvh.DocumentStatus = reader.GetInt32(7);
}
catch (Exception ex)
{
}
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error querying Journal Header Details");
}
return(jvh);
}
public Boolean updateJournalHeaderAndDetail(JournalVoucherHeader jvh, JournalVoucherHeader prevjvh, List <JournalVoucherDetail> JVDetails)
{
Boolean status = true;
string utString = "";
try
{
string updateSQL = "update JournalVoucherHeader set Narration='" + jvh.Narration +
"', Comments='" + jvh.Comments +
"', JournalDate='" + jvh.JournalDate.ToString("yyyy-MM-dd") +
"', CommentStatus='" + jvh.CommentStatus +
"', ForwarderList='" + jvh.ForwarderList + "'" +
" where DocumentID='" + prevjvh.DocumentID + "'" +
" and TemporaryNo=" + prevjvh.TemporaryNo +
" and TemporaryDate='" + prevjvh.TemporaryDate.ToString("yyyy-MM-dd") + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("update", "JournalVoucherHeader", "", updateSQL) +
Main.QueryDelimiter;
updateSQL = "Delete from JournalVoucherDetail where DocumentID='" + prevjvh.DocumentID + "'" +
" and TemporaryNo=" + prevjvh.TemporaryNo +
" and TemporaryDate='" + prevjvh.TemporaryDate.ToString("yyyy-MM-dd") + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("delete", "JournalVoucherDetail", "", updateSQL) +
Main.QueryDelimiter;
foreach (JournalVoucherDetail jvd in JVDetails)
{
updateSQL = "insert into JournalVoucherDetail " +
"(DocumentID,TemporaryNo,TemporaryDate,AccountCode,AmountDebit,AmountCredit,SLCode,SLType) " +
"values ('" + jvd.DocumentID + "'," +
jvd.TemporaryNo + "," +
"'" + jvd.TemporaryDate.ToString("yyyy-MM-dd") + "'," +
"'" + jvd.AccountCode + "'," +
jvd.AmountDebit + "," +
jvd.AmountCredit + ",'" + jvd.SLCode + "','" + jvd.SLType + "')";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("insert", "JournalVoucherDetail", "", updateSQL) +
Main.QueryDelimiter;
}
if (!UpdateTable.UT(utString))
{
status = false;
MessageBox.Show("Transaction Exception Occured");
}
}
catch (Exception ex)
{
status = false;
}
return(status);
}
public Boolean InsertJournalHeaderAndDetail(JournalVoucherHeader jvh, List <JournalVoucherDetail> JVDetails)
{
Boolean status = true;
string utString = "";
string updateSQL = "";
try
{
jvh.TemporaryNo = DocumentNumberDB.getNumber(jvh.DocumentID, 1);
if (jvh.TemporaryNo <= 0)
{
MessageBox.Show("Error in Creating New Number");
return(false);
}
updateSQL = "update DocumentNumber set TempNo =" + jvh.TemporaryNo +
" where FYID='" + Main.currentFY + "' and DocumentID='" + jvh.DocumentID + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("update", "DocumentNumber", "", updateSQL) +
Main.QueryDelimiter;
updateSQL = "insert into JournalVoucherHeader " +
"(DocumentID,TemporaryNo,TemporaryDate,JournalNo,JournalDate,Narration," +
"Comments,CommentStatus,CreateUser,CreateTime,ForwarderList,DocumentStatus,Status)" +
" values (" +
"'" + jvh.DocumentID + "'," +
jvh.TemporaryNo + "," +
"'" + jvh.TemporaryDate.ToString("yyyy-MM-dd") + "'," +
jvh.JournalNo + "," +
"'" + jvh.JournalDate.ToString("yyyy-MM-dd") + "'," +
"'" + jvh.Narration + "'," +
"'" + jvh.Comments + "'," +
"'" + jvh.CommentStatus + "'," +
"'" + Login.userLoggedIn + "'," +
"GETDATE()" + "," +
"'" + jvh.ForwarderList + "'," +
jvh.DocumentStatus + "," +
jvh.status + ")";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("insert", "JournalVoucherHeader", "", updateSQL) +
Main.QueryDelimiter;
updateSQL = "Delete from JournalVoucherDetail where DocumentID='" + jvh.DocumentID + "'" +
" and TemporaryNo=" + jvh.TemporaryNo +
" and TemporaryDate='" + jvh.TemporaryDate.ToString("yyyy-MM-dd") + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("delete", "JournalVoucherDetail", "", updateSQL) +
Main.QueryDelimiter;
foreach (JournalVoucherDetail jvd in JVDetails)
{
updateSQL = "insert into JournalVoucherDetail " +
"(DocumentID,TemporaryNo,TemporaryDate,AccountCode,AmountDebit,AmountCredit,SLCode,SLType) " +
"values ('" + jvd.DocumentID + "'," +
jvh.TemporaryNo + "," +
"'" + jvd.TemporaryDate.ToString("yyyy-MM-dd") + "'," +
"'" + jvd.AccountCode + "'," +
jvd.AmountDebit + "," +
jvd.AmountCredit + ",'" + jvd.SLCode + "','" + jvd.SLType + "')";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("insert", "JournalVoucherDetail", "", updateSQL) +
Main.QueryDelimiter;
}
if (!UpdateTable.UT(utString))
{
status = false;
}
}
catch (Exception ex)
{
status = false;
MessageBox.Show("Transaction Exception Occured");
}
return(status);
}
public List <JournalVoucherHeader> getFilteredJournalHeader(string userList, int opt, string userCommentStatusString)
{
JournalVoucherHeader jvh;
List <JournalVoucherHeader> JVHeaders = new List <JournalVoucherHeader>();
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,JournalNo,JournalDate," +
" Narration,CreateUser,ForwardUser,ApproveUser,CreatorName,CreateTime,ForwarderName,ApproverName,ForwarderList,status,DocumentStatus,CommentStatus " +
" from ViewJournalVoucher" +
" 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 TemporaryDate desc,DocumentID asc,TemporaryNo desc";
string query2 = "select distinct DocumentID,TemporaryNo,TemporaryDate,JournalNo,JournalDate," +
" Narration,CreateUser,ForwardUser,ApproveUser,CreatorName,CreateTime,ForwarderName,ApproverName,ForwarderList,status,DocumentStatus,CommentStatus " +
" from ViewJournalVoucher" +
" 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 distinct DocumentID,TemporaryNo,TemporaryDate,JournalNo,JournalDate," +
" Narration,CreateUser,ForwardUser,ApproveUser,CreatorName,CreateTime,ForwarderName,ApproverName,ForwarderList,status,DocumentStatus,CommentStatus " +
" from ViewJournalVoucher" +
" where ((createuser='******'" +
" or ForwarderList like '%" + userList + "%'" +
" or commentStatus like '%" + acStr + "%'" +
" or approveUser='******')" +
" and DocumentStatus = 99) order by JournalDate desc,DocumentID asc,JournalNo desc";
string query6 = "select distinct DocumentID,TemporaryNo,TemporaryDate,JournalNo,JournalDate," +
" Narration,CreateUser,ForwardUser,ApproveUser,CreatorName,CreateTime,ForwarderName,ApproverName,ForwarderList,status,DocumentStatus,CommentStatus " +
" from ViewJournalVoucher" +
" where DocumentStatus = 99 order by JournalDate desc,DocumentID asc,JournalNo 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
{
jvh = new JournalVoucherHeader();
jvh.DocumentID = reader.GetString(0);
jvh.TemporaryNo = reader.GetInt32(1);
jvh.TemporaryDate = reader.GetDateTime(2);
jvh.JournalNo = reader.GetInt32(3);
jvh.JournalDate = reader.GetDateTime(4);
jvh.Narration = reader.GetString(5);
jvh.CreateUser = reader.GetString(6);
jvh.ForwardUser = reader.GetString(7);
jvh.ApproveUser = reader.GetString(8);
jvh.CreatorName = reader.GetString(9);
jvh.CreateTime = reader.GetDateTime(10);
jvh.ForwarderName = reader.GetString(11);
jvh.ApproverName = reader.GetString(12);
if (!reader.IsDBNull(13))
{
jvh.ForwarderList = reader.GetString(13);
}
else
{
jvh.ForwarderList = "";
}
jvh.status = reader.GetInt32(14);
jvh.DocumentStatus = reader.GetInt32(15);
if (!reader.IsDBNull(16))
{
jvh.CommentStatus = reader.GetString(16);
}
else
{
jvh.CommentStatus = "";
}
JVHeaders.Add(jvh);
}
catch (Exception ex)
{
}
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error querying Journal Header Details");
}
return(JVHeaders);
}