public string Save(REceiptTypes.ReceiptDataType _SaveData,out string RCVTmp)
{
RCVTmp = GetNewTMPPayID(_SaveData.CompanyID, _SaveData.AccPeriod);
MySql.Data.MySqlClient.MySqlTransaction Mytrans;
MySqlConnection CurCon = new MySqlConnection();
CurCon = Mycommon.AccountConnection;
if (CurCon.State == ConnectionState.Closed)
CurCon.Open();
string respond = "";
Mytrans = Mycommon.AccountConnection.BeginTransaction();
MySqlCommand oSqlCommand = new MySqlCommand();
if (!ExistReceipt(RCVTmp, CurCon))
{
respond = SaveReceipt(_SaveData, RCVTmp, CurCon);
if (respond != "True")
{
Mytrans.Rollback();
Mycommon.AccountConnection.Close();
CurCon.Dispose();
return respond;
}
else
{
respond = SaveDetails(_SaveData.ReceiptList, RCVTmp, CurCon);
if (respond != "True")
{
Mytrans.Rollback();
Mycommon.AccountConnection.Close();
CurCon.Dispose();
return respond;
}
else
{
Mytrans.Commit();
Mycommon.AccountConnection.Close();
CurCon.Dispose();
return "True";
}
}
}
else
return "Number Already Exist, Use Update Button";
}
public string Update(REceiptTypes.ReceiptDataType _SaveData)
{
MySql.Data.MySqlClient.MySqlTransaction Mytrans;
MySqlConnection CurCon = new MySqlConnection();
CurCon = Mycommon.AccountConnection;
if (CurCon.State == ConnectionState.Closed)
CurCon.Open();
string respond = "";
Mytrans = Mycommon.AccountConnection.BeginTransaction();
MySqlCommand oSqlCommand = new MySqlCommand();
if (ExistReceipt(_SaveData.ReceiptID , CurCon))
{
respond = UpdateReceipt(_SaveData, CurCon);
if (respond != "True")
{
Mytrans.Rollback();
Mycommon.AccountConnection.Close();
CurCon.Dispose();
return respond;
}
else
{
respond = UpdateDetails(_SaveData.ReceiptList, CurCon);
if (respond != "True")
{
Mytrans.Rollback();
Mycommon.AccountConnection.Close();
CurCon.Dispose();
return respond;
}
else
{
Mytrans.Commit();
Mycommon.AccountConnection.Close();
CurCon.Dispose();
return "True";
}
}
}
else
return "Number Already Exist, Use Update Button";
}
public string GetExistReceiptDetails(int ItemNo, string RcptNo, out REceiptTypes.ReceiptDetailsDataType _ExistData)
{
_ExistData = new REceiptTypes.ReceiptDetailsDataType();
MySqlCommand oSqlCommand = new MySqlCommand();
string sqlQuery = "Select "
+ "ItemNo,"
+ "RcptNo,"
+ "AccID,"
+ "Description,"
+ "Cr,"
+ "FCr,"
+ "RcptTrRef,"
+ "Vat,"
+ "JobNo,Exrate"
+ " from tblreceiptdetails"
+ " Where 1=1 "
+ " and ItemNo=@ItemNo"
+ " and RcptNo=@RcptNo";
oSqlCommand.Parameters.AddWithValue("@ItemNo", ItemNo);
oSqlCommand.Parameters.AddWithValue("@RcptNo", RcptNo);
DataRow r = Mycommon.GetDataRowAccount(sqlQuery, oSqlCommand, "Get Exist data ReceiptDetails");
if (r != null)
{
try
{
bool resp = false;
int inItemNo = 0;
resp = int.TryParse(r["ItemNo"].ToString(), out inItemNo);
_ExistData.ItemNo = inItemNo;
_ExistData.RcptNo = r["RcptNo"].ToString();
_ExistData.AccID = r["AccID"].ToString();
_ExistData.Description = r["Description"].ToString();
decimal deCr = 0;
resp = decimal.TryParse(r["Cr"].ToString(), out deCr);
_ExistData.Cr = deCr;
decimal deFCr = 0;
resp = decimal.TryParse(r["FCr"].ToString(), out deFCr);
_ExistData.FCr = deFCr;
_ExistData.RcptTrRef = r["RcptTrRef"].ToString();
decimal deVat = 0;
resp = decimal.TryParse(r["Vat"].ToString(), out deVat);
_ExistData.Vat = deVat;
_ExistData.JobNo = r["JobNo"].ToString();
decimal deExrate = 0;
resp = decimal.TryParse(r["Exrate"].ToString(), out deExrate);
_ExistData.Exrate = deExrate;
return "True";
}
catch (Exception ex)
{
return ex.Message;
}
}
else
return "data not found ";
}
public string UpdateReceiptDetails(REceiptTypes.ReceiptDetailsDataType _Update, MySqlConnection _ActCon)
{
MySqlCommand oSqlCommand = new MySqlCommand();
string sqlQuery = "Update tblreceiptdetails Set "
+ "ItemNo=@ItemNo,"
+ "RcptNo=@RcptNo,"
+ "AccID=@AccID,"
+ "Description=@Description,"
+ "Cr=@Cr,"
+ "FCr=@FCr,"
+ "RcptTrRef=@RcptTrRef,"
+ "Vat=@Vat,"
+ "JobNo=@JobNo,Exrate=@Exrate"
+ " Where 1=1 "
+ " and ItemNo=@ItemNo"
+ " and RcptNo=@RcptNo";
try
{
oSqlCommand.Parameters.AddWithValue("@ItemNo", _Update.ItemNo);
oSqlCommand.Parameters.AddWithValue("@RcptNo", _Update.RcptNo);
oSqlCommand.Parameters.AddWithValue("@AccID", _Update.AccID);
oSqlCommand.Parameters.AddWithValue("@Description", _Update.Description);
oSqlCommand.Parameters.AddWithValue("@Cr", _Update.Cr);
oSqlCommand.Parameters.AddWithValue("@FCr", _Update.FCr);
oSqlCommand.Parameters.AddWithValue("@RcptTrRef", _Update.RcptTrRef);
oSqlCommand.Parameters.AddWithValue("@Vat", _Update.Vat);
oSqlCommand.Parameters.AddWithValue("@JobNo", _Update.JobNo);
oSqlCommand.Parameters.AddWithValue("@Exrate", _Update.Exrate);
string respond = Mycommon.ExicuteAnyCommandAccountWithTrans(sqlQuery, oSqlCommand, _ActCon, "Update ReceiptDetails");
return respond;
}
catch (Exception ex)
{
return ex.Message;
}
}
public string SaveReceiptDetails(REceiptTypes.ReceiptDetailsDataType _SaveData, MySqlConnection _ActCon)
{
MySqlCommand oSqlCommand = new MySqlCommand();
string sqlQuery = "Insert Into tblreceiptdetails ("
+ "ItemNo,"
+ "RcptNo,"
+ "AccID,"
+ "Description,"
+ "Cr,"
+ "FCr,"
+ "RcptTrRef,"
+ "Vat,"
+ "JobNo,Exrate)"
+ " Values ("
+ "@ItemNo,"
+ "@RcptNo,"
+ "@AccID,"
+ "@Description,"
+ "@Cr,"
+ "@FCr,"
+ "@RcptTrRef,"
+ "@Vat,"
+ "@JobNo,@Exrate)";
try
{
oSqlCommand.Parameters.AddWithValue("@ItemNo", _SaveData.ItemNo);
oSqlCommand.Parameters.AddWithValue("@RcptNo", _SaveData.RcptNo);
oSqlCommand.Parameters.AddWithValue("@AccID", _SaveData.AccID);
oSqlCommand.Parameters.AddWithValue("@Description", _SaveData.Description);
oSqlCommand.Parameters.AddWithValue("@Cr", _SaveData.Cr);
oSqlCommand.Parameters.AddWithValue("@FCr", _SaveData.FCr);
oSqlCommand.Parameters.AddWithValue("@RcptTrRef", _SaveData.RcptTrRef);
oSqlCommand.Parameters.AddWithValue("@Vat", _SaveData.Vat);
oSqlCommand.Parameters.AddWithValue("@JobNo", _SaveData.JobNo);
oSqlCommand.Parameters.AddWithValue("@Exrate", _SaveData.Exrate);
string respond = Mycommon.ExicuteAnyCommandAccountWithTrans(sqlQuery, oSqlCommand,_ActCon, "Save ReceiptDetails");
return respond;
}
catch (Exception ex)
{
return ex.Message;
}
}
public string GetExistReceipt(string ReceiptID, out REceiptTypes.ReceiptDataType _ExistData)
{
_ExistData = new REceiptTypes.ReceiptDataType();
MySqlCommand oSqlCommand = new MySqlCommand();
string sqlQuery = "Select "
+ "ReceiptID,"
+ "ReceiptMethod,"
+ "Description,"
+ "AccountID,"
+ "CurRate,"
+ "Dr,"
+ "FDr,"
+ "ReceiptStatus,"
+ "TrRef,"
+ "TrUser,"
+ "TrDate,"
+ "TrTime,"
+ "ChequeNumber,"
+ "AccPeriod,"
+ "CompanyID,"
+ "RcptFromCatID,"
+ "RcptFromName,"
+ "RcptActualDate,"
+ "ApproveBy,"
+ "ApproveDate,"
+ "ApproveTime,"
+ "AcountedBy,"
+ "AccountDate,"
+ "AccountedTime"
+ " from tblreceipt"
+ " Where 1=1 "
+ " and ReceiptID=@ReceiptID";
oSqlCommand.Parameters.AddWithValue("@ReceiptID", ReceiptID);
DataRow r = Mycommon.GetDataRowAccount(sqlQuery, oSqlCommand, "Get Exist data Receipt");
if (r != null)
{
try
{
bool resp = false;
_ExistData.ReceiptID = r["ReceiptID"].ToString();
int inReceiptMethod = 0;
resp = int.TryParse(r["ReceiptMethod"].ToString(), out inReceiptMethod);
_ExistData.ReceiptMethod = inReceiptMethod;
_ExistData.Description = r["Description"].ToString();
_ExistData.AccountID = r["AccountID"].ToString();
decimal deCurRate = 0;
resp = decimal.TryParse(r["CurRate"].ToString(), out deCurRate);
_ExistData.CurRate = deCurRate;
decimal deDr = 0;
resp = decimal.TryParse(r["Dr"].ToString(), out deDr);
_ExistData.Dr = deDr;
decimal deFDr = 0;
resp = decimal.TryParse(r["FDr"].ToString(), out deFDr);
_ExistData.FDr = deFDr;
int inReceiptStatus = 0;
resp = int.TryParse(r["ReceiptStatus"].ToString(), out inReceiptStatus);
_ExistData.ReceiptStatus = inReceiptStatus;
_ExistData.TrRef = r["TrRef"].ToString();
_ExistData.TrUser = r["TrUser"].ToString();
DateTime dtTrDate = new DateTime(1900, 1, 1);
resp = DateTime.TryParse(r["TrDate"].ToString(), out dtTrDate);
if (resp)
_ExistData.TrDate = dtTrDate;
else
_ExistData.TrDate = new DateTime(1900, 1, 1);
_ExistData.TrTime = r["TrTime"].ToString();
_ExistData.ChequeNumber = r["ChequeNumber"].ToString();
int inAccPeriod = 0;
resp = int.TryParse(r["AccPeriod"].ToString(), out inAccPeriod);
_ExistData.AccPeriod = inAccPeriod;
int inCompanyID = 0;
resp = int.TryParse(r["CompanyID"].ToString(), out inCompanyID);
_ExistData.CompanyID = inCompanyID;
int inRcptFromCatID = 0;
resp = int.TryParse(r["RcptFromCatID"].ToString(), out inRcptFromCatID);
_ExistData.RcptFromCatID = inRcptFromCatID;
_ExistData.RcptFromName = r["RcptFromName"].ToString();
DateTime dtRcptActualDate = new DateTime(1900, 1, 1);
resp = DateTime.TryParse(r["RcptActualDate"].ToString(), out dtRcptActualDate);
if (resp)
_ExistData.RcptActualDate = dtRcptActualDate;
else
_ExistData.RcptActualDate = new DateTime(1900, 1, 1);
_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 <REceiptTypes.ReceiptDetailsDataType> _ExtDetails=new List<REceiptTypes.ReceiptDetailsDataType>();
string rsp = GetReceiptList(ReceiptID, out _ExtDetails);
_ExistData.ReceiptList = _ExtDetails;
return "True";
}
catch (Exception ex)
{
return ex.Message;
}
}
else
return "data not found ";
}
public string UpdateReceipt(REceiptTypes.ReceiptDataType _Update, MySqlConnection _ActCon)
{
MySqlCommand oSqlCommand = new MySqlCommand();
string sqlQuery = "Update tblreceipt Set "
+ "ReceiptID=@ReceiptID,"
+ "ReceiptMethod=@ReceiptMethod,"
+ "Description=@Description,"
+ "AccountID=@AccountID,"
+ "CurRate=@CurRate,"
+ "Dr=@Dr,"
+ "FDr=@FDr,"
+ "ReceiptStatus=@ReceiptStatus,"
+ "TrRef=@TrRef,"
+ "TrUser=@TrUser,"
+ "TrDate=CurDate(),"
+ "TrTime=CurTime(),"
+ "ChequeNumber=@ChequeNumber,"
+ "AccPeriod=@AccPeriod,"
+ "CompanyID=@CompanyID,"
+ "RcptFromCatID=@RcptFromCatID,"
+ "RcptFromName=@RcptFromName,"
+ "RcptActualDate=@RcptActualDate"
+ " Where 1=1 "
+ " and ReceiptID=@ReceiptID";
try
{
oSqlCommand.Parameters.AddWithValue("@ReceiptID", _Update.ReceiptID);
oSqlCommand.Parameters.AddWithValue("@ReceiptMethod", _Update.ReceiptMethod);
oSqlCommand.Parameters.AddWithValue("@Description", _Update.Description);
oSqlCommand.Parameters.AddWithValue("@AccountID", _Update.AccountID);
oSqlCommand.Parameters.AddWithValue("@CurRate", _Update.CurRate);
oSqlCommand.Parameters.AddWithValue("@Dr", _Update.Dr);
oSqlCommand.Parameters.AddWithValue("@FDr", _Update.FDr);
oSqlCommand.Parameters.AddWithValue("@ReceiptStatus", _Update.ReceiptStatus);
oSqlCommand.Parameters.AddWithValue("@TrRef", _Update.TrRef);
oSqlCommand.Parameters.AddWithValue("@TrUser", _Update.TrUser);
oSqlCommand.Parameters.AddWithValue("@ChequeNumber", _Update.ChequeNumber);
oSqlCommand.Parameters.AddWithValue("@AccPeriod", _Update.AccPeriod);
oSqlCommand.Parameters.AddWithValue("@CompanyID", _Update.CompanyID);
oSqlCommand.Parameters.AddWithValue("@RcptFromCatID", _Update.RcptFromCatID);
oSqlCommand.Parameters.AddWithValue("@RcptFromName", _Update.RcptFromName);
oSqlCommand.Parameters.AddWithValue("@RcptActualDate", _Update.RcptActualDate);
string respond = Mycommon.ExicuteAnyCommandAccountWithTrans(sqlQuery, oSqlCommand,_ActCon, "Update Receipt");
return respond;
}
catch (Exception ex)
{
return ex.Message;
}
}
public string SaveReceipt(REceiptTypes.ReceiptDataType _SaveData,string RcvNumber, MySqlConnection _ActCon)
{
MySqlCommand oSqlCommand = new MySqlCommand();
string sqlQuery = "Insert Into tblreceipt ("
+ "ReceiptID,"
+ "ReceiptMethod,"
+ "Description,"
+ "AccountID,"
+ "CurRate,"
+ "Dr,"
+ "FDr,"
+ "ReceiptStatus,"
+ "TrRef,"
+ "TrUser,"
+ "TrDate,"
+ "TrTime,"
+ "ChequeNumber,"
+ "AccPeriod,"
+ "CompanyID,"
+ "RcptFromCatID,"
+ "RcptFromName,"
+ "RcptActualDate)"
+ " Values ("
+ "@ReceiptID,"
+ "@ReceiptMethod,"
+ "@Description,"
+ "@AccountID,"
+ "@CurRate,"
+ "@Dr,"
+ "@FDr,"
+ "@ReceiptStatus,"
+ "@TrRef,"
+ "@TrUser,"
+ "CurDate(),"
+ "CurTime(),"
+ "@ChequeNumber,"
+ "@AccPeriod,"
+ "@CompanyID,"
+ "@RcptFromCatID,"
+ "@RcptFromName,"
+ "@RcptActualDate)";
try
{
oSqlCommand.Parameters.AddWithValue("@ReceiptID", RcvNumber);
oSqlCommand.Parameters.AddWithValue("@ReceiptMethod", _SaveData.ReceiptMethod);
oSqlCommand.Parameters.AddWithValue("@Description", _SaveData.Description);
oSqlCommand.Parameters.AddWithValue("@AccountID", _SaveData.AccountID);
oSqlCommand.Parameters.AddWithValue("@CurRate", _SaveData.CurRate);
oSqlCommand.Parameters.AddWithValue("@Dr", _SaveData.Dr);
oSqlCommand.Parameters.AddWithValue("@FDr", _SaveData.FDr);
oSqlCommand.Parameters.AddWithValue("@ReceiptStatus", _SaveData.ReceiptStatus);
oSqlCommand.Parameters.AddWithValue("@TrRef", _SaveData.TrRef);
oSqlCommand.Parameters.AddWithValue("@TrUser", _SaveData.TrUser);
oSqlCommand.Parameters.AddWithValue("@ChequeNumber", _SaveData.ChequeNumber);
oSqlCommand.Parameters.AddWithValue("@AccPeriod", _SaveData.AccPeriod);
oSqlCommand.Parameters.AddWithValue("@CompanyID", _SaveData.CompanyID);
oSqlCommand.Parameters.AddWithValue("@RcptFromCatID", _SaveData.RcptFromCatID);
oSqlCommand.Parameters.AddWithValue("@RcptFromName", _SaveData.RcptFromName);
oSqlCommand.Parameters.AddWithValue("@RcptActualDate", _SaveData.RcptActualDate);
string respond = Mycommon.ExicuteAnyCommandAccountWithTrans(sqlQuery, oSqlCommand,_ActCon, "Save Receipt");
return respond;
}
catch (Exception ex)
{
return ex.Message;
}
}
private string SetHeaderDatatoClass(out REceiptTypes.ReceiptDataType _SaveHeader)
{
_SaveHeader = new REceiptTypes.ReceiptDataType();
try
{
_SaveHeader.AccountID = MyCommon.GetSelectedID(cmbFromAcc, true);
_SaveHeader.ChequeNumber = cmbMethodObg.Text;
_SaveHeader.Dr = decimal.Parse(txtToalAmountLKR.Text);
_SaveHeader.CurRate = decimal.Parse(txtExRate.Text);
_SaveHeader.Description = txtfromMemo.Text;
_SaveHeader.FDr = decimal.Parse(txtToalAmountFCR.Text);
_SaveHeader.ReceiptID = txtRcptNumber.Text;
_SaveHeader.ReceiptMethod = int.Parse(MyCommon.GetSelectedID(cmbPayMethod, true));
_SaveHeader.ReceiptStatus = 0;
_SaveHeader.TrUser = Program.AccountStatic.UserName;
_SaveHeader.CompanyID = Program.AccountStatic.CompanyID;
_SaveHeader.AccPeriod = Program.AccountStatic.CurrentAccPeriod;
_SaveHeader.RcptFromCatID = int.Parse(MyCommon.GetSelectedID(cmbPayFor, true));
_SaveHeader.RcptFromName = cmbPayTo.Text;
_SaveHeader.RcptActualDate = dtpPVNDate.Value;
List<REceiptTypes.ReceiptDetailsDataType> _SaveDetailList;
string respond = SetDetailDataToClass(out _SaveDetailList);
_SaveHeader.ReceiptList = _SaveDetailList;
return "True";
}
catch (Exception ex)
{
return ex.Message;
}
}
