/// <summary>
/// 搜索庫存物料
/// </summary>
/// <param name="itemEntity"></param>
/// <returns></returns>
public List<MaterialProfile_mlpf_Info> SearchRecords_Inventory(ERPMaterialFilter itemEntity)
{
try
{
return _GetERPMaterialSQL.SearchRecords_Inventory(itemEntity);
}
catch (Exception Ex)
{
throw Ex;
}
}
/// <summary>
/// 庫存物料搜索
/// </summary>
/// <param name="p_objFilter"></param>
/// <returns></returns>
public List<MaterialProfile_mlpf_Info> MaterialInventorySearch(ERPMaterialFilter p_objFilter)
{
using (ICPDBDataContext db = new ICPDBDataContext())
{
var infos = db.ExecuteQuery<MaterialProfile_mlpf_Info>(GetInventoryMaterialSQL(p_objFilter), new object[] { });
if (infos != null)
{
return infos.ToList<MaterialProfile_mlpf_Info>();
}
}
return null;
}
public List<MaterialProfile_mlpf_Info> SearchRecords_Job(ERPMaterialFilter JobFilter)
{
return _ERPMaterialBll.SearchRecords_Job(JobFilter);
}
public List<MaterialProfile_mlpf_Info> SearchRecords_Inventory(ERPMaterialFilter InventoryFilter)
{
return _ERPMaterialBll.SearchRecords_Inventory(InventoryFilter);
}
string GetWhereSql_Alloc(ERPMaterialFilter p_objFilter, bool p_blnKCFD, string p_strActualOrAlloc, string brandColumn)
{
StringBuilder l_strSQL = new StringBuilder();
if (p_strActualOrAlloc.ToUpper() == "R")
{
l_strSQL.AppendLine("and ict_cType='R' and ict_cStat<>'C'");
l_strSQL.AppendLine("and b.cod_cKey2='A'");
l_strSQL.AppendLine("and b.cod_cValue in ('A','R','V','S')");
l_strSQL.AppendLine("and itd_lAllowPick=1");
l_strSQL.AppendLine("and (rfr_cStatus='201' or rfr_cStatus is null)");
l_strSQL.AppendLine("and ict_dAddDt>convert(datetime,'2007-09-20 12:45:00',120)");
}
if (p_objFilter.SoNo != "")
{
if (!p_blnKCFD)
{
l_strSQL.AppendLine("and ict_cJob = '" + p_objFilter.SoNo + "'");
}
else
{
l_strSQL.AppendLine("and soh_cSoNO = '" + p_objFilter.SoNo + "'");
}
}
if (p_objFilter.RFNo != "")
{
l_strSQL.AppendLine("and ict_cRf like '%" + p_objFilter.RFNo + "%'");
}
if (p_objFilter.ItemDesc != "")
{
l_strSQL.AppendLine("and itm_cDesc2 like N'%" + p_objFilter.ItemDesc + "%'");
}
if (p_objFilter.ItemID != "")
{
l_strSQL.AppendLine("and itm_cItem like '%" + p_objFilter.ItemID + "%'");
}
if (p_objFilter.ProductName != "")
{
l_strSQL.AppendLine("and ict_cProdDesc like N'%" + p_objFilter.ProductName + "%'");
}
if (p_objFilter.TrxNo > 0)
{
l_strSQL.AppendLine("and ict_ftrxno=" + p_objFilter.TrxNo.ToString());
}
//領料單狀態
if (p_objFilter.TrxStatus != "")
{
if (p_objFilter.TrxStatus == "H")
{
l_strSQL.AppendLine("and isnull(iplm_cRemark,'')<>''");
}
else if (p_objFilter.TrxStatus == "Y")
{
l_strSQL.AppendLine("and isnull(iplm_cStat,'Y')='" + p_objFilter.TrxStatus + "' and isnull(iplm_cRemark,'')=''");
}
else
{
l_strSQL.AppendLine("and iplm_cStat='" + p_objFilter.TrxStatus + "' and iplm_cRemark=''");
}
}
if (p_objFilter.Type != "")
{
l_strSQL.AppendLine("and itm_cType='" + p_objFilter.Type + "'");
}
if (p_objFilter.Brand != "")
{
l_strSQL.AppendLine("and " + brandColumn + " Like '%" + p_objFilter.Brand + "%'");
}
return l_strSQL.ToString();
}
/// <summary>
/// 跟Job物料搜索SQL
/// </summary>
/// <param name="p_objFilter"></param>
/// <returns></returns>
string GetJobMaterialSQL(ERPMaterialFilter p_objFilter)
{
string brandColumn = GetBrandColumn(p_objFilter.Cat, p_objFilter.Sub);
StringBuilder l_strSQL = new StringBuilder();
if (p_objFilter.IsUnHandle)
{
l_strSQL.AppendLine(GetSelectSql_NorAlloc(brandColumn));
l_strSQL.AppendLine(GetWhereSql_Alloc(p_objFilter, false, "R", brandColumn));
l_strSQL.AppendLine("Union");
l_strSQL.AppendLine(GetSelectSql_FDAlloc(brandColumn));
l_strSQL.AppendLine(GetWhereSql_Alloc(p_objFilter, true, "R", brandColumn));
}
else
{
l_strSQL.AppendLine(GetUnionSelectSql_PickList("R", brandColumn));
l_strSQL.AppendLine(GetWhereSql_Alloc(p_objFilter, false, "R", brandColumn));
l_strSQL.AppendLine("Union");
l_strSQL.AppendLine(GetUnionSelectSql_PickListFD("R", brandColumn));
l_strSQL.AppendLine(GetWhereSql_Alloc(p_objFilter, true, "R", brandColumn));
l_strSQL.AppendLine("Union");
l_strSQL.AppendLine(GetUnionSelectSql_PickList("A", brandColumn));
l_strSQL.AppendLine(GetWhereSql_Alloc(p_objFilter, false, "A", brandColumn));
l_strSQL.AppendLine("Union");
l_strSQL.AppendLine(GetUnionSelectSql_PickListFD("A", brandColumn));
l_strSQL.AppendLine(GetWhereSql_Alloc(p_objFilter, true, "A", brandColumn));
}
return l_strSQL.ToString();
}
/// <summary>
/// 取得庫存系統物料SQL
/// </summary>
/// <param name="p_objFilter"></param>
/// <returns></returns>
string GetInventoryMaterialSQL(ERPMaterialFilter p_objFilter)
{
StringBuilder l_strSQL = new StringBuilder();
StringBuilder l_strWhere = new StringBuilder();
#region Condition
l_strWhere.AppendLine("Where 1=1");
if (p_objFilter.Type != "" && p_objFilter.Type != "-1")
{
l_strWhere.AppendLine("And a.itm_ctype=N'" + p_objFilter.Type + "' ");
}
if (p_objFilter.Cat != "")
{
l_strWhere.AppendLine("And a.itm_ccat=N'" + p_objFilter.Cat + "' ");
}
if (p_objFilter.Sub != "")
{
l_strWhere.AppendLine("And a.itm_csub1=N'" + p_objFilter.Sub + "'");
}
if (p_objFilter.ItemID != "")
{
l_strWhere.AppendLine("And a.itm_citem Like '%" + p_objFilter.ItemID + "%'");
}
if (p_objFilter.ItemDesc != "")
{
l_strWhere.AppendLine("And a.itm_cdesc2 Like N'%" + p_objFilter.ItemDesc + "%'");
}
if (p_objFilter.ISC != "")
{
l_strWhere.AppendLine("And a.itm_cicsit Like N'%" + p_objFilter.ISC + "%'");
}
if (p_objFilter.SpecDesc != "")
{
l_strWhere.AppendLine("And (a.itm_cCSpExtra Like N'%" + p_objFilter.SpecDesc + "%' Or a.itm_cESpExtra Like N'%" + p_objFilter.SpecDesc + "%')");
}
if (p_objFilter.MoNo != "")
{
l_strWhere.AppendLine("And c.jobno=N'" + p_objFilter.MoNo + "'");
}
if (p_objFilter.PoNo != "")
{
l_strWhere.AppendLine("And j.icd_cPO=N'" + p_objFilter.PoNo + "' ");
}
if (p_objFilter.SuppNo != "")
{
l_strWhere.AppendLine("And c.suppNo=N'" + p_objFilter.SuppNo + "'");
}
if (p_objFilter.CustomerCode != "")
{
l_strWhere.AppendLine("And e.Custcode=N'" + p_objFilter.CustomerCode + "' ");
}
//if (p_objFilter.Brand != "")
if (p_objFilter.Cat.Trim() != "" && p_objFilter.Sub.Trim() != "" && p_objFilter.Brand != "")
{
string brandColumn = GetBrandColumn(p_objFilter.Cat, p_objFilter.Sub);
if (brandColumn.Trim() != "")
l_strWhere.AppendLine("And a." + brandColumn + " Like N'%" + p_objFilter.Brand + "%'");
//l_strWhere.AppendLine("And " + brandColumn + "=N'" + p_objFilter.Brand + "' ");
}
#endregion Condition
#region MyRegion
l_strSQL.AppendLine("select DISTINCT top 100 a.itm_citem as 'mlpf_cMaterialCode'");
l_strSQL.AppendLine(",a.itm_ctype as 'mlpf_cMaterialType'");
l_strSQL.AppendLine(",a.itm_ccat as 'ItemCat'");
l_strSQL.AppendLine(",a.itm_csub1 as 'ItemSub'");
l_strSQL.AppendLine(",IsNull(j.icd_cPO,'') as 'PONO'");
l_strSQL.AppendLine(",IsNull(K.slmr_cSuppLotNo,'') as 'BatchNO'");
l_strSQL.AppendLine(",a.itm_cDesc2 As 'mlpf_cMaterialName'");
l_strSQL.AppendLine(",a.itm_cdesc As 'ItemEngDesc'");
l_strSQL.AppendLine(",a.itm_cum as 'mlpf_cMaterialUnit'");
//l_strSQL.AppendLine(",a.itm_cicsit as 'ISCNo',a.itm_fmoq as 'MoqRam'");
//l_strSQL.AppendLine(",a.itm_cdept as 'Dept'");
l_strSQL.AppendLine(",IsNull(um.cod_crmk,'') As 'cMaterialUnitDesc'");
l_strSQL.AppendLine(",IsNull(t.cod_crmk,'') As 'mlpf_cMaterialType'");
l_strSQL.AppendLine(",IsNull(cat.cat_cchn,'') As 'CatDesc'");
l_strSQL.AppendLine(",IsNull(sub.cat_cchn,'') As 'SubDesc'");
l_strSQL.AppendLine(",itm_cItemShortName As mlpf_cMaterialAbbreviation");
//l_strSQL.AppendLine(",'' As Loc");
//l_strSQL.AppendLine(",a.itm_cBrand As Brand");
l_strSQL.AppendLine(",'' As mlpf_cDescription");
l_strSQL.AppendLine(",convert(bit,'0') As mlpf_lIsDeleted");
l_strSQL.AppendLine(",Getdate() As mlpf_dUpdateTime");
//l_strSQL.AppendLine(",IsNull(itm_cCSp2,'') As BrandDesc");
l_strSQL.AppendLine(",Case When sub.cat_cesp1='BRAND' Then itm_cCSp1 ");
l_strSQL.AppendLine("When sub.cat_cesp2='BRAND' Then itm_cCSp2");
l_strSQL.AppendLine("When sub.cat_cesp3='BRAND' Then itm_cCSp3");
l_strSQL.AppendLine("When sub.cat_cesp4='BRAND' Then itm_cCSp4");
l_strSQL.AppendLine("When sub.cat_cesp5='BRAND' Then itm_cCSp5");
l_strSQL.AppendLine("When sub.cat_cesp6='BRAND' Then itm_cCSp6");
l_strSQL.AppendLine("When sub.cat_cesp7='BRAND' Then itm_cCSp7");
l_strSQL.AppendLine("When sub.cat_cesp8='BRAND' Then itm_cCSp8");
l_strSQL.AppendLine("When sub.cat_cesp9='BRAND' Then itm_cCSp9");
l_strSQL.AppendLine("Else itm_cCSp10 End As mlpf_cMaterialBrand");
l_strSQL.AppendLine(",NEWID() As mlpf_MLPFID");
l_strSQL.AppendLine(",0 As MachineType");
l_strSQL.AppendLine(",0 As TrxNo");
l_strSQL.AppendLine(",'' As SONO");
l_strSQL.AppendLine(",'' As RFNO");
l_strSQL.AppendLine(",'' As PagerUsage");
l_strSQL.AppendLine(",IsNull(itm_fNum1,Cast(0 As Decimal(8,4))) As mlpf_fMaterialWidth");
l_strSQL.AppendLine(",IsNull(itm_fNum2,Cast(0 As Decimal(8,4))) As mlpf_fMaterialLength");
l_strSQL.AppendLine(",IsNull(itm_cUM1,'') As WidthUnit");
l_strSQL.AppendLine(",IsNull(itm_cUM2,'') As LengthUnit");
l_strSQL.AppendLine("from icpdb.dbo.icpitm As a With(nolock)");
l_strSQL.AppendLine("Left Join icpdb.dbo.icpcod As t With(nolock) On t.cod_ckey1 = 'itemtype' And t.cod_cvalue=a.itm_ctype");
l_strSQL.AppendLine("Left Join icpdb.dbo.icpcod As um With(nolock) On um.cod_ckey1='UM' And um.cod_cvalue=a.itm_cum");
l_strSQL.AppendLine("Left Join icpdb.dbo.icpcat As cat With(nolock) On cat.cat_clevel=0 And cat.cat_ccat=a.itm_ccat");
l_strSQL.AppendLine("Left Join icpdb.dbo.icpcat As sub With(nolock) On sub.cat_cLevel=1 And sub.cat_cSub1=a.itm_csub1 And sub.cat_ccat=a.itm_ccat");
l_strSQL.AppendLine("Left Join icpdb.dbo.icpBnd As bnd With(nolock) On bnd.bnd_cBrand=a.itm_cBrand");
if (p_objFilter.PoNo != "" || p_objFilter.MoNo != "" || p_objFilter.CustomerCode != "" || p_objFilter.SuppNo != "")
{
l_strSQL.AppendLine("left join newICS.dbo.POline b With(nolock) on b.itemNo=a.itm_cicsit ");
if (p_objFilter.MoNo != "" || p_objFilter.CustomerCode != "" || p_objFilter.SuppNo != "")
{
l_strSQL.AppendLine("left join newICS.dbo.Purch_order c With(nolock) on c.poNO=b.PoNO ");
}
if (p_objFilter.CustomerCode != "")
{
l_strSQL.AppendLine("left join icpdb.dbo.lppJob d With(nolock) on d.jobNo=c.jobNo ");
l_strSQL.AppendLine("left join icpdb.dbo.Customer e With(nolock) on e.custcode=d.customer ");
}
}
l_strSQL.AppendLine("left join icpdb.dbo.icpicd j With(nolock) on a.itm_cItem=j.icd_cItem");
l_strSQL.AppendLine("left join icpdb.dbo.SupplyLotMstr_slmr K With(nolock) on K.slmr_citem = j.icd_citem and K.slmr_cloc = j.icd_cloc and K.slmr_clot = j.icd_clot");
//if(true)
//{
//l_strWhere.AppendLine("And a.itm_cStat='" & m_strWareState & "' ");
//}
l_strSQL.AppendLine(l_strWhere.ToString());
l_strSQL.AppendLine(p_objFilter.Type != "P" ? "And a.itm_cpaper='A' order by itm_citem " : "");
return l_strSQL.ToString();
#endregion
}
public List<MaterialProfile_mlpf_Info> SearchRecords_Job(ERPMaterialFilter searchCondition)
{
return MaterialJobSearch(searchCondition);
}
public List<MaterialProfile_mlpf_Info> SearchRecords_Inventory(ERPMaterialFilter searchCondition)
{
return MaterialInventorySearch(searchCondition);
}
/// <summary>
/// 跟Job物料搜索
/// </summary>
/// <param name="p_objFilter"></param>
/// <returns></returns>
public List<MaterialProfile_mlpf_Info> MaterialJobSearch(ERPMaterialFilter p_objFilter)
{
//IList<IDomainObject> retValue = new List<IDomainObject>();
//Database db = DatabaseFactory.CreateDatabase("ICPDB");
//DbCommand dbCommand = db.GetSqlStringCommand(GetJobMaterialSQL(p_objFilter));
//MaterialProfile item;
//using (IDataReader dataReader = db.ExecuteReader(dbCommand))
//{
// while (dataReader.Read())
// {
// item = new MaterialProfile();
// GetERPItemData(dataReader, item);
// //GetGroupItemData(dataReader, item);
// retValue.Add(item);
// }
//}
//return retValue;
using (ICPDBDataContext db = new ICPDBDataContext())
{
var infos = db.ExecuteQuery<MaterialProfile_mlpf_Info>(GetJobMaterialSQL(p_objFilter), new object[] { });
if (infos != null)
{
return infos.ToList<MaterialProfile_mlpf_Info>();
}
}
return null;
}