public List<PaymentAge> GetInvoicePaymentAgeList(string cardCode) { StringBuilder oSQL = new StringBuilder(); oSQL.Append("select seriesName, docNum, CardCode,cardName,DocDate,DocDueDate,DocTotal - PaidToDate pendingToPay, DATEDIFF(dd,DocDueDate,getdate()) pendingTime, numAtCard, "); oSQL.Append("CASE when DATEDIFF(dd,DocDueDate,getdate()) <= 30 then DocTotal - PaidToDate else 0 End c30, "); oSQL.Append("CASE when DATEDIFF(dd,DocDueDate,getdate()) between 31 and 60 then DocTotal - PaidToDate else 0 End c60, "); oSQL.Append("CASE when DATEDIFF(dd,DocDueDate,getdate()) between 61 and 90 then DocTotal - PaidToDate else 0 End c90, "); oSQL.Append("CASE when DATEDIFF(dd,DocDueDate,getdate()) between 91 and 120 then DocTotal - PaidToDate else 0 End c120, "); oSQL.Append("CASE when DATEDIFF(dd,DocDueDate,getdate()) > 120 then DocTotal - PaidToDate else 0 End c121 "); oSQL.Append(string.Format("from OINV a inner join NNM1 b on a.series = b.series where a.cardcode = '{0}' and PaidToDate != DocTotal", cardCode)); DbCommand myCommand = this.dataBase.GetSqlStringCommand(oSQL.ToString()); List<PaymentAge> documents = new List<PaymentAge>(); using (this.reader = this.dataBase.ExecuteReader(myCommand)) { while (this.reader.Read()) { PaymentAge document = new PaymentAge(); document.seriesName = this.reader.IsDBNull(0) ? "" : this.reader.GetValue(0).ToString(); document.docNum = this.reader.IsDBNull(1) ? "" : this.reader.GetValue(1).ToString(); document.cardCode = this.reader.IsDBNull(2) ? "" : this.reader.GetValue(2).ToString(); document.cardName = this.reader.IsDBNull(3) ? "" : this.reader.GetValue(3).ToString(); document.docDate = DateTime.Parse(this.reader.GetValue(4).ToString()).ToString("yyyy-MM-dd"); document.docDueDate = DateTime.Parse(this.reader.GetValue(5).ToString()).ToString("yyyy-MM-dd"); document.pendingToPay = this.reader.IsDBNull(6) ? 0 : double.Parse(this.reader.GetValue(6).ToString()); document.pendingTime = this.reader.IsDBNull(7) ? 0 : double.Parse(this.reader.GetValue(7).ToString()); document.numAtCard = this.reader.IsDBNull(8) ? "" : this.reader.GetValue(8).ToString(); document.up30 = this.reader.IsDBNull(9) ? 0 : double.Parse(this.reader.GetValue(9).ToString()); document.up60 = this.reader.IsDBNull(10) ? 0 : double.Parse(this.reader.GetValue(10).ToString()); document.up90 = this.reader.IsDBNull(11) ? 0 : double.Parse(this.reader.GetValue(11).ToString()); document.up120 = this.reader.IsDBNull(12) ? 0 : double.Parse(this.reader.GetValue(12).ToString()); document.up9999 = this.reader.IsDBNull(13) ? 0 : double.Parse(this.reader.GetValue(13).ToString()); documents.Add(document); } } return documents; }
public List<PaymentAge> GetPaymentAgeList(string cardCode) { string today = DateTime.Now.ToString("yyyy-MM-dd"); StringBuilder oSQL = new StringBuilder(); oSQL.Append("select case when transType = -3 then 'CB' when transType = 13 then 'FA' when transType = 14 then 'RC' when transType = 15 then 'NE' when transType = 16 then 'DV' when transType = 18 then 'TT' "); oSQL.Append("when transType = 19 then 'TP' when transType = 20 then 'EP' when transType = 21 then 'DM' when transType = 24 then 'PR' when transType = 25 then 'DP' when transType = 30 then 'AS' "); oSQL.Append("when transType = 46 then 'PP' when transType = 59 then 'EM' when transType = 60 then 'SM' when transType = 67 then 'IM' when transType = 69 then 'DI' when transType = 162 then 'RI' "); oSQL.Append("when transType = 202 then 'OF' when transType = 204 then 'AN' when transType = 321 then 'ID' end seriesName, "); oSQL.Append(string.Format("docNum, '{0}' cardCode, (select cardname from ocrd where cardcode = '{1}') cardName, ", cardCode, cardCode)); oSQL.Append("refDate docDate, dueDate docDueDate, -balDueCred pendingToPay, DATEDIFF(dd,dueDate,getdate()) pendingTime, numAtCard, "); oSQL.Append("CASE when DATEDIFF(dd,dueDate,getdate()) <= 15 then -balDueCred else 0 End c15, "); oSQL.Append("CASE when DATEDIFF(dd,dueDate,getdate()) between 16 and 30 then -balDueCred else 0 End c30, "); oSQL.Append("CASE when DATEDIFF(dd,dueDate,getdate()) between 31 and 60 then -balDueCred else 0 End c60, "); oSQL.Append("CASE when DATEDIFF(dd,dueDate,getdate()) between 61 and 90 then -balDueCred else 0 End c90, "); oSQL.Append("CASE when DATEDIFF(dd,dueDate,getdate()) between 91 and 120 then -balDueCred else 0 End c120, "); oSQL.Append("CASE when DATEDIFF(dd,dueDate,getdate()) > 120 then -balDueCred else 0 End c121 "); oSQL.Append("from (SELECT MAX(T0.[TransType])transType, MAX(T0.[BaseRef]) docNum, MAX(T0.[RefDate]) refDate, MAX(T0.[DueDate]) dueDate, "); oSQL.Append("MAX(T0.[BalDueCred]) + SUM(T1.[ReconSum]) BalDueCred, MAX(T0.[LineMemo]) LineMemo, MAX(T5.[NumAtCard]) numAtCard, MAX(T4.[DunTerm]) DunTerm "); oSQL.Append("FROM [dbo].[JDT1] T0 "); oSQL.Append("INNER JOIN [dbo].[ITR1] T1 ON T1.[TransId] = T0.[TransId] AND T1.[TransRowId] = T0.[Line_ID] "); oSQL.Append("INNER JOIN [dbo].[OITR] T2 ON T2.[ReconNum] = T1.[ReconNum] "); oSQL.Append("INNER JOIN [dbo].[OJDT] T3 ON T3.[TransId] = T0.[TransId] "); oSQL.Append("INNER JOIN [dbo].[OCRD] T4 ON T4.[CardCode] = T0.[ShortName] "); oSQL.Append("LEFT OUTER JOIN [dbo].[B1_JournalTransSourceView] T5 ON T5.[ObjType] = T0.[TransType] AND T5.[DocEntry] = T0.[CreatedBy] "); oSQL.Append("AND (T5.[TransType] <> 'I' OR (T5.[TransType] = 'I' AND T5.[InstlmntID] = T0.[SourceLine] )) "); oSQL.Append(string.Format("WHERE T0.[RefDate] <= ('{0}') AND T4.[CardType] = 'C' AND T4.[Balance] <> 0 AND T4.[CardCode] = ('{1}') AND T2.[ReconDate] > ('{2}') AND T1.[IsCredit] = 'C' ", today, cardCode, today)); oSQL.Append("GROUP BY T0.[TransId], T0.[Line_ID], T0.[BPLName] HAVING MAX(T0.[BalFcCred]) <>- SUM(T1.[ReconSumFC]) OR MAX(T0.[BalDueCred]) <>- SUM(T1.[ReconSum]) "); oSQL.Append("UNION ALL "); oSQL.Append("SELECT MAX(T0.[TransType]), MAX(T0.[BaseRef]), MAX(T0.[RefDate]), MAX(T0.[DueDate]), - MAX(T0.[BalDueDeb]) - SUM(T1.[ReconSum]), MAX(T0.[LineMemo]), MAX(T5.[NumAtCard]), MAX(T4.[DunTerm]) "); oSQL.Append("FROM [dbo].[JDT1] T0 "); oSQL.Append("INNER JOIN [dbo].[ITR1] T1 ON T1.[TransId] = T0.[TransId] AND T1.[TransRowId] = T0.[Line_ID] "); oSQL.Append("INNER JOIN [dbo].[OITR] T2 ON T2.[ReconNum] = T1.[ReconNum] "); oSQL.Append("INNER JOIN [dbo].[OJDT] T3 ON T3.[TransId] = T0.[TransId] "); oSQL.Append("INNER JOIN [dbo].[OCRD] T4 ON T4.[CardCode] = T0.[ShortName] "); oSQL.Append("LEFT OUTER JOIN [dbo].[B1_JournalTransSourceView] T5 ON T5.[ObjType] = T0.[TransType] AND T5.[DocEntry] = T0.[CreatedBy] "); oSQL.Append("AND (T5.[TransType] <> 'I' OR (T5.[TransType] = 'I' AND T5.[InstlmntID] = T0.[SourceLine] )) "); oSQL.Append(string.Format("WHERE T0.[RefDate] <= ('{0}') AND T4.[CardType] = 'C' AND T4.[Balance] <> 0 AND T4.[CardCode] = ('{1}') AND T2.[ReconDate] > ('{2}') AND T1.[IsCredit] = 'D' ", today, cardCode, today)); oSQL.Append("GROUP BY T0.[TransId], T0.[Line_ID], T0.[BPLName] HAVING MAX(T0.[BalFcDeb]) <>- SUM(T1.[ReconSumFC]) OR MAX(T0.[BalDueDeb]) <>- SUM(T1.[ReconSum]) "); oSQL.Append("UNION ALL "); oSQL.Append("SELECT MAX(T0.[TransType]), MAX(T0.[BaseRef]), MAX(T0.[RefDate]), MAX(T0.[DueDate]), MAX(T0.[BalDueCred]) - MAX(T0.[BalDueDeb]), MAX(T0.[LineMemo]), MAX(T3.[NumAtCard]), MAX(T2.[DunTerm]) "); oSQL.Append("FROM [dbo].[JDT1] T0 "); oSQL.Append("INNER JOIN [dbo].[OJDT] T1 ON T1.[TransId] = T0.[TransId] "); oSQL.Append("INNER JOIN [dbo].[OCRD] T2 ON T2.[CardCode] = T0.[ShortName] "); oSQL.Append("LEFT OUTER JOIN [dbo].[B1_JournalTransSourceView] T3 ON T3.[ObjType] = T0.[TransType] AND T3.[DocEntry] = T0.[CreatedBy] "); oSQL.Append("AND (T3.[TransType] <> 'I' OR (T3.[TransType] = 'I' AND T3.[InstlmntID] = T0.[SourceLine] )) "); oSQL.Append(string.Format("WHERE T0.[RefDate] <= ('{0}') AND T2.[CardType] = 'C' AND T2.[Balance] <> 0 AND T2.[CardCode] = ('{1}') ", today, cardCode)); oSQL.Append("AND (T0.[BalDueCred] <> T0.[BalDueDeb] OR T0.[BalFcCred] <> T0.[BalFcDeb] ) "); oSQL.Append("AND NOT EXISTS (SELECT U0.[TransId], U0.[TransRowId] FROM [dbo].[ITR1] U0 INNER JOIN [dbo].[OITR] U1 ON U1.[ReconNum] = U0.[ReconNum] "); oSQL.Append(string.Format("WHERE T0.[TransId] = U0.[TransId] AND T0.[Line_ID] = U0.[TransRowId] AND U1.[ReconDate] > ('{0}') GROUP BY U0.[TransId], U0.[TransRowId]) ", today)); oSQL.Append("GROUP BY T0.[TransId], T0.[Line_ID], T0.[BPLName]) a "); DbCommand myCommand = this.dataBase.GetSqlStringCommand(oSQL.ToString()); List<PaymentAge> documents = new List<PaymentAge>(); using (this.reader = this.dataBase.ExecuteReader(myCommand)) { while (this.reader.Read()) { PaymentAge document = new PaymentAge(); document.seriesName = this.reader.IsDBNull(0) ? "" : this.reader.GetValue(0).ToString(); document.docNum = this.reader.IsDBNull(1) ? "" : this.reader.GetValue(1).ToString(); document.cardCode = this.reader.IsDBNull(2) ? "" : this.reader.GetValue(2).ToString(); document.cardName = this.reader.IsDBNull(3) ? "" : this.reader.GetValue(3).ToString(); document.docDate = DateTime.Parse(this.reader.GetValue(4).ToString()).ToString("yyyy-MM-dd"); document.docDueDate = DateTime.Parse(this.reader.GetValue(5).ToString()).ToString("yyyy-MM-dd"); document.pendingToPay = this.reader.IsDBNull(6) ? 0 : double.Parse(this.reader.GetValue(6).ToString()); document.pendingTime = this.reader.IsDBNull(7) ? 0 : double.Parse(this.reader.GetValue(7).ToString()); document.numAtCard = this.reader.IsDBNull(8) ? "" : this.reader.GetValue(8).ToString(); document.up15 = this.reader.IsDBNull(9) ? 0 : double.Parse(this.reader.GetValue(9).ToString()); document.up30 = this.reader.IsDBNull(10) ? 0 : double.Parse(this.reader.GetValue(10).ToString()); document.up60 = this.reader.IsDBNull(11) ? 0 : double.Parse(this.reader.GetValue(11).ToString()); document.up90 = this.reader.IsDBNull(12) ? 0 : double.Parse(this.reader.GetValue(12).ToString()); document.up120 = this.reader.IsDBNull(13) ? 0 : double.Parse(this.reader.GetValue(13).ToString()); document.up9999 = this.reader.IsDBNull(14) ? 0 : double.Parse(this.reader.GetValue(14).ToString()); documents.Add(document); } } return documents; }