public Boolean updatejvaccmapping(jvaccmapping doc, jvaccmapping prevdoc)
{
Boolean status = true;
string utString = "";
try
{
string updateSQL = "update AutoJVAccountCodes set AccountCodeDebit='" + doc.AccountCodeDebit + "'," +
"AccountCodeCredit='" + doc.AccountCodeCredit + "'," +
" Status=" + doc.Status +
" where RowID=" + prevdoc.RowID;
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("update", "AutoJVAccountCodes", "", 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 static jvaccmapping getjvaccmappingPerDocument(string jvname, string docid)
{
jvaccmapping jvaccmappingrec = new jvaccmapping();
try
{
SqlConnection conn = new SqlConnection(Login.connString);
string query = "select RowID,JVName,DocumentID,AccountCodeDebit,AccountCodeCredit,Status " +
" from AutoJVAccountCodes " +
" where JVName = '" + jvname + "' and DocumentID = '" + docid + "' and Status=1";
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
jvaccmappingrec = new jvaccmapping();
jvaccmappingrec.RowID = reader.GetInt32(0);
jvaccmappingrec.JVName = reader.GetString(1);
jvaccmappingrec.DocumentID = reader.GetString(2);
jvaccmappingrec.AccountCodeDebit = reader.GetString(3);
jvaccmappingrec.AccountCodeCredit = reader.GetString(4);
jvaccmappingrec.Status = reader.GetInt32(5);
}
conn.Close();
}
catch (Exception)
{
MessageBox.Show(System.Reflection.MethodBase.GetCurrentMethod().Name + "() : Error");
}
return(jvaccmappingrec);
}
public Boolean validateDocument(jvaccmapping doc)
{
Boolean status = true;
try
{
if (doc.JVName == null || doc.JVName.Trim().Length == 0)
{
return(false);
}
if (doc.DocumentID == null || doc.DocumentID.Trim().Length == 0)
{
return(false);
}
if (doc.AccountCodeDebit == null || doc.AccountCodeDebit.Trim().Length == 0)
{
return(false);
}
if (doc.AccountCodeCredit == null || doc.AccountCodeCredit.Trim().Length == 0 ||
doc.AccountCodeCredit.Trim() == doc.AccountCodeDebit.Trim())
{
return(false);
}
}
catch (Exception ex)
{
MessageBox.Show(this.ToString() + "-" + System.Reflection.MethodBase.GetCurrentMethod().Name + "() : Error");
}
return(status);
}
public Boolean insertAutoJVAccountCodes(jvaccmapping doc)
{
Boolean status = true;
string utString = "";
try
{
DateTime cdt = DateTime.Now;
string updateSQL = "insert into AutoJVAccountCodes (JVName,DocumentID,AccountCodeDebit,AccountCodeCredit,Status)" +
" values (" +
"'" + doc.JVName + "'," +
"'" + doc.DocumentID + "'," +
"'" + doc.AccountCodeDebit + "'," +
"'" + doc.AccountCodeCredit + "'," +
doc.Status + ")";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("insert", "AutoJVAccountCodes", "", 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 List <jvaccmapping> getjvaccmappingList()
{
jvaccmapping jvaccmappingrec;
List <jvaccmapping> jvaccmappingsList = new List <jvaccmapping>();
try
{
SqlConnection conn = new SqlConnection(Login.connString);
string query = "select a.RowID,a.JVName,a.DocumentID,d.DocumentName,a.AccountCodeDebit,b.Name,a.AccountCodeCredit,c.Name,a.Status " +
" from AutoJVAccountCodes a, AccountCode b,AccountCode c,Document d " +
" where a.AccountCodeDebit = b.AccountCode and a.AccountCodeCredit = c.AccountCode and a.DocumentID = d.DocumentID" +
" order by a.DocumentID";
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
jvaccmappingrec = new jvaccmapping();
jvaccmappingrec.RowID = reader.GetInt32(0);
jvaccmappingrec.JVName = reader.GetString(1);
jvaccmappingrec.DocumentID = reader.GetString(2);
jvaccmappingrec.DocumentName = reader.GetString(3);
jvaccmappingrec.AccountCodeDebit = reader.GetString(4);
jvaccmappingrec.AccountNameDebit = reader.GetString(5);
jvaccmappingrec.AccountCodeCredit = reader.GetString(6);
jvaccmappingrec.AccountNameCredit = reader.GetString(7);
jvaccmappingrec.Status = reader.GetInt32(8);
jvaccmappingsList.Add(jvaccmappingrec);
}
conn.Close();
}
catch (Exception)
{
MessageBox.Show(this.ToString() + "-" + System.Reflection.MethodBase.GetCurrentMethod().Name + "() : Error");
}
return(jvaccmappingsList);
}
public static Boolean InsertSJVHeaderAndDetail(SJVHeader jvh)
{
//call when invoice inward is saved
//Invoice types MRN,Work Order, PO General
Boolean status = true;
string utString = "";
string updateSQL = "";
try
{
updateSQL = "Delete from SJVHeader where InvDocumentID='" + jvh.InvDocumentID + "'" +
" and InvTempNo=" + jvh.InvTempNo +
" and InvTempDate='" + jvh.InvTempDate.ToString("yyyy-MM-dd") + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
updateSQL = "Delete from SJVDetail where InvDocumentID='" + jvh.InvDocumentID + "'" +
" and InvTempNo=" + jvh.InvTempNo +
" and InvTempDate='" + jvh.InvTempDate.ToString("yyyy-MM-dd") + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
//--
updateSQL = "insert into SJVHeader " +
"(DocumentID,TemporaryNo,TemporaryDate,JournalNo,JournalDate,Narration," +
"InvDocumentID,InvTempNo,InvTempDate,InvReferenceNo," +
"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.InvDocumentID + "'," +
jvh.InvTempNo + "," +
"'" + jvh.InvTempDate.ToString("yyyy-MM-dd") + "'," +
jvh.InvReferenceNo + "," +
"'" + jvh.Comments + "'," +
"'" + jvh.CommentStatus + "'," +
"'" + Login.userLoggedIn + "'," +
"GETDATE()" + "," +
"'" + jvh.ForwarderList + "'," +
jvh.DocumentStatus + "," +
jvh.status + ")";
utString = utString + updateSQL + Main.QueryDelimiter;
//////utString = utString +
//////ActivityLogDB.PrepareActivityLogQquerString("insert", "SJVHeader", "", updateSQL) +
//////Main.QueryDelimiter;
jvaccmapping jvAcc = AutoJVAccMappingDB.getjvaccmappingPerDocument(jvh.DocumentID, jvh.InvDocumentID);
if (jvAcc.AccountCodeDebit == null || jvAcc.AccountCodeDebit.Trim().Length == 0 || jvAcc.AccountCodeCredit == null || jvAcc.AccountCodeCredit.Length == 0)
{
MessageBox.Show("Debit and credit account not mapped for this document.\n Failed to update sales journal.");
return(false);
}
updateSQL = "insert into SJVDetail " +
"(DocumentID,TemporaryNo,TemporaryDate,AccountCode,AmountDebit,AmountCredit,SLCode,SLType,INVDocumentID,InvTempNo,InvTempDate) " +
"values ('" + jvh.DocumentID + "'," +
jvh.TemporaryNo + "," +
"'" + jvh.TemporaryDate.ToString("yyyy-MM-dd") + "'," +
/// "'11111111110248'," +//sundry creditors // credit
"'" + jvAcc.AccountCodeDebit + "'," +
(jvh.Amtount) + "," +
0 + ",'" + jvh.Customer + "','Party','" + jvh.InvDocumentID + "'," +
jvh.InvTempNo + "," +
"'" + jvh.InvTempDate.ToString("yyyy-MM-dd") + "')";
utString = utString + updateSQL + Main.QueryDelimiter;
//////utString = utString +
//////ActivityLogDB.PrepareActivityLogQquerString("insert", "SJVDetail", "", updateSQL) +
//////Main.QueryDelimiter;
//--
updateSQL = "insert into SJVDetail " +
"(DocumentID,TemporaryNo,TemporaryDate,AccountCode,AmountDebit,AmountCredit,SLCode,SLType,INVDocumentID,InvTempNo,InvTempDate) " +
"values ('" + jvh.DocumentID + "'," +
jvh.TemporaryNo + "," +
"'" + jvh.TemporaryDate.ToString("yyyy-MM-dd") + "'," +
//"'11111111110557'," + //purchase ac
"'" + jvAcc.AccountCodeCredit + "'," +
0 + "," +
(jvh.Amtount - jvh.TaxAmount) + ",'','','" + jvh.InvDocumentID + "'," +
jvh.InvTempNo + "," +
"'" + jvh.InvTempDate.ToString("yyyy-MM-dd") + "')";
utString = utString + updateSQL + Main.QueryDelimiter;
//////utString = utString +
//////ActivityLogDB.PrepareActivityLogQquerString("insert", "SJVDetail", "", updateSQL) +
//////Main.QueryDelimiter;
//Updating SJV references in INvoice out
updateSQL = "update InvoiceOutHeader set SJVTNo=" + jvh.TemporaryNo +
", SJVTDate='" + jvh.TemporaryDate.ToString("yyyy-MM-dd") + "'" +
", SJVNo='" + jvh.JournalNo + "'" +
", SJVDate='" + jvh.JournalDate.ToString("yyyy-MM-dd") + "'" +
" where DocumentID='" + jvh.InvDocumentID + "'" +
" and TemporaryNo=" + jvh.InvTempNo +
" and TemporaryDate='" + jvh.InvTempDate.ToString("yyyy-MM-dd") + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
////utString = utString +
////ActivityLogDB.PrepareActivityLogQquerString("update", "JournalVoucherHeader", "", updateSQL) +
////Main.QueryDelimiter;
try
{
TaxItemDB taxitemdb = new TaxItemDB();
List <taxitem> TaxItems = taxitemdb.getTaxItems();
string[] lst1 = jvh.TaxDetail.Split('\n');
for (int i = 0; i < lst1.Length - 1; i++)
{
string taxCode = "Not Found";
string[] lst2 = lst1[i].Split('-');
int ind = searchList(TaxItems, lst2[0]);
if (ind >= 0)
{
taxCode = TaxItems[ind].AccountCodeOUT;
}
updateSQL = "insert into SJVDetail " +
"(DocumentID,TemporaryNo,TemporaryDate,AccountCode,AmountDebit,AmountCredit,SLCode,SLType,INVDocumentID,InvTempNo,InvTempDate) " +
"values ('" + jvh.DocumentID + "'," +
jvh.TemporaryNo + "," +
"'" + jvh.TemporaryDate.ToString("yyyy-MM-dd") + "'," +
"'" + taxCode + "'," +
0 + "," +
lst2[1] + ",'','','" + jvh.InvDocumentID + "'," +
jvh.InvTempNo + "," +
"'" + jvh.InvTempDate.ToString("yyyy-MM-dd") + "')";
utString = utString + updateSQL + Main.QueryDelimiter;
}
}
catch (Exception ex)
{
MessageBox.Show("InsertSJVHeaderAndDetail() : Error creating tax entries - " + ex.ToString());
}
if (!UpdateTable.UT(utString))
{
status = false;
}
}
catch (Exception ex)
{
status = false;
MessageBox.Show("Transaction Exception Occured");
}
return(status);
}
