270 lines
12 KiB
C#
270 lines
12 KiB
C#
|
using HRM.BO;
|
|||
|
|
using Ease.Core.DataAccess;
|
|||
|
|
using System;
|
|||
|
|
using System.Data;
|
|||
|
|
|
|||
|
|
namespace HRM.DA
|
|||
|
|
{
|
|||
|
|
internal class LocationDA
|
|||
|
|
{
|
|||
|
|
#region Constructor
|
|||
|
|
|
|||
|
|
private LocationDA()
|
|||
|
|
{
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#region Insert function
|
|||
|
|
|
|||
|
|
internal static void Insert(TransactionContext tc, Location item)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery(
|
|||
|
|
"INSERT INTO Location(LocationID, code, description, parentID, CreatedBy, CreationDate, SequenceNo, Status,TIRE, payrolltypeid, Longitude, Latitude, AttendancePoint, descriptioninbangla, LatLongSetBy, LatLongSetDate, LatLongApproveStatus)" +
|
|||
|
|
" VALUES(%n, %s, %s, %n,%n, %d, %n, %n,%n,%n,%n,%n, %b, %u, %n, %d, %b)", item.ID, item.Code, item.Name, item.ParentID,
|
|||
|
|
item.CreatedBy, item.CreatedDate, item.Sequence, item.Status, item.Tier, item.PayrollTypeID, item.Longitude, item.Latitude, item.AttendancePoint, item.NameInBangla, item.LatLongSetBy, item.LatLongSetDate, item.LatLongApproveStatus);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#region Update function
|
|||
|
|
|
|||
|
|
internal static void Update(TransactionContext tc, Location item)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL(@"UPDATE Location SET code=%s, description=%s, parentID=%n,ModifiedBy=%n, ModifiedDate=%d, SequenceNo=%n, Status=%n, Longitude=%n, Latitude=%n, AttendancePoint=%b, descriptioninbangla=%u, LatLongSetBy=%n, LatLongSetDate=%d, LatLongApproveStatus=%b" +
|
|||
|
|
" WHERE LocationID=%n", item.Code, item.Name, item.ParentID, item.ModifiedBy, item.ModifiedDate,
|
|||
|
|
item.Sequence, item.Status, item.Longitude, item.Latitude, item.AttendancePoint, item.NameInBangla, item.LatLongSetBy, item.LatLongSetDate, item.LatLongApproveStatus ,item.ID);
|
|||
|
|
tc.ExecuteNonQuery(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#region Get Function
|
|||
|
|
|
|||
|
|
internal static IDataReader Get(TransactionContext tc)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM Location");
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader Get(TransactionContext tc, int ID)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM Location WHERE LocationID=%n", ID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetEmployeeLocation(TransactionContext tc, int ID)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM Location WHERE LocationID IN(Select LocationID From Employee Where EmployeeID=%n)", ID);
|
|||
|
|
}
|
|||
|
|
internal static IDataReader Get(TransactionContext tc, string sCode)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM Location WHERE Code=%s", sCode);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetChilds(TransactionContext tc, int ID)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM Location WHERE ParentID=%n Order By DESCRIPTION", ID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader Get(TransactionContext tc, EnumStatus status, int payrollTypeID)
|
|||
|
|
{
|
|||
|
|
if (EnumStatus.Active == status || EnumStatus.Inactive == status)
|
|||
|
|
{
|
|||
|
|
string sql =
|
|||
|
|
SQLParser.MakeSQL(
|
|||
|
|
"SELECT * FROM Location Where Status=%n and payrollTypeid=%n Order By DESCRIPTION", status,
|
|||
|
|
payrollTypeID);
|
|||
|
|
return tc.ExecuteReader(sql);
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM Location Where payrollTypeid=%n Order By DESCRIPTION",
|
|||
|
|
payrollTypeID);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
internal static IDataReader GetParent(TransactionContext tc, EnumStatus status, int payrollTypeID)
|
|||
|
|
{
|
|||
|
|
if (EnumStatus.Active == status || EnumStatus.Inactive == status)
|
|||
|
|
{
|
|||
|
|
string sql =
|
|||
|
|
SQLParser.MakeSQL(
|
|||
|
|
"SELECT * FROM Location parentid is null Where and Status=%n Order By DESCRIPTION", status,
|
|||
|
|
payrollTypeID);
|
|||
|
|
return tc.ExecuteReader(sql);
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM Location Where parentid is null Order By DESCRIPTION",
|
|||
|
|
payrollTypeID);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetByTier(TransactionContext tc, int tier)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM Location Where Tire=%n Order by Description", tier);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#region Delete function
|
|||
|
|
|
|||
|
|
internal static void Delete(TransactionContext tc, int ID)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("DELETE FROM Location WHERE LocationID=%n", ID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
internal static IDataReader Get(TransactionContext tc, EnumStatus sts)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL("Select * From Location where Status = %n", sts);
|
|||
|
|
return tc.ExecuteReader(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetForJV(TransactionContext tc, DateTime dSalaryMonth, int payrollTypeID)
|
|||
|
|
{
|
|||
|
|
string sql =
|
|||
|
|
SQLParser.MakeSQL(
|
|||
|
|
"Select * from Location where LocationID in(Select Distinct LocationID from SalaryMonthly where PayrollTypeID=%n and SalaryMonth=%d) order by Code",
|
|||
|
|
payrollTypeID, dSalaryMonth);
|
|||
|
|
return tc.ExecuteReader(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetAllLocation(TransactionContext tc, object payrollTypeID, EnumStatus status,
|
|||
|
|
string code, string name)
|
|||
|
|
{
|
|||
|
|
string sqlClause = string.Empty;
|
|||
|
|
sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("PayrollTypeID = %n", payrollTypeID);
|
|||
|
|
if (EnumStatus.Active == status || EnumStatus.Inactive == status)
|
|||
|
|
{
|
|||
|
|
sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("status = %n", status);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
if (!string.IsNullOrWhiteSpace(code))
|
|||
|
|
{
|
|||
|
|
sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("Code = %s", code);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
if (!string.IsNullOrWhiteSpace(name))
|
|||
|
|
{
|
|||
|
|
sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("Name LIKE %s", ("%" + name + "%"));
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM Location %q ", sqlClause);
|
|||
|
|
}
|
|||
|
|
internal static IDataReader GetAllLocation(TransactionContext tc, string code, string name)
|
|||
|
|
{
|
|||
|
|
string sqlClause = string.Empty;
|
|||
|
|
if (!string.IsNullOrWhiteSpace(code))
|
|||
|
|
{
|
|||
|
|
sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("Code = %s", code);
|
|||
|
|
}
|
|||
|
|
if (!string.IsNullOrWhiteSpace(name))
|
|||
|
|
{
|
|||
|
|
sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("Name LIKE %s", ("%" + name + "%"));
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM Location %q ", sqlClause);
|
|||
|
|
}
|
|||
|
|
internal static IDataReader GetByEmployeeID(TransactionContext tc, int employeeID)
|
|||
|
|
{
|
|||
|
|
string query = SQLParser.MakeSQL(@"SELECT l.* FROM EMPLOYEE e,Location l " +
|
|||
|
|
"WHERE e.LOCATIONID = l.LOCATIONID " +
|
|||
|
|
"AND e.EMPLOYEEID = %n", employeeID);
|
|||
|
|
return tc.ExecuteReader(query);
|
|||
|
|
}
|
|||
|
|
internal static DataSet GetLocationFromCoord(TransactionContext tc, double Lat, double Long)
|
|||
|
|
{
|
|||
|
|
DataSet locations = new DataSet();
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
string sql = string.Empty;
|
|||
|
|
|
|||
|
|
//sql = SQLParser.MakeSQL(@"
|
|||
|
|
// DECLARE
|
|||
|
|
// @GEO1 GEOGRAPHY,
|
|||
|
|
// @LAT VARCHAR(10),
|
|||
|
|
// @LONG VARCHAR(10)
|
|||
|
|
|
|||
|
|
// SET @LAT=%s
|
|||
|
|
// SET @LONG=%s
|
|||
|
|
|
|||
|
|
// SET @geo1= geography::Point(@LAT, @LONG, 4326)
|
|||
|
|
|
|||
|
|
// SELECT LOCATIONID,CODE, DESCRIPTION,LEFT(CONVERT(VARCHAR,
|
|||
|
|
// (@geo1.STDistance(geography::Point(ISNULL(Latitude,0),
|
|||
|
|
// ISNULL(Longitude,0), 4326)))/1000),5)+' Km'
|
|||
|
|
// as DISTANCE from LOCATION
|
|||
|
|
// WHERE (@geo1.STDistance(geography::Point(ISNULL(Latitude,0),
|
|||
|
|
// ISNULL(Longitude,0), 4326)))/1000 < 0.2", Lat, Long);
|
|||
|
|
|
|||
|
|
sql = SQLParser.MakeSQL(@"
|
|||
|
|
DECLARE @GEO1 GEOGRAPHY,
|
|||
|
|
@LAT DECIMAL(9, 6),
|
|||
|
|
@LONG DECIMAL(9, 6),
|
|||
|
|
@DistanceThreshold FLOAT
|
|||
|
|
|
|||
|
|
SET @LAT = %s
|
|||
|
|
SET @LONG = %s
|
|||
|
|
SET @DistanceThreshold = 0.2
|
|||
|
|
|
|||
|
|
SET @geo1 = geography::Point(@LAT, @LONG, 4326)
|
|||
|
|
|
|||
|
|
DECLARE @DistanceInMeters FLOAT
|
|||
|
|
SET @DistanceInMeters = @DistanceThreshold * 1000
|
|||
|
|
|
|||
|
|
DECLARE @MinLatitude DECIMAL(9, 6)
|
|||
|
|
DECLARE @MaxLatitude DECIMAL(9, 6)
|
|||
|
|
DECLARE @MinLongitude DECIMAL(9, 6)
|
|||
|
|
DECLARE @MaxLongitude DECIMAL(9, 6)
|
|||
|
|
|
|||
|
|
SET @MinLatitude = @LAT - @DistanceThreshold / 111.2
|
|||
|
|
SET @MaxLatitude = @LAT + @DistanceThreshold / 111.2
|
|||
|
|
SET @MinLongitude = @LONG - @DistanceThreshold / (111.2 * COS(RADIANS(@LAT)))
|
|||
|
|
SET @MaxLongitude = @LONG + @DistanceThreshold / (111.2 * COS(RADIANS(@LAT)))
|
|||
|
|
|
|||
|
|
SELECT LOCATIONID, CODE, DESCRIPTION,
|
|||
|
|
LEFT(CONVERT(VARCHAR, (@geo1.STDistance(geography::Point(ISNULL(Latitude, 0), ISNULL(Longitude, 0), 4326))) / 1000), 5) + ' Km' AS DISTANCE
|
|||
|
|
FROM LOCATION With(NoLock)
|
|||
|
|
WHERE Latitude BETWEEN @MinLatitude AND @MaxLatitude
|
|||
|
|
AND Longitude BETWEEN @MinLongitude AND @MaxLongitude
|
|||
|
|
AND(@geo1.STDistance(geography::Point(ISNULL(Latitude, 0), ISNULL(Longitude, 0), 4326))) <= @DistanceInMeters", Lat, Long);
|
|||
|
|
|
|||
|
|
|
|||
|
|
locations = tc.ExecuteDataSet(sql);
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
throw new Exception(ex.Message);
|
|||
|
|
}
|
|||
|
|
return locations;
|
|||
|
|
}
|
|||
|
|
internal static DataTable GetLocationAllTier(TransactionContext tc, int locationID)
|
|||
|
|
{
|
|||
|
|
DataSet presentDays = new DataSet();
|
|||
|
|
string sql = SQLParser.MakeSQL(@"SELECT l1.DESCRIPTION AS Child,
|
|||
|
|
l2.DESCRIPTION AS MidChild,
|
|||
|
|
l3.DESCRIPTION AS Parent FROM LOCATION l1
|
|||
|
|
LEFT JOIN LOCATION l2
|
|||
|
|
ON l1.PARENTID = l2.LOCATIONID
|
|||
|
|
LEFT JOIN LOCATION l3
|
|||
|
|
ON l2.PARENTID = l3.LOCATIONID
|
|||
|
|
WHERE l1.LOCATIONID = %n", locationID);
|
|||
|
|
presentDays = tc.ExecuteDataSet(sql);
|
|||
|
|
|
|||
|
|
return presentDays.Tables[0];
|
|||
|
|
}
|
|||
|
|
internal static void SaveLocation(TransactionContext tc, int locationID, double latitude, double longitude, int userid, bool latLongApproveStatus)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("UPDATE Location SET latitude=%n, longitude=%n, LatLongSetBy=%n, LatLongSetDate=%d, LatLongApproveStatus=%b" +
|
|||
|
|
" WHERE LocationID=%n", latitude, longitude, userid, DateTime.Now, latLongApproveStatus, locationID);
|
|||
|
|
}
|
|||
|
|
internal static IDataReader GetLowerTierLocations(TransactionContext tc, int LocationID)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL("SELECT * FROM LOCATION WHERE PARENTID = %n and AttendancePoint = 1", LocationID);
|
|||
|
|
|
|||
|
|
return tc.ExecuteReader(sql);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|