// protected override void OnActionExecuting(ActionExecutingContext filterContext) // { // var sessionUser = (LoginModel) Session[CommonConstants.UserSession]; // if (sessionUser == null) // { // var sessionKey = filterContext.RouteData.Values["ssk"]; // // if (sessionKey == null || sessionKey == "") // { // filterContext.Result = new RedirectToRouteResult(new // RouteValueDictionary(new {controller = "Login", action = "Index"})); // } // else // { // LoginModel loginModel = new LoginModel(); // string sql = "select s.session_key, s.username from sessions s where s.session_key = :0"; // List<LoginModel> lstUser = // OracleHelper.ExecuteCommandText<LoginModel>(ConnectionString, sql, sessionKey); // if (lstUser.Count > 0) // { // loginModel.SessionKey = lstUser[0].SessionKey; // loginModel.Username = lstUser[0].Username; // Session.Add(CommonConstants.UserSession, loginModel); // } // else // { // filterContext.Result = new RedirectToRouteResult(new // RouteValueDictionary(new {controller = "Login", action = "Index"})); // } // } // } // // base.OnActionExecuting(filterContext); // } protected override void OnAuthorization(AuthorizationContext filterContext) { var sessionUser = (LoginModel)Session[CommonConstants.UserSession]; var sessionKey = filterContext.RouteData.Values["ssk"]; if (sessionUser == null) { if (sessionKey == null || sessionKey == "") { filterContext.Result = new RedirectToRouteResult(new RouteValueDictionary(new { controller = "Login", action = "Index" })); } else { LoginModel loginModel = new LoginModel(); string sql = "select s.session_key, s.username from sessions s where s.session_key = :0"; List <LoginModel> lstUser = OracleHelper.ExecuteCommandText <LoginModel>(ConnectionString, sql, sessionKey); if (lstUser.Count > 0) { loginModel.SessionKey = lstUser[0].SessionKey; loginModel.Username = lstUser[0].Username; Session.Add(CommonConstants.UserSession, loginModel); } else { filterContext.Result = new RedirectToRouteResult(new RouteValueDictionary(new { controller = "Login", action = "Index" })); } } } else { var ssk = sessionKey != null ? sessionKey : sessionUser.SessionKey; if (sessionKey != null && sessionUser.SessionKey != sessionKey) { Session[CommonConstants.UserSession] = null; LoginModel loginModel = new LoginModel(); string sql = "select s.session_key, s.username from sessions s where s.session_key = :0"; List <LoginModel> lstUser = OracleHelper.ExecuteCommandText <LoginModel>(ConnectionString, sql, sessionKey); if (lstUser.Count > 0) { loginModel.SessionKey = lstUser[0].SessionKey; loginModel.Username = lstUser[0].Username; Session.Add(CommonConstants.UserSession, loginModel); } } string sqlGetRoleType = "select\r\n ur.ROLETYPE_LIST || ',' || (select dr.ROLETYPE_LIST from apec_docs_department_role dr where dr.DEPARTMENT_ID = sd.dep_id) ROLETYPE_LIST\r\n from sessions s\r\n left join users u on s.user_id = u.user_id\r\n left join apec_docs_user_role ur on u.staff_id = ur.staff_id\r\n left join apec_staff_deparment sd on u.staff_id = sd.staff_id\r\n where s.session_key = :0 and sd.dep_id is not null"; List <RoleType> lstRoleType = OracleHelper.ExecuteCommandText <RoleType>(ConnectionString, sqlGetRoleType, ssk); var strAccessAction = lstRoleType.Count > 0 ? lstRoleType[0].RoleTypeString : ""; var strAction = filterContext.HttpContext.Request.Url.ToString(); var strActionNumber = ""; if (strAction.Contains("Create") || strAction.Contains("create")) { strActionNumber = "1"; } if (strAction.Contains("Edit") || strAction.Contains("Edit")) { strActionNumber = "2"; } if (strAction.Contains("DownloadDoc") || strAction.Contains("DownloadDocZip") || strAction.Contains("downloadDoc") || strAction.Contains("downloadDocZip") || strAction.Contains("UploadedFiles")) { strActionNumber = "5"; } if (!strAccessAction.Contains(strActionNumber)) { filterContext.Result = new RedirectToRouteResult(new RouteValueDictionary(new { controller = "Home", action = "CheckPermission" })); } } base.OnAuthorization(filterContext); }
// 查看详细 private void gvCLJBXX_MouseDoubleClick(object sender, MouseEventArgs e) { ColumnView cv = (ColumnView)gcCLJBXX.FocusedView; DataRowView dr = (DataRowView)cv.GetFocusedRow(); if (dr == null) { return; } string vin = (string)dr.Row.ItemArray[0]; // 获取此VIN的详细信息,带入窗口 string sql = String.Format(@"select * from FC_CLJBXX where vin = '{0}'", vin); // 获取燃料信息 string rlsql = String.Format(@"select e.* from RLLX_PARAM_ENTITY e where e.vin = '{0}'", vin); DataTable dtJbxx = OracleHelper.ExecuteDataSet(OracleHelper.conn, sql, null).Tables[0]; DataTable dtRlxx = OracleHelper.ExecuteDataSet(OracleHelper.conn, rlsql, null).Tables[0]; // 弹出详细信息窗口,可修改 JbxxViewForm jvf = new JbxxViewForm("UPLOADOT") { status = "1" }; if (dtJbxx.Rows.Count > 0) { for (int i = 0; i < dtJbxx.Columns.Count; i++) { DataColumn dc = dtJbxx.Columns[i]; Control[] c = jvf.Controls.Find("tb" + dc.ColumnName, true); if (c.Length > 0) { if (c[0] is TextEdit) { c[0].Text = dtJbxx.Rows[0].ItemArray[i].ToString(); continue; } if (c[0] is ComboBoxEdit) { ComboBoxEdit cb = c[0] as ComboBoxEdit; cb.Text = dtJbxx.Rows[0].ItemArray[i].ToString(); if (cb.Text == "汽油" || cb.Text == "柴油" || cb.Text == "两用燃料" || cb.Text == "双燃料" || cb.Text == "纯电动" || cb.Text == "非插电式混合动力" || cb.Text == "插电式混合动力" || cb.Text == "燃料电池") { string rlval; if (cb.Text == "汽油" || cb.Text == "柴油" || cb.Text == "两用燃料" || cb.Text == "双燃料") { rlval = "传统能源"; } else { rlval = cb.Text; } // 构建燃料参数控件 jvf.getParamList(rlval, true); } } } } } for (int i = 0; i < dtRlxx.Rows.Count; i++) { DataRow drrlxx = dtRlxx.Rows[i]; string cName = drrlxx.ItemArray[1].ToString(); Control[] c = jvf.Controls.Find(cName, true); if (c.Length > 0) { if (c[0] is TextEdit) { c[0].Text = drrlxx.ItemArray[3].ToString(); continue; } if (c[0] is ComboBoxEdit) { ComboBoxEdit cb = c[0] as ComboBoxEdit; cb.Text = drrlxx.ItemArray[3].ToString(); } } } (jvf.Controls.Find("tc", true)[0] as XtraTabControl).SelectedTabPageIndex = 0; jvf.MaximizeBox = false; jvf.MinimizeBox = false; Utils.SetFormMid(jvf); jvf.ShowDialog(); if (jvf.DialogResult == DialogResult.Cancel) { this.refrashCurrentPage(); } }
//初始化燃料参数(新的方式) private DataTable InitRLLX(string type, string vin, string strClxh, string strClzl, string strRllx, DateTime startDate, DateTime endDate) { string sqlCondition = " AND 1=1"; if (!string.IsNullOrEmpty(vin)) { sqlCondition += String.Format(" AND FC_CLJBXX.VIN='{0}'", vin.Trim()); } if (!string.IsNullOrEmpty(strClxh)) { sqlCondition += String.Format(" AND FC_CLJBXX.CLXH='{0}'", strClxh.Trim()); } if (!string.IsNullOrEmpty(strClzl)) { sqlCondition += String.Format(" AND FC_CLJBXX.CLZL='{0}'", strClzl.Trim()); } if (!string.IsNullOrEmpty(strRllx)) { sqlCondition += String.Format(" AND FC_CLJBXX.RLLX='{0}'", strRllx.Trim()); } switch (this.type) { case "1": sqlCondition += String.Format(" AND FC_CLJBXX.STATUS='{0}'", this.type); break; case "2": sqlCondition += String.Format(" AND FC_CLJBXX.STATUS='{0}'", this.type); break; case "0": sqlCondition += String.Format(" AND FC_CLJBXX.STATUS='{0}'", this.type); break; } StringBuilder sql = new StringBuilder(); sql = sql.Append("select * from "); sql.Append("( "); sql.Append(" select FC_CLJBXX.VIN,CLXH,TYMC,RLLX,CT_ZHGKRLXHL,ZCZBZL,CT_BSQXS,ZWPS "); sql.Append(" FROM FC_CLJBXX LEFT JOIN VIEW_RLLX_PARAM_ENTTITY ON FC_CLJBXX.VIN = VIEW_RLLX_PARAM_ENTTITY.VIN"); sql.AppendFormat(" WHERE (((FC_CLJBXX.RLLX)='汽油' Or (FC_CLJBXX.RLLX)='柴油' Or (FC_CLJBXX.RLLX)='两用燃料' Or (FC_CLJBXX.RLLX)='双燃料')) AND to_date(to_char(FC_CLJBXX.CLZZRQ,'yyyy/MM/dd'),'yyyy-mm-dd hh24:mi:ss') >=to_date('{0}','yyyy-mm-dd hh24:mi:ss') AND to_date(to_char(FC_CLJBXX.CLZZRQ,'yyyy/MM/dd'),'yyyy-mm-dd hh24:mi:ss') <=to_date('{1}','yyyy-mm-dd hh24:mi:ss') {2}", startDate, endDate, sqlCondition); sql.Append(" union"); sql.Append(" select FC_CLJBXX.VIN,CLXH,TYMC,RLLX,'',ZCZBZL,'',ZWPS "); sql.Append(" FROM FC_CLJBXX LEFT JOIN VIEW_RLLX_PARAM_ENTTITY_CDD ON FC_CLJBXX.VIN=VIEW_RLLX_PARAM_ENTTITY_CDD.VIN"); sql.AppendFormat(" WHERE FC_CLJBXX.RLLX='纯电动' AND to_date(to_char(FC_CLJBXX.CLZZRQ,'yyyy/MM/dd'),'yyyy-mm-dd hh24:mi:ss') >=to_date('{0}','yyyy-mm-dd hh24:mi:ss') AND to_date(to_char(FC_CLJBXX.CLZZRQ,'yyyy/MM/dd'),'yyyy-mm-dd hh24:mi:ss') <=to_date('{1}','yyyy-mm-dd hh24:mi:ss') {2}", startDate, endDate, sqlCondition); sql.Append(" union"); sql.Append(" select FC_CLJBXX.VIN,CLXH,TYMC,RLLX,FCDS_HHDL_ZHGKRLXHL,ZCZBZL,FCDS_HHDL_BSQXS,ZWPS "); sql.Append(" FROM FC_CLJBXX LEFT JOIN VIEW_RLLX_PARAM_ENTTITY_FCDS ON FC_CLJBXX.VIN=VIEW_RLLX_PARAM_ENTTITY_FCDS.VIN"); sql.AppendFormat(" WHERE FC_CLJBXX.RLLX='非插电式混合动力' AND to_date(to_char(FC_CLJBXX.CLZZRQ,'yyyy/MM/dd'),'yyyy-mm-dd hh24:mi:ss') >=to_date('{0}','yyyy-mm-dd hh24:mi:ss') AND to_date(to_char(FC_CLJBXX.CLZZRQ,'yyyy/MM/dd'),'yyyy-mm-dd hh24:mi:ss') <=to_date('{1}','yyyy-mm-dd hh24:mi:ss') {2}", startDate, endDate, sqlCondition); sql.Append(" union"); sql.Append(" select FC_CLJBXX.VIN,CLXH,TYMC,RLLX,CDS_HHDL_ZHGKRLXHL,ZCZBZL,CDS_HHDL_BSQXS,ZWPS "); sql.Append(" FROM FC_CLJBXX LEFT JOIN VIEW_RLLX_PARAM_ENTTITY_CDS ON FC_CLJBXX.VIN=VIEW_RLLX_PARAM_ENTTITY_CDS.VIN"); sql.AppendFormat(" WHERE FC_CLJBXX.RLLX='插电式混合动力' AND to_date(to_char(FC_CLJBXX.CLZZRQ,'yyyy/MM/dd'),'yyyy-mm-dd hh24:mi:ss') >=to_date('{0}','yyyy-mm-dd hh24:mi:ss') AND to_date(to_char(FC_CLJBXX.CLZZRQ,'yyyy/MM/dd'),'yyyy-mm-dd hh24:mi:ss') <=to_date('{1}','yyyy-mm-dd hh24:mi:ss') {2}", startDate, endDate, sqlCondition); sql.Append(" union"); sql.Append(" select FC_CLJBXX.VIN,CLXH,TYMC,RLLX,'',ZCZBZL,'',ZWPS "); sql.Append(" FROM FC_CLJBXX LEFT JOIN VIEW_RLLX_PARAM_ENTTITY_RLDC ON FC_CLJBXX.VIN=VIEW_RLLX_PARAM_ENTTITY_RLDC.VIN"); sql.AppendFormat(" WHERE FC_CLJBXX.RLLX='燃料电池' AND to_date(to_char(FC_CLJBXX.CLZZRQ,'yyyy/MM/dd'),'yyyy-mm-dd hh24:mi:ss') >=to_date('{0}','yyyy-mm-dd hh24:mi:ss') AND to_date(to_char(FC_CLJBXX.CLZZRQ,'yyyy/MM/dd'),'yyyy-mm-dd hh24:mi:ss') <=to_date('{1}','yyyy-mm-dd hh24:mi:ss') {2}", startDate, endDate, sqlCondition); sql.Append(") s"); DataSet ds = OracleHelper.ExecuteDataSet(OracleHelper.conn, sql.ToString(), null); if (ds != null && ds.Tables[0].Rows.Count > 0) { return(ds.Tables[0]); } return(null); }
//处理数据插入到数据库中 private bool InsertFC_CLJBXX(DataView dv, string flag, string rllxParam) { bool result = false; DataTable dtParams = OracleHelper.ExecuteDataSet(OracleHelper.conn, (string)@"select * from RLLX_PARAM", null).Tables[0]; using (OracleConnection con = new OracleConnection(OracleHelper.conn)) { con.Open(); OracleTransaction tra = null; try { tra = con.BeginTransaction(); foreach (DataRow drMain in dv.Table.Rows) { #region 待生成的燃料基本信息数据存入燃料基本信息表 string vin = drMain["VIN"].ToString().Trim(); string sqlDeleteBasic = String.Format("DELETE FROM FC_CLJBXX WHERE VIN='{0}'", vin); OracleHelper.ExecuteNonQuery(tra, sqlDeleteBasic, null); DateTime clzzrqDate; try { clzzrqDate = DateTime.ParseExact(drMain["CLZZRQ"].ToString().Trim(), "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture); } catch (Exception) { clzzrqDate = Convert.ToDateTime(drMain["CLZZRQ"]); } OracleParameter clzzrq = new OracleParameter("CLZZRQ", clzzrqDate) { DbType = DbType.Date }; DateTime uploadDeadlineDate = miutils.QueryUploadDeadLine(clzzrqDate); OracleParameter uploadDeadline = new OracleParameter("UPLOADDEADLINE", uploadDeadlineDate) { DbType = DbType.Date }; OracleParameter creTime = new OracleParameter("CREATETIME", DateTime.Now) { DbType = DbType.Date }; OracleParameter upTime = new OracleParameter("UPDATETIME", DateTime.Now) { DbType = DbType.Date }; string qtxx; if (dv.Table.Columns.Contains("CT_QTXX")) { qtxx = drMain["CT_QTXX"].ToString().Trim(); } else { qtxx = string.Empty; } OracleParameter[] param = { new OracleParameter("VIN", drMain["VIN"].ToString().Trim()), new OracleParameter("USER_ID", Utils.localUserId), new OracleParameter("QCSCQY", drMain["QCSCQY"].ToString().Trim()), new OracleParameter("JKQCZJXS", drMain["JKQCZJXS"].ToString().Trim()), clzzrq, uploadDeadline, new OracleParameter("CLXH", drMain["CLXH"].ToString().Trim()), new OracleParameter("CLZL", drMain["CLZL"].ToString().Trim()), new OracleParameter("RLLX", drMain["RLLX"].ToString().Trim()), new OracleParameter("ZCZBZL", drMain["ZCZBZL"].ToString().Trim()), new OracleParameter("ZGCS", drMain["ZGCS"].ToString().Trim()), new OracleParameter("LTGG", drMain["LTGG"].ToString().Trim()), new OracleParameter("ZJ", drMain["ZJ"].ToString().Trim()), new OracleParameter("TYMC", drMain["TYMC"].ToString().Trim()), new OracleParameter("YYC", drMain["YYC"].ToString().Trim()), new OracleParameter("ZWPS", drMain["ZWPS"].ToString().Trim()), new OracleParameter("ZDSJZZL", drMain["ZDSJZZL"].ToString().Trim()), new OracleParameter("EDZK", drMain["EDZK"].ToString().Trim()), new OracleParameter("LJ", drMain["LJ"].ToString().Trim()), new OracleParameter("QDXS", drMain["QDXS"].ToString().Trim()), new OracleParameter("JYJGMC", drMain["JYJGMC"].ToString().Trim()), new OracleParameter("JYBGBH", drMain["JYBGBH"].ToString().Trim()), new OracleParameter("HGSPBM", drMain["HGSPBM"].ToString().Trim()), new OracleParameter("QTXX", qtxx), // 0:已上报;1:待上报/补传待上报;2:已修改未上报 new OracleParameter("STATUS", flag), creTime, upTime, }; OracleHelper.ExecuteNonQuery(tra, (string)@"INSERT INTO FC_CLJBXX ( VIN,USER_ID,QCSCQY,JKQCZJXS,CLZZRQ,UPLOADDEADLINE,CLXH,CLZL, RLLX,ZCZBZL,ZGCS,LTGG,ZJ, TYMC,YYC,ZWPS,ZDSJZZL,EDZK,LJ, QDXS,JYJGMC,JYBGBH,HGSPBM,QTXX,STATUS,CREATETIME,UPDATETIME ) VALUES ( :VIN,:USER_ID,:QCSCQY,:JKQCZJXS,:CLZZRQ,:UPLOADDEADLINE,:CLXH,:CLZL, :RLLX,:ZCZBZL,:ZGCS,:LTGG,:ZJ, :TYMC,:YYC,:ZWPS,:ZDSJZZL,:EDZK,:LJ, :QDXS,:JYJGMC,:JYBGBH,:HGSPBM,:QTXX,:STATUS,:CREATETIME,:UPDATETIME)", param); #endregion #region 插入参数信息 string sqlDelParam = String.Format("DELETE FROM RLLX_PARAM_ENTITY WHERE VIN ='{0}'", drMain["VIN"].ToString().Trim()); OracleHelper.ExecuteNonQuery(tra, sqlDelParam, null); var rows = dtParams.Select(String.Format("FUEL_TYPE='{0}' and STATUS='1'", rllxParam)); // 待生成的燃料参数信息存入燃料参数表 foreach (DataRow drParam in rows) { string paramCode = drParam["PARAM_CODE"].ToString().Trim(); OracleParameter[] paramList = { new OracleParameter("PARAM_CODE", paramCode), new OracleParameter("VIN", drMain["VIN"].ToString().Trim()), new OracleParameter("PARAM_VALUE", drMain[paramCode].ToString().Trim()), new OracleParameter("V_ID", "") }; OracleHelper.ExecuteNonQuery(tra, (string)@"INSERT INTO RLLX_PARAM_ENTITY (PARAM_CODE,VIN,PARAM_VALUE,V_ID) VALUES (:PARAM_CODE,:VIN,:PARAM_VALUE,:V_ID)", paramList); } #endregion } tra.Commit(); result = true; } catch (Exception ex) { tra.Rollback(); throw ex; } finally { tra.Dispose(); con.Close(); } } return(result); }
/// <summary> /// 创建数据库访问实例 /// </summary> /// <param name="dbtype"></param> public void Init(string connstr) { helper = new OracleHelper(connstr); }
public static void ClassCleanup() { OracleHelper.DropTable(ConnectionString, TableName); }
/// <summary> /// 导出到Excel /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnExport_Click(object sender, EventArgs e) { OracleHelper oH = OracleHelper.getBaseDao(); if (string.IsNullOrEmpty(cbPN.Text.Trim())) { return; } if (CmdHelper.ifExistsTheProcessByName("EXCEL")) { return; } //获取该成品对应的工序。 System.Data.DataTable dt = null; OracleParameter paramPN = new OracleParameter("v_PN", OracleDbType.NVarchar2, 20); OracleParameter paramCur = new OracleParameter("retureCur", OracleDbType.RefCursor); paramPN.Direction = ParameterDirection.Input; paramCur.Direction = ParameterDirection.ReturnValue; paramPN.Value = cbPN.Text.Trim(); OracleParameter[] parameters = new OracleParameter[2] { paramCur, paramPN }; string procName = "Show_Products_Cost.get_P_C_Info_To_Piecework"; dt = oH.getDT(procName, parameters); _fileName = cbPN.Text.Trim() + ".xls"; DirectoryHelper.createDirecotry(_defaultDir); xlsFilePath = _defaultDir + _fileName; //获取一个Excel V_New_Excel v_Excel = new V_New_Excel(xlsFilePath); Worksheet wS = v_Excel.WS; Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(wS); uEHelper.setSpecificCellValue(1, 1, cbPN.Text.Trim() + "报工单", 11, true); //写序号标题 uEHelper.setSpecificCellValue(2, 3, "姓名"); uEHelper.setSpecificCellValue(3, 1, "序号"); uEHelper.setSpecificCellValue(3, 2, "部位"); uEHelper.setSpecificCellValue(3, 3, "工序"); //姓名占据两列 //uEHelper.merge("C2", "D2"); //uEHelper.merge("C3","D3"); //Range range = wS.Range[wS.Cells[2, 3], wS.Cells[2, 4]]; //uEHelper.textAlighRight(range); //工序名称,单据时间靠右 //range = wS.Range[wS.Cells[3, 4], wS.Cells[3, 5]]; //uEHelper.textAlighRight(range); //设置第三列宽度 uEHelper.setColumnWidth("C", 42.25M); uEHelper.setColumnWidth("A", 5.13M); uEHelper.setRowHeight(1, 31); uEHelper.setRowHeight(2, 31); pb.Visible = true; pb.Value = 0; pb.Maximum = dt.Rows.Count; lblResult.Visible = false; lblPrompt.Visible = true; lblPrompt.Text = "请等待!"; //开始写信息 for (int i = 0; i <= dt.Rows.Count - 1; i++) { uEHelper.setSpecificCellValue(i + 4, 1, dt.Rows[i]["row_num"].ToString()); uEHelper.setSpecificCellValue(i + 4, 2, dt.Rows[i]["Summary_Process"].ToString()); uEHelper.setSpecificCellValue(i + 4, 3, dt.Rows[i]["specific_Process"].ToString()); pb.Value++; } pb.Visible = false; pb.Value = 0; lblResult.Visible = true; lblPrompt.Visible = false; lblPrompt.Text = ""; for (int columnIndex = 4; columnIndex <= 30; columnIndex++) { uEHelper.setColumnWidthByColumnIndex(columnIndex, 4.5M); } Range range = wS.Range[wS.Cells[2 + dt.Rows.Count, 30], wS.Cells[2 + dt.Rows.Count, 30]]; uEHelper.setAllTheBoxLine(range); uEHelper.merge("A1", "AD1"); v_Excel.displayAlert(); uEHelper.MergeTheSpecificColumnWithoutBlankContent(2); //设置边框。 uEHelper.setAllTheBoxLine(); v_Excel.saveWithoutAutoFit(); v_Excel.closeTheApp(); ShowResult.show(lblResult, cbPN.Text.Trim() + " 产量统计模板保存于: " + xlsFilePath, true); timerRestoreLblResult.Start(); }
/// <summary> /// 得到一个对象实体 /// </summary> public Entity.BASE_BUS_OVERRUN_SUBMIT GetEntity(decimal ID) { StringBuilder strSql = new StringBuilder(); strSql.Append("select ID, BUSCODE, BUSLONG, BUSWIDTH, BUSHEIGHT, WEIGHT, PHOTO, INNAME, OUTNAME, TICKTIME, USERID, DEPTID, LOCATION, BUSUNIT, REMARKS "); strSql.Append(" from BASE_BUS_OVERRUN_SUBMIT "); strSql.Append(" where ID=:ID "); OracleParameter[] parameters = { new OracleParameter(":ID", OracleType.Number, 4) }; parameters[0].Value = ID; Entity.BASE_BUS_OVERRUN_SUBMIT entity = new Entity.BASE_BUS_OVERRUN_SUBMIT(); DataTable dt = OracleHelper.Query(CommandType.Text, strSql.ToString(), parameters); if (dt.Rows.Count > 0) { if (dt.Rows[0]["ID"].ToString() != "") { entity.ID = decimal.Parse(dt.Rows[0]["ID"].ToString()); } entity.BUSCODE = dt.Rows[0]["BUSCODE"].ToString(); if (dt.Rows[0]["BUSLONG"].ToString() != "") { entity.BUSLONG = decimal.Parse(dt.Rows[0]["BUSLONG"].ToString()); } if (dt.Rows[0]["BUSWIDTH"].ToString() != "") { entity.BUSWIDTH = decimal.Parse(dt.Rows[0]["BUSWIDTH"].ToString()); } if (dt.Rows[0]["BUSHEIGHT"].ToString() != "") { entity.BUSHEIGHT = decimal.Parse(dt.Rows[0]["BUSHEIGHT"].ToString()); } if (dt.Rows[0]["WEIGHT"].ToString() != "") { entity.WEIGHT = decimal.Parse(dt.Rows[0]["WEIGHT"].ToString()); } entity.PHOTO = dt.Rows[0]["PHOTO"].ToString(); entity.INNAME = dt.Rows[0]["INNAME"].ToString(); entity.OUTNAME = dt.Rows[0]["OUTNAME"].ToString(); if (dt.Rows[0]["TICKTIME"].ToString() != "") { entity.TICKTIME = DateTime.Parse(dt.Rows[0]["TICKTIME"].ToString()); } if (dt.Rows[0]["USERID"].ToString() != "") { entity.USERID = decimal.Parse(dt.Rows[0]["USERID"].ToString()); } if (dt.Rows[0]["DEPTID"].ToString() != "") { entity.DEPTID = decimal.Parse(dt.Rows[0]["DEPTID"].ToString()); } entity.LOCATION = dt.Rows[0]["LOCATION"].ToString(); entity.BUSUNIT = dt.Rows[0]["BUSUNIT"].ToString(); entity.REMARKS = dt.Rows[0]["REMARKS"].ToString(); return(entity); } else { return(null); } }
/// <summary> /// 厂家 出库 /// </summary> private void GI(string data) { string moveCode = ""; switch (GIType) { case 0: moveCode = "MOVE0401"; //buffer break; case 1: moveCode = "MOVE0402"; //其他厂家 break; } tbDocno.Text = GetDocNo(); string stock_block = GIType == 0 ? "" : "final_stock_to = null,";//final_stock_to = decode(GIType,0,final_stock_to,null) //出库 string sql = "update pacsd_pm_box set final_move_type = '" + destMoveType + "',final_move_code = '" + moveCode + "',final_vend_to = :final_vend_to, final_vend_from = final_vend_to," + stock_block + " final_buffer_to = :final_buffer_to,final_doc_no = :final_doc_no,last_doc_no = final_doc_no,operation_window = 'GICJ',box_case_status = :box_case_status,box_status = :box_status, " + " update_date = to_char(sysdate,'yyyyMMdd'),update_time = to_char(sysdate,'hh24miss'),update_user = :update_user,update_ip = :update_ip" + " where box_label = '" + data + "' " + " and fct_code = '" + PaCSGlobal.LoginUserInfo.Fct_code + "' "; OracleParameter[] cmdParam = new OracleParameter[] { new OracleParameter(":final_vend_to", OracleType.VarChar, 50), new OracleParameter(":final_doc_no", OracleType.VarChar, 50), new OracleParameter(":update_user", OracleType.VarChar, 50), new OracleParameter(":update_ip", OracleType.VarChar, 50), new OracleParameter(":box_case_status", OracleType.VarChar, 50), new OracleParameter(":box_status", OracleType.VarChar, 50), new OracleParameter(":final_buffer_to", OracleType.VarChar, 50) }; cmdParam[0].Value = GIType == 0 ? PaCSGlobal.LoginUserInfo.Venderid : destCode; cmdParam[1].Value = docno; cmdParam[2].Value = PaCSGlobal.LoginUserInfo.Id; cmdParam[3].Value = PaCSGlobal.GetClientIp(); DataTable dtStatus = TonerGlobal.GetCommInfoByCode(moveCode); if (dtStatus.Rows.Count > 0) { cmdParam[4].Value = dtStatus.Rows[0]["BOX_CASE_STATUS"].ToString(); cmdParam[5].Value = dtStatus.Rows[0]["BOX_STATUS"].ToString(); } else { cmdParam[4].Value = ""; cmdParam[5].Value = ""; } cmdParam[6].Value = GIType == 0 ? destCode : ""; int i = OracleHelper.ExecuteNonQuery(sql, cmdParam); //插出prog表 TonerGlobal.InsertIntoProg(data); lbStatus.Text = "【" + data + "】:出库成功"; panelStatus.BackColor = Color.GreenYellow; //提示成功语音 PaCSGlobal.PlayWavOk(); //刷新列表 TonerGlobal.SetGridView(GetData(docno), gridView1, gridControl1); }
/// <summary> /// 每卷使用明细 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void backgroundWorker2_DoWork(object sender, DoWorkEventArgs e) { try { this.Invoke((MethodInvoker) delegate { btnQuery.Enabled = false; DataTable dt = null; dt = OracleHelper.ExecuteDataTable(getSql2(ls_item, ls_sec_boxno, ls_sec_rollno, ls_sec_start, ls_status)); if (dt == null) { btnQuery.Enabled = true; return; } dt = setDtHeader2(dt); //更改标题栏和显示顺序 gridControl2.DataSource = dt; grdRoll.BestFitColumns(); grdRoll.Columns["Qty"].AppearanceCell.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Far; grdRoll.Columns["Qty"].SummaryItem.SummaryType = SummaryItemType.Sum; grdRoll.Columns["Qty"].SummaryItem.DisplayFormat = "Sum:{0:f0} "; grdRoll.Columns["Qty"].Width = 100; //grdRoll.Columns["Qty"].SummaryItem.SummaryType = SummaryItemType.Sum; //grdRoll.Columns["Qty"].SummaryItem.DisplayFormat = "Sum:{0:f0} "; grdRoll.Columns["仓库库存"].AppearanceCell.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Far; grdRoll.Columns["仓库库存"].SummaryItem.SummaryType = SummaryItemType.Sum; grdRoll.Columns["仓库库存"].SummaryItem.DisplayFormat = "{0:f0} "; grdRoll.Columns["部门库存"].AppearanceCell.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Far; grdRoll.Columns["部门库存"].SummaryItem.SummaryType = SummaryItemType.Sum; grdRoll.Columns["部门库存"].SummaryItem.DisplayFormat = "{0:f0} "; grdRoll.Columns["投入生产线库存"].AppearanceCell.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Far; grdRoll.Columns["投入生产线库存"].SummaryItem.SummaryType = SummaryItemType.Sum; grdRoll.Columns["投入生产线库存"].SummaryItem.DisplayFormat = "{0:f0} "; grdRoll.Columns["已扫描数"].AppearanceCell.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Far; grdRoll.Columns["已扫描数"].SummaryItem.SummaryType = SummaryItemType.Sum; grdRoll.Columns["已扫描数"].SummaryItem.DisplayFormat = "{0:f0} "; grdRoll.Columns["扫描后删除数"].AppearanceCell.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Far; grdRoll.Columns["扫描后删除数"].SummaryItem.SummaryType = SummaryItemType.Sum; grdRoll.Columns["扫描后删除数"].SummaryItem.DisplayFormat = "{0:f0} "; grdRoll.Columns["报废数"].AppearanceCell.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Far; grdRoll.Columns["报废数"].SummaryItem.SummaryType = SummaryItemType.Sum; grdRoll.Columns["报废数"].SummaryItem.DisplayFormat = "{0:f0} "; //冻结 grdRoll.Columns["Security Type"].Fixed = DevExpress.XtraGrid.Columns.FixedStyle.Left; grdRoll.Columns["Box No"].Fixed = DevExpress.XtraGrid.Columns.FixedStyle.Left; grdRoll.Columns["Roll No"].Fixed = DevExpress.XtraGrid.Columns.FixedStyle.Left; grdRoll.Columns["Security Start"].Fixed = DevExpress.XtraGrid.Columns.FixedStyle.Left; grdRoll.Columns["Security End"].Fixed = DevExpress.XtraGrid.Columns.FixedStyle.Left; grdRoll.Columns["Qty"].Fixed = DevExpress.XtraGrid.Columns.FixedStyle.Left; btnQuery.Enabled = true; SecuGlobal.showOK(panelStatus, lblStatus, "OK"); }); } catch (Exception err) { //XtraMessageBox.Show(this, "System error[ShowData]: " + err.Message); SecuGlobal.showNG(panelStatus, lblStatus, err.Message); } }
public int ExecuteSqlTran(List <CommandInfo> list, List <CommandInfo> oracleCmdSqlList) { MySqlConnection connection = DbConn; using (MySqlCommand cmd = new MySqlCommand()) { cmd.Connection = connection; MySqlTransaction tx = connection.BeginTransaction(); cmd.Transaction = tx; try { foreach (CommandInfo myDE in list) { string cmdText = myDE.CommandText; MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Parameters; PrepareCommand(cmd, connection, tx, cmdText, cmdParms); if (myDE.EffentNextType == EffentNextType.SolicitationEvent) { if (myDE.CommandText.ToLower().IndexOf("count(") == -1) { tx.Rollback(); throw new Exception("违背要求" + myDE.CommandText + "必须符合select count(..的格式"); //return 0; } object obj = cmd.ExecuteScalar(); bool isHave = false; if (obj == null && obj == DBNull.Value) { isHave = false; } isHave = Convert.ToInt32(obj) > 0; if (isHave) { //引发事件 myDE.OnSolicitationEvent(); } } if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine) { if (myDE.CommandText.ToLower().IndexOf("count(") == -1) { tx.Rollback(); throw new Exception("SQL:违背要求" + myDE.CommandText + "必须符合select count(..的格式"); //return 0; } object obj = cmd.ExecuteScalar(); bool isHave = false; if (obj == null && obj == DBNull.Value) { isHave = false; } isHave = Convert.ToInt32(obj) > 0; if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave) { tx.Rollback(); throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须大于0"); //return 0; } if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave) { tx.Rollback(); throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须等于0"); //return 0; } continue; } int val = cmd.ExecuteNonQuery(); if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0) { tx.Rollback(); throw new Exception("SQL:违背要求" + myDE.CommandText + "必须有影响行"); //return 0; } cmd.Parameters.Clear(); } string oraConnectionString = PubConstant.GetConnectionString("ConnectionStringPPC"); bool res = OracleHelper.ExecuteSqlTran(oraConnectionString, oracleCmdSqlList); if (!res) { tx.Rollback(); throw new Exception("执行失败"); // return -1; } tx.Commit(); return(1); } catch (MySql.Data.MySqlClient.MySqlException e) { tx.Rollback(); throw e; } } }
private void SetUnDirty(string table, string fildName, string connstr) { string sql = string.Format("Update {0} Set {1}=-1 Where {1}=1", table, fildName); OracleHelper.ExecuteNonQuery(connstr, sql); }
public void ParseResult(string strSource, ref string strResult, ref string strReserved, ref string strCmd) { string file = "south990J样本号重复处理.txt"; int a = 2; string temp = strSource; //连接access数据库 //配置文件中读取密码,查询语句 //读取配置文件 //a1,a2,a3,a4 仪器通道码和his通道码一致 StreamReader sr = new StreamReader(@".\south990J.txt", System.Text.Encoding.GetEncoding("GB2312")); string bounds = sr.ReadLine(); string configPath = sr.ReadLine(); sr.Close(); OleDbConnection sConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + configPath + ";Persist Security Info=False;Jet OLEDB:Database Password=iamysh"); string sqlMessage = null; try { sConn.Open(); writelog.Write(strDevice, "处理进度: " + "连接成功", "log"); } catch (Exception ex) { writelog.Write(strDevice, "处理失败: " + ex.ToString(), "log"); } string dateTime = DateTime.Now.ToString("yyyy-MM-dd"); sqlMessage = "select * from mainsj where a55= '" + dateTime + '\''; OleDbCommand sCmd = new OleDbCommand(sqlMessage, sConn); OleDbDataAdapter myda = new OleDbDataAdapter(sqlMessage, sConn); DataTable ds = new DataTable(); DataSet ds_GraphChannel = new DataSet(); DataSet dsGUID = new DataSet(); DataSet dsTestItem = new DataSet(); strReserved = ""; strResult = ""; strCmd = ""; init();//初始化 try { string strTestTime; //检验时间 string strSampleNo; //标本号 string strBarCode = ""; //条码 string strOperator = ""; //检验医师 string strSampleType = ""; //检验类型 string StrSpecimen = ""; //标本类型 string ChannelType; //0-普通结果;1-直方图;2-散点图;3-直方图界标;4-散点图界标;5-BASE64 string testItemID = ""; //通道项目ID DataRow[] FindRow; //解析设置 TestGraph = new List <string>(); saveResult = new SaveResult(); // IsUpdate(strSource);//重新检查是否有新数据 DataSet dsResult = new DataSet(); myda.Fill(dsResult); //tItemChannel得到数据集,提供样本号为后面判断是否有新数据 tItemChannel = OracleHelper.GetDataTable(@"Select 通道编码, m.项目id, Nvl(小数位数, 2) As 小数位数, Nvl(换算比, 0) As 换算比, Nvl(加算值, 0) As 加算值, j.结果类型 From 仪器检测项目 m, 检验项目 j Where m.项目id = j.项目id and m.仪器Id='" + strInstrument_id + "'"); if (dsResult.Tables[0].Rows.Count == 0)//判断是否有新标本 { writelog.Write(strDevice, DateTime.Now.ToString() + "未检测到新数据!", "log"); return; } // DataTable dt1 = SelectDistinct(dsResult.Tables[0], "ChemExamineID"); DataTable dt1 = dsResult.Tables[0]; string[] boundsArray = bounds.Split('|'); foreach (DataRow dr in dt1.Rows) //循环标本号 { string time1 = ""; string no1 = ""; StringBuilder sBuilder = new StringBuilder(""); strTestTime = DateTime.Parse(dr["a55"].ToString()).ToString("yyyy-MM-dd"); strSampleNo = dr["a1"].ToString(); time1 = strTestTime; no1 = strSampleNo; writelog.Write(strDevice, "处理失败: " + strTestTime + strSampleNo, "log"); for (int kl = 0; kl < boundsArray.Length; kl++) { sBuilder.Append(boundsArray[kl] + ',' + dr[boundsArray[kl]] + '|'); } string TestResultValue = ""; if (Helper.CompareSampleNoAndTime(file, no1, time1)) { continue; } string str = sBuilder.ToString().Remove(sBuilder.Length - 1, 1); string[] strs = str.Split('|'); for (int i = 0; i < strs.Length; i++) { FindRow = tItemChannel.Select("通道编码='" + strs[i].Split(',')[0] + "'"); if (FindRow.Length == 0) //无普通结果则查找图像能道,无图像通道则更新通道类型为空 { ChannelType = null; writelog.Write(strDevice, "未设置通道:" + strs[i].Split(',')[0], "log"); } else { testItemID = FindRow[0]["项目id"].ToString(); ChannelType = "0"; //普通结果 TestResultValue = TestResultValue + testItemID + "^" + strs[i].Split(',')[1] + "|"; } } TestResultValue = strTestTime + "|" + strSampleNo + "^" + strSampleType + "^" + strBarCode + "|" + strOperator + "|" + StrSpecimen + "|" + "|" + TestResultValue; try { saveResult.SaveTextResult(strInstrument_id, TestResultValue, TestGraph, null); saveResult.UpdateData(); writelog.Write(strDevice, "解析结果: " + TestResultValue, "result"); } catch (Exception ex) { writelog.Write(strDevice, "保存失败: " + ex.ToString(), "log"); } } } catch (Exception exp1) { writelog.Write(strDevice, "处理失败: " + exp1.ToString(), "log"); } finally { if (sConn != null) { sConn.Close(); } sConn = null; } }
/// <summary> /// 查询用户登录记录 /// </summary> /// <returns></returns> public string searchUserRecord(string guid, int nowpage, int pagesize) { string Select_Sql = ""; //条件语句 int count = 0; //条件查询到的记录数 int start = 0; //起始查询条数 int last = 0; //最终查询条数 int pagecount = 0; //总页数 string responseText = ""; if (guid == "") { responseText = "[{\"msg\":\"fail\",\"count\":0,\"record\":\"\",\"status\":\"未获取当前用户编号\"}]"; } string sql0 = string.Format("select count(*) from GX_ADMIN_ACCOUNT_RECORD where GUID='{0}'", guid); DataTable dt0 = OracleHelper.GetTable(sql0, null); count = Convert.ToInt32(dt0.Rows[0][0]); //得到了全部的记录数 if (count % pagesize == 0) //计算得到全部页数 { pagecount = count / pagesize; } else { pagecount = count / pagesize + 1; } if (count == 0) { start = 0; } else { start = (nowpage - 1) * pagesize + 1; //计算该显示记录的行数范围; } if (start <= count) { if (count - start >= pagesize) //start起始 last结束 { last = start + pagesize - 1; } else { last = count; } } string sql = string.Format("select * from(select a.*,rownum row_num from (select * from GX_ADMIN_ACCOUNT_RECORD where 1=1 and GUID='{0}' order by ORDER_TIME desc) a) b where b.row_num between {1} and {2}", guid, start, last); DataTable dt = OracleHelper.GetTable(sql, null); responseText = JsonHelper.getRecordJson(dt); if (dt.Rows.Count != 0) { responseText = "[{\"msg\":\"success\",\"count\":\"" + count.ToString() + "\",\"record\":[" + responseText + "]}]"; } else { responseText = "[{\"msg\":\"fail\",\"count\":0,\"record\":\"\",\"status\":\"没有数据\"}]"; } return(responseText); }
public DataTable GetMyMessage(int uid) { string sec = string.Format("SELECT b.userid, b.username, b.realname, b.phone,a.MESSAGEID ,a.messagebody, a.senddate FROM base_message a, base_user b WHERE a.sender = b.userid and a.touser = {0} ORDER BY a.senddate DESC", uid); return(OracleHelper.Query(sec).Tables[0]); }
protected void InfoTree_NodeValidating(object sender, TreeListNodeValidationEventArgs e) { if (e.NewValues["INFOCODE"].ToString().Trim().Length < 1) { e.Errors["INFOCODE"] = "请输入编码!"; } if (e.NewValues["INFOCODE"].ToString().Trim().Length > 0) { //GhtnTech.SEP.OraclDAL.DALCS_BaseInfoSet cb = new GhtnTech.SEP.OraclDAL.DALCS_BaseInfoSet(); try { //DataSet ds = cb.GetBaseInfoSetList(" and INFOID=" + int.Parse(e.NewValues["FID"].ToString().Trim())); StringBuilder strSql = new StringBuilder(); strSql.Append("select * FROM CS_BaseInfoSet"); strSql.Append(" where INFOID=" + int.Parse(e.NewValues["FID"].ToString().Trim())); DataSet ds = OracleHelper.Query(strSql.ToString()); //int A = e.NewValues["INFOCODE"].ToString().Trim().Length; //int b = int.Parse(ds.Tables[0].Rows[0]["CODINGL"].ToString().Trim()); if (e.NewValues["INFOCODE"].ToString().Trim().Length != int.Parse(ds.Tables[0].Rows[0]["CODINGL"].ToString().Trim())) { e.Errors["INFOCODE"] = "编码长度与设置不符合,请重新输入!"; } if (ds.Tables[0].Rows[0]["CODINGTYPE"].ToString().Trim() == "NUMBERIC") { try { Convert.ToInt32(e.NewValues["INFOCODE"].ToString().Trim()); } catch { e.Errors["INFOCODE"] = "编码类型与设置不符合,请重新输入!"; } } if (ds.Tables[0].Rows[0]["CODINGTYPE"].ToString().Trim() == "STRING") { try { Convert.ToInt32(e.NewValues["INFOCODE"].ToString().Trim()); e.Errors["INFOCODE"] = "编码类型与设置不符合,请重新输入!"; } catch { } } StringBuilder strsSql = new StringBuilder(); strsSql.Append("select * FROM CS_BaseInfoSet"); strsSql.Append(" where FID=" + int.Parse(e.NewValues["FID"].ToString().Trim())); DataSet dsQC = OracleHelper.Query(strsSql.ToString()); for (int i = 0; i < dsQC.Tables[0].Rows.Count; i++) { if (e.NewValues["INFOCODE"].ToString().Trim() == dsQC.Tables[0].Rows[i]["INFOCODE"].ToString().Trim()) { e.Errors["INFOCODE"] = "相同根节点下已经存在相同的编码,请重新输入!"; } } } catch { } } if (!IsStringValueNotEmpty(e.NewValues["INFONAME"])) { e.Errors["INFONAME"] = "请输入信息!"; } if (!IsStringValueNotEmpty(e.NewValues["CODINGTYPE"])) { e.Errors["CODINGTYPE"] = "请输入信息!"; } if (!IsStringValueNotEmpty(e.NewValues["CODINGL"])) { e.Errors["CODINGL"] = "请输入信息!"; } if (!IsDateValid(e.NewValues["PDAY"])) { e.Errors["PDAY"] = "请选择当天或当天之前的日期信息!"; } }
// 删除 private void barBtnDel_ItemClick(object sender, ItemClickEventArgs e) { if (this.gvOCN_RLLXPARAM.DataSource == null) { MessageBox.Show("没有可以操作的记录!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } this.gvOCN_RLLXPARAM.PostEditor(); var dataSource = (DataView)this.gvOCN_RLLXPARAM.DataSource; var dtSelected = dataSource.Table.Copy(); dtSelected.Clear(); if (dataSource != null && dataSource.Table.Rows.Count > 0) { for (int i = 0; i < dataSource.Table.Rows.Count; i++) { bool result = false; bool.TryParse(dataSource.Table.Rows[i]["check"].ToString(), out result); if (result) { dtSelected.Rows.Add(dataSource.Table.Rows[i].ItemArray); } } } var SC_OCNSelected = dtSelected.AsDataView().ToTable(true, new string[] { "SC_OCN", "VERSION" }); if (SC_OCNSelected.Rows.Count == 0) { MessageBox.Show("请选择您要操作的记录!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } var sc_ocnArray = SC_OCNSelected.AsEnumerable().Select(d => d.Field <string>("SC_OCN")).ToArray(); if (OracleHelper.Exists(OracleHelper.conn, String.Format("SELECT COUNT(*) FROM VIN_INFO WHERE SC_OCN IN ('{0}') AND MERGER_STATUS=0", string.Join("','", sc_ocnArray)))) { MessageBox.Show("您选择要操作的记录包含未和合成的数据!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } if (MessageBox.Show("编组删除,确定要删除吗?", "删除确认", MessageBoxButtons.OKCancel, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) != DialogResult.OK) { return; } using (OracleConnection conn = new OracleConnection(OracleHelper.conn)) { conn.Open(); using (OracleTransaction trans = conn.BeginTransaction()) { foreach (DataRow dr in dtSelected.Rows) { try { string version = OracleHelper.ExecuteScalar(OracleHelper.conn, string.Format("SELECT MIN(VERSION) FROM OCN_RLLX_PARAM_ENTITY WHERE OPERATION='4' AND SC_OCN='{0}'", dr["SC_OCN"])).ToString(); int versionNew = string.IsNullOrEmpty(version) ? 0 : Convert.ToInt32(version) - 1; OracleHelper.ExecuteNonQuery(OracleHelper.conn, string.Format("UPDATE OCN_RLLX_PARAM_ENTITY SET OPERATION = '4',VERSION = '{0}' WHERE SC_OCN='{1}' AND OPERATION = '{2}' AND VERSION={3} ", versionNew, dr["SC_OCN"], dr["OPERATION"], dr["VERSION"])); } catch (Exception ex) { trans.Rollback(); MessageBox.Show(String.Format("数据库操作出现异常,删除失败:{0}!", ex.Message), "提示", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } } if (trans.Connection != null) { trans.Commit(); } } } this.refrashCurrentPage(); }
/// <summary> /// 更新一条数据 /// </summary> public bool Update(Entity.BASE_BUS_OVERRUN_SUBMIT entity) { StringBuilder strSql = new StringBuilder(); strSql.Append("update BASE_BUS_OVERRUN_SUBMIT set "); strSql.Append(" ID = :ID , "); strSql.Append(" BUSCODE = :BUSCODE , "); strSql.Append(" BUSLONG = :BUSLONG , "); strSql.Append(" BUSWIDTH = :BUSWIDTH , "); strSql.Append(" BUSHEIGHT = :BUSHEIGHT , "); strSql.Append(" WEIGHT = :WEIGHT , "); strSql.Append(" PHOTO = :PHOTO , "); strSql.Append(" INNAME = :INNAME , "); strSql.Append(" OUTNAME = :OUTNAME , "); strSql.Append(" TICKTIME = :TICKTIME , "); strSql.Append(" USERID = :USERID , "); strSql.Append(" DEPTID = :DEPTID , "); strSql.Append(" LOCATION = :LOCATION , "); strSql.Append(" BUSUNIT = :BUSUNIT , "); strSql.Append(" REMARKS = :REMARKS "); strSql.Append(" where ID=:ID "); OracleParameter[] parameters = { new OracleParameter(":ID", OracleType.Number, 4), new OracleParameter(":BUSCODE", OracleType.VarChar, 20), new OracleParameter(":BUSLONG", OracleType.Number, 4), new OracleParameter(":BUSWIDTH", OracleType.Number, 4), new OracleParameter(":BUSHEIGHT", OracleType.Number, 4), new OracleParameter(":WEIGHT", OracleType.Number, 4), new OracleParameter(":PHOTO", OracleType.VarChar, 200), new OracleParameter(":INNAME", OracleType.VarChar, 50), new OracleParameter(":OUTNAME", OracleType.VarChar, 50), new OracleParameter(":TICKTIME", OracleType.DateTime), new OracleParameter(":USERID", OracleType.Number, 4), new OracleParameter(":DEPTID", OracleType.Number, 4), new OracleParameter(":LOCATION", OracleType.VarChar, 100), new OracleParameter(":BUSUNIT", OracleType.VarChar, 50), new OracleParameter(":REMARKS", OracleType.VarChar, 50) }; parameters[15].Value = entity.ID; parameters[16].Value = entity.BUSCODE; parameters[17].Value = entity.BUSLONG; parameters[18].Value = entity.BUSWIDTH; parameters[19].Value = entity.BUSHEIGHT; parameters[20].Value = entity.WEIGHT; parameters[21].Value = entity.PHOTO; parameters[22].Value = entity.INNAME; parameters[23].Value = entity.OUTNAME; parameters[24].Value = entity.TICKTIME; parameters[25].Value = entity.USERID; parameters[26].Value = entity.DEPTID; parameters[27].Value = entity.LOCATION; parameters[28].Value = entity.BUSUNIT; parameters[29].Value = entity.REMARKS; int rows = OracleHelper.ExecuteNonQuery(strSql.ToString(), parameters); if (rows > 0) { return(true); } else { return(false); } }
// 导入Excel private void barBtnImport_ItemClick(object sender, ItemClickEventArgs e) { if (openFileDialog1.ShowDialog() == DialogResult.OK) { Dictionary <string, string> error = new Dictionary <string, string>(); string msg = string.Empty; try { SplashScreenManager.ShowForm(typeof(DevWaitForm)); // STEP1:导入系统,验证单元格格式 var ds = ImportExcel.ReadExcelToDataSet(openFileDialog1.FileName); for (int i = 0; i < ds.Tables["TEMPLATE"].Columns.Count; i++) { for (int j = 0; j < ds.Tables[0].Rows.Count; j++) { if (ds.Tables[0].Rows[j][i].GetType() != typeof(System.DBNull)) { if (ds.Tables[0].Rows[j][i].GetType() != typeof(System.String)) { MessageBox.Show(String.Format("【{1}】列中第【{0}】行的单元格格式不正确,应为文本格式!", j + 2, ds.Tables[0].Columns[i].ColumnName), "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } } } } // STEP2:替换列名,验证参数的数值 var dt = ImportExcel.SwitchRLLXPARAMColumnName(ds); if (dt != null) { string sc_ocn_error = string.Empty; error = DataVerifyHelper.VerifyRLLXPARAMData(dt); if (error.Count == 0) { using (OracleConnection conn = new OracleConnection(OracleHelper.conn)) { conn.Open(); using (OracleTransaction trans = conn.BeginTransaction()) { // STEP3:验证无误,导入系统数据库 foreach (DataRow dr in dt.Rows) { if (sc_ocn_error.Equals(dr["SC_OCN"].ToString())) { continue; } try { string exist = OracleHelper.ExecuteScalar(trans, string.Format("SELECT COUNT(*) FROM OCN_RLLX_PARAM_ENTITY WHERE OPERATION!='4' AND SC_OCN='{0}' AND CSBM='{1}'", dr["SC_OCN"], dr["CSBM"])).ToString(); int existNum = string.IsNullOrEmpty(exist) ? 0 : Convert.ToInt32(exist); if (existNum > 0) { error.Add(dr["SC_OCN"].ToString().Trim(), "系统已经存在改生产OCN的燃料参数数据!"); sc_ocn_error = dr["SC_OCN"].ToString(); continue; } OracleParameter[] parameters = { new OracleParameter("SC_OCN", OracleDbType.NVarchar2, 255), new OracleParameter("CSBM", OracleDbType.NVarchar2, 255), new OracleParameter("CSMC", OracleDbType.NVarchar2, 255), new OracleParameter("RLLX", OracleDbType.NVarchar2, 255), new OracleParameter("CSZ", OracleDbType.NVarchar2, 255), new OracleParameter("OPERATION", OracleDbType.NVarchar2, 255), new OracleParameter("CREATE_TIME", OracleDbType.Date), new OracleParameter("CREATE_ROLE", OracleDbType.NVarchar2, 255), new OracleParameter("UPDATE_TIME", OracleDbType.Date), new OracleParameter("UPDATE_ROLE", OracleDbType.NVarchar2, 255), new OracleParameter("VERSION", OracleDbType.Int32), }; parameters[0].Value = dr["SC_OCN"]; parameters[1].Value = dr["CSBM"]; parameters[2].Value = dr["CSMC"]; parameters[3].Value = dr["RLLX"]; parameters[4].Value = dr["CSZ"]; parameters[5].Value = "0"; parameters[6].Value = System.DateTime.Today; parameters[7].Value = Utils.localUserId; parameters[8].Value = System.DateTime.Today; parameters[9].Value = Utils.localUserId; parameters[10].Value = 0; OracleHelper.ExecuteNonQuery(trans, "Insert into OCN_RLLX_PARAM_ENTITY (SC_OCN,CSBM,CSMC,RLLX,CSZ,OPERATION,CREATE_TIME,CREATE_ROLE,UPDATE_TIME,UPDATE_ROLE,VERSION) values (:SC_OCN,:CSBM,:CSMC,:RLLX,:CSZ,:OPERATION,:CREATE_TIME,:CREATE_ROLE,:UPDATE_TIME,:UPDATE_ROLE,:VERSION)", parameters); } catch (Exception ex) { error.Add(String.Format("{0} {0}", dr["SC_OCN"], dr["CSBM"]), ex.Message); sc_ocn_error = dr["SC_OCN"].ToString(); continue; } } if (trans.Connection != null) { trans.Commit(); } } } // STEP4:处理无误,处理完成的文件 if (error.Count == 0) { var destFolder = Path.Combine(Path.GetDirectoryName(openFileDialog1.FileName), DateTime.Today.ToLongDateString() + "-燃料参数数据-Imported"); Directory.CreateDirectory(destFolder); try { File.Move(openFileDialog1.FileName, Path.Combine(destFolder, String.Format("Imported-{0}{1}", Path.GetFileNameWithoutExtension(openFileDialog1.FileName), Path.GetExtension(openFileDialog1.FileName)))); } catch (Exception ex) { MessageBox.Show(String.Format("Excel处理操作异常:导入完成,{0}", ex.Message), "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } } } foreach (KeyValuePair <string, string> kvp in error) { msg += String.Format("{0}\r\n{1}\r\n", kvp.Key, kvp.Value); } } } catch (Exception ex) { MessageBox.Show("Excel导入操作异常:" + ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } finally { SplashScreenManager.CloseForm(); } MessageForm msgForm = new MessageForm(msg + String.Format("\r\n{0}Excel导入操作完成", Path.GetFileNameWithoutExtension(openFileDialog1.FileName))) { Text = "燃料参数导入信息" }; msgForm.Show(); SearchLocal(1); } }
//获取总数 private int queryCount() { string sqlCount = String.Format("select count(*) from VIEW_INSPECT where 1=1 {0}", this.queryParam()); return(Convert.ToInt32(OracleHelper.GetSingle(OracleHelper.conn, sqlCount))); }
public bool UpdateAlarm(string deviceCode, string deviceName, string faultMessage, string faultType, TimeSpan faultDuration) { //SPPG_WMS.sp_error_log(ls_device_id in varchar2,ls_device_name in varchar2,ls_error_inf in varchar2, ri_ret out number, rs_ret out varchar2) //SPPG_WMS.sp_error_log(ls_device_id in varchar2,ls_device_name in varchar2,ls_error_inf in varchar2,li_Error_time in number,ls_Error_type in varchar2,ri_ret out number, rs_ret out varchar2). bool sucess = false; OracleHelper.LocalConnectionString = connectionString; if (!OracleHelper.CanConnectDb(true)) { OprationState = "上传设备错误信息失败,数据库未连接!"; Program.ErrorManager.Add(new RuntimeError(oprationState, RuntimeError.EVisibility.User)); return(false); } OracleTransaction tran = OracleHelper.BeginTransaction(); OracleParameter[] param = new OracleParameter[] { new OracleParameter("ls_device_id", OracleType.VarChar), new OracleParameter("ls_device_name", OracleType.VarChar), new OracleParameter("ls_error_inf", OracleType.VarChar), new OracleParameter("li_Error_time", OracleType.Int32), new OracleParameter("ls_Error_type", OracleType.VarChar), new OracleParameter("ri_ret", OracleType.Int32), new OracleParameter("rs_ret", OracleType.VarChar, 512) }; param[0].Value = deviceCode; param[1].Value = deviceName; param[2].Value = faultMessage; param[3].Value = (int)(faultDuration.Seconds); param[0].Direction = ParameterDirection.Input; param[1].Direction = ParameterDirection.Input; param[2].Direction = ParameterDirection.Input; param[3].Direction = ParameterDirection.Input; param[4].Direction = ParameterDirection.Input; param[5].Direction = ParameterDirection.Output; param[6].Direction = ParameterDirection.Output; try { OracleHelper.ExecuteNonQuery(tran, CommandType.StoredProcedure, "SPPG_WMS.sp_error_log", param); if ((int)param[5].Value != 0) { OracleHelper.RollbackTransaction(tran); sucess = false; OprationState = string.Format("上传设备错误信息失败!代码:{0}可能是由于:{1}", param[5].Value, param[6].Value); Program.ErrorManager.Add(new RuntimeError(oprationState, RuntimeError.EVisibility.User)); } else { sucess = true; } } catch (System.Exception ex) { OracleHelper.RollbackTransaction(tran);//.Rollback(); sucess = false; OprationState = string.Format("上传设备错误信息失败!数据库操作错误"); Program.ErrorManager.Add(new RuntimeError(oprationState, RuntimeError.EVisibility.Programer, ex)); } finally { if (sucess) { OracleHelper.CommitTransaction(tran); //Program.ErrorManager.Add(new RuntimeError("上传设备报警信息-成功", RuntimeError.EVisibility.Programer)); } } return(sucess); }
//获取燃料类型 private string QueryRLLX(string vin, string param) { return(OracleHelper.ExecuteScalar(OracleHelper.conn, string.Format("select PARAM_VALUE from RLLX_PARAM_ENTITY where vin='{0}' and PARAM_CODE='{1}'", vin, param), null).ToString()); }
/// <summary> /// OnDragDrop is used to create classes corresponding to the selection dragged /// from the Server Explorer /// </summary> private void OnDragDrop(object sender, DragEventArgs e) { // Check if the data present is in the DSRef format if (e.Data.GetDataPresent(DSRefNavigator.DataSourceReferenceFormat)) { try { // Create a navigator for the DSRef Consumer (and dispose it when finished) using(DSRefNavigator navigator = new DSRefNavigator(e.Data.GetData( DSRefNavigator.DataSourceReferenceFormat) as Stream)) { _output = new OutputWindowHelper(DTEHelper.GetDTE(Store)); // Get connection info of the connection of selected tables string providerType = null; IDbConnection connection = ServerExplorerHelper.GetConnection(navigator, out providerType); IDbHelper helper; switch(providerType) { case "System.Data.SqlClient.SqlConnection": helper = new SqlHelper(connection); break; case "System.Data.OracleClient.OracleConnection": case "Oracle.DataAccess.Client.OracleConnection": Log("Selecting Oracle Helper for provider " + providerType); helper = new OracleHelper(connection); break; case "MySql.Data.MySqlClient.MySqlConnection": helper = new MySqlHelper(connection); break; default: // TODO: Support other databases with native providers. Log( string.Format( @"Failed: ActiveWriter does not support model generation through {0}. Supported providers: System.Data.SqlClient.SqlConnection, System.Data.OracleClient.OracleConnection, Oracle.DataAccess.Client.OracleConnection, MySql.Data.MySqlClient.MySqlConnection. You can help us improve this functionality, though. See http://www.castleproject.org/others/contrib/index.html to access ActiveWriter source code under the contrib repository, and check Dsl\ServerExplorerSupport\IDbHelper.cs for the start.", providerType)); return; } // Get the root element where we'll add the classes Model model = Helper.GetModel(Store); if (model == null) { Log("Failed: Cannot get the model for the store."); return; } _manager = new DiagramManager(Store, model); _manager.OutputWindow = _output; // Create a transaction to add the clases. using(Transaction txAdd = model.Store.TransactionManager.BeginTransaction("Add classes")) { List<DSRefNode> tableList = new List<DSRefNode>(); // Get the tables from the Server Explorer selection // We'll iterate this list twice to use nodes' list to // determine if we have to generate relations for each // table or not. foreach(DSRefNode node in navigator.ChildTableNodes) { tableList.Add(node); } _manager.Tables = tableList; _relations = new List<Relation>(); foreach(DSRefNode node in tableList) { // Create the table and add it to the model ModelClass cls = _manager.NewClass(node.Owner, node.Name); PopulateClass(cls, connection, helper); _manager.AssignModel(cls); } // Create relations if (_relations != null && _relations.Count > 0) { HandleRelations(); } // Commit the transaction and add tables to the model txAdd.Commit(); } // TODO: Auto layout doesn't work well. Will check with future versions of DSL tools. // this.AutoLayoutShapeElements(this.NestedChildShapes); } } catch(Exception ex) { Debug.WriteLine(ex.Message); } finally { _manager = null; _relations = null; _output = null; } } }
//数据处理 private void barButtonItem6_ItemClick(object sender, ItemClickEventArgs e) { try { if (this.xtraTabControl1.SelectedTabPage.Text == "补传数据") //需要补传数据 { var vins = GetDataFormat(); if (vins != null && vins.Table.Rows.Count == 0) { MessageBox.Show("请选择要操作的数据!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } var rllxName = GetRadio(); if (InsertFC_CLJBXX(vins, "1", rllxName)) { foreach (Form f in Application.OpenForms) { if (f.Name == "SearchLocalOTForm") { f.Activate(); ((SearchLocalOTForm)f).LocalData(vins); ((MainForm)this.MdiParent).Ribbon.SelectedPage = ((SearchLocalOTForm)f).Ribbon.Pages[0]; return; } } SearchLocalOTForm slo = new SearchLocalOTForm() { MdiParent = MdiParent }; slo.LocalData(vins); ((MainForm)this.MdiParent).Ribbon.SelectedPage = slo.Ribbon.Pages[0]; slo.Show(); } else { MessageBox.Show("操作失败,请检查数据!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } } if (this.xtraTabControl1.SelectedTabPage.Text == "撤销数据") //需要撤销数据 { var vins = GetDataFormat(); if (vins != null && vins.Table.Rows.Count == 0) { MessageBox.Show("请选择要操作的数据!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } //将选中的处理数据更改状态 foreach (DataRow dr in vins.Table.Rows) { OracleHelper.ExecuteNonQuery(OracleHelper.conn, String.Format("DELETE FROM FC_CLJBXX WHERE VIN = '{0}'", dr["VIN"]), null); OracleHelper.ExecuteNonQuery(OracleHelper.conn, String.Format("DELETE FROM RLLX_PARAM_ENTITY WHERE VIN ='{0}'", dr["VIN"]), null); OracleHelper.ExecuteNonQuery(OracleHelper.conn, string.Format("INSERT INTO FC_CLJBXX (VIN,HGSPBM,USER_ID,QCSCQY,JKQCZJXS,CLXH,CLZL,RLLX,ZCZBZL,ZGCS,LTGG,ZJ,CLZZRQ,UPLOADDEADLINE,TYMC,YYC,ZWPS,ZDSJZZL,EDZK,LJ,QDXS,CREATETIME,UPDATETIME,STATUS,JYJGMC,JYBGBH,QTXX,V_ID) SELECT VIN,HGSPBM,USER_ID,QCSCQY,JKQCZJXS,CLXH,CLZL,RLLX,ZCZBZL,ZGCS,LTGG,ZJ,CLZZRQ,UPLOADDEADLINE,TYMC,YYC,ZWPS,ZDSJZZL,EDZK,LJ,QDXS,CREATETIME,UPDATETIME,STATUS,JYJGMC,JYBGBH,QTXX,V_ID FROM FC_CLJBXX_ADC WHERE VIN='{0}'", dr["VIN"]), null); OracleHelper.ExecuteNonQuery(OracleHelper.conn, string.Format("UPDATE FC_CLJBXX SET STATUS='3',USER_ID='{0}' WHERE VIN='{1}'", Utils.localUserId, dr["VIN"]), null); OracleHelper.ExecuteNonQuery(OracleHelper.conn, string.Format("INSERT INTO RLLX_PARAM_ENTITY (PARAM_CODE,VIN,PARAM_VALUE,V_ID) SELECT PARAM_CODE,VIN,PARAM_VALUE,V_ID FROM RLLX_PARAM_ENTITY_ADC WHERE VIN='{0}'", dr["VIN"]), null); } foreach (Form f in Application.OpenForms) { if (f.Name == "SearchLocalUploadedForm") { f.Activate(); ((SearchLocalUploadedForm)f).LocalData(vins); ((MainForm)this.MdiParent).Ribbon.SelectedPage = ((SearchLocalUploadedForm)f).Ribbon.Pages[0]; return; } } SearchLocalUploadedForm sluf = new SearchLocalUploadedForm() { MdiParent = this.MdiParent }; sluf.LocalData(vins); ((MainForm)this.MdiParent).Ribbon.SelectedPage = sluf.Ribbon.Pages[0]; sluf.Show(); } if (this.xtraTabControl1.SelectedTabPage.Text == "修改数据") //需要修改数据 { var str = GetCheckString(); if (str == null || str.Count == 0) { MessageBox.Show("请选择要操作的数据!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } DataView vins = GetCheckData(); var dt = (DataTable)gridControl2.DataSource; DataTable dtNew = dt.Clone(); foreach (string s in str) { var dr = dt.Select(String.Format("vin='{0}'", s)); if (dr.Length > 0) { foreach (DataRow r in dr) { dtNew.Rows.Add(r.ItemArray); } continue; } } switch (radioGroup1.SelectedIndex) { case 0: dtNew = miutils.D2D(miutils.dictCTNY, dtNew, MitsUtils.CTNY); break; case 1: dtNew = miutils.D2D(miutils.dictFCDSHHDL, dtNew, MitsUtils.FCDSHHDL); break; case 2: dtNew = miutils.D2D(miutils.dictCDSHHDL, dtNew, MitsUtils.CDSHHDL); break; case 3: dtNew = miutils.D2D(miutils.dictCDD, dtNew, MitsUtils.CDD); break; case 4: dtNew = miutils.D2D(miutils.dictRLDC, dtNew, MitsUtils.RLDC); break; } var rllxName = GetRadio(); if (InsertFC_CLJBXX(dtNew.DefaultView, "2", rllxName)) { foreach (Form f in Application.OpenForms) { if (f.Name == "SearchLocalUpdateForm") { f.Activate(); ((SearchLocalUpdateForm)f).LocalData(vins); ((MainForm)this.MdiParent).Ribbon.SelectedPage = ((SearchLocalUpdateForm)f).Ribbon.Pages[0]; return; } } SearchLocalUpdateForm suf = new SearchLocalUpdateForm() { MdiParent = this.MdiParent }; suf.LocalData(vins); ((MainForm)this.MdiParent).Ribbon.SelectedPage = suf.Ribbon.Pages[0]; suf.Show(); } else { MessageBox.Show("操作失败,请检查数据!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } } } catch (Exception ex) { throw ex; } }
/// <summary> /// 新股日历 /// </summary> private void Xgrl() { List <XgInfo> List = new List <XgInfo>(); DateTime dtnow = System.DateTime.Now; string sql = string.Format(@"select * from (select * from view_xgzx where fxjc='10' and sgr is not null union all select * from view_xgzx where fxjc='20' and sgr between sysdate-1 and sysdate) order by sgr "); DataTable dt = OracleHelper.ExecuteDataTable(sql, ConnDhzb); if (dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { XgInfo oj = new XgInfo() { dhnm = dr["dhnm"].ToString(), zqdm = dr["zqdm"].ToString(), zqjc = dr["zqjc"].ToString(), sgdm_swfx = dr["sgdm_swfx"].ToString(), ssd = dr["ssd"].ToString().Equals("90") ? "深圳证券交易所" : "上海证券交易所", mgfxj = ConvertHelper.ConvertTo2XS_(dr["mgfxj"]), syl = ConvertHelper.ConvertTo2XS_(dr["syl"]), fxlsx = ConvertHelper.ConvertToDanWei(dr["fxlsx"], 2) + "万", wsfxjh = ConvertHelper.ConvertToDanWei(dr["wsfxjh"], 2) + "万", sgsz = dr["ssd"].ToString().Equals("90") ? "深圳市值" + ConvertHelper.ConvertToDanWei(dr["sgsx_swfx"], 2) + "万" : "上海市值" + ConvertHelper.ConvertToDanWei(dr["sgsx_swfx"], 2) + "万", sgsx_swfx = dr["sgsx_swfx"].ToString().Equals("") ? "--" : dr["sgsx_swfx"].ToString(), jys = dr["ssd"].ToString().Equals("90") ? "深" : "沪", bklx = "", zql_wsfx = ConvertHelper.ConvertTo2XS100(dr["zql_wsfx"]) + "%", ssrq = dr["ssrq"].ToString(), myqhl_xg = dr["myqhl_xg"].ToString(), xtcxcs = dr["xtcxcs"].ToString(), gsjj = "", sgr = ConvertHelper.DateToShotStringNull(dr["sgr"]), fxjgggr = ConvertHelper.DateToShotStringNull(dr["fxjgggr"]), paydateonline = ConvertHelper.DateToShotStringNull(dr["paydateonline"]), orderbydate = Convert.ToDateTime(ConvertHelper.DateToShotString(dr["sgr"])), updateTime = ConvertHelper.DateToShotString(dtnow) }; XgInfo xg = new XgInfo() { dhnm = dr["dhnm"].ToString(), zqdm = dr["zqdm"].ToString(), zqjc = dr["zqjc"].ToString(), sgdm_swfx = dr["sgdm_swfx"].ToString(), ssd = dr["ssd"].ToString().Equals("90") ? "深圳证券交易所" : "上海证券交易所", mgfxj = ConvertHelper.ConvertTo2XS_(dr["mgfxj"]), syl = ConvertHelper.ConvertTo2XS_(dr["syl"]), fxlsx = ConvertHelper.ConvertToDanWei(dr["fxlsx"], 2) + "万", wsfxjh = ConvertHelper.ConvertToDanWei(dr["wsfxjh"], 2) + "万", sgsz = dr["ssd"].ToString().Equals("90") ? "深圳市值" + ConvertHelper.ConvertToDanWei(dr["sgsx_swfx"], 2) + "万" : "上海市值" + ConvertHelper.ConvertToDanWei(dr["sgsx_swfx"], 2) + "万", sgsx_swfx = dr["sgsx_swfx"].ToString().Equals("") ? "--" : dr["sgsx_swfx"].ToString(), jys = dr["ssd"].ToString().Equals("90") ? "深" : "沪", bklx = "", zql_wsfx = ConvertHelper.ConvertTo2XS100(dr["zql_wsfx"]) + "%", ssrq = ConvertHelper.DateToShotStringNull(dr["ssrq"]), myqhl_xg = dr["myqhl_xg"].ToString(), xtcxcs = dr["xtcxcs"].ToString(), gsjj = dr["gsjj"].ToString(), sgr = ConvertHelper.DateToShotStringNull(dr["sgr"]), fxjgggr = ConvertHelper.DateToShotStringNull(dr["fxjgggr"]), paydateonline = ConvertHelper.DateToShotStringNull(dr["paydateonline"]), orderbydate = Convert.ToDateTime(ConvertHelper.DateToShotString(dr["sgr"])), updateTime = ConvertHelper.DateToShotString(dtnow) }; List.Add(oj); redis.StringSet(XgKeyDetail + xg.zqdm, xg, TimeSpan.FromSeconds(Convert.ToDouble(Config.RedisExpiry))); } } //科创板(10天内),生产环境<=0 string sql_kcb = string.Format(@"select a.SecuCode,SecurityAbbr,ApplyCodeOnline ,IssuePrice,PERAfterIssueCutNP, IssueVol,SharesOnline,ApplyMaxOnline , a.LotRateOnline ,a.ListedDate, OnlineStartDate,LotRatePublDate ,PayDateOnline , c.BriefIntroText,d.ClosePrice from LC_STIBIPOIssue a left join SecuMain b on a.SecuCode=b.SecuCode left join LC_STIBStockArchives c on b.CompanyCode=c.CompanyCode left join (select aa.ClosePrice,aa.TradingDay,aa.InnerCode from LC_STIBDailyQuote aa join ( select InnerCode,MAX(TradingDay) TradingDay from LC_STIBDailyQuote group by InnerCode) bb on aa.InnerCode=bb.InnerCode and aa.TradingDay=bb.TradingDay) d on b.InnerCode=d.InnerCode where DateDiff(dd,a.OnlineStartDate,GETDATE())>=-10 and DateDiff(dd,a.OnlineStartDate,GETDATE())<=0 order by a.OnlineStartDate "); DataTable dt_kcb = SqlHelper.ExecuteQuery(sql_kcb, ConnJy); if (dt_kcb.Rows.Count > 0) { foreach (DataRow dr in dt_kcb.Rows) { decimal spj = dr["ClosePrice"].Equals(DBNull.Value) ? 0 : Convert.ToDecimal(dr["ClosePrice"]); decimal fxj = dr["IssuePrice"].Equals(DBNull.Value) ? 0 : Convert.ToDecimal(dr["IssuePrice"]); string mqhl = ((spj - fxj) * 500).ToString("G0"); XgInfo oj = new XgInfo() { dhnm = "", zqdm = dr["SecuCode"].ToString(), zqjc = dr["SecurityAbbr"].ToString(), sgdm_swfx = dr["ApplyCodeOnline"].ToString(), ssd = "上海证券交易所", mgfxj = ConvertHelper.ConvertTo2XS_(dr["IssuePrice"]), syl = ConvertHelper.ConvertTo2XS_(dr["PERAfterIssueCutNP"]), fxlsx = ConvertHelper.ConvertToDanWei(dr["IssueVol"], 2) + "万", wsfxjh = ConvertHelper.ConvertToDanWei(dr["SharesOnline"], 2) + "万", sgsz = "上海市值" + ConvertHelper.ConvertToDanWei(Convert.ToDouble(dr["ApplyMaxOnline"]) * 10, 2) + "万", sgsx_swfx = dr["ApplyMaxOnline"].ToString(), jys = "沪", bklx = "科", zql_wsfx = ConvertHelper.ConvertTo2XS(dr["LotRateOnline"]) + "%", ssrq = ConvertHelper.DateToShotStringNull(dr["ListedDate"]), myqhl_xg = mqhl, xtcxcs = "--", gsjj = "", sgr = ConvertHelper.DateToShotStringNull(dr["OnlineStartDate"]), fxjgggr = ConvertHelper.DateToShotStringNull(dr["LotRatePublDate"]), paydateonline = ConvertHelper.DateToShotStringNull(dr["PayDateOnline"]), orderbydate = Convert.ToDateTime(ConvertHelper.DateToShotString(dr["OnlineStartDate"])), updateTime = ConvertHelper.DateToShotString(dtnow) }; XgInfo xg = new XgInfo() { dhnm = "", zqdm = dr["SecuCode"].ToString(), zqjc = dr["SecurityAbbr"].ToString(), sgdm_swfx = dr["ApplyCodeOnline"].ToString(), ssd = "上海证券交易所", mgfxj = ConvertHelper.ConvertTo2XS_(dr["IssuePrice"]), syl = ConvertHelper.ConvertTo2XS_(dr["PERAfterIssueCutNP"]), fxlsx = ConvertHelper.ConvertToDanWei(dr["IssueVol"], 2) + "万", wsfxjh = ConvertHelper.ConvertToDanWei(dr["SharesOnline"], 2) + "万", sgsz = "上海市值" + ConvertHelper.ConvertToDanWei(Convert.ToDouble(dr["ApplyMaxOnline"]) * 10, 2) + "万", sgsx_swfx = dr["ApplyMaxOnline"].ToString(), jys = "沪", bklx = "科", zql_wsfx = ConvertHelper.ConvertTo2XS(dr["LotRateOnline"]) + "%", ssrq = ConvertHelper.DateToShotStringNull(dr["ListedDate"]), myqhl_xg = mqhl, xtcxcs = "--", gsjj = dr["BriefIntroText"].ToString(), sgr = ConvertHelper.DateToShotStringNull(dr["OnlineStartDate"]), fxjgggr = ConvertHelper.DateToShotStringNull(dr["LotRatePublDate"]), paydateonline = ConvertHelper.DateToShotStringNull(dr["PayDateOnline"]), orderbydate = Convert.ToDateTime(ConvertHelper.DateToShotString(dr["OnlineStartDate"])), updateTime = ConvertHelper.DateToShotString(dtnow) }; List.Add(oj); redis.StringSet(XgKeyDetail + xg.zqdm, xg, TimeSpan.FromSeconds(Convert.ToDouble(Config.RedisExpiry))); } } var _list = List.OrderBy(p => p.orderbydate).ToList(); dicJAarry.Add("1", _list); // redis.StringSet("ZXPendingPurchase2", JsonConvert.SerializeObject(_list)); }
// 김민우: 오토 메일 발송 public void CreateHtml(CompanyInfo company) { StringBuilder sql = new StringBuilder(); String a = "\""; StringBuilder html = new StringBuilder(); // HTML 작성 html.Append(@" <HTML> <HEAD> <TITLE>Xác nhận đăng ký công ty (업체 등록 확인) </TITLE> </HEAD> <BODY> <table> <tr> <td> Công ty mới đã được đăng ký. Hãy kiểm tra. (새로운 업체가 등록 신청 되었습니다. 확인 바랍니다). </td> </tr> <br> <tr><td> Thông tin công ty (업체 정보) </td></tr> <tr><td> Tên công ty (회사명): " + company.CompanyName + @" </td></tr> <tr><td>Mã đăng ký kinh doanh (사업자 등록 번호): " + company.RegNumber1 + @" - " + company.RegNumber2 + @" - " + company.RegNumber3 + @" </td></tr> <tr><td> Số điện thoại (전화 번호): " + company.Phone1 + @" - " + company.Phone2 + @" - " + company.Phone3 + @" </td></tr> <tr><td>Tên người đại diện (대표자명): " + company.MasterName + @" </td></tr> <tr><td> Địa chỉ (주소): " + company.Address + @" </td></tr> <tr><td> Người đăng ký (등록자): " + company.EmployeeName + @" - " + company.EmployeeDept + @" </td></tr> </table> </BODY> </HTML> "); OracleParameter param = new OracleParameter(":CONTENTS", OracleType.Clob); param.Value = Convert.ToString(html); sql.Append(@" INSERT INTO AM_MAIL_MESSAGE (AID, CONFIG_ID, SUBJECT, FROM_NAME, TO_LIST, CC_LIST, MAIL_PRIORITY, MAIL_ATTACH, CONTENTS, FLG_HTML, FLG_SEND, SCHEDULE_DATE) VALUES (SEQ_ALERT_MESSAGE.NEXTVAL, 9999, '[Visit System]A new company has been registered.', 'HmVinaMailMaster:[email protected]', '[email protected],[email protected],[email protected],[email protected]', '' , 'N', '', :CONTENTS,'Y','N', SYSDATE) "); // 2021-03-24 Nguyen Van An: Database에 베트남어와 한국어가 깨져서 영어로 바꿈. (DB không đọc được tiếng việt và tiếng hàn nên thay đề mục hiển thị tiếng anh) //[Khách thăm ra/vào]Công ty mới đã được đăng ký ([내방반출입] 새로운 업체가 등록 신청 되었습니다.) OracleHelper.ExecuteNonQuery(OracleHelper.ConnectionStringLocalTransactionAM, CommandType.Text, sql.ToString(), param); }
//连接按钮 private void button1_Click(object sender, EventArgs e) { button1.Enabled = false; button1.Text = "正在登陆中..."; try { if (checkBox1.Checked) { if (!File.Exists(configFileName)) { File.Create(configFileName, 100); } StringBuilder sb = new StringBuilder(); sb.Append("<?xml version='1.0' encoding='utf-8'?>"); sb.Append("<CodeCreateConfig>"); sb.Append("<DataSource>" + comboBox1.Text + "</DataSource>"); sb.Append("<Type>" + comboBox2.Text + "</Type>"); sb.Append("<ServerName>" + textBox1.Text.Trim() + "</ServerName>"); sb.Append("<DataSourceName>" + comboBox3.Text + "</DataSourceName>"); sb.Append("<UserName>" + textBox3.Text.Trim() + "</UserName>"); sb.Append("<Pwd>" + textBox4.Text.Trim() + "</Pwd>"); sb.Append("</CodeCreateConfig>"); XmlDocument doc = new XmlDocument(); doc.LoadXml(sb.ToString()); doc.Save(configFileName); } else { if (File.Exists(configFileName)) { File.Delete(configFileName); } } } catch (Exception ex) { //MessageBox.Show(ex.Message + "2"); } //连接到数据库 if (comboBox1.SelectedIndex == 0) //sql { if (comboBox2.SelectedIndex == 0) //windows身份验证 { //SqlHelper.connStr = "Data Source=.;Initial Catalog=Tennis;Integrated Security=True"; SqlHelper.connStr = "Data Source=" + textBox1.Text.Trim() + ";Initial Catalog=" + comboBox3.Text.Trim() + ";Integrated Security=True"; } else if (comboBox2.SelectedIndex == 1)//sql身份验证 { SqlHelper.connStr = "server=" + textBox1.Text.Trim() + ";database=" + comboBox3.Text.Trim() + ";uid=" + textBox3.Text.Trim() + ";pwd=" + textBox4.Text.Trim(); } DataTable dt = SqlHelper.GetDataTable(" select * from dbo.sysobjects where xtype = 'U' order by name"); if (dt == null) { MessageBox.Show("数据库连接失敗了!"); return; } OracleHelper.connStr = ""; this.Hide(); MSSqlCreate f3 = new MSSqlCreate(); f3.StartPosition = FormStartPosition.CenterScreen; f3.Show(); } else if (comboBox1.SelectedIndex == 1)//oracle { //OracleHelper.connStr = "Data Source=vipg;User ID=vipgtest; Password=vipg"; OracleHelper.connStr = "Data Source=" + comboBox3.Text.Trim() + ";User ID=" + textBox3.Text.Trim() + ";Password="******""; DataTable dt = OracleHelper.GetDataTable(" select table_name name from user_tables order by table_name "); if (dt != null) { MessageBox.Show("数据库连接失敗了!"); return; } this.Hide(); OracleCreate f2 = new OracleCreate(); f2.StartPosition = FormStartPosition.CenterScreen; f2.Show(); } }
public IActionResult Test() { OracleHelper.InitFunctions(); return(Content("")); }
private bool TryConnectToDataBase() { return(OracleHelper.IsReady()); }
public void GetRules(string StrDevice) { DataSet dsTestItem = new DataSet(); DataSet dsRules = new DataSet(); strInstrument_id = StrDevice; DrTestTimeSignField = null; //检验时间标识 DrTestTimeField = null; //检验时间 DrSampleNoSignField = null; DrSampleNoField = null; DrBarCodeSignField = null; DrBarCodeField = null; DrSampleTypeSignField = null; DrSampleTypeField = null; DrOperatorSignField = null; DrOperatorField = null; DrSpecimenSignField = null; DrSpecimenField = null; DrResultSignField = null; DrResultInfoField = null; DrResultCountField = null; DrSingleResultField = null; DrChannelField = null; DrResultField = null; dsRules = dsHandle.GetDataSet(@"Extractvalue(Column_Value, '/item/item_code') As item_code, Extractvalue(Column_Value, '/item/separated_first') As separated_first, Extractvalue(Column_Value, '/item/no_first') As no_first, Extractvalue(Column_Value, '/item/separated_second') As separated_second, Extractvalue(Column_Value, '/item/no_second') As no_second, Extractvalue(Column_Value, '/item/start_bits') As start_bits, Extractvalue(Column_Value, '/item/length') As length, Extractvalue(Column_Value, '/item/sign') As sign,Extractvalue(Column_Value, '/item/format') As format", "Table(Xmlsequence(Extract((Select 解析规则 From 检验仪器 Where ID = '" + strInstrument_id + "'), '/root/item'))) ", ""); //检验指标通道 //ds_ItemChannel = dsHandle.GetDataSet("通道编码,项目id,nvl(小数位数,2) as 小数位数,nvl(换算比,0) as 换算比", "仪器检测项目", "仪器id = '" + strInstrument_id + "'"); tItemChannel = OracleHelper.GetDataTable(@"Select 通道编码, m.项目id, Nvl(小数位数, 2) As 小数位数, Nvl(换算比, 0) As 换算比, Nvl(加算值, 0) As 加算值, j.结果类型 From 仪器检测项目 m, 检验项目 j Where m.项目id = j.项目id and m.仪器Id='" + StrDevice + "'"); ds_ItemChannel.CaseSensitive = true; //检验图像通道 //ds_GraphChannel = dsHandle.GetDataSet("CHANNEL_NO,GRAPH_TYPE", "TEST_GRAPH_CHANNEL", "instrument_id = '" + strInstrument_id + "'"); //ds_GraphChannel.CaseSensitive = true; FindRow = dsRules.Tables[0].Select("item_code = '01'"); //检验日期标识 if (FindRow.Length != 0) { DrTestTimeSignField = FindRow[0]; } FindRow = dsRules.Tables[0].Select("item_code = '02'"); //检验日期 if (FindRow.Length != 0) { DrTestTimeField = FindRow[0]; } FindRow = dsRules.Tables[0].Select("item_code = '03'"); //常规样本号标识 if (FindRow.Length != 0) { DrSampleNoSignField = FindRow[0]; } FindRow = dsRules.Tables[0].Select("item_code = '04'"); //常规样本号 if (FindRow.Length != 0) { DrSampleNoField = FindRow[0]; } else { DrSampleNoField = null; } FindRow = dsRules.Tables[0].Select("item_code = '05'"); //质控样本号 if (FindRow.Length != 0) { DrQCSampleField = FindRow[0]; } FindRow = dsRules.Tables[0].Select("item_code = '06'"); //条码号标识 if (FindRow.Length != 0) { DrBarCodeSignField = FindRow[0]; } FindRow = dsRules.Tables[0].Select("item_code = '07'"); //条码号 if (FindRow.Length != 0) { DrBarCodeField = FindRow[0]; } FindRow = dsRules.Tables[0].Select("item_code = '08'"); //样本类型标识 if (FindRow.Length != 0) { DrSampleTypeSignField = FindRow[0]; } FindRow = dsRules.Tables[0].Select("item_code = '09'"); //样本类型 if (FindRow.Length != 0) { DrSampleTypeField = FindRow[0]; } FindRow = dsRules.Tables[0].Select("item_code = '10'"); //检验人标识 if (FindRow.Length != 0) { DrOperatorSignField = FindRow[0]; } FindRow = dsRules.Tables[0].Select("item_code = '11'"); //检验人 if (FindRow.Length != 0) { DrOperatorField = FindRow[0]; } FindRow = dsRules.Tables[0].Select("item_code = '12'"); //标本标识 if (FindRow.Length != 0) { DrSpecimenSignField = FindRow[0]; } FindRow = dsRules.Tables[0].Select("item_code = '13'"); //标本 if (FindRow.Length != 0) { DrSpecimenField = FindRow[0]; } FindRow = dsRules.Tables[0].Select("item_code = '14'"); //结果标识 if (FindRow.Length != 0) { DrResultSignField = FindRow[0]; } FindRow = dsRules.Tables[0].Select("item_code = '15'"); //结果信息 if (FindRow.Length != 0) { DrResultInfoField = FindRow[0]; } FindRow = dsRules.Tables[0].Select("item_code = '16'"); //结果数 if (FindRow.Length != 0) { DrResultCountField = FindRow[0]; } FindRow = dsRules.Tables[0].Select("item_code = '17'"); //单个结果 if (FindRow.Length != 0) { DrSingleResultField = FindRow[0]; } FindRow = dsRules.Tables[0].Select("item_code = '18'"); //通道号 if (FindRow.Length != 0) { DrChannelField = FindRow[0]; } FindRow = dsRules.Tables[0].Select("item_code = '19'"); //结果值 if (FindRow.Length != 0) { DrResultField = FindRow[0]; } FindRow = dsRules.Tables[0].Select("item_code = '20'"); //盘号 //if (FindRow.Length != 0) Field_Row[4] = FindRow[0]; FindRow = dsRules.Tables[0].Select("item_code = '21'"); //杯号 //if (FindRow.Length != 0) Field_Row[5] = FindRow[0]; strDevice = OracleHelper.GetDataTable("select 名称 from 检验仪器 where id='" + StrDevice + "'").Rows[0]["名称"].ToString(); strDeviceID = StrDevice; }
/// <summary> /// 请求带条件的操作日志 /// </summary> /// <param name="admin_name"></param> /// <param name="oper_time_start"></param> /// <param name="oper_time_end"></param> /// <param name="nowpage"></param> /// <param name="pagesize"></param> /// <returns></returns> public string requestMoreOperationLog(string admin_name, string oper_time_start, string oper_time_end, int nowpage, int pagesize) { string responseText = ""; string Select_Sql = ""; //条件语句 int count = 0; //条件查询到的记录数 int start = 0; //起始查询条数 int last = 0; //最终查询条数 int pagecount = 0; //总页数 if (!(string.IsNullOrEmpty(admin_name)) && admin_name != "00") { Select_Sql += string.Format(" and (USER_NAME = '{0}')", admin_name); } if (!(string.IsNullOrEmpty(oper_time_start))) { Select_Sql += string.Format(" and to_char(to_date(ADD_TIME,'yyyy-MM-dd HH24:mi:ss'),'yyyy-MM-dd') >='{0}'", oper_time_start); } if (!(string.IsNullOrEmpty(oper_time_end))) { Select_Sql += string.Format(" and to_char(to_date(ADD_TIME,'yyyy-MM-dd HH24:mi:ss'),'yyyy-MM-dd') <='{0}'", oper_time_end); } string sql0 = "select count(*) from GX_OPERATION_LOG where 1=1 and DEL_FLAG='0' " + Select_Sql; DataTable dt0 = OracleHelper.GetTable(sql0, null); count = Convert.ToInt32(dt0.Rows[0][0]); //得到了全部的记录数 if (count % pagesize == 0) //计算得到全部页数 { pagecount = count / pagesize; } else { pagecount = count / pagesize + 1; } if (count == 0) { start = 0; } else { start = (nowpage - 1) * pagesize + 1; //计算该显示记录的行数范围; } if (start <= count) { if (count - start >= pagesize) //start起始 last结束 { last = start + pagesize - 1; } else { last = count; } } string sql = string.Format("select * from(select a.*,rownum row_num from (select * from GX_OPERATION_LOG where 1=1 and DEL_FLAG='0' {0} order by ADD_TIME desc) a) b where b.row_num between {1} and {2}", Select_Sql, start, last); DataTable dt = OracleHelper.GetTable(sql, null); responseText = JsonHelper.getRecordJson(dt); responseText = "[{\"msg\":\"success\",\"count\":" + count.ToString() + ",\"servers\":[" + responseText + "]}]"; return(responseText); }