//This Asset list generate with relation to fieldservice table.
public static DataTable getAssetsByLocation(Int32 ID_Location)
{
string strSql;
if (!Login.OnLineMode)
{
using (CEConn localDB = new CEConn())
{
DataTable dtAssets = new DataTable("dtAssets");
strSql = "select A.*, isNULL(E.UserName,'') as Employee from Assets A Left outer join Employees E on A.ID_Employee=E.ServerKey where A.ID_Location = " + ID_Location + " and A.RowStatus=" + Convert.ToInt32(RowStatus.Synchronized);
localDB.FillDataTable(ref dtAssets, strSql);
return(dtAssets);
}
}
else
{
RConnection.RConnection OnConn = new RConnection.RConnection();
OnConn.Url = Login.webConURL;
strSql = "select Top " + Login.ItemLimit.ToString() + " A.*, isNULL(E.UserName,'') as Employee from Assets A Left outer join Employees E on A.ID_Employee=E.ID_Employee where A.ID_Location = " + ID_Location;
return(OnConn.getDataTable(strSql));
//strSql = "exec Update_Asset_Location_by_HH '" + TagID + "' ," + ID_Location + ", " + Login.ID + " ,0,'" + ReferenceNo + "'";
//OnConn.runQuery(strSql);
//throw new ApplicationException("On line FS section not implemented yet.");
}
}
public static DataTable getLocationList(string searchString, ComboBox cb)
{
if (!Login.OnLineMode)
{
using (CEConn localDB = new CEConn())
{
string strSql;
strSql = " select serverKey as ID_Location,Name from Locations where ServerKey<>0 and tagid<>'0000000000000000000000D' AND [Name] Like '" + searchString + "%' order by Name";
DataTable dtList = new DataTable();
localDB.FillDataTable(ref dtList, strSql);
//return dtList;
cb.Items.Clear();
LocationItem item;
if (dtList != null && dtList.Rows.Count > 0)
{
foreach (DataRow dr in dtList.Rows)
{
//dtt.ImportRow(dr);
item = new LocationItem(dr["Name"].ToString(), dr["ID_Location"].ToString());
cb.Items.Add(item);
}
}
//dtt.Columns["ServerKey"].ColumnName = "ID_Location";
return(null);
}
}
else
{
RConnection.RConnection OnConn = new RConnection.RConnection();
OnConn.Url = Login.webConURL;
string strSql;
strSql = " select ID_Location,Name from Locations where Is_Active=1 and Is_Deleted=0 and tagid<>'0000000000000000000000D' AND [Name] Like '" + searchString + "%' order by Name";
DataTable dtList = (DataTable)OnConn.getDataTable(strSql);
cb.Items.Clear();
LocationItem item;
if (dtList != null && dtList.Rows.Count > 0)
{
foreach (DataRow dr in dtList.Rows)
{
//dtt.ImportRow(dr);
item = new LocationItem(dr["Name"].ToString(), dr["ID_Location"].ToString());
cb.Items.Add(item);
}
}
//return (DataTable)OnConn.getDataTable(strSql);
return(null);
//return (DataTable)OnConn.getDataTable(strSql);
//throw new ApplicationException("Online functionality not implemented yet.");
}
}
public static DataTable getGroups(Int32 ParentID, string searchText)
{
if (!Login.OnLineMode)
{
using (CEConn localDB = new CEConn())
{
string strSql;
strSql = " select ServerKey as ID_AssetGroup,Name from Asset_Groups where ParentID =" + ParentID + " AND [Name] LIKE '" + searchText + "%'";
DataTable dtList = new DataTable();
localDB.FillDataTable(ref dtList, strSql);
return(dtList);
}
}
else
{
RConnection.RConnection OnConn = new RConnection.RConnection();
OnConn.Url = Login.webConURL;
string strSql;
strSql = " select ID_AssetGroup,Name from Asset_Groups where ParentID =" + ParentID + " AND [Name] LIKE '" + searchText + "%'" + " and Is_Active=1 and Is_Deleted=0";
DataTable dt;
dt = (DataTable)OnConn.getDataTable(strSql);
return(dt);
//throw new ApplicationException("Online functionality not implemented yet.");
}
}
/* Commented Constructor will uncomment if required to get information by title.
* public Locations(String Title)
* {
*
* if (!Login.OnLineMode)
* {
* using (CEConn localDB = new CEConn())
* {
* string strSql;
* strSql = " select * from Locations where Title='" + Title + "'";
* SqlCeDataReader dr;
* dr = localDB.getReader(strSql);
* while (dr.Read())
* {
* _ServerKey = Convert.ToInt32(dr["ServerKey"].ToString().Trim());
* _Title = (String)dr["Title"];
* _Name = (String)dr["Name"];
* _RowStatus = Convert.ToInt32(dr["RowStatus"]);
* }
* dr.Close();
* }
* }
* else
* {
* RConnection.RConnection OnConn = new RConnection.RConnection();
* OnConn.Url = Login.webConURL;
* string strSql;
* strSql = " select Title,Name,ID_Location from Locations where Title='" + Title +"'";
* DataTable dt;
* dt = OnConn.getDataTable(strSql);
* if (dt.Rows.Count != 0)
* {
* _ServerKey = (int)dt.Rows[0]["ID_Location"];
* _Title = (String)dt.Rows[0]["Title"];
* _Name = (String)dt.Rows[0]["Name"];
* _RowStatus = Convert.ToInt32(RowStatus.Synchronized);
* }
* //throw new ApplicationException("Online functionality not implemented yet.");
* }
* }
*/
//This location list generated with relation to FieldService table entries and employee [and due date when online].
public static DataTable getLocationList()
{
StringBuilder sql = new StringBuilder("");
if (!Login.OnLineMode)
{
using (CEConn localDB = new CEConn())
{
string strSql;
strSql = " select distinct L.serverKey as ID_Location,L.Name "
+ " from Locations L,FS_AssetStatus FS,Assets A "
+ " where L.ServerKey<>0 and FS.ID_Asset=A.ServerKey "
+ " and (FS.FSStatus=" + Convert.ToInt32(FSStatusType.New) + " or FS.FSStatus=" + Convert.ToInt32(FSStatusType.InProcess) + ")"
+ " and L.ServerKey=A.ID_Location order by L.Name";
DataTable dtList = new DataTable();
localDB.FillDataTable(ref dtList, strSql);
return(dtList);
}
}
else
{
//throw new ApplicationException("Online functionality not implemented yet.");
RConnection.RConnection OnConn = new RConnection.RConnection();
OnConn.Url = Login.webConURL;
string strSql;
//strSql = "select distinct L.ID_Location,L.Name from Locations L,FieldService FS,Employees E where L.ID_Location=FS.ID_Location and E.ID_Employee=FS.ID_Employee and FS.ID_Employee=" + Login.ID + " and FS.dueDate >= '" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss tt") + "' order by L.Name";
strSql = " select distinct L.ID_Location,L.Name "
+ " from Locations L,FS_AssetStatus FS,Assets A "
+ " where FS.ID_Asset=A.ID_Asset "
+ " and (FS.FSStatus=" + Convert.ToInt32(FSStatusType.New) + " or FS.FSStatus=" + Convert.ToInt32(FSStatusType.InProcess) + ")"
+ " and L.ID_Location=A.ID_Location and FS.Is_Active=1 and FS.Is_Deleted=0 order by L.Name";
return((DataTable)OnConn.getDataTable(strSql));
}
}
public Task(Int32 TaskID)
{
if (!Login.OnLineMode)
{
using (CEConn localDB = new CEConn())
{
string strSql;
strSql = " select * from Tasks where ServerKey=" + TaskID;
SqlCeDataReader dr;
dr = localDB.getReader(strSql);
while (dr.Read())
{
_ServerKey = TaskID;
_Title = (String)dr["Title"];
_RowStatus = Convert.ToInt32(dr["RowStatus"]);
}
dr.Close();
}
}
else
{
RConnection.RConnection OnConn = new RConnection.RConnection();
OnConn.Url = Login.webConURL;
string strSql;
strSql = " select Title from Tasks where ID_Tasks=" + TaskID;
DataTable dt;
dt = OnConn.getDataTable(strSql);
if (dt.Rows.Count != 0)
{
_ServerKey = TaskID;
_Title = (String)dt.Rows[0]["Title"];
_RowStatus = Convert.ToInt32(RowStatus.Synchronized);
}
}
}
public static DataTable getMenuOptionTable()
{
if (!Login.OnLineMode)
{
using (CEConn localDB = new CEConn())
{
string strSql;
strSql = " select M.ID_MenuOptions,M.Name from Authority A ";
strSql += " inner join Menu_Options M on M.ServerKey=A.ID_MenuOptions ";
strSql += " where A.ID_SecurityGroups=" + _RoleId;
DataTable dtOptions = new DataTable("MenuOptions");
localDB.FillDataTable(ref dtOptions, strSql);
return(dtOptions);
}
}
else
{
RConnection.RConnection OnConn = new RConnection.RConnection();
OnConn.Url = Login.webConURL;
string strSql;
strSql = " select M.ID_MenuOptions,M.Name from Authority A ";
strSql += " inner join Menu_Options M on M.ID_MenuOptions=A.ID_MenuOptions ";
strSql += " where A.ID_SecurityGroups=" + _RoleId;
DataTable dt;
dt = (DataTable)OnConn.getDataTable(strSql);
return(dt);
//throw new ApplicationException("Online functionality not implemented yet.");
}
}
public static DataTable GetAssetStatus()
{
if (!Login.OnLineMode)
{
using (CEConn localDB = new CEConn())
{
string strSql;
strSql = " select ServerKey as ID_AssetStatus,Status from Asset_Status";
DataTable dtList = new DataTable();
localDB.FillDataTable(ref dtList, strSql);
return(dtList);
}
}
else
{
RConnection.RConnection OnConn = new RConnection.RConnection();
OnConn.Url = Login.webConURL;
string strSql;
strSql = " select ID_AssetStatus,Status from Asset_Status where Is_Active=1 and Is_Deleted=0";
DataTable dt;
dt = (DataTable)OnConn.getDataTable(strSql);
return(dt);
//throw new ApplicationException("Online functionality not implemented yet.");
}
}
public Locations(String TagID)
{
if (!Login.OnLineMode)
{
using (CEConn localDB = new CEConn())
{
string strSql;
strSql = " select * from Locations where TagID='" + TagID + "'";
SqlCeDataReader dr;
dr = localDB.getReader(strSql);
while (dr.Read())
{
_ServerKey = Convert.ToInt32(dr["ServerKey"].ToString().Trim());
_TagNo = (String)dr["TagID"];
_Name = (String)dr["Name"];
_RowStatus = Convert.ToInt32(dr["RowStatus"]);
if (dr["LocationNo"] != DBNull.Value)
{
_LocNo = (String)dr["LocationNo"];
}
else
{
_LocNo = "";
}
}
dr.Close();
}
}
else
{
RConnection.RConnection OnConn = new RConnection.RConnection();
OnConn.Url = Login.webConURL;
string strSql;
strSql = " select TagID,Name,ID_Location,LocationNo from Locations where TagID='" + TagID + "'";
DataTable dt;
dt = OnConn.getDataTable(strSql);
if (dt.Rows.Count != 0)
{
_ServerKey = (int)dt.Rows[0]["ID_Location"];
_TagNo = (String)dt.Rows[0]["TagID"];
_Name = (String)dt.Rows[0]["Name"];
_RowStatus = Convert.ToInt32(RowStatus.Synchronized);
if (dt.Rows[0]["LocationNo"] != DBNull.Value)
{
_LocNo = (String)dt.Rows[0]["LocationNo"];
}
else
{
_LocNo = "";
}
}
//throw new ApplicationException("Online functionality not implemented yet.");
}
}
//This Group list generate with relation to FieldService table.
public static DataTable getGroups(Int32 ParentID)
{
StringBuilder sql = new StringBuilder("");
if (!Login.OnLineMode)
{
using (CEConn localDB = new CEConn())
{
string strSql;
sql.Append(" select distinct G.ServerKey as ID_AssetGroup,G.Name from Asset_Groups G, Assets A, FS_AssetStatus FS ");
sql.Append(" where G.ParentID =" + ParentID + " and FS.ID_Asset=A.ServerKey ");
sql.Append(" and (FS.FSStatus=" + Convert.ToInt32(FSStatusType.New) + " or FS.FSStatus=" + Convert.ToInt32(FSStatusType.InProcess) + ")");
sql.Append(" and (A.GroupIDs like '%,'+ Convert(nvarchar(6),G.ID_AssetGroup) + ',%' or A.ID_AssetGroup=G.ServerKey)");
//strSql = " select distinct G.ServerKey as ID_AssetGroup,G.Name from Asset_Groups G, Assets A, FS_AssetStatus FS "
// + " where G.ParentID =" + ParentID + " and FS.ID_Asset=A.ServerKey "
// + " and (FS.FSStatus=" + Convert.ToInt32(FSStatusType.New) + " or FS.FSStatus=" + Convert.ToInt32(FSStatusType.InProcess) + ")"
// + " and (A.GroupIDs like '%,'+ Convert(nvarchar(6),G.ID_AssetGroup) + ',%' or A.ID_AssetGroup=G.ServerKey)";
strSql = sql.ToString();
// strSql = " select distinct G.ServerKey as ID_AssetGroup,G.Name from Asset_Groups G where G.ParentID = " + ParentID;
DataTable dtList = new DataTable();
localDB.FillDataTable(ref dtList, strSql);
//return localDB.GetGroups(strSql);
return(dtList);
}
}
else
{
RConnection.RConnection OnConn = new RConnection.RConnection();
OnConn.Url = Login.webConURL;
string strSql;
//strSql = " select ID_AssetGroup,Name from Asset_Groups where ParentID =" + ParentID + " and Is_Active=1 and Is_Deleted=0";
sql.Append(" select distinct G.ID_AssetGroup,G.Name from Asset_Groups G, Assets A, FS_AssetStatus FS ");
sql.Append(" where G.ParentID =" + ParentID + " and FS.ID_Asset=A.ID_Asset ");
sql.Append(" and (FS.FSStatus=" + Convert.ToInt32(FSStatusType.New) + " or FS.FSStatus=" + Convert.ToInt32(FSStatusType.InProcess) + ")");
sql.Append(" and (dbo.GetParentGroups(A.ID_AssetGroup) like '%,'+ Convert(nvarchar(6),G.ID_AssetGroup) + ',%' or A.ID_AssetGroup=G.ID_AssetGroup) and FS.IS_Active=1 and FS.IS_Deleted=0");
//strSql = " select distinct G.ID_AssetGroup,G.Name from Asset_Groups G, Assets A, FS_AssetStatus FS "
// + " where G.ParentID =" + ParentID + " and FS.ID_Asset=A.ID_Asset "
// + " and (FS.FSStatus=" + Convert.ToInt32(FSStatusType.New) + " or FS.FSStatus=" + Convert.ToInt32(FSStatusType.InProcess) + ")"
// + " and (dbo.GetParentGroups(A.ID_AssetGroup) like '%,'+ Convert(nvarchar(6),G.ID_AssetGroup) + ',%' or A.ID_AssetGroup=G.ID_AssetGroup) and FS.IS_Active=1 and FS.IS_Deleted=0";
DataTable dt;
dt = (DataTable)OnConn.getDataTable(sql.ToString());
return(dt);
//throw new ApplicationException("Online functionality not implemented yet.");
}
}
public Employee(Int32 EmpID)
{
_ServerKey = EmpID;
if (!Login.OnLineMode)
{
using (CEConn localDB = new CEConn())
{
string strSql;
strSql = " select * from Employees where ServerKey=" + _ServerKey;
SqlCeDataReader dr;
dr = localDB.getReader(strSql);
while (dr.Read())
{
_ID_SecurityGroup = Convert.ToInt32(dr["ID_SecurityGroup"]);
_TagID = (String)dr["TagID"];
if (dr["EmpNo"] != DBNull.Value)
{
_EmpNo = (String)dr["EmpNo"];
}
_UserName = (String)dr["UserName"];
_ServerKey = Convert.ToInt32(dr["ServerKey"]);
_RowStatus = Convert.ToInt32(dr["RowStatus"]);
}
dr.Close();
}
}
else
{
RConnection.RConnection OnConn = new RConnection.RConnection();
OnConn.Url = Login.webConURL;
string strSql;
strSql = " select ID_Employee,ID_SecurityGroup,TagID,EmpNo,UserName from Employees where ID_Employee=" + _ServerKey;
DataTable dt;
dt = OnConn.getDataTable(strSql);
if (dt.Rows.Count != 0)
{
_ID_SecurityGroup = Convert.ToInt32(dt.Rows[0]["ID_SecurityGroup"]);
_TagID = (String)dt.Rows[0]["TagID"];
if (dt.Rows[0]["EmpNo"] != DBNull.Value)
{
_EmpNo = (String)dt.Rows[0]["EmpNo"];
}
_UserName = (String)dt.Rows[0]["UserName"];
_ServerKey = Convert.ToInt32(dt.Rows[0]["ID_Employee"]);
_RowStatus = Convert.ToInt32(RowStatus.Synchronized);
}
//throw new ApplicationException("Online Employee functionality not implemented yet.");
}
}
public FS_AssetStatus(Int32 FSID)
{
if (!Login.OnLineMode)
{
using (CEConn localDB = new CEConn())
{
string strSql;
strSql = " select * from FS_AssetStatus where ServerKey=" + FSID;
SqlCeDataReader dr;
dr = localDB.getReader(strSql);
while (dr.Read())
{
_ServerKey = FSID;
_ID_Tasks = (Int32)dr["ID_Tasks"];
_ID_Asset = (Int32)dr["ID_Asset"];
_ID_Template = (Int32)dr["ID_Template"];
_DueDate = (DateTime)dr["DueDate"];
_FSStatus = (Int32)dr["FSStatus"];
_TaskStatus = (Int32)dr["TaskStatus"];
_ID_Employee = (Int32)dr["ID_Employee"];
_RowStatus = Convert.ToInt32(dr["RowStatus"]);
}
dr.Close();
}
}
else
{
//throw new ApplicationException("Online functionality not implemented yet.");
RConnection.RConnection OnConn = new RConnection.RConnection();
OnConn.Url = Login.webConURL;
string strSql;
strSql = " select F.ID_Tasks, F.ID_Asset, F.ID_Template, F.DueDate, F.TaskStatus, F.FSStatus, F.ID_Employee from FieldService where ID_FieldService=" + FSID;
DataTable dt;
dt = OnConn.getDataTable(strSql);
if (dt.Rows.Count != 0)
{
_ServerKey = FSID;
_ID_Tasks = (Int32)dt.Rows[0]["ID_Tasks"];
_ID_Asset = (Int32)dt.Rows[0]["ID_Asset"];
_ID_Template = (Int32)dt.Rows[0]["ID_Template"];
_DueDate = (DateTime)dt.Rows[0]["DueDate"];
_FSStatus = (Int32)dt.Rows[0]["FSStatus"];
_TaskStatus = (Int32)dt.Rows[0]["TaskStatus"];
_ID_Employee = (Int32)dt.Rows[0]["ID_Employee"];
_RowStatus = Convert.ToInt32(RowStatus.Synchronized);
}
}
}
public FieldServices(Int32 FSID)
{
if (!Login.OnLineMode)
{
using (CEConn localDB = new CEConn())
{
string strSql;
strSql = " select * from FieldService where ServerKey=" + FSID;
SqlCeDataReader dr;
dr = localDB.getReader(strSql);
while (dr.Read())
{
_ServerKey = FSID;
_Title = (String)dr["Title"];
_ID_Location = (Int32)dr["ID_Location"];
_ID_Employee = (Int32)dr["ID_Employee"];
_RowStatus = Convert.ToInt32(dr["RowStatus"]);
}
dr.Close();
}
}
else
{
//throw new ApplicationException("Online functionality not implemented yet.");
RConnection.RConnection OnConn = new RConnection.RConnection();
OnConn.Url = Login.webConURL;
string strSql;
strSql = " select Title,ID_Location,ID_Employee from FieldService where ID_FieldService=" + FSID;
DataTable dt;
dt = OnConn.getDataTable(strSql);
if (dt.Rows.Count != 0)
{
_ServerKey = FSID;
_Title = (String)dt.Rows[0]["Title"];
_ID_Location = (Int32)dt.Rows[0]["ID_Location"];
_ID_Employee = (Int32)dt.Rows[0]["ID_Employee"];
_RowStatus = Convert.ToInt32(RowStatus.Synchronized);
}
}
}
public static DataTable getTaskList(Int64 ID_Asset, Int64 ID_Template)
{
if (!Login.OnLineMode)
{
using (CEConn localDB = new CEConn())
{
string strSql;
strSql = " select Tsk.serverKey as ID_Tasks,Tsk.Title,FS.FSStatus,FS.TaskStatus,FS.ID_FSAssetStatus,FS.Comments,FS.DueDate "
+ " from FS_Templates T,FS_AssetStatus FS, Assets A, Tasks Tsk"
+ " where T.ServerKey<>0 and FS.ID_Template=T.ServerKey and FS.ID_Asset=A.ServerKey "
+ " and (FS.FSStatus = " + Convert.ToInt32(FSStatusType.New) + " or FS.FSStatus = " + Convert.ToInt32(FSStatusType.InProcess) + ")"
+ " and T.ServerKey = " + ID_Template + " and FS.ID_Tasks=Tsk.ServerKey"
+ " and A.ServerKey=" + ID_Asset + " order by Tsk.Title";
DataTable dtList = new DataTable();
localDB.FillDataTable(ref dtList, strSql);
return(dtList);
}
}
else
{
//throw new ApplicationException("Online functionality not implemented yet.");
RConnection.RConnection OnConn = new RConnection.RConnection();
OnConn.Url = Login.webConURL;
string strSql;
//strSql = "select distinct L.ID_Location,L.Name from Locations L,FieldService FS,Employees E where L.ID_Location=FS.ID_Location and E.ID_Employee=FS.ID_Employee and FS.ID_Employee=" + Login.ID + " and FS.dueDate >= '" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss tt") + "' order by L.Name";
strSql = " select distinct Tsk.ID_Tasks,Tsk.Title,FS.FSStatus,FS.TaskStatus,FS.ID_FSAssetStatus,FS.Comments,FS.DueDate "
+ " from FS_Templates T, FS_AssetStatus FS, Assets A, Tasks as Tsk "
+ " where T.ID_Template<>0 and FS.ID_Template=T.ID_Template and FS.ID_Asset=A.ID_Asset and A.ID_Asset=" + ID_Asset
+ " and (FS.FSStatus = " + Convert.ToInt32(FSStatusType.New) + " or FS.FSStatus = " + Convert.ToInt32(FSStatusType.InProcess) + ")"
+ " and T.ID_Template = " + ID_Template + " and FS.ID_Tasks=Tsk.ID_Tasks"
+ " and FS.Is_Active=1 and FS.Is_Deleted=0 order by Tsk.Title";
//strSql = " select distinct L.ID_Location,L.Name "
// + "from Locations L,FS_AssetStatus FS,Assets A "
// + "where FS.ID_Asset=A.ID_Asset and L.ID_Location=A.ID_Location and FS.Is_Active=1 and FS.Is_Deleted=0 order by L.Name";
return((DataTable)OnConn.getDataTable(strSql));
}
}
//This Asset list generate with relation to fieldservice table.
public static DataTable getAssets(Int64 ID_Location, Int64 ID_AssetGroup, Int64 ID_Asset)
{
string strSql;
if (!Login.OnLineMode)
{
using (CEConn localDB = new CEConn())
{
DataTable dtAssets = new DataTable("dtAssets");
strSql = "select distinct A.ServerKey as ID_Asset, A.TagID,A.Name,A.ID_Employee,A.Date_Modified,A.ID_Location,A.ID_AssetGroup,A.RowStatus,A.AssetNo,A.GroupIDs, E.UserName as Employee from Assets A " +
" Left outer join Employees E on A.ID_Employee=E.ServerKey "
+ " inner join FS_AssetStatus F on A.ServerKey=F.ID_Asset "
+ " where (A.ID_Location = " + ID_Location + " or 0=" + ID_Location + " or -2=" + ID_Location + ")"
+ " and (A.GroupIDs like '%," + ID_AssetGroup + ",%' or A.ID_AssetGroup = " + ID_AssetGroup + " or 0=" + ID_AssetGroup + " or -2=" + ID_AssetGroup + ")"
+ " and (F.FSStatus=" + Convert.ToInt32(FSStatusType.New) + " or F.FSStatus=" + Convert.ToInt32(FSStatusType.InProcess) + ")"
+ " and (A.ServerKey=" + ID_Asset + " or 0 =" + ID_Asset + ") ";
localDB.FillDataTable(ref dtAssets, strSql);
return(dtAssets);
}
}
else
{
RConnection.RConnection OnConn = new RConnection.RConnection();
OnConn.Url = Login.webConURL;
strSql = "select distinct top 100 A.*, isNULL(E.UserName,'') as Employee from Assets A "
+ " Left outer join Employees E on A.ID_Employee=E.ID_Employee "
+ " inner join FS_AssetStatus F on A.ID_Asset=F.ID_Asset "
+ " where (A.ID_Location = " + ID_Location + " or 0=" + ID_Location + " or -2=" + ID_Location + ")"
+ " and (dbo.GetParentGroups(A.ID_AssetGroup) like '%," + ID_AssetGroup + ",%' or A.ID_AssetGroup = " + ID_AssetGroup + " or 0=" + ID_AssetGroup + " or -2=" + ID_AssetGroup + ")"
+ " and (A.ID_Asset=" + ID_Asset + " or 0 =" + ID_Asset + ")"
+ " and (F.FSStatus=" + Convert.ToInt32(FSStatusType.New) + " or F.FSStatus=" + Convert.ToInt32(FSStatusType.InProcess) + ")"
+ " and F.IS_Active=1 and F.Is_Deleted=0";
return(OnConn.getDataTable(strSql));
//strSql = "exec Update_Asset_Location_by_HH '" + TagID + "' ," + ID_Location + ", " + Login.ID + " ,0,'" + ReferenceNo + "'";
//OnConn.runQuery(strSql);
//throw new ApplicationException("On line FS section not implemented yet.");
}
}
public static DataTable getTasksList()
{
if (!Login.OnLineMode)
{
using (CEConn localDB = new CEConn())
{
string strSql;
strSql = " select serverKey as ID_Tasks,Title from Tasks where ServerKey<>0 order by Title";
DataTable dtList = new DataTable();
localDB.FillDataTable(ref dtList, strSql);
return(dtList);
}
}
else
{
RConnection.RConnection OnConn = new RConnection.RConnection();
OnConn.Url = Login.webConURL;
string strSql;
strSql = " select ID_Tasks,Title from Tasks where Is_Active=1 and Is_Deleted=0 order by Title";
return((DataTable)OnConn.getDataTable(strSql));
}
}
//public Reasons(Int32 ReasonID)
//{
// if (!Login.OnLineMode)
// {
// using (CEConn localDB = new CEConn())
// {
// string strSql;
// strSql = " select * from Locations where ServerKey=" + LocID;
// SqlCeDataReader dr;
// dr = localDB.getReader(strSql);
// while (dr.Read())
// {
// _ServerKey = LocID;
// _TagNo = (String)dr["TagID"];
// _Name = (String)dr["Name"];
// _RowStatus = Convert.ToInt32(dr["RowStatus"]);
// }
// dr.Close();
// }
// }
// else
// {
// RConnection.RConnection OnConn = new RConnection.RConnection();
// OnConn.Url = Login.webConURL;
// string strSql;
// strSql = " select TagID,Name from Locations where ID_Location=" + LocID;
// DataTable dt;
// dt = OnConn.getDataTable(strSql);
// if (dt.Rows.Count != 0)
// {
// _ServerKey = LocID;
// _TagNo = (String)dt.Rows[0]["TagID"];
// _Name = (String)dt.Rows[0]["Name"];
// _RowStatus = Convert.ToInt32(RowStatus.Synchronized);
// }
// //throw new ApplicationException("Online functionality not implemented yet.");
// }
//}
public static DataTable getReasonsList()
{
if (!Login.OnLineMode)
{
using (CEConn localDB = new CEConn())
{
string strSql;
strSql = " select serverKey as ID_Reason,Name from Reasons where ServerKey<>0 order by Name";
DataTable dtList = new DataTable();
localDB.FillDataTable(ref dtList, strSql);
return(dtList);
}
}
else
{
RConnection.RConnection OnConn = new RConnection.RConnection();
OnConn.Url = Login.webConURL;
string strSql;
strSql = " select ID_Reason,Name from Reasons where Is_Active=1 and Is_Deleted=0 order by Name";
return((DataTable)OnConn.getDataTable(strSql));
//throw new ApplicationException("Online functionality not implemented yet.");
}
}
/* Commented Constructor will uncomment if required to get information by title.
* public Locations(String Title)
* {
*
* if (!Login.OnLineMode)
* {
* using (CEConn localDB = new CEConn())
* {
* string strSql;
* strSql = " select * from Locations where Title='" + Title + "'";
* SqlCeDataReader dr;
* dr = localDB.getReader(strSql);
* while (dr.Read())
* {
* _ServerKey = Convert.ToInt32(dr["ServerKey"].ToString().Trim());
* _Title = (String)dr["Title"];
* _Name = (String)dr["Name"];
* _RowStatus = Convert.ToInt32(dr["RowStatus"]);
* }
* dr.Close();
* }
* }
* else
* {
* RConnection.RConnection OnConn = new RConnection.RConnection();
* OnConn.Url = Login.webConURL;
* string strSql;
* strSql = " select Title,Name,ID_Location from Locations where Title='" + Title +"'";
* DataTable dt;
* dt = OnConn.getDataTable(strSql);
* if (dt.Rows.Count != 0)
* {
* _ServerKey = (int)dt.Rows[0]["ID_Location"];
* _Title = (String)dt.Rows[0]["Title"];
* _Name = (String)dt.Rows[0]["Name"];
* _RowStatus = Convert.ToInt32(RowStatus.Synchronized);
* }
* //throw new ApplicationException("Online functionality not implemented yet.");
* }
* }
*/
//This location list generated with relation to FieldService table entries and employee [and due date when online].
public static DataTable getLocationList()
{
if (!Login.OnLineMode)
{
using (CEConn localDB = new CEConn())
{
string strSql;
strSql = " select distinct L.serverKey as ID_Location,L.Name from Locations L,FieldService FS,Employees E where L.ServerKey<>0 and L.ServerKey=FS.ID_Location and E.ServerKey=FS.ID_Employee and FS.ID_Employee=" + Login.ID + " order by L.Name";
DataTable dtList = new DataTable();
localDB.FillDataTable(ref dtList, strSql);
return(dtList);
}
}
else
{
//throw new ApplicationException("Online functionality not implemented yet.");
RConnection.RConnection OnConn = new RConnection.RConnection();
OnConn.Url = Login.webConURL;
string strSql;
strSql = "select distinct L.ID_Location,L.Name from Locations L,FieldService FS,Employees E where L.ID_Location=FS.ID_Location and E.ID_Employee=FS.ID_Employee and FS.ID_Employee=" + Login.ID + " and FS.dueDate >= '" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss tt") + "' order by L.Name";
return((DataTable)OnConn.getDataTable(strSql));
}
}
public static object getLocationList(string searchString, bool serachForTagID, ListView lv)
{
if (!Login.OnLineMode)
{
string strSql;
if (serachForTagID)
{
strSql = " select serverKey as ID_Location,Name,TagID from Locations where ServerKey<>0 and tagid<>'0000000000000000000000D' AND [TagID] Like '%" + searchString + "%' order by UsageCount Desc,Name";
}
else
{
strSql = " select serverKey as ID_Location,Name,TagID from Locations where ServerKey<>0 and tagid<>'0000000000000000000000D' AND [Name] Like '" + searchString + "%' order by UsageCount Desc,Name";
}
DataTable dtList = new DataTable();
using (CEConn localDB = new CEConn())
{
localDB.FillDataTable(ref dtList, strSql);
//lv.Items.Clear();
//string[] subitems = new string[3];
//if (dtList != null && dtList.Rows.Count > 0)
//{
// foreach (DataRow dr in dtList.Rows)
// {
// subitems[0] = dr["ID_Location"].ToString();
// subitems[1] = dr["Name"].ToString();
// subitems[2] = dr["TagID"].ToString();
// lv.Items.Add(new ListViewItem(subitems));
// }
//}
}
//dtt.Columns["ServerKey"].ColumnName = "ID_Location";
return(dtList);
}
else
{
RConnection.RConnection OnConn = new RConnection.RConnection();
OnConn.Url = Login.webConURL;
string strSql;
if (serachForTagID)
{
strSql = " select ID_Location,Name,TagID from Locations where Is_Active=1 and Is_Deleted=0 and tagid<>'0000000000000000000000D' AND [TagID] Like '%" + searchString + "%' order by UsageCount Desc,Name";
}
else
{
strSql = " select ID_Location,Name,TagID from Locations where Is_Active=1 and Is_Deleted=0 and tagid<>'0000000000000000000000D' AND [Name] Like '" + searchString + "%' order by UsageCount Desc,Name";
}
//return (DataTable)OnConn.getDataTable(strSql);
//throw new ApplicationException("Online functionality not implemented yet.");
// ----------------------------------------------
DataTable dtLocs = (DataTable)OnConn.getDataTable(strSql);
//lv.Items.Clear();
//string[] subitems = new string[3];
//foreach (DataRow dr in dtLocs.Rows)
//{
// subitems[0] = dr["ID_Location"].ToString();
// subitems[1] = dr["Name"].ToString();
// subitems[2] = dr["TagID"].ToString();
// lv.Items.Add(new ListViewItem(subitems));
//}
return(dtLocs);
// -------------------------------------------
}
}
public static DataTable getGroups(Int32 ParentID, ListView lv)
{
if (!Login.OnLineMode)
{
DataTable dtList = null;
using (CEConn localDB = new CEConn())
{
string strSql;
strSql = " select ServerKey as ID_AssetGroup,Name from Asset_Groups where ParentID =" + ParentID + "";
localDB.FillDataTable(ref dtList, strSql);
//return dtList;
}
lv.Items.Clear();
if (dtList != null)
{
string[] subitems = new string[3];
foreach (DataRow dr in dtList.Rows)
{
subitems[0] = dr["ID_AssetGroup"].ToString();
subitems[1] = dr["Name"].ToString();
subitems[2] = "";
lv.Items.Add(new ListViewItem(subitems));
}
}
//dtt.Columns["ServerKey"].ColumnName = "ID_Location";
return(null);
}
else
{
RConnection.RConnection OnConn = new RConnection.RConnection();
OnConn.Url = Login.webConURL;
string strSql;
strSql = " select ID_AssetGroup,Name from Asset_Groups where ParentID =" + ParentID + " and Is_Active=1 and Is_Deleted=0";
DataTable dt;
dt = (DataTable)OnConn.getDataTable(strSql);
lv.Items.Clear();
string[] subitems = new string[3];
foreach (DataRow dr in dt.Rows)
{
subitems[0] = dr["ID_AssetGroup"].ToString();
subitems[1] = dr["Name"].ToString();
subitems[2] = "";
lv.Items.Add(new ListViewItem(subitems));
}
//return dt;
return(null);
//throw new ApplicationException("Online functionality not implemented yet.");
}
}