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");
}