public IHttpActionResult Get(string codeVer, string dbVer)
{
try
{
string sql = @"select MobileCodeVersion, MobileDbVersion, isnull(NumDayFLEMSheetAvail,0) KeepDays from system_ctrl";
DataTable table = SqlCommon.ExecuteDataAdapter(sql, WebCommon.WebConnection);
SystemInfo info = table.Select().Select(r => new SystemInfo
{
CodeVersion = Convert.ToString(r["MobileCodeVersion"]),
DataBaseVersion = Convert.ToString(r["MobileDbVersion"]),
KeepDays = ConvertEx.ToNullable <int>(r["KeepDays"]),
PatchScript = new List <string>()
}).First();
if (info.CodeVersion == codeVer && info.DataBaseVersion != dbVer)
{
string fileName = HttpContext.Current.Server.MapPath("~/App_Data/UpdateScript.sql");
FileInfo fileInfo = new FileInfo(fileName);
string script = fileInfo.OpenText().ReadToEnd();
info.PatchScript.Add(script);
}
return(Ok(info));
}
catch (Exception e)
{
SqlCommon.ReportInfo(e.Message);
return(BadRequest(e.Message));
}
}
public IHttpActionResult Get(int companyId)
{
try
{
string sql = $"select cer_id, pri_id, eqi_Class, isnull(sch_enabled, 0) Schedulable, cast(case UseEquipOverride when 'T' then 1 else 0 end as bit) as UseOverride, " +
$"rate, isnull(TimeCode, 'U') BillCycle from costing_equipment_class m join unit_time_measurement u on m.UOM=u.Id";
DataTable table = SqlCommon.ExecuteDataAdapter(sql, WebCommon.GetTRConnectionAsync(companyId));
List <ProjectEquipmentClass> list = new List <ProjectEquipmentClass>();
table.Select().ToList().ForEach(r => list.Add(new ProjectEquipmentClass
{
MatchId = r.Field <int>("cer_id"),
CompanyId = companyId,
ProjectId = r.Field <int>("pri_id"),
ClassCode = r.Field <string>("eqi_Class"),
Schedulable = r.Field <bool>("Schedulable"),
UseOverride = r.Field <bool>("UseOverride"),
BillRate = r.Field <decimal?>("rate"),
BillCycle = (Equipment.EnumBillCycle)(Convert.ToChar(r["BillCycle"])),
}));
return(Ok(list));
}
catch (Exception e)
{
SqlCommon.ReportInfo(e.Message);
return(BadRequest(e.Message));
}
}
public IHttpActionResult Get(int companyId)
{
try
{
string sql = $"select isnull(u.TimeCode, 'U') BillCycle, m.Bill_Out_Rate, Rate_Type, m.IS_Default from FA_RatSchedule_Setup m " +
$"join unit_time_measurement u on m.uom_id = u.Id";
DataTable table = SqlCommon.ExecuteDataAdapter(sql, WebCommon.GetTRConnectionAsync(companyId));
List <EquipmentDefaultBillRate> list = new List <EquipmentDefaultBillRate>();
table.Select().ToList().ForEach(r =>
{
list.Add(new EquipmentDefaultBillRate
{
CompanyId = companyId,
GroupType = (EnumGroupType)(Convert.ToChar(r["Rate_Type"])),
BillCycle = (EnumBillCycle)(Convert.ToChar(r["BillCycle"])),
BillRate = (decimal)r["Bill_Out_Rate"],
IsDefault = (bool)r["IS_Default"]
});
});
return(Ok(list));
}
catch (Exception e)
{
SqlCommon.ReportInfo(e.Message);
return(BadRequest(e.Message));
}
}
public IHttpActionResult Get(int companyId)
{
try
{
string sql = "select ol_id, pri_id, ol_code, ol_desc, ol_ot, ol_dt, ol_week_ot, ol_week_dt from ot_limit";
DataTable table = SqlCommon.ExecuteDataAdapter(sql, WebCommon.GetTRConnectionAsync(companyId));
List <OvertimeLimit> list = new List <OvertimeLimit>();
table.Select().ToList().ForEach(r => list.Add(new OvertimeLimit
{
MatchId = r.Field <int>("ol_id"),
CompanyId = companyId,
ProjectId = r.Field <int>("pri_id"),
Code = r.Field <string>("ol_code"),
Desc = r.Field <string>("ol_desc"),
DailyLimit = r.Field <decimal?>("ol_ot"),
DailyDoubleLimit = r.Field <decimal?>("ol_dt"),
WeeklyLimit = r.Field <decimal?>("ol_week_ot"),
WeeklyDoubleLimit = r.Field <decimal?>("ol_week_dt"),
}));
return(Ok(list));
}
catch (Exception e)
{
SqlCommon.ReportInfo(e.Message);
return(BadRequest(e.Message));
}
}
public IHttpActionResult Get(int companyId)
{
try
{
string sql = $"select cwce_id, emp_no, e.cwc_id, start_date, end_date, c.pri_id, c.WC_Code from costing_work_class_emp e join costing_work_class c on e.cwc_id = c.cwc_id";
DataTable table = SqlCommon.ExecuteDataAdapter(sql, WebCommon.GetTRConnectionAsync(companyId));
List <LabourTemplate> list = new List <LabourTemplate>();
table.Select().ToList().ForEach(r => list.Add(new LabourTemplate
{
MatchId = r.Field <int>("cwce_id"),
CompanyId = companyId,
ProjectId = r.Field <int>("pri_id"),
EmpNum = r.Field <int>("emp_no"),
WorkClassCode = r.Field <string>("WC_Code"),
StartDate = r.Field <DateTime?>("start_date"),
EndDate = r.Field <DateTime?>("end_date"),
}));
return(Ok(list));
}
catch (Exception e)
{
SqlCommon.ReportInfo(e.Message);
return(BadRequest(e.Message));
}
}
public IHttpActionResult Get(int companyId)
{
try
{
string sql = "select eqi_num, eqi_code, eqi_desc1, eqi_class, eqi_cat, isnull(own_type,'U') own_type from Equip_ID";
DataTable table = SqlCommon.ExecuteDataAdapter(sql, WebCommon.GetTRConnectionAsync(companyId));
List <Equipment> list = new List <Equipment>();
table.Select().ToList().ForEach(r => list.Add(new Equipment
{
EqpNum = (string)r["eqi_num"],
CompanyId = companyId,
AssetCode = (string)r["eqi_code"],
Desc = (string)r["eqi_desc1"],
ClassCode = (string)r["eqi_class"],
CategoryCode = (string)r["eqi_cat"],
OwnerType = ConvertEx.CharToEnum <EnumOwnerType>(r["own_type"])
}));
return(Ok(list));
}
catch (Exception e)
{
SqlCommon.ReportInfo(e.Message);
return(BadRequest(e.Message));
}
}
public IHttpActionResult Get(int companyId)
{
try
{
string sql = $"select w.Id, w.Description, w.ValueType, isnull(w.BillingRateType, 'Unknown')[BillingRateType], " +
$"w.InclInHoursThisWeek, w.Time_Code_ID_OT, w.Time_Code_ID_DT, e.comp_code[Component], ReportTypeColumn from WS_EMP_Time_Code w " +
$"join earn_code e on e.eg_code = w.eg_code and e.ec_code = w.ec_code";
DataTable table = SqlCommon.ExecuteDataAdapter(sql, WebCommon.GetTRConnectionAsync(companyId));
List <TimeCode> list = new List <TimeCode>();
table.Select().ToList().ForEach(r => list.Add(new TimeCode
{
MatchId = (int)r["Id"],
CompanyId = companyId,
Desc = $"{r["Description"]}",
ValueType = ConvertEx.StringToEnum <EnumValueType>(r["ValueType"]),
BillingType = ConvertEx.StringToEnum <EnumBillingRateType>(r["BillingRateType"]),
OvertimeId = r.Field <int?>("Time_Code_ID_OT"),
DoubleTimeId = r.Field <int?>("Time_Code_ID_DT"),
IncludedInWeekCalc = (bool)r["InclInHoursThisWeek"],
Component = ConvertEx.CharToEnum <EnumComponentType>(r["Component"]),
ReportTypeColumn = Convert.ToString(r["ReportTypeColumn"])
}));
return(Ok(list));
}
catch (Exception e)
{
SqlCommon.ReportInfo(e.Message);
return(BadRequest(e.Message));
}
}
public IHttpActionResult Get(int companyId)
{
try
{
string sql = $"select emp_no, emp_first_name, emp_last_name, wc_code, ol_code from Employee";
DataTable table = SqlCommon.ExecuteDataAdapter(sql, WebCommon.GetTRConnectionAsync(companyId));
List <Employee> list = new List <Employee>();
table.Select().ToList().ForEach(r => list.Add(new Employee
{
EmpNum = Convert.ToInt32(r["emp_no"]),
CompanyId = companyId,
FirstName = Convert.ToString(r["emp_first_name"]),
LastName = Convert.ToString(r["emp_last_name"]),
WorkClassCode = Convert.ToString(r["wc_code"]),
OvertimeCode = Convert.ToString(r["ol_code"]),
}));
return(Ok(list));
}
catch (Exception e)
{
SqlCommon.ReportInfo(e.Message);
return(BadRequest(e.Message));
}
}
public IHttpActionResult Get(int companyId)
{
try
{
string sql = "select eqi_num, emp_no, ea_date, ea_release_date, eg_code, ec_code from equip_assign";
DataTable table = SqlCommon.ExecuteDataAdapter(sql, WebCommon.GetTRConnectionAsync(companyId));
List <EquipmentAssignment> list = new List <EquipmentAssignment>();
table.Select().ToList().ForEach(r => list.Add(new EquipmentAssignment
{
CompanyId = companyId,
EqpNum = $"{r["eqi_num"]}",
EmpNum = (int)r["emp_no"],
AssignedDate = r.Field <DateTime?>("ea_date"),
ReleasedDate = r.Field <DateTime?>("ea_release_date"),
EarnGroup = Convert.ToString(r["eg_code"]),
EarnCode = Convert.ToString(r["ec_code"]),
}));
return(Ok(list));
}
catch (Exception e)
{
SqlCommon.ReportInfo(e.Message);
return(BadRequest(e.Message));
}
}
public IHttpActionResult Get(int companyId)
{
try
{
string sql = $"select Id, wc_code, wc_desc, Regular, OverTime, DoubleTime, Travel from work_class";
DataTable table = SqlCommon.ExecuteDataAdapter(sql, WebCommon.GetTRConnectionAsync(companyId));
List <WorkClass> list = new List <WorkClass>();
table.Select().ToList().ForEach(r => list.Add(new WorkClass
{
MatchId = (int)r["Id"],
CompanyId = companyId,
Code = $"{r["wc_code"]}",
Desc = $"{r["wc_desc"]}",
RegularBillRate = r.Field <decimal?>("Regular"),
OvertimeBillRate = r.Field <decimal?>("OverTime"),
DoubleTimeBillRate = r.Field <decimal?>("DoubleTime"),
TravelBillRate = r.Field <decimal?>("Travel")
}));
return(Ok(list));
}
catch (Exception e)
{
SqlCommon.ReportInfo(e.Message);
return(BadRequest(e.Message));
}
}
public IHttpActionResult Get(int companyId)
{
try
{
string sql = "select cece_id, c.pri_id, eqi_num, c.eqi_class, start_date, end_date from costing_equipment_class_equip t " +
"join Equip_ID e on t.eqi_id = e.eqi_id " +
"join costing_equipment_class c on t.cer_id = c.cer_id";
DataTable table = SqlCommon.ExecuteDataAdapter(sql, WebCommon.GetTRConnectionAsync(companyId));
List <EquipmentTemplate> list = new List <EquipmentTemplate>();
table.Select().ToList().ForEach(r => list.Add(new EquipmentTemplate
{
MatchId = r.Field <int>("cece_id"),
CompanyId = companyId,
ProjectId = r.Field <int>("pri_id"),
EqpNum = r.Field <string>("eqi_num"),
EquipClassCode = r.Field <string>("eqi_class"),
StartDate = r.Field <DateTime?>("start_date"),
EndDate = r.Field <DateTime?>("end_date"),
}));
return(Ok(list));
}
catch (Exception e)
{
SqlCommon.ReportInfo(e.Message);
return(BadRequest(e.Message));
}
}
public IHttpActionResult Get(int companyId)
{
try
{
string sql = $"select lv4ID, lv3ID, lv4_code, lv4_desc from Level4_Codes";
DataTable table = SqlCommon.ExecuteDataAdapter(sql, WebCommon.GetTRConnectionAsync(companyId));
List <LevelFourCode> list = new List <LevelFourCode>();
table.Select().ToList().ForEach(r => list.Add(new LevelFourCode
{
MatchId = (int)r["lv4ID"],
CompanyId = companyId,
Level3Id = (int)r["lv3ID"],
Code = $"{r["lv4_code"]}",
Desc = $"{r["lv4_desc"]}",
}));
return(Ok(list));
}
catch (Exception e)
{
SqlCommon.ReportInfo(e.Message);
return(BadRequest(e.Message));
}
}
public IHttpActionResult Get()
{
try
{
string sql = $"select * from COMPANIES";
DataTable table = SqlCommon.ExecuteDataAdapter(sql, WebCommon.WebConnection);
List <Company> list = new List <Company>();
table.Select().ToList().ForEach(r => list.Add(new Company
{
MatchId = (int)r["AutoID"],
CompanyName = (string)r["Company_Name"],
ShortName = ConvertEx.StrOrEmpty(r["companyShortName"]),
Active = (bool)r["Active"],
}));
foreach (var com in list)
{
sql = @"select * from Company";
table = SqlCommon.ExecuteDataAdapter(sql, WebCommon.GetTRConnectionAsync(com.MatchId));
com.CompanyAddress1 = Convert.ToString(table.Rows[0]["ADD1"]);
com.CompanyAddress2 = Convert.ToString(table.Rows[0]["ADD2"]);
com.CompanyAddress3 = Convert.ToString(table.Rows[0]["ADD3"]);
com.CompanyCity = Convert.ToString(table.Rows[0]["City"]);
com.CompanyState = Convert.ToString(table.Rows[0]["State"]);
com.CompanyZip = Convert.ToString(table.Rows[0]["Zip"]);
com.CompanyPhone = Convert.ToString(table.Rows[0]["Phone"]);
com.CompanyFax = Convert.ToString(table.Rows[0]["Fax"]);
com.CompanyEmail = Convert.ToString(table.Rows[0]["Email"]);
com.CompanyWeb = Convert.ToString(table.Rows[0]["WebPage"]);
sql = "select isnull(case when rtrim(week_start) = '' then null else week_start end, 'U') as week_start from hr_cntl";
EnumDayInWeek day = ConvertEx.CharToEnum <EnumDayInWeek>(SqlCommon.ExecuteScalar(sql, WebCommon.GetTRConnectionAsync(com.MatchId)));
com.WeekStart = ConvertEx.StringToEnum <DayOfWeek>(Enum.GetName(typeof(EnumDayInWeek), day));
sql = @"select use_cat_class from fa_setup";
table = SqlCommon.ExecuteDataAdapter(sql, WebCommon.GetTRConnectionAsync(com.MatchId));
com.EquipRateGroupType = (EnumGroupType)Convert.ToChar(table.Rows[0]["use_cat_class"]);
sql = @"select lv1_active_gc, lv1_gencon_desc, lv2_active_gc, lv2_gencon_desc, lv3_active_gc, lv3_gencon_desc, lv4_active_gc, lv4_gencon_desc from proj_cntl";
table = SqlCommon.ExecuteDataAdapter(sql, WebCommon.GetTRConnectionAsync(com.MatchId));
com.MaxLevelCode = (table.Rows[0]["lv4_active_gc"] != DBNull.Value ? 4 :
(table.Rows[0]["lv3_active_gc"] != DBNull.Value ? 3 :
(table.Rows[0]["lv2_active_gc"] != DBNull.Value ? 2 :
(table.Rows[0]["lv1_active_gc"] != DBNull.Value ? 1 : 0))));
com.Level1CodeDesc = Convert.ToString(table.Rows[0]["lv1_gencon_desc"]);
com.Level2CodeDesc = Convert.ToString(table.Rows[0]["lv2_gencon_desc"]);
com.Level3CodeDesc = Convert.ToString(table.Rows[0]["lv3_gencon_desc"]);
com.Level4CodeDesc = Convert.ToString(table.Rows[0]["lv4_gencon_desc"]);
}
return(Ok(list));
}
catch (Exception e)
{
SqlCommon.ReportInfo(e.Message);
return(BadRequest(e.Message));
}
}
public static WebToken GetByGuid(Guid guid)
{
string sql = $"select * from MobileWebToken where AuthToken='{guid}' and ExpiresOn>getdate()";
DataTable table = SqlCommon.ExecuteDataAdapter(sql, WebCommon.WebConnection);
return(table.Select().Select(r => new WebToken
{
UserId = (int)r["UserId"],
AuthToken = (Guid)r["AuthToken"],
IssuedOn = (DateTime)r["IssuedOn"],
ExpiresOn = (DateTime)r["ExpiresOn"]
}).SingleOrDefault());
}
public IHttpActionResult Get(int companyId)
{
try
{
string sql = $"select pri_id, pri_name, pri_code, ph.customer_id, pri_site1, pri_start_date, pri_est_completion_date, billable, Customer_File_Num, " +
$"site_address, site_city, site_state, site_zip, c.customer_Code, c.Name, c.Bill_Address_1, c.bill_address_2, c.bill_address_3, c.bill_city, " +
$"c.bill_state, c.bill_zip, Pri_desc from dbo.proj_header ph join customers c on c.customer_id = ph.customer_id where ph.pri_type = 'pgc' and ph.pri_status='A'";
DataTable table = SqlCommon.ExecuteDataAdapter(sql, WebCommon.GetTRConnectionAsync(companyId));
List <Project> list = new List <Project>();
table.Select().ToList().ForEach(r => list.Add(new Project
{
MatchId = r.Field <int>("pri_id"),
CompanyId = companyId,
Name = r.Field <string>("pri_name"),
Code = r.Field <int>("pri_code"),
CustomerId = r.Field <int>("customer_id"),
CustomerCode = r.Field <string>("customer_Code"),
CustomerName = r.Field <string>("Name"),
CustomerAddress = r.Field <string>("Bill_Address_1"),
SiteLocation = r.Field <string>("pri_site1"),
StartDate = r.Field <DateTime?>("pri_start_date"),
EstCompletionDate = r.Field <DateTime?>("pri_est_completion_date"),
Billable = r.Field <bool>("billable"),
POReference = Convert.ToString(r["Customer_File_Num"]),
SiteAddress = Convert.ToString(r["site_address"]),
SiteCity = Convert.ToString(r["site_city"]),
SiteState = Convert.ToString(r["site_state"]),
SiteZip = Convert.ToString(r["site_zip"]),
CustomerAddress2 = Convert.ToString(r["bill_address_2"]),
CustomerAddress3 = Convert.ToString(r["bill_address_3"]),
CustomerCity = Convert.ToString(r["bill_city"]),
CustomerState = Convert.ToString(r["bill_state"]),
CustomerZip = Convert.ToString(r["bill_zip"]),
ProjectExtendedDescription = Convert.ToString(r["Pri_desc"])
}));
return(Ok(list));
}
catch (Exception e)
{
SqlCommon.ReportInfo(e.Message);
return(BadRequest(e.Message));
}
}
public static string GetTRConnectionAsync(int companyId)
{
lock (myLock)
{
if (_trList == null)
{
string sql = @"select AutoID, Company_Name, CompanyServerName, TreasuryDBName from COMPANIES";
DataTable table = SqlCommon.ExecuteDataAdapter(sql, WebConnection);
_trList = new Dictionary <int, string>();
table.Select().ToList().ForEach(r =>
{
string tr = $"Data Source={r["CompanyServerName"]};Initial Catalog={r["TreasuryDBName"]};Persist Security Info=True;User ID={userName};Password={password}";
_trList.Add((int)r["AutoID"], tr);
});
}
}
return(_trList[companyId]);
}
public IHttpActionResult Get(int companyId)
{
try
{
DataTable table = SqlCommon.ExecuteDataAdapter("WS_FLEM_SecuritySync", WebCommon.WebConnection);
List <MobileData.Security> list = new List <MobileData.Security>();
table.Select().Where(x => (int)x["CompanyId"] == companyId).ToList().ForEach(r => list.Add(new MobileData.Security
{
Function_ID = (int)r["Function_ID"],
CompanyId = (int)r["CompanyId"],
Department = (string)r["Department"],
}));
return(Ok(list));
}
catch (Exception e)
{
SqlCommon.ReportInfo(e.Message);
return(BadRequest(e.Message));
}
}
public IHttpActionResult Get()
{
try
{
DataTable table = SqlCommon.ExecuteDataAdapter("WS_FLEM_ContextGroup_Get", WebCommon.WebConnection);
List <ContextGroup> list = new List <ContextGroup>();
table.Select().ToList().ForEach(r => list.Add(new ContextGroup
{
ID = (int)r["Id"],
Name = (string)r["Name"],
IsSystem = (bool)r["IsSystem"]
}));
return(Ok(list));
}
catch (Exception e)
{
SqlCommon.ReportInfo(e.Message);
return(BadRequest(e.Message));
}
}
public IHttpActionResult Get()
{
try
{
DataTable table = SqlCommon.ExecuteDataAdapter("WS_FLEM_ContextUsage_Get", WebCommon.WebConnection);
List <ContextUsage> list = new List <ContextUsage>();
table.Select().ToList().ForEach(r => list.Add(new ContextUsage
{
ID = (int)r["Id"],
ContextItemID = (int)r["ContextItemID"],
ContextGroupID = (int)r["ContextGroupID"],
}));
return(Ok(list));
}
catch (Exception e)
{
SqlCommon.ReportInfo(e.Message);
return(BadRequest(e.Message));
}
}
public IHttpActionResult Get(int companyId)
{
try
{
string sql = $"select supplier, name from Supplier_master";
DataTable table = SqlCommon.ExecuteDataAdapter(sql, WebCommon.GetTRConnectionAsync(companyId));
List <Supplier> list = new List <Supplier>();
table.Select().ToList().ForEach(r => list.Add(new Supplier
{
CompanyId = companyId,
SupplierCode = (string)r["supplier"],
SupplierName = (string)r["name"]
}));
return(Ok(list));
}
catch (Exception e)
{
SqlCommon.ReportInfo(e.Message);
return(BadRequest(e.Message));
}
}
public IHttpActionResult Get(int companyId)
{
try
{
string sql = "select fac_code, fac_desc from fa_cat";
DataTable table = SqlCommon.ExecuteDataAdapter(sql, WebCommon.GetTRConnectionAsync(companyId));
List <EquipmentCategory> list = new List <EquipmentCategory>();
table.Select().ToList().ForEach(r => list.Add(new EquipmentCategory
{
CompanyId = companyId,
Code = r.Field <string>("fac_code"),
Desc = r.Field <string>("fac_desc"),
}));
return(Ok(list));
}
catch (Exception e)
{
SqlCommon.ReportInfo(e.Message);
return(BadRequest(e.Message));
}
}
public IHttpActionResult Get()
{
try
{
DataTable table = SqlCommon.ExecuteDataAdapter("WS_FLEM_SecurityFunctionSync", WebCommon.WebConnection);
List <SecurityFunction> list = new List <SecurityFunction>();
table.Select().ToList().ForEach(r => list.Add(new SecurityFunction
{
Id = (int)r["Id"],
ParentId = ConvertEx.ToNullable <int>(r["ParentId"]),
Description = (string)r["Description"],
FieldServices = (bool)r["FieldServices"]
}));
return(Ok(list));
}
catch (Exception e)
{
SqlCommon.ReportInfo(e.Message);
return(BadRequest(e.Message));
}
}
public IHttpActionResult Get(int companyId)
{
try
{
string sql = @"select cwc_id, pri_id, WC_Code, Standard, OverTime, Doubletime, TravelTime, isnull(sch_enabled, 'false') Schedulable, CeilingCost, sch_bud_hrs_reg, sch_bud_hrs_tt from costing_work_class";
DataTable table = SqlCommon.ExecuteDataAdapter(sql, WebCommon.GetTRConnectionAsync(companyId));
Func <double?, decimal?> ZeroToNull = new Func <double?, decimal?>((value) =>
{
return((value == null || value.Value == 0.0) ? null : (decimal?)value);
});
List <ProjectWorkClass> list = new List <ProjectWorkClass>();
table.Select().ToList().ForEach(r => list.Add(new ProjectWorkClass
{
MatchId = r.Field <int>("cwc_id"),
CompanyId = companyId,
ProjectId = r.Field <int>("pri_id"),
WorkClassCode = r.Field <string>("WC_Code"),
Schedulable = r.Field <bool>("Schedulable"),
RegularBillRate = ZeroToNull(r.Field <double?>("Standard")),
OvertimeBillRate = ZeroToNull(r.Field <double?>("OverTime")),
DoubleTimeBillRate = ZeroToNull(r.Field <double?>("Doubletime")),
TravelBillRate = ZeroToNull(r.Field <double?>("TravelTime")),
CeilingCost = r.Field <decimal?>("CeilingCost"),
RegularHours = r.Field <decimal?>("sch_bud_hrs_reg"),
TravelHours = r.Field <decimal?>("sch_bud_hrs_tt"),
}));
return(Ok(list));
}
catch (Exception e)
{
SqlCommon.ReportInfo(e.Message);
return(BadRequest(e.Message));
}
}
public IHttpActionResult Get()
{
try
{
string sql = @"select AutoID from COMPANIES";
DataTable table = SqlCommon.ExecuteDataAdapter(sql, WebCommon.WebConnection);
List <int> companyIdList = new List <int>();
table.Select().ToList().ForEach(r => companyIdList.Add((int)r["AutoID"]));
sql = @"select ID, Windows_Login from Contact where Windows_Login is not null and IsFieldManager='T' and Type='User'"; //todo: use IsFieldManager?
table = SqlCommon.ExecuteDataAdapter(sql, WebCommon.WebConnection);
List <LoginUser> list = new List <LoginUser>();
table.Select().ToList().ForEach(r =>
{
int userId = r.Field <int>("ID");
string loginName = r.Field <string>("Windows_Login");
List <UserAccess> asscessList = new List <UserAccess>();
List <ProjectAccess> projectList = new List <ProjectAccess>();
foreach (int companyId in companyIdList)
{
sql = $"select name, department from mluser where contactID={userId}";
DataTable userTable = SqlCommon.ExecuteDataAdapter(sql, WebCommon.GetTRConnectionAsync(companyId));
if (userTable.Rows.Count > 0)
{
var mlUserName = (string)userTable.Rows[0]["name"];
asscessList.Add(new UserAccess {
CompanyId = companyId, UserName = mlUserName, Department = (string)userTable.Rows[0]["department"]
});
//todo: pri_type='PGC' ??
sql = $"PC_ProjectSearch @username='******', @pri_type='PGC', @include_co='B', @pri_id=null, @pri_id2=null, @pri_status=null, @non_closed=null, " +
$"@customer_id=null, @customer_id2=null, @pri_division=null, @proj_manager=null, @engineer=null, @architect=null, @field_forman=null, @proj_accountant=null, " +
$"@salesperson=null, @gen_contractor=null, @estimator=null, @customer_contact=null, @prc_code=null, @prcl_code=null, " +
$"@pri_whs=null, @customer_po=null, @est_completion=null, @geographic_area_id=null, @municipalities_id=null, " +
$"@communities_id=null, @land_use_id=null, @external_ref=null, @co_pri_id=null, @co_pri_id2=null, @proj_customer_type_id=null, " +
$"@field_manager=null, @proj_coordinator=null, @LandSubType=null, @RawLandHolding_Pri_ID=null, @pri_profit=null";
SqlCommon.ExecuteNonQuery(sql, WebCommon.GetTRConnectionAsync(companyId));
sql = $"select pri_id from working_proj_selected where username='******'";
DataTable projectTable = SqlCommon.ExecuteDataAdapter(sql, WebCommon.GetTRConnectionAsync(companyId));
projectTable.Select().ToList().ForEach(p => projectList.Add(new ProjectAccess {
CompanyId = companyId, ProjectId = (int)p["pri_id"]
}));
}
}
list.Add(new LoginUser
{
MatchId = userId,
LoginName = loginName,
AccessList = asscessList,
ProjectList = projectList,
});
});
return(Ok(list));
}
catch (Exception e)
{
SqlCommon.ReportInfo(e.Message);
return(BadRequest(e.Message));
}
}
public static DataTable ExecuteDataAdapter(string sql)
{
return(SqlCommon.ExecuteDataAdapter(sql, MobileDB));
}