public string SaveJournalData(JournalType.JournalDataDataType _SaveData, MySqlConnection _ActCon, out string JeNumber)
{
MySqlCommand oSqlCommand = new MySqlCommand();
JeNumber = GetNewJEnumber(_ActCon);
string sqlQuery = "Insert Into tbljournalheader ("
+ "JounalID,"
+ "TimePeriod,"
+ "CompanyID,"
+ "Reason,"
+ "JeStatus,"
+ "Jedate,"
+ "JeUser,"
+ "LastAccDate,"
+ "JeTime)"
+ " Values ("
+ "@JounalID,"
+ "@TimePeriod,"
+ "@CompanyID,"
+ "@Reason,"
+ "@JeStatus,"
+ "@Jedate,"
+ "@JeUser,"
+ "curDate(),"
+ "curTime())";
try
{
oSqlCommand.Parameters.AddWithValue("@JounalID", JeNumber);
oSqlCommand.Parameters.AddWithValue("@TimePeriod", _SaveData.TimePeriod);
oSqlCommand.Parameters.AddWithValue("@CompanyID", _SaveData.CompanyID);
oSqlCommand.Parameters.AddWithValue("@Reason", _SaveData.Reason);
oSqlCommand.Parameters.AddWithValue("@JeStatus", _SaveData.JeStatus);
oSqlCommand.Parameters.AddWithValue("@Jedate", _SaveData.Jedate);
oSqlCommand.Parameters.AddWithValue("@JeUser", _SaveData.JeUser);
string respond = Mycommon.ExicuteAnyCommandAccountWithTrans(sqlQuery, oSqlCommand, _ActCon, "Save JournalData");
return(respond);
}
catch (Exception ex)
{
return(ex.Message);
}
}
public string UpdateJournalData(JournalType.JournalDataDataType _Update, MySqlConnection _ActCon)
{
MySqlCommand oSqlCommand = new MySqlCommand();
string sqlQuery = "Update tbljournalheader Set "
+ "JounalID=@JounalID,"
+ "TimePeriod=@TimePeriod,"
+ "CompanyID=@CompanyID,"
+ "Reason=@Reason,"
+ "JeStatus=@JeStatus,"
+ "Jedate=@Jedate,"
+ "JeUser=@JeUser,"
+ "LastAccDate=curDate(),"
+ "JeTime=curTime()"
+ " Where 1=1 "
+ " and JounalID=@JounalID";
try
{
oSqlCommand.Parameters.AddWithValue("@JounalID", _Update.JounalID);
oSqlCommand.Parameters.AddWithValue("@TimePeriod", _Update.TimePeriod);
oSqlCommand.Parameters.AddWithValue("@CompanyID", _Update.CompanyID);
oSqlCommand.Parameters.AddWithValue("@Reason", _Update.Reason);
oSqlCommand.Parameters.AddWithValue("@JeStatus", _Update.JeStatus);
oSqlCommand.Parameters.AddWithValue("@Jedate", _Update.Jedate);
oSqlCommand.Parameters.AddWithValue("@JeUser", _Update.JeUser);
string respond = Mycommon.ExicuteAnyCommandAccountWithTrans(sqlQuery, oSqlCommand, _ActCon, "Update JournalData");
return(respond);
}
catch (Exception ex)
{
return(ex.Message);
}
}
public string GetExistJournalData(string JounalID, out JournalType.JournalDataDataType _ExistData)
{
_ExistData = new JournalType.JournalDataDataType();
MySqlCommand oSqlCommand = new MySqlCommand();
string sqlQuery = "Select "
+ "SysID,"
+ "JounalID,"
+ "TimePeriod,"
+ "CompanyID,"
+ "Reason,"
+ "JeStatus,"
+ "Jedate,"
+ "JeUser,"
+ "LastAccDate,"
+ "JeTime,"
+ "ApproveBy,"
+ "ApproveDate,"
+ "ApproveTime,"
+ "AcountedBy,"
+ "AccountDate,"
+ "AccountedTime"
+ " from tbljournalheader"
+ " Where 1=1 "
+ " and JounalID=@JounalID";
oSqlCommand.Parameters.AddWithValue("@JounalID", JounalID);
DataRow r = Mycommon.GetDataRowAccount(sqlQuery, oSqlCommand, "Get Exist data JournalData");
if (r != null)
{
try
{
bool resp = false;
int inSysID = 0;
resp = int.TryParse(r["SysID"].ToString(), out inSysID);
_ExistData.SysID = inSysID;
_ExistData.JounalID = r["JounalID"].ToString();
int inTimePeriod = 0;
resp = int.TryParse(r["TimePeriod"].ToString(), out inTimePeriod);
_ExistData.TimePeriod = inTimePeriod;
int inCompanyID = 0;
resp = int.TryParse(r["CompanyID"].ToString(), out inCompanyID);
_ExistData.CompanyID = inCompanyID;
_ExistData.Reason = r["Reason"].ToString();
int inJeStatus = 0;
resp = int.TryParse(r["JeStatus"].ToString(), out inJeStatus);
_ExistData.JeStatus = inJeStatus;
DateTime dtJedate = new DateTime(1900, 1, 1);
resp = DateTime.TryParse(r["Jedate"].ToString(), out dtJedate);
if (resp)
_ExistData.Jedate = dtJedate;
else
_ExistData.Jedate = new DateTime(1900, 1, 1);
_ExistData.JeUser = r["JeUser"].ToString();
DateTime dtLastAccDate = new DateTime(1900, 1, 1);
resp = DateTime.TryParse(r["LastAccDate"].ToString(), out dtLastAccDate);
if (resp)
_ExistData.LastAccDate = dtLastAccDate;
else
_ExistData.LastAccDate = new DateTime(1900, 1, 1);
_ExistData.JeTime = r["JeTime"].ToString();
_ExistData.ApproveBy = r["ApproveBy"].ToString();
DateTime dtApproveDate = new DateTime(1900, 1, 1);
resp = DateTime.TryParse(r["ApproveDate"].ToString(), out dtApproveDate);
if (resp)
_ExistData.ApproveDate = dtApproveDate;
else
_ExistData.ApproveDate = new DateTime(1900, 1, 1);
_ExistData.ApproveTime = r["ApproveTime"].ToString();
_ExistData.AcountedBy = r["AcountedBy"].ToString();
DateTime dtAccountDate = new DateTime(1900, 1, 1);
resp = DateTime.TryParse(r["AccountDate"].ToString(), out dtAccountDate);
if (resp)
_ExistData.AccountDate = dtAccountDate;
else
_ExistData.AccountDate = new DateTime(1900, 1, 1);
_ExistData.AccountedTime = r["AccountedTime"].ToString();
List<JournalType.JournalDetailsDataType> _DataList = new List<JournalType.JournalDetailsDataType>();
string respond = GetJournalDetailsList(JounalID, out _DataList);
_ExistData.DetailList = _DataList;
return "True";
}
catch (Exception ex)
{
return ex.Message;
}
}
else
return "data not found ";
}
public string GetExistJournalData(string JounalID, out JournalType.JournalDataDataType _ExistData)
{
_ExistData = new JournalType.JournalDataDataType();
MySqlCommand oSqlCommand = new MySqlCommand();
string sqlQuery = "Select "
+ "SysID,"
+ "JounalID,"
+ "TimePeriod,"
+ "CompanyID,"
+ "Reason,"
+ "JeStatus,"
+ "Jedate,"
+ "JeUser,"
+ "LastAccDate,"
+ "JeTime,"
+ "ApproveBy,"
+ "ApproveDate,"
+ "ApproveTime,"
+ "AcountedBy,"
+ "AccountDate,"
+ "AccountedTime"
+ " from tbljournalheader"
+ " Where 1=1 "
+ " and JounalID=@JounalID";
oSqlCommand.Parameters.AddWithValue("@JounalID", JounalID);
DataRow r = Mycommon.GetDataRowAccount(sqlQuery, oSqlCommand, "Get Exist data JournalData");
if (r != null)
{
try
{
bool resp = false;
int inSysID = 0;
resp = int.TryParse(r["SysID"].ToString(), out inSysID);
_ExistData.SysID = inSysID;
_ExistData.JounalID = r["JounalID"].ToString();
int inTimePeriod = 0;
resp = int.TryParse(r["TimePeriod"].ToString(), out inTimePeriod);
_ExistData.TimePeriod = inTimePeriod;
int inCompanyID = 0;
resp = int.TryParse(r["CompanyID"].ToString(), out inCompanyID);
_ExistData.CompanyID = inCompanyID;
_ExistData.Reason = r["Reason"].ToString();
int inJeStatus = 0;
resp = int.TryParse(r["JeStatus"].ToString(), out inJeStatus);
_ExistData.JeStatus = inJeStatus;
DateTime dtJedate = new DateTime(1900, 1, 1);
resp = DateTime.TryParse(r["Jedate"].ToString(), out dtJedate);
if (resp)
{
_ExistData.Jedate = dtJedate;
}
else
{
_ExistData.Jedate = new DateTime(1900, 1, 1);
}
_ExistData.JeUser = r["JeUser"].ToString();
DateTime dtLastAccDate = new DateTime(1900, 1, 1);
resp = DateTime.TryParse(r["LastAccDate"].ToString(), out dtLastAccDate);
if (resp)
{
_ExistData.LastAccDate = dtLastAccDate;
}
else
{
_ExistData.LastAccDate = new DateTime(1900, 1, 1);
}
_ExistData.JeTime = r["JeTime"].ToString();
_ExistData.ApproveBy = r["ApproveBy"].ToString();
DateTime dtApproveDate = new DateTime(1900, 1, 1);
resp = DateTime.TryParse(r["ApproveDate"].ToString(), out dtApproveDate);
if (resp)
{
_ExistData.ApproveDate = dtApproveDate;
}
else
{
_ExistData.ApproveDate = new DateTime(1900, 1, 1);
}
_ExistData.ApproveTime = r["ApproveTime"].ToString();
_ExistData.AcountedBy = r["AcountedBy"].ToString();
DateTime dtAccountDate = new DateTime(1900, 1, 1);
resp = DateTime.TryParse(r["AccountDate"].ToString(), out dtAccountDate);
if (resp)
{
_ExistData.AccountDate = dtAccountDate;
}
else
{
_ExistData.AccountDate = new DateTime(1900, 1, 1);
}
_ExistData.AccountedTime = r["AccountedTime"].ToString();
List <JournalType.JournalDetailsDataType> _DataList = new List <JournalType.JournalDetailsDataType>();
string respond = GetJournalDetailsList(JounalID, out _DataList);
_ExistData.DetailList = _DataList;
return("True");
}
catch (Exception ex)
{
return(ex.Message);
}
}
else
{
return("data not found ");
}
}
public string Update(JournalType.JournalDataDataType _SaveData)
{
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 = UpdateJournalData(_SaveData, CurCon);
if (respond != "True")
{
Mytrans.Rollback();
CurCon.Close();
return(respond);
}
else
{
foreach (JournalType.JournalDetailsDataType item in _SaveData.DetailList)
{
if (!ExistJournalDetails(item.JeNumber, item.LineRef, CurCon))
{
respond = UpdateJournalDetails(item, CurCon);
if (respond != "True")
{
Mytrans.Rollback();
CurCon.Close();
return(respond);
}
}
else
{
respond = UpdateJournalDetails(item, 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 void btnUpdate_Click(object sender, EventArgs e)
{
if (MyJournal.ExistJournalData(textJENo.Text))
{
if (MessageBox.Show("Do you want to save ? ", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.Yes)
{
int Jstatus = MyJournal.GetJEStatus(textJENo.Text);
string respond = "";
JournalType.JournalDataDataType _SaveData = new JournalType.JournalDataDataType();
if (!ChechBothSideIsCorrectlyBalance())
{
Program.VerningMessage("Total Credit And Debit Amount Miss match, pls re-check total amount");
}
else
{
if (Jstatus != 3)
{
respond = SetDataToJournal(out _SaveData);
if (respond != "True")
Program.VerningMessage(respond);
else
{
respond = MyJournal.Update(_SaveData);
if (respond == "True")
{
Program.InformationMessage("Journal Entry saved successfully");
}
else
Program.VerningMessage(respond);
}
}
else
{
Program.VerningMessage("This journal already Accounted you cannot change");
}
}
}
}
}
private string SetDataToJournal(out JournalType.JournalDataDataType _SaveData)
{
_SaveData = new JournalType.JournalDataDataType();
try
{
_SaveData.Jedate = dtpJEdate.Value;
_SaveData.JeStatus = 0;
_SaveData.JounalID = textJENo.Text;
_SaveData.Reason = txtDescription.Text;
_SaveData.JeUser = Program.AccountStatic.UserName;
_SaveData.TimePeriod = Program.AccountStatic.CurrentAccPeriod;
_SaveData.CompanyID = Program.AccountStatic.CompanyID ;
List<JournalType.JournalDetailsDataType> _JournalList = new List<JournalType.JournalDetailsDataType>();
string respond = SetJournalDetailList(out _JournalList);
_SaveData.DetailList = _JournalList;
return "True";
}
catch (Exception ex)
{
return ex.Message;
}
}
