public FrmNewCaseHis(bool isAdd, Int64 base_id, Int64 reg_id) { InitializeComponent(); //string sPwd = Des.Decrypt(ClsFrmMng.KEY, ConfigurationManager.AppSettings["DbPwd"]); db = new Database("XE"); _reg_id = reg_id; _isAdd = isAdd; _baseID = base_id; //ConditionValidationRule ruleNoEmpty = new ConditionValidationRule(); //ruleNoEmpty.ConditionOperator = ConditionOperator.IsNotBlank; //ruleNoEmpty.ErrorText = "该项不能为空。"; //dxValidationProvider1.SetValidationRule(MEMOMemoEdit, ruleNoEmpty); //dxValidationProvider1.SetValidationRule(DOSE_MEMOMemoEdit, ruleNoEmpty); caseHis.REG_ID = _reg_id; caseHis.BASE_INFO_ID = _baseID; caseHis.OPERATOR = ClsFrmMng.WorkerID; cASEHISTORYBindingSource.DataSource = caseHis; lookUpEdit1.Properties.DataSource = db.Fetch<VALUE_CODE>("where GROUPNAME = @0", new object[] { 11 }); lookUpEdit1.Properties.DisplayMember = "DSP_MEMBER"; lookUpEdit1.Properties.ValueMember = "VALUE_MEMBER"; vALUECODEBindingSource.DataSource = db.Fetch<VALUE_CODE>("where GROUPNAME = @0", new object[] { 181 }); }
/// <summary> /// /// </summary> /// <param name="base_id"></param> /// <param name="reg_id"></param> /// <param name="isTemp">0:长期, 1:临时</param> public FrmNewDav(Int64 base_id, Int64 reg_id, int isTemp) { InitializeComponent(); if (isTemp == 0) this.Text = "新建长期医嘱"; else this.Text = "新建临时医嘱"; //string sPwd = Des.Decrypt(ClsFrmMng.KEY, ConfigurationManager.AppSettings["DbPwd"]); db = new Database("XE"); _regID = reg_id; _baseID = base_id; _isTemp = isTemp; docAdv.REG_ID = _regID; docAdv.BASE_INFO_ID = _baseID; docAdv.OPERATOR = ClsFrmMng.WorkerID; docAdv.ADVICE_TYPE = _isTemp; docAdv.IS_DEL = 0; dOCADVICEBindingSource.DataSource = docAdv; vALUECODEBindingSource.DataSource = db.Fetch<VALUE_CODE>(""); M_UNITLookUpEdit.Properties.DataSource = db.Fetch<VALUE_CODE>("where GROUPNAME = @0", new object[] { 14 }); M_UNITLookUpEdit.Properties.DisplayMember = "DSP_MEMBER"; M_UNITLookUpEdit.Properties.ValueMember = "VALUE_MEMBER"; M_ACTIONLookUpEdit.Properties.DataSource = db.Fetch<VALUE_CODE>("where GROUPNAME = @0", new object[] { 13 }); M_ACTIONLookUpEdit.Properties.DisplayMember = "DSP_MEMBER"; M_ACTIONLookUpEdit.Properties.ValueMember = "VALUE_MEMBER"; }
public FrmNewRegist() { InitializeComponent(); //string sPwd = Des.Decrypt(ClsFrmMng.KEY, ConfigurationManager.AppSettings["DbPwd"]); db = new Database("XE"); //db.OpenSharedConnection(); lstOutpatientCategory = db.Fetch<OUTPATIENT_CATEGORY>(""); OUTPATIENT_CATEGORYTextEdit.Properties.TextEditStyle = DevExpress.XtraEditors.Controls.TextEditStyles.DisableTextEditor; OUTPATIENT_CATEGORYTextEdit.Properties.Columns.Add(new DevExpress.XtraEditors.Controls.LookUpColumnInfo("CATEGORY_ID")); OUTPATIENT_CATEGORYTextEdit.Properties.Columns.Add(new DevExpress.XtraEditors.Controls.LookUpColumnInfo("CATEGORY_NAME")); OUTPATIENT_CATEGORYTextEdit.Properties.DataSource = lstOutpatientCategory; OUTPATIENT_CATEGORYTextEdit.Properties.ValueMember = "CATEGORY_ID"; // 对应ID OUTPATIENT_CATEGORYTextEdit.Properties.DisplayMember = "CATEGORY_NAME"; // 显示内容 patientReg.STATUS = 0; patientReg.OPERATOR = ClsFrmMng.WorkerID; pATIENTREGISTBindingSource.DataSource = patientReg; //db.CloseSharedConnection(); ConditionValidationRule ruleNoEmpty = new ConditionValidationRule(); ruleNoEmpty.ConditionOperator = ConditionOperator.IsNotBlank; ruleNoEmpty.ErrorText = "该项不能为空。"; dxValidationProvider1.SetValidationRule(NAMETextEdit, ruleNoEmpty); dxValidationProvider1.SetValidationRule(OUTPATIENT_CATEGORYTextEdit, ruleNoEmpty); pATIENTBASEINFOBindingSource.DataSource = db.Fetch<PATIENT_BASEINFO>(""); NAMETextEdit.Properties.EditValueChanged += Properties_EditValueChanged; }
private void GetItem(string SuppBatchGuid) { List<V_ERP_SuppOrder> items = null; using (var db = new Database(SQLCONN.Conn)) { if (!string.IsNullOrEmpty(UserName) && UserName != "admin") { items = db.Fetch<V_ERP_SuppOrder>("select * from V_ERP_SuppOrder where SuppBatchGuid=@0 and SuppName =@1", SuppBatchGuid, UserName); } else { items = db.Fetch<V_ERP_SuppOrder>("select * from V_ERP_SuppOrder where SuppBatchGuid=@0", SuppBatchGuid); } items.ForEach(j => { j.Reserved2 = KyGYS.Controls.CommonUtil.GetItemImgFileName(j.Reserved2); j.CusSavedFileName = j.CusSavedFileName!=null?KyGYS.Controls.CommonUtil.GetItemImgFileName(j.CusSavedFileName):null; j.CtmSavedFileName = j.CtmSavedFileName != null ? KyGYS.Controls.CommonUtil.GetItemImgFileName(j.CtmSavedFileName) : null; }); var grd = new EasyGridData<V_ERP_SuppOrder>(); grd.total = items.Count().ToString(); grd.rows = items; string data = Newtonsoft.Json.JsonConvert.SerializeObject(grd); Response.Write(data); Response.End(); } }
public IList <Contato> PesquisaAll(string query) { Database db = new PetaPoco.Database("PRWDEV"); if (query == null) { return(db.Fetch <Contato>("Select * FROM contatosbandre")); } else { return(db.Fetch <Contato>("Select * FROM contatosbandre WHERE nome like '%' + @query + '%' or cpf like '%' + @query + '%' or estadoid = @query", new { query })); } }
List<CONSUMABLES_LOG1> _lst; // 日志表 #endregion Fields #region Constructors public FrmConsumOut_Comfirm(List<CONSUMABLES_LOG1> lst, List<CONSUMABLES_LOG> lst1) { InitializeComponent(); ////string sPwd = Des.Decrypt(ClsFrmMng.KEY, ConfigurationManager.AppSettings["DbPwd"]); db = new Database("XE"); _lst = lst; _consumLst = lst1; cONSUMABLESLOG1BindingSource.DataSource = lst; aCCOUNTBindingSource.DataSource = db.Fetch<ACCOUNT>(""); vALUECODEBindingSource3.DataSource = db.Fetch<VALUE_CODE>("where groupName = @0", new object[] { 20 }); // 操作类型 }
public FrmEdtPath(Int64 id) { InitializeComponent(); //string sPwd = Des.Decrypt(ClsFrmMng.KEY, ConfigurationManager.AppSettings["DbPwd"]); db = new Database("XE"); _id = id; pathHis = db.Single<VASCULARPATH_HISTORY>("where ID = @0", _id); vASCULARPATHHISTORYBindingSource.DataSource = pathHis; vALUECODEBindingSource.DataSource = db.Fetch<VALUE_CODE>("where groupName = 193"); vALUECODEBindingSource1.DataSource = db.Fetch<VALUE_CODE>("where groupName = 194"); }
/// <summary> /// /// </summary> /// <param name="base_id"></param> /// <param name="reg_id"></param> public FrmNewBC_Consumable(Int64 base_id, Int64 reg_id) { InitializeComponent(); db = new Database("XE"); _regID = reg_id; _baseID = base_id; vALUECODEBindingSource.DataSource = db.Fetch<VALUE_CODE>(""); vALUEGROUPBindingSource.DataSource = db.Fetch<VALUE_GROUP>(""); LoadData(); }
private static List<Comment> GetAllComments() { using (var db = new Database("umbracoDbDSN")) { return db.Fetch<Comment>("SELECT * FROM forumComments WHERE isSpam = 1 ORDER BY id DESC"); } }
dynamic IStudent.GetStudent(int id) { var entity = _db.Single <entity.StudentEntity>(id); var entityEx = _db.Fetch <Student.entity.StudentEx>("select * from StudentExInfo where studentId=@0", entity.Id); dynamic retStudent = new ExpandoObject(); retStudent.Id = entity.Id; retStudent.IsHide = entity.IsHide; retStudent.Name = entity.Name; foreach (var extItem in entityEx) { (retStudent as ICollection <KeyValuePair <string, object> >).Add(new KeyValuePair <string, object>(extItem.KeyName, extItem.KeyValue)); } return(retStudent); }
List <CheckIn.Model.Class> IClass.GetClassList(string name) { var retList = new List <CheckIn.Model.Class>(); string where = " 1=1 "; if (!string.IsNullOrEmpty(name)) { where += "and name like '%" + name + "%'"; } var entityList = _db.Fetch <Entity.ClassEntity>("select * from classInfo " + where); foreach (var item in entityList) { var newModel = new CheckIn.Model.Class(); newModel.ClassTime = item.ClassTime; newModel.Description = item.Description; newModel.EndTime = item.EndTime; newModel.Id = item.Id; newModel.InputTime = item.InputTime; //newModel.InputUser = new user newModel.Level = item.ClassLevel; newModel.Name = item.Name; newModel.StartTime = item.StartTime; newModel.Teacher = item.Teacher; newModel.Type = item.Type; retList.Add(newModel); } return(retList); }
public FrmNewLayout() { InitializeComponent(); db = new Database("XE"); vALUECODEBindingSource.DataSource = db.Fetch<VALUE_CODE>("where GROUPNAME = @0", 21); vALUECODEBindingSource1.DataSource = db.Fetch<VALUE_CODE>("where GROUPNAME = @0", 22); // 区域 mACHINELAYOUTBindingSource.DataSource = mach; ConditionValidationRule ruleNoEmpty = new ConditionValidationRule(); ruleNoEmpty.ConditionOperator = ConditionOperator.IsNotBlank; ruleNoEmpty.ErrorText = "该项不能为空。"; dxValidationProvider1.SetValidationRule(FLOORIDTextEdit, ruleNoEmpty); // 楼层 dxValidationProvider1.SetValidationRule(AREAIDTextEdit, ruleNoEmpty); // 区域 }
void barBtnRefresh_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e) { using (var db = new Database()) { this.gridControlEx1.DataSource = db.Fetch<t_role>("select * from t_role where IsUsing=1"); this.gridControlEx1.ReleaseFocusedRow(); } }
public ActionResult Index(int page = 1) { var db = new PetaPoco.Database("MonoCMS"); var categories = db.Fetch<Category>("SELECT category_name FROM category"); return View(categories); //return View(); }
public ActionResult GetLeases() { var db = new PetaPoco.Database("umbracoDbDSN"); var listOfLeases = db.Fetch<Lease>(new Sql().Select("*").From("_sg_Lease")); return Json(listOfLeases); }
private static List<Topic> GetAllTopics() { using (var db = new Database("umbracoDbDSN")) { return db.Fetch<Topic>("SELECT * FROM forumTopics WHERE isSpam = 1 ORDER BY id DESC"); } }
public static List<Route> getRoutes() { using (Database db = new PetaPoco.Database(ModelConfig.connectionStringName("bikes"))) { return db.Fetch<Route>("WHERE deleted = FALSE"); } }
////utility function to build the sql statement to perform the join //private static Sql sql() //{ // return Sql // .Builder // .Append("SELECT a.id, a.bike_id, a.rider_id, a.route_id, a.ride_date, a.notes,") // .Append("a.reward, a.distance, a.paid, a.payment_id,") // .Append("b.name AS route, c.name AS rider, d.name AS bike") // .Append("FROM ride a, route b, rider c, bike d") // .Append("WHERE a.route_id = b.id AND a.rider_id = c.id AND a.bike_id = d.id"); //} public static List<Ride> getRides() { using (Database db = new PetaPoco.Database(ModelConfig.connectionStringName("bikes"))) { return db.Fetch<Ride>(""); } }
public FrmEdtCaseHis( Int64 id) { InitializeComponent(); //string sPwd = Des.Decrypt(ClsFrmMng.KEY, ConfigurationManager.AppSettings["DbPwd"]); db = new Database("XE"); _id = id; caseHis = db.Single<CASE_HISTORY>("select * from CASE_HISTORY where ID = @0", _id); cASEHISTORYBindingSource.DataSource = caseHis; lookUpEdit1.Properties.DataSource = db.Fetch<VALUE_CODE>("where GROUPNAME = @0", new object[]{11}); lookUpEdit1.Properties.DisplayMember = "DSP_MEMBER"; lookUpEdit1.Properties.ValueMember = "VALUE_MEMBER"; vALUECODEBindingSource.DataSource = db.Fetch<VALUE_CODE>("where GROUPNAME = @0", new object[] { 181 }); }
public FrmEdtPatient_Course(Int64 id) { InitializeComponent(); db = new Database("XE"); _id = id; //ConditionValidationRule ruleNoEmpty = new ConditionValidationRule(); //ruleNoEmpty.ConditionOperator = ConditionOperator.IsNotBlank; //ruleNoEmpty.ErrorText = "该项不能为空。"; //dxValidationProvider1.SetValidationRule(INFECTIOUS_DISEASETextEdit, ruleNoEmpty); vALUECODEBindingSource.DataSource = db.Fetch<VALUE_CODE>("where groupName = @0", 193); // 通路类型 vALUECODEBindingSource1.DataSource = db.Fetch<VALUE_CODE>("where groupName = @0", 44); // 透析器型号 v = db.Single<PATIENT_COURSE>("where ID = @0", _id); pATIENTCOURSEBindingSource.DataSource = v; }
private void EditView_Load(object sender, EventArgs e) { using (var db = new Database()) { LstMac =db.Fetch<t_mac>("select * from t_mac"); memedt.Lines = LstMac.Select(k => k.Mac).ToArray(); } }
public FrmNewPatientBaseInfo() { InitializeComponent(); ////string sPwd = Des.Decrypt(ClsFrmMng.KEY, ConfigurationManager.AppSettings["DbPwd"]); db = new Database("XE"); ConditionValidationRule ruleNoEmpty = new ConditionValidationRule(); ruleNoEmpty.ConditionOperator = ConditionOperator.IsNotBlank; ruleNoEmpty.ErrorText = "该项不能为空。"; dxValidationProvider1.SetValidationRule(NAMETextEdit, ruleNoEmpty); dxValidationProvider1.SetValidationRule(ID_CODETextEdit, ruleNoEmpty); pATIENTBASEINFOBindingSource.DataSource = patientBase; MEDICARE_TYPETextEdit.Properties.DataSource = db.Fetch<VALUE_CODE>("select DSP_MEMBER, VALUE_MEMBER from VALUE_CODE where GROUPNAME = @0", new object[] { 10 }); MEDICARE_TYPETextEdit.Properties.DisplayMember = "DSP_MEMBER"; MEDICARE_TYPETextEdit.Properties.ValueMember = "VALUE_MEMBER"; EDUCATIONAL_LEVELTextEdit.Properties.DataSource = db.Fetch<VALUE_CODE>("where GROUPNAME = @0", new object[] { 8 }); EDUCATIONAL_LEVELTextEdit.Properties.DisplayMember = "DSP_MEMBER"; EDUCATIONAL_LEVELTextEdit.Properties.ValueMember = "VALUE_MEMBER"; MARITAL_STATUSTextEdit.Properties.DataSource = db.Fetch<VALUE_CODE>("where GROUPNAME = @0", new object[] { 9 }); MARITAL_STATUSTextEdit.Properties.DisplayMember = "DSP_MEMBER"; MARITAL_STATUSTextEdit.Properties.ValueMember = "VALUE_MEMBER"; vALUECODEBindingSource.DataSource = db.Fetch<VALUE_CODE>("where groupname = 42"); vALUECODEBindingSource1.DataSource = db.Fetch<VALUE_CODE>("where groupname = 41"); vALUECODEBindingSource2.DataSource = db.Fetch<VALUE_CODE>("where groupname = 62"); vALUECODEBindingSource3.DataSource = db.Fetch<VALUE_CODE>("where groupname = 63"); }
public FrmConsumIn(Int64 consumLstID) { InitializeComponent(); gridView2.InvalidValueException += gridView2_InvalidValueException; id = consumLstID; ////string sPwd = Des.Decrypt(ClsFrmMng.KEY, ConfigurationManager.AppSettings["DbPwd"]); db = new Database("XE"); cONSUMABLESWAREHOUSEBindingSource.DataSource = db.Fetch<CONSUMABLES_WAREHOUSE>("where ID = @0", new object[]{consumLstID}); cONSUMABLESLOGBindingSource.DataSource = lstConsumIn; vALUECODEBindingSource.DataSource = db.Fetch<VALUE_GROUP>("where FATHERID = @0", new object[] { 16 }); // 耗材分类 vALUECODEBindingSource1.DataSource = db.Fetch<VALUE_CODE>("where groupName = @0", new object[] { 17 }); // 耗材供应商 vALUECODEBindingSource2.DataSource = db.Fetch<VALUE_CODE>(""); vALUECODEBindingSource3.DataSource = db.Fetch<VALUE_CODE>("where groupName = @0", new object[] { 20 }); // 操作类型 }
public static List <PersonalProject> GetOrgProjects(string projectId, OrganizationType organizationType) { var db = new PetaPoco.Database("DefaultConnection"); var sql = new Sql(@"select op.*,o.OrganizationName,op.StudysiteNo, o.SubDomain from sms_orgProject op left join cts_organization o on op.organizationId=o.id where op.projectid=@0 and o.OrganizationType=@1 and isnull(op.isdeleted,0)<>1 and isnull(o.isdeleted,0)<>1", projectId, organizationType); return(db.Fetch <PersonalProject>(sql)); }
public FrmNewConsum() { InitializeComponent(); //string sPwd = Des.Decrypt(ClsFrmMng.KEY, ConfigurationManager.AppSettings["DbPwd"]); db = new Database("XE"); cONSUMABLESWAREHOUSEBindingSource.DataSource = consumWh; vALUEGROUPBindingSource.DataSource = db.Fetch<VALUE_GROUP>("where FATHERID = @0", new object[] { 16 }); // 耗材分类 vALUECODEBindingSource2.DataSource = db.Fetch<VALUE_CODE>("where groupName = @0", new object[] { 17 }); // 耗材供应商 ConditionValidationRule ruleNoEmpty = new ConditionValidationRule(); ruleNoEmpty.ConditionOperator = ConditionOperator.IsNotBlank; ruleNoEmpty.ErrorText = "该项不能为空。"; dxValidationProvider1.SetValidationRule(CONSUMABLES_CODETextEdit, ruleNoEmpty); dxValidationProvider1.SetValidationRule(NAMETextEdit, ruleNoEmpty); }
public FrmEdtDrugsAllergy(Int64 id) { InitializeComponent(); _id = id; //string sPwd = Des.Decrypt(ClsFrmMng.KEY, ConfigurationManager.AppSettings["DbPwd"]); db = new Database("XE"); _id = id; vALUECODEBindingSource.DataSource = db.Fetch<VALUE_CODE>("where GROUPNAME = 185"); vALUECODEBindingSource1.DataSource = db.Fetch<VALUE_CODE>("where GROUPNAME = 186"); vALUECODEBindingSource2.DataSource = db.Fetch<VALUE_CODE>("where GROUPNAME = 187"); vALUECODEBindingSource3.DataSource = db.Fetch<VALUE_CODE>("where GROUPNAME = 183"); vALUECODEBindingSource4.DataSource = db.Fetch<VALUE_CODE>("where GROUPNAME = 184"); drugAllergyHis = db.Single<DRUG_ALLERGY_HISTORY>("select * from DRUG_ALLERGY_HISTORY where ID = @0", _id); dRUGALLERGYHISTORYBindingSource.DataSource = drugAllergyHis; }
public static List<Rider> getRiders(bool includeDeleted = false) { String whereClause = includeDeleted ? "" : "WHERE deleted = FALSE"; using (Database db = new PetaPoco.Database(ModelConfig.connectionStringName("bikes"))) { List<Rider> riders = db.Fetch<Rider>(whereClause); return riders; } }
public FrmNewPath(Int64 base_id, Int64 reg_id) { InitializeComponent(); //string sPwd = Des.Decrypt(ClsFrmMng.KEY, ConfigurationManager.AppSettings["DbPwd"]); db = new Database("XE"); _regID = reg_id; _baseID = base_id; path.REG_ID = _regID; path.BASE_INFO_ID = _baseID; path.OPERATOR = ClsFrmMng.WorkerID; vASCULARPATHHISTORYBindingSource.DataSource = path; vALUECODEBindingSource.DataSource = db.Fetch<VALUE_CODE>("where groupName = 193"); vALUECODEBindingSource1.DataSource = db.Fetch<VALUE_CODE>("where groupName = 194"); vALUECODEBindingSource2.DataSource = db.Fetch<VALUE_CODE>("where groupName = 246"); }
public FrmPatientBaseInfo() { InitializeComponent(); //string sPwd = Des.Decrypt(ClsFrmMng.KEY, ConfigurationManager.AppSettings["DbPwd"]); db = new Database("XE"); vALUECODEBindingSource.DataSource = db.Fetch<VALUE_CODE>(""); //vALUECODEBindingSource1.DataSource = db.Fetch<VALUE_CODE>("where groupname = 41"); }
/// <summary> /// Fetches the complete set of elements and returns this set as an IEnumerable. /// </summary> /// <returns>the set fetched</returns> public override IEnumerable <SalesOrderHeader> FetchSet() { var headers = new List <SalesOrderHeader>(); var dbFactory = new PetaPoco.Database(ConnectionStringToUse, "System.Data.SqlClient"); dbFactory.OpenSharedConnection(); headers = dbFactory.Fetch <SalesOrderHeader>(CommandText); dbFactory.CloseSharedConnection(); return(headers); }
public FrmMain(XtraForm frmUserLogin, string userID, string userName, int iGroupID) { InitializeComponent(); //navBarControl1.AllowSelectedLink = true; //xtraTabbedMdiManager1.ClosePageButtonShowMode = DevExpress.XtraTab.ClosePageButtonShowMode.InActiveTabPageHeaderAndOnMouseHover; _frmUserLogin = frmUserLogin; _userID = userID; _userName = userName; ClsFrmMng.GroupID = iGroupID; ClsFrmMng.WorkerID = _userID; ClsFrmMng.WorkerName = _userName; SkinHelper.InitSkinPopupMenu(menuStyle); // 获取值集代码 //string sPwd = Des.Decrypt(ClsFrmMng.KEY, ConfigurationManager.AppSettings["DbPwd"]); PetaPoco.Database db = new Database("XE"); ClsFrmMng.lstRegStatus = db.Fetch<REG_STATUS>(""); ClsFrmMng.lstIndecumentCode = db.Fetch<INDUCEMENT_CODE>(""); ClsFrmMng.lstDigesCode = db.Fetch<DIGESTIVESYS_CODE>(""); ClsFrmMng.lstHaveOrNull = new List<HaveOrNull>(); ClsFrmMng.lstHaveOrNull.Add(new HaveOrNull(0, "无")); ClsFrmMng.lstHaveOrNull.Add(new HaveOrNull(1, "有")); ClsFrmMng.lstDocDavType = new List<DocAdvType>(); ClsFrmMng.lstDocDavType.Add(new DocAdvType(0, "长期医嘱")); ClsFrmMng.lstDocDavType.Add(new DocAdvType(1, "临时医嘱")); ClsFrmMng.lstDocDavType.Add(new DocAdvType(9, "默认医嘱")); ClsFrmMng.lstSexType = new List<SexType>(); ClsFrmMng.lstSexType.Add(new SexType("1", "男")); ClsFrmMng.lstSexType.Add(new SexType("2", "女")); if (iGroupID != 1) { mitAuthrz.Visibility = DevExpress.XtraBars.BarItemVisibility.Never; this.Shown += FrmMain_Shown; } ClientFormStateClass formStat = new ClientFormStateClass(); formStat.RestorePanelsState(dockManager1); }
public string getAllMarkers() { using (var db = new PetaPoco.Database("linkSTEM")) { var SQL = Sql.Builder.Append("SELECT *") .Append("FROM [dbo].[Markers]"); return(JsonConvert.SerializeObject(db.Fetch <Marker>(SQL))); } }
//验证数据 private void btnCtl3_Click(object sender, EventArgs e) { if (string.IsNullOrEmpty(fileBrowser1.Text)) { MsgBox.ShowErrMsg("请先选择导入文件"); return; } //读取文件数据 var dt = XlsCommon.Read(fileBrowser1.Text); dt.Columns.Add("错误信息", typeof(System.String)); gridControlEx1.DataSource = dt; var cnt = 0; List<T_ERP_Item> items = null; using (var db = new Database(this.ConnString)) { items = db.Fetch<UltraDbEntity.T_ERP_Item>(" select * from T_ERP_Item "); } foreach (DataRow dr in dt.Rows) { if (!ChkData(dr, items)) cnt++; } if (cnt > 0) { gridControlEx1.RefreshDataSource(); return; } //判断是否存在重复编码的 var query = from t in dt.AsEnumerable() group t by new { t1 = t.Field<string>("商品编码"), t2 = t.Field<string>("规格编码") } into m select new { OuterIid = m.Key.t1, OuterSkuId = m.Key.t2, Cnt = m.Count() }; if (query.ToList().Count > 0) { query.ToList().ForEach(q => { if (q.Cnt > 1) { foreach (DataRow dr in dt.Rows) { if (dr["商品编码"].ToString().Equals(q.OuterIid) && dr["规格编码"].ToString().Equals(q.OuterSkuId)) dr["错误信息"] = ("存在重复的商品编码与规格编码"); } cnt++; } }); } gridControlEx1.RefreshDataSource(); btnImp.Enabled = cnt < 1; }
public FrmEdtDiagonsis_Pathological(Int64 id) { InitializeComponent(); db = new Database("XE"); _id = id; ConditionValidationRule ruleNoEmpty = new ConditionValidationRule(); ruleNoEmpty.ConditionOperator = ConditionOperator.IsNotBlank; ruleNoEmpty.ErrorText = "该项不能为空。"; dxValidationProvider1.SetValidationRule(PATHOLOGICAL_DIAGNOSIS_TYPELookUpEdit, ruleNoEmpty); vALUECODEBindingSource.DataSource = db.Fetch<VALUE_CODE>("where groupName = @0", 129); vALUECODEBindingSource1.DataSource = db.Fetch<VALUE_CODE>("where groupName = @0", 123); vALUECODEBindingSource2.DataSource = db.Fetch<VALUE_CODE>("where groupName = @0", 124); vALUECODEBindingSource3.DataSource = db.Fetch<VALUE_CODE>("where groupName = @0", 126); vALUECODEBindingSource4.DataSource = db.Fetch<VALUE_CODE>("where groupName = @0", 125); diag = db.Single<DIAGNOSIS_PATHOLOGICAL>("where ID = @0", _id); dIAGNOSISPATHOLOGICALBindingSource.DataSource = diag; }
public string getProviderMarkers(int providerID) { using (var db = new PetaPoco.Database("linkSTEM")) { var SQL = Sql.Builder.Append("SELECT *") .Append("FROM [dbo].[Markers]") .Append("WHERE [provider_ID] = @0", providerID); return(JsonConvert.SerializeObject(db.Fetch <Marker>(SQL))); } }
private IEnumerable<SalesSummaryReportLine> GetReportLines() { // Icky, ugly, crap - never put data access code in a controller (unless you're doing a demo) var database = new Database(ConnectionStrings.Main, "System.Data.SqlClient"); var reportLines = database .Fetch<SalesSummaryReportLine>(EmbeddedSql.OldSkoolQueryText); // var reportLines = database.Fetch<SalesSummaryReportLine>(EmbeddedSql.PetaPocoQueryText); return reportLines; }
public FrmEdtUremic(Int64 id) { InitializeComponent(); //string sPwd = Des.Decrypt(ClsFrmMng.KEY, ConfigurationManager.AppSettings["DbPwd"]); db = new Database("XE"); _id = id; uremicHis = db.Single<UREMIC_SYMPTOMS_HI>("select * from UREMIC_SYMPTOMS_HIS where ID = @0", _id); uREMICSYMPTOMSHIBindingSource.DataSource = uremicHis; DIGESTIVETextEdit.Properties.TextEditStyle = DevExpress.XtraEditors.Controls.TextEditStyles.Standard; DIGESTIVETextEdit.Properties.DataSource = ClsFrmMng.lstIndecumentCode; DIGESTIVETextEdit.Properties.DisplayMember = "INDUCEMENT_MEMO"; DIGESTIVETextEdit.Properties.ValueMember = "INDUCEMENT_MEMO"; vALUECODEBindingSource.DataSource = db.Fetch<VALUE_CODE>("where groupName = 192"); vALUECODEBindingSource1.DataSource = db.Fetch<VALUE_CODE>("where groupName = 189"); vALUECODEBindingSource2.DataSource = db.Fetch<VALUE_CODE>("where groupName = 190"); }
public ActionResult Index() { var db = new PetaPoco.Database("DefaultConnection"); //var sql1 = new Sql("CREATE TABLE #tb1(Id varchar(50) not null)"); //db.Execute(sql1); var sql2 = new Sql(@"select op.*,o.OrganizationName,op.StudysiteNo, o.SubDomain from sms_orgProject op left join cts_organization o on op.organizationId=o.id where op.projectid =@0 and o.OrganizationType=@1 and isnull(op.isdeleted,0)<>1 and isnull(o.isdeleted,0)<>1", "593169721116912623", 2); var kk = db.Fetch <PersonalProject>(sql2); var ssj = db.LastArgs; var ks = db.LastCommand; var s = db.LastSQL; var k12 = GetOrgProjects("593079586929400170", OrganizationType.StudySite); var k2 = GetOrgProjectsString("593079586929400170", OrganizationType.StudySite); //(!input.isSiteDataCollection && input.SubjectVisitIds != null&& input.SubjectVisitIdsString == null ? " and #subjectVisit.Id in " + string.Format("('{0}')", string.Join("','", input.SubjectVisitIds)) : (!input.isSiteDataCollection && input.SubjectVisitIds != null ? " and #subjectVisit.Id in " + string.Format("({0})", string.Join("','", input.SubjectVisitIds)) : "")) //(!input.isSiteDataCollection && input.SubjectVisitIds != null ? " and #subjectVisit.Id in " + string.Format("('{0}')", string.Join("','", input.SubjectVisitIds)) : "") // // Create the article // var a = new test(); // a.name = "我的标题"; // a.type = 4; // db.Insert("test", "id", a); // var result = db.Page<test>(1, 2, // <-- page number and items per page //"SELECT * FROM test WHERE type=@0 ORDER BY id DESC", 3); // var ssd = db.Query<test>("SELECT * FROM test"); // // 查询所有数据 // foreach (var aaa in db.Query<test>("SELECT * FROM test")) // { // //var ss = aa; // Response.Write(aaa.name); // //Console.WriteLine("{0} - {1}", a.article_id, a.title); // } // //查询标量 // long count = db.ExecuteScalar<long>("SELECT Count(*) FROM test"); // Response.Write(count); // //查询单条数据 // var aa = db.SingleOrDefault<test>("SELECT * FROM test WHERE id=@0", 1); // Response.Write(aa.name); return(View()); }
/// <summary> /// Fetches the complete set of elements and returns this set as an IEnumerable. /// </summary> /// <returns>the set fetched</returns> public override IEnumerable <SalesOrderHeader> FetchSet() { var headers = new List <SalesOrderHeader>(); var dbFactory = new PetaPoco.Database(ConnectionStringToUse, "System.Data.SqlClient"); dbFactory.OpenSharedConnection(); dbFactory.EnableAutoSelect = false; dbFactory.EnableNamedParams = false; dbFactory.ForceDateTimesToUtc = false; headers = dbFactory.Fetch <SalesOrderHeader>(CommandText); dbFactory.CloseSharedConnection(); return(headers); }
/// <summary> /// 获取员工信息集合(简要信息) /// 区别:这是简要的员工信息,用来做任务分配 /// </summary> /// <param name="LoginGuid">登入者GUID</param> /// <returns></returns> public List <MEmployee> GetEmployeeList(string LoginGuid) { List <MEmployee> employees; using (SqlConnection sqlConnection = new SqlConnection(SystemSqlConn)) { sqlConnection.Open(); using (Database db = new PetaPoco.Database(sqlConnection)) { employees = db.Fetch <MEmployee>(@"SELECT EmployeeGuid,EmployeeCode,EmployeeNickName,EmployeeSex,EmployeeTel,TelOperator FROM Tb_Employee WHERE BelongToUser = @0 AND EmployeeIsLocked = 0", LoginGuid); } return(employees); } }
protected void rfrCache_Click(object sender, EventArgs e) { cacheManager.RemoveFromCache("providers"); pixProviders p = Models.SettingsCache.GetProvider(Convert.ToInt16(txtIP.Text)); var db = new PetaPoco.Database("myConnectionString"); string sQuery = "SELECT * FROM LandingPagesByGEO"; var result = db.Fetch <object>(sQuery); if (p == null) { label1.Text = "NULL"; } else { label1.Text = p.name; } }
private void ProcessProductGroups( PetaPoco.Database pDb, Connector connector, List <Content> content, List <ProductGroupMapping> productGroupMappings, FilterInfo filter, ProductGroupMapping parent, List <ContentProductGroup> contentProductGroups, List <ContentProductGroup> newContentProductGroupsToInsertInDb ) { if (filter.Flatten) { if (parent == null) { log.WarnFormat("FlattenHierarchy is not supported on root mappings"); return; } //combine all child groups into this one List <int> childIDList = productGroupMappings.Select(x => x.ProductGroupID).ToList(); //recursive? var groupProducts = content.AsQueryable(); if (parent.FilterByParentGroup) { if (filter.ProductGroupFilter.Count > 0) { foreach (int id in filter.ProductGroupFilter) { groupProducts = groupProducts.Where(x => x.ProductGroupVendors.Contains(id)); } } } //filter all groupProducts = groupProducts.Where(x => x.ProductGroupVendors.Any(y => childIDList.Contains(y))); foreach (var mapping in productGroupMappings) { if (mapping.FilterByParentGroup) { if (filter.ProductGroupFilter.Count > 0) { foreach (int id in filter.ProductGroupFilter) { groupProducts = groupProducts.Where(x => x.ProductGroupVendors.Contains(id)); } } } } foreach (var product in groupProducts) { var currentRecord = contentProductGroups.Where( x => x.ConnectorID == connector.ConnectorID && x.ProductID == product.ProductID && x.ProductGroupMappingID == parent.ProductGroupMappingID).SingleOrDefault(); if (currentRecord == null) { var newRecord = new ContentProductGroup() { ConnectorID = connector.ConnectorID, ProductID = product.ProductID, ProductGroupMappingID = parent.ProductGroupMappingID, Exists = true, CreationTime = DateTime.Now, CreatedBy = Concentrator.Objects.Web.Client.User.UserID }; newContentProductGroupsToInsertInDb.Add(newRecord); contentProductGroups.Add(newRecord); } else { currentRecord.Exists = true; } } //unit.Save(); } else { foreach (var mapping in productGroupMappings) { var childMappings = pDb.Fetch <ProductGroupMapping>("SELECT * FROM ProductGroupMapping WHERE ParentProductGroupMappingID = @0", mapping.ProductGroupMappingID); bool hasChildren = childMappings.Count > 0; if (hasChildren) { var childFilter = new FilterInfo(); // do child groups if (mapping.FilterByParentGroup) { childFilter.ProductGroupFilter.AddRange(filter.ProductGroupFilter); } childFilter.ProductGroupFilter.Add(mapping.ProductGroupID); childFilter.Flatten = mapping.FlattenHierarchy; ProcessProductGroups(pDb, connector, content, childMappings, childFilter, mapping, contentProductGroups, newContentProductGroupsToInsertInDb); } else { var groupProducts = content.Where(c => c.ProductGroupVendors.Contains(mapping.ProductGroupID)).ToList(); if (mapping.FilterByParentGroup) { if (filter.ProductGroupFilter.Count > 0) { foreach (int id in filter.ProductGroupFilter) { groupProducts = groupProducts.Where(x => x.ProductGroupVendors.Contains(id)).ToList(); } } } foreach (var product in groupProducts) { var currentRecord = contentProductGroups.Where( x => x.ConnectorID == connector.ConnectorID && x.ProductID == product.ProductID && x.ProductGroupMappingID == mapping.ProductGroupMappingID).SingleOrDefault(); if (currentRecord == null) { var newRecord = new ContentProductGroup() { ConnectorID = connector.ConnectorID, ProductID = product.ProductID, ProductGroupMappingID = mapping.ProductGroupMappingID, Exists = true, CreationTime = DateTime.Now, CreatedBy = Concentrator.Objects.Web.Client.User.UserID }; newContentProductGroupsToInsertInDb.Add(newRecord); contentProductGroups.Add(newRecord); } else { currentRecord.Exists = true; } } } } } }
public static List <T> FetchManyToOne <T, T1, T2, T3>(this Database db, Func <T, object> key, Sql Sql) { var relator = new Relator(); return(db.Fetch <T, T1, T2, T3, T>((a, b, c, d) => relator.ManyToOne(a, b, c, d, key), Sql)); }
protected override void Process() { using (var unit = GetUnitOfWork()) { try { var _assortmentRepo = unit.Scope.Repository <VendorAssortment>(); var connectors = (from c in this.Connectors where (((ConnectorType)c.ConnectorType).Has(ConnectorType.WebAssortment) || ((ConnectorType)c.ConnectorType).Has(ConnectorType.ShopAssortment)) #if !DEBUG && c.IsActive #endif select c).ToList(); foreach (Connector conn in connectors) { bool excludeProducts = conn.ConnectorSettings.GetValueByKey <bool>("ExcludeProducts", false); using (var pDb = new PetaPoco.Database(Connection, "System.Data.SqlClient")) { pDb.CommandTimeout = 15 * 60; var productMatches = pDb.Query <ProductMatch>("SELECT * FROM ProductMatch WHERE isMatched = 1").ToList(); List <string> vendorItemNumbersToExclude = new List <string>(); Dictionary <int, string> vendorItemNumbers = new Dictionary <int, string>(); Dictionary <int, Content> contentList = new Dictionary <int, Content>(); log.DebugFormat("Start Generating Assortment for {0}", conn.Name); log.DebugFormat("Connector {0}({1}) has {2} rules for assortment generation", conn.Name, conn.ConnectorID, conn.ContentProducts.Count); bool assortmentLoaded = false; var publicationRules = pDb.Fetch <ConnectorPublication>("SELECT * FROM ConnectorPublication WHERE ConnectorID = @0", conn.ConnectorID); if (excludeProducts) { vendorItemNumbersToExclude = pDb.Fetch <string>("Select value from ExcludeProduct where ConnectorID = @0", conn.ConnectorID).ToList(); vendorItemNumbers = pDb.Fetch <Product>("select productID, VendorItemNumber from product").ToDictionary(x => x.ProductID, y => y.VendorItemNumber); } foreach (ContentProduct rule in conn.ContentProducts.OrderBy(x => x.ProductContentIndex)) { var connectorPublicationRules = publicationRules.Where(x => x.VendorID == rule.VendorID && (!x.FromDate.HasValue || (x.FromDate.HasValue && x.FromDate <= DateTime.Now)) && (!x.ToDate.HasValue || (x.ToDate.HasValue && x.ToDate >= DateTime.Now))).ToList(); if (!assortmentLoaded) { assortmentLoaded = rule.IsAssortment; } #region content logic log.DebugFormat("Processing Rule {0} for connectorid {1}", rule.ProductContentID, conn.ConnectorID); if (rule.ProductID.HasValue) { #region Single Product if (assortmentLoaded && !rule.IsAssortment) { // exclude product if (rule.ProductID.HasValue) { contentList.Remove(rule.ProductID.Value); } } else { var vass = _assortmentRepo.GetSingle(a => a.VendorID == rule.VendorID && a.ProductID == rule.ProductID.Value && a.IsActive); if (vass != null) { var groups = (from va in _assortmentRepo.GetAllAsQueryable() from pa in va.ProductGroupVendors select new { va.VendorAssortmentID, pa.ProductGroupVendorID }).ToList(); var vendoradd = (from va in _assortmentRepo.GetAllAsQueryable() where va.VendorAssortmentID == vass.VendorAssortmentID select new VendorAdds { VendorAssortmentID = va.VendorAssortmentID, ConcentratorStatusID = va.VendorPrices.FirstOrDefault().ConcentratorStatusID, QuantityOnHand = unit.Scope.Repository <VendorStock>().GetAllAsQueryable(c => c.ProductID == va.ProductID && c.VendorID == va.VendorID).Sum(c => c.QuantityOnHand), ProductID = va.ProductID, BrandID = va.Product.BrandID }).ToList(); Content content = null; if (!contentList.ContainsKey(vass.ProductID)) { if (vass.ProductGroupVendors == null) { vass.ProductGroupVendors = new List <ProductGroupVendor>(); } content = new Content { ConnectorID = conn.ConnectorID, ProductID = rule.ProductID.Value, ShortDescription = vass.ShortDescription, LongDescription = vass.LongDescription, ProductContentID = rule.ProductContentID, ProductGroupVendors = vass.ProductGroupVendors.Select(c => c.ProductGroupID).ToList(), Product = unit.Scope.Repository <Product>().GetSingle(c => c.ProductID == rule.ProductID.Value) }; contentList.Add(rule.ProductID.Value, content); CheckConnectorPublication(content, connectorPublicationRules, vendoradd.FirstOrDefault(x => x.VendorAssortmentID == vass.VendorAssortmentID), unit, contentList); } } else { log.DebugFormat( "Could not add product {0} to content, does not exists in vender assortment (VendorID {1})", rule.ProductID.Value, rule.VendorID); } } #endregion } else { #region Non Product //include scenario string additionalFilters = String.Empty; if (rule.BrandID.HasValue) { additionalFilters += String.Format(" AND P.BrandID = {0}", rule.BrandID); } //if (rule.ProductGroupID.HasValue) //{ // vendorAssortment = vendorAssortment.Where(c => c.ProductGroupVendors.Any(l => l.ProductGroupID == rule.ProductGroupID.Value && l.VendorID == rule.VendorID)); //} string query = String.Format(@"SELECT VA.VendorAssortmentID, VP.ConcentratorStatusID, P.BrandID, VA.ProductID, ISNULL(SUM(VS.QuantityOnHand),0) AS QuantityOnHand FROM VendorAssortment VA INNER JOIN Product P ON (VA.ProductID = P.ProductID) INNER JOIN VendorStock VS ON (VA.VendorID = VS.VendorID AND VA.ProductID = VS.ProductID) INNER JOIN VendorPrice VP ON (VA.VendorAssortmentID = VP.VendorAssortmentID) WHERE VA.VendorID = {0} {1} GROUP BY VA.VendorAssortmentID, VP.ConcentratorStatusID, P.BrandID, VA.ProductID ", rule.VendorID, additionalFilters); var vendoradd = pDb.Query <VendorAdds>(query).ToDictionary(x => x.VendorAssortmentID, y => y); // base collection //var vendorAssortment = pDb.Fetch<VendorAssortment>("SELECT * FROM VendorAssortment WHERE VendorID = @0 AND IsActive = 1", rule.VendorID); var vendorAssortmentQuery = string.Format("SELECT va.*, pavSentToWehkamp.Value as 'SentToWehkamp', pavSentAsDummy.Value as 'SentToWehkampAsDummy' FROM VendorAssortment va LEFT OUTER JOIN ProductAttributeValue pavSentToWehkamp ON va.ProductID = pavSentToWehkamp.ProductID AND pavSentToWehkamp.AttributeID = (SELECT AttributeID FROM ProductAttributeMetaData pamd WHERE pamd.AttributeCode = 'SentToWehkamp') LEFT OUTER JOIN ProductAttributeValue pavSentAsDummy ON va.ProductID = pavSentAsDummy.ProductID AND pavSentAsDummy.AttributeID = (SELECT AttributeID FROM ProductAttributeMetaData pamd WHERE pamd.AttributeCode = 'SentToWehkampAsDummy') WHERE va.VendorID = {0} AND va.IsActive = 1", rule.VendorID); var vendorAssortment = pDb.Fetch <VendorAssortmentWehkampSent>(vendorAssortmentQuery); var productGroupVendors = pDb.Fetch <ProductGroupVendor>("SELECT * FROM ProductGroupVendor WHERE VendorID = @0 OR VendorID = @1", rule.VendorID, rule.Vendor.ParentVendorID ?? rule.VendorID); string groupsQuery = string.Format(@"SELECT DISTINCT VA.VendorAssortmentID, PGV.ProductGroupID FROM VendorAssortment VA INNER JOIN VendorProductGroupAssortment VPGA ON (VPGA.VendorAssortmentID = VA.VendorAssortmentID) INNER JOIN ProductGroupVendor PGV ON (PGV.ProductGroupVendorID = VPGA.ProductGroupVendorID) WHERE VA.VendorID = {0} AND VA.IsActive=1", rule.VendorID); var groupSource = pDb.Fetch <VaGroupInfo>(groupsQuery); var groups = (from g in groupSource group g by g.VendorAssortmentID into grouped select grouped).ToDictionary(x => x.Key, y => (from pg in y select pg.ProductGroupID).ToList()); groupSource = null; var productRepo = pDb.Fetch <Product>("SELECT * FROM Product").ToDictionary(x => x.ProductID, y => y); List <ProductAttributeValue> values = pDb.Query <ProductAttributeValue>(string.Format(@"select pav.* from productattributevalue pav inner join connectorpublication cp on pav.attributeid = cp.attributeid where cp.connectorid = {0}", rule.ConnectorID)).ToList(); foreach (var va in vendorAssortment) { if (!groups.ContainsKey(va.VendorAssortmentID)) { continue; } if (assortmentLoaded && !rule.IsAssortment) { contentList.Remove(va.ProductID); } else { if (excludeProducts && (string.IsNullOrEmpty(va.SentToWehkamp) || va.SentToWehkamp.ToLowerInvariant() == "false") && (string.IsNullOrEmpty(va.SentToWehkampAsDummy) || va.SentToWehkampAsDummy.ToLowerInvariant() == "false")) { string vendorItemNumber = vendorItemNumbers[va.ProductID]; if (ExcludeProduct(vendorItemNumber, vendorItemNumbersToExclude)) { continue; } } Content content = null; if (!contentList.ContainsKey(va.ProductID)) { content = new Content { ConnectorID = conn.ConnectorID, ProductID = va.ProductID, ShortDescription = va.ShortDescription, LongDescription = va.LongDescription, LineType = va.LineType, ProductContentID = rule.ProductContentID, ProductGroupVendors = groups[va.VendorAssortmentID], Product = productRepo[va.ProductID] }; //content.Product.RelatedProductsSource = relatedProducts.Where(c => c.ProductID == content.ProductID).ToList(); content.Product.ProductAttributeValues = values.Where(c => c.ProductID == content.ProductID).ToList(); contentList.Add(va.ProductID, content); } VendorAdds vad = null; vendoradd.TryGetValue(va.VendorAssortmentID, out vad); CheckConnectorPublication(content, connectorPublicationRules, vad, unit, contentList); } } #endregion } log.DebugFormat("Finished Processing Rule {0} for Connector {1}", rule.ProductContentID, conn.Name); #endregion } ///put logic here try { var vendorsettings = pDb.Query <ContentProduct>("SELECT * FROM ContentProduct WHERE ConnectorID = @0", conn.ConnectorID) .Select(v => new { v.ProductContentIndex, v.ProductContentID }).Distinct().ToDictionary(x => x.ProductContentID, x => x.ProductContentIndex); var copyContentList = (from c in contentList.Values join pm in productMatches on c.ProductID equals pm.ProductID select c).ToList(); foreach (var c in copyContentList) { var match = productMatches.Where(x => x.ProductID == c.ProductID).FirstOrDefault(); if (match != null) { var matches = productMatches.Where(x => x.ProductMatchID == match.ProductMatchID && x.ProductID != c.ProductID).ToList(); if (matches.Count > 0) { foreach (var product in matches) { if (!c.ProductContentID.HasValue) { continue; } int contentVendorIndex = vendorsettings[c.ProductContentID.Value]; Content matchContent = null; if (contentList.TryGetValue(product.ProductID, out matchContent)) { if (!matchContent.ProductContentID.HasValue) { continue; } int matchContentIndex = vendorsettings[matchContent.ProductContentID.Value]; if (contentVendorIndex > matchContentIndex) { contentList.Remove(c.ProductID); } else { contentList.Remove(matchContent.ProductID); } } } } } } } catch (Exception ex) { log.AuditError("Cleaning Matches failed for {0}", conn.Name); } var curContent = pDb.Fetch <Content>("SELECT * FROM Content WHERE ConnectorID = @0", conn.ConnectorID).ToDictionary(x => x.ProductID, y => y); try { log.DebugFormat("Cleaning up assortment for {0}", conn.Name); var delcontent = (from c in curContent where !contentList.ContainsKey(c.Key) select c.Value).ToList(); foreach (var rec in delcontent) { pDb.Delete("Content", "ProductID, ConnectorID", rec); } log.DebugFormat("Finished cleaning up for {0}", conn.Name); } catch (Exception ex) { log.FatalFormat("Assorment cleanup failed for {0} error {1}", conn.ConnectorID, ex.StackTrace); } #region Existing Content //OBSOLETE: //log.Debug("Processing Existing Content"); //foreach (var curCon in contentList.Values) //{ // Content exCon = null; // curContent.TryGetValue(curCon.ProductID, out exCon); // if (exCon != null) // { // exCon.ExtendedCatalog = curCon.ExtendedCatalog; // exCon.LedgerClass = curCon.LedgerClass; // exCon.LineType = curCon.LineType; // exCon.LongDescription = curCon.LongDescription; // exCon.ProductDesk = curCon.ProductDesk; // exCon.ShortDescription = curCon.ShortDescription; // exCon.ProductContentID = curCon.ProductContentID; // pDb.Update("Content", "ProductID, ConnectorID", exCon, new List<String>() { "ShortDescription", "LongDescription", "LineType", "LedgerClass", "ProductDesk", "ExtendedCatalog", "ProductContentID" }); // } //} ////unit.Save(); //log.Debug("Finished processing existing content"); #endregion var newContent = (from c in contentList where !curContent.ContainsKey(c.Key) select c.Value).ToList(); log.DebugFormat("Inserting {0} rows for connector {1}", newContent.Count(), conn.ConnectorID); foreach (var c in newContent) { pDb.Insert("Content", "ProductID, ConnectorID", false, new { ProductID = c.ProductID, ConnectorID = c.ConnectorID, c.ProductContentID, c.ShortDescription, c.LongDescription, c.ExtendedCatalog, c.LedgerClass, c.LineType, c.ProductDesk, CreatedBy = c.CreatedBy }); } #region contentproductgroups try { var contentProductGroups = pDb.Fetch <ContentProductGroup>("SELECT * FROM ContentProductGroup WHERE IsCustom = 0 AND ConnectorID = @0", conn.ConnectorID); //List<ProductGroupMapping> productGroupMappings = pDb.Fetch<ProductGroupMapping>("SELECT * FROM ProductGroupMapping WHERE Depth = 0 AND (ConnectorID = @0 OR ConnectorID = @1)", // conn.ConnectorID, conn.ParentConnectorID ?? conn.ConnectorID); List <ProductGroupMapping> productGroupMappings = pDb.Fetch <ProductGroupMapping>("SELECT * FROM ProductGroupMapping WHERE (ConnectorID = @0 OR ConnectorID = @1)", conn.ConnectorID, conn.ParentConnectorID ?? conn.ConnectorID); Dictionary <int, List <int> > MappingProductGroup = ( from c in productGroupMappings group c by c.ProductGroupID into gr select new { gr.Key, Value = gr.Select(c => c.ProductGroupMappingID) }) .ToDictionary(c => c.Key, c => c.Value.ToList()); //Start assortment tree processes var tree = BuildTree(productGroupMappings); var products = GetContentsPerMapping(contentList.Values.ToList(), productGroupMappings, MappingProductGroup); //add products to tree AddProductsToTree(tree, products, contentList.Values.ToList(), MappingProductGroup); //end assortment tree processes var toSync = tree.FlattenTreeToList(conn.ConnectorID, 1); SyncNewContentProductGroups(toSync, pDb, conn.ConnectorID); log.DebugFormat("Finish Remove unused contentproductgroups"); } catch (Exception ex) { log.Fatal("Error processing contentproductgroups for connectorID" + conn.ConnectorID.ToString(), ex); } #endregion log.DebugFormat("Finish insert assortment for connector {0}: {1}", conn.ConnectorID, conn.Name); if (conn.ConnectorSettings.GetValueByKey <bool>("SyncContentProductGroupsWithParentConnector", false)) { SyncCustomContentProducts(conn, pDb); } } } } catch (Exception ex) { log.Fatal("Processs assortment error", ex); } } }
/// <summary> /// Gets any indexes with a null LastProcessed /// </summary> /// <returns></returns> internal IEnumerable <RetreaveIndex> GetUnprocessedIndexes() { var results = _database.Fetch <RetreaveIndex>("WHERE LastProcessed IS NULL AND Active = 1"); return(FillResults(results)); }
public static List <T> FetchOneToMany <T, T1>(this Database db, Func <T, object> key, Sql Sql) { var relator = new Relator(); return(db.Fetch <T, T1, T>((a, b) => relator.OneToMany(a, b, key), Sql)); }