文件操作 - Class1.cs

文件: E:\HostingSpaces\kimblycr\guard.kimbly.in\wwwroot\App_Code\Class1.cs

编辑文件内容

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Web.Configuration;
public abstract class MyConnection
{
    static string contring = WebConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
    public static SqlConnection mycon = new SqlConnection(contring);

}
public class InsertClass : MyConnection
{
    public Int32 InsertCustom(Dictionary<string, string> parameters, string table)
    {
        string cmdtxt = "Insert into " + table + " (";
        string cmdval = " Values(";
        for (int i = 0; i < parameters.Count; i++)
        {
            cmdtxt += parameters.ElementAt(i).Key + " , ";
            cmdval += "'" + parameters.ElementAt(i).Value.Replace("'", "") + "' , ";
        }
        int index = cmdtxt.LastIndexOf(",");
        cmdtxt = cmdtxt.Remove(index) + ")";
        index = cmdval.LastIndexOf(",");
        cmdval = cmdval.Remove(index) + ")";
        SqlCommand cmd = new SqlCommand(cmdtxt + cmdval);
        cmd.CommandText += ";Select @@IDENTITY;";
        cmd.Connection = mycon;
        if (mycon.State == ConnectionState.Closed)
        {
            mycon.Open();
        }
        int id = Convert.ToInt32(cmd.ExecuteScalar());
        mycon.Close();
        return id;
    }
    public void InsertCustomVoid(Dictionary<string, string> parameters, string table)
    {
        string cmdtxt = "Insert into " + table + " (";
        string cmdval = " Values(";
        for (int i = 0; i < parameters.Count; i++)
        {
            cmdtxt += parameters.ElementAt(i).Key + " , ";
            cmdval += "'" + parameters.ElementAt(i).Value.Replace("'", "") + "' , ";
        }
        int index = cmdtxt.LastIndexOf(",");
        cmdtxt = cmdtxt.Remove(index) + ")";
        index = cmdval.LastIndexOf(",");
        cmdval = cmdval.Remove(index) + ")";
        SqlCommand cmd = new SqlCommand(cmdtxt + cmdval);
        cmd.Connection = mycon;
        if (mycon.State == ConnectionState.Closed)
        {
            mycon.Open();
        }
        cmd.ExecuteNonQuery();
        mycon.Close();
    }
    public void InsertPlanExecution(SqlConnection con2, int PlanID, int CheckPointID, string starttime, int SiteID)
    {
        SqlCommand cmd = new SqlCommand("InsertPlanExecution", con2);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@PlanID", PlanID);
        cmd.Parameters.AddWithValue("@CheckPointID", CheckPointID);
        cmd.Parameters.AddWithValue("@TimePlanned", starttime);
        cmd.Parameters.AddWithValue("@SiteID", SiteID);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }
}
public class SelectClass : MyConnection
{
    public int GetCount(string table, string condition)
    {
        string cmdtxt = "Select count(*) as cnt from " + table + " where " + condition;
        SqlCommand cmd = new SqlCommand(cmdtxt, mycon);
        if (mycon.State != ConnectionState.Open)
        {
            mycon.Open();
        }
        int valu = Convert.ToInt32(cmd.ExecuteScalar());
        mycon.Close();
        return valu;
    }
    public DataTable GetDetails(string StoredProcedure, int NumberOfParameters, Dictionary<string, string> Parameters)
    {
        SqlCommand cmd = new SqlCommand(StoredProcedure, mycon);
        cmd.CommandType = CommandType.StoredProcedure;
        for (int i = 0; i < NumberOfParameters; i++)
        {
            cmd.Parameters.Add("@" + Parameters.ElementAt(i).Key, Parameters.ElementAt(i).Value);
        }
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        return dt;
    }
    public DataSet GetDetails(string StoredProcedure, Dictionary<string, string> Parameters)
    {
        SqlCommand cmd = new SqlCommand(StoredProcedure, mycon);
        cmd.CommandType = CommandType.StoredProcedure;
        for (int i = 0; i < Parameters.Count; i++)
        {
            cmd.Parameters.Add("@" + Parameters.ElementAt(i).Key, Parameters.ElementAt(i).Value);
        }
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        return ds;
    }
    public int GetCount(string table, string Primarykey, int val)
    {
        string cmdtxt = "Select count(*) as cnt from " + table + " where " + Primarykey + " = '" + val.ToString() + "'";
        SqlCommand cmd = new SqlCommand(cmdtxt, mycon);
        if (mycon.State != ConnectionState.Open)
        {
            mycon.Open();
        }
        int valu = Convert.ToInt32(cmd.ExecuteScalar());
        mycon.Close();
        return valu;
    }
    public int GetCount(string table, string Primarykey, string val)
    {
        string cmdtxt = "Select count(*) as cnt from " + table + " where " + Primarykey + " = '" + val.ToString() + "'";
        SqlCommand cmd = new SqlCommand(cmdtxt, mycon);
        if (mycon.State != ConnectionState.Open)
        {
            mycon.Open();
        }
        int valu = Convert.ToInt32(cmd.ExecuteScalar());
        mycon.Close();
        return valu;
    }
    public DataTable GetDetails(string table, string Primarykey, int val)
    {
        string cmdtxt = "Select * from " + table + " where " + Primarykey + " = '" + val.ToString() + "'";
        SqlCommand cmd = new SqlCommand(cmdtxt, mycon);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        return dt;
    }
    public string GetDetailsScalar(string table, string Primarykey, int val, string col)
    {
        string cmdtxt = "Select " + col + " from " + table + " where " + Primarykey + " = '" + val.ToString() + "'";
        SqlCommand cmd = new SqlCommand(cmdtxt, mycon);
        if (mycon.State != ConnectionState.Open)
        {
            mycon.Open();
        }
        string valu = cmd.ExecuteScalar().ToString();
        mycon.Close();
        return valu;
    }
    public string GetDetailsScalar(string table, string Primarykey, string val, string col)
    {
        string cmdtxt = "Select " + col + " from " + table + " where " + Primarykey + " = '" + val + "'";
        SqlCommand cmd = new SqlCommand(cmdtxt, mycon);
        if (mycon.State != ConnectionState.Open)
        {
            mycon.Open();
        }
        object obj = cmd.ExecuteScalar();
        string valu = "";
        if (obj != null)
            valu = cmd.ExecuteScalar().ToString();
        mycon.Close();
        return valu;
    }
    public DataTable GetDetails(string table, string Primarykey, string val)
    {
        string cmdtxt = "Select * from " + table + " where " + Primarykey + " = '" + val + "'";
        SqlCommand cmd = new SqlCommand(cmdtxt, mycon);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        return dt;
    }
    public string GetCustomQueryScalar(string query)
    {
        SqlCommand cmd = new SqlCommand(query, mycon);
        if (mycon.State != ConnectionState.Open)
        {
            mycon.Open();
        }
        object obj = cmd.ExecuteScalar();
        string valu = "";
        if (obj != null)
            valu = cmd.ExecuteScalar().ToString();
        mycon.Close();
        return valu;
    }  
    public DataTable PullCrashReport(DateTime dtfrom, DateTime dtto)
    {
        SqlCommand cmd = new SqlCommand("PullCrashReport", mycon);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@FromDate", dtfrom);
        cmd.Parameters.AddWithValue("@ToDate", dtto);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        return dt;
    }
    public DataTable PickGuardsCheckPointsReport(DateTime txtdFrom, DateTime txtdTo, string condition)
    {
        string cmdtxt = "select g.Name as GuardName, c.Name as CheckPoints, t.DateStamp as DateTime from Transactions as t inner join CheckPoints as c on t.Chip=c.ID inner join Guards as g on t.GuardID=g.AutoID where t.Date between @txtdFrom and @txtdTo and g.ID in (" + condition + ") order by g.Name, t.DateStamp";
        SqlCommand cmd = new SqlCommand(cmdtxt, mycon);
        cmd.Parameters.AddWithValue("@txtdFrom", txtdFrom);
        cmd.Parameters.AddWithValue("@txtdTo", txtdTo);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        return dt;
    }
    public DataTable PickGuardsEventsReport(DateTime txtdFrom, DateTime txtdTo, string condition)
    {
        string cmdtxt = "select g.Name as GuardName, e.Name as Events, k.DateStamp as DateTime from KeypadTransactions as k inner join Guards as g on k.GuardID=g.AutoID inner join Events as e on k.Value=e.KeyNO where k.Date between @txtdFrom and @txtdTo and g.ID in (" + condition + ") order by g.Name, k.DateStamp";
        SqlCommand cmd = new SqlCommand(cmdtxt, mycon);
        cmd.Parameters.AddWithValue("@txtdFrom", txtdFrom);
        cmd.Parameters.AddWithValue("@txtdTo", txtdTo);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        return dt;
    }
    public DataTable PickRoutesINShifts(string sid)
    {
        string cmdtxt = "select r.Name as RouteName, i.StartTime, i.EndTime, i.Count from RoutesInShift as i inner join Routes as r on i.RouteID=r.AutoID where i.ShiftID=@Sid order by i.StartTime";
        SqlCommand cmd = new SqlCommand(cmdtxt, mycon);
        cmd.Parameters.AddWithValue("@Sid", sid);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        return dt;
    }
    public DataTable CPReportForShifts(DateTime starttime, DateTime endtime, DateTime date, int siteID)
    {
        //localTime.ToString("HH:mm", CultureInfo.CurrentCulture);
        string sTime = starttime.ToString("HH:mm:ss");
        string eTime = endtime.ToString("HH:mm:ss");
        SqlCommand cmd = new SqlCommand("CPReportForShifts", mycon);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@StartTime", sTime);
        cmd.Parameters.AddWithValue("@EndTime", eTime);
        cmd.Parameters.AddWithValue("@Date", date);
        cmd.Parameters.AddWithValue("@siteID", siteID);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        return dt;
    }
    public DataTable EventsReportForShifts(DateTime starttime, DateTime endtime, DateTime date, int siteID)
    {
        //localTime.ToString("HH:mm", CultureInfo.CurrentCulture);
        string sTime = starttime.ToString("HH:mm:ss");
        string eTime = endtime.ToString("HH:mm:ss");
        SqlCommand cmd = new SqlCommand("EventsReportForShifts", mycon);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@StartTime", sTime);
        cmd.Parameters.AddWithValue("@EndTime", eTime);
        cmd.Parameters.AddWithValue("@Date", date);
        cmd.Parameters.AddWithValue("@siteID", siteID);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        return dt;
    }
    public int CountTransForChip(DateTime date, string stime, string etime, string chip)
    {
        SqlCommand cmd = new SqlCommand("CountTransForChip", mycon);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@Date", date);
        cmd.Parameters.AddWithValue("@stime", stime);
        cmd.Parameters.AddWithValue("@etime", etime);
        cmd.Parameters.AddWithValue("@chip", chip);
        if (mycon.State != ConnectionState.Open)
        {
            mycon.Open();
        }
        object obj = cmd.ExecuteScalar();
        int cnt = 0;
        if (obj != null)
            cnt = Convert.ToInt32(cmd.ExecuteScalar());
        mycon.Close();
        return cnt;
    }
    public DataTable GetShiftRouteDetails(int SiteID, int ShiftID)
    {
        SqlCommand cmd = new SqlCommand("GetShiftRouteDetails", mycon);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@SiteID", SiteID);
        cmd.Parameters.AddWithValue("@ShiftID", ShiftID);        
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        return dt;
    }
    public DataTable GetCheckPointInRoute(int SiteID, int RouteID)
    {
        SqlCommand cmd = new SqlCommand("GetCheckPointInRoute", mycon);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@SiteID", SiteID);
        cmd.Parameters.AddWithValue("@RouteID", RouteID);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        return dt;
    }
    public DataTable GetCheckpointsInRouteAll(int SiteID, int RouteID)
    {
        SqlCommand cmd = new SqlCommand("GetCheckpointsInRouteAll", mycon);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@SiteID", SiteID);
        cmd.Parameters.AddWithValue("@RouteID", RouteID);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        return dt;
    }
    public DataTable GetClosestTransaction(SqlConnection con2, DateTime dtime, string Chip, string Trackerno, int SiteID, DateTime StartTime, DateTime EndTime)
    {
        SqlCommand cmd = new SqlCommand("GetClosestTransaction", mycon);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@Base", dtime);
        cmd.Parameters.AddWithValue("@Chip", Chip);
        cmd.Parameters.AddWithValue("@Tracker", Trackerno);
        cmd.Parameters.AddWithValue("@SiteID", SiteID);
        cmd.Parameters.AddWithValue("@StartTime", StartTime);
        cmd.Parameters.AddWithValue("@EndTime", EndTime);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        return dt;
    }
    public DataTable GetRouteStartTime(int RouteID, int SiteID)
    {
        SqlCommand cmd = new SqlCommand("GetRouteStartTime", mycon);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@RouteID", RouteID);
        cmd.Parameters.AddWithValue("@SiteID", SiteID);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        return dt;
    }
    public DataTable GetPlanTimeReached(int PlanID)
    {
        SqlCommand cmd = new SqlCommand("GetPlanTimeReached", mycon);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@PlanID", PlanID);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        return dt;
    }
}
public class UpdateClass : MyConnection
{
    public void UpdPassword(string user, string pwd)
    {
        SqlCommand cmd = new SqlCommand("UpdPassword", mycon);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@UserName", user);
        cmd.Parameters.AddWithValue("@password", pwd);        
        if (mycon.State != ConnectionState.Open)
        {
            mycon.Open();
        }
        cmd.ExecuteNonQuery();
        mycon.Close();
        cmd.Dispose();
    }
    public void UpdPlanExecution(SqlConnection con1, string status, string GuardID, decimal TimeReached, int PlanID, int CheckpointID, int SiteID)
    {
        SqlCommand cmd = new SqlCommand("UpdPlanExecution", con1);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@GuardID", GuardID);
        cmd.Parameters.AddWithValue("@status", status);
        cmd.Parameters.AddWithValue("@TimeReached", TimeReached);
        cmd.Parameters.AddWithValue("@PlanID", PlanID);
        cmd.Parameters.AddWithValue("@CheckpointID", CheckpointID);
        cmd.Parameters.AddWithValue("@SiteID", SiteID);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }

}
public class DeleteClass : MyConnection
{
    public void DelPlanExecutionByID(int PlanID, int SiteID)
    {
        SqlCommand cmd = new SqlCommand("DelPlanExecutionByID", mycon);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@PlanID", PlanID);
        cmd.Parameters.AddWithValue("@SiteID", SiteID);
        if (mycon.State == ConnectionState.Closed)
        {
            mycon.Open();
        }
        cmd.ExecuteNonQuery();
        mycon.Close();
        cmd.Dispose();
    }
}

文件操作 - Class1.cs

文件: E:\HostingSpaces\kimblycr\guard.kimbly.in\wwwroot\App_Code\Class1.cs

编辑文件内容

修改文件时间

删除文件