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 List <purSupplyTrace> GetListTrace(string purBillNo, string materialId, Int64 deliveryTime, string supplyUserId)
{
List <purSupplyTrace> traceList = new List <purSupplyTrace>();
StringBuilder builder = new StringBuilder();
StringBuilder sqlWhere = new StringBuilder();
string supplierId = string.Empty;
supplierId = supplyLogin.DefautDate.getSupplIer(supplyUserId);
sqlWhere.AppendFormat(" A.SUPPLYUSERID = '{0}' AND A.SUPPLIERID = '{1}' ", supplyUserId, supplierId);
#region 构建Sql语句
if (purBillNo != "")
{
if (materialId != "")
{
if (deliveryTime != 0)
{
sqlWhere.AppendFormat(" AND PURBILLNO = '{0}' AND MATERIALID = '{1}' AND DELIVERYTIME = '{2}'", purBillNo, materialId, deliveryTime);
}
else
{
sqlWhere.AppendFormat(" AND PURBILLNO = '{0}' AND MATERIALID = '{1}' ", purBillNo, materialId);
}
}
else
{
if (deliveryTime != 0)
{
sqlWhere.AppendFormat(" AND PURBILLNO = '{0}' AND DELIVERYTIME = '{1}'", purBillNo, deliveryTime);
}
else
{
sqlWhere.AppendFormat(" AND PURBILLNO = '{0}' ", purBillNo);
}
}
}
else
{
if (materialId != "")
{
if (deliveryTime != 0)
{
sqlWhere.AppendFormat(" AND MATERIALID = '{0}' AND DELIVERYTIME = '{1}'", materialId, deliveryTime);
}
else
{
sqlWhere.AppendFormat(" AND MATERIALID = '{0}' ", materialId);
}
}
else
{
if (deliveryTime != 0)
{
sqlWhere.AppendFormat(" AND DELIVERYTIME = '{0}'", deliveryTime);
}
}
}
#endregion
builder.AppendFormat(@"SELECT A.PURBILLNO ,
A.PERSONID ,
B.PERSONNAME ,
A.SUPPLIERID ,
C.SUPPLIERNAME ,
A.SUPPLYUSERID ,
D.PERSONNAME AS SUPPLYUSERNAME ,
D.PHONENO ,
A.MATERIALID ,
E.MATERIALNAME ,
A.QUANTITY ,
A.DELIVERYTIME ,
A.CYCLETIME ,
A.PLANSTARTTIME ,
A.REALSTARTTIME ,
A.PLANENDTIME ,
A.REALENDTIME ,
A.PLANINWARETIME ,
A.REALINWARETIME ,
A.PLANSENDTIME ,
A.REALSENDTIME ,
A.STOCKQTY ,
A.INWAYQTY
FROM PURSUPPLYTRACE A
LEFT JOIN COMPERSON B ON B.PERSONID = A.PERSONID
LEFT JOIN COMPERSON D ON D.PERSONID = A.SUPPLYUSERID
LEFT JOIN COMSUPPLIER C ON C.SUPPLIERID = A.SUPPLIERID
LEFT JOIN COMMATERIAL E ON E.MATERIALID = A.MATERIALID WHERE {0}", sqlWhere);
using (IDataReader orderReader = this.DataAccess.ExecuteDataReader(builder.ToString()))
{
while (orderReader.Read())
{
purSupplyTrace PurSupplyTrace = new purSupplyTrace();
//主键
PurSupplyTrace.PurBillNo = LibSysUtils.ToString(orderReader["PURBILLNO"]);
PurSupplyTrace.PersonId = LibSysUtils.ToString(orderReader["PERSONID"]);
PurSupplyTrace.PersonName = LibSysUtils.ToString(orderReader["PERSONNAME"]);
PurSupplyTrace.SupplierId = LibSysUtils.ToString(orderReader["SUPPLIERID"]);
PurSupplyTrace.SupplierName = LibSysUtils.ToString(orderReader["SUPPLIERNAME"]);
PurSupplyTrace.SupplyUserId = LibSysUtils.ToString(orderReader["SUPPLYUSERID"]);
PurSupplyTrace.SupplyUserName = LibSysUtils.ToString(orderReader["SUPPLYUSERNAME"]);
PurSupplyTrace.SupplyUserTel = LibSysUtils.ToString(orderReader["PHONENO"]);
//主键
PurSupplyTrace.MaterialId = LibSysUtils.ToString(orderReader["MATERIALID"]);
PurSupplyTrace.MaterialName = LibSysUtils.ToString(orderReader["MATERIALNAME"]);
PurSupplyTrace.Quantity = LibSysUtils.ToInt32(orderReader["QUANTITY"]);
PurSupplyTrace.DeliveryTime = LibSysUtils.ToInt64(orderReader["DELIVERYTIME"]);
PurSupplyTrace.CycleTime = LibSysUtils.ToInt32(orderReader["CYCLETIME"]);
PurSupplyTrace.PlanStartTime = LibSysUtils.ToInt64(orderReader["PLANSTARTTIME"]);
PurSupplyTrace.RealStartTime = LibSysUtils.ToInt64(orderReader["REALSTARTTIME"]);
PurSupplyTrace.PlanEndTime = LibSysUtils.ToInt64(orderReader["PLANENDTIME"]);
PurSupplyTrace.RealEndTime = LibSysUtils.ToInt64(orderReader["REALENDTIME"]);
PurSupplyTrace.PlanInWareTime = LibSysUtils.ToInt64(orderReader["PLANINWARETIME"]);
PurSupplyTrace.RealInWareTime = LibSysUtils.ToInt64(orderReader["REALINWARETIME"]);
PurSupplyTrace.PlanSendTime = LibSysUtils.ToInt64(orderReader["PLANSENDTIME"]);
PurSupplyTrace.RealSendTime = LibSysUtils.ToInt64(orderReader["REALSENDTIME"]);
PurSupplyTrace.StockQty = LibSysUtils.ToInt32(orderReader["STOCKQTY"]);
PurSupplyTrace.InWareQty = LibSysUtils.ToInt32(orderReader["INWAYQTY"]);
traceList.Add(PurSupplyTrace);
}
}
return(traceList);
}
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;
}
}
public ColumnSchema(DataColumn field, string tableName)
{
this.Name = field.ColumnName;
this.Size = field.MaxLength;
LibDataType libDataType = (LibDataType)field.ExtendedProperties[FieldProperty.DataType];
string defaultValueForamt = "CONSTRAINT [DF_{0}_{1}] DEFAULT {2} ";
switch (libDataType)
{
case LibDataType.Text:
this.ColumnType = LibDbSchema.VARCHAR;
this.DefaultValue = string.Format("('{0}')", LibSysUtils.ToString(field.DefaultValue));
if (this.Size == -1)
{
this._ColumnTypeStr = string.Format("[{0}] [{1}](MAX) NOT NULL ", this.Name, LibDbSchema.VARCHAR);
}
else
{
this._ColumnTypeStr = string.Format("[{0}] [{1}]({2}) NOT NULL ", this.Name, LibDbSchema.VARCHAR, this.Size);
}
this._DefaultValueStr = string.Format(defaultValueForamt, tableName, this.Name, this.DefaultValue);
break;
case LibDataType.NText:
this.ColumnType = LibDbSchema.NVARCHAR;
this.DefaultValue = string.Format("('{0}')", LibSysUtils.ToString(field.DefaultValue));
if (this.Size == -1)
{
this._ColumnTypeStr = string.Format("[{0}] [{1}](MAX) NOT NULL", this.Name, LibDbSchema.NVARCHAR);
}
else
{
this._ColumnTypeStr = string.Format("[{0}] [{1}]({2}) NOT NULL", this.Name, LibDbSchema.NVARCHAR, this.Size);
}
this._DefaultValueStr = string.Format(defaultValueForamt, tableName, this.Name, this.DefaultValue);
break;
case LibDataType.Int32:
this.ColumnType = LibDbSchema.INT;
this.DefaultValue = string.Format("(({0}))", LibSysUtils.ToInt32(field.DefaultValue));
this._ColumnTypeStr = string.Format("[{0}] [{1}] NOT NULL", this.Name, LibDbSchema.INT);
this._DefaultValueStr = string.Format(defaultValueForamt, tableName, this.Name, this.DefaultValue);
break;
case LibDataType.Int64:
this.ColumnType = LibDbSchema.BIGINT;
this.DefaultValue = string.Format("(({0}))", LibSysUtils.ToInt64(field.DefaultValue));
this._ColumnTypeStr = string.Format("[{0}] [{1}] NOT NULL", this.Name, LibDbSchema.BIGINT);
this._DefaultValueStr = string.Format(defaultValueForamt, tableName, this.Name, this.DefaultValue);
break;
case LibDataType.Numeric:
this.ColumnType = LibDbSchema.DECIMAL;
this.DefaultValue = string.Format("(({0}))", LibSysUtils.ToDecimal(field.DefaultValue));
this._ColumnTypeStr = string.Format("[{0}] [{1}](18,9) NOT NULL", this.Name, LibDbSchema.DECIMAL);
this._DefaultValueStr = string.Format(defaultValueForamt, tableName, this.Name, this.DefaultValue);
break;
case LibDataType.Float:
case LibDataType.Double:
this.ColumnType = LibDbSchema.FLOAT;
this.DefaultValue = string.Format("(({0}))", LibSysUtils.ToSingle(field.DefaultValue));
this._ColumnTypeStr = string.Format("[{0}] [{1}] NOT NULL", this.Name, LibDbSchema.FLOAT);
this._DefaultValueStr = string.Format(defaultValueForamt, tableName, this.Name, this.DefaultValue);
break;
case LibDataType.Byte:
this.ColumnType = LibDbSchema.TINYINT;
this.DefaultValue = string.Format("(({0}))", LibSysUtils.ToByte(field.DefaultValue));
this._ColumnTypeStr = string.Format("[{0}] [{1}] NOT NULL", this.Name, LibDbSchema.TINYINT);
this._DefaultValueStr = string.Format(defaultValueForamt, tableName, this.Name, this.DefaultValue);
break;
case LibDataType.Boolean:
this.ColumnType = LibDbSchema.BIT;
this.DefaultValue = string.Format("(({0}))", LibSysUtils.ToInt32(field.DefaultValue));
this._ColumnTypeStr = string.Format("[{0}] [{1}] NOT NULL", this.Name, LibDbSchema.BIT);
this._DefaultValueStr = string.Format(defaultValueForamt, tableName, this.Name, this.DefaultValue);
break;
case LibDataType.Binary:
this.ColumnType = LibDbSchema.BINARY;
this.DefaultValue = string.Format("('{0}')", LibSysUtils.ToString(field.DefaultValue));
this._ColumnTypeStr = string.Format("[{0}] {1} NULL", this.Name, LibDbSchema.BINARY);
this._DefaultValueStr = string.Format(defaultValueForamt, tableName, this.Name, this.DefaultValue);
break;
case LibDataType.DateTime:
this.ColumnType = LibDbSchema.DateTime;
this.DefaultValue = string.Format("('{0}')", LibSysUtils.ToString(field.DefaultValue));
this._ColumnTypeStr = string.Format("[{0}] {1} NULL", this.Name, LibDbSchema.DateTime);
this._DefaultValueStr = string.Format(defaultValueForamt, tableName, this.Name, this.DefaultValue);
break;
case LibDataType.Date:
this.ColumnType = LibDbSchema.DateTime;
this.DefaultValue = string.Format("('{0}')", LibSysUtils.ToString(field.DefaultValue));
this._ColumnTypeStr = string.Format("[{0}] {1} NULL", this.Name, LibDbSchema.Date);
this._DefaultValueStr = string.Format(defaultValueForamt, tableName, this.Name, this.DefaultValue);
break;
case LibDataType.Time:
this.ColumnType = LibDbSchema.Date;
this.DefaultValue = string.Format("('{0}')", LibSysUtils.ToString(field.DefaultValue));
this._ColumnTypeStr = string.Format("[{0}] {1} NULL", this.Name, LibDbSchema.Time);
this._DefaultValueStr = string.Format(defaultValueForamt, tableName, this.Name, this.DefaultValue);
break;
default:
break;
}
}
private void SetTableInfo(DataTable table)
{
if (table.ExtendedProperties.ContainsKey(TableProperty.UsingApproveRow))
{
UsingApproveRow = (bool)table.ExtendedProperties[TableProperty.UsingApproveRow];
}
if (table.ExtendedProperties.ContainsKey(TableProperty.UsingAttachment))
{
UsingAttachment = (bool)table.ExtendedProperties[TableProperty.UsingAttachment];
}
if (table.ParentRelations != null && table.ParentRelations.Count > 0)
{
string parentName = table.ParentRelations[0].ParentTable.TableName;
for (int i = 0; i < table.DataSet.Tables.Count; i++)
{
if (string.Compare(table.DataSet.Tables[i].TableName, parentName, true) == 0)
{
this.ParentIndex = i;
break;
}
}
}
this.Pk = this.GetPk(table);
StringBuilder builder = new StringBuilder();
StringBuilder newRowObj = new StringBuilder();
StringBuilder tempBuilder = new StringBuilder();
int r = 0;
foreach (DataColumn item in table.Columns)
{
if (!_UsingRowNo && item.ColumnName == "ROWNO")
{
_UsingRowNo = true;
}
if (!_UsingRowId && item.ColumnName == "ROW_ID")
{
_UsingRowId = true;
}
if (!this.IsDynamic)
{
if (item.ExtendedProperties.ContainsKey(FieldProperty.IsDynamic))
{
this.IsDynamic = (bool)item.ExtendedProperties[FieldProperty.IsDynamic];
}
}
if (item.ExtendedProperties.ContainsKey(FieldProperty.SubTableIndex))
{
SubTableMap.Add(item.ColumnName, (int)item.ExtendedProperties[FieldProperty.SubTableIndex]);
}
tempBuilder.AppendFormat("name:'{0}'", item.ColumnName);
LibDataType dateType = (LibDataType)item.ExtendedProperties[FieldProperty.DataType];
switch (dateType)
{
case LibDataType.Text:
case LibDataType.NText:
newRowObj.AppendFormat("{0}:'{1}',", item.ColumnName, LibSysUtils.ToString(item.DefaultValue));
break;
case LibDataType.Int32:
tempBuilder.Append(",type:'number'");
newRowObj.AppendFormat("{0}:{1},", item.ColumnName, LibSysUtils.ToInt32(item.DefaultValue));
break;
case LibDataType.Int64:
tempBuilder.Append(",type:'number'");
newRowObj.AppendFormat("{0}:{1},", item.ColumnName, LibSysUtils.ToInt64(item.DefaultValue));
break;
case LibDataType.Numeric:
tempBuilder.Append(",type:'number'");
newRowObj.AppendFormat("{0}:{1},", item.ColumnName, LibSysUtils.ToDecimal(item.DefaultValue));
break;
case LibDataType.Float:
tempBuilder.Append(",type:'number'");
newRowObj.AppendFormat("{0}:{1},", item.ColumnName, LibSysUtils.ToSingle(item.DefaultValue));
break;
case LibDataType.Double:
tempBuilder.Append(",type:'number'");
newRowObj.AppendFormat("{0}:{1},", item.ColumnName, LibSysUtils.ToDouble(item.DefaultValue));
break;
case LibDataType.Byte:
tempBuilder.Append(",type:'number'");
newRowObj.AppendFormat("{0}:{1},", item.ColumnName, LibSysUtils.ToByte(item.DefaultValue));
break;
case LibDataType.Boolean:
tempBuilder.Append(",type:'boolean'");
newRowObj.AppendFormat("{0}:{1},", item.ColumnName, LibSysUtils.ToBoolean(item.DefaultValue) ? "true" : "false");
break;
case LibDataType.Binary:
newRowObj.AppendFormat("{0}:'{1}',", item.ColumnName, LibSysUtils.ToString(item.DefaultValue));
break;
}
if (r == 0)
{
builder.Append("{" + tempBuilder.ToString() + "}");
}
else
{
builder.Append(",{" + tempBuilder.ToString() + "}");
}
r++;
tempBuilder.Length = 0;
}
newRowObj.Remove(newRowObj.Length - 1, 1);
this.Fields = string.Format("[{0}]", builder.ToString());
this.NewRowObj = "{" + newRowObj.ToString() + "}";
}
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);
}
public static LibQueryCondition MergeQueryCondition(DataTable table, LibQueryCondition condition, Dictionary <string, List <LibQueryField> > powerQueryFieldDic)
{
if (powerQueryFieldDic == null || powerQueryFieldDic.Count == 0)
{
return(condition);
}
if (condition == null || condition.QueryFields.Count == 0)
{
condition = new LibQueryCondition();
foreach (var item in powerQueryFieldDic)
{
foreach (var subItem in item.Value)
{
condition.QueryFields.Add(subItem);
}
}
return(condition);
}
List <LibQueryField> addList = new List <LibQueryField>();
List <string> removeList = new List <string>();
//将权限（仅存在一个权限设定，即非or的情况）合并到当前用户的选择条件中
foreach (var powerQuery in powerQueryFieldDic)
{
if (powerQuery.Value.Count == 1)
{
bool exist = false;
LibQueryField other = powerQuery.Value[0];
foreach (var item in condition.QueryFields)
{
if (item.Name == powerQuery.Key)
{
exist = true;
DataColumn col = table.Columns[item.Name];
LibDataType dataType = (LibDataType)col.ExtendedProperties[FieldProperty.DataType];
switch (dataType)
{
case LibDataType.Text:
case LibDataType.NText:
case LibDataType.Binary:
string curStr1 = item.Value[0].ToString();
string otherStr1 = other.Value[0].ToString();
string curStr2 = string.Empty;
string otherStr2 = string.Empty;
if (item.Value.Count == 2)
{
curStr2 = item.Value[1].ToString();
}
if (other.Value.Count == 2)
{
otherStr2 = other.Value[1].ToString();
}
MergeFieldQuery(addList, item, other, curStr1, otherStr1, curStr2, otherStr2);
break;
case LibDataType.Int32:
int curInt1 = LibSysUtils.ToInt32(item.Value[0]);
int otherInt1 = LibSysUtils.ToInt32(other.Value[0]);
int curInt2 = 0;
int otherInt2 = 0;
if (item.Value.Count == 2)
{
curInt2 = LibSysUtils.ToInt32(item.Value[1]);
}
if (other.Value.Count == 2)
{
otherInt2 = LibSysUtils.ToInt32(other.Value[1]);
}
MergeFieldQuery(addList, item, other, curInt1, otherInt1, curInt2, otherInt2);
break;
case LibDataType.Int64:
long curLong1 = LibSysUtils.ToInt64(item.Value[0]);
long otherLong1 = LibSysUtils.ToInt64(other.Value[0]);
long curLong2 = 0;
long otherLong2 = 0;
if (item.Value.Count == 2)
{
curLong2 = LibSysUtils.ToInt64(item.Value[1]);
}
if (other.Value.Count == 2)
{
otherLong2 = LibSysUtils.ToInt64(other.Value[1]);
}
MergeFieldQuery(addList, item, other, curLong1, otherLong1, curLong2, otherLong2);
break;
case LibDataType.Numeric:
decimal curDecimal1 = LibSysUtils.ToDecimal(item.Value[0]);
decimal otherDecimal1 = LibSysUtils.ToDecimal(other.Value[0]);
decimal curDecimal2 = 0;
decimal otherDecimal2 = 0;
if (item.Value.Count == 2)
{
curDecimal2 = LibSysUtils.ToDecimal(item.Value[1]);
}
if (other.Value.Count == 2)
{
otherDecimal2 = LibSysUtils.ToDecimal(other.Value[1]);
}
MergeFieldQuery(addList, item, other, curDecimal1, otherDecimal1, curDecimal2, otherDecimal2);
break;
case LibDataType.Float:
float curFloat1 = LibSysUtils.ToSingle(item.Value[0]);
float otherFloat1 = LibSysUtils.ToSingle(other.Value[0]);
float curFloat2 = 0;
float otherFloat2 = 0;
if (item.Value.Count == 2)
{
curFloat2 = LibSysUtils.ToSingle(item.Value[1]);
}
if (other.Value.Count == 2)
{
otherFloat2 = LibSysUtils.ToSingle(other.Value[1]);
}
MergeFieldQuery(addList, item, other, curFloat1, otherFloat1, curFloat2, otherFloat2);
break;
case LibDataType.Double:
double curDouble1 = LibSysUtils.ToDouble(item.Value[0]);
double otherDouble1 = LibSysUtils.ToDouble(other.Value[0]);
double curDouble2 = 0;
double otherDouble2 = 0;
if (item.Value.Count == 2)
{
curDouble2 = LibSysUtils.ToDouble(item.Value[1]);
}
if (other.Value.Count == 2)
{
otherDouble2 = LibSysUtils.ToDouble(other.Value[1]);
}
MergeFieldQuery(addList, item, other, curDouble1, otherDouble1, curDouble2, otherDouble2);
break;
case LibDataType.Byte:
byte curByte1 = LibSysUtils.ToByte(item.Value[0]);
byte otherByte1 = LibSysUtils.ToByte(other.Value[0]);
byte curByte2 = 0;
byte otherByte2 = 0;
if (item.Value.Count == 2)
{
curByte2 = LibSysUtils.ToByte(item.Value[1]);
}
if (other.Value.Count == 2)
{
otherByte2 = LibSysUtils.ToByte(other.Value[1]);
}
MergeFieldQuery(addList, item, other, curByte1, otherByte1, curByte2, otherByte2);
break;
case LibDataType.Boolean:
item.QueryChar = other.QueryChar;
item.Value = other.Value;
break;
}
break;
}
}
if (!exist)
{
condition.QueryFields.Add(other);
removeList.Add(powerQuery.Key);
}
}
}
foreach (var item in addList)
{
condition.QueryFields.Add(item);
}
//仅添加合并后剩余的权限条件（仅剩下or条件的权限）
foreach (var item in powerQueryFieldDic)
{
if (!removeList.Contains(item.Key))
{
condition.PowerQueryFieldDic.Add(item.Key, item.Value);
}
}
return(condition);
}
/// <summary>
/// 获取滚动计划查询结果
/// </summary>
/// <param name="supplyUserId"></param>
/// <param name="materialId"></param>
/// <param name="personId"></param>
/// <param name="purchaseOrder"></param>
/// <param name="planDate"></param>
/// <returns>list</returns>
public List <purRollPlan> GetListRollPlan(string materialId, string purchaseOrder, string supplyUserId)
{
List <purRollPlan> rollplanlist = new List <purRollPlan>();
StringBuilder builder = new StringBuilder();
string supplierId = string.Empty;
supplierId = supplyLogin.DefautDate.getSupplIer(supplyUserId);
StringBuilder sqlWhere = new StringBuilder();
sqlWhere.AppendFormat(" AND B.SUPPLYUSERID = '{0}' AND B.SUPPLIERID = '{1}' ", supplyUserId, supplierId);
#region 构建SQL条件
if (materialId != "")
{
if (purchaseOrder != "")
{
sqlWhere.AppendFormat(" AND MATERIALID = '{0}' AND PURCHASEORDER = '{1}'", materialId, purchaseOrder);
}
else
{
sqlWhere.AppendFormat(" AND MATERIALID = '{0}'", materialId);
}
}
else
{
if (purchaseOrder != "")
{
sqlWhere.AppendFormat(" AND PURCHASEORDER = '{0}'", purchaseOrder);
}
}
#endregion
builder.AppendFormat(@"SELECT B.BILLNO,
B.ROW_ID,
B.PURCHASEORDER ,
B.PERSONID ,
D.PERSONNAME ,
B.SUPPLIERID,
F.SUPPLIERNAME,
B.SUPPLYUSERID ,
G.PERSONNAME AS SUPPLYUSERNAME ,
G.PHONENO,
B.MATERIALID,
E.MATERIALNAME,
A.PLANDATE,
A.DELIVERYNOTENO,
A.BARCODE,
A.ARRIVEDATE,
A.ARRIVEQUANTITY,
B.WORKNO
FROM dbo.PURSUPPLYROLLPLANPOST A
LEFT JOIN dbo.PURPURCHASEPLANDETAIL B ON A.FROMBILLNO = B.BILLNO
AND A.FROMROWID = B.ROW_ID
LEFT JOIN dbo.PURPURCHASEPLAN C ON B.BILLNO = C.BILLNO
LEFT JOIN dbo.COMPERSON D ON B.PERSONID = D.PERSONID
LEFT JOIN dbo.COMMATERIAL E ON B.MATERIALID = E.MATERIALID
LEFT JOIN dbo.COMSUPPLIER F ON F.SUPPLIERID=B.SUPPLIERID
LEFT JOIN dbo.COMPERSON G ON G.PERSONID = B.PERSONID
WHERE C.CURRENTSTATE = 2
AND B.BILLSTATE = 0
{0} ORDER BY B.PURCHASEORDER", sqlWhere);
using (IDataReader orderReader = this.DataAccess.ExecuteDataReader(builder.ToString()))
{
SortedDictionary <purRollPlan, List <int> > dic = new SortedDictionary <purRollPlan, List <int> >(new purRollPlanCompare());
while (orderReader.Read())
{
purRollPlan key = new purRollPlan()
{
BillNo = LibSysUtils.ToString(orderReader["BILLNO"]),
Row_Id = LibSysUtils.ToInt32(orderReader["ROW_ID"]),
PurBillNo = LibSysUtils.ToString(orderReader["PURCHASEORDER"]),
PurPersonId = LibSysUtils.ToString(orderReader["PERSONID"]),
PurPersonName = LibSysUtils.ToString(orderReader["PERSONNAME"]),
SupplierId = LibSysUtils.ToString(orderReader["SUPPLIERID"]),
SupplierName = LibSysUtils.ToString(orderReader["SUPPLIERNAME"]),
SupplyUserId = LibSysUtils.ToString(orderReader["SUPPLYUSERID"]),
SupplyUserName = LibSysUtils.ToString(orderReader["SUPPLYUSERNAME"]),
MaterialId = LibSysUtils.ToString(orderReader["MATERIALID"]),
MaterialName = LibSysUtils.ToString(orderReader["MATERIALNAME"]),
DeliveryNoteNo = LibSysUtils.ToString(orderReader["DELIVERYNOTENO"]),
Barcode = LibSysUtils.ToString(orderReader["BARCODE"]),
ArriveDate = LibSysUtils.ToInt64(orderReader["ARRIVEDATE"]),
ArriveQuantity = LibSysUtils.ToInt32(orderReader["ARRIVEQUANTITY"]),
SupplyUserTel = LibSysUtils.ToString(orderReader["PHONENO"]),
WorkId = LibSysUtils.ToString(orderReader["WORKNO"]),
};
if (dic.Keys.Contains(key))
{
dic[key].Add(LibSysUtils.ToInt32(orderReader["PLANDATE"]));
}
else
{
dic.Add(key, new List <int>()
{
LibSysUtils.ToInt32(orderReader["PLANDATE"])
});
}
}
foreach (KeyValuePair <purRollPlan, List <int> > item in dic)
{
item.Key.PlanDate = item.Value;
rollplanlist.Add(item.Key);
}
}
return(rollplanlist);
}