private void Getdata() { string strConnstr = ConfigurationManager.AppSettings["ConnectionString_POS"]; Dbconn conn = new Dbconn(strConnstr, true); string strPhone = this.txtPhone.Text; if (strPhone.Trim() != "") { string strName = conn.GetDataTableFirstValue(@"SELECT vipname as 姓名, mobtel as 手机号, vipcode as 卡号 FROM vip_user WHERE mobtel=@Param0 OR vipcode=@Param0", new string[] { strPhone }).ToString(); this.lblName.Text = strName; DataSet ds = conn.GetDataSet(@"SELECT b.ticketid AS 券号 , a.sums AS 面值 , CASE ISNULL(b.getsure,0)WHEN 0 THEN '否' ELSE '是' END AS 是否使用 , b.crdate AS 创建日期 , b.verifycode AS 验证码,a.mobtel, a.vipcode, a.vipid FROM vip_user a INNER JOIN vip_ticket b ON a.vipid = b.vipid WHERE b.tictypeid = '31' AND (a.mobtel=@param0 OR a.vipcode=@param0) ORDER BY b.crdate DESC ", new string[] { strPhone }); RpList.DataSource = ds.Tables[0]; RpList.DataBind(); if (ds.Tables[0].Rows.Count == 0) { this.lblMessage.Text = "<font color='red'>没有查询到手机号/卡号为" + strPhone + "的相关信息,请重试</font>"; } else { this.lblMessage.Text = ""; } } else { this.lblMessage.Text = ""; RpList.DataSource = new DataTable(); RpList.DataBind(); } }
public static void UpdateFullText(string pstrWSID, Dbconn pconn) { string strSQL = @" DECLARE @cols NVARCHAR(MAX) SET @cols = '' SELECT @cols = @cols + 'convert(varchar(1000),isnull(' + name + ',''''),21)+'' ''+' FROM syscolumns WHERE ( id = ( SELECT id FROM sysobjects WHERE ( name = 'WS0E_WSExtDoc' ) ) ) SELECT @cols as A "; string strInfoColumns = pconn.GetDataTableFirstValue(strSQL).ToString(); strSQL = strInfoColumns.Substring(0, strInfoColumns.Length - 1); strSQL = "SELECT " + strSQL + " FROM [B01_MDM].[WS0E_WSExtDoc] WHERE WSID=@Param0"; string strValue = pconn.GetDataTableFirstValue(strSQL, new string[] { pstrWSID }).ToString(); strSQL = @" DECLARE @cols NVARCHAR(MAX) SET @cols = '' SELECT @cols = @cols + 'convert(varchar(1000),isnull(' + name + ',''''),21)+'' ''+' FROM syscolumns WHERE ( id = ( SELECT id FROM sysobjects WHERE ( name = 'WS0M_WSMethodDoc' ) ) ) SELECT @cols as A "; string strFunctionColumns = pconn.GetDataTableFirstValue(strSQL).ToString(); strSQL = "SELECT " + strFunctionColumns.Substring(0, strFunctionColumns.Length - 1) + " AS A FROM [B01_MDM].[WS0M_WSMethodDoc] WHERE WSID=@Param0"; DataTable dt = pconn.GetDataTable(strSQL, new string[] { pstrWSID }); for (int i = 0; i < dt.Rows.Count; i++) { strValue += " " + dt.Rows[i]["A"].ToString(); } strSQL = " SELECT 1 AS A FROM [B05_RPT].[WS0V_WSMethodView] WHERE WSID=@Param0"; if (pconn.GetDataTableRowCount(strSQL, new string[] { pstrWSID }) == 1) { pconn.ExcuteQuerryByTran("UPDATE [B05_RPT].[WS0V_WSMethodView] SET fulltext=@Param0 WHERE WSID=@PARAM1", new string[] { strValue, pstrWSID }); } else { pconn.ExcuteQuerryByTran("INSERT INTO [B05_RPT].[WS0V_WSMethodView](fulltext,WSID)Values(@Param0,@PARAM1)", new string[] { strValue, pstrWSID }); } }
/// <summary> /// 对审核步骤进行审核 /// </summary> /// <returns></returns> public DataSet GetData() { try { //DataSet ds = new DataSet(); DataSet ds_Return = new DataSet(); DataSet dsRequest = new DataSet(); ArrayList listAMSendList = new ArrayList(); Dbconn conn = new Dbconn("HZY_ITSM"); string strXML = ""; strXML = m_request.Tables[0].Rows[0]["XML"].ToString(); DataSet ds = Common.Common.GetDSByExcelXML(strXML); string strCheck_ID = ""; string strCheck_User = ""; string strCheck_Result = ""; string strCheck_RemarK = ""; string strKey_Value = ""; string strKeyColumn = ""; string strRequestTableName = ""; strCheck_ID = ds.Tables["LIST"].Rows[0]["ID"].ToString(); if (strCheck_ID == "") { ds_Return = Common.Common.GetRequestDataSet(new string[] { "Result" }, new string[] { "1:您的请求有误!" }); return ds_Return; } DataTable dt = conn.GetDataTable(@" SELECT t1.ID , t1.Key_ID , t1.Seq_Index , t1.Seq_Name , t1.Check_User , t1.Check_Type, t1.Check_Type_Table_Name, t1.Check_Type_Key_Column, t1.Check_Date , t1.Check_Result , t1.Remark , t2.Table_Templete FROM dbo.Check_List t1 LEFT JOIN dbo.check_Table_Type t2 ON t1.Check_Type = t2.Check_Type WHERE t1.id = @Param0 ", new string[1] { strCheck_ID }); if (dt.Rows.Count == 0) { ds_Return = Common.Common.GetRequestDataSet(new string[] { "Result" }, new string[] { "2:您的请求有误!" }); return ds_Return; } strKey_Value = dt.Rows[0]["Key_ID"].ToString(); strKeyColumn = dt.Rows[0]["Check_Type_Key_Column"].ToString(); strRequestTableName = dt.Rows[0]["Check_Type_Table_Name"].ToString(); if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "GET") { ITSM_Server_Request_EditByXML itsm_SR = new ITSM_Server_Request_EditByXML(); strXML = @"<ROOT><OPTYPE>GET</OPTYPE> <LIST> <ROW> <condition>" + strKeyColumn + @"='" + strKey_Value + @"'</condition> </ROW> </LIST> </ROOT>"; DataSet ds_ITSMSR_Request = Common.Common.GetRequestDataSet(new string[1] { "XML" }, new string[1] { strXML}); itsm_SR.Request = ds_ITSMSR_Request; DataSet ds_ITSMSR_Response = itsm_SR.GetData(); if (ds_ITSMSR_Response.Tables.Count == 0 || ds_ITSMSR_Response.Tables[0].Rows.Count == 0) { ds_Return = Common.Common.GetRequestDataSet(new string[] { "Result" }, new string[] { "3:获取原始单据有误!" }); return ds_Return; } string strTableTemplete = dt.Rows[0]["Table_Templete"].ToString(); for (int i = 0; i < ds_ITSMSR_Response.Tables[0].Columns.Count; i++) { string strTemp = ds_ITSMSR_Response.Tables[0].Rows[0][i].ToString().Replace("\n", "<p>"); if(strTemp == "") { strTemp = " "; } strTableTemplete = strTableTemplete.Replace("[@@" + ds_ITSMSR_Response.Tables[0].Columns[i].ColumnName + "]", strTemp); } dt.Rows[0]["Table_Templete"] = Microsoft.JScript.GlobalObject.escape(strTableTemplete); dt.Columns.Add("timestamps"); dt.Rows[0]["timestamps"] = ds_ITSMSR_Response.Tables[0].Rows[0]["timestamps"]; ds_Return.Tables.Add(dt.Copy()); } else if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "EDIT") { strCheck_ID = ds.Tables["LIST"].Rows[0]["ID"].ToString(); strCheck_User = ds.Tables["LIST"].Rows[0]["Check_User"].ToString(); strCheck_Result = ds.Tables["LIST"].Rows[0]["Check_Result"].ToString(); strCheck_RemarK = ds.Tables["LIST"].Rows[0]["Check_Remark"].ToString(); DataTable dtCheckList = conn.GetDataTable("SELECT * FROM Check_List WHERE ID='" + strCheck_ID + "'"); if (dt.Rows.Count == 0) { throw new Exception("该审核单已不存在,请联系管理员"); } int nSeq_ID = Convert.ToInt32(dt.Rows[0]["Seq_Index"]); //判断是否已经进行后续的审批 string strSql = "SELECT '1' as A from Check_List WHERE Check_Type='" + dt.Rows[0]["Check_Type"].ToString() + @"' AND Key_ID='" + dt.Rows[0]["Key_ID"].ToString() + @"' AND Check_Result is not null AND Seq_Index>" + nSeq_ID.ToString() + ""; if (conn.GetDataTableRowCount(strSql) > 0) { throw new Exception("下一道的流程已经审批,本次审批不能再做修改!"); } dtCheckList.Rows[0]["Check_User"] = strCheck_User; dtCheckList.Rows[0]["Check_Date"] = DateTime.Now; dtCheckList.Rows[0]["Check_Result"] = strCheck_Result; dtCheckList.Rows[0]["Remark"] = strCheck_RemarK; dtCheckList.Rows[0]["updateTime"] = DateTime.Now; ArrayList listTable = new ArrayList(); listTable.Add("Check_List"); try { conn.BeginTransaction(); conn.TableLock(listTable); //判断数据是否为最新数据 strSql = "SELECT timestamps+0 as timestamps from " + strRequestTableName + " WHERE Deleted=0 AND " + strKeyColumn + "='" + strKey_Value + "'"; string strTimestamps = conn.GetDataTableFirstValue(strSql).ToString(); if (strTimestamps != ds.Tables["LIST"].Rows[0]["timestamps"].ToString()) { throw new Exception("原申请单数据已被修改,请刷新后重试!"); } //dt.Columns.Remove("timestamps"); conn.Update("Check_List", dtCheckList, "ID='" + strCheck_ID + "'"); string[] strColumn = new string[3]; string[] strValue = new string[3]; strColumn[0] = "Check_Type"; strColumn[1] = "Seq_Name"; strColumn[2] = "Key_ID"; strValue[0] = dt.Rows[0]["Check_Type"].ToString(); strValue[1] = dt.Rows[0]["Seq_Name"].ToString(); strValue[2] = dt.Rows[0]["Key_ID"].ToString(); DataSet requestDs = Common.Common.GetRequestDataSet(strColumn, strValue); ITSM_Check_List_Edit checkListEdit = new ITSM_Check_List_Edit(); checkListEdit.Conn = conn; checkListEdit.ListAMSendList = listAMSendList; checkListEdit.Request = requestDs; checkListEdit.Execute(); if (strCheck_Result != "同意") { //更新原表的状态为已驳回 strSql = "UPDATE " + strRequestTableName + " SET State='已驳回' WHERE Deleted=0 AND " + strKeyColumn + "='" + strKey_Value + "'"; conn.ExcuteQuerryByTran(strSql); } else { //判断是否有已驳回的状态 strSql = "SELECT '1' as A from Check_List WHERE Check_Type='" + dt.Rows[0]["Check_Type"].ToString() + @"' AND Key_ID='" + strKey_Value + @"' AND (Seq_index = " + nSeq_ID.ToString() + @" AND Check_Result='已驳回')"; //没有已驳回的记录 if (conn.GetDataTableRowCount(strSql) == 0) { //判断是否还有后续的流程 strSql = "SELECT '1' as A from Check_List WHERE Check_Type='" + dt.Rows[0]["Check_Type"].ToString() + @"' AND Key_ID='" + strKey_Value + @"' AND (Seq_Index>" + nSeq_ID.ToString() + @" OR (Seq_Index=" + nSeq_ID.ToString() + @" AND Must_Check=1 AND Check_Date IS NULL ))"; if (conn.GetDataTableRowCount(strSql) == 0) { //更新原表的状态为已审核 strSql = "UPDATE " + strRequestTableName + " SET State='已审核' WHERE Deleted=0 AND " + strKeyColumn + "='" + strKey_Value + "'"; conn.ExcuteQuerryByTran(strSql); ITSM_Common.SendAMMessage("", "马卫清", "有服务器申请表需要操作", "您有有服务器申请表需要操作,请进入EXCEL操作"); } else { //更新原表的状态为审核中 strSql = "UPDATE " + strRequestTableName + " SET State='审核中' WHERE Deleted=0 AND " + strKeyColumn + "='" + strKey_Value + "'"; conn.ExcuteQuerryByTran(strSql); } } } conn.CommitTransaction(); for (int i = 0; i < listAMSendList.Count; i++) { string[] strList = (string[])(listAMSendList[i]); ITSM_Common.SendAMMessage(strList[0], strList[1], strList[2]); } } catch (Exception) { conn.RollbackTransaction(); throw; } ds_Return = Common.Common.GetRequestDataSet(new string[] { "Result" }, new string[] { "true" }); } return ds_Return; } catch { throw; } }
/// <summary> /// 根据MDM销售大区来编辑Entity /// </summary> /// <returns></returns> public DataSet GetData() { Dbconn conn = new Dbconn("MDM_Master"); try { string strXML = m_request.Tables[0].Rows[0]["XML"].ToString(); string strEntityCode = "0018"; string strMarketTableName = "SaleArea_For_FM"; DataSet ds = Common.Common.GetDSByExcelXML(strXML); //获取销售大区的所有属性 MDM_Entity_Market_PropertyList clsPropertyList = new MDM_Entity_Market_PropertyList(); clsPropertyList.Request = Common.Common.GetRequestDataSet(new string[2] { "Entity", "MarketTableName" }, new string[2] { strEntityCode, strMarketTableName }); DataTable dt_PropertyList = clsPropertyList.GetData().Tables[0]; //修改销售大区数据 DataTable dtSaleArea = new DataTable(strEntityCode); dtSaleArea.Namespace = "CN"; //定义表达方式 DataTable dtSaleAreaLink = new DataTable("L0014"); dtSaleAreaLink.Namespace = "CN"; //定义表达方式 dtSaleAreaLink.Columns.Add("Body_ID"); dtSaleAreaLink.Columns.Add("Body_ID_0008"); dtSaleAreaLink.Columns.Add("Body_ID_0018"); bool bCreateTable = true; for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++) { DataRow dr = dtSaleArea.NewRow(); DataRow drLink = dtSaleAreaLink.NewRow(); //drLink["body_ID"] = ds.Tables["LIST"].Rows[i]["body_ID"].ToString(); drLink["Body_ID_0008"] = ds.Tables["LIST"].Rows[i]["Body_ID_0008"].ToString(); if (drLink["Body_ID_0008"].ToString() == "") { throw new Exception("店铺品牌的Body_ID不能为空!"); } dtSaleAreaLink.Rows.Add(drLink); if (bCreateTable) { dtSaleArea.Columns.Add("body_ID"); } dr["body_ID"] = ds.Tables["LIST"].Rows[i]["body_ID_0018"].ToString(); for (int j = 0; j < ds.Tables["LIST"].Columns.Count; j++) { string strColumnName = ds.Tables["LIST"].Columns[j].ColumnName; //如果修改的列是属于品牌信息的话,要对品牌的Entity进行修改 DataRow[] drList = dt_PropertyList.Select("Table_Property_Name = '" + strColumnName + "'"); if (drList.Length > 0) { string strPorpertyname = drList[0]["Porperty_Name"].ToString(); if (bCreateTable) { dtSaleArea.Columns.Add(strPorpertyname); } dr[strPorpertyname] = ds.Tables["LIST"].Rows[i][j].ToString(); } } dtSaleArea.Rows.Add(dr); bCreateTable = false; } DataSet dsRequest = new DataSet(); dsRequest.Tables.Add(dtSaleArea); MDM_Entity_Edit cls_Entity_Edit = new MDM_Entity_Edit(); cls_Entity_Edit.Request = dsRequest; cls_Entity_Edit.GetDataByRequestDs = true; cls_Entity_Edit.Execute(); ArrayList listBody_0018 = cls_Entity_Edit.BodyID; //获取品牌的BODYID //修改LINK数据 for (int i = 0; i < listBody_0018.Count; i++) { dtSaleAreaLink.Rows[i]["Body_ID_0018"] = listBody_0018[i].ToString(); //到表里查找Link的BodyID string strSQL = "SELECT Body_ID FROM MDM_L0014_B WHERE Body_ID_0018='" + listBody_0018[i].ToString() + "'"; string strLinkBodyID = conn.GetDataTableFirstValue(strSQL).ToString(); dtSaleAreaLink.Rows[i]["Body_ID"] = strLinkBodyID; } dsRequest.Clear(); dsRequest.Tables.Add(dtSaleAreaLink); cls_Entity_Edit.Request = dsRequest; cls_Entity_Edit.Execute(); ArrayList listBody = cls_Entity_Edit.BodyID; string strMessage = "影响的行数为:" + listBody.Count.ToString() +"行" ; for (int i = 0; i < listBody.Count; i++) { strMessage += ";" + listBody[i].ToString(); } return Common.Common.GetRequestDataSet(new string[2] { "result", "Message" }, new string[2] { "true", strMessage }); } catch { throw; } }
private string GetStoreCode(string str加盟商Body_id,Dbconn conn) { string strReturn = ""; string strMaxCode = ""; if (m_MaxCodeName.Contains(str加盟商Body_id)) { strMaxCode = m_MaxCode[m_MaxCodeName.IndexOf(str加盟商Body_id)].ToString(); } else { strMaxCode = conn.GetDataTableFirstValue(@" SELECT MAX(t2.Value_Text) AS code FROM dbo.MDM_L0013_B t1 LEFT JOIN dbo.MDM_0006_E t2 ON t1.Body_ID_0006 = t2.Body_ID WHERE Body_ID_0010 = '" + str加盟商Body_id + @"' AND Property_ID IN ( SELECT Property_ID FROM dbo.Lib_ExdPropertyList WHERE Class_Name = '0006' AND Property_Name = '店铺ID' ) ").ToString(); if (strMaxCode == "") { //根据加盟商名称获取加盟商编号 string str加盟商编号 = conn.GetDataTableFirstValue(@" SELECT Value_Text FROM dbo.MDM_0010_E WHERE Body_ID='" + str加盟商Body_id + @"' AND Property_ID IN ( SELECT Property_ID FROM dbo.Lib_ExdPropertyList WHERE Class_Name='0010' AND Property_Name='加盟商编码' ) ").ToString(); if (str加盟商编号.Length == 5) { strMaxCode = str加盟商编号 + "000"; } else //二级加盟商 { strMaxCode = str加盟商编号 + "00"; } } } strReturn = strMaxCode.Substring(0, 5) + (Convert.ToInt32(strMaxCode.Substring(5)) + 1).ToString().PadLeft(3, '0'); if (m_MaxCodeName.Contains(str加盟商Body_id)) { m_MaxCode[m_MaxCodeName.IndexOf(str加盟商Body_id)] = strReturn; } else { m_MaxCodeName.Add(str加盟商Body_id); m_MaxCode.Add(strReturn); } return strReturn; }
public bool Execute() { string strEnv_ID = ""; string strEnv_SN = "EP_PLMFZ"; Dbconn conn_PLM_AS = new Dbconn("PLM_AS"); DataSet ds = conn_PLM_AS.GetDataSet( @" SELECT --44 as Env_ID, null as VexSSONewID, username as App_UserName, RIGHT(sys.fn_VarBinToHexStr(hashbytes('MD5', convert(vARCHAR(100),PASSWORD))), 32) as App_Password, PASSWORD as App_PasswordSrc, userNameC as App_UserName_CN, getdate() Chang_Password_Time , 1 Checked , 'system' Check_User , getdate() Check_Time , getdate() Createtime , getdate() Updatetime, 0 as oprationFlag FROM PLMAss.dbo.USERINFO u "); Dbconn connMDM = new Dbconn("VexSSO"); try { ArrayList tableList = new ArrayList(); connMDM.BeginTransaction(); tableList.Add("MDM_System_User"); connMDM.TableLock(tableList); strEnv_ID = connMDM.GetDataTableFirstValue("SELECT [Env_ID] FROM [MDM_System_Env] WHERE Env_SN='" + strEnv_SN + "'").ToString(); DataTable dtMDM = connMDM.GetDataTable(@" SELECT [App_UserID] ,[Env_ID] ,[VexSSONewID] ,[App_UserName] ,[App_Password] ,[App_PasswordSrc] ,[App_UserName_CN] ,[Chang_Password_Time] ,[Checked] ,[Check_User] ,[Check_Time] ,[Createtime] ,[Updatetime] FROM [dbo].[MDM_System_User] WHERE Env_ID='" + strEnv_ID + @"' "); DataTable dtMDMUpdate = dtMDM.Clone(); DataTable dtMDMInsert = dtMDM.Clone(); DataTable dtMDMDelete = dtMDM.Clone(); for (int i = 0; i < dtMDM.Rows.Count; i++) { string strApp_UserName = dtMDM.Rows[i]["App_UserName"].ToString(); DataRow[] dr = ds.Tables[0].Select("App_UserName='******'"); if (dr.Length > 0) { dtMDM.Rows[i]["App_Password"] = dr[0]["App_Password"]; dtMDM.Rows[i]["App_PasswordSrc"] = dr[0]["App_PasswordSrc"]; dtMDM.Rows[i]["App_UserName_CN"] = dr[0]["App_UserName_CN"]; dtMDMUpdate.ImportRow(dtMDM.Rows[i]); dr[0]["oprationFlag"] = "1"; } else { dtMDMDelete.ImportRow(dtMDM.Rows[i]); } } DataRow[] drInsert = ds.Tables[0].Select("oprationFlag=0"); for (int i = 0; i < drInsert.Length; i++) { DataRow dr = dtMDMInsert.NewRow(); dr["Env_ID"] = strEnv_ID; dr["VexSSONewID"] = drInsert[i]["VexSSONewID"]; dr["App_UserName"] = drInsert[i]["App_UserName"]; dr["App_Password"] = drInsert[i]["App_Password"]; dr["App_PasswordSrc"] = drInsert[i]["App_PasswordSrc"]; dr["App_UserName_CN"] = drInsert[i]["App_UserName_CN"]; dr["Chang_Password_Time"] = drInsert[i]["Chang_Password_Time"]; dr["Checked"] = drInsert[i]["Checked"]; dr["Check_User"] = drInsert[i]["Check_User"]; dr["Check_Time"] = drInsert[i]["Check_Time"]; dr["Createtime"] = drInsert[i]["Createtime"]; dr["Updatetime"] = drInsert[i]["Updatetime"]; dtMDMInsert.Rows.Add(dr); } if (dtMDMDelete.Rows.Count > 0) { for (int i = 0; i < dtMDMDelete.Rows.Count; i++) { string strWhere = "App_UserID='" + dtMDMDelete.Rows[i]["App_UserID"] + "'"; connMDM.Delete("MDM_System_User", strWhere); } } if (dtMDMUpdate.Rows.Count > 0) { ArrayList listKey = new ArrayList(); listKey.Add("App_UserID"); connMDM.Update("MDM_System_User", dtMDMUpdate, listKey); } if (dtMDMInsert.Rows.Count > 0) { dtMDMUpdate.Columns.Remove("App_UserID"); connMDM.InsertBYSQLBC("MDM_System_User", dtMDMInsert); } connMDM.CommitTransaction(); return true; } catch { connMDM.RollbackTransaction(); throw; } }
public bool Execute() { string strEnv_ID = ""; string strEnv_SN = "HZY_CMT"; Dbconn conn_HZY_SBS = new Dbconn("F22GP_ForSalesPlan"); DataSet ds = conn_HZY_SBS.GetDataSet( @" SELECT --98 AS Env_ID , NULL AS VexSSONewID , b.userid AS App_UserName , RIGHT(sys.fn_VarBinToHexStr(hashbytes('MD5', CONVERT(VARCHAR(100), b.password))), 32) AS App_Password , b.password AS App_PasswordSrc , CASE WHEN b.dptype <> 0 THEN ISNULL(a.depotid,'') + '_' + d_name ELSE b.username END AS App_UserName_CN , GETDATE() Chang_Password_Time , 1 Checked , 'system' Check_User , GETDATE() Check_Time , GETDATE() Createtime , GETDATE() Updatetime, 0 as oprationFlag FROM j_user b LEFT JOIN j_depot a ON a.depotid = b.depotid "); Dbconn connMDM = new Dbconn("VexSSO"); try { ArrayList tableList = new ArrayList(); connMDM.BeginTransaction(); tableList.Add("MDM_System_User"); connMDM.TableLock(tableList); strEnv_ID = connMDM.GetDataTableFirstValue("SELECT [Env_ID] FROM [MDM_System_Env] WHERE Env_SN='" + strEnv_SN + "'").ToString(); DataTable dtMDM = connMDM.GetDataTable(@" SELECT [App_UserID] ,[Env_ID] ,[VexSSONewID] ,[App_UserName] ,[App_Password] ,[App_PasswordSrc] ,[App_UserName_CN] ,[Chang_Password_Time] ,[Checked] ,[Check_User] ,[Check_Time] ,[Createtime] ,[Updatetime] FROM [dbo].[MDM_System_User] WHERE Env_ID='" + strEnv_ID + @"' "); DataTable dtMDMUpdate = dtMDM.Clone(); DataTable dtMDMInsert = dtMDM.Clone(); DataTable dtMDMDelete = dtMDM.Clone(); for (int i = 0; i < dtMDM.Rows.Count; i++) { string strApp_UserName = dtMDM.Rows[i]["App_UserName"].ToString(); DataRow[] dr = ds.Tables[0].Select("App_UserName='******'"); if (dr.Length > 0) { dtMDM.Rows[i]["App_Password"] = dr[0]["App_Password"]; dtMDM.Rows[i]["App_PasswordSrc"] = dr[0]["App_PasswordSrc"]; dtMDM.Rows[i]["App_UserName_CN"] = dr[0]["App_UserName_CN"]; dtMDMUpdate.ImportRow(dtMDM.Rows[i]); dr[0]["oprationFlag"] = "1"; } else { dtMDMDelete.ImportRow(dtMDM.Rows[i]); } } DataRow[] drInsert = ds.Tables[0].Select("oprationFlag=0"); for (int i = 0; i < drInsert.Length; i++) { DataRow dr = dtMDMInsert.NewRow(); dr["Env_ID"] = strEnv_ID; dr["VexSSONewID"] = drInsert[i]["VexSSONewID"]; dr["App_UserName"] = drInsert[i]["App_UserName"]; dr["App_Password"] = drInsert[i]["App_Password"]; dr["App_PasswordSrc"] = drInsert[i]["App_PasswordSrc"]; dr["App_UserName_CN"] = drInsert[i]["App_UserName_CN"]; dr["Chang_Password_Time"] = drInsert[i]["Chang_Password_Time"]; dr["Checked"] = drInsert[i]["Checked"]; dr["Check_User"] = drInsert[i]["Check_User"]; dr["Check_Time"] = drInsert[i]["Check_Time"]; dr["Createtime"] = drInsert[i]["Createtime"]; dr["Updatetime"] = drInsert[i]["Updatetime"]; dtMDMInsert.Rows.Add(dr); } if (dtMDMDelete.Rows.Count > 0) { for (int i = 0; i < dtMDMDelete.Rows.Count; i++) { string strWhere = "App_UserID='" + dtMDMDelete.Rows[i]["App_UserID"] + "'"; connMDM.Delete("MDM_System_User", strWhere); } } if (dtMDMUpdate.Rows.Count > 0) { ArrayList listKey = new ArrayList(); listKey.Add("App_UserID"); connMDM.Update("MDM_System_User", dtMDMUpdate, listKey); } if (dtMDMInsert.Rows.Count > 0) { dtMDMUpdate.Columns.Remove("App_UserID"); connMDM.InsertBYSQLBC("MDM_System_User", dtMDMInsert); } connMDM.CommitTransaction(); return true; } catch { connMDM.RollbackTransaction(); throw; } }
/// <summary> /// 按照传过来的参数确定最大号 /// </summary> /// <param name="pstrCodeType">类型</param> /// <param name="pstrParam0">参数0</param> /// <param name="pstrParam1">参数1</param> /// <param name="pstrParam2">参数2</param> /// <param name="m_conn">参数3,连接</param> /// <returns></returns> public static string GetMaxKeyCode(string pstrCodeType, string pstrParam0, string pstrParam1, string pstrParam2, Dbconn m_conn) { try { DataSet ds_Return = new DataSet(); DataTable resultTable = new DataTable(); string strMaxPurchaseCode = "";//最大采购单号为空 int nMaxPurchaseCode = 0;//采购单号int string MaxDeliveryplanDataCode = ""; string strMaxDeliveryplanDataCode = "0"; if (pstrCodeType == "服装童装配饰") { //按照条件获取采购单最大编号 strMaxPurchaseCode = m_conn.GetDataTableFirstValue(@" SELECT MAX(PO1_POCode) PO1_POCode FROM [B02_BILL].[PO1_POHead] where PO1_POCode like '24%'").ToString(); //如果最大号为空,放默认值2400000 if (strMaxPurchaseCode == "") { nMaxPurchaseCode = 2400000; } else { //取最大号,加1 nMaxPurchaseCode = Convert.ToInt32(strMaxPurchaseCode) + 1; } return nMaxPurchaseCode.ToString("0000000"); //返回string } else if (pstrCodeType == "新增合同编号") { string strMaxContractCode = ""; string strYear = DateTime.Now.Year.ToString().Substring(2, 2); string strMonth = DateTime.Now.Month.ToString("00"); strMaxContractCode = m_conn.GetDataTableFirstValue(@" SELECT MAX(PC1_ContractCode) FROM [B02_BILL].[PC1_POContractHead] where PC1_RegionCode = '" + pstrParam0 + "' AND PC1_POTypeCode = '" + pstrParam1 + "'").ToString(); if (strMaxContractCode == "") { strMaxContractCode = pstrParam0 + pstrParam1 + strYear + strMonth + "001"; } else { string strRight = strMaxContractCode.Substring(6, 3); //取流水号 string strOldYear = strMaxContractCode.Substring(2, 2); //取年份 string strOldMonth = strMaxContractCode.Substring(4, 2); //取月份 if (Convert.ToInt32(strYear) == Convert.ToInt32(strOldYear) && Convert.ToInt32(strMonth) == Convert.ToInt32(strOldMonth)) { int ContractNo = Convert.ToInt32(strRight) + 1; if (ContractNo.ToString().Length == 1) { strMaxContractCode = pstrParam0 + pstrParam1 + strYear + strMonth + "00" + ContractNo.ToString(); } else if (ContractNo.ToString().Length == 2) { strMaxContractCode = pstrParam0 + pstrParam1 + strYear + strMonth + "0" + ContractNo.ToString(); } else { strMaxContractCode = pstrParam0 + pstrParam1 + strYear + strMonth + ContractNo.ToString(); } } else { strMaxContractCode = pstrParam0 + pstrParam1 + strYear + strMonth + "001"; } } return strMaxContractCode; } else if (pstrCodeType == "新增收货单编号") { string strMaxReceiptCode = m_conn.GetDataTableFirstValue(@" SELECT MAX(RP1_ReceiptCode) FROM [B02_BILL].[RP1_POReceiptHead] where RP1_PO1_POCode = '" + pstrParam0 + "'").ToString(); if (strMaxReceiptCode != "") { int nNum = Convert.ToInt32(strMaxReceiptCode.Substring(9, 2)) + 1; string strNum = ""; if (nNum < 10) { strNum = "0" + nNum; } else { strNum = nNum.ToString(); }; strMaxReceiptCode = "RE" + pstrParam0 + strNum; } else { strMaxReceiptCode = "RE" + pstrParam0 + "01"; } return strMaxReceiptCode; } else if (pstrCodeType == "新增上货编号") { //最大编号 string strSQL = @"SELECT MAX([Code]) FROM [B02_BILL].[CO1_CustomerOrderHead] WITH (NOLOCK) WHERE [Code] LIKE 'CO" + pstrParam0 + "%'"; string strMaxCoCode = m_conn.GetDataTableFirstValue(strSQL).ToString(); ////日期 //string strNow = DateTime.Now.Year.ToString().Substring(2, 2); //Int32 intMonth = Convert.ToInt32(DateTime.Now.Month.ToString()); //Int32 intDay = Convert.ToInt32(DateTime.Now.Day.ToString()); ////月 //if ( intMonth < 10){ // strNow += "0" + intMonth.ToString(); //}else{ // strNow += intMonth.ToString(); //} ////日 //if (intDay < 10) //{ // strNow += "0" + intDay.ToString(); //} //else { // strNow += intDay.ToString(); //} if (strMaxCoCode != "") { string strMaxNow = strMaxCoCode.Substring(2, 6); string strNum = ""; if (pstrParam0 == strMaxNow) { int nNum = Convert.ToInt32(strMaxCoCode.Substring(8, 3)) + 1; if (nNum < 10) { strNum = "00" + nNum.ToString(); } else if (nNum >= 10 && nNum < 100) { strNum = "0" + nNum.ToString(); } else { strNum = nNum.ToString(); } strMaxCoCode = "CO" + pstrParam0 + strNum; } else { strMaxCoCode = "CO" + pstrParam0 + "001"; }; } else { strMaxCoCode = "CO" + pstrParam0 + "001"; } return strMaxCoCode; } else if (pstrCodeType == "新增退货单编号") { string strMaxCliamCode = m_conn.GetDataTableFirstValue(@" SELECT MAX(RT1_RetreatCode) FROM [B02_BILL].[RT1_RetreatHEAD] where RT1_PO1_POCode = '" + pstrParam1+ "'").ToString(); if (strMaxCliamCode != "") { int nNum = Convert.ToInt32(strMaxCliamCode.Substring(9, 2)) + 1; string strNum = ""; if (nNum < 10) { strNum = "0" + nNum; } else { strNum = nNum.ToString(); }; strMaxCliamCode = "RT" + pstrParam1 + strNum; } else { strMaxCliamCode = "RT" + pstrParam1 + "01"; } return strMaxCliamCode; } else if (pstrCodeType == "家具家饰") { //取采购类型是家具家饰最大号 strMaxPurchaseCode = m_conn.GetDataTableFirstValue(@" SELECT MAX(PO1_POCode) PO1_POCode FROM [B02_BILL].[PO1_POHead] where PO1_POCode like '25%'").ToString(); //如果为空放默认值2500000,如果不为空,数据库最大号加1 if (strMaxPurchaseCode == "") { nMaxPurchaseCode = 2500000; } else { nMaxPurchaseCode = Convert.ToInt32(strMaxPurchaseCode) + 1; } return nMaxPurchaseCode.ToString("0000000"); } else if (pstrCodeType == "新增到货计划编号") { DateTime Newdatetime = DateTime.Now;//取本地当前时间 //string strDatetime = pstrParam0.Substring(2,8).ToString(); // string strDateTime = pstrParam0.Substring(2, 2) + pstrParam0.Substring(5, 2) + pstrParam0.Substring(8, 2);//截取组成yymmdd格式 //取最大号规格是PL+yymmdd+流水号 MaxDeliveryplanDataCode = m_conn.GetDataTableFirstValue(@" SELECT MAX(AP1_APCode) FROM [B02_BILL].[AP1_APOHead] WHERE AP1_APCode LIKE 'PL" + strDateTime + "%'").ToString(); //如果为空,默认放置PL+yymmdd+01 if (MaxDeliveryplanDataCode == "") { strMaxDeliveryplanDataCode = "PL" + strDateTime + "01"; } else { //如果不为空,先截取PL,再截取中间时间,再截取最后流水号+1。 strMaxDeliveryplanDataCode = MaxDeliveryplanDataCode.Substring(0, MaxDeliveryplanDataCode.Length - 2) + (Convert.ToInt32(MaxDeliveryplanDataCode.Substring(8, 2)) + 1).ToString("00"); } return strMaxDeliveryplanDataCode; } else if (pstrCodeType == "新增付款申请单") { //取付款申请单最大号格式PR+采购单号+流水号,默认是PR+采购单号+01 string MaxPR1_RequestCode = m_conn.GetDataTableFirstValue(@" SELECT MAX(PR1_RequestCode) FROM [B02_BILL].PR1_PaymentRequisit WHERE PR1_RequestCode LIKE 'PR" + pstrParam0 + "%'").ToString(); if (MaxPR1_RequestCode == "") { MaxPR1_RequestCode = "PR" + pstrParam0 + "01"; } else { MaxPR1_RequestCode = MaxPR1_RequestCode.Substring(0, MaxPR1_RequestCode.Length - 2) + (Convert.ToInt32(MaxPR1_RequestCode.Substring(9, 2)) + 1).ToString("00"); } return MaxPR1_RequestCode; } //采购子表新增项次 else if (pstrCodeType == "新增项次") { int nMaxPO2_LISeq = 0;//默认是0 //取数据库,当前采购单子行,最大项次号,如果为空,默认放置1, string MaxPO2_LISeq = m_conn.GetDataTableFirstValue(@" SELECT MAX(PO2_LISeq) FROM [B02_BILL].[PO2_PODetail] WHERE PO2_POCode = '" + pstrParam0 + "'").ToString(); if (MaxPO2_LISeq == "") { nMaxPO2_LISeq = 1; } else { nMaxPO2_LISeq = Convert.ToInt32(MaxPO2_LISeq) + 1; } return nMaxPO2_LISeq.ToString("0"); } else if (pstrCodeType == "新增付款单") { //取付款单最大号,规格是PY+采购单号+流水号,如果为空放默认值,PY+采购单号+流水号01开始 string MaxPB1_PaymentBill = m_conn.GetDataTableFirstValue(@" SELECT MAX(PB1_PayBillCode) FROM [B02_BILL].[PB1_PaymentBill] WHERE PB1_PayBillCode LIKE 'PY" + pstrParam0 + "%'").ToString(); if (MaxPB1_PaymentBill == "") { MaxPB1_PaymentBill = "PY" + pstrParam0 + "01"; } else { MaxPB1_PaymentBill = MaxPB1_PaymentBill.Substring(0, MaxPB1_PaymentBill.Length - 2) + (Convert.ToInt32(MaxPB1_PaymentBill.Substring(9, 2)) + 1).ToString("00"); } return MaxPB1_PaymentBill; } else if (pstrCodeType == "新增供应商") { if (pstrParam1 == "C")//如果是国内,取参数0、1、2 分类、币种、特征 为条件获取最大值 { string MaxSupplierCode = m_conn.GetDataTableFirstValue(@" SELECT MAX(SP1_SupplierCode) FROM [B01_MDM].[SP1_Supplier] WHERE SP1_SupplierCode LIKE '" + pstrParam0 + pstrParam1 + pstrParam2 + "%'").ToString(); if (MaxSupplierCode == "") { MaxSupplierCode = pstrParam0 + pstrParam1 + pstrParam2 + "001"; } else { //如果存在,截取最后3位加1 MaxSupplierCode = MaxSupplierCode.Substring(0, MaxSupplierCode.Length - 3) + (Convert.ToInt32(MaxSupplierCode.Substring(3, 3)) + 1).ToString("000"); } return MaxSupplierCode; } else { //如果是国外,直接取供应商表A开头的供应商最大号加1 string MaxSupplierCode = m_conn.GetDataTableFirstValue(@" SELECT MAX(SP1_SupplierCode) FROM [B01_MDM].[SP1_Supplier] WHERE SP1_SupplierCode LIKE 'A%' AND LEN(SP1_SupplierCode)=5").ToString(); if (MaxSupplierCode == "") { MaxSupplierCode = "A0001"; } else { //取最大号加1 MaxSupplierCode = MaxSupplierCode.Substring(0, MaxSupplierCode.Length - 4) + (Convert.ToInt32(MaxSupplierCode.Substring(1, MaxSupplierCode.Length - 1)) + 1).ToString("0000"); } return MaxSupplierCode; } } else if (pstrCodeType == "新增品牌") { // string MaxBrandCode = m_conn.GetDataTableFirstValue(@" // SELECT MAX(BR1_BrandCode) // FROM [B01_MDM].[BR1_Brand]").ToString(); // if (MaxBrandCode == "") // { // MaxBrandCode = "Z001"; // } // else // { // MaxBrandCode = MaxBrandCode.Substring(0, MaxBrandCode.Length - 3) + (Convert.ToInt32(MaxBrandCode.Substring(1, 3)) + 1).ToString("000"); // } // return MaxBrandCode; int strMaxBrandCode = 10; for (int i = 10; i < 99; i++) { string strMaxCode = m_conn.GetDataTableFirstValue(@" SELECT * FROM [B01_MDM].[BR1_Brand] WHERE BR1_BrandCode = '" + i + "'").ToString(); if (strMaxCode == "") { strMaxBrandCode = i; break; } } return strMaxBrandCode.ToString(); } else if (pstrCodeType == "获取SKC最大号") { string strMaxSKCCode = m_conn.GetDataTableFirstValue(@" SELECT MAX(ST1_StyleCode) maxskc FROM [B01_MDM].[ST1_Style] WHERE ST1_StyleCode LIKE '" + pstrParam0 + "%'").ToString(); if (strMaxSKCCode == "") { strMaxSKCCode = pstrParam0 + "001"; } else { //strMaxSKCCode = strMaxSKCCode.Substring(0, strMaxSKCCode.Length - 4) + (Convert.ToInt32(strMaxSKCCode.Substring(6, 3)) + 1).ToString("000"); strMaxSKCCode = strMaxSKCCode.Substring(0, 6) + (Convert.ToInt32(strMaxSKCCode.Substring(6, 3)) + 1).ToString("000"); } return strMaxSKCCode; } return ""; } catch { throw; } }
public DataSet GetData() { try { DataSet ds = new DataSet(); DataSet ds_Return = new DataSet(); string strXML = ""; strXML = m_request.Tables[0].Rows[0]["XML"].ToString(); ds = Common.Common.GetDSByExcelXML(strXML); DataTable dtOPTYPE = ds.Tables["OPTYPE"]; DataTable dtList = ds.Tables["List"]; var strzt = dtList.Rows[0]["zt"].ToString();//获取帐套 conn = new Dbconn("PLM_AS_" + strzt); if (dtOPTYPE.Rows[0][0].ToString().ToUpper() == "SendAM".ToUpper()) { string strProductName = dtList.Rows[0]["ProductName"].ToString(); string strUserPost = conn.GetDataTableFirstValue(@" SELECT UserPost FROM Userinfo WHERE username = @Param0 ", new string[] { m_hzyMessage.User_Name }).ToString(); string strUserNameC = conn.GetDataTableFirstValue(@" SELECT UserNameC FROM Userinfo WHERE username = @Param0 ", new string[] { m_hzyMessage.User_Name }).ToString(); string strSQL = @" SELECT Designer,DesignerId,OrderSeason,producttype,part, brand FROM dbo.FLEXPLMProduct WHERE ProductName=@Param0" ; DataTable dt = conn.GetDataTable(strSQL, new string[] { strProductName }); for (int i = 0; i < dt.Rows.Count; i++) { string strContent = @"{6}你好:{0}于{1}变更了设计号为{2}的款式信息,变更后信息为: <p> 设计号:{2}<p> <p> 订货会季节:{3}<p> <p> 款式类型:{4}<p> <p> 款式类别:{5}<p> <p> 请及时跟踪!<p> "; string strDesigner = dt.Rows[i]["Designer"].ToString(); string strBrand = dt.Rows[i]["brand"].ToString(); if (strDesigner != strUserNameC) { string strContentSend = string.Format(strContent, new string[] { strUserNameC, DateTime.Now.ToString("yyyy年MM月dd日"), dt.Rows[i]["DesignerId"].ToString(), dt.Rows[i]["OrderSeason"].ToString(), dt.Rows[i]["producttype"].ToString(), dt.Rows[i]["part"].ToString() , strDesigner}); if (Common.Common.m_RouteCode != null && Common.Common.m_RouteCode.ToUpper() == "PROD") { AM.AMSendMsg.Send("AM", "PLMAS", strDesigner, "PLM系统", strContentSend, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); } else { AM.AMSendMsg.Send("AM", "PLMAS", "马卫清", "PLM系统", strContentSend, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); } } DataTable dt1 = conn.GetDataTable(@" SELECT UserNameC FROM dbo.USERINFO WHERE UserPost='版师助理' AND OwnedBrand=@param0 ", new string[] { strBrand }); for (int j = 0; j < dt1.Rows.Count; j++) { strDesigner = dt1.Rows[j][0].ToString(); string strContentSend = string.Format(strContent, new string[] { strUserNameC, DateTime.Now.ToString("yyyy年MM月dd日"), dt.Rows[i]["DesignerId"].ToString(), dt.Rows[i]["OrderSeason"].ToString(), dt.Rows[i]["producttype"].ToString(), dt.Rows[i]["part"].ToString(), strDesigner }); //正式库 if (Common.Common.m_RouteCode != null && Common.Common.m_RouteCode.ToUpper() == "PROD") { AM.AMSendMsg.Send("AM", "PLMAS", strDesigner, "PLM系统", strContentSend, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); } else//其他系统 { AM.AMSendMsg.Send("AM", "PLMAS", "卞磊", "PLM系统", strContentSend, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); } } } } dt_EditResult.Rows.Add(new object[] { true, "操作成功" }); ds_Return.Tables.Add(dt_EditResult); return ds_Return; } catch { conn.RollbackTransaction(); throw; } }
/// <summary> /// 根据MDM国家区域信息来编辑Entity /// </summary> /// <returns></returns> public DataSet GetData() { Dbconn conn = new Dbconn("MDM_Master"); try { string strXML = m_request.Tables[0].Rows[0]["XML"].ToString(); DataSet ds = Common.Common.GetDSByExcelXML(strXML); GetEntityCode(ds); if (m_EntityCode_S == "") { return new DataSet(); } ds.Tables["LIST"].Columns.Remove("type"); //获所有属性 MDM_Entity_Market_PropertyList clsPropertyList = new MDM_Entity_Market_PropertyList(); clsPropertyList.Request = Common.Common.GetRequestDataSet(new string[2] { "Entity", "MarketTableName" }, new string[2] { m_EntityCode_S, m_MKTTableName }); DataTable dt_PropertyList = clsPropertyList.GetData().Tables[0]; //修改国家数据 string strUpdateXML = "<table Entity=\"" + m_EntityCode_S + "\" Expression=\"CN\">"; for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++) { strUpdateXML += "<row>"; strUpdateXML += "<body_ID>" + ds.Tables["LIST"].Rows[i]["body_ID_" + m_EntityCode_S].ToString() + "</body_ID>"; for (int j = 0; j < ds.Tables["LIST"].Columns.Count; j++) { string strColumnName = ds.Tables["LIST"].Columns[j].ColumnName; //如果修改的列是属于国家信息的话,要对洲的Entity进行修改 DataRow[] drList = dt_PropertyList.Select("Table_Property_Name = '" + strColumnName + "'"); if (drList.Length > 0) { string strPorpertyname = drList[0]["Porperty_Name"].ToString(); strUpdateXML += "<" + strPorpertyname + ">" + ds.Tables["LIST"].Rows[i][j].ToString() + "</" + strPorpertyname + ">"; } } strUpdateXML += "</row>"; } strUpdateXML += "</table>"; MDM_Entity_Edit cls_Entity_Edit = new MDM_Entity_Edit(); cls_Entity_Edit.Request = Common.Common.GetRequestDataSet(new string[1] { "XML" }, new string[1] { strUpdateXML }); cls_Entity_Edit.Execute(); ArrayList alBodyID = cls_Entity_Edit.BodyID; //修改国家与洲的Link strUpdateXML = "<table Entity=\"" + m_LinkEntityCode + "\" Expression=\"CN\">"; for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++) { //获取国家的bodyid string strBodyID = alBodyID[i].ToString(); //到表里查找Link的BodyID string strSQL = "SELECT Body_ID FROM MDM_" + m_LinkEntityCode + "_B WHERE Body_ID_" + m_EntityCode_S + "='" + strBodyID + "'"; string strLinkBodyID = conn.GetDataTableFirstValue(strSQL).ToString(); strUpdateXML += "<row>"; strUpdateXML += "<body_ID>" + strLinkBodyID + "</body_ID>"; strUpdateXML += "<body_ID_" + m_EntityCode_S + ">" + strBodyID + "</body_ID_" + m_EntityCode_S + ">"; strUpdateXML += "<body_ID_" + m_EntityCode_P + ">" + ds.Tables["LIST"].Rows[i]["body_ID_" + m_EntityCode_P].ToString() + "</body_ID_" + m_EntityCode_P + ">"; strUpdateXML += "</row>"; } strUpdateXML += "</table>"; cls_Entity_Edit.Request = Common.Common.GetRequestDataSet(new string[1] { "XML" }, new string[1] { strUpdateXML }); cls_Entity_Edit.Execute(); return Common.Common.GetRequestDataSet(new string[1] { "result" }, new string[1] { "true" }); } catch { throw; } }
private void Getdata() { string strConnstr = ConfigurationManager.AppSettings["ConnectionString_POS"]; Dbconn conn = new Dbconn(strConnstr,true); string strPhone = this.txtPhone.Text; if (strPhone.Trim() != "") { string strName = conn.GetDataTableFirstValue(@"SELECT vipname as 姓名, mobtel as 手机号, vipcode as 卡号 FROM vip_user WHERE mobtel=@Param0 OR vipcode=@Param0", new string[] { strPhone }).ToString(); this.lblName.Text = strName; DataSet ds = conn.GetDataSet(@"SELECT b.ticketid AS 券号 , a.sums AS 面值 , CASE ISNULL(b.getsure,0)WHEN 0 THEN '否' ELSE '是' END AS 是否使用 , b.crdate AS 创建日期 , b.verifycode AS 验证码,a.mobtel, a.vipcode, a.vipid FROM vip_user a INNER JOIN vip_ticket b ON a.vipid = b.vipid WHERE b.tictypeid = '31' AND (a.mobtel=@param0 OR a.vipcode=@param0) ORDER BY b.crdate DESC ", new string[] { strPhone }); RpList.DataSource = ds.Tables[0]; RpList.DataBind(); if (ds.Tables[0].Rows.Count == 0) { this.lblMessage.Text = "<font color='red'>没有查询到手机号/卡号为" + strPhone + "的相关信息,请重试</font>"; } else { this.lblMessage.Text = ""; } } else { this.lblMessage.Text = ""; RpList.DataSource = new DataTable(); RpList.DataBind(); } }
public DataSet GetData() { try { DataSet ds = new DataSet(); DataSet ds_Return = new DataSet(); string strXML = ""; strXML = m_request.Tables[0].Rows[0]["XML"].ToString(); ds = Common.Common.GetDSByExcelXML(strXML); DataTable dtOPTYPE = ds.Tables["OPTYPE"]; DataTable dtList = ds.Tables["List"]; var strzt = dtList.Rows[0]["zt"].ToString();//获取帐套 conn = new Dbconn("PLM_AS_" + strzt); if (dtOPTYPE.Rows[0][0].ToString().ToUpper() == "GETComboxBySession".ToUpper()) { string strUserPost = conn.GetDataTableFirstValue(@" SELECT UserPost FROM Userinfo WHERE username = @Param0 ", new string[] { m_hzyMessage.User_Name }).ToString(); string strSQL = ""; if (strUserPost == "管理员") { strSQL = @" SELECT UserNameC FROM Userinfo WHERE UserPost = '设计师'"; } else if (strUserPost == "设计师" || strUserPost == "版师助理") { strSQL = @" SELECT UserNameC FROM Userinfo WHERE OwnedBrand IN ( SELECT OwnedBrand FROM Userinfo WHERE username=@param0 ) and UserPost = '设计师' "; } else { strSQL = @" SELECT UserNameC FROM Userinfo WHERE username=@param0"; } ds_Return = conn.GetDataSet(strSQL, new string[] { m_hzyMessage.User_Name }); } return ds_Return; } catch { conn.RollbackTransaction(); throw; } }
public bool Execute() { string strEnv_SN = "Present"; string strEnv_ID = ""; Dbconn conn_EHR = new Dbconn("EHR"); DataSet ds = conn_EHR.GetDataSet( @" SELECT NULL AS VexSSONewID , t2.C_PASSWORD [App_Password], t2.C_PASSWORD App_PasswordSrc, t1.C_CODE App_UserName, t1.C_NAME App_UserName_CN, GETDATE() Chang_Password_Time , 1 Checked , 'system' Check_User , GETDATE() Check_Time , GETDATE() Createtime , GETDATE() Updatetime , 0 AS oprationFlag FROM ehr.TB_INF_EMPLOYEE t1 LEFT JOIN ehr.TB_SYS_USER t2 ON t1.C_OID = t2.C_EMPLOYEEID WHERE t1.C_EMPLOYEESTATUS!=3 "); Dbconn connMDM = new Dbconn("VexSSO"); try { ArrayList tableList = new ArrayList(); connMDM.BeginTransaction(); tableList.Add("MDM_System_User"); connMDM.TableLock(tableList); strEnv_ID = connMDM.GetDataTableFirstValue("SELECT [Env_ID] FROM [MDM_System_Env] WHERE Env_SN='" + strEnv_SN + "'").ToString(); DataTable dtMDM = connMDM.GetDataTable(@" SELECT [App_UserID] ,[Env_ID] ,[VexSSONewID] ,[App_UserName] ,[App_Password] ,[App_PasswordSrc] ,[App_UserName_CN] ,[Chang_Password_Time] ,[Checked] ,[Check_User] ,[Check_Time] ,[Createtime] ,[Updatetime] FROM [dbo].[MDM_System_User] WHERE Env_ID='" + strEnv_ID + @"'"); DataTable dtMDMUpdate = dtMDM.Clone(); DataTable dtMDMInsert = dtMDM.Clone(); DataTable dtMDMDelete = dtMDM.Clone(); for (int i = 0; i < dtMDM.Rows.Count; i++) { string strApp_UserName = dtMDM.Rows[i]["App_UserName"].ToString(); DataRow[] dr = ds.Tables[0].Select("App_UserName='******'"); if (dr.Length > 0) { dtMDM.Rows[i]["App_Password"] = dr[0]["App_Password"]; dtMDM.Rows[i]["App_PasswordSrc"] = dr[0]["App_PasswordSrc"]; dtMDM.Rows[i]["App_UserName_CN"] = dr[0]["App_UserName_CN"]; dtMDMUpdate.ImportRow(dtMDM.Rows[i]); dr[0]["oprationFlag"] = "1"; } else { dtMDMDelete.ImportRow(dtMDM.Rows[i]); } } DataRow[] drInsert = ds.Tables[0].Select("oprationFlag=0"); for (int i = 0; i < drInsert.Length; i++) { DataRow dr = dtMDMInsert.NewRow(); dr["Env_ID"] = strEnv_ID; dr["VexSSONewID"] = drInsert[i]["VexSSONewID"]; dr["App_UserName"] = drInsert[i]["App_UserName"]; dr["App_Password"] = drInsert[i]["App_Password"]; dr["App_PasswordSrc"] = drInsert[i]["App_PasswordSrc"]; dr["App_UserName_CN"] = drInsert[i]["App_UserName_CN"]; dr["Chang_Password_Time"] = drInsert[i]["Chang_Password_Time"]; dr["Checked"] = drInsert[i]["Checked"]; dr["Check_User"] = drInsert[i]["Check_User"]; dr["Check_Time"] = drInsert[i]["Check_Time"]; dr["Createtime"] = drInsert[i]["Createtime"]; dr["Updatetime"] = drInsert[i]["Updatetime"]; dtMDMInsert.Rows.Add(dr); } if (dtMDMDelete.Rows.Count > 0) { for (int i = 0; i < dtMDMDelete.Rows.Count; i++) { string strWhere = "App_UserID='" + dtMDMDelete.Rows[i]["App_UserID"] + "'"; connMDM.Delete("MDM_System_User", strWhere); } } if (dtMDMUpdate.Rows.Count > 0) { ArrayList listKey = new ArrayList(); listKey.Add("App_UserID"); connMDM.Update("MDM_System_User", dtMDMUpdate, listKey); } if (dtMDMInsert.Rows.Count > 0) { dtMDMUpdate.Columns.Remove("App_UserID"); connMDM.InsertBYSQLBC("MDM_System_User", dtMDMInsert); } connMDM.CommitTransaction(); return true; } catch { connMDM.RollbackTransaction(); throw; } }
/// <summary> /// 编辑WS信息 /// </summary> /// <returns></returns> public bool Execute() { Dbconn conn = new Dbconn("WSRR"); try { if(m_request == null || m_request.Tables.Count==0 || m_request.Tables[0].Rows.Count == 0) { return true; } string strWS_ID = m_request.Tables[0].Rows[0]["WS_ID"].ToString(); string strWS_Name = m_request.Tables[0].Rows[0]["WS_Name"].ToString(); ArrayList listTable = new ArrayList(); listTable.Add("WS"); conn.BeginTransaction(); conn.TableLock(listTable); string strWhere = " WS_Name='" + strWS_Name + "'"; if (strWS_ID != "") { strWhere += " AND WS_ID != '"+strWS_ID+"'"; } //判断是否已经存在 int nRow = conn.GetDataTableRowCount("SELECT 'A' as A FROM WS WHERE " + strWhere, new string[0]); if (nRow > 0) { throw new Exception(strWS_Name + "已经存在,不能更新"); } DSWSRR.WSDataTable dt_Add = new DSWSRR.WSDataTable(); dt_Add.PrimaryKey = null; dt_Add.Columns["WS_ID"].DataType = typeof(string); string strWS_IDSrc = ""; if (m_request.Tables[0].Columns.Contains("WS_IDSrc")) { strWS_IDSrc = m_request.Tables[0].Rows[0]["WS_IDSrc"].ToString(); } DataRow dr = dt_Add.NewRow(); for (int i = 0; i < dt_Add.Columns.Count-4; i++) { if (dt_Add.Columns[i].ColumnName == "WS_Host") { string strENV = ConfigurationManager.AppSettings["WSRR_ENV"]; if (strENV.ToLower() == "prod") { dr[i] = m_request.Tables[0].Rows[0]["WS_Host_Prod"].ToString(); } else { dr[i] = m_request.Tables[0].Rows[0]["WS_Host_Test"].ToString(); } } else { dr[i] = m_request.Tables[0].Rows[0][dt_Add.Columns[i].ColumnName].ToString(); } } dt_Add.Rows.Add(dr); if (strWS_ID == "") //如果为空,说明是复制数据 { dt_Add.Columns.Remove("Update_User"); dt_Add.Columns.Remove("Update_Time"); strWS_ID = conn.GetDataTableFirstValue("SELECT NEWID() as ID").ToString(); dt_Add.Rows[0]["WS_ID"] = strWS_ID; conn.Insert("WS", dt_Add); //添加参数表的信息 DataTable dtParam = conn.GetDataTable("SELECT * FROM WS_Param WHERE WS_ID='" + strWS_IDSrc + "'"); //更新WSID for (int i = 0; i < dtParam.Rows.Count; i++) { dtParam.Rows[i]["WS_ID"] = strWS_ID; } dtParam.Columns.Remove("Param_ID"); conn.Insert("WS_Param", dtParam); } else { dt_Add.Columns.Remove("Create_User"); dt_Add.Columns.Remove("Create_Time"); dt_Add.Rows[0]["Update_Time"] = DateTime.Now; strWhere = " WS_ID = '" + strWS_ID + "'"; conn.Update("WS", dt_Add, strWhere); } conn.CommitTransaction(); return true; } catch { conn.RollbackTransaction(); throw; } }
public string GetMerchatCode(string strBody_Id_0001, Dbconn conn, string strParentID) { string strCode = ""; string str = ""; if (strParentID != "") { //查找上级加盟商的最大值 str = conn.GetDataTableFirstValue(@"SELECT MAX(加盟商编码) FROM MDM_Market2.dbo.V_L0006 WHERE 父加盟商='" + strParentID + "'").ToString(); if (str == "") { //检查加盟商是否存在 str = conn.GetDataTableFirstValue(@"SELECT MAX(加盟商编码) FROM MDM_Market2.dbo.V_L0006 WHERE 加盟商编码='" + strParentID + "'").ToString(); if (str == "") { throw new Exception("父加盟商不正确"); } strCode = strParentID + "1"; } else if (str.Length != 5) { throw new Exception("父加盟商不正确"); } else { strCode = strParentID + Convert.ToInt32(str.Substring(4, 1) + 1).ToString(); if (strCode.Length == 6) { throw new Exception(strParentID + "的二级加盟商不允许超过10个,请联系IT部"); } } } else { str = conn.GetDataTableFirstValue(@"SELECT MAX(加盟商编码) FROM MDM_Market2.dbo.V_L0006 WHERE body_id_0001='" + strBody_Id_0001 + "'").ToString(); if (str == "") { str = conn.GetDataTableFirstValue(@"SELECT 公司前缀 FROM MDM_Market2.dbo.V_0001 WHERE body_id='" + strBody_Id_0001 + "'").ToString(); if (str == "") { throw new Exception("公司的前缀没有设置,请先设置公司前缀"); } strCode = str.Substring(0, 1) + "0000"; } else { strCode = str.Substring(0, 1) + (Convert.ToInt32(str.Substring(1)) + 1).ToString("0000"); } } return strCode; }
/// <summary> /// 编辑县级市信息 /// </summary> /// <returns></returns> public DataSet GetData() { Dbconn conn = new Dbconn("MDM_Master2"); try { DataSet ds_Return = new DataSet(); DataSet dsRequest = new DataSet(); string strXML = ""; strXML = m_request.Tables[0].Rows[0]["XML"].ToString(); if (strXML == "") { throw new Exception("提交的XML为空!"); } DataSet dsXML = Common.Common.GetDSByExcelXML(strXML); DataTable dt_OPTYPE = dsXML.Tables["OPTYPE"]; string strMDM_Class = "0016"; string strMDM_LinkClass = "L0011"; conn.LockTableList.Add("MDM_" + strMDM_LinkClass + "_B"); conn.LockTableList.Add("MDM_" + strMDM_Class + "_B"); conn.LockTableList.Add("MDM_" + strMDM_LinkClass + "_E"); conn.LockTableList.Add("MDM_" + strMDM_Class + "_E"); conn.BeginTransaction(); //获取所有属性 MDM2_GetEntityPropertyList cls = new MDM2_GetEntityPropertyList(); cls.hzyMessage = this.hzyMessage; cls.MainEntity = strMDM_Class; DataTable dtPorperty = cls.GetData().Tables[0]; for (int i = 0; i < dt_OPTYPE.Rows.Count; i++) { DataTable dt_List = dsXML.Tables[i + 1]; if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "UPDATE" || dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "INSERT" ) { //插入BODY表 for (int j = 0; j < dt_List.Rows.Count; j++) { string strBody_id_0016 = ""; string strBody_id_L0011 = ""; string strBody_id_0015 = null;//用于存放地级市的信息,如果为null,表示不做变更,如果不为NULL,需要变更 if (dt_List.Columns.Contains("Body_id_0015")) { strBody_id_0015 = dt_List.Rows[j]["Body_id_0015"].ToString(); if (strBody_id_0015 != "") { //核对boid_ID是否存在 MDM2_BodyIDCheck clsCheck = new MDM2_BodyIDCheck(); clsCheck.Conn = conn; clsCheck.hzyMessage = this.hzyMessage; clsCheck.MDMClass = "0015"; clsCheck.Body_ID = strBody_id_0015; if (clsCheck.Execute() == false) { throw new Exception("省的信息不正确!"); } } } strBody_id_0016 = dt_List.Rows[j]["body_ID_0016"].ToString(); strBody_id_L0011 = dt_List.Rows[j]["body_ID"].ToString(); MDM2_EntityCreatedBodyID_Edit clsBody = new MDM2_EntityCreatedBodyID_Edit(); if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "INSERT") { //处理0016的实体 clsBody.Conn = conn; clsBody.hzyMessage = hzyMessage; clsBody.MDMClass = strMDM_Class; clsBody.Execute(); strBody_id_0016 = clsBody.BodyID; if (strBody_id_0016 == "") { throw new Exception("BodyID_0016创建失败!"); } if (strBody_id_0015 == null) { throw new Exception("省信息不正确!"); } strBody_id_L0011 = ""; } else if (strBody_id_0016 != "" && strBody_id_L0011 != "") //更新 { //核对0010是否存在 MDM2_BodyIDCheck clsCheck = new MDM2_BodyIDCheck(); clsCheck.hzyMessage = this.hzyMessage; clsCheck.MDMClass = strMDM_Class; clsCheck.Body_ID = strBody_id_0016; clsCheck.Conn = conn; if (clsCheck.Execute() == false) { throw new Exception("Body_id_0016信息不正确!"); } //核对L0010是否存在 clsCheck.hzyMessage = this.hzyMessage; clsCheck.MDMClass = strMDM_LinkClass; clsCheck.Body_ID = strBody_id_L0011; clsCheck.Conn = conn; if (clsCheck.Execute() == false) { throw new Exception("Body_id_L0011信息不正确!"); } } else { throw new Exception("传入的参数有误,如果反复出现,请重新登录!"); } clsBody.MDMClass = strMDM_LinkClass; clsBody.BodyID = strBody_id_L0011; clsBody.LinkBodyIDList = strBody_id_0015 + "," + strBody_id_0016; clsBody.Execute(); strBody_id_L0011 = clsBody.BodyID; //处理0015的实体 for (int k = 0; k < dt_List.Columns.Count; k++) { string strColumnName = dt_List.Columns[k].ColumnName; if (dtPorperty.Select("Property_Name='" + strColumnName + "'").Length == 0) { continue; } MDM2_EntityPropertyValue_Edit clsProperty = new MDM2_EntityPropertyValue_Edit(); clsProperty.Conn = conn; clsProperty.hzyMessage = hzyMessage; clsProperty.MDMClass = strMDM_Class; clsProperty.ExpressionName = "CN"; clsProperty.PropertyName = strColumnName; clsProperty.BodyID = strBody_id_0016; clsProperty.PropertyValue = dt_List.Rows[j][k].ToString(); if (clsProperty.Execute() == false) { throw new Exception("修改失败," + clsProperty.Message); } } } } else if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "DELETE") { for (int rowIndex = 0; rowIndex < dt_List.Rows.Count; rowIndex++) { string strBody_id_L0011 = dt_List.Rows[rowIndex]["body_id"].ToString(); string strBody_ID_0016 = conn.GetDataTableFirstValue("select body_id_0016 from MDM_L0011_B where Body_id=@param0", new string[1] { strBody_id_L0011 }).ToString(); MDM2_EntityDeleteBodyID_Edit clsDelete = new MDM2_EntityDeleteBodyID_Edit(); clsDelete.Conn = conn; clsDelete.hzyMessage = hzyMessage; //删除行政区实体的数据 if (strBody_ID_0016 != "") { clsDelete.MDMClass = strMDM_Class; clsDelete.BodyID = strBody_ID_0016; if (clsDelete.Execute() == false) { throw new Exception("删除失败。"); } } //删除Link数据 clsDelete.MDMClass = strMDM_LinkClass; clsDelete.BodyID = strBody_id_L0011; if (clsDelete.Execute() == false) { throw new Exception("删除失败。"); } } } } dt_EditResult.Rows.Add(new object[] { true, "操作成功" }); ds_Return.Tables.Add(dt_EditResult); conn.CommitTransaction(); return ds_Return; } catch { conn.RollbackTransaction(); throw; } }
public string Get拓展类型Changed(string strBody_Id_0006, string str拓展类型, Dbconn conn) { string strDepotName = conn.GetDataTableFirstValue(@"SELECT 店铺ID +' '+ isnull(店铺名称,'') as 店铺名称 FROM MDM_Market2.dbo.MKT_0006 WHERE body_id='" + strBody_Id_0006 + @"' AND 拓展类型 <> '" + str拓展类型 + "'").ToString(); return strDepotName; }
public string GetDepotID(string strBody_Id_L0006, Dbconn conn) { string strCode = ""; string str = conn.GetDataTableFirstValue(@"SELECT MAX(店铺ID) FROM MDM_Market2.dbo.V_L0013 WHERE body_id_L0006='" + strBody_Id_L0006 + @"' AND 店铺ID not like 'shop%'").ToString(); if (str == "") { str = conn.GetDataTableFirstValue(@"SELECT 加盟商编码 FROM MDM_Market2.dbo.V_L0006 WHERE body_id='" + strBody_Id_L0006 + "'").ToString(); if (str == "") { throw new Exception("加盟商编码没有设置,请先设置加盟商编码"); } if (str.Length == 5) { strCode = str + "000"; } else if (str.Length == 6) { strCode = str + "00"; } } else { strCode = str.Substring(0, 5) + (Convert.ToInt32(str.Substring(5)) + 1).ToString("000"); } return strCode; }
public bool Execute() { string strEnv_ID = ""; string strEnv_SN = "HZY_SCM"; Dbconn conn_PLM_AS = new Dbconn("ERP_PROD"); DataSet ds = conn_PLM_AS.GetDataSet( @" SELECT NULL AS VexSSONewID , IDSUNO AS App_UserName , RIGHT(sys.fn_VarBinToHexStr(HASHBYTES('MD5', CONVERT(VARCHAR(100), '123456a'))), 32) AS App_PasswordSrc , RIGHT(sys.fn_VarBinToHexStr(HASHBYTES('MD5', CONVERT(VARCHAR(100), '123456a'))), 32) AS App_Password , IDSUNM AS App_UserName_CN , GETDATE() Chang_Password_Time , 1 Checked , 'system' Check_User , GETDATE() Check_Time , GETDATE() Createtime , GETDATE() Updatetime , 0 AS oprationFlag FROM CIDMAS WHERE IDCONO = 100 AND IDSTAT = 20"); Dbconn connMDM = new Dbconn("VexSSO"); try { ArrayList tableList = new ArrayList(); connMDM.BeginTransaction(); tableList.Add("MDM_System_User"); connMDM.TableLock(tableList); strEnv_ID = connMDM.GetDataTableFirstValue("SELECT [Env_ID] FROM [MDM_System_Env] WHERE Env_SN='" + strEnv_SN + "'").ToString(); DataTable dtMDM = connMDM.GetDataTable(@" SELECT [App_UserID] ,[Env_ID] ,[VexSSONewID] ,[App_UserName] ,[App_Password] ,[App_PasswordSrc] ,[App_UserName_CN] ,[Chang_Password_Time] ,[Checked] ,[Check_User] ,[Check_Time] ,[Createtime] ,[Updatetime] FROM [dbo].[MDM_System_User] WHERE Env_ID='" + strEnv_ID + @"' "); DataTable dtMDMUpdate = dtMDM.Clone(); DataTable dtMDMInsert = dtMDM.Clone(); DataTable dtMDMDelete = dtMDM.Clone(); for (int i = 0; i < dtMDM.Rows.Count; i++) { string strApp_UserName = dtMDM.Rows[i]["App_UserName"].ToString(); DataRow[] dr = ds.Tables[0].Select("App_UserName='******'"); if (dr.Length > 0) { //只修改供应商名称不修改帐号密码 //dtMDM.Rows[i]["App_Password"] = dr[0]["App_Password"]; //dtMDM.Rows[i]["App_PasswordSrc"] = dr[0]["App_PasswordSrc"]; dtMDM.Rows[i]["App_UserName_CN"] = dr[0]["App_UserName_CN"]; dtMDMUpdate.ImportRow(dtMDM.Rows[i]); dr[0]["oprationFlag"] = "1"; } else { dtMDMDelete.ImportRow(dtMDM.Rows[i]); } } DataRow[] drInsert = ds.Tables[0].Select("oprationFlag=0"); for (int i = 0; i < drInsert.Length; i++) { DataRow dr = dtMDMInsert.NewRow(); dr["Env_ID"] = strEnv_ID; dr["VexSSONewID"] = drInsert[i]["VexSSONewID"]; dr["App_UserName"] = drInsert[i]["App_UserName"]; dr["App_Password"] = drInsert[i]["App_Password"]; dr["App_PasswordSrc"] = drInsert[i]["App_PasswordSrc"]; dr["App_UserName_CN"] = drInsert[i]["App_UserName_CN"]; dr["Chang_Password_Time"] = drInsert[i]["Chang_Password_Time"]; dr["Checked"] = drInsert[i]["Checked"]; dr["Check_User"] = drInsert[i]["Check_User"]; dr["Check_Time"] = drInsert[i]["Check_Time"]; dr["Createtime"] = drInsert[i]["Createtime"]; dr["Updatetime"] = drInsert[i]["Updatetime"]; dtMDMInsert.Rows.Add(dr); } if (dtMDMDelete.Rows.Count > 0) { for (int i = 0; i < dtMDMDelete.Rows.Count; i++) { string strWhere = "App_UserID='" + dtMDMDelete.Rows[i]["App_UserID"] + "'"; connMDM.Delete("MDM_System_User", strWhere); } } if (dtMDMUpdate.Rows.Count > 0) { ArrayList listKey = new ArrayList(); listKey.Add("App_UserID"); connMDM.Update("MDM_System_User", dtMDMUpdate, listKey); } if (dtMDMInsert.Rows.Count > 0) { dtMDMUpdate.Columns.Remove("App_UserID"); connMDM.InsertBYSQLBC("MDM_System_User", dtMDMInsert); } connMDM.CommitTransaction(); return true; } catch { connMDM.RollbackTransaction(); throw; } }
/// <summary> /// 按照传过来的参数确定最大号 /// </summary> /// <param name="pstrCodeType">类型</param> /// <param name="pstrParam0">参数0</param> /// <param name="pstrParam1">参数1</param> /// <param name="pstrParam2">参数2</param> /// <param name="m_conn">参数3,连接</param> /// <returns></returns> public static string GetMaxKeyCode(string pstrCodeType, string pstrParam0, string pstrParam1, string pstrParam2, Dbconn m_conn) { try { DataSet ds_Return = new DataSet(); DataTable resultTable = new DataTable(); string strMaxPurchaseCode = "";//最大采购单号为空 int nMaxPurchaseCode = 0;//采购单号int string MaxDeliveryplanDataCode = ""; string strMaxDeliveryplanDataCode = "0"; if (pstrCodeType == "采购单编号生成") { strMaxPurchaseCode = m_conn.GetDataTableFirstValue(@" SELECT MAX(PO1_POCode) PO1_POCode FROM [B02_BILL].[PO1_POHead] where PO1_POCode like '2" + pstrParam0 + "%' AND PO1_DIVI='" + pstrParam1 + "'").ToString(); //如果最大号为空,放默认值2400000 if (strMaxPurchaseCode == "") { nMaxPurchaseCode = Convert.ToInt32("2" + pstrParam0 + "00000"); } else { //取最大号,加1 nMaxPurchaseCode = Convert.ToInt32(strMaxPurchaseCode) + 1; } return nMaxPurchaseCode.ToString("0000000"); } else if (pstrCodeType == "新增合同编号") { string strMaxContractCode = ""; string strYear = DateTime.Now.Year.ToString().Substring(2, 2); string strMonth = DateTime.Now.Month.ToString("00"); strMaxContractCode = m_conn.GetDataTableFirstValue(@" SELECT MAX(PC1_ContractCode) FROM [B02_BILL].[PC1_POContractHead] where PC1_RegionCode = '" + pstrParam0 + "' AND PC1_POTypeCode = '" + pstrParam1 + "' AND PC1_DIVI='" + pstrParam2 + "'").ToString(); if (strMaxContractCode == "") { strMaxContractCode = pstrParam0 + pstrParam1 + strYear + strMonth + "0001"; } else { string strRight = strMaxContractCode.Substring(6, 4); //取流水号 string strOldYear = strMaxContractCode.Substring(2, 2); //取年份 string strOldMonth = strMaxContractCode.Substring(4, 2); //取月份 if (Convert.ToInt32(strYear) == Convert.ToInt32(strOldYear) && Convert.ToInt32(strMonth) == Convert.ToInt32(strOldMonth)) { int ContractNo = Convert.ToInt32(strRight) + 1; if (ContractNo.ToString().Length == 1) { strMaxContractCode = pstrParam0 + pstrParam1 + strYear + strMonth + "000" + ContractNo.ToString(); } else if (ContractNo.ToString().Length == 2) { strMaxContractCode = pstrParam0 + pstrParam1 + strYear + strMonth + "00" + ContractNo.ToString(); } else if (ContractNo.ToString().Length == 3) { strMaxContractCode = pstrParam0 + pstrParam1 + strYear + strMonth + "0" + ContractNo.ToString(); } else { strMaxContractCode = pstrParam0 + pstrParam1 + strYear + strMonth + ContractNo.ToString(); } } else { strMaxContractCode = pstrParam0 + pstrParam1 + strYear + strMonth + "0001"; } } return strMaxContractCode; } else if (pstrCodeType == "新增收货单编号") { string strMaxReceiptCode = m_conn.GetDataTableFirstValue(@" SELECT MAX(RP1_ReceiptCode) FROM [B02_BILL].[RP1_POReceiptHead] where RP1_PO1_POCode = '" + pstrParam0 + "'").ToString(); if (strMaxReceiptCode != "") { int nNum = Convert.ToInt32(strMaxReceiptCode.Substring(9, 2)) + 1; string strNum = ""; if (nNum < 10) { strNum = "0" + nNum; } else { strNum = nNum.ToString(); }; strMaxReceiptCode = "RE" + pstrParam0 + strNum; } else { strMaxReceiptCode = "RE" + pstrParam0 + "01"; } return strMaxReceiptCode; } else if (pstrCodeType == "新增上货编号") { //最大编号 string strSQL = @"SELECT MAX([Code]) FROM [B02_BILL].[CO1_CustomerOrderHead] WITH (NOLOCK) WHERE [Code] LIKE 'CO" + pstrParam0 + "%'"; string strMaxCoCode = m_conn.GetDataTableFirstValue(strSQL).ToString(); if (strMaxCoCode != "") { string strMaxNow = strMaxCoCode.Substring(2, 6); string strNum = ""; if (pstrParam0 == strMaxNow) { int nNum = Convert.ToInt32(strMaxCoCode.Substring(8, 3)) + 1; if (nNum < 10) { strNum = "00" + nNum.ToString(); } else if (nNum >= 10 && nNum < 100) { strNum = "0" + nNum.ToString(); } else { strNum = nNum.ToString(); } strMaxCoCode = "CO" + pstrParam0 + strNum; } else { strMaxCoCode = "CO" + pstrParam0 + "001"; }; } else { strMaxCoCode = "CO" + pstrParam0 + "001"; } return strMaxCoCode; } else if (pstrCodeType == "新增退货单编号") { string strMaxCliamCode = m_conn.GetDataTableFirstValue(@" SELECT MAX(RT1_RetreatCode) FROM [B02_BILL].[RT1_RetreatHEAD] where RT1_PO1_POCode = '" + pstrParam1 + "'").ToString(); if (strMaxCliamCode != "") { int nNum = Convert.ToInt32(strMaxCliamCode.Substring(9, 2)) + 1; string strNum = ""; if (nNum < 10) { strNum = "0" + nNum; } else { strNum = nNum.ToString(); }; strMaxCliamCode = "RT" + pstrParam1 + strNum; } else { strMaxCliamCode = "RT" + pstrParam1 + "01"; } return strMaxCliamCode; } else if (pstrCodeType == "新增到货计划编号") { DateTime Newdatetime = DateTime.Now;//取本地当前时间 //string strDatetime = pstrParam0.Substring(2,8).ToString(); // string strDateTime = pstrParam0.Substring(2, 2) + pstrParam0.Substring(5, 2) + pstrParam0.Substring(8, 2);//截取组成yymmdd格式 //取最大号规格是PL+yymmdd+流水号 MaxDeliveryplanDataCode = m_conn.GetDataTableFirstValue(@" SELECT MAX(AP1_APCode) FROM [B02_BILL].[AP1_APOHead] WHERE AP1_APCode LIKE 'PL" + strDateTime + "%' AND AP1_DIVI='" + pstrParam1 + "'").ToString(); //集团版增加环境条件 //如果为空,默认放置PL+yymmdd+01 if (MaxDeliveryplanDataCode == "") { strMaxDeliveryplanDataCode = "PL" + strDateTime + "01"; } else { //如果不为空,先截取PL,再截取中间时间,再截取最后流水号+1。 strMaxDeliveryplanDataCode = MaxDeliveryplanDataCode.Substring(0, MaxDeliveryplanDataCode.Length - 2) + (Convert.ToInt32(MaxDeliveryplanDataCode.Substring(8, 2)) + 1).ToString("00"); } return strMaxDeliveryplanDataCode; } else if (pstrCodeType == "新增付款申请单") { //取付款申请单最大号格式PR+采购单号+流水号,默认是PR+采购单号+01 string MaxPR1_RequestCode = m_conn.GetDataTableFirstValue(@" SELECT MAX(PR1_RequestCode) FROM [B02_BILL].PR1_PaymentRequisit WHERE PR1_RequestCode LIKE 'PR" + pstrParam0 + "%' AND PR1_DIVI='" + pstrParam1 + "'").ToString(); if (MaxPR1_RequestCode == "") { MaxPR1_RequestCode = "PR" + pstrParam0 + "01"; } else { MaxPR1_RequestCode = MaxPR1_RequestCode.Substring(0, MaxPR1_RequestCode.Length - 2) + (Convert.ToInt32(MaxPR1_RequestCode.Substring(9, 2)) + 1).ToString("00"); } return MaxPR1_RequestCode; } //采购子表新增项次 else if (pstrCodeType == "新增项次") { int nMaxPO2_LISeq = 0;//默认是0 //取数据库,当前采购单子行,最大项次号,如果为空,默认放置1, string MaxPO2_LISeq = m_conn.GetDataTableFirstValue(@" SELECT MAX(PO2_LISeq) FROM [B02_BILL].[PO2_PODetail] WHERE PO2_POCode = '" + pstrParam0 + "' AND PO2_DIVI='" + pstrParam1 + "'").ToString(); if (MaxPO2_LISeq == "") { nMaxPO2_LISeq = 1; } else { nMaxPO2_LISeq = Convert.ToInt32(MaxPO2_LISeq) + 1; } return nMaxPO2_LISeq.ToString("0"); } else if (pstrCodeType == "新增付款单") { //取付款单最大号,规格是PY+采购单号+流水号,如果为空放默认值,PY+采购单号+流水号01开始 if (pstrParam1 == "") { pstrParam1 = "LX"; } string MaxPB1_PaymentBill = m_conn.GetDataTableFirstValue(@" SELECT MAX(PB1_PayBillCode) FROM [B02_BILL].[PB1_PaymentBill] WHERE PB1_PayBillCode LIKE 'PY" + pstrParam0 + "%' AND PB1_DIVI='" + pstrParam1 + "'").ToString(); if (MaxPB1_PaymentBill == "") { MaxPB1_PaymentBill = "PY" + pstrParam0 + "01"; } else { MaxPB1_PaymentBill = MaxPB1_PaymentBill.Substring(0, MaxPB1_PaymentBill.Length - 2) + (Convert.ToInt32(MaxPB1_PaymentBill.Substring(9, 2)) + 1).ToString("00"); } return MaxPB1_PaymentBill; } else if (pstrCodeType == "新增供应商") { if (pstrParam1 == "C")//如果是国内,取参数0、1、2 分类、币种、特征 为条件获取最大值 { string MaxSupplierCode = m_conn.GetDataTableFirstValue(@" SELECT MAX(SP1_SupplierCode) FROM [B01_MDM].[SP1_Supplier] WHERE SUBSTRING(SP1_SupplierCode,2,3) = '" + pstrParam0 + pstrParam1 + pstrParam2 + "'").ToString(); if (MaxSupplierCode == "") { MaxSupplierCode = pstrParam0 + pstrParam1 + pstrParam2 + "001"; } else { //如果存在,截取最后3位加1 MaxSupplierCode = MaxSupplierCode.Substring(1, MaxSupplierCode.Length - 4) + (Convert.ToInt32(MaxSupplierCode.Substring(4, 3)) + 1).ToString("000"); } return MaxSupplierCode; } else { //如果是国外,直接取供应商表A开头的供应商最大号加1 string MaxSupplierCode = m_conn.GetDataTableFirstValue(@" SELECT MAX(SP1_SupplierCode) FROM [B01_MDM].[SP1_Supplier] WHERE SUBSTRING(SP1_SupplierCode,2,3) = '" + pstrParam0 + pstrParam1 + pstrParam2 + "'").ToString(); if (MaxSupplierCode == "") { MaxSupplierCode = pstrParam0 + pstrParam1 + pstrParam2 + "001"; } else { int a = Convert.ToInt32(MaxSupplierCode.Substring(4, 3)) + 1; //取最大号加1 MaxSupplierCode = MaxSupplierCode.Substring(1, MaxSupplierCode.Length - 4) + (Convert.ToInt32(MaxSupplierCode.Substring(4, 3)) + 1).ToString("000"); } return MaxSupplierCode; } } else if (pstrCodeType == "新增品牌") { // string MaxBrandCode = m_conn.GetDataTableFirstValue(@" // SELECT MAX(BR1_BrandCode) // FROM [B01_MDM].[BR1_Brand]").ToString(); // if (MaxBrandCode == "") // { // MaxBrandCode = "Z001"; // } // else // { // MaxBrandCode = MaxBrandCode.Substring(0, MaxBrandCode.Length - 3) + (Convert.ToInt32(MaxBrandCode.Substring(1, 3)) + 1).ToString("000"); // } // return MaxBrandCode; int strMaxBrandCode = 10; for (int i = 10; i < 99; i++) { string strMaxCode = m_conn.GetDataTableFirstValue(@" SELECT * FROM [B01_MDM].[BR1_Brand] WHERE BR1_BrandCode = '" + i + "'").ToString(); if (strMaxCode == "") { strMaxBrandCode = i; break; } } return strMaxBrandCode.ToString(); } else if (pstrCodeType == "获取SKC最大号") { string strMaxSKCCode = m_conn.GetDataTableFirstValue(@" SELECT ISNULL(MAX(ST1_StyleCode),'') maxskc FROM [B01_MDM].[ST1_Style] WHERE ST1_StyleCode LIKE '" + pstrParam0 + "%' AND ST1_DIVI='" + pstrParam1 + "'").ToString(); if (strMaxSKCCode == "") { strMaxSKCCode = pstrParam0 + "0001"; } else { strMaxSKCCode = strMaxSKCCode.Substring(0, 5) + (Convert.ToInt32(strMaxSKCCode.Substring(5, 4)) + 1).ToString("0000"); // strMaxSKCCode = strMaxSKCCode.Substring(0, strMaxSKCCode.Length - 4) + (Convert.ToInt32(strMaxSKCCode.Substring(5, 4)) + 1).ToString("0000"); } return strMaxSKCCode; } else if (pstrCodeType == "获取试用品最大SKC号") { string strMaxSKCCode = m_conn.GetDataTableFirstValue(@" SELECT ISNULL(MAX(ST1_StyleCode),'') maxskc FROM [B01_MDM].[ST1_Style] WHERE ST1_StyleCode LIKE '" + pstrParam0 + "%' AND ST1_DIVI='" + pstrParam1 + "'").ToString(); if (strMaxSKCCode == "") { strMaxSKCCode = pstrParam0 + "001"; } else { strMaxSKCCode = strMaxSKCCode.Substring(0, 6) + (Convert.ToInt32(strMaxSKCCode.Substring(6, 3)) + 1).ToString("000"); } return strMaxSKCCode; } else if (pstrCodeType == "新增退仓单号") { //SR+两位年份+两位月份+两位日+两位流水码 DateTime day = DateTime.Now; string strYear = day.Year.ToString(); strYear = strYear.Substring(2, 2); string strMonth = day.Month.ToString(); if (strMonth.Length == 1) { strMonth = "0" + strMonth; }; string strDay = day.Day.ToString(); if (strDay.Length == 1) { strDay = "0" + strDay; } string strBill = "SR" + strYear + strMonth + strDay; string strMaxBillCode = m_conn.GetDataTableFirstValue(@" SELECT ISNULL(MAX(SR1_ShopRetreatBillCode),'') AS maxbill FROM [B02_BILL].[SR1_ShopRetreatHead] WHERE SR1_CONO = 'HYFG' AND SR1_DIVI = 'LX' AND SR1_ShopRetreatBillCode LIKE '%" + strBill + "%'").ToString(); if (strMaxBillCode == "") { strMaxBillCode = strBill + "01"; } else { string maxnum = strMaxBillCode.Substring(8); int num = Int32.Parse(maxnum); num++; maxnum = num.ToString(); if (maxnum.Length == 1) { maxnum = "0" + maxnum; }; strMaxBillCode = strBill + maxnum; } return strMaxBillCode; } return ""; } catch { throw; } }
/// <summary> /// 根据MDM加盟商信息来编辑Entity /// </summary> /// <returns></returns> public DataSet GetData() { Dbconn conn = new Dbconn("MDM_Master"); try { string strXML = m_request.Tables[0].Rows[0]["XML"].ToString(); string strStoreEntityCode = "0010"; string strMarketTableName = "Merchant_For_FM"; DataSet ds = Common.Common.GetDSByExcelXML(strXML); //判断Link是否需要修改 //获取加盟商的所有属性 MDM_Entity_Market_PropertyList clsPropertyList = new MDM_Entity_Market_PropertyList(); clsPropertyList.Request = Common.Common.GetRequestDataSet(new string[2] { "Entity", "MarketTableName" }, new string[2] { strStoreEntityCode, strMarketTableName }); DataTable dt_PropertyList = clsPropertyList.GetData().Tables[0]; //修改加盟商数据 string strUpdateXML = "<table Entity=\"" + strStoreEntityCode + "\" Expression=\"CN\">"; int nMaxCode = 0; for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++) { strUpdateXML += "<row>"; strUpdateXML += "<body_ID>" + ds.Tables["LIST"].Rows[i]["body_ID"].ToString() + "</body_ID>"; //新增 if (ds.Tables["LIST"].Rows[i]["body_ID"].ToString() == "") { //判断公司 string strComCode = GetComCode(ds.Tables["LIST"].Rows[i]["公司"].ToString()); if (strComCode == "") { throw new Exception("公司不正确。"); } string strCode = ""; string strMaxCode = ""; if (m_MaxCodeName.Contains(strComCode)) { strMaxCode = m_MaxCode[m_MaxCodeName.IndexOf(strComCode)].ToString(); } else { strMaxCode = conn.GetDataTableFirstValue(@" SELECT MAX(Value_Text) AS code FROM [dbo].[Lib_ExdPropertyList] t1 LEFT JOIN dbo.MDM_0010_E t2 ON t1.Property_ID = t2.Property_ID WHERE Class_name = '0010' AND property_name = '加盟商编码' AND Body_ID IN ( SELECT t2.Body_ID FROM [dbo].[Lib_ExdPropertyList] t1 LEFT JOIN dbo.MDM_0010_E t2 ON t1.Property_ID = t2.Property_ID WHERE Property_Name = '公司' AND t2.Value_Text = '" + ds.Tables["LIST"].Rows[i]["公司"].ToString() + @"' ) ").ToString(); } if (strMaxCode == "" || strMaxCode == strComCode + "0001") { strCode = strComCode + "1000"; } else { strCode = strComCode + (Convert.ToInt32(strMaxCode.Substring(1)) + 1).ToString(); } if (m_MaxCodeName.Contains(strComCode)) { m_MaxCode[m_MaxCodeName.IndexOf(strComCode)] = strCode; } else { m_MaxCode.Add(strCode); m_MaxCodeName.Add(strComCode); } ds.Tables["LIST"].Rows[i]["加盟商编码"] = strCode; } for (int j = 0; j < ds.Tables["LIST"].Columns.Count; j++) { string strColumnName = ds.Tables["LIST"].Columns[j].ColumnName; //如果修改的列是属于加盟商信息的话,要对加盟商的Entity进行修改 DataRow[] drList = dt_PropertyList.Select("Table_Property_Name = '" + strColumnName + "'"); if (drList.Length > 0) { string strPorpertyname = drList[0]["Porperty_Name"].ToString(); strUpdateXML += "<" + strPorpertyname + ">" + ds.Tables["LIST"].Rows[i][j].ToString() + "</" + strPorpertyname + ">"; } } strUpdateXML += "</row>"; } strUpdateXML += "</table>"; MDM_Entity_Edit cls_Entity_Edit = new MDM_Entity_Edit(); cls_Entity_Edit.Request = Common.Common.GetRequestDataSet(new string[1] { "XML" }, new string[1] { strUpdateXML }); cls_Entity_Edit.Execute(); return Common.Common.GetRequestDataSet(new string[1] { "result" }, new string[1] { "true" }); } catch { throw; } }