protected override void AfterUpdate()
{
base.AfterUpdate();
DataRow masterRow = this.DataSet.Tables[0].Rows[0];
string sql = string.Format("select PROGID from AXPAPPROVEFLOW where APPROVEFLOWID<>{0} and PROGID={1} and ISAPPROVEROW={2}",
LibStringBuilder.GetQuotObject(masterRow["APPROVEFLOWID"]), LibStringBuilder.GetQuotObject(masterRow["PROGID"]), LibSysUtils.ToBoolean(masterRow["ISAPPROVEROW"]) ? 1 : 0);
if (!string.IsNullOrEmpty(LibSysUtils.ToString(this.DataAccess.ExecuteScalar(sql))))
{
this.ManagerMessage.AddMessage(LibMessageKind.Error, "当前功能已存在审核主数据，不能重复建立");
}
//清除缓存
if (this.BillAction != AxCRL.Bcf.BillAction.AddNew && this.BillAction != AxCRL.Bcf.BillAction.SaveToDraft && this.BillAction != AxCRL.Bcf.BillAction.SubmitDraft)
{
LibApproveFlowCache.Default.RemoveCacheItem(LibSysUtils.ToString(masterRow["PROGID", DataRowVersion.Original]), LibSysUtils.ToBoolean(masterRow["ISAPPROVEROW", DataRowVersion.Original]));
}
}
public void UpdateAttachStruct(LibAttachData attachData, List <string> listSql)
{
if (string.IsNullOrEmpty(attachData.AttachSrc))
{
attachData.AttachSrc = LibCommUtils.GetInternalId().ToString();
StringBuilder builder = new StringBuilder();
foreach (var item in attachData.PkList)
{
if (item.Value.GetType() == typeof(string))
{
builder.AppendFormat("{0} = {1} and ", item.Key, LibStringBuilder.GetQuotObject(item.Value));
}
else
{
builder.AppendFormat("{0} = {1} and ", item.Key, item.Value);
}
}
if (builder.Length > 0)
{
builder.Remove(builder.Length - 4, 4);
}
//更新单据附件关联字段
listSql.Add(string.Format("update {0} set ATTACHMENTSRC='{1}' where {2}", attachData.TableName, attachData.AttachSrc, builder.ToString()));
}
foreach (var item in attachData.AttachList)
{
switch (item.Status)
{
case LibAttachStatus.Add:
listSql.Add(string.Format("insert into AXPATTACHMENTRECORD(BELONGTOID,ORDERID,ORDERNUM,ATTACHMENTNAME,CANUSE) values('{0}',{1},{2},'{3}',1)",
attachData.AttachSrc, item.OrderId, item.OrderNum, item.AttachmentName));
break;
case LibAttachStatus.Modif:
listSql.Add(string.Format("update AXPATTACHMENTRECORD set ORDERNUM={2},ATTACHMENTNAME='{3}' where BELONGTOID='{0}' and ORDERID={1}",
attachData.AttachSrc, item.OrderId, item.OrderNum, item.AttachmentName));
break;
case LibAttachStatus.Delete:
listSql.Add(string.Format("update AXPATTACHMENTRECORD set CANUSE=0 where BELONGTOID='{0}' and ORDERID={1}",
attachData.AttachSrc, item.OrderId));
break;
}
}
}
/// <summary>
/// 获取目录Id列表下的文档Id列表
/// </summary>
/// <param name="dirIdList"></param>
/// <returns></returns>
public List <string> GetDocIdsOfDirList(List <string> dirIdList)
{
for (int i = 0; i < dirIdList.Count; i++)
{
dirIdList[i] = LibStringBuilder.GetQuotObject(dirIdList[i]);//添加‘’
}
string dirIdsStr = string.Join(",", dirIdList);
string sql = " select DOCID from DMDOCUMENT where DIRID in ( " + dirIdsStr + " )";
DataSet ds = this.DataAccess.ExecuteDataSet(sql);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
List <string> docIdList = new List <string>();
foreach (DataRow row in ds.Tables[0].Rows)
{
docIdList.Add(LibSysUtils.ToString(row["DOCID"]));
}
return(docIdList);
}
else
{
return(null);
}
}
protected override void CheckFieldReturn(int tableIndex, string fieldName, object[] curPk, Dictionary <string, object> fieldKeyAndValue, Dictionary <string, object> returnValue)
{
base.CheckFieldReturn(tableIndex, fieldName, curPk, fieldKeyAndValue, returnValue);
if (tableIndex == 1 && fieldName == "PROGID" && curPk != null && curPk.Length == 1)
{
LibBcfBase bcfBase = LibBcfSystem.Default.GetBcfInstance(LibSysUtils.ToString(curPk[0]));
if (bcfBase != null)
{
bcfBase.Template.GetViewTemplate(bcfBase.DataSet);
returnValue.Add("OperatePowerData", BuildPowerInfo(bcfBase.Template.FuncPermission.Permission));
string sql = string.Format("select distinct BUTTONID,BUTTONNAME from AXPFUNCBUTTON where PROGID={0}", LibStringBuilder.GetQuotObject(curPk[0]));
Dictionary <string, string> dic = new Dictionary <string, string>();
using (IDataReader reader = this.DataAccess.ExecuteDataReader(sql, false))
{
while (reader.Read())
{
dic.Add(LibSysUtils.ToString(reader["BUTTONID"]), LibSysUtils.ToString(reader["BUTTONNAME"]));
}
}
returnValue.Add("ButtonPowerData", dic);
}
}
}
protected override void BeforeUpdate()
{
base.BeforeUpdate();
DataTable dt = this.DataSet.Tables[1];
var master = this.DataSet.Tables[0].Rows[0];
List <string> lists = new List <string>();
//string sqlById = string.Format("select count(*) from AXPRPTSEARCHFIELD where RPTSEARCHID = {0}", LibStringBuilder.GetQuotObject(master["RPTSEARCHID"]));
//int num = LibSysUtils.ToInt32(DataAccess.ExecuteScalar(sqlById));
//if (num > 0)
//{
// this.ManagerMessage.AddMessage(LibMessageKind.Error, string.Format("条件代码已经存在"));
//}
int count = 1;
int index = -1;
foreach (DataRow row in dt.Rows)
{
if (row.RowState != DataRowState.Added && row.RowState != DataRowState.Modified && row.RowState != DataRowState.Unchanged)
{
continue;
}
string field = row["SEARCHFIELD"].ToString();
if ((index = lists.IndexOf(field)) >= 0)
{
this.ManagerMessage.AddMessage(LibMessageKind.Error, string.Format("选择字段不能相同, 但第{0}行和第{1}行相同了", index + 1, count));
}
else
{
lists.Add(field);
}
count++;
}
if (this.ManagerMessage.IsThrow)
{
return;
}
// 删除 该用户的 该功能的 数据库
if (master.RowState == DataRowState.Added)
{
List <string> rptSearchId = new List <string>();
string sql = string.Format("select RPTSEARCHID from AXPRPTSEARCHFIELD where PROGID={0} and USERID={1}", LibStringBuilder.GetQuotObject(master["PROGID"]), LibStringBuilder.GetQuotObject(master["USERID"]));
using (var dr = DataAccess.ExecuteDataReader(sql))
{
while (dr.Read())
{
rptSearchId.Add(dr["RPTSEARCHID"].ToString());
}
}
if (rptSearchId.Count > 0)
{
List <string> sqls = new List <string>();
foreach (var item in rptSearchId)
{
string delSql = string.Format("delete AXPRPTSEARCHFIELD where RPTSEARCHID = {0}", LibStringBuilder.GetQuotString(item));
string delSql_child = string.Format("delete AXPRPTSEARCHFIELDDETAIL where RPTSEARCHID = {0}", LibStringBuilder.GetQuotString(item));
sqls.Add(delSql);
sqls.Add(delSql_child);
}
DataAccess.ExecuteNonQuery(sqls);
}
}
}
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;
}
}
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>
protected override void BeforeUpdate()
{
DataRow masterRow = this.DataSet.Tables[0].Rows[0];
// 不能出现相同的功能配置
string sql = string.Format("select PROGID from AXPBUSINESSFLOWCONFIG where BUSINESSFLOWCONFIGID<>{0} and PROGID={1}", LibStringBuilder.GetQuotObject(masterRow["BUSINESSFLOWCONFIGID"]), LibStringBuilder.GetQuotObject(masterRow["PROGID"]));
if (!string.IsNullOrEmpty(LibSysUtils.ToString(this.DataAccess.ExecuteScalar(sql))))
{
this.ManagerMessage.AddMessage(LibMessageKind.Error, "当前功能已存在单据业务流配置主数据，不能重复建立");
}
//不能出现相同的使用条件;相同的使用条件中不能出现相同的人；发短信或发微信时，接收人的电话或微信须存在
List <string> conditionList = new List <string>();
DataTable subTable = this.DataSet.Tables[1];
foreach (DataRow curRow in subTable.Rows)
{
if (curRow.RowState == DataRowState.Deleted)
{
continue;
}
//不能出现相同的使用条件
string useCondition = LibSysUtils.ToString(curRow["USECONDITION"]);
if (conditionList.Contains(useCondition))
{
this.ManagerMessage.AddMessage(LibMessageKind.Error, string.Format("单据业务流配置规则中行标识{0}的使用条件存在重复", LibSysUtils.ToInt32(curRow["ROW_ID"])));
}
else
{
conditionList.Add(useCondition);
}
#region 相同的使用条件中不能出现相同的人；发短信或发微信时，接收人的电话或微信须存在
List <string> personList = new List <string>();
DataRow[] childRows = curRow.GetChildRows(this.DataSet.Relations[1]);
if (childRows != null)
{
foreach (DataRow childRow in childRows)
{
if (childRow.RowState == DataRowState.Deleted)
{
continue;
}
//相同的使用条件中不能出现相同的人
string personId = LibSysUtils.ToString(childRow["PERSONID"]);
if (personList.Contains(personId))
{
this.ManagerMessage.AddMessage(LibMessageKind.Error, string.Format("单据业务流配置规则中行标识{0}下业务流配置明细中行标识{1}的接收人存在重复",
LibSysUtils.ToInt32(childRow["PARENTROWID"]), LibSysUtils.ToInt32(childRow["ROW_ID"])));
}
else
{
personList.Add(personId);
}
//发短信或发微信时，接收人的电话或微信须存在
string phoneNo = LibSysUtils.ToString(childRow["PHONENO"]);
string weChat = LibSysUtils.ToString(childRow["WECHAT"]);
bool needSms = LibSysUtils.ToBoolean(childRow["NEEDSMS"]);
bool sendWeChat = LibSysUtils.ToBoolean(childRow["SENDWECHAT"]);
if (string.IsNullOrWhiteSpace(weChat) && sendWeChat)
{
this.ManagerMessage.AddMessage(LibMessageKind.Error, string.Format("单据业务流配置规则中行标识{0}下业务流配置明细中行标识{1}的接收人的微信不存在，无法发微信",
LibSysUtils.ToInt32(childRow["PARENTROWID"]), LibSysUtils.ToInt32(childRow["ROW_ID"])));
}
if (string.IsNullOrWhiteSpace(phoneNo) && needSms)
{
this.ManagerMessage.AddMessage(LibMessageKind.Error, string.Format("单据业务流配置规则中行标识{0}下业务流配置明细中行标识{1}的接收人的电话不存在，无法发短信",
LibSysUtils.ToInt32(childRow["PARENTROWID"]), LibSysUtils.ToInt32(childRow["ROW_ID"])));
}
}
}
#endregion
}
}
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 object GetValueByName(string progId, object[] pks, string name)
{
object value = null;
string key = BuildCacheKey(progId, pks);
Dictionary <string, object> destObj = this.Get <Dictionary <string, object> >(key);
if (destObj == null)
{
destObj = new Dictionary <string, object>();
LibSqlModel sqlModel = LibSqlModelCache.Default.GetSqlModel(progId);
DataColumnCollection columns = sqlModel.Tables[0].Columns;
//说明缓存不存在则需创建
StringBuilder whereBuilder = new StringBuilder();
for (int i = 0; i < sqlModel.Tables[0].PrimaryKey.Length; i++)
{
if (i != 0)
{
whereBuilder.AppendFormat(" AND ");
}
if (pks[i].GetType() == typeof(string))
{
whereBuilder.AppendFormat("A.{0}={1}", sqlModel.Tables[0].PrimaryKey[i].ColumnName, LibStringBuilder.GetQuotObject(pks[i]));
}
else
{
whereBuilder.AppendFormat("A.{0}={1}", sqlModel.Tables[0].PrimaryKey[i].ColumnName, pks[i]);
}
}
SqlBuilder sqlBuilder = new SqlBuilder(progId);
string sql = sqlBuilder.GetQuerySql(0, "A.*", whereBuilder.ToString());
//TODO固定字段应排除
LibDataAccess dataAccess = new LibDataAccess();
using (IDataReader reader = dataAccess.ExecuteDataReader(sql))
{
if (reader.Read())
{
int count = reader.FieldCount;
for (int i = 0; i < count; i++)
{
string columnName = reader.GetName(i);
object columnValue = reader.GetValue(i);
if (dataAccess.DatabaseType == LibDatabaseType.Oracle && columnValue.GetType() == typeof(decimal))
{ //如果是oracle 数值类型都是number
LibDataType dataType = (LibDataType)((int)columns[columnName].ExtendedProperties[FieldProperty.DataType]);
switch (dataType)
{
case LibDataType.Int32:
columnValue = decimal.ToInt32((decimal)columnValue);
break;
case LibDataType.Int64:
columnValue = decimal.ToInt64((decimal)columnValue);
break;
case LibDataType.Float:
columnValue = decimal.ToSingle((decimal)columnValue);
break;
case LibDataType.Double:
columnValue = decimal.ToDouble((decimal)columnValue);
break;
case LibDataType.Byte:
columnValue = decimal.ToByte((decimal)columnValue);
break;
case LibDataType.Boolean:
columnValue = (decimal)columnValue == decimal.Zero ? false : true;
break;
}
}
destObj.Add(columnName, columnValue);
}
}
}
if (destObj.Count > 0)
{
//CacheItemPolicy policy = new CacheItemPolicy();
//policy.SlidingExpiration = new TimeSpan(0, 180, 0); //30分钟内不访问自动剔除
_Default.Set(key, destObj, new TimeSpan(0, 180, 0));
}
}
destObj.TryGetValue(name, out value);
return(value);
}
/// <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 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;
}
}