public int DataUpdate(FunctionModifyReq req)
{
int count = 0;
using (DbCommand cmd = Db.CreateConnection().CreateCommand())
{
string sql = @"
UPDATE FUNCTIONS
SET NAME=@NAME,CATEGORY=@CATEGORY,PARENT_SN=@PARENT_SN,URL=@URL,SORT=@SORT
,MODE=@MODE,MDATE=GETDATE(),MUSER=@MUSER
WHERE SN=@SN;
";
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
Db.AddInParameter(cmd, "SN", DbType.String, req.FUNCTIONS.SN);
Db.AddInParameter(cmd, "NAME", DbType.String, req.FUNCTIONS.NAME);
Db.AddInParameter(cmd, "CATEGORY", DbType.String, req.FUNCTIONS.CATEGORY);
Db.AddInParameter(cmd, "PARENT_SN", DbType.Int16, req.FUNCTIONS.PARENT_SN);
Db.AddInParameter(cmd, "URL", DbType.String, req.FUNCTIONS.URL);
Db.AddInParameter(cmd, "SORT", DbType.Int16, req.FUNCTIONS.SORT);
Db.AddInParameter(cmd, "MODE", DbType.String, req.FUNCTIONS.MODE);
//Db.AddInParameter(cmd, "MDATE", DbType.DateTime, req.FUNCTIONS.MDATE);
Db.AddInParameter(cmd, "MUSER", DbType.Int16, req.FUNCTIONS.MUSER);
count = Db.ExecuteNonQuery(cmd);
}
return(count);
}
public USER_INFO UserLoginAuthority(string UserId, string Password)
{
USER_INFO users = null;
string sql = @"
SELECT U.SN, U.NAME, U.PASSWORD, U.FORCE_PWD, G.ROLES_SN
FROM USERS U LEFT JOIN GRANTS G
ON U.SN=G.USERS_SN
WHERE U.ID=@ID AND U.MODE='Y'
";
using (DbCommand cmd = Db.GetSqlStringCommand(sql))
{
Db.AddInParameter(cmd, "ID", DbType.String, UserId);
using (IDataReader reader = this.Db.ExecuteReader(cmd))
{
if (reader.Read() && reader["PASSWORD"] as string == Password)
{
users = new USER_INFO()
{
SN = reader["SN"] as Int16? ?? null,
ID = UserId,
NAME = reader["NAME"] as string,
FORCE_PWD = reader["FORCE_PWD"] as Int16? ?? null,
ROLES_SN = reader["ROLES_SN"] as Int32? ?? null
};
}
}
}
return(users);
}
public int DataUpdate(PhraseModifyReq req)
{
int count = 0;
using (DbCommand cmd = Db.CreateConnection().CreateCommand())
{
string sql = @"
UPDATE PHRASE
SET PHRASE_GROUP=@PHRASE_GROUP,PHRASE_KEY=@PHRASE_KEY,PHRASE_VALUE=@PHRASE_VALUE,PHRASE_DESC=@PHRASE_DESC,SORT=@SORT,MODE=@MODE,MDATE=GETDATE(),MUSER=@MUSER
WHERE SN=@SN;
";
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
Db.AddInParameter(cmd, "SN", DbType.String, req.PHRASE.SN);
Db.AddInParameter(cmd, "PHRASE_GROUP", DbType.String, req.PHRASE.PHRASE_GROUP);
Db.AddInParameter(cmd, "PHRASE_KEY", DbType.String, req.PHRASE.PHRASE_KEY);
Db.AddInParameter(cmd, "PHRASE_VALUE", DbType.String, req.PHRASE.PHRASE_VALUE);
Db.AddInParameter(cmd, "PHRASE_DESC", DbType.String, req.PHRASE.PHRASE_DESC);
Db.AddInParameter(cmd, "SORT", DbType.String, req.PHRASE.SORT);
Db.AddInParameter(cmd, "MODE", DbType.String, req.PHRASE.MODE);
Db.AddInParameter(cmd, "MUSER", DbType.String, req.PHRASE.MUSER);
count = Db.ExecuteNonQuery(cmd);
}
return(count);
}
public void DeleteItem(string hospitalId, string moveId, string productId)
{
var sql = @"
delete goods_runtime from goods_runtime r, move_form_serial s
where r.apply_id = s.move_id
and r.serial_id = s.to_serial
and r.hospital_id = @p_hospital_id
and s.move_id = @p_move_id
and s.product_id = @p_product_id;
delete goods_inventory_runtime
where apply_id = @p_move_id
and hospital_id = @p_hospital_id
and product_id = @p_product_id;
delete move_form_serial
where move_id = @p_move_id
and product_id = @p_product_id;
delete move_form_items
where move_id = @p_move_id
and product_id = @p_product_id;
";
var cmd = Db.GetSqlStringCommand(sql);
Db.AddInParameter(cmd, "p_move_id", DbType.String, moveId);
Db.AddInParameter(cmd, "p_hospital_id", DbType.String, hospitalId);
Db.AddInParameter(cmd, "p_product_id", DbType.String, productId);
Db.ExecuteNonQuery(cmd, DbTrans);
}
public ROLES ModificationQuery(int?SN)
{
ROLES row = null;
using (DbCommand cmd = Db.CreateConnection().CreateCommand())
{
string sql = @"
SELECT SN,NAME,MODE,CDATE,CUSER,MDATE,MUSER
FROM ROLES
WHERE SN=@SN
";
Db.AddInParameter(cmd, "SN", DbType.Int32, SN);
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
using (IDataReader reader = Db.ExecuteReader(cmd))
{
while (reader.Read())
{
row = new ROLES
{
SN = reader["SN"] as Int32? ?? null,
NAME = reader["NAME"] as string,
MODE = reader["MODE"] as string,
CDATE = reader["CDATE"] as DateTime?,
CUSER = reader["CUSER"] as string,
MDATE = reader["MDATE"] as DateTime?,
MUSER = reader["MUSER"] as string
};
}
}
}
return(row);
}
public List <GRANTS> ByRolesQuery(int?ROLES_SN)
{
List <GRANTS> GRANTS = new List <GRANTS>();
using (DbCommand cmd = Db.CreateConnection().CreateCommand())
{
string sql = @"
SELECT U.SN,U.NAME,G.ROLES_SN
FROM USERS U LEFT JOIN GRANTS G
ON U.SN=G.USERS_SN AND G.ROLES_SN=@ROLES_SN
";
Db.AddInParameter(cmd, "ROLES_SN", DbType.Int32, ROLES_SN);
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
using (IDataReader reader = Db.ExecuteReader(cmd))
{
while (reader.Read())
{
GRANTS row = new GRANTS
{
ROLES_SN = reader["ROLES_SN"] as Int32? ?? null,
USERS_SN = reader["SN"] as Int16? ?? null,
USERS_NAME = reader["NAME"] as string
};
GRANTS.Add(row);
}
}
}
return(GRANTS);
}
public int DataCreate(RolesModifyReq req)
{
int count = 0;
using (DbCommand cmd = Db.CreateConnection().CreateCommand())
{
string sql = @"
INSERT ROLES (NAME,MODE,CDATE,CUSER,MDATE,MUSER)
VALUES (@NAME,@MODE,GETDATE(),@CUSER,GETDATE(),@MUSER) SET @SN = SCOPE_IDENTITY();
";
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
Db.AddInParameter(cmd, "NAME", DbType.String, req.ROLES.NAME);
Db.AddInParameter(cmd, "MODE", DbType.String, req.ROLES.MODE);
Db.AddInParameter(cmd, "CDATE", DbType.String, req.ROLES.CDATE);
Db.AddInParameter(cmd, "CUSER", DbType.String, req.ROLES.CUSER);
Db.AddInParameter(cmd, "MDATE", DbType.String, req.ROLES.MDATE);
Db.AddInParameter(cmd, "MUSER", DbType.String, req.ROLES.MUSER);
Db.AddOutParameter(cmd, "SN", DbType.Int32, 1);
count = Db.ExecuteNonQuery(cmd);
req.ROLES.SN = Db.GetParameterValue(cmd, "SN") as Int32? ?? null;
}
return(count);
}
public int DataCreate(FunctionModifyReq req)
{
int count = 0;
using (DbCommand cmd = Db.CreateConnection().CreateCommand())
{
string sql = @"
INSERT INTO FUNCTIONS (NAME,CATEGORY,PARENT_SN,URL,SORT,MODE,CDATE,CUSER,MDATE,MUSER)
VALUES (@NAME,@CATEGORY,@PARENT_SN,@URL,@SORT,@MODE,GETDATE(),@CUSER,GETDATE(),@MUSER) SET @SN = SCOPE_IDENTITY();";
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
Db.AddInParameter(cmd, "NAME", DbType.String, req.FUNCTIONS.NAME);
Db.AddInParameter(cmd, "CATEGORY", DbType.String, req.FUNCTIONS.CATEGORY);
Db.AddInParameter(cmd, "PARENT_SN", DbType.Int16, req.FUNCTIONS.PARENT_SN);
Db.AddInParameter(cmd, "URL", DbType.String, req.FUNCTIONS.URL);
Db.AddInParameter(cmd, "SORT", DbType.Int16, req.FUNCTIONS.SORT);
Db.AddInParameter(cmd, "MODE", DbType.String, req.FUNCTIONS.MODE);
//Db.AddInParameter(cmd, "CDATE", DbType.DateTime, req.FUNCTIONS.CDATE);
Db.AddInParameter(cmd, "CUSER", DbType.Int16, req.FUNCTIONS.CUSER);
//Db.AddInParameter(cmd, "MDATE", DbType.DateTime, req.FUNCTIONS.MDATE);
Db.AddInParameter(cmd, "MUSER", DbType.Int16, req.FUNCTIONS.MUSER);
Db.AddOutParameter(cmd, "SN", DbType.Int16, 1);
count = Db.ExecuteNonQuery(cmd);
req.FUNCTIONS.SN = Db.GetParameterValue(cmd, "SN") as Int16? ?? null;
}
return(count);
}
public void DeletePrinter(int id)
{
var command = Db.GetStoredProcCommand(DeletePrinterStoredProcedureName);
Db.AddInParameter(command, "@Id", System.Data.DbType.String, id);
Db.ExecuteNonQuery(command);
}
public int DataCreate(PhraseModifyReq req)
{
int count = 0;
using (DbCommand cmd = Db.CreateConnection().CreateCommand())
{
Int64?PHRASE_SN = new Sequence("SCC").GetSeqBigInt("PHRASE");
string sql = @"
INSERT PHRASE (SN,PHRASE_GROUP,PHRASE_KEY,PHRASE_VALUE,PHRASE_DESC,SORT,MODE,CDATE,CUSER,MDATE,MUSER)
VALUES (@SN,@PHRASE_GROUP,@PHRASE_KEY,@PHRASE_VALUE,@PHRASE_DESC,@SORT,@MODE,GETDATE(),@CUSER,GETDATE(),@MUSER);
";
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
Db.AddInParameter(cmd, "SN", DbType.Int32, PHRASE_SN);
Db.AddInParameter(cmd, "PHRASE_GROUP", DbType.String, req.PHRASE.PHRASE_GROUP);
Db.AddInParameter(cmd, "PHRASE_KEY", DbType.String, req.PHRASE.PHRASE_KEY);
Db.AddInParameter(cmd, "PHRASE_VALUE", DbType.String, req.PHRASE.PHRASE_VALUE);
Db.AddInParameter(cmd, "PHRASE_DESC", DbType.String, req.PHRASE.PHRASE_DESC);
Db.AddInParameter(cmd, "SORT", DbType.String, req.PHRASE.SORT);
Db.AddInParameter(cmd, "MODE", DbType.String, req.PHRASE.MODE);
Db.AddInParameter(cmd, "CUSER", DbType.String, req.PHRASE.CUSER);
Db.AddInParameter(cmd, "MUSER", DbType.String, req.PHRASE.MUSER);
count = Db.ExecuteNonQuery(cmd);
req.PHRASE.SN = (int?)PHRASE_SN;
}
return(count);
}
public FEELING ModificationQuery(int?SN)
{
FEELING row = null;
using (DbCommand cmd = Db.CreateConnection().CreateCommand())
{
string sql = @"
SELECT
FROM FEELING
WHERE SN=@SN
";
Db.AddInParameter(cmd, "SN", DbType.Int32, SN);
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
using (IDataReader reader = Db.ExecuteReader(cmd))
{
while (reader.Read())
{
row = new FEELING
{
};
}
}
}
return(row);
}
public void AddPrinter(string serial, string mark, int model, int copiesCounter)
{
var command = Db.GetStoredProcCommand(AddPrinterStoredProcedureName);
Db.AddInParameter(command, "@serial", System.Data.DbType.String, serial);
Db.AddInParameter(command, "@mark", System.Data.DbType.String, mark);
Db.AddInParameter(command, "@model", System.Data.DbType.Int32, model);
Db.AddInParameter(command, "@copiesCounter", System.Data.DbType.Int32, copiesCounter);
Db.ExecuteScalar(command);
}
public void UpdatePrinter(int id, string serial, string mark, int model, int copiesCounter)
{
var command = Db.GetStoredProcCommand(UpdatePrinterStoredProcedureName);
Db.AddInParameter(command, "@Id", System.Data.DbType.String, id);
Db.AddInParameter(command, "@serial", System.Data.DbType.String, serial);
Db.AddInParameter(command, "@mark", System.Data.DbType.String, mark);
Db.AddInParameter(command, "@model", System.Data.DbType.Int32, model);
Db.AddInParameter(command, "@copiesCounter", System.Data.DbType.Int32, copiesCounter);
Db.ExecuteNonQuery(command);
}
public CASE_OWNER ModificationQuery(int?SN)
{
CASE_OWNER row = null;
using (DbCommand cmd = Db.CreateConnection().CreateCommand())
{
string sql = @"
SELECT SN,NAME1,NAME2,NAME3,NAME4,NAME5,NAME6,TEL1,TEL2,TEL3,TEL4,TEL5,TEL6,YEARS_OLD,ADDRESS,TEACHER1,TEACHER2,TEACHER3,FAMILY,RESIDENCE,NOTE,PROBLEM,EXPERIENCE,SUGGEST,CDATE,CUSER,MDATE,MUSER
FROM CASE_OWNER
WHERE SN=@SN
";
Db.AddInParameter(cmd, "SN", DbType.Int32, SN);
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
using (IDataReader reader = Db.ExecuteReader(cmd))
{
while (reader.Read())
{
row = new CASE_OWNER
{
SN = reader["SN"] as int? ?? null,
NAME1 = reader["NAME1"] as string,
NAME2 = reader["NAME2"] as string,
NAME3 = reader["NAME3"] as string,
NAME4 = reader["NAME4"] as string,
NAME5 = reader["NAME5"] as string,
NAME6 = reader["NAME6"] as string,
TEL1 = reader["TEL1"] as string,
TEL2 = reader["TEL2"] as string,
TEL3 = reader["TEL3"] as string,
TEL4 = reader["TEL4"] as string,
TEL5 = reader["TEL5"] as string,
TEL6 = reader["TEL6"] as string,
YEARS_OLD = reader["YEARS_OLD"] as string,
ADDRESS = reader["ADDRESS"] as string,
FAMILY = reader["FAMILY"] as string,
EXPERIENCE = reader["EXPERIENCE"] as string,
SUGGEST = reader["SUGGEST"] as string,
CDATE = reader["CDATE"] as DateTime?,
CUSER = reader["CUSER"] as string,
MDATE = reader["MDATE"] as DateTime?,
MUSER = reader["MUSER"] as string
};
}
}
}
return(row);
}
public List <MoveFormEntity> FindAll(DateRangeCondition condition, PagerInfo pager)
{
pager.ComputePageCount(QueryCount(condition));
var list = new List <MoveFormEntity>();
var orderSql = " ORDER BY ";
if (pager.OrderFields.Count > 0)
{
foreach (var field in pager.OrderFields)
{
orderSql += field.Field + (field.Desc ? " DESC" : "") + ",";
}
}
else
{
orderSql += "form_no DESC";
}
var sql = string.Format(@"SELECT {0} FROM move_form WHERE status != 'deleted' and {1}", MOVE_COLUMNS, GetConditionSql(condition));
sql = @"SELECT * FROM
(
SELECT ROW_NUMBER() OVER(" + orderSql + @") pid," + MOVE_COLUMNS + @"
FROM (" + sql + @") t
) t1 WHERE t1.pid BETWEEN @p_pageNo * @p_pageSize + 1 AND (@p_pageNo + 1) * @p_pageSize ";
var dc = Db.GetSqlStringCommand(sql);
AddParameter(dc, condition);
Db.AddInParameter(dc, "p_pageNo", DbType.Int32, pager.PageIndex);
Db.AddInParameter(dc, "p_pageSize", DbType.Int32, pager.PageSize);
using (IDataReader reader = Db.ExecuteReader(dc))
{
while (reader.Read())
{
var entity = new MoveFormEntity();
entity.Init(reader);
list.Add(entity);
}
}
return(list);
}
public FeelingRetrieveRes ReportData(FeelingRetrieveReq req)
{
FeelingRetrieveRes res = new FeelingRetrieveRes()
{
FEELING = new List <FEELING>(),
Pagination = new Pagination
{
PageCount = 0,
RowCount = 0,
PageNumber = 0,
MinNumber = 0,
MaxNumber = 0,
StartTime = DateTime.Now
}
};
using (DbCommand cmd = Db.CreateConnection().CreateCommand())
{
string sql = @"SELECT TOP(@TOP)
FROM FEELING{0}
ORDER BY SN DESC;";
string where = "";
Db.AddInParameter(cmd, "TOP", DbType.Int32, 1000000);
if (where.Length > 0)
{
where = " WHERE" + where.Substring(4);
}
sql = String.Format(sql, where);
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
using (IDataReader reader = Db.ExecuteReader(cmd))
{
while (reader.Read())
{
var row = new FEELING
{
};
res.FEELING.Add(row);
}
}
}
res.Pagination.EndTime = DateTime.Now;
return(res);
}
public int DataDelete(CaseOwnerModifyReq req)
{
int count = 0;
using (DbCommand cmd = Db.CreateConnection().CreateCommand())
{
string sql = @"
DELETE FROM CASE_OWNER
WHERE SN=@SN;
";
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
Db.AddInParameter(cmd, "SN", DbType.String, req.CASE_OWNER.SN);
count = Db.ExecuteNonQuery(cmd);
}
return(count);
}
public MoveFormSerialEntity SaveSerial(MoveFormSerialEntity moveSerial)
{
string sql = string.Empty;
if (string.IsNullOrEmpty(moveSerial.Id))
{
sql = string.Format(@"insert into move_form_serial({0}) values(
@p_id
,@p_move_id
,@p_form_no
,@p_product_id
,@p_from_serial
,@p_to_serial
,@p_count
,@p_package_count)", SERIAL_COLUMNS);
moveSerial.Id = Guid.NewGuid().ToString();
}
else
{
sql = @"update move_form_serial set
move_id = @p_move_id
,form_no = @p_form_no
,product_id = @p_product_id
,from_serial = @p_from_serial
,to_serial = @p_to_serial
,count = @p_count
,package_count = @p_package_count
where id = @p_id";
}
DbCommand dc = Db.GetSqlStringCommand(sql);
Db.AddInParameter(dc, "p_id", DbType.String, moveSerial.Id);
Db.AddInParameter(dc, "p_move_id", DbType.String, moveSerial.MoveId);
Db.AddInParameter(dc, "p_form_no", DbType.Int32, moveSerial.FormNo);
Db.AddInParameter(dc, "p_product_id", DbType.String, moveSerial.ProductId);
Db.AddInParameter(dc, "p_from_serial", DbType.String, moveSerial.FromSerial);
Db.AddInParameter(dc, "p_to_serial", DbType.String, moveSerial.ToSerial);
Db.AddInParameter(dc, "p_count", DbType.Int32, moveSerial.Count);
Db.AddInParameter(dc, "p_package_count", DbType.Int32, moveSerial.PackageCount);
Db.ExecuteNonQuery(dc, DbTrans);
return(moveSerial);
}
public int DataCreate(CaseOwnerModifyReq req)
{
int count = 0;
using (DbCommand cmd = Db.CreateConnection().CreateCommand())
{
string sql = @"
INSERT CASE_OWNER (NAME1,NAME2,NAME3,NAME4,NAME5,NAME6,TEL1,TEL2,TEL3,TEL4,TEL5,TEL6,YEARS_OLD,ADDRESS,TEACHER1,TEACHER2,TEACHER3,FAMILY,RESIDENCE,NOTE,PROBLEM,EXPERIENCE,SUGGEST,CDATE,CUSER,MDATE,MUSER)
VALUES (@NAME1,@NAME2,@NAME3,@NAME4,@NAME5,@NAME6,@TEL1,@TEL2,@TEL3,@TEL4,@TEL5,@TEL6,@YEARS_OLD,@ADDRESS,@TEACHER1,@TEACHER2,@TEACHER3,@FAMILY,@RESIDENCE,@NOTE,@PROBLEM,@EXPERIENCE,@SUGGEST,GETDATE(),@CUSER,GETDATE(),@MUSER) SET @SN = SCOPE_IDENTITY();
";
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
Db.AddInParameter(cmd, "NAME1", DbType.String, req.CASE_OWNER.NAME1);
Db.AddInParameter(cmd, "NAME2", DbType.String, req.CASE_OWNER.NAME2);
Db.AddInParameter(cmd, "NAME3", DbType.String, req.CASE_OWNER.NAME3);
Db.AddInParameter(cmd, "NAME4", DbType.String, req.CASE_OWNER.NAME4);
Db.AddInParameter(cmd, "NAME5", DbType.String, req.CASE_OWNER.NAME5);
Db.AddInParameter(cmd, "NAME6", DbType.String, req.CASE_OWNER.NAME6);
Db.AddInParameter(cmd, "TEL1", DbType.String, req.CASE_OWNER.TEL1);
Db.AddInParameter(cmd, "TEL2", DbType.String, req.CASE_OWNER.TEL2);
Db.AddInParameter(cmd, "TEL3", DbType.String, req.CASE_OWNER.TEL3);
Db.AddInParameter(cmd, "TEL4", DbType.String, req.CASE_OWNER.TEL4);
Db.AddInParameter(cmd, "TEL5", DbType.String, req.CASE_OWNER.TEL5);
Db.AddInParameter(cmd, "TEL6", DbType.String, req.CASE_OWNER.TEL6);
Db.AddInParameter(cmd, "YEARS_OLD", DbType.String, req.CASE_OWNER.YEARS_OLD);
Db.AddInParameter(cmd, "ADDRESS", DbType.String, req.CASE_OWNER.ADDRESS);
Db.AddInParameter(cmd, "TEACHER1", DbType.String, req.CASE_OWNER.TEACHER1);
Db.AddInParameter(cmd, "TEACHER2", DbType.String, req.CASE_OWNER.TEACHER2);
Db.AddInParameter(cmd, "TEACHER3", DbType.String, req.CASE_OWNER.TEACHER3);
Db.AddInParameter(cmd, "FAMILY", DbType.String, req.CASE_OWNER.FAMILY);
Db.AddInParameter(cmd, "RESIDENCE", DbType.String, req.CASE_OWNER.RESIDENCE);
Db.AddInParameter(cmd, "NOTE", DbType.String, req.CASE_OWNER.NOTE);
Db.AddInParameter(cmd, "PROBLEM", DbType.String, req.CASE_OWNER.PROBLEM);
Db.AddInParameter(cmd, "EXPERIENCE", DbType.String, req.CASE_OWNER.EXPERIENCE);
Db.AddInParameter(cmd, "SUGGEST", DbType.String, req.CASE_OWNER.SUGGEST);
Db.AddInParameter(cmd, "CUSER", DbType.String, req.CASE_OWNER.CUSER);
Db.AddInParameter(cmd, "MUSER", DbType.String, req.CASE_OWNER.MUSER);
Db.AddOutParameter(cmd, "SN", DbType.Int32, 1);
count = Db.ExecuteNonQuery(cmd);
req.CASE_OWNER.SN = Db.GetParameterValue(cmd, "SN") as Int32? ?? null;
}
return(count);
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public string UserFunctionAuthority(USER_INFO UserInfo)
{
List <AUTHORITY> authority = new List <AUTHORITY>();
try
{
string sql = @"
SELECT F.SN, F.NAME, F.CATEGORY, F.URL, F.PARENT_SN, A.ROLES_SN
FROM FUNCTIONS F LEFT JOIN AUTHORITY A ON F.SN=A.FUNCTIONS_SN AND A.ROLES_SN=@ROLES_SN
WHERE F.MODE='Y'
ORDER BY F.SORT
";
using (DbCommand cmd = Db.GetSqlStringCommand(sql))
{
Db.AddInParameter(cmd, "ROLES_SN", DbType.String, UserInfo.ROLES_SN);
using (IDataReader reader = this.Db.ExecuteReader(cmd))
{
while (reader.Read())
{
AUTHORITY data = new AUTHORITY
{
//USERS_SN = reader["USERS_SN"] as Int16? ?? null,
//FUNCTIONS_SN = reader["SN"] as Int16? ?? null,
ROLES_SN = reader["ROLES_SN"] as Int32? ?? null,
FUNCTIONS = new FUNCTIONS()
{
SN = reader["SN"] as Int16? ?? null,
NAME = reader["NAME"] as string,
CATEGORY = reader["CATEGORY"] as string,
URL = reader["URL"] as string,
PARENT_SN = reader["PARENT_SN"] as Int16? ?? null
}
};
authority.Add(data);
}
}
}
}
catch
{
throw;
}
return(ConstructSidebar(authority, 0, -2));
}
private void PhraseQuery(string[] phrase_group)
{
using (DbCommand cmd = Db.CreateConnection().CreateCommand())
{
string sql = @"
SELECT PHRASE_GROUP,PHRASE_KEY,PHRASE_VALUE,PHRASE_DESC
FROM PHRASE
WHERE PHRASE_GROUP IN ({0}) AND MODE='Y'
ORDER BY PHRASE_GROUP,SORT";
string values = "";
foreach (string s in phrase_group)
{
values += ",@" + s;
Db.AddInParameter(cmd, s, DbType.String, s);
}
sql = string.Format(sql, values.TrimStart(','));
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
using (IDataReader reader = Db.ExecuteReader(cmd))
{
List <ItemList> rows = null;
string group = null;
while (reader.Read())
{
if (group != reader["PHRASE_GROUP"] as string)
{
rows = new List <ItemList>();
group = reader["PHRASE_GROUP"] as string;
pharse.Add(group, rows);
}
ItemList row = new ItemList
{
Key = reader["PHRASE_KEY"].ToString(),
Value = reader["PHRASE_VALUE"].ToString(),
Desc = reader["PHRASE_DESC"].ToString()
};
rows.Add(row);
}
}
}
}
public MoveFormEntity FindOne(string id)
{
var sql = string.Format("select {0} from move_form where id = @p_id", MOVE_COLUMNS);
var dc = Db.GetSqlStringCommand(sql);
Db.AddInParameter(dc, "p_id", DbType.String, id);
using (var reader = Db.ExecuteReader(dc))
{
if (reader.Read())
{
var entity = new MoveFormEntity();
entity.Init(reader);
return(entity);
}
}
return(null);
}
public Int64?GetSeqBigInt(string Type)
{
Int64?NewSeqNo = null;
using (DbCommand cmd = Db.CreateConnection().CreateCommand())
{
string sql = @"USP_SEQ_BIGINT";
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = sql;
Db.AddInParameter(cmd, "CUR_TYPE", DbType.String, Type);
using (IDataReader reader = Db.ExecuteReader(cmd))
{
reader.Read();
NewSeqNo = reader["NEW_NO"] as Int64? ?? null;
}
}
return(NewSeqNo);
}
public MoveFormItemEntity FindOneItem(string formId, string itemId)
{
var sql = string.Format("select {0} from move_form_items where move_id = @p_move_id and id = @p_id", ITEM_COLUMNS);
var dc = Db.GetSqlStringCommand(sql);
Db.AddInParameter(dc, "p_move_id", DbType.String, formId);
Db.AddInParameter(dc, "p_id", DbType.String, itemId);
using (var reader = Db.ExecuteReader(dc))
{
if (reader.Read())
{
var entity = new MoveFormItemEntity();
entity.Init(reader);
return(entity);
}
}
return(null);
}
public MoveFormSerialEntity FindToSerial(string formId, string toSerial)
{
var sql = string.Format("select {0} from move_form_serial where move_id = @p_move_id and to_serial = @p_to_serial", SERIAL_COLUMNS);
var dc = Db.GetSqlStringCommand(sql);
Db.AddInParameter(dc, "p_move_id", DbType.String, formId);
Db.AddInParameter(dc, "p_to_serial", DbType.String, toSerial);
using (var reader = Db.ExecuteReader(dc))
{
if (reader.Read())
{
var entity = new MoveFormSerialEntity();
entity.Init(reader);
return(entity);
}
}
return(null);
}
public string GetSeqNo(string Type, string Pre, int Len)
{
string NewSeqNo = "";
using (DbCommand cmd = Db.CreateConnection().CreateCommand())
{
string sql = @"USP_SEQ";
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = sql;
Db.AddInParameter(cmd, "CUR_TYPE", DbType.String, Type);
Db.AddInParameter(cmd, "CUR_PRE", DbType.String, Pre);
Db.AddInParameter(cmd, "LEN", DbType.Int32, Len);
using (IDataReader reader = Db.ExecuteReader(cmd))
{
reader.Read();
NewSeqNo = reader["NEW_NO"].ToString();
}
}
return(NewSeqNo);
}
public int DataUpdate(RolesModifyReq req)
{
int count = 0;
using (DbCommand cmd = Db.CreateConnection().CreateCommand())
{
string sql = @"
UPDATE ROLES
SET NAME=@NAME,MODE=@MODE,MDATE=GETDATE(),MUSER=@MUSER
WHERE SN=@SN;
";
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
Db.AddInParameter(cmd, "SN", DbType.String, req.ROLES.SN);
Db.AddInParameter(cmd, "NAME", DbType.String, req.ROLES.NAME);
Db.AddInParameter(cmd, "MODE", DbType.String, req.ROLES.MODE);
Db.AddInParameter(cmd, "MUSER", DbType.String, req.ROLES.MUSER);
count = Db.ExecuteNonQuery(cmd);
}
return(count);
}
private void AddParameter(DbCommand dc, DateRangeCondition condition)
{
Db.AddInParameter(dc, "p_user_id", DbType.String, condition.UserId);
Db.AddInParameter(dc, "p_hospital_id", DbType.String, condition.HospitalId);
int formNo;
if (!string.IsNullOrEmpty(condition.Content) && int.TryParse(condition.Content, out formNo))
{
Db.AddInParameter(dc, "p_form_no", DbType.Int32, formNo);
}
if (condition.BeginDate.HasValue)
{
Db.AddInParameter(dc, "p_begin_date", DbType.DateTime, condition.BeginDate);
}
if (condition.EndDate.HasValue)
{
Db.AddInParameter(dc, "p_end_date", DbType.DateTime, condition.EndDate);
}
}
public PHRASE ModificationQuery(int?SN)
{
PHRASE row = null;
using (DbCommand cmd = Db.CreateConnection().CreateCommand())
{
string sql = @"
SELECT SN,PHRASE_GROUP,PHRASE_KEY,PHRASE_VALUE,PHRASE_DESC,SORT,MODE,CDATE,CUSER,MDATE,MUSER
FROM PHRASE
WHERE SN=@SN
";
Db.AddInParameter(cmd, "SN", DbType.Int32, SN);
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
using (IDataReader reader = Db.ExecuteReader(cmd))
{
while (reader.Read())
{
row = new PHRASE
{
SN = reader["SN"] as Int32? ?? null,
PHRASE_GROUP = reader["PHRASE_GROUP"] as string,
PHRASE_KEY = reader["PHRASE_KEY"] as string,
PHRASE_VALUE = reader["PHRASE_VALUE"] as string,
PHRASE_DESC = reader["PHRASE_DESC"] as string,
SORT = reader["SORT"] as Int32? ?? null,
MODE = reader["MODE"] as string,
CDATE = reader["CDATE"] as DateTime?,
CUSER = reader["CUSER"] as string,
MDATE = reader["MDATE"] as DateTime?,
MUSER = reader["MUSER"] as string
};
}
}
}
return(row);
}
public FUNCTIONS ModificationQuery(Int16?SN)
{
FUNCTIONS row = null;
using (DbCommand cmd = Db.CreateConnection().CreateCommand())
{
string sql = @"
SELECT SN, NAME, MODE, CATEGORY, URL, PARENT_SN, SORT, CDATE, CUSER, MDATE, MUSER
FROM FUNCTIONS WHERE SN=@SN;
";
Db.AddInParameter(cmd, "SN", DbType.Int32, SN);
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
using (IDataReader reader = Db.ExecuteReader(cmd))
{
while (reader.Read())
{
row = new FUNCTIONS
{
SN = reader["SN"] as Int16? ?? null,
NAME = reader["NAME"] as string,
MODE = reader["MODE"] as string,
CATEGORY = reader["CATEGORY"] as string,
URL = reader["URL"] as string,
PARENT_SN = reader["PARENT_SN"] as Int16? ?? null,
SORT = reader["SORT"] as Int16? ?? null,
CDATE = reader["CDATE"] as DateTime? ?? null,
CUSER = reader["CUSER"] as Int16? ?? null,
MDATE = reader["MDATE"] as DateTime? ?? null,
MUSER = reader["MUSER"] as Int16? ?? null
};
}
}
}
return(row);
}