private void vocher(PaymentStruct dtMain)
{
OleDbConnection conn = connection();
var transaction = conn.BeginTransaction();
try
{
string strQuery =
"INSERT INTO payment ( Account_ID, Amount, [date], deposit, Bl_Amount, Description ) VALUES (" +
dtMain.accountId + "," + dtMain.amount + ",'" + dtMain.paymentDate + "'," + dtMain.paymentFlag + "," +
dtMain.balanceAmount + ",'" + dtMain.description + "')";
// connectAndInsertAccess(strQuery, dbPathm);
OleDbDataAdapter oledbAdapter = new OleDbDataAdapter();
oledbAdapter.InsertCommand = new OleDbCommand(strQuery, conn, transaction);
oledbAdapter.InsertCommand.ExecuteNonQuery();
string strQuery1 = "UPDATE Account SET Account.Balance= " + dtMain.balanceAmount +
" WHERE Account.ID=" +
dtMain.accountId;
//connectAndInsertAccess(strQuery1, dbPathm);
OleDbDataAdapter oledbAdapter1 = new OleDbDataAdapter();
oledbAdapter1.InsertCommand = new OleDbCommand(strQuery1, conn, transaction);
oledbAdapter1.InsertCommand.ExecuteNonQuery();
transaction.Commit();
conn.Close();
}
catch (Exception e)
{
transaction.Rollback();
throw;
}
}
/// <summary>
/// ثبت سند
/// </summary>
/// <param name="dtMain"></param>
/// <param name="clintName"></param>
/// <returns></returns>
public Boolean vocherPosting(PaymentStruct dtMain, string clintName)
{
vocher(dtMain);
PaymentStruct dtCash = dtMain;
dtCash.description = dtMain.description + " مربوط به مشتری " + clintName;
var clientSGHInfo = fillClientByClientNumber("000");
dtCash.balanceAmount = dtMain.amount + Convert.ToDecimal(clientSGHInfo.Rows[0]["Balance"].ToString());
dtCash.accountId = 4;
vocher(dtCash);
return(true);
}
/// <summary>
///
/// </summary>
/// <param name="loan"></param>
/// <returns></returns>
public int insertLoan(Loan.LoanStruct loan)
{
OleDbConnection conn;
try
{
conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.10.0;Data Source=" + dbPathm);
conn.Open();
}
catch
{
try
{
conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + dbPathm);
conn.Open();
}
catch
{
try
{
conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.14.0;Data Source=" + dbPathm);
conn.Open();
}
catch
{
conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.15.0;Data Source=" + dbPathm);
conn.Open();
}
}
}
#region sample
// DataSet dataSetLoan = new Database11DataSet();
// DataRow dataRowLoan = dataSetLoan.Tables["Loan"].NewRow();
// dataRowLoan["Client_id"] = loan.clientId;
// dataRowLoan["Grant_Date"] = loan.grantDate;
// dataRowLoan["Installment_Count"] = loan.longTimeLoan;
// dataRowLoan["Installment_Amount"] = loan.installmentAmount;
// dataRowLoan["Exp_Date"] = loan.expDate;
// dataRowLoan["First_installment_date"] = loan.installmentFirstDate;
// dataRowLoan["Loan_amount"] = loan.amount;
// dataRowLoan["Loan_status"] = int.Parse("0");
// /*
// using (myTrans = myConnection.BeginTransaction(
// IsolationLevel.RepeatableRead, "SampleTransaction"))
// {
//
// }*/
//
//
//
// dataSetLoan.Tables["Loan"].Rows.Add(dataRowLoan);
//
//
// OleDbDataAdapter dAdapterLoan = new
// OleDbDataAdapter(new OleDbCommand("SELECT * FROM Loan", conn));
// OleDbCommandBuilder oOrdersCmdBuilder = new OleDbCommandBuilder(dAdapterLoan);
// dAdapterLoan.Update(dataSetLoan, "Loan");
//for (int i = 0; i < loan.longTimeLoan; i++)
//{
// DataRow dataRowInstallmentTable = dataSetLoan.Tables["InstallmentTable"].NewRow();
// dataRowInstallmentTable["Loan_ID"] = loanId;
// dataRowInstallmentTable["Installment_Number"] = loan.installmentTable[i].installmentNumber;
// dataRowInstallmentTable["DUE_DATE"] = loan.installmentTable[i].dueDate;
// dataRowInstallmentTable["Installment_Amount"] = loan.installmentTable[i].installmentAmount;
// dataRowInstallmentTable["Installment_Status"] = loan.installmentTable[i].installmentStatus;
// dataSetLoan.Tables["InstallmentTable"].Rows.Add(dataRowInstallmentTable);
//}
//OleDbDataAdapter dAdapterInstallmentTable = new
// OleDbDataAdapter(new OleDbCommand("SELECT * FROM InstallmentTable", conn));
//OleDbCommandBuilder oOrdersCmdBuilderInstallmentTable = new OleDbCommandBuilder(dAdapterInstallmentTable);
//dAdapterInstallmentTable.Update(dataSetLoan, "InstallmentTable");
//
//
// DataSet oDS = fillSchema("select * from InstallmentTable");
// //DataSet oDS = new Database11DataSet();
//
//
// DataTable pTable = oDS.Tables["Table"];
// pTable.TableName = "InstallmentTable";
//
//
// for (int i = 0; i < loan.longTimeLoan; i++)
// {
// DataRow dataRowInstallmentTable = oDS.Tables["InstallmentTable"].NewRow();
// dataRowInstallmentTable["Loan_ID"] = loanId;
// dataRowInstallmentTable["Installment_Number"] = loan.installmentTable[i].installmentNumber;
// dataRowInstallmentTable["DUE_DATE"] = loan.installmentTable[i].dueDate;
// dataRowInstallmentTable["Installment_Amount"] = loan.installmentTable[i].installmentAmount;
// dataRowInstallmentTable["Installment_Status"] = loan.installmentTable[i].installmentStatus;
// oDS.Tables["InstallmentTable"].Rows.Add(dataRowInstallmentTable);
// }
//
//
// updateTable(oDS, "select * from InstallmentTable");
//
#endregion
string strQuery =
"INSERT INTO Loan ( Client_id, Grant_Date, Installment_Count, Installment_Amount, Exp_Date, First_installment_date, Loan_amount, Loan_status,calc_date ) VALUES (" +
loan.clientId + ",'" + loan.grantDate + "'," + loan.longTimeLoan + "," + loan.installmentAmount +
",'" +
loan.expDate + "','" + loan.installmentFirstDate + "'," + loan.amount + " , 0, '" + loan.grantDate + "')";
connectAndInsertAccess(strQuery, dbPathm);
string strQuery2 = "select Top 1 * from loan " + " where (((loan.Client_id)= " + loan.clientId + ")) ORDER BY loan.ID DESC";
DataTable loanDt = connectAndQueryAccess(strQuery2, dbPathm);
int loanId = (int)loanDt.Rows[0]["id"];
var transaction = conn.BeginTransaction();
try
{
for (int i = 0; i < loan.longTimeLoan; i++)
{
string strQuery1 =
"INSERT INTO InstallmentTable ( Loan_ID, Installment_Number, DUE_DATE, Installment_Amount, Installment_Status ) VALUES (" +
loanId + "," + loan.installmentTable[i].installmentNumber + ",'" +
loan.installmentTable[i].dueDate + "'," +
loan.installmentTable[i].installmentAmount + "," + loan.installmentTable[i].installmentStatus +
")";
OleDbDataAdapter oledbAdapterInstallmentTable = new OleDbDataAdapter();
oledbAdapterInstallmentTable.InsertCommand = new OleDbCommand(strQuery1, conn, transaction);
oledbAdapterInstallmentTable.InsertCommand.ExecuteNonQuery();
}
transaction.Commit();
conn.Close();
PaymentStruct dtLoan = new PaymentStruct();
dtLoan.accountId = 4;
dtLoan.amount = (-1) * loan.amount;
dtLoan.paymentFlag = false;
dtLoan.description = "اعطای تسهیلات :" + loanId;
dtLoan.paymentDate = loan.grantDate;
//مشتری صندوق
var clientSGHInfo = fillClientByClientNumber("000");
dtLoan.balanceAmount = dtLoan.amount + Convert.ToDecimal(clientSGHInfo.Rows[0]["Balance"].ToString());
vocher(dtLoan);
PaymentStruct dtcom = new PaymentStruct();
//مشتری کارمزد
var comInfo = fillClientByClientNumber("002");
dtcom.amount = loan.amount * (decimal)0.01;
dtcom.balanceAmount = dtcom.amount + Convert.ToDecimal(comInfo.Rows[0]["Balance"].ToString());
dtcom.description = "کارمزد تسهیلات :" + loanId;
dtcom.accountId = 80;
dtcom.paymentDate = loan.grantDate;
dtcom.paymentFlag = true;
vocher(dtcom);
return(loanId);
}
catch (Exception e)
{
transaction.Rollback();
throw;
}
}
