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 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 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 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 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("修改失敗"); } } }
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 btn_saveAll_Click(object sender, EventArgs e) { DataSet ds_StockInItem = (DataSet)Session["StockInItem"]; DataSet ds_StockProject = (DataSet)Session["ICS_Stock_Project"]; #region 驗證是否有輸入資料 if (ds_StockInItem == null || ds_StockProject == null) { ShowMsg2(UpdatePanel1, "請輸入資料"); return; } #endregion #region 驗證物料成本跟計畫分攤成本有沒有相同 int stockcost = 0; int projectcost = 0; //驗證物料成本跟計畫分攤成本有沒有相同 if (ds_StockInItem != null) { for (int i = 0; i < ds_StockInItem.Tables[0].Rows.Count; i++) { stockcost += Convert.ToInt32(ds_StockInItem.Tables[0].Rows[i]["StockIn_Cost"].ToString()); } } if (ds_StockProject!= null) { for (int j = 0; j < ds_StockProject.Tables[0].Rows.Count; j++) { projectcost += Convert.ToInt32(ds_StockProject.Tables[0].Rows[j]["ContractCost"].ToString()); } } if (stockcost != projectcost) { ShowMsg2(UpdatePanel1, "入庫單總成本與計畫總成本不一致,請重新調整"); return; } #endregion #region 新增入庫資料與計畫成本 string userid = (string)Session["UserID"]; string stockinid = ""; SQLDB db = new SQLDB(); DateTime now = DateTime.Now; if (hidden_Action.Value == "add") { //將入庫單時間紀錄起來,之後利用入庫單時間與登入者帳號抓取入庫單編號 string Stockin_date = now.ToString("yyyy/MM/dd HH:mm:ss"); string insertstockrecord = "INSERT INTO ICS_StockIn_Record ([UpdateTime],[UpdateUser]) VALUES ('" + Stockin_date + "','" + userid + "')"; if (db.ExecuteStatement(insertstockrecord)) { DataSet ds = db.Select("UpdateTime ='" + Stockin_date + "'and UpdateUser ='******'", "", "ICS_StockIn_Record"); if (ds.Tables[0].Rows.Count > 0) { //抓到入庫單編號 stockinid = ds.Tables[0].Rows[0]["StockInID"].ToString(); //將入庫單編號塞到StockInItem裡 for (int i = 0; i < ds_StockInItem.Tables[0].Rows.Count; i++) { DataRow dr = ds_StockInItem.Tables[0].Rows[i]; dr["StockInID"] = stockinid; dr["UpdateTime"] = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"); dr["UpdateUser"] = userid; } //將入庫單編號塞到StockProject裡 for (int j = 0; j < ds_StockProject.Tables[0].Rows.Count; j++) { DataRow dr = ds_StockProject.Tables[0].Rows[j]; dr["StockInID"] = stockinid; dr["UpdateTime"] = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"); dr["UpdateUser"] = userid; } //刪除rowindex欄位 ds_StockInItem.Tables[0].Columns.Remove("rowindex"); ds_StockProject.Tables[0].Columns.Remove("rowindex"); DataSet DSChangeItem = ds_StockInItem.GetChanges(); DataSet DSChangeProject = ds_StockProject.GetChanges(); if (db.Insert(DSChangeItem)) ShowMsg2(UpdatePanel1, "入庫單內容新增成功"); else ShowMsg2(UpdatePanel1, "入庫單內容新增失敗"); if (db.Insert(DSChangeProject)) ShowMsg2(UpdatePanel1, "入庫分攤成本新增成功"); else ShowMsg2(UpdatePanel1, "入庫分攤成本新增失敗"); } } } else//入庫單內容修改(目前暫時隱藏因為要考量的東西太多) { DataSet DSChangeItem = ds_StockInItem.GetChanges(DataRowState.Deleted); DataSet DSChangeProject = ds_StockProject.GetChanges(DataRowState.Deleted); if (DSChangeItem != null) { if (db.Update(DSChangeItem)) ShowMsg2(UpdatePanel1, "入庫單內容修改成功"); else ShowMsg2(UpdatePanel1, "入庫單內容修改失敗"); } if (DSChangeProject != null) { if (db.Update(DSChangeProject)) ShowMsg2(UpdatePanel1, "入庫分攤成本修改成功"); else ShowMsg2(UpdatePanel1, "入庫分攤成本修改失敗"); } } #endregion #region 更新物料庫存量與料價 #region 舊寫法用庫存資料即時算出物料庫存量與料價再存到ICS_Inventory /* //更新庫存量 //庫存數量 = 目前庫存數量+(入庫數 X 轉換係數) //查詢庫存資料 DataSet ds_inventory = db.Select("", "", "ICS_Inventory"); //查詢物料轉換係數 DataSet ds_material = db.Select("", "", "ICS_Material"); for (int i = 0; i < ds_StockInItem.Tables[0].Rows.Count; i++) { string materialid = ds_StockInItem.Tables[0].Rows[i]["MaterialID"].ToString(); string materialName = ds_StockInItem.Tables[0].Rows[i]["MaterialName"].ToString(); int stockInQuantity = Convert.ToInt32(ds_StockInItem.Tables[0].Rows[i]["StockIn_Quantity"].ToString()); int materialValue = Convert.ToInt32(ds_StockInItem.Tables[0].Rows[i]["Material_Single_Value"].ToString()); int stockinCost = Convert.ToInt32(ds_StockInItem.Tables[0].Rows[i]["StockIn_Cost"].ToString()); DataRow[] dr_invertory = ds_inventory.Tables[0].Select("MaterialID = '" + materialid + "'"); DataRow[] dr_material = ds_material.Tables[0].Select("NO = '" + materialid + "'"); double ConversionFactor = Convert.ToDouble(dr_material[0]["ConversionFactor"].ToString());//轉換係數 //若庫存有資料 if (dr_invertory.Length > 0) { double invetory_stockinquantity = Convert.ToDouble(dr_invertory[0]["StockInQuantity"].ToString());//庫存購入數量 double invetory_q = Convert.ToDouble(dr_invertory[0]["Quantity"].ToString());//庫存量 double inventoryCost = Convert.ToDouble(dr_invertory[0]["InventoryCost"].ToString());//庫存成本 //更新庫存購入數量:庫存購入數量+入庫量 dr_invertory[0]["StockInQuantity"] = invetory_stockinquantity + stockInQuantity; //更新庫存數量:庫存數量 = 目前庫存數量+(入庫數 X 轉換係數) dr_invertory[0]["Quantity"] = invetory_q + (stockInQuantity * ConversionFactor); //更新料價: (庫存成本+入庫成本)/(庫存數量+入庫數量) dr_invertory[0]["MaterialCost"] = (inventoryCost + stockinCost) / (stockInQuantity + invetory_stockinquantity); //更新庫存成本:料價 X 庫存購入數量 dr_invertory[0]["InventoryCost"] = (inventoryCost + stockinCost) / (stockInQuantity + invetory_stockinquantity) * (invetory_stockinquantity + stockInQuantity); dr_invertory[0]["UpdateTime"] = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"); } else//若庫存無此物料資料則新增 { DataRow dr = ds_inventory.Tables[0].NewRow(); dr["MaterialID"] = materialid; dr["MaterialName"] = materialName; dr["StockInQuantity"] = stockInQuantity; dr["Quantity"] = stockInQuantity * ConversionFactor; dr["MaterialCost"] = materialValue; dr["InventoryCost"] = stockinCost; dr["UpdateTime"] = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"); dr["UpdateUser"] = userid; ds_inventory.Tables[0].Rows.Add(dr); } } DataSet ds_invetoryEdit = ds_inventory.GetChanges(); if (db.Update(ds_invetoryEdit)) ShowMsg2(UpdatePanel1, "庫存量更新成功、料價更新成功"); else ShowMsg2(UpdatePanel1, "庫存量更新成功、料價更新失敗"); */ #endregion #region 新寫法:從View_ICS_Inventory查出物料庫存量與料價再存到ICS_Inventory_History //更新庫存量 //庫存數量 = 目前庫存數量+(入庫數 X 轉換係數) //組出要查詢的入庫物料 StringBuilder stockinItem = new StringBuilder("MaterialID in ("); for (int i = 0; i < ds_StockInItem.Tables[0].Rows.Count; i++) { string materialid = ds_StockInItem.Tables[0].Rows[i]["MaterialID"].ToString(); stockinItem.Append(" ' "); stockinItem.Append(materialid); stockinItem.Append(" ',"); } stockinItem.Remove(stockinItem.Length - 1, 1); //將最後的,號拿掉 stockinItem.Append(")"); //查詢View_ICS_Inventory即時物料庫存量與料價 DataSet ds_viewInventory = db.Select(stockinItem.ToString(), "", "View_ICS_Inventory"); //查詢庫存歷史資料 DataSet ds_inventoryHis = db.Select(" 1= 0", "", "ICS_Inventory_History"); for (int i = 0; i < ds_StockInItem.Tables[0].Rows.Count; i++) { string materialid = ds_StockInItem.Tables[0].Rows[i]["MaterialID"].ToString(); string materialName = ds_StockInItem.Tables[0].Rows[i]["MaterialName"].ToString(); string stockLocation = ds_StockInItem.Tables[0].Rows[i]["Stock_Location"].ToString(); DataRow[] dr_viewinventory = ds_viewInventory.Tables[0].Select("MaterialID = '" + materialid + "'"); DataRow dr = ds_inventoryHis.Tables[0].NewRow(); dr["StockInid"] = stockinid; dr["MaterialID"] = materialid; dr["MaterialName"] = materialName; dr["StockInQuantity"] = Convert.ToDouble(ds_StockInItem.Tables[0].Rows[i]["StockIn_Quantity"].ToString()); dr["Quantity"] = dr_viewinventory[0]["Quantity"]; dr["MaterialCost"] = dr_viewinventory[0]["MaterialCost"]; dr["InventoryCost"] = dr_viewinventory[0]["InventoryCost"]; dr["StockLocation"] = stockLocation; dr["UpdateTime"] = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"); dr["UpdateUser"] = userid; ds_inventoryHis.Tables[0].Rows.Add(dr); } DataSet ds_invetoryEdit = ds_inventoryHis.GetChanges(); if (db.Update(ds_invetoryEdit)) ShowMsg2(UpdatePanel1, "庫存量更新成功、料價更新成功"); else ShowMsg2(UpdatePanel1, "庫存量更新成功、料價更新失敗"); #endregion #endregion }
protected void Page_Load(object sender, EventArgs e) { SQLDB _operator = new SQLDB("", "KPT"); //定義連續錯誤超過幾筆的次數 string errorcount = System.Configuration.ConfigurationManager.AppSettings["ErrorCount"]; //幾分鐘檢核一次VD int VDCheckMinute = Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["VDCheckMinute"]); DateTime end = DateTime.Now; DateTime start = DateTime.Now.AddMinutes(VDCheckMinute); DataSet ds_ckeckvd = _operator.Select("Counts > '" + errorcount + "' and 時間 between '" + start.ToString("yyyy/MM/dd HH:mm:ss") + "' and '" + end.ToString("yyyy/MM/dd HH:mm:ss") + "' ", "設備編號,標準型態名稱,方向,星期,時段", "View_CheckVDFilter"); if (ds_ckeckvd.Tables[0].Rows.Count > 0) { DataSet ds_error = _operator.Select("1=0", "", "VD_ERRORRECORD"); DateTime time = DateTime.Now; foreach (DataRow dr in ds_ckeckvd.Tables[0].Rows) { DataRow dr_add = ds_error.Tables[0].NewRow(); dr_add["Vdid"] = dr["設備編號"]; dr_add["Vsrdir"] = dr["方向"]; dr_add["Week"] = dr["星期"]; dr_add["Hours"] = dr["時段"]; dr_add["TypeName"] = dr["標準型態名稱"]; dr_add["SpeedRange"] = dr["速度範圍"]; dr_add["LaneoccupyRange"] = dr["佔有率範圍"]; dr_add["FlowRange"] = dr["流量範圍"]; dr_add["RecordTime"] = dr["時間"]; dr_add["Counts"] = dr["Counts"]; dr_add["UpdateTime"] = time; ds_error.Tables[0].Rows.Add(dr_add); } DataSet DSChange = ds_error.GetChanges(); if (!_operator.Insert(DSChange)) { Response.Write("資料塞入錯誤"); } } else { DataSet ds_error = _operator.Select("1=0", "", "VD_ERRORRECORD_log"); DateTime time = DateTime.Now; DataRow dr_add = ds_error.Tables[0].NewRow(); dr_add["Vdid"] = "log"; dr_add["Vsrdir"] = "0"; dr_add["Week"] = "0"; dr_add["Hours"] = "0"; dr_add["TypeName"] = "0"; dr_add["SpeedRange"] = "0"; dr_add["LaneoccupyRange"] = "0"; dr_add["FlowRange"] = "0"; dr_add["RecordTime"] = DateTime.Now; dr_add["Counts"] = "0"; dr_add["UpdateTime"] = DateTime.Now; ds_error.Tables[0].Rows.Add(dr_add); DataSet DSChange = ds_error.GetChanges(); if (!_operator.Insert(DSChange)) { Response.Write("紀錄log塞入錯誤"); } } }