public override System.Data.IDbConnection GetConnection()
{
if (Connection == null)
Connection = base.GetConnection();
return Connection;
}
public bool TestConnection(string query)
{
try
{
IConn = this.GetConnection();
IConn.Open();
bool result = false;
System.Data.IDbCommand cmd = IConn.CreateCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = query;
System.Data.IDataReader _reader = cmd.ExecuteReader();
result = (_reader.FieldCount > 0);
_reader.Close();
IConn.Close();
return result;
}
catch (Exception ex) {
Logger.Error(ex.Message);
}
return false;
}
public FileSets(LocalListDatabase owner, DateTime time, long[] versions)
{
m_connection = owner.m_connection;
m_filesets = owner.FilesetTimes.ToArray();
m_tablename = "Filesets-" + Library.Utility.Utility.ByteArrayAsHexString(Guid.NewGuid().ToByteArray());
var tmp = owner.GetFilelistWhereClause(time, versions, m_filesets);
string query = tmp.Item1;
var args = tmp.Item2;
using(var cmd = m_connection.CreateCommand())
cmd.ExecuteNonQuery(string.Format(@"CREATE TEMPORARY TABLE ""{0}"" AS SELECT DISTINCT ""ID"" AS ""FilesetID"", ""Timestamp"" AS ""Timestamp"" FROM ""Fileset"" " + query, m_tablename), args);
}
/*
* 使用微软提供的操作Offfice的库DocumentFormat.OpenXml
* ClosedXML在此基础上进行了封装，简化操作。
*
* */
static void Main(string[] args)
{
HZ_Conn = new SqlConnection(HZ_ConnectionString);
HZ_Conn.Open();
DC_Conn = new SqlConnection(DC_ConnectionString);
DC_Conn.Open();
var workbook = new XLWorkbook();
var worksheet = workbook.Worksheets.Add("Sheet1");
var url = "http://webapp.hc.ihaozhuo.com/SMSPromotion.html#/{0}/{1}/{2}";
//http://webapp.hc.ihaozhuo.com/SMSPromotion.html#/{task}/{mobile}/{org}
var table = "bjbr008";
var deptCode = "bjbr008";
//黎波，刘述正，刘倩倩,
//var mobiles = new string[] { "15900860546", "17783055953","15601815186"};
var mobiles = HZ_Conn.Query<string>("select distinct telephone from " + table);//distinct
var shortLinkProxy = SortLinkServerProxy.ShareInstance();
var time = DateTime.Now;
var sms = (from c in mobiles
where !string.IsNullOrEmpty(c) && Regex.Match(c, "1[2|3|5|7|8|][0-9]{9}").Success
select new
{
mobile = c,
sms = shortLinkProxy.getSortLink(string.Format(url, 1, c, deptCode)) + " "
}).ToList();
Trace.WriteLine(string.Format("cost:{0}", DateTime.Now.Subtract(time).TotalSeconds));
var rowStart = 2;
var columnStart = 1;
foreach (var item in sms)
{
worksheet.Cell(rowStart, columnStart).Value = item.mobile;
worksheet.Cell(rowStart, columnStart + 1).Value = "【新乡第一人民医院】温馨提示,您的体检报告已完成，查看您的健康状况及阳性指标，永久保存报告详情请点击";
worksheet.Cell(rowStart, columnStart + 2).Value = item.sms + " 退订回N";
worksheet.Cell(rowStart, columnStart + 3).Value = item.sms;
rowStart++;
}
workbook.SaveAs("HelloWorld.xlsx");
}
public object Get(UserCsvListRequestTep request)
{
var context = TepWebContext.GetWebContext(PagePrivileges.AdminOnly);
var csv = new System.Text.StringBuilder();
var filename = DateTime.UtcNow.ToString("yy-MM-dd");
try {
context.Open();
context.LogInfo(this, string.Format("/user/csv GET"));
filename = context.GetConfigValue("SiteNameShort") + "-" + filename;
string sql = string.Format("SELECT usr.id,usr.username,usr.email,usr.firstname,usr.lastname,usr.level,usr.affiliation,usr.country,(SELECT log_time FROM usrsession WHERE id_usr=usr.id ORDER BY log_time ASC LIMIT 1) AS registration_date FROM usr;");
csv.Append("Id,Username,Email,FisrtName,LastName,Level,Affiliation,Country,Registration date" + Environment.NewLine);
System.Data.IDbConnection dbConnection = context.GetDbConnection();
System.Data.IDataReader reader = context.GetQueryResult(sql, dbConnection);
while (reader.Read())
{
csv.Append(String.Format("{0},{1},{2},{3},{4},{5},{6},{7},{8}{9}",
reader.GetValue(0) != DBNull.Value ? reader.GetString(0) : "",
reader.GetValue(1) != DBNull.Value ? reader.GetString(1) : "",
reader.GetValue(2) != DBNull.Value ? reader.GetString(2) : "",
reader.GetValue(3) != DBNull.Value ? reader.GetString(3) : "",
reader.GetValue(4) != DBNull.Value ? reader.GetString(4) : "",
reader.GetValue(5) != DBNull.Value ? reader.GetString(5) : "",
reader.GetValue(6) != DBNull.Value ? reader.GetString(6) : "",
reader.GetValue(7) != DBNull.Value ? reader.GetString(7) : "",
reader.GetValue(8) != DBNull.Value ? reader.GetString(8) : "",
Environment.NewLine));
}
context.CloseQueryResult(reader, dbConnection);
context.Close();
} catch (Exception e) {
context.LogError(this, e.Message, e);
context.Close();
throw e;
}
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}.csv", filename));
return(csv.ToString());
}
/// <summary>
/// Loads an SQLite connection instance and opening the database
/// </summary>
/// <returns>The SQLite connection instance.</returns>
/// <param name="targetpath">The optional path to the database.</param>
public static System.Data.IDbConnection LoadConnection(string targetpath)
{
if (string.IsNullOrWhiteSpace(targetpath))
{
throw new ArgumentNullException(nameof(targetpath));
}
System.Data.IDbConnection con = LoadConnection();
try
{
OpenSQLiteFile(con, targetpath);
}
catch (Exception ex)
{
Logging.Log.WriteErrorMessage(LOGTAG, "FailedToLoadConnectionSQLite", ex, @"Failed to load connection with path '{0}'.", targetpath);
DisposeConnection(con);
throw;
}
return(con);
}
public static void LogEvent(LogType logType, object entity, long userId, System.Data.IDbConnection connection)
{
//start new session because the underlying session can be in a fragile statue during the
//interceptor calls
using (ISession tempSession = NHibernateHelper.SessionFactory.OpenSession())
{
long entityId = -1;
PropertyInfo idProperty = entity.GetType().GetProperty("Id", BindingFlags.GetProperty | BindingFlags.Public | BindingFlags.Instance);
if (idProperty != null)
{
entityId = (long)idProperty.GetValue(entity, null);
}
AuditLogRecord record = new AuditLogRecord(logType.ToString(), entityId, entity.GetType(), userId);
//updates will be written to the EXISTING connection
//which will mean that calling code manages transactions and also
//it's quick (rather than opening new session)
tempSession.Save(record);
tempSession.Flush();
}
}
public void CreateNewSchemaAndUser(SqlProduct sqlProduct, out string server, out int port, NewDatabaseParameters newDbParameters, string sampleApp)
{
string sql = GetCreateNewSchemaAndUserSql(newDbParameters);
if (!string.IsNullOrEmpty(sampleApp))
{
string scriptFileName = Maps.GetDeploymentPath(string.Format("Sql/SampleApp/{0}.sql", sampleApp));
System.IO.FileInfo file = new System.IO.FileInfo(scriptFileName);
if (!file.Exists)
{
Maps.Instance.DuradosMap.Logger.Log("myAppConnectionController", "Post", "CreateNewSchemaAndUser", "Sample app file do not exists", null, 1, "Sample app file do not exists", DateTime.Now);
throw new Exception("Sample app file do not exists");
}
string script = file.OpenText().ReadToEnd();
sql += script.Replace("__DB__Name__", newDbParameters.DbName);
}
using (System.Data.IDbConnection connection = GetExternalAvailableInstanceConnection(sqlProduct, out server, out port))
{
CreateSchemaAndUser(sql, connection);
}
}
/// <summary>
/// Gets the new users.
/// </summary>
/// <returns>The new users.</returns>
/// <param name="context">Context.</param>
/// <param name="startdate">Startdate.</param>
/// <param name="enddate">Enddate.</param>
/// <param name="skipids">Skipids.</param>
/// <param name="allowedids">Allowedids.</param>
public static NameValueCollection GetNewUsers(IfyContext context, string startdate, string enddate, string skipids, string allowedids)
{
var ids = new NameValueCollection();
string sql = string.Format("SELECT DISTINCT usr.id,usr.level FROM usr {0}{1}{2};",
!string.IsNullOrEmpty(startdate) && !string.IsNullOrEmpty(enddate) ? " WHERE id NOT IN (SELECT id_usr FROM usrsession WHERE usrsession.log_time < '" + startdate + "' ) AND id IN (SELECT id_usr FROM usrsession WHERE usrsession.log_time <= '" + enddate + "')" : "",
string.IsNullOrEmpty(skipids) ? "" : (string.IsNullOrEmpty(startdate) || string.IsNullOrEmpty(enddate) ? " WHERE " : " AND ") + "id NOT IN (" + skipids + ")",
string.IsNullOrEmpty(allowedids) ? "" : ((string.IsNullOrEmpty(startdate) || string.IsNullOrEmpty(enddate)) && string.IsNullOrEmpty(skipids) ? " WHERE " : " AND ") + " id IN (" + allowedids + ")");
System.Data.IDbConnection dbConnection = context.GetDbConnection();
System.Data.IDataReader reader = context.GetQueryResult(sql, dbConnection);
while (reader.Read())
{
if (reader.GetValue(0) != DBNull.Value)
{
var id = reader.GetInt32(0);
var level = reader.GetInt32(1);
ids.Set(id + "", level + "");
}
}
context.CloseQueryResult(reader, dbConnection);
return(ids);
}
public decimal GetBillOutstanding(string ContractNo)
{
string sql = " Select isnull(a.OutStandingBalance,0) " +
" + " +
" (Select isnull(Sum(BillAmount),0) from tblBillConsumption where ContractNo = '" + ContractNo + "') " +
" - " +
" (Select isnull(Sum(PaidAmount),0) from tblBillConsumption where ContractNo = '" + ContractNo + "') as OutStanding" +
" from tblCustomerOutstandingBal a where ContractNo = '" + ContractNo + "'";
db = new SqlConnection(ConfigurationManager.ConnectionStrings["cnConsumption"].ConnectionString);
var obj = db.Query(sql).ToArray();
db.Close();
if (obj.Length > 0)
{
return(Convert.ToDecimal(obj[0].OutStanding));
}
else
{
return(0);
}
}
/// <summary>
/// 验证电极在Eman中是否存在
/// </summary>
public static void CheckCuprumIsExist(
System.Data.IDbConnection conn
, System.Data.IDbTransaction tran
, List <EACT_CUPRUM> CupRumList
, Eman_Mould mouldInfo
)
{
var sql = new StringBuilder();
sql.AppendLine("SELECT * FROM MouldPart WHERE MouldPart.mouldID = @MouldInteriorID AND MouldPart.mark <> 'delete' AND MouldPart.verifyID in");
sql.AppendLine("(");
sql.Append(string.Join(",", Enumerable.Select(CupRumList, u => string.Format("'{0}'", u.CUPRUMSN)).Distinct().ToArray()));
sql.AppendLine(")");
var list = conn.Query <MouldPart>(sql.ToString(), new { MouldInteriorID = mouldInfo.mouldID }, tran);
foreach (var item in CupRumList.ToList())
{
if (list.Where(u => u.verifyID == item.CUPRUMSN).Count() > 0)
{
CupRumList.Remove(item);
}
}
}
/// <summary>
///
/// </summary>
/// <returns></returns>
internal int Execute()
{
if (this.ExcuteType == DMSExcuteType.DELETE ||
this.ExcuteType == DMSExcuteType.INSERT ||
this.ExcuteType == DMSExcuteType.UPDATE)
{
string sql = this.GetResultSql();
try
{
System.Data.IDbTransaction trans = null;
using (System.Data.IDbConnection conn = this.Provider.GetOpenConnection())
{
return(DMSFrame.Access.DMSDbAccess.Execute(conn, this.CurrentType == null ? "" : this.CurrentType.FullName, sql, dynamicParameters, trans, 30));
}
}
catch (Exception ex)
{
Log.Debug(ReflectionUtils.GetMethodBaseInfo(System.Reflection.MethodBase.GetCurrentMethod()), string.Format("sql:{0} msg:{1}", sql, ex.Message), ex);
throw ex;
}
}
return(0);
}
protected BaseData()
{
try
{
if (connectionString == "")
{//use the enterprise library config to get a connection string
MGRELog.Write("Setting connection string");
Database db = Microsoft.Practices.EnterpriseLibrary.Data.DatabaseFactory.CreateDatabase("HUB");
System.Data.IDbConnection cnn = db.GetConnection();
connectionString = db.GetConnection().ConnectionString;
cnn.Dispose();
MGRELog.Write("Connection string set");
}
EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder();
//Set the provider name.
entityBuilder.Provider = "System.Data.SqlClient";
// Set the provider-specific connection string.
entityBuilder.ProviderConnectionString = connectionString;
// Set the Metadata location.
entityBuilder.Metadata = @"res://*/ETL.csdl|
res://*/ETL.ssdl|
res://*/ETL.msl";
entConn = new EntityConnection(entityBuilder.ToString());
dataContext = new ETLEntities(entConn);
}
catch (Exception ex)
{
HandleException(ex);
}
}
private LocalDatabase(System.Data.IDbConnection connection)
{
m_updateremotevolumeCommand = connection.CreateCommand();
m_selectremotevolumesCommand = connection.CreateCommand();
m_selectremotevolumeCommand = connection.CreateCommand();
m_insertlogCommand = connection.CreateCommand();
m_insertremotelogCommand = connection.CreateCommand();
m_removeremotevolumeCommand = connection.CreateCommand();
m_selectremotevolumeIdCommand = connection.CreateCommand();
m_createremotevolumeCommand = connection.CreateCommand();
m_insertIndexBlockLink = connection.CreateCommand();
m_insertlogCommand.CommandText = @"INSERT INTO ""LogData"" (""OperationID"", ""Timestamp"", ""Type"", ""Message"", ""Exception"") VALUES (?, ?, ?, ?, ?)";
m_insertlogCommand.AddParameters(5);
m_insertremotelogCommand.CommandText = @"INSERT INTO ""RemoteOperation"" (""OperationID"", ""Timestamp"", ""Operation"", ""Path"", ""Data"") VALUES (?, ?, ?, ?, ?)";
m_insertremotelogCommand.AddParameters(5);
m_updateremotevolumeCommand.CommandText = @"UPDATE ""Remotevolume"" SET ""OperationID"" = ?, ""State"" = ?, ""Hash"" = ?, ""Size"" = ? WHERE ""Name"" = ?";
m_updateremotevolumeCommand.AddParameters(5);
m_selectremotevolumesCommand.CommandText = @"SELECT ""Name"", ""Type"", ""Size"", ""Hash"", ""State"" FROM ""Remotevolume""";
m_selectremotevolumeCommand.CommandText = @"SELECT ""Type"", ""Size"", ""Hash"", ""State"" FROM ""Remotevolume"" WHERE ""Name"" = ?";
m_selectremotevolumeCommand.AddParameter();
m_removeremotevolumeCommand.CommandText = @"DELETE FROM ""Remotevolume"" WHERE ""Name"" = ?";
m_removeremotevolumeCommand.AddParameter();
m_selectremotevolumeIdCommand.CommandText = @"SELECT ""ID"" FROM ""Remotevolume"" WHERE ""Name"" = ?";
m_createremotevolumeCommand.CommandText = @"INSERT INTO ""Remotevolume"" (""OperationID"", ""Name"", ""Type"", ""State"", ""VerificationCount"") VALUES (?, ?, ?, ?, ?); SELECT last_insert_rowid();";
m_createremotevolumeCommand.AddParameters(5);
m_insertIndexBlockLink.CommandText = @"INSERT INTO ""IndexBlockLink"" (""IndexVolumeID"", ""BlockVolumeID"") VALUES (?, ?)";
m_insertIndexBlockLink.AddParameters(2);
}
/// <summary>
///
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="ExcuteType"></param>
/// <param name="countsql"></param>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="totalRecord"></param>
/// <returns></returns>
public ConditionResult <T> GetConditionResult <T>(DMSExcuteType ExcuteType, string countsql, string sql, DynamicParameters param, int pageIndex, int pageSize, int totalRecord)
where T : class
{
ConditionResult <T> resultList = new ConditionResult <T>()
{
PageIndex = pageIndex,
PageSize = pageSize,
TotalRecord = 0,
AllowPaging = true,
ResultList = new List <T>(),
};
using (System.Data.IDbConnection conn = this.GetOpenConnection())
{
if (ExcuteType == DMSExcuteType.SELECT)
{
if (totalRecord == 0)
{
totalRecord = (int)DMSDbAccess.ExecuteScalar(conn, typeof(T).FullName, countsql, param);
}
resultList.TotalRecord = totalRecord;
resultList.ResultList = DMSDbAccess.Query <T>(conn, typeof(T).FullName, sql, param, totalRecord, null, false, 30).ToList();
if (resultList.PageSize > 0)
{
resultList.TotalPage = resultList.TotalRecord / resultList.PageSize + (resultList.TotalRecord % resultList.PageSize == 0 ? 0 : 1);
}
}
else if (ExcuteType == DMSExcuteType.DELETE ||
ExcuteType == DMSExcuteType.INSERT ||
ExcuteType == DMSExcuteType.UPDATE)
{
resultList.TotalRecord = DMSDbAccess.Execute(conn, typeof(T).FullName, sql, param);
}
conn.Close();
}
return(resultList);
}
/// <summary>
/// 重複データが存在するか判定します。
/// </summary>
/// <param name="conn"></param>
/// <returns>重複データが存在した場合true、そうでなければfalse。</returns>
private bool ExistsDuplication(System.Data.IDbConnection conn)
{
// 重複データが存在するかチェック
var sql = @"
select
count(ID)
from
MEETING_ROOM
where
LOCATION_ID = :LocationId
and NAME = :Name
";
if (EditMode != EditMode.AddNew)
{
sql += @"
and ID <> :Id
";
}
var count = conn.ExecuteScalar <int>(sql, MeetingRoom);
return(count > 0);
}
public MissingBlockList(string volumename, System.Data.IDbConnection connection, System.Data.IDbTransaction transaction)
{
m_connection = connection;
m_transaction = new TemporaryTransactionWrapper(m_connection, transaction);
m_volumename = volumename;
var tablename = "MissingBlocks-" + Library.Utility.Utility.ByteArrayAsHexString(Guid.NewGuid().ToByteArray());
using (var cmd = m_connection.CreateCommand())
{
cmd.Transaction = m_transaction.Parent;
cmd.ExecuteNonQuery(string.Format(@"CREATE TEMPORARY TABLE ""{0}"" (""Hash"" TEXT NOT NULL, ""Size"" INTEGER NOT NULL, ""Restored"" INTEGER NOT NULL) ", tablename));
m_tablename = tablename;
var blockCount = cmd.ExecuteNonQuery(string.Format(@"INSERT INTO ""{0}"" (""Hash"", ""Size"", ""Restored"") SELECT DISTINCT ""Block"".""Hash"", ""Block"".""Size"", 0 AS ""Restored"" FROM ""Block"",""Remotevolume"" WHERE ""Block"".""VolumeID"" = ""Remotevolume"".""ID"" AND ""Remotevolume"".""Name"" = ? ", m_tablename), volumename);
if (blockCount == 0)
{
throw new Exception(string.Format("Unexpected empty block volume: {0}", volumename));
}
}
m_insertCommand = m_connection.CreateCommand();
m_insertCommand.Transaction = m_transaction.Parent;
m_insertCommand.AddParameters(3);
}
/// <summary>
/// 匹配典型电极工件
/// </summary>
public static void CheckTypicalSeparatePart(
System.Data.IDbConnection conn
, System.Data.IDbTransaction tran
, List <EACT_CUPRUM> CupRumList
)
{
var sql = new StringBuilder();
sql.AppendLine(" select * ");
sql.AppendLine(" from TypicalSeparatePart ");
sql.AppendLine(" where partType = 20");
var tsps = conn.Query <TypicalSeparatePart>(sql.ToString(), null, tran).ToList();
foreach (var item in CupRumList)
{
var tsp1 = tsps.FirstOrDefault(u => u.partName == item.verifyIDName);
if (tsp1 == null)
{
var tsp2 = tsps.FirstOrDefault(u => u.partName == "电极");
if (tsps == null)
{
throw new Exception(string.Format("系统中不存在典型电极工件：{0}", item.verifyIDName));
}
else
{
item.TypicalPartID = tsp2.partID;
item.PartClassID = tsp2.partClassID;
}
}
else
{
item.TypicalPartID = tsp1.partID;
item.PartClassID = tsp1.partClassID;
}
}
}
/// <summary>
/// 插入工艺相关性
/// </summary>
public static void InsertTechnicsRestrict(
System.Data.IDbConnection conn
, System.Data.IDbTransaction tran
, List <EACT_CUPRUM> CupRumList)
{
var querySql = new StringBuilder();
querySql.AppendLine("select mpt1.ID as preOperationID,mpt2.ID as operationID");
querySql.AppendLine("from MouldPartTechnics mpt1");
querySql.AppendLine("inner join MouldPartTechnics mpt2 on mpt1.partMonitorID = mpt2.partMonitorID ");
querySql.AppendLine(" and mpt2.operationOrderID = mpt1.operationOrderID+1");
querySql.AppendLine("where mpt1.partID in (");
querySql.Append(string.Join(",", Enumerable.Select(CupRumList, u => string.Format("'{0}'", u.MouldPart_PartID)).Distinct().ToArray()));
querySql.AppendLine(")");
querySql.AppendLine("order by mpt1.operationOrderID");
var list = conn.Query <TechnicsRestrict>(querySql.ToString(), null, tran);
foreach (var item in list)
{
string sql = " insert into TechnicsRestrict(preOperationID,operationID) values(@preOperationID,@operationID) ";
conn.Execute(sql, item);
}
}
public static System.Data.DataTable GetDataTable(System.Data.IDbCommand cmd)
{
System.Data.DataTable dt = new System.Data.DataTable();
using (System.Data.IDbConnection idbConn = GetConnection())
{
cmd.Connection = idbConn;
try
{
using (System.Data.Common.DbDataAdapter da = s_factory.CreateDataAdapter())
{
da.SelectCommand = (System.Data.Common.DbCommand)cmd;
da.Fill(dt);
}
}
catch
{
throw;
}
}
return(dt);
}
/// <summary>
/// Get the default connection, that was before configured
/// </summary>
/// <returns>Defalult connection (IDbConnection)</returns>
public static System.Data.IDbConnection Conection()
{
System.Data.IDbConnection newConnection = null;
if (StringConnection != null)
{
if (CType.Equals(ConnectionType.SqlServer))
{
newConnection = new System.Data.SqlClient.SqlConnection(StringConnection);
}
else if (CType.Equals(ConnectionType.Msql))
{
newConnection = new MySql.Data.MySqlClient.MySqlConnection(StringConnection);
}
else
{
throw new NullReferenceException("The argument ConnType does not satisfies the type of connection");
}
}
else
{
throw new NullReferenceException("String Connection Not Defined");
}
return(newConnection);
}
private void CreateSchemaAndUser(string sql, System.Data.IDbConnection connection)
{
if (connection == null)
{
throw new Exception("Failed to set a connection to external available instance");
}
if (connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
using (System.Data.IDbTransaction transaction = connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))
{
using (System.Data.IDbCommand command = connection.CreateCommand())
{
command.Transaction = transaction;
command.CommandText = sql;
try
{
//if (command.Connection.State == System.Data.ConnectionState.Closed)
// command.Connection.Open();
command.ExecuteScalar();
transaction.Commit();
}
catch (Exception ex)
{
if (connection != null && connection.State != System.Data.ConnectionState.Closed)
{
transaction.Rollback();
}
Maps.Instance.DuradosMap.Logger.Log("AppFactory", null, "CreateNewSchemaAndUser", ex, 1, "Faild to create new schema for new rds app");
throw new Exception("Faild to create new schema for new rds app", ex);
}
}
}
}
public static void DumpSQL(this System.Data.IDbConnection self, System.Data.IDbTransaction trans, string sql, params object[] parameters)
{
using (var c = self.CreateCommand())
{
c.CommandText = sql;
c.Transaction = trans;
if (parameters != null)
{
foreach (var p in parameters)
{
c.AddParameter(p);
}
}
using (var rd = c.ExecuteReader())
{
for (int i = 0; i < rd.FieldCount; i++)
{
Console.Write((i == 0 ? "" : "\t") + rd.GetName(i));
}
Console.WriteLine();
long n = 0;
while (rd.Read())
{
for (int i = 0; i < rd.FieldCount; i++)
{
Console.Write(string.Format((i == 0 ? "{0}" : "\t{0}"), rd.GetValue(i)));
}
Console.WriteLine();
n++;
}
Console.WriteLine(LC.L("{0} records", n));
}
}
}
public List <LogTownStatisItem> getTownList(System.Data.IDbConnection db, GetLogStatisList request, List <LogStatisItem> loglist, List <ADCDInfo> townList, List <LogStatisItem> loglistCounty, string _adcd, string stime, string etime, List <LogTownStatisItem> _list)
{
LogTownStatisItem ns = null; //330102000000000
var towlist = db.Select <ADCDInfo>(w => w.adcd.StartsWith(_adcd.Substring(0, 6)) && w.grade == 3 && w.adcd != _adcd).OrderBy(o => o.adcd).ToList();
_list = new List <LogTownStatisItem>();
towlist.ForEach(w =>
{
ns = new LogTownStatisItem();
ns.ADCD = w.adcd;
ns.ADNM = w.adnm;
var r = getTownPersonNum(db, w.adcd, stime, etime);
if (r != null)
{
ns.townPersonNum = r.townPersonNum;
ns.workgroupPersonNum = r.workgroupPersonNum;
ns.gridPersonNum = r.gridPersonNum;
ns.transferPersonNum = r.transferPersonNum;
ns.picPersonNum = r.picPersonNum;
}
//var a = db.SqlList<LogTownStatisItem>("EXEC LogTownPStatics @stime,@etime,@townadcd,@adcd", new { stime = stime, etime = etime, townadcd = adcd.Substring(0, 9), adcd = adcd }).ToList().FirstOrDefault();
//ns.townPersonNum = a != null ? a.townPersonNum : 0;
//var b = db.SqlList<LogTownStatisItem>("EXEC LogWorkingGroupPStatics @stime,@etime,@townadcd,@adcd", new { stime = stime, etime = etime, townadcd = adcd.Substring(0, 9), adcd = adcd }).ToList().FirstOrDefault();
//ns.workgroupPersonNum = b != null ? b.workgroupPersonNum:0;
//var c = db.SqlList<LogTownStatisItem>("EXEC LogGridPStatics @stime,@etime,@townadcd,@adcd", new { stime = stime, etime = etime, townadcd = adcd.Substring(0, 9), adcd = adcd }).ToList().FirstOrDefault();
//ns.gridPersonNum = c != null ? c.gridPersonNum : 0;
//var d = db.SqlList<LogTownStatisItem>("EXEC LogTransferPStatics @stime,@etime,@townadcd,@adcd", new { stime = stime, etime = etime, townadcd = adcd.Substring(0, 9), adcd = adcd }).ToList().FirstOrDefault();
//ns.transferPersonNum =d != null ? d.transferPersonNum:0;
//var e = db.SqlList<LogTownStatisItem>("EXEC LogPicPStatics @stime,@etime,@townadcd,@adcd", new { stime = stime, etime = etime, townadcd = adcd.Substring(0, 9), adcd = adcd }).ToList().FirstOrDefault();
//ns.picPersonNum = e != null ? e.picPersonNum : 0;
_list.Add(ns);
});
return(_list);
}
/// <summary>
/// 获得连接
/// </summary>
/// <returns></returns>
static System.Data.IDbConnection GetNewConnection()
{
System.Data.IDbConnection conn = null;
switch (Neusoft.FrameWork.Management.Connection.DBType)
{
case Neusoft.FrameWork.Management.Connection.EnumDBType.ORACLE:
conn = new System.Data.OracleClient.OracleConnection(Neusoft.FrameWork.Management.Connection.DataSouceString);
break;
case Neusoft.FrameWork.Management.Connection.EnumDBType.DB2:
conn = new IBM.Data.DB2.DB2Connection(Neusoft.FrameWork.Management.Connection.DataSouceString);
break;
case Neusoft.FrameWork.Management.Connection.EnumDBType.SQLSERVER:
conn = new System.Data.SqlClient.SqlConnection(Neusoft.FrameWork.Management.Connection.DataSouceString);
break;
default:
conn = new System.Data.OleDb.OleDbConnection(Neusoft.FrameWork.Management.Connection.DataSouceString);
break;
}
return(conn);
}
public override string GetDataBaseName(System.Data.IDbConnection con)
{
string result = GetDataBaseName(con).Split('.')[0];
return(result);
}
protected void initializeDbConnection() {
this.m_db_connection = Utils.CreateDbConnection();
this.m_datafetcher = Utils.InitializeDbConnection(this.m_config_dir, m_db_connection);
}
public NorthwindDupl(System.Data.IDbConnection connection)
: base(connection)
{
}
public DoctorData(System.Data.IDbConnection dbconnection)
{
_dbConnection = dbconnection;
}
private int Wcmm_Searchcount(iSqlConnection iConn, string Setof_Catalogue, string keywords, ListOptions options)
{
try
{
int numResults = 0;
string SQL = SQL_COUNT.Replace(Queryparam.Varstring.VAR_TABLENAME, TABLENAME);
SQL += CFunctions.Expression_GetPermit(options.GetAll, options.Username);
SQL += CFunctions.IsNullOrEmpty(Setof_Catalogue) ? "" : " AND A.cid IN(" + Setof_Catalogue + ")";
string Searchquery = CGeneral.Get_Searchquery(keywords);
SQL += string.IsNullOrEmpty(Searchquery) ? "" : " AND (" + Searchquery + ")";
using (iSqlDataReader dar = HELPER.executeReader(iConn, SQL))
{
if (dar.Read())
{
numResults = dar.IsDBNull(0) ? 0 : dar.GetInt32(0);
}
}
return numResults;
}
catch
{
return 0;
}
}
private int Wcmm_Searchcount(iSqlConnection iConn, int memberid, string keywords, ListOptions options)
{
try
{
int numResults = 0;
string SQL = SQL_COUNT.Replace(Queryparam.Varstring.VAR_TABLENAME, TABLENAME);
SQL += memberid == 0 ? "" : " AND A.memberid=" + memberid;
string Searchquery = CGeneral.Get_Searchquery(keywords);
SQL += string.IsNullOrEmpty(Searchquery) ? "" : " AND (" + Searchquery + ")";
using (iSqlDataReader dar = HELPER.executeReader(iConn, SQL))
{
if (dar.Read())
{
numResults = dar.IsDBNull(0) ? 0 : dar.GetInt32(0);
}
}
return numResults;
}
catch
{
return 0;
}
}
private int Wcmm_Getlistcount_com(iSqlConnection iConn, int status)
{
try
{
int numResults = 0;
string SQL = SQL_COUNT_COM.Replace(Queryparam.Varstring.VAR_TABLENAME, TABLENAME);
SQL += status == (int)CConstants.State.Status.None ? "" : " AND status=" + status;
using (iSqlDataReader dar = HELPER.executeReader(iConn, SQL))
{
if (dar.Read())
{
numResults = dar.IsDBNull(0) ? 0 : dar.GetInt32(0);
}
}
return numResults;
}
catch
{
return 0;
}
}
private int Searchcount(iSqlConnection iConn, string Setof_Catalogue, string keywords, ListOptions options)
{
try
{
int numResults = 0;
string SQL = SQL_COUNT.Replace(Queryparam.Varstring.VAR_TABLENAME, TABLENAME);
SQL += " AND A.status<>" + (int)CConstants.State.Status.Waitactive;
SQL += " AND A.status<>" + (int)CConstants.State.Status.Disabled;
SQL += options.Markas == (int)CConstants.State.MarkAs.None ? "" : " AND A.markas=" + options.Markas;
SQL += CFunctions.IsNullOrEmpty(Setof_Catalogue) ? "" : " AND A.cid IN(" + Setof_Catalogue + ")";
string Searchquery = CGeneral.Get_Searchquery(keywords);
SQL += string.IsNullOrEmpty(Searchquery) ? "" : " AND (" + Searchquery + ")";
using (iSqlDataReader dar = HELPER.executeReader(iConn, SQL))
{
if (dar.Read())
{
numResults = dar.IsDBNull(0) ? 0 : dar.GetInt32(0);
}
}
return numResults;
}
catch
{
return 0;
}
}
/// <summary>
/// ��ʼ������
/// </summary>
/// <param name="conn">���ݿ����Ӷ��ö�������Ѿ���</param>
public MyORMFramework( System.Data.IDbConnection conn )
{
myConnection = conn ;
}
private int Getlistcount(iSqlConnection iConn, int belongto, int iid, int markas)
{
try
{
int numResults = 0;
string SQL = SQL_COUNT.Replace(Queryparam.Varstring.VAR_TABLENAME, TABLENAME);
SQL += " AND A.status<>" + (int)CConstants.State.Status.Waitactive;
SQL += " AND A.status<>" + (int)CConstants.State.Status.Disabled;
SQL += markas == (int)CConstants.State.MarkAs.None ? "" : " AND A.markas=" + markas;
SQL += " AND A.belongto=" + belongto;
SQL += " AND A.iid=" + iid;
using (iSqlDataReader dar = HELPER.executeReader(iConn, SQL))
{
if (dar.Read())
{
numResults = dar.IsDBNull(0) ? 0 : dar.GetInt32(0);
}
}
return numResults;
}
catch
{
return 0;
}
}
public static void RealMain(string[] args)
{
//If we are on Windows, append the bundled "win-tools" programs to the search path
//We add it last, to allow the user to override with other versions
if (!Library.Utility.Utility.IsClientLinux)
{
Environment.SetEnvironmentVariable("PATH",
Environment.GetEnvironmentVariable("PATH") +
System.IO.Path.PathSeparator.ToString() +
System.IO.Path.Combine(
System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location),
"win-tools")
);
}
//If this executable is invoked directly, write to console, otherwise throw exceptions
bool writeConsole = System.Reflection.Assembly.GetEntryAssembly() == System.Reflection.Assembly.GetExecutingAssembly();
//If we are on windows we encrypt the database by default
//We do not encrypt on Linux as most distros use a SQLite library without encryption support,
//Linux users can use an encrypted home folder, or install a SQLite library with encryption support
if (!Library.Utility.Utility.IsClientLinux && string.IsNullOrEmpty(Environment.GetEnvironmentVariable(DB_KEY_ENV_NAME)))
{
//Note that the password here is a default password and public knowledge
//
//The purpose of this is to prevent casual read of the database, as well
// as protect from harddisk string scans, not to protect from determined
// attacks.
//
//If you desire better security, start Duplicati once with the commandline option
// --unencrypted-database to decrypt the database.
//Then set the environment variable DUPLICATI_DB_KEY to the desired key,
// and run Duplicati again without the --unencrypted-database option
// to re-encrypt it with the new key
//
//If you change the key, please note that you need to supply the same
// key when restoring the setup, as the setup being backed up will
// be encrypted as well.
Environment.SetEnvironmentVariable(DB_KEY_ENV_NAME, Library.AutoUpdater.AutoUpdateSettings.AppName + "_Key_42");
}
//Find commandline options here for handling special startup cases
Dictionary <string, string> commandlineOptions = Duplicati.Library.Utility.CommandLineParser.ExtractOptions(new List <string>(args));
foreach (string s in args)
{
if (
s.Equals("help", StringComparison.InvariantCultureIgnoreCase) ||
s.Equals("/help", StringComparison.InvariantCultureIgnoreCase) ||
s.Equals("usage", StringComparison.InvariantCultureIgnoreCase) ||
s.Equals("/usage", StringComparison.InvariantCultureIgnoreCase))
{
commandlineOptions["help"] = "";
}
}
//If the commandline issues --help, just stop here
if (commandlineOptions.ContainsKey("help"))
{
if (writeConsole)
{
Console.WriteLine(Strings.Program.HelpDisplayDialog);
foreach (Library.Interface.ICommandLineArgument arg in SupportedCommands)
{
Console.WriteLine(Strings.Program.HelpDisplayFormat(arg.Name, arg.LongDescription));
}
return;
}
else
{
throw new Exception("Server invoked with --help");
}
}
#if DEBUG
//Log various information in the logfile
if (!commandlineOptions.ContainsKey("log-file"))
{
commandlineOptions["log-file"] = System.IO.Path.Combine(StartupPath, "Duplicati.debug.log");
commandlineOptions["log-level"] = Duplicati.Library.Logging.LogMessageType.Profiling.ToString();
}
#endif
// Allow override of the environment variables from the commandline
if (commandlineOptions.ContainsKey("server-datafolder"))
{
Environment.SetEnvironmentVariable(DATAFOLDER_ENV_NAME, commandlineOptions["server-datafolder"]);
}
if (commandlineOptions.ContainsKey("server-encryption-key"))
{
Environment.SetEnvironmentVariable(DB_KEY_ENV_NAME, commandlineOptions["server-encryption-key"]);
}
//Set the %DUPLICATI_HOME% env variable, if it is not already set
if (string.IsNullOrEmpty(Environment.GetEnvironmentVariable(DATAFOLDER_ENV_NAME)))
{
#if DEBUG
//debug mode uses a lock file located in the app folder
Environment.SetEnvironmentVariable(DATAFOLDER_ENV_NAME, StartupPath);
#else
bool portableMode = commandlineOptions.ContainsKey("portable-mode") ? Library.Utility.Utility.ParseBool(commandlineOptions["portable-mode"], true) : false;
if (portableMode)
{
//Portable mode uses a data folder in the application home dir
Environment.SetEnvironmentVariable(DATAFOLDER_ENV_NAME, System.IO.Path.Combine(StartupPath, "data"));
}
else
{
//Normal release mode uses the systems "Application Data" folder
Environment.SetEnvironmentVariable(DATAFOLDER_ENV_NAME, System.IO.Path.Combine(System.Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData), Library.AutoUpdater.AutoUpdateSettings.AppName));
}
#endif
}
try
{
try
{
//This will also create Program.DATAFOLDER if it does not exist
Instance = new SingleInstance(Duplicati.Library.AutoUpdater.AutoUpdateSettings.AppName, Program.DATAFOLDER);
}
catch (Exception ex)
{
if (writeConsole)
{
Console.WriteLine(Strings.Program.StartupFailure(ex));
return;
}
else
{
throw new Exception(Strings.Program.StartupFailure(ex));
}
}
if (!Instance.IsFirstInstance)
{
if (writeConsole)
{
Console.WriteLine(Strings.Program.AnotherInstanceDetected);
return;
}
else
{
throw new SingleInstance.MultipleInstanceException(Strings.Program.AnotherInstanceDetected);
}
}
// Setup the log redirect
Duplicati.Library.Logging.Log.CurrentLog = Program.LogHandler;
if (commandlineOptions.ContainsKey("log-file"))
{
if (System.IO.File.Exists(commandlineOptions["log-file"]))
{
System.IO.File.Delete(commandlineOptions["log-file"]);
}
var loglevel = Duplicati.Library.Logging.LogMessageType.Error;
if (commandlineOptions.ContainsKey("log-level"))
{
Enum.TryParse <Duplicati.Library.Logging.LogMessageType>(commandlineOptions["log-level"], true, out loglevel);
}
Program.LogHandler.SetServerFile(commandlineOptions["log-file"], loglevel);
}
Version sqliteVersion = new Version((string)Duplicati.Library.SQLiteHelper.SQLiteLoader.SQLiteConnectionType.GetProperty("SQLiteVersion").GetValue(null, null));
if (sqliteVersion < new Version(3, 6, 3))
{
if (writeConsole)
{
//The official Mono SQLite provider is also broken with less than 3.6.3
Console.WriteLine(Strings.Program.WrongSQLiteVersion(sqliteVersion, "3.6.3"));
return;
}
else
{
throw new Exception(Strings.Program.WrongSQLiteVersion(sqliteVersion, "3.6.3"));
}
}
//Create the connection instance
System.Data.IDbConnection con = (System.Data.IDbConnection)Activator.CreateInstance(Duplicati.Library.SQLiteHelper.SQLiteLoader.SQLiteConnectionType);
try
{
DatabasePath = System.IO.Path.Combine(Program.DATAFOLDER, "Duplicati-server.sqlite");
if (!System.IO.Directory.Exists(System.IO.Path.GetDirectoryName(DatabasePath)))
{
System.IO.Directory.CreateDirectory(System.IO.Path.GetDirectoryName(DatabasePath));
}
#if DEBUG
//Default is to not use encryption for debugging
Program.UseDatabaseEncryption = commandlineOptions.ContainsKey("unencrypted-database") ? !Library.Utility.Utility.ParseBool(commandlineOptions["unencrypted-database"], true) : false;
#else
Program.UseDatabaseEncryption = commandlineOptions.ContainsKey("unencrypted-database") ? !Library.Utility.Utility.ParseBool(commandlineOptions["unencrypted-database"], true) : true;
#endif
con.ConnectionString = "Data Source=" + DatabasePath;
//Attempt to open the database, handling any encryption present
OpenDatabase(con);
Duplicati.Library.SQLiteHelper.DatabaseUpgrader.UpgradeDatabase(con, DatabasePath, typeof(Duplicati.Server.Database.Connection));
}
catch (Exception ex)
{
//Unwrap the reflection exceptions
if (ex is System.Reflection.TargetInvocationException && ex.InnerException != null)
{
ex = ex.InnerException;
}
if (writeConsole)
{
Console.WriteLine(Strings.Program.DatabaseOpenError(ex.Message));
return;
}
else
{
throw new Exception(Strings.Program.DatabaseOpenError(ex.Message), ex);
}
}
DataConnection = new Duplicati.Server.Database.Connection(con);
if (!DataConnection.ApplicationSettings.FixedInvalidBackupId)
{
DataConnection.FixInvalidBackupId();
}
StartOrStopUsageReporter();
if (commandlineOptions.ContainsKey("webservice-password"))
{
Program.DataConnection.ApplicationSettings.SetWebserverPassword(commandlineOptions["webservice-password"]);
}
ApplicationExitEvent = new System.Threading.ManualResetEvent(false);
Duplicati.Library.AutoUpdater.UpdaterManager.OnError += (Exception obj) =>
{
Program.DataConnection.LogError(null, "Error in updater", obj);
};
UpdatePoller = new UpdatePollThread();
DateTime lastPurge = new DateTime(0);
System.Threading.TimerCallback purgeTempFilesCallback = (x) => {
try
{
if (Math.Abs((DateTime.Now - lastPurge).TotalHours) < 23)
{
return;
}
lastPurge = DateTime.Now;
foreach (var e in Program.DataConnection.GetTempFiles().Where((f) => f.Expires < DateTime.Now))
{
try
{
if (System.IO.File.Exists(e.Path))
{
System.IO.File.Delete(e.Path);
}
}
catch (Exception ex)
{
Program.DataConnection.LogError(null, string.Format("Failed to delete temp file: {0}", e.Path), ex);
}
Program.DataConnection.DeleteTempFile(e.ID);
}
Duplicati.Library.Utility.TempFile.RemoveOldApplicationTempFiles((path, ex) => {
Program.DataConnection.LogError(null, string.Format("Failed to delete temp file: {0}", path), ex);
});
string pts;
if (!commandlineOptions.TryGetValue("log-retention", out pts))
{
pts = DEFAULT_LOG_RETENTION;
}
Program.DataConnection.PurgeLogData(Library.Utility.Timeparser.ParseTimeInterval(pts, DateTime.Now, true));
}
catch (Exception ex)
{
Program.DataConnection.LogError(null, "Failed during temp file cleanup", ex);
}
};
try
{
PurgeTempFilesTimer = new System.Threading.Timer(purgeTempFilesCallback, null, TimeSpan.FromHours(1), TimeSpan.FromDays(1));
}
catch (ArgumentOutOfRangeException)
{
//Bugfix for older Mono, slightly more resources used to avoid large values in the period field
PurgeTempFilesTimer = new System.Threading.Timer(purgeTempFilesCallback, null, TimeSpan.FromHours(1), TimeSpan.FromHours(1));
}
LiveControl = new LiveControls(DataConnection.ApplicationSettings);
LiveControl.StateChanged += new EventHandler(LiveControl_StateChanged);
LiveControl.ThreadPriorityChanged += new EventHandler(LiveControl_ThreadPriorityChanged);
LiveControl.ThrottleSpeedChanged += new EventHandler(LiveControl_ThrottleSpeedChanged);
Program.WorkThread = new Duplicati.Library.Utility.WorkerThread <Runner.IRunnerData>((x) =>
{
Runner.Run(x, true);
}, LiveControl.State == LiveControls.LiveControlState.Paused);
Program.Scheduler = new Scheduler(WorkThread);
Program.WorkThread.StartingWork += (worker, task) => { SignalNewEvent(null, null); };
Program.WorkThread.CompletedWork += (worker, task) => { SignalNewEvent(null, null); };
Program.WorkThread.WorkQueueChanged += (worker) => { SignalNewEvent(null, null); };
Program.Scheduler.NewSchedule += new EventHandler(SignalNewEvent);
Program.WorkThread.OnError += (worker, task, exception) => { Program.DataConnection.LogError(task == null ? null : task.BackupID, "Error in worker", exception); };
Action <long, Exception> registerTaskResult = (id, ex) => {
lock (Program.MainLock) {
// If the new results says it crashed, we store that instead of success
if (Program.TaskResultCache.Count > 0 && Program.TaskResultCache.Last().Key == id)
{
if (ex != null && Program.TaskResultCache.Last().Value == null)
{
Program.TaskResultCache.RemoveAt(Program.TaskResultCache.Count - 1);
}
else
{
return;
}
}
Program.TaskResultCache.Add(new KeyValuePair <long, Exception>(id, ex));
while (Program.TaskResultCache.Count > MAX_TASK_RESULT_CACHE_SIZE)
{
Program.TaskResultCache.RemoveAt(0);
}
}
};
Program.WorkThread.CompletedWork += (worker, task) => { registerTaskResult(task.TaskID, null); };
Program.WorkThread.OnError += (worker, task, exception) => { registerTaskResult(task.TaskID, exception); };
Program.WebServer = new WebServer.Server(commandlineOptions);
if (Program.WebServer.Port != DataConnection.ApplicationSettings.LastWebserverPort)
{
ServerPortChanged = true;
}
DataConnection.ApplicationSettings.LastWebserverPort = Program.WebServer.Port;
if (Library.Utility.Utility.ParseBoolOption(commandlineOptions, "ping-pong-keepalive"))
{
Program.PingPongThread = new System.Threading.Thread(PingPongMethod);
Program.PingPongThread.IsBackground = true;
Program.PingPongThread.Start();
}
ServerStartedEvent.Set();
ApplicationExitEvent.WaitOne();
}
catch (SingleInstance.MultipleInstanceException mex)
{
System.Diagnostics.Trace.WriteLine(Strings.Program.SeriousError(mex.ToString()));
if (writeConsole)
{
Console.WriteLine(Strings.Program.SeriousError(mex.ToString()));
}
else
{
throw mex;
}
}
catch (Exception ex)
{
System.Diagnostics.Trace.WriteLine(Strings.Program.SeriousError(ex.ToString()));
if (writeConsole)
{
Console.WriteLine(Strings.Program.SeriousError(ex.ToString()));
}
else
{
throw new Exception(Strings.Program.SeriousError(ex.ToString()), ex);
}
}
finally
{
StatusEventNotifyer.SignalNewEvent();
if (UpdatePoller != null)
{
UpdatePoller.Terminate();
}
if (Scheduler != null)
{
Scheduler.Terminate(true);
}
if (WorkThread != null)
{
WorkThread.Terminate(true);
}
if (Instance != null)
{
Instance.Dispose();
}
if (PurgeTempFilesTimer != null)
{
PurgeTempFilesTimer.Dispose();
}
if (PingPongThread != null)
{
try { PingPongThread.Abort(); }
catch { }
}
if (LogHandler != null)
{
LogHandler.Dispose();
}
}
}
public Blocklist(System.Data.IDbConnection connection, string volumename)
: base(connection, volumename)
{
}
public Basiclist(System.Data.IDbConnection connection, string volumename)
{
m_connection = connection;
m_volumename = volumename;
m_transaction = m_connection.BeginTransaction();
var tablename = TABLE_PREFIX + "-" + Library.Utility.Utility.ByteArrayAsHexString(Guid.NewGuid().ToByteArray());
using(var cmd = m_connection.CreateCommand())
{
cmd.Transaction = m_transaction;
cmd.ExecuteNonQuery(string.Format(@"CREATE TEMPORARY TABLE ""{0}"" {1}", tablename, TABLEFORMAT));
m_tablename = tablename;
}
m_insertCommand = m_connection.CreateCommand();
m_insertCommand.Transaction = m_transaction;
m_insertCommand.CommandText = string.Format(@"INSERT INTO ""{0}"" {1}", m_tablename, INSERTCOMMAND);
m_insertCommand.AddParameters(INSERTARGUMENTS);
}
public DBConnection(IDBProvider dbProvider, string ConnectionString)
{
this.CommandTimeout = 30;
this._dbProvider = dbProvider;
_conn = (System.Data.IDbConnection)_dbProvider.CreateConnection(ConnectionString);
}
protected virtual void Dispose(bool disposing)
{
if(!isDisposed)
{
if (disposing)
{
if (_trx != null) { _trx.Dispose(); _trx = null; }
if (_conn != null) { _conn.Dispose(); _conn = null; }
}
}
// Code to dispose the unmanaged resources
// held by the class
_trx = null;
_conn = null;
isDisposed = true;
}
private int Searchcount(iSqlConnection iConn, int belongto, string categoryid, string Searchquery)
{
try
{
int numResults = 0;
string SQL = SQL_COUNT_SEARCH.Replace(Queryparam.Varstring.VAR_TABLENAME, TABLENAME);
SQL += " AND A.status<>" + (int)CConstants.State.Status.Waitactive;
SQL += " AND A.status<>" + (int)CConstants.State.Status.Disabled;
SQL += CFunctions.IsNullOrEmpty(categoryid) || categoryid == "0" ? "" : " AND A.id IN(SELECT iid FROM " + LANG + CConstants.TBDBPREFIX + "itemcategory WHERE categoryid IN(" + categoryid + ") AND belongto=" + belongto + ")";
SQL += string.IsNullOrEmpty(Searchquery) ? "" : " AND (" + Searchquery + ")";
using (iSqlDataReader dar = HELPER.executeReader(iConn, SQL))
{
if (dar.Read())
{
numResults = dar.IsDBNull(0) ? 0 : dar.GetInt32(0);
}
}
return numResults;
}
catch
{
return 0;
}
}
public MissingBlockList(string volumename, System.Data.IDbConnection connection, System.Data.IDbTransaction transaction)
{
m_connection = connection;
m_transaction = new TemporaryTransactionWrapper(m_connection, transaction);
m_volumename = volumename;
var tablename = "MissingBlocks-" + Library.Utility.Utility.ByteArrayAsHexString(Guid.NewGuid().ToByteArray());
using(var cmd = m_connection.CreateCommand())
{
cmd.Transaction = m_transaction.Parent;
cmd.ExecuteNonQuery(string.Format(@"CREATE TEMPORARY TABLE ""{0}"" (""Hash"" TEXT NOT NULL, ""Size"" INTEGER NOT NULL, ""Restored"" INTEGER NOT NULL) ", tablename));
m_tablename = tablename;
var blockCount = cmd.ExecuteNonQuery(string.Format(@"INSERT INTO ""{0}"" (""Hash"", ""Size"", ""Restored"") SELECT DISTINCT ""Block"".""Hash"", ""Block"".""Size"", 0 AS ""Restored"" FROM ""Block"",""Remotevolume"" WHERE ""Block"".""VolumeID"" = ""Remotevolume"".""ID"" AND ""Remotevolume"".""Name"" = ? ", m_tablename), volumename);
if (blockCount == 0)
throw new Exception(string.Format("Unexpected empty block volume: {0}", volumename));
cmd.ExecuteNonQuery(string.Format(@"CREATE UNIQUE INDEX ""{0}-Ix"" ON ""{0}"" (""Hash"", ""Size"", ""Restored"")", tablename));
}
m_insertCommand = m_connection.CreateCommand();
m_insertCommand.Transaction = m_transaction.Parent;
m_insertCommand.CommandText = string.Format(@"UPDATE ""{0}"" SET ""Restored"" = ? WHERE ""Hash"" = ? AND ""Size"" = ? AND ""Restored"" = ? ", tablename);
m_insertCommand.AddParameters(4);
}
private int Wcmm_Getlistcount(iSqlConnection iConn, ListOptions options)
{
try
{
int numResults = 0;
string SQL = SQL_COUNT.Replace(Queryparam.Varstring.VAR_TABLENAME, TABLENAME);
SQL += CFunctions.Expression_GetPermit(options.GetAll, options.Username);
using (iSqlDataReader dar = HELPER.executeReader(iConn, SQL))
{
if (dar.Read())
{
numResults = dar.IsDBNull(0) ? 0 : dar.GetInt32(0);
}
}
return numResults;
}
catch
{
return 0;
}
}
public AuthorityImpl()
{
this.conn = Framework.Config.Connection.SQLiteConn();
}
private int Wcmm_Reportcount(iSqlConnection iConn, SearchInfo isearch, ListOptions options)
{
try
{
int numResults = 0;
string SQL = SQL_COUNT.Replace(Queryparam.Varstring.VAR_TABLENAME, TABLENAME);
SQL += CFunctions.IsNullOrEmpty(isearch.Setof_Category) ? "" : " AND A.cid IN(" + isearch.Setof_Category + ")";
SQL += string.IsNullOrEmpty(isearch.Searchquery) ? "" : " AND (" + isearch.Searchquery + ")";
SQL += " AND (A.timeupdate BETWEEN '" + isearch.Datefr + "' AND '" + isearch.Dateto + "')";
using (iSqlDataReader dar = HELPER.executeReader(iConn, SQL))
{
if (dar.Read())
{
numResults = dar.IsDBNull(0) ? 0 : dar.GetInt32(0);
}
}
return numResults;
}
catch
{
return 0;
}
}
public StorageHelper(System.Data.IDbConnection con)
{
m_connection = con;
m_previousTable = "Previous-" + Library.Utility.Utility.ByteArrayAsHexString(Guid.NewGuid().ToByteArray());
m_currentTable = "Current-" + Library.Utility.Utility.ByteArrayAsHexString(Guid.NewGuid().ToByteArray());
m_transaction = m_connection.BeginTransaction();
using(var cmd = m_connection.CreateCommand())
{
cmd.Transaction = m_transaction;
cmd.ExecuteNonQuery(string.Format(@"CREATE TEMPORARY TABLE ""{0}"" (""Path"" TEXT NOT NULL, ""FileHash"" TEXT NULL, ""MetaHash"" TEXT NOT NULL, ""Size"" INTEGER NOT NULL, ""Type"" INTEGER NOT NULL) ", m_previousTable));
cmd.ExecuteNonQuery(string.Format(@"CREATE TEMPORARY TABLE ""{0}"" (""Path"" TEXT NOT NULL, ""FileHash"" TEXT NULL, ""MetaHash"" TEXT NOT NULL, ""Size"" INTEGER NOT NULL, ""Type"" INTEGER NOT NULL) ", m_currentTable));
}
m_insertPreviousElementCommand = m_connection.CreateCommand();
m_insertPreviousElementCommand.Transaction = m_transaction;
m_insertPreviousElementCommand.CommandText = string.Format(@"INSERT INTO ""{0}"" (""Path"", ""FileHash"", ""MetaHash"", ""Size"", ""Type"") VALUES (?,?,?,?,?)", m_previousTable);
m_insertPreviousElementCommand.AddParameters(5);
m_insertCurrentElementCommand = m_connection.CreateCommand();
m_insertCurrentElementCommand.Transaction = m_transaction;
m_insertCurrentElementCommand.CommandText = string.Format(@"INSERT INTO ""{0}"" (""Path"", ""FileHash"", ""MetaHash"", ""Size"", ""Type"") VALUES (?,?,?,?,?)", m_currentTable);
m_insertCurrentElementCommand.AddParameters(5);
}
private int Wcmm_Getlistcount(iSqlConnection iConn, int memberid, ListOptions options)
{
try
{
int numResults = 0;
string SQL = SQL_COUNT.Replace(Queryparam.Varstring.VAR_TABLENAME, TABLENAME);
SQL += memberid == 0 ? "" : " AND A.memberid=" + memberid;
using (iSqlDataReader dar = HELPER.executeReader(iConn, SQL))
{
if (dar.Read())
{
numResults = dar.IsDBNull(0) ? 0 : dar.GetInt32(0);
}
}
return numResults;
}
catch
{
return 0;
}
}
public int GetDictCount(iSqlConnection iConn, string categoryid)
{
try
{
int numResults = 0;
string SQL =
@"select COUNT(*) as sl from vndd_itemcategory a left join
vndd_news b on a.iid = b.id where b.status <> 3 and a.categoryid = " + categoryid;
using (iSqlDataReader dar = HELPER.executeReader(iConn, SQL))
{
if (dar.Read())
{
numResults = dar.IsDBNull(0) ? 0 : dar.GetInt32(0);
}
}
return numResults;
}
catch
{
return 0;
}
}
private static void Main(string[] args)
{
HZ_Conn = new SqlConnection(HZ_ConnectionString);
HZ_Conn.Open();
var ddddd = @"DECLARE @cname nvarchar(50);
DECLARE @nikename nvarchar(50);
DECLARE @mobile varchar(30);
DECLARE @certificatetype nvarchar(20);
DECLARE @certificatecode nvarchar(50);
DECLARE @address nvarchar(200);
DECLARE @photourl nvarchar(500);
DECLARE @birthday date;
DECLARE @career nvarchar(20);
DECLARE @gender nvarchar(2);
DECLARE @heigth decimal(9,2);
DECLARE @weight decimal(9,2);
DECLARE @regison datetime;
set @cname = 'test_cname21';
SET @nikename = 'nikename_';
SET @mobile = 'mobile';
SET @certificatetype = '1';
SET @certificatecode = 'certificatecode';
SET @address = 'address';
SET @photourl = 'photourl';
SET @birthday = '1887-03-15';
SET @career = 'career';
SET @gender = '1';
SET @heigth = 2.08;
SET @weight = 66.66;
set @regison = GETDATE();
DECLARE @accountID varchar(40);
set @accountID = 'debc83c4-e7ba-4174-a40b-cc56b1540325';
DECLARE @ID int;
DECLARE @isNew as bit = 0;
MERGE CUSTOMER as target
using(select @accountID as AccountID) as source
on target.ACCOUNT_ID = Source.AccountID AND target.IS_DELETE = 0
WHEN MATCHED THEN
UPDATE set CNAME = @cname
,NICKNAME = @nikename
,MOBILE = @mobile
,CERTIFICATE_TYPE = @certificatetype
,CERTIFICATE_CODE = @certificatecode
,ADDRESS = @address
,PHOTO_URL = @photourl
,BIRTHDAY = @birthday
,CAREER = @career
,GENDER = @gender
,HEIGHT = @heigth
,WEIGHT = @weight
,REGIST_ON = @regison
,VERSION = target.VERSION + 1
,@ID = target.ID
WHEN NOT MATCHED BY TARGET THEN
INSERT (CNAME
,NICKNAME
,MOBILE
,CERTIFICATE_TYPE
,CERTIFICATE_CODE
,ADDRESS
,PHOTO_URL
,BIRTHDAY
,CAREER
,GENDER
,HEIGHT
,WEIGHT
,REGIST_ON
,ACCOUNT_ID
,IS_DELETE
,CREATED_ON
,MODIFIED_ON
,VERSION
,GUID)
values(@cname
,@nikename
,@mobile
,@certificatetype
,@certificatecode
,@address
,@photourl
,@birthday
,@career
,@gender
,@heigth
,@weight
,@regison
,@accountID
,0
,GETDATE()
,GETDATE()
,1
,NEWID());
if @ID is NULL
begin
set @ID = SCOPE_IDENTITY()
set @isNew = 1;
end;
SELECT @ID as ID,@isNew as IsNew";
for (int i = 0; i < 1000; i++)
{
Task.Factory.StartNew(() =>
{
try
{
using (var conn = new SqlConnection(HZ_ConnectionString))
{
conn.Open();
for (int j= 0; j < 100; j++)
{
conn.Execute(ddddd);
Thread.Sleep(50);
}
}
}
catch (System.Exception ex)
{
Trace.WriteLine(ex.StackTrace);
}
});
}
Console.WriteLine("完成");
Console.ReadLine();
//DC_Conn = new SqlConnection(DC_ConnectionString);
//DC_Conn.Open();
var sql56 = @"SELECT
Date,ISNULL([4],0) AS [4],ISNULL([7],0) AS [7],ISNULL([14],0) AS [14],ISNULL([15],0) AS [15],ISNULL([19],0) AS [19],ISNULL([20],0) AS [20],ISNULL([21],0) AS [21],ISNULL([22],0) AS [22],ISNULL([23],0) AS [23],ISNULL([24],0) AS [24],ISNULL([25],0) AS [25],ISNULL([26],0) AS [26],ISNULL([27],0) AS [27],ISNULL([28],0) AS [28],ISNULL([29],0) AS [29],ISNULL([30],0) AS [30],ISNULL([1020],0) AS [1020],ISNULL([1021],0) AS [1021],ISNULL([1022],0) AS [1022],ISNULL([1023],0) AS [1023],ISNULL([1024],0) AS [1024],ISNULL([1025],0) AS [1025],ISNULL([1026],0) AS [1026],ISNULL([1027],0) AS [1027],ISNULL([1028],0) AS [1028],ISNULL([1029],0) AS [1029],ISNULL([1030],0) AS [1030],ISNULL([1031],0) AS [1031],ISNULL([1032],0) AS [1032],ISNULL([1033],0) AS [1033],ISNULL([1034],0) AS [1034],ISNULL([1035],0) AS [1035],ISNULL([1036],0) AS [1036],ISNULL([1037],0) AS [1037],ISNULL([1038],0) AS [1038],ISNULL([1039],0) AS [1039],ISNULL([1040],0) AS [1040],ISNULL([1041],0) AS [1041],ISNULL([1042],0) AS [1042],ISNULL([1043],0) AS [1043],ISNULL([1044],0) AS [1044],ISNULL([1045],0) AS [1045],ISNULL([1046],0) AS [1046],ISNULL([1047],0) AS [1047],ISNULL([1048],0) AS [1048],ISNULL([1049],0) AS [1049],ISNULL([1050],0) AS [1050],ISNULL([1051],0) AS [1051],ISNULL([1052],0) AS [1052],ISNULL([1053],0) AS [1053],ISNULL([1054],0) AS [1054],ISNULL([1055],0) AS [1055],ISNULL([1056],0) AS [1056],ISNULL([1057],0) AS [1057],ISNULL([1058],0) AS [1058],ISNULL([1059],0) AS [1059],ISNULL([1060],0) AS [1060],ISNULL([1061],0) AS [1061],ISNULL([1062],0) AS [1062],ISNULL([1063],0) AS [1063],ISNULL([1066],0) AS [1066],ISNULL([1067],0) AS [1067],ISNULL([1068],0) AS [1068],ISNULL([1069],0) AS [1069],ISNULL([1070],0) AS [1070],ISNULL([1071],0) AS [1071],ISNULL([1072],0) AS [1072],ISNULL([1073],0) AS [1073],ISNULL([1074],0) AS [1074],ISNULL([1075],0) AS [1075],ISNULL([1076],0) AS [1076],ISNULL([1077],0) AS [1077],ISNULL([1078],0) AS [1078],ISNULL([1079],0) AS [1079],ISNULL([1080],0) AS [1080],ISNULL([1081],0) AS [1081],ISNULL([1082],0) AS [1082],ISNULL([1083],0) AS [1083],ISNULL([1084],0) AS [1084],ISNULL([1085],0) AS [1085],ISNULL([1086],0) AS [1086],ISNULL([1087],0) AS [1087],ISNULL([2081],0) AS [2081],ISNULL([2082],0) AS [2082],ISNULL([2083],0) AS [2083],ISNULL([2084],0) AS [2084],ISNULL([2085],0) AS [2085],ISNULL([2086],0) AS [2086],ISNULL([2087],0) AS [2087],ISNULL([2088],0) AS [2088],ISNULL([2089],0) AS [2089],ISNULL([2090],0) AS [2090],ISNULL([2091],0) AS [2091],ISNULL([2092],0) AS [2092],ISNULL([2093],0) AS [2093],ISNULL([2094],0) AS [2094],ISNULL([2095],0) AS [2095],ISNULL([2096],0) AS [2096],ISNULL([2097],0) AS [2097],ISNULL([2098],0) AS [2098],ISNULL([2099],0) AS [2099],ISNULL([2104],0) AS [2104],ISNULL([2105],0) AS [2105],ISNULL([2106],0) AS [2106],ISNULL([2107],0) AS [2107],ISNULL([2108],0) AS [2108],ISNULL([2109],0) AS [2109],ISNULL([2110],0) AS [2110],ISNULL([2111],0) AS [2111],ISNULL([2112],0) AS [2112],ISNULL([2113],0) AS [2113],ISNULL([2114],0) AS [2114],ISNULL([2115],0) AS [2115],ISNULL([2116],0) AS [2116],ISNULL([2117],0) AS [2117],ISNULL([2118],0) AS [2118],ISNULL([2119],0) AS [2119],ISNULL([2120],0) AS [2120],ISNULL([2121],0) AS [2121],ISNULL([2122],0) AS [2122],ISNULL([2123],0) AS [2123],ISNULL([2124],0) AS [2124],ISNULL([2125],0) AS [2125],ISNULL([2126],0) AS [2126],ISNULL([2127],0) AS [2127],ISNULL([2128],0) AS [2128],ISNULL([2129],0) AS [2129],ISNULL([2130],0) AS [2130],ISNULL([2131],0) AS [2131],ISNULL([2132],0) AS [2132],ISNULL([2133],0) AS [2133],ISNULL([2134],0) AS [2134],ISNULL([2135],0) AS [2135],ISNULL([2136],0) AS [2136],ISNULL([2137],0) AS [2137],ISNULL([2138],0) AS [2138],ISNULL([3137],0) AS [3137],ISNULL([3138],0) AS [3138],ISNULL([3139],0) AS [3139],ISNULL([3140],0) AS [3140],ISNULL([3143],0) AS [3143],ISNULL([3144],0) AS [3144],ISNULL([3145],0) AS [3145],ISNULL([3146],0) AS [3146],ISNULL([3147],0) AS [3147],ISNULL([3148],0) AS [3148],ISNULL([3149],0) AS [3149]
FROM
(SELECT
t1.date AS Date
,ISNULL(SEVICE_DEPT_ID, 0) AS DeptID
,ISNULL(RegistCount, 0) AS RegistCount
FROM (SELECT
CONVERT(VARCHAR(10), DATEADD(dd, number, CONVERT(VARCHAR(8), '2016-08-05', 120) + '01'), 120) AS date
FROM master..spt_values
WHERE type = 'P'
AND DATEADD(dd, number, CONVERT(VARCHAR(8), '2016-08-05', 120) + '01') <= '2016-08-23'
AND DATEADD(dd, number, CONVERT(VARCHAR(8), '2016-08-05', 120) + '01') >= '2016-08-05') t1
LEFT JOIN (SELECT
STAT_DATE
,SEVICE_DEPT_ID
,SUM(REGIST_COUNT) RegistCount
FROM STAT_SCAN_REGIST_DAILY
WHERE IS_DELETE = 0
AND STAT_DATE >= '2016-08-05'
AND STAT_DATE < '2016-08-23'
GROUP BY STAT_DATE
,SEVICE_DEPT_ID) t2
ON t1.date = t2.STAT_DATE) tt1
PIVOT (SUM(RegistCount) FOR DeptID IN ([4],[7],[14],[15],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[1020],[1021],[1022],[1023],[1024],[1025],[1026],[1027],[1028],[1029],[1030],[1031],[1032],[1033],[1034],[1035],[1036],[1037],[1038],[1039],[1040],[1041],[1042],[1043],[1044],[1045],[1046],[1047],[1048],[1049],[1050],[1051],[1052],[1053],[1054],[1055],[1056],[1057],[1058],[1059],[1060],[1061],[1062],[1063],[1066],[1067],[1068],[1069],[1070],[1071],[1072],[1073],[1074],[1075],[1076],[1077],[1078],[1079],[1080],[1081],[1082],[1083],[1084],[1085],[1086],[1087],[2081],[2082],[2083],[2084],[2085],[2086],[2087],[2088],[2089],[2090],[2091],[2092],[2093],[2094],[2095],[2096],[2097],[2098],[2099],[2104],[2105],[2106],[2107],[2108],[2109],[2110],[2111],[2112],[2113],[2114],[2115],[2116],[2117],[2118],[2119],[2120],[2121],[2122],[2123],[2124],[2125],[2126],[2127],[2128],[2129],[2130],[2131],[2132],[2133],[2134],[2135],[2136],[2137],[2138],[3137],[3138],[3139],[3140],[3143],[3144],[3145],[3146],[3147],[3148],[3149])) AS ReGist";
var result = HZ_Conn.Query(sql56);
var temlate = "【安宁鑫湖医院】温馨提示，您的体检报告已经完成，请查看您的健康状况及阳性指标，永久保存报告详情请点击";
//var temlate = "【河南省直三院】温馨提示，您的体检报告已经完成，请查看您的健康状况及阳性指标，永久保存报告详情请点击";
var url = "http://webapp.hc.ihaozhuo.com/SMSPromotion.html#/{0}/{1}/{2}";
// http://webapp.hc.ihaozhuo.com/SMSPromotion.html#/{task}/{mobile}/{org}
var table = "bjbr001";
//var mobiles = new string[] { "15900860546","18297300619","17783055953" };
var mobiles = HZ_Conn.Query<string>("select distinct Mobilphone from " + table);
var shortLinkProxy = SortLinkServerProxy.ShareInstance();
var time = DateTime.Now;
var sms = (from c in mobiles
where !string.IsNullOrEmpty(c) && Regex.Match(c, "1[2|3|5|7|8|][0-9]{9}").Success
select new
{
mobile = c,
sms = shortLinkProxy.getSortLink(string.Format(url, 1, c, table)) + " "
}).ToList();
Trace.WriteLine(string.Format("cost:{0}", DateTime.Now.Subtract(time).TotalSeconds));
using (StreamWriter sw2 = new StreamWriter(table + ".txt", true, Encoding.Default))
{
var now = DateTime.Now;
Trace.WriteLine("write start");
foreach (var item in sms)
{
sw2.WriteLine(item.mobile + " " + item.sms);
//Trace.WriteLine(item);
}
sw2.Flush();
Trace.WriteLine(string.Format("write cost:{0}", DateTime.Now.Subtract(now).TotalSeconds));
Trace.WriteLine(string.Format("电话号码数量：{0}，有效电话号码：{1}", mobiles.Count(), sms.Count()));
}
return;
var examSQL = @"SELECT
t.AGE AS Age,
t.BIRTHDAY AS Birthday,
t.CHECK_DATE AS CheckDate,
t.CHECK_UNIT_CODE AS CheckUnitCode,
t.CHECK_UNIT_NAME AS CheckUnitName,
t.COMMIT_USER_NAME AS CommitUserName,
t.CUSTOMER_ID AS CustomerId,
t.CUSTOMER_NAME AS CustomerName,
t.ID AS Id,
t.REG_DATE AS RegDate,
t.REPORT_DATE AS ReportDate,
t.WORK_NO AS WorkNo,
t.Created_By AS CreatedBy,
t.Created_On AS CreatedOn,
t.Guid AS Guid,
t.Is_Delete AS IsDelete,
t.Modified_By AS ModifiedBy,
t.Modified_On AS ModifiedOn,
t.Version AS Version
FROM
RPT_EXAM t
where t.CHECK_UNIT_CODE = @checkUnitCode
and t.WORK_NO = @workNo";
// var examSQL = @"SELECT
// t.AGE AS Age,
// t.BIRTHDAY AS Birthday,
// t.CHECK_DATE AS CheckDate,
// t.CHECK_UNIT_CODE AS CheckUnitCode,
// t.CHECK_UNIT_NAME AS CheckUnitName,
// t.COMMIT_USER_NAME AS CommitUserName,
// t.CUSTOMER_ID AS CustomerId,
// t.CUSTOMER_NAME AS CustomerName,
// t.ID AS Id,
// t.REG_DATE AS RegDate,
// t.REPORT_DATE AS ReportDate,
// t.WORK_NO AS WorkNo,
// t.Created_By AS CreatedBy,
// t.Created_On AS CreatedOn,
// t.Guid AS Guid,
// t.Is_Delete AS IsDelete,
// t.Modified_By AS ModifiedBy,
// t.Modified_On AS ModifiedOn,
// t.Version AS Version
//FROM
// RPT_EXAM t
//WHERE
// (t.CHECK_UNIT_CODE = :unitCode1
// AND t.WORK_NO = '0000000023')
// OR (t.CHECK_UNIT_CODE = 'bjbr002'
// AND t.WORK_NO = '0000000025')
// OR (t.CHECK_UNIT_CODE = 'bjbr002'
// AND t.WORK_NO = '0000000061')";
var sql = @"SELECT
t.CHECK_ITEM_CODE AS CheckItemCode,
t.CHECK_ITEM_NAME AS CheckItemName,
t.CHECK_STATE_ID AS CheckStateId,
t.CHECK_USER_NAME AS CheckUserName,
t.DEPARTMENT_NAME AS DepartmentName,
t.ID AS Id,
t.RPT_EXAM_ID AS RptExamId,
t.SALE_PRICE AS SalePrice,
t.SHOW_INDEX AS ShowIndex,
t.Created_By AS CreatedBy,
t.Created_On AS CreatedOn,
t.Guid AS Guid,
t.Is_Delete AS IsDelete,
t.Modified_By AS ModifiedBy,
t.Modified_On AS ModifiedOn,
t.Version AS Version
FROM
RPT_CHECK_ITEM t
WHERE
t.Is_Delete = 0
AND t.RPT_EXAM_ID = @examID
SELECT
t.APPEND_INFO AS AppendInfo,
t.CHECK_INDEX_CODE AS CheckIndexCode,
t.CHECK_INDEX_NAME AS CheckIndexName,
t.HIGH_VALUE_REF AS HighValueRef,
t.HZ_CHECK_INDEX_NAME AS HzCheckIndexName,
t.ID AS Id,
t.IS_ABANDON AS IsAbandon,
t.IS_CALC AS IsCalc,
t.LOW_VALUE_REF AS LowValueRef,
t.RESULT_FLAG_ID AS ResultFlagId,
t.RESULT_TYPE_ID AS ResultTypeId,
t.RESULT_VALUE AS ResultValue,
t.RPT_EXAMITEM_ID AS RptExamitemId,
t.SHOW_INDEX AS ShowIndex,
t.TEXT_REF AS TextRef,
t.UNIT AS Unit,
t.Created_By AS CreatedBy,
t.Created_On AS CreatedOn,
t.Guid AS Guid,
t.Is_Delete AS IsDelete,
t.Modified_By AS ModifiedBy,
t.Modified_On AS ModifiedOn,
t.Version AS Version
FROM
RPT_CHECK_RESULTS t
WHERE
t.Is_Delete = 0
AND t.RPT_EXAMITEM_ID IN (SELECT
t1.ID
FROM RPT_CHECK_ITEM t1
WHERE t1.RPT_EXAM_ID = @examID)
SELECT
t.Id AS Id,
t.Is_Privacy AS IsPrivacy,
t.Medical_Explanation AS MedicalExplanation,
t.Reason_Result AS ReasonResult,
t.Review_Advice AS ReviewAdvice,
t.Rpt_Exam_Id AS RptExamId,
t.Summary_Advice AS SummaryAdvice,
t.Summary_Code AS SummaryCode,
t.Summary_Description AS SummaryDescription,
t.Summary_Name AS SummaryName,
t.Created_By AS CreatedBy,
t.Created_On AS CreatedOn,
t.Guid AS Guid,
t.Is_Delete AS IsDelete,
t.Modified_By AS ModifiedBy,
t.Modified_On AS ModifiedOn,
t.Version AS Version
FROM
RPT_SUMMARYS t
WHERE
t.Is_Delete = 0
AND t.RPT_EXAM_ID = @examID
SELECT
t.Group_Info_Id
FROM
RPT_LABELS t
WHERE
t.Is_Delete = 0
AND t.RPT_EXAM_ID = @examID";//
var testsql = @"select count(t1.ID)
from DOCTOR t1,DOCTOR_ACCOUNT t2
where t1.ID = t2.DOCTOR_ID
and t1.IS_DELETE = 0
and t2.IS_DELETE = 0
and t2.IS_ENABLED = 1
and t1.SERVICE_DEPT_ID = @deptID
select count(t2.ID)
from DOCTOR t1,CUSTOMER t2,CUST_SERVICE_INFO t3,DOCTOR_ACCOUNT t4
where t1.ID = t3.DOCTOR_ID
and t2.ID = t3.CUSTOMER_ID
and t4.DOCTOR_ID = t1.ID
and t1.IS_DELETE = 0
and t2.IS_DELETE = 0
and t3.IS_DELETE = 0
and t4.IS_DELETE = 0
and t4.IS_ENABLED = 1
and t1.SERVICE_DEPT_ID = @deptID";
var sql_dc = @"SELECT
t1.RID as RID,
t2.Code as CheckUnitCode,
t1.Mobilphone as Mobilphone,
t1.RecordDate as RecordDate
FROM
CustomerReg t1,
CheckUnits t2
WHERE
t1.CheckUnitsRID = t2.RID
AND t1.Mobilphone IS NOT NULL
AND t1.Mobilphone != ''
AND t1.RecordDate >= CAST(@dateStart AS DATETIME)
AND t1.RecordDate <= CAST(@dateEnd AS DATETIME)
AND t2.Code IN (@deptCodes)";
var testSql = @"SELECT
t5.date,
ISNULL(t1.count, 0) AS SmsSendCount,
ISNULL(t2.count, 0) AS Hits,
ISNULL(t3.count, 0) AS Dloaded,
ISNULL(t4.count, 0) AS Registrations
FROM
(SELECT
CAST(t.CREATED_ON AS DATE) AS date,
COUNT(t.CREATED_ON) AS count
FROM dbo.SMS_DETAIL t
WHERE t.CREATED_ON >= @start
AND t.CREATED_ON <= DATEADD(day,1,@end)
--and t.SERVICE_DEPT_ID =
GROUP BY CAST(t.CREATED_ON AS DATE)) t1
FULL OUTER JOIN (SELECT
CAST(t.CREATED_ON AS DATE) AS date,
COUNT(t.CREATED_ON) AS count
FROM dbo.SMS_POPULARIZE_TRAFFIC t
WHERE t.CREATED_ON >= @start
AND t.CREATED_ON <= DATEADD(day,1,@end)
--and t.SERVICE_DEPT_ID =
GROUP BY CAST(t.CREATED_ON AS DATE)) t2 ON t1.date = t2.date
FULL OUTER JOIN (SELECT
CAST(t.CREATED_ON AS DATE) AS date,
COUNT(t.CREATED_ON) AS count
FROM dbo.SMS_POPULARIZE_DOWNLOADS t
WHERE t.CREATED_ON >= @start
AND t.CREATED_ON <= DATEADD(day,1,@end)
--and t.SERVICE_DEPT_ID =
GROUP BY CAST(t.CREATED_ON AS DATE)) t3 ON t2.date = t3.date
FULL OUTER JOIN (SELECT
CAST(t1.CREATED_ON AS DATE) AS date,
COUNT(t1.CREATED_ON) AS count
FROM dbo.CUSTOMER t1,
dbo.CUST_SERVICE_INFO t2
WHERE t1.ID = t2.CUSTOMER_ID
AND t1.CREATED_ON >= @start
AND t1.CREATED_ON <= DATEADD(day,1,@end)
--and t2.SERVICE_DEPT_ID =
GROUP BY CAST(t1.CREATED_ON AS DATE)) t4 ON t3.date = t4.date
RIGHT OUTER JOIN (SELECT
CONVERT(VARCHAR(10), DATEADD(dd, number, CONVERT(VARCHAR(8), @start, 120) + '01'), 120) AS date
FROM master..spt_values
WHERE type = 'P'
AND DATEADD(dd, number, CONVERT(VARCHAR(8), @start, 120) + '01') <= @end
AND DATEADD(dd, number, CONVERT(VARCHAR(8), @start, 120) + '01') >= @start) t5 ON t4.date = t5.date";
var testSql2 = @"SELECT
t5.date,
ISNULL(t1.count, 0) AS SmsSendCount,
ISNULL(t2.count, 0) AS Hits,
ISNULL(t3.count, 0) AS Dloaded,
ISNULL(t4.count, 0) AS Registrations
FROM
(SELECT
CAST(t.CREATED_ON AS DATE) AS date,
COUNT(t.CREATED_ON) AS count
FROM dbo.SMS_DETAIL t
WHERE t.CREATED_ON >= @start
AND t.CREATED_ON <= DATEADD(DAY, 1, @end)
and t.SERVICE_DEPT_ID = @deptID
GROUP BY CAST(t.CREATED_ON AS DATE)) t1
FULL OUTER JOIN (SELECT
CAST(t.CREATED_ON AS DATE) AS date,
COUNT(t.CREATED_ON) AS count
FROM dbo.SMS_POPULARIZE_TRAFFIC t
WHERE t.CREATED_ON >= @start
AND t.CREATED_ON <= DATEADD(DAY, 1, @end)
and t.SERVICE_DEPT_ID = @deptID
GROUP BY CAST(t.CREATED_ON AS DATE)) t2 ON t1.date = t2.date
FULL OUTER JOIN (SELECT
CAST(t.CREATED_ON AS DATE) AS date,
COUNT(t.CREATED_ON) AS count
FROM dbo.SMS_POPULARIZE_DOWNLOADS t
WHERE t.CREATED_ON >= @start
AND t.CREATED_ON <= DATEADD(DAY, 1, @end)
and t.SERVICE_DEPT_ID = @deptID
GROUP BY CAST(t.CREATED_ON AS DATE)) t3 ON t2.date = t3.date
FULL OUTER JOIN (SELECT
CAST(t1.CREATED_ON AS DATE) AS date,
COUNT(t1.CREATED_ON) AS count
FROM dbo.CUSTOMER t1,
dbo.CUST_SERVICE_INFO t2
WHERE t1.ID = t2.CUSTOMER_ID
AND t1.CREATED_ON >= @start
AND t1.CREATED_ON <= DATEADD(DAY, 1, @end)
and t2.SERVICE_DEPT_ID = @deptID
GROUP BY CAST(t1.CREATED_ON AS DATE)) t4 ON t3.date = t4.date
RIGHT OUTER JOIN (SELECT
CONVERT(VARCHAR(10), DATEADD(dd, number, CONVERT(VARCHAR(8), @start, 120) + '01'), 120) AS date
FROM master..spt_values
WHERE type = 'P'
AND DATEADD(dd, number, CONVERT(VARCHAR(8), @start, 120) + '01') <= @end
AND DATEADD(dd, number, CONVERT(VARCHAR(8), @start, 120) + '01') >= @start) t5 ON t4.date = t5.date";
var items = HZ_Conn.Query<SmsStatisticResultItem>(testSql2, new { start = new DateTime(2016, 5, 1), end = new DateTime(2016, 5, 30), deptID = 4 });
//Console.ReadLine();
//var dddd = DC_Conn.Query<DataCenterCustomerInfoModel>(sql_dc,new { deptCodes = new string[] { "bjbr002" },dateStart = new DateTime(2016,2,17),dateEnd = new DateTime(2016,2,18) });
//Stopwatch timer = new Stopwatch();
//var testKeys = new int[] { 100,200,500,1000,1500,2000 };
//foreach(var key in testKeys)
//{
// var mobiles = dddd.Take(key).Select(c => c.Mobilphone);
// var sql_hz_mobile = @"select t.MOBILE from CUSTOMER t where t.MOBILE in @mobile";
// timer.Start();
// var ddddddd = HZ_Conn.Query<string>(sql_hz_mobile,new { mobile = mobiles });
// timer.Stop();
// var second = timer.Elapsed.TotalSeconds;
// Trace.WriteLine(string.Format("{1} phones Execution time was {0:F1} s.",second,key));
//}
//Console.ReadLine();
//var query1 = HZ_Conn.QueryMultiple(testsql,new { deptID = 4 });
//var count1 = query1.Read<int>();
//var count2 = query1.Read<int>();
var exams = HZ_Conn.Query<RptExam>(examSQL, new { checkUnitCode = "bjbr002", workNo = "0000068516" });
var exam = exams.FirstOrDefault();
if (exam != null)
{
using (var query = HZ_Conn.QueryMultiple(sql, new { examID = exams.FirstOrDefault().Id }))
{
var chechItems = query.Read<RptCheckItem>().ToList();
var checkResults = query.Read<RptCheckResult>().ToList();
var summarys = query.Read<RptSummary>().ToList();
var labels = query.Read<int>().ToList();
}
}
}
private int Getlistcountattr(iSqlConnection iConn, string categoryid, int attrid, ListOptions options)
{
try
{
int numResults = 0;
string SQL = SQL_COUNT.Replace(Queryparam.Varstring.VAR_TABLENAME, TABLENAME);
SQL += " AND A.status<>" + (int)CConstants.State.Status.Waitactive;
SQL += " AND A.status<>" + (int)CConstants.State.Status.Disabled;
SQL += options.Markas == (int)CConstants.State.MarkAs.None ? "" : " AND A.markas=" + options.Markas;
SQL += CFunctions.IsNullOrEmpty(categoryid) ? "" : " AND A.id IN(SELECT iid FROM " + LANG + CConstants.TBDBPREFIX + "itemcategory WHERE categoryid IN(" + categoryid + ") AND belongto=" + Webcmm.Id.Video + ")";
SQL += attrid == 0 ? "" : " AND A.id IN(SELECT iid FROM " + LANG + CConstants.TBDBPREFIX + "itemcategoryattr WHERE categoryid IN(" + attrid + ") AND belongto=" + Webcmm.Id.Video + ")";
using (iSqlDataReader dar = HELPER.executeReader(iConn, SQL))
{
if (dar.Read())
{
numResults = dar.IsDBNull(0) ? 0 : dar.GetInt32(0);
}
}
return numResults;
}
catch
{
return 0;
}
}
public NorthwindAbstractBaseClass(System.Data.IDbConnection connection)
: base(connection)
{
}
private int Wcmm_Searchcount(iSqlConnection iConn, string categoryid, string Searchquery, ListOptions options)
{
try
{
int numResults = 0;
string SQL = SQL_COUNT.Replace(Queryparam.Varstring.VAR_TABLENAME, TABLENAME);
SQL += CFunctions.Expression_GetPermit(options.GetAll, options.Username);
SQL += CFunctions.IsNullOrEmpty(categoryid) ? "" : " AND A.id IN(SELECT iid FROM " + LANG + CConstants.TBDBPREFIX + "itemcategory WHERE categoryid IN(" + categoryid + ") AND belongto=" + Webcmm.Id.Video + ")";
SQL += string.IsNullOrEmpty(Searchquery) ? "" : " AND (" + Searchquery + ")";
using (iSqlDataReader dar = HELPER.executeReader(iConn, SQL))
{
if (dar.Read())
{
numResults = dar.IsDBNull(0) ? 0 : dar.GetInt32(0);
}
}
return numResults;
}
catch
{
return 0;
}
}
protected IEnumerable <Dictionary <string, object> > ExecuteQuery(string query, List <DTO.QueryComposer.QueryComposerResponseErrorDTO> errors, bool viewSQL)
{
List <Dictionary <string, object> > results = new List <Dictionary <string, object> >();
List <string> ltCols = new List <string>();
System.Data.IDbConnection conn = null;
try
{
if (!viewSQL)
{
conn = Utilities.OpenConnection(settings, logger);
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = query;
cmd.CommandTimeout = Convert.ToInt32(settings.GetSetting("CommandTimeout", 120));
logger.Debug("Executing query:" + Environment.NewLine + query);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Dictionary <string, object> row = new Dictionary <string, object>();
for (int i = 0; i < reader.FieldCount; i++)
{
row.Add(reader.GetName(i), reader.GetValue(i).ConvertDBNullToNull());
}
results.Add(row);
}
}
var testRow = results.FirstOrDefault();
if (testRow != null && testRow.Count > 0)
{
try
{
foreach (string column in testRow.Keys)
{
if (SummaryQueryUtil.IsCheckedColumn(column))
{
ltCols.Add(column);
var depCols = SummaryQueryUtil.GetDependentComputedColumns(column, testRow);
if (depCols != null && depCols.Count > 0)
{
ltCols.AddRange(depCols);
}
}
}
}
catch { }
}
var columnNames = ltCols.ToArray();
if (columnNames != null && columnNames.Length != 0 && _lowThresholdValue.HasValue)
{
foreach (Dictionary <string, object> row in results)
{
try
{
foreach (string column in columnNames)
{
object currentValue;
if (row.TryGetValue(column, out currentValue))
{
double value = Convert.ToDouble(currentValue);
if (value > 0 && value < _lowThresholdValue)
{
row[LowThresholdColumnName] = true;
}
}
}
}
catch { }
}
}
}
}
else
{
Dictionary <string, object> row = new Dictionary <string, object>();
row.Add("SQL", query);
results.Add(row);
}
}
catch (Exception ex)
{
errors.Add(new DTO.QueryComposer.QueryComposerResponseErrorDTO
{
Code = "999",
Description = ex.UnwindException(true)
});
}
finally
{
if (conn != null)
{
conn.Dispose();
conn = null;
}
}
return(results);
}
public void Updatetotal_detail(iSqlConnection iConn, string name)
{
try
{
string SQL = SQL_GETIFO;
SQL += " AND name='" + name + "'";
using (iSqlDataReader dar = HELPER.executeReader(iConn, SQL))
{
if (dar.Read())
{
dar.Close();
SQL = "UPDATE " + TABLENAME + " SET counter=counter+1 WHERE name='" + name + "'";
HELPER.executeNonQuery(iConn, SQL);
}
else
{
AccesscounterInfo info = new AccesscounterInfo();
info.Name = name;
info.Counter = 1;
this.Save(info);
}
}
}
catch (Exception ex)
{
throw ex;
}
}
private int Getlistcount(iSqlConnection iConn, int cid, int pid)
{
try
{
int numResults = 0;
string SQL = SQL_COUNT.Replace(Queryparam.Varstring.VAR_TABLENAME, TABLENAME);
SQL += " AND A.status<>" + (int)CConstants.State.Status.Waitactive;
SQL += " AND A.status<>" + (int)CConstants.State.Status.Disabled;
SQL += cid == 0 ? "" : " AND A.cid=" + cid;
SQL += pid == -1 ? "" : " AND A.pid=" + pid;
using (iSqlDataReader dar = HELPER.executeReader(iConn, SQL))
{
if (dar.Read())
{
numResults = dar.IsDBNull(0) ? 0 : dar.GetInt32(0);
}
}
return numResults;
}
catch
{
return 0;
}
}
/// <summary>
/// ���ٶ���
/// </summary>
public void Dispose()
{
if( myConnection != null )
{
if( myConnection.State == System.Data.ConnectionState.Open )
{
myConnection.Close();
}
myConnection = null ;
}
}
public override void CloseConnection(System.Data.IDbConnection conn)
{
}
public FilesAndMetadata(System.Data.IDbConnection connection, string filetablename, string blocktablename, long blocksize, BlockVolumeReader curvolume)
{
m_filetablename = filetablename;
m_blocktablename = blocktablename;
m_blocksize = blocksize;
m_connection = connection;
using (var c = m_connection.CreateCommand())
{
m_tmptable = "VolumeFiles-" + Library.Utility.Utility.ByteArrayAsHexString(Guid.NewGuid().ToByteArray());
c.CommandText = string.Format(@"CREATE TEMPORARY TABLE ""{0}"" ( ""Hash"" TEXT NOT NULL, ""Size"" INTEGER NOT NULL )", m_tmptable);
c.ExecuteNonQuery();
c.CommandText = string.Format(@"INSERT INTO ""{0}"" (""Hash"", ""Size"") VALUES (?,?)", m_tmptable);
c.AddParameters(2);
foreach (var s in curvolume.Blocks)
{
c.SetParameterValue(0, s.Key);
c.SetParameterValue(1, s.Value);
c.ExecuteNonQuery();
}
// The index _HashSizeIndex is not needed anymore. Index on "Blocks-..." is used on Join in GetMissingBlocks
}
}
/// <summary>
/// Helper method with logic to handle opening a database in possibly encrypted format
/// </summary>
/// <param name="con">The SQLite connection object</param>
internal static void OpenDatabase(System.Data.IDbConnection con)
{
bool noEncryption = !Program.UseDatabaseEncryption;
string password = Environment.GetEnvironmentVariable(DB_KEY_ENV_NAME);
System.Reflection.MethodInfo setPwdMethod = con.GetType().GetMethod("SetPassword", new Type[] { typeof(string) });
string attemptedPassword;
if (noEncryption || string.IsNullOrEmpty(password))
{
attemptedPassword = null; //No encryption specified, attempt to open without
}
else
{
attemptedPassword = password; //Encryption specified, attempt to open with
}
if (setPwdMethod != null)
{
setPwdMethod.Invoke(con, new object[] { attemptedPassword });
}
try
{
//Attempt to open in preferred state
con.Open();
// Do a dummy query to make sure we have a working db
using (var cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT COUNT(*) FROM SQLITE_MASTER";
cmd.ExecuteScalar();
}
}
catch
{
try
{
//We can't try anything else without a password
if (string.IsNullOrEmpty(password))
{
throw;
}
//Open failed, now try the reverse
if (attemptedPassword == null)
{
attemptedPassword = password;
}
else
{
attemptedPassword = null;
}
con.Close();
setPwdMethod.Invoke(con, new object[] { attemptedPassword });
con.Open();
// Do a dummy query to make sure we have a working db
using (var cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT COUNT(*) FROM SQLITE_MASTER";
cmd.ExecuteScalar();
}
}
catch
{
try { con.Close(); }
catch { }
}
//If the db is not open now, it won't open
if (con.State != System.Data.ConnectionState.Open)
{
throw; //Report original error
}
//The open method succeeded with the non-default method, now change the password
System.Reflection.MethodInfo changePwdMethod = con.GetType().GetMethod("ChangePassword", new Type[] { typeof(string) });
changePwdMethod.Invoke(con, new object[] { noEncryption?null: password });
}
}
public ContentImpl()
{
this.conn = Framework.Config.Connection.SQLiteConn();
}
