/// <summary>
///
/// </summary>
/// <param name="DBName"></param>
/// <param name="model"></param>
/// <returns></returns>
public bool Add(string DBName, Model.Sys_Columns model)
{
IDataBase db = DBHelperFactory.CreateDBInstance(DBName);
if (db.DBType == Model.Enum.DBEnum.SqlServer)//SqlServer
{
StringBuilder sb = new StringBuilder();
sb.Append("alter table " + model.TableName + " add column " + model.ColumnName + " " + model.ColumnType + model.Size);
if (model.IsNullable)
{
sb.Append(" null");
}
else
{
sb.Append(" not null ");
}
//TODO:增加默认值
}
else if (db.DBType == Model.Enum.DBEnum.MySql)//MySql
{
}
else if (db.DBType == Model.Enum.DBEnum.Oracle)//Oracle
{
}
else
{
throw new Exception(DBName + "数据库类型未知");
}
return(true);
}
/// <summary>
/// 将表和信息记录到sys表
/// </summary>
/// <param name="model"></param>
public bool AddTableInfo(MyPlatform.Model.Sys_Tables model)
{
List <SqlCommandData> sqlCommands = new List <SqlCommandData>();//事务参数
IDataBase dbDefault = DBHelperFactory.CreateDBInstance(defaultCon);
SqlCommandData sc2 = SqlFactory.CreateInsertSqlByRef <MyPlatform.Model.Sys_Tables>(model);
sqlCommands.Add(sc2);
//dbDefault.ExecuteTran(sqlCommands);
//sqlCommands = new List<SqlCommandData>();
//SqlCommandData scID = new SqlCommandData();
//scID.CommandText = "select SCOPE_IDENTITY()";
//sqlCommands.Add(scID);
//int id = Convert.ToInt32(dbDefault.ExecuteScalar("select IDENT_CURRENT('Sys_Tables')"));
SqlCommandData sc3 = new SqlCommandData();
sc3.CommandText = @"INSERT INTO [dbo].[Sys_Columns]
([CreatedBy] ,[CreatedDate] ,[UpdatedBy] ,[UpdatedDate]
,[Deleted] ,[TableID] ,[TableName] ,[ColumnName] ,[ColumnName_EN]
,[ColumnName_CN] ,[ColumnType] ,[Size] ,[IsNullable] ,[DefaultValue]
,[Remark]) VALUES ('" + model.CreatedBy + "','" + model.CreatedDate + "','" + model.UpdatedBy + "','" + model.UpdatedDate + "','0',(SELECT IDENT_CURRENT('Sys_Tables')),'"
+ model.TableName + "','CreatedBy','CreatedBy','创建人','NVarchar',30,0,'','')";
sqlCommands.Add(sc3);
SqlCommandData sc4 = new SqlCommandData();
sc4.CommandText = @"INSERT INTO [dbo].[Sys_Columns]
([CreatedBy] ,[CreatedDate] ,[UpdatedBy] ,[UpdatedDate]
,[Deleted] ,[TableID] ,[TableName] ,[ColumnName] ,[ColumnName_EN]
,[ColumnName_CN] ,[ColumnType] ,[Size] ,[IsNullable] ,[DefaultValue]
,[Remark]) VALUES ('" + model.CreatedBy + "','" + model.CreatedDate + "','" + model.UpdatedBy + "','" + model.UpdatedDate + "','0',(SELECT IDENT_CURRENT('Sys_Tables')),'"
+ model.TableName + "','CreatedDate','CreatedDate','创建时间','DateTime',0,0,'','')";
sqlCommands.Add(sc4);
SqlCommandData sc5 = new SqlCommandData();
sc5.CommandText = @"INSERT INTO [dbo].[Sys_Columns]
([CreatedBy] ,[CreatedDate] ,[UpdatedBy] ,[UpdatedDate]
,[Deleted] ,[TableID] ,[TableName] ,[ColumnName] ,[ColumnName_EN]
,[ColumnName_CN] ,[ColumnType] ,[Size] ,[IsNullable] ,[DefaultValue]
,[Remark]) VALUES ('" + model.CreatedBy + "','" + model.CreatedDate + "','" + model.UpdatedBy + "','" + model.UpdatedDate + "','0',(SELECT IDENT_CURRENT('Sys_Tables')),'"
+ model.TableName + "','UpdatedBy','UpdatedBy','更新人','NVarchar',30,1,'','')";
sqlCommands.Add(sc5);
SqlCommandData sc6 = new SqlCommandData();
sc6.CommandText = @"INSERT INTO [dbo].[Sys_Columns]
([CreatedBy] ,[CreatedDate] ,[UpdatedBy] ,[UpdatedDate]
,[Deleted] ,[TableID] ,[TableName] ,[ColumnName] ,[ColumnName_EN]
,[ColumnName_CN] ,[ColumnType] ,[Size] ,[IsNullable] ,[DefaultValue]
,[Remark]) VALUES ('" + model.CreatedBy + "','" + model.CreatedDate + "','" + model.UpdatedBy + "','" + model.UpdatedDate + "','0',(SELECT IDENT_CURRENT('Sys_Tables')),'"
+ model.TableName + "','UpdatedDate','UpdatedDate','更新时间','DateTime',0,1,'','')";
sqlCommands.Add(sc6);
SqlCommandData sc7 = new SqlCommandData();
sc7.CommandText = @"INSERT INTO [dbo].[Sys_Columns]
([CreatedBy] ,[CreatedDate] ,[UpdatedBy] ,[UpdatedDate]
,[Deleted] ,[TableID] ,[TableName] ,[ColumnName] ,[ColumnName_EN]
,[ColumnName_CN] ,[ColumnType] ,[Size] ,[IsNullable] ,[DefaultValue]
,[Remark]) VALUES ('" + model.CreatedBy + "','" + model.CreatedDate + "','" + model.UpdatedBy + "','" + model.UpdatedDate + "','0',(SELECT IDENT_CURRENT('Sys_Tables')),'"
+ model.TableName + "','Deleted','Deleted','是否已删除','Bit',0,1,0,'')";
sqlCommands.Add(sc7);
return(dbDefault.ExecuteTran(sqlCommands));
}
public void Init()
{
var connStr = System.Configuration.ConfigurationManager.AppSettings["ConntionString"].ToString();
DBBase dbbase = new DBBase();
DataBaseHelper = DBHelperFactory.GetSqlDataClassHelper(SqlPrividerType.SqlClient, connStr, GetNameSpaceName(dbbase.GetType()));
}
/// <summary>
/// 获取列集合
/// </summary>
/// <param name="tableID">表ID</param>
/// <returns></returns>
public DataSet GetList(int tableID)
{
string sql = "SELECT * FROM dbo.Sys_Columns where tableID=@tableID";
IDataParameter[] pars = { new SqlParameter("tableID", SqlDbType.Int) };
pars[0].Value = tableID;
IDataBase db = DBHelperFactory.CreateDBInstance("Default");
return(db.Query(sql, pars));
}
/// <summary>
/// 是否存在表,0-不存在，1-存在
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="dbName">数据库名</param>
/// <param name="dbTypeCode">数据库类型</param>
/// <returns></returns>
public ReturnData ExistsTable(string tableName, string dbCon)
{
ReturnData result = new ReturnData();
try
{
IDataBase db = DBHelperFactory.CreateDBInstance(dbCon);
Dictionary <string, string> dic = DBInfoCache.GetDBInfo(dbCon);
if (dic == null)
{
result.SetErrorMsg("找不到对应的数据库配置信息");
}
else
{
string sql = "";
IDataParameter[] paras = new IDataParameter[1];
switch (dic["DBTypeCode"].ToLower())
{
case "sqlserver":
sql = " SELECT 1 FROM dbo.SysObjects WHERE ID = object_id(@tableName) AND OBJECTPROPERTY(ID, 'IsTable') = 1 ";
paras = new IDataParameter[1] {
new SqlParameter("@tableName", SqlDbType.VarChar, 30)
};
paras[0].Value = tableName;
break;
case "oracle": //TODO:集成oracle
result.SetErrorMsg("oracle数据库未实现！");
return(result);
break;
case "mysql": //TODO:集成mysql
result.SetErrorMsg("oracle数据库未实现！");
return(result);
break;
default:
break;
}
result.S = Convert.ToInt32(db.ExecuteScalar(sql, paras)) > 0;
if (!result.S)
{
result.M = "数据库已经存在表名为：" + tableName + "的表";
}
result.S = true;
}
}
catch (SqlException ex)
{
result.SetErrorMsg(ex.Message);
}
return(result);
}
public DataSet GetDetail(int apiID)
{
DataSet ds = new DataSet();
string sql = "select * from sys_apis where ID=@ID;select * from api_input where apiid=@ID;select * from api_output where apiid=@ID;";
SqlParameter[] pars = { new SqlParameter("@ID", apiID) };
IDataBase db = DBHelperFactory.CreateDBInstance(defaultCon);
ds = db.Query(sql, pars);
return(ds);
}
/// <summary>
/// 查询API信息
/// </summary>
/// <param name="condition"></param>
/// <returns></returns>
public DataSet GetList(string condition)
{
DataSet ds = new DataSet();
condition = "%" + condition + "%";
string sql = "select * from Sys_APIs WHERE PATINDEX(@condition,Title+ApiName+Description)>0";
IDataBase db = DBHelperFactory.CreateDBInstance(defaultCon);
IDataParameter[] pars = { new SqlParameter("@condition", condition) };
return(db.Query(sql, pars));
}
public DataTable GetMemberUniqueRuns(int memberID)
{
string sql = "select distinct l.Location, p.Product from RunLog rl " +
"join RunLog_Product rlp on rlp.RunLogID = rl.RunLogID " +
"join Product p on rlp.ProductID = p.ProductID " +
"join Location l on l.LocationID = rl.DeliverToLocationID " +
"where rl.RiderMemberID = " + memberID + " " +
"order by Location";
return(DBHelperFactory.DBHelper().ExecuteDataTable(sql));
}
/// <summary>
/// 统计表数据记录数
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="dbCon">数据库连接名</param>
/// <returns></returns>
public int RecordCount(string tableName, string dbCon)
{
try
{
IDataBase db = DBHelperFactory.CreateDBInstance(dbCon);
string sql = "";
sql = "select count(1) from @tableName";
SqlParameter[] pars = { new SqlParameter("@tableName", SqlDbType.VarChar, 100) };
pars[0].Value = tableName;
return(Convert.ToInt32(db.ExecuteScalar(sql, pars)));
}
catch (SqlException ex)
{
throw ex;
}
}
/// <summary>
/// 创建表以及默认字段CreatedBy、CreatedDate、UpdatedDate、CreatedDate、Deleted
/// </summary>
/// <param name="model">表信息</param>
/// <returns></returns>
public ReturnData Add(MyPlatform.Model.Sys_Tables model)
{
ReturnData result = new ReturnData();
try
{
List <SqlCommandData> sqlCommands = new List <SqlCommandData>();//事务参数
StringBuilder sql = new StringBuilder();
sql.Append("Create table {0} (");
sql.Append(" ID int primary key identity(1,1),");
sql.Append(" CreatedBy nvarchar(30) not null,");
sql.Append(" CreatedDate DATETIME DEFAULT(GETDATE()) not null,");
sql.Append(" UpdatedBy nvarchar(30) default(''),");
sql.Append(" UpdatedDate datetime default(getdate()),");
sql.Append(" Deleted bit DEFAULT(0)");
sql.Append(" )");
SqlCommandData sc = new SqlCommandData();
sc.CommandText = string.Format(sql.ToString(), model.TableName);
sqlCommands.Add(sc);
IDataBase dbCreate = DBHelperFactory.CreateDBInstance(model.DBCon);
if (dbCreate.ExecuteTran(sqlCommands))
{
if (AddTableInfo(model))//记录表信息和列信息
{
result.S = true;
}
else
{
result.S = false;
result.SetErrorMsg("保存表和列信息到系统表失败");
}
}
else
{
result.S = false;
result.SetErrorMsg("表格创建失败！");
}
}
catch (Exception ex)
{
result.S = false;
result.SetErrorMsg("保存失败：" + ex.Message);
}
return(result);
}
/// <summary>
/// 获取
/// </summary>
/// <param name="DBName"></param>
/// <returns></returns>
public DataTable GetListByDBName(string DBCon)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from Sys_Tables where 1=1 ");
IDataBase db = DBHelperFactory.CreateDBInstance(defaultCon);
if (!string.IsNullOrEmpty(DBCon))
{
strSql.Append(" and DBCon=@DBCon");
SqlParameter[] parameters = { new SqlParameter("@DBCon", SqlDbType.VarChar, 30) };
parameters[0].Value = DBCon;
return(db.Query(strSql.ToString(), parameters).Tables[0]);
}
else
{
return(db.Query(strSql.ToString()).Tables[0]);
}
}
public List <string> ListMobileNumbersWithTag(string tag)
{
List <string> ret = new List <string>();
string sql = "select distinct m.MobileNumber " +
"from Member m " +
"join Member_Tag mt on mt.MemberID = m.MemberID " +
"join Tag t on t.TagID = mt.TagID " +
"and m.LeaveDate is null " +
"where t.Tag = '" + tag + "'";
DataTable tbl = DBHelperFactory.DBHelper().ExecuteDataTable(sql);
if (tbl != null && tbl.Rows.Count > 0)
{
foreach (DataRow r in tbl.Rows)
{
ret.Add(r[0].ToString());
}
}
return(ret);
}
public List <string> ListMobileNumbersWithAnyTagsIn(string tagsCsv)
{
List <string> ret = new List <string>();
string[] tags = tagsCsv.Split(',');
List <string> tagList = new List <string>();
foreach (string tag in tags)
{
if (tag.Trim() != string.Empty)
{
tagList.Add(tag);
}
}
string inClause = "in (";
foreach (string t in tagList)
{
inClause += "'" + t.Trim() + "',";
}
inClause = inClause.Substring(0, inClause.Length - 1);
inClause += ")";
string sql = "select distinct m.MobileNumber " +
"from Member m " +
"join Member_Tag mt on mt.MemberID = m.MemberID " +
"join Tag t on t.TagID = mt.TagID " +
"and m.LeaveDate is null " +
"where t.Tag " + inClause;
DataTable tbl = DBHelperFactory.DBHelper().ExecuteDataTable(sql);
if (tbl != null && tbl.Rows.Count > 0)
{
foreach (DataRow r in tbl.Rows)
{
ret.Add(r[0].ToString());
}
}
return(ret);
}
/// <summary>
///
/// </summary>
/// <param name="tableID"></param>
/// <param name="page"></param>
/// <returns></returns>
public ReturnData GetDetail(int tableID, Pagination page)
{
ReturnData result = new ReturnData();
try
{
DataSet ds = new DataSet();
int startIndex = DALUtils.CalStartIndex(page.PageSize, page.PageIndex);
int endIndex = DALUtils.CalEndIndex(page.PageSize, page.PageIndex);
string sql = "select * from sys_tables where ID=@ID;select * from (select ROW_NUMBER() OVER(ORDER BY orderNO)RN,* from sys_columns where tableID=@ID)t where t.rn>" + startIndex.ToString() + " and t.rn<" + endIndex.ToString() + ";select count(1)TotalCount from sys_columns where tableID=@ID";
IDataBase db = DBHelperFactory.CreateDBInstance(defaultCon);
SqlParameter[] pars = { new SqlParameter("@ID", tableID) };
ds = db.Query(sql, pars);
result.D = ds;
result.S = true;
}
catch (Exception ex)
{
result.SetErrorMsg(ex.Message);
}
return(result);
}
/*
*
* public DataTable Report_RecentRunLog()
* {
* string sql =
* "SELECT " +
* "date(CallDate) as Date, " +
* "TIME(rpad(replace(CallTime, '.',':'), 5, '0')) as Time, " +
* "CONCAT(m.FirstName, ' ', m.LastName) as Rider, " +
* "Consignment, " +
* "CollectFrom as Origin, " +
* "Destination, " +
* "CONCAT(con.FirstName, ' ', con.LastName) as Controller " +
* "FROM RawRunLog rr " +
* "LEFT JOIN Member m on rr.Rider = (CONCAT(m.LastName, ' ', m.FirstName)) " +
* "LEFT JOIN Member con on rr.Controller = (CONCAT(con.LastName, ' ', con.FirstName)) " +
* "order by CallDate desc, RawRunLogID desc " +
* "LIMIT 100;";
* return DBHelperFactory.DBHelper().ExecuteDataTable(sql);
* }
*
* public DataTable Report_Top10Riders()
* {
* string sql =
* "select Name from " +
* "(select CONCAT(m.FirstName, ' ', m.LastName) Name, count(*) Runs " +
* "from RunLog rl " +
* "LEFT join Member m on m.MemberID = rl.RiderMemberID " +
* "group by Name " +
* "order by Runs desc " +
* "LIMIT 10) top " +
* "order by Name;";
* return DBHelperFactory.DBHelper().ExecuteDataTable(sql);
* }
*
* public DataTable Report_Top102013Riders()
* {
* string sql =
* "select Name from " +
* "(select CONCAT(m.FirstName, ' ', m.LastName) Name, count(*) Runs " +
* "from RawRunLog rr " +
* "LEFT join Member m on rr.Rider = (CONCAT(m.LastName, ' ', m.FirstName)) " +
* "where m.FirstName is not null " +
* "group by Name " +
* "order by Runs desc " +
* "LIMIT 10) sub " +
* "order by Name;";
* return DBHelperFactory.DBHelper().ExecuteDataTable(sql);
* }
*
* public DataTable Report_RunButNoLogin()
* {
* string sql = "select CONCAT(m.FirstName, ' ', m.LastName) as Rider, date(m.JoinDate) as Joined, m.EmailAddress as Email, date(max(rr.CallDate)) as LastRun, count(*) as Runs " +
* "from RawRunLog rr " +
* "LEFT join Member m on rr.Rider = (CONCAT(m.LastName, ' ', m.FirstName)) " +
* "where m.MemberID not in " +
* "(select m.MemberID from User u join Member m on m.MemberID = u.MemberID where u.lastLoginDate is not null) " +
* "and rr.CallDate > '2013-05-01' " +
* "and m.LeaveDate is null " +
* "group by m.MemberID " +
* "order by max(rr.CallDate) desc;";
* return DBHelperFactory.DBHelper().ExecuteDataTable(sql);
* }
*
* public DataTable Report_AverageCallsPerDay()
* {
* string sql = "SELECT dayname(case when Hour(CallTime) > 17 then CallDate else AddDate(CallDate, -1) end) as ShiftDay " +
* ", round(count(*) / 34.28) as AverageCalls " +
* //", ceil((count(*) / @weeks) * @riderfactor) as RidersRequired " +
* "FROM RawRunLog " +
* "WHERE CallDate > AddDate(CURRENT_DATE, -240) " +
* "AND(Consignment like '%blood%' " +
* "or Consignment like '%plate%' " +
* "or Consignment like '%plas%' " +
* "or Consignment like '%ffp%' " +
* "or Consignment like '%sample%' " +
* "or Consignment like '%drugs%' " +
* "or Consignment like '%cd%' " +
* "or Consignment like '%data%' " +
* "or Consignment like '%disk%' " +
* "or Consignment like '%disc%' " +
* "or Consignment like '%package%') " +
* "GROUP BY dayname(case when Hour(CallTime) > 17 then CallDate else AddDate(CallDate, -1) end) " +
* "ORDER BY dayofweek(case when Hour(CallTime) > 17 then CallDate else AddDate(CallDate, -1) end);";
* return DBHelperFactory.DBHelper().ExecuteDataTable(sql);
* }
*
* public DataTable Report_CallsPerHourHeatMap()
* {
* string sql = "select dayname(case when Hour(CallTime) > 17 then CallDate else AddDate(CallDate, -1) end) as Day, " +
* "Hour(CallTime) as Hour, count(*) as Calls " +
* "from RawRunLog " +
* "WHERE CallDate > AddDate(CURRENT_DATE, -240) " +
* "AND Hour(CallTime) >= 0 AND Hour(CallTime) <= 23 " +
* "AND(Consignment like '%blood%' " +
* "or Consignment like '%plate%' " +
* "or Consignment like '%plas%' " +
* "or Consignment like '%ffp%' " +
* "or Consignment like '%sample%' " +
* "or Consignment like '%drugs%' " +
* "or Consignment like '%cd%' " +
* "or Consignment like '%data%' " +
* "or Consignment like '%disk%' " +
* "or Consignment like '%disc%' " +
* "or Consignment like '%package%') " +
* "group by dayname(case when Hour(CallTime) > 17 then CallDate else AddDate(CallDate, -1) end), Hour(CallTime) " +
* "ORDER BY dayofweek(case when Hour(CallTime) > 17 then CallDate else AddDate(CallDate, -1) end), Hour(CallTime)";
* return DBHelperFactory.DBHelper().ExecuteDataTable(sql);
* }
*
* public DataTable Report_TodaysUsers()
* {
* string sql = "select CONCAT(m.FirstName, ' ', m.LastName) as Member " +
* "from User u join Member m on m.MemberID = u.MemberID where u.lastLoginDate > CURRENT_DATE() " +
* "order by lastLoginDate desc;";
* return DBHelperFactory.DBHelper().ExecuteDataTable(sql);
* }
*
*
*
* public DataTable Report_BoxesByProductByMonth()
* {
* string sql = "select concat(MONTHNAME(rl.DutyDate), ' ', year(rl.DutyDate)) as Month " +
* ", p.Product, sum(rlp.Quantity) as BoxesCarried from RunLog rl " +
* "join RunLog_Product rlp on rlp.RunLogID = rl.RunLogID " +
* "join Product p on p.ProductID = rlp.ProductID " +
* "group by Month, Product " +
* "order by month(rl.DutyDate), Product;";
* return DBHelperFactory.DBHelper().ExecuteDataTable(sql);
* }
*
*/
public DataTable Report_RunLog(string year)
{
string sql = "select RunLogID as ID, date_format(DutyDate, '%Y-%m-%d') as 'DutyDate', " +
"coalesce(date_format(CallDateTime, '%Y-%m-%d %H:%i'), 'N/A') as 'CallDateTime', cf.Location as 'CallFrom', " +
"CASE WHEN rl.RunLogType='M' THEN Concat(cl.Location,' ', rl.CollectionPostcode) ELSE cl.Location END 'From', " +
"CASE WHEN rl.RunLogType = 'M' THEN Concat(dl.Location,' ', rl.DeliverToPostcode) ELSE dl.Location END as 'To', coalesce(date_format(rl.CollectDateTime, '%H:%i'), 'NOT ACCEPTED') as Collected, " +
"date_format(rl.DeliverDateTime, '%H:%i') as Delivered, " +
"CASE WHEN rl.RunLogType = 'M' THEN Concat(fl.Location,' ', rl.DeliverToPostcode) ELSE fl.Location END as 'Destination', concat(m.LastName, ' ', m.FirstName) as Rider, " +
"v.VehicleType as 'Vehicle', rl.Description as 'Consignment', " +
"concat(c.LastName, ' ', c.FirstName) as Controller from RunLog rl " +
"left join Member m on m.MemberID = rl.RiderMemberID " +
"join Member c on c.MemberID = rl.ControllerMemberID " +
"join Location cf on cf.LocationID = rl.CallFromLocationID " +
"join Location cl on cl.LocationID = rl.CollectionLocationID " +
"join Location dl on dl.LocationID = rl.DeliverToLocationID " +
"join Location fl on fl.LocationID = rl.FinalDestinationLocationID " +
"left join VehicleType v on v.VehicleTypeID = rl.VehicleTypeID " +
"where YEAR(DutyDate) = " + year +
" order by rl.DutyDate desc, rl.CallDateTime desc;";
DataTable ret = DBHelperFactory.DBHelper().ExecuteDataTable(sql);
return(ret);
}
/*
*
* public DataTable Report_RecentRunLog()
* {
* string sql =
* "SELECT " +
* "date(CallDate) as Date, " +
* "TIME(rpad(replace(CallTime, '.',':'), 5, '0')) as Time, " +
* "CONCAT(m.FirstName, ' ', m.LastName) as Rider, " +
* "Consignment, " +
* "CollectFrom as Origin, " +
* "Destination, " +
* "CONCAT(con.FirstName, ' ', con.LastName) as Controller " +
* "FROM RawRunLog rr " +
* "LEFT JOIN Member m on rr.Rider = (CONCAT(m.LastName, ' ', m.FirstName)) " +
* "LEFT JOIN Member con on rr.Controller = (CONCAT(con.LastName, ' ', con.FirstName)) " +
* "order by CallDate desc, RawRunLogID desc " +
* "LIMIT 100;";
* return DBHelperFactory.DBHelper().ExecuteDataTable(sql);
* }
*
* public DataTable Report_Top10Riders()
* {
* string sql =
* "select Name from " +
* "(select CONCAT(m.FirstName, ' ', m.LastName) Name, count(*) Runs " +
* "from RunLog rl " +
* "LEFT join Member m on m.MemberID = rl.RiderMemberID " +
* "group by Name " +
* "order by Runs desc " +
* "LIMIT 10) top " +
* "order by Name;";
* return DBHelperFactory.DBHelper().ExecuteDataTable(sql);
* }
*
* public DataTable Report_Top102013Riders()
* {
* string sql =
* "select Name from " +
* "(select CONCAT(m.FirstName, ' ', m.LastName) Name, count(*) Runs " +
* "from RawRunLog rr " +
* "LEFT join Member m on rr.Rider = (CONCAT(m.LastName, ' ', m.FirstName)) " +
* "where m.FirstName is not null " +
* "group by Name " +
* "order by Runs desc " +
* "LIMIT 10) sub " +
* "order by Name;";
* return DBHelperFactory.DBHelper().ExecuteDataTable(sql);
* }
*
* public DataTable Report_RunButNoLogin()
* {
* string sql = "select CONCAT(m.FirstName, ' ', m.LastName) as Rider, date(m.JoinDate) as Joined, m.EmailAddress as Email, date(max(rr.CallDate)) as LastRun, count(*) as Runs " +
* "from RawRunLog rr " +
* "LEFT join Member m on rr.Rider = (CONCAT(m.LastName, ' ', m.FirstName)) " +
* "where m.MemberID not in " +
* "(select m.MemberID from User u join Member m on m.MemberID = u.MemberID where u.lastLoginDate is not null) " +
* "and rr.CallDate > '2013-05-01' " +
* "and m.LeaveDate is null " +
* "group by m.MemberID " +
* "order by max(rr.CallDate) desc;";
* return DBHelperFactory.DBHelper().ExecuteDataTable(sql);
* }
*
* public DataTable Report_AverageCallsPerDay()
* {
* string sql = "SELECT dayname(case when Hour(CallTime) > 17 then CallDate else AddDate(CallDate, -1) end) as ShiftDay " +
* ", round(count(*) / 34.28) as AverageCalls " +
* //", ceil((count(*) / @weeks) * @riderfactor) as RidersRequired " +
* "FROM RawRunLog " +
* "WHERE CallDate > AddDate(CURRENT_DATE, -240) " +
* "AND(Consignment like '%blood%' " +
* "or Consignment like '%plate%' " +
* "or Consignment like '%plas%' " +
* "or Consignment like '%ffp%' " +
* "or Consignment like '%sample%' " +
* "or Consignment like '%drugs%' " +
* "or Consignment like '%cd%' " +
* "or Consignment like '%data%' " +
* "or Consignment like '%disk%' " +
* "or Consignment like '%disc%' " +
* "or Consignment like '%package%') " +
* "GROUP BY dayname(case when Hour(CallTime) > 17 then CallDate else AddDate(CallDate, -1) end) " +
* "ORDER BY dayofweek(case when Hour(CallTime) > 17 then CallDate else AddDate(CallDate, -1) end);";
* return DBHelperFactory.DBHelper().ExecuteDataTable(sql);
* }
*
* public DataTable Report_CallsPerHourHeatMap()
* {
* string sql = "select dayname(case when Hour(CallTime) > 17 then CallDate else AddDate(CallDate, -1) end) as Day, " +
* "Hour(CallTime) as Hour, count(*) as Calls " +
* "from RawRunLog " +
* "WHERE CallDate > AddDate(CURRENT_DATE, -240) " +
* "AND Hour(CallTime) >= 0 AND Hour(CallTime) <= 23 " +
* "AND(Consignment like '%blood%' " +
* "or Consignment like '%plate%' " +
* "or Consignment like '%plas%' " +
* "or Consignment like '%ffp%' " +
* "or Consignment like '%sample%' " +
* "or Consignment like '%drugs%' " +
* "or Consignment like '%cd%' " +
* "or Consignment like '%data%' " +
* "or Consignment like '%disk%' " +
* "or Consignment like '%disc%' " +
* "or Consignment like '%package%') " +
* "group by dayname(case when Hour(CallTime) > 17 then CallDate else AddDate(CallDate, -1) end), Hour(CallTime) " +
* "ORDER BY dayofweek(case when Hour(CallTime) > 17 then CallDate else AddDate(CallDate, -1) end), Hour(CallTime)";
* return DBHelperFactory.DBHelper().ExecuteDataTable(sql);
* }
*
* public DataTable Report_TodaysUsers()
* {
* string sql = "select CONCAT(m.FirstName, ' ', m.LastName) as Member " +
* "from User u join Member m on m.MemberID = u.MemberID where u.lastLoginDate > CURRENT_DATE() " +
* "order by lastLoginDate desc;";
* return DBHelperFactory.DBHelper().ExecuteDataTable(sql);
* }
*
*
*
* public DataTable Report_BoxesByProductByMonth()
* {
* string sql = "select concat(MONTHNAME(rl.DutyDate), ' ', year(rl.DutyDate)) as Month " +
* ", p.Product, sum(rlp.Quantity) as BoxesCarried from RunLog rl " +
* "join RunLog_Product rlp on rlp.RunLogID = rl.RunLogID " +
* "join Product p on p.ProductID = rlp.ProductID " +
* "group by Month, Product " +
* "order by month(rl.DutyDate), Product;";
* return DBHelperFactory.DBHelper().ExecuteDataTable(sql);
* }
*
*/
public DataTable Report_RunLog()
{
string sql = "select RunLogID as ID, date_format(DutyDate, '%Y-%m-%d') as 'DutyDate', " +
"coalesce(date_format(CallDateTime, '%Y-%m-%d %H:%i'), 'N/A') as 'CallDateTime', cf.Location as 'CallFrom', cl.Location as 'From', " +
"dl.Location as 'To', coalesce(date_format(rl.CollectDateTime, '%H:%i'), 'NOT ACCEPTED') as Collected, " +
"date_format(rl.DeliverDateTime, '%H:%i') as Delivered, " +
//"timediff(rl.DeliverDateTime, rl.CollectDateTime) as 'Run Time', " +
"fl.Location as 'Destination', concat(m.FirstName, ' ', m.LastName) as Rider, " +
"v.VehicleType as 'Vehicle', rl.Description as 'Consignment', " +
"concat(c.FirstName, ' ', c.LastName) as Controller from RunLog rl " +
"left join Member m on m.MemberID = rl.RiderMemberID " +
"join Member c on c.MemberID = rl.ControllerMemberID " +
"join Location cf on cf.LocationID = rl.CallFromLocationID " +
"join Location cl on cl.LocationID = rl.CollectionLocationID " +
"join Location dl on dl.LocationID = rl.DeliverToLocationID " +
"join Location fl on fl.LocationID = rl.FinalDestinationLocationID " +
"left join VehicleType v on v.VehicleTypeID = rl.VehicleTypeID " +
"where DutyDate > '2013-12-31' or CallDateTime > '2013-12-31' " +
"order by rl.DutyDate desc, rl.CallDateTime desc;";
DataTable ret = DBHelperFactory.DBHelper().ExecuteDataTable(sql);
return(ret);
}
/// <summary>
/// 新增API
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public ReturnData Add(Dictionary <object, object> dic)
{
ReturnData result = new ReturnData();
try
{
IDataBase db = DBHelperFactory.CreateDBInstance(defaultCon);
//保存API信息
string sqlApi = @"insert into sys_apis(Title ,
ApiName ,
Description ,
CreatedBy ,
CreatedDate
) values(@title,@apiName,@description,'',getdate())";
List <SqlCommandData> liSql = new List <SqlCommandData>();
SqlCommandData cmdApi = new SqlCommandData();
cmdApi.CommandText = sqlApi;
cmdApi.Paras = new SqlParameter[] {
new SqlParameter("@title", dic["title"]),
new SqlParameter("@apiName", dic["apiName"]),
new SqlParameter("@description", dic["description"])
};
liSql.Add(cmdApi);
//保存输入参数信息
string sqlInput = "";
string[] arrInput = (string[])dic["inputParam"];
SqlParameter[] parInput = new SqlParameter[4];
for (int i = 0; i < arrInput.Length; i++)
{
if (i % 4 == 0)
{
sqlInput = @"insert into api_input( ApiID ,
ParamName ,
ParamType ,
IsRequired ,
Description ,
OrderNo ,
CreatedBy ,
CreatedDate) values((SELECT IDENT_CURRENT('Sys_Apis')),@input" + i.ToString();
SqlParameter param = new SqlParameter("@input" + i.ToString(), arrInput[i]);
parInput[i % 4] = param;
}
else if (i % 4 == 3)
{
sqlInput += ",@input" + i.ToString() + "," + (i * 40).ToString() + ",'',getdate())";
SqlParameter param = new SqlParameter("@input" + i.ToString(), arrInput[i]);
parInput[i % 4] = param;
SqlCommandData cmdInput = new SqlCommandData();
cmdInput.CommandText = sqlInput;
cmdInput.Paras = parInput;
liSql.Add(cmdInput);
//重新声明新的输入参数信息
parInput = new SqlParameter[4];
sqlInput = "";
}
else
{
sqlInput += ",@input" + i.ToString();
SqlParameter param = new SqlParameter("@input" + i.ToString(), arrInput[i]);
parInput[i % 4] = param;
}
}
//保存输出参数信息
string sqlOutput = "";
string[] arrOutput = (string[])dic["outputParam"];
int outputColumns = Convert.ToInt32(dic["outputColumns"]);
SqlParameter[] parOutput = new SqlParameter[outputColumns];
if (outputColumns == 4)
{
for (int i = 0; i < arrOutput.Length; i++)
{
if (i % 4 == 0)
{
sqlOutput = @"insert into api_output( ApiID ,
ParamName ,
ParamType ,
IsRequired ,
Description ,
OrderNo ,
CreatedBy ,
CreatedDate) values((SELECT IDENT_CURRENT('Sys_Apis')),@output" + i.ToString();
SqlParameter param = new SqlParameter("@output" + i.ToString(), arrOutput[i]);
parOutput[i % 4] = param;
}
else if (i % 4 == 3)
{
sqlOutput += ",@output" + i.ToString() + "," + (i * 40).ToString() + ",'',getdate())";
SqlParameter param = new SqlParameter("@output" + i.ToString(), arrOutput[i]);
parOutput[i % 4] = param;
SqlCommandData cmdOutput = new SqlCommandData();
cmdOutput.CommandText = sqlOutput;
cmdOutput.Paras = parOutput;
liSql.Add(cmdOutput);
//重新声明新的输入参数信息
parOutput = new SqlParameter[4];
sqlOutput = "";
}
else
{
sqlOutput += ",@output" + i.ToString();
SqlParameter param = new SqlParameter("@output" + i.ToString(), arrOutput[i]);
parOutput[i % 4] = param;
}
}
}
else if (outputColumns == 3)
{
for (int i = 0; i < arrOutput.Length; i++)
{
if (i % 3 == 0)
{
sqlOutput = @"insert into api_output( ApiID ,
ParamName ,
ParamType ,
IsRequired ,
Description ,
OrderNo ,
CreatedBy ,
CreatedDate) values((SELECT IDENT_CURRENT('Sys_Apis')),@output" + i.ToString();
SqlParameter param = new SqlParameter("@output" + i.ToString(), arrOutput[i]);
parOutput[i % 3] = param;
}
else if (i % 3 == 2)
{
sqlOutput += ",'',@output" + i.ToString() + "," + (i * 40).ToString() + ",'',getdate())";
SqlParameter param = new SqlParameter("@output" + i.ToString(), arrOutput[i]);
parOutput[i % 3] = param;
SqlCommandData cmdOutput = new SqlCommandData();
cmdOutput.CommandText = sqlOutput;
cmdOutput.Paras = parOutput;
liSql.Add(cmdOutput);
//重新声明新的输入参数信息
parOutput = new SqlParameter[3];
sqlOutput = "";
}
else
{
sqlOutput += ",@output" + i.ToString();
SqlParameter param = new SqlParameter("@output" + i.ToString(), arrOutput[i]);
parOutput[i % 3] = param;
}
}
}
if (db.ExecuteTran(liSql))
{
result.S = true;
}
else
{
result.SetErrorMsg("添加失败！");
}
}
catch (Exception ex)
{
result.SetErrorMsg(ex.Message);
}
return(result);
}
public void GetDataBase(string con)
{
currentDB = DBHelperFactory.CreateDBInstance(con);
}
public SqlHelper(string connectString, DataBaseClass dataBaseClass)
{
_dataBaseClass = dataBaseClass;
_iDBHelper = DBHelperFactory.CreateDBHelper(dataBaseClass, connectString);
}
/// <summary>
/// Initializes and instance of a class implementing the IDBHelper interface.
/// </summary>
private static void InitializeDBHelper()
{
var connectionString = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;
IDBHelper _DB = DBHelperFactory.GetInstance(DataBaseType.SQLServer, connectionString);
}
public DataTable RunReport(SERVDataContract.Report report)
{
return(DBHelperFactory.DBHelper().ExecuteDataTable(report.Query));
}
/// <summary>
/// 刪除表以及相关信息
/// </summary>
/// <param name="tableID"></param>
/// <param name="dbName"></param>
/// <returns></returns>
public bool Delete(int tableID)
{
IDataBase db = new SqlServerDataBase("Default");
//无数据的表可以删除
string sql = "select ID,DBCon,TableName,DBTypeCode from Sys_tables where id=@tableID";
SqlParameter[] pars = { new SqlParameter("@tableID", tableID) };
DataTable dt = db.Query(sql, pars).Tables[0];
if (dt.Rows.Count > 0)
{
IDataBase db2 = DBHelperFactory.CreateDBInstance(dt.Rows[0]["DBCon"].ToString());
string sql2 = "select count(1) from " + dt.Rows[0]["TableName"].ToString();
IDataParameter[] pars2 = new IDataParameter[1];
//TODO:增加多类型数据库操作
switch (dt.Rows[0]["DBTypeCode"].ToString().ToLower())
{
case "sqlserver":
pars2 = new IDataParameter[1] {
new SqlParameter("@tableName", dt.Rows[0]["TableName"])
};
break;
default:
break;
}
if (Convert.ToInt32(db2.ExecuteScalar(sql2)) == 0)
{
string sqlDelete = "";
switch (dt.Rows[0]["DBTypeCode"].ToString().ToLower())
{
case "sqlserver":
sqlDelete = "drop table " + dt.Rows[0]["TableName"].ToString();
break;
default:
break;
}
db2.ExecuteNonQuery(sqlDelete);
IDataParameter[] pars4 = { new SqlParameter("@tableID", tableID) };
List <SqlCommandData> li = new List <SqlCommandData>();
SqlCommandData scd = new SqlCommandData();
scd.CommandText = "update sys_tables set deleted=1 where id=@tableID";
scd.Paras = new SqlParameter[1] {
new SqlParameter("@tableID", tableID)
};
li.Add(scd);
SqlCommandData scd2 = new SqlCommandData();
scd2.CommandText = "update sys_columns set deleted=1 where tableid=@tableID";
scd2.Paras = new SqlParameter[1] {
new SqlParameter("@tableID", tableID)
};
li.Add(scd2);
return(db.ExecuteTran(li));
}
else
{
return(false);
}
}
return(true);
}
private int GetTagId(string tagName)
{
return(Convert.ToInt32(DBHelperFactory.DBHelper().ExecuteScalar("select TagID from Tag where Tag = " + SERV.Utils.String.DBSafeString(tagName))));
}
public DataTable ExecuteSQL(string sql)
{
return(DBHelperFactory.DBHelper().ExecuteDataTable(sql));
}
