/// <summary>
/// get sap A501 & A306 ladder price
/// </summary>
/// <returns></returns>
private SapMiddleData GetSapPriceList()
{
//抓全部資料，由於不是參數化查詢，第二個參數傳null
string _SqlA501 = @"select a.KUNNR ,d.name1,a.ZZENDCUST,e.name1 as zname1,a.MATNR ,b.KONWA,c.KSTBM,c.KBETR,c.KLFN1
,a.KNUMH ,b.KPEIN ,a.DATAB ,a.kschl from sapabap1.A501 a join sapabap1.KONP b on a.KNUMH=b.KNUMH join sapabap1.KONM c on a.KNUMH=c.KNUMH join sapabap1.kna1 d on a.kunnr=d.kunnr join sapabap1.kna1 e on a.ZZENDCUST=e.kunnr
where DATBI='99991231' ";
if (txtDate.Text != "")
{
_SqlA501 += " and DATAB > " + txtDate.Text;
}
_SqlA501 += " order by a.knumh,a.KUNNR,a.ZZENDCUST,c.KLFN1,a.matnr";
DataTable dtA501 = OdbcHelper.GetDataTableText(_SqlA501, null);
string _SqlA306 = @"select a.KUNNR ,d.name1, a.MATNR, b.KONWA ,c.KSTBM,c.KBETR,c.KLFN1 ,a.KNUMH ,b.KPEIN,a.DATAB
,a.kschl from sapabap1.A305 a join sapabap1.KONP b on a.KNUMH=b.KNUMH join sapabap1.KONM c on a.KNUMH=c.KNUMH
join sapabap1.kna1 d on a.kunnr=d.kunnr where DATBI='99991231' ";
if (txtDate.Text != "")
{
_SqlA306 += " and DATAB > " + txtDate.Text;
}
_SqlA306 += " order by a.knumh,a.KUNNR,a.matnr,c.KLFN1";
DataTable dtA306 = OdbcHelper.GetDataTableText(_SqlA306, null);
dtA501.Merge(dtA306);
SapPrice _SapPrice = new SapPrice();
SapMiddleData _SapMiddleData = _SapPrice.Parse(dtA501);
return(_SapMiddleData);
}
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 <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>
/// Get Deleted BackupServer List By IP And StartFolder
/// </summary>
/// 2014-06-30 wjd add
/// <param name="bkIP"></param>
/// <param name="startFolder"></param>
/// <returns></returns>
public IList <BackupServer> GetDeletedBackupServerList(string bkIP, string startFolder)
{
OdbcConnection conn;
IList <BackupServer> BackupServer;
SearchCondition[] condition = new SearchCondition[]
{
new SearchCondition {
con = "backupServerIP=?", param = "@backupServerIP", value = bkIP
},
new SearchCondition {
con = "startFile=?", param = "@startFile", value = startFolder
},
new SearchCondition {
con = "deleteFlg=?", param = "@deleteFlg", value = "1"
}
};
using (conn = OdbcHelper.CreateConntion())
{
conn.Open();
BackupServer = BackupServerDal.GetBackupServer(condition, conn);
conn.Close();
return(BackupServer);
}
}
/// <summary>
///
/// </summary>
/// <param name="Log"></param>
/// <param name="conn"></param>
/// <returns></returns>
public int UpdateLog(Log Log, OdbcConnection conn)
{
string sql = @"UPDATE log
SET monitorServerID = ?
,monitorFileName = ?
,monitorFileStatus = ?
,monitorFilePath = ?
,monitorFileType = ?
,monitorFileSize = ?
,monitorTime = ?
,transferFlg = ?
,backupServerGroupID = ?
,backupServerID = ?
,backupServerFileName = ?
,backupServerFilePath = ?
,backupServerFileType = ?
,backupServerFileSize = ?
,backupStartTime = ?
,backupEndTime = ?
,backupTime = ?
,backupFlg = ?
,copyStartTime = ?
,copyEndTime = ?
,copyTime = ?
,copyFlg = ?
,updater = ?
,updateDate = ?
WHERE id=@id";
OdbcParameter[] spvalues = DBTool.GetOdbcPm(Log);
return(OdbcHelper.ExecuteNonQuery(conn, System.Data.CommandType.Text, sql, spvalues));
}
public IList <MonitorFileListen> GetMonitorFileListenList(string fileName, string updateTime)
{
OdbcConnection conn;
IList <MonitorFileListen> MonitorFileListen;
//SearchCondition[] condition = new SearchCondition[] { new SearchCondition { con = "deleteFlg=?", param = "@deleteFlg", value = "0" },
//new SearchCondition { con = "monitorFileName like '%?%'", param = "@monitorFileName", value = fileName },
//new SearchCondition { con = "Convert(varchar,updateDate,120) like '%?%'", param = "@updateDate", value = updateTime }};
string where = "deleteFlg = 0";
if (fileName != "")
{
where += " AND monitorfileName like '" + fileName + "%'";
}
if (updateTime != "")
{
where += " AND Convert(varchar,updateDate,120) like '" + updateTime + "%'";
}
using (conn = OdbcHelper.CreateConntion())
{
conn.Open();
//MonitorFileListen = MonitorFileListenDal.GetPartMonitorFileListenList(condition, conn);
MonitorFileListen = MonitorFileListenDal.GetPartMonitorFileListenList(where, conn);
conn.Close();
return(MonitorFileListen);
}
}
public int GetMaxId(OdbcConnection conn)
{
string sql = @"SELECT max(id) as maxId FROM monitorServer";
int count = (int)OdbcHelper.ExecuteScalar(conn, CommandType.Text, sql);
return(count);
}
/// <summary>
/// only get buyer 1000 data
/// country name in english
/// US/CN need mapping region
/// </summary>
/// <returns></returns>
private List <WebApi.DataModel.CustomModel.SAP.BpXY> GetBpXYList()
{
List <WebApi.DataModel.CustomModel.SAP.BpXY> _BpXYList = new List <WebApi.DataModel.CustomModel.SAP.BpXY>();
BLL.BpXY _BpXY = new BLL.BpXY();
string _Sql = @" select a.kunnr,c.BU_SORT1,c.BU_SORT2,vkorg,d.NAME_CO ,a.KDGRP ,b.land1 ,e.landx,d.REGION
from sapabap1.KNVV a
join sapabap1.kna1 b on a.kunnr=b.kunnr
join sapabap1.but000 c on a.kunnr = c.partner
JOIN sapabap1.ADRC d ON d.ADDRNUMBER = b.ADRNR
join sapabap1.t005t e on b.land1=e.land1
where c.bu_group='1000' and e.spras='E' and e.mandt='888'
and a.loevm <> 'X' ";
DataTable dt = OdbcHelper.GetDataTableText(_Sql, null);
string _SqlRegion = @" select bland,bezei from sapabap1.T005U where mandt='888' and spras='E' and (land1='CN'
or land1='US') ";
DataTable dtRegion = OdbcHelper.GetDataTableText(_SqlRegion, null);
_BpXYList = _BpXY.Parse(dt, dtRegion);
string _SqlCE11000 = @" select ERLOS,GJAHR,KNDNR from sapabap1.CE11000
WHERE PALEDGER = '01'
AND ( VRGAR = 'F' OR VRGAR = 'Y' ) ";
DataTable dtCE11000 = OdbcHelper.GetDataTableText(_SqlCE11000, null);
_BpXYList = _BpXY.ParseRevenue(_BpXYList, dtCE11000);
return(_BpXYList);
}
/// <summary>
/// get customer address data no need content end cust(20001XXX)
/// except customer be delete
/// --d.bu_group='1000'
/// --and
/// </summary>
/// <returns></returns>
private List <WebApi.DataModel.CustomModel.SAP.BpAddressAll> GetBpAddressAllList()
{
string _SqlKNA1 = @" SELECT a.land1, a.kunnr,a.name1,str_suppl1,str_suppl2 ,b.street
,b.LOCATION
,b.POST_CODE1,b.CITY1
,b.COUNTRY,b.REGION,b.TIME_ZONE
, CITY2
,str_suppl3
,HOME_CITY
,c.VKORG
from sapabap1.kna1 a
join sapabap1.adrc b on a.adrnr=b.ADDRNUMBER
join sapabap1.knvv c on a.kunnr=c.kunnr
join sapabap1.but000 d on a.kunnr = d.partner
where
d.bu_group='1000'
and
c.loevm <> 'X'
order by a.kunnr ";
DataTable dtKNA1 = OdbcHelper.GetDataTableText(_SqlKNA1, null);
IList <WebApi.DataModel.CustomModel.SAP.BpAddressAll> _BpAddressAllIList = dtKNA1.ToList <WebApi.DataModel.CustomModel.SAP.BpAddressAll>();
List <WebApi.DataModel.CustomModel.SAP.BpAddressAll> _BpAddressAllList = _BpAddressAllIList as List <WebApi.DataModel.CustomModel.SAP.BpAddressAll>;
return(_BpAddressAllList);
}
public IList <Log> GetLogListByProc(int pindex, int pagesize, string groupId, string dtStart, string dtEnd, string tmStart, string tmEnd, int displayFlg, int tranferLfg, int stateFlg, int logFlg, string name)
{
IList <Log> LogList;
OdbcParameter[] parameters =
{
//new OdbcParameter("@DBServerIP", DBServerIP),
new OdbcParameter("@groupId", groupId),
new OdbcParameter("@StartDate", dtStart),
new OdbcParameter("@EndDate", dtEnd),
new OdbcParameter("@StartTime", tmStart),
new OdbcParameter("@EndTime", tmEnd),
new OdbcParameter("@TranferFlg", tranferLfg),
new OdbcParameter("@StateFlg", stateFlg),
new OdbcParameter("@LogFlg", logFlg),
new OdbcParameter("@backupServerFileName", name),
new OdbcParameter("@Pindex", pindex),
new OdbcParameter("@Psize", pagesize)
};
OdbcConnection conn;
//2014-06-03 wjd modified
using (conn = OdbcHelper.CreateConntion()) //xiecongwen 20141217 改为访问各自数据库 98部分弃用
{
conn.Open();
LogList = LogDal.GetLogListByProc(conn, parameters, "{call sp_GetTransferList2(?,?,?,?,?,?,?,?,?,?,?)}");
conn.Close();
return(LogList);
}
}
/// <summary>
/// Get BackupServer List By Name But This Id
/// </summary>
/// 2014-06-24 wjd add
/// <param name="id"></param>
/// <param name="bkName"></param>
/// <returns></returns>
public IList <BackupServer> GetBackupServerListByNameButId(int id, string bkName)
{
OdbcConnection conn;
IList <BackupServer> BackupServer;
SearchCondition[] condition = new SearchCondition[]
{
new SearchCondition {
con = "id!=?", param = "@id", value = id.ToString()
},
new SearchCondition {
con = "deleteFlg=?", param = "@deleteFlg", value = "0"
},
new SearchCondition {
con = "backupServerName=?", param = "@backupServerName", value = bkName
}
};
using (conn = OdbcHelper.CreateConntion())
{
conn.Open();
BackupServer = BackupServerDal.GetBackupServer(condition, conn);
conn.Close();
return(BackupServer);
}
}
/// <summary>
/// 転送対象の失敗の場合、レコードの抽出
/// </summary>
/// <returns></returns>
public IList <MonitorServerFile> GetTransferNGFileList(string monitorServerID)
{
OdbcConnection conn;
IList <MonitorServerFile> MonitorServerFile;
SearchCondition[] condition = new SearchCondition[] { new SearchCondition {
con = "deleteFlg= ? ", param = "@deleteFlg", value = "0"
},
new SearchCondition {
con = "monitorServerID= ? ", param = "@monitorServerID", value = monitorServerID
},
new SearchCondition {
con = "transferFlg=?", param = "@transferFlg", value = "2"
},
new SearchCondition {
con = "id IN (SELECT max(id) AS id FROM monitorServerFile GROUP BY monitorFilePath) ", param = "", value = ""
} };
using (conn = OdbcHelper.CreateConntion())
{
conn.Open();
MonitorServerFile = MonitorServerFileDal.GetMonitorServerFile(condition, conn);
conn.Close();
return(MonitorServerFile);
}
}
/// <summary>
/// Get Log For Winform
/// </summary>
/// 2014-06-03 wjd modified
/// <param name="pindex"></param>
/// <param name="pagesize"></param>
/// <param name="groupId"></param>
/// <param name="dtStart"></param>
/// <param name="dtEnd"></param>
/// <param name="tmStart"></param>
/// <param name="tmEnd"></param>
/// <param name="displayFlg"></param>
/// <param name="tranferLfg"></param>
/// <param name="stateFlg"></param>
/// <param name="logFlg"></param>
/// <param name="name"></param>
/// <returns></returns>
public IList <Log> GetLogListByProc(int pindex, int pagesize, string groupId, DateTime dtStart, DateTime dtEnd, string tmStart, string tmEnd, int displayFlg, int tranferLfg, int stateFlg, int logFlg, string name)
{
IList <Log> LogList;
OdbcParameter[] parameters =
{
new OdbcParameter("@groupId", groupId),
new OdbcParameter("@StartDate", dtStart.ToString("yyyy-MM-dd")),
new OdbcParameter("@EndDate", dtEnd.ToString("yyyy-MM-dd")),
new OdbcParameter("@StartTime", tmStart),
new OdbcParameter("@EndTime", tmEnd),
new OdbcParameter("@TranferFlg", tranferLfg),
new OdbcParameter("@StateFlg", stateFlg),
new OdbcParameter("@LogFlg", logFlg),
new OdbcParameter("@backupServerFileName", name),
new OdbcParameter("@Pindex", pindex),
new OdbcParameter("@Psize", pagesize)
};
OdbcConnection conn;
using (conn = OdbcHelper.CreateConntion())
{
conn.Open();
LogList = LogDal.GetLogListByProc(conn, parameters, "{call sp_GetTransferList2(?,?,?,?,?,?,?,?,?,?,?)}");
conn.Close();
return(LogList);
}
}
/// <summary>
///
/// </summary>
/// <param name="conditon"></param>
/// <param name="page"></param>
/// <param name="pagesize"></param>
/// <param name="conn"></param>
/// <returns></returns>
public IList <MonitorServerFile> GetMonitorServerFilePage(IEnumerable <SearchCondition> conditon, int page, int pagesize, OdbcConnection 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;
OdbcParameter[] spvalues = DBTool.GetOdbcParam(conditon);
DataTable dt = OdbcHelper.Squery(sql, conn, spvalues);
lMonitorServerFile = DBTool.GetListFromDatatable <MonitorServerFile>(dt);
return(lMonitorServerFile);
}
/// <summary>
///
/// </summary>
/// <param name="conditon"></param>
/// <param name="conn"></param>
/// <returns></returns>
public IList <MonitorServerFile> GetMonitorServerFile(IEnumerable <SearchCondition> conditon, OdbcConnection conn)
{
IList <MonitorServerFile> lMonitorServerFile = new List <MonitorServerFile>();
string sql = @"SELECT id
,monitorServerID
,monitorFileName
,monitorFileDirectory
,monitorFilePath
,monitorFileType
,monitorFileSize
,monitorFileStatus
,monitorStartTime
,transferFlg
,transferNum
FROM monitorServerFile";
if (conditon.Count() > 0)
{
string con = DBTool.GetSqlcon(conditon);
sql += " where " + con;
}
sql += " order by id desc";
OdbcParameter[] spvalues = DBTool.GetOdbcParam(conditon);
DataTable dt = OdbcHelper.Squery(sql, conn, spvalues);
lMonitorServerFile = DBTool.GetListFromDatatable <MonitorServerFile>(dt);
return(lMonitorServerFile);
}
//
public static void SaveopcXML()
{
try
{
string sPath = "D:/OpcXML/data.xml";
ZipFile(sPath);
FileStream fs = new FileStream("D:/OpcXML/data.zip", FileMode.Open);
BinaryReader br = new BinaryReader(fs);
Byte[] byData = br.ReadBytes((int)fs.Length);
string sData = System.Text.Encoding.UTF8.GetString(byData);
DateTime dt_value = DateTime.Now;
string sCreateTime = dt_value.Year.ToString("000#") +
dt_value.Month.ToString("0#") +
dt_value.Day.ToString("0#") +
dt_value.Hour.ToString("0#") +
dt_value.Minute.ToString("0#") +
dt_value.Second.ToString("0#");
//rawtohex()
fs.Dispose();//释放资源
DeleteFile("D:/OpcXML/data.zip");
OdbcHelper.OracleExeQuery("update U_WIPPROCDATA set FILE_DATA=:zp, CREATE_TIME=" + sCreateTime + ",CREATE_USER_ID='1111' where FACTORY ='15车间' AND LOT_ID ='1'", byData);
fs.Close();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return;
}
}
/// <summary>
/// 転送容量の取得
/// </summary>
/// <param name="groupId">グループid</param>
/// <param name="dtStart">開始日付</param>
/// <param name="dtEnd">終了日付</param>
/// <param name="tmStart">開始時間</param>
/// <param name="tmEnd">終了時間</param>
/// <param name="displayFlg">表示フラグ</param>
/// <param name="tranferLfg">0:転送中-完了両方；1:転送中のみ</param>
/// <param name="stateFlg">0:OK-NG両方；1:NGのみ</param>
/// <param name="logFlg">0:ログ表示；1:転送容量表示</param>
/// <returns></returns>
public IList <TransferLog> GetTransferLogListByProc(int groupId, string dtStart, string dtEnd, string tmStart, string tmEnd, int displayFlg, int tranferFlg, int stateFlg, int logFlg)
{
IList <TransferLog> LogList;
OdbcParameter[] parameters =
{
new OdbcParameter("@groupId", groupId),
new OdbcParameter("@StartDate", dtStart),
new OdbcParameter("@EndDate", dtEnd),
new OdbcParameter("@StartTime", tmStart),
new OdbcParameter("@EndTime", tmEnd),
new OdbcParameter("@TranferFlg", tranferFlg),
new OdbcParameter("@StateFlg", stateFlg),
new OdbcParameter("@LogFlg", logFlg)
};
OdbcConnection conn;
using (conn = OdbcHelper.CreateConntion())
{
conn.Open();
//sqlcommand ストアド実行方法
//LogList = TransferLogDal.GetTransferLogListByProc(conn, parameters, "sp_GetTranferList");
//odbccommand ストアド実行方法
LogList = TransferLogDal.GetTransferLogListByProc(conn, parameters, "{call sp_GetTranferList(?,?,?,?,?,?,?,?)}");
conn.Close();
return(LogList);
}
}
public IList <MonitorFileListen> GetMonitorFileListenList(string fileName, int monitorServerId)
{
OdbcConnection conn;
IList <MonitorFileListen> MonitorFileListen;
string where = "deleteFlg = 0";
if (fileName != "")
{
where += " AND monitorfileName like '%" + fileName + "%'";
}
if (monitorServerId != -1)
{
where += " AND monitorServerID = " + monitorServerId;
}
using (conn = OdbcHelper.CreateConntion())
{
conn.Open();
MonitorFileListen = MonitorFileListenDal.GetPartMonitorFileListenList(where, conn);
conn.Close();
return(MonitorFileListen);
}
}
private List <WebApi.DataModel.CustomModel.SAP.BpCountry> GetCountryList()
{
string _Sql = @"select SPRAS,LAND1,LANDX from sapabap1.T005T
where ( SPRAS='1' or SPRAS='M' or SPRAS='E' ) and MANDT='888' ";
DataTable dt = OdbcHelper.GetDataTableText(_Sql, null);
IList <WebApi.DataModel.CustomModel.SAP.BpCountry> _BpCountryIList = dt.ToList <WebApi.DataModel.CustomModel.SAP.BpCountry>();
List <WebApi.DataModel.CustomModel.SAP.BpCountry> _BpCountryList = _BpCountryIList as List <WebApi.DataModel.CustomModel.SAP.BpCountry>;
return(_BpCountryList);
}
/// <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);
}
public int GetMonitorFileListenListCount(string monitorFileName, string monitorServerID, string dirname)
{
OdbcConnection conn;
int count = 0;
//simplify sql——2014-07-03 wjd modified
string where = "";//deleteFlg = 0 AND monitorType<>'削除'
//monitorServerId format: DBServerIP|monitorServerId Id——2014-07-06 wjd modified
string DBServerIP = "";
string msId = "";
if (monitorServerID.Trim('|').IndexOf('|') > 0)
{
string[] array = monitorServerID.Trim('|').Split('|');
DBServerIP = "'" + array[0] + "'";
msId = array[1];
}
else
{
DBServerIP = Common.CommonUtil.GetLoginIPWithQuote();
msId = monitorServerID;
}
//file or folder
if (monitorFileName != "")
{
if (dirname == "0")
{
where += " monitorFileName like '%" + monitorFileName + "%'";// AND monitorFileRelativeDirectory not like '%\\" + monitorFileName + "%'
}
else
{
where += " monitorFileRelativeDirectory like '%\\" + monitorFileName + "%'";//id IN(select min(id) from (SELECT min(id) as id,min(CHARINDEX('\\" + monitorFileName + "', monitorFileRelativeDirectory)) AS patindexs FROM monitorFileListen where deleteFlg=0 and monitorType<>'削除' group by monitorFileRelativeDirectory) as a group by a.patindexs) AND
}
where += " AND ";
}
//one or all monitorServers
if (msId != "-1")
{
where += " monitorServerID = " + msId + " AND ";
}
//add condition DBServerIP——2014-06-04 wjd modified
where += " DBServerIP in (" + DBServerIP + ") ";
using (conn = OdbcHelper.CreateConntionAll())
{
conn.Open();
count = MonitorFileListenDal.GetMonitorFileListenListCount(where, conn);
conn.Close();
return(count);
}
}
private List <WebApi.DataModel.CustomModel.SAP.BpAddress> GetBpAddressList()
{
BLL.BpAddress _BpAddress = new BLL.BpAddress();
List <WebApi.DataModel.CustomModel.SAP.BpAddress> _BpAddressList = new List <WebApi.DataModel.CustomModel.SAP.BpAddress>();
string _SqlKNA1 = @" SELECT kunnr,a.name1,str_suppl1,str_suppl2
from sapabap1.kna1 a
join sapabap1.adrc b on a.adrnr=b.ADDRNUMBER ";
DataTable dtKNA1 = OdbcHelper.GetDataTableText(_SqlKNA1, null);
_BpAddressList = _BpAddress.Parse(dtKNA1);
return(_BpAddressList);
}
/// <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 List <WebApi.DataModel.CustomModel.SAP.BpSales> GetBpSalesList()
{
string _SqlKNA1 = @" SELECT a.vkorg, a.kunnr ,c.name1,c.name2,SNAME
FROM sapabap1.KNVP a
INNER JOIN sapabap1.PA0001 b ON b.PERNR = a.PERNR
join sapabap1.kna1 c on a.kunnr=c.kunnr ";
DataTable dtKNA1 = OdbcHelper.GetDataTableText(_SqlKNA1, null);
IList <WebApi.DataModel.CustomModel.SAP.BpSales> _BpSalesIList = dtKNA1.ToList <WebApi.DataModel.CustomModel.SAP.BpSales>();
List <WebApi.DataModel.CustomModel.SAP.BpSales> _BpSalesList = _BpSalesIList as List <WebApi.DataModel.CustomModel.SAP.BpSales>;
return(_BpSalesList);
}
/// <summary>
/// test sap_proc='S'
/// </summary>
/// <returns></returns>
private List <EdiStatus> GetSapEdiStatusList()
{
string _Sql = @"select
mandt,vbeln,sap_proc,seq,laeda,aenam
from sapabap1.zsdt046
where sap_proc = '' ";
DataTable dt = OdbcHelper.GetDataTableText(_Sql, null);
IList <EdiStatus> _EdiStatusIList = dt.ToList <EdiStatus>();
List <EdiStatus> _EdiStatusList = _EdiStatusIList as List <EdiStatus>;
return(_EdiStatusList);
}
public static void SaveopcXML()
{
try
{
DateTime dt_value = DateTime.Now;
string sCreateTime = dt_value.Year.ToString("000#") +
dt_value.Month.ToString("0#") +
dt_value.Day.ToString("0#") +
dt_value.Hour.ToString("0#") +
dt_value.Minute.ToString("0#") +
dt_value.Second.ToString("0#");
int itemID = 167;
string[] aTestData = ReadFiles.ReadInTestData();
string[] aTestType = ReadFiles.ReadInTestType();
string[] aTestStep = ReadFiles.ReadInTestStep();
string[] aTestOper = ReadFiles.ReadInTestOper();
string sAcType = " ";
//for (int i = 0; i < aTestData.Length; i++)
//{
// if(aTestType[i]!="Q"&& aTestType[i] != "5S"&& aTestType[i] != "E")
// {
// sAcType = "P";
// }
// else
// {
// sAcType = aTestType[i];
// }
// OdbcHelper.SQLExeQuery("INSERT INTO U_WIPPROCCONFITEM (FACTORY,ITEM_ID,ITEM_DESC,ACTIVITY_DESC,ACTIVITY_TYPE,USER_QUALIFICATION, CREATE_TIME,CREATE_USER_ID,UPDATE_TIME,UPDATE_USER_ID) " +
// " VALUES ('CWEC','" + itemID + "',' ','" + aTestData[i].Trim() + "','" + sAcType.Trim() + "',' ','" + sCreateTime + "','ADMIN','" + sCreateTime + "','ADMIN')");
// itemID++;
//}
string sStep = " ";
string sOper = " ";
int index = -1;
for (int i = 0; i < aTestType.Length; i++)
{
if (aTestType[i] == "1")
{
index++;
}
OdbcHelper.SQLExeQuery("INSERT INTO U_WIPPROCCONFSETITEMREL (FACTORY,SET_ID,ITEM_ID,OPER,STEP,SEQ_NUM,CRITICAL_FLAG, CREATE_TIME,CREATE_USER_ID,UPDATE_TIME,UPDATE_USER_ID) " +
" VALUES ('CWEC','W6L34DFB','" + itemID + "',' ','" + aTestStep[index].Trim() + "'," + Convert.ToInt32(itemID) + ",'N','" + sCreateTime + "','ADMIN',' ',' ')");
itemID++;
}
//rawtohex()
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return;
}
}
public IList <UserInfo> GetUserInfoPage(IEnumerable <SearchCondition> condition, int page, int pagesize)
{
OdbcConnection conn;
IList <UserInfo> UserInfo;
using (conn = OdbcHelper.CreateConntion())
{
conn.Open();
UserInfo = UserInfoDal.GetUserInfoPage(condition, page, pagesize, conn);
conn.Close();
return(UserInfo);
}
}
public int DeleteUserInfo(int UserInfoId, string loginID)
{
OdbcConnection conn;
int count = 0;
using (conn = OdbcHelper.CreateConntion())
{
conn.Open();
count = UserInfoDal.DeleteUserInfoById(UserInfoId, loginID, conn);
conn.Close();
}
return(count);
}
public int InsertUserInfo(UserInfo UserInfo)
{
OdbcConnection conn;
int count = 0;
using (conn = OdbcHelper.CreateConntion())
{
conn.Open();
count = UserInfoDal.InsertUserInfo(UserInfo, conn);
conn.Close();
}
return(count);
}
public int GetUserInfoCount(IEnumerable <SearchCondition> condition)
{
OdbcConnection conn;
int count = 0;
using (conn = OdbcHelper.CreateConntion())
{
conn.Open();
count = UserInfoDal.GetUserInfoCount(condition, conn);
conn.Close();
}
return(count);
}
