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;
}
}
