/// <summary>
/// انتخاب تاریخ ها جهت صدور سند - تاریخ کامل بر اساس سال و ماه
/// </summary>
/// <returns></returns>
public static DataTable GetFullDatesToIssueDocument(int Year, int Month, int FleetCode)
{
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
try
{
DB.setQuery(string.Format(@"Select Distinct CAST(Date AS Date ) Date,SUM(adp.TCount) TCount
from AUTDailyPerformanceRportOnBus adp
left join AUTBus ab on adp.BusCode = ab.Code
WHERE (substring(dbo.DateEnToFa(adp.Date),0,5)=" + Year + @"
AND substring(dbo.DateEnToFa(adp.Date),6,2)=" + Month + @") AND
(adp.DocumentCode < 1 OR adp.DocumentCode IS NULL) And adp.CardType=0 and adp.Tcount > 0 and adp.SetPrinter = 1 and adp.Error = 0
and ab.FleetCode = " + FleetCode + @" and ab.active = 1
GROUP BY Date
ORDER BY Date "));
return(DB.Query_DataTable());
}
catch
{
return(null);
}
finally
{
DB.Dispose();
}
}
public static DataTable ReportDialyPerformance(string pSelect, string pWhere, string pGroupby)
{
ClassLibrary.JDataBase db = new ClassLibrary.JDataBase();
try
{
if (pSelect.Trim() == string.Empty)
{
pSelect = " tbl.Code, tbl.Date, tbl.BUSNumber, tbl.DriverName, tbl.BusOwnerName, tbl.LineName, tbl.LineNumber, tbl.ZoneName, " +
"tbl.AddressZone, tbl.TelZone, tbl.Price";
}
db.setQuery("select " + pSelect + @" from (select dbo.AUTDailyPerformanceRportOnBus.Code, dbo.AUTDailyPerformanceRportOnBus.Date, dbo.AUTBus.BUSNumber, dbo.clsAllPerson.Name AS DriverName,
clsAllPerson_1.Name AS BusOwnerName, dbo.AUTLine.LineName, dbo.AUTLine.LineNumber, dbo.AUTZone.Name AS ZoneName,
dbo.AUTZone.Address AS AddressZone, dbo.AUTZone.Tel AS TelZone, dbo.AUTDailyPerformanceRportOnBus.Price , dbo.AUTDailyPerformanceRportOnBus.DriverCode,dbo.AUTDailyPerformanceRportOnBus.OwnerCode,dbo.AUTDailyPerformanceRportOnBus.LineCode,dbo.AUTDailyPerformanceRportOnBus.ZoneCode,dbo.AUTDailyPerformanceRportOnBus.BusCode " +
@" FROM AUTDailyPerformanceRportOnBus
left JOIN AUTZone ON AUTZone.Code = AUTDailyPerformanceRportOnBus.ZoneCode
left JOIN AUTBus ON AUTDailyPerformanceRportOnBus.BusCode = AUTBus.BUSNumber
left JOIN AUTBusOwner ON AUTDailyPerformanceRportOnBus.OwnerCode = AUTBusOwner.Code
left JOIN AUTLine ON AUTDailyPerformanceRportOnBus.LineCode = AUTLine.LineNumber
left JOIN Cards ON AUTDailyPerformanceRportOnBus.DriverCode = Cards.CardCode
left JOIN clsAllPerson ON Cards.PCode = clsAllPerson.Code
left JOIN clsAllPerson AS clsAllPerson_1 ON clsAllPerson_1.Code = AUTBusOwner.CodePerson ) tbl " + " " + pWhere + " " + pGroupby);
return(db.Query_DataTable());
}
catch
{
return(null);
}
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();
}
}
private void contextMenuStrip2_ItemClicked(object sender, ToolStripItemClickedEventArgs e)
{
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
try
{
DB.setQuery("select * from refGroupName where PostCode=" + ClassLibrary.JMainFrame.CurrentPostCode.ToString() + " and groupName=N'" + e.ClickedItem.Text + "' order by GroupName");
DataTable DT = DB.Query_DataTable();
foreach (DataRow dr in DT.Rows)
{
for (int i = 0; i < chbUsers.Items.Count; i++)
{
if ((chbUsers.Items[i] as ClassLibrary.JKeyValue).Value.ToString() == dr["PostCodeGuest"].ToString())
{
chbUsers.SetItemChecked(i, true);
}
}
}
}
catch
{
}
finally
{
DB.Dispose();
}
}
/// <summary>
/// خروجی گزارش بر اساس تاریخ ها
/// </summary>
/// <returns></returns>
public static DataTable GetDriversReportByDate(DateTime[] Dates, int[] FleetCode = null)
{
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
try
{
string FleetCodeQuery = "";
if (FleetCode.Length > 0)
{
FleetCodeQuery = " And AUTBus.FleetCode in (";
for (int i = 0; i < FleetCode.Length; i++)
{
FleetCodeQuery += FleetCode[i].ToString() + ",";
}
FleetCodeQuery = FleetCodeQuery.Remove(FleetCodeQuery.Length - 1, 1);
FleetCodeQuery += ")";
}
string strDates = "";
for (int i = 0; i < Dates.Length; i++)
{
strDates += "'" + Dates[i].ToString("yyyy-MM-dd") + "'";;
if (Dates.Length > 0 && i < Dates.Length - 1)
{
strDates += ", ";
}
}
DB.setQuery(string.Format(@" SELECT
OwnerCode , clsAllPerson .Name OwnerName ,fba.AccountNo , AUTBus.Code BusCode , AUTBus .BUSNumber,AUTBus.LastLineNumber
,Sum(TCount) 'Count', Sum(cast(Price as float)) * 10.0 as SumPrice
FROM [AUTDailyPerformanceRportOnBus]
Left JOIN clsAllPerson ON clsAllPerson .Code = [AUTDailyPerformanceRportOnBus].OwnerCode
Left JOIN AUTBus ON AUTBus.Code = [AUTDailyPerformanceRportOnBus].BusCode
LEFT JOIN finBankAccount fba ON fba.PCode = [AUTDailyPerformanceRportOnBus].OwnerCode
WHERE AUTBus.Active = 1 and Error = 0 and Tcount > 0 and SetPrinter = 1 and CardType=0 and (DocumentCode < 1 OR DocumentCode IS NULL )
AND (Cast ([AUTDailyPerformanceRportOnBus].Date AS Date) IN ({0}))
" + FleetCodeQuery + @"
Group By [AUTDailyPerformanceRportOnBus].OwnerCode , clsAllPerson .Name,fba.AccountNo
, AUTBus.Code , AUTBus .BUSNumber,AUTBus.LastLineNumber
order by OwnerName
", strDates));
return(DB.Query_DataTable());
}
catch
{
return(null);
}
finally
{
DB.Dispose();
}
}
public bool Delete()
{
// if (!JPermission.CheckPermission("BusManagment.WorkOrder.JTariff.Delete"))
// return false;
EzamBeTable AT = new EzamBeTable();
AT.SetValueProperty(this);
//if (JMessages.Question("آیا میخواهید سرویس انتخاب شده حذف شود؟", "") == System.Windows.Forms.DialogResult.Yes)
// {
ClassLibrary.JDataBase Db = new ClassLibrary.JDataBase();
Db.setQuery(@"DELETE FROM AutBusServices WHERE Date = '" + this.StartTime.ToString("yyyy-MM-dd") + "' AND isok<>11 and EzamBeCode = " + Code + @"
declare @tarrif_code int = (select TarrifCode from AutTarrifEzamBe where code = " + Code + @")
if((select Status from AUTTariff where code = @tarrif_code) = 1)
begin
exec [dbo].[SP_TarrifeTaeeidByTarrifCode]
@tarif_code = @tarrif_code
end
select 1");
Db.beginTransaction("EzamBeDelete");
try
{
if (Convert.ToInt32(Db.Query_DataTable().Rows[0][0]) == 1)
{
if (AT.Delete())
{
Db.Commit();
return(true);
}
else
{
Db.Rollback("EzamBeDelete");
return(false);
}
}
}
catch (Exception ex)
{
ClassLibrary.JSystem.Except.AddException(ex);
Db.Rollback("EzamBeDelete");
return(false);
}
finally { Db.Dispose(); }
// }
return(false);
}
/// <summary>
/// انتخاب تاریخ ها جهت صدور سند
/// </summary>
/// <returns></returns>
public static DataTable GetDatesToIssueDocument()
{
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
try
{
DB.setQuery(string.Format(@" Select Distinct CAST(Date AS Date ) Date
-- (Select Fa_Date FROM StaticDates WHERE En_Date = CAST(Date AS Date )) Date
from AUTDailyPerformanceRportOnBus
WHERE DocumentCode = 0 OR DocumentCode IS NULL ORDER BY Date "));
return(DB.Query_DataTable());
}
catch
{
return(null);
}
finally
{
DB.Dispose();
}
}
/// <summary>
/// انتخاب تاریخ ها جهت صدور سند - سال ها
/// </summary>
/// <returns></returns>
public static DataTable GetYearDatesToIssueDocument(int FleetCode)
{
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
try
{
DB.setQuery(string.Format(@"Select Distinct substring(dbo.DateEnToFa(adp.Date),0,5) Date
from AUTDailyPerformanceRportOnBus adp
left join AUTBus ab on adp.BusCode = ab.Code
WHERE adp.Error = 0 and adp.Tcount > 0 and adp.SetPrinter = 1 and adp.CardType=0
And (adp.DocumentCode < 1 OR adp.DocumentCode IS NULL) and ab.FleetCode = " + FleetCode.ToString() + " and ab.active = 1 ORDER BY Date "));
return(DB.Query_DataTable());
}
catch
{
return(null);
}
finally
{
DB.Dispose();
}
}
/// <summary>
/// انتخاب ناوگان ها جهت صدور سند - ناوگان ها
/// </summary>
/// <returns></returns>
public static DataTable GetFleetsToIssueDocument()
{
ClassLibrary.JDataBase DB = new ClassLibrary.JDataBase();
try
{
DB.setQuery(string.Format(@"Select distinct af.Name FleetName,af.Code FleetCode
from AUTDailyPerformanceRportOnBus adp
left join AUTBus ab on adp.BusCode = ab.Code
left join AUTFleet af on ab.FleetCode = af.Code
WHERE adp.Tcount > 0 and adp.SetPrinter = 1 and adp.CardType=0 And (adp.DocumentCode < 1 OR adp.DocumentCode IS NULL)
And af.Name Is NOT NULL and adp.Error = 0"));
return(DB.Query_DataTable());
}
catch
{
return(null);
}
finally
{
DB.Dispose();
}
}
private void btnSeletGroup_Click(object sender, EventArgs e)
{
contextMenuStrip2.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");
DataTable DT = DB.Query_DataTable();
foreach (DataRow dr in DT.Rows)
{
contextMenuStrip2.Items.Add(dr["GroupName"].ToString());
}
}
catch
{
}
finally
{
DB.Dispose();
}
contextMenuStrip2.Show(btnSeletGroup, new Point(0, 0));
}
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 int Insert(bool isWeb = false)
{
//if (!JPermission.CheckPermission("BusManagment.WorkOrder.JTariff.Insert"))
// return 0;
string Query = @"
DECLARE @EzamBeCode int = (SELECT ISNULL(MAX(Code), 0) + 1 FROM [dbo].[AutTarrifEzamBe])
INSERT INTO [dbo].[AutTarrifEzamBe]
([Code]
,[TarrifCode]
,[DriverPCode]
,[LineCode]
,[EzamBe]
,[BusCodeBeJa]
,[NumOfSevice]
,[FirstStationCode]
,[LastStationCode]
,[StartTime]
,[FinishTime]
,[IsOk]
,[InsertDate])
VALUES
(@EzamBeCode
," + this.TarrifCode + @"
," + this.DriverPCode + @"
," + this.LineCode + @"
," + this.EzamBe + @"
," + this.BusCodeBeJa + @"
," + this.NumOfSevice + @"
,null
,null
,'" + this.StartTime + @"'
,'" + this.FinishTime + @"'
,1
,GETDATE())
INSERT INTO [dbo].[AutBusServices]
([Date]
,[BusNumber]
,[FirstStationCode]
,[FirstStationDate]
,[LastStationCode]
,[LastStationDate]
,[DriverCardSerial]
,[DriverPersonCode]
,[InsertDate]
,[FromConsole]
,[IsOk]
,[Deleted]
,[NumOfService]
,[EzamBeCode]
,[LineNumber])
VALUES
('" + this.StartTime.ToString("yyyy-MM-dd") + @"'
,(SELECT BusNumber FROM AUTBus WHERE Code = (SELECT BusCode FROM AUTTAriff WHERE Code = " + this.TarrifCode + @"))
,null
,'" + this.StartTime + @"'
,null
,'" + this.FinishTime + @"'
,null
," + this.DriverPCode + @"
,GETDATE()
,0
,4
,0
," + this.NumOfSevice + @"
,@EzamBeCode
,(Select LineNumber from AUTLine Where Code=" + this.LineCode + @" ))
declare @tarrif_code int = (select TarrifCode from AutTarrifEzamBe where code = @EzamBeCode)
if((select Status from AUTTariff where code = @tarrif_code) = 1)
begin
exec [dbo].[SP_TarrifeTaeeidByTarrifCode]
@tarif_code = @tarrif_code
end
SELECT @EzamBeCode";
//,(SELECT BusNumber FROM AUTBus WHERE Code = " + (this.BusCodeBeJa > 0 ? this.BusCodeBeJa.ToString() : "(SELECT BusCode FROM AUTTAriff WHERE Code = " + this.TarrifCode + ")") + @")
EzamBeTable AT = new EzamBeTable();
ClassLibrary.JDataBase Db = new ClassLibrary.JDataBase();
DataTable dt = new DataTable();
Db.setQuery(Query);
Db.beginTransaction("EzamBeInsert");
try
{
dt = Db.Query_DataTable();
Code = Convert.ToInt32(dt.Rows[0][0]);
Db.Commit();
}
catch (Exception ex)
{
ClassLibrary.JSystem.Except.AddException(ex);
Db.Rollback("EzamBeInsert");
}
finally
{
Db.Dispose();
}
if (Code > 0 && !isWeb)
{
Nodes.DataTable.Merge(JEzamBes.GetDataTable(Code));
}
return(Code);
}
public bool Update(bool isWeb = false)
{
//if (!JPermission.CheckPermission("BusManagment.WorkOrder.JTariff.Update"))
// return false;
string Query = @"
UPDATE [dbo].[AutTarrifEzamBe]
SET
DriverPCode = " + this.DriverPCode + @"
,LineCode = " + this.LineCode + @"
,EzamBe = " + this.EzamBe + @"
,BusCodeBeJa = " + this.BusCodeBeJa + @"
,NumOfSevice = " + this.NumOfSevice + @"
,StartTime = '" + this.StartTime + @"'
,FinishTime = '" + this.FinishTime + @"'
WHERE Code = " + Code + @"
UPDATE AutBusServices
SET
Date = '" + this.StartTime.ToString("yyyy-MM-dd") + @"'
,FirstStationDate = '" + this.StartTime + @"'
,LastStationDate = '" + this.FinishTime + @"'
,DriverPersonCode = " + this.DriverPCode + @"
,NumOfService = " + this.NumOfSevice + @"
WHERE Date = '" + this.StartTime.ToString("yyyy-MM-dd") + "' AND isok<>11 and EzamBeCode = " + Code + @"
declare @tarrif_code int = (select TarrifCode from AutTarrifEzamBe where code = " + Code + @")
if((select Status from AUTTariff where code = @tarrif_code) = 1)
begin
exec [dbo].[SP_TarrifeTaeeidByTarrifCode]
@tarif_code = @tarrif_code
end
select 1" ;
//,BusNumber = (SELECT BusNumber FROM AUTBus WHERE Code = " + (this.BusCodeBeJa > 0 ? this.BusCodeBeJa.ToString() : "(SELECT BusCode FROM AUTTAriff WHERE Code = " + this.TarrifCode + ")") + @")
EzamBeTable AT = new EzamBeTable();
ClassLibrary.JDataBase Db = new ClassLibrary.JDataBase();
DataTable dt = new DataTable();
Db.setQuery(Query);
Db.beginTransaction("EzamBeUpdate");
try
{
if (Convert.ToInt32(Db.Query_DataTable().Rows[0][0]) == 1)
{
if (!isWeb)
{
Nodes.Refreshdata(Nodes.CurrentNode, JEzamBes.GetDataTable(Code).Rows[0]);
}
Db.Commit();
return(true);
}
else
{
Db.Rollback("EzamBeUpdate");
return(false);
}
}
catch (Exception ex)
{
ClassLibrary.JSystem.Except.AddException(ex);
Db.Rollback("EzamBeUpdate");
return(false);
}
finally { Db.Dispose(); }
}
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
}