public static System.Data.DataTable GetAllJoinData(int deviceCode)
{
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
try
{
//both
string query = "";
if (deviceCode != 0)
{
query = string.Format(@"select * from AVLDevice where code in (SELECT ad.Code FROM AVLJoinDevice ajd inner join AVLDevice ad on (ad.Code =ajd.[parentDeviceCode] or ad.Code=ajd.childDeviceCode )where [childDeviceCode]={0} or [parentDeviceCode]={0})
", deviceCode);
}
else
{
return(null);
}
DB.setQuery(query);
return(DB.Query_DataTable());
}
catch (Exception ex)
{
ClassLibrary.JSystem.Except.AddException(ex);
return(null);
}
finally
{
DB.Dispose();
}
}
public static System.Data.DataTable GetData(int chidDevice = 0, int parentDevice = 0)
{
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
try
{
//both
string query = ""; // string.Format(@"SELECT * FROM AVLJoinDevice where [childDeviceCode]={0} and parentDeviceCode={1}", chidDevice,parentDevice);
if (parentDevice == 0 && chidDevice > 0) //just child device code
{
query = string.Format(@"SELECT * FROM AVLJoinDevice where [childDeviceCode]={0}", chidDevice);
}
if (parentDevice > 0 && chidDevice == 0)//just parent device code
{
query = string.Format(@"SELECT * FROM AVLJoinDevice where [parentDeviceCode]={0}", parentDevice);
}
// if (parentDevice == 0 && chidDevice == 0) //none
// query = "SELECT * FROM AVLJoinDevice ";
DB.setQuery(query);
return(DB.Query_DataTable());
}
catch (Exception ex)
{
ClassLibrary.JSystem.Except.AddException(ex);
return(null);
}
finally
{
DB.Dispose();
}
}
public bool Find(int pCode, bool pSetDate = false)
{
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
try
{
DB.setQuery("select * from ReferOrderUsers where PostCode=" + ClassLibrary.JMainFrame.CurrentPostCode + " and PostCodeUser=" + pCode);
System.Data.DataTable DT = DB.Query_DataTable();
if (DT.Rows.Count == 1)
{
if (pSetDate)
{
ClassLibrary.JTable.SetToClassProperty(this, DT.Rows[0]);
}
return(true);
}
else
{
return(false);
}
}
finally
{
DB.Dispose();
}
}
public static System.Data.DataTable GetDataTableAllCars(int pCode = 0)
{
if (!ClassLibrary.JPermission.CheckPermission("AVL.Coordinate.Traditional.JTransactionCars.GetDataTableAllCars"))
{
return(null);
}
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
try
{
string query = @"SELECT CarID
,[LastLatitude]
, LastLongitude
, LastDate
FROM [avlsite_com_1].[dbo].[Cars] WHERE 1=1 ";
if (pCode != 0)
{
query += " AND CarID=" + pCode;
}
// query += " AND "+ ClassLibrary.JPermission.getObjectSql("AVL.Coordinate.Traditional.JTransactionCars.GetDataTableAllCars", "TransactionCars.CarID");
DB.setQuery(query);
return(DB.Query_DataTable());
}
catch (Exception ex)
{
ClassLibrary.JSystem.Except.AddException(ex);
return(null);
}
finally
{
DB.Dispose();
}
}
/// <summary>
/// گرفتن تمام اطلاعات که چه دستگاه زیرمجموعه یا سرگروه باشد
/// </summary>
/// <param name="devieCode"></param>
/// <returns></returns>
public static System.Data.DataTable GetAllData(int deviceCode)
{
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
try
{
//both
string query = "";
if (deviceCode != 0)
{
query = string.Format(@"SELECT * FROM AVLJoinDevice where [childDeviceCode]={0} or [parentDeviceCode]={0}", deviceCode);
}
else
{
return(null);
}
DB.setQuery(query);
return(DB.Query_DataTable());
}
catch (Exception ex)
{
ClassLibrary.JSystem.Except.AddException(ex);
return(null);
}
finally
{
DB.Dispose();
}
}
public static System.Data.DataTable GetAreas(string pCode, string ObjectCode, bool IsGeofence = false)
{
if (!ClassLibrary.JPermission.CheckPermission("AVL.Area.JAreas.GetAreas"))
{
return(null);
}
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
try
{
string query = @"SELECT * FROM [AVLArea] WHERE [IsGeofence] = '" + IsGeofence.ToString() + "'";
query += " AND personCode=" + pCode + " AND (ObjectsCodes LIKE '%" + ObjectCode + ",%')";
// query += ClassLibrary.JPermission.getObjectSql("AVL.Area.JAreas.GetAreas", "AVLArea.Code");
DB.setQuery(query);
return(DB.Query_DataTable());
}
catch (Exception ex)
{
ClassLibrary.JSystem.Except.AddException(ex);
return(null);
}
finally
{
DB.Dispose();
}
}
public static System.Data.DataTable GetDataTable(int pCode)
{
if (!ClassLibrary.JPermission.CheckPermission("AVL.Area.JAreas.GetDataTable"))
{
return(null);
}
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
try
{
string query = @"SELECT * FROM [AVLArea] WHERE ";
query += " personCode=" + pCode;
// query += " AND "+ClassLibrary.JPermission.getObjectSql("AVL.Area.JAreas.GetDataTable", "AVLArea.Code");
DB.setQuery(query);
return(DB.Query_DataTable());
}
catch (Exception ex)
{
ClassLibrary.JSystem.Except.AddException(ex);
return(null);
}
finally
{
DB.Dispose();
}
}
public static JObjectList FindByObjectCode(string objectCode, string userCode)
{
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
try
{
string query = "SELECT * FROM AVLObjectList WHERE ObjectCode=" + objectCode;
if (int.Parse(userCode) > 0)
{
query += " AND personCode=" + userCode;
}
DB.setQuery(query);
DB.Query_DataReader();
if (DB.DataReader.Read())
{
AVL.ObjectList.JObjectList ol = new JObjectList();
ClassLibrary.JTable.SetToClassProperty(ol, DB.DataReader);
return(ol);
}
return(null);
}
finally
{
DB.Dispose();
}
}
/// <summary>
/// Insert Data with specified query.
/// </summary>
/// <param name="Query"></param>
/// <param name="isWeb"></param>
/// <returns></returns>
public int Insert(string Query, bool isWeb = false)
{
if (!ClassLibrary.JPermission.CheckPermission("AVL.GPSData.JSocketManager.Insert"))
{
return(0);
}
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
try
{
string query = Query;
// + " WHERE " + ClassLibrary.JPermission.getObjectSql("AVL.GPSData.JSocketManager", "JSocketManager.Code");
DB.setQuery(query);
return(DB.Query_DataTable().Rows.Count);
}
catch (Exception ex)
{
ClassLibrary.JSystem.Except.AddException(ex);
return(0);
}
finally
{
DB.Dispose();
}
}
//اگر کد برابر صفر باشد تنها مقادیر مربوط به دیتای کاربر فعلی را می گیرد.
public bool GetData(int code = 0, int userCode = -1)
{
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
try
{
if (code == 0)
{
DB.setQuery("select top 1 * from AVLCash where userCode=" + userCode);
}
else
{
DB.setQuery("select top 1 * from AVLCash where code=" + code + " AND userCode=" + userCode);
}
DB.Query_DataReader();
if (DB.DataReader.Read())
{
ClassLibrary.JTable.SetToClassProperty(this, DB.DataReader);
return(true);
}
return(false);
}
finally
{
DB.Dispose();
}
}
public bool GetData(string pCode, string personCode)
{
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
try
{
DB.setQuery("select top 1 * from AVLObjectList where Code=" + pCode + " AND personCode=" + personCode);
if (int.Parse(personCode) < 1)
{
DB.setQuery("select top 1 * from AVLObjectList where Code=" + pCode);
}
if (int.Parse(pCode) < 1)
{
DB.setQuery("select top 1 * from AVLObjectList where personCode=" + personCode);
}
DB.Query_DataReader();
if (DB.DataReader.Read())
{
ClassLibrary.JTable.SetToClassProperty(this, DB.DataReader);
return(true);
}
return(false);
}
finally
{
DB.Dispose();
}
}
public static System.Data.DataTable GetDataTable(int pCode = 0, bool CheckProceced = false)
{
if (!ClassLibrary.JPermission.CheckPermission("AVL.GPSData.JSocketManagers.GetDataTable"))
{
return(null);
}
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
try
{
string query = @"SELECT * FROM [ClsSocketClientDataManager]"
+ " WHERE 1=1 ";// + ClassLibrary.JPermission.getObjectSql("AVL.GPSData.JSocketManagers.GetDataTable", "JSocketManager.Code");
if (CheckProceced)
{
query += " AND [IsProceced]='false'";
}
if (pCode > 0)
{
query += " AND Code=" + pCode;
}
DB.setQuery(query);
return(DB.Query_DataTable());
}
catch (Exception ex)
{
ClassLibrary.JSystem.Except.AddException(ex);
return(null);
}
finally
{
DB.Dispose();
}
}
public static System.Data.DataTable GetDistinctDataTable(int pCode, string DistinctColumn)
{
if (!ClassLibrary.JPermission.CheckPermission("AVL.RegisterDeivce.JRegisterDevices.GetDataTable"))
{
return(null);
}
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
try
{
string query = @"SELECT Distinct " + DistinctColumn + ",Count(Code) as count FROM AVLDevice";
if (pCode > 0)
{
query += " where personCode=" + pCode;
}
query += " group by " + DistinctColumn;
DB.setQuery(query);
return(DB.Query_DataTable());
}
catch (Exception ex)
{
ClassLibrary.JSystem.Except.AddException(ex);
return(null);
}
finally
{
DB.Dispose();
}
}
public static System.Data.DataTable GetDataTable(int pCode)
{
if (!ClassLibrary.JPermission.CheckPermission("AVL.RegisterDeivce.JRegisterDevices.GetDataTable"))
{
return(null);
}
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
try
{
string query = @"SELECT * FROM AVLDevice";
if (pCode > 0)
{
query += " where personCode=" + pCode;
}
// + " WHERE " + ClassLibrary.JPermission.getObjectSql("AVL.RegisterDeivce.JRegisterDevices.GetDataTable", "AVLRegisterDevice.Code");
DB.setQuery(query);
return(DB.Query_DataTable());
}
catch (Exception ex)
{
ClassLibrary.JSystem.Except.AddException(ex);
return(null);
}
finally
{
DB.Dispose();
}
}
public static bool SendToOldDatabase(byte[] pData)
{
System.Data.SqlClient.SqlConnectionStringBuilder constr = new System.Data.SqlClient.SqlConnectionStringBuilder();
constr.DataSource = ".";
constr.InitialCatalog = "BusAvlSendLogs";
constr.UserID = "sa";
constr.Password = "******";
ClassLibrary.JDataBase db = new ClassLibrary.JDataBase(constr);
try
{
db.setQuery("Insert into SendLogs(SendLog) VALUES(@SendLog)");
db.AddParams("SendLog", pData);
if (db.Query_Execute() >= 0)
{
return(true);
}
return(false);
}
catch (Exception ex)
{
return(false);
}
finally
{
db.Dispose();
}
}
public static System.Data.DataTable GetDataTable(int pCode)
{
if (!ClassLibrary.JPermission.CheckPermission("AVL.Coordinate.JCoordinates.GetDataTable"))
{
return(null);
}
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
try
{
string query = @"SELECT * FROM [AVLCoordinate] WHERE 1=1 ";
if (pCode != 0)
{
query += " AND Code=" + pCode;
}
// query += " AND "+ ClassLibrary.JPermission.getObjectSql("AVL.Coordinate.JCoordinates.GetDataTable", "AVLCoordinate.Code");
DB.setQuery(query);
return(DB.Query_DataTable());
}
catch (Exception ex)
{
ClassLibrary.JSystem.Except.AddException(ex);
return(null);
}
finally
{
DB.Dispose();
}
}
public static System.Data.DataTable GetDataTable(int deviceCode)
{
if (!ClassLibrary.JPermission.CheckPermission("AVL.RegisterDeivce.DeviceObjectHistory.JDeviceObjectHistories.GetDataTable"))
{
return(null);
}
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
try
{
string query = @"SELECT * FROM AVLDeviceObjectHistory";
query += " where DeviceCode=" + deviceCode;
DB.setQuery(query);
return(DB.Query_DataTable());
}
catch (Exception ex)
{
ClassLibrary.JSystem.Except.AddException(ex);
return(null);
}
finally
{
DB.Dispose();
}
}
public static System.Data.DataTable GetDataTableInDate(string id = "0", string fromDate = "1/1/1", string toDate = "1/1/1")
{
if (!ClassLibrary.JPermission.CheckPermission("AVL.Coordinate.Traditional.JTransactionCars.GetDataTableInDate"))
{
return(null);
}
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
try
{
string query = @"SELECT CarID
,Latitude
, Longitude
, LastDate
FROM [avlsite_com_1].[dbo].[Cars] WHERE LastLatitude IS NOT NULL AND LastLongitude IS NOT NULL AND
LastDate BETWEEN DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)
AND DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 1)
";
//query += " AND "+ClassLibrary.JPermission.getObjectSql("AVL.Coordinate.Traditional.JTransactionCars.GetDataTableInDate", "TransactionCars.CarID");
DB.setQuery(query);
return(DB.Query_DataTable());
}
catch (Exception ex)
{
ClassLibrary.JSystem.Except.AddException(ex);
return(null);
}
finally
{
DB.Dispose();
}
}
public static System.Data.DataTable GetAreas(bool checkPermission)
{
bool hasPermission = true;
if (checkPermission)
{
ClassLibrary.JPermission.CheckPermission("AVL.Area.JAreas.GetAreas");
}
if (!hasPermission)
{
return(null);
}
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
try
{
string query = @"SELECT * FROM [AVLArea] ";
DB.setQuery(query);
return(DB.Query_DataTable());
}
catch (Exception ex)
{
ClassLibrary.JSystem.Except.AddException(ex);
return(null);
}
finally
{
DB.Dispose();
}
}
public bool Varified()
{
ClassLibrary.JDataBase db = ClassLibrary.JGlobal.MainFrame.GetDBO();
try
{
db.setQuery(@"select * from AVLUserVarify
WHERE userID=@userID AND phoneVarified = @phoneVarified ");
db.AddParams("@userID", this.userID);
db.AddParams("@phoneVarified", true);
if (db.Query_DataTable().Rows.Count > 0)
{
return(true);
}
return(false);
}
catch (Exception ex)
{
ClassLibrary.JSystem.Except.AddException(ex);
return(false);
}
finally
{
db.Dispose();
}
}
/// <summary>
/// Returns latest gateaway invoice inserted.
/// </summary>
/// <returns></returns>
public static JAVLPaid GetLatestGateawayInvoice(int userCode, string RefId)
{
JAVLPaid paid = new JAVLPaid();
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
try
{
DB.setQuery(@"select top 1 * from AVLPaid
where invoiceNumber='g'
AND State=0
AND type='G'
AND userCode=" + userCode +
" AND documentDateTime BETWEEN '" + DateTime.Now.AddMinutes(-15) + "' AND '" + DateTime.Now.ToString() + "'" +
" AND RefId='" + RefId + "'");
DB.Query_DataReader();
if (DB.DataReader.Read())
{
ClassLibrary.JTable.SetToClassProperty(paid, DB.DataReader);
}
return(paid);
}
finally
{
DB.Dispose();
}
}
static public System.Data.DataTable GetDataTable(int patentUserCode, int CurrentUserCode)
{
if (!ClassLibrary.JPermission.CheckPermission("AVL.SubUserObjects.JSubUserObjectss.GetDataTable"))
{
return(null);
}
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
try
{
string query = @"SELECT * FROM [AVLSubUserObjects]";
query += " where parentUserCode=" + patentUserCode;
query += " AND userCode=" + CurrentUserCode;
DB.setQuery(query);
return(DB.Query_DataTable());
}
catch (Exception ex)
{
ClassLibrary.JSystem.Except.AddException(ex);
return(null);
}
finally
{
DB.Dispose();
}
}
public static System.Data.DataTable GetDataTable(int objectCode, int pCode)
{
if (!ClassLibrary.JPermission.CheckPermission("AVL.ObjectList.JObjectList.GetDataTable"))
{
return(null);
}
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
try
{
string query = @"SELECT * FROM AVLObjectList"
+ " WHERE Code=" + objectCode + " AND personCode=" + pCode;// +" AND " + ClassLibrary.JPermission.getObjectSql("AVL.ObjectList.JObjectList.GetDataTable", "AVLObjectList.Code");
DB.setQuery(query);
return(DB.Query_DataTable());
}
catch (Exception ex)
{
ClassLibrary.JSystem.Except.AddException(ex);
return(null);
}
finally
{
DB.Dispose();
}
}
public static System.Data.DataTable GetDataTable(int pCode = 0)
{
if (!ClassLibrary.JPermission.CheckPermission("AVL.UserObjects.JUserObjects.GetDataTable"))
{
return(null);
}
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
try
{
string query = @"SELECT * FROM AVLUserObjects"
+ " WHERE 1=1 ";// +ClassLibrary.JPermission.getObjectSql("AVL.UserObjects.JUserObjects.GetDataTable", "AVLUserObject.Code");
if (pCode != 0)
{
query += " AND personCode= " + pCode.ToString() + " AND isActive='true' ";
}
DB.setQuery(query);
return(DB.Query_DataTable());
}
catch (Exception ex)
{
ClassLibrary.JSystem.Except.AddException(ex);
return(null);
}
finally
{
DB.Dispose();
}
}
public static System.Data.DataTable GetDataTable(int pCode = 0)
{
if (!ClassLibrary.JPermission.CheckPermission("AVL.Purchase.JPurchasePlans.GetDataTable"))
{
return(null);
}
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
try
{
string query = @"SELECT * FROM AVLPurchasePlan";
// + " WHERE " + ClassLibrary.JPermission.getObjectSql("AVL.Purchase.JPurchasePlans.GetDataTable", "AVLPurchasePlan.Code");
DB.setQuery(query);
return(DB.Query_DataTable());
}
catch (Exception ex)
{
ClassLibrary.JSystem.Except.AddException(ex);
return(null);
}
finally
{
DB.Dispose();
}
}
public static System.Data.DataTable GetDataTable(int pCode = 0)
{
if (!ClassLibrary.JPermission.CheckPermission("AVL.Device.JDeviceModels.GetDataTable"))
{
return(null);
}
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
try
{
string query = @"SELECT * FROM DeviceModel";
// + " WHERE " + ClassLibrary.JPermission.getObjectSql("AVL.Device.JDeviceModels.GetDataTable", "AVLDeviceModel.Code");
if (pCode > 0)
{
query += " WHERE Code=" + pCode.ToString();
}
DB.setQuery(query);
return(DB.Query_DataTable());
}
catch (Exception ex)
{
ClassLibrary.JSystem.Except.AddException(ex);
return(null);
}
finally
{
DB.Dispose();
}
}
private void lbGroup_MouseDoubleClick(object sender, MouseEventArgs e)
{
_SelectedItem = lbGroup.SelectedItem;
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
try
{
DB.setQuery("select * from refGroupName where PostCode=" + ClassLibrary.JMainFrame.CurrentPostCode.ToString() + " and groupName=N'" + (_SelectedItem as ClassLibrary.JKeyValue).Value + "' order by GroupName");
DataTable DT = DB.Query_DataTable();
for (int i = 0; i < chkUsers.Items.Count; i++)
{
chkUsers.SetItemChecked(i, false);
}
foreach (DataRow dr in DT.Rows)
{
for (int i = 0; i < chkUsers.Items.Count; i++)
{
if ((chkUsers.Items[i] as ClassLibrary.JKeyValue).Value.ToString() == dr["PostCodeGuest"].ToString())
{
chkUsers.SetItemChecked(i, true);
}
}
}
}
catch
{
}
finally
{
DB.Dispose();
}
}
public void _SetForm()
{
DataTable DT = Employment.JEOrganizationChart.GetAllData();
chkUsers.Items.Clear();
foreach (DataRow dr in DT.Rows)
{
ClassLibrary.JKeyValue jKeyValue = new ClassLibrary.JKeyValue();
jKeyValue.Value = dr["Code"];
jKeyValue.Key = dr["Full_Title"].ToString();
chkUsers.Items.Add(jKeyValue);
}
lbGroup.Items.Clear();
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
try {
DB.setQuery("select GroupName from refGroupName where PostCode=" + ClassLibrary.JMainFrame.CurrentPostCode.ToString() + " group by GroupName order by GroupName");
DT = DB.Query_DataTable();
foreach (DataRow dr in DT.Rows)
{
ClassLibrary.JKeyValue jKeyValue = new ClassLibrary.JKeyValue();
jKeyValue.Value = dr["GroupName"].ToString();
jKeyValue.Key = dr["GroupName"].ToString();
lbGroup.Items.Add(jKeyValue);
}
}
catch
{
}
finally
{
DB.Dispose();
}
}
public bool Save()
{
ManagementShares.TransferRequest.JRequestSell SellRequest = new ManagementShares.TransferRequest.JRequestSell();
SellRequest.GetData(Code);
SellRequest.Status = Convert.ToInt32(cmbStatus.SelectedValue);
ClassLibrary.JDataBase JDB = new ClassLibrary.JDataBase();
bool Res = SellRequest.Update(JDB);
JDB.Dispose();
return(Res);
}
public static bool AddAVL(DataTable pDT, string pErrorCommand)
{
if (pDT == null || pDT.Rows.Count == 0)
{
return(true);
}
string fileName = Guid.NewGuid().ToString();
BusManagment.Transaction.JTicketTransactions.WriteDataTableToFile(pDT, fileName, false);
ClassLibrary.JDataBase db = new ClassLibrary.JDataBase();
int BusSerial = 0;
try
{
if (pDT.Rows.Count >= 1)
{
int.TryParse(pDT.Rows[0]["BusSerial"].ToString(), out BusSerial);
}
if (BusSerial > 0 && BusSerial < 10000)
{
if (semaphore[BusSerial] == null)
{
semaphore[BusSerial] = new Semaphore(1, 1);
}
semaphore[BusSerial].WaitOne();
}
db.DataTypesName = "dbo.AVLPointList";
db.setQuery(@"EXEC SP_InsertAUTAvlTransactionWithDataTable @List");
db.AddParams("@List", pDT);
db.CommandTimeout = 60 * pDT.Rows.Count;
db.Query_Execute();
if (!db.Error)
{
BusManagment.Transaction.JTicketTransactions.Deleted(fileName, false);
}
return(true);
}
catch (Exception ex)
{
return(false);
}
finally
{
if (BusSerial > 0 && BusSerial < 10000)
{
semaphore[BusSerial].Release();
}
db.Dispose();
}
}