Demo entry 6746037

c#

   

Submitted by xuyue on May 28, 2018 at 08:51
Language: C#. Code size: 136.1 kB.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Ext.Net;
using Socialworkers.Data;
using Socialworkers.Entities;
using System.Data;
using System.Data.SqlClient;

public partial class admin_JiuBao_Index : System.Web.UI.Page
{
    private string AreaId = "";
    private string Actual_AreaId = "";
    private string GroupId = "";
    public string Uid = "";
    public string Sid = "";

    public string TableStr = "";
    protected void Page_Load(object sender, EventArgs e)
    {
        
        if (!new PublicClass().IsLogin())
        {
            Response.Redirect("~/Error.aspx?type=0");
        }
        AreaId = Session["Areaid"].ToString();
        Actual_AreaId = Session["Areaid"].ToString();
        Uid = Session["Uid"].ToString();
        Sid = Session["Sid"].ToString();
        GroupId = Session["GroupId"].ToString();
        if (!IsPostBack && !X.IsAjaxRequest)
        {
            SetMainInfo(int.Parse(AreaId));
            SetToolBarStatus();
            AreaComboInit();
        }
    }

    private void SetToolBarStatus()
    {
        if (GroupId.Equals("1"))
        {
            JiuBaoUserInfo jbui = DataRepository.JiuBaoUserInfoProvider.GetById(int.Parse(Session["Sid"].ToString()));

            //用户名,社区
            if (jbui != null)
            {
                showtooltip.Html = "<b>欢迎您,<span style='color:red;font-size:13px;'>" + Secret.AESDecrypt(jbui.Username) + "</span>  用户,您属于九堡镇( <span style='color:red;font-size:13px;'>" + jbui.Origin + "</span> )</b>";
            }
        }
        else
        {
            DataSet ds = DataRepository.Provider.ExecuteDataSet(CommandType.Text, "select a.Name,u.UserName,t.Name from UserInfo u left join AreaInfo a on u.AreaId = a.id left join TsocialworkersTable t on u.sid = t.id where  u.id = " + Session["Uid"].ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (string.IsNullOrEmpty(ds.Tables[0].Rows[0][2].ToString()))
                {
                    showtooltip.Html = "<b>欢迎您,<span style='color:red;font-size:13px;'>" + ds.Tables[0].Rows[0][1].ToString() + "</span>  用户,您属于 <span style='color:red;font-size:13px;'>" + ds.Tables[0].Rows[0][0].ToString() + "</span> </b>";

                }
                else
                {
                    showtooltip.Html = "<b>欢迎您,<span style='color:red;font-size:13px;'>" + ds.Tables[0].Rows[0][2].ToString() + "</span>  用户,您属于 <span style='color:red;font-size:13px;'>" + ds.Tables[0].Rows[0][0].ToString() + "</span> </b>";
                }
            }
        }
    }
    //改变社区
    [DirectMethod(Msg = "加载中...", ShowMask = true, Namespace = "X")]
    public string ChangeArea()
    {
        if (AreaCombo.SelectedIndex >= 0)
        {
            SetMainInfo(int.Parse(AreaCombo.SelectedItem.Value));
            TList<AreaMap> ams = DataRepository.AreaMapProvider.Find("AreaId = '" + AreaCombo.SelectedItem.Value + "'");
            if (ams.Count > 0)
            {
                return ams[0].Mapx + "," + ams[0].Mapy;
            }
        }
        return "60789,35917";
    }
    //设置右边简介
    private void SetMainInfo(int id)
    {
        AreaInfo ai = DataRepository.AreaInfoProvider.GetById(id);
        MainInfomationPanel.Html = ai.Content.ToString();
    }
    private void AreaComboInit()
    {
        string sql = "";
        if (GroupId.Equals("1"))
        {
            sql = "select Id,Name from  AreaInfo  where Id ='" + Actual_AreaId + "' or parentId = '" + Actual_AreaId + "' order by AreaInfo.orderid";

        }
        else
        {
            sql = "select Id,Name from AreaInfo  where Id ='" + Actual_AreaId + "' order by AreaInfo.orderid";

        }
        DataSet ds = DataRepository.Provider.ExecuteDataSet(CommandType.Text, sql);


        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
        {
            Ext.Net.ListItem li = new Ext.Net.ListItem();
            li.Text = ds.Tables[0].Rows[i][1].ToString();
            li.Value = ds.Tables[0].Rows[i][0].ToString();
            AreaCombo.Items.Add(li);
        }
        if (ds.Tables[0].Rows.Count > 0)
        {
            AreaCombo.SelectedIndex = 0;
        }
    }



    protected void OnExit_Click(object sender, DirectEventArgs e)
    {
        new PublicClass().Exit();
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Ext.Net;
using Socialworkers.Entities;
using Socialworkers.Data;
using System.Data;
using System.Text;
using System.Net;
using System.IO;
using OpenMas;
using System.Data.SqlClient;
public partial class admin_JiuBao_NewSendMessage : System.Web.UI.Page
{
    private int stmstart = 0;
    private int stmlimit = 0;
    private int ntmstart = 0;
    private int ntmlimit = 0;
    private int mtmstart = 0;
    private int mtmlimit = 0;
    private string Uid = "";
    private string Sid = "";
    private string AreaId = "";
    private int hasNum = 0;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!new PublicClass().IsLogin())
        {
            Response.Redirect("~/Error.aspx?type=0");
        }

        AreaId = Session["Areaid"].ToString();
        Uid = Session["Uid"].ToString();
        Sid = Session["Sid"].ToString();
        if (!IsPostBack)
        {
            //默认短信署名
            TList<AreaInfo> ais = DataRepository.AreaInfoProvider.Find("Id = 7" + AreaId);
            if (ais.Count > 0)
            {
                txtName.Text = ais[0].Name;
            }
            if (!AreaId.Equals("7"))
            {
                QueryFormInit();
            }

            //日期
            DateField_BeginDate.SelectedDate = DateTime.Now;
          
            //时间24
            for (int i = 0; i < 24; i++)
            {
                ComboBox_Hour.Items.Add(new Ext.Net.ListItem(i.ToString(), i.ToString()));
                if (i.Equals(DateTime.Now.Hour))
                {
                    ComboBox_Hour.SelectedIndex = i;
                }
            }
            //分钟60
            for (int i = 0; i < 60; i++)
            {
                ComboBox_Minute.Items.Add(new Ext.Net.ListItem(i.ToString(), i.ToString()));
                if (i.Equals(DateTime.Now.Minute))
                {
                    ComboBox_Minute.SelectedIndex = i;
                }
            }
            //秒60
            for (int i = 0; i < 60; i++)
            {
                ComboBox_Second.Items.Add(new Ext.Net.ListItem(i.ToString(), i.ToString()));
                if (i.Equals(DateTime.Now.Second))
                {
                    ComboBox_Second.SelectedIndex = i;
                }
            }
        }
        if (!AreaId.Equals("7"))
        {
            //社区额度
            int limitNum = 0;


            string sql = "select LimitNum from MessageLimit  where AreaId = '" + AreaId + "' and LimitTime='" + new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1) + "'";
            DataSet ds = DataRepository.Provider.ExecuteDataSet(CommandType.Text, sql);
            if (ds.Tables[0].Rows.Count <= 0)
            {
                TList<ParameterConst> pcs = DataRepository.ParameterConstProvider.Find(" AreaId = '7' AND Type = 'MessageLimit'");
                if (pcs.Count > 0)
                {
                    limitNum = int.Parse(pcs[0].Value);
                }
                else
                {
                    limitNum = 5000;
                }
                string sql_insert = "insert into MessageLimit(AreaId, SendNum, LimitNum, LimitTime, Remarks) Values('" + AreaId + "','0','" + limitNum + "','" + new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1) + "','')";
                DataRepository.Provider.ExecuteNonQuery(CommandType.Text, sql_insert);
            }
            else
            {
                limitNum = int.Parse(ds.Tables[0].Rows[0]["LimitNum"].ToString());
            }

            //检测已发短信
            PublicFunction pf = new PublicFunction();
            pf.UpdateMessageLog(AreaId);

