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