//查詢標準型態資料 protected void lnkbtn_calStandard_Click(object sender, EventArgs e) { string vd = ddlst_Device.SelectedValue; SQLDB _operator = new SQLDB("VD_STANDARD", "KPT"); DataSet vdStandard = _operator.Select("Vdid = '" + vd + "' AND Vsrdir = '" + ddlst_Vsrdir.SelectedValue + "'", "Vdid , TypeName,Week,Hours", "VD_STANDARD"); RunChart(vdStandard); }
protected override void InitData() { //BindDropDownListData(ddl_department, (DataSet)Application["App_Systm_Department"], "DepartmentName", "DepartmentID"); SQLDB db = new SQLDB(); DataSet ds = db.Select("", "", "role"); this.gv.DataSource = ds; this.gv.DataBind(); }
protected void SearchData() { SQLDB _operator = new SQLDB("DeviceKind"); string conditionName = ddlst_SearchType.SelectedValue; string condition = (txt_Query_Reason.Text.Trim().Length > 0 ? " " + conditionName + " like '%" + txt_Query_Reason.Text.Trim() + "%' " : ""); //condition += query_company; DataSet ds = _operator.Select(condition); Session["DS_MIS"] = ds; gv.DataSource = ds; gv.DataBind(); if (ds.Tables[0].Rows.Count == 0) { ShowMsg2(UpdatePanel1, "查詢無資料"); } }
private bool LoadData() { bool suc = false; SQLDB db = new SQLDB("CD_MaterialOfDevice"); DataSet ds = new DataSet(); ds = db.Select("Device_ID = '"+hidden_DeviceID.Value+"'","","CD_MaterialOfDevice"); if (ds.Tables[0].Rows.Count > 0) { suc = true; gv_Material.DataSource = ds; gv_Material.DataBind(); Session["MIS_Material"] = ds; } return suc; }
protected void File_Upload(object sender, AjaxControlToolkit.AjaxFileUploadEventArgs e) { string filename = e.FileName; string strDestPath = Server.MapPath("../Temp/"); //將資料存到網站 AjaxFileUpload1.SaveAs(@strDestPath + filename); //將資料存到DB SQLDB _operator = new SQLDB(); DataSet ds = _operator.Select("1=0", "", "StudyFileUpload"); DataRow dr = ds.Tables[0].NewRow(); dr["FileName"] = filename; dr["UpdateTime"] = DateTime.Now; ds.Tables[0].Rows.Add(dr); DataSet DSChange = ds.GetChanges(); _operator.Insert(DSChange); }
//修改模式載入資料 private bool LoadData() { bool suc = false; SQLDB _operator = new SQLDB(); if (hidden_Caseid.Value != null ) { DataSet ds = new DataSet(); ds = _operator.Select("caseid = '" + hidden_Caseid.Value + "'", "", "WarrantyNotify"); if (ds.Tables[0].Rows.Count > 0) { suc = true; DataRow dr = ds.Tables[0].Rows[0]; DropDownList_DeviceID.SelectedValue = dr["DeviceID"].ToString(); TextBox_DeviceID_add.Text = dr["DeviceID"].ToString(); //找出保固廠商的廠商編號,因為資料庫只有紀錄廠商名稱 if (dr["WarrantyCompany"].ToString().Length > 0) { DataSet ds_company = (DataSet)Application["App_Mis_Company"]; DataRow[] dr_company = ds_company.Tables[0].Select("CompanyName = '" + dr["WarrantyCompany"].ToString() + "'"); DropDownList_WarrantyCompany_add.SelectedValue = dr_company[0]["CompanyID"].ToString(); } DropDownList_WarrantyContract_add.SelectedValue = dr["WarrantyContract"].ToString(); DropDownList_FaultModel_add.SelectedValue = dr["FaultModel"].ToString(); TextBox_FaultDescribe_add.Text = dr["FaultDescribe"].ToString(); DropDownList_ContractCombineNum.SelectedValue = dr["ContractCombineNum"].ToString(); if (dr["NotifyDate"].ToString().Length > 0) TextBox_NotifyDate_add.Text = Convert.ToDateTime(dr["NotifyDate"].ToString()).ToString("yyyy-MM-dd HH:mm:ss"); if (dr["RepairDeadline"].ToString().Length > 0) { TextBox_RepairDeadline_add.Text = Convert.ToDateTime(dr["RepairDeadline"].ToString()).ToString("yyyy-MM-dd HH:mm:ss"); } DropDownList_RepairDateOption_add.SelectedValue = dr["RepairDateOption"].ToString(); //併案編號 DropDownList_ContractCombineNum.SelectedValue = dr["ContractCombineNum"].ToString(); //重複通知說明 TextBox_RepeatNotify_add.Text = dr["RepeatNotify"].ToString(); } } return suc; }
protected void SearchData() { SQLDB _operator = new SQLDB("VD_DOORSILL", "KPT"); string conditionName = ddlst_SearchType.SelectedValue; string condition = (txt_Query_Reason.Text.Trim().Length > 0 ? " " + conditionName + " like '%" + txt_Query_Reason.Text.Trim() + "%' " : ""); //condition += query_company; DataSet ds = _operator.Select(condition); ////LINQ 測試 //DataSet ds_std = _operator.Select("", "", "VD_DOORSILL"); //DataTable dt = ds_std.Tables[0]; //var std = ( // from std_table in dt.AsEnumerable() // where std_table.Field<string>("DeviceID").ToString().Equals("V012441") // select std_table); //std.Single().SetField("DeviceID", "test"); //if (std.Any()) //{ // DataTable dt_door = std.CopyToDataTable(); // gv.DataSource = dt_door; // gv.DataBind(); //} //else //{ // ShowMsg2(UpdatePanel1, "查詢無資料"); //} Session["DS_MIS"] = ds; if (ds.Tables[0].Rows.Count > 0) { gv.DataSource = ds; gv.DataBind(); } else { ShowMsg2(UpdatePanel1, "查詢無資料"); } }
protected void SearchData() { SQLDB _operator = new SQLDB("VD_ERRORRECORD", "KPT"); string conditionName = ddlst_SearchType.SelectedValue; StringBuilder condition = new StringBuilder(); if (conditionName.Equals("RecordTime") || conditionName.Equals("UpdateTime")) { if (txt_StartDate.Text.Length > 0 && txt_EndDate.Text.Length > 0) { condition.Append(conditionName + "> '" + txt_StartDate.Text + "' AND " + conditionName + "< '" + txt_EndDate.Text + "' "); } else { if (txt_StartDate.Text.Length > 0) { condition.Append(conditionName + "> '" + txt_StartDate.Text + "' "); } else { condition.Append(conditionName + "< '" + txt_EndDate.Text + "' "); } } } else { condition.Append(txt_Query_Reason.Text.Trim().Length > 0 ? " " + conditionName + " like '%" + txt_Query_Reason.Text.Trim() + "%' " : ""); } DataSet ds = _operator.Select(condition.ToString()); Session["DS_MIS"] = ds; gv.DataSource = ds; gv.DataBind(); if (ds.Tables[0].Rows.Count == 0) { ScriptManager.RegisterStartupScript(this, this.GetType(), "", "alert(\"查詢無資料\");", true); //ShowMsg2(UpdatePanel1, "查詢無資料"); } }
protected override void InitData() { SQLDB companyDB = new SQLDB("Company"); DataSet companyDS = companyDB.Select("1=1"); DataTable companyDT = companyDS.Tables[0]; foreach (DataRow companyData in companyDT.Rows) { ListItem company = new ListItem(); company.Value = companyData["CompanyID"].ToString(); company.Text = companyData["CompanyName"].ToString(); ddl_company.Items.Add(company); } }
protected void btn_StockOutSave_Click(object sender, EventArgs e) { SQLDB db = new SQLDB("ICS_StockOut_Item"); if (ddl_MateriName.SelectedIndex == 0) { ShowPageMsg("請選擇物料名稱"); return; } if (txt_ConsumeQuantity.Text.Length == 0) { ShowPageMsg("請輸入領用數量"); return; } //載入即時料價資料與庫存量資料 DataSet ds_ICSInventory = new DataSet(); ds_ICSInventory = db.Select("Materialid = '" + ddl_MateriName.SelectedValue + "'", "", "View_ICS_Inventory"); DataSet ds = new DataSet(); DataTable dt = new DataTable(); if (gv_stockOut_Item.Rows.Count > 0) { ds = (DataSet)Session["ICS_StockOut_Item"]; } else { ds = db.Select("1 =0", "", "ICS_StockOut_Item"); //加入流水號欄位,用在刪除gridview項目用的 ds.Tables[0].Columns.Add("rowindex"); ds.Tables[0].Columns.Add("Quantity"); } DataRow dr = ds.Tables[0].NewRow(); dr["MaterialName"] = ddl_MateriName.SelectedItem.Text; dr["MaterialID"] = ddl_MateriName.SelectedValue; dr["ConsumeQuantity"] = Convert.ToInt32(txt_ConsumeQuantity.Text); //塞入料價與庫存數量 if (ds_ICSInventory.Tables[0].Rows.Count > 0) { DataRow dr_inventory = ds_ICSInventory.Tables[0].Rows[0]; double materialcost = Convert.ToDouble(dr_inventory["MaterialCost"]); dr["StockOutCost"] = (materialcost * Convert.ToDouble(txt_ConsumeQuantity.Text)).ToString("0.000"); dr["Quantity"] = dr_inventory["Quantity"].ToString().Split('.')[0]; dr["MaterialCost"] = Convert.ToDouble(dr_inventory["MaterialCost"]).ToString("0.000"); } ds.Tables[0].Rows.Add(dr); //塞流水號 for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { ds.Tables[0].Rows[i]["rowindex"] = i + 1; } gv_stockOut_Item.DataSource = ds; gv_stockOut_Item.DataBind(); Session["ICS_StockOut_Item"] = ds; }
protected void btn_saveAll_Click(object sender, EventArgs e) { DataSet ds_StockOut_Item = (DataSet)Session["ICS_StockOut_Item"]; #region 驗證入庫物料與計畫編號是否有輸入資料 if (ds_StockOut_Item == null || ddl_ProjectName.SelectedIndex == 0) { ShowMsg2(UpdatePanel1, "請輸入資料"); return; } #endregion if (hidden_Action.Value == "add") { string userid = (string)Session["UserID"]; string stockoutid = ""; SQLDB db = new SQLDB(); DateTime now = DateTime.Now; string Stockout_date = now.ToString("yyyy/MM/dd HH:mm:ss"); #region 新增出庫單 DataSet ds_stockOut_record = db.Select(" 1=0", "", "ICS_StockOut_Record"); DataRow dr_record = ds_stockOut_record.Tables[0].NewRow(); if (ddl_WarrantyNotify.SelectedIndex != 0) { dr_record["CaseID"] = ddl_WarrantyNotify.SelectedValue; } dr_record["ProjectID"] = ddl_ProjectName.SelectedValue; dr_record["ProjectName"] = ddl_ProjectName.SelectedItem.Text; if (ddl_Region.SelectedIndex != 0) { dr_record["RegionID"] = ddl_Region.SelectedValue; } dr_record["StockOutDate"] = txt_StockOutTime.Text; dr_record["StockOutUser"] = userid; dr_record["IsOut"] = false; dr_record["UpdateTime"] = Stockout_date; ds_stockOut_record.Tables[0].Rows.Add(dr_record); DataSet DSChange = ds_stockOut_record.GetChanges(); if (!db.Insert(DSChange)) { ShowMsg(UpdatePanel1, "新增出庫單失敗"); return; } #endregion #region 新增出庫物料 //將出庫單時間紀錄起來,之後利用出庫單時間與登入者帳號抓取入庫單編號 DataSet ds = db.Select("UpdateTime ='" + Stockout_date + "'and StockOutUser ='******' and ProjectID = '" + ddl_ProjectName.SelectedValue + "' ", "", "ICS_StockOut_Record"); if (ds.Tables[0].Rows.Count > 0) { //抓到入庫單編號 stockoutid = ds.Tables[0].Rows[0]["StockOutID"].ToString(); //將入庫單編號塞到StockOutItem裡 for (int i = 0; i < ds_StockOut_Item.Tables[0].Rows.Count; i++) { DataRow dr = ds_StockOut_Item.Tables[0].Rows[i]; dr["StockOutID"] = stockoutid; dr["UpdateTime"] = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"); dr["UpdateUser"] = userid; } //刪除rowindex欄位 ds_StockOut_Item.Tables[0].Columns.Remove("rowindex"); DataSet DSChangeItem = ds_StockOut_Item.GetChanges(); if (db.Insert(DSChangeItem)) ShowMsgAndRedirect(UpdatePanel1, "新增成功", "StockOut_QD.aspx"); else ShowMsg2(UpdatePanel1, "新增失敗"); } #endregion } }
protected void gv_RowCommand(object sender, GridViewCommandEventArgs e) { SQLDB db = new SQLDB(); if (e.CommandName == "singleMapView") { int index = Convert.ToInt32(e.CommandArgument); GridViewRow row = gv.Rows[index]; string id = row.Cells[2].Text; Response.Redirect("mapViewer.aspx?type=Device&id=" + id); } if (e.CommandName == "Search") { int index = Convert.ToInt32(e.CommandArgument); DataSet DS = db.Select("device_id = '" + gv.Rows[index].Cells[2].Text.Replace(" ", "") + "'", " CheckDate desc", "CD_DeviceCheckDate"); //hid_deviceid.Value = gv2.Rows[index].Cells[0].Text.Replace(" ", ""); if (DS.Tables[0].Rows.Count > 0) { Session["MIS_DeviceHeckDate"] = DS; gv3.DataSource = DS; gv3.DataBind(); } else { gv3.DataSource = null; gv3.DataBind(); ShowMsg2(UpdatePanel2, "無資料"); } } }
//取得案件通報資料 public static DataSet GetWarrantyNotify() { SQLDB db = new SQLDB("WarrantyNotify"); return db.Select(); }
protected void Button1_Click(object sender, EventArgs e) { dataType.Value = Request.QueryString["type"].Trim(); string table; SQLDB db; DataSet ds; DataTable dt; string sqlStr = ""; if (dataType.Value.Equals("Device")) { table = "View_ParkingDeviceOnMap"; if (DropDownList1.SelectedValue != "null") sqlStr += "Location LIKE '%%" + DropDownList1.SelectedValue + "%%' "; db = new SQLDB(table); ds = db.Select(sqlStr); if (ds.Tables.Count == 1) { dt = ds.Tables[0]; JSONWebService jsonString = new JSONWebService(); jsonData.Value = jsonString.GetJson(dt); } else { jsonData.Value = "null"; } } else if (dataType.Value.Equals("Case")) { table = "View_ParkingCaseOnMap"; if (DropDownList1.SelectedValue != "null") sqlStr += "Location LIKE '%%" + DropDownList1.SelectedValue + "%%' "; if (TextBox1.Text.Length != 0) { if (sqlStr.Length != 0) sqlStr += "AND "; sqlStr += "RepairDeadline>'" + TextBox1.Text + "' "; } if (TextBox1.Text.Length != 0) { if (sqlStr.Length != 0) sqlStr += "AND "; sqlStr += "RepairDeadline<'" + TextBox2.Text + "' "; } db = new SQLDB(table); ds = db.Select(sqlStr); if (ds.Tables.Count == 1) { dt = ds.Tables[0]; JSONWebService jsonString = new JSONWebService(); jsonData.Value = jsonString.GetJson(dt); } else { jsonData.Value = "null"; } } }
protected void gv_RowCommand(object sender, GridViewCommandEventArgs e) { if (e.CommandName == "singleMapView") { int index = Convert.ToInt32(e.CommandArgument); GridViewRow row = gv.Rows[index]; string id = row.Cells[1].Text; Response.Redirect("../MIS/mapViewer.aspx?type=Case&id=" + id); } //使用零件查詢 if (e.CommandName == "cmd_materialQuery") { gv2.DataSource = null; gv2.DataBind(); int index = Convert.ToInt32(e.CommandArgument); GridViewRow row = gv.Rows[index]; string caseid = row.Cells[1].Text; SQLDB _operator = new SQLDB(); DataSet ds = _operator.Select("caseid = '" + caseid + "'", "", "ICS_MaterialRepaired"); if (ds.Tables[0].Rows.Count > 0) { Session["DS_Material"] = ds; gv2.DataSource = ds; gv2.DataBind(); } else { ShowMsg(UpdatePanel1, "無資料"); } } }
//取得耗材種類資料 public static DataSet GetMaterialType() { SQLDB db = new SQLDB("ICS_Material_Type"); return db.Select(); }
//取得物料管理物料 public static DataSet GetICSMaterial() { SQLDB db = new SQLDB("ICS_Material"); return db.Select(); }
//取得計畫資料 public static DataSet GetProject() { SQLDB db = new SQLDB("ICS_Project"); return db.Select(); }
//取得系統角色 public static DataSet GetRole() { SQLDB db = new SQLDB("Role"); return db.Select(); }
//搜尋入庫單內容與入庫單計畫分攤 private void SearchStockItemProject(string stockinid) { SQLDB _operator = new SQLDB(); //抓取入庫內容 DataSet DS = _operator.Select("stockInID = '" + stockinid + "'", "", "ICS_StockIn_Item"); if (DS.Tables[0].Rows.Count > 0) { gv_stockitem.DataSource = DS; gv_stockitem.DataBind(); } else { gv_stockitem.DataSource = null; gv_stockitem.DataBind(); ShowMsg2(UpdatePanel1, "入庫單內容無資料"); labelView(false); } //抓取計畫分攤成本 DataSet ds_project = _operator.Select("stockInID = '" + stockinid + "'", "", "ICS_Stock_Project"); if (ds_project.Tables[0].Rows.Count > 0) { gv_stock_project.DataSource = ds_project; gv_stock_project.DataBind(); } else { gv_stock_project.DataSource = null; gv_stock_project.DataBind(); ShowMsg2(UpdatePanel1, "計畫分攤無資料"); labelView(false); } }
private DataSet calStandard(string vd, string vsrdir, DateTime start, DateTime end) { //string vd = ddlst_Device.SelectedValue; SQLDB _operator = new SQLDB("VD_DOORSILL", "KPT"); //撈出相對應的異常資料門檻值 DataSet vdDoorSill = _operator.Select("DeviceID = '" + vd + "'", "", "VD_DOORSILL"); //用來存放各VD之標準型態 DataSet dsCaledStd = new DataSet(); //先刪除舊標準型態中與計算出來的有衝突的資料 StringBuilder query = new StringBuilder(); query.Append(" SELECT vdid,vsrdir,avg(speed)as SpeedAvg,isnull(stdev(speed),0) as SpeedStandard, "); query.Append(" avg(laneoccupy) as laneoccupyAvg,isnull(stdev(laneoccupy),0)as LaneOccupyStandard, "); query.Append(" avg(isnull(volume_T,0)+volume_L+volume_S+volume_M) as FlowAvg,isnull(stdev(isnull(volume_T,0)+volume_L+volume_S+volume_M),0)as FlowStandard, "); query.Append(" DatePart(WEEKDAY, datacollecttime) as week,DatePart(HOUR, datacollecttime) as hours "); query.Append(" from VD_Value_his "); query.Append(" where vdid = '" + vd + "' AND vsrdir = '" + vsrdir + "' "); if (vdDoorSill.Tables[0].Rows.Count > 0) { DataRow door = vdDoorSill.Tables[0].Rows[0]; query.Append(" AND (isnull(volume_T,0)+volume_L+volume_S+volume_M) < " + door["FlowTop"].ToString() + " and speed < " + door["SpeedTop"].ToString() + " and laneoccupy < " + door["OccupyTop"].ToString() + " "); query.Append(" AND ((isnull(volume_T,0)+volume_L+volume_S+volume_M) <> " + door["FlowBottom"].ToString() + " and speed <> 0 or (isnull(volume_T,0)+volume_L+volume_S+volume_M) <> " + door["FlowBottom"].ToString() + " and laneoccupy <> 0)"); query.Append(" AND (laneoccupy <> " + door["OccupyBottom"].ToString() + " and speed <> 0 or laneoccupy <> " + door["OccupyBottom"].ToString() + " and (isnull(volume_T,0)+volume_L+volume_S+volume_M) <> 0)"); query.Append(" AND (speed <> laneoccupy and speed <> (isnull(volume_T,0)+volume_L+volume_S+volume_M) and (isnull(volume_T,0)+volume_L+volume_S+volume_M) <> laneoccupy)"); } else { query.Append(" AND (isnull(volume_T,0)+volume_L+volume_S+volume_M) < 60 and speed < 255 and laneoccupy < 100 "); query.Append(" AND ((isnull(volume_T,0)+volume_L+volume_S+volume_M) <> 0 and speed <> 0 or (isnull(volume_T,0)+volume_L+volume_S+volume_M) <> 0 and laneoccupy <> 0) "); query.Append(" AND (laneoccupy <> 0 and speed <> 0 or laneoccupy <> 0 and (isnull(volume_T,0)+volume_L+volume_S+volume_M) <> 0)"); query.Append(" AND (speed <> laneoccupy and speed <> (isnull(volume_T,0)+volume_L+volume_S+volume_M) and (isnull(volume_T,0)+volume_L+volume_S+volume_M) <> laneoccupy) "); } query.Append(" AND datacollecttime between '" + start.ToString("yyyy/MM/dd HH:mm:ss") + "' and '" + end.ToString("yyyy/MM/dd HH:mm:ss") + "'"); query.Append(" GROUP BY vdid,vsrdir,DatePart(WEEKDAY, datacollecttime), DatePart(HOUR, datacollecttime) "); //若是標準型態試算,則算出標準型態並進行資料排序,以利於畫圖表 query.Append(" Order by vdid,DatePart(WEEKDAY, datacollecttime),DatePart(HOUR, datacollecttime) "); dsCaledStd = _operator.SelectQuery(query.ToString()); return dsCaledStd; }
//取得設備合約資料 public static DataSet GetContract() { SQLDB db = new SQLDB("Contract"); return db.Select(); }
protected void Button1_Click(object sender, EventArgs e) { Staff _operator = new Staff("Company"); //物料名稱 String my_MaterialName = txt_MaterialName.Text; //物料單位 String my_MaterialUnit = txt_MaterialUnit.Text; //安全存量 String my_MaterialSafeQuantity = txt_MaterialSafeQuantity.Text; //更新人員 String USER; if (Session["UserID"] != null) { USER = Session["UserID"].ToString(); } else { USER = "******"; } if (hidden_Action.Value == "add") { string sql = "INSERT INTO ICS_Material (MaterialName,MaterialUnit,MaterialSafeQuantity,UpdateTime,UpdateUser,MaterialTypeID) VALUES ('" + my_MaterialName.ToString() + "','" + my_MaterialUnit.ToString() + "','" + my_MaterialSafeQuantity.ToString() + "',getdate(),'" + USER + "','" + cbo_materialType.SelectedValue + "') "; if (_operator.ExecuteStatement(sql)) { ShowMsg2(UpdatePanel1, "儲存成功"); UpdateServerData(); //更新Application Data Response.AddHeader("Refresh", "3; url=Inventory_AQM.aspx"); } else { ShowMsg2(UpdatePanel1, "儲存失敗"); } } else { SQLDB db = new SQLDB(); DataSet ds = new DataSet(); ds = db.Select("NO = '"+hidden_Materialid.Value+"'","","ICS_Material"); DataRow dr = ds.Tables[0].Rows[0]; dr["MaterialName"] = my_MaterialName.ToString(); dr["MaterialUnit"] = my_MaterialUnit.ToString(); dr["MaterialSafeQuantity"] = my_MaterialSafeQuantity.ToString(); dr["UpdateTime"] = DateTime.Now; dr["UpdateUser"] = USER; dr["MaterialTypeID"] = cbo_materialType.SelectedValue; DataSet DSChange = ds.GetChanges(DataRowState.Modified); //string sql = "UPDATE from ICS_Material (MaterialName,MaterialUnit,MaterialSafeQuantity,UpdateTime,UpdateUser,MaterialTypeID) VALUES ('" + my_MaterialName.ToString() + "','" + my_MaterialUnit.ToString() + "','" + my_MaterialSafeQuantity.ToString() + "',getdate(),'" + USER + "','" + cbo_materialType.SelectedValue + "') "; if (db.Update(DSChange)) { ShowMsg2(UpdatePanel1, "儲存成功"); UpdateServerData(); //更新Application Data Response.AddHeader("Refresh", "3; url=Inventory_AQM.aspx"); } else { ShowMsg2(UpdatePanel1, "儲存失敗"); } } }
protected bool LoadData() { SQLDB deviceModel = new SQLDB("DeviceKind"); FieldEdit(false); bool suc = false; string rowfilter = "DeviceKindId=" + hidden_EquipmentKindId.Value; //直接去資料庫抓 deviceModelDS = deviceModel.Select(rowfilter); if (deviceModelDS.Tables[0].Rows.Count == 1) { DataTable dt = deviceModelDS.Tables[0]; DataRow dr = dt.Rows[0]; txt_equipmentKindId.Text = dr["DeviceKindId"].ToString(); txt_equipmentKind.Text = dr["DeviceKind"].ToString(); txt_DeviceKindName.Text = dr["DeviceKindName"].ToString(); image.ImageUrl = iconPath + dr["DeviceKindId"].ToString() + ".gif"; Session["DS_Mis"] = deviceModelDS; suc = true; } return suc; }
protected override void InitData() { imagePath.Value = ConfigurationManager.AppSettings["imagesUrl"]; SQLDB sectorDB = new SQLDB("View_AllLocation"); DataSet sectorDS = sectorDB.Select(); DataTable sectorDT = sectorDS.Tables[0]; ListItem defaultItem = new ListItem(); defaultItem.Text = "無"; defaultItem.Value = "null"; DropDownList1.Items.Add(defaultItem); foreach(DataRow sectorData in sectorDT.Rows){ ListItem sector = new ListItem(); sector.Text = sectorData["Location"].ToString(); sector.Value = sectorData["Location"].ToString(); DropDownList1.Items.Add(sector); } if (Request.QueryString["type"].Trim() != null && Request.QueryString["id"].Trim() != null) { dataType.Value = Request.QueryString["type"].Trim(); string id = Request.QueryString["id"].Trim(); string table; SQLDB db; DataSet ds; DataTable dt; if (dataType.Value.Equals("Device")) { //設定修復期限是否要顯示 TextBox1.Visible = false; TextBox2.Visible = false; Button1.Visible = false; lbl_repairend.Visible = false; lbl2.Visible = false; TextBox1.Enabled = false; TextBox2.Enabled = false; Image1.Visible = false; Image2.Visible = false; table = "View_ParkingDeviceOnMap"; db = new SQLDB(table); if (id.Equals("0")) { ds = db.Select(); if (ds.Tables.Count == 1) { dt = ds.Tables[0]; JSONWebService jsonString = new JSONWebService(); jsonData.Value = jsonString.GetJson(dt); } else { jsonData.Value = "null"; } } else { ds = db.Select("Device_ID='" + id + "'"); if (ds.Tables.Count == 1) { dt = ds.Tables[0]; JSONWebService jsonString = new JSONWebService(); jsonData.Value = jsonString.GetJson(dt); } else { jsonData.Value = "null"; } } } else if (dataType.Value.Equals("Case")) { Button2.Visible = false; table = "View_ParkingCaseOnMap"; db = new SQLDB(table); if (id.Equals("0")) { ds = db.Select("status = '維修中'"); dt = ds.Tables[0]; JSONWebService jsonString = new JSONWebService(); jsonData.Value = jsonString.GetJson(dt); } else { ds = db.Select("CaseID='" + id + "'"); dt = ds.Tables[0]; JSONWebService jsonString = new JSONWebService(); jsonData.Value = jsonString.GetJson(dt); } } } }
protected void cmd_Save_Click1(object sender, EventArgs e) { SQLDB deviceModel = new SQLDB("DeviceKind"); if (hidden_Action.Value.Equals("add")) { DataSet DS = new DataSet(); DataTable DT = new DataTable("DeviceKind"); DT.Columns.Add("DeviceKindId"); DT.Columns.Add("DeviceKind"); DT.Columns.Add("DeviceKindName"); DS.Tables.Add(DT); DataRow DR = DS.Tables[0].NewRow(); DR["DeviceKindId"] = deviceModel.Select().Tables[0].Rows.Count + 1; DR["DeviceKind"] = txt_equipmentKind.Text; DR["DeviceKindName"] = txt_DeviceKindName.Text; DS.Tables[0].Rows.Add(DR); DataSet _changed = DS.GetChanges(); if (deviceModel.Insert(_changed)) { ReDirect("新增成功"); } else { ReDirect("新增失敗"); } } else if (hidden_Action.Value.Equals("edit")){ DataSet ds = (DataSet)Session["DS_Mis"]; if (ds != null){ ds.Tables[0].Rows[0]["DeviceKind"] = txt_equipmentKind.Text; ds.Tables[0].Rows[0]["DeviceKindName"] = txt_DeviceKindName.Text; DataSet DSChange = ds.GetChanges(DataRowState.Modified); if (deviceModel.Update(DSChange)) { ReDirect("修改成功"); } else { ReDirect("修改失敗"); } } } }
protected void lnkbtn_saveStandardall_Click(object sender, EventArgs e) { SQLDB _operator = new SQLDB("VD_Info", "KPT"); string query = "select vdid from VD_Info group by vdid"; DataSet ds_allvd = _operator.SelectQuery(query); for (int f = 10; f < ds_allvd.Tables[0].Rows.Count; f++) { int vsrdir2 = 2; for (int k = 0; k < vsrdir2; k++) { DataSet dsCaledStd = new DataSet(); dsCaledStd = calStandard(ds_allvd.Tables[0].Rows[f]["vdid"].ToString(), k.ToString(), new DateTime(2014, 06, 09, 12, 0, 0), new DateTime(2014, 07, 09, 12, 0, 0)); if (dsCaledStd.Tables[0].Rows.Count > 0) { DataSet ds_standard = _operator.Select("", "", "VD_STANDARD"); DateTime updatetime = DateTime.Now; DataSet ds_standardAdd = _operator.Select("1=0", "", "VD_STANDARD"); DataSet ds_standardAddHis = _operator.Select("1=0", "", "VD_STANDARD_HIS"); for (int i = 0; i < dsCaledStd.Tables[0].Rows.Count; i++) { DataRow dr = dsCaledStd.Tables[0].Rows[i]; string vdid = dr["vdid"].ToString(); string vsrdir = dr["vsrdir"].ToString(); string week = dr["week"].ToString(); string hours = dr["hours"].ToString(); DataRow[] dr_std = ds_standard.Tables[0].Select("Vdid = '" + vdid + "' AND Vsrdir = '" + vsrdir + "' AND Week = '" + week + "' AND Hours = '" + hours + "' "); DataRow dr_add = ds_standardAdd.Tables[0].NewRow(); dr_add["Vdid"] = vdid; dr_add["TypeName"] = "Default"; dr_add["Vsrdir"] = vsrdir; dr_add["Hours"] = hours; dr_add["Week"] = week; dr_add["SpeedAvg"] = dr["SpeedAvg"].ToString(); dr_add["SpeedStandard"] = dr["SpeedStandard"].ToString(); dr_add["LaneOccupyAvg"] = dr["LaneOccupyAvg"].ToString(); dr_add["LaneOccupyStandard"] = dr["LaneOccupyStandard"].ToString(); dr_add["FlowAvg"] = dr["FlowAvg"].ToString(); dr_add["FlowStandard"] = dr["FlowStandard"].ToString(); dr_add["Times"] = 2; dr_add["UpdateTime"] = updatetime; ds_standardAdd.Tables[0].Rows.Add(dr_add); //將有衝突的標準型態備份到VD_STANDARD_HIS if (dr_std.Length > 0) { DataRow dr_addhis = ds_standardAddHis.Tables[0].NewRow(); dr_addhis["StandardNum"] = dr_std[0]["StandardNum"]; dr_addhis["Vdid"] = vdid; dr_addhis["TypeName"] = "Default"; dr_addhis["Vsrdir"] = vsrdir; dr_addhis["Hours"] = hours; dr_addhis["Week"] = week; dr_addhis["SpeedAvg"] = dr_std[0]["SpeedAvg"].ToString(); dr_addhis["SpeedStandard"] = dr_std[0]["SpeedStandard"].ToString(); dr_addhis["LaneOccupyAvg"] = dr_std[0]["LaneOccupyAvg"].ToString(); dr_addhis["LaneOccupyStandard"] = dr_std[0]["LaneOccupyStandard"].ToString(); dr_addhis["FlowAvg"] = dr_std[0]["FlowAvg"].ToString(); dr_addhis["FlowStandard"] = dr["FlowStandard"].ToString(); dr_addhis["Times"] = 2; dr_addhis["UpdateTime"] = updatetime; ds_standardAddHis.Tables[0].Rows.Add(dr_addhis); dr_std[0].Delete(); } } DataSet DSChange = ds_standard.GetChanges(DataRowState.Deleted); if (DSChange != null) { if (_operator.Delete(DSChange)) { // ShowMsg(UpdatePanel1, "資料刪除成功"); } } DataSet DSChangeAdd = ds_standardAdd.GetChanges(); DataSet DSChangeAddHis = ds_standardAddHis.GetChanges(); bool suc = false; if (DSChangeAdd != null) { if (_operator.Insert(DSChangeAdd)) { suc = true; } } if (DSChangeAddHis != null) { if (_operator.Insert(DSChangeAdd)) { suc = true; } } //if (suc) //{ // ShowMsg(UpdatePanel2, "建立成功"); //} } } } ShowMsg(UpdatePanel2, "建立成功"); }
protected void cmd_Save_Click(object sender, EventArgs e) { //_operator.Device_ID = txt_Device_ID.Text; ////if (cbo_deviceModel.SelectedValue != null) ////{ //// _operator.DeviceModel = Convert.ToInt32(cbo_deviceModel.SelectedValue); ////} //if (cbo_region.SelectedValue != null) //{ // _operator.Area_ID = Convert.ToInt32(cbo_region.SelectedValue); //} //_operator.Location = txt_sectorName.Text; ////_operator.TCModel = txt_tcModel.Text; //if (cbo_deviceContractID.SelectedValue != null) //{ // _operator.DeviceContractID = Convert.ToInt32(cbo_deviceContractID.SelectedValue); //} ////_operator.Status = txt_deviceStatus.Text; //_operator.DeviceNote = txt_deviceNote.Text; //if (txt_longitude.Text.Length > 0) // _operator.Gis_X= txt_longitude.Text; //if (txt_latitude.Text.Length > 0) // _operator.Gis_Y = txt_latitude.Text; ////if (imageFile.HasFile) { //// string FileName = imageFile.FileName; //// string FileType = System.IO.Path.GetExtension(FileName); //// Match match = Regex.Match(FileType,"(jpg|png|gif|JPG|PNG|GIF)"); //// if (match.Success) //// { //// if (image.ImageUrl.Length > 0) //// { //// string existedFilename = System.IO.Path.GetFileName(image.ImageUrl); //// System.IO.FileInfo file = new System.IO.FileInfo(ConfigurationManager.AppSettings["imageUploadFolder"] + existedFilename); //// if (file.Exists) //// { //// file.Delete(); //// } //// } //// _operator.DevicePhoto = DateTime.Now.ToString("yyyyMMddHHmmss") + FileType; //// string mappath = ConfigurationManager.AppSettings["imageUploadFolder"] + _operator.DevicePhoto; //// imageFile.SaveAs(Server.MapPath(mappath)); //// } //// else //// { //// ReDirect("上傳檔案格式錯誤"); //// return; //// } ////} //// if (txt_Device_ID.Text.Length == 0) { ShowPageMsg("請輸入設備編號"); return; } if (txt_Comm_Server_IP.Text.Length == 0) { ShowPageMsg("請輸入通訊伺服器IP"); return; } if (txt_sectorName.Text.Length== 0) { ShowPageMsg("請輸入路口名稱"); return; } if (txt_longitude.Text.Length == 0) { ShowPageMsg("請輸入經度資料"); return; } if (txt_latitude.Text.Length==0) { ShowPageMsg("請輸入緯度資料"); return; } if (cbo_region.SelectedValue == "null") { ShowPageMsg("請選擇地區"); return; } if (cbo_DeviceModel.SelectedValue == "null") { ShowPageMsg("請選擇設備種類"); return; } //if (txt_Attribute.Text.Length == 0) //{ // ShowPageMsg("請輸入屬性資料"); // return; //} //if (txt_Status.Text.Length == 0) //{ // ShowPageMsg("請輸入狀態"); // return; //} //if (txt_Receive_Port.Text.Length == 0) //{ // ShowPageMsg("請輸入接收Port"); // return; //} //if (txt_Remote_IP.Text.Length == 0) //{ // ShowPageMsg("請輸入遠端IP"); // return; //} //if (txt_Remote_Port.Text.Length == 0) //{ // ShowPageMsg("請輸入遠端Port"); // return; //} //if (txt_Phone_Number.Text.Length == 0) //{ // ShowPageMsg("請輸入電話號碼"); // return; //} //if (txt_Equipment_ID.Text.Length == 0) //{ // ShowPageMsg("請輸入設備ID"); // return; //} if (hidden_Action.Value.Equals("add")) { SQLDB _operator = new SQLDB(); DataSet ds_add = _operator.Select("1=0", "", "Device_Config"); DataRow dr = ds_add.Tables[0].NewRow(); dr["Device_ID"] = txt_Device_ID.Text; if (cbo_region.SelectedValue != "null") { dr["Area_ID"] = Convert.ToInt32(cbo_region.SelectedValue); } dr["Comm_Server_IP"] = txt_Comm_Server_IP.Text; dr["Device_Kind"] = cbo_DeviceModel.SelectedItem.Text; dr["DeviceModel"] = cbo_DeviceModel.SelectedValue; dr["Location"] = txt_sectorName.Text; if (cbo_deviceContractID.SelectedValue != "null") { dr["DeviceContractID"] = cbo_deviceContractID.SelectedValue; } dr["Gis_X"] = txt_longitude.Text; dr["Gis_Y"] = txt_latitude.Text; //dr["Equipment_ID"] = txt_Equipment_ID.Text; //dr["Attribute"] = txt_Attribute.Text; //dr["Status"] = txt_Status.Text; //dr["Receive_Port"] = Convert.ToInt32(txt_Receive_Port.Text); //dr["Remote_IP"] = txt_Remote_IP.Text; //dr["Remote_Port"] = Convert.ToInt32(txt_Remote_Port.Text); //dr["Phone_Number"] = txt_Phone_Number.Text; ds_add.Tables[0].Rows.Add(dr); DataSet DSChange = ds_add.GetChanges(); if (_operator.Insert(DSChange)) { ReDirect("新增成功"); UpdateServerData(); } else { ShowPageMsg("新增失敗"); } } else if (hidden_Action.Value.Equals("edit")) { bool suc = false; //第一種方法,直接GetChange DataSet ds = _operator.Select("Device_ID = '" + hidden_Device_ID.Value + "'", "", "Device_Config"); if (ds != null) { DataRow dr = ds.Tables[0].Rows[0]; dr["Device_ID"] = txt_Device_ID.Text; dr["Comm_Server_IP"] = txt_Comm_Server_IP.Text; //dr["DeviceModel"] = _operator.DeviceModel; dr["Area_ID"] = cbo_region.SelectedValue; dr["Location"] = txt_sectorName.Text; //dr["TCModel"] = _operator.TCModel; dr["DeviceContractID"] = cbo_deviceContractID.SelectedValue; //dr["Status"] = _operator.Status; //dr["DeviceNote"] = _operator.DeviceNote; dr["Gis_X"] = txt_longitude.Text; dr["Gis_Y"] = txt_latitude.Text; //if(imageFile.HasFile) // dr["DevicePhoto"] = _operator.DevicePhoto; //dr["Equipment_ID"] = txt_Equipment_ID.Text; //dr["Attribute"] = txt_Attribute.Text; //dr["Status"] = txt_Status.Text; //dr["Receive_Port"] = Convert.ToInt32(txt_Receive_Port.Text); //dr["Remote_IP"] = txt_Remote_IP.Text; //dr["Remote_Port"] = Convert.ToInt32(txt_Remote_Port.Text); //dr["Phone_Number"] = txt_Phone_Number.Text; dr["Device_Kind"] = cbo_DeviceModel.SelectedItem.Text; dr["DeviceModel"] = cbo_DeviceModel.SelectedValue; DataSet DSChange = ds.GetChanges(DataRowState.Modified); suc = _operator.Update(DSChange); } //第二種方法,直接傳資料給class檔,至資料庫抓資料再更新 //_operator.Breakdown_Type_ID = Convert.ToInt32(hidden_BreakDown_Type_ID.Value); //_operator.Breakdown_Type = txt_BreakDown_Type.Text.Trim(); //suc = _operator.EditBreakDownType(_operator); if (suc) { ReDirect("修改成功"); UpdateServerData(); } else { ShowPageMsg("修改失敗"); } } }
//private void ExportTOExcel() //{ // ExcelReport report = null; // try // { // report = new ExcelReport("MIS/EquipmentData"); // DataSet ds = (DataSet)Session["DS_MIS"]; // for (int j = 0; j < ds.Tables[0].Rows.Count; j++) // { // report.FillData(j + 3, 1, ds.Tables[0].Rows[j]["DeviceID"].ToString(), 1); // report.FillData(j + 3, 2, ds.Tables[0].Rows[j]["DeviceModelName"].ToString(), 1); // report.FillData(j + 3, 3, ds.Tables[0].Rows[j]["RegionName"].ToString(), 1); // report.FillData(j + 3, 4, ds.Tables[0].Rows[j]["SectorName"].ToString(), 1); // report.FillData(j + 3, 5, ds.Tables[0].Rows[j]["TCModel"].ToString(), 1); // report.FillData(j + 3, 6, ds.Tables[0].Rows[j]["ContractName"].ToString(), 1); // report.FillData(j + 3, 7, ds.Tables[0].Rows[j]["DeviceStatus"].ToString(), 1); // report.FillData(j + 3, 8, ds.Tables[0].Rows[j]["DeviceNote"].ToString(), 1); // report.FillData(j + 3, 9, ds.Tables[0].Rows[j]["Longitude"].ToString(), 1); // report.FillData(j + 3, 10, ds.Tables[0].Rows[j]["Latitude"].ToString(), 1); // //report.FillData(j + 1, 2, ds.Tables[0].Rows[j]["DevicePhoto"].ToString(), 1); // } // ScriptManager.RegisterStartupScript(this, this.GetType(), "newprintwindow2", "open_new_window(\"../Temp/" + report.Report() + "\");", true); // } // catch (Exception e) // { // if (report != null) // { // ScriptManager.RegisterStartupScript(this, this.GetType(), "newprintwindow2", "open_new_window(\"../Temp/" + report.Report() + "\");", true); // } // } // finally // { // try // { // report.Close(); // } // catch (Exception ex) // { // } // } //} /// <summary> /// 重新計算檢修周期:以設備合約起始日期開始累加檢修週期直到合約截止 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void lnkbtn_Add_Click(object sender, EventArgs e) { SQLDB db = new SQLDB(); //先將舊有檢修日期刪除 string deleteCheckDate = "Delete from CD_DeviceCheckDate where 1=1"; db.ExecuteStatement(deleteCheckDate); string queryDevice = "SELECT [Device_ID],[ContractStartDate],[ContractEndDate],[Cycle] FROM [View_DeviceConfig] where [ContractStartDate] is not null and [ContractEndDate] is not null"; DataSet ds = db.SelectQuery(queryDevice); DataSet ds_checkdate = db.Select("1 = 0", "", "CD_DeviceCheckDate"); for (int i = 0; i < ds.Tables[0].Rows.Count ; i++) { string device = ds.Tables[0].Rows[i]["Device_ID"].ToString(); DateTime start = Convert.ToDateTime(ds.Tables[0].Rows[i]["ContractStartDate"].ToString()); DateTime end = Convert.ToDateTime(ds.Tables[0].Rows[i]["ContractEndDate"].ToString()); TimeSpan cycle = TimeSpan.FromDays( Convert.ToDouble( ds.Tables[0].Rows[i]["cycle"].ToString())); while (start < end) { start += cycle; if (start < end) { DataRow dr = ds_checkdate.Tables[0].NewRow(); dr["Device_ID"] = ds.Tables[0].Rows[i]["Device_ID"].ToString(); dr["CheckDate"] = start.ToString("yyyy/MM/dd HH:mm:ss"); ds_checkdate.Tables[0].Rows.Add(dr); } } } //開始將檢修日期塞到資料庫 DataSet DSChange = ds_checkdate.GetChanges(); if (db.Insert(DSChange)) { ShowMsg(UpdatePanel1, "資料計算成功"); } }
protected override void InitData() { SQLDB _operator = new SQLDB(); DataSet ds_area = _operator.Select("", "", "Area_Data"); BindDropDownListData(cbo_region, ds_area, "AreaName", "AreaID"); //BindDropDownListData(cbo_deviceModel, (DataSet)Application["App_Mis_DeviceModel"], "DeviceModel", "DeviceModelId"); string query_contract = " select ('('+ContractNum+')'+ContractName) as ContractCombine,* from Contract "; DataSet ds_contract = _operator.SelectQuery(query_contract); BindDropDownListData(cbo_deviceContractID, ds_contract, "ContractCombine", "ContractId"); DataSet ds_DeviceModel = _operator.Select("", "", "DeviceKind"); BindDropDownListData(cbo_DeviceModel, ds_DeviceModel, "DeviceKindName", "DeviceKindId"); }