private void btnReplenish_Click(object sender, EventArgs e)
{
if (clsAccess.checkForInsertRestriction(lblTitle.Text, Classes.clsUser.Username) != true)
{
return;
}
if (dataGridView1.Rows.Count == 0)
{
Alert.show("No record(s) found.", Alert.AlertType.warning);
return;
}
string msg = Environment.NewLine + "Are you sure you want to proceed?";
DialogResult result = MessageBox.Show(this, msg, "PLDT Credit Cooperative", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (result == DialogResult.Yes)
{
//=================================================================================
//COnnection
//=================================================================================
using (SqlConnection con = new SqlConnection(global.connectString()))
{
con.Open();
string jvNo;
//=================================================================================
//Create JV For Replenished Withdrawal
//=================================================================================
//=================================================================================
//JOURNAL HEADER
//=================================================================================
SqlCommand cmdHeader = new SqlCommand();
cmdHeader.Connection = con;
cmdHeader.CommandText = "sp_InsertJournalHeader";
cmdHeader.CommandType = CommandType.StoredProcedure;
cmdHeader.Parameters.AddWithValue("@JV_Date", DateTime.Today.ToShortDateString());
cmdHeader.Parameters.AddWithValue("@userID", DBNull.Value);
cmdHeader.Parameters.AddWithValue("@AdjTo", DBNull.Value);
cmdHeader.Parameters.AddWithValue("@Particular", "REPLENISHMENT OF SAVINGS WITHDRAWAL");
cmdHeader.Parameters.AddWithValue("@Loan_No", DBNull.Value);
cmdHeader.Parameters.AddWithValue("@Posted", true);
cmdHeader.Parameters.AddWithValue("@Transaction_Type", "TRAN001");
cmdHeader.Parameters.AddWithValue("@summarize", true);
cmdHeader.Parameters.AddWithValue("@Prepared_By", Classes.clsUser.Username);
cmdHeader.Parameters.AddWithValue("@Poseted_By", Classes.clsUser.Username);
cmdHeader.ExecuteNonQuery();
//=================================================================================
//GET JV
//=================================================================================
SqlCommand cmdJV = new SqlCommand();
cmdJV.Connection = con;
cmdJV.CommandText = "sp_GetJVNoAfterSaving";
cmdJV.CommandType = CommandType.StoredProcedure;
cmdJV.Parameters.AddWithValue("@jv_date", DateTime.Today.ToShortDateString());
cmdJV.Parameters.AddWithValue("@Prepared_By", Classes.clsUser.Username);
SqlDataAdapter adapter = new SqlDataAdapter(cmdJV);
DataTable dt = new DataTable();
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
jvNo = dt.Rows[0].ItemArray[0].ToString();
}
else
{
return;
}
//=================================================================================
//JOURNAL DETAILS
//=================================================================================
if (dataGridView1.Rows.Count > 0)
{
foreach (DataGridViewRow row in dataGridView1.Rows)
{
//Adding all the Debit 300.1 code
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "sp_InsertJournalDetail";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@JV_No", jvNo);
cmd.Parameters.AddWithValue("@Account_Code", "300.1");
cmd.Parameters.AddWithValue("@userID", Convert.ToInt32(row.Cells["userID"].Value));
cmd.Parameters.AddWithValue("@Subsidiary_Code", row.Cells["EmployeeID"].Value);
cmd.Parameters.AddWithValue("@Loan_No", DBNull.Value);
cmd.Parameters.AddWithValue("@Debit", Convert.ToDecimal(row.Cells["AmtWithdrawn"].Value));
cmd.Parameters.AddWithValue("@Credit", Convert.ToDecimal("0.00"));
cmd.ExecuteNonQuery();
}
SqlCommand cmd1 = new SqlCommand();
cmd1.Connection = con;
cmd1.CommandText = "sp_InsertJournalDetail";
cmd1.CommandType = CommandType.StoredProcedure;
cmd1.Parameters.AddWithValue("@JV_No", jvNo);
cmd1.Parameters.AddWithValue("@Account_Code", "104");
cmd1.Parameters.AddWithValue("@userID", DBNull.Value);
cmd1.Parameters.AddWithValue("@Subsidiary_Code", DBNull.Value);
cmd1.Parameters.AddWithValue("@Loan_No", DBNull.Value);
cmd1.Parameters.AddWithValue("@Debit", Convert.ToDecimal("0.00"));
cmd1.Parameters.AddWithValue("@Credit", Convert.ToDecimal(txtAmount.Text));
cmd1.ExecuteNonQuery();
}
//=================================================================================
//Replenish Withdrawal
//=================================================================================
if (dataGridView1.Rows.Count > 0)
{
//Get all details of withdrawal slip in temp table
foreach (DataGridViewRow row in dataGridView1.Rows)
{
//For Saving of Replenishment and JVNO
SqlCommand cmd2 = new SqlCommand();
cmd2.Connection = con;
cmd2.CommandText = "sp_UpdateReplenishAndJVNoWithdrawalSlip";
cmd2.CommandType = CommandType.StoredProcedure;
cmd2.Parameters.AddWithValue("@Withdrawal_Slip_No", row.Cells["Withdrawal_Slip_No"].Value);
cmd2.Parameters.AddWithValue("@JV_No", jvNo);
cmd2.Parameters.AddWithValue("@Posted_By", Classes.clsUser.Username);
cmd2.ExecuteNonQuery();
}
}
//=================================================================================
//Call Disbursement
//=================================================================================
DisbursementVoucher cv = new DisbursementVoucher();
cv.ForReplenishment();
foreach (Form form in Application.OpenForms)
{
if (form.GetType() == typeof(DisbursementVoucher))
{
form.Activate();
DisbursementVoucher cv1 = new DisbursementVoucher();
cv1 = (DisbursementVoucher)Application.OpenForms["DisbursementVoucher"];
cv1.dataGridView1.Rows[0].Cells[3].Value = txtAmount.Text; //==Debit
cv1.dataGridView1.Rows[1].Cells[4].Value = txtAmount.Text; //==Credit
cv1.cmbTransaction.SelectedValue = "TRAN001";
cv1.cmbBank.SelectedValue = "PCIB";
cv1.txtAmount.Text = txtAmount.Text;
cv1.txtParticular.Text = "REPLENISHMENT OF SAVINGS WITHDRAWAL - IN REFERENCE TO JV#" + jvNo;
cv1.radioClient.Checked = true;
cv1.txtPayee.Text = "CASHIER";
cv1.txtPayeeName.Text = clsCashReplenishment.getCashier();
return;
}
}
cv.dataGridView1.Rows.Add(1);
//for (int i = 0; i < 1; i++)
//{
// cv.dataGridView1.Rows[i].Cells[3].Value = txtAmount.Text; //==Debit
// cv.dataGridView1.Rows[1].Cells[4].Value = txtAmount.Text; //==Credit
//}
cv.getBankFromWithdrawal = "";
cv.getTransaction = "TRAN001";
cv.cmbBank.SelectedValue = "PCIB";
cv.txtAmount.Text = txtAmount.Text;
cv.txtParticular.Text = "REPLENISHMENT OF SAVINGS WITHDRAWAL - IN REFERENCE TO JV#" + jvNo;
cv.radioClient.Checked = true;
cv.txtPayee.Text = "CASHIER";
cv.txtPayeeName.Text = clsCashReplenishment.getCashier();
cv.dataGridView1.Rows[0].Cells[0].Value = "104";
cv.dataGridView1.Rows[0].Cells[3].Value = txtAmount.Text;
int rowId = cv.dataGridView1.Rows.Add();
// Grab the new row!
DataGridViewRow row2 = cv.dataGridView1.Rows[rowId];
// Add the data
row2.Cells[0].Value = "102.17";
row2.Cells[4].Value = txtAmount.Text;
//put jv to disbursement
cv.jvno = jvNo;
//=================================================================================
//Load Data Replenish
//=================================================================================
clsCashReplenishment.loadReplenishment(dataGridView1, Convert.ToInt32(txtRangingFrom.Text), Convert.ToInt32(txtRangingTo.Text), dtFrom.Text, dtTo.Text, txtTotalNoSlips, txtAmount);
//=================================================================================
//MessageBox Before Showing Disbursement
//=================================================================================
this.Close();
cv.Show();
}
Alert.show("Cash withdrawal successfully replenished.", Alert.AlertType.success);
} //User Click No For Return
else
{
return;
}
}
public void btnRelease_Click(object sender, EventArgs e)
{
if (txtWithdrawalSlipNo.Text == "")
{
Alert.show("No withdrawal for release.", Alert.AlertType.error);
return;
}
if (status.Text == "POSTED")
{
Alert.show("This Withdrawal Already Posted!", Alert.AlertType.warning);
return;
}
if (txtCancelledBy.Text != "")
{
Alert.show("This Withdrawal Already Cancelled!", Alert.AlertType.error);
return;
}
if (dtReleaseDate.Text != "")
{
Alert.show("This Withdrawal Already Released!", Alert.AlertType.error);
return;
}
using (SqlConnection con = new SqlConnection(global.connectString()))
{
con.Open();
if (radioCheque.Checked == true)
{
SqlDataAdapter adapter = new SqlDataAdapter("SELECT CV_No FROM Withdrawal_Slip WHERE Withdrawal_Slip_No = '" + txtWithdrawalSlipNo.Text + "'", con);
DataTable dt = new DataTable();
adapter.Fill(dt);
if (dt.Rows[0].ItemArray[0].ToString() != "")
{
}
}
string msg = Environment.NewLine + "Are you sure you want to release this?";
DialogResult result = MessageBox.Show(this, msg, "PLDT Credit Cooperative", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (result == DialogResult.Yes)
{
if (btnRelease.Text == "RELEASE")
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "sp_ReleaseWithdrawalDate";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Withdrawal_Slip_No", txtWithdrawalSlipNo.Text);
cmd.ExecuteNonQuery();
//Put the date to the textbox
dtReleaseDate.Text = Convert.ToDateTime(clsSavingsDataEntry.returnReleaseDate(txtWithdrawalSlipNo.Text)).ToShortDateString();
status.Visible = true;
status.Text = "RELEASED";
//refresh
savings = (Savings)Application.OpenForms["Savings"];
savings.refreshData();
btnSave.Text = "NEW";
//=================================================================================
//MessageBox Before Showing Disbursement
//=================================================================================
this.Close();
Alert.show("Successfully Released!", Alert.AlertType.success);
}
//CHECK IF WITHDRAWABLE METHOD IS CHECK
//IF CHECK GO TO DISBURSEMENT AND SAVE THE RELEASED VALUE
//===========================================================================================================
if (radioCheque.Checked == true)
{
//Put true for disbursement posting if withdrawal from check
Classes.clsDisbursement.releaseCashWithdrawal = true;
Classes.clsDisbursement.slipFromWithdrawal = txtWithdrawalSlipNo.Text;
//=================================================================================
//Call Disbursement
//=================================================================================
DisbursementVoucher cv = new DisbursementVoucher();
//03-10-2019
cv.ForReplenishment();
cv.fromWithdrawal = true;
cv.dataGridView1.Rows.Add(1);
cv.getTransaction = "TRAN001";
cv.getBankFromWithdrawal = cmbBankName.SelectedValue.ToString();
Classes.clsDisbursement.userID = Classes.clsSavingsDataEntry.userID;
cv.txtChequeNo.Text = txtCheque.Text;
cv.txtAmount.Text = txtAmountWithdrawn.Text;
slip = txtWithdrawalSlipNo.Text;
slip = slip.Substring(12, 4);
cv.txtParticular.Text = "Withdrawal of Savings per Slip receipt no. " + slip + " Dated : " + DateTime.Now.ToShortDateString();
cv.radioMember.Checked = true;
cv.txtPayee.Text = txtEmployeeID.Text;
cv.txtPayeeName.Text = txtName.Text;
cv.dataGridView1.Rows[0].Cells[0].Value = "300.1";
cv.dataGridView1.Rows[0].Cells[3].Value = txtAmountWithdrawn.Text;
//Put the Subsidiary or the Member ID and Name in Disbursement Details
cv.dataGridView1.Rows[0].Cells[1].Value = txtEmployeeID.Text + " - " + txtName.Text;
cv.dataGridView1.Rows[0].Cells[5].Value = Classes.clsSavingsDataEntry.userID;
int rowId = cv.dataGridView1.Rows.Add();
// Grab the new row!
DataGridViewRow row2 = cv.dataGridView1.Rows[rowId];
//Added March 10 , 2019 as Per Sir Manny Request For Cheque Release
SqlDataAdapter adapterBank = new SqlDataAdapter("SELECT Bank_Account_Code FROM Bank WHERE Bank_Code ='" + cmbBankName.SelectedValue.ToString() + "'", con);
DataTable dtBank = new DataTable();
adapterBank.Fill(dtBank);
// Add the data
row2.Cells[0].Value = dtBank.Rows[0].ItemArray[0].ToString();
row2.Cells[4].Value = txtAmountWithdrawn.Text;
cv.btnEdit.Enabled = false;
cv.btnPost.Enabled = true;
cv.btnCancel.Enabled = true;
cv.btnPrint.Enabled = true;
cv.btnPrintCheque.Enabled = true;
cv.btnSearch.Enabled = false;
cv.dataGridView1.Enabled = false;
cv.txtChequeNo.Enabled = false;
cv.dtChequeDate.Enabled = false;
cv.txtAmount.Enabled = false;
cv.txtParticular.Enabled = false;
cv.btnSearchPayee.Enabled = false;
cv.btnSearchLoan.Enabled = false;
cv.btnNew.Enabled = false;
cv.btnNew.Text = "NEW";
//BEFORE NEXT WINDOW REMOVE THE OPEN TRANSACTION
//============================================================
// REMOVE OPEN FORM
//============================================================
clsOpen.deleteTransaction("Savings", txtWithdrawalSlipNo.Text);
cv.Show();
//=================================================================================
//SAVING DISBURSEMENT
//=================================================================================
SqlCommand cmdHeader = new SqlCommand();
cmdHeader.Connection = con;
cmdHeader.CommandText = "sp_InsertDisbursementHeader";
cmdHeader.CommandType = CommandType.StoredProcedure;
cmdHeader.Parameters.AddWithValue("@CVDate", DateTime.Now.ToShortDateString());
//FOr Payee Type
//Member = 0 Client = 1
if (cv.radioMember.Checked == true)
{
cmdHeader.Parameters.AddWithValue("@Payee_Type", "0");
}
else
{
cmdHeader.Parameters.AddWithValue("@Payee_Type", "1");
}
//Check if Theres a Member or Client
if (Classes.clsDisbursement.userID.ToString() == "")
{
cmdHeader.Parameters.AddWithValue("@userID", DBNull.Value);
}
else if (Classes.clsDisbursement.userID == 0)
{
cmdHeader.Parameters.AddWithValue("@userID", DBNull.Value);
}
else
{
cmdHeader.Parameters.AddWithValue("@userID", Classes.clsDisbursement.userID.ToString());
}
cmdHeader.Parameters.AddWithValue("@Payee", cv.txtPayee.Text);
cmdHeader.Parameters.AddWithValue("@Payee_Name", cv.txtPayeeName.Text);
cmdHeader.Parameters.AddWithValue("@Particulars", cv.txtParticular.Text);
cmdHeader.Parameters.AddWithValue("@Loan_No", cv.txtLoanNo.Text);
cmdHeader.Parameters.AddWithValue("@Bank_Code", cv.cmbBank.SelectedValue);
cmdHeader.Parameters.AddWithValue("@Check_No", cv.txtChequeNo.Text);
cmdHeader.Parameters.AddWithValue("@Check_Date", cv.dtChequeDate.Text);
cmdHeader.Parameters.AddWithValue("@Amount", Convert.ToDecimal(cv.txtAmount.Text));
cmdHeader.Parameters.AddWithValue("@Transaction_Type", cv.cmbTransaction.SelectedValue);
cmdHeader.Parameters.AddWithValue("@Prepared_By", Classes.clsUser.Username);
cmdHeader.ExecuteNonQuery();
//Get The CV NO.
SqlCommand cmdCV = new SqlCommand();
cmdCV.Connection = con;
cmdCV.CommandText = "sp_GetCVNoAfterSaving";
cmdCV.CommandType = CommandType.StoredProcedure;
cmdCV.Parameters.AddWithValue("@CV_Date", DateTime.Now.ToShortDateString());
cmdCV.Parameters.AddWithValue("@Prepared_By", Classes.clsUser.Username);
SqlDataAdapter adapter = new SqlDataAdapter(cmdCV);
DataTable dt = new DataTable();
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
cv.txtCVNo.Text = dt.Rows[0].ItemArray[0].ToString();
}
else
{
return;
}
//Insert CV Details
//SAVE DETAILS ============================================================================================
if (cv.dataGridView1.Rows.Count > 0)
{
foreach (DataGridViewRow row in cv.dataGridView1.Rows)
{
if (row.Cells[0].Value != null)
{
SqlCommand cmdDetail = new SqlCommand();
cmdDetail.Connection = con;
cmdDetail.CommandText = "sp_InsertDisbursementDetail";
cmdDetail.CommandType = CommandType.StoredProcedure;
cmdDetail.Parameters.AddWithValue("@CV_No", cv.txtCVNo.Text);
cmdDetail.Parameters.AddWithValue("@Account_Code", row.Cells[0].Value);
if (Convert.ToInt32(row.Cells[5].Value) == 0)
{
cmdDetail.Parameters.AddWithValue("@userID", DBNull.Value);
}
else
{
cmdDetail.Parameters.AddWithValue("@userID", Convert.ToInt32(row.Cells[5].Value));
}
if (row.Cells[1].Value != null)
{
cmdDetail.Parameters.AddWithValue("@Subsidiary_Code", row.Cells[1].Value);
}
else
{
cmdDetail.Parameters.AddWithValue("@Subsidiary_Code", DBNull.Value);
}
if (row.Cells[2].Value != null)
{
cmdDetail.Parameters.AddWithValue("@Loan_No", row.Cells[2].Value);
}
else
{
cmdDetail.Parameters.AddWithValue("@Loan_No", DBNull.Value);
}
cmdDetail.Parameters.AddWithValue("@Debit", Convert.ToDecimal(row.Cells[3].Value));
cmdDetail.Parameters.AddWithValue("@Credit", Convert.ToDecimal(row.Cells[4].Value));
cmdDetail.ExecuteNonQuery();
}
}
}
//=================================================================================
// SAVE WITHDRAWAL SLIP FOR CHANGING PURPOSES
//=================================================================================
SqlCommand cmdWD = new SqlCommand();
cmdWD.Connection = con;
cmdWD.CommandText = "UPDATE Disbursement_Header SET wd_slip_no = '" + txtWithdrawalSlipNo.Text + "' WHERE CV_No = '" + cv.txtCVNo.Text + "'";
cmdWD.CommandType = CommandType.Text;
cmdWD.ExecuteNonQuery();
//=================================================================================
// SAVE CV NO FOR WITHDRAWAL SLIP
//=================================================================================
SqlCommand cmdWDCV = new SqlCommand();
cmdWDCV.Connection = con;
cmdWDCV.CommandText = "UPDATE Withdrawal_Slip SET cv_no = '" + cv.txtCVNo.Text + "' WHERE Withdrawal_Slip_No = '" + txtWithdrawalSlipNo.Text + "'";
cmdWDCV.CommandType = CommandType.Text;
cmdWDCV.ExecuteNonQuery();
Alert.show("Withdrawal voucher successfully prepared.", Alert.AlertType.success);
SqlDataAdapter adapterchck = new SqlDataAdapter("SELECT CV_No FROM Withdrawal_Slip WHERE Withdrawal_Slip_No = '" + txtWithdrawalSlipNo.Text + "'", con);
DataTable dtchck = new DataTable();
adapterchck.Fill(dtchck);
status.Text = "FOR RELEASE - CV#" + dtchck.Rows[0].ItemArray[0].ToString();
status.Visible = true;
btnRelease.Enabled = false;
}
}
else
{
return;
}
}
}