protected void Showgrid()
{
try
{
string str = ddlfinancialyear.SelectedItem.Text.Trim();
string[] token = str.Split('-');
string[] tokens = str.Split('-');
string first = token[token.Length - 2];
string last = tokens[tokens.Length - 1];
string sSql = "SELECT ";
sSql += "[PurchaseType] as 'PurchaseType', ";
sSql += "[ProductType] as 'ProductType', ";
sSql += "[01] AS Jan, ";
sSql += "[02] AS Feb, ";
sSql += "[03] AS Mar, ";
sSql += "[04] AS Apr, ";
sSql += "[05] AS May, ";
sSql += "[06] AS Jun, ";
sSql += "[07] AS Jul, ";
sSql += "[08] AS Aug, ";
sSql += "[09] AS Sep, ";
sSql += "[10] AS Oct, ";
sSql += "[11] AS Nov, ";
sSql += "[12] AS Dec ";
sSql += "FROM ";
sSql += "( ";
sSql += "SELECT [PurchaseType],[ProductType], DATEPART(Month, [Date]) AS Month, CONVERT(decimal(10,2), CONVERT(varbinary(20), [PriceConsume]))as PriceConsume,[Active] ";
sSql += "FROM [HB_CloseStock]";
sSql += "Where ";
if (ddlfinancialyear.SelectedValue != "")
{
sSql += "CONVERT (datetime, Date, 103) >= CONVERT (datetime, '01/04/" + first + "', 103) and CONVERT (datetime, Date, 103) <= CONVERT (datetime, '31/03/" + last + "', 103)";
}
else
{
sSql += "CONVERT (datetime, Date, 103) >= CONVERT (datetime, '" + DateTime.Now.ToString("01/04/yyyy") + "', 103) and CONVERT (datetime, Date, 103) <= CONVERT (datetime, '" + DateTime.Now.ToString("31/03/yyyy") + "', 103)";
}
sSql += ")";
sSql += "N ";
sSql += "PIVOT ";
sSql += "( ";
sSql += "SUM ([PriceConsume]) ";
sSql += "FOR Month IN ";
sSql += "( ";
sSql += "[01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12]) ";
sSql += ") AS pvt ";
sSql += "WHERE [Active] = 'Y' ";
if ((ddlPurchaseType.SelectedItem.Text != "-- Select --") && (ddlProductType.SelectedItem.Text != "-- Select --"))
{
sSql += "and [PurchaseType] = '" + ddlPurchaseType.SelectedItem + "' and [ProductType]='" + ddlProductType.SelectedItem + "'";
}
else if (ddlPurchaseType.SelectedItem.Text != "-- Select --")
{
sSql += "and [PurchaseType] = '" + ddlPurchaseType.SelectedItem + "'";
}
sSql += "ORDER BY [ProductType] ";
SqlDataSource1.SelectCommand = sSql;
dbgrid.DataBind();
}
catch (Exception ex)
{
lblMessage.Text = ex.Message;
}
}
{
try
{
string str = ddlfinancialyear.SelectedItem.Text.Trim();
string[] token = str.Split('-');
string[] tokens = str.Split('-');
string first = token[token.Length - 2];
string last = tokens[tokens.Length - 1];
string sSql = "SELECT ";
sSql += "[PurchaseType] as 'PurchaseType', ";
sSql += "[ProductType] as 'ProductType', ";
sSql += "[01] AS Jan, ";
sSql += "[02] AS Feb, ";
sSql += "[03] AS Mar, ";
sSql += "[04] AS Apr, ";
sSql += "[05] AS May, ";
sSql += "[06] AS Jun, ";
sSql += "[07] AS Jul, ";
sSql += "[08] AS Aug, ";
sSql += "[09] AS Sep, ";
sSql += "[10] AS Oct, ";
sSql += "[11] AS Nov, ";
sSql += "[12] AS Dec ";
sSql += "FROM ";
sSql += "( ";
sSql += "SELECT [PurchaseType],[ProductType], DATEPART(Month, [Date]) AS Month, CONVERT(decimal(10,2), CONVERT(varbinary(20), [PriceConsume]))as PriceConsume,[Active] ";
sSql += "FROM [HB_CloseStock]";
sSql += "Where ";
if (ddlfinancialyear.SelectedValue != "")
{
sSql += "CONVERT (datetime, Date, 103) >= CONVERT (datetime, '01/04/" + first + "', 103) and CONVERT (datetime, Date, 103) <= CONVERT (datetime, '31/03/" + last + "', 103)";
}
else
{
sSql += "CONVERT (datetime, Date, 103) >= CONVERT (datetime, '" + DateTime.Now.ToString("01/04/yyyy") + "', 103) and CONVERT (datetime, Date, 103) <= CONVERT (datetime, '" + DateTime.Now.ToString("31/03/yyyy") + "', 103)";
}
sSql += ")";
sSql += "N ";
sSql += "PIVOT ";
sSql += "( ";
sSql += "SUM ([PriceConsume]) ";
sSql += "FOR Month IN ";
sSql += "( ";
sSql += "[01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12]) ";
sSql += ") AS pvt ";
sSql += "WHERE [Active] = 'Y' ";
if ((ddlPurchaseType.SelectedItem.Text != "-- Select --") && (ddlProductType.SelectedItem.Text != "-- Select --"))
{
sSql += "and [PurchaseType] = '" + ddlPurchaseType.SelectedItem + "' and [ProductType]='" + ddlProductType.SelectedItem + "'";
}
else if (ddlPurchaseType.SelectedItem.Text != "-- Select --")
{
sSql += "and [PurchaseType] = '" + ddlPurchaseType.SelectedItem + "'";
}
sSql += "ORDER BY [ProductType] ";
SqlDataSource1.SelectCommand = sSql;
dbgrid.DataBind();
}
catch (Exception ex)
{
lblMessage.Text = ex.Message;
}
}
No comments:
Post a Comment