public DataSet GetData() { try { DataSet ds = new DataSet(); DataSet ds_Return = new DataSet(); //根据用户登录信息取到权限信息 MDM_System_User_Authorization_GetListBySession_ID cls = new MDM_System_User_Authorization_GetListBySession_ID(); cls.hzyMessage = this.hzyMessage; DataSet dsAUZ = cls.GetData(); string strComList = ""; if (dsAUZ.Tables[0].Rows.Count > 0) { DataRow[] listAuzCom = dsAUZ.Tables[0].Select("Authorization_Name" + " like'%COM%'"); for (int k = 0; k < listAuzCom.Length; k++) { if (k > 0) { strComList += ","; } string strCom = listAuzCom[k]["Authorization_Name"].ToString(); strComList += strCom.Substring(strCom.Length - 1); } } string strXML = ""; strXML = m_request.Tables[0].Rows[0]["XML"].ToString(); ds = Common.Common.GetDSByExcelXML(strXML); DataTable dt_OPTYPE = ds.Tables["OPTYPE"]; for (int i = 0; i < ds.Tables["OPTYPE"].Rows.Count; i++) { //判断款号是否存在 if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "CHECKCLOTHING") { string strSQL = @" SELECT count(1) AS COUNT FROM j_clothing WITH(NOLOCK) WHERE 1=1" + m_hzyPageInfo.Where; //ds_Return = conn.GetDataSetForPageList(strSQL + strWhereTmp, new string[0],m_hzyPageInfo); ds_Return = conn.GetDataSet(strSQL); } if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "GETALLCLOTHING") { string strSQL = @" SELECT * FROM j_clothing WITH(NOLOCK) WHERE 1=1" + m_hzyPageInfo.Where; //ds_Return = conn.GetDataSetForPageList(strSQL + strWhereTmp, new string[0],m_hzyPageInfo); ds_Return = conn.GetDataSet(strSQL); } } return ds_Return; } catch { conn.RollbackTransaction(); throw; } }
public DataSet GetData() { try { DataSet ds = new DataSet(); DataSet ds_Return = new DataSet(); //根据用户登录信息取到权限信息 MDM_System_User_Authorization_GetListBySession_ID cls = new MDM_System_User_Authorization_GetListBySession_ID(); cls.hzyMessage = this.hzyMessage; DataSet dsAUZ = cls.GetData(); string strComList = ""; if (dsAUZ.Tables[0].Rows.Count > 0) { DataRow[] listAuzCom = dsAUZ.Tables[0].Select("Authorization_Name" + " like'%COM%'"); for (int k = 0; k < listAuzCom.Length; k++) { if (k > 0) { strComList += ","; } string strCom = listAuzCom[k]["Authorization_Name"].ToString(); strComList += strCom.Substring(strCom.Length - 1); } } else //店铺 { strComList = hzyMessage.User_Name_CN.Substring(0, 1); if (Convert.ToChar(strComList.ToUpper()) > 'Z' || hzyMessage.User_Name_CN.Length < 8) { strComList = ""; } else { strComList = hzyMessage.User_Name_CN.Substring(0, 8); } if (strComList== "") { throw new Exception("你没有权限,请联系管理员!"); } } string strXML = ""; strXML = m_request.Tables[0].Rows[0]["XML"].ToString(); ds = Common.Common.GetDSByExcelXML(strXML); DataTable dt_OPTYPE = ds.Tables["OPTYPE"]; for (int i = 0; i < ds.Tables["OPTYPE"].Rows.Count; i++) { #region GET if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "GET") { string strSQL = @" SELECT * FROM ( SELECT CASE WHEN LEFT(t1.depotid, 1) = 'N' THEN '纳卉' WHEN LEFT(t1.depotid, 1) = 'A' THEN '华中' WHEN LEFT(t1.depotid, 1) = 'Z' THEN '郑州' WHEN LEFT(t1.depotid, 1) = 'K' THEN '诺珂' WHEN LEFT(t1.depotid, 1) = 'D' THEN '贝爱' WHEN LEFT(t1.depotid, 1) = 'E' THEN '雅莹' WHEN LEFT(t1.depotid, 1) = 'X' THEN '新疆' WHEN LEFT(t1.depotid, 1) = 'W' THEN '维姬' WHEN LEFT(t1.depotid, 1) = 'J' THEN '卓莱雅' WHEN LEFT(t1.depotid, 1) = 'M' THEN '西南' WHEN LEFT(t1.depotid, 1) = 'L' THEN '华南' WHEN LEFT(t1.depotid, 1) = 'H' THEN '泓雅' WHEN LEFT(t1.depotid, 1) = 'G' THEN '雅斓' WHEN LEFT(t1.depotid, 1) = 'F' THEN '东北' END AS 公司 , t4.typename 大类名称 , t2.name 维修商 , t3.parentid , t3.typename 小类名称 , t2.linkman orglinkman, t2.tel orgtel, t1.billid , t1.depotid , t1.depotName , t1.j_form, t1.typeid , t1.content , t1.img , t1.chk_sure , CONVERT(VARCHAR(100), t1.chk_date, 23) AS chk_date , t1.chk_name , CONVERT(VARCHAR(100), t1.fb_date, 23) AS fb_date , t1.fb_name , t1.fb_comment , t1.fb_content , CONVERT(VARCHAR(100), t1.schemaenddate, 23) AS schemaenddate , t1.isquality , t1.ischangeschema , t1.changecause , t1.orgid , CASE t1.state WHEN 0 THEN '未上报' WHEN 1 THEN '已上报' WHEN 2 THEN '已跟进' WHEN 3 THEN '已退回' WHEN 4 THEN '施工超期' WHEN 5 THEN '完成' WHEN 6 THEN '已更改' WHEN 7 THEN '处理超期' END AS state , t1.end_crdate , CONVERT(VARCHAR(100), t1.enddate, 23) AS enddate , t1.endday , t1.opinion , t1.comment , t1.linkman , t1.linkTel , t1.end_crname , t1.Cost_sure , t1.Cost_crname , t1.Cost_crdate , t1.createtime , t1.crname , t1.updatetime FROM [mat].[mat_bill] t1 LEFT JOIN mat.mat_org t2 ON t1.orgid = t2.orgid LEFT JOIN mat.mat_type t3 ON t1.typeid = t3.typeid LEFT JOIN mat.mat_type t4 ON t3.parentid = t4.typeid ) t1 WHERE 1=1 " + m_hzyPageInfo.Where; string strWhereTmp = "AND (t1.depotid LIKE '" + strComList.Replace(",", "%' OR t1.depotid LIKE '") + "%')"; ds_Return = conn.GetDataSetForPageList(strSQL+strWhereTmp, new string[0], m_hzyPageInfo); } #endregion #region INSERT if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "INSERT") { if (Convert.ToChar(hzyMessage.User_Name_CN.Substring(0, 1).ToUpper()) > 'Z') { int nAuzCount = dsAUZ.Tables[0].Select("Authorization_Name='MAT_MENU_总部维修跟进' OR Authorization_Name='admin'").Length; if (nAuzCount < 1) { throw new Exception("你没有权限,请联系管理员!"); } } DataTable dt_List = ds.Tables["List"]; string strDepotid = dt_List.Rows[0]["depotid"].ToString(); if (strDepotid == "") { throw new Exception("数据异常,缺少店铺编号,请联系管理员!"); } string strDepotid_Sub = strDepotid.Substring(0,1); bool bCom = strComList.Contains(strDepotid_Sub); if (!bCom) { throw new Exception("你没有权限,请联系管理员!"); } bool bResult = false; ArrayList listColumn = new ArrayList(); listColumn.Add("depotid"); listColumn.Add("depotname"); listColumn.Add("typeid"); listColumn.Add("content"); listColumn.Add("img"); listColumn.Add("comment"); listColumn.Add("linkman"); listColumn.Add("linkTel"); listColumn.Add("j_form"); dt_List.Columns.Remove("parentid"); DataTable dt_Data = new DataTable(); for (int k = 0; k < listColumn.Count; k++) { dt_Data.Columns.Add(listColumn[k].ToString()); } for (int k = 0; k < dt_List.Rows.Count; k++) { DataRow dr = dt_Data.NewRow(); for (int l = 0; l < listColumn.Count; l++) { if (k == 0 && !dt_List.Columns.Contains(listColumn[l].ToString())) { throw new Exception(listColumn[l].ToString() + "列不能为空"); } dr[listColumn[l].ToString()] = dt_List.Rows[k][listColumn[l].ToString()].ToString(); } dt_Data.Rows.Add(dr); dt_Data.Columns.Add("crname"); dt_Data.Columns.Add("createtime"); dt_Data.Columns.Add("state"); dt_Data.Rows[0]["crname"] = hzyMessage.User_Name_CN; dt_Data.Rows[0]["state"] = "0"; } conn.LockTableList.Add("[mat].[mat_bill]"); conn.BeginTransaction(); bResult = conn.Insert("[mat].[mat_bill]", dt_Data); conn.CommitTransaction(); if (bResult) { dt_EditResult.Rows.Add(new object[] { true, "保存成功" }); } else { dt_EditResult.Rows.Add(new object[] { false, "保存失败" }); } ds_Return.Tables.Add(dt_EditResult); } #endregion #region UPDATE_SUBMIT 上报 else if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "UPDATE_SUBMIT") { if (Convert.ToChar(hzyMessage.User_Name_CN.Substring(0, 1).ToUpper()) > 'Z') { int nAuzCount = dsAUZ.Tables[0].Select("Authorization_Name='MAT_MENU_总部维修跟进' OR Authorization_Name='admin'").Length; if (nAuzCount < 1) { throw new Exception("你没有权限,请联系管理员!"); } } DataTable dt_List = ds.Tables["List"]; string strBillid = dt_List.Rows[0]["billid"].ToString(); string strDepotid = dt_List.Rows[0]["depotid"].ToString(); if (strDepotid =="") { throw new Exception("数据异常,缺少店铺编号,请联系管理员!"); } string strDepotid_Sub = strDepotid.Substring(0, 1); bool bCom = strComList.Contains(strDepotid_Sub); if (!bCom) { throw new Exception("你没有权限,请联系管理员!"); } DateTime timeNow = DateTime.Now; dt_List.Rows[0]["chk_date"] = timeNow; dt_List.Columns.Remove("billid"); dt_List.Columns.Remove("depotid"); dt_List.Columns.Add("chk_name"); dt_List.Rows[0]["chk_name"] = hzyMessage.User_Name_CN; bool bResult = false; conn.LockTableList.Add("[mat].[mat_bill]"); conn.BeginTransaction(); bResult = conn.Update("[mat].[mat_bill]", dt_List, "billid=" + strBillid); conn.CommitTransaction(); if (bResult) { dt_EditResult.Rows.Add(new object[] { true, "上报成功" }); } else { dt_EditResult.Rows.Add(new object[] { false, "上报失败" }); } ds_Return.Tables.Add(dt_EditResult); } #endregion #region REPLY_UPDATE else if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "REPLY_UPDATE") { int nAuzCount = dsAUZ.Tables[0].Select("Authorization_Name='MAT_MENU_总部维修处理' OR Authorization_Name='admin'").Length; if (nAuzCount < 1) { throw new Exception("你没有权限,请联系管理员!"); } DataTable dt_List = ds.Tables["List"]; string strDepotid = dt_List.Rows[0]["depotid"].ToString(); if (strDepotid == "") { throw new Exception("数据异常,缺少店铺编号,请联系管理员!"); } string strDepotid_Sub = strDepotid.Substring(0, 1); bool bCom = strComList.Contains(strDepotid_Sub); if (!bCom) { throw new Exception("你没有权限,请联系管理员!"); } string strBillid = dt_List.Rows[0]["billid"].ToString(); DateTime timeNow = DateTime.Now; dt_List.Rows[0]["fb_date"] = timeNow; dt_List.Rows[0]["fb_name"] = hzyMessage.User_Name_CN; string strSchemaenddate = dt_List.Rows[0]["schemaenddate"].ToString(); if (strSchemaenddate == "") { dt_List.Columns.Remove("schemaenddate"); } dt_List.Columns.Remove("billid"); dt_List.Columns.Remove("depotid"); bool bResult = false; conn.LockTableList.Add("[mat].[mat_bill]"); conn.BeginTransaction(); bResult = conn.Update("[mat].[mat_bill]", dt_List, "billid=" + strBillid); conn.CommitTransaction(); if (bResult) { dt_EditResult.Rows.Add(new object[] { true, "单据" + strBillid + "回复成功" }); } else { dt_EditResult.Rows.Add(new object[] { false, "回复失败" }); } ds_Return.Tables.Add(dt_EditResult); } #endregion #region CHANGE_UPDATE else if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "CHANGE_UPDATE") { int nAuzCount = dsAUZ.Tables[0].Select("Authorization_Name='MAT_MENU_总部维修处理' OR Authorization_Name='admin'").Length; if (nAuzCount < 1) { throw new Exception("你没有权限,请联系管理员!"); } DataTable dt_List = ds.Tables["List"]; string strDepotid = dt_List.Rows[0]["depotid"].ToString(); if (strDepotid == "") { throw new Exception("数据异常,缺少店铺编号,请联系管理员!"); } string strDepotid_Sub = strDepotid.Substring(0, 1); bool bCom = strComList.Contains(strDepotid_Sub); if (!bCom) { throw new Exception("你没有权限,请联系管理员!"); } bool bResult = false; conn.LockTableList.Add("[mat].[mat_bill]"); conn.LockTableList.Add("[mat].[mat_billsub]"); conn.BeginTransaction(); DataTable dt_List_for_insert = ds.Tables["List"]; DataTable dt_List_for_update = ds.Tables["List"]; //insert DataTable dt_List_insert = new DataTable(); dt_List_insert.Columns.Add("schemaenddate"); dt_List_insert.Columns.Add("billid"); dt_List_insert.Columns.Add("changecause"); dt_List_insert.Columns.Add("createtime"); dt_List_insert.Columns.Add("updatetime"); for (int j = 0; j < dt_List_for_insert.Rows.Count; j++) { string strSchemaenddate = dt_List_for_insert.Rows[j]["schemaenddate_change"].ToString(); string strBillid_insert = dt_List_for_insert.Rows[j]["billid"].ToString(); string strChangecause = dt_List_for_insert.Rows[j]["changecause"].ToString(); dt_List_insert.Rows.Add(new object[] { strSchemaenddate, strBillid_insert, strChangecause, "", "" }); } bResult = conn.Insert("[mat].[mat_billsub]", dt_List_insert); //update string strBillid = dt_List_for_update.Rows[0]["billid"].ToString(); string strSchemaenddate_Update = dt_List_for_update.Rows[0]["schemaenddate_change"].ToString(); dt_List_for_update.Columns.Add("schemaenddate"); dt_List_for_update.Rows[0]["schemaenddate"] = strSchemaenddate_Update; dt_List_for_update.Columns.Remove("billid"); dt_List_for_update.Columns.Remove("schemaenddate_change"); dt_List_for_update.Columns.Add("ischangeschema"); dt_List_for_update.Rows[0]["ischangeschema"] = "是"; dt_List_for_update.Columns.Remove("depotid"); bResult = conn.Update("[mat].[mat_bill]", dt_List_for_update, "billid=" + strBillid); conn.CommitTransaction(); if (bResult) { dt_EditResult.Rows.Add(new object[] { true, "单据" + strBillid + "更改计划成功" }); } else { dt_EditResult.Rows.Add(new object[] { false, "更改计划失败" }); } ds_Return.Tables.Add(dt_EditResult); } #endregion #region SAVE_UPDATE else if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "SAVE_UPDATE") { if (Convert.ToChar(hzyMessage.User_Name_CN.Substring(0, 1).ToUpper()) > 'Z') { int nAuzCount = dsAUZ.Tables[0].Select("Authorization_Name='MAT_MENU_总部维修跟进' OR Authorization_Name='admin'").Length; if (nAuzCount < 1) { throw new Exception("你没有权限,请联系管理员!"); } } DataTable dt_List = ds.Tables["List"]; string strDepotid = dt_List.Rows[0]["depotid"].ToString(); if (strDepotid == "") { throw new Exception("数据异常,缺少店铺编号,请联系管理员!"); } string strDepotid_Sub = strDepotid.Substring(0, 1); bool bCom = strComList.Contains(strDepotid_Sub); if (!bCom) { throw new Exception("你没有权限,请联系管理员!"); } string strBillid = dt_List.Rows[0]["billid"].ToString(); DateTime timeNow = DateTime.Now; dt_List.Columns.Remove("depotid"); dt_List.Columns.Remove("billid"); dt_List.Columns.Add("end_crdate"); dt_List.Columns.Add("end_crname"); dt_List.Rows[0]["end_crdate"] = timeNow; dt_List.Rows[0]["end_crname"] = hzyMessage.User_Name_CN; bool bResult = false; conn.LockTableList.Add("[mat].[mat_bill]"); conn.BeginTransaction(); bResult = conn.Update("[mat].[mat_bill]", dt_List, "billid=" + strBillid); conn.CommitTransaction(); if (bResult) { dt_EditResult.Rows.Add(new object[] { true, "单据" + strBillid + "确认完成" }); } else { dt_EditResult.Rows.Add(new object[] { false, "确认失败" }); } ds_Return.Tables.Add(dt_EditResult); } #endregion #region DELETE else if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "DELETE") { if (Convert.ToChar(hzyMessage.User_Name_CN.Substring(0, 1).ToUpper()) >'Z') { int nAuzCount = dsAUZ.Tables[0].Select("Authorization_Name='MAT_MENU_总部维修跟进' OR Authorization_Name='admin'").Length; if (nAuzCount < 1) { throw new Exception("你没有权限,请联系管理员!"); } } DataTable dt_List = ds.Tables["List"]; string strDepotid = dt_List.Rows[0]["depotid"].ToString(); if (strDepotid == "") { throw new Exception("数据异常,缺少店铺编号,请联系管理员!"); } string strDepotid_Sub = strDepotid.Substring(0, 1); bool bCom = strComList.Contains(strDepotid_Sub); if (!bCom) { throw new Exception("你没有权限,请联系管理员!"); } bool bResult = false; conn.LockTableList.Add("[mat].[mat_bill]"); conn.BeginTransaction(); string strState = dt_List.Rows[0]["state"].ToString(); string strBillid = dt_List.Rows[0]["billid"].ToString(); //if (strState != "未上报" && strState != "已上报") //{ // throw new Exception("你删除的数据不属于未上报信息!"); //} if (strState != "未上报" && strState != "已上报") { throw new Exception("只能删除未上报或者已上报单据!"); } dt_List.Columns.Remove("billid"); dt_List.Columns.Remove("depotid"); bResult = conn.Delete("[mat].[mat_bill]", "billid=" + strBillid); conn.CommitTransaction(); if (bResult) { dt_EditResult.Rows.Add(new object[] { true, "单据" + strBillid + "删除成功" }); } else { dt_EditResult.Rows.Add(new object[] { false, "单据" + strBillid + "删除失败" }); } ds_Return.Tables.Add(dt_EditResult); } #endregion #region INFO_UPDATE else if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "INFO_UPDATE") { DataTable dt_List = ds.Tables["List"]; bool bResult = false; string strlinkman_edit = dt_List.Rows[0]["linkman_edit"].ToString(); string strlinktel_edit = dt_List.Rows[0]["linkman_edit"].ToString(); string strcomment_edit = dt_List.Rows[0]["linkman_edit"].ToString(); dt_List.Columns.Remove("linkman_edit"); dt_List.Columns.Remove("linktel_edit"); dt_List.Columns.Remove("comment_edit"); dt_List.Columns.Add("linkman"); dt_List.Rows[0]["linkman"] = strlinkman_edit; dt_List.Columns.Add("linktel"); dt_List.Rows[0]["linktel"] = strlinktel_edit; dt_List.Columns.Add("comment"); dt_List.Rows[0]["comment"] = strcomment_edit; conn.BeginTransaction(); string strBillid = dt_List.Rows[0]["billid"].ToString(); dt_List.Columns.Remove("billid"); bResult = conn.Update("[mat].[mat_bill]", dt_List, "billid=" + strBillid); conn.CommitTransaction(); if (bResult) { dt_EditResult.Rows.Add(new object[] { true, "修改成功" }); } else { dt_EditResult.Rows.Add(new object[] { false, "修改失败" }); } ds_Return.Tables.Add(dt_EditResult); } #endregion #region RETURE_UPDATE else if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "RETURE_UPDATE") { DataTable dt_List = ds.Tables["List"]; bool bResult = false; string strorgid_reture = dt_List.Rows[0]["orgid_reture"].ToString(); string strisquality_reture = dt_List.Rows[0]["isquality_reture"].ToString(); string strfb_content_reture = dt_List.Rows[0]["fb_content_reture"].ToString(); string strfb_comment_reture = dt_List.Rows[0]["fb_comment_reture"].ToString(); dt_List.Columns.Remove("orgid_reture"); dt_List.Columns.Remove("isquality_reture"); dt_List.Columns.Remove("fb_content_reture"); dt_List.Columns.Remove("fb_comment_reture"); dt_List.Columns.Add("orgid"); dt_List.Rows[0]["orgid"] = strorgid_reture; dt_List.Columns.Add("isquality"); dt_List.Rows[0]["isquality"] = strisquality_reture; dt_List.Columns.Add("fb_content"); dt_List.Rows[0]["fb_content"] = strfb_content_reture; dt_List.Columns.Add("fb_comment"); dt_List.Rows[0]["fb_comment"] = strfb_comment_reture; conn.BeginTransaction(); string strBillid = dt_List.Rows[0]["billid"].ToString(); dt_List.Columns.Remove("billid"); bResult = conn.Update("[mat].[mat_bill]", dt_List, "billid=" + strBillid); conn.CommitTransaction(); if (bResult) { dt_EditResult.Rows.Add(new object[] { true, "修改成功" }); } else { dt_EditResult.Rows.Add(new object[] { false, "修改失败" }); } ds_Return.Tables.Add(dt_EditResult); } #endregion #region RETURE_DETAIL else if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "RETURE_DETAIL") { string strSQL = @" SELECT [billSubid] ,[billid] ,[changecause] ,CONVERT(VARCHAR(100), schemaenddate, 23) AS schemaenddate ,[createtime] ,[updatetime] FROM [mat].[mat_billsub] WHERE 1=1 " + m_hzyPageInfo.Where; ds_Return = conn.GetDataSetForPageList(strSQL, new string[0], m_hzyPageInfo); } #endregion } return ds_Return; } catch { conn.RollbackTransaction(); throw; } }
public DataSet GetData() { try { DataSet ds = new DataSet(); DataSet ds_Return = new DataSet(); //根据用户登录信息取到权限信息 MDM_System_User_Authorization_GetListBySession_ID cls = new MDM_System_User_Authorization_GetListBySession_ID(); cls.hzyMessage = this.hzyMessage; DataSet dsAUZ = cls.GetData(); string strComList = ""; if (dsAUZ.Tables[0].Rows.Count > 0) { DataRow[] listAuzCom = dsAUZ.Tables[0].Select("Authorization_Name" + " like'%COM%'"); for (int k = 0; k < listAuzCom.Length; k++) { if (k > 0) { strComList += ","; } string strCom = listAuzCom[k]["Authorization_Name"].ToString(); strComList += strCom.Substring(strCom.Length - 1); } } string strXML = ""; strXML = m_request.Tables[0].Rows[0]["XML"].ToString(); ds = Common.Common.GetDSByExcelXML(strXML); DataTable dt_OPTYPE = ds.Tables["OPTYPE"]; for (int i = 0; i < ds.Tables["OPTYPE"].Rows.Count; i++) { if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "GETFORCOMBOBOX") { string strSQL = @" SELECT a.depotid, a.d_name, a.areaid, b.shutname, CASE a. m_type WHEN 11 THEN '自营' WHEN 92 THEN '加盟' WHEN 0 THEN '总仓' END AS m_type FROM [F22GP].[dbo].[j_depot] a, [F22GP].dbo.j_area b WHERE a.areaid = b.areaid" + m_hzyPageInfo.Where; string strWhereTmp = " AND (depotid LIKE '" + strComList.Replace(",", "%' OR depotid LIKE '") + "%')"; ds_Return = conn.GetDataSetForPageList(strSQL + strWhereTmp, new string[0],m_hzyPageInfo); } //取GP店铺所有信息 else if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "GETDEPOTINFO") { string strSQL = @" SELECT CASE WHEN LEFT(DEPOTID, 1) = 'N' THEN '纳卉' WHEN LEFT(DEPOTID, 1) = 'A' THEN '嘉雅' WHEN LEFT(DEPOTID, 1) = 'Z' THEN '郑州' WHEN LEFT(DEPOTID, 1) = 'K' THEN '诺珂' WHEN LEFT(DEPOTID, 1) = 'D' THEN '贝爱' WHEN LEFT(DEPOTID, 1) = 'E' THEN '雅莹' WHEN LEFT(DEPOTID, 1) = 'X' THEN '新疆' WHEN LEFT(DEPOTID, 1) = 'W' THEN '维姬' WHEN LEFT(DEPOTID, 1) = 'J' THEN '卓莱雅' WHEN LEFT(DEPOTID, 1) = 'M' THEN '嘉澜' WHEN LEFT(DEPOTID, 1) = 'L' THEN '雅妍集' WHEN LEFT(DEPOTID, 1) = 'H' THEN '泓雅' WHEN LEFT(DEPOTID, 1) = 'H' THEN '慧斓' WHEN LEFT(DEPOTID, 1) = 'F' THEN '嘉莹' WHEN LEFT(DEPOTID, 1) = 'Y' THEN '恩派雅' WHEN LEFT(DEPOTID, 1) = 'P' THEN '澳门' WHEN LEFT(DEPOTID, 1) = 'S' THEN '斓居' WHEN LEFT(DEPOTID, 1) = 'R' THEN '斓绣' WHEN LEFT(DEPOTID, 1) = 'G' THEN '雅斓' WHEN LEFT(DEPOTID, 1) = 'O' THEN '雅斓电商' WHEN LEFT(DEPOTID, 1) = 'Q' THEN '拉萨格桑拉' END AS 公司 , depotid , areaid , d_name , r_name , Tel , Fax , email , CASE m_type WHEN 11 THEN '自营' WHEN 92 THEN '加盟' WHEN 0 THEN '总仓' END AS m_type , Comment , CrDate , updatetime , scale , acreage , begdate , enddate , fristdate , avgsum , leven , pact , present , cwkh , cwbm , jsrq , sums , rate , bail , s_sums , bank , account , backrate , backadd , backspare , backdate , bachcredit , havepact , fh_sums , unitid , address , hjsl , yyrs , bdje , fhjs , dpthelp , power_rate , power_jpric , power_xpric , power_nums , power_sprice , power_2rate , freeze , power_45 , zbset , pos_haveprn , pos_prncnt , pos_prn , pos_scr , pos_vipzsz , pos_vipshow , pos_round , clothing_len , pos_ifday , pos_day , pos_fh , parentid , qryday , mobtel , floor , floortype , floorname , floorloop , stylenums , depotpower , brandpower , del , xpname , qxadd , setkc0 , zbget , zmrqdate , onlybdkc , nozckc , autosetdepotid , fh_sure , parentget , maxkcsums , maxkcnums , pos_off , FRoundDec , pos_AppPro , pos_ProInc , pos_SelCx , pos_novip , pos_ifkc , pic1 , pic2 , pceid , d_dq , d_dd , d_hp , d_lx , b28web , brandid , begdepotdate , enddepotdate , old_depotid , depot_no , pos_cxsy , pos_jprice , pos_cxvip , pos_nozs , pos_spxp , vip_chkup , vip_lookunit , webprint , costcenter , indentplanid , dhh_sums , fm_type , f_enddepotdate , Dh_depot , m_skfs , m_swzt , Modify_Date , oldvnewv , ChangeReason , thck_sure , d_qd , Pos_VipBdz , ESB_ID , ESB_src_App , Retail_ThDay , YearGoal , AreaManager , BrandNumber FROM dbo.j_depot WHERE 1 = 1 " + m_hzyPageInfo.Where; ds_Return = conn.GetDataSetForPageList(strSQL, new string[0], m_hzyPageInfo); } //取GP店铺所有营业员信息 else if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "GETEMPLOYEEINFO") { string strSQL = @" SELECT employeeid , names , sex , depotid , groupid , userrate , birthday , IDCard , workdate , telephone , address , usedstatus , valid , updatetime , Cr_name , Comment , cr_date , CrName , level , Modify_Date , ESB_ID , ESB_src_App , YearGoal , Position , job_number FROM dbo.j_employee WHERE 1=1 and valid = 0" + m_hzyPageInfo.Where; ds_Return = conn.GetDataSetForPageList(strSQL, new string[0], m_hzyPageInfo); } //取GP店铺前缀 else if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "GETSTORE_PREFIX") { string strSQL = @" sELECT 店铺前缀 FROM ( SELECT DISTINCT SUBSTRING(depotid, 1, 1) 店铺前缀 FROM [dbo].[j_depot] WHERE 1 = 1 AND depotid NOT LIKE 's%' ) t " + m_hzyPageInfo.Where; ds_Return = conn.GetDataSet(strSQL); } //通过店铺编号前缀获取店铺大区 else if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "GETDQ") { string strSQL = @" SELECT DISTINCT d_dq , d_dq FROM dbo.j_depot WHERE d_name NOT LIKE '停%' AND d_name NOT LIKE 'P停%' AND m_type NOT IN ( '0', '90', '80' ) AND d_dq IS NOT NULL " + m_hzyPageInfo.Where; ds_Return = conn.GetDataSet(strSQL); } //通过店铺登入ID判断是店铺还是总部人员 else if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "VERIFICATION") { string strSQL = @" SELECT TOP 2 depotid FROM dbo.j_user WITH ( NOLOCK ) WHERE 1=1 " + m_hzyPageInfo.Where; ds_Return = conn.GetDataSetForPageList(strSQL, new string[0], m_hzyPageInfo); } } return ds_Return; } catch { conn.RollbackTransaction(); throw; } }
public DataSet GetData() { try { DataSet ds = new DataSet(); DataSet ds_Return = new DataSet(); DataSet ds_ReturnGP = new DataSet(); DataSet ds_ReturnInventory = new DataSet(); string StrUser_Name = hzyMessage.User_Name; //根据用户登录信息取到权限信息 MDM_System_User_Authorization_GetListBySession_ID cls = new MDM_System_User_Authorization_GetListBySession_ID(); cls.hzyMessage = this.hzyMessage; DataSet dsAUZ = cls.GetData(); string strComList = ""; if (dsAUZ.Tables[0].Rows.Count < 1) { strComList = hzyMessage.User_Name_CN.Substring(0, 1); if (Convert.ToChar(strComList.ToUpper()) > 'Z' || hzyMessage.User_Name_CN.Length < 8) { strComList = ""; } else { strComList = hzyMessage.User_Name_CN.Substring(0, 8); } if (strComList == "") { throw new Exception("你没有权限,请联系管理员!"); } } string strXML = ""; strXML = m_request.Tables[0].Rows[0]["XML"].ToString(); ds = Common.Common.GetDSByExcelXML(strXML); DataTable dt_OPTYPE = ds.Tables["OPTYPE"]; for (int i = 0; i < ds.Tables["OPTYPE"].Rows.Count; i++) { if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "GETINVENTORY") { string strSQL_GP = @" SELECT '' Inventory_Diff_ID, a.inventoryid , a.depotid , b.d_dq , b.d_name , a.zcnums, a.zcsums, CONVERT(VARCHAR(100), a.setdate, 21) AS setdate, CONVERT(VARCHAR(100), a.sure_date, 21) AS sure_date, '未申请' as state, ''as Return_Sums, '' as updatetime FROM d_Inventory a INNER JOIN j_depot b ON a.depotid = b.depotid " + m_hzyPageInfo.Where; string strWhereTmp = "AND (a.depotid LIKE '" + strComList.Replace(",", "%' OR a.depotid LIKE '") + "%')"; ds_ReturnGP = conn_GP.GetDataSet(strSQL_GP + strWhereTmp); string strSQL_Inventory = @" SELECT [Inventory_Diff_ID] ,[Inventoryid] ,[Depotid] ,[Depotname] ,[AreaName] ,[Nums] ,[Sums] ,[Return_Sums], CONVERT(VARCHAR(100), Setdate, 21) AS Setdate, CONVERT(VARCHAR(100), sure_date, 21) AS sure_date ,[sure_date] ,[State] ,[CreateTime] ,[UpdateTime] ,[Timestamps] FROM [inventory].[Inventory_Diff] WHERE Inventory_Diff_ID IN ( SELECT Inventory_Diff_ID FROM ( SELECT ROW_NUMBER() OVER ( PARTITION BY Inventoryid ORDER BY CreateTime DESC ) AS a , Inventory_Diff_ID FROM [inventory].[Inventory_Diff] ) f WHERE a = 1 ) " + m_hzyPageInfo.Where; ds_ReturnInventory = conn_Inventory.GetDataSet(strSQL_Inventory); for (int g = 0; g < ds_ReturnInventory.Tables[0].Rows.Count; g++) { string strState = ds_ReturnInventory.Tables[0].Rows[g]["State"].ToString(); string strInventoryid = ds_ReturnInventory.Tables[0].Rows[g]["Inventoryid"].ToString(); string strReturn_sums = ds_ReturnInventory.Tables[0].Rows[g]["Return_Sums"].ToString(); string strInventory_Diff_ID = ds_ReturnInventory.Tables[0].Rows[g]["Inventory_Diff_ID"].ToString(); string strNums = ds_ReturnInventory.Tables[0].Rows[g]["Nums"].ToString(); string strSums = ds_ReturnInventory.Tables[0].Rows[g]["Sums"].ToString(); string strUpdatedate = ds_ReturnInventory.Tables[0].Rows[g]["updatetime"].ToString(); DataRow[] drGP = ds_ReturnGP.Tables[0].Select("inventoryid = " + "'" + strInventoryid + "'"); if (drGP.Length == 0) { //throw new Exception("没有查询到该店铺盘亏数据!"); } else { drGP[0]["return_sums"] = strReturn_sums; drGP[0]["state"] = strState; drGP[0]["inventory_diff_id"] = strInventory_Diff_ID; drGP[0]["zcnums"] = strNums; drGP[0]["zcsums"] = strSums; drGP[0]["updatetime"] = strUpdatedate; } } } if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "GETSPINFO") { string strSQL_SP = @" SELECT * FROM ( SELECT ROW_NUMBER() OVER ( PARTITION BY t2.Inventoryid ORDER BY t2.CreateTime DESC ) AS a , t1.Inventory_Diff_ID , t1.Inventoryid , t1.Depotid , t1.Depotname , t1.AreaName , t1.Nums , t1.Sums , t1.Return_Sums , CONVERT(VARCHAR(100), t1.Setdate, 23) AS Setdate, CONVERT(VARCHAR(100), t1.sure_date, 23) AS sure_date, t1.State , t1.Content , CONVERT(VARCHAR(100), t2.Amountreceived, 23) AS Amountreceived, CONVERT(VARCHAR(100), t2.Amount_Of_Remittance, 23) AS Amount_Of_Remittance, t2.Serialnumber, t2.Method, CONVERT(VARCHAR(100), t2.Received_Time, 23) AS Received_Time, t2.Img, t2.Filepath, t2.Bankoutlets, CONVERT(VARCHAR(100), t2.Remittance_Time, 23) AS Remittance_Time, t2.CreateTime , t2.CreateUser , t2.UpdateTime , t2.UpdateUser , t2.timestamps FROM [inventory].[Inventory_Diff] t1 LEFT JOIN [inventory].[Inventory_Diff_State] t2 ON t2.Inventory_Diff_ID = t1.Inventory_Diff_ID WHERE EXISTS ( SELECT 1 FROM [inventory].[Inventort_Authority] t3 WHERE ( Branch_POS = @param0 OR Financial_POS = @param0 OR General_POS = @param0 ) AND t1.AreaName = t3.AreaName AND t3.Store_prefix = LEFT([Depotid], 1) ) ) t4 WHERE a = 1 " + m_hzyPageInfo.Where; ds_ReturnGP = conn_Inventory.GetDataSet(strSQL_SP, new string[1] { StrUser_Name }); } if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "INSERT") { bool bResult = false; conn_Inventory.LockTableList.Add("[inventory].[Inventory_Diff]"); conn_Inventory.LockTableList.Add("[inventory].[Inventory_Diff_State]"); string strGuid = System.Guid.NewGuid().ToString(); string strInventory_Diff_ID = ds.Tables["list"].Rows[0]["inventory_diff_id"].ToString(); string strstate = ds.Tables["list"].Rows[0]["state"].ToString(); //小翅膀提醒参数抽取 string pstrDepotname = ds.Tables["list"].Rows[0]["depotname"].ToString(); string pstrUser_Name = GetUserName(hzyMessage.User_Name_CN); string pstrDepotid = ds.Tables["list"].Rows[0]["depotid"].ToString(); string pstrAreaName = ds.Tables["list"].Rows[0]["areaname"].ToString(); string pstrInventoryID = ds.Tables["list"].Rows[0]["inventoryid"].ToString(); string pstrState = ds.Tables["list"].Rows[0]["state"].ToString(); DataTable dt_Inventory_Diff = ds.Tables["list"].Copy(); DataTable dt_Inventory_Diff_State = ds.Tables["list"].Copy(); DateTime timeNow = DateTime.Now; dt_Inventory_Diff.Columns.Add("CreateTime"); dt_Inventory_Diff.Rows[0]["CreateTime"] = timeNow; dt_Inventory_Diff.Columns.Add("UpdateTime"); dt_Inventory_Diff.Rows[0]["UpdateTime"] = timeNow; dt_Inventory_Diff.Columns.Remove("filepath"); dt_Inventory_Diff.Rows[0]["Inventory_Diff_ID"] = strGuid; dt_Inventory_Diff_State.Columns.Add("CreateTime"); dt_Inventory_Diff_State.Rows[0]["CreateTime"] = timeNow; dt_Inventory_Diff_State.Columns.Add("UpdateTime"); dt_Inventory_Diff_State.Rows[0]["UpdateTime"] = timeNow; dt_Inventory_Diff_State.Rows[0]["Inventory_Diff_ID"] = strGuid; dt_Inventory_Diff_State.Columns.Add("CreateUser"); dt_Inventory_Diff_State.Columns.Add("UpdateUser"); dt_Inventory_Diff_State.Rows[0]["UpdateUser"] = GetUserName(hzyMessage.User_Name_CN); dt_Inventory_Diff_State.Rows[0]["CreateUser"] = GetUserName(hzyMessage.User_Name_CN); dt_Inventory_Diff_State.Columns.Remove("setdate"); dt_Inventory_Diff_State.Columns.Remove("depotid"); dt_Inventory_Diff_State.Columns.Remove("return_sums"); dt_Inventory_Diff_State.Columns.Remove("areaname"); dt_Inventory_Diff_State.Columns.Remove("depotname"); dt_Inventory_Diff_State.Columns.Remove("nums"); dt_Inventory_Diff_State.Columns.Remove("sure_date"); dt_Inventory_Diff_State.Columns.Remove("sums"); conn_Inventory.BeginTransaction(); if (strstate == "待财务汇款确认") { DateTime timeNow1 = DateTime.Now; dt_Inventory_Diff.Columns.Add("UpdateTime"); dt_Inventory_Diff.Rows[0]["UpdateTime"] = timeNow1; dt_Inventory_Diff.Columns.Remove("setdate"); dt_Inventory_Diff.Columns.Remove("createtime"); dt_Inventory_Diff.Columns.Remove("depotid"); dt_Inventory_Diff.Columns.Remove("return_sums"); dt_Inventory_Diff.Columns.Remove("areaname"); dt_Inventory_Diff.Columns.Remove("depotname"); dt_Inventory_Diff.Columns.Remove("nums"); dt_Inventory_Diff.Columns.Remove("sure_date"); dt_Inventory_Diff.Columns.Remove("sums"); dt_Inventory_Diff.Columns.Remove("content"); dt_Inventory_Diff.Columns.Remove("inventory_diff_id"); dt_Inventory_Diff.Columns.Remove("Inventoryid"); bResult = conn_Inventory.Update("[inventory].[Inventory_Diff]", dt_Inventory_Diff, "inventory_diff_id=" + "'" + strInventory_Diff_ID + "'"); } else { bResult = conn_Inventory.Insert("[inventory].[Inventory_Diff]", dt_Inventory_Diff); bResult = conn_Inventory.Insert("[inventory].[Inventory_Diff_State]", dt_Inventory_Diff_State); } AM_Message_Send.SendMessage_Submit_inventory(pstrDepotname, pstrUser_Name, pstrDepotid, pstrAreaName, pstrInventoryID, pstrState); conn_Inventory.CommitTransaction(); if (bResult) { dt_EditResult.Rows.Add(new object[] { true, "提交成功" }); } else { dt_EditResult.Rows.Add(new object[] { false, "提交失败" }); } ds_ReturnGP.Tables.Add(dt_EditResult); } //分公司,销售总部提交 if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "INSERTBRANCH") { bool bResult = false; conn_Inventory.LockTableList.Add("[inventory].[Inventory_Diff]"); conn_Inventory.LockTableList.Add("[inventory].[Inventory_Diff_State]"); //string strGuid = System.Guid.NewGuid().ToString(); //更新主表(通过GUID,修改状态和修改时间) DataTable dt_Inventory_Diff = ds.Tables["list"].Copy(); string strInventory_Diff_ID = ds.Tables["list"].Rows[0]["inventory_diff_id"].ToString(); //小翅膀提醒参数抽取 string pstrDepotname = ds.Tables["list"].Rows[0]["depotname"].ToString(); string pstrUser_Name = GetUserName(hzyMessage.User_Name_CN); string pstrDepotid = ds.Tables["list"].Rows[0]["depotid"].ToString(); string pstrAreaName = ds.Tables["list"].Rows[0]["areaname"].ToString(); string pstrInventoryID = ds.Tables["list"].Rows[0]["inventoryid"].ToString(); string pstrState = ds.Tables["list"].Rows[0]["state"].ToString(); DateTime timeNow = DateTime.Now; dt_Inventory_Diff.Columns.Add("UpdateTime"); dt_Inventory_Diff.Rows[0]["UpdateTime"] = timeNow; dt_Inventory_Diff.Columns.Remove("setdate"); dt_Inventory_Diff.Columns.Remove("depotid"); dt_Inventory_Diff.Columns.Remove("return_sums"); dt_Inventory_Diff.Columns.Remove("areaname"); dt_Inventory_Diff.Columns.Remove("depotname"); dt_Inventory_Diff.Columns.Remove("nums"); dt_Inventory_Diff.Columns.Remove("sure_date"); dt_Inventory_Diff.Columns.Remove("sums"); dt_Inventory_Diff.Columns.Remove("content"); dt_Inventory_Diff.Columns.Remove("inventory_diff_id"); //dt_Inventory_Diff.Columns.Remove("Inventoryid"); dt_Inventory_Diff.Columns.Remove("filepath"); DataTable dt_Inventory_Diff_State = ds.Tables["list"].Copy(); dt_Inventory_Diff_State.Columns.Add("CreateTime"); dt_Inventory_Diff_State.Rows[0]["CreateTime"] = timeNow; dt_Inventory_Diff_State.Columns.Add("UpdateTime"); dt_Inventory_Diff_State.Rows[0]["UpdateTime"] = timeNow; dt_Inventory_Diff_State.Columns.Add("CreateUser"); dt_Inventory_Diff_State.Columns.Add("UpdateUser"); dt_Inventory_Diff_State.Rows[0]["UpdateUser"] = GetUserName(hzyMessage.User_Name_CN); dt_Inventory_Diff_State.Rows[0]["CreateUser"] = GetUserName(hzyMessage.User_Name_CN); dt_Inventory_Diff_State.Columns.Remove("setdate"); dt_Inventory_Diff_State.Columns.Remove("depotid"); dt_Inventory_Diff_State.Columns.Remove("return_sums"); dt_Inventory_Diff_State.Columns.Remove("areaname"); dt_Inventory_Diff_State.Columns.Remove("depotname"); dt_Inventory_Diff_State.Columns.Remove("nums"); dt_Inventory_Diff_State.Columns.Remove("sure_date"); dt_Inventory_Diff_State.Columns.Remove("sums"); conn_Inventory.BeginTransaction(); conn_Inventory.TableKeyList.Clear(); conn_Inventory.TableKeyList.Add("inventoryid"); bResult = conn_Inventory.Update("[inventory].[Inventory_Diff]", dt_Inventory_Diff, conn_Inventory.TableKeyList); bResult = conn_Inventory.Insert("[inventory].[Inventory_Diff_State]", dt_Inventory_Diff_State); AM_Message_Send.SendMessage_Submit_inventory(pstrDepotname, pstrUser_Name, pstrDepotid, pstrAreaName, pstrInventoryID, pstrState); conn_Inventory.CommitTransaction(); if (bResult) { dt_EditResult.Rows.Add(new object[] { true, "提交成功" }); } else { dt_EditResult.Rows.Add(new object[] { false, "提交失败" }); } ds_ReturnGP.Tables.Add(dt_EditResult); } //店铺汇款 if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "INSERTREMITTANCE") { bool bResult = false; conn_Inventory.LockTableList.Add("[inventory].[Inventory_Diff]"); conn_Inventory.LockTableList.Add("[inventory].[Inventory_Diff_State]"); //更新主表(通过GUID,修改状态和修改时间) DataTable dt_Inventory_Diff = ds.Tables["list"].Copy(); DateTime timeNow = DateTime.Now; //小翅膀提醒参数抽取 string pstrDepotname = ds.Tables["list"].Rows[0]["depotname"].ToString(); string pstrUser_Name = GetUserName(hzyMessage.User_Name_CN); string pstrDepotid = ds.Tables["list"].Rows[0]["depotid"].ToString(); string pstrAreaName = ds.Tables["list"].Rows[0]["areaname"].ToString(); string pstrInventoryID = ds.Tables["list"].Rows[0]["inventoryid"].ToString(); string pstrState = ds.Tables["list"].Rows[0]["state"].ToString(); string strInventory_Diff_ID = ds.Tables["list"].Rows[0]["inventory_diff_id"].ToString(); dt_Inventory_Diff.Columns.Add("UpdateTime"); dt_Inventory_Diff.Rows[0]["UpdateTime"] = timeNow; dt_Inventory_Diff.Columns.Remove("setdate"); dt_Inventory_Diff.Columns.Remove("depotid"); dt_Inventory_Diff.Columns.Remove("return_sums"); dt_Inventory_Diff.Columns.Remove("areaname"); dt_Inventory_Diff.Columns.Remove("depotname"); dt_Inventory_Diff.Columns.Remove("nums"); dt_Inventory_Diff.Columns.Remove("sure_date"); dt_Inventory_Diff.Columns.Remove("sums"); dt_Inventory_Diff.Columns.Remove("inventory_diff_id"); dt_Inventory_Diff.Columns.Remove("method"); //dt_Inventory_Diff.Columns.Remove("inventoryid"); dt_Inventory_Diff.Columns.Remove("serialnumber"); dt_Inventory_Diff.Columns.Remove("amount_of_remittance"); dt_Inventory_Diff.Columns.Remove("img"); dt_Inventory_Diff.Columns.Remove("bankoutlets"); dt_Inventory_Diff.Columns.Remove("remittance_time"); DataTable dt_Inventory_Diff_State = ds.Tables["list"].Copy(); dt_Inventory_Diff_State.Columns.Add("CreateTime"); dt_Inventory_Diff_State.Rows[0]["CreateTime"] = timeNow; dt_Inventory_Diff_State.Columns.Add("UpdateTime"); dt_Inventory_Diff_State.Rows[0]["UpdateTime"] = timeNow; dt_Inventory_Diff_State.Columns.Add("CreateUser"); dt_Inventory_Diff_State.Columns.Add("UpdateUser"); dt_Inventory_Diff_State.Rows[0]["UpdateUser"] = GetUserName(hzyMessage.User_Name_CN); dt_Inventory_Diff_State.Rows[0]["CreateUser"] = GetUserName(hzyMessage.User_Name_CN); dt_Inventory_Diff_State.Columns.Remove("setdate"); dt_Inventory_Diff_State.Columns.Remove("depotid"); dt_Inventory_Diff_State.Columns.Remove("return_sums"); dt_Inventory_Diff_State.Columns.Remove("areaname"); dt_Inventory_Diff_State.Columns.Remove("depotname"); dt_Inventory_Diff_State.Columns.Remove("nums"); dt_Inventory_Diff_State.Columns.Remove("sure_date"); dt_Inventory_Diff_State.Columns.Remove("sums"); //return new DataSet(); conn_Inventory.BeginTransaction(); conn_Inventory.TableKeyList.Clear(); conn_Inventory.TableKeyList.Add("inventoryid"); bResult = conn_Inventory.Update("[inventory].[Inventory_Diff]", dt_Inventory_Diff, conn_Inventory.TableKeyList); //bResult = conn_Inventory.Update("[inventory].[Inventory_Diff]", dt_Inventory_Diff, "inventory_diff_id=" + "'" + strInventory_Diff_ID + "'"); bResult = conn_Inventory.Insert("[inventory].[Inventory_Diff_State]", dt_Inventory_Diff_State); AM_Message_Send.SendMessage_Submit_inventory(pstrDepotname, pstrUser_Name, pstrDepotid, pstrAreaName, pstrInventoryID, pstrState); conn_Inventory.CommitTransaction(); if (bResult) { dt_EditResult.Rows.Add(new object[] { true, "提交成功" }); } else { dt_EditResult.Rows.Add(new object[] { false, "提交失败" }); } ds_ReturnGP.Tables.Add(dt_EditResult); } //财务审批确认 if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "INSERTFINANCIAL") { bool bResult = false; conn_Inventory.LockTableList.Add("[inventory].[Inventory_Diff]"); conn_Inventory.LockTableList.Add("[inventory].[Inventory_Diff_State]"); //更新主表(通过GUID,修改状态和修改时间) DataTable dt_Inventory_Diff = ds.Tables["list"].Copy(); string strInventory_Diff_ID = ds.Tables["list"].Rows[0]["inventory_diff_id"].ToString(); //小翅膀提醒参数抽取 string pstrDepotname = ds.Tables["list"].Rows[0]["depotname"].ToString(); string pstrUser_Name = hzyMessage.User_Name_CN; string pstrDepotid = ds.Tables["list"].Rows[0]["depotid"].ToString(); string pstrAreaName = ds.Tables["list"].Rows[0]["areaname"].ToString(); string pstrInventoryID = ds.Tables["list"].Rows[0]["inventoryid"].ToString(); string pstrState = ds.Tables["list"].Rows[0]["state"].ToString(); DateTime timeNow = DateTime.Now; dt_Inventory_Diff.Columns.Add("UpdateTime"); dt_Inventory_Diff.Rows[0]["UpdateTime"] = timeNow; dt_Inventory_Diff.Columns.Remove("setdate"); dt_Inventory_Diff.Columns.Remove("depotid"); dt_Inventory_Diff.Columns.Remove("return_sums"); dt_Inventory_Diff.Columns.Remove("areaname"); dt_Inventory_Diff.Columns.Remove("depotname"); dt_Inventory_Diff.Columns.Remove("nums"); dt_Inventory_Diff.Columns.Remove("sure_date"); dt_Inventory_Diff.Columns.Remove("sums"); dt_Inventory_Diff.Columns.Remove("inventory_diff_id"); dt_Inventory_Diff.Columns.Remove("method"); //dt_Inventory_Diff.Columns.Remove("inventoryid"); dt_Inventory_Diff.Columns.Remove("serialnumber"); dt_Inventory_Diff.Columns.Remove("amountreceived"); dt_Inventory_Diff.Columns.Remove("content"); dt_Inventory_Diff.Columns.Remove("amount_of_remittance"); dt_Inventory_Diff.Columns.Remove("filepath"); dt_Inventory_Diff.Columns.Remove("received_time"); dt_Inventory_Diff.Columns.Remove("img"); dt_Inventory_Diff.Columns.Remove("bankoutlets"); dt_Inventory_Diff.Columns.Remove("remittance_time"); DataTable dt_Inventory_Diff_State = ds.Tables["list"].Copy(); dt_Inventory_Diff_State.Columns.Add("CreateTime"); dt_Inventory_Diff_State.Rows[0]["CreateTime"] = timeNow; dt_Inventory_Diff_State.Columns.Add("UpdateTime"); dt_Inventory_Diff_State.Rows[0]["UpdateTime"] = timeNow; dt_Inventory_Diff_State.Columns.Add("CreateUser"); dt_Inventory_Diff_State.Columns.Add("UpdateUser"); dt_Inventory_Diff_State.Rows[0]["UpdateUser"] = GetUserName(hzyMessage.User_Name_CN); dt_Inventory_Diff_State.Rows[0]["CreateUser"] = GetUserName(hzyMessage.User_Name_CN); dt_Inventory_Diff_State.Columns.Remove("setdate"); dt_Inventory_Diff_State.Columns.Remove("depotid"); dt_Inventory_Diff_State.Columns.Remove("return_sums"); dt_Inventory_Diff_State.Columns.Remove("areaname"); dt_Inventory_Diff_State.Columns.Remove("depotname"); dt_Inventory_Diff_State.Columns.Remove("nums"); dt_Inventory_Diff_State.Columns.Remove("sure_date"); dt_Inventory_Diff_State.Columns.Remove("sums"); //return new DataSet(); conn_Inventory.BeginTransaction(); conn_Inventory.TableKeyList.Clear(); conn_Inventory.TableKeyList.Add("inventoryid"); bResult = conn_Inventory.Update("[inventory].[Inventory_Diff]", dt_Inventory_Diff, conn_Inventory.TableKeyList); //bResult = conn_Inventory.Update("[inventory].[Inventory_Diff]", dt_Inventory_Diff, "inventory_diff_id=" + "'" + strInventory_Diff_ID + "'"); bResult = conn_Inventory.Insert("[inventory].[Inventory_Diff_State]", dt_Inventory_Diff_State); AM_Message_Send.SendMessage_Submit_inventory(pstrDepotname, pstrUser_Name, pstrDepotid, pstrAreaName, pstrInventoryID, pstrState); conn_Inventory.CommitTransaction(); if (bResult) { dt_EditResult.Rows.Add(new object[] { true, "提交成功" }); } else { dt_EditResult.Rows.Add(new object[] { false, "提交失败" }); } ds_ReturnGP.Tables.Add(dt_EditResult); } if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "GETINFODETAIL") { string strSQL_SP = @" SELECT * FROM ( SELECT t1.Inventoryid , t1.Amount_Of_Remittance , t1.amountreceived , t1.Serialnumber , t1.Method , t1.State , CONVERT(VARCHAR(100), t1.remittance_time, 23) AS remittance_time, CONVERT(VARCHAR(100), t1.received_time, 23) AS received_time, t1.CreateTime , t1.CreateUser , t1.UpdateTime , t1.UpdateUser , t1.timestamps , t2.Depotid , t2.Depotname , t2.AreaName , t2.Nums , t2.Sums , t2.Return_Sums , t1.Content , t2.Setdate , t2.sure_date FROM [inventory].[Inventory_Diff_State] t1 LEFT JOIN [inventory].[Inventory_Diff] t2 ON t1.Inventory_Diff_ID = t2.Inventory_Diff_ID ) t3 WHERE 1 = 1 " + m_hzyPageInfo.Where; ds_ReturnGP = conn_Inventory.GetDataSetForPageList(strSQL_SP, new string[0], m_hzyPageInfo); } if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "GETLOSSTABLE") { string strWhere = ""; string beginDate = ds.Tables["list"].Rows[0]["begindate"].ToString(); string endDate = ds.Tables["list"].Rows[0]["endDate"].ToString(); if (beginDate.Trim() != "") { strWhere = strWhere + " and setDate >='" + beginDate.Trim() + "'"; } if (endDate.Trim() != "") { strWhere = strWhere + " and setDate <='" + endDate.Trim() + "'"; } string strSQL = @" SELECT * FROM ( SELECT t1.Inventoryid,t1.AreaName,t2.d_dd,t1.Depotid,t1.Depotname, t1.Nums,t1.Sums,t1.Return_Sums, convert(varchar(10),t1.Setdate,20) Setdate , convert(varchar(100),sure_date,20) sure_date, t1.State, t1.CreateTime FROM [inventory].[Inventory_Diff] t1 , [GP].[F22GP].[dbo].[j_depot] t2 WHERE t1.Depotid = t2.depotid UNION ALL SELECT a.Inventoryid , b.d_dq AreaName, b.d_dd, a.depotid , b.d_name Depotname, a.zcnums Nums, a.zcsums Sums, null AS Return_Sums , CONVERT(VARCHAR(10), a.setdate, 20) AS setdate , convert(varchar(100),a.sure_date,20) sure_date, '未申请' AS state , null AS CreateTime FROM [GP].[F22GP].dbo.d_Inventory a INNER JOIN [GP].[F22GP].dbo.j_depot b ON a.depotid = b.depotid WHERE not exists ( SELECT Inventoryid FROM [inventory].[Inventory_Diff] c where a.Inventoryid=c.Inventoryid) AND b.m_type = 11 and d_dq not in ('雅斓大区','总部仓') AND SUBSTRING(b.depotid, 1, 1) NOT in ('D','J','Y','N','G') ) TT Where 1=1 AND Nums<>0 " + strWhere + m_hzyPageInfo.Where; ds_ReturnGP = conn_Inventory.GetDataSetForPageList(strSQL, m_hzyPageInfo); } } return ds_ReturnGP; } catch { conn_Inventory.RollbackTransaction(); throw; } }