Hi,
I need to send daily summary report of the user actions on website i.e. if user adds some other user or user modified some details.
In short the entire work report of user needs to be mail to admin.
I am using windows service for this.
I want to know logic to implement summary report of day for every user
Please Help me.
Thanks in advance
I am attaching my code
DataTable dt = new DataTable();
string createdquery = "SELECT LISTNAME,EMAILID,FNAME,LNAME,DATECREATED FROM TBL_LISTCONTACTS WHERE DATEPART(DAY, DATECREATED) = @Day AND DATEPART(MONTH, DATECREATED) = @Month";
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(createdquery))
{
cmd.Connection = con;
cmd.Parameters.AddWithValue("@Day", DateTime.Today.Day);
cmd.Parameters.AddWithValue("@Month", DateTime.Today.Month);
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
sda.Fill(dt);
WriteToFile("ROWS:.......................... " + dt.Rows.Count);
}
}
}
DataTable mdt = new DataTable();
string modifcre = "SELECT LISTNAME,EMAILID,FNAME,LNAME,DATEMODIFIED,DATECREATED FROM TBL_LISTCONTACTS WHERE DATEPART(DAY, DATEMODIFIED) = @Day AND DATEPART(MONTH, DATEMODIFIED) = @Month";
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(modifcre))
{
cmd.Connection = con;
cmd.Parameters.AddWithValue("@Day", DateTime.Today.Day);
cmd.Parameters.AddWithValue("@Month", DateTime.Today.Month);
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
sda.Fill(mdt);
WriteToFile("ROWS:.......................... " + mdt.Rows.Count);
}
}
}
if (dt.Rows.Count > 0 || mdt.Rows.Count > 0)
{
/*****************************************Created for lopp*****************************************/
for (int i = 0; i < dt.Rows.Count; i++)
{
string listname = dt.Rows[i]["LISTNAME"].ToString();
string name = dt.Rows[i]["FNAME"].ToString();
string email = dt.Rows[i]["EMAILID"].ToString();
//WriteToFile("LISTNAME from TBL_LISTCONTAACTS:........................................ " + i + "................................" + listname);
//WriteToFile("name from TBL_LISTCONTAACTS:........................................ " + i + "................................" + name);
//WriteToFile("email from TBL_LISTCONTAACTS:........................................ " + i + "................................" + email);
DataTable createdtstbllist = new DataTable();
string createdtstbllistquery = "SELECT [LISTNAME],[LISTUSERLOGIN],LISTFROMEMAILID FROM [MAILCHIMP].[dbo].[TBL_LIST] where LISTNAME=@listname";
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(createdtstbllistquery))
{
cmd.Connection = con;
cmd.Parameters.AddWithValue("@listname", listname);
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
sda.Fill(createdtstbllist);
//WriteToFile("ROWS of TBL_LIST:.......................... " + createdtstbllist.Rows.Count);
}
}
}
for (int j = 0; j < createdtstbllist.Rows.Count; j++)
{
string tbllistname = createdtstbllist.Rows[j]["LISTNAME"].ToString();
string useradmin = createdtstbllist.Rows[j]["LISTUSERLOGIN"].ToString();
string LISTFROMEMAILID = createdtstbllist.Rows[j]["LISTFROMEMAILID"].ToString();
//WriteToFile("tbllistname from TBL_LIST:........................................ " + j + "................................" + tbllistname);
//WriteToFile("LISTUSERLOGIN from TBL_LIST:........................................ " + j + "................................" + useradmin);
//WriteToFile("LISTFROMEMAILID from TBL_LIST:........................................ " + j + "................................" + LISTFROMEMAILID);
DataTable creaetdtsstbllogin = new DataTable();
string creaettblloginquery = "SELECT UNAME, UFNAME,UEMAILID FROM TBL_LOGIN where UNAME=@useradmin";
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(creaettblloginquery))
{
cmd.Connection = con;
cmd.Parameters.AddWithValue("@useradmin", useradmin);
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
sda.Fill(creaetdtsstbllogin);
//WriteToFile("ROWS of TBL_LOGIN:.......................... " + creaetdtsstbllogin.Rows.Count);
}
}
}
for (int k = 0; k < creaetdtsstbllogin.Rows.Count; k++)
{
dtemails = new DataTable();
string UNAME = creaetdtsstbllogin.Rows[k]["UNAME"].ToString();
emailid = creaetdtsstbllogin.Rows[k]["UEMAILID"].ToString();
emailidcoum = new DataColumn("col1", typeof(String));
dtemails.Columns.Add(emailidcoum);
emailidcoum = new DataColumn("col2", typeof(String));
dtemails.Columns.Add(emailidcoum);
emailidcoum = new DataColumn("col3", typeof(String));
dtemails.Columns.Add(emailidcoum);
emailidcoum = new DataColumn("col4", typeof(String));
dtemails.Columns.Add(emailidcoum);
dr = dtemails.NewRow();
dr[0] = emailid;
dr[1] = UNAME;
dr[2] = "add";
dr[3] = "";
dtemails.Rows.Add(dr);
addresult = "created";
//WriteToFile("EMAILID Rows Created:.......................... " + dtemails.Rows.Count);
// string UNAME = creaetdtsstbllogin.Rows[k]["UNAME"].ToString();
//WriteToFile("username:.......................... " + k + "................................" + UNAME);
//WriteToFile("ADMIN ID:.......................... " + k + "................................" + emailid);
}
}
}
/*****************************************modified for lopp*****************************************/
for (int i = 0; i < mdt.Rows.Count; i++)
{
string dm = mdt.Rows[i]["DATEMODIFIED"].ToString();
string dc = mdt.Rows[i]["DATECREATED"].ToString();
//WriteToFile("DM........................................ " + dm);
//WriteToFile("DC........................................ " + dc);
DateTime dcre = Convert.ToDateTime(mdt.Rows[i]["DATEMODIFIED"].ToString());
if (dm != dc)
{
WriteToFile(" Date Modfications........................................ ");
string listname = mdt.Rows[i]["LISTNAME"].ToString();
string name = mdt.Rows[i]["FNAME"].ToString();
string email = mdt.Rows[i]["EMAILID"].ToString();
//WriteToFile("LISTNAME from TBL_LISTCONTAACTS:........................................ " + i + "................................" + listname);
//WriteToFile("name from TBL_LISTCONTAACTS:........................................ " + i + "................................" + name);
//WriteToFile("email from TBL_LISTCONTAACTS:........................................ " + i + "................................" + email);
DataTable modfitbllist = new DataTable();
string modfitbllistquery = "SELECT [LISTNAME],[LISTUSERLOGIN],LISTFROMEMAILID FROM [MAILCHIMP].[dbo].[TBL_LIST] where LISTNAME=@listname";
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(modfitbllistquery))
{
cmd.Connection = con;
cmd.Parameters.AddWithValue("@listname", listname);
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
sda.Fill(modfitbllist);
//WriteToFile("ROWS of TBL_LIST:.......................... " + modfitbllist.Rows.Count);
}
}
}
for (int j = 0; j < modfitbllist.Rows.Count; j++)
{
string tbllistname = modfitbllist.Rows[j]["LISTNAME"].ToString();
string useradmin = modfitbllist.Rows[j]["LISTUSERLOGIN"].ToString();
string LISTFROMEMAILID = modfitbllist.Rows[j]["LISTFROMEMAILID"].ToString();
//WriteToFile("tbllistname from TBL_LIST:........................................ " + j + "................................" + tbllistname);
//WriteToFile("LISTUSERLOGIN from TBL_LIST:........................................ " + j + "................................" + useradmin);
//WriteToFile("LISTFROMEMAILID from TBL_LIST:........................................ " + j + "................................" + LISTFROMEMAILID);
DataTable modtbllogindts = new DataTable();
string modtblloginquery = "SELECT UNAME, UFNAME,UEMAILID FROM TBL_LOGIN where UNAME=@useradmin";
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(modtblloginquery))
{
cmd.Connection = con;
cmd.Parameters.AddWithValue("@useradmin", useradmin);
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
sda.Fill(modtbllogindts);
//WriteToFile("ROWS of TBL_LOGIN:.......................... " + modtbllogindts.Rows.Count);
}
}
}
for (int k = 0; k < modtbllogindts.Rows.Count; k++)
{
string UNAME = modtbllogindts.Rows[k]["UNAME"].ToString();
emailid = modtbllogindts.Rows[k]["UEMAILID"].ToString();
dr = dtemails.NewRow();
dr[0] = emailid;
dr[1] = UNAME;
if ( addresult != "" )
{
dr[2] = "add";
}
else
{
dr[2] = "";
}
dr[2] = "update";
dtemails.Rows.Add(dr);
updateresult = "update";
}
}
}
else
{
//WriteToFile("Date is equal........................................ ");
}
}
}
for (int i = 0; i < dtemails.Rows.Count; i++)
{
//if (addresult != "" && updateresult != "")
// {
// WriteToFile("both");
// WriteToFile("Email sent successfully to: " + dtemails.Rows[i][1].ToString() + " " + dtemails.Rows[i][0].ToString());
// }
// else if (addresult != "")
// {
// WriteToFile("addresult");
// WriteToFile("Email sent successfully to: " + dtemails.Rows[i][1].ToString() + " " + dtemails.Rows[i][0].ToString());
// }
// else if (updateresult != "")
// {
// WriteToFile("updateresult");
// WriteToFile("Email sent successfully to: " + dtemails.Rows[i][1].ToString() + " " + dtemails.Rows[i][0].ToString());
// }
string add = dtemails.Rows[i][2].ToString();
string update = dtemails.Rows[i][3].ToString();
if (add!="" && update!="")
{
WriteToFile("both");
WriteToFile("Email sent successfully to: " + dtemails.Rows[i][1].ToString() + " " + dtemails.Rows[i][0].ToString());
}
else if (add != "")
{
WriteToFile("addresult");
WriteToFile("Email sent successfully to: " + dtemails.Rows[i][1].ToString() + " " + dtemails.Rows[i][0].ToString());
break;
}
else if (update != "")
{
WriteToFile("updateresult");
WriteToFile("Email sent successfully to: " + dtemails.Rows[i][1].ToString() + " " + dtemails.Rows[i][0].ToString());
}
}