public string GetExistJournalDetails(string JeNumber, int LineRef, out JournalType.JournalDetailsDataType _ExistData)
{
_ExistData = new JournalType.JournalDetailsDataType();
MySqlCommand oSqlCommand = new MySqlCommand();
string sqlQuery = "Select "
+ "JeNumber,"
+ "LineRef,"
+ "AccountID,"
+ "Description,"
+ "CurRate,"
+ "Cr,"
+ "Dr,"
+ "AcTrRef,Vat"
+ " from tbljournaldetails"
+ " Where 1=1 "
+ " and JeNumber=@JeNumber"
+ " and LineRef=@LineRef";
oSqlCommand.Parameters.AddWithValue("@JeNumber", JeNumber);
oSqlCommand.Parameters.AddWithValue("@LineRef", LineRef);
DataRow r = Mycommon.GetDataRowAccount(sqlQuery, oSqlCommand, "Get Exist data JournalDetails");
if (r != null)
{
try
{
bool resp = false;
_ExistData.JeNumber = r["JeNumber"].ToString();
int inLineRef = 0;
resp = int.TryParse(r["LineRef"].ToString(), out inLineRef);
_ExistData.LineRef = inLineRef;
_ExistData.AccountID = r["AccountID"].ToString();
_ExistData.Description = r["Description"].ToString();
decimal deCurRate = 0;
resp = decimal.TryParse(r["CurRate"].ToString(), out deCurRate);
_ExistData.CurRate = deCurRate;
decimal deCr = 0;
resp = decimal.TryParse(r["Cr"].ToString(), out deCr);
_ExistData.Cr = deCr;
decimal deDr = 0;
resp = decimal.TryParse(r["Dr"].ToString(), out deDr);
_ExistData.Dr = deDr;
decimal deVat = 0;
resp = decimal.TryParse(r["Vat"].ToString(), out deVat);
_ExistData.Vat = deVat;
_ExistData.AcTrRef = r["AcTrRef"].ToString();
return("True");
}
catch (Exception ex)
{
return(ex.Message);
}
}
else
{
return("data not found ");
}
}
public string UpdateJournalDetails(JournalType.JournalDetailsDataType _Update, MySqlConnection _ActCon)
{
MySqlCommand oSqlCommand = new MySqlCommand();
string sqlQuery = "Update tbljournaldetails Set "
+ "JeNumber=@JeNumber,"
+ "LineRef=@LineRef,"
+ "AccountID=@AccountID,"
+ "Description=@Description,"
+ "CurRate=@CurRate,"
+ "Cr=@Cr,"
+ "Dr=@Dr,"
+ "AcTrRef=@AcTrRef,Vat=@Vat"
+ " Where 1=1 "
+ " and JeNumber=@JeNumber"
+ " and LineRef=@LineRef";
try
{
oSqlCommand.Parameters.AddWithValue("@JeNumber", _Update.JeNumber);
oSqlCommand.Parameters.AddWithValue("@LineRef", _Update.LineRef);
oSqlCommand.Parameters.AddWithValue("@AccountID", _Update.AccountID);
oSqlCommand.Parameters.AddWithValue("@Description", _Update.Description);
oSqlCommand.Parameters.AddWithValue("@CurRate", _Update.CurRate);
oSqlCommand.Parameters.AddWithValue("@Cr", _Update.Cr);
oSqlCommand.Parameters.AddWithValue("@Dr", _Update.Dr);
oSqlCommand.Parameters.AddWithValue("@AcTrRef", _Update.AcTrRef);
oSqlCommand.Parameters.AddWithValue("@Vat", _Update.Vat);
string respond = Mycommon.ExicuteAnyCommandAccountWithTrans(sqlQuery, oSqlCommand, _ActCon, "Update JournalDetails");
return(respond);
}
catch (Exception ex)
{
return(ex.Message);
}
}
//==============
public string SaveJournalDetails(JournalType.JournalDetailsDataType _SaveData, MySqlConnection _ActCon)
{
MySqlCommand oSqlCommand = new MySqlCommand();
string sqlQuery = "Insert Into tbljournaldetails ("
+ "JeNumber,"
+ "LineRef,"
+ "AccountID,"
+ "Description,"
+ "CurRate,"
+ "Cr,"
+ "Dr,"
+ "AcTrRef,Vat)"
+ " Values ("
+ "@JeNumber,"
+ "@LineRef,"
+ "@AccountID,"
+ "@Description,"
+ "@CurRate,"
+ "@Cr,"
+ "@Dr,"
+ "@AcTrRef,@Vat)";
try
{
oSqlCommand.Parameters.AddWithValue("@JeNumber", _SaveData.JeNumber);
oSqlCommand.Parameters.AddWithValue("@LineRef", _SaveData.LineRef);
oSqlCommand.Parameters.AddWithValue("@AccountID", _SaveData.AccountID);
oSqlCommand.Parameters.AddWithValue("@Description", _SaveData.Description);
oSqlCommand.Parameters.AddWithValue("@CurRate", _SaveData.CurRate);
oSqlCommand.Parameters.AddWithValue("@Cr", _SaveData.Cr);
oSqlCommand.Parameters.AddWithValue("@Dr", _SaveData.Dr);
oSqlCommand.Parameters.AddWithValue("@AcTrRef", _SaveData.AcTrRef);
oSqlCommand.Parameters.AddWithValue("@Vat", _SaveData.Vat);
string respond = Mycommon.ExicuteAnyCommandAccountWithTrans(sqlQuery, oSqlCommand, _ActCon, "Save JournalDetails");
return(respond);
}
catch (Exception ex)
{
return(ex.Message);
}
}
public string GetJournalDetailsList(string JeNumber, out List <JournalType.JournalDetailsDataType> _DataList)
{
_DataList = new List <JournalType.JournalDetailsDataType>();
MySqlCommand oSqlCommand = new MySqlCommand();
string sql1 = "Select JeNumber,LineRef from tbljournaldetails where JeNumber=@JeNumber";
oSqlCommand.Parameters.AddWithValue("@JeNumber", JeNumber);
DataTable tb = Mycommon.GetDataTableAccount(sql1, oSqlCommand, "Get Journal data List");
if (tb != null)
{
foreach (DataRow r in tb.Rows)
{
JournalType.JournalDetailsDataType _OneItem = new JournalType.JournalDetailsDataType();
string respond = "";
string Je = "";
int Lineref = 0;
bool resp = int.TryParse(r["LineRef"].ToString(), out Lineref);
respond = GetExistJournalDetails(JeNumber, Lineref, out _OneItem);
if (respond == "True")
{
_DataList.Add(_OneItem);
}
else
{
return(respond);
}
}
return("True");
}
else
{
return("No Data Found");
}
}
public string GetJournalDetailsList(string JeNumber, out List<JournalType.JournalDetailsDataType> _DataList)
{
_DataList = new List<JournalType.JournalDetailsDataType>();
MySqlCommand oSqlCommand = new MySqlCommand();
string sql1 = "Select JeNumber,LineRef from tbljournaldetails where JeNumber=@JeNumber";
oSqlCommand.Parameters.AddWithValue("@JeNumber", JeNumber);
DataTable tb = Mycommon.GetDataTableAccount(sql1, oSqlCommand, "Get Journal data List");
if (tb != null)
{
foreach (DataRow r in tb.Rows )
{
JournalType.JournalDetailsDataType _OneItem = new JournalType.JournalDetailsDataType();
string respond = "";
string Je = "";
int Lineref = 0;
bool resp = int.TryParse(r["LineRef"].ToString(), out Lineref);
respond = GetExistJournalDetails(JeNumber, Lineref, out _OneItem);
if (respond == "True")
_DataList.Add(_OneItem);
else
return respond;
}
return "True";
}
else
return "No Data Found";
}
public string GetExistJournalDetails(string JeNumber, int LineRef, out JournalType.JournalDetailsDataType _ExistData)
{
_ExistData = new JournalType.JournalDetailsDataType();
MySqlCommand oSqlCommand = new MySqlCommand();
string sqlQuery = "Select "
+ "JeNumber,"
+ "LineRef,"
+ "AccountID,"
+ "Description,"
+ "CurRate,"
+ "Cr,"
+ "Dr,"
+ "AcTrRef,Vat"
+ " from tbljournaldetails"
+ " Where 1=1 "
+ " and JeNumber=@JeNumber"
+ " and LineRef=@LineRef";
oSqlCommand.Parameters.AddWithValue("@JeNumber", JeNumber);
oSqlCommand.Parameters.AddWithValue("@LineRef", LineRef);
DataRow r = Mycommon.GetDataRowAccount(sqlQuery, oSqlCommand,"Get Exist data JournalDetails");
if (r != null)
{
try
{
bool resp = false;
_ExistData.JeNumber = r["JeNumber"].ToString();
int inLineRef = 0;
resp = int.TryParse(r["LineRef"].ToString(), out inLineRef);
_ExistData.LineRef = inLineRef;
_ExistData.AccountID = r["AccountID"].ToString();
_ExistData.Description = r["Description"].ToString();
decimal deCurRate = 0;
resp = decimal.TryParse(r["CurRate"].ToString(), out deCurRate);
_ExistData.CurRate = deCurRate;
decimal deCr = 0;
resp = decimal.TryParse(r["Cr"].ToString(), out deCr);
_ExistData.Cr = deCr;
decimal deDr = 0;
resp = decimal.TryParse(r["Dr"].ToString(), out deDr);
_ExistData.Dr = deDr;
decimal deVat = 0;
resp = decimal.TryParse(r["Vat"].ToString(), out deVat);
_ExistData.Vat = deVat;
_ExistData.AcTrRef = r["AcTrRef"].ToString();
return "True";
}
catch (Exception ex)
{
return ex.Message;
}
}
else
return "data not found ";
}
public string Save(JournalType.JournalDataDataType _SaveData,out string JeNumber)
{
JeNumber = "";
MySql.Data.MySqlClient.MySqlTransaction Mytrans;
MySqlConnection CurCon = new MySqlConnection();
CurCon = Mycommon.AccountConnection;
string respond = "";
decimal FullPayment = 0;
if (CurCon.State == ConnectionState.Open)
{
CurCon.Close();
CurCon.Open();
}
else
{
CurCon.Open();
}
Mytrans = Mycommon.AccountConnection.BeginTransaction();
respond = SaveJournalData(_SaveData, CurCon, out JeNumber);
if (respond != "True")
{
Mytrans.Rollback();
CurCon.Close();
return respond;
}
else
{
foreach (JournalType.JournalDetailsDataType item in _SaveData.DetailList)
{
JournalType.JournalDetailsDataType OneItem = new JournalType.JournalDetailsDataType();
OneItem = item;
OneItem.JeNumber = JeNumber;
if (!ExistJournalDetails(item.JeNumber, item.LineRef, CurCon))
{
respond = SaveJournalDetails(OneItem, CurCon);
if (respond != "True")
{
Mytrans.Rollback();
CurCon.Close();
return respond;
}
}
else
{
respond = UpdateJournalDetails(OneItem, CurCon);
if (respond != "True")
{
Mytrans.Rollback();
CurCon.Close();
return respond;
}
}
}
if (respond != "True")
{
Mytrans.Rollback();
CurCon.Close();
return respond;
}
else
{
Mytrans.Commit();
CurCon.Close();
return "True";
}
}
}
public string Save(JournalType.JournalDataDataType _SaveData, out string JeNumber)
{
JeNumber = "";
MySql.Data.MySqlClient.MySqlTransaction Mytrans;
MySqlConnection CurCon = new MySqlConnection();
CurCon = Mycommon.AccountConnection;
string respond = "";
decimal FullPayment = 0;
if (CurCon.State == ConnectionState.Open)
{
CurCon.Close();
CurCon.Open();
}
else
{
CurCon.Open();
}
Mytrans = Mycommon.AccountConnection.BeginTransaction();
respond = SaveJournalData(_SaveData, CurCon, out JeNumber);
if (respond != "True")
{
Mytrans.Rollback();
CurCon.Close();
return(respond);
}
else
{
foreach (JournalType.JournalDetailsDataType item in _SaveData.DetailList)
{
JournalType.JournalDetailsDataType OneItem = new JournalType.JournalDetailsDataType();
OneItem = item;
OneItem.JeNumber = JeNumber;
if (!ExistJournalDetails(item.JeNumber, item.LineRef, CurCon))
{
respond = SaveJournalDetails(OneItem, CurCon);
if (respond != "True")
{
Mytrans.Rollback();
CurCon.Close();
return(respond);
}
}
else
{
respond = UpdateJournalDetails(OneItem, CurCon);
if (respond != "True")
{
Mytrans.Rollback();
CurCon.Close();
return(respond);
}
}
}
if (respond != "True")
{
Mytrans.Rollback();
CurCon.Close();
return(respond);
}
else
{
Mytrans.Commit();
CurCon.Close();
return("True");
}
}
}
private string SetJournalDetailList(out List<JournalType.JournalDetailsDataType> _JournalList)
{
_JournalList = new List<JournalType.JournalDetailsDataType>();
if (dgvJE.Rows.Count > 0)
{
foreach (DataGridViewRow r in dgvJE.Rows)
{
JournalType.JournalDetailsDataType _OneData = new JournalType.JournalDetailsDataType();
decimal dr = 0, cr = 0, vat = 0;
int Ref1 = 0;
try
{
bool resp = decimal.TryParse(r.Cells["dgvJE_Dr"].Value.ToString(), out dr);
resp = decimal.TryParse(r.Cells["dgvJE_Cr"].Value.ToString(), out cr);
resp = decimal.TryParse(r.Cells["dgvJE_Vat"].Value.ToString(), out vat);
resp = int.TryParse(r.Cells["dgvJE_Ref"].Value.ToString(), out Ref1);
_OneData.AccountID = r.Cells["dgvJE_AcNo"].Value.ToString();
_OneData.Cr = cr;
_OneData.CurRate = decimal.Parse(lblExrate.Text);
_OneData.Description = r.Cells["dgvJE_Memo"].Value.ToString();
_OneData.Dr = dr;
_OneData.JeNumber = textJENo.Text;
_OneData.LineRef = Ref1;
_OneData.Vat = vat;
_JournalList.Add(_OneData);
}
catch (Exception EX)
{
return EX.Message;
}
}
return "True";
}
else
return "No Data Found";
}