public bool GetCustDwg(CostSheetSearchModel costsheetModel)
{
try
{
DataTable dt = new DataTable();
dt = ToDataTable((from o in DB.DDCI_INFO
where ((Convert.ToBoolean(Convert.ToInt16(o.DELETE_FLAG)) == false) || (o.DELETE_FLAG == null)) && (o.CUST_DWG_NO != null && o.CUST_DWG_NO != "")
select new { o.CUST_DWG_NO }).ToList());
if (dt != null)
{
costsheetModel.CustDwgNo = dt.DefaultView;
}
else
{
costsheetModel.CustDwgNo = null;
}
return(true);
}
catch (Exception ex)
{
throw ex.LogException();
}
}
public DataView GetCostSheetSearchDetails(CostSheetSearchModel costsheetsearch)
{
//ddci_info i,ddcust_mast c,prd_ciref
//select ci_reference as CIReference,prod_desc as ProductDesc,cust_name as CustomerName,cust_dwg_no as CustDwgNo,part_no as PartNo,responsibility as Resp, loc_code as Location
//from ddci_info i,ddcust_mast c,prd_ciref p
//where i.ci_reference=p.ci_ref (+) and i.cust_code = c.cust_code (+) and part_no ='W01330'
//and prod_desc like '%ASDF%' and cust_name like '%ASDF%' and cust_dwg_no like '%ASDF%'
//and pending=1 and ci_reference not like 'O%' and ci_reference not like 'U%' and ci_reference not like 'X%' and allot_part_no=1 order by ci_reference
//1
//select ci_reference as CIReference,prod_desc as ProductDesc,cust_name as CustomerName,cust_dwg_no as CustDwgNo,part_no as PartNo,responsibility as Resp, loc_code as Location from ddci_info i,ddcust_mast c,prd_ciref p where i.ci_reference=p.ci_ref (+) and i.cust_code = c.cust_code (+) and pending=0 order by ci_reference
//select ci_reference as CIReference,prod_desc as ProductDesc,cust_name as CustomerName,cust_dwg_no as CustDwgNo,part_no as PartNo,responsibility as Resp, loc_code as Location from ddci_info i,ddcust_mast c,prd_ciref p where i.ci_reference=p.ci_ref (+) and i.cust_code = c.cust_code (+) and pending=0 and ci_reference not like 'O%' and ci_reference not like 'U%' and ci_reference not like 'X%' order by ci_reference
//select ci_reference as CIReference,prod_desc as ProductDesc,cust_name as CustomerName,cust_dwg_no as CustDwgNo,part_no as PartNo,responsibility as Resp, loc_code as Location from ddci_info i,ddcust_mast c,prd_ciref p where i.ci_reference=p.ci_ref (+) and i.cust_code = c.cust_code (+) and pending=0 and ci_reference like 'X%' order by ci_reference
//select ci_reference as CIReference,prod_desc as ProductDesc,cust_name as CustomerName,cust_dwg_no as CustDwgNo,part_no as PartNo,responsibility as Resp, loc_code as Location from ddci_info i,ddcust_mast c,prd_ciref p where i.ci_reference=p.ci_ref (+) and i.cust_code = c.cust_code (+) and pending=0 and allot_part_no=1 order by ci_reference
//select ci_reference as CIReference,prod_desc as ProductDesc,cust_name as CustomerName,cust_dwg_no as CustDwgNo,part_no as PartNo,responsibility as Resp, loc_code as Location from ddci_info i,ddcust_mast c,prd_ciref p where i.ci_reference=p.ci_ref (+) and i.cust_code = c.cust_code (+) and pending=1 order by ci_reference
//
try
{
//dataValue = ToDataTable((from c in DB.DDOPER_MAST.AsEnumerable()
// where ((Convert.ToBoolean(Convert.ToInt16(c.DELETE_FLAG)) == false) || (c.DELETE_FLAG == null)) && !c.OPER_DESC.StartsWith("FORG")
// orderby c.OPER_CODE ascending
// select new { c.OPER_CODE, c.OPER_DESC }).ToList());
//List<DDCI_INFO> featureMastNew = (from cost in DB.DDCI_INFO
// select cost).ToList();
//return featureMastNew;
string subQuery = string.Empty;
subQuery = " where 1 = 1 ";
if (costsheetsearch.ChkShowPending)
{
subQuery += " and pending = 1 ";
}
else
{
subQuery += " and pending = 0 ";
}
if (!string.IsNullOrEmpty(costsheetsearch.PROD_DESC))
{
subQuery += " AND UPPER(prod_desc) like '%" + sqlEncode(costsheetsearch.PROD_DESC.ToValueAsString().Trim().ToUpper()) + "%'";
}
if (!string.IsNullOrEmpty(costsheetsearch.PART_NO))
{
subQuery += " AND UPPER(part_no) = '" + sqlEncode(costsheetsearch.PART_NO.ToUpper().Trim()) + "'";
}
if (!string.IsNullOrEmpty(costsheetsearch.CUST_NAME))
{
subQuery += " AND UPPER(cust_name) like '%" + sqlEncode(costsheetsearch.CUST_NAME.ToUpper()) + "%'";
}
if (!string.IsNullOrEmpty(costsheetsearch.CUST_DWG_NO))
{
subQuery += " AND UPPER(cust_dwg_no) like '%" + sqlEncode(costsheetsearch.CUST_DWG_NO.ToUpper()) + "%'";
}
if (costsheetsearch.ChkShowDomesticOnly)
{
subQuery += " AND ci_reference not like 'O%' and ci_reference not like 'U%' and ci_reference not like 'X%'";
}
if (costsheetsearch.ChkShowExpertOnly)
{
subQuery += " AND ci_reference like 'X%'";
}
//Right Click - Pending Allot partnumber - Condition should be Allot_part_no = 0 - 17/12/2015
if (costsheetsearch.ChkPendingPartNoAllocation)
{
subQuery += " AND (allot_part_no=0 or allot_part_no is null) ";
}
else
{
//subQuery += " AND (allot_part_no=1) ";
}
//if (costsheetsearch.ChkShowAll == true && (costsheetsearch.PROD_DESC.IsNotNullOrEmpty() || costsheetsearch.PART_NO.IsNotNullOrEmpty() || costsheetsearch.CUST_NAME.IsNotNullOrEmpty() || costsheetsearch.CUST_DWG_NO.IsNotNullOrEmpty()))
//{
// // subQuery = "";
// //CostSheetSearch.PROD_DESC = string.Empty;
// //CostSheetSearch.PART_NO = string.Empty;
// //CostSheetSearch.CUST_NAME = string.Empty;
// //CostSheetSearch.CUST_DWG_NO = string.Empty;
//}
//else
//original
//if (costsheetsearch.ChkShowAll == true)
//{
// subQuery = " where 1 = 1 and pending = 0 ";
//}
//end
//new
if (costsheetsearch.ChkShowAll == true && (string.IsNullOrEmpty(costsheetsearch.PROD_DESC) && string.IsNullOrEmpty(costsheetsearch.PART_NO) && string.IsNullOrEmpty(costsheetsearch.CUST_NAME) && string.IsNullOrEmpty(costsheetsearch.CUST_DWG_NO)))
{
subQuery = " where 1 = 1 and pending = 0 ";
}
//else if (costsheetsearch.ChkShowAll == true && !string.IsNullOrEmpty(costsheetsearch.CUST_DWG_NO))
else
{
subQuery += "";
}
//new end
subQuery += " order by ci_reference ";
string getquery = " select ci_reference,prod_desc,cust_name,cust_dwg_no ,part_no ,responsibility, loc_code as location,ddi.idpk as CI_INFO_PK "
+ " from ddci_info ddi "
+ " Left join ddcust_mast ddc on ddc.CUST_CODE = ddi.CUST_CODE "
+ " Left join prd_ciref prd on prd.CI_REF = ddi.CI_REFERENCE " + subQuery;
var getcollection = ToDataTable(DB.ExecuteQuery <CostSheetSearchModel>(getquery).ToList());
return(getcollection.DefaultView);
//return ToDataTable((from ddct in DB.DDCI_INFO
// join ddcust in DB.DDCUST_MAST on ddct.CUST_CODE equals ddcust.CUST_CODE
// select new
// {
// CI_REFERENCE = ddct.CI_REFERENCE,
// PROD_DESC = ddct.PROD_DESC,
// CUST_DWG_NO = ddct.CUST_DWG_NO,
// CUST_NAME = ddcust.CUST_NAME
// }).ToList()).DefaultView;
}
catch (Exception ex)
{
throw ex.LogException();
}
}
