public IList <StatsTargetConfig> GetAlltargets()
{
var result = new List <StatsTargetConfig>();
SqlConnection conn = new SqlConnection();
conn.ConnectionString = CONNSTRING;
var strSqlContactStmt = @"SELECT Id, Title, Amount, CreatedUserId, CreatedDate,TargetDay,Reached FROM dbo.StatsTargetConfig";
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = strSqlContactStmt;
cmd.Connection = conn;
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
var statsConfig = new StatsTargetConfig()
{
Id = Convert.ToInt32(reader["Id"]),
TargetDay = Convert.ToDateTime(reader["TargetDay"]),
TargetAmount = Convert.ToDecimal(reader["Amount"]),
IsReached = Convert.ToBoolean(reader["Reached"]),
TargetTitle = reader["Title"].ToString(),
};
result.Add(statsConfig);
}
}
conn.Close();
return(result);
}
public IList <StatsTargetConfig> GetAllTargetItems()
{
var result = new List <StatsTargetConfig>();
SqlConnection con = new SqlConnection();
con.ConnectionString = CONNSTRING;
using (SqlCommand cmd = new SqlCommand("dbo.spgetstatssonfigtargets", con))
{
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
SqlDataReader sdr = cmd.ExecuteReader();
while (sdr.Read())
{
var obj = new StatsTargetConfig()
{
Id = Convert.ToInt32(sdr["Id"]),
IsReached = Convert.ToBoolean(sdr["Reached"]),
TargetDay = Convert.ToDateTime(sdr["TargetDay"]),
TargetAmount = Convert.ToDecimal(sdr["Amount"]),
TargetTitle = sdr["Title"].ToString()
};
result.Add(obj);
}
}
con.Close();
return(result);
}
public void CreateStatsConfig(StatsTargetConfig obj, int userId)
{
SqlConnection conn = new SqlConnection();
conn.ConnectionString = CONNSTRING;
var strSqlContactStmt = @"INSERT INTO StatsTargetConfig(Title, Amount, CreatedUserId, CreatedDate, TargetDay,Reached)
VALUES(@targettitle, @targetamount, @createduserId,CURRENT_TIMESTAMP ,@targetday,@reached)";
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = conn;
conn.Open();
cmd.Parameters.Add("@targettitle", SqlDbType.NVarChar).Value = obj.TargetTitle;
cmd.Parameters.Add("@reached", SqlDbType.Bit).Value = obj.IsReached;
cmd.Parameters.Add("@targetamount", SqlDbType.Money).Value = obj.TargetAmount;
cmd.Parameters.Add("@targetday", SqlDbType.DateTime).Value = obj.TargetDay;
cmd.Parameters.Add("@createduserId", SqlDbType.NVarChar).Value = userId;
cmd.CommandText = strSqlContactStmt;
cmd.ExecuteNonQuery();
}
conn.Close();
}
public void InsertIntoTargetConfig(StatsTargetConfig obj, int userId)
{
SqlConnection conn = new SqlConnection();
conn.ConnectionString = CONNSTRING;
using (SqlCommand cmd = new SqlCommand("dbo.spinsertstatsconfig", conn))
{
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@title", SqlDbType.VarChar).Value = obj.TargetTitle;
cmd.Parameters.Add("@amount", SqlDbType.Money).Value = obj.TargetAmount;
cmd.Parameters.Add("@targetday", SqlDbType.DateTime).Value = obj.TargetDay;
cmd.Parameters.Add("@reached", SqlDbType.Bit).Value = obj.IsReached;
cmd.Parameters.Add("@createduserid", SqlDbType.Int).Value = userId;
cmd.Parameters.Add("@createdate", SqlDbType.DateTime).Value = DateTime.Now;
cmd.Parameters.Add("@createdId", SqlDbType.Int).Direction = ParameterDirection.Output;
conn.Open();
cmd.ExecuteNonQuery();
int contractID = Convert.ToInt32(cmd.Parameters["@createdId"].Value);
conn.Close();
}
}