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;
}