//
// 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(" ", "") == "0") // { // e.Row.Cells[2].Text = "否"; // } // else if (e.Row.Cells[2].Text.Trim().Replace(" ", "") == "1") // { // e.Row.Cells[2].Text = "是"; // } // //发票类型 // if (e.Row.Cells[3].Text.Trim().Replace(" ", "") == "0") // { // e.Row.Cells[3].Text = "增值税发票"; // } // else if (e.Row.Cells[3].Text.Trim().Replace(" ", "") == "1") // { // e.Row.Cells[3].Text = "普通发票"; // } // if (e.Row.Cells[4].Text == " :") // { // e.Row.Cells[4].Text = "0.00"; // } // string a = e.Row.Cells[5].Text; // if (e.Row.Cells[5].Text == "\" \"") // { // e.Row.Cells[5].Text = "0"; // } // if (e.Row.Cells[6].Text == " :") // { // 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(" ", "") == "1") { if (e.Row.Cells[17].Text.Trim().Replace(" ", "") == "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(" ", "") == "2") { TextBox sj = (TextBox)e.Row.Cells[13].FindControl("TextBox8"); sj.ReadOnly = true; if (e.Row.Cells[17].Text.Trim().Replace(" ", "") == "2") { e.Row.Cells[3].Text = "临时"; } else { e.Row.Cells[3].Text = "实次"; } } else if (e.Row.Cells[3].Text.Trim().Replace(" ", "") == "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(" ", "") == "1") { e.Row.Cells[4].Text = "增值税专用"; } else if (e.Row.Cells[4].Text.Trim().Replace(" ", "") == "2") { e.Row.Cells[4].Text = "增值税普发"; } if (string.IsNullOrEmpty(e.Row.Cells[7].Text.Trim().Replace(" ", ""))) { e.Row.Cells[7].Text = "0.00"; } string a = e.Row.Cells[8].Text; if (string.IsNullOrEmpty(e.Row.Cells[8].Text.Trim().Replace(" ", ""))) { e.Row.Cells[8].Text = "0.00"; } if (string.IsNullOrEmpty(e.Row.Cells[10].Text.Trim().Replace(" ", ""))) { e.Row.Cells[10].Text = "0.00"; } if (string.IsNullOrEmpty(e.Row.Cells[11].Text.Trim().Replace(" ", ""))) { e.Row.Cells[11].Text = "0.00"; } Button zs = (Button)e.Row.Cells[5].FindControl("btnzhangshu"); if (string.IsNullOrEmpty(zs.Text.Trim().Replace(" ", ""))) { 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(" ", ""); 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(" ", "").ToString(); string bbb = jine2.Text.Trim().Replace(" ", "").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(" ", "") != "固定") { 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_idstring 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(" ",""); 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; } } }}