public static String GetServerName_SparCE()
{
DataTable DT = SqlFunctions.GetData("select serverproperty('MachineName') ");
String servername = DT.Rows[0][0].ToString();
return(servername);
}
public static string GetRomPersonalidByCatmanStoreID(string CatmanStoreID)
{
DataTable DTRomID = SqlFunctions.GetData("select Personalid from manrom_store_allocation where storeid=" + CatmanStoreID);
String Personalid = DTRomID.Rows[0][0].ToString();
return(Personalid);
}
public static string GetManGenderByGenderID(string p)
{
DataTable DTmangender = SqlFunctions.GetData("select gender from mangender where genderid=" + p);
String gender = DTmangender.Rows[0][0].ToString();
return(gender);
}
public static string GetRegionByRegionID(string p)
{
DataTable DTRegion = SqlFunctions.GetData("select region from dimmarketregion where regionid=" + p);
String region = DTRegion.Rows[0][0].ToString();
return(region);
}
public static string GetMediaDescriptionByMediaID(string mediaGroupID)
{
DataTable DTmediadescription = SqlFunctions.GetData("select description from dimmarkettopsmediagroup where mediagroupid=" + mediaGroupID);
String mediaDescription = DTmediadescription.Rows[0][0].ToString();
return(mediaDescription);
}
public static string GetLevelByLevelID(string p)
{
DataTable DTmanLevel = SqlFunctions.GetData("select experience from manlevel where levelid=" + p);
String level = DTmanLevel.Rows[0][0].ToString();
return(level);
}
public static string GetJobtitleByJobtitleID(string p)
{
DataTable DTmanJobtitle = SqlFunctions.GetData("select jobtitle from manjobtitle where jobtitleid=" + p);
String Jobtitle = DTmanJobtitle.Rows[0][0].ToString();
return(Jobtitle);
}
public static String GetLatestSaleID()
{
DataTable DT = SqlFunctions.GetData("select max(saleid) from factSalesCurr_DC_Weekly");
String MaxSalesID = DT.Rows[0][0].ToString();
return(MaxSalesID);
}
public static bool GetDiscontinuedEmployeeByPersonalID(string p)
{
Boolean discontinued = false;
DataTable DTBoolean = SqlFunctions.GetData("select discontinued from manemployee where personalid=" + p);
discontinued = Boolean.Parse(DTBoolean.Rows[0][0].ToString());
return(discontinued);
}
public static List <string> GetCatmanStoreIdListByMediaGroup(string MediaGroup)
{
List <String> lst = new List <String>();
DataTable DTStoresBymediagroup = SqlFunctions.GetData("Select catmanstoreid from dimmarkettopsstore where Mediagroupid=" + MediaGroup);
foreach (DataRow r in DTStoresBymediagroup.Rows)
{
lst.Add(r["catmanstoreid"].ToString());
}
return(lst);
}
public static DataTable GetUnMappedStores()
{
System.Data.SqlTypes.SqlInt32 x = System.Data.SqlTypes.SqlInt32.Null;
DbParameter p = new SqlParameter();
p.DbType = DbType.Int32;
p.Value = x;
DataTable dtUnMapped = SqlFunctions.GetData("SELECT dimMarketRegion.Region, dimMarketTopsStore.Store ,dimMarketTopsStore.POS, dimMarketTopsStore.BackOffice, dimMarketTopsStore.Merged, dimMarketTopsStore.TopsID,dimmarkettopsstore.CatmanStoreID FROM dimMarketTopsStore INNER JOIN dimMarketRegion ON dimMarketTopsStore.RegionID = dimMarketRegion.RegionID where catmanstoreid is " + p.Value + " order by region,store asc");
return(dtUnMapped);
}
public static Boolean NextToGroceryStore(String TopsID)
{
Boolean nexttoGroceryStore = false;
DataTable DT = SqlFunctions.GetData("select nexttogrocerystore from dimmarkettopsstore where topsid=" + TopsID);
if (DT.Rows[0][0].ToString() == "True")
{
nexttoGroceryStore = true;
}
return(nexttoGroceryStore);
}
public static String GetMerchandisingName(String Merchid)
{
DataTable DT = SqlFunctions.GetData("select merchandising from vsales where merchid=" + Merchid);
String merchandising = String.Empty;
if (DT.Rows.Count != 0)
{
merchandising = DT.Rows[0][0].ToString();
}
return(merchandising);
}
public static bool GetSearchIfName_SurnameExists(string Name, string Surname)
{
Boolean Exists = false;
DataTable DTExists = SqlFunctions.GetData("select name,surname from manemployee where name='" + Name + "' and surname='" + Surname + "'");
if (DTExists.Rows.Count != 0)
{
Exists = true;
}
return(Exists);
}
public static void InsertRomPersonalIDInmanRom_Store_Allocation(int RomID, int TopsID)
{
DataTable DTcatmanstoreid = LookUp.LU_dimMarketTopsStore.GetCatmanStoreIDByTopsID(TopsID);
String catmanStoreid = DTcatmanstoreid.Rows[0][0].ToString();
DataTable DTPersonalIDvsStoreID = SqlFunctions.GetData("select personalid,storeid from manRom_Store_Allocation where Personalid=" + RomID.ToString() + " and storeid=" + catmanStoreid);
if (DTPersonalIDvsStoreID.Rows.Count == 0)
{
SqlFunctions.ExecuteNonReturnSQL("insert into manRom_Store_Allocation(Personalid,storeid) values(" + RomID + "," + catmanStoreid + ")");
}
}
public static DataTable Execute6MonthQuery(String Merchid)
{
DataTable DT = SqlFunctions.GetData("SET NOCOUNT ON SELECT Region,Storeid,Store,Profile AS Cluster,Barcode,Manufacturer,Brand,Description,SizeDesc,Merchandising,Units,Sales,id_Day AS Date FROM vSales " +
"WHERE RegionId IN (1,2,3,4,5,9) " +
"AND Merchid = " + Merchid + " " +
"AND [year]=2010 " +
"AND [monthid] in (1,2,3,4,5,6) " +
"AND SourceID = 1 " +
"AND discontinued=0");
return(DT);
}
public static string GetDefaultStoreGivenTopsID(String topsid)
{
DataTable DT = SqlFunctions.GetData("select defaultCatmanstoreid from dimmarkettopsstore where topsid=" + topsid);
String storeid = DT.Rows[0][0].ToString();
if (storeid.Length == 0)
{
return("N/A");
}
DataTable DTT = SqlFunctions.GetData("select store from dimmarketstore where storeid=" + storeid);
return(DTT.Rows[0][0].ToString());
}
public static DataTable GetRomtoStoreMappedStoresByPersonalID(int Personalid)
{
DataTable DTMappedStores = SqlFunctions.GetData("Select storeid from manrom_store_allocation where personalid=" + Personalid.ToString());
DataTable DTStores = new DataTable();
DTStores = null;
String Stores = GeneralFunctions.FormatDatatableIntoSQL_INT_String(DTMappedStores, "Storeid", ",");
if (Stores.Length != 0)
{
DTStores = SqlFunctions.GetData("Select Storeid,store from dimmarketstore where storeid in(" + Stores + ") order by store asc");
}
return(DTStores);
}
public static DataTable GetCompleteStoreList()
{
DataTable DTS = SqlFunctions.GetData("SELECT vStore.Region AS REGION, vStore.Store AS STORE, vStore.Profile AS CLUSTER, dimMarketMediaGroupType.MediaGroup AS [MEDIA GROUP], " +
"manEmployee.Name + ' ' + manEmployee.Surname AS ROM, vStore.Geography AS GEOGRAPHY, vStore.Format AS FORMAT, " +
" vStore.StoreType AS [STORE TYPE], vStore.Type AS TYPE, vStore.TypeId, vStore.RegionId, vStore.ProfileId, vStore.StoreID, " +
" manRom_Store_Allocation.PersonalID, vStore.FormatId, vStore.GeographicId, dimMarketMediaGroups.MediaGroupId" +
" FROM manEmployee INNER JOIN" +
" manRom_Store_Allocation ON manEmployee.PersonalId = manRom_Store_Allocation.PersonalID RIGHT OUTER JOIN" +
" dimMarketMediaGroupType INNER JOIN" +
" dimMarketMediaGroups ON dimMarketMediaGroupType.MediaGroupId = dimMarketMediaGroups.MediaGroupId RIGHT OUTER JOIN" +
" vStore ON dimMarketMediaGroups.StoreId = vStore.StoreID ON manRom_Store_Allocation.StoreID = vStore.StoreID" +
" WHERE (vStore.SourceId = 1) AND (vStore.EtaID NOT IN (3))" +
" ORDER BY vStore.Region, vStore.Store, Cluster, dimMarketMediaGroupType.MediaGroup");
return(DTS);
}
public static DataTable ExecuteQuarterlyQuery(String Merchid, String MonthsString, String MonthsString2010, String YearString2008, String YearString2009, String YearString2010)
{
DataTable DT = SqlFunctions.GetData("SET NOCOUNT ON SELECT Region,Storeid,Store,Profile AS Cluster,Barcode,Manufacturer,Brand,Description,SizeDesc,Merchandising,Units,Sales,id_Day AS Date FROM vSales" +
" WHERE RegionId IN (1,2,3,4,5,9)" +
" AND MerchID IN (" + Merchid + ")" +
" AND (([year] in (" + YearString2008 + ")" +
" AND monthID in (" + MonthsString + "))" +
" or ([year] in (" + YearString2009 + ")" +
" AND monthID in (" + MonthsString + "))" +
" or ([year] in (" + YearString2010 + ")" +
" AND monthID in (" + MonthsString2010 + ")))" +
" AND SourceID = 1" +
" AND discontinued=0");
return(DT);
}
public static string GetMediaGroupDescriptionByTopsID(int TopsID)
{
String MediaDescription = "";
DataTable DTmediaResults = SqlFunctions.GetData("select mediagroupID from dimmarkettopsstore where topsid=" + TopsID.ToString());
String resultDummy = DTmediaResults.Rows[0][0].ToString();
if (resultDummy.Length == 0)
{
MediaDescription = "";
}
else
{
MediaDescription = LU_dimMarketTopsMediaGroup.GetMediaDescriptionByMediaID(DTmediaResults.Rows[0][0].ToString());
}
return(MediaDescription);
}
public static string GetEmployeeFullnameByUsername(String username)
{
DataTable DTRomsRaw = SqlFunctions.GetData("select name,surname from manemployee where Username='******'");
DataTable DTRomsConcatNameSurname = new DataTable();
DTRomsConcatNameSurname.Columns.Add("FullName");
foreach (DataRow r in DTRomsRaw.Rows)
{
DataRow DR = DTRomsConcatNameSurname.NewRow();
DR["FullName"] = r["name"].ToString() + " " + r["Surname"].ToString();
DTRomsConcatNameSurname.Rows.Add(DR);
}
String RomFullname = DTRomsConcatNameSurname.Rows[0]["FullName"].ToString();
return(RomFullname);
}
public static string GetRomFullnameByTopsID(int TopsID)
{
String RomFullname = "";
DataTable DTPersonalid = SqlFunctions.GetData("select Personalid from dimmarkettopsstore where topsid=" + TopsID.ToString());
String resultDummy = DTPersonalid.Rows[0][0].ToString();
if (resultDummy.Length == 0)
{
RomFullname = "";
}
else
{
RomFullname = LU_manEmployee.GetEmployeeFullnameByPersonalID(int.Parse(DTPersonalid.Rows[0][0].ToString()));
}
return(RomFullname);
}
public static DataTable GetListOfEmployees(String Jobtitle)
{
DataTable DTRomsRaw = SqlFunctions.GetData("select name,surname,PersonalID from manemployee where jobtitleid=" + Jobtitle + " and discontinued=0 order by name");
DataTable DTRomsConcatNameSurname = new DataTable();
DTRomsConcatNameSurname.Columns.Add("FullName");
DTRomsConcatNameSurname.Columns.Add("PersonalID");
foreach (DataRow r in DTRomsRaw.Rows)
{
DataRow DR = DTRomsConcatNameSurname.NewRow();
DR["FullName"] = r["name"].ToString() + " " + r["Surname"].ToString();
DR["PersonalID"] = r["PersonalID"].ToString();
DTRomsConcatNameSurname.Rows.Add(DR);
}
return(DTRomsConcatNameSurname);
}
public static DataTable GetNameBySupplierID(String SupplierID)
{
DataTable DT = SqlFunctions.GetData("select distinct(name) from vSupplierContacts_Merch_Data_Scan where supplierid=" + SupplierID);
return(DT);
}
public static DataTable GetDistinctSuppliers()
{
DataTable DT = SqlFunctions.GetData("select distinct(supplierID),Name from vSupplierContacts_Merch_Data_Scan order by name");
return(DT);
}
public static DataTable GetProductIDByBarcode(String Barcode)
{
DataTable DT = SqlFunctions.GetData("Select Productid from dimprodNpl where barcode='" + Barcode + "'");
return(DT);
}
public static DataTable GetPersonalidMerchidbyPersonalidMerchID(string Personalid, string Merchid)
{
DataTable DTTT = SqlFunctions.GetData("select * from buyerCategorymapping where personalid=" + Personalid + " and merchid=" + Merchid);
return(DTTT);
}
public static DataTable GetMerchIDByPersonalID(int p)
{
DataTable DTMerchCat = SqlFunctions.GetData("select merchid from buyercategoryMapping where personalid=" + p.ToString());
return(DTMerchCat);
}
public static object GetManJender()
{
DataTable DTJender = SqlFunctions.GetData("select genderid,gender from mangender");
return(DTJender);
}
