public void r101Implementation(SystemMenuModel instance)
{
System.Collections.ArrayList mass = new System.Collections.ArrayList();
System.Collections.ArrayList key1 = new System.Collections.ArrayList();
System.Collections.ArrayList key2 = new System.Collections.ArrayList();
System.Collections.ArrayList key3 = new System.Collections.ArrayList();
using (SqlText sql = new SqlText(String.Format("SELECT PageName, PageDescription, PageRoles, PageUrl FROM SystemMenu")))
{
System.Data.Common.DbDataReader readerSender = sql.ExecuteReader();
while (readerSender.Read())
{
//Web.Sitemap
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load(HttpContext.Current.Server.MapPath("~/Web.sitemap"));
XmlNodeList elementList = xmlDoc.GetElementsByTagName("siteMapNode");
foreach (XmlNode node in elementList)
{
if (node.Attributes["title"].Value == readerSender["PageName"].ToString())
{
XmlAttribute description = xmlDoc.CreateAttribute("description");
node.Attributes.Append(description);
node.Attributes["description"].Value = readerSender["PageDescription"].ToString();
XmlAttribute url = xmlDoc.CreateAttribute("url");
node.Attributes.Append(url);
node.Attributes["url"].Value = readerSender["PageUrl"].ToString();
XmlAttribute roles = xmlDoc.CreateAttribute("roles");
node.Attributes.Append(roles);
node.Attributes["roles"].Value = readerSender["PageRoles"].ToString();
xmlDoc.Save(HttpContext.Current.Server.MapPath("~/Web.sitemap"));
}
}
//Web.config
xmlDoc = new XmlDocument();
xmlDoc.Load(HttpContext.Current.Server.MapPath("~/web.config"));
bool pathXml = true;
elementList = xmlDoc.GetElementsByTagName("location");
foreach (XmlNode node in elementList)
{
if (node.Attributes["path"].Value == readerSender["PageUrl"].ToString().Substring(2))
{
//node.InnerXml
pathXml = false;
string rp = "<system.web><authorization><allow roles=\"" + readerSender["PageRoles"].ToString() + "\" /><deny users=\" * \" /></authorization></system.web>";
node.InnerXml = rp;
xmlDoc.Save(HttpContext.Current.Server.MapPath("~/web.config"));
}
}
if (pathXml && readerSender["PageRoles"].ToString() != "*")
{
XmlElement locationNode = xmlDoc.CreateElement("location");
locationNode.SetAttribute("path", readerSender["PageUrl"].ToString().Substring(2));
XmlNodeList elementListConfig = xmlDoc.GetElementsByTagName("configuration");
XmlElement swNode = xmlDoc.CreateElement("system.web");
locationNode.AppendChild(swNode);
XmlElement authorizationNode = xmlDoc.CreateElement("authorization");
swNode.AppendChild(authorizationNode);
XmlElement allowNode = xmlDoc.CreateElement("allow");
allowNode.SetAttribute("roles", readerSender["PageRoles"].ToString());
authorizationNode.AppendChild(allowNode);
XmlElement denyNode = xmlDoc.CreateElement("deny");
denyNode.SetAttribute("users", " * ");
authorizationNode.AppendChild(denyNode);
foreach (XmlNode nodeConfig in elementListConfig)
{
nodeConfig.AppendChild(locationNode);
xmlDoc.Save(HttpContext.Current.Server.MapPath("~/web.config"));
}
}
}
readerSender.Close();
readerSender.Dispose();
}
}
} // End Sub WriteArray
public virtual void SerializeDataTableAsAssociativeJsonArray(System.Data.Common.DbCommand cmd, System.IO.Stream output, bool pretty, System.Text.Encoding enc)
{
Newtonsoft.Json.JsonSerializer ser = new Newtonsoft.Json.JsonSerializer();
using (System.IO.TextWriter sw = new System.IO.StreamWriter(output, enc))
{
using (Newtonsoft.Json.JsonTextWriter jsonWriter = new Newtonsoft.Json.JsonTextWriter(sw))
{
if (pretty)
{
jsonWriter.Formatting = Newtonsoft.Json.Formatting.Indented;
}
// jsonWriter.WriteStartObject();
// jsonWriter.WritePropertyName("tables");
// jsonWriter.WriteStartArray();
using (System.Data.Common.DbConnection con = this.Connection)
{
cmd.Connection = con;
if (con.State != System.Data.ConnectionState.Open)
{
con.Open();
}
try
{
using (System.Data.Common.DbDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.SequentialAccess
| System.Data.CommandBehavior.CloseConnection
))
{
do
{
// jsonWriter.WriteStartObject(); // tbl = new Table();
//jsonWriter.WritePropertyName("columns");
//// WriteArray(jsonWriter, dr);
//WriteAssociativeArray(jsonWriter, dr);
//jsonWriter.WritePropertyName("rows");
jsonWriter.WriteStartArray();
if (dr.HasRows)
{
string[] columns = new string[dr.FieldCount];
for (int i = 0; i < dr.FieldCount; i++)
{
columns[i] = dr.GetName(i);
} // Next i
while (dr.Read())
{
object[] thisRow = new object[dr.FieldCount];
// jsonWriter.WriteStartArray(); // object[] thisRow = new object[dr.FieldCount];
jsonWriter.WriteStartObject(); // tbl = new Table();
for (int i = 0; i < dr.FieldCount; ++i)
{
jsonWriter.WritePropertyName(columns[i]);
object obj = dr.GetValue(i);
if (obj == System.DBNull.Value)
{
obj = null;
}
jsonWriter.WriteValue(obj);
} // Next i
// jsonWriter.WriteEndArray(); // tbl.Rows.Add(thisRow);
jsonWriter.WriteEndObject();
} // Whend
} // End if (dr.HasRows)
jsonWriter.WriteEndArray();
// jsonWriter.WriteEndObject(); // ser.Tables.Add(tbl);
} while (dr.NextResult());
} // End using dr
}
catch (System.Exception ex)
{
System.Console.WriteLine(ex.Message);
throw;
}
if (con.State != System.Data.ConnectionState.Closed)
{
con.Close();
}
} // End using con
// jsonWriter.WriteEndArray();
// jsonWriter.WriteEndObject();
jsonWriter.Flush();
} // End Using jsonWriter
} // End Using sw
} // End Sub SerializeDataTableAsAssociativeJsonArray
} // End Function GetClass
public virtual System.Collections.Generic.List <T> GetList <T>(System.Data.IDbCommand cmd)
{
System.Collections.Generic.List <T> lsReturnValue = new System.Collections.Generic.List <T>();
T tThisValue = default(T);
System.Type tThisType = typeof(T);
lock (cmd)
{
using (System.Data.Common.DbDataReader rdr = ExecuteReader(cmd))
{
lock (rdr)
{
if (IsSimpleType(tThisType))
{
while (rdr.Read())
{
object objVal = rdr.GetValue(0);
// tThisValue = System.Convert.ChangeType(objVal, T),
tThisValue = (T)ConvertResult <T>(objVal, true);
lsReturnValue.Add(tThisValue);
} // End while (idr.Read())
}
else
{
int iFieldCount = rdr.FieldCount;
Setter_t <T>[] mems = new Setter_t <T> [iFieldCount];
for (int i = 0; i < iFieldCount; ++i)
{
string strName = rdr.GetName(i);
mems[i] = LinqHelper.GetSetter <T>(strName);
} // Next i
while (rdr.Read())
{
tThisValue = System.Activator.CreateInstance <T>();
for (int i = 0; i < iFieldCount; ++i)
{
Setter_t <T> setter = mems[i];
if (setter != null)
{
object objVal = rdr.GetValue(i);
setter(tThisValue, objVal);
}
} // Next i
lsReturnValue.Add(tThisValue);
} // Whend
} // End if IsSimpleType(tThisType)
rdr.Close();
} // End Lock rdr
} // End Using rdr
} // End lock cmd
return(lsReturnValue);
} // End Function GetList
} // End Sub MultipleLargeDataSets
public static void MultipleLargeDataSets(System.IO.Stream strm, string strSQL)
{
Newtonsoft.Json.JsonSerializer serializer = new Newtonsoft.Json.JsonSerializer();
using (System.IO.StreamWriter output = new System.IO.StreamWriter(strm))
{
using (Newtonsoft.Json.JsonTextWriter jsonWriter = new Newtonsoft.Json.JsonTextWriter(output))
{
jsonWriter.Formatting = Newtonsoft.Json.Formatting.Indented;
jsonWriter.WriteStartObject();
jsonWriter.WritePropertyName("Tables");
jsonWriter.WriteStartArray();
using (System.Data.Common.DbDataReader dr = SQL.ExecuteReader(strSQL
, System.Data.CommandBehavior.CloseConnection
| System.Data.CommandBehavior.SequentialAccess
))
{
do
{
jsonWriter.WriteStartObject(); // tbl = new Table();
jsonWriter.WritePropertyName("Columns");
jsonWriter.WriteStartArray();
for (int i = 0; i < dr.FieldCount; ++i)
{
jsonWriter.WriteStartObject();
jsonWriter.WritePropertyName("ColumnName");
jsonWriter.WriteValue(dr.GetName(i));
jsonWriter.WritePropertyName("FieldType");
jsonWriter.WriteValue(dr.GetFieldType(i).AssemblyQualifiedName);
jsonWriter.WriteEndObject();
} // Next i
jsonWriter.WriteEndArray();
jsonWriter.WritePropertyName("Rows");
jsonWriter.WriteStartArray();
if (dr.HasRows)
{
while (dr.Read())
{
object[] thisRow = new object[dr.FieldCount];
jsonWriter.WriteStartArray(); // object[] thisRow = new object[dr.FieldCount];
for (int i = 0; i < dr.FieldCount; ++i)
{
jsonWriter.WriteValue(dr.GetValue(i));
} // Next i
jsonWriter.WriteEndArray(); // tbl.Rows.Add(thisRow);
} // Whend
} // End if (dr.HasRows)
jsonWriter.WriteEndArray();
jsonWriter.WriteEndObject(); // ser.Tables.Add(tbl);
} while (dr.NextResult());
} // End Using dr
jsonWriter.WriteEndArray();
jsonWriter.WriteEndObject();
jsonWriter.Flush();
output.Flush();
output.BaseStream.Flush();
output.Close();
// context.Response.Output.Flush();
// context.Reponse.OutputStream.Flush();
// context.Response.Flush();
} // End Using jsonWriter
} // End using output
} // End Sub MultipleLargeDataSets
public IEnumerable <DataTransferObject.Foundation.BasicPriceView> QueryBasicPrice(string airline, string departure, string arrival, Core.Pagination pagination)
{
IList <DataTransferObject.Foundation.BasicPriceView> result = new List <DataTransferObject.Foundation.BasicPriceView>();
string fields = "[Id],[Airline],[Departure],[Arrival],[FlightDate],[ETDZDate],[Price],[Mileage],ModifyTime";
string catelog = "[dbo].[T_BasicPrice]";
string orderbyFiled = "[Airline] DESC,[FlightDate] DESC,[Departure],[Arrival],ModifyTime desc";
string where = "";
if (!string.IsNullOrWhiteSpace(airline))
{
where += "([Airline] IS NULL OR [Airline] = '" + airline + "') AND ";
}
if (!string.IsNullOrWhiteSpace(departure) || !string.IsNullOrWhiteSpace(arrival))
{
if (!string.IsNullOrWhiteSpace(departure) && string.IsNullOrWhiteSpace(arrival))
{
where += "([Departure] = '" + departure + "' OR [Arrival] = '" + departure + "') AND ";
}
if (!string.IsNullOrWhiteSpace(arrival) && string.IsNullOrWhiteSpace(departure))
{
where += "([Departure] = '" + arrival + "' OR [Arrival] = '" + arrival + "') AND ";
}
if (!string.IsNullOrWhiteSpace(arrival) && !string.IsNullOrWhiteSpace(departure))
{
where += "(([Departure] = '" + arrival + "' AND [Arrival] = '" + departure + "') OR ([Departure] = '" + departure + "' AND [Arrival] = '" + arrival + "')) AND ";
}
}
if (where.Length > 0)
{
where = where.Remove(where.Length - 5, 5);
}
using (DbOperator dbOperator = new DbOperator(Provider, ConnectionString))
{
dbOperator.AddParameter("@iField", fields);
dbOperator.AddParameter("@iCatelog", catelog);
dbOperator.AddParameter("@iCondition", where);
dbOperator.AddParameter("@iOrderBy", orderbyFiled);
dbOperator.AddParameter("@iPagesize", pagination.PageSize);
dbOperator.AddParameter("@iPageIndex", pagination.PageIndex);
dbOperator.AddParameter("@iGetCount", pagination.GetRowCount);
System.Data.Common.DbParameter totalCount = dbOperator.AddParameter("@oTotalCount");
totalCount.DbType = System.Data.DbType.Int32;
totalCount.Direction = System.Data.ParameterDirection.Output;
using (System.Data.Common.DbDataReader reader = dbOperator.ExecuteReader("dbo.P_Pagination", System.Data.CommandType.StoredProcedure))
{
while (reader.Read())
{
DataTransferObject.Foundation.BasicPriceView basicPriceView = new DataTransferObject.Foundation.BasicPriceView();
basicPriceView.Id = reader.GetGuid(0);
basicPriceView.Airline = reader.IsDBNull(1) ? string.Empty : reader.GetString(1);
basicPriceView.Departure = reader.GetString(2);
basicPriceView.Arrival = reader.GetString(3);
basicPriceView.FlightDate = reader.GetDateTime(4);
basicPriceView.ETDZDate = reader.GetDateTime(5);
basicPriceView.Price = reader.GetDecimal(6);
basicPriceView.Mileage = reader.GetDecimal(7);
basicPriceView.ModifyTime = reader.IsDBNull(8) ? new DateTime(2013, 10, 26, 0, 0, 0) : reader.GetDateTime(8);
result.Add(basicPriceView);
}
}
if (pagination.GetRowCount)
{
pagination.RowCount = (int)totalCount.Value;
}
}
return(result);
}
} // End Sub SaveAssembly
public static void SaveAssembly2(string assemblyName, string destinationPath)
{
string sql = @"SELECT af.name, af.content FROM sys.assemblies a INNER JOIN sys.assembly_files af ON a.assembly_id = af.assembly_id WHERE a.name = @assemblyname";
using (System.Data.Common.DbConnection conn = new System.Data.SqlClient.SqlConnection("context connection=true")) //Create current context connection
{
using (System.Data.Common.DbCommand cmd = new System.Data.SqlClient.SqlCommand(sql, (System.Data.SqlClient.SqlConnection)conn))
{
System.Data.Common.DbParameter param = new System.Data.SqlClient.SqlParameter("@assemblyname", System.Data.SqlDbType.NVarChar);
param.Value = assemblyName;
// param.Size = 128;
cmd.Parameters.Add(param);
using (System.IO.Stream fs = new System.IO.FileStream("logo" + "pub_id" + ".bmp", System.IO.FileMode.OpenOrCreate, System.IO.FileAccess.Write))
{
using (System.IO.BinaryWriter bw = new System.IO.BinaryWriter(fs))
{
long startIndex = 0;
var buffer = new byte[1024];
int bufferSize = buffer.Length;
if (cmd.Connection.State != System.Data.ConnectionState.Open)
{
cmd.Connection.Open(); //Open the context connetion
}
using (System.Data.Common.DbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read()) //Iterate through assembly files
{
string assemblyFileName = reader.GetString(0); //get assembly file name from the name (first) column
long retval = reader.GetBytes(1, startIndex, buffer, 0, bufferSize);
// Continue reading and writing while there are bytes beyond the size of the buffer.
while (retval == bufferSize)
{
bw.Write(buffer);
bw.Flush();
// Reposition the start index to the end of the last buffer and fill the buffer.
startIndex += bufferSize;
retval = reader.GetBytes(1, startIndex, buffer, 0, bufferSize);
} // Whend
// Write the remaining buffer.
bw.Write(buffer, 0, (int)retval);
bw.Flush();
bw.Close();
} // Whend reader.Read
} // End Using reader
} // End using bw
fs.Flush();
fs.Close();
} // End using fs
} // End using cmd
if (conn.State != System.Data.ConnectionState.Closed)
{
conn.Close();
}
} // End Using conn
} // End Sub SaveAssembly2
public ActionStatus SaveHardwareKit(Entity.KitVerifyRequest requestData, bool isEdit)
{
var response = new ActionStatus();
try
{
logger.InfoLog(Constants.ACTION_ENTRY, "HardwareKitRepository.SaveHardwareKit");
var createdHardwareKits = new List <BulkUploadResponse>();
var xmlData = string.Empty;
using (var stringwriter = new System.IO.StringWriter())
{
var serializer = new XmlSerializer(requestData.GetType());
serializer.Serialize(stringwriter, requestData);
xmlData = stringwriter.ToString();
}
using (var sqlDataAccess = new SqlDataAccess(ConnectionString))
{
List <System.Data.Common.DbParameter> parameters = sqlDataAccess.CreateParams(component.helper.SolutionConfiguration.CurrentUserId, component.helper.SolutionConfiguration.Version);
parameters.Add(sqlDataAccess.CreateParameter("data", xmlData.ToString(), DbType.Xml, ParameterDirection.Input));
parameters.Add(sqlDataAccess.CreateParameter("isEdit", (isEdit == false ? 0 : 1), DbType.Boolean, ParameterDirection.Input));
System.Data.Common.DbDataReader dbDataReader = sqlDataAccess.ExecuteReader(sqlDataAccess.CreateCommand("[HardwareKit_AddUpdate]", CommandType.StoredProcedure, null), parameters.ToArray());
while (dbDataReader.Read())
{
var item = new Entity.BulkUploadResponse();
if (dbDataReader["kitGuid"] != null)
{
item.kitGuid = new Guid(dbDataReader["kitGuid"].ToString().ToUpper());
item.kitTypeGuid = new Guid(dbDataReader["kitTypeGuid"].ToString().ToUpper());
item.kitCode = (dbDataReader["kitCode"].ToString());
createdHardwareKits.Add(item);
}
else
{
response.Message = dbDataReader["fieldname"].ToString().ToUpper();
response.Data = "Unable to save Hardware Kit!";
response.Success = false;
}
}
if (createdHardwareKits.Count != 0 || createdHardwareKits.Count == 1)
{
response.Success = true;
response.Message = "Hardware Kit saved successfully!";
response.Data = createdHardwareKits;
}
else
{
response.Success = false;
response.Data = createdHardwareKits;
response.Message = "Unable to save Hardware Kit!";
}
}
logger.InfoLog(Constants.ACTION_EXIT, "HardwareKitRepository.SaveHardwareKit");
}
catch (Exception ex)
{
response.Success = false;
response.Message = ex.Message.ToString();
logger.ErrorLog(Constants.ACTION_EXCEPTION, ex);
}
return(response);
}
public List <SqlVarParameter> GetParameters(DB.Coding.DataUtility dataUtility, SelectStatementInfo selectStatement, ref List <SqlVarParameter> allSqlVarParameters)
{
List <SqlVarParameter> sqlVarParameters = ParseVarName(selectStatement.sqlWithOutSubSelect);
if (sqlVarParameters.Count > 0)
{
string sqlParametersSelect = "select ";
for (int i = 0; i < sqlVarParameters.Count; i++)
{
if (i != 0)
{
sqlParametersSelect += ",";
}
sqlParametersSelect += sqlVarParameters[i].fullName;
}
sqlParametersSelect += " from " + selectStatement.fromClause + " where 1>2 ";
Regex fromReg = new Regex(@"\s+from\s+", RegexOptions.IgnoreCase);
System.Data.Common.DbCommand cmd = dataUtility.GetDbCommand(FormatSql(sqlParametersSelect), dataUtility.con);
System.Data.DataTable dt = new System.Data.DataTable();
System.Data.Common.DbDataReader reader = cmd.ExecuteReader();
dt = reader.GetSchemaTable();
reader.Read();
for (int i = 0; i < dt.Rows.Count; i++)
{
SqlVarParameter parameter = sqlVarParameters[i];
DB.Coding.Field field = new DB.Coding.Field();
field.name = dt.Rows[i]["BaseColumnName"].ToString();
field.structFieldName = dt.Rows[i]["ColumnName"].ToString();
field.allowNull = Convert.ToBoolean(dt.Rows[i]["AllowDBNull"]);
field.csTypeLink = dataUtility.csTypeDic[dt.Rows[i]["DataType"].ToString().Split('.')[1]];
if (dataUtility.dbType == DB.DBType.Sqlite)
{
field.sqlType = dt.Rows[i]["DataTypeName"].ToString();
}
else
{
field.sqlType = reader.GetDataTypeName(i);
}
field.isId = Convert.ToBoolean(dt.Rows[i]["IsKey"]);
field.length = Convert.ToInt32(dt.Rows[i]["ColumnSize"]);
field.localType = dt.Rows[i]["DataType"].ToString();
field.isId = Convert.ToBoolean(dt.Rows[i]["IsKey"]);
field.position = Convert.ToInt32(dt.Rows[i]["ColumnOrdinal"]);
field.dbType = dataUtility.GetDbType(Convert.ToInt32(dt.Rows[i]["ProviderType"]));
//field.hasDefault = dt.Rows[i]["DefaultValue"] != null;
//field.defautlValue = field.hasDefault ? dt.Rows[i]["DefaultValue"].ToString() : null;
if (!string.IsNullOrEmpty(field.sqlType))
{
if (dataUtility.typeDic.ContainsKey(field.sqlType.ToLower()))
{
field.simpleType = dataUtility.typeDic[field.sqlType.ToLower()].simpleType;
}
else
{
field.simpleType = "String";
}
}
else
{
field.simpleType = "";
}
parameter.field = field;
allSqlVarParameters.Add(parameter);
}
reader.Close();
}
//分析子查询里的参数
for (int i = 0; i < selectStatement.selects.Count; i++)
{
GetParameters(dataUtility, selectStatement.selects[i], ref allSqlVarParameters);
}
return(sqlVarParameters);
}
public List <DB.Coding.Field> GetFields(DB.Coding.DataUtility dataUtility, SelectStatementInfo selectStatement)
{
List <DB.Coding.Field> fields = new List <DB.Coding.Field>();
string selectFiledsSql = string.Format("select {0} from {1} where 1>2", selectStatement.selectClause, selectStatement.fromClause);
System.Data.Common.DbCommand cmd = dataUtility.GetDbCommand(FormatSql(selectFiledsSql), dataUtility.con);
System.Data.DataTable dt = new System.Data.DataTable();
System.Data.Common.DbDataReader reader = cmd.ExecuteReader();
dt = reader.GetSchemaTable();
reader.Read();
Regex nameReg = new Regex(@"^[_0-9a-zA-Z]+$");
for (int i = 0; i < dt.Rows.Count; i++)
{
DB.Coding.Field field = new DB.Coding.Field();
field.name = dt.Rows[i]["BaseColumnName"].ToString();
field.structFieldName = dt.Rows[i]["ColumnName"].ToString();
//查看名称是否合法,不合法则重命名.
if (!nameReg.IsMatch(field.name))
{
field.name = "_column" + i.ToString();
}
if (!nameReg.IsMatch(field.structFieldName))
{
field.structFieldName = "_column" + i.ToString();
}
field.allowNull = Convert.ToBoolean(dt.Rows[i]["AllowDBNull"]);
field.csTypeLink = dataUtility.csTypeDic[dt.Rows[i]["DataType"].ToString().Split('.')[1]];
if (dataUtility.dbType == DB.DBType.Sqlite)
{
field.sqlType = dt.Rows[i]["DataTypeName"].ToString();
}
else
{
field.sqlType = reader.GetDataTypeName(i);
}
field.isId = Convert.ToBoolean(dt.Rows[i]["IsKey"]);
field.length = Convert.ToInt32(dt.Rows[i]["ColumnSize"]);
field.localType = dt.Rows[i]["DataType"].ToString();
field.isId = Convert.ToBoolean(dt.Rows[i]["IsKey"]);
field.position = Convert.ToInt32(dt.Rows[i]["ColumnOrdinal"]);
field.dbType = dataUtility.GetDbType(Convert.ToInt32(dt.Rows[i]["ProviderType"]));
//field.hasDefault = dt.Rows[i]["DefaultValue"] != null;
//field.defautlValue = field.hasDefault ? dt.Rows[i]["DefaultValue"].ToString() : null;
//如果没有相对应的数据库类型
if (!string.IsNullOrEmpty(field.sqlType))
{
if (dataUtility.typeDic.ContainsKey(field.sqlType.ToLower()))
{
field.simpleType = dataUtility.typeDic[field.sqlType.ToLower()].simpleType;
}
else
{
switch (field.localType)
{
case "System.Int16": field.simpleType = "Number"; break;
case "System.Int32": field.simpleType = "Number"; break;
case "System.Int64": field.simpleType = "Number"; break;
case "System.UInt16": field.simpleType = "Number"; break;
case "System.UInt32": field.simpleType = "Number"; break;
case "System.UInt64": field.simpleType = "Number"; break;
case "System.Byte": field.simpleType = "Number"; break;
case "System.SByte": field.simpleType = "Number"; break;
case "System.Single": field.simpleType = "Number"; break;
case "System.Decimal": field.simpleType = "Number"; break;
case "System.Double": field.simpleType = "Number"; break;
case "System.TimeSpan": field.simpleType = "Time"; break;
case "System.DateTime:": field.simpleType = "Time"; break;
default: field.simpleType = field.simpleType = "String"; break;
}
}
}
else
{
switch (field.localType)
{
case "System.Int16": field.simpleType = "Number"; break;
case "System.Int32": field.simpleType = "Number"; break;
case "System.Int64": field.simpleType = "Number"; break;
case "System.UInt16": field.simpleType = "Number"; break;
case "System.UInt32": field.simpleType = "Number"; break;
case "System.UInt64": field.simpleType = "Number"; break;
case "System.Byte": field.simpleType = "Number"; break;
case "System.SByte": field.simpleType = "Number"; break;
case "System.Single": field.simpleType = "Number"; break;
case "System.Decimal": field.simpleType = "Number"; break;
case "System.Double": field.simpleType = "Number"; break;
case "System.TimeSpan": field.simpleType = "Time"; break;
case "System.DateTime:": field.simpleType = "Time"; break;
default: field.simpleType = field.simpleType = "String"; break;
}
}
fields.Add(field);
}
reader.Close();
return(fields);
}
public void SerializeLargeDataset(HttpContext context)
{
string strSQL = @"
SELECT TOP 10 * FROM T_Benutzer;
SELECT TOP 10 * FROM T_Benutzergruppen;
-- SELECT * FROM T_Benutzer LIMIT 10;
-- SELECT * FROM T_Benutzergruppen LIMIT 10;
-- SELECT * FROM T_Benutzer OFFSET 0 FETCH NEXT 10 ROWS ONLY;
-- SELECT * FROM T_Benutzergruppen OFFSET 0 FETCH NEXT 10 ROWS ONLY;
";
Newtonsoft.Json.JsonSerializer ser = new Newtonsoft.Json.JsonSerializer();
using (Newtonsoft.Json.JsonTextWriter jsonWriter = new Newtonsoft.Json.JsonTextWriter(context.Response.Output))
{
jsonWriter.Formatting = Newtonsoft.Json.Formatting.Indented;
jsonWriter.WriteStartObject();
jsonWriter.WritePropertyName("Tables");
jsonWriter.WriteStartArray();
using (System.Data.Common.DbConnection con = SQL.CreateConnection())
{
if (con.State != System.Data.ConnectionState.Open)
{
con.Open();
}
using (System.Data.Common.DbCommand cmd = con.CreateCommand())
{
cmd.CommandText = strSQL;
using (System.Data.Common.DbDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.SequentialAccess
| System.Data.CommandBehavior.CloseConnection
))
{
do
{
jsonWriter.WriteStartObject(); // tbl = new Table();
jsonWriter.WritePropertyName("Columns");
jsonWriter.WriteStartArray();
for (int i = 0; i < dr.FieldCount; ++i)
{
jsonWriter.WriteStartObject();
jsonWriter.WritePropertyName("ColumnName");
jsonWriter.WriteValue(dr.GetName(i));
jsonWriter.WritePropertyName("FieldType");
jsonWriter.WriteValue(GetAssemblyQualifiedNoVersionName(dr.GetFieldType(i)));
jsonWriter.WriteEndObject();
} // Next i
jsonWriter.WriteEndArray();
jsonWriter.WritePropertyName("Rows");
jsonWriter.WriteStartArray();
if (dr.HasRows)
{
while (dr.Read())
{
object[] thisRow = new object[dr.FieldCount];
jsonWriter.WriteStartArray(); // object[] thisRow = new object[dr.FieldCount];
for (int i = 0; i < dr.FieldCount; ++i)
{
jsonWriter.WriteValue(dr.GetValue(i));
} // Next i
jsonWriter.WriteEndArray(); // tbl.Rows.Add(thisRow);
} // Whend
} // End if (dr.HasRows)
jsonWriter.WriteEndArray();
jsonWriter.WriteEndObject(); // ser.Tables.Add(tbl);
} while (dr.NextResult());
} // End using dr
} // End using cmd
if (con.State != System.Data.ConnectionState.Closed)
{
con.Close();
}
} // End using con
jsonWriter.WriteEndArray();
jsonWriter.WriteEndObject();
jsonWriter.Flush();
} // End Using jsonWriter
context.Response.Output.Flush();
context.Response.OutputStream.Flush();
context.Response.Flush();
} // End Sub SerializeLargeDataset
private void FillresultsNew <T>(Type currentType, EntityCollection <T> records, System.Data.Common.DbDataReader result) where T : class, IDataEntity
{
var CurrentTypeProperties = currentType.GetProperties().ToList();
while (result.Read())
{
//now iterate through the properties
//if the property has a maped field with Field attribute defined
// we set the property value from that field
//otherwise we se the property value from the field with name same as property's name
T newEntity = (T)Activator.CreateInstance(typeof(T));
for (int i = 0; i < CurrentTypeProperties.Count; i++)
{
var property = CurrentTypeProperties[i];
DataInterface.Attribbutes.Field FieldAttribute = (DataInterface.Attribbutes.Field)(from attr in property.GetCustomAttributes(true) where attr.GetType() == typeof(DataInterface.Attribbutes.Field) select attr).FirstOrDefault();
string _fieldName;
if (FieldAttribute != null)
{
_fieldName = FieldAttribute.Name;
}
else
{
_fieldName = property.Name;
}
try
{
if (result[_fieldName] != DBNull.Value)
{
if (result[_fieldName].GetType() == typeof(Int64))
{
if (property.PropertyType == typeof(string))
{
property.SetValue(newEntity, result[_fieldName].ToString());
}
else
{
property.SetValue(newEntity, Convert.ToInt32(result[_fieldName]));
}
}
else
{
if (result[_fieldName].GetType() == typeof(Int32))
{
if (property.PropertyType == typeof(string))
{
property.SetValue(newEntity, result[_fieldName].ToString());
}
else
{
property.SetValue(newEntity, result[_fieldName]);
}
}
else
{
property.SetValue(newEntity, result[_fieldName]);
}
}
}
}
catch (Exception ex)
{
var aa = ex;
}
}
if (_Encryptor != null)
{
if (newEntity is IEncryptableClass)
{
_Encryptor.Decryptproperties((IEncryptableClass)newEntity);
}
}
records.Add(newEntity);
}
}
} // End Sub SerializeLargeDataset
public void SerializeLargeTable(HttpContext context)
{
Newtonsoft.Json.JsonSerializer ser = new Newtonsoft.Json.JsonSerializer();
using (Newtonsoft.Json.JsonTextWriter jsonWriter = new Newtonsoft.Json.JsonTextWriter(context.Response.Output))
{
jsonWriter.Formatting = Newtonsoft.Json.Formatting.Indented;
jsonWriter.WriteStartObject();
using (System.Data.Common.DbConnection con = SQL.CreateConnection())
{
if (con.State != System.Data.ConnectionState.Open)
{
con.Open();
}
using (System.Data.Common.DbCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT TOP 10000 * FROM T_LOG_SAP_Interface";
using (System.Data.Common.DbDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.SequentialAccess
| System.Data.CommandBehavior.CloseConnection
))
{
jsonWriter.WritePropertyName("Columns");
jsonWriter.WriteStartArray();
for (int i = 0; i < dr.FieldCount; i++)
{
string colName = dr.GetName(i);
System.Type t = dr.GetFieldType(i);
jsonWriter.WriteStartObject();
jsonWriter.WritePropertyName("ColumnName");
jsonWriter.WriteValue(colName);
jsonWriter.WritePropertyName("DataType");
jsonWriter.WriteValue(GetAssemblyQualifiedNoVersionName(t));
// jsonWriter.WritePropertyName("DateTimeMode");
// jsonWriter.WriteValue(column.DateTimeMode.ToString());
jsonWriter.WriteEndObject();
}
jsonWriter.WriteEndArray();
jsonWriter.WritePropertyName("Rows");
jsonWriter.WriteStartArray();
if (dr.HasRows)
{
int fieldCount = dr.FieldCount;
while (dr.Read())
{
jsonWriter.WriteStartArray();
for (int i = 0; i < fieldCount; ++i)
{
object obj = dr.GetValue(i);
jsonWriter.WriteValue(obj);
} // Next i
jsonWriter.WriteEndArray();
jsonWriter.Flush();
context.Response.Output.Flush();
context.Response.Flush();
} // Whend while (dr.Read())
} // End if (dr.HasRows)
dr.Close();
jsonWriter.WriteEndArray();
} // End using dr
} // End using cmd
if (con.State != System.Data.ConnectionState.Closed)
{
con.Close();
}
} // End using con
jsonWriter.WriteEndObject();
jsonWriter.Flush();
context.Response.Output.Flush();
context.Response.OutputStream.Flush();
context.Response.Flush();
} // End Using jsonWriter
} // End Sub SerializeLargeTable
private void DumpTable(db mydb, string database, string table, string outdir)
{
if (excludeTables.Any(c => useRegexpTables ? Regex.IsMatch(table, c) : c == table))
{
return;
}
if (!Directory.Exists(outdir))
{
Directory.CreateDirectory(outdir);
}
string filename = Path.Combine(outdir, table + ".txt");
if (!overwrite)
{
if (File.Exists(filename))
{
return;
}
}
string tablename = db.GetTableName(dbprovider, database, table);
// Check number of rows
string sqlCount = _sql = $"select count(*) from {tablename}";
int rowcount;
object o = mydb.ExecuteScalarSQL(sqlCount);
if (o.GetType() == typeof(long))
{
rowcount = (int)(long)o;
}
else
{
rowcount = (int)o;
}
if (!exportempty && rowcount == 0)
{
return;
}
string sql = GetTableQuery(mydb, tablename, rowcount);
using (StreamWriter sw = new StreamWriter(filename))
{
using (System.Data.Common.DbDataReader reader = mydb.ExecuteReaderSQL(sql))
{
List <int> columns = new List <int>();
for (int i = 0; i < reader.FieldCount; i++)
{
string colname = reader.GetName(i);
if (excludeColumns.Any(c => useRegexpColumns ? Regex.IsMatch(colname, c) : c == colname))
{
continue;
}
if (reader.GetFieldType(i) == typeof(byte[]) && !binaryhex && !binaryfile)
{
continue;
}
columns.Add(i);
}
if (sortColumns)
{
columns = columns
.OrderBy(c => reader.GetName(c))
.Select(c => c)
.ToList();
}
// Write column names
if (header)
{
bool isFirstCol = true;
for (int i = 0; i < columns.Count; i++)
{
if (reader.GetFieldType(i) == typeof(byte[]) && !binaryhex)
{
continue;
}
if (!isFirstCol)
{
sw.Write(separator);
}
isFirstCol = false;
sw.Write(reader.GetName(columns[i]));
}
sw.WriteLine();
}
c_c += columns.Count;
if (maxrows != -1 && rowcount > maxrows)
{
sw.WriteLine($"{rowcount} rows.");
}
else
{
// Write data
int rownum = 0;
while (reader.Read())
{
bool isFirstCol = true;
for (int i = 0; i < columns.Count; i++)
{
if (reader.GetFieldType(i) != typeof(byte[]))
{
if (!isFirstCol)
{
sw.Write(separator);
}
isFirstCol = false;
if (escapecharacters)
{
sw.Write(FixValue(reader.GetValue(columns[i])));
}
else
{
sw.Write(reader.GetValue(columns[i]));
}
}
else
{
if (binaryhex)
{
if (!isFirstCol)
{
sw.Write(separator);
}
isFirstCol = false;
if (!reader.IsDBNull(i))
{
int length = (int)reader.GetBytes(i, 0, null, 0, 0);
byte[] buffer = new byte[length];
reader.GetBytes(i, 0, buffer, 0, length);
sw.Write(FixBinaryValue(buffer));
}
}
if (binaryfile)
{
if (!reader.IsDBNull(i))
{
string filename_data = Path.Combine(outdir, $"{table}_{reader.GetName(i)}_{rownum}.{extension}");
int length = (int)reader.GetBytes(i, 0, null, 0, 0);
byte[] buffer = new byte[length];
reader.GetBytes(i, 0, buffer, 0, length);
WriteBinaryfile(filename_data, buffer);
}
}
}
}
sw.WriteLine();
rownum++;
}
}
reader.Close();
}
}
}
/// <summary>
/// Merges the indexes. All the "synthetic index" magic happens here through SQL queries.
/// </summary>
/// <param name='refTask'>
/// Reference task.
/// </param>
/// <param name='curTask'>
/// Current task.
/// </param>
/// <exception cref='Exception'>
/// throws Exception if not called after a Create().
/// </exception>
internal void MergeIndexes(long refTask, long curTask)
{
if (indexDbConn == null)
{
throw new Exception("Must call Create() prior to MergeIndexes()");
}
string refIndexName = Path.Combine(Utilities.ConfigManager.GetValue("Backups.IndexFolder"), "t" + refTask + ".idx");
string rcurIndexName = Path.Combine(Utilities.ConfigManager.GetValue("Backups.IndexFolder"), "t" + curTask + ".idx");
string attachRefQuery = "ATTACH DATABASE \"" + refIndexName + "\" AS reft";
string attachCurQuery = "ATTACH DATABASE \"" + rcurIndexName + "\" AS curt";
System.Data.Common.DbCommand comm = indexDbConn.CreateCommand();
foreach (string curRootDrive in GetrootDrives())
{
comm.CommandText = attachRefQuery;
comm.ExecuteNonQuery();
comm.CommandText = attachCurQuery;
comm.ExecuteNonQuery();
// step1 : merge unchanged items (only existing in reference backup)
/*string mergeUnchangedQ = "INSERT INTO items"
+" SELECT ri.id, ri.parentid, ri.name, ri.chunk, ri.data"
+" FROM reft.items ri, reft.bchunks rc, reft.rootdrives rrd WHERE ri.chunk = rc.id"
+" AND rc.rootdrive = rrd.id AND rrd.mountpath='"+curRootDrive+"'"
+" AND ri.id NOT IN"
+" (SELECT ci.id FROM curt.items ci, curt.bchunks cc, curt.rootdrives crd WHERE ci.chunk = cc.id"
+" AND cc.rootdrive = crd.id AND crd.mountpath='"+curRootDrive+"')";*/
string mergeUnchangedQ = "INSERT INTO items"
+ " SELECT ri.id, ri.parentid, ri.name, ri.chunk, ri.data"
+ " FROM reft.items ri, reft.bchunks rc, reft.rootdrives rrd WHERE ri.chunk = rc.id"
+ " AND rc.rootdrive = rrd.id AND rrd.mountpath='" + curRootDrive + "'"
+ " AND NOT EXISTS"
+ " (SELECT ci.id FROM curt.items ci, curt.bchunks cc, curt.rootdrives crd WHERE ci.chunk = cc.id"
+ " AND cc.rootdrive = crd.id AND crd.mountpath='" + curRootDrive + "' AND ci.id = ri.id)";
comm.CommandText = mergeUnchangedQ;
int res = comm.ExecuteNonQuery();
Logger.Append(Severity.DEBUG, "MergeIndexes : mergequery1 = " + mergeUnchangedQ + ", inserted " + res + " unchanged items.");
// step2 : merge chunks corresponding to these untouched items
string mergeOldChunks = "INSERT INTO bchunks SELECT rc.rootdrive , rc.id , rc.taskid , rc.name , rc.stor1 , rc.stor2 , rc.stor3"
+ " FROM reft.bchunks rc, reft.rootdrives rrd"
+ " WHERE rc.rootdrive = rrd.id AND rrd.mountpath='" + curRootDrive + "'";
comm.CommandText = mergeOldChunks;
comm.ExecuteNonQuery();
// step3 : merge totally new items (non existent in previous backups so non-existent in merge index)
//string mergeNewQ = "insert into items select * from curt.items";
/*string mergeNewQ = "INSERT INTO items"
+" SELECT ri.id, ri.parentid, ri.name, ri.chunk, ri.data"
+" FROM reft.items ri, reft.bchunks rc, reft.rootdrives rrd WHERE ri.chunk = rc.id"
+" AND rc.rootdrive = rrd.id AND rrd.mountpath='"+curRootDrive+"'"
+" AND ri.id NOT IN"
+" (SELECT ci.id FROM items ci, bchunks cc, rootdrives crd WHERE ci.chunk = cc.id"
+" AND cc.rootdrive = crd.id AND crd.mountpath='"+curRootDrive+"')";*/
string mergeNewQ = "INSERT INTO items"
+ " SELECT ri.id, ri.parentid, ri.name, ri.chunk, ri.data"
+ " FROM curt.items ri, curt.bchunks rc, curt.rootdrives rrd WHERE ri.chunk = rc.id"
+ " AND rc.rootdrive = rrd.id AND rrd.mountpath='" + curRootDrive + "'"
+ " AND ri.id NOT IN"
+ " (SELECT ci.id FROM items ci, bchunks cc, rootdrives crd WHERE ci.chunk = cc.id"
+ " AND cc.rootdrive = crd.id AND crd.mountpath='" + curRootDrive + "')";
comm.CommandText = mergeNewQ;
res = comm.ExecuteNonQuery();
Logger.Append(Severity.DEBUG, "MergeIndexes : mergequery2 (newq) = " + mergeNewQ + ", inserted " + res + " new items.");
//step 4 : merge modified items (oresent in both ref and cur indexes). This is the most complicated/evoluted step as we need to merge the old metadata
// with the new one for partial files (changed blocks tracking)
string getModifiedItemsQ = "SELECT i.id, i.parentid, i.name, i.chunk, i.data"
+ " FROM reft.items i"//, reft.items ri, curt.items ci"
+ " WHERE i.id IN (SELECT id from curt.items)";
//+" AND i.id IN (SELECT id from reft.items)";
comm.CommandText = getModifiedItemsQ;
System.Data.Common.DbDataReader reader = comm.ExecuteReader(System.Data.CommandBehavior.SequentialAccess);
while (reader.Read())
{
}
Logger.Append(Severity.DEBUG, "MergeIndexes : mergequery3 (MODq) = " + getModifiedItemsQ);
}
//string mergeNewChunks = "INSERT INTO bchunks SELECT * FROM curt.bchunks";
//string cleanupChunks = "DELETE FROM bchunks where id not in (SELECT distinct(bchunk) from
//comm.CommandText = mergeNewChunks;
//comm.ExecuteNonQuery();
}
/// <summary>
/// 获取体验券
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static List <Experience> GetExperienceList(string sql)
{
System.Data.Common.DbDataReader dbreader = null;
List <Experience> list = new List <Experience>();
try
{
dbreader = DbHelper.ExecuteReader(sql);
while (dbreader.Read())
{
Experience exp = new Experience();
exp.Id = DBNull.Value != dbreader["Id"] ? Convert.ToInt16(dbreader["Id"].ToString()) : 0;
exp.Name = DBNull.Value != dbreader["EX_NAME"] ? dbreader["EX_NAME"].ToString() : string.Empty;
exp.Type = DBNull.Value != dbreader["EX_TYPE"] ? Convert.ToInt16(dbreader["EX_TYPE"].ToString()) : 0;
exp.Annount = DBNull.Value != dbreader["EX_ANMOUNT"] ? Convert.ToDecimal(dbreader["EX_ANMOUNT"].ToString()) : 0;
exp.Rceharge = DBNull.Value != dbreader["EX_RECHARGE"] ? Convert.ToDecimal(dbreader["EX_RECHARGE"].ToString()) : 0;
exp.Num = DBNull.Value != dbreader["EX_NUM"] ? Convert.ToInt16(dbreader["EX_NUM"].ToString()) : 0;
//if (dbreader["EX_STARTDATE"] != null && !string.IsNullOrEmpty(dbreader["EX_STARTDATE"].ToString()))
// exp.StartDate = Convert.ToDateTime(dbreader["EX_STARTDATE"]);
//if (dbreader["EX_ENDDATE"] != null && !string.IsNullOrEmpty(dbreader["EX_ENDDATE"].ToString()))
// exp.EndDate = Convert.ToDateTime(dbreader["EX_ENDDATE"]);
if (System.DBNull.Value != dbreader["EX_STARTDATE"])
{
exp.StartDate = Convert.ToDateTime(dbreader["EX_STARTDATE"]);
}
else
{
exp.StartDate = DateTime.MinValue;
}
if (System.DBNull.Value != dbreader["EX_ENDDATE"])
{
exp.EndDate = Convert.ToDateTime(dbreader["EX_ENDDATE"]);
}
else
{
exp.EndDate = DateTime.MinValue;
}
//exp.EndDate = System.DBNull.Value != dbreader["EX_ENDDATE"] ? Convert.ToDateTime(dbreader["EX_ENDDATE"]) : DateTime.MinValue;
exp.CreatID = DBNull.Value != dbreader["EX_CREATID"] ? dbreader["EX_CREATID"].ToString() : string.Empty;
exp.Effective = DBNull.Value != dbreader["EX_EFFECTIVE"] ? Convert.ToInt16(dbreader["EX_EFFECTIVE"].ToString()) : 0;
//exp.EffectiveTime = System.DBNull.Value != dbreader["EX_EFFECTIVETIME"] ? Convert.ToDateTime(dbreader["EX_EFFECTIVETIME"]) : DateTime.MinValue;
if (System.DBNull.Value != dbreader["EX_EFFECTIVETIME"])
{
exp.EffectiveTime = Convert.ToDateTime(dbreader["EX_EFFECTIVETIME"]);
}
else
{
exp.EffectiveTime = DateTime.MinValue;
}
list.Add(exp);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
if (null != dbreader)
{
dbreader.Close();
dbreader.Dispose();
}
}
return(list);
}
public override void boton_Click(int indice)
{
DataSet ds;
//frmReportes visor;
Dialogos.frmSeleccionAnioMes frmFecha;
EmpresaEntity empresa = new ConsultaEmpresas().getById(1);
switch (indice)
{
case 0: //Jornada Legal
ds = Model.DB.ejecutarDataSet(Model.TipoComando.SP, "reporteHorarios");
Model.DB.ejecutarDataSet(ref ds, Model.TipoComando.SP, "empresaConsultar", "@idEmpresa", 1);
Sueldos.Reportes.CrystalReport.ReportesCreador.JornadaLegal(ds);
break;
case 1: //Consulta de campos empledo
//TODO Incorporar el filtro de estado de empleado
Dialogos.frmSeleccionCampoEmpleado seleccionCampo = new Sueldos.View.Dialogos.frmSeleccionCampoEmpleado();
if (seleccionCampo.ShowDialog() == DialogResult.OK)
{
if (seleccionCampo.Filtrado)
{
if (seleccionCampo.Historico)
{
ds = Model.DB.ejecutarDataSet(Model.TipoComando.SP, "ReporteEmpleadosPorCampoEmpleadoValorHistorico",
"indice", seleccionCampo.Indice,
"contenido", seleccionCampo.Contenido,
"idEstado", seleccionCampo.Estado,
"idLiquidacion", seleccionCampo.LiquidacionID);
}
else
{
ds = Model.DB.ejecutarDataSet(Model.TipoComando.SP, "ReporteEmpleadosPorCampoEmpleadoValor",
"indice", seleccionCampo.Indice,
"contenido", seleccionCampo.Contenido,
"idEstado", seleccionCampo.Estado);
}
ds.Tables[0].TableName = "ReporteEmpleadosPorCampoEmpleado";
}
else
{
if (seleccionCampo.Historico)
{
ds = Model.DB.ejecutarDataSet(Model.TipoComando.SP, "ReporteEmpleadosPorCampoEmpleadoHistorico",
"indice", seleccionCampo.Indice,
"tipo", seleccionCampo.Tipo,
"idEstado", seleccionCampo.Estado,
"idLiquidacion", seleccionCampo.LiquidacionID);
}
else
{
ds = Model.DB.ejecutarDataSet(Model.TipoComando.SP, "ReporteEmpleadosPorCampoEmpleado",
"indice", seleccionCampo.Indice,
"tipo", seleccionCampo.Tipo,
"idEstado", seleccionCampo.Estado);
}
ds.Tables[0].TableName = "ReporteEmpleadosPorCampoEmpleado";
}
EmpresaEntity emp = new ConsultaEmpresas().getById(1);
Sueldos.Reportes.CrystalReport.ReportesCreador.ConsultaDeCamposEmpledo(
ds,
seleccionCampo.ContenidoDescripcion,
seleccionCampo.LiquidacionDescripcion,
seleccionCampo.IndiceDescripcion,
seleccionCampo.EstadoDescripcion,
emp.RazonSocial,
Application.ProductVersion);
}
break;
case 2: //Acumulados por Tipo
frmFecha = new Sueldos.View.Dialogos.frmSeleccionAnioMes();
if (frmFecha.ShowDialog() == DialogResult.OK)
{
ds = Model.DB.ejecutarDataSet(Model.TipoComando.SP, "reporteAcumuladosPorTipo",
"anioMes", frmFecha.AnioMes);
Sueldos.Reportes.CrystalReport.ReportesCreador.AcumuladosPorTipo(ds);
}
break;
case 3: //Sindicatos
//TODO cpereyra
MessageBox.Show("No implementado todavia");
Dialogos.frmSeleccionItem selItem = new Dialogos.frmSeleccionItem();
selItem.Nombre = "Sindicato";
selItem.Lista = Model.DB.ejecutarDataSet(Model.TipoComando.SP, "tablasConsultarContenidoyDetalleParaCombo",
"tabla", "empleadosSueldos",
"indice", 14);
selItem.HabilitarTodos = false;
if (selItem.ShowDialog() == DialogResult.OK)
{
frmFecha = new Sueldos.View.Dialogos.frmSeleccionAnioMes();
if (frmFecha.ShowDialog() == DialogResult.OK)
{
ds = Model.DB.ejecutarDataSet(Model.TipoComando.SP, "reporteSindicatos",
"idSindicato", selItem.SelectedID,
"anioMes", frmFecha.AnioMes);
List <string> titulos = new List <string>();
System.Data.Common.DbDataReader rs = Model.DB.ejecutarDataReader(Model.TipoComando.SP, "reporteSindicatosDescripcion", "idSindicato", selItem.SelectedID);
while (rs.Read())
{
titulos.Add(rs["Descripcion"].ToString());
}
Sueldos.Reportes.CrystalReport.ReportesCreador.Sindicatos(ds, titulos, selItem.SelectedDescripcion, frmFecha.AnioMesDescripcion);
}
}
break;
case 4: //Vacaciones Por Año
frmFecha = new Sueldos.View.Dialogos.frmSeleccionAnioMes();
if (frmFecha.ShowDialog() == DialogResult.OK)
{
string fecha = "31/12/" + frmFecha.Anio;
DataSet resultado = Model.DB.ejecutarDataSet(Model.TipoComando.SP, "vacacionesCalculaDiasTodos", "fechaTope", fecha);
resultado.DataSetName = "vacacionesCalculaDiasTodos";
Model.DataSetTo.CSV(resultado, Model.Delimitador.PuntoComa);
resultado.Dispose();
}
break;
case 5: //Fechas de Jubilación
ds = Model.DB.ejecutarDataSet(Model.TipoComando.SP, "ReporteEmpleadosPorFechaDeJubilacion");
Sueldos.Reportes.CrystalReport.ReportesCreador.FechasDeJubilacion(ds, empresa.RazonSocial, Application.ProductVersion);
break;
}
}
void backgroundWorker_DoWork(object sender, System.ComponentModel.DoWorkEventArgs e)
{
try
{
MeasureGroup mg = currentStat.IntermediateMeasureGroup;
DataSource oDataSource = mg.Parent.DataSource;
DsvTableBinding oTblBinding = new DsvTableBinding(mg.Parent.DataSourceView.ID, MeasureGroupHealthCheckPlugin.GetTableIdForDataItem(mg.Measures[0].Source));
DataTable dtTable = mg.ParentDatabase.DataSourceViews[oTblBinding.DataSourceViewID].Schema.Tables[oTblBinding.TableID];
//check whether this fact table uses an alternate datasource
if (dtTable.ExtendedProperties.ContainsKey("DataSourceID"))
{
oDataSource = mg.ParentDatabase.DataSources[dtTable.ExtendedProperties["DataSourceID"].ToString()];
}
Microsoft.DataWarehouse.Design.DataSourceConnection openedDataSourceConnection = Microsoft.DataWarehouse.DataWarehouseUtilities.GetOpenedDataSourceConnection((object)null, oDataSource.ID, oDataSource.Name, oDataSource.ManagedProvider, oDataSource.ConnectionString, oDataSource.Site, false);
try
{
if (openedDataSourceConnection != null)
{
openedDataSourceConnection.QueryTimeOut = 0;
}
else
{
throw new Exception("Couldn't open connection from data source " + oDataSource.Name);
}
command = openedDataSourceConnection.CreateCommand();
command.CommandText = currentStat.SQL;
if (backgroundWorker.CancellationPending)
{
return;
}
System.Data.Common.DbDataReader reader = null;
try
{
try
{
reader = command.ExecuteReader();
}
catch (Exception innerEx)
{
if (backgroundWorker.CancellationPending)
{
return;
}
else
{
throw innerEx;
}
}
if (!backgroundWorker.CancellationPending && reader.Read())
{
lock (command)
{
if (Convert.IsDBNull(reader["OriginalRecordCount"]))
{
currentStat.OriginalRecordCount = null;
}
else
{
currentStat.OriginalRecordCount = Convert.ToInt64(reader["OriginalRecordCount"]);
}
if (Convert.IsDBNull(reader["CompressedRecordCount"]))
{
currentStat.CompressedRecordCount = null;
}
else
{
currentStat.CompressedRecordCount = Convert.ToInt64(reader["CompressedRecordCount"]);
}
if (Convert.IsDBNull(reader["MatrixDimensionRecordCount"]))
{
currentStat.MatrixDimensionRecordCount = null;
}
else
{
currentStat.MatrixDimensionRecordCount = Convert.ToInt64(reader["MatrixDimensionRecordCount"]);
}
currentStat.Status = M2MMatrixCompressionPlugin.M2MMatrixCompressionStat.M2MMatrixCompressionStatStatus.Complete;
}
foreach (M2MMatrixCompressionPlugin.M2MMatrixCompressionStat stat in _list)
{
if (stat != currentStat && currentStat.IntermediateMeasureGroupName == stat.IntermediateMeasureGroupName && stat.SQL == currentStat.SQL)
{
stat.OriginalRecordCount = currentStat.OriginalRecordCount;
stat.CompressedRecordCount = currentStat.CompressedRecordCount;
stat.MatrixDimensionRecordCount = currentStat.MatrixDimensionRecordCount;
stat.Status = M2MMatrixCompressionPlugin.M2MMatrixCompressionStat.M2MMatrixCompressionStatStatus.Complete;
}
}
}
}
finally
{
try
{
if ((reader != null) && !reader.IsClosed)
{
reader.Close();
}
}
catch { }
command = null;
}
}
finally
{
try
{
openedDataSourceConnection.Close();
}
catch { }
}
}
catch (Exception ex)
{
currentStat.Error = ex.Message + "\r\n" + ex.StackTrace;
foreach (M2MMatrixCompressionPlugin.M2MMatrixCompressionStat stat in _list)
{
if (stat != currentStat && currentStat.IntermediateMeasureGroupName == stat.IntermediateMeasureGroupName && stat.SQL == currentStat.SQL)
{
stat.Error = currentStat.Error;
}
}
}
}
public List <string> GetFldsValues(string strSql, List <string> lstFlds)
{
if (lstFlds == null)
{
return(null);
}
if (lstFlds.Count < 1)
{
return(null);
}
System.Data.Common.DbDataReader reader = null;
List <string> lstReturn = null;
try
{
System.Data.SqlClient.SqlCommand mycmd = cnn.CreateCommand();
mycmd.CommandText = strSql;
reader = mycmd.ExecuteReader();
if (reader == null)
{
bSuccess = false;
strError = "内容不存在";
return(null);
}
if (!reader.HasRows)
{
bSuccess = true;
strError = "内容为空";
reader.Close();
return(null);
}
lstReturn = new List <string>();
if (reader.Read())
{
for (int i = 0; i < lstFlds.Count; i++)
{
if (!Convert.IsDBNull(reader[lstFlds[i]]))
{
string strRT = reader[lstFlds[i]].ToString().TrimEnd();
lstReturn.Add(strRT);
}
else
{
lstReturn.Add("");
}
}
}
reader.Close();
bSuccess = true;
}
catch (System.Exception ex)
{
bSuccess = false;
strError = ex.Message;
if (reader != null && !reader.IsClosed)
{
reader.Close();
}
return(null);
}
return(lstReturn);
}
public override void ExecuteResult(System.Web.Mvc.ControllerContext context)
{
context.HttpContext.Response.ContentType = "application/json";
context.HttpContext.Response.ContentEncoding = System.Text.Encoding.UTF8;
using (Newtonsoft.Json.JsonTextWriter jsonWriter = new Newtonsoft.Json.JsonTextWriter(context.HttpContext.Response.Output))
{
jsonWriter.Formatting = Newtonsoft.Json.Formatting.Indented;
jsonWriter.WriteStartObject();
jsonWriter.WritePropertyName("Tables");
jsonWriter.WriteStartArray();
using (System.Data.Common.DbConnection con = SQL.CreateConnection())
{
if (con.State != System.Data.ConnectionState.Open)
{
con.Open();
}
using (System.Data.Common.DbCommand cmd = this.GetCommand(con))
{
using (System.Data.Common.DbDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.SequentialAccess
| System.Data.CommandBehavior.CloseConnection
))
{
do
{
jsonWriter.WriteStartObject(); // tbl = new Table();
jsonWriter.WritePropertyName("Columns");
jsonWriter.WriteStartArray();
for (int i = 0; i < dr.FieldCount; ++i)
{
jsonWriter.WriteStartObject();
jsonWriter.WritePropertyName("ColumnName");
jsonWriter.WriteValue(dr.GetName(i));
jsonWriter.WritePropertyName("FieldType");
jsonWriter.WriteValue(SQL.GetAssemblyQualifiedNoVersionName(dr.GetFieldType(i)));
jsonWriter.WriteEndObject();
} // Next i
jsonWriter.WriteEndArray();
jsonWriter.WritePropertyName("Rows");
jsonWriter.WriteStartArray();
if (dr.HasRows)
{
while (dr.Read())
{
object[] thisRow = new object[dr.FieldCount];
jsonWriter.WriteStartArray(); // object[] thisRow = new object[dr.FieldCount];
for (int i = 0; i < dr.FieldCount; ++i)
{
jsonWriter.WriteValue(dr.GetValue(i));
} // Next i
jsonWriter.WriteEndArray(); // tbl.Rows.Add(thisRow);
} // Whend
} // End if (dr.HasRows)
jsonWriter.WriteEndArray();
jsonWriter.WriteEndObject(); // ser.Tables.Add(tbl);
} while (dr.NextResult());
} // End using dr
} // End using cmd
if (con.State != System.Data.ConnectionState.Closed)
{
con.Close();
}
} // End using con
jsonWriter.WriteEndArray();
jsonWriter.WriteEndObject();
jsonWriter.Flush();
} // End Using jsonWriter
context.HttpContext.Response.Output.Flush();
context.HttpContext.Response.OutputStream.Flush();
context.HttpContext.Response.Flush();
} // End Sub SerializeLargeDataset
public static void Test()
{
System.Data.SqlClient.SqlConnectionStringBuilder csb = new System.Data.SqlClient.SqlConnectionStringBuilder();
csb.DataSource = System.Environment.MachineName;
csb.IntegratedSecurity = true;
if (!csb.IntegratedSecurity)
{
csb.UserID = "DAL_Test";
csb.Password = "******";
} // End if (!csb.IntegratedSecurity)
csb.InitialCatalog = "COR_Basic_Demo_V4";
cDAL DAL = cDAL.CreateInstance();
DAL.ConnectionString = csb.ConnectionString;
using (System.Data.Common.DbConnection dbConnection = DAL.GetConnection())
{
object objUser1 = DAL.ExecuteScalar("SELECT TOP 1 BE_User FROM T_Benutzer ORDER BY BE_User;", dbConnection);
object objUser2 = DAL.ExecuteScalar("SELECT TOP 1 BE_User FROM T_Benutzer ORDER BY BE_ID;", dbConnection);
DAL.ExecuteNonQuery("UPDATE T_Benutzer SET BE_Hash = BE_Hash;", dbConnection);
DAL.ExecuteNonQuery("UPDATE T_Benutzer SET BE_Hash = BE_Hash;", dbConnection);
#if WITH_CONNECTION
//using (System.Data.Common.DbDataReader reader = DAL.ExecuteReader("SELECT * FROM T_Benutzer; SELECT * FROM T_Benutzergruppen;", dbConnection))
using (System.Data.Common.DbDataReader reader = DAL.ExecuteReader_Buggy("SELECT * FROM T_Benutzer; SELECT * FROM T_Benutzergruppen;"))
#else
DAL.ExecuteReader("SELECT * FROM T_Benutzer; SELECT * FROM T_Benutzergruppen;", delegate(System.Data.Common.DbDataReader reader)
#endif
{
do
{
for (int i = 0; i < reader.FieldCount; ++i)
{
string fieldName = reader.GetName(i);
System.Type fieldType = reader.GetFieldType(i);
System.Console.WriteLine("{0}:\t{1}\t{2}", i, fieldName, fieldType.ToString());
} // Next i
if (reader.HasRows)
{
int rowCount = 1;
while (reader.Read())
{
System.Console.WriteLine(@"Row {0}", rowCount);
for (int i = 0; i < reader.FieldCount; ++i)
{
string fieldName = reader.GetName(i);
object fieldValue = reader.GetValue(i);
System.Console.WriteLine(@" - {0}: {1}", fieldName, System.Convert.ToString(fieldValue));
} // Next i
++rowCount;
} // Whend
--rowCount;
} // End if (reader.HasRows)
} while (reader.NextResult());
} // End Using reader
#if !WITH_CONNECTION
);
#endif
object objUser3 = DAL.ExecuteScalar("SELECT TOP 1 BE_User FROM T_Benutzer ORDER BY BE_Hash;", dbConnection);
object objUser4 = DAL.ExecuteScalar("SELECT TOP 1 BE_User FROM T_Benutzer ORDER BY BE_Passwort;", dbConnection);
} // End Using dbConnection
} // End Sub Test
public ActionStatus UploadHardwareKit(List <Entity.HardwareKitDTO> requestData)
{
var response = new ActionStatus();
try
{
logger.InfoLog(Constants.ACTION_ENTRY, "HardwareKitRepository.SaveHardwareKit");
List <Guid> createdHardwareKits = new List <Guid>();
var stringResult = string.Empty;
using (var stringwriter = new System.IO.StringWriter())
{
var serializer = new XmlSerializer(requestData.GetType());
serializer.Serialize(stringwriter, requestData);
stringResult = stringwriter.ToString();
}
stringResult = stringResult.Replace("HardwareKitDTO", "HardwareKit").Replace("KitDeviceDTO", "KitDevice");
XDocument requestXML = XDocument.Parse(stringResult);
requestXML = new XDocument(new XElement("HardwareKits", requestXML.Root));
using (var sqlDataAccess = new SqlDataAccess(ConnectionString))
{
List <System.Data.Common.DbParameter> parameters = sqlDataAccess.CreateParams(component.helper.SolutionConfiguration.CurrentUserId, component.helper.SolutionConfiguration.Version);
parameters.Add(sqlDataAccess.CreateParameter("data", requestXML.ToString(), DbType.Xml, ParameterDirection.Input));
System.Data.Common.DbDataReader dbDataReader = sqlDataAccess.ExecuteReader(sqlDataAccess.CreateCommand("[KitDevice_AddUpdate]", CommandType.StoredProcedure, null), parameters.ToArray());
while (dbDataReader.Read())
{
if (dbDataReader["guid"] != null)
{
createdHardwareKits.Add(new Guid(dbDataReader["guid"].ToString().ToUpper()));
}
else
{
response.Message = dbDataReader["fieldname"].ToString().ToUpper();
response.Data = null;
response.Success = false;
}
}
if (createdHardwareKits.Count != 0 && createdHardwareKits.Count == 1)
{
// requestData.Guid = createdHardwareKits.FirstOrDefault();
response.Success = true;
response.Data = requestData;
}
else
{
response.Success = false;
response.Data = requestData;
response.Message = "Unable to save Hardware-Kit";
}
//var result = DataUtils.DataReaderToList<Entity.HardwareKitResponse>(dbDataReader, null);
//result.Count = int.Parse(parameters.Where(p => p.ParameterName.Equals("count")).FirstOrDefault().Value.ToString());
}
logger.InfoLog(Constants.ACTION_EXIT, "HardwareKitRepository.SaveHardwareKit");
}
catch (Exception ex)
{
response.Success = false;
response.Message = ex.Message.ToString();
logger.ErrorLog(Constants.ACTION_EXCEPTION, ex);
}
return(response);
}
/// <summary>
/// Procesa las fichadas de un dia de un legajo tratando de obtener el total
/// de horas extras trabajadas
/// </summary>
/// <param name="fecha"></param>
/// <param name="legajo"></param>
private void procesaDia(DateTime fecha, int legajo)
{
int horaEntrada = 0;
int horaSalida = 0;
int limiteDia = 0;
int minutosExtras = 0;
int horaDesde = 0;
int horaHasta = 0;
int idTipoHoraExtra = 0;
int cont = 1;
int idDia;
double limiteHs50 = 0;
limiteHs50 = this.consultaLimiteHs50();
// Model.DB.Enlazado = true;
System.Data.Common.DbDataReader rs = Model.DB.ejecutarDataReader(Model.TipoComando.SP, "relojConsultarFichadasFechaEnMinutos", "@legajo", legajo, "@fecha", fecha);
System.Data.Common.DbDataReader rsHorasExtrasDefiniciones = null;
//limpio horas extras del legajo antes de procesar
Console.WriteLine("eliminando registro: " + legajo + " fecha: " + fecha);
Model.DB.ejecutarProceso(Model.TipoComando.SP, "horasExtrasEliminar", "@legajo", legajo, "@fecha", fecha);
//determino dia de la semana. siempre 0-dom/6-sab
idDia = (int)fecha.DayOfWeek;
if (rs.HasRows)
{
while (rs.Read()) //recorro fichadas del dia
{
this.pbProceso.Value += 1;
if (Convert.ToInt32(rs["limiteDia"]) == 24)
{
//horaEntrada = Convert.ToInt32(rs["minutos"]);
horaEntrada = 24 * 60 - horaSalida;
}
else
{
if (Convert.ToInt32(rs["minutos"]) < 0)
{
horaEntrada = Convert.ToInt32(rs["minutos"]);
}
if (Convert.ToInt32(rs["minutos"]) > 0)
{
horaSalida = Convert.ToInt32(rs["minutos"]);
}
}
if (horaEntrada < 0)
{
horaEntrada = -1 * horaEntrada;
}
if (horaSalida < 0)
{
horaSalida = -1 * horaSalida;
}
if (cont % 2 == 0) //solo cuando es par verifico
{
rsHorasExtrasDefiniciones = Model.DB.ejecutarDataReader(Model.TipoComando.SP, "horasExtrasDefinicionesConsultarDia", "@idDia", idDia);
while (rsHorasExtrasDefiniciones.Read()) //recorro deficiones de hs. extras para ese dia
{
if (this.verificaSiFeriado(fecha))
{
limiteDia = 0;
horaDesde = 0; //00:00:00
horaHasta = 1440; //24:00:00
idTipoHoraExtra = 89;
}
else
{
limiteDia = Convert.ToInt32(rsHorasExtrasDefiniciones["limiteDia"]);
horaDesde = pasaAminutos(Convert.ToDateTime(rsHorasExtrasDefiniciones["horaDesde"]));
horaHasta = pasaAminutos(Convert.ToDateTime(rsHorasExtrasDefiniciones["horaHasta"]));
idTipoHoraExtra = Convert.ToInt32(rsHorasExtrasDefiniciones["idTipoHoraExtra"]);
}
if (horaEntrada > horaSalida) //(limiteDia == 24) //&& (horaEntrada>=horaDesde))
{
//minutosExtras = this.obtieneMinutosExtras(horaDesde, 24*60, 24*60-horaSalida, 24*60);
minutosExtras = this.obtieneMinutosExtras(horaDesde, 24 * 60, horaEntrada, 24 * 60);
//minutosExtras += this.obtieneMinutosExtras(0, horaHasta, 0, horaEntrada);
minutosExtras += this.obtieneMinutosExtras(0, horaHasta, 0, horaSalida);
}
else
{ //en caso de nocturnas no definidas en el horario del empleado
if (horaDesde > horaHasta)
{
minutosExtras = this.obtieneMinutosExtras(0, horaHasta, horaEntrada, horaSalida);
}
else
{
minutosExtras = this.obtieneMinutosExtras(horaDesde, horaHasta, horaEntrada, horaSalida);
}
}
//horas al 50% Lu a Vi > 10 Hs (540 min)
if (idDia >= 1 && idDia <= 5 && idTipoHoraExtra == 80)
{
if (minutosExtras > limiteHs50 * 60)
{
minutosExtras = (int)(minutosExtras - (limiteHs50 * 60));
}
else
{
minutosExtras = 0;
}
}
Console.WriteLine("total id: " + Convert.ToInt32(rsHorasExtrasDefiniciones["id"]) + " minutos extras: " + minutosExtras.ToString());
Console.WriteLine("insertando registro: " + legajo + " tipo Hora: " + idTipoHoraExtra);
if (minutosExtras > 0)
{
Model.DB.ejecutarProceso(Model.TipoComando.SP, "horasExtrasActualizar", "@legajo", legajo, "@fecha", fecha, "@idTipoHoraExtra", idTipoHoraExtra, "cantidad", minutosExtras);
}
Thread.Sleep(100); //OJO PARA CONTROLAR timeout
}
rsHorasExtrasDefiniciones.Close();
Model.DB.desconectarDB();
}
cont++;
}
rs.Close();
Model.DB.desconectarDB();
// Model.DB.Enlazado = false;
}
}
