private void updated_Status_changed_email(string user)
{
if (trip.Statuscode == "C")
{
CtmJob job = Manager.ORManager.GetObject(new Wilson.ORMapper.OPathQuery <CtmJob>("JobNo='" + trip.JobNo + "'"));
CtmJobDet1 det1 = Manager.ORManager.GetObject <CtmJobDet1>(trip.Det1Id);
if (job != null)
{
//EmailTemplateBiz emailBz = new EmailTemplateBiz();
//EmailTemplate temp = null;
//temp = emailBz.getTempalte(job.ClientId, "Trip"+trip.TripCode, job.EmailAddress);
//if (temp != null)
//{
// string contNo = "";
// string sealNo = "";
// if (det1 != null)
// {
// contNo = det1.ContainerNo;
// sealNo = det1.SealNo;
// }
// if (emailBz.sendEmail(job.ClientRefNo, contNo, sealNo, job.Vessel, job.Voyage, job.EtaDate, job.Pod,job.Pol, temp).status)
// {
// CtmJobEventLog l = new CtmJobEventLog();
// l.Controller = user;
// l.JobNo = trip.JobNo;
// l.Platform = "Web";
// l.Note1Type = "Email";
// l.Remark = "TripComplete email to:" + temp.EmailTo;
// l.log();
// }
//}
}
}
}
private bool inserting_verify(BizResult bzR, string user, CtmJobDet1 par)
{
bool res = false;
string role = SafeValue.SafeString(ConnectSql.ExecuteScalar(string.Format("select role from [user] where Name='{0}'", user)));
CtmJob job = Manager.ORManager.GetObject(new Wilson.ORMapper.OPathQuery(typeof(C2.CtmJob), "JobNo='" + par.JobNo + "'")) as CtmJob;
if (job == null)
{
bzR.context = "Can not find this job";
}
else
{
if (role.ToLower() == "client")
{
switch (job.JobStatus)
{
case "Booked":
res = true;
break;
case "Cancel":
bzR.context = "This job have been cancel";
break;
case "Reject":
case "Confirmed":
case "Completed":
case "Closed":
default:
bzR.context = "No permission";
break;
}
}
else
{
switch (job.JobStatus)
{
case "Booked":
case "Cancel":
bzR.context = "Requair confirm this job";
break;
case "Completed":
case "Closed":
bzR.context = "This Job have completed";
break;
default:
res = true;
break;
}
}
}
return(res);
}
private void inserted_auto_CreateTrip(string user)
{
CtmJob job = Manager.ORManager.GetObject(new Wilson.ORMapper.OPathQuery <CtmJob>("JobNo='" + det1.JobNo + "'"));
if (job != null)
{
CtmJobDet2Biz det2Bz = new CtmJobDet2Biz(0);
CtmJobDet2 det2 = new CtmJobDet2();
det2.JobNo = det1.JobNo;
det2.Det1Id = det1.Id;
det2.ContainerNo = det1.ContainerNo;
det2.FromDate = det1.ScheduleDate;
det2.FromTime = det1.ScheduleTime;
det2.ToDate = det1.ScheduleDate;
det2.ToTime = det1.ScheduleTime;
det2.Statuscode = "P";
det2.SubletFlag = "N";
det2.Overtime = "Normal";
det2.OverDistance = "N";
if (job.JobType == "IMP")
{
det2.TripCode = "IMP";
det2.FromCode = job.PickupFrom;
det2.ToCode = job.DeliveryTo;
det2Bz.insert(user, det2);
det2.TripCode = "RET";
det2.FromCode = job.DeliveryTo;
det2.ToCode = det1.YardAddress;
det2Bz.insert(user, det2);
}
if (job.JobType == "EXP")
{
det2.TripCode = "COL";
det2.FromCode = det1.YardAddress;
det2.ToCode = job.PickupFrom;
det2Bz.insert(user, det2);
det2.TripCode = "EXP";
det2.FromCode = job.PickupFrom;
det2.ToCode = job.DeliveryTo;
det2Bz.insert(user, det2);
}
if (job.JobType == "LOC")
{
det2.TripCode = "LOC";
det2.FromCode = job.PickupFrom;
det2.ToCode = job.DeliveryTo;
det2Bz.insert(user, det2);
}
}
}
private void updating_verify_diffJobType_tripType(BizResult bzR, CtmJob job, CtmJobDet2 par)
{
if (job != null && trip != null)
{
if (par.TripCode.Equals("IMP") || par.TripCode.Equals("EXP"))
{
if (job != null)
{
if (job.JobType.Equals("EXP") && par.TripCode.Equals("IMP"))
{
bzR.context = "Export Job can not save IMP trip";
}
if (job.JobType.Equals("IMP") && par.TripCode.Equals("EXP"))
{
bzR.context = "Import Job can not save EXP trip";
}
}
}
}
}
private string add_job(string client)
{
DateTime now = DateTime.Now;
string jobNo = "";
C2.CtmJob job = new C2.CtmJob();
jobNo = C2Setup.GetNextNo("", "CTM_Job_WGR", DateTime.Now);
job.JobNo = jobNo;
job.JobDate = DateTime.Now;
job.ClientId = client;
job.DeliveryTo = "";
job.StatusCode = "USE";
job.QuoteNo = jobNo;
job.QuoteStatus = "None";
job.JobStatus = "Confirmed";
job.JobType = "WGR";
C2.Manager.ORManager.StartTracking(job, Wilson.ORMapper.InitialState.Inserted);
C2.Manager.ORManager.PersistChanges(job);
C2Setup.SetNextNo("", "CTM_Job_WGR", jobNo, now);
return(jobNo);
}
private MemoryStream PrintHaulierSub(string orderNo, string jobType, string haulierCode)
{
XtraReport rpt = new XtraReport();
rpt.LoadLayout(Server.MapPath(@"~\PagesContTrucking\Report\repx\HaulierTruckingAdviceSub.repx"));
rpt.DataSource = DocPrint.PrintHaulierSub(orderNo, jobType, haulierCode);
QR q = new QR();
Wilson.ORMapper.OPathQuery query = new Wilson.ORMapper.OPathQuery(typeof(C2.CtmJob), "JobNo='" + orderNo + "'");
C2.CtmJob job = C2.Manager.ORManager.GetObject(query) as C2.CtmJob;
string text = string.Format(@"JobNo:" + orderNo + ",JobDate:" + job.JobDate.ToString("dd/MM/yyyy"));
Bitmap bt = q.Create_QR(text);
string path = MapPath("~/files/barcode/");
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
string fileName = orderNo + ".png";
string filePath = path + fileName;
if (File.Exists(filePath))
{
File.Delete(filePath);
}
bt.Save(Server.MapPath("~/files/barcode/") + fileName);
DevExpress.XtraReports.UI.XRPictureBox qr_code = rpt.Report.FindControl("barcode", true) as DevExpress.XtraReports.UI.XRPictureBox;
if (qr_code != null)
{
qr_code.ImageUrl = "/files/barcode/" + fileName;
}
System.IO.MemoryStream str = new MemoryStream();
rpt.ExportToPdf(str);
return(str);
}
private string create_job(DateTime now)
{
C2.CtmJob job = new C2.CtmJob();
string jobNo = C2Setup.GetNextNo("", "CTM_Job_WDO", DateTime.Now);
job.JobNo = jobNo;
job.JobDate = DateTime.Now;
string client = System.Configuration.ConfigurationManager.AppSettings["EdiClient"];
job.ClientId = EzshipHelper.GetPartyId(client);
job.DeliveryTo = "";
job.JobType = "WDO";
job.StatusCode = "USE";
job.Contractor = "YES";
job.HaulierId = job.ClientId;
job.QuoteNo = jobNo;
job.JobStatus = "Confirmed";
job.QuoteStatus = "None";
job.QuoteDate = DateTime.Today;
C2.Manager.ORManager.StartTracking(job, Wilson.ORMapper.InitialState.Inserted);
C2.Manager.ORManager.PersistChanges(job);
C2Setup.SetNextNo("", "CTM_Job_WDO", jobNo, now);
return(jobNo);
}
protected void grid_CustomDataCallback(object sender, DevExpress.Web.ASPxGridView.ASPxGridViewCustomDataCallbackEventArgs e)
{
string par = e.Parameters;
if (par == "Save")
{
if (list.Count > 0)
{
for (int i = 0; i < list.Count; i++)
{
string no = SafeValue.SafeString(Request.QueryString["no"]);
int id = list[i].id;
Wilson.ORMapper.OPathQuery query = new Wilson.ORMapper.OPathQuery(typeof(C2.CtmJob), "Id='" + id + "'");
C2.CtmJob ctmJob = C2.Manager.ORManager.GetObject(query) as C2.CtmJob;
ctmJob.BillingRefNo = no;
ctmJob.ShowInvoice_Ind = SafeValue.SafeString(cmb_IsShow.Value);
C2.Manager.ORManager.StartTracking(ctmJob, Wilson.ORMapper.InitialState.Updated);
C2.Manager.ORManager.PersistChanges(ctmJob);
}
e.Result = "Action Success!";
}
}
}
private void set_signed_barcode(XtraReport rpt, string orderNo, string httpPath, string filename)
{
QR q = new QR();
Wilson.ORMapper.OPathQuery query = new Wilson.ORMapper.OPathQuery(typeof(C2.CtmJob), "JobNo='" + orderNo + "'");
C2.CtmJob job = C2.Manager.ORManager.GetObject(query) as C2.CtmJob;
//string text = string.Format(@"JobNo:" + orderNo);
Bitmap bt = q.Create_QR(httpPath);
string path = MapPath("~/files/barcode/");
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
string fileName = orderNo + ".png";
string filePath = path + fileName;
if (File.Exists(filePath))
{
File.Delete(filePath);
}
bt.Save(Server.MapPath("~/files/barcode/") + fileName);
DevExpress.XtraReports.UI.XRPictureBox qr_code = rpt.Report.FindControl("barcode", true) as DevExpress.XtraReports.UI.XRPictureBox;
if (qr_code != null)
{
qr_code.ImageUrl = "/files/barcode/" + fileName;
}
string sql_trip = string.Format(@"select top 1 Id from ctm_jobdet2 where JobNo='{0}'", orderNo);
string tripId = ConnectSql_mb.ExecuteScalar(sql_trip);
string Signature_Consignee = "";
string Signature_Driver = "";
string signature_time = "";
string signature_time1 = "";
string sql_signature = string.Format(@"select Id,FileType,FileName,FilePath,FileNote,CreateDateTime From CTM_Attachment where FileType='Signature' and RefNo=@RefNo and charindex(@sType, FileNote,0)>0 and TripId=@tripId");
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@RefNo", orderNo, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@sType", "Consignee", SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@tripId", tripId, SqlDbType.Int));
DataTable dt = ConnectSql_mb.GetDataTable(sql_signature, list);
if (dt.Rows.Count > 0)
{
Signature_Consignee = dt.Rows[0]["FilePath"].ToString();
signature_time = dt.Rows[0]["CreateDateTime"].ToString();
}
list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@RefNo", orderNo, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@sType", "Driver", SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@tripId", tripId, SqlDbType.Int));
dt = ConnectSql_mb.GetDataTable(sql_signature, list);
if (dt.Rows.Count > 0)
{
Signature_Driver = dt.Rows[0]["FilePath"].ToString();
signature_time1 = dt.Rows[0]["CreateDateTime"].ToString();
}
DevExpress.XtraReports.UI.XRPictureBox signature = rpt.Report.FindControl("signature", true) as DevExpress.XtraReports.UI.XRPictureBox;
if (signature != null)
{
signature.ImageUrl = Signature_Consignee;
}
DevExpress.XtraReports.UI.XRLabel time = rpt.Report.FindControl("lbl_time", true) as DevExpress.XtraReports.UI.XRLabel;
if (time != null)
{
time.Text = signature_time;
}
DevExpress.XtraReports.UI.XRPictureBox signature1 = rpt.Report.FindControl("signature1", true) as DevExpress.XtraReports.UI.XRPictureBox;
if (signature1 != null)
{
signature1.ImageUrl = Signature_Driver;
}
DevExpress.XtraReports.UI.XRLabel time1 = rpt.Report.FindControl("lbl_time1", true) as DevExpress.XtraReports.UI.XRLabel;
if (time1 != null)
{
time1.Text = signature_time1;
}
}
static bool contStatusCompleted_createCost(int contId, string BillClass)
{
bool res = false;
string sql = null;
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
sql = string.Format(@"select det1.JobNo,job.JobType,job.ClientId,det1.ContainerNo,det1.ContainerType,det1.BillType
from ctm_jobdet1 as det1
left outer join ctm_job as job on det1.jobno=job.JobNo
where det1.Id=@contId");
list.Add(new ConnectSql_mb.cmdParameters("@contId", contId, SqlDbType.Int));
DataTable dt_jobdet1 = ConnectSql_mb.GetDataTable(sql, list);
if (dt_jobdet1.Rows.Count > 0)
{
res = true;
string JobNo = dt_jobdet1.Rows[0]["JobNo"].ToString();
string JobType = dt_jobdet1.Rows[0]["JobType"].ToString();
string ClientId = dt_jobdet1.Rows[0]["ClientId"].ToString();
string BillType = dt_jobdet1.Rows[0]["BillType"].ToString();
//=========== job level cost creat
CtmJob.jobCost_Create(JobNo, JobType, ClientId, BillClass);
decimal gst = 0;
string gstType = "";
string chgTypeId = "";
string lineSource = "S";
#region container level cost
string ContainerNo = dt_jobdet1.Rows[0]["ContainerNo"].ToString().Replace(" ", "");
string ContainerType = dt_jobdet1.Rows[0]["ContainerType"].ToString().Replace(" ", "");
#region JobType
string sql_rate = string.Format(@"select Id,ChgCode,ChgCodeDes,Qty,Price,GstType,BillClass,BillScope,CurrencyId,ExRate,ContSize,ContType,JobType from job_rate where ClientId=@ClientId and BillScope=@LineType and BillClass=@BillClass and BillType=@BillType and JobType=@JobType and JobNo='-1' and LineStatus=@LineStatus");
list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@ClientId", ClientId, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@LineType", "CONT", SqlDbType.NVarChar, 30));
list.Add(new ConnectSql_mb.cmdParameters("@BillClass", BillClass, SqlDbType.NVarChar, 30));
list.Add(new ConnectSql_mb.cmdParameters("@BillType", BillType, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@JobType", JobType, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@LineStatus", "N", SqlDbType.NVarChar, 30));
DataTable dt_rate = ConnectSql_mb.GetDataTable(sql_rate, list);
if (dt_rate.Rows.Count == 0)
{
#region CLient=STD
sql_rate = string.Format(@"select Id,ChgCode,ChgCodeDes,Qty,Price,GstType,BillClass,BillScope,CurrencyId,ExRate,ContSize,ContType,JobType from job_rate where ClientId=@ClientId and BillScope=@LineType and BillClass=@BillClass and BillType=@BillType and JobType=@JobType and JobNo='-1' and LineStatus=@LineStatus");
list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@ClientId", "STD", SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@LineType", "CONT", SqlDbType.NVarChar, 30));
list.Add(new ConnectSql_mb.cmdParameters("@BillClass", BillClass, SqlDbType.NVarChar, 30));
list.Add(new ConnectSql_mb.cmdParameters("@BillType", BillType, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@JobType", JobType, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@LineStatus", "N", SqlDbType.NVarChar, 30));
dt_rate = ConnectSql_mb.GetDataTable(sql_rate, list);
lineSource = "D";
#endregion
}
for (int i = 0; i < dt_rate.Rows.Count; i++)
{
string chgCode = SafeValue.SafeString(dt_rate.Rows[i]["ChgCode"]);
string chgCodeDes = SafeValue.SafeString(dt_rate.Rows[i]["ChgCodeDes"]);
gstType = SafeValue.SafeString(dt_rate.Rows[i]["GstType"]);
string sql_chgCode = string.Format(@"select top 1 ChgcodeId,ChgcodeDes,GstP,GstTypeId,ChgTypeId from XXChgCode where ChgcodeId='{0}'", chgCode);
DataTable dt_chgCode = ConnectSql.GetTab(sql_chgCode);
if (dt_chgCode.Rows.Count > 0)
{
gst = SafeValue.SafeDecimal(dt_chgCode.Rows[0]["GstP"]);
gstType = SafeValue.SafeString(dt_chgCode.Rows[0]["GstTypeId"]);
chgTypeId = SafeValue.SafeString(dt_chgCode.Rows[0]["ChgTypeId"]);
}
int id = SafeValue.SafeInt(dt_rate.Rows[i]["Id"], 0);
string contType = SafeValue.SafeString(dt_rate.Rows[i]["ContType"]);
string contSize = SafeValue.SafeString(dt_rate.Rows[i]["ContSize"]);
string ContSize = (ContainerType.Length >= 2 ? ContainerType.Substring(0, 2) : "0");
string jobType = SafeValue.SafeString(dt_rate.Rows[i]["JobType"]);
sql = string.Format(@"select Id from job_cost where LineType=@LineType and JobNo=@JobNo and BillClass=@BillClass and JobType=@JobType and LineSource=@LineSource and ContNo=@ContainerNo and ChgCode=@ChgCode");
list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@LineType", "CONT", SqlDbType.NVarChar, 30));
list.Add(new ConnectSql_mb.cmdParameters("@BillClass", BillClass, SqlDbType.NVarChar, 30));
list.Add(new ConnectSql_mb.cmdParameters("@LineSource", "S", SqlDbType.NVarChar, 10));
list.Add(new ConnectSql_mb.cmdParameters("@JobNo", JobNo, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@JobType", JobType, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@ContainerNo", ContainerNo, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@ChgCode", chgCode, SqlDbType.NVarChar, 100));
DataTable dt = ConnectSql_mb.GetDataTable(sql, list);
if (dt.Rows.Count <= 0)
{
if (contSize.Length > 0 && contType.Length > 0)
{
#region TRUCKING
if (chgCodeDes.ToUpper().Contains("TRUCKING"))
{
if (ContainerType == contType && (ContainerType == "20HD" || ContainerType == "40HD"))
{
Create_Cost(dt_rate.Rows[i], JobNo, ContainerNo, ContainerType, BillType, ClientId, JobType, BillClass, gst, gstType, lineSource);
}
else if ((ContainerType.Substring(0, 2) == contSize && ContainerType != "20HD") || (ContainerType.Substring(0, 2) == contSize && ContainerType != "40HD"))
{
Create_Cost(dt_rate.Rows[i], JobNo, ContainerNo, ContainerType, BillType, ClientId, JobType, BillClass, gst, gstType, lineSource);
}
}
else
{
Create_Cost(dt_rate.Rows[i], JobNo, ContainerNo, ContainerType, BillType, ClientId, JobType, BillClass, gst, gstType, lineSource);
}
#endregion
}
else if (contSize.Length > 0)
{
#region WAREHOUSE
if (ContainerType.Length > 2 && ContainerType.Substring(0, 2) == contSize)
{
Create_Cost(dt_rate.Rows[i], JobNo, ContainerNo, ContainerType, BillType, ClientId, JobType, BillClass, gst, gstType, lineSource);
}
#endregion
}
else
{
Create_Cost(dt_rate.Rows[i], JobNo, ContainerNo, ContainerType, BillType, ClientId, JobType, BillClass, gst, gstType, lineSource);
}
}
}
#endregion
#region No JobType
list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@ClientId", ClientId, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@LineType", "CONT", SqlDbType.NVarChar, 30));
list.Add(new ConnectSql_mb.cmdParameters("@BillClass", BillClass, SqlDbType.NVarChar, 30));
list.Add(new ConnectSql_mb.cmdParameters("@BillType", BillType, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@JobType", "", SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@LineStatus", "N", SqlDbType.NVarChar, 30));
dt_rate = ConnectSql_mb.GetDataTable(sql_rate, list);
if (dt_rate.Rows.Count == 0)
{
#region CLient=STD
lineSource = "D";
sql_rate = string.Format(@"select Id,ChgCode,ChgCodeDes,Qty,Price,GstType,BillClass,BillScope,CurrencyId,ExRate,ContSize,ContType,JobType from job_rate where ClientId=@ClientId and BillScope=@LineType and BillClass=@BillClass and BillType=@BillType and JobType=@JobType and JobNo='-1' and LineStatus=@LineStatus");
list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@ClientId", "STD", SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@LineType", "CONT", SqlDbType.NVarChar, 30));
list.Add(new ConnectSql_mb.cmdParameters("@BillClass", BillClass, SqlDbType.NVarChar, 30));
list.Add(new ConnectSql_mb.cmdParameters("@BillType", BillType, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@JobType", "", SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@LineStatus", "N", SqlDbType.NVarChar, 30));
dt_rate = ConnectSql_mb.GetDataTable(sql_rate, list);
#endregion
}
for (int i = 0; i < dt_rate.Rows.Count; i++)
{
string chgCode = SafeValue.SafeString(dt_rate.Rows[i]["ChgCode"]);
string chgCodeDes = SafeValue.SafeString(dt_rate.Rows[i]["ChgCodeDes"]);
int id = SafeValue.SafeInt(dt_rate.Rows[i]["Id"], 0);
string contType = SafeValue.SafeString(dt_rate.Rows[i]["ContType"]);
string contSize = SafeValue.SafeString(dt_rate.Rows[i]["ContSize"]);
string ContSize = (ContainerType.Length >= 2 ? ContainerType.Substring(0, 2) : "0");
string jobType = SafeValue.SafeString(dt_rate.Rows[i]["JobType"]);
sql = string.Format(@"select Id from job_cost where LineType=@LineType and JobNo=@JobNo and BillClass=@BillClass and JobType=@JobType and LineSource=@LineSource and ContNo=@ContainerNo and ChgCode=@ChgCode");
list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@LineType", "CONT", SqlDbType.NVarChar, 30));
list.Add(new ConnectSql_mb.cmdParameters("@BillClass", BillClass, SqlDbType.NVarChar, 30));
list.Add(new ConnectSql_mb.cmdParameters("@LineSource", "S", SqlDbType.NVarChar, 10));
list.Add(new ConnectSql_mb.cmdParameters("@JobNo", JobNo, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@JobType", JobType, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@ContainerNo", ContainerNo, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@ChgCode", chgCode, SqlDbType.NVarChar, 100));
DataTable dt = ConnectSql_mb.GetDataTable(sql, list);
if (dt.Rows.Count <= 0)
{
Create_Cost(dt_rate.Rows[i], JobNo, ContainerNo, ContainerType, BillType, ClientId, JobType, BillClass, gst, gstType, lineSource);
}
}
#endregion
#endregion
}
Create_PSALOLO(contId, BillClass);
return(res);
}
private void job_save()
{
try
{
ASPxPageControl pageControl = this.grid_job.FindEditFormTemplateControl("pageControl") as ASPxPageControl;
ASPxTextBox txt_JobNo = pageControl.FindControl("txt_JobNo") as ASPxTextBox;
string jobNo = SafeValue.SafeString(txt_JobNo.Text, "");
Wilson.ORMapper.OPathQuery query = new Wilson.ORMapper.OPathQuery(typeof(C2.CtmJob), "JobNo='" + jobNo + "'");
C2.CtmJob ctmJob = C2.Manager.ORManager.GetObject(query) as C2.CtmJob;
ASPxDateEdit jobDate = pageControl.FindControl("txt_JobDate") as ASPxDateEdit;
bool isNew = false;
if (ctmJob == null)
{
isNew = true;
ctmJob = new C2.CtmJob();
ctmJob.JobNo = C2Setup.GetNextNo("", "CTM_Job", jobDate.Date);
}
ASPxDateEdit jobEta = pageControl.FindControl("date_Eta") as ASPxDateEdit;
ASPxDateEdit jobEtd = pageControl.FindControl("date_Etd") as ASPxDateEdit;
//ASPxButtonEdit partyId = pageControl.FindControl("btn_PartyId") as ASPxButtonEdit;
ASPxTextBox ves = pageControl.FindControl("txt_Ves") as ASPxTextBox;
ASPxTextBox voy = pageControl.FindControl("txt_Voy") as ASPxTextBox;
//ASPxButtonEdit carrier = pageControl.FindControl("btn_CarrierId") as ASPxButtonEdit;
//ASPxTextBox CarrierBlNo = pageControl.FindControl("txt_CarrierBlNo") as ASPxTextBox;
//ASPxTextBox CarrierBkgNo = pageControl.FindControl("txt_CarrierBkgNo") as ASPxTextBox;
ASPxComboBox Terminal = pageControl.FindControl("cbb_Terminal") as ASPxComboBox;
ASPxMemo PickupFrom = pageControl.FindControl("txt_PickupFrom") as ASPxMemo;
ASPxMemo DeliveryTo = pageControl.FindControl("txt_DeliveryTo") as ASPxMemo;
ASPxMemo Remark = pageControl.FindControl("txt_Remark") as ASPxMemo;
ASPxMemo SpecialInstruction = pageControl.FindControl("txt_SpecialInstruction") as ASPxMemo;
ASPxTextBox txt_Pol = pageControl.FindControl("txt_Pol") as ASPxTextBox;
ASPxTextBox txt_Pod = pageControl.FindControl("txt_Pod") as ASPxTextBox;
ASPxTextBox txt_EtaTime = pageControl.FindControl("txt_EtaTime") as ASPxTextBox;
ASPxTextBox txt_EtdTime = pageControl.FindControl("txt_EtdTime") as ASPxTextBox;
ASPxComboBox cbb_JobType = pageControl.FindControl("cbb_JobType") as ASPxComboBox;
//ASPxButtonEdit btn_ClientId = pageControl.FindControl("btn_ClientId") as ASPxButtonEdit;
ASPxTextBox txt_ClientRefNo = pageControl.FindControl("txt_ClientRefNo") as ASPxTextBox;
//ASPxButtonEdit btn_HaulierId = pageControl.FindControl("btn_HaulierId") as ASPxButtonEdit;
ctmJob.JobDate = SafeValue.SafeDate(jobDate.Date, new DateTime(1753, 1, 1));
ctmJob.EtaDate = SafeValue.SafeDate(jobEta.Date, new DateTime(1753, 1, 1));
ctmJob.EtdDate = SafeValue.SafeDate(jobEtd.Date, new DateTime(1753, 1, 1));
//ctmJob.PartyId = partyId.Text;
ctmJob.Vessel = ves.Text;
ctmJob.Voyage = voy.Text;
ctmJob.CarrierId = "";
ctmJob.CarrierBlNo = "";
ctmJob.CarrierBkgNo = "";
ctmJob.Terminalcode = SafeValue.SafeString(Terminal.Value);
ctmJob.PickupFrom = PickupFrom.Text;
ctmJob.DeliveryTo = DeliveryTo.Text;
ctmJob.Remark = Remark.Text;
ctmJob.SpecialInstruction = SpecialInstruction.Text;
ctmJob.Pol = txt_Pol.Text;
ctmJob.Pod = txt_Pod.Text;
ctmJob.EtaTime = txt_EtaTime.Text;
ctmJob.EtdTime = txt_EtdTime.Text;
ctmJob.JobType = cbb_JobType.Text;
ctmJob.ClientId = "";
ctmJob.ClientRefNo = txt_ClientRefNo.Text;
ctmJob.HaulierId = "";
string userId = HttpContext.Current.User.Identity.Name;
if (isNew)
{
ctmJob.StatusCode = "USE";
ctmJob.CreateBy = userId;
ctmJob.CreateDateTime = DateTime.Now;
ctmJob.UpdateBy = userId;
ctmJob.UpdateDateTime = DateTime.Now;
C2.Manager.ORManager.StartTracking(ctmJob, Wilson.ORMapper.InitialState.Inserted);
C2.Manager.ORManager.PersistChanges(ctmJob);
}
else
{
ctmJob.UpdateBy = userId;
ctmJob.UpdateDateTime = DateTime.Now;
C2.Manager.ORManager.StartTracking(ctmJob, Wilson.ORMapper.InitialState.Updated);
C2.Manager.ORManager.PersistChanges(ctmJob);
}
if (isNew)
{
txt_JobNo.Text = ctmJob.JobNo;
//txt_search_JobNo.Text = txt_JobNo.Text;
C2Setup.SetNextNo("", "CTM_Job", ctmJob.JobNo, jobDate.Date);
}
Session["CTM_Job"] = "JobNo='" + ctmJob.JobNo + "'";
this.dsJob.FilterExpression = Session["CTM_Job"].ToString();
if (this.grid_job.GetRow(0) != null)
{
this.grid_job.StartEdit(0);
}
}
catch { }
}
private MemoryStream PrintDelivery(string orderNo, string jobType)
{
XtraReport rpt = new XtraReport();
rpt.LoadLayout(Server.MapPath(@"~\PagesContTrucking\Report\repx\DeliveryOrder.repx"));
rpt.DataSource = DocPrint.PrintHaulier(orderNo, jobType);
QR q = new QR();
Wilson.ORMapper.OPathQuery query = new Wilson.ORMapper.OPathQuery(typeof(C2.CtmJob), "JobNo='" + orderNo + "'");
C2.CtmJob job = C2.Manager.ORManager.GetObject(query) as C2.CtmJob;
string text = string.Format(@"JobNo:" + orderNo + ",JobDate:" + job.JobDate.ToString("dd/MM/yyyy"));
Bitmap bt = q.Create_QR(text);
string path = MapPath("~/files/barcode/");
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
string fileName = orderNo + ".png";
string filePath = path + fileName;
if (File.Exists(filePath))
{
File.Delete(filePath);
}
bt.Save(Server.MapPath("~/files/barcode/") + fileName);
DevExpress.XtraReports.UI.XRPictureBox qr_code = rpt.Report.FindControl("barcode", true) as DevExpress.XtraReports.UI.XRPictureBox;
if (qr_code != null)
{
qr_code.ImageUrl = "/files/barcode/" + fileName;
}
string sql_trip = string.Format(@"select top 1 Id from ctm_jobdet2 where JobNo='{0}'", orderNo);
string tripId = ConnectSql_mb.ExecuteScalar(sql_trip);
string Signature_Consignee = "";
string Signature_Driver = "";
string sql_signature = string.Format(@"select Id,FileType,FileName,FilePath,FileNote From CTM_Attachment where FileType='Signature' and RefNo=@RefNo and charindex(@sType, FileNote,0)>0 and TripId=@tripId");
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@RefNo", orderNo, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@sType", "Consignee", SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@tripId", tripId, SqlDbType.Int));
DataTable dt = ConnectSql_mb.GetDataTable(sql_signature, list);
if (dt.Rows.Count > 0)
{
Signature_Consignee = dt.Rows[0]["FilePath"].ToString();
}
list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@RefNo", orderNo, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@sType", "Driver", SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@tripId", tripId, SqlDbType.Int));
dt = ConnectSql_mb.GetDataTable(sql_signature, list);
if (dt.Rows.Count > 0)
{
Signature_Driver = dt.Rows[0]["FilePath"].ToString();
}
DevExpress.XtraReports.UI.XRPictureBox signature = rpt.Report.FindControl("signature", true) as DevExpress.XtraReports.UI.XRPictureBox;
if (signature != null)
{
signature.ImageUrl = Signature_Consignee;
}
DevExpress.XtraReports.UI.XRPictureBox signature1 = rpt.Report.FindControl("signature1", true) as DevExpress.XtraReports.UI.XRPictureBox;
if (signature1 != null)
{
signature1.ImageUrl = Signature_Driver;
}
System.IO.MemoryStream str = new MemoryStream();
rpt.ExportToPdf(str);
return(str);
}
private string job_save()
{
string res = "";
try
{
ASPxPageControl pageControl = this.grid_job.FindEditFormTemplateControl("pageControl") as ASPxPageControl;
ASPxTextBox txt_JobNo = this.grid_job.FindEditFormTemplateControl("txt_JobNo") as ASPxTextBox;
string jobNo = SafeValue.SafeString(txt_JobNo.Text, "");
Wilson.ORMapper.OPathQuery query = new Wilson.ORMapper.OPathQuery(typeof(C2.CtmJob), "JobNo='" + jobNo + "'");
C2.CtmJob ctmJob = C2.Manager.ORManager.GetObject(query) as C2.CtmJob;
ASPxDateEdit jobDate = this.grid_job.FindEditFormTemplateControl("txt_JobDate") as ASPxDateEdit;
bool isNew = false;
if (ctmJob == null)
{
isNew = true;
ctmJob = new C2.CtmJob();
ctmJob.JobNo = C2Setup.GetNextNo("", "CTM_Job", jobDate.Date);
}
ASPxComboBox cbb_JobType = this.grid_job.FindEditFormTemplateControl("cbb_JobType") as ASPxComboBox;
ASPxButtonEdit btn_ClientId = this.grid_job.FindEditFormTemplateControl("btn_ClientId") as ASPxButtonEdit;
ASPxTextBox txt_ClientRefNo = this.grid_job.FindEditFormTemplateControl("txt_ClientRefNo") as ASPxTextBox;
ASPxTextBox txt_notifiEmail = this.grid_job.FindEditFormTemplateControl("txt_notifiEmail") as ASPxTextBox;
ASPxButtonEdit btn_SubClientId = this.grid_job.FindEditFormTemplateControl("btn_SubClientId") as ASPxButtonEdit;
ASPxButtonEdit txt_WareHouseId = this.grid_job.FindEditFormTemplateControl("txt_WareHouseId") as ASPxButtonEdit;
ASPxTextBox txt_ClientContact = this.grid_job.FindEditFormTemplateControl("txt_ClientContact") as ASPxTextBox;
ctmJob.ClientContact = SafeValue.SafeString(txt_ClientContact.Text);
ctmJob.JobDate = SafeValue.SafeDate(jobDate.Date, new DateTime(1753, 1, 1));
ctmJob.JobType = cbb_JobType.Text;
ctmJob.ClientId = btn_ClientId.Text;
ctmJob.ClientRefNo = txt_ClientRefNo.Text;
ctmJob.EmailAddress = txt_notifiEmail.Text;
ctmJob.SubClientId = btn_SubClientId.Text;
if (txt_WareHouseId != null)
{
ctmJob.WareHouseCode = txt_WareHouseId.Text;
}
string userId = HttpContext.Current.User.Identity.Name;
if (isNew)
{
ctmJob.StatusCode = "USE";
ctmJob.CreateBy = userId;
ctmJob.CreateDateTime = DateTime.Now;
ctmJob.UpdateBy = userId;
ctmJob.UpdateDateTime = DateTime.Now;
C2.Manager.ORManager.StartTracking(ctmJob, Wilson.ORMapper.InitialState.Inserted);
C2.Manager.ORManager.PersistChanges(ctmJob);
}
else
{
ctmJob.UpdateBy = userId;
ctmJob.UpdateDateTime = DateTime.Now;
C2.Manager.ORManager.StartTracking(ctmJob, Wilson.ORMapper.InitialState.Updated);
C2.Manager.ORManager.PersistChanges(ctmJob);
C2.CtmJobEventLog elog = new C2.CtmJobEventLog();
elog.Platform_isWeb();
elog.Controller = userId;
elog.ActionLevel_isJOB(ctmJob.Id);
elog.Remark = "Job Update";
elog.log();
}
if (isNew)
{
txt_JobNo.Text = ctmJob.JobNo;
//txt_search_JobNo.Text = txt_JobNo.Text;
C2Setup.SetNextNo("", "CTM_Job", ctmJob.JobNo, jobDate.Date);
}
//res = Job_Check_JobLevel(ctmJob.JobNo);
Session["CTM_Job_" + txt_search_JobNo.Text] = "JobNo='" + ctmJob.JobNo + "'";
this.dsJob.FilterExpression = Session["CTM_Job_" + txt_search_JobNo.Text].ToString();
if (this.grid_job.GetRow(0) != null)
{
this.grid_job.StartEdit(0);
}
}
catch { }
return(res);
}
public static DataTable DsReceipt(string recNo)
{
DataTable tab = new DataTable();
#region init column
tab.Columns.Add("Title");
tab.Columns.Add("JobType");
tab.Columns.Add("BarCode");
tab.Columns.Add("CustCode");
tab.Columns.Add("Port");
tab.Columns.Add("RecNo");
tab.Columns.Add("RecDate");
tab.Columns.Add("RecTime");
tab.Columns.Add("RecFrom");
tab.Columns.Add("Markings");
tab.Columns.Add("Contents");
tab.Columns.Add("ContNo");
tab.Columns.Add("ComplDate");
tab.Columns.Add("ComplTime");
tab.Columns.Add("Qty");
tab.Columns.Add("PackType");
tab.Columns.Add("M3");
tab.Columns.Add("Weight");
tab.Columns.Add("UserId");
tab.Columns.Add("SpcRemark");
tab.Columns.Add("InternalExternal");
tab.Columns.Add("ForkLift");
tab.Columns.Add("Dn");
tab.Columns.Add("Tracing");
tab.Columns.Add("Wh");
tab.Columns.Add("Admin");
tab.Columns.Add("Sr");
tab.Columns.Add("Removal");
tab.Columns.Add("Ot");
tab.Columns.Add("LongLength");
tab.Columns.Add("OverWeight");
tab.Columns.Add("Sticker");
tab.Columns.Add("Ugt");
tab.Columns.Add("Sec");
tab.Columns.Add("Dg");
tab.Columns.Add("Misc");
tab.Columns.Add("Other");
tab.Columns.Add("Waive");
tab.Columns.Add("Total");
tab.Columns.Add("OtherRemarks");
tab.Columns.Add("VesVoy");
tab.Columns.Add("BillAmt");
tab.Columns.Add("DoNo");
tab.Columns.Add("IsCn");
tab.Columns.Add("Cn");
tab.Columns.Add("HblN");
tab.Columns.Add("JobOrder");
tab.Columns.Add("ClearRmk");
tab.Columns.Add("VehicleUser");
tab.Columns.Add("VehicleTel");
tab.Columns.Add("VehicleNric");
tab.Columns.Add("VehicleNo1");
tab.Columns.Add("VehicleNo2");
tab.Columns.Add("VehicleNo3");
tab.Columns.Add("QtyWord");
tab.Columns.Add("TotQty");
#endregion
ObjectQuery query = new ObjectQuery(typeof(C2.CtmJob), "JobNo='" + recNo + "'", "");
ObjectSet objSet = C2.Manager.ORManager.GetObjectSet(query);
C2.CtmJob job = objSet[0] as C2.CtmJob;
ObjectQuery query1 = new ObjectQuery(typeof(C2.JobHouse), "JobNo='" + recNo + "'", "");
ObjectSet objSet1 = C2.Manager.ORManager.GetObjectSet(query1);
DataRow row = tab.NewRow();
if (objSet1.Count > 0 && objSet1[0] != null)
{
C2.JobHouse obj = objSet1[0] as C2.JobHouse;
if (job.JobType == "DO")
{
row["Title"] = "DELIVERY NOTE";
}
if (job.JobType == "GR")
{
row["Title"] = "CARGO RECEIPT";
}
if (job.JobType == "TP")
{
row["Title"] = "CARGO DIRECT RELEASE";
}
row["JobType"] = obj.JobType;
row["BarCode"] = "04-" + job.JobNo + "-00";
row["RecNo"] = obj.JobNo;
row["RecDate"] = job.JobDate.ToString("dd/MM/yyyy");
row["RecTime"] = job.JobDate.ToString("HH:mm");
row["JobOrder"] = job.JobNo;
row["RecFrom"] = EzshipHelper.GetPartyName(obj.ClientId);
row["CustCode"] = obj.ClientId;
string agCode = "";
//row["Port"] = obj.JobType=="E" ? obj.PortDisc : obj.PortLoad;
row["Markings"] = obj.Marking1;
row["Contents"] = obj.Marking2;
row["ContNo"] = obj.ContNo;
row["ComplDate"] = job.UpdateDateTime.ToString("dd/MM/yyyy");
row["ComplTime"] = job.UpdateDateTime.ToString("HH:mm");
row["HblN"] = obj.HblNo;
row["Qty"] = obj.Qty.ToString();
row["PackType"] = obj.UomCode;
row["M3"] = obj.Volume.ToString("0.000");
row["Weight"] = obj.Weight.ToString("0.000");
row["UserId"] = job.CreateBy;
string value = SafeValue.SafeString("0.00");
row["ForkLift"] = value;
row["Dn"] = value;
row["Tracing"] = value;
row["Wh"] = value;
row["Admin"] = value;
row["Sr"] = value;
row["Removal"] = value;
row["Dg"] = value;
row["Sec"] = value;
row["LongLength"] = Helper.Safe.SafeDecimal(value).ToString("0.00");
row["OverWeight"] = Helper.Safe.SafeDecimal(value).ToString("0.00");
row["Sticker"] = Helper.Safe.SafeDecimal(value).ToString("0.00");
row["Ot"] = value;
row["Ugt"] = value;
row["Misc"] = value;
row["Other"] = value;
//if (obj.WaiveAmt == 0)
// row["Waive"] = "0.00";
//else
// row["Waive"] = "(" + obj.WaiveAmt.ToString("0.00") + ")";
row["Total"] = value;
row["OtherRemarks"] = obj.Remark1;
row["IsCn"] = "DELIVERY NOTE/OFFICIAL RECEIPT";
row["Cn"] = "";
row["VesVoy"] = job.Vessel + " / " + job.Voyage;
row["DoNo"] = "";
row["ClearRmk"] = "";
row["VehicleUser"] = "";
row["VehicleTel"] = "";
row["VehicleNric"] = "";
row["VehicleNo1"] = "";
row["VehicleNo2"] = "";
row["VehicleNo3"] = "";
NumberToMoney num = new NumberToMoney();
string s = num.NumberToCurrency(Convert.ToDecimal(obj.Qty));
row["QtyWord"] = s + " " + obj.UomCode;
row["TotQty"] = obj.Qty;
row["SpcRemark"] = "";
row["InternalExternal"] = S.Text("") == "CO-LOAD" ? "Internal" : "External";
}
tab.Rows.Add(row);
return(tab);
}
public static DataSet PrintImpHaulier(string orderNo)
{
DataSet set = new DataSet();
ObjectQuery query = new ObjectQuery(typeof(C2.CtmJob), "JobNo='" + orderNo + "'", "");
ObjectSet objSet = C2.Manager.ORManager.GetObjectSet(query);
if (objSet.Count == 0 || objSet[0] == null)
{
return(set);
}
C2.CtmJob obj = objSet[0] as C2.CtmJob;
ObjectQuery query1 = new ObjectQuery(typeof(C2.CtmJobDet1), "JobNo='" + orderNo + "'", "ContainerNo");
ObjectSet objSet1 = C2.Manager.ORManager.GetObjectSet(query1);
#region init column
DataTable mast = new DataTable("Mast");
DataTable det = new DataTable("Det");
mast.Columns.Add("JobOrderNo");
mast.Columns.Add("Haulier");
mast.Columns.Add("OceanBl");
mast.Columns.Add("Yard");
mast.Columns.Add("Carrier");
mast.Columns.Add("CrNo");
mast.Columns.Add("Contact1");
mast.Columns.Add("Contact2");
mast.Columns.Add("Tel1");
mast.Columns.Add("Fax1");
mast.Columns.Add("Terminal");
mast.Columns.Add("Permit");
mast.Columns.Add("LastDate");
mast.Columns.Add("Portnet");
mast.Columns.Add("Ft20");
mast.Columns.Add("Ft40");
mast.Columns.Add("Ft45");
mast.Columns.Add("DeliveryTo");
mast.Columns.Add("VesselNo");
mast.Columns.Add("VoyNo");
mast.Columns.Add("Etd");
mast.Columns.Add("Eta");
mast.Columns.Add("PortLoad");
mast.Columns.Add("ByWho");
mast.Columns.Add("ImpRefNo");
mast.Columns.Add("CustName");
mast.Columns.Add("AgentName");
mast.Columns.Add("UserData2");
mast.Columns.Add("UserData3");
mast.Columns.Add("UserData4");
mast.Columns.Add("Outside");
mast.Columns.Add("Note1");
mast.Columns.Add("Note2");
mast.Columns.Add("StampUser");
det.Columns.Add("Idx");
det.Columns.Add("JobOrderNo");
det.Columns.Add("ContNo");
det.Columns.Add("SealNo");
det.Columns.Add("FtSize");
det.Columns.Add("FtRemark");
det.Columns.Add("FtType");
det.Columns.Add("FtKgs");
det.Columns.Add("FtQty");
det.Columns.Add("FtPack");
det.Columns.Add("FtTruckIn");
det.Columns.Add("FtCbm");
det.Columns.Add("FtNom");
#endregion
DataRow row = mast.NewRow();
string sql_haulier = "select Name,CrNo,Contact1,Contact2,Tel1,Fax1 from xxparty where partyid='" + obj.HaulierId + "'";
DataTable tab_hauler = Helper.Sql.List(sql_haulier);
row["Haulier"] = "";
row["CrNo"] = "";
row["Contact1"] = "";
row["Contact2"] = "";
row["Tel1"] = "";
row["Fax1"] = "";
if (tab_hauler.Rows.Count > 0)
{
row["Haulier"] = tab_hauler.Rows[0]["Name"];
row["CrNo"] = tab_hauler.Rows[0]["CrNo"].ToString();
row["Contact1"] = tab_hauler.Rows[0]["Contact1"].ToString();
row["Contact2"] = tab_hauler.Rows[0]["Contact2"].ToString();
row["Tel1"] = tab_hauler.Rows[0]["Tel1"].ToString();
row["Fax1"] = tab_hauler.Rows[0]["Fax1"].ToString();
}
row["JobOrderNo"] = orderNo;
row["VesselNo"] = obj.Vessel;
row["VoyNo"] = obj.Voyage;
row["DeliveryTo"] = Helper.Sql.One(string.Format(@"select Address from xxparty where PartyId='{0}'", obj.ClientId));
row["Ft20"] = 0;
row["Ft40"] = 0;
row["Ft45"] = 0;
string sql = "select name from xxparty where partyid='" + SafeValue.SafeInt(obj.ClientId, 0) + "'";
row["CustName"] = SafeValue.SafeString(Helper.Sql.One(sql), "");
sql = "select name from xxparty where partyid='" + SafeValue.SafeInt(obj.CarrierId, 0) + "'";
row["AgentName"] = SafeValue.SafeString(Helper.Sql.One(sql), "");
row["Etd"] = obj.EtdDate.ToString("dd/MM/yyyy");
row["Eta"] = obj.EtaDate.ToString("dd/MM/yyyy");
sql = "select name from [XXPort] where code='" + SafeValue.SafeString(obj.Pol, "") + "'";
row["PortLoad"] = SafeValue.SafeString(Helper.Sql.One(sql), obj.Pol);
row["Carrier"] = obj.CarrierId;
row["OceanBl"] = "";
row["Yard"] = obj.YardRef;
row["ByWho"] = obj.CreateBy;
row["ImpRefNo"] = obj.ClientRefNo;
row["UserData2"] = "";
row["Note1"] = obj.SpecialInstruction;
row["StampUser"] = obj.CreateBy;
mast.Rows.Add(row);
for (int i = 0; i < objSet1.Count; i++)
{
C2.CtmJobDet1 cont = objSet1[i] as C2.CtmJobDet1;
DataRow row1 = det.NewRow();
string contType = cont.ContainerType;
row1["JobOrderNo"] = orderNo;
row1["ContNo"] = cont.ContainerNo;
row1["SealNo"] = cont.SealNo;
string ftSize = "";
if (contType.Substring(0, 2) == "20")
{
ftSize = "20";
}
if (contType.Substring(0, 2) == "40")
{
ftSize += "40";
}
if (contType.Substring(0, 2) == "45")
{
ftSize += "45";
}
row1["FtSize"] = ftSize;
row1["FtType"] = "";
row1["FtQty"] = cont.Qty;
row1["FtKgs"] = cont.Weight.ToString("0");
row1["FtCbm"] = cont.Volume.ToString("#,##0.00");
row1["FtTruckIn"] = "TRUCK-IN:" + cont.ScheduleDate.ToString("dd-MMM");
det.Rows.Add(row1);
}
set.Tables.Add(mast);
set.Tables.Add(det);
set.Relations.Add("", mast.Columns["JobOrderNo"], det.Columns["JobOrderNo"]);
return(set);
}
public static DataSet DsImpTs(string orderNo, string docType)
{
DataSet set = new DataSet();
if (docType == "IV" || docType == "IV1")
{
ObjectQuery query = new ObjectQuery(typeof(C2.XAArInvoice), "DocNo='" + orderNo + "'", "");
ObjectSet objSet = C2.Manager.ORManager.GetObjectSet(query);
if (objSet.Count == 0 || objSet[0] == null)
{
return(new DataSet());
}
C2.XAArInvoice obj = objSet[0] as C2.XAArInvoice;
#region Invoice
DataTable mast = InitMastDataTable();
DataRow row = mast.NewRow();
string mastRefNo = obj.MastRefNo;
string mastType = obj.MastType;
Wilson.ORMapper.OPathQuery job = new Wilson.ORMapper.OPathQuery(typeof(C2.CtmJob), "JobNo='" + mastRefNo + "'");
C2.CtmJob ctmJob = C2.Manager.ORManager.GetObject(job) as C2.CtmJob;
if (ctmJob != null)
{
row["Vessel"] = ctmJob.Vessel;
row["Voyage"] = ctmJob.Voyage;
row["Eta"] = ctmJob.EtaDate.ToString("dd.MM.yy");
row["CustRefNo"] = ctmJob.ClientRefNo;
row["Pol"] = ctmJob.Pol;
row["Pod"] = ctmJob.Pod;
row["CrBkgNo"] = ctmJob.CarrierBkgNo;
row["HblNo"] = ctmJob.CarrierBlNo;
}
row["JobNo"] = mastRefNo;
row["BillType"] = "TAX INVOICE";
row["DocNo"] = obj.DocNo;
row["DocDate"] = obj.DocDate.ToString("dd/MM/yyyy");
row["DocDueDate"] = obj.DocDueDate.ToString("dd/MM/yyyy");
row["UserId"] = HttpContext.Current.User.Identity.Name;
row["PartyName"] = obj.PartyName;
row["PartyTo"] = obj.PartyTo;
string sql = string.Format(@"select Address,Contact1,Fax1,Tel1,CrNo from XXParty where PartyId='{0}'", obj.PartyTo);
DataTable tab = ConnectSql_mb.GetDataTable(sql);
if (tab.Rows.Count > 0)
{
row["PartyAdd"] = tab.Rows[0]["Address"].ToString();
row["Contact1"] = tab.Rows[0]["Contact1"].ToString();
row["Fax1"] = tab.Rows[0]["Fax1"].ToString();
row["Tel1"] = tab.Rows[0]["Tel1"].ToString();
row["CrNo"] = tab.Rows[0]["CrNo"].ToString();
}
row["Terms"] = obj.Term;
row["Terms"] = obj.Term;
row["CompanyName"] = System.Configuration.ConfigurationManager.AppSettings["CompanyName"];
row["MoneyWords"] = "";
row["Remark"] = SafeValue.SafeString(ConnectSql.ExecuteScalar(string.Format(@"select Description from XAArInvoice where MastRefNo='{0}'", mastRefNo)));
decimal subTotal = 0;
decimal gstTotal = obj.TaxAmt;
DataTable details = InitDetailDataTable();
sql = string.Format(@"select ChgCode,Qty,MastType,DocNo,ChgDes1,Price,Gst,GstAmt,Unit,
LineLocAmt, Currency,ExRate,
LocAmt,JobRefNo,GstType from XAArInvoiceDet where DocId={0} order by Gst desc", obj.SequenceId);
DataTable det = ConnectSql.GetTab(sql);
for (int i = 0; i < det.Rows.Count; i++)
{
DataRow row1 = details.NewRow();
string cntType = SafeValue.SafeString(det.Rows[i]["JobRefNo"]);
string chgCode = SafeValue.SafeString(det.Rows[i]["ChgCode"]);
row1["No"] = i + 1;
row1["DocNo"] = SafeValue.SafeString(det.Rows[i]["DocNo"]);
row1["ChgDes1"] = SafeValue.SafeString(det.Rows[i]["ChgDes1"]);
row1["ChgCode"] = SafeValue.SafeString(det.Rows[i]["ChgCode"]);
row1["LineAmt"] = SafeValue.SafeString(det.Rows[i]["LineLocAmt"]);
row1["Unit"] = SafeValue.SafeString(det.Rows[i]["Unit"]);
row1["Currency"] = SafeValue.SafeString(det.Rows[i]["Currency"]);
row1["ExRate"] = SafeValue.SafeString(det.Rows[i]["ExRate"]);
row1["Price"] = string.Format("{0:#,##0.00}", SafeValue.SafeString(det.Rows[i]["Price"]));
row1["Gst"] = SafeValue.SafeInt(SafeValue.SafeDecimal(det.Rows[i]["Gst"]) * 100, 0) + "% " + det.Rows[i]["GstType"] + "R";
decimal amt = SafeValue.ChinaRound(SafeValue.SafeInt(det.Rows[i]["Qty"], 0) * SafeValue.SafeDecimal(det.Rows[i]["Price"], 0), 2);
decimal gstAmt = SafeValue.ChinaRound((amt * SafeValue.SafeDecimal(det.Rows[i]["Gst"], 0)), 2);
row1["Amt"] = amt;
row1["GstAmt"] = SafeValue.SafeString(det.Rows[i]["GstAmt"]);
row1["LocAmt"] = SafeValue.SafeString(det.Rows[i]["LocAmt"]);
row1["Qty"] = SafeValue.SafeInt(det.Rows[i]["Qty"], 0);
details.Rows.Add(row1);
subTotal += amt;
}
sql = string.Format(@"select ContainerNo,SealNo,ContainerType from CTM_JobDet1 where JobNo='{0}' ", mastRefNo);
DataTable cnt = ConnectSql.GetTab(sql);
for (int i = 0; i < cnt.Rows.Count; i++)
{
if (cnt.Rows.Count - i > 1)
{
row["ContainerNo"] += SafeValue.SafeString(cnt.Rows[i]["ContainerNo"]) + " / " + SafeValue.SafeString(cnt.Rows[i]["SealNo"])
+ " / " + SafeValue.SafeString(cnt.Rows[i]["ContainerType"]) + "\n";
}
else
{
row["ContainerNo"] += SafeValue.SafeString(cnt.Rows[i]["ContainerNo"]) + " / " + SafeValue.SafeString(cnt.Rows[i]["SealNo"])
+ " / " + SafeValue.SafeString(cnt.Rows[i]["ContainerType"]);
}
}
row["SubTotal"] = subTotal;
row["GstTotal"] = gstTotal;
row["TotalAmt"] = subTotal + gstTotal;
mast.Rows.Add(row);
#endregion
set.Tables.Add(mast);
set.Tables.Add(details);
}
if (docType == "PL" || docType == "PL1")
{
ObjectQuery query = new ObjectQuery(typeof(C2.XAApPayable), "DocNo='" + orderNo + "'", "");
ObjectSet objSet = C2.Manager.ORManager.GetObjectSet(query);
if (objSet.Count == 0 || objSet[0] == null)
{
return(new DataSet());
}
C2.XAApPayable obj = objSet[0] as C2.XAApPayable;
#region PL
DataTable mast = InitMastDataTable();
DataRow row = mast.NewRow();
string mastRefNo = obj.MastRefNo;
string mastType = obj.MastType;
if (mastType == "CTM")
{
Wilson.ORMapper.OPathQuery job = new Wilson.ORMapper.OPathQuery(typeof(C2.CtmJob), "JobNo='" + mastRefNo + "'");
C2.CtmJob ctmJob = C2.Manager.ORManager.GetObject(job) as C2.CtmJob;
row["Vessel"] = ctmJob.Vessel;
row["Voyage"] = ctmJob.Voyage;
row["Eta"] = ctmJob.EtaDate.ToString("dd.MM.yy");
}
row["DocNo"] = obj.DocNo;
row["DocDate"] = obj.DocDate.ToString("dd/MM/yyyy");
row["SupplierBillNo"] = obj.SupplierBillNo;
row["SupplierBillDate"] = obj.SupplierBillDate.ToString("dd/MM/yyyy");
row["PartyName"] = obj.PartyName;
string sql = string.Format(@"select Address from XXParty where PartyId='{0}'", obj.PartyTo);
row["PartyAdd"] = C2.Manager.ORManager.ExecuteScalar(sql);
row["Terms"] = obj.Term;
decimal subTotal = 0;
decimal gstTotal = 0;
DataTable details = InitDetailDataTable();
sql = string.Format(@"select ChgCode,sum(Qty) as Qty,MastType,max(DocNo) DocNo,max(ChgDes1) as ChgDes1,Price,max(Gst) Gst,max(GstAmt) GstAmt,
max(LocAmt) LocAmt,max(JobRefNo) JobRefNo,max(GstType) GstType from XAApPayableDet where DocId={0} group by ChgCode,MastType,Price order by Gst desc", obj.SequenceId);
DataTable det = ConnectSql.GetTab(sql);
for (int i = 0; i < det.Rows.Count; i++)
{
DataRow row1 = details.NewRow();
string cntType = SafeValue.SafeString(det.Rows[i]["JobRefNo"]);
string chgCode = SafeValue.SafeString(det.Rows[i]["ChgCode"]);
row1["DocNo"] = SafeValue.SafeString(det.Rows[i]["DocNo"]);
row1["ChgDes1"] = SafeValue.SafeString(det.Rows[i]["ChgDes1"]);
row1["ChgCode"] = SafeValue.SafeString(det.Rows[i]["ChgCode"]);
row1["Price"] = string.Format("{0:#,##0.00}", SafeValue.SafeString(det.Rows[i]["Price"]));
row1["Gst"] = SafeValue.SafeInt(SafeValue.SafeDecimal(det.Rows[i]["Gst"]) * 100, 0) + "% " + det.Rows[i]["GstType"] + "R";
decimal amt = SafeValue.ChinaRound(SafeValue.SafeInt(det.Rows[i]["Qty"], 0) * SafeValue.SafeDecimal(det.Rows[i]["Price"], 0), 2);
decimal gstAmt = SafeValue.ChinaRound((amt * SafeValue.SafeDecimal(det.Rows[i]["Gst"], 0)), 2);
row1["Amt"] = amt;
row1["GstAmt"] = SafeValue.SafeString(det.Rows[i]["GstAmt"]);
row1["LocAmt"] = SafeValue.SafeString(det.Rows[i]["LocAmt"]);
row1["Qty"] = SafeValue.SafeInt(det.Rows[i]["Qty"], 0);
details.Rows.Add(row1);
subTotal += amt;
gstTotal += gstAmt;
}
row["SubTotal"] = subTotal;
row["GstTotal"] = gstTotal;
row["Total"] = subTotal + gstTotal;
mast.Rows.Add(row);
#endregion
set.Tables.Add(mast);
set.Tables.Add(details);
}
return(set);
}
protected void grid_wh_CustomDataCallback(object sender, ASPxGridViewCustomDataCallbackEventArgs e)
{
if (Request.QueryString["no"] != null)
{
try
{
if (list.Count > 0)
{
string no = SafeValue.SafeString(Request.QueryString["no"]);
string jobType = SafeValue.SafeString(Request.QueryString["jobType"]);
string jobNo = "";
Wilson.ORMapper.OPathQuery query = new Wilson.ORMapper.OPathQuery(typeof(C2.CtmJob), "JobNo='" + no + "'");
C2.CtmJob job = C2.Manager.ORManager.GetObject(query) as C2.CtmJob;
if (job != null)
{
jobNo = C2Setup.GetNextNo("", "CTM_Job_DO", DateTime.Today);
job.JobNo = jobNo;
job.QuoteNo = "";
job.JobType = "DO";
job.JobDate = DateTime.Now;
job.JobStatus = "Confirmed";
C2.Manager.ORManager.StartTracking(job, Wilson.ORMapper.InitialState.Inserted);
C2.Manager.ORManager.PersistChanges(job);
C2Setup.SetNextNo("", "CTM_Job_DO", jobNo, DateTime.Today);
}
for (int i = 0; i < list.Count; i++)
{
int id = list[i].id;
decimal qty = list[i].qty;
decimal weight = list[i].weight;
decimal volume = list[i].volume;
decimal sku_qty = list[i].sku_qty;
decimal l = list[i].l;
decimal h = list[i].h;
decimal w = list[i].w;
string refNo = list[i].refNo;
if (qty > 0)
{
Wilson.ORMapper.OPathQuery query1 = new Wilson.ORMapper.OPathQuery(typeof(C2.JobHouse), "Id=" + id + "");
C2.JobHouse house = C2.Manager.ORManager.GetObject(query1) as C2.JobHouse;
house.CargoType = "OUT";
house.JobNo = jobNo;
house.ContNo = "";
house.JobType = jobType;
house.QtyOrig = qty;
house.PackQty = sku_qty;
house.Weight = weight;
house.Volume = volume;
house.WeightOrig = weight;
house.VolumeOrig = volume;
house.LandStatus = "Normal";
house.DgClass = "Normal";
house.DamagedStatus = "Normal";
house.LengthPack = l;
house.WidthPack = w;
house.HeightPack = h;
house.RefNo = no;
house.Qty = qty;
house.OpsType = "Delivery";
C2.Manager.ORManager.StartTracking(house, Wilson.ORMapper.InitialState.Inserted);
C2.Manager.ORManager.PersistChanges(house);
}
else
{
}
}
e.Result = "Success";
}
else
{
e.Result = "Pls Select at least one Cargo";
}
}
catch { }
}
}
public static DataSet PrintHaulier(string orderNo, string jobType)
{
DataSet set = new DataSet();
ObjectQuery query = new ObjectQuery(typeof(C2.CtmJob), "JobNo='" + orderNo + "'", "");
ObjectSet objSet = C2.Manager.ORManager.GetObjectSet(query);
if (objSet.Count == 0 || objSet[0] == null)
{
return(set);
}
C2.CtmJob obj = objSet[0] as C2.CtmJob;
ObjectQuery query1 = new ObjectQuery(typeof(C2.CtmJobDet1), "JobNo='" + orderNo + "'", "");
ObjectSet objSet1 = C2.Manager.ORManager.GetObjectSet(query1);
#region init column
DataTable mast = new DataTable("Mast");
DataTable det = new DataTable("Det");
mast.Columns.Add("JobNo");
mast.Columns.Add("JobType");
mast.Columns.Add("Haulier");
mast.Columns.Add("Attn");
mast.Columns.Add("DeliveryTo");
mast.Columns.Add("OceanBl");
mast.Columns.Add("Yard");
mast.Columns.Add("Carrier");
mast.Columns.Add("CrNo");
mast.Columns.Add("Contact1");
mast.Columns.Add("Contact2");
mast.Columns.Add("Tel1");
mast.Columns.Add("Fax1");
mast.Columns.Add("Terminal");
mast.Columns.Add("Permit");
mast.Columns.Add("LastDate");
mast.Columns.Add("Portnet");
mast.Columns.Add("Ft20");
mast.Columns.Add("Ft40");
mast.Columns.Add("Ft45");
mast.Columns.Add("Vessel");
mast.Columns.Add("Voyage");
mast.Columns.Add("Etd");
mast.Columns.Add("Eta");
mast.Columns.Add("Pol");
mast.Columns.Add("ByWho");
mast.Columns.Add("ClientRefNo");
mast.Columns.Add("CustName");
mast.Columns.Add("AgentName");
mast.Columns.Add("UserData2");
mast.Columns.Add("UserData3");
mast.Columns.Add("UserData4");
mast.Columns.Add("Outside");
mast.Columns.Add("Note1");
mast.Columns.Add("Note2");
mast.Columns.Add("StampUser");
mast.Columns.Add("Remark");
mast.Columns.Add("SpecialInstruction");
det.Columns.Add("Idx");
det.Columns.Add("JobNo");
det.Columns.Add("ContNo");
det.Columns.Add("SealNo");
det.Columns.Add("FtSize");
det.Columns.Add("FtRemark");
det.Columns.Add("FtType");
det.Columns.Add("FtKgs");
det.Columns.Add("FtQty");
det.Columns.Add("FtPack");
det.Columns.Add("FtTruckIn");
det.Columns.Add("FtCbm");
det.Columns.Add("FtNom");
#endregion
DataRow row = mast.NewRow();
string sql_haulier = "select Name,CrNo,Contact1,Contact2,Tel1,Fax1 from xxparty where partyid='" + obj.ClientId + "'";
DataTable tab_hauler = Helper.Sql.List(sql_haulier);
row["Attn"] = obj.Terminalcode;
row["DeliveryTo"] = obj.DeliveryTo;
row["CrNo"] = "";
row["Contact1"] = "";
row["Contact2"] = "";
row["Tel1"] = "";
row["Fax1"] = "";
if (tab_hauler.Rows.Count > 0)
{
row["Haulier"] = tab_hauler.Rows[0]["Name"];
row["CrNo"] = tab_hauler.Rows[0]["CrNo"].ToString();
row["Contact1"] = tab_hauler.Rows[0]["Contact1"].ToString();
row["Contact2"] = tab_hauler.Rows[0]["Contact2"].ToString();
row["Tel1"] = tab_hauler.Rows[0]["Tel1"].ToString();
row["Fax1"] = tab_hauler.Rows[0]["Fax1"].ToString();
}
row["JobNo"] = orderNo;
row["JobType"] = obj.JobType;
row["Vessel"] = obj.Vessel;
row["Voyage"] = obj.Voyage;
row["Permit"] = obj.PermitNo;
string sql = "select name from xxparty where partyid='" + obj.ClientId + "'";
row["CustName"] = SafeValue.SafeString(ConnectSql.ExecuteScalar(sql), "");
row["Etd"] = obj.EtdDate.ToString("dd/MM/yyyy");
row["Eta"] = obj.EtaDate.ToString("dd/MM/yyyy");
sql = "select name from [XXPort] where code='" + SafeValue.SafeString(obj.Pol, "") + "'";
row["Pol"] = SafeValue.SafeString(Helper.Sql.One(sql), "");
sql = "select name from xxparty where partyid='" + obj.CarrierId + "'";
row["Carrier"] = SafeValue.SafeString(Helper.Sql.One(sql), "");
row["OceanBl"] = obj.CarrierBlNo;
row["Yard"] = obj.YardRef;
row["ByWho"] = "";
row["ClientRefNo"] = obj.ClientRefNo;
row["UserData2"] = "";
row["SpecialInstruction"] = obj.SpecialInstruction;
row["Remark"] = obj.Remark;
row["StampUser"] = HttpContext.Current.User.Identity.Name;
int ft20 = 0;
int ft40 = 0;
int ft45 = 0;
for (int i = 0; i < objSet1.Count; i++)
{
C2.CtmJobDet1 cont = objSet1[i] as C2.CtmJobDet1;
DataRow row1 = det.NewRow();
row1["Idx"] = i + 1;
row1["JobNo"] = orderNo;
row1["ContNo"] = cont.ContainerNo;
row1["SealNo"] = cont.SealNo;
string ftSize = "";
//int index = cont.ContainerType.IndexOf("FT", 2);
if (cont.ContainerType.IndexOf("20", 0) == 0)
{
ft20++;
}
if (cont.ContainerType.IndexOf("40", 0) == 0)
{
ft40++;
}
if (cont.ContainerType.IndexOf("45", 0) == 0)
{
ft45++;
}
row1["FtSize"] = ftSize;
row1["FtType"] = cont.ContainerType;
row1["FtQty"] = cont.Qty;
row1["FtKgs"] = cont.Weight.ToString("0");
row1["FtCbm"] = cont.Volume.ToString("#,##0.00");
row1["FtTruckIn"] = "TRUCK-IN:" + cont.ScheduleDate.ToString("dd-MMM");
det.Rows.Add(row1);
}
row["Ft20"] = ft20;
row["Ft40"] = ft40;
row["Ft45"] = ft45;
mast.Rows.Add(row);
set.Tables.Add(mast);
set.Tables.Add(det);
return(set);
}
protected void grid_CustomDataCallback(object sender, DevExpress.Web.ASPxGridView.ASPxGridViewCustomDataCallbackEventArgs e)
{
string paras = e.Parameters;
string[] ar = paras.Split('_');
if (paras == "OK")
{
#region
string jobNo = GetJobNo();
if (jobNo.Length > 0)
{
if (list.Count > 0)
{
e.Result = create_house(list.Count, -1, jobNo);
}
else
{
e.Result = "Action Error";
}
}
else
{
e.Result = "NO Export";
}
#endregion
}
if (paras == "New")
{
#region
DateTime now = date_Assign.Date;
string jobNo = GetJobNo();
if (now != null && jobNo.Length == 0)
{
string userId = HttpContext.Current.User.Identity.Name;
#region New Export Job
C2.CtmJob job = new C2.CtmJob();
jobNo = C2Setup.GetNextNo("", "CTM_Job_EXP", now);
job.JobNo = jobNo;
job.JobDate = now;
string client = System.Configuration.ConfigurationManager.AppSettings["EdiClient"];
job.ClientId = EzshipHelper.GetPartyId(client);
job.DeliveryTo = "";
job.JobType = "EXP";
job.StatusCode = "USE";
job.Contractor = "YES";
job.HaulierId = job.ClientId;
job.QuoteNo = jobNo;
job.JobStatus = "Confirmed";
job.QuoteStatus = "None";
job.QuoteDate = DateTime.Today;
job.Pol = txt_Pol.Text;
job.Pod = "SGSIN";
C2.Manager.ORManager.StartTracking(job, Wilson.ORMapper.InitialState.Inserted);
C2.Manager.ORManager.PersistChanges(job);
C2Setup.SetNextNo("", "CTM_Job_EXP", jobNo, now);
e.Result = " Job No is " + jobNo;
#endregion
}
else
{
e.Result = "Create Error!Today had a export job";
}
#endregion
}
if (paras == "Assign")
{
#region
if (list.Count > 0)
{
for (int i = 0; i < list.Count; i++)
{
int id = list[i].id;
int shipIndex = list[i].shipIndex;
DateTime shipDate = date_Assign.Date;
string postCode = list[i].postCode;
string contIndex = list[i].contIndex;
decimal qty = list[i].qty;
decimal weight = list[i].weight;
decimal volume = list[i].volume;
update_house(id, shipDate, shipIndex.ToString(), postCode, contIndex, qty, weight, volume);
e.Result = "";
}
}
else
{
e.Result = "Action Error";
}
#endregion
}
if (ar.Length >= 2)
{
if (ar[0].Equals("Saveline"))
{
#region
ASPxGridView grid = sender as ASPxGridView;
int rowIndex = SafeValue.SafeInt(ar[1], -1);
ASPxLabel lbl_Id = this.grid.FindRowCellTemplateControl(rowIndex, null, "lbl_Id") as ASPxLabel;
ASPxDateEdit date_ShipDate = this.grid.FindRowCellTemplateControl(rowIndex, (DevExpress.Web.ASPxGridView.GridViewDataColumn) this.grid.Columns["ShipDate"], "date_ShipDate") as ASPxDateEdit;
ASPxTextBox txt_ShipIndex = this.grid.FindRowCellTemplateControl(rowIndex, (DevExpress.Web.ASPxGridView.GridViewDataColumn) this.grid.Columns["ShipIndex"], "txt_ShipIndex") as ASPxTextBox;
ASPxTextBox txt_ShipPortCode = this.grid.FindRowCellTemplateControl(rowIndex, (DevExpress.Web.ASPxGridView.GridViewDataColumn) this.grid.Columns["ShipPortCode"], "txt_ShipPortCode") as ASPxTextBox;
ASPxComboBox cbb_ContIndex = this.grid.FindRowCellTemplateControl(rowIndex, (DevExpress.Web.ASPxGridView.GridViewDataColumn) this.grid.Columns["ContIndex"], "cbb_ContIndex") as ASPxComboBox;
ASPxSpinEdit spin_Qty = this.grid.FindRowCellTemplateControl(rowIndex, (DevExpress.Web.ASPxGridView.GridViewDataColumn) this.grid.Columns["QtyOrig"], "spin_Qty") as ASPxSpinEdit;
ASPxSpinEdit spin_Weight = this.grid.FindRowCellTemplateControl(rowIndex, (DevExpress.Web.ASPxGridView.GridViewDataColumn) this.grid.Columns["WeightOrig"], "spin_Weight") as ASPxSpinEdit;
ASPxSpinEdit spin_Volume = this.grid.FindRowCellTemplateControl(rowIndex, (DevExpress.Web.ASPxGridView.GridViewDataColumn) this.grid.Columns["VolumeOrig"], "spin_Volume") as ASPxSpinEdit;
e.Result = update_house(SafeValue.SafeInt(lbl_Id.Text, 0), date_ShipDate.Date, txt_ShipIndex.Text, txt_ShipPortCode.Text, cbb_ContIndex.Text, spin_Qty.Value, spin_Weight.Value, spin_Volume.Value);
#endregion
}
if (ar[0].Equals("CargoListline"))
{
#region
ASPxGridView grid = sender as ASPxGridView;
int rowIndex = SafeValue.SafeInt(ar[1], -1);
ASPxTextBox txt_Id = grid.FindRowCellTemplateControl(rowIndex, null, "txt_Id") as ASPxTextBox;
e.Result = txt_Id.Text;
#endregion
}
if (ar[0].Equals("Pickline"))
{
#region
ASPxGridView grid = sender as ASPxGridView;
int rowIndex = SafeValue.SafeInt(ar[1], -1);
string jobNo = GetJobNo();
if (jobNo.Length > 0)
{
e.Result = create_house(0, rowIndex, jobNo);
}
else
{
e.Result = "NO Export";
}
#endregion
}
}
}
protected void grid_wh_CustomDataCallback(object sender, ASPxGridViewCustomDataCallbackEventArgs e)
{
string para = e.Parameters;
if (Request.QueryString["no"] != null)
{
try
{
#region list
if (list.Count > 0)
{
string no = SafeValue.SafeString(Request.QueryString["no"]);
string jobType = SafeValue.SafeString(Request.QueryString["type"]);
string client = SafeValue.SafeString(Request.QueryString["clientId"]);
#region OK
if (para == "OK")
{
string jobNo = "";
string refType = "";
bool action = false;
Wilson.ORMapper.OPathQuery query = new Wilson.ORMapper.OPathQuery(typeof(C2.CtmJob), "JobNo='" + no + "'");
C2.CtmJob job = C2.Manager.ORManager.GetObject(query) as C2.CtmJob;
C2.CtmJobDet2 trip = null;
if (job != null)
{
refType = SafeValue.SafeString(Request.QueryString["action"]);
jobNo = C2Setup.GetNextNo("", "CTM_Job_" + refType, DateTime.Today);
job.JobNo = jobNo;
job.QuoteNo = jobNo;
job.JobType = refType;
job.JobDate = DateTime.Now;
job.JobStatus = "Confirmed";
job.QuoteStatus = "None";
C2.Manager.ORManager.StartTracking(job, Wilson.ORMapper.InitialState.Inserted);
C2.Manager.ORManager.PersistChanges(job);
C2Setup.SetNextNo("", "CTM_Job_" + refType, jobNo, DateTime.Today);
#region Trip
trip = new C2.CtmJobDet2();
string sql = string.Format(@"select max(TripIndex) from CTM_JobDet2 where JobType=@JobType and JobNo=@JobNo");
List <ConnectSql_mb.cmdParameters> list_cmd = new List <ConnectSql_mb.cmdParameters>();
list_cmd.Add(new ConnectSql_mb.cmdParameters("@JobNo", jobNo, SqlDbType.NVarChar, 100));
list_cmd.Add(new ConnectSql_mb.cmdParameters("@JobType", refType, SqlDbType.NVarChar, 100));
string maxIdex = SafeValue.SafeString(ConnectSql_mb.ExecuteScalar(sql, list_cmd).context, "//00");
int n = SafeValue.SafeInt(maxIdex.Substring(maxIdex.LastIndexOf("/") + 1), 0) + 1;
string str = (100 + n).ToString().Substring(1);
trip.BookingDate = DateTime.Today;
trip.TripIndex = jobNo + "/" + refType + "/" + str;
trip.JobNo = jobNo;
trip.FromDate = DateTime.Today;
trip.ToDate = DateTime.Today;
trip.CreateUser = HttpContext.Current.User.Identity.Name;
trip.CreateTime = DateTime.Now;
trip.UpdateUser = HttpContext.Current.User.Identity.Name;
trip.UpdateTime = DateTime.Now;
trip.JobType = refType;
trip.TripCode = refType;
trip.Remark = job.Remark;
trip.ToCode = job.DeliveryTo;
trip.FromCode = job.PickupFrom;
trip.Statuscode = "P";
C2.Manager.ORManager.StartTracking(trip, Wilson.ORMapper.InitialState.Inserted);
C2.Manager.ORManager.PersistChanges(trip);
string userId = HttpContext.Current.User.Identity.Name;
C2.CtmJobEventLog elog = new C2.CtmJobEventLog();
elog.Platform_isWeb();
elog.Controller = userId;
elog.ActionLevel_isCONT(trip.Id);
elog.setActionLevel(trip.Id, CtmJobEventLogRemark.Level.Container, 1, "");
elog.log();
#endregion
}
for (int i = 0; i < list.Count; i++)
{
int id = list[i].id;
decimal qty = list[i].qty;
decimal weight = list[i].weight;
decimal volume = list[i].volume;
decimal sku_qty = list[i].sku_qty;
decimal l = list[i].l;
decimal h = list[i].h;
decimal w = list[i].w;
string refNo = list[i].refNo;
string toLoc = list[i].toLoc;
if (qty > 0)
{
Wilson.ORMapper.OPathQuery query1 = new Wilson.ORMapper.OPathQuery(typeof(C2.JobHouse), "Id=" + id + "");
C2.JobHouse house = C2.Manager.ORManager.GetObject(query1) as C2.JobHouse;
house.LineId = id;
house.CargoType = "OUT";
house.JobNo = jobNo;
house.ContNo = "";
house.JobType = refType;
house.QtyOrig = qty;
house.PackQty = sku_qty;
house.Weight = weight;
house.Volume = volume;
house.WeightOrig = weight;
house.VolumeOrig = volume;
house.LandStatus = "Normal";
house.DgClass = "Normal";
house.DamagedStatus = "Normal";
house.LengthPack = l;
house.WidthPack = w;
house.HeightPack = h;
house.RefNo = no;
house.Qty = qty;
house.OpsType = "Delivery";
house.ClientId = client;
house.CargoStatus = "P";
house.TripId = trip.Id;
house.TripIndex = trip.TripIndex;
C2.Manager.ORManager.StartTracking(house, Wilson.ORMapper.InitialState.Inserted);
C2.Manager.ORManager.PersistChanges(house);
int lineId = house.Id;
if (refType == "TR")
{
house.LineId = lineId;
house.Location = toLoc;
house.JobNo = jobNo;
house.TransferNo = jobNo;
house.RefNo = refNo;
house.CargoType = "IN";
house.RefNo = no;
house.CargoStatus = "P";
C2.Manager.ORManager.StartTracking(house, Wilson.ORMapper.InitialState.Inserted);
C2.Manager.ORManager.PersistChanges(house);
}
action = true;
}
else
{
action = false;
}
}
if (action)
{
e.Result = "Action Success! No is " + jobNo;
}
else
{
e.Result = "Action Error!";
}
}
#endregion
#region New WDO Trip
if (para == "NewTrip")
{
bool action = false;
for (int i = 0; i < list.Count; i++)
{
int id = list[i].id;
decimal qty = list[i].qty;
decimal weight = list[i].weight;
decimal volume = list[i].volume;
decimal sku_qty = list[i].sku_qty;
decimal l = list[i].l;
decimal h = list[i].h;
decimal w = list[i].w;
string refNo = list[i].refNo;
string toLoc = list[i].toLoc;
if (qty > 0)
{
Wilson.ORMapper.OPathQuery query1 = new Wilson.ORMapper.OPathQuery(typeof(C2.JobHouse), "Id=" + id + "");
C2.JobHouse house = C2.Manager.ORManager.GetObject(query1) as C2.JobHouse;
house.LineId = id;
house.CargoType = "OUT";
house.JobNo = no;
house.ContNo = "";
house.JobType = jobType;
house.QtyOrig = qty;
house.PackQty = sku_qty;
house.Weight = weight;
house.Volume = volume;
house.WeightOrig = weight;
house.VolumeOrig = volume;
house.LandStatus = "Normal";
house.DgClass = "Normal";
house.DamagedStatus = "Normal";
house.LengthPack = l;
house.WidthPack = w;
house.HeightPack = h;
house.RefNo = no;
house.Qty = qty;
house.OpsType = "Delivery";
house.ClientId = client;
house.TripIndex = SafeValue.SafeString(cbb_TripNo.Text);
house.TripId = SafeValue.SafeInt(cbb_TripNo.Value, 0);
house.CargoStatus = "P";
C2.Manager.ORManager.StartTracking(house, Wilson.ORMapper.InitialState.Inserted);
C2.Manager.ORManager.PersistChanges(house);
}
action = true;
}
if (action)
{
e.Result = "Success";
}
else
{
e.Result = "Action Error!";
}
}
#endregion
}
else
{
e.Result = "Pls Select at least one Cargo";
}
#endregion
}
catch { }
}
}
private bool ePOD_SendEmail(int tripId)
{
bool res = false;
string sql = string.Format(@"select job.JobNo,job.EmailAddress,job.JobType,det2.epodHardCopy
from ctm_jobdet2 as det2
left outer join ctm_job as job on job.JobNo=det2.JobNo
where det2.Id=@tripId");
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@tripId", tripId, SqlDbType.Int));
DataTable dt = ConnectSql_mb.GetDataTable(sql, list);
if (dt.Rows.Count > 0)
{
string JobNo = dt.Rows[0]["JobNo"].ToString();
string email = dt.Rows[0]["EmailAddress"].ToString();
string JobType = dt.Rows[0]["JobType"].ToString();
string hardCopy = dt.Rows[0]["epodHardCopy"].ToString();
if (email != "")
{
try
{
//bool action = false;
string path1 = string.Format("~/files/pdf/");
string path2 = path1.Replace(' ', '_').Replace('\'', '_');
string pathx = path2.Substring(1);
//string path3 = MapPath(path2);
//string path3 = Path.Combine(@"C:\boox\boox_ell", path2);
string path3 = HttpContext.Current.Server.MapPath(path2);
if (!Directory.Exists(path3))
{
Directory.CreateDirectory(path3);
}
string p = string.Format(@"~\files\pdf\DeliveryOrder_Crane_{0}.pdf", DateTime.Now.ToString("yyyyMMdd_HHmmss"));
string e_file = HttpContext.Current.Server.MapPath(p);
//if (!File.Exists(e_file))
//{
// File.Create(e_file);
//}
MemoryStream ms = new MemoryStream();
XtraReport rpt = new XtraReport();
if (hardCopy == "HardCopy")
{
rpt.LoadLayout(Server.MapPath(@"~\PagesContTrucking\Report\repx\DO_HardCopy.repx"));
}
else
{
rpt.LoadLayout(Server.MapPath(@"~\PagesContTrucking\Report\repx\DeliveryOrder_CRA.repx"));
}
rpt.DataSource = DocPrint.PrintJobSheet(JobNo, JobType, tripId + "");
//============ bind signatrue
QR q = new QR();
Wilson.ORMapper.OPathQuery query = new Wilson.ORMapper.OPathQuery(typeof(C2.CtmJob), "JobNo='" + JobNo + "'");
C2.CtmJob job = C2.Manager.ORManager.GetObject(query) as C2.CtmJob;
string text = string.Format(@"JobNo:" + JobNo + ",JobDate:" + job.JobDate.ToString("dd/MM/yyyy"));
Bitmap bt = q.Create_QR(text);
string path = HttpContext.Current.Server.MapPath("~/files/barcode/");
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
string fileName = JobNo + ".png";
string filePath = path + fileName;
if (File.Exists(filePath))
{
File.Delete(filePath);
}
bt.Save(Server.MapPath("~/files/barcode/") + fileName);
string Signature_Consignee = "";
string Signature_Driver = "";
string sql_signature = string.Format(@"select Id,FileType,FileName,FilePath,FileNote From CTM_Attachment where FileType='Signature' and RefNo=@RefNo and charindex(@sType, FileNote,0)>0 and TripId=@tripId");
list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@RefNo", JobNo, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@sType", "Consignee", SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@tripId", tripId, SqlDbType.Int));
dt = ConnectSql_mb.GetDataTable(sql_signature, list);
if (dt.Rows.Count > 0)
{
Signature_Consignee = dt.Rows[0]["FilePath"].ToString().Replace(@"/500", "");
}
list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@RefNo", JobNo, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@sType", "Driver", SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@tripId", tripId, SqlDbType.Int));
dt = ConnectSql_mb.GetDataTable(sql_signature, list);
if (dt.Rows.Count > 0)
{
Signature_Driver = dt.Rows[0]["FilePath"].ToString();
}
DevExpress.XtraReports.UI.XRPictureBox qr_code = rpt.Report.FindControl("barcode", true) as DevExpress.XtraReports.UI.XRPictureBox;
if (qr_code != null)
{
qr_code.ImageUrl = "/files/barcode/" + fileName;
}
DevExpress.XtraReports.UI.XRPictureBox signature = rpt.Report.FindControl("signature", true) as DevExpress.XtraReports.UI.XRPictureBox;
if (signature != null)
{
signature.ImageUrl = Signature_Consignee;
}
signature = rpt.Report.FindControl("signature1", true) as DevExpress.XtraReports.UI.XRPictureBox;
if (signature != null)
{
signature.ImageUrl = Signature_Driver;
}
System.IO.MemoryStream str = new MemoryStream();
rpt.CreateDocument();
rpt.ExportToPdf(e_file);
Helper.Email.SendEmail(email, "*****@*****.**", "*****@*****.**", "Delivery Order " + JobNo, "", p);
res = true;
}
catch { }
// string company = System.Configuration.ConfigurationManager.AppSettings["CompanyName"];
// string address1 = System.Configuration.ConfigurationManager.AppSettings["CompanyAddress1"];
// string address2 = System.Configuration.ConfigurationManager.AppSettings["CompanyAddress2"];
// string address3 = System.Configuration.ConfigurationManager.AppSettings["CompanyAddress3"];
// sql = string.Format(@"select Email1,Email2,Name from xxparty where PartyId='{0}'",JobNo);
// DataTable tab = ConnectSql.GetTab(sql);
// string add = address1 + " " + address2 + " " + address3;
// string title = "Haulier Advice";
// if (tab.Rows.Count > 0)
// {
// string email1 = SafeValue.SafeString(tab.Rows[0]["Email1"]);
// string email2 = SafeValue.SafeString(tab.Rows[0]["Email2"]);
// string name = SafeValue.SafeString(tab.Rows[0]["Name"]);
// string mes = string.Format(@"<b>{0}</b><br><br>
//{1}<br><br>
//<b>Dear Customer, <br><br>Kindly review attached document for HAULIER ADVICE.</b>
//<br><br>
//<b>This is a computer generated email, please DO NOT reply.
//<br><br>
//Pls email to : [email protected] for any questions.
//</b><br><br>
//***IMPORTANT NOTICE***<br><br>
//
//1. Pls wait for our delivery department to call you for delivery arrangement<br><br>
//
//2. Delivery arrangement will be made within 3 working days from the date of unstuffing of container.<br><br>
//<br><br>", company, add);
// if (email1.Length > 0)
// {
// Helper.Email.SendEmail(email1, "[email protected],[email protected]", "*****@*****.**", title, mes, p);
// //Helper.Email.SendEmail("*****@*****.**", "[email protected],[email protected]", "", title, mes, p);
// action = true;
// }
// if (email2.Length > 0)
// {
// if (action)
// Helper.Email.SendEmail(email2, "", "", title, mes, p);
// else
// Helper.Email.SendEmail(email2, "[email protected],[email protected]", "*****@*****.**", title, mes, p);
// }
// //"*****@*****.**", "*****@*****.**"
// res = true;
// }
}
}
return(res);
}
private void container_new()
{
C2.CtmJob job = C2.Manager.ORManager.GetObject(new Wilson.ORMapper.OPathQuery <C2.CtmJob>("JobNo='" + txt_JobNo.Text + "'"));
if (job != null)
{
string user = HttpContext.Current.User.Identity.Name;
C2.CtmJobDet1Biz det1Bz = new C2.CtmJobDet1Biz(0);
C2.CtmJobDet1 det1 = new C2.CtmJobDet1();
det1.JobNo = job.JobNo;
det1.RequestDate = DateTime.Now;
det1.CfsInDate = DateTime.Now;
det1.CfsOutDate = DateTime.Now;
det1.YardPickupDate = DateTime.Now;
det1.YardReturnDate = DateTime.Now;
det1.CdtDate = DateTime.Now;
det1.YardExpiryDate = DateTime.Now;
det1.F5Ind = "N";
det1.UrgentInd = "N";
det1.StatusCode = "New";
//det1.YardCode = job.YardCode;
//det1.YardAddress = job.YardRef;
#region container sss
string values = "";
if (btn_ContNo.Text.Trim().Length > 0)
{
det1.ContainerNo = btn_ContNo.Text;
det1.ContainerType = txt_ContType.Text;
det1.SealNo = txt_SealNo.Text;
det1.YardAddress = btn_YardAddress.Text;
det1.Remark = txt_Remark.Text;
det1.ScheduleDate = SafeValue.SafeDate(date_YardExpiry.Date, new DateTime(1990, 1, 1));
det1.ScheduleTime = txt_time.Text;
det1.Weight = SafeValue.SafeDecimal(txt_Weight.Text);
det1.ServiceType = SafeValue.SafeString(cmb_ServiceType.Value);
det1.UrgentInd = txt_Urgent.Text;
det1.Permit = txt_Permit.Text;
det1.DgClass = txt_dg.Text;
det1.JobType = job.JobType;
det1.Br = job.CarrierBkgNo;
C2.BizResult bRes = det1Bz.insert(user, det1);
if (!bRes.status)
{
values = bRes.context;
}
}
if (btn_ContNo1.Text.Trim().Length > 0)
{
det1.ContainerNo = btn_ContNo1.Text;
det1.ContainerType = txt_ContType1.Text;
det1.SealNo = txt_SealNo1.Text;
det1.YardAddress = btn_YardAddress1.Text;
det1.Remark = txt_Remark1.Text;
det1.ScheduleDate = SafeValue.SafeDate(date_YardExpiry1.Date, new DateTime(1990, 1, 1));
det1.ScheduleTime = txt_time1.Text;
det1.Weight = SafeValue.SafeDecimal(txt_Weight1.Text);
det1.ServiceType = SafeValue.SafeString(cmb_ServiceType1.Value);
det1.UrgentInd = txt_Urgent1.Text;
det1.Permit = txt_Permit1.Text;
det1.DgClass = txt_dg1.Text;
det1.JobType = job.JobType;
det1.Br = job.CarrierBkgNo;
C2.BizResult bRes = det1Bz.insert(user, det1);
if (!bRes.status)
{
values = bRes.context;
}
}
if (btn_ContNo2.Text.Trim().Length > 0)
{
det1.ContainerNo = btn_ContNo2.Text;
det1.ContainerType = txt_ContType2.Text;
det1.SealNo = txt_SealNo2.Text;
det1.YardAddress = btn_YardAddress2.Text;
det1.Remark = txt_Remark2.Text;
det1.ScheduleDate = SafeValue.SafeDate(date_YardExpiry2.Date, new DateTime(1990, 1, 1));
det1.ScheduleTime = txt_time2.Text;
det1.Weight = SafeValue.SafeDecimal(txt_Weight2.Text);
det1.ServiceType = SafeValue.SafeString(cmb_ServiceType2.Value);
det1.UrgentInd = txt_Urgent2.Text;
det1.Permit = txt_Permit2.Text;
det1.DgClass = txt_dg2.Text;
det1.JobType = job.JobType;
det1.Br = job.CarrierBkgNo;
C2.BizResult bRes = det1Bz.insert(user, det1);
if (!bRes.status)
{
values = bRes.context;
}
}
if (btn_ContNo3.Text.Trim().Length > 0)
{
det1.ContainerNo = btn_ContNo3.Text;
det1.ContainerType = txt_ContType3.Text;
det1.SealNo = txt_SealNo3.Text;
det1.YardAddress = btn_YardAddress3.Text;
det1.Remark = txt_Remark3.Text;
det1.ScheduleDate = SafeValue.SafeDate(date_YardExpiry3.Date, new DateTime(1990, 1, 1));
det1.ScheduleTime = txt_time3.Text;
det1.Weight = SafeValue.SafeDecimal(txt_Weight3.Text);
det1.ServiceType = SafeValue.SafeString(cmb_ServiceType3.Value);
det1.UrgentInd = txt_Urgent3.Text;
det1.Permit = txt_Permit3.Text;
det1.DgClass = txt_dg3.Text;
det1.JobType = job.JobType;
det1.Br = job.CarrierBkgNo;
C2.BizResult bRes = det1Bz.insert(user, det1);
if (!bRes.status)
{
values = bRes.context;
}
}
if (btn_ContNo4.Text.Trim().Length > 0)
{
det1.ContainerNo = btn_ContNo4.Text;
det1.ContainerType = txt_ContType4.Text;
det1.SealNo = txt_SealNo4.Text;
det1.YardAddress = btn_YardAddress4.Text;
det1.Remark = txt_Remark4.Text;
det1.ScheduleDate = SafeValue.SafeDate(date_YardExpiry4.Date, new DateTime(1990, 1, 1));
det1.ScheduleTime = txt_time4.Text;
det1.Weight = SafeValue.SafeDecimal(txt_Weight4.Text);
det1.ServiceType = SafeValue.SafeString(cmb_ServiceType4.Value);
det1.UrgentInd = txt_Urgent4.Text;
det1.Permit = txt_Permit4.Text;
det1.DgClass = txt_dg4.Text;
det1.JobType = job.JobType;
det1.Br = job.CarrierBkgNo;
C2.BizResult bRes = det1Bz.insert(user, det1);
if (!bRes.status)
{
values = bRes.context;
}
}
if (btn_ContNo5.Text.Trim().Length > 0)
{
det1.ContainerNo = btn_ContNo5.Text;
det1.ContainerType = txt_ContType5.Text;
det1.SealNo = txt_SealNo5.Text;
det1.YardAddress = btn_YardAddress5.Text;
det1.Remark = txt_Remark5.Text;
det1.ScheduleDate = SafeValue.SafeDate(date_YardExpiry5.Date, new DateTime(1990, 1, 1));
det1.ScheduleTime = txt_time5.Text;
det1.Weight = SafeValue.SafeDecimal(txt_Weight5.Text);
det1.ServiceType = SafeValue.SafeString(cmb_ServiceType5.Value);
det1.UrgentInd = txt_Urgent5.Text;
det1.Permit = txt_Permit5.Text;
det1.DgClass = txt_dg5.Text;
det1.JobType = job.JobType;
det1.Br = job.CarrierBkgNo;
C2.BizResult bRes = det1Bz.insert(user, det1);
if (!bRes.status)
{
values = bRes.context;
}
}
if (btn_ContNo6.Text.Trim().Length > 0)
{
det1.ContainerNo = btn_ContNo6.Text;
det1.ContainerType = txt_ContType6.Text;
det1.SealNo = txt_SealNo6.Text;
det1.YardAddress = btn_YardAddress6.Text;
det1.Remark = txt_Remark6.Text;
det1.ScheduleDate = SafeValue.SafeDate(date_YardExpiry6.Date, new DateTime(1990, 1, 1));
det1.ScheduleTime = txt_time6.Text;
det1.Weight = SafeValue.SafeDecimal(txt_Weight6.Text);
det1.ServiceType = SafeValue.SafeString(cmb_ServiceType6.Value);
det1.UrgentInd = txt_Urgent6.Text;
det1.Permit = txt_Permit6.Text;
det1.DgClass = txt_dg6.Text;
det1.JobType = job.JobType;
det1.Br = job.CarrierBkgNo;
C2.BizResult bRes = det1Bz.insert(user, det1);
if (!bRes.status)
{
values = bRes.context;
}
}
if (btn_ContNo7.Text.Trim().Length > 0)
{
det1.ContainerNo = btn_ContNo7.Text;
det1.ContainerType = txt_ContType7.Text;
det1.SealNo = txt_SealNo7.Text;
det1.YardAddress = btn_YardAddress7.Text;
det1.Remark = txt_Remark7.Text;
det1.ScheduleDate = SafeValue.SafeDate(date_YardExpiry7.Date, new DateTime(1990, 1, 1));
det1.ScheduleTime = txt_time7.Text;
det1.Weight = SafeValue.SafeDecimal(txt_Weight7.Text);
det1.ServiceType = SafeValue.SafeString(cmb_ServiceType7.Value);
det1.UrgentInd = txt_Urgent7.Text;
det1.Permit = txt_Permit7.Text;
det1.DgClass = txt_dg7.Text;
det1.JobType = job.JobType;
det1.Br = job.CarrierBkgNo;
C2.BizResult bRes = det1Bz.insert(user, det1);
if (!bRes.status)
{
values = bRes.context;
}
}
if (btn_ContNo8.Text.Trim().Length > 0)
{
det1.ContainerNo = btn_ContNo8.Text;
det1.ContainerType = txt_ContType8.Text;
det1.SealNo = txt_SealNo8.Text;
det1.YardAddress = btn_YardAddress8.Text;
det1.Remark = txt_Remark8.Text;
det1.ScheduleDate = SafeValue.SafeDate(date_YardExpiry8.Date, new DateTime(1990, 1, 1));
det1.ScheduleTime = txt_time8.Text;
det1.Weight = SafeValue.SafeDecimal(txt_Weight8.Text);
det1.ServiceType = SafeValue.SafeString(cmb_ServiceType8.Value);
det1.UrgentInd = txt_Urgent8.Text;
det1.Permit = txt_Permit8.Text;
det1.DgClass = txt_dg8.Text;
det1.JobType = job.JobType;
det1.Br = job.CarrierBkgNo;
C2.BizResult bRes = det1Bz.insert(user, det1);
if (!bRes.status)
{
values = bRes.context;
}
}
if (btn_ContNo9.Text.Trim().Length > 0)
{
det1.ContainerNo = btn_ContNo9.Text;
det1.ContainerType = txt_ContType9.Text;
det1.SealNo = txt_SealNo9.Text;
det1.YardAddress = btn_YardAddress9.Text;
det1.Remark = txt_Remark9.Text;
det1.ScheduleDate = SafeValue.SafeDate(date_YardExpiry9.Date, new DateTime(1990, 1, 1));
det1.ScheduleTime = txt_time9.Text;
det1.Weight = SafeValue.SafeDecimal(txt_Weight9.Text);
det1.ServiceType = SafeValue.SafeString(cmb_ServiceType9.Value);
det1.UrgentInd = txt_Urgent9.Text;
det1.Permit = txt_Permit9.Text;
det1.DgClass = txt_dg9.Text;
det1.JobType = job.JobType;
det1.Br = job.CarrierBkgNo;
C2.BizResult bRes = det1Bz.insert(user, det1);
if (!bRes.status)
{
values = bRes.context;
}
}
if (values.Length == 0)
{
values = "Success";
}
Response.Write("<script>parent.Popup_ContainerBatchAdd_callback('" + values + "');</script>");
#endregion
}
}
public static DataSet PrintTallySheet(string orderNo, string jobType)
{
DataSet set = new DataSet();
ObjectQuery query = new ObjectQuery(typeof(C2.CtmJob), "JobNo='" + orderNo + "'", "");
ObjectSet objSet = C2.Manager.ORManager.GetObjectSet(query);
if (objSet.Count == 0 || objSet[0] == null)
{
return(set);
}
C2.CtmJob obj = objSet[0] as C2.CtmJob;
#region init column
DataTable mast = new DataTable("Mast");
DataTable det = new DataTable("Details");
mast.Columns.Add("JobNo");
mast.Columns.Add("JobType");
mast.Columns.Add("Haulier");
mast.Columns.Add("Attn");
mast.Columns.Add("DeliveryTo");
mast.Columns.Add("OceanBl");
mast.Columns.Add("Yard");
mast.Columns.Add("Carrier");
mast.Columns.Add("CrNo");
mast.Columns.Add("Contact1");
mast.Columns.Add("Contact2");
mast.Columns.Add("Tel1");
mast.Columns.Add("Fax1");
mast.Columns.Add("Terminal");
mast.Columns.Add("Permit");
mast.Columns.Add("LastDate");
mast.Columns.Add("Portnet");
mast.Columns.Add("ContNo");
mast.Columns.Add("SealNo");
mast.Columns.Add("Ft20");
mast.Columns.Add("Ft40");
mast.Columns.Add("Ft45");
mast.Columns.Add("FtSize");
mast.Columns.Add("ContType");
mast.Columns.Add("CarrierBkgNo");
mast.Columns.Add("Vessel");
mast.Columns.Add("Voyage");
mast.Columns.Add("Etd");
mast.Columns.Add("Eta");
mast.Columns.Add("Pol");
mast.Columns.Add("ByWho");
mast.Columns.Add("ClientRefNo");
mast.Columns.Add("CustName");
mast.Columns.Add("AgentName");
mast.Columns.Add("UserData2");
mast.Columns.Add("UserData3");
mast.Columns.Add("UserData4");
mast.Columns.Add("Outside");
mast.Columns.Add("Note1");
mast.Columns.Add("Note2");
mast.Columns.Add("StampUser");
mast.Columns.Add("Remark");
mast.Columns.Add("SpecialInstruction");
det.Columns.Add("TotalQty");
det.Columns.Add("TotalM3");
det.Columns.Add("No");
det.Columns.Add("JobNo");
det.Columns.Add("ContNo");
det.Columns.Add("BookingNo");
det.Columns.Add("HblNo");
det.Columns.Add("OpsType");
det.Columns.Add("Qty");
det.Columns.Add("Uom");
det.Columns.Add("QtyOrig");
det.Columns.Add("WeightOrig");
det.Columns.Add("VolumeOrig");
det.Columns.Add("PackType");
det.Columns.Add("SkuCode");
det.Columns.Add("Weight");
det.Columns.Add("PackQty");
det.Columns.Add("PackUom");
det.Columns.Add("Volume");
det.Columns.Add("Location");
det.Columns.Add("Length");
det.Columns.Add("Width");
det.Columns.Add("Height");
det.Columns.Add("Marking1");
det.Columns.Add("Marking2");
det.Columns.Add("Remark1");
det.Columns.Add("Pod");
#endregion
#region Data
string contNo = "";
string contType = "";
DataRow row = mast.NewRow();
string sql_haulier = "select Name,CrNo,Contact1,Contact2,Tel1,Fax1 from xxparty where partyid='" + obj.ClientId + "'";
DataTable tab_hauler = Helper.Sql.List(sql_haulier);
row["Attn"] = obj.Terminalcode;
row["DeliveryTo"] = obj.DeliveryTo;
row["CrNo"] = "";
row["Contact1"] = "";
row["Contact2"] = "";
row["Tel1"] = "";
row["Fax1"] = "";
if (tab_hauler.Rows.Count > 0)
{
row["Haulier"] = tab_hauler.Rows[0]["Name"];
row["CrNo"] = tab_hauler.Rows[0]["CrNo"].ToString();
row["Contact1"] = tab_hauler.Rows[0]["Contact1"].ToString();
row["Contact2"] = tab_hauler.Rows[0]["Contact2"].ToString();
row["Tel1"] = tab_hauler.Rows[0]["Tel1"].ToString();
row["Fax1"] = tab_hauler.Rows[0]["Fax1"].ToString();
}
row["CarrierBkgNo"] = obj.CarrierBkgNo;
row["JobNo"] = orderNo;
row["JobType"] = obj.JobType;
row["Vessel"] = obj.Vessel;
row["Voyage"] = obj.Voyage;
row["Permit"] = obj.PermitNo;
string sql = "select name from xxparty where partyid='" + obj.ClientId + "'";
row["CustName"] = SafeValue.SafeString(ConnectSql.ExecuteScalar(sql), "");
row["Etd"] = obj.EtdDate.ToString("dd/MM/yyyy");
row["Eta"] = obj.EtaDate.ToString("dd/MM/yyyy");
sql = "select name from [XXPort] where code='" + SafeValue.SafeString(obj.Pol, "") + "'";
row["Pol"] = SafeValue.SafeString(Helper.Sql.One(sql), "");
sql = "select name from xxparty where partyid='" + obj.CarrierId + "'";
row["Carrier"] = SafeValue.SafeString(Helper.Sql.One(sql), "");
row["OceanBl"] = obj.CarrierBlNo;
row["Yard"] = obj.YardRef;
row["ByWho"] = "";
row["ClientRefNo"] = obj.ClientRefNo;
row["UserData2"] = "";
row["SpecialInstruction"] = obj.SpecialInstruction;
row["Remark"] = obj.Remark;
row["StampUser"] = HttpContext.Current.User.Identity.Name;
int n = 0;
sql = string.Format(@"select ContainerNo,ContainerType from Ctm_JobDet1 where JobNo='{0}'", orderNo);
DataTable tab_cont = ConnectSql_mb.GetDataTable(sql);
for (int a = 0; a < tab_cont.Rows.Count; a++)
{
contNo = SafeValue.SafeString(tab_cont.Rows[a]["ContainerNo"]);
contType = SafeValue.SafeString(tab_cont.Rows[a]["ContainerType"]);
row["ContNo"] += contNo + " " + contType + " / ";
}
decimal total = 0;
decimal m3 = 0;
string lastContNo = "";
sql = string.Format(@"select * from job_house where JobNo='{0}' order by Id asc", orderNo);
DataTable dt_det = ConnectSql_mb.GetDataTable(sql);
for (int i = 0; i < dt_det.Rows.Count; i++)
{
DataRow rowDet = dt_det.Rows[i];
DataRow row1 = det.NewRow();
row1["No"] = n + 1;
row1["ContNo"] = rowDet["ContNo"];
row1["JobNo"] = orderNo;
row1["BookingNo"] = rowDet["BookingNo"];
row1["HblNo"] = rowDet["HblNo"];
row1["Length"] = rowDet["LengthPack"];
row1["Width"] = rowDet["WidthPack"];
row1["Height"] = rowDet["HeightPack"];
row1["Weight"] = rowDet["Weight"];
row1["Volume"] = rowDet["Volume"];
row1["Qty"] = SafeValue.SafeDecimal(rowDet["Qty"], 0);
row1["Uom"] = SafeValue.SafeDecimal(rowDet["UomCode"], 0);
row1["QtyOrig"] = SafeValue.SafeDecimal(rowDet["QtyOrig"], 0);
row1["VolumeOrig"] = SafeValue.SafeDecimal(rowDet["VolumeOrig"], 0);
row1["WeightOrig"] = rowDet["WeightOrig"];
row1["PackType"] = rowDet["PackTypeOrig"];
row1["PackQty"] = rowDet["PackQty"];
row1["PackUom"] = rowDet["PackUom"];
sql = "select name from [XXPort] where code='" + SafeValue.SafeString(obj.Pod, "") + "'";
row1["Pod"] = SafeValue.SafeString(Helper.Sql.One(sql), "");
total += SafeValue.SafeDecimal(rowDet["QtyOrig"], 0);
m3 += SafeValue.SafeDecimal(rowDet["VolumeOrig"], 0);
n++;
det.Rows.Add(row1);
}
mast.Rows.Add(row);
#endregion
set.Tables.Add(mast);
set.Tables.Add(det);
//int count = mast.Rows.Count;
//int m = det.Rows.Count;
//set.Relations.Add("", mast.Columns["ContNo"], det.Columns["ContNo"]);
return(set);
}
public void ImportExcel(string batch, string file, out string error_text)
{
Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense(MapPath("~/Aspose.lic"));
Workbook wb = new Workbook();
if (file.ToLower().EndsWith(".csv"))
{
wb.Open(file, FileFormatType.CSV);
}
else
{
wb.Open(file);
}
int empty_i = 0;
string re_text = "";
Worksheet ws = wb.Worksheets[0];
int existDo = 0;
int successJob = 0;
int successItem = 0;
int errorDo = 0;
//=================================== version 1
bool beginImport = false;
//int existDo = 0;
//int successJob = 0;
int SortIndex = 0;
bool action = false;
//Job Info
string client = ws.Cells["B" + 2].StringValue;
string vessel = ws.Cells["B" + 3].StringValue;
string voyage = ws.Cells["D" + 3].StringValue;
string pol = ws.Cells["B" + 4].StringValue;
string pod = ws.Cells["D" + 4].StringValue;
string warehouse = ws.Cells["H" + 2].StringValue;
string isWarehouse = ws.Cells["J" + 2].StringValue;
DateTime now = DateTime.Now;
string jobNo = "";
C2.CtmJob job = new C2.CtmJob();
jobNo = C2Setup.GetNextNo("", "CTM_Job_WGR", DateTime.Now);
job.JobNo = jobNo;
job.JobType = "WGR";
job.JobDate = DateTime.Now;
job.ClientId = EzshipHelper.GetPartyId(client);
job.DeliveryTo = "";
job.StatusCode = "USE";
job.QuoteNo = jobNo;
job.QuoteStatus = "None";
job.JobStatus = "Confirmed";
job.Vessel = vessel;
job.Voyage = voyage;
job.Pol = pol;
job.Pod = pod;
job.WareHouseCode = warehouse;
job.IsWarehouse = isWarehouse;
C2.Manager.ORManager.StartTracking(job, Wilson.ORMapper.InitialState.Inserted);
C2.Manager.ORManager.PersistChanges(job);
C2Setup.SetNextNo("", "CTM_Job_WGR", jobNo, now);
auto_add_trailer_trip(jobNo, "WGR");
//Order Info
string bkgRefNo = ws.Cells["B" + 7].StringValue;
string hblNo = ws.Cells["F" + 7].StringValue;
string conNo = ws.Cells["I" + 7].StringValue;
string shipper = ws.Cells["B" + 8].StringValue;
string consignee = ws.Cells["B" + 9].StringValue;
string ic_uen = ws.Cells["H" + 9].StringValue;
string responsible = ws.Cells["K" + 9].StringValue;
string email1 = ws.Cells["C" + 10].StringValue;
string email2 = ws.Cells["H" + 10].StringValue;
string tel = ws.Cells["C" + 11].StringValue;
string postCode = ws.Cells["E" + 11].StringValue;
string mobile = ws.Cells["H" + 11].StringValue;
string address = ws.Cells["B" + 12].StringValue;
string receiver = ws.Cells["B" + 14].StringValue;
string contact = ws.Cells["B" + 15].StringValue;
string delivery = ws.Cells["B" + 16].StringValue;
string prepaidInd = ws.Cells["C" + 18].StringValue;
decimal collectAmount1 = SafeValue.SafeDecimal(ws.Cells["H" + 18].StringValue);
decimal collectAmount2 = SafeValue.SafeDecimal(ws.Cells["K" + 18].StringValue);
string dutyPayment = ws.Cells["B" + 19].StringValue;
string incoterm = ws.Cells["B" + 20].StringValue;
decimal ocean_freight = SafeValue.SafeDecimal(ws.Cells["H" + 20].StringValue);
string remark = ws.Cells["B" + 21].StringValue;
string fee1CurrId = ws.Cells["H" + 24].StringValue;
string totalQty = ws.Cells["B" + 24].StringValue;
string packType = ws.Cells["D" + 2].StringValue;
string weight = ws.Cells["F" + 24].StringValue;
string m3 = ws.Cells["J" + 24].StringValue;
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
ConnectSql_mb.cmdParameters cpar = null;
#region list add
cpar = new ConnectSql_mb.cmdParameters("@BookingNo", bkgRefNo, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@HblNo", hblNo, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@JobNo", jobNo, SqlDbType.NVarChar);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@RefNo", jobNo, SqlDbType.NVarChar);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ContNo", conNo, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ShipperInfo", EzshipHelper.GetPartyId(shipper), SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ConsigneeInfo", consignee, SqlDbType.NVarChar, 100);
list.Add(cpar);
if (responsible == "个人")
{
cpar = new ConnectSql_mb.cmdParameters("@Responsible", "PERSON", SqlDbType.NVarChar, 100);
list.Add(cpar);
}
else
{
cpar = new ConnectSql_mb.cmdParameters("@Responsible", "COMPANY", SqlDbType.NVarChar, 100);
list.Add(cpar);
}
cpar = new ConnectSql_mb.cmdParameters("@ConsigneeRemark", ic_uen, SqlDbType.NVarChar, 100);
list.Add(cpar);
if (email2.Length > 0)
{
email1 += "," + email2;
}
cpar = new ConnectSql_mb.cmdParameters("@ConsigneeEmail", email1, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ConsigneeTel", tel, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ConsigneeZip", postCode, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Mobile1", mobile, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ConsigneeAddress", address, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ClientId", receiver, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ClientEmail", contact, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ClientAddress", delivery, SqlDbType.NVarChar, 100);
list.Add(cpar);
string prepaid = "NO";
if (prepaidInd.Equals("√"))
{
prepaid = "YES";
}
cpar = new ConnectSql_mb.cmdParameters("@PrepaidInd", prepaid, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@CollectAmount1", collectAmount1, SqlDbType.Decimal, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@CollectAmount2", collectAmount2, SqlDbType.Decimal, 100);
list.Add(cpar);
string payment = "";
if (dutyPayment.Equals("税中国付/DUTY PAID"))
{
payment = "DUTY PAID";
}
else
{
payment = "DUTY UNPAID";
}
cpar = new ConnectSql_mb.cmdParameters("@DutyPayment", payment, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Incoterm", incoterm, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Ocean_Freight", ocean_freight, SqlDbType.Decimal, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Remark1", remark, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Currency", fee1CurrId, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Qty", SafeValue.SafeDecimal(totalQty, 0), SqlDbType.Decimal, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@PackTypeOrig", packType, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Weight", SafeValue.SafeDecimal(weight, 0), SqlDbType.Decimal, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Volume", SafeValue.SafeDecimal(m3, 0), SqlDbType.Decimal, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@JobType", "WGR", SqlDbType.NVarChar, 100);
list.Add(cpar);
#endregion
string sql = string.Format(@"insert into job_house (BookingNo,HblNo,JobNo,RefNo,JobType,ContNo,CargoStatus,ShipperInfo,ConsigneeInfo,Responsible,ConsigneeRemark,
ConsigneeEmail,ConsigneeTel,Mobile1,ConsigneeAddress,ClientId,ClientEmail,ClientAddress,Remark1,Prepaid_Ind,Collect_Amount1,Collect_Amount2,Duty_Payment,Incoterm,Ocean_Freight,Currency,Qty,PackTypeOrig,Weight,Volume,CargoType)
values (@BookingNo,@HblNo,@JobNo,@RefNo,@JobType,@ContNo,'P',@ShipperInfo,@ConsigneeInfo,@Responsible,@ConsigneeRemark,@ConsigneeEmail,@ConsigneeTel,@Mobile1,@ConsigneeAddress,@ClientId,@ClientEmail,@ClientAddress,@Remark1,@PrepaidInd,@CollectAmount1,
@CollectAmount2,@DutyPayment,@Incoterm,@Ocean_Freight,@Currency,@Qty,@PackTypeOrig,@Weight,@Volume,'IN') select @@identity");
ConnectSql_mb.sqlResult re = ConnectSql_mb.ExecuteScalar(sql, list);
if (re.status)
{
string id = SafeValue.SafeString(re.context);
sql = string.Format(@"update job_house set LineId={0} where Id={0}", id);
ConnectSql_mb.ExecuteNonQuery(sql);
//insert_cargo(SafeValue.SafeInt(id, 0));
if (id.Length > 0)
{
action = true;
for (int i = 25; true; i++)
{
if (empty_i >= 10)
{
break;
}
DateTime date = DateTime.Today;
string A = ws.Cells["A" + i].StringValue;
string B = ws.Cells["B" + i].StringValue;
string C = ws.Cells["C" + i].StringValue;
if (A.Trim().ToUpper().Equals("序号"))
{
beginImport = true;
empty_i++;
i = i + 1;
continue;
}
try
{
SortIndex = SafeValue.SafeInt(ws.Cells["A" + i].StringValue, 0);
}
catch
{
empty_i++;
continue;
}
if (C.Length <= 0 && B.Length <= 0)
{
empty_i++;
continue;
}
//empty_i = 0;
if (beginImport)
{
#region
string D = ws.Cells["D" + i].StringValue;
string E = ws.Cells["E" + i].StringValue;
string F = ws.Cells["F" + i].StringValue;
string G = ws.Cells["G" + i].StringValue;
#endregion
//=====================检测是否存在Bill Number
string billNo = D;
string billItem = E;
string sql_check = string.Format(@"select Marks1 from job_stock where OrderId={0} and SortIndex={1} and Marks1='{2}'", id, SortIndex, C);
DataTable dt_check = ConnectSql.GetTab(sql_check);
if (dt_check.Rows.Count > 0)
{
existDo++;
continue;
}
else
{
sql_check = string.Format(@"select count(*) from job_stock where OrderId={0}", id);
int n = SafeValue.SafeInt(ConnectSql.ExecuteScalar(sql_check), 0);
SortIndex = n + 1;
}
try
{
list = new List <ConnectSql_mb.cmdParameters>();
#region list add
cpar = new ConnectSql_mb.cmdParameters("@SortIndex", SortIndex, SqlDbType.Int, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@B", B, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@C", C, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@D", D, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@E", SafeValue.SafeInt(E, 0), SqlDbType.Int);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@F", SafeValue.SafeDecimal(F, 0), SqlDbType.Decimal);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@G", SafeValue.SafeDecimal(G), SqlDbType.Decimal);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@OrderId", id, SqlDbType.Int, 100);
list.Add(cpar);
#endregion
sql = string.Format(@"insert into job_stock (SortIndex,Marks1,Marks2,Uom1,Qty2,Price1,Price2,
OrderId) values (@SortIndex,@B,@C,@D,@E,@F,@G,@OrderId)");
re = ConnectSql_mb.ExecuteNonQuery(sql, list);
if (re.status)
{
successJob++;
//action = true;
}
else
{
errorDo++;
//throw new Exception(re.context);
}
}
catch (Exception ex)
{
errorDo++;
//throw new Exception(ex.ToString());
}
}
else
{
}
}
if (action)
{
//VilaParty(consignee, uen, ic, email1, email2, tel1, tel2, mobile1, mobile2, address, responsible);
}
}
}
re_text = string.Format(@"添加了 {0} 条货物", successJob);
re_text += successItem > 0 ? string.Format(@" {0} 条记录.", successItem) : ".";
re_text += existDo > 0 ? string.Format(@" {0} 条已存在", existDo) : "";
re_text += errorDo > 0 ? string.Format(@" {0} 条错误", errorDo) : "";
error_text = re_text;
}
public static DataSet PrintDeliveryOrder(string orderNo, string jobType)
{
DataSet set = new DataSet();
ObjectQuery query = new ObjectQuery(typeof(C2.CtmJob), "JobNo='" + orderNo + "'", "");
ObjectSet objSet = C2.Manager.ORManager.GetObjectSet(query);
if (objSet.Count == 0 || objSet[0] == null)
{
return(set);
}
C2.CtmJob obj = objSet[0] as C2.CtmJob;
ObjectQuery query1 = new ObjectQuery(typeof(C2.JobHouse), "JobNo='" + orderNo + "'", "");
ObjectSet objSet1 = C2.Manager.ORManager.GetObjectSet(query1);
#region init column
DataTable mast = new DataTable("Mast");
DataTable det = new DataTable("Det");
mast.Columns.Add("JobNo");
mast.Columns.Add("JobDate");
mast.Columns.Add("JobType");
mast.Columns.Add("Haulier");
mast.Columns.Add("Attn");
mast.Columns.Add("DeliveryTo");
mast.Columns.Add("PickupFrom");
mast.Columns.Add("OceanBl");
mast.Columns.Add("Yard");
mast.Columns.Add("Carrier");
mast.Columns.Add("CrNo");
mast.Columns.Add("Contact1");
mast.Columns.Add("Contact2");
mast.Columns.Add("Tel1");
mast.Columns.Add("Fax1");
mast.Columns.Add("Terminal");
mast.Columns.Add("Permit");
mast.Columns.Add("LastDate");
mast.Columns.Add("Portnet");
mast.Columns.Add("Ft20");
mast.Columns.Add("Ft40");
mast.Columns.Add("Ft45");
mast.Columns.Add("Vessel");
mast.Columns.Add("Voyage");
mast.Columns.Add("Etd");
mast.Columns.Add("Eta");
mast.Columns.Add("Pol");
mast.Columns.Add("Pod");
mast.Columns.Add("ByWho");
mast.Columns.Add("ClientRefNo");
mast.Columns.Add("PartyName");
mast.Columns.Add("PartyAdd");
mast.Columns.Add("AgentName");
mast.Columns.Add("UserData2");
mast.Columns.Add("UserData3");
mast.Columns.Add("UserData4");
mast.Columns.Add("Outside");
mast.Columns.Add("Note1");
mast.Columns.Add("Note2");
mast.Columns.Add("UserId");
mast.Columns.Add("Remark");
mast.Columns.Add("SpecialInstruction");
mast.Columns.Add("WarehouseCode");
mast.Columns.Add("CrBkgNo");
mast.Columns.Add("TotalQty");
det.Columns.Add("No");
det.Columns.Add("JobNo");
det.Columns.Add("CargoStatus");
det.Columns.Add("CargoType");
det.Columns.Add("ContNo");
det.Columns.Add("BookingNo");
det.Columns.Add("HblNo");
det.Columns.Add("OpsType");
det.Columns.Add("Qty");
det.Columns.Add("PackType");
det.Columns.Add("SkuCode");
det.Columns.Add("Weight");
det.Columns.Add("PackQty");
det.Columns.Add("PackUom");
det.Columns.Add("Volume");
det.Columns.Add("Location");
det.Columns.Add("Length");
det.Columns.Add("Width");
det.Columns.Add("Height");
det.Columns.Add("Marking1");
det.Columns.Add("Marking2");
det.Columns.Add("Remark1");
#endregion
DataRow row = mast.NewRow();
string sql_haulier = "select Name,CrNo,Contact1,Contact2,Tel1,Fax1 from xxparty where partyid='" + obj.ClientId + "'";
DataTable tab_hauler = Helper.Sql.List(sql_haulier);
if (jobType == "gr")
{
row["JobType"] = "Goods Receipt Note".ToUpper();
}
if (jobType == "do")
{
row["JobType"] = "Delivery Order".ToUpper();
}
if (jobType == "tp")
{
row["JobType"] = "Transport Instruction".ToUpper();
}
if (jobType == "tr")
{
row["JobType"] = "Transfer Instruction".ToUpper();
}
row["Attn"] = obj.Terminalcode;
row["DeliveryTo"] = obj.DeliveryTo;
row["PickupFrom"] = obj.PickupFrom;
row["CrNo"] = "";
row["Contact1"] = "";
row["Contact2"] = "";
row["Tel1"] = "";
row["Fax1"] = "";
if (tab_hauler.Rows.Count > 0)
{
row["Haulier"] = tab_hauler.Rows[0]["Name"];
row["CrNo"] = tab_hauler.Rows[0]["CrNo"].ToString();
row["Contact1"] = tab_hauler.Rows[0]["Contact1"].ToString();
row["Contact2"] = tab_hauler.Rows[0]["Contact2"].ToString();
row["Tel1"] = tab_hauler.Rows[0]["Tel1"].ToString();
row["Fax1"] = tab_hauler.Rows[0]["Fax1"].ToString();
}
row["JobNo"] = orderNo;
row["JobDate"] = obj.EtdDate.ToString("dd/MM/yyyy");
row["Vessel"] = obj.Vessel;
row["Voyage"] = obj.Voyage;
row["Permit"] = obj.PermitNo;
string sql = "select name from xxparty where partyid='" + obj.ClientId + "'";
row["PartyName"] = SafeValue.SafeString(ConnectSql.ExecuteScalar(sql), "");
sql = "select Address from xxparty where partyid='" + obj.ClientId + "'";
row["PartyAdd"] = SafeValue.SafeString(ConnectSql.ExecuteScalar(sql), "");
row["Etd"] = obj.EtdDate.ToString("dd/MM/yyyy");
row["Eta"] = obj.EtaDate.ToString("dd/MM/yyyy");
sql = "select name from [XXPort] where code='" + SafeValue.SafeString(obj.Pol, "") + "'";
row["Pol"] = SafeValue.SafeString(Helper.Sql.One(sql), "");
sql = "select name from [XXPort] where code='" + SafeValue.SafeString(obj.Pod, "") + "'";
row["Pod"] = SafeValue.SafeString(Helper.Sql.One(sql), "");
sql = "select name from xxparty where partyid='" + obj.CarrierId + "'";
row["Carrier"] = SafeValue.SafeString(Helper.Sql.One(sql), "");
row["OceanBl"] = obj.CarrierBlNo;
row["CrBkgNo"] = obj.CarrierBkgNo;
row["Yard"] = obj.YardRef;
row["ByWho"] = "";
row["ClientRefNo"] = obj.ClientRefNo;
row["UserData2"] = "";
row["SpecialInstruction"] = obj.SpecialInstruction;
row["Remark"] = obj.Remark;
row["UserId"] = HttpContext.Current.User.Identity.Name;
decimal total = 0;
for (int i = 0; i < objSet1.Count; i++)
{
C2.JobHouse house = objSet1[i] as C2.JobHouse;
DataRow row1 = det.NewRow();
row1["No"] = i + 1;
row1["JobNo"] = orderNo;
row1["ContNo"] = house.ContNo;
row1["CargoStatus"] = house.CargoStatus;
row1["CargoType"] = house.CargoType;
row1["Location"] = house.Location;
row1["BookingNo"] = house.BookingNo;
row1["HblNo"] = house.HblNo;
row1["Length"] = house.LengthPack;
row1["Width"] = house.WidthPack;
row1["Height"] = house.HeightPack;
row1["Weight"] = house.WeightOrig;
row1["Volume"] = house.WeightOrig;
row1["Qty"] = house.QtyOrig;
row1["PackType"] = house.PackTypeOrig;
row1["PackQty"] = house.PackQty;
row1["PackUom"] = house.PackUom;
total += house.QtyOrig;
det.Rows.Add(row1);
}
row["TotalQty"] = total;
mast.Rows.Add(row);
set.Tables.Add(mast);
set.Tables.Add(det);
return(set);
}
public static DataSet DsQuotation(string orderNo, string docType)
{
DataSet set = new DataSet();
#region Quotation
DataTable mast = InitMastDataTable();
DataRow row = mast.NewRow();
Wilson.ORMapper.OPathQuery job = new Wilson.ORMapper.OPathQuery(typeof(C2.CtmJob), "QuoteNo='" + orderNo + "'");
C2.CtmJob ctmJob = C2.Manager.ORManager.GetObject(job) as C2.CtmJob;
if (ctmJob != null)
{
row["Vessel"] = ctmJob.Vessel;
row["Voyage"] = ctmJob.Voyage;
row["Eta"] = ctmJob.EtaDate.ToString("dd/MM/yy");
row["CustRefNo"] = ctmJob.ClientRefNo;
row["Pol"] = ctmJob.Pol;
row["Pod"] = ctmJob.Pod;
row["CrBkgNo"] = ctmJob.CarrierBkgNo;
row["HblNo"] = ctmJob.CarrierBlNo;
row["QuoteNo"] = ctmJob.QuoteNo;
row["QuoteDate"] = ctmJob.QuoteDate.ToString("dd/MM/yy");
row["JobNo"] = ctmJob.JobNo;
row["BillType"] = "QUOTATION";
row["UserId"] = HttpContext.Current.User.Identity.Name;
row["PartyName"] = EzshipHelper.GetPartyName(ctmJob.ClientId);
string sql = string.Format(@"select Address from XXParty where PartyId='{0}'", ctmJob.ClientId);
row["PartyAdd"] = C2.Manager.ORManager.ExecuteScalar(sql);
row["Terms"] = ctmJob.IncoTerm;
row["CompanyName"] = System.Configuration.ConfigurationManager.AppSettings["CompanyName"];
row["MoneyWords"] = "";
row["QuoteRemark"] = ctmJob.QuoteRemark;
row["JobDes"] = ctmJob.JobDes;
row["TerminalRemark"] = ctmJob.TerminalRemark;
decimal subTotal = 0;
decimal gstTotal = 0;
DataTable details = InitDetailDataTable();
sql = string.Format(@"select count(*) from job_rate where JobNo='{0}' and ChgCode='LUMPSUM'", orderNo);
int count = SafeValue.SafeInt(ConnectSql_mb.ExecuteScalar(sql), 0);
string sql_n = "";
if (count > 0)
{
sql = string.Format(@"select LineType,LineStatus,JobNo,JobType,ClientId,BillScope,BillType,BillClass,ContSize,ContType,ChgCode,ChgCodeDes,Remark,Price from job_rate where JobNo='{0}' and ChgCode='LUMPSUM'", orderNo);
sql_n = string.Format(@"select LineType,LineStatus,JobNo,JobType,ClientId,BillScope,BillType,BillClass,ContSize,ContType,ChgCode,ChgCodeDes,Remark,Price from job_rate where JobNo='{0}' and ChgCode<>'LUMPSUM'", orderNo);
}
else
{
sql = string.Format(@"select LineType,LineStatus,JobNo,JobType,ClientId,BillScope,BillType,BillClass,ContSize,ContType,ChgCode,ChgCodeDes,Remark,Price from job_rate where JobNo='{0}'", orderNo);
}
DataTable det = ConnectSql.GetTab(sql);
for (int i = 0; i < det.Rows.Count; i++)
{
DataRow row1 = details.NewRow();
string chgCode = SafeValue.SafeString(det.Rows[i]["ChgCode"]);
string chgCodeDes = SafeValue.SafeString(det.Rows[i]["ChgCodeDes"]);
row1["No"] = i + 1;
string chgDes = "";
DataTable det_n = ConnectSql.GetTab(sql_n);
for (int j = 0; j < det_n.Rows.Count; j++)
{
chgDes += "- " + SafeValue.SafeString(det_n.Rows[j]["ChgCodeDes"]) + "\n";
}
if (chgCode == "LUMPSUM")
{
chgCodeDes = ctmJob.LumSumRemark;
}
row1["ChgDes1"] = chgCodeDes;
row1["OtherChgDes"] = chgDes;
row1["ChgCode"] = SafeValue.SafeString(det.Rows[i]["ChgCode"]);
row1["Qty"] = 1;
row1["Rmk"] = SafeValue.SafeString(det.Rows[i]["Remark"]);
row1["LineAmt"] = SafeValue.ChinaRound(1 * SafeValue.SafeDecimal(det.Rows[i]["Price"]), 2);
string sql_chgCode = string.Format(@"select top 1 ChgcodeId,ChgcodeDes,GstP,GstTypeId,ChgTypeId from XXChgCode where ChgcodeDes='{0}'", chgCode);
DataTable dt_chgCode = ConnectSql.GetTab(sql_chgCode);
decimal gst = 0;
string gstType = "";
string chgTypeId = "";
if (dt_chgCode.Rows.Count > 0)
{
gst = SafeValue.SafeDecimal(dt_chgCode.Rows[0]["GstP"]);
gstType = SafeValue.SafeString(dt_chgCode.Rows[0]["GstTypeId"]);
chgTypeId = SafeValue.SafeString(dt_chgCode.Rows[0]["ChgTypeId"]);
}
row1["Price"] = string.Format("{0:#,##0.00}", SafeValue.SafeString(det.Rows[i]["Price"]));
row1["Gst"] = SafeValue.SafeInt(gst * 100, 0) + "% " + gstType + "R";
decimal amt = SafeValue.ChinaRound(1 * SafeValue.SafeDecimal(det.Rows[i]["Price"], 0), 2);
decimal gstAmt = SafeValue.ChinaRound((amt * gst), 2);
row1["Amt"] = amt;
row1["GstAmt"] = gstAmt;
row1["LocAmt"] = amt + gstAmt;
subTotal += amt;
gstTotal += gstAmt;
details.Rows.Add(row1);
}
row["SubTotal"] = subTotal;
row["GstTotal"] = gstTotal;
row["TotalAmt"] = subTotal + gstTotal;
mast.Rows.Add(row);
set.Tables.Add(mast);
set.Tables.Add(details);
}
#endregion
return(set);
}
private bool updating_verify(BizResult bzR, string user, CtmJobDet2 par)
{
bool res = false;
string role = SafeValue.SafeString(ConnectSql.ExecuteScalar(string.Format("select role from [user] where Name='{0}'", user)));
if (role.ToLower() == "client")
{
bzR.context = "No permission";
return(res);
}
if (role.ToLower().Equals("driver"))
{
if (trip.DriverCode.Equals(user) || trip.DriverCode2.Equals(user) || trip.DriverCode11.Equals(user) || trip.DriverCode12.Equals(user))
{
}
else
{
bzR.context = "No permission";
return(res);
}
}
CtmJobDet1 det1 = Manager.ORManager.GetObject <CtmJobDet1>(par.Det1Id);
CtmJob job = Manager.ORManager.GetObject(new Wilson.ORMapper.OPathQuery(typeof(C2.CtmJob), "JobNo='" + par.JobNo + "'")) as CtmJob;
if (job == null)
{
bzR.context = "Mast data is empty";
}
else
{
switch (job.StatusCode)
{
case "CLS":
bzR.context = "This Job have been completed";
break;
case "CNL":
bzR.context = "This Job have been Canceled";
break;
default:
break;
}
//switch (job.JobStatus)
//{
// case "Booked":
// bzR.context = "Requair confirm job";
// break;
// case "Cancel":
// bzR.context = "This job have been cancel";
// break;
// case "Reject":
// bzR.context = "This job have been Reject";
// break;
// case "Completed":
// case "Closed":
// bzR.context = "This job have been Completed";
// break;
// default:
// break;
//}
if (det1 != null)
{
//if (det1.StatusCode == "Completed")
//{
// bzR.context = "This container have been Completed";
//}
}
updating_verify_diffJobType_tripType(bzR, job, par);
if (bzR.context.Length == 0)
{
res = true;
}
}
return(res);
}