protected override void WfCreateBoBasic()
{
BoMaster = new AdmBLL(LoginInfo.CompNo, TabMaster.TargetTable, TabMaster.TargetColumn, TabMaster.ViewTable);
BoBas = new BasBLL(BoMaster.OfGetConntion());
BoInv = new InvBLL(BoMaster.OfGetConntion());
return;
}
private string WfGetFieldMSDesc(AdmBLL pBoAdm, string pTableName, string pColumnName)
{
string rtnColumnName = "";
StringBuilder sbSql;
List <SqlParameter> sqlParmList;
try
{
sbSql = new StringBuilder();
sbSql.AppendLine("SELECT b.value");
sbSql.AppendLine("FROM INFORMATION_SCHEMA.COLUMNS a");
sbSql.AppendLine(" LEFT JOIN sys.extended_properties b ON b.name='MS_Description' AND b.major_id=object_id(a.TABLE_SCHEMA+'.'+a.TABLE_NAME)");
sbSql.AppendLine(" AND b.minor_id = a.ORDINAL_POSITION ");
sbSql.AppendLine("WHERE");
sbSql.AppendLine(" OBJECTPROPERTY(OBJECT_ID(a.TABLE_SCHEMA+'.'+a.TABLE_NAME), 'IsMsShipped')=0 ");
sbSql.AppendLine(" AND a.table_name=@table_name");
sbSql.AppendLine(" AND a.column_name=@column_name");
sqlParmList = new List <SqlParameter>();
sqlParmList.Add(new SqlParameter("@table_name", pTableName));
sqlParmList.Add(new SqlParameter("@column_name", pColumnName));
rtnColumnName = GlobalFn.isNullRet(pBoAdm.OfGetFieldValue(sbSql.ToString(), sqlParmList.ToArray()), "");
return(rtnColumnName);
}
catch (Exception ex)
{
throw ex;
}
}
protected override void WfCreateBoBasic()
{
//tabMaster.strBLLClassName = "YR.ERP.BLL.MSSQL.CommonBLL";
BoMaster = new AdmBLL(LoginInfo.CompNo, TabMaster.TargetTable, TabMaster.TargetColumn, TabMaster.ViewTable);
BoAdm = new AdmBLL(BoMaster.OfGetConntion());
return;
}
//原始檔保留
//private void WfGenAtb(string pTableName)
//{
// atb_tb l_atb;
// List<atc_tb> l_atcs;
// try
// {
// YREntities yrContent = new YREntities();
// l_atb = new atb_tb();
// l_atb.atb01 = pTableName;
// l_atb.atb02 = WfGetTbMSDesc(pTableName);
// l_atb.atb03 = "";
// yrContent.atb_tb.Add(l_atb);
// l_atcs = WfGetAtcAll(pTableName);
// if (l_atcs != null)
// yrContent.atc_tb.AddRange(l_atcs);
// yrContent.SaveChanges();
// }
// catch (Exception ex)
// {
// throw ex;
// }
//}
#endregion
#region WfGetAtcAll 取得明細 atc_tb list
private List <atc_tb> WfGetAtcAll(AdmBLL pBoAdm, string pTableName)
{
StringBuilder sbSql;
DataTable dtBase;
List <SqlParameter> sqlParmList;
atc_tb atcModel;
List <atc_tb> atcList;
try
{
sbSql = new StringBuilder();
//sbSql.AppendLine("SELECT a.table_name,a.column_name,b.value");
//sbSql.AppendLine("FROM INFORMATION_SCHEMA.COLUMNS a");
//sbSql.AppendLine(" LEFT OUTER JOIN sys.extended_properties b ");
//sbSql.AppendLine(" ON b.major_id = OBJECT_ID(a.TABLE_SCHEMA+'.'+a.TABLE_NAME) ");
//sbSql.AppendLine(" AND b.minor_id = a.ORDINAL_POSITION AND b.name = 'MS_Description' ");
//sbSql.AppendLine("WHERE");
//sbSql.AppendLine(" a.table_name=@table_name");
sbSql.AppendLine("SELECT");
sbSql.AppendLine("a.name [table_name],b.name [column_name],c.value [Description]");
sbSql.AppendLine("FROM sys.tables a");
sbSql.AppendLine("INNER JOIN sys.columns b on a.object_id = b.object_id");
sbSql.AppendLine("LEFT JOIN sys.extended_properties c ON a.object_id = c.major_id AND b.column_id = c.minor_id AND c.name = 'MS_Description'");
sbSql.AppendLine("WHERE a.name = @table_name");
sqlParmList = new List <SqlParameter>();
sqlParmList.Add(new SqlParameter("@table_name", pTableName));
dtBase = pBoAdm.OfGetDataTable(sbSql.ToString(), sqlParmList.ToArray());
if (dtBase == null)
{
return(null);
}
atcList = new List <atc_tb>();
foreach (DataRow ldr in dtBase.Rows)
{
atcModel = new atc_tb();
atcModel.atc01 = GlobalFn.isNullRet(ldr["table_name"], "");
atcModel.atc02 = GlobalFn.isNullRet(ldr["column_name"], "");
atcModel.atc03 = GlobalFn.isNullRet(ldr["Description"], "");
atcModel.atc04 = "";
atcList.Add(atcModel);
}
return(atcList);
}
catch (System.Exception ex)
{
throw ex;
}
}
private void WfReUpdateDeatail(AdmBLL pBoAdm, string pTableName)
{
try
{
pBoAdm.TRAN = pBoAdm.OfGetConntion().BeginTransaction();
WfGenAtb(pBoAdm, pTableName, true);
pBoAdm.TRAN.Commit();
}
catch (Exception ex)
{
pBoAdm.TRAN.Rollback();
throw ex;
}
}
private string WfGetTbMSDesc(AdmBLL pBoAdm, string pTableName)
{
string rtnTableName = "";
StringBuilder sbSql;
try
{
sbSql = new StringBuilder();
sbSql.AppendLine("SELECT value FROM sys.extended_properties");
sbSql.AppendLine("WHERE");
sbSql.AppendLine(" name='MS_Description'");
sbSql.AppendLine(" and minor_id=0");
sbSql.AppendLine(string.Format(" and major_id=object_id('dbo.{0}')", pTableName));
rtnTableName = GlobalFn.isNullRet(pBoAdm.OfGetFieldValue(sbSql.ToString(), null), "");
return(rtnTableName);
}
catch (Exception ex)
{
throw ex;
}
}
//*****************************表單自訂Fuction****************************************
#region 以 YR資料庫來產生所有的資料來源
private void WfGenAllData(AdmBLL pBoAdm)
{
StringBuilder sbSql;
DataTable dtBase;
string tableName;
int chkCnts;
List <SqlParameter> sqlParmList;
try
{
pBoAdm.TRAN = pBoAdm.OfGetConntion().BeginTransaction();
sbSql = new StringBuilder();
sbSql.AppendLine("SELECT * FROM INFORMATION_SCHEMA.tables WHERE table_type='BASE TABLE'");
dtBase = pBoAdm.OfGetDataTable(sbSql.ToString());
if (dtBase == null)
{
return;
}
foreach (DataRow ldr in dtBase.Rows)
{
tableName = GlobalFn.isNullRet(ldr["table_name"], "");
sbSql = new StringBuilder();
sbSql.AppendLine("SELECT COUNT(1) FROM atb_tb WHERE atb01=@atb01");
sqlParmList = new List <SqlParameter>();
sqlParmList.Add(new SqlParameter("@atb01", tableName));
chkCnts = GlobalFn.isNullRet(pBoAdm.OfGetFieldValue(sbSql.ToString(), sqlParmList.ToArray()), 0);
if (chkCnts == 0)
{
WfGenAtb(pBoAdm, tableName, false);//不更新 atc_tb
}
}
pBoAdm.TRAN.Commit();
}
catch (Exception ex)
{
pBoAdm.TRAN.Rollback();
throw ex;
}
}
protected override void WfCreateBoBasic()
{
BoMaster = new AdmBLL(LoginInfo.CompNo, TabMaster.TargetTable, TabMaster.TargetColumn, TabMaster.ViewTable);
return;
}
//檢查登入角色是否有執行該程式的權限
protected override bool WfAppendUpdate()
{
DataTable dtAddTb;
AdmBLL boAppend;
StringBuilder sbSql;
vw_admi600 masterModel = null;
List <SqlParameter> sqlParmList = null;
try
{
masterModel = DrMaster.ToItem <vw_admi600>();
boAppend = new AdmBLL(BoMaster.OfGetConntion());
boAppend.TRAN = BoMaster.TRAN;
boAppend.OfCreateDao("add_tb", "*", "");
sbSql = new StringBuilder();
sbSql.AppendLine("SELECT * FROM add_tb");
sbSql.AppendLine("WHERE add01=@add01");
sbSql.AppendLine("AND add02=@add02");
sqlParmList = new List <SqlParameter>();
sqlParmList.Add(new SqlParameter("@add01", LoginInfo.UserRole));
sqlParmList.Add(new SqlParameter("@add02", masterModel.ado01));
dtAddTb = boAppend.OfGetDataTable(sbSql.ToString(), sqlParmList.ToArray());
if (masterModel.ado07.ToUpper() == "M")//menus 時
{
if (dtAddTb != null && dtAddTb.Rows.Count > 0)
{
dtAddTb.Rows[0].Delete();
if (boAppend.OfUpdate(dtAddTb) < 1)
{
WfShowErrorMsg("刪除權限資料檔(add_tb)失敗!");
return(false);
}
}
}
else //報表或程式時
{
if (dtAddTb.Rows.Count == 0 && GlobalFn.isNullRet(masterModel.ado16, "") != "Y")
{
var result = WfShowConfirmMsg("無此程式的權限,請問是否自動新增 ?");
//var i = WfShowConfirmMsg("無此程式的權限,請問是否自動新增");
//if (i == 1)
if (result == DialogResult.Yes)
{
var drNew = dtAddTb.NewRow();
drNew["add01"] = LoginInfo.UserRole;
drNew["add02"] = masterModel.ado01;
drNew["add03"] = "Y";
drNew["add04"] = "Y";
drNew["add05"] = "Y";
drNew["add06"] = "Y";
drNew["add07"] = "Y";
drNew["add08"] = "Y";
drNew["add09"] = "Y";
drNew["add10"] = "Y";
drNew["add11"] = "Y";
drNew["add12"] = "Y";
dtAddTb.Rows.Add(drNew);
if (boAppend.OfUpdate(dtAddTb) < 1)
{
WfShowErrorMsg("刪除權限資料檔(add_tb)失敗!");
return(false);
}
}
}
}
return(true);
}
catch (Exception ex)
{
throw ex;
}
}
protected override void WfCreateBoBasic()
{
BoMaster = new AdmBLL(LoginInfo.CompNo, TabMaster.TargetTable, TabMaster.TargetColumn, TabMaster.ViewTable);
YRAdmBll = new AdmBLL(); //指向Y00 不共用transaction
return;
}
private void WfGenAtb(AdmBLL pBoAdm, string pTableName, bool reUpdateAtc)
{
StringBuilder sbSql;
List <SqlParameter> sqlParmList;
DataTable dtAtb, dtAtc;
DataRow drAtb, drAtc;
List <atc_tb> atcTableSchemaList; //從db schema 截取來源
List <atc_tb> atcList; //從atc_tb 截取來源
int chkCnts = 0;
try
{
pBoAdm.OfCreateDao("atb_tb", "*", "atb_tb");
sbSql = new StringBuilder();
sbSql.AppendLine("SELECT * FROM atb_tb");
sbSql.AppendLine("WHERE atb01=@atb01 ");
sqlParmList = new List <SqlParameter>();
sqlParmList.Add(new SqlParameter("@atb01", pTableName));
dtAtb = pBoAdm.OfGetDataTable(sbSql.ToString(), sqlParmList.ToArray());
if (dtAtb.Rows.Count == 0)
{
drAtb = dtAtb.NewRow();
drAtb["atb01"] = pTableName;
drAtb["atb02"] = WfGetTbMSDesc(pBoAdm, pTableName);
drAtb["atb03"] = "";
drAtb["atbsecu"] = LoginInfo.UserNo;
drAtb["atbsecg"] = LoginInfo.GroupNo;
drAtb["atbcreu"] = LoginInfo.UserNo;
drAtb["atbcreg"] = LoginInfo.DeptNo;
drAtb["atbcred"] = Now;
dtAtb.Rows.Add(drAtb);
pBoAdm.OfUpdate(dtAtb);
}
//更新資料表明細
atcTableSchemaList = WfGetAtcAll(pBoAdm, pTableName);
pBoAdm.OfCreateDao("atc_tb", "*", "atc_tb");
sbSql = new StringBuilder();
sbSql.AppendLine("SELECT * FROM atc_tb");
sbSql.AppendLine("WHERE atc01=@atc01 ");
sqlParmList = new List <SqlParameter>();
sqlParmList.Add(new SqlParameter("@atc01", pTableName));
dtAtc = pBoAdm.OfGetDataTable(sbSql.ToString(), sqlParmList.ToArray());
atcList = dtAtc.ToList <atc_tb>();
foreach (atc_tb atcModel in atcTableSchemaList)
{
chkCnts = atcList.Where(p => p.atc02 == atcModel.atc02)
.Count();
if (chkCnts > 0)
{
if (reUpdateAtc == true)
{
var drAtcs = dtAtc.Select(string.Format("atc02='{0}'", atcModel.atc02));
drAtcs[0]["atc03"] = atcModel.atc03;
//drAtcs[0]["atc04"] = atcModel.atc04; //額外功能要保留
drAtcs[0]["atcmodu"] = LoginInfo.UserNo;
drAtcs[0]["atcmodg"] = LoginInfo.DeptNo;
drAtcs[0]["atcmodd"] = Now;
continue;
}
else
{
continue;
}
}
else
{
drAtc = dtAtc.NewRow();
drAtc["atc01"] = atcModel.atc01;
drAtc["atc02"] = atcModel.atc02;
drAtc["atc03"] = atcModel.atc03;
drAtc["atc04"] = atcModel.atc04;
drAtc["atccreu"] = LoginInfo.UserNo;
drAtc["atccreg"] = LoginInfo.DeptNo;
drAtc["atccred"] = Now;
dtAtc.Rows.Add(drAtc);
}
}
pBoAdm.OfUpdate(dtAtc);
}
catch (Exception ex)
{
throw ex;
}
}
