private void btnShareHeader_Click(object sender, EventArgs e) { DBFunction dBFunction = new DBFunction(); Dictionary <string, string> ldict = new Dictionary <string, string>(); ldict.Add("SHARES_NAME", txtShareName.Text); ldict.Add("SHARE_TYPE_CODE_ID", "4"); if (rdDelivery.Checked) { ldict.Add("SHARE_TYPE_CODE_VALUE", "DELV"); } else if (rdOption.Checked) { ldict.Add("SHARE_TYPE_CODE_VALUE", "OPTN"); } int id = dBFunction.InsertIntoTable(DBName, "SHARES_TRACKER_HEADER", ldict); if (id != 0) { lblBuyId.Text = Convert.ToString(id); lblSellId.Text = Convert.ToString(id); lblChargeId.Text = Convert.ToString(id); } LoadForm(); }
public void Test_03_ScriptOfSelectCustomersWithOrdersOver500() { DBSelectQuery orderValue = DBQuery.Select() .Sum(DBField.Field("UnitPrice") * DBField.Field("Quantity") * (DBConst.Const(1.0) - DBField.Field("Discount"))).As("OrderValue") .And(DBFunction.IsNull("OrderID", DBConst.Const("No ID"))) .Field("O", "customerID") .From("Orders").As("O") .InnerJoin("Order Details").As("OD") .On("OD", "OrderID", Compare.Equals, "O", "OrderID") .GroupBy("O", "CustomerID"); DBQuery q = DBQuery.SelectTopN(10) .Field("CUST", "CompanyName").As("Company Name") .Field("CUST", "CustomerID").As("Customer ID") .And(DBFunction.IsNull("CUST", "ContactName", DBConst.Const("No Contact"))).As("Contact") .Sum("ORD", "OrderValue").As("Total Value") .From("Customers").As("CUST") .InnerJoin(orderValue).As("ORD") .On("ORD", "CustomerID", Compare.Equals, "CUST", "CustomerID") .WhereField("ORD", "OrderValue", Compare.GreaterThanEqual, DBConst.Const(500.0)) .GroupBy("CUST", "CompanyName").And("CUST", "CustomerID") .OrderBy("Total Value", Order.Descending); this.OutputSql(q, "SelectCustomersWithOrdersOver500"); this.OutputXML(q, "SelectCustomersWithOrdersOver500"); }
private void InsertIntoAssetHeader(int UserId) { Dictionary <string, string> ldict = new Dictionary <string, string>(); ldict.Add(TableEnum.enmAssetHeader.ASSET_HEADER_ID.ToString(), UserId.ToString()); DBFunction.InsertIntoTable(Constant.Common.DATABASE_NAME, TableEnum.enmTableName.ASSET_HEADER.ToString(), ldict); }
public ActionResult <Paging <ProductSaleByDayNSEntity> > GetJoinPaging2() { var repository = GetRepository(); var query = QueryFactory.Create <ProductSaleByDayNSEntity>(m => DBFunction.Function <DateTime>("ISNULL", m.UpdateDate, DateTime.Now) > new DateTime(2019, 6, 26)); var jq = query.InnerJoin(QueryFactory.Create <ShopEntity>(), m => m.ShopID, m => m.SysNo, (x, y) => x); jq.And(m => m.DataSource.Contains("")); jq.OrderByDescing(m => m.Sales + 1); jq.OrderBy(m => m.ProductName + m.OutProductID); jq.StarSize = 10; jq.Rows = 5; var res = jq.Select(m => m); var paging = repository.GetPaging(res); //也可以下面这样返回dto.第二个参数表示第一个表是否要查询所有列. var res2 = jq.Select(m => new PSDto { ShopName = m.ProductName }, true); var paging2 = repository.GetPaging(res2); var res3 = jq.Select(m => m.AveragePrice); var paging3 = repository.GetPaging(res3); var count = paging.TotalCount; var lst = paging.ToList();//或者paging.Items return(paging); }
private void TestJq2() { var repository = GetRepository(); Expression <Func <ProductSaleByDayNSEntity, bool> > where = m => !string.IsNullOrEmpty(m.ShopID) && m.SysNo > 0; var query = QueryFactory.Create(where); var jq = query.InnerJoin(QueryFactory.Create <ShopEntity>(), m => m.ShopID, m => m.SysNo, (x, y) => new { Sale = x, Shop = y }); var r = jq.Select(m => new { TName = DBFunction.Function <string>("CONCAT", m.Sale.DataSource, ",", m.Sale.ProductName), m.Sale.ShopID, ShopID2 = m.Shop.SysNo }); var rlst = repository.GetList(r); Assert.True(rlst.Count > 10); Assert.AreEqual(rlst[0].ShopID, rlst[0].ShopID2); Assert.False(string.IsNullOrEmpty(rlst[0].TName)); Assert.True(rlst.Any(m => m.TName.Contains("商品"))); Assert.True(rlst.Any(m => m.TName.Contains("来源"))); jq.OrderBy(m => m.Sale.StatisticalDate); jq.StarSize = 10; jq.Rows = 5; r = jq.Select(m => new { TName = DBFunction.Function <string>("CONCAT", m.Sale.DataSource, ",", m.Sale.ProductName), m.Sale.ShopID, ShopID2 = m.Shop.SysNo }); var plst = repository.GetPaging(r); Assert.True(plst.Items.Count() == 5); Assert.True(plst.TotalCount > 20); }
/// <inheritdoc /> public async Task <Page <Workflow> > ListWorkflows(WorkflowFilter filter = null) { filter ??= new WorkflowFilter(); LoadOperation <Workflow> operation = database.Load <Workflow>(); PredicateExpression <Workflow> predicate = null; if (filter.Name?.Length > 0) { if (filter.Name.Length == 1) { predicate &= w => w.Name == filter.Name[0]; } else { predicate &= w => w.Name.In(filter.Name); } } if (!string.IsNullOrEmpty(filter.Query)) { string q = filter.Query.TranslateWildcards(); predicate &= w => w.Name.Like(q); } return(Page <Workflow> .Create( (await operation.Where(predicate?.Content).ApplyFilter(filter).ExecuteEntitiesAsync()).ToArray(), await database.Load <Workflow>(w => DBFunction.Count()).Where(predicate?.Content).ExecuteScalarAsync <long>(), filter.Continue )); }
public ActionResult <Paging <ProductSaleByDayNSEntity> > Select() { var repository = GetRepository(); var query = QueryFactory.Create <ProductSaleByDayNSEntity>(m => DBFunction.Function <DateTime>("ISNULL", m.UpdateDate, DateTime.Now) > new DateTime(2019, 6, 26)); query.OrderBy(m => m.Sales + 1 + m.NumberOfSales); query.OrderByDescing(m => m.ShopID); query.StarSize = 10; query.Rows = 4; //var res1 = query.Select(m => m); //var lst1 = repository.GetPaging(res1); var res2 = query.Select(m => m.SysNo); //var lst2 = repository.GetPaging(res2); //var res3 = query.Select(m => m.Sales + m.AveragePrice + 2); //var lst3 = repository.GetPaging(res3); var q = QueryFactory.Create <ProductSaleByDayNSEntity>(); q.And(m => m.Sales > 0); q.And(m => res2.Contains(m.SysNo)); var lst4 = repository.GetPaging(q); return(lst4); }
/// <summary> /// /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnUpdateHeader_Click(object sender, EventArgs e) { string lstrQuery = GlobalFunction.GetQueryById(Constant.Query.UPDATE_SHARE_HEADER); lstrQuery = string.Format(lstrQuery, txtShareNameDelivery.Text, ((KeyValuePair <string, string>)cmbSeviceDelivery.SelectedItem).Key, iintUpdateId); DBFunction.UpdateTable(Constant.Common.DATABASE_NAME, lstrQuery); LoadForm(); }
/// <summary> /// /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnUpdatePay_Click(object sender, EventArgs e) { string strdate = dateTransactiondate.Value.ToString(Constant.Common.DATE_FORMAT) + Constant.Common.SPACE + dateTransactiondate.Value.ToLongTimeString(); string str = "Update SHARES_PAYIN_PAYOUT SET SHARES_EXTRA_CHARGES_AMT = " + Convert.ToDecimal(txtExtraChargeAmt.Text) + ", SHARES_EXTRA_CHARGES_DESCRIPTION = '" + txtExtraChargeName.Text + "', SHARES_CHARGES_DATE = '" + strdate + "' Where SHARES_TRANSACTION_ID = " + iintUpdateId; DBFunction.UpdateTable(Constant.Common.DATABASE_NAME, str); Sum(); }
public ActionResult Detail2(string ID) { DBFunction DB = new DBFunction(); ParentsDetail model = new ParentsDetail(); model.ParentsIndexDetail = DB.ParentsGetTutorData(ID); return(View(model)); }
public ActionResult Index(string subject = null, string grade = null, string gender = null) { DBFunction DB = new DBFunction(); HomeIndex model = new HomeIndex(); model.QuestionnaireDatas = DB.GetQuestionnaireData(subject, grade, gender); return(View(model)); }
public ActionResult Detail(string ID) { DBFunction DB = new DBFunction(); HomeDetail model = new HomeDetail(); model.QuestionnaireData = DB.GetQuestionnaireData(ID); return(View(model)); }
public ActionResult RegisterView(string act) { DBFunction DB = new DBFunction(); string LoginName = Session["LoginName"].ToString(); Register register = DB.GetRegister(LoginName); ViewBag.act = act; return(View(register)); }
// GET: Parents public ActionResult Index(string gender = null, string school = null, string subject = null) { ParentsIndex model = new ParentsIndex(); DBFunction DB = new DBFunction(); model.ParentsIndexList = DB.ParentsGetTutorData(gender, school, subject); return(View(model)); }
private void Assets_Load(object sender, EventArgs e) { string lstrQuery = "SELECT ASSET_HEADER_ID FROM ASSET_HEADER WHERE USER_LOGIN_ID = " + Login.UserId; lblFinalAssetValue.Text = string.Empty; iintAssetHdrId = Convert.ToInt32(DBFunction.FetchScalarFromDatabase(Constant.Common.DATABASE_NAME, lstrQuery)); GetTotalAssetAmt(); this.aSSET_PROCEDURETableAdapter1.Fill(this.assets1.ASSET_PROCEDURE, Login.UserId); }
private void btnCalculate_Click(object sender, EventArgs e) { string lstrAmtQuery = "SELECT SUM(ASSET_AMOUNT) FROM ASSET_DETAIL WHERE ASSET_HEADER_ID = " + iintAssetHdrId; string lstrAmt = DBFunction.FetchScalarFromDatabase(Constant.Common.DATABASE_NAME, lstrAmtQuery); string lstrQuery = "Update ASSET_HEADER Set ASSET_TOTAL_AMOUNT = " + lstrAmt + " Where ASSET_HEADER_ID =" + iintAssetHdrId; DBFunction.FetchScalarFromDatabase(Constant.Common.DATABASE_NAME, lstrQuery); GetTotalAssetAmt(); }
/// <inheritdoc /> public async Task <Page <int> > ListRevisions(string type, long id, ListFilter filter = null) { filter ??= new ListFilter(); return(Page <int> .Create( (await database.Load <ArchivedObject>(o => o.Revision).Where(o => o.Type == type && o.ObjectId == id).ApplyFilter(filter).ExecuteSetAsync <int>()).ToArray(), await database.Load <ArchivedObject>(o => DBFunction.Count()).Where(o => o.Type == type && o.ObjectId == id).ExecuteScalarAsync <long>(), filter.Continue )); }
private void btnShareExtraCharge_Click(object sender, EventArgs e) { DBFunction dBFunction = new DBFunction(); Dictionary <string, string> ldict = new Dictionary <string, string>(); ldict.Add("SHARES_EXTRA_CHARGES_AMT", txtExtraChargeAmt.Text); ldict.Add("SHARES_EXTRA_CHARGES_DESCRIPTION", txtExtraChargeName.Text); ldict.Add("SHARES_CHARGES_DATE", dateExtraCharge.Text); dBFunction.InsertIntoTable(DBName, "SHARES_TRACKER_EXTRA_CHARGES", ldict); }
/// <summary> /// /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnUpdateMaint_Click(object sender, EventArgs e) { string strdate = dateExtraCharge.Value.ToString(Constant.Common.DATE_FORMAT) + Constant.Common.SPACE + dateExtraCharge.Value.ToLongTimeString(); string bit = CheckIsConsider.Checked ? Constant.Common.Bit_Value.BIT_1 : Constant.Common.Bit_Value.BIT_0; string str = "Update [SHARES_TRACKER_EXTRA_CHARGES] SET SHARES_EXTRA_CHARGES_AMT = " + Convert.ToDecimal(txtExtraChargeAmt.Text) + ", SHARES_EXTRA_CHARGES_DESCRIPTION = '" + txtExtraChargeName.Text + "', SHARES_CHARGES_DATE = '" + strdate + "', Is_Consider = " + bit + " where SHARES_TRACKER_EXTRA_CHARGES_ID = " + iintUpdateId; DBFunction.UpdateTable(Constant.Common.DATABASE_NAME, str); LoadForm(); Sum(); }
/// <summary> /// /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void lnkLogout_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e) { this.Hide(); Login form1 = new Login(); form1.Show(); string str = GlobalFunction.GetQueryById(Constant.Query.LOG_OFF_QUERY); DBFunction.UpdateTable(Constant.Common.DATABASE_NAME, str); }
private void LoadCategory() { DBFunction lDBFunction = new DBFunction(); string lstrQuery = "Select Expenses_Category_Name from Expenses_Category where Expenses_Category_Table_Value = 'HUWF'"; DataTable ldtbTable = lDBFunction.FetchDataFromDatabase("Bharat", lstrQuery); foreach (DataRow dr in ldtbTable.Rows) { cmbcategory.Items.Add(Convert.ToString(dr["Expenses_Category_Name"])); } }
public string checkRecommendCode(string Code) { string result = ""; DBFunction DB = new DBFunction(); if (!DB.checkRecommendCode(Code)) { result = "該推薦碼不存在"; } return(result); }
private string GenerateAll() { string schema = CBSchemas.SelectedItem.ToString(); string table = CBTables.SelectedItem.ToString(); this.WriteLog(string.Format("Hai selezionato {0}.{1} !", schema, table)); this.WriteLog("Sto acquisendo le colonne dalla tabella selezionata..."); List <TableColumns> columns = DBFunction.GetColumnsFromTable(this.context381, table); this.WriteLog(string.Format("Trovate {0} colonne", columns.Count)); //generate entity DirectoryInfo dir = Directory.CreateDirectory(Path.Combine(schema, table)); File.WriteAllText(Path.Combine(dir.FullName, "Entity.cs"), this.GenerateEntity(columns, table, schema)); this.WriteLog("Classe dell'Entity Framework generata con successo!!!"); //generate controller string controller = File.ReadAllText(@"Resource\Controller.cs"); File.WriteAllText(Path.Combine(dir.FullName, string.Format("{0}.Controller.cs", this.GetClassName(table))), this.GenerateController(columns, table, schema, controller)); this.WriteLog("Controller generato con successo!!!"); //generate service string service = File.ReadAllText(@"Resource\Service.cs"); File.WriteAllText(Path.Combine(dir.FullName, string.Format("{0}.cs", this.GetClassName(table))), this.GenerateService(columns, table, schema, service)); this.WriteLog("Service generato con successo!!!"); //generate DTO string dto = File.ReadAllText(@"Resource\DTO.cs"); File.WriteAllText(Path.Combine(dir.FullName, string.Format("{0}DTO.cs", this.GetClassName(table))), this.GenerateDTO(columns, table, schema, dto)); this.WriteLog("DTO generato con successo!!!"); //generate assembler string assembler = File.ReadAllText(@"Resource\Assembler.cs"); File.WriteAllText(Path.Combine(dir.FullName, string.Format("{0}.{1}.Assembler.cs", this.TranslateSchema(schema, true), this.GetClassName(table))), this.GenerateAssembler(columns, table, schema, assembler)); this.WriteLog("Assembler generato con successo!!!"); return(dir.FullName); }
private void btnShareCharge_Click(object sender, EventArgs e) { DBFunction dBFunction = new DBFunction(); Dictionary <string, string> ldict = new Dictionary <string, string>(); ldict.Add("SHARES_TRACKER_HEADER_ID", lblChargeId.Text); ldict.Add("SHARES_CHARGES_AMT", txtShareChargesAmt.Text); ldict.Add("SHARES_CHARGES_DESCRIPTION", txtShareChargesDesc.Text); dBFunction.InsertIntoTable(DBName, "SHARES_TRACKER_CHARGES", ldict); LoadForm(); }
/// <summary> /// /// </summary> /// <param name="astrType"></param> /// <param name="astrAmt"></param> /// <param name="adttime"></param> /// <param name="aintCategoryId"></param> private void AddAmount(string astrType, string astrAmt, string adttime, string aintCategoryId) { Dictionary <string, string> ldict = new Dictionary <string, string>(); ldict.Add(TableEnum.enmHusband_Wife.HUSBAND_WIFE_TYPE_VALUE.ToString(), astrType); ldict.Add(TableEnum.enmHusband_Wife.HUSBAND_WIFE_AMT.ToString(), astrAmt); ldict.Add(TableEnum.enmHusband_Wife.HUSBAND_WIFE_DATE.ToString(), adttime); ldict.Add(TableEnum.enmHusband_Wife.EXPENSES_CATEGORY_ID.ToString(), aintCategoryId); ldict.Add(TableEnum.enmHusband_Wife.HUSBAND_WIFE_TYPE_ID.ToString(), Constant.Common.CodeId.CODE_ID_2); DBFunction.InsertIntoTable(Constant.Common.DATABASE_NAME, TableEnum.enmTableName.HUSBAND_WIFE.ToString(), ldict); }
private void btnShareSell_Click(object sender, EventArgs e) { DBFunction dBFunction = new DBFunction(); Dictionary <string, string> ldict = new Dictionary <string, string>(); ldict.Add("SHARES_TRACKER_HEADER_ID", lblSellId.Text); ldict.Add("SHARES_PRICE_AMT", txtSellPrice.Text); ldict.Add("SHARES_QUANTITY", txtSellQty.Text); ldict.Add("SHARES_SELL_DATE", dateSell.Text); dBFunction.InsertIntoTable(DBName, "SHARES_TRACKER_SELL", ldict); LoadForm(); }
private void btnAdd_Click(object sender, EventArgs e) { DBFunction dBFunction = new DBFunction(); Dictionary <string, string> ldict = new Dictionary <string, string>(); ldict.Add("SHARES_TRACKER_HEADER_ID", lblBuyId.Text); ldict.Add("SHARES_PRICE_AMT", txtBuyPrice.Text); ldict.Add("SHARES_QUANTITY", txtBuyQty.Text); ldict.Add("SHARES_BUY_DATE", dateBuy.Text); dBFunction.InsertIntoTable(DBName, "SHARES_TRACKER_BUY", ldict); LoadForm(); }
private void btnrecycling_Click(object sender, EventArgs e) { if (lsttrash.SelectedItems.Count <= 0) { MessageBox.Show("צריך לבחור שורה בטבלה"); return; } DialogResult result = MessageBox.Show("האם אתה בטוח שברצונך לשחזר?", "שחזור הכרטיס", MessageBoxButtons.OKCancel, MessageBoxIcon.Question); if (result == DialogResult.Cancel) { return; } int id = int.Parse(lsttrash.SelectedItems[0].SubItems[5].Text); string sql = "update peoples set show="; SqlDataReader reader; People p = new People(); p.ID = id; reader = People.ReadById(p.ID); while (reader.Read()) { PeopleManipulations.ReaderToPeople(ref p, ref reader, true, true); } reader.Close(); DBFunction.CloseConnections(); if (p.Chadchan.Length > 0) { sql += "5"; } else { sql += "0"; } sql += " where ID=" + id; if (DBFunction.Execute(sql)) { MessageBox.Show("השחזור בוצע בהצלחה"); } else { MessageBox.Show("", "קרתה שגיאה בעת השחזור. נא נסו שוב במועד מיוחר יותר", MessageBoxButtons.OK, MessageBoxIcon.Error); } LoadTab10(); ShiduchActivity.insertActivity( new ShiduchActivity() { Action = (int)ShiduchActivity.ActionType.recycling, Date = DateTime.Now, PeopleId = id, UserId = GLOBALVARS.MyUser.ID, }); }
/// <inheritdoc /> public async Task <Page <Script> > ListScripts(ListFilter filter = null) { filter ??= new ListFilter(); LoadOperation <Script> operation = database.Load <Script>(); operation.ApplyFilter(filter); return(Page <Script> .Create( (await operation.ExecuteEntitiesAsync()).ToArray(), await database.Load <Script>(s => DBFunction.Count()).ExecuteScalarAsync <long>(), filter.Continue )); }
private void AddAmount(string astrType, string astrAmt, string adttime, string aintCategoryId) { DBFunction ldBFunction = new DBFunction(); Dictionary <string, string> ldict = new Dictionary <string, string>(); ldict.Add("Husband_Wife_Type", astrType); ldict.Add("Husband_Wife_Amt", astrAmt); ldict.Add("Husband_Wife_Date", adttime); ldict.Add("EXPENSES_CATEGORY_ID", aintCategoryId); ldict.Add("HUSBAND_WIFE_TYPE_ID", "1"); ldBFunction.InsertIntoTable("Bharat", "Husband_Wife", ldict); }