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."); } }