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, 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 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 UserVo selectUserVo(UserVo UserVo)
{
UserVo userVo = new UserVo();
using (SqlCommon sqlCommon = new SqlCommon())
{
string procedureName = "UP_USER_SELECT";
List <SqlParameter> sqlParameters = new List <SqlParameter>();
sqlParameters.Add(new SqlParameter("@USER_ID", UserVo.user.userId));
sqlParameters.Add(new SqlParameter("@PWD", UserVo.user.pwd));
DataSet ds = sqlCommon.ExecuteProcedure(procedureName, sqlParameters);
if ((ds != null) &&
(ds.Tables != null) && (ds.Tables[0] != null) &&
(ds.Tables[0].Rows.Count > 0) && (ds.Tables[0].Rows[0] != null))
{
userVo.user.userId = ds.Tables[0].Rows[0]["USER_ID"].ToString();
userVo.user.userNm = ds.Tables[0].Rows[0]["USER_NM"].ToString();
userVo.user.userGradeNo = ds.Tables[0].Rows[0]["USER_GRADE_NO"].ToString();
userVo.userGrade.userGradeNo = ds.Tables[0].Rows[0]["USER_GRADE_NO"].ToString();
userVo.userGrade.userGradeNm = ds.Tables[0].Rows[0]["USER_GRADE_NM"].ToString();
}
}
return(userVo);
}
/// <summary>
/// 구성Vo조회
/// </summary>
/// <param name="configurationVo">구성Vo</param>
/// <returns>구성Vo</returns>
public ConfigurationVo selectConfigurationVo(ConfigurationVo configurationVo)
{
ConfigurationVo rtnConfigurationVo = new ConfigurationVo();
using (SqlCommon sqlCommon = new SqlCommon())
{
string procedureName = "UP_CONFIGURATION_SELECT";
List <SqlParameter> sqlParameters = new List <SqlParameter>();
sqlParameters.Add(new SqlParameter("@CONFIGURATION_ID", configurationVo.configuration.configurationId));
DataSet ds = sqlCommon.ExecuteProcedure(procedureName, sqlParameters);
if ((ds != null) &&
(ds.Tables != null) && (ds.Tables[0] != null) &&
(ds.Tables[0].Rows.Count > 0) && (ds.Tables[0].Rows[0] != null))
{
rtnConfigurationVo.configuration.configurationId = ds.Tables[0].Rows[0]["CONFIGURATION_ID"].ToString();
rtnConfigurationVo.configuration.configurationNm = ds.Tables[0].Rows[0]["CONFIGURATION_NM"].ToString();
rtnConfigurationVo.configuration.configurationVal = ds.Tables[0].Rows[0]["CONFIGURATION_VAL"].ToString();
rtnConfigurationVo.configuration.configurationUsed = bool.Parse(ds.Tables[0].Rows[0]["CONFIGURATION_USED"].ToString());
rtnConfigurationVo.configuration.configurationSubgroupNo = int.Parse(ds.Tables[0].Rows[0]["CONFIGURATION_SUBGROUP_NO"].ToString());
}
}
return(rtnConfigurationVo);
}
private int GetNewID()
{
string tblName = "Sales_JobMasterList_Invoice";
int nID = SqlCommon.GetNewlyInsertedRecordID(tblName);
return(nID);
}
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(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 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 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 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 IntoHospital GetIntoHospital(Request request)
{
string sqlBak = $"select top 1 * from(select 病人编号,住院号,姓名,入院日期,医保类型,性别,身份证号,年龄,出生日期,民族,婚否,职业,电话,家庭住址,联系人,联系人地址,联系电话,关系,入院诊断,病情,确诊诊断,出院日期,医师代码,科室,病室,床位 from ZY_病案库 where 住院号<>'0') as BAK where 住院号='{request.InPatientNo}' and 身份证号='{request.IdentCard}' order by 入院日期 desc";
DataTable dtBak = SqlCommon.ExecuteSqlToDataSet(SqlCommon.GetConnectionStringFromConnectionStrings("HisConnectionString"), sqlBak).Tables[0];
if (dtBak != null && dtBak.Rows.Count > 0)
{
string sqlPatBaseInf = $"select top 1 * from (select patId,PatName,MainNarrative,InDate,Diagnosis1 from EH_PatBasInf union select patId,PatName,MainNarrative,InDate,Diagnosis1 from EH_PatBasInfOut) as EH_PatBasInf where PatId='{dtBak.Rows[0]["住院号"].ToString()}' order by InDate desc";
DataTable dtPatBaseInf = SqlCommon.ExecuteSqlToDataSet(SqlCommon.GetConnectionStringFromConnectionStrings("DzblConnectionString"), sqlPatBaseInf).Tables[0];
IntoHospital intoHospital = new IntoHospital
{
HospitalId = ConfigurationManager.AppSettings["HospitalId"],
HospitalName = ConfigurationManager.AppSettings["HospitalName"],
PatientName = dtBak.Rows[0]["姓名"].ToString(),
PatientAge = dtBak.Rows[0]["年龄"].ToString(),
IdentCard = dtBak.Rows[0]["身份证号"].ToString(),
GenderValue = dtBak.Rows[0]["性别"].ToString() == "男" ? "1" : "0",
Rcvdate = Convert.ToDateTime(dtBak.Rows[0]["入院日期"]).ToString("yyyy-MM-dd"),
ActionInChief = dtPatBaseInf != null && dtPatBaseInf.Rows.Count > 0 && dtPatBaseInf.Rows[0]["MainNarrative"].ToString() != "" ? dtPatBaseInf.Rows[0]["MainNarrative"].ToString() : "暂无",
AllergicHistory = "暂无",
HisPresentIllness = "暂无",
PastHistory = "暂无",
PersonalHistory = "暂无",
FamilyHistory = "暂无",
RecordDate = Convert.ToDateTime(dtBak.Rows[0]["入院日期"]).ToString("yyyy-MM-dd"),
PhysicalExamination = "暂无",
AssistantExamination = "暂无",
PrimaryDiagnosis = dtBak.Rows[0]["入院诊断"].ToString(),
Process = null,
BloodAmylase = null,
UrineRoutines = null,
AmylaseInUrine = null,
BloodRoutine = null,
ColorUltrasound = null,
DischargeDiagnosis = dtBak.Rows[0]["确诊诊断"].ToString(),
Recoder = dtBak.Rows[0]["医师代码"].ToString(),
RecordingTime = Convert.ToDateTime(dtBak.Rows[0]["入院日期"]).ToString("yyyy-MM-dd hh:mm:ss"),
T = null,
P = null,
R = null,
Bp = null,
Other1 = null,
Other2 = null,
Other3 = null,
Other4 = null,
Other5 = null,
PID = dtBak.Rows[0]["病人编号"].ToString(),
InpatientNo = dtBak.Rows[0]["住院号"].ToString(),
DzjkNo = null
};
return(intoHospital);
}
else
{
return(null);
}
}
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));
}
}
/// <summary>
/// 사용자가족Vo추가
/// </summary>
/// <param name="userFamilyVo">사용자가족Vo</param>
/// <returns>사용자가족Vo</returns>
public void insertUserFamilyVo(UserFamilyVo userFamilyVo)
{
using (SqlCommon sqlCommon = new SqlCommon())
{
string procedureName = "UP_USER_FAMILY_INSERT";
List <SqlParameter> sqlParameters = new List <SqlParameter>();
sqlParameters.Add(new SqlParameter("@USER_ID", userFamilyVo.userFamily.userId));
sqlParameters.Add(new SqlParameter("@USER_FAMILY_TYPE_NO", userFamilyVo.userFamily.userFamilyTypeNo));
sqlParameters.Add(new SqlParameter("@USER_FAMILY_NM", userFamilyVo.userFamily.userFamilyNm));
sqlParameters.Add(new SqlParameter("@BIRTH_YEAR", userFamilyVo.userFamily.birthYear));
sqlParameters.Add(new SqlParameter("@SEX_TYPE_NO", userFamilyVo.userFamily.sexTypeNo));
sqlParameters.Add(new SqlParameter("@JOB_TYPE_NO", userFamilyVo.userFamily.jobTypeNo));
if (userFamilyVo.userFamily.retirementAge != null)
{
sqlParameters.Add(new SqlParameter("@RETIREMENT_AGE", userFamilyVo.userFamily.retirementAge));
}
if (userFamilyVo.userFamily.lifeSpan != null)
{
sqlParameters.Add(new SqlParameter("@LIFE_SPAN", userFamilyVo.userFamily.lifeSpan));
}
sqlCommon.ExecuteProcedure(procedureName, sqlParameters);
}
}
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 void CreateNew()
{
InsertRecord();
int myNewID = SqlCommon.GetNewlyInsertedRecordID("Invoice_Item");
UpdateSerialID(myNewID);
}
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 List <UserFamilyBirthYearVo> selectlistUserFamilyTypeVo(UserFamilyBirthYearVo userFamilyBirthYearVo)
{
List <UserFamilyBirthYearVo> userFamilyBirthYearVoList = new List <UserFamilyBirthYearVo>();
using (SqlCommon sqlCommon = new SqlCommon())
{
string procedureName = "UP_USER_FAMILY_BIRTH_YEAR_LIST";
DataSet ds = sqlCommon.ExecuteProcedure(procedureName, null);
if ((ds != null) &&
(ds.Tables != null) && (ds.Tables[0] != null) &&
(ds.Tables[0].Rows.Count > 0) && (ds.Tables[0].Rows[0] != null))
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
UserFamilyBirthYearVo userFamilyBirthYearVoResult = new UserFamilyBirthYearVo();
userFamilyBirthYearVoResult.userFamilyBirthYear.birthYear = int.Parse(dr["BIRTH_YEAR"].ToString());
if ((dr["DEFAULT_VAL"] != null) && (dr["DEFAULT_VAL"].ToString() == "Y"))
{
userFamilyBirthYearVoResult.userFamilyBirthYear.defaultVal = true;
}
userFamilyBirthYearVoList.Add(userFamilyBirthYearVoResult);
}
}
}
return(userFamilyBirthYearVoList);
}
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 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 List <UserFamilyLifeSpanVo> selectlistUserFamilyLifeSpanVo(UserFamilyLifeSpanVo userFamilyLifeSpanVo)
{
List <UserFamilyLifeSpanVo> userFamilyLifeSpanVoList = new List <UserFamilyLifeSpanVo>();
using (SqlCommon sqlCommon = new SqlCommon())
{
string procedureName = "UP_USER_FAMILY_LIFE_SPAN_LIST";
DataSet ds = sqlCommon.ExecuteProcedure(procedureName, null);
if ((ds != null) &&
(ds.Tables != null) && (ds.Tables[0] != null) &&
(ds.Tables[0].Rows.Count > 0) && (ds.Tables[0].Rows[0] != null))
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
UserFamilyLifeSpanVo userFamilyLifeSpanVoResult = new UserFamilyLifeSpanVo();
userFamilyLifeSpanVoResult.userFamilyLifeSpan.lifeSpan = int.Parse(dr["LIFE_SPAN"].ToString());
userFamilyLifeSpanVoList.Add(userFamilyLifeSpanVoResult);
}
}
}
return(userFamilyLifeSpanVoList);
}
public List <UserFamilySexTypeVo> selectlistUserFamilySexTypeVo(UserFamilySexTypeVo userFamilySexTypeVo)
{
List <UserFamilySexTypeVo> userFamilySexTypeVoList = new List <UserFamilySexTypeVo>();
using (SqlCommon sqlCommon = new SqlCommon())
{
string procedureName = "UP_USER_FAMILY_SEX_TYPE_LIST";
DataSet ds = sqlCommon.ExecuteProcedure(procedureName, null);
if ((ds != null) &&
(ds.Tables != null) && (ds.Tables[0] != null) &&
(ds.Tables[0].Rows.Count > 0) && (ds.Tables[0].Rows[0] != null))
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
UserFamilySexTypeVo userFamilySexTypeVoResult = new UserFamilySexTypeVo();
userFamilySexTypeVoResult.userFamilySexType.sexTypeNo = int.Parse(dr["SEX_TYPE_NO"].ToString());
userFamilySexTypeVoResult.userFamilySexType.sexTypeNm = dr["SEX_TYPE_NM"].ToString();
userFamilySexTypeVoList.Add(userFamilySexTypeVoResult);
}
}
}
return(userFamilySexTypeVoList);
}
public List <PrescriptionDetail> GetPrescriptionDetails(string cardNo, string startDate, string endDate)
{
string sqlMzhj = $"select * from (select 发票流水号,日期,卡号,病人姓名,科室ID,医师,YF_ID,名称,单位,数量,用法,用量 from 划价临时库 where 材质分类 in ('西药','中成药','中草药','中药颗粒','中药饮片') and 科室ID<>0 and 发票流水号<>'-1' union select 发票流水号,日期,卡号,病人姓名,科室ID,医师,YF_ID,名称,单位,数量,用法,用量 from 划价流水帐 where 材质分类 in ('西药','中成药','中草药','中药颗粒','中药饮片') and 科室ID<>0 and 发票流水号<>'-1') as Mzhj where 卡号='{cardNo}' and 日期 between '{startDate}' and '{endDate}'";
DataTable dtMzhj = SqlCommon.ExecuteSqlToDataSet(SqlCommon.GetConnectionStringFromConnectionStrings("HisConnectionString"), sqlMzhj).Tables[0];
List <PrescriptionDetail> prescriptionDetails = new List <PrescriptionDetail>();
if (dtMzhj != null && dtMzhj.Rows.Count > 0)
{
for (int i = 0; i < dtMzhj.Rows.Count; i++)
{
PrescriptionDetail prescriptionDetail = new PrescriptionDetail
{
MedicineNO = dtMzhj.Rows[i]["YF_ID"].ToString(),
MedicineName = dtMzhj.Rows[i]["名称"].ToString(),
MedicineCount = Convert.ToDouble(dtMzhj.Rows[i]["数量"]).ToString("0.00"),
MedicineUnit = dtMzhj.Rows[i]["单位"].ToString(),
Usage = dtMzhj.Rows[i]["用法"].ToString() + dtMzhj.Rows[i]["用量"].ToString(),
};
prescriptionDetails.Add(prescriptionDetail);
}
}
return(prescriptionDetails);
}
public List <UserFamilyRetirementAgeVo> selectlistUserFamilyRetirementAgeVo(UserFamilyRetirementAgeVo userFamilyRetirementAgeVo)
{
List <UserFamilyRetirementAgeVo> userFamilyRetirementAgeVoList = new List <UserFamilyRetirementAgeVo>();
using (SqlCommon sqlCommon = new SqlCommon())
{
string procedureName = "UP_USER_FAMILY_RETIREMENT_AGE_LIST";
DataSet ds = sqlCommon.ExecuteProcedure(procedureName, null);
if ((ds != null) &&
(ds.Tables != null) && (ds.Tables[0] != null) &&
(ds.Tables[0].Rows.Count > 0) && (ds.Tables[0].Rows[0] != null))
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
UserFamilyRetirementAgeVo userFamilyRetirementAgeVoResult = new UserFamilyRetirementAgeVo();
userFamilyRetirementAgeVoResult.userFamilyRetirementAge.retirementAge = int.Parse(dr["RETIREMENT_AGE"].ToString());
userFamilyRetirementAgeVoList.Add(userFamilyRetirementAgeVoResult);
}
}
}
return(userFamilyRetirementAgeVoList);
}
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()
{
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 void InsertItem()
{
InsertWorkorderItem();
int newWoItemID = SqlCommon.GetNewlyInsertedRecordID("WO_Item");
NewWorkItemID = newWoItemID;
GenerateSerialID(newWoItemID);
}
public void SqlInsertUpdate()
{
string sql = $"update MobileWebToken set AuthToken='{AuthToken}', IssuedOn='{IssuedOn}', ExpiresOn='{ExpiresOn}' where UserId={UserId}; " +
$"IF(@@ROWCOUNT=0) " +
$"insert MobileWebToken(UserId, AuthToken, IssuedOn, ExpiresOn) " +
$"values({UserId}, '{AuthToken}', '{IssuedOn}', '{ExpiresOn}');";
SqlCommon.ExecuteNonQuery(sql, WebCommon.WebConnection);
}
