using System; using System.Data; using System.Collections.Generic; using System.Text; using DSWeb.Areas.Import.Models.Finance; using DSWeb.Areas.Import.Models.Comm; using Microsoft.Practices.EnterpriseLibrary.Data; using HcUtility.Comm; using WebSqlHelper; using System.Data.SqlClient; using DSWeb.EntityDA; namespace DSWeb.Areas.Import.DAL.Finance { public partial class FinanceDAL { #region 费用参数 private const string PARM_FEE_GID = "@gid"; private const string PARM_FEE_BSNO = "@bsno"; private const string PARM_FEE_TYPE = "@fee_type"; private const string PARM_FEE_NAME = "@fee_name"; private const string PARM_FEE_DESCRIPTION = "@fee_description"; private const string PARM_FEE_CUSTOMER_TYPE = "@customer_type"; private const string PARM_FEE_CUSTOMER_NAME = "@customer_name"; private const string PARM_FEE_UNIT = "@unit"; private const string PARM_FEE_UNIT_PRICE = "@unit_price"; private const string PARM_FEE_TAXUNIT_PRICE = "@taxunit_price"; private const string PARM_FEE_QUANTITY = "@quantity"; private const string PARM_FEE_AMOUNT = "@amount"; private const string PARM_FEE_CURRENCY = "@currency"; private const string PARM_FEE_EXCHANGE_RATE = "@exchange_rate"; private const string PARM_FEE_REASON = "@reason"; private const string PARM_FEE_REMARK = "@remark"; private const string PARM_FEE_COMMISSION_RATE = "@comm_rate"; private const string PARM_FEE_SETTLEMENT = "@settlement"; private const string PARM_FEE_INVOICE = "@invoice"; private const string PARM_FEE_ORDER_AMOUNT = "@order_amount"; private const string PARM_FEE_ORDER_INVOCE = "@order_invoice"; private const string PARM_FEE_SUBMIT_DATE = "@submit_date"; private const string PARM_FEE_AUDIT_OPERATOR = "@audit_operator"; private const string PARM_FEE_AUDIT_DATE = "@audit_date"; private const string PARM_FEE_ENTER_OPERATOR = "@enter_operator"; private const string PARM_FEE_ENTER_DATE = "@enter_date"; private const string PARM_FEE_DEBITNO = "@debitno"; private const string PARM_FEE_ISDEBIT = "@is_debit"; private const string PARM_FEE_ISOPEN = "@is_open"; private const string PARM_FEE_ISADVANCEPAY = "@is_advance_pay"; private const string PARM_FEE_SORT = "@sort"; private const string PARM_FEE_ISINVOICE = "@is_invoice"; private const string PARM_FEE_STATUS = "@fee_status"; private const string PARM_FEE_FRT = "@fee_frt"; private const string PARM_FEE_AUDIT_STATUS = "@audit_status"; private const string PARM_FEE_AUDIT_WMSOUTBSNO = "@WMSOUTBSNO"; private const string PARM_FEE_TAXRATE = "@taxrate"; private const string PARM_FEE_NOTAXAMOUNT = "@notaxamount"; private const string PARM_FEE_ACCTAXRATE = "@acctaxrate"; private const string PARM_FEE_LINENUM = "@LINENUM"; private const string PARM_FEE_MODIFIEDUSER = "@MODIFIEDUSER"; private const string PARM_FEE_MODIFIEDTIME = "@MODIFIEDTIME"; private const string PARM_FEE_F_NO = "@F_NO"; #endregion static private SqlParameter[] GetInsertParms() { SqlParameter[] parms = new SqlParameter[]{ new SqlParameter(PARM_FEE_GID,SqlDbType.VarChar,36), new SqlParameter(PARM_FEE_BSNO,SqlDbType.VarChar,60), new SqlParameter(PARM_FEE_TYPE,SqlDbType.VarChar,20), new SqlParameter(PARM_FEE_NAME,SqlDbType.VarChar,100), new SqlParameter(PARM_FEE_DESCRIPTION,SqlDbType.VarChar,100), new SqlParameter(PARM_FEE_CUSTOMER_TYPE,SqlDbType.VarChar,50), new SqlParameter(PARM_FEE_CUSTOMER_NAME,SqlDbType.VarChar,50), new SqlParameter(PARM_FEE_UNIT,SqlDbType.VarChar,20), new SqlParameter(PARM_FEE_UNIT_PRICE,SqlDbType.Decimal,18), new SqlParameter(PARM_FEE_QUANTITY,SqlDbType.Decimal,18), new SqlParameter(PARM_FEE_AMOUNT,SqlDbType.Decimal,18), new SqlParameter(PARM_FEE_CURRENCY,SqlDbType.VarChar,20), new SqlParameter(PARM_FEE_EXCHANGE_RATE,SqlDbType.Decimal,18), new SqlParameter(PARM_FEE_REASON,SqlDbType.VarChar,50), new SqlParameter(PARM_FEE_REMARK,SqlDbType.VarChar,100), new SqlParameter(PARM_FEE_COMMISSION_RATE,SqlDbType.Decimal,18), new SqlParameter(PARM_FEE_ENTER_OPERATOR,SqlDbType.VarChar,50), new SqlParameter(PARM_FEE_ENTER_DATE,SqlDbType.SmallDateTime), new SqlParameter(PARM_FEE_ISOPEN,SqlDbType.VarChar,1), new SqlParameter(PARM_FEE_ISADVANCEPAY,SqlDbType.VarChar,1), new SqlParameter(PARM_FEE_SORT,SqlDbType.Int), new SqlParameter(PARM_FEE_STATUS,SqlDbType.VarChar,20), new SqlParameter(PARM_FEE_FRT,SqlDbType.VarChar,20), new SqlParameter(PARM_FEE_TAXRATE,SqlDbType.Decimal,18), new SqlParameter(PARM_FEE_NOTAXAMOUNT,SqlDbType.Decimal,18), new SqlParameter(PARM_FEE_ACCTAXRATE,SqlDbType.Decimal,18), new SqlParameter(PARM_FEE_LINENUM,SqlDbType.Decimal,18), new SqlParameter(PARM_FEE_TAXUNIT_PRICE,SqlDbType.Decimal,18), new SqlParameter(PARM_FEE_MODIFIEDUSER,SqlDbType.VarChar,50), new SqlParameter(PARM_FEE_MODIFIEDTIME,SqlDbType.DateTime), new SqlParameter(PARM_FEE_F_NO,SqlDbType.VarChar,50), new SqlParameter(PARM_FEE_SETTLEMENT,SqlDbType.Decimal,18) }; return parms; } private const string SQL_INSERT_FEE = " INSERT INTO ch_fee(GID, BSNO, FEETYPE, FEENAME, FEEDESCRIPTION, CUSTOMERTYPE, CUSTOMERNAME, UNIT, UNITPRICE, QUANTITY, AMOUNT, CURRENCY, " + " EXCHANGERATE, REASON, REMARK, COMMISSIONRATE,ENTEROPERATOR, ENTERDATE,ISOPEN, ISADVANCEDPAY, SORT, FEESTATUS, FEEFRT, " + " TAXRATE,NOTAXAMOUNT,ACCTAXRATE,LINENUM,TAXUNITPRICE,MODIFIEDUSER,MODIFIEDTIME,F_NO,SETTLEMENT) " + " VALUES " + " (@gid,@bsno,@fee_type,@fee_name,@fee_description,@customer_type,@customer_name,@unit,@unit_price,@quantity,@amount,@currency, " + " @exchange_rate,@reason,@remark,@comm_rate,@enter_operator,@enter_date,@is_open,@is_advance_pay,@sort,@fee_status,@fee_frt, " + " @taxrate,@notaxamount,@acctaxrate,@LINENUM,@taxunit_price,@MODIFIEDUSER,@MODIFIEDTIME,@F_NO,@SETTLEMENT)";//DateTime.Now.ToString() #region 融资业务列表 static public List GetDataList(string strCondition, string sort) { var strSql = new StringBuilder(); strSql.Append(" select F_NO,F_CURRENCY,F_Rate,F_RMB,F_AMOUNT,CUSTOMER,f.REMARK,f.OP,dbo.trimtime(ENTERDATE) ENTERDATE "); strSql.Append(" ,dbo.trimdate(STARTDATE) STARTDATE,dbo.trimdate(ENDDATE) ENDDATE "); strSql.Append(" ,DAYS,isnull(BANK_INTEREST_RATE,0) BANK_INTEREST_RATE,isnull(BANK_INTEREST,0) BANK_INTEREST "); strSql.Append(" ,isnull(CUST_INTEREST_RATE,0) CUST_INTEREST_RATE,isnull(CUST_INTEREST,0) CUST_INTEREST "); strSql.Append(" ,isnull(BANK_EXCHANGERATE,0) BANK_EXCHANGERATE,isnull(CUST_EXCHANGERATE,0) CUST_EXCHANGERATE "); strSql.Append(" ,MAINNO,F_STATUS,FINANCE_BANK,M.[HTH],M.[seller],M.[company],M.[buyer], M.[BillNo],dbo.trimdate(BANKENDDATE) BANKENDDATE "); strSql.Append(" ,dbo.trimdate(ArrivalDate) ArrivalDate "); strSql.Append(" ,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=10 and EnumValueID=M.port) as portRef "); strSql.Append(" ,(select Name from company where gid=M.company) as companyname ,(select showname from [user] where gid=f.OP) as OPREF "); strSql.Append(" ,isnull(CUSTSTLAMOUNT,0) CUSTSTLAMOUNT,F_RMB-isnull(CUSTSTLAMOUNT,0) AS CUSTBALAMOUNT ");// strSql.Append(" ,(select sum(F_AMOUNT) from IMPORT_FINANCE_BODY where F_NO=f.F_NO and F_TYPE='2') BANKSTLAMOUNT,F_AMOUNT-(select sum(F_AMOUNT) from IMPORT_FINANCE_BODY where F_NO=f.F_NO and F_TYPE='2') AS BANKBALAMOUNT "); strSql.Append(" ,dbo.f_financeBank(f.F_NO) BANKNAME "); strSql.Append(" ,case when isnull(CUSTSTLAMOUNT,0)=0 then '未还款' when isnull(CUSTSTLAMOUNT,0)>=F_RMB then '全部归还' "); strSql.Append(" when isnull(CUSTSTLAMOUNT,0)>0 and isnull(CUSTSTLAMOUNT,0) 0) return list[0]; return new Financemb(); } private static List SetDateList(StringBuilder strSql) { var headList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { Financemb data = new Financemb(); #region Set DB data to Object data.F_NO = Convert.ToString(reader["F_NO"]); if (reader["F_AMOUNT"] != DBNull.Value) data.F_AMOUNT = Convert.ToDecimal(reader["F_AMOUNT"]); //headRow.SETTLETYPE = ds.Tables[0].Rows[0]["FSETTLCODE"].ToString() == "" ? 0 : int.Parse(ds.Tables[0].Rows[0]["FSETTLCODE"].ToString());//结算方式 //字段过滤器（判断是否存在该列） //reader.GetSchemaTable().DefaultView.RowFilter = "ColumnName= 'F_Rate'"; //if (reader.GetSchemaTable().DefaultView.Count > 0) //{ if (reader["F_Rate"] != DBNull.Value) //data.F_Rate = Convert.ToDecimal(reader["F_Rate"]); data.F_Rate = reader["F_Rate"].ToString() == "" ? 1 : Convert.ToDecimal( reader["F_Rate"]);//融资汇率 if (reader["F_RMB"] != DBNull.Value) //data.F_RMB = Convert.ToDecimal(reader["F_RMB"]); data.F_RMB = reader["F_RMB"].ToString() == "" ? 0 : Convert.ToDecimal(reader["F_RMB"]);//融资金额折合RMB //} data.F_CURRENCY = Convert.ToString(reader["F_CURRENCY"]); data.CUSTOMER = Convert.ToString(reader["CUSTOMER"]); data.REMARK = Convert.ToString(reader["REMARK"]); data.OP = Convert.ToString(reader["OP"]); data.OPREF = Convert.ToString(reader["OPREF"]); data.ENTERDATE = Convert.ToString(reader["ENTERDATE"]); if (!string.IsNullOrEmpty( reader["STARTDATE"].ToString() )) data.STARTDATE = Convert.ToDateTime(reader["STARTDATE"]).ToString("yyyy-MM-dd");//到港日期 if (!string.IsNullOrEmpty(reader["ENDDATE"].ToString())) data.ENDDATE = Convert.ToDateTime(reader["ENDDATE"]).ToString("yyyy-MM-dd");//到港日期 if (!string.IsNullOrEmpty(reader["BANKENDDATE"].ToString())) data.BANKENDDATE = Convert.ToDateTime(reader["BANKENDDATE"]).ToString("yyyy-MM-dd");//到港日期 if (!string.IsNullOrEmpty(reader["DAYS"] .ToString())) data.DAYS = Convert.ToInt16(reader["DAYS"]); data.BANK_INTEREST_RATE = Convert.ToString(reader["BANK_INTEREST_RATE"]); data.BANK_INTEREST = Convert.ToString(reader["BANK_INTEREST"]); data.CUST_INTEREST_RATE = Convert.ToString(reader["CUST_INTEREST_RATE"]); data.CUST_INTEREST = Convert.ToString(reader["CUST_INTEREST"]); data.BANK_EXCHANGERATE = Convert.ToString(reader["BANK_EXCHANGERATE"]); data.CUST_EXCHANGERATE = Convert.ToString(reader["CUST_EXCHANGERATE"]); data.MAINNO = Convert.ToString(reader["MAINNO"]); data.F_STATUS = Convert.ToString(reader["F_STATUS"]); data.FINANCE_BANK = Convert.ToString(reader["FINANCE_BANK"]); if (reader["CUSTSTLAMOUNT"] != DBNull.Value) data.CUSTSTLAMOUNT = reader["CUSTSTLAMOUNT"].ToString() == "" ? 0 : Convert.ToDecimal(reader["CUSTSTLAMOUNT"]); if (reader["BANKSTLAMOUNT"] != DBNull.Value) data.BANKSTLAMOUNT = reader["BANKSTLAMOUNT"].ToString() == "" ? 0 : Convert.ToDecimal(reader["BANKSTLAMOUNT"]); //if (reader["CUSTBALAMOUNT"] != DBNull.Value) // data.CUSTBALAMOUNT = reader["CUSTBALAMOUNT"].ToString() == "" ? 0 : Convert.ToDecimal(reader["CUSTBALAMOUNT"]); if (reader["BANKBALAMOUNT"] != DBNull.Value) data.BANKBALAMOUNT = reader["BANKBALAMOUNT"].ToString() == "" ? 0 : Convert.ToDecimal(reader["BANKBALAMOUNT"]); data.CUSTBALAMOUNT = data.F_RMB - data.CUSTSTLAMOUNT; data.BANKNAME = Convert.ToString(reader["BANKNAME"]); data.CUSTSTLSTATUS = Convert.ToString(reader["CUSTSTLSTATUS"]); #endregion headList.Add(data); } reader.Close(); } return headList; } #endregion #region 融资还款明细列表(import_finance_BODY) static public List GetFinanceList(string strCondition, string sort) { var strSql = new StringBuilder(); strSql.Append(" select GID,F_NO,F_AMOUNT,F_CURRENCY,CUSTOMER,ISFEE,F_TYPE, "); strSql.Append(" ENDDATE,[DAYS],EXCHANGERATE,INTEREST_RATE,D_INTEREST_RATE,Y_INTEREST_RATE,INTEREST,INTERESTUSD,REMARK,"); strSql.Append(" RMBVALUE,HANDFEE,TELXFEE,OTFEE,RMBAMOUNT,FINEFEE,TTLAMOUNTRMB"); strSql.Append(" ,BANKID,OP,dbo.trimtime(ENTERDATE) ENTERDATE,(select showname from [user] where gid=b.OP) as OPREF "); strSql.Append(",(CASE ISFEE WHEN 1 THEN '锁定' else '未锁定' end) as ISFEEREF "); strSql.Append(",b.BSNO,(select CUSTNO from v_op_bill where bsno=b.bsno) CUSTNO "); strSql.Append(" FROM IMPORT_FINANCE_BODY b"); if (!string.IsNullOrEmpty(strCondition)) { strSql.Append(" where " + strCondition); } return SetFinanceBodyList(strSql); } static public FinanceBodymb GetFinanceData(string condition, string sort) { var list = GetFinanceList(condition, sort); if (list.Count > 0) return list[0]; return new FinanceBodymb(); } private static List SetFinanceBodyList(StringBuilder strSql) { var headList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { FinanceBodymb data = new FinanceBodymb(); #region Set DB data to Object data.GID = Convert.ToString(reader["GID"]); data.F_NO = Convert.ToString(reader["F_NO"]); if (reader["F_AMOUNT"] != DBNull.Value) data.F_AMOUNT = Convert.ToDecimal(reader["F_AMOUNT"]); data.F_CURRENCY = Convert.ToString(reader["F_CURRENCY"]); data.CUSTOMER = Convert.ToString(reader["CUSTOMER"]); data.ISFEE = Convert.ToString(reader["ISFEE"]); data.ISFEEREF = Convert.ToString(reader["ISFEEREF"]); data.F_TYPE = Convert.ToString(reader["F_TYPE"]); data.ENDDATE = Convert.ToDateTime(reader["ENDDATE"]).ToString("yyyy-MM-dd"); if (reader["DAYS"] != DBNull.Value) data.DAYS = Convert.ToInt32(reader["DAYS"]); if (reader["EXCHANGERATE"] != DBNull.Value) data.EXCHANGERATE = Convert.ToDecimal(reader["EXCHANGERATE"]); if (reader["D_INTEREST_RATE"] != DBNull.Value) data.D_INTEREST_RATE = Convert.ToDecimal(reader["D_INTEREST_RATE"]); if (reader["INTEREST_RATE"] != DBNull.Value) data.INTEREST_RATE = Convert.ToDecimal(reader["INTEREST_RATE"]); if (reader["Y_INTEREST_RATE"] != DBNull.Value) data.Y_INTEREST_RATE = Convert.ToDecimal(reader["Y_INTEREST_RATE"]); if (reader["INTEREST"] != DBNull.Value) data.INTEREST = Convert.ToDecimal(reader["INTEREST"]); if (reader["INTERESTUSD"] != DBNull.Value) data.INTERESTUSD = Convert.ToDecimal(reader["INTERESTUSD"]); data.REMARK = Convert.ToString(reader["REMARK"]); if (reader["RMBVALUE"] != DBNull.Value) data.RMBVALUE = Convert.ToDecimal(reader["RMBVALUE"]); if (reader["HANDFEE"] != DBNull.Value) data.HANDFEE = Convert.ToDecimal(reader["HANDFEE"]); if (reader["TELXFEE"] != DBNull.Value) data.TELXFEE = Convert.ToDecimal(reader["TELXFEE"]); if (reader["OTFEE"] != DBNull.Value) data.OTFEE = Convert.ToDecimal(reader["OTFEE"]); if (reader["RMBAMOUNT"] != DBNull.Value) data.RMBAMOUNT = Convert.ToDecimal(reader["RMBAMOUNT"]); if (reader["FINEFEE"] != DBNull.Value) data.FINEFEE = Convert.ToDecimal(reader["FINEFEE"]); if (reader["TTLAMOUNTRMB"] != DBNull.Value) data.TTLAMOUNTRMB = Convert.ToDecimal(reader["TTLAMOUNTRMB"]); data.BANKID = Convert.ToString(reader["BANKID"]); data.OP = Convert.ToString(reader["OP"]); data.OPREF = Convert.ToString(reader["OPREF"]); data.ENTERDATE = Convert.ToString(reader["ENTERDATE"]); data.BSNO = Convert.ToString(reader["BSNO"]); data.CUSTNO = Convert.ToString(reader["CUSTNO"]); #endregion headList.Add(data); } reader.Close(); } return headList; } public static DBResult SaveBody(string F_NO, List bodyList,bool isfee) { var result = new DBResult(); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { var cmdInsert = db.GetSqlStringCommand( @"insert into IMPORT_FINANCE_BODY (GID,F_NO,F_AMOUNT,F_CURRENCY,CUSTOMER,ISFEE,F_TYPE,ENDDATE,DAYS,EXCHANGERATE,D_INTEREST_RATE,INTEREST_RATE,Y_INTEREST_RATE ,INTEREST,INTERESTUSD,REMARK,RMBVALUE,HANDFEE,TELXFEE,OTFEE,RMBAMOUNT,FINEFEE,TTLAMOUNTRMB,BANKID,BSNO) values (@GID,@F_NO,@F_AMOUNT,@F_CURRENCY,@CUSTOMER,@ISFEE,@F_TYPE,@ENDDATE,@DAYS,@EXCHANGERATE,@D_INTEREST_RATE,@INTEREST_RATE,@Y_INTEREST_RATE ,@INTEREST,@INTERESTUSD,@REMARK,@RMBVALUE,@HANDFEE,@TELXFEE,@OTFEE,@RMBAMOUNT,@FINEFEE,@TTLAMOUNTRMB,@BANKID,@BSNO) "); var cmdUpdate = db.GetSqlStringCommand( @"update IMPORT_FINANCE_BODY set F_AMOUNT=@F_AMOUNT,F_CURRENCY=@F_CURRENCY,CUSTOMER=@CUSTOMER,ISFEE=@ISFEE,F_TYPE=@F_TYPE,ENDDATE=@ENDDATE ,DAYS=@DAYS,EXCHANGERATE=@EXCHANGERATE,D_INTEREST_RATE=@D_INTEREST_RATE,INTEREST_RATE=@INTEREST_RATE,Y_INTEREST_RATE=@Y_INTEREST_RATE,INTEREST=@INTEREST,INTERESTUSD=@INTERESTUSD,REMARK=@REMARK,RMBVALUE=@RMBVALUE, HANDFEE=@HANDFEE,TELXFEE=@TELXFEE,OTFEE=@OTFEE,RMBAMOUNT=@RMBAMOUNT,FINEFEE=@FINEFEE,TTLAMOUNTRMB=@TTLAMOUNTRMB,BANKID=@BANKID,BSNO=@BSNO where GID=@GID "); if (bodyList != null) { foreach (var enumValue in bodyList) { if (enumValue.F_NO == "*" || enumValue.F_NO == "") { cmdInsert.Parameters.Clear(); db.AddInParameter(cmdInsert, "@GID", DbType.String, enumValue.GID); db.AddInParameter(cmdInsert, "@F_NO", DbType.String, F_NO); db.AddInParameter(cmdInsert, "@F_AMOUNT", DbType.Decimal, enumValue.F_AMOUNT); db.AddInParameter(cmdInsert, "@F_CURRENCY", DbType.String, enumValue.F_CURRENCY); db.AddInParameter(cmdInsert, "@CUSTOMER", DbType.String, enumValue.CUSTOMER); if (isfee) db.AddInParameter(cmdInsert, "@ISFEE", DbType.String, "1"); else db.AddInParameter(cmdInsert, "@ISFEE", DbType.String, enumValue.ISFEE); db.AddInParameter(cmdInsert, "@F_TYPE", DbType.String, enumValue.F_TYPE); db.AddInParameter(cmdInsert, "@ENDDATE", DbType.String, enumValue.ENDDATE); db.AddInParameter(cmdInsert, "@DAYS", DbType.Int16, enumValue.DAYS); db.AddInParameter(cmdInsert, "@EXCHANGERATE", DbType.Decimal, enumValue.EXCHANGERATE); db.AddInParameter(cmdInsert, "@D_INTEREST_RATE", DbType.Decimal, enumValue.D_INTEREST_RATE); db.AddInParameter(cmdInsert, "@INTEREST_RATE", DbType.Decimal, enumValue.INTEREST_RATE); db.AddInParameter(cmdInsert, "@Y_INTEREST_RATE", DbType.Decimal, enumValue.Y_INTEREST_RATE); db.AddInParameter(cmdInsert, "@INTEREST", DbType.Decimal, enumValue.INTEREST); db.AddInParameter(cmdInsert, "@INTERESTUSD", DbType.Decimal, enumValue.INTERESTUSD); db.AddInParameter(cmdInsert, "@REMARK", DbType.String, enumValue.REMARK); db.AddInParameter(cmdInsert, "@RMBVALUE", DbType.Decimal, enumValue.RMBVALUE); db.AddInParameter(cmdInsert, "@HANDFEE", DbType.Decimal, enumValue.HANDFEE); db.AddInParameter(cmdInsert, "@OTFEE", DbType.Decimal, enumValue.OTFEE); db.AddInParameter(cmdInsert, "@TELXFEE", DbType.Decimal, enumValue.TELXFEE); db.AddInParameter(cmdInsert, "@RMBAMOUNT", DbType.Decimal, enumValue.RMBAMOUNT); db.AddInParameter(cmdInsert, "@FINEFEE", DbType.Decimal, enumValue.FINEFEE); db.AddInParameter(cmdInsert, "@TTLAMOUNTRMB", DbType.Decimal, enumValue.TTLAMOUNTRMB); db.AddInParameter(cmdInsert, "@BANKID", DbType.String, enumValue.BANKID); db.AddInParameter(cmdInsert, "@BSNO", DbType.String, enumValue.BSNO); db.ExecuteNonQuery(cmdInsert, tran); } else { cmdUpdate.Parameters.Clear(); db.AddInParameter(cmdUpdate, "@GID", DbType.String, enumValue.GID); db.AddInParameter(cmdUpdate, "@F_AMOUNT", DbType.Decimal, enumValue.F_AMOUNT); db.AddInParameter(cmdUpdate, "@F_CURRENCY", DbType.String, enumValue.F_CURRENCY); db.AddInParameter(cmdUpdate, "@CUSTOMER", DbType.String, enumValue.CUSTOMER); if (isfee) db.AddInParameter(cmdUpdate, "@ISFEE", DbType.String, "1"); else db.AddInParameter(cmdUpdate, "@ISFEE", DbType.String, enumValue.ISFEE); db.AddInParameter(cmdUpdate, "@F_TYPE", DbType.String, enumValue.F_TYPE); db.AddInParameter(cmdUpdate, "@ENDDATE", DbType.String, enumValue.ENDDATE); db.AddInParameter(cmdUpdate, "@DAYS", DbType.Int16, enumValue.DAYS); db.AddInParameter(cmdUpdate, "@EXCHANGERATE", DbType.Decimal, enumValue.EXCHANGERATE); db.AddInParameter(cmdUpdate, "@D_INTEREST_RATE", DbType.Decimal, enumValue.D_INTEREST_RATE); db.AddInParameter(cmdUpdate, "@INTEREST_RATE", DbType.Decimal, enumValue.INTEREST_RATE); db.AddInParameter(cmdUpdate, "@Y_INTEREST_RATE", DbType.Decimal, enumValue.Y_INTEREST_RATE); db.AddInParameter(cmdUpdate, "@INTEREST", DbType.Decimal, enumValue.INTEREST); db.AddInParameter(cmdUpdate, "@INTERESTUSD", DbType.Decimal, enumValue.INTERESTUSD); db.AddInParameter(cmdUpdate, "@REMARK", DbType.String, enumValue.REMARK); db.AddInParameter(cmdUpdate, "@RMBVALUE", DbType.Decimal, enumValue.RMBVALUE); db.AddInParameter(cmdUpdate, "@HANDFEE", DbType.Decimal, enumValue.HANDFEE); db.AddInParameter(cmdUpdate, "@OTFEE", DbType.Decimal, enumValue.OTFEE); db.AddInParameter(cmdUpdate, "@TELXFEE", DbType.Decimal, enumValue.TELXFEE); db.AddInParameter(cmdUpdate, "@RMBAMOUNT", DbType.Decimal, enumValue.RMBAMOUNT); db.AddInParameter(cmdUpdate, "@FINEFEE", DbType.Decimal, enumValue.FINEFEE); db.AddInParameter(cmdUpdate, "@TTLAMOUNTRMB", DbType.Decimal, enumValue.TTLAMOUNTRMB); db.AddInParameter(cmdUpdate, "@BANKID", DbType.String, enumValue.BANKID); db.AddInParameter(cmdUpdate, "@BSNO", DbType.String, enumValue.BSNO); db.ExecuteNonQuery(cmdUpdate, tran); } } } tran.Commit(); } catch (Exception e) { tran.Rollback(); result.Success = false; result.Message = "保存出现错误,"+e.Message+",请重试或联系系统管理员"; return result; } } result.Success = true; result.Message = "保存成功" + result.Message; return result; } static public int CreateBodyFee(string F_NO, List bodyList, string USERID,string buildtype) { int iResult = 0; if (bodyList != null) { foreach (var enumValue in bodyList) { iResult = CreateBodyFeeFn(F_NO,enumValue,USERID, buildtype); } } return iResult; } static public int CreateBodyFeeFn(string F_NO, FinanceBodymb enumValue, string USERID,string buildtype) { int iResult = 0; using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction)) { try { var endDateTime = String.IsNullOrEmpty(enumValue.ENDDATE)? DateTime.Now: Convert.ToDateTime(enumValue.ENDDATE); var remark1 ="融资时间： " + endDateTime.AddDays(enumValue.DAYS*-1+1).ToString("yyyy-MM-dd") + " 还款时间：" + endDateTime.ToString("yyyy-MM-dd") + " 计息天数：" + enumValue.DAYS; if (enumValue.RMBVALUE != 0) { SqlParameter[] validParms = new SqlParameter[] { new SqlParameter(PARM_FEE_GID,SqlDbType.VarChar,36) }; SqlParameter[] parms = GetInsertParms(); /* + " (@gid,@bsno,@fee_type,@fee_name,@fee_description,@customer_type,@customer_name,@unit,@unit_price,@quantity,@amount,@currency, " + " @exchange_rate,@reason,@remark,@comm_rate,@enter_operator,@enter_date,@is_open,@is_advance_pay,@sort,@fee_status,@fee_frt, " + " @taxrate,@notaxamount,@acctaxrate,@LINENUM,@taxunit_price,@MODIFIEDUSER,@MODIFIEDTIME,@F_NO,@SETTLEMENT)";//DateTime.Now.ToString() */ parms = GetInsertParms(); parms[0].Value = Guid.NewGuid().ToString(); parms[1].Value = F_NO; parms[2].Value = enumValue.F_TYPE; parms[3].Value = "融资本金"; parms[4].Value = ""; parms[5].Value = ""; parms[6].Value = enumValue.CUSTOMER; parms[7].Value = "票"; parms[8].Value = enumValue.F_AMOUNT; parms[9].Value = 1; parms[10].Value = enumValue.F_AMOUNT; parms[11].Value = enumValue.F_CURRENCY; parms[12].Value = enumValue.EXCHANGERATE;//汇率 parms[13].Value = ""; parms[14].Value = enumValue.RMBAMOUNT + "RMB "+ remark1;// parms[15].Value = "0"; parms[16].Value = USERID; parms[17].Value = DateTime.Now; parms[18].Value = "0"; parms[19].Value = "0"; parms[20].Value = 1; parms[21].Value = 0; parms[22].Value = ""; parms[23].Value = 0; parms[24].Value = enumValue.F_AMOUNT; parms[25].Value = 0; parms[26].Value = 1; parms[27].Value = enumValue.F_AMOUNT; parms[28].Value = USERID; parms[29].Value = DateTime.Now.ToString(); parms[30].Value = enumValue.GID; parms[31].Value = 0; SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_INSERT_FEE, parms); } else if (enumValue.RMBVALUE == 0 && enumValue.F_AMOUNT != 0) { SqlParameter[] validParms = new SqlParameter[] { new SqlParameter(PARM_FEE_GID,SqlDbType.VarChar,36) }; SqlParameter[] parms = GetInsertParms(); parms[0].Value = Guid.NewGuid().ToString(); parms[1].Value = F_NO; parms[2].Value = enumValue.F_TYPE; parms[3].Value = "融资本金"; parms[4].Value = ""; parms[5].Value = ""; parms[6].Value = enumValue.CUSTOMER; parms[7].Value = "票"; parms[8].Value = enumValue.F_AMOUNT; parms[9].Value = 1; parms[10].Value = enumValue.F_AMOUNT; parms[11].Value = enumValue.F_CURRENCY; parms[12].Value = 1; parms[13].Value = ""; parms[14].Value = remark1; parms[15].Value = "0"; parms[16].Value = USERID; parms[17].Value = DateTime.Now; parms[18].Value = "0"; parms[19].Value = "0"; parms[20].Value = 1; parms[21].Value = 0; parms[22].Value = ""; parms[23].Value = 0; parms[24].Value = enumValue.F_AMOUNT; parms[25].Value = 0; parms[26].Value = 1; parms[27].Value = enumValue.F_AMOUNT; parms[28].Value = USERID; parms[29].Value = DateTime.Now.ToString(); parms[30].Value = enumValue.GID; parms[31].Value = 0; SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_INSERT_FEE, parms); } if (enumValue.INTEREST != 0) { SqlParameter[] validParms = new SqlParameter[] { new SqlParameter(PARM_FEE_GID,SqlDbType.VarChar,36) }; SqlParameter[] parms = GetInsertParms(); parms[0].Value = Guid.NewGuid().ToString(); parms[1].Value = buildtype == "true" ? F_NO : enumValue.BSNO; parms[2].Value = enumValue.F_TYPE; parms[3].Value = "附加费";//原 融资利息 parms[4].Value = ""; parms[5].Value = ""; parms[6].Value = enumValue.CUSTOMER; parms[7].Value = "票"; parms[8].Value = enumValue.INTEREST; parms[9].Value = 1; parms[10].Value = enumValue.INTEREST; parms[11].Value = "RMB"; parms[12].Value = 1; parms[13].Value = ""; parms[14].Value = remark1; parms[15].Value = "0"; parms[16].Value = USERID; parms[17].Value = DateTime.Now; parms[18].Value = "0"; parms[19].Value = "0"; parms[20].Value = 1; parms[21].Value = 0; parms[22].Value = ""; parms[23].Value = 0; parms[24].Value = enumValue.INTEREST; parms[25].Value = 0; parms[26].Value = 1; parms[27].Value = enumValue.INTEREST; parms[28].Value = USERID; parms[29].Value = DateTime.Now.ToString(); parms[30].Value = enumValue.BSNO; parms[31].Value = 0; SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_INSERT_FEE, parms); } else if (enumValue.INTEREST==0&&enumValue.INTERESTUSD != 0) { SqlParameter[] validParms = new SqlParameter[] { new SqlParameter(PARM_FEE_GID,SqlDbType.VarChar,36) }; SqlParameter[] parms = GetInsertParms(); parms[0].Value = Guid.NewGuid().ToString(); parms[1].Value = buildtype == "true" ? F_NO : enumValue.BSNO; parms[2].Value = enumValue.F_TYPE; parms[3].Value = "附加费";//原 融资利息 parms[4].Value = ""; parms[5].Value = ""; parms[6].Value = enumValue.CUSTOMER; parms[7].Value = "票"; parms[8].Value = enumValue.INTERESTUSD; parms[9].Value = 1; parms[10].Value = enumValue.INTERESTUSD; parms[11].Value = enumValue.F_CURRENCY; parms[12].Value = 1; parms[13].Value = ""; parms[14].Value = remark1; parms[15].Value = "0"; parms[16].Value = USERID; parms[17].Value = DateTime.Now; parms[18].Value = "0"; parms[19].Value = "0"; parms[20].Value = 1; parms[21].Value = 0; parms[22].Value = ""; parms[23].Value = 0; parms[24].Value = enumValue.INTERESTUSD; parms[25].Value = 0; parms[26].Value = 1; parms[27].Value = enumValue.INTERESTUSD; parms[28].Value = USERID; parms[29].Value = DateTime.Now.ToString(); parms[30].Value = enumValue.BSNO; parms[31].Value = 0; SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_INSERT_FEE, parms); } if (enumValue.HANDFEE != 0) { SqlParameter[] validParms = new SqlParameter[] { new SqlParameter(PARM_FEE_GID,SqlDbType.VarChar,36) }; SqlParameter[] parms = GetInsertParms(); parms[0].Value = Guid.NewGuid().ToString(); parms[1].Value = buildtype == "true" ? F_NO : enumValue.BSNO; parms[2].Value = enumValue.F_TYPE; parms[3].Value = "手续费"; parms[4].Value = ""; parms[5].Value = ""; parms[6].Value = enumValue.CUSTOMER; parms[7].Value = "票"; parms[8].Value = enumValue.HANDFEE; parms[9].Value = 1; parms[10].Value = enumValue.HANDFEE; parms[11].Value = "RMB"; parms[12].Value = 1; parms[13].Value = ""; parms[14].Value = remark1; parms[15].Value = "0"; parms[16].Value = USERID; parms[17].Value = DateTime.Now; parms[18].Value = "0"; parms[19].Value = "0"; parms[20].Value = 1; parms[21].Value = 0; parms[22].Value = ""; parms[23].Value = 0; parms[24].Value = enumValue.HANDFEE; parms[25].Value = 0; parms[26].Value = 1; parms[27].Value = enumValue.HANDFEE; parms[28].Value = USERID; parms[29].Value = DateTime.Now.ToString(); parms[30].Value = enumValue.GID; parms[31].Value = 0; SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_INSERT_FEE, parms); } if (enumValue.TELXFEE != 0) { SqlParameter[] validParms = new SqlParameter[] { new SqlParameter(PARM_FEE_GID,SqlDbType.VarChar,36) }; SqlParameter[] parms = GetInsertParms(); parms[0].Value = Guid.NewGuid().ToString(); parms[1].Value = buildtype == "true" ? F_NO : enumValue.BSNO; parms[2].Value = enumValue.F_TYPE; parms[3].Value = "电报费"; parms[4].Value = ""; parms[5].Value = ""; parms[6].Value = enumValue.CUSTOMER; parms[7].Value = "票"; parms[8].Value = enumValue.TELXFEE; parms[9].Value = 1; parms[10].Value = enumValue.TELXFEE; parms[11].Value = "RMB"; parms[12].Value = 1; parms[13].Value = ""; parms[14].Value = remark1; parms[15].Value = "0"; parms[16].Value = USERID; parms[17].Value = DateTime.Now; parms[18].Value = "0"; parms[19].Value = "0"; parms[20].Value = 1; parms[21].Value = 0; parms[22].Value = ""; parms[23].Value = 0; parms[24].Value = enumValue.TELXFEE; parms[25].Value = 0; parms[26].Value = 1; parms[27].Value = enumValue.TELXFEE; parms[28].Value = USERID; parms[29].Value = DateTime.Now.ToString(); parms[30].Value = enumValue.GID; parms[31].Value = 0; SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_INSERT_FEE, parms); } if (enumValue.FINEFEE != 0) { SqlParameter[] validParms = new SqlParameter[] { new SqlParameter(PARM_FEE_GID,SqlDbType.VarChar,36) }; SqlParameter[] parms = GetInsertParms(); parms[0].Value = Guid.NewGuid().ToString(); parms[1].Value = buildtype == "true" ? F_NO : enumValue.BSNO; parms[2].Value = enumValue.F_TYPE; parms[3].Value = "罚息"; parms[4].Value = ""; parms[5].Value = ""; parms[6].Value = enumValue.CUSTOMER; parms[7].Value = "票"; parms[8].Value = enumValue.FINEFEE; parms[9].Value = 1; parms[10].Value = enumValue.FINEFEE; parms[11].Value = "RMB"; parms[12].Value = 1; parms[13].Value = ""; parms[14].Value = remark1; parms[15].Value = "0"; parms[16].Value = USERID; parms[17].Value = DateTime.Now; parms[18].Value = "0"; parms[19].Value = "0"; parms[20].Value = 1; parms[21].Value = 0; parms[22].Value = ""; parms[23].Value = 0; parms[24].Value = enumValue.FINEFEE; parms[25].Value = 0; parms[26].Value = 1; parms[27].Value = enumValue.FINEFEE; parms[28].Value = USERID; parms[29].Value = DateTime.Now.ToString(); parms[30].Value = enumValue.GID; parms[31].Value = 0; SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_INSERT_FEE, parms); } if (enumValue.OTFEE != 0) { SqlParameter[] validParms = new SqlParameter[] { new SqlParameter(PARM_FEE_GID,SqlDbType.VarChar,36) }; SqlParameter[] parms = GetInsertParms(); parms[0].Value = Guid.NewGuid().ToString(); parms[1].Value = buildtype == "true" ? F_NO : enumValue.BSNO; parms[2].Value = enumValue.F_TYPE; parms[3].Value = "其他费用"; parms[4].Value = ""; parms[5].Value = ""; parms[6].Value = enumValue.CUSTOMER; parms[7].Value = "票"; parms[8].Value = enumValue.OTFEE; parms[9].Value = 1; parms[10].Value = enumValue.OTFEE; parms[11].Value = "RMB"; parms[12].Value = 1; parms[13].Value = ""; parms[14].Value = remark1; parms[15].Value = "0"; parms[16].Value = USERID; parms[17].Value = DateTime.Now; parms[18].Value = "0"; parms[19].Value = "0"; parms[20].Value = 1; parms[21].Value = 0; parms[22].Value = ""; parms[23].Value = 0; parms[24].Value = enumValue.OTFEE; parms[25].Value = 0; parms[26].Value = 1; parms[27].Value = enumValue.OTFEE; parms[28].Value = USERID; parms[29].Value = DateTime.Now.ToString(); parms[30].Value = enumValue.GID; parms[31].Value = 0; SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_INSERT_FEE, parms); } sqlTran.Commit(); iResult = 1;//状态为1表示插入成功 } catch (Exception execError) { iResult = -1;//有异常,插入失败 sqlTran.Rollback(); iResult = -2;//插入异常,事务已回滚成功 throw execError; } finally { SqlHelper.CloseConnection(); } } return iResult; } static public int DeleteBody(string tempGId) { int iResult = 0; using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction)) { try { var strSql = new StringBuilder(); strSql.Append("Delete from IMPORT_FINANCE_BODY where "); strSql.Append(" GId in (" + tempGId + ")"); SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql.ToString()); sqlTran.Commit(); iResult = 1;//状态为1表示插入成功 } catch (Exception execError) { iResult = -1;//有异常,插入失败 sqlTran.Rollback(); iResult = -2;//插入异常,事务已回滚成功 throw execError; } finally { SqlHelper.CloseConnection(); } } return iResult; } #endregion #region 融资申请 static public List GetBodyList(string condition) { var strSql = new StringBuilder(); strSql.Append(" select c.GID,c.CUSTOMERNAME CUSTOMER,M.ID,M.contractno,c.F_NO,M.HTH,M.billno,M.BUYER,M.seller,ARRIVALDATE "); strSql.Append(" ,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=10 and EnumValueID=M.port) as portRef "); strSql.Append(" ,(select NAME from [COMPANY] where GID=M.COMPANY) as COMPANYNAME "); strSql.Append(" ,c.AMOUNT as W_AMOUNT "); strSql.Append(" ,0 as S_AMOUNT "); strSql.Append(" ,f.F_CURRENCY CURRENCY "); strSql.Append(" from Import_Finance_do c left join IMPORT_Main M ON M.contractno=c.contractno "); strSql.Append(" left join Import_Finance f ON f.F_NO=c.F_NO "); strSql.Append(" where 1=1 "); if (!string.IsNullOrEmpty(condition)) { strSql.Append(" and " + condition); } return SetBodyAddList(strSql); } static public List GetBodyAddList(string condition) { var strSql = new StringBuilder(); strSql.Append(" select '' GID, c.CUSTOMERNAME as CUSTOMER,M.ID,M.contractno,'' F_NO,M.HTH,M.billno,M.BUYER,M.seller,M.ARRIVALDATE "); strSql.Append(" ,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=10 and EnumValueID=M.port) as portRef "); strSql.Append(" ,sum(c.amount) as W_AMOUNT "); strSql.Append(" ,c.CURRENCY "); strSql.Append(" ,(select NAME from [COMPANY] where GID=m.COMPANY) as COMPANYNAME "); strSql.Append(" from ch_fee c left join IMPORT_Main M ON M.contractno=c.BSNO "); strSql.Append(" where c.FEETYPE=1 AND c.FEESTATUS in (10) "); if (!string.IsNullOrEmpty(condition)) { strSql.Append(" and " + condition); } strSql.Append(" group by c.CUSTOMERNAME,M.ID,M.contractno,M.HTH,M.billno,M.BUYER,M.seller,M.port,M.COMPANY,M.ARRIVALDATE,c.CURRENCY "); return SetBodyAddList(strSql); } private static List SetBodyAddList(StringBuilder strSql) { var headList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { FinanceTradeBody data = new FinanceTradeBody(); #region Set DB data to Object data.GID = Convert.ToString(reader["GID"]); data.F_NO = Convert.ToString(reader["F_NO"]); data.CUSTOMER = Convert.ToString(reader["CUSTOMER"]); data.CONTRACTNO = Convert.ToString(reader["CONTRACTNO"]); data.HTH = Convert.ToString(reader["HTH"]); data.SELLER = Convert.ToString(reader["SELLER"]); data.BUYER = Convert.ToString(reader["BUYER"]); data.BILLNO = Convert.ToString(reader["BILLNO"]); data.COMPANYNAME = Convert.ToString(reader["COMPANYNAME"]); data.PORTREF = Convert.ToString(reader["PORTREF"]); data.CURR = Convert.ToString(reader["CURRENCY"]); if (reader["ARRIVALDATE"] != DBNull.Value) data.ARRIVALDATE = Convert.ToDateTime(reader["ARRIVALDATE"]).ToString("yyyy-MM-dd"); if (reader["W_AMOUNT"] != DBNull.Value) data.W_AMOUNT = Convert.ToDecimal(reader["W_AMOUNT"]); if (reader["W_AMOUNT"] != DBNull.Value) data.R_AMOUNT = Convert.ToDecimal(reader["W_AMOUNT"]); #endregion headList.Add(data); } reader.Close(); } return headList; } public static DBResult AddBill(string billno, List bodylist) { var result = new DBResult(); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { var cmdInsert = db.GetSqlStringCommand( @"insert into Import_Finance_do (GID,F_NO,CONTRACTNO,CUSTOMERNAME,AMOUNT,DOAMOUNT) values (@GID,@F_NO,@CONTRACTNO,@CUSTOMERNAME,@AMOUNT,@DOAMOUNT) "); var cmdUpdate = db.GetSqlStringCommand( @"UPDATE IMPORT_FINANCE SET F_AMOUNT=ISNULL(F_AMOUNT,0)+@F_AMOUNT WHERE F_NO=@F_NO "); var cmdUpdateM = db.GetSqlStringCommand( @"update ch_fee set FEESTATUS=11,F_NO=@F_NO where BSNO=@CONTRACTNO AND FEESTATUS=10 AND CURRENCY=@CURRENCY"); decimal amount = 0; if (bodylist != null) { foreach (var enumValue in bodylist) { cmdInsert.Parameters.Clear(); db.AddInParameter(cmdInsert, "@GID", DbType.String, Guid.NewGuid().ToString()); db.AddInParameter(cmdInsert, "@F_NO", DbType.String, billno); db.AddInParameter(cmdInsert, "@CONTRACTNO", DbType.String, enumValue.CONTRACTNO); db.AddInParameter(cmdInsert, "@CUSTOMERNAME", DbType.String, enumValue.CUSTOMER); db.AddInParameter(cmdInsert, "@AMOUNT", DbType.Decimal, enumValue.R_AMOUNT); db.AddInParameter(cmdInsert, "@DOAMOUNT", DbType.Decimal, 0); db.ExecuteNonQuery(cmdInsert, tran); amount = amount + enumValue.R_AMOUNT; cmdUpdateM.Parameters.Clear(); db.AddInParameter(cmdUpdateM, "@CONTRACTNO", DbType.String, enumValue.CONTRACTNO); db.AddInParameter(cmdUpdateM, "@F_NO", DbType.String, billno); db.AddInParameter(cmdUpdateM, "@CURRENCY", DbType.String, enumValue.CURR); db.ExecuteNonQuery(cmdUpdateM, tran); } } cmdUpdate.Parameters.Clear(); db.AddInParameter(cmdUpdate, "@F_NO", DbType.String, billno); db.AddInParameter(cmdUpdate, "@F_AMOUNT", DbType.Decimal, amount); db.ExecuteNonQuery(cmdUpdate, tran); result = new DBResult(); result.Success = true; result.Message = "成功"; tran.Commit(); } catch (Exception) { tran.Rollback(); result.Success = false; result.Message = "保存出现错误，请重试或联系系统管理员"; return result; } } result.Success = true; result.Message = "保存成功"; return result; } public static DBResult DelBill(string billno, List bodylist) { var result = new DBResult(); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { var cmdInsert = db.GetSqlStringCommand( @"delete from Import_Finance_do where GID=@GID "); var cmdUpdate = db.GetSqlStringCommand( @"UPDATE IMPORT_FINANCE SET F_AMOUNT=ISNULL(F_AMOUNT,0)-@F_AMOUNT WHERE F_NO=@F_NO "); var cmdUpdateM = db.GetSqlStringCommand( @"update ch_fee set FEESTATUS=10 where F_NO=@F_NO and BSNO=@CONTRACTNO "); decimal amount = 0; if (bodylist != null) { foreach (var enumValue in bodylist) { cmdInsert.Parameters.Clear(); db.AddInParameter(cmdInsert, "@GID", DbType.String, enumValue.GID); db.ExecuteNonQuery(cmdInsert, tran); amount = amount + enumValue.W_AMOUNT; cmdUpdateM.Parameters.Clear(); db.AddInParameter(cmdUpdateM, "@CONTRACTNO", DbType.String, enumValue.CONTRACTNO); db.AddInParameter(cmdUpdateM, "@F_NO", DbType.String, billno); db.ExecuteNonQuery(cmdUpdateM, tran); } } cmdUpdate.Parameters.Clear(); db.AddInParameter(cmdUpdate, "@F_NO", DbType.String, billno); db.AddInParameter(cmdUpdate, "@F_AMOUNT", DbType.Decimal, amount); db.ExecuteNonQuery(cmdUpdate, tran); result = new DBResult(); result.Success = true; result.Message = "成功"; tran.Commit(); } catch (Exception) { tran.Rollback(); result.Success = false; result.Message = "保存出现错误，请重试或联系系统管理员"; return result; } } result.Success = true; result.Message = "保存成功"; return result; } #endregion #region 提交和撤销融资申请 public static DBResult SubmitAudit(String USERID, String bill) { var result = new DBResult(); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { var cmdupdate = db.GetSqlStringCommand("update IMPORT_FINANCE set F_STATUS='提交融资' where F_NO=@F_NO"); cmdupdate.Parameters.Clear(); db.AddInParameter(cmdupdate, "@F_NO", DbType.String, bill); db.ExecuteNonQuery(cmdupdate, tran); result = new DBResult(); result.Success = true; result.Message = "提交成功"; tran.Commit(); } catch (Exception e) { tran.Rollback(); result.Success = false; result.Message = "提交审核错误，请重试或联系系统管理员 " + e.Message; return result; } } return result; } public static DBResult SubmitAuditList(String USERID, String bills) { var result = new DBResult(); var billList = bills.Split(','); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { foreach (var bill in billList) { var cmdupdate = db.GetSqlStringCommand("update IMPORT_FINANCE set F_STATUS='提交融资' where F_NO=@F_NO"); cmdupdate.Parameters.Clear(); db.AddInParameter(cmdupdate, "@F_NO", DbType.String, bill); db.ExecuteNonQuery(cmdupdate, tran); result = new DBResult(); result.Success = true; result.Message = "提交成功"; } tran.Commit(); } catch (Exception) { tran.Rollback(); result.Success = false; result.Message = "提交审核错误，请重试或联系系统管理员"; return result; } } return result; } public static DBResult SubmitAuditBack(String USERID, String bill) { var result = new DBResult(); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { var cmdupdate = db.GetSqlStringCommand("update IMPORT_FINANCE set F_STATUS='新建融资' where F_NO=@F_NO"); cmdupdate.Parameters.Clear(); db.AddInParameter(cmdupdate, "@F_NO", DbType.String, bill); db.ExecuteNonQuery(cmdupdate, tran); result = new DBResult(); result.Success = true; result.Message = "撤销成功"; tran.Commit(); } catch (Exception) { tran.Rollback(); result.Success = false; result.Message = "撤销提交错误，请重试或联系系统管理员"; return result; } } return result; } public static DBResult SubmitAuditBackList(String USERID, String bills) { var result = new DBResult(); var billList = bills.Split(','); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { foreach (var bill in billList) { var cmdupdate = db.GetSqlStringCommand("update IMPORT_FINANCE set F_STATUS='新建融资' where F_NO=@F_NO"); cmdupdate.Parameters.Clear(); db.AddInParameter(cmdupdate, "@F_NO", DbType.String, bill); db.ExecuteNonQuery(cmdupdate, tran); result = new DBResult(); result.Success = true; result.Message = "撤销提交成功"; } tran.Commit(); } catch (Exception) { tran.Rollback(); result.Success = false; result.Message = "撤销提交错误，请重试或联系系统管理员"; return result; } } return result; } #endregion #region 银行明细 static public List GetBankList(string strCondition) { var strSql = new StringBuilder(); strSql.Append(" select GID,F_NO,BANK,AMOUNT,DOAMOUNT "); strSql.Append(" FROM IMPORT_FINANCE_BANK "); if (!string.IsNullOrEmpty(strCondition)) { strSql.Append(" where " + strCondition); } return SetBankList(strSql); } private static List SetBankList(StringBuilder strSql) { var headList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { FinanceBank data = new FinanceBank(); #region Set DB data to Object data.GID = Convert.ToString(reader["GID"]); data.F_NO = Convert.ToString(reader["F_NO"]); data.BANK = Convert.ToString(reader["BANK"]); if (reader["AMOUNT"] != DBNull.Value) data.AMOUNT = Convert.ToDecimal(reader["AMOUNT"]); if (reader["DOAMOUNT"] != DBNull.Value) data.DOAMOUNT = Convert.ToDecimal(reader["DOAMOUNT"]); #endregion headList.Add(data); } reader.Close(); } return headList; } public static DBResult SaveBank(string F_NO, List bodyList) { var result = new DBResult(); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { var cmdInsert = db.GetSqlStringCommand( @"insert into IMPORT_FINANCE_BANK (GID,F_NO,AMOUNT,DOAMOUNT,BANK) values (@GID,@F_NO,@AMOUNT,@DOAMOUNT,@BANK) "); var cmdUpdate = db.GetSqlStringCommand( @"update IMPORT_FINANCE_BANK set AMOUNT=@AMOUNT,DOAMOUNT=@DOAMOUNT,BANK=@BANK where GID=@GID "); if (bodyList != null) { foreach (var enumValue in bodyList) { if (enumValue.F_NO == "*" || enumValue.F_NO == "") { cmdInsert.Parameters.Clear(); db.AddInParameter(cmdInsert, "@GID", DbType.String, enumValue.GID); db.AddInParameter(cmdInsert, "@F_NO", DbType.String, F_NO); db.AddInParameter(cmdInsert, "@AMOUNT", DbType.Decimal, enumValue.AMOUNT); db.AddInParameter(cmdInsert, "@DOAMOUNT", DbType.Decimal, enumValue.DOAMOUNT); db.AddInParameter(cmdInsert, "@BANK", DbType.String, enumValue.BANK); db.ExecuteNonQuery(cmdInsert, tran); } else { cmdUpdate.Parameters.Clear(); db.AddInParameter(cmdUpdate, "@GID", DbType.String, enumValue.GID); db.AddInParameter(cmdUpdate, "@AMOUNT", DbType.Decimal, enumValue.AMOUNT); db.AddInParameter(cmdUpdate, "@DOAMOUNT", DbType.Decimal, enumValue.DOAMOUNT); db.AddInParameter(cmdUpdate, "@BANK", DbType.String, enumValue.BANK); db.ExecuteNonQuery(cmdUpdate, tran); } } } tran.Commit(); } catch (Exception) { tran.Rollback(); result.Success = false; result.Message = "保存出现错误，请重试或联系系统管理员"; return result; } } result.Success = true; result.Message = "保存成功" + result.Message; return result; } static public int CreateBankFee(string F_NO, List bodyList, string USERID) { int iResult = 0; using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction)) { try { if (bodyList != null) { foreach (var enumValue in bodyList) { SqlParameter[] validParms = new SqlParameter[] { new SqlParameter(PARM_FEE_GID,SqlDbType.VarChar,36) }; SqlParameter[] parms = GetInsertParms(); parms = GetInsertParms(); parms[0].Value = Guid.NewGuid().ToString(); parms[1].Value = F_NO; parms[2].Value = 2; parms[3].Value = "融资本金"; parms[4].Value = ""; parms[5].Value = ""; parms[6].Value = enumValue.BANK; parms[7].Value = "票"; parms[8].Value = enumValue.AMOUNT; parms[9].Value = 1; parms[10].Value = enumValue.AMOUNT; parms[11].Value ="USD"; parms[12].Value = 1; parms[13].Value = ""; parms[14].Value = ""; parms[15].Value = "0"; parms[16].Value = USERID; parms[17].Value = DateTime.Now; parms[18].Value = "0"; parms[19].Value = "0"; parms[20].Value = 1; parms[21].Value = 1; parms[22].Value = ""; parms[23].Value = 0; parms[24].Value = enumValue.AMOUNT; parms[25].Value = 0; parms[26].Value = 1; parms[27].Value = enumValue.AMOUNT; parms[28].Value = USERID; parms[29].Value = DateTime.Now.ToString(); parms[30].Value =enumValue.F_NO; parms[31].Value = 0; SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_INSERT_FEE, parms); } } sqlTran.Commit(); iResult = 1;//状态为1表示插入成功 } catch (Exception execError) { iResult = -1;//有异常,插入失败 sqlTran.Rollback(); iResult = -2;//插入异常,事务已回滚成功 throw execError; } finally { SqlHelper.CloseConnection(); } } return iResult; } static public int DeleteBank(string tempGId) { int iResult = 0; using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction)) { try { var strSql = new StringBuilder(); strSql.Append("Delete from IMPORT_FINANCE_BANK where "); strSql.Append(" GId in (" + tempGId + ")"); SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql.ToString()); sqlTran.Commit(); iResult = 1;//状态为1表示插入成功 } catch (Exception execError) { iResult = -1;//有异常,插入失败 sqlTran.Rollback(); iResult = -2;//插入异常,事务已回滚成功 throw execError; } finally { SqlHelper.CloseConnection(); } } return iResult; } #endregion #region 银行核销 static public List GetAddBankFinalList(string strCondition, string sort) { var strSql = new StringBuilder(); strSql.Append(" select f.F_NO,f.F_CURRENCY,f.F_Rate,f.F_RMB,f.F_AMOUNT,f.CUSTOMER,f.REMARK,f.OP,dbo.trimtime(f.ENTERDATE) ENTERDATE,dbo.trimdate(f.STARTDATE) STARTDATE,dbo.trimdate(f.ENDDATE) ENDDATE "); strSql.Append(" ,f.DAYS,b.AMOUNT BANK_INTEREST_RATE,b.DOAMOUNT CUST_INTEREST_RATE,b.AMOUNT-b.DOAMOUNT CUST_INTEREST,f.BANK_EXCHANGERATE,f.CUST_EXCHANGERATE "); strSql.Append(" ,b.GID MAINNO,f.F_STATUS,b.BANK FINANCE_BANK,M.[HTH],M.[seller],M.[company],M.[buyer], M.[BillNo],dbo.trimdate(BANKENDDATE) BANKENDDATE "); strSql.Append(" ,case M.[ArrivalDate] when '1900-01-01 00:00:00.000' then '' else CONVERT(varchar , M.[ArrivalDate],23) end as ArrivalDate "); strSql.Append(" ,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=10 and EnumValueID=M.port) as portRef "); strSql.Append(" ,(select Name from company where gid=M.company) as companyname "); strSql.Append(" ,(select showname from [user] where gid=f.OP) as OPREF "); strSql.Append(" ,CUSTSTLAMOUNT,F_RMB-CUSTSTLAMOUNT AS CUSTBALAMOUNT "); strSql.Append(" ,BANKSTLAMOUNT,F_AMOUNT-BANKSTLAMOUNT AS BANKBALAMOUNT "); strSql.Append(" ,(select sum(isnull(INTERESTUSD,0)) from IMPORT_FINANCE_BODY where BANKID=b.GID) as BANK_INTEREST "); strSql.Append(" ,dbo.f_financeBank(f.F_NO) BANKNAME "); strSql.Append(" ,case when isnull(CUSTSTLAMOUNT,0)=0 then '未还款' when isnull(CUSTSTLAMOUNT,0)>=F_RMB then '全部归还' "); strSql.Append(" when isnull(CUSTSTLAMOUNT,0)>0 and isnull(CUSTSTLAMOUNT,0)0 "); if (!string.IsNullOrEmpty(strCondition)) { strSql.Append(" and " + strCondition); } return SetDateList(strSql); } static public List GetBankStlDetailList(string strCondition, string sort) { var strSql = new StringBuilder(); strSql.Append(" select f.F_NO,f.F_CURRENCY,f.F_Rate,f.F_RMB,f.F_AMOUNT,f.CUSTOMER,f.REMARK,f.OP,dbo.trimtime(f.ENTERDATE) ENTERDATE,dbo.trimdate(f.STARTDATE) STARTDATE,dbo.trimdate(f.ENDDATE) ENDDATE "); strSql.Append(" ,f.DAYS,b.AMOUNT BANK_INTEREST_RATE,d.DOAMOUNT CUST_INTEREST_RATE,b.AMOUNT-b.DOAMOUNT CUST_INTEREST,f.BANK_EXCHANGERATE,f.CUST_EXCHANGERATE "); strSql.Append(" ,b.GID MAINNO,f.F_STATUS,b.BANK FINANCE_BANK,'' HTH,'' seller,'' company,'' buyer, d.GID BillNo,dbo.trimdate(f.BANKENDDATE) BANKENDDATE "); strSql.Append(" ,'' as ArrivalDate "); strSql.Append(" ,'' as portRef "); strSql.Append(" ,'' as companyname "); strSql.Append(" ,(select showname from [user] where gid=f.OP) as OPREF "); strSql.Append(" ,CUSTSTLAMOUNT,F_RMB-CUSTSTLAMOUNT AS CUSTBALAMOUNT "); strSql.Append(" ,BANKSTLAMOUNT,F_AMOUNT-BANKSTLAMOUNT AS BANKBALAMOUNT "); strSql.Append(" ,(select sum(isnull(INTERESTUSD,0)) from IMPORT_FINANCE_BODY where BANKID=b.GID) as BANK_INTEREST "); strSql.Append(" ,dbo.f_financeBank(f.F_NO) BANKNAME "); strSql.Append(" ,case when isnull(CUSTSTLAMOUNT,0)=0 then '未还款' when isnull(CUSTSTLAMOUNT,0)>=F_RMB then '全部归还' "); strSql.Append(" when isnull(CUSTSTLAMOUNT,0)>0 and isnull(CUSTSTLAMOUNT,0) bodylist) { var result = new DBResult(); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { var cmdInsert = db.GetSqlStringCommand( @"insert into Import_Finance_Bankdo (GID,F_NO,BANKID,DOAMOUNT) values (@GID,@F_NO,@BANKID,@DOAMOUNT) "); var cmdUpdateM = db.GetSqlStringCommand( @"UPDATE Import_Finance_Bank SET DOAMOUNT=AMOUNT WHERE GID=@GID "); var cmdUpdate = db.GetSqlStringCommand( @"UPDATE IMPORT_FINANCE_BODY SET F_AMOUNT=F_AMOUNT+@AMOUNT WHERE F_NO=@F_NO "); decimal amount = 0; var F_NO = ""; if (bodylist != null) { foreach (var enumValue in bodylist) { cmdInsert.Parameters.Clear(); db.AddInParameter(cmdInsert, "@GID", DbType.String, Guid.NewGuid().ToString()); db.AddInParameter(cmdInsert, "@F_NO", DbType.String, billno); db.AddInParameter(cmdInsert, "@BANKID", DbType.String, enumValue.MAINNO); db.AddInParameter(cmdInsert, "@DOAMOUNT", DbType.Decimal, enumValue.CUST_INTEREST); db.ExecuteNonQuery(cmdInsert, tran); amount = amount + Convert.ToDecimal(enumValue.CUST_INTEREST); cmdUpdateM.Parameters.Clear(); db.AddInParameter(cmdUpdateM, "@GID", DbType.String, enumValue.MAINNO); db.ExecuteNonQuery(cmdUpdateM, tran); F_NO = enumValue.F_NO; } } cmdUpdate.Parameters.Clear(); db.AddInParameter(cmdUpdate, "@F_NO", DbType.String, billno); db.AddInParameter(cmdUpdate, "@AMOUNT", DbType.Decimal, amount); db.ExecuteNonQuery(cmdUpdate, tran); result = new DBResult(); result.Success = true; result.Message = "成功"; tran.Commit(); } catch (Exception) { tran.Rollback(); result.Success = false; result.Message = "保存出现错误，请重试或联系系统管理员"; return result; } } result.Success = true; result.Message = "保存成功"; return result; } public static DBResult DelBankStlBill(string billno, List bodylist) { var result = new DBResult(); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { var cmdInsert = db.GetSqlStringCommand( @"delete from Import_Finance_Bankdo where GID=@GID "); var cmdUpdate = db.GetSqlStringCommand( @"UPDATE IMPORT_FINANCE_BODY SET F_AMOUNT=(select sum(F_AMOUNT) from Import_Finance_Bankdo where ) "); var cmdUpdateM = db.GetSqlStringCommand( @"UPDATE Import_Finance_Bank SET DOAMOUNT=0 WHERE GID=@GID "); decimal amount = 0; var F_NO = ""; if (bodylist != null) { foreach (var enumValue in bodylist) { cmdInsert.Parameters.Clear(); db.AddInParameter(cmdInsert, "@GID", DbType.String, enumValue); db.ExecuteNonQuery(cmdInsert, tran); amount = amount + Convert.ToDecimal(enumValue.CUST_INTEREST_RATE); cmdUpdateM.Parameters.Clear(); db.AddInParameter(cmdUpdateM, "@GID", DbType.String, enumValue.MAINNO); db.ExecuteNonQuery(cmdUpdateM, tran); } } cmdUpdate.Parameters.Clear(); db.AddInParameter(cmdUpdate, "@F_NO", DbType.String, billno); db.AddInParameter(cmdUpdate, "@F_AMOUNT", DbType.Decimal, amount); db.ExecuteNonQuery(cmdUpdate, tran); result = new DBResult(); result.Success = true; result.Message = "成功"; tran.Commit(); } catch (Exception) { tran.Rollback(); result.Success = false; result.Message = "保存出现错误，请重试或联系系统管理员"; return result; } } result.Success = true; result.Message = "保存成功"; return result; } public static DBResult SubmitBankStl(FinanceBodymb head, List bodyList, string USERID) { var result = new DBResult(); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { var cmdDelete = db.GetSqlStringCommand( @"delete from ch_fee where FEETYPE=2 AND FEESTATUS not in (8,9) AND f_no=@f_no "); var cmdUpdate = db.GetSqlStringCommand( @"update IMPORT_FINANCE_BANK set BANKINTEREST=@INTEREST where GID=@GID "); var cmdUpdateBody = db.GetSqlStringCommand( @"update IMPORT_FINANCE_BODY set EXCHANGERATE=@EXCHANGERATE,RMBVALUE=@RMBVALUE,HANDFEE=@HANDFEE,INTEREST=@INTEREST where GID=@GID "); var cmdUpdateBodyM = db.GetSqlStringCommand( @"update IMPORT_FINANCE_BODY set ISFEE=1 where GID=@GID "); if (bodyList != null) { foreach (var enumValue in bodyList) { if (Convert.ToDecimal(enumValue.CUST_INTEREST_RATE) != 0 && head.INTERESTUSD != 0) { var bankinterest = head.INTERESTUSD * (head.F_AMOUNT / Convert.ToDecimal(enumValue.CUST_INTEREST_RATE)); if (bankinterest != 0) { cmdUpdate.Parameters.Clear(); db.AddInParameter(cmdUpdate, "@GID", DbType.String, enumValue.MAINNO); db.AddInParameter(cmdUpdate, "@INTEREST", DbType.Decimal, bankinterest); db.ExecuteNonQuery(cmdUpdate, tran); } var bankbody = GetFinanceList("BANKID='" + enumValue.MAINNO + "'", ""); if (bankbody != null) { foreach (var financebody in bankbody) { if (financebody.EXCHANGERATE!=head.EXCHANGERATE) { cmdUpdateBody.Parameters.Clear(); db.AddInParameter(cmdUpdateBody, "@GID", DbType.String, financebody.GID); db.AddInParameter(cmdUpdateBody, "@EXCHANGERATE", DbType.Decimal, head.EXCHANGERATE); db.AddInParameter(cmdUpdateBody, "@RMBVALUE", DbType.Decimal, financebody.F_AMOUNT*head.EXCHANGERATE); financebody.RMBVALUE = financebody.F_AMOUNT * head.EXCHANGERATE; var handfee = (financebody.F_AMOUNT * head.EXCHANGERATE) *Convert.ToDecimal(0.001); financebody.HANDFEE = handfee; db.AddInParameter(cmdUpdateBody, "@HANDFEE", DbType.Decimal, handfee); financebody.INTEREST = (financebody.F_AMOUNT * head.EXCHANGERATE + handfee + financebody.TELXFEE + financebody.OTFEE) * (financebody.INTEREST_RATE / 30) * financebody.DAYS; db.AddInParameter(cmdUpdateBody, "@INTEREST", DbType.Decimal, financebody.INTEREST); db.ExecuteNonQuery(cmdUpdateBody, tran); } if (financebody.ISFEE != "1" || financebody.EXCHANGERATE != head.EXCHANGERATE) { cmdDelete.Parameters.Clear(); db.AddInParameter(cmdDelete, "@f_no", DbType.String, financebody.GID); db.ExecuteNonQuery(cmdDelete, tran); CreateBodyFeeFn(financebody.F_NO, financebody, USERID,"true"); } } } } } } cmdUpdateBodyM.Parameters.Clear(); db.AddInParameter(cmdUpdateBodyM, "@GID", DbType.String, head.GID); db.ExecuteNonQuery(cmdUpdateBodyM, tran); tran.Commit(); } catch (Exception) { tran.Rollback(); result.Success = false; result.Message = "保存出现错误，请重试或联系系统管理员"; return result; } } result.Success = true; result.Message = "保存成功" + result.Message; return result; } public static DBResult SubmitBackBankStl(FinanceBodymb head) { var result = new DBResult(); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { var cmdUpdateBodyM = db.GetSqlStringCommand( @"update IMPORT_FINANCE_BODY set ISFEE=0 where GID=@GID "); cmdUpdateBodyM.Parameters.Clear(); db.AddInParameter(cmdUpdateBodyM, "@GID", DbType.String, head.GID); db.ExecuteNonQuery(cmdUpdateBodyM, tran); tran.Commit(); } catch (Exception) { tran.Rollback(); result.Success = false; result.Message = "保存出现错误，请重试或联系系统管理员"; return result; } } result.Success = true; result.Message = "保存成功" + result.Message; return result; } #endregion #region 判断是否有费用 public static bool GetFeeCount(string BSNO) { var isfee = false; var strSql = new StringBuilder(); strSql.Append("Select count(*) as count "); strSql.Append(" from ch_fee "); strSql.Append(" where BSNO='" + BSNO + "'"); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { var evData = Convert.ToInt32(reader["count"]); if (evData > 0) { isfee = true; }; } reader.Close(); } return isfee; } public static DBResult DeleteDetail(string bsno) { var result = new DBResult(); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { var cmdDelete = db.GetSqlStringCommand("delete from IMPORT_FINANCE_BODY where F_NO='" + bsno + "'"); db.ExecuteNonQuery(cmdDelete, tran); var cmdDelete2 = db.GetSqlStringCommand("delete from Import_Finance_Bank where F_NO='" + bsno + "'"); db.ExecuteNonQuery(cmdDelete2, tran); tran.Commit(); } catch (Exception) { tran.Rollback(); result.Success = false; result.Message = "删除出现错误，请重试或联系系统管理员"; return result; } } result.Success = true; result.Message = "操作成功"; return result; } public static DBResult CancelFinancel(string bsno) { var result = new DBResult(); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { var cmdDelete = db.GetSqlStringCommand("update Import_main set FinanceStatus='0' where contractno='" + bsno + "'"); db.ExecuteNonQuery(cmdDelete, tran); tran.Commit(); } catch (Exception) { tran.Rollback(); result.Success = false; result.Message = "删除出现错误，请重试或联系系统管理员"; return result; } } result.Success = true; result.Message = "操作成功"; return result; } public static DBResult AuditFinancelStatus(string F_NO) { var result = new DBResult(); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { var cmdDelete = db.GetSqlStringCommand(" update Import_main set FinanceStatus='2' where contractno in (select CONTRACTNO from Import_Finance_do where F_NO='"+F_NO+"')" + " update ch_fee set feestatus=11 where F_NO=='" + F_NO + "'"); db.ExecuteNonQuery(cmdDelete, tran); tran.Commit(); } catch (Exception) { tran.Rollback(); result.Success = false; result.Message = "删除出现错误，请重试或联系系统管理员"; return result; } } result.Success = true; result.Message = "操作成功"; return result; } static public int SaveFinanceFee(Financemb Finance, string tempUserID) { int iResult = 0; using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction)) { try { SqlParameter[] validParms = new SqlParameter[] { new SqlParameter(PARM_FEE_GID,SqlDbType.VarChar,36) }; SqlParameter[] parms = GetInsertParms(); parms[0].Value = Guid.NewGuid().ToString(); parms[1].Value = Finance.F_NO; parms[2].Value =1; parms[3].Value ="融资本金"; parms[4].Value =""; parms[5].Value =""; parms[6].Value = Finance.CUSTOMER; parms[7].Value ="票"; parms[8].Value = Finance.F_AMOUNT; parms[9].Value = 1; parms[10].Value = Finance.F_AMOUNT; parms[11].Value = Finance.F_CURRENCY; parms[12].Value = 1; parms[13].Value =""; parms[14].Value = ""; parms[15].Value ="0"; parms[16].Value = tempUserID; parms[17].Value = DateTime.Now; parms[18].Value = "0"; parms[19].Value = "0"; parms[20].Value = 1; parms[21].Value =1; parms[22].Value =""; parms[23].Value =0; parms[24].Value = Finance.F_AMOUNT; parms[25].Value =0; parms[26].Value = 1; parms[27].Value = Finance.F_AMOUNT; parms[28].Value = tempUserID; parms[29].Value = DateTime.Now.ToString(); parms[30].Value = Finance.F_NO; parms[31].Value = 0; SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_INSERT_FEE, parms); sqlTran.Commit(); iResult = 1;//状态为1表示插入成功 } catch (Exception execError) { iResult = -1;//有异常,插入失败 sqlTran.Rollback(); iResult = -2;//插入异常,事务已回滚成功 throw execError; } finally { SqlHelper.CloseConnection(); } } return iResult; } #endregion public static DBResult updateFinancel (string bsno) { var result = new DBResult(); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { var cmdDelete = db.GetSqlStringCommand("update Import_main set FinanceStatus='1' where contractno='" + bsno + "'"); db.ExecuteNonQuery(cmdDelete, tran); tran.Commit(); } catch (Exception) { tran.Rollback(); result.Success = false; result.Message = "删除出现错误，请重试或联系系统管理员"; return result; } } result.Success = true; result.Message = "操作成功"; return result; } public static int p_update_Finance(string billno) { Database db = DatabaseFactory.CreateDatabase(); var cmd = db.GetStoredProcCommand("p_update_Finance"); db.AddInParameter(cmd, "@con_no", DbType.String, billno); db.ExecuteNonQuery(cmd); return 0; } static public int ChangeStatus(string ContractNo, string status) { var strSql = new StringBuilder(); strSql.Append(" update ch_fee set feestatus="+status+" where feetype=1 and feestatus<>8 and feestatus<>9 and bsno='" + ContractNo + "'"); return setStatus(strSql);//ExecuteNonQuery } private static int setStatus(StringBuilder strSql) { var _count = 0; Database db = DatabaseFactory.CreateDatabase(); _count = db.ExecuteNonQuery(CommandType.Text, strSql.ToString()); return _count; } public static string GetRangAddFeeDAStr(string userid, string username, string companyid) { string str = ""; string modustr = "MODFINANCEPAYAPPFEE"; var strSql = new StringBuilder(); strSql.Append("SELECT "); strSql.Append(" VISIBLERANGE,OPERATERANGE,AUTHORITYID "); strSql.Append(" from VW_User_Authority "); strSql.Append(" where [NAME]='" + modustr + "' and USERID='" + userid + "' and ISDELETE=0"); string visiblerange = "4"; string operaterange = "4"; string AUTHORITYID = ""; Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { visiblerange = Convert.ToString(reader["VISIBLERANGE"]); operaterange = Convert.ToString(reader["OPERATERANGE"]); AUTHORITYID = Convert.ToString(reader["AUTHORITYID"]); break; } reader.Close(); } if (visiblerange == "4") { str = " c.ENTEROPERATOR='" + userid + "'"; } else if (visiblerange == "3") { str = " c.ENTEROPERATOR='" + userid + "'"; } else if (visiblerange == "2") { var rangeDa = new RangeDA(); var deptname = rangeDa.GetDEPTNAME(userid); str = " c.ENTEROPERATOR in (select USERID from user_company where COMPANYID='" + companyid + "') and c.ENTEROPERATOR in (select userid from user_baseinfo where DEPTNAME='" + deptname + "')"; } else if (visiblerange == "1") { str = " c.ENTEROPERATOR in (select USERID from user_company where COMPANYID='" + companyid + "') "; } else if (visiblerange == "5") { var userstr = new StringBuilder(); userstr.Append(" select USERID FROM user_company WHERE COMPANYID IN (select COMPANYID from user_authority_range_company where userid='" + userid + "' and AUTHORITYID='" + AUTHORITYID + "' and VISIBLERANGE=1)"); Database userdb = DatabaseFactory.CreateDatabase(); using (IDataReader reader = userdb.ExecuteReader(CommandType.Text, userstr.ToString())) { str = ""; while (reader.Read()) { if (str == "") { str = " (c.ENTEROPERATOR='" + Convert.ToString(reader["USERID"]) + "'"; } else { str = str + " or c.ENTEROPERATOR='" + Convert.ToString(reader["USERID"]) + "'"; }; } str = str + ")"; reader.Close(); } } else if (visiblerange == "6") { var userstr = new StringBuilder(); userstr.Append(" select OPID,(select SHOWNAME from [user] where GID=user_authority_range_op.OPID) SHOWNAME from user_authority_range_op where userid='" + userid + "' and AUTHORITYID='" + AUTHORITYID + "' and VISIBLERANGE=1"); Database userdb = DatabaseFactory.CreateDatabase(); using (IDataReader reader = userdb.ExecuteReader(CommandType.Text, userstr.ToString())) { str = ""; while (reader.Read()) { if (str == "") { str = " (c.ENTEROPERATOR='" + Convert.ToString(reader["OPID"]) + "' "; } else { str = str + " or c.ENTEROPERATOR='" + Convert.ToString(reader["OPID"]) + "' "; }; } str = str + ")"; reader.Close(); } } else if (visiblerange == "0") { str = " 1=1 "; } return str; } #region 参照部分 #endregion } }