public List<Membership> GetFee(Membership mem,bool donor,bool member)
{
try
{
// List<object> obj = new List<object>();
List<Membership> members = new List<Membership>();
string select="";
if(member==true)
select = "SELECT MonthlyFee from MembershipData where MemberName='" + mem.MemberName + "'";
else if (donor==true)
select = "SELECT DonorFee from Donor where DonorName='" + mem.Donor.DonorName + "'";
con = new OleDbConnection();
this.readconfile = new ReadConfigFile();
con.ConnectionString = this.readconfile.ConfigString(ConfigFiles.ProjectConfigFile);
con.Open();
tran = con.BeginTransaction();
cmd = new OleDbCommand(select, con);
cmd.Transaction = tran;
dr = cmd.ExecuteReader();
while (dr.Read())
{
Membership m = new Membership();
Donor d = new Donor();
if(member==true)
m.MonthlyFee = dr["MonthlyFee"] == System.DBNull.Value ? 0 : Convert.ToDecimal(dr["MonthlyFee"]);
else
m.Donor.DonorFee = dr["DonorFee"] == System.DBNull.Value ? 0 : Convert.ToDecimal(dr["DonorFee"]);
members.Add(m);
}
dr.Close();
tran.Commit();
return members;
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}
public bool SaveDonorData(Donor d)
{
try
{
string insert;
if (d.MID == 0)
insert = "Insert Into Donor(DonorName,DonorLastName,Age,Gender,NIC"
+ ",Refrence,Phone,Email,Bloodgroup,DonorFee,CollectionDate,Adress,BranchName,DonorNo,Status,FundType,City,CurrentDate) "
+ "values ('" + d.DonorName + "','" + d.DonorLastName + "'," + d.AGE + ",'" + d.Gender + "','" + d.NIC + "','"
+ d.Refrence + "'," + d.Phone + ",'" + d.Email + "','" + d.BloodGroup + "','"
+ d.DonorFee + "',#" + d.CollectionDate + "#,'" + d.Adress + "','" + d.branch.BranchName + "',"
+ "" + d.DonorNo + ",'" + d.Status + "','" + d.FundType + "','"+d.City.CityName+"',#"+d.CurrentDate+"# )";
else
insert = "Update Donor set DonorName='" + d.DonorName + "',DonorLastName='" + d.DonorLastName + "',Age=" + d.AGE + ""
+ ", Gender= '" + d.Gender + "',NIC= '" + d.NIC + "',Refrence='" + d.Refrence + "',Phone='" + d.Phone + "',Email='" + d.Email + "',"
+ " Bloodgroup= '" + d.BloodGroup + "',DonorFee='" + d.DonorFee + "' ,CollectionDate= #" + d.CollectionDate + "#,Adress= '" + d.Adress + "',BranchName= '" + d.branch.BranchName + "',"
+ " DonorNo=" + d.DonorNo + " ,Status='" + d.Status + "' ,FundType= '" + d.FundType + "',City= '" + d.City.CityName + "',CurrentDate=#" + d.CurrentDate + "# where MID=" + d.MID + "";
con = new OleDbConnection();
this.readconfile = new ReadConfigFile();
con.ConnectionString = this.readconfile.ConfigString(ConfigFiles.ProjectConfigFile);
con.Open();
tran = con.BeginTransaction();
cmd = new OleDbCommand(insert, con);
cmd.Transaction = tran;
cmd.ExecuteNonQuery();
tran.Commit();
return true;
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}
public List <Membership> GetMembersAndDonors()
{
try
{
members = new List <Membership>();
string Select = "SELECT Mid,MemberName from MembershipData Union All select Mid,DonorName from Donor";
con = new OleDbConnection();
this.readconfile = new ReadConfigFile();
con.ConnectionString = this.readconfile.ConfigString(ConfigFiles.ProjectConfigFile);
con.Open();
tran = con.BeginTransaction();
cmd = new OleDbCommand(Select, con);
cmd.Transaction = tran;
dr = cmd.ExecuteReader();
while (dr.Read())
{
Membership m = new Membership();
Donor d = new Donor();
m.MemberName = dr["MemberName"] == System.DBNull.Value ? null : Convert.ToString(dr["MemberName"]);
m.MembershipNo = dr["Mid"] == System.DBNull.Value ? 0 : Convert.ToInt32(dr["Mid"]);
members.Add(m);
}
dr.Close();
tran.Commit();
return(members);
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}
public List <Membership> GetMembersCollection(MemberCollection mem)
{
try
{
members = new List <Membership>();
string Select = "SELECT MonthlyFee from MembershipData where MemberName='" + mem.MemberName + "'";
con = new OleDbConnection();
this.readconfile = new ReadConfigFile();
con.ConnectionString = this.readconfile.ConfigString(ConfigFiles.ProjectConfigFile);
con.Open();
tran = con.BeginTransaction();
cmd = new OleDbCommand(Select, con);
cmd.Transaction = tran;
dr = cmd.ExecuteReader();
while (dr.Read())
{
Membership m = new Membership();
Donor d = new Donor();
m.MonthlyFee = dr["MonthlyFee"] == System.DBNull.Value ? 0 : Convert.ToDecimal(dr["MonthlyFee"]);
members.Add(m);
}
dr.Close();
tran.Commit();
return(members);
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}
public DSRefrenceCollection GetRefrenceCollection(bool rbDonor, bool rbMember, Donor d, Membership m, string fromdate, string todate)
{
DSRefrenceCollection ds = new DSRefrenceCollection();
try
{
//string MyQry = "SELECT MemberCollection.MemberName as MemberName, MemberCollection.CollectionFee as CollectionFee from MemberCollection"+
// " left join MembershipData on MemberCollection.MemberName=MembershipData.Refrence where MemberCollection.MemberName='"+m.MemberName+"'"+
// " UNION ALL"+
// " SELECT DonorCollection.DonorName as MemberName, DonorCollection.CollectionFee as CollectionFee from DonorCollection"+
// " left join Donor on DonorCollection.DonorName=Donor.Refrence where DonorCollection.DonorName='"+d.DonorName+"'";
string MyDonorQry = " Select mc.MemberName,mc.CollectionFee from MemberCollection mc where mc.MemberName='" + d.DonorName + "'" +
" or mc.MemberName in(select MembershipData.MemberName from MembershipData where" +
" MembershipData.Refrence='" + d.DonorName + "')" + //and CollectionDate>=#"+fromdate+"# and CollectionDate<=#"+todate +"#" +
" union all" +
" select dc.DonorName,dc.CollectionFee from DonorCollectionCollection dc where dc.DonorName ='" + d.DonorName + "'" +
" or dc.DonorName in(select Donor.Donorname from Donor where " +
" Donor.Refrence='" + d.DonorName + "') "; //and CollectionDate>=#"+fromdate+"# and CollectionDate<=#"+todate +"#";
String MemberQry = " Select mc.MemberName,mc.CollectionFee from MemberCollection mc where mc.MemberName='" + m.MemberName + "'" +
" or mc.MemberName in(select MembershipData.MemberName from MembershipData where" +
" MembershipData.Refrence='" + m.MemberName + "')" + // and CollectionDate>=#" + fromdate + "# and CollectionDate<=#" + todate + "#" +
" union all" +
" select dc.DonorName,dc.CollectionFee from DonorCollection dc where dc.DonorName ='" + m.MemberName + "'" +
" or dc.DonorName in(select Donor.Donorname from Donor where " +
" Donor.Refrence='" + m.MemberName + "')"; // and CollectionDate>=#"+fromdate+"# and CollectionDate<=#"+todate +"#";
con = new OleDbConnection();
this.readconfile = new ReadConfigFile();
con.ConnectionString = this.readconfile.ConfigString(ConfigFiles.ProjectConfigFile);
con.Open();
if (con.State == ConnectionState.Open)
{
string select = "";
if (rbDonor == true)
{
select = MyDonorQry;
}
else
{
select = MemberQry;
}
da = new OleDbDataAdapter(select, con);
da.Fill(ds, ds.Tables[0].TableName);
}
return(ds);
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}
public DSDonorPlusMember GetDonorPlusMemberData(Donor d, bool Branches, bool Cities, bool All, bool Donationtype, string fromdate, string todate)
{
DSDonorPlusMember ds = new DSDonorPlusMember();
try
{
string branches = "Select MemberName,MembershipNo,CollectionDate,MemberShipFee,MonthlyFee,BranchName,Refrence,Bloodgroup,'' as FundType,'' as City" +
" from MembershipData Where BranchName='" + d.branch.BranchName + "'" + // and CollectionDate>=#" + fromdate + "# and CollectionDate<=#" + todate + "#"
" union all select DonorName,DonorNo,CollectionDate,DonorFee, null as MonthlyFee,BranchName,'' as Refrence,'' as Bloodgroup, FundType,City" +
" from Donor Where BranchName='" + d.branch.BranchName + "'"; //and CollectionDate>=#" + fromdate + "# and CollectionDate<=#" + todate + "#"
string all = "Select MemberName,MembershipNo,CollectionDate,MemberShipFee,MonthlyFee,BranchName,Refrence,Bloodgroup,'' as FundType,City" +
" from MembershipData " + //and CollectionDate>=#" + fromdate + "# and CollectionDate<=#" + todate + "#"
" union all select DonorName,DonorNo,CollectionDate,DonorFee, null as MonthlyFee,BranchName,'' as Refrence,'' as Bloodgroup, FundType,City" +
" from Donor "; //and CollectionDate>=#" + fromdate + "# and CollectionDate<=#" + todate + "#"
string cities = "Select MemberName,MembershipNo,CollectionDate,MemberShipFee,MonthlyFee,BranchName,Refrence,Bloodgroup,'' as FundType,City " +
" from MembershipData Where City='" + d.City.CityName + "'" +
" union all select DonorName,DonorNo,CollectionDate,DonorFee, null as MonthlyFee,BranchName,'' as Refrence,'' as Bloodgroup, FundType,City " +
" from Donor Where City='" + d.City.CityName + "'";//and CollectionDate>=#" + fromdate + "# and CollectionDate<=#" + todate + "#"
string donationtype = "Select '' as MemberName,null as MembershipNo,null as CollectionDate, null as MemberShipFee,'' as BranchName,'' as FundType,'' as City" +
" from MembershipData " +
" union all select DonorName,DonorNo,CollectionDate,DonorFee,BranchName, FundType,City" +
" from Donor Where FundType='" + d.FundType + "'";//and CollectionDate>=#" + fromdate + "# and CollectionDate<=#" + todate + "#"
con = new OleDbConnection();
this.readconfile = new ReadConfigFile();
con.ConnectionString = this.readconfile.ConfigString(ConfigFiles.ProjectConfigFile);
con.Open();
if (con.State == ConnectionState.Open)
{
string select = "";
if (Branches == true)
{
select = branches;
}
else if (Cities == true)
{
select = cities;
}
else if (All == true)
{
select = all;
}
else if (Donationtype == true)
{
select = donationtype;
}
da = new OleDbDataAdapter(select, con);
da.Fill(ds, ds.Tables[0].TableName);
}
return(ds);
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}
public DSDonor GetData(Donor d, bool Branches, bool Cities, bool All, bool Donationtype, string fromdate, string todate, bool Datewise)
{
DSDonor ds = new DSDonor();
try
{
string branches = "Select * from Donor Where BranchName='" + d.branch.BranchName + "'";
string all = "Select * from Donor";
string cities = "Select * from Donor Where City='" + d.City.CityName + "'";
string donationtype = " Select * from Donor Where FundType='" + d.FundType + "'";
string datewise = " SELECT * from donor where CurrentDate>=#" + fromdate + "# and CurrentDate<=#" + todate + "#";
con = new OleDbConnection();
this.readconfile = new ReadConfigFile();
con.ConnectionString = this.readconfile.ConfigString(ConfigFiles.ProjectConfigFile);
con.Open();
if (con.State == ConnectionState.Open)
{
string select = "";
if (Branches == true)
{
select = branches;
}
else if (Cities == true)
{
select = cities;
}
else if (All == true)
{
select = all;
}
else if (Donationtype == true)
{
select = donationtype;
}
else if (Datewise == true)
{
select = datewise;
}
da = new OleDbDataAdapter(select, con);
da.Fill(ds, ds.Tables[0].TableName);
}
return(ds);
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}
public DSRefrenceCollection GetRefrenceCollection(bool rbDonor, bool rbMember, Donor d, Membership m, string fromdate, string todate)
{
DSRefrenceCollection ds = new DSRefrenceCollection();
try
{
string MyDonorQry = " Select mc.MemberName,'' as DonorName,mc.CollectionFee from MemberCollection mc where mc.MemberName='" + d.DonorName + "'" +
" or mc.MemberName in(select MembershipData.MemberName from MembershipData where" +
" MembershipData.Refrence='" + d.DonorName + "'" + " and CollectionDate>=#" + fromdate + "# and CollectionDate<=#" + todate + "#)" +
" union all" +
" select '' as MemberName,dc.DonorName as DonorName,dc.CollectionFee from DonorCollection dc where dc.DonorName ='" + d.DonorName + "'" +
" or dc.DonorName in(select Donor.Donorname from Donor where" +
" Donor.Refrence='" + d.DonorName + "' and CollectionDate>=#" + fromdate + "# and CollectionDate<=#" + todate + "#)" +
" union all" +
" Select dc.DonorName as MemberName,'' as DonorName,dc.CollectionFee from DonorCollection dc where" +
" dc.DonorName in(select MembershipData.MemberName from MembershipData where " +
" MembershipData.Refrence='" + d.DonorName + "' and CollectionDate>=#" + fromdate + "# and CollectionDate<=#" + todate + "#)";
String MemberQry = " Select mc.MemberName,'' as DonorName,mc.CollectionFee from MemberCollection mc where mc.MemberName='" +m.MemberName+ "'" +
" or mc.MemberName in(select MembershipData.MemberName from MembershipData where" +
" MembershipData.Refrence='" + m.MemberName+ "'" + " and CollectionDate>=#" + fromdate + "# and CollectionDate<=#" + todate + "#)" +
" union all" +
" select '' as MemberName,dc.DonorName as DonorName,dc.CollectionFee from DonorCollection dc where dc.DonorName ='" + d.DonorName + "'" +
" or dc.DonorName in(select Donor.Donorname from Donor where" +
" Donor.Refrence='" +m.MemberName + "' and CollectionDate>=#" + fromdate + "# and CollectionDate<=#" + todate + "#)" +
" union all" +
" Select dc.DonorName as MemberName,'' as DonorName,dc.CollectionFee from DonorCollection dc where" +
" dc.DonorName in(select MembershipData.MemberName from MembershipData where " +
" MembershipData.Refrence='" + m.MemberName + "' and CollectionDate>=#" + fromdate + "# and CollectionDate<=#" + todate + "#)";
con = new OleDbConnection();
this.readconfile = new ReadConfigFile();
con.ConnectionString = this.readconfile.ConfigString(ConfigFiles.ProjectConfigFile);
con.Open();
if (con.State == ConnectionState.Open)
{
string select = "";
if (rbDonor == true)
select = MyDonorQry;
else
select = MemberQry;
da = new OleDbDataAdapter(select, con);
da.Fill(ds, ds.Tables[0].TableName);
}
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}
public DSDonorAndMemberCollection GetDonorAndMemberCollection(Donor d, bool All, bool Donationtype, string fromdate, string todate)
{
DSDonorAndMemberCollection ds = new DSDonorAndMemberCollection();
try
{
string all = "Select DonorName, '' as MemberName,CollectionDate,CollectionFee,ReciptNo,DonationType,Other,CheckDetail"+
" from DonorCollection "+
" union all Select '' as DonorName, MemberName,CollectionDate,CollectionFee,ReciptNo,'' as DonationType, "+
" null as Other, null as CheckDetail from MemberCollection order by ReciptNo desc";
//where CollectionDate>=#" + fromdate +
//" # and CollectionDate<=#" + todate + "#" + " order by ReciptNo desc";
string donationtype = "Select DonorName,CollectionDate,CollectionFee,ReciptNo,DonationType,CheckDetail" +
" from DonorCollection Where DonationType='" + d.FundType + "'" + " and CollectionDate>=#" + fromdate + "# and CollectionDate<=#" + todate + "#";
string Other = "Select DonorName,CollectionDate,CollectionFee,ReciptNo,DonationType,Other,CheckDetail" +
" from DonorCollection Where Len(Other)>0 and CollectionDate>=#" + fromdate + "# and CollectionDate<=#" + todate + "#";
con = new OleDbConnection();
this.readconfile = new ReadConfigFile();
con.ConnectionString = this.readconfile.ConfigString(ConfigFiles.ProjectConfigFile);
con.Open();
if (con.State == ConnectionState.Open)
{
string select = "";
if (All == true)
{
select = all;
}
else if (Donationtype == true && d.FundType == "Others")
{
select = Other;
}
else if (Donationtype == true && d.FundType != "Others")
{
select = donationtype;
}
da = new OleDbDataAdapter(select, con);
da.Fill(ds, ds.Tables[0].TableName);
}
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}
