public static bool Disapprove(int pCode)
{
ClassLibrary.JDataBase Db = new ClassLibrary.JDataBase();
Db.setQuery(@"DECLARE @StartDate DATETIME
UPDATE AutTarrifEzamBe SET IsOk = 0, @StartDate = StartTime WHERE Code = " + pCode + @"
UPDATE AutBusServices SET IsOk = 3 WHERE Date = CAST(@StartDate AS DATE) AND isok<>11 and EzamBeCode = " + pCode);
Db.beginTransaction("EzamBeDisapprove");
try
{
if (Db.Query_Execute() >= 0)
{
Db.Commit();
return(true);
}
else
{
Db.Rollback("EzamBeDisapprove");
return(false);
}
}
catch (Exception ex)
{
ClassLibrary.JSystem.Except.AddException(ex);
Db.Rollback("EzamBeDisapprove");
return(false);
}
finally { Db.Dispose(); }
}
public static int AvlTestInsert(string data, string method)
{
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
try
{
DB.setQuery(string.Format(@" INSERT INTO avlTest (data,method) values (N'{0}','{1}')",
data,
method));
DB.Query_Execute();
DB.setQuery("SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];");
System.Data.DataTable DT = DB.Query_DataTable();
if (DT.Rows.Count == 1)
{
return(int.Parse(DT.Rows[0][0].ToString().Trim()));
}
return(0);
}
catch
{
return(0);
}
finally
{
DB.Dispose();
}
}
public static bool AvlTestDelete(int pCode)
{
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
try
{
DB.setQuery(string.Format(@"DELETE FROM avlTest WHERE Code = {0}", pCode));
return(DB.Query_Execute() >= 0);
}
catch
{
return(false);
}
finally
{
DB.Dispose();
}
}
public static bool UpdateDocumentCode(int[] Codes, int DocumentCode)
{
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
try
{
DB.setQuery(string.Format(@"UPDATE AUTDailyPerformanceRportOnBus SET DocumentCode = {0}
WHERE Code IN({1})", DocumentCode, JDataBase.GetInSQLClause(Codes)));
if (DB.Query_Execute() >= 0)
{
return(true);
}
return(false);
}
catch
{
return(false);
}
finally
{
DB.Dispose();
}
}
public bool Update(DateTime pDate,
uint pBusCode,
string pDriverCode,
int pOwnerCode,
uint pLineCode,
int pZoneCode,
uint pPrice)
{
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
try
{
DB.setQuery(@"SELECT Code FROM AUTDailyPerformanceRportOnBus
WHERE
CAST(Date as date)=CAST('" + pDate.ToString("yyyy-MM-dd") + @"' as date) AND
BusCode=@BusCode AND
DriverCode=@DriverCode AND
OwnerCode=@OwnerCode AND
LineCode=@LineCode AND
ZoneCode=@ZoneCode AND
DocumentCode <> 0 AND DocumentCode IS NOT Null ");
DB.AddParams("Date", pDate);
DB.AddParams("BusCode", Convert.ToInt32(pBusCode));
DB.AddParams("DriverCode", pDriverCode);
DB.AddParams("OwnerCode", pOwnerCode);
DB.AddParams("LineCode", Convert.ToInt32(pLineCode));
DB.AddParams("ZoneCode", pZoneCode);
DB.AddParams("Price", Convert.ToInt32(pPrice));
System.Data.DataTable DT = DB.Query_DataTable();
if (DT.Rows.Count > 1)
{
}
else
if (DT.Rows.Count == 1)
{
DB.setQuery(@"UPDATE AUTDailyPerformanceRportOnBus SET Price=Price+@Price , TCount=TCount+1
WHERE Code = " + DT.Rows[0][0].ToString());
if (DB.Query_Execute() >= 0)
{
return(true);
}
return(false);
}
else
{
DB.setQuery(@"INSERT INTO AUTDailyPerformanceRportOnBus
(Code,Date,BusCode,DriverCode,OwnerCode,LineCode,ZoneCode,Price,TCount)
Values(
isnull((Select MAX(Code) From AUTDailyPerformanceRportOnBus), 0) + 1,
@Date,
@BusCode,
@DriverCode,
@OwnerCode,
@LineCode,
@ZoneCode,
@Price,
1
)");
if (DB.Query_Execute() >= 0)
{
return(true);
}
return(false);
}
}
finally
{
DB.Dispose();
}
return(false);
}
public static void ServiceProcessFunction(ClassLibrary.JDataBase Db)
{
//Service Set With Job This Process Not Need
return;
//Start
Db.setQuery(@"select top 1000 TblA.*,TblB.LineCode,TblB.GoFirstStation,TblB.GoLastStation,TblB.BackFirstStation,TblB.BackLastStation from (
select
abps.[Code],abps.[EventDate],abps.[BusNumber],abps.[StationCode],abps.[InsertDate],abps.[IsProcessed],abps.[ProcessDate],ab.LastLineNumber
from AutBusPassingStations abps
left join AUTBus ab on abps.BusNumber = ab.BUSNumber
where abps.IsProcessed = 0 and abps.EventDate between DateADD(month , -1 ,getdate()) and getdate()
)TblA
left join (
select al.Code LineCode,al.LineNumber
,(select top 1 StationCode from AUTLineStation where LineCode = al.Code and IsBack = 0 and Priority =
(select min(Priority) from AUTLineStation where LineCode = al.Code and IsBack = 0)) GoFirstStation
,(select top 1 StationCode from AUTLineStation where LineCode = al.Code and IsBack = 0 and Priority =
(select max(Priority) from AUTLineStation where LineCode = al.Code and IsBack = 0)) GoLastStation
,(select top 1 StationCode from AUTLineStation where LineCode = al.Code and IsBack = 1 and Priority =
(select min(Priority) from AUTLineStation where LineCode = al.Code and IsBack = 1)) BackFirstStation
,(select top 1 StationCode from AUTLineStation where LineCode = al.Code and IsBack = 1 and Priority =
(select max(Priority) from AUTLineStation where LineCode = al.Code and IsBack = 1)) BackLastStation
from AUTLine al
where al.Code in (select distinct LineCode from AUTLineStation)
)TblB on TblA.LastLineNumber = TblB.LineNumber
");
DataTable DtStationPass = Db.Query_DataTable();
if (DtStationPass != null)
{
if (DtStationPass.Rows.Count > 0)
{
for (int i = 0; i < DtStationPass.Rows.Count; i++)
{
if (DtStationPass.Rows[i]["StationCode"].ToString() == DtStationPass.Rows[i]["GoFirstStation"].ToString() ||
DtStationPass.Rows[i]["StationCode"].ToString() == DtStationPass.Rows[i]["BackFirstStation"].ToString())
{
Db.setQuery(@"INSERT INTO [dbo].[AutBusServices]
([Date]
,[BusNumber]
,[FirstStationCode]
,[FirstStationDate]
,[LastStationCode]
,[LastStationDate]
,[DriverCardSerial]
,[DriverPersonCode]
,[InsertDate]
,[FromConsole]
,[IsOk]
,[Deleted]
,[NumOfService]
,[EzamBeCode]
,[LineNumber])
VALUES
(cast('" + DtStationPass.Rows[i]["EventDate"].ToString() + @"' as date)
," + DtStationPass.Rows[i]["BusNumber"].ToString() + @"
," + DtStationPass.Rows[i]["StationCode"].ToString() + @"
,'" + DtStationPass.Rows[i]["EventDate"].ToString() + @"'
,0
,null
,0
,0
,getdate()
,0
,4
,0
,1
,null
,null)");
Db.Query_Execute();
}
else if (DtStationPass.Rows[i]["StationCode"].ToString() == DtStationPass.Rows[i]["GoLastStation"].ToString() ||
DtStationPass.Rows[i]["StationCode"].ToString() == DtStationPass.Rows[i]["BackLastStation"].ToString())
{
Db.setQuery(@"UPDATE [dbo].[AutBusServices]
SET [LastStationCode] = " + DtStationPass.Rows[i]["StationCode"].ToString() + @"
,[LastStationDate] = '" + DtStationPass.Rows[i]["EventDate"].ToString() + @"'
,[InsertDate] = getdate()
WHERE [BusNumber] = " + DtStationPass.Rows[i]["BusNumber"].ToString() + @" and [Date] = cast('" + DtStationPass.Rows[i]["EventDate"].ToString() + @"' as date)
and ([LastStationCode] = 0 or [LastStationCode] Is Null)");
Db.Query_Execute();
}
Db.setQuery(@"update AutBusPassingStations set IsProcessed = 1 , ProcessDate = getdate() where Code = " + DtStationPass.Rows[i]["Code"].ToString());
Db.Query_Execute();
}
}
}
//End
}