public List <BrandEntity> ListBrandCheckProduct()
{
string sql = @" select b.BrandID,b.BrandName,b.BrandEnabled,t.TrdID,t.TrdName from BRAND b , TRADEMARK t
where t.TrdID = b.TrdID and b.BrandID not in (select BrandID from PRODUCT)";
return(Db.ReadList(sql, Make));
}
public List <WeakOfferDetailsEntity> GetListOffer(string offID)
{
string sql = @"SELECT
offdta.[OffID]
,offdta.[OfdNo]
,offer.[OffStatus]
,s.[SupID]
,s.[SupName]
,s.[SubAddress]
,p.[PrdID]
,p.[ClrID]
,c.[ClrName]
,p.[TypeProductID]
,t.[TypeName]
,b.[TrdID]
,tr.[TrdName]
,p.[BrandID]
,b.[BrandName]
,[Description]
,[Waranty]
,[PrdEnabled]
,offdta.[AmountPost]
,offdta.[AmountApprove]
,offdta.[Cost]
FROM [PRODUCT] as p, [COLOR] as c , [BRAND] as b, [TYPEPRODUCT] as t, [TRADEMARK] as tr , SUPPLIER as s,[OFFERDETAIL] as offdta ,OFFER as offer
where c.ClrID = p.ClrID and p.TypeProductID = t.TypeProductID and b.TrdID = tr.TrdID
and p.BrandID = b.BrandID and s.SupID = offer.SupID and PrdEnabled = 0 and p.PrdID = offdta.PrdID and offer.OffID = offdta.OffID and offer.OffID = '" + offID + "'";
return(Db.ReadList(sql, MakeGetID));
}
public List <BANK> ListBank()
{
string sql = @"SELECT *
FROM [dbo].[BANK] where BnkEnabled = 0";
return(Db.ReadList(sql, Make));
}
public List <BrandEntity> ListBrandEdit(string id)
{
string sql = @"select t.TrdID,t.TrdName,b.[BrandID],b.BrandName,b.BrandEnabled
FROM BRAND b ,TRADEMARK t where b.TrdID = t.TrdID and
b.[BrandID] not in (Select p.[BrandID] FROM [PRODUCT] p where p.BrandID != '" + id + "') ";
return(Db.ReadList(sql, Make));
}
public List <ColorEntity> ListColor()
{
string sql = @"SELECT [ClrID]
,[ClrName]
,[ClrEnabled]
FROM [COLOR] where ClrEnabled = 0";
return(Db.ReadList(sql, Make));
}
public List <TypeproductEntity> ListTypeproduct()
{
string sql = @"SELECT [TypeProductID]
,[TypeName]
,[TypeEnabled]
FROM [dbo].[TYPEPRODUCT] where TypeEnabled = 0";
return(Db.ReadList(sql, Make));
}
public List <TradmarkEntity> ListTradmark()
{
string sql = @"SELECT [TrdID]
,[TrdName]
,[TrdEnabled]
FROM [dbo].[TRADEMARK] where TrdEnabled = 0
order by TrdID asc";
return(Db.ReadList(sql, Make));
}
public List <PositionsEntity> ListPosition()
{
//string sql = @"SELECT [DepID],[PosName]FROM [POSITION]";
string sql = @"SELECT p.[PosID],p.[PosName],d.[DepID],d.DepName
FROM [POSITION] as p ,[DEPARTMENT] as d
where p.DepID = d.DepID and PosEnabled = 0
ORDER BY P.[DEPID]";
return(Db.ReadList(sql, Make));
}
public List <WeakPosDetailsEntity> GetListPosition(string PosID, string DepID)
{
// string sql = @"SELECT TOP 1000 r.RoleName,pr.RoleID,p.[PosID],d.DepName,p.[PosName]
// FROM [_PosInRoles] as pr ,[_Roles] as r ,[DEPARTMENT] d , [POSITION] p
// where pr.RoleID = r.RoleID and p.DepID = d.DepID and p.PosID =" + PosID + " and pr.[PosID] =" + PosID;
string sql = @"SELECT pr.[RoleID],r.[RoleName],p.[PosID],p.[PosName],d.[DepID],d.[DepName]
FROM [gamingstore].[dbo].[_PosInRoles] as pr ,[gamingstore].[dbo].[_Roles] as r ,[gamingstore].[dbo].[DEPARTMENT] d ,[gamingstore].[dbo].[POSITION] p
where pr.RoleID = r.RoleID and p.DepID = d.DepID and p.PosID ='" + PosID + "' and pr.[PosID] ='" + PosID + "'";
return(Db.ReadList(sql, MakeMulti));
}
public List <SupplierEntity> ListSupplier()
{
string sql = @"SELECT [SupID]
,[SupName]
,[SubAddress]
,[SubEnabled]
FROM [SUPPLIER]
where SubEnabled = 0
order by SupID asc";
return(Db.ReadList(sql, Make));
}
public List <BrandEntity> ListBrandAll()
{
string sql = @"SELECT [BrandID]
,[BrandName]
,b.TrdID
,t.TrdName
,[BrandEnabled]
FROM [BRAND] as b , TRADEMARK as t
where t.TrdID = b.TrdID and BrandEnabled =0
order by BrandID asc";
return(Db.ReadList(sql, Make));
}
public List <PROMOTION> ListPROMOTION()
{
string sql = @"SELECT convert(int,DATEDIFF(DAY, pm.ProMStartDate, pm.ProMEndDate)) as RangeDate,*
FROM [dbo].[PROMOTION] as pm where pm.ProMEnabled = 0";
return(Db.ReadList(sql, Make));
//select *
// from(
// SELECT convert(int,DATEDIFF(DAY, pm.ProMStartDate, pm.ProMEndDate)) as diff , *
// FROM [dbo].[PROMOTION] as pm where pm.ProMEnabled = 0
// ) aa
// group by aa.ProMID,aa.diff,aa.ProMStartDate,aa.ProMEndDate,aa.Discount,aa.ProMEnabled
// having aa.diff > 0
}
public List <OfferEntity> ListOfferForOrder(string id)
{
string sql = @"SELECT
[OffID]
,e.[EmpID]
,e.Fname
,o.[EmpApprove]
,s.SupName
,[OffDate]
,[OffStatus]
FROM [OFFER] o ,[SUPPLIER] s,EMPLOYEE e
where o.SupID = s.SupID and e.EmpID = o.EmpID and [OffEnabled] = 0 and OffStatus like 'Approve' and s.SupID = '" + id + "'";
return(Db.ReadList(sql, Make));
}
public List <OfferEntity> ListOffer()
{
string sql = @"SELECT [OffID]
,e.[EmpID]
,e.Fname
,s.SupName
,[OffDate]
,[OffStatus]
,[OffEnabled]
,[EmpApprove]
FROM [OFFER] o ,[SUPPLIER] s,EMPLOYEE e
where o.SupID = s.SupID and e.EmpID = o.EmpID and [OffEnabled] = 0 and OffStatus not like 'Arrive'
order by OffID desc ";
return(Db.ReadList(sql, Make));
}
public List <PROMOTION_DTA> GetPROMOTION2(string PROMOTIONID)
{
string sql = @"SELECT pm.ProMID,p_dta.PrdID
,p.[ClrID]
,c.[ClrName]
,p.[TypeProductID]
,t.[TypeName]
,b.[TrdID]
,tr.[TrdName]
,p.[BrandID]
,b.[BrandName]
FROM [PROMOTION_PRODUCT_DTA] as p_dta,PROMOTION as pm,[PRODUCT] as p, [COLOR] as c , [BRAND] as b, [TYPEPRODUCT] as t, [TRADEMARK] as tr
where c.ClrID = p.ClrID and p.TypeProductID = t.TypeProductID and b.TrdID = tr.TrdID
and p.BrandID = b.BrandID and p.PrdID = p_dta.PrdID and pm.ProMID = p_dta.ProMID and pm.ProMID ='" + PROMOTIONID + "'";
return(Db.ReadList(sql, MakeID));
}
public List <BrandEntity> ListBrand(string SupID)
{
// string sql = @"
// SELECT t.TrdID,t.TrdName,b.[BrandID],b.BrandName,b.BrandEnabled,p2.PrdID from PRODUCT p2
// , BRAND b ,TRADEMARK t where b.TrdID = t.TrdID and p2.BrandID = b.BrandID and
// p2.[BrandID] not in(
// SELECT b.[BrandID]
// FROM BRAND b ,[PRODUCT] p where b.BrandID = p.BrandID and p.PrdID in(
// select PrdID from PRODUCTSUPPLIER
// where SupID='" + SupID + "')) order by t.TrdName";
string sql = @" SELECT t.TrdID,t.TrdName,b.[BrandID],b.BrandName,b.BrandEnabled from
BRAND b ,TRADEMARK t where b.TrdID = t.TrdID and b.BrandID not in(
SELECT b.[BrandID]
FROM BRAND b ,[PRODUCT] p where b.BrandID = p.BrandID and p.PrdID in(
select PrdID from PRODUCTSUPPLIER
where SupID='" + SupID + "')) order by t.TrdName";
return(Db.ReadList(sql, Make));
}
public List <ProductEntity> ListProductNoPic(string SupID)
{
string sql = @"SELECT p.[PrdID]
,p.[ClrID]
,c.ClrName
,p.[TypeProductID]
,t.TypeName
,b.TrdID
,tr.TrdName
,p.[BrandID]
,b.BrandName
,[Description]
,[Waranty]
,PrdEnabled ,PrdStatus
FROM [PRODUCT] as p, [COLOR] as c , [BRAND] as b, [TYPEPRODUCT] as t, [TRADEMARK] as tr, PRODUCTSUPPLIER ps,SUPPLIER s
where c.ClrID = p.ClrID and p.TypeProductID = t.TypeProductID and b.TrdID = tr.TrdID
and p.BrandID = b.BrandID and PrdEnabled = 0 and p.PrdID = ps.PrdID and ps.SupID = s.SupID and ps.SupID = '" + SupID + "'";
return(Db.ReadList(sql, Make2));
}
public List <EmpPosDetial> ListEmployee()
{
string sql = @"SELECT [EmpID]
,e.PosID
,p.PosName
,[Idcard]
,[Email]
,[Fname]
,[Lname]
,[Address]
,[Sex]
,[Salary]
,[Username]
,[Password]
,[EStatus]
FROM [dbo].[EMPLOYEE] as e , dbo.POSITION as p where p.PosID = e.PosID and EStatus = 0
order by e.PosID asc";
return(Db.ReadList(sql, Make));
}
public List <ProductEntity> ListProductPic1()
{
string sql = @"SELECT p.[PrdID]
,p.[ClrID]
,c.ClrName
,p.[TypeProductID]
,t.TypeName
,b.TrdID
,tr.TrdName
,p.[BrandID]
,b.BrandName
,[Photo]
,[Description]
,[Waranty]
,PrdEnabled
,PrdStatus,ReorderPoint,Price
FROM [PRODUCT] as p, [PHOTO] as ph , [COLOR] as c , [BRAND] as b, [TYPEPRODUCT] as t, [TRADEMARK] as tr
where p.PrdID = ph.PrdID and c.ClrID = p.ClrID and p.TypeProductID = t.TypeProductID and b.TrdID = tr.TrdID
and p.BrandID = b.BrandID and PrdEnabled = 0 and ph.PhotoID = 1";
return(Db.ReadList(sql, MakeL));
}
public List <WeakOrderDetailsEntity> GetListOrderOLD(string ordID)
{
string sql = @"SELECT
ISNULL(rec_dta.[AmountRec],0) as [AmountRec]
,offdta.[AmountApprove],
odta.OrdID,odta.OrdNo,o.OrdDate,o.TotalCost,
offdta.[OffID]
,offdta.[OfdNo]
,offer.[OffStatus]
,offdta.[AmountPost]
,offdta.[Cost]
,s.[SupID]
,s.[SupName]
,s.[SubAddress]
,p.[PrdID]
,p.[ClrID]
,c.[ClrName]
,p.[TypeProductID]
,t.[TypeName]
,b.[TrdID]
,tr.[TrdName]
,p.[BrandID]
,b.[BrandName]
,[Description]
,[Waranty]
,[PrdEnabled]
,p.SerialStatus
FROM [PRODUCT] as p, [COLOR] as c , [BRAND] as b, [TYPEPRODUCT] as t, [TRADEMARK] as tr , SUPPLIER as s,[OFFERDETAIL] as offdta ,OFFER as offer
, [ORDER_PRODUCT] o ,
RECEIVEORDER_DTA as rec_dta right join [ORDER_PRODUCT_DTA] odta
on odta.OrdID = rec_dta.OrdID and odta.OrdNo = rec_dta.OrdNo
where c.ClrID = p.ClrID and p.TypeProductID = t.TypeProductID and b.TrdID = tr.TrdID
and p.BrandID = b.BrandID and s.SupID = offer.SupID and PrdEnabled = 0 and
p.PrdID = offdta.PrdID and offer.OffID = offdta.OffID and o.SupID = s.SupID
and o.OrdID = odta.OrdID and odta.OffID = offdta.OffID and odta.OfdNo = offdta.OfdNo
and odta.OrdID = '" + ordID + "'" + "order by odta.OrdNo";
return(Db.ReadList(sql, MakeGetIDOLD));
}
public List <OrderEntity> ListOrder()
{
// string sql = @"SELECT [OrdID]
// ,p.[SupID]
// ,s.SupName
// ,p.[EmpID]
// ,e.Fname
// ,[OrdDate]
// ,[OrdEnabled]
// ,[TotalCost]
// FROM [ORDER_PRODUCT] p , [SUPPLIER] s, [EMPLOYEE] e
// where p.SupID = s.SupID and e.EmpID = p.EmpID and OrdEnabled = 0";
string sql = @"SELECT [OrdID]
,p.[SupID]
,s.SupName
,p.[EmpID]
,e.Fname
,[OrdDate]
,[OrdEnabled]
,[TotalCost]
FROM [ORDER_PRODUCT] p , [SUPPLIER] s, [EMPLOYEE] e
where p.SupID = s.SupID and e.EmpID = p.EmpID and OrdEnabled = 0
and p.OrdID IN(
select distinct aa.OrdID
from(
SELECT a.OrdID,a.OrdNo , a.OrdAmount - ISNULL(sum(b.AmountRec),0) as cal
FROM [ORDER_PRODUCT_DTA] a
left outer join [RECEIVEORDER_DTA] b
ON a.OrdID = b.OrdID
and a.OrdNo = b.OrdNo
group by a.OrdID,a.OrdNo,a.OrdAmount
) aa
group by aa.OrdID,aa.OrdNo
having sum(aa.cal) > 0
)" ;
return(Db.ReadList(sql, Make));
}
public List <DepartmentEntity> ListDepartment()
{
string sql = @"SELECT [DepID],[DepName] FROM [DEPARTMENT] where DepEnabled = 0;";
return(Db.ReadList(sql, Make));
}
public List<RoleEntity> ListRole()
{
string sql = @"SELECT [RoleID],[RoleName] FROM [_Roles] where RoleEnabled = 0 ";
return Db.ReadList(sql, Make);
}
