private void BindGrid()
{
string ConditionStr = " PDT_Product.ID NOT IN (SELECT Product FROM MCS_PUB.dbo.PDT_ProductExtInfo WHERE Supplier=" + Session["OwnerClient"].ToString() + ")";
if (tr_List.SelectedValue != "" && tr_List.SelectedValue != "1")
{
string _categoryids = "";
DataTable dt = TreeTableBLL.GetAllChildByNodes("MCS_Pub.dbo.PDT_Category", "ID", "SuperID", tr_List.SelectedValue);
for (int i = 0; i < dt.Rows.Count; i++)
{
_categoryids += dt.Rows[i]["ID"].ToString() + ",";
}
_categoryids += tr_List.SelectedValue;
ConditionStr += " AND PDT_Product.Category IN (" + _categoryids + ")";
}
if (tbx_SearchKey.Text != "")
{
ConditionStr += " AND (";
ConditionStr += " PDT_Product.FullName LIKE '%" + tbx_SearchKey.Text + "%' ";
ConditionStr += "OR PDT_Product.ShortName LIKE '%" + tbx_SearchKey.Text + "%' ";
ConditionStr += "OR PDT_Product.BarCode LIKE '%" + tbx_SearchKey.Text + "%' ";
ConditionStr += "OR PDT_Product.FactoryName LIKE '%" + tbx_SearchKey.Text + "%' ";
ConditionStr += "OR PDT_Product.FactoryCode LIKE '%" + tbx_SearchKey.Text + "%' ";
ConditionStr += ")";
}
gv_List.ConditionString = ConditionStr;
gv_List.BindGrid();
}
protected void bt_Add_Click(object sender, EventArgs e)
{
if (tr_Position.SelectValue != "0")
{
int position = int.Parse(tr_Position.SelectValue);
int beginday = int.Parse(tbx_BeginDay.Text);
int endday = int.Parse(tbx_EndDay.Text);
EWF_Flow_InitPositionBLL bll = new EWF_Flow_InitPositionBLL();
bll.Model.App = (Guid)ViewState["AppID"];
bll.Model.Position = position;
bll.Model.BeginDay = beginday;
bll.Model.EndDay = endday;
bll.Add();
if (cb_IncludeChild.Checked)
{
DataTable dt = TreeTableBLL.GetAllChildByNodes("MCS_SYS.dbo.Org_Position", "ID", "SuperID", tr_Position.SelectValue);
foreach (DataRow dr in dt.Rows)
{
bll.Model.ID = Guid.NewGuid();
bll.Model.Position = (int)dr["ID"];
bll.Add();
}
}
}
BindGrid();
}
private void BindGrid()
{
if (ViewState["Details"] != null)
{
ListTable <PBM_DeliveryDetail> Details = (ListTable <PBM_DeliveryDetail>)ViewState["Details"];
int category = 1;
int.TryParse(tr_Category.SelectValue, out category);
if (category > 1)
{
DataTable dt = TreeTableBLL.GetAllChildByNodes("MCS_PUB.dbo.PDT_Category", "ID", "SuperID", category.ToString());
List <int> categorys = new List <int>();
foreach (DataRow dr in dt.Rows)
{
categorys.Add((int)dr["ID"]);
}
categorys.Add(category);
gv_List.BindGrid(Details.GetListItem().Where(p => categorys.Contains(new PDT_ProductBLL(p.Product).GetProductExtInfo((int)Session["OwnerClient"]).Category)).ToList());
}
else
{
gv_List.BindGrid(Details.GetListItem());
}
if (Details.GetListItem().Count == 0)
{
bt_Confirm.Visible = false;
}
}
}
protected void tr_OfficialCity_Selected(object sender, MCSControls.MCSWebControls.SelectedEventArgs e)
{
cbl_OfficialList.Items.Clear();
DataTable dt = TreeTableBLL.GetAllChildByNodes("MCS_SYS.dbo.Addr_OfficialCity", "ID", "SuperID", tr_OfficialCity.SelectValue);
for (int i = 0; i < dt.Rows.Count; i++)
{
Addr_OfficialCityBLL city = new Addr_OfficialCityBLL((int)dt.Rows[i]["ID"]);
if (city.Model.Level == 3)
{
string fullname = TreeTableBLL.GetFullPathName("MCS_SYS.dbo.Addr_OfficialCity", city.Model.ID);
ListItem item = new ListItem(fullname, city.Model.ID.ToString());
IList <Addr_OfficialCityInOrganizeCity> organizecitys = Addr_OfficialCityInOrganizeCityBLL.GetModelList("OfficialCity=" + city.Model.ID.ToString());
if (organizecitys.Count > 0)
{
Addr_OrganizeCity organizecity = new Addr_OrganizeCityBLL(organizecitys[0].OrganizeCity).Model;
if (organizecity != null)
{
item.Text += "; 已归属于片区:【" + organizecity.Name + "】";
}
item.Enabled = false;
}
cbl_OfficialList.Items.Add(item);
}
}
}
protected void gv_ListDetail_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
int id = (int)gv_ListDetail.DataKeys[e.Row.RowIndex][0];
UC_GridView gv_Detail = (UC_GridView)e.Row.FindControl("gv_Detail");
if (gv_Detail != null)
{
IList <FNA_FeeWriteOffDetail> lists = new FNA_FeeWriteOffBLL(id).Items;
//费用类型
int accounttile = 0;
int.TryParse(tr_AccountTitle.SelectValue, out accounttile);
if (ddl_FeeType.SelectedValue != "0" && !(accounttile > 1))
{
IList <AC_AccountTitleInFeeType> titles = AC_AccountTitleInFeeTypeBLL.GetModelList("FeeType=" + ddl_FeeType.SelectedValue);
int[] ids = new int[titles.Count];
for (int i = 0; i < titles.Count; i++)
{
ids[i] = titles[i].AccountTitle;
}
lists = lists.Where(p => ids.Contains(p.AccountTitle)).ToList();
}
//费用科目
if (accounttile > 1)
{
DataTable dt = TreeTableBLL.GetAllChildByNodes("MCS_PUB.dbo.AC_AccountTitle", "ID", "SuperID", accounttile.ToString());
int[] ids = new int[dt.Rows.Count + 1];
for (int i = 0; i < dt.Rows.Count; i++)
{
ids[i] = (int)dt.Rows[i]["ID"];
}
ids[ids.Length - 1] = accounttile;
lists = lists.Where(p => ids.Contains(p.AccountTitle)).ToList();
}
//核销金额判断
decimal _cost = 0;
decimal.TryParse(tbx_WriteOffCost.Text, out _cost);
if (_cost != 0)
{
if (ddl_WriteOffCostOP.SelectedValue == ">")
{
lists = lists.Where(p => p.WriteOffCost + p.AdjustCost > _cost).ToList();
}
else if (ddl_WriteOffCostOP.SelectedValue == "<")
{
lists = lists.Where(p => p.WriteOffCost + p.AdjustCost < _cost).ToList();
}
}
gv_Detail.BindGrid(lists);
}
}
}
private void BindKBGridView(UC_GridView gv_KB, int Catalog)
{
DataTable dt = TreeTableBLL.GetAllChildByNodes("MCS_OA.dbo.KB_Catalog", "ID", "SuperID", Catalog.ToString());
string catalogs = Catalog.ToString();
foreach (DataRow dr in dt.Rows)
{
catalogs += "," + dr["ID"].ToString();
}
string condition = "ID IN(Select TOP 3 ID FROM MCS_OA.dbo.KB_Article WHERE Catalog IN (" + catalogs + ") AND IsDelete = 'N' AND HasApproved = 'Y' ORDER BY ApproveTime DESC)";
gv_KB.BindGrid(KB_ArticleBLL.GetModelList(condition));
gv_KB.Attributes["CatalogID"] = Catalog.ToString();
}
public string GetAllChildPosition()
{
DataTable dt = TreeTableBLL.GetAllChildByNodes("MCS_OA.dbo.KB_Catalog", "ID", "SuperID", _m.ID.ToString());
string ids = "";
for (int i = 0; i < dt.Rows.Count; i++)
{
if (ids != "")
{
ids += ",";
}
ids += dt.Rows[i]["ID"];
}
return(ids);
}
private void BindGrid()
{
string ConditionStr = "INV_Inventory.Quantity <> 0 ";
if (ddl_WareHouse.SelectedValue != "0")
{
ConditionStr += " AND Inv_Inventory.WareHouse = " + ddl_WareHouse.SelectedValue;
}
else
{
ConditionStr += " AND INV_Inventory.WareHouse IN (SELECT ID FROM MCS_CM.dbo.CM_WareHouse WHERE Client=" + Session["OwnerClient"].ToString() + ")";
}
if (tr_List.SelectedValue != "" && tr_List.SelectedValue != "1")
{
string _categoryids = "";
DataTable dt = TreeTableBLL.GetAllChildByNodes("MCS_Pub.dbo.PDT_Category", "ID", "SuperID", tr_List.SelectedValue);
for (int i = 0; i < dt.Rows.Count; i++)
{
_categoryids += dt.Rows[i]["ID"].ToString() + ",";
}
_categoryids += tr_List.SelectedValue;
ConditionStr += " AND INV_Inventory.Product IN (SELECT ID FROM MCS_PUB.dbo.PDT_Product WHERE Category IN (" + _categoryids + "))";
}
if (tbx_SearchKey.Text != "")
{
ConditionStr += " AND (INV_Inventory.Product IN (SELECT ID FROM MCS_PUB.dbo.PDT_Product WHERE ";
ConditionStr += " FullName LIKE '%" + tbx_SearchKey.Text + "%' ";
ConditionStr += "OR ShortName LIKE '%" + tbx_SearchKey.Text + "%' ";
ConditionStr += "OR BarCode LIKE '%" + tbx_SearchKey.Text + "%' ";
ConditionStr += "OR FactoryName LIKE '%" + tbx_SearchKey.Text + "%' ";
ConditionStr += "OR FactoryCode LIKE '%" + tbx_SearchKey.Text + "%' ";
ConditionStr += ") )";
}
IList <INV_Inventory> lists = INV_InventoryBLL.GetModelList(ConditionStr);
ViewState["TotalInventory"] = lists.Sum(p => p.Price * p.Quantity);
gv_List.BindGrid(lists);
gv_List.Columns[gv_List.Columns.Count - 1].Visible = ddl_WareHouse.SelectedValue != "0";
bt_Adjust.Enabled = ddl_WareHouse.SelectedValue != "0";
}
protected void bt_AddAll_Click(object sender, EventArgs e)
{
string ConditionStr = " PDT_Product.ID NOT IN (SELECT Product FROM MCS_PUB.dbo.PDT_ProductExtInfo WHERE Supplier=" + Session["OwnerClient"].ToString() + ")";
if (tr_List.SelectedValue != "" && tr_List.SelectedValue != "1")
{
string _categoryids = "";
DataTable dt = TreeTableBLL.GetAllChildByNodes("MCS_Pub.dbo.PDT_Category", "ID", "SuperID", tr_List.SelectedValue);
for (int i = 0; i < dt.Rows.Count; i++)
{
_categoryids += dt.Rows[i]["ID"].ToString() + ",";
}
_categoryids += tr_List.SelectedValue;
ConditionStr += " AND PDT_Product.Category IN (" + _categoryids + ")";
}
int count = 0;
IList <PDT_Product> product = PDT_ProductBLL.GetModelList(ConditionStr);
foreach (PDT_Product p in product)
{
int id = p.ID;
PDT_ProductBLL bll = new PDT_ProductBLL(id);
PDT_ProductExtInfo extinfo = new PDT_ProductExtInfo();
extinfo.Code = bll.Model.FactoryCode == "" ? bll.Model.Code : bll.Model.FactoryCode;
extinfo.Supplier = (int)Session["OwnerClient"];
bll.SetProductExtInfo(extinfo);
count++;
}
if (count > 0)
{
MessageBox.Show(this, string.Format("成功将{0}个商品加入经营范围!", count));
BindGrid();
}
else
{
MessageBox.Show(this, "请选择要加入经营产商品!");
}
}
private void BindGrid()
{
IList <Right_Assign> list;
if (tr_Module.SelectedValue != "")
{
DataTable dt = TreeTableBLL.GetAllChildByNodes("MCS_SYS.dbo.Right_Module", "ID", "SuperID", tr_Module.SelectedValue.Substring(1));
string ids = tr_Module.SelectedValue.Substring(1) + ",";
foreach (DataRow row in dt.Rows)
{
ids += row[0].ToString() + ",";
}
ids = ids.Substring(0, ids.Length - 1);
list = Right_Assign_BLL.GetModelList("RoleName='" + tr_Role.SelectedValue + "' AND Module in (" + ids + ")");
}
else
{
list = (IList <Right_Assign>)ViewState["RightAssign"];
}
gv_list.TotalRecordCount = list.Count;
gv_list.PageIndex = (int)ViewState["PageIndex"];
gv_list.BindGrid <Right_Assign>(list);
}
protected void btn_addsearch_Click(object sender, EventArgs e)
{
if (ddl_Field.SelectedValue == "0")
{
return;
}
//添加条件
ListItem lt = new ListItem();
#region 条件选择
UD_TableList table = new UD_TableListBLL(new Guid(ddl_TableName.SelectedValue), true).Model;
if (table == null)
{
return;
}
UD_ModelFields modelfield = new UD_ModelFieldsBLL(new Guid(ddl_Field.SelectedValue), true).Model;
if (modelfield == null)
{
return;
}
//字段全称
string fielddisplayname = table.DisplayName + "." + modelfield.DisplayName;
string fieldfullname = "";
if (modelfield.Flag == "Y")
{
fieldfullname = table.Name + "." + modelfield.FieldName; //实体字段
}
else
{
fieldfullname = "MCS_SYS.dbo.UF_Spilt(" + table.Name + ".ExtPropertys,'|'," + modelfield.Position.ToString() + ")";
}
if (rbl_ValueFrom.SelectedValue == "M")
{
#region 从手工设定中取值
switch (modelfield.RelationType)
{
case 1:
#region 字典关联
if (cbl_SearchValue.SelectedIndex != -1)
{
lt.Text = fielddisplayname + " 包含 ( ";
lt.Value = fieldfullname + " IN ( ";
foreach (ListItem item in cbl_SearchValue.Items)
{
if (item.Selected)
{
lt.Text += item.Text + ",";
lt.Value += "'" + item.Value + "',";
}
}
lt.Text = lt.Text.Substring(0, lt.Text.Length - 1);
lt.Value = lt.Value.Substring(0, lt.Value.Length - 1);
lt.Text += ")";
lt.Value += ")";
}
break;
#endregion
case 2:
#region 实体表关联
lt.Text = fielddisplayname + " 为 ";
lt.Value = fieldfullname + " = ";
if (!string.IsNullOrEmpty(modelfield.SearchPageURL))
{
//通过查询控件查询
lt.Text += MCSSelectControl1.SelectText;
lt.Value += "'" + MCSSelectControl1.SelectValue + "'";
}
else if (new UD_TableListBLL(modelfield.RelationTableName).Model.TreeFlag == "Y")
{
#region 通过树形结构查询
if (modelfield.RelationTableName == "MCS_SYS.dbo.Addr_OrganizeCity")
{
if (ddl_TreeLevel.SelectedValue == "0")
{
lt.Text += MCSTreeControl1.SelectText;
lt.Value = fieldfullname + " = " + MCSTreeControl1.SelectValue;
}
else if (ddl_TreeLevel.SelectedValue == "100")
{
//所属于
lt.Text = fielddisplayname + " " + ddl_TreeLevel.SelectedItem.Text + " " + MCSTreeControl1.SelectText;
lt.Value = "MCS_SYS.dbo.UF_IsChildOrganizeCity(" + MCSTreeControl1.SelectValue + "," + fieldfullname + ") = 0";
}
else
{
//指定级别上级
lt.Text = fielddisplayname + " 的 " + ddl_TreeLevel.SelectedItem.Text + " 为 " + MCSTreeControl1.SelectText;
lt.Value = "MCS_SYS.dbo.UF_GetSuperOrganizeCityByLevel02(" + fieldfullname + "," + ddl_TreeLevel.SelectedValue + ") = " + MCSTreeControl1.SelectValue;
}
}
else if (modelfield.RelationTableName == "MCS_SYS.dbo.Addr_OfficialCity")
{
if (ddl_TreeLevel.SelectedValue == "0")
{
lt.Text += MCSTreeControl1.SelectText;
lt.Value = fieldfullname + " = " + MCSTreeControl1.SelectValue;
}
else if (ddl_TreeLevel.SelectedValue == "100")
{
//所属于
lt.Text = fielddisplayname + " " + ddl_TreeLevel.SelectedItem.Text + " " + MCSTreeControl1.SelectText;
lt.Value = "MCS_SYS.dbo.UF_IsChildOfficialCityCity(" + MCSTreeControl1.SelectValue + "," + fieldfullname + ") = 0";
}
else
{
//指定级别上级
lt.Text = fielddisplayname + " 的 " + ddl_TreeLevel.SelectedItem.Text + " 为 " + MCSTreeControl1.SelectText;
lt.Value = "MCS_SYS.dbo.UF_GetSuperOfficialCityByLevel02(" + fieldfullname + "," + ddl_TreeLevel.SelectedValue + ") = " + MCSTreeControl1.SelectValue;
}
}
else
{
lt.Text += MCSTreeControl1.SelectText;
lt.Value = fieldfullname + " IN (";
DataTable dt = TreeTableBLL.GetAllChildByNodes(modelfield.RelationTableName, "ID", "SuperID", MCSTreeControl1.SelectValue);
foreach (DataRow dr in dt.Rows)
{
lt.Value += "'" + dr[0].ToString() + "',";
}
lt.Value += "'" + MCSTreeControl1.SelectValue + "')";
}
#endregion
}
else
{
#region 其他关联方式的实体表
if (cbl_SearchValue.SelectedIndex != -1)
{
lt.Text = fielddisplayname + " 包含 ( ";
lt.Value = fieldfullname + " IN ( ";
foreach (ListItem item in cbl_SearchValue.Items)
{
if (item.Selected)
{
lt.Text += item.Text + ",";
lt.Value += "'" + item.Value + "',";
}
}
lt.Text = lt.Text.Substring(0, lt.Text.Length - 1);
lt.Value = lt.Value.Substring(0, lt.Value.Length - 1);
lt.Text += ")";
lt.Value += ")";
}
#endregion
}
break;
#endregion
default:
#region 非关联字段
lt.Text = fielddisplayname + " " + ddl_op.SelectedItem.Text + " (" + tbx_searchvalue.Text + ")";
lt.Value = fieldfullname + " = ";
if (ddl_op.SelectedValue != "like")
{
if (tbx_searchvalue.Text.StartsWith("@"))
{
lt.Value = fieldfullname + " " + ddl_op.SelectedItem.Value + tbx_searchvalue.Text;
}
else
{
lt.Value = fieldfullname + " " + ddl_op.SelectedItem.Value + " '" + tbx_searchvalue.Text + "'";
}
}
else
{
lt.Value = fieldfullname + " " + ddl_op.SelectedItem.Value + " '%" + tbx_searchvalue.Text + "%'";
}
break;
#endregion
}
#endregion
}
else
{
#region 从参数中取值
switch (modelfield.RelationType)
{
case 1:
#region 字典关联
lt.Text = fielddisplayname + " = " + ddl_Param.SelectedValue;
lt.Value = fieldfullname + " = " + ddl_Param.SelectedValue;
#endregion
break;
case 2:
#region 实体表关联
lt.Text = fielddisplayname + " 为 ";
lt.Value = fieldfullname + " = ";
if (ddl_TreeLevel.Visible)
{
#region 管理片区及行政城市选项
if (ddl_TreeLevel.SelectedValue == "0")
{
//当前级
lt.Text += ddl_Param.SelectedValue;
lt.Value = fieldfullname + " = " + ddl_Param.SelectedValue;
}
else if (ddl_TreeLevel.SelectedValue == "100")
{
//所属于
lt.Text = fielddisplayname + " " + ddl_TreeLevel.SelectedItem.Text + " " + ddl_Param.SelectedValue;
if (modelfield.RelationTableName == "MCS_SYS.dbo.Addr_OrganizeCity")
{
lt.Value = "MCS_SYS.dbo.UF_IsChildOrganizeCity(" + ddl_Param.SelectedValue + "," + fieldfullname + ") = 0";
}
else if (modelfield.RelationTableName == "MCS_SYS.dbo.Addr_OfficialCity")
{
lt.Value = "MCS_SYS.dbo.UF_IsChildOfficialCityCity(" + ddl_Param.SelectedValue + "," + fieldfullname + ") = 0";
}
}
else
{
lt.Text = fielddisplayname + " 的 " + ddl_TreeLevel.SelectedItem.Text + " 为 " + ddl_Param.SelectedValue;
//指定级别上级
if (modelfield.RelationTableName == "MCS_SYS.dbo.Addr_OrganizeCity")
{
lt.Value = "MCS_SYS.dbo.UF_GetSuperOrganizeCityByLevel02(" + fieldfullname + "," + ddl_TreeLevel.SelectedValue + ") = " + ddl_Param.SelectedValue;
}
else if (modelfield.RelationTableName == "MCS_SYS.dbo.Addr_OfficialCity")
{
lt.Value = "MCS_SYS.dbo.UF_GetSuperOfficialCityByLevel02(" + fieldfullname + "," + ddl_TreeLevel.SelectedValue + ") = " + ddl_Param.SelectedValue;
}
}
#endregion
}
else
{
lt.Text += ddl_Param.SelectedValue;
lt.Value = fieldfullname + " = " + ddl_Param.SelectedValue;
}
break;
#endregion
default:
#region 非关联字段
lt.Text = fielddisplayname + " " + ddl_op.SelectedItem.Text + " (" + ddl_Param.SelectedValue + ")";
lt.Value = fieldfullname + " = ";
if (ddl_op.SelectedValue != "like")
{
lt.Value = fieldfullname + " " + ddl_op.SelectedItem.Value + ddl_Param.SelectedValue;
}
else
{
lt.Value = fieldfullname + " LIKE '%'+" + ddl_Param.SelectedValue + "+'%'";
}
break;
#endregion
}
#endregion
}
#endregion
if (!string.IsNullOrEmpty(lt.Text))
{
lbx_search.Items.Add(lt);
}
}
private void BindGrid()
{
int category = 0;
int.TryParse(tr_Category.SelectValue, out category);
if (MCSTabControl1.SelectedIndex == 0)
{
string condition = " 1 = 1 ";
#region 获取产品分类
if (category > 1)
{
condition += " AND PDT_StandardPrice_Detail.Product IN (SELECT Product FROM MCS_PUB.dbo.PDT_ProductExtInfo WHERE Supplier=" + Session["OwnerClient"].ToString();
string _categoryids = "";
DataTable dt = TreeTableBLL.GetAllChildByNodes("MCS_Pub.dbo.PDT_Category", "ID", "SuperID", tr_Category.SelectValue);
for (int i = 0; i < dt.Rows.Count; i++)
{
_categoryids += dt.Rows[i]["ID"].ToString() + ",";
}
_categoryids += tr_Category.SelectValue;
condition += " AND PDT_ProductExtInfo.Category IN (" + _categoryids + ")";
condition += ")";
}
#endregion
if (tbx_ProductText.Text != "")
{
condition += "AND PDT_StandardPrice_Detail.Product IN (SELECT ID FROM MCS_PUB.dbo.PDT_Product WHERE ";
condition += " (FullName like '%" + tbx_ProductText.Text + "%' OR ShortName like '%" + tbx_ProductText.Text + "%' OR FactoryCode like '%" + tbx_ProductText.Text +
"%' OR PDT_Product.ID IN (SELECT Product FROM MCS_PUB.dbo.PDT_ProductExtInfo WHERE Supplier=" + Session["OwnerClient"].ToString() + " AND PDT_ProductExtInfo.Code LIKE '%" + tbx_ProductText.Text + "%'))";
condition += ")";
}
IList <PDT_StandardPrice_Detail> list = new PDT_StandardPriceBLL((int)ViewState["ID"]).GetDetail(condition);
gv_List.BindGrid <PDT_StandardPrice_Detail>(list);
}
else
{
//获取非价表产品列表
string condition = "PDT_Product.ID NOT IN (SELECT Product FROM MCS_PUB.dbo.PDT_StandardPrice_Detail WHERE PriceID=" + ViewState["ID"].ToString() + ")";
condition += " AND State = 1 AND ApproveFlag = 1";
condition += " AND PDT_Product.ID IN (SELECT Product FROM MCS_PUB.dbo.PDT_ProductExtInfo WHERE Supplier=" + Session["OwnerClient"].ToString();
#region 获取产品分类
if (category > 1)
{
string _categoryids = "";
DataTable dt = TreeTableBLL.GetAllChildByNodes("MCS_Pub.dbo.PDT_Category", "ID", "SuperID", tr_Category.SelectValue);
for (int i = 0; i < dt.Rows.Count; i++)
{
_categoryids += dt.Rows[i]["ID"].ToString() + ",";
}
_categoryids += tr_Category.SelectValue;
condition += " AND PDT_ProductExtInfo.Category IN (" + _categoryids + ")";
}
#endregion
condition += ")";
if (tbx_ProductText.Text != "")
{
condition += " AND (FullName like '%" + tbx_ProductText.Text + "%' OR ShortName like '%" + tbx_ProductText.Text + "%' OR Code like '%" + tbx_ProductText.Text +
"%' OR PDT_Product.ID IN (SELECT Product FROM MCS_PUB.dbo.PDT_ProductExtInfo WHERE Supplier=" + Session["OwnerClient"].ToString() + " AND PDT_ProductExtInfo.Code LIKE '%" + tbx_ProductText.Text + "%'))";
}
IList <PDT_Product> products = PDT_ProductBLL.GetModelList(condition);
gv_NotInList.BindGrid <PDT_Product>(products);
}
cb_SelectAll.Checked = false;
}
protected void bt_Create_Click(object sender, EventArgs e)
{
DateTime begindate = DateTime.Parse(tbx_begindate.Text);
DateTime enddate = DateTime.Parse(tbx_enddate.Text);
#region 规则校验
if (select_PlanStaff.SelectValue == "")
{
MessageBox.Show(this, "请正确选择要填报计划的员工!");
return;
}
if (begindate < DateTime.Today)
{
MessageBox.Show(this, "开始日期不能小于今天！");
return;
}
if (enddate < begindate)
{
MessageBox.Show(this, "截止日期必须大于开始日期！");
return;
}
if ((enddate - begindate).Days > 31)
{
MessageBox.Show(this, "日期范围不能超过一个月！");
return;
}
if (JN_WorkingPlanBLL.GetModelList("Staff = " + select_PlanStaff.SelectValue +
" AND ( (BeginDate BETWEEN '" + begindate.ToString("yyyy-MM-dd") + "' AND '" + enddate.ToString("yyyy-MM-dd") +
"') OR ('" + begindate.ToString("yyyy-MM-dd") + "' BETWEEN BeginDate AND EndDate) )").Count > 0)
{
MessageBox.Show(this, "日期范围与系统中已填报的计划有日期重叠！");
return;
}
#endregion
InitGridView(begindate, enddate);
DataTable dt = GenareateDataTable(begindate, enddate);
#region 页面控件使能控制
tr_adddetail.Visible = true;
tbx_begindate.Enabled = false;
tbx_enddate.Enabled = false;
select_PlanStaff.Enabled = false;
tr_OrganizeCity.Enabled = false;
cbx_GenarateSynergetic.Visible = false;
bt_Create.Visible = false;
bt_Save.Visible = true;
#endregion
#region 载入该操作员所有负责客户，加入客户拜访记录计划中
IList <CM_Client> clients = CM_ClientBLL.GetModelList("ClientManager=" + select_PlanStaff.SelectValue + " AND ActiveFlag=1 AND ApproveFlag=1");
foreach (CM_Client client in clients)
{
DataRow dr = dt.NewRow();
dr["WorkingClassify"] = "1";
dr["WorkingClassifyName"] = DictionaryBLL.GetDicCollections("OA_WorkingClassify")["1"].Name;
dr["RelateClient"] = client.ID;
dr["RelateClientName"] = client.FullName;
dr["RelateStaff"] = 0;
dr["RelateStaffName"] = "";
dr["OfficialCity"] = client.OfficalCity;
if (client.OfficalCity > 0)
{
dr["OfficialCityName"] = TreeTableBLL.GetFullPathName("MCS_SYS.dbo.Addr_OfficialCity", client.OfficalCity).Replace("->", " ");
}
dt.Rows.Add(dr);
}
#endregion
#region 加入协同拜访工作项，管理片区非总部员工，将所有下级员工加入
if (cbx_GenarateSynergetic.Checked && int.Parse(tr_OrganizeCity.SelectValue) > 1)
{
#region 所有下级职位(不含本级职位)
string positions = "";
int position = new Org_StaffBLL(int.Parse(select_PlanStaff.SelectValue)).Model.Position;
DataTable dtAllChilePosition = TreeTableBLL.GetAllChildByNodes("MCS_SYS.dbo.Org_Position", "ID", "SuperID", position.ToString());
for (int i = 0; i < dtAllChilePosition.Rows.Count; i++)
{
positions += dtAllChilePosition.Rows[i]["ID"].ToString();
if (i != dtAllChilePosition.Rows.Count - 1)
{
positions += ",";
}
}
#endregion
#region 所有本级及下级管理片区
string orgcitys = "";
Addr_OrganizeCityBLL orgcity = new Addr_OrganizeCityBLL(int.Parse(tr_OrganizeCity.SelectValue), true);
orgcitys = orgcity.GetAllChildNodeIDs();
if (orgcitys != "")
{
orgcitys += ",";
}
orgcitys += tr_OrganizeCity.SelectValue;
#endregion
if (positions != "" && orgcitys != "")
{
IList <Org_Staff> staffs = Org_StaffBLL.GetStaffList("OrganizeCity IN (" + orgcitys + ") AND Position IN (" + positions + ") AND Dimission=1 AND ApproveFlag=1");
foreach (Org_Staff staff in staffs)
{
DataRow dr = dt.NewRow();
dr["WorkingClassify"] = "2";
dr["WorkingClassifyName"] = DictionaryBLL.GetDicCollections("OA_WorkingClassify")["2"].Name;
dr["RelateClient"] = 0;
dr["RelateClientName"] = "";
dr["RelateStaff"] = staff.ID;
dr["RelateStaffName"] = staff.RealName;
dr["OfficialCity"] = staff.OfficialCity;
if (staff.OfficialCity > 0)
{
dr["OfficialCityName"] = TreeTableBLL.GetFullPathName("MCS_SYS.dbo.Addr_OfficialCity", staff.OfficialCity).Replace("->", " ");
}
dt.Rows.Add(dr);
}
}
}
#endregion
#region 加入除客户拜访以外的工作项
foreach (ListItem item in ddl_WorkingClassify.Items)
{
if (int.Parse(item.Value) >= 3)
{
DataRow dr = dt.NewRow();
dr["WorkingClassify"] = int.Parse(item.Value);
dr["WorkingClassifyName"] = item.Text;
dr["Description"] = "";
dr["RelateClient"] = 0;
dr["RelateClientName"] = "";
dr["RelateStaff"] = 0;
dr["RelateStaffName"] = "";
dr["OfficialCity"] = (int)ViewState["StaffOfficialCity"];
if (ViewState["StaffOfficialCity"] != null && (int)ViewState["StaffOfficialCity"] != 0)
{
dr["OfficialCityName"] = new Addr_OfficialCityBLL((int)ViewState["StaffOfficialCity"]).Model.Name;
}
else
{
dr["OfficialCity"] = 0;
}
dr["Cost1"] = 0;
dr["Cost2"] = 0;
dt.Rows.Add(dr);
}
}
#endregion
ViewState["WorkingPlanData"] = dt;
ViewState["BeginDate"] = begindate;
ViewState["EndDate"] = enddate;
BindGrid();
}
private void BindGrid()
{
int month = int.Parse(ddl_Month.SelectedValue);
int organizecity = int.Parse(tr_OrganizeCity.SelectValue);
int level = int.Parse(ddl_Level.SelectedValue);
int feetype = int.Parse(ddl_FeeType.SelectedValue);
int state = int.Parse(ddl_State.SelectedValue);
int flag = int.Parse(ddl_Flag.SelectedValue);
if (MCSTabControl1.SelectedIndex == 0)
{
#region 显示汇总单数据源
Dictionary_Data dicFeeType = null;
if (feetype > 0)
{
dicFeeType = DictionaryBLL.GetDicCollections("FNA_FeeType")[feetype.ToString()];
}
DataTable dtSummary_Sub;
DataTable dtSummary = FNA_FeeApplyBLL.GetSummaryTotal(month, organizecity, level, feetype, state, flag, int.Parse(Session["UserID"].ToString()));
if (dtSummary.Rows.Count == 0)
{
gv_List.DataBind();
return;
}
else
{
dtSummary_Sub = FNA_FeeApplyBLL.GetSummaryTotal_Sub(month, organizecity, level, feetype, state, flag, int.Parse(Session["UserID"].ToString()));
}
#region 矩阵化数据表，扩展表数据列
dtSummary.Columns.Add(new DataColumn("ID", Type.GetType("System.Int32")));
if (level < 10)
{
foreach (DataRow row in dtSummary.Rows)
{
row["ID"] = row["OrganizeCity"];
}
dtSummary = MatrixTable.Matrix(dtSummary, new string[] { "ID", "管理片区名称" },
new string[] { "FeeTypeName", "AccountTitleName" }, "ApplyCost", true, true);
}
else
{
if (level == 10)
{
//按经销商查看
#region 将经销商的ID赋至表ID列
foreach (DataRow row in dtSummary.Rows)
{
row["ID"] = row["经销商ID"] == DBNull.Value ? 0 : row["经销商ID"];
row["经销商名称"] = row["经销商名称"] == DBNull.Value ? "无" : row["经销商名称"];
}
#endregion
dtSummary = MatrixTable.Matrix(dtSummary, new string[] { "ID", "管理片区名称", "经销商名称" },
new string[] { "FeeTypeName", "AccountTitleName" }, "ApplyCost", true, true);
}
else if (level == 20)
{
//按门店查看
#region 将门店的ID赋至表ID列
foreach (DataRow row in dtSummary.Rows)
{
row["ID"] = row["客户ID"] == DBNull.Value ? 0 : row["客户ID"];
row["客户名称"] = row["客户名称"] == DBNull.Value ? "无" : row["客户名称"];
}
#endregion
dtSummary = MatrixTable.Matrix(dtSummary, new string[] { "ID", "管理片区名称", "客户名称" },
new string[] { "FeeTypeName", "AccountTitleName" }, "ApplyCost", true, true);
}
}
dtSummary = MatrixTable.ColumnSummaryTotal(dtSummary, new int[] { 1 }, new string[] { "sales" });
dtSummary.Columns["合计"].ColumnName = "费用合计";
dtSummary.Columns.Add("本月费用合计", Type.GetType("System.Decimal"));
dtSummary.Columns.Add("预计销量", Type.GetType("System.Decimal"));
dtSummary.Columns.Add("上月销量", Type.GetType("System.Decimal"));
dtSummary.Columns.Add("平均销量", Type.GetType("System.Decimal"));
dtSummary.Columns.Add("预计费点", Type.GetType("System.String"));
if (dicFeeType != null && dicFeeType.Description == "BudgetControl" && level < 10)
{
dtSummary.Columns.Add("预算总额", Type.GetType("System.Decimal"));
dtSummary.Columns.Add("其中扩增额", Type.GetType("System.Decimal"));
dtSummary.Columns.Add("预算余额", Type.GetType("System.Decimal"));
dtSummary.Columns.Add("终结费用", Type.GetType("System.Decimal"));
}
if (dicFeeType != null && dicFeeType.Description == "FeeRateControl")
{
dtSummary.Columns.Add("上月余额", Type.GetType("System.Decimal"));
}
#endregion
decimal sumTotalVolume = 0, sumAvgVolume = 0, sumSalesForcast = 0, sumHappenApplyCost = 0;
decimal sumTotalBudget = 0, sumExtBudget = 0, sumPreMonthBudgetBalance = 0, sumUsableAmount = 0, sumCancelCost = 0;
int premonth = month - 1;
if (premonth >= AC_AccountMonthBLL.GetCurrentMonth())
{
premonth = AC_AccountMonthBLL.GetCurrentMonth() - 1;
}
foreach (DataRow row in dtSummary.Rows)
{
int id = 0;
if (int.TryParse(row["ID"].ToString(), out id) && id > 0)
{
string filter = "ID=" + id;
bool includechild = false;
if (level < 10 && new Addr_OrganizeCityBLL(id).Model.Level >= level)
{
includechild = true;
}
#region 计算销量数据
decimal happenApplyCost = 0, forcast = 0, preSales = 0, aVGSales = 0, feeRate = 0;
DataRow[] drows = dtSummary_Sub.Select(filter);
if (drows.Length > 0)
{
decimal.TryParse(drows[0]["HappenApplyCost"].ToString(), out happenApplyCost);
decimal.TryParse(drows[0]["Forcast"].ToString(), out forcast);
decimal.TryParse(drows[0]["PreSales"].ToString(), out preSales);
decimal.TryParse(drows[0]["AVGSales"].ToString(), out aVGSales);
decimal.TryParse(drows[0]["FeeRate"].ToString(), out feeRate);
}
row["本月费用合计"] = happenApplyCost.ToString("0.##");
row["上月销量"] = preSales.ToString("0.##");
row["平均销量"] = aVGSales.ToString("0.##");
row["预计销量"] = forcast.ToString("0.##");
row["预计费点"] = feeRate.ToString("0.##%");
sumHappenApplyCost += (decimal)row["本月费用合计"];
sumTotalVolume += (decimal)row["上月销量"];
sumAvgVolume += (decimal)row["平均销量"];
sumSalesForcast += (decimal)row["预计销量"];
#endregion
#region 预算总额及余额
if (dicFeeType != null && dicFeeType.Description == "BudgetControl" && level < 10)
{
row["预算总额"] = (FNA_BudgetBLL.GetSumBudgetAmount(month, id, feetype, includechild) + FNA_BudgetBLL.GetSumBudgetAmount(month, id, 0, includechild));
sumTotalBudget += (decimal)row["预算总额"];
row["其中扩增额"] = (FNA_BudgetExtraApplyBLL.GetExtraAmount(month, id, feetype, includechild) + FNA_BudgetExtraApplyBLL.GetExtraAmount(month, id, 0, includechild));
sumExtBudget += (decimal)row["其中扩增额"];
row["预算余额"] = (FNA_BudgetBLL.GetUsableAmount(month, id, feetype, includechild) + FNA_BudgetBLL.GetUsableAmount(month, id, 0, includechild));
sumUsableAmount += (decimal)row["预算余额"];
row["终结费用"] = (FNA_FeeApplyBLL.GetCancelCost(month, id, feetype, includechild) + FNA_FeeApplyBLL.GetCancelCost(month, id, 0, includechild));
sumCancelCost += (decimal)row["终结费用"];
}
#endregion
//#region 计算预计费点
//if (dicFeeType != null && dicFeeType.Description == "FeeRateControl" && level < 10)
//{
// row["上月余额"] = FNA_BudgetBLL.GetUsableAmount(month - 1, id, feetype, includechild);
// sumPreMonthBudgetBalance += (decimal)row["上月余额"];
// if ((decimal)row["预计销量"] != 0)
// row["预计费点"] = (((decimal)row["费用合计"] - (decimal)row["上月余额"]) / (decimal)row["预计销量"]).ToString("0.00%");
//}
//else if ((decimal)row["预计销量"] != 0)
// row["预计费点"] = ((decimal)row["费用合计"] / (decimal)row["预计销量"]).ToString("0.00%");
//#endregion
}
#region 求合计行
if (id == 0)
{
row["本月费用合计"] = sumHappenApplyCost.ToString("0.##");
row["上月销量"] = sumTotalVolume.ToString("0.##");
row["平均销量"] = sumAvgVolume.ToString("0.##");
row["预计销量"] = sumSalesForcast.ToString("0.##");
#region 预算总额及余额
if (dicFeeType != null && dicFeeType.Description == "BudgetControl" && level < 10)
{
row["预算总额"] = sumTotalBudget.ToString("0.##");
row["其中扩增额"] = sumExtBudget.ToString("0.##");
row["预算余额"] = sumUsableAmount.ToString("0.##");
row["终结费用"] = sumCancelCost.ToString("0.##");
}
#endregion
#region 预计费点
if (sumSalesForcast != 0)
{
if (dicFeeType != null && dicFeeType.Description == "FeeRateControl" && level < 10)
{
row["上月余额"] = sumPreMonthBudgetBalance;
row["预计费点"] = (((decimal)row["费用合计"] - sumPreMonthBudgetBalance) / sumSalesForcast).ToString("0.##%");
}
else
{
row["预计费点"] = ((decimal)row["费用合计"] / sumSalesForcast).ToString("0.##%");
}
}
#endregion
}
#endregion
}
gv_List.DataSource = dtSummary;
gv_List.DataBind();
if (dtSummary.Columns.Count >= 24)
{
gv_List.Width = new Unit(dtSummary.Columns.Count * 55);
}
else
{
gv_List.Width = new Unit(100, UnitType.Percentage);
}
MatrixTable.GridViewMatric(gv_List);
if (level == 20)
{
//按终端门店显示时，将上下行相同管理片区合并
MatrixTable.GridViewMergSampeValueRow(gv_List, 0);
MatrixTable.GridViewMergSampeValueRow(gv_List, 1);
}
#region 是否可以批量审批
if (state != 1 || level >= 10)
{
gv_List.Columns[0].ItemStyle.Width = new Unit(1);
foreach (GridViewRow row in gv_List.Rows)
{
row.FindControl("bt_Approved").Visible = false;
row.FindControl("bt_UnApproved").Visible = false;
}
}
else
{
gv_List.Columns[0].ItemStyle.Width = new Unit(68);
}
#endregion
#endregion
}
else
{
string condition = "1=1";
#region 组织明细记录的查询条件
//管理片区及所有下属管理片区
if (tr_OrganizeCity.SelectValue == "0")
{
#region 绑定用户可管辖的管理片区
Org_StaffBLL staff = new Org_StaffBLL((int)Session["UserID"]);
tr_OrganizeCity.DataSource = staff.GetStaffOrganizeCity();
if (tr_OrganizeCity.DataSource.Select("ID = 1").Length > 0)
{
tr_OrganizeCity.RootValue = "0";
tr_OrganizeCity.SelectValue = "1";
}
else
{
tr_OrganizeCity.RootValue = new Addr_OrganizeCityBLL(staff.Model.OrganizeCity).Model.SuperID.ToString();
tr_OrganizeCity.SelectValue = staff.Model.OrganizeCity.ToString();
}
if ((int)ViewState["OrganizeCity"] > 0)
{
tr_OrganizeCity.SelectValue = ViewState["OrganizeCity"].ToString();
}
#endregion
}
if (tr_OrganizeCity.SelectValue != "1")
{
Addr_OrganizeCityBLL orgcity = new Addr_OrganizeCityBLL(int.Parse(tr_OrganizeCity.SelectValue));
string orgcitys = orgcity.GetAllChildNodeIDs();
if (orgcitys != "")
{
orgcitys += ",";
}
orgcitys += tr_OrganizeCity.SelectValue;
condition += " AND FNA_FeeApply.OrganizeCity IN (" + orgcitys + ")";
}
//会计月条件
condition += " AND FNA_FeeApply.AccountMonth = " + ddl_Month.SelectedValue;
//费用类型
if (ddl_FeeType.SelectedValue != "0")
{
condition += " AND FNA_FeeApply.FeeType = " + ddl_FeeType.SelectedValue;
}
int accounttile = 0;
int.TryParse(tr_AccountTitle.SelectValue, out accounttile);
decimal _cost = 0;
decimal.TryParse(tbx_ApplyCost.Text, out _cost);
//费用科目
if (accounttile > 1)
{
DataTable dt = TreeTableBLL.GetAllChildByNodes("MCS_PUB.dbo.AC_AccountTitle", "ID", "SuperID", accounttile.ToString());
string ids = "";
foreach (DataRow dr in dt.Rows)
{
ids += dr["ID"].ToString() + ",";
}
ids += accounttile.ToString();
if (_cost == 0)
{
condition += " AND FNA_FeeApply.ID IN (SELECT ApplyID FROM MCS_FNA.dbo.FNA_FeeApplyDetail WHERE AccountTitle IN(" + ids + ") AND FNA_FeeApplyDetail.ApplyID=FNA_FeeApply.ID)";
}
else
{
condition += " AND FNA_FeeApply.ID IN (SELECT ApplyID FROM MCS_FNA.dbo.FNA_FeeApplyDetail WHERE AccountTitle IN(" + ids + ") AND (ApplyCost+AdjustCost)" + ddl_ApplyCostOP.SelectedValue + " " + _cost.ToString() + " AND FNA_FeeApplyDetail.ApplyID=FNA_FeeApply.ID)";
}
}
else if (_cost != 0)//金额判断
{
condition += " AND FNA_FeeApply.ID IN (SELECT ApplyID FROM MCS_FNA.dbo.FNA_FeeApplyDetail WHERE (ApplyCost+AdjustCost)" + ddl_ApplyCostOP.SelectedValue + " " + _cost.ToString() + " AND FNA_FeeApplyDetail.ApplyID=FNA_FeeApply.ID)";
}
//审批状态
if (ddl_State.SelectedValue == "0")
{
condition += " AND FNA_FeeApply.State IN (2,3) ";
}
else if (ddl_State.SelectedValue == "1")
{
condition +=
@" AND FNA_FeeApply.State = 2 AND FNA_FeeApply.ApproveTask IN
(SELECT EWF_Task_Job.Task FROM MCS_EWF.dbo.EWF_Task_JobDecision INNER JOIN
MCS_EWF.dbo.EWF_Task_Job ON EWF_Task_JobDecision.Job = EWF_Task_Job.ID
WHERE EWF_Task_JobDecision.RecipientStaff=" + Session["UserID"].ToString() + @" AND
EWF_Task_JobDecision.DecisionResult=1 and EWF_Task_Job.Status=3)";
}
else if (ddl_State.SelectedValue == "2")
{
condition += " AND FNA_FeeApply.State = 3 ";
}
else if (ddl_State.SelectedValue == "3")
{
AC_AccountMonth m = new AC_AccountMonthBLL(month).Model;
condition +=
@" AND FNA_FeeApply.State IN (2,3) AND FNA_FeeApply.ApproveTask IN
(SELECT EWF_Task_Job.Task FROM MCS_EWF.dbo.EWF_Task_JobDecision INNER JOIN
MCS_EWF.dbo.EWF_Task_Job ON EWF_Task_JobDecision.Job = EWF_Task_Job.ID
WHERE EWF_Task_JobDecision.RecipientStaff=" + Session["UserID"].ToString() + @" AND
EWF_Task_JobDecision.DecisionResult IN(2,5,6) AND
EWF_Task_JobDecision.DecisionTime BETWEEN DATEADD(month,-1,'" + m.BeginDate.ToString("yyyy-MM-dd") + @"') AND
DATEADD(month,3,'" + m.BeginDate.ToString("yyyy-MM-dd") + @"'))";
}
#endregion
gv_ListDetail.ConditionString = condition;
gv_ListDetail.BindGrid();
btn_Approve.Visible = state == 1;
btn_UnApprove.Visible = state == 1;
gv_ListDetail.Columns[gv_ListDetail.Columns.Count - 1].Visible = state == 1;
}
}
private void BindGrid()
{
string condition = "1=1";
#region 组织查询条件
//管理片区及所有下属管理片区
if (tr_OrganizeCity.SelectValue != "1")
{
Addr_OrganizeCityBLL orgcity = new Addr_OrganizeCityBLL(int.Parse(tr_OrganizeCity.SelectValue));
string orgcitys = orgcity.GetAllChildNodeIDs();
if (orgcitys != "")
{
orgcitys += ",";
}
orgcitys += tr_OrganizeCity.SelectValue;
condition += " AND FNA_FeeApply.OrganizeCity IN (" + orgcitys + ")";
}
//会计月条件
if ((int)ViewState["AccountTitle"] == 82)
{
condition += " AND EXISTS (SELECT ApplyID FROM MCS_FNA.dbo.FNA_FeeApplyDetail WHERE FNA_FeeApplyDetail.AccountTitle=82 AND FNA_FeeApplyDetail.ApplyID=FNA_FeeApply.ID AND BeginMonth=" + ddl_Month.SelectedValue + ")";
}
else
{
condition += " AND FNA_FeeApply.AccountMonth = " + ddl_Month.SelectedValue;
}
//申请单号
if (tbx_SheetCode.Text != "")
{
condition += " AND FNA_FeeApply.SheetCode like '%" + tbx_SheetCode.Text + "%'";
}
if (!string.IsNullOrEmpty(Select_InsertStaff.SelectValue))
{
condition += " AND FNA_FeeApply.InsertStaff=" + Select_InsertStaff.SelectValue;
}
if (!string.IsNullOrEmpty(select_ApplyClient.SelectValue))
{
condition += " AND FNA_FeeApply.Client=" + select_ApplyClient.SelectValue;
}
//费用类型
if (ddl_FeeType.SelectedValue != "0")
{
condition += " AND FNA_FeeApply.FeeType = " + ddl_FeeType.SelectedValue;
}
if (!Right_Assign_BLL.GetAccessRight((string)Session["UserName"], 4703, "Browse"))
{
//无查看营养教育费用权限
condition += " AND FNA_FeeApply.FeeType <> " + ConfigHelper.GetConfigInt("CSOCostType").ToString();
}
//审批状态
if (ddl_State.SelectedValue != "0")
{
condition += " AND FNA_FeeApply.State = " + ddl_State.SelectedValue;
}
//核销状态
if (ddl_WriteOffState.SelectedValue == "1")
{
condition += " AND FNA_FeeApply.State=3 AND FNA_FeeApply.ID IN (SELECT ApplyID FROM MCS_FNA.dbo.FNA_FeeApplyDetail WHERE AvailCost > 0 AND FNA_FeeApplyDetail.ApplyID=FNA_FeeApply.ID)";
}
else if (ddl_WriteOffState.SelectedValue == "2")
{
condition += " AND FNA_FeeApply.State=3 AND FNA_FeeApply.ID NOT IN (SELECT ApplyID FROM MCS_FNA.dbo.FNA_FeeApplyDetail WHERE AvailCost > 0 AND FNA_FeeApplyDetail.ApplyID=FNA_FeeApply.ID)";
}
//会计科目
int accounttile = 0;
if (int.TryParse(tr_AccountTitle.SelectValue, out accounttile) && accounttile > 1)
{
DataTable dt = TreeTableBLL.GetAllChildByNodes("MCS_PUB.dbo.AC_AccountTitle", "ID", "SuperID", accounttile.ToString());
string ids = "";
foreach (DataRow dr in dt.Rows)
{
ids += dr["ID"].ToString() + ",";
}
ids += accounttile.ToString();
condition += " AND FNA_FeeApply.ID IN (SELECT ApplyID FROM MCS_FNA.dbo.FNA_FeeApplyDetail WHERE AccountTitle IN(" + ids + ") AND FNA_FeeApplyDetail.ApplyID=FNA_FeeApply.ID)";
}
//标题
if (tbx_Title.Text != "")
{
condition += " AND MCS_SYS.dbo.UF_Spilt(FNA_FeeApply.ExtPropertys,'|',4) LIKE '%" + tbx_Title.Text + "%'";
}
#endregion
gv_List.ConditionString = condition;
gv_List.BindGrid();
}
protected void btn_addsearch_Click(object sender, EventArgs e)
{
if (ddl_Field.SelectedValue == "0")
{
return;
}
//添加条件
ListItem lt = new ListItem();
#region 条件选择
UD_TableList table = new UD_TableListBLL(new Guid(ddl_TableName.SelectedValue), true).Model;
if (table == null)
{
return;
}
UD_ModelFields modelfield = null;
try
{
modelfield = new UD_ModelFieldsBLL(new Guid(ddl_Field.SelectedValue), true).Model;
}
catch { }
if (modelfield == null)
{
//自定义字段
lt.Text = ddl_Field.SelectedItem.Text + " " + ddl_op.SelectedItem.Text + " ('" + tbx_searchvalue.Text + "')";
lt.Value = ddl_Field.SelectedValue + " " + ddl_op.SelectedItem.Value + " '" + tbx_searchvalue.Text + "'";
}
else
{
string fielddisplayname = table.DisplayName + "." + modelfield.DisplayName;
string fieldfullname = "";
if (modelfield.Flag == "Y")
{
fieldfullname = table.Name + "." + modelfield.FieldName; //实体字段
}
else
{
fieldfullname = "MCS_SYS.dbo.UF_Spilt(" + table.Name + ".ExtPropertys,'|'," + modelfield.Position.ToString() + ")";
}
if (ddl_op.SelectedValue == "NOTNULL")
{
lt.Text = fielddisplayname + " 不为空";
lt.Value = " ISNULL(" + fieldfullname + ",'')<>'' ";
lbx_search.Items.Add(lt);
return;
}
if (ddl_op.SelectedValue == "ISNULL")
{
lt.Text = fielddisplayname + " 为空";
lt.Value = " ISNULL(" + fieldfullname + ",'')='' ";
lbx_search.Items.Add(lt);
return;
}
switch (modelfield.RelationType)
{
case 1:
#region 字典关联
if (cbl_SearchValue.SelectedIndex != -1)
{
lt.Text = fielddisplayname + " 包含 ( ";
lt.Value = fieldfullname + " IN ( ";
foreach (ListItem item in cbl_SearchValue.Items)
{
if (item.Selected)
{
lt.Text += item.Text + ",";
lt.Value += "'" + item.Value + "',";
}
}
lt.Text = lt.Text.Substring(0, lt.Text.Length - 1);
lt.Value = lt.Value.Substring(0, lt.Value.Length - 1);
lt.Text += ")";
lt.Value += ")";
}
break;
#endregion
case 2:
#region 实体表关联
lt.Text = fielddisplayname + " 为 ";
lt.Value = fieldfullname + " = ";
if (!string.IsNullOrEmpty(modelfield.SearchPageURL))
{
//通过查询控件查询
lt.Text += MCSSelectControl1.SelectText;
lt.Value += "'" + MCSSelectControl1.SelectValue + "'";
}
else if (new UD_TableListBLL(modelfield.RelationTableName).Model.TreeFlag == "Y")
{
#region 通过树形结构查询
if (modelfield.RelationTableName == "MCS_SYS.dbo.Addr_OrganizeCity" || modelfield.RelationTableName == "MCS_SYS.dbo.Addr_OfficialCity")
{
if (ddl_TreeLevel.SelectedValue == "0")
{
lt.Text += MCSTreeControl1.SelectText;
lt.Value = fieldfullname + " = " + MCSTreeControl1.SelectValue;
}
else if (ddl_TreeLevel.SelectedValue == "100")
{
//所属于
lt.Text = fielddisplayname + " " + ddl_TreeLevel.SelectedItem.Text + " " + MCSTreeControl1.SelectText;
lt.Value = fieldfullname + " IN (";
DataTable dt = TreeTableBLL.GetAllChildByNodes(modelfield.RelationTableName, "ID", "SuperID", MCSTreeControl1.SelectValue);
foreach (DataRow dr in dt.Rows)
{
lt.Value += "'" + dr[0].ToString() + "',";
}
lt.Value += "'" + MCSTreeControl1.SelectValue + "')";
}
}
else
{
lt.Text += MCSTreeControl1.SelectText;
lt.Value = fieldfullname + " IN (";
DataTable dt = TreeTableBLL.GetAllChildByNodes(modelfield.RelationTableName, "ID", "SuperID", MCSTreeControl1.SelectValue);
foreach (DataRow dr in dt.Rows)
{
lt.Value += "'" + dr[0].ToString() + "',";
}
lt.Value += "'" + MCSTreeControl1.SelectValue + "')";
}
#endregion
}
else
{
#region 其他关联方式的实体表
if (cbl_SearchValue.SelectedIndex != -1)
{
lt.Text = fielddisplayname + " 包含 ( ";
lt.Value = fieldfullname + " IN ( ";
foreach (ListItem item in cbl_SearchValue.Items)
{
if (item.Selected)
{
lt.Text += item.Text + ",";
lt.Value += "'" + item.Value + "',";
}
}
lt.Text = lt.Text.Substring(0, lt.Text.Length - 1);
lt.Value = lt.Value.Substring(0, lt.Value.Length - 1);
lt.Text += ")";
lt.Value += ")";
}
#endregion
}
break;
#endregion
default:
#region 非关联字段
lt.Text = fielddisplayname + " " + ddl_op.SelectedItem.Text + " ('" + tbx_searchvalue.Text + "')";
lt.Value = fieldfullname + " = ";
if (ddl_op.SelectedValue == "StartWith")
{
lt.Value = fieldfullname + " " + "Like" + " '" + tbx_searchvalue.Text + "%'";
}
else if (ddl_op.SelectedValue == "NotStartWith")
{
lt.Value = fieldfullname + " " + "Not Like" + " '" + tbx_searchvalue.Text + "%'";
}
else if (ddl_op.SelectedValue != "like")
{
if (modelfield.DataType == 4) //日期(datetime)
{
lt.Value = "CONVERT(DATETIME,CONVERT(VARCHAR,CONVERT(DATETIME," + fieldfullname + "),111))" + " " + ddl_op.SelectedItem.Value + " '" + tbx_searchvalue.Text + "'";
}
else
{
lt.Value = fieldfullname + " " + ddl_op.SelectedItem.Value + " '" + tbx_searchvalue.Text + "'";
}
}
else
{
lt.Value = fieldfullname + " " + ddl_op.SelectedItem.Value + " '%" + tbx_searchvalue.Text + "%'";
}
break;
#endregion
}
}
#endregion
if (!string.IsNullOrEmpty(lt.Text))
{
lbx_search.Items.Add(lt);
}
}
private void BindGrid()
{
int month = int.Parse(ddl_Month.SelectedValue);
int organizecity = int.Parse(tr_OrganizeCity.SelectValue);
int level = int.Parse(ddl_Level.SelectedValue);
int feetype = int.Parse(ddl_FeeType.SelectedValue);
int state = int.Parse(ddl_State.SelectedValue);
if (MCSTabControl1.SelectedIndex == 0)
{
#region 显示汇总单数据源
Dictionary_Data dicFeeType = null;
if (feetype > 0)
{
dicFeeType = DictionaryBLL.GetDicCollections("FNA_FeeType")[feetype.ToString()];
}
#region 额外条件
string condition = "";
//费用代垫客户
if (select_Client.SelectValue != "")
{
condition += " AND FNA_FeeWriteOff.InsteadPayClient=" + select_Client.SelectValue;
}
//费用代垫员工
if (Select_InsteadPayStaff.SelectValue != "")
{
condition += " AND MCS_SYS.dbo.UF_Spilt2('MCS_FNA.dbo.FNA_FeeWriteOff',FNA_FeeWriteOff.ExtPropertys,'InsteadPayStaff')=" + Select_InsteadPayStaff.SelectValue;
}
//核销申请人
if (Select_InsertStaff.SelectValue != "")
{
condition += " AND FNA_FeeWriteOff.InsertStaff=" + Select_InsertStaff.SelectValue;
}
#endregion
DataTable dtSummary = FNA_FeeWriteOffBLL.GetSummaryTotal(month, organizecity, level, feetype, state, int.Parse(Session["UserID"].ToString()), condition);
if (dtSummary.Rows.Count == 0)
{
gv_List.DataBind();
return;
}
#region 矩阵化数据表，扩展表数据列
dtSummary.Columns.Add(new DataColumn("ID", Type.GetType("System.Int32")));
if (level < 10)
{
foreach (DataRow row in dtSummary.Rows)
{
row["ID"] = row["OrganizeCity"];
}
dtSummary = MatrixTable.Matrix(dtSummary, new string[] { "ID", "管理片区名称" },
new string[] { "FeeTypeName", "AccountTitleName" }, "WriteOffCost", true, true);
}
else
{
if (level == 10)
{
//按经销商查看
#region 将经销商的所属管理片区赋至管理片区列
Dictionary <int, string> dicFullOrganizeCityName = new Dictionary <int, string>();
foreach (DataRow row in dtSummary.Rows)
{
row["ID"] = row["经销商ID"] == DBNull.Value ? 0 : row["经销商ID"];
row["经销商名称"] = row["经销商名称"] == DBNull.Value ? "无" : row["经销商名称"];
if ((int)row["ID"] > 0)
{
CM_Client c = new CM_ClientBLL((int)row["ID"]).Model;
if (c != null)
{
if (!dicFullOrganizeCityName.ContainsKey(c.OrganizeCity))
{
dicFullOrganizeCityName.Add(c.OrganizeCity, TreeTableBLL.GetFullPathName("MCS_SYS.dbo.Addr_OrganizeCity", c.OrganizeCity));
}
row["管理片区名称"] = dicFullOrganizeCityName[c.OrganizeCity];
}
}
}
#endregion
dtSummary = MatrixTable.Matrix(dtSummary, new string[] { "ID", "管理片区名称", "经销商名称" },
new string[] { "FeeTypeName", "AccountTitleName" }, "WriteOffCost", true, true);
}
else if (level == 20)
{
//按门店查看
#region 将门店的所属管理片区赋至管理片区列
Dictionary <int, string> dicFullOrganizeCityName = new Dictionary <int, string>();
foreach (DataRow row in dtSummary.Rows)
{
row["ID"] = row["客户ID"] == DBNull.Value ? 0 : row["客户ID"];
row["客户名称"] = row["客户名称"] == DBNull.Value ? "无" : row["客户名称"];
if ((int)row["ID"] > 0)
{
CM_Client c = new CM_ClientBLL((int)row["ID"]).Model;
if (c != null)
{
if (!dicFullOrganizeCityName.ContainsKey(c.OrganizeCity))
{
dicFullOrganizeCityName.Add(c.OrganizeCity, TreeTableBLL.GetFullPathName("MCS_SYS.dbo.Addr_OrganizeCity", c.OrganizeCity));
}
row["管理片区名称"] = dicFullOrganizeCityName[c.OrganizeCity];
}
}
}
#endregion
dtSummary = MatrixTable.Matrix(dtSummary, new string[] { "ID", "管理片区名称", "客户名称" },
new string[] { "FeeTypeName", "AccountTitleName" }, "WriteOffCost", true, true);
}
}
dtSummary = MatrixTable.ColumnSummaryTotal(dtSummary, new int[] { 1 }, new string[] { "WriteOffCost" });
#endregion
#region 暂不计算分析数据
//dtSummary.Columns["合计"].ColumnName = "费用合计";
//dtSummary.Columns.Add("预计销量", Type.GetType("System.Decimal"));
//if (dicFeeType != null && dicFeeType.Description == "BudgetControl" && level < 10)
//{
// dtSummary.Columns.Add("预算总额", Type.GetType("System.Decimal"));
// dtSummary.Columns.Add("其中扩增额", Type.GetType("System.Decimal"));
// dtSummary.Columns.Add("预算余额", Type.GetType("System.Decimal"));
//}
//if (dicFeeType != null && dicFeeType.Description == "FeeRateControl")
// dtSummary.Columns.Add("上月余额", Type.GetType("System.Decimal"));
//dtSummary.Columns.Add("费点", Type.GetType("System.String"));
//decimal sumTotalVolume = 0, sumTotalBudget = 0, sumExtBudget = 0, sumPreMonthBudgetBalance = 0, sumUsableAmount = 0;
//foreach (DataRow row in dtSummary.Rows)
//{
// int id = 0;
// if (int.TryParse(row[0].ToString(), out id) && id > 0)
// {
// bool includechild = true;
// if (level < 10 && new Addr_OrganizeCityBLL(id).Model.Level < level) includechild = false;
// #region 计算费点
// if (level < 10)
// row["预计销量"] = SVM_SalesForcastBLL.GetTotalVolume(month, id, includechild);
// else if (level == 10 || level == 20)
// row["预计销量"] = SVM_SalesForcastBLL.GetTotalVolumeByClient(month, id);
// sumTotalVolume += (decimal)row["预计销量"];
// if (dicFeeType != null && dicFeeType.Description == "BudgetControl" && level < 10)
// {
// row["预算总额"] = (FNA_BudgetBLL.GetSumBudgetAmount(month, id, feetype, includechild) + FNA_BudgetBLL.GetSumBudgetAmount(month, id, 0, includechild));
// sumTotalBudget += (decimal)row["预算总额"];
// row["其中扩增额"] = (FNA_BudgetExtraApplyBLL.GetExtraAmount(month, id, feetype, includechild) + FNA_BudgetExtraApplyBLL.GetExtraAmount(month, id, 0, includechild));
// sumExtBudget += (decimal)row["其中扩增额"];
// row["预算余额"] = (FNA_BudgetBLL.GetUsableAmount(month, id, feetype, includechild) + FNA_BudgetBLL.GetUsableAmount(month, id, 0, includechild));
// sumUsableAmount += (decimal)row["预算余额"];
// }
// if (dicFeeType != null && dicFeeType.Description == "FeeRateControl" && level < 10)
// {
// row["上月余额"] = FNA_BudgetBLL.GetUsableAmount(month - 1, id, feetype, includechild);
// sumPreMonthBudgetBalance += (decimal)row["上月余额"];
// if ((decimal)row["预计销量"] != 0)
// row["费点"] = (((decimal)row["费用合计"] - (decimal)row["上月余额"]) / (decimal)row["预计销量"]).ToString("0.00%");
// }
// else if ((decimal)row["预计销量"] != 0)
// row["费点"] = ((decimal)row["费用合计"] / (decimal)row["预计销量"]).ToString("0.00%");
// #endregion
// }
// #region 求合计行
// if (id == 0)
// {
// row["预计销量"] = sumTotalVolume.ToString("0.00");
// #region 预算总额及余额
// if (dicFeeType != null && dicFeeType.Description == "BudgetControl" && level < 10)
// {
// row["预算总额"] = sumTotalBudget.ToString("0.00");
// row["其中扩增额"] = sumExtBudget.ToString("0.00");
// row["预算余额"] = sumUsableAmount.ToString("0.00");
// }
// #endregion
// #region 费点
// if (sumTotalVolume != 0)
// {
// if (dicFeeType != null && dicFeeType.Description == "FeeRateControl" && level < 10)
// {
// row["上月余额"] = sumPreMonthBudgetBalance;
// row["费点"] = (((decimal)row["费用合计"] - sumPreMonthBudgetBalance) / sumTotalVolume).ToString("0.##%");
// }
// else
// row["费点"] = ((decimal)row["费用合计"] / sumTotalVolume).ToString("0.##%");
// }
// #endregion
// }
// #endregion
//}
#endregion
#endregion
gv_List.DataSource = dtSummary;
gv_List.DataBind();
if (dtSummary.Columns.Count >= 24)
{
gv_List.Width = new Unit(dtSummary.Columns.Count * 55);
}
else
{
gv_List.Width = new Unit(100, UnitType.Percentage);
}
MatrixTable.GridViewMatric(gv_List);
if (level == 20)
{
//按终端门店显示时，将上下行相同管理片区合并
MatrixTable.GridViewMergSampeValueRow(gv_List, 0);
MatrixTable.GridViewMergSampeValueRow(gv_List, 1);
}
#region 是否可以批量审批
if (state != 1 || level >= 10)
{
gv_List.Columns[0].ItemStyle.Width = new Unit(1);
foreach (GridViewRow row in gv_List.Rows)
{
row.FindControl("bt_Approved").Visible = false;
row.FindControl("bt_UnApproved").Visible = false;
}
}
else
{
gv_List.Columns[0].ItemStyle.Width = new Unit(68);
}
#endregion
}
else
{
string condition = "1=1";
#region 组织明细记录的查询条件
//管理片区及所有下属管理片区
if (tr_OrganizeCity.SelectValue != "1")
{
Addr_OrganizeCityBLL orgcity = new Addr_OrganizeCityBLL(int.Parse(tr_OrganizeCity.SelectValue));
string orgcitys = orgcity.GetAllChildNodeIDs();
if (orgcitys != "")
{
orgcitys += ",";
}
orgcitys += tr_OrganizeCity.SelectValue;
condition += " AND FNA_FeeWriteOff.OrganizeCity IN (" + orgcitys + ")";
}
//会计月条件
condition += " AND FNA_FeeWriteOff.AccountMonth = " + ddl_Month.SelectedValue;
int accounttile = 0;
int.TryParse(tr_AccountTitle.SelectValue, out accounttile);
decimal _cost = 0;
decimal.TryParse(tbx_WriteOffCost.Text, out _cost);
//费用类型
if (ddl_FeeType.SelectedValue != "0" && !(accounttile > 1))
{
IList <AC_AccountTitleInFeeType> titles = AC_AccountTitleInFeeTypeBLL.GetModelList("FeeType=" + ddl_FeeType.SelectedValue);
string ids = "";
foreach (AC_AccountTitleInFeeType item in titles)
{
ids += item.AccountTitle + ",";
}
if (ids.EndsWith(","))
{
ids = ids.Substring(0, ids.Length - 1);
}
condition += " AND FNA_FeeWriteOff.ID IN (SELECT WriteOffID FROM MCS_FNA.dbo.FNA_FeeWriteOffDetail WHERE AccountTitle IN(" + ids + ") AND FNA_FeeWriteOffDetail.WriteOffID=FNA_FeeWriteOff.ID)";
}
//费用科目
if (accounttile > 1)
{
DataTable dt = TreeTableBLL.GetAllChildByNodes("MCS_PUB.dbo.AC_AccountTitle", "ID", "SuperID", accounttile.ToString());
string ids = "";
foreach (DataRow dr in dt.Rows)
{
ids += dr["ID"].ToString() + ",";
}
ids += accounttile.ToString();
if (_cost == 0)
{
condition += " AND FNA_FeeWriteOff.ID IN (SELECT WriteOffID FROM MCS_FNA.dbo.FNA_FeeWriteOffDetail WHERE AccountTitle IN(" + ids + ") AND FNA_FeeWriteOffDetail.WriteOffID=FNA_FeeWriteOff.ID)";
}
else
{
condition += " AND FNA_FeeWriteOff.ID IN (SELECT WriteOffID FROM MCS_FNA.dbo.FNA_FeeWriteOffDetail WHERE AccountTitle IN(" + ids + ") AND (WriteOffCost+AdjustCost)" + ddl_WriteOffCostOP.SelectedValue + " " + _cost.ToString() + " AND FNA_FeeWriteOffDetail.WriteOffID=FNA_FeeWriteOff.ID)";
}
}
else if (_cost != 0)
{ //核销金额判断
condition += " AND FNA_FeeWriteOff.ID IN (SELECT WriteOffID FROM MCS_FNA.dbo.FNA_FeeWriteOffDetail WHERE (WriteOffCost+AdjustCost)" + ddl_WriteOffCostOP.SelectedValue + " " + _cost.ToString() + " AND FNA_FeeWriteOffDetail.WriteOffID=FNA_FeeWriteOff.ID)";
}
//审批状态
if (ddl_State.SelectedValue == "0")
{
condition += " AND FNA_FeeWriteOff.State IN (2,3) ";
}
else if (ddl_State.SelectedValue == "1")
{
condition +=
@" AND FNA_FeeWriteOff.State = 2 AND FNA_FeeWriteOff.ApproveTask IN
(SELECT EWF_Task_Job.Task FROM MCS_EWF.dbo.EWF_Task_JobDecision INNER JOIN
MCS_EWF.dbo.EWF_Task_Job ON EWF_Task_JobDecision.Job = EWF_Task_Job.ID
WHERE EWF_Task_JobDecision.RecipientStaff=" + Session["UserID"].ToString() + @" AND
EWF_Task_JobDecision.DecisionResult=1 and EWF_Task_Job.Status=3)";
}
else if (ddl_State.SelectedValue == "2")
{
condition += " AND FNA_FeeWriteOff.State = 3 ";
}
else if (ddl_State.SelectedValue == "3")
{
AC_AccountMonth m = new AC_AccountMonthBLL(month).Model;
condition +=
@" AND FNA_FeeWriteOff.State IN (2,3) AND FNA_FeeWriteOff.ApproveTask IN
(SELECT EWF_Task_Job.Task FROM MCS_EWF.dbo.EWF_Task_JobDecision INNER JOIN
MCS_EWF.dbo.EWF_Task_Job ON EWF_Task_JobDecision.Job = EWF_Task_Job.ID
WHERE EWF_Task_JobDecision.RecipientStaff=" + Session["UserID"].ToString() + @" AND
EWF_Task_JobDecision.DecisionResult IN(2,5,6) AND
EWF_Task_JobDecision.DecisionTime BETWEEN DATEADD(month,-1,'" + m.BeginDate.ToString("yyyy-MM-dd") + @"') AND
DATEADD(month,3,'" + m.BeginDate.ToString("yyyy-MM-dd") + @"'))";
}
//费用代垫客户
if (select_Client.SelectValue != "")
{
condition += " AND FNA_FeeWriteOff.InsteadPayClient=" + select_Client.SelectValue;
}
//费用代垫员工
if (Select_InsteadPayStaff.SelectValue != "")
{
condition += " AND MCS_SYS.dbo.UF_Spilt2('MCS_FNA.dbo.FNA_FeeWriteOff',FNA_FeeWriteOff.ExtPropertys,'InsteadPayStaff')=" + Select_InsteadPayStaff.SelectValue;
}
//核销申请人
if (Select_InsertStaff.SelectValue != "")
{
condition += " AND FNA_FeeWriteOff.InsertStaff=" + Select_InsertStaff.SelectValue;
}
#endregion
gv_ListDetail.ConditionString = condition;
gv_ListDetail.BindGrid();
}
}
