public BillingDataTypes.PendingPaymentDataType TransformBillToPending(BillingDataTypes.BillingDataType _DataIn) { BillingDataTypes.PendingPaymentDataType _TransForm = new BillingDataTypes.PendingPaymentDataType(); _TransForm.AccountID = _DataIn.AccountID; _TransForm.AccPeriod = _DataIn.AccPeriod; _TransForm.BillNo = _DataIn.BillNo; _TransForm.BillStatus = 0; _TransForm.CompanyID = _DataIn.CompanyID ; _TransForm.Cr = _DataIn.Cr ; _TransForm.CurRate = _DataIn.CurRate; _TransForm.Description = _DataIn.Description; _TransForm.FCr = _DataIn.FCr; _TransForm.PayedAmount =0; _TransForm.Supplier = _DataIn.PayToID; _TransForm.TobePayDate = _DataIn.TobePayDate; _TransForm.BillDate = _DataIn.TrDate; return _TransForm; }
public string DoBillTransaction(BillingDataTypes.BillingDataType _SaveData) { Billing MyBill= new Billing(IsLocalLoging); try { 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(); MySqlCommand oSqlCommand = new MySqlCommand(); string MainAccountRef = "", TrReference = "", PVNNumber = ""; MainAccountRef = GetAccReference(_SaveData.AccountID, CurCon); int Counter = 0; string DrAccRef = ""; string macAdd = MyGeneral.GetMACAddress(); foreach (BillingDataTypes.BillingDetailsDataType _OneData in _SaveData.BillingDetails) { AccountTransactionType.AccountTransactionDataType _OneTrans = new AccountTransactionType.AccountTransactionDataType(); string TransRefDr = ""; string AccRef = GetAccReference(_OneData.AccID, CurCon); Counter += 1; if (Counter < 5) DrAccRef = DrAccRef + "," + AccRef; _OneTrans.AccountID = _OneData.AccID; _OneTrans.AccountYear = _SaveData.AccPeriod; _OneTrans.ActualDate = _SaveData.BillDate; _OneTrans.CompanyID = _SaveData.CompanyID; _OneTrans.Cr = 0; _OneTrans.CurRate = _SaveData.CurRate; _OneTrans.Description = _OneData.Description; _OneTrans.Dr = _OneData.Dr; _OneTrans.FCr = 0; _OneTrans.FDr = _OneData.Fdr ; _OneTrans.MainRef = AccRef; _OneTrans.PayMethod = 5; _OneTrans.RelRef = MainAccountRef; _OneTrans.TransType = 2; _OneTrans.TrMachine = macAdd; _OneTrans.TrUser = _SaveData.TrUser; _OneTrans.VoucherID = _SaveData.BillNo; FullPayment = FullPayment + _OneData.Dr; respond = SaveAccountTransaction(_OneTrans, CurCon, out TransRefDr); if (respond != "True") { Mytrans.Rollback(); CurCon.Close(); return respond; } else { respond = UpdateTranseRefinReleventTable(2, true, TransRefDr, _SaveData.BillNo, _OneData.ItemNo.ToString(), CurCon); if (respond != "True") { Mytrans.Rollback(); CurCon.Close(); return respond; } else { respond = UpdateLastRefonAccount(_OneTrans.AccountID, AccRef, CurCon); if (respond != "True") { Mytrans.Rollback(); CurCon.Close(); return respond; } } } } if (respond != "True") { Mytrans.Rollback(); CurCon.Close(); return respond; } else { AccountTransactionType.AccountTransactionDataType _SaveTranData = new AccountTransactionType.AccountTransactionDataType(); _SaveTranData.AccountID = _SaveData.AccountID; //1 _SaveTranData.AccountYear = _SaveData.AccPeriod; //2 _SaveTranData.Description = _SaveData.Description; //3 _SaveTranData.CompanyID = _SaveData.CompanyID; //4 _SaveTranData.Cr = _SaveData.Cr; //5 _SaveTranData.CurRate = _SaveData.CurRate; //6 _SaveTranData.Dr = 0; //7 _SaveTranData.FCr = _SaveData.FCr ; //8 _SaveTranData.FDr = 0; //9 _SaveTranData.MainRef = MainAccountRef; //10 _SaveTranData.PayMethod = 5; //11 _SaveTranData.TrID = TrReference; //12 _SaveTranData.TransType = 2; //13 _SaveTranData.RelRef = DrAccRef; //14 _SaveTranData.TrMachine = macAdd; //14 _SaveTranData.VoucherID = _SaveData.BillNo; //15 _SaveTranData.TrUser = _SaveData.TrUser; //16 _SaveTranData.ActualDate = _SaveData.BillDate; respond = SaveAccountTransaction(_SaveTranData, CurCon, out TrReference); if ( FullPayment != _SaveData.Cr) { Mytrans.Rollback(); CurCon.Close(); return "Amount Not balance"; } if (respond != "True") { Mytrans.Rollback(); CurCon.Close(); return respond; } else { respond = UpdateTranseRefinReleventTable(2, false, TrReference, _SaveData.BillNo, "", CurCon); if (respond != "True") { Mytrans.Rollback(); CurCon.Close(); return respond; } else { respond = UpdateLastRefonAccount(_SaveData.AccountID, MainAccountRef, CurCon); if (respond != "True") { Mytrans.Rollback(); CurCon.Close(); return respond; } else { BillingDataTypes.PendingPaymentDataType _SavependingData = new BillingDataTypes.PendingPaymentDataType(); _SavependingData.AccountID = _SaveData.AccountID; _SavependingData.AccPeriod = _SaveData.AccPeriod; _SavependingData.BillDate = _SaveData.BillDate; _SavependingData.BillNo = _SaveData.BillNo; _SavependingData.BillStatus = 0; _SavependingData.CompanyID = _SaveData.CompanyID; _SavependingData.Cr = _SaveData.Cr; _SavependingData.CurRate = _SaveData.CurRate; _SavependingData.Description = _SaveData.Description; _SavependingData.FCr = _SaveData.FCr; _SavependingData.PayedAmount = 0; _SavependingData.Supplier = _SaveData.PayToID; _SavependingData.TobePayDate = _SaveData.TobePayDate; _SavependingData.TotalAmount = _SaveData.Cr; respond = MyBill.SavePP(_SavependingData, CurCon); if (respond != "True") { Mytrans.Rollback(); CurCon.Close(); return respond; } else { //respond = SetBillGetToLog(_SaveData.BillNo, _SaveData.PayToID); Mytrans.Commit(); CurCon.Close(); return "True"; } } } } } } catch (Exception ex) { return ex.Message; } }
private string SetDataToPayBill(out BillingDataTypes.BillingDataType _SaveData) { MakeDueDate(); _SaveData = new BillingDataTypes.BillingDataType(); try { _SaveData.AccountID = lblAccnumber.Text; _SaveData.AccPeriod = Program.AccountStatic.CurrentAccPeriod; _SaveData.BillNo = cmbGRN.Text; _SaveData.BillStatus = 0; _SaveData.CompanyID = Program.AccountStatic.CompanyID; _SaveData.Cr =decimal.Parse(lblTotalLKR.Text) ; _SaveData.CurRate = decimal.Parse(lblExchangerate.Text); _SaveData.Description = txtDescription.Text; _SaveData.FCr = decimal.Parse(lblTotalUSD.Text); _SaveData.PayToCatID = 0; //_SaveData.PayToID = int.Parse(MyCommon.GetSelectedID(cmbSupplier, true)); //Edited by manjula _SaveData.PayToID = Convert.ToInt32(supid); _SaveData.TobePayDate = DateTime.Parse(lblDueDate.Text); _SaveData.TrUser = Program.AccountStatic.UserName; _SaveData.TrDate = dtpInvoiceDate.Value; _SaveData.BillDate = dtpBilingDate.Value; _SaveData.PONo = dtpGRNDetals.Rows[0].Cells["PONo"].Value.ToString(); List<BillingDataTypes.BillingDetailsDataType> _SaveDetails = new List<BillingDataTypes.BillingDetailsDataType>(); string respond = SetPayBillDetails(out _SaveDetails); _SaveData.BillingDetails = _SaveDetails; return "True"; } catch (Exception ex) { return ex.Message; } }
public string UpdatePendingPayment(BillingDataTypes.PendingPaymentDataType _Update, MySqlConnection _ActCon) { MySqlCommand oSqlCommand = new MySqlCommand(); string sqlQuery = "Update tblpendingpayablebill Set " + "Description=@Description," + "AccountID=@AccountID," + "CurRate=@CurRate," + "Cr=@Cr," + "FCr=@FCr," + "BillStatus=@BillStatus," + "AccPeriod=@AccPeriod," + "Supplier=@Supplier," + "TotalAmount=@TotalAmount," + "PayedAmount=@PayedAmount," + "TobePayDate=@TobePayDate," + "BillDate=@BillDate" + " Where 1=1 " + " and Supplier=@Supplier" + " and BillNo=@BillNo"; try { oSqlCommand.Parameters.AddWithValue("@CompanyID", _Update.CompanyID); oSqlCommand.Parameters.AddWithValue("@BillNo", _Update.BillNo); oSqlCommand.Parameters.AddWithValue("@Description", _Update.Description); oSqlCommand.Parameters.AddWithValue("@AccountID", _Update.AccountID); oSqlCommand.Parameters.AddWithValue("@CurRate", _Update.CurRate); oSqlCommand.Parameters.AddWithValue("@Cr", _Update.Cr); oSqlCommand.Parameters.AddWithValue("@FCr", _Update.FCr); oSqlCommand.Parameters.AddWithValue("@BillStatus", _Update.BillStatus); oSqlCommand.Parameters.AddWithValue("@AccPeriod", _Update.AccPeriod); oSqlCommand.Parameters.AddWithValue("@Supplier", _Update.Supplier); oSqlCommand.Parameters.AddWithValue("@TotalAmount", _Update.TotalAmount); oSqlCommand.Parameters.AddWithValue("@PayedAmount", _Update.PayedAmount); oSqlCommand.Parameters.AddWithValue("@TobePayDate", _Update.TobePayDate); oSqlCommand.Parameters.AddWithValue("@BillDate", _Update.BillDate); string respond = Mycommon.ExicuteAnyCommandAccountWithTrans(sqlQuery, oSqlCommand, _ActCon,"Update PendingPayment"); return respond; } catch (Exception ex) { return ex.Message; } }
public string SavePendingPaymentInTrans(BillingDataTypes.PendingPaymentDataType _SaveData,MySqlConnection _ActCon) { MySqlCommand oSqlCommand = new MySqlCommand(); string sqlQuery = "Insert Into tblpendingpayablebill (" + "CompanyID," + "BillNo," + "Description," + "AccountID," + "CurRate," + "Cr," + "FCr," + "BillStatus," + "AccPeriod," + "Supplier," + "TotalAmount," + "PayedAmount," + "TobePayDate,BillDate)" + " Values (" + "@CompanyID," + "@BillNo," + "@Description," + "@AccountID," + "@CurRate," + "@Cr," + "@FCr," + "@BillStatus," + "@AccPeriod," + "@Supplier," + "@TotalAmount," + "@PayedAmount," + "@TobePayDate,@BillDate)"; try { oSqlCommand.Parameters.AddWithValue("@CompanyID", _SaveData.CompanyID); oSqlCommand.Parameters.AddWithValue("@BillNo", _SaveData.BillNo); oSqlCommand.Parameters.AddWithValue("@Description", _SaveData.Description); oSqlCommand.Parameters.AddWithValue("@AccountID", _SaveData.AccountID); oSqlCommand.Parameters.AddWithValue("@CurRate", _SaveData.CurRate); oSqlCommand.Parameters.AddWithValue("@Cr", _SaveData.Cr); oSqlCommand.Parameters.AddWithValue("@FCr", _SaveData.FCr); oSqlCommand.Parameters.AddWithValue("@BillStatus", _SaveData.BillStatus); oSqlCommand.Parameters.AddWithValue("@AccPeriod", _SaveData.AccPeriod); oSqlCommand.Parameters.AddWithValue("@Supplier", _SaveData.Supplier); oSqlCommand.Parameters.AddWithValue("@TotalAmount", _SaveData.TotalAmount); oSqlCommand.Parameters.AddWithValue("@PayedAmount", _SaveData.PayedAmount); oSqlCommand.Parameters.AddWithValue("@TobePayDate", _SaveData.TobePayDate); oSqlCommand.Parameters.AddWithValue("@BillDate", _SaveData.BillDate); string respond = Mycommon.ExicuteAnyCommandAccountWithTrans(sqlQuery, oSqlCommand,_ActCon,"Save PendingPayment"); return respond; } catch (Exception ex) { return ex.Message; } }
public string SavePP(BillingDataTypes.PendingPaymentDataType _SaveData, MySqlConnection _ActCon) { string respond = ""; if (!ExistPendingPaymentInTrans(_SaveData.Supplier, _SaveData.BillNo, _ActCon)) respond = SavePendingPaymentInTrans(_SaveData, _ActCon); else respond = UpdatePendingPayment(_SaveData, _ActCon); return respond; }
public string GetExistBillingDetails(int ItemNo, string BillNo, out BillingDataTypes.BillingDetailsDataType _ExistData) { _ExistData = new BillingDataTypes.BillingDetailsDataType(); MySqlCommand oSqlCommand = new MySqlCommand(); string sqlQuery = "Select " + "ItemNo," + "BillNo," + "AccID," + "Description," + "Dr," + "Fdr," + "TrRef," + "Vat" + " from tblpayablebilldetails" + " Where 1=1 " + " and ItemNo=@ItemNo" + " and BillNo=@BillNo"; oSqlCommand.Parameters.AddWithValue("@ItemNo", ItemNo); oSqlCommand.Parameters.AddWithValue("@BillNo", BillNo); DataRow r = Mycommon.GetDataRowAccount(sqlQuery, oSqlCommand, "Get Exist data BillingDetails"); if (r != null) { try { bool resp = false; int inItemNo = 0; resp = int.TryParse(r["ItemNo"].ToString(), out inItemNo); _ExistData.ItemNo = inItemNo; _ExistData.BillNo = r["BillNo"].ToString(); _ExistData.AccID = r["AccID"].ToString(); _ExistData.Description = r["Description"].ToString(); 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; _ExistData.TrRef = r["TrRef"].ToString(); decimal deVat = 0; resp = decimal.TryParse(r["Vat"].ToString(), out deVat); _ExistData.Vat = deVat; return "True"; } catch (Exception ex) { return ex.Message; } } else return "data not found "; }
public string UpdateBillingDetails(BillingDataTypes.BillingDetailsDataType _Update, MySqlConnection _ActcCon) { MySqlCommand oSqlCommand = new MySqlCommand(); string sqlQuery = "Update tblpayablebilldetails Set " + "ItemNo=@ItemNo," + "BillNo=@BillNo," + "AccID=@AccID," + "Description=@Description," + "Dr=@Dr," + "Fdr=@Fdr," + "TrRef=@TrRef," + "Vat=@Vat" + " Where 1=1 " + " and ItemNo=@ItemNo" + " and BillNo=@BillNo"; try { oSqlCommand.Parameters.AddWithValue("@ItemNo", _Update.ItemNo); oSqlCommand.Parameters.AddWithValue("@BillNo", _Update.BillNo); oSqlCommand.Parameters.AddWithValue("@AccID", _Update.AccID); oSqlCommand.Parameters.AddWithValue("@Description", _Update.Description); oSqlCommand.Parameters.AddWithValue("@Dr", _Update.Dr); oSqlCommand.Parameters.AddWithValue("@Fdr", _Update.Fdr); oSqlCommand.Parameters.AddWithValue("@TrRef", _Update.TrRef); oSqlCommand.Parameters.AddWithValue("@Vat", _Update.Vat); string respond = Mycommon.ExicuteAnyCommandAccountWithTrans(sqlQuery, oSqlCommand,_ActcCon , "Update BillingDetails"); return respond; } catch (Exception ex) { return ex.Message; } }
public string SaveBillingDetailsinTrance(BillingDataTypes.BillingDetailsDataType _SaveData,MySqlConnection _ActcCon) { MySqlCommand oSqlCommand = new MySqlCommand(); string sqlQuery = "Insert Into tblpayablebilldetails (" + "ItemNo," + "BillNo," + "AccID," + "Description," + "Dr," + "Fdr," + "TrRef," + "Vat)" + " Values (" + "@ItemNo," + "@BillNo," + "@AccID," + "@Description," + "@Dr," + "@Fdr," + "@TrRef," + "@Vat)"; try { oSqlCommand.Parameters.AddWithValue("@ItemNo", _SaveData.ItemNo); oSqlCommand.Parameters.AddWithValue("@BillNo", _SaveData.BillNo); oSqlCommand.Parameters.AddWithValue("@AccID", _SaveData.AccID); oSqlCommand.Parameters.AddWithValue("@Description", _SaveData.Description); oSqlCommand.Parameters.AddWithValue("@Dr", _SaveData.Dr); oSqlCommand.Parameters.AddWithValue("@Fdr", _SaveData.Fdr); oSqlCommand.Parameters.AddWithValue("@TrRef", _SaveData.TrRef); oSqlCommand.Parameters.AddWithValue("@Vat", _SaveData.Vat); string respond = Mycommon.ExicuteAnyCommandAccountWithTrans(sqlQuery, oSqlCommand,_ActcCon, "Save BillingDetails"); return respond; } catch (Exception ex) { return ex.Message; } }
public string Save(BillingDataTypes.BillingDataType _SaveData) { MySql.Data.MySqlClient.MySqlTransaction Mytrans; MySqlConnection CurCon = new MySqlConnection(); CurCon = Mycommon.AccountConnection; string respond = ""; if (CurCon.State == ConnectionState.Closed) CurCon.Open(); Mytrans = Mycommon.AccountConnection.BeginTransaction(); MySqlCommand oSqlCommand = new MySqlCommand(); respond = SaveBilling(_SaveData, CurCon); if (respond != "True") { Mytrans.Rollback(); return respond; } else { foreach (BillingDataTypes.BillingDetailsDataType _OneItem in _SaveData.BillingDetails) { if (!ExistBillingDetails(_OneItem.ItemNo, _OneItem.BillNo, CurCon)) { respond = SaveBillingDetailsinTrance(_OneItem, CurCon); if (respond != "True") { Mytrans.Rollback(); CurCon.Close(); return respond; } } else { respond = UpdateBillingDetails(_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 GetExistBilling(string BillNo,int SupID, out BillingDataTypes.BillingDataType _ExistData) { _ExistData = new BillingDataTypes.BillingDataType(); MySqlCommand oSqlCommand = new MySqlCommand(); string sqlQuery = "Select " + "BillNo," + "Description," + "AccountID," + "CurRate," + "Cr," + "FCr," + "BillStatus," + "TrRef," + "TrUser," + "TrDate," + "TrTime," + "AccPeriod," + "CompanyID," + "PayToCatID," + "PayToID," + "PayAmount," + "TobePayDate,BillDate" + " from tblpayablebill" + " Where 1=1 " + " and BillNo=@BillNo and PayToID=@PayToID"; oSqlCommand.Parameters.AddWithValue("@BillNo", BillNo); oSqlCommand.Parameters.AddWithValue("@PayToID", SupID); DataRow r = Mycommon.GetDataRowAccount(sqlQuery, oSqlCommand, "Get Exist data Billing"); if (r != null) { try { bool resp = false; _ExistData.BillNo = r["BillNo"].ToString(); _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 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; int inBillStatus = 0; resp = int.TryParse(r["BillStatus"].ToString(), out inBillStatus); _ExistData.BillStatus = inBillStatus; _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(); 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 inPayToCatID = 0; resp = int.TryParse(r["PayToCatID"].ToString(), out inPayToCatID); _ExistData.PayToCatID = inPayToCatID; int inPayToID = 0; resp = int.TryParse(r["PayToID"].ToString(), out inPayToID); _ExistData.PayToID = inPayToID; decimal dePayAmount = 0; resp = decimal.TryParse(r["PayAmount"].ToString(), out dePayAmount); _ExistData.PayAmount = dePayAmount; DateTime dtTobePayDate = new DateTime(1900, 1, 1); resp = DateTime.TryParse(r["TobePayDate"].ToString(), out dtTobePayDate); if (resp) _ExistData.TobePayDate = dtTobePayDate; else _ExistData.TobePayDate = new DateTime(1900, 1, 1); DateTime dtBillDate = new DateTime(1900, 1, 1); resp = DateTime.TryParse(r["BillDate"].ToString(), out dtBillDate); if (resp) _ExistData.BillDate = dtBillDate; else _ExistData.BillDate = new DateTime(1900, 1, 1); List<BillingDataTypes.BillingDetailsDataType> _ExtList; string respond = GetBillingDetaisList(BillNo, out _ExtList); _ExistData.BillingDetails = _ExtList; return "True"; } catch (Exception ex) { return ex.Message; } } else return "data not found "; }
public string UpdateBilling(BillingDataTypes.BillingDataType _Update, MySqlConnection _CurCon) { MySqlCommand oSqlCommand = new MySqlCommand(); string sqlQuery = "Update tblpayablebill Set " + "BillNo=@BillNo," + "Description=@Description," + "AccountID=@AccountID," + "CurRate=@CurRate," + "Cr=@Cr," + "FCr=@FCr," + "BillStatus=@BillStatus," + "TrUser=@TrUser," + "TrDate=curdate()," + "TrTime=curtime()," + "AccPeriod=@AccPeriod," + "CompanyID=@CompanyID," + "PayToCatID=@PayToCatID," + "PayToID=@PayToID," + "TobePayDate=@TobePayDate,BillDate=@BillDate" + " Where 1=1 " + " and BillNo=@BillNo and PayToID=@PayToID"; try { oSqlCommand.Parameters.AddWithValue("@BillNo", _Update.BillNo); oSqlCommand.Parameters.AddWithValue("@Description", _Update.Description); oSqlCommand.Parameters.AddWithValue("@AccountID", _Update.AccountID); oSqlCommand.Parameters.AddWithValue("@CurRate", _Update.CurRate); oSqlCommand.Parameters.AddWithValue("@Cr", _Update.Cr); oSqlCommand.Parameters.AddWithValue("@FCr", _Update.FCr); oSqlCommand.Parameters.AddWithValue("@BillStatus", _Update.BillStatus); oSqlCommand.Parameters.AddWithValue("@TrUser", _Update.TrUser); oSqlCommand.Parameters.AddWithValue("@AccPeriod", _Update.AccPeriod); oSqlCommand.Parameters.AddWithValue("@CompanyID", _Update.CompanyID); oSqlCommand.Parameters.AddWithValue("@PayToCatID", _Update.PayToCatID); oSqlCommand.Parameters.AddWithValue("@PayToID", _Update.PayToID); oSqlCommand.Parameters.AddWithValue("@TobePayDate", _Update.TobePayDate); oSqlCommand.Parameters.AddWithValue("@BillDate", _Update.BillDate); string respond = Mycommon.ExicuteAnyCommandAccountWithTrans(sqlQuery, oSqlCommand,_CurCon, "Update Billing"); return respond; } catch (Exception ex) { return ex.Message; } }
public string SaveBilling(BillingDataTypes.BillingDataType _SaveData,MySqlConnection _CurCon) { MySqlCommand oSqlCommand = new MySqlCommand(); string sqlQuery = "Insert Into tblpayablebill (" + "BillNo," + "Description," + "AccountID," + "CurRate," + "Cr," + "FCr," + "BillStatus," + "TrUser," + "TrDate," + "TrTime," + "AccPeriod," + "CompanyID," + "PayToCatID," + "PayToID," + "TobePayDate,BillDate,PONo)" + " Values (" + "@BillNo," + "@Description," + "@AccountID," + "@CurRate," + "@Cr," + "@FCr," + "@BillStatus," + "@TrUser," + "curdate()," + "curTime()," + "@AccPeriod," + "@CompanyID," + "@PayToCatID," + "@PayToID," + "@TobePayDate,@BillDate,@PONo)"; try { oSqlCommand.Parameters.AddWithValue("@BillNo", _SaveData.BillNo); oSqlCommand.Parameters.AddWithValue("@Description", _SaveData.Description); oSqlCommand.Parameters.AddWithValue("@AccountID", _SaveData.AccountID); oSqlCommand.Parameters.AddWithValue("@CurRate", _SaveData.CurRate); oSqlCommand.Parameters.AddWithValue("@Cr", _SaveData.Cr); oSqlCommand.Parameters.AddWithValue("@FCr", _SaveData.FCr); oSqlCommand.Parameters.AddWithValue("@BillStatus", _SaveData.BillStatus); oSqlCommand.Parameters.AddWithValue("@TrUser", _SaveData.TrUser); oSqlCommand.Parameters.AddWithValue("@AccPeriod", _SaveData.AccPeriod); oSqlCommand.Parameters.AddWithValue("@CompanyID", _SaveData.CompanyID); oSqlCommand.Parameters.AddWithValue("@PayToCatID", _SaveData.PayToCatID); oSqlCommand.Parameters.AddWithValue("@PayToID", _SaveData.PayToID); oSqlCommand.Parameters.AddWithValue("@TobePayDate", _SaveData.TobePayDate); oSqlCommand.Parameters.AddWithValue("@BillDate", _SaveData.BillDate); oSqlCommand.Parameters.AddWithValue("@PONo", _SaveData.PONo); string respond = Mycommon.ExicuteAnyCommandAccountWithTrans(sqlQuery, oSqlCommand,_CurCon, "Save Billing"); return respond; } catch (Exception ex) { return ex.Message; } }
public string GetExistPendingPayment(int CompanyID, string BillNo, out BillingDataTypes.PendingPaymentDataType _ExistData) { _ExistData = new BillingDataTypes.PendingPaymentDataType(); MySqlCommand oSqlCommand = new MySqlCommand(); string sqlQuery = "Select " + "CompanyID," + "BillNo," + "Description," + "AccountID," + "CurRate," + "Cr," + "FCr," + "BillStatus," + "AccPeriod," + "Supplier," + "TotalAmount," + "PayedAmount," + "TobePayDate" + " from tblpendingpayablebill" + " Where 1=1 " + " and CompanyID=@CompanyID" + " and BillNo=@BillNo"; oSqlCommand.Parameters.AddWithValue("@CompanyID", CompanyID); oSqlCommand.Parameters.AddWithValue("@BillNo", BillNo); DataRow r = Mycommon.GetDataRowAccount(sqlQuery, oSqlCommand, "Get Exist data PendingPayment"); if (r != null) { try { bool resp = false; int inCompanyID = 0; resp = int.TryParse(r["CompanyID"].ToString(), out inCompanyID); _ExistData.CompanyID = inCompanyID; _ExistData.BillNo = r["BillNo"].ToString(); _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 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; int inBillStatus = 0; resp = int.TryParse(r["BillStatus"].ToString(), out inBillStatus); _ExistData.BillStatus = inBillStatus; int inAccPeriod = 0; resp = int.TryParse(r["AccPeriod"].ToString(), out inAccPeriod); _ExistData.AccPeriod = inAccPeriod; int inSupplier = 0; resp = int.TryParse(r["Supplier"].ToString(), out inSupplier); _ExistData.Supplier = inSupplier; decimal deTotalAmount = 0; resp = decimal.TryParse(r["TotalAmount"].ToString(), out deTotalAmount); _ExistData.TotalAmount = deTotalAmount; decimal dePayedAmount = 0; resp = decimal.TryParse(r["PayedAmount"].ToString(), out dePayedAmount); _ExistData.PayedAmount = dePayedAmount; DateTime dtTobePayDate = new DateTime(1900, 1, 1); resp = DateTime.TryParse(r["TobePayDate"].ToString(), out dtTobePayDate); if (resp) _ExistData.TobePayDate = dtTobePayDate; else _ExistData.TobePayDate = new DateTime(1900, 1, 1); DateTime dtBillDate = new DateTime(1900, 1, 1); resp = DateTime.TryParse(r["BillDate"].ToString(), out dtBillDate); if (resp) _ExistData.BillDate = dtBillDate; else _ExistData.BillDate = new DateTime(1900, 1, 1); return "True"; } catch (Exception ex) { return ex.Message; } } else return "data not found "; }