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