//筛选与用户相关的项目
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();
}
