public static List <ArtrnMin> InvoiceList(string data_path, TransactionStatus.STATUS status = TransactionStatus.STATUS.WARRANTY, INVOICE_TYPE invoice_type = INVOICE_TYPE.IV)
{
if (!File.Exists(data_path + "artrn.dbf"))
{
throw new Exception("ค้นหาไฟล์ artrn.dbf ไม่พบ");
}
if (!File.Exists(data_path + "stcrd.dbf"))
{
throw new Exception("ค้นหาไฟล์ stcrd.dbf ไม่พบ");
}
if (!File.Exists(data_path + "stmas.dbf"))
{
throw new Exception("ค้นหาไฟล์ stmas.dbf ไม่พบ");
}
if (!File.Exists(data_path + "isacc.dbf"))
{
throw new Exception("ค้นหาไฟล์ isacc.dbf ไม่พบ");
}
if (!File.Exists(data_path + "artrnrm.dbf"))
{
throw new Exception("ค้นหาไฟล์ artrnrm.dbf ไม่พบ");
}
DataTable dt = new DataTable();
using (OleDbConnection conn = new OleDbConnection(@"Provider=VFPOLEDB.1;Data Source=" + data_path))
{
conn.Open();
var cmd = conn.CreateCommand();
switch (status)
{
case TransactionStatus.STATUS.ALL:
cmd.CommandText = "Select artrn.docnum, artrn.docdat, artrn.cuscod, artrn.num2, armas.cusnam From artrn ";
cmd.CommandText += "Left Join armas On artrn.cuscod = armas.cuscod ";
cmd.CommandText += "Where artrn.docnum In ";
cmd.CommandText += "(Select stcrd.docnum From stcrd Left Join stmas On stcrd.stkcod=stmas.stkcod Left Join isacc On stmas.acccod=isacc.acccod Where isacc.method='X') ";
cmd.CommandText += "And rectyp='" + ((int)invoice_type).ToString() + "'";
cmd.CommandText += "Order By docdat,docnum ASC";
break;
case TransactionStatus.STATUS.WARRANTY:
cmd.CommandText = "Select artrn.docnum, artrn.docdat, artrn.cuscod, artrn.num2, armas.cusnam From artrn ";
cmd.CommandText += "Left Join armas On artrn.cuscod = armas.cuscod ";
cmd.CommandText += "Where artrn.docnum In ";
cmd.CommandText += "(Select stcrd.docnum From stcrd Left Join stmas On stcrd.stkcod=stmas.stkcod Left Join isacc On stmas.acccod=isacc.acccod Where isacc.method='X') ";
cmd.CommandText += "And rectyp='" + ((int)invoice_type).ToString() + "' And num2=0 ";
cmd.CommandText += "Order By docdat,docnum ASC";
break;
case TransactionStatus.STATUS.WARRANTED:
cmd.CommandText = "Select artrn.docnum, artrn.docdat, artrn.cuscod, artrn.num2, armas.cusnam From artrn ";
cmd.CommandText += "Left Join armas On artrn.cuscod = armas.cuscod ";
cmd.CommandText += "Where artrn.docnum In ";
cmd.CommandText += "(Select stcrd.docnum From stcrd Left Join stmas On stcrd.stkcod=stmas.stkcod Left Join isacc On stmas.acccod=isacc.acccod Where isacc.method='X') ";
cmd.CommandText += "And rectyp='" + ((int)invoice_type).ToString() + "' And num2>0 ";
cmd.CommandText += "Order By docdat,docnum ASC";
break;
default:
cmd.CommandText = "Select artrn.docnum, artrn.docdat, artrn.cuscod, artrn.num2, armas.cusnam From artrn ";
cmd.CommandText += "Left Join armas On artrn.cuscod = armas.cuscod ";
cmd.CommandText += "Where artrn.docnum In ";
cmd.CommandText += "(Select stcrd.docnum From stcrd Left Join stmas On stcrd.stkcod=stmas.stkcod Left Join isacc On stmas.acccod=isacc.acccod Where isacc.method='X') ";
cmd.CommandText += "And rectyp='" + ((int)invoice_type).ToString() + "'";
cmd.CommandText += "Order By docdat,docnum ASC";
break;
}
//if (status == TransactionStatus.STATUS.WARRANTY)
//{
// cmd.CommandText = "Select artrn.docnum, artrn.docdat, artrn.cuscod, artrn.num2, armas.cusnam From artrn ";
// cmd.CommandText += "Left Join armas On artrn.cuscod = armas.cuscod ";
// cmd.CommandText += "Where artrn.docnum In ";
// cmd.CommandText += "(Select stcrd.docnum From stcrd Left Join stmas On stcrd.stkcod=stmas.stkcod Left Join isacc On stmas.acccod=isacc.acccod Where isacc.method='X') ";
// cmd.CommandText += "And rectyp='" + ((int)invoice_type).ToString() + "' And num2=0 ";
// cmd.CommandText += "Order By docdat,docnum ASC";
//}
//else
//{
// cmd.CommandText = "Select artrn.docnum, artrn.docdat, artrn.cuscod, artrn.num2, armas.cusnam From artrn ";
// cmd.CommandText += "Left Join armas On artrn.cuscod = armas.cuscod ";
// cmd.CommandText += "Where artrn.docnum In ";
// cmd.CommandText += "(Select stcrd.docnum From stcrd Left Join stmas On stcrd.stkcod=stmas.stkcod Left Join isacc On stmas.acccod=isacc.acccod Where isacc.method='X') ";
// cmd.CommandText += "And rectyp='" + ((int)invoice_type).ToString() + "' ";
// cmd.CommandText += "Order By docdat,docnum ASC";
//}
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(dt);
conn.Close();
}
List <ArtrnMin> artrn = new List <ArtrnMin>();
foreach (DataRow row in dt.Rows)
{
try
{
ArtrnMin a = new ArtrnMin
{
cuscod = !row.IsNull("cuscod") ? row["cuscod"].ToString().Trim() : string.Empty,
cusnam = !row.IsNull("cusnam") ? row["cusnam"].ToString().Trim() : string.Empty,
docnum = !row.IsNull("docnum") ? row["docnum"].ToString().Trim() : string.Empty,
docdat = !row.IsNull("docdat") ? (DateTime?)row["docdat"] : null,
warranty_spec = !row.IsNull("num2") ? ((double)row["num2"] > 0 ? "Y" : "") : ""
};
artrn.Add(a);
}
catch (Exception)
{
continue;
}
}
return(artrn);
}
private void cbStatus_SelectedIndexChanged(object sender, EventArgs e)
{
this.status = (TransactionStatus.STATUS)((ComboboxItem)((ComboBox)sender).SelectedItem).Value;
this.GetInvoiceList();
}
