Exemplo n.º 1
0
        public static Int32 chknewentry1(string path, string type)
        {
            OleDbConnection conn = new OleDbConnection("Provider=Microsoft.jet.OLEDB.4.0; Data Source=" + path);

            conn.Open();
            OleDbCommand    cmd = new OleDbCommand("select realstat from location ", conn);
            OleDbDataReader rdr = null;

            rdr = cmd.ExecuteReader();
            DataTable dt = new DataTable();

            dt.Load(rdr);
            DataSet   ds   = new DataSet();
            DbCommand cmd1 = BaseDb.CreateStrCommand();

            cmd1.CommandText = "select realstat from location ";
            //"select ralstat from location where type='" + type + "'"
            DataTable dt1 = new DataTable();

            dt1 = BaseDb.ExecuteSelect(cmd1);
            ds.Tables.Add(dt1);
            ds.Tables.Add(dt);
            DataView dtview = new DataView(dt1);
            int      i      = (dtview.FindRows(dt)).Count();

            return(i);
            //dtview.RowFilter="realstat='"+
        }
Exemplo n.º 2
0
        public static Int32 chknewentry(string path, string type)
        {
            OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Persist Security Info=True");

            conn.Open();
            OleDbCommand    cmd = new OleDbCommand("select realstat from location ", conn);
            OleDbDataReader rdr = null;

            rdr = cmd.ExecuteReader();
            DataTable dt = new DataTable();

            dt.Load(rdr);
            DataSet   ds   = new DataSet();
            DbCommand cmd1 = BaseDb.CreateStrCommand();

            cmd1.CommandText = "select realstat from Location ";
            DataTable dt1 = new DataTable();

            dt1 = BaseDb.ExecuteSelect(cmd1);

            var query1       = dt.AsEnumerable().Select(a => new { ID = a["realstat"].ToString() });
            var query2       = dt1.AsEnumerable().Select(b => new { ID = b["realstat"].ToString() });
            var exceptresult = query1.Except(query2);

            int i = exceptresult.Count();

            return(i);
        }
Exemplo n.º 3
0
        public static DataTable GetPassword(string userid)
        {
            DbCommand cmd = BaseDb.CreateStrCommand();

            cmd.CommandText = "select password from usermaster where userid='" + userid + "'";
            DataTable dt = BaseDb.ExecuteSelect(cmd);

            return(dt);
        }
Exemplo n.º 4
0
        public static DataTable  getparameterdet()
        {
            DbCommand tmpcmd = BaseDb.CreatetmpCommand();

            tmpcmd.CommandText = "select parameterid,parameterdesc from dataparameter where querytype='insert'";
            DataTable dt = BaseDb.ExecuteSelect(tmpcmd);

            return(dt);
        }
Exemplo n.º 5
0
        public static DataTable getlocationdet()
        {
            DbCommand tmpcmd = BaseDb.CreatetmpCommand();

            tmpcmd.CommandText = "select distinct realstat,statname from location where querytype='insert'";
            DataTable dt = BaseDb.ExecuteSelect(tmpcmd);

            return(dt);
        }
Exemplo n.º 6
0
 public static DataTable showdata(string srno)
 {
     try
     {
         DbCommand dbcom = BaseDb.CreateStrCommand();
         dbcom.CommandText = "select userid,username,convert(varchar,start_dt,103) as start_dt,convert(varchar,close_dt,103) as close_dt,case when status = 'A' then 'Active' else 'Inactive' end as status from usermaster where srno=" + Convert.ToInt16(srno);
         DataTable dt = new DataTable();
         dt = BaseDb.ExecuteSelect(dbcom);
         return(dt);
     }
     catch (Exception ex)
     {
         throw(ex);
     }
 }
