public static DataTable GetDataTable(string buses = "")
{
ClassLibrary.JDataBase db = new ClassLibrary.JDataBase();
try
{
db.setQuery("Select * from AUTAvlTransaction where Code in (select MAX(code) From AUTAvlTransaction GROUP BY BUSCode) AND BusCode in(" + buses + ")");
return(db.Query_DataTable());
}
finally
{
db.Dispose();
}
}
public static DataTable GetDataTable(string buses, DateTime dateA, DateTime dateB)
{
ClassLibrary.JDataBase db = new ClassLibrary.JDataBase();
try
{
db.setQuery("select * from AUTAVLTransactionCache Where BusCode in (" + buses + ") and [EventDate]>= CAST('" + dateA.ToString("yyyy-MM-dd HH:mm:ss") + "' as datetime) and [EventDate]< CAST('" + dateB.ToString("yyyy-MM-dd HH:mm:ss") + "' as datetime) order by [EventDate] AND BusCode in (select MAX(code) From AUTAVLTransactionCache GROUP BY BUSCode)");
return(db.Query_DataTable());
}
finally
{
db.Dispose();
}
}
public static System.Data.DataTable FindByLabel(string label, int userCode)
{
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
System.Data.SqlClient.SqlDataReader dr;
try
{
DB.setQuery("SELECT ObjectCode FROM AVLObjectList WHERE Type=N'" + label + "' AND personCode=" + userCode);
return(DB.Query_DataTable());
}
finally
{
DB.Dispose();
}
}
public void _SetForm()
{
if (Code > 0)
{
ClassLibrary.JDataBase db = new ClassLibrary.JDataBase();
db.setQuery(@"select Code,Text from entSharePriceText where Code = " + Code);
System.Data.DataTable Dt = new System.Data.DataTable();
Dt = db.Query_DataTable();
if (Dt.Rows.Count > 0)
{
txtText.Text = Dt.Rows[0]["Text"].ToString();
}
}
}
public static int GetNumberOpenFactor()
{
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
try
{
DB.setQuery("select Count(*) c from AccFactor where payState=0 AND userCode=" + WebClassLibrary.SessionManager.Current.MainFrame.CurrentUserCode);
if (DB.Query_DataTable().Rows.Count == 0)
{
return(0);
}
else
{
return((int)DB.Query_DataTable().Rows[0]["c"]);
}
}
catch
{
}
finally
{
DB.Dispose();
}
return(0);
}
public static System.Data.DataTable GetDataTablePoints(string devicecode = "0", string fromDate = "1/1/1", string toDate = "1/1/1", string StartRow = "0", string LastRow = "", bool checkPermission = true)
{
bool haspermission = true;
if (checkPermission)
{
haspermission = (ClassLibrary.JPermission.CheckPermission("AVL.Coordinate.Traditional.JTransactionCars.GetDataTablePoints"));
}
if (!haspermission)
{
return(null);
}
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
try
{
string query = @"select * from (SELECT
row_number() over(order by [DeviceSendDateTime] ) as Row, *
FROM [AVLCoordinate] where
lat IS NOT NULL
AND lng IS NOT NULL
AND DeviceSendDateTime BETWEEN '" + fromDate +
"' AND '" + toDate +
"' AND DeviceCode = " + devicecode;
if (!string.IsNullOrEmpty(LastRow))
{
query += string.Format(" ) a WHERE a.Row BETWEEN {0} AND {1}", StartRow, LastRow);
}
else
{
query += ") a";
}
query += " order by DeviceSendDateTime";
DB.setQuery(query);
return(DB.Query_DataTable());
}
catch (Exception ex)
{
ClassLibrary.JSystem.Except.AddException(ex);
return(null);
}
finally
{
DB.Dispose();
}
}
public static DataTable GetBusDetails(string BusSerial)
{
ClassLibrary.JDataBase db = new ClassLibrary.JDataBase();
try
{
db.setQuery(@"select B.Code, B.BUSNumber, A.Capacity, A.Plaque, PO.Name OwnerName, PO.Fam OwnerFam from AUTBus B
left join AUTAutomobile A on A.code = B.CarCode
left join AUTBusOwner O on O.BusCode = B.Code
left join clsPerson PO on PO.Code = O.CodePerson
where BUSNumber = " + BusSerial);
return(db.Query_DataTable());
}
finally
{
db.Dispose();
}
}
public static DataTable GetDataTable(int lineCode = 0, int PathType = 0)
{
string where = "";
if (lineCode > 0)
{
where = " Where LineCode=" + lineCode + " and PathType = " + PathType;
}
ClassLibrary.JDataBase db = new ClassLibrary.JDataBase();
try
{
db.setQuery("Select * From AUTFleetLinePoints " + where + " Order By OrderNo ASC");
return(db.Query_DataTable());
}
finally
{
db.Dispose();
}
}
public static System.Data.DataTable GetDataTablePoints(string id = "0", string fromDate = "1/1/1", string toDate = "1/1/1", string StartRow = "0", string LastRow = "")
{
if (!ClassLibrary.JPermission.CheckPermission("AVL.Coordinate.Traditional.JTransactionCars.GetDataTablePoints"))
{
return(null);
}
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
try
{
string query = @"select * from (SELECT
row_number() over(order by [DateSend] ) as Row, [TransactionID]
,[CarID]
,[Longitude]
,[Latitude]
,[Altitude]
,[DateSend]
,[DateReceive]
FROM [avlsite_com_1].[dbo].[TransactionCars] where Latitude IS NOT NULL
AND Longitude IS NOT NULL
AND DateSend BETWEEN '" + fromDate +
"' AND '" + toDate +
"' AND CarID=" + id;
// query += " AND "+ClassLibrary.JPermission.getObjectSql("AVL.Coordinate.Traditional.JTransactionCars.GetDataTablePoints", "TransactionCars.CarID");
if (!string.IsNullOrEmpty(LastRow))
{
query += string.Format(" ) a WHERE a.Row BETWEEN {0} AND {1}", StartRow, LastRow);
}
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(bool checkpermission, string pParameters, int pCode = 0)
{
bool haspermission = true;
if (checkpermission)
{
haspermission = ClassLibrary.JPermission.CheckPermission("AVL.RegisterDeivce.JRegisterDevices.GetDataTable");
}
if (!haspermission)
{
return(null);
}
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
string parameters = "";
try
{
parameters = pParameters;
}
catch { parameters = ""; }
try
{
string query = string.Format(@"SELECT * FROM AVLDevice WHERE 1=1 {0} ", parameters);
if (pCode > 0)
{
query += " AND 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 System.Data.DataTable GetUserAllDevices(string imei)
{
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
try
{
string query = string.Format(@"SELECT * FROM [AVLDB].[dbo].[AVLDevice] where personCode=(select top 1 personCode from AVLDevice WHERE IMEI='" + imei + "')");
DB.setQuery(query);
return(DB.Query_DataTable());
}
catch (Exception ex)
{
ClassLibrary.JSystem.Except.AddException(ex);
return(null);
}
finally
{
DB.Dispose();
}
}
public static DataTable GetDataTableNew(int lineCode = 0, int PathType = 0)
{
string where = "";
if (lineCode > 0)
{
where = "and als.LineCode=" + lineCode + " and IsBack = " + PathType;
}
ClassLibrary.JDataBase db = new ClassLibrary.JDataBase();
try
{
db.setQuery(@"select ats.Lat latitude,ats.Lng longitude from autlinestation als
left join autstation ats on als.StationCode = ats.Code
where linecode not in(select LineCode from AUTFleetLinePoints where pathtype=" + PathType + "+1) " + where + " Order By Priority");
return(db.Query_DataTable());
}
finally
{
db.Dispose();
}
}
public static System.Data.DataTable GetDataTableToday(int pCode)
{
if (!ClassLibrary.JPermission.CheckPermission("AVL.ObjectList.JObjectList.GetDataTable"))
{
return(null);
}
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
try
{
string query;
try
{
//string parameters = WebClassLibrary.SessionManager.Current.Session["OnlineMapLastFilter"].ToString();
}
catch { }
query = @"SELECT ol.* FROM AVLObjectList ol inner join AVLCash c On c.userCode=ol.personCode "
+ " WHERE ol.personCode=" + pCode + " AND ol.lastSendDate > DATEADD(hour,-24,getdate()) AND (c.paid>0) ";// ";//" AND " + ClassLibrary.JPermission.getObjectSql("AVL.ObjectList.JObjectList.GetDataTable", "AVLObjectList.Code");
AVL.SubUserObjects.jSubUserObjects user = new SubUserObjects.jSubUserObjects();
user.GetData(WebClassLibrary.SessionManager.Current.MainFrame.CurrentUserCode);
if (user.Code > 0)
{
string parentObjects = user.objects.Remove(user.objects.Length - 1);
query += " OR Code IN ( " + parentObjects + ")";
}
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()
{
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
try
{
string query = @"SELECT * FROM [AVLTSIPAndroidSocket]"
+ " WHERE [IsProceced]='false'";
DB.setQuery(query);
return(DB.Query_DataTable());
}
catch (Exception ex)
{
ClassLibrary.JSystem.Except.AddException(ex);
return(null);
}
finally
{
DB.Dispose();
}
}
public static object Get(string KeyName)
{
ClassLibrary.JDataBase db = new ClassLibrary.JDataBase();
try
{
db.setQuery("Select * from AUTSettings where KeyName = N'" + KeyName + "'");
DataTable dt = db.Query_DataTable();
if (dt != null && dt.Rows.Count > 0)
{
return(dt.Rows[0]["KeyValue"]);
}
else
{
return("");
}
}
finally
{
db.Dispose();
}
}
public int Find()
{
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
try
{
DB.setQuery("select Code from AUTBazRasService a where a.BusNumber="
+ BusNumber
+ " and a.MoveDate='"
+ MoveDate.ToString("yyyy-MM-dd HH:mm:ss") + "'");
DataTable DT = DB.Query_DataTable();
if (DT != null && DT.Rows.Count == 1)
{
return(int.Parse(DT.Rows[0]["Code"].ToString()));
}
}
finally
{
DB.Dispose();
}
return(0);
}
public static Int64 GetOneDayPrice(int pCode)
{
if (!ClassLibrary.JPermission.CheckPermission("AVL.RegisterDeivce.JRegisterDevices.GetDataTable"))
{
return(0);
}
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
try
{
string query = @"SELECT isnull(ADM.UnitPrice*(count(AD.Code)-1),0) FROM AVLDevice AD
inner join AVLDeviceModel ADM on AD.DeviceType=ADM.Code
Inner join AVLJoinDevice j on j.parentDeviceCode=AD.Code ";
if (pCode > 0)
{
query += " where AD.personCode=" + pCode;
}
query += " group by UnitPrice,AD.Code";
DB.setQuery(query);
System.Data.DataTable dt = DB.Query_DataTable();
if (dt != null && dt.Rows.Count == 1)
{
return(Int64.Parse(dt.Rows[0][0].ToString()));
}
else
{
return(1);
}
}
catch (Exception ex)
{
ClassLibrary.JSystem.Except.AddException(ex);
return(0);
}
finally
{
DB.Dispose();
}
}
public static System.Data.DataTable GetDataTable(int FactorCode)
{
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
try
{
string query = string.Format(@"SELECT ROW_NUMBER() over(partition by FactorCode order by code) Row, Code,describe,count,cast(unitPrice as bigint)unitPrice,cast(TotalUnitPrice as bigint)TotalUnitPrice,product,FactorCode,parameter FROM AccFactorItem WHERE FactorCode= {0}", FactorCode);
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 GetDataTableInArea(AVL.Controls.Map.Point SouthWest, AVL.Controls.Map.Point NorthEast)
{
if (!ClassLibrary.JPermission.CheckPermission("AVL.Coordinate.Traditional.JTransactionCars.GetDataTableInArea"))
{
return(null);
}
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
try
{
string query = @"SELECT CarID
,[LastLatitude]
, LastLongitude
, 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)
AND " +
string.Format("LastLatitude BETWEEN {0} AND {1} AND LastLongitude BETWEEN {2} AND {3} ",
SouthWest.Latitude,
NorthEast.Latitude,
SouthWest.Longitude,
NorthEast.Longitude);
// query += " AND "+ ClassLibrary.JPermission.getObjectSql("AVL.Coordinate.Traditional.JTransactionCars.GetDataTableInArea", "TransactionCars.CarID");
DB.setQuery(query);
return(DB.Query_DataTable());
}
catch (Exception ex)
{
ClassLibrary.JSystem.Except.AddException(ex);
return(null);
}
finally
{
DB.Dispose();
}
}
/// <summary>
/// return rows in 1 minute ago
///
/// </summary>
/// <param name="checkpermission"></param>
/// <returns></returns>
public static System.Data.DataTable GetDataTable(bool checkpermission = true)
{
bool _hasPermision = true;
if (checkpermission)
{
ClassLibrary.JPermission.CheckPermission("AVL.Coordinate.Traditional.JTransactionCars.GetDataTablePoints");
}
if (!_hasPermision)
{
return(null);
}
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
try
{
string query = @"select * from (SELECT
row_number() over(order by [DeviceSendDateTime] ) as Row, *
FROM [AVLCoordinate] where lat IS NOT NULL
AND lng IS NOT NULL
AND DeviceSendDateTime >DATEADD (second , -1 , GETDATE() ) ";
if (_hasPermision)
{
;// query += " AND "+ ClassLibrary.JPermission.getObjectSql("AVL.Coordinate.Traditional.JTransactionCars.GetDataTablePoints", "TransactionCars.CarID");
}
query += " ) a";
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.Coordinate.Traditional.JTransactionCars.GetDataTable"))
{
return(null);
}
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
try
{
string query = @"SELECT CarID
,[LastLatitude]
, LastLongitude
, 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)
";
// string query = @"SELECT * FROM [avlsite_com_1].[dbo].[TransactionCars] WHERE ";
if (pCode != 0)
{
query += " AND CarID=" + pCode;
}
// query += " AND "+ClassLibrary.JPermission.getObjectSql("AVL.Coordinate.Traditional.JTransactionCars.GetDataTable", "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(int deviceCode, int personCode)
{
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
try
{
string query = @"SELECT * FROM [AVLArea] WHERE deviceCode =" + deviceCode;
if (personCode > 0)
{
query += " and personCode=" + personCode;
}
DB.setQuery(query);
return(DB.Query_DataTable());
}
catch (Exception ex)
{
ClassLibrary.JSystem.Except.AddException(ex);
return(null);
}
finally
{
DB.Dispose();
}
}
public static bool DistanceMeasurement(ClassLibrary.JDataBase db)
{
double Distance = 0;
try
{
if (db.datatable != null)
{
db.datatable.Clear();
}
db.setQuery(@"SELECT TOP 1 BusCode,[Date]EDate FROM dbo.AUTDailyAvlPerformanceRportOnBus
WHERE IsProceced = 0 --AND [Date] < Cast(GETDATE() AS Date)
GROUP BY BusCode,[Date]
ORDER BY BusCode,EDate");
DataTable Dt = db.Query_DataTable();
if (Dt != null)
{
if (Dt.Rows.Count > 0)
{
db.setQuery(@"SELECT EventDate,Latitude,Longitude FROM dbo.AUTAvlTransaction
WHERE BusCode = " + Dt.Rows[0]["BusCode"].ToString() + @" AND
CAST(EventDate AS DATE) = cast('" + Dt.Rows[0]["EDate"].ToString() + @"' as Date)
GROUP BY EventDate,Latitude,Longitude
ORDER BY EventDate");
DataTable DtTransactions = db.Query_DataTable();
if (DtTransactions != null)
{
if (DtTransactions.Rows.Count > 1)
{
Distance = GetDistance(DtTransactions, MeasureUnits.Kilometers);
db.setQuery(@"Update [dbo].[AUTDailyAvlPerformanceRportOnBus] set [IsProceced] = 1,[Distance] = " + Distance.ToString() + @"
,[ProcessDate] = getdate()
where BusCode = " + Dt.Rows[0]["BusCode"].ToString() + @" and [Date] = cast('"
+ Dt.Rows[0]["EDate"].ToString() + @"' as date)");
db.Query_Execute();
return(true);
}
else
{
if (DtTransactions.Rows.Count == 1)
{
db.setQuery(@"Update [dbo].[AUTDailyAvlPerformanceRportOnBus] set [IsProceced] = 1,[Distance] = 0,[ProcessDate] = getdate()
where BusCode = " + Dt.Rows[0]["BusCode"].ToString() + @" and [Date] = cast('"
+ Dt.Rows[0]["EDate"].ToString() + @"' as date)");
db.Query_Execute();
}
return(false);
}
}
else
{
return(false);
}
}
else
{
return(false);
}
}
else
{
return(false);
}
}
catch
{
return(false);
}
}