public ActionResult SnNumber(string id, string topic = null, string param = null,
string displayLanguages = null, string isPdf = "0", string isWaiting = "0")
{
var table = DbDapper.RunDataTableSql(@"SELECT TOP(1) b.WfdWorkflowNodeId,c.AppPageId,b.TaskId,a.AreaCode FROM dbo.InstTask a
INNER JOIN dbo.InstProc b ON a.Id = b.TaskId
INNER JOIN dbo.WfdWorkflowNode c ON b.WfdWorkflowNodeId = c.Id
WHERE Sn = @SnNumber AND b.NodeType IN (0,1)
ORDER BY b.ProcTime DESC", new { SnNumber = id });
if (table.Rows.Count > 0)
{
return(Redirect("/SysPages/AppPage/?nodeId={0}&pageId={1}&taskId={2}&areaCode={3}&displayLanguages={4}¶m={5}&isPdf={6}&topoc={7}"
.Fill(table.Rows[0]["WfdWorkflowNodeId"].ToString(),
table.Rows[0]["AppPageId"].ToString(),
table.Rows[0]["TaskId"].ToString(),
table.Rows[0]["AreaCode"].ToString(),
displayLanguages,
param,
isPdf,
topic,
isWaiting)));
}
return(RedirectToAction("ErrorMessage", "Account", new { error = "该单号为历史单号，请到老系统中查询" }));
}
/// <summary>
/// 链接字符串测试
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btn_connect_Click(object sender, EventArgs e)
{
btn_connect.Enabled = false;
ReleaseBuildHelper rbh = new ReleaseBuildHelper();
var sb = new StringBuilder();
// 当前数据
try
{
DbDapper.RunDataTableSql("select 1");
sb.AppendLine("1.当前数据库连接成功!");
}
catch
{
sb.AppendLine("1.当前数据库连接失败!");
}
//目标数据
try
{
rbh.DbTargetQuery("select 1");
sb.AppendLine("2.目标数据库连接成功!");
}
catch
{
sb.AppendLine("2.目标数据库连接失败!");
}
btn_connect.Enabled = true;
MessageBox.Show(sb.ToString());
}
public ActionResult AppPage(string nodeId, string pageId, string jobId, string taskId, string procId,
string draftId, string areaCode, string topic = null, string isPrint = "0", string param = null,
string displayLanguages = null, string isPdf = "0", string isWaiting = "0")
{
if (string.IsNullOrWhiteSpace(pageId))
{
return(new JsonResult()
{
JsonRequestBehavior = JsonRequestBehavior.AllowGet, Data = "该链接无效，请检查单号或者页面是否存在。"
});
}
ViewBag.pageId = pageId;
ViewBag.jobId = jobId;
ViewBag.nodeId = nodeId;
ViewBag.taskId = taskId;
ViewBag.procId = procId;
ViewBag.draftId = draftId;
ViewBag.isPrint = isPrint;
ViewBag.areaCode = areaCode;
ViewBag.param = param;
ViewBag.isPdf = isPdf;
ViewBag.displayLanguages = displayLanguages;
ViewBag.topic = topic;
ViewBag.isWaiting = isWaiting;
var node = DbDapper.Get <WfdWorkflowNode>(nodeId);
ViewBag.nodeType = node?.Type;
return(View());
}
public virtual T Insert(T model)
{
var creationTime = model.GetType().GetProperty("CreationTime");
creationTime?.SetValue(model, DateTime.Now);
DbDapper.Insert <T>(model, DbConnection, DbTransaction);
return(model);
}
private void GetSyncScript()
{
var result = DbDapper.Query <string>(SyncInsertOrUpdateQuery);
var sb = new StringBuilder();
foreach (var str in result)
{
sb.AppendLine(str);
}
SyncSql = sb.ToString();
}
/// <summary>
/// 存在于表中
/// </summary>
/// <param name="columnName"></param>
/// <returns></returns>
private bool ColumnInTable(string columnName)
{
var tableSplited = TableName.Split('.');
var objectName = TableName;
if (tableSplited.Length != 1)
{
objectName = tableSplited[tableSplited.Length - 1];
}
var existInTable = @"SELECT * FROM sys.[columns] WHERE [object_id] = (SELECT TOP 1 [object_id] FROM sys.[objects] WHERE name = '{0}') AND NAME = N'{1}'"
.Fill(objectName, columnName);
return(DbDapper.Count(existInTable) > 0);
}
public ActionResult MplReport(string id, string param)
{
var table = DbDapper.RunDataTableSql(@"
SELECT TOP ( 1 )
b.WfdWorkflowNodeId ,
c.AppPageId ,
b.TaskId ,
a.AreaCode
FROM dbo.InstTask a
INNER JOIN dbo.InstProc b ON a.Id = b.TaskId
INNER JOIN dbo.WfdWorkflowNode c ON b.WfdWorkflowNodeId = c.Id
WHERE Sn = @SnNumber
AND b.NodeType IN ( 0, 1 )
ORDER BY b.ProcTime DESC", new { SnNumber = id });
return(Redirect(table.Rows.Count > 0 ? "/SysPages/AppPage/?nodeId={0}&pageId={1}&taskId={2}&areaCode={3}¶m={4}".Fill(table.Rows[0]["WfdWorkflowNodeId"].ToString(), table.Rows[0]["AppPageId"].ToString(), table.Rows[0]["TaskId"].ToString(), table.Rows[0]["AreaCode"].ToString(), param) : "/SysPages/AppPage/?nodeId={0}&pageId={1}&taskId={2}&areaCode={3}".Fill("", "", "", "")));
}
public void Analyze(bool needSql = false)
{
Table = DbDapper.RunDataTableSql(Query);
//获取表名
InitializeTableName();
//初始化列
InitializeColumns();
//初始化From后面的语句
InitializeFromString();
//获取查询语句
BuildQuery();
//获取同步脚本
if (needSql)
{
GetSyncScript();
}
}
public ServiceConfig(string code)
{
DataTable dt = DbDapper.RunDataTableSql("select * from base_if_config where code='" + code + "'");
if (dt.Rows.Count <= 0)
{
return;
}
DataRow dr = dt.Rows[0];
Url = dr["if_url"].ToString();
UserAccount = dr["if_user"].ToString();
Password = dr["if_password"].ToString();
ServiceName = dr["req_name"].ToString();
RequestName = dr["param_name"].ToString();
MethodName = dr["method_name"].ToString();
RespName = dr["resp_name"].ToString();
SAPIfCode = dr["sap_ifcode"].ToString();
SAPSender = dr["sap_sender"].ToString();
SAPReceiver = dr["sap_receiver"].ToString();
}
public ActionResult Mpl01Report(string nodeId, string pageId, string jobId, string taskId,
string procId, string draftId, string areaCode, string isPrint = "0", string param = null)
{
if (string.IsNullOrWhiteSpace(pageId))
{
return(new JsonResult()
{
JsonRequestBehavior = JsonRequestBehavior.AllowGet, Data = "该链接无效，请检查单号或者页面是否存在。"
});
}
ViewBag.pageId = pageId;
ViewBag.jobId = jobId;
ViewBag.nodeId = nodeId;
ViewBag.taskId = taskId;
ViewBag.procId = procId;
ViewBag.draftId = draftId;
ViewBag.isPrint = isPrint;
ViewBag.areaCode = areaCode;
var node = DbDapper.Get <WfdWorkflowNode>(nodeId);
ViewBag.nodeType = node?.Type;
return(View());
}
public virtual int Count(string where = "")
{
return(DbDapper.Count <T>(where, DbConnection, DbTransaction));
}
public virtual int Execute(string sql, object param = null)
{
return(DbDapper.ExecuteNonQuery(sql, param, Connection, Transaction));
}
public virtual int SoftDelete <TModel>(TModel entity) where TModel : CreationAndDeletionEntity
{
entity.MarkDeleted();
return(DbDapper.Update(entity, DbConnection, DbTransaction));
}
public virtual T Update(T cate)
{
DbDapper.Update(cate, DbConnection, DbTransaction);
return(cate);
}
public virtual List <T> GetAll(object where = null)
{
return(DbDapper.GetList <T>(where, DbConnection, DbTransaction));
}
public virtual PagedList <T> GetPagedList(IPaging pageInput, string where = null)
{
return(DbDapper.GetPagedList <T>(pageInput, where, DbConnection, DbTransaction));
}
public virtual DataTable GetDataTable(string sql, object param = null)
{
return(DbDapper.RunDataTableSql(sql, param, DbConnection, DbTransaction));
}
public virtual TModel QueryFirst <TModel>(string sql, object param = null)
{
return(DbDapper.Query <TModel>(sql, param, DbConnection, DbTransaction).FirstOrDefault());
}
public virtual T First(string where = null)
{
return(DbDapper.First <T>(where, DbConnection, DbTransaction));
}
public virtual bool Any(string where = "")
{
return(DbDapper.Any <T>(where, DbConnection, DbTransaction));
}
public virtual bool Any(object whereCondition)
{
return(DbDapper.Any <T>(whereCondition, DbConnection, DbTransaction));
}
public virtual PagedList <TModel> Query <TModel>(string sql, IPaging input, object param = null)
{
return(DbDapper.Query <TModel>(sql, input, param, DbConnection, DbTransaction));
}
public virtual int Count(string sql, object param)
{
return(DbDapper.Count(sql, param, DbConnection, DbTransaction));
}
public virtual void Execute(string sql, object param = null)
{
DbDapper.ExecuteNonQuery(sql, param, DbConnection, DbTransaction);
}
public virtual T Get(string id)
{
return(DbDapper.Get <T>(id, DbConnection, DbTransaction));
}
public virtual PagedDatatable GetDataTableNoOrder(string sql, IPaging input, object param = null)
{
return(DbDapper.RunDataTableSqlNoOrder(sql, input, param, DbConnection, DbTransaction));
}
public virtual int Delete(string id)
{
return(DbDapper.Delete <T>(id, DbConnection, DbTransaction));
}
private static void Main(string[] args)
{
AppDomain.CurrentDomain.UnhandledException +=
(sender, eventArgs) =>
{
IocManager.Instance.Resolve <ILogger>()
.Error(sender.ToString() + eventArgs.ExceptionObject.ToString());
};
MiniAbp.StartWithSqlServer(
"Data Source = 136.17.76.54;Initial Catalog=Yfvic.Bpm0321;Persist Security Info=true;User ID=sa;PWD=Abcd1234;Packet Size=4096;");
// DbDapper.RunDataTableSql("");
string allTableSql = @"--查找所涉及的表及字段
SELECT TableName = s.SchemaName ,
ColumnName = c.SchemaName ,
c.WhereInputType ,
c.WhereInputContent,
CASE WHEN ISNULL(s.ParentBusinessTableId, '') = ''
THEN 1
ELSE
0
END AS IsMainTable
FROM dbo.AppBusinessTableColumn c
INNER JOIN ( SELECT t.Id ,
t.SchemaName,
t.ParentBusinessTableId
FROM dbo.WfdWorkflowNode n
INNER JOIN dbo.AppPage p ON n.AppPageId = p.Id
INNER JOIN dbo.AppBusinessTable t ON p.TableIds LIKE '%'
+ t.Id + '%'
WHERE n.Id = '{{NodeId}}'
) s ON s.Id = c.BusinessTableId
WHERE ISNULL(c.WhereInputType, '') <> ''";
string buildTaskSql = @"
--创建表
IF NOT EXISTS (SELECT * FROM sys.objects o WHERE o.name = '{{TableName}}')
BEGIN
CREATE TABLE [dbo].[{{TableName}}](
[Id] [NVARCHAR](50) NOT NULL,
[LanguageCulture] [NVARCHAR](50) NOT NULL,
[TaskId] [NVARCHAR](50) NOT NULL,
[GridOrder] [INT] NULL,
CONSTRAINT [PK_dbo.{{TableName}}] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END ";
var buildColumnSql = @"
--字段不存在则生成字段
IF NOT EXISTS(SELECT * FROM sys.[columns] c INNER JOIN sys.[objects] o ON o.[object_id] = c.[object_id] WHERE c.name = '{{ColumnName}}' AND o.name = '{{TableName}}')
BEGIN
ALTER TABLE dbo.[{{TableName}}] ADD [{{ColumnName}}] NVARCHAR(4000) NULL
END";
//查询并生成多语言 数据表和字段
var allTableColumn =
DbDapper.Query <TableColName>(allTableSql.Replace("{{NodeId}}", "14bf7911-a7d9-4623-9a19-cbe60d0c04e4"));
var allTable = allTableColumn.Select(r => r.TableName).Distinct();
foreach (var t in allTable)
{
//生成表
var newTbName = t + "_$lang";
var buildTb = buildTaskSql.Replace("{{TableName}}", newTbName);
DbDapper.ExecuteNonQuery(buildTb);
//生成所有字段
var allColumns = allTableColumn.Where(r => r.TableName == t);
foreach (var col in allColumns)
{
var buildCol = buildColumnSql.Replace("{{TableName}}", newTbName);
buildCol = buildCol.Replace("{{ColumnName}}", col.ColumnName);
DbDapper.ExecuteNonQuery(buildCol);
}
}
var addOrUpdateColumnsSql = @"
--蓄水池
--Id/Text
IF NOT EXISTS(SELECT * FROM dbo.{{TableName}} l WHERE l.TaskId = '{{TaskId}}'AND ('{{GridOrder}}' = '' OR '{{GridOrder}}' = l.GridOrder AND l.LanguageCulture = '{{LanguageCulture}}')
BEGIN
INSERT INTO dbo.yfvic_fin05_quot_$lang(Id ,LanguageCulture, TaskId, GridOrder, {{Columns}})VALUES (NEWID(), '{{LanguageCulture}}', '{{TaskId}}', '{{ColumnValues}}')
END
ELSE
BEGIN
UPDATE dbo.{{TableName}} SET {{ColumnPair}} WHERE TaskId = '{{TaskId}}'AND ('{{GridOrder}}' = '' OR '{{GridOrder}}' = GridOrder AND LanguageCulture = '{{LanguageCulture}}'
END ";
var executeSql = addOrUpdateColumnsSql;
executeSql = executeSql.Replace("{{TableName}}", "");
executeSql = executeSql.Replace("{{TaskId}}", "");
executeSql = executeSql.Replace("{{LanguageCulture}}", "");
executeSql = executeSql.Replace("{{GridOrder}}", "");
executeSql = executeSql.Replace("{{Columns}}", "");
executeSql = executeSql.Replace("{{ColumnValues}}", "");
executeSql = executeSql.Replace("{{ColumnPair}}", "");
DbDapper.ExecuteNonQuery(executeSql);
}
public virtual int Delete(T entity)
{
return(DbDapper.Delete <T>(entity, DbConnection, DbTransaction));
}
