/// <summary>
///
/// </summary>
/// <param name="conn"></param>
/// <param name="paras"></param>
/// <param name="strProcedureName"></param>
/// <returns></returns>
public IList <Log> GetLogListByProc(OdbcConnection conn, OdbcParameter[] paras, string strProcedureName)
{
IList <Log> lLog = new List <Log>();
DataSet ds = new DataSet();
OdbcCommand cmd = new OdbcCommand();
cmd.Parameters.AddRange(paras);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = strProcedureName;
cmd.Connection = conn;
cmd.CommandTimeout = 150;
try
{
OdbcDataAdapter da = new OdbcDataAdapter(cmd);
da.Fill(ds);
if (ds.Tables.Count > 0)
{
lLog = DBTool.GetListFromDatatable <Log>(ds.Tables[0]);
}
}
catch (Exception e)
{
throw e;
}
return(lLog);
}
public IList <WareHouseInfo> GetWareHouse(IEnumerable <SearchCondition> conditon, SqlConnection conn)
{
IList <WareHouseInfo> l = new List <WareHouseInfo>();
string sql = @"SELECT [id]
,[Name]
,[No]
,[Address]
,[Tel]
,[detail]
,[InsertDateTime]
,[InsertUser]
,[UpdateDateTime]
,[UpdateUser]
FROM [WareHouse]";
if (conditon.Count() > 0)
{
string con = DBTool.GetSqlcon(conditon);
sql += " where " + con;
}
SqlParameter[] spvalues = DBTool.GetSqlParam(conditon);
DataTable dt = SqlHelper.Squery(sql, conn, spvalues);
l = DBTool.GetListFromDatatable <WareHouseInfo>(dt);
return(l);
}
public IList <BackupServerFile> GetBackupServerFilePage(IEnumerable <SearchCondition> conditon, int page, int pagesize, OdbcConnection conn)
{
IList <BackupServerFile> lBackupServerFile = new List <BackupServerFile>();
string sql = @"SELECT id
,backupServerGroupID
,backupServerID
,backupServerFileName
,backupServerFilePath
,backupServerFileType
,backupServerFileSize
,backupStartTime
,backupEndTime
,backupTime
,backupFlg
,copyStartTime
,copyEndTime
,copyTime
,copyFlg
,ROW_NUMBER() over(order by createDate) as row
FROM backupServerFile ";
if (conditon.Count() > 0)
{
string con = DBTool.GetSqlcon(conditon);
sql += " where " + con;
}
sql = "select * from (" + sql + ") as a where row>" + (page - 1) * pagesize + " and row<=" + page * pagesize;
OdbcParameter[] spvalues = DBTool.GetOdbcParam(conditon);
DataTable dt = OdbcHelper.Squery(sql, conn, spvalues);
lBackupServerFile = DBTool.GetListFromDatatable <BackupServerFile>(dt);
return(lBackupServerFile);
}
/// <summary>
/// 根据条件获取采购单
/// </summary>
/// <param name="conditon"></param>
/// <returns></returns>
public IList <SailBillInfo> GetSailBill(IEnumerable <SearchCondition> conditon, SqlConnection conn)
{
IList <SailBillInfo> l = new List <SailBillInfo>();
string sql = @"SELECT [id]
,[WareHouseID]
,[SailNO]
,[SailDate]
,[IsReview]
,[ReviewUser]
,[StuffID]
,[Detail]
,[Define1]
,[Define2]
,[Define3]
,[InsertDateTime]
,[InsertUser]
,[UpdateDateTime]
,[UpdateUser]
FROM [SailBillHead]";
if (conditon.Count() > 0)
{
string con = DBTool.GetSqlcon(conditon);
sql += " where " + con;
}
SqlParameter[] spvalues = DBTool.GetSqlParam(conditon);
DataTable dt = SqlHelper.Squery(sql, conn, spvalues);
l = DBTool.GetListFromDatatable <SailBillInfo>(dt);
foreach (SailBillInfo csi in l)
{
csi.SaleBillDetail = GetDetail(csi.id, conn);
}
return(l);
}
public IList <WareHouseInfo> GetWareHouse(IEnumerable <SearchCondition> conditon, int page, int pagesize, SqlConnection conn)
{
IList <WareHouseInfo> l = new List <WareHouseInfo>();
string sql = @"SELECT [id]
,[Name]
,[No]
,[Address]
,[Tel]
,[detail]
,[InsertDateTime]
,[InsertUser]
,[UpdateDateTime]
,[UpdateUser]
,ROW_NUMBER() over(order by InsertDateTime) as row
FROM [Product] ";
if (conditon.Count() > 0)
{
string con = DBTool.GetSqlcon(conditon);
sql += " where " + con;
}
sql = "select * from (" + sql + ") as a where row>" + (page - 1) * pagesize + " and row<=" + page * pagesize;
SqlParameter[] spvalues = DBTool.GetSqlParam(conditon);
DataTable dt = SqlHelper.Squery(sql, conn, spvalues);
l = DBTool.GetListFromDatatable <WareHouseInfo>(dt);
return(l);
}
/// <summary>
/// 根据条件获取入库单
/// </summary>
/// <param name="conditon"></param>
/// <returns></returns>
public IList <StockInInfo> GetStockIn(IEnumerable <SearchCondition> conditon, SqlConnection conn)
{
IList <StockInInfo> l = new List <StockInInfo>();
string sql = @"SELECT [id]
,[StockInNO]
,[WarehouseID]
,[StockInTP]
,[StockInDate]
,[StockInReason]
,[SupplierID]
,[InsertDateTime]
,[InsertUser]
,[UpdateDateTime]
,[UpdateUser]
FROM [StockInHead]";
if (conditon.Count() > 0)
{
string con = DBTool.GetSqlcon(conditon);
sql += " where " + con;
}
SqlParameter[] spvalues = DBTool.GetSqlParam(conditon);
DataTable dt = SqlHelper.Squery(sql, conn, spvalues);
l = DBTool.GetListFromDatatable <StockInInfo>(dt);
foreach (StockInInfo csi in l)
{
csi.stockInDetail = GetDetail(csi.id, conn);
}
return(l);
}
/// <summary>
/// 获取指定页的入库单
/// </summary>
/// <param name="conditon"></param>
/// <param name="page">页数</param>
/// <param name="pagesize">每页数量</param>
/// <param name="conn"></param>
/// <returns></returns>
public IList <StockInInfo> GetPageStockIn(IEnumerable <SearchCondition> conditon, int page, int pagesize, SqlConnection conn)
{
IList <StockInInfo> l = new List <StockInInfo>();
string sql = @"SELECT [id]
,[StockInNO]
,[WarehouseID]
,[StockInTP]
,[StockInDate]
,[StockInReason]
,[SupplierID]
,[InsertDateTime]
,[InsertUser]
,[UpdateDateTime]
,[UpdateUser]
,ROW_NUMBER() over(order by InsertDateTime) as row
FROM [StockInHead] ";
if (conditon.Count() > 0)
{
string con = DBTool.GetSqlcon(conditon);
sql += " where " + con;
}
sql = "select * from (" + sql + ") as a where row>" + (page - 1) * pagesize + " and row<=" + page * pagesize;
SqlParameter[] spvalues = DBTool.GetSqlParam(conditon);
DataTable dt = SqlHelper.Squery(sql, conn, spvalues);
l = DBTool.GetListFromDatatable <StockInInfo>(dt);
foreach (StockInInfo csi in l)
{
csi.stockInDetail = GetDetail(csi.id, conn);
}
return(l);
}
public IList <BackupServerFile> GetBackupServerFile(IEnumerable <SearchCondition> conditon, OdbcConnection conn)
{
IList <BackupServerFile> lBackupServerFile = new List <BackupServerFile>();
string sql = @"SELECT id
,backupServerGroupID
,backupServerID
,backupServerFileName
,backupServerFilePath
,backupServerFileType
,backupServerFileSize
,backupStartTime
,backupEndTime
,backupTime
,backupFlg
,copyStartTime
,copyEndTime
,copyTime
,copyFlg
FROM backupServerFile";
if (conditon.Count() > 0)
{
string con = DBTool.GetSqlcon(conditon);
sql += " where " + con;
}
OdbcParameter[] spvalues = DBTool.GetOdbcParam(conditon);
DataTable dt = OdbcHelper.Squery(sql, conn, spvalues);
lBackupServerFile = DBTool.GetListFromDatatable <BackupServerFile>(dt);
return(lBackupServerFile);
}
public IList <MonitorServerFile> GetMonitorServerFilePage(IEnumerable <SearchCondition> conditon, int page, int pagesize, SqlConnection conn)
{
IList <MonitorServerFile> lMonitorServerFile = new List <MonitorServerFile>();
string sql = @"SELECT id
,monitorServerID
,monitorFileName
,monitorFilePath
,monitorFileType
,monitorFileSize
,monitorTime
,transferFlg
ROW_NUMBER() over(order by createDate) as row
FROM monitorServerFile ";
if (conditon.Count() > 0)
{
string con = DBTool.GetSqlcon(conditon);
sql += " where " + con;
}
sql = "select * from (" + sql + ") as a where row>" + (page - 1) * pagesize + " and row<=" + page * pagesize;
SqlParameter[] spvalues = DBTool.GetSqlParam(conditon);
DataTable dt = SqlHelper.Squery(sql, conn, spvalues);
lMonitorServerFile = DBTool.GetListFromDatatable <MonitorServerFile>(dt);
return(lMonitorServerFile);
}
public IList <UserInfo> GetUser(IEnumerable <SearchCondition> conditon, SqlConnection conn)
{
IList <UserInfo> l = new List <UserInfo>();
string sql = @"SELECT [id]
,[UserID]
,[Password]
,[UserName]
,[Email]
,[InsertDateTime]
,[InsertUser]
,[UpdateDateTime]
,[UpdateUser]
FROM [User]";
if (conditon.Count() > 0)
{
string con = DBTool.GetSqlcon(conditon);
sql += " where " + con;
}
SqlParameter[] spvalues = DBTool.GetSqlParam(conditon);
DataTable dt = SqlHelper.Squery(sql, conn, spvalues);
l = DBTool.GetListFromDatatable <UserInfo>(dt);
return(l);
}
public IList <StockInfo> GetStock(IEnumerable <SearchCondition> conditon, SqlConnection conn)
{
IList <StockInfo> l = new List <StockInfo>();
string sql = @"SELECT [id]
,[WarehouseID]
,[ProductID]
,[StockInTP]
,[StockInDate]
,[StockInReason]
,[StockOutTP]
,[StockOutDate]
,[StockOutReason]
,[Num]
,[InsertDateTime]
,[InsertUser]
,[UpdateDateTime]
,[UpdateUser]
FROM [Stock]";
if (conditon.Count() > 0)
{
string con = DBTool.GetSqlcon(conditon);
sql += " where " + con;
}
SqlParameter[] spvalues = DBTool.GetSqlParam(conditon);
DataTable dt = SqlHelper.Squery(sql, conn, spvalues);
l = DBTool.GetListFromDatatable <StockInfo>(dt);
return(l);
}
public IList <ProductInfo> GetProduct(IEnumerable <SearchCondition> conditon, SqlConnection conn)
{
IList <ProductInfo> l = new List <ProductInfo>();
string sql = @"SELECT [id]
,[ProductNO]
,[ProductName]
,[CategoryID]
,[ProductSKU]
,[ProductDetail]
,[Price]
,[SailPrice]
,[Place]
,[SaveTime]
,[Define1]
,[Define2]
,[Define3]
,[InsertDateTime]
,[InsertUser]
,[UpdateDateTime]
,[UpdateUser]
FROM [Product]";
if (conditon.Count() > 0)
{
string con = DBTool.GetSqlcon(conditon);
sql += " where " + con;
}
SqlParameter[] spvalues = DBTool.GetSqlParam(conditon);
DataTable dt = SqlHelper.Squery(sql, conn, spvalues);
l = DBTool.GetListFromDatatable <ProductInfo>(dt);
return(l);
}
public IList <ProductInfo> GetPageProduct(IEnumerable <SearchCondition> conditon, int page, int pagesize, SqlConnection conn)
{
IList <ProductInfo> l = new List <ProductInfo>();
string sql = @"SELECT [id]
,[ProductNO]
,[ProductName]
,[CategoryID]
,[ProductSKU]
,[ProductDetail]
,[Price]
,[SailPrice]
,[Place]
,[SaveTime]
,[Define1]
,[Define2]
,[Define3]
,[InsertDateTime]
,[InsertUser]
,[UpdateDateTime]
,[UpdateUser]
,ROW_NUMBER() over(order by InsertDateTime) as row
FROM [Product] ";
if (conditon.Count() > 0)
{
string con = DBTool.GetSqlcon(conditon);
sql += " where " + con;
}
sql = "select * from (" + sql + ") as a where row>" + (page - 1) * pagesize + " and row<=" + page * pagesize;
SqlParameter[] spvalues = DBTool.GetSqlParam(conditon);
DataTable dt = SqlHelper.Squery(sql, conn, spvalues);
l = DBTool.GetListFromDatatable <ProductInfo>(dt);
return(l);
}
public IList <CategoryInfo> GetPageCategory(IEnumerable <SearchCondition> conditon, int page, int pagesize, SqlConnection conn)
{
IList <CategoryInfo> lcategory = new List <CategoryInfo>();
string sql = @"SELECT [id]
,[categoryName]
,[parentID]
,[InsertDateTime]
,[InsertUser]
,[UpdateDateTime]
,[UpdateUser]
,ROW_NUMBER() over(order by InsertDateTime) as row
FROM [Category] ";
if (conditon.Count() > 0)
{
string con = DBTool.GetSqlcon(conditon);
sql += " where " + con;
}
sql = "select * from (" + sql + ") as a where row>" + (page - 1) * pagesize + " and row<=" + page * pagesize;
SqlParameter[] spvalues = DBTool.GetSqlParam(conditon);
DataTable dt = SqlHelper.Squery(sql, conn, spvalues);
lcategory = DBTool.GetListFromDatatable <CategoryInfo>(dt);
return(lcategory);
}
public IList <MonitorServerFile> GetMonitorServerFile(IEnumerable <SearchCondition> conditon, SqlConnection conn)
{
IList <MonitorServerFile> lMonitorServerFile = new List <MonitorServerFile>();
string sql = @"SELECT id
,monitorServerID
,monitorFileName
,monitorFilePath
,monitorFileType
,monitorFileSize
,monitorFileStatus
,monitorTime
,transferFlg
FROM monitorServerFile";
if (conditon.Count() > 0)
{
string con = DBTool.GetSqlcon(conditon);
sql += " where " + con;
}
sql += " order by id asc";
SqlParameter[] spvalues = DBTool.GetSqlParam(conditon);
DataTable dt = SqlHelper.Squery(sql, conn, spvalues);
lMonitorServerFile = DBTool.GetListFromDatatable <MonitorServerFile>(dt);
return(lMonitorServerFile);
}
/// <summary>
///
/// </summary>
/// <param name="where"></param>
/// <param name="page"></param>
/// <param name="pagesize"></param>
/// <param name="conn"></param>
/// <returns></returns>
public IList <MonitorFileListen> GetMonitorFileListenPage(string sql, int page, int pagesize, OdbcConnection conn)
{
IList <MonitorFileListen> lMonitorFileListen = new List <MonitorFileListen>();
sql = "select * from (" + sql + ") as a where row>" + (page - 1) * pagesize + " and row<=" + page * pagesize;
DataTable dt = OdbcHelper.Squery(sql, conn);
lMonitorFileListen = DBTool.GetListFromDatatable <MonitorFileListen>(dt);
return(lMonitorFileListen);
}
/// <summary>
/// Get BackupServerGroup By BackupServerID
/// </summary>
/// 2014-06-23 wjd add
/// <param name="conditon"></param>
/// <param name="conn"></param>
/// <returns></returns>
public IList <BackupServerGroup> GetBackupServerGroupByBackupServerID(int backupServerID, OdbcConnection conn)
{
IList <BackupServerGroup> lBackupServerGroup = new List <BackupServerGroup>();
string sql = @"select g.id,g.backupServerGroupName,g.monitorServerID,g.memo from [backupServerGroup] g
left join [backupServerGroupDetail] gd on gd.[backupServerGroupID] = g.[id]
where gd.backupServerID = " + backupServerID + " and gd.[deleteFlg] = 0 and g.[deleteFlg] = 0;";
DataTable dt = OdbcHelper.Squery(sql, conn);
lBackupServerGroup = DBTool.GetListFromDatatable <BackupServerGroup>(dt);
return(lBackupServerGroup);
}
private IEnumerable <ChangeStockBody> GetChangeStockDetail(Guid headId, SqlConnection conn)
{
IList <ChangeStockBody> lckbody = new List <ChangeStockBody>();
string sql = @"SELECT [HeadId]
,[ProductID]
,[Num]
FROM [ChangeStockBody] where HeadId=@HeadId";
SqlParameter sp = new SqlParameter("@HeadId", headId);
DataTable dt = SqlHelper.Squery(sql, conn, sp);
lckbody = DBTool.GetListFromDatatable <ChangeStockBody>(dt);
return(lckbody);
}
/// <summary>
/// 获取传送容量
/// </summary>
/// <param name="DBServerIP">like '192.168.253.131,192.168.254.33'</param>
/// <param name="groupId"></param>
/// <param name="dtStart"></param>
/// <param name="dtEnd"></param>
/// <param name="tmStart"></param>
/// <param name="tmEnd"></param>
/// <param name="name">file or folder name for searching</param>
/// <param name="stateFlg">1:OK, 0:NG or 2:both</param>
/// <returns></returns>
public IList <TransferLog> GetTransferLogList(string DBServerIP, int groupID, string dtStart, string dtEnd, string tmStart, string tmEnd, string name,
int stateFlg)
{
IList <TransferLog> lLog = new List <TransferLog>();
StringBuilder sb = new StringBuilder();
sb.Append(@"select monitorFileName,monitorFileSize,convert(varchar(10),backupStartTime,111) as DT,DATEPART(HH,backupStartTime) as HH from [log] where backupStartTime between '@StartDatetime' and '@EndDatetime' and DBServerIP in (@DBServerIP) ");
if (groupID > 0)
{
sb.Append(" and backupServerGroupID=@groupID ");
}
if (!string.IsNullOrWhiteSpace(name))
{
sb.Append(" and backupServerFileName like '%@backupServerFileName%' ");
}
if (stateFlg != 2)
{
sb.Append(" and backupFlg = ").Append(stateFlg);
}
sb.Replace("@StartDatetime", dtStart + " " + tmStart).Replace("@EndDatetime", dtEnd + " " + tmEnd).Replace("@DBServerIP", DBServerIP).Replace("@groupID", groupID.ToString()).Replace("@backupServerFileName", name);
string sql = @"select DT as transferDate, SUM(cast(monitorFileSize as bigint)) as transferFileSize,COUNT(monitorFileSize) as transferFileCount,HH as transferTime from (" + sb.ToString() + ") as a group by DT,HH order by DT,HH ";
try
{
using (SqlConnection conn = SqlHelper.CreateConntion())
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.CommandTimeout = 150;
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
if (dt != null && dt.Rows.Count > 0)
{
lLog = DBTool.GetListFromDatatable <TransferLog>(dt);
}
}
}
catch (Exception ex)
{
logger.Error(ex.Message);
}
return(lLog);
}
/// <summary>
/// 根据条件获取バックアップ元 from all DB
/// </summary>
/// 2014-07-06 wjd add
/// <param name="conditon"></param>
/// <param name="conn"></param>
/// <returns></returns>
public IList <MonitorServer> GetMonitorServerAll(IEnumerable <SearchCondition> conditon, OdbcConnection conn)
{
IList <MonitorServer> lMonitorServer = new List <MonitorServer>();
string sql = @"SELECT distinct id,DBServerIP,monitorServerName,monitorServerIP,monitorSystem,memo,account,password,monitorMacPath,monitorLocalPath,copyInit FROM monitorServer";
if (conditon.Count() > 0)
{
string con = DBTool.GetSqlcon(conditon);
sql += " where " + con;
}
OdbcParameter[] spvalues = DBTool.GetOdbcParam(conditon);
DataTable dt = OdbcHelper.Squery(sql, conn, spvalues);
lMonitorServer = DBTool.GetListFromDatatable <MonitorServer>(dt);
return(lMonitorServer);
}
/// <summary>
/// Get BackupServerGroup from All DB
/// </summary>
/// 2014-07-02 wjd add
/// <param name="conditon"></param>
/// <param name="conn"></param>
/// <returns></returns>
public IList <BackupServerGroup> GetBackupServerGroupAll(IEnumerable <SearchCondition> conditon, OdbcConnection conn)
{
IList <BackupServerGroup> lBackupServerGroup = new List <BackupServerGroup>();
string sql = "select id,DBServerIP,backupServerGroupName,monitorServerID,memo from backupServerGroup";
if (conditon.Count() > 0)
{
string con = DBTool.GetSqlcon(conditon);
sql += " where " + con;
}
OdbcParameter[] spvalues = DBTool.GetOdbcParam(conditon);
DataTable dt = OdbcHelper.Squery(sql, conn, spvalues);
lBackupServerGroup = DBTool.GetListFromDatatable <BackupServerGroup>(dt);
return(lBackupServerGroup);
}
public IList <BackupServer> GetBackupServer(IEnumerable <SearchCondition> conditon, SqlConnection conn)
{
IList <BackupServer> lBackupServer = new List <BackupServer>();
string sql = "select id,backupServerName,backupServerIP,memo,account,password,startFile from backupServer";
if (conditon.Count() > 0)
{
string con = DBTool.GetSqlcon(conditon);
sql += " where " + con;
}
SqlParameter[] spvalues = DBTool.GetSqlParam(conditon);
DataTable dt = SqlHelper.Squery(sql, conn, spvalues);
lBackupServer = DBTool.GetListFromDatatable <BackupServer>(dt);
return(lBackupServer);
}
public IList <UserInfo> GetUserInfo(IEnumerable <SearchCondition> conditon, SqlConnection conn)
{
IList <UserInfo> lUserInfo = new List <UserInfo>();
string sql = @"select id,loginID,name,password,mail,mailFlg,authorityFlg from userInfo";
if (conditon.Count() > 0)
{
string con = DBTool.GetSqlcon(conditon);
sql += " where " + con;
}
SqlParameter[] spvalues = DBTool.GetSqlParam(conditon);
DataTable dt = SqlHelper.Squery(sql, conn, spvalues);
lUserInfo = DBTool.GetListFromDatatable <UserInfo>(dt);
return(lUserInfo);
}
/// <summary>
///
/// </summary>
/// <param name="conditon"></param>
/// <param name="conn"></param>
/// <returns></returns>
public IList <ManualBackupServer> GetManualBackupServer(IEnumerable <SearchCondition> conditon, OdbcConnection conn)
{
IList <ManualBackupServer> lManualBackupServer = new List <ManualBackupServer>();
string sql = "select id,DBServerIP,serverIP,account,password,startFile,drive from manualBackupServer";
if (conditon.Count() > 0)
{
string con = DBTool.GetSqlcon(conditon);
sql += " where " + con;
}
OdbcParameter[] spvalues = DBTool.GetOdbcParam(conditon);
DataTable dt = OdbcHelper.Squery(sql, conn, spvalues);
lManualBackupServer = DBTool.GetListFromDatatable <ManualBackupServer>(dt);
return(lManualBackupServer);
}
public IList <FileTypeSet> GetFileTypeSet(IEnumerable <SearchCondition> conditon, SqlConnection conn)
{
IList <FileTypeSet> lFileTypeSet = new List <FileTypeSet>();
string sql = "select id,monitorServerFolderName,monitorServerID,exceptAttribute1,exceptAttribute2,exceptAttribute3,systemFileFlg,hiddenFileFlg from fileTypeSet";
if (conditon.Count() > 0)
{
string con = DBTool.GetSqlcon(conditon);
sql += " where " + con;
}
SqlParameter[] spvalues = DBTool.GetSqlParam(conditon);
DataTable dt = SqlHelper.Squery(sql, conn, spvalues);
lFileTypeSet = DBTool.GetListFromDatatable <FileTypeSet>(dt);
return(lFileTypeSet);
}
public IList <MonitorServer> GetMonitorServer(IEnumerable <SearchCondition> conditon, SqlConnection conn)
{
IList <MonitorServer> lMonitorServer = new List <MonitorServer>();
string sql = @"SELECT id,monitorServerName,monitorServerIP,memo,account,password,startFile,monitorDrive FROM monitorServer";
if (conditon.Count() > 0)
{
string con = DBTool.GetSqlcon(conditon);
sql += " where " + con;
}
SqlParameter[] spvalues = DBTool.GetSqlParam(conditon);
DataTable dt = SqlHelper.Squery(sql, conn, spvalues);
lMonitorServer = DBTool.GetListFromDatatable <MonitorServer>(dt);
return(lMonitorServer);
}
public IList <MonitorFileListen> GetPartMonitorFileListenList(string where, OdbcConnection conn)
{
IList <MonitorFileListen> lMonitorFileListen = new List <MonitorFileListen>();
string sql = @"select id,monitorServerID,monitorFileName,monitorType,monitorServerIP,sharePoint,monitorLocalPath,monitorFileRelativeDirectory,monitorFileRelativeFullPath,monitorFileLastWriteTime,monitorFileSize,
monitorFileExtension,monitorFileCreateTime,monitorFileLastAccessTime,monitorStatus,monitorFileStartTime,
monitorFileEndTime,deleteFlg,deleter,deleteDate,creater,createDate,updater,updateDate
from MonitorFileListen";
if (where != "")
{
sql += " where " + where;
}
DataTable dt = OdbcHelper.Squery(sql, conn);
lMonitorFileListen = DBTool.GetListFromDatatable <MonitorFileListen>(dt);
return(lMonitorFileListen);
}
/// <summary>
///
/// </summary>
/// <param name="where"></param>
/// <param name="conn"></param>
/// <returns></returns>
public IList <Log> GetConditionLog(string where, OdbcConnection conn)
{
IList <Log> lLog = new List <Log>();
string sql = @"SELECT id
,monitorServerID
,monitorFileName
,monitorFileStatus
,monitorFilePath
,monitorFileType
,monitorFileSize
,monitorTime
,transferFlg
,backupServerGroupID
,backupServerID
,backupServerFileName
,backupServerFilePath
,backupServerFileType
,backupServerFileSize
,backupStartTime
,backupEndTime
,backupTime
,backupFlg
,copyStartTime
,copyEndTime
,copyTime
,copyFlg
,creater
,createDate
FROM log";
if (where != "")
{
sql += " where " + where;
}
sql += " order by id desc";
try
{
//conn.ConnectionTimeout = 150;
DataTable dt = OdbcHelper.Squery(sql, conn);
lLog = DBTool.GetListFromDatatable <Log>(dt);
}
catch (Exception ex) {
}
return(lLog);
}
private IEnumerable <CheckBillBody> GetDetail(Guid headId, SqlConnection conn)
{
IList <CheckBillBody> lckbody = new List <CheckBillBody>();
string sql = @"SELECT
,[ProductID]
,[ProductName]
,[CategoryID]
,[Price]
,[Place]
,[NowNum]
,[RealNum]
FROM [CheckBillBody] where HeadId=@HeadId";
SqlParameter sp = new SqlParameter("@HeadId", headId);
DataTable dt = SqlHelper.Squery(sql, conn, sp);
lckbody = DBTool.GetListFromDatatable <CheckBillBody>(dt);
return(lckbody);
}
public IList <UserInfo> GetUserInfoPage(IEnumerable <SearchCondition> conditon, int page, int pagesize, SqlConnection conn)
{
IList <UserInfo> lUserInfo = new List <UserInfo>();
string sql = @"SELECT id,loginID,name,password,mail,mailFlg,authorityFlg
,ROW_NUMBER() over(order by createDate) as row
FROM userInfo ";
if (conditon.Count() > 0)
{
string con = DBTool.GetSqlcon(conditon);
sql += " where " + con;
}
sql = "select * from (" + sql + ") as a where row>" + (page - 1) * pagesize + " and row<=" + page * pagesize;
SqlParameter[] spvalues = DBTool.GetSqlParam(conditon);
DataTable dt = SqlHelper.Squery(sql, conn, spvalues);
lUserInfo = DBTool.GetListFromDatatable <UserInfo>(dt);
return(lUserInfo);
}
