0
Answer

show message input string was not in a correct format

jishmi raj

jishmi raj
10y
594
1
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Mahallu.DAL;
using Mahallu.BAL;
using Mahallu.General;
using System.Text.RegularExpressions;
using System.Data.SqlClient;
namespace Mahallu.UI
{
public partial class frmSubsrDetails : Form
{
SubcriptionDetBAL subBal = new SubcriptionDetBAL();
SubcriptionDetDAL subDal = new SubcriptionDetDAL();
SubCollectionBAL subcollBal = new SubCollectionBAL();
SubCollectionDAL subcollDal = new SubCollectionDAL();
BalanceSheetBAL balncBal = new BalanceSheetBAL();
BalanceSheetDAL balanceDal = new BalanceSheetDAL();
MemberDetailsBAL memBAL=new MemberDetailsBAL();
MemberDetailsDAL memDAL=new MemberDetailsDAL();
HomeDetailsBAL hombal = new HomeDetailsBAL();
HomeDetailsDAL homeDal = new HomeDetailsDAL();
private BindingSource source = new BindingSource();
dbConnection db = new dbConnection();
DataTable dtGrade = new DataTable();
private BindingSource source1 = new BindingSource();
public static string subID;
public static string FmId;
public static string grdAmnt;
public static string subcollId;
public static string maharegno;
public static string housname;
public static string grdtype;
public static string amntpaid;
public static string Oldbalance;
public static string balnce;
public static string date;
public static string PrevAmount;
public static string MemberId;
public static string subMcolId;
public static string ReceiptNo;
string FinanceID;
FinanceYearBAL finbal = new FinanceYearBAL();
FinancialYearDAL findal = new FinancialYearDAL();
public frmSubsrDetails()
{
InitializeComponent();
}
private void btnSave_Click(object sender, EventArgs e)
{
Boolean flag = true;
if(cmbSubType .SelectedIndex ==-1)//! //if (txtGradeName.Text =="")
{
flag = false ;
errorProvider1.SetError(cmbSubType, "Reqiured");//txtGradeName
}
if (txtAmount .Text == "")
{
flag = false;
errorProvider1.SetError(txtAmount, "Reqiured");
}
if (flag == true)
{
errorProvider1.Clear();
subBal.Grade = cmbSubType.SelectedItem.ToString(); ; //txtGradeName.Text;
subBal.Amount = txtAmount.Text;
subBal.SubsPeriod = txtSubPeriod.Text;
subBal.Description = txtDescri.Text;
subBal.Mode = "INSERT";
subDal.insertSubs(subBal);
MessageBox.Show("Saved");
GridBind();
}
}
private void frmSubsrDetails_Load(object sender, EventArgs e)
{
GridBind();
dgvSubsFamlyBind();
gridSubColln();
gridSearchSubColln();
TotalAmounts();
AllMembers();
gridMembSubColln();
GrpSubsMembers();
FamilyGropBinding();
if (db.Language() == "MAL")
{
Malyalamversion();
}
if (db.User == "User")
{
btnClear.Enabled = false;
btnDelete.Enabled = false;
btnMClear.Enabled = false;
btnMSave.Enabled = false;
btnMUpdate.Enabled = false;
btnNew.Enabled = false;
btnSave.Enabled = false;
btnsubcolClear.Enabled = false;
btnSubCollSave.Enabled = false;
btnSubCollUpdate.Enabled = false;
btnUpdate.Enabled = false;
dgvSubscripItems.Enabled = false;
dgvSuCollen.Enabled = false;
dgvMembColn.Enabled = false;
}
GetFinID();
}
private void GrpSubsMembers()
{
string slctmmbrs="SELECT tbl_MemberId.MemberId, tbl_MemberId.Memb_Id, tbl_MemberId.Name, tbl_MemberId.SubGrade, tbl_Subs.Amount FROM tbl_MemberId INNER JOIN tbl_Subs ON tbl_MemberId.SubGrade = tbl_Subs.Grade";
db.cmd = new SqlCommand(slctmmbrs, db.Connect());
DataTable dtMmbrs= new DataTable();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtMmbrs);
if (dtMmbrs.Rows.Count > 0)
{
dgvMemberrGP.DataSource = dtMmbrs;
dgvMemberrGP.Columns[1].Visible = false;
dgvMemberrGP.Columns["Month2"].DisplayIndex = 5;
}
}
public void GetFinID()
{
finbal.Mode = "SelectActive";
db.dt = findal.ViewFinYear(finbal);
if (db.dt.Rows.Count > 0)
{
lblFYMsg.Text = lblCFY_GM.Text = lblCFYGRP.Text = db.dt.Rows[0][2].ToString() + " To " + db.dt.Rows[0][3].ToString();
lblFinID.Text = db.dt.Rows[0][0].ToString();
string from = db.dt.Rows[0]["FinYearFrom"].ToString();
string to = db.dt.Rows[0]["FinYearTo"].ToString();
}
}
private void GridBind()
{
subBal.Mode = "SELECT_ALL";
db.dt =dtGrade = subDal.Select_ALL(subBal);
if (db.dt.Rows.Count > 0)
{
dgvSubscripItems.DataSource = db.dt;
dgvSubscripItems.EnableHeadersVisualStyles = false;
dgvSubscripItems.ColumnHeadersDefaultCellStyle.BackColor = Color.BlueViolet;
dgvSubscripItems.ColumnHeadersDefaultCellStyle.ForeColor = Color.White;
dgvSubscripItems.Columns[0].Visible = false;
dgvSubscripItems.Columns[1].HeaderText = "Subs:Period";
dgvSubscripItems.Columns[2].HeaderText = "Subs:Type Name";
dgvSubscripItems.Columns[3].HeaderText = "Amount";
dgvSubscripItems.Columns[4].HeaderText = "Description";
}
else
{
cmbSubType.Focus();//txtGradeName
}
}
private void dgvSubsFamlyBind()
{
DataTable dt = new DataTable();
dt = homeDal.Select4Subscription();
if (dt.Rows.Count > 0)
{
dgvSubsSelect.DataSource = dt;
}
else
{
MessageBox.Show("No Details Available");
}
}
private void AllMembers()
{
DataSet ds = new DataSet();
ds = memDAL.SelectAll();
if (ds.Tables[2].Rows.Count > 0)
{
dgvMembView.DataSource = ds.Tables[2];
dgvMembView.Columns[0].Visible = false;
}
else
{
MessageBox.Show("There is no Member's available in your DataBase");
}
}
private void Malyalamversion()
{
gbAddSubs.Text = "????????";
gbAddSubs.Font = new Font("meera", 11, FontStyle.Bold);
lblgradename.Text = "???????? ???";
lblDescri.Text = "??????";
lblSubPeriod.Text = "???????";
lblAmount.Text = label8.Text = "???";
gbSubCollec.Text = "???????? ???????‍";
gbSubCollec.Font = new Font("meera", 9, FontStyle.Regular );
gbSubStatus.Text = "???????? ??????";
lblMahRegNo.Text = label16.Text = "??:???:?:";
lblHousNam.Text = "??????????";
lblGradeType.Text = label7 .Text = "???????? ???";
lblAmountPaid.Text = "????? ???";
lblBalance.Text = label10.Text = "??????";
lblDate.Text = label18.Text = "?????";
gbSubSearch.Text = "???????? ?????????‍";
gbSubSearch.Font = new Font("meera", 9, FontStyle.Regular);
lblSearchBy.Text = "???????? ????";
lblTotalAmount.Text = label9.Text = label32.Text = "??? ???";
lblTotalAmountCollectd.Text = "??? ??????? ???";
lblBalanceTotal.Text = "??? ??????";
gbSubStatusM.Text = "???????? ?????????‍";
gbMembSubColn.Text = "????????";
gbMembSubColn.Font = new Font("meera", 9, FontStyle.Regular);
lblMemberMalId.Text = lblMembID.Text = "???????‍ ? ??";
lblNameM.Text = "????";
lblGradeTypeM.Text = label34.Text = "???????? ???";
lblAmonpaidM.Text = "????? ???";
lblBalM.Text = "??????";
lblDateM.Text = label24.Text = "?????";
lblOldBalance.Text = "????‍????? ?????‍??‌";
rbFamily.Text="????";
rbMember.Text="????";
lbl1.Text = label33 .Text = "?????? ???";
lbl2.Text="????? ???";
lbl3.Text = label31 .Text = "?????‍??";
lbl4.Text = label13 .Text = "????‍?????\n"+"?????‍??‌";
lblCFY.Text = lblCFY1.Text = "?????????? ???‍??";
lblPrvFyearbala.Text = label20.Text = "?????? ???‍?????\n" + "?????‍??";
label6.Text = "??????";
lblHousGrpName.Text = "??????????";
lblMembrNm_.Text = "";
}
private void btnClear_Click(object sender, EventArgs e)
{
txtAmount.Text = "";
txtDescri.Text = "";
// txtGradeName.Text = "";
cmbSubType.SelectedIndex = -1;
txtSubPeriod.Text = "";
}
private void dgvSubscripItems_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
subID = dgvSubscripItems .CurrentRow.Cells[0].Value.ToString();
txtAmount.Text = dgvSubscripItems.CurrentRow.Cells[3].Value.ToString();
txtDescri.Text = dgvSubscripItems.CurrentRow.Cells[4].Value.ToString();
// cmbSubType.SelectedIndex = -1;
cmbSubType.SelectedItem = dgvSubscripItems.CurrentRow.Cells[2].Value.ToString();
txtSubPeriod.Text = dgvSubscripItems.CurrentRow.Cells[1].Value.ToString();
btnUpdate.Visible = true;
btnSave.Visible = false;
btnDelete.Visible = true;
btnNew.Visible = true;
}
private void btnUpdate_Click(object sender, EventArgs e)
{
subBal.SubsId = subID;
subBal.SubsPeriod = txtSubPeriod.Text;
subBal.Grade = cmbSubType.SelectedItem.ToString();//
subBal.Amount = txtAmount.Text;
subBal.Description = txtDescri.Text;
subBal.Mode = "UPDATE";
subDal.UpdateSubs(subBal);
MessageBox.Show("Updated");
GridBind();
}
private void btnNew_Click(object sender, EventArgs e)
{
btnSave.Visible = true;
btnClear.Visible = true;
btnUpdate.Visible = false;
btnDelete.Visible = false;
txtSubPeriod.Text = "";
cmbSubType.SelectedIndex = -1; //txtGradeName.Text = "";
txtDescri.Text = "";
txtAmount.Text = "";
cmbSubType.Focus();
}
private void label8_Click(object sender, EventArgs e)
{
}
private void btnDelete_Click(object sender, EventArgs e)
{
if (MessageBox.Show("Are You Sure Want to Delete", "Delete", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) == DialogResult.OK)
{
subBal.SubsId = subID;
subBal.Mode = "DELETE";
subDal.DeleteSubs(subBal);
MessageBox.Show("deleted");
GridBind();
}
}
private void TotalAmounts()
{
int sum1 = 0,sum2=0,sum3=0;
for (int i = 0; i < this.dgvSearchSubs .Rows.Count; i++)
{
if (dgvSearchSubs.Rows[i].Cells[6].Value != null)
{
sum1 += Convert.ToInt32(this.dgvSearchSubs[6, i].Value);
}
if (dgvSearchSubs.Rows[i].Cells[7].Value != null)
{
sum2 += Convert.ToInt32(this.dgvSearchSubs[7, i].Value);
}
if (dgvSearchSubs.Rows[i].Cells[8].Value != null)
{
sum3 += Convert.ToInt32(this.dgvSearchSubs[8, i].Value);
}
}
txtDispToatal.Text = sum1.ToString();
txtDispTotalamnt.Text = sum2.ToString();
txtBalnceTotal.Text = sum3.ToString();
}
private void dgvSubsSelect_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
FmId = dgvSubsSelect.CurrentRow.Cells[0].Value.ToString();
txtHouseName.Text = dgvSubsSelect.CurrentRow.Cells[1].Value.ToString();
txtxmhregno.Text = dgvSubsSelect.CurrentRow.Cells[2].Value.ToString();
txtGradeType.Text = dgvSubsSelect.CurrentRow.Cells[4].Value.ToString();
txtGradeType.Focus();
grdAmnt = "";
Oldbalance = "";
}
private void txtGradeType_Leave(object sender, EventArgs e)
{
}
private void txtAmountPaid_TextChanged(object sender, EventArgs e)
{
chbStatus.Visible = false;
try
{
//if (Updateamntpaid == "")
//{
grdAmnt = "";
subBal.Grade = txtGradeType.Text;
subBal.Mode = "GrdAmnt";
db.dt = subDal.SelectGradeAmnt(subBal);
if (db.dt.Rows.Count > 0)
{
grdAmnt = db.dt.Rows[0]["Amount"].ToString();
}
//float a = float.Parse(grdAmnt);
//float b = float.Parse(txtAmountPaid.Text);
int a = int.Parse(grdAmnt);
int b = int.Parse(txtAmountPaid.Text);
txtBalance.Text = (a - b).ToString();
if (Oldbalance != "")
{
if (b > float.Parse(Oldbalance))
//if(b>int.Parse(txtOldBalance.Text))
{
MessageBox.Show("Amount Paid Not Greater Than Fixed Subscription Amount");
txtBalance.Text = "";
txtBalance.Focus();
}
if (b <= float.Parse(Oldbalance))
//if(b <=float.Parse(txtOldBalance.Text))
{
float c = float.Parse(Oldbalance);
float d = float.Parse(txtAmountPaid.Text);
//int c=int.Parse(txtOldBalance.Text);
//int d = int.Parse(txtAmountPaid.Text);
txtBalance.Text = (c - d).ToString();
if (c - d == 0)
{
chbStatus.Visible = true;
}
}
}
//if (Updateamntpaid != "")
//{
// float c = float.Parse(Updateamntpaid);
// float d = float.Parse(txtAmountPaid.Text);
// txtBalance.Text = (c - d).ToString();
//}
//}
//else
//{
// float a = float.Parse(Updateamntpaid);
// float b = float.Parse(txtAmountPaid.Text);
// txtBalance.Text = (a - b).ToString();
//}
}
catch(Exception ex)
{
ex.Message.StartsWith("Input string was not in a correct format");
txtAmountPaid.Focus();
}
}
private void gridSubColln()
{
subcollBal.Mode = "SELECT_ALL";
db.dt= subcollDal.Select_ALL(subcollBal);
source1.DataSource = db.dt;
if (db.dt.Rows.Count > 0)
{
dgvSuCollen.DataSource = source1;
dgvSuCollen.Columns[0].Visible=false;
dgvSuCollen.Columns[1].Visible = false;
//dgvSuCollen.Columns[9].Visible = false;
dgvSuCollen.Columns[10].Visible = false;
dgvSuCollen.Columns[12].Visible = false;
dgvSuCollen.Columns[2].HeaderText = "House Name";
dgvSuCollen.Columns[3].HeaderText = "Mahal Reg No:";
dgvSuCollen.Columns[4].HeaderText = "Date";
dgvSuCollen.Columns[5].HeaderText = "Subs:Type";
dgvSuCollen.Columns[6].HeaderText = "Amount";
dgvSuCollen.Columns[7].HeaderText = "Amount Paid";
dgvSuCollen.Columns[8].HeaderText = "Balance";
dgvSuCollen.Columns[9].HeaderText = "Receipt No:";
dgvSuCollen.Columns[11].HeaderText = "Completed";
cmbMHRegNo.Items.Clear();
string str = "SELECT DISTINCT MahalluRegNo AS MahalluRegNo FROM tbl_SubCollection";
db.cmd = new SqlCommand(str, db.Connect());
db.adapter = new SqlDataAdapter(db.cmd);
DataTable dtMahreg = new DataTable();
db.adapter.Fill(dtMahreg);
for (int i = 0; i < dtMahreg.Rows.Count; i++)
{
cmbMHRegNo.Items.Add(dtMahreg.Rows[i]["MahalluRegNo"].ToString());
}
cmbMHRegNo.Items.Insert(0, "--Select--");
cmbMHRegNo.SelectedIndex = 0;
//dgvSuCollen.Columns[9].HeaderText = "Receipt No:";
}
}
private void gridMembSubColln()
{
subcollBal.Mode = "SELECT_ALL_MColn";
db.dt = subcollDal.Select_ALL(subcollBal);
if (db.dt.Rows.Count > 0)
{
dgvMembColn.DataSource = db.dt;
dgvMembColn.Columns[0].Visible = false;
//dgvMembColn.Columns[9].Visible = false;
dgvMembColn.Columns[10].Visible = false;
dgvMembColn.Columns[1].Visible = false;
dgvMembColn.Columns[2].HeaderText = "Member ID";
dgvMembColn.Columns[3].HeaderText = "Name";
dgvMembColn.Columns[4].HeaderText = "Date";
dgvMembColn.Columns[5].HeaderText = "Subs:Type";
dgvMembColn.Columns[6].HeaderText = "Amount";
dgvMembColn.Columns[7].HeaderText = "Amount Paid";
dgvMembColn.Columns[8].HeaderText = "Balance";
}
}
private void gridSearchSubColln()
{
subcollBal.Mode = "SELECT_ALL";
db.dt = subcollDal.Select_ALL(subcollBal);
source.DataSource = db.dt;
if (db.dt.Rows.Count > 0)
{
dgvSearchSubs.DataSource = source;
dgvSearchSubs.EnableHeadersVisualStyles = false;
dgvSearchSubs.ColumnHeadersDefaultCellStyle.BackColor = Color.DarkViolet ;
dgvSearchSubs.ColumnHeadersDefaultCellStyle.ForeColor = Color.White;
dgvSearchSubs.Columns[0].Visible = false;
dgvSearchSubs.Columns[1].Visible = false;
dgvSearchSubs.Columns[10].Visible = false;
dgvSearchSubs.Columns[12].Visible = false;
dgvSearchSubs.Columns[2].HeaderText = "House Name";
dgvSearchSubs.Columns[3].HeaderText = "Mahal Reg No:";
dgvSearchSubs.Columns[4].HeaderText = "Date";
dgvSearchSubs.Columns[5].HeaderText = "Subs:Type";
dgvSearchSubs.Columns[6].HeaderText = "Amount";
dgvSearchSubs.Columns[7].HeaderText = "Amount Paid";
dgvSearchSubs.Columns[8].HeaderText = "Balance";
dgvSearchSubs.Columns[9].HeaderText = "Receipt No:";
dgvSearchSubs.Columns[11].HeaderText = "Completed";
}
TotalAmounts();
}
private void gridMemberSearchSubColln()
{
subcollBal.Mode = "SELECT_ALL_MColn";
db.dt.Clear();
db.dt = subcollDal.Select_ALL(subcollBal);
source.DataSource = db.dt;
if (db.dt.Rows.Count > 0)
{
try
{
dgvSearchSubs.DataSource = source;
dgvSearchSubs.EnableHeadersVisualStyles = false;
dgvSearchSubs.ColumnHeadersDefaultCellStyle.BackColor = Color.DarkViolet;
dgvSearchSubs.ColumnHeadersDefaultCellStyle.ForeColor = Color.White;
dgvSearchSubs.Columns[0].Visible = false;
dgvSearchSubs.Columns[1].Visible = false;
dgvSearchSubs.Columns[2].HeaderText = "Memb ID";
dgvSearchSubs.Columns[3].HeaderText = "Name";
dgvSearchSubs.Columns[4].HeaderText = "Date";
dgvSearchSubs.Columns[5].HeaderText = "Grade";
dgvSearchSubs.Columns[6].HeaderText = "Amount";
dgvSearchSubs.Columns[7].HeaderText = "Amount Paid";
dgvSearchSubs.Columns[8].HeaderText = "Balance";
dgvSearchSubs.Columns[9].HeaderText = "Receipt No:";
}
catch
{
}
}
TotalAmounts();
}
private void btnSubCollSave_Click(object sender, EventArgs e)
{
Boolean flag = true;
if (txtAmountPaid .Text == "")
{
flag = false ;
errorProvider1.SetError(txtAmountPaid, "Reqiured");
}
if (txtReceiptNo.Text == "")
{
flag = false;
errorProvider1.SetError(txtReceiptNo, "Reqiured");
}
string str = "select ReceiptNo from tbl_SubCollection where ReceiptNo='" + txtReceiptNo.Text + "'";
db.cmd = new SqlCommand(str, db.Connect());
db.adapter = new SqlDataAdapter(db.cmd);
DataTable dtreciept = new DataTable();
db.adapter.Fill(dtreciept);
if (dtreciept.Rows.Count > 0)
{
flag = false;
MessageBox.Show("Receipt Number Already Exist", "Pay Balance");
}
if (flag == true)
{
errorProvider1.Clear();
subcollBal.FamilyId = FmId;
subcollBal.FinYearID = lblFinID.Text;
subcollBal.HouseName = txtHouseName.Text;
subcollBal.MahalluRegNo = txtxmhregno.Text;
subcollBal.Date = dtpDate.Text;
subcollBal.Grade = txtGradeType.Text;
subcollBal.SubAmount = grdAmnt;
subcollBal.AmountPaid = txtAmountPaid.Text;
subcollBal.Balance = txtBalance.Text;
subcollBal.ReceiptNo = txtReceiptNo.Text;
subcollBal.OldBalance = txtOldBalance.Text;
if (chbStatus.Checked == true)
{
subcollBal.Status = "1";
}
else
{ subcollBal.Status = "0"; }
subcollBal.Mode = "INSERT";
subcollDal.insertSubsCollection(subcollBal);
//string str = "select Max(SubCollID) as ID from tbl_SubCollection";
//db.cmd = new SqlCommand(str,db.Connect());
//db.adapter = new SqlDataAdapter(db.cmd);
//DataTable dt5 = new DataTable();
//db.adapter.Fill(dt5 );
balncBal.LedgerName = "Subscription";
balncBal.LedgerType = "Income";
balncBal.BillDate = System.DateTime.Now.ToShortDateString();
balncBal.Amount = txtAmountPaid.Text;
balncBal.StatusInCr = txtAmountPaid.Text;
balncBal.StatusOutDr = "0";
//balncBal.FK = dt5.Rows[0][0].ToString();
balncBal.FK = txtReceiptNo.Text;
balanceDal.InsertBalanceSheet(balncBal);
MessageBox.Show("Saved Successfully");
gridSubColln();
gridSearchSubColln();
}
}
private void dgvSuCollen_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
Oldbalance = "";
FmId = "";
lblTotAmntpaid.Visible = false ;
lblBalanceUptodate.Visible = false;
dgvSuCollen.CurrentRow.Cells[0].Value.ToString();
subcollId = dgvSuCollen.CurrentRow.Cells[0].Value.ToString();
FmId = dgvSuCollen.CurrentRow.Cells[1].Value.ToString();
txtHouseName.Text = dgvSuCollen.CurrentRow.Cells[2].Value.ToString();
txtxmhregno.Text = dgvSuCollen.CurrentRow.Cells[3].Value.ToString();
dtpDate.Text = dgvSuCollen.CurrentRow.Cells[4].Value.ToString();
Oldbalance = dgvSuCollen.CurrentRow.Cells[8].Value.ToString();
txtGradeType.Text = dgvSuCollen.CurrentRow.Cells[5].Value.ToString();
txtAmountPaid.Text = dgvSuCollen.CurrentRow.Cells[7].Value.ToString();
PrevAmount = dgvSuCollen.CurrentRow.Cells[7].Value.ToString();
txtBalance.Text = dgvSuCollen.CurrentRow.Cells[8].Value.ToString();
txtReceiptNo.Text = dgvSuCollen.CurrentRow.Cells[9].Value.ToString();
txtOldBalance.Text = dgvSuCollen.CurrentRow.Cells[10].Value.ToString();
FinanceID = dgvSuCollen.CurrentRow.Cells[12].Value.ToString();
btnSubCollSave.Visible = false;
btnSubCollUpdate.Visible = true;
btnPayBalance.Enabled = true;
btnDeleteSub.Enabled = true;
lblPayBalnce.Visible = true;
string str = "SELECT SUM(CAST(AmountPaid AS float)) AS Sum FROM tbl_SubCollection WHERE MahalluRegNo ='" + txtxmhregno.Text + "' AND Grade='"+txtGradeType.Text +"'";
db.cmd = new SqlCommand(str,db.Connect());
db.adapter = new SqlDataAdapter(db.cmd );
DataTable dtTotal = new DataTable();
db.adapter.Fill(dtTotal);
lblBalanceUptodate.Text = dtTotal.Rows[0][0].ToString();
lblTotAmntpaid.Visible = true;
lblBalanceUptodate.Visible = true;
DataGridViewRow gvr = this.dgvSuCollen.Rows[e.RowIndex];
if (gvr.Cells[11].Value.ToString() == "True")
{
gvr.DefaultCellStyle.BackColor = Color.LightGreen ;
}
PrevFinAmntCalculn();
}
private void PrevFinAmntCalculn()
{
string str = "SELECT Balance FROM tbl_SubCollection WHERE (Grade = '" + txtGradeType.Text + "') AND (Balance = '0') AND (Status = 'True') AND (FinYearID <> '" + lblFinID.Text + "') AND FamilyId='" + FmId + "'";
db.cmd = new SqlCommand(str, db.Connect());
db.adapter = new SqlDataAdapter(db.cmd);
DataTable dtfn = new DataTable();
db.adapter.Fill(dtfn);
if (dtfn.Rows.Count > 0)
{ txtPrevBalnce.Text = "0.0"; }
else
{
string str1 = "SELECT Balance FROM tbl_SubCollection WHERE (Grade = '" + txtGradeType.Text + "') AND (Status = 'False') AND (FinYearID <> '" + lblFinID.Text + "') AND FamilyId='" + FmId + "'";
db.cmd = new SqlCommand(str1, db.Connect());
db.adapter = new SqlDataAdapter(db.cmd);
DataTable dtblnc = new DataTable();
db.adapter.Fill(dtblnc);
//float sum = 0;
for (int i = 0; i < dtblnc.Rows.Count; i++)
{
//sum = sum + float.Parse(dtblnc.Rows[i]["Balance"].ToString());
txtPrevBalnce.Text = dtblnc.Rows[i]["Balance"].ToString();
txtCFYgrp.Text = dtblnc.Rows[i]["Balance"].ToString();
}
}
}
private void btnsubcolClear_Click(object sender, EventArgs e)
{
Oldbalance = "";
PrevAmount = "";
subcollId = "";
btnDeleteSub.Enabled = false;
txtHouseName.Text = "";
txtxmhregno.Text = "";
dtpDate.Text = "";
txtGradeType.Text = "";
txtAmountPaid.Text = "";
txtBalance.Text = "";
txtxmhregno.Focus();
}
private void btnSubCollUpdate_Click(object sender, EventArgs e)
{
subcollBal.SubCollID = subcollId;
subcollBal.FinYearID = lblFinID.Text;
subcollBal.HouseName = txtHouseName.Text;
subcollBal.MahalluRegNo = txtxmhregno.Text;
subcollBal.Date = dtpDate.Text;
subcollBal.Grade = txtGradeType.Text;
subcollBal.SubAmount = grdAmnt;
subcollBal.ReceiptNo = txtReceiptNo.Text;
subcollBal.OldBalance = txtOldBalance.Text;
float a, b;
a = float.Parse(PrevAmount);
b = float.Parse(txtAmountPaid.Text);
subcollBal.AmountPaid = (a + b).ToString();
subcollBal.AmountPaid = txtAmountPaid.Text;
subcollBal.Balance = txtBalance.Text;
if (chbStatus.Checked == true)
{ subcollBal.Status = "1"; }
else
{ subcollBal.Status = "0"; }
subcollBal.Mode = "Update";
subcollDal.UpdateSubsCollection(subcollBal);
balncBal.LedgerName = "Subscription";
balncBal.LedgerType = "Income";
balncBal.BillDate = System.DateTime.Now.ToShortDateString();
balncBal.Amount = txtAmountPaid.Text;
balncBal.StatusInCr = txtAmountPaid.Text;
balncBal.FK = txtReceiptNo.Text;
balncBal.StatusOutDr = "0";
balanceDal.InsertBalanceSheet(balncBal);
//string str = "update tbl_BalanceSheet set LedgerName ='Subscription',LedgerType = 'Income',BillDate = '"+System.DateTime.Now.ToShortDateString()+"',Amount = '"+txtAmountPaid.Text+"',StatusInCr = '"+txtAmountPaid.Text+"',StatusOutDr = '0' where FK='"+txtReceiptNo.Text +"'";
//db.cmd = new SqlCommand(str, db.Connect());
//db.cmd.ExecuteNonQuery();
MessageBox.Show("Updated Successfully");
gridSubColln();
gridSearchSubColln();
}
private void txtSearch_TextChanged(object sender, EventArgs e)
{
try
{
if (txtSearch.Text != "")
{
source.Filter = cmbSearchBy.Text + " LIKE '" + txtSearch.Text + "%' ";
TotalAmounts();
}
else
{
source.Filter = "";
TotalAmounts();
}
}
catch
{
txtSearch.Focus();
}
}
private void btnDateSearch_Click(object sender, EventArgs e)
{
source.Filter = "Date >='" + dtpStDate .Text + "' and Date <='" + dtpEndDate.Text + "' ";
TotalAmounts();
}
private void chbAdvncd_CheckedChanged(object sender, EventArgs e)
{
if (chbAdvncd.Checked == true)
{
panelAdvncd.Enabled = true;
}
else
{
panelAdvncd.Enabled = false;
}
}
private void txtxmhregno_Leave(object sender, EventArgs e)
{
DataSet ds = new DataSet();
hombal.MahalluRegNo = txtxmhregno.Text;
ds = homeDal.ViewHome4Subs(hombal);
if (ds.Tables[1].Rows.Count > 0)
{
txtHouseName.Text = ds.Tables[1].Rows[0]["HouseName"].ToString();
txtGradeType.Text = ds.Tables[1].Rows[0]["SubsGrade"].ToString();
FmId = ds.Tables[1].Rows[0]["FamilyId"].ToString();
}
else
{
MessageBox.Show("Incorrect Mahallu Reg No:");
}
}
private void btnPrint_Click(object sender, EventArgs e)
{
ReceiptNo = txtReceiptNo.Text;
maharegno = txtxmhregno.Text;
housname = txtHouseName.Text;
grdtype = txtGradeType.Text;
amntpaid = txtAmountPaid.Text;
balnce = txtBalance.Text;
date = dtpDate.Text;
frmSubscripPrint a = new frmSubscripPrint();
a.Show();
}
private void button1_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
app.Visible = true;
worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;
worksheet.Name = "Subscription Collection";
for (int i = 1; i < dgvSearchSubs.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = dgvSearchSubs.Columns[i - 1].HeaderText;
}
for (int i = 0; i < dgvSearchSubs.Rows.Count; i++)
{
for (int j = 0; j < dgvSearchSubs.Columns.Count; j++)
{
worksheet.Cells[i + 2, j + 1] = dgvSearchSubs.Rows[i].Cells[j].Value.ToString();
}
}
}
private void tabPage2_Click(object sender, EventArgs e)
{
}
private void tabPage5_Click(object sender, EventArgs e)
{
}
private void label1_Click(object sender, EventArgs e)
{
}
private void dgvMembView_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
MemberId = dgvMembView.CurrentRow.Cells[0].Value.ToString();
txtmembId .Text = dgvMembView.CurrentRow.Cells[2].Value.ToString();
txtMemName .Text = dgvMembView.CurrentRow.Cells[1].Value.ToString();
txtMGType.Text = dgvMembView.CurrentRow.Cells[3].Value.ToString();
grdAmnt = "";
Oldbalance = "";
}
private void txtMAmntpaid_TextChanged(object sender, EventArgs e)
{
try
{
grdAmnt = "";
subBal.Grade = txtMGType.Text;
subBal.Mode = "GrdAmnt";
db.dt = subDal.SelectGradeAmnt(subBal);
if (db.dt.Rows.Count > 0)
{
grdAmnt = db.dt.Rows[0]["Amount"].ToString();
}
float a = float.Parse(grdAmnt);
float b = float.Parse(txtMAmntpaid.Text);
txtMBalance.Text = (a - b).ToString();
if (Oldbalance != "")
{
if (b > float.Parse(Oldbalance))
{
MessageBox.Show("Amount Paid Not Greater Than Fixed Subscription Amount");
txtMBalance.Text = "";
txtMBalance.Focus();
}
if (b <= float.Parse(Oldbalance))
{
float c = float.Parse(Oldbalance);
float d = float.Parse(txtMAmntpaid.Text);
txtMBalance.Text = (c - d).ToString();
}
}
}
catch (Exception ex)
{
ex.Message.StartsWith("Input string was not in a correct format");
txtMAmntpaid.Focus();
}
}
txtMemName .Text = "";
private void btnMSave_Click(object sender, EventArgs e)
{
Boolean flag = true;
if (txtMAmntpaid .Text == "")
{
flag = false ;
errorProvider1.SetError(txtMAmntpaid, "Reqiured");
}
if (txtMReceiptNo.Text == "")
{
flag = false;
errorProvider1.SetError(txtMReceiptNo, "Reqiured");
}
if (flag == true)
{
errorProvider1.Clear();
subcollBal.MemberId = MemberId;
subcollBal.Memb_ID = txtmembId.Text;
subcollBal.MemName = txtMemName.Text;
subcollBal.Date = dtpMpaiddate.Text;
subcollBal.Grade = txtMGType.Text;
subcollBal.SubAmount = grdAmnt;
subcollBal.AmountPaid = txtMAmntpaid.Text;
subcollBal.Balance = txtMBalance.Text;
subcollBal.ReceiptNo = txtMReceiptNo.Text;
subcollBal.FinYearID = lblFinID.Text ;
subcollBal.Mode = "INSERT_M_Colln";
subcollDal.insertMembSubsCollection(subcollBal);
balncBal.LedgerName = "Subscription";
balncBal.LedgerType = "Income";
balncBal.BillDate = System.DateTime.Now.ToShortDateString();
balncBal.Amount = txtMAmntpaid.Text;
balncBal.StatusInCr = txtMAmntpaid.Text;
balncBal.StatusOutDr = "0";
balanceDal.InsertBalanceSheet(balncBal);
MessageBox.Show("Saved Successfully");
gridMembSubColln();
}
}
private void btnMClear_Click(object sender, EventArgs e)
{
PrevAmount = "";
txtmembId .Text = ""; dtpDate.Text = "";
txtMGType.Text = "";
txtMAmntpaid .Text = "";
txtMBalance.Text = "";
txtmembId.Focus();
Oldbalance = "";
btnMSave.Visible = true;
btnMUpdate.Visible = false;
}
private void btnMUpdate_Click(object sender, EventArgs e)
{
subcollBal.SubMCollID = subMcolId;
subcollBal.MemberId = MemberId;
subcollBal.Memb_ID = txtmembId.Text;
subcollBal.MemName = txtMemName.Text;
subcollBal.Date = dtpMpaiddate.Text;
subcollBal.Grade = txtMGType.Text;
subcollBal.ReceiptNo = txtMReceiptNo.Text;
subcollBal.SubAmount = grdAmnt;
subcollBal.FinYearID = lblFinID.Text;
float a, b;
a = float.Parse(PrevAmount);
b = float.Parse(txtMAmntpaid.Text);
subcollBal.AmountPaid = (a + b).ToString();
subcollBal.Balance = txtMBalance.Text;
subcollBal.Mode = "Update_MemColn";
subcollDal.UpdateMembSubsCollection(subcollBal);
balncBal.LedgerName = "Subscription";
balncBal.LedgerType = "Income";
balncBal.BillDate = System.DateTime.Now.ToShortDateString();
balncBal.Amount = txtMAmntpaid.Text;
balncBal.StatusInCr = txtMAmntpaid.Text;
balncBal.StatusOutDr = "0";
balanceDal.InsertBalanceSheet(balncBal);
MessageBox.Show("Saved Successfully");
gridMembSubColln();
}
private void dgvMembColn_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
Oldbalance = "";
Oldbalance = dgvMembColn.CurrentRow.Cells[8].Value.ToString();
subMcolId = dgvMembColn.CurrentRow.Cells[0].Value.ToString();
MemberId = dgvMembColn.CurrentRow.Cells[1].Value.ToString();
txtmembId.Text = dgvMembColn.CurrentRow.Cells[2].Value.ToString();
txtMemName .Text = dgvMembColn.CurrentRow.Cells[3].Value.ToString();
dtpMpaiddate .Text = dgvMembColn.CurrentRow.Cells[4].Value.ToString();
txtMGType.Text = dgvMembColn.CurrentRow.Cells[5].Value.ToString();
txtMAmntpaid .Text =PrevAmount = dgvMembColn.CurrentRow.Cells[7].Value.ToString();
Oldbalance = dgvMembColn.CurrentRow.Cells[8].Value.ToString();
txtMBalance.Text = dgvMembColn.CurrentRow.Cells[8].Value.ToString();
txtMReceiptNo.Text = dgvMembColn.CurrentRow.Cells[9].Value.ToString();
btnMSave.Visible = false;
btnMUpdate.Visible = true;
}
private void gbSubSearch_Enter(object sender, EventArgs e)
{
}
private void rbFamily_CheckedChanged(object sender, EventArgs e)
{
gridSearchSubColln();
cmbSearchBy.Items.Remove("Memb_ID");
cmbSearchBy.Items.Remove("MemName");
}
private void rbMember_CheckedChanged(object sender, EventArgs e)
{
gridMemberSearchSubColln();
cmbSearchBy.Items.Insert(0, "Memb_ID");
cmbSearchBy.Items.Insert(1, "MemName");
}
private void txtGradeType_TextChanged(object sender, EventArgs e)
{
for (int i = 0; i < dtGrade.Rows.Count; i++)
{
if (dtGrade.Rows[i]["Grade"].ToString() == txtGradeType.Text)
{
txtSubsAmnt.Text = dtGrade.Rows[i]["Amount"].ToString();
}
}
}
private void btnPayBalance_Click(object sender, EventArgs e)
{
Boolean flag = true;
if (txtAmountPaid .Text == "")
{
flag = false ;
errorProvider1.SetError(txtAmountPaid, "Reqiured");
}
if (txtReceiptNo.Text == "")
{
flag = false;
errorProvider1.SetError(txtReceiptNo, "Reqiured");
}
string str = "select ReceiptNo from tbl_SubCollection where ReceiptNo='" + txtReceiptNo.Text + "'";
db.cmd = new SqlCommand(str, db.Connect());
db.adapter = new SqlDataAdapter(db.cmd);
DataTable dtreciept = new DataTable();
db.adapter.Fill(dtreciept);
if (dtreciept.Rows.Count > 0)
{
flag = false;
MessageBox.Show("Receipt Number Already Exist","Pay Balance");
}
if (flag == true)
{
errorProvider1.Clear();
subcollBal.FamilyId = FmId;
subcollBal.FinYearID = lblFinID.Text;
subcollBal.HouseName = txtHouseName.Text;
subcollBal.MahalluRegNo = txtxmhregno.Text;
subcollBal.Date = dtpDate.Text;
subcollBal.Grade = txtGradeType.Text;
subcollBal.SubAmount = grdAmnt;
subcollBal.AmountPaid = txtAmountPaid.Text;
subcollBal.Balance = txtBalance.Text;
subcollBal.ReceiptNo = txtReceiptNo.Text;
subcollBal.OldBalance = txtOldBalance.Text;
if (chbStatus.Checked == true)
{ subcollBal.Status = "1"; }
else
{ subcollBal.Status = "0"; }
subcollBal.Mode = "INSERT";
subcollDal.insertSubsCollection(subcollBal);
balncBal.LedgerName = "Subscription";
balncBal.LedgerType = "Income";
balncBal.BillDate = System.DateTime.Now.ToShortDateString();
balncBal.Amount = txtAmountPaid.Text;
balncBal.StatusInCr = txtAmountPaid.Text;
balncBal.StatusOutDr = "0";
balncBal.FK = txtReceiptNo.Text;
balanceDal.InsertBalanceSheet(balncBal);
MessageBox.Show("Balance Paid Successfully");
gridSubColln();
gridSearchSubColln();
}
}
private void txtBalance_TextChanged(object sender, EventArgs e)
{
if (txtBalance.Text == "0")
{
chbStatus.Visible = true;
}
}
private void btnSearch_Click(object sender, EventArgs e)
{
string str = "select * from tbl_SubCollection where MahalluRegNo='"+cmbMHRegNo.Text +"' ";
db.cmd = new SqlCommand(str, db.Connect());
db.adapter = new SqlDataAdapter(db.cmd);
DataTable dt2 = new DataTable();
db.adapter.Fill(dt2);
if (dt2.Rows.Count > 0)
{
dgvFamilySubs.DataSource = dt2;
dgvFamilySubs.Columns[0].Visible = false;
dgvFamilySubs.Columns[1].Visible = false;
dgvFamilySubs.Columns[10].Visible = false;
dgvFamilySubs.Columns[6].Visible = false;
dgvFamilySubs.Columns[12].Visible = false;
dgvFamilySubs.Columns[2].HeaderText = "House Name";
dgvFamilySubs.Columns[3].HeaderText = "Mahal Reg No:";
dgvFamilySubs.Columns[4].HeaderText = "Date";
dgvFamilySubs.Columns[5].HeaderText = "Subs:Type";
//dgvFamilySubs.Columns[6].HeaderText = "Amount";
dgvFamilySubs.Columns[7].HeaderText = "Amount Paid";
dgvFamilySubs.Columns[8].HeaderText = "Balance";
dgvFamilySubs.Columns[9].HeaderText = "Receipt No:";
dgvFamilySubs.Columns[11].HeaderText = "Completed";
TotalAmountsFamily();
}
}
private void TotalAmountsFamily()
{
int /*sum1 = 0,*/ sum2 = 0 /*sum3 = 0*/;
for (int i = 0; i < this.dgvFamilySubs.Rows.Count; i++)
{
//if (dgvFamilySubs.Rows[i].Cells[6].Value != null)
//{
// sum1 += Convert.ToInt32(this.dgvFamilySubs[6, i].Value);
//}
if (dgvFamilySubs.Rows[i].Cells[7].Value != null)
{
sum2 += Convert.ToInt32(this.dgvFamilySubs[7, i].Value);
}
//if (dgvFamilySubs.Rows[i].Cells[8].Value != null)
//{
// sum3 += Convert.ToInt32(this.dgvFamilySubs[8, i].Value);
//}
}
txtSubsAmntDisp.Text = dgvFamilySubs.Rows[0].Cells[6].Value .ToString();
txtTotamntCollnDisp.Text = sum2.ToString();
txtBalnceDisp.Text = (float.Parse(txtSubsAmntDisp.Text) - float.Parse(txtTotamntCollnDisp.Text)).ToString();
txtOldbalncedisp.Text = dgvFamilySubs.Rows[0].Cells[10].Value .ToString();
}
private void btnDateSerch_Click(object sender, EventArgs e)
{
string str = "select * from tbl_SubCollection where (Date BETWEEN CONVERT(datetime,'"+dtp1.Text +"') AND CONVERT(datetime,'"+dtp2.Text +"')) AND MahalluRegNo='" + cmbMHRegNo.Text + "'";
db.cmd = new SqlCommand(str, db.Connect());
db.adapter = new SqlDataAdapter(db.cmd);
DataTable dt2 = new DataTable();
db.adapter.Fill(dt2);
if (dt2.Rows.Count > 0)
{
dgvFamilySubs.DataSource = dt2;
dgvFamilySubs.Columns[0].Visible = false;
dgvFamilySubs.Columns[1].Visible = false;
dgvFamilySubs.Columns[10].Visible = false;
dgvFamilySubs.Columns[6].Visible = false;
dgvFamilySubs.Columns[2].HeaderText = "House Name";
dgvFamilySubs.Columns[3].HeaderText = "Mahal Reg No:";
dgvFamilySubs.Columns[4].HeaderText = "Date";
dgvFamilySubs.Columns[5].HeaderText = "Subs:Type";
//dgvFamilySubs.Columns[6].HeaderText = "Amount";
dgvFamilySubs.Columns[7].HeaderText = "Amount Paid";
dgvFamilySubs.Columns[8].HeaderText = "Balance";
dgvFamilySubs.Columns[9].HeaderText = "Receipt No:";
dgvFamilySubs.Columns[11].HeaderText = "Completed";
TotalAmountsFamily();
}
}
private void checkBox1_CheckedChanged(object sender, EventArgs e)
{
if (checkBox1.Checked == true)
{
panel1.Enabled = true;
}
if (checkBox1.Checked == false )
{
panel1.Enabled = false ;
}
}
private void btnDeleteSub_Click(object sender, EventArgs e)
{
//subcollId
if (MessageBox.Show("Are You Sure Want To Delete ?", "Delete", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
string str = "delete from tbl_SubCollection where SubCollID='" + subcollId + "'";
db.cmd = new SqlCommand(str, db.Connect());
db.cmd.ExecuteNonQuery();
string str2 = "delete from tbl_BalanceSheet where FK='" + txtReceiptNo.Text + "'";
db.cmd = new SqlCommand(str2, db.Connect());
db.cmd.ExecuteNonQuery();
MessageBox.Show("Deleted","Delete");
gridSubColln();
gridSearchSubColln();
}
}
private void label6_Click(object sender, EventArgs e)
{
}
private void txtSearchGrid_TextChanged(object sender, EventArgs e)
{
if (txtSearchGrid.Text != "")
{
source1.Filter = cmbSearch.Text + " LIKE '" + txtSearchGrid.Text + "%' ";
}
else
{
source1.Filter = "";
}
}
private void dgvFamilyGrpColn_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
}
private void FamilyGropBinding()
{
string str = "SELECT tbl_Family.FamilyId,tbl_Family.MahalluRegNo, tbl_Family.HouseName, tbl_Family.HouseOwner, tbl_Subs.Grade, tbl_Subs.Amount FROM tbl_Family INNER JOIN tbl_Subs ON tbl_Family.SubsGrade = tbl_Subs.Grade";
db.cmd = new SqlCommand(str, db.Connect());
db.adapter = new SqlDataAdapter(db.cmd);
DataTable dt3 = new DataTable();
db.adapter.Fill(dt3);
if (dt3.Rows.Count > 0)
{
dgvFamilyGrpColn.DataSource = dt3;
dgvFamilyGrpColn.Columns[1].Visible = false;
dgvFamilyGrpColn.Columns["Month"].DisplayIndex = 6;
}
else
{
}
}
private void dgvFamilyGrpColn_CellEnter(object sender, DataGridViewCellEventArgs e)
{
TotalamntGrp = 0;
if (e.ColumnIndex == Month.Index && e.RowIndex >= 0)
{
panelMonth.Visible = true;
}
else
{
panelMonth.Visible = false;
}
lblFamilID.Text = dgvFamilyGrpColn.Rows[e.RowIndex].Cells[1].Value.ToString();
lblAmountDisp.Text = dgvFamilyGrpColn.Rows[e.RowIndex].Cells[6].Value.ToString();
lblHouseNam.Text = dgvFamilyGrpColn.Rows[e.RowIndex].Cells[3].Value.ToString();
lblMahalluRegNo.Text = dgvFamilyGrpColn.Rows[e.RowIndex].Cells[2].Value.ToString();
txtSubsType.Text = dgvFamilyGrpColn.Rows[e.RowIndex].Cells[5].Value.ToString();
txtSubsAmntGroup.Text = dgvFamilyGrpColn.Rows[e.RowIndex].Cells[6].Value.ToString();
viewPaidMonths();
PrevFinAmntGroupCalculn();
}
private void viewPaidMonths()
{
chbJan.Checked = false; chbFeb.Checked = false; chbMarch.Checked = false;
chbApril.Checked = false; chbMay.Checked = false; chbJune.Checked = false;
chbJuly.Checked = false; chbAug.Checked = false; chbSept.Checked = false;
chbOct.Checked = false; chbNov.Checked = false; chbDec.Checked = false;
string str = "select Date from tbl_SubCollection where FamilyId='" + lblFamilID.Text + "' and Grade='" + txtSubsType.Text + "'";
db.cmd = new SqlCommand(str,db.Connect());
db.adapter = new SqlDataAdapter(db.cmd );
DataTable dt = new DataTable();
dt.Clear();
db.adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
string Paiddate = dt.Rows[i][0].ToString();
string paidMonth = Paiddate.Substring(0, 2);
if (paidMonth == "01") { chbJan.Checked = true; }
if (paidMonth == "02") { chbFeb.Checked = true; }
if (paidMonth == "03") { chbMarch.Checked = true; }
if (paidMonth == "04") { chbApril.Checked = true; }
if (paidMonth == "05") { chbMay.Checked = true; }
if (paidMonth == "06") { chbJune.Checked = true; }
if (paidMonth == "07") { chbJuly.Checked = true; }
if (paidMonth == "08") { chbAug.Checked = true; }
if (paidMonth == "09") { chbSept.Checked = true; }
if (paidMonth == "10") { chbOct.Checked = true; }
if (paidMonth == "11") { chbNov.Checked = true; }
if (paidMonth == "12") { chbDec.Checked = true; }
//btnSaveGrp.Enabled = false;
//btnUpdateGrp.Enabled = true;
}
txtTotAmntCollctd.Text = (double.Parse(lblAmountDisp.Text) * dt.Rows.Count).ToString();
TotalamntGrp = double.Parse(lblAmountDisp.Text) * dt.Rows.Count;
}
else
{
chbJan.Checked = false ; chbFeb.Checked = false; chbMarch.Checked = false;
chbApril.Checked = false; chbMay.Checked = false;chbJune.Checked = false;
chbJuly.Checked = false; chbAug.Checked = false;chbSept.Checked = false;
chbOct.Checked = false;chbNov.Checked = false; chbDec.Checked = false;
TotalamntGrp = 0;
//btnSaveGrp.Enabled = true;
//btnUpdateGrp.Enabled = false;
}
}
private void PrevFinAmntGroupCalculn()
{
string str = "SELECT Balance FROM tbl_SubCollection WHERE (Grade = '" + txtSubsType.Text + "') AND (Balance = '0') AND (Status = 'True') AND (FinYearID <> '" + lblFinID.Text + "') AND FamilyId='" + lblFamilID.Text + "'";
db.cmd = new SqlCommand(str, db.Connect());
db.adapter = new SqlDataAdapter(db.cmd);
DataTable dtfn = new DataTable();
db.adapter.Fill(dtfn);
if (dtfn.Rows.Count > 0)
{ txtPrevBalnce.Text = "0.0"; txtCFYgrp.Text = "0.0"; }
else
{
string str1 = "SELECT Balance FROM tbl_SubCollection WHERE (Grade = '" + txtSubsType.Text + "') AND (Status = 'False') AND (FinYearID <> '" + lblFinID.Text + "') AND FamilyId='" + lblFamilID.Text + "'";
db.cmd = new SqlCommand(str1, db.Connect());
db.adapter = new SqlDataAdapter(db.cmd);
DataTable dtblnc = new DataTable();
db.adapter.Fill(dtblnc);
for (int i = 0; i < dtblnc.Rows.Count; i++)
{
txtPrevBalnce.Text = dtblnc.Rows[i]["Balance"].ToString();
txtCFYgrp.Text = dtblnc.Rows[i]["Balance"].ToString();
}
}
}
private void PrevFinAmntMembCalculn()
{
string str = "SELECT Balance FROM tbl_Sub_MemCollection WHERE (Grade = '" + txtSubsTyp_GM.Text + "') AND (Balance = '0') AND (Status = 'True') AND (FinYearID <> '" + lblFinID.Text + "') AND MemberId='" + lblMmbrID_GM.Text + "'";
db.cmd = new SqlCommand(str, db.Connect());
db.adapter = new SqlDataAdapter(db.cmd);
DataTable dtfn = new DataTable();
db.adapter.Fill(dtfn);
if (dtfn.Rows.Count > 0)
{ txtCFY_GM.Text = "0.0"; }
else
{
string str1 = "SELECT Balance FROM tbl_Sub_MemCollection WHERE (Grade = '" + txtSubsTyp_GM.Text + "') AND (Status = 'False') AND (FinYearID <> '" + lblFinID.Text + "') AND MemberId='" + lblMmbrID_GM.Text + "'";
db.cmd = new SqlCommand(str1, db.Connect());
db.adapter = new SqlDataAdapter(db.cmd);
DataTable dtblnc = new DataTable();
db.adapter.Fill(dtblnc);
for (int i = 0; i < dtblnc.Rows.Count; i++)
{
txtCFY_GM.Text = dtblnc.Rows[i]["Balance"].ToString();
txtCFY_GM.Text = dtblnc.Rows[i]["Balance"].ToString();
}
}
}
double TotalamntGrp = 0;
private void chbAll_CheckedChanged(object sender, EventArgs e)
{
if (chbAll.Checked == true)
{
chbJan.Checked = true; chbFeb.Checked = true; chbMarch .Checked = true;
chbApril.Checked = true;chbMay.Checked = true; chbJune .Checked = true;
chbJuly .Checked = true;chbAug.Checked = true; chbSept.Checked = true;
chbOct.Checked = true; chbNov.Checked = true; chbDec.Checked = true;
}
if (chbAll.Checked == false )
{
chbJan.Checked = false;chbFeb.Checked = false; chbMarch.Checked = false;
chbApril.Checked = false; chbMay.Checked = false;chbJune.Checked = false;
chbJuly.Checked = false; chbAug.Checked = false; chbSept.Checked = false;
chbOct.Checked = false; chbNov.Checked = false; chbDec.Checked = false;
}
}
private void chbJan_CheckedChanged(object sender, EventArgs e)
{
if (chbJan.Checked == true)
{
TotalamntGrp = TotalamntGrp + double.Parse(lblAmountDisp.Text);
txtTotAmntCollctd.Text = TotalamntGrp.ToString();
}
if (chbJan .Checked == false)
{
TotalamntGrp = TotalamntGrp - double.Parse(lblAmountDisp.Text);
txtTotAmntCollctd.Text = TotalamntGrp.ToString();
}
}
private void chbFeb_CheckedChanged(object sender, EventArgs e)
{
if (chbFeb.Checked == true)
{
TotalamntGrp = TotalamntGrp + double.Parse(lblAmountDisp.Text);
txtTotAmntCollctd.Text = TotalamntGrp.ToString();
}
if (chbFeb.Checked == false)
{
TotalamntGrp = TotalamntGrp - double.Parse(lblAmountDisp.Text);
txtTotAmntCollctd.Text = TotalamntGrp.ToString();
}
}
private void chbMarch_CheckedChanged(object sender, EventArgs e)
{
if (chbMarch.Checked == true)
{
TotalamntGrp = TotalamntGrp + double.Parse(lblAmountDisp.Text);
txtTotAmntCollctd.Text = TotalamntGrp.ToString();
}
if (chbMarch.Checked == false)
{
TotalamntGrp = TotalamntGrp - double.Parse(lblAmountDisp.Text);
txtTotAmntCollctd.Text = TotalamntGrp.ToString();
}
}
private void chbApril_CheckedChanged(object sender, EventArgs e)
{
if (chbApril.Checked == true)
{
TotalamntGrp = TotalamntGrp + double.Parse(lblAmountDisp.Text);
txtTotAmntCollctd.Text = TotalamntGrp.ToString();
}
if (chbApril.Checked == false)
{
TotalamntGrp = TotalamntGrp - double.Parse(lblAmountDisp.Text);
txtTotAmntCollctd.Text = TotalamntGrp.ToString();
}
}
private void chbMay_CheckedChanged(object sender, EventArgs e)
{
if (chbMay.Checked == true)
{
TotalamntGrp = TotalamntGrp + double.Parse(lblAmountDisp.Text);
txtTotAmntCollctd.Text = TotalamntGrp.ToString();
}
if (chbMay.Checked == false)
{
TotalamntGrp = TotalamntGrp - double.Parse(lblAmountDisp.Text);
txtTotAmntCollctd.Text = TotalamntGrp.ToString();
}
}
private void chbJune_CheckedChanged(object sender, EventArgs e)
{
if (chbJune.Checked == true)
{
TotalamntGrp = TotalamntGrp + double.Parse(lblAmountDisp.Text);
txtTotAmntCollctd.Text = TotalamntGrp.ToString();
}
if (chbJune.Checked == false)
{
TotalamntGrp = TotalamntGrp - double.Parse(lblAmountDisp.Text);
txtTotAmntCollctd.Text = TotalamntGrp.ToString();
}
}
private void chbJuly_CheckedChanged(object sender, EventArgs e)
{
if (chbJuly.Checked == true)
{
TotalamntGrp = TotalamntGrp + double.Parse(lblAmountDisp.Text);
txtTotAmntCollctd.Text = TotalamntGrp.ToString();
}
if (chbJuly.Checked == false)
{
TotalamntGrp = TotalamntGrp - double.Parse(lblAmountDisp.Text);
txtTotAmntCollctd.Text = TotalamntGrp.ToString();
}
}
private void chbAug_CheckedChanged(object sender, EventArgs e)
{
if (chbAug.Checked == true)
{
TotalamntGrp = TotalamntGrp + double.Parse(lblAmountDisp.Text);
txtTotAmntCollctd.Text = TotalamntGrp.ToString();
}
if (chbAug.Checked == false)
{
TotalamntGrp = TotalamntGrp - double.Parse(lblAmountDisp.Text);
txtTotAmntCollctd.Text = TotalamntGrp.ToString();
}
}
private void chbSept_CheckedChanged(object sender, EventArgs e)
{
if (chbSept.Checked == true)
{
TotalamntGrp = TotalamntGrp + double.Parse(lblAmountDisp.Text);
txtTotAmntCollctd.Text = TotalamntGrp.ToString();
}
if (chbSept.Checked == false)
{
TotalamntGrp = TotalamntGrp - double.Parse(lblAmountDisp.Text);
txtTotAmntCollctd.Text = TotalamntGrp.ToString();
}
}
private void chbOct_CheckedChanged(object sender, EventArgs e)
{
if (chbOct.Checked == true)
{
TotalamntGrp = TotalamntGrp + double.Parse(lblAmountDisp.Text);
txtTotAmntCollctd.Text = TotalamntGrp.ToString();
}
if (chbOct.Checked == false)
{
TotalamntGrp = TotalamntGrp - double.Parse(lblAmountDisp.Text);
txtTotAmntCollctd.Text = TotalamntGrp.ToString();
}
}
private void chbNov_CheckedChanged(object sender, EventArgs e)
{
if (chbNov.Checked == true)
{
TotalamntGrp = TotalamntGrp + double.Parse(lblAmountDisp.Text);
txtTotAmntCollctd.Text = TotalamntGrp.ToString();
}
if (chbNov.Checked == false)
{
TotalamntGrp = TotalamntGrp - double.Parse(lblAmountDisp.Text);
txtTotAmntCollctd.Text = TotalamntGrp.ToString();
}
}
private void chbDec_CheckedChanged(object sender, EventArgs e)
{
if (chbDec.Checked == true)
{
TotalamntGrp = TotalamntGrp + double.Parse(lblAmountDisp.Text);
txtTotAmntCollctd.Text = TotalamntGrp.ToString();
}
if (chbDec.Checked == false)
{
TotalamntGrp = TotalamntGrp - double.Parse(lblAmountDisp.Text);
txtTotAmntCollctd.Text = TotalamntGrp.ToString();
}
}
private void btnSaveGrp_Click(object sender, EventArgs e)
{
Boolean flag = true;
if (txtRecieptNo.Text == "")
{
flag = false;
errorProvider1.SetError(txtRecieptNo, "Reqiured");
}
string str1 = "select ReceiptNo from tbl_SubCollection where ReceiptNo='" + txtRecieptNo.Text + "'";
db.cmd = new SqlCommand(str1, db.Connect());
db.adapter = new SqlDataAdapter(db.cmd);
DataTable dtreciept1 = new DataTable();
db.adapter.Fill(dtreciept1);
if (dtreciept1.Rows.Count > 0)
{
flag = false;
MessageBox.Show("Receipt Number Already Exist", "Pay Balance");
}
if (flag == true)
{
string date = "";
if (chbJan.Checked == true)
{
date = "01/01/" + System.DateTime.Now.ToString("yyyy");
string getid = "select SubCollID from tbl_SubCollection where Date='" + date + "' and FamilyId='" + lblFamilID.Text + "'";
db.cmd = new SqlCommand(getid, db.Connect());
DataTable dtid = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtid);
if (dtid.Rows.Count > 0)
{
string updt = "update tbl_SubCollection set FamilyId='" + lblFamilID.Text + "',HouseName='" + lblHouseNam.Text + "',MahalluRegNo='" + lblMahalluRegNo.Text + "',Date='" + date + "',Grade='" + txtSubsType.Text + "',SubAmount='" + txtSubsAmntGroup.Text + "',AmountPaid='" + lblAmountDisp.Text + "',Balance='" + txtBalnceGrp.Text + "',ReceiptNo='" + txtRecieptNo.Text + "',OldBalance='" + txtOldBalncGrp.Text + "',Status='True',FinYearID='" + lblFinID.Text + "' where SubCollID='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(updt, db.Connect());
db.cmd.ExecuteNonQuery();
string updtBS = "update tbl_BalanceSheet set BillDate='" + dtpColectdOn.Text + "' where FK='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(updtBS, db.Connect());
db.cmd.ExecuteNonQuery();
}
else
{
string str = "insert into tbl_SubCollection(FamilyId,HouseName,MahalluRegNo,Date,Grade,SubAmount,AmountPaid,Balance,ReceiptNo,OldBalance,Status,FinYearID)values('" + lblFamilID.Text + "','" + lblHouseNam.Text + "','" + lblMahalluRegNo.Text + "','" + date + "','" + txtSubsType.Text + "','" + txtSubsAmntGroup.Text + "','" + lblAmountDisp.Text + "','" + txtBalnceGrp.Text + "','" + txtRecieptNo.Text + "','" + txtOldBalncGrp.Text + "','True','" + lblFinID.Text + "')";
db.cmd = new SqlCommand(str, db.Connect());
db.cmd.ExecuteNonQuery();
string id = "select MAX(SubCollID) as ID from tbl_SubCollection";
db.cmd = new SqlCommand(id, db.Connect());
DataTable dtSubColID = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtSubColID);
string InsertBS = "insert into tbl_BalanceSheet(LedgerName,LedgerType,BillDate,Amount,StatusInCr,StatusOutDr,FK)values('Family-Subs-January','Income','" + dtpColectdOn.Text + "','" + lblAmountDisp.Text + "','" + lblAmountDisp.Text + "','0','" + dtSubColID.Rows[0][0].ToString() + "')";
db.cmd = new SqlCommand(InsertBS, db.Connect());
db.cmd.ExecuteNonQuery();
}
}
if (chbJan.Checked == false )
{
date = "01/01/" + System.DateTime.Now.ToString("yyyy");
string getid = "select SubCollID from tbl_SubCollection where Date='" + date + "' and FamilyId='" + lblFamilID.Text + "'";
db.cmd = new SqlCommand(getid, db.Connect());
DataTable dtid = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtid);
if (dtid.Rows.Count > 0)
{
string str2 = "delete from tbl_SubCollection where SubCollID='" + dtid.Rows[0][0].ToString() + "'and FamilyId='" + lblFamilID.Text + "' ";
db.cmd = new SqlCommand(str2, db.Connect());
db.cmd.ExecuteNonQuery();
string del = "delete from tbl_BalanceSheet where FK='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(del, db.Connect());
db.cmd.ExecuteNonQuery();
}
}
if (chbFeb.Checked == true)
{
date = "02/01/" + System.DateTime.Now.ToString("yyyy");
string getid = "select SubCollID from tbl_SubCollection where Date='" + date + "' and FamilyId='" + lblFamilID.Text + "'";
db.cmd = new SqlCommand(getid, db.Connect());
DataTable dtid = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtid);
if (dtid.Rows.Count > 0)
{
string updt = "update tbl_SubCollection set FamilyId='" + lblFamilID.Text + "',HouseName='" + lblHouseNam.Text + "',MahalluRegNo='" + lblMahalluRegNo.Text + "',Date='" + date + "',Grade='" + txtSubsType.Text + "',SubAmount='" + txtSubsAmntGroup.Text + "',AmountPaid='" + lblAmountDisp.Text + "',Balance='" + txtBalnceGrp.Text + "',ReceiptNo='" + txtRecieptNo.Text + "',OldBalance='" + txtOldBalncGrp.Text + "',Status='True',FinYearID='" + lblFinID.Text + "' where SubCollID='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(updt, db.Connect());
db.cmd.ExecuteNonQuery();
string updtBS = "update tbl_BalanceSheet set BillDate='" + dtpColectdOn.Text + "' where FK='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(updtBS, db.Connect());
db.cmd.ExecuteNonQuery();
}
else
{
string str = "insert into tbl_SubCollection(FamilyId,HouseName,MahalluRegNo,Date,Grade,SubAmount,AmountPaid,Balance,ReceiptNo,OldBalance,Status,FinYearID)values('" + lblFamilID.Text + "','" + lblHouseNam.Text + "','" + lblMahalluRegNo.Text + "','" + date + "','" + txtSubsType.Text + "','" + txtSubsAmntGroup.Text + "','" + lblAmountDisp.Text + "','" + txtBalnceGrp.Text + "','" + txtRecieptNo.Text + "','" + txtOldBalncGrp.Text + "','True','" + lblFinID.Text + "')";
db.cmd = new SqlCommand(str, db.Connect());
db.cmd.ExecuteNonQuery();
string id = "select MAX(SubCollID) as ID from tbl_SubCollection";
db.cmd = new SqlCommand(id, db.Connect());
DataTable dtSubColID = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtSubColID);
string InsertBS = "insert into tbl_BalanceSheet(LedgerName,LedgerType,BillDate,Amount,StatusInCr,StatusOutDr,FK)values('Family-Subs-February','Income','" + dtpColectdOn.Text + "','" + lblAmountDisp.Text + "','" + lblAmountDisp.Text + "','0','" + dtSubColID.Rows[0][0].ToString() + "')";
db.cmd = new SqlCommand(InsertBS, db.Connect());
db.cmd.ExecuteNonQuery();
}
}
if (chbFeb .Checked == false)
{
date = "02/01/" + System.DateTime.Now.ToString("yyyy");
string getid = "select SubCollID from tbl_SubCollection where Date='" + date + "' and FamilyId='" + lblFamilID.Text + "'";
db.cmd = new SqlCommand(getid, db.Connect());
DataTable dtid = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtid);
if (dtid.Rows.Count > 0)
{
string str2 = "delete from tbl_SubCollection where SubCollID='" + dtid.Rows[0][0].ToString() + "'and FamilyId='" + lblFamilID.Text + "' ";
db.cmd = new SqlCommand(str2, db.Connect());
db.cmd.ExecuteNonQuery();
string del = "delete from tbl_BalanceSheet where FK='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(del, db.Connect());
db.cmd.ExecuteNonQuery();
}
}
if (chbMarch.Checked == true)
{
date = "03/01/" + System.DateTime.Now.ToString("yyyy");
string getid = "select SubCollID from tbl_SubCollection where Date='" + date + "' and FamilyId='" + lblFamilID.Text + "'";
db.cmd = new SqlCommand(getid, db.Connect());
DataTable dtid = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtid);
if (dtid.Rows.Count > 0)
{
string updt = "update tbl_SubCollection set FamilyId='" + lblFamilID.Text + "',HouseName='" + lblHouseNam.Text + "',MahalluRegNo='" + lblMahalluRegNo.Text + "',Date='" + date + "',Grade='" + txtSubsType.Text + "',SubAmount='" + txtSubsAmntGroup.Text + "',AmountPaid='" + lblAmountDisp.Text + "',Balance='" + txtBalnceGrp.Text + "',ReceiptNo='" + txtRecieptNo.Text + "',OldBalance='" + txtOldBalncGrp.Text + "',Status='True',FinYearID='" + lblFinID.Text + "' where SubCollID='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(updt, db.Connect());
db.cmd.ExecuteNonQuery();
string updtBS = "update tbl_BalanceSheet set BillDate='" + dtpColectdOn.Text + "' where FK='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(updtBS, db.Connect());
db.cmd.ExecuteNonQuery();
}
else
{
string str = "insert into tbl_SubCollection(FamilyId,HouseName,MahalluRegNo,Date,Grade,SubAmount,AmountPaid,Balance,ReceiptNo,OldBalance,Status,FinYearID)values('" + lblFamilID.Text + "','" + lblHouseNam.Text + "','" + lblMahalluRegNo.Text + "','" + date + "','" + txtSubsType.Text + "','" + txtSubsAmntGroup.Text + "','" + lblAmountDisp.Text + "','" + txtBalnceGrp.Text + "','" + txtRecieptNo.Text + "','" + txtOldBalncGrp.Text + "','True','" + lblFinID.Text + "')";
db.cmd = new SqlCommand(str, db.Connect());
db.cmd.ExecuteNonQuery();
string id = "select MAX(SubCollID) as ID from tbl_SubCollection";
db.cmd = new SqlCommand(id, db.Connect());
DataTable dtSubColID = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtSubColID);
string InsertBS = "insert into tbl_BalanceSheet(LedgerName,LedgerType,BillDate,Amount,StatusInCr,StatusOutDr,FK)values('Family-Subs-March','Income','" + dtpColectdOn.Text + "','" + lblAmountDisp.Text + "','" + lblAmountDisp.Text + "','0','" + dtSubColID.Rows[0][0].ToString() + "')";
db.cmd = new SqlCommand(InsertBS, db.Connect());
db.cmd.ExecuteNonQuery();
}
}
if (chbMarch .Checked == false)
{
date = "03/01/" + System.DateTime.Now.ToString("yyyy");
string getid = "select SubCollID from tbl_SubCollection where Date='" + date + "' and FamilyId='" + lblFamilID.Text + "'";
db.cmd = new SqlCommand(getid, db.Connect());
DataTable dtid = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtid);
if (dtid.Rows.Count > 0)
{
string str2 = "delete from tbl_SubCollection where SubCollID='" + dtid.Rows[0][0].ToString() + "'and FamilyId='" + lblFamilID.Text + "' ";
db.cmd = new SqlCommand(str2, db.Connect());
db.cmd.ExecuteNonQuery();
string del = "delete from tbl_BalanceSheet where FK='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(del, db.Connect());
db.cmd.ExecuteNonQuery();
}
}
if (chbApril.Checked == true)
{
date = "04/01/" + System.DateTime.Now.ToString("yyyy");
string getid = "select SubCollID from tbl_SubCollection where Date='" + date + "' and FamilyId='" + lblFamilID.Text + "'";
db.cmd = new SqlCommand(getid, db.Connect());
DataTable dtid = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtid);
if (dtid.Rows.Count > 0)
{
string updt = "update tbl_SubCollection set FamilyId='" + lblFamilID.Text + "',HouseName='" + lblHouseNam.Text + "',MahalluRegNo='" + lblMahalluRegNo.Text + "',Date='" + date + "',Grade='" + txtSubsType.Text + "',SubAmount='" + txtSubsAmntGroup.Text + "',AmountPaid='" + lblAmountDisp.Text + "',Balance='" + txtBalnceGrp.Text + "',ReceiptNo='" + txtRecieptNo.Text + "',OldBalance='" + txtOldBalncGrp.Text + "',Status='True',FinYearID='" + lblFinID.Text + "' where SubCollID='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(updt, db.Connect());
db.cmd.ExecuteNonQuery();
string updtBS = "update tbl_BalanceSheet set BillDate='" + dtpColectdOn.Text + "' where FK='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(updtBS, db.Connect());
db.cmd.ExecuteNonQuery();
}
else
{
string str = "insert into tbl_SubCollection(FamilyId,HouseName,MahalluRegNo,Date,Grade,SubAmount,AmountPaid,Balance,ReceiptNo,OldBalance,Status,FinYearID)values('" + lblFamilID.Text + "','" + lblHouseNam.Text + "','" + lblMahalluRegNo.Text + "','" + date + "','" + txtSubsType.Text + "','" + txtSubsAmntGroup.Text + "','" + lblAmountDisp.Text + "','" + txtBalnceGrp.Text + "','" + txtRecieptNo.Text + "','" + txtOldBalncGrp.Text + "','True','" + lblFinID.Text + "')";
db.cmd = new SqlCommand(str, db.Connect());
db.cmd.ExecuteNonQuery();
string id = "select MAX(SubCollID) as ID from tbl_SubCollection";
db.cmd = new SqlCommand(id, db.Connect());
DataTable dtSubColID = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtSubColID);
string InsertBS = "insert into tbl_BalanceSheet(LedgerName,LedgerType,BillDate,Amount,StatusInCr,StatusOutDr,FK)values('Family-Subs-April','Income','" + dtpColectdOn.Text + "','" + lblAmountDisp.Text + "','" + lblAmountDisp.Text + "','0','" + dtSubColID.Rows[0][0].ToString() + "')";
db.cmd = new SqlCommand(InsertBS, db.Connect());
db.cmd.ExecuteNonQuery();
}
}
if (chbApril .Checked == false)
{
date = "04/01/" + System.DateTime.Now.ToString("yyyy");
string getid = "select SubCollID from tbl_SubCollection where Date='" + date + "' and FamilyId='" + lblFamilID.Text + "'";
db.cmd = new SqlCommand(getid, db.Connect());
DataTable dtid = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtid);
if (dtid.Rows.Count > 0)
{
string str2 = "delete from tbl_SubCollection where SubCollID='" + dtid.Rows[0][0].ToString() + "'and FamilyId='" + lblFamilID.Text + "' ";
db.cmd = new SqlCommand(str2, db.Connect());
db.cmd.ExecuteNonQuery();
string del = "delete from tbl_BalanceSheet where FK='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(del, db.Connect());
db.cmd.ExecuteNonQuery();
}
}
if (chbMay.Checked == true)
{
date = "05/01/" + System.DateTime.Now.ToString("yyyy");
string getid = "select SubCollID from tbl_SubCollection where Date='" + date + "' and FamilyId='" + lblFamilID.Text + "'";
db.cmd = new SqlCommand(getid, db.Connect());
DataTable dtid = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtid);
if (dtid.Rows.Count > 0)
{
string updt = "update tbl_SubCollection set FamilyId='" + lblFamilID.Text + "',HouseName='" + lblHouseNam.Text + "',MahalluRegNo='" + lblMahalluRegNo.Text + "',Date='" + date + "',Grade='" + txtSubsType.Text + "',SubAmount='" + txtSubsAmntGroup.Text + "',AmountPaid='" + lblAmountDisp.Text + "',Balance='" + txtBalnceGrp.Text + "',ReceiptNo='" + txtRecieptNo.Text + "',OldBalance='" + txtOldBalncGrp.Text + "',Status='True',FinYearID='" + lblFinID.Text + "' where SubCollID='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(updt, db.Connect());
db.cmd.ExecuteNonQuery();
string updtBS = "update tbl_BalanceSheet set BillDate='" + dtpColectdOn.Text + "' where FK='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(updtBS, db.Connect());
db.cmd.ExecuteNonQuery();
}
else
{
string str = "insert into tbl_SubCollection(FamilyId,HouseName,MahalluRegNo,Date,Grade,SubAmount,AmountPaid,Balance,ReceiptNo,OldBalance,Status,FinYearID)values('" + lblFamilID.Text + "','" + lblHouseNam.Text + "','" + lblMahalluRegNo.Text + "','" + date + "','" + txtSubsType.Text + "','" + txtSubsAmntGroup.Text + "','" + lblAmountDisp.Text + "','" + txtBalnceGrp.Text + "','" + txtRecieptNo.Text + "','" + txtOldBalncGrp.Text + "','True','" + lblFinID.Text + "')";
db.cmd = new SqlCommand(str, db.Connect());
db.cmd.ExecuteNonQuery();
string id = "select MAX(SubCollID) as ID from tbl_SubCollection";
db.cmd = new SqlCommand(id, db.Connect());
DataTable dtSubColID = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtSubColID);
string InsertBS = "insert into tbl_BalanceSheet(LedgerName,LedgerType,BillDate,Amount,StatusInCr,StatusOutDr,FK)values('Family-Subs-May','Income','" + dtpColectdOn.Text + "','" + lblAmountDisp.Text + "','" + lblAmountDisp.Text + "','0','" + dtSubColID.Rows[0][0].ToString() + "')";
db.cmd = new SqlCommand(InsertBS, db.Connect());
db.cmd.ExecuteNonQuery();
}
}
if (chbMay .Checked == false)
{
date = "05/01/" + System.DateTime.Now.ToString("yyyy");
string getid = "select SubCollID from tbl_SubCollection where Date='" + date + "' and FamilyId='" + lblFamilID.Text + "'";
db.cmd = new SqlCommand(getid, db.Connect());
DataTable dtid = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtid);
if (dtid.Rows.Count > 0)
{
string str2 = "delete from tbl_SubCollection where SubCollID='" + dtid.Rows[0][0].ToString() + "'and FamilyId='" + lblFamilID.Text + "' ";
db.cmd = new SqlCommand(str2, db.Connect());
db.cmd.ExecuteNonQuery();
string del = "delete from tbl_BalanceSheet where FK='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(del, db.Connect());
db.cmd.ExecuteNonQuery();
}
}
if (chbJune.Checked == true)
{
date = "06/01/" + System.DateTime.Now.ToString("yyyy");
string getid = "select SubCollID from tbl_SubCollection where Date='" + date + "' and FamilyId='" + lblFamilID.Text + "'";
db.cmd = new SqlCommand(getid, db.Connect());
DataTable dtid = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtid);
if (dtid.Rows.Count > 0)
{
string updt = "update tbl_SubCollection set FamilyId='" + lblFamilID.Text + "',HouseName='" + lblHouseNam.Text + "',MahalluRegNo='" + lblMahalluRegNo.Text + "',Date='" + date + "',Grade='" + txtSubsType.Text + "',SubAmount='" + txtSubsAmntGroup.Text + "',AmountPaid='" + lblAmountDisp.Text + "',Balance='" + txtBalnceGrp.Text + "',ReceiptNo='" + txtRecieptNo.Text + "',OldBalance='" + txtOldBalncGrp.Text + "',Status='True',FinYearID='" + lblFinID.Text + "' where SubCollID='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(updt, db.Connect());
db.cmd.ExecuteNonQuery();
string updtBS = "update tbl_BalanceSheet set BillDate='" + dtpColectdOn.Text + "' where FK='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(updtBS, db.Connect());
db.cmd.ExecuteNonQuery();
}
else
{
string str = "insert into tbl_SubCollection(FamilyId,HouseName,MahalluRegNo,Date,Grade,SubAmount,AmountPaid,Balance,ReceiptNo,OldBalance,Status,FinYearID)values('" + lblFamilID.Text + "','" + lblHouseNam.Text + "','" + lblMahalluRegNo.Text + "','" + date + "','" + txtSubsType.Text + "','" + txtSubsAmntGroup.Text + "','" + lblAmountDisp.Text + "','" + txtBalnceGrp.Text + "','" + txtRecieptNo.Text + "','" + txtOldBalncGrp.Text + "','True','" + lblFinID.Text + "')";
db.cmd = new SqlCommand(str, db.Connect());
db.cmd.ExecuteNonQuery();
string id = "select MAX(SubCollID) as ID from tbl_SubCollection";
db.cmd = new SqlCommand(id, db.Connect());
DataTable dtSubColID = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtSubColID);
string InsertBS = "insert into tbl_BalanceSheet(LedgerName,LedgerType,BillDate,Amount,StatusInCr,StatusOutDr,FK)values('Family-Subs-June','Income','" + dtpColectdOn.Text + "','" + lblAmountDisp.Text + "','" + lblAmountDisp.Text + "','0','" + dtSubColID.Rows[0][0].ToString() + "')";
db.cmd = new SqlCommand(InsertBS, db.Connect());
db.cmd.ExecuteNonQuery();
}
}
if (chbJune.Checked == false)
{
date = "06/01/" + System.DateTime.Now.ToString("yyyy");
string getid = "select SubCollID from tbl_SubCollection where Date='" + date + "' and FamilyId='" + lblFamilID.Text + "'";
db.cmd = new SqlCommand(getid, db.Connect());
DataTable dtid = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtid);
if (dtid.Rows.Count > 0)
{
string str2 = "delete from tbl_SubCollection where SubCollID='" + dtid.Rows[0][0].ToString() + "'and FamilyId='" + lblFamilID.Text + "' ";
db.cmd = new SqlCommand(str2, db.Connect());
db.cmd.ExecuteNonQuery();
string del = "delete from tbl_BalanceSheet where FK='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(del, db.Connect());
db.cmd.ExecuteNonQuery();
}
}
if (chbJuly.Checked == true)
{
date = "07/01/" + System.DateTime.Now.ToString("yyyy");
string getid = "select SubCollID from tbl_SubCollection where Date='" + date + "' and FamilyId='" + lblFamilID.Text + "'";
db.cmd = new SqlCommand(getid, db.Connect());
DataTable dtid = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtid);
if (dtid.Rows.Count > 0)
{
string updt = "update tbl_SubCollection set FamilyId='" + lblFamilID.Text + "',HouseName='" + lblHouseNam.Text + "',MahalluRegNo='" + lblMahalluRegNo.Text + "',Date='" + date + "',Grade='" + txtSubsType.Text + "',SubAmount='" + txtSubsAmntGroup.Text + "',AmountPaid='" + lblAmountDisp.Text + "',Balance='" + txtBalnceGrp.Text + "',ReceiptNo='" + txtRecieptNo.Text + "',OldBalance='" + txtOldBalncGrp.Text + "',Status='True',FinYearID='" + lblFinID.Text + "' where SubCollID='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(updt, db.Connect());
db.cmd.ExecuteNonQuery();
string updtBS = "update tbl_BalanceSheet set BillDate='" + dtpColectdOn.Text + "' where FK='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(updtBS, db.Connect());
db.cmd.ExecuteNonQuery();
}
else
{
string str = "insert into tbl_SubCollection(FamilyId,HouseName,MahalluRegNo,Date,Grade,SubAmount,AmountPaid,Balance,ReceiptNo,OldBalance,Status,FinYearID)values('" + lblFamilID.Text + "','" + lblHouseNam.Text + "','" + lblMahalluRegNo.Text + "','" + date + "','" + txtSubsType.Text + "','" + txtSubsAmntGroup.Text + "','" + lblAmountDisp.Text + "','" + txtBalnceGrp.Text + "','" + txtRecieptNo.Text + "','" + txtOldBalncGrp.Text + "','True','" + lblFinID.Text + "')";
db.cmd = new SqlCommand(str, db.Connect());
db.cmd.ExecuteNonQuery();
string id = "select MAX(SubCollID) as ID from tbl_SubCollection";
db.cmd = new SqlCommand(id, db.Connect());
DataTable dtSubColID = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtSubColID);
string InsertBS = "insert into tbl_BalanceSheet(LedgerName,LedgerType,BillDate,Amount,StatusInCr,StatusOutDr,FK)values('Family-Subs-July','Income','" + dtpColectdOn.Text + "','" + lblAmountDisp.Text + "','" + lblAmountDisp.Text + "','0','" + dtSubColID.Rows[0][0].ToString() + "')";
db.cmd = new SqlCommand(InsertBS, db.Connect());
db.cmd.ExecuteNonQuery();
}
}
if (chbJuly .Checked == false)
{
date = "07/01/" + System.DateTime.Now.ToString("yyyy");
string getid = "select SubCollID from tbl_SubCollection where Date='" + date + "' and FamilyId='" + lblFamilID.Text + "'";
db.cmd = new SqlCommand(getid, db.Connect());
DataTable dtid = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtid);
if (dtid.Rows.Count > 0)
{
string str2 = "delete from tbl_SubCollection where SubCollID='" + dtid.Rows[0][0].ToString() + "'and FamilyId='" + lblFamilID.Text + "' ";
db.cmd = new SqlCommand(str2, db.Connect());
db.cmd.ExecuteNonQuery();
string del = "delete from tbl_BalanceSheet where FK='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(del, db.Connect());
db.cmd.ExecuteNonQuery();
}
}
if (chbAug.Checked == true)
{
date = "08/01/" + System.DateTime.Now.ToString("yyyy");
string getid = "select SubCollID from tbl_SubCollection where Date='" + date + "' and FamilyId='" + lblFamilID.Text + "'";
db.cmd = new SqlCommand(getid, db.Connect());
DataTable dtid = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtid);
if (dtid.Rows.Count > 0)
{
string updt = "update tbl_SubCollection set FamilyId='" + lblFamilID.Text + "',HouseName='" + lblHouseNam.Text + "',MahalluRegNo='" + lblMahalluRegNo.Text + "',Date='" + date + "',Grade='" + txtSubsType.Text + "',SubAmount='" + txtSubsAmntGroup.Text + "',AmountPaid='" + lblAmountDisp.Text + "',Balance='" + txtBalnceGrp.Text + "',ReceiptNo='" + txtRecieptNo.Text + "',OldBalance='" + txtOldBalncGrp.Text + "',Status='True',FinYearID='" + lblFinID.Text + "' where SubCollID='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(updt, db.Connect());
db.cmd.ExecuteNonQuery();
string updtBS = "update tbl_BalanceSheet set BillDate='" + dtpColectdOn.Text + "' where FK='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(updtBS, db.Connect());
db.cmd.ExecuteNonQuery();
}
else
{
string str = "insert into tbl_SubCollection(FamilyId,HouseName,MahalluRegNo,Date,Grade,SubAmount,AmountPaid,Balance,ReceiptNo,OldBalance,Status,FinYearID)values('" + lblFamilID.Text + "','" + lblHouseNam.Text + "','" + lblMahalluRegNo.Text + "','" + date + "','" + txtSubsType.Text + "','" + txtSubsAmntGroup.Text + "','" + lblAmountDisp.Text + "','" + txtBalnceGrp.Text + "','" + txtRecieptNo.Text + "','" + txtOldBalncGrp.Text + "','True','" + lblFinID.Text + "')";
db.cmd = new SqlCommand(str, db.Connect());
db.cmd.ExecuteNonQuery();
string id = "select MAX(SubCollID) as ID from tbl_SubCollection";
db.cmd = new SqlCommand(id, db.Connect());
DataTable dtSubColID = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtSubColID);
string InsertBS = "insert into tbl_BalanceSheet(LedgerName,LedgerType,BillDate,Amount,StatusInCr,StatusOutDr,FK)values('Family-Subs-August','Income','" + dtpColectdOn.Text + "','" + lblAmountDisp.Text + "','" + lblAmountDisp.Text + "','0','" + dtSubColID.Rows[0][0].ToString() + "')";
db.cmd = new SqlCommand(InsertBS, db.Connect());
db.cmd.ExecuteNonQuery();
}
}
if (chbAug .Checked == false)
{
date = "08/01/" + System.DateTime.Now.ToString("yyyy");
string getid = "select SubCollID from tbl_SubCollection where Date='" + date + "' and FamilyId='" + lblFamilID.Text + "'";
db.cmd = new SqlCommand(getid, db.Connect());
DataTable dtid = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtid);
if (dtid.Rows.Count > 0)
{
string str2 = "delete from tbl_SubCollection where SubCollID='" + dtid.Rows[0][0].ToString() + "'and FamilyId='" + lblFamilID.Text + "' ";
db.cmd = new SqlCommand(str2, db.Connect());
db.cmd.ExecuteNonQuery();
string del = "delete from tbl_BalanceSheet where FK='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(del, db.Connect());
db.cmd.ExecuteNonQuery();
}
}
if (chbSept.Checked == true)
{
date = "09/01/" + System.DateTime.Now.ToString("yyyy");
string getid = "select SubCollID from tbl_SubCollection where Date='" + date + "' and FamilyId='" + lblFamilID.Text + "'";
db.cmd = new SqlCommand(getid, db.Connect());
DataTable dtid = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtid);
if (dtid.Rows.Count > 0)
{
string updt = "update tbl_SubCollection set FamilyId='" + lblFamilID.Text + "',HouseName='" + lblHouseNam.Text + "',MahalluRegNo='" + lblMahalluRegNo.Text + "',Date='" + date + "',Grade='" + txtSubsType.Text + "',SubAmount='" + txtSubsAmntGroup.Text + "',AmountPaid='" + lblAmountDisp.Text + "',Balance='" + txtBalnceGrp.Text + "',ReceiptNo='" + txtRecieptNo.Text + "',OldBalance='" + txtOldBalncGrp.Text + "',Status='True',FinYearID='" + lblFinID.Text + "' where SubCollID='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(updt, db.Connect());
db.cmd.ExecuteNonQuery();
string updtBS = "update tbl_BalanceSheet set BillDate='" + dtpColectdOn.Text + "' where FK='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(updtBS, db.Connect());
db.cmd.ExecuteNonQuery();
}
else
{
string str = "insert into tbl_SubCollection(FamilyId,HouseName,MahalluRegNo,Date,Grade,SubAmount,AmountPaid,Balance,ReceiptNo,OldBalance,Status,FinYearID)values('" + lblFamilID.Text + "','" + lblHouseNam.Text + "','" + lblMahalluRegNo.Text + "','" + date + "','" + txtSubsType.Text + "','" + txtSubsAmntGroup.Text + "','" + lblAmountDisp.Text + "','" + txtBalnceGrp.Text + "','" + txtRecieptNo.Text + "','" + txtOldBalncGrp.Text + "','True','" + lblFinID.Text + "')";
db.cmd = new SqlCommand(str, db.Connect());
db.cmd.ExecuteNonQuery();
string id = "select MAX(SubCollID) as ID from tbl_SubCollection";
db.cmd = new SqlCommand(id, db.Connect());
DataTable dtSubColID = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtSubColID);
string InsertBS = "insert into tbl_BalanceSheet(LedgerName,LedgerType,BillDate,Amount,StatusInCr,StatusOutDr,FK)values('Family-Subs-September','Income','" + dtpColectdOn.Text + "','" + lblAmountDisp.Text + "','" + lblAmountDisp.Text + "','0','" + dtSubColID.Rows[0][0].ToString() + "')";
db.cmd = new SqlCommand(InsertBS, db.Connect());
db.cmd.ExecuteNonQuery();
}
}
if (chbSept .Checked == false)
{
date = "09/01/" + System.DateTime.Now.ToString("yyyy");
string getid = "select SubCollID from tbl_SubCollection where Date='" + date + "' and FamilyId='" + lblFamilID.Text + "'";
db.cmd = new SqlCommand(getid, db.Connect());
DataTable dtid = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtid);
if (dtid.Rows.Count > 0)
{
string str2 = "delete from tbl_SubCollection where SubCollID='" + dtid.Rows[0][0].ToString() + "'and FamilyId='" + lblFamilID.Text + "' ";
db.cmd = new SqlCommand(str2, db.Connect());
db.cmd.ExecuteNonQuery();
string del = "delete from tbl_BalanceSheet where FK='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(del, db.Connect());
db.cmd.ExecuteNonQuery();
}
}
if (chbOct.Checked == true)
{
date = "10/01/" + System.DateTime.Now.ToString("yyyy");
string getid = "select SubCollID from tbl_SubCollection where Date='" + date + "' and FamilyId='" + lblFamilID.Text + "'";
db.cmd = new SqlCommand(getid, db.Connect());
DataTable dtid = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtid);
if (dtid.Rows.Count > 0)
{
string updt = "update tbl_SubCollection set FamilyId='" + lblFamilID.Text + "',HouseName='" + lblHouseNam.Text + "',MahalluRegNo='" + lblMahalluRegNo.Text + "',Date='" + date + "',Grade='" + txtSubsType.Text + "',SubAmount='" + txtSubsAmntGroup.Text + "',AmountPaid='" + lblAmountDisp.Text + "',Balance='" + txtBalnceGrp.Text + "',ReceiptNo='" + txtRecieptNo.Text + "',OldBalance='" + txtOldBalncGrp.Text + "',Status='True',FinYearID='" + lblFinID.Text + "' where SubCollID='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(updt, db.Connect());
db.cmd.ExecuteNonQuery();
string updtBS = "update tbl_BalanceSheet set BillDate='" + dtpColectdOn.Text + "' where FK='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(updtBS, db.Connect());
db.cmd.ExecuteNonQuery();
}
else
{
string str = "insert into tbl_SubCollection(FamilyId,HouseName,MahalluRegNo,Date,Grade,SubAmount,AmountPaid,Balance,ReceiptNo,OldBalance,Status,FinYearID)values('" + lblFamilID.Text + "','" + lblHouseNam.Text + "','" + lblMahalluRegNo.Text + "','" + date + "','" + txtSubsType.Text + "','" + txtSubsAmntGroup.Text + "','" + lblAmountDisp.Text + "','" + txtBalnceGrp.Text + "','" + txtRecieptNo.Text + "','" + txtOldBalncGrp.Text + "','True','" + lblFinID.Text + "')";
db.cmd = new SqlCommand(str, db.Connect());
db.cmd.ExecuteNonQuery();
string id = "select MAX(SubCollID) as ID from tbl_SubCollection";
db.cmd = new SqlCommand(id, db.Connect());
DataTable dtSubColID = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtSubColID);
string InsertBS = "insert into tbl_BalanceSheet(LedgerName,LedgerType,BillDate,Amount,StatusInCr,StatusOutDr,FK)values('Family-Subs-October','Income','" + dtpColectdOn.Text + "','" + lblAmountDisp.Text + "','" + lblAmountDisp.Text + "','0','" + dtSubColID.Rows[0][0].ToString() + "')";
db.cmd = new SqlCommand(InsertBS, db.Connect());
db.cmd.ExecuteNonQuery();
}
}
if (chbOct .Checked == false)
{
date = "10/01/" + System.DateTime.Now.ToString("yyyy");
string getid = "select SubCollID from tbl_SubCollection where Date='" + date + "' and FamilyId='" + lblFamilID.Text + "'";
db.cmd = new SqlCommand(getid, db.Connect());
DataTable dtid = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtid);
if (dtid.Rows.Count > 0)
{
string str2 = "delete from tbl_SubCollection where SubCollID='" + dtid.Rows[0][0].ToString() + "'and FamilyId='" + lblFamilID.Text + "' ";
db.cmd = new SqlCommand(str2, db.Connect());
db.cmd.ExecuteNonQuery();
string del = "delete from tbl_BalanceSheet where FK='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(del, db.Connect());
db.cmd.ExecuteNonQuery();
}
}
if (chbNov.Checked == true)
{
date = "11/01/" + System.DateTime.Now.ToString("yyyy");
string getid = "select SubCollID from tbl_SubCollection where Date='" + date + "' and FamilyId='" + lblFamilID.Text + "'";
db.cmd = new SqlCommand(getid, db.Connect());
DataTable dtid = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtid);
if (dtid.Rows.Count > 0)
{
string updt = "update tbl_SubCollection set FamilyId='" + lblFamilID.Text + "',HouseName='" + lblHouseNam.Text + "',MahalluRegNo='" + lblMahalluRegNo.Text + "',Date='" + date + "',Grade='" + txtSubsType.Text + "',SubAmount='" + txtSubsAmntGroup.Text + "',AmountPaid='" + lblAmountDisp.Text + "',Balance='" + txtBalnceGrp.Text + "',ReceiptNo='" + txtRecieptNo.Text + "',OldBalance='" + txtOldBalncGrp.Text + "',Status='True',FinYearID='" + lblFinID.Text + "' where SubCollID='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(updt, db.Connect());
db.cmd.ExecuteNonQuery();
string updtBS = "update tbl_BalanceSheet set BillDate='" + dtpColectdOn.Text + "' where FK='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(updtBS, db.Connect());
db.cmd.ExecuteNonQuery();
}
else
{
string str = "insert into tbl_SubCollection(FamilyId,HouseName,MahalluRegNo,Date,Grade,SubAmount,AmountPaid,Balance,ReceiptNo,OldBalance,Status,FinYearID)values('" + lblFamilID.Text + "','" + lblHouseNam.Text + "','" + lblMahalluRegNo.Text + "','" + date + "','" + txtSubsType.Text + "','" + txtSubsAmntGroup.Text + "','" + lblAmountDisp.Text + "','" + txtBalnceGrp.Text + "','" + txtRecieptNo.Text + "','" + txtOldBalncGrp.Text + "','True','" + lblFinID.Text + "')";
db.cmd = new SqlCommand(str, db.Connect());
db.cmd.ExecuteNonQuery();
string id = "select MAX(SubCollID) as ID from tbl_SubCollection";
db.cmd = new SqlCommand(id, db.Connect());
DataTable dtSubColID = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtSubColID);
string InsertBS = "insert into tbl_BalanceSheet(LedgerName,LedgerType,BillDate,Amount,StatusInCr,StatusOutDr,FK)values('Family-Subs-November','Income','" + dtpColectdOn.Text + "','" + lblAmountDisp.Text + "','" + lblAmountDisp.Text + "','0','" + dtSubColID.Rows[0][0].ToString() + "')";
db.cmd = new SqlCommand(InsertBS, db.Connect());
db.cmd.ExecuteNonQuery();
}
}
if (chbNov .Checked == false)
{
date = "11/01/" + System.DateTime.Now.ToString("yyyy");
string getid = "select SubCollID from tbl_SubCollection where Date='" + date + "' and FamilyId='" + lblFamilID.Text + "'";
db.cmd = new SqlCommand(getid, db.Connect());
DataTable dtid = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtid);
if (dtid.Rows.Count > 0)
{
string str2 = "delete from tbl_SubCollection where SubCollID='" + dtid.Rows[0][0].ToString() + "'and FamilyId='" + lblFamilID.Text + "' ";
db.cmd = new SqlCommand(str2, db.Connect());
db.cmd.ExecuteNonQuery();
string del = "delete from tbl_BalanceSheet where FK='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(del, db.Connect());
db.cmd.ExecuteNonQuery();
}
}
if (chbDec.Checked == true)
{
date = "12/01/" + System.DateTime.Now.ToString("yyyy");
string getid = "select SubCollID from tbl_SubCollection where Date='" + date + "' and FamilyId='" + lblFamilID.Text + "'";
db.cmd = new SqlCommand(getid, db.Connect());
DataTable dtid = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtid);
if (dtid.Rows.Count > 0)
{
string updt = "update tbl_SubCollection set FamilyId='" + lblFamilID.Text + "',HouseName='" + lblHouseNam.Text + "',MahalluRegNo='" + lblMahalluRegNo.Text + "',Date='" + date + "',Grade='" + txtSubsType.Text + "',SubAmount='" + txtSubsAmntGroup.Text + "',AmountPaid='" + lblAmountDisp.Text + "',Balance='" + txtBalnceGrp.Text + "',ReceiptNo='" + txtRecieptNo.Text + "',OldBalance='" + txtOldBalncGrp.Text + "',Status='True',FinYearID='" + lblFinID.Text + "' where SubCollID='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(updt, db.Connect());
db.cmd.ExecuteNonQuery();
string updtBS = "update tbl_BalanceSheet set BillDate='" + dtpColectdOn.Text + "' where FK='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(updtBS, db.Connect());
db.cmd.ExecuteNonQuery();
}
else
{
string str = "insert into tbl_SubCollection(FamilyId,HouseName,MahalluRegNo,Date,Grade,SubAmount,AmountPaid,Balance,ReceiptNo,OldBalance,Status,FinYearID)values('" + lblFamilID.Text + "','" + lblHouseNam.Text + "','" + lblMahalluRegNo.Text + "','" + date + "','" + txtSubsType.Text + "','" + txtSubsAmntGroup.Text + "','" + lblAmountDisp.Text + "','" + txtBalnceGrp.Text + "','" + txtRecieptNo.Text + "','" + txtOldBalncGrp.Text + "','True','" + lblFinID.Text + "')";
db.cmd = new SqlCommand(str, db.Connect());
db.cmd.ExecuteNonQuery();
string id = "select MAX(SubCollID) as ID from tbl_SubCollection";
db.cmd = new SqlCommand(id, db.Connect());
DataTable dtSubColID = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtSubColID);
string InsertBS = "insert into tbl_BalanceSheet(LedgerName,LedgerType,BillDate,Amount,StatusInCr,StatusOutDr,FK)values('Family-Subs-December','Income','" + dtpColectdOn.Text + "','" + lblAmountDisp.Text + "','" + lblAmountDisp.Text + "','0','" + dtSubColID.Rows[0][0].ToString() + "')";
db.cmd = new SqlCommand(InsertBS, db.Connect());
db.cmd.ExecuteNonQuery();
}
}
if (chbDec .Checked == false)
{
date = "12/01/" + System.DateTime.Now.ToString("yyyy");
string getid = "select SubCollID from tbl_SubCollection where Date='" + date + "' and FamilyId='" + lblFamilID.Text + "'";
db.cmd = new SqlCommand(getid, db.Connect());
DataTable dtid = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtid);
if (dtid.Rows.Count > 0)
{
string str2 = "delete from tbl_SubCollection where SubCollID='" + dtid.Rows[0][0].ToString() + "'and FamilyId='" + lblFamilID.Text + "' ";
db.cmd = new SqlCommand(str2, db.Connect());
db.cmd.ExecuteNonQuery();
string del = "delete from tbl_BalanceSheet where FK='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(del, db.Connect());
db.cmd.ExecuteNonQuery();
}
}
MessageBox.Show("Saved");
}
}
private void btnUpdateGrp_Click(object sender, EventArgs e)
{
MessageBox.Show("Updated");
}
private void btnPrintGrp_Click(object sender, EventArgs e)
{
}
private void dgvMemberrGP_CellEnter(object sender, DataGridViewCellEventArgs e)
{
TotalamntGrp = 0;
if (e.ColumnIndex == Month2.Index && e.RowIndex >= 0)
{
panelGrp.Visible = true;
}
else
{
panelGrp.Visible = false;
}
lblMmbrID_GM.Text = dgvMemberrGP.Rows[e.RowIndex].Cells[1].Value.ToString();
lblMmbrAmnt_GM.Text = dgvMemberrGP.Rows[e.RowIndex].Cells[5].Value.ToString();
lblMmbrName_GM.Text = dgvMemberrGP.Rows[e.RowIndex].Cells[3].Value.ToString();
lblMembID_GM2.Text = dgvMemberrGP.Rows[e.RowIndex].Cells[2].Value.ToString();
txtSubsTyp_GM.Text = dgvMemberrGP.Rows[e.RowIndex].Cells[4].Value.ToString();
txtSubsAmnt_GM.Text = dgvMemberrGP.Rows[e.RowIndex].Cells[5].Value.ToString();
ViewPaidBalnceMembrs();
}
private void ViewPaidBalnceMembrs()
{
TotalamntGrp = 0;
chbJan2.Checked = false; chbFeb2.Checked = false; chbMar2.Checked = false;
chbApril2.Checked = false; chbMay2.Checked = false; chbJune2.Checked = false;
chbJuly2.Checked = false; chbAug2.Checked = false; chbSep2.Checked = false;
chbOct2.Checked = false; chbNov2.Checked = false; chbDec2.Checked = false;
string str = "select Date from tbl_Sub_MemCollection where MemberId='" + lblMmbrID_GM.Text + "' and Grade='" + txtSubsTyp_GM.Text + "'";
db.cmd = new SqlCommand(str, db.Connect());
db.adapter = new SqlDataAdapter(db.cmd);
DataTable dt = new DataTable();
dt.Clear();
db.adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
string Paiddate = dt.Rows[i][0].ToString();
string paidMonth = Paiddate.Substring(0, 2);
if (paidMonth == "01") { chbJan2 .Checked = true; }
if (paidMonth == "02") { chbFeb2 .Checked = true; }
if (paidMonth == "03") { chbMar2 .Checked = true; }
if (paidMonth == "04") { chbApril2 .Checked = true; }
if (paidMonth == "05") { chbMay2 .Checked = true; }
if (paidMonth == "06") { chbJune2 .Checked = true; }
if (paidMonth == "07") { chbJuly2 .Checked = true; }
if (paidMonth == "08") { chbAug2 .Checked = true; }
if (paidMonth == "09") { chbSep2 .Checked = true; }
if (paidMonth == "10") { chbOct2 .Checked = true; }
if (paidMonth == "11") { chbNov2 .Checked = true; }
if (paidMonth == "12") { chbDec2 .Checked = true; }
txtTotal_GM.Text = (double.Parse(lblMmbrAmnt_GM.Text) * dt.Rows.Count).ToString();
TotalamntGrp = double.Parse(lblMmbrAmnt_GM.Text) * dt.Rows.Count;
//btnSave_GM.Enabled = false;
//btnUpdate_GM.Enabled = true;
}
}
else
{
chbJan2.Checked = false; chbFeb2.Checked = false; chbMar2.Checked = false;
chbApril2.Checked = false; chbMay2.Checked = false; chbJune2.Checked = false;
chbJuly2.Checked = false; chbAug2.Checked = false; chbSep2.Checked = false;
chbOct2.Checked = false; chbNov2.Checked = false; chbDec2.Checked = false;
TotalamntGrp = 0;
//btnSave_GM.Enabled = true;
//btnUpdate_GM.Enabled = false;
}
}
private void chbAll2_CheckedChanged(object sender, EventArgs e)
{
if (chbAll2 .Checked == true)
{
chbJan2 .Checked = true; chbFeb2 .Checked = true; chbMar2 .Checked = true;
chbApril2 .Checked = true; chbMay2 .Checked = true; chbJune2 .Checked = true;
chbJuly2 .Checked = true; chbAug2 .Checked = true; chbSep2 .Checked = true;
chbOct2 .Checked = true; chbNov2 .Checked = true; chbDec2 .Checked = true;
}
if (chbAll2 .Checked == false)
{
chbJan2 .Checked = false; chbFeb2 .Checked = false; chbMar2 .Checked = false;
chbApril2 .Checked = false; chbMay2 .Checked = false; chbJune2 .Checked = false;
chbJuly2 .Checked = false; chbAug2 .Checked = false; chbSep2 .Checked = false;
chbOct2 .Checked = false; chbNov2 .Checked = false; chbDec2 .Checked = false;
}
}
private void chbJan2_CheckedChanged(object sender, EventArgs e)
{
if (chbJan2 .Checked == true)
{
TotalamntGrp = TotalamntGrp + double.Parse(lblMmbrAmnt_GM.Text);
txtTotal_GM.Text = TotalamntGrp.ToString();
}
if (chbJan2 .Checked == false)
{
TotalamntGrp = TotalamntGrp - double.Parse(lblMmbrAmnt_GM.Text);
txtTotal_GM.Text = TotalamntGrp.ToString();
}
}
private void chbFeb2_CheckedChanged(object sender, EventArgs e)
{
if (chbFeb2.Checked == true)
{
TotalamntGrp = TotalamntGrp + double.Parse(lblMmbrAmnt_GM.Text);
txtTotal_GM.Text = TotalamntGrp.ToString();
}
if (chbFeb2.Checked == false)
{
TotalamntGrp = TotalamntGrp - double.Parse(lblMmbrAmnt_GM.Text);
txtTotal_GM.Text = TotalamntGrp.ToString();
}
}
private void chbMar2_CheckedChanged(object sender, EventArgs e)
{
if (chbMar2.Checked == true)
{
TotalamntGrp = TotalamntGrp + double.Parse(lblMmbrAmnt_GM.Text);
txtTotal_GM.Text = TotalamntGrp.ToString();
}
if (chbMar2.Checked == false)
{
TotalamntGrp = TotalamntGrp - double.Parse(lblMmbrAmnt_GM.Text);
txtTotal_GM.Text = TotalamntGrp.ToString();
}
}
private void chbApril2_CheckedChanged(object sender, EventArgs e)
{
if (chbApril2 .Checked == true)
{
TotalamntGrp = TotalamntGrp + double.Parse(lblMmbrAmnt_GM.Text);
txtTotal_GM.Text = TotalamntGrp.ToString();
}
if (chbApril2.Checked == false)
{
TotalamntGrp = TotalamntGrp - double.Parse(lblMmbrAmnt_GM.Text);
txtTotal_GM.Text = TotalamntGrp.ToString();
}
}
private void chbMay2_CheckedChanged(object sender, EventArgs e)
{
if (chbMay2.Checked == true)
{
TotalamntGrp = TotalamntGrp + double.Parse(lblMmbrAmnt_GM.Text);
txtTotal_GM.Text = TotalamntGrp.ToString();
}
if (chbMay2.Checked == false)
{
TotalamntGrp = TotalamntGrp - double.Parse(lblMmbrAmnt_GM.Text);
txtTotal_GM.Text = TotalamntGrp.ToString();
}
}
private void chbJune2_CheckedChanged(object sender, EventArgs e)
{
if (chbJune2.Checked == true)
{
TotalamntGrp = TotalamntGrp + double.Parse(lblMmbrAmnt_GM.Text);
txtTotal_GM.Text = TotalamntGrp.ToString();
}
if (chbJune2.Checked == false)
{
TotalamntGrp = TotalamntGrp - double.Parse(lblMmbrAmnt_GM.Text);
txtTotal_GM.Text = TotalamntGrp.ToString();
}
}
private void chbJuly2_CheckedChanged(object sender, EventArgs e)
{
if (chbJuly2.Checked == true)
{
TotalamntGrp = TotalamntGrp + double.Parse(lblMmbrAmnt_GM.Text);
txtTotal_GM.Text = TotalamntGrp.ToString();
}
if (chbJuly2.Checked == false)
{
TotalamntGrp = TotalamntGrp - double.Parse(lblMmbrAmnt_GM.Text);
txtTotal_GM.Text = TotalamntGrp.ToString();
}
}
private void chbAug2_CheckedChanged(object sender, EventArgs e)
{
if (chbAug2.Checked == true)
{
TotalamntGrp = TotalamntGrp + double.Parse(lblMmbrAmnt_GM.Text);
txtTotal_GM.Text = TotalamntGrp.ToString();
}
if (chbAug2.Checked == false)
{
TotalamntGrp = TotalamntGrp - double.Parse(lblMmbrAmnt_GM.Text);
txtTotal_GM.Text = TotalamntGrp.ToString();
}
}
private void chbSep2_CheckedChanged(object sender, EventArgs e)
{
if (chbSep2 .Checked == true)
{
TotalamntGrp = TotalamntGrp + double.Parse(lblMmbrAmnt_GM.Text);
txtTotal_GM.Text = TotalamntGrp.ToString();
}
if (chbSep2.Checked == false)
{
TotalamntGrp = TotalamntGrp - double.Parse(lblMmbrAmnt_GM.Text);
txtTotal_GM.Text = TotalamntGrp.ToString();
}
}
private void chbOct2_CheckedChanged(object sender, EventArgs e)
{
if (chbOct2 .Checked == true)
{
TotalamntGrp = TotalamntGrp + double.Parse(lblMmbrAmnt_GM.Text);
txtTotal_GM.Text = TotalamntGrp.ToString();
}
if (chbOct2.Checked == false)
{
TotalamntGrp = TotalamntGrp - double.Parse(lblMmbrAmnt_GM.Text);
txtTotal_GM.Text = TotalamntGrp.ToString();
}
}
private void chbNov2_CheckedChanged(object sender, EventArgs e)
{
if (chbNov2 .Checked == true)
{
TotalamntGrp = TotalamntGrp + double.Parse(lblMmbrAmnt_GM.Text);
txtTotal_GM.Text = TotalamntGrp.ToString();
}
if (chbNov2 .Checked == false)
{
TotalamntGrp = TotalamntGrp - double.Parse(lblMmbrAmnt_GM.Text);
txtTotal_GM.Text = TotalamntGrp.ToString();
}
}
private void chbDec2_CheckedChanged(object sender, EventArgs e)
{
if (chbDec2 .Checked == true)
{
TotalamntGrp = TotalamntGrp + double.Parse(lblMmbrAmnt_GM.Text);
txtTotal_GM.Text = TotalamntGrp.ToString();
}
if (chbDec2.Checked == false)
{
TotalamntGrp = TotalamntGrp - double.Parse(lblMmbrAmnt_GM.Text);
txtTotal_GM.Text = TotalamntGrp.ToString();
}
}
private void btnSave_GM_Click(object sender, EventArgs e)
{
Boolean flag = true;
if (txtReceipt_GM.Text == "")
{
flag = false;
errorProvider1.SetError(txtReceipt_GM, "Reqiured");
}
string str1 = "select ReceiptNo from tbl_Sub_MemCollection where ReceiptNo='" + txtReceipt_GM.Text + "'";
db.cmd = new SqlCommand(str1, db.Connect());
db.adapter = new SqlDataAdapter(db.cmd);
DataTable dtreciept1 = new DataTable();
db.adapter.Fill(dtreciept1);
if (dtreciept1.Rows.Count > 0)
{
flag = false;
MessageBox.Show("Receipt Number Already Exist", "Pay Balance");
}
if (flag == true)
{
string date = "";
if (chbJan2.Checked == true)
{
date = "01/01/" + System.DateTime.Now.ToString("yyyy");
string getid = "select SubMCollID from tbl_Sub_MemCollection where Date='" + date + "'";
db.cmd = new SqlCommand(getid, db.Connect());
DataTable dtid = new DataTable();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtid);
if (dtid.Rows.Count > 0)
{
string updt = "update tbl_Sub_MemCollection set MemberId='" + lblMmbrID_GM.Text + "',Memb_ID='" + lblMembID_GM2.Text + "',MemName='" + lblMmbrName_GM.Text + "',Date='" + date + "',Grade='" + txtSubsTyp_GM.Text + "',SubAmount='" + lblMmbrAmnt_GM.Text + "',AmountPaid='" + txtSubsAmnt_GM.Text + "',Balance='" + txtBalnce_GM.Text + "',ReceiptNo='" + txtReceipt_GM.Text + "',FinYearID='" + lblFinID.Text + "' where SubMCollID='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(updt, db.Connect());
db.cmd.ExecuteNonQuery();
string updtBS = "update tbl_BalanceSheet set BillDate='" + dtpCollectdON.Text + "' where FK='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(updtBS, db.Connect());
db.cmd.ExecuteNonQuery();
}
else
{
string str = "insert into tbl_Sub_MemCollection(MemberId,Memb_ID,MemName,Date,Grade,SubAmount,AmountPaid,Balance,ReceiptNo,FinYearID)values('" + lblMmbrID_GM.Text + "','" + lblMembID_GM2.Text + "','" + lblMmbrName_GM.Text + "','" + date + "','" + txtSubsTyp_GM.Text + "','" + txtSubsAmnt_GM.Text + "','" + lblMmbrAmnt_GM.Text + "','" + txtBalnce_GM.Text + "','" + txtReceipt_GM.Text + "','" + lblFinID.Text + "')";
db.cmd = new SqlCommand(str, db.Connect());
db.cmd.ExecuteNonQuery();
string id = "select MAX(SubMCollID) as ID from tbl_Sub_MemCollection";
db.cmd = new SqlCommand(id, db.Connect());
DataTable dtSubColID = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtSubColID);
string InsertBS = "insert into tbl_BalanceSheet(LedgerName,LedgerType,BillDate,Amount,StatusInCr,StatusOutDr,FK)values('Memb-Subs-January','Income','" + dtpCollectdON.Text + "','" + lblMmbrAmnt_GM.Text + "','" + lblMmbrAmnt_GM.Text + "','0','" + dtSubColID.Rows[0][0].ToString() + "')";
db.cmd = new SqlCommand(InsertBS, db.Connect());
db.cmd.ExecuteNonQuery();
}
}
if (chbJan2 .Checked == false)
{
date = "01/01/" + System.DateTime.Now.ToString("yyyy");
string getid = "select SubMCollID from tbl_Sub_MemCollection where Date='" + date + "' and MemberId='" + lblMmbrID_GM.Text + "'";
db.cmd = new SqlCommand(getid, db.Connect());
DataTable dtid = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtid);
if (dtid.Rows.Count > 0)
{
string str2 = "delete from tbl_Sub_MemCollection where SubMCollID='" + dtid.Rows[0][0].ToString() + "'and MemberId='" + lblMmbrID_GM.Text + "'";
db.cmd = new SqlCommand(str2, db.Connect());
db.cmd.ExecuteNonQuery();
string del = "delete from tbl_BalanceSheet where FK='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(del, db.Connect());
db.cmd.ExecuteNonQuery();
}
}
if (chbFeb2 .Checked == true)
{
date = "02/01/" + System.DateTime.Now.ToString("yyyy");
string getid = "select SubMCollID from tbl_Sub_MemCollection where Date='" + date + "'";
db.cmd = new SqlCommand(getid, db.Connect());
DataTable dtid = new DataTable();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtid);
if (dtid.Rows.Count > 0)
{
string updt = "update tbl_Sub_MemCollection set MemberId='" + lblMmbrID_GM.Text + "',Memb_ID='" + lblMembID_GM2.Text + "',MemName='" + lblMmbrName_GM.Text + "',Date='" + date + "',Grade='" + txtSubsTyp_GM.Text + "',SubAmount='" + lblMmbrAmnt_GM.Text + "',AmountPaid='" + txtSubsAmnt_GM.Text + "',Balance='" + txtBalnce_GM.Text + "',ReceiptNo='" + txtReceipt_GM.Text + "',FinYearID='" + lblFinID.Text + "' where SubMCollID='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(updt, db.Connect());
db.cmd.ExecuteNonQuery();
string updtBS = "update tbl_BalanceSheet set BillDate='" + dtpCollectdON.Text + "' where FK='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(updtBS, db.Connect());
db.cmd.ExecuteNonQuery();
}
else
{
string str = "insert into tbl_Sub_MemCollection(MemberId,Memb_ID,MemName,Date,Grade,SubAmount,AmountPaid,Balance,ReceiptNo,FinYearID)values('" + lblMmbrID_GM.Text + "','" + lblMembID_GM2.Text + "','" + lblMmbrName_GM.Text + "','" + date + "','" + txtSubsTyp_GM.Text + "','" + txtSubsAmnt_GM.Text + "','" + lblMmbrAmnt_GM.Text + "','" + txtBalnce_GM.Text + "','" + txtReceipt_GM.Text + "','" + lblFinID.Text + "')";
db.cmd = new SqlCommand(str, db.Connect());
db.cmd.ExecuteNonQuery();
string id = "select MAX(SubMCollID) as ID from tbl_Sub_MemCollection";
db.cmd = new SqlCommand(id, db.Connect());
DataTable dtSubColID = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtSubColID);
string InsertBS = "insert into tbl_BalanceSheet(LedgerName,LedgerType,BillDate,Amount,StatusInCr,StatusOutDr,FK)values('Memb-Subs-February','Income','" + dtpCollectdON.Text + "','" + lblMmbrAmnt_GM.Text + "','" + lblMmbrAmnt_GM.Text + "','0','" + dtSubColID.Rows[0][0].ToString() + "')";
db.cmd = new SqlCommand(InsertBS, db.Connect());
db.cmd.ExecuteNonQuery();
}
}
if (chbFeb2 .Checked == false)
{
date = "02/01/" + System.DateTime.Now.ToString("yyyy");
string getid = "select SubMCollID from tbl_Sub_MemCollection where Date='" + date + "' and MemberId='" + lblMmbrID_GM.Text + "'";
db.cmd = new SqlCommand(getid, db.Connect());
DataTable dtid = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtid);
if (dtid.Rows.Count > 0)
{
string str2 = "delete from tbl_Sub_MemCollection where SubMCollID='" + dtid.Rows[0][0].ToString() + "'and MemberId='" + lblMmbrID_GM.Text + "'";
db.cmd = new SqlCommand(str2, db.Connect());
db.cmd.ExecuteNonQuery();
string del = "delete from tbl_BalanceSheet where FK='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(del, db.Connect());
db.cmd.ExecuteNonQuery();
}
}
if (chbMar2 .Checked == true)
{
date = "03/01/" + System.DateTime.Now.ToString("yyyy");
string getid = "select SubMCollID from tbl_Sub_MemCollection where Date='" + date + "'";
db.cmd = new SqlCommand(getid, db.Connect());
DataTable dtid = new DataTable();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtid);
if (dtid.Rows.Count > 0)
{
string updt = "update tbl_Sub_MemCollection set MemberId='" + lblMmbrID_GM.Text + "',Memb_ID='" + lblMembID_GM2.Text + "',MemName='" + lblMmbrName_GM.Text + "',Date='" + date + "',Grade='" + txtSubsTyp_GM.Text + "',SubAmount='" + lblMmbrAmnt_GM.Text + "',AmountPaid='" + txtSubsAmnt_GM.Text + "',Balance='" + txtBalnce_GM.Text + "',ReceiptNo='" + txtReceipt_GM.Text + "',FinYearID='" + lblFinID.Text + "' where SubMCollID='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(updt, db.Connect());
db.cmd.ExecuteNonQuery();
string updtBS = "update tbl_BalanceSheet set BillDate='" + dtpCollectdON.Text + "' where FK='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(updtBS, db.Connect());
db.cmd.ExecuteNonQuery();
}
else
{
string str = "insert into tbl_Sub_MemCollection(MemberId,Memb_ID,MemName,Date,Grade,SubAmount,AmountPaid,Balance,ReceiptNo,FinYearID)values('" + lblMmbrID_GM.Text + "','" + lblMembID_GM2.Text + "','" + lblMmbrName_GM.Text + "','" + date + "','" + txtSubsTyp_GM.Text + "','" + txtSubsAmnt_GM.Text + "','" + lblMmbrAmnt_GM.Text + "','" + txtBalnce_GM.Text + "','" + txtReceipt_GM.Text + "','" + lblFinID.Text + "')";
db.cmd = new SqlCommand(str, db.Connect());
db.cmd.ExecuteNonQuery();
string id = "select MAX(SubMCollID) as ID from tbl_Sub_MemCollection";
db.cmd = new SqlCommand(id, db.Connect());
DataTable dtSubColID = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtSubColID);
string InsertBS = "insert into tbl_BalanceSheet(LedgerName,LedgerType,BillDate,Amount,StatusInCr,StatusOutDr,FK)values('Memb-Subs-March','Income','" + dtpCollectdON.Text + "','" + lblMmbrAmnt_GM.Text + "','" + lblMmbrAmnt_GM.Text + "','0','" + dtSubColID.Rows[0][0].ToString() + "')";
db.cmd = new SqlCommand(InsertBS, db.Connect());
db.cmd.ExecuteNonQuery();
}
}
if (chbMar2 .Checked == false)
{
date = "03/01/" + System.DateTime.Now.ToString("yyyy");
string getid = "select SubMCollID from tbl_Sub_MemCollection where Date='" + date + "' and MemberId='" + lblMmbrID_GM.Text + "'";
db.cmd = new SqlCommand(getid, db.Connect());
DataTable dtid = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtid);
if (dtid.Rows.Count > 0)
{
string str2 = "delete from tbl_Sub_MemCollection where SubMCollID='" + dtid.Rows[0][0].ToString() + "'and MemberId='" + lblMmbrID_GM.Text + "'";
db.cmd = new SqlCommand(str2, db.Connect());
db.cmd.ExecuteNonQuery();
string del = "delete from tbl_BalanceSheet where FK='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(del, db.Connect());
db.cmd.ExecuteNonQuery();
}
}
if (chbApril2 .Checked == true)
{
date = "04/01/" + System.DateTime.Now.ToString("yyyy");
string getid = "select SubMCollID from tbl_Sub_MemCollection where Date='" + date + "'";
db.cmd = new SqlCommand(getid, db.Connect());
DataTable dtid = new DataTable();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtid);
if (dtid.Rows.Count > 0)
{
string updt = "update tbl_Sub_MemCollection set MemberId='" + lblMmbrID_GM.Text + "',Memb_ID='" + lblMembID_GM2.Text + "',MemName='" + lblMmbrName_GM.Text + "',Date='" + date + "',Grade='" + txtSubsTyp_GM.Text + "',SubAmount='" + lblMmbrAmnt_GM.Text + "',AmountPaid='" + txtSubsAmnt_GM.Text + "',Balance='" + txtBalnce_GM.Text + "',ReceiptNo='" + txtReceipt_GM.Text + "',FinYearID='" + lblFinID.Text + "' where SubMCollID='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(updt, db.Connect());
db.cmd.ExecuteNonQuery();
string updtBS = "update tbl_BalanceSheet set BillDate='" + dtpCollectdON.Text + "' where FK='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(updtBS, db.Connect());
db.cmd.ExecuteNonQuery();
}
else
{
string str = "insert into tbl_Sub_MemCollection(MemberId,Memb_ID,MemName,Date,Grade,SubAmount,AmountPaid,Balance,ReceiptNo,FinYearID)values('" + lblMmbrID_GM.Text + "','" + lblMembID_GM2.Text + "','" + lblMmbrName_GM.Text + "','" + date + "','" + txtSubsTyp_GM.Text + "','" + txtSubsAmnt_GM.Text + "','" + lblMmbrAmnt_GM.Text + "','" + txtBalnce_GM.Text + "','" + txtReceipt_GM.Text + "','" + lblFinID.Text + "')";
db.cmd = new SqlCommand(str, db.Connect());
db.cmd.ExecuteNonQuery();
string id = "select MAX(SubMCollID) as ID from tbl_Sub_MemCollection";
db.cmd = new SqlCommand(id, db.Connect());
DataTable dtSubColID = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtSubColID);
string InsertBS = "insert into tbl_BalanceSheet(LedgerName,LedgerType,BillDate,Amount,StatusInCr,StatusOutDr,FK)values('Memb-Subs-April','Income','" + dtpCollectdON.Text + "','" + lblMmbrAmnt_GM.Text + "','" + lblMmbrAmnt_GM.Text + "','0','" + dtSubColID.Rows[0][0].ToString() + "')";
db.cmd = new SqlCommand(InsertBS, db.Connect());
db.cmd.ExecuteNonQuery();
}
}
if (chbApril2 .Checked == false)
{
date = "04/01/" + System.DateTime.Now.ToString("yyyy");
string getid = "select SubMCollID from tbl_Sub_MemCollection where Date='" + date + "' and MemberId='" + lblMmbrID_GM.Text + "'";
db.cmd = new SqlCommand(getid, db.Connect());
DataTable dtid = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtid);
if (dtid.Rows.Count > 0)
{
string str2 = "delete from tbl_Sub_MemCollection where SubMCollID='" + dtid.Rows[0][0].ToString() + "'and MemberId='" + lblMmbrID_GM.Text + "'";
db.cmd = new SqlCommand(str2, db.Connect());
db.cmd.ExecuteNonQuery();
string del = "delete from tbl_BalanceSheet where FK='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(del, db.Connect());
db.cmd.ExecuteNonQuery();
}
}
if (chbMay2 .Checked == true)
{
date = "05/01/" + System.DateTime.Now.ToString("yyyy");
string getid = "select SubMCollID from tbl_Sub_MemCollection where Date='" + date + "'";
db.cmd = new SqlCommand(getid, db.Connect());
DataTable dtid = new DataTable();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtid);
if (dtid.Rows.Count > 0)
{
string updt = "update tbl_Sub_MemCollection set MemberId='" + lblMmbrID_GM.Text + "',Memb_ID='" + lblMembID_GM2.Text + "',MemName='" + lblMmbrName_GM.Text + "',Date='" + date + "',Grade='" + txtSubsTyp_GM.Text + "',SubAmount='" + lblMmbrAmnt_GM.Text + "',AmountPaid='" + txtSubsAmnt_GM.Text + "',Balance='" + txtBalnce_GM.Text + "',ReceiptNo='" + txtReceipt_GM.Text + "',FinYearID='" + lblFinID.Text + "' where SubMCollID='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(updt, db.Connect());
db.cmd.ExecuteNonQuery();
string updtBS = "update tbl_BalanceSheet set BillDate='" + dtpCollectdON.Text + "' where FK='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(updtBS, db.Connect());
db.cmd.ExecuteNonQuery();
}
else
{
string str = "insert into tbl_Sub_MemCollection(MemberId,Memb_ID,MemName,Date,Grade,SubAmount,AmountPaid,Balance,ReceiptNo,FinYearID)values('" + lblMmbrID_GM.Text + "','" + lblMembID_GM2.Text + "','" + lblMmbrName_GM.Text + "','" + date + "','" + txtSubsTyp_GM.Text + "','" + txtSubsAmnt_GM.Text + "','" + lblMmbrAmnt_GM.Text + "','" + txtBalnce_GM.Text + "','" + txtReceipt_GM.Text + "','" + lblFinID.Text + "')";
db.cmd = new SqlCommand(str, db.Connect());
db.cmd.ExecuteNonQuery();
string id = "select MAX(SubMCollID) as ID from tbl_Sub_MemCollection";
db.cmd = new SqlCommand(id, db.Connect());
DataTable dtSubColID = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtSubColID);
string InsertBS = "insert into tbl_BalanceSheet(LedgerName,LedgerType,BillDate,Amount,StatusInCr,StatusOutDr,FK)values('Memb-Subs-May','Income','" + dtpCollectdON.Text + "','" + lblMmbrAmnt_GM.Text + "','" + lblMmbrAmnt_GM.Text + "','0','" + dtSubColID.Rows[0][0].ToString() + "')";
db.cmd = new SqlCommand(InsertBS, db.Connect());
db.cmd.ExecuteNonQuery();
}
}
if (chbMay2 .Checked == false)
{
date = "05/01/" + System.DateTime.Now.ToString("yyyy");
string getid = "select SubMCollID from tbl_Sub_MemCollection where Date='" + date + "' and MemberId='" + lblMmbrID_GM.Text + "'";
db.cmd = new SqlCommand(getid, db.Connect());
DataTable dtid = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtid);
if (dtid.Rows.Count > 0)
{
string str2 = "delete from tbl_Sub_MemCollection where SubMCollID='" + dtid.Rows[0][0].ToString() + "'and MemberId='" + lblMmbrID_GM.Text + "'";
db.cmd = new SqlCommand(str2, db.Connect());
db.cmd.ExecuteNonQuery();
string del = "delete from tbl_BalanceSheet where FK='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(del, db.Connect());
db.cmd.ExecuteNonQuery();
}
}
if (chbJune2 .Checked == true)
{
date = "06/01/" + System.DateTime.Now.ToString("yyyy");
string getid = "select SubMCollID from tbl_Sub_MemCollection where Date='" + date + "'";
db.cmd = new SqlCommand(getid, db.Connect());
DataTable dtid = new DataTable();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtid);
if (dtid.Rows.Count > 0)
{
string updt = "update tbl_Sub_MemCollection set MemberId='" + lblMmbrID_GM.Text + "',Memb_ID='" + lblMembID_GM2.Text + "',MemName='" + lblMmbrName_GM.Text + "',Date='" + date + "',Grade='" + txtSubsTyp_GM.Text + "',SubAmount='" + lblMmbrAmnt_GM.Text + "',AmountPaid='" + txtSubsAmnt_GM.Text + "',Balance='" + txtBalnce_GM.Text + "',ReceiptNo='" + txtReceipt_GM.Text + "',FinYearID='" + lblFinID.Text + "' where SubMCollID='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(updt, db.Connect());
db.cmd.ExecuteNonQuery();
string updtBS = "update tbl_BalanceSheet set BillDate='" + dtpCollectdON.Text + "' where FK='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(updtBS, db.Connect());
db.cmd.ExecuteNonQuery();
}
else
{
string str = "insert into tbl_Sub_MemCollection(MemberId,Memb_ID,MemName,Date,Grade,SubAmount,AmountPaid,Balance,ReceiptNo,FinYearID)values('" + lblMmbrID_GM.Text + "','" + lblMembID_GM2.Text + "','" + lblMmbrName_GM.Text + "','" + date + "','" + txtSubsTyp_GM.Text + "','" + txtSubsAmnt_GM.Text + "','" + lblMmbrAmnt_GM.Text + "','" + txtBalnce_GM.Text + "','" + txtReceipt_GM.Text + "','" + lblFinID.Text + "')";
db.cmd = new SqlCommand(str, db.Connect());
db.cmd.ExecuteNonQuery();
string id = "select MAX(SubMCollID) as ID from tbl_Sub_MemCollection";
db.cmd = new SqlCommand(id, db.Connect());
DataTable dtSubColID = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtSubColID);
string InsertBS = "insert into tbl_BalanceSheet(LedgerName,LedgerType,BillDate,Amount,StatusInCr,StatusOutDr,FK)values('Memb-Subs-June','Income','" + dtpCollectdON.Text + "','" + lblMmbrAmnt_GM.Text + "','" + lblMmbrAmnt_GM.Text + "','0','" + dtSubColID.Rows[0][0].ToString() + "')";
db.cmd = new SqlCommand(InsertBS, db.Connect());
db.cmd.ExecuteNonQuery();
}
}
if (chbJune2 .Checked == false)
{
date = "06/01/" + System.DateTime.Now.ToString("yyyy");
string getid = "select SubMCollID from tbl_Sub_MemCollection where Date='" + date + "' and MemberId='" + lblMmbrID_GM.Text + "'";
db.cmd = new SqlCommand(getid, db.Connect());
DataTable dtid = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtid);
if (dtid.Rows.Count > 0)
{
string str2 = "delete from tbl_Sub_MemCollection where SubMCollID='" + dtid.Rows[0][0].ToString() + "'and MemberId='" + lblMmbrID_GM.Text + "'";
db.cmd = new SqlCommand(str2, db.Connect());
db.cmd.ExecuteNonQuery();
string del = "delete from tbl_BalanceSheet where FK='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(del, db.Connect());
db.cmd.ExecuteNonQuery();
}
}
if (chbJuly2 .Checked == true)
{
date = "07/01/" + System.DateTime.Now.ToString("yyyy");
string getid = "select SubMCollID from tbl_Sub_MemCollection where Date='" + date + "'";
db.cmd = new SqlCommand(getid, db.Connect());
DataTable dtid = new DataTable();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtid);
if (dtid.Rows.Count > 0)
{
string updt = "update tbl_Sub_MemCollection set MemberId='" + lblMmbrID_GM.Text + "',Memb_ID='" + lblMembID_GM2.Text + "',MemName='" + lblMmbrName_GM.Text + "',Date='" + date + "',Grade='" + txtSubsTyp_GM.Text + "',SubAmount='" + lblMmbrAmnt_GM.Text + "',AmountPaid='" + txtSubsAmnt_GM.Text + "',Balance='" + txtBalnce_GM.Text + "',ReceiptNo='" + txtReceipt_GM.Text + "',FinYearID='" + lblFinID.Text + "' where SubMCollID='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(updt, db.Connect());
db.cmd.ExecuteNonQuery();
string updtBS = "update tbl_BalanceSheet set BillDate='" + dtpCollectdON.Text + "' where FK='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(updtBS, db.Connect());
db.cmd.ExecuteNonQuery();
}
else
{
string str = "insert into tbl_Sub_MemCollection(MemberId,Memb_ID,MemName,Date,Grade,SubAmount,AmountPaid,Balance,ReceiptNo,FinYearID)values('" + lblMmbrID_GM.Text + "','" + lblMembID_GM2.Text + "','" + lblMmbrName_GM.Text + "','" + date + "','" + txtSubsTyp_GM.Text + "','" + txtSubsAmnt_GM.Text + "','" + lblMmbrAmnt_GM.Text + "','" + txtBalnce_GM.Text + "','" + txtReceipt_GM.Text + "','" + lblFinID.Text + "')";
db.cmd = new SqlCommand(str, db.Connect());
db.cmd.ExecuteNonQuery();
string id = "select MAX(SubMCollID) as ID from tbl_Sub_MemCollection";
db.cmd = new SqlCommand(id, db.Connect());
DataTable dtSubColID = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtSubColID);
string InsertBS = "insert into tbl_BalanceSheet(LedgerName,LedgerType,BillDate,Amount,StatusInCr,StatusOutDr,FK)values('Memb-Subs-July','Income','" + dtpCollectdON.Text + "','" + lblMmbrAmnt_GM.Text + "','" + lblMmbrAmnt_GM.Text + "','0','" + dtSubColID.Rows[0][0].ToString() + "')";
db.cmd = new SqlCommand(InsertBS, db.Connect());
db.cmd.ExecuteNonQuery();
}
}
if (chbJuly2 .Checked == false)
{
date = "07/01/" + System.DateTime.Now.ToString("yyyy");
string getid = "select SubMCollID from tbl_Sub_MemCollection where Date='" + date + "' and MemberId='" + lblMmbrID_GM.Text + "'";
db.cmd = new SqlCommand(getid, db.Connect());
DataTable dtid = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtid);
if (dtid.Rows.Count > 0)
{
string str2 = "delete from tbl_Sub_MemCollection where SubMCollID='" + dtid.Rows[0][0].ToString() + "'and MemberId='" + lblMmbrID_GM.Text + "'";
db.cmd = new SqlCommand(str2, db.Connect());
db.cmd.ExecuteNonQuery();
string del = "delete from tbl_BalanceSheet where FK='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(del, db.Connect());
db.cmd.ExecuteNonQuery();
}
}
if (chbAug2 .Checked == true)
{
date = "08/01/" + System.DateTime.Now.ToString("yyyy");
string getid = "select SubMCollID from tbl_Sub_MemCollection where Date='" + date + "'";
db.cmd = new SqlCommand(getid, db.Connect());
DataTable dtid = new DataTable();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtid);
if (dtid.Rows.Count > 0)
{
string updt = "update tbl_Sub_MemCollection set MemberId='" + lblMmbrID_GM.Text + "',Memb_ID='" + lblMembID_GM2.Text + "',MemName='" + lblMmbrName_GM.Text + "',Date='" + date + "',Grade='" + txtSubsTyp_GM.Text + "',SubAmount='" + lblMmbrAmnt_GM.Text + "',AmountPaid='" + txtSubsAmnt_GM.Text + "',Balance='" + txtBalnce_GM.Text + "',ReceiptNo='" + txtReceipt_GM.Text + "',FinYearID='" + lblFinID.Text + "' where SubMCollID='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(updt, db.Connect());
db.cmd.ExecuteNonQuery();
string updtBS = "update tbl_BalanceSheet set BillDate='" + dtpCollectdON.Text + "' where FK='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(updtBS, db.Connect());
db.cmd.ExecuteNonQuery();
}
else
{
string str = "insert into tbl_Sub_MemCollection(MemberId,Memb_ID,MemName,Date,Grade,SubAmount,AmountPaid,Balance,ReceiptNo,FinYearID)values('" + lblMmbrID_GM.Text + "','" + lblMembID_GM2.Text + "','" + lblMmbrName_GM.Text + "','" + date + "','" + txtSubsTyp_GM.Text + "','" + txtSubsAmnt_GM.Text + "','" + lblMmbrAmnt_GM.Text + "','" + txtBalnce_GM.Text + "','" + txtReceipt_GM.Text + "','" + lblFinID.Text + "')";
db.cmd = new SqlCommand(str, db.Connect());
db.cmd.ExecuteNonQuery();
string id = "select MAX(SubMCollID) as ID from tbl_Sub_MemCollection";
db.cmd = new SqlCommand(id, db.Connect());
DataTable dtSubColID = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtSubColID);
string InsertBS = "insert into tbl_BalanceSheet(LedgerName,LedgerType,BillDate,Amount,StatusInCr,StatusOutDr,FK)values('Memb-Subs-August','Income','" + dtpCollectdON.Text + "','" + lblMmbrAmnt_GM.Text + "','" + lblMmbrAmnt_GM.Text + "','0','" + dtSubColID.Rows[0][0].ToString() + "')";
db.cmd = new SqlCommand(InsertBS, db.Connect());
db.cmd.ExecuteNonQuery();
}
}
if (chbAug2 .Checked == false)
{
date = "08/01/" + System.DateTime.Now.ToString("yyyy");
string getid = "select SubMCollID from tbl_Sub_MemCollection where Date='" + date + "' and MemberId='" + lblMmbrID_GM.Text + "'";
db.cmd = new SqlCommand(getid, db.Connect());
DataTable dtid = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtid);
if (dtid.Rows.Count > 0)
{
string str2 = "delete from tbl_Sub_MemCollection where SubMCollID='" + dtid.Rows[0][0].ToString() + "'and MemberId='" + lblMmbrID_GM.Text + "'";
db.cmd = new SqlCommand(str2, db.Connect());
db.cmd.ExecuteNonQuery();
string del = "delete from tbl_BalanceSheet where FK='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(del, db.Connect());
db.cmd.ExecuteNonQuery();
}
}
if (chbSep2 .Checked == true)
{
date = "09/01/" + System.DateTime.Now.ToString("yyyy");
string getid = "select SubMCollID from tbl_Sub_MemCollection where Date='" + date + "'";
db.cmd = new SqlCommand(getid, db.Connect());
DataTable dtid = new DataTable();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtid);
if (dtid.Rows.Count > 0)
{
string updt = "update tbl_Sub_MemCollection set MemberId='" + lblMmbrID_GM.Text + "',Memb_ID='" + lblMembID_GM2.Text + "',MemName='" + lblMmbrName_GM.Text + "',Date='" + date + "',Grade='" + txtSubsTyp_GM.Text + "',SubAmount='" + lblMmbrAmnt_GM.Text + "',AmountPaid='" + txtSubsAmnt_GM.Text + "',Balance='" + txtBalnce_GM.Text + "',ReceiptNo='" + txtReceipt_GM.Text + "',FinYearID='" + lblFinID.Text + "' where SubMCollID='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(updt, db.Connect());
db.cmd.ExecuteNonQuery();
string updtBS = "update tbl_BalanceSheet set BillDate='" + dtpCollectdON.Text + "' where FK='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(updtBS, db.Connect());
db.cmd.ExecuteNonQuery();
}
else
{
string str = "insert into tbl_Sub_MemCollection(MemberId,Memb_ID,MemName,Date,Grade,SubAmount,AmountPaid,Balance,ReceiptNo,FinYearID)values('" + lblMmbrID_GM.Text + "','" + lblMembID_GM2.Text + "','" + lblMmbrName_GM.Text + "','" + date + "','" + txtSubsTyp_GM.Text + "','" + txtSubsAmnt_GM.Text + "','" + lblMmbrAmnt_GM.Text + "','" + txtBalnce_GM.Text + "','" + txtReceipt_GM.Text + "','" + lblFinID.Text + "')";
db.cmd = new SqlCommand(str, db.Connect());
db.cmd.ExecuteNonQuery();
string id = "select MAX(SubMCollID) as ID from tbl_Sub_MemCollection";
db.cmd = new SqlCommand(id, db.Connect());
DataTable dtSubColID = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtSubColID);
string InsertBS = "insert into tbl_BalanceSheet(LedgerName,LedgerType,BillDate,Amount,StatusInCr,StatusOutDr,FK)values('Memb-Subs-September','Income','" + dtpCollectdON.Text + "','" + lblMmbrAmnt_GM.Text + "','" + lblMmbrAmnt_GM.Text + "','0','" + dtSubColID.Rows[0][0].ToString() + "')";
db.cmd = new SqlCommand(InsertBS, db.Connect());
db.cmd.ExecuteNonQuery();
}
}
if (chbSep2 .Checked == false)
{
date = "09/01/" + System.DateTime.Now.ToString("yyyy");
string getid = "select SubMCollID from tbl_Sub_MemCollection where Date='" + date + "' and MemberId='" + lblMmbrID_GM.Text + "'";
db.cmd = new SqlCommand(getid, db.Connect());
DataTable dtid = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtid);
if (dtid.Rows.Count > 0)
{
string str2 = "delete from tbl_Sub_MemCollection where SubMCollID='" + dtid.Rows[0][0].ToString() + "'and MemberId='" + lblMmbrID_GM.Text + "'";
db.cmd = new SqlCommand(str2, db.Connect());
db.cmd.ExecuteNonQuery();
string del = "delete from tbl_BalanceSheet where FK='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(del, db.Connect());
db.cmd.ExecuteNonQuery();
}
}
if (chbOct2 .Checked == true)
{
date = "10/01/" + System.DateTime.Now.ToString("yyyy");
string getid = "select SubMCollID from tbl_Sub_MemCollection where Date='" + date + "'";
db.cmd = new SqlCommand(getid, db.Connect());
DataTable dtid = new DataTable();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtid);
if (dtid.Rows.Count > 0)
{
string updt = "update tbl_Sub_MemCollection set MemberId='" + lblMmbrID_GM.Text + "',Memb_ID='" + lblMembID_GM2.Text + "',MemName='" + lblMmbrName_GM.Text + "',Date='" + date + "',Grade='" + txtSubsTyp_GM.Text + "',SubAmount='" + lblMmbrAmnt_GM.Text + "',AmountPaid='" + txtSubsAmnt_GM.Text + "',Balance='" + txtBalnce_GM.Text + "',ReceiptNo='" + txtReceipt_GM.Text + "',FinYearID='" + lblFinID.Text + "' where SubMCollID='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(updt, db.Connect());
db.cmd.ExecuteNonQuery();
string updtBS = "update tbl_BalanceSheet set BillDate='" + dtpCollectdON.Text + "' where FK='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(updtBS, db.Connect());
db.cmd.ExecuteNonQuery();
}
else
{
string str = "insert into tbl_Sub_MemCollection(MemberId,Memb_ID,MemName,Date,Grade,SubAmount,AmountPaid,Balance,ReceiptNo,FinYearID)values('" + lblMmbrID_GM.Text + "','" + lblMembID_GM2.Text + "','" + lblMmbrName_GM.Text + "','" + date + "','" + txtSubsTyp_GM.Text + "','" + txtSubsAmnt_GM.Text + "','" + lblMmbrAmnt_GM.Text + "','" + txtBalnce_GM.Text + "','" + txtReceipt_GM.Text + "','" + lblFinID.Text + "')";
db.cmd = new SqlCommand(str, db.Connect());
db.cmd.ExecuteNonQuery();
string id = "select MAX(SubMCollID) as ID from tbl_Sub_MemCollection";
db.cmd = new SqlCommand(id, db.Connect());
DataTable dtSubColID = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtSubColID);
string InsertBS = "insert into tbl_BalanceSheet(LedgerName,LedgerType,BillDate,Amount,StatusInCr,StatusOutDr,FK)values('Memb-Subs-October','Income','" + dtpCollectdON.Text + "','" + lblMmbrAmnt_GM.Text + "','" + lblMmbrAmnt_GM.Text + "','0','" + dtSubColID.Rows[0][0].ToString() + "')";
db.cmd = new SqlCommand(InsertBS, db.Connect());
db.cmd.ExecuteNonQuery();
}
}
if (chbOct2 .Checked == false)
{
date = "10/01/" + System.DateTime.Now.ToString("yyyy");
string getid = "select SubMCollID from tbl_Sub_MemCollection where Date='" + date + "' and MemberId='" + lblMmbrID_GM.Text + "'";
db.cmd = new SqlCommand(getid, db.Connect());
DataTable dtid = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtid);
if (dtid.Rows.Count > 0)
{
string str2 = "delete from tbl_Sub_MemCollection where SubMCollID='" + dtid.Rows[0][0].ToString() + "'and MemberId='" + lblMmbrID_GM.Text + "'";
db.cmd = new SqlCommand(str2, db.Connect());
db.cmd.ExecuteNonQuery();
string del = "delete from tbl_BalanceSheet where FK='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(del, db.Connect());
db.cmd.ExecuteNonQuery();
}
}
if (chbNov2 .Checked == true)
{
date = "11/01/" + System.DateTime.Now.ToString("yyyy");
string getid = "select SubMCollID from tbl_Sub_MemCollection where Date='" + date + "'";
db.cmd = new SqlCommand(getid, db.Connect());
DataTable dtid = new DataTable();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtid);
if (dtid.Rows.Count > 0)
{
string updt = "update tbl_Sub_MemCollection set MemberId='" + lblMmbrID_GM.Text + "',Memb_ID='" + lblMembID_GM2.Text + "',MemName='" + lblMmbrName_GM.Text + "',Date='" + date + "',Grade='" + txtSubsTyp_GM.Text + "',SubAmount='" + lblMmbrAmnt_GM.Text + "',AmountPaid='" + txtSubsAmnt_GM.Text + "',Balance='" + txtBalnce_GM.Text + "',ReceiptNo='" + txtReceipt_GM.Text + "',FinYearID='" + lblFinID.Text + "' where SubMCollID='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(updt, db.Connect());
db.cmd.ExecuteNonQuery();
string updtBS = "update tbl_BalanceSheet set BillDate='" + dtpCollectdON.Text + "' where FK='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(updtBS, db.Connect());
db.cmd.ExecuteNonQuery();
}
else
{
string str = "insert into tbl_Sub_MemCollection(MemberId,Memb_ID,MemName,Date,Grade,SubAmount,AmountPaid,Balance,ReceiptNo,FinYearID)values('" + lblMmbrID_GM.Text + "','" + lblMembID_GM2.Text + "','" + lblMmbrName_GM.Text + "','" + date + "','" + txtSubsTyp_GM.Text + "','" + txtSubsAmnt_GM.Text + "','" + lblMmbrAmnt_GM.Text + "','" + txtBalnce_GM.Text + "','" + txtReceipt_GM.Text + "','" + lblFinID.Text + "')";
db.cmd = new SqlCommand(str, db.Connect());
db.cmd.ExecuteNonQuery();
string id = "select MAX(SubMCollID) as ID from tbl_Sub_MemCollection";
db.cmd = new SqlCommand(id, db.Connect());
DataTable dtSubColID = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtSubColID);
string InsertBS = "insert into tbl_BalanceSheet(LedgerName,LedgerType,BillDate,Amount,StatusInCr,StatusOutDr,FK)values('Memb-Subs-November','Income','" + dtpCollectdON.Text + "','" + lblMmbrAmnt_GM.Text + "','" + lblMmbrAmnt_GM.Text + "','0','" + dtSubColID.Rows[0][0].ToString() + "')";
db.cmd = new SqlCommand(InsertBS, db.Connect());
db.cmd.ExecuteNonQuery();
}
}
if (chbNov2 .Checked == false)
{
date = "11/01/" + System.DateTime.Now.ToString("yyyy");
string getid = "select SubMCollID from tbl_Sub_MemCollection where Date='" + date + "' and MemberId='" + lblMmbrID_GM.Text + "'";
db.cmd = new SqlCommand(getid, db.Connect());
DataTable dtid = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtid);
if (dtid.Rows.Count > 0)
{
string str2 = "delete from tbl_Sub_MemCollection where SubMCollID='" + dtid.Rows[0][0].ToString() + "'and MemberId='" + lblMmbrID_GM.Text + "'";
db.cmd = new SqlCommand(str2, db.Connect());
db.cmd.ExecuteNonQuery();
string del = "delete from tbl_BalanceSheet where FK='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(del, db.Connect());
db.cmd.ExecuteNonQuery();
}
}
if (chbDec2 .Checked == true)
{
date = "12/01/" + System.DateTime.Now.ToString("yyyy");
string getid = "select SubMCollID from tbl_Sub_MemCollection where Date='" + date + "'";
db.cmd = new SqlCommand(getid, db.Connect());
DataTable dtid = new DataTable();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtid);
if (dtid.Rows.Count > 0)
{
string updt = "update tbl_Sub_MemCollection set MemberId='" + lblMmbrID_GM.Text + "',Memb_ID='" + lblMembID_GM2.Text + "',MemName='" + lblMmbrName_GM.Text + "',Date='" + date + "',Grade='" + txtSubsTyp_GM.Text + "',SubAmount='" + lblMmbrAmnt_GM.Text + "',AmountPaid='" + txtSubsAmnt_GM.Text + "',Balance='" + txtBalnce_GM.Text + "',ReceiptNo='" + txtReceipt_GM.Text + "',FinYearID='" + lblFinID.Text + "' where SubMCollID='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(updt, db.Connect());
db.cmd.ExecuteNonQuery();
string updtBS = "update tbl_BalanceSheet set BillDate='" + dtpCollectdON.Text + "' where FK='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(updtBS, db.Connect());
db.cmd.ExecuteNonQuery();
}
else
{
string str = "insert into tbl_Sub_MemCollection(MemberId,Memb_ID,MemName,Date,Grade,SubAmount,AmountPaid,Balance,ReceiptNo,FinYearID)values('" + lblMmbrID_GM.Text + "','" + lblMembID_GM2.Text + "','" + lblMmbrName_GM.Text + "','" + date + "','" + txtSubsTyp_GM.Text + "','" + txtSubsAmnt_GM.Text + "','" + lblMmbrAmnt_GM.Text + "','" + txtBalnce_GM.Text + "','" + txtReceipt_GM.Text + "','" + lblFinID.Text + "')";
db.cmd = new SqlCommand(str, db.Connect());
db.cmd.ExecuteNonQuery();
string id = "select MAX(SubMCollID) as ID from tbl_Sub_MemCollection";
db.cmd = new SqlCommand(id, db.Connect());
DataTable dtSubColID = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtSubColID);
string InsertBS = "insert into tbl_BalanceSheet(LedgerName,LedgerType,BillDate,Amount,StatusInCr,StatusOutDr,FK)values('Memb-Subs-December','Income','" + dtpCollectdON.Text + "','" + lblMmbrAmnt_GM.Text + "','" + lblMmbrAmnt_GM.Text + "','0','" + dtSubColID.Rows[0][0].ToString() + "')";
db.cmd = new SqlCommand(InsertBS, db.Connect());
db.cmd.ExecuteNonQuery();
}
}
if (chbDec2 .Checked == false)
{
date = "12/01/" + System.DateTime.Now.ToString("yyyy");
string getid = "select SubMCollID from tbl_Sub_MemCollection where Date='" + date + "' and MemberId='" + lblMmbrID_GM.Text + "'";
db.cmd = new SqlCommand(getid, db.Connect());
DataTable dtid = new DataTable();
dtid.Clear();
db.adapter = new SqlDataAdapter(db.cmd);
db.adapter.Fill(dtid);
if (dtid.Rows.Count > 0)
{
string str2 = "delete from tbl_Sub_MemCollection where SubMCollID='" + dtid.Rows[0][0].ToString() + "'and MemberId='" + lblMmbrID_GM.Text + "'";
db.cmd = new SqlCommand(str2, db.Connect());
db.cmd.ExecuteNonQuery();
string del = "delete from tbl_BalanceSheet where FK='" + dtid.Rows[0][0].ToString() + "'";
db.cmd = new SqlCommand(del, db.Connect());
db.cmd.ExecuteNonQuery();
}
}
MessageBox.Show("Saved","Subscription");
}
}
private void btnPrint_GM_Click(object sender, EventArgs e)
{
ReceiptNo = txtRecieptNo.Text;
maharegno = lblMahalluRegNo.Text;
housname = lblHouseNam.Text;
grdtype = txtSubsType.Text;
amntpaid = txtTotAmntCollctd.Text;
balnce = txtBalnceGrp.Text;
date = dtpColectdOn.Text;
frmSubscripPrint a = new frmSubscripPrint();
a.Show();
}
private void gbSubCollec_Enter(object sender, EventArgs e)
{
}
private void groupBox2_Enter(object sender, EventArgs e)
{
}
}
}
Next Recommended Forum