public DataTable list()
{
string txtQuery = string.Format("SELECT * FROM {0}", this.tableName);
try
{
return(sqlite.ExecuteDataTable(txtQuery));
}
catch (Exception ex)
{
throw ex;
}
}
internal DataTable ExcuteDatatable(string SQLStatement, string dbPath)
{
Sqlite sqlite = new Sqlite(dbPath);
DataTable dt = new DataTable();
try
{
dt = sqlite.ExecuteDataTable(SQLStatement);
}
catch (Exception ex)
{
throw ex;
}
return(dt);
}
public DataTable getReportData(string invoiceid)
{
try
{
string id = invoiceid.ToString();
string Query = @"
select item.item_name , item.quantity ,
item.price_per_unit,item.unit,
case when(item.discount isnull) then 0 else item.discount end as item_discount ,
item.item_total as item_total ,
invoice.line_total as line_total ,
invoice.discount as invoice_discount,
invoice.basis_amount as basis_amount ,
invoice.tax_total as tax_total,
invoice.grand_total as grand_total ,
invoice.invoice_name , invoice.purpose , invoice.purpose_code ,
invoice.tax_code,
invoice.tax_rate,
""false"" as charge_indicator ,
invoice.service_charge,
item.unit_xml ,
item.item_code,
item.item_code_inter,
invoice.original,
invoice.difference,
invoice.issue_date,
invoice.remark,
case when(invoice.invoice_name like ""%ภาษี%"") then 'Y' else 'N' end as invoice_debit_flag,
case when(invoice.purpose = """") then 'Y' else 'N' end as purpose_flag,
(item.item_total+(item.item_total * (tax_rate/100))) as item_total_including_tax
from invoice_item item
left
join invoice invoice on (invoice.invoice_id = item.invoice_id)
where invoice.invoice_id = ""*id""
";
Query = Query.Replace("*id", id);
DataTable dt = new System.Data.DataTable();
dt = sqlite.ExecuteDataTable(Query);
dt_item_raw = dt.Copy();
int limit = 19;
int group = 0;
int seq = 0;
int total_seq = 0;
dt.Columns.Add("seq");
dt.Columns.Add("grouping");
dt.Columns.Add("Appearance");
DataRow[] Editrows = dt.Select();
//Run Sequence
for (int i = 0; i < Editrows.Length; i++)
{
if (seq < limit)
{
Editrows[i]["grouping"] = group;
Editrows[i]["seq"] = seq;
}
else
{
seq = 0;
}
seq++;
if (seq == limit)
{
seq = 0; group++;
}
total_seq++;
}
dt.AcceptChanges();
//Add Rows Dummy for full Table
if ((dt.Rows.Count % limit) != 0)
{
DataRow[] Rows_count = dt.Select(" grouping =" + group);
while (total_seq < (group + 1) * limit)
{
dt.Rows.Add();
DataRow[] Addrow = dt.Select();
Addrow[total_seq]["grouping"] = group;
dt.AcceptChanges();
total_seq++;
}
}
// Assign Flag Y fro appearance
var max = dt.Compute("max(grouping)", string.Empty);
DataRow[] Appearance = dt.Select("grouping =" + max);
foreach (var dr in Appearance)
{
dr["Appearance"] = "Y";
}
dt.AcceptChanges();
DataSet ds = new DataSet();
ds.Tables.Add(dt);
return(dt);
}
catch (Exception ex)
{
throw ex;
}
}