void QueryUserList(UnitParameter up, dynamic sql)
{
sql.sql = @"select a.*,c.CodeText as StoreName,
d.UserUID as RecommendUID,e.[UserAccountNo] as RecommendAccountNo,
e.[UserMobile] as recommendMobile, e.[UserName] as RecommendName,f.CodeText as RecommededStoreName
from [UserInfo] a
left join CodeDictionary c on c.CodeValue=a.BelongToStore
join [Map_Recommend] d on a.UserUID=d.UserUID
join [UserInfo] e on e.UserUID=d.[RecommededUID]
left join CodeDictionary f on f.CodeValue=e.BelongToStore";
var where = "";
if (ComFunc.nvl(up.GetValue("UserID")) != "")
{
where += "(a.UserMobile like '%'+@UserID+'%' or a.UserAccountNo like '%'+@UserID+'%')";
}
if (ComFunc.nvl(up.GetValue("RecommendedID")) != "")
{
where += (where == ""?"":" and ") + "(e.UserMobile like '%'+@RecommendedID+'%' or e.UserAccountNo like '%'+@RecommendedID+'%')";
}
where = where == "" ? "" : " where " + where;
sql.sql += where;
}
void addUserInfo(UnitParameter up, dynamic sql)
{
sql.sql = @"INSERT INTO [UserInfo]
([UserUID]
,[UserAccountNo]
,[UserMobile]
,[UserName]
,[UserSex]
,[UserRealName]
,[UserRealID]
,[UserType]
,[BelongToStore])
VALUES
(@UserUID
,@UserAccountNo
,@UserMobile
,@UserName
,@UserSex
,@UserRealName
,@UserRealID
,@UserType
,@BelongToStore)";
if (ComFunc.nvl(up.GetValue("RecommededUID")) != "")
{
sql.sql += @"delete from Map_Recommend where UserUID=@UserUID and RecommededUID=@RecommededUID
insert into Map_Recommend(UserUID,RecommededUID)values(@UserUID,@RecommededUID)";
}
}
void sample_queryByPage(UnitParameter up, dynamic sql)
{
if (ComFunc.nvl(up.GetValue("prod_code_custs")) == "")
{
sql.sql = @"select a.* from hw_po_line a join hw_po b on a.hw_contract_no=b.hw_contract_no where a.quantity>ifnull(a.freeze_quantity,0)+ifnull(a.used_quantity,0) and b.status='Signed' and b.is_valid_ebs=1 ";
}
else
{
sql.presql = @"call PROCEDURE_split(@prod_code_custs,',');";
sql.sql = @"select a.* from hw_po_line a join hw_po b on a.hw_contract_no=b.hw_contract_no where a.quantity>ifnull(a.freeze_quantity,0)+ifnull(a.used_quantity,0) and b.status='Signed' and b.is_valid_ebs=1 and a.prod_code_cust in (select * from splittable)";
}
sql.orderby = @"hw_contract_no,po_line_no";
}
public DataCollection DoOperate(ParameterStd p)
{
string flag = p.GetValue <string>("_unit_action_flag_");
//预执行
T t = (T)Activator.CreateInstance(typeof(T), true);
UnitParameter up = (UnitParameter)p;
UnitDataCollection rtn = new UnitDataCollection();
if (up.Dao is ADBAccess)
{
var sqlobj = t.GetSqlFunc(flag)(up);
ADBAccess dba = (ADBAccess)up.Dao;
string regstr = "";
string regexpress = @"(?isx)
(') #开始标记“<tag...>”
(?> #分组构造，用来限定量词“*”修饰范围
\1 (?<Open>) #命名捕获组，遇到开始标记，入栈，Open计数加1
|\1 (?<-Open>) #狭义平衡组，遇到结束标记，出栈，Open计数减1
|[^']* #右侧不为开始或结束标记的任意字符
)
(?(Open)(?!)) #判断是否还有'OPEN'，有则说明不配对，什么都不匹配
\1 #结束标记“</tag>”
";
string tmpsql = "";
if (dba is OracleAccess)
{
regstr = @"(?<=:)[a-zA-Z0-9_]*\d*";
}
else
{
regstr = @"(?<=@)[A-Za-z0-9_]+\d*";
}
Regex re = new Regex(regstr);
Regex re2 = new Regex(regexpress);
try
{
if (!(sqlobj is FrameDLRObject))
{
throw new TypeRequiredException("需要指定的动态数据对象：FrameDLRObject");
}
string presql = sqlobj.presql;
DBAPageP dbc = new DBAPageP();
dba.BeginTransaction();
if (!string.IsNullOrEmpty(presql))
{
tmpsql = presql.Replace("''", "#sp#");
foreach (Match m in re2.Matches(tmpsql))
{
tmpsql = tmpsql.Replace(m.Value, "#sp#");
}
foreach (System.Text.RegularExpressions.Match m in re.Matches(tmpsql))
{
dbc.SQL_Parameters.Add(m.ToString(), up.GetValue(m.ToString()));
}
dba.ExecuteNoQuery(presql, dbc.SQL_Parameters);
}
//执行翻页查询
string sql = sqlobj.sql;
string orderby = sqlobj.orderby;
if (!string.IsNullOrEmpty(sql))
{
tmpsql = sql.Replace("''", "#sp#");
foreach (Match m in re2.Matches(tmpsql))
{
tmpsql = tmpsql.Replace(m.Value, "#sp#");
}
dbc.SQL_Parameters.Clear();
foreach (System.Text.RegularExpressions.Match m in re.Matches(tmpsql))
{
if (up.GetValue(m.ToString()) is byte[])
{
dbc.SQL_Parameters.Add(m.ToString(), up.GetValue(m.ToString()), System.Data.DbType.Binary);
}
else
{
dbc.SQL_Parameters.Add(m.ToString(), up.GetValue(m.ToString()));
}
}
dbc.SQL = sql;
dbc.OrderBy = orderby;
dbc.Count_of_OnePage = up.Count_Of_OnePage;
dbc.CurrentPage = up.CurrentPage;
dba.StartPageByCondition(dbc);
rtn.QueryTable = dba.GoToPage(up.ToPage);
rtn.Count_Of_OnePage = up.Count_Of_OnePage;
rtn.CurrentPage = dba.CurrentPage;
rtn.TotalPage = dba.TotalPage;
rtn.TotalRow = dba.TotalRow;
}
//收尾处理
string aftersql = sqlobj.aftersql;
if (!string.IsNullOrEmpty(aftersql))
{
tmpsql = aftersql.Replace("''", "#sp#");
foreach (Match m in re2.Matches(tmpsql))
{
tmpsql = tmpsql.Replace(m.Value, "#sp#");
}
dbc.SQL_Parameters.Clear();
foreach (System.Text.RegularExpressions.Match m in re.Matches(tmpsql))
{
dbc.SQL_Parameters.Add(m.ToString(), up.GetValue(m.ToString()));
}
dba.ExecuteNoQuery(aftersql, dbc.SQL_Parameters);
}
dba.CommitTransaction();
}
catch
{
if (dba != null)
{
dba.RollbackTransaction();
}
throw;
}
}
return(rtn);
}
public DataCollection DoOperate(ParameterStd p)
{
UnitDataCollection rtn = new UnitDataCollection();
string flag = p.GetValue <string>("_unit_action_flag_");
UnitParameter up = (UnitParameter)p;
if (up.Dao is ADBAccess)
{
T t = (T)Activator.CreateInstance(typeof(T), true);
var sqlobj = t.GetSqlFunc(flag)(up);
if (!(sqlobj is FrameDLRObject))
{
throw new TypeRequiredException("需要指定的动态数据对象：FrameDLRObject");
}
string sql = sqlobj.sql;
ADBAccess dba = (ADBAccess)up.Dao;
DBOParameterCollection dbc = new DBOParameterCollection();
if (!string.IsNullOrEmpty(sql))
{
string regstr = "";
regstr = @"(?<=" + (dba.ParameterFlagChar == "$"?"\\$": dba.ParameterFlagChar) + @")[A-Za-z0-9_]+\d*";
string regexpress = @"(?isx)
(') #开始标记“<tag...>”
(?> #分组构造，用来限定量词“*”修饰范围
\1 (?<Open>) #命名捕获组，遇到开始标记，入栈，Open计数加1
|\1 (?<-Open>) #狭义平衡组，遇到结束标记，出栈，Open计数减1
|[^']* #右侧不为开始或结束标记的任意字符
)
(?(Open)(?!)) #判断是否还有'OPEN'，有则说明不配对，什么都不匹配
\1 #结束标记“</tag>”
";
Regex re = new Regex(regstr);
string tmpsql = "";
Regex re2 = new Regex(regexpress);
tmpsql = sql.Replace("''", "#sp#");
foreach (Match m in re2.Matches(tmpsql))
{
tmpsql = tmpsql.Replace(m.Value, "#sp#");
}
foreach (System.Text.RegularExpressions.Match m in re.Matches(tmpsql))
{
if (up.GetValue(m.ToString()) is byte[])
{
dbc.Add(m.ToString(), up.GetValue(m.ToString()), System.Data.DbType.Binary);
}
else if (up.GetValue(m.ToString()) is DateTime)
{
dbc.Add(m.ToString(), up.GetValue(m.ToString()), System.Data.DbType.DateTime);
}
else if (up.GetValue(m.ToString()) is int)
{
dbc.Add(m.ToString(), up.GetValue(m.ToString()), System.Data.DbType.Int32);
}
else if (up.GetValue(m.ToString()) is double)
{
dbc.Add(m.ToString(), up.GetValue(m.ToString()), System.Data.DbType.Double);
}
else
{
dbc.Add(m.ToString(), up.GetValue(m.ToString()));
}
}
}
try
{
rtn.QueryDatas = dba.Query(sql, dbc);
}catch (Exception ex)
{
FrameDLRObject dp = FrameDLRObject.CreateInstance(Base.Constants.FrameDLRFlags.SensitiveCase);
foreach (var item in dbc)
{
if (item.Value.ParameterValue is DateTime)
{
dp.SetValue(item.Key, DateTimeStd.IsDateTimeThen(item.Value.ParameterValue, "yyyy-MM-dd HH:mm:ss"));
}
else if (item.Value.ParameterValue is DBNull)
{
dp.SetValue(item.Key, null);
}
else
{
dp.SetValue(item.Key, item.Value.ParameterValue);
}
}
GlobalCommon.Logger.WriteLog(Base.Constants.LoggerLevel.ERROR, $"QuerySql={sql};\nParameters={dp.ToJSONString()}");
throw ex;
}
if (rtn.QueryDatas != null && rtn.QueryDatas.Tables.Count > 0)
{
rtn.QueryTable = rtn.QueryDatas[0];
}
}
return(rtn);
}
public DataCollection DoOperate(ParameterStd p)
{
string flag = p.GetValue <string>("_unit_action_flag_");
//预执行
T t = (T)Activator.CreateInstance(typeof(T), true);
UnitParameter up = (UnitParameter)p;
UnitDataCollection rtn = new UnitDataCollection();
if (up.Dao is ADBAccess)
{
var sqlobj = t.GetSqlFunc(flag)(up);
ADBAccess dba = (ADBAccess)up.Dao;
string regstr = "";
string regexpress = @"(?isx)
(') #开始标记“<tag...>”
(?> #分组构造，用来限定量词“*”修饰范围
\1 (?<Open>) #命名捕获组，遇到开始标记，入栈，Open计数加1
|\1 (?<-Open>) #狭义平衡组，遇到结束标记，出栈，Open计数减1
|[^']* #右侧不为开始或结束标记的任意字符
)
(?(Open)(?!)) #判断是否还有'OPEN'，有则说明不配对，什么都不匹配
\1 #结束标记“</tag>”
";
string tmpsql = "";
regstr = @"(?<=" + (dba.ParameterFlagChar == "$" ? "\\$" : dba.ParameterFlagChar) + @")[A-Za-z0-9_]+\d*";
Regex re = new Regex(regstr);
Regex re2 = new Regex(regexpress);
try
{
if (!(sqlobj is FrameDLRObject))
{
throw new TypeRequiredException("需要指定的动态数据对象：FrameDLRObject");
}
string presql = sqlobj.presql;
DBAPageP dbc = new DBAPageP();
//dba.BeginTransaction();
if (!string.IsNullOrEmpty(presql))
{
tmpsql = presql.Replace("''", "#sp#");
foreach (Match m in re2.Matches(tmpsql))
{
tmpsql = tmpsql.Replace(m.Value, "#sp#");
}
foreach (System.Text.RegularExpressions.Match m in re.Matches(tmpsql))
{
dbc.SQL_Parameters.Add(m.ToString(), up.GetValue(m.ToString()));
}
try
{
dba.ExecuteNoQuery(presql, dbc.SQL_Parameters);
}
catch (Exception ex)
{
FrameDLRObject dp = FrameDLRObject.CreateInstance(Base.Constants.FrameDLRFlags.SensitiveCase);
foreach (var item in dbc.SQL_Parameters)
{
if (item.Value.ParameterValue is DateTime)
{
dp.SetValue(item.Key, DateTimeStd.IsDateTimeThen(item.Value.ParameterValue, "yyyy-MM-dd HH:mm:ss"));
}
else if (item.Value.ParameterValue is DBNull)
{
dp.SetValue(item.Key, null);
}
else
{
dp.SetValue(item.Key, item.Value.ParameterValue);
}
}
GlobalCommon.Logger.WriteLog(Base.Constants.LoggerLevel.ERROR, $"QueryByPage PreSql={presql};\nParameters={dp.ToJSONString()}");
throw ex;
}
}
//执行翻页查询
string sql = sqlobj.sql;
string orderby = sqlobj.orderby;
if (!string.IsNullOrEmpty(sql))
{
tmpsql = sql.Replace("''", "#sp#");
foreach (Match m in re2.Matches(tmpsql))
{
tmpsql = tmpsql.Replace(m.Value, "#sp#");
}
dbc.SQL_Parameters.Clear();
foreach (System.Text.RegularExpressions.Match m in re.Matches(tmpsql))
{
if (up.GetValue(m.ToString()) is byte[])
{
dbc.SQL_Parameters.Add(m.ToString(), up.GetValue(m.ToString()), System.Data.DbType.Binary);
}
else
{
dbc.SQL_Parameters.Add(m.ToString(), up.GetValue(m.ToString()));
}
}
try
{
dbc.SQL = sql;
dbc.OrderBy = orderby;
dbc.Count_of_OnePage = up.Count_Of_OnePage;
dbc.CurrentPage = up.CurrentPage;
dba.StartPageByCondition(dbc);
rtn.QueryTable = dba.GoToPage(up.ToPage);
rtn.QueryDatas = new DataSetStd();
rtn.QueryDatas.Tables.Add(rtn.QueryTable);
rtn.Count_Of_OnePage = up.Count_Of_OnePage;
rtn.CurrentPage = dba.CurrentPage;
rtn.TotalPage = dba.TotalPage;
rtn.TotalRow = dba.TotalRow;
}
catch (Exception ex)
{
FrameDLRObject dp = FrameDLRObject.CreateInstance(Base.Constants.FrameDLRFlags.SensitiveCase);
foreach (var item in dbc.SQL_Parameters)
{
if (item.Value.ParameterValue is DateTime)
{
dp.SetValue(item.Key, DateTimeStd.IsDateTimeThen(item.Value.ParameterValue, "yyyy-MM-dd HH:mm:ss"));
}
else if (item.Value.ParameterValue is DBNull)
{
dp.SetValue(item.Key, null);
}
else
{
dp.SetValue(item.Key, item.Value.ParameterValue);
}
}
GlobalCommon.Logger.WriteLog(Base.Constants.LoggerLevel.ERROR, $"QueryByPage sql={sql};\n order by={orderby};\nParameters={dp.ToJSONString()}");
throw ex;
}
}
//收尾处理
string aftersql = sqlobj.aftersql;
if (!string.IsNullOrEmpty(aftersql))
{
tmpsql = aftersql.Replace("''", "#sp#");
foreach (Match m in re2.Matches(tmpsql))
{
tmpsql = tmpsql.Replace(m.Value, "#sp#");
}
dbc.SQL_Parameters.Clear();
foreach (System.Text.RegularExpressions.Match m in re.Matches(tmpsql))
{
dbc.SQL_Parameters.Add(m.ToString(), up.GetValue(m.ToString()));
}
try
{
dba.ExecuteNoQuery(aftersql, dbc.SQL_Parameters);
}catch (Exception ex)
{
FrameDLRObject dp = FrameDLRObject.CreateInstance(Base.Constants.FrameDLRFlags.SensitiveCase);
foreach (var item in dbc.SQL_Parameters)
{
if (item.Value.ParameterValue is DateTime)
{
dp.SetValue(item.Key, DateTimeStd.IsDateTimeThen(item.Value.ParameterValue, "yyyy-MM-dd HH:mm:ss"));
}
else if (item.Value.ParameterValue is DBNull)
{
dp.SetValue(item.Key, null);
}
else
{
dp.SetValue(item.Key, item.Value.ParameterValue);
}
}
GlobalCommon.Logger.WriteLog(Base.Constants.LoggerLevel.ERROR, $"QueryByPage AfterSql={aftersql};\nParameters={dp.ToJSONString()}");
throw ex;
}
}
//dba.CommitTransaction();
}
catch
{
//if (dba != null)
// dba.RollbackTransaction();
throw;
}
}
return(rtn);
}
public DataCollection DoOperate(ParameterStd p)
{
UnitDataCollection rtn = new UnitDataCollection();
string flag = p.GetValue <string>("_unit_action_flag_");
UnitParameter up = (UnitParameter)p;
if (up.Dao is ADBAccess)
{
T t = (T)Activator.CreateInstance(typeof(T), true);
var sqlobj = t.GetSqlFunc(flag)(up);
if (!(sqlobj is FrameDLRObject))
{
throw new TypeRequiredException("需要指定的动态数据对象：FrameDLRObject");
}
string sql = sqlobj.sql;
ADBAccess dba = (ADBAccess)up.Dao;
DBOParameterCollection dbc = new DBOParameterCollection();
if (!string.IsNullOrEmpty(sql))
{
string regstr = "";
if (dba is OracleAccess)
{
regstr = @"(?<=:)[a-zA-Z0-9_]*\d*";
}
else
{
regstr = @"(?<=@)[A-Za-z0-9_]+\d*";
}
string regexpress = @"(?isx)
(') #开始标记“<tag...>”
(?> #分组构造，用来限定量词“*”修饰范围
\1 (?<Open>) #命名捕获组，遇到开始标记，入栈，Open计数加1
|\1 (?<-Open>) #狭义平衡组，遇到结束标记，出栈，Open计数减1
|[^']* #右侧不为开始或结束标记的任意字符
)
(?(Open)(?!)) #判断是否还有'OPEN'，有则说明不配对，什么都不匹配
\1 #结束标记“</tag>”
";
Regex re = new Regex(regstr);
string tmpsql = "";
Regex re2 = new Regex(regexpress);
tmpsql = sql.Replace("''", "#sp#");
foreach (Match m in re2.Matches(tmpsql))
{
tmpsql = tmpsql.Replace(m.Value, "#sp#");
}
foreach (System.Text.RegularExpressions.Match m in re.Matches(tmpsql))
{
if (up.GetValue(m.ToString()) is byte[])
{
dbc.Add(m.ToString(), up.GetValue(m.ToString()), System.Data.DbType.Binary);
}
else if (up.GetValue(m.ToString()) is DateTime)
{
dbc.Add(m.ToString(), up.GetValue(m.ToString()), System.Data.DbType.DateTime);
}
else if (up.GetValue(m.ToString()) is int)
{
dbc.Add(m.ToString(), up.GetValue(m.ToString()), System.Data.DbType.Int32);
}
else if (up.GetValue(m.ToString()) is double)
{
dbc.Add(m.ToString(), up.GetValue(m.ToString()), System.Data.DbType.Double);
}
else
{
dbc.Add(m.ToString(), up.GetValue(m.ToString()));
}
}
}
rtn.QueryDatas = dba.Query(sql, dbc);
if (rtn.QueryDatas != null && rtn.QueryDatas.Tables.Count > 0)
{
rtn.QueryTable = rtn.QueryDatas[0];
}
}
return(rtn);
}