有用的函數

單位轉換函數
ALTER FUNCTION [dbo].[GetSrcToDestUnitRate]
(
 -- Add the parameters for the function here
 @src varchar(10), --轉換前的單位
 @dest varchar(10) --轉換後的單位
)
RETURNS float
AS
BEGIN
 declare @srcRate float, @destRate float, @rate float
 select @srcRate=0, @destRate=0, @rate=0
 if @src=@dest
 begin
  set @rate=1
 end else
 begin
  select @srcRate = isnull(Rate,0) from vpubUnitEx where Unit=@src
  select @destRate = isnull(Rate,0) from vpubUnitEx where Unit=@dest
  set @rate= case when @destRate=0 or @srcRate=0 then 1 else @srcRate / @destRate end
 end
 RETURN @rate
END


樹型結構
  public void InitTree(TreeNodeCollection Nds,int BOMID, string Plant, string DataType,string bomstr)
  {
   //parentID 為0時,為BOM的第一層
   //BOM主檔資料刪除的不在Header中顯示,點Header時,Item檔中的料號在料號主檔中刪除了的,不能在BOM樹中展開.
   //   DataSet ds_item =func.GetDataTable("select * from setBOMItem a left join pubMaterialMaster b on a.Component =b.MaterialNumber  where BOMID =" + BOMID + " and a.Active ='A' and b.Active ='A' "+func.GetPlantStr(Session["uID"].ToString(),"Plant","View"));
   DataSet ds_item =func.GetDataTable(getbomItem(BOMID.ToString(),String.Format("{0:yyyy-MM-dd }",DateTime.Now)));
   if (ds_item.Tables[0].Rows.Count ==0)
   {
    return;
   }
   TreeNode tmpNd;
   for(int k=0;k < ds_item.Tables[0].Rows.Count;k++)
   {
    tmpNd =new TreeNode();
    tmpNd.ID =ds_item.Tables[0].Rows[k]["ID"].ToString();
    tmpNd.Text =ds_item.Tables[0].Rows[k]["Component"].ToString();
    tmpNd.Expanded =true;
    
    Nds.Add(tmpNd);
    j=j+1;
    //InitTree_Internal(tmpNd.Nodes,
    DataSet ds;
    DataRow dr =ds_item.Tables[0].Rows[k];
    ds=func.GetDataTable("select bomID from setBOMHeader where MaterialNumber='"+dr["Component"].ToString()+"' and Plant='"+ Plant+"' "+
     "and DataType='"+ DataType +"' and AlterNativeBom =1 and Active='A' "+
     "and validdate=(select max(validdate) from setBOMHeader where MaterialNumber='"+dr["Component"].ToString()+"' "+
     "and Plant='"+ Plant+"' and DataType='"+ DataType +"' and ValidDate <='"+String.Format("{0:yyyy-MM-dd }",DateTime.Now)+"' and AlterNativeBom=1 and Active='A') ");
    if (ds.Tables[0].Rows.Count>0)
    {
     //如果內交料號有BOM時，以內交BOM為主
     string Internal_PN =func.GetDataOne("select ProcurementType from pubMaterialParameter where MaterialNumber ='"+dr["Component"].ToString()+"' and Plant ='"+Plant+"' and Active ='A' ");
     if (Internal_PN =="N")
     {
      string Procurement =getProcurementType(dr["Component"].ToString(),Plant,DataType,"1","Y");
      if (Procurement !="")
      {
       string[] arr =Procurement.Split('_');
       string InternalbomID =arr[0].ToString();
       string Internal_MainPlant =arr[1].ToString();
       if (bomstr.IndexOf(tmpNd.Text,0)==-1)
       {
        bomstr =bomstr +"_"+tmpNd.Text;
        InitTree_Internal(tmpNd.Nodes,Convert.ToInt32(InternalbomID),Internal_MainPlant, DataType,bomstr);
       }
      }
     }
     else if (Internal_PN =="F")//PorcurementType =F,但又有BOM,這時不展BOM
     {
     }
     else
     {
      if (bomstr.IndexOf(tmpNd.Text,0)==-1)
      {
       bomstr =bomstr +"_"+tmpNd.Text;
       InitTree(tmpNd.Nodes,(int)(ds.Tables[0].Rows[0]["bomID"]),Plant, DataType,bomstr);
      }
     }
    }
    else
    {
     string Procurement =getProcurementType(dr["Component"].ToString(),Plant,DataType,"1","Y");
     if (Procurement !="")
     {
      string[] arr =Procurement.Split('_');
      string InternalbomID =arr[0].ToString();
      string Internal_MainPlant =arr[1].ToString();
      if (bomstr.IndexOf(tmpNd.Text,0)==-1)
      {
       bomstr =bomstr +"_"+tmpNd.Text;
       InitTree_Internal(tmpNd.Nodes,Convert.ToInt32(InternalbomID),Internal_MainPlant, DataType,bomstr);
      }
     }

    }
   }
   
  }