public static List <DashBoardSettingInfo> ListDashBoardSettingForView(int UserModuleID, int PortalID)
{
List <KeyValuePair <string, object> > ParaMeterCollection = new List <KeyValuePair <string, object> >();
ParaMeterCollection.Add(new KeyValuePair <string, object>("UserModuleID", UserModuleID));
ParaMeterCollection.Add(new KeyValuePair <string, object>("PortalID", PortalID));
//SqlDataReader reader = null;
OracleDataReader reader = null;
try
{
//SQLHandler Objsql = new SQLHandler();
OracleHandler Objsql = new OracleHandler();
reader = Objsql.ExecuteAsDataReader("usp_DashBoardSettingsSelect", ParaMeterCollection);
List <DashBoardSettingInfo> lstSetting = new List <DashBoardSettingInfo>();
while (reader.Read())
{
lstSetting.Add(new DashBoardSettingInfo(reader["START_DATE"].ToString(), reader["END_DATE"].ToString(), reader["SELECT_TYPE"].ToString()));
}
return(lstSetting);
}
catch (Exception)
{
throw;
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
public static List <MenuInfo> GetMenuFront(int PortalID, string UserName, string CultureCode)
{
List <MenuInfo> lstPages = new List <MenuInfo>();
string StoredProcedureName = "usp_SageMenuGetClientView";
List <KeyValuePair <string, object> > ParaMeterCollection = new List <KeyValuePair <string, object> >();
ParaMeterCollection.Add(new KeyValuePair <string, object>("prefix", "---"));
ParaMeterCollection.Add(new KeyValuePair <string, object>("IsDeleted", 0));
ParaMeterCollection.Add(new KeyValuePair <string, object>("PortalID", PortalID));
ParaMeterCollection.Add(new KeyValuePair <string, object>("UserName", UserName));
ParaMeterCollection.Add(new KeyValuePair <string, object>("CultureCode", CultureCode));
OracleDataReader SQLReader = null;
try
{
OracleHandler sagesql = new OracleHandler();
SQLReader = sagesql.ExecuteAsDataReader(StoredProcedureName, ParaMeterCollection);
while (SQLReader.Read())
{
MenuInfo objMenu = new MenuInfo();
objMenu.PageID = int.Parse(SQLReader["PageID"].ToString());
objMenu.PageOrder = int.Parse(SQLReader["PageOrder"].ToString());
objMenu.ParentID = int.Parse(SQLReader["ParentID"].ToString());
objMenu.Level = int.Parse(SQLReader["Level"].ToString());
objMenu.LevelPageName = SQLReader["LevelPageName"].ToString();
objMenu.SEOName = SQLReader["SEOName"].ToString();
objMenu.TabPath = SQLReader["TabPath"].ToString();
objMenu.IsVisible = bool.Parse(SQLReader["IsVisible"].ToString());
objMenu.ShowInMenu = bool.Parse(SQLReader["ShowInMenu"].ToString());
objMenu.IconFile = SQLReader["IconFile"].ToString();
lstPages.Add(objMenu);
}
}
catch (Exception e)
{
throw e;
}
finally
{
if (SQLReader != null)
{
SQLReader.Close();
}
}
return(lstPages);
}
public static List <MenuInfo> GetSideMenu(int PortalID, string UserName, int menuID, string CultureCode)
{
List <MenuInfo> lstPages = new List <MenuInfo>();
string StoredProcedureName = "usp_SageMenuGetSideMenu";
List <KeyValuePair <string, object> > ParaMeterCollection = new List <KeyValuePair <string, object> >();
ParaMeterCollection.Add(new KeyValuePair <string, object>("PortalID", PortalID));
ParaMeterCollection.Add(new KeyValuePair <string, object>("UserName", UserName));
ParaMeterCollection.Add(new KeyValuePair <string, object>("MenuID", menuID));
ParaMeterCollection.Add(new KeyValuePair <string, object>("CultureCode", CultureCode));
OracleDataReader SQLReader = null;
try
{
OracleHandler sagesql = new OracleHandler();
SQLReader = sagesql.ExecuteAsDataReader(StoredProcedureName, ParaMeterCollection);
while (SQLReader.Read())
{
MenuInfo objMenu = new MenuInfo();
objMenu.PageID = int.Parse(SQLReader["PageID"].ToString());
objMenu.PageOrder = int.Parse(SQLReader["PageOrder"].ToString());
objMenu.PageName = SQLReader["PageName"].ToString();
objMenu.ParentID = int.Parse(SQLReader["ParentID"].ToString());
objMenu.Level = int.Parse(SQLReader["Level"].ToString());
objMenu.TabPath = SQLReader["TabPath"].ToString();
objMenu.LevelPageName = SQLReader["LevelPageName"].ToString();
lstPages.Add(objMenu);
}
return(lstPages);
}
catch (Exception e)
{
throw e;
}
finally
{
if (SQLReader != null)
{
SQLReader.Close();
}
}
}
public static string LoadProviderSetting(int providerId, AspxCommonInfo aspxCommonObj)
{
List <KeyValuePair <string, object> > parameter = new List <KeyValuePair <string, object> >();
parameter.Add(new KeyValuePair <string, object>("ShippingProviderID", providerId));
parameter.Add(new KeyValuePair <string, object>("StoreID", aspxCommonObj.StoreID));
parameter.Add(new KeyValuePair <string, object>("PortalID", aspxCommonObj.PortalID));
OracleHandler sqlH = new OracleHandler();
var reader = sqlH.ExecuteAsDataReader("usp_Aspx_GetShippingProviderSetting", parameter);
var dataQuery = from d in reader.Cast <System.Data.Common.DbDataRecord>()
select new
{
SettingKey = (String)d["SettingKey"],
SettingValue = (String)d["SettingValue"]
};
var data = dataQuery.ToDictionary(z => z.SettingKey, z => z.SettingValue);
string retString = JSONHelper.Serialize <Dictionary <string, string> >(data);
return(retString);
}
public static List <MenuInfo> GetAdminMenu()
{
List <MenuInfo> lstPages = new List <MenuInfo>();
string StoredProcedureName = "usp_sagemenugetadminmenu";
OracleDataReader SQLReader = null;
try
{
OracleHandler sagesql = new OracleHandler();
SQLReader = sagesql.ExecuteAsDataReader(StoredProcedureName);
while (SQLReader.Read())
{
MenuInfo objMenu = new MenuInfo();
objMenu.PageID = int.Parse(SQLReader["PageID"].ToString());
objMenu.PageOrder = int.Parse(SQLReader["PageOrder"].ToString());
objMenu.ParentID = int.Parse(SQLReader["ParentID"].ToString());
objMenu.Level = int.Parse(SQLReader["Level"].ToString());
objMenu.LevelPageName = SQLReader["LevelPageName"].ToString();
objMenu.SEOName = SQLReader["SEOName"].ToString();
objMenu.TabPath = SQLReader["TabPath"].ToString();
objMenu.IsVisible = bool.Parse(SQLReader["IsVisible"].ToString());
objMenu.ShowInMenu = bool.Parse(SQLReader["ShowInMenu"].ToString());
objMenu.PageName = SQLReader["PageName"].ToString();
lstPages.Add(objMenu);
}
return(lstPages);
}
catch (Exception e)
{
throw e;
}
finally
{
if (SQLReader != null)
{
SQLReader.Close();
}
}
}
public void ExportToCSV(bool includeHeaderAsFirstRow, string separator, string spName, List <KeyValuePair <string, object> > parameter, string filePath)
{
OracleHandler sqlh = new OracleHandler();
OracleDataReader dataReader = sqlh.ExecuteAsDataReader(spName, parameter);
List <string> csvRows = new List <string>();
StringBuilder sb = null;
StreamWriter sw = new StreamWriter(filePath, false);
if (includeHeaderAsFirstRow)
{
sb = new StringBuilder();
for (int index = 0; index < dataReader.FieldCount; index++)
{
if (dataReader.GetName(index) != null)
{
sb.Append(dataReader.GetName(index));
}
if (index < dataReader.FieldCount - 1)
{
sb.Append(separator);
}
}
sw.WriteLine(sb.ToString());
}
while (dataReader.Read())
{
sb = new StringBuilder();
for (int index = 0; index < dataReader.FieldCount - 1; index++)
{
if (!dataReader.IsDBNull(index))
{
string value = dataReader.GetValue(index).ToString();
if (dataReader.GetFieldType(index) == typeof(String))
{
//If double quotes are used in value, ensure each are replaced but 2.
if (value.IndexOf("\"") >= 0)
{
value = value.Replace("\"", "\"\"");
}
//If separtor are is in value, ensure it is put in double quotes.
if (value.IndexOf(separator) >= 0)
{
value = "\"" + value + "\"";
}
}
sb.Append(value);
}
if (index < dataReader.FieldCount - 1)
{
sb.Append(separator);
}
}
if (!dataReader.IsDBNull(dataReader.FieldCount - 1))
{
sb.Append(dataReader.GetValue(dataReader.FieldCount - 1).ToString().Replace(separator, " "));
}
sw.WriteLine(sb.ToString());
}
sw.Close();
dataReader.Close();
sb = null;
DownloadFile(filePath, true);
}
public void ExportTOExcel(string filePath, string spName, List <KeyValuePair <string, object> > parameter, DataTable resultsData)
{
OracleHandler sqlh = new OracleHandler();
OracleDataReader reader = sqlh.ExecuteAsDataReader(spName, parameter); //command.ExecuteReader();
int c = 0;
bool firstTime = true;
//Get the Columns names, types, this will help
//when we need to format the cells in the excel sheet.
DataTable dtSchema = reader.GetSchemaTable();
var listCols = new List <DataColumn>();
if (dtSchema != null)
{
foreach (DataRow drow in dtSchema.Rows)
{
string columnName = Convert.ToString(drow["ColumnName"]).ToString();
var column = new DataColumn(columnName, (Type)(drow["DataType"]));
column.Unique = (bool)drow["IsUnique"];
column.AllowDBNull = (bool)drow["AllowDBNull"];
column.AutoIncrement = (bool)drow["IsAutoIncrement"];
listCols.Add(column);
resultsData.Columns.Add(column);
}
}
while (reader.Read())
{
DataRow dataRow = resultsData.NewRow();
for (int i = 0; i < listCols.Count; i++)
{
dataRow[(listCols[i])] = reader[i];
}
resultsData.Rows.Add(dataRow);
c++;
if (c == rowsPerSheet)
{
c = 0;
ExportToOxml(firstTime, filePath, resultsData);
resultsData.Clear();
firstTime = false;
}
}
if (resultsData.Rows.Count == 0)
{
DataRow dataRow = resultsData.NewRow();
for (int i = 0; i < listCols.Count; i++)
{
string paramType = listCols[i].DataType.FullName.ToLower();
if (listCols[i].AllowDBNull == false)
{
listCols[i].AllowDBNull = true;
}
if (paramType == "system.string")
{
dataRow[(listCols[i])] = "";
}
else
{
dataRow[(listCols[i])] = DBNull.Value;
}
}
resultsData.Rows.Add(dataRow);
}
if (resultsData.Rows.Count > 0)
{
ExportToOxml(firstTime, filePath, resultsData);
resultsData.Clear();
}
reader.Close();
DownloadFile(filePath, false);
}
public static CustomerPersonalInfo GetCustomerDetailsByCustomerID(int CustomerID, AspxCommonInfo aspxCommonObj)
{
try
{
List <KeyValuePair <string, object> > parameter = CommonParmBuilder.GetParamSPUC(aspxCommonObj);
parameter.Add(new KeyValuePair <string, object>("customerID", CustomerID));
OracleHandler sqlH = new OracleHandler();
Oracle.DataAccess.Client.OracleDataReader drCustDetail = sqlH.ExecuteAsDataReader("usp_Aspx_GetCustomerDetailsByCustomerID", parameter);
CustomerPersonalInfo customerPersonalInfo = new CustomerPersonalInfo();
int count = 0;
while (drCustDetail.Read())
{
if (count == 0)
{
DateTime tempDateTime;
int tempInt;
//customerPersonalInfo.AccountCreatedIn = DateTime.TryParse(drCustDetail["CreateDate"].ToString(), out tempDateTime) ? tempDateTime : DateTime.MinValue;
customerPersonalInfo.AccountCreatedOn = DateTime.TryParse(drCustDetail["CreateDate"].ToString(), out tempDateTime) ? tempDateTime.ToString("HH:mm dd/MM/yyyy") : "Error retrieving data";
//customerPersonalInfo.ConfirmedEmail = drCustDetail["Email"].ToString();
customerPersonalInfo.CustomerID = Int32.TryParse(drCustDetail["CustomerID"].ToString(), out tempInt) ? tempInt : 0;
customerPersonalInfo.LastLoggedIn = DateTime.TryParse(drCustDetail["LastLoginDate"].ToString(), out tempDateTime) ? tempDateTime.ToString("HH:mm dd/MM/yyyy") : "Never";
customerPersonalInfo.PortalID = Int32.TryParse(drCustDetail["PortalID"].ToString(), out tempInt) ? tempInt : 0;
customerPersonalInfo.UserName = drCustDetail["UserName"].ToString();
string strLifetimeSales = FixDouble(drCustDetail["LifetimeSales"].ToString());
customerPersonalInfo.LifetimeSales = strLifetimeSales;
string strAverageSales = FixDouble(drCustDetail["AverageSales"].ToString());
customerPersonalInfo.AverageSales = strAverageSales;
if (drCustDetail["DefaultBilling"].ToString() == "True" && drCustDetail["DefaultShipping"].ToString() == "True")
{
//Billing address
customerPersonalInfo.BillingAddress = new AddressInfo();
customerPersonalInfo.BillingAddress.Address1 = drCustDetail["saAddress1"].ToString();
customerPersonalInfo.BillingAddress.Address2 = drCustDetail["saAddress2"].ToString();
customerPersonalInfo.BillingAddress.City = drCustDetail["saCity"].ToString();
customerPersonalInfo.BillingAddress.Company = drCustDetail["saCompany"].ToString();
customerPersonalInfo.BillingAddress.Country = drCustDetail["saCountry"].ToString();
customerPersonalInfo.BillingAddress.FirstName = drCustDetail["saFirstName"].ToString();
customerPersonalInfo.BillingAddress.LastName = drCustDetail["saLastName"].ToString();
customerPersonalInfo.BillingAddress.Zip = drCustDetail["saZip"].ToString();
customerPersonalInfo.BillingAddress.State = drCustDetail["saState"].ToString();
//Shipping address
customerPersonalInfo.ShippingAddress = new AddressInfo();
customerPersonalInfo.ShippingAddress.Address1 = drCustDetail["saAddress1"].ToString();
customerPersonalInfo.ShippingAddress.Address2 = drCustDetail["saAddress2"].ToString();
customerPersonalInfo.ShippingAddress.City = drCustDetail["saCity"].ToString();
customerPersonalInfo.ShippingAddress.Company = drCustDetail["saCompany"].ToString();
customerPersonalInfo.ShippingAddress.Country = drCustDetail["saCountry"].ToString();
customerPersonalInfo.ShippingAddress.FirstName = drCustDetail["saFirstName"].ToString();
customerPersonalInfo.ShippingAddress.LastName = drCustDetail["saLastName"].ToString();
customerPersonalInfo.ShippingAddress.Zip = drCustDetail["saZip"].ToString();
customerPersonalInfo.ShippingAddress.State = drCustDetail["saState"].ToString();
}
else if (drCustDetail["DefaultBilling"].ToString() == "True")
{
//Billing address
customerPersonalInfo.BillingAddress = new AddressInfo();
customerPersonalInfo.BillingAddress.Address1 = drCustDetail["saAddress1"].ToString();
customerPersonalInfo.BillingAddress.Address2 = drCustDetail["saAddress2"].ToString();
customerPersonalInfo.BillingAddress.City = drCustDetail["saCity"].ToString();
customerPersonalInfo.BillingAddress.Company = drCustDetail["saCompany"].ToString();
customerPersonalInfo.BillingAddress.Country = drCustDetail["saCountry"].ToString();
customerPersonalInfo.BillingAddress.FirstName = drCustDetail["saFirstName"].ToString();
customerPersonalInfo.BillingAddress.LastName = drCustDetail["saLastName"].ToString();
customerPersonalInfo.BillingAddress.Zip = drCustDetail["saZip"].ToString();
customerPersonalInfo.BillingAddress.State = drCustDetail["saState"].ToString();
}
else if (drCustDetail["DefaultShipping"].ToString() == "True")
{
//Shipping address
customerPersonalInfo.ShippingAddress = new AddressInfo();
customerPersonalInfo.ShippingAddress.Address1 = drCustDetail["saAddress1"].ToString();
customerPersonalInfo.ShippingAddress.Address2 = drCustDetail["saAddress2"].ToString();
customerPersonalInfo.ShippingAddress.City = drCustDetail["saCity"].ToString();
customerPersonalInfo.ShippingAddress.Company = drCustDetail["saCompany"].ToString();
customerPersonalInfo.ShippingAddress.Country = drCustDetail["saCountry"].ToString();
customerPersonalInfo.ShippingAddress.FirstName = drCustDetail["saFirstName"].ToString();
customerPersonalInfo.ShippingAddress.LastName = drCustDetail["saLastName"].ToString();
customerPersonalInfo.ShippingAddress.Zip = drCustDetail["saZip"].ToString();
customerPersonalInfo.ShippingAddress.State = drCustDetail["saState"].ToString();
}
}
else
{
if (drCustDetail["DefaultBilling"].ToString() == "True")
{
//Billing address
customerPersonalInfo.BillingAddress = new AddressInfo();
customerPersonalInfo.BillingAddress.Address1 = drCustDetail["saAddress1"].ToString();
customerPersonalInfo.BillingAddress.Address2 = drCustDetail["saAddress2"].ToString();
customerPersonalInfo.BillingAddress.City = drCustDetail["saCity"].ToString();
customerPersonalInfo.BillingAddress.Company = drCustDetail["saCompany"].ToString();
customerPersonalInfo.BillingAddress.Country = drCustDetail["saCountry"].ToString();
customerPersonalInfo.BillingAddress.FirstName = drCustDetail["saFirstName"].ToString();
customerPersonalInfo.BillingAddress.LastName = drCustDetail["saLastName"].ToString();
customerPersonalInfo.BillingAddress.Zip = drCustDetail["saZip"].ToString();
customerPersonalInfo.BillingAddress.State = drCustDetail["saState"].ToString();
}
else if (drCustDetail["DefaultShipping"].ToString() == "True")
{
//Shipping address
customerPersonalInfo.ShippingAddress = new AddressInfo();
customerPersonalInfo.ShippingAddress.Address1 = drCustDetail["saAddress1"].ToString();
customerPersonalInfo.ShippingAddress.Address2 = drCustDetail["saAddress2"].ToString();
customerPersonalInfo.ShippingAddress.City = drCustDetail["saCity"].ToString();
customerPersonalInfo.ShippingAddress.Company = drCustDetail["saCompany"].ToString();
customerPersonalInfo.ShippingAddress.Country = drCustDetail["saCountry"].ToString();
customerPersonalInfo.ShippingAddress.FirstName = drCustDetail["saFirstName"].ToString();
customerPersonalInfo.ShippingAddress.LastName = drCustDetail["saLastName"].ToString();
customerPersonalInfo.ShippingAddress.Zip = drCustDetail["saZip"].ToString();
customerPersonalInfo.ShippingAddress.State = drCustDetail["saState"].ToString();
}
}
count++;
}
return(customerPersonalInfo);
}
catch (Exception e)
{
throw e;
}
}