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); }