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 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, int syncId)
{
try
{
using (SqlConnection sqlcon = new SqlConnection(WebCommon.WebConnection))
{
using (SqlDataAdapter da = new SqlDataAdapter())
{
da.SelectCommand = new SqlCommand("WS_FLEM_POHeader_Get", sqlcon);
da.SelectCommand.Parameters.Add(new SqlParameter("@CompanyId", companyId));
da.SelectCommand.Parameters.Add(new SqlParameter("@SyncId", syncId));
da.SelectCommand.CommandType = CommandType.StoredProcedure;
DataTable table = new DataTable();
da.Fill(table);
List <string[]> list = table.Select().Select(r => new string[] { Convert.ToInt32(r["matchID"]).ToString(), Convert.ToString(r["PO"]) }).ToList();
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 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 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 GetSyncId(int companyId, string clientMac)
{
try
{
using (SqlConnection sqlcon = new SqlConnection(WebCommon.WebConnection))
{
sqlcon.Open();
string sSQL = @"WS_SyncInit";
SqlCommand cmd = new SqlCommand(sSQL, sqlcon);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter paraSyncId;
cmd.Parameters.Add(paraSyncId = new SqlParameter("@SyncId", -1)
{
Direction = ParameterDirection.InputOutput
});
cmd.Parameters.Add(new SqlParameter("@ClientMac", clientMac));
cmd.Parameters.Add(new SqlParameter("@CompanyId", companyId));
cmd.ExecuteNonQuery();
return(Ok((int)paraSyncId.Value));
}
}
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(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 Post(int syncId, [FromBody] DeleteHistory item)
{
try
{
using (SqlConnection sqlcon = new SqlConnection(WebCommon.WebConnection))
{
sqlcon.Open();
string sSQL = @"WS_FLEM_DeleteRecord";
SqlCommand cmd = new SqlCommand(sSQL, sqlcon);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@SyncId", syncId));
cmd.Parameters.Add(new SqlParameter("@TableName", item.TableName));
cmd.Parameters.Add(new SqlParameter("@MatchId", item.MatchId));
cmd.Parameters.Add(new SqlParameter("@CompanyId", item.CompanyId));
cmd.ExecuteNonQuery();
return(Ok());
}
}
catch (Exception e)
{
SqlCommon.ReportInfo(e.Message);
return(BadRequest(e.Message));
}
}
public IHttpActionResult Get(int syncId)
{
try
{
using (SqlConnection sqlcon = new SqlConnection(WebCommon.WebConnection))
{
sqlcon.Open();
string sSQL = @"WS_FLEM_DeleteHistory_Get";
SqlCommand cmd = new SqlCommand(sSQL, sqlcon);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@SyncId", syncId));
DataTable table = new DataTable();
table.Load(cmd.ExecuteReader());
List <DeleteHistory> list = new List <DeleteHistory>();
table.Select().ToList().ForEach(r => list.Add(new DeleteHistory()
{
TableName = Convert.ToString(r["TableName"]),
CompanyId = Convert.ToInt32(r["CompanyId"]),
MatchId = Convert.ToInt32(r["MatchId"])
}));
return(Ok(list));
}
}
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 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 IHttpActionResult Get(int companyId, string clientMac, int contactId)
{
try
{
DateTime?lastUpdateTime = WebCommon.GetSyncTime(companyId, clientMac);
using (SqlConnection sqlcon = new SqlConnection(WebCommon.WebConnection))
{
sqlcon.Open();
string sSQL = @"WS_FLEM_LEMEquip_Get";
SqlCommand cmd = new SqlCommand(sSQL, sqlcon);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@contactId", contactId));
cmd.Parameters.Add(new SqlParameter("@CompanyId", companyId));
cmd.Parameters.Add(new SqlParameter("@LastSyncTime", (object)lastUpdateTime ?? DBNull.Value));
DataTable table = new DataTable();
table.Load(cmd.ExecuteReader());
List <EquipTimeEntry> list = table.Select().Select(r => new EquipTimeEntry
{
MatchId = (int)r["MatchId"],
CompanyId = companyId,
HeaderId = (int)r["HeaderMatchId"],
EqpNum = $"{r["eqi_num"]}",
ChangeOrderId = ConvertEx.ToNullable <int>(r["EstId"]),
Level1Id = ConvertEx.ToNullable <int>(r["lv1_id"]),
Level2Id = ConvertEx.ToNullable <int>(r["lv2_id"]),
Level3Id = ConvertEx.ToNullable <int>(r["lv3_id"]),
Level4Id = ConvertEx.ToNullable <int>(r["lv4_id"]),
Billable = (bool)r["Billable"],
Quantity = Convert.ToDecimal(r["Quantity"]),
BillCycle = (Equipment.EnumBillCycle)(Convert.ToChar(r["BillCycle"])),
BillAmount = ConvertEx.ToNullable <decimal>(r["dollars_total"]),
}).ToList();
return(Ok(list));
}
}
catch (Exception e)
{
SqlCommon.ReportInfo(e.Message);
return(BadRequest(e.Message));
}
}
public IHttpActionResult Get(int companyId, string clientMac, int contactId)
{
try
{
DateTime?lastUpdateTime = WebCommon.GetSyncTime(companyId, clientMac);
using (SqlConnection sqlcon = new SqlConnection(WebCommon.WebConnection))
{
sqlcon.Open();
string sSQL = @"WS_FLEM_LEMHeader_Get";
using (SqlCommand cmd = new SqlCommand(sSQL, sqlcon))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@contactId", contactId));
cmd.Parameters.Add(new SqlParameter("@CompanyId", companyId));
cmd.Parameters.Add(new SqlParameter("@LastSyncTime", (object)lastUpdateTime ?? DBNull.Value));
DataTable table = new DataTable();
table.Load(cmd.ExecuteReader());
List <LemHeader> list = table.Select().Select(r => new LemHeader
{
MatchId = r.Field <int>("MatchId"),
CompanyId = companyId,
LogDate = r.Field <DateTime>("LogDate"),
LogStatus = (EnumLogStatus)Enum.Parse(typeof(EnumLogStatus), $"{r["LogStatus"]}"),
ProjectId = r.Field <int>("pri_ID"),
LemNum = Convert.ToString(r["LemNum"]),
CreatorId = r.Field <int>("LogCreatedBy"),
Description = Convert.ToString(r["Description"]),
ApprovalComments = Convert.ToString(r["ApprovalComments"]),
}).ToList();
return(Ok(list));
}
}
}
catch (Exception e)
{
SqlCommon.ReportInfo(e.Message);
return(BadRequest(e.Message));
}
}
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 IHttpActionResult Post(int syncId, [FromBody] LemHeader log)
{
try
{
using (SqlConnection sqlcon = new SqlConnection(WebCommon.WebConnection))
{
sqlcon.Open();
string sSQL = @"WS_FLEM_LogHeader_PostPut";
using (SqlCommand cmd = new SqlCommand(sSQL, sqlcon))
{
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter paraMatchId;
SqlParameter paraLemNum;
cmd.Parameters.Add(new SqlParameter("@SyncId", syncId));
cmd.Parameters.Add(new SqlParameter("@ContactId", log.CreatorId));
cmd.Parameters.Add(new SqlParameter("@CompanyId", log.CompanyId));
cmd.Parameters.Add(paraMatchId = new SqlParameter("@MatchId", log.MatchId)
{
Direction = ParameterDirection.InputOutput
});
cmd.Parameters.Add(new SqlParameter("@LogDate", log.LogDate));
cmd.Parameters.Add(new SqlParameter("@LogStatus", Enum.GetName(typeof(EnumLogStatus), log.LogStatus)));
cmd.Parameters.Add(new SqlParameter("@ProjectId", log.ProjectId));
cmd.Parameters.Add(paraLemNum = new SqlParameter("@LemNum", log.LemNum)
{
Direction = ParameterDirection.InputOutput, Size = 20
});
cmd.Parameters.Add(new SqlParameter("@BillAmount", log.BillAmount));
cmd.Parameters.Add(new SqlParameter("@Description", ConvertEx.DbNullable(log.Description)));
cmd.Parameters.Add(new SqlParameter("@EmailData", SqlDbType.VarBinary, log.EmailData?.Length ?? 0)).Value = ConvertEx.DbNullable(log.EmailData);
cmd.ExecuteNonQuery();
string[] returnData = new string[] { paraMatchId.Value.ToString(), paraLemNum.Value.ToString() };
return(Ok(returnData));
}
}
}
catch (Exception e)
{
SqlCommon.ReportInfo(e.Message);
return(BadRequest(e.Message));
}
}
public IHttpActionResult Get(int contactId, int companyId)
{
try
{
var nullableConvert = new Func <object, int?>((obj) =>
{
int v = Convert.ToInt32(obj);
return(v == -1 ? null : (int?)v);
});
using (SqlConnection sqlcon = new SqlConnection(WebCommon.WebConnection))
{
sqlcon.Open();
string sSQL = @"WS_FLEM_ProjBudget_Get ";
using (SqlCommand cmd = new SqlCommand(sSQL, sqlcon))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@contactId", contactId));
cmd.Parameters.Add(new SqlParameter("@CompanyId", companyId));
DataTable table = new DataTable();
table.Load(cmd.ExecuteReader());
List <ProjectLevelCode> list = table.Select().Select(r => new ProjectLevelCode
{
CompanyId = companyId,
ProjectId = Convert.ToInt32(r["pri_id"]),
Level1Id = nullableConvert(r["lv1id"]),
Level2Id = nullableConvert(r["lv2id"]),
Level3Id = nullableConvert(r["lv3id"]),
Level4Id = nullableConvert(r["lv4id"]),
}).ToList();
return(Ok(list));
}
}
}
catch (Exception e)
{
SqlCommon.ReportInfo(e.Message);
return(BadRequest(e.Message));
}
}
public IHttpActionResult Post(int syncId, [FromBody] EquipTimeEntry entry)
{
try
{
using (SqlConnection sqlcon = new SqlConnection(WebCommon.WebConnection))
{
sqlcon.Open();
string sSQL = @"WS_FLEM_EquipTimeEntry_PostPut";
SqlCommand cmd = new SqlCommand(sSQL, sqlcon);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter paraMatchId;
cmd.Parameters.Add(new SqlParameter("@SyncId", syncId));
cmd.Parameters.Add(new SqlParameter("@CompanyId", entry.CompanyId));
cmd.Parameters.Add(paraMatchId = new SqlParameter("@MatchId", entry.MatchId)
{
Direction = ParameterDirection.InputOutput
});
cmd.Parameters.Add(new SqlParameter("@HeaderMatchId", entry.HeaderId));
cmd.Parameters.Add(new SqlParameter("@EqpNum", entry.EqpNum));
cmd.Parameters.Add(new SqlParameter("@Level1Id", (object)entry.Level1Id ?? DBNull.Value));
cmd.Parameters.Add(new SqlParameter("@Level2Id", (object)entry.Level2Id ?? DBNull.Value));
cmd.Parameters.Add(new SqlParameter("@Level3Id", (object)entry.Level3Id ?? DBNull.Value));
cmd.Parameters.Add(new SqlParameter("@Level4Id", (object)entry.Level4Id ?? DBNull.Value));
cmd.Parameters.Add(new SqlParameter("@Billable", entry.Billable));
cmd.Parameters.Add(new SqlParameter("@Quantity", entry.Quantity));
cmd.Parameters.Add(new SqlParameter("@BillCycle", (char)entry.BillCycle));
cmd.Parameters.Add(new SqlParameter("@BillAmount", (object)entry.BillAmount ?? DBNull.Value));
cmd.Parameters.Add(new SqlParameter("@EstId", (object)entry.ChangeOrderId ?? DBNull.Value));
cmd.ExecuteNonQuery();
return(Ok(paraMatchId.Value));
}
}
catch (Exception e)
{
SqlCommon.ReportInfo(e.Message);
return(BadRequest(e.Message));
}
}
public static void SqlUpdateEmail(int id, byte[] email)
{
try
{
using (SqlConnection con = new SqlConnection(MobileCommon.MobileDB))
{
con.Open();
string sql = $"Update LemHeader set EmailData=@Email where id={id}";
using (SqlCommand cmd = new SqlCommand(sql, con))
{
SqlParameter param = cmd.Parameters.Add("@Email", SqlDbType.VarBinary);
param.Value = email;
cmd.ExecuteNonQuery();
}
}
}
catch (Exception e)
{
SqlCommon.ReportInfo("Sql Error: " + e);
}
}
public IHttpActionResult Post(int syncId, [FromBody] LemAP ap)
{
try
{
using (SqlConnection sqlcon = new SqlConnection(WebCommon.WebConnection))
{
sqlcon.Open();
string sSQL = @"WS_FLEM_APDet_PostPut ";
SqlCommand cmd = new SqlCommand(sSQL, sqlcon);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@SyncId", syncId));
cmd.Parameters.Add(new SqlParameter("@CompanyId", ap.CompanyId));
cmd.Parameters.Add(new SqlParameter("@HeaderMatchId", ap.HeaderId));
cmd.Parameters.Add(new SqlParameter("@ap_gl_alloc_id", null));
cmd.Parameters.Add(new SqlParameter("@Amount", null));
cmd.Parameters.Add(new SqlParameter("@MarkupPct", null));
cmd.Parameters.Add(new SqlParameter("@MarkupAmt", null));
cmd.Parameters.Add(new SqlParameter("@BillAmt", null));
foreach (var detail in ap.DetailList)
{
cmd.Parameters["@ap_gl_alloc_id"].Value = detail.MatchId;
cmd.Parameters["@Amount"].Value = detail.Amount;
cmd.Parameters["@MarkupPct"].Value = detail.MarkupPercent;
cmd.Parameters["@MarkupAmt"].Value = detail.MarkupAmount;
cmd.Parameters["@BillAmt"].Value = detail.BillAmount;
cmd.ExecuteNonQuery();
}
return(Ok());
}
}
catch (Exception e)
{
SqlCommon.ReportInfo(e.Message);
return(BadRequest(e.Message));
}
}
public IHttpActionResult Get(int contactId, int companyId)
{
try
{
using (SqlConnection sqlcon = new SqlConnection(WebCommon.WebConnection))
{
sqlcon.Open();
string sSQL = @"WS_FLEM_Defaults_Get";
using (SqlCommand cmd = new SqlCommand(sSQL, sqlcon))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@contactId", contactId));
cmd.Parameters.Add(new SqlParameter("@CompanyId", companyId));
DataTable table = new DataTable();
table.Load(cmd.ExecuteReader());
List <DefaultEarning> list = table.Select().Select(r => new DefaultEarning
{
CompanyId = companyId,
ProjectId = Convert.ToInt32(r["pri_id"]),
Level1Id = ConvertEx.ToNullable <int>(r["lv1id"]),
Level2Id = ConvertEx.ToNullable <int>(r["lv2id"]),
Level3Id = ConvertEx.ToNullable <int>(r["lv3id"]),
Level4Id = ConvertEx.ToNullable <int>(r["lv4id"]),
EarningType = ConvertEx.CharToEnum <EnumEarningType>(r["type"])
}).ToList();
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()
{
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(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(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));
}
}