public ActionResult Report8(Report8Model model)
{
//return Pdf("/reportsgen/report8/", this._report[8].ToString(), false);
return new ReportsGenController().Report8(model);
}
//public ActionResult Report8(int? id)
public ActionResult Report8(Report8Model model)
{
//string start_date = model.start_date;
//string end_date = model.end_date;
//string customer_code = model.customer_code;
//string customer_name = model.customer_name;
ArrayList where = new ArrayList();
/*
if (! String.IsNullOrWhiteSpace(start_date))
{
where.Add("q.create_date >= '" + start_date + "'");
}
if (! String.IsNullOrWhiteSpace(end_date))
{
where.Add("q.create_date <= '" + end_date + "'");
}
*/
/*
if (String.IsNullOrWhiteSpace(start_date) && String.IsNullOrWhiteSpace(end_date))
{
*/
DateTime now = DateTime.Now.AddMonths(-2);
where.Add("q.confirm_date <= '" + Common.to_date(DB_DATE_FORMAT, now) + "'");
//where.Add("q.confirm_date <> '1900-01-01'");
where.Add("q.confirm_date is not null");
where.Add("(q.invoice = '' or q.invoice is null)");
//}
if (model.dummy.HasValue)
{
switch (model.dummy.Value)
{
case 2:
where.Add(" q.dummy = 1");
break;
case 3:
where.Add(" q.dummy = 0");
break;
}
}
//if (! String.IsNullOrWhiteSpace(customer_code))
//{
// where.Add("c.customer_code LIKE '%" + customer_code + "%'");
//}
//if (! String.IsNullOrWhiteSpace(customer_name))
//{
// where.Add("c.name LIKE '%" + customer_name + "%'");
//}
if (model.MasterCustomerID.HasValue)
where.Add("c.customer_id = " + model.MasterCustomerID.Value);
string sql = Common.doSql(@"
SELECT
q.id, q.number, q.confirm_date, q.remark,
q.issue_date, q.lang, (q.supplier_id+q.material_estimation2+q.material_estimation3) AS material_estimation, c.customer_code, c.name AS customer_name,
c.address1, c.address2, c.tel1, c.fax1, c.contact,
c.title, q.dummy, c.tel2, c.fax2
FROM {p}_quotation AS q
LEFT JOIN {p}_relate_customers AS c ON q.customer_id = c.id
WHERE q.status = '1'
" + (where.Count > 0 ? " AND " + String.Join(" AND ", where.ToArray()) : String.Empty) + " ORDER BY q.billing_date ASC");
IEnumerable<report8> quotation = Common.queryToObject<report8>(this._db.ExecuteStoreQuery<report8>(sql));
if (quotation.Count() > 0)
{
ArrayList qid = new ArrayList();
Hashtable items = new Hashtable();
foreach (report8 item in quotation)
{
qid.Add(item.id);
items.Add(item.id, item);
}
if (qid.Count > 0)
{
sql = Common.doSql(@"
SELECT
r.id1, i.*
FROM {p}_quotation_items AS i
LEFT JOIN {p}_relate AS r ON r.id2 = i.id
WHERE r.table1 = 'quotation'
AND r.table2 = 'quotation_items'
AND r.id1 IN ('" + String.Join("','", qid.ToArray()) + @"')
ORDER BY r.id1 ASC, i.id ASC");
IEnumerable<report7_items> quotation_items = Common.queryToObject<report7_items>(this._db.ExecuteStoreQuery<report7_items>(sql));
if (quotation_items.Count() > 0)
{
foreach (report8 item in quotation)
{
item.customer_code = trimZero(item.customer_code);
item.items = quotation_items.Where(theItem => theItem.id1 == item.id).OrderBy(theItem => theItem.nSequence).ToList();
}
}
else
{
foreach (report8 item in quotation)
{
item.items = new List<report7_items>();
}
}
}
ViewBag.title = this._title;
sql = "SELECT * FROM recsys_system_parameter s WHERE s.status = '1'";
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["reportConnection"].ConnectionString);
using (connection)
{
SqlCommand command = new SqlCommand(sql, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
if (reader["name"].ToString().Equals("company_eng_name_68"))
ViewBag.Name_Eng = reader["value"].ToString().Substring(0, 3);
}
}
reader.Close();
}
return View("~/Views/ReportsGen/Report8.cshtml", quotation);
}
else
{
return Content("找不到任何紀錄。");
}
}