protected override void InitData() { SQLDB _operator = new SQLDB("VD_Info", "KPT"); string query = "select vdid from VD_Info group by vdid"; DataSet ds = _operator.SelectQuery(query); for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { ListItem vditem = new ListItem(); vditem.Value = ds.Tables[0].Rows[i]["vdid"].ToString(); vditem.Text = ds.Tables[0].Rows[i]["vdid"].ToString(); ddlst_Device.Items.Add(vditem); } }
protected void SearchData() { gv.DataSource = null; gv.DataBind(); SQLDB db = new SQLDB(); StringBuilder query = new StringBuilder(" select * from "); query.Append(" (SELECT DeviceID,COUNT(*) as counts "); query.Append(" FROM WarrantyNotify "); string conditionName = ddlst_SearchType.SelectedValue; if (txt_startDateTime.Text.Length > 0 || txt_endDateTime.Text.Length > 0) { query.Append(" WHERE "); if (txt_startDateTime.Text.Length > 0 && txt_endDateTime.Text.Length > 0) { query.Append(" NotifyDate >= '" + txt_startDateTime.Text + "' and NotifyDate <= '" + txt_endDateTime.Text + "' "); } else if (txt_startDateTime.Text.Length > 0) { query.Append(" NotifyDate >= '" + txt_startDateTime.Text + "' "); } else if (txt_endDateTime.Text.Length > 0) { query.Append(" NotifyDate <= '" + txt_endDateTime.Text + "' "); } } query.Append(" GROUP BY deviceid) w left join View_DeviceConfig d on w.deviceid = d.Device_ID "); if (txt_Query_Reason.Text.Trim().Length > 0) { query.Append(" where "+conditionName + " like '%" + txt_Query_Reason.Text.Trim() + "%' "); } query.Append("order by counts desc"); DataSet ds = db.SelectQuery(query.ToString()); //Session["DS_MIS"] = ds; gv.DataSource = ds; gv.DataBind(); if (ds.Tables[0].Rows.Count == 0) { ShowMsg2(UpdatePanel1, "查詢無資料"); } }
private bool LoadData() { txt_MaterialName.Enabled = false; SQLDB _operator = new SQLDB("ICS_Material"); bool suc = false; string query = "select * from ICS_Material m left join ICS_Material_Type mt on m.MaterialTypeID = mt.MaterialTypeID where NO = '" + hidden_Materialid.Value + "'"; DataSet ds2 = _operator.SelectQuery(query); if (ds2.Tables[0].Rows.Count == 1) { suc = true; DataTable dt = ds2.Tables[0]; DataRow dr = dt.Rows[0]; txt_MaterialName.Text = dr["MaterialName"].ToString(); cbo_materialType.SelectedValue = dr["MaterialTypeID"].ToString(); txt_MaterialSafeQuantity.Text = dr["MaterialSafeQuantity"].ToString(); txt_MaterialUnit.Text = dr["MaterialUnit"].ToString(); } return suc; }
protected void SearchData() { gv.DataSource = null; gv.DataBind(); SQLDB db = new SQLDB(); StringBuilder query = new StringBuilder(" SELECT WarrantyCompany,CONVERT(float, AVG(datediff(MINUTE,NotifyDate,RepairDate)))/1440 as avgtime FROM WarrantyNotify "); query.Append(" where NotifyDate is not null and RepairDate is not null and datediff(MINUTE,NotifyDate,RepairDate)> 0 "); string conditionName = ddlst_SearchType.SelectedValue; StringBuilder condition = new StringBuilder(); bool isquery = false; if (txt_Query_Reason.Text.Trim().Length > 0) { condition.Append( conditionName + " like '%" + txt_Query_Reason.Text.Trim() + "%' and"); isquery = true; } if (txt_startDateTime.Text.Length > 0) { condition.Append(" NotifyDate >= '" + txt_startDateTime.Text + "' and"); isquery = true; } if (txt_endDateTime.Text.Length > 0) { condition.Append(" NotifyDate <= '" + txt_endDateTime.Text + "' and"); isquery = true; } if (isquery) { query.Append(" and "+ condition.ToString().Substring(0, condition.ToString().Length - 3)); } query.Append(" GROUP BY WarrantyCompany order by avgtime desc"); DataSet ds = db.SelectQuery(query.ToString()); //Session["DS_MIS"] = ds; gv.DataSource = ds; gv.DataBind(); if (ds.Tables[0].Rows.Count == 0) { ShowMsg2(UpdatePanel1, "查詢無資料"); } }
protected void SearchData() { //string query_company = GetCompanyScope(); StringBuilder query = new StringBuilder("select * from ICS_Material m left join ICS_Material_Type mt on m.MaterialTypeID = mt.MaterialTypeID left join ICS_Stock_QuantityNow sq on m.NO = sq.MaterialID"); SQLDB _operator = new SQLDB(); if (txt_Query_Reason.Text.Trim().Length > 0) { query.Append(" WHERE " + ddlst_SearchType.SelectedValue + " like '%" + txt_Query_Reason.Text.Trim() + "%'"); } DataSet ds = _operator.SelectQuery(query.ToString()); Session["DS_MIS"] = ds; gv.DataSource = ds; gv.DataBind(); if (ds.Tables[0].Rows.Count == 0) { ShowMsg2(UpdatePanel1, "查詢無資料"); } }
protected void SearchData() { gv.DataSource = null; gv.DataBind(); SQLDB db = new SQLDB(); StringBuilder query = new StringBuilder(" SELECT FaultDescribe,COUNT(*) as counts FROM WarrantyNotify "); string conditionName = ddlst_SearchType.SelectedValue; StringBuilder condition = new StringBuilder(); bool isquery = false; if (txt_Query_Reason.Text.Trim().Length > 0) { condition.Append(conditionName + " like '%" + txt_Query_Reason.Text.Trim() + "%' and"); isquery = true; } if (txt_startDateTime.Text.Length > 0) { condition.Append(" NotifyDate >= '" + txt_startDateTime.Text + "' and"); isquery = true; } if (txt_endDateTime.Text.Length > 0) { condition.Append(" NotifyDate <= '" + txt_endDateTime.Text + "' and"); isquery = true; } if (isquery) { query.Append(" WHERE " + condition.ToString().Substring(0, condition.ToString().Length - 3)); } query.Append(" GROUP BY FaultDescribe order by counts desc"); DataSet ds = db.SelectQuery(query.ToString()); //Session["DS_MIS"] = ds; gv.DataSource = ds; gv.DataBind(); if (ds.Tables[0].Rows.Count == 0) { ShowMsg2(UpdatePanel1, "查詢無資料"); } }
protected void SearchData() { gv.DataSource = null; gv.DataBind(); SQLDB db = new SQLDB(); StringBuilder query = new StringBuilder(" select * "); query.Append(" from View_ICS_InventoryCost "); string conditionName = ddlst_SearchType.SelectedValue; StringBuilder condition = new StringBuilder(); bool isquery = false; if (txt_Query_Reason.Text.Trim().Length > 0) { condition.Append(conditionName + " like '%" + txt_Query_Reason.Text.Trim() + "%' and"); isquery = true; } if (txt_startDateTime.Text.Length > 0) { condition.Append(" si.UpdateTime >= '" + txt_startDateTime.Text + "' and"); isquery = true; } if (txt_endDateTime.Text.Length > 0) { condition.Append(" si.UpdateTime <= '" + txt_endDateTime.Text + "' and"); isquery = true; } if (isquery) { query.Append(" WHERE " + condition.ToString().Substring(0, condition.ToString().Length - 3)); } DataSet ds = db.SelectQuery(query.ToString()); //Session["DS_MIS"] = ds; gv.DataSource = ds; gv.DataBind(); if (ds.Tables[0].Rows.Count == 0) { ShowMsg2(UpdatePanel1, "查詢無資料"); } }
protected void SearchData() { gv.DataSource = null; gv.DataBind(); SQLDB db = new SQLDB(); StringBuilder query = new StringBuilder(" select DeviceID,isnull(AVG(ABS(datediff(day,NotifyDate,RepairDate))),0) as RepairAvgDay FROM WarrantyNotify "); string conditionName = ddlst_SearchType.SelectedValue; StringBuilder condition = new StringBuilder(); bool isquery = false; if (txt_Query_Reason.Text.Trim().Length > 0) { condition.Append(conditionName + " like '%" + txt_Query_Reason.Text.Trim() + "%' and"); isquery = true; } if (txt_startDateTime.Text.Length > 0) { condition.Append(" NotifyDate >= '" + txt_startDateTime.Text + "' and"); isquery = true; } if (txt_endDateTime.Text.Length > 0) { condition.Append(" NotifyDate <= '" + txt_endDateTime.Text + "' and"); isquery = true; } if (isquery) { query.Append(" WHERE " + condition.ToString().Substring(0, condition.ToString().Length - 3)); } query.Append(" group by DeviceID order by RepairAvgDay desc"); DataSet ds = db.SelectQuery(query.ToString()); gv.DataSource = ds; gv.DataBind(); if (ds.Tables[0].Rows.Count == 0) { ShowMsg2(UpdatePanel1, "查詢無資料"); } }
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, "建立成功"); }
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; }
protected void cmd_ExportExcel_Click(object sender, EventArgs e) { SQLDB db = new SQLDB(); StringBuilder query = new StringBuilder(" select DeviceID as 設備編號,isnull(AVG(ABS(datediff(day,NotifyDate,RepairDate))),0) as 平均修復天數 FROM WarrantyNotify "); string conditionName = ddlst_SearchType.SelectedValue; StringBuilder condition = new StringBuilder(); bool isquery = false; if (txt_Query_Reason.Text.Trim().Length > 0) { condition.Append(conditionName + " like '%" + txt_Query_Reason.Text.Trim() + "%' and"); isquery = true; } if (txt_startDateTime.Text.Length > 0) { condition.Append(" NotifyDate >= '" + txt_startDateTime.Text + "' and"); isquery = true; } if (txt_endDateTime.Text.Length > 0) { condition.Append(" NotifyDate <= '" + txt_endDateTime.Text + "' and"); isquery = true; } if (isquery) { query.Append(" WHERE " + condition.ToString().Substring(0, condition.ToString().Length - 3)); } query.Append(" Group by DeviceID order by isnull(AVG(ABS(datediff(day,NotifyDate,RepairDate))),0) desc "); DataSet ds = db.SelectQuery(query.ToString()); if (ds.Tables[0].Rows.Count > 2000) { ShowMsg2(UpdatePanel1, "查詢筆數過多,請重新調整查詢條件"); return; } HSSFWorkbook workbook = new HSSFWorkbook(); MemoryStream ms = new MemoryStream(); HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("Sheet01"); sheet.SetColumnWidth(0, 48 * 255); sheet.SetColumnWidth(1, 48 * 255); //sheet.DisplayGridlines = true; // 設定儲存格樣式-黑框線,跨欄置中 ICellStyle style2 = workbook.CreateCellStyle(); style2.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; style2.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; style2.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; style2.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; style2.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; style2.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; style2.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; style2.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; style2.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; style2.Alignment = HorizontalAlignment.Center; style2.VerticalAlignment = VerticalAlignment.Center; style2.WrapText = true; // 設定儲存格樣式-黑框線,跨欄置中,標題 ICellStyle style3 = workbook.CreateCellStyle(); style3.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; style3.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; style3.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; style3.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; style3.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; style3.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; style3.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; style3.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; style3.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; style3.Alignment = HorizontalAlignment.Center; style3.VerticalAlignment = VerticalAlignment.Center; style3.WrapText = true; NPOI.SS.UserModel.IFont font = workbook.CreateFont(); //以下為設定粗體字,字體大小 font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; font.FontHeightInPoints = 20; style3.SetFont(font); //插入標題列 IRow row = sheet.CreateRow(0); row.Height = 32 * 20; //標題列增高為32 ICell cell2 = row.CreateCell(0); cell2.SetCellValue("故障時距統計"); sheet.AddMergedRegion(new NPOI.SS.Util.Region(0, 0, 0, 1)); cell2.CellStyle = style3; for (int i = 1; i < ds.Tables[0].Columns.Count; i++) { //IRow row = sheet.CreateRow(0); ICell cell = row.CreateCell(i); cell.SetCellValue(ds.Tables[0].Columns[i].ToString()); cell.CellStyle = style3; } //插入各欄位名稱列 row = sheet.CreateRow(1); for (int i = 0; i < ds.Tables[0].Columns.Count; i++) { //IRow row = sheet.CreateRow(0); ICell cell = row.CreateCell(i); cell.SetCellValue(ds.Tables[0].Columns[i].ToString()); cell.CellStyle = style2; } //插入資料列 for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { row = sheet.CreateRow(i + 2); for (int j = 0; j < ds.Tables[0].Columns.Count; j++) { ICell cell = row.CreateCell(j); cell.SetCellValue(ds.Tables[0].Rows[i][j].ToString()); cell.CellStyle = style2; } } //Workbook workbook = new Workbook(); //Worksheet worksheet = new Worksheet("First Sheet"); //worksheet.Cells[0, 1] = new Cell("VD資料檢核異常報表"); //MemoryStream ms = new MemoryStream(); //ExcelLibrary.DataSetHelper.CreateWorkbook(ms, ds); workbook.Write(ms); Response.AddHeader("Content-Disposition", string.Format("attachment; filename=StatisticsWorkbook.xls")); Response.BinaryWrite(ms.ToArray()); workbook = null; ms.Close(); ms.Dispose(); }
protected DataTable GetData() { SQLDB db = new SQLDB(); StringBuilder query = new StringBuilder(" select top 20 * from "); query.Append(" (SELECT DeviceID,COUNT(*) as counts "); query.Append(" FROM WarrantyNotify "); string conditionName = ddlst_SearchType.SelectedValue; StringBuilder condition = new StringBuilder(); bool isquery = false; if (txt_Query_Reason.Text.Trim().Length > 0) { condition.Append(conditionName + " like '%" + txt_Query_Reason.Text.Trim() + "%' and"); isquery = true; } if (txt_startDateTime.Text.Length > 0) { condition.Append(" NotifyDate >= '" + txt_startDateTime.Text + "' and"); isquery = true; } if (txt_endDateTime.Text.Length > 0) { condition.Append(" NotifyDate <= '" + txt_endDateTime.Text + "' and"); isquery = true; } if (isquery) { query.Append(" WHERE " + condition.ToString().Substring(0, condition.ToString().Length - 3)); } query.Append(" GROUP BY deviceid) w left join View_DeviceConfig d on w.deviceid = d.Device_ID order by counts desc"); DataSet ds = db.SelectQuery(query.ToString()); //Session["DS_MIS"] = ds; if (ds.Tables[0].Rows.Count == 0) { ShowMsg2(UpdatePanel1, "查詢無資料"); } return ds.Tables[0]; }
protected void cmd_ExportExcel_Click(object sender, EventArgs e) { SQLDB _operator = new SQLDB(); StringBuilder select = new StringBuilder(); select.Append(" select DeviceID as 設備編號,Counts as 損壞次數,Device_Kind as 設備種類 ,AreaName as 地區 ,Location as 路段 ,Gis_X as 經度,Gis_Y as 緯度 "); select.Append(" from (SELECT DeviceID,COUNT(*) as counts FROM WarrantyNotify "); //時間查詢條件 if (txt_startDateTime.Text.Length > 0 && txt_endDateTime.Text.Length > 0) { select.Append(" WHERE "); if (txt_startDateTime.Text.Length > 0 && txt_endDateTime.Text.Length > 0) //若有設定起始時間與結束時間 { select.Append("NotifyDate >= '" + txt_startDateTime.Text + "' AND NotifyDate <= '" + txt_endDateTime.Text + "'"); } else if (txt_startDateTime.Text.Length > 0) //若有設定起始時間 { select.Append("NotifyDate >= '" + txt_startDateTime.Text + "'"); } else if (txt_endDateTime.Text.Length > 0) //若有設定結束時間 { select.Append("NotifyDate <= '" + txt_startDateTime.Text + "'"); } } select.Append(" GROUP BY deviceid) w left join View_DeviceConfig d on w.deviceid = d.Device_ID "); string conditionName = ddlst_SearchType.SelectedValue; if (txt_Query_Reason.Text.Trim().Length > 0) //各欄位查詢條件 { select.Append("where " + conditionName + " like '%" + txt_Query_Reason.Text.Trim() + "%' "); } select.Append(" order by counts desc "); DataSet ds = _operator.SelectQuery(select.ToString()); if (ds.Tables[0].Rows.Count > 2000) { ShowMsg2(UpdatePanel1, "查詢筆數過多,請重新調整查詢條件"); return; } HSSFWorkbook workbook = new HSSFWorkbook(); MemoryStream ms = new MemoryStream(); HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("Sheet01"); sheet.SetColumnWidth(4, 48 * 255); //sheet.DisplayGridlines = true; // 設定儲存格樣式-黑框線,跨欄置中 ICellStyle style2 = workbook.CreateCellStyle(); style2.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; style2.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; style2.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; style2.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; style2.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; style2.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; style2.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; style2.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; style2.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; style2.Alignment = HorizontalAlignment.Center; style2.VerticalAlignment = VerticalAlignment.Center; style2.WrapText = true; // 設定儲存格樣式-黑框線,跨欄置中,標題 ICellStyle style3 = workbook.CreateCellStyle(); style3.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; style3.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; style3.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; style3.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; style3.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; style3.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; style3.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; style3.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; style3.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; style3.Alignment = HorizontalAlignment.Center; style3.VerticalAlignment = VerticalAlignment.Center; style3.WrapText = true; NPOI.SS.UserModel.IFont font = workbook.CreateFont(); //以下為設定粗體字,字體大小 font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; font.FontHeightInPoints = 20; style3.SetFont(font); //插入標題列 IRow row = sheet.CreateRow(0); row.Height = 32 * 20; //標題列增高為32 ICell cell2 = row.CreateCell(0); cell2.SetCellValue("維修次數統計"); sheet.AddMergedRegion(new Region(0, 0, 0, 6)); cell2.CellStyle = style3; for (int i = 1; i < ds.Tables[0].Columns.Count; i++) { //IRow row = sheet.CreateRow(0); ICell cell = row.CreateCell(i); cell.SetCellValue(ds.Tables[0].Columns[i].ToString()); cell.CellStyle = style3; } //插入各欄位名稱列 row = sheet.CreateRow(1); for (int i = 0; i < ds.Tables[0].Columns.Count; i++) { //IRow row = sheet.CreateRow(0); ICell cell = row.CreateCell(i); cell.SetCellValue(ds.Tables[0].Columns[i].ToString()); cell.CellStyle = style2; } //插入資料列 for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { row = sheet.CreateRow(i + 2); for (int j = 0; j < ds.Tables[0].Columns.Count; j++) { ICell cell = row.CreateCell(j); cell.SetCellValue(ds.Tables[0].Rows[i][j].ToString()); cell.CellStyle = style2; } } //Workbook workbook = new Workbook(); //Worksheet worksheet = new Worksheet("First Sheet"); //worksheet.Cells[0, 1] = new Cell("VD資料檢核異常報表"); //MemoryStream ms = new MemoryStream(); //ExcelLibrary.DataSetHelper.CreateWorkbook(ms, ds); workbook.Write(ms); Response.AddHeader("Content-Disposition", string.Format("attachment; filename=StatisticsWorkbook.xls")); Response.BinaryWrite(ms.ToArray()); workbook = null; ms.Close(); ms.Dispose(); }
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"); }
protected void cmd_SaveAll_Click(object sender, EventArgs e) { SQLDB _operator = new SQLDB("VD_Info", "KPT"); string query = "select Vdid from VD_STANDARD group by Vdid"; DataSet ds = _operator.SelectQuery(query); for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { DataRow dr_vd = ds.Tables[0].Rows[i]; //SQLDB _operator = new SQLDB("VD_DOORSILL", "KPT"); DataSet DS = _operator.Select("1=0", "", "VD_DOORSILL"); DataRow DR = DS.Tables[0].NewRow(); DR["DeviceID"] = dr_vd["vdid"].ToString(); DR["SpeedTop"] = "255"; DR["SpeedBottom"] = "0"; DR["OccupyTop"] = "100"; DR["OccupyBottom"] = "0"; DR["FlowTop"] = "60"; DR["FlowBottom"] = "0"; DS.Tables[0].Rows.Add(DR); DataSet _changed = DS.GetChanges(); region.Insert(_changed); } ReDirect("新增成功"); //UpdateServerData(); }
//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 void SearchData() { SQLDB db = new SQLDB(); StringBuilder selectquery = new StringBuilder("select * " + " from View_DeviceConfig v left join ( " + " select Device_ID,MAX(ChecKDate) ChecKDate " + " FROM CD_DeviceCheckDate " + " where checkdate < GETDATE() " + " group by Device_ID " + " ) t on v.Device_ID = t.device_ID "); string conditionName = ddlst_SearchType.SelectedValue; StringBuilder condition = new StringBuilder(); //若查詢日期則把模糊查詢like改為= if (conditionName == "ContractStartDate" || conditionName == "ContractEndDate" || conditionName == "CheckDate") { 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 + "'"); } if (txt_EndDate.Text.Length > 0) { condition.Append(conditionName + "< '" + txt_EndDate.Text + "'"); } } } else { condition.Append(txt_Query_Reason.Text.Trim().Length > 0 ? " " + conditionName + " like '%" + txt_Query_Reason.Text.Trim() + "%' " : ""); } if (condition.Length > 0) { selectquery.Append(" WHERE " + condition); } DataSet ds = db.SelectQuery(selectquery.ToString()); Session["DS_MIS"] = ds; gv.DataSource = ds; gv.DataBind(); if (ds.Tables[0].Rows.Count == 0) { ShowMsg2(UpdatePanel1, "查詢無資料"); } }
protected override void InitData() { SQLDB _operator = new SQLDB(); DataSet ds_device = _operator.Select("", "", "Device_Config"); //自動載入通知日期 TextBox_NotifyDate_add.Text = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); //下拉選單,DEVICEID BindDropDownListData(DropDownList_DeviceID, ds_device, "Device_Id", "Device_Id"); //DropDownList_DeviceID.SelectedIndex = 0; //ADD下拉選單,保固廠商 DataSet ds_comapny = _operator.Select("", "", "Company"); BindDropDownListData(DropDownList_WarrantyCompany_add, ds_comapny, "CompanyName", "CompanyID"); //ADD下拉選單,指定合約 string query_contract = " select ('('+ContractNum+')'+ContractName) as ContractCombine,* from Contract "; DataSet ds_contract = _operator.SelectQuery(query_contract); BindDropDownListData(DropDownList_WarrantyContract_add, ds_contract, "ContractCombine", "ContractID"); }