using System; using System.Collections.Generic; using System.Linq; using System.Web.Mvc; using DSWeb.Areas.OA.DAL.Baoxiao; using DSWeb.Areas.OA.Models.Baoxiao; using DSWeb.Areas.OA.Models.Comm; using DSWeb.TruckMng.Comm.Cookie; using DSWeb.TruckMng.Helper; using DSWeb.TruckMng.Helper.Repository; using DSWeb.Areas.CommMng.DAL; using HcUtility.Comm; using HcUtility.Core; using Microsoft.Practices.EnterpriseLibrary.Data; using System.Text; using System.Data; using DSWeb.Areas.Import.Models.CwVouitems; using DSWeb.Areas.Import.Models.CwDesign; using DSWeb.Areas.Import.DAL.CwVouchersDAL; using DSWeb.Areas.Import.Models.CwVouchers; using DSWeb.EntityDA; using WebSqlHelper; using DSWeb.SoftMng.Filter; namespace DSWeb.Areas.TruckMng.Controllers { [JsonRequestBehavior] public class BaoxiaoController : Controller { // // GET: /Import/XXH public ActionResult Index() { return View(); } // // GET: /Import/XXH/Edit public ActionResult Edit() { return View(); } public ContentResult CreateVoucher ( string VoucherDate, string UsdExrate, string User, string useRate, string feesql, string DEPTGID, string zhaiyao ) { var modb = new ModelObjectDB(); DBResult result = CreateDCVoucher(VoucherDate, UsdExrate, User, useRate, feesql, DEPTGID, zhaiyao); var jsonRespose = new JsonResponse { Success = result.Success, Message = result.Message }; return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) }; } public DBResult CreateDCVoucher ( string VoucherDate, string UsdExrate, string User, string useRate, string feesql, string DEPTGID, string zhaiyao ) { var result = new DBResult(); result.Success = true; var UserID = Convert.ToString(CookieConfig.GetCookie_UserId(Request)); Database db_t = DatabaseFactory.CreateDatabase(); string strCheckRow = " select USERNAME from [user] where gid= '" + UserID + "' "; object statusObj = SqlHelper.ExecuteScalar(db_t.ConnectionString, CommandType.Text, strCheckRow, null); var EnName = statusObj.ToString(); var VouitemsList = new List(); var VouOrdNo = Guid.NewGuid().ToString(); CwDesign CWDesign = CwVouchersDAL.GetCwDesign(); //var _DEPTNO = ""; if (CWDesign.AR == "" || CWDesign.AR == null) { result.Success = false; result.Message = "请先设置帐套信息,才能生成凭证！"; } else { decimal DebitAmt = Convert.ToDecimal(0.00); decimal CreditAmt = Convert.ToDecimal(0.00); int itemsno = 1; CwVouchers Voucher = new CwVouchers(); Database db = DatabaseFactory.CreateDatabase(); //借方数据 成本 进项税 销项税 using (var conn = db.CreateConnection()) { conn.Open(); var strSql = new StringBuilder(); if (useRate == "1") {/* ");*/ strSql.Append(" select vs.mblno,_t2.bsno,_t2.CUSTOMERNAME,_t2.jf,_t2.REASON,_t2.FEENAME,_t2.kemu,_t2.CHEQUENUM,_t2.BlockName "); strSql.Append(" ,(select value from info_client_company where CompanyID=(select top 1 companyid from vw_user where deptgid='" + DEPTGID + "') "); strSql.Append(" and customergid=(select gid from info_client where SHORTNAME=_t2.CUSTOMERNAME and isstop=0) and fieldname='CWDM') CustomerDM"); strSql.Append(" ,case reason when '费用' then feename "); if (DEPTGID == "959EA28A-F5BE-8126-2E9D-CAE6C5BAD033") {//是天津分集装箱部 strSql.Append(" else '业务编号：'+ isnull(vs.CUSTNO,'')+'/'+feename end as ZHAIYAO, "); } else { strSql.Append(" else '提单号：'+ isnull(vs.mblno,'')+'/'+feename end as ZHAIYAO, "); } strSql.Append(" isnull(OP.PROJECTNO,0) PROJECTNO, ic.FARCODE, "); strSql.Append(" (select deptno from sys_dept where GID='" + DEPTGID + "') deptno "); strSql.Append(" FROM (select _t.bsno,_t.customername, jf,reason,feename, "); strSql.Append(" case reason when '成本' then '520101'");//(select FINANCESOFTCODE from sys_dept where GID='"+DEPTGID+"') "); strSql.Append(" when '垫付' then case '" + DEPTGID + "' when 'FCD03297-2B64-5B9E-C9D5-2D6B475AE932' then '12210602' else '12210601' end "); strSql.Append(" when '押箱' then '12210501' when '费用' then dbo.getfeegroup(feename) "); //退押箱 应该怎么处理？？邓羽20150326 strSql.Append(" when '税' then case '" + DEPTGID + "' when 'FCD03297-2B64-5B9E-C9D5-2D6B475AE932' then '2221010102' else '2221010101' end end as kemu "); strSql.Append(" ,CHEQUENUM, case reason when '成本' then (select FINANCESOFTCODE from sys_dept where GID='" + DEPTGID + "')"); strSql.Append(" when '垫付' then '' when '押箱' then '' when '费用' then "); strSql.Append(" case feename when '餐费' then '02:业务板块' when '业务招待费' then '02:业务板块' else '109:中储业务板块' end "); strSql.Append(" end as BlockName "); strSql.Append(" FROM (select bsno,customername,notaxamount jf,reason,feename,CHEQUENUM from ch_fee where bxgid IN " + feesql); strSql.Append(" and ISADVANCEDPAY=0 "); strSql.Append(" union all select bsno,customername,amount jf,reason,feename,CHEQUENUM from ch_fee where bxgid IN " + feesql); strSql.Append(" and ISADVANCEDPAY=1 "); strSql.Append(" union all select bsno,customername,tax as jf,'税','进项税额',CHEQUENUM from ch_fee where bxgid IN " + feesql); strSql.Append(" and ISADVANCEDPAY=0 "); strSql.Append(" and tax<>0 )_t )_t2 "); strSql.Append(" left join vw_settlement vs on vs.bsno=_t2.bsno "); strSql.Append(" left join info_client ic on ic.shortname=_t2.customername and ic.isstop=0 "); strSql.Append(" left join OA_ProjectInfo OP on OP.BLNO=vs.MBLNO order by kemu "); } using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { //var Accitems = CwVouchersDAL.GetCwAccitems(Convert.ToString(reader["FEENAME"])); var Accitems = CwVouchersDAL.GetCwAccitems(Convert.ToString(reader["kemu"])); if (Accitems.ACCID == "" || Accitems.ACCID == null) { result.Success = false; result.Message = "单号:" + Convert.ToString(reader["mblno"]) + " 没有对应的财务科目！"; } //if ( Convert.ToString(reader["mblno"])) CwVouitems data = new CwVouitems(); data.CORPID = Convert.ToString(reader["FARCODE"]); data.CUSTOMERNAME = Convert.ToString(reader["CustomerDM"]); data.BLNO = Convert.ToString(reader["CHEQUENUM"]);//此处改为放置支票号 var MBLNO = Convert.ToString(reader["MBLNO"]); if (MBLNO == "外代IC卡充值")//充值 { data.CUSTOMERNAME = "011200000057"; } if (MBLNO == "外运IC卡充值")//充值 { data.CUSTOMERNAME = "011200000060"; } if (MBLNO == "太平洋IC卡充值")//充值 { data.CUSTOMERNAME = "011200000021"; } if (Convert.ToString(reader["CustomerDM"]) == "" && !(MBLNO == "外代IC卡充值" || MBLNO == "外运IC卡充值" || MBLNO == "太平洋IC卡充值") && (Convert.ToString(reader["Reason"]) != "费用")) { result.Success = false; result.Message = "单号:" + Convert.ToString(reader["CUSTOMERNAME"]) + " 没有设置分公司财务代码！"; } data.ACCID = Accitems.ACCID; data.ACCNAME = Accitems.ACCNAME;//科目名称 data.ORDNO = VouOrdNo; data.EXPLAN = Convert.ToString(reader["zhaiyao"]);//摘要 data.ITEMNO = itemsno; data.ISSYS = true; //if (Accitems.DC == "借") //{ data.DC = "D"; //} //else if (Accitems.DC == "贷") //{ // data.DC = "C"; //} data.AMTDR = Convert.ToDecimal(reader["jf"]); data.AMTCR = Convert.ToDecimal(0.00); data.FCYCR = Convert.ToDecimal(0.00); data.FCYDR = Convert.ToDecimal(reader["jf"]); data.FCYEXRATE = Convert.ToDecimal(1); data.FCYNO = "RMB"; data.DEPTID = Convert.ToString(reader["DEPTNO"]);//部门编码 data.ITEMID = Convert.ToString(reader["PROJECTNO"]);//通过提单号查询出的项目编号 DebitAmt = DebitAmt + data.AMTDR; if (data.AMTDR != 0) { itemsno = itemsno + 1; VouitemsList.Add(data); } var buztype = Convert.ToString(reader["reason"]); var feename = Convert.ToString(reader["feename"]); if (feename == "进项税额") { data.FIELD2 = Convert.ToString(reader["DEPTNO"]) + ":部门";//部门编码 data.FIELD1 = "03:税率"; } else { switch (buztype) { case ("成本"): if (data.ITEMID == "" || data.ITEMID == "0") { result.Success = false; var mes = ""; if (Convert.ToString(reader["mblno"]) == "") { mes = "(空白编号)"; } else mes = Convert.ToString(reader["mblno"]); result.Message = "单号:" + mes + " 没有设置项目编号！"; } data.FIELD1 = Convert.ToString(reader["DEPTNO"]) + ":部门";//部门编码 //data.FIELD2 = Convert.ToString(reader["CUSTOMERNAME"]) + ":客商";//客商 data.FIELD2 = data.ITEMID + ":业务编号";//业务编号 data.FIELD3 = Convert.ToString(reader["BlockName"]) + ":中物储业务板块";//业务板块 break; case ("垫付"): if (data.ITEMID == "" || data.ITEMID == "0") { result.Success = false; var mes = ""; if (Convert.ToString(reader["mblno"]) == "") { mes = "(空白编号)"; } else mes = Convert.ToString(reader["mblno"]); result.Message = "单号:" + mes + " 没有设置项目编号！"; } data.FIELD1 = Convert.ToString(reader["DEPTNO"]) + ":部门";//部门编码 data.FIELD2 = data.CUSTOMERNAME + ":客商";//客商 data.FIELD3 = data.ITEMID + ":业务编号";//业务编号 break; case ("押箱"): if (data.ITEMID == "" || data.ITEMID == "0") { result.Success = false; var mes = ""; if (Convert.ToString(reader["mblno"]) == "") { mes = "(空白编号)"; } else mes = Convert.ToString(reader["mblno"]); result.Message = "单号:" + mes + " 没有设置项目编号！"; } data.FIELD1 = Convert.ToString(reader["DEPTNO"]) + ":部门";//部门编码 data.FIELD2 = data.CUSTOMERNAME + ":客商";//客商 data.FIELD3 = data.ITEMID + ":业务编号";//业务编号 break; case ("费用"): data.FIELD1 = Convert.ToString(reader["DEPTNO"]) + ":部门";//部门编码 data.FIELD3 = Convert.ToString(reader["BlockName"]) + ":中物储业务板块";//业务编号 break; default: break; } //case buztype when } } reader.Close(); } } //贷方 数据来自 OA_baoxiao_pay using (var conn = db.CreateConnection()) { conn.Open(); var strSql = new StringBuilder(); if (useRate == "1") { strSql.Append(" select obp.*,sb.subjectcode,sb.bankname,sb.codename,sb.ACCOUNT,(select deptno from sys_dept where GID='" + DEPTGID + "') deptno from OA_baoxiao_pay obp "); strSql.Append(" left join sys_bank sb on sb.FINANCESOFTCODE=obp.FINANCIALCODE "); strSql.Append(" where bxgid IN " + feesql + " "); } using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { var Accitems = CwVouchersDAL.GetCwAccitems(Convert.ToString(reader["subjectcode"])); var BANKNAME = Convert.ToString(reader["bankname"]); var CODENAME = Convert.ToString(reader["FINANCIALCODE"]);//obp.FINANCIALCODE sb.codename if (Accitems.ACCID == "" || Accitems.ACCID == null) { result.Success = false; result.Message = "贷方:" + BANKNAME + " 没有对应的财务科目！"; } CwVouitems data = new CwVouitems(); if (Accitems.ACCID == "12210501")//压箱费 { data.FIELD1 = Convert.ToString(reader["deptno"]) + ":部门"; data.FIELD2 = Convert.ToString(reader["ACCOUNT"]) + ":客商"; data.FIELD3 = Convert.ToString(reader["FINANCIALCODE"]) + ":业务编号"; } else if (Accitems.ACCID == "112101")//汇票 { //data.FIELD1 = _DEPTNO+":部门"; data.FIELD1 = Convert.ToString(reader["ACCOUNT"]) + ":客商"; //data.FIELD3 = Convert.ToString(reader["FINANCIALCODE"])+":业务编号"; } else { //data.FIELD1 = ""; data.FIELD1 = Convert.ToString(reader["FINANCIALCODE"]) + ":银行账户";//银行 data.CUSTOMERNAME = ""; } //支付结算没有客户 data.ACCID = Accitems.ACCID;//银行的科目编号 data.ACCNAME = Accitems.ACCNAME;//科目名称 data.ORDNO = VouOrdNo; data.BLNO = Convert.ToString(reader["TICKETNO"]);//票号 data.EXPLAN = zhaiyao;//摘要 data.ITEMNO = itemsno; data.ISSYS = true; //if (Accitems.DC == "借") //{ //data.DC = "D"; //} //else if (Accitems.DC == "贷") //{ data.DC = "C"; //} data.AMTDR = Convert.ToDecimal(0.00); data.AMTCR = Convert.ToDecimal(reader["AMOUNT"]); data.FCYCR = Convert.ToDecimal(reader["AMOUNT"]); data.FCYDR = Convert.ToDecimal(0.00); data.FCYEXRATE = Convert.ToDecimal(1); data.FCYNO = "RMB"; data.DEPTID = Convert.ToString(reader["deptno"]);//部门编码 //data.ITEMID = Convert.ToInt32(reader["PROJECTNO"]);//用提单号填充项目编码 data.FSETTLCODE = Convert.ToString(reader["SETTLEMENTTYPE"]); CreditAmt = CreditAmt + data.AMTCR; itemsno = itemsno + 1; VouitemsList.Add(data); } reader.Close(); } } //凭证主表信息 Voucher.ORDNO = VouOrdNo; var voudate = Convert.ToDateTime(VoucherDate); Voucher.ACCYEAR = voudate.ToString("yyyy"); Voucher.ACCMONTH = voudate.ToString("MM"); Voucher.PREPARED = EnName;//User;//制单人 Voucher.VOUDATE = voudate;//制单时间 Voucher.VKNO = CWDesign.TVW; Voucher.AMTDR = DebitAmt; Voucher.AMTCR = CreditAmt; //// var sSQL = "SELECT top 1 * from cw_design"; T_ALL_DA T_ALL_DA = new EntityDA.T_ALL_DA(); DataSet dsCwDesign = T_ALL_DA.GetAllSQL(sSQL); if (dsCwDesign == null) { result.Success = false; result.Message = "请先设置帐套！"; } //// Voucher.VOUNO = CwVouchersDAL.getVOUNO(dsCwDesign, Voucher.ACCYEAR, Voucher.ACCMONTH); Voucher.VOUALLNO = Voucher.ACCYEAR + Voucher.ACCMONTH + Voucher.VOUNO; Voucher.ATTACHS = CWDesign.AFFIXNO; Voucher.FCY = ""; Voucher.QTY = "";//是否含有数量科目="";//varchar="";//(1) Voucher.VOUPROP = "";//凭证属性="";//varchar="";//(1) Voucher.CHECKED = "";//审核人="";//varchar="";//(10) Voucher.ENTERED = "";//记账人="";//varchar="";//(10) Voucher.ERRMSG = "";//错误信息="";//varchar="";//(6) Voucher.ISDELETE = false;//是否废除="";//bit="";//Y Voucher.DELETEUSER = "";//废除操作人="";//Varchar(36)="";//Y var strSel = " AND BXGID in " + feesql; if (result.Success) { result = CwVouchersDAL.InsertVoucher(Voucher, VouitemsList, "OA_Baoxiao", Voucher.VOUALLNO, strSel, User); } } return result; } #region 参照部分 #endregion } }