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)); } }