月底发票确认后台

//
// DotNetNuke?- http://www.dotnetnuke.com
// Copyright (c) 2002-2"+mont1+"7
// by DotNetNuke Corporation
//
// Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated
// documentation files (the "Software"), to deal in the Software without restriction, including without limitation
// the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and
// to permit persons to whom the Software is furnished to do so, subject to the following conditions:
//
// The above copyright notice and this permission notice shall be included in all copies or substantial portions
// of the Software.
//
// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED
// TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL
// THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF
// CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
// DEALINGS IN THE SOFTWARE.
//

using System;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Collections;
using System.Collections.Generic;
using System.Reflection;
using Insus.NET;

using DotNetNuke;
using DotNetNuke.Common.Utilities;
using DotNetNuke.Security;
using DotNetNuke.Services.Exceptions;
using DotNetNuke.Services.Localization;
using DotNetNuke.Entities.Modules;
using DotNetNuke.Entities.Modules.Actions;
using DbHelper;
using System.Web.UI;
using C1.Web.Wijmo.Controls.C1GridView;
using System.Data;

namespace Modules.AGL_FP_MONTHCONFIRM
{

/// -----------------------------------------------------------------------------
/// <summary>
/// The ViewAGL_FP_MONTHCONFIRM class displays the content
/// </summary>
/// <remarks>
/// </remarks>
/// <history>
/// </history>
/// -----------------------------------------------------------------------------
partial class ViewAGL_FP_MONTHCONFIRM : PortalModuleBase, IActionable
{
#region "Event Handlers"
string gjpgjp = DbHelper.DAL.GJPSqlServer;
/// -----------------------------------------------------------------------------
/// <summary>
/// Page_Load runs when the control is loaded
/// </summary>
/// -----------------------------------------------------------------------------
protected void Page_Load(object sender, System.EventArgs e)
{
try
{

/* 导入JS路劲文件 */
//ScriptManager scriptManager = ScriptManager.GetCurrent(Page);
//scriptManager.Scripts.Add(new ScriptReference(this.PortalSettings.HomeDirectory + "/js/WdatePicker.js"));
//TextBox1.Attributes.Add("onkeydown", "if(event.keyCode==13){return false}");
if (!Page.IsPostBack)
{

btnpanduan.Enabled = false;
btntijiao.Enabled = false;
try
{
changzong();
}
catch (Exception ex)
{

}
//string zhe = "2017-08";
string zhe = DateTime.Now.ToString("yyyy-MM");//年月
string nian = DateTime.Now.ToString("yyyy");//年
string[] zhehe = zhe.Split('-');
string shanghe;
string zhe1;
string sangeyue = "";
if (Convert.ToInt32(zhehe[1]) == 1)
{
nian = (Convert.ToInt32(nian) - 1).ToString();//去年
shanghe = "12";
sangeyue = (Convert.ToInt32(shanghe) - 3).ToString();
}
else
{
if (Convert.ToInt32(zhehe[1]) > 10)
{
shanghe = (Convert.ToInt32(zhehe[1]) - 1).ToString();
}
else
{
shanghe = "0" + (Convert.ToInt32(zhehe[1]) - 1).ToString();
}
}
if (Convert.ToInt32(zhehe[1]) == 1)
{
sangeyue = "09";
}
if (Convert.ToInt32(zhehe[1]) == 2)
{
sangeyue = "10";
}
if (Convert.ToInt32(zhehe[1]) == 3)
{
sangeyue = "11";
}
if (Convert.ToInt32(zhehe[1]) == 4)
{
sangeyue = "12";
}
if (Convert.ToInt32(zhehe[1]) >= 5)
{
sangeyue = "0" + (Convert.ToInt32(zhehe[1]) - 4).ToString();
}
//string mont = "2017-08";
string mont = DateTime.Now.ToString("yyyy-MM");
string year = DateTime.Now.ToString("yyyy");
string mm = this.textbox_date.Text;
//string mm = "2017-08";
string str = "select count(*) from ins_fapiaotongji where time ='" + mont + "'";
object count = DbHelper.DAL.ExecuteScalar(str);
if (Convert.ToInt32(count) >= 1)
{
string zongj = "select * from ins_fapiaotongji where time ='" + mont + "' ";
DataTable dt = DbHelper.DAL.DateSet(zongj).Tables[0];
for (int i = 0; i < dt.Columns.Count; i++)
{
TextBox1.Text = dt.Rows[0][1].ToString();
TextBox2.Text = dt.Rows[0][2].ToString();
TextBox4.Text = dt.Rows[0][3].ToString();
TextBox6.Text = dt.Rows[0][7].ToString();
TextBox7.Text = dt.Rows[0][8].ToString();
TextBox5.Text = dt.Rows[0][4].ToString();
TextBox9.Text = dt.Rows[0][5].ToString();
TextBox10.Text = dt.Rows[0][6].ToString();
}
btnpanduan.Enabled = false;
btntijiao.Enabled = false;
}
this.textbox_date.Text = mont;

string erp = "select distinct c.fullName,invlinshi.fengzFlag,inv.customer_id,inv.company,invlinshi.id,0 as amount,inv.account_bank,inv.account_number,inv.address,inv.identity_number,inv.telephone,inv.address,invlinshi.fp_type,invlinshi.statement_ci,invlinshi.state,inv.statement,byyk.benyueyikai,syyk.sanyueyikai as sanyueyikai from Invoices inv left join Invoices_linshi invlinshi on invlinshi.customer_id = inv.customer_id left join " + gjpgjp + "btype c on c.typeid=inv.customer_id left join (select sum(cast((case when InvoicesMoney='' then '0' else isnull(InvoicesMoney,0) end)as decimal)) as benyueyikai,customer_id from [InvoiceSure] where invid in(select id from Invoices_linshi where duicuo=0) and left([dateStr],7) = '" + zhe + "' group by customer_id) as byyk on byyk.customer_id =inv.customer_id left join (select sum(cast((case when InvoicesMoney='' then '0' else isnull(InvoicesMoney,0) end)as decimal)) as sanyueyikai,customer_id from [InvoiceSure] where invid in(select id from Invoices_linshi where duicuo=0) and left([dateStr],10) between '" + nian + "-" + sangeyue + "-26' and '" + nian + "-" + shanghe + "-25' group by customer_id) syyk on syyk.customer_id=inv.customer_id where (left(invlinshi.creat_date,7) = '" + zhe + "' or invlinshi.creat_date ='2000-01-01') and (( inv.statement ='1' and invlinshi.statement_ci='') or (inv.statement ='2' and invlinshi.statement_ci='1') or (inv.statement ='2' and invlinshi.statement_ci='2') or (inv.statement ='1' and invlinshi.statement_ci='1') or (inv.statement ='3' and invlinshi.statement_ci='2')) order by inv.statement,c.FullName asc";
DataTable derp = DbHelper.DAL.DateSet(erp).Tables[0];
derp.Columns.Add("本月金额", typeof(string));
derp.Columns.Add("三月金额", typeof(string));
for (int i = 0; i < derp.Rows.Count; i++)
{
if (Convert.IsDBNull(derp.Rows[i]["benyueyikai"]) || derp.Rows[i]["benyueyikai"] == null || derp.Rows[i]["benyueyikai"].ToString() == "")
{
derp.Rows[i]["benyueyikai"] = "0";
}
if (Convert.IsDBNull(derp.Rows[i]["sanyueyikai"]) || derp.Rows[i]["sanyueyikai"] == null || derp.Rows[i]["sanyueyikai"].ToString() == "")
{
derp.Rows[i]["sanyueyikai"] = "0";
}
if (Convert.IsDBNull(derp.Rows[i]["amount"]) || derp.Rows[i]["amount"] == null || derp.Rows[i]["amount"].ToString() == "")
{
derp.Rows[i]["amount"] = "0";
}
string name = derp.Rows[i][0].ToString();
string gjp = "select * from (SELECT isnull(sum(cast(S.total as decimal)),0) AS 本月金额,BT.FullName FROM dbo.ASum_SaleBill AS S LEFT OUTER JOIN dbo.ASum_BillIndex AS BI ON S.BillNumberId = BI.BillNumberId LEFT OUTER JOIN dbo.btype AS BT ON BI.btypeid = BT.typeId where BT.FullName = '" + name + "' and left(CAST(S.OutFactoryDate AS DATE),7) = '" + zhe + "' group by BT.FullName ) as benyueshiji left join (SELECT isnull(sum(cast(S.total as decimal)),0) AS 三月金额,BT.FullName FROM dbo.ASum_SaleBill AS S LEFT OUTER JOIN dbo.ASum_BillIndex AS BI ON S.BillNumberId = BI.BillNumberId LEFT OUTER JOIN dbo.btype AS BT ON BI.btypeid = BT.typeId where BT.FullName = '" + name + "' and CAST(S.OutFactoryDate AS DATE) between '" + nian + "-" + sangeyue + "-26' and '" + nian + "-" + shanghe + "-25' group by BT.FullName) as sanyueshiji on benyueshiji.FullName=sanyueshiji.FullName";
DataTable dgjp = GJPDbHelper.DAL.DateSet(gjp).Tables[0];
if (dgjp.Rows.Count == 1)
{
if (Convert.IsDBNull(dgjp.Rows[0]["本月金额"]) || dgjp.Rows[0]["本月金额"] == null || dgjp.Rows[0]["本月金额"].ToString() == "")
{
derp.Rows[i]["本月金额"] = "0.00";
}
else
{

derp.Rows[i]["本月金额"] = Convert.ToDecimal(dgjp.Rows[0]["本月金额"]).ToString("N");
}
if (Convert.IsDBNull(dgjp.Rows[0]["三月金额"]) || dgjp.Rows[0]["三月金额"] == null || dgjp.Rows[0]["三月金额"].ToString() == "")
{
derp.Rows[i]["三月金额"] = "0.00";
}
else
{
derp.Rows[i]["三月金额"] = Convert.ToDecimal(dgjp.Rows[0]["三月金额"]).ToString("N");
}
}
else
{
derp.Rows[i]["本月金额"] = "0.00";
derp.Rows[i]["三月金额"] = "0.00";
}
string sumamountStr = "select sum(cast((case when invlinshi.amount='' then '0' else isnull(invlinshi.amount,0) end)as float)) from Invoices_linshi invlinshi left join Invoices inv on invlinshi.customer_id = inv.customer_id left join " + gjpgjp + "btype c on c.typeid=inv.customer_id left join (select sum(cast((case when InvoicesMoney='' then '0' else isnull(InvoicesMoney,0) end)as decimal)) as benyueyikai,customer_id from [InvoiceSure] where invid in(select id from Invoices_linshi where duicuo=0) and left([dateStr],7) = '" + zhe + "' group by customer_id) as byyk on byyk.customer_id =inv.customer_id left join (select sum(cast((case when InvoicesMoney='' then '0' else isnull(InvoicesMoney,0) end)as decimal)) as sanyueyikai,customer_id from [InvoiceSure] where invid in(select id from Invoices_linshi where duicuo=0) and left([dateStr],10) between '" + nian + "-" + sangeyue + "-26' and '" + nian + "-" + shanghe + "-25' group by customer_id) syyk on syyk.customer_id=inv.customer_id where (left(invlinshi.creat_date,7) = '" + zhe + "' or invlinshi.creat_date ='2000-01-01') and (( inv.statement ='1' and invlinshi.statement_ci='') or (inv.statement ='2' and invlinshi.statement_ci='1') or (inv.statement ='2' and invlinshi.statement_ci='2') or (inv.statement ='1' and invlinshi.statement_ci='1') or (inv.statement ='3' and invlinshi.statement_ci='2')) and c.FullName='" + derp.Rows[i]["fullName"].ToString() + "'";

DataTable sumamountTable = DbHelper.DAL.DateSet(sumamountStr).Tables[0];
if (sumamountTable.Rows.Count == 1)
{
if (Convert.IsDBNull(sumamountTable.Rows[0][0]) || sumamountTable.Rows[0][0] == null || sumamountTable.Rows[0][0].ToString() == "")
{
derp.Rows[i]["amount"] = "0";
}
else
{

derp.Rows[i]["amount"] = Convert.ToInt32(Math.Round(Convert.ToDecimal(sumamountTable.Rows[0][0].ToString()), 0));
}
}
else
{
derp.Rows[i]["amount"] = "0";
}

}

this.C1GridView2.DataSource = DbHelper.DAL.ReplaceFullName(derp, "fullName");
this.C1GridView2.DataBind();

}
else
{
string mm = this.textbox_date.Text;
string str = "select count(*) from ins_fapiaotongji where time ='" + mm + "'";
object count = DbHelper.DAL.ExecuteScalar(str);
if (Convert.ToInt32(count) >= 1)
{
string zongj = "select * from ins_fapiaotongji where time ='" + mm + "' ";
DataTable dt = DbHelper.DAL.DateSet(zongj).Tables[0];
for (int i = 0; i < dt.Columns.Count; i++)
{
TextBox1.Text = dt.Rows[0][1].ToString();
TextBox2.Text = dt.Rows[0][2].ToString();
TextBox4.Text = dt.Rows[0][3].ToString();
TextBox6.Text = dt.Rows[0][7].ToString();
TextBox7.Text = dt.Rows[0][8].ToString();
TextBox5.Text = dt.Rows[0][4].ToString();
TextBox9.Text = dt.Rows[0][5].ToString();
TextBox10.Text = dt.Rows[0][6].ToString();
}
btnpanduan.Enabled = false;
btntijiao.Enabled = false;
}
else
{
TextBox1.Text = "";
TextBox2.Text = "";
TextBox4.Text = "";
TextBox6.Text = "";
TextBox7.Text = "";
TextBox5.Text = "";
TextBox9.Text = "";
TextBox10.Text = "";
}
btnpanduan.Enabled = true;
btntijiao.Enabled = true;
}

}

catch (Exception exc)
{
//Module failed to load
Exceptions.ProcessModuleLoadException(this, exc);
}
}
/// -----------------------------------------------------------------------------
/// <summary>
/// lstContent_ItemDataBound runs when items are bound. Here the
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
/// -----------------------------------------------------------------------------
protected void lstContent_ItemDataBound(object sender, System.Web.UI.WebControls.DataListItemEventArgs e)
{
string strContent = "";

// add content to template
if (!string.IsNullOrEmpty((string)Settings["template"]))
{
strContent = (string)Settings["template"];
ArrayList objProperties = CBO.GetPropertyInfo(typeof(AGL_FP_MONTHCONFIRMInfo));
foreach (PropertyInfo objPropertyInfo in objProperties)
{
strContent = strContent.Replace("[" + objPropertyInfo.Name.ToUpper() + "]", Server.HtmlDecode(DataBinder.Eval(e.Item.DataItem, objPropertyInfo.Name).ToString()));
}
}
else
{
strContent = DataBinder.Eval(e.Item.DataItem, "Content").ToString();
}

// assign the content
Label lblContent = (Label)e.Item.FindControl("lblContent");
lblContent.Text = strContent;
}

#endregion
#region "Optional Interfaces"

/// -----------------------------------------------------------------------------
/// <summary>
/// Registers the module actions required for interfacing with the portal framework
/// </summary>
/// <value></value>
/// <returns></returns>
/// <remarks></remarks>
/// <history>
/// </history>
/// -----------------------------------------------------------------------------
public ModuleActionCollection ModuleActions
{
get
{
ModuleActionCollection Actions = new ModuleActionCollection();
Actions.Add(GetNextActionID(), Localization.GetString(ModuleActionType.AddContent, this.LocalResourceFile),
ModuleActionType.AddContent, "", "add.gif", EditUrl(), false, DotNetNuke.Security.SecurityAccessLevel.Edit,
true, false);
return Actions;
}
}

#endregion
#region
//发票列表表示前处理
//protected void C1GridView1_RowDataBound(object sender, C1GridViewRowEventArgs e)
//{
// if (e.Row.RowType == C1GridViewRowType.DataRow)
// {
// //月结
// if (e.Row.Cells[2].Text.Trim().Replace("&nbsp;", "") == "0")
// {
// e.Row.Cells[2].Text = "否";
// }
// else if (e.Row.Cells[2].Text.Trim().Replace("&nbsp;", "") == "1")
// {
// e.Row.Cells[2].Text = "是";
// }
// //发票类型
// if (e.Row.Cells[3].Text.Trim().Replace("&nbsp;", "") == "0")
// {
// e.Row.Cells[3].Text = "增值税发票";
// }
// else if (e.Row.Cells[3].Text.Trim().Replace("&nbsp;", "") == "1")
// {
// e.Row.Cells[3].Text = "普通发票";
// }
// if (e.Row.Cells[4].Text == "&nbsp:")
// {
// e.Row.Cells[4].Text = "0.00";
// }
// string a = e.Row.Cells[5].Text;
// if (e.Row.Cells[5].Text == ""&nbsp"")
// {
// e.Row.Cells[5].Text = "0";
// }
// if (e.Row.Cells[6].Text == "&nbsp:")
// {
// e.Row.Cells[6].Text = "0.00";
// }
// double d1 = Convert.ToDouble(e.Row.Cells[4].Text);
// string d2 = Convert.ToString(e.Row.Cells[4].Text);
// double d3 =Convert.ToDouble(d2);
// if (d1 > d3)
// {
// e.Row.Cells[4].CssClass = "hong";
// }
// else
// {
// e.Row.Cells[4].CssClass = "lv";
// }
// }
//}
#endregion
protected void C1GridView2_RowDataBound(object sender, C1GridViewRowEventArgs e)
{
if (e.Row.RowType == C1GridViewRowType.DataRow)
{
if (Convert.IsDBNull(e.Row.Cells[8].Text.Trim()))
{
e.Row.Cells[8].Text = "0";
}
if (Convert.IsDBNull(e.Row.Cells[11].Text.Trim()))
{
e.Row.Cells[11].Text = "0";
}
if (string.IsNullOrEmpty(e.Row.Cells[8].Text.Trim()))
{
e.Row.Cells[8].Text = "0";
}
if (string.IsNullOrEmpty(e.Row.Cells[11].Text.Trim()))
{
e.Row.Cells[11].Text = "0";
}
if (e.Row.Cells[3].Text.Trim().Replace("&nbsp;", "") == "1")
{
if (e.Row.Cells[17].Text.Trim().Replace("&nbsp;", "") == "1")
{
e.Row.Cells[3].Text = "临时";
}
else
{
e.Row.Cells[3].Text = "固定";
//TextBox jine = (TextBox)e.Row.Cells[11].FindControl("TextBox8");
//jine.Text = "";
}
}
else if (e.Row.Cells[3].Text.Trim().Replace("&nbsp;", "") == "2")
{
TextBox sj = (TextBox)e.Row.Cells[13].FindControl("TextBox8");
sj.ReadOnly = true;
if (e.Row.Cells[17].Text.Trim().Replace("&nbsp;", "") == "2")
{
e.Row.Cells[3].Text = "临时";
}
else
{
e.Row.Cells[3].Text = "实次";
}
}
else if (e.Row.Cells[3].Text.Trim().Replace("&nbsp;", "") == "3")
{
TextBox ls = (TextBox)e.Row.Cells[13].FindControl("TextBox8");
ls.ReadOnly = true;
e.Row.Cells[3].Text = "临时";
}
//发票类型
if (e.Row.Cells[4].Text.Trim().Replace("&nbsp;", "") == "1")
{
e.Row.Cells[4].Text = "增值税专用";
}
else if (e.Row.Cells[4].Text.Trim().Replace("&nbsp;", "") == "2")
{
e.Row.Cells[4].Text = "增值税普发";
}
if (string.IsNullOrEmpty(e.Row.Cells[7].Text.Trim().Replace("&nbsp;", "")))
{
e.Row.Cells[7].Text = "0.00";
}
string a = e.Row.Cells[8].Text;
if (string.IsNullOrEmpty(e.Row.Cells[8].Text.Trim().Replace("&nbsp;", "")))
{
e.Row.Cells[8].Text = "0.00";
}
if (string.IsNullOrEmpty(e.Row.Cells[10].Text.Trim().Replace("&nbsp;", "")))
{
e.Row.Cells[10].Text = "0.00";
}
if (string.IsNullOrEmpty(e.Row.Cells[11].Text.Trim().Replace("&nbsp;", "")))
{
e.Row.Cells[11].Text = "0.00";
}
Button zs = (Button)e.Row.Cells[5].FindControl("btnzhangshu");
if (string.IsNullOrEmpty(zs.Text.Trim().Replace("&nbsp;", "")))
{
zs.Text = "0";
}
if (e.Row.Cells[24].Text.Trim() == "1")
{
string str = "select count(*) from ins_fapiaotongji where time ='" + this.textbox_date.Text + "'";
object count = DbHelper.DAL.ExecuteScalar(str);
if (Convert.ToInt32(count) >= 1)
{
TextBox jine = (TextBox)e.Row.Cells[13].FindControl("TextBox8");
jine.CssClass = "TextBox3";
jine.ReadOnly = true;
Button b = (Button)e.Row.Cells[23].FindControl("button3");
b.Enabled = false;
b.Text = "已处理";
}
else
{
TextBox jine = (TextBox)e.Row.Cells[13].FindControl("TextBox8");
Button b = (Button)e.Row.Cells[23].FindControl("button3");
string fullname1=e.Row.Cells[1].Text.Trim().Replace("&nbsp;", "");
if (e.Row.Cells[3].Text == "固定")
{
string weikai = "select * from InvoiceSure where invid in(select id from Invoices_linshi where duicuo=0) and statement='固定' and fullname='" + fullname1 + "' and left(dateStr,7)='" + this.textbox_date.Text + "'";
DataTable dt = DbHelper.DAL.DateSet(weikai).Tables[0];
if (dt.Rows.Count > 0)
{
jine.CssClass = "TextBox3";
jine.ReadOnly = true;
b.Enabled = false;
b.Text = "已处理";
}
else
{
jine.CssClass = "TextBox3";
jine.ReadOnly = false;
b.Enabled = true;
b.Text = "未处理";
}
}
else
{
jine.CssClass = "TextBox3";
jine.ReadOnly = true;
b.Enabled = false;
b.Text = "已处理";
}
}
}
else
{
Button b = (Button)e.Row.Cells[23].FindControl("button3");
b.Text = "未处理";
}
double d1 = Convert.ToDouble(e.Row.Cells[7].Text);
double d2 = Convert.ToDouble(e.Row.Cells[8].Text);

TextBox jine1 = (TextBox)e.Row.Cells[12].FindControl("TextBox3");
TextBox jine2 = (TextBox)e.Row.Cells[13].FindControl("TextBox8");
string aaa = jine1.Text.Trim().Replace("&nbsp;", "").ToString();
string bbb = jine2.Text.Trim().Replace("&nbsp;", "").ToString();
if (aaa == "")
{
aaa = "0.00";
}
if (bbb == "")
{
bbb = "0.00";
}
double d3 = Convert.ToDouble(aaa);
double d4 = Convert.ToDouble(bbb);
//if (d3 > d4)
//{
// jine1.CssClass = "hong";
//}
//else if (d3 < d4)
//{
// jine2.CssClass = "hong";
//}
//else
//{

//}
//if (d1 > d2)
//{
// e.Row.Cells[6].CssClass = "hong";
//}
//else if(d1<d2)
//{
// e.Row.Cells[7].CssClass = "hong";
//}
//else
//{

//}
if (e.Row.Cells[27].Text.Trim() == "0")
{
e.Row.Cells[27].Text = "未处理";
}
else
{
e.Row.Cells[27].Text = "已处理";
}
//string dc=e.Row.Cells[25].Text;
//if (dc == "1")
//{
// e.Row.Cells[0].CssClass = "cuowu";
//}
if (e.Row.Cells[3].Text.Trim().Replace("&nbsp;", "") != "固定")
{
TextBox jine4 = (TextBox)e.Row.Cells[13].FindControl("TextBox8");
//jine4.Text = "";
jine4.ReadOnly = true;
//jine4.CssClass = "gray";
}
else
{
TextBox jine4 = (TextBox)e.Row.Cells[13].FindControl("TextBox8");
jine4.Text = Convert.ToDecimal(e.Row.Cells[10].Text).ToString("N"); ;
}
double he = Convert.ToDouble(e.Row.Cells[10].Text);
double yikai = Convert.ToDouble(e.Row.Cells[11].Text);
System.Web.UI.WebControls.TextBox kekai = (System.Web.UI.WebControls.TextBox)e.Row.Cells[12].FindControl("TextBox3");
if (yikai < 0)
{
kekai.Text = Convert.ToDecimal((he + yikai)).ToString("N");
}
else
{
kekai.Text = Convert.ToDecimal((he - yikai)).ToString("N");
}
string m = textbox_date.Text;
string fullname = e.Row.Cells[20].Text.Trim();
string ID = e.Row.Cells[28].Text.Trim();
double liuliu = Convert.ToDouble(e.Row.Cells[7].Text);
double qiqi = Convert.ToDouble(e.Row.Cells[8].Text);
System.Web.UI.WebControls.Label ljq = (System.Web.UI.WebControls.Label)e.Row.Cells[9].FindControl("TextBoxchae");
ljq.Text = Convert.ToDecimal((liuliu - qiqi)).ToString("N");
if (Convert.ToDecimal(ljq.Text) < 0)
{
//ljq.CssClass = "hong";
e.Row.Cells[9].CssClass = "hong";
}

//string cusid = e.Row.Cells[12].Text.Trim();
System.Web.UI.WebControls.Button btn_OrderDetail = (System.Web.UI.WebControls.Button)e.Row.Cells[23].FindControl("button1");
btn_OrderDetail.Attributes.Add("onclick", "return dnnModal.show('/DesktopModules/AGL_FP_MONTHCONFIRM/xinxi.aspx?fullname=" +
fullname + "&ID=" + ID + "&m=" + m + "&popUp=true',true,800,1000,false)");
System.Web.UI.WebControls.Button btnzhangshu = (System.Web.UI.WebControls.Button)e.Row.Cells[26].FindControl("btnzhangshu");
btnzhangshu.Attributes.Add("onclick", "return dnnModal.show('/DesktopModules/AGL_FP_MONTHCONFIRM/fapiao.aspx?fullname=" +
fullname + "&ID="
+ ID + "&m=" + m + "&popUp=true',true,400,1000,false)");
}
}

protected void btnqueren_Click(object sender, EventArgs e)
{
string fapiao = "";
if (fapiao == "增值税")
{
fapiao = "0";
}
else
{
fapiao = "1";
}
string guding = "";
if (guding == "是")
{
guding = "1";
}
else
{
guding = "0";
}
string mont = this.textbox_date.Text;
//string mont = "2017-08";
string str = "select count(*) from ins_fapiaotongji where time ='" + mont + "'";
object count = DbHelper.DAL.ExecuteScalar(str);
if (Convert.ToInt32(count) >= 1)
{
string zongj = "select * from ins_fapiaotongji where time ='" + mont + "' ";
DataTable dt = DbHelper.DAL.DateSet(zongj).Tables[0];
for (int i = 0; i < dt.Columns.Count; i++)
{
TextBox1.Text = dt.Rows[0][1].ToString();
TextBox2.Text = dt.Rows[0][2].ToString();
TextBox4.Text = dt.Rows[0][3].ToString();
TextBox6.Text = dt.Rows[0][7].ToString();
TextBox7.Text = dt.Rows[0][8].ToString();
TextBox10.Text = dt.Rows[0][6].ToString();
TextBox9.Text = dt.Rows[0][5].ToString();
TextBox5.Text = dt.Rows[0][4].ToString();
}
btnpanduan.Enabled = false;
btntijiao.Enabled = false;
}
//string zhe = "2017-08";
string zhe = this.textbox_date.Text;//年月
string[] zhehe = zhe.Split('-');
string nian = zhehe[0].ToString();//年
string shanghe;
string zhe1;
string sangeyue = "";
if (Convert.ToInt32(zhehe[1]) == 1)
{
nian = (Convert.ToInt32(nian) - 1).ToString();//去年
shanghe = "12";
sangeyue = (Convert.ToInt32(shanghe) - 3).ToString();
}
else
{
if (Convert.ToInt32(zhehe[1]) > 10)
{
shanghe = (Convert.ToInt32(zhehe[1]) - 1).ToString();
}
else
{
shanghe = "0" + (Convert.ToInt32(zhehe[1]) - 1).ToString();
}
}
if (Convert.ToInt32(zhehe[1]) == 1)
{
sangeyue = "09";
}
if (Convert.ToInt32(zhehe[1]) == 2)
{
sangeyue = "10";
}
if (Convert.ToInt32(zhehe[1]) == 3)
{
sangeyue = "11";
}
if (Convert.ToInt32(zhehe[1]) == 4)
{
sangeyue = "12";
}
if (Convert.ToInt32(zhehe[1]) >= 5)
{
sangeyue = "0" + (Convert.ToInt32(zhehe[1]) - 4).ToString();
}
//select c.fullName,inv.fengzFlag,inv.customer_id,inv.company,inv.id,inv.amount,inv.account_bank,inv.account_number,inv.address,inv.beizhu,inv.identity_number,inv.telephone,inv.address,inv.fp_type,inv.statement,byyk.benyueyikai,syyk.sanyueyikai as sanyueyikai
//string erp = "select distinct c.fullName,0 as fengzFlag,inv.customer_id,inv.company,0 as id,0 as amount,inv.account_bank,inv.account_number,inv.address,inv.identity_number,inv.telephone,inv.address,0 as fp_type,inv.statement,byyk.benyueyikai,syyk.sanyueyikai as sanyueyikai from Invoices inv left join " + gjpgjp + "btype c on c.typeid=inv.customer_id left join (select sum(cast((case when InvoicesMoney='' then '0' else isnull(InvoicesMoney,0) end)as decimal)) as benyueyikai,customer_id from [InvoiceSure] where left([dateStr],10) between '" + nian + "-" + shanghe + "-26' and '" + zhe + "-25' group by customer_id) as byyk on byyk.customer_id =inv.customer_id left join (select sum(cast((case when InvoicesMoney='' then '0' else isnull(InvoicesMoney,0) end)as decimal)) as sanyueyikai,customer_id from [InvoiceSure] where left([dateStr],10) between '" + nian + "-" + sangeyue + "-26' and '" + nian + "-" + shanghe + "-25' group by customer_id) syyk on syyk.customer_id=inv.customer_id where (inv.creat_date between '" + nian + "-" + shanghe + "-26' and '" + zhe + "-25' or inv.creat_date ='2000-01-01') and (( inv.statement =1 and inv.statement_ci='') or (inv.statement =2 and inv.statement_ci=1) or (inv.statement =3 and inv.statement_ci =2)) order by inv.statement asc";
// left join Invoices_linshi invlinshi on invlinshi.customer_id = inv.customer_id

string erp = "select distinct c.fullName,invlinshi.fengzFlag,inv.customer_id,inv.company,invlinshi.id,0 as amount,inv.account_bank,inv.account_number,inv.address,inv.identity_number,inv.telephone,inv.address,invlinshi.fp_type,invlinshi.statement_ci,invlinshi.state,inv.statement,byyk.benyueyikai,syyk.sanyueyikai as sanyueyikai from Invoices inv left join Invoices_linshi invlinshi on invlinshi.customer_id = inv.customer_id left join " + gjpgjp + "btype c on c.typeid=inv.customer_id left join (select sum(cast((case when InvoicesMoney='' then '0' else isnull(InvoicesMoney,0) end)as decimal)) as benyueyikai,customer_id from [InvoiceSure] where invid in(select id from Invoices_linshi where duicuo=0) and left([dateStr],7) = '" + zhe + "' group by customer_id) as byyk on byyk.customer_id =inv.customer_id left join (select sum(cast((case when InvoicesMoney='' then '0' else isnull(InvoicesMoney,0) end)as decimal)) as sanyueyikai,customer_id from [InvoiceSure] where invid in(select id from Invoices_linshi where duicuo=0) and left([dateStr],10) between '" + nian + "-" + sangeyue + "-26' and '" + nian + "-" + shanghe + "-25' group by customer_id) syyk on syyk.customer_id=inv.customer_id where (left(invlinshi.creat_date,7) ='"+zhe+"' or invlinshi.creat_date ='2000-01-01') and (( inv.statement ='1' and invlinshi.statement_ci='')) order by inv.statement asc";

DataTable derp = DbHelper.DAL.DateSet(erp).Tables[0];
derp.Columns.Add("本月金额", typeof(string));
derp.Columns.Add("三月金额", typeof(string));
//foreach (DataRow dr in derp.Rows)
//{
// dr["本月金额"] = "0";
// dr["三月金额"] = "0";
//}
for (int i = 0; i < derp.Rows.Count; i++)
{
if (Convert.IsDBNull(derp.Rows[i]["benyueyikai"]) || derp.Rows[i]["benyueyikai"] == null || derp.Rows[i]["benyueyikai"].ToString() == "")
{
derp.Rows[i]["benyueyikai"] = "0";
}
if (Convert.IsDBNull(derp.Rows[0]["sanyueyikai"]) || derp.Rows[0]["sanyueyikai"] == null || derp.Rows[0]["sanyueyikai"].ToString() == "")
{
derp.Rows[i]["sanyueyikai"] = "0";
}
if (Convert.IsDBNull(derp.Rows[i]["amount"]) || derp.Rows[i]["amount"] == null || derp.Rows[i]["amount"].ToString() == "")
{
derp.Rows[i]["amount"] = "0";
}
string name = derp.Rows[i][0].ToString();
string gjp = "select * from (SELECT isnull(sum(cast(S.total as decimal)),0) AS 本月金额,BT.FullName as FullName FROM dbo.ASum_SaleBill AS S LEFT OUTER JOIN dbo.ASum_BillIndex AS BI ON S.BillNumberId = BI.BillNumberId LEFT OUTER JOIN dbo.btype AS BT ON BI.btypeid = BT.typeId where BT.FullName = '" + name + "' and left(CAST(S.OutFactoryDate AS DATE),7) = '" + zhe + "' group by BT.FullName ) as benyueshiji left join (SELECT isnull(sum(cast(S.total as decimal)),0) AS 三月金额,BT.FullName FROM dbo.ASum_SaleBill AS S LEFT OUTER JOIN dbo.ASum_BillIndex AS BI ON S.BillNumberId = BI.BillNumberId LEFT OUTER JOIN dbo.btype AS BT ON BI.btypeid = BT.typeId where BT.FullName = '" + name + "' and CAST(S.OutFactoryDate AS DATE) between '" + nian + "-" + sangeyue + "-26' and '" + nian + "-" + shanghe + "-25' group by BT.FullName) as sanyueshiji on benyueshiji.FullName=sanyueshiji.FullName";
DataTable dgjp = GJPDbHelper.DAL.DateSet(gjp).Tables[0];
if (dgjp.Rows.Count == 1)
{
if (Convert.IsDBNull(dgjp.Rows[0]["本月金额"]) || dgjp.Rows[0]["本月金额"] == null || dgjp.Rows[0]["本月金额"].ToString() == "")
{
derp.Rows[i]["本月金额"] = "0";
}
else
{

derp.Rows[i]["本月金额"] = Convert.ToDecimal(dgjp.Rows[0]["本月金额"]).ToString("N");
}
if (Convert.IsDBNull(dgjp.Rows[0]["三月金额"]) || dgjp.Rows[0]["三月金额"] == null || dgjp.Rows[0]["三月金额"].ToString() == "")
{
derp.Rows[i]["三月金额"] = "0";
}
else
{
derp.Rows[i]["三月金额"] = Convert.ToDecimal(dgjp.Rows[0]["三月金额"]).ToString("N");
}
}
else
{
derp.Rows[i]["本月金额"] = "0.00";
derp.Rows[i]["三月金额"] = "0.00";
}
string sumamountStr = "select sum(cast((case when invlinshi.amount='' then '0' else isnull(invlinshi.amount,0) end)as float)) from Invoices_linshi invlinshi left join Invoices inv on invlinshi.customer_id = inv.customer_id left join " + gjpgjp + "btype c on c.typeid=inv.customer_id left join (select sum(cast((case when InvoicesMoney='' then '0' else isnull(InvoicesMoney,0) end)as decimal)) as benyueyikai,customer_id from [InvoiceSure] where invid in(select id from Invoices_linshi where duicuo=0) and left([dateStr],7) = '" + zhe + "' group by customer_id) as byyk on byyk.customer_id =inv.customer_id left join (select sum(cast((case when InvoicesMoney='' then '0' else isnull(InvoicesMoney,0) end)as decimal)) as sanyueyikai,customer_id from [InvoiceSure] where invid in(select id from Invoices_linshi where duicuo=0) and left([dateStr],10) between '" + nian + "-" + sangeyue + "-26' and '" + nian + "-" + shanghe + "-25' group by customer_id) syyk on syyk.customer_id=inv.customer_id where (left(invlinshi.creat_date,7) = '" + zhe + "' or invlinshi.creat_date ='2000-01-01') and (( inv.statement ='1' and invlinshi.statement_ci='') or (inv.statement ='2' and invlinshi.statement_ci='1') or (inv.statement ='2' and invlinshi.statement_ci='2') or (inv.statement ='1' and invlinshi.statement_ci='1') or (inv.statement ='3' and invlinshi.statement_ci='2')) and c.FullName='" + derp.Rows[i]["fullName"].ToString() + "'";
DataTable sumamountTable = DbHelper.DAL.DateSet(sumamountStr).Tables[0];
if (sumamountTable.Rows.Count == 1)
{
if (Convert.IsDBNull(sumamountTable.Rows[0][0]) || sumamountTable.Rows[0][0] == null || sumamountTable.Rows[0][0].ToString() == "")
{
derp.Rows[i]["amount"] = "0";
}
else
{

derp.Rows[i]["amount"] = Convert.ToInt32(Math.Round(Convert.ToDecimal(sumamountTable.Rows[0][0].ToString()), 0));
}
}
else
{
derp.Rows[i]["amount"] = "0";
}
}
this.C1GridView2.DataSource = DbHelper.DAL.ReplaceFullName(derp, "fullName");
this.C1GridView2.DataBind();
changzong();
}
protected void button3_Click(object sender, EventArgs e)
{
btnpanduan.Enabled = false;
btntijiao.Enabled = false;
string m = textbox_date.Text;
InsusJsUtility jis = new InsusJsUtility();
string yea = DateTime.Now.ToString("yyyy-MM-dd");
string mm = this.textbox_date.Text; ;
Button button = (Button)sender;
C1GridViewRow gvr = (C1GridViewRow)button.Parent.Parent;
string company = C1GridView2.Rows[gvr.RowIndex].Cells[2].Text.Trim();
string statement = C1GridView2.Rows[gvr.RowIndex].Cells[3].Text.Trim();
string fp_type = C1GridView2.Rows[gvr.RowIndex].Cells[4].Text.Trim();
string total = C1GridView2.Rows[gvr.RowIndex].Cells[7].Text.Trim();
string out_totalmoney = C1GridView2.Rows[gvr.RowIndex].Cells[8].Text.Trim();
string moth = C1GridView2.Rows[gvr.RowIndex].Cells[10].Text.Trim().ToString();
string yikai = C1GridView2.Rows[gvr.RowIndex].Cells[11].Text.Trim();
TextBox jine = (TextBox)C1GridView2.Rows[gvr.RowIndex].Cells[13].FindControl("TextBox8");
decimal d = Convert.ToDecimal(jine.Text.Trim());
string mon = d.ToString();
string customer_id = C1GridView2.Rows[gvr.RowIndex].Cells[14].Text.Trim();
string identity_number = C1GridView2.Rows[gvr.RowIndex].Cells[15].Text.Trim();
string state = C1GridView2.Rows[gvr.RowIndex].Cells[16].Text.Trim();
string statement_ci = C1GridView2.Rows[gvr.RowIndex].Cells[17].Text.Trim().Replace("&nbsp;","");
string address = C1GridView2.Rows[gvr.RowIndex].Cells[18].Text.Trim();
string telephone = C1GridView2.Rows[gvr.RowIndex].Cells[19].Text.Trim();
string fullname = C1GridView2.Rows[gvr.RowIndex].Cells[20].Text.Trim();
string account_bank = C1GridView2.Rows[gvr.RowIndex].Cells[21].Text.Trim();
string account_number = C1GridView2.Rows[gvr.RowIndex].Cells[22].Text.Trim();
int invid = Convert.ToInt32(C1GridView2.Rows[gvr.RowIndex].Cells[28].Text.Trim());
string str = "insert into [InvoiceSure] values(0," + invid + ",'" + customer_id + "','" + mon + "',null,'" + yea + "','" + state + "','" + account_bank + "','" + account_number + "','" + statement + "','" + statement_ci + "','" + identity_number + "','" + address + "','" + telephone + "','" + fp_type + "','" + mon + "','" + fullname + "','" + company + "','封账',1,"+this.UserId.ToString()+")";
double benyue = Convert.ToDouble(moth);
double yik = Convert.ToDouble(yikai);
double cha = benyue - yik;

if (string.IsNullOrEmpty(mon))
{
Response.Write("<script>alert('请输入')</script>");
C1GridView2.Rows[gvr.RowIndex].Cells[13].FindControl("TextBox3").Focus();
}
else
{
DbHelper.DAL.addOperation_Log(this.UserId.ToString(), "发票月度封账", "新增", "对客户名称为" + fullname + "id为" + invid + "的发票进行了封账");
string up = "update [Invoices_linshi] set fengzFlag = 1 where customer_id ='" + customer_id + "' and id=" + invid + " ";
DbHelper.DAL.ExecuteNonQuery(up);
int count = DbHelper.DAL.ExecuteNonQuery(str);
if (count >= 1)
{
int a = gvr.RowIndex;
string year = DateTime.Now.ToString("yyyy");
string fapiao = "";
if (fapiao == "增值税")
{
fapiao = "0";
}
else
{
fapiao = "1";
}
string guding = "";
if (guding == "是")
{
guding = "1";
}
else
{
guding = "0";
}
string mont = this.textbox_date.Text;
string[] c = mont.Split('-');
int shijiyuefen = Convert.ToInt32(c[1]) - 3;
string mont1 = "";
if (shijiyuefen < 10)
{
mont1 = "0" + shijiyuefen.ToString();
}
else
{
mont1 = shijiyuefen.ToString();
}
string zhe = this.textbox_date.Text;//年月
string[] zhehe = zhe.Split('-');
string nian = zhehe[0].ToString();
string shanghe;
string zhe1;
string sangeyue = "";
if (Convert.ToInt32(zhehe[1]) == 1)
{
nian = (Convert.ToInt32(nian) - 1).ToString();//去年
shanghe = "12";
sangeyue = (Convert.ToInt32(shanghe) - 3).ToString();
}
else
{
if (Convert.ToInt32(zhehe[1]) > 10)
{
shanghe = (Convert.ToInt32(zhehe[1]) - 1).ToString();
}
else
{
shanghe = "0" + (Convert.ToInt32(zhehe[1]) - 1).ToString();
}
}
if (Convert.ToInt32(zhehe[1]) == 1)
{
sangeyue = "09";
}
if (Convert.ToInt32(zhehe[1]) == 2)
{
sangeyue = "10";
}
if (Convert.ToInt32(zhehe[1]) == 3)
{
sangeyue = "11";
}
if (Convert.ToInt32(zhehe[1]) == 4)
{
sangeyue = "12";
}
if (Convert.ToInt32(zhehe[1]) >= 5)
{
sangeyue = "0" + (Convert.ToInt32(zhehe[1]) - 4).ToString();
}

string erp = "select distinct c.fullName,invlinshi.fengzFlag,inv.customer_id,inv.company,invlinshi.id,0 as amount,inv.account_bank,inv.account_number,inv.address,inv.identity_number,inv.telephone,inv.address,invlinshi.fp_type,invlinshi.statement_ci,invlinshi.state,inv.statement,byyk.benyueyikai,syyk.sanyueyikai as sanyueyikai from Invoices inv left join Invoices_linshi invlinshi on invlinshi.customer_id = inv.customer_id left join " + gjpgjp + "btype c on c.typeid=inv.customer_id left join (select sum(cast((case when InvoicesMoney='' then '0' else isnull(InvoicesMoney,0) end)as decimal)) as benyueyikai,customer_id from [InvoiceSure] where invid in(select id from Invoices_linshi where duicuo=0) and left([dateStr],7) = '" + zhe + "' group by customer_id) as byyk on byyk.customer_id =inv.customer_id left join (select sum(cast((case when InvoicesMoney='' then '0' else isnull(InvoicesMoney,0) end)as decimal)) as sanyueyikai,customer_id from [InvoiceSure] where invid in(select id from Invoices_linshi where duicuo=0) and left([dateStr],10) between '" + nian + "-" + sangeyue + "-26' and '" + nian + "-" + shanghe + "-25' group by customer_id) syyk on syyk.customer_id=inv.customer_id where (left(invlinshi.creat_date,7) = '" + zhe + "' or inv.creat_date ='2000-01-01') and (( inv.statement ='1' and invlinshi.statement_ci='') or (inv.statement ='2' and invlinshi.statement_ci='1') or (inv.statement ='2' and invlinshi.statement_ci='2') or (inv.statement ='1' and invlinshi.statement_ci='1') or (inv.statement ='3' and invlinshi.statement_ci='2')) order by inv.statement asc";

DataTable derp = DbHelper.DAL.DateSet(erp).Tables[0];
derp.Columns.Add("本月金额", typeof(string));
derp.Columns.Add("三月金额", typeof(string));
for (int i = 0; i < derp.Rows.Count; i++)
{
if (Convert.IsDBNull(derp.Rows[i]["benyueyikai"]) || derp.Rows[i]["benyueyikai"] == null || derp.Rows[i]["benyueyikai"].ToString() == "")
{
derp.Rows[i]["benyueyikai"] = "0.00";
}
if (Convert.IsDBNull(derp.Rows[0]["sanyueyikai"]) || derp.Rows[0]["sanyueyikai"] == null || derp.Rows[0]["sanyueyikai"].ToString() == "")
{
derp.Rows[i]["sanyueyikai"] = "0.00";
}
if (Convert.IsDBNull(derp.Rows[i]["amount"]) || derp.Rows[i]["amount"] == null || derp.Rows[i]["amount"].ToString() == "")
{
derp.Rows[i]["amount"] = "0";
}
string name = derp.Rows[i][0].ToString();
string gjp = "select * from (SELECT isnull(sum(cast(S.total as decimal)),0) AS 本月金额,BT.FullName FROM dbo.ASum_SaleBill AS S LEFT OUTER JOIN dbo.ASum_BillIndex AS BI ON S.BillNumberId = BI.BillNumberId LEFT OUTER JOIN dbo.btype AS BT ON BI.btypeid = BT.typeId where BT.FullName = '" + name + "' and left(CAST(S.OutFactoryDate AS DATE),7) = '" + zhe + "' group by BT.FullName ) as benyueshiji left join (SELECT isnull(sum(cast(S.total as decimal)),0) AS 三月金额,BT.FullName FROM dbo.ASum_SaleBill AS S LEFT OUTER JOIN dbo.ASum_BillIndex AS BI ON S.BillNumberId = BI.BillNumberId LEFT OUTER JOIN dbo.btype AS BT ON BI.btypeid = BT.typeId where BT.FullName = '" + name + "' and CAST(S.OutFactoryDate AS DATE) between '" + nian + "-" + sangeyue + "-26' and '" + nian + "-" + shanghe + "-25' group by BT.FullName) as sanyueshiji on benyueshiji.FullName=sanyueshiji.FullName";
DataTable dgjp = GJPDbHelper.DAL.DateSet(gjp).Tables[0];
if (dgjp.Rows.Count == 1)
{

derp.Rows[i]["本月金额"] = Convert.ToString(dgjp.Rows[0]["本月金额"]);
derp.Rows[i]["三月金额"] = Convert.ToString(dgjp.Rows[0]["三月金额"]);
}
else
{
derp.Rows[i]["本月金额"] = "0";
derp.Rows[i]["三月金额"] = "0";
}
string sumamountStr = "select sum(cast((case when invlinshi.amount='' then '0' else isnull(invlinshi.amount,0) end)as float)) from Invoices_linshi invlinshi left join Invoices inv on invlinshi.customer_id = inv.customer_id left join " + gjpgjp + "btype c on c.typeid=inv.customer_id left join (select sum(cast((case when InvoicesMoney='' then '0' else isnull(InvoicesMoney,0) end)as decimal)) as benyueyikai,customer_id from [InvoiceSure] where invid in(select id from Invoices_linshi where duicuo=0) and left([dateStr],7) = '" + zhe + "' group by customer_id) as byyk on byyk.customer_id =inv.customer_id left join (select sum(cast((case when InvoicesMoney='' then '0' else isnull(InvoicesMoney,0) end)as decimal)) as sanyueyikai,customer_id from [InvoiceSure] where invid in(select id from Invoices_linshi where duicuo=0) and left([dateStr],10) between '" + nian + "-" + sangeyue + "-26' and '" + nian + "-" + shanghe + "-25' group by customer_id) syyk on syyk.customer_id=inv.customer_id where (left(invlinshi.creat_date,7) = '" + zhe + "' or invlinshi.creat_date ='2000-01-01') and (( inv.statement ='1' and invlinshi.statement_ci='') or (inv.statement ='2' and invlinshi.statement_ci='1') or (inv.statement ='2' and invlinshi.statement_ci='2') or (inv.statement ='1' and invlinshi.statement_ci='1') or (inv.statement ='3' and invlinshi.statement_ci='2')) and c.FullName='" + derp.Rows[i]["fullName"].ToString() + "'";
DataTable sumamountTable = DbHelper.DAL.DateSet(sumamountStr).Tables[0];
if (sumamountTable.Rows.Count == 1)
{
if (Convert.IsDBNull(sumamountTable.Rows[0][0]) || sumamountTable.Rows[0][0] == null || sumamountTable.Rows[0][0].ToString() == "")
{
derp.Rows[i]["amount"] = "0";
}
else
{

derp.Rows[i]["amount"] = Convert.ToInt32(Math.Round(Convert.ToDecimal(sumamountTable.Rows[0][0].ToString()), 0));
}
}
else
{
derp.Rows[i]["amount"] = "0";
}
}
this.C1GridView2.DataSource = DbHelper.DAL.ReplaceFullName(derp, "fullName");

this.C1GridView2.DataBind();
TextBox read = (TextBox)C1GridView2.Rows[gvr.RowIndex].Cells[13].FindControl("TextBox8");
read.ReadOnly = true;
Button b = (Button)C1GridView2.Rows[gvr.RowIndex].Cells[23].FindControl("button3");
b.Enabled = false;
b.Text = "已处理";
changzong();
Response.Write("<script>alert('已成功封账')</script>");
}
}

}

protected void btnjisuan_Click(object sender, EventArgs e)
{
string year = DateTime.Now.ToString("yyyy");
string fapiao = "";
if (fapiao == "增值税专用")
{
fapiao = "0";
}
else
{
fapiao = "1";
}
string guding = "";
if (guding == "是")
{
guding = "1";
}
else
{
guding = "0";
}
double alljine = 0;
foreach (C1GridViewRow gvr in C1GridView2.Rows)
{
TextBox a = (TextBox)C1GridView2.Rows[gvr.RowIndex].Cells[11].FindControl("TextBox3");
alljine += Convert.ToDouble(a.Text.Trim());
}
//string mont = "2017-08";
string mont = this.textbox_date.Text;
string[] c = mont.Split('-');
//string[] b = mont.Split('-');
int shijiyuefen = Convert.ToInt32(c[1]) - 3;
string mont1 = "";
if (shijiyuefen < 10)
{
mont1 = "0" + shijiyuefen.ToString();
}
else
{
mont1 = shijiyuefen.ToString();
}
string str4 = " select sum(cast((case when InvoicesMoney='' then '0' else isnull(InvoicesMoney,0) end)as decimal)) from [InvoiceSure] where invid in(select id from Invoices_linshi where duicuo=0) and left([dateStr],7) = '" + mont + "'";
object ob = DbHelper.DAL.ExecuteScalar(str4);
string str5 = "select count(*) from [InvoiceSure] where invid in(select id from Invoices_linshi where duicuo=0) and left([dateStr],7) = '" + mont + "'";
object cishu = DbHelper.DAL.ExecuteScalar(str5);
this.txtkaizhang.Text = alljine.ToString();
this.txtgeshu.Text = cishu.ToString();
}
protected void button4_Click(object sender, EventArgs e)
{ }

protected void btntijiao_Click(object sender, EventArgs e)
{
try
{
string aaa = DbHelper.DAL.ExecuteScalar("select DisplayName from Users where UserID=" + this.UserId.ToString()).ToString();
if (aaa.Trim() == "杜总")
{

double alljine = 0;// 发票总额
double pujin = 0;//普通发票金额
double zzsjin = 0;//增值税发票金额
double fplxbfb = 0;//发票类型百分比
double gdyjjin = 0;//固定月结金额
double cjjin = 0;//次结金额
int a = 0;
int b = 0;
int zongshu = C1GridView2.Rows.Count;
foreach (C1GridViewRow gvr in C1GridView2.Rows)
{
string statement = C1GridView2.Rows[gvr.RowIndex].Cells[3].Text.Trim();
string fp_type = C1GridView2.Rows[gvr.RowIndex].Cells[4].Text.Trim();
if (fp_type == "增值税专用")
{
TextBox jine1 = (TextBox)C1GridView2.Rows[gvr.RowIndex].Cells[13].FindControl("TextBox8");//本次发票金额
if (jine1.Text == "")
{
jine1.Text = "0";
}
zzsjin += Convert.ToDouble(jine1.Text.Trim());
a++;
}
else
{
TextBox jine2 = (TextBox)C1GridView2.Rows[gvr.RowIndex].Cells[13].FindControl("TextBox8");//本次发票金额
if (jine2.Text == "")
{
jine2.Text = "0";
}
pujin += Convert.ToDouble(jine2.Text.Trim());
b++;
}
if (statement == "固定")
{
TextBox jine3 = (TextBox)C1GridView2.Rows[gvr.RowIndex].Cells[13].FindControl("TextBox8");//本次发票金额
if (jine3.Text == "")
{
jine3.Text = "0";
}
gdyjjin += Convert.ToDouble(jine3.Text.Trim());
}
else
{
TextBox jine4 = (TextBox)C1GridView2.Rows[gvr.RowIndex].Cells[13].FindControl("TextBox8");//本次发票金额
if (jine4.Text == "")
{
jine4.Text = "0";
}
cjjin += Convert.ToDouble(jine4.Text.Trim());
}
string mm = this.textbox_date.Text;
//string mm = "2017-08";
string moth = C1GridView2.Rows[gvr.RowIndex].Cells[10].Text.Trim();//本月实际金额
string yikai = C1GridView2.Rows[gvr.RowIndex].Cells[11].Text.Trim();//已开发票金额
TextBox jine = (TextBox)C1GridView2.Rows[gvr.RowIndex].Cells[13].FindControl("TextBox8");//本次发票金额
alljine += Convert.ToDouble(jine.Text.Trim());
string customer_id = C1GridView2.Rows[gvr.RowIndex].Cells[14].Text.Trim();
int invid = Convert.ToInt32(C1GridView2.Rows[gvr.RowIndex].Cells[28].Text.Trim());
string mon = jine.Text.Trim();
double benyue = Convert.ToDouble(moth);
double yik = Convert.ToDouble(yikai);
double cha = benyue - yik;
}
//string zhe = "2017-08";
string zhe = this.textbox_date.Text;//年月
//string nian = zhe[0].ToString();//年
string[] zhehe = zhe.Split('-');
string nian = zhehe[0].ToString();
string shanghe;
string zhe1;
string sangeyue = "";
if (Convert.ToInt32(zhehe[1]) == 1)
{
nian = (Convert.ToInt32(nian) - 1).ToString();//去年
shanghe = "12";
sangeyue = (Convert.ToInt32(shanghe) - 3).ToString();
}
else
{
if (Convert.ToInt32(zhehe[1]) > 10)
{
shanghe = (Convert.ToInt32(zhehe[1]) - 1).ToString();
}
else
{
shanghe = "0" + (Convert.ToInt32(zhehe[1]) - 1).ToString();
}
}
if (Convert.ToInt32(zhehe[1]) == 1)
{
sangeyue = "09";
}
if (Convert.ToInt32(zhehe[1]) == 2)
{
sangeyue = "10";
}
if (Convert.ToInt32(zhehe[1]) == 3)
{
sangeyue = "11";
}
if (Convert.ToInt32(zhehe[1]) == 4)
{
sangeyue = "12";
}
if (Convert.ToInt32(zhehe[1]) >= 5)
{
sangeyue = "0" + (Convert.ToInt32(zhehe[1]) - 4).ToString();
}
string zjine = "select sum(cast((case when amount='' then '0' else isnull(amount,0) end)as float)) amount from Invoices_linshi WHERE left([creat_date],7) = '" + zhe + "'";

object obzjine = DbHelper.DAL.ExecuteScalar(zjine);
alljine += Convert.ToDouble(obzjine);
cjjin = Convert.ToDouble(obzjine);
string zzszy = "select isnull(count(*),0) c from Invoices inv left join Invoices_linshi invlinshi on invlinshi.customer_id = inv.customer_id WHERE (left(invlinshi.creat_date,7) ='"+zhe+"' or inv.creat_date ='2000-01-01') and invlinshi.fp_type='1' and (( inv.statement ='1' and invlinshi.statement_ci='') or (inv.statement ='3' and invlinshi.statement_ci='1') or (inv.statement ='2' and invlinshi.statement_ci='1') or (inv.statement ='3' and invlinshi.statement_ci='2'))";

//int zzszycount = DbHelper.DAL.ExecuteNonQuery(zjine);
object o1 = DbHelper.DAL.ExecuteScalar(zzszy);
int zzszycount = Convert.ToInt32(o1);
string ptpt = "select isnull(count(*),0) c from Invoices inv left join Invoices_linshi invlinshi on invlinshi.customer_id = inv.customer_id WHERE (left(invlinshi.creat_date,7) ='"+zhe+"' or inv.creat_date ='2000-01-01') and invlinshi.fp_type='2' and (( inv.statement ='1' and invlinshi.statement_ci='') or (inv.statement ='3' and invlinshi.statement_ci='1') or (inv.statement ='2' and invlinshi.statement_ci='1') or (inv.statement ='3' and invlinshi.statement_ci='2'))";

//int ptcount = DbHelper.DAL.ExecuteNonQuery(ptpt);
object o12 = DbHelper.DAL.ExecuteScalar(ptpt);
int ptcount = Convert.ToInt32(o12);
double zzs = Math.Round((double)zzszycount / (Convert.ToDouble(zzszycount) + Convert.ToDouble(ptcount)), 3) * 100;
double pt = 100 - zzs;
TextBox5.Text = zzs.ToString() + "%" + "/" + pt.ToString() + "%";
string year = DateTime.Now.ToString("yyyy");
//string mont = "2017-08";
string mont = this.textbox_date.Text;
string[] c = mont.Split('-');
//string[] b = mont.Split('-');
int shijiyuefen = Convert.ToInt32(c[1]) - 3;
string mont1 = "";
if (shijiyuefen < 10)
{
mont1 = "0" + shijiyuefen.ToString();
}
else
{
mont1 = shijiyuefen.ToString();
}
string lszhuanyong = "select isnull(sum(cast((case when invlinshi.amount='' then '0' else isnull(invlinshi.amount,0) end)as float)),0) AS jine from Invoices_linshi invlinshi left join [Invoices] inv on invlinshi.customer_id = inv.customer_id where (left(invlinshi.creat_date,7) ='"+zhe+"') and (inv.statement ='3' and invlinshi.statement_ci='1') and invlinshi.fp_type=1";

object oblszhuanyong = DbHelper.DAL.ExecuteScalar(lszhuanyong);
string lsputong = "select isnull(sum(cast((case when invlinshi.amount='' then '0' else isnull(invlinshi.amount,0) end)as float)),0) AS jine from Invoices_linshi invlinshi left join [Invoices] inv on invlinshi.customer_id = inv.customer_id where (left(invlinshi.creat_date,7) ='"+zhe+"') and (inv.statement ='3' and invlinshi.statement_ci='1') and invlinshi.fp_type=2";

object oblsputong = DbHelper.DAL.ExecuteScalar(lsputong);
TextBox1.Text = Convert.ToDecimal((pujin + zzsjin) + Convert.ToDouble(oblsputong) + Convert.ToDouble(oblszhuanyong)).ToString("N");
TextBox2.Text = Convert.ToDecimal(pujin + Convert.ToDouble(oblsputong)).ToString("N");
TextBox4.Text = Convert.ToDecimal(zzsjin + Convert.ToDouble(oblszhuanyong)).ToString("N");
TextBox6.Text = Convert.ToDecimal(gdyjjin).ToString("N");
TextBox7.Text = Convert.ToDecimal((pujin + zzsjin) + Convert.ToDouble(oblsputong) + Convert.ToDouble(oblszhuanyong) - gdyjjin).ToString("N");
TextBox9.Text = zzs.ToString() + "%";
TextBox10.Text = pt.ToString() + "%";
string zongji = "insert into ins_fapiaotongji values('" + TextBox1.Text + "','" + TextBox2.Text + "','" + TextBox4.Text + "','" + TextBox5.Text + "','" + TextBox9.Text + "','" + TextBox10.Text + "','" + TextBox6.Text + "','" + TextBox7.Text + "','" + zhe + "')";
DbHelper.DAL.ExecuteNonQuery(zongji);

foreach (C1GridViewRow gvr in C1GridView2.Rows)
{
string m = textbox_date.Text;
InsusJsUtility jis = new InsusJsUtility();
//string yea = "2017-08-11";
string yea = DateTime.Now.ToString("yyyy-MM-dd");
string mm = this.textbox_date.Text;
//string mm = "2017-08";
Button button = (Button)sender;
string company = C1GridView2.Rows[gvr.RowIndex].Cells[2].Text.Trim();
string statement = C1GridView2.Rows[gvr.RowIndex].Cells[3].Text.Trim();
string fp_type = C1GridView2.Rows[gvr.RowIndex].Cells[4].Text.Trim();
string total = C1GridView2.Rows[gvr.RowIndex].Cells[7].Text.Trim();
string out_totalmoney = C1GridView2.Rows[gvr.RowIndex].Cells[8].Text.Trim();
string moth = C1GridView2.Rows[gvr.RowIndex].Cells[10].Text.Trim().ToString();
string yikai = C1GridView2.Rows[gvr.RowIndex].Cells[11].Text.Trim();
TextBox jine = (TextBox)C1GridView2.Rows[gvr.RowIndex].Cells[13].FindControl("TextBox8");
decimal d = Convert.ToDecimal(jine.Text.Trim());
string mon = d.ToString();
//string mon = jine.Text.Trim().ToString();
string customer_id = C1GridView2.Rows[gvr.RowIndex].Cells[14].Text.Trim();
string identity_number = C1GridView2.Rows[gvr.RowIndex].Cells[15].Text.Trim();
string state = C1GridView2.Rows[gvr.RowIndex].Cells[16].Text.Trim();
string statement_ci = C1GridView2.Rows[gvr.RowIndex].Cells[17].Text.Trim();
string address = C1GridView2.Rows[gvr.RowIndex].Cells[18].Text.Trim();
string telephone = C1GridView2.Rows[gvr.RowIndex].Cells[19].Text.Trim();
string fullname = C1GridView2.Rows[gvr.RowIndex].Cells[20].Text.Trim();
string account_bank = C1GridView2.Rows[gvr.RowIndex].Cells[21].Text.Trim();
string account_number = C1GridView2.Rows[gvr.RowIndex].Cells[22].Text.Trim();
int invid = Convert.ToInt32(C1GridView2.Rows[gvr.RowIndex].Cells[28].Text.Trim());
int fengzFlag = Convert.ToInt32(C1GridView2.Rows[gvr.RowIndex].Cells[26].Text.Trim());
if (statement == "固定")
{
string stryikai = "select * from InvoiceSure where invid in(select id from Invoices_linshi where duicuo=0) and left(dateStr,7) ='" + this.textbox_date.Text + "' and customer_id='" + customer_id + "' and statement = '" + statement + "'";
DataTable dyikai = DbHelper.DAL.DateSet(stryikai).Tables[0];
if (dyikai.Rows.Count > 0)
{

}
else
{
string str = "insert into [InvoiceSure] values(0," + invid + ",'" + customer_id + "','" + mon + "',null,'" + yea + "','" + state + "','" + account_bank + "','" + account_number + "','" + statement + "','" + statement_ci + "','" + identity_number + "','" + address + "','" + telephone + "','" + fp_type + "','" + mon + "','" + fullname + "','" + company + "','封账',1," + this.UserId.ToString() + ")";
//string str = "insert into [InvoiceSure] values(0," + invid + "," + customer_id + ",'" + mon + "',null,'" + yea + "','" + state + "','" + account_bank + "','" + account_number + "','" + statement + "','" + statement_ci + "','" + identity_number + "','" + address + "','" + telephone + "','" + fp_type + "','" + out_totalmoney + "','" + fullname + "','" + company + "','封账',1)";
double benyue = Convert.ToDouble(moth);
double yik = Convert.ToDouble(yikai);
double cha = benyue - yik;
//string up = "update [Invoices] set fengzFlag = 1 where customer_id =" + customer_id + " and id =" + invid + "";
string up = "update [Invoices_linshi] set fengzFlag = 1 where customer_id ='" + customer_id + "' and id =" + invid + "";
DbHelper.DAL.ExecuteNonQuery(up);
int count = DbHelper.DAL.ExecuteNonQuery(str);
Button b1 = (Button)C1GridView2.Rows[gvr.RowIndex].Cells[23].FindControl("button3");
b1.Enabled = false;
b1.Text = "已处理";
TextBox read = (TextBox)C1GridView2.Rows[gvr.RowIndex].Cells[13].FindControl("TextBox3");
read.ReadOnly = true;
}
}

}
string fapiao = "";
if (fapiao == "增值税专用")
{
fapiao = "0";
}
else
{
fapiao = "1";
}
string guding = "";
if (guding == "是")
{
guding = "1";
}
else
{
guding = "0";
}
string erp = "select distinct c.fullName,invlinshi.fengzFlag,inv.customer_id,inv.company,invlinshi.id,0 as amount,inv.account_bank,inv.account_number,inv.address,inv.identity_number,inv.telephone,inv.address,invlinshi.fp_type,invlinshi.statement_ci,invlinshi.state,inv.statement,byyk.benyueyikai,syyk.sanyueyikai as sanyueyikai from Invoices inv left join Invoices_linshi invlinshi on invlinshi.customer_id = inv.customer_id left join " + gjpgjp + "btype c on c.typeid=inv.customer_id left join (select sum(cast((case when InvoicesMoney='' then '0' else isnull(InvoicesMoney,0) end)as decimal)) as benyueyikai,customer_id from [InvoiceSure] where invid in(select id from Invoices_linshi where duicuo=0) and left([dateStr],10) between '" + nian + "-" + shanghe + "-26' and '" + zhe + "-25' group by customer_id) as byyk on byyk.customer_id =inv.customer_id left join (select sum(cast((case when InvoicesMoney='' then '0' else isnull(InvoicesMoney,0) end)as decimal)) as sanyueyikai,customer_id from [InvoiceSure] where invid in(select id from Invoices_linshi where duicuo=0) and left([dateStr],10) between '" + nian + "-" + sangeyue + "-26' and '" + nian + "-" + shanghe + "-25' group by customer_id) syyk on syyk.customer_id=inv.customer_id where (left(invlinshi.creat_date,7) ='"+zhe+"' or inv.creat_date ='2000-01-01') and (( inv.statement ='1' and invlinshi.statement_ci='') or (inv.statement ='2' and invlinshi.statement_ci='1') or (inv.statement ='2' and invlinshi.statement_ci='2') or (inv.statement ='1' and invlinshi.statement_ci='1') or (inv.statement ='3' and invlinshi.statement_ci='2')) order by inv.statement asc";
DataTable derp = DbHelper.DAL.DateSet(erp).Tables[0];
derp.Columns.Add("本月金额", typeof(string));
derp.Columns.Add("三月金额", typeof(string));
for (int i = 0; i < derp.Rows.Count; i++)
{
if (Convert.IsDBNull(derp.Rows[i]["benyueyikai"]) || derp.Rows[i]["benyueyikai"] == null || derp.Rows[i]["benyueyikai"].ToString() == "")
{
derp.Rows[i]["benyueyikai"] = "0";
}
if (Convert.IsDBNull(derp.Rows[0]["sanyueyikai"]) || derp.Rows[0]["sanyueyikai"] == null || derp.Rows[0]["sanyueyikai"].ToString() == "")
{
derp.Rows[i]["sanyueyikai"] = "0";
}
if (Convert.IsDBNull(derp.Rows[i]["amount"]) || derp.Rows[i]["amount"] == null || derp.Rows[i]["amount"].ToString() == "")
{
derp.Rows[i]["amount"] = "0";
}
string name = derp.Rows[i][0].ToString();
string gjp = "select * from (SELECT isnull(sum(cast(S.total as decimal)),0) AS 本月金额,BT.FullName FROM dbo.ASum_SaleBill AS S LEFT OUTER JOIN dbo.ASum_BillIndex AS BI ON S.BillNumberId = BI.BillNumberId LEFT OUTER JOIN dbo.btype AS BT ON BI.btypeid = BT.typeId where BT.FullName = '" + name + "' and left(CAST(S.OutFactoryDate AS DATE),7) = '" + zhe + "' group by BT.FullName ) as benyueshiji left join (SELECT isnull(sum(cast(S.total as decimal)),0) AS 三月金额,BT.FullName FROM dbo.ASum_SaleBill AS S LEFT OUTER JOIN dbo.ASum_BillIndex AS BI ON S.BillNumberId = BI.BillNumberId LEFT OUTER JOIN dbo.btype AS BT ON BI.btypeid = BT.typeId where BT.FullName = '" + name + "' and CAST(S.OutFactoryDate AS DATE) between '" + nian + "-" + sangeyue + "-26' and '" + nian + "-" + shanghe + "-25' group by BT.FullName) as sanyueshiji on benyueshiji.FullName=sanyueshiji.FullName";
DataTable dgjp = GJPDbHelper.DAL.DateSet(gjp).Tables[0];
if (dgjp.Rows.Count == 1)
{

derp.Rows[i]["本月金额"] = Convert.ToString(dgjp.Rows[0]["本月金额"]);
derp.Rows[i]["三月金额"] = Convert.ToString(dgjp.Rows[0]["三月金额"]);
}
else
{

derp.Rows[i]["本月金额"] = "0";
derp.Rows[i]["三月金额"] = "0";
}

string sumamountStr = "select sum(cast((case when invlinshi.amount='' then '0' else isnull(invlinshi.amount,0) end)as float)) from Invoices_linshi invlinshi left join Invoices inv on invlinshi.customer_id = inv.customer_id left join " + gjpgjp + "btype c on c.typeid=inv.customer_id left join (select sum(cast((case when InvoicesMoney='' then '0' else isnull(InvoicesMoney,0) end)as decimal)) as benyueyikai,customer_id from [InvoiceSure] where invid in(select id from Invoices_linshi where duicuo=0) and left([dateStr],7) = '" + zhe + "' group by customer_id) as byyk on byyk.customer_id =inv.customer_id left join (select sum(cast((case when InvoicesMoney='' then '0' else isnull(InvoicesMoney,0) end)as decimal)) as sanyueyikai,customer_id from [InvoiceSure] where invid in(select id from Invoices_linshi where duicuo=0) and left([dateStr],10) between '" + nian + "-" + sangeyue + "-26' and '" + nian + "-" + shanghe + "-25' group by customer_id) syyk on syyk.customer_id=inv.customer_id where (left(invlinshi.creat_date,7) = '" + zhe + "' or invlinshi.creat_date ='2000-01-01') and (( inv.statement ='1' and invlinshi.statement_ci='') or (inv.statement ='2' and invlinshi.statement_ci='1') or (inv.statement ='2' and invlinshi.statement_ci='2') or (inv.statement ='1' and invlinshi.statement_ci='1') or (inv.statement ='3' and invlinshi.statement_ci='2')) and c.FullName='" + derp.Rows[i]["fullName"].ToString() + "'";

DataTable sumamountTable = DbHelper.DAL.DateSet(sumamountStr).Tables[0];
if (sumamountTable.Rows.Count == 1)
{
if (Convert.IsDBNull(sumamountTable.Rows[0][0]) || sumamountTable.Rows[0][0] == null || sumamountTable.Rows[0][0].ToString() == "")
{
derp.Rows[i]["amount"] = "0";
}
else
{

derp.Rows[i]["amount"] = Convert.ToInt32(Math.Round(Convert.ToDecimal(sumamountTable.Rows[0][0].ToString()), 0));
}
}
else
{
derp.Rows[i]["amount"] = "0";
}

}
this.C1GridView2.DataSource = DbHelper.DAL.ReplaceFullName(derp, "fullName");
this.C1GridView2.DataBind();
DbHelper.DAL.addOperation_Log(this.UserId.ToString(), "发票月度封账", "新增", "增加封账信息：进行了批量封账操作");
Response.Write("<script>alert('已封账')</script>");
btnpanduan.Enabled = false;
btntijiao.Enabled = false;

}
else
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "aa", "alert('没有权限！！！');", true);
}
}
catch (Exception ex)
{

}
}
protected void btnjiancha_Click(object sender, EventArgs e)
{

}
protected void btnpanduan_Click(object sender, EventArgs e)
{
try
{
string aaa = DbHelper.DAL.ExecuteScalar("select DisplayName from Users where UserID=" + this.UserId.ToString()).ToString();
if (aaa.Trim() == "杜总")
{

double alljine = 0;// 发票总额
double pujin = 0;//普通发票金额
double zzsjin = 0;//增值税发票金额
double fplxbfb = 0;//发票类型百分比
double gdyjjin = 0;//固定月结金额
double cjjin = 0;//次结金额
int a = 0;
int b = 0;
int zongshu = C1GridView2.Rows.Count;
foreach (C1GridViewRow gvr in C1GridView2.Rows)
{
string statement = C1GridView2.Rows[gvr.RowIndex].Cells[3].Text.Trim();
string fp_type = C1GridView2.Rows[gvr.RowIndex].Cells[4].Text.Trim();
if (fp_type == "增值税专用")
{
TextBox jine1 = (TextBox)C1GridView2.Rows[gvr.RowIndex].Cells[13].FindControl("TextBox8");//本次发票金额
if (jine1.Text == "")
{
jine1.Text = "0";
}
zzsjin += Convert.ToDouble(jine1.Text.Trim());
a++;
}
else
{
TextBox jine2 = (TextBox)C1GridView2.Rows[gvr.RowIndex].Cells[13].FindControl("TextBox8");//本次发票金额
if (jine2.Text == "")
{
jine2.Text = "0";
}
pujin += Convert.ToDouble(jine2.Text.Trim());
b++;
}
if (statement == "固定")
{
TextBox jine3 = (TextBox)C1GridView2.Rows[gvr.RowIndex].Cells[13].FindControl("TextBox8");//本次发票金额
if (jine3.Text == "")
{
jine3.Text = "0";
}
gdyjjin += Convert.ToDouble(jine3.Text.Trim());
}
else
{
TextBox jine4 = (TextBox)C1GridView2.Rows[gvr.RowIndex].Cells[13].FindControl("TextBox8");//本次发票金额
if (jine4.Text == "")
{
jine4.Text = "0";
}
cjjin += Convert.ToDouble(jine4.Text.Trim());
}
string mm = this.textbox_date.Text;
//string mm = "2017-08";
string moth = C1GridView2.Rows[gvr.RowIndex].Cells[10].Text.Trim();//本月实际金额
string yikai = C1GridView2.Rows[gvr.RowIndex].Cells[11].Text.Trim();//已开发票金额
TextBox jine = (TextBox)C1GridView2.Rows[gvr.RowIndex].Cells[13].FindControl("TextBox8");//本次发票金额
alljine += Convert.ToDouble(jine.Text.Trim());
string customer_id = C1GridView2.Rows[gvr.RowIndex].Cells[14].Text.Trim();
int invid = Convert.ToInt32(C1GridView2.Rows[gvr.RowIndex].Cells[28].Text.Trim());
string mon = jine.Text.Trim();
double benyue = Convert.ToDouble(moth);
double yik = Convert.ToDouble(yikai);
double cha = benyue - yik;
}
//string zhe = "2017-08";
string zhe = this.textbox_date.Text;//年月
//string nian = zhe[0].ToString();//年
string[] zhehe = zhe.Split('-');
string nian = zhehe[0].ToString();
string shanghe;
string zhe1;
string sangeyue = "";
if (Convert.ToInt32(zhehe[1]) == 1)
{
nian = (Convert.ToInt32(nian) - 1).ToString();//去年
shanghe = "12";
sangeyue = (Convert.ToInt32(shanghe) - 3).ToString();
}
else
{
if (Convert.ToInt32(zhehe[1]) > 10)
{
shanghe = (Convert.ToInt32(zhehe[1]) - 1).ToString();
}
else
{
shanghe = "0" + (Convert.ToInt32(zhehe[1]) - 1).ToString();
}
}
if (Convert.ToInt32(zhehe[1]) == 1)
{
sangeyue = "09";
}
if (Convert.ToInt32(zhehe[1]) == 2)
{
sangeyue = "10";
}
if (Convert.ToInt32(zhehe[1]) == 3)
{
sangeyue = "11";
}
if (Convert.ToInt32(zhehe[1]) == 4)
{
sangeyue = "12";
}
if (Convert.ToInt32(zhehe[1]) >= 5)
{
sangeyue = "0" + (Convert.ToInt32(zhehe[1]) - 4).ToString();
}

string zjine = "select sum(cast((case when amount='' then '0' else isnull(amount,0) end)as decimal)) amount from Invoices_linshi WHERE [creat_date] between '" + nian + "-" + shanghe + "-26' and '" + zhe + "-25'";

object obzjine = DbHelper.DAL.ExecuteScalar(zjine);
alljine += Convert.ToDouble(obzjine);
cjjin = Convert.ToDouble(obzjine);

string zzszy = "select isnull(count(*),0) c from Invoices inv left join Invoices_linshi invlinshi on invlinshi.customer_id = inv.customer_id WHERE (left(invlinshi.creat_date,7) ='"+zhe+"' or inv.creat_date ='2000-01-01') and invlinshi.fp_type='1' and ((inv.statement ='1' and invlinshi.statement_ci='') or (inv.statement ='3' and invlinshi.statement_ci='1') or (inv.statement ='2' and invlinshi.statement_ci='1') or (inv.statement ='3' and invlinshi.statement_ci='2'))";

//int zzszycount = DbHelper.DAL.ExecuteNonQuery(zjine);
object o1 = DbHelper.DAL.ExecuteScalar(zzszy);
int zzszycount = Convert.ToInt32(o1);

string ptpt = "select isnull(count(*),0) c from Invoices inv left join Invoices_linshi invlinshi on invlinshi.customer_id = inv.customer_id WHERE (left(invlinshi.creat_date,7) ='"+zhe+"' or invlinshi.creat_date ='2000-01-01') and invlinshi.fp_type='2' and ((inv.statement ='1' and invlinshi.statement_ci='') or (inv.statement ='3' and invlinshi.statement_ci='1') or (inv.statement ='2' and invlinshi.statement_ci='1') or (inv.statement ='3' and invlinshi.statement_ci='2'))";

//int ptcount = DbHelper.DAL.ExecuteNonQuery(ptpt);
object o12 = DbHelper.DAL.ExecuteScalar(ptpt);
int ptcount = Convert.ToInt32(o12);
double zzs = Math.Round((double)zzszycount / (Convert.ToDouble(zzszycount) + Convert.ToDouble(ptcount)), 3) * 100;
double pt = 100 - zzs;
TextBox5.Text = zzs.ToString() + "%" + "/" + pt.ToString() + "%";
string year = DateTime.Now.ToString("yyyy");
//string mont = "2017-08";
string mont = this.textbox_date.Text;
string[] c = mont.Split('-');
//string[] b = mont.Split('-');
int shijiyuefen = Convert.ToInt32(c[1]) - 3;
string mont1 = "";
if (shijiyuefen < 10)
{
mont1 = "0" + shijiyuefen.ToString();
}
else
{
mont1 = shijiyuefen.ToString();
}

string erp = "select distinct c.fullName,invlinshi.fengzFlag,inv.customer_id,inv.company,invlinshi.id,0 as amount,inv.account_bank,inv.account_number,inv.address,inv.identity_number,inv.telephone,inv.address,invlinshi.fp_type,invlinshi.statement_ci,invlinshi.state,inv.statement,byyk.benyueyikai,syyk.sanyueyikai as sanyueyikai from Invoices inv left join Invoices_linshi invlinshi on invlinshi.customer_id = inv.customer_id left join " + gjpgjp + "btype c on c.typeid=inv.customer_id left join (select sum(cast((case when InvoicesMoney='' then '0' else isnull(InvoicesMoney,0) end)as decimal)) as benyueyikai,customer_id from [InvoiceSure] where invid in(select id from Invoices_linshi where duicuo=0) and left([dateStr],10) between '" + nian + "-" + shanghe + "-26' and '" + zhe + "-25' group by customer_id) as byyk on byyk.customer_id =inv.customer_id left join (select sum(cast((case when InvoicesMoney='' then '0' else isnull(InvoicesMoney,0) end)as decimal)) as sanyueyikai,customer_id from [InvoiceSure] where invid in(select id from Invoices_linshi where duicuo=0) and left([dateStr],10) between '" + nian + "-" + sangeyue + "-26' and '" + nian + "-" + shanghe + "-25' group by customer_id) syyk on syyk.customer_id=inv.customer_id where (left(invlinshi.creat_date,7) ='"+zhe+"' or invlinshi.creat_date ='2000-01-01') and (( inv.statement ='1' and invlinshi.statement_ci='') or (inv.statement ='2' and invlinshi.statement_ci='1') or (inv.statement ='2' and invlinshi.statement_ci='2') or (inv.statement ='1' and invlinshi.statement_ci='1') or (inv.statement ='3' and invlinshi.statement_ci='2')) order by inv.statement asc";
DataTable derp = DbHelper.DAL.DateSet(erp).Tables[0];
derp.Columns.Add("本月金额", typeof(string));
derp.Columns.Add("三月金额", typeof(string));
for (int i = 0; i < derp.Rows.Count; i++)
{
if (Convert.IsDBNull(derp.Rows[i]["benyueyikai"]) || derp.Rows[i]["benyueyikai"] == null || derp.Rows[i]["benyueyikai"].ToString() == "")
{
derp.Rows[i]["benyueyikai"] = "0";
}
if (Convert.IsDBNull(derp.Rows[0]["sanyueyikai"]) || derp.Rows[0]["sanyueyikai"] == null || derp.Rows[0]["sanyueyikai"].ToString() == "")
{
derp.Rows[i]["sanyueyikai"] = "0";
}
if (Convert.IsDBNull(derp.Rows[i]["amount"]) || derp.Rows[i]["amount"] == null || derp.Rows[i]["amount"].ToString() == "")
{
derp.Rows[i]["amount"] = "0";
}
string name = derp.Rows[i][0].ToString();
string gjp = "select * from (SELECT isnull(sum(cast(S.total as decimal)),0) AS 本月金额,BT.FullName FROM dbo.ASum_SaleBill AS S LEFT OUTER JOIN dbo.ASum_BillIndex AS BI ON S.BillNumberId = BI.BillNumberId LEFT OUTER JOIN dbo.btype AS BT ON BI.btypeid = BT.typeId where BT.FullName = '" + name + "' and left(CAST(S.OutFactoryDate AS DATE),7) = '" + zhe + "' group by BT.FullName ) as benyueshiji left join (SELECT isnull(sum(cast(S.total as decimal)),0) AS 三月金额,BT.FullName FROM dbo.ASum_SaleBill AS S LEFT OUTER JOIN dbo.ASum_BillIndex AS BI ON S.BillNumberId = BI.BillNumberId LEFT OUTER JOIN dbo.btype AS BT ON BI.btypeid = BT.typeId where BT.FullName = '" + name + "' and CAST(S.OutFactoryDate AS DATE) between '" + nian + "-" + sangeyue + "-26' and '" + nian + "-" + shanghe + "-25' group by BT.FullName) as sanyueshiji on benyueshiji.FullName=sanyueshiji.FullName";
DataTable dgjp = GJPDbHelper.DAL.DateSet(gjp).Tables[0];
if (dgjp.Rows.Count == 1)
{

derp.Rows[i]["本月金额"] = Convert.ToString(dgjp.Rows[0]["本月金额"]);
derp.Rows[i]["三月金额"] = Convert.ToString(dgjp.Rows[0]["三月金额"]);
}
else
{
derp.Rows[i]["本月金额"] = "0";
derp.Rows[i]["三月金额"] = "0";
}

string sumamountStr = "select sum(cast((case when invlinshi.amount='' then '0' else isnull(invlinshi.amount,0) end)as float)) from Invoices_linshi invlinshi left join Invoices inv on invlinshi.customer_id = inv.customer_id left join " + gjpgjp + "btype c on c.typeid=inv.customer_id left join (select sum(cast((case when InvoicesMoney='' then '0' else isnull(InvoicesMoney,0) end)as decimal)) as benyueyikai,customer_id from [InvoiceSure] where invid in(select id from Invoices_linshi where duicuo=0) and left([dateStr],7) = '" + zhe + "' group by customer_id) as byyk on byyk.customer_id =inv.customer_id left join (select sum(cast((case when InvoicesMoney='' then '0' else isnull(InvoicesMoney,0) end)as decimal)) as sanyueyikai,customer_id from [InvoiceSure] where invid in(select id from Invoices_linshi where duicuo=0) and left([dateStr],10) between '" + nian + "-" + sangeyue + "-26' and '" + nian + "-" + shanghe + "-25' group by customer_id) syyk on syyk.customer_id=inv.customer_id where (left(invlinshi.creat_date,7) = '" + zhe + "' or invlinshi.creat_date ='2000-01-01') and (( inv.statement ='1' and invlinshi.statement_ci='') or (inv.statement ='2' and invlinshi.statement_ci='1') or (inv.statement ='2' and invlinshi.statement_ci='2') or (inv.statement ='1' and invlinshi.statement_ci='1') or (inv.statement ='3' and invlinshi.statement_ci='2')) and c.FullName='" + derp.Rows[i]["fullName"].ToString() + "'";
DataTable sumamountTable = DbHelper.DAL.DateSet(sumamountStr).Tables[0];
if (sumamountTable.Rows.Count == 1)
{
if (Convert.IsDBNull(sumamountTable.Rows[0][0]) || sumamountTable.Rows[0][0] == null || sumamountTable.Rows[0][0].ToString() == "")
{
derp.Rows[i]["amount"] = "0";
}
else
{

derp.Rows[i]["amount"] = Convert.ToInt32(Math.Round(Convert.ToDecimal(sumamountTable.Rows[0][0].ToString()), 0));
}
}
else
{
derp.Rows[i]["amount"] = "0";
}

}
this.C1GridView2.DataSource = DbHelper.DAL.ReplaceFullName(derp, "fullName");
this.C1GridView2.DataBind();

string lszhuanyong = "select isnull(sum(cast((case when invlinshi.amount='' then '0' else isnull(invlinshi.amount,0) end)as decimal)),0) AS jine from Invoices_linshi invlinshi left join [Invoices] inv on invlinshi.customer_id = inv.customer_id where (left(invlinshi.creat_date,7) ='"+zhe+"') and (inv.statement ='3' and invlinshi.statement_ci='1') and invlinshi.fp_type=1";
object oblszhuanyong = DbHelper.DAL.ExecuteScalar(lszhuanyong);

string lsputong = "select isnull(sum(cast((case when invlinshi.amount='' then '0' else isnull(invlinshi.amount,0) end)as decimal)),0) AS jine from Invoices_linshi invlinshi left join [Invoices] inv on invlinshi.customer_id = inv.customer_id where (left(invlinshi.creat_date,7) ='"+zhe+"') and (inv.statement ='3' and invlinshi.statement_ci='1') and invlinshi.fp_type=2";

object oblsputong = DbHelper.DAL.ExecuteScalar(lsputong);
TextBox1.Text = Convert.ToDecimal((pujin + zzsjin) + Convert.ToDouble(oblsputong) + Convert.ToDouble(oblszhuanyong)).ToString("N");
TextBox2.Text = Convert.ToDecimal(pujin + Convert.ToDouble(oblsputong)).ToString("N");
TextBox4.Text = Convert.ToDecimal(zzsjin + Convert.ToDouble(oblszhuanyong)).ToString("N");
TextBox6.Text = Convert.ToDecimal(gdyjjin).ToString("N");
TextBox7.Text = Convert.ToDecimal((pujin + zzsjin) + Convert.ToDouble(oblsputong) + Convert.ToDouble(oblszhuanyong) - gdyjjin).ToString("N");
TextBox9.Text = zzs.ToString() + "%";
TextBox10.Text = pt.ToString() + "%";
}
else
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "aa", "alert('没有权限！！！');", true);
}
}
catch (Exception ex)
{

}
}
protected void btnzhangshu_Click(object sender, EventArgs e)
{

}
public void changzong()
{
string aaa = DbHelper.DAL.ExecuteScalar("select DisplayName from Users where UserID=" + this.UserId.ToString()).ToString();
if (aaa.Trim() == "杜总")
{
btnpanduan.Enabled = true;
btntijiao.Enabled = true;
}
}
}
}