public List <ContractorModel> GetContractors() { List <ContractorModel> model = new List <ContractorModel>(); string commandText = @"SELECT cnt.ID AS CONTRACTOR_ID , cnt.CONTRACTOR_TYPE_ID , cnt.C_NAME , cnt.ID_CONTRACTOR , cnt.S_TOWN_ID , cnt.C_ADDRESS , ISNULL(c_cmp.ACCOUNTABLE_PERSON, '') AS ACCOUNTABLE_PERSON FROM CONTRACTOR cnt LEFT JOIN CONTRACTOR_COMPANY c_cmp ON c_cmp.CONTRACTOR_ID = cnt.ID ORDER BY cnt.C_NAME "; using (DataTable dtContractors = FillDataTable(commandText)) { foreach (DataRow drContractor in dtContractors.Rows) { model.Add(new ContractorModel() { ContractorId = TryParse.ToInt32(drContractor["CONTRACTOR_ID"]), Name = TryParse.ToString(drContractor["C_NAME"]), ContractorTypeId = TryParse.ToInt32(drContractor["CONTRACTOR_TYPE_ID"]), IdContractor = TryParse.ToString(drContractor["ID_CONTRACTOR"]), TownId = TryParse.ToInt32(drContractor["S_TOWN_ID"]), Address = TryParse.ToString(drContractor["C_ADDRESS"]), AccountablePerson = TryParse.ToString(drContractor["ACCOUNTABLE_PERSON"]) }); } } return(model); }
public ContractorModel GetContractor(int id) { ContractorModel model = null; string commandText = @"SELECT cnt.ID AS CONTRACTOR_ID , cnt.CONTRACTOR_TYPE_ID , cnt.C_NAME , cnt.ID_CONTRACTOR , cnt.S_TOWN_ID , cnt.C_ADDRESS , ISNULL(c_cmp.ACCOUNTABLE_PERSON, '') AS ACCOUNTABLE_PERSON FROM CONTRACTOR cnt LEFT JOIN CONTRACTOR_COMPANY c_cmp ON c_cmp.CONTRACTOR_ID = cnt.ID WHERE cnt.ID = " + SQLInt(id); using (DataTable dtContractors = FillDataTable(commandText)) { if (dtContractors.Rows.Count > 0) { DataRow drContractor = dtContractors.Rows[0]; model = new ContractorModel() { ContractorId = TryParse.ToInt32(drContractor["CONTRACTOR_ID"]), Name = TryParse.ToString(drContractor["C_NAME"]), ContractorTypeId = TryParse.ToInt32(drContractor["CONTRACTOR_TYPE_ID"]), IdContractor = TryParse.ToString(drContractor["ID_CONTRACTOR"]), TownId = TryParse.ToInt32(drContractor["S_TOWN_ID"]), Address = TryParse.ToString(drContractor["C_ADDRESS"]), AccountablePerson = TryParse.ToString(drContractor["ACCOUNTABLE_PERSON"]) }; } } return(model); }
public DiscountModel GetDiscount(int id) { DiscountModel model = null; string commandText = @"SELECT d.ID AS DISCOUNT_ID , d.D_NAME , d.VALID_FROM_DATE , d.VALID_TO_DATE , d.DISCOUNT_KIND_ID , dk.DK_NAME , d.D_VALUE FROM DISCOUNT d LEFT JOIN DISCOUNT_KIND dk ON dk.ID = d.DISCOUNT_KIND_ID WHERE d.ID = " + SQLInt(id); using (DataTable dtDiscounts = FillDataTable(commandText)) { if (dtDiscounts.Rows.Count > 0) { DataRow drDiscount = dtDiscounts.Rows[0]; model = new DiscountModel() { DiscountId = TryParse.ToInt32(drDiscount["DISCOUNT_ID"]), Name = TryParse.ToString(drDiscount["D_NAME"]), DiscountKindId = TryParse.ToInt32(drDiscount["DISCOUNT_KIND_ID"]), DiscountKindName = TryParse.ToString(drDiscount["DK_NAME"]), ValidFrom = TryParse.ToDateTime(drDiscount["VALID_FROM_DATE"]), ValidTo = TryParse.ToDateTime(drDiscount["VALID_TO_DATE"]), Value = TryParse.ToDecimal(drDiscount["D_VALUE"]), }; } } return(model); }
public HydraAppsModel GetApplications() { HydraAppsModel model = new HydraAppsModel(); using (SQLiteConnection connection = this.OpenConnection()) { string commandText = @"SELECT ap.uid , ap.app_name , ap.app_url , ap.description , dv.id AS developer_id , dv.dev_name AS developer_name FROM application ap LEFT JOIN developer dv ON dv.id = ap.developer_id ORDER BY ap.app_name "; using (DataTable dtApplications = this.Fill(commandText)) { foreach (DataRow app in dtApplications.Rows) { model.Add(new HydraAppModel() { UId = TryParse.ToGuid(app["uid"]), Name = TryParse.ToString(app["app_name"]), DeveloperId = TryParse.ToInt32(app["developer_id"]), DeveloperName = TryParse.ToString(app["developer_name"]), AppUrl = TryParse.ToString(app["app_url"]), Description = TryParse.ToString(app["description"]) }); } } } return(model); }
/// <summary> /// Добавя карта /// </summary> public void Add(CCardModel model) { string commandText = @"INSERT INTO C_CARD ( C_NUMBER, VALID_FROM_DATE, VALID_TO_DATE, IS_BLOCKED, CONTRACTOR_ID, DISCOUNT_GROUP_ID, OWNER_NAME , CAR_NUMBER, IS_INVOICE, IS_MANUAL_INPUT, C_PASSWORD, C_COMMENT, S_PAYMENT_TYPE_ID ) VALUES (" + SQLString(model.CNumber) + ", " + SQLDateTime(model.ValidFrom) + ", " + SQLDateTime(model.ValidTo) + ", " + SQLInt(model.IsBlocked) + ", " + SQLInt(model.ContractorId) + ", " + SQLInt(model.DiscountGroupId) + ", " + SQLString(model.OwnerName) + ", " + SQLString(model.CarNumber) + ", " + SQLInt(model.IsInvoice) + ", " + SQLInt(model.IsManualInput) + ", " + SQLString(model.Password) + ", " + SQLString(model.Comment) + ", " + SQLInt(model.PaymentTypeId) + @") SELECT @@IDENTITY"; model.CardId = TryParse.ToInt32(ExecuteScalar(commandText)); }
public DiscountGroupModel GetDiscountGroup(int id) { DiscountGroupModel model = null; string commandText = @"SELECT dg.ID AS DISCOUNT_GROUP_ID , dg.DG_NAME , (SELECT ',' + cast(sgi.DISCOUNT_ID AS nvarchar(MAX)) AS [text()] FROM DISCOUNT_GROUP_ITEM sgi WHERE sgi.DISCOUNT_GROUP_ID = dg.ID For XML PATH ('') ) + ',' AS DISCOUNT_LIST FROM DISCOUNT_GROUP dg WHERE dg.ID = " + SQLInt(id); using (DataTable dtDiscounts = FillDataTable(commandText)) { if (dtDiscounts.Rows.Count > 0) { DataRow drDiscount = dtDiscounts.Rows[0]; model = new DiscountGroupModel() { DiscountGroupId = TryParse.ToInt32(drDiscount["DISCOUNT_GROUP_ID"]), Name = TryParse.ToString(drDiscount["DG_NAME"]), DiscountList = TryParse.ToString(drDiscount["DISCOUNT_LIST"]) }; } } return(model); }
public List <DiscountModel> GetDiscounts() { List <DiscountModel> model = new List <DiscountModel>(); string commandText = @"SELECT d.ID AS DISCOUNT_ID , d.D_NAME , d.VALID_FROM_DATE , d.VALID_TO_DATE , d.DISCOUNT_KIND_ID , dk.DK_NAME , d.D_VALUE FROM DISCOUNT d LEFT JOIN DISCOUNT_KIND dk ON dk.ID = d.DISCOUNT_KIND_ID ORDER BY d.D_NAME "; using (DataTable dtDiscounts = FillDataTable(commandText)) { foreach (DataRow drDiscount in dtDiscounts.Rows) { model.Add(new DiscountModel() { DiscountId = TryParse.ToInt32(drDiscount["DISCOUNT_ID"]), Name = TryParse.ToString(drDiscount["D_NAME"]), DiscountKindId = TryParse.ToInt32(drDiscount["DISCOUNT_KIND_ID"]), DiscountKindName = TryParse.ToString(drDiscount["DK_NAME"]), ValidFrom = TryParse.ToDateTime(drDiscount["VALID_FROM_DATE"]), ValidTo = TryParse.ToDateTime(drDiscount["VALID_TO_DATE"]), Value = TryParse.ToDecimal(drDiscount["D_VALUE"]), }); } } return(model); }
public List <CCardModel> GetCards() { List <CCardModel> model = new List <CCardModel>(); string commandText = @"SELECT cc.ID , cc.C_NUMBER , cc.VALID_FROM_DATE , cc.VALID_TO_DATE , cc.IS_BLOCKED , cc.CONTRACTOR_ID FROM C_CARD cc ORDER BY cc.C_NUMBER "; using (DataTable dtCCards = FillDataTable(commandText)) { foreach (DataRow drCCard in dtCCards.Rows) { model.Add(new CCardModel() { CardId = TryParse.ToInt32(drCCard["ID"]), CNumber = TryParse.ToString(drCCard["C_NUMBER"]), ValidFrom = TryParse.ToDateTime(drCCard["VALID_FROM_DATE"]), ValidTo = TryParse.ToDateTime(drCCard["VALID_TO_DATE"]), IsBlocked = (TryParse.ToInt32(drCCard["IS_BLOCKED"]) == 1), ContractorId = TryParse.ToInt32(drCCard["CONTRACTOR_ID"]) }); } } return(model); }
public AppMenuModel GetAppMenu(int siteID, string name) { AppMenuModel appMenu = new AppMenuModel(); using (SQLiteCommand command = new SQLiteCommand(base.SQLConnection)) { // Данни за менюто command.CommandText = @"SELECT mn.ID AS MENU_ID FROM S_MENUS mn WHERE mn.SITE_ID = " + ParameterInt(siteID) + @" AND mn.MENU_NAME = " + ParameterString(name); using (SQLiteDataReader dReader = command.ExecuteReader()) { if (dReader.HasRows && dReader.Read()) { appMenu.MenuID = Int32.Parse(dReader["MENU_ID"].ToString()); } } // Елементи на менуто command.CommandText = @"SELECT mni.ID AS ITEM_ID , mni.MENU_ITEM_TYPE_ID , mni.PARENT_ID , mni.ITEM_TEXT , mni.ITEM_VALUE , mni.LINK_AREA , mni.LINK_CONTROLLER , mni.LINK_ACTION , spg.PAGE_NAME FROM S_MENU_ITEMS mni LEFT JOIN S_PAGES spg ON spg.ID = mni.S_PAGE_ID WHERE mni.MENU_ID = " + ParameterInt(appMenu.MenuID) + @" ORDER BY mni.ORDER_NO "; using (SQLiteDataReader dReader = command.ExecuteReader()) { if (dReader.HasRows) { while (dReader.Read()) { AppMenuItemModel item = new AppMenuItemModel(); item.ItemID = TryParse.ToInt32(dReader["ITEM_ID"]); item.ItemTypeID = TryParse.ToInt32(dReader["MENU_ITEM_TYPE_ID"]); item.ParentID = TryParse.ToInt32(dReader["PARENT_ID"]); item.ItemText = dReader["ITEM_TEXT"].ToString(); item.ItemValue = dReader["ITEM_VALUE"].ToString(); item.LinkArea = dReader["LINK_AREA"].ToString(); item.LinkController = dReader["LINK_CONTROLLER"].ToString(); item.LinkAction = dReader["LINK_ACTION"].ToString(); item.PageName = dReader["PAGE_NAME"].ToString(); appMenu.Add(item); } } } } return(appMenu); }
/// <summary> /// Основни данни за нова страницата /// </summary> public SPageModel GetNewPage(int siteID, int typePage) { SPageModel page = null; using (SQLiteCommand command = new SQLiteCommand(base.SQLConnection)) { command.CommandText = @"SELECT pgt.ID AS PAGE_TYPE_ID , pgt.PAGE_TYPE_NAME , upt.S_PAGE_TYPE_EDITOR_ID FROM S_PAGE_TYPES pgt LEFT JOIN U_PAGE_TYPE_EDITOR upt ON upt.S_PAGE_TYPE_ID = pgt.ID AND upt.U_USER_ID = " + ParameterInt(HttpSession.UserID) + @" WHERE pgt.ID = " + ParameterInt(typePage); using (SQLiteDataReader dReader = command.ExecuteReader()) { if (dReader.HasRows && dReader.Read()) { page = new SPageModel { ID = 0, SiteID = siteID, TypeID = TryParse.ToInt32(dReader["PAGE_TYPE_ID"]), TypeName = dReader["PAGE_TYPE_NAME"].ToString(), TypeEditorID = TryParse.ToInt32(dReader["S_PAGE_TYPE_EDITOR_ID"]) }; } else { page = new SPageModel { ID = 0, SiteID = siteID, TypeID = 1, TypeName = "HTML Page", TypeEditorID = 2 // Tinymce }; } } } // Само при редакция if (page.ID > 0) { // MVC Page if (page.TypeID == 2) { string pagePath = GetPagePath(page.SiteVeiw, page.Name); page.Value = ReaderCSHTML(pagePath); } } return(page); }
public int Add(DiscountGroupModel model) { string commandText = @"INSERT INTO DISCOUNT_GROUP ( DG_NAME ) VALUES (" + SQLString(model.Name) + @") SELECT @@IDENTITY"; model.DiscountGroupId = TryParse.ToInt32(ExecuteScalar(commandText)); return(model.DiscountGroupId); }
/// <summary> /// Основни данни за страницата /// </summary> public SPageModel GetPage(int siteID, int pageID) { SPageModel page = null; using (SQLiteCommand command = new SQLiteCommand(base.SQLConnection)) { command.CommandText = @"SELECT pg.ID , pg.PAGE_NAME , pg.PAGE_TITLE , pg.PAGE_VALUE , pg.PAGE_TYPE_ID , pgt.PAGE_TYPE_NAME , upt.S_PAGE_TYPE_EDITOR_ID , st.VIEW_ID FROM S_PAGES pg INNER JOIN S_SITES st ON st.ID = pg.SITE_ID LEFT JOIN S_PAGE_TYPES pgt ON pgt.ID = pg.PAGE_TYPE_ID LEFT JOIN U_PAGE_TYPE_EDITOR upt ON upt.S_PAGE_TYPE_ID = pg.PAGE_TYPE_ID AND upt.U_USER_ID = " + ParameterInt(HttpSession.UserID) + @" WHERE pg.ID = " + ParameterInt(pageID) + @" AND pg.SITE_ID = " + ParameterInt(siteID); using (SQLiteDataReader dReader = command.ExecuteReader()) { if (dReader.HasRows && dReader.Read()) { page = new SPageModel { ID = pageID, SiteID = siteID, Name = dReader["PAGE_NAME"].ToString(), Title = dReader["PAGE_TITLE"].ToString(), Value = dReader["PAGE_VALUE"].ToString(), TypeID = TryParse.ToInt32(dReader["PAGE_TYPE_ID"]), TypeName = dReader["PAGE_TYPE_NAME"].ToString(), TypeEditorID = TryParse.ToInt32(dReader["S_PAGE_TYPE_EDITOR_ID"]), SiteVeiw = dReader["VIEW_ID"].ToString() }; } } } // MVC Page if (page.TypeID == 2) { string pagePath = GetPagePath(page.SiteVeiw, page.Name); page.Value = ReaderCSHTML(pagePath); } return(page); }
public CCardModel GetCard(int id) { CCardModel model = null; string commandText = @"SELECT cc.ID , cc.C_NUMBER , cc.VALID_FROM_DATE , cc.VALID_TO_DATE , cc.IS_BLOCKED , c.ID AS CONTRACTOR_ID , c.C_NAME AS CONTRACTOR_NAME , cc.DISCOUNT_GROUP_ID , cc.OWNER_NAME , cc.CAR_NUMBER , cc.IS_INVOICE , cc.IS_MANUAL_INPUT , cc.C_COMMENT , cc.S_PAYMENT_TYPE_ID , cc.C_PASSWORD FROM C_CARD cc LEFT JOIN CONTRACTOR c ON c.ID = cc.CONTRACTOR_ID WHERE cc.ID = " + SQLInt(id); using (DataTable dtCCards = FillDataTable(commandText)) { foreach (DataRow drCCard in dtCCards.Rows) { model = new CCardModel() { CardId = TryParse.ToInt32(drCCard["ID"]), CNumber = TryParse.ToString(drCCard["C_NUMBER"]), ValidFrom = TryParse.ToDateTime(drCCard["VALID_FROM_DATE"]), ValidTo = TryParse.ToDateTime(drCCard["VALID_TO_DATE"]), IsBlocked = (TryParse.ToInt32(drCCard["IS_BLOCKED"]) == 1), ContractorId = TryParse.ToInt32(drCCard["CONTRACTOR_ID"]), ContractorName = TryParse.ToString(drCCard["CONTRACTOR_NAME"]), DiscountGroupId = TryParse.ToInt32(drCCard["DISCOUNT_GROUP_ID"], -1), OwnerName = TryParse.ToString(drCCard["OWNER_NAME"]), CarNumber = TryParse.ToString(drCCard["CAR_NUMBER"]), IsInvoice = (TryParse.ToInt32(drCCard["IS_INVOICE"]) == 1), IsManualInput = (TryParse.ToInt32(drCCard["IS_MANUAL_INPUT"]) == 1), Password = TryParse.ToString(drCCard["C_PASSWORD"]), Comment = TryParse.ToString(drCCard["C_COMMENT"]), PaymentTypeId = TryParse.ToInt32(drCCard["S_PAYMENT_TYPE_ID"]) }; } } return(model); }
/// <summary> /// Добавя контрагент /// </summary> public void Add(ContractorModel model) { // Добавя контрагент string commandText = @"INSERT INTO CONTRACTOR ( CONTRACTOR_TYPE_ID, C_NAME, ID_CONTRACTOR, S_TOWN_ID, C_ADDRESS ) VALUES (" + SQLInt(model.ContractorTypeId) + ", " + SQLString(model.Name) + ", " + SQLString(model.IdContractor) + ", " + SQLInt(model.TownId) + ", " + SQLString(model.Address) + @") SELECT @@IDENTITY"; model.ContractorId = TryParse.ToInt32(ExecuteScalar(commandText)); // Записва разширена информация за контаргента SaveExtension(model); }
public List <DiscountGroupModel> GetDiscountGroups() { List <DiscountGroupModel> model = new List <DiscountGroupModel>(); string commandText = @"SELECT dg.ID AS DISCOUNT_GROUP_ID , dg.DG_NAME FROM DISCOUNT_GROUP dg ORDER BY dg.DG_NAME "; using (DataTable dtDiscounts = FillDataTable(commandText)) { foreach (DataRow drDiscount in dtDiscounts.Rows) { model.Add(new DiscountGroupModel() { DiscountGroupId = TryParse.ToInt32(drDiscount["DISCOUNT_GROUP_ID"]), Name = TryParse.ToString(drDiscount["DG_NAME"]), }); } } return(model); }
public List <DiscountKindModel> GetDiscountKinds() { List <DiscountKindModel> model = new List <DiscountKindModel>(); string commandText = @"SELECT dk.ID , dk.DK_NAME FROM DISCOUNT_KIND dk ORDER BY dk.DK_NAME "; using (DataTable dtDiscounts = FillDataTable(commandText)) { foreach (DataRow drDiscount in dtDiscounts.Rows) { model.Add(new DiscountKindModel() { DiscountKindId = TryParse.ToInt32(drDiscount["ID"]), Name = TryParse.ToString(drDiscount["DK_NAME"]) }); } } return(model); }
public AccountModel Login(string userName, string passWord) { AccountModel model = null; string commandText = @"SELECT a.ID , a.A_EMAIL , a.FIRST_NAME , a.LAST_NAME , a.LANGUAGE_CODE FROM S_ACCOUNT a WHERE a.A_USERNAME = "******" AND a.A_PASSWORD = "******" AND a.IS_ACTIVE = 1 "; using (DataTable dtAccount = FillDataTable(commandText)) { // Има открит логин if (dtAccount.Rows.Count == 1) { DataRow drAccount = dtAccount.Rows[0]; model = new AccountModel() { ActionId = TryParse.ToInt32(drAccount["ID"]), FirstName = TryParse.ToString(drAccount["FIRST_NAME"]), LastName = TryParse.ToString(drAccount["LAST_NAME"]), LanguageCode = TryParse.ToString(drAccount["LANGUAGE_CODE"]) }; // Отбелязва логването commandText = @"UPDATE S_ACCOUNT SET LOGIN_SESSION_ID = " + SQLString(xSession.SessionId) + @" , LOGIN_LAST_DATE = GETDATE() WHERE ID = " + SQLInt(model.ActionId); ExecuteNonQuery(commandText); } } return(model); }
/// <summary> /// Списък на страниците /// </summary> /// <param name="siteID"></param> /// <returns></returns> public List <SPageModel> GetPages(int siteID) { List <SPageModel> pages = new List <SPageModel>(); using (SQLiteCommand command = new SQLiteCommand(base.SQLConnection)) { command.CommandText = @"SELECT pg.ID , pg.PAGE_NAME , pg.PAGE_TITLE , pg.PAGE_TYPE_ID , pgt.PAGE_TYPE_NAME FROM S_PAGES pg LEFT JOIN S_PAGE_TYPES pgt ON pgt.ID = pg.PAGE_TYPE_ID WHERE pg.SITE_ID = " + ParameterInt(siteID); using (SQLiteDataAdapter dAdapter = new SQLiteDataAdapter(command)) { using (DataTable dtPages = new DataTable()) { dAdapter.Fill(dtPages); foreach (DataRow dr in dtPages.Rows) { pages.Add(new SPageModel { ID = TryParse.ToInt32(dr["ID"]), Name = dr["PAGE_NAME"].ToString(), Title = dr["PAGE_TITLE"].ToString(), TypeID = TryParse.ToInt32(dr["PAGE_TYPE_ID"]), TypeName = dr["PAGE_TYPE_NAME"].ToString() }); } } } } return(pages); }
public List <NewsModel> GetNews(int category = 0) { List <NewsModel> news = new List <NewsModel>(); using (SqlCommand command = new SqlCommand()) { command.Connection = base.SQLConnection; command.CommandText = @"SELECT ns.ID , ns.N_GUID , ns.N_TITLE , ns.N_DESCRIPTION , ns.N_BODY , ns.N_PUB_DATE , ns.N_CATEGORY_ID , ns.N_SAIT_ID , ns.N_LINK FROM NEWS ns"; using (SqlDataReader dReader = command.ExecuteReader()) { if (dReader.HasRows) { while (dReader.Read()) { NewsModel item = new NewsModel(); item.ID = TryParse.ToInt32(dReader["ID"]); item.Title = dReader["N_TITLE"].ToString(); item.Description = dReader["N_DESCRIPTION"].ToString(); item.Body = dReader["N_BODY"].ToString(); news.Add(item); } } } } return(news); }
private static Int32 GetSessionInt32(string name) { object value = HttpContext.Current.Session[name]; return(TryParse.ToInt32(value, 0)); }
public AccountModel GetAccount(AccountLoginModel login) { int userId = 0; string userPassword = ""; using (SQLiteCommand command = new SQLiteCommand()) { command.Connection = base.SQLConnection; command.CommandText = @"SELECT u.ID , u.PASSWORD FROM U_USERS u WHERE u.LOGIN_ID = " + ParameterString(login.LoginID) + @" AND u.IS_ACTIVE = 1 "; using (SQLiteDataReader dReader = command.ExecuteReader()) { if (dReader.HasRows) { if (dReader.Read()) { userId = TryParse.ToInt32(dReader["ID"]); userPassword = dReader["PASSWORD"].ToString(); } } } } AccountModel account = null; // Проверява паролата if (userPassword != login.Password) { return(null); } else { account = new AccountModel(); account.ID = userId; account.LoginID = login.LoginID; } // Взема ролите на потребителя using (SQLiteCommand command = new SQLiteCommand()) { command.Connection = base.SQLConnection; command.CommandText = @"SELECT r.ID , r.ROLE_NAME FROM U_USER_ROLES ur LEFT JOIN U_ROLES r ON r.ID = ur.U_ROLE_ID WHERE ur.U_USER_ID = " + ParameterInt(userId); using (SQLiteDataReader dReader = command.ExecuteReader()) { if (dReader.HasRows) { while (dReader.Read()) { if (account.Roles == null) { account.Roles = dReader["ROLE_NAME"].ToString(); } else { account.Roles += ";" + dReader["ROLE_NAME"].ToString(); } } } } } return(account); }