Procedure

Procedure

CREATE proc [Insert_Purchase]                 
@filepath varchar(200),                       
@sheet varchar(50)                       
as                   
Declare @enteredby varchar(50)=''                 
Declare @SheetName varchar (50),@path varchar(500),@Query varchar(Max)                        
Select @SheetName =  @sheet,@path=@filepath                       
Select @path = 'Excel 12.0;IMEX=1;Database='+@path        
truncate table XLImport_purchase                        
Set @Query =  'Insert into XLImport_purchase([PO No])                          
Select [PO No] FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'','''+@path+''', '+@SheetName+')'                       
Exec (@query)                   
if exists (select 1 from Purchase where Purchase.isActive='1' and [PO No] in                  
(select distinct [PO No] from XLImport_Purchase))                 
begin                 
delete from Purchase where [PO No] in (select distinct [PO No] from XLImport_purchase)                   
Set @Query = 'Insert into Purchase(EnteredBy,Isactive,EnteredOn,[Item Code],[Item Name],[Loc Name],[PO No],[Doc No],             
[Issue Date],[Tax Rate1],[Tax Rate2],[Tax Rate3],AddTaxRate,Quantity,[Unit Cost],[Basic Amt  (BF Tax)],CST,VAT,[ADD  VAT],             
[Total Amt  (AF Tax)])                          
Select '''+@enteredby+''',1,getdate(),[Item Code],[Item Name],[Loc Name],[PO No],[Doc No],             
[Issue Date],[Tax Rate1],[Tax Rate2],[Tax Rate3],AddTaxRate,Quantity,[Unit Cost],[Basic Amt  (BF Tax)],CST,VAT,[ADD  VAT],             
[Total Amt  (AF Tax)] FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'','''+@path+''', '+@SheetName+')'                         
Exec (@query)                   
select 1               
end                 
else if exists (select 1 from Purchase where Purchase.isActive='2' and [PO No] in (select distinct [PO No] from XLImport_purchase))                 
begin                 
select 0                
end                 
begin                 
Set @Query = 'Insert into Purchase(EnteredBy,Isactive,EnteredOn,[Item Code],[Item Name],[Loc Name],[PO No],[Doc No],             
[Issue Date],[Tax Rate1],[Tax Rate2],[Tax Rate3],AddTaxRate,Quantity,[Unit Cost],[Basic Amt  (BF Tax)],CST,VAT,[ADD  VAT],             
[Total Amt  (AF Tax)])                          
Select '''+@enteredby+''',1,getdate(),[Item Code],[Item Name],[Loc Name],[PO No],[Doc No],             
[Issue Date],[Tax Rate1],[Tax Rate2],[Tax Rate3],AddTaxRate,Quantity,[Unit Cost],[Basic Amt  (BF Tax)],CST,VAT,[ADD  VAT],             
[Total Amt  (AF Tax)] FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'','''+@path+''', '+@SheetName+')'                          
select 2               
Exec (@query)                  
end                 
truncate table XLImport_purchase         
delete from purchase where [Doc No] is null  
update Purchase set AddTaxRate = 0 where AddTaxRate is null      
    -------------

CREATE proc                  
[all_deliver_report] --'01-Apr-2012','02-Apr-2012','UNDelivery'       
@fromdate varchar(15),                                                     
@todate varchar(15),         
@type varchar(20)              
as                 
if(@type='Delivery')         
begin         
select COUNT(DocketNo) AS TotalDocketNo,isnull(sum(isnull(cast(REPLACE([Recommended Price],',','') as decimal(18,2)), 0)),0) as TotalAmount                 
from salesnew where DocketNo in (select [Docket No] from  Delivery)      
 and convert(date,[Invoice Date],103) >= CONVERT(date,@fromdate,103)                           
and convert(date,[Invoice Date],103) <= CONVERT(date,@todate,103)     
end          
else         
begin         
select COUNT(DocketNo) AS TotalDocketNo,isnull(sum(isnull(cast(REPLACE([Recommended Price],',','') as decimal(18,2)), 0)),0) as TotalAmount                 
from salesnew where DocketNo not in (select [Docket No] from  Delivery)      
and OrderNois not in(select [OrderNo is] from Sales_Return  where convert(date,[Invoice Date],103) >= CONVERT(date,@fromdate,103)                           
and convert(date,[Invoice Date],103) <= CONVERT(date,@todate,103))             
and convert(date,[Invoice Date],103) >= CONVERT(date,@fromdate,103)                           
--and convert(date,[Invoice Date],103) <= CONVERT(date,@todate,103)     
end