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);
}
