using System; using System.Data; using System.Collections; using System.Collections.Generic; using System.Text; using DSWeb.Areas.Account.Models.Chfee_management; using Microsoft.Practices.EnterpriseLibrary.Data; using HcUtility.Comm; using DSWeb.Areas.CommMng.Models; using DSWeb.Areas.Account.Models.MsCwVouchersGl3; using DSWeb.MvcShipping.Models.MsCwVouchersGl; using DSWeb.MvcShipping.Models.MsCwVouitemsGl; using DSWeb.MvcShipping.DAL.MsBaseInfoDAL; using DSWeb.Areas.CommMng.DAL; using DSWeb.EntityDA; using HcUtility.Core; using DSWeb.MvcShipping.DAL.MsSysParamSet; namespace DSWeb.Areas.Account.DAL.Chfee_management { public partial class Chfee_managementDAL { #region 管理费用列表 static public List GetDataList(string strCondition, string USERID, string sort = null) { //管理费用列表 string strCwSTARTGID = BasicDataRefDAL.GetCwSTARTGID(USERID); var strSql = new StringBuilder(); strSql.Append(" SELECT Feeid,FEESTATUS,FEETYPE,AMOUNT,dbo.trimdate(FEEDATE) FEEDATE "); strSql.Append(" ,dbo.trimdate(CREATEDATE) CREATEDATE,CREATOR,VOUCHERNO,case cm.ISVOU when 1 then '是' else '否' end as ISVOU,dbo.trimdate(AUDITDATE) AUDITDATE,AUDITOR "); strSql.Append(" ,(select showname from [user] where codename=CREATOR) CREATOR_REF,(select showname from [user] where codename=AUDITOR) AUDITOR_REF"); strSql.Append(" ,OPERATORSIDE,BILLINGDATE,REMARK,cm.currency,cm.exchangerate "); strSql.Append(" ,convert(bigint ,cm.TimeMark) as TimeMark,ACCDATE "); strSql.Append(" ,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99024 and EnumValueID=cm.FeeStatus) as FeeStatus_Ref "); strSql.Append(" ,(select ACCNAME from cw_accitems where accid=cm.OPERATORSIDE) as OPERATORSIDE_Ref "); strSql.Append(" ,case FEETYPE when 1 then '收入' else '支出' end as FeeType_Ref"); strSql.Append(" ,OPERATORSIDE_gl,(select top 1 accname from cw_accitems_gl where accid=OPERATORSIDE_gl and startgid=(select startgid from [cw_design_startusing_user] where usergid='" + USERID + "')) OPERATORSIDE_gl_REF"); strSql.Append(" ,VOUALLNO=(select top 1 VOUALLNO from cw_vouno_bs_gl WITH(NOLOCK) where BSGID=cm.Feeid and BSTABLENAME='ch_fee_management' and STARTGID='" + strCwSTARTGID + "')"); strSql.Append(" FROM ch_fee_management cm where cm.ISDELETE<>1 "); if (!string.IsNullOrEmpty(strCondition)) { strSql.Append(" and " + strCondition); } var sortstring = DatasetSort.Getsortstring(sort); if (!string.IsNullOrEmpty(sortstring)) { strSql.Append(" order by " + sortstring); } else { strSql.Append(" order by CREATEDATE DESC"); } return SetData(strSql); } static public C_Mmb GetData(string condition,string USERID) { var list = GetDataList(condition,USERID); if (list.Count > 0) return list[0]; return new C_Mmb(); } private static List SetData(StringBuilder strSql) { var headList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { C_Mmb data = new C_Mmb(); #region Set DB data to Object data.Feeid = Convert.ToString(reader["Feeid"]); data.FEESTATUS = Convert.ToString(reader["FEESTATUS"]); data.FEETYPE = Convert.ToString(reader["FEETYPE"]); data.AMOUNT = Convert.ToString(reader["AMOUNT"]); data.FEEDATE = Convert.ToString(reader["FEEDATE"]); data.CREATEDATE = Convert.ToString(reader["CREATEDATE"]); data.CREATOR = Convert.ToString(reader["CREATOR"]); data.CREATOR_REF = Convert.ToString(reader["CREATOR_REF"]); data.VOUCHERNO = Convert.ToString(reader["VOUCHERNO"]); data.ISVOU = Convert.ToString(reader["ISVOU"]); data.AUDITDATE = Convert.ToString(reader["AUDITDATE"]); data.AUDITOR = Convert.ToString(reader["AUDITOR"]); data.AUDITOR_REF = Convert.ToString(reader["AUDITOR_REF"]); data.OPERATORSIDE = Convert.ToString(reader["OPERATORSIDE"]); data.OPERATORSIDE_Ref = Convert.ToString(reader["OPERATORSIDE_Ref"]); data.REMARK = Convert.ToString(reader["REMARK"]); data.TimeMark = Convert.ToDecimal(reader["TimeMark"]); data.FeeStatus_Ref = Convert.ToString(reader["FeeStatus_Ref"]); data.FeeType_Ref = Convert.ToString(reader["FeeType_Ref"]); data.CURRENCY = Convert.ToString(reader["CURRENCY"]); data.EXCHANGERATE = Convert.ToString(reader["EXCHANGERATE"]); data.ACCDATE = Convert.ToString(reader["ACCDATE"]); data.OPERATORSIDE_gl = Convert.ToString(reader["OPERATORSIDE_gl"]); data.OPERATORSIDE_gl_REF = Convert.ToString(reader["OPERATORSIDE_gl_REF"]); data.VOUALLNO = Convert.ToString(reader["VOUALLNO"]); #endregion headList.Add(data); } reader.Close(); } return headList; } #endregion #region 管理费用明细 static public List GetBodyList(string strCondition,string USERID) { string strCwSTARTGID = BasicDataRefDAL.GetCwSTARTGID(USERID); var strSql = new StringBuilder(); strSql.Append(" SELECT * "); strSql.Append(" ,(select top 1 accname from cw_accitems_gl where accid=OPERATORSIDE_gl and startgid=(select startgid from [cw_design_startusing_user] where usergid='" + USERID + "')) OPERATORSIDE_gl_REF"); strSql.Append(" ,(select ACCNAME from cw_accitems where accid=cfm.feename) as Feenameref "); strSql.Append(" ,(select voucherno from ch_fee_management where Feeid=cfm.Feeid) as Voucherno "); strSql.Append(" ,(select deptname from sys_dept where gid=cfm.deptgid) as deptname "); strSql.Append(" ,VOUALLNO=(select top 1 VOUALLNO from cw_vouno_bs_gl WITH(NOLOCK) where BSGID=cfm.Feeid and BSTABLENAME='ch_fee_management' and STARTGID='" + strCwSTARTGID + "')"); strSql.Append(" FROM ch_fee_Mbody cfm "); if (!string.IsNullOrEmpty(strCondition)) { strSql.Append(" where " + strCondition); } return BodySetData(strSql); } private static List BodySetData(StringBuilder strSql) { var headList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { C_MBodymb data = new C_MBodymb(); #region Set DB data to Object data.gid = Convert.ToString(reader["gid"]); data.Feeid = Convert.ToString(reader["Feeid"]); data.CUSTOMERSIDE = Convert.ToString(reader["CUSTOMERSIDE"]); data.FEENAME = Convert.ToString(reader["FEENAME"]); data.AMOUNT = Convert.ToString(reader["AMOUNT"]); data.REMARK = Convert.ToString(reader["REMARK"]); data.FEENAMEREF = Convert.ToString(reader["FEENAMEREF"]); data.DEPTGID = Convert.ToString(reader["DEPTGID"]); data.SALE = Convert.ToString(reader["SALE"]); data.DEPTNAME = Convert.ToString(reader["DEPTNAME"]); data.VOUCHERNO = Convert.ToString(reader["VOUCHERNO"]); data.CUSTBANK = Convert.ToString(reader["CUSTBANK"]); data.CUSTACCOUNT = Convert.ToString(reader["CUSTACCOUNT"]); data.OPERATORSIDE_gl = Convert.ToString(reader["OPERATORSIDE_gl"]); data.OPERATORSIDE_gl_REF = Convert.ToString(reader["OPERATORSIDE_gl_REF"]); data.VOUALLNO = Convert.ToString(reader["VOUALLNO"]); #endregion headList.Add(data); } reader.Close(); } return headList; } #endregion #region 费用列表 static public List GetBodyList_Index ( string strCondition, string USERID ) { string strCwSTARTGID = BasicDataRefDAL.GetCwSTARTGID(USERID); var strSql = new StringBuilder(); strSql.Append(" SELECT * "); strSql.Append(" ,(select top 1 accname from cw_accitems_gl where accid=cfm.OPERATORSIDE_gl and startgid=(select startgid from [cw_design_startusing_user] where usergid='" + USERID + "')) OPERATORSIDE_gl_REF"); strSql.Append(" ,(select ACCNAME from cw_accitems where accid=cfm.feename) as Feenameref "); strSql.Append(" ,(select voucherno from ch_fee_management where Feeid=cfm.Feeid) as Voucherno "); strSql.Append(" ,(select deptname from sys_dept where gid=cfm.deptgid) as deptname "); strSql.Append(" ,m.CURRENCY,case M.CURRENCY when 'RMB' then cfm.amount else 0 end AMOUNTRMB "); strSql.Append(" ,case M.CURRENCY when 'USD' then cfm.amount else 0 end AMOUNTUSD "); strSql.Append(" ,case when M.CURRENCY not in('RMB','USD') then cfm.amount else 0 end AMOUNTOTHER "); strSql.Append(" ,VOUALLNO=(select top 1 VOUALLNO from cw_vouno_bs_gl WITH(NOLOCK) where BSGID=cfm.Feeid and BSTABLENAME='ch_fee_management' and STARTGID='" + strCwSTARTGID + "')"); strSql.Append(" FROM ch_fee_Mbody cfm left join ch_fee_management m on m.FEEID=cfm.FEEID "); if (!string.IsNullOrEmpty(strCondition)) { strSql.Append(" where " + strCondition); } return BodySetData_Index(strSql); } private static List BodySetData_Index ( StringBuilder strSql ) { var headList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { C_MBodymb_2 data = new C_MBodymb_2(); #region Set DB data to Object data.gid = Convert.ToString(reader["gid"]); data.Feeid = Convert.ToString(reader["Feeid"]); data.CUSTOMERSIDE = Convert.ToString(reader["CUSTOMERSIDE"]); data.FEENAME = Convert.ToString(reader["FEENAME"]); data.AMOUNT = Convert.ToString(reader["AMOUNT"]); data.REMARK = Convert.ToString(reader["REMARK"]); data.FEENAMEREF = Convert.ToString(reader["FEENAMEREF"]); data.DEPTGID = Convert.ToString(reader["DEPTGID"]); data.DEPTNAME = Convert.ToString(reader["DEPTNAME"]); data.SALE = Convert.ToString(reader["SALE"]); data.VOUCHERNO = Convert.ToString(reader["VOUCHERNO"]); data.CUSTBANK = Convert.ToString(reader["CUSTBANK"]); data.CUSTACCOUNT = Convert.ToString(reader["CUSTACCOUNT"]); data.OPERATORSIDE_gl = Convert.ToString(reader["OPERATORSIDE_gl"]); data.OPERATORSIDE_gl_REF = Convert.ToString(reader["OPERATORSIDE_gl_REF"]); data.CURRENCY = Convert.ToString(reader["CURRENCY"]); data.AMOUNTRMB = Convert.ToString(reader["AMOUNTRMB"]); data.AMOUNTUSD = Convert.ToString(reader["AMOUNTUSD"]); data.AMOUNTOTHER = Convert.ToString(reader["AMOUNTOTHER"]); data.VOUALLNO = Convert.ToString(reader["VOUALLNO"]); #endregion headList.Add(data); } reader.Close(); } return headList; } #endregion #region 删除 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 ch_fee_Mbody where Feeid='" + 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; } #endregion #region 生成总账凭证 #region 生成汇率列表 static public List GetCodeCurrencyList(string strGids, string companyid, string strUserID) { string strCwSTARTGID = BasicDataRefDAL.GetCwSTARTGID(strUserID); string strCwACCDATE = BasicDataRefDAL.GetCwACCDATE(strUserID); var strSql = new StringBuilder(); strSql.Append("select CURRENCY,(select top 1 BEGRATE from cw_currency_rate where ACCDATE='" + strCwACCDATE + "' and STARTGID='" + strCwSTARTGID + "' and LINKGID in (select GID from code_currency where CODENAME=VW_ch_fee_management_mbody.CURRENCY)) as FCYEXRATE from VW_ch_fee_management_mbody where CURRENCY<>'RMB'"); if (!string.IsNullOrEmpty(strGids)) { string strCondition = " and Feeid in ('" + strGids.Trim().Replace(",", "','") + "')"; strSql.Append(strCondition); } else { strSql.Append(" and 1<0"); } strSql.Append(" group by CURRENCY order by CURRENCY"); return SetCodeCurrencyData(strSql); } private static List SetCodeCurrencyData(StringBuilder strSql) { var headList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { MsCwVouchersGl4 data = new MsCwVouchersGl4(); #region Set DB data to Object data.CURR = (reader["CURRENCY"] == null ? "" : Convert.ToString(reader["CURRENCY"])); data.FCYEXRATE = (reader["FCYEXRATE"] == null ? "" : Convert.ToString(reader["FCYEXRATE"])); #endregion headList.Add(data); } reader.Close(); } return headList; } #endregion #region 生成总账凭证 public static DBResult onAddCwVouchers(string strGids, MsCwVouchersGl3 headData, List bodyList, string strUserID, string strSHOWNAME, string companyid) { Database db = DatabaseFactory.CreateDatabase(); var result = new DBResult(); T_ALL_DA T_ALL_DA = new EntityDA.T_ALL_DA(); string sSQL = ""; int iResult = 0; string zt = ""; string sError = "操作完成！"; string strError = ""; string strCwSTARTGID = BasicDataRefDAL.GetCwSTARTGID(strUserID); var CWSTARTUSING = MsSysParamSetDAL.GetData("PARAMNAME='CWSTARTUSING'"); if (CWSTARTUSING.PARAMVALUE != "") { if (strCwSTARTGID != CWSTARTUSING.PARAMVALUE) { result.Success = false; result.Message = "当前账套不是设置的账套,不能生成凭证!"; return result; } } string strCwACCDATE = BasicDataRefDAL.GetCwACCDATE(strUserID); string sNum = ""; string strCondition = " and Feeid not in (select [BSGID] from [cw_vouno_bs_gl] WITH(NOLOCK) where BSTABLENAME='ch_fee_management' and STARTGID='" + strCwSTARTGID + "')"; string sAndGids = " and Feeid in ('" + strGids.Trim().Replace(",", "','") + "')"; #region 生成前的判断 //帐套设置 sSQL = "SELECT top 1 * from cw_design_gl WITH(NOLOCK) where [STARTGID]='" + strCwSTARTGID + "'";// and CORPID='"+companyid+"' DataSet dsCwDesign = T_ALL_DA.GetAllSQL(sSQL); if (dsCwDesign == null) { result.Success = false; result.Message = "请先设置帐套信息!" + result.Message; return result; } //帐套设置_核算科目 sSQL = "SELECT [GID],[LINKGID],[ITEMGID],[OPTYPE],[MODIFIEDUSER],[MODIFIEDTIME],[MODIFIEDUSERNAME]=(select top 1 SHOWNAME from [user] where gid=[cw_design_gl_item].MODIFIEDUSER),[ITEMCODE]=(SELECT top 1 ITEMCODE from cw_item where GID=[cw_design_gl_item].[ITEMGID]),[ITEMNAME]=(SELECT top 1 ITEMNAME from cw_item where GID=[cw_design_gl_item].[ITEMGID]) FROM [cw_design_gl_item] WITH(NOLOCK) where LINKGID in (SELECT top 1 GID from cw_design_gl WITH(NOLOCK) where [STARTGID]='" + strCwSTARTGID + "') ORDER BY [ITEMCODE]"; DataSet dsCwDesignItem = T_ALL_DA.GetAllSQL(sSQL); #endregion string[] items = strGids.Trim().Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries); int n = 0; if (items.Length > 0) { for (int i = 0; i < items.Length; i++) { sAndGids = " and Feeid in ('" + items[i].ToString().Trim().Replace(",", "','") + "')"; DBResult result2 = GetIsVoucher(items[i].ToString().Trim(), strUserID); if (!result2.Success) { strError = result2.Message; } else { #region 判断收支科目是否存在当前账套中 string blSQL = "select count(*) as iNum from VW_ch_fee_management_mbody WITH(NOLOCK) where OPERATORSIDE not in (select accid from cw_accitems_gl where [STARTGID]='" + strCwSTARTGID + "' and [YEAR]='" + strCwACCDATE.Substring(0, 4) + "' and DETAILED=1)" + sAndGids; int iNum = int.Parse(T_ALL_DA.GetStrSQL("iNum", blSQL)); if (iNum > 0) { strError = "收支科目不存在当前账套中，请重新选择!"; continue; } #endregion #region 判断贷方科目是否存在当前账套中 blSQL = "select count(*) as iNum from VW_ch_fee_management_mbody WITH(NOLOCK) where OPERATORSIDE_gl not in (select accid from cw_accitems_gl where [STARTGID]='" + strCwSTARTGID + "' and [YEAR]='" + strCwACCDATE.Substring(0, 4) + "' and DETAILED=1)" + sAndGids; iNum = int.Parse(T_ALL_DA.GetStrSQL("iNum", blSQL)); if (iNum > 0) { strError = "录入的科目不存在当前账套中，请重新选择!"; continue; } #endregion #region 提前生成凭证号 string sACCDATE = headData.VOUDATE.Substring(0, 7); if (DateTime.Parse(sACCDATE + "-01") < DateTime.Parse(strCwACCDATE + "-01")) { sACCDATE = strCwACCDATE; } string strBPVW = dsCwDesign.Tables[0].Rows[0]["TVW"].ToString().Trim(); string strVOUNOhj = DSWeb.MvcShipping.DAL.MsBaseInfoDAL.MsBaseInfoDAL.GetCwVOUNO(strBPVW, sACCDATE, strUserID); string[] items2 = strVOUNOhj.Trim().Split(new string[] { "~" }, StringSplitOptions.RemoveEmptyEntries); string strVOUNO = items2[3].ToString().Trim(); string sACCMONTH = DateTime.Parse(headData.VOUDATE.ToString()).Month.ToString();//月 if (sACCMONTH.ToString().Length == 1) { sACCMONTH = "0" + sACCMONTH.ToString();//月 } string cfVOUALLNO = strBPVW + DateTime.Parse(headData.VOUDATE.ToString()).Year.ToString() + sACCMONTH + strVOUNO;//年+月+自动生成编号=凭证号 #endregion #region 把凭证号和业务GID插入到凭证生成的关系表 blSQL = "INSERT INTO [cw_vouno_bs_gl]([GID],[BSTABLENAME],[BSGID],[VOUALLNO],[ORDNO],[STARTGID],[CREATEUSER],[CREATETIME])" + "select newid() as [GID],'ch_fee_management' as [BSTABLENAME],Feeid as [BSGID],'" + cfVOUALLNO + "' as [VOUALLNO],'' as [ORDNO],'" + strCwSTARTGID + "' as [STARTGID],'" + strUserID + "' as [CREATEUSER],getdate() as [CREATETIME]" + " from ch_fee_management WITH(NOLOCK) where 1=1" + sAndGids + " and Feeid not in (select BSGID from cw_vouno_bs_gl WITH(NOLOCK) where BSTABLENAME='ch_fee_management' and [STARTGID]='" + strCwSTARTGID + "')"; #endregion int iCount = db.ExecuteNonQuery(CommandType.Text, blSQL); if (iCount != 0) { iResult = getSC(dsCwDesign, null, sAndGids, headData, bodyList, strCwACCDATE, strUserID, companyid, cfVOUALLNO, strSHOWNAME); zt += "," + iResult; if (zt.IndexOf("-") < 0) { n++; } } else { strError = "有已经生成凭证的数据，不允许重复生成，请重新选择!"; continue; } } } } if (strError.Trim() != "") { sError = strError.Trim()+"
"; } sError = "共" + items.Length + "票单据，生成" + n + "条凭证，失败" + (items.Length - n) + "条凭证！"; result.Success = true; result.Message = sError;//"操作完成！"; return result; } /// /// 生成凭证 /// /// 帐套设置数据集 /// gid业务编号 /// protected static int getSC(DataSet dsCwDesign, DataSet dsCwDesignItem, string sAndGids, MsCwVouchersGl3 headData, List bodyList, string strACCDATE, string strUserID, string strCompanyID, string cfVOUALLNO, string strSHOWNAME) { var result = new DBResult(); #region 基本变量 //全部 string sSQL = ""; string strEXPLAN = ""; string strFCY = "RMB"; string strIsExplan2 = ""; Decimal dEXCHANGERATE = 0; //总合计 Decimal strJie = 0; Decimal strDai = 0; string strBPVW = dsCwDesign.Tables[0].Rows[0]["TVW"].ToString().Trim();//凭证字 T_ALL_DA T_ALL_DA = new EntityDA.T_ALL_DA(); string strCwSTARTGID = BasicDataRefDAL.GetCwSTARTGID(strUserID); string strCondition = sAndGids + " and Feeid in (select [BSGID] from [cw_vouno_bs_gl] WITH(NOLOCK) where BSTABLENAME='ch_fee_management' and VOUALLNO='" + cfVOUALLNO + "' and STARTGID='" + strCwSTARTGID + "')"; //摘要 string strISEXPLAN = ",CUSTOMERSIDE, REMARK_F"; #endregion #region 科目设置 //科目设置 收支科目 sSQL = "SELECT top 1 * from cw_accitems_gl where ACCID=(SELECT top 1 OPERATORSIDE From VW_ch_fee_management_mbody WHERE 1=1" + strCondition + ")"; DataSet dsSZKM = T_ALL_DA.GetAllSQL(sSQL); //科目设置 汇兑损益 sSQL = "SELECT top 1 * from cw_accitems_gl where ACCID='" + dsCwDesign.Tables[0].Rows[0]["EGOL"].ToString().Trim() + "' and [YEAR]=SUBSTRING('" + strACCDATE + "',1,4) and [STARTGID]='" + strCwSTARTGID + "'"; DataSet dsEGOL = T_ALL_DA.GetAllSQL(sSQL); #endregion #region 基本数据集 string strORDNO = getGid(); IList CwVouitemsEntities = new List(); #region 提取费率 string tbRATE = ""; string tbRATE_CASH = ""; //是否按照系统录入汇率 if (headData.ISRATE == true) { tbRATE = "EXCHANGERATE"; tbRATE_CASH = "EXCHANGERATE"; } else { tbRATE = "EXCHANGERATE"; tbRATE_CASH = "(CASE"; if (bodyList != null) { if (bodyList.Count > 0) { for (int i = 0; i < bodyList.Count; i++) { tbRATE_CASH += " WHEN CURRENCY='" + bodyList[i].CURR.ToString().Trim() + "' THEN " + bodyList[i].FCYEXRATE.ToString().Trim(); } } } tbRATE_CASH += " ELSE EXCHANGERATE END)"; } #endregion #region 科目设置：收支 string sqlOPERATORSIDE = "SELECT (CASE WHEN FEETYPE = 1 THEN '应收账款' WHEN FEETYPE=2 THEN '应付账款' ELSE '' END) as FEETYPE" + ",CURRENCY," + tbRATE + " as EXCHANGERATE" + ",SUM(AMOUNT_F) AS je" + ",convert(decimal(18,2),round(SUM(AMOUNT_F*" + tbRATE + "),2)) AS hj" + ",OPERATORSIDE,OPERATORSIDE_gl" + strISEXPLAN + " From VW_ch_fee_management_mbody WHERE 1=1" + strCondition + " GROUP BY FEETYPE,CURRENCY,EXCHANGERATE,OPERATORSIDE,OPERATORSIDE_gl" + strISEXPLAN + " ORDER BY FEETYPE desc,CURRENCY" + strISEXPLAN; DataSet dsOPERATORSIDE = T_ALL_DA.GetAllSQL(sqlOPERATORSIDE); #endregion #endregion if (dsOPERATORSIDE != null) { #region 插入明细表 if (dsOPERATORSIDE.Tables[0].Rows.Count > 0) { for (int i = 0; i < dsOPERATORSIDE.Tables[0].Rows.Count; i++) { //摘要 strEXPLAN = " " + dsOPERATORSIDE.Tables[0].Rows[i]["CUSTOMERSIDE"].ToString().Trim() + " " + dsOPERATORSIDE.Tables[0].Rows[i]["REMARK_F"].ToString().Trim(); //科目设置 反向收支科目 sSQL = "SELECT top 1 * from cw_accitems_gl where ACCID='" + dsOPERATORSIDE.Tables[0].Rows[i]["OPERATORSIDE_gl"].ToString().Trim() + "'"; DataSet dsFXSZKM = T_ALL_DA.GetAllSQL(sSQL); #region 收支科目 MsCwVouitemsGl MsCwVouitemsGl = new MsCwVouitemsGl(); //MsCwVouitemsGl.GID 唯一编码 newid() MsCwVouitemsGl.ORDNO = strORDNO.Trim(); MsCwVouitemsGl.ITEMNO = 0;// 行号 MsCwVouitemsGl.PACCID = "";//父级科目贷码 MsCwVouitemsGl.PROPERTY = 0;//科目属性 MsCwVouitemsGl.GRADE = 0;//科目级别 if (dsOPERATORSIDE.Tables[0].Rows[i]["FEETYPE"].ToString().Trim().IndexOf("收") > -1) { #region 收入 strJie = strJie + Decimal.Parse(dsOPERATORSIDE.Tables[0].Rows[i]["hj"].ToString().Trim()); // MsCwVouitemsGl.ACCID = dsSZKM.Tables[0].Rows[0]["ACCID"].ToString().Trim();//账款科目贷码 MsCwVouitemsGl.AMTDR = Decimal.Parse(dsOPERATORSIDE.Tables[0].Rows[i]["hj"].ToString().Trim());//本位币借方金额(互斥) MsCwVouitemsGl.AMTCR = 0;//本位币贷方金额(互斥) MsCwVouitemsGl.FCYNO = dsOPERATORSIDE.Tables[0].Rows[i]["CURRENCY"].ToString().Trim();//外币编号usd MsCwVouitemsGl.ISFCY = false;//是否外币 MsCwVouitemsGl.FCYDR = 0;//外币借方金额 MsCwVouitemsGl.FCYCR = 0;//外币贷方金额 MsCwVouitemsGl.FCYEXRATE = 1;//汇率 dEXCHANGERATE = 1;//汇率 if (MsCwVouitemsGl.FCYNO.ToString().Trim() != "RMB") { MsCwVouitemsGl.ISFCY = true;//是否外币 MsCwVouitemsGl.FCYDR = Decimal.Parse(dsOPERATORSIDE.Tables[0].Rows[i]["je"].ToString().Trim());//外币借方金额 MsCwVouitemsGl.FCYCR = 0;//外币贷方金额 MsCwVouitemsGl.FCYEXRATE = Decimal.Parse(dsOPERATORSIDE.Tables[0].Rows[i]["EXCHANGERATE"].ToString().Trim());//汇率 dEXCHANGERATE = MsCwVouitemsGl.FCYEXRATE;//汇率 strFCY = MsCwVouitemsGl.FCYNO; } #endregion } else { #region 支出 strDai = strDai + Decimal.Parse(dsOPERATORSIDE.Tables[0].Rows[i]["hj"].ToString().Trim()); // MsCwVouitemsGl.ACCID = dsSZKM.Tables[0].Rows[0]["ACCID"].ToString().Trim();//账款科目贷码 MsCwVouitemsGl.AMTDR = 0;//本位币借方金额(互斥) MsCwVouitemsGl.AMTCR = Decimal.Parse(dsOPERATORSIDE.Tables[0].Rows[i]["hj"].ToString().Trim());//本位币贷方金额(互斥) MsCwVouitemsGl.FCYNO = dsOPERATORSIDE.Tables[0].Rows[i]["CURRENCY"].ToString().Trim();//外币编号usd MsCwVouitemsGl.ISFCY = false;//是否外币 MsCwVouitemsGl.FCYDR = 0;//外币借方金额 MsCwVouitemsGl.FCYCR = 0;//外币贷方金额 MsCwVouitemsGl.FCYEXRATE = 1;//汇率 dEXCHANGERATE = 1;//汇率 if (MsCwVouitemsGl.FCYNO.ToString().Trim() != "RMB") { MsCwVouitemsGl.ISFCY = true;//是否外币 MsCwVouitemsGl.FCYDR = 0;//外币借方金额 MsCwVouitemsGl.FCYCR = Decimal.Parse(dsOPERATORSIDE.Tables[0].Rows[i]["je"].ToString().Trim());//外币贷方金额 MsCwVouitemsGl.FCYEXRATE = Decimal.Parse(dsOPERATORSIDE.Tables[0].Rows[i]["EXCHANGERATE"].ToString().Trim());//汇率 dEXCHANGERATE = MsCwVouitemsGl.FCYEXRATE;//汇率 strFCY = MsCwVouitemsGl.FCYNO; } #endregion } // MsCwVouitemsGl.ACCNAME = dsSZKM.Tables[0].Rows[0]["ACCNAME"].ToString().Trim();//科目名称 MsCwVouitemsGl.ISDEPTACC = Boolean.Parse(dsSZKM.Tables[0].Rows[0]["ISDEPTACC"].ToString().Trim());//核算部门，按规则设置生成 MsCwVouitemsGl.ISEMPLACC = Boolean.Parse(dsSZKM.Tables[0].Rows[0]["ISEMPLACC"].ToString().Trim());//核算人员，按规则设置生成 MsCwVouitemsGl.ISCORPACC = Boolean.Parse(dsSZKM.Tables[0].Rows[0]["ISCORPACC"].ToString().Trim());//核算客户，按规则设置生成 MsCwVouitemsGl.ISITEMACC = Boolean.Parse(dsSZKM.Tables[0].Rows[0]["ISITEMACC"].ToString().Trim());//核算项目，按规则设置生成 if (dsSZKM.Tables[0].Rows[0]["DC"].ToString().Trim() == "借") { MsCwVouitemsGl.DC = "D";//借方向 } else { MsCwVouitemsGl.DC = "C";//贷方向 } // string strACCALL = ""; //是否核算客户 MsCwVouitemsGl.CORPID = "";//客户(系统) MsCwVouitemsGl.CUSTOMERNAME = "";//客户(系统) if (dsSZKM.Tables[0].Rows[0]["ISCORPACC"].ToString().Trim() == "True") { MsCwVouitemsGl.CORPID = "";//客户(系统) MsCwVouitemsGl.CUSTOMERNAME = dsOPERATORSIDE.Tables[0].Rows[i]["CUSTOMERSIDE"].ToString().Trim();//客户(系统) strACCALL += "▁" + MsCwVouitemsGl.CUSTOMERNAME; } //是否核算人员_揽货人 if (dsSZKM.Tables[0].Rows[0]["ISEMPLACC"].ToString().Trim() == "True") { MsCwVouitemsGl.SALE = dsOPERATORSIDE.Tables[0].Rows[i]["SALE"].ToString().Trim();//人员名称 strACCALL += "▁" + MsCwVouitemsGl.SALE; } //是否核算部门 if (dsSZKM.Tables[0].Rows[0]["ISDEPTACC"].ToString().Trim() == "True") { MsCwVouitemsGl.DEPTGID = dsOPERATORSIDE.Tables[0].Rows[i]["DEPTGID"].ToString().Trim();//揽货人的部门 strACCALL += "▁" + dsOPERATORSIDE.Tables[0].Rows[i]["DEPTNAME"].ToString().Trim(); } //是否核算项目 if (dsSZKM.Tables[0].Rows[0]["ISITEMACC"].ToString().Trim() == "True") { MsCwVouitemsGl.ITEMGID = ""; if (dsCwDesignItem != null) { for (int j = 0; j < dsCwDesignItem.Tables[0].Rows.Count; j++) { if (dsOPERATORSIDE.Tables[0].Rows[0]["OPLBNAME"].ToString().Trim() == dsCwDesignItem.Tables[0].Rows[j]["OPTYPE"].ToString().Trim()) { MsCwVouitemsGl.ITEMGID = dsCwDesignItem.Tables[0].Rows[j]["ITEMGID"].ToString().Trim();//是否核算项目 strACCALL += "▁" + dsCwDesignItem.Tables[0].Rows[j]["ITEMNAME"].ToString().Trim(); } } } } MsCwVouitemsGl.ACCALL = BasicDataRefDAL.setCAGA(dsSZKM.Tables[0].Rows[0]["PACCGID"].ToString(), MsCwVouitemsGl.ACCNAME.ToString().Trim()) + strACCALL; MsCwVouitemsGl.EXPLAN = strEXPLAN;//摘要 strIsExplan2 = strEXPLAN;//摘要 MsCwVouitemsGl.BLNO = ""; //if (dsCwDesign.Tables[0].Rows[0]["ISRPMBLNO"].ToString().Trim() == "True") //{ // MsCwVouitemsGl.BLNO = dsVwcfsdAR.Tables[0].Rows[i]["MBLNO"].ToString().Trim(); //} if (dsCwDesign.Tables[0].Rows[0]["ISVBM"].ToString().Trim() == "True") { MsCwVouitemsGl.ISSYS = false;//是否手动录入 } else { MsCwVouitemsGl.ISSYS = true;//是否手动录入 } MsCwVouitemsGl.QTYUNIT = "";//数量包装单位 MsCwVouitemsGl.QTYDR = 0;//借方数量 MsCwVouitemsGl.QTYCR = 0;//贷方数量 MsCwVouitemsGl.DEPTID = "";//部门(系统) MsCwVouitemsGl.EMPLID = "";//人员(系统) MsCwVouitemsGl.PITEMID = 0;//父级项目编号 MsCwVouitemsGl.ITEMID = "";//项目编号 MsCwVouitemsGl.FIELD1 = "";//预留字段1 MsCwVouitemsGl.FIELD2 = "";//预留字段2 MsCwVouitemsGl.FIELD3 = "";//预留字段3 MsCwVouitemsGl.FIELD4 = "";//预留字段4 MsCwVouitemsGl.FIELD5 = "";//预留字段5 MsCwVouitemsGl.FIELD6 = "";//预留字段6 MsCwVouitemsGl.FIELD7 = "";//预留字段7 MsCwVouitemsGl.FIELD8 = "";//预留字段8 MsCwVouitemsGl.FIELD9 = "";//预留字段9 MsCwVouitemsGl.FIELD10 = "";//预留字段10 MsCwVouitemsGl.FSETTLCODE = "";//结算方式 MsCwVouitemsGl.FSETTLDATE = DateTime.Parse(DateTime.Parse(headData.VOUDATE).ToString("yyyy-MM-dd"));//结算日期 MsCwVouitemsGl.FSETTLENO = "";//财务辅助编号 MsCwVouitemsGl.MODIFIEDUSER = strUserID;//最后一次更新操作人 #endregion CwVouitemsEntities.Add(MsCwVouitemsGl); #region 反向收支科目 MsCwVouitemsGl MsCwVouitemsGl2 = new MsCwVouitemsGl(); //MsCwVouitemsGl2.GID 唯一编码 newid() MsCwVouitemsGl2.ORDNO = strORDNO.Trim(); MsCwVouitemsGl2.ITEMNO = 0;// 行号 MsCwVouitemsGl2.PACCID = "";//父级科目贷码 MsCwVouitemsGl2.PROPERTY = 0;//科目属性 MsCwVouitemsGl2.GRADE = 0;//科目级别 if (dsOPERATORSIDE.Tables[0].Rows[i]["FEETYPE"].ToString().Trim().IndexOf("收") > -1) { #region 支出 strDai = strDai + Decimal.Parse(dsOPERATORSIDE.Tables[0].Rows[i]["hj"].ToString().Trim()); // MsCwVouitemsGl2.ACCID = dsFXSZKM.Tables[0].Rows[0]["ACCID"].ToString().Trim();//账款科目贷码 MsCwVouitemsGl2.AMTDR = 0;//本位币借方金额(互斥) MsCwVouitemsGl2.AMTCR = Decimal.Parse(dsOPERATORSIDE.Tables[0].Rows[i]["hj"].ToString().Trim());//本位币贷方金额(互斥) MsCwVouitemsGl2.FCYNO = dsOPERATORSIDE.Tables[0].Rows[i]["CURRENCY"].ToString().Trim();//外币编号usd MsCwVouitemsGl2.ISFCY = false;//是否外币 MsCwVouitemsGl2.FCYDR = 0;//外币借方金额 MsCwVouitemsGl2.FCYCR = 0;//外币贷方金额 MsCwVouitemsGl2.FCYEXRATE = 1;//汇率 dEXCHANGERATE = 1;//汇率 if (MsCwVouitemsGl2.FCYNO.ToString().Trim() != "RMB") { MsCwVouitemsGl2.ISFCY = true;//是否外币 MsCwVouitemsGl2.FCYDR = 0;//外币借方金额 MsCwVouitemsGl2.FCYCR = Decimal.Parse(dsOPERATORSIDE.Tables[0].Rows[i]["je"].ToString().Trim());//外币贷方金额 MsCwVouitemsGl2.FCYEXRATE = Decimal.Parse(dsOPERATORSIDE.Tables[0].Rows[i]["EXCHANGERATE"].ToString().Trim());//汇率 dEXCHANGERATE = MsCwVouitemsGl2.FCYEXRATE;//汇率 strFCY = MsCwVouitemsGl2.FCYNO; } #endregion } else { #region 收入 strJie = strJie + Decimal.Parse(dsOPERATORSIDE.Tables[0].Rows[i]["hj"].ToString().Trim()); // MsCwVouitemsGl2.ACCID = dsFXSZKM.Tables[0].Rows[0]["ACCID"].ToString().Trim();//账款科目贷码 MsCwVouitemsGl2.AMTDR = Decimal.Parse(dsOPERATORSIDE.Tables[0].Rows[i]["hj"].ToString().Trim());//本位币借方金额(互斥) MsCwVouitemsGl2.AMTCR = 0;//本位币贷方金额(互斥) MsCwVouitemsGl2.FCYNO = dsOPERATORSIDE.Tables[0].Rows[i]["CURRENCY"].ToString().Trim();//外币编号usd MsCwVouitemsGl2.ISFCY = false;//是否外币 MsCwVouitemsGl2.FCYDR = 0;//外币借方金额 MsCwVouitemsGl2.FCYCR = 0;//外币贷方金额 MsCwVouitemsGl2.FCYEXRATE = 1;//汇率 dEXCHANGERATE = 1;//汇率 if (MsCwVouitemsGl2.FCYNO.ToString().Trim() != "RMB") { MsCwVouitemsGl2.ISFCY = true;//是否外币 MsCwVouitemsGl2.FCYDR = Decimal.Parse(dsOPERATORSIDE.Tables[0].Rows[i]["je"].ToString().Trim());//外币借方金额 MsCwVouitemsGl2.FCYCR = 0;//外币贷方金额 MsCwVouitemsGl2.FCYEXRATE = Decimal.Parse(dsOPERATORSIDE.Tables[0].Rows[i]["EXCHANGERATE"].ToString().Trim());//汇率 dEXCHANGERATE = MsCwVouitemsGl2.FCYEXRATE;//汇率 strFCY = MsCwVouitemsGl2.FCYNO; } #endregion } // MsCwVouitemsGl2.ACCNAME = dsFXSZKM.Tables[0].Rows[0]["ACCNAME"].ToString().Trim();//科目名称 MsCwVouitemsGl2.ISDEPTACC = Boolean.Parse(dsFXSZKM.Tables[0].Rows[0]["ISDEPTACC"].ToString().Trim());//核算部门，按规则设置生成 MsCwVouitemsGl2.ISEMPLACC = Boolean.Parse(dsFXSZKM.Tables[0].Rows[0]["ISEMPLACC"].ToString().Trim());//核算人员，按规则设置生成 MsCwVouitemsGl2.ISCORPACC = Boolean.Parse(dsFXSZKM.Tables[0].Rows[0]["ISCORPACC"].ToString().Trim());//核算客户，按规则设置生成 MsCwVouitemsGl2.ISITEMACC = Boolean.Parse(dsFXSZKM.Tables[0].Rows[0]["ISITEMACC"].ToString().Trim());//核算项目，按规则设置生成 if (dsFXSZKM.Tables[0].Rows[0]["DC"].ToString().Trim() == "借") { MsCwVouitemsGl2.DC = "D";//借方向 } else { MsCwVouitemsGl2.DC = "C";//贷方向 } // strACCALL = ""; //是否核算客户 MsCwVouitemsGl2.CORPID = "";//客户(系统) MsCwVouitemsGl2.CUSTOMERNAME = "";//客户(系统) if (dsFXSZKM.Tables[0].Rows[0]["ISCORPACC"].ToString().Trim() == "True") { MsCwVouitemsGl2.CORPID = "";//客户(系统) MsCwVouitemsGl2.CUSTOMERNAME = dsOPERATORSIDE.Tables[0].Rows[i]["CUSTOMERSIDE"].ToString().Trim();//客户(系统) strACCALL += "▁" + MsCwVouitemsGl2.CUSTOMERNAME; } //是否核算人员_揽货人 if (dsFXSZKM.Tables[0].Rows[0]["ISEMPLACC"].ToString().Trim() == "True") { MsCwVouitemsGl2.SALE = dsOPERATORSIDE.Tables[0].Rows[i]["SALE"].ToString().Trim();//人员名称 strACCALL += "▁" + MsCwVouitemsGl2.SALE; } //是否核算部门 if (dsFXSZKM.Tables[0].Rows[0]["ISDEPTACC"].ToString().Trim() == "True") { MsCwVouitemsGl2.DEPTGID = dsOPERATORSIDE.Tables[0].Rows[i]["DEPTGID"].ToString().Trim();//揽货人的部门 strACCALL += "▁" + dsOPERATORSIDE.Tables[0].Rows[i]["DEPTNAME"].ToString().Trim(); } //是否核算项目 if (dsFXSZKM.Tables[0].Rows[0]["ISITEMACC"].ToString().Trim() == "True") { MsCwVouitemsGl2.ITEMGID = ""; if (dsCwDesignItem != null) { for (int j = 0; j < dsCwDesignItem.Tables[0].Rows.Count; j++) { if (dsOPERATORSIDE.Tables[0].Rows[0]["OPLBNAME"].ToString().Trim() == dsCwDesignItem.Tables[0].Rows[j]["OPTYPE"].ToString().Trim()) { MsCwVouitemsGl2.ITEMGID = dsCwDesignItem.Tables[0].Rows[j]["ITEMGID"].ToString().Trim();//是否核算项目 strACCALL += "▁" + dsCwDesignItem.Tables[0].Rows[j]["ITEMNAME"].ToString().Trim(); } } } } MsCwVouitemsGl2.ACCALL = BasicDataRefDAL.setCAGA(dsFXSZKM.Tables[0].Rows[0]["PACCGID"].ToString(), MsCwVouitemsGl2.ACCNAME.ToString().Trim()) + strACCALL; MsCwVouitemsGl2.EXPLAN = strEXPLAN;//摘要 strIsExplan2 = strEXPLAN;//摘要 MsCwVouitemsGl2.BLNO = ""; //if (dsCwDesign.Tables[0].Rows[0]["ISRPMBLNO"].ToString().Trim() == "True") //{ // MsCwVouitemsGl2.BLNO = dsVwcfsdAR.Tables[0].Rows[i]["MBLNO"].ToString().Trim(); //} if (dsCwDesign.Tables[0].Rows[0]["ISVBM"].ToString().Trim() == "True") { MsCwVouitemsGl2.ISSYS = false;//是否手动录入 } else { MsCwVouitemsGl2.ISSYS = true;//是否手动录入 } MsCwVouitemsGl2.QTYUNIT = "";//数量包装单位 MsCwVouitemsGl2.QTYDR = 0;//借方数量 MsCwVouitemsGl2.QTYCR = 0;//贷方数量 MsCwVouitemsGl2.DEPTID = "";//部门(系统) MsCwVouitemsGl2.EMPLID = "";//人员(系统) MsCwVouitemsGl2.PITEMID = 0;//父级项目编号 MsCwVouitemsGl2.ITEMID = "";//项目编号 MsCwVouitemsGl2.FIELD1 = "";//预留字段1 MsCwVouitemsGl2.FIELD2 = "";//预留字段2 MsCwVouitemsGl2.FIELD3 = "";//预留字段3 MsCwVouitemsGl2.FIELD4 = "";//预留字段4 MsCwVouitemsGl2.FIELD5 = "";//预留字段5 MsCwVouitemsGl2.FIELD6 = "";//预留字段6 MsCwVouitemsGl2.FIELD7 = "";//预留字段7 MsCwVouitemsGl2.FIELD8 = "";//预留字段8 MsCwVouitemsGl2.FIELD9 = "";//预留字段9 MsCwVouitemsGl2.FIELD10 = "";//预留字段10 MsCwVouitemsGl2.FSETTLCODE = "";//结算方式 MsCwVouitemsGl2.FSETTLDATE = DateTime.Parse(DateTime.Parse(headData.VOUDATE).ToString("yyyy-MM-dd"));//结算日期 MsCwVouitemsGl2.FSETTLENO = "";//财务辅助编号 MsCwVouitemsGl2.MODIFIEDUSER = strUserID;//最后一次更新操作人 #endregion CwVouitemsEntities.Add(MsCwVouitemsGl2); } } #endregion #region 插入主表 MsCwVouchersGl MsCwVouchersGl = new MsCwVouchersGl(); MsCwVouchersGl.ORDNO = strORDNO.Trim();//唯一编码 newid() varchar (36) MsCwVouchersGl.VOUDATE = headData.VOUDATE;//凭证日期 ////strY + "~" + strM + "~" + sVOUNO + "~" + strVOUNO + "~" + strVOUDATE + "~" + strACCDATE; //string sACCDATE = headData.VOUDATE.Substring(0, 7); //if (DateTime.Parse(sACCDATE + "-01") < DateTime.Parse(strACCDATE + "-01")) //{ // sACCDATE = strACCDATE; //} //string strVOUNOhj = MsBaseInfoDAL.GetCwVOUNO(strBPVW.Trim(), sACCDATE, strUserID); //string[] items = strVOUNOhj.Trim().Split(new string[] { "~" }, StringSplitOptions.RemoveEmptyEntries); //string sVOUNO = items[2].ToString().Trim(); //string strVOUNO = items[3].ToString().Trim(); //MsCwVouchersGl.VOUNO = sVOUNO;//月初清零 varchar (16) MsCwVouchersGl.VOUNO = int.Parse(cfVOUALLNO.Trim().Substring(cfVOUALLNO.Trim().Length - 4)).ToString();//sVOUNO;//月初清零 varchar (16) MsCwVouchersGl.ACCYEAR = DateTime.Parse(headData.VOUDATE.ToString()).Year.ToString();//年 MsCwVouchersGl.ACCMONTH = DateTime.Parse(headData.VOUDATE.ToString()).Month.ToString();//月 if (MsCwVouchersGl.ACCMONTH.ToString().Length == 1) { MsCwVouchersGl.ACCMONTH = "0" + MsCwVouchersGl.ACCMONTH.ToString();//月 } MsCwVouchersGl.VKNO = strBPVW.Trim();//凭证字 //MsCwVouchersGl.VOUALLNO = MsCwVouchersGl.VKNO + MsCwVouchersGl.ACCYEAR + MsCwVouchersGl.ACCMONTH + strVOUNO;//年+月+自动生成编号=凭证号 MsCwVouchersGl.VOUALLNO = cfVOUALLNO.Trim().Replace("记", strBPVW.Trim());//年+月+自动生成编号=凭证号 MsCwVouchersGl.ATTACHS = 1;// int.Parse(dsCwDesign.Tables[0].Rows[0]["AFFIXNO"].ToString().Trim());//附件（单据）数 MsCwVouchersGl.AMTDR = strJie;// iAMTDR;//本位币借方 MsCwVouchersGl.AMTCR = strDai;//iAMTDR;// iAMTCR;//本位币贷方 MsCwVouchersGl.FCY = strFCY;//是否含有外币科目 MsCwVouchersGl.QTY = "";//是否含有数量科目 MsCwVouchersGl.VOUPROP = "";//凭证属性 MsCwVouchersGl.PREPARED = headData.PREPARED == null ? strSHOWNAME : headData.PREPARED.ToString().Trim();//制单人 MsCwVouchersGl.CHECKED = "";//审核人 MsCwVouchersGl.ENTERED = "";//记账人 MsCwVouchersGl.ERRMSG = "";//错误信息 MsCwVouchersGl.ISDELETE = false;//是否废除 MsCwVouchersGl.DELETEUSER = "";//废除操作人 MsCwVouchersGl.CREATEUSER = strUserID;//创建人 MsCwVouchersGl.CREATETIME = DateTime.Now;//创建时间 MsCwVouchersGl.MODIFIEDUSER = strUserID;//最后一次更新操作人 MsCwVouchersGl.MODIFIEDTIME = DateTime.Now;//最后一次更新操作时间 MsCwVouchersGl.DbOperationType = DbOperationType.DbotIns; MsCwVouchersGl.STARTGID = strCwSTARTGID; #endregion #region 生成入库 var modb = new ModelObjectDB(); result = modb.Save(MsCwVouchersGl); if (result.Success == true) { //int n = 1; BasicDataRefDAL.SetGLOrder(ref CwVouitemsEntities); foreach (var enumValue in CwVouitemsEntities) { enumValue.GID = Guid.NewGuid().ToString(); //enumValue.ITEMNO = n;//行号 enumValue.DbOperationType = DbOperationType.DbotIns; var modb2 = new ModelObjectDB(); DBResult result2 = modb2.Save(enumValue); //n++; } //更新凭证生成关系表中的ORDNO数据 string strLS = sAndGids.Replace("Feeid", "BSGID"); string blSQL = "update [cw_vouno_bs_gl] set ORDNO='" + MsCwVouchersGl.ORDNO + "' where VOUALLNO='" + cfVOUALLNO + "' and BSTABLENAME='ch_fee_management' and [STARTGID]='" + strCwSTARTGID + "'" + strLS; bool bl = T_ALL_DA.GetExecuteSqlCommand(blSQL); blSQL = "update code_fill_serialno set ISDELETE=1,DELETEUSER='" + strUserID + "',DELETETIME=GETDATE() where TABLENAME='cw_vouchers_gl' and SERIALNO='" + cfVOUALLNO + "' and STARTGID='" + strCwSTARTGID + "'"; bl = T_ALL_DA.GetExecuteSqlCommand(blSQL); } else { //result.Success = false; //result.Message = result.Message; //return result; //删除凭证生成关系表中的ch_fee_management的GID数据 string strLS = sAndGids.Replace("Feeid", "BSGID"); string blSQL = "delete from [cw_vouno_bs_gl] where VOUALLNO='" + cfVOUALLNO + "' and BSTABLENAME='ch_fee_management' and [STARTGID]='" + strCwSTARTGID + "'" + strLS; bool bl = T_ALL_DA.GetExecuteSqlCommand(blSQL); return -2;//重复数据 } #endregion } else { //删除凭证生成关系表中的ch_fee_management的GID数据 string strLS = sAndGids.Replace("Feeid", "BSGID"); string blSQL = "delete from [cw_vouno_bs_gl] where VOUALLNO='" + cfVOUALLNO + "' and BSTABLENAME='ch_fee_management' and [STARTGID]='" + strCwSTARTGID + "'" + strLS; bool bl = T_ALL_DA.GetExecuteSqlCommand(blSQL); return -3;//结算无明细数据 } //result.Success = true; //result.Message = result.Message; //return result; return 1; } #endregion #region 判断是否生成 public static String getGid() { string strgid = Guid.NewGuid().ToString(); strgid = strgid.Replace("-", ""); strgid = "VOU" + strgid; return strgid; } public static DBResult GetIsVoucher(string strGids, string strUserID) { string strCwSTARTGID = BasicDataRefDAL.GetCwSTARTGID(strUserID); var result = new DBResult(); T_ALL_DA T_ALL_DA = new EntityDA.T_ALL_DA(); string sSQL = "SELECT count(*) as inum from VW_ch_fee_management_mbody where Feeid in ('" + strGids.Trim().Replace(",", "','") + "') and (Feeid in (select [BSGID] from [cw_vouno_bs_gl] WITH(NOLOCK) where BSTABLENAME='ch_fee_management' and STARTGID='" + strCwSTARTGID + "') or FEESTATUS=1 or OPERATORSIDE_gl='' or OPERATORSIDE_gl is null or OPERATORSIDE='' or OPERATORSIDE is null)";//因烟台爱德林不需要“关系人”，因此去掉“ or CUSTOMERSIDE='' or CUSTOMERSIDE is null”！2016-12-13 int inum = int.Parse(T_ALL_DA.GetStrSQL("inum", sSQL)); if (inum == 0) { result.Success = true; result.Message = ""; return result; } else { result.Success = false; result.Message = "请选择已审核通过、总账科目、收支科目不为空，且未生成凭证的单据!";//因烟台爱德林不需要“关系人”，因此去掉“关系人不为空”的判断！2016-12-13 return result; } } /// /// 是否自动弹出结算总账凭证 /// /// public static DBResult onIsSettlementsopen(string strUserID) { var result = new DBResult(); T_ALL_DA T_ALL_DA = new EntityDA.T_ALL_DA(); string strCwSTARTGID = BasicDataRefDAL.GetCwSTARTGID(strUserID); string sSQL = "SELECT top 1 ISSETTLEMENTSOPEN from cw_design_gl WITH(NOLOCK) where [STARTGID]='" + strCwSTARTGID + "'"; Boolean blISSETTLEMENTSOPEN = Boolean.Parse(T_ALL_DA.GetStrSQL("ISSETTLEMENTSOPEN", sSQL)); if (blISSETTLEMENTSOPEN) { result.Success = true; result.Message = ""; return result; } else { result.Success = false; result.Message = ""; return result; } } #endregion #endregion } }