//筛选与用户相关的项目 private void bindProj() { string sqlCmd = "select B.id,B.projName from OA_Sys_ProjMember A left join OA_sys_Project B on A.projId=B.id where A.userId='" + GetUser() + "'"; DataTable dt = new DataTable(); SqlSel.GetSqlSel(ref dt, sqlCmd); ddl_proj.DataTextField = "projName"; ddl_proj.DataValueField = "id"; ddl_proj.DataSource = dt; ddl_proj.DataBind(); }
private void loadInfo(string userWXId) { if (!string.IsNullOrEmpty(userWXId)) { string sqlCmd = "select A.*,B.deptName from OA_Sys_EmployeeInfo A left join OA_sys_department B on A.deptId=B.id where A.id='" + userWXId + "'"; DataTable dt = new DataTable(); SqlSel.GetSqlSel(ref dt, sqlCmd); label_name.Text = dt.Rows[0]["loginId"].ToString() + "/" + dt.Rows[0]["chineseName"].ToString() + "/" + dt.Rows[0]["deptName"].ToString(); label_date.Text = DateTime.Now.ToShortDateString(); } }
//插入附件信息 private void InsertFiles(string formId) { string sqlCmd = ""; foreach (GridRow gr in Grid1.Rows) { string fileName = gr.DataKeys[0].ToString(); sqlCmd = "insert into OA_Sys_files (FormId,fileName,formDataName) values ('" + formId + "','" + fileName + "','OA_PayMent_Main')"; SqlSel.ExeSql(sqlCmd); } }
protected void btn_filter_Click(object sender, EventArgs e) { try { DateTime date1 = Convert.ToDateTime(DatePicker1.Text); DateTime date2 = Convert.ToDateTime(DatePicker2.Text); string strTimeC = "", endTimeC = ""; string strTimeD = "", endTimeD = ""; string onlineStatus = "%"; if (ckb_onlineStatus.Checked) { onlineStatus = "0"; } strTimeD = date1.ToString("yyyy-MM-dd") + " " + "12:00"; endTimeD = date2.AddDays(1).ToString("yyyy-MM-dd") + " " + "12:00"; strTimeC = date1.ToString("yyyy-MM-dd"); endTimeC = date2.AddDays(1).ToString("yyyy-MM-dd"); string sqlCmd = "select A.*,B.bindTotal,T.orderno,T.plancount,T.itemNo,T.itemParm,T.endTime,T.itemName,(lswlex_c2+'*'+lswlex_c3+'*'+lswlex_c4) as itemParm_size,"; sqlCmd += " lswlex_c9 as itemParm_weight,lswlex_c5 as itemParm_color,lswlex_c7 as itemParm_sfjz from"; sqlCmd += " (select isnull(C.tradeNo,D.tradeNo) as tradeNo,isnull(bindSumC,0) as bindSumC,isnull(bindSumCB,0) as bindSumCB,isnull(bindSumCS,0) as bindSumCS,"; sqlCmd += " isnull(bindSumD,0) as bindSumD,isnull(bindSumDB,0) as bindSumDB,isnull(bindSumDS,0) as bindSumDS,"; sqlCmd += " (isnull(bindSumC,0)+isnull(bindSumD,0)) as BindSum,(isnull(bindSumCB,0)+isnull(bindSumDB,0)) as BindSumB,(isnull(bindSumCS,0)+isnull(bindSumDS,0)) as BindSumS from "; sqlCmd += " (select dd.bindSumD,dd.tradeNo,isnull(ddb.bindSumDB,0) as bindSumDB,(bindSumD - isnull(ddb.bindSumDB,0)) as bindSumDS from "; sqlCmd += " (select sum(bindQty) as bindSumD,tradeNo from PLM_Serials_BindBarCode where createTime>'" + strTimeD + "' and createtime<'" + endTimeD + "'"; sqlCmd += " and excUser='******' and lineId like '" + ddl_line.SelectedValue + "' group by tradeNo) dd"; sqlCmd += " left join "; sqlCmd += " (select sum(bindQty) as bindSumDB,tradeNo from PLM_Serials_BindBarCode where createTime>'" + strTimeD + "' and createtime<'" + endTimeD + "' "; sqlCmd += " and excUser='******' and lineId like '" + ddl_line.SelectedValue + "' and barCode like 'B%' group by tradeNo) ddb on dd.tradeNo=ddb.tradeNo) D"; sqlCmd += " full join "; sqlCmd += " (select cc.bindSumC,cc.tradeNo,isnull(ccb.bindSumCB,0) as bindSumCB,(bindSumC - isnull(ccb.bindSumCB,0)) as bindSumCS from "; sqlCmd += " (select sum(bindQty) as bindSumC,tradeNo from PLM_Serials_BindBarCode where createTime>'" + strTimeC + "' and createtime<'" + endTimeC + "' "; sqlCmd += " and excUser='******' and lineId like '" + ddl_line.SelectedValue + "' group by tradeNo) cc"; sqlCmd += " left join "; sqlCmd += " (select sum(bindQty) as bindSumCB,tradeNo from PLM_Serials_BindBarCode where createTime>'" + strTimeC + "' and createtime<'" + endTimeC + "' "; sqlCmd += " and excUser='******' and lineId like '" + ddl_line.SelectedValue + "' and barCode like 'B%' group by tradeNo) ccb on cc.tradeNo=ccb.tradeNo) C"; sqlCmd += " ON D.tradeNo=C.tradeNo) A"; sqlCmd += " left join (select sum(bindQty) as bindTotal,tradeno from PLM_Serials_BindBarCode group by tradeNo) B on A.tradeno=B.tradeNo"; sqlCmd += " left join PLM_Product_OnLine t on a.tradeno=t.id"; sqlCmd += " left join view_plm_lswlex on lswlex_wlbh=itemNo"; sqlCmd += " where onlineStatus like '" + onlineStatus + "'"; DataTable dt = new DataTable(); SqlSel.GetSqlSel(ref dt, sqlCmd); Grid1.DataSource = dt; Grid1.DataBind(); getSummaryData(); } catch (Exception ex) { Alert.Show(ex.Message); } }
private void bindProj() { string sqlCmd = "select * from OA_sys_Project"; DataTable dt = new DataTable(); SqlSel.GetSqlSel(ref dt, sqlCmd); ddl_proj.DataTextField = "projName"; ddl_proj.DataValueField = "id"; ddl_proj.DataSource = dt; ddl_proj.DataBind(); ddl_proj.Items.Insert(0, new FineUI.ListItem("所有项目", "%")); }
private void bindGrid2(int roleId) { string sqlCmd = "select (case when menuId is null then 0 else 1 end ) as CanRead,* from OA_Sys_Menu left join ("; sqlCmd += "select menuId from OA_Sys_RoleMenu "; sqlCmd += "where roleId='" + roleId + "') a on menuId=OA_Sys_Menu.id where enabled=1"; DataTable dt = new DataTable(); SqlSel.GetSqlSel(ref dt, sqlCmd); Grid2.DataSource = dt; Grid2.DataBind(); }
//加载文档分类 private void bindDocType() { string sqlCmd = "select * from OA_DocMgmt_DocType where pid='0' and stat='1'"; DataTable dt = new DataTable(); SqlSel.GetSqlSel(ref dt, sqlCmd); ddl_docType.DataTextField = "type_name"; ddl_docType.DataValueField = "id"; ddl_docType.DataSource = dt; ddl_docType.DataBind(); this.ddl_docType.Items.Insert(0, new FineUI.ListItem("所有类型", "%")); }
//加载已审批用车申请 private void loadReqList() { //加载所有已审批完成行政未作废的用车申请 string sqlCmd = "select (select A.chineseName + '/' + B.deptName from OA_Sys_EmployeeInfo A left join OA_sys_department B on A.deptId=B.id where A.id=OA_Car_Main.reqMan) AS reqName,*"; sqlCmd += ",(case when adminRegister='0' then '待发车' when adminRegister='1' then '待返还' end) as curStatus from OA_Car_Main where CurrentStepId='0' and adminRegister <> '-1' and adminRegister <> '2' order by adminRegister"; DataTable dt = new DataTable(); SqlSel.GetSqlSel(ref dt, sqlCmd); Grid1.DataSource = dt; Grid1.DataBind(); }
private void bindGrid() { string sqlCmd = "select b.title as parentTitle,a.title,a.ImageUrl,a.NavigateUrl,a.Remark,a.id "; sqlCmd += "from OA_Sys_Menu a left join OA_Sys_Menu b "; sqlCmd += "on b.id=a.ParentMenuId where a.enabled=1"; DataTable dt = new DataTable(); SqlSel.GetSqlSel(ref dt, sqlCmd); Grid1.DataSource = dt; Grid1.DataBind(); }
// private void bindGrid(int startIndex) { string sqlCmd = "select * from t_trade_orderReg_orig left join "; sqlCmd += "g_cfg_shoplist on t_trade_orderReg_orig.shopID_orig=g_cfg_shoplist.shopID "; sqlCmd += "where t_trade_orderReg_orig.id>" + startIndex + " order by tradeNo_orig"; DataTable dt = new DataTable(); SqlSel.GetSqlSel(ref dt, sqlCmd); Grid1.DataSource = dt; Grid1.DataBind(); }
private void ProjMemberBind(string projId) { if (!string.IsNullOrEmpty(projId)) { string sqlCmd = "select A.id,B.chineseName,C.deptName,A.isManager from OA_Sys_ProjMember A left join OA_Sys_EmployeeInfo B on A.userId=B.id "; sqlCmd += "left join OA_sys_department C on C.id=B.deptId where A.projId='" + projId + "'"; DataTable dt = new DataTable(); SqlSel.GetSqlSel(ref dt, sqlCmd); Grid1.DataSource = dt; Grid1.DataBind(); } }
protected void Grid1_RowCommand(object sender, FineUI.GridCommandEventArgs e) { if (e.CommandName == "Delete") { int rowID = Convert.ToInt32(Grid1.DataKeys[e.RowIndex][0]); string sqlCmd = "update OA_Announcement set isDeleted='1' where id='" + rowID + "'"; SqlSel.ExeSql(sqlCmd); bindGrid(); Alert.ShowInTop("删除成功!"); } }
private void loadProperty() { string sqlCmd = "select (propertyNo + '-' + propertyName) as propertyName from OA_Property_Register where propertyType='1' and UseStatus='1' order by propertyName";//类型为办公用品且可用的类目 DataTable dt = new DataTable(); SqlSel.GetSqlSel(ref dt, sqlCmd); ddl_propertyName.DataValueField = "propertyName"; ddl_propertyName.DataTextField = "propertyName"; ddl_propertyName.DataSource = dt; ddl_propertyName.DataBind(); }
private void bindCheckDetail(string formId, string formDataName) { string sqlCmd = "select C.chineseName,B.dealAdc,B.optTime,isnull((case when dealType='1' then '同意' when dealType='-1' then '否决' end),'未阅') as nodeAdc from OA_Sys_step_empList A left join OA_Sys_NodeDetail B on A.ID=B.NodeId "; sqlCmd += "left join OA_Sys_EmployeeInfo C on C.id= A.userid "; sqlCmd += "where A.formId='" + formId + "' and formDataName='" + formDataName + "'"; DataTable dt = new DataTable(); SqlSel.GetSqlSel(ref dt, sqlCmd); Grid1.DataSource = dt; Grid1.DataBind(); }
//插入附件信息 private void InsertFiles(string formId) { string sqlCmd = ""; foreach (GridRow gr in Grid1.Rows) { string fileName = gr.DataKeys[0].ToString(); //服务器文件名 string realFileName = gr.Values[1].ToString(); //原文件名 sqlCmd = "insert into OA_Sys_files (FormId,fileName,realFileName,formDataName) values ('" + formId + "','" + fileName + "','" + realFileName + "','OA_Announcement')"; SqlSel.ExeSql(sqlCmd); } }
private void loadDeptInfo() { string sqlCmd = "select * from OA_sys_department order by deptName"; DataTable dt = new DataTable(); SqlSel.GetSqlSel(ref dt, sqlCmd); ddl_dept.DataTextField = "deptName"; ddl_dept.DataValueField = "id"; ddl_dept.DataSource = dt; ddl_dept.DataBind(); this.ddl_dept.Items.Insert(0, new FineUI.ListItem("所有部门", "%")); }
private void loadUserInfo(string deptId) { string sqlCmd = "select * from OA_Sys_EmployeeInfo where deptId like '" + deptId + "'"; DataTable dt = new DataTable(); SqlSel.GetSqlSel(ref dt, sqlCmd); ddl_emp.DataTextField = "chineseName"; ddl_emp.DataValueField = "id"; ddl_emp.DataSource = dt; ddl_emp.DataBind(); this.ddl_emp.Items.Insert(0, new FineUI.ListItem("所有人员", "%")); }
private void loadHisReqList(string userId) { string sqlCmd = "select A.*,isnull(B.stepName,'已完成') as checkStatus,C.flowName from OA_Fines_Main A"; sqlCmd += " left join OA_sys_flow C ON C.ID=A.FlowId"; sqlCmd += " LEFT JOIN OA_Sys_Flow_Step B ON A.CurrentStepID=B.id WHERE reqMan='" + userId + "' order by reqDte desc"; DataTable dt = new DataTable(); SqlSel.GetSqlSel(ref dt, sqlCmd); Grid1.DataSource = dt; Grid1.DataBind(); }
protected void btn_search_Click(object sender, EventArgs e) { string sqlCmd = "select A.*,c.chineseName,d.deptName from OA_Attendence_Record A"; sqlCmd += " left join OA_Sys_EmployeeInfo c on c.id=a.userId left join OA_sys_department d on d.id=c.deptId"; sqlCmd += " where recordDate>='" + DatePicker1.Text + "' and recordDate<='" + DatePicker2.Text + "' order by recordDate"; DataTable dt = new DataTable(); SqlSel.GetSqlSel(ref dt, sqlCmd); Grid1.DataSource = dt; Grid1.DataBind(); }
//加载产线信息 private void bindLineInfo() { string sqlCmd = "select * from PLM_Product_Line where LineStatus=1"; DataTable dt = new DataTable(); SqlSel.GetSqlSel(ref dt, sqlCmd); ddl_line.DataValueField = "id"; ddl_line.DataTextField = "LineName"; ddl_line.DataSource = dt; ddl_line.DataBind(); this.ddl_line.Items.Insert(0, new FineUI.ListItem("请选择", "%")); }
//加载待盖章的用印申请 private void loadReqList() { //加载所有已审批完成盖章的用印申请 string sqlCmd = "select (select A.chineseName + '/' + B.deptName from OA_Sys_EmployeeInfo A left join OA_sys_department B on A.deptId=B.id where A.id=reqMan) AS reqName,*"; sqlCmd += " from OA_UseStamp_Main where CurrentStepId='0' and adminRegister = '0' order by reqDte desc"; DataTable dt = new DataTable(); SqlSel.GetSqlSel(ref dt, sqlCmd); Grid1.DataSource = dt; Grid1.DataBind(); }
protected void btnSubmit_Click(object sender, EventArgs e) { try { if (string.IsNullOrEmpty(numbbox_days.Text) & string.IsNullOrEmpty(numbbox_hours.Text)) { Alert.ShowInTop("天数和小时至少填一项。"); return; } decimal days = 0; decimal hours = 0; if (!string.IsNullOrEmpty(numbbox_days.Text)) { days = Convert.ToDecimal(numbbox_days.Text); } if (!string.IsNullOrEmpty(numbbox_hours.Text)) { hours = Convert.ToDecimal(numbbox_hours.Text); } string stepId = getStepId(ddl_flow.SelectedValue);//获取步骤id string sqlCmd = "insert into OA_Leave_Main ([reqMan],[reqDte],[strTime],[endTime],[lastDays],[leaveReason],[FlowId],[CurrentStepID],[workReplacer],[lastHours]) values "; sqlCmd += "('" + GetUser() + "','" + DateTime.Now + "','" + DatePicker1.Text + " " + TimePicker1.Text + "','" + DatePicker2.Text + " " + TimePicker2.Text + "','" + days + "',"; sqlCmd += "'" + TextArea_desc.Text.Trim() + "','" + ddl_flow.SelectedValue + "','" + stepId + "','" + txb_replacer.Text.Trim() + "','" + hours + "')"; int exeCount = SqlSel.ExeSql(sqlCmd); if (exeCount > 0) { //取当前单据id sqlCmd = "select max(id) from OA_Leave_Main"; string formId = SqlSel.GetSqlScale(sqlCmd).ToString(); //插入附件信息表 InsertFiles(formId); Alert.Show("提交成功!"); //表单重置 SimpleForm1.Reset(); Grid1.DataSource = null; Grid1.DataBind(); //推送信息至相关审批人 pushMessage(stepId, "OA_Leave_Main", formId, "考勤申请"); } else { Alert.Show("提交失败!"); return; } } catch (Exception ex) { Alert.Show(ex.Message); } }
/// <summary> /// 获取审批信息 /// </summary> /// <param name="formId">表单id</param> /// <param name="formDataName">表单数据库名</param> /// <returns></returns> public static DataTable getCheckDtl(string formId, string formDataName) { string sqlCmd = "select (D.deptName+ '/' +C.chineseName) as chineseName,B.dealAdc,B.optTime,"; sqlCmd += "isnull((case when dealType='1' then '同意' when dealType='-1' then '否决' end),'未阅') as nodeAdc "; sqlCmd += "from OA_Sys_step_empList A left join OA_Sys_NodeDetail B on A.ID=B.NodeId "; sqlCmd += "left join OA_Sys_EmployeeInfo C on C.id= A.userid left join OA_Sys_department D on D.id=C.deptId "; sqlCmd += "where A.formId='" + formId + "' and formDataName='" + formDataName + "' order by optTime desc"; DataTable dt = new DataTable(); SqlSel.GetSqlSel(ref dt, sqlCmd); return(dt); }
protected void btn_save_Click(object sender, EventArgs e) { if (!string.IsNullOrEmpty(recvId)) { string sqlCmd = "insert into OA_Sys_Flow_Step (flowId,stepName,stepOrderNo) values ("; sqlCmd += "'" + recvId + "','" + txb_stepName.Text.Trim() + "','" + getMaxSort() + "')"; SqlSel.ExeSql(sqlCmd); Alert.ShowInParent("编辑成功,请退出当前窗口!"); SimpleForm1.Reset(); //PageContext.RegisterStartupScript(ActiveWindow.GetHideReference()); } }
private void loadAnncInfo(string anncId) { string sqlCmd = "select * from OA_Announcement where id='" + anncId + "'"; DataTable dt = new DataTable(); SqlSel.GetSqlSel(ref dt, sqlCmd); if (dt.Rows.Count > 0) { Label_title.Text = dt.Rows[0]["AnncTitle"].ToString(); //公告标题 Label_date.Text = dt.Rows[0]["CreateTime"].ToString(); //发布时间 label_context.Text = dt.Rows[0]["AnncContext"].ToString(); //公告内容 } }
public void ProcessRequest(HttpContext context) { string rowId = context.Request.QueryString["id"]; string sqlCmd = "select a.orderCount,b.orderNo,b.planSum,b.planProdDate from PLM_Product_Rel a left join View_PLM_ERPData b "; sqlCmd += "on a.orderid=b.productSN where prodId='" + rowId + "'"; DataTable dt = new DataTable(); SqlSel.GetSqlSel(ref dt, sqlCmd); context.Response.ContentType = "text/plain"; context.Response.Write(Newtonsoft.Json.JsonConvert.SerializeObject(dt, new DataTableConverter())); }
// private void checkBoxListBind(string userId) { DataTable dt = new DataTable(); string sqlCmd = "select * from OA_Sys_UserRole where userid='" + userId + "'"; SqlSel.GetSqlSel(ref dt, sqlCmd); string[] roleArray = new string[dt.Rows.Count];//创建一个角色数组 for (int i = 0; i < dt.Rows.Count; i++) { roleArray[i] = dt.Rows[i]["roleId"].ToString(); } ckb_roleList.SelectedValueArray = roleArray; }
protected void tgb_wlbh_TriggerClick(object sender, EventArgs e) { string sqlCmd = "select (LSWLEX_C2+'*'+LSWLEX_C3+'*'+LSWLEX_C4+','+LSWLEX_C9+','+LSWLEX_C5+','+LSWLEX_C1+','+LSWLEX_C8+','+LSWLEX_C7) AS RequireParm,isnull(orderSum,0) as historySum,* from View_PLM_ERPData a "; sqlCmd += "left join (select SUM(OrderCount) as orderSum,orderid from PLM_Product_Rel group by orderid) b on b.orderid=a.productSN "; sqlCmd += "left join view_plm_lswlex d on d.lswlex_wlbh=a.itemNo "; sqlCmd += "where itemNo ='" + tgb_wlbh.Text + "' order by planProdDate desc"; DataTable dt = new DataTable(); SqlSel.GetSqlSel(ref dt, sqlCmd); Grid2.DataSource = dt; Grid2.DataBind(); }
//获取菜单序列号 private int getSortIndex(string parentId) { int sortIndex = 0; DataTable dt = new DataTable(); string sqlCmd = "select max(sortIndex) from OA_Sys_Menu where ParentMenuId='" + parentId + "'"; string res = SqlSel.GetSqlScale(sqlCmd).ToString(); if (!string.IsNullOrEmpty(res)) { sortIndex = Convert.ToInt32(res); } return(sortIndex); }
//ERP查询结果 private void bindGrid(string tradeNo) { string sqlCmd = "select 0 as canRead,(LSWLEX_C2+'*'+LSWLEX_C3+'*'+LSWLEX_C4+','+LSWLEX_C9+','+LSWLEX_C5+','+LSWLEX_C1+','+LSWLEX_C8+','+LSWLEX_C7) AS RequireParm,isnull(orderSum,0) as historySum,* from View_PLM_ERPData a "; sqlCmd += "left join (select SUM(OrderCount) as orderSum,orderid from PLM_Product_Rel group by orderid) b on orderid=productSN "; sqlCmd += "left join view_plm_lswlex d on d.lswlex_wlbh=a.itemNo "; sqlCmd += "where orderNo like '%" + tradeNo + "%' order by planProdDate desc"; DataTable dt = new DataTable(); SqlSel.GetSqlSel(ref dt, sqlCmd); Grid2.DataSource = dt; Grid2.DataBind(); }