/// <summary>
/// 获取权限菜单
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="userCode"></param>
/// <returns></returns>
public static IEnumerable <MenuRolesModel> GetAuthorizationMenuInfo(string userCode, string roleCode)
{
string sql = @"Select mr.RoleCode,m.MenuCode,MenuName,MenuIcon,ParentCode,MenuPath,Component,Title,AlwaysShow
From Menus m
Inner Join MenusRole mr on m.MenuCode = mr.MenuCode
Inner Join Users u on u.RoleCode = mr.RoleCode
Where mr.RoleCode=@RoleCode And u.UserCode=@UserCode";
var param = new DynamicParameters();
param.Add("@RoleCode", roleCode);
param.Add("@UserCode", userCode);
IEnumerable <MenuRolesModel> menuRoles = AppDataBase.Query <MenuRolesModel>(sql, param);
IEnumerable <MenuRolesModel> menuRolesModels = from x in menuRoles where object.Equals(x.ParentCode, null) select x;
foreach (MenuRolesModel item in menuRolesModels)
{
item.Children = (from x in menuRoles where x.ParentCode == item.MenuCode select x);
}
return(menuRolesModels);
}
public GetExportData GetExportData(string objectCode)
{
GetExportData getExportData = new GetExportData();
using (IDbConnection dbConnection = (new AppDataBase()).connection)
{
dbConnection.Open();
// IDbTransaction transaction = dbConnection.BeginTransaction();
try
{
string sql = @"select * from V_FLC_OBJECTPROPERTY where obj_code = '" + objectCode + @"' and lan = 'zn_CN'
order by obj_table ,obj_pro_using desc,obj_pro_null desc ";
getExportData.v_FLC_s = AppDataBase.Query <V_FLC_OBJECTPROPERTY>(sql, null, null, dbConnection);
string sql2 = @"select *from flc_objects objs
left join flc_lang lan on lan.key=objs.obj_code||'.'||objs.obj_table
where obj_code='" + objectCode + "' and lan='zn_CN' and is_main=0 ";
getExportData.objectsUnions = AppDataBase.Query <ObjectsUnionLang>(sql2, null, null, dbConnection);
}
catch (Exception e)
{
throw e;
}
}
return(getExportData);
}
private static void Update_FLC_Lang(IDbCommand dbCommand, string key, string zn_CN, string en_US, IDbTransaction dbTransaction = null)
{
string sql = "delete FLC_LANG where key=:key";
var param = new DynamicParameters();
param.Add(":key", key);
AppDataBase.Execute(sql, param, dbTransaction);
sql = "Insert into FLC_LANG(key,value,lan) values (:key,:zn_CN,:en_US)";
//FLC_LANG中value列设置不允许为空，如果为空则不插入
if (!(zn_CN == null || zn_CN == string.Empty))
{
sql = "Insert into FLC_LANG(key,value,lan) values (:key,:zn_CN,:en_US)";
param.Add(":zn_CN", zn_CN);
param.Add(":en_US", "zn_CN");
AppDataBase.Execute(sql, param, dbTransaction);
}
if (!(en_US == null || en_US == string.Empty))
{
param = new DynamicParameters();
param.Add(":key", key);
param.Add(":zn_CN", en_US);
param.Add(":en_US", "En_US");
AppDataBase.Execute(sql, param, dbTransaction);
}
}
public IEnumerable <dynamic> GetObjectDataL()
{
IEnumerable <dynamic> dynamics = null;
using (IDbConnection dbConnection = (new AppDataBase()).connection)
{
dbConnection.Open();
try
{
string sql = @"Select Obj.Id,Obj.Obj_Code,Lng.Key,Lng.Value as Chinese,Flc.Value as English,
case Obj.Is_Enable when 1 then '是' when 0 then '否' end Is_Enable,
case Obj.Is_System when 1 then '是' when 0 then '否' end Is_System
From FLC_OBJECT Obj
Left Join FLC_LANG Lng On Lng.Key=Obj.Obj_Code And Lng.lan='zn_CN'
Left Join FLC_LANG Flc On Flc.Key=Lng.Key And Flc.lan='en_US'
where is_show='1'
Order By Obj.Obj_Code Asc";
dynamics = AppDataBase.Query <dynamic>(sql, null, null, dbConnection);
}
catch (Exception e)
{
throw e;
}
}
return(dynamics);
}
public IEnumerable <dynamic> GetProdclineDA(string opseq, string cinvcode)
{
IEnumerable <dynamic> GetProdclineDA = null;
using (IDbConnection dbConnection = (new AppDataBase()).connection)
{
dbConnection.Open();
try
{
string sql = @"select *from Data_PRODCLLIN_Item0 item0
inner join data_PRODCLLIN duc on item0.mid=duc.id
inner join data_INVENTORY inv on inv.cinvccode=duc.ccinvcode
where item0.opseq='" + opseq + "' and inv.cinvcode='" + cinvcode + @"' and versioncode=(select max(versioncode) from
(select *from Data_PRODCLLIN_Item0 item0
inner join data_PRODCLLIN duc on item0.mid=duc.id
inner join data_INVENTORY inv on inv.cinvccode=duc.ccinvcode
where item0.opseq='" + opseq + "' and inv.cinvcode='" + cinvcode + @"') t
)";
GetProdclineDA = AppDataBase.Query <dynamic>(sql, null, null, dbConnection);
}
catch (Exception e)
{
throw e;
}
}
return(GetProdclineDA);
}
public IEnumerable <V_FLC_MENU> GetObjectList(string lan)
{
IEnumerable <V_FLC_MENU> v_FLC_MENUs = null;
lan = string.IsNullOrEmpty(lan) ? "zn_CN" : lan;
using (IDbConnection dbConnection = (new AppDataBase()).connection)
{
dbConnection.Open();
try
{
string sql = @"select t1.* from v_FLC_MENU t1 where lan = :lan
and is_sys <> 1 and is_enable = 1 and is_show = 1 and is_from_model <> 0 order by disp_order";
var parm = new DynamicParameters();
parm.Add(":lan", lan);
v_FLC_MENUs = AppDataBase.Query <V_FLC_MENU>(sql, parm, null, dbConnection);
}
catch (Exception e)
{
throw e;
}
}
return(v_FLC_MENUs);
}
public GetProperty GetProperty(string obj_code)
{
GetProperty getExportData = new GetProperty();
using (IDbConnection dbConnection = (new AppDataBase()).connection)
{
dbConnection.Open();
// IDbTransaction transaction = dbConnection.BeginTransaction();
try
{
string sql = @"select * from V_FLC_OBJECTPROPERTY
where obj_code = :obj_code and lan = 'zn_CN' ";
var para = new DynamicParameters();
para.Add(":obj_code", obj_code);
getExportData.objects = AppDataBase.Query <V_FLC_OBJECTPROPERTY>(sql, para, null, dbConnection);
string sql2 = @"select *from flc_objects objs
left join flc_lang lan on lan.key=objs.obj_code||'.'||objs.obj_table
where obj_code=:obj_code and lan='zn_CN' and is_main=0 ";
getExportData.tables = AppDataBase.Query <ObjectsUnionLang>(sql2, para, null, dbConnection);
}
catch (Exception e)
{
throw e;
}
}
return(getExportData);
}
public void InstanceDB()
{
AppDataBase db = new AppDataBase();
Realms.Realm instance = db.GetInstanceSync();
ReceiptDao = new ReceiptDao(instance);
}
/// <summary>
/// 获取全部菜单权限
/// </summary>
/// <param name="role"></param>
/// <param name="lan"></param>
public GetAllAuthByRole GetAllAuthByRole(string role, string lan)
{
//尽管role没有用到，考虑客户端调用的时候 传值了，为了减少更改
//所有还是留着了
lan = string.IsNullOrEmpty(lan) ? "zn_CN" : lan;
GetAllAuthByRole getAllAuthByRole = new GetAllAuthByRole();
using (IDbConnection dbConnection = (new AppDataBase()).connection)
{
dbConnection.Open();
try
{
string sql = @"select * from v_FLC_MENU
where lan = :lan order by disp_order ";
var parm = new DynamicParameters();
parm.Add(":lan", lan);
getAllAuthByRole.dtNodes = AppDataBase.Query <V_FLC_MENU>(sql, parm, null, dbConnection);
sql = @"select t.*,t1.value from FLC_OBJ_OPERATION t
left join FLC_LANG t1 on t.operation_id = t1.key order by obj_code,page,btn_index";
getAllAuthByRole.dtOperation = AppDataBase.Query <FLC_OBJ_OPERATION>(sql, null, null, dbConnection);
}
catch (Exception e)
{
throw e;
}
}
return(getAllAuthByRole);
}
/// <summary>
/// 获取权限列表
/// </summary>
/// <returns></returns>
public static IEnumerable <RoleModel> GetRoles()
{
string sql = "Select AutoID,RoleCode,RoleName From Roles";
IEnumerable <RoleModel> roleModels = AppDataBase.Query <RoleModel>(sql);
return(roleModels);
}
public static string CrateObject_Drop(string obj_code)
{
//删除1.flc_object flc_objects flc_object_property flc_lang对应数据清除 2.对应表清除
string del_obj = "delete from flc_object where obj_code like '" + obj_code + "%'";
string del_objs = "delete from flc_objects where obj_code like '" + obj_code + "%'";
string del_obj_pro = "delete from flc_object_property where obj_code like '" + obj_code + "%'";
string del_lang = "delete from flc_lang where key like '" + obj_code + "%'";
string sql;
var param = new DynamicParameters();
using (IDbConnection dbConnection = AppDataBase.DbConection)
{
dbConnection.Open();
IDbTransaction transaction = dbConnection.BeginTransaction();
try
{
sql = "Select * from FLC_OBJECTS where obj_code = :obj_code";
param.Add(":obj_code", obj_code);
IEnumerable <Flc_Objects> obj_relations = AppDataBase.Query <Flc_Objects>(sql, param, transaction);
foreach (Flc_Objects item in obj_relations)
{
string[] args = item.Obj_Table.Split('_');
if (!(args[0] == "FLC"))
{
AppDataBase.Execute(del_obj, null, transaction);
AppDataBase.Execute(del_objs, null, transaction);
AppDataBase.Execute(del_obj_pro, null, transaction);
AppDataBase.Execute(del_lang, null, transaction);
string setable = "select count(1) coun from user_tables where table_name=:obj_table";
param = new DynamicParameters();
param.Add(":obj_table", item.Obj_Table);
int count = Convert.ToInt32(AppDataBase.ExecuteScalar(setable, dbConnection, param));
if (count > 0)
{
sql = "drop table " + item.Obj_Table;
AppDataBase.Execute(sql, null, transaction);
}
}
}
transaction.Commit();
}
catch (Exception e)
{
transaction.Rollback();
throw e;
}
}
return("ok");
}
public DbActor()
{
Context.System.Scheduler.ScheduleTellRepeatedly(AppDataBase.EventualConsistencyReloadingInterval, AppDataBase.EventualConsistencyReloadingInterval, Self, new CheckReHyDrationMessage(), Self);
Receive <CheckReHyDrationMessage>(_ =>
{
if (AppDataBase.TurnOnEventualConsistencyReloading)
{
foreach (var className in AppDataBase.GetAllAccessedClasses())
{
if (AppDataBase.HasRecentlyAccessed(className))
{
AppDataBase.ResetLastAccessUtc(className);
if (AppDataBase.DbActorFacts.ContainsKey(className))
{
DbReHydrationActorActor.Tell(new CheckReHyDrationMessage(className));
}
}
}
}
});
this.Receive <DbActorRequestMessage>(message =>
{
if (message.TransactionId == null || message.TransactionId.Count == 0)
{
return;
}
if (message.Operation == null)
{
RemoveAllStoredTransactionRoutes(message);
return;
}
string transactionSig = null;
transactionSig = GetTransactionSigIfTransactionIdIsPresentInTransactionRoute(message);
if (transactionSig == null && message.TransactionId.Count > 1)
{
transactionSig = CreateNewTransactionActor(transactionSig, message);
}
if (transactionSig != null)
{
_transactionActors[transactionSig].Forward(message);
}
else
{
var id = message.TransactionId[0];
if (!_actors.ContainsKey(id))
{
_actors.Add(id, Context.System.ActorOf(Props.Create(() => new DbPerIdActor(id, false))));
}
_actors[id].Forward(message);
}
});
}
/// <summary>
/// AppDataのインスタンスを取得する。
/// </summary>
/// <returns>AppDataのインスタンス</returns>
internal static AppData GetInstance()
{
if (null == AppDataBase <AppData> ._instance)
{
return(AppDataBase <AppData> .GetInstanceBase(AppCommon.GetAppPath() + FileName));
}
else
{
return(AppDataBase <AppData> .GetInstanceBase());
}
}
public void PublishNewMessageAsync(Message message)
{
string topic = string.Format("{0}/{1}", message.Receiver, PhoneNumber);
byte[] data = Encoding.UTF8.GetBytes(message.Text);
if (mqttClient.IsConnected)
{
mqttClient.Publish(topic, data, MqttMsgBase.QOS_LEVEL_EXACTLY_ONCE, true);
AppDataBase.SaveItemAsync(message);
}
}
/// <summary>
/// 通过用户编码获取菜单
/// </summary>
/// <param name="user_code"></param>
/// <param name="lan"></param>
/// <param name="bAdmin"></param>
/// <returns></returns>
public IEnumerable <V_FLC_MENU> GetMenuAuthByUser(string user_code, string lan, string bAdmin)
{
string sql = "";
lan = string.IsNullOrEmpty(lan) ? "zn_CN" : lan;
if (user_code == "admins_group")
{
bAdmin = "1";
}
IEnumerable <V_FLC_MENU> v_FLC_MENUs = null;
using (IDbConnection dbConnection = (new AppDataBase()).connection)
{
dbConnection.Open();
IDbTransaction transaction = dbConnection.BeginTransaction();
try
{
//查看该用户是否拥有管理员角色
sql = @" select count(1) from DATA_ROLE_USER where user_code='" + user_code + "' and role_code='admins_group'";
string count = AppDataBase.ExecuteScalar(sql, dbConnection, null, transaction).ToString();
if (count != "0")
{
bAdmin = "1";
}
var para = new DynamicParameters();
para.Add(":lan", lan);
para.Add(":user_code", user_code);
sql = @"select t1.* from FLC_MENU_AUTH t left join v_FLC_MENU t1 on t.menu_id = t1.menucode
where lan = :lan and t.role_code in (select role_code from DATA_ROLE_USER where user_code = :user_code)
and is_sys = 0 and is_enable = 1 and is_show=1 order by disp_order";
if (bAdmin == "1")
{
sql = @"select t1.* from v_FLC_MENU t1 where lan = :lan
and is_sys = 0 and is_enable = 1 and is_show=1 and is_admin=0 order by disp_order";
}
v_FLC_MENUs = AppDataBase.Query <V_FLC_MENU>(sql, para, transaction, dbConnection);
transaction.Commit();
}
catch (Exception e)
{
transaction.Rollback();
throw e;
}
}
return(v_FLC_MENUs);
}
/// <summary>
/// 根据流水表的主键直接获取流水号
/// 如果obj不存在则插入
/// 返回值 1 说明是新插入的流水行值
/// </summary>
/// <param name="obj">对象名</param>
/// <param name="connString">连接串</param>
/// <returns></returns>
public static int getSerialNumInt(string obj, IDbTransaction transaction = null, IDbConnection dbConnection = null)
{
int type = 1;
var par = new DynamicParameters();
par.Add("@keyCode", obj);
par.Add("@Type", type);
par.Add("@SerialNum", 0, DbType.Int32, ParameterDirection.Output);
par.Add("@SerialLength", 0, DbType.Int32, ParameterDirection.Output);
DynamicParameters Last_par = (DynamicParameters)AppDataBase.QueryStoredProcedure("GetSerialNum", par, transaction, dbConnection);
return(Last_par.Get <int>("@SerialNum"));
}
/// <summary>
/// 登录函数
/// </summary>
/// <param name="userModel"></param>
/// <returns></returns>
public static UsersModel GetUserModel(UserModel userModel)
{
string sql = @"Selecat usercode,password,username From flc_user Where UserCode=:UserCode";
var param = new DynamicParameters();
param.Add(":UserCode", userModel.UserCode);
UsersModel userModels = AppDataBase.QuerySingle <UsersModel>(sql, param);
userModels.ConvertDescription();
return(userModels);
}
public static string ChangePwd(string UserCode, string NewPwd)
{
try
{
string sql = "Update DATA_UA_USER set password = :newPwd where CUSER_ID = :userCode";
var param = new DynamicParameters();
param.Add(":newPwd", NewPwd);
param.Add(":userCode", UserCode);
int num = AppDataBase.Execute(sql, param);
return(num.ToString());
}
catch (Exception ex)
{
return(ex.Message);
}
}
/// <summary>
/// 对角色设置权限
/// </summary>
/// <param name="setAuthToRole"></param>
/// <returns></returns>
public string SetAuthToRole(SetAuthToRole setAuthToRole)
{
string Role = setAuthToRole.Role;
using (IDbConnection dbConnection = (new AppDataBase()).connection)
{
dbConnection.Open();
IDbTransaction transaction = dbConnection.BeginTransaction();
try
{
string sql = "Delete FLC_AUTHORIZATION_ROLE where role_code = :role_code";
var para = new DynamicParameters();
para.Add(":role_code", Role);
AppDataBase.Execute(sql, para, transaction, dbConnection);
sql = @"Insert into FLC_AUTHORIZATION_ROLE (id,obj_code, page, operation_id, role_code,menu_id,is_operation)
values(:id,:obj_code, :page, :operation_id, :role_code,:menu_id,:is_operation)";
foreach (FLC_AUTHORIZATION_ROLE dr in setAuthToRole.fLC_AUTHORIZATION_ROLEs)
{
int id = MethodGetSerial.getSerialNumInt("FLC_AUTHORIZATION_ROLE", transaction, dbConnection);
para = new DynamicParameters();
para.Add(":id", id);
para.Add(":obj_code", dr.OBJ_CODE);
para.Add(":page", dr.PAGE);
para.Add(":operation_id", dr.OPERATION_ID);
para.Add(":role_code", dr.ROLE_CODE);
para.Add(":menu_id", dr.MENU_ID);
para.Add(":is_operation", dr.IS_OPERATION);
AppDataBase.Execute(sql, para, transaction, dbConnection);
}
transaction.Commit();
}
catch (Exception e)
{
transaction.Rollback();
throw e;
}
}
return("ok");
}
public IEnumerable <dynamic> GetAdminRolebyUser(string user_code)
{
IEnumerable <dynamic> data_role = null;
using (IDbConnection dbConnection = (new AppDataBase()).connection)
{
dbConnection.Open();
try
{
string sql = @" select *from DATA_ROLE_USER where user_code='" + user_code + "' and role_code='admins_group'";//查看该用户是否拥有管理员角色
data_role = AppDataBase.Query <dynamic>(sql, null, null, dbConnection);
}
catch (Exception e)
{
throw e;
}
}
return(data_role);
}
/// <summary>
/// 设置对象操作给角色
/// </summary>
/// <param name="saveOperationAuthToRole"></param>
/// <returns></returns>
public string SaveOperationAuthToRole(SaveOperationAuthToRole saveOperationAuthToRole)
{
string role_code = saveOperationAuthToRole.role_code;
using (IDbConnection dbConnection = (new AppDataBase()).connection)
{
dbConnection.Open();
IDbTransaction transaction = dbConnection.BeginTransaction();
try
{
string sql = "";
var parm = new DynamicParameters();
sql = "Delete FLC_OPERATION_AUTH where role_code = :role_code";
parm.Add(":role_code", role_code);
AppDataBase.Execute(sql, parm, transaction, dbConnection);
sql = @"Insert into FLC_OPERATION_AUTH(id, obj_code, page, operation_id, role_code)
values(:id, :obj_code, :page, :operation_id, :role_code)";
foreach (var dr in saveOperationAuthToRole.fLC_OPEERATION_AUTHs)
{
int id = MethodGetSerial.getSerialNumInt("FLC_OPERATION_AUTH", transaction, dbConnection);
parm.Add(":id", id);
parm.Add(":obj_code", dr.OBJ_CODE);
parm.Add(":page", dr.PAGE);
parm.Add(":operation_id", dr.OPERATION_ID);
AppDataBase.Execute(sql, parm, transaction, dbConnection);
}
transaction.Commit();
}
catch (Exception e)
{
transaction.Rollback();
throw e;
}
}
return("ok");
}
public static Isexist_Obj isexist_Obj(string obj_code, string Language)
{
try
{
string is_exit = "select * from flc_object where obj_code='" + obj_code + "'";
object o = AppDataBase.ExecuteScalar(is_exit);
if (o == null || o.ToString() == string.Empty)
{
is_exit = "1";
}
else
{
is_exit = "0";
}
string all_object = @"select obj_code,value from flc_object t left join flc_lang e on
t.obj_code=e.key and e.lan='" + Language + "'";
List <Allobj> allobjs = AppDataBase.Query <Allobj>(all_object).ToList();
Allobj allobj = new Allobj();
allobj.Obj_Code = obj_code;
allobj.Value = "当前对象";
allobjs.Insert(0, allobj);
foreach (Allobj item in allobjs)
{
if (item.Value == "")
{
item.Value = item.Obj_Code;
}
}
Isexist_Obj isexist_Obj = new Isexist_Obj();
isexist_Obj.is_exit = is_exit;
isexist_Obj.allobj = allobjs;
return(isexist_Obj);
}
catch (Exception e)
{
throw e;
}
}
public void ClientMqttMsgPublishReceived(object sender, MqttMsgPublishEventArgs e)
{
string receivedMessage = Encoding.Default.GetString(e.Message);
string[] topicLevels = e.Topic.Split('/');
if (topicLevels.Length == 2)
{
string messageSender = topicLevels[1];
Message message = new Message()
{
Text = receivedMessage,
Sender = messageSender,
Receiver = PhoneNumber,
CreatedTime = DateTime.Now,
Status = MessageStatus.NotRead
};
AppDataBase.SaveItemAsync(message);
MessagingCenter.Send(this, "newMessageReceived", message);
}
}
public IEnumerable <FLC_MENU_AUTH> GetRoleMenuAuthByRole(string role_code)
{
IEnumerable <FLC_MENU_AUTH> fLC_MENU_s = null;
using (IDbConnection dbConnection = (new AppDataBase()).connection)
{
dbConnection.Open();
try
{
string sql = "select * from FLC_MENU_AUTH where role_code = :role_code";
var parm = new DynamicParameters();
parm.Add(":role_code", role_code);
fLC_MENU_s = AppDataBase.Query <FLC_MENU_AUTH>(sql, parm, null, dbConnection);
}
catch (Exception e)
{
throw e;
}
}
return(fLC_MENU_s);
}
/// <summary>
/// 通过角色编码获取菜单
/// </summary>
/// <param name="role_code"></param>
/// <param name="lan"></param>
/// <returns></returns>
public IEnumerable <V_FLC_MENU> GetMenuAuthByRole(string role_code, string lan)
{
string bAdmin = "0";
if (role_code == "admins_group")
{
bAdmin = "1";
}
lan = string.IsNullOrEmpty(lan) ? "zn_CN" : lan;
IEnumerable <V_FLC_MENU> v_FLC_MENUs = null;
using (IDbConnection dbConnection = (new AppDataBase()).connection)
{
dbConnection.Open();
IDbTransaction transaction = dbConnection.BeginTransaction();
try
{
string sql = @"select t1.* from FLC_MENU_AUTH t
left join v_FLC_MENU t1 on t.menu_id = t1.menucode
where lan = :lan and t.role_code = :role_code and menugrade = 0 order by disp_order";
if (bAdmin == "1")
{
sql = @"select t.* from v_FLC_MENU t where lan = :lan order by disp_order ";
}
var parm = new DynamicParameters();
parm.Add(":lan", lan);
parm.Add(":role_code", role_code);
v_FLC_MENUs = AppDataBase.Query <V_FLC_MENU>(sql, parm, transaction, dbConnection);
}
catch (Exception e)
{
throw e;
}
}
return(v_FLC_MENUs);
}
/// <summary>
/// 返回能够操作的角色列表
/// </summary>
/// <param name="user_code"></param>
/// <returns></returns>
public IEnumerable <dynamic> GetRoleListByUser(string user_code)
{
IEnumerable <dynamic> data_role = null;
using (IDbConnection dbConnection = (new AppDataBase()).connection)
{
dbConnection.Open();
try
{
string sql = " select * from data_role where role_code!='admins_group' ";
var parm = new DynamicParameters();
parm.Add(":user_code", user_code);
data_role = AppDataBase.Query <dynamic>(sql, parm, null, dbConnection);
}
catch (Exception e)
{
throw e;
}
}
return(data_role);
}
/// <summary>
/// 给角色设置菜单权限
/// </summary>
/// <param name="saveMenuAuthToRole"></param>
/// <returns></returns>
public string SaveMenuAuthToRole(SaveMenuAuthToRole saveMenuAuthToRole)
{
string role_code = saveMenuAuthToRole.role_code;
using (IDbConnection dbConnection = (new AppDataBase()).connection)
{
dbConnection.Open();
IDbTransaction transaction = dbConnection.BeginTransaction();
try
{
string sql = "";
var parm = new DynamicParameters();
sql = "Delete flc_menu_auth where role_code = :role_code";
parm.Add(":role_code", role_code);
AppDataBase.Execute(sql, parm, transaction, dbConnection);
sql = "Insert into flc_menu_auth(id, menu_id, role_code) values(:id, :menu_id, :role_code)";
foreach (FLC_MENU_AUTH item in saveMenuAuthToRole.fLC_MENU_AUTHs)
{
int id = MethodGetSerial.getSerialNumInt("flc_menu_auth", transaction, dbConnection);
parm.Add(":id", id);
parm.Add(":menu_id", item.MENU_ID);
AppDataBase.Execute(sql, parm, transaction, dbConnection);
}
transaction.Commit();
}
catch (Exception e)
{
transaction.Rollback();
throw e;
}
}
return("ok");
}
public static LoadMenuUnionCompany LoadMenu(string UserCode, string Language)
{
string sql = @"Select * from V_FLC_MENU where lan = :lan
and menucode in (select menu_id from FLC_MENU_AUTH where role_code
in (select role_code from DATA_ROLE_USER where user_code = :user_code)) and is_show=1 and is_admin in (0)
order by disp_order";
string sqladmins = @"select count(1) from data_role_user where role_code in('admins_group' ,'DATA-MANAGER') and user_code='" + UserCode + "'";
object o = AppDataBase.ExecuteScalar(sqladmins);
if (o != null && o.ToString() != "0")
{
sql = "select * from V_FLC_MENU where lan = :lan and is_show=1 and is_sys <> 1 and is_admin in (0,1) order by disp_order";
}
if (System.IO.File.Exists("admin"))
{
sql = @"select * from V_FLC_MENU where lan = :lan and is_show=1 order by disp_order";
}
var param = new DynamicParameters();
param.Add(":user_code", UserCode);
param.Add(":lan", Language);
IEnumerable <LoadMenu> menuRoles = AppDataBase.Query <LoadMenu>(sql, param);
sql = "select id,name from data_company where rownum=1";
Company company = AppDataBase.QuerySingle <Company>(sql);
LoadMenuUnionCompany loadMenuUnionCompany = new LoadMenuUnionCompany();
loadMenuUnionCompany.company = company;
loadMenuUnionCompany.loadMenu = menuRoles;
return(loadMenuUnionCompany);
}
public static string crateObject_alter(crateObject_alter crateObject_Alter)
{
try
{
using (IDbConnection dbConnection = AppDataBase.DbConection)
{
// dbConnection=new
dbConnection.Open();
IDbTransaction transaction = dbConnection.BeginTransaction();
try
{
#region 对象名称
Obj_Name obj_Name = crateObject_Alter.ObjectName;
if (obj_Name != null)
{
string obj_code = obj_Name.Obj_Code;
string zn_CN = obj_Name.zn_CN;
string en_US = obj_Name.en_US;
if (obj_Name.Is_Enable == "是")
{
obj_Name.Is_Enable = "1";
}
else
{
obj_Name.Is_Enable = "0";
}
int is_enable = Convert.ToInt32(obj_Name.Is_Enable);
string upobject = "update FLC_OBJECT set is_enable=:is_enable where obj_code = :obj_code";
var parm3 = new DynamicParameters();
parm3.Add(":is_enable", is_enable);
parm3.Add(":obj_code", obj_code);
AppDataBase.Execute(upobject, parm3, transaction);
//Update_FLC_Lang(obj_code, zn_CN, en_US, transaction);
}
#endregion
#region 对象表修改
List <ObjRelation> objRelations = crateObject_Alter.ObjRelation;
foreach (ObjRelation dr in objRelations)
{
int is_del = dr.is_del;
string obj_code = dr.obj_code;
string obj_table = dr.obj_table;
string zn_CN2 = dr.zn_CN;
string en_US2 = dr.en_US;
int is_main = dr.is_main;
//只做中英文修改 还有删除操作(flc_objects flc_obj_property flc_lang 中删除,删除表)
string signs = obj_code + "." + obj_table;
#region 除操作
if (is_del == 1)
{
//主表不删除
if (is_main == 0)
{
//flc_objects删除
string del_objects = "delete from FLC_OBJECTS where obj_code=:obj_code and obj_table=:obj_table";
//flc_obj_property删除
string delvalue_FlcObjPro = "delete from FLC_OBJECT_PROPERTY where obj_code=:obj_code and obj_table=:obj_table";
var parm1 = new DynamicParameters();
parm1.Add(":obj_code", obj_code);
parm1.Add(":obj_table", obj_table);
//flc_lang删除
string delvalue_FlcLang = "delete from FLC_LANG where key='" + signs + "'";
string sele_tb = "select count(*) from user_tables where table_name='" + obj_table + "'";
string drop_table = "drop table " + obj_table;
AppDataBase.Execute(del_objects, parm1, transaction);
AppDataBase.Execute(delvalue_FlcObjPro, parm1, transaction);
AppDataBase.Execute(delvalue_FlcLang, transaction);
//判断表是否存在
int i = Convert.ToInt32(AppDataBase.ExecuteScalar(sele_tb, null, transaction));
if (i > 0)
{
AppDataBase.Execute(drop_table, null, transaction);
}
else
{
throw new Exception("此表不存在 ");
}
}
}
#endregion
#region 修改操作或新增
else
{
// Update_FLC_Lang(signs, zn_CN2, en_US2, transaction);
string seobjects = "Select count(*) from FLC_OBJECTS where obj_code = :obj_code and obj_table=:obj_table";
var parm10 = new DynamicParameters();
parm10.Add(":obj_code", obj_code);
parm10.Add(":obj_table", obj_table);
string inobjects = "insert into FLC_OBJECTS(obj_code,obj_table,is_main,id) values (:obj_code,:obj_table,:is_main,:id)";
//不存在就插入
int count = Convert.ToInt32(AppDataBase.ExecuteScalar(seobjects, dbConnection, parm10, transaction));
if (count < 1)
{
int id = MethodGetSerial.getSerialNumInt("FLC_OBJECTS", transaction);
var parm11 = new DynamicParameters();
parm11.Add(":obj_code", obj_code);
parm11.Add(":obj_table", obj_table);
parm11.Add(":is_main", is_main);
parm11.Add(":id", id);
AppDataBase.Execute(inobjects, parm11);
//新增之后构建表
string creobjtab = "create table {0} (id number null,mid number null)";
creobjtab = string.Format(creobjtab, obj_table, transaction);
string setable = "select count(*) coun from user_tables where table_name=:obj_table";
var parm12 = new DynamicParameters();
parm12.Add(":obj_table", obj_table);
int i = Convert.ToInt32(AppDataBase.ExecuteScalar(setable, dbConnection, parm12, transaction));
if (count < 1)
{
AppDataBase.Execute(creobjtab, null, transaction);
}
}
}
#endregion
}
#endregion
#region 对象表属性修改
List <Flc_Object_Property> objproperty = crateObject_Alter.Objproperty;
foreach (Flc_Object_Property dr in objproperty)
{
#region 属性
string is_del = dr.Is_Del.ToString();
string obj_code = dr.Obj_Code.ToUpper();
string obj_table = dr.Obj_Table.ToUpper();
string obj_pro_code = dr.Obj_Pro_Code.ToUpper();
int obj_pro_type = Convert.ToInt32(dr.Obj_Pro_Type);
string obj_pro_length;
if (dr.Obj_Pro_Length.ToString() == null)
{
obj_pro_length = " ";
}
else
{
obj_pro_length = dr.Obj_Pro_Length.ToString();
}
int obj_pro_visible = dr.Obj_Pro_Visible;
int obj_pro_enable = dr.Obj_Pro_Enable;
int obj_pro_null = dr.Obj_Pro_Null;
if (dr.Obj_Pro_Default == null)
{
dr.Obj_Pro_Default = " ";
}
string obj_pro_default = dr.Obj_Pro_Default;
if (dr.Obj_Pro_Object == null)
{
dr.Obj_Pro_Object = " ";
}
string obj_pro_object = dr.Obj_Pro_Object;
string obj_pro_digit;
if (dr.Obj_Pro_Digit == null)
{
obj_pro_digit = " ";
}
else
{
obj_pro_digit = dr.Obj_Pro_Digit.ToString();
}
int obj_pro_using = dr.Obj_Pro_Using;
string zn_CN = dr.Zn_Cn;
string en_US = dr.en_Us;
string codes = obj_code + "." + obj_table + "." + obj_pro_code;
#endregion
#region 除操作
if (is_del == "1" || is_del == "true")
{
string sefopro = "Select count(*) from FLC_OBJECT_PROPERTY where obj_code=:obj_code and obj_pro_code=:obj_pro_code";
var parm3 = new DynamicParameters();
parm3.Add(":obj_code", obj_code);
parm3.Add(":obj_pro_code", obj_pro_code);
int count = Convert.ToInt32(AppDataBase.ExecuteScalar(sefopro, dbConnection, parm3, transaction));
if (count < 1)
{
continue;
}
else
{
//1.属性表中删除，2.语言表删除 3.对象表中删除
string delvalue_FlcObjPro = "delete from FLC_OBJECT_PROPERTY where obj_code=:obj_code and obj_pro_code=:obj_pro_code";
AppDataBase.Execute(delvalue_FlcObjPro, parm3, transaction);
string delvalue_FlcLang = "delete from FLC_LANG where key='{0}'";
AppDataBase.Execute(string.Format(delvalue_FlcLang, codes), null, transaction);
string delcolumn = "Alter table {0} drop column {1}";
AppDataBase.Execute(string.Format(delcolumn, obj_table, obj_pro_code), null, transaction);
}
}
#endregion
#region 新增或更新操作
else
{
#region 操作语言表
//Update_FLC_Lang(codes, zn_CN, en_US, transaction);
codes = string.Empty;
#endregion
string sefopro = "Select count(*) from FLC_OBJECT_PROPERTY where obj_code=:obj_code and obj_pro_code=:obj_pro_code";
string infopro = @"insert into FLC_OBJECT_PROPERTY values(:obj_code,:obj_table,:obj_pro_code,:obj_pro_type,:obj_pro_length,
:obj_pro_visible,:obj_pro_enable,:obj_pro_null,:obj_pro_default,:obj_pro_object,:obj_pro_digit,:obj_pro_using)";
string upfopro = @"update FLC_OBJECT_PROPERTY set obj_code=:obj_code,obj_table=:obj_table,obj_pro_code=:obj_pro_code,obj_pro_type=:obj_pro_type,obj_pro_length=:obj_pro_length,
obj_pro_visible=:obj_pro_visible,obj_pro_enable=:obj_pro_enable,obj_pro_null=:obj_pro_null,obj_pro_default=:obj_pro_default,obj_pro_object=:obj_pro_object,
obj_pro_digit=:obj_pro_digit,obj_pro_using=:obj_pro_using where obj_code=:obj_code and obj_pro_code=:obj_pro_code";
var parm3 = new DynamicParameters();
parm3.Add(":obj_code", obj_code);
parm3.Add(":obj_pro_code", obj_pro_code);
var parm4 = new DynamicParameters();
parm4.Add(":obj_code", obj_code);
parm4.Add(":obj_table", obj_table);
parm4.Add(":obj_pro_code", obj_pro_code);
parm4.Add(":obj_pro_type", obj_pro_type);
parm4.Add(":obj_pro_length", obj_pro_length);
parm4.Add(":obj_pro_visible", obj_pro_visible);
parm4.Add(":obj_pro_enable", obj_pro_enable);
parm4.Add(":obj_pro_null", obj_pro_null);
parm4.Add(":obj_pro_default", obj_pro_default);
parm4.Add(":obj_pro_object", obj_pro_object);
parm4.Add(":obj_pro_digit", obj_pro_digit);
parm4.Add(":obj_pro_using", obj_pro_using);
string typess = string.Empty;
switch (obj_pro_type)
{
case 1:
typess = "nvarchar2(" + obj_pro_length + ")";
break;
case 2:
typess = "nvarchar2(" + obj_pro_length + ")";
break;
case 3:
typess = "number(" + obj_pro_length + ")";
break;
case 4:
typess = "Date";
break;
case 5:
typess = "nvarchar2(60)";
break;
//case 6:
//typess = "nvarchar2(255)";
//break;
default:
typess = "nvarchar2(255)";
break;
}
int count = Convert.ToInt32(AppDataBase.ExecuteScalar(sefopro, dbConnection, parm3, transaction));
if (count < 1)
{
AppDataBase.Execute(infopro, parm4, transaction);
//判断表是否存在 不存在报错
string setable = "select count(*) coun from user_tables where table_name='" + obj_table + "'";
int i = Convert.ToInt32(AppDataBase.ExecuteScalar(setable, null, transaction));
if (i > 0)
{
//在对象表中添加该属性
string add = "alter table " + obj_table + " add " + obj_pro_code + " " + typess;
AppDataBase.Execute(add, null, transaction);
}
}
else
{
//更新之后对应语言表也要更新(已完成) 同时对象表也要更新修改
//此处修改对象表有1种 修改字段类型
string gettype = "select obj_pro_type from flc_object_property where obj_code=:obj_code and obj_pro_code=:obj_pro_code";
string getlength = "select obj_pro_length from flc_object_property where obj_code=:obj_code and obj_pro_code=:obj_pro_code";
var parm5 = new DynamicParameters();
parm5.Add(":obj_code", obj_code);
parm5.Add(":obj_pro_code", obj_pro_code);
int type = Convert.ToInt32(AppDataBase.ExecuteScalar(gettype, dbConnection, parm5, transaction));
string length = AppDataBase.ExecuteScalar(getlength, dbConnection, parm5, transaction).ToString();
if (length == "")
{
length = "0";
}
//更新属性表中值
AppDataBase.Execute(upfopro, parm4, transaction);
if (obj_pro_length == "")
{
obj_pro_length = "0";
}
if (type == obj_pro_type && Convert.ToInt32(length) >= Convert.ToInt32(obj_pro_length))
{
continue;
}
else
{
//此处异常会有很多
try
{
string alter = "alter table {0} modify {1} {2}";
alter = string.Format(alter, obj_table, obj_pro_code, typess);
AppDataBase.Execute(alter, null, transaction);
}
catch (Exception e)
{
throw new Exception(e.ToString());
}
}
}
}
}
#endregion
#endregion
}
catch (Exception e)
{
transaction.Rollback();
throw e;
}
transaction.Commit();
}
}
catch (Exception e)
{
throw e;
}
return("ok");
}
public static string crateObject_ADD(crateObject_alter crateObject_Alter)
{
try
{
using (IDbConnection dbConnection = (new AppDataBase()).connection)
{
dbConnection.Open();
IDbTransaction transaction = dbConnection.BeginTransaction();
try
{
#region 对象名称
Obj_Name obj_Name = crateObject_Alter.ObjectName;
if (obj_Name != null)
{
string obj_code = obj_Name.Obj_Code;
string zn_CN1 = obj_Name.zn_CN;
string en_US1 = obj_Name.en_US;
string is_enable = obj_Name.Is_Enable;
//Update_FLC_Lang(dbConnection, obj_code, zn_CN1, en_US1, transaction);
string sql = "delete FLC_OBJECT where obj_code = :obj_code";
var parm1 = new DynamicParameters();
parm1.Add(":obj_code", obj_code);
// AppDataBase.ExecuteScalar(sql, dbConnection, parm1, transaction);
sql = "select count(*) from FLC_OBJECT where obj_code = :obj_code";
string str = AppDataBase.ExecuteScalar(sql, dbConnection, parm1, transaction).ToString();
int id1 = MethodGetSerial.getSerialNumInt("FLC_OBJECT", transaction, dbConnection);
string inobject = "insert into FLC_OBJECT(id,obj_code,is_enable) values (:id,:obj_code,:is_enable)";
var parm2 = new DynamicParameters();
parm2.Add(":id", id1);
parm2.Add(":obj_code", obj_code);
parm2.Add(":is_enable", is_enable);
AppDataBase.Execute(inobject, parm2, transaction);
}
//取得流水号
#endregion
#region 对象表新增
List <ObjRelation> objRelations = crateObject_Alter.ObjRelation;
foreach (ObjRelation dr in objRelations)
{
string obj_code = dr.obj_code;
string obj_table = dr.obj_table;
string zn_CN2 = dr.zn_CN;
string en_US2 = dr.en_US;
int is_main = dr.is_main;
//只做中英文修改 还有删除操作(flc_objects flc_obj_property flc_lang 中删除,删除表)
string sign = obj_code + "." + obj_table;
// Update_FLC_Lang(sign, zn_CN2, en_US2,transaction);
#region 在FLC_OBJECTS新增对应数据
//在FLC_OBJECTS删除对应数据
string sql = "delete FLC_OBJECTS where obj_code = :obj_code and obj_table=:obj_table";
var parm3 = new DynamicParameters();
parm3.Add(":obj_code", obj_code);
parm3.Add(":obj_table", obj_table);
AppDataBase.ExecuteScalar(sql, dbConnection, parm3, transaction);
//在FLC_OBJECTS新增对应数据
sql = "insert into FLC_OBJECTS(obj_code,obj_table,is_main,id) values (:obj_code,:obj_table,:is_main,:id)";
//取得流水号
int id2 = MethodGetSerial.getSerialNumInt("FLC_OBJECTS", transaction);
var parm4 = new DynamicParameters();
parm4.Add(":obj_code", obj_code);
parm4.Add(":obj_table", obj_table);
parm4.Add(":is_main", is_main);
parm4.Add(":id", id2);
AppDataBase.Execute(sql, parm4, transaction);
#endregion
#region 构建对象表
//查找此表是否存在语句
string setable = "select count(*) coun from user_tables where table_name=:obj_table";
var parm5 = new DynamicParameters();
parm5.Add(":obj_table", obj_table);
//创建表语句
//主表
string creobjtab = "create table {0} (id number null)";
creobjtab = string.Format(creobjtab, obj_table);
//子表
string creobjtabs = "create table {0} (id number null,mid number null)";
creobjtabs = string.Format(creobjtabs, obj_table);
//查找此表是否存在 存在删除再创建。
int count = Convert.ToInt32(AppDataBase.ExecuteScalar(setable, dbConnection, parm5, transaction));
if (count > 0)
{
sql = "drop table " + obj_table;
AppDataBase.Execute(sql, null, transaction);
}
if (is_main == 1)
{
AppDataBase.Execute(creobjtab, null, transaction);
}
else
{
AppDataBase.Execute(creobjtabs, null, transaction);
}
#endregion
}
#endregion
#region 对象属性
List <Flc_Object_Property> objproperty = crateObject_Alter.Objproperty;
foreach (Flc_Object_Property dr in objproperty)
{
#region 属性
string obj_code = dr.Obj_Code.ToUpper();
string obj_table = dr.Obj_Table.ToUpper();
string obj_pro_code = dr.Obj_Pro_Code.ToUpper();
int obj_pro_type = Convert.ToInt32(dr.Obj_Pro_Type);
string obj_pro_length;
if (dr.Obj_Pro_Length.ToString() == null)
{
obj_pro_length = " ";
}
else
{
obj_pro_length = dr.Obj_Pro_Length.ToString();
}
int obj_pro_visible = dr.Obj_Pro_Visible;
int obj_pro_enable = dr.Obj_Pro_Enable;
int obj_pro_null = dr.Obj_Pro_Null;
if (dr.Obj_Pro_Default == null)
{
dr.Obj_Pro_Default = " ";
}
string obj_pro_default = dr.Obj_Pro_Default;
if (dr.Obj_Pro_Object == null)
{
dr.Obj_Pro_Object = " ";
}
string obj_pro_object = dr.Obj_Pro_Object;
string obj_pro_digit;
if (dr.Obj_Pro_Digit == null)
{
obj_pro_digit = " ";
}
else
{
obj_pro_digit = dr.Obj_Pro_Digit.ToString();
}
int obj_pro_using = dr.Obj_Pro_Using;
string zn_CN3 = dr.Zn_Cn;
string en_US3 = dr.en_Us;
#endregion
string codes = obj_code + "." + obj_table + "." + obj_pro_code;
//Update_FLC_Lang(codes, zn_CN3, en_US3,transaction);
#region 在FLC_OBJECT_PROPERTY插入数据
string sql = "delete FLC_OBJECT_PROPERTY where obj_code=:obj_code and obj_pro_code=:obj_pro_code";
var parm6 = new DynamicParameters();
parm6.Add(":obj_code", obj_code);
parm6.Add(":obj_pro_code", obj_pro_code);
AppDataBase.ExecuteScalar(sql, dbConnection, parm6, transaction);
string infopro = @"insert into FLC_OBJECT_PROPERTY values(:obj_code,:obj_table,:obj_pro_code,:obj_pro_type,:obj_pro_length,
:obj_pro_visible,:obj_pro_enable,:obj_pro_null,:obj_pro_default,:obj_pro_object,:obj_pro_digit,:obj_pro_using)";
var parm7 = new DynamicParameters();
parm7.Add(":obj_code", obj_code);
parm7.Add(":obj_table", obj_table);
parm7.Add(":obj_pro_code", obj_pro_code);
parm7.Add(":obj_pro_type", obj_pro_type);
parm7.Add(":obj_pro_length", obj_pro_length);
parm7.Add(":obj_pro_visible", obj_pro_visible);
parm7.Add(":obj_pro_enable", obj_pro_enable);
parm7.Add(":obj_pro_null", obj_pro_null);
parm7.Add(":obj_pro_default", obj_pro_default);
parm7.Add(":obj_pro_object", obj_pro_object);
parm7.Add(":obj_pro_digit", obj_pro_digit);
parm7.Add(":obj_pro_using", obj_pro_using);
AppDataBase.Execute(infopro, parm7, transaction);
#endregion
#region 在表中插入数据
//拼类型
string typess = string.Empty;
switch (obj_pro_type)
{
case 1:
typess = "nvarchar2(" + obj_pro_length + ")";
break;
case 2:
typess = "nvarchar2(" + obj_pro_length + ")";
break;
case 3:
typess = "number(" + obj_pro_length + ")";
break;
case 4:
typess = "Date";
break;
case 5:
typess = "nvarchar2(60)";
break;
default:
typess = "nvarchar2(255)";
break;
}
//在对象表中添加该属性
string add = "alter table " + obj_table + " add " + obj_pro_code + " {0}";
add = string.Format(add, typess);
AppDataBase.Execute(add, null, transaction);
#endregion
}
#endregion
transaction.Commit();
}
catch (Exception e)
{
transaction.Rollback();
// dbConnection.Close();
//transaction.Dispose();
throw e;
}
}
}
catch (Exception e)
{
throw e;
}
return("ok");
}