            //已发
            string alltotal = "select (select SUM(SMSCount) from OpenMasMessageStatus where GatawayStatus='DELIVRD' and MessageId in (select messageid from OpenMasMessageSend where Areaid=" + AreaId + " and createTime>='" + new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1) + "'))";
            DataSet ds_total = DataRepository.Provider.ExecuteDataSet(CommandType.Text, alltotal);
            int sendNum = 0;
            if (ds_total.Tables[0].Rows.Count > 0)
            {
                sendNum = (string.IsNullOrEmpty(ds_total.Tables[0].Rows[0][0].ToString()) ? 0 : int.Parse(ds_total.Tables[0].Rows[0][0].ToString()));
                hasNum = limitNum - sendNum;
            }
            else
            {
                hasNum = limitNum;
            }
            Main.Title = "短信群发(" + (string.IsNullOrEmpty(ds_total.Tables[0].Rows[0][0].ToString()) ? "" : "当月已发送 " + sendNum + " 条;  ") + "剩余可发" + hasNum + "条)";
        }
    }

    #region 查询
    protected void QueryAction(object sender, DirectEventArgs e)
    {
        stmstart = 0;


        QueryWindow.Hide();
        C_Hid_QueryStr.Text = getQuerySqlStr();
        PagingToolBar2.PageIndex = 0;
    }
    protected void QueryAction2(object sender, DirectEventArgs e)
    {
        ntmstart = 0;


        QueryWindow2.Hide();
        string sqlStr = "";
        if (!string.IsNullOrEmpty(TextField_2_Name.Text))
        {
            sqlStr += " AND name like '%" + TextField_2_Name.Text + "%' ";
        }
        if (!string.IsNullOrEmpty(TextField_2_Number.Text))
        {
            sqlStr += " AND PhoneNumber like '%" + TextField_2_Number.Text + "%' ";
        }
        if (!string.IsNullOrEmpty(TextField_2_Duty.Text))
        {
            sqlStr += " AND CompanyName like '%" + TextField_2_Duty.Text + "%' ";
        }
        if (!string.IsNullOrEmpty(TextField_2_Type.Text))
        {
            sqlStr += " AND Type like '%" + TextField_2_Type.Text + "%' ";
        }
        C_Hid_QueryStr2.Text = sqlStr;
        PagingToolBar1.PageIndex = 0;
    }
    protected void QueryAction3(object sender, DirectEventArgs e)
    {
        ntmstart = 0;


        QueryWindow3.Hide();
        string sqlStr = "";
        if (!string.IsNullOrEmpty(TextField_Cus_Name.Text))
        {
            sqlStr += " AND name like '%" + TextField_Cus_Name.Text + "%' ";
        }
        if (!string.IsNullOrEmpty(TextField_Cus_Num.Text))
        {
            sqlStr += " AND PhoneName like '%" + TextField_Cus_Num.Text + "%' ";
        }
        if (!string.IsNullOrEmpty(TextField_Cus_Remarks.Text))
        {
            sqlStr += " AND Remarks like '%" + TextField_Cus_Remarks.Text + "%' ";
        }

        C_Hid_QueryStr.Text = sqlStr;
        PagingToolBar3.PageIndex = 0;
    }
    private void QueryFormInit()
    {
        string queryAreaId = AreaId;

        //片区
        ParameterizedSqlFilterBuilder<SmallArea> filter_SmallArea = new ParameterizedSqlFilterBuilder<SmallArea>();
        filter_SmallArea.AppendEquals("Aid", queryAreaId);

        TList<SmallArea> smallAreas = DataRepository.SmallAreaProvider.Find(filter_SmallArea, " OrderId");

        foreach (SmallArea smallArea in smallAreas)
        {
            Ext.Net.TreeNode node = new Ext.Net.TreeNode();
            node.Text = smallArea.Name;
            node.Leaf = true;
            node.Checked = ThreeStateBool.False;
            Mc_SmallArea.NodeOptions = node;
        }

        //小区
        DataSet ds_FamilyAdd = DataRepository.Provider.ExecuteDataSet(CommandType.Text, "select FamilyAdd from Account where Aid = '" + queryAreaId + "'  group by FamilyAdd order by ISNUMERIC(FamilyAdd),case when ISNUMERIC(FamilyAdd)=0 then 0 else cast(FamilyAdd as float) end asc,FamilyAdd");
        for (int i = 0; i < ds_FamilyAdd.Tables[0].Rows.Count; i++)
        {
            Ext.Net.TreeNode node = new Ext.Net.TreeNode();
            node.Text = ds_FamilyAdd.Tables[0].Rows[i]["FamilyAdd"].ToString();
            node.Leaf = true;
            node.Checked = ThreeStateBool.False;
            Mc_FamilyAdd.NodeOptions = node;
        }

        //幢
        DataSet ds_Building = DataRepository.Provider.ExecuteDataSet(CommandType.Text, "select Building from Account where Aid = '" + queryAreaId + "'   group by Building order by ISNUMERIC(Building),case when ISNUMERIC(Building)=0 then 0 else cast(Building as float) end asc,Building");
        for (int i = 0; i < ds_Building.Tables[0].Rows.Count; i++)
        {
            Ext.Net.TreeNode node = new Ext.Net.TreeNode();
            node.Text = ds_Building.Tables[0].Rows[i]["Building"].ToString();
            node.Leaf = true;
            node.Checked = ThreeStateBool.False;
            Mc_Building.NodeOptions = node;
        }

        //低保困难
        string[] ServiceTypeArr = { "市级", "区级", "镇扩面", "村(社)扩面" };
        foreach (string tmp in ServiceTypeArr)
        {
            Ext.Net.TreeNode node = new Ext.Net.TreeNode();
            node.Text = tmp;
            node.Leaf = true;
            node.Checked = ThreeStateBool.False;
            Mc_ServiceType.NodeOptions = node;
        }

        //房屋性质
        DataSet ds_HouseProperties = DataRepository.Provider.ExecuteDataSet(CommandType.Text, "select HouseProperties from Account where Aid = '" + queryAreaId + "' and HouseProperties<>'' and HouseProperties is not null   group by HouseProperties order by HouseProperties");
        for (int i = 0; i < ds_HouseProperties.Tables[0].Rows.Count; i++)
        {
            Ext.Net.TreeNode node = new Ext.Net.TreeNode();
            node.Text = ds_HouseProperties.Tables[0].Rows[i]["HouseProperties"].ToString();
            node.Leaf = true;
            node.Checked = ThreeStateBool.False;
            Mc_HouseProperties.NodeOptions = node;
        }
        //人员类别
        string[] PTypeArr = { "独居", "高龄", "空巢", "无劳保", "育龄妇女", "统战人员", "特殊人员" };
        foreach (string tmp in PTypeArr)
        {
            Ext.Net.TreeNode node = new Ext.Net.TreeNode();
            node.Text = tmp;
            node.Leaf = true;
            node.Checked = ThreeStateBool.False;
            Mc_PType.NodeOptions = node;
        }
        //户主关系
        DataSet ds_Relationship = DataRepository.Provider.ExecuteDataSet(CommandType.Text, "select Relationship from PopulationInfo where accountid in (select id from Account where Aid = '" + queryAreaId + "') and Relationship<>'' and Relationship is not null   group by Relationship order by Relationship");
        for (int i = 0; i < ds_Relationship.Tables[0].Rows.Count; i++)
        {
            Ext.Net.TreeNode node = new Ext.Net.TreeNode();
            node.Text = ds_Relationship.Tables[0].Rows[i]["Relationship"].ToString();
            node.Leaf = true;
            node.Checked = ThreeStateBool.False;
            Mc_Relationship.NodeOptions = node;
        }
        //性别
        string[] SexArr = { "男", "女" };
        foreach (string tmp in SexArr)
        {
            Ext.Net.TreeNode node = new Ext.Net.TreeNode();
            node.Text = tmp;
            node.Leaf = true;
            node.Checked = ThreeStateBool.False;
            Mc_Sex.NodeOptions = node;
        }
        //户口性质
        DataSet ds_AccountType = DataRepository.Provider.ExecuteDataSet(CommandType.Text, "select AccountType from PopulationInfo where accountid in (select id from Account where Aid = '" + queryAreaId + "') and AccountType<>'' and AccountType is not null   group by AccountType order by AccountType");
        for (int i = 0; i < ds_AccountType.Tables[0].Rows.Count; i++)
        {
            Ext.Net.TreeNode node = new Ext.Net.TreeNode();
            node.Text = ds_AccountType.Tables[0].Rows[i]["AccountType"].ToString();
            node.Leaf = true;
            node.Checked = ThreeStateBool.False;
            Mc_AccountType.NodeOptions = node;
        }
        //政治面貌
        string[] PolityFaceArr = { "团员", "在职党员", "社区党员", "民族党派", "群众", "其他" };
        foreach (string tmp in PolityFaceArr)
        {
            Ext.Net.TreeNode node = new Ext.Net.TreeNode();
            node.Text = tmp;
            node.Leaf = true;
            node.Checked = ThreeStateBool.False;
            Mc_PolityFace.NodeOptions = node;
        }
        //民族
        DataSet ds_Nation = DataRepository.Provider.ExecuteDataSet(CommandType.Text, "select Nation from PopulationInfo where accountid in (select id from Account where Aid = '" + queryAreaId + "') and Nation<>'' and Nation is not null   group by Nation order by Nation");
        for (int i = 0; i < ds_Nation.Tables[0].Rows.Count; i++)
        {
            Ext.Net.TreeNode node = new Ext.Net.TreeNode();
            node.Text = ds_Nation.Tables[0].Rows[i]["Nation"].ToString();
            node.Leaf = true;
            node.Checked = ThreeStateBool.False;
            Mc_Nation.NodeOptions = node;
        }
        //优抚对象
        string[] YoufuArr = { "现役军人", "退伍军人", "伤残军人" };
        foreach (string tmp in YoufuArr)
        {
            Ext.Net.TreeNode node = new Ext.Net.TreeNode();
            node.Text = tmp;
            node.Leaf = true;
            node.Checked = ThreeStateBool.False;
            Mc_Youfu.NodeOptions = node;
        }
        //劳模
        string[] LaomoArr = { "市级", "区级" };
        foreach (string tmp in LaomoArr)
        {
            Ext.Net.TreeNode node = new Ext.Net.TreeNode();
            node.Text = tmp;
            node.Leaf = true;
            node.Checked = ThreeStateBool.False;
            Mc_Laomo.NodeOptions = node;
        }

        //残疾类别
        string[] CanjiArr = { "精神残疾", "视力", "视力一级", "视力二级", "视力三级", "视力四级", "智力", "智力一级", "智力二级", "智力三级", "智力四级", "肢体", "肢体一级", "肢体二级", "肢体三级", "肢体四级", "听力语言", "听力语言一级", "听力语言二级", "听力语言三级", "听力语言四级", "军残一级", "军残二级", "军残三级", "军残四级", "军残五级", "军残六级", "军残七级", "军残八级", "军残九级", "军残十级" };
        foreach (string tmp in CanjiArr)
        {
            Ext.Net.TreeNode node = new Ext.Net.TreeNode();
            node.Text = tmp;
            node.Leaf = true;
            node.Checked = ThreeStateBool.False;
            Mc_Canji.NodeOptions = node;
        }
        //文化程度
        DataSet ds_Education = DataRepository.Provider.ExecuteDataSet(CommandType.Text, "select Education from PopulationInfo where accountid in (select id from Account where Aid = '" + queryAreaId + "') and Education<>'' and Education is not null   group by Education order by Education");
        for (int i = 0; i < ds_Education.Tables[0].Rows.Count; i++)
        {
            Ext.Net.TreeNode node = new Ext.Net.TreeNode();
            node.Text = ds_Education.Tables[0].Rows[i]["Education"].ToString();
            node.Leaf = true;
            node.Checked = ThreeStateBool.False;
            Mc_Education.NodeOptions = node;
        }
        //就业状况
        string[] JiuyeArr = { "在职", "失业", "灵活就业", "企业退休", "离休", "退休" };
        foreach (string tmp in JiuyeArr)
        {
            Ext.Net.TreeNode node = new Ext.Net.TreeNode();
            node.Text = tmp;
            node.Leaf = true;
            node.Checked = ThreeStateBool.False;
            Mc_Jiuye.NodeOptions = node;
        }
        //健康状况
        string[] JiankangArr = { "一般", "良好", "规定病种", "卧床不起" };
        foreach (string tmp in JiankangArr)
        {
            Ext.Net.TreeNode node = new Ext.Net.TreeNode();
            node.Text = tmp;
            node.Leaf = true;
            node.Checked = ThreeStateBool.False;
            Mc_Jiankang.NodeOptions = node;
        }
        //婚姻状况
        string[] HunyingArr = { "未婚", "已婚", "离异", "丧偶" };
        foreach (string tmp in HunyingArr)
        {
            Ext.Net.TreeNode node = new Ext.Net.TreeNode();
            node.Text = tmp;
            node.Leaf = true;
            node.Checked = ThreeStateBool.False;
            Mc_Hunying.NodeOptions = node;
        }
    }
    private string getQuerySqlStr()
    {
        string sqlStr = "";

        if (!string.IsNullOrEmpty(Mc_SmallArea.Value))
        {
            string[] str_SmallAreaArr = Mc_SmallArea.Value.Split('|');
            string tmpStr_SmallArea = "";
            foreach (string str in str_SmallAreaArr)
            {
                if (!string.IsNullOrEmpty(str))
                {
                    if (!string.IsNullOrEmpty(tmpStr_SmallArea))
                    {
                        tmpStr_SmallArea += "," + "'" + str + "'";
                    }
                    else
                    {
                        tmpStr_SmallArea += "'" + str + "'";
                    }
                }
            }
            if (!string.IsNullOrEmpty(tmpStr_SmallArea))
            {
                sqlStr += " And Account.SmallAreaId in (select id from SmallArea where Name in (" + tmpStr_SmallArea + "))";
            }
        }

        if (!string.IsNullOrEmpty(Mc_FamilyAdd.Value))
        {
            string[] str_FamilyAdd = Mc_FamilyAdd.Value.Split('|');

            string tmpStr_FamilyAdd = "";


            foreach (string str in str_FamilyAdd)
            {

                if (!string.IsNullOrEmpty(str))
                {
                    if (!string.IsNullOrEmpty(tmpStr_FamilyAdd))
                    {
                        tmpStr_FamilyAdd += "," + "'" + str + "'";
                    }
                    else
                    {
                        tmpStr_FamilyAdd += "'" + str + "'";
                    }
                }
            }


            if (!string.IsNullOrEmpty(tmpStr_FamilyAdd))
            {
                sqlStr += " And Account.FamilyAdd in (" + tmpStr_FamilyAdd + ")";
            }
        }

        if (!string.IsNullOrEmpty(Mc_Building.Value))
        {
            string[] str_Building = Mc_Building.Value.Split('|');
            string tmpStr_Building = "";
            foreach (string str in str_Building)
            {
                if (!string.IsNullOrEmpty(str))
                {
                    if (!string.IsNullOrEmpty(tmpStr_Building))
                    {
                        tmpStr_Building += "," + "'" + str + "'";
                    }
                    else
                    {
                        tmpStr_Building += "'" + str + "'";
                    }
                }
            }
            if (!string.IsNullOrEmpty(tmpStr_Building))
            {
                sqlStr += " And Account.Building in (" + tmpStr_Building + ")";
            }
        }

        if (!string.IsNullOrEmpty(Mc_ServiceType.Value))
        {
            string[] str_ServiceType = Mc_ServiceType.Value.Split('|');
            string tmpStr_ServiceType = "";
            foreach (string str in str_ServiceType)
            {
                if (!string.IsNullOrEmpty(str))
                {
                    if (!string.IsNullOrEmpty(tmpStr_ServiceType))
                    {
                        tmpStr_ServiceType += "," + "'" + str + "'";
                    }
                    else
                    {
                        tmpStr_ServiceType += "'" + str + "'";
                    }
                }
            }
            if (!string.IsNullOrEmpty(tmpStr_ServiceType))
            {
                sqlStr += " And Account.ServiceType in (" + tmpStr_ServiceType + ")";
            }
        }

        if (!string.IsNullOrEmpty(Mc_HouseProperties.Value))
        {
            string[] str_HouseProperties = Mc_HouseProperties.Value.Split('|');
            string tmpStr_HouseProperties = "";
            foreach (string str in str_HouseProperties)
            {
                if (!string.IsNullOrEmpty(str))
                {
                    if (!string.IsNullOrEmpty(tmpStr_HouseProperties))
                    {
                        tmpStr_HouseProperties += "," + "'" + str + "'";
                    }
                    else
                    {
                        tmpStr_HouseProperties += "'" + str + "'";
                    }
                }
            }
            if (!string.IsNullOrEmpty(tmpStr_HouseProperties))
            {
                sqlStr += " And Account.HouseProperties in (" + tmpStr_HouseProperties + ")";
            }
        }

        if (!string.IsNullOrEmpty(TextField_AreaCount_Min.Text))
        {

            sqlStr += " And (ISNUMERIC(Account.AreaCount) <> 0 And cast (Account.AreaCount as float) >= '" + TextField_AreaCount_Min.Text + "')";

        }
        if (!string.IsNullOrEmpty(TextField_AreaCount_Max.Text))
        {
            sqlStr += " And (ISNUMERIC(Account.AreaCount) <> 0 And cast (Account.AreaCount as float) <= '" + TextField_AreaCount_Max.Text + "')";


        }

        if (!string.IsNullOrEmpty(Mc_PType.Value))
        {
            string[] str_PType = Mc_PType.Value.Split('|');
            string tmpStr_PType = "";
            foreach (string str in str_PType)
            {
                if (!string.IsNullOrEmpty(str))
                {

                    if (!string.IsNullOrEmpty(tmpStr_PType))
                    {
                        tmpStr_PType += " Or PopulationInfo.PType like '%" + str + "%' ";
                    }
                    else
                    {
                        tmpStr_PType += " PopulationInfo.PType like '%" + str + "%' ";
                    }
                }
            }
            if (!string.IsNullOrEmpty(tmpStr_PType))
            {
                sqlStr += " And (" + tmpStr_PType + ")";
            }
        }
        if (!string.IsNullOrEmpty(Mc_Relationship.Value))
        {
            string[] str_Relationship = Mc_Relationship.Value.Split('|');
            string tmpStr_Relationship = "";
            foreach (string str in str_Relationship)
            {
                if (!string.IsNullOrEmpty(str))
                {
                    if (!string.IsNullOrEmpty(tmpStr_Relationship))
                    {
                        tmpStr_Relationship += "," + "'" + str + "'";
                    }
                    else
                    {
                        tmpStr_Relationship += "'" + str + "'";
                    }
                }
            }
            if (!string.IsNullOrEmpty(tmpStr_Relationship))
            {
                sqlStr += " And PopulationInfo.Relationship in (" + tmpStr_Relationship + ")";
            }
        }
        if (!string.IsNullOrEmpty(Mc_Sex.Value))
        {
            string[] str_Sex = Mc_Sex.Value.Split('|');
            string tmpStr_Sex = "";
            foreach (string str in str_Sex)
            {
                if (!string.IsNullOrEmpty(str))
                {
                    string sexStr = "0";
                    if (str.Equals("男"))
                    {
                        sexStr = "1";
                    }
                    else
                    {
                        sexStr = "0";
                    }
                    if (!string.IsNullOrEmpty(tmpStr_Sex))
                    {
                        tmpStr_Sex += "," + sexStr;
                    }
                    else
                    {
                        tmpStr_Sex += sexStr;
                    }
                }
            }
            if (!string.IsNullOrEmpty(tmpStr_Sex))
            {
                sqlStr += " And PopulationInfo.Sex in (" + tmpStr_Sex + ")";
            }
        }
        if (!string.IsNullOrEmpty(Mc_AccountType.Value))
        {
            string[] str_AccountType = Mc_AccountType.Value.Split('|');
            string tmpStr_AccountType = "";
            foreach (string str in str_AccountType)
            {
                if (!string.IsNullOrEmpty(str))
                {
                    if (!string.IsNullOrEmpty(tmpStr_AccountType))
                    {
                        tmpStr_AccountType += "," + "'" + str + "'";
                    }
                    else
                    {
                        tmpStr_AccountType += "'" + str + "'";
                    }
                }
            }
            if (!string.IsNullOrEmpty(tmpStr_AccountType))
            {
                sqlStr += " And PopulationInfo.AccountType in (" + tmpStr_AccountType + ")";
            }
        }
        if (!string.IsNullOrEmpty(Mc_PolityFace.Value))
        {
            string[] str_PolityFace = Mc_PolityFace.Value.Split('|');
            string tmpStr_PolityFace = "";
            foreach (string str in str_PolityFace)
            {
                if (!string.IsNullOrEmpty(str))
                {
                    if (!string.IsNullOrEmpty(tmpStr_PolityFace))
                    {
                        tmpStr_PolityFace += "," + "'" + str + "'";
                    }
                    else
                    {
                        tmpStr_PolityFace += "'" + str + "'";
                    }
                }
            }
            if (!string.IsNullOrEmpty(tmpStr_PolityFace))
            {
                sqlStr += " And PopulationInfo.PolityFace in (" + tmpStr_PolityFace + ")";
            }
        }
        if (!string.IsNullOrEmpty(Mc_Nation.Value))
        {
            string[] str_Nation = Mc_Nation.Value.Split('|');
            string tmpStr_Nation = "";
            foreach (string str in str_Nation)
            {
                if (!string.IsNullOrEmpty(str))
                {
                    if (!string.IsNullOrEmpty(tmpStr_Nation))
                    {
                        tmpStr_Nation += "," + "'" + str + "'";
                    }
                    else
                    {
                        tmpStr_Nation += "'" + str + "'";
                    }
                }
            }
            if (!string.IsNullOrEmpty(tmpStr_Nation))
            {
                sqlStr += " And PopulationInfo.Nation in (" + tmpStr_Nation + ")";
            }
        }
        if (!string.IsNullOrEmpty(Mc_Youfu.Value))
        {
            string[] str_Youfu = Mc_Youfu.Value.Split('|');
            string tmpStr_Youfu = "";
            foreach (string str in str_Youfu)
            {
                if (!string.IsNullOrEmpty(str))
                {
                    if (!string.IsNullOrEmpty(tmpStr_Youfu))
                    {
                        tmpStr_Youfu += "," + "'" + str + "'";
                    }
                    else
                    {
                        tmpStr_Youfu += "'" + str + "'";
                    }
                }
            }
            if (!string.IsNullOrEmpty(tmpStr_Youfu))
            {
                sqlStr += " And PopulationInfo.Youfu in (" + tmpStr_Youfu + ")";
            }
        }
        if (!string.IsNullOrEmpty(Mc_Laomo.Value))
        {
            string[] str_Laomo = Mc_Laomo.Value.Split('|');
            string tmpStr_Laomo = "";
            foreach (string str in str_Laomo)
            {
                if (!string.IsNullOrEmpty(str))
                {
                    if (!string.IsNullOrEmpty(tmpStr_Laomo))
                    {
                        tmpStr_Laomo += "," + "'" + str + "'";
                    }
                    else
                    {
                        tmpStr_Laomo += "'" + str + "'";
                    }
                }
            }
            if (!string.IsNullOrEmpty(tmpStr_Laomo))
            {
                sqlStr += " And PopulationInfo.Laomo in (" + tmpStr_Laomo + ")";
            }
        }
        if (!string.IsNullOrEmpty(Mc_Canji.Value))
        {
            string[] str_Canji = Mc_Canji.Value.Split('|');
            string tmpStr_Canji = "";
            foreach (string str in str_Canji)
            {
                if (!string.IsNullOrEmpty(str))
                {
                    if (!string.IsNullOrEmpty(tmpStr_Canji))
                    {
                        tmpStr_Canji += "," + "'" + str + "'";
                    }
                    else
                    {
                        tmpStr_Canji += "'" + str + "'";
                    }
                }
            }
            if (!string.IsNullOrEmpty(tmpStr_Canji))
            {
                sqlStr += " And PopulationInfo.Canji in (" + tmpStr_Canji + ")";
            }
        }
        if (!string.IsNullOrEmpty(Mc_Education.Value))
        {
            string[] str_Education = Mc_Education.Value.Split('|');
            string tmpStr_Education = "";
            foreach (string str in str_Education)
            {
                if (!string.IsNullOrEmpty(str))
                {
                    if (!string.IsNullOrEmpty(tmpStr_Education))
                    {
                        tmpStr_Education += "," + "'" + str + "'";
                    }
                    else
                    {
                        tmpStr_Education += "'" + str + "'";
                    }
                }
            }
            if (!string.IsNullOrEmpty(tmpStr_Education))
            {
                sqlStr += " And PopulationInfo.Education in (" + tmpStr_Education + ")";
            }
        }
        if (!string.IsNullOrEmpty(Mc_Jiuye.Value))
        {
            string[] str_Jiuye = Mc_Jiuye.Value.Split('|');
            string tmpStr_Jiuye = "";
            foreach (string str in str_Jiuye)
            {
                if (!string.IsNullOrEmpty(str))
                {
                    if (!string.IsNullOrEmpty(tmpStr_Jiuye))
                    {
                        tmpStr_Jiuye += "," + "'" + str + "'";
                    }
                    else
                    {
                        tmpStr_Jiuye += "'" + str + "'";
                    }
                }
            }
            if (!string.IsNullOrEmpty(tmpStr_Jiuye))
            {
                sqlStr += " And PopulationInfo.Jiuye in (" + tmpStr_Jiuye + ")";
            }
        }
        if (!string.IsNullOrEmpty(Mc_Jiankang.Value))
        {
            string[] str_Jiankang = Mc_Jiankang.Value.Split('|');
            string tmpStr_Jiankang = "";
            foreach (string str in str_Jiankang)
            {
                if (!string.IsNullOrEmpty(str))
                {
                    if (!string.IsNullOrEmpty(tmpStr_Jiankang))
                    {
                        tmpStr_Jiankang += "," + "'" + str + "'";
                    }
                    else
                    {
                        tmpStr_Jiankang += "'" + str + "'";
                    }
                }
            }
            if (!string.IsNullOrEmpty(tmpStr_Jiankang))
            {
                sqlStr += " And PopulationInfo.Jiankang in (" + tmpStr_Jiankang + ")";
            }
        }
        if (!string.IsNullOrEmpty(Mc_Hunying.Value))
        {
            string[] str_Hunying = Mc_Hunying.Value.Split('|');
            string tmpStr_Hunying = "";
            foreach (string str in str_Hunying)
            {
                if (!string.IsNullOrEmpty(str))
                {
                    if (!string.IsNullOrEmpty(tmpStr_Hunying))
                    {
                        tmpStr_Hunying += "," + "'" + str + "'";
                    }
                    else
                    {
                        tmpStr_Hunying += "'" + str + "'";
                    }
                }
            }
            if (!string.IsNullOrEmpty(tmpStr_Hunying))
            {
                sqlStr += " And PopulationInfo.Hunying in (" + tmpStr_Hunying + ")";
            }
        }
        if (DateField_Birthday.SelectedValue != null)
        {


            sqlStr += " And PopulationInfo.Birthday ='" + DateField_Birthday.Value.ToString() + "'";

        }
        if (!string.IsNullOrEmpty(TextField_Age_Min.Text))
        {
            int b = 0;
            try
            {

                b = int.Parse(TextField_Age_Min.Text);
            }
            catch
            {
                throw new Exception("最小年龄不为正整数");
            }
            if (b <= 0)
            {
                throw new Exception("最小年龄不为正整数");
            }
            DateTime maxTime = DateTime.Now.AddYears(0 - b);
            sqlStr += " And PopulationInfo.Birthday <='" + maxTime + "'";

        }
        if (!string.IsNullOrEmpty(TextField_Age_Max.Text))
        {
            int b = 0;
            try
            {

                b = int.Parse(TextField_Age_Max.Text);
            }
            catch
            {
                throw new Exception("最大年龄不为正整数");
            }
            if (b <= 0)
            {
                throw new Exception("最大年龄不为正整数");
            }
            DateTime minTime = DateTime.Now.AddYears(0 - b);
            sqlStr += " And PopulationInfo.Birthday >='" + minTime + "'";

        }
        if (!string.IsNullOrEmpty(TextField_Name.Text))
        {
            sqlStr += " And PopulationInfo.Name like '%" + TextField_Name.Text + "%'";
        }
        if (!string.IsNullOrEmpty(TextField_Identycode.Text))
        {
            sqlStr += " And PopulationInfo.Identycode like '%" + TextField_Identycode.Text + "%'";
        }
        if (!string.IsNullOrEmpty(TextField_Tel.Text))
        {
            sqlStr += " And ( PopulationInfo.FamilyTel like '%" + TextField_Tel.Text + "%' or PopulationInfo.MobilePhone like '%" + TextField_Tel.Text + "%'  )";
        }
        if (!string.IsNullOrEmpty(TextField_HouseHoldPlace.Text))
        {
            sqlStr += " And ( PopulationInfo.HouseHoldPlace like '%" + TextField_HouseHoldPlace.Text + "%'  )";
        }
        if (!string.IsNullOrEmpty(TextField_Residence.Text))
        {
            sqlStr += " And ( PopulationInfo.Residence like '%" + TextField_Residence.Text + "%'  )";
        }
        if (!string.IsNullOrEmpty(TextField_Workplace.Text))
        {
            sqlStr += " And ( PopulationInfo.Workplace like '%" + TextField_Workplace.Text + "%'  )";
        }
        if (!string.IsNullOrEmpty(TextField_Remarks.Text))
        {
            sqlStr += " And ( PopulationInfo.Remarks like '%" + TextField_Remarks.Text + "%'  )";
        }
        return sqlStr;
    }
    #endregion
    [DirectMethod(ShowMask = true, Msg = "数据加载中...", Namespace = "X")]
    public void ShowNumbersWin()
    {


        if (FirstInsert_TextField.Text.Equals("True"))
        {
            ComboBoxStore.DataBind();

            UsefulNumberStore.DataBind();
            WinNumbers.ActiveIndex = 0;
            FirstInsert_TextField.Text = "False";
        }

        WinNumbers.Show();
    }
    [DirectMethod(ShowMask = true, Msg = "数据加载中...", Namespace = "X")]
    public void AllStoreSelections(string type)
    {
        DataSet ds = null;
        int selectCount = 0;
        int wrongCount = 0;
        int repeatCount = 0;
        int rightCount = 0;
        string codes = txtNumbers.Text;
        string sql = "";
        if (type.Equals("2"))
        {
            string whereStr = "";
            if (!ShowEmptyCheckBox.Checked)
            {
                whereStr = " AND PopulationInfo.MobilePhone is not null AND PopulationInfo.MobilePhone <> '' ";
            }
            string QueryStr = C_Hid_QueryStr.Text;
            //一户一人

            sql = "select PopulationInfo.MobilePhone from PopulationInfo left join Account on Account.id = PopulationInfo.accountid left join AreaInfo on Account.aid = AreaInfo.id where AreaInfo.id = " + AreaId;

            string onlyonewhereStr = "";
            if (OnlyOneCheckBox.Checked)
            {
                onlyonewhereStr += " and PopulationInfo.Id in (select top 1 gbp.Id from PopulationInfo gbp where PopulationInfo.AccountId=gbp.AccountId order by case ISNULL(gbp.MobilePhone,'') when '' then 0 else 1 end desc,case gbp.relationship when '本人' then 1 when '户主' then 2 else 3 end asc)";
            }

            sql += QueryStr + whereStr + onlyonewhereStr;


        }
        else if (type.Equals("1"))
        {
            string whereStr = "area='" + AreaId + "'";
            if (!ShowEmptyCheckBox.Checked)
            {
                whereStr += " AND MobilePhone is not null AND MobilePhone <> '' ";
            }
            sql = "select MobilePhone from UsefulNumbers where " + whereStr;
        }
        else if (type.Equals("3"))
        {


            string whereStr = "AreaId='" + AreaId + "' AND GroupName = '" + CboType.SelectedItem.Value + "'";
            if (!ShowEmptyCheckBox.Checked)
            {
                whereStr += " AND PhoneName is not null AND PhoneName <> '' ";
            }
            sql = "select PhoneName as MobilePhone from SendMessageNumbers where " + whereStr;
        }
        try
        {
            ds = DataRepository.Provider.ExecuteDataSet(CommandType.Text, sql);
            selectCount = ds.Tables[0].Rows.Count;

            for (int i = 0; i < selectCount; i++)
            {
                string tempStr = ds.Tables[0].Rows[i]["MobilePhone"].ToString();
                if (string.IsNullOrEmpty(tempStr))
                {
                    wrongCount++;
                }
                else if (codes.IndexOf(tempStr) != -1)
                {
                    repeatCount++;
                }
                else
                {
                    if (string.IsNullOrEmpty(codes))
                    {
                        codes = tempStr;
                    }
                    else
                    {
                        codes += "," + tempStr;
                    }
                    rightCount++;
                }
            }
        }
        catch
        {
            throw new Exception("数据库查询失败。");
        }
        txtNumbers.Text = codes;
        string[] arrTemp = codes.Split(',');
        NumerCount.Text = arrTemp.Length.ToString();


        MessageBoxButtonsConfig bts = new MessageBoxButtonsConfig();
        MessageBoxButtonConfig btn_yes = new MessageBoxButtonConfig();
        MessageBoxButtonConfig btn_no = new MessageBoxButtonConfig();
        btn_yes.Text = "继续添加";
        btn_no.Text = "添加完毕";
        btn_no.Handler = "Ext.getCmp('WinNumbers').hide();";
        bts.Yes = btn_yes;
        bts.No = btn_no;

        X.Msg.Confirm("提示", "本次共选择了" + selectCount + "条记录,其中" + wrongCount + "条错误记录," + repeatCount + "条重复记录,成功添加" + rightCount + "条正确记录。", bts).Show();
        // WinNumbers.Hide();

    }
    protected void SubSelectedItems(object sender, DirectEventArgs e)
    {
        string json = e.ExtraParams["Values"];

    }

    protected void PopulationStore_RefreshData(object sender, StoreRefreshDataEventArgs e)
    {

        stmstart = e.Start / e.Limit;
        stmlimit = e.Limit;
        PopulationStoreBind();
    }

    protected void CustomGroupStore_RefreshData(object sender, StoreRefreshDataEventArgs e)
    {

        mtmstart = e.Start / e.Limit;
        mtmlimit = e.Limit;
        CustomGroupStoreBind();
    }
    private void CustomGroupStoreBind()
    {
        if (mtmstart <= 0)
        {
            mtmlimit = 15;
            mtmstart = 0;
        }
        int count = 0;
        TList<SendMessageNumbers> smns = DataRepository.SendMessageNumbersProvider.GetPaged("AreaId='" + AreaId + "' AND GroupName = '" + CboType.SelectedItem.Value + "'" + C_Hid_QueryStr.Text, "id", mtmstart, mtmlimit, out count);


        CustomGroupStore.DataSource = smns;
        CustomGroupStore.DataBind();

        (this.PopulationStore.Proxy[0] as PageProxy).Total = count;
    }

    private void PopulationStoreBind()
    {
        if (stmlimit <= 0)
        {
            stmstart = 0;
            stmlimit = 15;
        }
        string QueryStr = C_Hid_QueryStr.Text;


        string whereStr = "";
        if (!ShowEmptyCheckBox.Checked)
        {
            whereStr = " AND PopulationInfo.MobilePhone is not null AND PopulationInfo.MobilePhone <> '' ";
        }

        //一户一人

        string orderby_sql = " order by AreaInfo.orderid desc,account.aid,account.FamilyAdd,ISNUMERIC(account.Building),case when ISNUMERIC(account.Building)=0 then 0 else cast(account.Building as float) end, ISNUMERIC(account.Unit),case when ISNUMERIC(account.Unit)=0 then 0 else cast(account.Unit as float) end,ISNUMERIC(account.Room),case when ISNUMERIC(account.Room)=0 then 0 else cast(account.Room as float) end asc ";

        string sql = "select top " + stmlimit;
        string sql_select = " PopulationInfo.Id,Shequ=AreaInfo.Name,ShowAdd=(Account.FamilyAdd+(case when nullif(Account.Building,'') is not null then Account.Building+'幢' else '' end)+(case when nullif(Account.Unit,'') is not null then Account.Unit+'单元' else '' end)+(case when nullif(Account.Room,'') is not null then Account.Room+'室' else '' end)),PopulationInfo.Name,PopulationInfo.Unit,PopulationInfo.Room,PopulationInfo.Relationship,PopulationInfo.AccountId,PopulationInfo.ReName,PopulationInfo.Identycode,PopulationInfo.Birthday,PopulationInfo.FamilyAdd,PopulationInfo.FamilyTel,PopulationInfo.Residence,PopulationInfo.Sex,PopulationInfo.Nation,PopulationInfo.Education,PopulationInfo.PolityFace,PopulationInfo.MarryStatus,PopulationInfo.HealthStatus,PopulationInfo.CommunityDuty,PopulationInfo.AccountType,PopulationInfo.HouseHoldPlace,PopulationInfo.MobilePhone,PopulationInfo.WorkStatus,PopulationInfo.Workplace,PopulationInfo.Remarks,PopulationInfo.Ptype,PopulationInfo.Canji,PopulationInfo.Youfu,PopulationInfo.Starlevel,PopulationInfo.Laomo,PopulationInfo.Hunying,PopulationInfo.Jiankang,PopulationInfo.Jiuye,PopulationInfo.Companyaddress,PopulationInfo.IsAccount,PopulationInfo.IsPregnant  ";
        string sql_from = " from PopulationInfo left join Account on Account.id = PopulationInfo.accountid left join AreaInfo on Account.aid = AreaInfo.id where AreaInfo.id = " + AreaId;

        string onlyonewhereStr = "";
        if (OnlyOneCheckBox.Checked)
        {
            onlyonewhereStr += " and PopulationInfo.Id in (select top 1 gbp.Id from PopulationInfo gbp where PopulationInfo.AccountId=gbp.AccountId order by case ISNULL(gbp.MobilePhone,'') when '' then 0 else 1 end desc,case gbp.relationship when '本人' then 1 when '户主' then 2 else 3 end asc)";
        }

        sql += sql_select + sql_from + QueryStr + whereStr;
        sql += " and PopulationInfo.Id not in ( select top " + stmstart * stmlimit + " PopulationInfo.Id " + sql_from + QueryStr + whereStr + onlyonewhereStr + orderby_sql + ")";

        sql += onlyonewhereStr;
        sql += orderby_sql;
        string sql_count = "select count(*) from PopulationInfo left join Account on Account.id = PopulationInfo.accountid left join AreaInfo on Account.aid = AreaInfo.id where AreaInfo.id = " + AreaId + QueryStr + whereStr + onlyonewhereStr;

        try
        {
            DataSet ds = DataRepository.Provider.ExecuteDataSet(CommandType.Text, sql);
            PopulationStore.DataSource = ds;
            PopulationStore.DataBind();
            DataSet ds_count = DataRepository.Provider.ExecuteDataSet(CommandType.Text, sql_count);

            (this.PopulationStore.Proxy[0] as PageProxy).Total = int.Parse(ds_count.Tables[0].Rows[0][0].ToString());
        }
        catch (Exception err)
        {
            throw (new Exception(sql));
        }
    }
    //上传Txt
    protected void UpLoadFile(object sender, DirectEventArgs e)
    {

        try
        {
            ExcelHelper exh = null;

            TList<SendMessageNumbers> smns = DataRepository.SendMessageNumbersProvider.Find("AreaId = '" + AreaId + "' AND GroupName = '" + NewGroupNameText + "'");

            if (smns.Count > 0)
            {
                X.Msg.Alert("提示", "该组已经存在").Show();
            }
            else
            {
                string TempPath = HttpContext.Current.Server.MapPath("../../TempFile//");
                string oFileName = "o" + Session["Uid"].ToString() + "_" + Session["Sid"].ToString() + "_" + DateTime.Now.Hour.ToString()
                                                + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString()
                                               + DateTime.Now.Millisecond.ToString() + ".xls";

                string TempFileName = TempPath + oFileName;
                int i = 1;
                while (File.Exists(TempFileName))
                {
                    TempFileName = TempPath + "(" + (i++) + ")" + ".xls";
                }
                txtFileUpload.PostedFile.SaveAs(TempFileName);
                txtFileUpload.PostedFile.InputStream.Close();
                exh = new ExcelHelper(TempFileName);
                int j = 0;
                //开始循环写入数据 
                int noneCount = 0;
                for (int k = 1; k <= exh.GetWorkSheetUserRangRowsCount(); k++)
                {
                    if (noneCount >= 15) {
                        break;
                    }
                    if (string.IsNullOrEmpty(exh.GetCellValue(k, 1).ToString()) && string.IsNullOrEmpty(exh.GetCellValue(k, 2).ToString()))
                    {
                        noneCount++;
                    }
                    else
                    {
                        noneCount = 0;
                        SendMessageNumbers smn = new SendMessageNumbers();
                        smn.AreaId = int.Parse(AreaId);
                        smn.UserId = int.Parse(Uid);
                        smn.Name = exh.GetCellValue(k, 1).ToString();
                        smn.PhoneName = exh.GetCellValue(k, 2).ToString();
                        smn.Remarks = exh.GetCellValue(k, 3).ToString();
                        smn.GroupName = NewGroupNameText.Text;
                        smn.CreateTime = DateTime.Now;
                        DataRepository.SendMessageNumbersProvider.Save(smn);
                    }
                }
                exh.KillExcelProcess();
            }




        }
        catch (Exception ex)
        {
            e.Success = false;
            e.ErrorMessage = ex.Message;
        }
        ComboBoxStoreDateBind();
    }
    //号码源
    protected void ComboBoxStore_RefreshData(object sender, StoreRefreshDataEventArgs e)
    {

        ComboBoxStoreDateBind();
    }
    private void ComboBoxStoreDateBind()
    {

        DataSet ds = DataRepository.Provider.ExecuteDataSet(CommandType.Text, "select GroupName as valueF,GroupName as showF from SendMessageNumbers where AreaId='" + AreaId + "' group by GroupName");

        DataRow newRow = ds.Tables[0].NewRow();

        if (AreaId.Equals("7")) { }
        else
        {
            newRow = ds.Tables[0].NewRow();
            newRow["showF"] = "一户一表";
            newRow["valueF"] = "1";
            ds.Tables[0].Rows.InsertAt(newRow, 0);
        }

        newRow = ds.Tables[0].NewRow();
        newRow["showF"] = "常用电话表";
        newRow["valueF"] = "0";
        ds.Tables[0].Rows.InsertAt(newRow, 0);



        ComboBoxStore.DataSource = ds;
        ComboBoxStore.DataBind();

    }

    protected void DeleteNewGroup(object sender, DirectEventArgs e)
    {
        if ((CboType.SelectedItem.Text.Equals("常用电话表") && CboType.SelectedItem.Value.Equals("0")) || (CboType.SelectedItem.Text.Equals("一户一表") && CboType.SelectedItem.Value.Equals("1")))
        {
            X.Msg.Alert("提示", "该类无法删除").Show();
        }
        else
        {
            try
            {
                TList<SendMessageNumbers> smns = DataRepository.SendMessageNumbersProvider.Find("AreaId = '" + AreaId + "' AND GroupName = '" + CboType.SelectedItem.Value + "'");
                DataRepository.SendMessageNumbersProvider.Delete(smns);
                ComboBoxStore.DataBind();
                CboType.SelectedItem.Value = "0";
                WinNumbers.ActiveIndex = 0;
                X.Msg.Alert("提示", "删除成功").Show();
            }
            catch
            {
                X.Msg.Alert("提示", "删除失败").Show();
            }
        }
    }

    protected void BtnAllSave_Click(object sender, DirectEventArgs e)
    {

        try
        {
            if (txtNumbers.Text.ToString().Trim() == "")
            {
                X.Msg.Alert("消息", "短信发送失败,原因:未选择号码").Show();
            }

            else if (txtcontent.Text.ToString().Trim() == "")
            {
                X.Msg.Alert("消息", "短信发送失败,原因:未填写短信内容").Show();
            }
            else
            {
                string code = txtNumbers.Text.ToString().Trim();
                string content = txtcontent.Text.ToString();
                if (!string.IsNullOrEmpty(txtName.Text.ToString()))
                {
                    content += "(" + txtName.Text.ToString() + ")";
                }

                string[] phonenumbers = code.Split(',');
                if (phonenumbers.Length <= hasNum || AreaId.Equals("7"))
                {
                    if (phonenumbers.Length > 0)
                    {

                        if (TimeSendCheckbox.Checked)
                        {
                            //开始时间现在 - 结束时间
                            DateTime beginTime = DateField_BeginDate.SelectedDate;


                            int send_year = DateField_BeginDate.SelectedDate.Year;
                            int send_month = DateField_BeginDate.SelectedDate.Month;
                            int send_day = DateField_BeginDate.SelectedDate.Day;
                            int send_hour = int.Parse(ComboBox_Hour.SelectedItem.Value);
                            int send_minute = int.Parse(ComboBox_Minute.SelectedItem.Value);
                            int send_second = int.Parse(ComboBox_Second.SelectedItem.Value);
                            DateTime ExpectDateTime = new DateTime(send_year, send_month, send_day, send_hour, send_minute, send_second);
                           // X.Msg.Alert("", ExpectDateTime.ToString()).Show();
                            
                            string Url = "http://218.108.2.165:9080/OpenMasService";
                            Sms Client = new Sms(Url);
                            string externcode = AreaId; //自定义扩展代码(模块)
                            string ApplicationID = "zjtxfw";
                            string Password = "vYdeV3$VjHVi";
                            OpenMasMessageSend omms = new OpenMasMessageSend();
                            omms.AreaId = int.Parse(AreaId);
                            omms.MessageId = "";
                            omms.MessageContent = content;
                            omms.PhoneNumbers = code;
                            omms.NumCount = phonenumbers.Length;
                            omms.CreateTime = DateTime.Now;
                            omms.ReportFlag = 0;
                            omms.Sid = int.Parse(Sid);
                            DataRepository.OpenMasMessageSendProvider.Save(omms);
                            string number = Client.SendMessage(phonenumbers, content, externcode, ApplicationID, Password, ExpectDateTime);
                            omms.MessageId = number;
                            DataRepository.OpenMasMessageSendProvider.Save(omms);
                             
                        }
                        else
                        {
                            
                            string Url = "http://218.108.2.165:9080/OpenMasService";
                            Sms Client = new Sms(Url);
                            string externcode = AreaId; //自定义扩展代码(模块)
                            string ApplicationID = "zjtxfw";
                            string Password = "vYdeV3$VjHVi";
                            OpenMasMessageSend omms = new OpenMasMessageSend();
                            omms.AreaId = int.Parse(AreaId);
                            omms.MessageId = "";
                            omms.MessageContent = content;
                            omms.PhoneNumbers = code;
                            omms.NumCount = phonenumbers.Length;
                            omms.CreateTime = DateTime.Now;
                            omms.ReportFlag = 0;
                            omms.Sid = int.Parse(Sid);
                            DataRepository.OpenMasMessageSendProvider.Save(omms);
                            string number = Client.SendMessage(phonenumbers, content, externcode, ApplicationID, Password);
                            omms.MessageId = number;
                            DataRepository.OpenMasMessageSendProvider.Save(omms);
                            
                        }
                        X.Msg.Alert("消息", "已发送").Show();
                    }
                    else
                    {
                        X.Msg.Alert("消息", "无号码").Show();
                    }
                }
                else
                {
                    X.Msg.Alert("错误", "发送短信量超出剩余量").Show();
                }
            }

        }
        catch (Exception ex)
        {
            e.ErrorMessage = ex.Message;
            e.Success = false;
        }
    }
    public string GetHtmlFromUrl(string url)
    {
        string strRet = null;
        if (url == null || url.Trim().ToString() == "")
        {
            return strRet;
        }
        string targeturl = url.Trim().ToString();
        try
        {
            HttpWebRequest hr = (HttpWebRequest)WebRequest.Create(targeturl);
            hr.UserAgent = "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)";
            hr.Method = "GET";
            hr.Timeout = 30 * 60 * 1000;
            WebResponse hs = hr.GetResponse();
            Stream sr = hs.GetResponseStream();
            StreamReader ser = new StreamReader(sr, Encoding.UTF8);
            strRet = ser.ReadToEnd();
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        return strRet;
    }
    protected void UsefulNumberStore_RefreshData(object sender, StoreRefreshDataEventArgs e)
    {

        ntmstart = e.Start / e.Limit;
        ntmlimit = e.Limit;
        UsefulNumberStoreDataBind();
    }

    private void UsefulNumberStoreDataBind()
    {
        if (ntmlimit <= 0)
        {
            ntmstart = 0;
            ntmlimit = 15;
        }
        int count;
        string whereStr = "area='" + AreaId + "'";
        if (!ShowEmptyCheckBox.Checked)
        {
            whereStr += " AND MobilePhone is not null AND MobilePhone <> '' ";
        }

        whereStr += C_Hid_QueryStr2.Text;

        TList<UsefulNumbers> stp = DataRepository.UsefulNumbersProvider.GetPaged(whereStr, "", ntmstart, ntmlimit, out count);
        UsefulNumberStore.DataSource = stp;
        UsefulNumberStore.DataBind();
        (this.UsefulNumberStore.Proxy[0] as PageProxy).Total = count;
    }
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Socialworkers.Data;
using Ext.Net;
using Socialworkers.Entities;
using System.Data;


public partial class admin_JiuBao_AreaGuide : System.Web.UI.Page
{
    private string Actual_AreaId = "";
    private string TypeKind = "";
    private string GroupId = "";
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!new PublicClass().IsLogin())
        {
            Response.Redirect("~/Error.aspx?type=0");
        }
        Actual_AreaId = Session["Areaid"].ToString();
        GroupId = Session["GroupId"].ToString();
        if (!GroupId.Equals("1"))
        {
            Response.Redirect("~/Error.aspx?type=2");
        }

        if (Request["TypeKind"] == null)
        {
            Response.Redirect("~/Error.aspx?type=1");

        }
        TypeKind = Request["TypeKind"].ToString();
        Hid_TypeKind.Text = TypeKind;


    }
    protected void AreaStore_RefreshData(object sender, StoreRefreshDataEventArgs e)
    {
        string sqlStr = "select id as AreaId,Name as AreaName,AccountCount=0,AreaImg=a.AreaImgPath from AreaInfo a where ParentId = " + Actual_AreaId;
        if (TypeKind.Equals("1"))
        {
            ExcelOut.Hidden = false;
            ExcelOutSep.Hidden = false;
            //社会组织
            sqlStr = "select id as AreaId,Name as AreaName,AccountCount=(select count(*) from Organization where areaid = a.id),AreaImg=a.AreaImgPath from AreaInfo a where ParentId = " + Actual_AreaId;
        }
        else if (TypeKind.Equals("2"))
        {
            //党员信息
            sqlStr = "select id as AreaId,Name as AreaName,AccountCount=(select count(*) from jbPartyInfo where areaid = a.id),AreaImg=a.AreaImgPath from AreaInfo a where ParentId = " + Actual_AreaId;
        }
        else if (TypeKind.Equals("3"))
        {
            //企业信息
            sqlStr = "select id as AreaId,Name as AreaName,AccountCount=(select count(*) from jbCorpInfo where Area = a.id),AreaImg=a.AreaImgPath from AreaInfo a where ParentId = " + Actual_AreaId;
        }
        else if (TypeKind.Equals("4"))
        {
            //一户一表
            sqlStr = "select id as AreaId,Name as AreaName,AccountCount=(select count(*) from Account where aid = a.id),PopulationCount=(select count(*) from PopulationInfo where accountid in (select id from Account where aid = a.id) and isDead = 0 AND (islivein <> 1 or islivein is null)),AreaImg=a.AreaImgPath from AreaInfo a where ParentId = " + Actual_AreaId;
        }
        else if (TypeKind.Equals("5"))
        {
            //出租
            sqlStr = "select id as AreaId,Name as AreaName,AccountCount=(select count(*) from Account where aid = a.id and account.HouseProperties like '%租%'),PopulationCount=(select count(*) from PopulationInfo where accountid in (select id from Account where aid = a.id and account.HouseProperties like '%租%') and isDead = 0 AND (islivein <> 1 or islivein is null)),AreaImg=a.AreaImgPath from AreaInfo a where ParentId = " + Actual_AreaId;
        }
        else if (TypeKind.Equals("6"))
        {
            //社工
            sqlStr = "select id as AreaId,Name as AreaName,AccountCount=(select count(*) from TsocialworkersTable where areaid = a.id),AreaImg=a.AreaImgPath from AreaInfo a where ParentId = " + Actual_AreaId;
        }
        else if (TypeKind.Equals("7"))
        {
            //计生
            sqlStr = "select id as AreaId,Name as AreaName,AccountCount=(select count(*) from PopulationInfo where accountid in (select id from Account where aid = a.id) and Womanstatus = '1'),AreaImg=a.AreaImgPath from AreaInfo a where ParentId = " + Actual_AreaId;
        }
        else if (TypeKind.Equals("8"))
        {
            //特殊人
            sqlStr = "select id as AreaId,Name as AreaName,AccountCount=(select count(*) from PopulationInfo where accountid in (select id from Account where aid = a.id) and (ptype <> '' or Canji<>'')),AreaImg=a.AreaImgPath from AreaInfo a where ParentId = " + Actual_AreaId;
        }
        else if (TypeKind.Equals("9"))
        {
            //死亡
            sqlStr = "select id as AreaId,Name as AreaName,AccountCount=(select count(*) from PopulationInfo where accountid in (select id from Account where aid = a.id) and isDead='1' ),AreaImg=a.AreaImgPath from AreaInfo a where ParentId = " + Actual_AreaId;
        }
        else if (TypeKind.Equals("10"))
        {
            //失业
            sqlStr = "select id as AreaId,Name as AreaName,AccountCount=(select count(*) from PopulationInfo where accountid in (select id from Account where aid = a.id) and isDead='1' ),AreaImg=a.AreaImgPath from AreaInfo a where ParentId = " + Actual_AreaId;
        }
        else if (TypeKind.Equals("11"))
        {
            //退休
            sqlStr = "select id as AreaId,Name as AreaName,AccountCount=(select count(*) from PopulationInfo where accountid in (select id from Account where aid = a.id) and isDead='1' ),AreaImg=a.AreaImgPath from AreaInfo a where ParentId = " + Actual_AreaId;
        }
        else if (TypeKind.Equals("12"))
        {
            //4050
            sqlStr = "select id as AreaId,Name as AreaName,AccountCount=(select count(*) from PopulationInfo where accountid in (select id from Account where aid = a.id) and isDead='1' ),AreaImg=a.AreaImgPath from AreaInfo a where ParentId = " + Actual_AreaId;
        }
        else if (TypeKind.Equals("13"))
        {
            //8090
            sqlStr = "select id as AreaId,Name as AreaName,AccountCount=(select count(*) from PopulationInfo where accountid in (select id from Account where aid = a.id) and isDead='1' ),AreaImg=a.AreaImgPath from AreaInfo a where ParentId = " + Actual_AreaId;
        }
        else if (TypeKind.Equals("14"))
        {
            //规章制度
            sqlStr = "select id as AreaId,Name as AreaName,AccountCount=(select count(*) from Rules where areaid=a.id),AreaImg=a.AreaImgPath from AreaInfo a where ParentId = " + Actual_AreaId;
        }
        sqlStr += " order by orderid";
        DataSet ds = DataRepository.Provider.ExecuteDataSet(CommandType.Text, sqlStr);
        int AccountCountSum = 0;
        int PopulationCountSum = 0;
        if (ds.Tables[0].Rows.Count == 0)
        {
            CardPanel1StatusBarLabel.Text = "无信息";
        }
        else
        {
            ds.Tables[0].Columns.Add("ShowCountStr");
            int i = 0;
            for (i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                AccountCountSum += int.Parse(ds.Tables[0].Rows[i]["AccountCount"].ToString());
                if (TypeKind.Equals("1"))
                {
                    ds.Tables[0].Rows[i]["ShowCountStr"] = "共" + ds.Tables[0].Rows[i]["AccountCount"].ToString() + "个社会组织";
                }
                else if (TypeKind.Equals("2"))
                {
                    ds.Tables[0].Rows[i]["ShowCountStr"] = "共" + ds.Tables[0].Rows[i]["AccountCount"].ToString() + "个党员";
                }
                else if (TypeKind.Equals("3"))
                {
                    ds.Tables[0].Rows[i]["ShowCountStr"] = "共" + ds.Tables[0].Rows[i]["AccountCount"].ToString() + "家企业";
                }
                else if (TypeKind.Equals("4") || TypeKind.Equals("5"))
                {
                    PopulationCountSum += int.Parse(ds.Tables[0].Rows[i]["PopulationCount"].ToString());
                    ds.Tables[0].Rows[i]["ShowCountStr"] = "共" + ds.Tables[0].Rows[i]["AccountCount"].ToString() + "户," + ds.Tables[0].Rows[i]["PopulationCount"].ToString() + "人";
                }
                else if (TypeKind.Equals("6"))
                {
                    ds.Tables[0].Rows[i]["ShowCountStr"] = "共" + ds.Tables[0].Rows[i]["AccountCount"].ToString() + "人";
                }
                else if (TypeKind.Equals("7"))
                {
                    ds.Tables[0].Rows[i]["ShowCountStr"] = "共" + ds.Tables[0].Rows[i]["AccountCount"].ToString() + "人";
                }
                else if (TypeKind.Equals("8"))
                {
                    ds.Tables[0].Rows[i]["ShowCountStr"] = "共" + ds.Tables[0].Rows[i]["AccountCount"].ToString() + "人";
                }
                else if (TypeKind.Equals("9"))
                {
                    ds.Tables[0].Rows[i]["ShowCountStr"] = "共" + ds.Tables[0].Rows[i]["AccountCount"].ToString() + "人";
                }
                else if (TypeKind.Equals("14"))
                {
                    ds.Tables[0].Rows[i]["ShowCountStr"] = "共" + ds.Tables[0].Rows[i]["AccountCount"].ToString() + "条制度";
                }
            }
            if (TypeKind.Equals("1"))
            {
                CardPanel1StatusBarLabel.Text = i + "个社区,共" + AccountCountSum + "社会组织";
            }
            else if (TypeKind.Equals("2"))
            {
                CardPanel1StatusBarLabel.Text = i + "个社区,共" + AccountCountSum + "党员";
            }
            else if (TypeKind.Equals("3"))
            {
                CardPanel1StatusBarLabel.Text = i + "个社区,共" + AccountCountSum + "家企业";
            }
            else if (TypeKind.Equals("4") || TypeKind.Equals("5"))
            {
                CardPanel1StatusBarLabel.Text = i + "个社区,共" + AccountCountSum + "户," + PopulationCountSum + "人";
            }
            else if (TypeKind.Equals("6"))
            {
                CardPanel1StatusBarLabel.Text = i + "个社区,共" + AccountCountSum + "人";
            }
            else if (TypeKind.Equals("7"))
            {
                CardPanel1StatusBarLabel.Text = i + "个社区,共" + AccountCountSum + "人";
            }
            else if (TypeKind.Equals("8"))
            {
                CardPanel1StatusBarLabel.Text = i + "个社区,共" + AccountCountSum + "人";
            }
            else if (TypeKind.Equals("9"))
            {
                CardPanel1StatusBarLabel.Text = i + "个社区,共" + AccountCountSum + "人";
            }
            else if (TypeKind.Equals("14"))
            {
                CardPanel1StatusBarLabel.Text = i + "个社区,共" + AccountCountSum + "条制度";
            }
        }
        AreaStore.DataSource = ds;
        AreaStore.DataBind();
    }

    protected void ToNewExcel(object sender, EventArgs e)
    {

        if (TypeKind.Equals("1"))
        {
            string sql = "select (select Name from AreaInfo where Id=[Organization].AreaId) as 社区"
                        + ",[Name] as 组织名称"
                        + ",(case [Creater] when 1 then '公益服务类' when 2 then '社会事务类' when 3 then '文化体育类' when 4 then '慈善救助类' when 5 then '法律维权类' when 6 then '其他类' else ''  end)  as 组织类别"
                         + ",[CapName] as 负责人"
                        + ",[CapTel] as 联系电话"
                         + ",[TeamCreateTime] as 成立时间"
                        + ",[PeopleCount]  as 会员人数"
                         + ",[TeacherTel] as 备案登记号"
                        + ",[TeacherName] as 业务主管单位"
                        + ",[TeamerName] as 队员姓名"
                        + ",[TeamRemarks] as 组织简介"
                        + ",[TeamAward] as 获得荣誉 from Organization ORDER BY (select OrderId from areainfo where id = [Organization].AreaId)";
            DataSet ds = DataRepository.Provider.ExecuteDataSet(CommandType.Text, sql);

            string strFileName = "社会组织信息";

            Response.AddHeader("content-disposition", "attachment; filename=" + Server.UrlEncode(strFileName) + ".xls");
            Response.ContentType = "application/excel";


            Response.Write(StaticPublic.ToExcel(ds, new int[] { }));

            Response.End();
        }
    }
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Ext.Net;
using Socialworkers.Data;
using Socialworkers.Entities;
using System.Data;
using System.IO;

