/// <summary>
/// 填充对于指定功能模块数据的同步到其他站点的历史记录
/// </summary>
/// <param name="userHandle"></param>
/// <param name="dt"></param>
/// <param name="progId"></param>
/// <param name="internalId">内码</param>
public static void FillSyncDataHistory(LibHandle userHandle, DataTable dt, string progId, string internalId)
{
if (userHandle == null || dt == null || string.IsNullOrEmpty(progId) || ExistAxpSyncDataInfo == false || ExistLinkSiteTable == false)
{
return;
}
try
{
if (string.IsNullOrEmpty(internalId) == false)
{
string sql = string.Format("select A.*,C.PERSONNAME,D.SHORTNAME from AXPSYNCDATAHISTORY A " +
" left join AXPUSER B on A.USERID = B.USERID " +
" left join COMPERSON C on B.PERSONID = C.PERSONID " +
" left join AXPLINKSITE D on A.SITEID = D.SITEID " +
" where A.PROGID = {0} and A.INTERNALID = {1} " +
" order by A.SYNCTIME desc",// 时间降序
LibStringBuilder.GetQuotString(progId), LibStringBuilder.GetQuotString(internalId));
LibDataAccess dataAccess = new LibDataAccess();
dataAccess.ExecuteDataTable(sql, dt, true, 200);//取前200条
}
dt.AcceptChanges();
}
catch (Exception exp)
{
LibCommUtils.AddOutput(@"Error\CrossSiteCall", string.Format("error:{0}\r\nStacktrace:{1}", exp.Message, exp.StackTrace));
}
}
/// <summary>
/// 获取用户可访问的站点
/// </summary>
/// <param name="userHandle"></param>
/// <returns></returns>
public List <LinkSiteInfo> GetLinkSites(string userHandle)
{
// 判断userHandle存不存在
LibHandle handle = LibHandleCache.Default.GetCurrentHandle(userHandle);
if (handle == null)
{
throw new Exception("用户句柄无效");
}
List <LinkSiteInfo> linkSiteInfoList = new List <LinkSiteInfo>();
string sql = string.Format("select B.SITEID, B.SITENAME, B.SHORTNAME, B.SITEURL, B.SVCURL, B.ISSLAVE,B.ISSENDTO from AXPUSERSITE A join AXPLINKSITE B on A.SITEID = B.SITEID where A.USERID = {0}", LibStringBuilder.GetQuotString(handle.UserId));
LibDataAccess dataAccess = new LibDataAccess();
using (IDataReader reader = dataAccess.ExecuteDataReader(sql))
{
while (reader.Read())
{
var siteInfo = new LinkSiteInfo()
{
SiteId = LibSysUtils.ToString(reader["SITEID"]),
SiteName = LibSysUtils.ToString(reader["SITENAME"]),
ShortName = LibSysUtils.ToString(reader["SHORTNAME"]),
SiteUrl = LibSysUtils.ToString(reader["SITEURL"]),
SvcUrl = LibSysUtils.ToString(reader["SVCURL"]),
IsSlave = LibSysUtils.ToBoolean(reader["ISSLAVE"]),
IsSendTo = LibSysUtils.ToBoolean(reader["ISSENDTO"]),
};
linkSiteInfoList.Add(siteInfo);
}
}
return(linkSiteInfoList);
}
public CodingRule GetCodingRule(BillType billType, string progId)
{
CodingRule codingRule = this.Get <CodingRule>(progId);
if (codingRule == null)
{
lock (_LockRuleObj)
{
codingRule = this.Get <CodingRule>(progId);
if (codingRule == null)
{
codingRule = new CodingRule();
Dictionary <int, int> listIndex = new Dictionary <int, int>();
SqlBuilder sqlBuilder = new SqlBuilder("com.CodingRule");
string sql = sqlBuilder.GetQuerySql(1, "B.ROW_ID,B.SECTIONTYPE,B.SECTIONLENGTH,B.FIELDNAME,B.SECTIONVALUE", string.Format("A.PROGID = {0} And A.VALIDITYSTARTDATE <= {1} And (A.VALIDITYENDDATE >= {1} or A.VALIDITYENDDATE = 0)", LibStringBuilder.GetQuotString(progId), LibDateUtils.GetCurrentDate()), "B.ROWNO ASC");
LibDataAccess dataAccess = new LibDataAccess();
using (IDataReader reader = dataAccess.ExecuteDataReader(sql))
{
while (reader.Read())
{
CodingRuleItem rule = new CodingRuleItem();
rule.SectionType = (SectionType)LibSysUtils.ToInt32(reader["SECTIONTYPE"]);
rule.Length = LibSysUtils.ToInt32(reader["SECTIONLENGTH"]);
switch (rule.SectionType)
{
case SectionType.None:
rule.Value = LibSysUtils.ToString(reader["SECTIONVALUE"]);
break;
case SectionType.Dynamic:
rule.FieldName = LibSysUtils.ToString(reader["FIELDNAME"]);
rule.Values.Add(rule.FieldName, LibSysUtils.ToString(reader["SECTIONVALUE"]));
int rowId = LibSysUtils.ToInt32(reader["ROW_ID"]);
if (!listIndex.ContainsKey(rowId))
{
listIndex.Add(rowId, codingRule.Items.Count);
}
if (!codingRule.CreateOnSave)
{
codingRule.CreateOnSave = true;
}
break;
}
codingRule.Items.Add(rule);
}
}
sql = sqlBuilder.GetQuerySql(2, "C.PARENTROWID,C.FIELDVALUE,C.SECTIONVALUE", string.Format("A.PROGID = {0} And A.VALIDITYSTARTDATE <= {1} And (A.VALIDITYENDDATE >= {1} or A.VALIDITYENDDATE = 0)", LibStringBuilder.GetQuotString(progId), LibDateUtils.GetCurrentDate()));
using (IDataReader reader = dataAccess.ExecuteDataReader(sql))
{
while (reader.Read())
{
int rowId = LibSysUtils.ToInt32(reader["PARENTROWID"]);
if (listIndex.ContainsKey(rowId))
{
CodingRuleItem rule = codingRule.Items[listIndex[rowId]];
string fieldValue = LibSysUtils.ToString(reader["FIELDVALUE"]);
if (!rule.Values.ContainsKey(fieldValue))
{
rule.Values.Add(fieldValue, LibSysUtils.ToString(reader["SECTIONVALUE"]));
}
}
}
}
//如果为单据，默认产生编码规则 日期+6位流水码
if (codingRule.Items.Count == 0)
{
if (billType == BillType.Bill)
{
codingRule.Items.Add(new CodingRuleItem()
{
SectionType = SectionType.DateL
});
codingRule.Items.Add(new CodingRuleItem()
{
SectionType = SectionType.SerialNum, Length = 6
});
}
}
else
{
codingRule.IsSetRule = true;
}
if (codingRule.Items.Count > 0)
{
this.Set(progId, codingRule, new TimeSpan(0, 180, 0));
}
}
}
}
return(codingRule);
}
public string Register(RegisterInfo info)
{
string error = string.Empty;
LibDataAccess dataAccess = new LibDataAccess();
string userId = LibSysUtils.ToString(dataAccess.ExecuteScalar(string.Format("select USERID from AXPUSER where USERID={0}",
LibStringBuilder.GetQuotString(info.inputId))));
if (string.IsNullOrEmpty(userId))
{
string personId = LibSysUtils.ToString(dataAccess.ExecuteScalar(string.Format("select PERSONID from COMPERSON where PERSONNAME={0} and DEPTID={1}",
LibStringBuilder.GetQuotString(info.inputName), LibStringBuilder.GetQuotString(info.inputDept))));
//如果遇到同部门同名的情况。建议客户手动创建人员主数据。
if (string.IsNullOrEmpty(personId))
{
LibBcfData bcfData = (LibBcfData)LibBcfSystem.Default.GetBcfInstance("com.Person");
DataSet dataSet = bcfData.AddNew(null);
DataRow masterRow = dataSet.Tables[0].Rows[0];
masterRow.BeginEdit();
try
{
if (string.IsNullOrEmpty(LibSysUtils.ToString(masterRow["PERSONID"])))
{
masterRow["PERSONID"] = personId = LibCommUtils.GetInternalId().ToString();
}
masterRow["PERSONNAME"] = info.inputName;
masterRow["GENDER"] = info.gender;
masterRow["DEPTID"] = info.inputDept;
masterRow["MAIL"] = info.inputEmail;
masterRow["PHONENO"] = info.inputPhone;
}
finally
{
masterRow.EndEdit();
}
dataSet = bcfData.InnerSave(BillAction.AddNew, new object[] { personId }, dataSet);
personId = LibSysUtils.ToString(dataSet.Tables[0].Rows[0]["PERSONID"]);
}
if (!string.IsNullOrEmpty(personId))
{
string sql = string.Format("insert into AXPUSER(USERID,USERPASSWORD,PERSONID,ISUSE) values({0},{1},{2},0)",
LibStringBuilder.GetQuotString(info.inputId), LibStringBuilder.GetQuotString(info.inputPassword1),
LibStringBuilder.GetQuotString(personId));
dataAccess.ExecuteNonQuery(sql);
}
}
else
{
error = "账号已注册";
}
return(error);
}
/// <summary>
/// 在跨站登陆前，访问获取Token
/// </summary>
/// <param name="userHandle">当前用户句柄</param>
/// <returns></returns>
public string GetToken(string userHandle)
{
LibHandle handle = LibHandleCache.Default.GetCurrentHandle(userHandle);
if (handle == null)
{
return(string.Empty);
}
if (EnvProvider.Default.IsSSOManageSite)
{
return(handle.GetToCheckToken());
}
else
{
try
{
string sql = string.Format("select USERPASSWORD from AXPUSER where USERID = {0}", LibStringBuilder.GetQuotString(handle.UserId));
LibDataAccess dataAccess = new LibDataAccess();
var pwd = dataAccess.ExecuteScalar(sql);
if (pwd == null)
{
return(string.Empty);
}
string baseUrl = EnvProvider.Default.SSOManageSiteUrl;
string url = baseUrl + "/sysSvc/getTokenByUserId";
string errorInfo = "";
dynamic obj = LibNetUtils.HttpPostCall <dynamic>(url, new { userId = handle.UserId, pwd = pwd.ToString() }, out errorInfo);
if (string.IsNullOrEmpty(errorInfo))
{
string token = obj.GetTokenByUserIdResult.Value;
return(token);
}
return(string.Empty);
}
catch (Exception)
{
return(string.Empty);
//throw;
}
}
}
public LoginInfo AppLogin(string userId, string password, string clientId, int clientType, bool quitOther = false)
{
LoginInfo loginInfo = new LoginInfo();
//检查是否具有 AXPUSERAPP数据表，用于判定是否支持移动端App登录
LibSqlModel sqlModel = LibSqlModelCache.Default.GetSqlModel("axp.User");
bool hasAXPUSERAPP = false;
if (sqlModel != null && sqlModel.Tables.Count > 1 && sqlModel.Tables[1].TableName.Equals("AXPUSERAPP"))
{
hasAXPUSERAPP = true;
}
if (hasAXPUSERAPP == false)
{
return(loginInfo);//如果没有需要的相关字段则直接返回
}
SqlBuilder builder = new SqlBuilder("axp.User");
string sql = string.Format(@"SELECT
A.PERSONID,
A.ROLEID,
A.WALLPAPER,A.WALLPAPERSTRETCH,B.PERSONNAME,B.PHONENO,B.CORNET,B.HEADPORTRAIT,B.MAIL
FROM AXPUSER A LEFT JOIN COMPERSON B ON B.PERSONID=A.PERSONID
WHERE A.USERID={0} and A.USERPASSWORD={1} AND A.ISUSE=1", LibStringBuilder.GetQuotString(userId), LibStringBuilder.GetQuotString(password));
//builder.GetQuerySql(0, "A.PERSONID,A.PERSONNAME,A.PHONENO,A.CORNET,A.HEADPORTRAIT,A.MAIL,A.ROLEID,A.WALLPAPER,A.WALLPAPERSTRETCH", string.Format("A.USERID={0} And A.USERPASSWORD={1} And A.ISUSE=1", LibStringBuilder.GetQuotString(userId), LibStringBuilder.GetQuotString(password)));
LibDataAccess dataAccess = new LibDataAccess();
string roleId = string.Empty;
bool exists = false;
using (IDataReader reader = dataAccess.ExecuteDataReader(sql))
{
if (reader.Read())
{
loginInfo.PersonId = LibSysUtils.ToString(reader["PERSONID"]);
loginInfo.PersonName = LibSysUtils.ToString(reader["PERSONNAME"]);
roleId = LibSysUtils.ToString(reader["ROLEID"]);
loginInfo.Wallpaper = LibSysUtils.ToString(reader["WALLPAPER"]);
loginInfo.Stretch = LibSysUtils.ToBoolean(reader["WALLPAPERSTRETCH"]);
//20170214 施卢威 增加头像 Email 短号信息
loginInfo.Headportrait = LibSysUtils.ToString(reader["HEADPORTRAIT"]);
loginInfo.UserEMail = LibSysUtils.ToString(reader["MAIL"]);
loginInfo.Cornet = LibSysUtils.ToString(reader["CORNET"]);
loginInfo.UserPhone = LibSysUtils.ToString(reader["PHONENO"]);
exists = true;
}
}
if (exists)
{
#region 帐号与登录设备关联
string appSql = string.Empty;
//查询帐号是否已有设备标识
int isAPPClient = LibSysUtils.ToInt32(dataAccess.ExecuteScalar(string.Format("SELECT COUNT(*) from AXPUSERAPP WHERE USERID = '{0}' and CLIENTTYPE={1}", userId, clientType)));
if (isAPPClient > 0)
{
//更新设备标识信息
appSql = string.Format("UPDATE AXPUSERAPP SET CLIENTID={1} WHERE USERID={0} AND CLIENTTYPE={2}", LibStringBuilder.GetQuotString(userId), LibStringBuilder.GetQuotString(clientId), clientType);
}
else
{
int curMaxRowId = LibSysUtils.ToInt32(dataAccess.ExecuteScalar(string.Format("SELECT Max(ROW_ID) from AXPUSERAPP WHERE USERID='{0}'", userId)));
//插入账户对应的App设备标识信息。
appSql = string.Format("insert into AXPUSERAPP(USERID,ROW_ID,ROWNO,CLIENTTYPE,CLIENTID) values('{0}',{1},{2},{3},'{4}')",
userId, curMaxRowId + 1, curMaxRowId + 1, clientType, clientId, 1);
}
dataAccess.ExecuteNonQuery(appSql);
#endregion
LibHandle handle = LibHandleCache.Default.IsExistsHandle(LibHandeleType.PC, userId);
if (handle != null)
{
if (quitOther)
{
LibHandleCache.Default.RemoveHandle(handle.Handle);
}
else
{
loginInfo.IsUsed = true;
}
}
if (!loginInfo.IsUsed)
{
long currentCount = LibHandleCache.Default.GetCount();
long maxUserCount = (long)LibHandleCache.Default.MaxUserCount;
if (maxUserCount != -1 && maxUserCount < currentCount)
{
loginInfo.IsOverUser = true;
}
else
{
string loginIp = string.Empty;
//Zhangkj20161219 增加LoginIp
System.ServiceModel.OperationContext context = System.ServiceModel.OperationContext.Current;
//对于非WCF的访问context为null
if (context != null)
{
System.ServiceModel.Channels.MessageProperties properties = context.IncomingMessageProperties;
System.ServiceModel.Channels.RemoteEndpointMessageProperty endpoint = properties[System.ServiceModel.Channels.RemoteEndpointMessageProperty.Name] as System.ServiceModel.Channels.RemoteEndpointMessageProperty;
loginIp = endpoint.Address + ":" + endpoint.Port.ToString();
handle = LibHandleCache.Default.GetHandle(string.Empty, LibHandeleType.PC, userId, loginInfo.PersonId, loginInfo.PersonName, roleId, loginIp);
}
else
{
handle = LibHandleCache.Default.GetHandle(string.Empty, LibHandeleType.PC, userId, loginInfo.PersonId, loginInfo.PersonName, roleId);
}
//创建新的Handle
if (handle != null)
{
loginInfo.Handle = handle.Handle;
}
}
}
}
return(loginInfo);
}
public LoginInfo Login1(string userId, string password, bool quitOther = false)
{
LoginInfo loginInfo = new LoginInfo();
SqlBuilder builder = new SqlBuilder("axp.User");
string sql = builder.GetQuerySql(0, "A.Id,A.Name,A.UserImage", string.Format("A.Id={0} And A.Password={1} And A.ISUSE=1", LibStringBuilder.GetQuotString(userId), LibStringBuilder.GetQuotString(password)));
LibDataAccess dataAccess = new LibDataAccess();
string roleId = string.Empty;
bool exists = false;
using (IDataReader reader = dataAccess.ExecuteDataReader(sql))
{
if (reader.Read())
{
loginInfo.Id = LibSysUtils.ToString(reader[0]);
loginInfo.Name = LibSysUtils.ToString(reader[1]);
loginInfo.UserImage = LibSysUtils.ToString(reader[2]);
exists = true;
}
}
if (exists)
{
LibHandle handle = LibHandleCache.Default.IsExistsHandle(LibHandeleType.PC, userId);
if (handle != null)
{
//LibHandleCache.Default.RemoveHandle(handle.Handle);
}
if (!loginInfo.IsUsed)
{
string loginIp = string.Empty;
//Zhangkj20161219 增加LoginIp
System.ServiceModel.OperationContext context = System.ServiceModel.OperationContext.Current;
//对于非WCF的访问context为null
if (context != null)
{
System.ServiceModel.Channels.MessageProperties properties = context.IncomingMessageProperties;
System.ServiceModel.Channels.RemoteEndpointMessageProperty endpoint = properties[System.ServiceModel.Channels.RemoteEndpointMessageProperty.Name] as System.ServiceModel.Channels.RemoteEndpointMessageProperty;
loginIp = endpoint.Address + ":" + endpoint.Port.ToString();
}
//创建新的Handle
handle = LibHandleCache.Default.GetHandle(string.Empty, LibHandeleType.PC, userId, loginInfo.PersonId, loginInfo.PersonName, roleId, loginIp);
if (handle != null)
{
loginInfo.Handle = handle.Handle;
}
}
}
return(loginInfo);
}
public Dictionary <string, object> GetPrintTemplateJs(string billNo, string printTplNo, int printTplRowId, int printTplSubRowId)
{
List <LabelTemplateRule> LabelTemplateRuleList = new List <LabelTemplateRule>();
StringBuilder builder = new StringBuilder(), builderVal = new StringBuilder();
SqlBuilder sqlBuilder = new SqlBuilder("axp.PrintTpl");
string printTplJs = string.Empty, sql = string.Empty;
Dictionary <string, object> dic = new Dictionary <string, object>(), billValueDic = new Dictionary <string, object>();
builder.Append(sqlBuilder.GetQuerySql(2, "C.TPLJS", string.Format(" C.PRINTTPLID = {0} AND C.PARENTROWID = {1} AND C.ROW_ID = {2} ", LibStringBuilder.GetQuotString(printTplNo), printTplRowId, printTplSubRowId)));
using (IDataReader reader = this.DataAccess.ExecuteDataReader(builder.ToString()))
{
while (reader.Read())
{
printTplJs = LibSysUtils.ToString(reader["TPLJS"]);
}
}
builder.Clear();
builder.Append(sqlBuilder.GetQuerySql(3, "D.FIELDNAME,D.TABLEINDEX,D.TPLPARAM", string.Format(" D.PRINTTPLID = {0} AND D.GRANDFATHERROWID = {1} AND D.PARENTROWID= {2} ", LibStringBuilder.GetQuotString(printTplNo), printTplRowId, printTplSubRowId)));
Dictionary <string, PropertyCollection> fieldCollection = new Dictionary <string, PropertyCollection>();
using (IDataReader reader = this.DataAccess.ExecuteDataReader(builder.ToString()))
{
while (reader.Read())
{
LabelTemplateRule labelTemplateRule = new LabelTemplateRule();
labelTemplateRule.FieldName = LibSysUtils.ToString(reader["FIELDNAME"]);
labelTemplateRule.TableIndex = LibSysUtils.ToInt32(reader["TABLEINDEX"]);
labelTemplateRule.Tplparam = LibSysUtils.ToString(reader["TPLPARAM"]);
LabelTemplateRuleList.Add(labelTemplateRule);
builderVal.AppendFormat("{0}.{1},", (char)(labelTemplateRule.TableIndex + (int)'A'), labelTemplateRule.FieldName);
if (!fieldCollection.ContainsKey(labelTemplateRule.FieldName))
{
fieldCollection.Add(labelTemplateRule.FieldName, this.DataSet.Tables[labelTemplateRule.TableIndex].Columns[labelTemplateRule.FieldName].ExtendedProperties);
}
}
}
if (builderVal.Length > 0)
{
builderVal.Remove(builderVal.Length - 1, 1);
sql = new SqlBuilder(this.ProgId).GetQuerySql(0, builderVal.ToString(), string.Format(" A.BILLNO={0} ", LibStringBuilder.GetQuotString(billNo)));
using (IDataReader reader = this.DataAccess.ExecuteDataReader(sql, true))
{
if (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
string name = reader.GetName(i);
PropertyCollection propertyList = fieldCollection[name];
LibControlType controlType = (LibControlType)propertyList[FieldProperty.ControlType];
switch (controlType)
{
case LibControlType.TextOption:
billValueDic.Add(name, ((string[])propertyList[FieldProperty.Option])[LibSysUtils.ToInt32(reader[i])]);
break;
case LibControlType.YesNo:
if (LibSysUtils.ToBoolean(reader[i]))
{
billValueDic.Add(name, "是");
}
else
{
billValueDic.Add(name, "否");
}
break;
case LibControlType.Date:
int date = LibSysUtils.ToInt32(reader[i]);
if (date == 0)
{
billValueDic.Add(name, string.Empty);
}
else
{
billValueDic.Add(name, LibDateUtils.LibDateToDateTime(date).ToLongDateString());
}
break;
case LibControlType.DateTime:
long dateTime = LibSysUtils.ToInt64(reader[i]);
if (dateTime == 0)
{
billValueDic.Add(name, string.Empty);
}
else
{
billValueDic.Add(name, LibDateUtils.LibDateToDateTime(dateTime).ToLocalTime());
}
break;
case LibControlType.Rate:
double rate = LibSysUtils.ToDouble(reader[i]);
billValueDic.Add(name, string.Format("{0}%", rate * 100));
break;
case LibControlType.KeyValueOption:
billValueDic.Add(name, ((LibTextOptionCollection)propertyList[FieldProperty.KeyValueOption])[LibSysUtils.ToInt32(reader[i])].Value);
break;
default:
billValueDic.Add(name, reader[i]);
break;
}
}
}
}
}
dic.Add("TemplateJs", printTplJs);
dic.Add("LabelTemplateRuleList", LabelTemplateRuleList);
dic.Add("BillValueDic", billValueDic);
return(dic);
}
private List <string> FindFilterFieldsByAdmin()
{
List <string> list = new List <string>();
string sql = string.Format("select SEARCHFIELD from AXPRPTSEARCHFIELDDETAIL where RPTSEARCHID in (select top 1 RPTSEARCHID from AXPRPTSEARCHFIELD A where A.PROGID = {0} and A.USERID = 'admin' and A.ISON = 1 ORDER BY CREATETIME DESC)", LibStringBuilder.GetQuotString(this.ProgId));
using (IDataReader dr = DataAccess.ExecuteDataReader(sql))
{
while (dr.Read())
{
list.Add(dr[0].ToString());
}
}
return(list);
}
/// <summary>
/// 获取指定数据的子数据主键值列表。
/// </summary>
/// <param name="dataType">数据类型</param>
/// <param name="dataId">数据主键标识</param>
/// <param name="tabName">数据表名称</param>
/// <param name="keyIdColumnName">主键列名</param>
/// <param name="parenetIdColumnName">关联到父数据的数据列名称</param>
/// <param name="isContainsSelf">是否包含自身，默认为false</param>
/// <returns></returns>
public List <object> GetSubDataIds(LibDataType dataType, object dataId, string tabName, string keyIdColumnName, string parenetIdColumnName, bool isContainsSelf = false)
{
List <object> list = new List <object>();
bool needQuot = dataType == LibDataType.Text || dataType == LibDataType.NText;
if (string.IsNullOrEmpty(tabName.Trim()) || string.IsNullOrEmpty(keyIdColumnName.Trim()) || string.IsNullOrEmpty(parenetIdColumnName.Trim()))
{
return(list);
}
try
{
//从本级数据开始到最底级目录。第一行为本级数据（dataId标识的数据），下面的是按层级的子级数据
string sqlFindSub = "";
string tempTableName = string.Format("{0}_{1}", "temp", DateTime.Now.Ticks);
if (this.DataAccess.DatabaseType == LibDatabaseType.SqlServer)
{
sqlFindSub = string.Format(" with {0} as " +
" ( " +
" select a.{1},a.{2} from {3} a where {1} = {4} " +
" union all " +
" select k.{1},k.{2} from {3} k inner " +
" join {0} t on t.{1} = k.{2} " +
" ) select * from " + tempTableName,
tempTableName, keyIdColumnName.Trim(), parenetIdColumnName.Trim(), tabName.Trim(), (needQuot) ? LibStringBuilder.GetQuotObject(dataId) : dataId);
}
else
{
//Oracle的递归查询待测试
sqlFindSub = string.Format("select {0},{1} " +
" from {2} " +
" START WITH {0} = {3} " +
" CONNECT BY PRIOR {0} = {1} ", keyIdColumnName.Trim(), parenetIdColumnName.Trim(), tabName.Trim(), (needQuot) ? LibStringBuilder.GetQuotObject(dataId) : dataId);
}
DataTable subDirDt = null;
DataSet ds2 = this.DataAccess.ExecuteDataSet(sqlFindSub);
if (ds2 != null && ds2.Tables.Count > 0)
{
subDirDt = ds2.Tables[0];
}
if (subDirDt != null && subDirDt.Rows.Count > 0)
{
DataRow row = null;
//正序，从当前数据向下级数据方向
object id = null;
for (int i = (isContainsSelf ? 0 : 1); i < subDirDt.Rows.Count; i++)
{
row = subDirDt.Rows[i];
if (needQuot)
{
id = LibSysUtils.ToString(row[keyIdColumnName.Trim()]);
}
else
{
if (row[keyIdColumnName.Trim()] != DBNull.Value)
{
id = row[keyIdColumnName.Trim()];
}
}
if (list.Contains(id) == false)
{
list.Add(id);
}
}
}
}
catch (Exception exp)
{
//to do log
throw exp;
}
return(list);
}
public List <PrintTemplateIds> GetPrintTemplateIds(string billNo)
{
int num = 0;
string progId = this.ProgId;
string printTplId = string.Empty;
StringBuilder builder = new StringBuilder();
SqlBuilder sqlBuilder = new SqlBuilder("axp.PrintTpl");
SqlBuilder sqlBillBuilder = new SqlBuilder(this.ProgId);
DataTable dt = new DataTable();
List <PrintTemplateIds> printTemplateIds = new List <PrintTemplateIds>();
builder.Append(sqlBuilder.GetQuerySql(0, "A.PRINTTPLID", string.Format("A.PROGID = {0}", LibStringBuilder.GetQuotString(progId))));
using (IDataReader reader = this.DataAccess.ExecuteDataReader(builder.ToString()))
{
while (reader.Read())
{
printTplId = LibSysUtils.ToString(reader["PRINTTPLID"]);
}
}
if (printTplId == string.Empty)
{
this.ManagerMessage.AddMessage(LibMessageKind.Error, "打印模板尚未设置！");
}
else
{
builder.Clear();
builder.Append(sqlBillBuilder.GetQuerySql(0, "A.*", string.Format("A.BILLNO = {0}", LibStringBuilder.GetQuotString(billNo))));
this.DataAccess.ExecuteDataTable(builder.ToString(), dt);//将单据数据放入dt中
builder.Clear();
builder.Append(sqlBuilder.GetQuerySql(1, "B.ROW_ID,B.USECONDITION", string.Format(" B.PRINTTPLID = {0} ", LibStringBuilder.GetQuotString(printTplId))));
string sql = builder.ToString();
using (IDataReader reader = this.DataAccess.ExecuteDataReader(builder.ToString(), true))
{
while (reader.Read())
{
List <DataRow> dataRowList = new List <DataRow> {
dt.Rows[0]
};
if (LibSysUtils.ToString(reader["USECONDITION"]) != string.Empty)
{
if (LibParseHelper.Parse(LibSysUtils.ToString(reader["USECONDITION"]), dataRowList))//判断是否匹配到条件符合的打印模板明细
{
num = LibSysUtils.ToInt32(reader["ROW_ID"]);
}
}
}
}
if (num == 0)
{
builder.Clear();
builder.Append(sqlBuilder.GetQuerySql(1, "B.ROW_ID", string.Format(" B.PRINTTPLID = {0} AND B.USECONDITION = '' ", LibStringBuilder.GetQuotString(printTplId))));
using (IDataReader reader = this.DataAccess.ExecuteDataReader(builder.ToString()))
{
while (reader.Read())
{
if (LibSysUtils.ToInt32(reader["ROW_ID"]) == 0)
{
this.ManagerMessage.AddMessage(LibMessageKind.Error, "打印模板明细中没有设置默认模板！");
}
else
{
num = LibSysUtils.ToInt32(reader["ROW_ID"]);
}
}
}
}
if (num > 0)
{
builder.Clear();
builder.Append(sqlBuilder.GetQuerySql(2, "C.PRINTTPLID,C.PARENTROWID,C.ROW_ID", string.Format(" C.PRINTTPLID = {0} AND C.PARENTROWID = {1} ", LibStringBuilder.GetQuotString(printTplId), num), " C.ROW_ID ASC "));
using (IDataReader reader = this.DataAccess.ExecuteDataReader(builder.ToString(), true))
{
while (reader.Read())
{
PrintTemplateIds printTemplateId = new PrintTemplateIds();
printTemplateId.PrintTplNo = LibSysUtils.ToString(reader["PRINTTPLID"]);
printTemplateId.PrintTplRowId = LibSysUtils.ToInt32(reader["PARENTROWID"]);
printTemplateId.PrintTplSubRowId = LibSysUtils.ToInt32(reader["ROW_ID"]);
printTemplateIds.Add(printTemplateId);
}
}
}
}
return(printTemplateIds);
}
public IList <FuzzyResult> FuzzySearchField(int tableIndex, string fieldName, string relSource, string relName,
string query, object[] curPks = null, Dictionary <string, object> selConditionParam = null, string[] currentPks = null)
{
IList <FuzzyResult> list = new List <FuzzyResult>();
RelativeSourceCollection relSources = (RelativeSourceCollection)this.DataSet.Tables[tableIndex].Columns[fieldName].ExtendedProperties[FieldProperty.RelativeSource];
RelativeSource curRelSource = null;
foreach (RelativeSource item in relSources)
{
if (string.Compare(relSource, item.RelSource, true) == 0)
{
curRelSource = item;
break;
}
}
if (curRelSource == null)
{
return(list);
}
SqlBuilder sqlBuilder = new SqlBuilder(relSource);
StringBuilder builder = new StringBuilder();
LibBcfBase bcfBase = LibBcfSystem.Default.GetBcfInstance(relSource);
BillType billType = bcfBase.Template.BillType;
if (relSource.Split(new string[] { "axp" }, StringSplitOptions.None).Length == 1 && (billType == BillType.Bill || billType == BillType.Master))
{
builder.AppendFormat("And A.CURRENTSTATE=2");
}
if (curPks != null && curPks.Length > 0)
{
builder.AppendFormat(" And {0}", GetRelWhere(relSource, curRelSource.TableIndex, 'A', curPks));
}
string selCondition = string.Empty;
if (curRelSource.SelConditions.Count > 0)
{
foreach (SelCondition item in curRelSource.SelConditions)
{
builder.AppendFormat(" And {0}", item.Condition);
}
selCondition = builder.ToString();
selCondition = selCondition.Replace("CURRENT_PERSON", LibStringBuilder.GetQuotObject(this.Handle.PersonId));
if (selConditionParam != null && selConditionParam.Count > 0)
{
LibSqlModel model = LibSqlModelCache.Default.GetSqlModel(this.ProgId);
if (model != null)
{
foreach (KeyValuePair <string, object> item in selConditionParam)
{
string[] temp = item.Key.Split('.');
int index = (int)temp[0][0] - (int)'A';
DataColumn col = model.Tables[index].Columns[temp[1]];
LibDataType dataType = (LibDataType)col.ExtendedProperties[FieldProperty.DataType];
if (dataType == LibDataType.Text)
{
selCondition = selCondition.Replace(string.Format("@{0}", item.Key), LibStringBuilder.GetQuotObject(item.Value));
}
else
{
selCondition = selCondition.Replace(string.Format("@{0}", item.Key), item.Value.ToString());
}
}
}
}
}
else
{
selCondition = builder.ToString();
}
string powerStr = LibPermissionControl.Default.GetShowCondition(this.Handle, relSource, this.Handle.PersonId);
if (!string.IsNullOrEmpty(powerStr))
{
selCondition = string.Format("{0} and {1}", selCondition, powerStr);
}
if (curRelSource.ContainsSub == false && string.IsNullOrEmpty(curRelSource.ParentColumnName) == false &&
currentPks != null && currentPks.Length > 0 && string.IsNullOrEmpty(currentPks[0]) == false)
{
//对于父子结构数据，如果不包含子数据且指定了父列外键列的名称，则添加额外的查询条件 Zhangkj 20170316
DataColumn keyColumn = this.DataSet.Tables[tableIndex].PrimaryKey[0];
string keyColumnName = this.DataSet.Tables[tableIndex].PrimaryKey[0].ColumnName;//目前仅支持单主键
string dataId = currentPks[0];
LibDataType dataType = keyColumn.ExtendedProperties.ContainsKey(FieldProperty.DataType) ? (LibDataType)keyColumn.ExtendedProperties[FieldProperty.DataType] : LibDataTypeConverter.ConvertToLibType(keyColumn.DataType);
List <object> subIds = this.GetSubDataIds(dataType, dataId, this.DataSet.Tables[tableIndex].TableName, keyColumnName, curRelSource.ParentColumnName, true);
if (subIds != null && subIds.Count > 0)
{
bool needQuot = dataType == LibDataType.Text || dataType == LibDataType.NText;
List <object> quotSubIds = new List <object>();
foreach (object obj in subIds)
{
quotSubIds.Add((needQuot) ? LibStringBuilder.GetQuotObject(obj) : obj);
}
selCondition = string.Format("{0} and A.{1} not in ({2})", selCondition, keyColumnName, string.Join(",", quotSubIds));
}
}
string sql = sqlBuilder.GetFuzzySql(curRelSource.TableIndex, relSources, query, selCondition, curRelSource.ParentColumnName);
LibDataAccess dataAccess = new LibDataAccess();
int count = 0;
int filterCount = curRelSource.SearchFilterCount;
using (IDataReader reader = dataAccess.ExecuteDataReader(sql))
{
while (reader.Read())
{
if (reader.FieldCount == 1)
{
list.Add(new FuzzyResult(LibSysUtils.ToString(reader[0]), string.Empty));
}
else if (reader.FieldCount == 2)
{
list.Add(new FuzzyResult(LibSysUtils.ToString(reader[0]), LibSysUtils.ToString(reader[1])));
}
else if (reader.FieldCount == 3)
{
FuzzyResult fuzzyResult = new FuzzyResult(LibSysUtils.ToString(reader[0]), LibSysUtils.ToString(reader[1]));
fuzzyResult.ContainsKeyField = LibSysUtils.ToString(reader[2]);//将除Id Name列以外的包含查询关键字的列的内容
list.Add(fuzzyResult);
}
else if (reader.FieldCount == 4)
{
FuzzyResult fuzzyResult = new FuzzyResult(LibSysUtils.ToString(reader[0]), LibSysUtils.ToString(reader[1]));
fuzzyResult.ContainsKeyField = LibSysUtils.ToString(reader[2]); //将除Id Name列以外的包含查询关键字的列的内容
fuzzyResult.ParentId = LibSysUtils.ToString(reader[3]); //树形结构的父数据Id
if (curRelSource.ExpandAll)
{
fuzzyResult.TreeNodeExpanded = true;
}
list.Add(fuzzyResult);
}
count++;
if (count == filterCount)
{
break;
}
}
}
if (list.Count > 1)
{
LibControlType controlType = (LibControlType)this.DataSet.Tables[tableIndex].Columns[fieldName].ExtendedProperties[FieldProperty.ControlType];
if (controlType == LibControlType.IdNameTree && string.IsNullOrEmpty(curRelSource.ParentColumnName) == false)
{
//处理树形结构数据
List <FuzzyResult> newList = list.ToList();//先全部放入
List <FuzzyResult> tempList = null;
using (MemoryStream stream = new MemoryStream())
{
System.Runtime.Serialization.Formatters.Binary.BinaryFormatter formatter = new System.Runtime.Serialization.Formatters.Binary.BinaryFormatter();
formatter.Serialize(stream, list.ToList());
stream.Position = 0;
tempList = formatter.Deserialize(stream) as List <FuzzyResult>;//深度复制一份
}
//查找是其他节点的子节点的进行处理
int index = 0;
while (index < list.Count)
{
FuzzyResult child = list[index];
FuzzyResult parent = (from re in list
where re != null && re.Id.Equals(child.ParentId)
select re).FirstOrDefault();
if (parent != default(FuzzyResult))
{
newList.Remove(child);
parent.Children.Add(child);
}
index++;
}
newList[0].TotalList = tempList;
list = newList;
}
}
return(list);
}
private string GetRelWhere(string relSource, int tableIndex, char prefix, object[] curPks)
{
StringBuilder whereBuilder = new StringBuilder();
LibSqlModel relModel = LibSqlModelCache.Default.GetSqlModel(relSource);
DataColumn[] cols = relModel.Tables[tableIndex].PrimaryKey;
for (int i = 0; i < curPks.Length; i++)
{
DataColumn pk = cols[i];
if (i != 0)
{
whereBuilder.Append(" AND ");
}
LibDataType dataType = (LibDataType)pk.ExtendedProperties[FieldProperty.DataType];
if (dataType == LibDataType.Text)
{
whereBuilder.AppendFormat("{0}.{1} = {2}", prefix, pk.ColumnName, LibStringBuilder.GetQuotObject(curPks[i]));
}
else
{
whereBuilder.AppendFormat("{0}.{1} = {2}", prefix, pk.ColumnName, LibSysUtils.ToString(curPks[i]) == "" ? 0 : curPks[i]);
}
}
return(whereBuilder.ToString());
}
private static void BuildQueryStr(LibDataType dataType, LibQueryField queryField, StringBuilder builder, string prefix, bool needAnd, string realName)
{
bool needQuot = dataType == LibDataType.Text || dataType == LibDataType.NText;
string addStr = needAnd ? "and " : string.Empty;
string fieldName = string.IsNullOrEmpty(realName) ? queryField.Name : realName;
if (!string.IsNullOrEmpty(prefix))
{
prefix = string.Format("{0}.", prefix);
}
switch (queryField.QueryChar)
{
case LibQueryChar.Equal:
if (queryField.Value.Count > 0)
{
if (needQuot)
{
builder.AppendFormat("{0}{1}={2} {3}", prefix, fieldName, LibStringBuilder.GetQuotObject(queryField.Value[0]), addStr);
}
else
{
builder.AppendFormat("{0}{1}={2} {3}", prefix, fieldName, queryField.Value[0], addStr);
}
}
break;
case LibQueryChar.Contain:
if (queryField.Value.Count > 0)
{
builder.AppendFormat("{0}{1} like '%{2}%' {3}", prefix, fieldName, queryField.Value[0], addStr);
}
break;
case LibQueryChar.Region:
if (queryField.Value.Count == 2)
{
if (needQuot)
{
builder.AppendFormat("{0}{1} between {2} and {3} {4}", prefix, fieldName, LibStringBuilder.GetQuotObject(queryField.Value[0]), LibStringBuilder.GetQuotObject(queryField.Value[1]), addStr);
}
else
{
builder.AppendFormat("{0}{1} between {2} and {3} {4}", prefix, fieldName, queryField.Value[0], queryField.Value[1], addStr);
}
}
break;
case LibQueryChar.GreaterOrEqual:
if (queryField.Value.Count > 0)
{
if (needQuot)
{
builder.AppendFormat("{0}{1}>={2} {3}", prefix, fieldName, LibStringBuilder.GetQuotObject(queryField.Value[0]), addStr);
}
else
{
builder.AppendFormat("{0}{1}>={2} {3}", prefix, fieldName, queryField.Value[0], addStr);
}
}
break;
case LibQueryChar.LessOrEqual:
if (queryField.Value.Count > 0)
{
if (needQuot)
{
builder.AppendFormat("{0}{1}<={2} {3}", prefix, fieldName, LibStringBuilder.GetQuotObject(queryField.Value[0]), addStr);
}
else
{
builder.AppendFormat("{0}{1}<={2} {3}", prefix, fieldName, queryField.Value[0], addStr);
}
}
break;
case LibQueryChar.GreaterThan:
if (queryField.Value.Count > 0)
{
if (needQuot)
{
builder.AppendFormat("{0}{1}>{2} {3}", prefix, fieldName, LibStringBuilder.GetQuotObject(queryField.Value[0]), addStr);
}
else
{
builder.AppendFormat("{0}{1}>{2} {3}", prefix, fieldName, queryField.Value[0], addStr);
}
}
break;
case LibQueryChar.LessThan:
if (queryField.Value.Count > 0)
{
if (needQuot)
{
builder.AppendFormat("{0}{1}<{2} {3}", prefix, fieldName, LibStringBuilder.GetQuotObject(queryField.Value[0]), addStr);
}
else
{
builder.AppendFormat("{0}{1}<{2} {3}", prefix, fieldName, queryField.Value[0], addStr);
}
}
break;
case LibQueryChar.UnequalTo:
if (queryField.Value.Count > 0)
{
if (needQuot)
{
builder.AppendFormat("{0}{1}<>{2} {3}", prefix, fieldName, LibStringBuilder.GetQuotObject(queryField.Value[0]), addStr);
}
else
{
builder.AppendFormat("{0}{1}<>{2} {3}", prefix, fieldName, queryField.Value[0], addStr);
}
}
break;
case LibQueryChar.Include:
if (queryField.Value.Count > 0)
{
if (needQuot)
{
StringBuilder tempBuilder = new StringBuilder();
string[] dest = queryField.Value[0].ToString().Split(',');
for (int i = 0; i < dest.Length; i++)
{
if (i == 0)
{
tempBuilder.AppendFormat("{0}", LibStringBuilder.GetQuotString(dest[i]));
}
else
{
tempBuilder.AppendFormat(",{0}", LibStringBuilder.GetQuotString(dest[i]));
}
}
builder.AppendFormat("{0}{1} in ({2}) {3}", prefix, fieldName, tempBuilder.ToString(), addStr);
}
else
{
builder.AppendFormat("{0}{1} in ({2}) {3}", prefix, fieldName, queryField.Value[0], addStr);
}
}
break;
}
}
public void ExportRadXMLData(string filePath, DataSet dataSet, HashSet <int> tableIndex = null, bool dbField = false)
{
Stopwatch watch = new Stopwatch();
watch.Start();
#region 文件名，路径
string templateFile = System.IO.Path.Combine(AxCRL.Comm.Runtime.EnvProvider.Default.RuningPath, "TempData", "ExcelModel", "List.xml");
String bodyXML = File.ReadAllText(templateFile, Encoding.UTF8);
#endregion
StringBuilder names = new StringBuilder("");
StringBuilder Worksheet = new StringBuilder("");
for (int index = 0; index < dataSet.Tables.Count; index++)
{
StringBuilder headCols = new StringBuilder("");
StringBuilder rows = new StringBuilder("");
if (tableIndex != null && !tableIndex.Contains(index))
{
continue;
}
System.Data.DataTable dt = dataSet.Tables[index];
string tableName = string.Empty;
if (dbField)
{
tableName = dt.TableName;
}
else
{
if (dt.ExtendedProperties.ContainsKey(TableProperty.DisplayText))
{
tableName = LibSysUtils.ToString(dt.ExtendedProperties[TableProperty.DisplayText]);
}
else
{
tableName = dt.TableName;
}
}
names.AppendLine(String.Format("<NamedRange ss:Name=\"{0}\" ss:RefersTo=\"={0}!R1C1:R{1}C{2}\"/>", tableName, (dt.Rows.Count + 1).ToString(), dt.Columns.Count.ToString()));
//如果存在文本列名相同时则需要此结构
Dictionary <string, int> sameColDic = null;
if (!dbField)
{
sameColDic = new Dictionary <string, int>();
}
#region 填充表头
foreach (DataColumn col in dt.Columns)
{
string name = dbField ? col.ColumnName : string.IsNullOrEmpty(col.Caption) ? col.ColumnName : col.Caption;
if (sameColDic.ContainsKey(name))
{
sameColDic[name]++;
name += sameColDic[name].ToString();
}
else
{
sameColDic.Add(name, 0);
}
headCols.Append(String.Format("<Cell><Data ss:Type=\"String\">{0}</Data><NamedCell ss:Name=\"{1}\"/></Cell>\r\n", name, tableName));
}
#endregion
#region 表格具体内容
string type = string.Empty;
object value = string.Empty;
foreach (DataRow curRow in dt.Rows)
{
if (curRow.RowState == DataRowState.Deleted)
{
continue;
}
StringBuilder builder = new StringBuilder();
#region 填充行的格式
rows.Append("<Row>\r\n");
for (int i = 0; i < dt.Columns.Count; i++)
{
string style = string.Empty;
#region 填充的值和类型
DataColumn col = dt.Columns[i];
LibDataType dataType = (LibDataType)col.ExtendedProperties[FieldProperty.DataType];
LibControlType ctrlType = (LibControlType)col.ExtendedProperties[FieldProperty.ControlType];
switch (dataType)
{
case LibDataType.Text:
case LibDataType.NText:
case LibDataType.Binary:
case LibDataType.Int64:
if (dataType == LibDataType.Int64 && ctrlType == LibControlType.DateTime)
{
long dateTime = LibSysUtils.ToInt64(curRow[col]);
if (dateTime != 0)
{
type = "DateTime";
style = string.Format(" ss:StyleID=\"s23\"");
value = LibDateUtils.LibDateToDateTime(dateTime).ToString("yyyy-MM-ddTHH:mm:ss");
}
else
{
type = "String";
value = string.Empty;
}
}
else
{
type = "String";
value = curRow[col];
}
break;
case LibDataType.Int32:
case LibDataType.Numeric:
case LibDataType.Float:
case LibDataType.Double:
case LibDataType.Byte:
if (dataType == LibDataType.Int32 && ctrlType == LibControlType.Date)
{
int date = LibSysUtils.ToInt32(curRow[col]);
if (date != 0)
{
type = "DateTime";
value = string.Format("{0}T00:00:00.000", LibDateUtils.LibDateToDateTime(date).ToString("yyyy-MM-dd"));
style = string.Format(" ss:StyleID=\"s23\"");
}
else
{
type = "String";
value = string.Empty;
}
}
else if (dataType == LibDataType.Int32 && ctrlType == LibControlType.HourMinute)
{
type = "Number";
string time = LibSysUtils.ToString(curRow[col]);
switch (time.Length)
{
case 1: time = "000" + time + "00"; break;
case 2: time = "00" + time + "00"; break;
case 3: time = "0" + time + "00"; break;
case 4: time = time + "00"; break;
default: time = time + "00"; break;
}
time = "20150101" + time;
value = LibStringBuilder.GetQuotObject(LibDateUtils.LibDateToDateTime(LibSysUtils.ToInt64(time)).ToString("HH:mm"));
}
else if (dataType == LibDataType.Numeric)
{
type = "Number";
style = string.Format(" ss:StyleID=\"s24\"");
value = curRow[col];
}
else
{
type = "Number";
value = curRow[col];
}
break;
case LibDataType.Boolean:
type = "Number";
value = LibSysUtils.ToBoolean(curRow[col.ColumnName]) ? 1 : 0;
break;
}
#endregion
rows.Append(string.Format("<Cell{3}><Data ss:Type=\"{0}\">{1}</Data><NamedCell ss:Name=\"{2}\"/></Cell>\r\n", type, value, tableName, style));
}
rows.Append("</Row>\r\n");
#endregion
}
#endregion
#region 构建表格模板
Worksheet.AppendLine(string.Format("<Worksheet ss:Name=\"{0}\">\n<Table ss:ExpandedColumnCount=\"{1}\" ss:ExpandedRowCount=\"{2}\" x:FullColumns=\"1\" x:FullRows=\"1\" ss:DefaultRowHeight=\"12\">", tableName, dt.Columns.Count.ToString(), (dt.Rows.Count + 1).ToString()));
Worksheet.AppendLine(string.Format(@"<Row>
{0}
</Row>
{1}
</Table>", headCols.ToString(), rows.ToString()));
Worksheet.AppendLine("<WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
Worksheet.AppendLine("<PageSetup>");
Worksheet.AppendLine("<Header x:Data=\"&A\"/>");
Worksheet.AppendLine("<Footer x:Data=\"Page &P\"/>");
Worksheet.AppendLine(@"</PageSetup>
<Selected/>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>");
#endregion
}
#region 将数据替换到模板中
DateTime datetime = DateTime.Now;
bodyXML = bodyXML.Replace("{##Author##}", "Administrator");
bodyXML = bodyXML.Replace("{##Created##}", datetime.ToString());
bodyXML = bodyXML.Replace("{##Names##}", names.ToString());
bodyXML = bodyXML.Replace("{##Worksheet##}", Worksheet.ToString());
#endregion
try
{
string path = filePath;
using (System.IO.FileStream fs = new System.IO.FileStream(path, System.IO.FileMode.Create))
{
using (System.IO.StreamWriter sw = new System.IO.StreamWriter(fs))
{
sw.Write(bodyXML);
}
}
watch.Stop();
string time = watch.ElapsedMilliseconds.ToString();
}
catch (Exception ex)
{
throw ex;
}
}
private List <string> CanFilterFields(string userId = "")
{
List <string> list = new List <string>();
if (string.IsNullOrEmpty(userId))
{
return(FindFilterFieldsByAdmin());
}
else
{
string sql = string.Format("select SEARCHFIELD from AXPRPTSEARCHFIELDDETAIL where RPTSEARCHID in (select top 1 RPTSEARCHID from AXPRPTSEARCHFIELD A where A.PROGID = {0} and A.USERID = {1} and A.ISON = 1 ORDER BY CREATETIME DESC)", LibStringBuilder.GetQuotString(this.ProgId), LibStringBuilder.GetQuotString(userId));
using (IDataReader dr = DataAccess.ExecuteDataReader(sql))
{
while (dr.Read())
{
list.Add(dr[0].ToString());
}
}
}
if (list.Count <= 0)
{
list = FindFilterFieldsByAdmin();
}
return(list);
}
/// <summary>
/// 单点登录。
/// 1.检查当前本站点系统账户中是否存在userId，如果否则不予登录
/// 2.使用userId、loginToken等访问sso管理站点的CheckSSOLoginState方法检验是否已经SSO登录过。
/// 如果未登录过则不予登录。
/// 3.判断当前站点中userId是否已经登录过，如果是则使用对应的LibHandle信息返回，否则则新构造LibHande并返回。
/// </summary>
///<param name="ssoInfo">单点登录信息</param>
/// <returns></returns>
public LoginInfo SSOLogin(SSOInfo ssoInfo)
{
LoginInfo loginInfo = new LoginInfo()
{
IsUsed = true, IsOverUser = false
};
SqlBuilder builder = new SqlBuilder("axp.User");
string sql = builder.GetQuerySql(0, "A.PERSONID,A.PERSONNAME,A.ROLEID,A.WALLPAPER,A.WALLPAPERSTRETCH", string.Format("A.USERID={0} And A.ISUSE=1", LibStringBuilder.GetQuotString(ssoInfo.UserId)));
LibDataAccess dataAccess = new LibDataAccess();
string roleId = string.Empty;
using (IDataReader reader = dataAccess.ExecuteDataReader(sql))
{
if (reader.Read())
{
loginInfo.PersonId = LibSysUtils.ToString(reader[0]);
loginInfo.PersonName = LibSysUtils.ToString(reader[1]);
roleId = LibSysUtils.ToString(reader[2]);
loginInfo.Wallpaper = LibSysUtils.ToString(reader[3]);
loginInfo.Stretch = LibSysUtils.ToBoolean(reader[4]);
loginInfo.IsUsed = false;
}
else
{
return(loginInfo);
}
}
if (CheckToken(ssoInfo))
{
// 授权成功
LibHandle handle = LibHandleCache.Default.IsExistsHandle(LibHandeleType.PC, ssoInfo.UserId);
if (handle == null)
{
handle = LibHandleCache.Default.GetHandle(string.Empty, LibHandeleType.PC, ssoInfo.UserId, loginInfo.PersonId, loginInfo.PersonName, roleId);
}
loginInfo.Handle = handle.Handle;
return(loginInfo);
}
return(loginInfo);
}
/// <summary>
/// 删除索引
/// </summary>
/// <param name="fileInfo"></param>
/// <returns></returns>
public bool DeleteIndex(AbstractFileBase fileInfo)
{
try
{
IndexWriter writer = new IndexWriter(FSDirectory.Open(new System.IO.DirectoryInfo(IndexDic)), PanGuAnalyzer, IndexWriter.MaxFieldLength.LIMITED);
writer.DeleteDocuments(new Term("fileId", fileInfo.FileId));
//这两句一定要执行
writer.Optimize();
writer.Close();
LibDataAccess dataAccess = new LibDataAccess();
dataAccess.ExecuteNonQuery(string.Format("update DMDOCUMENT set ISFULLINDEX = 0 where DOCID = {0}", LibStringBuilder.GetQuotString(fileInfo.FileId)));
return(true);
}
catch (Exception)
{
return(false);
}
}
public SetPwdResult SetPassword(string handle, string oldPwd, string newPwd)
{
SetPwdResult result = new SetPwdResult();
LibHandle libHandle = LibHandleCache.Default.GetCurrentHandle(handle) as LibHandle;
if (libHandle == null)
{
throw new Exception("用户句柄无效。");
}
string sql = string.Format("select USERID from AXPUSER where USERID={0} and USERPASSWORD={1}",
LibStringBuilder.GetQuotString(libHandle.UserId), LibStringBuilder.GetQuotString(oldPwd));
LibDataAccess dataAccess = new LibDataAccess();
string userId = LibSysUtils.ToString(dataAccess.ExecuteScalar(sql));
if (string.IsNullOrEmpty(userId))
{
result.Msg = "输入的旧密码与系统不匹配。";
result.Success = false;
}
else
{
dataAccess.ExecuteNonQuery(string.Format("update AXPUSER set USERPASSWORD={2} where USERID={0} and USERPASSWORD={1}",
LibStringBuilder.GetQuotString(libHandle.UserId), LibStringBuilder.GetQuotString(oldPwd),
LibStringBuilder.GetQuotString(newPwd)));
result.Success = true;
}
return(result);
}
/// <summary>
/// 创建索引
/// </summary>
/// <param name="fileInfo"></param>
/// <returns></returns>
public bool CreateIndex(AbstractFileBase fileInfo)
{
try
{
IndexWriter writer = new IndexWriter(FSDirectory.Open(new System.IO.DirectoryInfo(IndexDic)), PanGuAnalyzer, IndexWriter.MaxFieldLength.LIMITED);
//FileStream fs = File.OpenRead(Path.Combine(fileInfo.FilePath, fileInfo.FileName));
//1.创建Document对象
Document doc = new Document();
//2.给doc对象添加Field
doc.Add(new Field("fileId", fileInfo.FileId, Field.Store.YES, Field.Index.NOT_ANALYZED));
//doc.Add(new Field("fileName", fileInfo.FileName, Field.Store.YES, Field.Index.ANALYZED));
//doc.Add(new Field("filePath", fileInfo.FilePath, Field.Store.YES, Field.Index.ANALYZED));
//doc.Add(new Field("createTime", fileInfo.CreateTime, Field.Store.YES, Field.Index.ANALYZED));
//doc.Add(new Field("upLoadPersonId", fileInfo.UpLoadPersonId, Field.Store.YES, Field.Index.ANALYZED));
//doc.Add(new Field("content", new StreamReader(fs, Encoding.UTF8)));
doc.Add(new Field("content", fileInfo.Content, Field.Store.YES, Field.Index.ANALYZED));
//将doc对象写入索引文件
writer.AddDocument(doc);
writer.Optimize();
writer.Commit();
writer.Close();
//修改数据库的
LibDataAccess dataAccess = new LibDataAccess();
dataAccess.ExecuteNonQuery(string.Format("update DMDOCUMENT set ISFULLINDEX = 1 where DOCID = {0}", LibStringBuilder.GetQuotString(fileInfo.FileId)));
return(true);
}
catch (Exception)
{
return(false);
}
}
public void SetWallpaper(string handle, string wallpaper, bool stretch)
{
LibHandle libHandle = LibHandleCache.Default.GetCurrentHandle(handle) as LibHandle;
if (libHandle == null)
{
throw new Exception("用户句柄无效。");
}
LibDataAccess dataAccess = new LibDataAccess();
dataAccess.ExecuteNonQuery(string.Format("update AXPUSER set WALLPAPER={0},WALLPAPERSTRETCH={1} where USERID={2}", LibStringBuilder.GetQuotString(wallpaper), stretch ? 1 : 0, LibStringBuilder.GetQuotString(libHandle.UserId)));
}
public void RemoveFromSaleBillNo(string fromSaleBillNo)
{
//通过销售订单号查找派工单号
string sql = string.Format(@"SELECT DISTINCT F.BILLNO
FROM WORKORDER A
INNER JOIN WORKORDERDETAIL B
ON A.BILLNO = B.BILLNO
INNER JOIN PLSSALESORDER C
ON B.FROMBILLNO = C.BILLNO
INNER JOIN PLSPRODUCEMONTHPLANDETAIL D
ON D.WORKORDERBILLNO = B.BILLNO
INNER JOIN PLSPRODUCEDAYPLANDETAIL E
ON E.FROMBILLNO = D.BILLNO
AND E.FROMROWID = D.ROW_ID
INNER JOIN PPWORKORDER F
ON E.PWORKORDERNO = F.BILLNO
WHERE A.PARENTBILLNO IS NULL AND C.BILLNO = {0} ", LibStringBuilder.GetQuotString(fromSaleBillNo));
LibDataAccess dataAccess = new LibDataAccess();
StringBuilder builder = new StringBuilder();
Int32 index = 1;
using (IDataReader reader = dataAccess.ExecuteDataReader(sql))
{
while (reader.Read())
{
string workOrderNo = LibSysUtils.ToString(reader["BILLNO"]);
HYProduceData hyProduceData = LibHYProduceCache.Default.GetProduceData(workOrderNo);
DataTable dt = hyProduceData.TenWorkRecord.Tables[0];
for (int i = 0; i < dt.Rows.Count; i++)
{
string saleBillNo = LibSysUtils.ToString(dt.Rows[i]["FROMSALEBILLNO"]);
if (saleBillNo == fromSaleBillNo)
{
dt.Rows[i].Delete();
}
}
dt.AcceptChanges();
if (index == 1)
{
builder.AppendFormat(" AND (WORKORDERNO = {0}", LibStringBuilder.GetQuotObject(reader["BILLNO"]));
}
else
{
builder.AppendFormat(" OR WORKORDERNO = {0}", LibStringBuilder.GetQuotObject(reader["BILLNO"]));
}
index++;
}
}
if (builder.Length > 0)
{
builder.Append(")");
sql = string.Format(@"DELETE FROM PPTENWORKRECORD WHERE 1=1 {0} AND FROMSALEBILLNO = '{1}'", builder.ToString(), fromSaleBillNo);
LibDBTransaction trans = dataAccess.BeginTransaction();
try
{
int result = dataAccess.ExecuteNonQuery(sql);
trans.Commit();
}
catch (Exception)
{
trans.Rollback();
}
}
}
public string RecoverPassword(string userId)
{
string error = string.Empty;
if (string.Compare(userId, "admin", true) == 0)
{
error = "账户admin不允许重置密码";
return(error);
}
LibDataAccess dataAccess = new LibDataAccess();
string id = string.Empty, personId = string.Empty, email = string.Empty;
using (IDataReader reader = dataAccess.ExecuteDataReader(string.Format("select A.USERID,A.PERSONID,B.MAIL from AXPUSER A inner join COMPERSON B on B.PERSONID=A.PERSONID where A.USERID={0}",
LibStringBuilder.GetQuotString(userId))))
{
if (reader.Read())
{
id = LibSysUtils.ToString(reader[0]);
personId = LibSysUtils.ToString(reader[1]);
email = LibSysUtils.ToString(reader[2]);
}
}
if (string.IsNullOrEmpty(id))
{
error = "该账号未注册";
}
else if (string.IsNullOrEmpty(email))
{
error = "该账号未关联邮箱，请联系管理员";
}
else
{
Random random = new Random();
string pw = string.Format("{0}{1}", userId, random.Next(1000, 9999));
dataAccess.ExecuteNonQuery(string.Format("update AXPUSER set USERPASSWORD={0} where USERID={1}", LibStringBuilder.GetQuotString(pw), LibStringBuilder.GetQuotString(userId)));
List <AxCRL.Core.Mail.LibMailParam> list = new List <AxCRL.Core.Mail.LibMailParam>();
AxCRL.Core.Mail.LibMailParam param = new AxCRL.Core.Mail.LibMailParam();
param.Content = string.Format("您的账号 {0} 密码已重置。新密码为{1}", userId, pw);
param.MailKind = AxCRL.Core.Mail.LibMailKind.Info;
param.Subject = "智慧工厂账号密码重置";
param.To.Add(LibSysUtils.ToString(personId));
list.Add(param);
ThreadPool.QueueUserWorkItem(LibMailHelper.SendMail, list);
}
return(error);
}
private LibBusinessTask GetBusinessTask(string taskId)
{
SqlBuilder sqlBuilder = new SqlBuilder("axp.ScheduleTask");
string sql = sqlBuilder.GetQuerySql(0, "A.TASKID,A.PROGID,A.BUSINESSTASKID,A.CALENDARID,A.ISJUSTWORKDAY,A.INTERVALTIME,A.EXECDATE,A.REPEATDATEMARK,A.EXECCONDITION,A.TITLE,A.MAINCONTENT,B.EXECTIME",
string.Format("A.TASKID={0}", LibStringBuilder.GetQuotString(taskId)), "B.EXECTIME");
LibDataAccess dataAccess = new LibDataAccess();
LibBusinessTask task = null;
using (IDataReader reader = dataAccess.ExecuteDataReader(sql))
{
while (reader.Read())
{
if (task == null)
{
task = new LibBusinessTask();
task.BusinessTaskId = LibSysUtils.ToString(reader["BUSINESSTASKID"]);
task.CalendarId = LibSysUtils.ToString(reader["CALENDARID"]);
task.ExecCondition = LibSysUtils.ToString(reader["ExecCondition"]);
task.ExecDate = LibSysUtils.ToInt32(reader["EXECDATE"]);
task.IntervalTime = LibSysUtils.ToInt32(reader["INTERVALTIME"]);
task.IsJustWorkDay = LibSysUtils.ToBoolean(reader["ISJUSTWORKDAY"]);
task.ProgId = LibSysUtils.ToString(reader["PROGID"]);
task.Title = LibSysUtils.ToString(reader["TITLE"]);
task.MainContent = LibSysUtils.ToString(reader["MAINCONTENT"]);
task.RepeatDateMark = LibSysUtils.ToInt32(reader["REPEATDATEMARK"]);
task.TaskId = taskId;
}
task.ExecTime.Add(LibSysUtils.ToInt32(reader["EXECTIME"]));
}
}
sql = string.Format("select distinct A.TASKID,A.PERSONID,D.DEPTID,C.USERID from AXPSCHEDULETASKPERSON A " +
"inner join AXPSCHEDULETASK B on B.TASKID=A.TASKID left join COMPERSON D on D.PERSONID=A.PERSONID inner join AXPUSER C on C.PERSONID=A.PERSONID " +
"where B.TASKID={0}", LibStringBuilder.GetQuotString(taskId));
using (IDataReader reader = dataAccess.ExecuteDataReader(sql, false))
{
while (reader.Read())
{
task.Liaison.Add(new LibBusinessTaskLiaison()
{
PersonId = LibSysUtils.ToString(reader["PERSONID"]),
DeptId = LibSysUtils.ToString(reader["DEPTID"]),
UserId = LibSysUtils.ToString(reader["USERID"])
});
}
}
return(task);
}
/// <summary>
/// 一般站点登录SSO管理站点(SSO管理站点才有用，前端不需要访问）
/// </summary>
/// <param name="userId">用户名</param>
/// <param name="pwd">密码</param>
/// <returns></returns>
public string GetTokenByUserId(string userId, string pwd)
{
if (!EnvProvider.Default.IsSSOManageSite)
{
return(string.Empty);
}
LoginInfo loginInfo = new LoginInfo();
SqlBuilder builder = new SqlBuilder("axp.User");
string sql = builder.GetQuerySql(0, "A.PERSONID,A.PERSONNAME,A.ROLEID,A.WALLPAPER,A.WALLPAPERSTRETCH", string.Format("A.USERID={0} And A.USERPASSWORD={1} And A.ISUSE=1", LibStringBuilder.GetQuotString(userId), LibStringBuilder.GetQuotString(pwd)));
LibDataAccess dataAccess = new LibDataAccess();
string roleId = string.Empty;
bool exists = false;
using (IDataReader reader = dataAccess.ExecuteDataReader(sql))
{
if (reader.Read())
{
loginInfo.PersonId = LibSysUtils.ToString(reader[0]);
loginInfo.PersonName = LibSysUtils.ToString(reader[1]);
roleId = LibSysUtils.ToString(reader[2]);
loginInfo.Wallpaper = LibSysUtils.ToString(reader[3]);
loginInfo.Stretch = LibSysUtils.ToBoolean(reader[4]);
exists = true;
}
}
if (exists)
{
LibHandle handle = LibHandleCache.Default.IsExistsHandle(LibHandeleType.PC, userId);
if (handle != null)
{
return(handle.GetToCheckToken());
}
handle = LibHandleCache.Default.GetHandle(string.Empty, LibHandeleType.PC, userId, loginInfo.PersonId, loginInfo.PersonName, roleId);
return(handle.Token);
}
return(string.Empty);
}
public string MoveUserPicture(string progId, string internalId, string fileName)
{
string path = Path.Combine(EnvProvider.Default.RuningPath, "UserPicture", progId, internalId);
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
string orgFilePath = Path.Combine(EnvProvider.Default.RuningPath, "UserPicture", fileName);
DirectoryInfo info = new DirectoryInfo(path);
FileInfo[] files = info.GetFiles();
foreach (var item in files)
{
item.Delete();
}
File.Move(orgFilePath, Path.Combine(path, fileName));
LibDataAccess dataAccess = new LibDataAccess();
LibSqlModel model = LibSqlModelCache.Default.GetSqlModel(progId);
dataAccess.ExecuteNonQuery(string.Format("update {0} set IMGSRC={1} where INTERNALID={2}", model.Tables[0].TableName,
LibStringBuilder.GetQuotString(fileName), LibStringBuilder.GetQuotString(internalId)));
return(fileName);
}
public LoginInfo Login(string userId, string password, bool quitOther = false)
{
LoginInfo loginInfo = new LoginInfo();
SqlBuilder builder = new SqlBuilder("axp.User");
string sql = builder.GetQuerySql(0, "A.PERSONID,A.PERSONNAME,A.ROLEID,A.WALLPAPER,A.WALLPAPERSTRETCH", string.Format("A.USERID={0} And A.USERPASSWORD={1} And A.ISUSE=1", LibStringBuilder.GetQuotString(userId), LibStringBuilder.GetQuotString(password)));
LibDataAccess dataAccess = new LibDataAccess();
string roleId = string.Empty;
bool exists = false;
using (IDataReader reader = dataAccess.ExecuteDataReader(sql))
{
if (reader.Read())
{
loginInfo.PersonId = LibSysUtils.ToString(reader[0]);
loginInfo.PersonName = LibSysUtils.ToString(reader[1]);
roleId = LibSysUtils.ToString(reader[2]);
loginInfo.Wallpaper = LibSysUtils.ToString(reader[3]);
loginInfo.Stretch = LibSysUtils.ToBoolean(reader[4]);
exists = true;
}
}
if (exists)
{
LibHandle handle = LibHandleCache.Default.IsExistsHandle(LibHandeleType.PC, userId);
if (handle != null)
{
if (quitOther)
{
LibHandleCache.Default.RemoveHandle(handle.Handle);
}
else
{
loginInfo.IsUsed = true;
}
}
if (!loginInfo.IsUsed)
{
long currentCount = LibHandleCache.Default.GetCount();
long maxUserCount = (long)LibHandleCache.Default.MaxUserCount;
if (maxUserCount != -1 && maxUserCount < currentCount)
{
loginInfo.IsOverUser = true;
}
else
{
string loginIp = string.Empty;
//Zhangkj20161219 增加LoginIp
System.ServiceModel.OperationContext context = System.ServiceModel.OperationContext.Current;
//对于非WCF的访问context为null
if (context != null)
{
System.ServiceModel.Channels.MessageProperties properties = context.IncomingMessageProperties;
System.ServiceModel.Channels.RemoteEndpointMessageProperty endpoint = properties[System.ServiceModel.Channels.RemoteEndpointMessageProperty.Name] as System.ServiceModel.Channels.RemoteEndpointMessageProperty;
loginIp = endpoint.Address + ":" + endpoint.Port.ToString();
}
//创建新的Handle
handle = LibHandleCache.Default.GetHandle(string.Empty, LibHandeleType.PC, userId, loginInfo.PersonId, loginInfo.PersonName, roleId, loginIp);
if (handle != null)
{
loginInfo.Handle = handle.Handle;
}
}
}
}
return(loginInfo);
}
public void ExportToExcel(string filePath, DataSet dataSet, HashSet <int> tableIndex = null, bool dbField = false)
{
try
{
IList <string> dmlSqlList = new List <string>();
IList <string> sqlList = new List <string>();
for (int index = 0; index < dataSet.Tables.Count; index++)
{
if (tableIndex != null && !tableIndex.Contains(index))
{
continue;
}
System.Data.DataTable table = dataSet.Tables[index];
//如果存在文本列名相同时则需要此结构
Dictionary <string, int> sameColDic = null;
if (!dbField)
{
sameColDic = new Dictionary <string, int>();
}
string columnStr = string.Empty;
StringBuilder columnDefineBuilder = new StringBuilder();
StringBuilder columnBuilder = new StringBuilder();
foreach (DataColumn col in table.Columns)
{
string name = dbField ? col.ColumnName : string.IsNullOrEmpty(col.Caption) ? col.ColumnName : col.Caption;
if (sameColDic.ContainsKey(name))
{
sameColDic[name]++;
name += sameColDic[name].ToString();
}
else
{
sameColDic.Add(name, 0);
}
columnBuilder.AppendFormat("{0},", name);
LibDataType dataType = (LibDataType)col.ExtendedProperties[FieldProperty.DataType];
switch (dataType)
{
case LibDataType.Text:
case LibDataType.NText:
//columnDefineBuilder.AppendFormat("{0} String,", name);
columnDefineBuilder.AppendFormat("{0} memo,", name);
break;
case LibDataType.Int32:
LibControlType ctrlType = (LibControlType)col.ExtendedProperties[FieldProperty.ControlType];
if (ctrlType == LibControlType.Date)
{
columnDefineBuilder.AppendFormat("{0} Date,", name);
}
else if (ctrlType == LibControlType.HourMinute)
{
columnDefineBuilder.AppendFormat("{0} String,", name);
}
else
{
columnDefineBuilder.AppendFormat("{0} Integer,", name);
}
break;
case LibDataType.Int64:
ctrlType = (LibControlType)col.ExtendedProperties[FieldProperty.ControlType];
if (ctrlType == LibControlType.DateTime)
{
columnDefineBuilder.AppendFormat("{0} DateTime,", name);
}
else
{
columnDefineBuilder.AppendFormat("{0} Long,", name);
}
break;
case LibDataType.Numeric:
columnDefineBuilder.AppendFormat("{0} Currency,", name);
break;
case LibDataType.Float:
columnDefineBuilder.AppendFormat("{0} Single,", name);
break;
case LibDataType.Double:
columnDefineBuilder.AppendFormat("{0} Double,", name);
break;
case LibDataType.Byte:
columnDefineBuilder.AppendFormat("{0} Integer,", name);
break;
case LibDataType.Boolean:
columnDefineBuilder.AppendFormat("{0} Integer,", name);
break;
case LibDataType.Binary:
columnDefineBuilder.AppendFormat("{0} memo,", name);
break;
}
}
if (columnBuilder.Length > 0)
{
columnBuilder.Remove(columnBuilder.Length - 1, 1);
columnDefineBuilder.Remove(columnDefineBuilder.Length - 1, 1);
}
columnStr = columnBuilder.ToString();
string tableName = string.Empty;
if (dbField)
{
tableName = table.TableName;
}
else
{
if (table.ExtendedProperties.ContainsKey(TableProperty.DisplayText))
{
tableName = LibSysUtils.ToString(table.ExtendedProperties[TableProperty.DisplayText]);
}
else
{
tableName = table.TableName;
}
}
dmlSqlList.Add(string.Format("CREATE TABLE {0} ({1})", tableName, columnDefineBuilder.ToString()));
foreach (DataRow curRow in table.Rows)
{
if (curRow.RowState == DataRowState.Deleted)
{
continue;
}
StringBuilder builder = new StringBuilder();
for (int i = 0; i < table.Columns.Count; i++)
{
DataColumn col = table.Columns[i];
LibDataType dataType = (LibDataType)col.ExtendedProperties[FieldProperty.DataType];
LibControlType ctrlType = (LibControlType)col.ExtendedProperties[FieldProperty.ControlType];
switch (dataType)
{
case LibDataType.Text:
case LibDataType.NText:
case LibDataType.Binary:
case LibDataType.Int64:
if (dataType == LibDataType.Int64 && ctrlType == LibControlType.DateTime)
{
long dateTime = LibSysUtils.ToInt64(curRow[col]);
if (dateTime != 0)
{
builder.AppendFormat("{0},", LibStringBuilder.GetQuotObject(LibDateUtils.LibDateToDateTime(dateTime).ToString("yyyy-MM-dd HH:mm:ss")));
}
else
{
builder.Append("null,");
}
}
else
{
builder.AppendFormat("{0},", LibStringBuilder.GetQuotObject(curRow[col]));
}
break;
case LibDataType.Int32:
case LibDataType.Numeric:
case LibDataType.Float:
case LibDataType.Double:
case LibDataType.Byte:
if (dataType == LibDataType.Int32 && ctrlType == LibControlType.Date)
{
int date = LibSysUtils.ToInt32(curRow[col]);
if (date != 0)
{
builder.AppendFormat("{0},", LibStringBuilder.GetQuotObject(LibDateUtils.LibDateToDateTime(date).ToLongDateString()));
}
else
{
builder.Append("null,");
}
}
else if (dataType == LibDataType.Int32 && ctrlType == LibControlType.HourMinute)
{
string time = LibSysUtils.ToString(curRow[col]);
switch (time.Length)
{
case 1: time = "000" + time + "00"; break;
case 2: time = "00" + time + "00"; break;
case 3: time = "0" + time + "00"; break;
case 4: time = time + "00"; break;
default: time = time + "00"; break;
}
time = "20150101" + time;
builder.AppendFormat("{0},", LibStringBuilder.GetQuotObject(LibDateUtils.LibDateToDateTime(LibSysUtils.ToInt64(time)).ToString("HH:mm")));
}
else
{
builder.AppendFormat("{0},", curRow[col]);
}
break;
case LibDataType.Boolean:
builder.AppendFormat("{0},", LibSysUtils.ToBoolean(curRow[col.ColumnName]) ? 1 : 0);
break;
}
}
if (builder.Length > 0)
{
builder.Remove(builder.Length - 1, 1);
}
sqlList.Add(string.Format("insert into {0}({1}) values({2})", tableName, columnBuilder, builder.ToString()));
}
}
string connStr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='{0}'; Extended Properties='Excel 8.0;HDR=Yes;IMEX=2,ReadOnly=False'", filePath);
using (OleDbConnection conn = new OleDbConnection(connStr))
{
conn.Open();
try
{
foreach (string sql in dmlSqlList)
{
using (OleDbCommand command = new OleDbCommand(sql, conn))
{
command.ExecuteNonQuery();
}
}
foreach (string sql in sqlList)
{
using (OleDbCommand command = new OleDbCommand(sql, conn))
{
command.ExecuteNonQuery();
}
}
}
finally
{
conn.Close();
}
}
}
catch (Exception ex)
{
string path = System.IO.Path.Combine(AxCRL.Comm.Runtime.EnvProvider.Default.MainPath, "Output", "Error", "Excel", string.Format("{0}.txt", DateTime.Now.Ticks));
using (System.IO.FileStream fs = new System.IO.FileStream(path, System.IO.FileMode.Create))
{
using (System.IO.StreamWriter sw = new System.IO.StreamWriter(fs))
{
sw.Write(ex);
}
}
throw;
}
}
/// <summary>
/// 构造跨站点调用的临时登录
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
public LibHandle GetCrossCallHandle(string userId)
{
if (string.IsNullOrEmpty(userId))
{
return(null);
}
LibHandle handle = new LibHandle(LibHandeleType.CrossSiteCall, userId, "");
SqlBuilder builder = new SqlBuilder("axp.User");
string sql = builder.GetQuerySql(0, "A.PERSONID,A.PERSONNAME,A.ROLEID,A.WALLPAPER,A.WALLPAPERSTRETCH", string.Format("A.USERID={0} And A.ISUSE=1", LibStringBuilder.GetQuotString(userId)));
LibDataAccess dataAccess = new LibDataAccess();
bool exist = false;
using (IDataReader reader = dataAccess.ExecuteDataReader(sql))
{
if (reader.Read())
{
handle.PersonId = LibSysUtils.ToString(reader[0]);
handle.PersonName = LibSysUtils.ToString(reader[1]);
handle.RoleId = LibSysUtils.ToString(reader[2]);
exist = true;
}
}
if (exist)
{
this.Set(handle.Handle, handle);
return(handle);
}
else
{
return(null);
}
}
/// <summary>
/// 将同步数据的配置更新到数据库
/// </summary>
/// <param name="dt"></param>
public static void UpdateSyncDataSetting(DataTable dt)
{
if (dt == null || ExistAxpSyncDataInfo == false || ExistLinkSiteTable == false)
{
return;
}
List <string> sqlList = new List <string>();
string setting = string.Empty;
foreach (DataRow row in dt.Rows)
{
setting = LibSysUtils.ToString(row["SETTINGID"]);
if (string.IsNullOrEmpty(setting))
{
continue;
}
sqlList.Add(string.Format("update AXPSYNCDATASETTING set ISSYNCTO = {0} where SETTINGID = {1}",
LibSysUtils.ToBoolean(row["ISSYNCTO"]) ? 1 : 0, LibStringBuilder.GetQuotString(setting)));
}
LibDataAccess dataAccess = new LibDataAccess();
dataAccess.ExecuteNonQuery(sqlList);
}
