public static void clearall()
{
DbCommand tmpdb = BaseDb.CreatetmpCommand();
tmpdb.CommandText = "Delete from avlbldatadet";
BaseDb.ExecuteNonQuery(tmpdb);
tmpdb.CommandText = "delete from misngdatadet";
BaseDb.ExecuteNonQuery(tmpdb);
tmpdb.CommandText = "delete from dataparameter";
BaseDb.ExecuteNonQuery(tmpdb);
tmpdb.CommandText = "delete from datatype";
BaseDb.ExecuteNonQuery(tmpdb);
tmpdb.CommandText = "delete from location";
BaseDb.ExecuteNonQuery(tmpdb);
}
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;
}
}
public static Int32 chkverfied()
{
int i = 0, datacnt = 0, cnt = 0;
DbCommand tmpcmd = BaseDb.CreatetmpCommand();
tmpcmd.CommandText = "select count(*) from location ";
datacnt = Convert.ToInt32(BaseDb.ExecuteScalar(tmpcmd).ToString());
if (datacnt > 0)
{
tmpcmd.CommandText = "select count(*) from location where verify=1";
cnt = Convert.ToInt32(BaseDb.ExecuteScalar(tmpcmd).ToString());
}
tmpcmd.CommandText = "select count(*) from dataparameter ";
datacnt = Convert.ToInt32(BaseDb.ExecuteScalar(tmpcmd).ToString());
if (datacnt > 0)
{
tmpcmd.CommandText = "select count(*) from dataparameter where verify=1";
cnt = Convert.ToInt32(BaseDb.ExecuteScalar(tmpcmd).ToString());
}
tmpcmd.CommandText = "select count(*) from avlbldatadet ";
datacnt = Convert.ToInt32(BaseDb.ExecuteScalar(tmpcmd).ToString());
if (datacnt > 0)
{
tmpcmd.CommandText = "select count(*) from avlbldatadet where verify=1";
cnt = Convert.ToInt32(BaseDb.ExecuteScalar(tmpcmd).ToString());
}
tmpcmd.CommandText = "select count(*) from misngdatadet ";
datacnt = Convert.ToInt32(BaseDb.ExecuteScalar(tmpcmd).ToString());
if (datacnt > 0)
{
tmpcmd.CommandText = "select count(*) from misngdatadet where verify=1";
cnt = Convert.ToInt32(BaseDb.ExecuteScalar(tmpcmd).ToString());
}
if (cnt > 0)
{
i = 1;
}
return(i);
}
public static int updatedatadescription(MetaDataEntity objm, string parameterid)
{
DbCommand cmd = BaseDb.CreatetmpCommand();
cmd.CommandText = "update dataparameter set parameterdesc='" + objm.getparameter + "' where parameterid='" + parameterid + "'";
int error = -1;
try
{
BaseDb.ExecuteNonQuery(cmd);
error = 0;
return(error);
}
catch (Exception ex)
{
throw (ex);
}
finally
{
}
}
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);
}
public static int updatedesc(string SaveLocation)
{
OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + SaveLocation);
conn.Open();
DataTable paradescdt = new DataTable();
OleDbCommand cmd = new OleDbCommand("SELECT UCase(LTrim(RTrim([Data Type]))) AS parameterid,Unit.[Data Type Description] as descr FROM Unit ", conn);
OleDbDataReader rdr = null;
rdr = cmd.ExecuteReader();
paradescdt.Load(rdr);
try
{
DbCommand tmpcmd = BaseDb.CreatetmpCommand();
if (paradescdt.Rows.Count > 0)
{
for (int i = 0; i < paradescdt.Rows.Count; i++)
{
tmpcmd.CommandText = "select count(*) from dataparameter where upper(ltrim(rtrim(parameterid)))='" + paradescdt.Rows [i]["parameterid"].ToString() + "'";
int cnt = Convert.ToInt32(BaseDb.ExecuteScalar(tmpcmd).ToString());
if (cnt > 0)
{
tmpcmd.CommandText = "update dataparameter set parameterdesc='" + paradescdt.Rows[i]["descr"].ToString() + "' where upper(ltrim(rtrim(parameterid)))='" + paradescdt.Rows [i]["parameterid"].ToString() + "'";
BaseDb.ExecuteNonQuery(tmpcmd);
}
}
}
conn.Close();
DbCommand cmd1 = BaseDb.CreatetmpCommand();
cmd1.CommandText = "select count(*) from dataparameter where parameterdesc is null";
int cnt1 = Convert.ToInt32(BaseDb.ExecuteScalar(cmd1).ToString());
return(cnt1);
//{
// rowindex = 2 + index;
// if ((int)rowindex > rg.Row)
// {
// break;
// }
// else
// {
// string abc = ((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowindex, colindex1]).Value2.ToString();
// string def = ((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowindex, colindex2]).Value2.ToString();
// index++;
// DbCommand cmd = BaseDb.CreatetmpCommand();
// cmd.CommandText = "select count(*) from dataparameter where parameterid='" + abc + "'";
// int i = Convert.ToInt32(BaseDb.ExecuteScalar(cmd).ToString());
// if (i > 0)
// {
// cmd.CommandText = "update dataparameter set parameterdesc='" + def + "' where parameterid='" + abc + "'";
// BaseDb.ExecuteNonQuery(cmd);
// }
// }
//}
//DbCommand cmd1 = BaseDb.CreatetmpCommand();
//cmd1.CommandText = "select count(*) from dataparameter where parameterdesc is null";
//int cnt = Convert.ToInt32(BaseDb.ExecuteScalar(cmd1).ToString());
//return (cnt);
}
catch (Exception ex)
{
throw (ex);
}
//string strconn = "Provider=Microsoft.Jet.OLEDB.4.0 ;" +
// "Data Source=C:\\units1.xls;" + "Extended Properties= Excel 8.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);
}