public Boolean FinalizeCustomerOB(CustomerOBheader Acchr)
{
Boolean status = true;
string utString = "";
try
{
string updateSQL = "update CustomerOBheader set Status = 1" +
" where DocumentNo=" + Acchr.DocumentNo +
" and DocumentDate='" + Acchr.DocumentDate.ToString("yyyy-MM-dd") + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("update", "CustomerOBheader", "", 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 insertCustomerOBHeader(CustomerOBheader Acchr)
//{
// Boolean status = true;
// string utString = "";
// try
// {
// string updateSQL = "insert into CustomerOBHeader " +
// " (DocumentID,FYID,DocumentDate,DocumentNo,Status," +
// "CreateUser,CreateTime)" +
// "values (" +
// "'" + Acchr.DocumentID + "'," +
// Acchr.FYID + "," +
// "'" + Acchr.DocumentDate.ToString("yyyy-MM-dd") + "'," +
// "'" + Acchr.DocumentNo + "','"+Acchr.Status+"'," +
// "'" + Login.userLoggedIn + "'," +
// "GETDATE())";
// //"'" + pheader.ForwardUser + "'," +
// //"'" + pheader.ApproveUser + "'," +
// utString = utString + updateSQL + Main.QueryDelimiter;
// utString = utString +
// ActivityLogDB.PrepareActivityLogQquerString("insert", "CustomerOBHeader", "", 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 UpdateCustomerDetail(List<CustomerOBdetail> AccDetail, CustomerOBheader Acchdr)
//{
// Boolean status = true;
// string utString = "";
// try
// {
// string updateSQL = "Delete from CustomerOBDetail where DocumentID='" + Acchdr.DocumentID + "'" +
// " and DocumentNo=" + Acchdr.DocumentNo +
// " and DocumentDate='" + Acchdr.DocumentDate.ToString("yyyy-MM-dd") + "'";
// utString = utString + updateSQL + Main.QueryDelimiter;
// utString = utString +
// ActivityLogDB.PrepareActivityLogQquerString("delete", "CustomerOBDetail", "", updateSQL) +
// Main.QueryDelimiter;
// foreach (CustomerOBdetail acc in AccDetail)
// {
// updateSQL = "insert into CustomerOBDetail " +
// "(DocumentID,DocumentNo,DocumentDate,SerialNo,CustomerID,BalanceDebit,BalanceCredit) " +
// "values ('" + acc.DocumentID + "'," +
// acc.DocumentNo + "," +
// "'" + acc.DocumentDate.ToString("yyyy-MM-dd") + "'," +
// "'" + acc.SerialNo + "','" +
// acc.AccountCode + "'," +
// "'" + acc.BalanceDebit + "'," +
// "'" + acc.BalanceCredit + "')";
// utString = utString + updateSQL + Main.QueryDelimiter;
// utString = utString +
// ActivityLogDB.PrepareActivityLogQquerString("insert", "CustomerOBDetail", "", 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 validateAccOB(CustomerOBheader acchdr)
{
Boolean status = true;
try
{
if (acchdr.DocumentID.Trim().Length == 0 || acchdr.DocumentID == null)
{
return(false);
}
//if (acchdr.DocumentNo == 0)
//{
// return false;
//}
//if (acchdr.DocumentDate == null)
//{
// return false;
//}
if (acchdr.FYID.Trim().Length == 0 || acchdr.FYID == null)
{
return(false);
}
}
catch (Exception ex)
{
MessageBox.Show(this.ToString() + "-" + System.Reflection.MethodBase.GetCurrentMethod().Name + "() : Error");
return(false);
}
return(status);
}
public static List <CustomerOBheader> getCustomerOblist(int opt)
{
CustomerOBheader Alist;
List <CustomerOBheader> AccList = new List <CustomerOBheader>();
try
{
string query1 = "select distinct DocumentID,DocumentNo,DocumentDate,FYID, " +
"Status, Createuser,CreatorName, CreateTime " +
"from ViewCustomerOB where (Createuser='******' and Status = 0 )";
string query2 = "select distinct DocumentID,DocumentNo,DocumentDate,FYID, " +
"Status, Createuser,CreatorName, CreateTime " +
"from ViewCustomerOB where Status = 1";
SqlConnection conn = new SqlConnection(Login.connString);
string query = "";
switch (opt)
{
case 1:
query = query1;
break;
case 2:
query = query2;
break;
case 6:
query = query2;
break;
default:
query = "";
break;
}
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Alist = new CustomerOBheader();
Alist.DocumentID = reader.GetString(0);
Alist.DocumentNo = reader.GetInt32(1);
Alist.DocumentDate = reader.GetDateTime(2);
Alist.FYID = reader.GetString(3);
Alist.Status = reader.GetInt32(4);
Alist.CreateUser = reader.GetString(5);
Alist.CreatorName = reader.GetString(6);
Alist.CreateTime = reader.GetDateTime(7);
AccList.Add(Alist);
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show(System.Reflection.MethodBase.GetCurrentMethod().Name + "() : Error");
}
return(AccList);
}
public Boolean updateCustHeaderAndDetail(CustomerOBheader newcust, List <CustomerOBdetail> CustDetail)
{
Boolean status = true;
string utString = "";
try
{
string updateSQL = "update CustomerOBHeader set FYID='" + newcust.FYID + "'" +
" where DocumentID='" + newcust.DocumentID +
"' and DocumentNo=" + newcust.DocumentNo +
" and DocumentDate='" + newcust.DocumentDate.ToString("yyyy-MM-dd") + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("update", "CustomerOBHeader", "", updateSQL) +
Main.QueryDelimiter;
updateSQL = "Delete from CustomerOBDetail where DocumentID='" + newcust.DocumentID + "'" +
" and DocumentNo=" + newcust.DocumentNo +
" and DocumentDate='" + newcust.DocumentDate.ToString("yyyy-MM-dd") + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("delete", "CustomerOBDetail", "", updateSQL) +
Main.QueryDelimiter;
foreach (CustomerOBdetail cob in CustDetail)
{
updateSQL = "insert into CustomerOBDetail " +
"(DocumentID,DocumentNo,DocumentDate,SerialNo,CustomerID,BalanceDebit,BalanceCredit,FYID) " +
"values ('" + cob.DocumentID + "'," +
cob.DocumentNo + "," +
"'" + cob.DocumentDate.ToString("yyyy-MM-dd") + "'," +
"'" + cob.SerialNo + "','" +
cob.AccountCode + "'," +
"'" + cob.BalanceDebit + "'," +
"'" + cob.BalanceCredit + "','" + cob.FYID + "')";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("insert", "CustomerOBDetail", "", updateSQL) +
Main.QueryDelimiter;
}
if (!UpdateTable.UT(utString))
{
status = false;
MessageBox.Show("Transaction Exception Occured");
}
}
catch (Exception ex)
{
status = false;
}
return(status);
}
public static List <CustomerOBdetail> getCustomerOBDetail(CustomerOBheader Acchr)
{
CustomerOBdetail Accd;
List <CustomerOBdetail> AccDetail = new List <CustomerOBdetail>();
try
{
string query = "";
SqlConnection conn = new SqlConnection(Login.connString);
query = "select RowID,DocumentID,DocumentNo,DocumentDate,SerialNo, " +
"CustomerID,CustomerName,BalanceDebit,BalanceCredit " +
"from ViewCustomerOB " +
" where DocumentID='" + Acchr.DocumentID + "'" +
" and DocumentNo=" + Acchr.DocumentNo +
" and DocumentDate='" + Acchr.DocumentDate.ToString("yyyy-MM-dd") + "'" + " order by SerialNo";
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Accd = new CustomerOBdetail();
Accd.RowID = reader.GetInt32(0);
Accd.DocumentID = reader.GetString(1);
Accd.DocumentNo = reader.GetInt32(2);
Accd.DocumentDate = reader.GetDateTime(3).Date;
if (!reader.IsDBNull(4))
{
Accd.SerialNo = reader.GetInt32(4);
}
Accd.AccountCode = reader.GetString(5);
Accd.AccountName = reader.GetString(6);
Accd.BalanceDebit = reader.GetDecimal(7);
Accd.BalanceCredit = reader.GetDecimal(8);
AccDetail.Add(Accd);
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show(System.Reflection.MethodBase.GetCurrentMethod().Name + "() : Error");
}
return(AccDetail);
}
public Boolean InsertCustHeaderAndDetail(CustomerOBheader newcust, List <CustomerOBdetail> CustDetail)
{
Boolean status = true;
string utString = "";
string updateSQL = "";
try
{
newcust.DocumentNo = DocumentNumberDB.getNumber(newcust.DocumentID, 1);
if (newcust.DocumentNo <= 0)
{
MessageBox.Show("Error in Creating New Number");
return(false);
}
updateSQL = "update DocumentNumber set TempNo =" + newcust.DocumentNo +
" where FYID='" + Main.currentFY + "' and DocumentID='" + newcust.DocumentID + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("update", "DocumentNumber", "", updateSQL) +
Main.QueryDelimiter;
updateSQL = "insert into CustomerOBHeader " +
" (DocumentID,FYID,DocumentDate,DocumentNo,Status," +
"CreateUser,CreateTime)" +
"values (" +
"'" + newcust.DocumentID + "','" +
newcust.FYID + "'," +
"'" + newcust.DocumentDate.ToString("yyyy-MM-dd") + "'," +
"'" + newcust.DocumentNo + "','" + newcust.Status + "'," +
"'" + Login.userLoggedIn + "'," +
"GETDATE())";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("insert", "CustomerOBHeader", "", updateSQL) +
Main.QueryDelimiter;
updateSQL = "Delete from CustomerOBDetail where DocumentID='" + newcust.DocumentID + "'" +
" and DocumentNo=" + newcust.DocumentNo +
" and DocumentDate='" + newcust.DocumentDate.ToString("yyyy-MM-dd") + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("delete", "CustomerOBDetail", "", updateSQL) +
Main.QueryDelimiter;
foreach (CustomerOBdetail cob in CustDetail)
{
updateSQL = "insert into CustomerOBDetail " +
"(DocumentID,DocumentNo,DocumentDate,SerialNo,CustomerID,BalanceDebit,BalanceCredit,FYID) " +
"values ('" + cob.DocumentID + "'," +
newcust.DocumentNo + "," +
"'" + cob.DocumentDate.ToString("yyyy-MM-dd") + "'," +
"'" + cob.SerialNo + "','" +
cob.AccountCode + "'," +
"'" + cob.BalanceDebit + "'," +
"'" + cob.BalanceCredit + "','" + cob.FYID + "')";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("insert", "CustomerOBDetail", "", updateSQL) +
Main.QueryDelimiter;
}
if (!UpdateTable.UT(utString))
{
status = false;
}
}
catch (Exception ex)
{
status = false;
MessageBox.Show("Transaction Exception Occured");
}
return(status);
}