/* 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));
}
}
//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 string Update(Int32 FSID, FSStatusType fst, FSStatusType tst, String Comments)
{
string strSql;
if (!Login.OnLineMode)
{
using (CEConn localDB = new CEConn())
{
strSql = "Update FS_AssetStatus set FSStatus=" + Convert.ToInt32(fst) + ", TaskStatus =" + Convert.ToInt32(tst) + ", comments ='" + Comments + "', Date_Modified = getDate(), ModifiedBy =" + Login.ID + ", ID_Employee =" + Login.ID + ", RowStatus= " + Convert.ToInt32(RowStatus.Modify) + " where ID_FSAssetStatus=" + FSID;
localDB.runQuery(strSql);
return("");
}
}
else
{
//Update FS_AssetStatus
Synchronize.Synchronise OnConn = new Synchronize.Synchronise();
OnConn.Url = Login.webURL;
String result;
result = OnConn.UpdateFS_AssetStatus1(FSID, Convert.ToInt32(fst), Convert.ToInt32(tst), Login.ID, DateTime.Now, Comments);
return(result);
}
}
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 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 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 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 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 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 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 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.");
}
}
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);
}
}
//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 static void AddDocument(String TagID,
List <string> lstAssetTags, List <float> lstQty, String documentNo,
DateTime documentDate, String notes, int documentTypeID,
DateTime dispatchDate)
{
//if (!Login.OnLineMode)
//{
Int32 Skey;
Skey = minServerKey();
// added by vijay 08 Dec 2009
if (Skey == -2)
{
Skey = Skey - 1;
}
using (CEConn localDB = new CEConn())
{
string strSql;
string strSqlDetail;
strSql = " select count(*) from Assets A,Employees E,Locations L, Documents D where D.TagID='" + TagID + "' or A.TagID='" + TagID + "' or E.TagID='" + TagID + "' or L.TagID='" + TagID + "'";
if (Convert.ToInt32(localDB.getScalerValue(strSql)) > 0)
{
throw new ApplicationException("Duplicate Tag ID.");
}
strSql = " select count(*) from Documents D where D.DocumentNo='" + documentNo + "'";
if (Convert.ToInt32(localDB.getScalerValue(strSql)) > 0)
{
throw new ApplicationException("Duplicate Document No.");
}
strSql = " insert into Documents(TagID,documentNo,documentDate,notes,documentTypeID,dispatchDate,Date_Modified,ModifiedBy,RowStatus,serverKey)";
strSql += " values('" + TagID + "','" + documentNo.Replace("'", "''") + "','"
+ documentDate.ToString("yyyy-MM-dd hh:mm:ss") + "','" + notes.Replace("'", "''") + "'," + documentTypeID
+ ",'" + dispatchDate.ToString("yyyy-MM-dd hh:mm:ss") + "',getDate()," + Login.ID + "," + Convert.ToInt32(RowStatus.New) + "," + Skey + ")";
Int32 lstCount = 0;
foreach (string strAssetTag in lstAssetTags)
{
strSqlDetail = " insert into DocumentDetails(Qty,DocumentNo,TagID)";
strSqlDetail += " values(" + lstQty[lstCount] + ",'" + documentNo + "','" + strAssetTag + "')";
localDB.runQuery(strSqlDetail);
}
localDB.runQuery(strSql);
}
//}
//else
//{
// throw new ApplicationException("Online functionality not implemented yet.");
//}
}
public static DataTable getRows()
{
DataTable dtDocs = new DataTable("dtDocs");
string strSql;
using (CEConn localDB = new CEConn())
{
//select D.TagID,D.DocumentNo,D.DocumentTypeID,D.ServerKey,Ds.Qty,Ds.TagID as AssetTagID from DocumentDetails Ds,Documents D where D.DocumentNo=Ds.DocumentNo and D.TAGID='" + TagID + "'
strSql = "select D.TagID,D.DocumentNo,D.DocumentTypeID,D.ServerKey as DocumentID from Documents D"; // where RowStatus=" + Convert.ToInt32(Stat);
localDB.FillDataTable(ref dtDocs, strSql);
}
return(dtDocs);
}
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 static DataTable getRows(RowStatus Stat)
{
using (CEConn localDB = new CEConn())
{
DataTable dtFS = new DataTable("dtFS");
string strSql;
strSql = "select FS.*,A.TagID,A.Name from FS_AssetStatus FS,Assets A where FS.ID_Asset=A.ServerKey and FS.RowStatus=" + Convert.ToInt32(Stat);
localDB.FillDataTable(ref dtFS, strSql);
return(dtFS);
}
}
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 static DataTable getTaskPerformedRows(RowStatus Stat)
{
//The functionality is for insert data to centeralized data.
using (CEConn localDB = new CEConn())
{
DataTable dtFS = new DataTable("dtFS");
string strSql;
strSql = "select ID_Tasks,ID_Employee,ID_Asset,ServerKey from PerformedTask where RowStatus=" + Convert.ToInt32(Stat);
localDB.FillDataTable(ref dtFS, strSql);
return(dtFS);
}
}
public Documents(String TagID)
{
if (!Login.OnLineMode)
{
_lstAsset = new List <Assets>();
_lstQty = new List <double>();
_TagID = TagID;
using (CEConn localDB = new CEConn())
{
string strSql;
strSql = " select D.TagID,D.DocumentNo,D.DocumentID,D.DocumentDate,D.Notes,D.DocumentTypeID,D.DispatchDate,D.ServerKey,Ds.Qty,Ds.TagID as AssetTagID, D.RowStatus from DocumentDetails Ds,Documents D where D.DocumentNo=Ds.DocumentNo and D.TAGID='" + TagID + "'";
SqlCeDataReader dr;
dr = localDB.getReader(strSql);
bool runOnce = false;
while (dr.Read())
{
if (!runOnce)
{
_TagID = (String)dr["TagID"];
if (dr["DocumentNo"] != DBNull.Value)
{
_DocumentNo = (String)dr["DocumentNo"];
}
_DocumentID = Convert.ToInt64(dr["DocumentID"]);
_DocumentDate = Convert.ToDateTime(dr["DocumentDate"]);
if (dr["Notes"] != DBNull.Value)
{
_Notes = (String)dr["Notes"];
}
_DocumentTypeID = Convert.ToInt32(dr["DocumentTypeID"]);
_DispatchDate = Convert.ToDateTime(dr["DispatchDate"]);
_ServerKey = Convert.ToInt32(dr["ServerKey"]);
_RowStatus = Convert.ToInt32(dr["RowStatus"]);
}
Assets A = new Assets((String)dr["AssetTagID"]);
_lstAsset.Add(A);
_lstQty.Add(1);
}
dr.Close();
}
}
else
{
throw new ApplicationException("Online functionality not implemented yet.");
}
}
public static DataTable getNewRows()
{
using (CEConn localDB = new CEConn())
{
DataTable dtLocation = new DataTable("dtLocation");
string strSql;
strSql = "select * from Locations where (RowStatus=" + Convert.ToInt32(RowStatus.New) + " OR ServerKey < 0)";
localDB.FillDataTable(ref dtLocation, strSql);
//strSql = "update Locations set RowStatus=" + Convert.ToInt32(RowStatus.InProcess) + " where RowStatus=" + Convert.ToInt32(RowStatus.New);
//localDB.runQuery(strSql);
return(dtLocation);
}
}
public static DataTable getModifiedRows()
{
using (CEConn localDB = new CEConn())
{
DataTable dtEmployee = new DataTable("dtEmployee");
string strSql;
strSql = "select * from Employees where RowStatus=" + Convert.ToInt32(RowStatus.Modify);
localDB.FillDataTable(ref dtEmployee, strSql);
//strSql = "update Employees set RowStatus=" + Convert.ToInt32(RowStatus.InProcess) + " where RowStatus=" + Convert.ToInt32(RowStatus.New);
//localDB.runQuery(strSql);
return(dtEmployee);
}
}
static bool ValidateUserInOnlineMode(string username, string password)
{
bool result = false;
try
{
int serverKey = CheckUserInOnlineDB(username, password);
if (serverKey == -99) // user not found in online DB.
{
result = true;
}
else if (serverKey == _ID) // online and offline DB has same user with same IDs.
{
result = true;
}
else if (serverKey != _ID) // online and offline DB has same user with different IDs, update the ServerKey of offline DB with ID_Employee of online Key.
{
// Update Server Key of Offline DB.
using (CEConn localDB = new CEConn())
{
string strSql;
strSql = "Update Employees Set ServerKey = " + serverKey;
strSql += " where Employees.username='******' and Employees.password='******'";
int output = localDB.runQuery(strSql);
if (output > 0)
{
_ID = serverKey;
result = true;
}
}
}
// Validate offline user record with online user record
// if also exist in online db, check and update the serverkey field of offline user record with ID_employee
// field of online db.
}
catch (Exception ex)
{
Logger.LogError("Error while validating the offline login user with Online database : " + ex.Message);
}
return(result);
}
public static int getRecordCount(String tblName)
{
using (CEConn localDB = new CEConn())
{
string strSql;
strSql = " select count(*) as TotalRows from " + tblName;
Object o;
o = localDB.getScalerValue(strSql);
if (DBNull.Value == o)
{
return(0);
}
else
{
return(Convert.ToInt32(o));
}
}
}
public static DateTime getLastSyncedDate(String tblName)
{
using (CEConn localDB = new CEConn())
{
string strSql;
strSql = " select min(Date_Modified) as SyncedDate from " + tblName;
Object o;
o = localDB.getScalerValue(strSql);
if (DBNull.Value == o)
{
return(DateTime.Now.AddYears(-10));
}
else
{
return(Convert.ToDateTime(o));
}
}
}
public bool isDocumentTag()
{
if (_isDocumentTag)
{
return(true);
}
if (_isEmployeeTag)
{
return(false);
}
if (_isAssetTag == true)
{
return(false);
}
if (_isLocationTag == true)
{
return(false);
}
using (CEConn localDB = new CEConn())
{
string strSql;
strSql = " select serverKey as DocumentID from Documents where TagID='" + _TagID + "'";
_DataKey = Convert.ToInt32(localDB.getScalerValue(strSql));
}
if (_DataKey != 0)
{
_isDocumentTag = true;
return(true);
}
else
{
//We can assign DataKey here from Asset table.
_isDocumentTag = false;
return(false);
}
}
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 void AssignNewTag(String TagNo)
{
if (!Login.OnLineMode)
{
using (CEConn localDB = new CEConn())
{
string strSql;
if (_RowStatus != Convert.ToInt32(RowStatus.New))
{
_RowStatus = Convert.ToInt32(RowStatus.TagWrite);
}
strSql = " update Locations set TagID='" + TagNo + "', Date_MOdified=getDate(), ModifiedBy=" + Login.ID + ", RowStatus=" + _RowStatus + " where ServerKey=" + _ServerKey;
localDB.runQuery(strSql);
_TagNo = TagNo;
}
}
else
{
throw new ApplicationException("Online Location functionality not implemented yet.");
}
}
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)));
}
}
}
