//查詢標準型態資料
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");
}