Exemplo n.º 7
0
        public static System.Data.DataTable ShowUserDetailsbycond(string cond)
        {
            DbCommand cmd = BaseDb.CreateStrCommand();

            cmd.CommandText = "select srno,userid,username,case when status = 'A' then 'Active' else 'Inactive' end as status from usermaster where " + cond + " order by created_dt desc";
            System.Data.DataTable dt;
            try
            {
                dt = BaseDb.ExecuteSelect(cmd);
                return(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
Exemplo n.º 8
0
        public static DataTable GetMemInfo(string name)
        {
            DbCommand dbComm = BaseDb.CreateStrCommand();
            // dbComm.CommandText = "select * from Member_Details where Name='" + name + "'";
            DataTable fieldData;

            try
            {
                fieldData = BaseDb.ExecuteSelect(dbComm);
            }
            catch (Exception exc)
            {
                throw exc;
            }
            return(fieldData);
        }
Exemplo n.º 9
0
        public static DataTable GetHduInfoByReg(string RegNo)
        {
            DbCommand dbComm = BaseDb.CreateStrCommand();
            // dbComm.CommandText = "select * from Hdu_Details where RegNo='" + RegNo + "'";
            DataTable fieldData;

            try
            {
                fieldData = BaseDb.ExecuteSelect(dbComm);
            }
            catch (Exception exc)
            {
                throw exc;
            }
            return(fieldData);
        }
Exemplo n.º 10
0
        public static System.Data.DataTable getgriddata()
        {
            DbCommand cmd = BaseDb.CreatetmpCommand();

            cmd.CommandText = "select srno,parameterid,parameterdesc from dataparameter where parameterdesc is null";
            System.Data.DataTable dt;
            try
            {
                dt = BaseDb.ExecuteSelect(cmd);
                return(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
Exemplo n.º 11
0
        public static DataTable GetUserDetails(string userid)
        {
            DbCommand dbComm = BaseDb.CreateStrCommand();

            dbComm.CommandText = "select * from usermaster where userid= '" + userid + "'";
            DataTable fieldData;

            //try
            //{
            fieldData = BaseDb.ExecuteSelect(dbComm);
            //}
            //catch (Exception exc)
            //{
            //throw exc;
            //}
            return(fieldData);
        }
Exemplo n.º 12
0
        public static DataTable GetRoleId(string userid)
        {
            DbCommand dbComm = BaseDb.CreateStrCommand();

            dbComm.CommandText = "select PASSWORD as password from USERMASTER where USERID='" + userid + "'";
            DataTable fieldData;

            try
            {
                fieldData = BaseDb.ExecuteSelect(dbComm);
            }
            catch (Exception exc)
            {
                throw exc;
            }
            return(fieldData);
        }
Exemplo n.º 13
0
        public static DataTable GetPassword(string userid)
        {
            DbCommand dbComm = BaseDb.CreateStrCommand();

            dbComm.CommandText = "select password from hdumaster where userid= '" + userid + "'";
            DataTable fieldData;

            try
            {
                fieldData = BaseDb.ExecuteSelect(dbComm);
            }
            catch (Exception exc)
            {
                throw exc;
            }
            return(fieldData);
        }
Exemplo n.º 14
0
        public static DataTable GetDamName(string RegionName)
        {
            DbCommand dbComm = BaseDb.CreateStrCommand();

            dbComm.CommandText = "SELECT DAM_NAME FROM DAMMASTER WHERE (REGIONID = (SELECT REGIONID FROM REGIONMASTER WHERE (REGION_NAME = '" + RegionName + "')))";
            DataTable fieldData;

            try
            {
                fieldData = BaseDb.ExecuteSelect(dbComm);
            }
            catch (Exception exc)
            {
                throw exc;
            }
            return(fieldData);
        }
Exemplo n.º 15
0
        public static DataTable GetwebPassword(string userid)
        {
            DbCommand dbComm = BaseDb.CreateSqlCommand();

            dbComm.CommandText = "select password from viewusermaster1 where userid= '" + userid + "' and access_for_data_upload=1";
            DataTable fieldData;

            try
            {
                fieldData = BaseDb.ExecuteSelect(dbComm);
            }
            catch (Exception exc)
            {
                throw exc;
            }
            return(fieldData);
        }
Exemplo n.º 16
0
        public static DataTable GetHduDetails()
        {
            DbCommand dbComm = BaseDb.CreateStrCommand();

            dbComm.CommandText = "select * from Hdu_Details";
            DataTable fieldData;

            try
            {
                fieldData = BaseDb.ExecuteSelect(dbComm);
            }
            catch (Exception exc)
            {
                throw exc;
            }
            return(fieldData);
        }
Exemplo n.º 17
0
        public static DataTable GetAccessByDate(DateTime tdate, string uname)
        {
            DbCommand dbComm = BaseDb.CreateStrCommand();

            dbComm.CommandText = "SELECT * from usermaster where userid='" + uname + "' AND status='A' AND ((close_dt IS NULL) OR (close_dt >= '" + tdate + "'))";
            DataTable fieldData;

            //try
            //{
            fieldData = BaseDb.ExecuteSelect(dbComm);


            //}
            //catch (Exception exc)
            //{

            //throw exc;
            //}
            return(fieldData);
        }
Exemplo n.º 18
0
        public static DataTable getavailability()
        {
            DbCommand cmd = BaseDb.CreatetmpCommand();

            cmd.CommandText = "select realstat,datatypeid,parameterid,year from MISNGDATADET  order by REALSTAT ";
            DataTable dt = BaseDb.ExecuteSelect(cmd);

            dt.Columns.Add("availability");

            DataTable dtmsngyr = new DataTable();
            DbCommand tmpcmd   = BaseDb.CreatetmpCommand();
            DbCommand localcmd = BaseDb.CreateStrCommand();

            tmpcmd.CommandText = "select distinct realstat,datatypeid,parameterid from misngdatadet where cancel=0 ";
            DataTable dt1 = BaseDb.ExecuteSelect(tmpcmd);

            dt1.Columns.Add("availability");

            if (dt1.Rows.Count > 0)
            {
                foreach (DataRow dr in dt1.Rows)
                {
                    string avlbl = "";

                    tmpcmd.CommandText = "select count(*) from avlbldatadet where realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "'";
                    int cnt = Convert.ToInt32(BaseDb.ExecuteScalar(tmpcmd).ToString());
                    if (cnt == 0)
                    {
                        localcmd.CommandText = "select count(*) from avlbldatadet where realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "'";
                        int cnt1 = Convert.ToInt32(BaseDb.ExecuteScalar(localcmd).ToString());
                        if (cnt1 > 0)
                        {
                            localcmd.CommandText = "select convert(varchar,year(from_date)) as from_date,convert(varchar,year(to_date)) as to_date from avlbldatadet where realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "'";
                            DataTable dt2 = BaseDb.ExecuteSelect(localcmd);
                            if (dt2.Rows.Count > 0)
                            {
                                avlbl = dt2.Rows[0]["from_date"] + " - " + dt2.Rows[0]["to_date"];
                            }
                        }
                    }
                    else
                    {
                        tmpcmd.CommandText = "select convert(varchar,year(from_date)) as from_date,convert(varchar,year(to_date)) as to_date from avlbldatadet where realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "'";
                        DataTable dt3 = BaseDb.ExecuteSelect(tmpcmd);
                        if (dt3.Rows.Count > 0)
                        {
                            avlbl = dt3.Rows[0]["from_date"] + " - " + dt3.Rows[0]["to_date"];
                        }
                    }
                    dr["availability"] = avlbl.ToString();

                    //tmpcmd.CommandText = "select year from misngdatadet where cancel=1 and realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "' ";
                    //DataTable dtmsngavlbl = BaseDb.ExecuteSelect(tmpcmd);
                    //string avlblyear="";
                    //if (dtmsngavlbl.Rows.Count > 0)
                    //{
                    //    foreach (DataRow msngrow in dtmsngavlbl.Rows)
                    //    {
                    //        string avlblyear1 = msngrow["year"].ToString();
                    //        avlblyear = avlblyear + avlblyear1 + ",";

                    //    }

                    //}
                    //if (avlblyear == "")
                    //{
                    //    dr["msngavlbl"] = "";
                    //}
                    //else
                    //{

                    //    dr["msngavlbl"] = avlblyear.Substring(0, avlblyear.Length - 1);
                    //}
                }
            }

            if (dt1.Rows.Count > 0)
            {
                foreach (DataRow drow in dt.Rows)
                {
                    foreach (DataRow dr in dt1.Rows)
                    {
                        if (dr["realstat"].ToString() == drow["realstat"].ToString() && dr["parameterid"].ToString() == drow["parameterid"].ToString())
                        {
                            drow["availability"] = dr["availability"].ToString();
                        }
                    }
                }
            }
            return(dt);
        }
Exemplo n.º 19
0
        public static DataTable getmisng()
        {
            DataTable dtmsngyr = new DataTable();
            DbCommand tmpcmd   = BaseDb.CreatetmpCommand();
            DbCommand localcmd = BaseDb.CreateStrCommand();

            tmpcmd.CommandText = "select distinct realstat,datatypeid,parameterid,tiunit,divider,frequency from misngdatadet where cancel=0 ";
            DataTable dt = BaseDb.ExecuteSelect(tmpcmd);

            dt.Columns.Add("year");
            dt.Columns.Add("availability");
            dt.Columns.Add("msngavlbl");
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    string myear     = "";
                    string avlbl     = "";
                    string avlblyear = "";
                    dtmsngyr.Clear();
                    string tiunit = dr["tiunit"].ToString();
                    if (dr["tiunit"].ToString() == "" && dr["divider"].ToString() == "")
                    {
                        tmpcmd.CommandText = "select year from misngdatadet where cancel=0 and realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "' ";
                    }
                    else
                    {
                        tmpcmd.CommandText = "select year from misngdatadet where cancel=0 and realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "' and tiunit=" + dr["tiunit"] + " and divider=" + dr["divider"];
                    }
                    dtmsngyr = BaseDb.ExecuteSelect(tmpcmd);
                    if (dtmsngyr.Rows.Count > 0)
                    {
                        foreach (DataRow msngrow in dtmsngyr.Rows)
                        {
                            string myear1 = msngrow["year"].ToString();

                            myear = myear + myear1 + ", ";
                        }
                    }
                    if (myear == "")
                    {
                        dr["year"] = "";
                    }
                    else
                    {
                        dr["year"] = myear.Substring(0, myear.Length - 2);
                    }

                    if (dr["tiunit"].ToString() == "" && dr["divider"].ToString() == "")
                    {
                        tmpcmd.CommandText = "select count(*) from avlbldatadet where realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "'";
                    }
                    else
                    {
                        tmpcmd.CommandText = "select count(*) from avlbldatadet where realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "' and tiunit=" + dr["tiunit"] + " and divider=" + dr["divider"];
                    }
                    int cnt = Convert.ToInt32(BaseDb.ExecuteScalar(tmpcmd).ToString());
                    if (cnt == 0)
                    {
                        if (dr["tiunit"].ToString() == "" && dr["divider"].ToString() == "")
                        {
                            localcmd.CommandText = "select count(*) from avlbldatadet where realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "'";
                        }
                        else
                        {
                            localcmd.CommandText = "select count(*) from avlbldatadet where realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "' and tiunit=" + dr["tiunit"] + " and divider=" + dr["divider"];
                        }
                        int cnt1 = Convert.ToInt32(BaseDb.ExecuteScalar(localcmd).ToString());
                        if (cnt1 > 0)
                        {
                            if (dr["tiunit"].ToString() == "" && dr["divider"].ToString() == "")
                            {
                                localcmd.CommandText = "select convert(varchar,year(from_date)) as from_date,convert(varchar,year(to_date)) as to_date from avlbldatadet where realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "'";
                            }
                            else
                            {
                                localcmd.CommandText = "select convert(varchar,year(from_date)) as from_date,convert(varchar,year(to_date)) as to_date from avlbldatadet where realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "' and tiunit=" + dr["tiunit"] + " and divider=" + dr["divider"];
                            }
                            DataTable dt1 = BaseDb.ExecuteSelect(localcmd);
                            if (dt1.Rows.Count > 0)
                            {
                                avlbl = dt1.Rows[0]["from_date"] + " - " + dt1.Rows[0]["to_date"];
                            }
                        }
                    }
                    else
                    {
                        if (dr["tiunit"].ToString() == "" && dr["divider"].ToString() == "")
                        {
                            tmpcmd.CommandText = "select convert(varchar,year(from_date)) as from_date,convert(varchar,year(to_date)) as to_date from avlbldatadet where realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "' ";
                        }
                        else
                        {
                            tmpcmd.CommandText = "select convert(varchar,year(from_date)) as from_date,convert(varchar,year(to_date)) as to_date from avlbldatadet where realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "' and tiunit=" + dr["tiunit"] + " and divider=" + dr["divider"];
                        }
                        DataTable dt1 = BaseDb.ExecuteSelect(tmpcmd);
                        if (dt1.Rows.Count > 0)
                        {
                            avlbl = dt1.Rows[0]["from_date"] + " - " + dt1.Rows[0]["to_date"];
                        }
                    }
                    dr["availability"] = avlbl.ToString();
                    if (dr["tiunit"].ToString() == "" && dr["divider"].ToString() == "")
                    {
                        tmpcmd.CommandText = "select year from misngdatadet where cancel=1 and realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "'";
                    }
                    else
                    {
                        tmpcmd.CommandText = "select year from misngdatadet where cancel=1 and realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "' and tiunit=" + dr["tiunit"] + " and divider=" + dr["divider"];
                    }
                    DataTable dtmsngavlbl = BaseDb.ExecuteSelect(tmpcmd);
                    if (dtmsngavlbl.Rows.Count > 0)
                    {
                        foreach (DataRow msngrow in dtmsngavlbl.Rows)
                        {
                            string avlblyear1 = msngrow["year"].ToString();
                            avlblyear = avlblyear + avlblyear1 + ",";
                        }
                    }
                    if (avlblyear == "")
                    {
                        dr["msngavlbl"] = "";
                    }
                    else
                    {
                        dr["msngavlbl"] = avlblyear.Substring(0, avlblyear.Length - 1);
                    }
                }
            }

            // tmpcmd.CommandText = "select distinct realstat,parameterid from misngdatadet where cancel=0 and querytype='update'";


            return(dt);
        }