private int GetUpSave(double totalInc, double totalExp, double profit, string month, string year)
{
SqlConnection connection1 = new SqlConnection(ConnectionUtility.ConnectionString);
ViewClassForDetails viewClassForDetails = new ViewClassForDetails();
string query1 = "INSERT INTO [dbo].[ViewClassForDetailsTable] ([TotalInc],[TotalExp],[Profit],[Month],[Year]) VALUES ('" + totalInc + "' ,'" + totalExp + "' ,'" + profit + "' , '" + month + "' , '" + year + "' ) ";
SqlCommand command1 = new SqlCommand(query1, connection1);
connection1.Open();
int rowAffected = command1.ExecuteNonQuery();
connection1.Close();
return(rowAffected);
}
private int GetUpDate(double totalInc, double totalExp, double profit, string month, string year)
{
SqlConnection connection1 = new SqlConnection(ConnectionUtility.ConnectionString);
ViewClassForDetails viewClassForDetails = new ViewClassForDetails();
string query1 = "UPDATE [dbo].[ViewClassForDetailsTable] SET [TotalInc] = '" + totalInc + "' ,[TotalExp] = '" + totalExp + "' ,[Profit] = '" + profit + "' ,[Month] = '" + month + "' , [Year] = '" + year + "' WHERE [Month] = '" + month + "' AND [Year] = '" + year + "' ";
SqlCommand command1 = new SqlCommand(query1, connection1);
connection1.Open();
int rowAffected = command1.ExecuteNonQuery();
connection1.Close();
return(rowAffected);
}
private List <ViewClassForDetails> GetAllIncomeExpenseProfit(string year)
{
//List<ViewClassForDetails> ViewClassList = new List<ViewClassForDetails>();
//ViewClassForDetails viewClassFor = new ViewClassForDetails();
//foreach (ViewClassForDetails incom in ViewClassList)
//{
// //viewClassFor.TotalInc = Totalincome(income.Totalincome);
// ViewClassList.Add(incom);
//}
ViewClassForDetails viewClassFor = new ViewClassForDetails();
List <ViewClassForDetails> ViewClassList = new List <ViewClassForDetails>();
SqlConnection connection = new SqlConnection(ConnectionUtility.ConnectionString);
string query = "SELECT * FROM [dbo].[ViewClassForDetailsTable] WHERE [Year] = '" + year + "' ";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
viewClassFor.Month = reader["Month"].ToString();
viewClassFor.Year = reader["Year"].ToString();
viewClassFor.TotalInc = Convert.ToDouble(reader["TotalInc"].ToString());
viewClassFor.TotalExp = (double)(reader["TotalExp"]);
viewClassFor.Profit = (double)(reader["Profit"]);
ViewClassList.Add(viewClassFor);
}
reader.Close();
connection.Close();
return(ViewClassList);
}
/// <summary>
/// Update Income and Total Yearly Table's insert or update data
/// </summary>
/// <param name="income"></param>
/// <returns></returns>
private int SaveViewClassForDetailsTable(Income income, double incomeAmount, string inMonth, string inYear)
{
ViewClassForDetails viewClassForDetails = new ViewClassForDetails();
string dobdat = income.IncomeDate;
int ddateln = dobdat.Length;
string month = dobdat[ddateln - 5].ToString() + dobdat[ddateln - 4].ToString();
string dYear = dobdat[ddateln - 10].ToString() + dobdat[ddateln - 9].ToString() + dobdat[ddateln - 8].ToString() + dobdat[ddateln - 7].ToString();
// string month,string dYear
if (month == "01")
{
viewClassForDetails.Month = "January";
}
else if (month == "02")
{
viewClassForDetails.Month = "February";
}
else if (month == "03")
{
viewClassForDetails.Month = "March";
}
else if (month == "04")
{
viewClassForDetails.Month = "April";
}
else if (month == "05")
{
viewClassForDetails.Month = "May";
}
else if (month == "06")
{
viewClassForDetails.Month = "June";
}
else if (month == "07")
{
viewClassForDetails.Month = "July";
}
else if (month == "08")
{
viewClassForDetails.Month = "August";
}
else if (month == "09")
{
viewClassForDetails.Month = "September";
}
else if (month == "10")
{
viewClassForDetails.Month = "October";
}
else if (month == "11")
{
viewClassForDetails.Month = "November";
}
else if (month == "12")
{
viewClassForDetails.Month = "December";
}
viewClassForDetails.Year = dYear;
viewClassForDetails.TotalInc = Totalincome(viewClassForDetails.Month, viewClassForDetails.Year, incomeAmount);
viewClassForDetails.TotalExp = TotalExpence(viewClassForDetails.Month, viewClassForDetails.Year);
viewClassForDetails.Profit = (viewClassForDetails.TotalInc) - (viewClassForDetails.TotalExp);
viewClassForDetails.Year = viewClassForDetails.Year;
viewClassForDetails.Month = viewClassForDetails.Month;
var registration = new ViewClassForDetails
{
TotalInc = viewClassForDetails.TotalInc,
TotalExp = viewClassForDetails.TotalExp,
Profit = viewClassForDetails.Profit,
Month = viewClassForDetails.Month,
Year = viewClassForDetails.Year
};
if (inMonth != viewClassForDetails.Month && inYear != viewClassForDetails.Year)
{
//_context.ViewClassForDetailsTable.Add(registration);
int saveDetails = GetUpSave(viewClassForDetails.TotalInc, viewClassForDetails.TotalExp, viewClassForDetails.Profit, viewClassForDetails.Month, viewClassForDetails.Year);
if (saveDetails > 0)
{
return(1);
}
else
{
return(0);
}
}
else if (inMonth != viewClassForDetails.Month && inYear == viewClassForDetails.Year)
{
////// _context.ViewClassForDetailsTable.Add(registration);
////_context.Add(registration);
////_context.SaveChanges();
int saveDetails = GetUpSave(viewClassForDetails.TotalInc, viewClassForDetails.TotalExp, viewClassForDetails.Profit, viewClassForDetails.Month, viewClassForDetails.Year);
if (saveDetails > 0)
{
return(1);
}
else
{
return(0);
}
}
else if (inMonth == viewClassForDetails.Month && inYear != viewClassForDetails.Year)
{
// _context.ViewClassForDetailsTable.Add(registration);
//_context.Add(registration);
//_context.SaveChanges();
int saveDetails = GetUpSave(viewClassForDetails.TotalInc, viewClassForDetails.TotalExp, viewClassForDetails.Profit, viewClassForDetails.Month, viewClassForDetails.Year);
if (saveDetails > 0)
{
return(1);
}
else
{
return(0);
}
}
else if (inMonth == viewClassForDetails.Month && inYear == viewClassForDetails.Year)
{
int upDetails = GetUpDate(viewClassForDetails.TotalInc, viewClassForDetails.TotalExp, viewClassForDetails.Profit, viewClassForDetails.Month, viewClassForDetails.Year);
if (upDetails > 0)
{
return(1);
}
else
{
return(0);
}
}
if (registration != null)
{
return(1);
}
else
{
return(0);
}
}