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 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.");
}
}
/* 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 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.");
}
}
//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 void DeleteLocation(Int32 serverKey)
{
if (!Login.OnLineMode)
{
using (CEConn localDB = new CEConn())
{
string strSql;
strSql = "Delete from Locations ";
strSql += " Where ServerKey = " + serverKey;
localDB.runQuery(strSql);
}
}
else
{
string strSql;
strSql = "Delete from Locations ";
strSql += " Where ID_Location = " + serverKey;
RConnection.RConnection OnConn = new RConnection.RConnection();
OnConn.Url = Login.webConURL;
OnConn.runQuery(strSql);
}
}
public static void EditLocation(String TagID, String LocationNo, String LocationName, Int32 ServerKey, int usageCount)
{
if (!Login.OnLineMode)
{
using (CEConn localDB = new CEConn())
{
string strSql;
strSql = "Update Locations Set Name = '" + LocationName.Replace("'", "''") + "'" + ",LocationNo = '" + LocationNo.Replace("'", "''") + "',Date_Modified = getDate(),";
strSql += "ModifiedBy = " + Login.ID + ",RowStatus = " + Convert.ToInt32(RowStatus.Modify);
strSql += " Where ServerKey = " + ServerKey;
//strSql = " insert into Locations(TagID,Name,LocationNo,Date_Modified,ModifiedBy,RowStatus,serverKey)";
//strSql += " values('" + TagID + "','" + LocationName.Replace("'", "''") + "','" + LocationNo.Replace("'", "''") + "',getDate()," + Login.ID + "," + Convert.ToInt32(RowStatus.New) + "," + Skey + ")";
localDB.runQuery(strSql);
}
}
else
{
string strSql;
strSql = "Update Locations Set Name = '" + LocationName.Replace("'", "''") + "'" + ",LocationNo = '" + LocationNo.Replace("'", "''") + "',Date_Modified = getDate(),";
strSql += "Last_Modified_By = " + Login.ID + ",UsageCount = UsageCount + " + usageCount;
strSql += " Where ID_Location = " + ServerKey;
RConnection.RConnection OnConn = new RConnection.RConnection();
OnConn.Url = Login.webConURL;
OnConn.runQuery(strSql);
}
}
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.");
}
}
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 bool isEmployeeTag()
{
if (_isLocationTag)
{
return(false);
}
if (_isEmployeeTag == true)
{
return(_isEmployeeTag);
}
if (_isAssetTag == true)
{
return(false);
}
if (!Login.OnLineMode)
{
using (CEConn localDB = new CEConn())
{
string strSql;
strSql = " select serverKey as ID_Employee from Employees where TagID='" + _TagID + "'";
_DataKey = Convert.ToInt32(localDB.getScalerValue(strSql));
}
}
else
{
RConnection.RConnection OnConn = new RConnection.RConnection();
OnConn.Url = Login.webConURL;
string strSql;
strSql = " select ID_Employee from Employees where TagID='" + _TagID + "' and Is_Active=1 and Is_Deleted=0";
String strVal = OnConn.getScalerValue(strSql);
if (strVal.Trim().Length != 0)
{
_DataKey = Convert.ToInt32(strVal);
}
else
{
_DataKey = 0;
}
//throw new ApplicationException("Online functionality not implemented yet.");
}
if (_DataKey != 0)
{
_isEmployeeTag = true;
return(true);
}
else
{
//We can assign DataKey here from Asset table.
_isEmployeeTag = false;
return(false);
}
}
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 string getDispatchLocation()
{
string strSql;
if (Login.OnLineMode)
{
RConnection.RConnection OnConn = new RConnection.RConnection();
OnConn.Url = Login.webConURL;
strSql = " Update Locations Set is_deleted=0, is_active=1 Where tagid='0000000000000000000000D' ;";
strSql = strSql + " select Top 1 isNULL(ID_Location,'') from Locations where tagid='0000000000000000000000D' and is_deleted=0 and is_active=1";
return(OnConn.getScalerValue(strSql));
}
else
{
using (CEConn localDB = new CEConn())
{
strSql = " select ServerKey as ID_Location from Locations where tagid='0000000000000000000000D' and ServerKey<>0 ";
return(Convert.ToString(localDB.getScalerValue(strSql)));
}
}
}
public static string getLocationByName(string locName)
{
string strSql;
if (Login.OnLineMode)
{
RConnection.RConnection OnConn = new RConnection.RConnection();
OnConn.Url = Login.webConURL;
strSql = " select Top 1 isNULL(ID_Location,'') from Locations where is_deleted=0 and Name = '" + locName + "'";
return(OnConn.getScalerValue(strSql));
}
else
{
using (CEConn localDB = new CEConn())
{
strSql = " select ServerKey as ID_Location from Locations where ServerKey<>0 and Name='" + locName + "'";
return(Convert.ToString(localDB.getScalerValue(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 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));
}
}
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 void AssignNewTag(String TagNo)
{
Int32 p;
if (_RowStatus != Convert.ToInt32(RowStatus.New))
{
p = Convert.ToInt32(RowStatus.TagWrite);
}
else
{
p = _RowStatus;
}
if (!Login.OnLineMode)
{
using (CEConn localDB = new CEConn())
{
string strSql;
strSql = " update Employees set TagID='" + TagNo + "' , Date_MOdified=getDate(), ModifiedBy=" + Login.ID + ", RowStatus=" + p + " where ServerKey=" + _ServerKey;
localDB.runQuery(strSql);
_TagID = TagNo;
_RowStatus = p;
}
}
else
{
RConnection.RConnection OnConn = new RConnection.RConnection();
OnConn.Url = Login.webConURL;
string strSql;
strSql = " update Employees set TagID='" + TagNo + "' , Date_MOdified=getDate(), Last_Modified_By=" + Login.ID + " where ID_Employee=" + _ServerKey;
OnConn.runQuery(strSql);
_TagID = TagNo;
_RowStatus = p;
//throw new ApplicationException("Online Employee functionality not implemented yet.");
}
}
public static int CheckUserInOnlineDB(string username, string password)
{
int ID_Employee = -99;
try
{
webURL = urlStr + "Synchronise.asmx";
webConURL = urlStr + "RConnection.asmx";
RConnection.RConnection OnConn = new RConnection.RConnection();
OnConn.Url = Login.webConURL;
DataTable dt;
dt = (DataTable)OnConn.GetLogin(username, password);
if (dt.Rows.Count > 0)
{
ID_Employee = Convert.ToInt32(dt.Rows[0]["ServerKey"]);
}
//throw new ApplicationException("Online functionality not implemented yet.");
//Do the Online Coding.
}
catch (System.Web.Services.Protocols.SoapException ex)
{
Logger.LogError("Error in CheckUserInOnlineDB method : " + ex.Message);
ID_Employee = -99;
// return false;
}
catch (Exception ep)
{
Logger.LogError("Error in CheckUserInOnlineDB method : " + ep.Message);
// return false;
ID_Employee = -99;
}
return(ID_Employee);
}
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 bool isLocationTag()
{
if (_isEmployeeTag)
{
return(false);
}
if (_isLocationTag == true)
{
return(_isLocationTag);
}
if (_isAssetTag == true)
{
return(false);
}
if (!Login.OnLineMode)
{
using (CEConn localDB = new CEConn())
{
string strSql;
strSql = " select serverKey as ID_Location from Locations where TagID='" + _TagID + "'";
_DataKey = Convert.ToInt32(localDB.getScalerValue(strSql));
}
}
else
{
RConnection.RConnection OnConn = new RConnection.RConnection();
OnConn.Url = Login.webConURL;
string strSql;
try
{
strSql = " select ID_Location from Locations where TagID='" + _TagID + "'";
string oStr = OnConn.getScalerValue(strSql);
if (oStr.ToString().Length != 0)
{
_DataKey = Convert.ToInt32(oStr);
}
else
{
_DataKey = 0;
}
}
catch (Exception ex)
{
strSql = ex.ToString();
_DataKey = 0;
Logger.LogError(ex.Message);
}
// throw new ApplicationException("Online functionality not implemented yet.");
}
if (_DataKey != 0)
{
_isLocationTag = true;
return(true);
}
else
{
//We can assign DataKey here from Asset table.
_isLocationTag = false;
return(false);
}
}
public static bool verifyPassword(string username, string password)
{
bool result = false;
try
{
webURL = urlStr + "Synchronise.asmx";
webConURL = urlStr + "RConnection.asmx";
if (!Login.OnLineMode)
{
using (CEConn localDB = new CEConn())
{
string strSql;
strSql = "select E.*,grp.name as Role from Employees as E inner join Master_SecurityGroups grp ";
strSql += " on grp.ServerKey=E.ID_SecurityGroup ";
strSql += " where E.username='******' and E.password='******'";
SqlCeDataReader sr = localDB.getReader(strSql);
while (sr.Read())
{
_ID = Convert.ToInt32(sr["ServerKey"]);
_TagID = (String)sr["TagID"];
_EmpNo = (String)sr["EmpNo"];
_UserName = (String)sr["UserName"];
//_Password = (String)sr["Password"];
_Password = password;
_RoleId = Convert.ToInt32(sr["ID_SecurityGroup"]);
_Role = (String)sr["Role"];
// sr.Close();
result = true;
}
sr.Close();
//return false;
}
if (result == true)
{
if (ValidateUserInOnlineMode(username, password))
{
return(true);
}
else
{
return(false);
}
}
else
{
return(result);
}
}
else
{
RConnection.RConnection OnConn = new RConnection.RConnection();
OnConn.Url = Login.webConURL;
DataTable dt;
dt = (DataTable)OnConn.GetLogin(username, password);
if (dt.Rows.Count > 0)
{
_ID = Convert.ToInt32(dt.Rows[0]["ServerKey"]);
_TagID = (String)dt.Rows[0]["TagID"];
_EmpNo = (String)dt.Rows[0]["EmpNo"];
_UserName = (String)dt.Rows[0]["UserName"];
//_Password = (String)dt.Rows[0]["Password"];
_Password = password;
_RoleId = Convert.ToInt32(dt.Rows[0]["ID_SecurityGroup"]);
_Role = (String)dt.Rows[0]["Role"];
return(true);
}
return(false);
//throw new ApplicationException("Online functionality not implemented yet.");
//Do the Online Coding.
}
}
catch (System.Web.Services.Protocols.SoapException ex)
{
if (ex.Actor.ToString().ToLower().Trim() == "getlogin")
{
err = "Request from innvalid IP address.";
}
else
{
err = "Soap Exception";
}
Logger.LogError(ex.Message);
throw new Exception(ex.Message);
}
catch (Exception ep)
{
err = ep.Message;
Logger.LogError(ep.Message);
throw ep;
}
}
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.");
}
}
