using System;
using System.Data;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Resources;
using System.Reflection;
using System.DirectoryServices;
using System.Web.Security;
using System.Data.SqlClient;
using System.Threading;
using System.Security.Principal;
using System.Configuration;
using Microsoft.Web.UI.WebControls;
using Foxconn.DAL;
using System.Web.SessionState;
using System.Web.UI.HtmlControls;
using BLL;
using System.IO;
namespace CQS
{
/// <summary>
/// CommanPage 的摘要描述。
/// </summary>
public class PageCommon : System.Web.UI.Page
{
/// <summary>
/// 用戶資料對象
/// </summary>
protected UserInfo userInfo = null;
/// <summary>
/// 資源管理器
/// </summary>
// ResourceManager Loc;
string _pagename,_pagepath;
//BOM 展開時用來臨時存儲所有要展開的料號,用來判斷自身料號調用自身時的遞增歸的中止
string bomstr;
//BOM 展開時,初始料號的記錄
string MainMaterialNumber,mainvaliddate,QRNo,BomLocation,AssignDate,AlternativeBom,IntraTransfer,Multibom;
string str_MaterialTypePN,str_ProcurementTypePN,str_QuotationRoutingPN,MainPlant;
int QRItemNo;
CommClass.PubFunction func= new CommClass.PubFunction();
public PageCommon()
{
}
override protected void OnInit(EventArgs e){
this.Error += new System.EventHandler(this.PageCommon_Error);
base.OnInit(e);
}
private void PageCommon_Error(object serder,System.EventArgs e){
System.Exception ex =Server.GetLastError();
string sErrMsg = "<div style='font-bold:true'>"+ ex.Message+"</div><br>"+ ex.StackTrace ;
Session["sErrMsg"] = sErrMsg.Replace("\n","<br>");
Server.ClearError();
string AbsUri =Request.Url.AbsoluteUri;
string RawUrl = Request.RawUrl;
string RootUrl="";
RootUrl = AbsUri.Substring(0, AbsUri.Length - RawUrl.Length)+Request.ApplicationPath;
//
// string UrlAuthority;
// UrlAuthority = HttpContext.Current.Request.Url.GetLeftPart(UriPartial.Authority);
// UrlAuthority += HttpContext.Current.Request.ApplicationPath;
//
// 防止頁面不刷新,故加入時間參數,讓 IE 認為是不同網頁,從而實現頁面刷新的功能
Response.Redirect(RootUrl +"/ErrorMsg.aspx?Now="+ DateTime.Now.ToString() +"&MsgContent=");
}
#region 權限函數
//登陸頁面時進行身份驗證和權限設定
public void Page_login(string road)
{
if (Session["uID"]==null&Request["F_SysCode"]==null)
{
Response.Redirect(road+"ErrorMsg.aspx?MsgID=M0003");
}
if(Session["uID"]==null)
{
func.Login_Again();//Session 丟失後重新Login
}
else
{
if(Session["uID"].ToString()=="")
{
Response.Write("<script language=javascript>window.top.location='Default.aspx';</script>");
}
}
//判斷是否具有該頁面的權限
if (Request["F_SysCode"] == null)
{
Response.Redirect(road+"ErrorMsg.aspx?MsgID=M0001");
return;
}
if (func.haveMenu(Session["uID"].ToString(),Request["F_SysCode"].ToString()) == false)
{
Response.Redirect(road+"ErrorMsg.aspx?MsgID=M0001");
return;
}
}
//編輯/刪除權限判斷
//沒有編輯/刪除權限,編輯/刪除按鈕隱藏,如果編輯/刪除按鈕都隱藏且所在方格沒有其它物件則隱藏
public void dg_Permit(string uID,string F_SysCode,string Edit_ID,string Del_ID,string Show_ID,System.Web.UI.WebControls.DataGridItemEventArgs e,System.Web.UI.WebControls.DataGrid e1)
{
if((Edit_ID!=null)&(Del_ID!=null))
{
HtmlAnchor EditBtn=new HtmlAnchor();
EditBtn=(HtmlAnchor)(e.Item.FindControl(Edit_ID));
EditBtn.Visible=func.GetMenuPermit(uID,F_SysCode,"Edit");
LinkButton DelBtn=new LinkButton();
DelBtn=(LinkButton)(e.Item.FindControl(Del_ID));
DelBtn.Visible=func.GetMenuPermit(uID,F_SysCode,"Delete");
if(Show_ID==null)
{
if(EditBtn.Visible==false&DelBtn.Visible==false)
{
e.Item.Cells[e.Item.Cells.Count-1].Visible=false;
// e1.Columns[e.Item.Cells.Count-1].Visible=false;
}
}
}
else
{
if((Edit_ID==null)&(Del_ID!=null))
{
LinkButton DelBtn=new LinkButton();
DelBtn=(LinkButton)(e.Item.FindControl(Del_ID));
DelBtn.Visible=func.GetMenuPermit(uID,F_SysCode,"Delete");
if(Show_ID==null)
{
if(DelBtn.Visible==false)
{
e.Item.Cells[e.Item.Cells.Count-1].Visible=false;
// e1.Columns[e.Item.Cells.Count-1].Visible=false;
}
}
}
else
{
if((Edit_ID!=null)&(Del_ID==null))
{
HtmlAnchor EditBtn=new HtmlAnchor();
EditBtn=(HtmlAnchor)(e.Item.FindControl(Edit_ID));
EditBtn.Visible=func.GetMenuPermit(uID,F_SysCode,"Edit");
if(Show_ID==null)
{
if(EditBtn.Visible==false)
{
e.Item.Cells[e.Item.Cells.Count-1].Visible=false;
// e1.Columns[e.Item.Cells.Count-1].Visible=false;
}
}
}
else
{
e.Item.Cells[e.Item.Cells.Count-1].Visible=false;
// e1.Columns[e.Item.Cells.Count-1].Visible=false;
}
}
}
}
public bool dgedit_Permit(string uID,string F_SysCode,string Edit_ID,string Show_ID,System.Web.UI.WebControls.DataGridItemEventArgs e,System.Web.UI.WebControls.DataGrid e1)
{
//LinkButton DelBtn=new LinkButton();
//DelBtn=(LinkButton)(e.Item.FindControl(Del_ID));
HtmlAnchor EditBtn=new HtmlAnchor();
EditBtn=(HtmlAnchor)(e.Item.FindControl(Edit_ID));
if (func.GetMenuPermit(uID,F_SysCode,"Edit") ==false)
EditBtn.Visible=false;
// DelBtn.Visible=func.GetMenuPermit(uID,F_SysCode,"Delete");
// // if(e.Item.Cells[e.Item.Cells.Count-1].Controls.Count<3)
// // {
// // if(EditBtn.Visible==false&DelBtn.Visible==false)
// // {
// // e.Item.Cells[e.Item.Cells.Count-1].Visible=false;
// // }
// // }
// if(Show_ID==null)
// {
// if(EditBtn.Visible==false&DelBtn.Visible==false)
// {
// e.Item.Cells[e.Item.Cells.Count-1].Visible=false;
// e1.Columns[e.Item.Cells.Count-1].Visible=false;
// }
// }
return func.GetMenuPermit(uID,F_SysCode,"Edit");
}
public bool dgdel_Permit(string uID,string F_SysCode,string Edit_ID,string Del_ID,string Show_ID,System.Web.UI.WebControls.DataGridItemEventArgs e,System.Web.UI.WebControls.DataGrid e1)
{
LinkButton DelBtn=new LinkButton();
DelBtn=(LinkButton)(e.Item.FindControl(Del_ID));
HtmlAnchor EditBtn=new HtmlAnchor();
EditBtn=(HtmlAnchor)(e.Item.FindControl(Edit_ID));
if (func.GetMenuPermit(uID,F_SysCode,"Edit") ==false)
EditBtn.Visible=false;
DelBtn.Visible=func.GetMenuPermit(uID,F_SysCode,"Delete");
// if(e.Item.Cells[e.Item.Cells.Count-1].Controls.Count<3)
// {
// if(EditBtn.Visible==false&DelBtn.Visible==false)
// {
// e.Item.Cells[e.Item.Cells.Count-1].Visible=false;
// }
// }
if(Show_ID==null)
{
if(EditBtn.Visible==false&DelBtn.Visible==false)
{
e.Item.Cells[e.Item.Cells.Count-1].Visible=false;
// e1.Columns[e.Item.Cells.Count-1].Visible=false;
}
}
return func.GetMenuPermit(uID,F_SysCode,"Delete");
}
public void Plant_Permit(string uID,System.Web.UI.WebControls.DataGridItemEventArgs e,int i,string Edit_ID,string Del_ID)
{
LinkButton DelBtn=new LinkButton();
DelBtn=(LinkButton)(e.Item.FindControl(Del_ID));
HtmlAnchor EditBtn=new HtmlAnchor();
EditBtn=(HtmlAnchor)(e.Item.FindControl(Edit_ID));
EditBtn.Visible=func.GetPlantPermit(uID,e.Item.Cells[i].Text.ToString(),"Edit");
DelBtn.Visible=func.GetPlantPermit(uID,e.Item.Cells[i].Text.ToString(),"Delete");
}
public void Plantedit_Permit(string uID,System.Web.UI.WebControls.DataGridItemEventArgs e,int i,string Edit_ID)
{
//LinkButton DelBtn=new LinkButton();
//DelBtn=(LinkButton)(e.Item.FindControl(Del_ID));
HtmlAnchor EditBtn=new HtmlAnchor();
EditBtn=(HtmlAnchor)(e.Item.FindControl(Edit_ID));
EditBtn.Visible=func.GetPlantPermit(uID,e.Item.Cells[i].Text.ToString(),"Edit");
//DelBtn.Visible=func.GetPlantPermit(uID,e.Item.Cells[i].Text.ToString(),"Delete");
}
public void Plantdel_Permit(string uID,System.Web.UI.WebControls.DataGridItemEventArgs e,int i,string Del_ID)
{
LinkButton DelBtn=new LinkButton();
DelBtn=(LinkButton)(e.Item.FindControl(Del_ID));
//HtmlAnchor EditBtn=new HtmlAnchor();
//EditBtn=(HtmlAnchor)(e.Item.FindControl(Edit_ID));
//EditBtn.Visible=func.GetPlantPermit(uID,e.Item.Cells[i].Text.ToString(),"Edit");
DelBtn.Visible=func.GetPlantPermit(uID,e.Item.Cells[i].Text.ToString(),"Delete");
}
//判斷用戶對該頁面的保存權限
public void SavePermit(System.Web.UI.WebControls.Button SaveContinueBtn,System.Web.UI.WebControls.Button SaveCloseBtn,string uID,string F_SysCode,string Action)
{
SaveContinueBtn.Enabled=func.GetMenuPermit(uID,F_SysCode,Action);
SaveCloseBtn.Enabled=func.GetMenuPermit(uID,F_SysCode,Action);
}
#endregion
#region Open新窗口事件初始化
public void RegisterFunctionView()
{
string script;
script="var bgColorNotSelect = '#ffffff';"+" "+
"var bgColorSelected = '#fceade';"+" "+
"var selectedLine = null;"+" "+
"function SelectLine(trID){"+" "+
"if (document.getElementById(trID) !=null){"+" "+
" if (selectedLine==trID){"+" "+
" document.getElementById(trID).style.backgroundColor = bgColorNotSelect;"+" "+
"} else {"+" "+
" if (selectedLine !=null){"+" "+
" document.getElementById(selectedLine).style.background = bgColorNotSelect;"+" "+
" }"+" "+
" document.getElementById(trID).style.backgroundColor = bgColorSelected;"+" "+
"}"+" "+
"}"+" "+
"selectedLine=trID;"+" "+
"}"+" "+
"function OpenEditWindow(ID){"+" "+
"var openWnd; "+" "+
"openWnd=window.open('"+ PagePath +"'+ID,'"+ PageName +"','height=500,width=700,status=yes,toolbar=no,menubar=no,location=no,scrollbars=yes,resizable=yes',null);"+" "+
"openWnd.moveTo((parseInt(screen.width)-700)/2,(parseInt(screen.height)-500)/2);"+" "+
"}";
RegisterClientScriptBlock2("selectandedit",script);
}
//Move事件初始化
public void RegisterFunctionEdit()
{
string script2;
script2="if (typeof fcolor == 'undefined') { var fcolor = '#ffffff';}"+" "+
"if (typeof backcolor == 'undefined') { var backcolor = '#000000';}"+" "+
"if (typeof textcolor == 'undefined') { var textcolor = '#001A8C';}"+" "+
"if (typeof capcolor == 'undefined') { var capcolor = '#FFFFFF';}"+" "+
"if (typeof closecolor == 'undefined') { var closecolor = '#9999FF';}"+" "+
"if (typeof width == 'undefined') { var width = '400';}"+" "+
"if (typeof border == 'undefined') { var border = '1';}"+" "+
"var xPlus = 0;"+" "+
"var yPlus = 0;"+" "+
"ns4 = (document.layers)? true:false;"+" "+
"ie4 = (document.all)? true:false;"+" "+
"ie5 = (document.getElementById)? true:false;"+" "+
"if (ns4) over = document.overDiv;"+" "+
"if (ie4) over = overDiv.style;"+" "+
"document.onmousemove = mouseMove;"+" "+
"if (ns4) document.captureEvents(Event.MOUSEMOVE);"+" "+
"function drawMessage(text) {"+" "+
" dts(text);"+" "+
"}"+" "+
"function mout() {"+" "+
" hideObject(over);"+" "+
"}"+" "+
"function dts(text) {"+" "+
" txt = '<TABLE WIDTH='+width+' BORDER=0 CELLPADDING='+border+' CELLSPACING=0 BGCOLOR=LightGray><TR><TD><font face=Arial size=8pt><TABLE WIDTH=100% BORDER=0 CELLPADDING=2 CELLSPACING=0 BGCOLOR='+fcolor+'><TR><TD CLASS=P1><FONT FACE=Arial COLOR='+textcolor+'>'+text+'</FONT></TD></TR></TABLE></font></TD></TR></TABLE>';"+" "+
" layerWrite(txt);"+" "+
" disp();"+" "+
"}"+" "+
"function disp() {"+" "+
" xPlus = (x<400)? 1:-405;"+" "+
" yPlus = (y<150)? 1:150;"+" "+
" "+" "+
" moveTo(over, document.body.scrollLeft + x + xPlus, document.body.scrollTop + y);"+" "+
" showObject(over);"+" "+
"}"+" "+
"function mouseMove(e) {"+" "+
" if (ns4) {x=e.pageX; y=e.pageY;}"+" "+
" if (ie4) {x=event.x; y=event.y;}"+" "+
" if (ie5) {x=event.x; y=event.y;}"+" "+
" xPlus = (x<400)? 1:-405;"+" "+
" yPlus = (y<150)? 1:150;"+" "+
" moveTo(over, document.body.scrollLeft + x + xPlus, document.body.scrollTop + y);"+" "+
"}"+" "+
"function layerWrite(txt) {"+" "+
" if (ns4) {"+" "+
" var lyr = document.overDiv.document;"+" "+
" lyr.write(txt);"+" "+
" lyr.close();"+" "+
" }"+" "+
" else if (ie4){"+" "+
" document.all['overDiv'].innerHTML = txt;"+" "+
" }"+" "+
" else {"+" "+
" document.getElementById('overDiv').innerHTML = txt;"+" "+
" }"+" "+
"}"+" "+
"function showObject(obj) {"+" "+
" if (ns4) obj.visibility = 'show';"+" "+
" else if (ie4) obj.visibility = 'visible';"+" "+
" else obj.visibility = 'visible';"+" "+
"}"+" "+
"function hideObject(obj) {"+" "+
" if (ns4) obj.visibility = 'hide';"+" "+
" else if (ie4) obj.visibility = 'hidden';"+" "+
" else obj.visibility = 'hidden';"+" "+
"}"+" "+
""+" "+
"function moveTo(obj,xL,yL) {"+" "+
" obj.left = xL;"+" "+
" obj.top = yL;"+" "+
"}";
RegisterStartupScript2("drawmessage",script2);
}
public void RegisterClientScriptBlock2(string key,string script)
{
string sScript="<script language=JavaScript>" + script + "</script>";
base.RegisterClientScriptBlock(key,sScript);
}
public void RegisterStartupScript2(string key,string script)
{
string sScript="<script language=JavaScript>" + script + "</script>";
base.RegisterStartupScript(key,sScript);
}
protected void CloseWindow()
{
RegisterClientScriptBlock2("SubmitCloseWindow","window.opener=null;self.close();");
}
protected void ShowErrMsg(string psMsg)
{
psMsg = psMsg.Replace("\r\n", "--"); //Right
psMsg = psMsg.Replace("\n\r", "--"); //Right
this.Response.Redirect("ErrMsg.aspx?errmsg=" + psMsg);
}
#endregion
/// <summary>
/// 此方法只用於函數的最後一個語句才有效果，或者後面只有return語句。
/// 是RegisterClientScriptBlock的機制問題,因為不能在服務器端代碼未執行完的時候彈出客戶端窗口。
/// </summary>
protected void AlertMsg(string psMsg)
{
string sMsg = psMsg.Replace("\r\n", "--"); //Right
sMsg = psMsg.Replace("\n\r", "--"); //Right
sMsg=sMsg.Replace("'","\'");
this.RegisterClientScriptBlock("AlertMsg","<script language='javascript'>alert('" + sMsg + "');</script>");
}
public static void AlertMsg(System.Web.UI.Page pPage,string psMsg)
{
string sMsg = psMsg.Replace("\r\n", "--"); //Right
sMsg = psMsg.Replace("\n\r", "--"); //Right
sMsg=sMsg.Replace("'","\'");
pPage.RegisterClientScriptBlock("AlertMsg","<script language='javascript'>alert('" + sMsg + "');</script>");
}
public string GetString(string strNo)
{
//多語言支持
ResourceManager rm=new ResourceManager("CQS.SystemMsg",Assembly.GetExecutingAssembly());
return rm.GetString(strNo);
}
public string GetSqlconnString()
{
return Symmetric.Decrypt(System.Configuration.ConfigurationSettings.AppSettings["SqlConn"],"aaaabbbb");
}
#region 用戶資料的UserInfo
/// <summary>
/// 取得用戶資料，並保存到UseInfo類。
/// </summary>
/// <returns>用戶資料對象</returns>
protected UserInfo getUserInfo()
{
if(Session["uID"]==null)
{
// 沒有登陸，或登陸超時，或客戶機不是通過域登陸
if(!this.User.Identity.IsAuthenticated)
{
// RegisterClientScriptBlock("error","<script language=javascript>parent.document.location.href='login.aspx';</script>");
// if(Request.ApplicationPath + "/Default.aspx" != Request.CurrentExecutionFilePath && Request.ApplicationPath != Request.CurrentExecutionFilePath)
// {
// Response.Redirect(Request.ApplicationPath + "/Default.aspx");
// }
}
else
{
string[] arrUserName = this.User.Identity.Name.Trim().Split('\\');
string userID = arrUserName.GetValue(0).ToString();
if(arrUserName.Length>1)
{
userID = arrUserName.GetValue(1).ToString();
}
else
{
userID = arrUserName.GetValue(0).ToString();
}
if(IsValidateUser(userID))
{
userInfo = new UserInfo(userID);
Session["uID"] = userID;
// Session["UserInfo"] = userInfo;
}
else
{
Server.Transfer("Error.aspx?MsgID=I002");
}
}
}
else
{
string userID = Session["uID"].ToString();
userInfo = new UserInfo(userID);
// userInfo = (UserInfo)Session["UserInfo"];
}
// string language = "en-us";
// try
// {
// HttpCookie cookie = Request.Cookies["Language"];
// if(cookie!=null)
// {
// FormsAuthenticationTicket authTicket = null;
//
// authTicket = FormsAuthentication.Decrypt(cookie.Value);
//
// if(authTicket != null)
// {
// language = authTicket.Name.Trim();
// }
// }
// }
// catch
// {
// }
// Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo(language);
// Thread.CurrentThread.CurrentUICulture = new System.Globalization.CultureInfo(language);
// Loc = new ResourceManager("AIT.Location",Assembly.GetExecutingAssembly());
return userInfo;
}
/// <summary>
/// 域用戶驗證
/// </summary>
/// <param name="domain">域名</param>
/// <param name="userName">域用戶名稱</param>
/// <param name="password">域密碼</param>
/// <returns>true:通過驗證 false:未通過驗證</returns>
public bool IsAuthenticated(string domain, string userName, string password)
{
bool flag = false;
string domainAnduserName = "";
if(domain!="")
{
domainAnduserName = domain + @"\" + userName;
}
else
{
domainAnduserName = userName;
}
return flag;
}
/// <summary>
/// 驗證當前用戶是否是合法用戶，即是否存在本系統的用戶表
/// </summary>
/// <param name="userID">用戶ID</param>
/// <returns>true:合法; false:不合法</returns>
public bool IsValidateUser(string userID)
{
bool isValidate = false;
try
{
// isValidate = CPubEmployee.Exists(userID);
}
catch
{
}
return isValidate;
}
/// <summary>
/// 驗證用戶是否是本系統的合法用戶
/// </summary>
/// <param name="userID">用戶ID</param>
/// <param name="password">密碼</param>
/// <returns>true:合法 false:不合法</returns>
public bool IsUserCorrect(string userID, string password)
{
// 函數返回值
bool isCorrect= false;
try
{
// if(CPubEmployee.Exists(userID))
// {
// CPubEmployee employee = new CPubEmployee(userID);
// if(employee.Password == FormsAuthentication.HashPasswordForStoringInConfigFile(password.Trim(), "md5"))
// {
// isCorrect = true;
// }
// }
}
catch
{
}
return isCorrect;
}
public string PagePath
{
get
{
return this._pagepath;
}
set
{
this._pagepath=value;
}
}
public string PageName
{
get
{
return this._pagename;
}
set
{
this._pagename=value;
}
}
#endregion
#region 肖富劍,在新的展BOM機制下的展BOM程式,2006-08-24,2006-10-21修改版
/// <summary>
///
/// </summary>
/// <param name="DataType"></param>
/// <param name="validdate">指定展BOM的時間,SetBom時為Top料號的ValidDate</param>
/// <param name="MainPN">Top料號</param>
/// <param name="MainPlant">Top料號所在的工廠</param>
/// <param name="AlternativeBomstr">默認為1</param>
/// <param name="AssignDatestr">指定展BOM的時間,為系統當前時間或者用戶手動指定</param>
/// <param name="Location">SetBom或者是Expolding</param>
/// <param name="Multibom">Y表示展多階,N表示只展Top下的一階</param>
/// <param name="Procurement">ProcurementType,default為Y</param>
/// <param name="IntraTransfer">內交工廠,Default為Y</param>
/// <param name="Qty">指定待展開bom物料的數量,Default為1</param>
/// <param name="QRNostr">QRNo,如果在SetBom的地方時,傳空值</param>
/// <param name="QRItemNostr">QRItemNo,SetBom時為0</param>
public string SaveExplodBom_old(string DataType,string validdate, string MainPN,string MainPlantName,string AlternativeBomstr,string AssignDatestr,string Location,string Multibomstr,string Procurement,string IntraTransferstr,double Qty,string QRNostr,int QRItemNostr)
{
if (DataType == "") DataType="C";
CommClass.PubFunction func= new CommClass.PubFunction();
bomstr =MainPN;
MainMaterialNumber=MainPN;
MainPlant =MainPlantName;
mainvaliddate = validdate;
QRNo=QRNostr;
QRItemNo =QRItemNostr;
BomLocation =Location;
Multibom=Multibomstr;
AssignDate =AssignDatestr;
AlternativeBom =AlternativeBomstr;
IntraTransfer=IntraTransferstr;
if (MainMaterialNumber =="")
{
Response.Write("<script language=javascript>alert('MaterialNumber is null!');</script>");
return str_MaterialTypePN+"_"+str_ProcurementTypePN+"_"+str_QuotationRoutingPN;
}
if (MainPlant =="")
{
Response.Write("<script language=javascript>alert(' Plant is null!');</script>");
return str_MaterialTypePN+"_"+str_ProcurementTypePN+"_"+str_QuotationRoutingPN;
}
string bomID="0";
// if(QRNo !="")
// {//刪除QR下的BOM
// if (func.ExecuteSQL("delete from setBomStructure where isnull(QRNo,'') ='"+QRNo+"' and QRitemNo ='"+QRItemNo+"'") ==false)
// {
// Response.Write("<script language=javascript>alert('Delete the old BOM of QRNo ="+QRNo+" and QRItemNo= "+QRItemNo+" error');</script>");
// }
// }
if (Location=="SetBom")
{
//刪除當前天以前的資料
if (func.ExecuteSQL("delete from setBomStructure where isnull(QRNo,'') ='"+QRNo+"' and MainMaterialNumber ='"+MainMaterialNumber+"' and Plant='" + MainPlant +"' "+
"and DataType='"+ DataType+"' and MainValidDate ='"+mainvaliddate+"' and AlternativeBom ='"+AlternativeBom+"' "+ //and AssignDate <='"+AssignDate+"'
"and BomLocation ='"+Location+"' ") ==false)
{
Response.Write("<script language=javascript>alert('Delete the old BOM of MainMaterialNumber :"+MainMaterialNumber+" Failed!');</script>");
return str_MaterialTypePN+"_"+str_ProcurementTypePN+"_"+str_QuotationRoutingPN;
}
}
else //Approve的地方,刪除指定時間之前的所有資料
{
if (UpdateOrDeletePN(MainMaterialNumber,MainPlant) ==false)
{
// if (func.ExecuteSQL("delete from setBomStructure where isnull(QRNo,'') ='"+QRNo+"' and QRitemNo ='"+QRItemNo+"' "+ //and AssignDate <>'"+AssignDate+"'
// " and MainMaterialNumber ='"+MainMaterialNumber+"' and Plant='" + MainPlant +"' "+
// "and DataType='"+ DataType+"' and MainValidDate ='"+mainvaliddate+"' and AlternativeBom ='"+AlternativeBom+"' "+
// "and BomLocation ='"+Location+"' ") ==false)
if (func.ExecuteSQL("delete from setBomStructure where isnull(QRNo,'') ='"+QRNo+"' and QRitemNo ='"+QRItemNo+"' "+ //and AssignDate <>'"+AssignDate+"'
"and BomLocation ='"+Location+"' ") ==false)
{
Response.Write("<script language=javascript>alert('Delete the old BOM of MainMaterialNumber :"+MainMaterialNumber+" before "+AssignDate+" error');</script>");
return str_MaterialTypePN+"_"+str_ProcurementTypePN+"_"+str_QuotationRoutingPN;
}
}
if (func.GetDataTable("select * from setbomstructure where isnull(QRNo,'') ='"+QRNo+"' and QRitemNo ='"+QRItemNo+"' and MainMaterialNumber ='"+MainMaterialNumber+"' and Plant='" + MainPlant +"' "+
"and DataType='"+ DataType+"' and MainValidDate ='"+mainvaliddate+"' and AlternativeBom ='"+AlternativeBom+"' "+
"and BomLocation ='"+Location+"' and AssignDate ='"+AssignDate+"' ").Tables[0].Rows.Count >0)
{ //刪除指定時間以前的ＢＯＭ後退出
return str_MaterialTypePN+"_"+str_ProcurementTypePN+"_"+str_QuotationRoutingPN;
}
}
//至bom header檔中查找資料
DataSet ds_BomH = func.GetDataTable("select * from setbomheader where materialnumber='"+ MainMaterialNumber +"' and plant='"+ MainPlant +"' "+
"and ValidDate='"+ mainvaliddate +"' and datatype='"+ DataType +"' and AlternativeBom ='"+AlternativeBom+"' and Active ='A' ");
//得到料號在當前工廠中的 ProcurementType ,看是否是內交料
string Curr_ProcurementType =func.GetDataOne("select ProcurementType from pubMaterialParameter where MaterialNumber ='"+MainMaterialNumber+"' and Plant ='"+MainPlant+"' and Active ='A' ");
if (Curr_ProcurementType =="N")
{
// string InaterNal_validDate =AssignDate;
// string Internal_bomID =getProcurementType(MainMaterialNumber,MainPlant,DataType,AlternativeBom,IntraTransfer);
//
// if (Internal_bomID !="")
// {
// string[] arr =Internal_bomID.Split('_');
// MainPlant =arr[1].ToString();
// if (Internal_bomID !="")
// {
// if (Multibom =="Y")
// SaveBomItem(arr[0].ToString(),AssignDate,DataType,MainMaterialNumber,"T",bomstr,MainPlant);
// }
// }
Response.Write("<script language=javascript>alert('ProcurementType of Top PN:"+MainMaterialNumber+" is N (Internal),No Need to Expand Bom !');</script>");
return str_MaterialTypePN+"_"+str_ProcurementTypePN+"_"+str_QuotationRoutingPN;
}
//判斷是否有QuotationRouting，沒有的話，則退出展ＢＯＭ的程式
if (this.getQuotationRouting(MainMaterialNumber)==false)
{
//Response.Write("<script language=javascript>alert('The QtuotationRouting of the MaterialNumber:"+MainMaterialNumber+" is null ,Expolding BOM Fail !');</script>");
//return;
if (bomstr.IndexOf(MainMaterialNumber,0) ==-1)
str_QuotationRoutingPN=str_QuotationRoutingPN+","+MainMaterialNumber.ToString();
}
if (ds_BomH.Tables[0].Rows.Count<=0 && Curr_ProcurementType !="N")
{
str_MaterialTypePN=str_MaterialTypePN +","+MainMaterialNumber;
return str_MaterialTypePN+"_"+str_ProcurementTypePN+"_"+str_QuotationRoutingPN;
}
else
{
if (Curr_ProcurementType !="N")
{
bomID=ds_BomH.Tables[0].Rows[0]["bomID"].ToString();
//首先插入Ｔop料號的資料,調用getbomID_Data函數取得要插入的數據
string insertstr ="Insert into setBomStructure(QRNo,QRItemNo,AssignDate,BomLocation,AlternativeBom,bomID,DataType,MainMaterialNumber,mainvaliddate,MaterialNumber,ParentPN,Plant,InternalPlant,Level,Item,DescriptionCH,DescriptionEN,Version,ProjectCode,"+
"CMaterialNumber,CMVersion,MaterialType,PartType,BomQty,BomUnit,BHQty,BHUnit,BIQty,BIUnit,BHValidDate,BIValidDate,ComponentScrap,OperationScrap,YieldRate,PriceCategory,PurchaseModel,QuotationRouting,ProCurementType,MaterialOrigin,Remark,"+
"AlternativeFlag,AlternativeGroup,AlternativeRate,SubstituteMaterial,AlternativeQty,AlternativeUnit,VendorByCM,VendorByFIH,NetWeight,GrossWeight,WeightUnit,Source,Active,CreateBy,ModifyBy) "
+getbomID_Data(bomID,DataType,MainMaterialNumber,MainMaterialNumber,MainPlant,bomID,"T",Qty,false,Curr_ProcurementType);
if (func.ExecuteSQL(insertstr) ==false)
{
Response.Write("<script language=javascript>alert('Exploding Bom,insert Top MaterialNumber error');</script>");
return str_MaterialTypePN+"_"+str_ProcurementTypePN+"_"+str_QuotationRoutingPN;
}
//調節用遞歸函數添加主料號下的所有子階料號
SaveBomItem(bomID,AssignDate,DataType, MainMaterialNumber,"T",bomstr,MainPlant);
}
}
return str_MaterialTypePN+"_"+str_ProcurementTypePN+"_"+str_QuotationRoutingPN;
}
public string SaveExplodBom(string DataType,string validdate, string MainPN,string MainPlantName,string AlternativeBomstr,string AssignDatestr,string Location,string Multibomstr,string Procurement,string IntraTransferstr,double Qty,string QRNostr,int QRItemNostr)
{
if (DataType == "") DataType="C";
CommClass.PubFunction func= new CommClass.PubFunction();
bomstr =MainPN;
MainMaterialNumber=MainPN;
MainPlant =MainPlantName;
mainvaliddate = validdate;
QRNo=QRNostr;
QRItemNo =QRItemNostr;
BomLocation =Location;
Multibom=Multibomstr;
AssignDate =AssignDatestr;
AlternativeBom =AlternativeBomstr;
IntraTransfer=IntraTransferstr;
if (MainMaterialNumber =="")
{
Response.Write("<script language=javascript>alert('MaterialNumber is null!');</script>");
return str_MaterialTypePN+"_"+str_ProcurementTypePN+"_"+str_QuotationRoutingPN;
}
if (MainPlant =="")
{
Response.Write("<script language=javascript>alert(' Plant is null!');</script>");
return str_MaterialTypePN+"_"+str_ProcurementTypePN+"_"+str_QuotationRoutingPN;
}
string bomID="0";
// if(QRNo !="")
// {//刪除QR下的BOM
// if (func.ExecuteSQL("delete from setBomStructure where isnull(QRNo,'') ='"+QRNo+"' and QRitemNo ='"+QRItemNo+"'") ==false)
// {
// Response.Write("<script language=javascript>alert('Delete the old BOM of QRNo ="+QRNo+" and QRItemNo= "+QRItemNo+" error');</script>");
// }
// }
if (Location=="SetBom")
{
//刪除當前天以前的資料
if (func.ExecuteSQL("delete from setBomStructure where isnull(QRNo,'') ='"+QRNo+"' and MainMaterialNumber ='"+MainMaterialNumber+"' and Plant='" + MainPlant +"' "+
"and DataType='"+ DataType+"' and MainValidDate ='"+mainvaliddate+"' and AlternativeBom ='"+AlternativeBom+"' "+ //and AssignDate <='"+AssignDate+"'
"and BomLocation ='"+Location+"' ") ==false)
{
Response.Write("<script language=javascript>alert('Delete the old BOM of MainMaterialNumber :"+MainMaterialNumber+" Failed!');</script>");
return str_MaterialTypePN+"_"+str_ProcurementTypePN+"_"+str_QuotationRoutingPN;
}
}
else //Approve的地方,刪除指定時間之前的所有資料
{
if (UpdateOrDeletePN(MainMaterialNumber,MainPlant) ==false)
{
// if (func.ExecuteSQL("delete from setBomStructure where isnull(QRNo,'') ='"+QRNo+"' and QRitemNo ='"+QRItemNo+"' "+ //and AssignDate <>'"+AssignDate+"'
// " and MainMaterialNumber ='"+MainMaterialNumber+"' and Plant='" + MainPlant +"' "+
// "and DataType='"+ DataType+"' and MainValidDate ='"+mainvaliddate+"' and AlternativeBom ='"+AlternativeBom+"' "+
// "and BomLocation ='"+Location+"' ") ==false)
if (func.ExecuteSQL("delete from setBomStructure where isnull(QRNo,'') ='"+QRNo+"' and QRitemNo ='"+QRItemNo+"' "+ //and AssignDate <>'"+AssignDate+"'
"and BomLocation ='"+Location+"' ") ==false)
{
Response.Write("<script language=javascript>alert('Delete the old BOM of MainMaterialNumber :"+MainMaterialNumber+" before "+AssignDate+" error');</script>");
return str_MaterialTypePN+"_"+str_ProcurementTypePN+"_"+str_QuotationRoutingPN;
}
}
if (func.GetDataTable("select * from setbomstructure where isnull(QRNo,'') ='"+QRNo+"' and QRitemNo ='"+QRItemNo+"' and MainMaterialNumber ='"+MainMaterialNumber+"' and Plant='" + MainPlant +"' "+
"and DataType='"+ DataType+"' and MainValidDate ='"+mainvaliddate+"' and AlternativeBom ='"+AlternativeBom+"' "+
"and BomLocation ='"+Location+"' and AssignDate ='"+AssignDate+"' ").Tables[0].Rows.Count >0)
{ //刪除指定時間以前的ＢＯＭ後退出
return str_MaterialTypePN+"_"+str_ProcurementTypePN+"_"+str_QuotationRoutingPN;
}
}
//至bom header檔中查找資料
DataSet ds_BomH = func.GetDataTable("select * from setbomheader where materialnumber='"+ MainMaterialNumber +"' and plant='"+ MainPlant +"' "+
"and ValidDate='"+ mainvaliddate +"' and datatype='"+ DataType +"' and AlternativeBom ='"+AlternativeBom+"' and Active ='A' ");
//得到料號在當前工廠中的 ProcurementType ,看是否是內交料
string Curr_ProcurementType =func.GetDataOne("select ProcurementType from pubMaterialParameter where MaterialNumber ='"+MainMaterialNumber+"' and Plant ='"+MainPlant+"' and Active ='A' ");
if (Curr_ProcurementType =="N")//展內交BOM
{
string InaterNal_validDate =AssignDate;
string Internal_bomID =getProcurementType(MainMaterialNumber,MainPlant,DataType,AlternativeBom,IntraTransfer);
if (Internal_bomID !="")
{
string[] arr =Internal_bomID.Split('_');
MainPlant =arr[1].ToString();
if (Internal_bomID !="")
{
if (Multibom =="Y")
SaveBomItem(arr[0].ToString(),AssignDate,DataType,MainMaterialNumber,"T",bomstr,MainPlant);
}
}
}
//判斷是否有QuotationRouting，沒有的話，則退出展ＢＯＭ的程式
if (this.getQuotationRouting(MainMaterialNumber)==false)
{
//Response.Write("<script language=javascript>alert('The QtuotationRouting of the MaterialNumber:"+MainMaterialNumber+" is null ,Expolding BOM Fail !');</script>");
//return;
if (bomstr.IndexOf(MainMaterialNumber,0) ==-1)
str_QuotationRoutingPN=str_QuotationRoutingPN+","+MainMaterialNumber.ToString();
}
if (ds_BomH.Tables[0].Rows.Count<=0 && Curr_ProcurementType !="N")
{
str_MaterialTypePN=str_MaterialTypePN +","+MainMaterialNumber;
return str_MaterialTypePN+"_"+str_ProcurementTypePN+"_"+str_QuotationRoutingPN;
}
else
{
if (Curr_ProcurementType !="N")
{
bomID=ds_BomH.Tables[0].Rows[0]["bomID"].ToString();
//首先插入Ｔop料號的資料,調用getbomID_Data函數取得要插入的數據
string insertstr ="Insert into setBomStructure(QRNo,QRItemNo,AssignDate,BomLocation,AlternativeBom,bomID,DataType,MainMaterialNumber,mainvaliddate,MaterialNumber,ParentPN,Plant,InternalPlant,Level,Item,DescriptionCH,DescriptionEN,Version,ProjectCode,"+
"CMaterialNumber,CMVersion,MaterialType,PartType,BomQty,BomUnit,BHQty,BHUnit,BIQty,BIUnit,BHValidDate,BIValidDate,ComponentScrap,OperationScrap,YieldRate,PriceCategory,PurchaseModel,QuotationRouting,ProCurementType,MaterialOrigin,Remark,"+
"AlternativeFlag,AlternativeGroup,AlternativeRate,SubstituteMaterial,AlternativeQty,AlternativeUnit,VendorByCM,VendorByFIH,NetWeight,GrossWeight,WeightUnit,Source,Active,CreateBy,ModifyBy) "
+getbomID_Data(bomID,DataType,MainMaterialNumber,MainMaterialNumber,MainPlant,bomID,"T",Qty,false,Curr_ProcurementType);
if (func.ExecuteSQL(insertstr) ==false)
{
Response.Write("<script language=javascript>alert('Exploding Bom,insert Top MaterialNumber error');</script>");
return str_MaterialTypePN+"_"+str_ProcurementTypePN+"_"+str_QuotationRoutingPN;
}
//調節用遞歸函數添加主料號下的所有子階料號
SaveBomItem(bomID,AssignDate,DataType, MainMaterialNumber,"T",bomstr,MainPlant);
}
}
return str_MaterialTypePN+"_"+str_ProcurementTypePN+"_"+str_QuotationRoutingPN;
}
/// <summary>
///
/// </summary>
/// <param name="ParentBomID">需要展的BomID,bomID為上階料號的bomID</param>
/// <param name="AssignDate">批定的時間<</param>
/// <param name="DataType"></param>
/// <param name="ParentMaterialNumber"></param>
/// <param name="str_Level"></param>
/// <param name="bomstr"></param>
/// <param name="Curr_Plant">在當前工廠中展BOM</param>
private void SaveBomItem(string ParentBomID,string AssignDate,string DataType,string ParentMaterialNumber,string str_Level,string bomstr,string Curr_Plant)
{
CommClass.PubFunction func= new CommClass.PubFunction();
//getbomItem,找出一個BomID下面所有要循環的Item,ParentBomID為上階料號的bomID
DataSet ds_item =func.GetDataTable(this.getbomItem(ParentBomID,AssignDate));
string Level_str;
string Parent_bomID;//當前bomID的ParentPN的bomID
Parent_bomID=ParentBomID;
string save_plant;// =Curr_Plant;
string InternalbomID="",Internal_MainPlant="";
bool InterNal =false;
for(int i=0;i < ds_item.Tables[0].Rows.Count;i++)
{
save_plant =Curr_Plant;
//循環每一個料號，從參數檔中找相關的記錄
DataRow dr;
dr= ds_item.Tables[0].Rows[i];
Level_str =str_Level;
//insert or update base material number
if (str_Level !="T")
{
Level_str =Level_str+"."+(i+1).ToString();
}
else
Level_str =(i+1).ToString();
//得到上階料號在BOM中的實際用量,從setBomStructure中讀取
double ParentQty=getParentPNQty(MainMaterialNumber,ParentMaterialNumber,Curr_Plant,DataType,AlternativeBom);
//得到料號在當前工廠中的 ProcurementType
string Curr_ProcurementType =func.GetDataOne("select ProcurementType from pubMaterialParameter where MaterialNumber ='"+dr["Component"].ToString()+"' and Plant ='"+save_plant+"' and Active ='A' ");
//針對要循環的Item,找出有效時間小于AssignDate中最大的一筆
DataSet ds=func.GetDataTable("select bomID,ValidDate from setBOMHeader where MaterialNumber='"+dr["Component"].ToString()+"' "+
"and Plant='"+ Curr_Plant +"' and DataType='"+ DataType+"' and AlternativeBom ='"+AlternativeBom+"' and Active='A'" +
" and validdate= (select max(ValidDate) from setBOMHeader where MaterialNumber='"+dr["Component"].ToString()+"' "+
"and Plant='"+ Curr_Plant +"' and DataType='"+ DataType+"' and ValidDate <='"+AssignDate+"' and AlternativeBom ='"+AlternativeBom+"' and Active='A') " );
string Internal_PN =func.GetDataOne("select ProcurementType from pubMaterialParameter where MaterialNumber ='"+dr["Component"].ToString()+"' and Plant ='"+Curr_Plant+"' and Active ='A' ");
if (Internal_PN =="N")
{
string InaterNal_validDate =AssignDate;
string Internal_bomID =getProcurementType(dr["Component"].ToString(),Curr_Plant,DataType,AlternativeBom,IntraTransfer);
if (Internal_bomID !="")
{
InterNal=true;
string[] arr =Internal_bomID.Split('_');
InternalbomID =arr[0].ToString();
Internal_MainPlant =arr[1].ToString();
save_plant =Internal_MainPlant;
//得到料號在內交工廠中的 ProcurementType
Curr_ProcurementType =func.GetDataOne("select ProcurementType from pubMaterialParameter where MaterialNumber ='"+dr["Component"].ToString()+"' and Plant ='"+save_plant+"' and Active ='A' ");
}
else//內交工廠沒有BOM時
{
save_plant=func.GetDataOne("select InternalPlant from pubMaterialParameter where MaterialNumber ='"+dr["Component"].ToString()+"' and Plant ='"+Curr_Plant+"' and Active ='A' ");
Curr_ProcurementType ="N";
}
}
//插入本料號的數據
//string Plant_Save =getParentPlant(this.MainMaterialNumber,ParentMaterialNumber,this.MainPlant,DataType,this.AlternativeBom);
if (ds.Tables[0].Rows.Count>0)
{
string insertstr ="Insert into setBomStructure(QRNo,QRItemNo,AssignDate,BomLocation,AlternativeBom,bomID,DataType,MainMaterialNumber,mainvaliddate,MaterialNumber,ParentPN,Plant,InternalPlant,Level,Item,DescriptionCH,DescriptionEN,Version,ProjectCode,"+
"CMaterialNumber,CMVersion,MaterialType,PartType,BomQty,BomUnit,BHQty,BHUnit,BIQty,BIUnit,BHValidDate,BIValidDate,ComponentScrap,OperationScrap,YieldRate,PriceCategory,PurchaseModel,QuotationRouting,ProCurementType,MaterialOrigin,Remark,"+
"AlternativeFlag,AlternativeGroup,AlternativeRate,SubstituteMaterial,AlternativeQty,AlternativeUnit,VendorByCM,VendorByFIH,NetWeight,GrossWeight,WeightUnit,Source,Active,CreateBy,ModifyBy) "
+getbomID_Data(Parent_bomID,DataType,ParentMaterialNumber,dr["Component"].ToString(),save_plant,dr["ID"].ToString(), Level_str,ParentQty,InterNal,Curr_ProcurementType);
if (func.ExecuteSQL(insertstr) ==false)
{
Response.Write("<script language=javascript>alert('insert sub material number error');</script>");
}
}
else
{
string insertstr ="Insert into setBomStructure(QRNo,QRItemNo,AssignDate,BomLocation,AlternativeBom,bomID,DataType,MainMaterialNumber,mainvaliddate,MaterialNumber,ParentPN,Plant,InternalPlant,Level,Item,DescriptionCH,DescriptionEN,Version,ProjectCode,"+
"CMaterialNumber,CMVersion,MaterialType,PartType,BomQty,BomUnit,BHQty,BHUnit,BIQty,BIUnit,BHValidDate,BIValidDate,ComponentScrap,OperationScrap,YieldRate,PriceCategory,PurchaseModel,QuotationRouting,ProCurementType,MaterialOrigin,Remark,"+
"AlternativeFlag,AlternativeGroup,AlternativeRate,SubstituteMaterial,AlternativeQty,AlternativeUnit,VendorByCM,VendorByFIH,NetWeight,GrossWeight,WeightUnit,Source,Active,CreateBy,ModifyBy) "
+getbomID_Data(Parent_bomID,DataType,ParentMaterialNumber,dr["Component"].ToString(),save_plant,dr["ID"].ToString(), Level_str,ParentQty,InterNal,Curr_ProcurementType);
if (func.ExecuteSQL(insertstr) ==false)
{
Response.Write("<script language=javascript>alert('insert sub material number error');</script>");
}
}
// string Internal_PN =func.GetDataOne("select ProcurementType from pubMaterialParameter where MaterialNumber ='"+dr["Component"].ToString()+"' and Plant ='"+Curr_Plant+"' and Active ='A' ");
// if (Internal_PN =="N")
// {
// string InaterNal_validDate =AssignDate;
// string Internal_bomID =getProcurementType(dr["Component"].ToString(),Curr_Plant,DataType,AlternativeBom,IntraTransfer);
//
// if (Internal_bomID !="")
// {
// InterNal=true;
// string[] arr =Internal_bomID.Split('_');
// InternalbomID =arr[0].ToString();
// Internal_MainPlant =arr[1].ToString();
// save_plant =Internal_MainPlant;
if (Internal_PN =="N")
{
if (InternalbomID !="")
{
if (Multibom =="Y")
SaveBomItem(InternalbomID,AssignDate,DataType,dr["Component"].ToString(),Level_str,bomstr,save_plant);
}
}
// if (Curr_ProcurementType =="F")
// break;
//如果有自己的BOM且不是內交料號
if (ds.Tables[0].Rows.Count >0)
{
if (Internal_PN !="N" && Curr_ProcurementType !="F")
{
//判斷是否有QuotationRouting
if (this.getQuotationRouting(dr["Component"].ToString())==false)
{
if (bomstr.IndexOf(dr["Component"].ToString(),0) ==-1)
str_QuotationRoutingPN=str_QuotationRoutingPN+","+dr["Component"].ToString();
}
//有子階時,遞歸調用
if (bomstr.IndexOf(dr["Component"].ToString(),0) ==-1)
{
bomstr =bomstr+"_"+dr["Component"].ToString();
if (Multibom =="Y")
SaveBomItem(ds.Tables[0].Rows[0]["bomID"].ToString(),AssignDate,DataType,dr["Component"].ToString(),Level_str,bomstr,Curr_Plant);
}
}
}
}
}
/// <summary>
///
/// </summary>
/// <param name="ParentPNbomID">要得到的料號的信息的上階料號的bomID</param>
/// <param name="DataType"></param>
/// <param name="validdate"></param>
/// <param name="ParentPN"></param>
/// <param name="MaterialNumber"></param>
/// <param name="Plant"></param>
/// <param name="ID">子階料號中的ID</param>
/// <param name="Level_str"></param>
/// <param name="ParentPNBomQty">上階料號的BOM用量</param>
/// <param name="InterNalTrue">是否內交的標志</param>
/// <param name="ProCurementType">所要保存料號的ProcurementType</param>
/// <returns></returns>
private string getbomID_Data(string ParentPNbomID,string DataType,string ParentPN,string MaterialNumber, string Plant, string ID, string Level_str,double ParentPNBomQty,bool InterNalTrue,string ProCurementType)
{
string str_level="";
double str_BomQty=0;//Bom用量
string str_BomUnit="";
double str_BHQty=0;//作為Header使用的用量
string str_BHUnit="";
double str_BIQty=0;//作為Item使用的用量
string str_BIUnit="";
double BIQty=1,BHQty=1,BomQty=1;
//分三種情況處理Qty
//1、Top
//2、自制件
//3、外購件
//料號的BOM用量,為料號在Item中的用量/料號的上階料號的用量*上階料號的BOM用量
// string bomID="0";
string bomID =ParentPNbomID;
string BHValidDate="";
string BIValidDate="";
string Item="1";
DataSet ds_H=func.GetDataTable("select bomID,BaseQty,Unit,ValidDate from setBOMHeader where Plant ='"+Plant+"' "+
"and MaterialNumber='"+MaterialNumber+"' and DataType='"+ DataType +"' and "+
"validDate<='"+ this.AssignDate+"' and alternativebom='"+AlternativeBom+"' and Active='A' ");
//string str_ProCurementType =func.GetDataOne("select ProcurementType from pubMaterialParameter where MaterialNumber ='"+MaterialNumber+"' and Plant ='"+Plant+"' and Active ='A' ");
string str_ProCurementType=ProCurementType;
if (Level_str !="T")
{
str_level=Level_str;//得到類似 .１,..2和...3的值(點號的個數等于數字的值)
DataSet ds_I =func.GetDataTable("select * from setbomItem where ID ="+ID+" and Active ='A' order by item ");
if (ds_I.Tables[0].Rows.Count >0)
{
BIValidDate =string.Format("{0:yyyy-MM-dd}",ds_I.Tables[0].Rows[0]["ValidDateFrom"]);
//bomID = ds_I.Tables[0].Rows[0]["bomID"].ToString();
DataTable dt =func.GetDataTable("select bomID from setbomHeader where MaterialNumber='"+MaterialNumber+"' and Plant='"+Plant+"' and Active ='A' and ValidDate <='"+this.AssignDate+"' ").Tables[0];
if (dt.Rows.Count >0)
{
bomID =dt.Rows[0]["bomID"].ToString();
//驗證ProcurementType 是否正确
ProcurementType_Right(str_ProCurementType,true,MaterialNumber);
}
else
bomID ="0";
Item =ds_I.Tables[0].Rows[0]["Item"].ToString();
// BIQty =(Convert.ToDouble(ds_I.Tables[0].Rows[0]["Qty"])*func.GetSrcToDestUnitRate(str_BIUnit,MainUnit));
BIQty =Convert.ToDouble(ds_I.Tables[0].Rows[0]["Qty"]);
//得到上階料號的用量,BHQty,BHQty =料號作為Item的用量*料號作為Header的用量
DataSet ds_BHQty =func.GetDataTable("select bomID,BaseQty,Unit,ValidDate from setBOMHeader where bomID ='"+ParentPNbomID+"' ");
if (ds_BHQty.Tables[0].Rows.Count >0)
{
//上階料號維護的用量
BHQty =Convert.ToDouble(ds_BHQty.Tables[0].Rows[0]["BaseQty"]);
}
else
{
Response.Write("<script language=javascript>alert('Get ParentPN:"+ParentPN+" Qty Error!');</script>");
return "";
}
//如果Item中的bomID在Header中有記錄,則料號為自制件,Item的BOM用量必須*Header的用量
DataSet ds_I_H =func.GetDataTable("select * from setbomHeader where bomID ="+bomID+" and Active ='A' ");
if (ds_I_H.Tables[0].Rows.Count >0)
{//2、自制件
//BIQty/BHQty*ParentPNBomQty
str_BIUnit=ds_I.Tables[0].Rows[0]["Unit"].ToString();
// str_BIQty =BIQty.ToString();
BomQty =(BIQty/BHQty)*ParentPNBomQty;
str_BomQty=BomQty;
str_BomUnit =ds_I.Tables[0].Rows[0]["Unit"].ToString();
if (ds_H.Tables[0].Rows.Count>0)
{
str_BIQty =BomQty;
str_BHQty =ParentPNBomQty;
str_BHUnit =ds_H.Tables[0].Rows[0]["Unit"].ToString();
}
}
else
{//3、外購件,如果無記錄，則說明料號只在Item 中，為外購件
str_BIUnit=ds_I.Tables[0].Rows[0]["Unit"].ToString();
str_BIQty =BIQty;
BomQty =(BIQty/BHQty)*ParentPNBomQty;
str_BomQty=BomQty;
str_BomUnit =ds_I.Tables[0].Rows[0]["Unit"].ToString();
str_BHQty =0;
str_BHUnit ="";
//驗證ProcurementType 是否正确
ProcurementType_Right(str_ProCurementType,false,MaterialNumber);
}
}
}
else
{//1、Top
str_level="Top";
//如果為Top料號時,ID中傳入的bomID,在此計算它的bom用量
DataSet ds_bom =func.GetDataTable("select * from setbomHeader where bomID ="+ID+" and Active ='A' ");
if (ds_bom.Tables[0].Rows.Count >0)
{
str_BIQty =0;
str_BIUnit ="";
str_BHQty =ParentPNBomQty;
str_BHUnit =ds_bom.Tables[0].Rows[0]["Unit"].ToString();
str_BomQty =ParentPNBomQty;
str_BomUnit=ds_bom.Tables[0].Rows[0]["Unit"].ToString();
}
}
//從Ｒouting中抓本制程良率(自制和外購)
double str_Rate=0;
//string str_ProCurementType=ProCurementType;
//得到料號在當前工廠中的 ProcurementType
// string str_ProCurementType =func.GetDataOne("select ProcurementType from pubMaterialParameter where MaterialNumber ='"+MaterialNumber+"' and Plant ='"+Plant+"' and Active ='A' ");
//抓取 QuotationRouting 的值(分有無子階料號)
string str_QuotationRouting="";
DataSet ds=func.GetDataTable("select isnull(QuotationRouting,'') as QuotationRouting from pubMaterialMaster where MaterialNumber='"+MaterialNumber+"' and Active='A' ");
str_QuotationRouting =ds.Tables[0].Rows[0]["QuotationRouting"].ToString();
// DataSet ds_Q=func.GetDataTable("select MaterialNumber from setBomHeader where BomID =(select bomID from setBOMItem where Component='"+MaterialNumber+"')");
DataSet ds_Q =func.GetDataTable("select * from setBOMItem where bomID in ( select BomID from setBomHeader "+
"where MaterialNumber ='"+MaterialNumber+"' and Plant='"+Plant+"' and DataType='"+ DataType +"' "+
"and validdate <='"+ this.AssignDate+ "' and Active ='A' and AlternativeBom='"+AlternativeBom+"' ) and Active='A' order by item");
if (ds_Q.Tables[0].Rows.Count <=0)
{
//上一階料號的 QuotationRouting值
DataSet ds_ParentPN=func.GetDataTable("select isnull(QuotationRouting,'') as QuotationRouting from pubMaterialMaster where MaterialNumber='"+ParentPN+"' and Active='A' ");
if ( ds_ParentPN.Tables[0].Rows.Count >0 )
{
str_QuotationRouting =ds_ParentPN.Tables[0].Rows[0]["QuotationRouting"].ToString();
//判斷是否有 QuotationRouting
QuotationRouting_Right(str_QuotationRouting,true,ParentPN);
// if (str_QuotationRouting ==null || str_QuotationRouting =="")
// str_QuotationRoutingPN=str_QuotationRoutingPN +","+MaterialNumber;
// else
// {
//
// if (str_QuotationRoutingPN ==null || str_QuotationRoutingPN =="")
// str_QuotationRoutingPN=MaterialNumber;
// else
// {
// if (str_QuotationRoutingPN.ToString().IndexOf(MaterialNumber,0) ==-1)
// str_QuotationRoutingPN=str_QuotationRoutingPN +","+MaterialNumber;
// }
// }
}
}
DataSet ds_Rate =func.GetDataTable("select ProcurementType,isnull(ScrapRate,0) as ScrapRate from pubMaterialParameter where MaterialNumber='" + MaterialNumber+ "' and Plant='"+ Plant+"' and Active='A' ");
if (ds_Rate.Tables[0].Rows.Count >0)
{
if (ds_Rate.Tables[0].Rows[0]["ProcurementType"].ToString() =="E")
{//自制
DataSet ds11 = func.GetDataTable("select isnull(LossRate,0) as LossRate from setRoutingItem where rtID =(select top 1 rtID from setRoutingHeader where MaterialNumber='" + MaterialNumber+ "' and Plant='"+ Plant+"' and Active='A' order by ValidDate desc)");
if (ds11.Tables[0].Rows.Count >0 )
{
str_Rate=1-Convert.ToDouble(ds11.Tables[0].Rows[0]["LossRate"].ToString());
}
}
else
{
if (ds_Rate.Tables[0].Rows[0]["ScrapRate"]==System.DBNull.Value)
str_Rate =1;
else
str_Rate =1-Convert.ToDouble(ds_Rate.Tables[0].Rows[0]["ScrapRate"]);
}
}
else
str_Rate =1;
//從info Record 中抓價格等級,采購方式,物料來源
DataSet ds_3 =func.GetDataTable("select PriceCategory,PurchaseModel,MaterialOrigin from setInfoRecordHeader where MaterialNumber='" + MaterialNumber+ "' and Plant='"+ Plant+"' and Active='A' ");
string str_Price="0";
string str_Model="";
string str_Origin="";
if(ds_3.Tables[0].Rows.Count >0)
{
str_Price=ds_3.Tables[0].Rows[0]["PriceCategory"].ToString();
str_Model=ds_3.Tables[0].Rows[0]["PurchaseModel"].ToString();
str_Origin=ds_3.Tables[0].Rows[0]["MaterialOrigin"].ToString();
}
string instr;
DataSet ds_HH=func.GetDataTable("select bomID,BaseQty,Unit,ValidDate from setBOMHeader where Plant ='"+Plant+"' "+
"and MaterialNumber='"+MaterialNumber+"' and DataType='"+ DataType +"' and "+
"validDate <='"+ AssignDate +"' and alternativebom='"+AlternativeBom+"' and Active='A' ");
string PN_bomID;//如果是自制件時,為料號的bomID,否則為它的上階料號的bomID
if (ds_HH.Tables[0].Rows.Count >0)
{
// >0 表示此料號是自制件, 有子階料號, 則(bomQt和BIQty換位存儲)
if (Level_str =="T")
PN_bomID =ParentPNbomID;
else
PN_bomID=ds_HH.Tables[0].Rows[0]["bomID"].ToString();
instr="select "+ConvertToDBStr(QRNo)+","+QRItemNo+",'"+AssignDate+"','"+BomLocation+"','"+AlternativeBom+"',"+PN_bomID+", '"+DataType+"','"+MainMaterialNumber+"','"+ mainvaliddate+"','"+MaterialNumber+"','"+ParentPN+"','"+MainPlant+"','"+Plant+"','"+str_level+"','"+Item+"',b.DescriptionCH,b.DescriptionEN,b.Version,b.ProjectCode,"+
"b.CMaterialNumber,b.CVersion,b.MaterialType,b.PartType,"+str_BIQty+",'"+str_BIUnit+"',"+str_BHQty+",'"+str_BHUnit+"',"+str_BomQty+",'"+str_BomUnit+"',"+
"case when '"+BHValidDate+"'='' then NULL else '"+BHValidDate+"' end ,case when '"+BIValidDate+"'='' then NULL else '"+BIValidDate+"' end ,'0','0','"+str_Rate+"','"+str_Price+"','"+str_Model+"','"+str_QuotationRouting+"','"+str_ProCurementType+"','"+str_Origin+"',b.Remark,"+
"'','','0','','0','','',"+
"'',b.NetWeight,b.GrossWeight,b.WeightUnit,'M','A', '"+ Request.Cookies["LoginID"].Value +"','"+ Request.Cookies["LoginID"].Value+"' from "+
"(select * from pubMaterialMaster where MaterialNumber ='"+MaterialNumber+"' and Active ='A') b";
}
else
{
PN_bomID=ParentPNbomID;
instr= "select "+ConvertToDBStr(QRNo)+","+QRItemNo+",'"+AssignDate+"','"+BomLocation+"','"+AlternativeBom+"',"+PN_bomID+",'"+DataType+"','"+MainMaterialNumber+"','"+ mainvaliddate+"','"+MaterialNumber+"','"+ParentPN+"','"+MainPlant+"','"+Plant+"','"+str_level+"',isnull(a.Item,1),b.DescriptionCH,b.DescriptionEN,b.Version,b.ProjectCode,"+
"b.CMaterialNumber,b.CVersion,b.MaterialType,b.PartType,"+str_BIQty+",'"+str_BIUnit+"',"+str_BHQty+",'"+str_BHUnit+"',"+str_BomQty+",'"+str_BomUnit+"',"+
"case when '"+BHValidDate+"'='' then NULL else '"+BHValidDate+"' end ,case when '"+BIValidDate+"'='' then NULL else '"+BIValidDate+"' end ,a.ComponentScrap,a.OperationScrap,'"+str_Rate+"','"+str_Price+"','"+str_Model+"','"+str_QuotationRouting+"','"+str_ProCurementType+"','"+str_Origin+"',b.Remark,"+
"a.AlternativeFlag,a.AlternativeGroup,a.AlternativeRate,a.SubstituteMaterial,a.AlternativeQty,a.AlternativeUnit,a.VendorByCM,"+
"a.VendorByFIH,b.NetWeight,b.GrossWeight,b.WeightUnit,'M','A', '"+ Request.Cookies["LoginID"].Value +"','"+ Request.Cookies["LoginID"].Value+"' from "+
"(select * from setBomItem where ID ='"+ID+"' and Active ='A') a,"+
"(select * from pubMaterialMaster where MaterialNumber ='"+MaterialNumber+"' and Active ='A') b";
}
return instr;
}
private void ProcurementType_Right(string Curr_ProcurementType,bool SubPN,string MPN)
{
//判斷ProcurementType 是否正确
bool Flag;
Flag =true;
if (SubPN)
{
if (Curr_ProcurementType ==null || Curr_ProcurementType =="F")
Flag =false;
}
else
{
if (Curr_ProcurementType ==null ||Curr_ProcurementType =="E")
Flag =false;
}
if (!Flag)
{
if (str_ProcurementTypePN ==null || str_ProcurementTypePN =="")
str_ProcurementTypePN=MPN;
else
{
if (str_ProcurementTypePN.ToString().IndexOf(MPN,0) ==-1)
str_ProcurementTypePN=str_ProcurementTypePN +","+MPN;
}
}
}
private void QuotationRouting_Right(string Curr_QuotationRouting,bool SubPN,string MPN)
{
//判斷ProcurementType 是否正确
bool Flag;
Flag =true;
if (SubPN)
{
if (Curr_QuotationRouting==null || Curr_QuotationRouting =="")
Flag =false;
}
if (!Flag)
{
if (str_QuotationRoutingPN ==null || str_QuotationRoutingPN =="")
str_QuotationRoutingPN=MPN;
else
{
if (str_QuotationRoutingPN.ToString().IndexOf(MPN,0) ==-1)
str_QuotationRoutingPN=str_QuotationRoutingPN +","+MPN;
}
}
}
public string getbomItem(string bomID,string AssignDateTime)
{
//分兩部分找到Item,一部分料號沒有ValidDate,另一部分取ValidDate 最大的一筆
// string sql="select * from setbomItem where Active ='A' and bomID ="+bomID+" and Component not in ("+
// "select Component from setbomItem where bomID ="+bomID+" group by Component having Count(*) >1) and ValidDate <='"+AssignDateTime+"' "+
// "union "+
// "select * from setbomItem where ValidDate <='"+AssignDateTime+"' and Active ='A' and bomID ="+bomID+" and Component =("+
// "select component from setbomItem where bomID ="+bomID+" group by Component having Count(*) >1) and ValidDate ="+
// "(select Max(ValidDate) from setbomitem where bomID="+bomID+" and Component =("+
// "select component from setbomItem where bomID ="+bomID+" group by Component having Count(*) >1) )";
string sql="select * from setbomItem where Active ='A' and bomID ="+bomID+" and Component not in ("+
"select Component from setbomItem where bomID ="+bomID+" group by Component having Count(*) >1) and ValidDate <='"+AssignDateTime+"' "+
"union "+
"select a.* from setbomItem a left join ("+
"select component,Max(ValidDate) MaxValidDate from setbomItem where bomID ="+bomID+" group by Component having Count(*) >1"+
") b on a.component=b.component and a.ValidDate =b.MaxValidDate where ValidDate <='"+AssignDateTime+"' and Active ='A' and bomID ="+bomID+" order by item";
return sql;
}
/// <summary>
/// 沒有QuotationRouting，返回False
/// </summary>
/// <param name="PN"></param>
/// <returns></returns>
private bool getQuotationRouting(string PN,string QRNo)
{
//料號在當前工廠中沒有bom時,至料號參數檔看有沒有內交bom
bool QuotationRouting_bool =true;
if (QRNo !="")
{
string QuotationRouting = func.GetDataOne("select isnull(QuotationRouting,'') as QuotationRouting from pubMaterialMaster where materialnumber='"+ PN +"' and Active ='A' ");
if (QuotationRouting =="")
{
QuotationRouting_bool =false;
}
}
return QuotationRouting_bool;
}
/// <summary>
/// 沒有QuotationRouting，返回False
/// </summary>
/// <param name="PN"></param>
/// <returns></returns>
private bool getQuotationRouting(string PN)
{
//料號在當前工廠中沒有bom時,至料號參數檔看有沒有內交bom
bool QuotationRouting_bool =true;
string QuotationRouting = func.GetDataOne("select isnull(QuotationRouting,'') as QuotationRouting from pubMaterialMaster where materialnumber='"+ PN +"' and Active ='A' ");
if (QuotationRouting =="")
{
QuotationRouting_bool =false;
}
return QuotationRouting_bool;
}
private string getProcurementType(string PN,string Plantstr,string DataType,string AlternativeBom,string IntraTransfer)
{
string ProcurementType;
string bomID="";///要返回的作為內交的bomID
string return_str ="";
string InternalPlant =Plantstr;
// DataSet ds_BomH = func.GetDataTable("select * from setbomheader where materialnumber='"+ PN +"' and plant='"+ InternalPlant +"' "+
// "and ValidDate <='"+ mainvaliddate +"' and datatype='"+ DataType +"' and AlternativeBom ='"+AlternativeBom+"' and Active ='A' "+
// "and ValidDate =(select max(ValidDate) from setbomHeader where materialnumber='"+ PN +"' and plant='"+ InternalPlant +"' "+
// "and ValidDate <='"+ mainvaliddate +"' and datatype='"+ DataType +"' and AlternativeBom ='"+AlternativeBom+"' and Active ='A' )");
//
// if (ds_BomH.Tables[0].Rows.Count >0)
// return return_str;
// else //if (ds_BomH.Tables[0].Rows.Count<=0)
// {
//料號在當前工廠中沒有bom時,至料號參數檔看有沒有內交bom
DataSet ds_Parameter = func.GetDataTable("select * from pubMaterialParameter where materialnumber='"+ PN +"' "+
"and plant='"+ InternalPlant +"' and Active ='A' ");
if (ds_Parameter.Tables[0].Rows.Count >0)
{
string MMType ="";
MMType =func.GetDataOne("select MaterialType from pubmaterialmaster where MaterialNumber ='"+PN+"' and Active ='A' ");
// if (MMType =="S" || MMType =="F")
// {
ProcurementType =ds_Parameter.Tables[0].Rows[0]["ProcurementType"].ToString();
//如果Ｈeader和參數檔中有資料，則繼續執行
if (ProcurementType =="N")
{
//查看料號在內交工廠內有沒有有效期內的BomID,如果有就選最大有效的那筆
DataSet ds_BomH = func.GetDataTable("select * from setbomheader where materialnumber='"+ PN +"' "+
"and plant='"+ ds_Parameter.Tables[0].Rows[0]["InternalPlant"].ToString() +"'"+
"and datatype='"+ DataType +"' and AlternativeBom ='"+AlternativeBom+"' and Active ='A' "+
" and ValidDate =(select max(ValidDate) from setbomheader where materialnumber='"+ PN +"' "+
"and plant='"+ ds_Parameter.Tables[0].Rows[0]["InternalPlant"].ToString() +"'"+
"and datatype='"+ DataType +"' and AlternativeBom ='"+AlternativeBom+"' and Active ='A' and ValidDate <='"+AssignDate+"' ) ");
if (ds_BomH.Tables[0].Rows.Count >0)
{
//如果料號為內交時,根據傳入的參數IntraTransfer判斷，根據料號參數檔中的料號和內交工廠,輸入的data type,至bom header 中查找bom
//string Flag =func.GetDataOne("select Flag from pubInterNalPlant where Plant ='"+InternalPlant+"' and InternalPlant ='"+ds_Parameter.Tables[0].Rows[0]["InternalPlant"].ToString()+"' ");
bool Flag =InterNalPlant(InternalPlant,ds_Parameter.Tables[0].Rows[0]["InternalPlant"].ToString());
if (IntraTransfer =="Y")
{
if (Flag)
{
bomID =ds_BomH.Tables[0].Rows[0]["bomID"].ToString();
InternalPlant =ds_BomH.Tables[0].Rows[0]["Plant"].ToString();
return_str= bomID+"_"+InternalPlant;
}
}
}
else
{
//Response.Write("<script language=javascript>alert('the MaterialNumber:"+PN+" is Internal ,it is need a bom!');</script>");
str_MaterialTypePN=str_MaterialTypePN +","+PN;
}
}
else if (ProcurementType =="E")
{
// if (MMType =="S" || MMType =="F")
// {
//Response.Write("<script language=javascript>alert('ProcurementType of the MaterialNumber:"+PN+" is E and MaterialType is "+MMType+" !');</script>");
if (bomstr.IndexOf(PN,0) ==-1)
str_MaterialTypePN=str_MaterialTypePN +","+PN;
// }
}
else if (ProcurementType =="W")
{
if (bomstr.IndexOf(PN,0) ==-1)
str_MaterialTypePN=str_MaterialTypePN +","+PN;
}
else if (ProcurementType =="T")
{
if (bomstr.IndexOf(PN,0) ==-1)
str_MaterialTypePN=str_MaterialTypePN +","+PN;
}
else if (ProcurementType =="F")
{
if (MMType =="S" || MMType =="F")
{
if (bomstr.IndexOf(PN,0) ==-1)
str_ProcurementTypePN=str_ProcurementTypePN +","+PN;
}
}
else//ProcurementType為空的時候
{
if (bomstr.IndexOf(PN,0) ==-1)
str_ProcurementTypePN=str_ProcurementTypePN +","+PN;
}
//else//如果不是內交料號,又沒有BOM,但維護的成品和半成品時,給出相應的提示
// Response.Write("<script language=javascript>alert('MaterialType of the MaterialNumber:"+PN+" is S or F ,it is need a bom!');</script>");
}
// }
return return_str;
}
public bool InterNalPlant(string MainPlant,string InterPlant)
{
bool Flag =false;
if (func.GetDataTable("select Flag from pubInterNalPlant where Plant ='"+MainPlant+"' and InternalPlant ='"+InterPlant+"' and Active ='A' ").Tables[0].Rows.Count> 0)
Flag =true;
if (func.GetDataTable("select Flag from pubInterNalPlant where Plant ='"+InterPlant+"' and InternalPlant ='"+MainPlant+"' and Active ='A' ").Tables[0].Rows.Count >0)
Flag =true;
return Flag;
}
private double getParentPNQty(string MainPN,string ParentPN,string CPlant,string DataType,string AlternativeBom)
{//得到上階料號在BOM中的用量
double Parent_Qty=1;
DataSet ds_P = func.GetDataTable("select * from setbomstructure where MainMaterialNumber ='"+MainPN+"' and materialnumber='"+ ParentPN +"' "+
"and plant='"+CPlant +"' and MainValidDate='"+ this.mainvaliddate+"' "+
"and datatype='"+ DataType +"' and AlternativeBom ='"+AlternativeBom+"' and Active ='A' and isnull(QRNo,'')='"+QRNo+"' and BomLocation ='"+BomLocation+"'");
if (ds_P.Tables[0].Rows.Count >0)
{//setbomStructure中BIQty表示料號的用量
Parent_Qty =Convert.ToDouble(ds_P.Tables[0].Rows[0]["BIQty"].ToString());
}
return Parent_Qty;
}
private string getParentPlant(string MainPN,string ParentPN,string MainPlant,string DataType,string AlternativeBom)
{//得到上階料號在BOM中的用量
string ParentPlant=this.MainPlant;
DataSet ds_P = func.GetDataTable("select * from setbomstructure where MainMaterialNumber ='"+MainPN+"' and materialnumber='"+ ParentPN +"' "+
"and plant='"+MainPlant +"' and MainValidDate='"+ this.mainvaliddate+"' "+
"and datatype='"+ DataType +"' and AlternativeBom ='"+AlternativeBom+"' and Active ='A' and isnull(QRNo,'')='"+QRNo+"' and BomLocation ='"+BomLocation+"'");
if (ds_P.Tables[0].Rows.Count >0)
{//setbomStructure中BIQty表示料號的用量
ParentPlant =ds_P.Tables[0].Rows[0]["InternalPlant"].ToString();
}
return ParentPlant;
}
//修改因為料號的修改而受到影響的BOM結構
public void ChangePN(string PN)
{
//找出已有QR的料號,所以用料號匹配,不用主料號
DataSet ds =func.GetDataTable("select QRNo,QRItemNo,MainMaterialNumber,Plant,MainValidDate,DataType,AlternativeBom from setbomstructure "+
" where MaterialNumber ='"+PN+"' and isnull(QRNO,'') <>'' group by QRNo,QRItemNo,MainMaterialNumber,Plant,MainValidDate,DataType,AlternativeBom");
DataTable dt =ds.Tables[0];
if (dt.Rows.Count >0)
{
for(int i=0;i<dt.Rows.Count;i++)
{
//重新展開所受影響的QR和QRItemNo的Bom
DataRow dr=dt.Rows[i];
SaveExplodBom(dr["DataType"].ToString(),String.Format("{0:yyyy-MM-dd}",dr["MainValidDate"]),dr["MainMaterialNumber"].ToString(),dr["Plant"].ToString(),dr["AlternativeBom"].ToString(),String.Format("{0:yyyy-MM-dd }",DateTime.Now),"Exploding","Y","Y","Y",1,dr["QRNo"].ToString(),Convert.ToInt32(dr["QRItemNo"].ToString()));
}
}
}
public void ChangeBom(string bomID,string PN,string Plant,string ValidDate,string DataType,string AlternativeBom)
{
//找出已有QR的料號,所以用料號匹配,不用主料號
DataSet ds =func.GetDataTable("select QRNo,QRItemNo,MainMaterialNumber,Plant,MainValidDate,DataType,AlternativeBom from setbomstructure "+
" where MaterialNumber ='"+PN+"' and Plant ='"+Plant+"' and isnull(QRNO,'') <>'' group by QRNo,QRItemNo,MainMaterialNumber,Plant,MainValidDate,DataType,AlternativeBom");
DataTable dt =ds.Tables[0];
if (dt.Rows.Count >0)
{
for(int i=0;i<dt.Rows.Count;i++)
{
//重新展開所受影響的QR和QRItemNo的Bom
DataRow dr=dt.Rows[i];
SaveExplodBom(DataType,ValidDate,dr["MainMaterialNumber"].ToString(),dr["Plant"].ToString(),AlternativeBom,String.Format("{0:yyyy-MM-dd }",DateTime.Now),"Exploding","Y","Y","Y",1,dr["QRNo"].ToString(),Convert.ToInt32(dr["QRItemNo"].ToString()));
}
}
}
public void ChangeBomItem(string PN,string Plant)
{
//找出所有受影響的料號所在的QRNo和QRItemNo,所以用料號匹配,不用主料號
DataSet ds =func.GetDataTable("select QRNo,QRItemNo,MainMaterialNumber,Plant,MainValidDate,DataType,AlternativeBom from setbomstructure where MaterialNumber ='"+PN+"' and Plant ='"+Plant+"' and isnull(QRNO,'') <>'' group by QRNo,QRItemNo,MainMaterialNumber,Plant,MainValidDate,DataType,AlternativeBom");
DataTable dt =ds.Tables[0];
if (dt.Rows.Count >0)
{
for(int i=0;i<dt.Rows.Count;i++)
{
DataRow dr =dt.Rows[i];
SaveExplodBom(dr["DataType"].ToString(),String.Format("{0:yyyy-MM-dd}",dr["MainValidDate"]),dr["MainMaterialNumber"].ToString(),dr["Plant"].ToString(),"1",String.Format("{0:yyyy-MM-dd }",DateTime.Now),"Exploding","Y","Y","Y",1,dr["QRNo"].ToString(),Convert.ToInt32(dr["QRItemNo"].ToString()));
}
}
}
#endregion
#region 公共函數
public static bool IsDBNull(object pValue)
{//判斷數據庫中的Null值
if (pValue==null || pValue==DBNull.Value)
return true;
else
return false;
}
public string ToDBData(string str)
{
//將沒有數值的數字型的欄位庫轉換成null保存
if(str.Trim()=="")
return "null";
return str.Trim();
}
/// <summary>
/// 將空字串轉換成NULL
/// 主要用在數值型欄位
/// </summary>
/// <param name="str"></param>
/// <returns></returns>
public string ConvertToDBData(string str)
{
if(str.Trim()=="")
{
return "NULL";
}
else
{
return str.Trim();
}
}
/// <summary>
/// 將空字串轉換成NULL
/// 并可將字符串的一個單引號用兩個來代替
/// 主要用在字符型欄位
/// </summary>
/// <param name="str">欲轉換的字符串</param>
/// <returns>轉換後的字符串</returns>
public string ConvertToDBStr(string str)
{
if ( (str.Trim()=="") || (str.Trim()=="N/A") )
{
return "NULL";
}
else
{
return "'"+str.Trim().Replace("'","''")+"'";
}
}
/// <summary>
/// Add by reaky 2006/6/17
/// 將已Approve的資料重新變成Open狀態
/// </summary>
/// <param name="dataname"></param>
/// <param name="dataid"></param>
public void changeApproveStatus(string dataname, string dataid)
{
string sql;
sql = "update datQuotationApproveItem set status='Open', ApproveTime=NULL where dataname='"+ dataname +"' and dataid="+ dataid ;
sql += " update datQuotationApproveHeader set status='Open' from datQuotationApproveItem as a where a.qrID = datQuotationApproveHeader.qrID and a.dataname='"+ dataname +"' and a.dataid="+ dataid;
if (func.ExecuteSQL(sql) == false)
{
func.ExecuteJS("alert('update approve status fail!');");
}
}
/// <summary>
/// Add by reaky 2006/6/17
/// 檢測必段欄位是否指定了值
/// 如果沒有則會給出警告
/// </summary>
/// <param name="field"></param>
/// <param name="values"></param>
public bool checkMustBe(string values, string field)
{
if (values.Trim() == "")
{
func.AlertMsg(field + " must assign value");
return false;
}
else
{
return true;
}
}
/// <summary>
/// 抓取對應代碼的詳細描述
/// 如抓取國別代碼對應的國家名等
/// </summary>
/// <param name="tablename">要抓取的表單名,一般為cqsParameter</param>
/// <param name="fieldname">要抓取的欄位名,一般為text</param>
/// <param name="condition">要抓取的條件,一般為category=??</param>
/// <returns></returns>
public string getDesc(string tablename, string fieldname, string condition)
{
string rs="";
DataSet ds;
ds = func.GetDataTable("select "+ fieldname +" from "+ tablename +" where "+ condition);
if (ds.Tables[0].Rows.Count>0)
{
rs = ds.Tables[0].Rows[0][fieldname].ToString();
}
return rs;
}
public string getIdent_Current(string tablename)
{
DataSet ds;
ds = func.GetDataTable("select ident_current('"+ tablename +"') as ID ");
if (ds.Tables[0].Rows.Count>0)
{
return ds.Tables[0].Rows[0]["ID"].ToString();
}
else
{
return "0";
}
}
/// <summary>
/// 調用後台函數獲取 DataType
/// </summary>
/// <param name="PN"></param>
/// <param name="Plant"></param>
/// <param name="QRNo"></param>
/// <param name="QrItemNo"></param>
/// <returns></returns>
public string getBOMdatatype(string PN, string Plant, string QRNo, string QrItemNo)
{//返回為 C_MainValidDate
getBomFirst(QRNo,Convert.ToInt32(QrItemNo));
DataSet ds;
//ds = func.GetDataTable("select dbo.fn_getBOMDataType("+ ConvertToDBStr(PN) +","+ ConvertToDBStr(Plant) +","+ ConvertToDBStr(QRNo) +","+ ConvertToDBStr(QrItemNo) +") as datatype");
if (QRNo != "" && QrItemNo != "")
{
ds = func.GetDataTable("select distinct * from setBomstructure where QRNo ='"+QRNo+"' and QRItemNo ='"+QrItemNo+"' ");
if (ds.Tables[0].Rows.Count >0)
{
DataRow dr =ds.Tables[0].Rows[0];
return "C_"+string.Format("{0:yyyy-MM-dd}",dr["MainValidDate"]);
}
else
{
ds = func.GetDataTable("select Max(ValidDate) as validDate from setBomHeader where MaterialNumber ='"+PN+"' and Plant ='"+Plant+"' and DataType ='C' and Active ='A' ");
if (ds.Tables[0].Rows.Count >0)
{
DataRow dr =ds.Tables[0].Rows[0];
return "C_"+string.Format("{0:yyyy-MM-dd}",dr["validDate"]);
}
}
}
else
{
ds = func.GetDataTable("select Max(ValidDate) as validDate from setBomHeader where MaterialNumber ='"+PN+"' and Plant ='"+Plant+"' and DataType ='C' and Active ='A' ");
if (ds.Tables[0].Rows.Count >0)
{
DataRow dr =ds.Tables[0].Rows[0];
return "C_"+string.Format("{0:yyyy-MM-dd}",dr["validDate"]);
}
}
return "C_9999/01/01";
}
public string getBOMdatatype_cost(string PN, string Plant, string QRNo, string QrItemNo)
{
DataSet ds;
getBomFirst(QRNo,Convert.ToInt32(QrItemNo));
//ds = func.GetDataTable("select dbo.fn_getBOMDataType_cost("+ ConvertToDBStr(PN) +","+ ConvertToDBStr(Plant) +","+ ConvertToDBStr(QRNo) +","+ ConvertToDBStr(QrItemNo) +") as datatype");
//return ds.Tables[0].Rows[0]["datatype"].ToString();
if (QRNo != "" && QrItemNo != "")
{
ds = func.GetDataTable("select distinct * from setBomstructure where QRNo ='"+QRNo+"' and QRItemNo ='"+QrItemNo+"' ");
if (ds.Tables[0].Rows.Count >0)
{
DataRow dr =ds.Tables[0].Rows[0];
return "C_"+string.Format("{0:yyyy-MM-dd}",dr["MainValidDate"]);
}
else
{
ds = func.GetDataTable("select Max(ValidDate) as validDate from setBomHeader where MaterialNumber ='"+PN+"' and Plant ='"+Plant+"' and DataType ='C' and Active ='A' ");
if (ds.Tables[0].Rows.Count >0)
{
DataRow dr =ds.Tables[0].Rows[0];
return "C_"+string.Format("{0:yyyy-MM-dd}",dr["validDate"]);
}
}
}
else
{
ds = func.GetDataTable("select Max(ValidDate) as validDate from setBomHeader where MaterialNumber ='"+PN+"' and Plant ='"+Plant+"' and DataType ='C' and Active ='A' ");
if (ds.Tables[0].Rows.Count >0)
{
DataRow dr =ds.Tables[0].Rows[0];
return "C_"+string.Format("{0:yyyy-MM-dd}",dr["validDate"]);
}
}
return "C_9999/01/01";
}
/// <summary>
/// 獲取對應QR單 bom,routing,inforecord的Approve狀況
/// 只有在全部都Approve時才可以報價否則不允許
/// </summary>
/// <param name="qhID"></param>
/// <returns></returns>
[Ajax.AjaxMethod]
public string getApproveStatus(string qhID)
{
string app="0";
DataSet ds, da;
ds = func.GetDataTable("select * from datQuotationHeader where qhID="+ qhID);
if (ds.Tables[0].Rows.Count >0)
{
da = func.GetDataTable("select dbo.fn_getApproveStatus('"+ ds.Tables[0].Rows[0]["QrNo"].ToString()+"',"+ ds.Tables[0].Rows[0]["QrItemNo"].ToString()+") as app ");
app = da.Tables[0].Rows[0]["app"].ToString();
}
return app;
}
/// <summary>
/// 獲取對應QR單 bom,routing,inforecord的Approve狀況
/// 只有在全部都Approve時才可以報價否則不允許
/// </summary>
/// <param name="qhID"></param>
/// <returns></returns>
[Ajax.AjaxMethod]
public string getApproveStatusDetail_cost(string CEID)
{
string app="0";
DataSet ds, da;
ds = func.GetDataTable("select * from datcostEstimateH where CEID="+ CEID);
if (ds.Tables[0].Rows.Count >0)
{
da = func.GetDataTable("select dbo.fn_getApproveStatusDetail('"+ ds.Tables[0].Rows[0]["QrNo"].ToString()+"',"+ ds.Tables[0].Rows[0]["QrItemNo"].ToString()+") as app ");
app = da.Tables[0].Rows[0]["app"].ToString();
}
return app;
}
[Ajax.AjaxMethod]
public string getApproveStatus_cost(string CEID)
{
string app="0";
DataSet ds, da;
ds = func.GetDataTable("select * from datcostEstimateH where CEID="+ CEID);
if (ds.Tables[0].Rows.Count >0)
{
da = func.GetDataTable("select dbo.fn_getApproveStatus('"+ ds.Tables[0].Rows[0]["QrNo"].ToString()+"',"+ ds.Tables[0].Rows[0]["QrItemNo"].ToString()+") as app ");
app = da.Tables[0].Rows[0]["app"].ToString();
}
return app;
}
public string getUserPlant(string uid)
{
return "select plantID from cqsUserPlant where uid="+ uid;
}
//檢查某頁的datagrid是否顯示已刪除的記錄
public bool setIsShowDel(string f_syscode)
{
DataSet ds;
string LoginID = Session["LoginID"].ToString();
ds = func.GetDataTable("select delflag from setDel where uID='"+LoginID+"' and delflag=1 and F_syscode='"+f_syscode+"'");
if (ds.Tables[0].Rows.Count>0)
{
return true;
}
else
{
return false;
}
}
//檢查某個報價單的子報價單的料號是否重復，如重復則在料號後面加上_1,_2
public string PNtitlestr(string qhid)
{
string PN="";
string QRNO="";
string QRItemNO="";
string QHNO="";
DataTable dt=func.GetDataTable("select qhno,qritemno,qrno,MaterialNumber from datquotationHeader where qhid="+qhid).Tables[0];
if(dt.Rows.Count>0)
{
QRNO=dt.Rows[0]["qrno"].ToString();
QRItemNO=dt.Rows[0]["qritemno"].ToString();
QHNO=dt.Rows[0]["qhno"].ToString();
PN=dt.Rows[0]["MaterialNumber"].ToString();
}
if(QHNO.Length>14)
{
string num=func.GetDataOne("select count(*) from datquotationHeader where qhno like '%"+PN+"%' and qrno='"+QRNO+"' and QRItemNO="+QRItemNO+" and qhid<"+qhid);
if(num!="0")
{
PN=PN+"_"+num;
}
}
return PN;
}
/// <summary>
/// 設置工具欄的按鈕的圖片為可用或不可用
/// </summary>
/// <param name="toolbar">工具欄</param>
/// <param name="Index">索引</param>
/// <param name="Enabled">true:可用 false:不可用</param>
public void setToolbarEnabled(Toolbar toolbar, int Index, bool Enabled)
{
ToolbarButton tbBtn = (ToolbarButton)toolbar.Items[Index];
tbBtn.Enabled = Enabled;
string imageUrl = tbBtn.ImageUrl.Trim();
string url = imageUrl.Substring(0,imageUrl.LastIndexOf("/"));
string fileName = imageUrl.Substring(imageUrl.LastIndexOf("/") + 1);
string fileNameBegin = fileName.Substring(0,fileName.LastIndexOf("."));
string fileNameEnd = fileName.Substring(fileName.LastIndexOf(".") + 1);
int point = fileNameBegin.LastIndexOf("_");
if(point>=0)
{
fileNameBegin = fileNameBegin.Substring(0,fileNameBegin.LastIndexOf("_"));
}
if(Enabled)
{
tbBtn.ImageUrl = url + "/" + fileNameBegin + "_t." + fileNameEnd;
}
else
{
tbBtn.ImageUrl = url + "/" + fileNameBegin + "_f." + fileNameEnd;
}
}
//根據料號找出英文描述
public string PNdescriptionstr(string PN)
{
string PNDescriptEN=func.GetDataOne("select DescriptionEN from pubmaterialmaster where materialnumber='"+PN+"'");
if(PNDescriptEN==null)
{
PNDescriptEN=PN;
}
return PNDescriptEN;
}
#endregion
#region getBomFirst,肖富劍,QRNO和QRItemNO 唯一确定一個BOM的條件,函數用來判斷如果QR沒有BOM,則展BOM
/// <summary>
/// 函數用途:
/// 1、在使用BOM的地方，如Approve，Check Data status
/// 2、QR查詢的地方
/// </summary>
/// <param name="QRNo">QRNo,string</param>
/// <param name="QRItemNo">QRItemNo,int</param>
public void getBomFirst(string QRNo,int QRItemNo)
{
//指定展BOM的時間,為QR的建立時間或者Release時間
DataSet ds_Date = func.GetDataTable("select MaterialNumber,Plant,case when releasedate is not null and releasedate>=QRHCreateOn then releasedate else QRHCreateOn end as assigndate from vdatQRItem "+
"where qrno='" +QRNo+"' and qritemno="+QRItemNo);
if (ds_Date.Tables[0].Rows.Count>0)
{
string AssignDate =String.Format("{0:yyyy-MM-dd}",ds_Date.Tables[0].Rows[0]["assigndate"]);
string MainMaterialNumber =ds_Date.Tables[0].Rows[0]["MaterialNumber"].ToString();
string MainPlant =ds_Date.Tables[0].Rows[0]["Plant"].ToString();
//從BOMHeader中讀取小于指定時間的最大的一筆資料
DataSet ds_bom =func.GetDataTable("select Max(ValidDate) as MainValidDate from setbomHeader where MaterialNumber ='"+MainMaterialNumber+"' and Plant ='"+MainPlant+"' "+
"and DataType ='C' and AlternativeBom =1 and ValidDate <= '"+AssignDate+"' and Active ='A' ");
DataRow dr =ds_bom.Tables[0].Rows[0];
if (ds_bom.Tables[0].Rows.Count >0)
{
string DataType ="C";
double BomQty =1;//指定Top料號的展Bom的用量,默認為1
string MainValidDate=String.Format("{0:yyyy-MM-dd}",dr["MainValidDate"]);
string Location ="Exploding";
string AlternativeBom="1";
//如果QRNO沒有BOM,則重新展BOM
if (func.GetDataTable("select * from setbomstructure where QRNo ='"+QRNo+"' and QRItemNo='" +QRItemNo+"' ").Tables[0].Rows.Count <=0)
SaveExplodBom(DataType,MainValidDate,MainMaterialNumber,MainPlant,AlternativeBom,AssignDate,Location,"Y","Y","Y",BomQty,QRNo,QRItemNo);
}
else
{
func.AlertMsg("PN:" + MainMaterialNumber +" has not any Active BOM in "+MainPlant+", please set BOM first!");
return;
}
}
else
{
func.AlertMsg("QR:" + QRNo +" and "+QRItemNo+" found fail,Plesse check it!");
return;
}
}
#endregion
#region UpdateOrDeletePN,肖富劍,判斷能否對料號進行修改和刪除
/// <summary>
/// 根據PN和Plant 刪除 setBomHeader,setBomItem或者pubMaterialMaster中的記錄
/// 返回值為True 時,說明資料存在,不能刪除
/// </summary>
/// <param name="PN"></param>
/// <param name="Plant"></param>
/// <returns></returns>
public bool UpdateOrDeletePN(string PN,string Plant)
{//找出要編輯或刪除的料號
//如果QR被Close了,則不能刪除
DataSet ds =func.GetDataTable("select * from vdatQRItem where MaterialNumber ='"+PN+"' and Plant ='"+Plant+"' and isnull(CloseDate,'') !='' and Active ='A' ");
if (ds.Tables[0].Rows.Count >0)
return true;
else
return false;
}
/// <summary>
/// 根據ID刪除 setBomHeader,setBomItem或者pubMaterialMaster中的記錄
/// 返回值為True 時,說明資料存在,不能刪除
/// </summary>
/// <param name="ID">bomID或者BomItem中的ID</param>
/// <param name="TableName">setBomHeader,setBomItem或者pubMaterialMaster</param>
public bool UpdateOrDeletePN(int ID,string TableName)
{//找出要編輯或刪除的料號
string sqlstr;
DataSet ds,ds_PN;
DataRow dr_PN;
if (TableName.ToLower() =="setbomheader")
sqlstr ="select * from setBomHeader where bomID ="+ID+" and Active ='A' ";
else if (TableName.ToLower() =="setbomitem")
sqlstr ="select * from setBomItem where ID ="+ID+" and Active ='A' ";
else
{
sqlstr ="select * from pubMaterialMaster where pnID ="+ID+" and Active ='A' ";
ds_PN =func.GetDataTable(sqlstr);
if (ds_PN.Tables[0].Rows.Count>0)
{
dr_PN =ds_PN.Tables[0].Rows[0];
//如果QR被Close了,則不能刪除
ds =func.GetDataTable("select * from vdatQRItem where MaterialNumber ='"+dr_PN["MaterialNumber"].ToString()+"' and isnull(CloseDate,'') !='' and Active ='A' ");
if (ds.Tables[0].Rows.Count >0)
return true;
else
return false;
}
else
return false;
}
ds_PN =func.GetDataTable(sqlstr);
if (ds_PN.Tables[0].Rows.Count>0)
{
dr_PN =ds_PN.Tables[0].Rows[0];
//如果QR被Close了,則不能刪除
ds =func.GetDataTable("select * from vdatQRItem where MaterialNumber ='"+dr_PN["MaterialNumber"].ToString()+"' and Plant ='"+dr_PN["Plant"].ToString()+"' and isnull(CloseDate,'') <>'' and Active ='A' ");
if (ds.Tables[0].Rows.Count >0)
return true;
else
return false;
}
else
return false;
}
#endregion
#region DeleteBom,肖富劍,刪除Bom的資料,在料號資料修改之後,但不重展,因為會在getBomFirst中重新展Bom
public void DeleteBom(string PN,string Plant,string MainValidDate,string DataType)
{
//找出所有受影響的料號,所以用料號匹配,不用主料號
DataSet ds =func.GetDataTable("select MainMaterialNumber,Plant,MainValidDate,DataType from setbomstructure "+
"where MaterialNumber ='"+PN+"' and Plant ='"+Plant+"' and MainValidDate ='"+ MainValidDate+"' and "+
"DataType ='"+DataType+"' group by MainMaterialNumber,Plant,MainValidDate,DataType");
DataTable dt =ds.Tables[0];
if (dt.Rows.Count >0)
{
for(int i=0;i<dt.Rows.Count;i++)
{
//重新執行BOM的展開,把 BomLocation ='Exploding' 全部記錄刪除
if (func.ExecuteSQL("delete from setBomStructure where isnull(QRNo,'') <>'' and MainMaterialNumber ='"+dt.Rows[i]["MainMaterialNumber"].ToString()+"' and Plant='" + dt.Rows[i]["Plant"].ToString() +"' "+
"and DataType='"+ dt.Rows[i]["DataType"].ToString()+"' and MainValidDate ='"+string.Format("{0:yyyy-MM-dd}",dt.Rows[i]["MainValidDate"])+"' and BomLocation ='Exploding' ") ==false)
{
Response.Write("<script language=javascript>alert('Delete the old BOM of "+dt.Rows[i]["MainMaterialNumber"].ToString()+" error');</script>");
return;
}
}
}
// else
// Response.Write("<script language=javascript>alert('Can find the updated BOM,Update BOM fail!');</script>");
}
#endregion
#region QRReleaseFirst,肖富劍,在Approve項目之前,先确定QR是否有被 Release
public string QRReleaseFirst(string QRNo,string QRItemNo)
{
//DataSet ds_Date = func.GetDataTable("select releasedate as assigndate from vdatQRItem where qrno='" + QRNo +"' and releasedate <>'' and qritemno="+ QRItemNo);
//DataSet ds_Date = func.GetDataTable("select assigndate from vdatQuotationApprove where qrno='" + QRNo +"' and assigndate <>'' and qritemno="+ QRItemNo);
DataSet ds_Date = func.GetDataTable("select releasedate from datQRHeader where qrno='" + QRNo +"' and releasedate <>'' ");
string releasedate="";
if (ds_Date.Tables[0].Rows.Count>0)
{
releasedate =String.Format("{0:yyyy-MM-dd}",ds_Date.Tables[0].Rows[0]["releasedate"]);
}
else
{
func.AlertMsg("QRNO :" + QRNo +" has not realesed, please Realse QR first!");
}
return releasedate;
}
#endregion
#region 基本資料變動時，給設定的相關用戶發送email,王小伙
public int sendMailApproved(string dataname,int dataid,string loginid,string data)
{
return sendMailApproved(dataname,dataid,loginid,data);
}
#endregion
#region 肖富劍,給DataGrid 加顏色區分記錄
/// <summary>
/// DataGrid某一欄位的值用間隔色來區分顯示
/// </summary>
/// <param name="dg">DataGrid的名稱</param>
/// <param name="Col">比較的欄位，為整型</param>
public void SetDataGridColor(System.Web.UI.WebControls.DataGrid dg, params int[] Col)
{
int cc=dg.Items.Count;
//bool flg =true;
//DataGrid中第一行的底色
System.Drawing.Color changeColor =new System.Drawing.Color();
System.Drawing.Color FirstColor =new System.Drawing.Color();
//FirstColor =System.Drawing.Color.LightBlue;
FirstColor =System.Drawing.Color.Azure;
System.Drawing.Color SecondColor =new System.Drawing.Color();
SecondColor =System.Drawing.Color.White;
int i=1;
if (cc >0)
dg.Items[i-1].BackColor =FirstColor;
// if (dg.ItemStyle ==dg.AlternatingItemStyle )
// dg.Items[1].BackColor = Color.Black;
int match = 0;
while(i<cc)
{
//判斷是否所有指定欄位的值都相同
foreach (int tmp in Col){
//為了防止傳的欄位index超出實際欄位數,要做一下判斷,超出的就忽略不理它
if (tmp < cc) {
if (dg.Items[i].Cells[tmp].Text != dg.Items[i-1].Cells[tmp].Text){
match = 0;
break;
}
}
match = 1;
}
//如果指定的欄位全部都匹配則當前行與前一行顏色相同,否則改變顏色
if (match == 1)
{
dg.Items[i].BackColor = dg.Items[i-1].BackColor;
}
else
{
dg.Items[i].BackColor =changeColor;
}
if (dg.Items[i].BackColor==FirstColor)
changeColor=SecondColor;
else
changeColor=FirstColor;
i =i+1;
}
}
#endregion
/// <summary>
/// 下載數據到client端
/// </summary>
/// <param name="response"></param>
/// <param name="path"></param>
/// <param name="realFilename"></param>
/// <param name="VirtualName"></param>
public static void DownLoadToClient(System.Web.HttpResponse response, string filepath, string VirtualName) {
// if(realFilename.Trim()=="") {
// response.Write("<script>alert('Invalid filename!');</script>");
// return;
// }
//
try {
if(File.Exists(filepath)) {
FileInfo fi=new FileInfo(filepath);
response.Clear();
response.ClearHeaders();
response.Buffer=false;
response.AppendHeader("Content-Disposition","attachment;filename="+System.Web.HttpUtility.UrlEncode(System.Text.Encoding.UTF8.GetBytes(VirtualName)));
response.AppendHeader("Content-Length",fi.Length.ToString());
response.ContentType="application/octet-stream";
response.WriteFile(filepath);
response.Flush();
response.End();
}
}
catch(Exception){}
}
}
}
