public DataSet GetLeadTimeReportData(LeadTime_Search repParamSearch)
{
try
{
LeadTime_Report_List lstLeadTimeReport = new LeadTime_Report_List();
Common.OpenConnection();
String strSQL = "select * from View_Report_LeadTime";
string WhSQL = "";
string strconcat = "";
if (repParamSearch.PartyName != null && repParamSearch.PartyName != "" && repParamSearch.PartyName != "select")
{
WhSQL = WhSQL + strconcat + "popartyname In (" + repParamSearch.PartyName + ")";
strconcat = " and ";
}
if (repParamSearch.ItemName != null && repParamSearch.ItemName != "" && repParamSearch.ItemName != "select")
{
WhSQL = WhSQL + strconcat + "POStockItemName In (" + repParamSearch.ItemName + ")";
strconcat = " and ";
}
//--------------- PO Date -------------------
if (repParamSearch.StartDate_PO != null && repParamSearch.StartDate_PO != "")
{
WhSQL = WhSQL + strconcat + " PODate>='" + repParamSearch.StartDate_PO + "'";
strconcat = " and ";
}
if (repParamSearch.EndtDate_PO != null && repParamSearch.EndtDate_PO != "")
{
WhSQL = WhSQL + strconcat + " PODate<='" + repParamSearch.EndtDate_PO + "'";
strconcat = " and ";
}
//--------------- GRN Date -------------------
if (repParamSearch.StartDate_GRN != null && repParamSearch.StartDate_GRN != "")
{
WhSQL = WhSQL + strconcat + " GRNDate>='" + repParamSearch.StartDate_GRN + "'";
strconcat = " and ";
}
if (repParamSearch.EndtDate_GRN != null && repParamSearch.EndtDate_GRN != "")
{
WhSQL = WhSQL + strconcat + " GRNDate<='" + repParamSearch.EndtDate_GRN + "'";
strconcat = " and ";
}
//--------------- Invoice Date -------------------
if (repParamSearch.StartDate_Invoice != null && repParamSearch.StartDate_Invoice != "")
{
WhSQL = WhSQL + strconcat + " PurchaseDate>='" + repParamSearch.StartDate_Invoice + "'";
strconcat = " and ";
}
if (repParamSearch.EndtDate_Invoice != null && repParamSearch.EndtDate_Invoice != "")
{
WhSQL = WhSQL + strconcat + " PurchaseDate<='" + repParamSearch.EndtDate_Invoice + "'";
strconcat = " and ";
}
if (WhSQL != null && WhSQL != "")
{
strSQL = strSQL + " where " + WhSQL;
}
strSQL += " Order by podate, popartyname, POStockItemName";
SqlCommand cmd = new SqlCommand(strSQL, Common.conn);
DataSet dsLeadTime = new DataSet();
using (SqlDataAdapter sda = new SqlDataAdapter())
{
sda.SelectCommand = cmd;
sda.Fill(dsLeadTime, "rpt_LeadTime");
}
return(dsLeadTime);
}
catch (Exception ex)
{
return(null);
}
}
public DataSet GetReportData_FinalProductStock(FinalProductStock_Search repParamSearch)
{
LeadTime_Report_List lstLeadTimeReport = new LeadTime_Report_List();
Common.OpenConnection();
String strSQL = "";
strSQL += "SELECT Top(1000) sd.CompanyID, sd.StockDate, sd.StockItemName, sd.GodownName, sd.BatchName, sd.Quantity, sd.UOM, sd.Rate, sd.Amount * -1 AS amount, SI.StockGroup ";
strSQL += "FROM dbo.TD_Txn_StockDetails AS sd ";
strSQL += "INNER JOIN dbo.TD_Mst_StockItem AS SI ON sd.CompanyID = SI.CompanyID AND sd.StockItemName = SI.StockItemName ";
string WhSQL = "";
string strconcat = "";
if (repParamSearch.Company != null && repParamSearch.Company != "" && repParamSearch.Company != "select")
{
WhSQL = WhSQL + strconcat + " sd.CompanyID In (" + repParamSearch.Company + ")";
strconcat = " and ";
}
if (repParamSearch.StockGroup != null && repParamSearch.StockGroup != "" && repParamSearch.StockGroup != "select")
{
WhSQL = WhSQL + strconcat + " SI.StockGroup In (" + repParamSearch.StockGroup + ")";
strconcat = " and ";
}
if (repParamSearch.GodownName != null && repParamSearch.GodownName != "" && repParamSearch.GodownName != "select")
{
WhSQL = WhSQL + strconcat + " sd.GodownName In (" + repParamSearch.GodownName + ")";
strconcat = " and ";
}
if (repParamSearch.StartDate_StockDate != null && repParamSearch.StartDate_StockDate != "")
{
WhSQL = WhSQL + strconcat + " sd.StockDate>='" + repParamSearch.StartDate_StockDate + "'";
strconcat = " and ";
}
if (repParamSearch.EndDate_StockDate != null && repParamSearch.EndDate_StockDate != "")
{
WhSQL = WhSQL + strconcat + " sd.StockDate<='" + repParamSearch.EndDate_StockDate + "'";
strconcat = " and ";
}
if (repParamSearch.ItemName != null && repParamSearch.ItemName != "" && repParamSearch.ItemName != "select")
{
WhSQL = WhSQL + strconcat + " sd.StockItemName In (" + repParamSearch.ItemName + ")";
strconcat = " and ";
}
if (WhSQL != null && WhSQL != "")
{
strSQL = strSQL + " where " + WhSQL;
}
strSQL += " Order by CompanyID, StockDate, StockGroup, GodownName";
SqlCommand cmd = new SqlCommand(strSQL, Common.conn);
DataSet dsLeadTime = new DataSet();
using (SqlDataAdapter sda = new SqlDataAdapter())
{
sda.SelectCommand = cmd;
sda.Fill(dsLeadTime, "rpt_LeadTime");
}
return(dsLeadTime);
}