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