public BindingSource GetExpensesInfoByYear(int CYear)
{
SQLConn sQLConn = new SQLConn();
string query = @"select * from ExpensesInfo where Year(@CYear)";
SqlCommand sqlCommand = new SqlCommand(query, sQLConn.GetConnToLocalDb());
SqlParameter sqlParameter = new SqlParameter
{
ParameterName = "CYear",
SqlDbType = System.Data.SqlDbType.Int,
Value = CYear
};
sqlCommand.Parameters.Add(sqlParameter);
DataTable dataTable = new DataTable();
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter
{
SelectCommand = sqlCommand
};
sqlDataAdapter.Fill(dataTable);
BindingSource bindingSource = new BindingSource
{
DataSource = dataTable
};
sqlDataAdapter.Update(dataTable);
return(bindingSource);
}
public ExpensesInfo GetExpensesInfo(int CId)
{
SQLConn sQLConn = new SQLConn();
string query = @"select * from ExpensesInfo
where ID= @CId";
SqlCommand sqlCommand = new SqlCommand(query, sQLConn.GetConnToLocalDb());
SqlParameter sqlParameter = new SqlParameter();
sqlParameter = new SqlParameter
{
ParameterName = "CId",
SqlDbType = System.Data.SqlDbType.Int,
Value = CId
};
ExpensesInfo expensesInfo = new ExpensesInfo();
sqlCommand.Parameters.Add(sqlParameter);
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
if (sqlDataReader.Read())
{
expensesInfo.ID = Int32.Parse(sqlDataReader[0].ToString());
expensesInfo.EAmount = Int32.Parse(sqlDataReader[1].ToString());
expensesInfo.EDescription = sqlDataReader[2].ToString();
expensesInfo.EDate = DateTime.Parse(sqlDataReader[3].ToString());
}
return(expensesInfo);
}
public void DeleteNetSellingInfo(string CCPhone, string CSEngineNo)
{
SQLConn sQLConn = new SQLConn();
string query = @"delete from NetSellingInfo
where CPhone = @CCPhone
and SEngineNo = @CSEngineNo";
SqlCommand sqlCommand = new SqlCommand(query, sQLConn.GetConnToLocalDb());
SqlParameter sqlParameter = new SqlParameter();
sqlParameter = new SqlParameter
{
ParameterName = "CSEngineNo",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = CSEngineNo
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "CCPhone",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = CCPhone
};
sqlCommand.Parameters.Add(sqlParameter);
sqlCommand.ExecuteNonQuery();
}
public void DeleteLuckyMemberInfo(string COLPhone, string CSEngineNo)
{
SQLConn sQLConn = new SQLConn();
string query = @"Delete from installmentinfo
where OLPhone = @COLPhone and SEngineNo = @CSEngineNo";
SqlCommand sqlCommand = new SqlCommand(query, sQLConn.GetConnToLocalDb());
SqlParameter sqlParameter = new SqlParameter();
sqlParameter = new SqlParameter
{
ParameterName = "CSEngineNo",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = CSEngineNo
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "CMPhoe",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = COLPhone
};
sqlCommand.Parameters.Add(sqlParameter);
sqlCommand.ExecuteNonQuery();
}
public BindingSource GetMCommitteeMonthWise(string CMPhone, string CMName, string CMonth, string CYear)
{
SQLConn sQLConn = new SQLConn();
string query = @"select MPhone , MName , CAmount , CDate from committeeinfo
where MPhone = @CMPhone and MName = @CMName and Month(CDate) = @CMonth and Year(CDate) = @CYear";
SqlCommand sqlCommand = new SqlCommand(query, sQLConn.GetConnToLocalDb());
SqlParameter sqlParameter = new SqlParameter();
sqlParameter = new SqlParameter
{
ParameterName = "CMonth",
SqlDbType = System.Data.SqlDbType.Int,
Value = Int32.Parse(CMonth)
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "CYear",
SqlDbType = System.Data.SqlDbType.Int,
Value = Int32.Parse(CYear)
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "CMPhone",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = CMPhone
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "CMName",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = CMName
};
sqlCommand.Parameters.Add(sqlParameter);
DataTable dataTable = new DataTable();
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter
{
SelectCommand = sqlCommand
};
sqlDataAdapter.Fill(dataTable);
BindingSource bindingSource = new BindingSource
{
DataSource = dataTable
};
sqlDataAdapter.Update(dataTable);
return(bindingSource);
}
public void UpdateExpensesInfo(ExpensesInfo expensesInfo, int CId)
{
SQLConn sQLConn = new SQLConn();
string query = @"Update ExpensesInfo set Amount = @Amount , EDescription = @EDescription ,
EDate = @EDate , DDate = @DDate
where ID= @CId";
SqlCommand sqlCommand = new SqlCommand(query, sQLConn.GetConnToLocalDb());
SqlParameter sqlParameter = new SqlParameter
{
ParameterName = "Amount",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = expensesInfo.EAmount
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "EDescription",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = expensesInfo.EDescription
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "EDate",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = expensesInfo.EDate
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "DDate",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = expensesInfo.DDate
};
sqlCommand.Parameters.Add(sqlParameter);
sqlCommand.ExecuteNonQuery();
sqlParameter = new SqlParameter
{
ParameterName = "CId",
SqlDbType = System.Data.SqlDbType.Int,
Value = CId
};
sqlCommand.Parameters.Add(sqlParameter);
sqlCommand.ExecuteNonQuery();
}
public void InsertCommitteeInfo(CommitteeInfo committeeInfo)
{
SQLConn sQLConn = new SQLConn();
string query = @"insert into CommitteeInfo(MPhone , MName , CAmount , CDate , DDate) values(@MPhone , @MName , @CAmount , @CDate , @DDate)";
SqlCommand sqlCommand = new SqlCommand(query, sQLConn.GetConnToLocalDb());
SqlParameter sqlParameter = new SqlParameter
{
ParameterName = "MPhone",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = committeeInfo.MPhone
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "MName",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = committeeInfo.MName
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "CAmount",
SqlDbType = System.Data.SqlDbType.Int,
Value = committeeInfo.CAmount
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "CDate",
SqlDbType = System.Data.SqlDbType.Date,
Value = committeeInfo.CDate
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "DDate",
SqlDbType = System.Data.SqlDbType.Date,
Value = committeeInfo.DDate
};
sqlCommand.Parameters.Add(sqlParameter);
sqlCommand.ExecuteNonQuery();
}
public void InsertMemberLeaseInfo(MemberLeasesInfo memberLeasesInfo)
{
SQLConn sQLConn = new SQLConn();
string query = @"insert into MemberlesesInfo(MPhone , MName , SEngineNo , LAdvance ,LDate )
values(@MPhone , @MName , @SEngineNo , @LAdvance , @LDate)";
SqlCommand sqlCommand = new SqlCommand(query, sQLConn.GetConnToLocalDb());
SqlParameter sqlParameter = new SqlParameter
{
ParameterName = "MPhone",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = memberLeasesInfo.MPhone
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "MName",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = memberLeasesInfo.MName
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "SEngineNo",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = memberLeasesInfo.SEngineNo
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "LAdvance",
SqlDbType = System.Data.SqlDbType.Int,
Value = memberLeasesInfo.MLAdvance
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "LDate",
SqlDbType = System.Data.SqlDbType.Date,
Value = memberLeasesInfo.MLDate
};
sqlCommand.Parameters.Add(sqlParameter);
sqlCommand.ExecuteNonQuery();
}
public void InsertInstallmentInfo(InstallmentInfo installmentInfo)
{
SQLConn sQLConn = new SQLConn();
string query = @"insert into InstallmentInfo(@OLPhone , @SEnglineNo , @IAmount , @DDate , @IDate)";
SqlCommand sqlCommand = new SqlCommand(query, sQLConn.GetConnToLocalDb());
SqlParameter sqlParameter = new SqlParameter
{
ParameterName = "OLPhone",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = installmentInfo.OLPhone
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "SEngineNo",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = installmentInfo.SEngineNo
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "IAmount",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = installmentInfo.IAmount
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "DDate",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = installmentInfo.DDate
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "IDate",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = installmentInfo.IDate
};
sqlCommand.Parameters.Add(sqlParameter);
sqlCommand.ExecuteNonQuery();
}
public CommitteeInfo GetMCommitteeDateWise(string CMPHone, string CMName, DateTime CCDate)
{
SQLConn sQLConn = new SQLConn();
string query = @"select * from CommitteeInfo
where ddate = @ccdate and MPhone = @CMPhone and MName = @CMName ";
SqlCommand sqlCommand = new SqlCommand(query, sQLConn.GetConnToLocalDb());
SqlParameter sqlParameter = new SqlParameter
{
ParameterName = "CMPhone",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = CMPHone
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "CMName",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = CMName
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "CCDate",
SqlDbType = System.Data.SqlDbType.Date,
Value = CCDate
};
sqlCommand.Parameters.Add(sqlParameter);
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
if (sqlDataReader.Read())
{
CommitteeInfo committeeInfo = new CommitteeInfo
{
MPhone = sqlDataReader[0].ToString(),
MName = sqlDataReader[1].ToString(),
CAmount = Int32.Parse(sqlDataReader[2].ToString()),
DDate = DateTime.Parse(sqlDataReader[3].ToString()),
CDate = DateTime.Parse(sqlDataReader[4].ToString())
};
return(committeeInfo);
}
return(null);
}
public bool CheckExistence(CommitteeInfo committeeInfo)
{
SQLConn sQLConn = new SQLConn();
string query = @"select * from CommitteeInfo
where MPhone = @CMPhone and MName = @CMName and CDate = @CCDate and CAmount = @CAmount";
SqlCommand sqlCommand = new SqlCommand(query, sQLConn.GetConnToLocalDb());
SqlParameter sqlParameter = new SqlParameter
{
ParameterName = "CMPhone",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = committeeInfo.MPhone
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "CMName",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = committeeInfo.MName
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "CCDate",
SqlDbType = System.Data.SqlDbType.Date,
Value = committeeInfo.CDate
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "CAmount",
SqlDbType = System.Data.SqlDbType.Int,
Value = committeeInfo.CAmount
};
sqlCommand.Parameters.Add(sqlParameter);
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
if (sqlDataReader.Read())
{
return(true);
}
return(false);
}
public OutsiderLeasesInfo GetOutsiderLeasesInfo(string COLPhone, string CSEngine)
{
SQLConn sQLConn = new SQLConn();
string query = @"select * from OutsiderLeasesInfo where
SEngineNo = @CSEngineNo and OLPhone = @COLPhone";
SqlCommand sqlCommand = new SqlCommand(query, sQLConn.GetConnToLocalDb());
SqlParameter sqlParameter = new SqlParameter
{
ParameterName = "CSEngineNo",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = CSEngine
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "COLPhone",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = COLPhone
};
sqlCommand.Parameters.Add(sqlParameter);
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
if (sqlDataReader.Read())
{
OutsiderLeasesInfo outsiderLeasesInfo = new OutsiderLeasesInfo
{
OLCnic = sqlDataReader[0].ToString(),
OLName = sqlDataReader[1].ToString(),
OLPhone = sqlDataReader[2].ToString(),
OLAddress = sqlDataReader[3].ToString(),
SEngineNo = sqlDataReader[4].ToString(),
OLAmount = Int32.Parse(sqlDataReader[5].ToString()),
OLDuration = Int32.Parse(sqlDataReader[6].ToString()),
OLAdvance = Int32.Parse(sqlDataReader[7].ToString()),
OLInstallment = Int32.Parse(sqlDataReader[8].ToString()),
OLDate = DateTime.Parse(sqlDataReader[9].ToString()),
};
return(outsiderLeasesInfo);
}
return(null);
}
public void DeleteRcord(string CSEngine)
{
SQLConn sQLConn = new SQLConn();
string query = @"delete from StockInfo Where SEngineNo = @SCEngineNo";
SqlCommand sqlCommand = new SqlCommand(query, sQLConn.GetConnToLocalDb());
SqlParameter sqlParameter = new SqlParameter
{
ParameterName = "SCEngineNo",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = CSEngine
};
sqlCommand.Parameters.Add(sqlParameter);
sqlCommand.ExecuteNonQuery();
}
public BindingSource GetAllMemberLeasesInfo()
{
SQLConn sQLConn = new SQLConn();
string query = @"select * from memberlesesinfo";
SqlCommand sqlCommand = new SqlCommand(query, sQLConn.GetConnToLocalDb());
DataTable dataTable = new DataTable();
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
sqlDataAdapter.SelectCommand = sqlCommand;
sqlDataAdapter.Fill(dataTable);
BindingSource bindingSource = new BindingSource();
bindingSource.DataSource = dataTable;
sqlDataAdapter.Update(dataTable);
return(bindingSource);
}
public void UpdateCommitteInfo(int CCAmount, string CMPhone, string CMName, DateTime CCDate)
{
SQLConn sQLConn = new SQLConn();
string query = @"Update CommitteeInfo set
CAmount = @CAmount
where MPhone = @CMPhone and MName = @CMName and CDate = @CCDate";
SqlCommand sqlCommand = new SqlCommand(query, sQLConn.GetConnToLocalDb());
SqlParameter sqlParameter = new SqlParameter();
sqlParameter = new SqlParameter
{
ParameterName = "CAmount",
SqlDbType = System.Data.SqlDbType.Int,
Value = CCAmount
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "CCDate",
SqlDbType = System.Data.SqlDbType.Date,
Value = CCDate
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "CMPhone",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = CMPhone
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "CMName",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = CMName
};
sqlCommand.Parameters.Add(sqlParameter);
sqlCommand.ExecuteNonQuery();
}
public NetSellingInfo GetNetSellingInfo(string CCPhone, string CSEngineNo)
{
SQLConn sQLConn = new SQLConn();
string query = @"Select * From NetSellingInfo Where CPhone = @CCPhone
and SEngineNo = @CSEngineNo";
SqlCommand sqlCommand = new SqlCommand(query, sQLConn.GetConnToLocalDb());
SqlParameter sqlParameter = new SqlParameter
{
ParameterName = "CSEngineNo",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = CSEngineNo
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "CCPhone",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = CCPhone
};
sqlCommand.Parameters.Add(sqlParameter);
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
if (sqlDataReader.Read())
{
NetSellingInfo netSellingInfo = new NetSellingInfo
{
CCnic = sqlDataReader[0].ToString(),
CName = sqlDataReader[1].ToString(),
CAddress = sqlDataReader[2].ToString(),
CPhone = sqlDataReader[3].ToString(),
SEngineNo = sqlDataReader[4].ToString(),
CDate = DateTime.Parse(sqlDataReader[5].ToString()),
CAmount = Int32.Parse(sqlDataReader[6].ToString())
};
return(netSellingInfo);
}
return(null);
}
public void InsertLuckyMemberInfo(LukyMembersInfo lukyMembersInfo)
{
SQLConn sQLConn = new SQLConn();
string query = @"insert into LuckyMemberInfo(MPhone , MName , SEngineNo ,LDate )
values(@MPhone , @MName , @SEngineNo , @LDate)";
SqlCommand sqlCommand = new SqlCommand(query, sQLConn.GetConnToLocalDb());
SqlParameter sqlParameter = new SqlParameter
{
ParameterName = "MPhone",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = lukyMembersInfo.MPhone
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "MName",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = lukyMembersInfo.MName
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "SEngineNo",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = lukyMembersInfo.SEngineNo
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "LDate",
SqlDbType = System.Data.SqlDbType.Date,
Value = lukyMembersInfo.LDate
};
sqlCommand.Parameters.Add(sqlParameter);
sqlCommand.ExecuteNonQuery();
}
public void InsertExpensesInfo(ExpensesInfo expensesInfo)
{
SQLConn sQLConn = new SQLConn();
string query = @"insert into expensesinfo(@Amount , @EDescription , @EDate , @DDate)";
SqlCommand sqlCommand = new SqlCommand(query, sQLConn.GetConnToLocalDb());
SqlParameter sqlParameter = new SqlParameter
{
ParameterName = "Amount",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = expensesInfo.EAmount
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "EDescription",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = expensesInfo.EDescription
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "EDate",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = expensesInfo.EDate
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "DDate",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = expensesInfo.DDate
};
sqlCommand.Parameters.Add(sqlParameter);
sqlCommand.ExecuteNonQuery();
}
public InstallmentInfo GetInstallmentInfo(string COLPhone, string CSEngineNo)
{
SQLConn sQLConn = new SQLConn();
string query = @"select * from InstallmentInfo
where OLPhone= @COLPhone and SEngineNo = @CSEngineNo";
SqlCommand sqlCommand = new SqlCommand(query, sQLConn.GetConnToLocalDb());
SqlParameter sqlParameter = new SqlParameter();
sqlParameter = new SqlParameter
{
ParameterName = "COLPhone",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = COLPhone
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "CSEngineNo",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = CSEngineNo
};
sqlCommand.Parameters.Add(sqlParameter);
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
if (sqlDataReader.Read())
{
InstallmentInfo installmentInfo = new InstallmentInfo
{
OLPhone = sqlDataReader[0].ToString(),
SEngineNo = sqlDataReader[0].ToString(),
IAmount = Int32.Parse(sqlDataReader[0].ToString()),
DDate = DateTime.Parse(sqlDataReader[0].ToString()),
IDate = DateTime.Parse(sqlDataReader[0].ToString()),
};
return(installmentInfo);
}
return(null);
}
public MemberLeasesInfo GetMemberleasesInfo(string CSEngine, string CMPhone)
{
SQLConn sQLConn = new SQLConn();
string query = @"select * from memberlesesinfo where MPhone = @CMPhone and SEngineNo = @CSEngineNo";
SqlCommand sqlCommand = new SqlCommand(query, sQLConn.GetConnToLocalDb());
SqlParameter sqlParameter = new SqlParameter
{
ParameterName = "CMPhone",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = CMPhone
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "CSEngineNo",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = CSEngine
};
sqlCommand.Parameters.Add(sqlParameter);
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
if (sqlDataReader.Read())
{
MemberLeasesInfo memberLeasesInfo = new MemberLeasesInfo
{
MName = sqlDataReader[0].ToString(),
MPhone = sqlDataReader[1].ToString(),
SEngineNo = sqlDataReader[2].ToString(),
MLAdvance = Int32.Parse(sqlDataReader[3].ToString()),
MLDate = DateTime.Parse(sqlDataReader[4].ToString())
};
return(memberLeasesInfo);
}
return(null);
}
public void DeleteExpensesInfo(int CId)
{
SQLConn sQLConn = new SQLConn();
string query = @"Delete from ExpensesInfo
where ID= @CId";
SqlCommand sqlCommand = new SqlCommand(query, sQLConn.GetConnToLocalDb());
SqlParameter sqlParameter = new SqlParameter();
sqlParameter = new SqlParameter
{
ParameterName = "CId",
SqlDbType = System.Data.SqlDbType.Int,
Value = CId
};
sqlCommand.Parameters.Add(sqlParameter);
sqlCommand.ExecuteNonQuery();
}
public BindingSource GetAllOutsiderLeasesInfo()
{
SQLConn sQLConn = new SQLConn();
string query = @"select * from OutsiderLeasesInfo";
SqlCommand sqlCommand = new SqlCommand(query, sQLConn.GetConnToLocalDb());
DataTable dataTable = new DataTable();
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter
{
SelectCommand = sqlCommand
};
sqlDataAdapter.Fill(dataTable);
BindingSource bindingSource = new BindingSource
{
DataSource = dataTable
};
sqlDataAdapter.Update(dataTable);
return(bindingSource);
}
public LukyMembersInfo GetLuckyMemberInfo(string CSEngine, string CMPhone)
{
SQLConn sQLConn = new SQLConn();
string query = @"select * from luckymemberinfo where MPhone = @CMPhone and SEngine = @ESEngine";
SqlCommand sqlCommand = new SqlCommand(query, sQLConn.GetConnToLocalDb());
SqlParameter sqlParameter = new SqlParameter
{
ParameterName = "CMPhone",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = MPhone
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "CSEngineNo",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = MName
};
sqlCommand.Parameters.Add(sqlParameter);
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
if (sqlDataReader.Read())
{
LukyMembersInfo luckyMembersInfo = new LukyMembersInfo
{
MName = sqlDataReader[0].ToString(),
MPhone = sqlDataReader[1].ToString(),
SEngineNo = sqlDataReader[2].ToString(),
LDate = DateTime.Parse(sqlDataReader[4].ToString())
};
return(luckyMembersInfo);
}
return(null);
}
public MembersInfo GetMembersInfo(string MPhone, string MName)
{
SQLConn sQLConn = new SQLConn();
string query = @"select * from membersinfo where MPhone = @MPhone and MName = @MName";
SqlCommand sqlCommand = new SqlCommand(query, sQLConn.GetConnToLocalDb());
SqlParameter sqlParameter = new SqlParameter
{
ParameterName = "MPhone",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = MPhone
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "MName",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = MName
};
sqlCommand.Parameters.Add(sqlParameter);
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
if (sqlDataReader.Read())
{
MembersInfo membersInfo = new MembersInfo
{
MCnic = sqlDataReader[0].ToString(),
MName = sqlDataReader[1].ToString(),
MAddress = sqlDataReader[2].ToString(),
MPhone = sqlDataReader[3].ToString()
};
return(membersInfo);
}
return(null);
}
public void DeleteExpensesInfo(string CMPhone, string CMName, DateTime CCDate)
{
SQLConn sQLConn = new SQLConn();
string query = @"Delete from CommitteeInfo
where MPhone= @CMPhone and MName = @CMName and CDate = @CCDate";
SqlCommand sqlCommand = new SqlCommand(query, sQLConn.GetConnToLocalDb());
SqlParameter sqlParameter = new SqlParameter();
sqlParameter = new SqlParameter
{
ParameterName = "CMName",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = CMName
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "CMPhone",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = CMPhone
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "CCDate",
SqlDbType = System.Data.SqlDbType.Date,
Value = CCDate
};
sqlCommand.Parameters.Add(sqlParameter);
sqlCommand.ExecuteNonQuery();
}
public StockInfo GetStockInfo(string SEngine)
{
SQLConn sQLConn = new SQLConn();
string query = @"select * from stockinfo where SEngineNo = @SEngineNo";
SqlCommand sqlCommand = new SqlCommand(query, sQLConn.GetConnToLocalDb());
SqlParameter sqlParameter = new SqlParameter
{
ParameterName = "SEngineNo",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = SEngine
};
sqlCommand.Parameters.Add(sqlParameter);
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
if (sqlDataReader.Read())
{
StockInfo stockInfo = new StockInfo()
{
SEngineNo = sqlDataReader[0].ToString(),
SChassisNo = sqlDataReader[1].ToString(),
SCompany = sqlDataReader[2].ToString(),
SModel = sqlDataReader[3].ToString(),
SAmount = Int32.Parse(sqlDataReader[4].ToString()),
SRegNo = sqlDataReader[5].ToString(),
SFileNo = sqlDataReader[6].ToString(),
SDate = DateTime.Parse(sqlDataReader[7].ToString()),
CopyRecived = Int32.Parse(sqlDataReader[8].ToString()),
LetterRecived = Int32.Parse(sqlDataReader[9].ToString()),
};
return(stockInfo);
}
return(null);
}
public void UpdateMemberLeasesInfo(MemberLeasesInfo memberLeasesInfo, string CSEngine, string CMPhone)
{
SQLConn sQLConn = new SQLConn();
string query = @"Update MemberlesesInfo set
MPhone = @MPhone , MName = @MName , SEngineNo = @SEngineNo
, LAdvance = @LAdvance ,LDate = @LDate
where Mphone = @CMPhone and SEngineNo = @CSEngine";
SqlCommand sqlCommand = new SqlCommand(query, sQLConn.GetConnToLocalDb());
SqlParameter sqlParameter = new SqlParameter
{
ParameterName = "MPhone",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = memberLeasesInfo.MPhone
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "MName",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = memberLeasesInfo.MName
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "SEngineNo",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = memberLeasesInfo.SEngineNo
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "LAdvance",
SqlDbType = System.Data.SqlDbType.Int,
Value = memberLeasesInfo.MLAdvance
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "LDate",
SqlDbType = System.Data.SqlDbType.Date,
Value = memberLeasesInfo.MLDate
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "CSEngine",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = CSEngine
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "CMPhone",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = CMPhone
};
sqlCommand.Parameters.Add(sqlParameter);
sqlCommand.ExecuteNonQuery();
}
public void UpdateOutsiderLeasesInfo(OutsiderLeasesInfo outsiderLeasesInfo, string COLPhone, string CSEngine)
{
SQLConn sQLConn = new SQLConn();
string query = @"Update OutsiderLeasesInfo set
OLCnic = @OLCnic, OLName = @OLName, OLPhone = @OLPhone
, OLAdvance = @OLAdvance, OLAmount = @OLAmount , OLDuration = @OLDuration,
OLInstallment = @OLInstallment , OLDate = @OLDate
where OLPhone = @COLPhone and SEngineNo = @CSEngineNo";
SqlCommand sqlCommand = new SqlCommand(query, sQLConn.GetConnToLocalDb());
SqlParameter sqlParameter = new SqlParameter
{
ParameterName = "OLCnic",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = outsiderLeasesInfo.OLCnic
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "OLName",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = outsiderLeasesInfo.OLName
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "OLInstallment",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = outsiderLeasesInfo.OLInstallment
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "OLPhone",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = outsiderLeasesInfo.OLPhone
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "OLAddress",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = outsiderLeasesInfo.OLAddress
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "OLAmount",
SqlDbType = System.Data.SqlDbType.Int,
Value = outsiderLeasesInfo.OLAmount
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "OLDuration",
SqlDbType = System.Data.SqlDbType.Int,
Value = outsiderLeasesInfo.OLDuration
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "OLAdvance",
SqlDbType = System.Data.SqlDbType.Int,
Value = outsiderLeasesInfo.OLAdvance
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "SEngineNo",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = outsiderLeasesInfo.SEngineNo
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "OLDate",
SqlDbType = System.Data.SqlDbType.Date,
Value = outsiderLeasesInfo.OLDate
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "COLPhone",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = COLPhone
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "CSEngineNo",
SqlDbType = System.Data.SqlDbType.Int,
Value = CSEngine
};
sqlCommand.Parameters.Add(sqlParameter);
sqlCommand.ExecuteNonQuery();
}
public void UpdateLuckyMemberInfo(InstallmentInfo installmentInfo, string COLPhone, string CSEngineNo)
{
SQLConn sQLConn = new SQLConn();
string query = @"Update installmentinfo set
OLPhone = @OLPhone , SEngineNo = @SEngineNo , IAmount = @IAmount
, DDate = @DDate , IDate = @IDate
where OLPhone = @COLPhone and SEngineNo = @CSEngine";
SqlCommand sqlCommand = new SqlCommand(query, sQLConn.GetConnToLocalDb());
SqlParameter sqlParameter = new SqlParameter
{
ParameterName = "OLPhone",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = installmentInfo.OLPhone
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "SEngineNo",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = installmentInfo.SEngineNo
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "IAmount",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = installmentInfo.IAmount
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "DDate",
SqlDbType = System.Data.SqlDbType.Date,
Value = installmentInfo.DDate
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "IDate",
SqlDbType = System.Data.SqlDbType.Date,
Value = installmentInfo.IDate
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "COLPhone",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = COLPhone
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "CSEngineNo",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = CSEngineNo
};
sqlCommand.Parameters.Add(sqlParameter);
sqlCommand.ExecuteNonQuery();
}
public void InsertOutsiderLeasesInfo(OutsiderLeasesInfo outsiderLeasesInfo)
{
SQLConn sQLConn = new SQLConn();
string query = @"insert into OutsiderLeasesInfo (OLCnic , OLName , OLPhone , OLAddress,
SEngineNo , OLAmount , OLDuration , OLAdvance , OLInstallment , OLDate)
values(@OLCnic , @OLName , @OLPhone , @OLAddress ,
@SEngineNo , @OLAmount , @OLDuration , @OLAdvance , @OLInstallment , @OLDate)";
SqlCommand sqlCommand = new SqlCommand(query, sQLConn.GetConnToLocalDb());
SqlParameter sqlParameter = new SqlParameter
{
ParameterName = "OLCnic",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = outsiderLeasesInfo.OLCnic
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "OLName",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = outsiderLeasesInfo.OLName
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "OLPhone",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = outsiderLeasesInfo.OLPhone
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "OLAddress",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = outsiderLeasesInfo.OLAddress
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "SEngineNo",
SqlDbType = System.Data.SqlDbType.NVarChar,
Value = outsiderLeasesInfo.SEngineNo
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "OLAmount",
SqlDbType = System.Data.SqlDbType.Int,
Value = outsiderLeasesInfo.OLAmount
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "OLDuration",
SqlDbType = System.Data.SqlDbType.Int,
Value = outsiderLeasesInfo.OLDuration
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "OLAdvance",
SqlDbType = System.Data.SqlDbType.Int,
Value = outsiderLeasesInfo.OLAdvance
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "OLInstallment",
SqlDbType = System.Data.SqlDbType.Int,
Value = outsiderLeasesInfo.OLInstallment
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter
{
ParameterName = "OLDate",
SqlDbType = System.Data.SqlDbType.Date,
Value = outsiderLeasesInfo.OLDate
};
sqlCommand.Parameters.Add(sqlParameter);
sqlCommand.ExecuteNonQuery();
}
