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塞入錯誤");
}
}
}
