public List <ShitterOperation> GetOperationByDay(string shitterId, DateTime date)
{
//ToString("yyyy-MM-dd HH:mm:ss")
string query = "select * from shiter_operations where shit_number=" + shitterId + " and op_date='" + date.ToString("yyyy-MM-dd HH:mm:ss") + "' order by cover_type,paper_type";
DAL dal = new DAL();
DataTable dt = dal.GetDataTable(query);
List <ShitterOperation> lst = new List <ShitterOperation>();
ShitterOperation so;
foreach (DataRow row in dt.Rows)
{
so = new ShitterOperation();
so.Id = int.Parse(row["id"].ToString());
so.PaperType = row["paper_type"].ToString();
so.Customer = row["customer"].ToString();
so.CoverType = row["cover_type"].ToString();
so.ShiterNumber = row["shit_number"].ToString();
so.Amount = int.Parse(row["amount"].ToString());
so.SetupReq = row["setup_req"].ToString() == "true";
lst.Add(so);
}
return(lst);
}
private bool IsRecordExist(ShitterOperation so)
{
string query = string.Format(@"select * from shiter_operations
where amount={0} and shit_number='{1}'
and cover_type='{2}' and customer='{3}' and paper_type='{4}' and op_date='{5}'",
so.Amount, so.ShiterNumber, so.CoverType, so.Customer, so.PaperType,
so.OpDate.Date.ToString("yyyy-MM-dd HH:mm:ss"));
DAL d = new DAL();
DataTable dt = d.GetDataTable(query);
return(dt.Rows.Count > 0);
}
private ShitterOperation AddaptShitterOperation(DataTable dt)
{
if (dt.Rows.Count == 0)
{
return(new ShitterOperation());
}
ShitterOperation so = new ShitterOperation();
so.Id = int.Parse(dt.Rows[0]["id"].ToString());
so.PaperType = dt.Rows[0]["paper_type"].ToString();
so.Customer = dt.Rows[0]["customer"].ToString();
so.CoverType = dt.Rows[0]["cover_type"].ToString();
so.ShiterNumber = dt.Rows[0]["shit_number"].ToString();
so.Amount = int.Parse(dt.Rows[0]["amount"].ToString());
so.SetupReq = dt.Rows[0]["setup_req"].ToString() == "true";
return(so);
}
protected void btnSearch_Click(object sender, EventArgs e)
{
DateTime selectedDate = DateTime.Parse(Calendar1.SelectedDate.Year + "/" + Calendar1.SelectedDate.Month + "/" + Calendar1.SelectedDate.Day + " 00:00:00");
string id = ddlShiters.SelectedItem.Value;
if (id == "select one")
{
return;
}
if (sender != null)
{
Session["SUBMITED_SHITTER"] = id;
Session["SUBMITED_SHITTER_DATE"] = selectedDate;
}
string query = "select * from shiter_operations where shit_number=" + id + " and op_date='" + selectedDate.ToString("yyyy-MM-dd HH:mm:ss") + "' and not status=2";
DAL dal = new DAL();
operationsDt = dal.GetDataTable(query);
if (operationsDt.Rows.Count > 0)
{
ShitterOperation so;
foreach (DataRow row in operationsDt.Rows)
{
so = new ShitterOperation();
so.ShiterNumber = row["shit_number"].ToString();
so.Id = int.Parse(row["id"].ToString());
so.Amount = int.Parse(row["amount"].ToString());
so.PartialAmount = int.Parse(row["partial_amount"].ToString());
so.CoverType = row["cover_type"].ToString();
so.Customer = row["customer"].ToString();
so.PaperType = row["paper_type"].ToString();
so.OpDate = DateTime.Parse(row["op_date"].ToString());
so.SetupReq = row["setup_req"].ToString() == "1";
so.PartialAmountPercent = so.PartialAmount == 0 ? 0 : ((double)so.PartialAmount / (double)so.Amount) * 100;
shitterOperations.Add(so);
}
}
}
protected void ddlShiters_SelectedIndexChanged(object sender, EventArgs e)
{
string id = ddlShiters.SelectedItem.Value;
Session["SELECTED_EDIT_SHITTER"] = id;
if (id == "select one")
{
return;
}
string query = @"select * from shiter_operations
inner join shiters
on shiters.shit_number=shiter_operations.shit_number
where shiter_operations.shit_number=" + id;
DAL dal = new DAL();
operationsDt = dal.GetDataTable(query);
if (operationsDt.Rows.Count > 0)
{
ShitterOperation so;
foreach (DataRow row in operationsDt.Rows)
{
so = new ShitterOperation();
so.ShiterNumber = row["shit_number"].ToString();
so.Id = int.Parse(row["id"].ToString());
so.Amount = int.Parse(row["amount"].ToString());
so.PartialAmount = int.Parse(row["partial_amount"].ToString());
so.CoverType = row["cover_type"].ToString();
so.Customer = row["customer"].ToString();
so.PaperType = row["paper_type"].ToString();
so.OpDate = DateTime.Parse(row["op_date"].ToString());
so.SetupReq = row["setup_req"].ToString() == "1";
so.PartialAmountPercent = so.PartialAmount == 0 ? 0 : ((double)so.PartialAmount / (double)so.Amount) * 100;
shitterOperations.Add(so);
}
}
}
protected void btnUpload_Click(object sender, EventArgs e)
{
if (!FileUpload1.HasFile)
{
// No CSV file selected
return;
}
using (StreamReader sr = new StreamReader(FileUpload1.PostedFile.InputStream))
{
FileHelperEngine engine = new FileHelperEngine(typeof(ShitterOperationExcel));
string query = "";
ShitterOperation so;
DAL dal = new DAL();
int counter = 0;
List <ShitterOperation> shitterOperations = new List <ShitterOperation>();
bool isFileIsValid = true;
string validations = "";
try
{
foreach (ShitterOperationExcel entry in engine.ReadStream(sr))
{
var datStr = entry.OpDate.Split(' ')[0];
var arr = datStr.Split('/');
so = new ShitterOperation();
so.ShiterNumber = entry.ShiterNumber;
so.Amount = entry.Amount;
so.CoverType = entry.CoverType;
so.Customer = entry.Customer;
so.OpDate = new DateTime(int.Parse(arr[2]), int.Parse(arr[1]), int.Parse(arr[0]));
so.PaperType = entry.PaperType;
so.SetupReq = entry.SetupReq == "1";
so.PartialAmount = 0;
shitterOperations.Add(so);
}
}
catch (Exception ex)
{
var newEx = (FileHelpers.ConvertException)ex;
validations += newEx.FieldName + " - " + newEx.Message + "\n";
isFileIsValid = false;
}
if (!isFileIsValid)
{
MessageBox.Show("File is not valid please fix before upload\n" + validations);
}
else
{
foreach (var shitterOperation in shitterOperations)
{
if (IsRecordExist(shitterOperation))
{
continue;
}
counter++;
query = string.Format(@"insert into shiter_operations (amount,shit_number,cover_type,customer,paper_type,op_date,setup_req,active,partial_amount,status)
values({0},{1},'{2}','{3}','{4}', '{5}' ,'{6}',1,0,0)",
shitterOperation.Amount,
shitterOperation.ShiterNumber,
shitterOperation.CoverType,
shitterOperation.Customer, shitterOperation.PaperType,
shitterOperation.OpDate.Date.ToString("yyyy-MM-dd HH:mm:ss"), shitterOperation.SetupReq ? "1" : "0");
int aff = dal.DoQuery(query);
}
}
if (counter > 0)
{
MessageBox.Show("Upload successfuly " + counter + " items");
}
}
}