public partial class admin_JiuBao_ScoreCheckTotal : System.Web.UI.Page
{
    private int stmstart = 0;
    private int stmlimit = 0;
    private string AreaId = "";
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!new PublicClass().IsLogin())
        {
            Response.Redirect("~/Error.aspx?type=0");
        }
        AreaId = Session["Areaid"].ToString();


        if (!IsPostBack && !X.IsAjaxRequest)
        {
            int year = DateTime.Now.Year;
            for (int i = 2012; i <= year+1; i++)
            {
                SelectYearCombo.Items.Add(new Ext.Net.ListItem(i.ToString(), i.ToString()));
            }
            SelectYearCombo.SelectedItem.Value = year.ToString();
            int month = DateTime.Now.AddMonths(-1).Month;
            SelectMonthCombo.SelectedItem.Value = month.ToString();
        }
    }
    protected void CorpInfoStore_RefreshData(object sender, Ext.Net.StoreRefreshDataEventArgs e)
    {
        stmstart = e.Start / e.Limit;
        stmlimit = e.Limit;
        CorpInfogridBind();
    }
    private string getAreaStr()
    {
        string str = "";
        if (AreaId.Equals("7"))
        {
            str = " in (select id from AreaInfo where parentId = '7') ";
        }
        else
        {
            str = "  = '" + AreaId + "' ";
        }
        return str;
    }
    private void CorpInfogridBind()
    {
        
        string sql = "select WorkAdmin.swid," +
            "Shequ=(select Name from AreaInfo where id = (select areaid from TsocialworkersTable where id = WorkAdmin.swid))," +
            "ShequOrder=(select OrderId from AreaInfo where id = (select areaid from TsocialworkersTable where id = WorkAdmin.swid))," +
            "sum1=(select count(*) from Workrecord sum1_wr left join WorkAdmin sum1_wa on sum1_wa.id = sum1_wr.pid where sum1_wa.swid = WorkAdmin.swid and sum1_wr.worktype = '日常事务' " + getWhereStr("sum1_wa") + " )," +
            "sum2=(select count(*) from Workrecord sum1_wr left join WorkAdmin sum1_wa on sum1_wa.id = sum1_wr.pid where sum1_wa.swid = WorkAdmin.swid and sum1_wr.worktype = '事件处理' " + getWhereStr("sum1_wa") + " )," +
            "sum3=(select count(*) from Workrecord sum1_wr left join WorkAdmin sum1_wa on sum1_wa.id = sum1_wr.pid where sum1_wa.swid = WorkAdmin.swid and sum1_wr.worktype = '走访' " + getWhereStr("sum1_wa") + ")," +
            "sum5=(select count(*) from Dutyrecord where swid = (select name from TsocialworkersTable where id = WorkAdmin.swid) and areaid = (select areaid from TsocialworkersTable where id = WorkAdmin.swid) " + getWhereStr2("Dutyrecord") + ")," +
            "name = (select name from TsocialworkersTable where id = WorkAdmin.swid)," +
            "sum4 =count(*)" +
            " from Workrecord left join WorkAdmin on WorkAdmin.id = Workrecord.pid " +
            "where WorkAdmin.swid in (select id from TsocialworkersTable where areaId ='" + AreaId + "')  " + getWhereStr("WorkAdmin") + "  group by WorkAdmin.swid";

        sql = "select  Duties,orderid,areaid,swid=id,name " +
            ",Shequ=(select Name from AreaInfo where id = TsocialworkersTable.areaId) " +
            ",ShequOrder=(select OrderId from AreaInfo where id = TsocialworkersTable.areaId) " +
            ",sum1=(select count(*) from Workrecord sum1_wr left join WorkAdmin sum1_wa on sum1_wa.id = sum1_wr.pid where sum1_wa.swid = TsocialworkersTable.id and sum1_wr.worktype = '日常事务' " + getWhereStr("sum1_wa") + " ) " +
            ",sum2=(select count(*) from Workrecord sum1_wr left join WorkAdmin sum1_wa on sum1_wa.id = sum1_wr.pid where sum1_wa.swid = TsocialworkersTable.id and sum1_wr.worktype = '事件处理' " + getWhereStr("sum1_wa") + " ) " +
            ",sum3=(select count(*) from Workrecord sum1_wr left join WorkAdmin sum1_wa on sum1_wa.id = sum1_wr.pid where sum1_wa.swid = TsocialworkersTable.id and sum1_wr.worktype = '走访' " + getWhereStr("sum1_wa") + " ) " +
            ",sum5=(select count(*) from Dutyrecord where swid = TsocialworkersTable.Name and areaid = TsocialworkersTable.AreaId " + getWhereStr2("Dutyrecord") + ") " +
            ",sum7=(select count(*) from Dutyrecord where swid = TsocialworkersTable.Name and areaid = TsocialworkersTable.AreaId " + getWhereStr2("Dutyrecord") + ") " +
            ",scoreA=(select ScoreAll from NewWorkerScore where Name = TsocialworkersTable.Id and areaid = TsocialworkersTable.AreaId " + getWhereStr3("NewWorkerScore") + ") " +
            ",scoreB=(select ScoreAllB from NewWorkerScore where Name = TsocialworkersTable.Id and areaid = TsocialworkersTable.AreaId " + getWhereStr3("NewWorkerScore") + ") " +

            " from TsocialworkersTable where areaid " + getAreaStr();


        if (!string.IsNullOrEmpty(C_Hid_QueryStr.Text))
        {
            sql += " and name like '%" + C_Hid_QueryStr.Text + "%'";
        }

        sql += " order by areaid,isnull(OrderId,9999),id";
       
        int dayNum = DateTime.DaysInMonth(int.Parse(SelectYearCombo.SelectedItem.Value), int.Parse(SelectMonthCombo.SelectedItem.Value));
       
        DataSet ds = DataRepository.Provider.ExecuteDataSet(CommandType.Text, sql);
        for (int i = 1; i <= 12; i++)
        {
            ds.Tables[0].Columns.Add("dSum" + i);
        }


        ds.Tables[0].Columns.Add("sum3Str");
        ds.Tables[0].Columns.Add("sumA");
        ds.Tables[0].Columns.Add("sumB");
        ds.Tables[0].Columns.Add("colorflag");

        ds.Tables[0].Columns.Add("ChuQin", new Double().GetType());
        ds.Tables[0].Columns.Add("ShiJia", new Double().GetType());
        ds.Tables[0].Columns.Add("BingJia", new Double().GetType());
        ds.Tables[0].Columns.Add("QueJin", new Double().GetType());
        ds.Tables[0].Columns.Add("NianXiu", new Double().GetType());
        ds.Tables[0].Columns.Add("JieJia", new Double().GetType());
        ds.Tables[0].Columns.Add("ChanJia", new Double().GetType());
        ds.Tables[0].Columns.Add("TiaoXiu", new Double().GetType());
        ds.Tables[0].Columns.Add("SangJia", new Double().GetType());
        ds.Tables[0].Columns.Add("HunJia", new Double().GetType());
        ds.Tables[0].Columns.Add("ChiDao", new Double().GetType());
        ds.Tables[0].Columns.Add("ZaoTui", new Double().GetType());


        for (int k = 0; k < ds.Tables[0].Rows.Count; k++)
        {
            ds.Tables[0].Rows[k]["colorflag"] = 1;
            if (ds.Tables[0].Rows[k]["scoreA"] == null || ds.Tables[0].Rows[k]["scoreA"].ToString() == "")
            {
                ds.Tables[0].Rows[k]["sumA"] = "<span style='color:#A2A2A2;'>未填<span>";
            }
            else if (int.Parse(ds.Tables[0].Rows[k]["scoreA"].ToString()) < 100)
            {
                ds.Tables[0].Rows[k]["sumA"] = "<span style='color:red;'>" + ds.Tables[0].Rows[k]["scoreA"].ToString() + "<span>";
                ds.Tables[0].Rows[k]["colorflag"] = 0;
            }
            else
            {
                ds.Tables[0].Rows[k]["sumA"] = ds.Tables[0].Rows[k]["scoreA"].ToString();
            }

            if (ds.Tables[0].Rows[k]["scoreB"] == null || ds.Tables[0].Rows[k]["scoreB"].ToString() == "")
            {
                ds.Tables[0].Rows[k]["sumB"] = "<span style='color:#A2A2A2;'>未填<span>";
            }
            else if (int.Parse(ds.Tables[0].Rows[k]["scoreB"].ToString()) < 100)
            {
                ds.Tables[0].Rows[k]["sumB"] = "<span style='color:red;'>" + ds.Tables[0].Rows[k]["scoreB"].ToString() + "<span>";
                ds.Tables[0].Rows[k]["colorflag"] = 0;
            }
            else
            {
                ds.Tables[0].Rows[k]["sumB"] = ds.Tables[0].Rows[k]["scoreB"].ToString();
            }

            if (ds.Tables[0].Rows[k]["sum3"] != null && int.Parse(ds.Tables[0].Rows[k]["sum3"].ToString()) < 30)
            {
                ds.Tables[0].Rows[k]["sum3Str"] = "<span style='color:red;'>" + ds.Tables[0].Rows[k]["sum3"].ToString() + "<span>";
                ds.Tables[0].Rows[k]["colorflag"] = 0;
            }
            else
            {
                ds.Tables[0].Rows[k]["sum3Str"] = ds.Tables[0].Rows[k]["sum3"];
            }


            string sql2 = "SELECT Top 1 *  FROM NewWorkCheck WHERE Sid='" + ds.Tables[0].Rows[k]["swid"] + "' AND Year = '" + SelectYearCombo.SelectedItem.Value + "' AND Month = '" + SelectMonthCombo.SelectedItem.Value + "' ORDER BY Sid";

            DataSet ds2 = DataRepository.Provider.ExecuteDataSet(CommandType.Text, sql2);



            

            for (int i = 0; i < ds2.Tables[0].Rows.Count; i++)
            {

                string ChuQin = ds2.Tables[0].Rows[i]["ChuQin"].ToString();
                string[] tmp_arr = ChuQin.Split(',');
                if (tmp_arr.Length > 1)
                {
                    ds.Tables[0].Rows[k]["ChuQin"] = Double.Parse(ds.Tables[0].Rows[k]["ChuQin"].ToString().Equals("") ? "0" : ds.Tables[0].Rows[k]["ChuQin"].ToString()) + 0.5 * (tmp_arr.Length - 1);
                }

                string ShiJia = ds2.Tables[0].Rows[i]["ShiJia"].ToString();
                tmp_arr = ShiJia.Split(',');
                if (tmp_arr.Length > 1)
                {
                    ds.Tables[0].Rows[k]["ShiJia"] = Double.Parse(ds.Tables[0].Rows[k]["ShiJia"].ToString().Equals("") ? "0" : ds.Tables[0].Rows[k]["ShiJia"].ToString()) + 0.5 * (tmp_arr.Length - 1);
                }

                string BingJia = ds2.Tables[0].Rows[i]["BingJia"].ToString();
                tmp_arr = BingJia.Split(',');
                if (tmp_arr.Length > 1)
                {
                    ds.Tables[0].Rows[k]["BingJia"] = Double.Parse(ds.Tables[0].Rows[k]["BingJia"].ToString().Equals("") ? "0" : ds.Tables[0].Rows[k]["BingJia"].ToString()) + 0.5 * (tmp_arr.Length - 1);
                }

                string QueJin = ds2.Tables[0].Rows[i]["QueJin"].ToString();
                tmp_arr = QueJin.Split(',');
                if (tmp_arr.Length > 1)
                {
                    ds.Tables[0].Rows[k]["QueJin"] = Double.Parse(ds.Tables[0].Rows[k]["QueJin"].ToString().Equals("") ? "0" : ds.Tables[0].Rows[k]["QueJin"].ToString()) + 0.5 * (tmp_arr.Length - 1);
                }

                string NianXiu = ds2.Tables[0].Rows[i]["NianXiu"].ToString();
                tmp_arr = NianXiu.Split(',');
                if (tmp_arr.Length > 1)
                {
                    ds.Tables[0].Rows[k]["NianXiu"] = Double.Parse(ds.Tables[0].Rows[k]["NianXiu"].ToString().Equals("") ? "0" : ds.Tables[0].Rows[k]["NianXiu"].ToString()) + 0.5 * (tmp_arr.Length - 1);
                }

                string JieJia = ds2.Tables[0].Rows[i]["JieJia"].ToString();
                tmp_arr = JieJia.Split(',');
                if (tmp_arr.Length > 1)
                {
                    ds.Tables[0].Rows[k]["JieJia"] = Double.Parse(ds.Tables[0].Rows[k]["JieJia"].ToString().Equals("") ? "0" : ds.Tables[0].Rows[k]["JieJia"].ToString()) + 0.5 * (tmp_arr.Length - 1);
                }

                string ChanJia = ds2.Tables[0].Rows[i]["ChanJia"].ToString();
                tmp_arr = ChanJia.Split(',');
                if (tmp_arr.Length > 1)
                {
                    ds.Tables[0].Rows[k]["ChanJia"] = Double.Parse(ds.Tables[0].Rows[k]["ChanJia"].ToString().Equals("") ? "0" : ds.Tables[0].Rows[k]["ChanJia"].ToString()) + 0.5 * (tmp_arr.Length - 1);
                }

                string TiaoXiu = ds2.Tables[0].Rows[i]["TiaoXiu"].ToString();
                tmp_arr = TiaoXiu.Split(',');
                if (tmp_arr.Length > 1)
                {
                    ds.Tables[0].Rows[k]["TiaoXiu"] = Double.Parse(ds.Tables[0].Rows[k]["TiaoXiu"].ToString().Equals("") ? "0" : ds.Tables[0].Rows[k]["TiaoXiu"].ToString()) + 0.5 * (tmp_arr.Length - 1);
                }

                string SangJia = ds2.Tables[0].Rows[i]["SangJia"].ToString();
                tmp_arr = SangJia.Split(',');
                if (tmp_arr.Length > 1)
                {
                    ds.Tables[0].Rows[k]["SangJia"] = Double.Parse(ds.Tables[0].Rows[k]["SangJia"].ToString().Equals("") ? "0" : ds.Tables[0].Rows[k]["SangJia"].ToString()) + 0.5 * (tmp_arr.Length - 1);
                }

                string HunJia = ds2.Tables[0].Rows[i]["HunJia"].ToString();
                tmp_arr = HunJia.Split(',');
                if (tmp_arr.Length > 1)
                {
                    ds.Tables[0].Rows[k]["HunJia"] = Double.Parse(ds.Tables[0].Rows[k]["HunJia"].ToString().Equals("") ? "0" : ds.Tables[0].Rows[k]["HunJia"].ToString()) + 0.5 * (tmp_arr.Length - 1);
                }

                string ChiDao = ds2.Tables[0].Rows[i]["ChiDao"].ToString();
                tmp_arr = ChiDao.Split(',');
                if (tmp_arr.Length > 1)
                {
                    ds.Tables[0].Rows[k]["ChiDao"] = Double.Parse(ds.Tables[0].Rows[k]["ChiDao"].ToString().Equals("") ? "0" : ds.Tables[0].Rows[k]["ChiDao"].ToString()) + 1 * (tmp_arr.Length - 1);
                }

                string ZaoTui = ds2.Tables[0].Rows[i]["ZaoTui"].ToString();
                tmp_arr = ZaoTui.Split(',');
                if (tmp_arr.Length > 1)
                {
                    ds.Tables[0].Rows[k]["ZaoTui"] = Double.Parse(ds.Tables[0].Rows[k]["ZaoTui"].ToString().Equals("") ? "0" : ds.Tables[0].Rows[k]["ZaoTui"].ToString()) + 1 * (tmp_arr.Length - 1);
                }
            }

            /*
            string sql2 = "select ";

            for (int i = 1; i <= 31; i++)
            {
                sql2 += "wc.d" + i + ",";
            }
            sql2 += " noneStr='' from WorkCheck wc where wc.pid = '" + ds.Tables[0].Rows[k]["swid"] + "' and wc.lid in (select id from WorkCheckList where AreaId ='" + ds.Tables[0].Rows[k]["areaid"] + "' and CheckDate='" + SelectYearCombo.SelectedItem.Value + "-" + SelectMonthCombo.SelectedItem.Value + "-1')";

            DataSet ds2 = DataRepository.Provider.ExecuteDataSet(CommandType.Text, sql2);


            for (int j = 1; j <= 12; j++)
            {
                if (ds2.Tables[0].Rows.Count <= 0)
                {
                    ds.Tables[0].Rows[k]["dSum" + j] = "<span style='color:#A2A2A2;'>未填<span>";
                }
                else
                {
                    ds.Tables[0].Rows[k]["dSum" + j] = 0;
                }
            }

            for (int i = 0; i < ds2.Tables[0].Rows.Count; i++)
            {


                for (int j = 1; j <= dayNum; j++)
                {
                    string str = ds2.Tables[0].Rows[i]["d" + j].ToString();
                    switch (str)
                    {


                        case "出勤":
                            ds.Tables[0].Rows[k]["dSum1"] = int.Parse(ds.Tables[0].Rows[k]["dSum1"].ToString()) + 1;
                            break;
                        case "迟到":
                            ds.Tables[0].Rows[k]["dSum2"] = int.Parse(ds.Tables[0].Rows[k]["dSum2"].ToString()) + 1;
                            ds.Tables[0].Rows[k]["colorflag"] = 0;
                            break;
                        case "早退":
                            ds.Tables[0].Rows[k]["dSum3"] = int.Parse(ds.Tables[0].Rows[k]["dSum3"].ToString()) + 1;
                            ds.Tables[0].Rows[k]["colorflag"] = 0;
                            break;
                        case "事假":
                            ds.Tables[0].Rows[k]["dSum4"] = int.Parse(ds.Tables[0].Rows[k]["dSum4"].ToString()) + 1;
                            ds.Tables[0].Rows[k]["colorflag"] = 0;
                            break;
                        case "病假":
                            ds.Tables[0].Rows[k]["dSum5"] = int.Parse(ds.Tables[0].Rows[k]["dSum5"].ToString()) + 1;
                            ds.Tables[0].Rows[k]["colorflag"] = 0;
                            break;
                        case "年休":
                            ds.Tables[0].Rows[k]["dSum6"] = int.Parse(ds.Tables[0].Rows[k]["dSum6"].ToString()) + 1;
                            ds.Tables[0].Rows[k]["colorflag"] = 0;
                            break;
                        case "节假日":
                            ds.Tables[0].Rows[k]["dSum7"] = int.Parse(ds.Tables[0].Rows[k]["dSum7"].ToString()) + 1;
                            break;
                        case "产假":
                            ds.Tables[0].Rows[k]["dSum8"] = int.Parse(ds.Tables[0].Rows[k]["dSum8"].ToString()) + 1;
                            ds.Tables[0].Rows[k]["colorflag"] = 0;
                            break;
                        case "调休":
                            ds.Tables[0].Rows[k]["dSum9"] = int.Parse(ds.Tables[0].Rows[k]["dSum9"].ToString()) + 1;
                            ds.Tables[0].Rows[k]["colorflag"] = 0;
                            break;
                        case "丧假":
                            ds.Tables[0].Rows[k]["dSum10"] = int.Parse(ds.Tables[0].Rows[k]["dSum10"].ToString()) + 1;
                            ds.Tables[0].Rows[k]["colorflag"] = 0;
                            break;
                        case "婚假":
                            ds.Tables[0].Rows[k]["dSum11"] = int.Parse(ds.Tables[0].Rows[k]["dSum11"].ToString()) + 1;
                            ds.Tables[0].Rows[k]["colorflag"] = 0;
                            break;
                        case "缺勤":
                            ds.Tables[0].Rows[k]["dSum12"] = int.Parse(ds.Tables[0].Rows[k]["dSum12"].ToString()) + 1;
                            ds.Tables[0].Rows[k]["colorflag"] = 0;
                            break;
                    }
                }
            }*/

        }
        CorpInfoStore.DataSource = ds;
        CorpInfoStore.DataBind();

    }
    private string getWhereStr(string pre)
    {

        string where = "";
        if (SelectYearCombo.SelectedItem != null && SelectMonthCombo.SelectedItem != null)
        {
            where += " and " + pre + ".CreateDay >= '" + SelectYearCombo.SelectedItem.Value + "-" + SelectMonthCombo.SelectedItem.Value + "-1' and " + pre + ".CreateDay < '" + (new DateTime(int.Parse(SelectYearCombo.SelectedItem.Value), int.Parse(SelectMonthCombo.SelectedItem.Value), 1)).AddMonths(1).ToString() + "'";
        }

        return where;
    }
    private string getWhereStr2(string pre)
    {

        string where = "";
        if (SelectYearCombo.SelectedItem != null && SelectMonthCombo.SelectedItem != null)
        {
            where += " and " + pre + ".Dutytime >= '" + SelectYearCombo.SelectedItem.Value + "-" + SelectMonthCombo.SelectedItem.Value + "-1' and " + pre + ".Dutytime < '" + (new DateTime(int.Parse(SelectYearCombo.SelectedItem.Value), int.Parse(SelectMonthCombo.SelectedItem.Value), 1)).AddMonths(1).ToString() + "'";
        }

        return where;
    }
    private string getWhereStr3(string pre)
    {

        string where = "";
        if (SelectYearCombo.SelectedItem != null && SelectMonthCombo.SelectedItem != null)
        {
            where += " and " + pre + ".SelectDate = '" + SelectYearCombo.SelectedItem.Value + "-" + SelectMonthCombo.SelectedItem.Value + "-1'";
        }

        return where;
    }
    [DirectMethod(ShowMask = true, Msg = "加载中...", Namespace = "X")]
    public void ShowIframeWindow(string id)
    {


    }

    protected void ToNewExcel(object sender, EventArgs e)
    {



        string sql = "select  社区=(select Name from AreaInfo where id = TsocialworkersTable.areaId),name as 姓名,Duties as 职务 " +
       ",记录天数=(select count(*) from  WorkAdmin sum1_wa where sum1_wa.swid = TsocialworkersTable.id " + getWhereStr("sum1_wa") + " ) " +
       ",日常事务=(select count(*) from Workrecord sum1_wr left join WorkAdmin sum1_wa on sum1_wa.id = sum1_wr.pid where sum1_wa.swid = TsocialworkersTable.id and sum1_wr.worktype = '日常事务' " + getWhereStr("sum1_wa") + " ) " +
       ",事件处理=(select count(*) from Workrecord sum1_wr left join WorkAdmin sum1_wa on sum1_wa.id = sum1_wr.pid where sum1_wa.swid = TsocialworkersTable.id and sum1_wr.worktype = '事件处理' " + getWhereStr("sum1_wa") + " ) " +
       ",走访=(select count(*) from Workrecord sum1_wr left join WorkAdmin sum1_wa on sum1_wa.id = sum1_wr.pid where sum1_wa.swid = TsocialworkersTable.id and sum1_wr.worktype = '走访' " + getWhereStr("sum1_wa") + " ) " +
       ",值班=(select count(*) from Dutyrecord where swid = TsocialworkersTable.Name and areaid = TsocialworkersTable.AreaId " + getWhereStr2("Dutyrecord") + ") " +
       " from TsocialworkersTable where  areaid " + getAreaStr() + " order by (select OrderId from AreaInfo where id = TsocialworkersTable.areaId),TsocialworkersTable.areaId,isnull(TsocialworkersTable.OrderId,9999)";

        sql = "select id as swid,areaId,社区=(select Name from AreaInfo where id = TsocialworkersTable.areaId),name as 姓名,Duties as 职务 " +
           ",scoreA=(select ScoreAll from NewWorkerScore where Name = TsocialworkersTable.Id and areaid = TsocialworkersTable.AreaId " + getWhereStr3("NewWorkerScore") + ") " +
            ",scoreB=(select ScoreAllB from NewWorkerScore where Name = TsocialworkersTable.Id and areaid = TsocialworkersTable.AreaId " + getWhereStr3("NewWorkerScore") + ") " +

            " from TsocialworkersTable where areaid " + getAreaStr();


        if (!string.IsNullOrEmpty(C_Hid_QueryStr.Text))
        {
            sql += " and name like '%" + C_Hid_QueryStr.Text + "%'";
        }

        sql += " order by (select orderid from AreaInfo where id = TsocialworkersTable.areaid), areaid,isnull(OrderId,9999),id";
        int dayNum = DateTime.DaysInMonth(int.Parse(SelectYearCombo.SelectedItem.Value), int.Parse(SelectMonthCombo.SelectedItem.Value));
        DataSet ds = DataRepository.Provider.ExecuteDataSet(CommandType.Text, sql);





        ds.Tables[0].Columns.Add("出勤");
        ds.Tables[0].Columns.Add("迟到");
        ds.Tables[0].Columns.Add("早退");
        ds.Tables[0].Columns.Add("事假");
        ds.Tables[0].Columns.Add("病假");
        ds.Tables[0].Columns.Add("年休");
        ds.Tables[0].Columns.Add("节假日");
        ds.Tables[0].Columns.Add("产假");
        ds.Tables[0].Columns.Add("调休");
        ds.Tables[0].Columns.Add("丧假");
        ds.Tables[0].Columns.Add("婚假");
        ds.Tables[0].Columns.Add("缺勤");
        ds.Tables[0].Columns.Add("自评分");
        ds.Tables[0].Columns.Add("考评分");


        for (int k = 0; k < ds.Tables[0].Rows.Count; k++)
        {

            if (ds.Tables[0].Rows[k]["scoreA"] == null || ds.Tables[0].Rows[k]["scoreA"].ToString() == "")
            {
                ds.Tables[0].Rows[k]["自评分"] = "未填";
            }
            else if (int.Parse(ds.Tables[0].Rows[k]["scoreA"].ToString()) < 100)
            {
                ds.Tables[0].Rows[k]["自评分"] = "" + ds.Tables[0].Rows[k]["scoreA"].ToString() + "";

            }
            else
            {
                ds.Tables[0].Rows[k]["自评分"] = ds.Tables[0].Rows[k]["scoreA"].ToString();
            }

            if (ds.Tables[0].Rows[k]["scoreB"] == null || ds.Tables[0].Rows[k]["scoreB"].ToString() == "")
            {
                ds.Tables[0].Rows[k]["考评分"] = "未填";
            }
            else if (int.Parse(ds.Tables[0].Rows[k]["scoreA"].ToString()) < 100)
            {
                ds.Tables[0].Rows[k]["考评分"] = "" + ds.Tables[0].Rows[k]["scoreB"].ToString() + "";

            }
            else
            {
                ds.Tables[0].Rows[k]["考评分"] = ds.Tables[0].Rows[k]["scoreB"].ToString();
            }



            string sql2 = "select ";

            for (int i = 1; i <= 31; i++)
            {
                sql2 += "wc.d" + i + ",";
            }
            sql2 += " noneStr='' from WorkCheck wc where wc.pid = '" + ds.Tables[0].Rows[k]["swid"] + "' and wc.lid in (select id from WorkCheckList where AreaId ='" + ds.Tables[0].Rows[k]["areaid"] + "' and CheckDate='" + SelectYearCombo.SelectedItem.Value + "-" + SelectMonthCombo.SelectedItem.Value + "-1')";



            DataSet ds2 = DataRepository.Provider.ExecuteDataSet(CommandType.Text, sql2);


            for (int j = 1; j <= 12; j++)
            {
                if (ds2.Tables[0].Rows.Count <= 0)
                {
                    ds.Tables[0].Rows[k]["出勤"] = "未填";
                    ds.Tables[0].Rows[k]["迟到"] = "未填";
                    ds.Tables[0].Rows[k]["早退"] = "未填";
                    ds.Tables[0].Rows[k]["事假"] = "未填";
                    ds.Tables[0].Rows[k]["病假"] = "未填";
                    ds.Tables[0].Rows[k]["年休"] = "未填";
                    ds.Tables[0].Rows[k]["节假日"] = "未填";
                    ds.Tables[0].Rows[k]["产假"] = "未填";
                    ds.Tables[0].Rows[k]["调休"] = "未填";
                    ds.Tables[0].Rows[k]["丧假"] = "未填";
                    ds.Tables[0].Rows[k]["婚假"] = "未填";
                    ds.Tables[0].Rows[k]["缺勤"] = "未填";
                }
                else
                {
                    ds.Tables[0].Rows[k]["出勤"] = 0;
                    ds.Tables[0].Rows[k]["迟到"] = 0;
                    ds.Tables[0].Rows[k]["早退"] = 0;
                    ds.Tables[0].Rows[k]["事假"] = 0;
                    ds.Tables[0].Rows[k]["病假"] = 0;
                    ds.Tables[0].Rows[k]["年休"] = 0;
                    ds.Tables[0].Rows[k]["节假日"] = 0;
                    ds.Tables[0].Rows[k]["产假"] = 0;
                    ds.Tables[0].Rows[k]["调休"] = 0;
                    ds.Tables[0].Rows[k]["丧假"] = 0;
                    ds.Tables[0].Rows[k]["婚假"] = 0;
                    ds.Tables[0].Rows[k]["缺勤"] = 0;
                }
            }


            for (int i = 0; i < ds2.Tables[0].Rows.Count; i++)
            {


                for (int j = 1; j <= dayNum; j++)
                {
                    string str = ds2.Tables[0].Rows[i]["d" + j].ToString();
                    switch (str)
                    {

                        case "出勤":
                            ds.Tables[0].Rows[k]["出勤"] = int.Parse(ds.Tables[0].Rows[k]["出勤"].ToString()) + 1;
                            break;
                        case "迟到":
                            ds.Tables[0].Rows[k]["迟到"] = int.Parse(ds.Tables[0].Rows[k]["迟到"].ToString()) + 1;

                            break;
                        case "早退":
                            ds.Tables[0].Rows[k]["早退"] = int.Parse(ds.Tables[0].Rows[k]["早退"].ToString()) + 1;

                            break;
                        case "事假":
                            ds.Tables[0].Rows[k]["事假"] = int.Parse(ds.Tables[0].Rows[k]["事假"].ToString()) + 1;

                            break;
                        case "病假":
                            ds.Tables[0].Rows[k]["病假"] = int.Parse(ds.Tables[0].Rows[k]["病假"].ToString()) + 1;

                            break;
                        case "年休":
                            ds.Tables[0].Rows[k]["年休"] = int.Parse(ds.Tables[0].Rows[k]["年休"].ToString()) + 1;

                            break;
                        case "节假日":
                            ds.Tables[0].Rows[k]["节假日"] = int.Parse(ds.Tables[0].Rows[k]["节假日"].ToString()) + 1;
                            break;
                        case "产假":
                            ds.Tables[0].Rows[k]["产假"] = int.Parse(ds.Tables[0].Rows[k]["产假"].ToString()) + 1;

                            break;
                        case "调休":
                            ds.Tables[0].Rows[k]["调休"] = int.Parse(ds.Tables[0].Rows[k]["调休"].ToString()) + 1;

                            break;
                        case "丧假":
                            ds.Tables[0].Rows[k]["丧假"] = int.Parse(ds.Tables[0].Rows[k]["丧假"].ToString()) + 1;

                            break;
                        case "婚假":
                            ds.Tables[0].Rows[k]["婚假"] = int.Parse(ds.Tables[0].Rows[k]["婚假"].ToString()) + 1;

                            break;
                        case "缺勤":
                            ds.Tables[0].Rows[k]["缺勤"] = int.Parse(ds.Tables[0].Rows[k]["缺勤"].ToString()) + 1;

                            break;
                    }
                }
            }

        }


        ds.Tables[0].Columns.Remove("swid");
        ds.Tables[0].Columns.Remove("areaId");
        ds.Tables[0].Columns.Remove("scoreA");
        ds.Tables[0].Columns.Remove("scoreB");

        System.Web.UI.WebControls.GridView gvdata = new System.Web.UI.WebControls.GridView();
        gvdata.HeaderStyle.BackColor = System.Drawing.Color.Silver;
        gvdata.HeaderStyle.Font.Bold = true;
        gvdata.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;

        gvdata.DataSource = ds;
        gvdata.DataBind();
        const string style = @"<style> .text {mso-number-format:\@; } </style> ";
        Response.ClearContent();
        string strFileName = "考勤评议汇总";
        Response.AddHeader("content-disposition", "attachment; filename=" + Server.UrlEncode(strFileName) + ".xls");

        Response.ContentType = "application/excel";

        StringWriter sw = new StringWriter();

        HtmlTextWriter htw = new HtmlTextWriter(sw);
        //htw.WriteLine(Name);
        System.Web.UI.LiteralControl lt = new LiteralControl();
        lt.Text = "<div style=\"width:100%;font-bold:true;text-align:center;\">" + strFileName + "(" + SelectYearCombo.SelectedItem.Value + "年" + SelectMonthCombo.SelectedItem.Value + "月)" + "</div>";
        if (strFileName != "") lt.RenderControl(htw);
        gvdata.RenderControl(htw);
        // Style is added dynamically

        Response.Write(style);

        Response.Write(sw.ToString());

        Response.End();
    }

    private string EditString(string TextString)
    {
        if (TextString.IndexOf('\r') != -1)
        {
            TextString = TextString.Replace('\r', ' ');
        }
        if (TextString.IndexOf('\n') != -1)
        {
            TextString = TextString.Replace('\n', ' ');
        }
        if (TextString.IndexOf('\t') != -1)
        {
            TextString = TextString.Replace('\t', ' ');
        }
        if (TextString.IndexOf("'") != -1)
        {
            TextString = TextString.Replace("'", "");
        }
        if (TextString.IndexOf(@"""") != -1)
        {
            TextString = TextString.Replace(@"""", "");
        }
        return TextString;
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Socialworkers.Data;
using Socialworkers.Entities;
using System.Data;
using Ext.Net;
using System.IO;

public partial class admin_JiuBao_WorkTotal : System.Web.UI.Page
{
    private int stmstart = 0;
    private int stmlimit = 0;
    private string AreaId = "";
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!new PublicClass().IsLogin())
        {
            Response.Redirect("~/Error.aspx?type=0");
        }
        AreaId = Session["Areaid"].ToString();


        if (!IsPostBack && !X.IsAjaxRequest)
        {
            int year = DateTime.Now.Year;
            for (int i = 2012; i <= year+1; i++)
            {
                SelectYearCombo.Items.Add(new Ext.Net.ListItem(i.ToString(), i.ToString()));
            }
            SelectYearCombo.SelectedItem.Value = year.ToString();
            int month = DateTime.Now.Month;

            SelectMonthCombo.SelectedItem.Value = month.ToString();

        }
    }
    protected void CorpInfoStore_RefreshData(object sender, Ext.Net.StoreRefreshDataEventArgs e)
    {
        stmstart = e.Start / e.Limit;
        stmlimit = e.Limit;
        CorpInfogridBind();
    }

    private string getAreaStr()
    {
        string str = "";
        if (AreaId.Equals("7"))
        {
            str = " in (select id from AreaInfo where parentId = '7') ";
        }
        else
        {
            str = "  = '" + AreaId + "' ";
        }
        return str;
    }
    private void CorpInfogridBind()
    {
       
        string sql = "select WorkAdmin.swid," +
            "Shequ=(select Name from AreaInfo where id = (select areaid from TsocialworkersTable where id = WorkAdmin.swid))," +
            "sum1=(select count(*) from Workrecord sum1_wr left join WorkAdmin sum1_wa on sum1_wa.id = sum1_wr.pid where sum1_wa.swid = WorkAdmin.swid and sum1_wr.worktype = '日常事务' " + getWhereStr("sum1_wa") + " )," +
            "sum2=(select count(*) from Workrecord sum1_wr left join WorkAdmin sum1_wa on sum1_wa.id = sum1_wr.pid where sum1_wa.swid = WorkAdmin.swid and sum1_wr.worktype = '事件处理' " + getWhereStr("sum1_wa") + " )," +
            "sum3=(select count(*) from Workrecord sum1_wr left join WorkAdmin sum1_wa on sum1_wa.id = sum1_wr.pid where sum1_wa.swid = WorkAdmin.swid and sum1_wr.worktype = '走访' " + getWhereStr("sum1_wa") + ")," +
            "sum5=(select count(*) from Dutyrecord where swid = WorkAdmin.swid and areaid = (select areaid from TsocialworkersTable where id = WorkAdmin.swid) " + getWhereStr2("Dutyrecord") + ")," +
            "name = (select name from TsocialworkersTable where id = WorkAdmin.swid)," +
            "sum4 =count(*)" +
            " from Workrecord left join WorkAdmin on WorkAdmin.id = Workrecord.pid " +
            "where (select count(*) from Workrecord sum1_wr left join WorkAdmin sum1_wa on sum1_wa.id = sum1_wr.pid where sum1_wa.swid = WorkAdmin.swid and sum1_wr.worktype = '走访' " + getWhereStr("sum1_wa") + ")< 30 and WorkAdmin.swid in (select id from TsocialworkersTable where areaId " + getAreaStr() + ")" + getWhereStr("WorkAdmin") + "  group by WorkAdmin.swid";



        sql = "select Duties,orderid,areaid,swid=id,name " +
            ",ShequOrder=(select OrderId from AreaInfo where id = TsocialworkersTable.areaId) " +
            ",Shequ=(select Name from AreaInfo where id = TsocialworkersTable.areaId) " +
            ",sumdays=(select count(*) from  WorkAdmin sum1_wa where sum1_wa.swid = TsocialworkersTable.id " + getWhereStr("sum1_wa") + " ) " +
            ",sum1=(select count(*) from Workrecord sum1_wr left join WorkAdmin sum1_wa on sum1_wa.id = sum1_wr.pid where sum1_wa.swid = TsocialworkersTable.id and sum1_wr.worktype = '日常事务' " + getWhereStr("sum1_wa") + " ) " +
            ",sum2=(select count(*) from Workrecord sum1_wr left join WorkAdmin sum1_wa on sum1_wa.id = sum1_wr.pid where sum1_wa.swid = TsocialworkersTable.id and sum1_wr.worktype = '事件处理' " + getWhereStr("sum1_wa") + " ) " +
            ",sum3=(select count(*) from Workrecord sum1_wr left join WorkAdmin sum1_wa on sum1_wa.id = sum1_wr.pid where sum1_wa.swid = TsocialworkersTable.id and sum1_wr.worktype = '走访' " + getWhereStr("sum1_wa") + " ) " +
            ",sum5=(select count(*) from Dutyrecord where swid = TsocialworkersTable.Id and areaid = TsocialworkersTable.AreaId " + getWhereStr2("Dutyrecord") + ") " +
            " from TsocialworkersTable where  areaid " + getAreaStr();
        if (!string.IsNullOrEmpty(C_Hid_QueryStr.Text))
        {
            sql += " and name like '%" + C_Hid_QueryStr.Text + "%'";
        }

        if (OnlyNotRightCheckBox.Checked) {
            sql += " and (select count(*) from Workrecord sum1_wr left join WorkAdmin sum1_wa on sum1_wa.id = sum1_wr.pid where sum1_wa.swid = TsocialworkersTable.id and sum1_wr.worktype = '走访' " + getWhereStr("sum1_wa") + " ) < 30 ";
        }



        sql += " order by areaid,isnull(OrderId,9999),id";
        
        int dayNum = DateTime.DaysInMonth(int.Parse(SelectYearCombo.SelectedItem.Value), int.Parse(SelectMonthCombo.SelectedItem.Value));
        
        DataSet ds = DataRepository.Provider.ExecuteDataSet(CommandType.Text, sql);




        CorpInfoStore.DataSource = ds;
        CorpInfoStore.DataBind();

    }
    private string getWhereStr(string pre)
    {

        string where = "";
        if (SelectYearCombo.SelectedItem != null && SelectMonthCombo.SelectedItem != null)
        {
            where += " and " + pre + ".CreateDay >= '" + SelectYearCombo.SelectedItem.Value + "-" + SelectMonthCombo.SelectedItem.Value + "-1' and " + pre + ".CreateDay < '" + (new DateTime(int.Parse(SelectYearCombo.SelectedItem.Value), int.Parse(SelectMonthCombo.SelectedItem.Value), 1).AddMonths(1)).ToString() + "'";
        }

        return where;
    }
    private string getWhereStr2(string pre)
    {

        string where = "";
        if (SelectYearCombo.SelectedItem != null && SelectMonthCombo.SelectedItem != null)
        {
            where += " and " + pre + ".Dutytime >= '" + SelectYearCombo.SelectedItem.Value + "-" + SelectMonthCombo.SelectedItem.Value + "-1' and " + pre + ".Dutytime < '" + (new DateTime(int.Parse(SelectYearCombo.SelectedItem.Value), int.Parse(SelectMonthCombo.SelectedItem.Value), 1).AddMonths(1)).ToString() + "'";
        }

        return where;
    }
    private string getWhereStr3(string pre)
    {

        string where = "";
        if (SelectYearCombo.SelectedItem != null && SelectMonthCombo.SelectedItem != null)
        {
            where += " and " + pre + ".SelectDate = '" + SelectYearCombo.SelectedItem.Value + "-" + SelectMonthCombo.SelectedItem.Value + "-1'";
        }

        return where;
    }
    [DirectMethod(ShowMask = true, Msg = "加载中...", Namespace = "X")]
    public void ShowIframeWindow(string id)
    {

        string title = "";
        string url = "";
        title = "日常工作";
        url = "../JiuBao/SocialworkerWork.aspx?id=" + id;

        url += "&minTime=" + new DateTime(int.Parse(SelectYearCombo.SelectedItem.Value), int.Parse(SelectMonthCombo.SelectedItem.Value), 1);


        url += "&maxTime=" + new DateTime(int.Parse(SelectYearCombo.SelectedItem.Value), int.Parse(SelectMonthCombo.SelectedItem.Value), 1).AddMonths(1).AddDays(-1);

        IframeWindow.Title = title;

        CenterPanelID.AutoLoad.Scripts = true;
        CenterPanelID.AutoLoad.NoCache = true;
        CenterPanelID.AutoLoad.Mode = LoadMode.IFrame;
        CenterPanelID.AutoLoad.ShowMask = true;
        CenterPanelID.AutoLoad.MaskMsg = "页面载入中...";


        CenterPanelID.AutoLoad.Url = url;


        CenterPanelID.LoadContent();

        IframeWindow.Show();
    }
    protected void ToNewExcel(object sender, EventArgs e)
    {


            string QueryStr = C_Hid_QueryStr.Text;
            string orderby_sql = " order by (select orderId from AreaInfo where id = account.aid) desc,account.aid,account.FamilyAdd,ISNUMERIC(account.Building),case when ISNUMERIC(account.Building)=0 then 0 else cast(account.Building as float) end, ISNUMERIC(account.Unit),case when ISNUMERIC(account.Unit)=0 then 0 else cast(account.Unit as float) end,ISNUMERIC(account.Room),case when ISNUMERIC(account.Room)=0 then 0 else cast(account.Room as float) end asc ";
            //是否显示空户

            string emptyStr = "";



             string sql = "select  " +
                 "(select Name from SmallArea where id = account.SmallAreaId ) as 片区,account.FamilyAdd as 小区,account.Building as 幢,account.Unit as 单元,account.Room as 室," +
                 " PopulationInfo.Name as 姓名,PopulationInfo.Relationship as 户主关系, 性别=(case when PopulationInfo.Sex=1 then '男' when PopulationInfo.Sex=0 then '女' else '' end)," +
                 "PopulationInfo.Nation as 民族, PopulationInfo.Identycode as 身份证, 出生年月=CONVERT(varchar(12) ,PopulationInfo.Birthday, 111 ) , PopulationInfo.Education as 文化程度," +
                 " PopulationInfo.FamilyTel as 电话,PopulationInfo.MobilePhone as 手机, PopulationInfo.PolityFace as 政治面貌, " +
                   "PopulationInfo.Workplace as 工作单位,PopulationInfo.AccountType as 户口性质,PopulationInfo.HouseHoldPlace as 家庭地址,PopulationInfo.Residence as 现居住地,PopulationInfo.PType as 人员类别," +
                   "PopulationInfo.Canji as 残疾类别,PopulationInfo.Youfu as 优抚对象,PopulationInfo.Starlevel as 服务级别,PopulationInfo.laomo as 劳模,PopulationInfo.hunying as 婚姻," +
                   "PopulationInfo.jiankang as 健康状况,PopulationInfo.jiuye as 就业状况,PopulationInfo.Remarks as 备注 " +
                   "FROM account left join PopulationInfo on account.id = PopulationInfo.accountId where account.aid in (select id from AreaInfo where id = " + AreaId + ") " + QueryStr + emptyStr + orderby_sql;
             sql = "select 社区=(select Name from AreaInfo where id = TsocialworkersTable.areaId),name as 姓名,Duties as 职务 " +
            ",记录天数=(select count(*) from  WorkAdmin sum1_wa where sum1_wa.swid = TsocialworkersTable.id " + getWhereStr("sum1_wa") + " ) " +
            ",日常事务=(select count(*) from Workrecord sum1_wr left join WorkAdmin sum1_wa on sum1_wa.id = sum1_wr.pid where sum1_wa.swid = TsocialworkersTable.id and sum1_wr.worktype = '日常事务' " + getWhereStr("sum1_wa") + " ) " +
            ",事件处理=(select count(*) from Workrecord sum1_wr left join WorkAdmin sum1_wa on sum1_wa.id = sum1_wr.pid where sum1_wa.swid = TsocialworkersTable.id and sum1_wr.worktype = '事件处理' " + getWhereStr("sum1_wa") + " ) " +
            ",走访=(select count(*) from Workrecord sum1_wr left join WorkAdmin sum1_wa on sum1_wa.id = sum1_wr.pid where sum1_wa.swid = TsocialworkersTable.id and sum1_wr.worktype = '走访' " + getWhereStr("sum1_wa") + " ) " +
            ",值班=(select count(*) from Dutyrecord where swid = TsocialworkersTable.Id and areaid = TsocialworkersTable.AreaId " + getWhereStr2("Dutyrecord") + ") " +
            " from TsocialworkersTable where  areaid " + getAreaStr() ;
             if (!string.IsNullOrEmpty(C_Hid_QueryStr.Text))
             {
                 sql += " and name like '%" + C_Hid_QueryStr.Text + "%'";
             }

             if (OnlyNotRightCheckBox.Checked)
             {
                 sql += " and (select count(*) from Workrecord sum1_wr left join WorkAdmin sum1_wa on sum1_wa.id = sum1_wr.pid where sum1_wa.swid = TsocialworkersTable.id and sum1_wr.worktype = '走访' " + getWhereStr("sum1_wa") + " ) < 30 ";
             }



             sql += " order by (select OrderId from AreaInfo where id = TsocialworkersTable.areaId),areaid,isnull(OrderId,9999),id";
            DataSet ds = null;
            try
            {
                ds = DataRepository.Provider.ExecuteDataSet(CommandType.Text, sql);
            }
            catch (Exception err)
            {
                throw new Exception(sql);
            }


            System.Web.UI.WebControls.GridView gvdata = new System.Web.UI.WebControls.GridView();
            gvdata.HeaderStyle.BackColor = System.Drawing.Color.Silver;
            gvdata.HeaderStyle.Font.Bold = true;
            gvdata.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
         
            gvdata.DataSource = ds;
            gvdata.DataBind();
            const string style = @"<style> .text {mso-number-format:\@; } </style> ";
            Response.ClearContent();
            string strFileName = "工作汇总信息";
            Response.AddHeader("content-disposition", "attachment; filename=" + Server.UrlEncode(strFileName) + ".xls");

            Response.ContentType = "application/excel";

            StringWriter sw = new StringWriter();

            HtmlTextWriter htw = new HtmlTextWriter(sw);
            //htw.WriteLine(Name);
            System.Web.UI.LiteralControl lt = new LiteralControl();
            lt.Text = "<div style=\"width:100%;font-bold:true;text-align:center;\">" + strFileName + "(" + SelectYearCombo.SelectedItem.Value + "年" + SelectMonthCombo.SelectedItem.Value + "月)" + "</div>";
            if (strFileName != "") lt.RenderControl(htw);
            gvdata.RenderControl(htw);
            // Style is added dynamically

            Response.Write(style);

            Response.Write(sw.ToString());

            Response.End();
    }

    private string EditString(string TextString)
    {
        if (TextString.IndexOf('\r') != -1)
        {
            TextString = TextString.Replace('\r', ' ');
        }
        if (TextString.IndexOf('\n') != -1)
        {
            TextString = TextString.Replace('\n', ' ');
        }
        if (TextString.IndexOf('\t') != -1)
        {
            TextString = TextString.Replace('\t', ' ');
        }
        if (TextString.IndexOf("'") != -1)
        {
            TextString = TextString.Replace("'", "");
        }
        if (TextString.IndexOf(@"""") != -1)
        {
            TextString = TextString.Replace(@"""", "");
        }
        return TextString;
    }
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Ext.Net;
using Socialworkers.Data;
using Socialworkers.Entities;
using System.Data;
using System.IO;
using LitJson;

public partial class admin_JiuBao_ScoreCheckTotal : System.Web.UI.Page
{
    private int stmstart = 0;
    private int stmlimit = 0;
    private string AreaId = "";
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!new PublicClass().IsLogin())
        {
            Response.Redirect("~/Error.aspx?type=0");
        }
        AreaId = Session["Areaid"].ToString();


        if (!IsPostBack && !X.IsAjaxRequest)
        {
            int year = DateTime.Now.Year;
            for (int i = 2012; i <= year+1; i++)
            {
                SelectYearCombo.Items.Add(new Ext.Net.ListItem(i.ToString(), i.ToString()));
            }
            SelectYearCombo.SelectedItem.Value = year.ToString();
            int month = DateTime.Now.AddMonths(-1).Month;
            SelectMonthCombo.SelectedItem.Value = "1";
            SelectMonthComboEnd.SelectedItem.Value = month.ToString();
        }
    }
    protected void CorpInfoStore_RefreshData(object sender, Ext.Net.StoreRefreshDataEventArgs e)
    {
        stmstart = e.Start / e.Limit;
        stmlimit = e.Limit;
        CorpInfogridBind();
    }
    private string getAreaStr()
    {
        string str = "";
        if (AreaId.Equals("7"))
        {
            str = " in (select id from AreaInfo where parentId = '7') ";
        }
        else
        {
            str = "  = '" + AreaId + "' ";
        }
        return str;
    }
    //计算月份
    private string calMonths() {
        int startMonth = int.Parse(SelectMonthCombo.SelectedItem.Value);
        int endMonth = int.Parse(SelectMonthComboEnd.SelectedItem.Value);

        int smallInt = startMonth;
        int bigInt = endMonth;

        if (smallInt > bigInt) {
            smallInt = endMonth;
            bigInt = startMonth;
        }
        string returnStr = "(";
        for(int i=smallInt;i<=bigInt;i++){
            if (i != smallInt) {
                returnStr += ",";
            }
            returnStr += i;
        }
        returnStr += ")";
        return returnStr;
    }
    //计算月份第一天
    private string calMonthsFirstDay()
    {
        string year = SelectYearCombo.SelectedItem.Value;

        int startMonth = int.Parse(SelectMonthCombo.SelectedItem.Value);
        int endMonth = int.Parse(SelectMonthComboEnd.SelectedItem.Value);

        int smallInt = startMonth;
        int bigInt = endMonth;

        if (smallInt > bigInt)
        {
            smallInt = endMonth;
            bigInt = startMonth;
        }
        string returnStr = "(";
        for (int i = smallInt; i <= bigInt; i++)
        {
            if (i != smallInt)
            {
                returnStr += ",";
            }
            returnStr = returnStr + "'"+year+"-"+i+"-1'";
        }
        returnStr += ")";
        return returnStr;
    }
    
    private void CorpInfogridBind()
    {
        try
        {
            string sql = "select  t.Duties,t.orderid,t.areaid,swid=t.id,t.name,Shequ=a.Name,ShequOrder=a.OrderId " +
                ",sumA=(select sum(ScoreAll) from NewWorkerScore n where Name = t.Id and n.area = t.AreaId " + getWhereStr3("n") + ") " +
                ",sumB=(select sum(ScoreAllB) from NewWorkerScore n where Name = t.Id and n.area = t.AreaId " + getWhereStr3("n") + ") " +
                ",ChuQin=(select sum(dbo.SplitStrGetNum(chuQin)) from NewWorkCheck where Sid=t.Id and  Year = '" + SelectYearCombo.SelectedItem.Value + "' AND Month IN " + calMonths() + " )" +
                ",ShiJia=(select sum(dbo.SplitStrGetNum(ShiJia)) from NewWorkCheck where Sid=t.Id and  Year = '" + SelectYearCombo.SelectedItem.Value + "' AND Month IN " + calMonths() + " )" +
                ",BingJia=(select sum(dbo.SplitStrGetNum(BingJia)) from NewWorkCheck where Sid=t.Id and  Year = '" + SelectYearCombo.SelectedItem.Value + "' AND Month IN " + calMonths() + " )" +
                ",QueJin=(select sum(dbo.SplitStrGetNum(QueJin)) from NewWorkCheck where Sid=t.Id and  Year = '" + SelectYearCombo.SelectedItem.Value + "' AND Month IN " + calMonths() + " )" +
                ",NianXiu=(select sum(dbo.SplitStrGetNum(NianXiu)) from NewWorkCheck where Sid=t.Id and  Year = '" + SelectYearCombo.SelectedItem.Value + "' AND Month IN " + calMonths() + " )" +
                ",JieJia=(select sum(dbo.SplitStrGetNum(JieJia)) from NewWorkCheck where Sid=t.Id and  Year = '" + SelectYearCombo.SelectedItem.Value + "' AND Month IN " + calMonths() + " )" +
                ",ChanJia=(select sum(dbo.SplitStrGetNum(ChanJia)) from NewWorkCheck where Sid=t.Id and  Year = '" + SelectYearCombo.SelectedItem.Value + "' AND Month IN " + calMonths() + " )" +
                ",TiaoXiu=(select sum(dbo.SplitStrGetNum(TiaoXiu)) from NewWorkCheck where Sid=t.Id and  Year = '" + SelectYearCombo.SelectedItem.Value + "' AND Month IN " + calMonths() + " )" +
                ",SangJia=(select sum(dbo.SplitStrGetNum(SangJia)) from NewWorkCheck where Sid=t.Id and  Year = '" + SelectYearCombo.SelectedItem.Value + "' AND Month IN " + calMonths() + " )" +
                ",HunJia=(select sum(dbo.SplitStrGetNum(HunJia)) from NewWorkCheck where Sid=t.Id and  Year = '" + SelectYearCombo.SelectedItem.Value + "' AND Month IN " + calMonths() + " )" +
                ",ChiDao=(select sum(dbo.SplitStrGetNum(ChiDao)) from NewWorkCheck where Sid=t.Id and  Year = '" + SelectYearCombo.SelectedItem.Value + "' AND Month IN " + calMonths() + " )" +
                ",ZaoTui=(select sum(dbo.SplitStrGetNum(ZaoTui)) from NewWorkCheck where Sid=t.Id and  Year = '" + SelectYearCombo.SelectedItem.Value + "' AND Month IN " + calMonths() + " )" +
                " from TsocialworkersTable t left join AreaInfo a ON a.id=t.areaid  where areaid " + getAreaStr();
           
            if (!string.IsNullOrEmpty(C_Hid_QueryStr.Text))
            {
                sql += " and t.name like '%" + C_Hid_QueryStr.Text + "%'";
            }

            sql += " order by t.areaid,isnull(t.OrderId,9999),t.id";
            DataSet ds = DataRepository.Provider.ExecuteDataSet(CommandType.Text, sql);

            int startMonth = int.Parse(SelectMonthCombo.SelectedItem.Value);
            int endMonth = int.Parse(SelectMonthComboEnd.SelectedItem.Value);

            //新的考勤
            if (int.Parse(SelectYearCombo.SelectedItem.Value) > 2014 || (SelectYearCombo.SelectedItem.Value == "2014" && (startMonth >= 5 || endMonth>=5)))
            {
                for(int i=0;i<ds.Tables[0].Rows.Count;i++){
                    string sid = ds.Tables[0].Rows[i]["swid"].ToString(); 
                    string sql_score="select * from Iworkerscore where sid="+sid+" and selectdate in "+calMonthsFirstDay()+"";
                    DataSet ds_score = DataRepository.Provider.ExecuteDataSet(CommandType.Text, sql_score);
                    int score1 = 0;
                    int score2 = 0;
                    int score3 = 0;
                    #region 
                   
                    if (ds_score.Tables[0].Rows.Count > 0) {
                        DataRow dr = ds_score.Tables[0].Rows[0];
                        //自评
                        string self_data = dr["selfdata"].ToString();
                        if (!string.IsNullOrEmpty(self_data))
                        {
                            JsonData jd = JsonMapper.ToObject(self_data);
                            try
                            {
                                score1 += int.Parse(jd["s1"].ToString());
                            }
                            catch { }
                            finally
                            {
                                score1 += 0;
                            }
                            try
                            {
                                score1 += int.Parse(jd["s2"].ToString());
                            }
                            catch { }
                            finally
                            {
                                score1 += 0;
                            }
                            try
                            {
                                score1 += int.Parse(jd["s3"].ToString());
                            }
                            catch { }
                            finally
                            {
                                score1 += 0;
                            }
                        }
                        //考评
                        string score_data = dr["scoredata"].ToString();
                        if (!string.IsNullOrEmpty(score_data))
                        {
                            JsonData jd = JsonMapper.ToObject(score_data);
                            try
                            {
                                score2 += int.Parse(jd["s1"].ToString());
                            }
                            catch { }
                            finally
                            {
                                score2 += 0;
                            }
                            try
                            {
                                score2 += int.Parse(jd["s2"].ToString());
                            }
                            catch { }
                            finally
                            {
                                score2 += 0;
                            }
                            try
                            {
                                score2 += int.Parse(jd["s3"].ToString());
                            }
                            catch { }
                            finally
                            {
                                score2 += 0;
                            }
                        }
                        //镇评
                        string total_data = dr["total"].ToString();
                        if (!string.IsNullOrEmpty(total_data))
                        {
                            JsonData jd = JsonMapper.ToObject(total_data);
                            try
                            {
                                score3 += int.Parse(jd["s1"].ToString());
                            }
                            catch { }
                            finally
                            {
                                score3 += 0;
                            }
                            try
                            {
                                score3 += int.Parse(jd["s2"].ToString());
                            }
                            catch { }
                            finally
                            {
                                score3 += 0;
                            }
                        }
                    }
                    #endregion
                    if (!string.IsNullOrEmpty(ds.Tables[0].Rows[i]["sumA"].ToString()))
                    {
                        ds.Tables[0].Rows[i]["sumA"] = Double.Parse(ds.Tables[0].Rows[i]["sumA"].ToString()) + score1 + score3;
                    }
                    else
                    {
                        ds.Tables[0].Rows[i]["sumA"] = score1 + score3;
                    }
                    if (!string.IsNullOrEmpty(ds.Tables[0].Rows[i]["sumB"].ToString()))
                    {
                        ds.Tables[0].Rows[i]["sumB"] = Double.Parse(ds.Tables[0].Rows[i]["sumB"].ToString()) + score2 + score3;
                    }
                    else
                    {
                        ds.Tables[0].Rows[i]["sumB"] = score2 + score3;
                    }
                }
            }


            CorpInfoStore.DataSource = ds;
            CorpInfoStore.DataBind();
        }catch(Exception e){
            throw new Exception(e.Message);
        }

    }
    private string getWhereStr(string pre)
    {

        string where = "";
        if (SelectYearCombo.SelectedItem != null && SelectMonthCombo.SelectedItem != null)
        {
            where += " and " + pre + ".CreateDay >= '" + SelectYearCombo.SelectedItem.Value + "-" + SelectMonthCombo.SelectedItem.Value + "-1' and " + pre + ".CreateDay < '" + (new DateTime(int.Parse(SelectYearCombo.SelectedItem.Value), int.Parse(SelectMonthCombo.SelectedItem.Value), 1)).AddMonths(1).ToString() + "'";
        }

        return where;
    }
    private string getWhereStr2(string pre)
    {

        string where = "";
        if (SelectYearCombo.SelectedItem != null && SelectMonthCombo.SelectedItem != null)
        {
            where += " and " + pre + ".Dutytime >= '" + SelectYearCombo.SelectedItem.Value + "-" + SelectMonthCombo.SelectedItem.Value + "-1' and " + pre + ".Dutytime < '" + (new DateTime(int.Parse(SelectYearCombo.SelectedItem.Value), int.Parse(SelectMonthCombo.SelectedItem.Value), 1)).AddMonths(1).ToString() + "'";
        }

        return where;
    }
 
    private string getWhereStr3(string pre)
    {

        string where = "";
        if (SelectYearCombo.SelectedItem != null && SelectMonthCombo.SelectedItem != null)
        {
            where += " and " + pre + ".SelectDate in " + calMonthsFirstDay();
        }

        return where;
    }
    [DirectMethod(ShowMask = true, Msg = "加载中...", Namespace = "X")]
    public void ShowIframeWindow(string id)
    {


    }

    protected void ToNewExcel(object sender, EventArgs e)
    {
        string sql = "select 社区=a.Name,t.name as 姓名,t.Duties as 职务 " +
            ",出勤=(select sum(dbo.SplitStrGetNum(chuQin)) from NewWorkCheck where Sid=t.Id and  Year = '" + SelectYearCombo.SelectedItem.Value + "' AND Month IN " + calMonths() + " )" +
               ",事假=(select sum(dbo.SplitStrGetNum(ShiJia)) from NewWorkCheck where Sid=t.Id and  Year = '" + SelectYearCombo.SelectedItem.Value + "' AND Month IN " + calMonths() + " )" +
               ",病假=(select sum(dbo.SplitStrGetNum(BingJia)) from NewWorkCheck where Sid=t.Id and  Year = '" + SelectYearCombo.SelectedItem.Value + "' AND Month IN " + calMonths() + " )" +
               ",缺勤=(select sum(dbo.SplitStrGetNum(QueJin)) from NewWorkCheck where Sid=t.Id and  Year = '" + SelectYearCombo.SelectedItem.Value + "' AND Month IN " + calMonths() + " )" +
               ",年休=(select sum(dbo.SplitStrGetNum(NianXiu)) from NewWorkCheck where Sid=t.Id and  Year = '" + SelectYearCombo.SelectedItem.Value + "' AND Month IN " + calMonths() + " )" +
               ",节假=(select sum(dbo.SplitStrGetNum(JieJia)) from NewWorkCheck where Sid=t.Id and  Year = '" + SelectYearCombo.SelectedItem.Value + "' AND Month IN " + calMonths() + " )" +
               ",产假=(select sum(dbo.SplitStrGetNum(ChanJia)) from NewWorkCheck where Sid=t.Id and  Year = '" + SelectYearCombo.SelectedItem.Value + "' AND Month IN " + calMonths() + " )" +
               ",调休=(select sum(dbo.SplitStrGetNum(TiaoXiu)) from NewWorkCheck where Sid=t.Id and  Year = '" + SelectYearCombo.SelectedItem.Value + "' AND Month IN " + calMonths() + " )" +
               ",丧假=(select sum(dbo.SplitStrGetNum(SangJia)) from NewWorkCheck where Sid=t.Id and  Year = '" + SelectYearCombo.SelectedItem.Value + "' AND Month IN " + calMonths() + " )" +
               ",婚假=(select sum(dbo.SplitStrGetNum(HunJia)) from NewWorkCheck where Sid=t.Id and  Year = '" + SelectYearCombo.SelectedItem.Value + "' AND Month IN " + calMonths() + " )" +
               ",迟到=(select sum(dbo.SplitStrGetNum(ChiDao)) from NewWorkCheck where Sid=t.Id and  Year = '" + SelectYearCombo.SelectedItem.Value + "' AND Month IN " + calMonths() + " )" +
               ",早退=(select sum(dbo.SplitStrGetNum(ZaoTui)) from NewWorkCheck where Sid=t.Id and  Year = '" + SelectYearCombo.SelectedItem.Value + "' AND Month IN " + calMonths() + " )" +
               ",自评分=(select sum(ScoreAll) from NewWorkerScore n where Name = t.Id and n.area = t.AreaId " + getWhereStr3("n") + ") " +
               ",考评分=(select sum(ScoreAllB) from NewWorkerScore n where Name = t.Id and n.area = t.AreaId " + getWhereStr3("n") + ") " +
               
               " from TsocialworkersTable t left join AreaInfo a ON a.id=t.areaid  where areaid " + getAreaStr();


        if (!string.IsNullOrEmpty(C_Hid_QueryStr.Text))
        {
            sql += " and t.name like '%" + C_Hid_QueryStr.Text + "%'";
        }
        sql += " order by a.orderid,t.areaid,isnull(t.OrderId,9999),t.id";
    
      
        DataSet ds = DataRepository.Provider.ExecuteDataSet(CommandType.Text, sql);

        
        System.Web.UI.WebControls.GridView gvdata = new System.Web.UI.WebControls.GridView();
        gvdata.HeaderStyle.BackColor = System.Drawing.Color.Silver;
        gvdata.HeaderStyle.Font.Bold = true;
        gvdata.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;

        gvdata.DataSource = ds;
        gvdata.DataBind();
        const string style = @"<style> .text {mso-number-format:\@; } </style> ";
        Response.ClearContent();
        string strFileName = "考勤评议汇总";
        Response.AddHeader("content-disposition", "attachment; filename=" + Server.UrlEncode(strFileName) + ".xls");

        Response.ContentType = "application/excel";

        StringWriter sw = new StringWriter();

        HtmlTextWriter htw = new HtmlTextWriter(sw);
        //htw.WriteLine(Name);
        System.Web.UI.LiteralControl lt = new LiteralControl();
        lt.Text = "<div style=\"width:100%;font-bold:true;text-align:center;\">" + strFileName +  "</div>";
        if (strFileName != "") lt.RenderControl(htw);
        gvdata.RenderControl(htw);
        // Style is added dynamically

        Response.Write(style);

        Response.Write(sw.ToString());

        Response.End();
    }

    private string EditString(string TextString)
    {
        if (TextString.IndexOf('\r') != -1)
        {
            TextString = TextString.Replace('\r', ' ');
        }
        if (TextString.IndexOf('\n') != -1)
        {
            TextString = TextString.Replace('\n', ' ');
        }
        if (TextString.IndexOf('\t') != -1)
        {
            TextString = TextString.Replace('\t', ' ');
        }
        if (TextString.IndexOf("'") != -1)
        {
            TextString = TextString.Replace("'", "");
        }
        if (TextString.IndexOf(@"""") != -1)
        {
            TextString = TextString.Replace(@"""", "");
        }
        return TextString;
    }
}

This snippet took 0.31 seconds to highlight.

Back to the Entry List or Home.

Delete this entry (admin only).