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; } }