/// <summary>
/// 编辑系统属性信息
/// </summary>
/// <returns></returns>
public bool Execute()
{
Dbconn conn = new Dbconn("MDM");
try
{
string strProperty_ID = m_request.Tables[0].Rows[0]["Property_ID"].ToString();
m_request.Tables[0].Columns.Remove("Property_ID");
ArrayList listTable = new ArrayList();
listTable.Add("MDM_System_Property");
conn.BeginTransaction();
conn.TableLock(listTable);
if (strProperty_ID == "")
{
conn.Insert("MDM_System_Property", m_request.Tables[0]);
}
else
{
string strWhere = " Property_ID=" + strProperty_ID;
conn.Update("MDM_System_Property", m_request.Tables[0], strWhere);
}
conn.CommitTransaction();
return true;
}
catch
{
conn.RollbackTransaction();
throw;
}
}
/// <summary>
/// EnvoTemplates信息的CRUD
/// </summary>
/// <returns></returns>
public DataSet GetData()
{
DataSet ds_Return = new DataSet();
string strXML = m_request.Tables[0].Rows[0]["XML"].ToString();
if (strXML == "")
{
throw new Exception("XML不能为空");
}
string strTemplate_ID = m_request.Tables[0].Rows[0]["Template_ID"].ToString();
if (strTemplate_ID == "")
{
throw new Exception("Template_ID不能为空");
}
DataSet ds = Common.Common.GetDSByExcelXML(strXML);
DataTable dt_OPTYPE = ds.Tables["OPTYPE"];
Dbconn conn = new Dbconn("HZY_SDK");
if (dt_OPTYPE.Rows[0][0].ToString().ToUpper() == "INSERT")
{
bool bResult = true;
DataTable dt = new DataTable();
dt.Columns.Add("Template_ID");
dt.Rows.Add(new object[] { strTemplate_ID });
Commom_Ds.SetUpdateInfo(dt, hzyMessage);
conn.BeginTransaction();
bResult = conn.Insert("Log_ViewTemlpates", dt);
conn.ExcuteQuerryByTran("UPDATE EnvoTemplates set ViewCount=ViewCount+1 where Template_ID=" + strTemplate_ID);
conn.CommitTransaction();
if (bResult)
{
dt_EditResult.Rows.Add(new object[] { true, "修改成功" });
}
else
{
dt_EditResult.Rows.Add(new object[] { false, "修改失败" });
}
ds_Return.Tables.Add(dt_EditResult);
}
return ds_Return;
}
public DataSet GetData()
{
Dbconn conn = new Dbconn("WSRR");
string strXML = m_request.Tables[0].Rows[0]["XML"].ToString();
DataTable dt_EditResult = new dsCommon.dtResultDataTable();
DataSet dsRequest = new DataSet();
DataSet ds_Return = new DataSet();
dsRequest = Common.Common.GetDSByExcelXML(strXML);
DataTable dt_OPTYPE = dsRequest.Tables["OPTYPE"];
DataTable dt_LIST = dsRequest.Tables["LIST"];
for (int i = 0; i < dt_OPTYPE.Rows.Count; i++)
{
//获取用户权限信息
if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "GET")
{
string strUserName = dt_LIST.Rows[0]["UserName"].ToString();
string strSQL = @"
SELECT DISTINCT
T1.[AUZID]
,T1.[AUZName]
,0 AS Authorization_ID_F
, CASE WHEN T2.USERNAME IS NULL THEN '0'
ELSE '1' END HavaFlag
FROM [WSRR_Manage].[F01_CERT].[AUZ] T1 WITH ( NOLOCK )
LEFT JOIN [WSRR_Manage].[F01_CERT].[UserAUZ] T2 WITH ( NOLOCK ) ON T1.AUZID=T2.AUZID
AND T2.USERNAME=@param0";
ds_Return = conn.GetDataSet(strSQL, new string[] { strUserName });
}
//更新用户权限
else if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "UPDATE")
{
string strUserName = dt_LIST.Rows[0]["UserName"].ToString();
conn.LockTableList.Add("[WSRR_Manage].[F01_CERT].[UserAUZ]");//锁定表
conn.BeginTransaction();
//1.删除该用户的所有权限
string strWhere = " UserName="******"[WSRR_Manage].[F01_CERT].[UserAUZ]", strWhere);
//2.新增勾选的权限
bool insertFlag = conn.Insert("[WSRR_Manage].[F01_CERT].[UserAUZ]", dt_LIST);
conn.CommitTransaction();
//删除操作和插入操作都正确的情况下
if (deleteFlag && insertFlag)
{
dt_EditResult.Rows.Add(new object[] { true, strUserName + "权限更新成功!" });
}
else
{
dt_EditResult.Rows.Add(new object[] { true, strUserName + "权限更新失败!" });
}
ds_Return.Tables.Add(dt_EditResult);
}
}
return ds_Return;
}
/// <summary>
/// 获取系统环境信息
/// </summary>
/// <returns></returns>
public bool Execute()
{
Dbconn conn = new Dbconn("MDM");
try
{
string strEnv_ID = m_request.Tables[0].Rows[0]["Env_ID"].ToString();
string strEnv_SN = m_request.Tables[0].Rows[0]["Env_SN"].ToString();
m_request.Tables[0].Columns.Remove("Env_ID");
ArrayList listTable = new ArrayList();
listTable.Add("MDM_System_Env");
conn.BeginTransaction();
conn.TableLock(listTable);
//核对环境简称是否存在
string strSQL = "SELECT 1 AS a FROM MDM_System_Env where Env_SN='" + strEnv_SN + "'";
if(strEnv_ID != "")
{
strSQL += " AND Env_ID !=" + strEnv_ID;
}
DataTable dtTemp = conn.GetDataTableInTrans(strSQL);
if (dtTemp.Rows.Count > 0)
{
throw new Exception(strEnv_SN + "已存在！");
}
if (strEnv_ID == "")
{
conn.Insert("MDM_System_Env", m_request.Tables[0]);
}
else
{
string strWhere = " Env_ID=" + strEnv_ID;
conn.Update("MDM_System_Env", m_request.Tables[0], strWhere);
}
conn.CommitTransaction();
return true;
}
catch
{
conn.RollbackTransaction();
throw;
}
}
public DataSet GetData()
{
Dbconn conn = new Dbconn("WSRR");
string strXML = m_request.Tables[0].Rows[0]["XML"].ToString();
DataTable dt_EditResult = new dsCommon.dtResultDataTable();
DataSet dsRequest = new DataSet();
DataSet ds_Return = new DataSet();
dsRequest = Common.Common.GetDSByExcelXML(strXML);
DataTable dt_OPTYPE = dsRequest.Tables["OPTYPE"];
DataTable dt_LIST = dsRequest.Tables["LIST"];
for (int i = 0; i < dt_OPTYPE.Rows.Count; i++)
{
//获取用户权限信息
if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "GET")
{
string strSQL = @"
SELECT
[MenuID]
,[MenuName]
,[MenuFatherID]
,[ActionURL]
FROM [WSRR_Manage].[F01_CERT].[Menu]
";
ds_Return = conn.GetDataSet(strSQL);
//ds_Return.Tables[0].Rows.Add(new object[] { "0", "00_根目录", "-1", "-1" });
}
//更新
else if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "EDIT")
{
string strMenuID = dt_LIST.Rows[0]["MenuID"].ToString();
bool bResult = false;
conn.LockTableList.Add("[WSRR_Manage].[F01_CERT].[Menu]");//锁定表
conn.BeginTransaction();//开始事务
dt_LIST.Columns.Remove("MenuID");//移除属性，标识符列（ID）不能更新
bResult = conn.Update("[WSRR_Manage].[F01_CERT].[Menu]", dt_LIST, "MenuID=" + strMenuID);//update语句
conn.CommitTransaction();
if (bResult)
{
dt_EditResult.Rows.Add(new object[] { true, "修改成功" });
}
else
{
dt_EditResult.Rows.Add(new object[] { false, "修改失败" });
}
ds_Return.Tables.Add(dt_EditResult);
}
else if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "INSERT")
{
string strMenuID = dt_LIST.Rows[0]["MenuID"].ToString();
bool bResult = false;
conn.LockTableList.Add("[WSRR_Manage].[F01_CERT].[Menu]");//锁定表
conn.BeginTransaction();//开始事务
dt_LIST.Columns.Remove("MenuID");//移除属性，标识符列（ID）不能更新
bResult = conn.Insert("[WSRR_Manage].[F01_CERT].[Menu]", dt_LIST);//update语句
conn.CommitTransaction();
if (bResult)
{
dt_EditResult.Rows.Add(new object[] { true, "修改成功" });
}
else
{
dt_EditResult.Rows.Add(new object[] { false, "修改失败" });
}
ds_Return.Tables.Add(dt_EditResult);
}
else if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "DELETE")
{
string strMenuID = dt_LIST.Rows[0]["MenuID"].ToString();
bool bResult = false;
conn.LockTableList.Add("[WSRR_Manage].[F01_CERT].[Menu]");//锁定表
conn.BeginTransaction();//开始事务
dt_LIST.Columns.Remove("MenuID");//移除属性，标识符列（ID）不能更新
bResult = conn.Delete("[WSRR_Manage].[F01_CERT].[Menu]" , "MenuID=" + strMenuID);//update语句
conn.CommitTransaction();
if (bResult)
{
dt_EditResult.Rows.Add(new object[] { true, "修改成功" });
}
else
{
dt_EditResult.Rows.Add(new object[] { false, "修改失败" });
}
ds_Return.Tables.Add(dt_EditResult);
}
}
return ds_Return;
}
public bool Execute()
{
Dbconn conn = new Dbconn("iCollecter");
DataSet ds = conn.GetDataSet(
@" SELECT AMLoginName AS CN_LOGIN,TfgMachineNo,ComputerName
FROM ( SELECT ROW_NUMBER() OVER ( PARTITION BY TfgMachineNo ORDER BY collectDate DESC ) AS RowIndex ,
ComputerName ,
AMLoginName,
TfgMachineNo
FROM dbo.AMAndTFG WITH(NOLOCK)
WHERE 1 = 1
AND ISNULL(AMLoginUsers, '') <> ''
) t
WHERE RowIndex = 1
");
Dbconn connMDM = new Dbconn("MDM");
//获取用户ID
ds.Tables[0].Columns.Add("AM_User_id", typeof(Int32));
ds.Tables[0].AcceptChanges();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
string[] strParm = new string[1];
strParm[0]=ds.Tables[0].Rows[i]["CN_LOGIN"].ToString();
DataTable dt = connMDM.GetDataTable("SELECT AM_User_id FROM AM_User WHERE AM_login=@Param0", strParm);
if (dt.Rows.Count > 0)
{
ds.Tables[0].Rows[i]["AM_User_id"] = dt.Rows[0]["AM_User_id"];
}
else
{
ds.Tables[0].Rows[i].Delete();
}
}
ds.Tables[0].Columns.Remove("CN_LOGIN");
ds.Tables[0].AcceptChanges();
try
{
ArrayList tableList = new ArrayList();
connMDM.BeginTransaction();
tableList.Add("AM_TFG_Relation_log");
tableList.Add("AM_TFG_Relation");
connMDM.TableLock(tableList);
//存入日志数据
connMDM.ExcuteQuerryByTran(@"INSERT INTO dbo.AM_TFG_Relation_log
( Log_datetime ,
AM_User_id ,
TfgMachineNo ,
ComputerName ,
CreateTime ,
UpdateTime ,
Timestamps_old
)
SELECT GETDATE() ,
AM_User_id ,
TfgMachineNo ,
ComputerName ,
CreateTime ,
UpdateTime,
Convert(bigint,Timestamps)
FROM AM_TFG_Relation;");
//删除数据
connMDM.ExcuteQuerryByTran("DELETE FROM AM_TFG_Relation");
connMDM.Insert("AM_TFG_Relation", ds.Tables[0]);
connMDM.CommitTransaction();
return true;
}
catch
{
connMDM.RollbackTransaction();
throw;
}
}
/// <summary>
/// 进行登录信息写入
/// </summary>
/// <returns></returns>
private string DoLogin(string pIP, string pComputerName)
{
string strGUID = "";
Dbconn conn = new Dbconn("VexSSO");
DataTable dt_Guid = conn.GetDataTable("SELECT NEWID() AS GUID", new string[0]);
strGUID = dt_Guid.Rows[0][0].ToString();
DataTable dt = new System.Data.DataTable();
DateTime time = DateTime.Now;
dt.Columns.Add("SSO_Guid");
dt.Columns.Add("Company_sn");
dt.Columns.Add("Company_ID");
dt.Columns.Add("Env_sn");
dt.Columns.Add("Env_ID");
dt.Columns.Add("Login_Env_sn");
dt.Columns.Add("Login_Env_ID");
dt.Columns.Add("VexSSONewID");
dt.Columns.Add("User_ID");
dt.Columns.Add("User_Name");
dt.Columns.Add("User_Name_CN");
dt.Columns.Add("Request_IP");
dt.Columns.Add("Request_ComputerName");
dt.Columns.Add("Login_Time");
dt.Columns.Add("Last_Request_Time");
DataRow dr = dt.NewRow();
dr["SSO_Guid"] = strGUID;
dr["Company_sn"] = hzyMessage.Company_SN;
dr["Company_ID"] = hzyMessage.Company_ID;
dr["Env_sn"] = hzyMessage.Env_SN;
dr["Env_ID"] = hzyMessage.Env_ID;
dr["Login_Env_ID"] = hzyMessage.LoginEnv_ID;
dr["Login_Env_sn"] = hzyMessage.LoginEnv_SN;
dr["User_ID"] = hzyMessage.User_ID;
dr["User_Name"] = hzyMessage.User_Name;
dr["User_Name_CN"] = hzyMessage.User_Name_CN;
if (hzyMessage.VexSSONewID != "")
{
dr["VexSSONewID"] = hzyMessage.VexSSONewID;
}
dr["Request_IP"] = pIP;
dr["Request_ComputerName"] = pComputerName;
dr["Login_Time"] = time;
dr["Last_Request_Time"] = time;
dt.Rows.Add(dr);
conn.BeginTransaction();
try
{
conn.Insert("SSO_Login", dt);
conn.CommitTransaction();
}
catch (System.Exception ex)
{
conn.RollbackTransaction();
strGUID = "";
}
return strGUID;
}
private void Resend()
{
string strName = Request["Name"];
string strOldPhone = Request["oldphone"];
string strNewPhone = Request["newphone"];
string strcrdate = Request["crdate"].ToString();
string strsums = Request["sums"].ToString();
string strticketid = Request["ticketid"].ToString();
string strverifycode = Request["verifycode"].ToString();
string strVipID = Request["vipid"].ToString();
string strUid = Request["uid"].ToString();
if (string.IsNullOrEmpty(strOldPhone) ||
string.IsNullOrEmpty(strcrdate) ||
string.IsNullOrEmpty(strsums) ||
string.IsNullOrEmpty(strticketid) ||
string.IsNullOrEmpty(strVipID) ||
string.IsNullOrEmpty(strUid)
)
{
Response.Write("{'Message':'非法提交'}");
return;
}
else
{
if (strNewPhone == null)
{
strNewPhone = "";
}
string strPhone = strNewPhone;
if (strPhone == "")
{
strPhone = strOldPhone;
}
string strMessage = "尊敬的" + strName;
strMessage += "贵宾,您已通过EP雅莹店铺" + strcrdate.ToString() + "成功以";
strMessage += Convert.ToDecimal(strsums) * 25;
strMessage += "分兑换面额为" + strsums + "的券，券号" + strticketid;
strMessage += "，使用即使验证码：" + strverifycode;
strMessage += "。生活之美，EP雅莹愿与您一同缔造。";
AMSendWS.MsgCenter cls = new AMSendWS.MsgCenter();
string strResult = cls.SendSubjectMsgImmediately("SMS", "ep_actualtime", "积分兑换券验证码", strPhone, "信息部服务中心", strMessage, DateTime.Now.AddHours(-1).ToString("yyyy-MM-dd HH:mm:ss"), "1");
if (strResult == "0")
{
Response.Write("{'Message':'发送成功'}");
}
else
{
Response.Write("{'Message':'发送失败，原因：" + strResult.Remove('\n') + "'}");
}
//写日志
DataTable dt = new DataTable();
dt.Columns.Add("Account_Id");
dt.Columns.Add("Phone_Old");
dt.Columns.Add("Phone_New");
dt.Columns.Add("Ticket_Id");
dt.Columns.Add("Create_UserId");
dt.Columns.Add("Create_Time");
DataRow dr = dt.NewRow();
dr["Account_Id"] = strVipID;
dr["Phone_Old"] = strOldPhone;
dr["Phone_New"] = strNewPhone;
dr["Ticket_Id"] = strticketid;
dr["Create_UserId"] = strUid;
dr["Create_Time"] = DateTime.Now;
dt.Rows.Add(dr);
string strConnstr = ConfigurationManager.AppSettings["ConnectionString_CRMCUS"];
Dbconn conn = new Dbconn(strConnstr, true);
conn.BeginTransaction();
conn.Insert("Log_PhoneChange", dt);
conn.CommitTransaction();
return;
}
}
/// <summary>
/// 新建或修改固定资产类别记录
/// </summary>
/// <returns></returns>
public DataSet SetFATypes(Dbconn conn, String strType)
{
String strCode = "";
DataSet ds_Return = new DataSet();
string strActor = m_request.Tables[0].Rows[0]["actor"].ToString();
string strXML = m_request.Tables[0].Rows[0]["XML"].ToString();
DataSet dsXML = Common.Common.GetDSByExcelXML(strXML);
String strOPType = dsXML.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper();
String strID = dsXML.Tables["LIST"].Rows[0]["ct1_id"].ToString();
String strFatherID = dsXML.Tables["LIST"].Rows[0]["ct1_fatherid"].ToString();
DataTable dt_List = dsXML.Tables["LIST"];
if (strID == "")
{
throw new Exception("数据异常，缺少主键，请联系管理员！");
}
bool bResult = false;
if (strType == "NewFATypeDL") //新建大类
{
String strSQL1 = @" SELECT MAX(CT1_Code) AS code "
+ @" FROM M01_CONF.CT0_CSYTAB WHERE 1=1 "
+ " AND ISNULL(CONVERT(VARCHAR(50),CT1_FatherID),'') = '" + strFatherID
+ "'";
DataSet ds1 = conn.GetDataSet(strSQL1);
if (ds1.Tables == null)
{
dsXML.Tables["LIST"].Rows[0]["ct1_code"] = "01";
}
else
{
dsXML.Tables["LIST"].Rows[0]["ct1_code"] = int.Parse(ds1.Tables[0].Rows[0]["code"].ToString()) + 1;
if (int.Parse(ds1.Tables[0].Rows[0]["code"].ToString()) < 9)
{
dsXML.Tables["LIST"].Rows[0]["ct1_code"] = "0" + dsXML.Tables["LIST"].Rows[0]["ct1_code"].ToString();
}
}
conn.BeginTransaction();
bResult = conn.Insert("M01_CONF.CT0_CSYTAB", dt_List);
conn.CommitTransaction();
}
else if (strType == "NewFATypeXL") //新建小类
{
String strSQL1 = @" SELECT MAX(CT1_Code) AS code "
+ @" FROM M01_CONF.CT0_CSYTAB WHERE 1=1 "
+ " AND ISNULL(CONVERT(VARCHAR(50),CT1_FatherID),'') = '" + strFatherID
+ "'";
DataSet ds1 = conn.GetDataSet(strSQL1);
if (ds1.Tables == null)
{
return ds_Return;
}
else
{
if (ds1.Tables[0].Rows[0]["code"].ToString() == "")
{
String strSQL2 = @" SELECT CT1_Code "
+ @" FROM M01_CONF.CT0_CSYTAB WHERE 1=1 "
+ " AND ISNULL(CONVERT(VARCHAR(50),CT1_ID),'') = '" + strFatherID
+ "'";
DataSet ds2 = conn.GetDataSet(strSQL2);
dsXML.Tables["LIST"].Rows[0]["ct1_code"] = ds2.Tables[0].Rows[0]["CT1_Code"].ToString() + "001";
}
else
{
dsXML.Tables["LIST"].Rows[0]["ct1_code"] = int.Parse(ds1.Tables[0].Rows[0]["code"].ToString()) + 1;
if (int.Parse(ds1.Tables[0].Rows[0]["code"].ToString()) < 9999)
{
dsXML.Tables["LIST"].Rows[0]["ct1_code"] = "0" + dsXML.Tables["LIST"].Rows[0]["ct1_code"].ToString();
}
}
}
//dt_List.Columns.Add("CT1_RgUser");
//dt_List.Rows[0]["CT1_RgUser"] = strActor;
conn.BeginTransaction();
bResult = conn.Insert("M01_CONF.CT0_CSYTAB", dt_List);
conn.CommitTransaction();
}
else if (strType == "UpdFAType") //修改大小类
{
conn.BeginTransaction();
bResult = conn.Update("M01_CONF.CT0_CSYTAB", dt_List, "ct1_id='" + strID + "'");
conn.CommitTransaction();
}
strCode = dsXML.Tables["LIST"].Rows[0]["ct1_code"].ToString();
if (bResult)
{
dt_EditResult.Rows.Add(new object[] { true, strCode });
}
else
{
dt_EditResult.Rows.Add(new object[] { false, "保存失败！" });
}
ds_Return.Tables.Add(dt_EditResult);
return ds_Return;
}
/// <summary>
/// 根据XML内容更新到货计划
/// </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("GL_ERP");
string strXML = "";
string strSQL = "";
strXML = m_request.Tables[0].Rows[0]["XML"].ToString();
//XML是前台传入
ds = Common.Common.GetDSByExcelXML(strXML);
if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "GET")
//读取ROWS到/ROWS 中的参数
{
string strWHERE = "isnull(PRA_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'";
if (ds.Tables["LIST"].Columns.Contains("Condition"))
{
if (ds.Tables["LIST"].Rows[0]["Condition"].ToString().Trim() != "")
//读取LIST之间参数
{
strWHERE += " AND (" + ds.Tables["LIST"].Rows[0]["Condition"].ToString().Trim() + ")";
}
}
strSQL = @" SELECT
[PRA_CONO]
,[PRA_DIVI]
,[PRA_ArrivalPlanId]
,[PRA_ArrivalPlanCode]
,[PRA_PR1_OrderId]
,[PRA_PerchaseTotal]
,[PRA_ArrivalTotal]
,[PRA_PlanTotal]
,[PRA_PerchaseAmount]
,[PRA_ArrivalAmount]
,[PRA_PlanAmount]
,[PRA_TotalDiff]
,[PRA_AmountDiff]
,[PRA_Status]
,[PRA_RgUser]
,[PRA_RgDt]
,[PRA_LmUser]
,[PRA_LmDt]
,[PRA_UptNo]
,A.PR1_OrderCode
FROM B02_Bill.PRA_ArrivalPlan
LEFT JOIN B02_Bill.PR1_Order A ON PRA_PR1_OrderId=PR1_OrderId
WHERE " + strWHERE;
ds_Return = conn.GetDataSet(strSQL);
}
else
{
ArrayList tableList = new ArrayList();
tableList.Add("B02_Bill.PRA_ArrivalPlan");
conn.BeginTransaction();
//开启事物
conn.TableLock(tableList);
//锁表
try
{
if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "DELETE")
{
for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++)
{
strSQL = @"UPDATE B02_Bill.PRA_ArrivalPlan SET PRA_Status='E3CA9B7A-8E17-4E52-B667-39689445D4A1'
WHERE PRA_ArrivalPlanId='" + ds.Tables["LIST"].Rows[i]["PRA_ArrivalPlanId"].ToString() + @"'
AND isnull(PRA_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'
";
conn.ExcuteQuerryByTran(strSQL);
}
}
else if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "EDIT")
{
for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++)
{
DataTable dt = ds.Tables["LIST"].Clone();
dt.ImportRow(ds.Tables["LIST"].Rows[i]);
strSQL = @"SELECT * FROM B02_Bill.PRA_ArrivalPlan WHERE PRA_ArrivalPlanId=@param0
AND isnull(PRA_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'
";
DataTable dt_SR = conn.GetDataTable(strSQL, new string[1] { ds.Tables["LIST"].Rows[i]["PRA_ArrivalPlanId"].ToString() });
//update
if (dt_SR.Rows.Count > 0)
{
//处理结果
if (ds.Tables["LIST"].Rows[i]["PRA_LmUser"].ToString() != "")
{
string strCode = dt_SR.Rows[0]["PRA_UptNo"].ToString();
if (strCode == "0")
{
strCode = Convert.ToInt32(strCode) + 1.ToString();
}
else
{
strCode = "0";
}
if (!dt.Columns.Contains("PRA_LmDt"))
{
dt.Columns.Add("PRA_LmDt");
}
if (!dt.Columns.Contains("PRA_UptNo"))
{
dt.Columns.Add("PRA_UptNo");
}
// if (!dt.Columns.Contains("PRA_Status"))
// {
// dt.Columns.Add("PRA_Status");
// }
// dt.Rows[0]["PRA_Status"] = '0';
dt.Rows[0]["PRA_LmDt"] = DateTime.Now;
dt.Rows[0]["PRA_UptNo"] = strCode;
}
conn.Update("B02_Bill.PRA_ArrivalPlan", dt, "PRA_ArrivalPlanId='" + ds.Tables["LIST"].Rows[i]["PRA_ArrivalPlanId"].ToString() + @"'");
}
else //add
{
string PRA_ArrivalPlanId = Guid.NewGuid().ToString();
if (!dt.Columns.Contains("PRA_ArrivalPlanId"))
{
dt.Columns.Add("PRA_ArrivalPlanId");
}
// if (!dt.Columns.Contains("PRA_Status"))
// {
// dt.Columns.Add("PRA_Status");
// }
if (!dt.Columns.Contains("PRA_RgDt"))
{
dt.Columns.Add("PRA_RgDt");
}
if (!dt.Columns.Contains("PRA_LmDt"))
{
dt.Columns.Add("PRA_LmDt");
}
if (!dt.Columns.Contains("PRA_UptNo"))
{
dt.Columns.Add("PRA_UptNo");
}
dt.Rows[0]["PRA_ArrivalPlanId"] = PRA_ArrivalPlanId;
dt.Rows[0]["PRA_RgDt"] = DateTime.Now;
dt.Rows[0]["PRA_LmDt"] = DateTime.Now;
dt.Rows[0]["PRA_UptNo"] = '0';
// dt.Rows[0]["PRA_Status"] = '0';
conn.Insert("B02_Bill.PRA_ArrivalPlan", dt);
}
}
}
conn.CommitTransaction();
ds_Return = Common.Common.GetRequestDataSet(new string[] { "Result" }, new string[] { "true" });
}
catch
{
conn.RollbackTransaction();
//事物结束
throw;
}
}
return ds_Return;
}
catch
{
throw;
}
}
/// <summary>
/// EnvoTemplates信息的CRUD
/// </summary>
/// <returns></returns>
public DataSet GetData()
{
DataSet ds_Return = new DataSet();
string strXML = m_request.Tables[0].Rows[0]["XML"].ToString();
if (strXML == "")
{
throw new Exception("XML不能为空");
}
string strEnvo_ID = m_request.Tables[0].Rows[0]["Envo_ID"].ToString();
DataSet ds = Common.Common.GetDSByExcelXML(strXML);
DataTable dt_OPTYPE = ds.Tables["OPTYPE"];
Dbconn conn = new Dbconn("HZY_SDK");
if (dt_OPTYPE.Rows[0][0].ToString().ToUpper() == "GET")
{
if (strEnvo_ID == "")
{
throw new Exception("Envo_ID不能为空");
}
ds_Return = conn.GetDataSet(@"
SELECT
Template_ID ,
Envo_ID ,
Template_Name ,
--replace(replace(REPLACE([Text],CHAR(9),' '),char(13),'ㄧ'),char(10),'ㄧ') Text ,
Text ,
ViewCount ,
UpdateTime ,
UpdateUser
FROM dbo.EnvoTemplates
WHERE Envo_ID=@param0
ORDER BY ViewCount DESC
", new string[] { strEnvo_ID });
}
else if (dt_OPTYPE.Rows[0][0].ToString().ToUpper() == "UPDATE")
{
bool bResult = true;
if (ds.Tables["list"].Rows.Count > 0)
{
string strTemplate_ID = ds.Tables["list"].Rows[0]["Template_ID"].ToString();
if (strTemplate_ID == "")
{
throw new Exception("Template_ID不能为空");
}
ds.Tables["list"].Columns.Remove("Template_ID");
Commom_Ds.SetUpdateInfo(ds.Tables["list"], hzyMessage);
conn.BeginTransaction();
if (CheckName(ds.Tables["list"], conn) == false)
{
conn.RollbackTransaction();
throw new Exception("模板名称已经存在");
}
bResult = conn.Update("EnvoTemplates", ds.Tables["list"], "Template_ID='" + strTemplate_ID + "'");
conn.CommitTransaction();
}
if (bResult)
{
dt_EditResult.Rows.Add(new object[] { true, "修改成功" });
}
else
{
dt_EditResult.Rows.Add(new object[] { false, "修改失败" });
}
ds_Return.Tables.Add(dt_EditResult);
}
else if (dt_OPTYPE.Rows[0][0].ToString().ToUpper() == "INSERT")
{
bool bResult = true;
if (ds.Tables["list"].Rows.Count > 0)
{
ds.Tables["list"].Columns.Remove("Template_ID");
Commom_Ds.SetUpdateInfo(ds.Tables["list"], hzyMessage);
conn.BeginTransaction();
if (CheckName(ds.Tables["list"], conn) == false)
{
conn.RollbackTransaction();
throw new Exception("模板名称已经存在");
}
bResult = conn.Insert("EnvoTemplates", ds.Tables["list"]);
conn.CommitTransaction();
}
if (bResult)
{
dt_EditResult.Rows.Add(new object[] { true, "修改成功" });
}
else
{
dt_EditResult.Rows.Add(new object[] { false, "修改失败" });
}
ds_Return.Tables.Add(dt_EditResult);
}
else if (dt_OPTYPE.Rows[0][0].ToString().ToUpper() == "DELETE")
{
bool bResult = true;
if (ds.Tables["list"].Rows.Count > 0)
{
string strTemplate_ID = ds.Tables["list"].Rows[0]["Template_ID"].ToString();
if (strTemplate_ID == "")
{
throw new Exception("Template_ID不能为空");
}
conn.BeginTransaction();
bResult = conn.Delete("EnvoTemplates", "Template_ID='" + strTemplate_ID + "'");
conn.CommitTransaction();
}
if (bResult)
{
dt_EditResult.Rows.Add(new object[] { true, "修改成功" });
}
else
{
dt_EditResult.Rows.Add(new object[] { false, "修改失败" });
}
ds_Return.Tables.Add(dt_EditResult);
}
return ds_Return;
}
/// <summary>
/// 编辑任务参数信息
/// </summary>
/// <returns></returns>
public bool Execute()
{
Dbconn conn = new Dbconn("MDM"); //创建连接类
try
{
if (m_request == null
|| m_request.Tables.Count == 0
|| m_request.Tables[0].Rows.Count == 0)
{
return true;
}
string strTASK_ID = m_request.Tables[0].Rows[0]["task_id"].ToString(); //获取参数Task_ID
string strParam_Name = m_request.Tables[0].Rows[0]["param_name"].ToString(); //获取参数Param_Name
string strParam_ID = m_request.Tables[0].Rows[0]["param_id"].ToString(); //获取参数Param_ID
ArrayList listTable = new ArrayList();
listTable.Add("WS_SchedulingParam");
conn.BeginTransaction(); //开启事务
conn.TableLock(listTable); //锁表WS_SchedulingParam
string strWhere = " 1=1 ";
if (!string.IsNullOrEmpty(strTASK_ID))
{
strWhere += " AND TASK_ID = '" + strTASK_ID + "'";
}
if (!string.IsNullOrEmpty(strParam_Name))
{
strWhere += " AND Param_Name = '" + strParam_Name + "'";
}
//创建DataTable
DataTable dt_Add = new DataTable();
dt_Add.Columns.Add("Param_ID");
dt_Add.Columns.Add("TASK_ID");
dt_Add.Columns.Add("Param_Name");
dt_Add.Columns.Add("Param_Value");
DataRow dr = dt_Add.NewRow();
for (int i = 0; i < dt_Add.Columns.Count; i++)
{
dr[i] = m_request.Tables[0].Rows[0][dt_Add.Columns[i].ColumnName].ToString(); //将m_request中的数据逐个赋值
}
dt_Add.Rows.Add(dr);
if (strParam_ID == "") //如果为空，说明是复制数据
{
//判断是否已经存在
int nRow = conn.GetDataTableRowCount("SELECT * FROM WS_SchedulingParam WHERE " + strWhere, new string[0]);
if (nRow > 0)
{
throw new Exception(strParam_Name + "已经存在，不能更新");
}
strParam_ID = Guid.NewGuid().ToString(); //创建GUID
dt_Add.Rows[0]["Param_ID"] = strParam_ID;
conn.Insert("WS_SchedulingParam", dt_Add); //插入数据
}
else
{
dt_Add.Columns.Remove("Param_ID"); //删除Param_ID，更新不需要
dt_Add.Columns.Remove("Task_ID"); //删除Task_ID，更新不需要
strWhere = " 1=1 AND Param_ID = '" + strParam_ID + "'"; //重新设置where语句
conn.Update("WS_SchedulingParam", dt_Add, strWhere); //更新数据
}
//更新主表
DataTable dtScheduling = new DataTable();
dtScheduling.Columns.Add("UpdateTime");
DataRow drScheduling = dtScheduling.NewRow();
drScheduling["UpdateTime"] = DateTime.Now;
dtScheduling.Rows.Add(drScheduling);
string strSchedulingWhere=" 1=1 AND TASK_ID = '" + strTASK_ID + "'";
conn.Update("WS_Scheduling", dtScheduling, strSchedulingWhere); //更新数据
conn.CommitTransaction();
return true;
}
catch
{
conn.RollbackTransaction();
throw;
}
}
/// <summary>
/// 根据XML内容更新服务器申请单
/// </summary>
/// <returns></returns>
public DataSet GetData()
{
try
{
DataSet ds_Return = new DataSet();
DataSet ds = new DataSet();
DataSet dsRequest = new DataSet();
Dbconn conn = new Dbconn("HZY_ExcelAppStore");
string strXML = "";
string strSQL = "";
strXML = m_request.Tables[0].Rows[0]["XML"].ToString();
ds = Common.Common.GetDSByExcelXML(strXML);
if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "GET")
{
string strWHERE = " Deleted=0 ";
if (ds.Tables["LIST"].Columns.Contains("Condition"))
{
if (ds.Tables["LIST"].Rows[0]["Condition"].ToString().Trim() != "")
{
strWHERE += " AND (" + ds.Tables["LIST"].Rows[0]["Condition"].ToString().Trim() + ")";
}
}
strSQL = @" SELECT *
FROM ExcelAppStore WITH ( NOLOCK )
WHERE " + strWHERE;
ds_Return = conn.GetDataSet(strSQL);
}
else
{
ArrayList tableList = new ArrayList();
tableList.Add("ExcelAppStore");
conn.BeginTransaction();
conn.TableLock(tableList);
try
{
if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "DELETE")
{
for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++)
{
strSQL = @"UPDATE ExcelAppStore SET Deleted=1
WHERE AppName='" + ds.Tables["LIST"].Rows[i]["AppName"].ToString() + @"'
AND Deleted=0 ";
conn.ExcuteQuerryByTran(strSQL);
}
}
else if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "EDIT")
{
for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++)
{
DataTable dt = ds.Tables["LIST"].Clone();
dt.ImportRow(ds.Tables["LIST"].Rows[i]);
strSQL = @"SELECT * FROM ExcelAppStore WHERE AppName=@param0
AND Deleted=0 ";
DataTable dt_SR = conn.GetDataTable(strSQL, new string[1] { ds.Tables["LIST"].Rows[i]["AppName"].ToString() });
//update
if (dt_SR.Rows.Count > 0)
{
//运维操作
if (ds.Tables["LIST"].Rows[i]["AppName"].ToString() != "")
{
conn.Update("ExcelAppStore", dt, "AppName='" + ds.Tables["LIST"].Rows[i]["AppName"].ToString() + @"'");
}
}
else //add
{
conn.Insert("ExcelAppStore", dt);
}
}
}
conn.CommitTransaction();
ds_Return = Common.Common.GetRequestDataSet(new string[]{"Result"},new string[]{"true"});
}
catch
{
conn.RollbackTransaction();
throw;
}
}
return ds_Return;
}
catch
{
throw;
}
}
/// <summary>
/// 新建或修改鉴定单记录
/// </summary>
/// <returns></returns>
public DataSet SetITAppraisal(Dbconn conn, String strType)
{
DataSet ds_Return = new DataSet();
string strActor = m_request.Tables[0].Rows[0]["actor"].ToString();
string strXML = m_request.Tables[0].Rows[0]["XML"].ToString();
//获取申购单主表数据
DataSet dsXML = Common.Common.GetDSByExcelXML(strXML);
String strOPType = dsXML.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper();
String strID = dsXML.Tables["LIST"].Rows[0]["fa3_id"].ToString();
DataTable dt_List = dsXML.Tables["LIST"];
if (strID == "")
{
throw new Exception("数据异常，缺少主键，请联系管理员！");
}
bool bResult = false;
if (strType == "New") //新建单据
{
conn.BeginTransaction();
bResult = conn.Insert("[B02_BILL].[FA3_ITAppraisal]", dt_List);
conn.CommitTransaction();
}
else if (strType == "Upd") //修改单据
{
conn.BeginTransaction();
bResult = conn.Update("[B02_BILL].[FA3_ITAppraisal]", dt_List, "fa3_id='" + strID+"'");
conn.CommitTransaction();
}
if (bResult)
{
dt_EditResult.Rows.Add(new object[] { true, "保存成功！" });
}
else
{
dt_EditResult.Rows.Add(new object[] { false, "保存失败！" });
}
ds_Return.Tables.Add(dt_EditResult);
return ds_Return;
}
/// <summary>
/// 根据XML内容更新付款申请单
/// </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("GL_ERP");
string strXML = "";
string strSQL = "";
strXML = m_request.Tables[0].Rows[0]["XML"].ToString();
//XML是前台传入
ds = Common.Common.GetDSByExcelXML(strXML);
if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "GET")
//读取ROWS到/ROWS 中的参数
{
string strWHERE = "ISNULL(FNP_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'";
if (ds.Tables["LIST"].Columns.Contains("Condition"))
{
if (ds.Tables["LIST"].Rows[0]["Condition"].ToString().Trim() != "")
//读取LIST之间参数
{
strWHERE += " AND (" + ds.Tables["LIST"].Rows[0]["Condition"].ToString().Trim() + ")";
}
}
strSQL = @"SELECT
[FNP_CONO]
,[FNP_DIVI]
,[FNP_PayApplyId]
,[FNP_PayApplyCode]
,[FNP_PR0_ContractId]
,[FNP_SU0_SupplierId]
,[FNP_Type]
,[FNP_BankReceipt]
,[FNP_Status]
,[FNP_RgUser]
,[FNP_RgDt]
,[FNP_LmUser]
,[FNP_LmDt]
,[FNP_UptNo]
,A.PR0_ContractCode
,B.SU0_SupplierCode
,C.DC0_DICTNAME1 AS TypeDictName
,D.WFT_NODE AS WFT_NODE1
,E.WFT_NODE AS WFT_NODE2
,F.WFT_NODE AS WFT_NODE3
,G.WFT_NODE AS WFT_NODE4
,N.DC0_DICTNAME1 AS DC0_DICTNAME1
,O.DC0_DICTNAME1 AS DC0_DICTNAME2
,P.DC0_DICTNAME1 AS DC0_DICTNAME3
,Q.DC0_DICTNAME1 AS DC0_DICTNAME4
,I.WFP_AuditTime AS WFP_AuditTime1
,J.WFP_AuditTime AS WFP_AuditTime2
,K.WFP_AuditTime AS WFP_AuditTime3
,L.WFP_AuditTime AS WFP_AuditTime4
,R.[DC0_DictName1] AS StatusDictName
FROM B02_Bill.FNP_PayApply
LEFT JOIN B02_Bill.PR0_Contract A ON FNP_PR0_ContractId=A.PR0_ContractId
LEFT JOIN B01_MDM.SU0_Supplier B ON FNP_SU0_SupplierId=B.SU0_SupplierId
LEFT JOIN M01_CONF.DC0_Dictionary C ON FNP_Type=C.DC0_DictId
LEFT JOIN M02_BPM.WFT_AuditFlowDefine D ON D.WFT_Type='PayApply' and D.WFT_Step='1'
LEFT JOIN M02_BPM.WFT_AuditFlowDefine E ON E.WFT_Type='PayApply' and E.WFT_Step='2'
LEFT JOIN M02_BPM.WFT_AuditFlowDefine F ON F.WFT_Type='PayApply' and F.WFT_Step='3'
LEFT JOIN M02_BPM.WFT_AuditFlowDefine G ON G.WFT_Type='PayApply' and G.WFT_Step='4'
LEFT JOIN M02_BPM.WFP_AuditFlow I ON FNP_PayApplyId=I.WFP_BillId AND I.WFP_WFT_AFDId=D.WFT_AFDId
LEFT JOIN M02_BPM.WFP_AuditFlow J ON FNP_PayApplyId=J.WFP_BillId AND J.WFP_WFT_AFDId=E.WFT_AFDId
LEFT JOIN M02_BPM.WFP_AuditFlow K ON FNP_PayApplyId=K.WFP_BillId AND K.WFP_WFT_AFDId=F.WFT_AFDId
LEFT JOIN M02_BPM.WFP_AuditFlow L ON FNP_PayApplyId=L.WFP_BillId AND L.WFP_WFT_AFDId=G.WFT_AFDId
LEFT JOIN M01_CONF.DC0_Dictionary N ON I.WFP_AFResultId=N.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary O ON J.WFP_AFResultId=O.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary P ON K.WFP_AFResultId=P.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary Q ON L.WFP_AFResultId=Q.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary R ON FNP_Status=R.DC0_DictId
WHERE " + strWHERE;
ds_Return = conn.GetDataSet(strSQL);
}
else
{
ArrayList tableList = new ArrayList();
tableList.Add("B02_Bill.FNP_PayApply");
conn.BeginTransaction();
//开启事物
conn.TableLock(tableList);
//锁表
try
{
if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "DELETE")
{
for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++)
{
strSQL = @"UPDATE B02_Bill.FNP_PayApply SET FNP_Status='E3CA9B7A-8E17-4E52-B667-39689445D4A1'
WHERE FNP_PayApplyId='" + ds.Tables["LIST"].Rows[i]["FNP_PayApplyId"].ToString() + @"'
AND ISNULL(FNP_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1' ";
conn.ExcuteQuerryByTran(strSQL);
}
}
else if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "EDIT")
{
for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++)
{
DataTable dt = ds.Tables["LIST"].Clone();
dt.ImportRow(ds.Tables["LIST"].Rows[i]);
strSQL = @"SELECT * FROM B02_Bill.FNP_PayApply WHERE FNP_PayApplyId=@param0
AND ISNULL(FNP_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1' ";
DataTable dt_SR = conn.GetDataTable(strSQL, new string[1] { ds.Tables["LIST"].Rows[i]["FNP_PayApplyId"].ToString() });
//update
if (dt_SR.Rows.Count > 0)
{
//处理结果
strSQL = @"SELECT * FROM M02_BPM.WFP_AuditFlow WHERE WFP_BillId=@param0
AND WFP_AFResultId<>'' AND ISNULL(WFP_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'
";
DataTable dt_PA = conn.GetDataTable(strSQL, new string[1] { ds.Tables["LIST"].Rows[i]["FNP_PayApplyId"].ToString() });
//update
if (dt_PA.Rows.Count > 0)
{
throw new Exception("付款申请单已审核，不能进行修改！");
}
else
if (ds.Tables["LIST"].Rows[i]["FNP_LmUser"].ToString() != "")
{
string strCode = dt_SR.Rows[0]["FNP_UptNo"].ToString();
if (strCode == "0")
{
strCode = Convert.ToInt32(strCode) + 1.ToString();
}
else
{
strCode = "0";
}
if (!dt.Columns.Contains("FNP_LmDt"))
{
dt.Columns.Add("FNP_LmDt");
}
// if (!dt.Columns.Contains("FNP_Status"))
// {
// dt.Columns.Add("FNP_Status");
// }
if (!dt.Columns.Contains("FNP_UptNo"))
{
dt.Columns.Add("FNP_UptNo");
}
// dt.Rows[0]["FNP_Status"] = '0';
dt.Rows[0]["FNP_LmDt"] = DateTime.Now;
dt.Rows[0]["FNP_UptNo"] = strCode;
}
conn.Update("B02_Bill.FNP_PayApply", dt, "FNP_PayApplyId='" + ds.Tables["LIST"].Rows[i]["FNP_PayApplyId"].ToString() + @"'");
}
else //add
{
string FNP_PayApplyId = Guid.NewGuid().ToString();
if (!dt.Columns.Contains("FNP_PayApplyId"))
{
dt.Columns.Add("FNP_PayApplyId");
}
// if (!dt.Columns.Contains("FNP_Status"))
// {
// dt.Columns.Add("FNP_Status");
// }
if (!dt.Columns.Contains("FNP_RgDt"))
{
dt.Columns.Add("FNP_RgDt");
}
if (!dt.Columns.Contains("FNP_LmDt"))
{
dt.Columns.Add("FNP_LmDt");
}
if (!dt.Columns.Contains("FNP_UptNo"))
{
dt.Columns.Add("FNP_UptNo");
}
dt.Rows[0]["FNP_PayApplyId"] = FNP_PayApplyId;
// dt.Rows[0]["FNP_Status"] = '0';
dt.Rows[0]["FNP_RgDt"] = DateTime.Now;
dt.Rows[0]["FNP_LmDt"] = DateTime.Now;
dt.Rows[0]["FNP_UptNo"] = '0';
conn.Insert("B02_Bill.FNP_PayApply", dt);
GL_ERP_Common.CreateWorkFlow(conn, "PayApply", ds.Tables["LIST"].Rows[i]["FNP_CONO"].ToString(), ds.Tables["LIST"].Rows[i]["FNP_DIVI"].ToString(), FNP_PayApplyId,
"PayApply", "", "", ds.Tables["LIST"].Rows[i]["FNP_Status"].ToString(), ds.Tables["LIST"].Rows[i]["FNP_RgUser"].ToString(),
ds.Tables["LIST"].Rows[i]["FNP_LmUser"].ToString(), "", "FNP_PayApplyId", "B02_Bill.FNP_PayApply");
}
}
}
conn.CommitTransaction();
ds_Return = Common.Common.GetRequestDataSet(new string[] { "Result" }, new string[] { "true" });
}
catch
{
conn.RollbackTransaction();
//事物结束
throw;
}
}
return ds_Return;
}
catch
{
throw;
}
}
private void UpdateCMData(Dbconn conn, DataTable dt_CMData)
{
//更新旧数据
if (m_OldAutoId != "")
{
DataTable dt = new DataTable();
dt.Columns.Add("IsHistory");
dt.Rows.Add(new object[] { "1" });
conn.Update("CM_Depot_Opration_History", dt, "autoid =" + m_OldAutoId);
}
//插入新数据
conn.Insert("CM_Depot_Opration_History", dt_CMData);
}
/// <summary>
/// 根据XML内容更新审批流模版
/// </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("GL_ERP");
string strXML = "";
string strSQL = "";
strXML = m_request.Tables[0].Rows[0]["XML"].ToString();
//XML是前台传入
ds = Common.Common.GetDSByExcelXML(strXML);
if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "GET")
//读取ROWS到/ROWS 中的参数
{
string strWHERE = "1=1";
if (ds.Tables["LIST"].Columns.Contains("Condition"))
{
if (ds.Tables["LIST"].Rows[0]["Condition"].ToString().Trim() != "")
//读取LIST之间参数
{
strWHERE += " AND (" + ds.Tables["LIST"].Rows[0]["Condition"].ToString().Trim() + ")";
}
}
strSQL = @" SELECT
[WFT_CONO]
,[WFT_DIVI]
,[WFT_AFDId]
,[WFT_Type]
,[WFT_Node]
,[WFT_Person]
,[WFT_Step]
,[WFT_IsMust]
,[WFT_RgUser]
,[WFT_RgDt]
,[WFT_LmUser]
,[WFT_LmDt]
,[WFT_UptNo]
FROM GL_ERP.M02_BPM.WFT_AuditFlowDefine WITH ( NOLOCK )
WHERE " + strWHERE;
ds_Return = conn.GetDataSet(strSQL);
}
else
{
ArrayList tableList = new ArrayList();
tableList.Add("GL_ERP.M02_BPM.WFT_AuditFlowDefine");
conn.BeginTransaction();
//开启事物
conn.TableLock(tableList);
//锁表
try
{
if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "DELETE")
{
for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++)
{
strSQL = @"DELETE FROM GL_ERP.M02_BPM.WFT_AuditFlowDefine
WHERE WFT_AFDId='" + ds.Tables["LIST"].Rows[i]["WFT_AFDId"].ToString() + @"'
";
conn.ExcuteQuerryByTran(strSQL);
}
}
else if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "EDIT")
{
for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++)
{
DataTable dt = ds.Tables["LIST"].Clone();
dt.ImportRow(ds.Tables["LIST"].Rows[i]);
strSQL = @"SELECT * FROM GL_ERP.M02_BPM.WFT_AuditFlowDefine WHERE WFT_AFDId=@param0
";
DataTable dt_SR = conn.GetDataTable(strSQL, new string[1] { ds.Tables["LIST"].Rows[i]["WFT_AFDId"].ToString() });
//update
if (dt_SR.Rows.Count > 0)
{
//处理结果
if (ds.Tables["LIST"].Rows[i]["WFT_LmUser"].ToString() != "")
{
string strCode = dt_SR.Rows[0]["WFT_UptNo"].ToString();
if (strCode == "0")
{
strCode = Convert.ToInt32(strCode) + 1.ToString();
}
else
{
strCode = "0";
}
if (!dt.Columns.Contains("WFT_LmDt"))
{
dt.Columns.Add("WFT_LmDt");
}
if (!dt.Columns.Contains("WFT_UptNo"))
{
dt.Columns.Add("WFT_UptNo");
}
dt.Rows[0]["WFT_LmDt"] = DateTime.Now;
dt.Rows[0]["WFT_UptNo"] = strCode;
}
conn.Update("GL_ERP.M02_BPM.WFT_AuditFlowDefine", dt, "WFT_AFDId='" + ds.Tables["LIST"].Rows[i]["WFT_AFDId"].ToString() + @"'");
}
else //add
{
string WFT_AFDId = Guid.NewGuid().ToString();
if (!dt.Columns.Contains("WFT_AFDId"))
{
dt.Columns.Add("WFT_AFDId");
}
if (!dt.Columns.Contains("WFT_RgDt"))
{
dt.Columns.Add("WFT_RgDt");
}
if (!dt.Columns.Contains("WFT_LmDt"))
{
dt.Columns.Add("WFT_LmDt");
}
if (!dt.Columns.Contains("WFT_UptNo"))
{
dt.Columns.Add("WFT_UptNo");
}
dt.Rows[0]["WFT_AFDId"] = WFT_AFDId;
dt.Rows[0]["WFT_RgDt"] = DateTime.Now;
dt.Rows[0]["WFT_LmDt"] = DateTime.Now;
dt.Rows[0]["WFT_UptNo"] = '0';
conn.Insert("GL_ERP.M02_BPM.WFT_AuditFlowDefine", dt);
}
}
}
conn.CommitTransaction();
ds_Return = Common.Common.GetRequestDataSet(new string[] { "Result" }, new string[] { "true" });
}
catch
{
conn.RollbackTransaction();
//事物结束
throw;
}
}
return ds_Return;
}
catch
{
throw;
}
}
/// <summary>
/// 获取MDM应用的数据
/// </summary>
/// <returns></returns>
public bool Execute()
{
DateTime dateStart = DateTime.Now;
DateTime dateEnd = DateTime.Now;
if (m_request != null && m_request.Tables.Count >0 && m_request.Tables[0].Rows.Count == 1)
{
if (m_request.Tables[0].Columns.Contains("dateStart"))
{
dateStart = Convert.ToDateTime(m_request.Tables[0].Rows[0]["dateStart"]).AddDays(-1);
}
if (m_request.Tables[0].Columns.Contains("dateEnd"))
{
dateEnd = Convert.ToDateTime(m_request.Tables[0].Rows[0]["dateEnd"]).AddDays(-1);
}
}
DateTime currentDate = dateStart;
Dbconn conn = new Dbconn("BI_CustomerFlow");
DataTable dtLog = new DataTable();
dtLog.Columns.Add("Execute_Date");
dtLog.Columns.Add("Result");
dtLog.Columns.Add("Message");
dtLog.Columns.Add("Exception");
while (currentDate <= dateEnd)
{
try
{
dtLog.Clear();
//获取FTP的数据
GetFTPData(currentDate);
conn.LockTableList.Add("T_CustomerFlow");
conn.BeginTransaction();
conn.Delete("T_CustomerFlow", "Local_Date_and_Time between '" + currentDate.ToString("yyyy-MM-dd 00:00:00.000") + "' and '" + currentDate.ToString("yyyy-MM-dd 23:59:59.999") + "' ");
//插入数据
conn.InsertBYSQLBC("T_CustomerFlow", m_dtFTP);
dtLog.Rows.Add(new object[] { currentDate, true, "", "" });
conn.Insert("Log_GetFTPData", dtLog);
//throw new Exception("test");
conn.CommitTransaction();
}
catch (Exception ex)
{
conn.RollbackTransaction();
try
{
dtLog.Rows.Add(new object[] { currentDate, false, ex.Message, ex.ToString() });
conn.BeginTransaction();
conn.Insert("Log_GetFTPData", dtLog);
conn.CommitTransaction();
}
catch (Exception ex1)
{
}
try
{
AMSendWS.MsgCenter cls = new AMSendWS.MsgCenter();
cls.SendMsg("AM", "BI客流数据获取", "马卫清", "信息部服务中心", "BI客流数据获取:执行日期：“" + currentDate.ToString("yyyy-MM-dd") + "”时发生错误：" + ex.ToString(), DateTime.Now.AddDays(-1).ToString());
}
catch (Exception ex2)
{
}
}
currentDate = currentDate.AddDays(1);
}
return true;
}
/// <summary>
/// 根据XML内容更新采购单明细
/// </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("GL_ERP");
string strXML = "";
string strSQL = "";
string strSQL1 = "";
strXML = m_request.Tables[0].Rows[0]["XML"].ToString();
//XML是前台传入
ds = Common.Common.GetDSByExcelXML(strXML);
if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "GET")
//读取ROWS到/ROWS 中的参数
{
string strWHERE = "isnull(PR1D_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'";
if (ds.Tables["LIST"].Columns.Contains("Condition"))
{
if (ds.Tables["LIST"].Rows[0]["Condition"].ToString().Trim() != "")
//读取LIST之间参数
{
strWHERE += " AND (" + ds.Tables["LIST"].Rows[0]["Condition"].ToString().Trim() + ")";
}
}
strSQL = @"
SELECT
[PR1D_CONO]
,[PR1D_DIVI]
,[PR1D_OrderDetailId]
,[PR1D_PR1_OrderId]
,[PR1D_PD0_ProductId]
,CAST([PR1D_Total] AS DECIMAL(20,0)) AS PR1D_Total
,[PR1D_UnitID]
,CAST([PR1D_POriginalPrice] AS DECIMAL(20,2)) as PR1D_POriginalPrice
,CAST([PR1D_PCurrencyPrice] AS DECIMAL(20,2)) as PR1D_PCurrencyPrice
,CAST([PR1D_SCurrencyPrice] AS DECIMAL(20,2)) as PR1D_SCurrencyPrice
,CAST([PR1D_Discount] AS DECIMAL(20,2)) as PR1D_Discount
,CAST([PR1D_EstimateCost] AS DECIMAL(20,2)) as PR1D_EstimateCost
,CAST([PR1D_OriginalAmount] AS DECIMAL(20,2)) as PR1D_OriginalAmount
,CAST([PR1D_CurrencyAmount] AS DECIMAL(20,2)) as PR1D_CurrencyAmount
,[PR1D_Status]
,[PR1D_SendDate]
,[PR1D_Seq]
,[PR1D_RgUser]
,[PR1D_RgDt]
,[PR1D_LmUser]
,[PR1D_LmDt]
,[PR1D_UptNo]
,PR1D_CurrencyTypeId
,PD0_ColorId
,PD0_ColorSUId
,PD0_ColorBSUId
,PD0_SizeSUId
,A.PR1_OrderCode
,B.PD0_ProductCodeSU
,B.PD0_SKUSU as PR1D_PD0_SKUSU
,B.PD0_SKU as PR1D_PD0_SKU
,C.SU0_SupplierCode
,C.SU0_SupplierType
,D.DC0_DictName1 AS UnitDictName
,E.DC0_DictName1 AS StatusDictName
,H.DC0_DictName1 AS CurrencyTypeDictName
,I.DC0_DictName1 AS ColorTypeDictName
FROM B02_Bill.PR1D_OrderDetail
LEFT JOIN B02_Bill.PR1_Order A ON A.PR1_OrderId=PR1D_PR1_OrderId
LEFT JOIN B01_MDM.PD0_Product B ON B.PD0_ProductId=PR1D_PD0_ProductId
LEFT JOIN B01_MDM.SU0_Supplier C ON PR1_SU0_SupplierId=C.SU0_SupplierId
LEFT JOIN M01_CONF.DC0_Dictionary D ON PR1D_UnitID=D.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary E ON PR1D_Status=E.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary F ON B.PD0_SKUSU=F.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary G ON B.PD0_Code=G.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary H ON PR1D_CurrencyTypeId=H.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary I ON B.PD0_ColorId=H.DC0_DictId
WHERE " + strWHERE
+ @"ORDER BY [PR1D_LmDt] DESC ";
// ,PD0_SizeSUId
// ,I.DC0_DictName2 AS ColorBSUIdDictName
// ,I.DC0_DictName1 AS ColorSUIdDictName
// ,K.DC0_DictName1 AS SizeSUIdTypeDictName
ds_Return = conn.GetDataSet(strSQL);
}
else
{
ArrayList tableList = new ArrayList();
tableList.Add("B02_Bill.PR1D_OrderDetail");
conn.BeginTransaction();
//开启事物
conn.TableLock(tableList);
//锁表
try
{
if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "DELETE")
{
for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++)
{
strSQL = @"UPDATE B02_Bill.PR1D_OrderDetail SET PR1D_Status='E3CA9B7A-8E17-4E52-B667-39689445D4A1'
WHERE PR1D_OrderDetailId='" + ds.Tables["LIST"].Rows[i]["PR1D_OrderDetailId"].ToString() + @"'
AND isnull(PR1D_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'
";
conn.ExcuteQuerryByTran(strSQL);
strSQL = @"SELECT * FROM B02_Bill.PR1D_OrderDetail WHERE PR1D_PR1_OrderId=@param0
AND isnull(PR1D_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'
";
DataTable dt_SR = conn.GetDataTable(strSQL, new string[1] { ds.Tables["LIST"].Rows[i]["PR1D_PR1_OrderId"].ToString() });
//update
if (dt_SR.Rows.Count > 0)
{
strSQL = @"UPDATE AA SET AA.PR1_PurchaseTotal=BB.PR1_PurchaseTotal,
AA.PR1_PurchaseAmount=BB.PR1_PurchaseAmount,AA.PR1_POriginalAmount=BB.PR1_POriginalAmount,
AA.PR1_LmUser='******',
AA.PR1_LmDt=CONVERT(VARCHAR(100),GETDATE(),121),
AA.PR1_UptNo=AA.PR1_UptNo+1
FROM
B02_Bill.PR1_Order AA,
(SELECT SUM(PR1D_Total) AS PR1_PurchaseTotal,SUM(PR1D_OriginalAmount) AS PR1_POriginalAmount,
SUM(PR1D_EstimateCost) AS PR1D_EstimateCost,SUM(PR1D_CurrencyAmount) AS PR1_PurchaseAmount,PR1D_PR1_OrderId
FROM B02_Bill.PR1D_OrderDetail WHERE PR1D_PR1_OrderId='" + ds.Tables["LIST"].Rows[i]["PR1D_PR1_OrderId"].ToString() + @"'
AND isnull(PR1D_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'
GROUP BY PR1D_PR1_OrderId)BB
WHERE AA.PR1_OrderId=BB.PR1D_PR1_OrderId";
conn.ExcuteQuerryByTran(strSQL);
}
else
{
strSQL = @"UPDATE AA SET AA.PR1_PurchaseTotal='0',
AA.PR1_PurchaseAmount='0',AA.PR1_POriginalAmount='0',
AA.PR1_LmUser='******',
AA.PR1_LmDt=CONVERT(VARCHAR(100),GETDATE(),121),
AA.PR1_UptNo=AA.PR1_UptNo+1
FROM
B02_Bill.PR1_Order AA
WHERE AA.PR1_OrderId='" + ds.Tables["LIST"].Rows[i]["PR1D_PR1_OrderId"].ToString() + @"'";
conn.ExcuteQuerryByTran(strSQL);
}
}
}
else if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "EDIT")
{
for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++)
{
DataTable dt = ds.Tables["LIST"].Clone();
dt.ImportRow(ds.Tables["LIST"].Rows[i]);
strSQL = @"SELECT * FROM B02_Bill.PR1D_OrderDetail WHERE PR1D_OrderDetailId=@param0
AND isnull(PR1D_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'
";
DataTable dt_SR = conn.GetDataTable(strSQL, new string[1] { ds.Tables["LIST"].Rows[i]["PR1D_OrderDetailId"].ToString() });
//update
if (dt_SR.Rows.Count > 0)
{
strSQL = @"SELECT * FROM B01_MDM.PD0_Product WHERE PD0_ProductId=@param0
AND PD0_SKUSU=@param1 AND isnull(PD0_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'
";
DataTable dt_SU = conn.GetDataTable(strSQL, new string[2] { ds.Tables["LIST"].Rows[i]["PR1D_PD0_ProductId"].ToString(), ds.Tables["LIST"].Rows[i]["PR1D_PD0_SKUSU"].ToString() });
//update
if (dt_SU.Rows.Count > 0)
{
//处理结果
// if (ds.Tables["LIST"].Rows[i]["PR1D_LmUser"].ToString() != "")
// {
DataTable dtTemp = conn.GetDataTableInTrans(
@" SELECT CC.PR1D_CurrencyAmount,CC.PR1D_POriginalPrice,CC.PR1D_OriginalAmount,CC.PR1D_EstimateCost,CC.PR1D_PCurrencyPrice,
case when cast(right(SUBSTRING(cast(CC.PR1D_SCurrencyPrice as varchar(20)),1,charindex('.',cast(CC.PR1D_SCurrencyPrice as varchar(20)))-1),2)as int)<30 and '" + ds.Tables["LIST"].Rows[i]["PR1D_SupplierType"].ToString() + @"'='国外'
then CAST(SUBSTRING(SUBSTRING(cast(CC.PR1D_SCurrencyPrice as varchar(20)),1,charindex('.',CC.PR1D_SCurrencyPrice)-1),1,len(SUBSTRING(cast(CC.PR1D_SCurrencyPrice as varchar(20)),1,charindex('.',cast(CC.PR1D_SCurrencyPrice as varchar(20)))-1))-2)-1 AS VARCHAR(20))+'99'
when cast(right(SUBSTRING(cast(CC.PR1D_SCurrencyPrice as varchar(20)),1,charindex('.',cast(CC.PR1D_SCurrencyPrice as varchar(20)))-1),2)as int)>=30 and '" + ds.Tables["LIST"].Rows[i]["PR1D_SupplierType"].ToString() + @"'='国外'
then SUBSTRING(SUBSTRING(cast(CC.PR1D_SCurrencyPrice as varchar(20)),1,charindex('.',cast(CC.PR1D_SCurrencyPrice as varchar(20)))-1),1,len(SUBSTRING(cast(CC.PR1D_SCurrencyPrice as varchar(20)),1,charindex('.',cast(CC.PR1D_SCurrencyPrice as varchar(20)))-1))-2)+'99'
when '" + ds.Tables["LIST"].Rows[i]["PR1D_SupplierType"].ToString() + @"'='国内' then '0'
END AS PR1D_SCurrencyPrice FROM
(SELECT AA.PR1D_CurrencyAmount as PR1D_CurrencyAmount,AA.PR1D_POriginalPrice,AA.PR1D_OriginalAmount as PR1D_OriginalAmount,AA.PR1D_EstimateCost as PR1D_EstimateCost,
CASE WHEN '" + ds.Tables["LIST"].Rows[i]["PR1D_SupplierType"].ToString() + @"'='国内' THEN '0'
ELSE AA.PR1D_EstimateCost/" + ds.Tables["LIST"].Rows[i]["PR1D_Discount"].ToString() + @" END AS PR1D_SCurrencyPrice,AA.PR1D_PCurrencyPrice as PR1D_PCurrencyPrice
FROM
(select PR1_OrderId," + ds.Tables["LIST"].Rows[i]["PR1D_POriginalPrice"].ToString() + @" as PR1D_POriginalPrice ," + ds.Tables["LIST"].Rows[i]["PR1D_POriginalPrice"].ToString() + @"*PR1_ExchangeRate AS PR1D_PCurrencyPrice,
CASE WHEN '" + ds.Tables["LIST"].Rows[i]["PR1D_SupplierType"].ToString() + @"'='国内' THEN " + ds.Tables["LIST"].Rows[i]["PR1D_POriginalPrice"].ToString() + @"*PR1_ExchangeRate
ELSE " + ds.Tables["LIST"].Rows[i]["PR1D_POriginalPrice"].ToString() + @"*PR1_ExchangeRate*(1+PR1_Tariff)*(1+PR1_VAT)*(1+PR1_Freight)
END AS PR1D_EstimateCost," + ds.Tables["LIST"].Rows[i]["PR1D_POriginalPrice"].ToString() + @"*" + ds.Tables["LIST"].Rows[i]["PR1D_Total"].ToString() + @" AS PR1D_OriginalAmount,
" + ds.Tables["LIST"].Rows[i]["PR1D_POriginalPrice"].ToString() + @"*PR1_ExchangeRate*" + ds.Tables["LIST"].Rows[i]["PR1D_Total"].ToString() + @" AS PR1D_CurrencyAmount
FROM B02_Bill.PR1_Order
WHERE PR1_OrderId='" + ds.Tables["LIST"].Rows[i]["PR1D_PR1_OrderId"].ToString() + @"'
AND ISNULL(PR1_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1')AA,
B02_Bill.PR1_Order BB WHERE AA.PR1_OrderId=BB.PR1_OrderId)CC
");
string PR1D_CurrencyAmount = dtTemp.Rows[0]["PR1D_CurrencyAmount"].ToString();
string PR1D_PCurrencyPrice = dtTemp.Rows[0]["PR1D_PCurrencyPrice"].ToString();
string PR1D_OriginalAmount = dtTemp.Rows[0]["PR1D_OriginalAmount"].ToString();
string PR1D_EstimateCost = dtTemp.Rows[0]["PR1D_EstimateCost"].ToString();
string PR1D_SCurrencyPrice = dtTemp.Rows[0]["PR1D_SCurrencyPrice"].ToString();
string PR1D_POriginalPrice = dtTemp.Rows[0]["PR1D_POriginalPrice"].ToString();
if (PR1D_SCurrencyPrice == "0")
{
PR1D_SCurrencyPrice = ds.Tables["LIST"].Rows[i]["PR1D_SCurrencyPrice"].ToString();
}
string strCode = dt_SR.Rows[0]["PR1D_UptNo"].ToString();
if (strCode == "0")
{
strCode = Convert.ToInt32(strCode) + 1.ToString();
}
else
{
strCode = "0";
}
if (!dt.Columns.Contains("PR1D_LmDt"))
{
dt.Columns.Add("PR1D_LmDt");
}
if (!dt.Columns.Contains("PR1D_CurrencyAmount"))
{
dt.Columns.Add("PR1D_CurrencyAmount");
}
if (!dt.Columns.Contains("PR1D_PCurrencyPrice"))
{
dt.Columns.Add("PR1D_PCurrencyPrice");
}
if (!dt.Columns.Contains("PR1D_OriginalAmount"))
{
dt.Columns.Add("PR1D_OriginalAmount");
}
if (!dt.Columns.Contains("PR1D_EstimateCost"))
{
dt.Columns.Add("PR1D_EstimateCost");
}
if (!dt.Columns.Contains("PR1D_POriginalPrice"))
{
dt.Columns.Add("PR1D_POriginalPrice");
}
if (!dt.Columns.Contains("PR1D_SCurrencyPrice"))
{
dt.Columns.Add("PR1D_SCurrencyPrice");
}
if (!dt.Columns.Contains("PR1D_UptNo"))
{
dt.Columns.Add("PR1D_UptNo");
}
dt.Rows[0]["PR1D_LmDt"] = DateTime.Now;
dt.Rows[0]["PR1D_CurrencyAmount"] = PR1D_CurrencyAmount;
dt.Rows[0]["PR1D_PCurrencyPrice"] = PR1D_PCurrencyPrice;
dt.Rows[0]["PR1D_OriginalAmount"] = PR1D_OriginalAmount;
dt.Rows[0]["PR1D_EstimateCost"] = PR1D_EstimateCost;
dt.Rows[0]["PR1D_SCurrencyPrice"] = PR1D_SCurrencyPrice;
dt.Rows[0]["PR1D_POriginalPrice"] = PR1D_POriginalPrice;
dt.Rows[0]["PR1D_UptNo"] = strCode;
dt.Columns.Remove("PR1D_SupplierType");
dt.Columns.Remove("PR1D_PD0_SKUSU");
conn.Update("B02_Bill.PR1D_OrderDetail", dt, "PR1D_OrderDetailId='" + ds.Tables["LIST"].Rows[i]["PR1D_OrderDetailId"].ToString() + @"'");
strSQL = @"UPDATE AA SET AA.PR1_PurchaseTotal=BB.PR1_PurchaseTotal,
AA.PR1_PurchaseAmount=BB.PR1_PurchaseAmount,AA.PR1_POriginalAmount=BB.PR1_POriginalAmount,
AA.PR1_LmUser='******',
AA.PR1_LmDt=CONVERT(VARCHAR(100),GETDATE(),121),
AA.PR1_UptNo=AA.PR1_UptNo+1
FROM
B02_Bill.PR1_Order AA,
(SELECT SUM(PR1D_Total) AS PR1_PurchaseTotal,SUM(PR1D_OriginalAmount) AS PR1_POriginalAmount,
SUM(PR1D_EstimateCost) AS PR1D_EstimateCost,SUM(PR1D_CurrencyAmount) AS PR1_PurchaseAmount,PR1D_PR1_OrderId
FROM B02_Bill.PR1D_OrderDetail WHERE PR1D_PR1_OrderId='" + ds.Tables["LIST"].Rows[i]["PR1D_PR1_OrderId"].ToString() + @"'
AND isnull(PR1D_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'
GROUP BY PR1D_PR1_OrderId)BB
WHERE AA.PR1_OrderId=BB.PR1D_PR1_OrderId";
conn.ExcuteQuerryByTran(strSQL);
strSQL = @"UPDATE B01_MDM.PD0_Product SET PD0_PurchasePrice='" + PR1D_CurrencyAmount + @"',
PD0_SalePrice='" + PR1D_SCurrencyPrice + @"'，
PD0_LmUser='******',
PD0_LmDt=CONVERT(VARCHAR(100),GETDATE(),121),
PD0_UptNo=PD0_UptNo+1
WHERE PD0_ProductId='" + ds.Tables["LIST"].Rows[i]["PR1D_PD0_ProductId"].ToString() + @"' "
;
strSQL1 = @"UPDATE A SET A.PR0_Amount=B.PR1_POriginalAmount,A.PR0_Total=PR1_PurchaseTotal
FROM B02_Bill.PR0_Contract A,
(select SUM(PR1_POriginalAmount) as PR1_POriginalAmount,PR1_PR0_ContractId,SUM(PR1_PurchaseTotal) as PR1_PurchaseTotal
from B02_Bill.PR1_Order
WHERE PR1_OrderId='" + ds.Tables["LIST"].Rows[i]["PR1D_PR1_OrderId"].ToString() + @"'
AND ISNULL(PR1_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'
GROUP BY PR1_PR0_ContractId)B
WHERE A.PR0_ContractId=B.PR1_PR0_ContractId "
;
conn.ExcuteQuerryByTran(strSQL1);
}
}
else //add
{
strSQL = @"SELECT * FROM B01_MDM.PD0_Product WHERE PD0_ProductId=@param0
AND PD0_SKUSU=@param1
";
DataTable dt_SU = conn.GetDataTable(strSQL, new string[2] { ds.Tables["LIST"].Rows[i]["PR1D_PD0_ProductId"].ToString(), ds.Tables["LIST"].Rows[i]["PR1D_PD0_SKUSU"].ToString() });
//update
if (dt_SU.Rows.Count > 0)
{
DataTable dtTemp = conn.GetDataTableInTrans(
@"
SELECT CC.PR1D_CurrencyAmount,CC.PR1D_POriginalPrice,CC.PR1D_OriginalAmount,CC.PR1D_EstimateCost,CC.PR1D_PCurrencyPrice,
case when cast(right(SUBSTRING(cast(CC.PR1D_SCurrencyPrice as varchar(20)),1,charindex('.',cast(CC.PR1D_SCurrencyPrice as varchar(20)))-1),2)as int)<30 and '" + ds.Tables["LIST"].Rows[i]["PR1D_SupplierType"].ToString() + @"'='国外'
then CAST(SUBSTRING(SUBSTRING(cast(CC.PR1D_SCurrencyPrice as varchar(20)),1,charindex('.',CC.PR1D_SCurrencyPrice)-1),1,len(SUBSTRING(cast(CC.PR1D_SCurrencyPrice as varchar(20)),1,charindex('.',cast(CC.PR1D_SCurrencyPrice as varchar(20)))-1))-2)-1 AS VARCHAR(20))+'99'
when cast(right(SUBSTRING(cast(CC.PR1D_SCurrencyPrice as varchar(20)),1,charindex('.',cast(CC.PR1D_SCurrencyPrice as varchar(20)))-1),2)as int)>=30 and '" + ds.Tables["LIST"].Rows[i]["PR1D_SupplierType"].ToString() + @"'='国外'
then SUBSTRING(SUBSTRING(cast(CC.PR1D_SCurrencyPrice as varchar(20)),1,charindex('.',cast(CC.PR1D_SCurrencyPrice as varchar(20)))-1),1,len(SUBSTRING(cast(CC.PR1D_SCurrencyPrice as varchar(20)),1,charindex('.',cast(CC.PR1D_SCurrencyPrice as varchar(20)))-1))-2)+'99'
when '" + ds.Tables["LIST"].Rows[i]["PR1D_SupplierType"].ToString() + @"'='国内' then '0'
END AS PR1D_SCurrencyPrice FROM
(SELECT AA.PR1D_CurrencyAmount as PR1D_CurrencyAmount,AA.PR1D_POriginalPrice,AA.PR1D_OriginalAmount as PR1D_OriginalAmount,AA.PR1D_EstimateCost as PR1D_EstimateCost,
CASE WHEN '" + ds.Tables["LIST"].Rows[i]["PR1D_SupplierType"].ToString() + @"'='国内' THEN '0'
ELSE AA.PR1D_EstimateCost/" + ds.Tables["LIST"].Rows[i]["PR1D_Discount"].ToString() + @" END AS PR1D_SCurrencyPrice,AA.PR1D_PCurrencyPrice as PR1D_PCurrencyPrice
FROM
(select PR1_OrderId," + ds.Tables["LIST"].Rows[i]["PR1D_POriginalPrice"].ToString() + @" as PR1D_POriginalPrice ," + ds.Tables["LIST"].Rows[i]["PR1D_POriginalPrice"].ToString() + @"*PR1_ExchangeRate AS PR1D_PCurrencyPrice,
CASE WHEN '" + ds.Tables["LIST"].Rows[i]["PR1D_SupplierType"].ToString() + @"'='国内' THEN " + ds.Tables["LIST"].Rows[i]["PR1D_POriginalPrice"].ToString() + @"*PR1_ExchangeRate
ELSE " + ds.Tables["LIST"].Rows[i]["PR1D_POriginalPrice"].ToString() + @"*PR1_ExchangeRate*(1+PR1_Tariff)*(1+PR1_VAT)*(1+PR1_Freight)
END AS PR1D_EstimateCost," + ds.Tables["LIST"].Rows[i]["PR1D_POriginalPrice"].ToString() + @"*" + ds.Tables["LIST"].Rows[i]["PR1D_Total"].ToString() + @" AS PR1D_OriginalAmount,
" + ds.Tables["LIST"].Rows[i]["PR1D_POriginalPrice"].ToString() + @"*PR1_ExchangeRate*" + ds.Tables["LIST"].Rows[i]["PR1D_Total"].ToString() + @" AS PR1D_CurrencyAmount
FROM B02_Bill.PR1_Order
WHERE PR1_OrderId='" + ds.Tables["LIST"].Rows[i]["PR1D_PR1_OrderId"].ToString() + @"'
AND ISNULL(PR1_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1')AA,
B02_Bill.PR1_Order BB WHERE AA.PR1_OrderId=BB.PR1_OrderId)CC
");
string PR1D_CurrencyAmount = dtTemp.Rows[0]["PR1D_CurrencyAmount"].ToString();
string PR1D_PCurrencyPrice = dtTemp.Rows[0]["PR1D_PCurrencyPrice"].ToString();
string PR1D_OriginalAmount = dtTemp.Rows[0]["PR1D_OriginalAmount"].ToString();
string PR1D_EstimateCost = dtTemp.Rows[0]["PR1D_EstimateCost"].ToString();
string PR1D_SCurrencyPrice = dtTemp.Rows[0]["PR1D_SCurrencyPrice"].ToString();
string PR1D_POriginalPrice = dtTemp.Rows[0]["PR1D_POriginalPrice"].ToString();
if (PR1D_SCurrencyPrice == "0")
{
PR1D_SCurrencyPrice = ds.Tables["LIST"].Rows[i]["PR1D_SCurrencyPrice"].ToString();
}
string PR1D_OrderDetailId = Guid.NewGuid().ToString();
if (!dt.Columns.Contains("PR1D_OrderDetailId"))
{
dt.Columns.Add("PR1D_OrderDetailId");
}
if (!dt.Columns.Contains("PR1D_RgDt"))
{
dt.Columns.Add("PR1D_RgDt");
}
if (!dt.Columns.Contains("PR1D_LmDt"))
{
dt.Columns.Add("PR1D_LmDt");
}
if (!dt.Columns.Contains("PR1D_UptNo"))
{
dt.Columns.Add("PR1D_UptNo");
}
if (!dt.Columns.Contains("PR1D_CurrencyAmount"))
{
dt.Columns.Add("PR1D_CurrencyAmount");
}
if (!dt.Columns.Contains("PR1D_PCurrencyPrice"))
{
dt.Columns.Add("PR1D_PCurrencyPrice");
}
if (!dt.Columns.Contains("PR1D_OriginalAmount"))
{
dt.Columns.Add("PR1D_OriginalAmount");
}
if (!dt.Columns.Contains("PR1D_EstimateCost"))
{
dt.Columns.Add("PR1D_EstimateCost");
}
if (!dt.Columns.Contains("PR1D_SCurrencyPrice"))
{
dt.Columns.Add("PR1D_SCurrencyPrice");
}
dt.Rows[0]["PR1D_OrderDetailId"] = PR1D_OrderDetailId;
dt.Rows[0]["PR1D_RgDt"] = DateTime.Now;
dt.Rows[0]["PR1D_LmDt"] = DateTime.Now;
dt.Rows[0]["PR1D_UptNo"] = '0';
dt.Rows[0]["PR1D_CurrencyAmount"] = PR1D_CurrencyAmount;
dt.Rows[0]["PR1D_PCurrencyPrice"] = PR1D_PCurrencyPrice;
dt.Rows[0]["PR1D_OriginalAmount"] = PR1D_OriginalAmount;
dt.Rows[0]["PR1D_EstimateCost"] = PR1D_EstimateCost;
dt.Rows[0]["PR1D_SCurrencyPrice"] = PR1D_SCurrencyPrice;
dt.Rows[0]["PR1D_POriginalPrice"] = PR1D_POriginalPrice;
dt.Columns.Remove("PR1D_SupplierType");
dt.Columns.Remove("PR1D_PD0_SKUSU");
conn.Insert("B02_Bill.PR1D_OrderDetail", dt);
strSQL = @"UPDATE AA SET AA.PR1_PurchaseTotal=BB.PR1_PurchaseTotal,
AA.PR1_PurchaseAmount=BB.PR1_PurchaseAmount,AA.PR1_POriginalAmount=BB.PR1_POriginalAmount,
AA.PR1_LmUser='******',
AA.PR1_LmDt=CONVERT(VARCHAR(100),GETDATE(),121),
AA.PR1_UptNo=AA.PR1_UptNo+1
FROM
B02_Bill.PR1_Order AA,
(SELECT SUM(PR1D_Total) AS PR1_PurchaseTotal,SUM(PR1D_OriginalAmount) AS PR1_POriginalAmount,
SUM(PR1D_EstimateCost) AS PR1D_EstimateCost,SUM(PR1D_CurrencyAmount) AS PR1_PurchaseAmount,PR1D_PR1_OrderId
FROM B02_Bill.PR1D_OrderDetail WHERE PR1D_PR1_OrderId='" + ds.Tables["LIST"].Rows[i]["PR1D_PR1_OrderId"].ToString() + @"'
AND isnull(PR1D_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'
GROUP BY PR1D_PR1_OrderId)BB
WHERE AA.PR1_OrderId=BB.PR1D_PR1_OrderId";
conn.ExcuteQuerryByTran(strSQL);
strSQL1 = @"UPDATE B01_MDM.PD0_Product SET PD0_PurchasePrice='" + PR1D_CurrencyAmount + @"',
PD0_SalePrice='" + PR1D_SCurrencyPrice + @"',
PD0_LmUser='******',
PD0_LmDt=CONVERT(VARCHAR(100),GETDATE(),121),
PD0_UptNo=PD0_UptNo+1
WHERE PD0_ProductId='" + ds.Tables["LIST"].Rows[i]["PR1D_PD0_ProductId"].ToString() + @"' "
;
conn.ExcuteQuerryByTran(strSQL1);
strSQL1 = @"UPDATE A SET A.PR0_Amount=B.PR1_POriginalAmount,A.PR0_Total=PR1_PurchaseTotal
FROM B02_Bill.PR0_Contract A,
(select SUM(PR1_POriginalAmount) as PR1_POriginalAmount,PR1_PR0_ContractId,SUM(PR1_PurchaseTotal) as PR1_PurchaseTotal
from B02_Bill.PR1_Order
WHERE PR1_OrderId='" + ds.Tables["LIST"].Rows[i]["PR1D_PR1_OrderId"].ToString() + @"'
AND ISNULL(PR1_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'
GROUP BY PR1_PR0_ContractId)B
WHERE A.PR0_ContractId=B.PR1_PR0_ContractId "
;
conn.ExcuteQuerryByTran(strSQL1) ;
}
}
}
}
conn.CommitTransaction();
ds_Return = Common.Common.GetRequestDataSet(new string[] { "Result" }, new string[] { "true" });
}
catch
{
conn.RollbackTransaction();
//事物结束
throw;
}
}
return ds_Return;
}
catch
{
throw;
}
}
/// <summary>
/// 部署WS
/// </summary>
/// <returns></returns>
public bool Execute()
{
Dbconn conn = new Dbconn("WSRR");
Dbconn connDeploy = new Dbconn("WSRR_Deploy");
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 strSQL = "";
DataSet ds = conn.GetDataSet(@"
SELECT * FROM dbo.WS
WHERE WS_ID=@param0;
SELECT * FROM dbo.WS_Param
WHERE WS_ID=@param0",new string[]{strWS_ID});
if (ds.Tables.Count !=2 || ds.Tables[0].Rows.Count == 0)
{
throw new Exception("WS_ID的信息不正确！");
}
string strENV = ConfigurationManager.AppSettings["WSRR_ENV"];
if (strENV.ToLower() == "prod")
{
ds.Tables[0].Rows[0]["WS_Host"] = ds.Tables[0].Rows[0]["WS_Host_Test"].ToString();
}
else
{
ds.Tables[0].Rows[0]["WS_Host"] = ds.Tables[0].Rows[0]["WS_Host_Prod"].ToString();
}
ArrayList tableList = new ArrayList();
tableList.Add("WS");
tableList.Add("WS_Param");
connDeploy.BeginTransaction();
connDeploy.TableLock(tableList);
connDeploy.Delete("WS", "WS_ID='" + strWS_ID + "'");
connDeploy.Delete("WS_Param", "WS_ID='" + strWS_ID + "'");
connDeploy.Insert("WS", ds.Tables[0]);
connDeploy.Insert("WS_Param", ds.Tables[1]);
connDeploy.CommitTransaction();
return true;
}
catch
{
conn.RollbackTransaction();
throw;
}
}
/// <summary>
/// 编辑任务信息
/// </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 strTASK_ID = m_request.Tables[0].Rows[0]["task_id"].ToString(); //获取参数TaskID
string strWS_Name = m_request.Tables[0].Rows[0]["ws_name"].ToString(); //获取参数WS名称
ArrayList listTable = new ArrayList();
listTable.Add("WS_Scheduling");
conn.BeginTransaction(); //开启事务
conn.TableLock(listTable); //锁表WS_Scheduling
string strWhere = string.Empty;
//创建DataTable，用于传参
DataTable dt_Add = new DataTable();
dt_Add.Columns.Add("TASK_ID");
dt_Add.Columns.Add("WS_ID");
dt_Add.Columns.Add("URL");
dt_Add.Columns.Add("WS_Name");
dt_Add.Columns.Add("WS_LogicName");
dt_Add.Columns.Add("Execute_Type");
dt_Add.Columns.Add("Execute_StartTime");
dt_Add.Columns.Add("Execute_EndTime");
dt_Add.Columns.Add("Execute_Cycle");
dt_Add.Columns.Add("Execute_DayStartTime");
dt_Add.Columns.Add("Execute_DayEndTime");
dt_Add.Columns.Add("Execute_WeekTime");
dt_Add.Columns.Add("Execute_MonthTime");
dt_Add.Columns.Add("Enabled");
dt_Add.Columns.Add("Error_Count");
dt_Add.Columns.Add("Remind_ErrorCount");
dt_Add.Columns.Add("Remind_UserName");
dt_Add.Columns.Add("CreateUser");
dt_Add.Columns.Add("CreateTime");
dt_Add.Columns.Add("UpdateUser");
dt_Add.Columns.Add("UpdateTime");
DataRow dr = dt_Add.NewRow();
//循环DataTabel的列
for (int i = 0; i < dt_Add.Columns.Count-4 ; i++)
{
if (dt_Add.Columns[i].ColumnName == "TASK_ID")
{
continue;
}
else
{
dr[i] = m_request.Tables[0].Rows[0][dt_Add.Columns[i].ColumnName].ToString(); //对DataTabel中的每列设置值
}
}
dt_Add.Rows.Add(dr);
if (strTASK_ID == "") //如果为空，说明是复制数据
{
strTASK_ID = Guid.NewGuid().ToString(); //生成GUID
dt_Add.Rows[0]["TASK_ID"] = strTASK_ID;
dt_Add.Rows[0]["CreateTime"] = DateTime.Now; //设置创建时间
dt_Add.Rows[0]["CreateUser"] = hzyMessage.User_Name; //设置创建者
dt_Add.Rows[0]["UpdateTime"] = DateTime.Now; //设置更新时间
dt_Add.Rows[0]["UpdateUser"] = hzyMessage.User_Name; //设置更新者
dt_Add.Columns.Add("IsDeleted");
dt_Add.Rows[0]["IsDeleted"] = false; //将删除标志设为“False”
conn.Insert("WS_Scheduling", dt_Add); //插入数据
}
else
{
//更新
dt_Add.Columns.Remove("CreateUser"); //删除CreateUser列，更新不需要
dt_Add.Columns.Remove("CreateTime"); //删除CreateTime列，更新不需要
dt_Add.Columns.Remove("TASK_ID");
dt_Add.Rows[0]["UpdateTime"] = DateTime.Now; //将更新时间设为当前时间
dt_Add.Rows[0]["UpdateUser"] = hzyMessage.User_Name; //设置更新者
strWhere = " TASK_ID = '" + strTASK_ID + "'"; //设置更新条件
conn.Update("WS_Scheduling", dt_Add, strWhere); //更新数据
}
conn.CommitTransaction(); //提交事务
return true;
}
catch
{
conn.RollbackTransaction(); //回滚
throw;
}
}
public DataSet GetData()
{
Dbconn conn = new Dbconn("WSRR");
string strWhere = string.Empty;
string strXML = m_request.Tables[0].Rows[0]["XML"].ToString();
DataTable dt_EditResult = new dsCommon.dtResultDataTable();
DataSet dsRequest = new DataSet();
DataSet ds_Return = new DataSet();
dsRequest = Common.Common.GetDSByExcelXML(strXML);
DataTable dt_OPTYPE = dsRequest.Tables["OPTYPE"];
DataTable dt_LIST = dsRequest.Tables["LIST"];
for (int i = 0; i < dt_OPTYPE.Rows.Count; i++)
{
//获取权限信息
if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "GET")
{
string strSQL = @"
SELECT
[AUZID]
,[AUZName]
FROM [WSRR_Manage].[F01_CERT].[AUZ]";
ds_Return = conn.GetDataSetForPageList(strSQL,m_hzyPageInfo);
}
//新增环境的权限名称
else if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "INSERT")
{
//String Env_ID = dt_LIST.Rows[0]["Env_ID"].ToString();
String strAuzName = dt_LIST.Rows[0]["AuzName"].ToString();
string strsql = @"
SELECT COUNT(1) AS Cnt
FROM [WSRR_Manage].[F01_CERT].[AUZ]
WHERE Authorization_Name='" + strAuzName + "'";
DataTable dt = conn.GetDataTable(strsql);
if (int.Parse(dt.Rows[0][0].ToString()) > 0)
{
dt_EditResult.Rows.Add(new object[] { false, "权限：" + strAuzName + "已存在!" });
}
else
{
//dt_LIST.Rows[0]["AuzID"] = Guid.NewGuid().ToString();
conn.BeginTransaction();
bool flag = conn.Insert(" [WSRR_Manage].[F01_CERT].[AUZ]", dt_LIST);
conn.CommitTransaction();
if (flag)
{
dt_EditResult.Rows.Add(new object[] { true, "权限：" + strAuzName + "添加成功!" });
}
else
{
dt_EditResult.Rows.Add(new object[] { true, "权限：" + strAuzName + "添加失败!" });
}
}
ds_Return.Tables.Add(dt_EditResult);
}
//修改权限的菜单树
else if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "EDIT")
{
String strAuZID = dt_LIST.Rows[0]["AuzID"].ToString();
strWhere = " AuzID=" + strAuZID;
dt_LIST.Columns.Remove("AuzID");//移除属性，标识符列（ID）不能更新
conn.LockTableList.Add("[WSRR_Manage].[F01_CERT].[AUZ]");
conn.BeginTransaction();
bool flag = conn.Update("[WSRR_Manage].[F01_CERT].[AUZ]", dt_LIST, strWhere);
conn.CommitTransaction();
if (flag)
{
dt_EditResult.Rows.Add(new object[] { true, "权限修改成功!" });
}
else
{
dt_EditResult.Rows.Add(new object[] { false, "权限修改失败!" });
}
ds_Return.Tables.Add(dt_EditResult);
}
//删除权限
else if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "DELETE")
{
String strAuZID = dt_LIST.Rows[0]["AuzID"].ToString();
strWhere = " AuzID=" + strAuZID;
conn.BeginTransaction();
bool flag = conn.Delete("[WSRR_Manage].[F01_CERT].[AUZ]", strWhere);
conn.CommitTransaction();
if (flag)
{
dt_EditResult.Rows.Add(new object[] { true, "权限删除成功!" });
}
else
{
dt_EditResult.Rows.Add(new object[] { false, "权限删除失败!" });
}
ds_Return.Tables.Add(dt_EditResult);
}
}
return ds_Return;
}
/// <summary>
/// 修改审批记录
/// </summary>
/// <returns></returns>
public DataSet SetApproveRows(Dbconn conn, String strType)
{
DataSet ds_Return = new DataSet();
string strActor = m_request.Tables[0].Rows[0]["actor"].ToString();
string strXML = m_request.Tables[0].Rows[0]["XML"].ToString();
DataSet dsXML = Common.Common.GetDSByExcelXML(strXML);
String strOPType = dsXML.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper();
String strID = "";
//String strFatherID = dsXML.Tables["LIST"].Rows[0]["ct1_fatherid"].ToString();
DataTable dt_List = dsXML.Tables["LIST"];
if (strType == "SetApH" || strType == "NewApH")
{
strID = dsXML.Tables["LIST"].Rows[0]["bg4_id"].ToString();
}
else if (strType == "SetApAH" || strType == "NewApAH")
{
strID = dsXML.Tables["LIST"].Rows[0]["bg7_id"].ToString();
}
if (strID == "")
{
throw new Exception("数据异常，缺少主键，请联系管理员！");
}
bool bResult = false;
if (strType == "NewApH") //新建
{
conn.BeginTransaction();
bResult = conn.Insert("B01_MDM.BG4_EBudgetApprove", dt_List);
conn.CommitTransaction();
}
else if (strType == "SetApH") //修改
{
conn.BeginTransaction();
bResult = conn.Update("B01_MDM.BG4_EBudgetApprove", dt_List, "bg4_id='" + strID + "'");
conn.CommitTransaction();
}
if (strType == "NewApAH") //新建 追加
{
conn.BeginTransaction();
bResult = conn.Insert("B01_MDM.BG7_EBudgetApproveAdd", dt_List);
conn.CommitTransaction();
}
else if (strType == "SetApAH") //修改 追加
{
conn.BeginTransaction();
bResult = conn.Update("B01_MDM.BG7_EBudgetApproveAdd", dt_List, "bg7_id='" + strID + "'");
conn.CommitTransaction();
}
if (bResult)
{
dt_EditResult.Rows.Add(new object[] { true, "保存成功！" });
}
else
{
dt_EditResult.Rows.Add(new object[] { false, "保存失败！" });
}
ds_Return.Tables.Add(dt_EditResult);
return ds_Return;
}
/// <summary>
/// 新建或修改记录
/// </summary>
/// <returns></returns>
public DataSet SaveRows(Dbconn conn, String strType)
{
DataSet ds_Return = new DataSet();
string strActor = m_request.Tables[0].Rows[0]["actor"].ToString();
string strXML = m_request.Tables[0].Rows[0]["XML"].ToString();
DataSet dsXML = Common.Common.GetDSByExcelXML(strXML);
DataTable dtOptypes = dsXML.Tables["OPTYPE"];
bool bResult = true;
conn.BeginTransaction();
for (int i = 0; i < dtOptypes.Rows.Count; i++)
{
//如果包含rowindex，就循环移除
if (dsXML.Tables[i + 1].Columns.Contains("rowindex"))
{
dsXML.Tables[i + 1].Columns.Remove("rowindex");
}
//如果子表解析出来的XML，类型有insert，做新增
if (dtOptypes.Rows[i][0].ToString().ToUpper() == "INSERT")
{
DataTable dt_List1 = dsXML.Tables[i + 1];
//如果包含bg3_lmdt，就循环移除
if (dt_List1.Columns.Contains("ct1_lmdt"))
{
dt_List1.Columns.Remove("ct1_lmdt");
}
bResult = conn.Insert("M01_CONF.CT0_CSYTAB", dt_List1);
}
//如果类型包含，update，解析成表，做update
else if (dtOptypes.Rows[i][0].ToString().ToUpper() == "UPDATE")
{
DataTable dt_List2 = dsXML.Tables[i + 1];
//添加更新主键，按照GUID更新
ArrayList ListKey = new ArrayList();
ListKey.Add("ct1_id");
bResult = conn.Update("M01_CONF.CT0_CSYTAB", dt_List2, ListKey);
}
//如果类型包含，DELETE，解析成表，做DELETE
else if (dtOptypes.Rows[i][0].ToString().ToUpper() == "DELETE")
{
DataTable dt_List3 = dsXML.Tables[i + 1];
for (int k = 0; k < dt_List3.Rows.Count; k++)
{
string strID1 = dt_List3.Rows[k]["ct1_id"].ToString();
bResult = conn.Delete("M01_CONF.CT0_CSYTAB", "ct1_id=" + "'" + strID1 + "'");
}
}
}
conn.CommitTransaction();
if (bResult)
{
dt_EditResult.Rows.Add(new object[] { true, "保存成功！" });
}
else
{
dt_EditResult.Rows.Add(new object[] { false, "保存失败！" });
}
ds_Return.Tables.Add(dt_EditResult);
return ds_Return;
}
/// <summary>
/// 根据XML内容更新合同
/// </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("GL_ERP");
string strXML = "";
string strSQL = "";
strXML = m_request.Tables[0].Rows[0]["XML"].ToString();
//XML是前台传入
ds = Common.Common.GetDSByExcelXML(strXML);
if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "GET")
//读取ROWS到/ROWS 中的参数
{
string strWHERE = "ISNULL(PR0_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'";
if (ds.Tables["LIST"].Columns.Contains("Condition"))
{
if (ds.Tables["LIST"].Rows[0]["Condition"].ToString().Trim() != "")
//读取LIST之间参数
{
strWHERE += " AND (" + ds.Tables["LIST"].Rows[0]["Condition"].ToString().Trim() + ")";
}
}
strSQL = @"SELECT
[PR0_CONO]
,[PR0_DIVI]
,[PR0_ContractId]
,[PR0_ContractCode]
,[PR0_SU0_SupplierId]
,CAST([PR0_Amount] AS DECIMAL(20,2)) as PR0_Amount
,[PR0_CurrencyId]
,[PR0_Description]
,[PR0_Status]
,[PR0_ECopyPath]
,[PR0_PrePayRate]
,LEFT(CONVERT(VARCHAR(20),[PR0_StartDate],121),10) AS PR0_StartDate
,LEFT(CONVERT(VARCHAR(20),[PR0_EndDate],121),10) AS PR0_EndDate
,[PR0_RgUser]
,[PR0_RgDt]
,[PR0_LmUser]
,[PR0_LmDt]
,[PR0_UptNo]
,[PR0_SettleAccount]
,[PR0_Total]
,A.[SU0_SupplierCode] as SU0_SupplierCode
,A.[SU0_SupplierName] as SU0_SupplierName
,B.WFT_NODE AS WFT_NODE1
,C.WFT_NODE AS WFT_NODE2
,D.WFT_NODE AS WFT_NODE3
,E.WFT_NODE AS WFT_NODE4
,F.WFT_NODE AS WFT_NODE5
,L.DC0_DICTNAME1 AS DC0_DICTNAME1
,M.DC0_DICTNAME1 AS DC0_DICTNAME2
,N.DC0_DICTNAME1 AS DC0_DICTNAME3
,O.DC0_DICTNAME1 AS DC0_DICTNAME4
,P.DC0_DICTNAME1 AS DC0_DICTNAME5
,G.WFP_AuditTime AS WFP_AuditTime1
,H.WFP_AuditTime AS WFP_AuditTime2
,I.WFP_AuditTime AS WFP_AuditTime3
,J.WFP_AuditTime AS WFP_AuditTime4
,K.WFP_AuditTime AS WFP_AuditTime5
,G.WFP_AFID AS WFP_AFID1
,H.WFP_AFID AS WFP_AFID2
,I.WFP_AFID AS WFP_AFID3
,J.WFP_AFID AS WFP_AFID4
,K.WFP_AFID AS WFP_AFID5
,Q.[DC0_DictName1] AS CurrencyDictName
,R.[DC0_DictName1] AS StatusDictName
,S.[DC0_DictName1] AS AccountDictName
FROM B02_Bill.PR0_Contract WITH ( NOLOCK )
LEFT JOIN B01_MDM.SU0_Supplier A ON PR0_SU0_SupplierId=A.SU0_SupplierId
LEFT JOIN M02_BPM.WFT_AuditFlowDefine B ON B.WFT_Type='Contract' and B.WFT_Step='1'
LEFT JOIN M02_BPM.WFT_AuditFlowDefine C ON C.WFT_Type='Contract' and C.WFT_Step='2'
LEFT JOIN M02_BPM.WFT_AuditFlowDefine D ON D.WFT_Type='Contract' and D.WFT_Step='3'
LEFT JOIN M02_BPM.WFT_AuditFlowDefine E ON E.WFT_Type='Contract' and E.WFT_Step='4'
LEFT JOIN M02_BPM.WFT_AuditFlowDefine F ON F.WFT_Type='Contract' and F.WFT_Step='5'
LEFT JOIN M02_BPM.WFP_AuditFlow G ON PR0_ContractId=G.WFP_BillId AND G.WFP_WFT_AFDId=B.WFT_AFDId
LEFT JOIN M02_BPM.WFP_AuditFlow H ON PR0_ContractId=H.WFP_BillId AND H.WFP_WFT_AFDId=C.WFT_AFDId
LEFT JOIN M02_BPM.WFP_AuditFlow I ON PR0_ContractId=I.WFP_BillId AND I.WFP_WFT_AFDId=D.WFT_AFDId
LEFT JOIN M02_BPM.WFP_AuditFlow J ON PR0_ContractId=J.WFP_BillId AND J.WFP_WFT_AFDId=E.WFT_AFDId
LEFT JOIN M02_BPM.WFP_AuditFlow K ON PR0_ContractId=K.WFP_BillId AND K.WFP_WFT_AFDId=F.WFT_AFDId
LEFT JOIN M01_CONF.DC0_Dictionary L ON G.WFP_AFResultId=L.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary M ON H.WFP_AFResultId=M.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary N ON I.WFP_AFResultId=N.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary O ON J.WFP_AFResultId=O.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary P ON K.WFP_AFResultId=P.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary Q ON PR0_CurrencyId=Q.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary R ON PR0_Status=R.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary S ON PR0_SettleAccount=S.DC0_DictId
WHERE " + strWHERE
+ @"ORDER BY [PR0_LmDt] DESC ";
ds_Return = conn.GetDataSet(strSQL);
}
else
{
ArrayList tableList = new ArrayList();
tableList.Add("B02_Bill.PR0_Contract");
conn.BeginTransaction();
//开启事物
conn.TableLock(tableList);
//锁表
try
{
if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "DELETE")
{
for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++)
{
strSQL = @"UPDATE B02_Bill.PR0_Contract SET PR0_Status='E3CA9B7A-8E17-4E52-B667-39689445D4A1'
WHERE PR0_ContractId='" + ds.Tables["LIST"].Rows[i]["PR0_ContractId"].ToString() + @"'
AND ISNULL(PR0_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'
";
conn.ExcuteQuerryByTran(strSQL);
}
}
else if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "EDIT")
{
for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++)
{
DataTable dt = ds.Tables["LIST"].Clone();
dt.ImportRow(ds.Tables["LIST"].Rows[i]);
strSQL = @"SELECT * FROM B02_Bill.PR0_Contract WHERE PR0_ContractId=@param0
AND ISNULL(PR0_Status,'') <>'E3CA9B7A-8E17-4E52-B667-39689445D4A1' ";
DataTable dt_SR = conn.GetDataTable(strSQL, new string[1] { ds.Tables["LIST"].Rows[i]["PR0_ContractId"].ToString() });
//update
if (dt_SR.Rows.Count > 0)
{
strSQL = @"SELECT * FROM M02_BPM.WFP_AuditFlow WHERE WFP_BillId=@param0
AND WFP_AFResultId<>''
";
DataTable dt_CO = conn.GetDataTable(strSQL, new string[1] { ds.Tables["LIST"].Rows[i]["PR0_ContractId"].ToString() });
//update
if (dt_CO.Rows.Count > 0)
{
throw new Exception("合同已审核，不能进行修改！");
}
else
//处理结果
if (ds.Tables["LIST"].Rows[i]["PR0_LmUser"].ToString() != "")
{
string strCode = dt_SR.Rows[0]["PR0_UptNo"].ToString();
if (strCode == "0")
{
strCode = Convert.ToInt32(strCode) + 1.ToString();
}
else
{
strCode = "0";
}
if (!dt.Columns.Contains("PR0_LmDt"))
{
dt.Columns.Add("PR0_LmDt");
}
// if (!dt.Columns.Contains("PR0_Status"))
// {
// dt.Columns.Add("PR0_Status");
// }
if (!dt.Columns.Contains("PR0_UptNo"))
{
dt.Columns.Add("PR0_UptNo");
}
// dt.Rows[0]["PR0_Status"] = '0';
dt.Rows[0]["PR0_LmDt"] = DateTime.Now;
dt.Rows[0]["PR0_UptNo"] = strCode;
}
conn.Update("B02_Bill.PR0_Contract", dt, "PR0_ContractId='" + ds.Tables["LIST"].Rows[i]["PR0_ContractId"].ToString() + @"'");
}
else //add
{
DataTable dtTemp = conn.GetDataTableInTrans(
@"SELECT ISNULL(MAX(PR0_ContractCode),'') AS MaxCode,
RIGHT(left( CONVERT(VARCHAR(100),DateAdd(m,+0,GETDATE()),112),4),2)+'-'+
LEFT(RIGHT( CONVERT(VARCHAR(100),DateAdd(m,+0,GETDATE()),112),4),2) as PreCode FROM B02_Bill.PR0_Contract where
left(PR0_ContractCode,'2')=RIGHT(left( CONVERT(VARCHAR(100),DateAdd(m,+0,GETDATE()),112),4),2)
AND left(right(PR0_ContractCode,'4'),'2')=LEFT(RIGHT( CONVERT(VARCHAR(100),DateAdd(m,+0,GETDATE()),112),4),2) ");
//供应商编号生成
string strCode = dtTemp.Rows[0]["MaxCode"].ToString();
if (strCode == "")
{
strCode = dtTemp.Rows[0]["PreCode"].ToString() + "01";
// strCode = strCode.Substring(strCode.Substring(DateTime.Now.ToString().Length + 4).Length + 4)
// RIGHT(left(DateTime.Now.ToString(), 4), 2) + "-" + RIGHT(left(DateTime.Now.ToString(), 7), 2) + "01";
//dtTemp.Rows[0]["PreCode"].ToString() + "0001";
}
else
{
strCode = dtTemp.Rows[0]["PreCode"].ToString() + (Convert.ToInt32(strCode.Substring(strCode.Length - 2)) + 1).ToString("00");
//strCode = left(strCode, 2) + "-" + left(RIGHT(strCode, 4), 2) + (Convert.ToInt32(strCode.Substring(strCode.Length - 2)) + 1).ToString("00");
// dtTemp.Rows[0]["PreCode"].ToString() + (Convert.ToInt32(strCode.Substring(strCode.Length - 4)) + 1).ToString("0000");
}
string PR0_ContractId = Guid.NewGuid().ToString();
if (!dt.Columns.Contains("PR0_ContractId"))
{
dt.Columns.Add("PR0_ContractId");
}
// if (!dt.Columns.Contains("PR0_Status"))
// {
// dt.Columns.Add("PR0_Status");
// }
if (!dt.Columns.Contains("PR0_RgDt"))
{
dt.Columns.Add("PR0_RgDt");
}
if (!dt.Columns.Contains("PR0_LmDt"))
{
dt.Columns.Add("PR0_LmDt");
}
if (!dt.Columns.Contains("PR0_ContractCode"))
{
dt.Columns.Add("PR0_ContractCode");
}
if (!dt.Columns.Contains("PR0_UptNo"))
{
dt.Columns.Add("PR0_UptNo");
}
dt.Rows[0]["PR0_ContractId"] = PR0_ContractId;
// dt.Rows[0]["PR0_Status"] = '0';
dt.Rows[0]["PR0_RgDt"] = DateTime.Now;
dt.Rows[0]["PR0_LmDt"] = DateTime.Now;
dt.Rows[0]["PR0_ContractCode"] = strCode;
dt.Rows[0]["PR0_UptNo"] = '0';
conn.Insert("B02_Bill.PR0_Contract", dt);
GL_ERP_Common clsCommon = new GL_ERP_Common();
GL_ERP_Common.CreateWorkFlow(conn, "Contract", ds.Tables["LIST"].Rows[i]["PR0_CONO"].ToString(), ds.Tables["LIST"].Rows[i]["PR0_DIVI"].ToString(), PR0_ContractId,
"Contract", "", "", ds.Tables["LIST"].Rows[i]["PR0_Status"].ToString(), ds.Tables["LIST"].Rows[i]["PR0_RgUser"].ToString(),
ds.Tables["LIST"].Rows[i]["PR0_LmUser"].ToString(), "", "PR0_ContractId", "B02_Bill.PR0_Contract");
}
}
}
conn.CommitTransaction();
ds_Return = Common.Common.GetRequestDataSet(new string[] { "Result" }, new string[] { "true" });
}
catch
{
conn.RollbackTransaction();
//事物结束
throw;
}
}
return ds_Return;
}
catch
{
throw;
}
}
/// <summary>
/// 新建或修改预算科目类别记录
/// </summary>
/// <returns></returns>
public DataSet SetBMTypes(Dbconn conn, String strType)
{
String strCode = "";
DataSet ds_Return = new DataSet();
string strActor = m_request.Tables[0].Rows[0]["actor"].ToString();
string strXML = m_request.Tables[0].Rows[0]["XML"].ToString();
DataSet dsXML = Common.Common.GetDSByExcelXML(strXML);
String strOPType = dsXML.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper();
String strID = dsXML.Tables["LIST"].Rows[0]["ct1_id"].ToString();
//String strFatherID = dsXML.Tables["LIST"].Rows[0]["ct1_fatherid"].ToString();
DataTable dt_List = dsXML.Tables["LIST"];
if (strID == "")
{
throw new Exception("数据异常，缺少主键，请联系管理员！");
}
bool bResult = false;
if (strType == "NewBMType") //新建大小类
{
conn.BeginTransaction();
bResult = conn.Insert("M01_CONF.CT0_CSYTAB", dt_List);
conn.CommitTransaction();
}
else if (strType == "UpdBMType") //修改大小类
{
conn.BeginTransaction();
bResult = conn.Update("M01_CONF.CT0_CSYTAB", dt_List, "ct1_id='" + strID + "'");
conn.CommitTransaction();
}
if (bResult)
{
dt_EditResult.Rows.Add(new object[] { true, strCode });
}
else
{
dt_EditResult.Rows.Add(new object[] { false, "保存失败！" });
}
ds_Return.Tables.Add(dt_EditResult);
return ds_Return;
}
/// <summary>
/// 当进行用户绑定时，写入日志
/// </summary>
/// <param name="pstrVexSSOLoginUser"></param>
/// <param name="pstrVexSSOPassWord"></param>
/// <param name="pstrAccreditationType"></param>
/// <param name="pstrBindUser"></param>
/// <param name="pstrBindPassWord"></param>
/// <param name="pstrResult"></param>
private void WriteBindLog(string pstrVexSSOLoginUser, string pstrVexSSOPassWord,
string pstrAccreditationType, string pstrBindUser, string pstrBindPassWord,
string pstrResult)
{
Dbconn conn = new Dbconn("VEXSSO");
DataTable dt = new MDM.BindDataTable();
DataRow dr = dt.NewRow();
dr["VexSSOLoginUser"] = pstrVexSSOLoginUser;
dr["VexSSOPassWord"] = pstrVexSSOPassWord;
dr["AccreditationType"] = pstrAccreditationType;
dr["BindUser"] = pstrBindUser;
dr["BindPassWord"] = pstrBindPassWord;
dr["Result"] = pstrResult;
dt.Rows.Add(dr);
conn.BeginTransaction();
conn.Insert("M03_Govern.Bind", dt);
conn.CommitTransaction();
}
/// <summary>
/// 根据XML内容更新供应商
/// </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("GL_ERP");
string strXML = "";
string strSQL = "";
strXML = m_request.Tables[0].Rows[0]["XML"].ToString();
//XML是前台传入
ds = Common.Common.GetDSByExcelXML(strXML);
if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "GET")
//读取ROWS到/ROWS 中的参数
{
string strWHERE = "ISNULL(PD0_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'";
if (ds.Tables["LIST"].Columns.Contains("Condition"))
{
if (ds.Tables["LIST"].Rows[0]["Condition"].ToString().Trim() != "")
//读取LIST之间参数
{
strWHERE += " AND (" + ds.Tables["LIST"].Rows[0]["Condition"].ToString().Trim() + ")";
}
}
strSQL = @"
SELECT
[PD0_CONO]
,[PD0_DIVI]
,[PD0_ProductId]
,[PD0_SUO_SupplierId]
,[PD0_BD0_BrandId]
,[PD0_ProductCodeSU]
,[PD0_ColorSUId]
,[PD0_SizeSUId]
,[PD0_SKUSU]
,[PD0_Code]
,[PD0_ColorId]
,[PD0_SizeId]
,[PD0_SKU]
,[PD0_YearId]
,[PD0_SeasonId]
,[PD0_Lvl1]
,[PD0_Lvl2]
,[PD0_Lvl3]
,[PD0_SourceCountry]
,[PD0_Composition]
,[PD0_QulityLevel]
,[PD0_StandardId]
,[PD0_SecurityLevelId]
,cast([PD0_PurchasePrice] as decimal(20,2)) as PD0_PurchasePrice
,cast([PD0_SalePrice] as decimal(20,2)) as PD0_SalePrice
,[PD0_UnitId]
,[PD0_IsPrinted]
,[PD0_Status]
,[PD0_RgUser]
,[PD0_RgDt]
,[PD0_LmUser]
,[PD0_LmDt]
,[PD0_ColorSUId]
,[PD0_PicPath]
,[PD0_Lvl0]
,A.[SU0_SupplierCode]
,B.[BD0_BrandName]
,C.[DC0_DictName1] AS YearDictName
,D.[DC0_DictName1] AS SeasonDictName
,E.[DC0_DictName1] AS StandardDictName
,F.[DC0_DictName1] AS SecurityLevelDictName
,G.[DC0_DictName1] AS ColorSUDictName
,H.[DC0_DictName1] AS SizeSUDictName
,I.[DC0_DictName1] AS Lvl1DictName
,J.[DC0_DictName1] AS Lvl2DictName
,K.[DC0_DictName1] AS ColorBSUName
,M.[DC0_DictName1] AS ColorDictName
,N.[DC0_DictName1] AS SizeDictName
FROM B01_MDM.PD0_Product WITH ( NOLOCK )
LEFT JOIN B01_MDM.SU0_Supplier A ON PD0_SUO_SupplierId=A.SU0_SupplierId
LEFT JOIN B01_MDM.BD0_Brand B ON PD0_BD0_BrandId=B.BD0_BrandId
LEFT JOIN M01_CONF.DC0_Dictionary C ON PD0_YearId=C.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary D ON PD0_SeasonId=D.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary E ON PD0_StandardId=E.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary F ON PD0_SecurityLevelId=F.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary G ON PD0_ColorSUId=G.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary H ON PD0_SizeSUId=H.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary I ON PD0_Lvl1=I.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary J ON PD0_Lvl2=J.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary K ON PD0_ColorSUId=K.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary M ON PD0_ColorId=M.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary N ON PD0_SizeId=N.DC0_DictId
WHERE " + strWHERE
+ @"ORDER BY [PD0_LmDt] DESC ";
ds_Return = conn.GetDataSet(strSQL);
}
else
{
ArrayList tableList = new ArrayList();
tableList.Add("B01_MDM.PD0_Product");
conn.BeginTransaction();
//开启事物
conn.TableLock(tableList);
//锁表
try
{
if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "DELETE")
{
for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++)
{
strSQL = @"UPDATE B01_MDM.PD0_Product SET PD0_Status='E3CA9B7A-8E17-4E52-B667-39689445D4A1'
WHERE PD0_ProductId='" + ds.Tables["LIST"].Rows[i]["PD0_ProductId"].ToString() + @"'
AND ISNULL(PD0_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1' ";
conn.ExcuteQuerryByTran(strSQL);
}
}
else if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "EDIT")
{
for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++)
{
DataTable dt = ds.Tables["LIST"].Clone();
dt.ImportRow(ds.Tables["LIST"].Rows[i]);
strSQL = @"SELECT * FROM B01_MDM.PD0_Product WHERE PD0_ProductId=@param0
AND ISNULL(PD0_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1' ";
DataTable dt_SR = conn.GetDataTable(strSQL, new string[1] { ds.Tables["LIST"].Rows[i]["PD0_ProductId"].ToString() });
//update
if (dt_SR.Rows.Count > 0)
{
//处理结果
if (ds.Tables["LIST"].Rows[i]["PD0_LmUser"].ToString() != "")
{
string strCode = dt_SR.Rows[0]["PD0_UptNo"].ToString();
if (strCode == "0")
{
strCode = Convert.ToInt32(strCode) + 1.ToString();
}
else
{
strCode = "0";
}
if (!dt.Columns.Contains("PD0_LmDt"))
{
dt.Columns.Add("PD0_LmDt");
}
// if (!dt.Columns.Contains("PD0_Status"))
// {
// dt.Columns.Add("PD0_Status");
// }
if (!dt.Columns.Contains("PD0_UptNo"))
{
dt.Columns.Add("PD0_UptNo");
}
// dt.Rows[0]["PD0_Status"] = '0';
dt.Rows[0]["PD0_LmDt"] = DateTime.Now;
dt.Rows[0]["PD0_UptNo"] = strCode;
}
conn.Update("B01_MDM.PD0_Product", dt, "PD0_ProductId='" + ds.Tables["LIST"].Rows[i]["PD0_ProductId"].ToString() + @"'");
}
else //add
{
// string PD0_ProductId = Guid.NewGuid().ToString();
// if (!dt.Columns.Contains("PD0_ProductId"))
// {
// dt.Columns.Add("PD0_ProductId");
// }
// if (!dt.Columns.Contains("PD0_Status"))
// {
// dt.Columns.Add("PD0_Status");
// }
if (!dt.Columns.Contains("PD0_RgDt"))
{
dt.Columns.Add("PD0_RgDt");
}
if (!dt.Columns.Contains("PD0_LmDt"))
{
dt.Columns.Add("PD0_LmDt");
}
if (!dt.Columns.Contains("PD0_UptNo"))
{
dt.Columns.Add("PD0_UptNo");
}
// dt.Rows[0]["PD0_ProductId"] = PD0_ProductId;
// dt.Rows[0]["PD0_Status"] = '0';
dt.Rows[0]["PD0_RgDt"] = DateTime.Now;
dt.Rows[0]["PD0_LmDt"] = DateTime.Now;
dt.Rows[0]["PD0_UptNo"] = '0';
conn.Insert("B01_MDM.PD0_Product", dt);
}
}
}
conn.CommitTransaction();
ds_Return = Common.Common.GetRequestDataSet(new string[] { "Result" }, new string[] { "true" });
}
catch
{
conn.RollbackTransaction();
//事物结束
throw;
}
}
return ds_Return;
}
catch
{
throw;
}
}
/// <summary>
/// 根据XML内容更新到货计划字表
/// </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("GL_ERP");
string strXML = "";
string strSQL = "";
strXML = m_request.Tables[0].Rows[0]["XML"].ToString();
//XML是前台传入
ds = Common.Common.GetDSByExcelXML(strXML);
if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "GET")
//读取ROWS到/ROWS 中的参数
{
string strWHERE = "isnull(PRAD_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'";
string strWHERE1 = "isnull(PRAD_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'";
if (ds.Tables["LIST"].Columns.Contains("Condition"))
{
if (ds.Tables["LIST"].Rows[0]["Condition"].ToString().Trim() != "")
//读取LIST之间参数
{
strWHERE += " AND (" + ds.Tables["LIST"].Rows[0]["Condition"].ToString().Trim() + ")";
}
if (ds.Tables["LIST"].Rows[0]["Condition1"].ToString().Trim() != "")
//读取LIST之间参数
{
strWHERE1 += " AND (" + ds.Tables["LIST"].Rows[0]["Condition1"].ToString().Trim() + ")";
}
}
strSQL = @"SELECT * FROM B02_Bill.PRAD_ArrivalPlanDetail WHERE " + strWHERE;
DataTable dt_SR = conn.GetDataTable(strSQL);
if (dt_SR.Rows.Count > 0)
{
strSQL = @"SELECT
[PRAD_CONO]
,[PRAD_DIVI]
,[PRAD_ArrivalPlanDetailId]
,[PRAD_PRA_ArrivalPlanId]
,[PRAD_PD0_ProductId]
,[PRAD_UnitID]
,[PRAD_PR1D_OrderDetailId]
,[PRAD_ArrivalDate]
,[PRAD_PerchaseTotal]
,[PRAD_ArrivalTotal]
,[PRAD_PlanTotal]
,[PRAD_PlanArrival]
,[PRAD_PlanArrivalAmount]
,[PRAD_TotalDiff]
,[PRAD_Status]
,[PRAD_RgUser]
,[PRAD_RgDt]
,[PRAD_LmUser]
,[PRAD_LmDt]
,[PRAD_UptNo]
FROM B02_Bill.PRAD_ArrivalPlanDetail
WHERE " + strWHERE;
}
else
{
strSQL = @"SELECT
[PRAD_CONO]
,[PRAD_DIVI]
,'' AS [PRAD_ArrivalPlanDetailId]
,[PRA_ArrivalPlanId]
,[PR1D_OrderDetailId]
,[PR1D_PD0_ProductId]
,[PR1D_UnitID]
,'' AS [PRAD_ArrivalDate]
,ISNULL([PR1D_Total],'0') AS [PR1D_Total]
,SUM(ISNULL(PRAD_PlanArrival,'0')) AS PRAD_PlanTotal
,SUM(ISNULL([PRAD_ArrivalTotal],'0')) as PRAD_ArrivalTotal
,'0' AS [PRAD_PlanArrival]
,'0' AS [PRAD_PlanArrivalAmount]
,'0' as [PRAD_TotalDiff]
,'' as [PRAD_Status]
,'' as [PRAD_RgUser]
,'' as [PRAD_RgDt]
,'' as [PRAD_LmUser]
,'' as [PRAD_LmDt]
,'0' as [PRAD_UptNo]
FROM B02_Bill.PRA_ArrivalPlan
LEFT JOIN B02_Bill.PR1D_OrderDetail ON PRA_PR1_OrderId=PR1D_PR1_OrderId
LEFT JOIN B02_Bill.PRAD_ArrivalPlanDetail ON PR1D_OrderDetailId=PRAD_PR1D_OrderDetailId
WHERE " + strWHERE1
+ @"GROUP BY [PRAD_CONO],[PRAD_DIVI] ,[PRA_ArrivalPlanId],[PR1D_PD0_ProductId]
,[PR1D_OrderDetailId],[PR1D_UnitID]
,[PR1D_Total]";
}
ds_Return = conn.GetDataSet(strSQL);
// }
}
else
{
ArrayList tableList = new ArrayList();
tableList.Add("B02_Bill.PRAD_ArrivalPlanDetail");
conn.BeginTransaction();
//开启事物
conn.TableLock(tableList);
//锁表
try
{
if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "DELETE")
{
for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++)
{
strSQL = @"UPDATE B02_Bill.PRAD_ArrivalPlanDetail SET PRAD_Status='E3CA9B7A-8E17-4E52-B667-39689445D4A1'
WHERE PRAD_PRA_ArrivalPlanId='" + ds.Tables["LIST"].Rows[i]["PRAD_PRA_ArrivalPlanId"].ToString() + @"'
AND iusnull(PRA_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1' ";
conn.ExcuteQuerryByTran(strSQL);
}
}
else if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "EDIT")
{
for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++)
{
DataTable dt = ds.Tables["LIST"].Clone();
dt.ImportRow(ds.Tables["LIST"].Rows[i]);
strSQL = @"SELECT * FROM B02_Bill.PRAD_ArrivalPlanDetail WHERE PRAD_ArrivalPlanDetailId=@param0
AND PRAD_Status<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'
";
DataTable dt_SR = conn.GetDataTable(strSQL, new string[1] { ds.Tables["LIST"].Rows[i]["PRAD_ArrivalPlanDetailId"].ToString() });
//update
if (dt_SR.Rows.Count > 0)
{
//处理结果
if (ds.Tables["LIST"].Rows[i]["PRAD_LmUser"].ToString() != "")
{
string strCode = dt_SR.Rows[0]["PRAD_UptNo"].ToString();
if (strCode == "0")
{
strCode = Convert.ToInt32(strCode) + 1.ToString();
}
else
{
strCode = "0";
}
if (!dt.Columns.Contains("PRAD_LmDt"))
{
dt.Columns.Add("PRAD_LmDt");
}
if (!dt.Columns.Contains("PRAD_UptNo"))
{
dt.Columns.Add("PRAD_UptNo");
}
//if (!dt.Columns.Contains("PRA_Status"))
//{
// dt.Columns.Add("PRA_Status");
//}
//dt.Rows[0]["PRA_Status"] = '0';
dt.Rows[0]["PRA_LmDt"] = DateTime.Now;
dt.Rows[0]["PRA_UptNo"] = strCode;
}
conn.Update("B02_Bill.PRAD_ArrivalPlanDetail", dt, "PRAD_ArrivalPlanDetailId='" + ds.Tables["LIST"].Rows[i]["PRAD_ArrivalPlanDetailId"].ToString() + @"'");
strSQL = @"UPDATE AA SET AA.PR1_PurchaseTotal=BB.PR1_PurchaseTotal,
AA.PR1_PurchaseAmount=BB.PR1_PurchaseAmount,AA.PR1_POriginalAmount=BB.PR1_POriginalAmount,
AA.PR1_LmUser='******',
AA.PR1_LmDt='" + ds.Tables["LIST"].Rows[i]["PR1D_LmDt"].ToString() + @"',
AA.PR1_UptNo=AA.PR1D_UptNo+1
FROM
B02_Bill.PR1_Order AA,
(SELECT SUM(PR1D_Total) AS PR1_PurchaseTotal,SUM(PR1D_OriginalAmount) AS PR1_PurchaseAmount,
SUM(PR1D_EstimateCost) AS PR1D_EstimateCost,SUM(PR1D_CurrencyAmount) AS PR1_POriginalAmount,PR1D_PR1_OrderId
FROM B02_Bill.PR1D_OrderDetail WHERE PR1D_PR1_OrderId='" + ds.Tables["LIST"].Rows[i]["PR1D_PR1_OrderId"].ToString() + @"'
GROUP BY PR1D_PR1_OrderId)BB
WHERE AA.PR1_OrderId=BB.PR1D_PR1_OrderId";
conn.ExcuteQuerryByTran(strSQL);
}
else //add
{
DataTable dtTemp = conn.GetDataTableInTrans(
@" SELECT " + ds.Tables["LIST"].Rows[i]["PRAD_PerchaseTotal"].ToString() + @" -
" + ds.Tables["LIST"].Rows[i]["PRAD_ArrivalTotal"].ToString() + @" -
" + ds.Tables["LIST"].Rows[i]["PRAD_PlanArrival"].ToString() + @" as PRAD_TotalDiff
");
string PRAD_TotalDiff = dtTemp.Rows[0]["PRAD_TotalDiff"].ToString();
DataTable dtAmot = conn.GetDataTableInTrans(
@" SELECT ISNULL(PR1D_POriginalPrice * " + ds.Tables["LIST"].Rows[i]["PRAD_PlanArrival"].ToString() + @",'0')
as PRAD_PlanArrivalAmount
FROM B02_Bill.PR1D_OrderDetail WHERE PR1D_OrderDetailId='" + ds.Tables["LIST"].Rows[i]["PRAD_PR1D_OrderDetailId"].ToString() + @"'
AND PR1D_PD0_ProductId='" + ds.Tables["LIST"].Rows[i]["PRAD_PD0_ProductId"].ToString() + @"'
");
string PRAD_PlanArrivalAmount = dtAmot.Rows[0]["PRAD_PlanArrivalAmount"].ToString();
string PRAD_ArrivalPlanDetailId = Guid.NewGuid().ToString();
if (!dt.Columns.Contains("PRAD_ArrivalPlanDetailId"))
{
dt.Columns.Add("PRAD_ArrivalPlanDetailId");
}
if (!dt.Columns.Contains("PRAD_RgDt"))
{
dt.Columns.Add("PRAD_RgDt");
}
if (!dt.Columns.Contains("PRAD_LmDt"))
{
dt.Columns.Add("PRAD_LmDt");
}
if (!dt.Columns.Contains("PRAD_UptNo"))
{
dt.Columns.Add("PRAD_UptNo");
}
if (!dt.Columns.Contains("PRAD_TotalDiff"))
{
dt.Columns.Add("PRAD_TotalDiff");
}
if (!dt.Columns.Contains("PRAD_PlanArrivalAmount"))
{
dt.Columns.Add("PRAD_PlanArrivalAmount");
}
dt.Rows[0]["PRAD_ArrivalPlanDetailId"] = PRAD_ArrivalPlanDetailId;
dt.Rows[0]["PRAD_RgDt"] = DateTime.Now;
dt.Rows[0]["PRAD_LmDt"] = DateTime.Now;
dt.Rows[0]["PRAD_TotalDiff"] = PRAD_TotalDiff;
dt.Rows[0]["PRAD_PlanArrivalAmount"] = PRAD_PlanArrivalAmount;
conn.Insert("B02_Bill.PRAD_ArrivalPlanDetail", dt);
strSQL = @"UPDATE AA SET AA.PRA_PlanTotal=BB.PRAD_PlanTotal,
AA.PRA_PlanAmount=BB.PRAD_PlanArrivalAmount,
AA.PRA_LmUser='******',
AA.PRA_LmDt=CONVERT(VARCHAR(100),GETDATE(),121),
AA.PRA_TotalDiff=AA.PRA_PerchaseTotal-AA.PRA_ArrivalTotal-BB.PRA_PlanTotal,
AA.PRA_AmountDiff=AA.PRA_PerchaseAmount-AA.PRA_ArrivalAmount-BB.PRA_PlanAmount,
AA.PRA_UptNo=AA.PRA_UptNo+1
FROM
PRA_ArrivalPlan AA,
(SELECT PRAD_PRA_ArrivalPlanId,SUM(ISNULL(PRAD_PlanArrival,'0')) AS PRAD_PlanTotal,
SUM(ISNULL(PRAD_PlanArrivalAmount,'0')) AS PRAD_PlanArrivalAmount
FROM B02_Bill.PRAD_ArrivalPlanDetail
WHERE PRAD_PRA_ArrivalPlanId=' " + ds.Tables["LIST"].Rows[i]["PRAD_PRA_ArrivalPlanId"].ToString() + @" '
GROUP BY PRAD_PRA_ArrivalPlanId)BB
WHERE AA.PRA_ArrivalPlanId=BB.PRAD_PRA_ArrivalPlanId";
conn.ExcuteQuerryByTran(strSQL);
// strSQL = @"UPDATE AA SET AA.PR1_PurchaseTotal=BB.PR1_PurchaseTotal,
// AA.PR1_PurchaseAmount=BB.PR1_PurchaseAmount,AA.PR1_POriginalAmount=BB.PR1_POriginalAmount,
// AA.PR1_LmUser='******',
// AA.PR1_LmDt='" + ds.Tables["LIST"].Rows[i]["PR1D_LmDt"].ToString() + @"',
// AA.PR1_UptNo=AA.PR1D_UptNo+1
// FROM
// B02_Bill.PR1_Order AA,
// (SELECT SUM(PR1D_Total) AS PR1_PurchaseTotal,SUM(PR1D_OriginalAmount) AS PR1_PurchaseAmount,
// SUM(PR1D_EstimateCost) AS PR1D_EstimateCost,SUM(PR1D_CurrencyAmount) AS PR1_POriginalAmount,PR1D_PR1_OrderId
// FROM B02_Bill.PR1D_OrderDetail WHERE PR1D_PR1_OrderId='" + ds.Tables["LIST"].Rows[i]["PR1D_PR1_OrderId"].ToString() + @"'
// GROUP BY PR1D_PR1_OrderId)BB
// WHERE AA.PR1_OrderId=BB.PR1D_PR1_OrderId";
// conn.ExcuteQuerryByTran(strSQL);
}
}
}
conn.CommitTransaction();
ds_Return = Common.Common.GetRequestDataSet(new string[] { "Result" }, new string[] { "true" });
}
catch
{
conn.RollbackTransaction();
//事物结束
throw;
}
}
return ds_Return;
}
catch
{
throw;
}
}
/// <summary>
/// 新建或修改部门记录
/// </summary>
/// <returns></returns>
public DataSet SetDeptInfo(Dbconn conn, String strType)
{
String strCode = "";
DataSet ds_Return = new DataSet();
string strActor = m_request.Tables[0].Rows[0]["actor"].ToString();
string strXML = m_request.Tables[0].Rows[0]["XML"].ToString();
DataSet dsXML = Common.Common.GetDSByExcelXML(strXML);
String strOPType = dsXML.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper();
String strID = dsXML.Tables["LIST"].Rows[0]["oz1_id"].ToString();
DataTable dt_List = dsXML.Tables["LIST"];
if (strID == "")
{
throw new Exception("数据异常，缺少主键，请联系管理员！");
}
bool bResult = false;
conn.BeginTransaction();
if (strType == "NewDept") //新建部门
{
bResult = conn.Insert("[F01_CERT].[OZ1_Organization] ", dt_List);
}
else if (strType == "UpdDept") //修改部门
{
bResult = conn.Update("[F01_CERT].[OZ1_Organization] ", dt_List, "oz1_id='" + strID + "'");
}
else if (strType == "UpdDept1") //修改部门
{
//添加更新主键，按照GUID更新
ArrayList ListKey = new ArrayList();
ListKey.Add("oz1_id");
bResult = conn.Update("F01_CERT.OZ1_Organization", dt_List, ListKey);
}
else if (strType == "DelDept") //删除部门
{
for (int k = 0; k < dt_List.Rows.Count; k++)
{
string strID1 = dt_List.Rows[k]["oz1_id"].ToString();
bResult = conn.Delete("F01_CERT.OZ1_Organization", "oz1_id=" + "'" + strID1 + "'");
}
}
conn.CommitTransaction();
if (bResult)
{
dt_EditResult.Rows.Add(new object[] { true, strCode });
}
else
{
dt_EditResult.Rows.Add(new object[] { false, "保存失败！" });
}
ds_Return.Tables.Add(dt_EditResult);
return ds_Return;
}
/// <summary>
/// 根据XML内容更新品牌
/// </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("GL_ERP");
string strXML = "";
string strSQL = "";
strXML = m_request.Tables[0].Rows[0]["XML"].ToString();
//XML是前台传入
ds = Common.Common.GetDSByExcelXML(strXML);
if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "GET")
//读取ROWS到/ROWS 中的参数
{
string strWHERE = "ISNULL(BD0_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'";
if (ds.Tables["LIST"].Columns.Contains("Condition"))
{
if (ds.Tables["LIST"].Rows[0]["Condition"].ToString().Trim() != "")
//读取LIST之间参数
{
strWHERE += " AND (" + ds.Tables["LIST"].Rows[0]["Condition"].ToString().Trim() + ")";
}
}
strSQL = @" SELECT
[BD0_CONO]
,[BD0_DIVI]
,[BD0_BrandId]
,[BD0_BrandCode]
,[BD0_SU0_SupplierId]
,[BD0_BrandName]
,[BD0_CountryId]
,[BD0_StyleId]
,[BD0_CusGrpId]
,[BD0_Status]
,[BD0_RgUser]
,[BD0_RgDt]
,[BD0_LmUser]
,[BD0_LmDt]
,[BD0_UptNo]
,A.[SU0_SupplierCode]
,B.[DC0_DictName1] AS CountryDictName
,C.[DC0_DictName1] AS StyleDictName
,D.[DC0_DictName1] AS CusGrpDictName
FROM B01_MDM.BD0_Brand WITH ( NOLOCK )
LEFT JOIN B01_MDM.SU0_Supplier A ON BD0_SU0_SupplierId=A.SU0_SupplierId
LEFT JOIN M01_CONF.DC0_Dictionary B ON BD0_CountryId=B.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary C ON BD0_StyleId=C.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary D ON BD0_CusGrpId=D.DC0_DictId
WHERE " + strWHERE
+ @"ORDER BY [BD0_LmDt] DESC ";
ds_Return = conn.GetDataSet(strSQL);
}
else
{
ArrayList tableList = new ArrayList();
tableList.Add("B01_MDM.BD0_Brand");
conn.BeginTransaction();
//开启事物
conn.TableLock(tableList);
//锁表
try
{
if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "DELETE")
{
for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++)
{
strSQL = @"UPDATE B01_MDM.BD0_Brand SET BD0_Status=1
WHERE BD0_BrandId='" + ds.Tables["LIST"].Rows[i]["BD0_BrandId"].ToString() + @"'
AND ISNULL(BD0_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1' ";
conn.ExcuteQuerryByTran(strSQL);
}
}
else if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "EDIT")
{
for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++)
{
DataTable dt = ds.Tables["LIST"].Clone();
dt.ImportRow(ds.Tables["LIST"].Rows[i]);
strSQL = @"SELECT * FROM B01_MDM.BD0_Brand WHERE BD0_BrandId=@param0
AND ISNULL(BD0_Status,'') <>'E3CA9B7A-8E17-4E52-B667-39689445D4A1' ";
DataTable dt_SR = conn.GetDataTable(strSQL, new string[1] { ds.Tables["LIST"].Rows[i]["BD0_BrandId"].ToString() });
//update
if (dt_SR.Rows.Count > 0)
{
//处理结果
if (ds.Tables["LIST"].Rows[i]["BD0_LmUser"].ToString() != "")
{
string strCode = dt_SR.Rows[0]["BD0_UptNo"].ToString();
if (strCode == "0")
{
strCode = Convert.ToInt32(strCode) + 1.ToString();
}
else
{
strCode = "0";
}
if (!dt.Columns.Contains("BD0_LmDt"))
{
dt.Columns.Add("BD0_LmDt");
}
// if (!dt.Columns.Contains("BD0_Status"))
// {
// dt.Columns.Add("BD0_Status");
// }
if (!dt.Columns.Contains("BD0_UptNo"))
{
dt.Columns.Add("BD0_UptNo");
}
// dt.Rows[0]["BD0_Status"] = '0';
dt.Rows[0]["BD0_LmDt"] = DateTime.Now;
dt.Rows[0]["BD0_UptNo"] = strCode;
}
conn.Update("B01_MDM.BD0_Brand", dt, "BD0_BrandId='" + ds.Tables["LIST"].Rows[i]["BD0_BrandId"].ToString() + @"'");
}
else //add
{
strSQL = @"SELECT BD0_BrandCode FROM B01_MDM.BD0_Brand WHERE BD0_BrandCode=@param0
";
DataTable dt_SU = conn.GetDataTable(strSQL, new string[1] { ds.Tables["LIST"].Rows[i]["BD0_BrandCode"].ToString()});
if (dt_SU.Rows.Count > 0)
{
string BD0_BrandCode = dt_SU.Rows[0]["BD0_BrandCode"].ToString();
// ITSM_Common.SendAMMessage("你输入的品牌编号已存在，最近添加的品牌编号是" + BD0_BrandCode);
ds_Return = Common.Common.GetRequestDataSet(new string[] { "Result" }, new string[] { "你输入的品牌编号已存在，最近添加的品牌编号是" + BD0_BrandCode });
conn.RollbackTransaction();
return ds_Return;
}
else
{
string BD0_BrandId = Guid.NewGuid().ToString();
if (!dt.Columns.Contains("BD0_BrandId"))
{
dt.Columns.Add("BD0_BrandId");
}
// if (!dt.Columns.Contains("BD0_Status"))
// {
// dt.Columns.Add("BD0_Status");
// }
if (!dt.Columns.Contains("BD0_RgDt"))
{
dt.Columns.Add("BD0_RgDt");
}
if (!dt.Columns.Contains("BD0_LmDt"))
{
dt.Columns.Add("BD0_LmDt");
}
if (!dt.Columns.Contains("BD0_UptNo"))
{
dt.Columns.Add("BD0_UptNo");
}
dt.Rows[0]["BD0_BrandId"] = BD0_BrandId;
// dt.Rows[0]["BD0_Status"] = '0';
dt.Rows[0]["BD0_RgDt"] = DateTime.Now;
dt.Rows[0]["BD0_LmDt"] = DateTime.Now;
dt.Rows[0]["BD0_UptNo"] = '0';
conn.Insert("B01_MDM.BD0_Brand", dt);
}
}
}
}
conn.CommitTransaction();
ds_Return = Common.Common.GetRequestDataSet(new string[] { "Result" }, new string[] { "true" });
}
catch
{
conn.RollbackTransaction();
//事物结束
throw;
}
}
return ds_Return;
}
catch
{
throw;
}
}