public bool Exist(string strUsername)
{
DataRequest req = new DataRequest("select top 1 * from tbs_User where Username = @Username");
req.Parameters.Add("@Username", SqlDbType.VarChar, strUsername);
return(m_db.ExecuteCommand(req).Rows.Count > 0);
}
public bool Exist(decimal dcGroupID)
{
DataRequest req = new DataRequest("select top 1 * from tbs_UserGroup where GroupID = @GroupID");
req.Parameters.Add("@GroupID", SqlDbType.Decimal, dcGroupID);
return(m_db.ExecuteCommand(req).Rows.Count > 0);
}
public static bool AllowPermission(Database db, string strUsername, string strScreenClass, string strPermissionName)
{
string sql = string.Empty;
sql += " select top 1 ";
sql += " sec.*, scr.ClassName ";
sql += " from ";
sql += " tbs_SecurityMatch sec inner join tbs_Screen scr on sec.ScreenID = scr.ScreenID ";
sql += " where ";
sql += " sec.Username = @Username ";
sql += " and sec.Permission = @PermName ";
sql += " and scr.ClassName = @ClassName ";
sql += " union all ";
sql += " select top 1 ";
sql += " sec.*, scr.ClassName ";
sql += " from ";
sql += " tbs_SecurityMatch sec ";
sql += " inner join tbs_GroupMapping grp on sec.GroupID = grp.GroupID ";
sql += " inner join tbs_User usr on usr.Username = grp.Username ";
sql += " inner join tbs_Screen scr on sec.ScreenID = scr.ScreenID ";
sql += " where ";
sql += " usr.Username = @Username ";
sql += " and sec.Permission = @PermName ";
sql += " and scr.ClassName = @ClassName ";
DataRequest req = new DataRequest(sql);
req.Parameters.Add("@Username", SqlDbType.VarChar, strUsername);
req.Parameters.Add("@PermName", SqlDbType.VarChar, strPermissionName);
req.Parameters.Add("@ClassName", SqlDbType.VarChar, strScreenClass);
return(db.ExecuteCommand(req).Rows.Count > 0);
}
public static DataTable sp_GetCostTypeCombo(Database db, string CostType)
{
DataRequest req = new DataRequest("sp_GetCostTypeCombo", CommandType.StoredProcedure);
req.Parameters.Add("CostType", SqlDbType.VarChar, CostType);
return(db.ExecuteCommand(req));
}
public static DataTable sp_GetItemGroupCombo(Database db, int?ItemGroupCode)
{
DataRequest req = new DataRequest("sp_GetItemGroupCombo", CommandType.StoredProcedure);
req.Parameters.Add("ItemGroupCode", SqlDbType.Int, ItemGroupCode);
return(db.ExecuteCommand(req));
}
public static DataTable sp_ACS340_RetrieveData(Database db, int year)
{
DataRequest req = new DataRequest("sp_ACS340_RetrieveData", CommandType.StoredProcedure);
req.Parameters.Add("Year", SqlDbType.Int, year);
return(db.ExecuteCommand(req));
}
public static DataTable sp_ACS120_SearchMappingData(Database db, string mapAccCode)
{
DataRequest req = new DataRequest("sp_ACS120_SearchMappingData", CommandType.StoredProcedure);
req.Parameters.Add("AccCode", SqlDbType.VarChar, mapAccCode);
return(db.ExecuteCommand(req));
}
public static DataTable sp_ACS330_Get_JournalEntryData(Database db, int year, int period)
{
DataRequest req = new DataRequest("sp_ACS330_Get_JournalEntryData", CommandType.StoredProcedure);
req.Parameters.Add("Year", SqlDbType.Int, year);
req.Parameters.Add("Period", SqlDbType.Int, period);
return(db.ExecuteCommand(req));
}
public static DataTable sp_Common_Get_Revision(Database db, int year, int period)
{
DataRequest req = new DataRequest("sp_Common_Get_Revision", CommandType.StoredProcedure);
req.Parameters.Add("Year", SqlDbType.VarChar, year);
req.Parameters.Add("Period", SqlDbType.VarChar, period);
return(db.ExecuteCommand(req));
}
public static DataTable sp_RPT010_MOHSummaryReport(Database db, int Period, int Year)
{
DataRequest req = new DataRequest("sp_RPT010_MOHSummaryReport", CommandType.StoredProcedure);
req.Parameters.Add("Year", SqlDbType.Int, Year);
req.Parameters.Add("Period", SqlDbType.Int, Period);
return(db.ExecuteCommand(req));
}
public static DataTable sp_RPT050_DetailBudgetCapaVarianceReport(Database db, int Year, int Period)
{
DataRequest req = new DataRequest("sp_RPT050_DetailBudgetCapaVarianceReport", CommandType.StoredProcedure);
req.Parameters.Add("Year", SqlDbType.Int, Year);
req.Parameters.Add("Period", SqlDbType.Int, Period);
return(db.ExecuteCommand(req));
}
public static DataTable RPT030_CostOfGoodsManuReport_Detail(Database db, int Year, int Period)
{
DataRequest req = new DataRequest("sp_RPT030_CostOfGoodsManuReport_Detail", CommandType.StoredProcedure);
req.Parameters.Add("Year", SqlDbType.Int, Year);
req.Parameters.Add("Period", SqlDbType.Int, Period);
return(db.ExecuteCommand(req));
}
public static DataTable GetMessage(Database db, string MsgCode)
{
DataRequest req = new DataRequest("sp_Common_Get_Message", CommandType.StoredProcedure);
req.Parameters.Add("MsgCode", SqlDbType.NVarChar, MsgCode);
DataTable dtb = db.ExecuteCommand(req);
return(dtb);
}
public static DataTable sp_ACS110_SearchCostType(Database db, string CostTypeCode, string CostDesc, string CostGroup)
{
DataRequest req = new DataRequest("sp_ACS110_SearchCostType", CommandType.StoredProcedure);
req.Parameters.Add("CostTypeCode", SqlDbType.VarChar, CostTypeCode);
req.Parameters.Add("CostDesc", SqlDbType.VarChar, CostDesc);
req.Parameters.Add("CostGroup", SqlDbType.VarChar, CostGroup);
return(db.ExecuteCommand(req));
}
public static DataTable sp_RPT040_ClosingItemReport(Database db, int Year, int Period, int?ItemGroupFrom, int?ItemGroupTo, string ItemCodeFrom, string ItemCodeTo)
{
DataRequest req = new DataRequest("sp_RPT040_ClosingItemReport", CommandType.StoredProcedure);
req.Parameters.Add("Year", SqlDbType.Int, Year);
req.Parameters.Add("Period", SqlDbType.Int, Period);
req.Parameters.Add("ItemGroupFrom", SqlDbType.Int, ItemGroupFrom);
req.Parameters.Add("ItemGroupTo", SqlDbType.Int, ItemGroupTo);
req.Parameters.Add("ItemCodeFrom", SqlDbType.NVarChar, ItemCodeFrom);
req.Parameters.Add("ItemCodeTo", SqlDbType.NVarChar, ItemCodeTo);
return(db.ExecuteCommand(req));
}
public static DataTable sp_RPT020_CostReports(Database db, int Year, int Period, string ProdDateFrom, string ProdDateTo, string ProdOrderNoFrom, string ProdOrderNoTo, string ItemType)
{
DataRequest req = new DataRequest("sp_RPT020_CostReports", CommandType.StoredProcedure);
req.Parameters.Add("Year", SqlDbType.Int, Year);
req.Parameters.Add("Period", SqlDbType.Int, Period);
req.Parameters.Add("ProdDateFrom", SqlDbType.NVarChar, ProdDateFrom);
req.Parameters.Add("ProdDateTo", SqlDbType.NVarChar, ProdDateTo);
req.Parameters.Add("ProdOrderNoFrom", SqlDbType.NVarChar, ProdOrderNoFrom);
req.Parameters.Add("ProdOrderNoTo", SqlDbType.NVarChar, ProdOrderNoTo);
req.Parameters.Add("ItemType", SqlDbType.NVarChar, ItemType);
return(db.ExecuteCommand(req));
}
public static bool TestConnection(Database db)
{
try
{
db.Open();
// ถ้าไม่ระบุ DatabaseName ทาง SqlServer จะ Default ไปที่ Master
// ซึ่งจะทำให้เข้าได้ทุกกรณี
db.ExecuteCommand(new DataRequest("SELECT GetDate()"));
// เช็คตาราง ถ้าไม่พบ แสดงว่าไม่ใช่ Database ที่ต้องการ
//db.ExecuteCommand(new DataRequest("SELECT TOP 1 1 FROM tb_Msg"));
db.Close();
return(true);
}
catch (Exception ex)
{
Trace.WriteLine(ex.Message);
return(false);
}
}
public static DataTable sp_GetPeriodCombo(Database db)
{
DataRequest req = new DataRequest("sp_GetPeriodCombo", CommandType.StoredProcedure);
return(db.ExecuteCommand(req));
}
public static DataTable sp_Common_Get_Config(Database db)
{
DataRequest req = new DataRequest("sp_Common_Get_Config", CommandType.StoredProcedure);
return(db.ExecuteCommand(req));
}
