首页 > 代码库 > 我习惯把代码写得很整齐
我习惯把代码写得很整齐
我喜欢写C#代码，不喜欢写JS代码和SQL。相比于C#，总感觉JS和SQL写起来乱糟糟的。
最近做了几个统计页面，不得不拼接很长的SQL，我总是尽量把SQL写的层次分明，容易阅读。
应该还有别的实现方法，比如在数据库中写存储过程、函数，但是我不想维护数据库，在当前数据库中没有什么存储过程和函数的情况下，我总是尽量避免在数据库中写存储过程和函数。
别人实现复杂的统计功能的时候，是不是也是这么做的呢？有什么更好的方法？
using System;using System.Collections.Generic;using System.Text;using Demo.Service.Interface.Admin.Report;using Demo.Domain.Admin;using Demo.Const;using Simpo;namespace Demo.Service.Admin.Report{ /// <summary> /// 招生业绩趋势 /// </summary> public class PerformanceTrendService : Service, IPerformanceTrendService { #region 统计招生业绩趋势 /// <summary> /// 统计招生业绩趋势 /// </summary> /// <param name="schoolAreaId">校区ID</param> /// <param name="deptIds">部门ID集合</param> /// <param name="employeeUserId">员工ID</param> /// <param name="startYear">开始年份</param> /// <param name="endYear">结束年份，若为-1，则按月统计</param> public List<PerformanceTrend> GetList_Trend(int schoolAreaId, string deptIds, int employeeUserId, int startYear, int endYear) { StringBuilder sb = new StringBuilder(); //按年度月度 if (endYear == Constants.OptionAllVal)//按月度 { sb.Append(string.Format(@" select _Year, _Month, count(CustomerId) as DJKH, sum(IsStudent) as CJKH, (select count(cfr1.Id) from FM_CustomerFollowRecord cfr1 join FM_Customer cus1 on cfr1.CustomerId=cus1.Id join Sys_User u1 on cus1.RegistUserId=u1.Id join FM_Employee emp1 on u1.RelationId=emp1.Id join FM_Dept dpt1 on emp1.DeptId=dpt1.Id join FM_SchoolArea scl1 on dpt1.SchoolId=scl1.Id where year(cus1.RegistTime)=t._Year and month(cus1.RegistTime)=t._Month {0} {1} {2} ) as GJCS, ((select sum(psf2.PayAmount) from FM_PayStepFlow psf2 join FM_PayFlow pf2 on psf2.PayFlowId=pf2.Id join FM_Customer cus2 on pf2.CustomerCode=cus2.Code join Sys_User u2 on cus2.RegistUserId=u2.Id join FM_Employee emp2 on u2.RelationId=emp2.Id join FM_Dept dpt2 on emp2.DeptId=dpt2.Id join FM_SchoolArea scl2 on dpt2.SchoolId=scl2.Id where psf2.DelFlg<>1 and year(cus2.RegistTime)=t._Year and month(cus2.RegistTime)=t._Month {3} {4} {5} ) - ISNULL((select sum(rf3.RefundAmount) from FM_RefundFlow rf3 join FM_Customer cus3 on rf3.CustomerCode=cus3.Code join Sys_User u3 on cus3.RegistUserId=u3.Id join FM_Employee emp3 on u3.RelationId=emp3.Id join FM_Dept dpt3 on emp3.DeptId=dpt3.Id join FM_SchoolArea scl3 on dpt3.SchoolId=scl3.Id where rf3.DelFlg<>1 and rf3.SettleFlg=1 and year(cus3.RegistTime)=t._Year and month(cus3.RegistTime)=t._Month {6} {7} {8} ),0)) as CJE from ( ", schoolAreaId != Constants.OptionAllVal ? " and cus1.SchoolAreaId=" + schoolAreaId : "", strUtil.IsNullOrEmpty(deptIds) ? "" : " and dpt1.Id in (" + deptIds + ")", employeeUserId != Constants.OptionAllVal ? " and cus1.RegistUserId=" + employeeUserId : "", schoolAreaId != Constants.OptionAllVal ? " and pf2.SchoolAreaId=" + schoolAreaId : "", strUtil.IsNullOrEmpty(deptIds) ? "" : " and dpt2.Id in (" + deptIds + ")", employeeUserId != Constants.OptionAllVal ? " and cus2.RegistUserId=" + employeeUserId : "", schoolAreaId != Constants.OptionAllVal ? " and rf3.SchoolAreaId=" + schoolAreaId : "", strUtil.IsNullOrEmpty(deptIds) ? "" : " and dpt3.Id in (" + deptIds + ")", employeeUserId != Constants.OptionAllVal ? " and cus3.RegistUserId=" + employeeUserId : "")); } else//按年度 { sb.Append(string.Format(@"select _Year, count(CustomerId) as DJKH, sum(IsStudent) as CJKH, (select count(cfr1.Id) from FM_CustomerFollowRecord cfr1 join FM_Customer cus1 on cfr1.CustomerId=cus1.Id join Sys_User u1 on cus1.RegistUserId=u1.Id join FM_Employee emp1 on u1.RelationId=emp1.Id join FM_Dept dpt1 on emp1.DeptId=dpt1.Id join FM_SchoolArea scl1 on dpt1.SchoolId=scl1.Id where year(cus1.RegistTime)=t._Year {0} {1} {2} ) as GJCS, ((select sum(psf2.PayAmount) from FM_PayStepFlow psf2 join FM_PayFlow pf2 on psf2.PayFlowId=pf2.Id join FM_Customer cus2 on pf2.CustomerCode=cus2.Code join Sys_User u2 on cus2.RegistUserId=u2.Id join FM_Employee emp2 on u2.RelationId=emp2.Id join FM_Dept dpt2 on emp2.DeptId=dpt2.Id join FM_SchoolArea scl2 on dpt2.SchoolId=scl2.Id where psf2.DelFlg<>1 and year(cus2.RegistTime)=t._Year {3} {4} {5} ) - ISNULL((select sum(rf3.RefundAmount) from FM_RefundFlow rf3 join FM_Customer cus3 on rf3.CustomerCode=cus3.Code join Sys_User u3 on cus3.RegistUserId=u3.Id join FM_Employee emp3 on u3.RelationId=emp3.Id join FM_Dept dpt3 on emp3.DeptId=dpt3.Id join FM_SchoolArea scl3 on dpt3.SchoolId=scl3.Id where rf3.DelFlg<>1 and rf3.SettleFlg=1 and year(cus3.RegistTime)=t._Year {6} {7} {8} ),0)) as CJE from ( ", schoolAreaId != Constants.OptionAllVal ? " and cus1.SchoolAreaId=" + schoolAreaId : "", strUtil.IsNullOrEmpty(deptIds) ? "" : " and dpt1.Id in (" + deptIds + ")", employeeUserId != Constants.OptionAllVal ? " and cus1.RegistUserId=" + employeeUserId : "", schoolAreaId != Constants.OptionAllVal ? " and pf2.SchoolAreaId=" + schoolAreaId : "", strUtil.IsNullOrEmpty(deptIds) ? "" : " and dpt2.Id in (" + deptIds + ")", employeeUserId != Constants.OptionAllVal ? " and cus2.RegistUserId=" + employeeUserId : "", schoolAreaId != Constants.OptionAllVal ? " and rf3.SchoolAreaId=" + schoolAreaId : "", strUtil.IsNullOrEmpty(deptIds) ? "" : " and dpt3.Id in (" + deptIds + ")", employeeUserId != Constants.OptionAllVal ? " and cus3.RegistUserId=" + employeeUserId : "")); } sb.Append(string.Format(@" select year(cus.RegistTime) as _Year, month(cus.RegistTime) as _Month, cus.Id as CustomerId, cus.IsStudent as IsStudent from FM_Customer cus left join Sys_User u on cus.RegistUserId=u.Id left join FM_Employee emp on u.RelationId=emp.Id left join FM_Dept dpt on emp.DeptId=dpt.Id left join FM_SchoolArea scl on dpt.SchoolId=scl.Id where cus.DelFlg<>{0}", (int)Flag.Yes, DictType.CD08)); // 校区 if (schoolAreaId != Constants.OptionAllVal) { sb.Append(string.Format(" and cus.SchoolAreaId={0} ", schoolAreaId)); } // 部门 if (!strUtil.IsNullOrEmpty(deptIds)) { sb.Append(string.Format(" and dpt.Id in ({0}) ", deptIds)); } // 登记人 if (employeeUserId != Constants.OptionAllVal) { sb.Append(string.Format(" and cus.RegistUserId={0} ", employeeUserId)); } sb.Append(" ) t "); //按年度月度 if (endYear == Constants.OptionAllVal)//按月度 { sb.Append(string.Format(" where t._Year={0} group by t._Year, t._Month ", startYear)); } else//按年度 { sb.Append(" group by t._Year "); } return this.FindListBySql<PerformanceTrend>(sb.ToString()); } #endregion #region 统计招生业绩排行 /// <summary> /// 统计招生业绩排行 /// </summary> /// <param name="schoolAreaId">校区ID</param> /// <param name="deptIds">部门ID集合</param> /// <param name="employeeUserId">员工ID</param> /// <param name="startDate">统计时段开始</param> /// <param name="endDate">统计时段结束</param> public List<PerformanceRank> GetList_Rank(int schoolAreaId, string deptIds, int employeeUserId, string startDate, string endDate) { StringBuilder sb = new StringBuilder(); sb.Append(string.Format(@" select emp.Name as EmployeeName, dpt.Name as DeptName, count(cus.Id) as DJKH, sum(cus.IsStudent) as CJKH, (select count(cfr1.Id) from FM_CustomerFollowRecord cfr1 join FM_Customer cus1 on cfr1.CustomerId=cus1.Id join Sys_User u1 on cus1.RegistUserId=u1.Id join FM_Employee emp1 on u1.RelationId=emp1.Id join FM_Dept dpt1 on emp1.DeptId=dpt1.Id join FM_SchoolArea scl1 on dpt1.SchoolId=scl1.Id where cus1.RegistUserId=cus.RegistUserId and cus1.RegistTime>=‘{1}‘ and cus1.RegistTime<=‘{2}‘ {3} {4} {5} ) as GJCS, ((select sum(psf2.PayAmount) from FM_PayStepFlow psf2 join FM_PayFlow pf2 on psf2.PayFlowId=pf2.Id join FM_Customer cus2 on pf2.CustomerCode=cus2.Code join Sys_User u2 on cus2.RegistUserId=u2.Id join FM_Employee emp2 on u2.RelationId=emp2.Id join FM_Dept dpt2 on emp2.DeptId=dpt2.Id join FM_SchoolArea scl2 on dpt2.SchoolId=scl2.Id where psf2.DelFlg<>1 and cus2.RegistUserId=cus.RegistUserId and cus2.RegistTime>=‘{1}‘ and cus2.RegistTime<=‘{2}‘ {6} {7} {8} ) - ISNULL((select sum(rf3.RefundAmount) from FM_RefundFlow rf3 join FM_Customer cus3 on rf3.CustomerCode=cus3.Code join Sys_User u3 on cus3.RegistUserId=u3.Id join FM_Employee emp3 on u3.RelationId=emp3.Id join FM_Dept dpt3 on emp3.DeptId=dpt3.Id join FM_SchoolArea scl3 on dpt3.SchoolId=scl3.Id where rf3.DelFlg<>1 and rf3.SettleFlg=1 and cus3.RegistUserId=cus.RegistUserId and cus3.RegistTime>=‘{1}‘ and cus3.RegistTime<=‘{2}‘ {9} {10} {11} ),0)) as CJE from FM_Customer cus left join Sys_User u on cus.RegistUserId=u.Id left join FM_Employee emp on u.RelationId=emp.Id left join FM_Dept dpt on emp.DeptId=dpt.Id left join FM_SchoolArea scl on dpt.SchoolId=scl.Id where cus.DelFlg<>{0} and cus.RegistTime>=‘{1}‘ and cus.RegistTime<=‘{2}‘", (int)Flag.Yes, startDate + " 00:00:00", endDate + " 23:59:59", schoolAreaId != Constants.OptionAllVal ? " and cus1.SchoolAreaId=" + schoolAreaId : "", strUtil.IsNullOrEmpty(deptIds) ? "" : " and dpt1.Id in (" + deptIds + ")", employeeUserId != Constants.OptionAllVal ? " and cus1.RegistUserId=" + employeeUserId : "", schoolAreaId != Constants.OptionAllVal ? " and pf2.SchoolAreaId=" + schoolAreaId : "", strUtil.IsNullOrEmpty(deptIds) ? "" : " and dpt2.Id in (" + deptIds + ")", employeeUserId != Constants.OptionAllVal ? " and cus2.RegistUserId=" + employeeUserId : "", schoolAreaId != Constants.OptionAllVal ? " and rf3.SchoolAreaId=" + schoolAreaId : "", strUtil.IsNullOrEmpty(deptIds) ? "" : " and dpt3.Id in (" + deptIds + ")", employeeUserId != Constants.OptionAllVal ? " and cus3.RegistUserId=" + employeeUserId : "")); // 校区 if (schoolAreaId != Constants.OptionAllVal) { sb.Append(string.Format(" and cus.SchoolAreaId={0} ", schoolAreaId)); } // 部门 if (!strUtil.IsNullOrEmpty(deptIds)) { sb.Append(string.Format(" and dpt.Id in({0}) ", deptIds)); } // 登记人 if (employeeUserId != Constants.OptionAllVal) { sb.Append(string.Format(" and cus.RegistUserId={0} ", employeeUserId)); } sb.Append(string.Format(" group by cus.RegistUserId, emp.Name, dpt.Name ", startDate)); return this.FindListBySql<PerformanceRank>(sb.ToString()); } #endregion #region 来源途径分析 /// <summary> /// 来源途径分析 /// </summary> /// <param name="schoolAreaId">校区ID</param> /// <param name="stuOrCus">类型</param> /// <param name="startDate">统计时段开始</param> /// <param name="endDate">统计时段结束</param> public List<StuCusSource> GetList_Source(int schoolAreaId, int stuOrCus, string startDate, string endDate) { StringBuilder sb = new StringBuilder(); if (stuOrCus == (int)StuOrCus.Customer) //客户 { sb.Append(string.Format(@" select dict.Name as Source, count(cus.Id) as Num from FM_Customer cus join FM_SchoolArea scl on scl.Id=cus.SchoolAreaId join Sys_Dict dict on dict.Type=‘{3}‘ and dict.Code=cus.Source where cus.DelFlg<>{0} and cus.RegistTime>=‘{1}‘ and cus.RegistTime<=‘{2}‘", (int)Flag.Yes, startDate + " 00:00:00", endDate + " 23:59:59", DictType.CD02)); } else //学员 { sb.Append(string.Format(@" select dict.Name as Source, count(stu.Id) as Num from FM_Student stu join FM_SchoolArea scl on scl.Id=stu.SchoolAreaId join Sys_Dict dict on dict.Type=‘{3}‘ and dict.Code=stu.Source where stu.DelFlg<>{0} and stu.RegistTime>=‘{1}‘ and stu.RegistTime<=‘{2}‘", (int)Flag.Yes, startDate + " 00:00:00", endDate + " 23:59:59", DictType.CD02)); } // 校区 if (schoolAreaId != Constants.OptionAllVal) { sb.Append(string.Format(" and scl.Id={0} ", schoolAreaId)); } sb.Append(" group by dict.Name"); return this.FindListBySql<StuCusSource>(sb.ToString()); } #endregion }}
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。