public static DataTable GetDataTable(int pCode = 0)
{
JDataBase DB = new JDataBase();
try
{
string query = "select * from AUTCardType "
+ " Where " + JPermission.getObjectSql("BusManagment.Card.JCards.GetDataTable", "AUTCardType.Code");
if (pCode > 0)
{
query += " AND Code = " + pCode;
}
DB.setQuery(query);
return(DB.Query_DataTable());
}
catch (Exception ex)
{
JSystem.Except.AddException(ex);
return(null);
}
finally
{
DB.Dispose();
}
}
public static DataTable GetDataTable(int pCode = 0)
{
JDataBase DB = new JDataBase();
try
{
string query = "select *,(cast(Code as varchar) + ' - ' + Name)NameWithCode from AUTZone "
+ " Where " + JPermission.getObjectSql("BusManagment.Zone.JZones.GetDataTable", "AUTZone.Code");
if (pCode > 0)
{
query += " AND Code = " + pCode;
}
DB.setQuery(query);
return(DB.Query_DataTable());
}
catch (Exception ex)
{
JSystem.Except.AddException(ex);
return(null);
}
finally
{
DB.Dispose();
}
}
public static string GetWebQuery()
{
string PermitionSql = " And " + JPermission.getObjectSql("BusManagment.Bus.JBuses.GetAllBusesOnly", "abf.BusCode");
return(@"select abf.Code,abf.[Date],abf.StartTime,abf.EndTime,ab.BUSNumber,sdf.name FailureName,
abf.[Description],abf.InsertDate
from AUTBusFailure abf
left join AutBus ab on ab.Code = abf.BusCode
left join subdefine sdf on sdf.Code = abf.BusFailureCode
where 1 = 1 " + PermitionSql);
}
public static DataTable GetAllBusesOnly(int LineCode = 0)
{
string BusQuery = "";
JDataBase DB = new JDataBase();
DB.setQuery(@"select ISNULL(IsAdmin,0)IsAdmin from users where Code = " + ClassLibrary.JMainFrame.CurrentUserCode);
DataTable DtBusCheck = DB.Query_DataTable();
if (DtBusCheck.Rows[0]["IsAdmin"].ToString() != "True")
{
BusQuery = " and len(BusNumber)=4 and BusNumber < 6999 ";
}
try
{
string WhereLine = "";
if (LineCode > 0)
{
WhereLine = " and LastLineNumber in (SELECT LineNumber FROM AUTLine Where Code = " + LineCode + ")";
}
string query = "";
string PermitionSql = " And " + JPermission.getObjectSql("BusManagment.Bus.JBuses.GetAllBusesOnly", "Code");
if (PermitionSql.Length > 5)
{
query = @"Select Code,BusNumber from AUTBus Where Active = 1 and IsValid = 1 " + WhereLine + PermitionSql + BusQuery + " Order By BusNumber ASC";
}
else
{
query = @"Select Code,BusNumber from AUTBus Where Active = 1 and IsValid = 1 " + WhereLine + BusQuery + " Order By BusNumber ASC";
}
DB.setQuery(query);
return(DB.Query_DataTable());
}
catch (Exception ex)
{
JSystem.Except.AddException(ex);
return(null);
}
finally
{
DB.Dispose();
}
}
public static DataTable GetDataTable(int pCode)
{
JDataBase DB = new JDataBase();
try
{
string query = @"
SELECT
L.[Code]
,l.[LineName]
,l.[LineNumber]
,Z.[Name] AS 'ZoneName'
,F.[Name] AS 'FleetName'
,D.[name] AS 'LineType'
,L.[Status]
,l.[Rotate]
,l.[Distance]
,(Select Name From AUTStation WHERE Code = (Select Top 1 StationCode from AUTLineStation WHERE LineCode = L.Code AND IsBack = 0 Order BY Priority )) Source
,(Select Name From AUTStation WHERE Code = (Select Top 1 StationCode from AUTLineStation WHERE LineCode = L.Code AND IsBack = 0 Order BY Priority Desc )) Destination
FROM AUTLine L
INNER JOIN AUTZone Z ON L.ZoneCode = Z.Code
INNER JOIN AUTFleet F ON L.Fleet = F.Code
INNER JOIN subdefine D ON L.LineType = D.Code
"
+ " Where " + JPermission.getObjectSql("BusManagment.Line.JLines.GetDataTable", "L.Code");
if (pCode > 0)
{
query += " AND L.Code = " + pCode;
}
DB.setQuery(query);
return(DB.Query_DataTable());
}
catch (Exception ex)
{
JSystem.Except.AddException(ex);
return(null);
}
finally
{
DB.Dispose();
}
}
public static string GetWebQuery()
{
string PermitionSql = " And " + JPermission.getObjectSql("BusManagment.Bus.JBuses.GetWebQuery", "AUTBus.Code");
if (PermitionSql.Length < 5)
{
PermitionSql = "";
}
return(@"Select top 100 percent AUTBus.Code
, AUTAutomobile.Plaque
, AUTBus.BUSNumber
, AUTBus.LastLineNumber LineNumber
, cap.Name as OwnerName
, abo.OwnerCount
,(select value from finComparativeCode where ObjectCode=abo.CodePerson) TafziliCode
,(select f.AccountNo from finBankAccount f where f.PCode=abo.CodePerson) AccountNo
, dbo.DateEnToFa(abo.EndDate) EndDateContract
, AUTFleet.Name FleetName
, AUTBus.Active
, AUTBus.IsValid
, aht.IMEI GSMSerial
, (Select ID FROM AUTDevice WHERE Code = (Select TOP 1 DeviceCode FROM AUTBusDevise WHERE DeviceCode in (select Code from AUTDevice where Type = 2) and BusCode = AUTBus .Code Order by StartDate Desc )) Reader1Serial
, (Select ID FROM AUTDevice WHERE Code = (Select TOP 1 DeviceCode FROM AUTBusDevise WHERE DeviceCode in (select Code from AUTDevice where Type = 2) and BusCode = AUTBus .Code Order by StartDate asc )) Reader2Serial
, AUTBus.LastDate LastAvlDate
, AUTBus.TicketLastDate, aht.ConsulVersion
from AUTBus
left join AUTFleet on AUTFleet.Code = AUTBus.FleetCode
left join AUTAutomobile on AUTAutomobile.code = AUTBus.CarCode
left join (select *, COUNT(*) over (PARTITION BY BusCode) OwnerCount from AUTBusOwner where IsActive = 1) abo on AUTBus.Code = abo.BusCode
left join clsAllPerson cap on cap.Code = abo.CodePerson
left join
(
select BusSerial,IMEI, ConsulVersion from
(
select BusSerial,IMEI, cast(cast(substring([Version],1,1) AS INT)AS NVARCHAR)+
cast(cast(substring([Version],2,1) AS INT)AS NVARCHAR)+
cast(cast(substring([Version],3,1) AS INT)AS NVARCHAR) ConsulVersion, ROW_NUMBER() over (partition by BusSerial order by date desc) row_numb from AUTHeaderTransaction
) aht where row_numb = 1
) aht on aht.BusSerial = AUTBus.BUSNumber
where 1=1 " + PermitionSql);
}
public static DataTable GetDataTable(int pCode = 0)
{
if (!JPermission.CheckPermission("BusManagment.Bus.JBuses.GetDataTable"))
{
return(null);
}
JDataBase DB = new JDataBase();
try
{
string query = @"Select AUTBus.Code
, AUTAutomobile.Plaque
, AUTBus.BUSNumber
, AUTFleet.Name FleetName
, AUTBus.Active
, (Select IMEI FROM AUTDevice WHERE Code = (Select TOP 1 DeviceCode FROM AUTBusDevise WHERE BusCode = AUTBus .Code Order by StartDate Desc )) GSMSerial
, (Select Tel FROM AUTDevice WHERE Code = (Select TOP 1 DeviceCode FROM AUTBusDevise WHERE BusCode = AUTBus .Code Order by StartDate Desc )) Mobile
,(Select Fa_Date From StaticDates Where En_date = (Select TOP 1 StartDate FROM AUTBusDevise WHERE BusCode = AUTBus .Code Order by StartDate Desc )) StateChangeDate
,Properties.*
from AUTBus
inner join AUTFleet on AUTFleet.Code = AUTBus.FleetCode
inner join AUTAutomobile on AUTAutomobile.code = AUTBus.CarCode
left join [Propperty_ClassName_BusManagment.JBus_1] Properties ON Properties.ObjectCode = AUTBus.Code WHERE 1 = 1 "
+ " AND " + JPermission.getObjectSql("BusManagment.Bus.JBuses.GetDataTable", "AUTBus.Code");
if (pCode > 0)
{
query += " AND AUTBus.Code = " + pCode;
}
DB.setQuery(query);
return(DB.Query_DataTable());
}
catch (Exception ex)
{
JSystem.Except.AddException(ex);
return(null);
}
finally
{
DB.Dispose();
}
}
public static string GetWebBusPerformanceReportQuery(int ZoneCode = 0, int LineNumber = 0, int BusCode = 0, int DayType = -1, DateTime?StartEventDate = null, DateTime?EndEventDate = null, int OwnerCode = 0, bool CalcService = false, int TransactionType = 0, int CardType = -1, int FleetCode = 0,
int MinTransaction = 0, int MaxTransaction = 0)
{
string PermitionSql = " And " + JPermission.getObjectSql("BusManagment.Bus.JBuses.GetWebBusPerformanceReportQuery", "DP.BusCodeBus");
if (PermitionSql.Length < 5)
{
PermitionSql = "";
}
string PermitionSqlZone = " And " + JPermission.getObjectSql("BusManagment.Zone.JZones.GetDataTable", "AZ.Code");
if (PermitionSqlZone.Length < 5)
{
PermitionSqlZone = "";
}
string CardTypePermitionSql = JPermission.getObjectSql("BusManagment.Card.JCards.GetDataTable", "Code");
string FinalCardType = "";
if (CardTypePermitionSql == " 1 = 1 ")
{
CardTypePermitionSql = "";
}
else
{
JDataBase mydb = new JDataBase();
mydb.setQuery("Select Type From AutCardType Where " + CardTypePermitionSql);
DataTable DtCardType = mydb.Query_DataTable();
FinalCardType = "and DP.CardType in (" + String.Join(",", JDataBase.DataTableToStringtArray(DtCardType, "Type")) + ")";
mydb.Dispose();
}
DateTime StartDTime = new DateTime(StartEventDate.Value.Year, StartEventDate.Value.Month, StartEventDate.Value.Day);
DateTime EndDTime = new DateTime(EndEventDate.Value.Year, EndEventDate.Value.Month, EndEventDate.Value.Day);
string Query = "", WhereServiceStr = "", ShiftWhereStr = " where Error = 0 and TCount > 0 ", WhereStr = " where ab.Active = 1 and ab.IsValid = 1 ";
DateTime NullDatetime = new DateTime(0001, 1, 1, 12, 00, 00);
if (ZoneCode > 0 || LineNumber > 0 || BusCode > 0 || DayType > -1 || StartEventDate.HasValue == true || OwnerCode > 0 || CalcService == true || TransactionType > 0 || CardType > -1 || FleetCode > 0)
{
if (ZoneCode > 0)
{
WhereStr += @" and AZ.Code=" + ZoneCode;
}
if (LineNumber > 0)
{
WhereStr += @" and AL.Code=" + LineNumber;
}
if (FleetCode > 0)
{
WhereStr += " and AB.FleetCode = " + FleetCode;
}
if (OwnerCode > 0)
{
ShiftWhereStr += @" and OwnerPCode = " + OwnerCode;
}
else
{
if (BusCode > 0)
{
ShiftWhereStr += @" and BusCodeBus =" + BusCode;
}
}
if (DayType > -1)
{
ShiftWhereStr += " and IsHoliday = " + DayType;
}
if (StartEventDate.HasValue && StartEventDate.Value.Date > NullDatetime)
{
ShiftWhereStr += @" and [Date] between '" + StartDTime.ToShortDateString() + " 00:00:00' and '" + EndDTime.ToShortDateString() + " 23:59:59'";
WhereServiceStr += @" and [Date] between '" + StartDTime.ToShortDateString() + " 00:00:00' and '" + EndDTime.ToShortDateString() + " 23:59:59'";
}
//if(CalcService==true)
if (TransactionType > 0)
{
if (TransactionType == 1)
{
ShiftWhereStr += @" and DocumentCode = 0";
}
if (TransactionType == 2)
{
ShiftWhereStr += @" and DocumentCode > 0";
}
}
if (CardType > -1)
{
ShiftWhereStr += @" and CardType=" + CardType;
}
}
String WhereMinMaxStr = "";
if (MinTransaction > 0)
{
WhereMinMaxStr += " and TransactionCount/WorkDay > " + MinTransaction;
}
if (MaxTransaction > 0)
{
WhereMinMaxStr += " and TransactionCount/WorkDay < " + MaxTransaction;
}
Query = @"select * into #tmpShift from [dbo].AUTShiftDriver " + ShiftWhereStr + @"
<#PreviusSQL#>
select *,CASE WHEN WorkDay=0 THEN 0 ELSE TransactionCount/WorkDay END AvgWork
from
(select top 100 percent max(DP.Code) AS Code,AF.Name as FleetName,AZ.Name as ZoneName,
DP.LineNumber,AB.BUSNumber,CAP.Name,DP.CardType,DP.TicketPrice * 10 as TicketPrice,sum(DP.TCount) as TransactionCount,sum(cast(DP.TCount * DP.TicketPrice as BIGINT)) * 10 as InCome,
(
select sum(a.NumOfService) from AutBusServices a
where BusNumber=AB.BUSNumber and lineNumber=DP.LineNumber " + WhereServiceStr + @"
) ServiceCount,
(
select count(*) from
(
select Date from #tmpShift AP1
where 1=1
and ap1.BusNumber = ab.BusNumber
and ap1.TicketPrice=dp.TicketPrice
and ap1.LineNumber=dp.LineNumber
and ap1.CardType=dp.CardType
" + WhereServiceStr + @"
group by Date
)a
) WorkDay
,ALT.TransactionCount MinTransactionCount
from #tmpShift DP
left join [dbo].[AUTBus] AB on DP.BusCodeBus = AB.Code
left join [dbo].[AUTLine] AL on DP.LineNumber = AL.LineNumber
left join [dbo].[AUTZone] AZ on AL.ZoneCode = AZ.Code
left join [dbo].[AUTFleet] AF on AB.FleetCode = AF.Code
left join [dbo].[clsAllPerson] CAP on DP.OwnerPCode = CAP.Code
left join [dbo].[AUTDailyLineTransactionCount] ALT on ALT.LineCode=AL.Code
" + WhereStr + PermitionSql + FinalCardType + PermitionSqlZone + @"
GROUP BY dp.CardType,AF.Name,AZ.Name,DP.LineNumber,AB.BUSNumber,CAP.Name,DP.TicketPrice,DP.BusCodeBus,DP.OwnerPCode,ALT.TransactionCount
) as a
where 1=1 " + WhereMinMaxStr;
return(Query);
}
public static string GetWebBusPerformanceReportQueryAccounting(int ZoneCode = 0, int LineNumber = 0, int BusNumebr = 0, int DayType = -1, DateTime?StartEventDate = null, DateTime?EndEventDate = null, int OwnerCode = 0, bool CalcService = false, int TransactionType = 0, int CardType = -1, int FleetCode = 0)
{
string PermitionSql = " And " + JPermission.getObjectSql("BusManagment.Bus.JBuses.GetWebBusPerformanceReportQuery", "DP.BusCode");
if (PermitionSql.Length < 5)
{
PermitionSql = "";
}
string PermitionSqlZone = " And " + JPermission.getObjectSql("BusManagment.Zone.JZones.GetDataTable", "AZ.Code");
if (PermitionSqlZone.Length < 5)
{
PermitionSqlZone = "";
}
string CardTypePermitionSql = JPermission.getObjectSql("BusManagment.Card.JCards.GetDataTable", "Code");
string FinalCardType = "";
if (CardTypePermitionSql == " 1 = 1 ")
{
CardTypePermitionSql = "";
}
else
{
JDataBase mydb = new JDataBase();
mydb.setQuery("Select Type From AutCardType Where " + CardTypePermitionSql);
DataTable DtCardType = mydb.Query_DataTable();
FinalCardType = "and DP.CardType in (" + String.Join(",", JDataBase.DataTableToStringtArray(DtCardType, "Type")) + ")";
mydb.Dispose();
}
DateTime StartDTime = new DateTime(StartEventDate.Value.Year, StartEventDate.Value.Month, StartEventDate.Value.Day);
DateTime EndDTime = new DateTime(EndEventDate.Value.Year, EndEventDate.Value.Month, EndEventDate.Value.Day);
string Query = "", WhereStr = " where DP.TCount > 0 and DP.Error = 0 and DP.TicketPrice > 0 and DP.Price > 0 and ab.Active = 1 and ab.IsValid = 1 ";
DateTime NullDatetime = new DateTime(0001, 1, 1, 12, 00, 00);
if (ZoneCode > 0 || LineNumber > 0 || BusNumebr > 0 || DayType > -1 || StartEventDate.HasValue == true || OwnerCode > 0 || CalcService == true || TransactionType > 0 || CardType > -1 || FleetCode > 0)
{
if (ZoneCode > 0)
{
WhereStr += @" and AZ.Code=" + ZoneCode;
}
if (LineNumber > 0)
{
WhereStr += @" and AL.Code=" + LineNumber;
}
if (FleetCode > 0)
{
WhereStr += " and AB.FleetCode = " + FleetCode;
}
if (OwnerCode > 0)
{
WhereStr += @" and DP.OwnerCode = " + OwnerCode;
}
else
{
if (BusNumebr > 0)
{
WhereStr += @" and DP.BusCode=" + BusNumebr;
}
}
if (DayType > -1)
{
WhereStr += " and DP.IsHoliday = " + DayType;
}
if (StartEventDate.HasValue && StartEventDate.Value.Date > NullDatetime)
{
WhereStr += @" and DP.[Date] between '" + StartDTime.ToShortDateString() + " 00:00:00' and '" + EndDTime.ToShortDateString() + " 23:59:59'";
}
//if(CalcService==true)
//if (TransactionType > 0)
//{
// if (TransactionType == 1)
// WhereStr += @" and DP.DocumentCode = 0";
// if (TransactionType == 2)
// WhereStr += @" and DP.DocumentCode > 0";
//}
if (CardType > -1)
{
WhereStr += @" and DP.CardType=" + CardType;
}
}
// Query = @"select top 100 percent max(DP.Code) AS Code,AF.Name as FleetName,AZ.Name as ZoneName,
// DP.LineNumber,AB.BUSNumber,CAP.Name,DP.CardType,DP.TicketPrice * 10 as TicketPrice,sum(DP.TCount) as TransactionCount,sum(DP.Price) * 10 as InCome
// from [dbo].[AUTDailyPerformanceRportOnBus] DP
// left join [dbo].[AUTBus] AB on DP.BusCode = AB.Code
// left join [dbo].[AUTLine] AL on DP.LineNumber = AL.LineNumber
// left join [dbo].[AUTZone] AZ on AL.ZoneCode = AZ.Code
// left join [dbo].[AUTFleet] AF on AB.FleetCode = AF.Code
// left join [dbo].[clsAllPerson] CAP on DP.OwnerCode = CAP.Code
// " + WhereStr + PermitionSql + FinalCardType + PermitionSqlZone + @"
// GROUP BY dp.CardType,AF.Name,AZ.Name,DP.LineNumber,AB.BUSNumber,CAP.Name,DP.TicketPrice,DP.BusCode,DP.OwnerCode";
Query = @"select top 100 percent max(DP.Code) AS Code,Dp.Date,AF.Name as FleetName,AZ.Name as ZoneName,
DP.LineNumber,AB.BUSNumber,CAP.Name,DP.CardType,
sum(DP.TCount) as TransactionCount,sum(DP.Price) * 10 as InCome
,max(DP.DocumentCode) * 10 PaymentIncome
,(sum(DP.Price) - max(DP.DocumentCode)) * 10 Mandeh
from [dbo].[AUTDailyPerformanceRportOnBus] DP
left join [dbo].[AUTBus] AB on DP.BusCode = AB.Code
left join [dbo].[AUTLine] AL on DP.LineNumber = AL.LineNumber
left join [dbo].[AUTZone] AZ on AL.ZoneCode = AZ.Code
left join [dbo].[AUTFleet] AF on AB.FleetCode = AF.Code
left join [dbo].[clsAllPerson] CAP on DP.OwnerCode = CAP.Code
" + WhereStr + PermitionSql + FinalCardType + PermitionSqlZone + @"
GROUP BY Dp.Date,dp.CardType,AF.Name,AZ.Name,DP.LineNumber,AB.BUSNumber,CAP.Name,DP.BusCode,DP.OwnerCode" ;
return(Query);
}
public static string GetWebBusPerPerformanceReportQueryWithMuliBus(int ZoneCode = 0, int LineNumber = 0, int[] BusNumebr = null, DateTime?StartEventDate = null, DateTime?EndEventDate = null, int OwnerCode = 0, int CardType = -1)
{
string PermitionSql = " And " + JPermission.getObjectSql("BusManagment.Bus.JBuses.GetWebQuery", "DP.BusCode");
if (PermitionSql.Length < 5)
{
PermitionSql = "";
}
string CardTypePermitionSql = JPermission.getObjectSql("BusManagment.Card.JCards.GetDataTable", "Code");
string FinalCardType = "";
if (CardTypePermitionSql == " 1 = 1 ")
{
CardTypePermitionSql = "";
}
else
{
JDataBase mydb = new JDataBase();
mydb.setQuery("Select Type From AutCardType Where " + CardTypePermitionSql);
DataTable DtCardType = mydb.Query_DataTable();
FinalCardType = "and DP.CardType in (" + String.Join(",", JDataBase.DataTableToStringtArray(DtCardType, "Type")) + ")";
mydb.Dispose();
}
string Query = "", WhereStr = " where 1=1 ";
DateTime NullDatetime = new DateTime(0001, 1, 1, 12, 00, 00);
if (StartEventDate.HasValue == true || BusNumebr.Length > 0 || ZoneCode > 0 || LineNumber > 0 || OwnerCode > 0 || CardType > -1)
{
if (BusNumebr.Length > 0)
{
WhereStr += @" and DP.BusCode in (";
for (int i = 0; i < BusNumebr.Length; i++)
{
WhereStr += @"" + BusNumebr[i].ToString() + @",";
}
WhereStr = WhereStr.Remove(WhereStr.Length - 1, 1);
WhereStr += ") ";
}
if (ZoneCode > 0)
{
WhereStr += " and AZ.Code = " + ZoneCode;
}
if (LineNumber > 0)
{
WhereStr += " and AL.Code = " + LineNumber;
}
if (OwnerCode > 0)
{
WhereStr += " and DP.OwnerCode = " + OwnerCode;
}
if (CardType > -1)
{
WhereStr += " and DP.CardType = " + CardType;
}
if (StartEventDate.HasValue && StartEventDate.Value.Date > NullDatetime)
{
DateTime StartDTime = new DateTime(StartEventDate.Value.Year, StartEventDate.Value.Month, StartEventDate.Value.Day);
DateTime EndDTime = new DateTime(EndEventDate.Value.Year, EndEventDate.Value.Month, EndEventDate.Value.Day);
WhereStr += @" and DP.[Date] between '" + StartDTime.ToShortDateString() + "' and '" + EndDTime.ToShortDateString() + "'";
}
}
Query = @"select top 100 percent max(DP.Code) as Code,AF.Name as FleetName,AZ.Name as ZoneName,
DP.LineNumber,AB.BUSNumber,DP.CardType,sum(DP.TCount) as TransactionCount,sum(DP.Price) * 10 as InCome,
(N''+cast(AB.BUSNumber as varchar)+N' - کارت نوع '+cast(DP.CardType as varchar)) as ColumnName
from [dbo].[AUTDailyPerformanceRportOnBus] DP
left join [dbo].[AUTBus] AB on DP.BusCode = AB.Code
left join [dbo].[AUTLine] AL on DP.LineNumber = AL.LineNumber
left join [dbo].[AUTZone] AZ on AL.ZoneCode = AZ.Code
left join [dbo].[AUTFleet] AF on AL.Fleet = AF.Code
" + WhereStr + PermitionSql + FinalCardType + @"
group by AB.BUSNumber,AF.Name,AZ.Name,DP.LineNumber,DP.CardType " ;
return(Query);
}
public static string GetWebBusTransactionReportQuery(int ZoneCode = 0, int LineNumber = 0, int PersonelCode = 0, int BUSNumber = 0, DateTime?StartEventDate = null, DateTime?EndEventDate = null, TimeSpan?StartTime = null, TimeSpan?EndTime = null, int CardType = -1, int DayType = -1, int FleetCode = 0, Int64 PassengerCard = 0, string BankType = "1")
{
string PermitionSql = " And " + JPermission.getObjectSql("BusManagment.Bus.JBuses.GetWebBusTransactionReportQuery", "dbo.AUTTicketTransaction.BusCode");
if (PermitionSql.Length < 5)
{
PermitionSql = "";
}
string PermitionSqlZone = " And " + JPermission.getObjectSql("BusManagment.Zone.JZones.GetDataTable", "AUTTicketTransaction.ZoneCode");
if (PermitionSqlZone.Length < 5)
{
PermitionSqlZone = "";
}
string CardTypePermitionSql = JPermission.getObjectSql("BusManagment.Card.JCards.GetDataTable", "Code");
string FinalCardType = "";
if (CardTypePermitionSql == " 1 = 1 ")
{
CardTypePermitionSql = "";
}
else
{
JDataBase mydb = new JDataBase();
try
{
mydb.setQuery("Select Type From AutCardType Where " + CardTypePermitionSql);
DataTable DtCardType = mydb.Query_DataTable();
FinalCardType = "and dbo.AUTTicketTransaction.CardType in (" + String.Join(",", JDataBase.DataTableToStringtArray(DtCardType, "Type")) + ")";
}
finally
{
mydb.Dispose();
}
}
string Query = "", WhereStr = @" where 1=1 and dbo.AUTTicketTransaction.TicketPrice > 0
and len(dbo.AUTTicketTransaction.busnumber)>=4";
DateTime NullDatetime = new DateTime(0001, 1, 1, 12, 00, 00);
if (ZoneCode > 0 || LineNumber > 0 || PersonelCode > 0 || BUSNumber > 0 || StartEventDate.HasValue == true || StartTime.HasValue == true || CardType > -1 || DayType > -1 || FleetCode > 0)
{
if (ZoneCode > 0)
{
WhereStr += @" and AUTTicketTransaction.ZoneCode = " + ZoneCode;
}
if (FleetCode > 0)
{
WhereStr += " and AutLine.Fleet = " + FleetCode;
}
if (LineNumber > 0)
{
WhereStr += @" and AutLine.Code = " + LineNumber;
}
if (PersonelCode > 0)
{
WhereStr += @" and dbo.AUTTicketTransaction.DriverPersonCode=" + PersonelCode;
}
if (BUSNumber > 0)
{
WhereStr += @" and dbo.AUTTicketTransaction.BusCode=" + BUSNumber;
}
if ((StartEventDate.HasValue && StartEventDate.Value.Date > NullDatetime) && !StartTime.HasValue)
{
WhereStr += @" and convert(date,dbo.AUTTicketTransaction.EventDate) between '" + StartEventDate.Value.Date.ToShortDateString() + " 00:00:00' and '" + EndEventDate.Value.Date.ToShortDateString() + " 23:59:59'";
}
if ((StartEventDate.HasValue && StartEventDate.Value.Date > NullDatetime) && StartTime.HasValue)
{
DateTime StartDTime = new DateTime(StartEventDate.Value.Year, StartEventDate.Value.Month, StartEventDate.Value.Day, StartTime.Value.Hours, StartTime.Value.Minutes, StartTime.Value.Seconds);
DateTime EndDTime = new DateTime(EndEventDate.Value.Year, EndEventDate.Value.Month, EndEventDate.Value.Day, EndTime.Value.Hours, EndTime.Value.Minutes, EndTime.Value.Seconds);
WhereStr += @" and dbo.AUTTicketTransaction.EventDate between '" + StartDTime.ToString("yyyy-MM-dd HH:mm:ss") + "' and '" + EndDTime.ToString("yyyy-MM-dd HH:mm:ss") + "'";
}
if (DayType > -1)
{
if (DayType == 0)
{
WhereStr += " and cast(dbo.AUTTicketTransaction.EventDate as date) not in (select Date from AutHolidays)";
}
if (DayType == 1)
{
WhereStr += " and cast(dbo.AUTTicketTransaction.EventDate as date) in (select Date from AutHolidays)";
}
}
if (CardType > -1)
{
WhereStr += @" and AUTTicketTransaction.CardType = " + CardType;
}
}
if (!string.IsNullOrWhiteSpace(BankType))
{
WhereStr += @" and AUTTicketTransaction.BankType IN ( " + BankType + ")";
}
if (PassengerCard > 0)
{
WhereStr += @" and AUTTicketTransaction.PassengerCardSerial = " + PassengerCard;
}
Query = @"SELECT top 100 percent
max(dbo.AUTTicketTransaction.Code) Code,
max(dbo.AUTTicketTransaction.TransactionId)TransactionId,
dbo.AUTTicketTransaction.FleetName,
dbo.AUTTicketTransaction.LineNumber, dbo.AUTTicketTransaction.BUSNumber,
dbo.AUTTicketTransaction.DriverPersonName as DriverName,
case when dbo.AUTTicketTransaction.PassengerCardSerial2 = 0 then dbo.AUTTicketTransaction.PassengerCardSerial else Cast(dbo.AUTTicketTransaction.PassengerCardSerial as decimal(20,0))+POWER(CAST(2 as decimal(20,0)),63)-1 end PassengerCardSerial,
dbo.AUTTicketTransaction.CardType,
case dbo.AUTTicketTransaction.Banktype when 0 then N'کارت تبریز' when 1 then N'کارت تبریز2' when 2 then N'کارت CityWay' when 3 then N'کارت CityPay' else N'نامشخص' end BankType ,
dbo.AUTTicketTransaction.TicketPrice * 10 as TicketPrice,
dbo.AUTTicketTransaction.RemainPrice * 10 RemainPrice,
dbo.AUTTicketTransaction.EventDate,
min(dbo.AUTTicketTransaction.RecievedDate) RecievedDate
FROM dbo.AUTTicketTransaction Left Join AutLine on AUTTicketTransaction.LineNumber = AutLine.LineNumber"
+ WhereStr
+ PermitionSql
+ PermitionSqlZone
+ @"
group by EventDate, BusNumber,Banktype,DriverCardSerial,AUTTicketTransaction.PassengerCardSerial2 ,AUTTicketTransaction.LineNumber,FleetCode,BusCode,PassengerCardSerial,CardType,TicketPrice,RemainPrice,ReaderId,FleetName,DriverPersonName" ;
return(Query);
}
public static string GetWebReportQuery(int ZoneCode = 0, int LineNumber = 0, int DayType = -1, DateTime?StartEventDate = null, DateTime?EndEventDate = null, int CardType = -1, int FleetCode = 0, bool chInCome = false)
{
string PermitionSql = " And " + JPermission.getObjectSql("BusManagment.Bus.JBuses.GetWebBusTransactionReportQuery", "adp.BusCode");
if (PermitionSql.Length < 5)
{
PermitionSql = "";
}
string PermitionSqlZone = " And " + JPermission.getObjectSql("BusManagment.Zone.JZones.GetDataTable", "az.Code");
if (PermitionSqlZone.Length < 5)
{
PermitionSqlZone = "";
}
string CardTypePermitionSql = JPermission.getObjectSql("BusManagment.Card.JCards.GetDataTable", "Code");
string FinalCardType = "";
if (CardTypePermitionSql == " 1 = 1 ")
{
CardTypePermitionSql = "";
}
else
{
JDataBase mydb = new JDataBase();
mydb.setQuery("Select Type From AutCardType Where " + CardTypePermitionSql);
DataTable DtCardType = mydb.Query_DataTable();
FinalCardType = "and adp.CardType in (" + String.Join(",", JDataBase.DataTableToStringtArray(DtCardType, "Type")) + ")";
}
string Query = "", WhereStr = "", FleetWhereStr = "", WhereServiceStr = "", WhereStr2 = "", WhereStr3 = "", WhereStr4 = "", WhereStr5 = "";
DateTime NullDatetime = new DateTime(0001, 1, 1, 12, 00, 00);
DateTime StartDTime = new DateTime(StartEventDate.Value.Year, StartEventDate.Value.Month, StartEventDate.Value.Day);
DateTime EndDTime = new DateTime(EndEventDate.Value.Year, EndEventDate.Value.Month, EndEventDate.Value.Day);
if (ZoneCode > 0 || LineNumber > 0 || StartEventDate.HasValue == true || CardType > -1 || DayType > -1 || FleetCode > 0)
{
WhereStr = " where adp.TCount > 0 and adp.Price > 0 and adp.TicketPrice > 0 and adp.Error = 0 ";
if (ZoneCode > 0)
{
WhereStr += @" and az.Code=" + ZoneCode;
}
if (FleetCode > 0)
{
FleetWhereStr += " and FleetCode = " + FleetCode;
}
if (LineNumber > 0)
{
WhereStr += @" and al.Code=" + LineNumber;
}
if (StartEventDate.HasValue && StartEventDate.Value.Date > NullDatetime)
{
WhereStr += @" and adp.Date between '" + StartDTime.ToShortDateString() + " 00:00:00' and '" + EndDTime.ToShortDateString() + @" 23:59:59'";
WhereServiceStr += @" and Date between '" + StartDTime.ToShortDateString() + " 00:00:00' and '" + EndDTime.ToShortDateString() + @" 23:59:59'";
}
if (CardType > -1)
{
WhereStr += @" and adp.CardType=" + CardType;
}
if (DayType > -1)
{
WhereStr += " and adp.IsHoliDay = " + DayType;
}
}
if (chInCome)
{
WhereStr2 += "and adp.Date not in (select Date from AUTHolidays where AUTHolidays.date between '" + StartDTime.ToShortDateString() + " 00:00:00' and '" + EndDTime.ToShortDateString() + @" 23:59:59')";
WhereStr5 += "and adp.Date in (select Date from AUTHolidays where AUTHolidays.date between '" + StartDTime.ToShortDateString() + " 00:00:00' and '" + EndDTime.ToShortDateString() + @" 23:59:59')";
if (LineNumber > 0)
{
WhereStr3 += "and line.Code =" + LineNumber;
WhereStr4 += "and lineCode =" + LineNumber;
}
Query = @"select ROW_NUMBER()OVER(order by l.LineNumber) Code,*,round((income/busreal),0) AvgInCome
from (
select
a.LineNumber,case when OrganizationalService = 0 then 0 else cast(cast(DoneServiceCount as float) * OrganizationalBus / OrganizationalService as decimal(6, 2)) end BusReal
,cast(sum(adp.Tcount)as float)TransactionCount,a.DayKind,sum(cast(Price as float)) * 10.0 InCome,round((sum(cast(Price as float)) * 10.0)/((datediff(day,'" + StartDTime.ToShortDateString() + @"','" + EndDTime.ToShortDateString() + @"')+1)-(select count(*) from AUTHolidays where AUTHolidays.date between '" + StartDTime.ToShortDateString() + @"' and '" + EndDTime.ToShortDateString() + @"')),0)DailyIncome
from(
select
service.LineNumber, line.ZoneCode
,(select FLOOR(sum(c) * 1.0 / count(*)) from(select Date, count(*)c from(select Distinct buscode, Date from AUTTariff where 1 = 1 " + WhereStr4 + " and AUTTariff.LineCode = line.Code and AUTTariff.Date between '" + StartDTime.ToShortDateString() + @"' and '" + EndDTime.ToShortDateString() + @"' and AUTTariff.Date not in (select Date from AUTHolidays where AUTHolidays.date between '" + StartDTime.ToShortDateString() + @"' and '" + EndDTime.ToShortDateString() + @"'))a group by Date)a) OrganizationalBus,
(select sum(AUTTariff.MinNumOfService) from AUTTariff where 1 = 1 " + WhereStr4 + " and AUTTariff.LineCode = line.Code and AUTTariff.Date between '" + StartDTime.ToShortDateString() + @"' and '" + EndDTime.ToShortDateString() + @"' and AUTTariff.Date not in (select Date from AUTHolidays where AUTHolidays.date between '" + StartDTime.ToShortDateString() + @"' and '" + EndDTime.ToShortDateString() + @"' ) ) OrganizationalService,
sum(DoneServiceCount) DoneServiceCount,
N'روز عادی' DayKind
from
(select LineNumber, Date, sum(DoneServiceCount) DoneServiceCount from AUTServicingStatus group by LineNumber, Date) service
left join AUTLine line on line.LineNumber = service.LineNumber
where 1 = 1 " + WhereStr3 + " and service.Date between '" + StartDTime.ToShortDateString() + @"' and '" + EndDTime.ToShortDateString() + @"' and not exists(select code from AUTHolidays where AUTHolidays.date = service.Date)
group by service.LineNumber,line.ZoneCode,line.Code
)a
left join(select * from AUTDailyPerformanceRportOnBus where BusCode in (select Code from AUTBus where 1 = 1 and IsValid = 1 and Active = 1 " + FleetWhereStr + @")) adp on adp.LineNumber = a.LineNumber
left join AUTLine al on al.LineNumber = adp.LineNumber
left join AUTZone az on az.Code = al.ZoneCode
left join AUTFleet af on af.Code = al.Fleet
" + WhereStr + FinalCardType + PermitionSql + PermitionSqlZone + WhereStr2 + @"
group by adp.LineNumber,DayKind,a.LineNumber,OrganizationalService,OrganizationalBus,DoneServiceCount
union all
select
a.LineNumber,case when OrganizationalService = 0 then 0 else cast(cast(DoneServiceCount as float) * OrganizationalBus / OrganizationalService as decimal(6, 2)) end BusReal
,cast(sum(adp.Tcount)as float)TransactionCount,a.DayKind,sum(cast(Price as float)) * 10.0 InCome,round((sum(cast(Price as float)) * 10.0)/(select count(*) from AUTHolidays where AUTHolidays.date between '" + StartDTime.ToShortDateString() + @"' and '" + EndDTime.ToShortDateString() + @"'),0)DailyIncome
from(
select
service.LineNumber
, line.ZoneCode,
(select FLOOR(sum(c) * 1.0 / count(*)) from(select Date, count(*)c from(select Distinct buscode, Date from AUTTariff where 1 = 1 " + WhereStr4 + " and AUTTariff.Date in (select Date from AUTHolidays where AUTHolidays.date between '" + StartDTime.ToShortDateString() + @"' and '" + EndDTime.ToShortDateString() + @"'))a group by Date)a) OrganizationalBus,
(select sum(AUTTariff.MinNumOfService) from AUTTariff where 1 = 1 " + WhereStr4 + " and AUTTariff.LineCode = line.Code and AUTTariff.Date between '" + StartDTime.ToShortDateString() + @"' and '" + EndDTime.ToShortDateString() + @"' and AUTTariff.Date in (select Date from AUTHolidays where AUTHolidays.date between '" + StartDTime.ToShortDateString() + @"' and '" + EndDTime.ToShortDateString() + @"' ) ) OrganizationalService,
sum(DoneServiceCount) DoneServiceCount,
N'روز تعطیل' DayKind
from
(select LineNumber, Date, sum(DoneServiceCount) DoneServiceCount from AUTServicingStatus group by LineNumber, Date) service
left join AUTLine line on line.LineNumber = service.LineNumber
where 1 = 1 " + WhereStr3 + " and service.Date between '" + StartDTime.ToShortDateString() + @"' and '" + EndDTime.ToShortDateString() + @"' and exists(select code from AUTHolidays where AUTHolidays.date = service.Date)
group by service.LineNumber,line.ZoneCode,line.Code
)a
left join(select * from AUTDailyPerformanceRportOnBus where BusCode in (select Code from AUTBus where 1 = 1 and IsValid = 1 and Active = 1 " + FleetWhereStr + @")) adp on adp.LineNumber = a.LineNumber
left join AUTLine al on al.LineNumber = adp.LineNumber
left join AUTZone az on az.Code = al.ZoneCode
left join AUTFleet af on af.Code = al.Fleet
" + WhereStr + FinalCardType + PermitionSql + PermitionSqlZone + WhereStr5 + @"
group by adp.LineNumber,DayKind,a.LineNumber,OrganizationalService,OrganizationalBus,DoneServiceCount
)l
where BusReal<>0.00";
}
else
{
Query = @"select top 100 percent max(adp.Code)Code,af.Name FleetName,az.Name ZoneName,adp.LineNumber,adp.CardType,
cast(adp.TicketPrice * 10.0 as float)TicketPrice,cast(sum(adp.Tcount)as float)TransactionCount,sum(cast(adp.Price as float)) * 10.0 InCome ,
(
select sum(a.NumOfService) from AutBusServices a
where linenumber=adp.LineNumber " + WhereServiceStr + @"
) ServiceCount
from
(select * from AUTDailyPerformanceRportOnBus where BusCode in (select Code from AUTBus where 1 = 1 and IsValid = 1 and Active = 1 " + FleetWhereStr + @")) adp
left join AUTLine al on al.LineNumber = adp.LineNumber
left join AUTZone az on az.Code = al.ZoneCode
left join AUTFleet af on af.Code = al.Fleet
" + WhereStr + FinalCardType + PermitionSql + PermitionSqlZone + @"
group by af.Name,az.Name,adp.LineNumber,adp.CardType,adp.TicketPrice";
}
return(Query);
}
public static string GetWebReportQuery(int ZoneCode = 0, int DayType = -1, DateTime?StartEventDate = null, DateTime?EndEventDate = null,
int CardType = -1, int FleetCode = 0, Boolean noSHowTicketTypeCard = false)
{
string PermitionSql = " And " + JPermission.getObjectSql("BusManagment.Bus.JBuses.GetWebBusTransactionReportQuery", "adp.BusCode");
if (PermitionSql.Length < 5)
{
PermitionSql = "";
}
string CardTypePermitionSql = JPermission.getObjectSql("BusManagment.Card.JCards.GetDataTable", "Code");
string FinalCardType = "";
if (CardTypePermitionSql == " 1 = 1 ")
{
CardTypePermitionSql = "";
}
else
{
JDataBase mydb = new JDataBase();
mydb.setQuery("Select Type From AutCardType Where " + CardTypePermitionSql);
DataTable DtCardType = mydb.Query_DataTable();
FinalCardType = "and adp.CardType in (" + String.Join(",", JDataBase.DataTableToStringtArray(DtCardType, "Type")) + ")";
mydb.Dispose();
}
string Query = "", WhereStr = "", WhereStrFleet = "", WhereServiceStr = "";
DateTime NullDatetime = new DateTime(0001, 1, 1, 12, 00, 00);
DateTime StartDTime = new DateTime(StartEventDate.Value.Year, StartEventDate.Value.Month, StartEventDate.Value.Day);
DateTime EndDTime = new DateTime(EndEventDate.Value.Year, EndEventDate.Value.Month, EndEventDate.Value.Day);
if (ZoneCode > 0 || DayType > -1 || StartEventDate.HasValue == true || CardType > -1 || FleetCode > 0)
{
WhereStr = " where adp.TCount > 0 and adp.Price > 0 and adp.TicketPrice > 0 and adp.Error = 0 and ab.Active = 1 and ab.IsValid = 1 ";
if (ZoneCode > 0)
{
WhereStr += @" and az.Code=" + ZoneCode;
}
if (FleetCode > 0)
{
WhereStrFleet += @" and FleetCode = " + FleetCode;
}
if (StartEventDate.HasValue && StartEventDate.Value.Date > NullDatetime)
{
WhereStr += @" and adp.[Date] between '" + StartDTime.ToShortDateString() + " 00:00:00' and '" + EndDTime.ToShortDateString() + " 23:59:59'";
WhereServiceStr += @" and [Date] between '" + StartDTime.ToShortDateString() + " 00:00:00' and '" + EndDTime.ToShortDateString() + " 23:59:59'";
}
if (CardType > -1)
{
WhereStr += @" and adp.CardType=" + CardType;
}
if (DayType > -1)
{
WhereStr += " and adp.ISHoliDay = " + DayType;
}
}
if (noSHowTicketTypeCard)
{
Query = @"select top 100 percent max(adp.Code)Code,af.Name FleetName,az.Name ZoneName,
sum(adp.Tcount)TransactionCount,sum(cast(adp.Price as bigint))*10 InCome ,
(
select sum(a.NumOfService) from AutBusServices a
where zoneCode = az.code " + WhereServiceStr + @"
) ServiceCount
from
(select * from AUTDailyPerformanceRportOnBus where BusCode in (select Code from AUTBus where 1 = 1 " + WhereStrFleet + @")) adp
left join [dbo].[AUTBus] AB on adp.BusCode = AB.Code
left join AUTLine al on al.LineNumber = adp.LineNumber
left join AUTZone az on az.Code = al.ZoneCode
left join AUTFleet af on af.Code = al.Fleet
" + WhereStr + FinalCardType + PermitionSql + @"
group by af.Name,az.Name,az.code";
}
else
{
Query = @"select top 100 percent max(adp.Code)Code,af.Name FleetName,az.Name ZoneName,adp.CardType,cast(adp.TicketPrice * 10.0 as float) TicketPrice,
sum(adp.Tcount)TransactionCount,sum(cast(adp.Price as bigint))*10 InCome ,
(
select sum(a.NumOfService) from AutBusServices a
where zoneCode = az.code " + WhereServiceStr + @"
) ServiceCount
from
(select * from AUTDailyPerformanceRportOnBus where BusCode in (select Code from AUTBus where 1 = 1 " + WhereStrFleet + @")) adp
left join [dbo].[AUTBus] AB on adp.BusCode = AB.Code
left join AUTLine al on al.LineNumber = adp.LineNumber
left join AUTZone az on az.Code = al.ZoneCode
left join AUTFleet af on af.Code = al.Fleet
" + WhereStr + FinalCardType + PermitionSql + @"
group by af.Name,az.Name,adp.CardType,adp.TicketPrice,az.code";
}
return(Query);
}
public static string GetWebDriverPerformanceReportQuery(int DriverCode = 0, int ZoneCode = 0, int LineNumber = 0, int BusNumebr = 0, int DayType = -1, DateTime?StartEventDate = null, DateTime?EndEventDate = null, int OwnerCode = 0, bool CalcService = false, int CardType = -1, int FleetCode = 0, int MinTransaction = 0, int MaxTransaction = 0)
{
string PermitionSql = " and " + ClassLibrary.JPermission.getObjectSql("BusManagment.Bus.JBuses.GetAllBusesOnly", "DP.BusCode");
if (PermitionSql.Length < 5)
{
PermitionSql = "";
}
string CardTypePermitionSql = JPermission.getObjectSql("BusManagment.Card.JCards.GetDataTable", "Code");
string FinalCardType = "";
if (CardTypePermitionSql == " 1 = 1 ")
{
CardTypePermitionSql = "";
}
else
{
JDataBase mydb = new JDataBase();
mydb.setQuery("Select Type From AutCardType Where " + CardTypePermitionSql);
DataTable DtCardType = mydb.Query_DataTable();
FinalCardType = "and DP.CardType in (" + String.Join(",", JDataBase.DataTableToStringtArray(DtCardType, "Type")) + ")";
mydb.Dispose();
}
string Query = "", Having = "", WhereStr = " where Dp.TCount > 0 and DP.Price > 0 and DP.TicketPrice > 0 and DP.Error = 0 ";
string WhereDateStr = "";
string WhereMinMaxStr = "";
DateTime NullDatetime = new DateTime(0001, 1, 1, 12, 00, 00);
if (DriverCode > 0 || ZoneCode > 0 || LineNumber > 0 || BusNumebr > 0 || DayType > -1 || StartEventDate.HasValue == true || OwnerCode > 0 || CalcService == true || CardType > -1 || FleetCode > 0)
{
if (DriverCode > 0)
{
WhereStr += @" and DP.DriverPersonCode=" + DriverCode;
}
if (ZoneCode > 0)
{
WhereStr += @" and AZ.Code=" + ZoneCode;
}
if (FleetCode > 0)
{
WhereStr += @" and AL.Fleet = " + FleetCode;
}
if (LineNumber > 0)
{
WhereStr += @" and AL.Code=" + LineNumber;
}
if (OwnerCode > 0)
{
WhereStr += @" and DP.OwnerCode = " + OwnerCode;
}
else
{
if (BusNumebr > 0)
{
WhereStr += @" and DP.BusCode=" + BusNumebr;
}
}
if (StartEventDate.HasValue && StartEventDate.Value.Date > NullDatetime)
{
DateTime StartDTime = new DateTime(StartEventDate.Value.Year, StartEventDate.Value.Month, StartEventDate.Value.Day);
DateTime EndDTime = new DateTime(EndEventDate.Value.Year, EndEventDate.Value.Month, EndEventDate.Value.Day);
WhereStr += @" and DP.[Date] between '" + StartDTime.ToShortDateString() + "' and '" + EndDTime.ToShortDateString() + "'";
WhereDateStr += @" between '" + StartDTime.ToShortDateString() + "' and '" + EndDTime.ToShortDateString() + "'";
}
//if(CalcService==true)
if (DayType > -1)
{
WhereStr += " and DP.IsHoliDay = " + DayType;
}
if (CardType > -1)
{
WhereStr += @" and DP.CardType=" + CardType;
}
}
if (MinTransaction > 0)
{
WhereMinMaxStr += " and TransactionCount/WorkDay > " + MinTransaction;
}
if (MaxTransaction > 0)
{
WhereMinMaxStr += " and TransactionCount/WorkDay < " + MaxTransaction;
}
Query = @"select *,CASE WHEN WorkDay=0 THEN 0 ELSE TransactionCount/WorkDay END AvgWork from
(
select top 100 percent max(DP.Code) as Code,cap.Name as OwnerName,DP.DriverPersonCode as PersonelCode,ALLP.Name as DriverName,DP.DriverCardSerial
,AF.Name as FleetName,AZ.Name as ZoneName,
DP.LineNumber,AB.BUSNumber,DP.CardType,DP.TicketPrice * 10 as TicketPrice,sum(DP.TCount) as TransactionCount,sum(DP.Price) * 10 as InCome
,
(
select count(*) from
(
select Date from [AUTDailyPerformanceRportOnBus] AP1
where AP1.DriverCardSerial=dp.DriverCardSerial
and ap1.BusNumber = ab.BusNumber
and ap1.TicketPrice=dp.TicketPrice
and ap1.LineNumber=dp.LineNumber
and ap1.CardType=dp.CardType
and AP1.Date " + WhereDateStr + @"
group by Date
)a
) WorkDay,
(SELECT sum(DATEDIFF(minute, FirstStationDate,LastStationDate)) FROM AutBusServices ABC WHERE ABC.LineNumber=DP.LineNumber and abc.Date " + WhereDateStr + @" and abc.BusNumber=AB.BusNumber and abc.DriverPersonCode=dp.DriverPersonCode) KarKard
from [dbo].[AUTDailyPerformanceRportOnBus] DP
left join [dbo].[AUTBus] AB on DP.BusCode = AB.Code
left join [dbo].[AUTLine] AL on DP.LineNumber = AL.LineNumber
left join [dbo].[AUTZone] AZ on AL.ZoneCode = AZ.Code
left join [dbo].[AUTFleet] AF on AL.Fleet = AF.Code
left join [dbo].[clsAllPerson] ALLP on DP.DriverPersonCode = ALLP.Code
left join AUTBusOwner abo on DP.BusCode = abo.BusCode and abo.IsActive=1
left join clsAllPerson cap on abo.CodePerson = cap.Code
" + WhereStr + PermitionSql + FinalCardType + @"
group by cap.Name,DP.CardType,DP.DriverPersonCode,ALLP.Name,DP.DriverCardSerial,AF.Name,AZ.Name,DP.LineNumber,AB.BUSNumber,DP.TicketPrice
) as a
where 1=1 " + WhereMinMaxStr;
return(Query);
}
public static string GetWebDriverPerformanceReportQueryWithMultiDriver(int[] DriverCode, DateTime?StartEventDate = null, DateTime?EndEventDate = null, int ZoneCode = 0, int LineNumber = 0, int BusNumebr = 0, int CardType = -1, int BusOwner = 0, int fromId = 0, int ToId = 0)
{
string PermitionSql = " and " + ClassLibrary.JPermission.getObjectSql("BusManagment.Bus.JBuses.GetAllBusesOnly", "DP.BusCode");
if (PermitionSql.Length < 5)
{
PermitionSql = "";
}
string CardTypePermitionSql = JPermission.getObjectSql("BusManagment.Card.JCards.GetDataTable", "Code");
string FinalCardType = "";
if (CardTypePermitionSql == " 1 = 1 ")
{
CardTypePermitionSql = "";
}
else
{
JDataBase mydb = new JDataBase();
mydb.setQuery("Select Type From AutCardType Where " + CardTypePermitionSql);
DataTable DtCardType = mydb.Query_DataTable();
FinalCardType = "and DP.CardType in (" + String.Join(",", JDataBase.DataTableToStringtArray(DtCardType, "Type")) + ")";
mydb.Dispose();
}
string Query = "", WhereStr = "";
DateTime NullDatetime = new DateTime(0001, 1, 1, 12, 00, 00);
if (StartEventDate.HasValue == true || DriverCode.Length > 0 || ZoneCode > 0 || LineNumber > 0 || BusNumebr > 0 || CardType > -1)
{
WhereStr = " where 1=1 ";
if (DriverCode.Length > 0)
{
WhereStr += @" and ALLP.Code in (" + DriverCode;
for (int i = 0; i < DriverCode.Length; i++)
{
WhereStr += @"" + DriverCode[i].ToString() + @",";
}
WhereStr = WhereStr.Remove(WhereStr.Length - 1, 1);
WhereStr += ") ";
}
if (ZoneCode > 0)
{
WhereStr += " and AZ.Code = " + ZoneCode;
}
if (LineNumber > 0)
{
WhereStr += " and AL.Code = " + LineNumber;
}
if (BusNumebr > 0)
{
WhereStr += " and DP.BusCode = " + BusNumebr;
}
if (CardType > -1)
{
WhereStr += " and DP.CardType = " + CardType;
}
if (BusOwner > 0)
{
WhereStr += " and DP.OwnerCode = " + BusOwner;
}
if (StartEventDate.HasValue && StartEventDate.Value.Date > NullDatetime)
{
DateTime StartDTime = new DateTime(StartEventDate.Value.Year, StartEventDate.Value.Month, StartEventDate.Value.Day);
DateTime EndDTime = new DateTime(EndEventDate.Value.Year, EndEventDate.Value.Month, EndEventDate.Value.Day);
WhereStr += @" and DP.Date between '" + StartDTime.ToShortDateString() + "' and '" + EndDTime.ToShortDateString() + "'";
}
}
if (ToId == 0)
{
ToId = 1000000;
}
Query = @"select b.* from (
select row_number() over (order by A.TransactionCount)ROWID,A.* from (
select isnull(ALLP.Name,AB.BUSNumber) as DriverName,sum(cast(DP.TCount as float)) as TransactionCount,sum(cast(DP.Price as float)) * 10 as InCome
from [dbo].[AUTDailyPerformanceRportOnBus] DP
left join [dbo].[AUTBus] AB on DP.BusCode = AB.Code
left join [dbo].[AUTLine] AL on DP.LineNumber = AL.LineNumber
left join [dbo].[AUTZone] AZ on AL.ZoneCode = AZ.Code
left join [dbo].[AUTFleet] AF on AL.Fleet = AF.Code
left join [dbo].[clsAllPerson] ALLP on DP.DriverPersonCode = ALLP.Code
" + WhereStr + PermitionSql + FinalCardType + @"
group by ALLP.Name,AB.BUSNumber)A )B where B.RowID between " + fromId + @" and " + ToId;
return(Query);
}