using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using EmployeeMonitorMVc.Models.Repository;
using Dapper;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.Globalization;
namespace EmployeeMonitorMVc.Models.Repository
{
public class DatabseOperations : IDatabseOperations
{
string ConnectionString = "";
public string GetConnectionString()
{
//ConnectionString = "Data Source=JYOTHIS-PC\\SQLEXPRESS;Initial Catalog=CurrentwareEmployeeMonitor;User Id=sa; password=123;";
return ConnectionString;
}
public DataTable GetInDatatable(string str)
{
GetConnectionString();
DataTable dt1 = null;
dt1 = new DataTable();
SqlDataAdapter ad = new SqlDataAdapter(str, ConnectionString);
ad.Fill(dt1);
return dt1;
}
public void ExecuteQuery(string str)
{
GetConnectionString();
SqlConnection con = new SqlConnection(ConnectionString);
con.Open();
SqlCommand cmd = new SqlCommand(str, con);
cmd.ExecuteNonQuery();
con.Close();
}
/// <summary>
/// For getting menu settings based on user id
/// </summary>
/// <param name="user_id"></param>
/// <returns></returns>
public IEnumerable<Menulist> GetMenuList(string user_id)
{
using (SqlConnection con = new SqlConnection(GetConnectionString()))
{
var para = new DynamicParameters();
para.Add("@type", "");
return con.Query<Menulist>("Get_MenuList", para, null, true, 0, CommandType.StoredProcedure).ToList();
}
}
public void AddTomodificationTable(string user_id,string comments,string action,string editor_id)
{
string str = "insert into Modifications (user_id,comments,Action,Editor_id,edited_date) values(" + user_id + ",'" + comments + "'," + action + "," + editor_id + ",getdate())";
ExecuteQuery(str);
}
public IEnumerable<ChatUsers> GetChatUsers(string user_id)
{
using (SqlConnection con = new SqlConnection(GetConnectionString()))
{
var para = new DynamicParameters();
para.Add("@user_id", user_id);
return con.Query<ChatUsers>("Getchatusers", para, null, true, 0, CommandType.StoredProcedure).ToList();
}
}
public string getusername(string user_id)
{
string username = "";
string str = "select user_name from employee_table where user_id=" + user_id + "";
DataTable dt = GetInDatatable(str);
if(dt.Rows.Count>0)
{
username = dt.Rows[0][0].ToString();
}
return username;
}
/// <summary>
/// for getting user timings based on usedd id and date range
/// </summary>
/// <param name="user_id"></param>
/// <param name="startdt"></param>
/// <param name="enddt"></param>
/// <returns></returns>
public IEnumerable<GetUserSummary> UserSummaryTimings(string user_id, string startdt, string enddt)
{
using (SqlConnection con = new SqlConnection(GetConnectionString()))
{
var para = new DynamicParameters();
para.Add("user_id", Convert.ToInt32(user_id));
para.Add("start_date", Convert.ToDateTime(startdt));
para.Add("end_date", Convert.ToDateTime(enddt));
return con.Query<GetUserSummary>("Get_userTimings", para, null, true, 0, CommandType.StoredProcedure).ToList();
//con.Execute("sprocPaymentDetailsInsertUpdateSingleItem", paramater, null, 0, CommandType.StoredProcedure);
}
}
public IEnumerable<GetAllUserData> ALLUserSummaryTimings()
{
using (SqlConnection con = new SqlConnection(GetConnectionString()))
{
var para = new DynamicParameters();
return con.Query<GetAllUserData>("Get_Alluser_Data", para, null, true, 0, CommandType.StoredProcedure).ToList();
}
}
public IEnumerable<ChatContent> GetChatData(string user_id1, string user_id2)
{
using (SqlConnection con = new SqlConnection(GetConnectionString()))
{
var para = new DynamicParameters();
para.Add("user_id1", Convert.ToInt32(user_id1));
para.Add("user_id2", Convert.ToInt32(user_id2));
return con.Query<ChatContent>("Get_ChatData", para, null, true, 0, CommandType.StoredProcedure).ToList();
//con.Execute("sprocPaymentDetailsInsertUpdateSingleItem", paramater, null, 0, CommandType.StoredProcedure);
}
}
public DataSet GetUserProfile(string user_id)
{
using (SqlConnection con = new SqlConnection(GetConnectionString()))
{
con.Open();
DataSet ds = new DataSet();
try
{
SqlCommand cmd = new SqlCommand("get_profile_details", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("user_id", user_id);
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(ds);
if (ds.Tables.Count > 0)
{
return ds;
}
else
{
return ds = null;
}
}
catch (Exception)
{
throw;
}
finally
{
ds.Dispose();
}
}
}
#region layot page
public string GetLastUpdatedTime()
{
string upadted_time = "";
string str = "";
str = "select menu from menu_table where id=1";
SqlConnection con = new SqlConnection(GetConnectionString());
con.Open();
SqlCommand cmd = new SqlCommand(str, con);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
upadted_time = rdr["menu"].ToString();
}
con.Close();
return upadted_time;
}
public int GetNotificationCount(string user_id)
{
int Notification_count = 0;
string str = "select count(User_id) from Notification where User_id=" + user_id + " ";
DataTable dt=GetInDatatable(str);
if(dt.Rows.Count>0)
{
Notification_count = Convert.ToInt32(dt.Rows[0][0].ToString());
}
return Notification_count;
}
public int GetMessageNotificationCount(string user_id)
{
int Notification_count = 0;
string str = "select count(user_id) from employee_table where User_id=" + user_id + " and email_send_date=getdate()";
DataTable dt = GetInDatatable(str);
if (dt.Rows.Count > 0)
{
Notification_count = Convert.ToInt32(dt.Rows[0][0].ToString());
}
return Notification_count;
}
public string PredictedLeavingTime(string user_id)
{
string PredictedTime = "0";
string str = " select isnull(sum(Duration),0) as [Hrs],DatePart(Second,getdate()) as [Seconds] from " +
" (select case when ISNULL(punch_out, GETDATE())>punch_in " +
" then DATEDIFF(MINUTE, punch_in, ISNULL(punch_out, GETDATE())) else 0 end as 'Duration' " +
" from employee_details_table where user_id=" + user_id + " and Punch_date=CONVERT(date, getdate()) ) k";
DataTable dt = GetInDatatable(str);
if (dt.Rows.Count > 0)
{
int target = 480;
int current = Convert.ToInt32(dt.Rows[0][0]);
if (current >= target)
{
PredictedTime = "Congrats you completed 8 Hrs Today";
}
else if (current>0)
{
int Remaining = target - current;
DateTime dtStartDate = DateTime.Now.AddMinutes(Remaining);
PredictedTime = "8 Hrs Will Complete at "+dtStartDate.ToString("dd/MMM/yyyy hh:mm tt", CultureInfo.InvariantCulture);
}
else if(current==0)
{
PredictedTime = "";
}
}
return PredictedTime;
}
public string GetTodaysHoursForFireWorks(string user_id)
{
string TodayWorkingHours = "0";
string str = " select isnull(sum(Duration),0) as [Hrs],DatePart(Second,getdate()) as [Seconds] from " +
" (select case when ISNULL(punch_out, GETDATE())>punch_in " +
" then DATEDIFF(MINUTE, punch_in, ISNULL(punch_out, GETDATE())) else 0 end as 'Duration' " +
" from employee_details_table where user_id=" + user_id + " and Punch_date=CONVERT(date, getdate()) ) k";
DataTable dt = GetInDatatable(str);
if (dt.Rows.Count > 0)
{
TodayWorkingHours = dt.Rows[0][0].ToString();
}
return TodayWorkingHours;
}
public string GetTodaysHours(string user_id)
{
// elect sum(Duration / 60 + (Duration % 60) / 100.0) as [Hrs],DatePart(Second,getdate()) as [Seconds] from " +
string TodayWorkingHours = "0";
string str = " select isnull(sum(Duration),0) as [Hrs],DatePart(Second,getdate()) as [Seconds] from " +
" (select case when ISNULL(punch_out, GETDATE())>punch_in " +
" then DATEDIFF(MINUTE, punch_in, ISNULL(punch_out, GETDATE())) else 0 end as 'Duration' " +
" from employee_details_table where user_id=" + user_id + " and Punch_date=CONVERT(date, getdate()) ) k";
DataTable dt = GetInDatatable(str);
if (dt.Rows.Count > 0)
{
DateTime dtStartDate = DateTime.MinValue.AddMinutes(Convert.ToInt32(dt.Rows[0][0])).AddSeconds(Convert.ToInt32(dt.Rows[0][1]));
TodayWorkingHours = dtStartDate.ToString("HH:mm:ss", CultureInfo.InvariantCulture);
}
return TodayWorkingHours;
}
public string GetMonthHours(string user_id)
{
string CurrentMonthWorkingHours = "0";
string str = " select isnull(sum(Duration),0) as [Hrs],DatePart(Second,getdate()) as [Seconds] from " +
" (select case when ISNULL(punch_out, GETDATE())>punch_in " +
" then DATEDIFF(MINUTE, punch_in, ISNULL(punch_out, GETDATE())) else 0 end as 'Duration' " +
" from employee_details_table where user_id=" + user_id + " and MONTH(Punch_date)=MONTH(CONVERT(date, getdate())) ) k";
DataTable dt = GetInDatatable(str);
if (dt.Rows.Count > 0)
{
DateTime dtStartDate = DateTime.MinValue.AddMinutes(Convert.ToInt32(dt.Rows[0][0])).AddSeconds(Convert.ToInt32(dt.Rows[0][1]));
double minutes = Convert.ToDouble(Convert.ToInt32(dt.Rows[0][0]));
var result = TimeSpan.FromMinutes(minutes);
var hours = (int)result.TotalHours; //get hours
var minutes1 = result.Minutes.ToString("00"); //get minutes
CurrentMonthWorkingHours = hours + ":" + minutes1+":"+dt.Rows[0][1].ToString();
}
return CurrentMonthWorkingHours;
}
public string GetALLHours(string user_id)
{
string Allworkinghors = "0";
string str = " select isnull(sum(Duration),0) as [Hrs],DatePart(Second,getdate()) as [Seconds] from " +
" (select case when ISNULL(punch_out, GETDATE())>punch_in " +
" then DATEDIFF(MINUTE, punch_in, ISNULL(punch_out, GETDATE())) else 0 end as 'Duration' " +
" from employee_details_table where user_id=" + user_id + ") k";
DataTable dt = GetInDatatable(str);
if (dt.Rows.Count > 0)
{
DateTime dtStartDate = DateTime.MinValue.AddMinutes(Convert.ToInt32(dt.Rows[0][0])).AddSeconds(Convert.ToInt32(dt.Rows[0][1]));
double minutes = Convert.ToDouble(Convert.ToInt32(dt.Rows[0][0]));
var result = TimeSpan.FromMinutes(minutes);
var hours = (int)result.TotalHours; //get hours
var minutes1 = result.Minutes.ToString("00"); //get minutes
Allworkinghors = hours + ":" + minutes1 + ":" + dt.Rows[0][1].ToString();
}
return Allworkinghors;
}
public string GetYeaseterdayHours(string user_id)
{
string yeasterdayhrs = "0";
string str = " select isnull(sum(Duration),0) as [Hrs] from " +
" (select case when ISNULL(punch_out, GETDATE())>punch_in " +
" then DATEDIFF(MINUTE, punch_in, ISNULL(punch_out, GETDATE())) else 0 end as 'Duration' " +
" from employee_details_table where user_id=" + user_id + " and Punch_date=DATEADD(day, -1, CONVERT(date, getdate()))) k";
DataTable dt = GetInDatatable(str);
if (dt.Rows.Count > 0)
{
DateTime dtStartDate = DateTime.MinValue.AddMinutes(Convert.ToInt32(dt.Rows[0][0]));
yeasterdayhrs = dtStartDate.ToString("HH:mm:ss", CultureInfo.InvariantCulture);
}
return yeasterdayhrs;
}
public string LastServerUpdate()
{
string update_time = "";
try
{
string str = "select LastUpdated_time from Update_Settings";
DataTable dt = GetInDatatable(str);
if (dt.Rows.Count > 0)
{
update_time = dt.Rows[0][0].ToString();
DateTime dts = Convert.ToDateTime(update_time);
update_time = dts.ToString("dd/MMM/yyyy hh:mm tt", CultureInfo.InvariantCulture);
}
}
catch
{
}
return update_time;
}
#endregion
public DataTable GetUserDetails(string user_id)
{
DataTable ds = new DataTable();
using (SqlConnection con = new SqlConnection(GetConnectionString()))
{
con.Open();
try
{
SqlCommand cmd = new SqlCommand("authenticationcheck", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("id", user_id);
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(ds);
if (ds.Rows.Count > 0)
{
return ds;
}
}
catch (Exception)
{
throw;
}
}
return ds;
}
public void SaveProfile(string user_id,string emaiid,bool logintype,string pwd)
{
int type = 0;
if(logintype==true)
{
type = 1;
}
string str = "update employee_table set email_id='" + emaiid + "',Login_type=" + type + ",user_password='" + pwd + "' where user_id=" + user_id + " ";
ExecuteQuery(str);
}
public DataTable GetCalendarData(string user_id,string monthdatetime)
{
string str = " select isnull(sum(Duration),0) as [Hrs],DatePart(DAY,Punch_date) as Day,DatePart(MONTH,Punch_date) as Month ,Punch_date from " +
"(select Punch_date,case when ISNULL(punch_out, GETDATE())>punch_in " +
" then DATEDIFF(MINUTE, punch_in, ISNULL(punch_out, GETDATE())) else 0 end as 'Duration' " +
" from employee_details_table where user_id=" + user_id + " and MONTH(Punch_date)=MONTH(CONVERT(date, '" + monthdatetime + "')) ) k" +
" group by Punch_date";
DataTable dt = GetInDatatable(str);
return dt;
}
public DataTable GetNotifications(string user_id)
{
string str = "select Date_missed from Notification where USER_ID=" + user_id + "";
DataTable dt = GetInDatatable(str);
return dt;
}
public string GetCurrentstatus(string user_id)
{
string current_status = "";
string str = "select next_status from employee_table where user_id="+user_id+" ";
DataTable dt = GetInDatatable(str);
if(dt.Rows.Count>0)
{
int next_status = Convert.ToInt32(dt.Rows[0][0]);
if(next_status==1)
{
current_status = "IN";
}
else
{
current_status = "OUT";
}
}
return current_status;
}
public string SearchTotalTime(string user_id, string startdt, string enddt)
{
string duration = "";
try
{
string str = " select sum(Duration) as Duration " +
" from (select ID,user_id,punch_in ,ISNULL(punch_out, GETDATE()) as punch_out " +
" , case when ISNULL(punch_out, GETDATE())>punch_in " +
" then DATEDIFF(MINUTE, punch_in, ISNULL(punch_out, GETDATE())) else 0 end as 'Duration' " +
" from employee_details_table where user_id=" + user_id + " and Punch_date >='" + startdt + "' " +
" and Punch_date <= '" + enddt + "')k ";
DataTable dt = GetInDatatable(str);
if (dt.Rows.Count > 0)
{
DateTime dtStartDate = DateTime.MinValue.AddMinutes(Convert.ToInt32(dt.Rows[0][0]));
duration = dtStartDate.ToString("HH:mm:ss", CultureInfo.InvariantCulture);
//duration = dt.Rows[0][0].ToString();
}
}
catch
{
}
return duration;
}
public DataTable GetALLUsersDetails()
{
string str = "";
DataTable dt = null;
try
{
//str = "select u.user_id,u.user_name username,max(d.punch_in) 'in',max(d.punch_out) 'out' ," +
// " (select sum(DATEDIFF(MINUTE, punch_in, ISNULL(punch_out, GETDATE()))) Duration" +
// " from employee_details_table where user_id=u.user_id and Punch_date=CONVERT(date, getdate()))Duration,u.next_status " +
// " from employee_details_table d right outer join employee_table u on d.user_id=u.user_id" +
// " group by u.user_id,user_name,u.next_status order by u.user_name";
str = "select u.user_id,u.user_name username,max(d.punch_in) 'in',max(d.punch_out) 'out' ," +
" (select sum(DATEDIFF(MINUTE, punch_in, ISNULL(punch_out, GETDATE()))) Duration" +
" from employee_details_table where user_id=u.user_id )Duration,u.next_status " +
" from employee_details_table d right outer join employee_table u on d.user_id=u.user_id" +
" group by u.user_id,user_name,u.next_status order by u.user_name";
dt = GetInDatatable(str);
}
catch
{
}
return dt;
}
public void RecalculateAll()
{
try
{
//delete all data from tables
string str1 = "";
str1 = "delete from employee_details_table";
string str2 = "update employee_table set next_status=0";
string str3 = "update Update_Settings set LastUpdated_time='1999-06-07 15:33:45.710' ,Updated_date='1999-06-07',Last_upadted_Record=0";
string str4 = "delete from Notification";
ExecuteQuery(str1);
ExecuteQuery(str2);
ExecuteQuery(str3);
ExecuteQuery(str4);
RecaluteFrombeging();
}
catch (Exception ex)
{
}
}
public void RecaluteFrombeging()
{
string str = "select Table_name,table_time from Server_tables order by table_time asc";
DataTable DT_Tables = GetInDatatable(str);
if (DT_Tables.Rows.Count > 0)
{
for (int i = 0; i < DT_Tables.Rows.Count; i++)
{
try
{
string str3 = "update Update_Settings set LastUpdated_time='1999-06-07 15:33:45.710' ,Updated_date='1999-06-07',Last_upadted_Record=0";
string str2 = "update employee_table set next_status=0";
ExecuteQuery(str3);
ExecuteQuery(str2);
string qry = GetWhereQuery();
string table_name = DT_Tables.Rows[i]["Table_name"].ToString();
qry = " " + table_name + " " + qry;
WebReference.Service objmainsrv = new WebReference.Service();
DataTable dt = objmainsrv.GetLatestData(qry);
InsertData(dt);
}
catch (Exception ex)
{
}
}
}
}
public void RecaluteCurrentmonth()
{
string str = "select top 1 Table_name,table_time from Server_tables order by table_time desc";
DataTable DT_Tables = GetInDatatable(str);
if (DT_Tables.Rows.Count > 0)
{
for (int i = 0; i < DT_Tables.Rows.Count; i++)
{
try
{
//string str3 = "update Update_Settings set LastUpdated_time='1999-06-07 15:33:45.710' ,Updated_date='1999-06-07',Last_upadted_Record=0";
string str2 = "update employee_table set next_status=0";
//ExecuteQuery(str3);
ExecuteQuery(str2);
string qry = GetWhereQuery();
string table_name = DT_Tables.Rows[i]["Table_name"].ToString();
qry = " " + table_name + " " + qry;
WebReference.Service objmainsrv = new WebReference.Service();
DataTable dt = objmainsrv.GetLatestData(qry);
InsertData(dt);
}
catch (Exception ex)
{
}
}
}
}
public void InsertData(DataTable dt)
{
TimeZoneInfo timeZoneInfo;
DateTime dateTime;
//Set the time zone GMT-12:00) International Date Line West
timeZoneInfo = TimeZoneInfo.FindSystemTimeZoneById("Dateline Standard Time");
string LastUpdated_date = "";
string user_id = "";
int user_status = 0;
string date = "";
string maxval = "";
DateTime Todays_Date = DateTime.Now;
string strdate = Todays_Date.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture);
for (int i = 0; i < dt.Rows.Count; i++)
{
string qry1 = "select Updated_date from Update_Settings";
DataTable Dt_LastUpdated = GetInDatatable(qry1);
if (Dt_LastUpdated.Rows.Count > 0)//last update date
{
LastUpdated_date = Dt_LastUpdated.Rows[0]["Updated_date"].ToString();
DateTime LastUpdated_dt_date = Convert.ToDateTime(LastUpdated_date);
LastUpdated_date = LastUpdated_dt_date.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture);
//LastUpdated_dt_date = TimeZoneInfo.ConvertTime(Convert.ToDateTime(date), timeZoneInfo);
user_id = dt.Rows[i]["UserId"].ToString();
date = dt.Rows[i]["LogDate"].ToString();
DateTime start_date = Convert.ToDateTime(date);
//Get date and time in (GMT-12:00) International Date Line West
start_date = TimeZoneInfo.ConvertTime(Convert.ToDateTime(start_date), timeZoneInfo);
string DT_dateonly = start_date.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture);
date = start_date.ToString("yyyy-MM-dd HH:mm", CultureInfo.InvariantCulture);
if (LastUpdated_dt_date.Date < start_date.Date)//new day
{
UpdateStatusToIN();//update all employee statatus as 0
DeleteUnwantedRecords(LastUpdated_date);
}
user_status = CheckUserStatus(Convert.ToInt32(user_id));
maxval = dt.Rows[i][2].ToString();
AddRecord(user_status, user_id, date, maxval);
string str = "update Update_Settings set Updated_date=CONVERT(date, '" + date + "'),LastUpdated_time=GETDATE()";
ExecuteQuery(str);
}
}
}
public void UpdateStatusToIN()
{
string str = "update employee_table set next_status=0";
ExecuteQuery(str);
}
public int CheckUserStatus(int user_id)
{
int retString = 0;
string str3 = "select next_status from employee_table where user_id=" + user_id + "";
DataTable dt = GetInDatatable(str3);
if (dt.Rows.Count > 0)
{
retString = Convert.ToInt32(dt.Rows[0][0].ToString());
}
return retString;
}
public void DeleteUnwantedRecords(string LastUpdated_date)
{
try
{
string str = "select user_id,Punch_date from employee_details_table where Punch_date=CONVERT(date, '" + LastUpdated_date + "') and punch_out is null";
DataTable dt = GetInDatatable(str);
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
string str2 = "insert into Notification(User_id,Date_missed) values(" + dt.Rows[i]["user_id"].ToString() + ", CONVERT(date, '" + LastUpdated_date + "'))";
ExecuteQuery(str2);
}
}
}
catch (Exception ex)
{
}
}
public void AddRecord(int val, string user_id, string punchdate, string maxval)
{
DateTime start_date = Convert.ToDateTime(punchdate);
punchdate = start_date.ToString("yyyy-MM-dd HH:mm", CultureInfo.InvariantCulture);
if (val == 0)//insert
{
string str = "";
str = "insert into employee_details_table (user_id,punch_in,Punch_date,edited_user) values(" + user_id + ",'" + punchdate + "',CONVERT(date, '" + punchdate + "')," + 1 + ")";
ExecuteQuery(str);
string str3 = "";
str3 = "update employee_table set next_status=1 where user_id=" + user_id + "";
ExecuteQuery(str3);
}
else //update
{
string str1 = "select id from employee_details_table where user_id=" + user_id + " and punch_in=(select max(punch_in) from employee_details_table where user_id=" + user_id + " )";
DataTable dt = GetInDatatable(str1);
if (dt.Rows.Count > 0)
{
string id = dt.Rows[0][0].ToString();
string str2 = "update employee_details_table set punch_out='" + punchdate + "' where id=" + id + "";
ExecuteQuery(str2);
string str3 = "";
str3 = "update employee_table set next_status=0 where user_id=" + user_id + "";
ExecuteQuery(str3);
}
}
UpdateSqlSettings(maxval);
}
public void UpdateSqlSettings(string lastupdateval)
{
string str = "";
int status = 0;
string str2 = " select case " +
" when Last_upadted_Record >" + lastupdateval + " then 0 " +
" else 1 end from Update_Settings";
DataTable dt = GetInDatatable(str2);
if (dt.Rows.Count > 0)
{
status = Convert.ToInt32(dt.Rows[0][0]);
}
if (status == 1)
{
str = "update Update_Settings set LastUpdated_time=GETDATE(),Last_upadted_Record=" + lastupdateval + "";
ExecuteQuery(str);
}
else
{
str = "update Update_Settings set Updated_date=CONVERT(date, getdate()),LastUpdated_time=GETDATE()";
ExecuteQuery(str);
}
}
public string GetWhereQuery()
{
string whereCondition = "";
whereCondition = " Where ";
string today_date = "";
string updated_date = "";
int status = 0;
int Last_upadted_Record = 0;
string str = " select Updated_date,case when Updated_date=CONVERT(date, getdate()) then 0 else 1 end status,Last_upadted_Record,CONVERT(date, getdate()) Today from Update_Settings";
DataTable dt = GetInDatatable(str);
if (dt.Rows.Count > 0)
{
updated_date = dt.Rows[0]["Updated_date"].ToString();
today_date = dt.Rows[0]["Today"].ToString();
status = Convert.ToInt32(dt.Rows[0]["status"]);
Last_upadted_Record = Convert.ToInt32(dt.Rows[0]["Last_upadted_Record"]);
DateTime start_date = Convert.ToDateTime(updated_date);
updated_date = start_date.ToString("yyyy-MM-dd HH:mm", CultureInfo.InvariantCulture);
DateTime start_date1 = Convert.ToDateTime(today_date);
today_date = start_date1.ToString("yyyy-MM-dd HH:mm", CultureInfo.InvariantCulture);
}
whereCondition = whereCondition + " DeviceLogId>" + Last_upadted_Record + "";
return whereCondition;
}
public string GetActiveTablename()
{
string returnval = "";
string str2 = "select Table_name from Server_tables order by table_time desc";
DataTable dt1 = GetInDatatable(str2);
if (dt1.Rows.Count > 0)
{
returnval = dt1.Rows[0][0].ToString();
}
return returnval;
}
public void InsertNewTable(string table_name, string TableDate)
{
string str = "";
str = "insert into Server_tables (Table_name,table_time) values('" + table_name + "','" + TableDate + "')";
ExecuteQuery(str);
string str2 = "update employee_table set next_status=0";
string str3 = "update Update_Settings set Last_upadted_Record=0";
ExecuteQuery(str2);
ExecuteQuery(str3);
}
public DataTable GetUsersDetails(string user_id)
{
string str = "select user_name,Convert(Varchar,user_id)+'.jpg' as user_id,role,user_id as recvrid from employee_table where user_id<>" + user_id + "";
DataTable dt = GetInDatatable(str);
return dt;
}
public DataTable GetLatestChats(string user_id1,string user_id2)
{
DataTable dt = null;
try
{
if (user_id1.Contains(".jpg"))
{
user_id1 = user_id1.Replace(".jpg", "");
}
string str = "";
//str = " select Top 1 e.user_name,c.message,c.msg_date from chats " +
// " c join employee_table e on e.user_id=c.user_id1 where " +
// " (user_id1=" + user_id1 + " and user_id2=" + user_id2 + ") " +
// " or (user_id1=" + user_id2 + " and user_id2=" + user_id1 + ") " +
// " and " +
// " id not in(select c.id from chats c join employee_table e on " +
// " e.user_id=c.user_id1 where (user_id1=" + user_id1 + " and user_id2=" + user_id2 + ") or (user_id1=" + user_id2 + " and user_id2=" + user_id1 + ") )";
str = " select case e.user_id when "+user_id2+""+
" then 1 else 0 end as Status,Convert(Varchar,user_id)+'.jpg' as image_id,e.user_name,c.message,c.msg_date from chats c join employee_table e on e.user_id=c.user_id1 where (user_id1=" + user_id1 + " and user_id2=" + user_id2 + ") or (user_id1=" + user_id2 + " and user_id2=" + user_id1 + ") order by msg_date asc";
//str = "select message,msg_date from chats where (user_id1=" + user_id1 + " and user_id2=" + user_id2 + ") or (user_id1=" + user_id2 + " and user_id2=" + user_id1 + ") order by msg_date desc";
dt = GetInDatatable(str);
}
catch(Exception ex)
{
}
return dt;
}
public string GetLatestSigleChat(string user_id1,string user_id2)
{
string latest_msg="";
string str= " select Top 1 e.user_name,c.message,c.msg_date from chats "+
" c join employee_table e on e.user_id=c.user_id1 where "+
" (user_id1="+user_id1+" and user_id2="+user_id2+") "+
" or (user_id1="+user_id2+" and user_id2="+user_id1+") "+
" order by msg_date desc";
DataTable dt = GetInDatatable(str);
if(dt.Rows.Count>0)
{
latest_msg=dt.Rows[0]["message"].ToString();
}
return latest_msg;
}
public void InsertChat(string userID1,string userID2,string msg)
{
//str = "select e.user_name,c.message,c.msg_date from chats c join employee_table e on e.user_id=c.user_id1 where (user_id1=" + user_id1 + " and user_id2=" + user_id2 + ") or (user_id1=" + user_id2 + " and user_id2=" + user_id1 + ") order by msg_date desc";
string str = "insert into chats (user_id1,user_id2,message,msg_date,Read_status) values(" + userID1 + "," + userID2 + ",'" + msg + "',CURRENT_TIMESTAMP,0)";
ExecuteQuery(str);
}
public DataTable GetChatUser(string user_id)
{
string str = "";
str = " select e.user_name,Convert(Varchar,user_id)+'.jpg' as image_id,e.user_id,"+
" (select count(Read_status) from chats where Read_status=0 and user_id1=e.user_id and user_id2="+user_id+") as read_status from employee_table e left join chats c " +
" on c.user_id2=e.user_id " +
" where e.user_id<>" + user_id + " " +
" group by user_name,user_id";
DataTable dt = GetInDatatable(str);
return dt;
}
public DataTable getFirstEmployeeID(string user_id)
{
string str = "";
str = "select top 1 user_id,user_name from employee_table where user_id<>" + user_id + " order by user_id ";
DataTable dt = GetInDatatable(str);
return dt;
}
public void updateStatus(string user_id,string longin_user_id)
{
string str = "";
str = "update chats set Read_status=1 where user_id1=" + user_id + " and user_id2=" + longin_user_id + " ";
ExecuteQuery(str);
}
public int GetTotalUnreadedMessages(string user_id)
{
int Meaasges = 0;
try
{
string str = " select sum(read_status) " +
" from( select e.user_name,Convert(Varchar,user_id)+'.jpg' as image_id,e.user_id, " +
" (select count(Read_status) from chats where Read_status=0 and user_id1=e.user_id and user_id2=" + user_id + ") as read_status from employee_table e left join chats c " +
" on c.user_id2=e.user_id " +
" where e.user_id<>" + user_id + " " +
" group by user_name,user_id)k";
DataTable dt = GetInDatatable(str);
if (dt.Rows.Count > 0)
{
Meaasges = Convert.ToInt32(dt.Rows[0][0]);
}
}
catch(Exception ex)
{
}
return Meaasges;
}
public bool access(string user_id)
{
bool status = false;
string str = "select user_name from employee_table where user_id=" + user_id + " and master_admin=1 ";
DataTable dt = GetInDatatable(str);
if (dt.Rows.Count > 0)
{
status = true;
}
return status;
}
public DataTable GetPresentUserlist()
{
string str = "select Convert(Varchar,u.user_id)+'.jpg' as image_id,u.email_id," +
" u.user_id,u.user_name user_name,max(d.punch_in) 'punch_in'," +
" case next_status when 1 then 'IN' else 'OUT' end Status ," +
" max(d.punch_out) 'punch_out' ," +
" (select sum(DATEDIFF(MINUTE, punch_in, ISNULL(punch_out, GETDATE()))) Duration" +
" from employee_details_table where user_id=u.user_id and" +
" Punch_date=CONVERT(date, getdate()))Duration,u.next_status " +
" from employee_details_table d right outer join employee_table u" +
" on d.user_id=u.user_id where d. Punch_date=CONVERT(date, getdate())" +
" group by u.user_id,user_name,u.next_status,email_id order by u.user_name";
DataTable dt = GetInDatatable(str);
return dt;
}
public DataTable GetEmailSettings()
{
string str = "";
str = "select * from Email_settings";
DataTable dt = GetInDatatable(str);
return dt;
}
}
}
using System.Collections.Generic;
using System.Linq;
using System.Web;
using EmployeeMonitorMVc.Models.Repository;
using Dapper;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.Globalization;
namespace EmployeeMonitorMVc.Models.Repository
{
public class DatabseOperations : IDatabseOperations
{
string ConnectionString = "";
public string GetConnectionString()
{
//ConnectionString = "Data Source=JYOTHIS-PC\\SQLEXPRESS;Initial Catalog=CurrentwareEmployeeMonitor;User Id=sa; password=123;";
return ConnectionString;
}
public DataTable GetInDatatable(string str)
{
GetConnectionString();
DataTable dt1 = null;
dt1 = new DataTable();
SqlDataAdapter ad = new SqlDataAdapter(str, ConnectionString);
ad.Fill(dt1);
return dt1;
}
public void ExecuteQuery(string str)
{
GetConnectionString();
SqlConnection con = new SqlConnection(ConnectionString);
con.Open();
SqlCommand cmd = new SqlCommand(str, con);
cmd.ExecuteNonQuery();
con.Close();
}
/// <summary>
/// For getting menu settings based on user id
/// </summary>
/// <param name="user_id"></param>
/// <returns></returns>
public IEnumerable<Menulist> GetMenuList(string user_id)
{
using (SqlConnection con = new SqlConnection(GetConnectionString()))
{
var para = new DynamicParameters();
para.Add("@type", "");
return con.Query<Menulist>("Get_MenuList", para, null, true, 0, CommandType.StoredProcedure).ToList();
}
}
public void AddTomodificationTable(string user_id,string comments,string action,string editor_id)
{
string str = "insert into Modifications (user_id,comments,Action,Editor_id,edited_date) values(" + user_id + ",'" + comments + "'," + action + "," + editor_id + ",getdate())";
ExecuteQuery(str);
}
public IEnumerable<ChatUsers> GetChatUsers(string user_id)
{
using (SqlConnection con = new SqlConnection(GetConnectionString()))
{
var para = new DynamicParameters();
para.Add("@user_id", user_id);
return con.Query<ChatUsers>("Getchatusers", para, null, true, 0, CommandType.StoredProcedure).ToList();
}
}
public string getusername(string user_id)
{
string username = "";
string str = "select user_name from employee_table where user_id=" + user_id + "";
DataTable dt = GetInDatatable(str);
if(dt.Rows.Count>0)
{
username = dt.Rows[0][0].ToString();
}
return username;
}
/// <summary>
/// for getting user timings based on usedd id and date range
/// </summary>
/// <param name="user_id"></param>
/// <param name="startdt"></param>
/// <param name="enddt"></param>
/// <returns></returns>
public IEnumerable<GetUserSummary> UserSummaryTimings(string user_id, string startdt, string enddt)
{
using (SqlConnection con = new SqlConnection(GetConnectionString()))
{
var para = new DynamicParameters();
para.Add("user_id", Convert.ToInt32(user_id));
para.Add("start_date", Convert.ToDateTime(startdt));
para.Add("end_date", Convert.ToDateTime(enddt));
return con.Query<GetUserSummary>("Get_userTimings", para, null, true, 0, CommandType.StoredProcedure).ToList();
//con.Execute("sprocPaymentDetailsInsertUpdateSingleItem", paramater, null, 0, CommandType.StoredProcedure);
}
}
public IEnumerable<GetAllUserData> ALLUserSummaryTimings()
{
using (SqlConnection con = new SqlConnection(GetConnectionString()))
{
var para = new DynamicParameters();
return con.Query<GetAllUserData>("Get_Alluser_Data", para, null, true, 0, CommandType.StoredProcedure).ToList();
}
}
public IEnumerable<ChatContent> GetChatData(string user_id1, string user_id2)
{
using (SqlConnection con = new SqlConnection(GetConnectionString()))
{
var para = new DynamicParameters();
para.Add("user_id1", Convert.ToInt32(user_id1));
para.Add("user_id2", Convert.ToInt32(user_id2));
return con.Query<ChatContent>("Get_ChatData", para, null, true, 0, CommandType.StoredProcedure).ToList();
//con.Execute("sprocPaymentDetailsInsertUpdateSingleItem", paramater, null, 0, CommandType.StoredProcedure);
}
}
public DataSet GetUserProfile(string user_id)
{
using (SqlConnection con = new SqlConnection(GetConnectionString()))
{
con.Open();
DataSet ds = new DataSet();
try
{
SqlCommand cmd = new SqlCommand("get_profile_details", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("user_id", user_id);
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(ds);
if (ds.Tables.Count > 0)
{
return ds;
}
else
{
return ds = null;
}
}
catch (Exception)
{
throw;
}
finally
{
ds.Dispose();
}
}
}
#region layot page
public string GetLastUpdatedTime()
{
string upadted_time = "";
string str = "";
str = "select menu from menu_table where id=1";
SqlConnection con = new SqlConnection(GetConnectionString());
con.Open();
SqlCommand cmd = new SqlCommand(str, con);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
upadted_time = rdr["menu"].ToString();
}
con.Close();
return upadted_time;
}
public int GetNotificationCount(string user_id)
{
int Notification_count = 0;
string str = "select count(User_id) from Notification where User_id=" + user_id + " ";
DataTable dt=GetInDatatable(str);
if(dt.Rows.Count>0)
{
Notification_count = Convert.ToInt32(dt.Rows[0][0].ToString());
}
return Notification_count;
}
public int GetMessageNotificationCount(string user_id)
{
int Notification_count = 0;
string str = "select count(user_id) from employee_table where User_id=" + user_id + " and email_send_date=getdate()";
DataTable dt = GetInDatatable(str);
if (dt.Rows.Count > 0)
{
Notification_count = Convert.ToInt32(dt.Rows[0][0].ToString());
}
return Notification_count;
}
public string PredictedLeavingTime(string user_id)
{
string PredictedTime = "0";
string str = " select isnull(sum(Duration),0) as [Hrs],DatePart(Second,getdate()) as [Seconds] from " +
" (select case when ISNULL(punch_out, GETDATE())>punch_in " +
" then DATEDIFF(MINUTE, punch_in, ISNULL(punch_out, GETDATE())) else 0 end as 'Duration' " +
" from employee_details_table where user_id=" + user_id + " and Punch_date=CONVERT(date, getdate()) ) k";
DataTable dt = GetInDatatable(str);
if (dt.Rows.Count > 0)
{
int target = 480;
int current = Convert.ToInt32(dt.Rows[0][0]);
if (current >= target)
{
PredictedTime = "Congrats you completed 8 Hrs Today";
}
else if (current>0)
{
int Remaining = target - current;
DateTime dtStartDate = DateTime.Now.AddMinutes(Remaining);
PredictedTime = "8 Hrs Will Complete at "+dtStartDate.ToString("dd/MMM/yyyy hh:mm tt", CultureInfo.InvariantCulture);
}
else if(current==0)
{
PredictedTime = "";
}
}
return PredictedTime;
}
public string GetTodaysHoursForFireWorks(string user_id)
{
string TodayWorkingHours = "0";
string str = " select isnull(sum(Duration),0) as [Hrs],DatePart(Second,getdate()) as [Seconds] from " +
" (select case when ISNULL(punch_out, GETDATE())>punch_in " +
" then DATEDIFF(MINUTE, punch_in, ISNULL(punch_out, GETDATE())) else 0 end as 'Duration' " +
" from employee_details_table where user_id=" + user_id + " and Punch_date=CONVERT(date, getdate()) ) k";
DataTable dt = GetInDatatable(str);
if (dt.Rows.Count > 0)
{
TodayWorkingHours = dt.Rows[0][0].ToString();
}
return TodayWorkingHours;
}
public string GetTodaysHours(string user_id)
{
// elect sum(Duration / 60 + (Duration % 60) / 100.0) as [Hrs],DatePart(Second,getdate()) as [Seconds] from " +
string TodayWorkingHours = "0";
string str = " select isnull(sum(Duration),0) as [Hrs],DatePart(Second,getdate()) as [Seconds] from " +
" (select case when ISNULL(punch_out, GETDATE())>punch_in " +
" then DATEDIFF(MINUTE, punch_in, ISNULL(punch_out, GETDATE())) else 0 end as 'Duration' " +
" from employee_details_table where user_id=" + user_id + " and Punch_date=CONVERT(date, getdate()) ) k";
DataTable dt = GetInDatatable(str);
if (dt.Rows.Count > 0)
{
DateTime dtStartDate = DateTime.MinValue.AddMinutes(Convert.ToInt32(dt.Rows[0][0])).AddSeconds(Convert.ToInt32(dt.Rows[0][1]));
TodayWorkingHours = dtStartDate.ToString("HH:mm:ss", CultureInfo.InvariantCulture);
}
return TodayWorkingHours;
}
public string GetMonthHours(string user_id)
{
string CurrentMonthWorkingHours = "0";
string str = " select isnull(sum(Duration),0) as [Hrs],DatePart(Second,getdate()) as [Seconds] from " +
" (select case when ISNULL(punch_out, GETDATE())>punch_in " +
" then DATEDIFF(MINUTE, punch_in, ISNULL(punch_out, GETDATE())) else 0 end as 'Duration' " +
" from employee_details_table where user_id=" + user_id + " and MONTH(Punch_date)=MONTH(CONVERT(date, getdate())) ) k";
DataTable dt = GetInDatatable(str);
if (dt.Rows.Count > 0)
{
DateTime dtStartDate = DateTime.MinValue.AddMinutes(Convert.ToInt32(dt.Rows[0][0])).AddSeconds(Convert.ToInt32(dt.Rows[0][1]));
double minutes = Convert.ToDouble(Convert.ToInt32(dt.Rows[0][0]));
var result = TimeSpan.FromMinutes(minutes);
var hours = (int)result.TotalHours; //get hours
var minutes1 = result.Minutes.ToString("00"); //get minutes
CurrentMonthWorkingHours = hours + ":" + minutes1+":"+dt.Rows[0][1].ToString();
}
return CurrentMonthWorkingHours;
}
public string GetALLHours(string user_id)
{
string Allworkinghors = "0";
string str = " select isnull(sum(Duration),0) as [Hrs],DatePart(Second,getdate()) as [Seconds] from " +
" (select case when ISNULL(punch_out, GETDATE())>punch_in " +
" then DATEDIFF(MINUTE, punch_in, ISNULL(punch_out, GETDATE())) else 0 end as 'Duration' " +
" from employee_details_table where user_id=" + user_id + ") k";
DataTable dt = GetInDatatable(str);
if (dt.Rows.Count > 0)
{
DateTime dtStartDate = DateTime.MinValue.AddMinutes(Convert.ToInt32(dt.Rows[0][0])).AddSeconds(Convert.ToInt32(dt.Rows[0][1]));
double minutes = Convert.ToDouble(Convert.ToInt32(dt.Rows[0][0]));
var result = TimeSpan.FromMinutes(minutes);
var hours = (int)result.TotalHours; //get hours
var minutes1 = result.Minutes.ToString("00"); //get minutes
Allworkinghors = hours + ":" + minutes1 + ":" + dt.Rows[0][1].ToString();
}
return Allworkinghors;
}
public string GetYeaseterdayHours(string user_id)
{
string yeasterdayhrs = "0";
string str = " select isnull(sum(Duration),0) as [Hrs] from " +
" (select case when ISNULL(punch_out, GETDATE())>punch_in " +
" then DATEDIFF(MINUTE, punch_in, ISNULL(punch_out, GETDATE())) else 0 end as 'Duration' " +
" from employee_details_table where user_id=" + user_id + " and Punch_date=DATEADD(day, -1, CONVERT(date, getdate()))) k";
DataTable dt = GetInDatatable(str);
if (dt.Rows.Count > 0)
{
DateTime dtStartDate = DateTime.MinValue.AddMinutes(Convert.ToInt32(dt.Rows[0][0]));
yeasterdayhrs = dtStartDate.ToString("HH:mm:ss", CultureInfo.InvariantCulture);
}
return yeasterdayhrs;
}
public string LastServerUpdate()
{
string update_time = "";
try
{
string str = "select LastUpdated_time from Update_Settings";
DataTable dt = GetInDatatable(str);
if (dt.Rows.Count > 0)
{
update_time = dt.Rows[0][0].ToString();
DateTime dts = Convert.ToDateTime(update_time);
update_time = dts.ToString("dd/MMM/yyyy hh:mm tt", CultureInfo.InvariantCulture);
}
}
catch
{
}
return update_time;
}
#endregion
public DataTable GetUserDetails(string user_id)
{
DataTable ds = new DataTable();
using (SqlConnection con = new SqlConnection(GetConnectionString()))
{
con.Open();
try
{
SqlCommand cmd = new SqlCommand("authenticationcheck", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("id", user_id);
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(ds);
if (ds.Rows.Count > 0)
{
return ds;
}
}
catch (Exception)
{
throw;
}
}
return ds;
}
public void SaveProfile(string user_id,string emaiid,bool logintype,string pwd)
{
int type = 0;
if(logintype==true)
{
type = 1;
}
string str = "update employee_table set email_id='" + emaiid + "',Login_type=" + type + ",user_password='" + pwd + "' where user_id=" + user_id + " ";
ExecuteQuery(str);
}
public DataTable GetCalendarData(string user_id,string monthdatetime)
{
string str = " select isnull(sum(Duration),0) as [Hrs],DatePart(DAY,Punch_date) as Day,DatePart(MONTH,Punch_date) as Month ,Punch_date from " +
"(select Punch_date,case when ISNULL(punch_out, GETDATE())>punch_in " +
" then DATEDIFF(MINUTE, punch_in, ISNULL(punch_out, GETDATE())) else 0 end as 'Duration' " +
" from employee_details_table where user_id=" + user_id + " and MONTH(Punch_date)=MONTH(CONVERT(date, '" + monthdatetime + "')) ) k" +
" group by Punch_date";
DataTable dt = GetInDatatable(str);
return dt;
}
public DataTable GetNotifications(string user_id)
{
string str = "select Date_missed from Notification where USER_ID=" + user_id + "";
DataTable dt = GetInDatatable(str);
return dt;
}
public string GetCurrentstatus(string user_id)
{
string current_status = "";
string str = "select next_status from employee_table where user_id="+user_id+" ";
DataTable dt = GetInDatatable(str);
if(dt.Rows.Count>0)
{
int next_status = Convert.ToInt32(dt.Rows[0][0]);
if(next_status==1)
{
current_status = "IN";
}
else
{
current_status = "OUT";
}
}
return current_status;
}
public string SearchTotalTime(string user_id, string startdt, string enddt)
{
string duration = "";
try
{
string str = " select sum(Duration) as Duration " +
" from (select ID,user_id,punch_in ,ISNULL(punch_out, GETDATE()) as punch_out " +
" , case when ISNULL(punch_out, GETDATE())>punch_in " +
" then DATEDIFF(MINUTE, punch_in, ISNULL(punch_out, GETDATE())) else 0 end as 'Duration' " +
" from employee_details_table where user_id=" + user_id + " and Punch_date >='" + startdt + "' " +
" and Punch_date <= '" + enddt + "')k ";
DataTable dt = GetInDatatable(str);
if (dt.Rows.Count > 0)
{
DateTime dtStartDate = DateTime.MinValue.AddMinutes(Convert.ToInt32(dt.Rows[0][0]));
duration = dtStartDate.ToString("HH:mm:ss", CultureInfo.InvariantCulture);
//duration = dt.Rows[0][0].ToString();
}
}
catch
{
}
return duration;
}
public DataTable GetALLUsersDetails()
{
string str = "";
DataTable dt = null;
try
{
//str = "select u.user_id,u.user_name username,max(d.punch_in) 'in',max(d.punch_out) 'out' ," +
// " (select sum(DATEDIFF(MINUTE, punch_in, ISNULL(punch_out, GETDATE()))) Duration" +
// " from employee_details_table where user_id=u.user_id and Punch_date=CONVERT(date, getdate()))Duration,u.next_status " +
// " from employee_details_table d right outer join employee_table u on d.user_id=u.user_id" +
// " group by u.user_id,user_name,u.next_status order by u.user_name";
str = "select u.user_id,u.user_name username,max(d.punch_in) 'in',max(d.punch_out) 'out' ," +
" (select sum(DATEDIFF(MINUTE, punch_in, ISNULL(punch_out, GETDATE()))) Duration" +
" from employee_details_table where user_id=u.user_id )Duration,u.next_status " +
" from employee_details_table d right outer join employee_table u on d.user_id=u.user_id" +
" group by u.user_id,user_name,u.next_status order by u.user_name";
dt = GetInDatatable(str);
}
catch
{
}
return dt;
}
public void RecalculateAll()
{
try
{
//delete all data from tables
string str1 = "";
str1 = "delete from employee_details_table";
string str2 = "update employee_table set next_status=0";
string str3 = "update Update_Settings set LastUpdated_time='1999-06-07 15:33:45.710' ,Updated_date='1999-06-07',Last_upadted_Record=0";
string str4 = "delete from Notification";
ExecuteQuery(str1);
ExecuteQuery(str2);
ExecuteQuery(str3);
ExecuteQuery(str4);
RecaluteFrombeging();
}
catch (Exception ex)
{
}
}
public void RecaluteFrombeging()
{
string str = "select Table_name,table_time from Server_tables order by table_time asc";
DataTable DT_Tables = GetInDatatable(str);
if (DT_Tables.Rows.Count > 0)
{
for (int i = 0; i < DT_Tables.Rows.Count; i++)
{
try
{
string str3 = "update Update_Settings set LastUpdated_time='1999-06-07 15:33:45.710' ,Updated_date='1999-06-07',Last_upadted_Record=0";
string str2 = "update employee_table set next_status=0";
ExecuteQuery(str3);
ExecuteQuery(str2);
string qry = GetWhereQuery();
string table_name = DT_Tables.Rows[i]["Table_name"].ToString();
qry = " " + table_name + " " + qry;
WebReference.Service objmainsrv = new WebReference.Service();
DataTable dt = objmainsrv.GetLatestData(qry);
InsertData(dt);
}
catch (Exception ex)
{
}
}
}
}
public void RecaluteCurrentmonth()
{
string str = "select top 1 Table_name,table_time from Server_tables order by table_time desc";
DataTable DT_Tables = GetInDatatable(str);
if (DT_Tables.Rows.Count > 0)
{
for (int i = 0; i < DT_Tables.Rows.Count; i++)
{
try
{
//string str3 = "update Update_Settings set LastUpdated_time='1999-06-07 15:33:45.710' ,Updated_date='1999-06-07',Last_upadted_Record=0";
string str2 = "update employee_table set next_status=0";
//ExecuteQuery(str3);
ExecuteQuery(str2);
string qry = GetWhereQuery();
string table_name = DT_Tables.Rows[i]["Table_name"].ToString();
qry = " " + table_name + " " + qry;
WebReference.Service objmainsrv = new WebReference.Service();
DataTable dt = objmainsrv.GetLatestData(qry);
InsertData(dt);
}
catch (Exception ex)
{
}
}
}
}
public void InsertData(DataTable dt)
{
TimeZoneInfo timeZoneInfo;
DateTime dateTime;
//Set the time zone GMT-12:00) International Date Line West
timeZoneInfo = TimeZoneInfo.FindSystemTimeZoneById("Dateline Standard Time");
string LastUpdated_date = "";
string user_id = "";
int user_status = 0;
string date = "";
string maxval = "";
DateTime Todays_Date = DateTime.Now;
string strdate = Todays_Date.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture);
for (int i = 0; i < dt.Rows.Count; i++)
{
string qry1 = "select Updated_date from Update_Settings";
DataTable Dt_LastUpdated = GetInDatatable(qry1);
if (Dt_LastUpdated.Rows.Count > 0)//last update date
{
LastUpdated_date = Dt_LastUpdated.Rows[0]["Updated_date"].ToString();
DateTime LastUpdated_dt_date = Convert.ToDateTime(LastUpdated_date);
LastUpdated_date = LastUpdated_dt_date.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture);
//LastUpdated_dt_date = TimeZoneInfo.ConvertTime(Convert.ToDateTime(date), timeZoneInfo);
user_id = dt.Rows[i]["UserId"].ToString();
date = dt.Rows[i]["LogDate"].ToString();
DateTime start_date = Convert.ToDateTime(date);
//Get date and time in (GMT-12:00) International Date Line West
start_date = TimeZoneInfo.ConvertTime(Convert.ToDateTime(start_date), timeZoneInfo);
string DT_dateonly = start_date.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture);
date = start_date.ToString("yyyy-MM-dd HH:mm", CultureInfo.InvariantCulture);
if (LastUpdated_dt_date.Date < start_date.Date)//new day
{
UpdateStatusToIN();//update all employee statatus as 0
DeleteUnwantedRecords(LastUpdated_date);
}
user_status = CheckUserStatus(Convert.ToInt32(user_id));
maxval = dt.Rows[i][2].ToString();
AddRecord(user_status, user_id, date, maxval);
string str = "update Update_Settings set Updated_date=CONVERT(date, '" + date + "'),LastUpdated_time=GETDATE()";
ExecuteQuery(str);
}
}
}
public void UpdateStatusToIN()
{
string str = "update employee_table set next_status=0";
ExecuteQuery(str);
}
public int CheckUserStatus(int user_id)
{
int retString = 0;
string str3 = "select next_status from employee_table where user_id=" + user_id + "";
DataTable dt = GetInDatatable(str3);
if (dt.Rows.Count > 0)
{
retString = Convert.ToInt32(dt.Rows[0][0].ToString());
}
return retString;
}
public void DeleteUnwantedRecords(string LastUpdated_date)
{
try
{
string str = "select user_id,Punch_date from employee_details_table where Punch_date=CONVERT(date, '" + LastUpdated_date + "') and punch_out is null";
DataTable dt = GetInDatatable(str);
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
string str2 = "insert into Notification(User_id,Date_missed) values(" + dt.Rows[i]["user_id"].ToString() + ", CONVERT(date, '" + LastUpdated_date + "'))";
ExecuteQuery(str2);
}
}
}
catch (Exception ex)
{
}
}
public void AddRecord(int val, string user_id, string punchdate, string maxval)
{
DateTime start_date = Convert.ToDateTime(punchdate);
punchdate = start_date.ToString("yyyy-MM-dd HH:mm", CultureInfo.InvariantCulture);
if (val == 0)//insert
{
string str = "";
str = "insert into employee_details_table (user_id,punch_in,Punch_date,edited_user) values(" + user_id + ",'" + punchdate + "',CONVERT(date, '" + punchdate + "')," + 1 + ")";
ExecuteQuery(str);
string str3 = "";
str3 = "update employee_table set next_status=1 where user_id=" + user_id + "";
ExecuteQuery(str3);
}
else //update
{
string str1 = "select id from employee_details_table where user_id=" + user_id + " and punch_in=(select max(punch_in) from employee_details_table where user_id=" + user_id + " )";
DataTable dt = GetInDatatable(str1);
if (dt.Rows.Count > 0)
{
string id = dt.Rows[0][0].ToString();
string str2 = "update employee_details_table set punch_out='" + punchdate + "' where id=" + id + "";
ExecuteQuery(str2);
string str3 = "";
str3 = "update employee_table set next_status=0 where user_id=" + user_id + "";
ExecuteQuery(str3);
}
}
UpdateSqlSettings(maxval);
}
public void UpdateSqlSettings(string lastupdateval)
{
string str = "";
int status = 0;
string str2 = " select case " +
" when Last_upadted_Record >" + lastupdateval + " then 0 " +
" else 1 end from Update_Settings";
DataTable dt = GetInDatatable(str2);
if (dt.Rows.Count > 0)
{
status = Convert.ToInt32(dt.Rows[0][0]);
}
if (status == 1)
{
str = "update Update_Settings set LastUpdated_time=GETDATE(),Last_upadted_Record=" + lastupdateval + "";
ExecuteQuery(str);
}
else
{
str = "update Update_Settings set Updated_date=CONVERT(date, getdate()),LastUpdated_time=GETDATE()";
ExecuteQuery(str);
}
}
public string GetWhereQuery()
{
string whereCondition = "";
whereCondition = " Where ";
string today_date = "";
string updated_date = "";
int status = 0;
int Last_upadted_Record = 0;
string str = " select Updated_date,case when Updated_date=CONVERT(date, getdate()) then 0 else 1 end status,Last_upadted_Record,CONVERT(date, getdate()) Today from Update_Settings";
DataTable dt = GetInDatatable(str);
if (dt.Rows.Count > 0)
{
updated_date = dt.Rows[0]["Updated_date"].ToString();
today_date = dt.Rows[0]["Today"].ToString();
status = Convert.ToInt32(dt.Rows[0]["status"]);
Last_upadted_Record = Convert.ToInt32(dt.Rows[0]["Last_upadted_Record"]);
DateTime start_date = Convert.ToDateTime(updated_date);
updated_date = start_date.ToString("yyyy-MM-dd HH:mm", CultureInfo.InvariantCulture);
DateTime start_date1 = Convert.ToDateTime(today_date);
today_date = start_date1.ToString("yyyy-MM-dd HH:mm", CultureInfo.InvariantCulture);
}
whereCondition = whereCondition + " DeviceLogId>" + Last_upadted_Record + "";
return whereCondition;
}
public string GetActiveTablename()
{
string returnval = "";
string str2 = "select Table_name from Server_tables order by table_time desc";
DataTable dt1 = GetInDatatable(str2);
if (dt1.Rows.Count > 0)
{
returnval = dt1.Rows[0][0].ToString();
}
return returnval;
}
public void InsertNewTable(string table_name, string TableDate)
{
string str = "";
str = "insert into Server_tables (Table_name,table_time) values('" + table_name + "','" + TableDate + "')";
ExecuteQuery(str);
string str2 = "update employee_table set next_status=0";
string str3 = "update Update_Settings set Last_upadted_Record=0";
ExecuteQuery(str2);
ExecuteQuery(str3);
}
public DataTable GetUsersDetails(string user_id)
{
string str = "select user_name,Convert(Varchar,user_id)+'.jpg' as user_id,role,user_id as recvrid from employee_table where user_id<>" + user_id + "";
DataTable dt = GetInDatatable(str);
return dt;
}
public DataTable GetLatestChats(string user_id1,string user_id2)
{
DataTable dt = null;
try
{
if (user_id1.Contains(".jpg"))
{
user_id1 = user_id1.Replace(".jpg", "");
}
string str = "";
//str = " select Top 1 e.user_name,c.message,c.msg_date from chats " +
// " c join employee_table e on e.user_id=c.user_id1 where " +
// " (user_id1=" + user_id1 + " and user_id2=" + user_id2 + ") " +
// " or (user_id1=" + user_id2 + " and user_id2=" + user_id1 + ") " +
// " and " +
// " id not in(select c.id from chats c join employee_table e on " +
// " e.user_id=c.user_id1 where (user_id1=" + user_id1 + " and user_id2=" + user_id2 + ") or (user_id1=" + user_id2 + " and user_id2=" + user_id1 + ") )";
str = " select case e.user_id when "+user_id2+""+
" then 1 else 0 end as Status,Convert(Varchar,user_id)+'.jpg' as image_id,e.user_name,c.message,c.msg_date from chats c join employee_table e on e.user_id=c.user_id1 where (user_id1=" + user_id1 + " and user_id2=" + user_id2 + ") or (user_id1=" + user_id2 + " and user_id2=" + user_id1 + ") order by msg_date asc";
//str = "select message,msg_date from chats where (user_id1=" + user_id1 + " and user_id2=" + user_id2 + ") or (user_id1=" + user_id2 + " and user_id2=" + user_id1 + ") order by msg_date desc";
dt = GetInDatatable(str);
}
catch(Exception ex)
{
}
return dt;
}
public string GetLatestSigleChat(string user_id1,string user_id2)
{
string latest_msg="";
string str= " select Top 1 e.user_name,c.message,c.msg_date from chats "+
" c join employee_table e on e.user_id=c.user_id1 where "+
" (user_id1="+user_id1+" and user_id2="+user_id2+") "+
" or (user_id1="+user_id2+" and user_id2="+user_id1+") "+
" order by msg_date desc";
DataTable dt = GetInDatatable(str);
if(dt.Rows.Count>0)
{
latest_msg=dt.Rows[0]["message"].ToString();
}
return latest_msg;
}
public void InsertChat(string userID1,string userID2,string msg)
{
//str = "select e.user_name,c.message,c.msg_date from chats c join employee_table e on e.user_id=c.user_id1 where (user_id1=" + user_id1 + " and user_id2=" + user_id2 + ") or (user_id1=" + user_id2 + " and user_id2=" + user_id1 + ") order by msg_date desc";
string str = "insert into chats (user_id1,user_id2,message,msg_date,Read_status) values(" + userID1 + "," + userID2 + ",'" + msg + "',CURRENT_TIMESTAMP,0)";
ExecuteQuery(str);
}
public DataTable GetChatUser(string user_id)
{
string str = "";
str = " select e.user_name,Convert(Varchar,user_id)+'.jpg' as image_id,e.user_id,"+
" (select count(Read_status) from chats where Read_status=0 and user_id1=e.user_id and user_id2="+user_id+") as read_status from employee_table e left join chats c " +
" on c.user_id2=e.user_id " +
" where e.user_id<>" + user_id + " " +
" group by user_name,user_id";
DataTable dt = GetInDatatable(str);
return dt;
}
public DataTable getFirstEmployeeID(string user_id)
{
string str = "";
str = "select top 1 user_id,user_name from employee_table where user_id<>" + user_id + " order by user_id ";
DataTable dt = GetInDatatable(str);
return dt;
}
public void updateStatus(string user_id,string longin_user_id)
{
string str = "";
str = "update chats set Read_status=1 where user_id1=" + user_id + " and user_id2=" + longin_user_id + " ";
ExecuteQuery(str);
}
public int GetTotalUnreadedMessages(string user_id)
{
int Meaasges = 0;
try
{
string str = " select sum(read_status) " +
" from( select e.user_name,Convert(Varchar,user_id)+'.jpg' as image_id,e.user_id, " +
" (select count(Read_status) from chats where Read_status=0 and user_id1=e.user_id and user_id2=" + user_id + ") as read_status from employee_table e left join chats c " +
" on c.user_id2=e.user_id " +
" where e.user_id<>" + user_id + " " +
" group by user_name,user_id)k";
DataTable dt = GetInDatatable(str);
if (dt.Rows.Count > 0)
{
Meaasges = Convert.ToInt32(dt.Rows[0][0]);
}
}
catch(Exception ex)
{
}
return Meaasges;
}
public bool access(string user_id)
{
bool status = false;
string str = "select user_name from employee_table where user_id=" + user_id + " and master_admin=1 ";
DataTable dt = GetInDatatable(str);
if (dt.Rows.Count > 0)
{
status = true;
}
return status;
}
public DataTable GetPresentUserlist()
{
string str = "select Convert(Varchar,u.user_id)+'.jpg' as image_id,u.email_id," +
" u.user_id,u.user_name user_name,max(d.punch_in) 'punch_in'," +
" case next_status when 1 then 'IN' else 'OUT' end Status ," +
" max(d.punch_out) 'punch_out' ," +
" (select sum(DATEDIFF(MINUTE, punch_in, ISNULL(punch_out, GETDATE()))) Duration" +
" from employee_details_table where user_id=u.user_id and" +
" Punch_date=CONVERT(date, getdate()))Duration,u.next_status " +
" from employee_details_table d right outer join employee_table u" +
" on d.user_id=u.user_id where d. Punch_date=CONVERT(date, getdate())" +
" group by u.user_id,user_name,u.next_status,email_id order by u.user_name";
DataTable dt = GetInDatatable(str);
return dt;
}
public DataTable GetEmailSettings()
{
string str = "";
str = "select * from Email_settings";
DataTable dt = GetInDatatable(str);
return dt;
}
}
}
