protected void bindFunctionList()
{
functionList.Items.Clear();
//for (int i = 1; i <= 35; i++)
//{
// ListItem li = new ListItem();
// string img = "0" + i.ToString() + ".jpg";
// int len = img.Length;
// img = img.Substring(len - 6, 6);
// li.Value = img;
// li.Text = "<img src='../images/function_icon/" + img + "' />";
// functionList.Items.Add(li);
//}
SQLHelper db = new SQLHelper();
db.sql = "SELECT NewsId,Title,Description,pic FROM " + com.tablePrefix + "News WHERE lbid=64";
DataTable dt = db.Get_DataTable();
functionList.DataSource = dt;
foreach (DataRow dr in dt.Rows)
{
ListItem li = new ListItem();
li.Value = dr["NewsId"].ToString();
li.Text = "<img src='" + dr["pic"].ToString() + "' title='" + dr["Description"].ToString() + "' />";
functionList.Items.Add(li);
}
}
public void bindGv(bool addNewRow)
{
lbid = hlbid.Value;
SQLHelper db = new SQLHelper();
string pagesize = Pager2.PageSize.ToString();
string curpage = pg.request(Pager2.PagePara);
string pageCount = "";//总页数
int rowCount = 0;
if (pagesize == "") pagesize = "5";
if (curpage == "") curpage = "1";
string sql = @"SELECT *
FROM "+com.tablePrefix+@"News
WHERE lbid=" + lbid;
sql += " ORDER BY isTop DESC,IsIndex DESC,EditTime DESC";
sql = "exec sp_GetPageData '" + sql + "'," + curpage + "," + pagesize;
db.sql = sql;
DataSet ds = db.Get_DataSet();
DataTable dt = ds.Tables[2];
pageCount = ds.Tables[1].Rows[0]["COUNTPAGE"].ToString();
rowCount = Int32.Parse(ds.Tables[1].Rows[0]["ROWCOUNT"].ToString());
Pager2.RecordCount = rowCount;
if (rowCount == 0) Pager2.Visible = false;
if (addNewRow)
{
DataRow newRow = dt.NewRow();
dt.Rows.Add(newRow);
}
GV.DataSource = dt.DefaultView;
GV.DataBind();
}
/// <summary>
/// Creates a database out of the structure it is given
/// </summary>
/// <param name="Database">Database structure</param>
/// <param name="ConnectionString">The connection string to the database's location</param>
public static void CreateDatabase(Database Database, string ConnectionString)
{
string Command = BuildCommands(Database);
string[] Splitter = { "\n" };
string[] Commands = Command.Split(Splitter, StringSplitOptions.RemoveEmptyEntries);
ConnectionString = Regex.Replace(ConnectionString, "Pooling=(.*?;)", "", RegexOptions.IgnoreCase) + ";Pooling=false;";
string DatabaseConnectionString = Regex.Replace(ConnectionString, "Initial Catalog=(.*?;)", "", RegexOptions.IgnoreCase);
using (SQLHelper Helper = new SQLHelper(Commands[0], DatabaseConnectionString, CommandType.Text))
{
Helper.ExecuteNonQuery();
}
using (SQLHelper Helper = new SQLHelper("", ConnectionString, CommandType.Text))
{
try
{
Helper.BeginTransaction();
for (int x = 1; x < Commands.Length; ++x)
{
Helper.Command = Commands[x];
Helper.ExecuteNonQuery();
}
Helper.Commit();
}
catch { Helper.Rollback(); throw; }
}
}
/// <summary>
/// 得到當前最大ToDo Item ID
/// </summary>
/// <returns></returns>
private string GetMaxItemID(SQLHelper conn, string user_id, out int mySeq)
{
string rtn = "";
mySeq = 0;
string matchString = GetMatchString("D");
DataTable table = conn.OpenDataTable("select isnull(max(td_no),'') as maxid from lrtd00h (nolock) where td_no like '" + matchString + "%'", CommandType.Text);
if (table.Rows.Count > 0)
{
rtn = table.Rows[0][0].ToString();
}
table = conn.OpenDataTable("select isnull(max(td_seq),0) as maxid from lrtd00h (nolock) where td_owner= '" + user_id + "'", CommandType.Text);
if (table.Rows.Count > 0)
{
mySeq = Convert.ToInt32(table.Rows[0][0]);
}
else
{
mySeq = 0;
}
// 只留下流水號
rtn = rtn.Replace(matchString, "");
if (rtn == "")
rtn = "00000";
return rtn;
}
public static void del(string id)
{
SQLHelper.DeleteEntity<DXLb>(id);
SQLHelper db = new SQLHelper();
db.sql = "UPDATE lb SET isDeleted=1 WHERE id="+id;
db.ExecSql();
}
public DataTable getNewsDt()
{
productid = hlbid.Value;
SQLHelper db = new SQLHelper();
string pagesize = Pager0.PageSize.ToString();
string curpage = pg.request(Pager0.PagePara);
string pageCount = "";//总页数
int rowCount = 0;
if (pagesize == "") pagesize = "20";
if (curpage == "") curpage = "1";
string sql = @"SELECT c.imgTypeName,b.ColorValue,b.ColorName
,SUBSTRING((''000''+CONVERT(varchar,b.OrderId)),LEN(''000''+CONVERT(varchar,b.OrderId))-2,3)+''-''+b.ColorName AS ColorText
,a.*
FROM "+com.tablePrefix+@"News a LEFT JOIN "+com.tablePrefix+@"Color b ON a.colorId=b.id
LEFT JOIN "+com.tablePrefix+@"ProductImgType c ON c.id =a.pro_imgTypeid
WHERE a.ParentNewsID=" + productid;
sql += " ORDER BY a.OrderId";
sql = "exec sp_GetPageData '" + sql + "'," + curpage + "," + pagesize;
db.sql = sql;
DataSet ds = db.Get_DataSet();
DataTable dt = ds.Tables[2];
pageCount = ds.Tables[1].Rows[0]["COUNTPAGE"].ToString();
rowCount = Int32.Parse(ds.Tables[1].Rows[0]["ROWCOUNT"].ToString());
Pager0.RecordCount = rowCount;
if (rowCount == 0) Pager0.Visible = false;
return dt;
}
public DataTable getNewsDt()
{
SQLHelper db = new SQLHelper();
try
{
string pagesize = Pager1.PageSize.ToString();
string curpage = pg.request(Pager1.PagePara);
string pageCount = "";//总页数
int rowCount = 0;
if (pagesize == "") pagesize = "10";
if (curpage == "") curpage = "1";
string sql = "SELECT id,name,OrderId,JianCheng FROM " + com.tablePrefix + "province WHERE 1=1 ";
sql += " ORDER BY OrderId";
sql = "exec sp_GetPageData '" + sql + "'," + curpage + "," + pagesize;
db.sql = sql;
DataSet ds = db.Get_DataSet();
DataTable dt = ds.Tables[2];
pageCount = ds.Tables[1].Rows[0]["COUNTPAGE"].ToString();
rowCount = Int32.Parse(ds.Tables[1].Rows[0]["ROWCOUNT"].ToString());
Pager1.RecordCount = rowCount;
//if (rowCount == 0) Pager1.Visible = false;
return dt;
}
catch (NullReferenceException ex)
{
Response.Write("2. " + ex.Message + "。");
//Response.End();
return new DataTable();
}
}
protected void btnRun_Click(object sender, EventArgs e)
{
string sql = tbxSql.Text.Trim().ToUpper();
SQLHelper db = new SQLHelper();
db.sql = sql;
if (sql.Contains("SELECT "))
{
DataTable dt = db.Get_DataTable();
gv.DataSource = dt;
gv.DataBind();
lblMsg.Text = "执行完成，查询结果如下：";
}
else
{
string result = db.ExecSql();
if (result == "1")
{
lblMsg.Text = "执行完成";
}
else
{
lblMsg.Text = "执行失败，" + result;
}
}
}
public PeptideDAL(string sDataBase)
{
string sServer = "10.60.43.39";
string sUid = "root";
string sPwd = "root";
string sMySqlConnString = string.Format("Server={0};Database={1};Uid={2};Pwd={3};Persist Security Info=True;Pooling=False;", sServer, sDataBase, sUid, sPwd);
sqlHelper = new SQLHelper(sMySqlConnString);
lstColName.Add("ID");
lstColName.Add("AC");
lstColName.Add("DT");
lstColName.Add("DE");
lstColName.Add("GN");
lstColName.Add("OS");
lstColName.Add("OC");
lstColName.Add("OX");
lstColName.Add("RN");
lstColName.Add("RP");
lstColName.Add("RX");
lstColName.Add("RA");
lstColName.Add("RT");
lstColName.Add("RL");
lstColName.Add("RC");
lstColName.Add("RG");
lstColName.Add("CC");
lstColName.Add("DR");
lstColName.Add("PE");
lstColName.Add("KW");
lstColName.Add("FT");
lstColName.Add("SQ");
lstColName.Add("ModResSingle");
}
public DataTable getNewsDt()
{
SQLHelper db = new SQLHelper();
try
{
string pagesize = Pager1.PageSize.ToString();
string curpage = pg.request(Pager1.PagePara);
string pageCount = "";//总页数
int rowCount = 0;
if (pagesize == "") pagesize = "15";
if (curpage == "") curpage = "1";
string sql = @"SELECT id,ColorName,ColorValue,SUBSTRING((''000''+CONVERT(varchar,OrderId)),LEN(''000''+CONVERT(varchar,OrderId))-3,4) AS OrderId FROM " + com.tablePrefix + "Color ORDER BY OrderId";
sql = "exec sp_GetPageData '" + sql + "'," + curpage + "," + pagesize;
db.sql = sql;
DataSet ds = db.Get_DataSet();
DataTable dt = ds.Tables[2];
pageCount = ds.Tables[1].Rows[0]["COUNTPAGE"].ToString();
rowCount = Int32.Parse(ds.Tables[1].Rows[0]["ROWCOUNT"].ToString());
Pager1.RecordCount = rowCount;
if (rowCount == 0) Pager1.Visible = false;
return dt;
}
catch (Exception ex)
{
Response.Write("2. " + ex.Message + "。");
//Response.End();
return null;
}
}
private void Form1_Load(object sender, EventArgs e)
{
string folderLocation = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "jobTracker");
string dbLocation = Path.Combine(folderLocation,"jobTrackerDB.sqlite");
sqlHelper = new SQLHelper(dbLocation);
if (!File.Exists(dbLocation))
{
sqlHelper.createDB();
sqlHelper.createTable("jobs", createJobsTable.getJobsbasicInfo());
}
dataLocation = dbLocation;
txtExportLocation.Text = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
var jobsColumns = sqlHelper.getColumnNames("jobs");
foreach (string s in jobsColumns)
{
listViewDisplay.Columns.Add(s);
}
updateList();
}
public static DataTable AdminInfo(int id)
{
string sql = "SELECT * FROM " + com.tablePrefix + "Admin WHERE id=" + id.ToString() + "";
SQLHelper db = new SQLHelper();
db.sql = sql;
return db.Get_DataTable();
}
public static DataTable AdminInfo(string username)
{
string sql = "SELECT * FROM " + com.tablePrefix + "Admin WHERE username='******'";
SQLHelper db = new SQLHelper();
db.sql = sql;
return db.Get_DataTable();
}
/// <summary>
/// Creates a database out of the structure it is given
/// </summary>
/// <param name="Database">Database structure</param>
/// <param name="ConnectionString">The connection string to the database's location</param>
public static void CreateDatabase(Database Database,string ConnectionString)
{
try
{
string Command = BuildCommands(Database);
string[] Splitter = { "\n" };
string[] Commands = Command.Split(Splitter, StringSplitOptions.RemoveEmptyEntries);
string DatabaseConnectionString = Regex.Replace(ConnectionString, "Initial Catalog=(.*?;)", "");
using (SQLHelper Helper = new SQLHelper(Commands[0], DatabaseConnectionString, CommandType.Text))
{
try
{
Helper.Open();
Helper.ExecuteNonQuery();
}
catch { throw; }
finally { Helper.Close(); }
}
for (int x = 1; x < Commands.Length; ++x)
{
using (SQLHelper Helper = new SQLHelper(Commands[x], ConnectionString, CommandType.Text))
{
try
{
Helper.Open();
Helper.ExecuteNonQuery();
}
catch { throw; }
finally { Helper.Close(); }
}
}
}
catch { throw; }
}
/// <summary>
/// DNFData库的数据库连接对象
/// </summary>
/// <returns></returns>
public static Safe.Base.Contract.IDbHelper OrderSystemData()
{
//return Safe.CFData.DbHelper.Factory.GetIDbHelper(EConnType.MSSQL_CFDATA, true);
string connString = "data source=127.0.0.1;initial catalog=CYJH_OrderSystem;user id=sa;password=123456a;persist security info=True;Connection Timeout=5000;";//ConfigurationManager.ConnectionStrings["CYJHOrderSysConn"].ConnectionString;
SQLHelper sqlHelp = new SQLHelper(connString, true);
return sqlHelp;
//Safe.Base.DbHelper.SQLHelper.FromFile(@"D:\Configs\DataBase\CYJH_OrderSystem.dbc", true);
}
protected void bindProPlb()
{
SQLHelper db = new SQLHelper();
db.sql = "SELECT lbid,lbname FROM " + com.tablePrefix + "lb WHERE parentid=4 Order By OrderId";
DataTable dt = db.Get_DataTable();
rptProLbParent.DataSource = dt;
rptProLbParent.DataBind();
}
public PeptideModResDAL(string sDataBase)
{
string sServer = "10.60.43.39";
string sUid = "root";
string sPwd = "root";
string sMySqlConnString = string.Format("Server={0};Database={1};Uid={2};Pwd={3};Persist Security Info=True;Pooling=False;", sServer, sDataBase, sUid, sPwd);
sqlHelper = new SQLHelper(sMySqlConnString);
}
/// <summary>
/// 添加栏目
/// </summary>
/// <param name="lbname">栏目名称</param>
/// <param name="parentid">父栏目ID</param>
/// <returns>int,1=成功，0=已存在</returns>
public static string AddLb(string lbname, string parentid)
{
string maxlbid = MaxLbid();
string sql = "INSERT INTO " + com.tablePrefix + "lb (lbid,lbname,parentid,OrderId) VALUES(" + maxlbid + ",'" + lbname + "'," + parentid + "," + maxlbid + ")";
SQLHelper db = new SQLHelper();
db.sql = sql;
return db.ExecSql();
}
protected void btnAdd_Click(object sender, EventArgs e)
{
string lbid = hLbid.Value;
string id = hID.Value;
string title = pg.GetSafeString(txtTitle.Text);
string cityid = ddlCity.SelectedValue;
string newsBody = tbxBody.Text;
string tel = tbxDesc.Text;
//if (tel.Length < 8)
//{
// alert.ShowAndBack(Page, "联系方式不能小于8位数字");
// return;
//}
string sql = "";
if (cityid == "0" || cityid.Length==0)
{
alert.ShowAndBack(Page, "请选择省市");
return;
}
if (id.Length > 0)
{
//修改
sql = "UPDATE News SET Title='" + title + "',cityid='" + cityid + "',Description='" + tel + "',NewsBody='" + newsBody + "',EditTime=getdate() WHERE NewsID=" + id;
SQLHelper db = new SQLHelper();
db.sql = sql;
if (db.ExecSql() == "1")
{
alert.showAndGo("修改成功", "NetworkManage.aspx");
}
else
{
//alert.ShowAndBack(Page, "修改失败");
Response.Write(sql);
Response.End();
}
}
else
{
//添加
string newsid = clsNews.MaxNewsid();
lbid = "24";
sql = "INSERT INTO News(NewsID,lbid,Title,cityid,Description,NewsBody,Creator) VALUES(" + newsid + "," + lbid + ",'" + title + "','" + cityid + "','" + tel + "','" + newsBody + "','" + Session["AdminName"].ToString() + "')";
SQLHelper db = new SQLHelper();
db.sql = sql;
string result = db.ExecSql();
if (result == "1")
{
alert.showAndGo("添加成功", "NetworkManage.aspx");
}
else
{
Response.Write(result+",sql="+sql);
Response.End();
//alert.ShowAndBack(Page, "添加失败" + result);
}
}
}
public BESIMREG_NEWFORMs GetSIMREG_NEWFORMs(string msisdnTitle, string requestById, string requestTypeId, string deliverById, string requestFromDate, string requestToDate, string deliverFromDate, string deliverToDate, SQLHelper sqlHelper)
{
string sql = string.Empty;
string whereSql = string.Empty;
BESIMREG_NEWFORMs SIMREG_NEWFORMs = new BESIMREG_NEWFORMs();
try
{
whereSql += sqlHelper.MakeSQL(sql + " MSISDNTITLE=nvl($s,MSISDNTITLE)", msisdnTitle);
if (Convert.ToInt32(requestById) > 0)
{
whereSql += sqlHelper.MakeSQL(sql + " AND REQUESTEDBYID=$n", requestById);
}
if (Convert.ToInt32(requestTypeId) > 0)
{
whereSql += sqlHelper.MakeSQL(sql + " AND REQUESTEDTYPEID=$n", requestTypeId);
}
if (Convert.ToInt32(deliverById) > 0)
{
whereSql += sqlHelper.MakeSQL(sql + " AND DELIVEREDBYID=$n", deliverById);
}
if (!String.IsNullOrEmpty(requestFromDate))
{
whereSql += sqlHelper.MakeSQL(sql + " AND REQUESTEDDATE>=$s", requestFromDate);
}
if (!String.IsNullOrEmpty(requestToDate))
{
whereSql += sqlHelper.MakeSQL(sql + " AND REQUESTEDDATE<=$s", requestToDate);
}
if (!String.IsNullOrEmpty(deliverFromDate))
{
whereSql += sqlHelper.MakeSQL(sql + " AND DELIVEREDBYDATE>=$s", deliverFromDate);
}
if (!String.IsNullOrEmpty(deliverToDate))
{
whereSql += sqlHelper.MakeSQL(sql + " AND DELIVEREDBYDATE<=$s", deliverToDate);
}
sql = sqlHelper.MakeSQL(@"SELECT * FROM SIMREG_NEWFORM WHERE $q ORDER BY ID", whereSql);
IDataReader reader = sqlHelper.ExecuteQuery(sql);
AddToSIMREG_NEWFORMCollection(SIMREG_NEWFORMs, reader);
reader.Close();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return SIMREG_NEWFORMs;
}
protected void bindProvince()
{
SQLHelper db = new SQLHelper();
db.sql = "SELECT id,name FROM " + com.tablePrefix + "Province ORDER BY OrderID";
DataTable dt = db.Get_DataTable();
ddlProvince.DataSource = dt;
ddlProvince.DataValueField = "id";
ddlProvince.DataTextField = "name";
ddlProvince.DataBind();
}
protected void bindLbList()
{
pid = hPid.Value;
SQLHelper db = new SQLHelper();
db.sql = "SELECT lbid,lbname FROM " + com.tablePrefix + "lb WHERE parentid=" + pid + " Order by orderid";
DataTable dt = db.Get_DataTable();
lbList.DataSource = dt.DefaultView;
lbList.DataTextField = "lbname";
lbList.DataValueField = "lbid";
lbList.DataBind();
}
public void bindGv()
{
lbid = hlbid.Value;
string sql = @"SELECT * FROM " + com.tablePrefix + "jiameng ORDER BY AddTime DESC";
SQLHelper db = new SQLHelper();
db.sql = sql;
DataTable dt = db.Get_DataTable();
GV.DataSource = dt.DefaultView;
GV.DataBind();
}
protected void bindDDlLbid()
{
string sql = "SELECT lbid,lbname FROM " + com.tablePrefix + "lb WHERE parentid=1";
SQLHelper db = new SQLHelper();
db.sql = sql;
DataTable dt = db.Get_DataTable();
ddlLbid.DataSource = dt;
ddlLbid.DataTextField = "lbname";
ddlLbid.DataValueField = "lbid";
ddlLbid.DataBind();
}
public static Int32 GetSequenceNextValue(SQLHelper sqlHelper, string seqName)
{
string sql = string.Empty;
try
{
sql = "SELECT " + seqName + ".NEXTVAL FROM dual";
return Convert.ToInt32(sqlHelper.ExecuteScalar(sql));
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public void Delete(SQLHelper sqlHelper, int ID)
{
string sql = string.Empty;
try
{
sql = sqlHelper.MakeSQL(@"DELETE SIMREG_REQUESTEDBY WHERE REQUESTEDBYID=$n", ID);
sqlHelper.ExecuteNonQuery(sql);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public void bindGv()
{
lbid = hlbid.Value;
string sql = @"SELECT *
FROM "+com.tablePrefix+@"News
WHERE lbid=" + lbid;
sql += " ORDER BY isTop DESC,IsIndex DESC,AddTime DESC";
SQLHelper db = new SQLHelper();
db.sql = sql;
DataTable dt = db.Get_DataTable();
GV.DataSource = dt.DefaultView;
GV.DataBind();
}
public bool exists(string uname)
{
SQLHelper db = new SQLHelper();
db.sql = "SELECT * FROM " + com.tablePrefix + "Members WHERE username='******'";
DataTable dt = db.Get_DataTable();
if (dt.Rows.Count > 0)
{
return true;
}
else
{
return false;
}
}
public bool emailExists(string email)
{
SQLHelper db = new SQLHelper();
db.sql = "SELECT * FROM " + com.tablePrefix + "Members WHERE email='" + email + "'";
DataTable dt = db.Get_DataTable();
if (dt.Rows.Count > 0)
{
return true;
}
else
{
return false;
}
}
public void Save(BESIMREG_REQUESTEDBY entity)
{
try
{
sqlHelper = new SQLHelper(true);
daSIMREG_REQUESTEDBY.Save(sqlHelper, entity);
sqlHelper.CommitTran();
}
catch (Exception ex)
{
//SimReg.Common.Utility.SaveErrorLog(this.GetType().ToString(), "", ex);
if (sqlHelper != null) sqlHelper.Rollback();
throw ex;
}
}
public static DataTable GetCommodityFactor(Guid commodityFactorGroupId, Guid?gradingsID, string isdeposit) //
{
DataTable dt = SQLHelper.getDataTable(ConnectionString, "GetCommodityFactoryModified", commodityFactorGroupId, gradingsID, "export"); //
return(dt); //"GetCommodityFactor"
}
public void ProcessRequest(HttpContext context)
{
try
{
context.Response.ContentType = "text/plain";
string pageindex = HttpContext.Current.Request.Params["pageindex"];
if (string.IsNullOrEmpty(pageindex))
{
HttpContext.Current.Response.Write("pageindex error");
return;
}
string pagesize = HttpContext.Current.Request.Params["pagesize"];
if (string.IsNullOrEmpty(pagesize))
{
HttpContext.Current.Response.Write("pagesize error");
return;
}
string sqlCount = @"SELECT count(DISTINCT EquipmentId) FROM [dbo].[FaultUserInfo]";
DataSet dscount = SQLHelper.GetDataSet(sqlCount);
string sqlSearch = string.Format(@"SELECT temp.*
FROM ( SELECT TOP ( {0} * {1} ) ROW_NUMBER() OVER ( ORDER BY [EquipmentId] ) AS rownum ,
EquipmentId,EquipmentName,UserList
from (
select distinct EquipmentId,EquipmentName,
STUFF((SELECT ';'+UserName FROM [dbo].[FaultUserInfo] WHERE EquipmentId = A.EquipmentId FOR XML PATH('')),1,1,'')AS UserList
FROM [dbo].[FaultUserInfo] AS A
) q
) AS temp
WHERE temp.rownum > ( {0} * ( {1} - 1 ))
ORDER BY temp.[EquipmentId] ", pagesize, pageindex);
DataSet dsSearch = SQLHelper.GetDataSet(sqlSearch);
string jsonText = "";
JsonHelper jsonHelper = new JsonHelper();
if (dsSearch != null && dsSearch.Tables[0].Rows.Count > 0)
{
jsonText = jsonHelper.DataTableToJson(dsSearch.Tables[0], int.Parse(dscount.Tables[0].Rows[0][0].ToString()));
}
else
{
DataTable dt = new DataTable();
dt.Columns.Add("tips");
dt.AcceptChanges();
DataRow dr = dt.NewRow();
dr["tips"] = "没有数据";
dt.Rows.Add(dr);
dt.AcceptChanges();
jsonText = jsonHelper.DataTableToJson(dt, 0);
}
HttpContext.Current.Response.Write(jsonText);
}
catch (Exception ex)
{
JsonHelper jsonHelper = new JsonHelper();
DataTable dt = new DataTable();
dt.Columns.Add("tips");
dt.AcceptChanges();
DataRow dr = dt.NewRow();
dr["tips"] = "没有数据";
dt.Rows.Add(dr);
dt.AcceptChanges();
string jsonText = jsonHelper.DataTableToJson(dt, 0);
HttpContext.Current.Response.Write(jsonText);
//HttpContext.Current.Response.End();
}
}
/// <summary>
/// 保存数据
/// </summary>
/// <param name="type"></param>
private void SaveData(string type)
{
Model.Check_CheckWork checkWork = new Model.Check_CheckWork
{
CheckWorkCode = this.txtCheckWorkCode.Text.Trim(),
CheckTime = Funs.GetNewDateTime(this.txtCheckDate.Text.Trim()),
ProjectId = this.ProjectId,
Area = this.txtArea.Text.Trim()
};
if (!string.IsNullOrEmpty(this.drpThisUnit.SelectedValue))
{
checkWork.ThisUnitId = this.drpThisUnit.SelectedValue;
}
//本单位人员
string mainUnitPerson = string.Empty;
foreach (var item in this.drpMainUnitPerson.SelectedValueArray)
{
mainUnitPerson += item + ",";
}
if (!string.IsNullOrEmpty(mainUnitPerson))
{
mainUnitPerson = mainUnitPerson.Substring(0, mainUnitPerson.LastIndexOf(","));
}
checkWork.MainUnitPerson = mainUnitPerson;
//参与单位
string subUnits = string.Empty;
foreach (var item in this.drpSubUnits.SelectedValueArray)
{
subUnits += item + ",";
}
if (!string.IsNullOrEmpty(subUnits))
{
checkWork.SubUnits = subUnits.Substring(0, subUnits.LastIndexOf(","));
}
//参与用户
string subUnitPerson = string.Empty;
foreach (var item in this.drpSubUnitPerson.SelectedValueArray)
{
if (item != BLL.Const._Null)
{
subUnitPerson += item + ",";
}
}
if (!string.IsNullOrEmpty(subUnitPerson))
{
checkWork.SubUnitPerson = subUnitPerson.Substring(0, subUnitPerson.LastIndexOf(","));
}
checkWork.PartInPersonNames = this.txtPartInPersonNames.Text.Trim();
if (this.ckIsAgree.Checked)
{
checkWork.IsAgree = true;
}
checkWork.MainUnitDeputy = this.txtMainUnitDeputy.Text.Trim();
checkWork.MainUnitDeputyDate = Funs.GetNewDateTime(this.txtMainUnitDeputyDate.Text.Trim());
checkWork.SubUnitDeputy = this.txtSubUnitDeputy.Text.Trim();
checkWork.SubUnitDeputyDate = Funs.GetNewDateTime(this.txtSubUnitDeputyDate.Text.Trim());
////单据状态
checkWork.States = BLL.Const.State_0;
if (type == BLL.Const.BtnSubmit)
{
checkWork.States = this.ctlAuditFlow.NextStep;
}
if (!string.IsNullOrEmpty(this.CheckWorkId))
{
checkWork.CheckWorkId = this.CheckWorkId;
BLL.Check_CheckWorkService.UpdateCheckWork(checkWork);
BLL.LogService.AddSys_Log(this.CurrUser, checkWork.CheckWorkCode, checkWork.CheckWorkId, BLL.Const.ProjectCheckWorkMenuId, BLL.Const.BtnAdd);
}
else
{
checkWork.CheckWorkId = SQLHelper.GetNewID(typeof(Model.Check_CheckWork));
checkWork.CompileMan = this.CurrUser.UserId;
this.CheckWorkId = checkWork.CheckWorkId;
BLL.Check_CheckWorkService.AddCheckWork(checkWork);
BLL.LogService.AddSys_Log(this.CurrUser, checkWork.CheckWorkCode, checkWork.CheckWorkId, BLL.Const.ProjectCheckWorkMenuId, BLL.Const.BtnModify);
}
////保存流程审核数据
this.ctlAuditFlow.btnSaveData(this.ProjectId, BLL.Const.ProjectCheckWorkMenuId, this.CheckWorkId, (type == BLL.Const.BtnSubmit ? true : false), checkWork.Area, "../Check/CheckWorkView.aspx?CheckWorkId={0}");
}
/// <summary>
/// 更新多条记录
/// </summary>
/// <param name="sets">设置内容(例:id=1,name='aaa')</param>
/// <param name="strWhere">条件</param>
/// <returns>影响行数</returns>
public int Updates(string sets, string strWhere)
{
return(SQLHelper.Updates(table, sets, strWhere));
}
/// <summary>
/// 获取系统角色
/// </summary>
/// <returns></returns>
public static DataTable GetSysRole()
{
string strSql = "select * from SysRole";
return(SQLHelper.ExecuteDataset(strSql).Tables[0]);
}
/// <summary>
/// 获取用户集合
/// </summary>
/// <param name="roleID">角色ID</param>
/// <returns></returns>
public static DataTable GetSysUser(int roleID)
{
string strSql = string.Format("select u.* from SysUser u where 1=1 and u.ID in(select r.UserID from SysRoleUser r where r.RoleID={0})", roleID);
return(SQLHelper.ExecuteDataset(strSql).Tables[0]);
}
public static void CancelPhysicalAddress(Guid ID, Guid LastModifiedBy, DateTime LastModifiedTimestamp)
{
SQLHelper.execNonQuery(ConnectionString, "CancelPhysicalAddress", ID, LastModifiedBy, LastModifiedTimestamp);
}
public object UpdatePhysicalAddress()
{
return(SQLHelper.SaveAndReturn(ConnectionString, "UpdatePhysicalAddress", this));
}
public object InsertPhysicalAdddress()
{
return(SQLHelper.SaveAndReturn(ConnectionString, "AddPhysicalAddress", this));
}
/// <summary>
/// 获取指定条件的记录数
/// </summary>
/// <param name="strWhere">条件</param>
/// <returns>记录总数</returns>
public int Count(string strWhere)
{
return(SQLHelper.Count(table, strWhere));
}
/// <summary>
/// 是否存在该记录
/// </summary>
/// <param name="strWhere">条件</param>
/// <returns>是否存在该记录</returns>
public bool Exists(string strWhere)
{
return(Convert.ToInt32(SQLHelper.Count(table, strWhere)) > 0);
}
/// <summary>
/// 是否存在该记录
/// </summary>
/// <param name="ID">主键ID</param>
/// <returns>是否存在该记录</returns>
public bool Exist(int ID)
{
return(Convert.ToInt32(SQLHelper.Count(table, string.Format("{0}='{1}'", pk, ID))) > 0);
}
/// <summary>
/// 按条件删除表中的数据
/// </summary>
/// <param name="strWhere">条件</param>
/// <returns>影响行数</returns>
public int Deletes(string strWhere)
{
return(SQLHelper.Deletes(table, strWhere));
}
/// <summary>
/// 获得数据列表
/// </summary>
/// <param name="strWhere">条件</param>
/// <param name="orderBy">排序</param>
/// <returns>数据表</returns>
public DataTable GetData(string strWhere, string orderBy)
{
return(SQLHelper.GetData("*", table, strWhere, getOrder(orderBy)));
}
public void ProcessRequest(HttpContext context)
{
try
{
context.Response.ContentType = "text/plain";
string EId = HttpContext.Current.Request.Params["eid"];
string EType = HttpContext.Current.Request.Params["etype"];
string DTSTART = HttpContext.Current.Request.Params["dtstart"];
string DTEND = HttpContext.Current.Request.Params["dtend"];
DateTime dtbeginx, dtendx;
dtbeginx = DateTime.Parse(DTSTART);
dtendx = DateTime.Parse(DTEND);
//通过Cycletime 获取产量
//DataSet dsProduction=SQLHelper.GetDataSet(string.Format)
string sql = "";
if (EType == "1")//线体
{
sql = string.Format(@"select
a.starttime,isnull(a.endtime,N'{1}') endtime, b.ID,
b.EquipmentName,a.FaultType from FaultInfo(nolock) a
JOIN EquipmentData(nolock) b on a.parentid=b.ID and a.FaultType<>5 and b.ParentId=N'{2}'
where StartTime>=N'{0}' AND StartTime<=N'{1}'; --调试时候注释
SELECT a.EId,a.StartTime,a.EndTime,b.ID,b.ParentId,DATEDIFF(minute,starttime,isnull(endtime,getdate())) as cycletime FROM CycleTimeInfo(NOLOCK) a
join EquipmentData(nolock) b on a.ParentId=b.ID and a.IsPayPoint=1 and b.ParentId=N'{2}'
where a.EndTime>=N'{0}' and a.EndTime<N'{1}';
", dtbeginx.ToString("yyyy-MM-dd HH:mm:ss"), dtendx.ToString("yyyy-MM-dd HH:mm:ss"), EId);
}
else if (EType == "2")//站点
{
sql = string.Format(@"select
a.starttime,isnull(a.endtime,N'{1}') endtime, b.ID,
b.EquipmentName,a.FaultType from FaultInfo(nolock) a
JOIN EquipmentData(nolock) b on a.parentid=b.ID and a.FaultType<>5 and b.ID=N'{2}'
where StartTime>=N'{0}' AND StartTime<=N'{1}'; --调试时候注释
SELECT a.EId,a.StartTime,a.EndTime,b.ID,b.ParentId,DATEDIFF(minute,starttime,isnull(endtime,getdate())) as cycletime FROM CycleTimeInfo(NOLOCK) a
join EquipmentData(nolock) b on a.ParentId=b.ID and a.IsPayPoint=1 and b.ID=N'{2}'
where a.EndTime>=N'{0}' and a.EndTime<N'{1}';
", dtbeginx.ToString("yyyy-MM-dd HH:mm:ss"), dtendx.ToString("yyyy-MM-dd HH:mm:ss"), EId);
}
else if (EType == "3")//设备
{
sql = string.Format(@"select
a.starttime,isnull(a.endtime,N'{1}') endtime, b.ID,
b.EquipmentName,a.FaultType from FaultInfo(nolock) a
JOIN EquipmentData(nolock) b on a.StationId=b.ID and a.FaultType<>5 and b.ID=N'{2}'
where StartTime>=N'{0}' AND StartTime<=N'{1}'; --调试时候注释
SELECT a.EId,a.StartTime,a.EndTime,b.ID,b.ParentId,DATEDIFF(minute,starttime,isnull(endtime,getdate())) as cycletime FROM CycleTimeInfo(NOLOCK) a
join EquipmentData(nolock) b on a.EId=b.ID and a.IsPayPoint=1 and b.ID=N'{2}'
where a.EndTime>=N'{0}' and a.EndTime<N'{1}';
", dtbeginx.ToString("yyyy-MM-dd HH:mm:ss"), dtendx.ToString("yyyy-MM-dd HH:mm:ss"), EId);
}
if (sql != "")
{
DataSet ds = SQLHelper.GetDataSet(sql);
DataTable dtresult = new DataTable();
dtresult.Columns.Add("ymd");
dtresult.Columns.Add("hour");
dtresult.Columns.Add("countproduction");
dtresult.Columns.Add("countfault2"); //故障
dtresult.Columns.Add("countfault3"); //堵塞
dtresult.Columns.Add("countfault4"); //缺件
dtresult.Columns.Add("countfaulttime"); //故障次数
dtresult.Columns.Add("cycletimeAVG"); //平均CycleTime
DateTime dtfor = dtbeginx;
DateTime dtendfor;
for (int i = 0; dtfor < dtendx; i++)
{
dtfor = DateTime.Parse(dtfor.ToString("yyyy-MM-dd HH") + ":00:00");
dtendfor = DateTime.Parse(dtfor.ToString("yyyy-MM-dd HH") + ":59:59");
DataRow dr = dtresult.NewRow();
//获取年月日
dr["ymd"] = dtfor.ToString("yyyy-MM-dd");
//获取小时
dr["hour"] = dtfor.ToString("HH");
//获取此小时内生产数量
DataRow[] x = ds.Tables[1].Select("EndTime>='" + dtfor + "' and EndTime<='" + dtendfor + "'");
dr["countproduction"] = x.Length == 0 ? "" : x.Length.ToString();
//获取此小时内的平均CycleTime
if (x.Length > 0)
{
double totaltime = 0;
for (int k = 0; k < x.Length; k++)
{
totaltime += double.Parse(x[k]["cycletime"].ToString());
}
dr["cycletimeAVG"] = (totaltime / x.Length).ToString("f0");
}
else
{
dr["cycletimeAVG"] = "";
}
//获取此小时内故障
DataRow[] y = ds.Tables[0].Select(@"(starttime>='" + dtfor.ToString("yyyy-MM-dd HH:mm:ss") + "' and endtime<='" + dtendfor.ToString("yyyy-MM-dd HH:mm:ss") + "') " + //时间段落在查询时间段内的部分
"or (starttime>='" + dtfor.ToString("yyyy-MM-dd HH:mm:ss") + "' and (endtime is null)) " + //时间段在后部交叉部分
"or (starttime<'" + dtfor.ToString("yyyy-MM-dd HH:mm:ss") + "' and endtime<'" + dtendfor.ToString("yyyy-MM-dd HH:mm:ss") + "' and endtime>'" + dtfor.ToString("yyyy-MM-dd HH:mm:ss") + "' )"); //时间段在前段交叉部分
//获取故障次数
dr["countfaulttime"] = y.Length == 0 ? "" : y.Length.ToString();
//获取故障明细
double total2 = 0;
double total3 = 0;
double total4 = 0;
for (int j = 0; j < y.Length; j++)
{
DateTime dx2s = dtfor;
DateTime dx2e = dtendfor;
if (dx2s <= DateTime.Parse(y[j]["starttime"].ToString()))
//统计时间段开始时间小于故障开始时间，那么按照故障开始时间算，否则按照时间段开始时间算
{
dx2s = DateTime.Parse(y[j]["starttime"].ToString());
}
if (dx2e >= DateTime.Parse(y[j]["endtime"].ToString()))
//统计时间段结束时间大于故障结束时间，那么按照故障结束时间算，否则按照时间段结束时间算
{
dx2e = DateTime.Parse(y[j]["endtime"].ToString());
}
//故障
if (y[j]["FaultType"].ToString() == "2")
{
total2 += (dx2e - dx2s).TotalSeconds;
}
else if (y[j]["FaultType"].ToString() == "3")
{
total3 += (dx2e - dx2s).TotalSeconds;
}
else if (y[j]["FaultType"].ToString() == "4")
{
total4 += (dx2e - dx2s).TotalSeconds;
}
}
dr["countfault2"] = total2 == 0 ? "" : total2.ToString("f0");
dr["countfault3"] = total3 == 0 ? "" : total3.ToString("f0");
dr["countfault4"] = total4 == 0 ? "" : total4.ToString("f0");
dtresult.Rows.Add(dr);
dtresult.AcceptChanges();
dtfor = dtfor.AddHours(1);
}
string result = JsonConvert.SerializeObject(dtresult, new DataTableConverter());
HttpContext.Current.Response.Write(result);
}
}
catch (Exception ex)
{ }
}
public static DataTable GetPhysicalAddresForEdit(Guid ShedID, string AddressName)
{
return(SQLHelper.getDataTable(ConnectionString, "GetPhysicalAddresForEdit", ShedID, AddressName));
}
/// <summary>
/// 获取用户集合
/// </summary>
/// <param name="sqlWhere">查询条件</param>
/// <returns></returns>
public static DataTable GetSysUser(string sqlWhere)
{
string strSql = string.Format("select * from SysUser where 1=1 {0}", sqlWhere);
return(SQLHelper.ExecuteDataset(strSql).Tables[0]);
}
/// <summary>
/// 根据EventId 获取PrizeId
/// </summary>
/// <param name="strEventId"></param>
/// <returns></returns>
public DataSet GetPrizeByEventId(string strEventId)
{
string strSql = string.Format("SELECT TOP 1* FROM dbo.t_award_pool with(nolock) WHERE EventId='{0}' AND GETDATE()>ReleaseTime AND Balance=0 ORDER BY NEWID()", strEventId);
return(SQLHelper.ExecuteDataset(strSql));
}
/// <summary>
/// 更新多条记录
/// </summary>
/// <param name="sets">设置内容</param>
/// <param name="strWhere">条件</param>
/// <param name="primaryKeyIns">主键集合</param>
/// <returns>影响行数</returns>
public int Updates(string sets, string strWhere, string primaryKeyIns)
{
return(SQLHelper.Updates(table, sets, strWhere, pk, primaryKeyIns));
}
public void ProcessRequest(HttpContext context)
{
if (context.Request.RequestType == "GET")
{
string pid = context.Request.QueryString["pid"];
#region 判断此商品是否已经添加到购物车
string alreadybuycount = "";
if (_mss != null)
{
for (int i = 0; i < _mss.Count; i++)
{
if (_mss[i].proid == pid)
{
alreadybuycount = _mss[i].buyCount;
break;
}
}
}
#endregion 判断此商品是否已经添加到购物车
string sql = "select * from Product where ProductID=@ProductID";
SqlDataReader sdr = SQLHelper.ExecuteReader(sql, new SqlParameter("@ProductID", pid));
Product pro = new Product();
pro.ProductID = pid;
while (sdr.Read())
{
pro.ProductName = sdr["ProductName"].ToString();
pro.UnitPrice = Convert.ToDecimal(sdr["UnitPrice"]);
pro.ImagePath = "../Images/" + sdr["ImagePath"];
context.Session["pid"] = pid;
context.Session["proname"] = pro.ProductName;
context.Session["proprice"] = pro.UnitPrice;
}
string html = File.ReadAllText(context.Server.MapPath("~/htmls/DetailGoods.html"));
html = html.Replace("{产品图片}", $"<img src='{pro.ImagePath}' width='300px' height='300px'>");
html = html.Replace("{产品ID}", pro.ProductID);
html = html.Replace("{产品名称}", pro.ProductName);
html = html.Replace("{产品单价}", pro.UnitPrice.ToString());
html = html.Replace("{联系电话}", "<input typr='text' name='userphone'/>");
html = html.Replace("{送货地址}", "<input type='text' name='useraddress'/>");
string bcount = "0";
for (int i = 1; i < 51; i++)
{
bcount += string.Format("<option value='{0}'>{0}</option>", i);
}
html = html.Replace("{购买数量}", bcount);
if (alreadybuycount != "")
{
html = html.Replace("{已购买数量}", "<p>已购买:" + alreadybuycount + "件</p>");
}
else if (alreadybuycount == "")
{
html = html.Replace("{已购买数量}", "");
}
context.Response.Write(html);
}
else if (context.Request.RequestType == "POST")
{
if (context.Session["sadmin"] != null && context.Session["spwd"] != null)
{
string proid = context.Session["pid"].ToString();
string proname = context.Session["proname"].ToString();
decimal proprice = Convert.ToDecimal(context.Session["proprice"]);
string userphone = context.Request.Form["userphone"];
string useraddress = context.Request.Form["useraddress"];
string buyCount = context.Request.Form["buyCount"];
if (_mss == null)
{
_mss = new List <Shopping>();
#region 添加首个商品
Shopping ms = new Shopping();
ms.sadmin = context.Session["sadmin"].ToString();
ms.proid = proid;
ms.proname = proname;
ms.proprice = proprice;
ms.userphone = userphone;
ms.useraddress = useraddress;
ms.buyCount = buyCount;
_mss.Add(ms);
#endregion 添加首个商品
}
else if (_mss != null)
{
foreach (Shopping ims in _mss)
{
if (ims.proid != proid)//如果此商品ID不存在
{
Shopping ms = new Shopping();
ms.sadmin = context.Session["sadmin"].ToString();
ms.proid = proid;
ms.proname = proname;
ms.proprice = proprice;
ms.userphone = userphone;
ms.useraddress = useraddress;
ms.buyCount = buyCount;
_mss.Add(ms);
break;
}
else//如果商品已购买过,则增加数量,(修改),(添加)新的联系电话,和送货地址
{
int ibuyCount = Convert.ToInt32(ims.buyCount);
ibuyCount += Convert.ToInt32(buyCount);
buyCount = ibuyCount.ToString();
ims.buyCount = buyCount;
ims.userphone = userphone;
ims.useraddress = useraddress;
break;
}
}
}
context.Response.Redirect("~/SubOrder");
}
else
{
context.Response.Redirect("/Htmls/Index.html");
}
}
}
/// <summary>
/// 启动服务器
/// </summary>
public void RunWCFService()
{
try
{
string sqlText = "SELECT * FROM Frame_RemotingObject";
SqlParameter[] parms = null;
using (DataSet ds = SQLHelper.ExecuteDataSet(SQLHelper.SchuleConnection, CommandType.Text, sqlText, parms))
{
if (ds.Tables.Count > 0)
{
DataTable dt = ds.Tables[0];
//Console.WriteLine("Beginning Run WCF Service.");
Log.getInstance().Write(MsgType.Information, "Beginning Run WCF Service");
if (dt != null)
{
//Console.WriteLine("Service count :{0}", dt.Rows.Count);
Log.getInstance().Write(MsgType.Information, string.Format("Service count :{0}", dt.Rows.Count));
foreach (DataRow r in dt.Rows)
{
try
{
Type _t = getType(r["objecttype"].ToString(), r["objectDll"].ToString().Trim());
string[] types = r["interfaceType"].ToString().Split(new char[] { '|' });
Type[] listTypes = new Type[types.Length];
for (int ii = 0; ii < listTypes.Length; ii++)
{
Type _interface = getType(types[ii], r["interfaceDll"].ToString().Trim());
listTypes[ii] = _interface;
}
if (_t == null || r["uri"].ToString().Trim() == "" || listTypes.Length == 0)
{
Console.WriteLine(string.Format("SERVICE {0}-{1}注册失败。", r["objectDll"].ToString(), r["objecttype"].ToString()));
Log.getInstance().Write(MsgType.Error, string.Format("SERVICE {0}-{1}注册失败。", r["objectDll"].ToString(), r["objecttype"].ToString()));
continue;
}
this.RunWCF(_t, listTypes, r["uri"].ToString().Trim());
}
catch (Exception e)
{
Log.getInstance().Write(MsgType.Error, string.Format("SERVICE {0}-{1}注册失败。", r["objectDll"].ToString(), r["objecttype"].ToString()));
Log.getInstance().Write(e, MsgType.Error);
//Console.WriteLine(string.Format("SERVICE {0}-{1}注册失败。", r["objectDll"].ToString(), r["objecttype"].ToString()));
// Console.WriteLine("{0} {1} 原因：{2}",r["objecttype"].ToString(), r["objectDll"].ToString(),e.Message);
}
}
}
else
{
//Console.WriteLine("dt == null RunRemotingService.");
Log.getInstance().Write(MsgType.Information, "Frame_RemotingObject==null");
}
Console.WriteLine("End Run WCF Service.");
Log.getInstance().Write(MsgType.Information, "End Run WCF Service.");
}
}
}
catch (Exception ex)
{
string result = ex.Message;
//加载数据错误。//在此需要记录系统日志。
Console.WriteLine(result);
Log.getInstance().Write(ex, MsgType.Error);
}
}
public int UpdateSortPlusByParentMaxSort(string parentId, int maxSort)
{
SQLHelper helper = new SQLHelper("update XT_MENU set SORT=SORT+1 where PARENT_ID=@0 and SORT>@1", parentId, maxSort);
return(db.ExecuteNonQuery(helper));
}
/// <summary>
///
/// </summary>
private void SaveNew(bool isWuHuan)
{
if (string.IsNullOrEmpty(this.CheckWorkId))
{
Model.Check_CheckWork checkWork = new Model.Check_CheckWork
{
CheckWorkId = SQLHelper.GetNewID(typeof(Model.Check_CheckWork)),
CheckWorkCode = this.txtCheckWorkCode.Text.Trim(),
CheckTime = Funs.GetNewDateTime(this.txtCheckDate.Text.Trim()),
ProjectId = this.ProjectId,
Area = this.txtArea.Text.Trim()
};
if (!string.IsNullOrEmpty(this.drpThisUnit.SelectedValue))
{
checkWork.ThisUnitId = this.drpThisUnit.SelectedValue;
}
//本单位人员
string mainUnitPerson = string.Empty;
foreach (var item in this.drpMainUnitPerson.SelectedValueArray)
{
mainUnitPerson += item + ",";
}
if (!string.IsNullOrEmpty(mainUnitPerson))
{
mainUnitPerson = mainUnitPerson.Substring(0, mainUnitPerson.LastIndexOf(","));
}
checkWork.MainUnitPerson = mainUnitPerson;
//参与单位
string subUnits = string.Empty;
foreach (var item in this.drpSubUnits.SelectedValueArray)
{
subUnits += item + ",";
}
if (!string.IsNullOrEmpty(subUnits))
{
checkWork.SubUnits = subUnits.Substring(0, subUnits.LastIndexOf(","));
}
//参与用户
string subUnitPerson = string.Empty;
foreach (var item in this.drpSubUnitPerson.SelectedValueArray)
{
if (item != BLL.Const._Null)
{
subUnitPerson += item + ",";
}
}
if (!string.IsNullOrEmpty(subUnitPerson))
{
checkWork.SubUnitPerson = subUnitPerson.Substring(0, subUnitPerson.LastIndexOf(","));
}
//if (this.ckbIsCompleted.Checked)
//{
// checkWork.IsCompleted = true;
//}
checkWork.PartInPersonNames = this.txtPartInPersonNames.Text.Trim();
checkWork.MainUnitDeputy = this.txtMainUnitDeputy.Text.Trim();
checkWork.MainUnitDeputyDate = Funs.GetNewDateTime(this.txtMainUnitDeputyDate.Text.Trim());
checkWork.SubUnitDeputy = this.txtSubUnitDeputy.Text.Trim();
checkWork.SubUnitDeputyDate = Funs.GetNewDateTime(this.txtSubUnitDeputyDate.Text.Trim());
checkWork.CompileMan = this.CurrUser.UserId;
////单据状态
checkWork.States = BLL.Const.State_0;
this.CheckWorkId = checkWork.CheckWorkId;
if (this.ckIsAgree.Checked)
{
checkWork.IsAgree = true;
}
BLL.Check_CheckWorkService.AddCheckWork(checkWork);
BLL.LogService.AddSys_Log(this.CurrUser, checkWork.CheckWorkCode, checkWork.CheckWorkId, BLL.Const.ProjectCheckWorkMenuId, BLL.Const.BtnAdd);
if (isWuHuan)
{
checkWorkDetails = (from x in Funs.DB.Check_ProjectCheckItemDetail
join y in Funs.DB.Check_ProjectCheckItemSet on x.CheckItemSetId equals y.CheckItemSetId
where y.ProjectId == this.ProjectId && y.CheckType == "4"
orderby y.SortIndex
select new Model.View_Check_CheckWorkDetail
{
CheckWorkId = this.CheckWorkId,
CheckItem = y.CheckItemSetId,
CheckContent = x.CheckContent,
CheckItemStr = x.CheckContent,
SortIndex = x.SortIndex,
CheckResult = "合格",
}).ToList();
foreach (var item in checkWorkDetails)
{
Model.Check_CheckWorkDetail detail = new Model.Check_CheckWorkDetail
{
CheckWorkDetailId = SQLHelper.GetNewID(typeof(Model.Check_CheckWorkDetail)),
CheckWorkId = item.CheckWorkId,
CheckItem = item.CheckItem,
CheckContent = item.CheckContent,
SortIndex = item.SortIndex,
CheckResult = item.CheckResult,
};
BLL.Check_CheckWorkDetailService.AddCheckWorkDetail(detail);
}
}
}
}
/// <summary>
/// 根据父级编号查询指定序号的记录
/// </summary>
/// <param name="parentId"></param>
/// <param name="targetSort"></param>
/// <returns></returns>
public XT_MENU SelectByParentIdAndSort(string parentId, int targetSort)
{
SQLHelper helper = new SQLHelper("select * from XT_MENU where PARENT_ID=@0,SORT=@1", parentId, targetSort);
return(SelectFirstOrDefault(helper));
}
public static DataTable GetGradingResultStatusForFactorGroup()
{
DataTable dt = SQLHelper.getDataTable(ConnectionString, "GetGradingResultStatusForFactorGroup");
return(dt);
}
/// <summary>
/// 历史数据查询方法
/// </summary>
/// <param name="start"></param>
/// <param name="end"></param>
private void QueryProcess(string start, string end)
{
if (listParameter.Count == 0)
{
MessageBox.Show("至少要选择一个参数！", "查询提示");
return;
}
//对于参数进行一定的判断
DateTime t1 = Convert.ToDateTime(start);
DateTime t2 = Convert.ToDateTime(end);
if (t1 > t2)
{
MessageBox.Show("开始时间不能大于结束时间！", "查询提示");
return;
}
TimeSpan ts = t2 - t1;
if (ts.TotalHours > 10.0)
{
MessageBox.Show("查询间隔范围太大【10H】！", "查询提示");
return;
}
//拼接SQL语句
StringBuilder sb = new StringBuilder();
sb.Append("select InsertTime,");
string str = string.Join(",", listParameter.ToArray());
sb.Append(str);
sb.Append(" from TemperaTureMeasure where 1=1 and InsertTime between '{0}' and '{1}'");
string sql = string.Format(sb.ToString(), start, end);
DataTable dt = SQLHelper.GetDataSet(sql).Tables[0];
int rowcount = dt.Rows.Count;
if (rowcount > 0)
{
try
{
Invoke(new Action(() =>
{
this.chartHistory.Clear();
this.chartHistory.SeriesCount = listParameter.Count;
for (int i = 0; i < sqlArray.Length; i++)
{
this.chartHistory.Series[i].Name = sqlArray[i];
}
//DataTable转换为需要的类型
double[,] YData = new double[listParameter.Count, rowcount];
XData = new string[rowcount];
for (int i = 0; i < rowcount; i++)
{
for (int j = 0; j < listParameter.Count; j++)
{
if (dt.Rows[i][j + 1] is DBNull)
{
YData[j, i] = 0.0;
}
else
{
try
{
YData[j, i] = Convert.ToDouble(dt.Rows[i][j + 1]);
}
catch (Exception)
{
YData[j, i] = 0.0;
}
}
}
XData[i] = Convert.ToDateTime(dt.Rows[i][0]).ToString("yyyy/MM/dd HH:mm:ss");
}
this.chartHistory.Plot(YData, XData);
LogHelper.WriteLog($"历史曲线生成成功");
}));
}
catch (Exception ex)
{
LogHelper.WriteLog($"历史曲线生成失败", ex);
MessageBox.Show(ex.Message, "查询提示");
}
}
else
{
MessageBox.Show("未查询到数据，请检查！", "查询提示");
return;
}
}
public JsonResult GetUserRelationList(QueryBuilder qb)
{
if (string.IsNullOrEmpty(Request["NodeFullID"]))
{
return(Json(""));
}
string resID = Request["NodeFullID"].Split('.').Last();
string gridRowID = Request["GridRowID"];
string sql = @"
--继承自组织的角色
select S_A_User.* from S_A__RoleRes
join S_A__OrgRole on S_A__RoleRes.RoleID=S_A__OrgRole.RoleID
join S_A__OrgUser on S_A__OrgUser.OrgID=S_A__OrgRole.OrgID
join S_A_User on UserID=ID
join S_A_Org on S_A_Org.ID=S_A__OrgRole.OrgID
where (S_A_Org.FullID like '%'+ S_A_User.DeptID +'%' or S_A_Org.FullID like '%'+ S_A_User.PrjID +'%') and ResID='{0}' {1}
union
--组织角色
select S_A_User.* from S_A__RoleRes
join S_A__OrgRoleUser on S_A__OrgRoleUser.RoleID=S_A__RoleRes.RoleID
join S_A_User on S_A_User.ID=S_A__OrgRoleUser.UserID
join S_A_Org on S_A_Org.ID=S_A__OrgRoleUser.OrgID
where (S_A_Org.FullID like '%'+S_A_User.DeptID+'%' or S_A_Org.FullID like '%' + S_A_User.PrjID+'%') and S_A__RoleRes.ResID='{0}' {1}
union
--系统角色
select S_A_User.* from S_A__RoleRes
join S_A__RoleUser on S_A__RoleRes.RoleID=S_A__RoleUser.RoleID
join S_A_User on UserID = ID
where ResID='{0}' {2}
union
--组织
select S_A_User.* from S_A__OrgRes
join S_A__OrgUser on S_A__OrgRes.OrgID=S_A__OrgUser.OrgID
join S_A_User on UserID=ID
where ResID='{0}' {3}
union
--用户
select S_A_User.* from S_A__UserRes
join S_A_User on UserID=ID
where ResID='{0}' {4}
";
if (string.IsNullOrEmpty(gridRowID))
{
sql = string.Format(sql, resID, "", "", "", "");
}
else
{
sql = string.Format(sql
, resID
, string.Format("and S_A__RoleRes.RoleID='{0}'", gridRowID)
, string.Format("and S_A__RoleRes.RoleID='{0}'", gridRowID)
, string.Format("and S_A__OrgRes.OrgID='{0}'", gridRowID)
, string.Format("and S_A__UserRes.UserID='{0}'", gridRowID)
);
}
SQLHelper sqlHelper = SQLHelper.CreateSqlHelper("Base");
return(Json(sqlHelper.ExecuteGridData(sql, qb)));
}
public List <UserT> SelectAllUsers()
{
string sql = SQLHelper.GetSqlByMethodName(MethodBase.GetCurrentMethod().Name);
return(Connection.Fetch <UserT>(sql));
}
/// <summary>
/// 得到最大ID
/// </summary>
/// <returns>最大ID</returns>
public int GetMaxId()
{
object o = SQLHelper.Max(table, pk, "1=1");
return((o == DBNull.Value) ? 1 : Convert.ToInt32(o) + 1);
}
/// <summary>
/// 按条件删除指定主键集合的数据
/// </summary>
/// <param name="strWhere">条件</param>
/// <param name="primaryKeyIns">主键集合</param>
/// <returns>影响行数</returns>
public int Deletes(string strWhere, string primaryKeyIns)
{
return(SQLHelper.Deletes(table, strWhere, pk, primaryKeyIns));
}
