internal static List <HealthbillingKeyValue> loadHealthbilling(int year) { ReadProperty rp = new ReadProperty(); try { strSQL = string.Format(@"SELECT EInfo_id,Month,SUM([1C]+[1D]+[1E]+[1F]) AS RESULT FROM emp_Healthbilling WHERE Month BETWEEN 1 AND 12 AND Year = '{0}'GROUP BY EInfo_id, Month ORDER BY EInfo_id, CAST(Month AS INT)", year); List <HealthbillingKeyValue> obj = new List <HealthbillingKeyValue>(); int month = 0; using (SqlDataReader dr = SqlHelperIRS.ExecuteReader(CommandType.Text, strSQL)) { while (dr.Read()) { month = dr["Month"].ToInt(); obj.Add(new HealthbillingKeyValue() { EinfoID = dr["EInfo_id"].ToInt(), Month = dr["Month"].ToInt(), Value = dr["RESULT"].ToFloat(), TextField14 = string.Format("Text{0}", rp.code14[month - 1].ToString()), TextField15 = string.Format("Text{0}", rp.code15[month - 1].ToString()), TextField16 = string.Format("Text{0}", rp.code16[month - 1].ToString()), }); } } return(obj); } catch (Exception) { throw; } }
public static List <OutputXMLResult> QueryTestSoure() { List <OutputXMLResult> soure = new List <OutputXMLResult>(); strSQL = string.Format("SELECT EinfoID,ColumnName,TwelveMonth,Jan,Feb,Mar,Apr,May,June,July,Aug,Sept,Oct,Nov,Dec FROM A_IRSTest"); using (SqlDataReader dr = SqlHelperIRS.ExecuteReader(CommandType.Text, strSQL)) { while (dr.Read()) { soure.Add(new OutputXMLResult() { EinfoID = dr["EinfoID"].ToString(), ColumnName = dr["ColumnName"].ToInt(), TwelveMonth = dr["TwelveMonth"].ToString(), Jan = dr["Jan"].ToString(), Feb = dr["Feb"].ToString(), Mar = dr["Mar"].ToString(), Apr = dr["Apr"].ToString(), May = dr["May"].ToString(), June = dr["June"].ToString(), July = dr["July"].ToString(), Aug = dr["Aug"].ToString(), Sept = dr["Sept"].ToString(), Oct = dr["Oct"].ToString(), Nov = dr["Nov"].ToString(), Dec = dr["Dec"].ToString(), }); } } return(soure); }
/// <summary> /// 根据Year获取Column15 Code /// </summary> /// <param name="year"></param> /// <returns></returns> internal static string Code15(int year) { try { strSQL = string.Format("SELECT Amount FROM ref_line15amount WHERE Year='{0}'", year); return(SqlHelperIRS.ExecuteScalarToStr(CommandType.Text, strSQL)); } catch (Exception) { throw; } }
/// <summary> /// 获取Column14 Code /// </summary> /// <param name="num"></param> /// <returns></returns> internal static string Code14(int num) { try { strSQL = string.Format("SELECT Code FROM ref_line14code WHERE id ='{0}'", num); return(SqlHelperIRS.ExecuteScalarToStr(CommandType.Text, strSQL)); } catch (Exception) { throw; } }
internal static bool getRehireEmployeeDate(int EinfoID, int Year, ref int reHire, ref int end) { bool result = false; strSQL = string.Format("EXEC sp_IRS_rehireEmplyee '{0}',{1}", EinfoID, Year); using (SqlDataReader dr = SqlHelperIRS.ExecuteReader(CommandType.Text, strSQL)) { while (dr.Read()) { reHire = dr["mdtRehire"].ToInt(); end = dr["mdtEnd"].ToInt(); result = reHire > 0 || end > 0; } } return(result); }
internal static string Code15Date(int year) { try { strSQL = string.Format("SELECT Date FROM ref_line15amount WHERE Year ='{0}'", year); string result = SqlHelperIRS.ExecuteScalarToStr(CommandType.Text, strSQL); DateTime signatureDate = DateTime.MinValue; if (DateTime.TryParse(result, out signatureDate) && signatureDate > DateTime.MinValue) { return(signatureDate.ToString("MM/dd/yyyy")); } } catch (Exception) { throw; } return(string.Empty); }
internal static Dictionary <string, string> ALE1095CSoure() { Dictionary <string, string> dic = new Dictionary <string, string>(); strSQL = string.Format("SELECT NameALE ,EmployerEIN ,[Address] ,City,ST,Country,ZIP,NamePersonContact,ContactPhone,NameGovrnEntity,Entity_EmployeEIN,StreetEntity_address,Entity_city,Entity_state,Entity_countryandZip,Entity_contact,Entity_phone,Einfo_sig,Einfo_title,plan_start_month FROM ref_ALE"); using (SqlDataReader dr = SqlHelperIRS.ExecuteReader(CommandType.Text, strSQL)) { while (dr.Read()) { dic.Add("Text7", dr["NameALE"].ToString()); dic.Add("Text8", dr["EmployerEIN"].ToString()); dic.Add("Text9", dr["Address"].ToString()); dic.Add("Text10", dr["ContactPhone"].ToString()); dic.Add("Text11", dr["City"].ToString()); dic.Add("Text12", dr["ST"].ToString()); dic.Add("Text13", dr["ZIP"].ToString()); dic.Add("Text77", dr["plan_start_month"].ToString()); } } return(dic); }
/// <summary> /// Q: Should be 2D, because the employee's TD is 1/9/2015 years, he worked in January. Right? /// A: You are correct.The employee worked in January.As a result, it cannot be 2A or 2D. If there is no record on emp_helathbilling for January, it has to be left blank /// </summary> /// <param name="query"></param> /// <param name="employee"></param> /// <returns></returns> internal static bool TerminationBorder(List <HealthbillingKeyValue> query, IRSEmployee employee) { if (query.Count > 0) { var last = query.OrderByDescending(p => p.Month).Take(1).FirstOrDefault(); if (last.Value > 0) { //处理离职最后一个月 string start = employee.terminateDate.Month == last.Month ? employee.terminateDate.ToString("MM/01/yyyy") : string.Format("{0}/1/{1}", last.Month, employee.Year); string end = employee.terminateDate.Month == last.Month ? employee.terminateDate.ToShortDateString() : DateTime.Parse(start).AddMonths(1).AddDays(-1).ToShortDateString(); strSQL = string.Format("SELECT COUNT(*) FROM emp_LeaveRecord WHERE EInfo_id={0} AND leaveType IN('SWOP','LWOP','SWP') AND leaveDate BETWEEN '{1}' AND '{2}' ", employee.EinfoID, start, end); int result = SqlHelperIRS.ExecuteScalarToStr(CommandType.Text, strSQL).ToInt(); return(result == 0); } } return(false); }
internal static Dictionary <string, string> ALE1094CSoure() { string sig = string.Empty; Dictionary <string, string> dic = new Dictionary <string, string>(); strSQL = string.Format("SELECT NameALE ,EmployerEIN ,[Address] ,City,ST,ZIP,NamePersonContact,ContactPhone,NameGovrnEntity,Entity_EmployeEIN,StreetEntity_address,Entity_city,Entity_state,Entity_countryandZip,Entity_contact,Entity_phone,Einfo_sig,Einfo_title FROM ref_ALE"); using (SqlDataReader dr = SqlHelperIRS.ExecuteReader(CommandType.Text, strSQL)) { while (dr.Read()) { dic.Add("Text1", dr["NameALE"].ToString()); dic.Add("Text2", dr["EmployerEIN"].ToString()); dic.Add("Text3", dr["Address"].ToString()); dic.Add("Text4", dr["City"].ToString()); dic.Add("Text5", dr["ST"].ToString()); dic.Add("Text6", dr["ZIP"].ToString()); dic.Add("Text7", dr["NamePersonContact"].ToString()); dic.Add("Text8", dr["ContactPhone"].ToString()); dic.Add("Text9", dr["NameGovrnEntity"].ToString()); dic.Add("Text10", dr["Entity_EmployeEIN"].ToString()); dic.Add("Text11", dr["StreetEntity_address"].ToString()); dic.Add("Text12", dr["Entity_city"].ToString()); dic.Add("Text13", dr["Entity_state"].ToString()); dic.Add("Text14", dr["Entity_countryandZip"].ToString()); dic.Add("Text15", dr["Entity_contact"].ToString()); dic.Add("Text16", dr["Entity_phone"].ToString()); sig = dr["Einfo_sig"].ToString(); if (!string.IsNullOrEmpty(sig)) { dic.Add("Text19", String.Format("https://dbapp.hsgd.org/HRIS_HSGD/wupd/Signature.aspx?id={0}&ac=eid", sig)); } dic.Add("Text20", dr["Einfo_title"].ToString()); } } return(dic); }
internal static List <IRSEmployee> loadAllEmployees(string Condition, DateTime startDate, DateTime endDate, EnumIRSTotalNumber tn = EnumIRSTotalNumber.Text18_20) { List <IRSEmployee> Employees = new List <IRSEmployee>(); string employeeType = "AND employee_type in('001','002','003')"; if (tn == EnumIRSTotalNumber.ColumnC) { employeeType = "AND employee_type in('001','002','003','004','005')"; } #region T-SQL strSQL = string.Format(@"SELECT a.EInfo_id ,h.dept_id AS DepartId ,h.name AS Department ,b.Name ,b.Status ,b.[1C] ,b.[1D] ,b.[1E] ,b.[1F] ,b.[Month] ,b.[Year] ,REPLACE(a.SSN,'-','') AS SSN ,CASE WHEN isnull(a.Name_Middle,'') ='' THEN isnull(a.Name_First,'') +' , ' + isnull(a.Name_Last,'') ELSE isnull(a.Name_First,'') +' , '+ isnull(a.Name_Middle,'') + ' , ' + isnull(a.Name_Last,'') END AS EmployeeName ,a.Name_First ,a.Name_Middle ,a.Name_Last ,a.Date_Birth , isnull(e.street1,'')+isnull(e.street2,'') AS Address , Replace(Replace(e.city, ',', ''),'''','') City ,e.State , Replace(e.ZipCode, ',', '') ZipCode , cast(d.date_entry_to_agency AS DATE) HireDate ,cast(d.date_terminate_from_agency AS DATE) terminateDate ,CASE WHEN ISNULL((SELECT TOP 1 EInfo_id FROM Position_Info aa WHERE aa.EInfo_id IS NOT NULL AND aa.EInfo_id=a.EInfo_id GROUP BY aa.EInfo_id HAVING COUNT(*) >= 2),'')='' THEN '0' ELSE '1' END AS isRehire FROM ( SELECT * FROM Person WHERE EInfo_id IN ( SELECT DISTINCT b.EInfo_id FROM ( SELECT DISTINCT a.EInfo_id FROM person a INNER JOIN Employment_Control b ON b.Person_System_ID = a.Person_System_ID INNER JOIN Employment_Info c ON c.Employment_System_ID = b.Employment_System_ID INNER JOIN Position_Info d ON b.Position_System_ID=d.Position_System_ID WHERE CAST(position_start_date AS DATE)<'{2}' AND CAST(isnull(position_end_date,'12/31/2099') AS DATE)>'{3}' AND d.EInfo_id IN ( SELECT EInfo_id FROM Position_Info WHERE EInfo_id IS NOT NULL GROUP BY EInfo_id HAVING COUNT(*)>=2 ) {4} AND (c.employment_status=1 OR year(c.date_terminate_from_agency)>='{1}') UNION ALL SELECT aa.EInfo_id FROM ( SELECT a.* FROM person a INNER JOIN Employment_Control b ON b.Person_System_ID = a.Person_System_ID AND b.Update_Date = (SELECT Max(Update_Date) FROM Employment_Control WHERE Person_System_ID = a.Person_System_ID) INNER JOIN Employment_Info c ON c.Employment_System_ID = b.Employment_System_ID INNER JOIN Position_Info d ON b.Position_System_ID=d.Position_System_ID WHERE CAST(position_start_date AS DATE)<'{2}' {4} AND (c.employment_status=1 OR year(c.date_terminate_from_agency)>='{1}') EXCEPT SELECT a.* FROM person a INNER JOIN Employment_Control b ON b.Person_System_ID = a.Person_System_ID AND b.Update_Date = (SELECT Max(Update_Date) FROM Employment_Control WHERE Person_System_ID = a.Person_System_ID) INNER JOIN Employment_Info c ON c.Employment_System_ID = b.Employment_System_ID INNER JOIN Position_Info d ON b.Position_System_ID=d.Position_System_ID WHERE CAST(position_end_date AS DATE)<'{3}' {4} AND (c.employment_status=1 OR year(c.date_terminate_from_agency)>='{1}') ) aa )b ) )a LEFT JOIN emp_Healthbilling b ON a.EInfo_id=b.EInfo_id AND b.Year = '{1}' AND b.id = (SELECT TOP 1 aa.id FROM emp_Healthbilling aa WHERE aa.EInfo_id = a.EInfo_id ORDER BY aa.Month DESC) INNER JOIN Employment_Control c ON a.Person_System_ID = c.Person_System_ID AND c.Update_Date = (SELECT Max(Update_Date) FROM Employment_Control WHERE Person_System_ID = a.Person_System_ID) INNER JOIN Employment_Info d ON c.Employment_System_ID = d.Employment_System_ID INNER JOIN Position_Info g ON c.Position_System_ID = g.Position_System_ID LEFT JOIN person_address e ON e.Person_System_ID = a.Person_System_ID AND e.address_id = (SELECT max(address_id) FROM person_address where Person_System_ID=a.Person_System_ID) LEFT JOIN person_phone f ON f.Person_System_ID = a.Person_System_ID AND f.phone_id= (SELECT max(phone_id) FROM person_phone where Person_System_ID=a.Person_System_ID) LEFT JOIN ref_department h ON h.dept_id=g.dept_id WHERE a.EInfo_id NOT IN(7840,7940,7950,7960) {0} ORDER BY a.EInfo_id", Condition, startDate.Year, startDate, endDate, employeeType); #endregion using (SqlDataReader dr = SqlHelperIRS.ExecuteReader(CommandType.Text, strSQL)) { while (dr.Read()) { Employees.Add(new IRSEmployee() { Department = dr["Department"].ToString(), DepartId = dr["DepartId"].ToInt(), Name_First = dr["Name_First"].ToString(), Name_Middle = dr["Name_Middle"].ToString(), Name_Last = dr["Name_Last"].ToString(), Address = dr["Address"].ToString(), C1 = dr["1C"].ToString(), City = dr["City"].ToString(), D1 = dr["1D"].ToString(), E1 = dr["1E"].ToString(), EinfoID = dr["EInfo_id"].ToInt(), EmployeeName = dr["EmployeeName"].ToString(), F1 = dr["1F"].ToString(), HireDate = Convert.ToDateTime(dr["HireDate"].ToString()), terminateDate = !dr.IsDBNull(dr.GetOrdinal("terminateDate")) && !string.IsNullOrEmpty(dr["terminateDate"].ToString()) ? Convert.ToDateTime(dr["terminateDate"].ToString()) : DateTime.MinValue, DOB = !dr.IsDBNull(dr.GetOrdinal("Date_Birth")) && !string.IsNullOrEmpty(dr["Date_Birth"].ToString()) ? Convert.ToDateTime(dr["Date_Birth"].ToString()) : DateTime.MinValue, Month = dr["Month"].ToString(), Name = dr["Name"].ToString(), SSN = dr["SSN"].ToString(), State = dr["State"].ToString(), Status = dr["Status"].ToString(), Year = dr["Year"].ToString(), ZipCode = dr["ZipCode"].ToString(), isRehire = Convert.ToBoolean(Convert.ToInt16(dr["isRehire"])), ClosingDate = startDate }); } } return(Employees); }
private static void Init(int Year) { if (Output == null) { Output = new Dictionary <string, string>(); Dictionary <string, string> input = new Dictionary <string, string>(); SetInner(IRSQueryDB.ALE1094CSoure()); input.Add("Check1", "false"); input.Add("Check2", "false"); DateTime dtStart = new DateTime(Year + 1, 1, 1).AddDays(-1); DateTime dtEnd = new DateTime(Year, 1, 1); /*Please double check your formula for calculating 1094C items 18 and 20 to make sure it captures/include all employees in active status for at least one day of the selected year (e.g. 2015).*/ var employees = DBSourceFactory.GetDBSource(Year); string sigDate = IRSQueryDB.Code15Date(Year); if (!string.IsNullOrEmpty(sigDate)) { input.Add("Text21", sigDate); } input.Add("Text17", employees.Count.ToString()); input.Add("Check3", "true"); input.Add("Check4", "false"); input.Add("Text18", employees.Count.ToString()); input.Add("Check5", "true"); input.Add("Check9", "true");//计算B列 input.Add("Check10", "true"); /*如果当前月为7月,则往前推一年再+1个月, 再-1天可以得到2014/7/1 - 2014/7/31 */ //DateTime startDate = new DateTime(DateTime.Now.AddYears(-1).Year, DateTime.Now.AddYears(-1).Month + 1, 1); //DateTime endDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1); //DateTime startDate = new DateTime() DateTime dtTemp = dtEnd; int hire = 0; //int terminate = 0; Dictionary <int, int> ColumnB = new Dictionary <int, int>(); Dictionary <int, int> ColumnC = new Dictionary <int, int>(); while (dtTemp <= dtStart) { /*Calculate total number of Regular Full Time”, “Regular Part Time” and “Part Time 30 HRs for each month of the selected Year and enter values*/ string strSQL = string.Format("SELECT count(*) FROM Employment_Info WHERE employee_type IN ('001','002','003') AND date_entry_to_agency < '{0}' AND employment_status=1", dtTemp); hire = int.Parse(SqlHelperIRS.ExecuteScalarToStr(System.Data.CommandType.Text, strSQL)); ColumnB.Add(dtTemp.Month, hire); /*Count ALL Employees including those terminated for the selected year. (count Regular Full Time”, “Regular Part Time” , “Part Time 30 HRs, Part time 20 hrs and substitutes)*/ //strSQL = string.Format("SELECT count(*) FROM (SELECT DISTINCT Employee_ID FROM Employment_Info WHERE date_terminate_from_agency BETWEEN '{0}' AND '{1}' )A", dtTemp.AddMonths(-1), dtTemp.AddDays(-1)); //terminate = int.Parse(SqlHelper.ExecuteScalarToStr(CommandType.Text, strSQL)); //ColumnC.Add(dtTemp.Month, terminate); var tmp = IRSQueryDB.loadAllEmployees("", dtTemp.AddMonths(1).AddDays(-1), dtTemp, EnumIRSTotalNumber.ColumnC); ColumnC.Add(dtTemp.Month, tmp.Count); dtTemp = dtTemp.AddMonths(1); } /*Since box D is checked on line 22: it is not required to complete column (b). Please remove your calculations for column B.*/ var cb = input.FirstOrDefault(p => p.Key == "Check9" && p.Value == "true").Value; if (string.IsNullOrEmpty(cb)) { //Column B input.Add("Text25", ColumnB.FirstOrDefault(p => p.Key == 1).Value.ToString()); input.Add("Text28", ColumnB.FirstOrDefault(p => p.Key == 2).Value.ToString()); input.Add("Text31", ColumnB.FirstOrDefault(p => p.Key == 3).Value.ToString()); input.Add("Text34", ColumnB.FirstOrDefault(p => p.Key == 4).Value.ToString()); input.Add("Text37", ColumnB.FirstOrDefault(p => p.Key == 5).Value.ToString()); input.Add("Text40", ColumnB.FirstOrDefault(p => p.Key == 6).Value.ToString()); input.Add("Text43", ColumnB.FirstOrDefault(p => p.Key == 7).Value.ToString()); input.Add("Text46", ColumnB.FirstOrDefault(p => p.Key == 8).Value.ToString()); input.Add("Text49", ColumnB.FirstOrDefault(p => p.Key == 9).Value.ToString()); input.Add("Text52", ColumnB.FirstOrDefault(p => p.Key == 10).Value.ToString()); input.Add("Text55", ColumnB.FirstOrDefault(p => p.Key == 11).Value.ToString()); input.Add("Text58", ColumnB.FirstOrDefault(p => p.Key == 12).Value.ToString()); } //Column C input.Add("Text26", ColumnC.FirstOrDefault(p => p.Key == 1).Value.ToString()); input.Add("Text29", ColumnC.FirstOrDefault(p => p.Key == 2).Value.ToString()); input.Add("Text32", ColumnC.FirstOrDefault(p => p.Key == 3).Value.ToString()); input.Add("Text35", ColumnC.FirstOrDefault(p => p.Key == 4).Value.ToString()); input.Add("Text38", ColumnC.FirstOrDefault(p => p.Key == 5).Value.ToString()); input.Add("Text41", ColumnC.FirstOrDefault(p => p.Key == 6).Value.ToString()); input.Add("Text44", ColumnC.FirstOrDefault(p => p.Key == 7).Value.ToString()); input.Add("Text47", ColumnC.FirstOrDefault(p => p.Key == 8).Value.ToString()); input.Add("Text50", ColumnC.FirstOrDefault(p => p.Key == 9).Value.ToString()); input.Add("Text53", ColumnC.FirstOrDefault(p => p.Key == 10).Value.ToString()); input.Add("Text56", ColumnC.FirstOrDefault(p => p.Key == 11).Value.ToString()); input.Add("Text59", ColumnC.FirstOrDefault(p => p.Key == 12).Value.ToString()); SetInner(input); } }