public static DataTable MRPMonthYearTable()
{
DataTable dtTable = new DataTable();
SqlConnection cn = new SqlConnection(GlobalClass.SQLConnString());
DataTable dt = new DataTable();
SqlCommand cmd = null;
SqlDataAdapter adp;
cn.Open();
if (dtTable.Columns.Count == 0)
{
//Columns for AspxGridview
dtTable.Columns.Add("PK", typeof(string));
dtTable.Columns.Add("MRPMonth", typeof(string));
dtTable.Columns.Add("MRPYear", typeof(string));
//dtTable.Columns.Add("EntityCode", typeof(string));
}
//string qry = "SELECT [PK], [MRPMonth], [MRPYear], [EntityCode] FROM [dbo].[tbl_MRP_List] WHERE PK IN(SELECT MAX(PK) FROM [dbo].[tbl_MRP_List] GROUP BY MRPMonth, MRPYear) ORDER BY MRPMonth, MRPYear ASC";
string qry = "SELECT DISTINCT PK, MRPMonth, MRPYear, EntityCode FROM dbo.tbl_MRP_List WHERE(StatusKey = '4') AND (PK IN(SELECT MAX(PK) AS Expr1 FROM dbo.tbl_MRP_List AS tbl_MRP_List_1 GROUP BY MRPMonth, MRPYear)) ORDER BY MRPMonth, MRPYear";
cmd = new SqlCommand(qry);
cmd.Connection = cn;
adp = new SqlDataAdapter(cmd);
adp.Fill(dt);
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
DataRow dtRow = dtTable.NewRow();
dtRow["PK"] = row["PK"].ToString();
dtRow["MRPMonth"] = Convertion.INDEX_TO_MONTH(Convert.ToInt32(row["MRPMonth"].ToString()));
dtRow["MRPYear"] = row["MRPYear"].ToString();
//dtRow["EntityCode"] = row["EntityCode"].ToString();
dtTable.Rows.Add(dtRow);
}
}
dt.Clear();
cn.Close();
return dtTable;
}
public static DataTable MOPMaster(string DocNum)
{
DataTable dtTable = new DataTable();
SqlConnection cn = new SqlConnection(GlobalClass.SQLConnString());
DataTable dt = new DataTable();
SqlCommand cmd = null;
SqlDataAdapter adp;
if (dtTable.Columns.Count == 0)
{
//Columns for AspxGridview
dtTable.Columns.Add("PK", typeof(string));
dtTable.Columns.Add("DocNumber", typeof(string));
dtTable.Columns.Add("MonthYear", typeof(string));
dtTable.Columns.Add("Entity", typeof(string));
dtTable.Columns.Add("BU", typeof(string));
}
cn.Open();
string query = "SELECT dbo.tbl_MRP_List.PK, dbo.tbl_MRP_List.DocNumber, dbo.tbl_MRP_List.DateCreated, dbo.tbl_MRP_List.EntityCode, dbo.vw_AXEntityTable.NAME AS EntityName, dbo.tbl_MRP_List.BUCode, ISNULL(dbo.vw_AXOperatingUnitTable.NAME, '') AS BUName, dbo.tbl_MRP_List.MRPMonth, dbo.tbl_MRP_List.MRPYear FROM dbo.tbl_MRP_List LEFT OUTER JOIN dbo.vw_AXOperatingUnitTable ON dbo.tbl_MRP_List.BUCode = dbo.vw_AXOperatingUnitTable.OMOPERATINGUNITNUMBER LEFT OUTER JOIN dbo.vw_AXEntityTable ON dbo.tbl_MRP_List.EntityCode = dbo.vw_AXEntityTable.ID WHERE(dbo.tbl_MRP_List.DocNumber = '" + DocNum + "')";
cmd = new SqlCommand(query);
cmd.Connection = cn;
adp = new SqlDataAdapter(cmd);
adp.Fill(dt);
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
DataRow dtRow = dtTable.NewRow();
dtRow["PK"] = row["PK"].ToString();
dtRow["DocNumber"] = row["DocNumber"].ToString();
dtRow["MonthYear"] = Convertion.INDEX_TO_MONTH(Convert.ToInt32(row["MRPMonth"].ToString())) + " " + row["MRPYear"].ToString();;
dtRow["Entity"] = row["EntityName"].ToString();
dtRow["BU"] = row["BUName"].ToString();
dtTable.Rows.Add(dtRow);
}
}
dt.Clear();
cn.Close();
return(dtTable);
}
